[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT entry_id, entity_id, currency_code
BULK COLLECT INTO l_entry_id_list, l_entity_id_list, l_currency_code_list
FROM GCS_ENTRY_HEADERS
WHERE entry_id = p_entry_id;
SELECT ghd.entry_id, ghd.entity_id, ghd.currency_code
BULK COLLECT INTO l_entry_id_list, l_entity_id_list, l_currency_code_list
FROM GCS_CONS_ENG_RUN_DTLS GCR,
GCS_ENTRY_HEADERS GHD
WHERE GCR.run_detail_id = p_run_detail_id
AND GHD.entry_id in ( GCR.entry_id, GCR.stat_entry_id);
INSERT
INTO GCS_FEM_POSTING_GT(
ENTRY_ID,
SEQUENCE_NUM,
CURRENCY_CODE,
COMPANY_COST_CENTER_ORG_ID,
INTERCOMPANY_ID,
ENTITY_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
l_entry_id_list(i),
GCS_FEM_BAL_S.nextval,
l_currency_code_list(i),
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
l_entity_id_list(i),
GLE.LINE_ITEM_ID,
nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E),
GLE.YTD_BALANCE_E,
nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E),
nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E),
GLE.YTD_DEBIT_BALANCE_E,
GLE.YTD_CREDIT_BALANCE_E
FROM GCS_ENTRY_LINES GLE
WHERE GLE.entry_id = l_entry_id_list(i);
SELECT 'Y'
INTO l_recur_entry_flag
FROM dual
WHERE EXISTS
(SELECT 1
FROM GCS_CONS_ENG_RUN_DTLS GCERD,
GCS_ENTRY_HEADERS GHD
WHERE GCERD.run_name = p_run_name
AND GCERD.consolidation_entity_id = p_cons_entity_id
AND GCERD.child_entity_id = p_child_entity_id
AND GCERD.category_code = p_category_code
AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
AND (GHD.end_cal_period_id IS NULL OR ghd.start_cal_period_id <> ghd.end_cal_period_id));
INSERT
INTO GCS_FEM_POSTING_GT(
ENTRY_ID,
SEQUENCE_NUM,
CURRENCY_CODE,
COMPANY_COST_CENTER_ORG_ID,
INTERCOMPANY_ID,
ENTITY_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
GFB.ENTRY_ID,
GCS_FEM_BAL_S.nextval,
GFB.CURRENCY_CODE,
GFB.COMPANY_COST_CENTER_ORG_ID,
GFB.INTERCOMPANY_ID,
GFB.ENTITY_ID,
GFB.LINE_ITEM_ID,
GFB.XTD_BALANCE_E,
GFB.YTD_BALANCE_E,
GFB.PTD_DEBIT_BALANCE_E,
GFB.PTD_CREDIT_BALANCE_E,
GFB.YTD_DEBIT_BALANCE_E,
GFB.YTD_CREDIT_BALANCE_E
FROM (
SELECT max(GHD.entry_id) entry_id,
GHD.currency_code,
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
GLE.LINE_ITEM_ID,
max(GHD.ENTITY_ID) ENTITY_ID,
sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
FROM GCS_CONS_ENG_RUN_DTLS GCERD,
GCS_ENTRY_HEADERS GHD,
GCS_ENTRY_LINES GLE
WHERE GCERD.run_name = p_run_name
AND GCERD.consolidation_entity_id = p_cons_entity_id
AND GCERD.child_entity_id = p_child_entity_id
AND GCERD.category_code = p_category_code
AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
AND GLE.entry_id = GHD.entry_id
AND ((GHD.start_cal_period_id = GHD.end_cal_period_id)
OR ((GHD.start_cal_period_id <> GHD.end_cal_period_id OR GHD.end_cal_period_id is NULL)
AND (GHD.year_to_apply_re IS NULL OR (p_cal_period_year >= GHD.year_to_apply_re AND GLE.line_type_code <> 'PROFIT_LOSS')
OR (p_cal_period_year < GHD.year_to_apply_re AND GLE.line_type_code <> 'CALCULATED'))))
GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
GLE.intercompany_id ) GFB;
INSERT
INTO GCS_FEM_POSTING_GT(
ENTRY_ID,
SEQUENCE_NUM,
CURRENCY_CODE,
COMPANY_COST_CENTER_ORG_ID,
INTERCOMPANY_ID,
ENTITY_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
GFB.ENTRY_ID,
GCS_FEM_BAL_S.nextval,
GFB.CURRENCY_CODE,
GFB.COMPANY_COST_CENTER_ORG_ID,
GFB.INTERCOMPANY_ID,
GFB.ENTITY_ID,
GFB.LINE_ITEM_ID,
GFB.XTD_BALANCE_E,
GFB.YTD_BALANCE_E,
GFB.PTD_DEBIT_BALANCE_E,
GFB.PTD_CREDIT_BALANCE_E,
GFB.YTD_DEBIT_BALANCE_E,
GFB.YTD_CREDIT_BALANCE_E
FROM (
SELECT max(GHD.entry_id) entry_id,
GHD.currency_code,
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
GLE.LINE_ITEM_ID,
max(GHD.ENTITY_ID) ENTITY_ID,
sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
FROM GCS_CONS_ENG_RUN_DTLS GCERD,
GCS_ENTRY_HEADERS GHD,
GCS_ENTRY_LINES GLE
WHERE GCERD.run_name = p_run_name
AND GCERD.consolidation_entity_id = p_cons_entity_id
AND GCERD.child_entity_id = p_child_entity_id
AND GCERD.category_code = p_category_code
AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
AND GLE.entry_id = GHD.entry_id
GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
GLE.intercompany_id ) GFB;
SELECT 'Y'
INTO l_recur_entry_flag
FROM dual
WHERE EXISTS
(SELECT 1
FROM GCS_CONS_ENG_RUN_DTLS GCERD,
GCS_ENTRY_HEADERS GHD
WHERE GCERD.run_name = p_run_name
AND GCERD.consolidation_entity_id = p_cons_entity_id
AND GCERD.category_code = p_category_code
AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
AND (GHD.end_cal_period_id IS NULL OR ghd.start_cal_period_id <> ghd.end_cal_period_id));
INSERT
INTO GCS_FEM_POSTING_GT(
ENTRY_ID,
SEQUENCE_NUM,
CURRENCY_CODE,
COMPANY_COST_CENTER_ORG_ID,
INTERCOMPANY_ID,
ENTITY_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
GFB.ENTRY_ID,
GCS_FEM_BAL_S.nextval,
GFB.CURRENCY_CODE,
GFB.COMPANY_COST_CENTER_ORG_ID,
GFB.INTERCOMPANY_ID,
GFB.ENTITY_ID,
GFB.LINE_ITEM_ID,
GFB.XTD_BALANCE_E,
GFB.YTD_BALANCE_E,
GFB.PTD_DEBIT_BALANCE_E,
GFB.PTD_CREDIT_BALANCE_E,
GFB.YTD_DEBIT_BALANCE_E,
GFB.YTD_CREDIT_BALANCE_E
FROM (
SELECT max(GHD.entry_id) entry_id,
GHD.currency_code,
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
GLE.LINE_ITEM_ID,
max(GHD.ENTITY_ID) ENTITY_ID,
sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
FROM GCS_CONS_ENG_RUN_DTLS GCERD,
GCS_ENTRY_HEADERS GHD,
GCS_ENTRY_LINES GLE
WHERE GCERD.run_name = p_run_name
AND GCERD.consolidation_entity_id = p_cons_entity_id
AND GCERD.category_code = p_category_code
AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
AND GLE.entry_id = GHD.entry_id
AND ((GHD.start_cal_period_id = GHD.end_cal_period_id)
OR ((GHD.start_cal_period_id <> GHD.end_cal_period_id OR GHD.end_cal_period_id is NULL)
AND (GHD.year_to_apply_re IS NULL OR (p_cal_period_year >= GHD.year_to_apply_re AND GLE.line_type_code <> 'PROFIT_LOSS')
OR (p_cal_period_year < GHD.year_to_apply_re AND GLE.line_type_code <> 'CALCULATED'))))
GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
GLE.intercompany_id ) GFB;
INSERT
INTO GCS_FEM_POSTING_GT(
ENTRY_ID,
SEQUENCE_NUM,
CURRENCY_CODE,
COMPANY_COST_CENTER_ORG_ID,
INTERCOMPANY_ID,
ENTITY_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
GFB.ENTRY_ID,
GCS_FEM_BAL_S.nextval,
GFB.CURRENCY_CODE,
GFB.COMPANY_COST_CENTER_ORG_ID,
GFB.INTERCOMPANY_ID,
GFB.ENTITY_ID,
GFB.LINE_ITEM_ID,
GFB.XTD_BALANCE_E,
GFB.YTD_BALANCE_E,
GFB.PTD_DEBIT_BALANCE_E,
GFB.PTD_CREDIT_BALANCE_E,
GFB.YTD_DEBIT_BALANCE_E,
GFB.YTD_CREDIT_BALANCE_E
FROM (
SELECT max(GHD.entry_id) entry_id,
GHD.currency_code,
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
GLE.LINE_ITEM_ID,
max(GHD.ENTITY_ID) ENTITY_ID,
sum(nvl(GLE.XTD_BALANCE_E, GLE.YTD_BALANCE_E)) XTD_BALANCE_E,
sum(GLE.YTD_BALANCE_E) YTD_BALANCE_E,
sum(nvl(GLE.PTD_DEBIT_BALANCE_E, GLE.YTD_DEBIT_BALANCE_E)) PTD_DEBIT_BALANCE_E,
sum(nvl(GLE.PTD_CREDIT_BALANCE_E, GLE.YTD_CREDIT_BALANCE_E)) PTD_CREDIT_BALANCE_E,
sum(GLE.YTD_DEBIT_BALANCE_E) YTD_DEBIT_BALANCE_E,
sum(GLE.YTD_CREDIT_BALANCE_E) YTD_CREDIT_BALANCE_E
FROM GCS_CONS_ENG_RUN_DTLS GCERD,
GCS_ENTRY_HEADERS GHD,
GCS_ENTRY_LINES GLE
WHERE GCERD.run_name = p_run_name
AND GCERD.consolidation_entity_id = p_cons_entity_id
AND GCERD.category_code = p_category_code
AND GHD.entry_id in (GCERD.entry_id, GCERD.stat_entry_id)
AND GLE.entry_id = GHD.entry_id
GROUP BY GHD.currency_code, GLE.company_cost_center_org_id, GLE.line_item_id,
GLE.intercompany_id ) GFB;
PROCEDURE Process_Insert( p_hier_dataset_code NUMBER,
p_object_id NUMBER,
p_category_code VARCHAR2,
p_cons_entity_id NUMBER,
p_child_entity_id NUMBER,
p_cal_period_id NUMBER,
p_cal_period_year NUMBER,
p_ledger_id NUMBER,
p_run_name VARCHAR2,
p_run_detail_id NUMBER,
p_entry_id NUMBER,
p_undo VARCHAR2,
p_xlate VARCHAR2,
--Bugfix 5646770: Added parameter for topmost entity flag
p_topmost_entity_flag VARCHAR2,
errbuf IN OUT NOCOPY VARCHAR2,
retcode IN OUT NOCOPY VARCHAR2
) IS
l_req_id NUMBER := FND_GLOBAL.conc_request_id;
FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT.begin', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
INSERT INTO FEM_BALANCES(
DATASET_CODE,
CAL_PERIOD_ID,
CREATION_ROW_SEQUENCE,
SOURCE_SYSTEM_CODE,
LEDGER_ID,
COMPANY_COST_CENTER_ORG_ID,
CURRENCY_CODE,
CURRENCY_TYPE_CODE,
INTERCOMPANY_ID,
ENTITY_ID,
LINE_ITEM_ID,
CREATED_BY_REQUEST_ID,
CREATED_BY_OBJECT_ID,
LAST_UPDATED_BY_REQUEST_ID,
LAST_UPDATED_BY_OBJECT_ID,
XTD_BALANCE_E,
YTD_BALANCE_E,
PTD_DEBIT_BALANCE_E,
PTD_CREDIT_BALANCE_E,
YTD_DEBIT_BALANCE_E,
YTD_CREDIT_BALANCE_E,
--Bugfix 5646770: Added _F Columns for Top Most Entity
XTD_BALANCE_F,
YTD_BALANCE_F
)
SELECT
p_hier_dataset_code,
p_cal_period_id,
sequence_num,
g_src_sys_code,
p_ledger_id,
company_cost_center_org_id,
currency_code,
'TOTAL',
intercompany_id,
entity_id,
line_item_id,
l_req_id,
p_object_id,
l_req_id,
p_object_id,
XTD_BALANCE_E,
YTD_BALANCE_E,
PTD_DEBIT_BALANCE_E,
PTD_CREDIT_BALANCE_E,
YTD_DEBIT_BALANCE_E,
YTD_CREDIT_BALANCE_E,
--Bugfix 5646770: Added _F Columns for topmost entity
DECODE(p_topmost_entity_flag, 'Y', XTD_BALANCE_E, NULL) XTD_BALANCE_F,
DECODE(p_topmost_entity_flag, 'Y', YTD_BALANCE_E, NULL) YTD_BALANCE_F
FROM GCS_FEM_POSTING_GT;
FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT.end', to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT', 'GCS_NO_DATA_FOUND');
FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.PROCESS_INSERT', errbuf);
END Process_Insert;
SELECT
p_hier_dataset_code DATASET_CODE,
p_cal_period_id CAL_PERIOD_ID,
g_src_sys_code SOURCE_SYSTEM_CODE,
p_ledger_id LEDGER_ID,
GLE.SEQUENCE_NUM CREATION_ROW_SEQUENCE,
l_req_id CREATED_BY_REQUEST_ID,
p_object_id CREATED_BY_OBJECT_ID,
l_req_id LAST_UPDATED_BY_REQUEST_ID,
p_object_id LAST_UPDATED_BY_OBJECT_ID,
GLE.COMPANY_COST_CENTER_ORG_ID COMPANY_COST_CENTER_ORG_ID,
GLE.LINE_ITEM_ID LINE_ITEM_ID,
GLE.INTERCOMPANY_ID INTERCOMPANY_ID,
GLE.CURRENCY_CODE CURRENCY_CODE,
GLE.ENTITY_ID ENTITY_ID,
GLE.XTD_BALANCE_E,
GLE.YTD_BALANCE_E YTD_BALANCE_E,
GLE.PTD_DEBIT_BALANCE_E,
GLE.PTD_CREDIT_BALANCE_E,
GLE.YTD_DEBIT_BALANCE_E YTD_DEBIT_BALANCE_E,
GLE.YTD_CREDIT_BALANCE_E YTD_CREDIT_BALANCE_E
FROM GCS_FEM_POSTING_GT GLE) GFB
ON (
FB.CREATED_BY_OBJECT_ID = GFB.CREATED_BY_OBJECT_ID
AND FB.CREATED_BY_REQUEST_ID = GFB.CREATED_BY_REQUEST_ID
AND FB.CREATION_ROW_SEQUENCE = GFB.CREATION_ROW_SEQUENCE)
WHEN MATCHED THEN UPDATE SET
FB.xtd_balance_e = GFB.xtd_balance_e,
FB.ptd_credit_balance_e = GFB.ptd_credit_balance_e,
FB.ptd_debit_balance_e = GFB.ptd_debit_balance_e,
FB.ytd_balance_e = GFB.ytd_balance_e,
FB.ytd_credit_balance_e = GFB.ytd_credit_balance_e,
FB.ytd_debit_balance_e = GFB.ytd_debit_balance_e
WHEN NOT MATCHED THEN INSERT
(
FB.DATASET_CODE,
FB.CAL_PERIOD_ID,
FB.CREATION_ROW_SEQUENCE,
FB.SOURCE_SYSTEM_CODE,
FB.LEDGER_ID,
FB.COMPANY_COST_CENTER_ORG_ID,
FB.CURRENCY_CODE,
FB.CURRENCY_TYPE_CODE,
FB.LINE_ITEM_ID,
FB.ENTITY_ID,
FB.INTERCOMPANY_ID,
FB.CREATED_BY_REQUEST_ID,
FB.CREATED_BY_OBJECT_ID,
FB.LAST_UPDATED_BY_REQUEST_ID,
FB.LAST_UPDATED_BY_OBJECT_ID,
FB.XTD_BALANCE_E,
FB.YTD_BALANCE_E,
FB.PTD_DEBIT_BALANCE_E,
FB.PTD_CREDIT_BALANCE_E,
FB.YTD_DEBIT_BALANCE_E,
FB.YTD_CREDIT_BALANCE_E
)
VALUES
(
GFB.DATASET_CODE,
GFB.CAL_PERIOD_ID,
GFB.CREATION_ROW_SEQUENCE,
GFB.SOURCE_SYSTEM_CODE,
GFB.LEDGER_ID,
GFB.COMPANY_COST_CENTER_ORG_ID,
GFB.CURRENCY_CODE,
'TOTAL',
GFB.LINE_ITEM_ID,
GFB.ENTITY_ID,
GFB.INTERCOMPANY_ID,
GFB.CREATED_BY_REQUEST_ID,
GFB.CREATED_BY_OBJECT_ID,
GFB.LAST_UPDATED_BY_REQUEST_ID,
GFB.LAST_UPDATED_BY_OBJECT_ID,
GFB.XTD_BALANCE_E,
GFB.YTD_BALANCE_E,
GFB.PTD_DEBIT_BALANCE_E,
GFB.PTD_CREDIT_BALANCE_E,
GFB.YTD_DEBIT_BALANCE_E,
GFB.YTD_CREDIT_BALANCE_E);
SELECT
p_hier_dataset_code DATASET_CODE,
p_cal_period_id CAL_PERIOD_ID,
g_src_sys_code SOURCE_SYSTEM_CODE,
p_ledger_id LEDGER_ID,
GLE.SEQUENCE_NUM CREATION_ROW_SEQUENCE,
l_req_id CREATED_BY_REQUEST_ID,
p_object_id CREATED_BY_OBJECT_ID,
l_req_id LAST_UPDATED_BY_REQUEST_ID,
p_object_id LAST_UPDATED_BY_OBJECT_ID,
GLE.COMPANY_COST_CENTER_ORG_ID COMPANY_COST_CENTER_ORG_ID,
GLE.LINE_ITEM_ID LINE_ITEM_ID,
GLE.INTERCOMPANY_ID INTERCOMPANY_ID,
GLE.CURRENCY_CODE CURRENCY_CODE,
GLE.ENTITY_ID ENTITY_ID,
GLE.XTD_BALANCE_E,
GLE.YTD_BALANCE_E YTD_BALANCE_E,
GLE.PTD_DEBIT_BALANCE_E,
GLE.PTD_CREDIT_BALANCE_E,
GLE.YTD_DEBIT_BALANCE_E YTD_DEBIT_BALANCE_E,
GLE.YTD_CREDIT_BALANCE_E YTD_CREDIT_BALANCE_E
FROM GCS_FEM_POSTING_GT GLE) GFB
ON (
FB.CREATED_BY_OBJECT_ID = GFB.CREATED_BY_OBJECT_ID
AND FB.CREATED_BY_REQUEST_ID = GFB.CREATED_BY_REQUEST_ID
AND FB.CREATION_ROW_SEQUENCE = GFB.CREATION_ROW_SEQUENCE)
WHEN MATCHED THEN UPDATE SET
FB.xtd_balance_e = FB.xtd_balance_e + GFB.xtd_balance_e,
FB.ptd_credit_balance_e = FB.ptd_credit_balance_e + GFB.ptd_credit_balance_e,
FB.ptd_debit_balance_e = FB.ptd_debit_balance_e + GFB.ptd_debit_balance_e,
FB.ytd_balance_e = FB.ytd_balance_e + GFB.ytd_balance_e,
FB.ytd_credit_balance_e = FB.ytd_credit_balance_e + GFB.ytd_credit_balance_e,
FB.ytd_debit_balance_e = FB.ytd_debit_balance_e + GFB.ytd_debit_balance_e
WHEN NOT MATCHED THEN INSERT
(
FB.DATASET_CODE,
FB.CAL_PERIOD_ID,
FB.CREATION_ROW_SEQUENCE,
FB.SOURCE_SYSTEM_CODE,
FB.LEDGER_ID,
FB.COMPANY_COST_CENTER_ORG_ID,
FB.CURRENCY_CODE,
FB.CURRENCY_TYPE_CODE,
FB.LINE_ITEM_ID,
FB.ENTITY_ID,
FB.INTERCOMPANY_ID,
FB.CREATED_BY_REQUEST_ID,
FB.CREATED_BY_OBJECT_ID,
FB.LAST_UPDATED_BY_REQUEST_ID,
FB.LAST_UPDATED_BY_OBJECT_ID,
FB.XTD_BALANCE_E,
FB.YTD_BALANCE_E,
FB.PTD_DEBIT_BALANCE_E,
FB.PTD_CREDIT_BALANCE_E,
FB.YTD_DEBIT_BALANCE_E,
FB.YTD_CREDIT_BALANCE_E
)
VALUES
(
GFB.DATASET_CODE,
GFB.CAL_PERIOD_ID,
GFB.CREATION_ROW_SEQUENCE,
GFB.SOURCE_SYSTEM_CODE,
GFB.LEDGER_ID,
GFB.COMPANY_COST_CENTER_ORG_ID,
GFB.CURRENCY_CODE,
'TOTAL',
GFB.LINE_ITEM_ID,
GFB.ENTITY_ID,
GFB.INTERCOMPANY_ID,
GFB.CREATED_BY_REQUEST_ID,
GFB.CREATED_BY_OBJECT_ID,
GFB.LAST_UPDATED_BY_REQUEST_ID,
GFB.LAST_UPDATED_BY_OBJECT_ID,
GFB.XTD_BALANCE_E,
GFB.YTD_BALANCE_E,
GFB.PTD_DEBIT_BALANCE_E,
GFB.PTD_CREDIT_BALANCE_E,
GFB.YTD_DEBIT_BALANCE_E,
GFB.YTD_CREDIT_BALANCE_E);
INSERT
INTO GCS_FEM_CONTRIBUTIONS_H(
DATASET_CODE,
CAL_PERIOD_ID,
CREATED_BY_OBJECT_ID,
CREATION_ROW_SEQUENCE,
ENTRY_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN )
SELECT
p_hier_dataset_code,
p_cal_period_id,
P_object_id,
GFPG.sequence_num,
GFPG.entry_id,
sysdate,
l_user_id,
sysdate,
l_user_id,
l_login_id
FROM GCS_FEM_POSTING_GT GFPG;
SELECT fem_ledger_id
INTO l_ledger_id
FROM GCS_HIERARCHIES_B
WHERE hierarchy_id = p_hierarchy_id;
SELECT associated_object_id
INTO l_object_id
FROM GCS_CATEGORIES_B
WHERE category_code = p_category_code;
SELECT DECODE(top_entity_id, p_cons_entity_id, 'Y', 'N')
INTO l_topmost_entity_flag
FROM gcs_hierarchies_b
WHERE hierarchy_id = p_hierarchy_id;
SELECT child_entity_id
INTO l_entity_id
FROM gcs_cons_eng_run_dtls
WHERE run_detail_id = p_run_detail_id;
DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
WHERE dataset_code = p_hier_dataset_code
AND cal_period_id = p_cal_period_id
AND ledger_id = l_ledger_id
AND created_by_object_id = l_object_id
AND source_system_code = g_src_sys_code
AND entity_id = l_entity_id;
SELECT entity_id
INTO l_entity_id
FROM gcs_entry_headers
WHERE entry_id = p_entry_id;
DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
WHERE dataset_code = p_hier_dataset_code
AND cal_period_id = p_cal_period_id
AND ledger_id = l_ledger_id
AND created_by_object_id = l_object_id
AND source_system_code = g_src_sys_code
AND entity_id = l_entity_id;
process_insert( p_hier_dataset_code => p_hier_dataset_code,
p_object_id => l_object_id,
p_category_code => p_category_code,
p_cons_entity_id => p_cons_entity_id,
p_child_entity_id => p_child_entity_id,
p_cal_period_id => p_cal_period_id,
p_cal_period_year => l_cal_period_year,
p_ledger_id => l_ledger_id,
p_run_name => p_run_name,
p_run_detail_id => p_run_detail_id,
p_entry_id => p_entry_id,
p_undo => p_undo,
p_xlate => p_xlate,
--Bugfix 5646770: Added parameter for topmost entity flag
p_topmost_entity_flag => l_topmost_entity_flag,
errbuf => errbuf,
retcode => retcode);
PROCEDURE Gcs_Fem_Delete(
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY VARCHAR2,
p_hierarchy_id NUMBER,
p_balance_type_code VARCHAR2,
p_cal_period_id NUMBER,
p_entity_type VARCHAR2,
p_entity_id NUMBER,
p_hier_dataset_code NUMBER) IS
l_ledger_id NUMBER;
FND_LOG.STRING(procedureloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE.begin' || GCS_UTILITY_PKG.g_module_enter, to_char(sysdate, 'DD-MON-YYYY HH:MI:SS'));
FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_hierarchy_id = ' || to_char(p_hierarchy_id));
FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_cal_period_id = ' || to_char(p_cal_period_id));
FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_balance_type_code = ' || p_balance_type_code);
FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_entity_id = ' || to_char(p_entity_id));
FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'p_entity_type = ' || p_entity_type);
SELECT ghb.fem_ledger_id
INTO l_ledger_id
FROM gcs_hierarchies_b ghb
WHERE ghb.hierarchy_id = p_hierarchy_id;
SELECT nvl(dim_attribute_numeric_member, -1)
INTO l_oper_entity_id
FROM fem_entities_attr
WHERE entity_id = p_entity_id
AND version_id = g_oper_entity_ver
AND attribute_id = g_oper_entity_attr;
SELECT dim_attribute_numeric_member
INTO l_elim_entity_id
FROM fem_entities_attr
WHERE entity_id = p_entity_id
AND version_id = g_elim_entity_ver
AND attribute_id = g_elim_entity_attr;
SELECT associated_object_id
BULK COLLECT INTO l_objects_id
FROM gcs_categories_b
WHERE category_type_code IN ('ELIMINATION_RULE', 'CONSOLIDATION_RULE')
AND target_entity_code IN ('PARENT', 'ELIMINATION');
DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
WHERE dataset_code = p_hier_dataset_code
AND cal_period_id = p_cal_period_id
AND source_system_code = g_src_sys_code
AND ledger_id = l_ledger_id
AND entity_id IN (l_oper_entity_id, l_elim_entity_id)
AND created_by_object_id = l_objects_id(i);
SELECT associated_object_id
BULK COLLECT INTO l_objects_id
FROM gcs_categories_b
WHERE category_type_code IN ('ELIMINATION_RULE', 'CONSOLIDATION_RULE')
AND target_entity_code = 'CHILD';
DELETE /*+ INDEX(FEM_BALANCES FEM_BALANCES_N4) */ FROM FEM_BALANCES
WHERE dataset_code = p_hier_dataset_code
AND cal_period_id = p_cal_period_id
AND source_system_code = g_src_sys_code
AND ledger_id = l_ledger_id
AND entity_id = p_entity_id
AND created_by_object_id = l_objects_id(i);
FND_LOG.STRING(statementloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE.rowcount ', to_char(SQL%ROWCOUNT));
FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', 'GCS_NO_DATA_FOUND');
FND_LOG.STRING(unexpectedloglevel, 'gcs.plsql.GCS_FEM_POSTING_PKG.GCS_FEM_DELETE', errbuf);
END Gcs_Fem_Delete;