AIM: This query is used for Iexpence Report query.
Script:
select
APERH.EMPLOYEE_ID
,APERH.INVOICE_NUM EXPENSE_NUMBER
,DECODE(APERH.WORKFLOW_APPROVED_FLAG,’S’,’SAVED’,’I’,’IMPLICIT SAVE’,’R’,’MANAGER REJECTED’,’M’,’MANAGER APPROVED’,’P’
,’PAYABLES APPROVED’,’A’,’AUTO APPROVED’,’W’,’WITHDRAWN’,’Y’,’APPROVED’,’IN PROGRESS’) INVOICE_STATUS
,aperh.expense_status_code
,APERH.CREATION_DATE
,OVERRIDE_APPROVER_NAME APPROVER_NAME
, CASE
WHEN NVL2(AN.ENTERED_DATE, ‘Y’, ‘N’) = ‘Y’ THEN
AN.ENTERED_DATE
END “Manager Approved on”
,TOTAL
,AERA.REPORT_TYPE
,TO_DATE(AN.ENTERED_DATE) AUDIT_DATE
,(SELECT TO_DATE(AN.ENTERED_DATE)-TO_DATE(AN.ENTERED_DATE)
FROM AP_NOTES AN
WHERE AN.SOURCE_OBJECT_ID = APERH.REPORT_HEADER_ID
AND AN.NOTE_TYPE in(‘Pending your resolution’,’Pending System Administrator Action’)) AUDIT_DAYS
,TO_CHAR(SUBSTR(AN.NOTES_DETAIL, 1, 15)) AUDIT_STATUS
FROM AP_EXPENSE_REPORT_HEADERS_ALL APERH
,AP_EXPENSE_REPORTS_ALL AERA
,AP_NOTES AN
WHERE APERH.EXPENSE_REPORT_ID=AERA.EXPENSE_REPORT_ID
AND AN.SOURCE_OBJECT_ID = APERH.REPORT_HEADER_ID
AND APERH.INVOICE_NUM= NVL(:P_ INVOICE_NUM, APERH.INVOICE_NUM);
Do drop a note by writing us at Dileep.dinesh@staging.doyensys.com or use the comment section below to ask your questions
