[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_entry_lines(
p_run_name VARCHAR2,
p_hierarchy_id NUMBER,
p_entity_id NUMBER,
p_currency_code VARCHAR2,
p_bal_by_org VARCHAR2,
p_sec_track_col VARCHAR2,
p_is_elim_entity VARCHAR2,
p_cons_entity_id NUMBER,
p_re_template GCS_TEMPLATES_PKG.TemplateRecord,
p_cross_year_flag VARCHAR2,
p_category_code VARCHAR2,
p_init_entry_id NUMBER,
p_init_xlate_entry_id NUMBER,
p_init_stat_entry_id NUMBER,
p_recur_entry_id NUMBER,
p_recur_xlate_entry_id NUMBER,
p_recur_stat_entry_id NUMBER,
--Bugfix 5449718: Added the calendar period year and net to re flag as parameters
p_cal_period_year NUMBER,
p_net_to_re_flag VARCHAR2)
IS
fn_name VARCHAR2(30) := 'INSERT_ENTRY_LINES';
SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
geh.year_to_apply_re,
geh.currency_code,
'N',
0
BULK COLLECT INTO
l_entry_id_list
FROM gcs_cons_eng_run_dtls gcerd,
gcs_entry_headers geh
WHERE gcerd.run_name = p_run_name
AND gcerd.consolidation_entity_id = p_cons_entity_id
AND gcerd.child_entity_id IS NOT NULL
AND gcerd.category_code = p_category_code
AND gcerd.entry_id = geh.entry_id
AND geh.period_init_entry_flag = 'N'
AND p_cal_period_year < NVL(geh.year_to_apply_re, p_cal_period_year+1);
SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
geh.year_to_apply_re,
geh.currency_code,
'N',
0
BULK COLLECT INTO
l_entry_id_list
FROM gcs_cons_eng_run_dtls gcerd,
gcs_entry_headers geh
WHERE gcerd.run_name = p_run_name
AND gcerd.consolidation_entity_id = p_cons_entity_id
AND gcerd.child_entity_id IS NOT NULL
AND gcerd.category_code = p_category_code
AND gcerd.entry_id = geh.entry_id
AND geh.period_init_entry_flag = 'N';
SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
geh.year_to_apply_re,
geh.currency_code,
geh.period_init_entry_flag,
NVL(geh.end_cal_period_id, geh.start_cal_period_id) - geh.start_cal_period_id
BULK COLLECT INTO
l_entry_id_list
FROM gcs_cons_eng_run_dtls gcerd,
gcs_entry_headers geh
WHERE gcerd.run_name = p_run_name
AND gcerd.consolidation_entity_id = p_cons_entity_id
AND gcerd.child_entity_id IS NOT NULL
AND gcerd.category_code = p_category_code
AND gcerd.entry_id = geh.entry_id;
SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
geh.year_to_apply_re,
geh.currency_code,
'N',
0
BULK COLLECT INTO
l_entry_id_list
FROM gcs_cons_eng_run_dtls gcerd,
gcs_entry_headers geh
WHERE gcerd.run_name = p_run_name
AND gcerd.consolidation_entity_id = p_cons_entity_id
AND gcerd.child_entity_id = p_entity_id
AND gcerd.category_code = p_category_code
AND gcerd.entry_id = geh.entry_id
AND geh.period_init_entry_flag = 'N'
AND p_cal_period_year < NVL(geh.year_to_apply_re, p_cal_period_year+1);
SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
geh.year_to_apply_re,
geh.currency_code,
'N',
0
BULK COLLECT INTO
l_entry_id_list
FROM gcs_cons_eng_run_dtls gcerd,
gcs_entry_headers geh
WHERE gcerd.run_name = p_run_name
AND gcerd.consolidation_entity_id = p_cons_entity_id
AND gcerd.child_entity_id = p_entity_id
AND gcerd.category_code = p_category_code
AND gcerd.entry_id = geh.entry_id
AND geh.period_init_entry_flag = 'N';
SELECT NVL(gcerd.entry_id, gcerd.stat_entry_id),
geh.year_to_apply_re,
geh.currency_code,
geh.period_init_entry_flag,
NVL(geh.end_cal_period_id, geh.start_cal_period_id) - geh.start_cal_period_id
BULK COLLECT INTO
l_entry_id_list
FROM gcs_cons_eng_run_dtls gcerd,
gcs_entry_headers geh
WHERE gcerd.run_name = p_run_name
AND gcerd.consolidation_entity_id = p_cons_entity_id
AND gcerd.child_entity_id = p_entity_id
AND gcerd.category_code = p_category_code
AND gcerd.entry_id = geh.entry_id;
l_entry_id_list.DELETE(i);
l_entry_id_list.DELETE(i);
l_entry_list.DELETE;
l_currency_code_list.DELETE;
INSERT INTO GCS_ENTRY_LINES_GT
(entry_id,
description,
company_cost_center_org_id,
intercompany_id,
line_item_id,
xtd_balance_e,
ytd_balance_e,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e)
SELECT
--Bugfix 5449718: Remove the references to the init_xlate_entry_id, and removed all group by calcs as this is happening on a line by line basis.
--Also remove join to gcs_entry_headers as all of the information is available in the PL/SQL collection
decode(l_currency_code_list(i), 'STAT', p_init_stat_entry_id,
p_init_entry_id),
decode(feata.dim_attribute_varchar_member,
'REVENUE', 'PROFIT_LOSS',
'EXPENSE', 'PROFIT_LOSS',
'BALANCE_SHEET'),
l2.company_cost_center_org_id,
l2.intercompany_id,
l2.line_item_id,
decode(feata.dim_attribute_varchar_member,
'REVENUE', NVL(ytd_credit_balance_e,0) - NVL(ytd_debit_balance_e,0),
'EXPENSE', NVL(ytd_credit_balance_e,0) - NVL(ytd_debit_balance_e,0),
0),
0,
-1*(ytd_debit_balance_e),
-1*(ytd_credit_balance_e),
0,
0
FROM
GCS_ENTRY_LINES l2,
FEM_LN_ITEMS_ATTR lia,
FEM_EXT_ACCT_TYPES_ATTR feata
WHERE l2.entry_id = l_entry_list(i)
AND lia.attribute_id = g_li_eat_attr_id
AND lia.version_id = g_li_eat_ver_id
AND lia.line_item_id = l2.line_item_id
AND feata.attribute_id = g_acct_type_attr_id
AND feata.version_id = g_acct_type_ver_id
AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
l_entry_list.DELETE;
l_currency_code_list.DELETE;
INSERT INTO GCS_ENTRY_LINES_GT l1
(entry_id,
description,
company_cost_center_org_id,
intercompany_id,
line_item_id,
xtd_balance_e,
ytd_balance_e,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e)
SELECT
--Bugfix 5449718: Remove the references to the init_xlate_entry_id, and removed all group by calcs as this is happening on a line by line basis.
--Also remove join to gcs_entry_headers as all of the information is available in the PL/SQL collection
--Join to line type is no longer required as all rows for recurring entries have the line type code populated
decode(l_currency_code_list(i), 'STAT', p_init_stat_entry_id,
p_init_entry_id),
l2.line_type_code,
l2.company_cost_center_org_id,
l2.intercompany_id,
l2.line_item_id,
--XTD Balance should be determined by the line type code
DECODE(l2.line_type_code, 'PROFIT_LOSS', NVL(ytd_credit_balance_e, 0) - NVL(ytd_debit_balance_e, 0),
0),
0,
-1*(ytd_debit_balance_e),
-1*(ytd_credit_balance_e),
0,
0
FROM
GCS_ENTRY_LINES l2
WHERE l2.entry_id = l_entry_list(i)
AND l2.line_type_code IN ('PROFIT_LOSS', 'BALANCE_SHEET');
SELECT specific_intercompany_id
INTO l_intercompany_id
FROM GCS_CATEGORIES_B
WHERE category_code = 'INTRACOMPANY';
l_entry_list.DELETE;
l_currency_code_list.DELETE;
INSERT INTO GCS_ENTRY_LINES_GT l1
(entry_id,
description,
company_cost_center_org_id,
intercompany_id,
line_item_id,
xtd_balance_e,
ytd_balance_e,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e)
SELECT
--Bugfix 5449718: No longer need the target entries
decode(l_currency_code_list(i), 'STAT', p_init_stat_entry_id,
p_init_entry_id),
'BALANCE_SHEET',
decode(p_bal_by_org,
'Y', l2.company_cost_center_org_id,
decode(feata.dim_attribute_varchar_member,
'REVENUE', l_default_org_id,
'EXPENSE', l_default_org_id,
l2.company_cost_center_org_id)),
-- RE: use org id only if there is no specified intercompany id
decode(feata.dim_attribute_varchar_member,
'REVENUE', nvl(l_intercompany_id,
decode(p_bal_by_org,
'Y', l2.company_cost_center_org_id,
l_default_org_id)),
'EXPENSE', nvl(l_intercompany_id,
decode(p_bal_by_org,
'Y', l2.company_cost_center_org_id,
l_default_org_id)),
l2.intercompany_id),
-- line item (cannot be secondary tracking column)
decode(feata.dim_attribute_varchar_member,
'REVENUE', p_re_template.line_item_id,
'EXPENSE', p_re_template.line_item_id,
l2.line_item_id),
--Bugfix 5449718: If net to re flag is N then all balances except ptd debit and ptd credit will be zero
0,
0,
-1*(l2.ytd_debit_balance_e),
-1*(l2.ytd_credit_balance_e),
0,
0
FROM
--Bugfix 5449718: Remove source, target entry, and category joins.
GCS_ENTRY_LINES l2,
FEM_LN_ITEMS_ATTR lia,
FEM_EXT_ACCT_TYPES_ATTR feata
WHERE
l2.entry_id = l_entry_list(i)
AND lia.attribute_id = g_li_eat_attr_id
AND lia.version_id = g_li_eat_ver_id
AND lia.line_item_id = l2.line_item_id
AND feata.attribute_id = g_acct_type_attr_id
AND feata.version_id = g_acct_type_ver_id
AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
SELECT specific_intercompany_id
INTO l_intercompany_id
FROM GCS_CATEGORIES_B
WHERE category_code = 'INTRACOMPANY';
l_entry_list.DELETE;
l_currency_code_list.DELETE;
INSERT INTO GCS_ENTRY_LINES_GT l1
(entry_id,
description,
company_cost_center_org_id,
intercompany_id,
line_item_id,
xtd_balance_e,
ytd_balance_e,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e)
SELECT
--Bugfix 5449718: No longer need the target entries
decode(l_currency_code_list(i), 'STAT', p_recur_stat_entry_id,
p_recur_entry_id),
'BALANCE_SHEET',
decode(p_bal_by_org,
'Y', l2.company_cost_center_org_id,
decode(feata.dim_attribute_varchar_member,
'REVENUE', l_default_org_id,
'EXPENSE', l_default_org_id,
l2.company_cost_center_org_id)),
-- RE: use org id only if there is no specified intercompany id
decode(feata.dim_attribute_varchar_member,
'REVENUE', nvl(l_intercompany_id,
decode(p_bal_by_org,
'Y', l2.company_cost_center_org_id,
l_default_org_id)),
'EXPENSE', nvl(l_intercompany_id,
decode(p_bal_by_org,
'Y', l2.company_cost_center_org_id,
l_default_org_id)),
l2.intercompany_id),
-- line item (cannot be secondary tracking column)
decode(feata.dim_attribute_varchar_member,
'REVENUE', p_re_template.line_item_id,
'EXPENSE', p_re_template.line_item_id,
l2.line_item_id),
--Bugfix 5449718: If net to re flag is Y then all balances except ptd debit and ptd credit will be zero
ytd_balance_e,
ytd_balance_e,
0,
0,
ytd_debit_balance_e,
ytd_credit_balance_e
FROM
--Bugfix 5449718: Remove source, target entry, and category joins. Add join to fem_ext_acct_types_attr
GCS_ENTRY_LINES l2,
FEM_LN_ITEMS_ATTR lia,
FEM_EXT_ACCT_TYPES_ATTR feata
WHERE
l2.entry_id = l_entry_list(i)
AND lia.attribute_id = g_li_eat_attr_id
AND lia.version_id = g_li_eat_ver_id
AND lia.line_item_id = l2.line_item_id
AND feata.attribute_id = g_acct_type_attr_id
AND feata.version_id = g_acct_type_ver_id
AND feata.ext_account_type_code = lia.dim_attribute_varchar_member;
l_entry_list.DELETE;
l_currency_code_list.DELETE;
INSERT INTO GCS_ENTRY_LINES_GT l1
(entry_id,
description,
company_cost_center_org_id,
intercompany_id,
line_item_id,
xtd_balance_e,
ytd_balance_e,
ptd_debit_balance_e,
ptd_credit_balance_e,
ytd_debit_balance_e,
ytd_credit_balance_e)
SELECT
--Bugfix 5449718: No longer need the target entries
decode(l_currency_code_list(i), 'STAT', p_recur_stat_entry_id,
p_recur_entry_id),
'BALANCE_SHEET',
l2.company_cost_center_org_id,
l2.intercompany_id,
l2.line_item_id,
--Bugfix 5449718: If net to re flag is N then all balances except ptd debit and ptd credit will be zero
0,
0,
-1*ytd_debit_balance_e,
-1*ytd_credit_balance_e,
0,
0
FROM
--Bugfix 5449718: Remove source, target entry, and category joins.
GCS_ENTRY_LINES l2
WHERE
l2.entry_id = l_entry_list(i)
AND l2.line_type_code IN ('CALCULATED', 'BALANCE_SHEET');
INSERT INTO gcs_entry_lines
(entry_id,
line_type_code,
company_cost_center_org_id,
intercompany_id,
line_item_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,
MIN(description),
company_cost_center_org_id,
intercompany_id,
line_item_id,
SUM(NVL(xtd_balance_e,0)),
SUM(NVL(ytd_balance_e,0)),
SUM(NVL(ptd_debit_balance_e,0)),
SUM(NVL(ptd_credit_balance_e,0)),
SUM(NVL(ytd_debit_balance_e,0)),
SUM(NVL(ytd_credit_balance_e,0)),
sysdate,
GCS_PERIOD_INIT_PKG.g_fnd_user_id,
sysdate,
GCS_PERIOD_INIT_PKG.g_fnd_user_id,
GCS_PERIOD_INIT_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_entry_lines;