[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- 6) After successful suspense plug-in insert the header
-- entries into the GCS_ENTRY_HEADERS table by calling
-- the Insert Elimination Header procedure.
-- 7) All the above processing has to be completed in one
-- commit cycle. So here we may COMMIT.
-- Arguments
-- Notes
-- p_hierarchy_id Hierarchy id
-- p_cal_period_id calendar period id
-- p_entity_id Consolidation entity id.
-- p_balance_type balance type like 'ACTUAL' or 'ADB'
-- p_elim_mode Elimination mode Valid values are 'IE' for Intercompany
-- or 'IA' for Intracompany
-- p_currency_code Currency code like 'USD', 'EUR', etc..,
-- P_run_name Consolidation run name.
-- x_errbuf Returns error message to concurrent manager if there is an error.
-- x_ret_code Returns error code to concurrent manager if there is an error.
-- Syntax for calling from an external package.
-- GCS_INTERCO_PROCESSING_PKG.Interco_process_Main
-- (10041,
-- 24534640000000000000031002200140,
-- 1030682,
-- 'ACTUAL',
-- 'IE',
-- 'EUR',
-- 'Srini Run');
SELECT DATE_ASSIGN_VALUE
INTO g_period_start_date
FROM fem_cal_periods_attr fcpa
WHERE fcpa.cal_period_id = p_cal_period_id
AND fcpa.attribute_id =
g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_START_DATE').attribute_id
AND fcpa.version_id = g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_START_DATE').version_id;
SELECT DATE_ASSIGN_VALUE
INTO g_period_end_date
FROM fem_cal_periods_attr fcpa
WHERE fcpa.cal_period_id = p_cal_period_id
AND fcpa.attribute_id =
g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').attribute_id
AND fcpa.version_id = g_dimension_attr_info ('CAL_PERIOD_ID-CAL_PERIOD_END_DATE').version_id;
SELECT ghb.ie_by_org_code,
ghb.lob_dim_column_name,
ghb.lob_reporting_enabled_flag,
ghb.lob_hierarchy_obj_id,
ghb.fem_ledger_id
INTO g_match_rule_code,
l_lob_dim_col_name,
l_lob_rpt_enabled_flag,
l_lob_hier_obj_id,
l_fem_ledger_id
FROM GCS_HIERARCHIES_B ghb
WHERE ghb.hierarchy_id = p_hierarchy_id;
SELECT gcea.currency_code
INTO g_currency_code
FROM GCS_ENTITY_CONS_ATTRS gcea
WHERE gcea.hierarchy_id = p_hierarchy_id
AND gcea.entity_id = p_entity_id;
SELECT DIM_ATTRIBUTE_NUMERIC_MEMBER
INTO g_elim_entity_id
FROM FEM_ENTITIES_ATTR
WHERE attribute_id =
g_dimension_attr_info ('ENTITY_ID-ELIMINATION_ENTITY').attribute_id
AND entity_id = p_entity_id
AND version_id = g_dimension_attr_info ('ENTITY_ID-ELIMINATION_ENTITY').version_id;
SELECT entity_name
INTO g_elim_entity_name
FROM FEM_ENTITIES_TL
WHERE LANGUAGE = userenv('LANG')
AND entity_id = g_elim_entity_id;
SELECT parent_entity_id
INTO g_cons_entity_id
FROM GCS_CONS_RELATIONSHIPS
WHERE hierarchy_id = p_hierarchy_id
AND child_entity_id = p_entity_id
AND dominant_parent_flag = 'Y'
AND actual_ownership_flag ='Y'
AND (g_period_end_date
BETWEEN NVL(start_date,TO_DATE('01/01/1950', 'MM/DD/YYYY'))
AND NVL(END_DATE, TO_DATE('12/31/9999', 'MM/DD/YYYY')));
SELECT entity_name
INTO g_elim_entity_name
FROM FEM_ENTITIES_TL
WHERE LANGUAGE = userenv('LANG')
AND entity_id = g_elim_entity_id;
'Insert parent child entity relationships into'
||'GCS_INTERCO_SUBS_GT'
);
SELECT dataset_code
INTO l_data_set_code
FROM GCS_DATASET_CODES
WHERE hierarchy_id = p_hierarchy_id
AND balance_type_code = p_balance_type;
SELECT count(*) into g_no_rows
from GCS_INTERCO_HDR_GT;
SELECT count(*) into g_no_rows
from GCS_INTERCO_SUBS_GT;
'Call insert_interco_hdrs() routine'
);
'Error in inserting rows into temporary table '
||' SQL error message: '||SUBSTR(SQLERRM, 1, 255));
|| ' routine to insert intercompany eliminations'
);
'Error in inserting intercompany lines into '
||' GCS_ENTRY_LINES '
||' SQL error message: '||SUBSTR(SQLERRM, 1, 255));
|| ' routine to insert suspense elimination'
|| ' lines.');
|| ' routine to insert suspense elimination'
|| ' lines.');
'Error in inserting elimination headers '
||' GCS_ENTRY_LINES '
||' SQL error message: '||SUBSTR(SQLERRM, 1, 255));
SELECT object_definition_id INTO l_hierarchy_valid_id
FROM FEM_OBJECT_DEFINITION_B fod
WHERE fod.object_id = l_lob_hier_obj_id
AND (g_period_end_date
BETWEEN NVL(fod.effective_start_date,
TO_DATE('01/01/1950','MM/DD/YYYY'))
AND NVL(fod.effective_end_date,
TO_DATE('12/31/9999','MM/DD/YYYY')));
SELECT member_col
BULK COLLECT INTO l_dims_list
FROM fem_xdim_dimensions
WHERE gcs_utility_pkg.Get_Dimension_Required(member_col) = 'Y'
AND member_col <> 'ENTITY_ID';
l_sql_stmt := 'UPDATE GCS_ENTRY_LINES gel1
SET '||l_lob_dim_col_name||' = (SELECT
DECODE(
fcoa2.dim_attribute_numeric_member,
fcoa3.dim_attribute_numeric_member,
fcoa2.dim_attribute_numeric_member,
NVL(fcca.dim_attribute_numeric_member,
gel1.'||l_lob_dim_col_name||'))
FROM GCS_ENTRY_LINES gel,
fem_cctr_orgs_attr fcoa2,
fem_cctr_orgs_attr fcoa3,
fem_user_dim1_attr fcca
WHERE gel.entry_id = gel1.entry_id
AND gel.company_cost_center_org_id =
gel1.company_cost_center_org_id
AND gel.intercompany_id =
gel1.intercompany_id
AND gel.line_item_id = gel1.line_item_id
AND gel.company_cost_center_org_id =
fcoa2.company_cost_center_org_id
'
||l_text||
'
AND fcoa2.attribute_id = :attribute_id
AND fcoa2.version_id = :version_id
AND gel.intercompany_id = fcoa3.company_cost_center_org_id
AND fcoa3.attribute_id = :attribute_id
AND fcoa3.version_id = :version_id
AND fcca.user_dim1_id = ';
SELECT fcch1.parent_id
FROM fem_user_dim1_hier fcch1,
fem_user_dim1_hier fcch2
WHERE fcch1.child_id =
fcoa2.dim_attribute_numeric_member
AND fcch1.hierarchy_obj_def_id =
:hierarchy_id
AND fcch1.parent_id <> fcch1.child_id
AND fcch2.child_id =
fcoa3.dim_attribute_numeric_member
AND fcch2.hierarchy_obj_def_id =
:hierarchy_id
AND fcch2.parent_id <> fcch2.child_id
AND fcch1.parent_id = fcch2.parent_id
AND fcch1.parent_depth_num =
(SELECT MAX(fcch3.parent_depth_num)
FROM fem_user_Dim1_hier fcch3,
fem_user_dim1_hier fcch4
WHERE fcch3.child_id =
fcoa2.dim_attribute_numeric_member
AND fcch3.hierarchy_obj_def_id =
:hierarchy_id
AND fcch3.parent_id <> fcch3.child_id
AND fcch4.child_id =
fcoa3.dim_attribute_numeric_member
AND fcch4.hierarchy_obj_def_id =
:hierarchy_id
AND fcch4.parent_id <> fcch4.child_id
AND fcch3.parent_id = fcch4.parent_id))
AND fcca.attribute_id = :attribute_id
AND fcca.version_id = :version_id)
WHERE ENTRY_ID IN ( SELECT ENTRY_ID FROM GCS_INTERCO_HDR_GT)
AND description = ''SUSPENSE_LINE''';
' Error In Insert_Suspense_lines() '
|| SUBSTR(l_err_msg, 1, 255));
x_errbuf := 'Error in Insert_Suspense_lines()';
' Error In Insert_Interco_lines() '
|| SUBSTR(SQLERRM, 1, 255));
x_errbuf := 'Error in Insert_Interco_lines()';
' Error In Insert_Interco_Hdrs() '
|| SUBSTR(SQLERRM, 1, 255));
x_errbuf := 'Error in Insert_Interco_Hdrs()';
' Error In Insert_Elimination_Hdrs() '
|| SUBSTR(SQLERRM, 1, 255));
x_errbuf := 'Error in Insert_Elimination_Hdrs()';
'Insert distinct pairs of entities for each rule '
|| 'into GCS_INTERCO_HDR_GT in full '
|| 'consolidation run mode and elim-mode=''IE'''
|| '- Intercompany rule on Receivables side'
);
INSERT INTO GCS_INTERCO_HDR_GT
(entry_id, source_entity_id, target_entity_id, rule_id,
threshold_currency,threshold_amount,sus_financial_elem_id,
sus_product_id,sus_natural_account_id,
sus_channel_id,sus_line_item_id,sus_project_id,sus_customer_id,
sus_task_id, sus_user_dim1_id, sus_user_dim2_id,sus_user_dim3_id,
sus_user_dim4_id, sus_user_dim5_id, sus_user_dim6_id,
sus_user_dim7_id, sus_user_dim8_id, sus_user_dim9_id,
sus_user_dim10_id, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
currency_code)
SELECT GCS_ENTRY_HEADERS_S.NEXTVAL, git.src_id,
git.tar_id, git.rule_id,
git.threshold_currency,
git.threshold_amount, git.sus_financial_elem_id,
git.sus_product_id,git.sus_natural_account_id,
git.sus_channel_id,git.sus_line_item_id,
git.sus_project_id,git.sus_customer_id,
git.sus_task_id,git.sus_user_dim1_id,
git.sus_user_dim2_id,git.sus_user_dim3_id,
git.sus_user_dim4_id, git.sus_user_dim5_id,
git.sus_user_dim6_id, git.sus_user_dim7_id,
git.sus_user_dim8_id, git.sus_user_dim9_id,
git.sus_user_dim10_id,SYSDATE,g_fnd_user_id,sysdate,
g_fnd_user_id,g_fnd_login_id, git.currency_code
FROM (SELECT giet.src_entity_id
src_id,
giet.target_entity_id
tar_id,
gib.rule_id, gib.threshold_currency,
gib.threshold_amount,
DECODE(fc.currency_code,'STAT',10000,
gib.sus_financial_elem_id) "SUS_FINANCIAL_ELEM_ID",
gib.sus_product_id, gib.sus_natural_account_id,
gib.sus_channel_id, gib.sus_line_item_id,
gib.sus_project_id, gib.sus_customer_id,
gib.sus_task_id, gib.sus_user_dim1_id,
gib.sus_user_dim2_id, gib.sus_user_dim3_id,
gib.sus_user_dim4_id, gib.sus_user_dim5_id,
gib.sus_user_dim6_id, gib.sus_user_dim7_id,
gib.sus_user_dim8_id, gib.sus_user_dim9_id,
gib.sus_user_dim10_id, fc.currency_code
FROM GCS_INTERCO_ELM_TRX giet,
GCS_FLATTENED_RELNS gfr,
GCS_FLATTENED_RELNS gfr1,
GCS_INTERCO_MEMBERS gim,
GCS_INTERCO_RULES_B gib ,
FND_CURRENCIES fc
WHERE giet.hierarchy_id = p_hierarchy_id
AND giet.cal_period_id = p_cal_period_id
AND fc.currency_code IN (p_currency_code,'STAT')
AND gfr.run_name = g_consolidation_run_name
AND gfr.parent_entity_id = p_entity_id
AND giet.src_entity_id = gfr.child_entity_ID
AND NVL(gfr.consolidation_type_code,'X') <> 'NONE'
AND gfr1.run_name = g_consolidation_run_name
AND gfr1.parent_entity_id = p_entity_id
AND giet.target_entity_id = gfr1.child_entity_id
AND NVL(gfr1.consolidation_type_code,'X') <> 'NONE'
AND giet.src_entity_id <> giet.target_entity_id
AND giet.line_item_id = gim.line_item_id
AND gim.line_item_group = 1
AND gim.rule_id = gib.rule_id
AND gib.enabled_flag = 'Y'
AND NOT EXISTS
(SELECT 'X' FROM gcs_interco_elm_trx giet1,
gcs_interco_members gim1
WHERE giet1.hierarchy_id = p_hierarchy_id
AND giet1.cal_period_id = p_cal_period_id
AND giet1.src_entity_id =
giet.target_entity_id
AND giet1.target_entity_id =
giet.src_entity_id
AND gim1.rule_id = gim.rule_id
AND giet1.company_cost_center_org_id >
giet1.intercompany_id
AND gim1.line_item_group >
gim.line_item_group
AND gim1.line_item_id = gim.line_item_id)
GROUP BY
giet.src_entity_id,
giet.target_entity_id,
gib.rule_id,gib.threshold_currency,
gib.threshold_amount,
gib.sus_financial_elem_id,
gib.sus_product_id,gib.sus_natural_account_id,
gib.sus_channel_id,gib.sus_line_item_id,
gib.sus_project_id,gib.sus_customer_id,
gib.sus_task_id,gib.sus_user_dim1_id,
gib.sus_user_dim2_id,gib.sus_user_dim3_id,
gib.sus_user_dim4_id, gib.sus_user_dim5_id,
gib.sus_user_dim6_id, gib.sus_user_dim7_id,
gib.sus_user_dim8_id, gib.sus_user_dim9_id,
gib.sus_user_dim10_id, fc.currency_code) git
WHERE NOT EXISTS
(SELECT 'X'
FROM GCS_CONS_ENG_RUN_DTLS gcer
WHERE gcer.child_entity_id = git.src_id
AND gcer.contra_child_entity_id = git.tar_id
AND gcer.run_name = g_consolidation_run_name);
'Insert distinct pairs of entities for each rule '
|| 'into GCS_INTERCO_HDR_GT in full '
|| 'consolidation run mode and elim-mode=''IE'''
|| '- Intercompany rule Payables side'
);
INSERT INTO GCS_INTERCO_HDR_GT
(entry_id, source_entity_id, target_entity_id, rule_id,
threshold_currency,threshold_amount,sus_financial_elem_id,
sus_product_id,sus_natural_account_id,
sus_channel_id,sus_line_item_id,sus_project_id,sus_customer_id,
sus_task_id, sus_user_dim1_id, sus_user_dim2_id,sus_user_dim3_id,
sus_user_dim4_id, sus_user_dim5_id, sus_user_dim6_id,
sus_user_dim7_id, sus_user_dim8_id, sus_user_dim9_id,
sus_user_dim10_id, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
currency_code)
SELECT GCS_ENTRY_HEADERS_S.NEXTVAL, git.src_id,
git.tar_id, git.rule_id,
git.threshold_currency,
git.threshold_amount, git.sus_financial_elem_id,
git.sus_product_id,git.sus_natural_account_id,
git.sus_channel_id,git.sus_line_item_id,
git.sus_project_id,git.sus_customer_id,
git.sus_task_id,git.sus_user_dim1_id,
git.sus_user_dim2_id,git.sus_user_dim3_id,
git.sus_user_dim4_id, git.sus_user_dim5_id,
git.sus_user_dim6_id, git.sus_user_dim7_id,
git.sus_user_dim8_id, git.sus_user_dim9_id,
git.sus_user_dim10_id,SYSDATE,g_fnd_user_id,sysdate,
g_fnd_user_id,g_fnd_login_id, git.currency_code
FROM (SELECT giet.target_entity_id
src_id,
giet.src_entity_id
tar_id,
gib.rule_id, gib.threshold_currency,
gib.threshold_amount,
DECODE(fc.currency_code,'STAT',10000,
gib.sus_financial_elem_id) "SUS_FINANCIAL_ELEM_ID",
gib.sus_product_id, gib.sus_natural_account_id,
gib.sus_channel_id, gib.sus_line_item_id,
gib.sus_project_id, gib.sus_customer_id,
gib.sus_task_id, gib.sus_user_dim1_id,
gib.sus_user_dim2_id, gib.sus_user_dim3_id,
gib.sus_user_dim4_id, gib.sus_user_dim5_id,
gib.sus_user_dim6_id, gib.sus_user_dim7_id,
gib.sus_user_dim8_id, gib.sus_user_dim9_id,
gib.sus_user_dim10_id, fc.currency_code
FROM GCS_INTERCO_ELM_TRX giet,
GCS_FLATTENED_RELNS gfr,
GCS_FLATTENED_RELNS gfr1,
GCS_INTERCO_MEMBERS gim,
GCS_INTERCO_RULES_B gib ,
FND_CURRENCIES fc
WHERE giet.hierarchy_id = p_hierarchy_id
AND giet.cal_period_id = p_cal_period_id
AND fc.currency_code IN (p_currency_code,'STAT')
AND gfr.run_name = g_consolidation_run_name
AND gfr.parent_entity_id = p_entity_id
AND giet.src_entity_id = gfr.child_entity_ID
AND NVL(gfr.consolidation_type_code,'X') <> 'NONE'
AND gfr1.run_name = g_consolidation_run_name
AND gfr1.parent_entity_id = p_entity_id
AND giet.target_entity_id = gfr1.child_entity_id
AND NVL(gfr1.consolidation_type_code,'X') <> 'NONE'
AND giet.src_entity_id <> giet.target_entity_id
AND giet.line_item_id = gim.line_item_id
AND gim.line_item_group = 2
AND gim.rule_id = gib.rule_id
AND gib.enabled_flag = 'Y'
AND NOT EXISTS
(SELECT 'Y'
FROM GCS_INTERCO_HDR_GT gihg1,
GCS_INTERCO_MEMBERS gim1
WHERE gihg1.target_entity_id =
DECODE(gim1.line_item_id,
gim.line_item_id,
giet.target_entity_id,
giet.src_entity_id)
AND gihg1.source_entity_id =
DECODE(gim1.line_item_id,
gim.line_item_id,
giet.src_entity_id,
giet.target_entity_id)
AND gihg1.rule_id = gim.rule_id
AND gihg1.rule_id = gim.rule_id
AND gim1.rule_id = gihg1.rule_id
AND gim1.line_item_group = 1)
GROUP BY
giet.src_entity_id,
giet.target_entity_id,
gib.rule_id,gib.threshold_currency,
gib.threshold_amount,
gib.sus_financial_elem_id,
gib.sus_product_id,gib.sus_natural_account_id,
gib.sus_channel_id,gib.sus_line_item_id,
gib.sus_project_id,gib.sus_customer_id,
gib.sus_task_id,gib.sus_user_dim1_id,
gib.sus_user_dim2_id,gib.sus_user_dim3_id,
gib.sus_user_dim4_id, gib.sus_user_dim5_id,
gib.sus_user_dim6_id, gib.sus_user_dim7_id,
gib.sus_user_dim8_id, gib.sus_user_dim9_id,
gib.sus_user_dim10_id, fc.currency_code) git
WHERE NOT EXISTS
(SELECT 'X'
FROM GCS_CONS_ENG_RUN_DTLS gcer
WHERE gcer.child_entity_id = git.src_id
AND gcer.contra_child_entity_id = git.tar_id
AND gcer.run_name = g_consolidation_run_name);
'Delete pair(s) of entities that are already'
|| ' eliminated, for e.g. at a mid level parent'
);
DELETE FROM GCS_INTERCO_HDR_GT gihg
WHERE EXISTS
(SELECT 'X' FROM GCS_CONS_ENG_RUN_DTLS gcer
WHERE gcer.run_name = g_consolidation_run_name
AND gcer.category_code = 'INTERCOMPANY'
AND gcer.child_entity_id = gihg.target_entity_id
AND gcer.contra_child_entity_id =
gihg.source_entity_id
AND gcer.rule_id = gihg.rule_id
AND gcer.consolidation_entity_id <>
g_entity_id);
'Insert distinct pairs of entities for each rule '
|| 'into GCS_INTERCO_HDR_GT in full '
|| 'consolidation run mode and elim-mode=''IA'''
);
INSERT INTO GCS_INTERCO_HDR_GT
(entry_id, source_entity_id, target_entity_id, rule_id,
threshold_currency,threshold_amount,sus_financial_elem_id,
sus_product_id,sus_natural_account_id,
sus_channel_id,sus_line_item_id,sus_project_id,sus_customer_id,
sus_task_id, sus_user_dim1_id, sus_user_dim2_id,sus_user_dim3_id,
sus_user_dim4_id, sus_user_dim5_id, sus_user_dim6_id,
sus_user_dim7_id, sus_user_dim8_id, sus_user_dim9_id,
sus_user_dim10_id, creation_date, created_by,
last_update_date, last_updated_by, last_update_login,
currency_code)
SELECT GCS_ENTRY_HEADERS_S.NEXTVAL, git.src_entity_id,
git.target_entity_id, git.rule_id,
git.threshold_currency,
git.threshold_amount, git.sus_financial_elem_id,
git.sus_product_id,git.sus_natural_account_id,
git.sus_channel_id,git.sus_line_item_id,
git.sus_project_id,git.sus_customer_id,
git.sus_task_id,git.sus_user_dim1_id,
git.sus_user_dim2_id,git.sus_user_dim3_id,
git.sus_user_dim4_id, git.sus_user_dim5_id,
git.sus_user_dim6_id, git.sus_user_dim7_id,
git.sus_user_dim8_id, git.sus_user_dim9_id,
git.sus_user_dim10_id,SYSDATE,g_fnd_user_id,SYSDATE,
g_fnd_user_id,g_fnd_login_id, git.currency_code
FROM (SELECT giet.src_entity_id,giet.target_entity_id,
gib.rule_id, gib.threshold_currency,
gib.threshold_amount, gib.sus_financial_elem_id,
gib.sus_product_id, gib.sus_natural_account_id,
gib.sus_channel_id, gib.sus_line_item_id,
gib.sus_project_id, gib.sus_customer_id,
gib.sus_task_id, gib.sus_user_dim1_id,
gib.sus_user_dim2_id, gib.sus_user_dim3_id,
gib.sus_user_dim4_id, gib.sus_user_dim5_id,
gib.sus_user_dim6_id, gib.sus_user_dim7_id,
gib.sus_user_dim8_id, gib.sus_user_dim9_id,
gib.sus_user_dim10_id, fc.currency_code
FROM GCS_INTERCO_ELM_TRX giet,
GCS_INTERCO_MEMBERS gim,
GCS_INTERCO_RULES_B gib,
FND_CURRENCIES fc
WHERE giet.hierarchy_id = p_hierarchy_id
AND giet.cal_period_id = p_cal_period_id
AND fc.currency_code IN (P_currency_code, 'STAT')
AND giet.src_entity_id = p_entity_id
AND giet.target_entity_id = giet.src_entity_id
AND giet.line_item_id = gim.line_item_id
AND gim.rule_id = gib.rule_id
AND gib.enabled_flag = 'Y'
GROUP BY giet.src_entity_id,giet.target_entity_id,
gib.rule_id,gib.threshold_currency,
gib.threshold_amount,
gib.sus_financial_elem_id,
gib.sus_product_id,gib.sus_natural_account_id,
gib.sus_channel_id,gib.sus_line_item_id,
gib.sus_project_id,gib.sus_customer_id,
gib.sus_task_id,gib.sus_user_dim1_id,
gib.sus_user_dim2_id,gib.sus_user_dim3_id,
gib.sus_user_dim4_id, gib.sus_user_dim5_id,
gib.sus_user_dim6_id, gib.sus_user_dim7_id,
gib.sus_user_dim8_id, gib.sus_user_dim9_id,
gib.sus_user_dim10_id , fc.currency_code) git;
'Insert avialable information '
|| 'into GCS_CONS_ENG_RUN_DTLS '
|| ' if there is an error the information will be'
|| ' saved upto this point.'
);
INSERT INTO gcs_cons_eng_run_dtls
(run_detail_id, run_name, Consolidation_entity_id
, child_entity_id, contra_child_entity_id, entry_id, rule_id,
request_error_code, bp_request_error_code, category_code,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
SELECT gcs_cons_eng_run_dtls_s.nextval,
g_consolidation_run_name,
DECODE(g_elim_code, 'IE',p_entity_id,'IA', g_cons_entity_id),
gehg.source_entity_id, gehg.target_entity_id,
gehg.entry_id, gehg.rule_id,
'WARNING', 'WARNING',
DECODE(g_elim_code, 'IE', 'INTERCOMPANY', 'IA', 'INTRACOMPANY'),
SYSDATE, g_fnd_user_id,
SYSDATE, g_fnd_user_id,
g_fnd_login_id
FROM GCS_INTERCO_HDR_GT gehg
WHERE gehg.currency_code <> 'STAT';
||' Insert avialable information '
|| 'into GCS_CONS_ENG_RUN_DTLS '
|| ' if there is an error the information will be'
|| ' saved upto this point.'
);
INSERT INTO gcs_cons_eng_run_dtls
(run_detail_id, run_name, Consolidation_entity_id
, child_entity_id, contra_child_entity_id, entry_id, rule_id,
request_error_code, bp_request_error_code, category_code,
xlate_request_error_code, bp_xlate_request_error_code,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
SELECT gcs_cons_eng_run_dtls_s.nextval,
g_consolidation_run_name,
DECODE(g_elim_code, 'IE',p_entity_id,'IA', g_cons_entity_id),
gehg.source_entity_id, gehg.target_entity_id,
gehg.entry_id, gehg.rule_id,
'WARNING', 'WARNING',
DECODE(g_elim_code, 'IE', 'INTERCOMPANY', 'IA', 'INTRACOMPANY'),
DECODE(g_elim_code, 'IA',
DECODE( p_xlation_required,'Y',
'NOT_STARTED','N','NOT_APPLICABLE'),NULL),
DECODE(g_elim_code, 'IA',
DECODE( p_xlation_required,'Y',
'NOT_STARTED','N','NOT_APPLICABLE'),NULL),
SYSDATE, g_fnd_user_id,
SYSDATE, g_fnd_user_id,
g_fnd_login_id
FROM GCS_INTERCO_HDR_GT gehg
WHERE gehg.currency_code = 'STAT'
AND NOT EXISTS ( SELECT 1 FROM gcs_cons_eng_run_dtls gcr1
WHERE gehg.source_entity_id = gcr1.child_entity_id
AND gehg.target_entity_id =
gcr1.contra_child_entity_id
AND gehg.rule_id = gcr1.rule_id);
'Error in inserting intercompany headers'
||' into the GCS_INTERCO_HDR_GT' );
INSERT INTO gcs_cons_eng_run_dtls
(run_detail_id, run_name, Consolidation_entity_id
, child_entity_id, contra_child_entity_id, entry_id, rule_id,
request_error_code, bp_request_error_code, category_code,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
SELECT gcs_cons_eng_run_dtls_s.nextval,
g_consolidation_run_name,
DECODE(g_elim_code, 'IE',p_entity_id,'IA', g_cons_entity_id),
NULL, NULL,
NULL, NULL,
'WARNING','WARNING',
DECODE(g_elim_code, 'IE', 'INTERCOMPANY', 'IA', 'INTRACOMPANY'),
SYSDATE, g_fnd_user_id,
SYSDATE, g_fnd_user_id,
g_fnd_login_id
FROM DUAL;
SELECT gihg.entry_id, gihg.threshold_currency,
nvl(minimum_accountable_unit, power(10, -precision)) mau,
NVL(precision,2) pres
FROM GCS_INTERCO_HDR_GT gihg, GCS_ENTRY_LINES gel,
fnd_currencies fnc
WHERE gihg.entry_id = gel.entry_id
AND gihg.currency_code <> 'STAT'
AND gihg.threshold_currency <> p_currency_code
AND gihg.currency_code = fnc.currency_code
GROUP BY gihg.entry_id, threshold_currency,
nvl(minimum_accountable_unit, power(10, -precision)),
NVL(precision,2);
SELECT nvl(minimum_accountable_unit, power(10, -precision)) mau,
NVL(precision,2) pres
FROM fnd_currencies
WHERE currency_code = c_ccy;
'Intercompany- Inserting consolidation currency '
||' entity entry '
||' headers into GCS_ENTRY_HEADERS '
||'- where legitimate conversion rate is available.'
);
INSERT INTO gcs_entry_headers
(entry_id, entry_name, hierarchy_id, disabled_flag,
entity_id, currency_code, balance_type_code,
start_cal_period_id, end_cal_period_id,
description, entry_type_code,
processed_run_name, category_code,
process_code, suspense_exceeded_flag,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
SELECT gehg.ENTRY_ID,
gehg.ENTRY_ID,
MAX(p_hierarchy_id), 'N',
MAX(g_elim_entity_id), gehg.currency_code,
p_balance_type, MAX(p_cal_period_id),
MAX(p_cal_period_id),
Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
|| MAX(girt.rule_name)
||' executed for '||g_elim_entity_name,
'AUTOMATIC', g_consolidation_run_name,
DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
'SINGLE_RUN_FOR_PERIOD',
DECODE (GREATEST(ABS(SUM(NVL(giet.ytd_debit_balance_e,0))-
SUM(NVL(giet.ytd_credit_balance_e,0))),
ROUND(((MAX(gehg.threshold_amount)*
NVL(x_corp_rate,1))/entries.mau),
NVL(entries.pres,2))* entries.mau),
ROUND(((MAX(gehg.threshold_amount)*
NVL(x_corp_rate,1))/entries.mau),
NVL(entries.pres,2))* entries.mau, 'N', 'Y'),
MAX(SYSDATE), MAX(g_fnd_user_id),
MAX(SYSDATE), MAX(g_fnd_user_id),
MAX(g_fnd_login_id), 'N'
FROM GCS_INTERCO_HDR_GT gehg,
GCS_INTERCO_RULES_TL girt,
GCS_ENTRY_LINES giet
WHERE gehg.entry_id = entries.entry_id
AND gehg.rule_id = girt.rule_id
AND girt.language = USERENV('LANG')
AND gehg.entry_id = giet.entry_id(+)
AND giet.line_item_id (+) = gehg.sus_line_item_id
AND giet.description(+) = 'SUSPENSE_LINE'
GROUP BY gehg.ENTRY_ID, gehg.currency_code;
'Intercompany- Inserting consolidation currency '
||' entity entry '
||' headers into GCS_ENTRY_HEADERS '
||'- where conversion rate is 1, that means valid '
||' conversion rate is not available.'
);
INSERT INTO gcs_entry_headers
(entry_id, entry_name, hierarchy_id, disabled_flag,
entity_id, currency_code, balance_type_code,
start_cal_period_id, end_cal_period_id,
description, entry_type_code,
processed_run_name, category_code,
process_code, suspense_exceeded_flag,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
SELECT gehg.ENTRY_ID,
gehg.ENTRY_ID,
p_hierarchy_id, 'N',
g_elim_entity_id, gehg.currency_code,
p_balance_type, p_cal_period_id,
p_cal_period_id,
Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
|| girt.rule_name
||' executed for '||g_elim_entity_name,
'AUTOMATIC', g_consolidation_run_name,
DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
'SINGLE_RUN_FOR_PERIOD',
'X',
SYSDATE, g_fnd_user_id,
SYSDATE, g_fnd_user_id,
g_fnd_login_id, 'N'
FROM GCS_INTERCO_HDR_GT gehg,
GCS_INTERCO_RULES_TL girt
WHERE gehg.entry_id = entries.entry_id
AND gehg.rule_id = girt.rule_id
AND girt.language = USERENV('LANG');
INSERT INTO gcs_entry_headers
(entry_id, entry_name, hierarchy_id, disabled_flag,
entity_id, currency_code, balance_type_code,
start_cal_period_id, end_cal_period_id,
description, entry_type_code,
processed_run_name, category_code,
process_code, suspense_exceeded_flag,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
SELECT gehg.ENTRY_ID, gehg.ENTRY_ID,
p_hierarchy_id, 'N',
g_elim_entity_id, gehg.currency_code,
p_balance_type, p_cal_period_id,
p_cal_period_id,
Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
||girt.rule_name
||' executed for '||g_elim_entity_name,
'AUTOMATIC', g_consolidation_run_name,
DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
'SINGLE_RUN_FOR_PERIOD',
'X',
SYSDATE, g_fnd_user_id,
SYSDATE, g_fnd_user_id,
g_fnd_login_id, 'N'
FROM GCS_INTERCO_HDR_GT gehg,
GCS_INTERCO_RULES_TL girt
WHERE gehg.entry_id = entries.entry_id
AND gehg.rule_id = girt.rule_id
AND girt.language = USERENV('LANG');
'Intercompany- Inserting same currency '
|| ' entry headers into GCS_ENTRY_HEADERS'
);
INSERT INTO gcs_entry_headers
(entry_id, entry_name, hierarchy_id, disabled_flag,
entity_id, currency_code, balance_type_code,
start_cal_period_id, end_cal_period_id,
description, entry_type_code,
processed_run_name, category_code,
process_code, suspense_exceeded_flag,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
SELECT gehg.ENTRY_ID,
gehg.ENTRY_ID,
p_hierarchy_id, 'N',
g_elim_entity_id, gehg.currency_code,
p_balance_type, p_cal_period_id,
p_cal_period_id,
Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
||MAX(girt.rule_name)
||' executed for '||g_elim_entity_name,
'AUTOMATIC', g_consolidation_run_name,
DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
'SINGLE_RUN_FOR_PERIOD',
DECODE (GREATEST(ABS(SUM(NVL(giet.ytd_debit_balance_e,0))-
SUM(NVL(giet.ytd_credit_balance_e,0))),
MAX(gehg.threshold_amount)),
MAX(gehg.threshold_amount), 'N', 'Y'),
MAX(SYSDATE), MAX(g_fnd_user_id),
MAX(SYSDATE), MAX(g_fnd_user_id),
MAX(g_fnd_login_id), 'N'
FROM GCS_INTERCO_HDR_GT gehg,
GCS_INTERCO_RULES_TL girt,
GCS_ENTRY_LINES giet
WHERE (gehg.currency_code = P_currency_code
AND gehg.threshold_currency = P_currency_code)
AND gehg.rule_id = girt.rule_id
AND girt.language = USERENV('LANG')
AND gehg.entry_id = giet.entry_id(+)
AND giet.line_item_id(+) = gehg.sus_line_item_id
AND giet.description(+) = 'SUSPENSE_LINE'
GROUP BY gehg.ENTRY_ID, gehg.currency_code;
'Intercompany- Inserting stat currency entry '
|| ' headers into GCS_ENTRY_HEADERS'
);
INSERT INTO gcs_entry_headers
(entry_id, entry_name, hierarchy_id, disabled_flag,
entity_id, currency_code, balance_type_code,
start_cal_period_id, end_cal_period_id,
description, entry_type_code,
processed_run_name, category_code,
process_code, suspense_exceeded_flag,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login, PERIOD_INIT_ENTRY_FLAG)
SELECT gehg.ENTRY_ID,
gehg.ENTRY_ID,
p_hierarchy_id, 'N',
g_elim_entity_id, gehg.currency_code,
p_balance_type, p_cal_period_id,
p_cal_period_id,
Decode(g_elim_code,'IE','Intercompany ', 'Intracompany ')
||girt.rule_name
||' executed for '||g_elim_entity_name,
'AUTOMATIC', g_consolidation_run_name,
DECODE(g_elim_code,'IE','INTERCOMPANY','IA','INTRACOMPANY'),
'SINGLE_RUN_FOR_PERIOD',
'N',
SYSDATE, g_fnd_user_id,
SYSDATE, g_fnd_user_id,
g_fnd_login_id, 'N'
FROM GCS_INTERCO_HDR_GT gehg,
GCS_ENTRY_LINES gel,
GCS_INTERCO_RULES_TL girt
WHERE gehg.entry_id = gel.entry_id
AND gehg.currency_code = 'STAT'
AND gehg.rule_id = girt.rule_id
AND girt.language = USERENV('LANG')
GROUP BY gehg.entry_id, girt.rule_name, gehg.currency_code;
DELETE FROM gcs_entry_headers
WHERE entry_id IN
(SELECT gihg.entry_id from gcs_interco_hdr_gt gihg
WHERE NOT EXISTS
(SELECT entry_id from gcs_entry_lines geh
WHERE geh.entry_id = gihg.entry_id));
DELETE FROM gcs_cons_eng_run_dtls
WHERE entry_id IN
(SELECT gihg.entry_id from gcs_interco_hdr_gt gihg
WHERE gihg.currency_code <> 'STAT'
AND NOT EXISTS
(SELECT entry_id from gcs_entry_headers geh
WHERE geh.entry_id = gihg.entry_id));
USING (SELECT
DECODE(g_elim_code, 'IE',g_entity_id,'IA', g_cons_entity_id)
cons_entity_id,
gehg.rule_id rule_id,
DECODE(g_elim_code, 'IE', 'INTERCOMPANY', 'IA', 'INTRACOMPANY')
category_code,
DECODE(geh.suspense_exceeded_flag, 'X','WARNING','Y','WARNING',
' N','NO_ERROR', 'COMPLETED') req_err_code,
DECODE(geh.suspense_exceeded_flag, 'X','WARNING','Y','WARNING',
' N','NO_ERROR', 'COMPLETED') bp_req_err_code,
gehg.source_entity_id src_entity_id,
gehg.target_entity_id target_entity_id,
gehg.entry_id entry_id
FROM GCS_INTERCO_HDR_GT gehg,
GCS_ENTRY_HEADERS geh
WHERE gehg.entry_id = geh.entry_id
AND gehg.currency_code = 'STAT') stat_result
ON (stat_result.src_entity_id = gcer.child_entity_id
AND stat_result.target_entity_id =
gcer.contra_child_entity_id
AND stat_result.rule_id = gcer.rule_id
AND gcer.run_name = g_consolidation_run_name)
WHEN MATCHED THEN UPDATE SET
gcer.stat_entry_id = stat_result.entry_id,
gcer.request_error_code =
NVL(stat_result.req_err_code,gcer.request_error_code),
gcer.bp_request_error_code =
NVL(stat_result.bp_req_err_code,gcer.bp_request_error_code),
last_update_date = SYSDATE,
last_updated_by = g_fnd_user_id
WHEN NOT MATCHED THEN INSERT (gcer.run_detail_id, gcer.run_name,
gcer.Consolidation_entity_id,
gcer.child_entity_id, gcer.contra_child_entity_id ,
gcer.stat_entry_id, gcer.rule_id, gcer.request_error_code,
gcer.bp_request_error_code, gcer.category_code,
gcer.creation_date, gcer.created_by, gcer.last_update_date,
gcer.last_updated_by, gcer.last_update_login)
VALUES(gcs_cons_eng_run_dtls_s.nextval,
g_consolidation_run_name,
stat_result.cons_entity_id, stat_result.src_entity_id,
stat_result.target_entity_id, stat_result.entry_id,
stat_result.rule_id, stat_result.req_err_code,
stat_result.bp_req_err_code,
stat_result.category_code,
SYSDATE, g_fnd_user_id,
SYSDATE, g_fnd_user_id,
g_fnd_login_id);
UPDATE gcs_cons_eng_run_dtls gcer
SET (request_error_code,
bp_request_error_code, last_update_date,
last_updated_by) =
(SELECT
--DECODE(gehg.currency_code, 'STAT', gehg.entry_id, NULL),
DECODE(gcer.stat_entry_id, NULL,
DECODE(geh.suspense_exceeded_flag,
'X','WARNING','Y','WARNING',
' N','NO_ERROR', 'COMPLETED'),
gcer.request_error_code),
DECODE(gcer.stat_entry_id, NULL,
DECODE(geh.suspense_exceeded_flag, 'X','WARNING','Y','WARNING',
' N','NO_ERROR', 'COMPLETED'),
gcer.bp_request_error_code),
SYSDATE,
g_fnd_user_id
FROM GCS_INTERCO_HDR_GT gehg,
GCS_ENTRY_HEADERS geh
WHERE gehg.entry_id = geh.entry_id
AND gehg.entry_id = gcer.entry_id)
WHERE gcer.entry_id IN (SELECT entry_id from gcs_interco_hdr_gt
WHERE currency_code <> 'STAT');
SELECT 1 INTO l_warning
FROM DUAL
WHERE EXISTS (SELECT 1 FROM GCS_ENTRY_HEADERS
WHERE suspense_exceeded_flag = 'Y'
AND entry_id IN (SELECT entry_id from gcs_interco_hdr_gt));