VIEW: APPS.AP_APPLY_PREPAYS_FR_PREPAY_V

Source

View Text - Preformatted

SELECT AI.ROWID ROW_ID, AI.INVOICE_ID INVOICE_ID, AI.INVOICE_NUM INVOICE_NUM, AI.VENDOR_ID VENDOR_ID, AI.VENDOR_SITE_ID VENDOR_SITE_ID, PV.VENDOR_NAME VENDOR_NAME, PV.SEGMENT1 VENDOR_NUMBER, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE, AI.INVOICE_DATE INVOICE_DATE, AI.PAYMENT_CROSS_RATE_DATE PAYMENT_CROSS_RATE_DATE, AI.PAYMENT_CROSS_RATE_TYPE PAYMENT_CROSS_RATE_TYPE, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID, AI.DESCRIPTION DESCRIPTION, AI.ORG_ID ORG_ID, AP_PREPAY_UTILS_PKG.GET_INVOICE_UNPAID_AMOUNT( AI.INVOICE_ID) AMOUNT_UNPAID, NULLIF(AP_PREPAY_UTILS_PKG.GET_INCLUSIVE_TAX_UNPAID_AMT( AI.INVOICE_ID), 0) TAX_AMOUNT_UNPAID FROM AP_INVOICES AI, PO_VENDORS PV, PO_VENDOR_SITES_ALL PVS WHERE AI.VENDOR_ID = PV.VENDOR_ID AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT', 'CREDIT','DEBIT', 'PO PRICE ADJUST', 'ADJUSTMENT' ) AND AI.PAYMENT_STATUS_FLAG IN ('N','P') AND 0 < (SELECT SUM(APS.AMOUNT_REMAINING) FROM AP_PAYMENT_SCHEDULES_ALL APS WHERE APS.INVOICE_ID = AI.INVOICE_ID)/*bug 8847908 begin*/ AND EXISTS ( SELECT 1 FROM ap_invoice_lines ail WHERE ail.invoice_id = ai.invoice_id AND ail.line_type_lookup_code in ('ITEM', 'MISCELLANEOUS', 'FREIGHT') AND (ail.distribution_set_id IS NOT NULL OR ail.default_dist_ccid IS NOT NULL) AND rownum = 1 UNION ALL SELECT 1 FROM AP_INVOICE_DISTRIBUTIONS aid WHERE aid.invoice_id = ai.invoice_id AND aid.line_type_lookup_code IN ('ITEM', 'ACCRUAL', 'MISCELLANEOUS', 'FREIGHT') AND aid.dist_code_combination_id IS NOT NULL AND rownum = 1) /*bug 8847908 end*/ AND NOT EXISTS (SELECT 'INVOICE SELECTED FOR PAYMENT' FROM AP_SELECTED_INVOICES_ALL ASI WHERE ASI.INVOICE_ID = AI.INVOICE_ID)

View Text - HTML Formatted

SELECT AI.ROWID ROW_ID
, AI.INVOICE_ID INVOICE_ID
, AI.INVOICE_NUM INVOICE_NUM
, AI.VENDOR_ID VENDOR_ID
, AI.VENDOR_SITE_ID VENDOR_SITE_ID
, PV.VENDOR_NAME VENDOR_NAME
, PV.SEGMENT1 VENDOR_NUMBER
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, AI.PAYMENT_CURRENCY_CODE PAYMENT_CURRENCY_CODE
, AI.INVOICE_DATE INVOICE_DATE
, AI.PAYMENT_CROSS_RATE_DATE PAYMENT_CROSS_RATE_DATE
, AI.PAYMENT_CROSS_RATE_TYPE PAYMENT_CROSS_RATE_TYPE
, AI.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
, AI.DESCRIPTION DESCRIPTION
, AI.ORG_ID ORG_ID
, AP_PREPAY_UTILS_PKG.GET_INVOICE_UNPAID_AMOUNT( AI.INVOICE_ID) AMOUNT_UNPAID
, NULLIF(AP_PREPAY_UTILS_PKG.GET_INCLUSIVE_TAX_UNPAID_AMT( AI.INVOICE_ID)
, 0) TAX_AMOUNT_UNPAID
FROM AP_INVOICES AI
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
WHERE AI.VENDOR_ID = PV.VENDOR_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AI.INVOICE_TYPE_LOOKUP_CODE NOT IN ('PREPAYMENT'
, 'CREDIT'
, 'DEBIT'
, 'PO PRICE ADJUST'
, 'ADJUSTMENT' )
AND AI.PAYMENT_STATUS_FLAG IN ('N'
, 'P')
AND 0 < (SELECT SUM(APS.AMOUNT_REMAINING)
FROM AP_PAYMENT_SCHEDULES_ALL APS
WHERE APS.INVOICE_ID = AI.INVOICE_ID)/*BUG 8847908 BEGIN*/
AND EXISTS ( SELECT 1
FROM AP_INVOICE_LINES AIL
WHERE AIL.INVOICE_ID = AI.INVOICE_ID
AND AIL.LINE_TYPE_LOOKUP_CODE IN ('ITEM'
, 'MISCELLANEOUS'
, 'FREIGHT')
AND (AIL.DISTRIBUTION_SET_ID IS NOT NULL OR AIL.DEFAULT_DIST_CCID IS NOT NULL)
AND ROWNUM = 1 UNION ALL SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS AID
WHERE AID.INVOICE_ID = AI.INVOICE_ID
AND AID.LINE_TYPE_LOOKUP_CODE IN ('ITEM'
, 'ACCRUAL'
, 'MISCELLANEOUS'
, 'FREIGHT')
AND AID.DIST_CODE_COMBINATION_ID IS NOT NULL
AND ROWNUM = 1) /*BUG 8847908 END*/
AND NOT EXISTS (SELECT 'INVOICE SELECTED FOR PAYMENT'
FROM AP_SELECTED_INVOICES_ALL ASI
WHERE ASI.INVOICE_ID = AI.INVOICE_ID)