VIEW: APPS.XTR_AP_OPEN_TRX_V

Source

View Text - Preformatted

SELECT invoice_id, payment_num, source, source_name, trx_type_id, trx_type_name, org_id, org_name, vendor_id, vendor_name, vendor_site_id, vendor_site_name, company_code, trx_date, trx_number, currency_code, sob_currency_code, amount, max_discounted_amount, min_discounted_amount, max_discounted_date, min_discounted_date, min_due_date, applied_trx FROM XTR_AP_OPEN_APLD_TRX_V UNION ALL SELECT AIA.INVOICE_ID, to_number(null), 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, SUM((-1)* DECODE(AIA.PAYMENT_CURRENCY_CODE,AIA.INVOICE_CURRENCY_CODE, NVL(AID.PREPAY_AMOUNT_REMAINING,AID.AMOUNT), gl_currency_api.convert_amount( AIA.INVOICE_CURRENCY_CODE, AIA.PAYMENT_CURRENCY_CODE, AIA.PAYMENT_CROSS_RATE_DATE, AIA.PAYMENT_CROSS_RATE_TYPE, NVL(AID.PREPAY_AMOUNT_REMAINING,AID.AMOUNT)))), SUM((-1)* DECODE(AIA.PAYMENT_CURRENCY_CODE,AIA.INVOICE_CURRENCY_CODE, NVL(AID.PREPAY_AMOUNT_REMAINING,AID.AMOUNT), gl_currency_api.convert_amount( AIA.INVOICE_CURRENCY_CODE, AIA.PAYMENT_CURRENCY_CODE, AIA.PAYMENT_CROSS_RATE_DATE, AIA.PAYMENT_CROSS_RATE_TYPE, NVL(AID.PREPAY_AMOUNT_REMAINING,AID.AMOUNT)))), SUM((-1)* DECODE(AIA.PAYMENT_CURRENCY_CODE,AIA.INVOICE_CURRENCY_CODE, NVL(AID.PREPAY_AMOUNT_REMAINING,AID.AMOUNT), gl_currency_api.convert_amount( AIA.INVOICE_CURRENCY_CODE, AIA.PAYMENT_CURRENCY_CODE, AIA.PAYMENT_CROSS_RATE_DATE, AIA.PAYMENT_CROSS_RATE_TYPE, NVL(AID.PREPAY_AMOUNT_REMAINING,AID.AMOUNT)))), to_date(null), to_date(null), to_date(null), 'N' FROM AP_INVOICES_ALL AIA, AP_INVOICE_DISTRIBUTIONS_ALL AID, 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 = AID.INVOICE_ID AND AIA.ORG_ID = AID.ORG_ID AND AIA.CANCELLED_DATE IS NULL AND AIA.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT' AND NVL(AID.REVERSAL_FLAG,'N') <> 'Y' AND AIA.PAYMENT_STATUS_FLAG = 'Y' AND nvl(AID.PREPAY_AMOUNT_REMAINING,AID.AMOUNT) > 0 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 (+) GROUP BY AIA.INVOICE_ID, 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

View Text - HTML Formatted

SELECT INVOICE_ID
, PAYMENT_NUM
, SOURCE
, SOURCE_NAME
, TRX_TYPE_ID
, TRX_TYPE_NAME
, ORG_ID
, ORG_NAME
, VENDOR_ID
, VENDOR_NAME
, VENDOR_SITE_ID
, VENDOR_SITE_NAME
, COMPANY_CODE
, TRX_DATE
, TRX_NUMBER
, CURRENCY_CODE
, SOB_CURRENCY_CODE
, AMOUNT
, MAX_DISCOUNTED_AMOUNT
, MIN_DISCOUNTED_AMOUNT
, MAX_DISCOUNTED_DATE
, MIN_DISCOUNTED_DATE
, MIN_DUE_DATE
, APPLIED_TRX
FROM XTR_AP_OPEN_APLD_TRX_V UNION ALL SELECT AIA.INVOICE_ID
, TO_NUMBER(NULL)
, 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
, SUM((-1)* DECODE(AIA.PAYMENT_CURRENCY_CODE
, AIA.INVOICE_CURRENCY_CODE
, NVL(AID.PREPAY_AMOUNT_REMAINING
, AID.AMOUNT)
, GL_CURRENCY_API.CONVERT_AMOUNT( AIA.INVOICE_CURRENCY_CODE
, AIA.PAYMENT_CURRENCY_CODE
, AIA.PAYMENT_CROSS_RATE_DATE
, AIA.PAYMENT_CROSS_RATE_TYPE
, NVL(AID.PREPAY_AMOUNT_REMAINING
, AID.AMOUNT))))
, SUM((-1)* DECODE(AIA.PAYMENT_CURRENCY_CODE
, AIA.INVOICE_CURRENCY_CODE
, NVL(AID.PREPAY_AMOUNT_REMAINING
, AID.AMOUNT)
, GL_CURRENCY_API.CONVERT_AMOUNT( AIA.INVOICE_CURRENCY_CODE
, AIA.PAYMENT_CURRENCY_CODE
, AIA.PAYMENT_CROSS_RATE_DATE
, AIA.PAYMENT_CROSS_RATE_TYPE
, NVL(AID.PREPAY_AMOUNT_REMAINING
, AID.AMOUNT))))
, SUM((-1)* DECODE(AIA.PAYMENT_CURRENCY_CODE
, AIA.INVOICE_CURRENCY_CODE
, NVL(AID.PREPAY_AMOUNT_REMAINING
, AID.AMOUNT)
, GL_CURRENCY_API.CONVERT_AMOUNT( AIA.INVOICE_CURRENCY_CODE
, AIA.PAYMENT_CURRENCY_CODE
, AIA.PAYMENT_CROSS_RATE_DATE
, AIA.PAYMENT_CROSS_RATE_TYPE
, NVL(AID.PREPAY_AMOUNT_REMAINING
, AID.AMOUNT))))
, TO_DATE(NULL)
, TO_DATE(NULL)
, TO_DATE(NULL)
, 'N'
FROM AP_INVOICES_ALL AIA
, AP_INVOICE_DISTRIBUTIONS_ALL AID
, 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 = AID.INVOICE_ID
AND AIA.ORG_ID = AID.ORG_ID
AND AIA.CANCELLED_DATE IS NULL
AND AIA.INVOICE_TYPE_LOOKUP_CODE = 'PREPAYMENT'
AND NVL(AID.REVERSAL_FLAG
, 'N') <> 'Y'
AND AIA.PAYMENT_STATUS_FLAG = 'Y'
AND NVL(AID.PREPAY_AMOUNT_REMAINING
, AID.AMOUNT) > 0
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 (+) GROUP BY AIA.INVOICE_ID
, 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