VIEW: APPS.GMS_COMMITMENT_ENCUMBERED_V

Source

View Text - Preformatted

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(poh.closed_code,'OPEN') <> 'FINALLY CLOSED' AND NVL(pll.closed_code,'OPEN') <> 'FINALLY CLOSED' 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

View Text - HTML Formatted

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(POH.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
AND NVL(PLL.CLOSED_CODE
, 'OPEN') <> 'FINALLY CLOSED'
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