View: OIE_MGR_RPT_V

Product:	AP - Payables
Description:	Reporting View for Expenses Management Reporting
Implementation/DBA Data:	APPS.OIE_MGR_RPT_V

View Text

SELECT AERH.EMPLOYEE_ID
, PECX.FULL_NAME
, AERH.WEEK_END_DATE AS EXPENSE_REPORT_DATE
, AERH.REPORT_HEADER_ID
, AERL.DISTRIBUTION_LINE_NUMBER
, AERP.CATEGORY_CODE
, OPV.VIOLATION_TYPE
, SUM(NVL(AERL.FUNC_CURRENCY_AMT
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( AERL.CURRENCY_CODE
, ASP.BASE_CURRENCY_CODE
, SYSDATE
, ASP.DEFAULT_EXCHANGE_RATE_TYPE
, NVL(AERL.AMOUNT
, 0) ))) AS LINE_AMOUNT
, SUM(DECODE(OPV.VIOLATION_TYPE
, 'DAILY_SUM_LIMIT'
, 0
, 'CC_REQUIRED'
, 0
, NVL(FUNC_CURRENCY_ALLOWABLE_AMT
, 0))) AS ALLOWABLE_AMOUNT
, SUM(DECODE(OPV.VIOLATION_TYPE
, NULL
, 0
, 'CC_REQUIRED'
, 0
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( AERL.CURRENCY_CODE
, ASP.BASE_CURRENCY_CODE
, SYSDATE
, ASP.DEFAULT_EXCHANGE_RATE_TYPE
, NVL(OPV.EXCEEDED_AMOUNT
, 0) ))) AS VIOLATION_AMOUNT
, COUNT(OPV.VIOLATION_TYPE) AS NUMBER_OF_VIOLATIONS
, PERF.SUPERVISOR_ID
FROM PER_PEOPLE_X PECX
, PER_ASSIGNMENTS_F PERF
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_EXPENSE_REPORT_LINES AERL
, AP_POL_VIOLATIONS OPV
, AP_EXPENSE_REPORT_PARAMS AERP
, AP_SYSTEM_PARAMETERS ASP
WHERE PECX.PERSON_ID = AERH.EMPLOYEE_ID
AND PERF.PERSON_ID = PECX.PERSON_ID
AND PERF.PRIMARY_FLAG='Y'
AND PERF.ASSIGNMENT_TYPE IN ('C'
, 'E')
AND AERL.REPORT_HEADER_ID = AERH.REPORT_HEADER_ID
AND AERL.REPORT_HEADER_ID = OPV.REPORT_HEADER_ID(+)
AND AERL.DISTRIBUTION_LINE_NUMBER = OPV.DISTRIBUTION_LINE_NUMBER(+)
AND AERL.WEB_PARAMETER_ID = AERP.PARAMETER_ID
AND (AERH.SOURCE <> 'NONVALIDATEDWEBEXPENSE' OR AERH.WORKFLOW_APPROVED_FLAG IS NULL)
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 AERH.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND AERL.WEB_PARAMETER_ID <> (SELECT AP.PARAMETER_ID
FROM AP_EXPENSE_REPORT_PARAMS AP
, AP_EXPENSE_REPORTS AR
WHERE AR.REPORT_TYPE = 'SEEDED PERSONAL EXPENSE'
AND EXPENSE_TYPE_CODE = 'PERSONAL') GROUP BY AERH.EMPLOYEE_ID
, PECX.FULL_NAME
, AERH.WEEK_END_DATE
, AERH.REPORT_HEADER_ID
, AERL.DISTRIBUTION_LINE_NUMBER
, AERL.EXPENDITURE_TYPE
, OPV.VIOLATION_TYPE
, AERP.CATEGORY_CODE
, PERF.SUPERVISOR_ID UNION ALL SELECT AERH.PAID_ON_BEHALF_EMPLOYEE_ID AS EMPLOYEE_ID
, PECX.FULL_NAME
, AERH.WEEK_END_DATE AS EXPENSE_REPORT_DATE
, AERH.REPORT_HEADER_ID
, AERL.DISTRIBUTION_LINE_NUMBER
, AERP.CATEGORY_CODE
, OPV.VIOLATION_TYPE
, SUM(NVL(AERL.FUNC_CURRENCY_AMT
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( AERL.CURRENCY_CODE
, ASP.BASE_CURRENCY_CODE
, SYSDATE
, ASP.DEFAULT_EXCHANGE_RATE_TYPE
, NVL(AERL.AMOUNT
, 0) ))) AS LINE_AMOUNT
, SUM(DECODE(OPV.VIOLATION_TYPE
, 'DAILY_SUM_LIMIT'
, 0
, 'CC_REQUIRED'
, 0
, NVL(FUNC_CURRENCY_ALLOWABLE_AMT
, 0))) AS ALLOWABLE_AMOUNT
, SUM(DECODE(OPV.VIOLATION_TYPE
, NULL
, 0
, 'CC_REQUIRED'
, 0
, GL_CURRENCY_API.CONVERT_AMOUNT_SQL( AERL.CURRENCY_CODE
, ASP.BASE_CURRENCY_CODE
, SYSDATE
, ASP.DEFAULT_EXCHANGE_RATE_TYPE
, NVL(OPV.EXCEEDED_AMOUNT
, 0) ))) AS VIOLATION_AMOUNT
, COUNT(OPV.VIOLATION_TYPE) AS NUMBER_OF_VIOLATIONS
, PERF.SUPERVISOR_ID
FROM PER_PEOPLE_X PECX
, PER_ASSIGNMENTS_F PERF
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_EXPENSE_REPORT_LINES AERL
, AP_POL_VIOLATIONS OPV
, AP_EXPENSE_REPORT_PARAMS AERP
, AP_SYSTEM_PARAMETERS ASP
WHERE PECX.PERSON_ID = AERH.PAID_ON_BEHALF_EMPLOYEE_ID
AND PERF.PERSON_ID = PECX.PERSON_ID
AND PERF.PRIMARY_FLAG='Y'
AND PERF.ASSIGNMENT_TYPE IN ('C'
, 'E')
AND AERL.REPORT_HEADER_ID = AERH.REPORT_HEADER_ID
AND AERL.REPORT_HEADER_ID = OPV.REPORT_HEADER_ID(+)
AND AERL.DISTRIBUTION_LINE_NUMBER = OPV.DISTRIBUTION_LINE_NUMBER(+)
AND AERL.WEB_PARAMETER_ID = AERP.PARAMETER_ID
AND (AERH.SOURCE <> 'NONVALIDATEDWEBEXPENSE' OR AERH.WORKFLOW_APPROVED_FLAG IS NULL)
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 AERL.WEB_PARAMETER_ID <> (SELECT AP.PARAMETER_ID
FROM AP_EXPENSE_REPORT_PARAMS AP
, AP_EXPENSE_REPORTS AR
WHERE AR.REPORT_TYPE = 'SEEDED PERSONAL EXPENSE'
AND EXPENSE_TYPE_CODE = 'PERSONAL') GROUP BY AERH.PAID_ON_BEHALF_EMPLOYEE_ID
, PECX.FULL_NAME
, AERH.WEEK_END_DATE
, AERH.REPORT_HEADER_ID
, AERL.DISTRIBUTION_LINE_NUMBER
, AERL.EXPENDITURE_TYPE
, OPV.VIOLATION_TYPE
, AERP.CATEGORY_CODE
, PERF.SUPERVISOR_ID UNION ALL SELECT AERH.EMPLOYEE_ID
, PECX.FULL_NAME
, AERH.WEEK_END_DATE AS EXPENSE_REPORT_DATE
, AERH.REPORT_HEADER_ID
, TO_NUMBER(NULL)
, 'MEALS'
, OPV.VIOLATION_TYPE
, 0 AS LINE_AMOUNT
, SUM(NVL(FUNC_CURRENCY_ALLOWABLE_AMT
, 0)) AS ALLOWABLE_AMOUNT
, SUM(GL_CURRENCY_API.CONVERT_AMOUNT_SQL( AERH.DEFAULT_CURRENCY_CODE
, ASP.BASE_CURRENCY_CODE
, SYSDATE
, ASP.DEFAULT_EXCHANGE_RATE_TYPE
, NVL(OPV.EXCEEDED_AMOUNT
, 0) )) AS VIOLATION_AMOUNT
, 0 AS NUMBER_OF_VIOLATIONS
, PERF.SUPERVISOR_ID
FROM PER_PEOPLE_X PECX
, PER_ASSIGNMENTS_F PERF
, AP_EXPENSE_REPORT_HEADERS AERH
, AP_POL_VIOLATIONS OPV
, AP_SYSTEM_PARAMETERS ASP
WHERE PECX.PERSON_ID = AERH.EMPLOYEE_ID
AND PERF.PERSON_ID = PECX.PERSON_ID
AND PERF.PRIMARY_FLAG='Y'
AND PERF.ASSIGNMENT_TYPE IN ('C'
, 'E')
AND AERH.REPORT_HEADER_ID =OPV.REPORT_HEADER_ID
AND (AERH.SOURCE <> 'NONVALIDATEDWEBEXPENSE' OR AERH.WORKFLOW_APPROVED_FLAG IS NULL)
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 AERH.PAID_ON_BEHALF_EMPLOYEE_ID IS NULL
AND OPV.DISTRIBUTION_LINE_NUMBER = -1 GROUP BY AERH.EMPLOYEE_ID
, PECX.FULL_NAME
, AERH.WEEK_END_DATE
, AERH.REPORT_HEADER_ID
, OPV.VIOLATION_TYPE
, PERF.SUPERVISOR_ID

Columns