eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.CE_AP_FC_DISC_INVOICES_V

Source

View Text - Preformatted


SELECT APS.INVOICE_ID, /* 1 */ APS.PAYMENT_NUM, /* 2 */ APS.DUE_DATE, /* 3 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 4 */ DECODE(AI.AMOUNT_PAID, 0,0, -AI.AMOUNT_PAID),NVL(APS.AMOUNT_REMAINING,0) - NVL(APS.DISCOUNT_AMOUNT_AVAILABLE,0)), DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 5 */ 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(APS.DISCOUNT_AMOUNT_AVAILABLE,0)) * NVL(AI.EXCHANGE_RATE,1), ASP.BASE_CURRENCY_CODE)), NVL(APS.DISCOUNT_DATE, DUE_DATE), /* 6 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 7 */ DECODE(AI.AMOUNT_PAID, 0,0, -AI.AMOUNT_PAID), NVL(APS.AMOUNT_REMAINING,0) - NVL(APS.SECOND_DISC_AMT_AVAILABLE,0)), DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 8 */ 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(APS.SECOND_DISC_AMT_AVAILABLE,0)) * NVL(AI.EXCHANGE_RATE,1), ASP.BASE_CURRENCY_CODE)), NVL(APS.SECOND_DISCOUNT_DATE,DUE_DATE), /* 9 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 10 */ DECODE(AI.AMOUNT_PAID, 0,0, -AI.AMOUNT_PAID), NVL(APS.AMOUNT_REMAINING,0) - NVL(APS.THIRD_DISC_AMT_AVAILABLE,0)), DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 11 */ 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(APS.THIRD_DISC_AMT_AVAILABLE,0)) * NVL(AI.EXCHANGE_RATE,1), ASP.BASE_CURRENCY_CODE)), NVL(APS.THIRD_DISCOUNT_DATE,DUE_DATE), /* 12 */ AI.PAYMENT_CURRENCY_CODE, /* 13 */ NVL(APS.PAYMENT_PRIORITY,99), /* 14 */ NVL(PV.VENDOR_TYPE_LOOKUP_CODE,'-1'), /* 15 */ NVL(AI.PAY_GROUP_LOOKUP_CODE,'-1'), /* 16 */ AI.ORG_ID, /* 17 */ AI.PROJECT_ID, /* 18 */ NVL(APS.HOLD_FLAG, 'N'), /* 19 */ AI.INVOICE_NUM /* 20 */ FROM AP_PAYMENT_SCHEDULES_ALL APS, AP_INVOICES_ALL AI, PO_VENDOR_SITES_ALL PVS, PO_VENDORS PV, AP_SYSTEM_PARAMETERS_ALL ASP WHERE APS.INVOICE_ID = AI.INVOICE_ID AND (ASP.ORG_ID = AI.ORG_ID OR ASP.ORG_ID IS NULL) AND (AI.ORG_ID = PVS.ORG_ID OR AI.ORG_ID IS NULL) AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID AND PVS.VENDOR_ID = PV.VENDOR_ID AND AI.INVOICE_TYPE_LOOKUP_CODE != 'PAYMENT REQUEST' AND (APS.ORG_ID = AI.ORG_ID OR APS.ORG_ID IS NULL) AND APS.PAYMENT_NUM = DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', 1,APS.PAYMENT_NUM) AND (APS.PAYMENT_STATUS_FLAG != 'Y' OR (APS.PAYMENT_STATUS_FLAG = 'Y' AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND AI.AMOUNT_PAID != 0)) UNION ALL SELECT APS.INVOICE_ID, /* 1 */ APS.PAYMENT_NUM, /* 2 */ APS.DUE_DATE, /* 3 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 4 */ DECODE(AI.AMOUNT_PAID, 0,0, -AI.AMOUNT_PAID),NVL(APS.AMOUNT_REMAINING,0) - NVL(APS.DISCOUNT_AMOUNT_AVAILABLE,0)), DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 5 */ 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(APS.DISCOUNT_AMOUNT_AVAILABLE,0)) * NVL(AI.EXCHANGE_RATE,1), ASP.BASE_CURRENCY_CODE)), NVL(APS.DISCOUNT_DATE, DUE_DATE) , /* 6 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 7 */ DECODE(AI.AMOUNT_PAID, 0,0, -AI.AMOUNT_PAID), NVL(APS.AMOUNT_REMAINING,0) - NVL(APS.SECOND_DISC_AMT_AVAILABLE,0)), DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 8 */ 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(APS.SECOND_DISC_AMT_AVAILABLE,0)) * NVL(AI.EXCHANGE_RATE,1), ASP.BASE_CURRENCY_CODE)), NVL(APS.SECOND_DISCOUNT_DATE,DUE_DATE) , /* 9 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 10 */ DECODE(AI.AMOUNT_PAID, 0,0, -AI.AMOUNT_PAID), NVL(APS.AMOUNT_REMAINING,0) - NVL(APS.THIRD_DISC_AMT_AVAILABLE,0)), DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', /* 11 */ 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(APS.THIRD_DISC_AMT_AVAILABLE,0)) * NVL(AI.EXCHANGE_RATE,1), ASP.BASE_CURRENCY_CODE)), NVL(APS.THIRD_DISCOUNT_DATE,DUE_DATE) , /* 12 */ AI.PAYMENT_CURRENCY_CODE, /* 13 */ NVL(APS.PAYMENT_PRIORITY,99), /* 14 */ '-1', /* 15 */ NVL(AI.PAY_GROUP_LOOKUP_CODE,'-1'), /* 16 */ AI.ORG_ID, /* 17 */ AI.PROJECT_ID, /* 18 */ NVL(APS.HOLD_FLAG, 'N'), /* 19 */ AI.INVOICE_NUM /* 20 */ FROM AP_PAYMENT_SCHEDULES_ALL APS, AP_INVOICES_ALL AI , hz_parties hzp, hz_party_sites hzs, AP_SYSTEM_PARAMETERS_ALL ASP WHERE APS.INVOICE_ID = AI.INVOICE_ID AND (ASP.ORG_ID = AI.ORG_ID OR ASP.ORG_ID IS NULL) AND (APS.ORG_ID = AI.ORG_ID OR APS.ORG_ID IS NULL) AND ai.party_id = hzp.party_id AND ai.party_site_id = hzs.party_site_id AND hzp.party_id = hzs.party_id AND APS.PAYMENT_NUM = DECODE(AI.INVOICE_TYPE_LOOKUP_CODE, 'PREPAYMENT', 1,APS.PAYMENT_NUM) AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PAYMENT REQUEST' AND (APS.PAYMENT_STATUS_FLAG != 'Y' OR (APS.PAYMENT_STATUS_FLAG = 'Y' AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND AI.AMOUNT_PAID != 0))

View Text - HTML Formatted

SELECT APS.INVOICE_ID
, /* 1 */ APS.PAYMENT_NUM
, /* 2 */ APS.DUE_DATE
, /* 3 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 4 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 5 */ 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(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.DISCOUNT_DATE
, DUE_DATE)
, /* 6 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 7 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.SECOND_DISC_AMT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 8 */ 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(APS.SECOND_DISC_AMT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.SECOND_DISCOUNT_DATE
, DUE_DATE)
, /* 9 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 10 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.THIRD_DISC_AMT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 11 */ 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(APS.THIRD_DISC_AMT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.THIRD_DISCOUNT_DATE
, DUE_DATE)
, /* 12 */ AI.PAYMENT_CURRENCY_CODE
, /* 13 */ NVL(APS.PAYMENT_PRIORITY
, 99)
, /* 14 */ NVL(PV.VENDOR_TYPE_LOOKUP_CODE
, '-1')
, /* 15 */ NVL(AI.PAY_GROUP_LOOKUP_CODE
, '-1')
, /* 16 */ AI.ORG_ID
, /* 17 */ AI.PROJECT_ID
, /* 18 */ NVL(APS.HOLD_FLAG
, 'N')
, /* 19 */ AI.INVOICE_NUM /* 20 */
FROM AP_PAYMENT_SCHEDULES_ALL APS
, AP_INVOICES_ALL AI
, PO_VENDOR_SITES_ALL PVS
, PO_VENDORS PV
, AP_SYSTEM_PARAMETERS_ALL ASP
WHERE APS.INVOICE_ID = AI.INVOICE_ID
AND (ASP.ORG_ID = AI.ORG_ID OR ASP.ORG_ID IS NULL)
AND (AI.ORG_ID = PVS.ORG_ID OR AI.ORG_ID IS NULL)
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND PVS.VENDOR_ID = PV.VENDOR_ID
AND AI.INVOICE_TYPE_LOOKUP_CODE != 'PAYMENT REQUEST'
AND (APS.ORG_ID = AI.ORG_ID OR APS.ORG_ID IS NULL)
AND APS.PAYMENT_NUM = DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, 1
, APS.PAYMENT_NUM)
AND (APS.PAYMENT_STATUS_FLAG != 'Y' OR (APS.PAYMENT_STATUS_FLAG = 'Y'
AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND AI.AMOUNT_PAID != 0)) UNION ALL SELECT APS.INVOICE_ID
, /* 1 */ APS.PAYMENT_NUM
, /* 2 */ APS.DUE_DATE
, /* 3 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 4 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 5 */ 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(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.DISCOUNT_DATE
, DUE_DATE)
, /* 6 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 7 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.SECOND_DISC_AMT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 8 */ 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(APS.SECOND_DISC_AMT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.SECOND_DISCOUNT_DATE
, DUE_DATE)
, /* 9 */ DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 10 */ DECODE(AI.AMOUNT_PAID
, 0
, 0
, -AI.AMOUNT_PAID)
, NVL(APS.AMOUNT_REMAINING
, 0) - NVL(APS.THIRD_DISC_AMT_AVAILABLE
, 0))
, DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, /* 11 */ 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(APS.THIRD_DISC_AMT_AVAILABLE
, 0)) * NVL(AI.EXCHANGE_RATE
, 1)
, ASP.BASE_CURRENCY_CODE))
, NVL(APS.THIRD_DISCOUNT_DATE
, DUE_DATE)
, /* 12 */ AI.PAYMENT_CURRENCY_CODE
, /* 13 */ NVL(APS.PAYMENT_PRIORITY
, 99)
, /* 14 */ '-1'
, /* 15 */ NVL(AI.PAY_GROUP_LOOKUP_CODE
, '-1')
, /* 16 */ AI.ORG_ID
, /* 17 */ AI.PROJECT_ID
, /* 18 */ NVL(APS.HOLD_FLAG
, 'N')
, /* 19 */ AI.INVOICE_NUM /* 20 */
FROM AP_PAYMENT_SCHEDULES_ALL APS
, AP_INVOICES_ALL AI
, HZ_PARTIES HZP
, HZ_PARTY_SITES HZS
, AP_SYSTEM_PARAMETERS_ALL ASP
WHERE APS.INVOICE_ID = AI.INVOICE_ID
AND (ASP.ORG_ID = AI.ORG_ID OR ASP.ORG_ID IS NULL)
AND (APS.ORG_ID = AI.ORG_ID OR APS.ORG_ID IS NULL)
AND AI.PARTY_ID = HZP.PARTY_ID
AND AI.PARTY_SITE_ID = HZS.PARTY_SITE_ID
AND HZP.PARTY_ID = HZS.PARTY_ID
AND APS.PAYMENT_NUM = DECODE(AI.INVOICE_TYPE_LOOKUP_CODE
, 'PREPAYMENT'
, 1
, APS.PAYMENT_NUM)
AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PAYMENT REQUEST'
AND (APS.PAYMENT_STATUS_FLAG != 'Y' OR (APS.PAYMENT_STATUS_FLAG = 'Y'
AND AI.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND AI.AMOUNT_PAID != 0))