Import new categories on Oracle R12 Fixed Assets modul

 The following must be modified according to implementation requirements:

CREATE TABLE FA_CATEGORIES_B_TMP AS SELECT * FROM FA_CATEGORIES_B;

CREATE TABLE FA_CATEGORIES_TL_TMP AS SELECT * FROM FA_CATEGORIES_TL;

CREATE TABLE FA_CATEGORY_BOOKS_TMP AS SELECT * FROM FA_CATEGORY_BOOKS;

CREATE TABLE FA_CATEGORY_BOOK_DEFAULTS_TMP AS SELECT * FROM FA_CATEGORY_BOOK_DEFAULTS;

UPDATE FA_CATEGORY_BOOK_DEFAULTS_TMP set start_dpis = '31-jan-1964'


ALTER TABLE FA_CATEGORIES_B_TMP ADD (old_category_id NUMBER)

UPDATE FA_CATEGORIES_B_TMP
SET old_category_id = category_id

UPDATE FA_CATEGORIES_B_TMP SET CATEGORY_ID = FA_CATEGORIES_B_S.NEXTVAL;

UPDATE FA_CATEGORIES_TL_TMP B SET B.CATEGORY_ID = (SELECT A.CATEGORY_ID FROM FA_CATEGORIES_B_TMP A WHERE b.CATEGORY_ID = A.OLD_CATEGORY_ID)

UPDATE FA_CATEGORY_BOOKS_TMP B SET B.CATEGORY_ID = (SELECT A.CATEGORY_ID FROM FA_CATEGORIES_B_TMP A WHERE A.OLD_CATEGORY_ID = B.CATEGORY_ID)

UPDATE FA_CATEGORY_BOOK_DEFAULTS_TMP B SET B.CATEGORY_ID = (SELECT A.CATEGORY_ID FROM FA_CATEGORIES_B_TMP A WHERE A.OLD_CATEGORY_ID = B.CATEGORY_ID)


CREATE TABLE COMB_CONT
 (new_c1 NUMBER,
cont varchar2(15)--,
-- new_c2 NUMBER,
-- new_c3 NUMBER,
-- new_c4 NUMBER,
-- new_c5 NUMBER,
-- new_c6 NUMBER)
)


INSERT INTO COMB_CONT(cont)
SELECT DISTINCT --c.ASSET_CLEARING_ACCT A
--c.ASSET_COST_ACCT A
--c.cip_cost_acct A
--c.deprn_expense_acct A
----c.REVAL_AMORTIZATION_ACCT, c.REVAL_AMORTIZATION_ACCT||'-0000-000-00-A-0-0' c3,
--c.REVAL_RESERVE_ACCT
--c.DEPRN_RESERVE_ACCT
--c.IMPAIR_EXPENSE_ACCT
c.IMPAIR_RESERVE_ACCT
FROM FA_CATEGORY_BOOKS_TMP c; --WHERE c.REVAL_RESERVE_ACCT IS NOT NULL




UPDATE COMB_CONT A
SET A.new_c1 = (SELECT b.CODE_COMBINATION_ID FROM gl_code_combinations b
WHERE cont = b.segment1 AND b.segment2||'.'||b.segment4||'.'||b.segment3='0.0.OMP');


UPDATE FA_CATEGORY_BOOKS_TMP A SET
A.ASSET_CLEARING_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.ASSET_CLEARING_ACCT = B.cont),
A.ASSET_COST_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.ASSET_COST_ACCT = B.cont),
A.BONUS_RESERVE_ACCT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.BONUS_deprn_RESERVE_ACCT = B.cont),
A.BONUS_EXPENSE_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.BONUS_DEPRN_EXPENSE_ACCT = B.cont),
A.RESERVE_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.deprn_RESERVE_ACCT = B.cont),
A.DEPRN_EXPENSE_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.DEPRN_EXPENSE_ACCT = B.cont),
--a.REVAL_AMORT_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE a.REVAL_AMORT_ACCOUNT_CCID = B.REVAL_AMORT_ACCOUNT_CCID),
A.WIP_COST_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.CIP_COST_ACCT = B.cont),
A.WIP_CLEARING_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.ASSET_CLEARING_ACCT = B.cont),
A.REVAL_RESERVE_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.REVAL_RESERVE_ACCT = B.cont)--,
--A.IMPAIR_EXPENSE_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.IMPAIR_EXPENSE_ACCT = B.cont),
--A.IMPAIR_RESERVE_ACCOUNT_CCID = (SELECT DISTINCT b.new_c1 FROM COMB_CONT b WHERE A.IMPAIR_RESERVE_ACCT = B.cont);
where a.book_type_code = 'OMP_REGMF';


---check Life in months
SELECT DISTINCT x.life_in_months FROM FA_CATEGORY_BOOK_DEFAULTS_TMP x
WHERE x.LIFE_IN_MONTHS NOT IN (SELECT DISTINCT x1.life_in_months FROM fa_methods  x1 WHERE x1.METHOD_CODE = 'STL')
AND x.DEPRN_METHOD = 'STL'


ALTER TABLE APPS.FA_CATEGORIES_B_TMP DROP COLUMN OLD_CATEGORY_ID;

INSERT INTO FA_CATEGORIES_B SELECT * FROM FA_CATEGORIES_B_TMP;

INSERT INTO FA_CATEGORIES_TL SELECT * FROM FA_CATEGORIES_TL_TMP ;

INSERT INTO FA_CATEGORY_BOOKS SELECT * FROM FA_CATEGORY_BOOKS_TMP;

INSERT INTO FA_CATEGORY_BOOK_DEFAULTS SELECT * FROM FA_CATEGORY_BOOK_DEFAULTS_TMP;



DROP TABLE FA_CATEGORIES_B_TMP;

DROP TABLE FA_CATEGORIES_TL_TMP;

DROP TABLE FA_CATEGORY_BOOKS_TMP;

DROP TABLE FA_CATEGORY_BOOK_DEFAULTS_TMP;

DROP TABLE COMB_CONT;