VIEW: APPS.GMS_COMMITMENT_TXNS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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