[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT gvcd.dimension_id,
gvcd.value_set_id
BULK COLLECT INTO
l_dimension_vs_id
FROM fem_global_vs_combo_defs gvcd,
fem_ledgers_attr fla,
fem_entities_attr fea,
fem_tab_column_prop ftcp,
fem_tab_columns_b ftcb
WHERE fla.ledger_id = fea.dim_attribute_numeric_member
AND fla.attribute_id = l_ledger_vs_combo_attr
AND fla.version_id = l_ledger_vs_combo_version
AND fea.entity_id = p_entity_id
AND fea.attribute_id = l_entity_ledger_attr
AND fea.version_id = l_entity_ledger_version
AND ftcb.table_name = 'FEM_BALANCES'
AND ftcb.dimension_id = gvcd.dimension_id
AND ftcb.column_name = ftcp.column_name
AND ftcb.column_name <> 'INTERCOMPANY_ID'
AND ftcp.column_property_code = 'PROCESSING_KEY'
AND ftcp.table_name = ftcb.table_name
AND gvcd.global_vs_combo_id = fla.dim_attribute_numeric_member;
SELECT '
SELECT '
SELECT entity_name,
MIN(DECODE(fea.dim_attribute_varchar_member,
'O', 1,
'C', DECODE(fev.entity_id, gcs.parent_entity_id, 4, 2),
'E', 3)) entity_type
FROM gcs_cons_relationships gcs,
fem_entities_tl fev,
fem_entities_attr fea
WHERE hierarchy_id = p_hierarchy_id
AND child_entity_id = fev.entity_id
--Begin Fix#4198102
AND dominant_parent_flag = 'Y'
AND p_cal_period_end_date BETWEEN gcs.start_date AND NVL(gcs.end_date, p_cal_period_end_date)
--End Fix#4198102
AND fea.attribute_id = l_entity_type_attr_id
AND fea.version_id = l_entity_type_version_id
AND fev.entity_id = fea.entity_id
AND fev.language = p_language_code
AND gcs.parent_entity_id = p_entity_id
GROUP BY entity_name
UNION ALL
SELECT entity_name, 4
FROM fem_entities_tl fev
WHERE entity_id = p_entity_id
AND fev.language = p_language_code
ORDER BY entity_type, entity_name;
SELECT flit.line_item_name,
fet.entity_name,
SUM (fb.ytd_balance_e) balance,
MIN (DECODE (fea.dim_attribute_varchar_member,
'O', 1,
'C', DECODE (fet.entity_id, geca.entity_id, 4, 2),
'E', 3)) entity_type,
flit.line_item_id,
fet.entity_id,
decode(fea.dim_attribute_varchar_member,
'O', decode(fli_parents.line_item_id,
null, 'Y',
'N'),
'N') is_drillable
FROM fem_balances fb,
gcs_dataset_codes gdc,
fem_ln_items_tl flit,
(select flib.line_item_id
from fem_ln_items_b flib
where exists
(select 1
from fem_ln_items_hier flih,
gcs_system_options gso,
fem_object_definition_b odb,
fem_global_vs_combo_defs gvscd
where odb.object_id = gso.ln_item_hierarchy_obj_id
and p_cal_period_end_date BETWEEN odb.effective_start_date and odb.effective_end_date
and gvscd.global_vs_combo_id = gso.fch_global_vs_combo_id
and gvscd.dimension_id = 14
and flih.hierarchy_obj_def_id = odb.object_definition_id
and flih.parent_id = flib.line_item_id
and flih.child_id <> flih.parent_id
and flih.parent_value_set_id = gvscd.value_set_id
and flih.child_value_set_id = gvscd.value_set_id
)
) fli_parents,
gcs_entity_cons_attrs geca,
fem_entities_tl fet,
fem_entities_attr fea
WHERE fb.source_system_code = 70
AND fb.dataset_code = gdc.dataset_code
AND gdc.balance_type_code = p_balance_type_code
AND fb.line_item_id = flit.line_item_id
AND flit.language = p_language_code
AND geca.hierarchy_id = p_hierarchy_id
AND geca.hierarchy_id = gdc.hierarchy_id
AND fb.currency_code = p_currency_code
AND fb.currency_code = geca.currency_code
AND geca.entity_id = p_entity_id
AND fb.cal_period_id = p_cal_period_id
AND fb.entity_id = fet.entity_id
AND fet.language = p_language_code
AND fb.entity_id = fea.entity_id
AND fea.attribute_id = l_entity_type_attr_id
AND fea.version_id = l_entity_type_version_id
AND ( fb.entity_id = geca.entity_id
OR
EXISTS ( SELECT 1
FROM gcs_cons_relationships gcr
WHERE gcr.parent_entity_id = geca.entity_id
AND gcr.hierarchy_id = geca.hierarchy_id
AND gcr.dominant_parent_flag = 'Y'
AND p_cal_period_end_date BETWEEN gcr.start_date AND NVL(gcr.end_date, p_cal_period_end_date)
AND gcr.child_entity_id = fb.entity_id)
)
AND fli_parents.line_item_id (+)= flit.line_item_id
GROUP BY fet.entity_name, flit.line_item_name,
fet.entity_id, flit.line_item_id,
fea.dim_attribute_varchar_member, fli_parents.line_item_id
ORDER BY line_item_name, entity_type, entity_name;
SELECT date_assign_value
INTO l_cal_period_end_date
FROM fem_cal_periods_attr
WHERE cal_period_id = p_cal_period_id
AND attribute_id = l_cal_period_attr_id
AND version_id = l_cal_period_version_id;
SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('I','B');
SELECT 1
FROM gcs_xml_files
WHERE XML_FILE_ID = p_entry_id
AND XML_FILE_TYPE = p_xml_file_type
AND LANGUAGE = p_language_code;
SELECT gcer.run_name,
fcpt.cal_period_name,
flv.meaning balance_type,
gcer.start_time,
gcer.end_time,
fct.name currency_name,
ght.hierarchy_name,
ght.hierarchy_id,
fcpt.cal_period_id
INTO l_cmtb_header_data.run_name,
l_cmtb_header_data.cal_period_name,
l_cmtb_header_data.balance_type,
l_cmtb_header_data.start_time,
l_cmtb_header_data.end_time,
l_cmtb_header_data.currency_name,
l_cmtb_header_data.hierarchy_name,
l_cmtb_header_data.hierarchy_id,
l_cmtb_header_data.cal_period_id
FROM gcs_cons_eng_runs gcer,
gcs_hierarchies_tl ght,
fem_cal_periods_tl fcpt,
fnd_currencies_tl fct,
fnd_lookup_values flv
WHERE gcer.cal_period_id = fcpt.cal_period_id
AND fct.currency_code = p_currency_code
AND fct.language = v_languages.language_code
AND gcer.balance_type_code = flv.lookup_code
AND flv.lookup_type = 'BALANCE_TYPE_CODE'
AND flv.language = v_languages.language_code
AND fcpt.language = v_languages.language_code
AND gcer.hierarchy_id = p_hierarchy_id
AND gcer.hierarchy_id = ght.hierarchy_id
AND ght.language = v_languages.language_code
AND gcer.RUN_ENTITY_ID = p_entity_id
AND gcer.BALANCE_TYPE_CODE = p_balance_type_code
AND gcer.CAL_PERIOD_ID = p_cal_period_id
AND gcer.MOST_RECENT_FLAG = 'Y'
AND rownum <= 1;
insert into gcs_xml_files ( xml_file_id,
xml_file_type,
language,
xml_data,
last_update_date,
last_updated_by,
creation_date,
created_by,
object_version_number)
values
( p_entry_id,
p_xml_file_type,
v_languages.language_code,
''||new_line||l_cmtb_xml,
sysdate,
0,
sysdate,
0,
0);
g_api, 'Inserted CMTB XML for entry_id: '||p_entry_id
);
update gcs_xml_files
set xml_data = ''||new_line||l_cmtb_xml,
last_update_date = sysdate,
last_updated_by = 0,
object_version_number = object_version_number + 1
where xml_file_id = p_entry_id
and xml_file_type = p_xml_file_type
and language = v_languages.language_code;
g_api, 'Updated CMTB XML for entry_id: '||p_entry_id
);
SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('I','B');
SELECT 1
FROM gcs_xml_files
WHERE xml_file_id = p_entry_id
AND xml_file_type = p_xml_file_type
AND language = p_language_code;
SELECT geh.entry_name,
geh.description,
ghtl.hierarchy_name,
fetl.entity_name,
fctl.name currency_name,
fcptl_start.cal_period_name,
geh.suspense_exceeded_flag,
geh.rule_id,
geh.entity_id,
geh.hierarchy_id,
geh.start_cal_period_id,
geh.balance_type_code,
geh.currency_code,
fea_type.dim_attribute_varchar_member,
geh.category_code
INTO l_entry_header_data.entry_name,
l_entry_header_data.description,
l_entry_header_data.hierarchy_name,
l_entry_header_data.entity_name,
l_entry_header_data.currency_name,
l_entry_header_data.cal_period_name,
l_entry_header_data.suspense_flag,
l_entry_header_data.rule_id,
l_entry_header_data.entity_id,
l_entry_header_data.hierarchy_id,
l_entry_header_data.cal_period_id,
l_entry_header_data.balance_type_code,
l_entry_header_data.currency_code,
l_entry_header_data.entity_type_code,
l_entry_header_data.category_code
FROM gcs_entry_headers geh,
gcs_hierarchies_tl ghtl,
fem_entities_tl fetl,
fnd_currencies_tl fctl,
fem_cal_periods_tl fcptl_start,
fem_cal_periods_tl fcptl_end,
fem_entities_attr fea_type
WHERE geh.entry_id = p_entry_id
AND geh.hierarchy_id = ghtl.hierarchy_id
AND ghtl.language = v_languages.language_code
AND geh.entity_id = fetl.entity_id
AND fctl.language = v_languages.language_code
AND geh.currency_code = fctl.currency_code
AND fetl.language = v_languages.language_code
AND geh.start_cal_period_id = fcptl_start.cal_period_id
AND fcptl_start.language = v_languages.language_code
AND geh.end_cal_period_id = fcptl_end.cal_period_id(+)
AND fcptl_end.language(+) = v_languages.language_code
AND fea_type.entity_id = geh.entity_id
AND fea_type.attribute_id = l_entity_type_attr
AND fea_type.version_id = l_entity_type_version
AND ROWNUM <= 1;
g_api, ' Select statement results: '||
'l_entry_header_data.entry_name =>'||l_entry_header_data.entry_name||
'l_entry_header_data.description =>'||l_entry_header_data.description||
'l_entry_header_data.hierarchy_name =>'||l_entry_header_data.hierarchy_name||
'l_entry_header_data.entity_name =>'||l_entry_header_data.entity_name||
'l_entry_header_data.currency_name =>'||l_entry_header_data.currency_name||
'l_entry_header_data.cal_period_name =>'||l_entry_header_data.cal_period_name||
'l_entry_header_data.suspense_flag =>'||l_entry_header_data.suspense_flag||
'l_entry_header_data.rule_id =>'||l_entry_header_data.rule_id||
'l_entry_header_data.entity_id =>'||l_entry_header_data.entity_id||
'l_entry_header_data.hierarchy_id =>'||l_entry_header_data.hierarchy_id||
'l_entry_header_data.cal_period_id =>'||l_entry_header_data.cal_period_id||
'l_entry_header_data.balance_type_code =>'||l_entry_header_data.balance_type_code||
'l_entry_header_data.currency_code =>'||l_entry_header_data.currency_code
);
l_entry_lines_sql:= gcs_xml_utility_pkg.g_entry_lines_select_stmnt||
' and lang.language_code = '''||
v_languages.language_code||
''' and gel.entry_id = '||
p_entry_id|| ' ORDER BY ' ||
gcs_xml_utility_pkg.g_order_by_stmnt;
retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'ENTRY_LINES' );
l_rp_lines_sql:= gcs_xml_utility_pkg.g_rp_select_stmnt||
' and lang.language_code = '''||
v_languages.language_code||
''' and gel.rule_id = '||
l_entry_header_data.rule_id|| ' ORDER BY ' ||
gcs_xml_utility_pkg.g_order_by_stmnt||
gcs_xml_utility_pkg.g_order_by_stmnt1;
retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'RP_LINES' );
insert into gcs_xml_files
( xml_file_id,
xml_file_type,
language,
xml_data,
xml_execution_data,
last_update_date,
last_updated_by,
creation_date,
created_by,
object_version_number)
values ( p_entry_id,
p_xml_file_type,
v_languages.language_code,
''||new_line||l_entry_xml,
''||new_line||l_rp_lines_xml,
sysdate,
0,
sysdate,
0,
0 );
g_api, 'Inserted Entry XML for entry_id: '||p_entry_id
);
g_api, 'Inserted RP XML for entry_id: '||p_entry_id );
update gcs_xml_files
set xml_data = ''||new_line||l_entry_xml,
xml_execution_data = ''||new_line||l_rp_lines_xml,
last_update_date = sysdate,
last_updated_by = 0,
object_version_number = object_version_number + 1
where xml_file_id = p_entry_id
and xml_file_type = p_xml_file_type
and language = v_languages.language_code;
g_api, 'Updated Entry XML for entry_id: '||p_entry_id
);
g_api, 'Updated RP XML for entry_id: '||p_entry_id );
SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('I','B');
SELECT 1
FROM gcs_xml_files
WHERE xml_file_id = p_load_id
AND xml_file_type = l_xml_file_type
AND language = p_language_code;
SELECT gdsd.load_id,
gdsd.load_name,
gdsd.entity_id,
fet.entity_name,
flv.meaning balance_type,
gdsd.amount_type_code,
amt.meaning amount_type_name,
gdsd.currency_type_code,
curr.meaning curr_type_name,
gdsd.currency_code,
fct.name currency_name,
gdsd.cal_period_id,
fcpt.cal_period_name,
gdsd.measure_type_code,
measure.meaning measure_type_name,
gdsd.start_time,
gdsd.end_time,
gdsd.status_code,
gdsd.balance_type_code,
gdsd.balances_rule_id,
foct.object_name balances_rule_name,
flt.ledger_id,
flt.ledger_name,
fea_srcsys.dim_attribute_numeric_member source_system_code,
fla_comp.dim_attribute_varchar_member entity_currency_code,
fla_gvsc.dim_attribute_numeric_member sub_global_vs_combo_id
INTO l_ds_header_data.load_id,
l_ds_header_data.load_name,
l_ds_header_data.entity_id,
l_ds_header_data.entity_name,
l_ds_header_data.balance_type,
l_ds_header_data.amount_type_code,
l_ds_header_data.amount_type_name,
l_ds_header_data.curr_type_code,
l_ds_header_data.curr_type_name,
l_ds_header_data.currency_code,
l_ds_header_data.currency_name,
l_ds_header_data.cal_period_id,
l_ds_header_data.cal_period_name,
l_ds_header_data.measure_type_code,
l_ds_header_data.measure_type_name,
l_ds_header_data.start_time,
l_ds_header_data.end_time,
l_ds_header_data.status_code,
l_ds_header_data.balance_type_code,
l_ds_header_data.balances_rule_id,
l_ds_header_data.balances_rule_name,
l_ds_header_data.ledger_id,
l_ds_header_data.ledger_name,
l_ds_header_data.source_system_code,
l_ds_header_data.entity_currency_code,
l_ds_header_data.sub_global_vs_combo_id
FROM gcs_data_sub_dtls gdsd,
fem_entities_tl fet,
fnd_currencies_tl fct,
fem_cal_periods_tl fcpt,
fnd_lookup_values flv,
fnd_lookup_values amt,
fnd_lookup_values curr,
fnd_lookup_values measure,
fem_ledgers_tl flt,
fem_entities_attr fea_ledger,
fem_entities_attr fea_srcsys,
fem_object_catalog_tl foct,
fem_ledgers_attr fla_comp,
fem_ledgers_attr fla_gvsc
WHERE gdsd.load_id = p_load_id
AND gdsd.entity_id = fet.entity_id
AND fet.language = v_languages.language_code
AND gdsd.currency_code = fct.currency_code(+)
AND fct.language (+) = v_languages.language_code
AND gdsd.cal_period_id = fcpt.cal_period_id
AND fcpt.language = v_languages.language_code
AND gdsd.balance_type_code = flv.lookup_code
AND flv.language = v_languages.language_code
AND flv.lookup_type = 'GCS_BALANCE_TYPE_CODES'
AND flv.view_application_id = 266
AND gdsd.amount_type_code = amt.lookup_code
AND amt.lookup_type = 'GCS_AMOUNT_TYPE_CODES'
AND amt.LANGUAGE = v_languages.language_code
AND amt.view_application_id = 266
AND gdsd.currency_type_code = curr.lookup_code
AND curr.lookup_type = 'GCS_CURRENCY_TYPES'
AND curr.LANGUAGE = v_languages.language_code
AND curr.view_application_id = 266
AND gdsd.measure_type_code = measure.lookup_code
AND measure.lookup_type = 'GCS_MEASURE_TYPE_CODES'
AND measure.LANGUAGE = v_languages.language_code
AND measure.view_application_id = 266
AND fea_ledger.entity_id = gdsd.entity_id
AND fea_ledger.attribute_id = l_entity_ledger_attr
AND fea_ledger.version_id = l_entity_ledger_version
AND flt.ledger_id = fea_ledger.dim_attribute_numeric_member
AND flt.language = v_languages.language_code
AND fea_srcsys.entity_id = gdsd.entity_id
AND fea_srcsys.attribute_id = l_entity_srcsys_attr
AND fea_srcsys.version_id = l_entity_srcsys_version
AND foct.object_id (+) = gdsd.balances_rule_id
AND foct.language (+) = v_languages.language_code
AND fla_comp.ledger_id = fea_ledger.dim_attribute_numeric_member
AND fla_comp.attribute_id = l_ldg_curr_attr
AND fla_comp.version_id = l_ldg_curr_version
AND fla_gvsc.ledger_id = fea_ledger.dim_attribute_numeric_member
AND fla_gvsc.attribute_id = l_ledger_vs_combo_attr
AND fla_gvsc.version_id = l_ledger_vs_combo_version
AND ROWNUM < 2;
SELECT actual_output_dataset_code
INTO l_dataset_code
FROM fem_object_definition_b fodb,
fem_cal_periods_attr fcpa_enddate,
fem_dim_attr_versions_b fdavb_enddate,
fem_intg_bal_rule_defs fibrd
WHERE fodb.object_id = l_ds_header_data.balances_rule_id
AND fcpa_enddate.date_assign_value BETWEEN fodb.effective_start_date AND fodb.effective_end_date
AND fcpa_enddate.attribute_id = gcs_utility_pkg.get_dimension_attribute('CAL_PERIOD_ID-CAL_PERIOD_END_DATE')
AND fcpa_enddate.attribute_id = fdavb_enddate.attribute_id
AND fcpa_enddate.version_id = fdavb_enddate.version_id
AND fdavb_enddate.default_version_flag = 'Y'
AND fodb.object_definition_id = fibrd.bal_rule_obj_def_id
AND ROWNUM < 2;
l_ds_lines_sql:= gcs_xml_utility_pkg.g_datasub_lines_select_stmnt ||
' AND load_id = '|| p_load_id ||
l_value_set_where_clause ||
' ORDER BY ' || gcs_xml_utility_pkg.g_ds_order_by_stmnt;
retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'Lines' );
insert into gcs_xml_files
( xml_file_id,
xml_file_type,
language,
xml_data,
last_update_date,
last_updated_by,
creation_date,
created_by,
object_version_number)
values ( p_load_id,
l_xml_file_type,
v_languages.language_code,
''||new_line||l_ds_xml,
sysdate,
0,
sysdate,
0,
0 );
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.INSERT', 'Inserted DS LOAD XML for Load Id : '||p_load_id);
update gcs_xml_files
set xml_data = ''||new_line||l_ds_xml,
last_update_date = sysdate,
last_updated_by = 0,
object_version_number = object_version_number + 1
where xml_file_id = p_load_id
and xml_file_type = l_xml_file_type
and language = v_languages.language_code;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.UPDATE', 'Updated DS LOAD XML for Load Id : '||p_load_id);
l_dstb_lines_sql:= gcs_xml_utility_pkg.g_ogl_dstb_lines_select_stmnt||
l_ds_header_data.sub_global_vs_combo_id||
gcs_xml_utility_pkg.g_ogl_dstb_lines_select_stmnt1||
' AND fea_balrule.ENTITY_ID = '||
l_ds_header_data.entity_id ||
' AND gel.SOURCE_SYSTEM_CODE = '||
l_ds_header_data.source_system_code ||
' AND fct.language(+) = '''||
v_languages.language_code ||
''' AND gel.CAL_PERIOD_ID = '||
l_ds_header_data.cal_period_id;
l_dstb_lines_sql:= gcs_xml_utility_pkg.g_dstb_lines_select_stmnt||
' AND fem_entities_source_system.ENTITY_ID = '||
l_ds_header_data.entity_id ||
' AND fct.language(+) = '''||
v_languages.language_code ||
''' AND gel.CAL_PERIOD_ID = '||
l_ds_header_data.cal_period_id;
l_rowseq_hash_sql := gcs_xml_utility_pkg.g_ogl_hash_select_stmnt;
retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'Lines' );
insert into gcs_xml_files
( xml_file_id,
xml_file_type,
language,
xml_data,
last_update_date,
last_updated_by,
creation_date,
created_by,
object_version_number)
values ( p_load_id,
l_xml_file_type,
v_languages.language_code,
''||new_line||l_dstb_xml,
sysdate,
0,
sysdate,
0,
0 );
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.INSERT', 'Inserted DSTB XML for Load Id : '||p_load_id);
update gcs_xml_files
set xml_data = ''||new_line||l_dstb_xml,
last_update_date = sysdate,
last_updated_by = 0,
object_version_number = object_version_number + 1
where xml_file_id = p_load_id
and xml_file_type = l_xml_file_type
and language = v_languages.language_code;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.UPDATE', 'Updated DSTB XML for Load Id : '||p_load_id);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_DS_XML.UPDATE', 'Error DS XML for Load Id : '||p_load_id||' - '||SQLERRM);
SELECT language_code
FROM fnd_languages
WHERE installed_flag IN ('I','B');
SELECT 1
FROM gcs_xml_files
WHERE xml_file_id = p_ad_transaction_id
AND xml_file_type = p_xml_file_type
AND language = p_language_code;
SELECT
flv.meaning category_name,
treatments.transaction_date,
ght.hierarchy_name hierarchy_name,
fet1.entity_name consolidation_entity_name,
fet2.entity_name child_entity_name,
treatments.total_consideration||' '||fct.name total_consideration,
treatments.entry_name
INTO
l_ad_header_data.category_name,
l_ad_header_data.transaction_date,
l_ad_header_data.hierarchy_name,
l_ad_header_data.cons_entity_name,
l_ad_header_data.child_entity_name,
l_ad_header_data.total_consideration,
l_ad_header_data.entry_name
FROM
fnd_lookup_values flv,
fem_entities_tl fet1,
fem_entities_tl fet2,
gcs_entity_cons_attrs geca,
gcs_hierarchies_tl ght,
fnd_currencies_tl fct,
fnd_currencies fcb,
(select gtt_to.treatment_id to_treatment_id,
gtt_from.treatment_id from_treatment_id,
gtt_to.treatment_name to_treatment,
gtt_from.treatment_name from_treatment,
fcr.status_code,
gat.post_cons_relationship_id,
gat.pre_cons_relationship_id,
gat.intermediate_treatment_id,
gat.transaction_type_code,
gat.total_consideration,
fcr.request_id,
gat.ad_transaction_id,
gat.transaction_date,
nvl(gcr.parent_entity_id, gcr_pre.parent_entity_id) parent_entity_id,
nvl(gcr.child_entity_id, gcr_pre.child_entity_id) child_entity_id,
nvl(gcr.hierarchy_id, gcr_pre.hierarchy_id) hierarchy_id,
NVL (gcr.ownership_percent, 0) to_percent,
NVL (gcr_pre.ownership_percent, 0) from_percent,
geh.entry_name entry_name
from gcs_treatments_tl gtt_from,
gcs_treatments_tl gtt_to,
gcs_ad_transactions gat,
gcs_entry_headers geh,
fnd_concurrent_requests fcr,
gcs_cons_relationships gcr,
gcs_cons_relationships gcr_pre
where
gtt_from.LANGUAGE = v_languages.language_code AND
gtt_to.LANGUAGE = v_languages.language_code AND
gat.assoc_entry_id = geh.entry_id AND
gat.request_id = fcr.request_id (+) AND
gat.ad_transaction_id = p_ad_transaction_id AND
gat.post_cons_relationship_id = gcr.cons_relationship_id (+) AND
gat.pre_cons_relationship_id = gcr_pre.cons_relationship_id(+) AND
nvl(gcr.treatment_id, gat.intermediate_treatment_id) = gtt_to.treatment_id AND
nvl(gcr_pre.treatment_id, gat.intermediate_treatment_id) = gtt_from.treatment_id
) treatments,
(select grb.to_treatment_id,
grb.from_treatment_id,
grb.rule_id, grt.rule_name,
grb.transaction_type_code
from gcs_elim_rules_tl grt,
gcs_elim_rules_b grb
where grt.LANGUAGE = v_languages.language_code AND
grb.rule_id = grt.rule_id
) rules
WHERE treatments.to_treatment_id = rules.to_treatment_id (+) AND
treatments.from_treatment_id = rules.from_treatment_id (+) AND
rules.transaction_type_code (+)= treatments.transaction_type_code AND
fet1.entity_id = treatments.parent_entity_id AND
fet1.LANGUAGE = v_languages.language_code AND
fet2.entity_id = treatments.child_entity_id AND
fet2.LANGUAGE = v_languages.language_code AND
geca.entity_id = treatments.parent_entity_id AND
geca.hierarchy_id = ght.hierarchy_id AND
ght.language = v_languages.language_code AND
geca.currency_code = fcb.currency_code AND
treatments.hierarchy_id = ght.hierarchy_id AND
treatments.transaction_type_code = flv.lookup_code AND
flv.lookup_type = 'TRANSACTION_TYPE_CODE' AND
flv.LANGUAGE = v_languages.language_code AND
flv.view_application_id = 266 AND
fct.currency_code = fcb.currency_code AND
fct.LANGUAGE = v_languages.language_code AND
ROWNUM <= 1;
l_ad_lines_sql:= gcs_xml_utility_pkg.g_ad_lines_select_stmnt ||
' AND ad_transaction_id = ' ||
p_ad_transaction_id ||
' ORDER BY TRIAL_BALANCE_SEQ, '||
gcs_xml_utility_pkg.g_order_by_stmnt;
retval := xslprocessor.selectnodes (xmldom.makenode( doc_in), 'AD_LINES' );
insert into gcs_xml_files
(xml_file_id,
xml_file_type,
language,
xml_data,
last_update_date,
last_updated_by,
creation_date,
created_by,
object_version_number)
values (p_ad_transaction_id,
p_xml_file_type,
v_languages.language_code,
''||new_line||l_ad_xml,
sysdate,
0,
sysdate,
0,
0);
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_AD_XML.INSERT', 'Inserted AD XML for AD Transaction Id : '||p_ad_transaction_id);
update gcs_xml_files
set xml_data = ''||new_line||l_ad_xml,
last_update_date = sysdate,
last_updated_by = 0,
object_version_number = object_version_number + 1
where xml_file_id = p_ad_transaction_id
and xml_file_type = p_xml_file_type
and language = v_languages.language_code;
FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, g_api || '.GENERATE_AD_XML.UPDATE', 'Updated AD XML for AD Transaction Id : '||p_ad_transaction_id);
SELECT run_detail_id,
entry_id,
stat_entry_id,
request_error_code
FROM gcs_cons_eng_run_dtls
WHERE run_name = p_run_name
AND consolidation_entity_id = p_cons_entity_id
AND child_entity_id = p_child_entity_id
AND category_code = p_category_code;
SELECT run_detail_id,
entry_id,
stat_entry_id,
request_error_code
FROM gcs_cons_eng_run_dtls
WHERE run_name = p_run_name
AND consolidation_entity_id = p_cons_entity_id
AND child_entity_id IS NOT NULL
AND category_code = p_category_code;
SELECT run_detail_id,
entry_id,
stat_entry_id,
request_error_code
FROM gcs_cons_eng_run_dtls
WHERE run_name = p_run_name
AND consolidation_entity_id = p_cons_entity_id
AND category_code = p_category_code
AND child_entity_id = p_child_entity_id;
SELECT run_detail_id,
entry_id,
stat_entry_id,
request_error_code
FROM gcs_cons_eng_run_dtls
WHERE run_name = p_run_name
AND consolidation_entity_id = p_cons_entity_id
AND child_entity_id IS NOT NULL
AND category_code = p_category_code;
SELECT entry_id,
stat_entry_id,
request_error_code
INTO l_entry_id,
l_stat_entry_id,
l_request_error_code
FROM gcs_cons_eng_run_dtls
WHERE run_detail_id = p_run_detail_id;
SELECT entry_id,
request_error_code
INTO l_entry_id,
l_request_error_code
FROM gcs_cons_eng_run_dtls
WHERE run_detail_id = p_run_detail_id;