VIEW: APPS.CE_AP_FC_DUE_INVOICES_V

Source

View Text - Preformatted

SELECT APS.INVOICE_ID, /* 1 */ APS.PAYMENT_NUM, /* 2 */ (DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 3 */ DECODE(AI.AMOUNT_PAID, 0,0, -AI.AMOUNT_PAID), NVL(APS.AMOUNT_REMAINING,0))), DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 4 */ DECODE(AI.AMOUNT_PAID, 0,0, -AP_UTILITIES_PKG.AP_ROUND_CURRENCY((AI.AMOUNT_PAID * NVL(AI.EXCHANGE_RATE,1)),ASP.BASE_CURRENCY_CODE)), AP_UTILITIES_PKG.AP_ROUND_CURRENCY( NVL(APS.AMOUNT_REMAINING,0) * NVL(AI.EXCHANGE_RATE,1),ASP.BASE_CURRENCY_CODE)), AI.PAYMENT_CURRENCY_CODE, /* 5 */ NVL(APS.PAYMENT_PRIORITY,99), /* 6 */ NVL(PV.VENDOR_TYPE_LOOKUP_CODE,'-1'), /* 7 */ NVL(AI.PAY_GROUP_LOOKUP_CODE,'-1'), /* 8 */ APS.DUE_DATE, /* 9 */ APS.ORG_ID, /* 10 */ AI.PROJECT_ID, /* 11 */ NVL(APS.HOLD_FLAG, 'N'), /* 12 */ AI.INVOICE_NUM, /* 13 */ (APS.INVOICE_ID || 'X' || APS.PAYMENT_NUM) /* 14 */ FROM AP_PAYMENT_SCHEDULES_ALL APS, AP_INVOICES_ALL AI, AP_SUPPLIER_SITES_ALL PVS, AP_SUPPLIERS PV, AP_SYSTEM_PARAMETERS_ALL ASP WHERE APS.INVOICE_ID = AI.INVOICE_ID AND AI.ORG_ID = ASP.ORG_ID AND AI.ORG_ID = PVS.ORG_ID AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND PVS.VENDOR_ID = PV.VENDOR_ID AND APS.ORG_ID = AI.ORG_ID AND APS.PAYMENT_NUM = DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', 1, APS.PAYMENT_NUM) AND ((aps.Payment_Status_Flag != 'Y' AND ai.Invoice_Type_LookUp_Code != 'PREPAYMENT') OR (aps.Payment_Status_Flag = 'Y' AND ai.Invoice_Type_LookUp_Code = 'PREPAYMENT' AND Nvl(ai.Amount_Paid,0) != 0)) AND NOT EXISTS ( SELECT 1 FROM Ap_Invoice_Distributions_All aid WHERE aid.invoice_id = ai.invoice_id AND MATCH_STATUS_FLAG != 'A' ) AND EXISTS ( SELECT 1 FROM Ap_Invoice_Distributions_All aid WHERE aid.invoice_id = ai.invoice_id )

View Text - HTML Formatted

SELECT APS.INVOICE_ID
, /* 1 */ APS.PAYMENT_NUM
, /* 2 */ (DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 3 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0)))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 4 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AP_UTILITIES_PKG.AP_ROUND_CURRENCY((AI.AMOUNT_PAID * NVL(AI.EXCHANGE_RATE
, 1))
, ASP.BASE_CURRENCY_CODE))
, AP_UTILITIES_PKG.AP_ROUND_CURRENCY( NVL(APS.AMOUNT_REMAINING
, 0) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, AI.PAYMENT_CURRENCY_CODE
, /* 5 */ NVL(APS.PAYMENT_PRIORITY
, 99)
, /* 6 */ NVL(PV.VENDOR_TYPE_LOOKUP_CODE
, '-1')
, /* 7 */ NVL(AI.PAY_GROUP_LOOKUP_CODE
, '-1')
, /* 8 */ APS.DUE_DATE
, /* 9 */ APS.ORG_ID
, /* 10 */ AI.PROJECT_ID
, /* 11 */ NVL(APS.HOLD_FLAG
, 'N')
, /* 12 */ AI.INVOICE_NUM
, /* 13 */ (APS.INVOICE_ID || 'X' || APS.PAYMENT_NUM) /* 14 */
FROM AP_PAYMENT_SCHEDULES_ALL APS
, AP_INVOICES_ALL AI
, AP_SUPPLIER_SITES_ALL PVS
, AP_SUPPLIERS PV
, AP_SYSTEM_PARAMETERS_ALL ASP
WHERE APS.INVOICE_ID = AI.INVOICE_ID
AND AI.ORG_ID = ASP.ORG_ID
AND AI.ORG_ID = PVS.ORG_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PVS.VENDOR_ID = PV.VENDOR_ID
AND APS.ORG_ID = AI.ORG_ID
AND APS.PAYMENT_NUM = DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, 1
, APS.PAYMENT_NUM)
AND ((APS.PAYMENT_STATUS_FLAG != 'Y'
AND AI.INVOICE_TYPE_LOOKUP_CODE != 'PREPAYMENT') OR (APS.PAYMENT_STATUS_FLAG = 'Y'
AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND NVL(AI.AMOUNT_PAID
, 0) != 0))
AND NOT EXISTS ( SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID = AI.INVOICE_ID
AND MATCH_STATUS_FLAG != 'A' )
AND EXISTS ( SELECT 1
FROM AP_INVOICE_DISTRIBUTIONS_ALL AID
WHERE AID.INVOICE_ID = AI.INVOICE_ID )