Introduction
This report is about to find the expense details and time taken to complete the audit.
SELECT A.Report_Number,
A.Employee_Name,
A.Emp_Number,
A.Operating_Unit,
A.Amount,
A.Currency,
A.Last_Audited_By,
A.Report_submit_date,
TO_CHAR(A.Claim_Approved_Date,’DD-MON-YYYY HH24:MI:SS’) Claim_Approved_Date,
TO_CHAR(A.Pending_Your_Resolution_Date,’DD-MON-YYYY HH24:MI:SS’) Pending_Your_Resolution_Date,
TO_CHAR(A.Preparer_Response_Date,’DD-MON-YYYY HH24:MI:SS’) Preparer_Response_Date,
TO_CHAR(A.Audit_Complete_Date,’DD-MON-YYYY HH24:MI:SS’)Audit_Complete_Date,
ROUND((TIME_TAKEN(a.Report_header_id,a.Audit_Complete_Date,A.Claim_Approved_Date)*1440/60),2)Pending_resolution_time,
ROUND(((A.Audit_Complete_Date-A.Claim_Approved_Date)-NVL(TIME_TAKEN(a.Report_header_id,a.Audit_Complete_Date,A.Claim_Approved_Date),0))*1440/60,2)AUDIT_TIME_TAKEN_IN_HOURS
from
(SELECT aerh.invoice_num Report_Number,
papf.full_name Employee_Name,
aerh.Report_header_id Report_header_id,
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,
NVL (TO_CHAR (aerh.report_submitted_date, ‘DD-MON-YYYY HH24:MI:SS’), aerh.creation_date ) Report_submit_date,
(SELECT MAX(NVL(an.last_update_date, an.entered_date)) 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’ ) Claim_Approved_Date,
–(CASE WHEN aerh.expense_status_code = ‘RESOLUTN’ THEN TO_CHAR (aerh.last_update_date, ‘DD-MON-YYYY HH24:MI:SS’) ELSE NULL END ) –“Pending_Your_Resolution_Date”,
(SELECT MAX(NVL (an.last_update_date, an.entered_date)) 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, 13)) = ‘Return Reason’ ) Pending_Your_Resolution_Date,
(SELECT MAX(NVL (an.last_update_date, an.entered_date)) 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, 17)) = ‘Preparer Response’ ) Preparer_Response_Date,
(SELECT MAX(NVL (an.last_update_date, an.entered_date)) 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.’) 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 aerh.org_id=82
AND aerh.report_submitted_date BETWEEN ’01-JAN-2020′ AND ’31-JAN-2020′
–AND aerh.invoice_num =’Web3067576′
AND TRUNC (SYSDATE) BETWEEN TRUNC (PAPF.EFFECTIVE_START_DATE) AND TRUNC (PAPF.EFFECTIVE_END_DATE) )A
WHERE to_date(A.Audit_Complete_Date,’DD-MON-YYYY HH24:MI:SS’)>to_date(A.Claim_Approved_Date,’DD-MON-YYYY HH24:MI:SS’ );
/
Function :
CREATE OR REPLACE FUNCTION TIME_TAKEN(P_REPORT_HEADER_ID IN NUMBER,P_AUDIT_COMPLETE_DATE IN VARCHAR2,P_CLAIM_APPROVED_DATE IN VARCHAR2) RETURN VARCHAR2
IS
LS_AUDIT_TAKEN VARCHAR2(100):=0;
BEGIN
SELECT SUM((TO_DATE(B.CASE_TIME,’DD-MON-YYYY HH24:MI:SS’)-TO_DATE(B.End_Dated_time,’DD-MON-YYYY HH24:MI:SS’))) INTO LS_AUDIT_TAKEN FROM (select a.*,to_char(a.entered_date,’DD-MON-YYYY HH24:MI:SS’)End_Dated_time ,
CASE WHEN TO_CHAR (SUBSTR (A.notes_detail, 1, 13)) = ‘Return Reason’ THEN
LEAD(to_char(a.entered_date,’DD-MON-YYYY HH24:MI:SS’), 1, 0) OVER (ORDER BY a.note_id)
END CASE_TIME
from ap_notes a
where source_object_id=P_REPORT_HEADER_ID
AND a.entered_date BETWEEN P_CLAIM_APPROVED_DATE AND P_AUDIT_COMPLETE_DATE
AND (TO_CHAR (SUBSTR (A.notes_detail, 1, 13)) = ‘Return Reason’ OR TO_CHAR (SUBSTR (a.notes_detail, 1, 17)) = ‘Preparer Response’)
ORDER BY to_char(a.entered_date,’DD-MON-YYYY HH24:MI:SS’))B
WHERE TO_CHAR (SUBSTR (B.notes_detail, 1, 13)) = ‘Return Reason’;
–AND TO_CHAR(SUBSTR(B.notes_detail,1,24)) NOT IN(‘Expense report withdrawn’);
RETURN LS_AUDIT_TAKEN;
EXCEPTION WHEN OTHERS THEN
RETURN LS_AUDIT_TAKEN ;
END TIME_TAKEN;
Summary
This report is about to find the expense details and time taken to complete the audit.
