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_VIOLATIONS_V

Source

View Text - Preformatted


SELECT aerh.employee_id, aerh.paid_on_behalf_employee_id, aerh.week_end_date, aerh.invoice_num, aerh.report_header_id, aerl.amount, aerl.currency_code, aerl.distribution_line_number, aerl.receipt_currency_code, aerl.receipt_currency_amount, aerl.daily_amount, aerl.web_parameter_id, aerl.category_code, aerl.submitted_amount, apv.violation_number, apv.violation_type, apv.violation_date, DECODE(apv.violation_type, 'DAILY_SUM_LIMIT', 0, NVL(apv.allowable_amount, 0)) as allowable_amount, DECODE(apv.violation_type, NULL, 0, NVL(apv.exceeded_amount, 0)) as violation_amount, aerl.itemization_parent_id , aerl.report_line_id FROM ap_expense_report_headers_all aerh , ap_expense_report_lines_all aerl , ap_pol_violations_all apv WHERE aerh.report_header_id = aerl.report_header_id AND aerh.org_id = aerl.org_id AND apv.report_header_id = aerl.report_header_id AND apv.distribution_line_number = aerl.distribution_line_number AND apv.org_id = aerl.org_id AND (aerh.source <> 'NonValidatedWebExpense' OR aerh.workflow_approved_flag IS NULL) AND aerh.source <> 'Both Pay' AND NVL(AERH.expense_status_code, AP_WEB_OA_ACTIVE_PKG.GetReportStatusCode(AERH.Source, AERH.Workflow_approved_flag, AERH.report_header_id, 'N')) IN ('MGRPAYAPPR','INVOICED','PAID') AND NOT EXISTS (SELECT aerp.parameter_id FROM ap_expense_report_params_all aerp, ap_expense_reports_all aer WHERE aer.report_type = 'Seeded Personal Expense' AND aerp.expense_type_code = 'PERSONAL' AND aer.expense_report_id = aerp.expense_report_id AND aerl.web_parameter_id = aerp.parameter_id) AND ( (itemization_parent_id IS null) or ( itemization_parent_id <> -1 ))

View Text - HTML Formatted

SELECT AERH.EMPLOYEE_ID
, AERH.PAID_ON_BEHALF_EMPLOYEE_ID
, AERH.WEEK_END_DATE
, AERH.INVOICE_NUM
, AERH.REPORT_HEADER_ID
, AERL.AMOUNT
, AERL.CURRENCY_CODE
, AERL.DISTRIBUTION_LINE_NUMBER
, AERL.RECEIPT_CURRENCY_CODE
, AERL.RECEIPT_CURRENCY_AMOUNT
, AERL.DAILY_AMOUNT
, AERL.WEB_PARAMETER_ID
, AERL.CATEGORY_CODE
, AERL.SUBMITTED_AMOUNT
, APV.VIOLATION_NUMBER
, APV.VIOLATION_TYPE
, APV.VIOLATION_DATE
, DECODE(APV.VIOLATION_TYPE
, 'DAILY_SUM_LIMIT'
, 0
, NVL(APV.ALLOWABLE_AMOUNT
, 0)) AS ALLOWABLE_AMOUNT
, DECODE(APV.VIOLATION_TYPE
, NULL
, 0
, NVL(APV.EXCEEDED_AMOUNT
, 0)) AS VIOLATION_AMOUNT
, AERL.ITEMIZATION_PARENT_ID
, AERL.REPORT_LINE_ID
FROM AP_EXPENSE_REPORT_HEADERS_ALL AERH
, AP_EXPENSE_REPORT_LINES_ALL AERL
, AP_POL_VIOLATIONS_ALL APV
WHERE AERH.REPORT_HEADER_ID = AERL.REPORT_HEADER_ID
AND AERH.ORG_ID = AERL.ORG_ID
AND APV.REPORT_HEADER_ID = AERL.REPORT_HEADER_ID
AND APV.DISTRIBUTION_LINE_NUMBER = AERL.DISTRIBUTION_LINE_NUMBER
AND APV.ORG_ID = AERL.ORG_ID
AND (AERH.SOURCE <> 'NONVALIDATEDWEBEXPENSE' OR AERH.WORKFLOW_APPROVED_FLAG IS NULL)
AND AERH.SOURCE <> 'BOTH PAY'
AND NVL(AERH.EXPENSE_STATUS_CODE
, AP_WEB_OA_ACTIVE_PKG.GETREPORTSTATUSCODE(AERH.SOURCE
, AERH.WORKFLOW_APPROVED_FLAG
, AERH.REPORT_HEADER_ID
, 'N')) IN ('MGRPAYAPPR'
, 'INVOICED'
, 'PAID')
AND NOT EXISTS (SELECT AERP.PARAMETER_ID
FROM AP_EXPENSE_REPORT_PARAMS_ALL AERP
, AP_EXPENSE_REPORTS_ALL AER
WHERE AER.REPORT_TYPE = 'SEEDED PERSONAL EXPENSE'
AND AERP.EXPENSE_TYPE_CODE = 'PERSONAL'
AND AER.EXPENSE_REPORT_ID = AERP.EXPENSE_REPORT_ID
AND AERL.WEB_PARAMETER_ID = AERP.PARAMETER_ID)
AND ( (ITEMIZATION_PARENT_ID IS NULL) OR ( ITEMIZATION_PARENT_ID <> -1 ))