View: AP_EXPENSE_REPORT_VIOLATIONS_V

Product:	AP - Payables
Description:	View for all expense report violations and corresponding expense report lines that are ready for payment or have already been paid.
Implementation/DBA Data:	APPS.AP_EXPENSE_REPORT_VIOLATIONS_V

View Text

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
, 'CC_REQUIRED'
, 0
, NVL(APV.ALLOWABLE_AMOUNT
, 0)) AS ALLOWABLE_AMOUNT
, DECODE(APV.VIOLATION_TYPE
, NULL
, 0
, 'CC_REQUIRED'
, 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 ))

Columns