[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| for to determine sql statments for GT insert based on sources in |
| use for each extract type |
| |
| NOTES |
| This package relies on some static business logic specific to FA |
| as well as the XLA AAD setups for determining addition sources, |
| tables, columns to extract. |
| |
| Primary restrictions: |
| 1) in base R12 standard sources can only be seeded by ORACLE |
| thus attempts to add additional sources may either be ignored |
| or fail (depending on whether FKs for table/column are loaded) |
| 2) on a related note, the tables allowed in the seeded setup are |
| a small subset and to add a table requires not only the seed |
| and case impacts (GT), but also if it is a new table not yet |
| recognized by this program, various sections of this code must be |
| updated - including arrays and the "where clause append" section |
| WITHOUT the correct where clause the risks of excluding data or |
| causing cartesion products (and thus ora-1) will appear |
| 3) New sources from existing tables can be added at any time |
| without impact to this program, but require case changes to the |
| GT extract tables to hold those sources |
| 4) *** NEW *** |
| DO NOT EDIT /SAVE THIS FILE WITH TABS!!!!!!! |
| String comparison in particular looks for spaces and you risk |
| breaking the logic. If your editor does this, than use vi!!!!! |
| if you do this, then dont !!!! |
| |
| *** CUSTOMIZATION OF THIS PACKAGE OR STANDARD SOURCES IS NOT SUPPORTED *** |
| |
| HISTORY |
| 25-FEB-2006 BRIDGWAY Created |
| |
+===========================================================================*/
--+==========================================================================+
--| |
--| Private global constants |
--| |
--+==========================================================================+
C_CREATED_ERROR CONSTANT BOOLEAN := FALSE;
c_hdr_insert CONSTANT VARCHAR2(32000) := '
insert into fa_xla_ext_headers_b_gt (
event_id ,
DEFAULT_CCID ,
BOOK_TYPE_CODE ,
PERIOD_NAME ,
PERIOD_CLOSE_DATE ,
PERIOD_COUNTER ,
ACCOUNTING_DATE ,
TRANSFER_TO_GL_FLAG ';
c_hdr_select CONSTANT VARCHAR2(32000) := ' )
select ctlgd.event_id,
bc.FLEXBUILDER_DEFAULTS_CCID ,
bc.book_type_code ,
dp.PERIOD_NAME ,
dp.CALENDAR_PERIOD_CLOSE_DATE ,
dp.PERIOD_COUNTER ,
ctlgd.event_date ,';
c_hdr_select1 CONSTANT VARCHAR2(32000) := '
''Y'' ' ;
c_hdr_select2 CONSTANT VARCHAR2(32000) := '
decode(bc.GL_POSTING_ALLOWED_FLAG ,
''YES'', ''Y'',''N'') ';
c_line_insert_deprn CONSTANT VARCHAR2(32000) := '
insert into fa_xla_ext_lines_b_gt (
EVENT_ID ,
LINE_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_TYPE_CODE ,
LEDGER_ID ,
CURRENCY_CODE ,
ENTERED_AMOUNT ,
BONUS_ENTERED_AMOUNT ,
REVAL_ENTERED_AMOUNT ,
GENERATED_CCID ,
GENERATED_OFFSET_CCID ,
BONUS_GENERATED_CCID ,
BONUS_GENERATED_OFFSET_CCID ,
REVAL_GENERATED_CCID ,
REVAL_GENERATED_OFFSET_CCID ,
RESERVE_ACCOUNT_CCID ,
DEPRN_EXPENSE_ACCOUNT_CCID ,
BONUS_RESERVE_ACCT_CCID ,
BONUS_EXPENSE_ACCOUNT_CCID ,
REVAL_AMORT_ACCOUNT_CCID ,
REVAL_RESERVE_ACCOUNT_CCID ,
BOOK_TYPE_CODE ,
PERIOD_COUNTER '; -- Bug:6399642
c_line_select_deprn CONSTANT VARCHAR2(32000) := ' )
select ctlgd.EVENT_ID ,
dd.distribution_id as distribution_id,
dd.distribution_id as dist_id,
''DEPRN'' ,
bc.set_of_books_id ,
le.currency_code ,
dd.deprn_amount
- dd.deprn_adjustment_amount , -- BUG# 5094085 removing bonus subtraction intentionally
dd.bonus_deprn_amount
- dd.bonus_deprn_adjustment_amount ,
dd.reval_amortization ,
dd.deprn_expense_ccid ,
dd.deprn_reserve_ccid ,
dd.bonus_deprn_expense_ccid ,
dd.bonus_deprn_reserve_ccid ,
dd.reval_amort_ccid ,
dd.reval_reserve_ccid ,
cb.RESERVE_ACCOUNT_CCID ,
cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
cb.BONUS_RESERVE_ACCT_CCID ,
cb.BONUS_EXPENSE_ACCOUNT_CCID ,
cb.REVAL_AMORT_ACCOUNT_CCID ,
cb.REVAL_RESERVE_ACCOUNT_CCID ,
ctlgd.source_id_char_1 ,
dd.period_counter '; -- Bug:6399642
c_line_insert_def CONSTANT VARCHAR2(32000) := '
insert into fa_xla_ext_lines_b_gt (
EVENT_ID ,
LINE_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_TYPE_CODE ,
LEDGER_ID ,
CURRENCY_CODE ,
ENTERED_AMOUNT ,
BOOK_TYPE_CODE ,
TAX_BOOK_TYPE_CODE ,
GENERATED_CCID ,
GENERATED_OFFSET_CCID ';
c_line_select_def CONSTANT VARCHAR2(32000) := ' )
select ctlgd.EVENT_ID ,
df.distribution_id as distribution_id,
df.distribution_id as dist_id,
''DEFERRED'' ,
bc.set_of_books_id ,
le.currency_code ,
df.deferred_deprn_expense_amount ,
df.corp_book_type_code ,
df.tax_book_type_code ,
df.deferred_deprn_expense_ccid ,
df.deferred_deprn_reserve_ccid ';
c_line_insert_stg CONSTANT VARCHAR2(32000) := '
insert into fa_xla_ext_lines_stg_gt (
EVENT_ID ,
EVENT_TYPE_CODE ,
TRANSACTION_HEADER_ID ,
MEMBER_TRANSACTION_HEADER_ID ,
DISTRIBUTION_TYPE_CODE ,
BOOK_TYPE_CODE ,
LEDGER_ID ,
CURRENCY_CODE ,
ASSET_TYPE ,
ASSET_COST_ACCOUNT_CCID ,
ASSET_CLEARING_ACCOUNT_CCID ,
CIP_COST_ACCOUNT_CCID ,
CIP_CLEARING_ACCOUNT_CCID ,
RESERVE_ACCOUNT_CCID ,
DEPRN_EXPENSE_ACCOUNT_CCID ,
BONUS_RESERVE_ACCT_CCID ,
BONUS_EXPENSE_ACCOUNT_CCID ,
REVAL_AMORT_ACCOUNT_CCID ,
REVAL_RESERVE_ACCOUNT_CCID ,
UNPLAN_EXPENSE_ACCOUNT_CCID ,
ALT_COST_ACCOUNT_CCID ,
WRITE_OFF_ACCOUNT_CCID ';
c_line_select_stg CONSTANT VARCHAR2(32000) := ' )
select ctlgd.EVENT_ID ,
ctlgd.event_type_code ,
th.transaction_header_id ,
nvl(th.member_transaction_header_id,
th.transaction_header_id) ,
''TRX'' ,
bc.book_type_code , -- Bug:6272229
bc.set_of_books_id ,
le.currency_code ,
ah.asset_type ,
cb.ASSET_COST_ACCOUNT_CCID ,
cb.ASSET_CLEARING_ACCOUNT_CCID ,
cb.WIP_COST_ACCOUNT_CCID ,
cb.WIP_CLEARING_ACCOUNT_CCID ,
cb.RESERVE_ACCOUNT_CCID ,
cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
cb.BONUS_RESERVE_ACCT_CCID ,
cb.BONUS_EXPENSE_ACCOUNT_CCID ,
cb.REVAL_AMORT_ACCOUNT_CCID ,
cb.REVAL_RESERVE_ACCOUNT_CCID ,
cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
cb.ALT_COST_ACCOUNT_CCID ,
cb.WRITE_OFF_ACCOUNT_CCID ';
c_line_insert_trx CONSTANT VARCHAR2(32000) := '
insert into fa_xla_ext_lines_b_gt (
EVENT_ID ,
LINE_NUMBER ,
DISTRIBUTION_ID ,
DISTRIBUTION_TYPE_CODE ,
LEDGER_ID ,
CURRENCY_CODE ,
BOOK_TYPE_CODE ,
GENERATED_CCID ,
ASSET_ID ,
ASSET_TYPE ,
ASSET_COST_ACCOUNT_CCID ,
ASSET_CLEARING_ACCOUNT_CCID ,
CIP_COST_ACCOUNT_CCID ,
CIP_CLEARING_ACCOUNT_CCID ,
RESERVE_ACCOUNT_CCID ,
DEPRN_EXPENSE_ACCOUNT_CCID ,
BONUS_RESERVE_ACCT_CCID ,
BONUS_EXPENSE_ACCOUNT_CCID ,
REVAL_AMORT_ACCOUNT_CCID ,
REVAL_RESERVE_ACCOUNT_CCID ,
UNPLAN_EXPENSE_ACCOUNT_CCID ,
ALT_COST_ACCOUNT_CCID ,
WRITE_OFF_ACCOUNT_CCID ,
ENTERED_AMOUNT '; -- Bug:6399642
c_line_select_trx CONSTANT VARCHAR2(32000) := ' )
select stg.EVENT_ID ,
adj.adjustment_line_id ,
adj.distribution_id ,
stg.distribution_type_code ,
stg.ledger_id ,
stg.currency_code ,
stg.book_type_code ,
adj.code_combination_id ,
adj.asset_id ,
stg.asset_type ,
stg.ASSET_COST_ACCOUNT_CCID ,
stg.ASSET_CLEARING_ACCOUNT_CCID ,
stg.CIP_COST_ACCOUNT_CCID ,
stg.CIP_CLEARING_ACCOUNT_CCID ,
stg.RESERVE_ACCOUNT_CCID ,
stg.DEPRN_EXPENSE_ACCOUNT_CCID ,
stg.BONUS_RESERVE_ACCT_CCID ,
stg.BONUS_EXPENSE_ACCOUNT_CCID ,
stg.REVAL_AMORT_ACCOUNT_CCID ,
stg.REVAL_RESERVE_ACCOUNT_CCID ,
stg.UNPLAN_EXPENSE_ACCOUNT_CCID ,
stg.ALT_COST_ACCOUNT_CCID ,
stg.WRITE_OFF_ACCOUNT_CCID , ';
c_line_select_trx_dist1 CONSTANT VARCHAR2(32000) := ' )
select stg.EVENT_ID ,
adj.adjustment_line_id ,
adj.distribution_id ,
stg.distribution_type_code ,
stg.ledger_id ,
stg.currency_code ,
stg.book_type_code ,
adj.code_combination_id ,
adj.asset_id ,
stg.asset_type ,
cb.ASSET_COST_ACCOUNT_CCID ,
cb.ASSET_CLEARING_ACCOUNT_CCID ,
cb.WIP_COST_ACCOUNT_CCID ,
cb.WIP_CLEARING_ACCOUNT_CCID ,
cb.RESERVE_ACCOUNT_CCID ,
cb.DEPRN_EXPENSE_ACCOUNT_CCID ,
cb.BONUS_RESERVE_ACCT_CCID ,
cb.BONUS_EXPENSE_ACCOUNT_CCID ,
cb.REVAL_AMORT_ACCOUNT_CCID ,
cb.REVAL_RESERVE_ACCOUNT_CCID ,
cb.UNPLAN_EXPENSE_ACCOUNT_CCID ,
cb.ALT_COST_ACCOUNT_CCID ,
cb.WRITE_OFF_ACCOUNT_CCID , ';
''Rows inserted into lines: '' || to_char(SQL%ROWCOUNT));
c_mls_insert CONSTANT VARCHAR2(32000) := '
insert into fa_xla_ext_lines_tl_gt (
event_id ,
line_number ,
LEDGER_ID ,
TRANSACTION_HEADER_ID ,
ASSET_ID ,
DEPRN_RUN_ID ,
BOOK_TYPE_CODE ,
PERIOD_COUNTER '; -- Bug:6399642
c_mls_select CONSTANT VARCHAR2(32000) := ' )
select xl.event_id ,
xl.line_number ,
xl.ledger_id ,
xl.TRANSACTION_HEADER_ID ,
xl.ASSET_ID ,
xl.DEPRN_RUN_ID ,
xl.BOOK_TYPE_CODE ,
xl.PERIOD_COUNTER '; -- Bug:6399642
procedure delete_table_member (p_table IN OUT NOCOPY v30_tbl,
p_index IN number) is
l_procedure_name varchar2(80) := ' delete_table_member';
p_table.delete(p_index);
p_table.delete(l_count + 1);
end delete_table_member;
select distinct table_name,
decode(table_name,
-- standard headers/lines
'FA_BOOK_CONTROLS' , 'bc',
'FA_DEPRN_PERIODS' , 'dp',
'XLA_EVENTS_GT' , 'ctgld',
-- standard lines
'FA_ADDITIONS_B' , 'ad',
'FA_ADJUSTMENTS' , 'adj',
'FA_ASSET_HISTORY' , 'ah',
'FA_CATEGORY_BOOKS' , 'cb',
'FA_DISTRIBUTION_HISTORY' , 'dh',
'FA_DEFERRED_DEPRN' , 'df',
'FA_DEPRN_DETAIL' , 'dd',
'FA_LOOKUPS' , 'lu',
'FA_TRANSACTION_HEADERS' , 'th',
'FA_RETIREMENTS' , 'ret',
'FA_XLA_EXT_LINES_STG_GT' , 'stg',
'GL_LEDGERS' , 'le',
-- non-standard
'FA_ADDITIONS_TL' , 'adtl',
'FA_ASSET_INVOICES' , 'ai',
'FA_ASSET_KEYWORDS' , 'key',
'FA_BOOKS' , 'bk',
'FA_CATEGORIES_B' , 'cat',
'FA_CATEGORIES_TL' , 'cattl',
'FA_DEPRN_SUMMARY' , 'ds',
'FA_LEASES' , 'ls',
'FA_LOCATIONS' , 'loc',
'FA_METHODS' , 'mt',
'INVALID')
from all_tables tab,
TABLE(CAST(p_tables AS fa_char30_tbl_type)) fatab,
TABLE(CAST(p_schemas AS fa_char30_tbl_type)) stab
where tab.table_name = fatab.column_value
and tab.owner = stab.column_value;
select distinct
sources.source_code,
sources.source_table_name,
sources.source_column_name
from xla_aad_sources aad,
xla_sources_b sources,
TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
where aad.application_id = 140
and sources.application_id = 140
and aad.entity_code = p_entity_code
and aad.source_level_code = p_source_level_code
and aad.event_class_code = fatab.column_value
and aad.source_code = sources.source_code
and sources.source_table_name is not null
and sources.source_table_name not in ('FA_ADDITIONS_TL', 'FA_CATEGORIES_TL')
order by 2,1;
select distinct
sources.source_code,
sources.source_table_name,
sources.source_column_name
from xla_aad_sources aad,
xla_sources_b sources,
TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
where aad.application_id = 140
and sources.application_id = 140
and aad.entity_code = p_entity_code
and aad.source_level_code = p_source_level_code
and aad.event_class_code = fatab.column_value
and aad.source_code = sources.source_code
and sources.source_table_name is not null
and sources.source_table_name in
(select fatab2.column_value
from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
order by 2,1;
select distinct
sources.source_code,
'FA_XLA_EXT_LINES_STG_GT',
sources.source_code
from xla_aad_sources aad,
xla_sources_b sources,
TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
where aad.application_id = 140
and sources.application_id = 140
and aad.entity_code = p_entity_code
and aad.source_level_code = p_source_level_code
and aad.event_class_code = fatab.column_value
and aad.source_code = sources.source_code
and sources.source_table_name is not null
and sources.source_table_name not in
(select fatab2.column_value
from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
union
select distinct
sources.source_code,
sources.source_table_name,
sources.source_column_name
from xla_aad_sources aad,
xla_sources_b sources,
TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
where aad.application_id = 140
and sources.application_id = 140
and aad.entity_code = p_entity_code
and aad.source_level_code = p_source_level_code
and aad.event_class_code = fatab.column_value
and aad.source_code = sources.source_code
and sources.source_table_name is not null
and sources.source_table_name in
(select fatab2.column_value
from TABLE(CAST(p_known_tables AS fa_char30_tbl_type)) fatab2)
order by 2,1;
select distinct
sources.source_code,
sources.source_table_name,
sources.source_column_name
from xla_aad_sources aad,
xla_sources_b sources,
TABLE(CAST(p_event_class_table AS fa_char30_tbl_type)) fatab
where aad.application_id = 140
and sources.application_id = 140
and aad.entity_code = p_entity_code
and aad.source_level_code = p_source_level_code
and aad.event_class_code = fatab.column_value
and aad.source_code = sources.source_code
and sources.source_table_name in ('FA_ADDITIONS_TL', 'FA_CATEGORIES_TL')
order by 2,1;
l_insert varchar2(32000);
l_select varchar2(32000);
l_insert := c_hdr_insert;
l_select := c_hdr_select;
l_select := l_select || c_hdr_select1;
l_select := l_select || c_hdr_select2;
l_insert := c_line_insert_deprn;
l_select := c_line_select_deprn;
l_select := replace(l_select, 'select ' ,
'select /*+ ordered use_hash(CB,BC,LE) swap_join_inputs(CB) swap_join_inputs(BC) swap_join_inputs(LE) */ ');
l_insert := c_line_insert_def;
l_select := c_line_select_def;
l_insert := c_line_insert_trx;
l_select := c_line_select_trx_dist1;
l_select := c_line_select_trx;
l_select := l_select || c_line_adj_amt_fin1;
l_select := l_select || c_line_adj_amt_fin2;
l_select := l_select || c_line_adj_amt_xfr;
l_select := l_select || c_line_adj_amt_dist1;
l_select := l_select || c_line_adj_amt_dist2;
l_select := l_select || c_line_adj_amt_ret;
l_select := l_select || c_line_adj_amt_ret;
l_select := replace(l_select, 'select ' ,
'select /*+ leading(stg) index(adj, FA_ADJUSTMENTS_U1) */ ');
l_insert := c_line_insert_stg;
l_select := c_line_select_stg;
l_select := replace(l_select, 'select ' ,
'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_U1) */ ');
l_select := replace(l_select, 'select ' ,
'select /*+ leading(ctgld) index(th, FA_TRANSACTION_HEADERS_N7) */ ');
l_insert := c_mls_insert;
l_select := c_mls_select;
if (instr(upper(l_insert), ' ' || l_source_code(l_index) || ' ') > 0) then -- BUG# 6779783
delete_table_member(l_source_code, l_index);
delete_table_member(l_column_name, l_index);
delete_table_member(l_table_name, l_index);
l_insert := l_insert || ',' || fa_cmp_string_pkg.g_chr_newline || ' ' || l_source_code(i);
l_select := l_select || ',' || fa_cmp_string_pkg.g_chr_newline || ' ' || l_alias(i) || '.' || l_column_name(i) ;
l_select := replace(l_select, 'FA_ADJUSTMENTS_U1' ,
'FA_MC_ADJUSTMENTS_U1') ;
l_select := replace(l_select, 'stg.ledger_id', 'bc.set_of_books_id');
l_select := replace(l_select, 'stg.currency_code', 'le.currency_code');
l_bodypkg := l_insert || l_select || l_from || l_where || ';' || fa_cmp_string_pkg.g_chr_newline ;