[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_full_entry_lines(
p_entry_id NUMBER,
p_stat_entry_id NUMBER,
p_cons_entity_id NUMBER,
p_hierarchy_id NUMBER,
p_relationship_id NUMBER,
p_cal_period_id NUMBER,
p_period_end_date DATE,
p_currency_code VARCHAR2,
p_balance_type_code VARCHAR2,
p_dataset_code NUMBER)
IS
fn_name VARCHAR2(30) := 'INSERT_FULL_ENTRY_LINES';
SELECT child_entity_id,
gcs_utility_pkg.get_org_id(child_entity_id, hierarchy_id) org_id
FROM gcs_cons_relationships
WHERE hierarchy_id = p_hierarchy_id
AND parent_entity_id = p_cons_entity_id
AND actual_ownership_flag = 'Y';
SELECT balance_by_org_flag
INTO l_bal_by_org_flag
FROM gcs_hierarchies_b
WHERE hierarchy_id = p_hierarchy_id;
INSERT /*+ APPEND */ INTO GCS_ENTRY_LINES
(entry_id, line_type_code,
company_cost_center_org_id, line_item_id, intercompany_id,
xtd_balance_e, ytd_balance_e,
ptd_debit_balance_e, ptd_credit_balance_e,
ytd_debit_balance_e, ytd_credit_balance_e,
creation_date, created_by,
last_update_date, last_updated_by, last_update_login)
SELECT
decode(currency_code, 'STAT', p_stat_entry_id, p_entry_id), null,
company_cost_center_org_id, line_item_id, intercompany_id,
sum(xtd_balance_e), sum(ytd_balance_e),
sum(ptd_debit_balance_e), sum(ptd_credit_balance_e),
sum(ytd_debit_balance_e), sum(ytd_credit_balance_e),
sysdate, GCS_AGGREGATION_PKG.g_fnd_user_id,
sysdate, GCS_AGGREGATION_PKG.g_fnd_user_id,
GCS_AGGREGATION_PKG.g_fnd_login_id
FROM
GCS_HIERARCHIES_B ghb,
FEM_BALANCES fb,
GCS_CONS_RELATIONSHIPS gcr,
GCS_TREATMENTS_B gt
WHERE
ghb.hierarchy_id = p_hierarchy_id
AND gcr.hierarchy_id = ghb.hierarchy_id
AND gcr.parent_entity_id = p_cons_entity_id
AND gcr.actual_ownership_flag = 'Y'
AND p_period_end_date BETWEEN gcr.start_date
AND nvl(gcr.end_date, p_period_end_date)
AND gt.treatment_id (+) = gcr.treatment_id
AND nvl(gt.consolidation_type_code, 'FULL') <> 'NONE'
AND fb.dataset_code = p_dataset_code
AND fb.ledger_id = ghb.fem_ledger_id
AND fb.cal_period_id = p_cal_period_id
AND fb.source_system_code = GCS_UTILITY_PKG.g_gcs_source_system_code
AND fb.currency_code IN (p_currency_code, 'STAT')
AND fb.entity_id = gcr.child_entity_id
GROUP BY
fb.currency_code,
fb.company_cost_center_org_id,
fb.intercompany_id,
fb.line_item_id;
SELECT specific_intercompany_id
INTO l_intercompany_id
FROM GCS_CATEGORIES_B
WHERE category_code = 'AGGREGATION';
INSERT INTO gcs_entry_lines_gt
(entry_id, line_item_id, company_cost_center_org_id, intercompany_id,
xtd_balance_e, ytd_balance_e,
ptd_debit_balance_e, ptd_credit_balance_e,
ytd_debit_balance_e, ytd_credit_balance_e)
SELECT
decode(currency_code, 'STAT', p_stat_entry_id, p_entry_id),
fb.line_item_id,
-- company_cost_center_org_id
decode('Y',
-- matching against Retained Earnings Account template
l_default_org_id,
-- matching against Suspense Account template
l_default_org_id,
company_cost_center_org_id),
-- intercompany_id
decode(intercompany_id, company_cost_center_org_id,
decode(l_intercompany_id, NULL,
decode('Y',
-- matching against Retained Earnings Account template
l_default_org_id,
-- matching against Suspense Account template
l_default_org_id,
company_cost_center_org_id),
intercompany_id), intercompany_id),
sum(xtd_balance_e), sum(ytd_balance_e),
sum(ptd_debit_balance_e), sum(ptd_credit_balance_e),
sum(ytd_debit_balance_e), sum(ytd_credit_balance_e)
FROM
GCS_HIERARCHIES_B ghb,
FEM_BALANCES fb,
GCS_CONS_RELATIONSHIPS gcr,
GCS_TREATMENTS_B gt
WHERE ghb.hierarchy_id = p_hierarchy_id
AND gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id = p_cons_entity_id
AND gcr.actual_ownership_flag = 'Y'
AND p_period_end_date BETWEEN gcr.start_date AND
NVL (gcr.end_date, p_period_end_date)
AND gt.treatment_id(+) = gcr.treatment_id
AND NVL(gt.consolidation_type_code, 'FULL') <> 'NONE'
AND fb.dataset_code = p_dataset_code
AND fb.ledger_id = ghb.fem_ledger_id
AND fb.cal_period_id = p_cal_period_id
AND fb.source_system_code = gcs_utility_pkg.g_gcs_source_system_code
AND fb.currency_code IN (p_currency_code, 'STAT')
AND fb.entity_id = gcr.child_entity_id
GROUP BY
fb.currency_code,
-- company_cost_center_org_id
decode('Y',
-- matching against Retained Earnings Account template
l_default_org_id,
-- matching against Suspense Account template
l_default_org_id,
company_cost_center_org_id),
-- intercompany_id
decode(intercompany_id, company_cost_center_org_id,
decode(l_intercompany_id, NULL,
decode('Y',
-- matching against Retained Earnings Account template
l_default_org_id,
-- matching against Suspense Account template
l_default_org_id,
company_cost_center_org_id),
intercompany_id), intercompany_id),
fb.line_item_id;
UPDATE gcs_entry_lines_gt gelg
SET company_cost_center_org_id = l_default_org_id,
intercompany_id = DECODE(intercompany_id, company_cost_center_org_id,
DECODE(l_intercompany_id, NULL, l_default_org_id),
intercompany_id)
WHERE (
line_item_id, company_cost_center_org_id) IN (
SELECT
line_item_id,
retrieve_org_id (cr2.child_entity_id)
FROM gcs_cons_relationships cr2,
gcs_curr_treatments_b gctb
WHERE cr2.parent_entity_id = p_cons_entity_id
AND cr2.hierarchy_id = p_hierarchy_id
AND cr2.actual_ownership_flag = 'Y'
AND p_period_end_date BETWEEN cr2.start_date
AND NVL (cr2.end_date,
p_period_end_date
)
AND gctb.curr_treatment_id IN (
SELECT gcr.curr_treatment_id
FROM gcs_cons_relationships gcr
START WITH gcr.hierarchy_id = p_hierarchy_id
AND gcr.parent_entity_id =
p_cons_entity_id
AND gcr.actual_ownership_flag = 'Y'
AND p_period_end_date
BETWEEN gcr.start_date
AND NVL (gcr.end_date,
p_period_end_date
)
CONNECT BY PRIOR gcr.child_entity_id =
gcr.parent_entity_id
AND gcr.hierarchy_id = p_hierarchy_id
AND gcr.actual_ownership_flag = 'Y'
AND p_period_end_date
BETWEEN gcr.start_date
AND NVL (gcr.end_date,
p_period_end_date
))
GROUP BY
line_item_id, cr2.child_entity_id);
INSERT /*+ append */INTO gcs_entry_lines
(entry_id, company_cost_center_org_id, line_item_id,
intercompany_id,
xtd_balance_e, ytd_balance_e,
ptd_debit_balance_e, ptd_credit_balance_e,
ytd_debit_balance_e, ytd_credit_balance_e,
creation_date, created_by, last_update_date,
last_updated_by, last_update_login)
SELECT entry_id, company_cost_center_org_id, line_item_id,
intercompany_id,
SUM (xtd_balance_e), SUM (ytd_balance_e),
SUM (ptd_debit_balance_e), SUM (ptd_credit_balance_e),
SUM (ytd_debit_balance_e), SUM (ytd_credit_balance_e),
SYSDATE, gcs_aggregation_pkg.g_fnd_user_id, SYSDATE,
gcs_aggregation_pkg.g_fnd_user_id,
gcs_aggregation_pkg.g_fnd_login_id
FROM gcs_entry_lines_gt
GROUP BY entry_id,
company_cost_center_org_id,
line_item_id,
intercompany_id;
'Inserted ' || to_char(SQL%ROWCOUNT) || ' row(s)');
END insert_full_entry_lines;