Introduction
This Post illustrate steps required to AP Web Report Workflow Status in Oracle EBS R12.
Script to AP Web Report Workflow Status
SELECT ‘WPAY’ TYPE, gl.segment1 division, gl.segment3 dcc, gl.segment4 ccc,
h.report_submitted_date creation_date,
(TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
NULL timeout_date, NULL manager_level,
TO_CHAR (h.report_header_id) report_header_id,
h.invoice_num invoice_num, h.description description,
p.full_name requestor, h.flex_concatenated district, h.total total,
‘Payables’ current_approver
FROM ap_expense_report_headers_all h,
per_people_x p,
gl_code_combinations gl
WHERE p.person_id = h.employee_id
AND gl.code_combination_id = h.employee_ccid
AND h.workflow_approved_flag = ‘M’
UNION ALL
SELECT ‘WSYS’ TYPE, gl.segment1 division, gl.segment3 dcc, gl.segment4 ccc,
h.report_submitted_date creation_date,
(TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
NULL timeout_date, NULL manager_level, wfi.item_key report_header_id,
h.invoice_num invoice_num, h.description description,
p.full_name requestor, h.flex_concatenated district, h.total total,
DECODE (SUBSTR (wfi.assigned_user, 1, 3),
‘FND’, ‘SYSADMIN:TO’,
wfi.assigned_user
) current_approver
FROM wf_item_activity_statuses wfi,
ap_expense_report_headers_all h,
per_people_x p,
gl_code_combinations gl
WHERE wfi.item_key = TO_CHAR (h.report_header_id)
AND h.employee_ccid = gl.code_combination_id(+)
AND p.person_id = h.employee_id
AND wfi.item_type = ‘APEXP’
AND wfi.activity_status = ‘NOTIFIED’
AND wfi.end_date IS NULL
AND (wfi.assigned_user = ‘SYSADMIN’ OR wfi.assigned_user LIKE ‘FND_RESP%’
)
UNION ALL
SELECT ‘WTRM’ TYPE, gl.segment1 division, gl.segment3 dcc, gl.segment4 ccc,
h.report_submitted_date creation_date,
(TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
NULL timeout_date, NULL manager_level, wfi.item_key report_header_id,
h.invoice_num invoice_num, h.description description,
p.full_name requestor, h.flex_concatenated district, h.total total,
wfi.assigned_user current_approver
FROM wf_item_activity_statuses wfi,
ap_expense_report_headers_all h,
per_people_x p,
gl_code_combinations gl,
per_assignments_x a
WHERE wfi.item_key = TO_CHAR (h.report_header_id)
AND gl.code_combination_id = h.employee_ccid
AND p.person_id = h.employee_id
AND p.person_id = a.person_id
AND a.primary_flag = ‘Y’
AND wfi.item_type = ‘APEXP’
AND wfi.activity_status = ‘NOTIFIED’
AND wfi.end_date IS NULL
AND wfi.assigned_user IS NOT NULL
AND h.workflow_approved_flag IS NULL
AND hr_person_type_usage_info.get_user_person_type (SYSDATE,
a.supervisor_id
) LIKE ‘Ex-employee%’
UNION ALL
SELECT ‘SPAY’ TYPE, gl.segment1 division, gl.segment3 dcc, gl.segment4 ccc,
h.report_submitted_date creation_date,
(TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
NULL timeout_date, NULL manager_level,
TO_CHAR (h.report_header_id) report_header_id,
h.invoice_num invoice_num, h.description description,
p.full_name requestor, h.flex_concatenated district, h.total total,
DECODE (h.workflow_approved_flag,
‘Y’, ‘Complete’,
‘A’, ‘Complete’,
‘M’, ‘Mgr Approved’,
‘S’, ‘Saved’,
‘R’, ‘Rejected’,
‘P’, ‘AP Approved’,
NULL, ‘Waiting’,
NULL
) current_approver
FROM ap_expense_report_headers_all h,
per_people_x p,
gl_code_combinations gl
WHERE p.person_id = h.employee_id
AND gl.code_combination_id = h.employee_ccid
AND h.invoice_num LIKE ‘WEB%-1’
UNION ALL
SELECT ‘WMGR’ TYPE, gl.segment1 division, gl.segment3 dcc, gl.segment4 ccc,
h.report_submitted_date creation_date,
(TRUNC (SYSDATE) – TRUNC (h.report_submitted_date)) days_old,
notif.timeout_date, notif.manager_level, wfi.item_key report_header_id,
h.invoice_num invoice_num, h.description description,
p.full_name requestor, h.flex_concatenated district, h.total total,
DECODE (SUBSTR (wfi.assigned_user, 1, 3),
‘FND’, ‘SYSADMIN:TO’,
wfi.assigned_user
) current_approver
FROM wf_item_activity_statuses wfi,
ap_expense_report_headers_all h,
fnd_user u,
per_people_x p,
gl_code_combinations gl,
(SELECT SUBSTR (CONTEXT, 7, 6) CONTEXT, TRUNC (due_date) timeout_date,
message_name,
DECODE (message_name,
‘OIE_REQUEST_EMPLOYEE_APPROVAL’, ‘0’,
‘TIMED-OUT’
) manager_level
FROM wf_notifications
WHERE MESSAGE_TYPE = ‘APEXP’ AND status = ‘OPEN’) notif
WHERE wfi.item_key = TO_CHAR (h.report_header_id)
AND notif.CONTEXT = h.report_header_id
AND gl.code_combination_id = h.employee_ccid
AND wfi.assigned_user = u.user_name(+)
AND p.person_id = h.employee_id
AND wfi.item_type = ‘APEXP’
AND wfi.activity_status = ‘NOTIFIED’
AND wfi.end_date IS NULL
AND (h.workflow_approved_flag IS NULL OR h.workflow_approved_flag = ‘P’)
AND wfi.assigned_user != ‘SYSADMIN’
AND SUBSTR (wfi.assigned_user, 1, 3) != ‘FND’
What we expect in the script.
This script helps us to comprehend how AP Web Report Workflow Status. Couple of tables which is being used in the scripts are wf_item_activity_statuses wfi,ap_expense_report_headers_all ,per_people_x p,gl_code_combinations gl,per_assignments_x etc.
Summary
This Post described the script how AP Web Report Workflow Status in Oracle EBS R12.
Got any queries?
Do drop a note by writing us at doyen.ebiz@gmail.com or use the comment section below to ask your questions.
