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

Source

View Text - Preformatted


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'

View Text - HTML Formatted

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'