View: XTR_AP_OPEN_APLD_TRX_V

Product:	XTR - Treasury
Description:
Implementation/DBA Data:	APPS.XTR_AP_OPEN_APLD_TRX_V

View Text

SELECT AIA.INVOICE_ID
, APS.PAYMENT_NUM
, DECODE(ALK.DISPLAYED_FIELD
, NULL
, DECODE(AIA.SOURCE
, 'MANUAL INVOICE ENTRY'
, 'XTR_AP_SOURCE_MANUAL'
, 'XTR_AP_SOURCE_OTHER')
, AIA.SOURCE)
, DECODE(ALK.DISPLAYED_FIELD
, NULL
, DECODE(AIA.SOURCE
, 'MANUAL INVOICE ENTRY'
, FND_MESSAGE.GET_STRING('XTR'
, 'XTR_AP_SOURCE_MANUAL')
, FND_MESSAGE.GET_STRING('XTR'
, 'XTR_AP_SOURCE_OTHER'))
, ALK.DISPLAYED_FIELD)
, AIA.INVOICE_TYPE_LOOKUP_CODE
, ALC.DISPLAYED_FIELD
, AIA.ORG_ID
, HOU.NAME
, AIA.VENDOR_ID
, PO.VENDOR_NAME
, AIA.VENDOR_SITE_ID
, PVS.VENDOR_SITE_CODE
, XPV.PARTY_CODE
, AIA.INVOICE_DATE
, AIA.INVOICE_NUM
, AIA.PAYMENT_CURRENCY_CODE
, XPV.SET_OF_BOOKS_CURRENCY
, NVL(APS.AMOUNT_REMAINING
, 0)
, DECODE(PVS.ALWAYS_TAKE_DISC_FLAG
, 'Y'
, APS.AMOUNT_REMAINING-NVL(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0)
, APS.AMOUNT_REMAINING -DECODE(SIGN(APS.DISCOUNT_DATE-SYSDATE)
, 1
, APS.DISCOUNT_AMOUNT_AVAILABLE
, DECODE(SIGN(APS.SECOND_DISCOUNT_DATE-SYSDATE)
, 1
, APS.SECOND_DISC_AMT_AVAILABLE
, DECODE(SIGN(APS.THIRD_DISCOUNT_DATE-SYSDATE)
, 1
, APS.THIRD_DISC_AMT_AVAILABLE
, 0 ) ) ) )
, DECODE(PVS.ALWAYS_TAKE_DISC_FLAG
, 'Y'
, APS.AMOUNT_REMAINING-NVL(APS.DISCOUNT_AMOUNT_AVAILABLE
, 0)
, APS.AMOUNT_REMAINING -DECODE(SIGN(APS.THIRD_DISCOUNT_DATE-SYSDATE)
, 1
, APS.THIRD_DISC_AMT_AVAILABLE
, DECODE(SIGN(APS.SECOND_DISCOUNT_DATE-SYSDATE)
, 1
, APS.SECOND_DISC_AMT_AVAILABLE
, DECODE(SIGN(APS.DISCOUNT_DATE-SYSDATE)
, 1
, APS.DISCOUNT_AMOUNT_AVAILABLE
, 0 ) ) ) )
, DECODE(PVS.ALWAYS_TAKE_DISC_FLAG
, 'Y'
, APS.DUE_DATE
, DECODE(SIGN(APS.DISCOUNT_DATE-SYSDATE)
, 1
, APS.DISCOUNT_DATE
, DECODE(SIGN(APS.SECOND_DISCOUNT_DATE-SYSDATE)
, 1
, APS.SECOND_DISCOUNT_DATE
, DECODE(SIGN(APS.THIRD_DISCOUNT_DATE-SYSDATE)
, 1
, APS.THIRD_DISCOUNT_DATE
, APS.DUE_DATE ) ) ) )
, DECODE(PVS.ALWAYS_TAKE_DISC_FLAG
, 'Y'
, APS.DUE_DATE
, DECODE(SIGN(APS.THIRD_DISCOUNT_DATE-SYSDATE)
, 1
, APS.THIRD_DISCOUNT_DATE
, DECODE(SIGN(APS.SECOND_DISCOUNT_DATE-SYSDATE)
, 1
, APS.SECOND_DISCOUNT_DATE
, DECODE(SIGN(APS.DISCOUNT_DATE-SYSDATE)
, 1
, APS.DISCOUNT_DATE
, APS.DUE_DATE ) ) ) )
, APS.DUE_DATE
, 'Y'
FROM AP_INVOICES_ALL AIA
, AP_PAYMENT_SCHEDULES_ALL APS
, PO_VENDORS PO
, PO_VENDOR_SITES_ALL PVS
, HR_OPERATING_UNITS HOU
, XTR_PARTIES_V XPV
, AP_LOOKUP_CODES ALC
, AP_LOOKUP_CODES ALK
WHERE AIA.INVOICE_ID = APS.INVOICE_ID
AND AIA.ORG_ID = APS.ORG_ID
AND AIA.CANCELLED_DATE IS NULL
AND AIA.PAYMENT_STATUS_FLAG IN ('N'
, 'P')
AND APS.PAYMENT_STATUS_FLAG IN ('N'
, 'P')
AND AIA.ORG_ID = HOU.ORGANIZATION_ID
AND HOU.SET_OF_BOOKS_ID = (SELECT GLLE.LEDGER_ID
FROM GL_LEDGER_LE_V GLLE
WHERE GLLE.LEGAL_ENTITY_ID = (XPV.LEGAL_ENTITY_ID)
AND GLLE.LEDGER_CATEGORY_CODE = 'PRIMARY')
AND AIA.VENDOR_ID = PO.VENDOR_ID
AND AIA.ORG_ID = PVS.ORG_ID
AND AIA.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND ALC.LOOKUP_TYPE = 'INVOICE TYPE'
AND AIA.INVOICE_TYPE_LOOKUP_CODE = ALC.LOOKUP_CODE
AND ALK.LOOKUP_TYPE (+) = 'SOURCE'
AND AIA.SOURCE = ALK.LOOKUP_CODE (+)

Columns