upload categories by DB
--step1 check account_combination
select cost_account_cob,clear_account_cob,
accumulated_deprn_cob,a.* from setup_categories_all a
where not exists ( select 'challenge' from gl_code_combinations_kfv where concatenated_segments= a.accumulated_deprn_cob)
create table SETUP_CATEGORIES_ALL
(
CODE_COMBINATION VARCHAR2(84),
BOOK_NAME VARCHAR2(30),
DEPRN_METHOD VARCHAR2(20),
DEPRN_LIFE NUMBER,
DEPRN_PRORATION VARCHAR2(20),
RETIREMENT_PRORATION VARCHAR2(20),
COST_ACCOUNT VARCHAR2(20),
CLEAR_ACCOUNT VARCHAR2(20),
COST_ACCOUNT_COB VARCHAR2(200),
CLEAR_ACCOUNT_COB VARCHAR2(200),
EXPENSE_ACCOUNT VARCHAR2(20),
ACCUMULATED_DEPRN VARCHAR2(20),
ACCUMULATED_DEPRN_COB VARCHAR2(200),
CIP_COST_ACCOUNT VARCHAR2(20),
CIP_CLEAR_ACCOUNT VARCHAR2(20),
CIP_COST_ACCOUNT_COB VARCHAR2(200),
CIP_CLEAR_ACCOUNT_COB VARCHAR2(200),
MAJOR VARCHAR2(20),
MINOR VARCHAR2(20),
SUB VARCHAR2(20),
CAP VARCHAR2(20),
percent_salvage_value number
);
select * from FND_ID_FLEX_STRUCTURES_VL where id_flex_structure_code ='GECI_ACCOUNTING_FLEXFIELD'
-----chart_of_account = 50349
declare
v_category_id number;
cursor categories is
select * from setup_categories_all a where not exists (select 'challenge tang' from FA_CATEGORIES_B fc,FA_CATEGORY_BOOKS fb
where fc.category_id=fb.category_id
and fc.segment1 = upper(a.major)
and fc.segment2 = upper(a.minor)
and fc.segment3 = upper(a.sub)
and fc.segment4 = upper(a.cap)
and fb.book_type_code = a.book_name ) ;
begin
for C1 in categories loop
begin
select category_id
into v_category_id
from FA_CATEGORIES_B
where segment1 = upper(C1.major)
and segment2 = upper(C1.minor)
and segment3 = upper(C1.sub)
and segment4 = upper(C1.cap);
dbms_output.put_line(C1.major || '.' || C1.minor || '.' || C1.sub || '.' ||
C1.cap || ' Category ID: ' || v_category_id);
exception
when others then
v_category_id := null;
--- dbms_output.put_line('v_category_id is null');
end;
if v_category_id is null then
select FA_CATEGORIES_B_S.nextval into v_category_id from dual;
dbms_output.put_line(C1.major || '.' || C1.minor || '.' || C1.sub || '.' ||
C1.cap || ' Category1 ID: ' || v_category_id);
insert into FA_CATEGORIES_TL
(category_id,
language,
source_lang,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login)
values
(v_category_id, 'US', 'US', sysdate, 1763, 1763, sysdate, 982535);
insert into FA_CATEGORIES_TL
(category_id,
language,
source_lang,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login)
values
(v_category_id, 'ZHS', 'US', sysdate, 1763, 1763, sysdate, 982535);
insert into FA_CATEGORIES_B
(category_id,
summary_flag,
enabled_flag,
owned_leased,
last_update_date,
last_updated_by,
category_type,
capitalize_flag,
segment1,
segment2,
segment3,
segment4,
inventorial,
created_by,
creation_date,
last_update_login)
values
(v_category_id,
'N',
'Y',
'OWNED',
SYSDATE,
1763,
'NON-LEASE',
'YES',
upper(C1.major),
upper(C1.minor),
upper(C1.sub),
upper(C1.cap),
'YES',
1763,
SYSDATE,
982535);
insert into FA_CATEGORY_BOOKS
(category_id,
book_type_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
asset_clearing_acct,
asset_cost_acct,
cip_clearing_acct,
cip_cost_acct,
deprn_expense_acct,
deprn_reserve_acct,
asset_cost_account_ccid,----
asset_clearing_account_ccid,
wip_cost_account_ccid,
wip_clearing_account_ccid,
reserve_account_ccid,
bonus_deprn_expense_acct,
bonus_deprn_reserve_acct,
bonus_reserve_acct_ccid)
values
(v_category_id,
C1.book_name,
sysdate,
1763,
1763,
sysdate,
982535,
C1.clear_account,
C1.cost_account,
C1.cip_clear_account,
C1.cip_cost_account,
C1.expense_account,
C1.accumulated_deprn,
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.cost_account_cob),
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.clear_account_cob),
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.cip_cost_account_cob),
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.cip_clear_account_cob),
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.accumulated_deprn_cob),
C1.expense_account,
C1.accumulated_deprn,
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.accumulated_deprn_cob));
insert into FA_CATEGORY_BOOK_DEFAULTS
(category_id,
book_type_code,
start_dpis,
life_in_months,
deprn_method,
prorate_convention_code,
itc_eligible_flag,
use_itc_ceilings_flag,
depreciate_flag,
retirement_prorate_convention,
use_stl_retirements_flag,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
capital_gain_threshold,
use_deprn_limits_flag,
percent_salvage_value)
values
(v_category_id,
C1.book_name,
to_date('1970/01/01', 'YYYY/MM/DD'),
C1.deprn_life * 12,
C1.deprn_method,
C1.deprn_proration,
'NO',
'NO',
'YES',
C1.retirement_proration,
'NO',
sysdate,
1763,
1763,
sysdate,
982535,
12,
'NO',
C1.percent_salvage_value);
elsif v_category_id is not null then
dbms_output.put_line('v_category_id: ' || v_category_id);
insert into FA_CATEGORY_BOOKS
(category_id,
book_type_code,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
asset_clearing_acct,
asset_cost_acct,
cip_clearing_acct,
cip_cost_acct,
deprn_expense_acct,
deprn_reserve_acct,
asset_cost_account_ccid,
asset_clearing_account_ccid,
wip_cost_account_ccid,
wip_clearing_account_ccid,
reserve_account_ccid,
bonus_deprn_expense_acct,
bonus_deprn_reserve_acct,
bonus_reserve_acct_ccid)
values
(v_category_id,
C1.book_name,
sysdate,
1763,
1763,
sysdate,
982535,
C1.clear_account,
C1.cost_account,
C1.cip_clear_account,
C1.cip_cost_account,
C1.expense_account,
C1.accumulated_deprn,
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.cost_account_cob),
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.clear_account_cob),
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.cip_cost_account_cob),
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.cip_clear_account_cob),
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.accumulated_deprn_cob),
C1.expense_account,
C1.accumulated_deprn,
(select CODE_COMBINATION_ID
from gl_code_combinations_kfv
where chart_of_accounts_id = '&Chart_Of_Account_ID'
and concatenated_segments = C1.accumulated_deprn_cob));
insert into FA_CATEGORY_BOOK_DEFAULTS
(category_id,
book_type_code,
start_dpis,
life_in_months,
deprn_method,
prorate_convention_code,
itc_eligible_flag,
use_itc_ceilings_flag,
depreciate_flag,
retirement_prorate_convention,
use_stl_retirements_flag,
last_update_date,
last_updated_by,
created_by,
creation_date,
last_update_login,
capital_gain_threshold,
use_deprn_limits_flag,
percent_salvage_value)
values
(v_category_id,
C1.book_name,
to_date('1970/01/01', 'YYYY/MM/DD'),
C1.deprn_life * 12,
C1.deprn_method,
C1.deprn_proration,
'NO',
'NO',
'YES',
C1.retirement_proration,
'NO',
sysdate,
1763,
1763,
sysdate,
982535,
12,
'NO',
C1.percent_salvage_value);
/* dbms_output.put_line(''in);*/
end if;
end loop;
end;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
