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.POS_AP_EXPENSE_REPORTS_V

Source

View Text - Preformatted


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(NULL), 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(NULL), '', 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

View Text - HTML Formatted

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(NULL)
, 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(NULL)
, ''
, 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