Script to extract Expenses SLA Details
Introduction
This Post illustrate steps required to extract Expenses SLA Details in Oracle EBS R12.
Query to extract Expenses SLA Details
SELECT aerh.invoice_num “Report_Number”, papf.full_name “Employee_Name”,
papf.employee_number “Emp_Number”, hou.NAME “Operating_Unit”,
aerh.total “Amount”, aerh.default_currency_code “Currency”,
(SELECT meaning
FROM apps.fnd_lookup_values
WHERE lookup_type = ‘EXPENSE REPORT STATUS’
AND LANGUAGE = ‘US’
AND lookup_code = aerh.expense_status_code) “Report_status”,
(SELECT user_name
FROM apps.fnd_user
WHERE user_id = aerh.last_audited_by) “Last_Audited_By”,
DECODE (aerh.audit_code,
‘PAPERLESS_AUDIT’, DECODE (aerh.workflow_approved_flag,
‘N’, ‘Y’,
‘M’, ‘Y’,
NULL, ‘Y’,
‘N’
),
‘RECEIPT_BASED’, DECODE (aerh.workflow_approved_flag,
‘N’, ‘Y’,
‘M’, ‘Y’,
NULL, ‘Y’,
‘N’
),
‘N’
) “Requires_Audit”,
NVL (TO_CHAR (aerh.report_submitted_date, ‘DD-MON-YYYY’),
TO_CHAR (aerh.creation_date, ‘DD-MON-YYYY’)
) “Report_submit_date”,
(SELECT TO_CHAR (NVL (an.last_update_date, an.entered_date),
‘DD-MON-YYYY’
)
FROM apps.ap_notes an
WHERE an.source_object_code = ‘OIE_EXPENSE_REPORT’
AND an.source_object_id = aerh.report_header_id
AND TO_CHAR (SUBSTR (an.notes_detail, 1, 24)) =
‘Approver Action: Approve’
AND ROWNUM = 1) “Claim_Approved_Date”,
(CASE
WHEN aerh.expense_status_code = ‘RESOLUTN’
THEN TO_CHAR (aerh.last_update_date, ‘DD-MON-YYYY’)
ELSE NULL
END
) “Pending_Your_Resolution_Date”,
(SELECT TO_CHAR (NVL (an.last_update_date, an.entered_date),
‘DD-MON-YYYY’
)
FROM apps.ap_notes an
WHERE an.source_object_code = ‘OIE_EXPENSE_REPORT’
AND an.source_object_id = aerh.report_header_id
AND TO_CHAR (SUBSTR (an.notes_detail, 1, 15)) = ‘Complete Audit.’
AND ROWNUM = 1) “Audit_Complete_Date”
FROM apps.ap_expense_report_headers_all aerh,
apps.per_all_people_f papf,
apps.hr_operating_units hou
WHERE papf.person_id = aerh.employee_id
AND hou.organization_id = aerh.org_id
AND papf.current_employee_flag = ‘Y’
AND TRUNC (SYSDATE) BETWEEN TRUNC (papf.effective_start_date)
AND TRUNC (papf.effective_end_date)
AND aerh.org_id = fnd_profile.VALUE (‘ORG_ID’)
Summary
This Post described the script extract Expenses SLA Details 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.
