Introduction:
This will displays the Audit time taken for expenses
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’;
RETURN LS_AUDIT_TAKEN;
EXCEPTION WHEN OTHERS THEN
RETURN LS_AUDIT_TAKEN ;
END TIME_TAKEN;
Summay:
This function will useful to find audit time taken for the expenses
Know more about post.
Doyen.ebiz@gmail.com
