View: POS_AP_EXPENSE_REPORTS_V

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.POS_AP_EXPENSE_REPORTS_V

View Text

SELECT AI.DESCRIPTION DESCRIPTION
, TO_CHAR(AI.INVOICE_AMOUNT
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) INVOICE_AMOUNT
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, TO_CHAR( POS_AP_INVOICES_PKG.GET_AMOUNT_WITHHELD( AI.INVOICE_ID)) || ' ' || AI.INVOICE_CURRENCY_CODE WITHHELD_AMOUNT_CURRENCY_CODE
, AI.INVOICE_DATE INVOICE_DATE
, AI.INVOICE_ID INVOICE_ID
, AI.INVOICE_NUM INVOICE_NUM
, APS.DUE_DATE DUE_DATE
, TO_CHAR(APS.GROSS_AMOUNT
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(APS.AMOUNT_REMAINING
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, APS.DISCOUNT_DATE DISCOUNT_DATE
, TO_CHAR(APS.DISCOUNT_AMOUNT_AVAILABLE
, FND_CURRENCY.SAFE_GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) DISCOUNT_AMOUNT_AVAILABLE
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, POS_AP_INVOICES_PKG.GET_PO_NUMBER_LIST( AI.INVOICE_ID) PO_NUMBER
, POS_AP_INVOICE_PAYMENTS_PKG.GET_PAID_BY_LIST( AI.INVOICE_ID
, APS.PAYMENT_NUM) PAYMENT_NUMBER
, PVS.VENDOR_SITE_CODE VENDOR_SITE_CODE
, AI.VENDOR_SITE_ID VENDOR_SITE_ID
, AI.VENDOR_ID VENDOR_ID
, PV.EMPLOYEE_ID
, AI.INVOICE_ID REPORT_PRIMARY_KEY
, 'INVOICE' DOCUMENT_TYPE
, FNDLY.MEANING MANAGER_APPROVED
, FNDLY.MEANING AP_APPROVED
, AI.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AI.ATTRIBUTE1 ATTRIBUTE1
, AI.ATTRIBUTE2 ATTRIBUTE2
, AI.ATTRIBUTE3 ATTRIBUTE3
, AI.ATTRIBUTE4 ATTRIBUTE4
, AI.ATTRIBUTE5 ATTRIBUTE5
, AI.ATTRIBUTE6 ATTRIBUTE6
, AI.ATTRIBUTE7 ATTRIBUTE7
, AI.ATTRIBUTE8 ATTRIBUTE8
, AI.ATTRIBUTE9 ATTRIBUTE9
, AI.ATTRIBUTE10 ATTRIBUTE10
, AI.ATTRIBUTE11 ATTRIBUTE11
, AI.ATTRIBUTE12 ATTRIBUTE12
, AI.ATTRIBUTE13 ATTRIBUTE13
, AI.ATTRIBUTE14 ATTRIBUTE14
, AI.ATTRIBUTE15 ATTRIBUTE15
, P.FULL_NAME
FROM AP_INVOICES_ALL AI
, AP_PAYMENT_SCHEDULES_ALL APS
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
, FND_LOOKUPS FNDLN
, FND_LOOKUPS FNDLY
, PO_VENDORS PV
, PO_VENDOR_SITES_ALL PVS
, PER_PEOPLE_X P
WHERE AI.INVOICE_ID= APS.INVOICE_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_ID
AND AI.VENDOR_ID = PV.VENDOR_ID
AND ALC1.LOOKUP_TYPE = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE = AI.INVOICE_TYPE_LOOKUP_CODE
AND ALC2.LOOKUP_TYPE = 'INVOICE PAYMENT STATUS'
AND ALC2.LOOKUP_CODE = AI.PAYMENT_STATUS_FLAG
AND AI.INVOICE_TYPE_LOOKUP_CODE||'' = 'EXPENSE REPORT'
AND FNDLY.LOOKUP_TYPE = 'YES_NO'
AND FNDLY.LOOKUP_CODE = 'Y'
AND FNDLN.LOOKUP_TYPE = 'YES_NO'
AND FNDLN.LOOKUP_CODE = 'N'
AND P.PERSON_ID = PV.EMPLOYEE_ID UNION SELECT AERH.DESCRIPTION
, TO_CHAR(AERH.TOTAL
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( AERH.DEFAULT_CURRENCY_CODE
, 30))
, AERH.DEFAULT_CURRENCY_CODE
, ''
, AERH.WEEK_END_DATE
, -1
, AERH.INVOICE_NUM
, TO_DATE('')
, TO_CHAR(AERH.TOTAL
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( AERH.DEFAULT_CURRENCY_CODE
, 30))
, TO_CHAR(AERH.TOTAL
, FND_CURRENCY.SAFE_GET_FORMAT_MASK( AERH.DEFAULT_CURRENCY_CODE
, 30))
, TO_DATE('')
, ''
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, ''
, ''
, ' '
, -1
, -1
, AERH.EMPLOYEE_ID
, AERH.REPORT_HEADER_ID
, 'EXPENSE REPORT'
, DECODE(AERH.WORKFLOW_APPROVED_FLAG
, 'Y'
, FNDLY.MEANING
, 'A'
, FNDLY.MEANING
, 'M'
, FNDLY.MEANING
, FNDLN.MEANING)
, DECODE(AERH.WORKFLOW_APPROVED_FLAG
, 'Y'
, FNDLY.MEANING
, 'A'
, FNDLY.MEANING
, 'P'
, FNDLY.MEANING
, FNDLN.MEANING)
, AERH.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, AERH.ATTRIBUTE1 ATTRIBUTE1
, AERH.ATTRIBUTE2 ATTRIBUTE2
, AERH.ATTRIBUTE3 ATTRIBUTE3
, AERH.ATTRIBUTE4 ATTRIBUTE4
, AERH.ATTRIBUTE5 ATTRIBUTE5
, AERH.ATTRIBUTE6 ATTRIBUTE6
, AERH.ATTRIBUTE7 ATTRIBUTE7
, AERH.ATTRIBUTE8 ATTRIBUTE8
, AERH.ATTRIBUTE9 ATTRIBUTE9
, AERH.ATTRIBUTE10 ATTRIBUTE10
, AERH.ATTRIBUTE11 ATTRIBUTE11
, AERH.ATTRIBUTE12 ATTRIBUTE12
, AERH.ATTRIBUTE13 ATTRIBUTE13
, AERH.ATTRIBUTE14 ATTRIBUTE14
, AERH.ATTRIBUTE15 ATTRIBUTE15
, P.FULL_NAME
FROM AP_EXPENSE_REPORT_HEADERS_ALL AERH
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
, FND_LOOKUPS FNDLN
, FND_LOOKUPS FNDLY
, PER_PEOPLE_X P
WHERE AERH.VOUCHNO||''=0
AND AERH.SOURCE != 'NONVALIDATEDWEBEXPENSE'
AND ALC1.LOOKUP_TYPE = 'INVOICE TYPE'
AND ALC1.LOOKUP_CODE = 'EXPENSE REPORT'
AND ALC2.LOOKUP_TYPE = 'INVOICE PAYMENT STATUS'
AND ALC2.LOOKUP_CODE = 'N'
AND FNDLY.LOOKUP_TYPE = 'YES_NO'
AND FNDLY.LOOKUP_CODE = 'Y'
AND FNDLN.LOOKUP_TYPE = 'YES_NO'
AND FNDLN.LOOKUP_CODE = 'N'
AND P.PERSON_ID = AERH.EMPLOYEE_ID

Columns