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

Source

View Text - Preformatted


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

View Text - HTML Formatted

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