VIEW: APPS.OKE_DTS_PAYABLES_V

Source

View Text - Preformatted

SELECT PL.OKE_CONTRACT_HEADER_ID , PD.OKE_CONTRACT_LINE_ID , PD.OKE_CONTRACT_DELIVERABLE_ID , PD.PROJECT_ID , PD.TASK_ID , AI.INVOICE_ID , AI.INVOICE_NUM , AI.INVOICE_DATE , AI.INVOICE_CURRENCY_CODE , IT.INVOICE_TYPE , AI.INVOICE_AMOUNT , T.NAME , AI.PAYMENT_CURRENCY_CODE , AI.AMOUNT_PAID , ( SELECT SUM(AMOUNT_REMAINING) UNPAID_AMOUNT FROM AP_PAYMENT_SCHEDULES_ALL PS WHERE INVOICE_ID = AI.INVOICE_ID ) , AI.VENDOR_ID , V.VENDOR_NAME , V.SEGMENT1 , AI.VENDOR_SITE_ID , VS.VENDOR_SITE_CODE , AID.INVOICE_DISTRIBUTION_ID , AID.DISTRIBUTION_LINE_NUMBER , LT.LINE_TYPE , AID.AMOUNT , X.ALLOCATED_AMOUNT , PH.SEGMENT1 , PL.LINE_NUM , PLL.SHIPMENT_NUM FROM PO_LINES_ALL PL , PO_LINE_LOCATIONS_ALL PLL , PO_DISTRIBUTIONS_ALL PD , PO_HEADERS_ALL PH , AP_INVOICES_ALL AI , AP_INVOICE_DISTRIBUTIONS_ALL AID , PO_VENDORS V , PO_VENDOR_SITES_ALL VS , ( SELECT INVOICE_DISTRIBUTION_ID , AMOUNT ALLOCATED_AMOUNT , PO_DISTRIBUTION_ID FROM AP_INVOICE_DISTRIBUTIONS_ALL UNION ALL SELECT C.CHARGE_DIST_ID , C.ALLOCATED_AMOUNT , D.PO_DISTRIBUTION_ID FROM AP_CHRG_ALLOCATIONS_ALL C , AP_INVOICE_DISTRIBUTIONS_ALL D WHERE C.ITEM_DIST_ID = D.INVOICE_DISTRIBUTION_ID ) X , ( SELECT LOOKUP_CODE INVOICE_TYPE_LOOKUP_CODE , DISPLAYED_FIELD INVOICE_TYPE FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'INVOICE TYPE' ) IT , ( SELECT LOOKUP_CODE LINE_TYPE_LOOKUP_CODE , DISPLAYED_FIELD LINE_TYPE FROM AP_LOOKUP_CODES WHERE LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE' ) LT , AP_TERMS T WHERE PLL.PO_LINE_ID = PL.PO_LINE_ID AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID AND PH.PO_HEADER_ID = PL.PO_HEADER_ID AND X.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID AND AID.INVOICE_DISTRIBUTION_ID = X.INVOICE_DISTRIBUTION_ID AND AI.INVOICE_ID = AID.INVOICE_ID AND V.VENDOR_ID = AI.VENDOR_ID AND VS.VENDOR_SITE_ID = AI.VENDOR_SITE_ID AND LT.LINE_TYPE_LOOKUP_CODE (+) = AID.LINE_TYPE_LOOKUP_CODE AND IT.INVOICE_TYPE_LOOKUP_CODE = AI.INVOICE_TYPE_LOOKUP_CODE AND T.TERM_ID = AI.TERMS_ID AND PLL.shipment_type <> 'PREPAYMENT' AND PD.distribution_type <> 'PREPAYMENT'

View Text - HTML Formatted

SELECT PL.OKE_CONTRACT_HEADER_ID
, PD.OKE_CONTRACT_LINE_ID
, PD.OKE_CONTRACT_DELIVERABLE_ID
, PD.PROJECT_ID
, PD.TASK_ID
, AI.INVOICE_ID
, AI.INVOICE_NUM
, AI.INVOICE_DATE
, AI.INVOICE_CURRENCY_CODE
, IT.INVOICE_TYPE
, AI.INVOICE_AMOUNT
, T.NAME
, AI.PAYMENT_CURRENCY_CODE
, AI.AMOUNT_PAID
, ( SELECT SUM(AMOUNT_REMAINING) UNPAID_AMOUNT
FROM AP_PAYMENT_SCHEDULES_ALL PS
WHERE INVOICE_ID = AI.INVOICE_ID )
, AI.VENDOR_ID
, V.VENDOR_NAME
, V.SEGMENT1
, AI.VENDOR_SITE_ID
, VS.VENDOR_SITE_CODE
, AID.INVOICE_DISTRIBUTION_ID
, AID.DISTRIBUTION_LINE_NUMBER
, LT.LINE_TYPE
, AID.AMOUNT
, X.ALLOCATED_AMOUNT
, PH.SEGMENT1
, PL.LINE_NUM
, PLL.SHIPMENT_NUM
FROM PO_LINES_ALL PL
, PO_LINE_LOCATIONS_ALL PLL
, PO_DISTRIBUTIONS_ALL PD
, PO_HEADERS_ALL PH
, AP_INVOICES_ALL AI
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, PO_VENDORS V
, PO_VENDOR_SITES_ALL VS
, ( SELECT INVOICE_DISTRIBUTION_ID
, AMOUNT ALLOCATED_AMOUNT
, PO_DISTRIBUTION_ID
FROM AP_INVOICE_DISTRIBUTIONS_ALL UNION ALL SELECT C.CHARGE_DIST_ID
, C.ALLOCATED_AMOUNT
, D.PO_DISTRIBUTION_ID
FROM AP_CHRG_ALLOCATIONS_ALL C
, AP_INVOICE_DISTRIBUTIONS_ALL D
WHERE C.ITEM_DIST_ID = D.INVOICE_DISTRIBUTION_ID ) X
, ( SELECT LOOKUP_CODE INVOICE_TYPE_LOOKUP_CODE
, DISPLAYED_FIELD INVOICE_TYPE
FROM AP_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'INVOICE TYPE' ) IT
, ( SELECT LOOKUP_CODE LINE_TYPE_LOOKUP_CODE
, DISPLAYED_FIELD LINE_TYPE
FROM AP_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'INVOICE DISTRIBUTION TYPE' ) LT
, AP_TERMS T
WHERE PLL.PO_LINE_ID = PL.PO_LINE_ID
AND PD.LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PH.PO_HEADER_ID = PL.PO_HEADER_ID
AND X.PO_DISTRIBUTION_ID = PD.PO_DISTRIBUTION_ID
AND AID.INVOICE_DISTRIBUTION_ID = X.INVOICE_DISTRIBUTION_ID
AND AI.INVOICE_ID = AID.INVOICE_ID
AND V.VENDOR_ID = AI.VENDOR_ID
AND VS.VENDOR_SITE_ID = AI.VENDOR_SITE_ID
AND LT.LINE_TYPE_LOOKUP_CODE (+) = AID.LINE_TYPE_LOOKUP_CODE
AND IT.INVOICE_TYPE_LOOKUP_CODE = AI.INVOICE_TYPE_LOOKUP_CODE
AND T.TERM_ID = AI.TERMS_ID
AND PLL.SHIPMENT_TYPE <> 'PREPAYMENT'
AND PD.DISTRIBUTION_TYPE <> 'PREPAYMENT'