View: AP_EXPENSE_REPORT_HISTORY_V

Product:	AP - Payables
Description:	Expense Report History in Oracle Internet Expenses is based on this view
Implementation/DBA Data:	APPS.AP_EXPENSE_REPORT_HISTORY_V

View Text

SELECT NVL(AI.AMT_DUE_CCARD_COMPANY
, AERH.AMT_DUE_CCARD_COMPANY) AMT_DUE_CCARD_COMPANY
, NVL(AI.AMT_DUE_EMPLOYEE
, AERH.AMT_DUE_EMPLOYEE) AMT_DUE_EMPLOYEE
, AI.DESCRIPTION DESCRIPTION
, TO_CHAR((NVL(AI.AMT_DUE_CCARD_COMPANY
, AERH.AMT_DUE_CCARD_COMPANY) + NVL(AI.AMT_DUE_EMPLOYEE
, AERH.AMT_DUE_EMPLOYEE))
, FND_CURRENCY_CACHE.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
, DECODE(ALC.LOOKUP_CODE
, 'CANCELLED'
, AERH.TOTAL
, APS.GROSS_AMOUNT) GROSS_AMOUNT_QUERY
, TO_CHAR(DECODE(ALC.LOOKUP_CODE
, 'CANCELLED'
, AERH.TOTAL
, APS.GROSS_AMOUNT)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(APS.AMOUNT_REMAINING
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, P.PERSON_ID EMPLOYEE_ID
, AI.INVOICE_ID REPORT_PRIMARY_KEY
, AERH.REPORT_HEADER_ID REPORT_HEADER_ID
, 'INVOICE' DOCUMENT_TYPE
, P.FULL_NAME FULL_NAME
, ALC.DISPLAYED_FIELD STATUS
, AERH.SOURCE SOURCE
, DECODE(ALC.LOOKUP_CODE
, 'PAID'
, 'FNDVIEWPAYABLESENABLED'
, 'PARPAID'
, 'FNDVIEWPAYABLESENABLED'
, 'INVOICED'
, 'FNDVIEWPAYABLESENABLED'
, 'FNDVIEWPAYABLESDISABLED' ) VIEW_PAYABLES_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'SELFSERVICE'
, 'REPORTNUMBERENABLED'
, 'CREDIT CARD'
, 'REPORTNUMBERENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'REPORTNUMBERDISABLED') REPORT_NUMBER_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'SELFSERVICE'
, 'EXPENSELINESIMAGEENABLED'
, 'CREDIT CARD'
, 'EXPENSELINESIMAGEENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'EXPENSELINESIMAGEOLDURL') EXPENSE_LINES_FIELD
, DECODE (AERH.SOURCE
, 'XPENSEXPRESS'
, 'DUPLICATEIMAGEDISABLED'
, 'CREDIT CARD'
, 'DUPLICATEIMAGEDISABLED'
, NULL
, 'DUPLICATEIMAGEDISABLED'
, 'DUPLICATEIMAGEENABLED') DUPLICATE_FIELD
FROM PO_VENDORS PV
, AP_INVOICES AI
, AP_EXPENSE_REPORT_HEADERS AERH
, PER_PEOPLE_X P
, AP_PAYMENT_SCHEDULES APS
, AP_LOOKUP_CODES ALC
WHERE AI.INVOICE_ID= APS.INVOICE_ID
AND AI.INVOICE_ID = AERH.VOUCHNO(+)
AND ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND ALC.LOOKUP_CODE = DECODE(AI.CANCELLED_DATE
, NULL
, DECODE(APS.GROSS_AMOUNT
, 0
, 'PAID'
, DECODE(AI.PAYMENT_STATUS_FLAG
, 'Y'
, 'PAID'
, 'N'
, 'INVOICED'
, 'P'
, 'PARPAID'
, NULL))
, 'CANCELLED')
AND AI.INVOICE_TYPE_LOOKUP_CODE||'' = 'EXPENSE REPORT'
AND P.PERSON_ID = PV.EMPLOYEE_ID
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.AMT_DUE_CCARD_COMPANY + AI.AMT_DUE_EMPLOYEE)
, FND_CURRENCY_CACHE.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
, DECODE(ALC.LOOKUP_CODE
, 'CANCELLED'
, AERH.TOTAL
, APS.GROSS_AMOUNT) GROSS_AMOUNT_QUERY
, TO_CHAR(DECODE(ALC.LOOKUP_CODE
, 'CANCELLED'
, AERH.TOTAL
, APS.GROSS_AMOUNT)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(APS.AMOUNT_REMAINING
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, P.PERSON_ID EMPLOYEE_ID
, AI.INVOICE_ID REPORT_PRIMARY_KEY
, AERH.REPORT_HEADER_ID REPORT_HEADER_ID
, 'INVOICE' DOCUMENT_TYPE
, P.FULL_NAME FULL_NAME
, ALC.DISPLAYED_FIELD STATUS
, AERH.SOURCE SOURCE
, DECODE(ALC.LOOKUP_CODE
, 'PAID'
, 'FNDVIEWPAYABLESENABLED'
, 'PARPAID'
, 'FNDVIEWPAYABLESENABLED'
, 'INVOICED'
, 'FNDVIEWPAYABLESENABLED'
, 'FNDVIEWPAYABLESDISABLED' ) VIEW_PAYABLES_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'SELFSERVICE'
, 'REPORTNUMBERENABLED'
, 'CREDIT CARD'
, 'REPORTNUMBERENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'REPORTNUMBERDISABLED') REPORT_NUMBER_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'SELFSERVICE'
, 'EXPENSELINESIMAGEENABLED'
, 'CREDIT CARD'
, 'EXPENSELINESIMAGEENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'EXPENSELINESIMAGEOLDURL') EXPENSE_LINES_FIELD
, DECODE (AERH.SOURCE
, 'XPENSEXPRESS'
, 'DUPLICATEIMAGEDISABLED'
, 'CREDIT CARD'
, 'DUPLICATEIMAGEDISABLED'
, NULL
, 'DUPLICATEIMAGEDISABLED'
, 'DUPLICATEIMAGEENABLED') DUPLICATE_FIELD
FROM PO_VENDORS PV
, AP_INVOICES AI
, AP_EXPENSE_REPORT_HEADERS AERH
, PER_PEOPLE_X P
, AP_PAYMENT_SCHEDULES APS
, AP_LOOKUP_CODES ALC
WHERE AI.INVOICE_ID= APS.INVOICE_ID
AND AI.INVOICE_ID= AERH.VOUCHNO(+)
AND ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND ALC.LOOKUP_CODE = DECODE(AI.CANCELLED_DATE
, NULL
, DECODE(APS.GROSS_AMOUNT
, 0
, 'PAID'
, DECODE(AI.PAYMENT_STATUS_FLAG
, 'Y'
, 'PAID'
, 'N'
, 'INVOICED'
, 'P'
, 'PARPAID'
, NULL))
, 'CANCELLED')
AND AI.INVOICE_TYPE_LOOKUP_CODE||'' IN ('STANDARD'
, 'MIXED')
AND P.PERSON_ID = AI.PAID_ON_BEHALF_EMPLOYEE_ID
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(NVL(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE
, AERH.TOTAL)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE
, 30)) TOTAL_AMOUNT
, AERH.DEFAULT_CURRENCY_CODE
, ''
, AERH.WEEK_END_DATE
, -1
, AERH.INVOICE_NUM
, TO_DATE(''
, 'DD-MM-RRRR')
, AERH.TOTAL GROSS_AMOUNT_QUERY
, TO_CHAR((AERH.TOTAL)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(AERH.TOTAL
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, P.PERSON_ID EMPLOYEE_ID
, AERH.REPORT_HEADER_ID REPORT_PRIMARY_KEY
, AERH.REPORT_HEADER_ID REPORT_HEADER_ID
, 'EXPENSE REPORT'
, P.FULL_NAME FULL_NAME
, ALC.DISPLAYED_FIELD STATUS
, AERH.SOURCE SOURCE
, 'FNDVIEWPAYABLESDISABLED' VIEW_PAYABLES_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'SELFSERVICE'
, 'REPORTNUMBERENABLED'
, 'CREDIT CARD'
, 'REPORTNUMBERENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'REPORTNUMBERDISABLED') REPORT_NUMBER_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'SELFSERVICE'
, 'EXPENSELINESIMAGEENABLED'
, 'CREDIT CARD'
, 'EXPENSELINESIMAGEENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'EXPENSELINESIMAGEOLDURL') EXPENSE_LINES_FIELD
, DECODE (AERH.SOURCE
, 'XPENSEXPRESS'
, 'DUPLICATEIMAGEDISABLED'
, 'CREDIT CARD'
, 'DUPLICATEIMAGEDISABLED'
, DECODE (NVL(AERH.EXPENSE_STATUS_CODE
, AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(AERH.SOURCE
, AERH.WORKFLOW_APPROVED_FLAG
, AERH.REPORT_HEADER_ID))
, 'ERROR'
, 'DUPLICATEIMAGEDISABLED'
, 'DUPLICATEIMAGEENABLED')) DUPLICATE_FIELD
FROM PER_PEOPLE_X P
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_LOOKUP_CODES ALC
WHERE AERH.VOUCHNO =0
AND P.PERSON_ID = AERH.EMPLOYEE_ID
AND (AERH.SOURCE <> 'NONVALIDATEDWEBEXPENSE' OR AERH.WORKFLOW_APPROVED_FLAG IS NULL)
AND ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND ALC.LOOKUP_CODE = NVL(AERH.EXPENSE_STATUS_CODE
, AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(AERH.SOURCE
, AERH.WORKFLOW_APPROVED_FLAG
, AERH.REPORT_HEADER_ID
, 'N')) UNION ALL SELECT AERH.AMT_DUE_CCARD_COMPANY AMOUNT_DUE_CC_COMPANY
, AERH.AMT_DUE_EMPLOYEE AMOUNT_DUE_EMP
, AERH.DESCRIPTION
, TO_CHAR(NVL(AERH.AMT_DUE_CCARD_COMPANY+AERH.AMT_DUE_EMPLOYEE
, AERH.TOTAL)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE
, 30)) TOTAL_AMOUNT
, AERH.DEFAULT_CURRENCY_CODE
, ''
, AERH.WEEK_END_DATE
, -1
, AERH.INVOICE_NUM
, TO_DATE(''
, 'DD-MM-RRRR')
, AERH.TOTAL GROSS_AMOUNT_QUERY
, TO_CHAR((AERH.TOTAL)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(AERH.TOTAL
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AERH.DEFAULT_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, P.PERSON_ID EMPLOYEE_ID
, AERH.REPORT_HEADER_ID REPORT_PRIMARY_KEY
, AERH.REPORT_HEADER_ID REPORT_HEADER_ID
, 'EXPENSE REPORT'
, P.FULL_NAME FULL_NAME
, ALC.DISPLAYED_FIELD STATUS
, AERH.SOURCE SOURCE
, 'FNDVIEWPAYABLESDISABLED' VIEW_PAYABLES_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'SELFSERVICE'
, 'REPORTNUMBERENABLED'
, 'CREDIT CARD'
, 'REPORTNUMBERENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'REPORTNUMBERDISABLED') REPORT_NUMBER_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'SELFSERVICE'
, 'EXPENSELINESIMAGEENABLED'
, 'CREDIT CARD'
, 'EXPENSELINESIMAGEENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'EXPENSELINESIMAGEOLDURL') EXPENSE_LINES_FIELD
, DECODE (AERH.SOURCE
, 'XPENSEXPRESS'
, 'DUPLICATEIMAGEDISABLED'
, 'CREDIT CARD'
, 'DUPLICATEIMAGEDISABLED'
, DECODE (NVL(AERH.EXPENSE_STATUS_CODE
, AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(AERH.SOURCE
, AERH.WORKFLOW_APPROVED_FLAG
, AERH.REPORT_HEADER_ID))
, 'ERROR'
, 'DUPLICATEIMAGEDISABLED'
, 'DUPLICATEIMAGEENABLED')) DUPLICATE_FIELD
FROM PER_PEOPLE_X P
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_LOOKUP_CODES ALC
WHERE AERH.VOUCHNO =0
AND P.PERSON_ID = PAID_ON_BEHALF_EMPLOYEE_ID
AND AERH.EMPLOYEE_ID IS NULL
AND (AERH.SOURCE <> 'NONVALIDATEDWEBEXPENSE' OR AERH.WORKFLOW_APPROVED_FLAG IS NULL)
AND ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND ALC.LOOKUP_CODE = NVL(AERH.EXPENSE_STATUS_CODE
, AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(AERH.SOURCE
, AERH.WORKFLOW_APPROVED_FLAG
, AERH.REPORT_HEADER_ID
, 'N')) 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.AMT_DUE_CCARD_COMPANY + AI.AMT_DUE_EMPLOYEE)
, FND_CURRENCY_CACHE.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
, DECODE(ALC.LOOKUP_CODE
, 'CANCELLED'
, AERH.TOTAL
, APS.GROSS_AMOUNT) GROSS_AMOUNT_QUERY
, TO_CHAR(DECODE(ALC.LOOKUP_CODE
, 'CANCELLED'
, AERH.TOTAL
, APS.GROSS_AMOUNT)
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE
, 30)) GROSS_AMOUNT
, TO_CHAR(APS.AMOUNT_REMAINING
, FND_CURRENCY_CACHE.GET_FORMAT_MASK (AI.INVOICE_CURRENCY_CODE
, 30)) AMOUNT_REMAINING
, P.PERSON_ID EMPLOYEE_ID
, AI.INVOICE_ID REPORT_PRIMARY_KEY
, AERH.REPORT_HEADER_ID REPORT_HEADER_ID
, 'INVOICE' DOCUMENT_TYPE
, P.FULL_NAME FULL_NAME
, ALC.DISPLAYED_FIELD STATUS
, AERH.SOURCE SOURCE
, DECODE(ALC.LOOKUP_CODE
, 'PAID'
, 'FNDVIEWPAYABLESENABLED'
, 'PARPAID'
, 'FNDVIEWPAYABLESENABLED'
, 'INVOICED'
, 'FNDVIEWPAYABLESENABLED'
, 'FNDVIEWPAYABLESDISABLED' ) VIEW_PAYABLES_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'SELFSERVICE'
, 'REPORTNUMBERENABLED'
, 'CREDIT CARD'
, 'REPORTNUMBERENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'REPORTNUMBERENABLED'
, 'REPORTNUMBERDISABLED') REPORT_NUMBER_FIELD
, DECODE(AERH.SOURCE
, 'WEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'SELFSERVICE'
, 'EXPENSELINESIMAGEENABLED'
, 'CREDIT CARD'
, 'EXPENSELINESIMAGEENABLED'
, 'NONVALIDATEDWEBEXPENSE'
, 'EXPENSELINESIMAGEENABLED'
, 'EXPENSELINESIMAGEOLDURL') EXPENSE_LINES_FIELD
, DECODE (AERH.SOURCE
, 'XPENSEXPRESS'
, 'DUPLICATEIMAGEDISABLED'
, 'CREDIT CARD'
, 'DUPLICATEIMAGEDISABLED'
, NULL
, 'DUPLICATEIMAGEDISABLED'
, 'DUPLICATEIMAGEENABLED') DUPLICATE_FIELD
FROM AP_INVOICES AI
, AP_EXPENSE_REPORT_HEADERS AERH
, PER_PEOPLE_X P
, AP_PAYMENT_SCHEDULES APS
, AP_LOOKUP_CODES ALC
WHERE AI.INVOICE_ID= APS.INVOICE_ID
AND AI.INVOICE_ID = AERH.VOUCHNO(+)
AND ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
AND ALC.LOOKUP_CODE = DECODE(AI.CANCELLED_DATE
, NULL
, DECODE(APS.GROSS_AMOUNT
, 0
, 'PAID'
, DECODE(AI.PAYMENT_STATUS_FLAG
, 'Y'
, 'PAID'
, 'N'
, 'INVOICED'
, 'P'
, 'PARPAID'
, NULL))
, 'CANCELLED')
AND AI.INVOICE_TYPE_LOOKUP_CODE||'' = 'EXPENSE REPORT'
AND P.PERSON_ID = AI.PAID_ON_BEHALF_EMPLOYEE_ID
AND AP_WEB_DB_HR_INT_PKG.ISPERSONCWK(AI.PAID_ON_BEHALF_EMPLOYEE_ID)='Y'

Columns