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

Source

View Text - Preformatted


SELECT AI.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY, AI.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE, AI.DESCRIPTION DESCRIPTION , TO_CHAR(AI.INVOICE_AMOUNT,FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE, 30)) INVOICE_AMOUNT , AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , TO_CHAR(ICX_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 , APS.GROSS_AMOUNT GROSS_AMOUNT_QUERY , TO_CHAR(APS.GROSS_AMOUNT , FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE, 30)) GROSS_AMOUNT , TO_CHAR(APS.AMOUNT_REMAINING ,FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE,30)) AMOUNT_REMAINING , APS.DISCOUNT_DATE DISCOUNT_DATE , TO_CHAR(APS.DISCOUNT_AMOUNT_AVAILABLE,FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE, 30)) DISCOUNT_AMOUNT_AVAILABLE , ALC1.DISPLAYED_FIELD INVOICE_TYPE , ALC2.DISPLAYED_FIELD PAYMENT_STATUS , ICX_AP_INVOICES_PKG.GET_PO_NUMBER_LIST( AI.INVOICE_ID) PO_NUMBER , ICX_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 , P.PERSON_ID EMPLOYEE_ID, AI.INVOICE_ID REPORT_PRIMARY_KEY , 'INVOICE' DOCUMENT_TYPE , AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y') MANAGER_APPROVED , AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y') 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 PER_ALL_PEOPLE_F P, PO_VENDORS PV, AP_PAYMENT_SCHEDULES APS, PO_VENDOR_SITES PVS, AP_INVOICES AI, AP_LOOKUP_CODES ALC1, AP_LOOKUP_CODES ALC2 WHERE AI.INVOICE_ID= APS.INVOICE_ID AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_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 P.PERSON_ID = PV.EMPLOYEE_ID AND (TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE) AND PV.VENDOR_ID = AI.VENDOR_ID UNION ALL SELECT AI.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY, AI.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE, AI.DESCRIPTION DESCRIPTION , TO_CHAR(AI.INVOICE_AMOUNT,FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE, 30)) INVOICE_AMOUNT , AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE , TO_CHAR(ICX_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 , APS.GROSS_AMOUNT GROSS_AMOUNT_QUERY , TO_CHAR(APS.GROSS_AMOUNT , FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE, 30)) GROSS_AMOUNT , TO_CHAR(APS.AMOUNT_REMAINING ,FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE,30)) AMOUNT_REMAINING , APS.DISCOUNT_DATE DISCOUNT_DATE , TO_CHAR(APS.DISCOUNT_AMOUNT_AVAILABLE,FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE, 30)) DISCOUNT_AMOUNT_AVAILABLE , ALC1.DISPLAYED_FIELD INVOICE_TYPE , ALC2.DISPLAYED_FIELD PAYMENT_STATUS , ICX_AP_INVOICES_PKG.GET_PO_NUMBER_LIST( AI.INVOICE_ID) PO_NUMBER , ICX_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 , p.PERSON_ID, AI.INVOICE_ID REPORT_PRIMARY_KEY , 'INVOICE' DOCUMENT_TYPE , AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y') MANAGER_APPROVED , AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y') 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 PER_ALL_PEOPLE_F P, PO_VENDORS PV, AP_PAYMENT_SCHEDULES APS, PO_VENDOR_SITES PVS, AP_INVOICES AI, AP_LOOKUP_CODES ALC1, AP_LOOKUP_CODES ALC2 WHERE AI.INVOICE_ID= APS.INVOICE_ID AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_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||'' = 'STANDARD' AND P.PERSON_ID = AI.PAID_ON_BEHALF_EMPLOYEE_ID AND (TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE) AND PV.VENDOR_ID = AI.VENDOR_ID UNION ALL SELECT AERH.AMT_DUE_CCARD_COMPANY AMOUNT_DUE_CC_COMPANY, AERH.AMT_DUE_EMPLOYEE AMOUNT_DUE_EMP, AERH.DESCRIPTION, TO_CHAR((AERH.TOTAL),FND_CURRENCY.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE,30)) TOTAL_AMOUNT, AERH.DEFAULT_CURRENCY_CODE, '', AERH.WEEK_END_DATE, -1, AERH.INVOICE_NUM, TO_DATE(''), AERH.TOTAL GROSS_AMOUNT_QUERY, TO_CHAR((AERH.TOTAL),FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE,30)) GROSS_AMOUNT, TO_CHAR(AERH.TOTAL, FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE, 30)) AMOUNT_REMAINING, TO_DATE(''), '', ALC1.DISPLAYED_FIELD INVOICE_TYPE , ALC2.DISPLAYED_FIELD PAYMENT_STATUS , '', '', '', -1, -1, P.PERSON_ID, AERH.REPORT_HEADER_ID, 'EXPENSE REPORT', DECODE(AERH.WORKFLOW_APPROVED_FLAG, 'Y', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), 'A', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), 'M', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('N')), DECODE(AERH.WORKFLOW_APPROVED_FLAG, 'Y', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), 'A', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), 'P', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('N')), 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 PER_ALL_PEOPLE_F P, AP_EXPENSE_REPORT_HEADERS AERH, AP_LOOKUP_CODES ALC1, AP_LOOKUP_CODES ALC2 WHERE AERH.VOUCHNO + 0=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 P.PERSON_ID = AERH.EMPLOYEE_ID AND (TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE) UNION ALL SELECT AERH.AMT_DUE_CCARD_COMPANY AMOUNT_DUE_CC_COMPANY, AERH.AMT_DUE_EMPLOYEE AMOUNT_DUE_EMP, AERH.DESCRIPTION, TO_CHAR((AERH.TOTAL),FND_CURRENCY.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE,30)) TOTAL_AMOUNT, AERH.DEFAULT_CURRENCY_CODE, '', AERH.WEEK_END_DATE, -1, AERH.INVOICE_NUM, TO_DATE(''), AERH.TOTAL GROSS_AMOUNT_QUERY, TO_CHAR((AERH.TOTAL),FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE,30)) GROSS_AMOUNT, TO_CHAR(AERH.TOTAL, FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE, 30)) AMOUNT_REMAINING, TO_DATE(''), '', ALC1.DISPLAYED_FIELD INVOICE_TYPE , ALC2.DISPLAYED_FIELD PAYMENT_STATUS , '', '', '', -1, -1, p.PERSON_ID, AERH.REPORT_HEADER_ID, 'EXPENSE REPORT', DECODE(AERH.WORKFLOW_APPROVED_FLAG, 'Y', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), 'A', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), 'M', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('N')), DECODE(AERH.WORKFLOW_APPROVED_FLAG, 'Y', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), 'A', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), 'P', AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('Y'), AP_WEB_FND_LOOKUPS_PKG.getYesNoMeaning('N')), 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 PER_ALL_PEOPLE_F P, AP_EXPENSE_REPORT_HEADERS AERH, AP_LOOKUP_CODES ALC1, AP_LOOKUP_CODES ALC2 WHERE AERH.VOUCHNO + 0 =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 P.PERSON_ID = PAID_ON_BEHALF_EMPLOYEE_ID AND (TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE) AND AERH.EMPLOYEE_ID IS NULL

View Text - HTML Formatted

SELECT AI.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY
, AI.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE
, AI.DESCRIPTION DESCRIPTION
, TO_CHAR(AI.INVOICE_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) INVOICE_AMOUNT
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, TO_CHAR(ICX_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
, APS.GROSS_AMOUNT GROSS_AMOUNT_QUERY
, TO_CHAR(APS.GROSS_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(APS.AMOUNT_REMAINING
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, APS.DISCOUNT_DATE DISCOUNT_DATE
, TO_CHAR(APS.DISCOUNT_AMOUNT_AVAILABLE
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) DISCOUNT_AMOUNT_AVAILABLE
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, ICX_AP_INVOICES_PKG.GET_PO_NUMBER_LIST( AI.INVOICE_ID) PO_NUMBER
, ICX_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
, P.PERSON_ID EMPLOYEE_ID
, AI.INVOICE_ID REPORT_PRIMARY_KEY
, 'INVOICE' DOCUMENT_TYPE
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y') MANAGER_APPROVED
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y') 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 PER_ALL_PEOPLE_F P
, PO_VENDORS PV
, AP_PAYMENT_SCHEDULES APS
, PO_VENDOR_SITES PVS
, AP_INVOICES AI
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
WHERE AI.INVOICE_ID= APS.INVOICE_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_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 P.PERSON_ID = PV.EMPLOYEE_ID
AND (TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE)
AND PV.VENDOR_ID = AI.VENDOR_ID UNION ALL SELECT AI.AMT_DUE_CCARD_COMPANY AMT_DUE_CCARD_COMPANY
, AI.AMT_DUE_EMPLOYEE AMT_DUE_EMPLOYEE
, AI.DESCRIPTION DESCRIPTION
, TO_CHAR(AI.INVOICE_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) INVOICE_AMOUNT
, AI.INVOICE_CURRENCY_CODE INVOICE_CURRENCY_CODE
, TO_CHAR(ICX_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
, APS.GROSS_AMOUNT GROSS_AMOUNT_QUERY
, TO_CHAR(APS.GROSS_AMOUNT
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(APS.AMOUNT_REMAINING
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, APS.DISCOUNT_DATE DISCOUNT_DATE
, TO_CHAR(APS.DISCOUNT_AMOUNT_AVAILABLE
, FND_CURRENCY.GET_FORMAT_MASK(AI.INVOICE_CURRENCY_CODE
, 30)) DISCOUNT_AMOUNT_AVAILABLE
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, ICX_AP_INVOICES_PKG.GET_PO_NUMBER_LIST( AI.INVOICE_ID) PO_NUMBER
, ICX_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
, P.PERSON_ID
, AI.INVOICE_ID REPORT_PRIMARY_KEY
, 'INVOICE' DOCUMENT_TYPE
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y') MANAGER_APPROVED
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y') 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 PER_ALL_PEOPLE_F P
, PO_VENDORS PV
, AP_PAYMENT_SCHEDULES APS
, PO_VENDOR_SITES PVS
, AP_INVOICES AI
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
WHERE AI.INVOICE_ID= APS.INVOICE_ID
AND AI.VENDOR_SITE_ID = PVS.VENDOR_SITE_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||'' = 'STANDARD'
AND P.PERSON_ID = AI.PAID_ON_BEHALF_EMPLOYEE_ID
AND (TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE)
AND PV.VENDOR_ID = AI.VENDOR_ID UNION ALL SELECT AERH.AMT_DUE_CCARD_COMPANY AMOUNT_DUE_CC_COMPANY
, AERH.AMT_DUE_EMPLOYEE AMOUNT_DUE_EMP
, AERH.DESCRIPTION
, TO_CHAR((AERH.TOTAL)
, FND_CURRENCY.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE
, 30)) TOTAL_AMOUNT
, AERH.DEFAULT_CURRENCY_CODE
, ''
, AERH.WEEK_END_DATE
, -1
, AERH.INVOICE_NUM
, TO_DATE('')
, AERH.TOTAL GROSS_AMOUNT_QUERY
, TO_CHAR((AERH.TOTAL)
, FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(AERH.TOTAL
, FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, TO_DATE('')
, ''
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, ''
, ''
, ''
, -1
, -1
, P.PERSON_ID
, AERH.REPORT_HEADER_ID
, 'EXPENSE REPORT'
, DECODE(AERH.WORKFLOW_APPROVED_FLAG
, 'Y'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, 'A'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, 'M'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('N'))
, DECODE(AERH.WORKFLOW_APPROVED_FLAG
, 'Y'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, 'A'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, 'P'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('N'))
, 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 PER_ALL_PEOPLE_F P
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
WHERE AERH.VOUCHNO + 0=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 P.PERSON_ID = AERH.EMPLOYEE_ID
AND (TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE) UNION ALL SELECT AERH.AMT_DUE_CCARD_COMPANY AMOUNT_DUE_CC_COMPANY
, AERH.AMT_DUE_EMPLOYEE AMOUNT_DUE_EMP
, AERH.DESCRIPTION
, TO_CHAR((AERH.TOTAL)
, FND_CURRENCY.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE
, 30)) TOTAL_AMOUNT
, AERH.DEFAULT_CURRENCY_CODE
, ''
, AERH.WEEK_END_DATE
, -1
, AERH.INVOICE_NUM
, TO_DATE('')
, AERH.TOTAL GROSS_AMOUNT_QUERY
, TO_CHAR((AERH.TOTAL)
, FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(AERH.TOTAL
, FND_CURRENCY.GET_FORMAT_MASK(AERH.DEFAULT_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, TO_DATE('')
, ''
, ALC1.DISPLAYED_FIELD INVOICE_TYPE
, ALC2.DISPLAYED_FIELD PAYMENT_STATUS
, ''
, ''
, ''
, -1
, -1
, P.PERSON_ID
, AERH.REPORT_HEADER_ID
, 'EXPENSE REPORT'
, DECODE(AERH.WORKFLOW_APPROVED_FLAG
, 'Y'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, 'A'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, 'M'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('N'))
, DECODE(AERH.WORKFLOW_APPROVED_FLAG
, 'Y'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, 'A'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, 'P'
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('Y')
, AP_WEB_FND_LOOKUPS_PKG.GETYESNOMEANING('N'))
, 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 PER_ALL_PEOPLE_F P
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_LOOKUP_CODES ALC1
, AP_LOOKUP_CODES ALC2
WHERE AERH.VOUCHNO + 0 =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 P.PERSON_ID = PAID_ON_BEHALF_EMPLOYEE_ID
AND (TRUNC(SYSDATE) BETWEEN P.EFFECTIVE_START_DATE
AND P.EFFECTIVE_END_DATE)
AND AERH.EMPLOYEE_ID IS NULL