The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT application_short_name
FROM fnd_application
WHERE application_id = p_application_id;
DELETE FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = p_staging_context_code;
SELECT count(*)
FROM xla_aad_loader_defns_t xal
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.table_name = 'XLA_PRODUCT_RULES'
AND ROWNUM = 1;
SELECT fa.application_name file_app_name
, fa2.application_name resp_app_name
FROM xla_aad_loader_defns_t xal
, fnd_application_vl fa
, fnd_application_vl fa2
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.table_name = 'XLA_AAD'
AND xal.application_short_name = fa.application_short_name
AND fa.application_id <> p_application_id
AND fa2.application_id = p_application_id;
SELECT table_name
, DECODE(table_name
,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_code)
, analytical_criterion_code
, count(*)
FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = p_staging_context_code
AND table_name IN ('XLA_ANALYTICAL_HDRS')
GROUP BY
table_name
, DECODE(table_name
,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_code)
, analytical_criterion_code
HAVING count(*) > 1;
SELECT table_name
, DECODE(table_name
,'XLA_ANALYTICAL_SOURCES' ,analytical_criterion_type_code||C_CHAR||
analytical_criterion_code||C_CHAR||
event_class_code)
, analytical_criterion_type_code
, analytical_criterion_code
, event_class_code
, count(*)
FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = p_staging_context_code
AND table_name IN ('XLA_ANALYTICAL_SOURCES')
GROUP BY
table_name
, DECODE(table_name
,'XLA_ANALYTICAL_SOURCES' ,analytical_criterion_type_code||C_CHAR||
analytical_criterion_code||C_CHAR||
event_class_code)
, analytical_criterion_type_code
, analytical_criterion_code
, event_class_code
HAVING count(*) > 1;
SELECT table_name
, DECODE(table_name
,'XLA_PRODUCT_RULES' ,product_rule_type_code
,'XLA_LINE_DEFINITIONS' ,event_class_code||C_CHAR||
event_type_code||C_CHAR||
line_definition_owner_code
,'XLA_ACCT_LINE_TYPES' ,event_class_code||C_CHAR||
accounting_line_type_code
,'XLA_DESCRIPTIONS' ,description_type_code
,'XLA_SEG_RULES' ,segment_rule_type_code
,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_type_code
,'XLA_ANALYTICAL_DTLS' ,analytical_criterion_type_code||C_CHAR||
analytical_criterion_code
,'XLA_MAPPING_SETS' ,NULL)
, name
, count(*)
FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = p_staging_context_code
AND name IS NOT NULL
AND table_name IN ('XLA_PRODUCT_RULES'
,'XLA_LINE_DEFINITIONS'
,'XLA_ACCT_LINE_TYPES'
,'XLA_DESCRIPTIONS'
,'XLA_SEG_RULES'
,'XLA_ANALYTICAL_HDRS'
,'XLA_ANALYTICAL_DTLS'
,'XLA_MAPPING_SETS')
GROUP BY
table_name
, DECODE(table_name
,'XLA_PRODUCT_RULES' ,product_rule_type_code
,'XLA_LINE_DEFINITIONS' ,event_class_code||C_CHAR||
event_type_code||C_CHAR||
line_definition_owner_code
,'XLA_ACCT_LINE_TYPES' ,event_class_code||C_CHAR||
accounting_line_type_code
,'XLA_DESCRIPTIONS' ,description_type_code
,'XLA_SEG_RULES' ,segment_rule_type_code
,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_type_code
,'XLA_ANALYTICAL_DTLS' ,analytical_criterion_type_code||C_CHAR||
analytical_criterion_code
,'XLA_MAPPING_SETS' ,NULL)
, name
HAVING count(*) > 1;
SELECT table_name
, DECODE(table_name
,'XLA_PRODUCT_RULES' ,product_rule_type_code
,'XLA_LINE_DEFINITIONS' ,event_class_code||C_CHAR||
event_type_code||C_CHAR||
line_definition_owner_code
,'XLA_ACCT_LINE_TYPES' ,event_class_code||C_CHAR||
accounting_line_type_code
,'XLA_DESCRIPTIONS' ,description_type_code
,'XLA_SEG_RULES' ,segment_rule_type_code
,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_type_code
,'XLA_ANALYTICAL_DTLS' ,analytical_criterion_type_code||C_CHAR||
analytical_criterion_code
,'XLA_MAPPING_SETS' ,NULL)
, DECODE(table_name
,'XLA_PRODUCT_RULES' ,product_rule_code
,'XLA_LINE_DEFINITIONS' ,line_definition_code
,'XLA_ACCT_LINE_TYPES' ,accounting_line_code
,'XLA_DESCRIPTIONS' ,description_code
,'XLA_SEG_RULES' ,segment_rule_code
,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_code
,'XLA_ANALYTICAL_DTLS' ,analytical_detail_code
,'XLA_MAPPING_SETS' ,mapping_set_code) code
, count(*)
FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = p_staging_context_code
AND table_name IN ('XLA_PRODUCT_RULES'
,'XLA_LINE_DEFINITIONS'
,'XLA_ACCT_LINE_TYPES'
,'XLA_DESCRIPTIONS'
,'XLA_SEG_RULES'
,'XLA_ANALYTICAL_HDRS'
,'XLA_ANALYTICAL_DTLS'
,'XLA_MAPPING_SETS')
GROUP BY
table_name
, DECODE(table_name
,'XLA_PRODUCT_RULES' ,product_rule_type_code
,'XLA_LINE_DEFINITIONS' ,event_class_code||C_CHAR||
event_type_code||C_CHAR||
line_definition_owner_code
,'XLA_ACCT_LINE_TYPES' ,event_class_code||C_CHAR||
accounting_line_type_code
,'XLA_DESCRIPTIONS' ,description_type_code
,'XLA_SEG_RULES' ,segment_rule_type_code
,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_type_code
,'XLA_ANALYTICAL_DTLS' ,analytical_criterion_type_code||C_CHAR||
analytical_criterion_code
,'XLA_MAPPING_SETS' ,NULL)
, DECODE(table_name
,'XLA_PRODUCT_RULES' ,product_rule_code
,'XLA_LINE_DEFINITIONS' ,line_definition_code
,'XLA_ACCT_LINE_TYPES' ,accounting_line_code
,'XLA_DESCRIPTIONS' ,description_code
,'XLA_SEG_RULES' ,segment_rule_code
,'XLA_ANALYTICAL_HDRS' ,analytical_criterion_code
,'XLA_ANALYTICAL_DTLS' ,analytical_detail_code
,'XLA_MAPPING_SETS' ,mapping_set_code)
HAVING count(*) > 1;
SELECT xal.value_ccid_id_flex_struct_code id_flex_struct_code
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_id_flex_structures fif
ON fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_structure_code = xal.value_ccid_id_flex_struct_code
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.value_ccid_id_flex_struct_code IS NOT NULL
AND fif.id_flex_structure_code IS NULL
UNION
SELECT xal.trans_coa_id_flex_struct_code
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_id_flex_structures fif
ON fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_structure_code = xal.trans_coa_id_flex_struct_code
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.trans_coa_id_flex_struct_code IS NOT NULL
AND fif.id_flex_structure_code IS NULL
UNION
SELECT xal.acct_coa_id_flex_struct_code
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_id_flex_structures fif
ON fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_structure_code = xal.acct_coa_id_flex_struct_code
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.acct_coa_id_flex_struct_code IS NOT NULL
AND fif.id_flex_structure_code IS NULL;
SELECT xal.flex_value_set_name
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_flex_value_sets val
ON val.flex_value_set_name = xal.flex_value_set_name
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.flex_value_set_name IS NOT NULL
AND val.flex_value_set_id IS NULL;
SELECT xal.source_app_short_name app_short_name
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_application fap
ON fap.application_short_name = xal.source_app_short_name
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.source_app_short_name IS NOT NULL
AND fap.application_id IS NULL
UNION
SELECT xal.value_source_app_short_name
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_application fap
ON fap.application_short_name = xal.value_source_app_short_name
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.value_source_app_short_name IS NOT NULL
AND fap.application_id IS NULL
UNION
SELECT xal.view_app_short_name
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_application fap
ON fap.application_short_name = xal.view_app_short_name
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.view_app_short_name IS NOT NULL
AND fap.application_id IS NULL
UNION
SELECT xal.application_short_name
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_application fap
ON fap.application_short_name = xal.application_short_name
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.application_short_name IS NOT NULL
AND fap.application_id IS NULL
UNION
SELECT xal.input_source_app_short_name
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_application fap
ON fap.application_short_name = xal.input_source_app_short_name
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.input_source_app_short_name IS NOT NULL
AND fap.application_id IS NULL
UNION
SELECT xal.value_segment_rule_appl_sn
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_application fap
ON fap.application_short_name = xal.value_segment_rule_appl_sn
WHERE xal.staging_amb_context_code = p_staging_context_code
AND xal.value_segment_rule_appl_sn IS NOT NULL
AND fap.application_id IS NULL;
DELETE FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = p_staging_context_code
AND upper(table_name) = ('XLA_ANALYTICAL_HDRS')
AND analytical_criterion_code = i.analytical_criterion_code
AND rowid NOT IN (select max(rowid) from xla_aad_loader_defns_t
where staging_amb_context_code = p_staging_context_code
and upper(table_name) = ('XLA_ANALYTICAL_HDRS')
and analytical_criterion_code=i.analytical_criterion_code
group by analytical_criterion_code);
DELETE FROM xla_aad_loader_defns_t
WHERE staging_amb_context_code = p_staging_context_code
AND upper(table_name) = ('XLA_ANALYTICAL_SOURCES')
AND analytical_criterion_type_code = i.analytical_criterion_type_code
AND analytical_criterion_code = i.analytical_criterion_code
AND event_class_code = i.event_class_code
AND rowid NOT IN (select max(rowid) from xla_aad_loader_defns_t
where staging_amb_context_code = p_staging_context_code
and upper(table_name) = ('XLA_ANALYTICAL_SOURCES')
and analytical_criterion_type_code=i.analytical_criterion_type_code
and analytical_criterion_code = i.analytical_criterion_code
and event_class_code = i.event_class_code
group by analytical_criterion_type_code,analytical_criterion_code,event_class_code);
INSERT INTO xla_descriptions_b
(application_id
,amb_context_code
,description_type_code
,description_code
,transaction_coa_id
,enabled_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,description_type_code
,description_code
,flex.id_flex_num
,i.enabled_flag
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t i
,fnd_id_flex_structures flex
WHERE flex.application_id(+) = 101
AND flex.id_flex_code(+) = 'GL#'
AND flex.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
AND table_name = 'XLA_DESCRIPTIONS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_DESCRIPTIONS_B) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_descriptions_tl
(application_id
,amb_context_code
,description_type_code
,description_code
,language
,name
,description
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,description_type_code
,description_code
,fl.language_code
,name
,description
,USERENV('LANG')
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_languages fl
WHERE xal.table_name = 'XLA_DESCRIPTIONS'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_DESCRIPTIONS_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_desc_priorities
(application_id
,amb_context_code
,description_type_code
,description_code
,description_prio_id
,user_sequence
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,description_type_code
,description_code
,xla_desc_priorities_s.nextval
,priority_num
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_DESC_PRIORITIES'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_DESC_PRIORITIES) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_descript_details_b
(amb_context_code
,description_detail_id
,description_prio_id
,user_sequence
,value_type_code
,source_application_id
,source_type_code
,source_code
,flexfield_segment_code
,display_description_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,xla_descript_details_s.nextval
,p.description_prio_id
,xal.user_sequence
,value_type_code
,fap.application_id
,source_type_code
,source_code
,flexfield_segment_code
,display_description_flag
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,xla_desc_priorities p
,fnd_application fap
WHERE fap.application_short_name(+) = xal.source_app_short_name
AND p.user_sequence = xal.priority_num
AND p.description_type_code = xal.description_type_code
AND p.description_code = xal.description_code
AND p.amb_context_code = p_staging_context_code
AND p.application_id = p_application_id
AND table_name = 'XLA_DESCRIPT_DETAILS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_DESCRIPT_DETAILS_B) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_descript_details_tl
(amb_context_code
,description_detail_id
,language
,literal
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,xdd.description_detail_id
,fl.language_code
,literal
,USERENV('LANG')
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,xla_descript_details_b xdd
,xla_desc_priorities xdp
,fnd_languages fl
WHERE xdd.description_prio_id = xdp.description_prio_id
AND xdd.user_sequence = xal.user_sequence
AND xdp.user_sequence = xal.priority_num
AND xdp.description_type_code = xal.description_type_code
AND xdp.description_code = xal.description_code
AND xdp.amb_context_code = p_staging_context_code
AND xdp.application_id = p_application_id
AND xal.table_name = 'XLA_DESCRIPT_DETAILS'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_DESCRIPT_DETAILS_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_conditions
(amb_context_code
,condition_id
,user_sequence
,application_id
,description_prio_id
,bracket_left_code
,bracket_right_code
,value_type_code
,source_application_id
,source_type_code
,source_code
,flexfield_segment_code
,value_flexfield_segment_code
,value_source_application_id
,value_source_type_code
,value_source_code
,value_constant
,line_operator_code
,logical_operator_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,xla_conditions_s.nextval
,xal.condition_num
,p_application_id
,description_prio_id
,bracket_left_code
,bracket_right_code
,value_type_code
,fap.application_id
,source_type_code
,source_code
,flexfield_segment_code
,value_flexfield_segment_code
,fap2.application_id
,value_source_type_code
,value_source_code
,value_constant
,line_operator_code
,logical_operator_code
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,xla_desc_priorities p
,fnd_application fap
,fnd_application fap2
WHERE fap.application_short_name(+) = xal.source_app_short_name
AND fap2.application_short_name(+)= xal.value_source_app_short_name
AND p.user_sequence = xal.priority_num
AND p.description_type_code = xal.description_type_code
AND p.description_code = xal.description_code
AND p.amb_context_code = p_staging_context_code
AND p.application_id = p_application_id
AND table_name = 'XLA_DESC_CONDITIONS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_DESC_CONDITIONS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
SELECT DISTINCT
xal.mapping_set_code
,fif.id_flex_num
,fif.id_flex_structure_code
,xal.value_ccid_segment1
,xal.value_ccid_segment2
,xal.value_ccid_segment3
,xal.value_ccid_segment4
,xal.value_ccid_segment5
,xal.value_ccid_segment6
,xal.value_ccid_segment7
,xal.value_ccid_segment8
,xal.value_ccid_segment9
,xal.value_ccid_segment10
,xal.value_ccid_segment11
,xal.value_ccid_segment12
,xal.value_ccid_segment13
,xal.value_ccid_segment14
,xal.value_ccid_segment15
,xal.value_ccid_segment16
,xal.value_ccid_segment17
,xal.value_ccid_segment18
,xal.value_ccid_segment19
,xal.value_ccid_segment20
,xal.value_ccid_segment21
,xal.value_ccid_segment22
,xal.value_ccid_segment23
,xal.value_ccid_segment24
,xal.value_ccid_segment25
,xal.value_ccid_segment26
,xal.value_ccid_segment27
,xal.value_ccid_segment28
,xal.value_ccid_segment29
,xal.value_ccid_segment30
FROM xla_aad_loader_defns_t xal
,fnd_id_flex_structures fif
WHERE xal.table_name = 'XLA_MAPPING_SET_VALUES'
AND xal.staging_amb_context_code = p_staging_context_code
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_structure_code = xal.value_ccid_id_flex_struct_code;
SELECT name
FROM xla_mapping_sets_tl
WHERE mapping_set_code = p_mapping_set_code
AND language = USERENV('LANG');
INSERT INTO xla_mapping_sets_b
(amb_context_code
,mapping_set_code
,accounting_coa_id
,value_set_id
,flexfield_assign_mode_code
,flexfield_segment_code
,enabled_flag
,view_application_id
,lookup_type
,version_num
,updated_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,mapping_set_code
,flex.id_flex_num
,val.flex_value_set_id
,flexfield_assign_mode_code
,flexfield_segment_code
,xal.enabled_flag
,fap.application_id
,lookup_type
,NVL(version_num,1)
,'N'
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_application fap
,fnd_id_flex_structures flex
,fnd_flex_value_sets val
WHERE fap.application_short_name(+) = xal.view_app_short_name
AND val.flex_value_set_name(+) = xal.flex_value_set_name
AND flex.id_flex_code = 'GL#'
AND flex.application_id = 101
AND flex.id_flex_structure_code = xal.acct_coa_id_flex_struct_code
AND table_name = 'XLA_MAPPING_SETS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_MAPPING_SETS_B) = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_mapping_sets_tl
(amb_context_code
,mapping_set_code
,language
,name
,description
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,mapping_set_code
,fl.language_code
,name
,description
,USERENV('LANG')
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_languages fl
WHERE xal.table_name = 'XLA_MAPPING_SETS'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_MAPPING_SETS_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
UPDATE xla_aad_loader_defns_t
SET value_code_combination_id = l_code_combination_id
WHERE staging_amb_context_code = p_staging_context_code
AND mapping_set_code = l_ccid.mapping_set_code
AND value_ccid_id_flex_struct_code = l_ccid.id_flex_structure_code
AND nvl(value_ccid_segment1,C_CHAR) = nvl(l_ccid.value_ccid_segment1,C_CHAR)
AND nvl(value_ccid_segment2,C_CHAR) = nvl(l_ccid.value_ccid_segment2,C_CHAR)
AND nvl(value_ccid_segment3,C_CHAR) = nvl(l_ccid.value_ccid_segment3,C_CHAR)
AND nvl(value_ccid_segment4,C_CHAR) = nvl(l_ccid.value_ccid_segment4,C_CHAR)
AND nvl(value_ccid_segment5,C_CHAR) = nvl(l_ccid.value_ccid_segment5,C_CHAR)
AND nvl(value_ccid_segment6,C_CHAR) = nvl(l_ccid.value_ccid_segment6,C_CHAR)
AND nvl(value_ccid_segment7,C_CHAR) = nvl(l_ccid.value_ccid_segment7,C_CHAR)
AND nvl(value_ccid_segment8,C_CHAR) = nvl(l_ccid.value_ccid_segment8,C_CHAR)
AND nvl(value_ccid_segment9,C_CHAR) = nvl(l_ccid.value_ccid_segment9,C_CHAR)
AND nvl(value_ccid_segment10,C_CHAR) = nvl(l_ccid.value_ccid_segment10,C_CHAR)
AND nvl(value_ccid_segment11,C_CHAR) = nvl(l_ccid.value_ccid_segment11,C_CHAR)
AND nvl(value_ccid_segment12,C_CHAR) = nvl(l_ccid.value_ccid_segment12,C_CHAR)
AND nvl(value_ccid_segment13,C_CHAR) = nvl(l_ccid.value_ccid_segment13,C_CHAR)
AND nvl(value_ccid_segment14,C_CHAR) = nvl(l_ccid.value_ccid_segment14,C_CHAR)
AND nvl(value_ccid_segment15,C_CHAR) = nvl(l_ccid.value_ccid_segment15,C_CHAR)
AND nvl(value_ccid_segment16,C_CHAR) = nvl(l_ccid.value_ccid_segment16,C_CHAR)
AND nvl(value_ccid_segment17,C_CHAR) = nvl(l_ccid.value_ccid_segment17,C_CHAR)
AND nvl(value_ccid_segment18,C_CHAR) = nvl(l_ccid.value_ccid_segment18,C_CHAR)
AND nvl(value_ccid_segment19,C_CHAR) = nvl(l_ccid.value_ccid_segment19,C_CHAR)
AND nvl(value_ccid_segment20,C_CHAR) = nvl(l_ccid.value_ccid_segment20,C_CHAR)
AND nvl(value_ccid_segment21,C_CHAR) = nvl(l_ccid.value_ccid_segment21,C_CHAR)
AND nvl(value_ccid_segment22,C_CHAR) = nvl(l_ccid.value_ccid_segment22,C_CHAR)
AND nvl(value_ccid_segment23,C_CHAR) = nvl(l_ccid.value_ccid_segment23,C_CHAR)
AND nvl(value_ccid_segment24,C_CHAR) = nvl(l_ccid.value_ccid_segment24,C_CHAR)
AND nvl(value_ccid_segment25,C_CHAR) = nvl(l_ccid.value_ccid_segment25,C_CHAR)
AND nvl(value_ccid_segment26,C_CHAR) = nvl(l_ccid.value_ccid_segment26,C_CHAR)
AND nvl(value_ccid_segment27,C_CHAR) = nvl(l_ccid.value_ccid_segment27,C_CHAR)
AND nvl(value_ccid_segment28,C_CHAR) = nvl(l_ccid.value_ccid_segment28,C_CHAR)
AND nvl(value_ccid_segment29,C_CHAR) = nvl(l_ccid.value_ccid_segment29,C_CHAR)
AND nvl(value_ccid_segment30,C_CHAR) = nvl(l_ccid.value_ccid_segment30,C_CHAR);
INSERT INTO xla_mapping_set_values
(mapping_set_value_id
,amb_context_code
,mapping_set_code
,value_constant
,value_code_combination_id
,effective_date_from
,effective_date_to
,enabled_flag
,input_value_type_code
,input_value_constant
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
xla_mapping_set_values_s.nextval
,p_staging_context_code
,mapping_set_code
,value_constant
,value_code_combination_id
,effective_date_from
,effective_date_to
,enabled_flag
,input_value_type_code
,input_value_constant
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
WHERE table_name = 'XLA_MAPPING_SET_VALUES'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_MAPPING_SET_VALUES) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_analytical_hdrs_b
(amb_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,application_id
,balancing_flag
,display_order
,enabled_flag
,year_end_carry_forward_code
,display_in_inquiries_flag
,criterion_value_code
,version_num
,updated_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,fap.application_id
,balancing_flag
,display_order
,enabled_flag
,year_end_carry_forward_code
,display_in_inquiries_flag
,criterion_value_code
,NVL(version_num,1)
,'N'
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_application fap
WHERE fap.application_short_name(+) = xal.application_short_name
AND table_name = 'XLA_ANALYTICAL_HDRS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_ANALYTICAL_HDRS_B) = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_analytical_hdrs_tl
(amb_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,language
,name
,description
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,fl.language_code
,name
,description
,USERENV('LANG')
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_languages fl
WHERE xal.table_name = 'XLA_ANALYTICAL_HDRS'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_ANALYTICAL_HDRS_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_analytical_dtls_b
(amb_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,analytical_detail_code
,data_type_code
,grouping_order
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,analytical_detail_code
,data_type_code
,grouping_order
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
WHERE table_name = 'XLA_ANALYTICAL_DTLS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_ANALYTICAL_DTLS_B) = '||l_num_rows,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_analytical_dtls_tl
(amb_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,analytical_detail_code
,language
,name
,description
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,analytical_detail_code
,fl.language_code
,name
,description
,USERENV('LANG')
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_languages fl
WHERE xal.table_name = 'XLA_ANALYTICAL_DTLS'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_ANALYTICAL_DTLS_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_analytical_sources
(amb_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,analytical_detail_code
,entity_code
,event_class_code
,application_id
,source_code
,source_type_code
,source_application_id
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,analytical_criterion_type_code
,analytical_criterion_code
,analytical_detail_code
,entity_code
,event_class_code
,fap.application_id
,source_code
,source_type_code
,fap2.application_id
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_application fap
,fnd_application fap2
WHERE fap.application_short_name = xal.application_short_name
AND fap2.application_short_name = xal.source_app_short_name
AND table_name = 'XLA_ANALYTICAL_SOURCES'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_ANALYTICAL_SOURCES) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
SELECT DISTINCT
xal.segment_rule_type_code
,xal.segment_rule_code
,xal.user_sequence
,fif.id_flex_num
,fif.id_flex_structure_code
,xal.value_ccid_segment1
,xal.value_ccid_segment2
,xal.value_ccid_segment3
,xal.value_ccid_segment4
,xal.value_ccid_segment5
,xal.value_ccid_segment6
,xal.value_ccid_segment7
,xal.value_ccid_segment8
,xal.value_ccid_segment9
,xal.value_ccid_segment10
,xal.value_ccid_segment11
,xal.value_ccid_segment12
,xal.value_ccid_segment13
,xal.value_ccid_segment14
,xal.value_ccid_segment15
,xal.value_ccid_segment16
,xal.value_ccid_segment17
,xal.value_ccid_segment18
,xal.value_ccid_segment19
,xal.value_ccid_segment20
,xal.value_ccid_segment21
,xal.value_ccid_segment22
,xal.value_ccid_segment23
,xal.value_ccid_segment24
,xal.value_ccid_segment25
,xal.value_ccid_segment26
,xal.value_ccid_segment27
,xal.value_ccid_segment28
,xal.value_ccid_segment29
,xal.value_ccid_segment30
FROM xla_aad_loader_defns_t xal
,fnd_id_flex_structures fif
WHERE xal.table_name = 'XLA_SEG_RULE_DETAILS'
AND xal.staging_amb_context_code = p_staging_context_code
AND fif.application_id = 101
AND fif.id_flex_code = 'GL#'
AND fif.id_flex_structure_code = xal.value_ccid_id_flex_struct_code;
SELECT xsrt.name
,xlk.meaning seg_rule_owner
FROM xla_seg_rules_tl xsrt
,xla_lookups xlk
WHERE xsrt.segment_rule_type_code = p_seg_rule_type_code
AND xsrt.segment_rule_code = p_seg_rule_code
AND xsrt.amb_context_code = p_staging_context_code
AND xsrt.application_id = p_application_id
AND xsrt.language = USERENV('LANG')
AND xlk.lookup_type = 'XLA_OWNER_TYPE'
AND xlk.lookup_code = p_seg_rule_type_code;
INSERT INTO xla_seg_rules_b
(application_id
,amb_context_code
,segment_rule_type_code
,segment_rule_code
,transaction_coa_id
,accounting_coa_id
,flexfield_assign_mode_code
,flexfield_segment_code
,flex_value_set_id
,enabled_flag
,version_num
,updated_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,segment_rule_type_code
,segment_rule_code
,fift.id_flex_num
,fifa.id_flex_num
,flexfield_assign_mode_code
,flexfield_segment_code
,val.flex_value_set_id
,xal.enabled_flag
,NVL(xal.version_num,1)
,'N'
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_id_flex_structures fift
,fnd_id_flex_structures fifa
,fnd_flex_value_sets val
WHERE fift.application_id(+) = 101
AND fift.id_flex_code(+) = 'GL#'
AND fift.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
AND fifa.application_id(+) = 101
AND fifa.id_flex_code(+) = 'GL#'
AND fifa.id_flex_structure_code(+) = acct_coa_id_flex_struct_code
AND val.flex_value_set_name(+) = xal.flex_value_set_name
AND table_name = 'XLA_SEG_RULES'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_SEG_RULES_B) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_seg_rules_tl
(application_id
,amb_context_code
,segment_rule_type_code
,segment_rule_code
,language
,name
,description
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,segment_rule_type_code
,segment_rule_code
,fl.language_code
,name
,description
,USERENV('LANG')
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_languages fl
WHERE xal.table_name = 'XLA_SEG_RULES'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_SEG_RULES_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_seg_rule_details
(segment_rule_detail_id
,application_id
,amb_context_code
,segment_rule_type_code
,segment_rule_code
,user_sequence
,value_type_code
,value_source_application_id
,value_source_type_code
,value_source_code
,value_constant
,value_mapping_set_code
,value_flexfield_segment_code
,value_adr_version_num
,value_segment_rule_appl_id
,value_segment_rule_type_code
,value_segment_rule_code
,input_source_application_id
,input_source_type_code
,input_source_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
xla_seg_rule_details_s.nextval
,p_application_id
,p_staging_context_code
,segment_rule_type_code
,segment_rule_code
,user_sequence
,value_type_code
,fap.application_id
,value_source_type_code
,value_source_code
,value_constant
,value_mapping_set_code
,value_flexfield_segment_code
,NVL(value_adr_version_num,0)
,NVL(fap3.application_id,
CASE WHEN value_segment_rule_type_code IS NOT NULL
THEN p_application_id
ELSE NULL END)
,value_segment_rule_type_code
,value_segment_rule_code
,fap2.application_id
,input_source_type_code
,input_source_code
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
LEFT OUTER JOIN fnd_application fap
ON fap.application_short_name = xal.value_source_app_short_name
LEFT OUTER JOIN fnd_application fap2
ON fap2.application_short_name = xal.input_source_app_short_name
LEFT OUTER JOIN fnd_application fap3
ON fap3.application_short_name = xal.value_segment_rule_appl_sn
WHERE table_name = 'XLA_SEG_RULE_DETAILS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_SEG_RULE_DETAILS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
UPDATE xla_seg_rule_details
SET value_code_combination_id = l_code_combination_id
WHERE amb_context_code = p_staging_context_code
AND application_id = p_application_id
AND segment_rule_type_code = l_ccid.segment_rule_type_code
AND segment_rule_code = l_ccid.segment_rule_code
AND user_sequence = l_ccid.user_sequence;
INSERT INTO xla_conditions
(amb_context_code
,condition_id
,user_sequence
,application_id
,segment_rule_detail_id
,bracket_left_code
,bracket_right_code
,value_type_code
,source_application_id
,source_type_code
,source_code
,flexfield_segment_code
,value_flexfield_segment_code
,value_source_application_id
,value_source_type_code
,value_source_code
,value_constant
,line_operator_code
,logical_operator_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,xla_conditions_s.nextval
,xal.condition_num
,p_application_id
,segment_rule_detail_id
,xal.bracket_left_code
,xal.bracket_right_code
,xal.value_type_code
,fap.application_id
,xal.source_type_code
,xal.source_code
,xal.flexfield_segment_code
,xal.value_flexfield_segment_code
,fap2.application_id
,xal.value_source_type_code
,xal.value_source_code
,xal.value_constant
,xal.line_operator_code
,xal.logical_operator_code
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,xla_seg_rule_details d
,fnd_application fap
,fnd_application fap2
WHERE fap.application_short_name(+) = xal.source_app_short_name
AND fap2.application_short_name(+)= xal.value_source_app_short_name
AND d.user_sequence = xal.user_sequence
AND d.segment_rule_type_code = xal.segment_rule_type_code
AND d.segment_rule_code = xal.segment_rule_code
AND d.amb_context_code = p_staging_context_code
AND d.application_id = p_application_id
AND table_name = 'XLA_ADR_CONDITIONS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_ADR_CONDITIONS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_acct_line_types_b
(application_id
,amb_context_code
,entity_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,transaction_coa_id
,accounting_entry_type_code
,natural_side_code
,gl_transfer_mode_code
,switch_side_flag
,gain_or_loss_flag
,merge_duplicate_code
,enabled_flag
,accounting_class_code
,business_method_code
,business_class_code
,rounding_class_code
,encumbrance_type_id
,mpa_option_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,xal.entity_code
,xal.event_class_code
,xal.accounting_line_type_code
,xal.accounting_line_code
,flex.id_flex_num
,xal.accounting_entry_type_code
,xal.natural_side_code
,xal.gl_transfer_mode_code
,xal.switch_side_flag
,xal.inherit_desc_flag
,xal.merge_duplicate_code
,xal.enabled_flag
,xal.accounting_class_code
,NVL(xal.business_method_code,'NONE')
,xal.business_class_code
,NVL(xal.rounding_class_code,xal.accounting_class_code)
,get.encumbrance_type_id
,NVL(xal.mpa_option_code,'NONE')
,nvl(to_date(xal.orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(xal.owner)
,nvl(to_date(xal.orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(xal.owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_id_flex_structures flex
,gl_encumbrance_types get
WHERE flex.application_id(+) = 101
AND flex.id_flex_code(+) = 'GL#'
AND flex.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
AND get.encumbrance_type_key(+) = xal.encumbrance_type
AND table_name = 'XLA_ACCT_LINE_TYPES'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_ACCT_LINE_TYPES_B) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_acct_line_types_tl
(application_id
,amb_context_code
,entity_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,language
,name
,description
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,entity_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,fl.language_code
,name
,description
,USERENV('LANG')
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_languages fl
WHERE xal.table_name = 'XLA_ACCT_LINE_TYPES'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_ACCT_LINE_TYPES_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_jlt_acct_attrs
(application_id
,amb_context_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,accounting_attribute_code
,source_application_id
,source_type_code
,source_code
,event_class_default_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,accounting_attribute_code
,fap.application_id
,source_type_code
,source_code
,event_class_default_flag
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_application fap
WHERE fap.application_short_name(+) = xal.source_app_short_name
AND table_name = 'XLA_JLT_ACCT_ATTRS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_JLT_ACCT_ATTRS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_conditions
(amb_context_code
,condition_id
,user_sequence
,application_id
,entity_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,bracket_left_code
,bracket_right_code
,value_type_code
,source_application_id
,source_type_code
,source_code
,flexfield_segment_code
,value_flexfield_segment_code
,value_source_application_id
,value_source_type_code
,value_source_code
,value_constant
,line_operator_code
,logical_operator_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,xla_conditions_s.nextval
,xal.condition_num
,p_application_id
,entity_code
,event_class_code
,accounting_line_type_code
,accounting_line_code
,bracket_left_code
,bracket_right_code
,value_type_code
,fap.application_id
,source_type_code
,source_code
,flexfield_segment_code
,value_flexfield_segment_code
,fap2.application_id
,value_source_type_code
,value_source_code
,value_constant
,line_operator_code
,logical_operator_code
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_application fap
,fnd_application fap2
WHERE fap.application_short_name(+) = xal.source_app_short_name
AND fap2.application_short_name(+)= xal.value_source_app_short_name
AND table_name = 'XLA_JLT_CONDITIONS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_JLT_CONDITIONS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_line_definitions_b
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,transaction_coa_id
,accounting_coa_id
,enabled_flag
,validation_status_code
,budgetary_control_flag
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,fift.id_flex_num
,fifa.id_flex_num
,xal.enabled_flag
,'N'
,budgetary_control_flag
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_id_flex_structures fift
,fnd_id_flex_structures fifa
WHERE fift.application_id(+) = 101
AND fift.id_flex_code(+) = 'GL#'
AND fift.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
AND fifa.application_id(+) = 101
AND fifa.id_flex_code(+) = 'GL#'
AND fifa.id_flex_structure_code(+) = acct_coa_id_flex_struct_code
AND table_name = 'XLA_LINE_DEFINITIONS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_LINE_DEFINITIONS_B) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_line_definitions_tl
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,language
,name
,description
,source_lang
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,fl.language_code
,name
,description
,USERENV('LANG')
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_languages fl
WHERE xal.table_name = 'XLA_LINE_DEFINITIONS'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_LINE_DEFINITIONS_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_line_defn_jlt_assgns
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,description_type_code
,description_code
,active_flag
,inherit_desc_flag
,mpa_header_desc_type_code
,mpa_header_desc_code
,mpa_num_je_code
,mpa_gl_dates_code
,mpa_proration_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,description_type_code
,description_code
,active_flag
,NVL(inherit_desc_flag,'N')
,mpa_header_desc_type_code
,mpa_header_desc_code
,mpa_num_je_code
,mpa_gl_dates_code
,mpa_proration_code
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_LINE_DEFN_JLT_ASSGNS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_LINE_DEFN_JLT_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_line_defn_adr_assgns
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,flexfield_segment_code
,adr_version_num
,segment_rule_appl_id
,segment_rule_type_code
,segment_rule_code
,inherit_adr_flag
,side_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,flexfield_segment_code
,NVL(adr_version_num,0)
,NVL(fap.application_id,
CASE WHEN segment_rule_type_code IS NOT NULL
THEN p_application_id
ELSE NULL END)
,segment_rule_type_code
,segment_rule_code
,NVL(inherit_adr_flag,'N')
,NVL(side_code,'NA')
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_application fap
WHERE fap.application_short_name(+) = xal.segment_rule_appl_sn
AND xal.table_name = 'XLA_LINE_DEFN_ADR_ASSGNS'
AND xal.staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_LINE_DEFN_ADR_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_line_defn_ac_assgns
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,analytical_criterion_code
,analytical_criterion_type_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,analytical_criterion_code
,analytical_criterion_type_code
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_LINE_DEFN_AC_ASSGNS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_LINE_DEFN_AC_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_mpa_header_ac_assgns
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,analytical_criterion_code
,analytical_criterion_type_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,analytical_criterion_code
,analytical_criterion_type_code
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_MPA_HEADER_AC_ASSGNS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_MPA_HEADER_AC_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_mpa_jlt_assgns
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,mpa_accounting_line_type_code
,mpa_accounting_line_code
,description_type_code
,description_code
,inherit_desc_flag
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,mpa_accounting_line_type_code
,mpa_accounting_line_code
,description_type_code
,description_code
,inherit_desc_flag
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_MPA_JLT_ASSGNS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_MPA_JLT_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_mpa_jlt_ac_assgns
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,mpa_accounting_line_type_code
,mpa_accounting_line_code
,analytical_criterion_type_code
,analytical_criterion_code
,mpa_inherit_ac_flag
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,mpa_accounting_line_type_code
,mpa_accounting_line_code
,analytical_criterion_type_code
,analytical_criterion_code
,mpa_inherit_ac_flag
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_MPA_JLT_AC_ASSGNS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_MPA_JLT_AC_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_mpa_jlt_adr_assgns
(application_id
,amb_context_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,accounting_line_type_code
,accounting_line_code
,mpa_accounting_line_type_code
,mpa_accounting_line_code
,flexfield_segment_code
,segment_rule_type_code
,segment_rule_code
,segment_rule_appl_id
,inherit_adr_flag
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,xal.event_class_code
,xal.event_type_code
,xal.line_definition_owner_code
,xal.line_definition_code
,xal.accounting_line_type_code
,xal.accounting_line_code
,xal.mpa_accounting_line_type_code
,xal.mpa_accounting_line_code
,xal.flexfield_segment_code
,xal.segment_rule_type_code
,xal.segment_rule_code
,fap.application_id
,xal.inherit_adr_flag
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
, fnd_application fap
WHERE fap.application_short_name(+) = xal.segment_rule_appl_sn
AND table_name = 'XLA_MPA_JLT_ADR_ASSGNS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_MPA_JLT_ADR_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_product_rules_b
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,transaction_coa_id
,accounting_coa_id
,enabled_flag
,product_rule_version
,compile_status_code
,locking_status_flag
,product_rule_hash_id
,version_num
,updated_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,xal.product_rule_type_code
,xal.product_rule_code
,fift.id_flex_num
,fifa.id_flex_num
,xal.enabled_flag
,xal.product_rule_version
,'N'
,xal.locking_status_flag
,xpr.product_rule_hash_id
,NVL(xal.version_num,1)
,'N'
,nvl(to_date(xal.orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(xal.owner)
,nvl(to_date(xal.orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(xal.owner)
,0
FROM xla_aad_loader_defns_t xal
,xla_product_rules_b xpr
,fnd_id_flex_structures fift
,fnd_id_flex_structures fifa
WHERE fift.application_id(+) = 101
AND fift.id_flex_code(+) = 'GL#'
AND fift.id_flex_structure_code(+) = xal.trans_coa_id_flex_struct_code
AND fifa.application_id(+) = 101
AND fifa.id_flex_code(+) = 'GL#'
AND fifa.id_flex_structure_code(+) = xal.acct_coa_id_flex_struct_code
AND xpr.application_id(+) = p_application_id
AND xpr.amb_context_code(+) = p_amb_context_code
AND xpr.product_rule_type_code(+) = xal.product_rule_type_code
AND xpr.product_rule_code(+) = xal.product_rule_code
AND xal.table_name = 'XLA_PRODUCT_RULES'
AND xal.staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_PRODUCT_RULES_B) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_product_rules_tl
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,language
,name
,description
,source_lang
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,product_rule_type_code
,product_rule_code
,fl.language_code
,name
,description
,USERENV('LANG')
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_languages fl
WHERE xal.table_name = 'XLA_PRODUCT_RULES'
AND xal.staging_amb_context_code = p_staging_context_code
AND fl.installed_flag IN ('I', 'B');
trace(p_msg => '# insert (XLA_PRODUCT_RULES_TL) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_prod_acct_headers
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,entity_code
,event_class_code
,event_type_code
,description_type_code
,description_code
,accounting_required_flag
,locking_status_flag
,validation_status_code
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,product_rule_type_code
,product_rule_code
,entity_code
,event_class_code
,event_type_code
,description_type_code
,description_code
,accounting_required_flag
,locking_status_flag
,'N'
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_PROD_ACCT_HEADERS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_PROD_ACCT_HEADERS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_aad_hdr_acct_attrs
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,accounting_attribute_code
,source_application_id
,source_type_code
,source_code
,event_class_default_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,accounting_attribute_code
,fap.application_id
,source_type_code
,source_code
,event_class_default_flag
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_application fap
WHERE fap.application_short_name(+) = xal.source_app_short_name
AND table_name = 'XLA_AAD_HDR_ACCT_ATTRS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_AAD_HDR_ACCT_ATTRS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_aad_line_defn_assgns
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,line_definition_owner_code
,line_definition_code
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_AAD_LINE_DEFN_ASSGNS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_AAD_LINE_DEFN_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_aad_header_ac_assgns
(application_id
,amb_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,analytical_criterion_code
,analytical_criterion_type_code
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_application_id
,p_staging_context_code
,product_rule_type_code
,product_rule_code
,event_class_code
,event_type_code
,analytical_criterion_code
,analytical_criterion_type_code
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_AAD_HEADER_AC_ASSGNS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_AAD_HEADER_AC_ASSGNS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_stage_acctg_methods
(staging_amb_context_code
,accounting_method_type_code
,accounting_method_code
,name
,description
,transaction_coa_id
,accounting_coa_id
,enabled_flag
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,xal.accounting_method_type_code
,xal.accounting_method_code
,xal.name
,xal.description
,fift.id_flex_num
,fifa.id_flex_num
,xal.enabled_flag
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_id_flex_structures fift
,fnd_id_flex_structures fifa
WHERE fift.application_id(+) = 101
AND fift.id_flex_code(+) = 'GL#'
AND fift.id_flex_structure_code(+) = trans_coa_id_flex_struct_code
AND fifa.application_id(+) = 101
AND fifa.id_flex_code(+) = 'GL#'
AND fifa.id_flex_structure_code(+) = acct_coa_id_flex_struct_code
AND table_name = 'XLA_STAGE_ACCTG_METHODS'
AND staging_amb_context_code = p_staging_context_code;
trace(p_msg => '# insert (XLA_STAGING_ACCTG_METHODS) = '||SQL%ROWCOUNT,
p_module => l_log_module,
p_level => C_LEVEL_EVENT);
INSERT INTO xla_acctg_method_rules
(amb_context_code
,accounting_method_type_code
,accounting_method_code
,acctg_method_rule_id
,application_id
,product_rule_type_code
,product_rule_code
,start_date_active
,end_date_active
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,accounting_method_type_code
,accounting_method_code
,xla_acctg_method_rules_s.nextval
,p_application_id
,product_rule_type_code
,product_rule_code
,start_date_active
,end_date_active
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t
WHERE table_name = 'XLA_ACCTG_METHOD_RULES'
AND staging_amb_context_code = p_staging_context_code;
INSERT INTO xla_staging_components_h
(staging_amb_context_code
,component_type_code
,component_owner_code
,component_code
,version_num
,base_version_num
,application_id
,product_rule_version
,version_comment
,leapfrog_flag
,object_version_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
SELECT
p_staging_context_code
,component_type_code
,component_owner_code
,component_code
,version_num
,base_version_num
,NVL(fap.application_id,-1)
,product_rule_version
,version_comment
,leapfrog_flag
,1
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,nvl(to_date(orig_last_update_date, 'YYYY/MM/DD'), sysdate)
,fnd_load_util.owner_id(owner)
,0
FROM xla_aad_loader_defns_t xal
,fnd_application fap
WHERE fap.application_short_name(+) = xal.application_short_name
AND table_name = 'XLA_STAGING_COMPONENTS_H'
AND staging_amb_context_code = p_staging_context_code;