[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,
';
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,
';
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,
';
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,
';
SELECT max(GHD.entry_id) entry_id,
GHD.currency_code,
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
GLE.LINE_ITEM_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,
';
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,
';
SELECT max(GHD.entry_id) entry_id,
GHD.currency_code,
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
GLE.LINE_ITEM_ID,
';
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,
';
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,
';
SELECT max(GHD.entry_id) entry_id,
GHD.currency_code,
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
GLE.LINE_ITEM_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,
';
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,
';
SELECT max(GHD.entry_id) entry_id,
GHD.currency_code,
GLE.COMPANY_COST_CENTER_ORG_ID,
GLE.INTERCOMPANY_ID,
GLE.LINE_ITEM_ID,
';
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,
';
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,
';
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,
';
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.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.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,
';
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.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.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;