View: GMS_COMMITMENT_ENCUMBERED_V

Product:	GMS - Grants Accounting
Description:
Implementation/DBA Data:	APPS.GMS_COMMITMENT_ENCUMBERED_V

View Text

SELECT ADL.DOCUMENT_TYPE DOCUMENT_TYPE
, NULL TRANSACTION_SOURCE
, RH.SEGMENT1 CMT_NUMBER
, RL.ITEM_DESCRIPTION DESCRIPTION
, RD.PROJECT_ID PROJECT_ID
, RD.TASK_ID TASK_ID
, ADL.AWARD_ID AWARD_ID
, RD.EXPENDITURE_TYPE EXPENDITURE_TYPE
, RD.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, RD.EXPENDITURE_ORGANIZATION_ID ORGANIZATION_ID
, RL.SUGGESTED_VENDOR_NAME VENDOR_NAME
, DECODE (LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RL.UNIT_MEAS_LOOKUP_CODE ) UNIT_OF_MEASURE
, RL.UNIT_PRICE UNIT_PRICE
, RD.REQ_LINE_QUANTITY TOT_CMT_QUANTITY
, PO_INTG_DOCUMENT_FUNDS_GRP.GET_ACTIVE_ENCUMBRANCE_FUNC ('REQUISITION'
, RD.DISTRIBUTION_ID ) ACCT_RAW_COST
, ADL.BURDENABLE_RAW_COST ACCT_BURDENED_COST
, ADL.IND_COMPILED_SET_ID IND_COMPILED_SET_ID
, ADL.BURDENABLE_RAW_COST BURDENABLE_RAW_COST
, PP.PERIOD_NAME PA_PERIOD
, RD.GL_ENCUMBERED_DATE GL_DATE
FROM PO_REQUISITION_HEADERS_ALL RH
, PO_REQUISITION_LINES_ALL RL
, PO_REQ_DISTRIBUTIONS_ALL RD
, PO_LINE_TYPES LT
, GMS_AWARD_DISTRIBUTIONS ADL
, PA_PERIODS PP
WHERE RH.TYPE_LOOKUP_CODE = 'PURCHASE'
AND RH.REQUISITION_HEADER_ID = RL.REQUISITION_HEADER_ID
AND NVL (RL.MODIFIED_BY_AGENT_FLAG
, 'N') = 'N'
AND RL.SOURCE_TYPE_CODE = 'VENDOR'
AND RL.LINE_TYPE_ID = LT.LINE_TYPE_ID
AND RD.REQUISITION_LINE_ID = RL.REQUISITION_LINE_ID
AND NVL (RD.ENCUMBERED_FLAG
, 'N') = 'Y'
AND ADL.AWARD_SET_ID = RD.AWARD_ID
AND ADL.DISTRIBUTION_ID = RD.DISTRIBUTION_ID
AND ADL.ADL_STATUS = 'A'
AND ADL.FC_STATUS = 'A'
AND ADL.DOCUMENT_TYPE = 'REQ'
AND RD.EXPENDITURE_ITEM_DATE BETWEEN PP.START_DATE
AND PP.END_DATE UNION ALL SELECT ADL.DOCUMENT_TYPE DOCUMENT_TYPE
, NULL TRANSACTION_SOURCE
, POH.SEGMENT1 CMT_NUMBER
, POL.ITEM_DESCRIPTION DESCRIPTION
, POD.PROJECT_ID PROJECT_ID
, POD.TASK_ID TASK_ID
, ADL.AWARD_ID AWARD_ID
, POD.EXPENDITURE_TYPE EXPENDITURE_TYPE
, POD.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, POD.EXPENDITURE_ORGANIZATION_ID ORGANIZATION_ID
, V.VENDOR_NAME VENDOR_NAME
, DECODE (LT.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POL.UNIT_MEAS_LOOKUP_CODE ) UNIT_OF_MEASURE
, POL.UNIT_PRICE UNIT_PRICE
, GREATEST (0
, ( POD.QUANTITY_ORDERED - NVL (POD.QUANTITY_CANCELLED
, 0) - NVL (POD.UNENCUMBERED_QUANTITY
, 0) - GMS_BUDGET_BALANCE.GET_PO_QTY_INVOICED (POD.PO_DISTRIBUTION_ID
, NVL (POD.QUANTITY_BILLED
, 0 )
, 'Y' ) ) ) TOT_CMT_QUANTITY
, PO_INTG_DOCUMENT_FUNDS_GRP.GET_ACTIVE_ENCUMBRANCE_FUNC ('PO'
, POD.PO_DISTRIBUTION_ID ) ACCT_RAW_COST
, ADL.BURDENABLE_RAW_COST ACCT_BURDENED_COST
, ADL.IND_COMPILED_SET_ID IND_COMPILED_SET_ID
, ADL.BURDENABLE_RAW_COST BURDENABLE_RAW_COST
, PP.PERIOD_NAME PA_PERIOD
, POD.GL_ENCUMBERED_DATE GL_DATE
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_DISTRIBUTIONS_ALL POD
, PO_VENDORS V
, PO_LINE_TYPES LT
, PO_LINE_LOCATIONS_ALL PLL
, GMS_AWARD_DISTRIBUTIONS ADL
, GL_LEDGERS G
, PA_PERIODS PP
WHERE POH.VENDOR_ID = V.VENDOR_ID
AND POH.TYPE_LOOKUP_CODE IN ('STANDARD'
, 'BLANKET'
, 'PLANNED')
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'BLANKET'
, 'SCHEDULED'
, 'PLANNED')
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND POL.LINE_TYPE_ID = LT.LINE_TYPE_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POL.PO_LINE_ID = POD.PO_LINE_ID
AND NVL (POD.ENCUMBERED_FLAG
, 'N') = 'Y'
AND POD.AWARD_ID = ADL.AWARD_SET_ID
AND ADL.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
AND ADL.ADL_STATUS = 'A'
AND ADL.FC_STATUS = 'A'
AND (PO_INTG_DOCUMENT_FUNDS_GRP.GET_ACTIVE_ENCUMBRANCE_FUNC ('PO'
, POD.PO_DISTRIBUTION_ID ) <> 0 )
AND ADL.DOCUMENT_TYPE = 'PO'
AND G.LEDGER_ID = POD.SET_OF_BOOKS_ID
AND POD.EXPENDITURE_ITEM_DATE BETWEEN PP.START_DATE
AND PP.END_DATE UNION ALL SELECT ADL.DOCUMENT_TYPE DOCUMENT_TYPE
, NULL TRANSACTION_SOURCE
, I.INVOICE_NUM CMT_NUMBER
, D.DESCRIPTION DESCRIPTION
, D.PROJECT_ID PROJECT_ID
, D.TASK_ID TASK_ID
, ADL.AWARD_ID AWARD_ID
, D.EXPENDITURE_TYPE EXPENDITURE_TYPE
, D.EXPENDITURE_ITEM_DATE EXPENDITURE_ITEM_DATE
, D.EXPENDITURE_ORGANIZATION_ID ORGANIZATION_ID
, V.VENDOR_NAME VENDOR_NAME
, TO_CHAR (NULL) UNIT_OF_MEASURE
, TO_NUMBER (NULL) UNIT_PRICE
, PA_CMT_UTILS.GET_APDIST_QTY( D.INVOICE_DISTRIBUTION_ID
, D.INVOICE_ID
, NVL (D.BASE_AMOUNT
, D.AMOUNT)
, D.PA_QUANTITY
, 'GMS'
, 'N'
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')) TOT_CMT_QUANTITY
, PA_CMT_UTILS.GET_APDIST_AMT(D.INVOICE_DISTRIBUTION_ID
, D.INVOICE_ID
, NVL (D.BASE_AMOUNT
, D.AMOUNT)
, 'N'
, 'GMS'
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N')) ACCT_RAW_COST
, ADL.BURDENABLE_RAW_COST ACCT_BURDENED_COST
, ADL.IND_COMPILED_SET_ID IND_COMPILED_SET_ID
, ADL.BURDENABLE_RAW_COST BURDENABLE_RAW_COST
, PP.PERIOD_NAME PA_PERIOD
, D.ACCOUNTING_DATE GL_DATE
FROM AP_INVOICES_ALL I
, AP_INVOICE_DISTRIBUTIONS_ALL D
, PO_VENDORS V
, GMS_AWARD_DISTRIBUTIONS ADL
, GL_LEDGERS G
, PA_PERIODS PP
WHERE I.VENDOR_ID = V.VENDOR_ID
AND I.INVOICE_ID = D.INVOICE_ID
AND DECODE (D.PA_ADDITION_FLAG
, 'G'
, 'Y'
, 'Z'
, 'Y'
, 'T'
, 'Y'
, 'E'
, 'Y'
, NULL
, 'N'
, D.PA_ADDITION_FLAG ) <> 'Y'
AND NVL (D.MATCH_STATUS_FLAG
, 'N') = 'A'
AND D.AWARD_ID = ADL.AWARD_SET_ID
AND ADL.INVOICE_ID = I.INVOICE_ID
AND ADL.INVOICE_DISTRIBUTION_ID = D.INVOICE_DISTRIBUTION_ID
AND ADL.ADL_STATUS = 'A'
AND ADL.DOCUMENT_TYPE = 'AP'
AND ADL.FC_STATUS = 'A'
AND G.LEDGER_ID = D.SET_OF_BOOKS_ID
AND ( ( I.INVOICE_TYPE_LOOKUP_CODE <> 'PREPAYMENT' ) OR ( I.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND D.PO_DISTRIBUTION_ID IS NULL ) )
AND PA_CMT_UTILS.GET_APDIST_AMT(D.INVOICE_DISTRIBUTION_ID
, D.INVOICE_ID
, NVL (D.BASE_AMOUNT
, D.AMOUNT)
, 'N'
, 'GMS'
, NVL(G.SLA_LEDGER_CASH_BASIS_FLAG
, 'N') ) <> 0
AND NVL(ADL.PAYMENT_STATUS_FLAG
, 'N') <> 'Y'
AND D.EXPENDITURE_ITEM_DATE BETWEEN PP.START_DATE
AND PP.END_DATE UNION ALL SELECT ADL.DOCUMENT_TYPE DOCUMENT_TYPE
, PTS.USER_TRANSACTION_SOURCE TRANSACTION_SOURCE
, TO_CHAR (ENC.ENCUMBRANCE_ITEM_ID) CMT_NUMBER
, NULL DESCRIPTION
, ADL.PROJECT_ID PROJECT_ID
, ENC.TASK_ID TASK_ID
, ADL.AWARD_ID AWARD_ID
, ENC.ENCUMBRANCE_TYPE EXPENDITURE_TYPE
, ENC.ENCUMBRANCE_ITEM_DATE EXPENDITURE_ITEM_DATE
, NVL (ENC.OVERRIDE_TO_ORGANIZATION_ID
, GEA.INCURRED_BY_ORGANIZATION_ID ) ORGANIZATION_ID
, NULL VENDOR_NAME
, TO_CHAR (NULL) UNIT_OF_MEASURE
, TO_NUMBER (NULL) UNIT_PRICE
, TO_NUMBER (NULL) TOT_CMT_QUANTITY
, ENC.AMOUNT ACCT_RAW_COST
, ADL.BURDENABLE_RAW_COST ACCT_BURDENED_COST
, ADL.IND_COMPILED_SET_ID IND_COMPILED_SET_ID
, ADL.BURDENABLE_RAW_COST BURDENABLE_RAW_COST
, PP.PERIOD_NAME PA_PERIOD
, NULL GL_DATE
FROM GMS_ENCUMBRANCE_ITEMS_ALL ENC
, GMS_ENCUMBRANCES_ALL GEA
, GMS_AWARD_DISTRIBUTIONS ADL
, PA_TRANSACTION_SOURCES PTS
, PA_PERIODS PP
WHERE ENC.ENCUMBRANCE_ITEM_ID = ADL.EXPENDITURE_ITEM_ID
AND GEA.ENCUMBRANCE_ID = ENC.ENCUMBRANCE_ID
AND ADL.ADL_STATUS = 'A'
AND ADL.FC_STATUS = 'A'
AND ADL.DOCUMENT_TYPE = 'ENC'
AND NVL(ADL.REVERSED_FLAG
, 'N') = 'N'
AND ADL.LINE_NUM_REVERSED IS NULL
AND PTS.TRANSACTION_SOURCE(+) = ENC.TRANSACTION_SOURCE
AND ENC.ENCUMBRANCE_ITEM_DATE BETWEEN PP.START_DATE
AND PP.END_DATE

Columns