VIEW: APPS.AP_APPLY_PREPAYS_V

Source

View Text - Preformatted

SELECT AIL.ROWID ROW_ID, AIL.INVOICE_ID INVOICE_ID, AIL.LINE_NUMBER PREPAY_LINE_NUMBER, AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING (AIL.INVOICE_ID, AIL.LINE_NUMBER) PREPAY_AMOUNT_REMAINING, NULLIF(AP_PREPAY_UTILS_PKG.GET_INC_TAX_PP_AMT_REMAINING (AIL.INVOICE_ID, AIL.LINE_NUMBER), 0) TAX_AMOUNT_REMAINING, AIL.AMOUNT AMOUNT, AIL.ACCOUNTING_DATE ACCOUNTING_DATE, AIL.PERIOD_NAME PERIOD_NAME, AIL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, AIL.DESCRIPTION DESCRIPTION, AIL.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID, AIL.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID, AIL.RCV_TRANSACTION_ID RCV_TRANSACTION_ID, AIL.ORG_ID ORG_ID, AI.INVOICE_NUM PREPAY_NUMBER, AI.INVOICE_DATE INVOICE_DATE, AI.INVOICE_AMOUNT INVOICE_AMOUNT, AI.VENDOR_ID VENDOR_ID, AI.VENDOR_SITE_ID VENDOR_SITE_ID, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE, AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE, AI.PO_HEADER_ID PO_HEADER_ID, PH.SEGMENT1 PO_NUMBER, PV.VENDOR_NAME VENDOR_NAME, PV.SEGMENT1 VENDOR_NUMBER, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, RSH.RECEIPT_NUM RECEIPT_NUMBER, AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE FROM AP_INVOICES AI, AP_INVOICE_LINES_ALL AIL, PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS, PO_HEADERS_ALL PH, PO_LINE_LOCATIONS_ALL PLL, /*Contract Payments*/ RCV_TRANSACTIONS RTXNS, RCV_SHIPMENT_HEADERS RSH, RCV_SHIPMENT_LINES RSL WHERE AI.INVOICE_ID = AIL.INVOICE_ID AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM' AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND AI.PAYMENT_STATUS_FLAG = 'Y' AND AI.VENDOR_ID = PV.VENDOR_ID AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND AI.EARLIEST_SETTLEMENT_DATE <= SYSDATE AND AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING (AIL.INVOICE_ID, AIL.LINE_NUMBER) > 0 AND NVL(AIL.DISCARDED_FLAG,'N') <> 'Y' AND NVL(AIL.LINE_SELECTED_FOR_APPL_FLAG,'N') <> 'Y' AND AIL.PREPAY_APPL_REQUEST_ID IS NULL AND AIL.PO_HEADER_ID = PH.PO_HEADER_ID (+) AND AIL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+) AND NVL(PLL.SHIPMENT_TYPE,'DUMMY') <> 'PREPAYMENT' /*Contract Payments*/ AND AIL.RCV_TRANSACTION_ID = RTXNS.TRANSACTION_ID (+) AND RTXNS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID (+) AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+) AND AIL.INVOICE_ID IN (SELECT AIL.INVOICE_ID FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP WHERE FSP.ORG_ID = AIL.ORG_ID AND ((NVL(FSP.PURCH_ENCUMBRANCE_FLAG, 'N') = 'N' AND NOT EXISTS (SELECT MATCH_STATUS_FLAG FROM AP_INVOICE_DISTRIBUTIONS_ALL AID WHERE AID.INVOICE_ID = AIL.INVOICE_ID AND NVL(AID.MATCH_STATUS_FLAG, 'N') NOT IN ('T', 'A'))) OR (NVL(FSP.PURCH_ENCUMBRANCE_FLAG, 'N') = 'Y' AND NOT EXISTS (SELECT MATCH_STATUS_FLAG FROM AP_INVOICE_DISTRIBUTIONS_ALL AID1 WHERE AID1.INVOICE_ID = AIL.INVOICE_ID AND NVL(AID1.MATCH_STATUS_FLAG, 'N') <> 'A' )) ) ) AND NOT EXISTS (SELECT H.INVOICE_ID FROM AP_HOLDS H, AP_HOLD_CODES C WHERE H.INVOICE_ID = AIL.INVOICE_ID AND H.HOLD_LOOKUP_CODE = C.HOLD_LOOKUP_CODE AND ((H.RELEASE_LOOKUP_CODE IS NULL) AND (C.POSTABLE_FLAG = 'N' OR C.POSTABLE_FLAG = 'X')))

View Text - HTML Formatted

SELECT AIL.ROWID ROW_ID
, AIL.INVOICE_ID INVOICE_ID
, AIL.LINE_NUMBER PREPAY_LINE_NUMBER
, AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING (AIL.INVOICE_ID
, AIL.LINE_NUMBER) PREPAY_AMOUNT_REMAINING
, NULLIF(AP_PREPAY_UTILS_PKG.GET_INC_TAX_PP_AMT_REMAINING (AIL.INVOICE_ID
, AIL.LINE_NUMBER)
, 0) TAX_AMOUNT_REMAINING
, AIL.AMOUNT AMOUNT
, AIL.ACCOUNTING_DATE ACCOUNTING_DATE
, AIL.PERIOD_NAME PERIOD_NAME
, AIL.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AIL.DESCRIPTION DESCRIPTION
, AIL.PO_LINE_LOCATION_ID PO_LINE_LOCATION_ID
, AIL.PO_DISTRIBUTION_ID PO_DISTRIBUTION_ID
, AIL.RCV_TRANSACTION_ID RCV_TRANSACTION_ID
, AIL.ORG_ID ORG_ID
, AI.INVOICE_NUM PREPAY_NUMBER
, AI.INVOICE_DATE INVOICE_DATE
, AI.INVOICE_AMOUNT INVOICE_AMOUNT
, AI.VENDOR_ID VENDOR_ID
, AI.VENDOR_SITE_ID VENDOR_SITE_ID
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, AI.PAYMENT_CROSS_RATE PAYMENT_CROSS_RATE
, AI.PO_HEADER_ID PO_HEADER_ID
, PH.SEGMENT1 PO_NUMBER
, PV.VENDOR_NAME VENDOR_NAME
, PV.SEGMENT1 VENDOR_NUMBER
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, RSH.RECEIPT_NUM RECEIPT_NUMBER
, AI.EARLIEST_SETTLEMENT_DATE EARLIEST_SETTLEMENT_DATE
FROM AP_INVOICES AI
, AP_INVOICE_LINES_ALL AIL
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PO_HEADERS_ALL PH
, PO_LINE_LOCATIONS_ALL PLL
, /*CONTRACT PAYMENTS*/ RCV_TRANSACTIONS RTXNS
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
WHERE AI.INVOICE_ID = AIL.INVOICE_ID
AND AIL.LINE_TYPE_LOOKUP_CODE = 'ITEM'
AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND AI.PAYMENT_STATUS_FLAG = 'Y'
AND AI.VENDOR_ID = PV.VENDOR_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AI.EARLIEST_SETTLEMENT_DATE <= SYSDATE
AND AP_PREPAY_UTILS_PKG.GET_LINE_PREPAY_AMT_REMAINING (AIL.INVOICE_ID
, AIL.LINE_NUMBER) > 0
AND NVL(AIL.DISCARDED_FLAG
, 'N') <> 'Y'
AND NVL(AIL.LINE_SELECTED_FOR_APPL_FLAG
, 'N') <> 'Y'
AND AIL.PREPAY_APPL_REQUEST_ID IS NULL
AND AIL.PO_HEADER_ID = PH.PO_HEADER_ID (+)
AND AIL.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID(+)
AND NVL(PLL.SHIPMENT_TYPE
, 'DUMMY') <> 'PREPAYMENT' /*CONTRACT PAYMENTS*/
AND AIL.RCV_TRANSACTION_ID = RTXNS.TRANSACTION_ID (+)
AND RTXNS.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID (+)
AND RSL.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID (+)
AND AIL.INVOICE_ID IN (SELECT AIL.INVOICE_ID
FROM FINANCIALS_SYSTEM_PARAMS_ALL FSP
WHERE FSP.ORG_ID = AIL.ORG_ID
AND ((NVL(FSP.PURCH_ENCUMBRANCE_FLAG
, 'N') = 'N'
AND NOT EXISTS (SELECT MATCH_STATUS_FLAG
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID = AIL.INVOICE_ID
AND NVL(AID.MATCH_STATUS_FLAG
, 'N') NOT IN ('T'
, 'A'))) OR (NVL(FSP.PURCH_ENCUMBRANCE_FLAG
, 'N') = 'Y'
AND NOT EXISTS (SELECT MATCH_STATUS_FLAG
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID1
WHERE AID1.INVOICE_ID = AIL.INVOICE_ID
AND NVL(AID1.MATCH_STATUS_FLAG
, 'N') <> 'A' )) ) )
AND NOT EXISTS (SELECT H.INVOICE_ID
FROM AP_HOLDS H
, AP_HOLD_CODES C
WHERE H.INVOICE_ID = AIL.INVOICE_ID
AND H.HOLD_LOOKUP_CODE = C.HOLD_LOOKUP_CODE
AND ((H.RELEASE_LOOKUP_CODE IS NULL)
AND (C.POSTABLE_FLAG = 'N' OR C.POSTABLE_FLAG = 'X')))