View: GMS_COMMITMENT_TXNS_V

Product:	GMS - Grants Accounting
Description:	A view of all oustanding project-related commitment costs.
Implementation/DBA Data:	APPS.GMS_COMMITMENT_TXNS_V

View Text

SELECT PROJECT_ID
, TASK_ID
, TRANSACTION_SOURCE
, LINE_TYPE
, CMT_NUMBER
, CMT_DISTRIBUTION_ID
, CMT_HEADER_ID
, DESCRIPTION
, EXPENDITURE_ITEM_DATE
, PA_PERIOD
, GL_PERIOD
, CMT_LINE_NUMBER
, CMT_CREATION_DATE
, CMT_APPROVED_DATE
, CMT_REQUESTOR_NAME
, CMT_BUYER_NAME
, CMT_APPROVED_FLAG
, CMT_PROMISED_DATE
, CMT_NEED_BY_DATE
, ORGANIZATION_ID
, VENDOR_ID
, VENDOR_NAME
, EXPENDITURE_TYPE
, EXPENDITURE_CATEGORY
, REVENUE_CATEGORY
, SYSTEM_LINKAGE_FUNCTION
, UNIT_OF_MEASURE
, UNIT_PRICE
, TO_NUMBER (NULL) CMT_IND_COMPILED_SET_ID
, PA_CURRENCY.ROUND_CURRENCY_AMT(ACCT_RAW_COST)
, PA_CURRENCY.ROUND_CURRENCY_AMT(ACCT_BURDENED_COST)
, TOT_CMT_QUANTITY
, QUANTITY_ORDERED
, PA_CURRENCY.ROUND_CURRENCY_AMT(AMOUNT_ORDERED)
, ORIGINAL_QUANTITY_ORDERED
, PA_CURRENCY.ROUND_CURRENCY_AMT(ORIGINAL_AMOUNT_ORDERED)
, QUANTITY_CANCELLED
, PA_CURRENCY.ROUND_CURRENCY_AMT(AMOUNT_CANCELLED)
, QUANTITY_DELIVERED
, QUANTITY_INVOICED
, PA_CURRENCY.ROUND_CURRENCY_AMT(AMOUNT_INVOICED)
, QUANTITY_OUTSTANDING_DELIVERY
, PA_CURRENCY.ROUND_CURRENCY_AMT(AMOUNT_OUTSTANDING_DELIVERY)
, QUANTITY_OUTSTANDING_INVOICE
, PA_CURRENCY.ROUND_CURRENCY_AMT(AMOUNT_OUTSTANDING_INVOICE)
, QUANTITY_OVERBILLED
, PA_CURRENCY.ROUND_CURRENCY_AMT(AMOUNT_OVERBILLED)
, ORIGINAL_TXN_REFERENCE1
, ORIGINAL_TXN_REFERENCE2
, ORIGINAL_TXN_REFERENCE3
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, DENOM_CURRENCY_CODE
, PA_CURRENCY.ROUND_CURRENCY_AMT(DENOM_RAW_COST)
, PA_CURRENCY.ROUND_CURRENCY_AMT(DENOM_BURDENED_COST)
, ACCT_CURRENCY_CODE
, ACCT_RATE_DATE
, ACCT_RATE_TYPE
, ACCT_EXCHANGE_RATE
, RECEIPT_CURRENCY_CODE
, PA_CURRENCY.ROUND_CURRENCY_AMT(RECEIPT_CURRENCY_AMOUNT)
, RECEIPT_EXCHANGE_RATE
FROM GMS_COMMITMENTS_OVERRIDE_V UNION ALL SELECT GCOV.PROJECT_ID
, GCOV.TASK_ID
, TRANSACTION_SOURCE
, LINE_TYPE
, CMT_NUMBER
, CMT_DISTRIBUTION_ID
, CMT_HEADER_ID
, GCOV.DESCRIPTION
, EXPENDITURE_ITEM_DATE
, PA_PERIOD
, GL_PERIOD
, CMT_LINE_NUMBER
, CMT_CREATION_DATE
, CMT_APPROVED_DATE
, CMT_REQUESTOR_NAME
, CMT_BUYER_NAME
, CMT_APPROVED_FLAG
, CMT_PROMISED_DATE
, CMT_NEED_BY_DATE
, GCOV.ORGANIZATION_ID
, GCOV.VENDOR_ID
, GCOV.VENDOR_NAME
, ICC.EXPENDITURE_TYPE
, PET.EXPENDITURE_CATEGORY
, PET.REVENUE_CATEGORY_CODE
, 'BTC'
, GCOV.UNIT_OF_MEASURE
, UNIT_PRICE
, CMT_IND_COMPILED_SET_ID
, 0
, PA_CURRENCY.ROUND_CURRENCY_AMT(NVL(GCOV.BURDENABLE_RAW_COST
, 0) * CM.COMPILED_MULTIPLIER) BURDEN_COST
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, 0
, ORIGINAL_TXN_REFERENCE1
, ORIGINAL_TXN_REFERENCE2
, ORIGINAL_TXN_REFERENCE3
, GCOV.LAST_UPDATE_DATE
, GCOV.LAST_UPDATED_BY
, GCOV.CREATION_DATE
, GCOV.CREATED_BY
, GCOV.LAST_UPDATE_LOGIN
, GCOV.REQUEST_ID
, GCOV.PROGRAM_APPLICATION_ID
, GCOV.PROGRAM_ID
, GCOV.PROGRAM_UPDATE_DATE
, GCOV.DENOM_CURRENCY_CODE
, 0 DENOM_RAW_COST
, PA_CURRENCY.ROUND_CURRENCY_AMT(NVL(GCOV.BURDENABLE_RAW_COST
, 0) * CM.COMPILED_MULTIPLIER / NVL(GCOV.ACCT_EXCHANGE_RATE
, 1)) DENOM_BURDENED_COST
, ACCT_CURRENCY_CODE
, ACCT_RATE_DATE
, ACCT_RATE_TYPE
, ACCT_EXCHANGE_RATE
, RECEIPT_CURRENCY_CODE
, PA_CURRENCY.ROUND_CURRENCY_AMT(RECEIPT_CURRENCY_AMOUNT)
, RECEIPT_EXCHANGE_RATE
FROM PA_IND_RATE_SCH_REVISIONS IRSR
, GMS_COMMITMENTS_OVERRIDE_V GCOV
, PA_COST_BASE_EXP_TYPES CBET
, PA_COMPILED_MULTIPLIERS CM
, PA_IND_COST_CODES ICC
, PA_IND_RATE_SCHEDULES_ALL_BG IRS
, PA_IND_COMPILED_SETS ICS
, PA_EXPENDITURE_TYPES PET /*ADDED FOR BUG 2904358*/
WHERE CBET.COST_BASE_TYPE = 'INDIRECT COST'
AND GCOV.ACCT_RAW_COST <> 0
AND ICS.IND_COMPILED_SET_ID = GCOV.CMT_IND_COMPILED_SET_ID /*AND CM.IND_COMPILED_SET_ID = GCOV.CMT_IND_COMPILED_SET_ID *** BURDENING ENHANCEMENT */
AND CM.IND_COMPILED_SET_ID = DECODE( CBET.COST_BASE
, NULL
, NULL
, ICS.IND_COMPILED_SET_ID )
AND ICC.IND_COST_CODE = CM.IND_COST_CODE
AND CBET.COST_BASE = CM.COST_BASE
AND CBET.EXPENDITURE_TYPE = GCOV.EXPENDITURE_TYPE
AND IRSR.COST_PLUS_STRUCTURE = CBET.COST_PLUS_STRUCTURE
AND IRS.IND_RATE_SCH_ID = IRSR.IND_RATE_SCH_ID
AND ICS.IND_RATE_SCH_REVISION_ID = IRSR.IND_RATE_SCH_REVISION_ID
AND ICS.ORGANIZATION_ID = GCOV.ORGANIZATION_ID
AND ICS.COST_BASE = CBET.COST_BASE /* BURDENING ENHANCEMENTS */
AND ICC.EXPENDITURE_TYPE = PET.EXPENDITURE_TYPE /*ADDED FOR BUG 2904358*/ UNION ALL SELECT PROJECT_ID
, TASK_ID
, TRANSACTION_SOURCE
, LINE_TYPE
, CMT_NUMBER
, CMT_DISTRIBUTION_ID
, CMT_HEADER_ID
, DESCRIPTION
, EXPENDITURE_ITEM_DATE
, PA_PERIOD
, GL_PERIOD
, CMT_LINE_NUMBER
, CMT_CREATION_DATE
, CMT_APPROVED_DATE
, CMT_REQUESTOR_NAME
, CMT_BUYER_NAME
, CMT_APPROVED_FLAG
, CMT_PROMISED_DATE
, CMT_NEED_BY_DATE
, ORGANIZATION_ID
, VENDOR_ID
, VENDOR_NAME
, EXPENDITURE_TYPE
, EXPENDITURE_CATEGORY
, REVENUE_CATEGORY
, SYSTEM_LINKAGE_FUNCTION
, UNIT_OF_MEASURE
, UNIT_PRICE
, CMT_IND_COMPILED_SET_ID
, ACCT_RAW_COST
, ACCT_BURDENED_COST
, TOT_CMT_QUANTITY
, QUANTITY_ORDERED
, AMOUNT_ORDERED
, ORIGINAL_QUANTITY_ORDERED
, ORIGINAL_AMOUNT_ORDERED
, QUANTITY_CANCELLED
, AMOUNT_CANCELLED
, QUANTITY_DELIVERED
, QUANTITY_INVOICED
, AMOUNT_INVOICED
, QUANTITY_OUTSTANDING_DELIVERY
, AMOUNT_OUTSTANDING_DELIVERY
, QUANTITY_OUTSTANDING_INVOICE
, AMOUNT_OUTSTANDING_INVOICE
, QUANTITY_OVERBILLED
, AMOUNT_OVERBILLED
, ORIGINAL_TXN_REFERENCE1
, ORIGINAL_TXN_REFERENCE2
, ORIGINAL_TXN_REFERENCE3
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, DENOM_CURRENCY_CODE
, DENOM_RAW_COST
, DENOM_BURDENED_COST
, ACCT_CURRENCY_CODE
, ACCT_RATE_DATE
, ACCT_RATE_TYPE
, ACCT_EXCHANGE_RATE
, RECEIPT_CURRENCY_CODE
, RECEIPT_CURRENCY_AMOUNT
, RECEIPT_EXCHANGE_RATE
FROM GMS_ENC_PSI_V

Columns