VIEW: APPS.GL_CONSOLIDATION_HISTORY_V

Source

View Text - Preformatted

SELECT GL_CS.consolidation_set_id CONSOLIDATION_SET_ID, GL_CS.name CONSOLIDATION_SET_NAME, C.consolidation_id CONSOLIDATION_ID, C.name CONSOLIDATION_NAME, M.coa_mapping_id MAPPING_ID, M.name MAPPING_NAME, LEDGER1.ledger_id FROM_LEDGER_ID, LEDGER2.ledger_id TO_LEDGER_ID, gl_fc_xlate_pkg.get_ledger_name(ledger1.name, ledger1.ledger_id, c.from_currency_code) FROM_LEDGER_NAME, LEDGER2.name TO_LEDGER_NAME, LEDGER2.chart_of_accounts_id TO_CHART_OF_ACCOUNTS_ID, HIS.actual_flag ACTUAL_FLAG, NVL(HIS.status, 'NT') STATUS_CODE, NVL(HIS.obsolete_consolidation_flag, 'N') OBSOLETE_CONSOLIDATION_FLAG, FLV.meaning OBSOLETE_CONSOLIDATION_STATUS, HIS.request_id REQ_ID, LEDGER2.currency_code TO_CURRENCY_CODE, LEDGER1.currency_code FROM_CURRENCY_CODE, HIS.from_period_name FROM_PERIOD_NAME, C.method METHOD_FLAG, HIS.from_budget_name SOURCE_BUDGET, HIS.to_budget_name TARGET_BUDGET, HIS.from_budget_version_id SOURCE_BUDGET_VERSION_ID, HIS.to_budget_version_id TARGET_BUDGET_VERSION_ID, C.description CONSOLIDATION_DESCRIPTION, GL_CS.description CONSOLIDATION_SET_DESCRIPTION, M.description MAPPING_DESCRIPTION, HIS.to_period_name TO_PERIOD_NAME, HIS.je_batch_id JE_BATCH_ID, JB.control_total JB_CONTROL_TOTAL, JB.running_total_dr JB_RUNNING_TOTAL_DR, JB.running_total_cr JB_RUNNING_TOTAL_CR, JB.rowid JB_ROW_ID, JB.chart_of_accounts_id JB_CHART_OF_ACCOUNTS_ID, JB.name JB_NAME, JB.status JB_STATUS, JB.status_verified JB_STATUS_VERIFIED, JB.actual_flag JB_ACTUAL_FLAG, JB.budgetary_control_status JB_BUDGETARY_CONTROL_STATUS, JB.default_period_name JB_DEFAULT_PERIOD_NAME, JB.posting_run_id JB_POSTING_RUN_ID, JB.request_id JB_REQUEST_ID, JB.approval_status_code JB_APPROVAL_STATUS_CODE, JB.last_update_date JB_LAST_UPDATE_DATE, JB.last_updated_by JB_LAST_UPDATED_BY, JB.last_update_login JB_LAST_UPDATE_LOGIN, HIS.rowid ROW_ID, HIS.last_update_date LAST_UPDATE_DATE, HIS.last_updated_by LAST_UPDATED_BY, HIS.consolidation_run_id CONSOLIDATION_RUN_ID FROM gl_je_batches JB, (select * from gl_consolidation_history WHERE status is not null) HIS, gl_consolidation_sets GL_CS, gl_consolidation C, gl_coa_mappings M, gl_ledgers LEDGER1, gl_ledgers LEDGER2, fnd_lookup_values FLV WHERE C.from_ledger_id = LEDGER1.ledger_id AND C.to_ledger_id = LEDGER2.ledger_id AND HIS.consolidation_id(+) = C.consolidation_id+0 AND HIS.to_period_name(+) = GL_CONS_WRK_PKG.get_period AND GL_CS.consolidation_set_id(+) = HIS.consolidation_set_id AND JB.je_batch_id(+) = HIS.je_batch_id AND EXISTS (SELECT 1 FROM gl_access_set_ledgers gl_asl WHERE gl_asl.access_set_id = GL_CONS_WRK_PKG.get_access_set_id AND gl_asl.ledger_id IN (C.from_ledger_id, C.to_ledger_id)) AND FLV.lookup_type = 'OBSOLETE_CONSOLIDATION_STATUS' AND FLV.language = userenv('LANG') AND FLV.lookup_code = NVL(HIS.obsolete_consolidation_flag, 'N') AND c.coa_mapping_id = m.coa_mapping_id

View Text - HTML Formatted

SELECT GL_CS.CONSOLIDATION_SET_ID CONSOLIDATION_SET_ID
, GL_CS.NAME CONSOLIDATION_SET_NAME
, C.CONSOLIDATION_ID CONSOLIDATION_ID
, C.NAME CONSOLIDATION_NAME
, M.COA_MAPPING_ID MAPPING_ID
, M.NAME MAPPING_NAME
, LEDGER1.LEDGER_ID FROM_LEDGER_ID
, LEDGER2.LEDGER_ID TO_LEDGER_ID
, GL_FC_XLATE_PKG.GET_LEDGER_NAME(LEDGER1.NAME
, LEDGER1.LEDGER_ID
, C.FROM_CURRENCY_CODE) FROM_LEDGER_NAME
, LEDGER2.NAME TO_LEDGER_NAME
, LEDGER2.CHART_OF_ACCOUNTS_ID TO_CHART_OF_ACCOUNTS_ID
, HIS.ACTUAL_FLAG ACTUAL_FLAG
, NVL(HIS.STATUS
, 'NT') STATUS_CODE
, NVL(HIS.OBSOLETE_CONSOLIDATION_FLAG
, 'N') OBSOLETE_CONSOLIDATION_FLAG
, FLV.MEANING OBSOLETE_CONSOLIDATION_STATUS
, HIS.REQUEST_ID REQ_ID
, LEDGER2.CURRENCY_CODE TO_CURRENCY_CODE
, LEDGER1.CURRENCY_CODE FROM_CURRENCY_CODE
, HIS.FROM_PERIOD_NAME FROM_PERIOD_NAME
, C.METHOD METHOD_FLAG
, HIS.FROM_BUDGET_NAME SOURCE_BUDGET
, HIS.TO_BUDGET_NAME TARGET_BUDGET
, HIS.FROM_BUDGET_VERSION_ID SOURCE_BUDGET_VERSION_ID
, HIS.TO_BUDGET_VERSION_ID TARGET_BUDGET_VERSION_ID
, C.DESCRIPTION CONSOLIDATION_DESCRIPTION
, GL_CS.DESCRIPTION CONSOLIDATION_SET_DESCRIPTION
, M.DESCRIPTION MAPPING_DESCRIPTION
, HIS.TO_PERIOD_NAME TO_PERIOD_NAME
, HIS.JE_BATCH_ID JE_BATCH_ID
, JB.CONTROL_TOTAL JB_CONTROL_TOTAL
, JB.RUNNING_TOTAL_DR JB_RUNNING_TOTAL_DR
, JB.RUNNING_TOTAL_CR JB_RUNNING_TOTAL_CR
, JB.ROWID JB_ROW_ID
, JB.CHART_OF_ACCOUNTS_ID JB_CHART_OF_ACCOUNTS_ID
, JB.NAME JB_NAME
, JB.STATUS JB_STATUS
, JB.STATUS_VERIFIED JB_STATUS_VERIFIED
, JB.ACTUAL_FLAG JB_ACTUAL_FLAG
, JB.BUDGETARY_CONTROL_STATUS JB_BUDGETARY_CONTROL_STATUS
, JB.DEFAULT_PERIOD_NAME JB_DEFAULT_PERIOD_NAME
, JB.POSTING_RUN_ID JB_POSTING_RUN_ID
, JB.REQUEST_ID JB_REQUEST_ID
, JB.APPROVAL_STATUS_CODE JB_APPROVAL_STATUS_CODE
, JB.LAST_UPDATE_DATE JB_LAST_UPDATE_DATE
, JB.LAST_UPDATED_BY JB_LAST_UPDATED_BY
, JB.LAST_UPDATE_LOGIN JB_LAST_UPDATE_LOGIN
, HIS.ROWID ROW_ID
, HIS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, HIS.LAST_UPDATED_BY LAST_UPDATED_BY
, HIS.CONSOLIDATION_RUN_ID CONSOLIDATION_RUN_ID
FROM GL_JE_BATCHES JB
, (SELECT *
FROM GL_CONSOLIDATION_HISTORY
WHERE STATUS IS NOT NULL) HIS
, GL_CONSOLIDATION_SETS GL_CS
, GL_CONSOLIDATION C
, GL_COA_MAPPINGS M
, GL_LEDGERS LEDGER1
, GL_LEDGERS LEDGER2
, FND_LOOKUP_VALUES FLV
WHERE C.FROM_LEDGER_ID = LEDGER1.LEDGER_ID
AND C.TO_LEDGER_ID = LEDGER2.LEDGER_ID
AND HIS.CONSOLIDATION_ID(+) = C.CONSOLIDATION_ID+0
AND HIS.TO_PERIOD_NAME(+) = GL_CONS_WRK_PKG.GET_PERIOD
AND GL_CS.CONSOLIDATION_SET_ID(+) = HIS.CONSOLIDATION_SET_ID
AND JB.JE_BATCH_ID(+) = HIS.JE_BATCH_ID
AND EXISTS (SELECT 1
FROM GL_ACCESS_SET_LEDGERS GL_ASL
WHERE GL_ASL.ACCESS_SET_ID = GL_CONS_WRK_PKG.GET_ACCESS_SET_ID
AND GL_ASL.LEDGER_ID IN (C.FROM_LEDGER_ID
, C.TO_LEDGER_ID))
AND FLV.LOOKUP_TYPE = 'OBSOLETE_CONSOLIDATION_STATUS'
AND FLV.LANGUAGE = USERENV('LANG')
AND FLV.LOOKUP_CODE = NVL(HIS.OBSOLETE_CONSOLIDATION_FLAG
, 'N')
AND C.COA_MAPPING_ID = M.COA_MAPPING_ID