Pending For Approval Expense Reports SQL Query in Oracle apps

Pending For Approval Expense Reports SQL Query in Oracle apps

 

In this post , you will get the sql query to find the Open Expense Reports which are pending for managers approval in oracle apps. This query will give you the complete details of the expense reports and the information's about the current approval. Here below is the sql query to find the Open Expense Reports which are pending for managers approval in oracle apps. 

 

 

Sql Query Pending For Approval Expense Reports SQL Query in Oracle apps

 

 

select (SELECT PAPF.FULL_NAME FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID=AERHA.EMPLOYEE_ID
AND PAPF.EFFECTIVE_END_DATE>SYSDATE) "Employee Name",
 (SELECT PAPF.EMAIL_ADDRESS FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID=AERHA.EMPLOYEE_ID
AND PAPF.EFFECTIVE_END_DATE>SYSDATE) "Employee Email",
AERHA.INVOICE_NUM,
AERHA.CREATION_DATE,
AERHA.TOTAL,DESCRIPTION,
AERHA.DEFAULT_CURRENCY_CODE,
AERHA.FLEX_CONCATENATED COST_CENTER,
decode(AERHA.EXPENSE_STATUS_CODE,'PENDMGR','Pending Manager Approval') "Expense Status",
 (SELECT PAPF.FULL_NAME FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID=AERHA.EXPENSE_CURRENT_APPROVER_ID
AND PAPF.EFFECTIVE_END_DATE>SYSDATE) "Current Approver Name",
 (SELECT PAPF.EMAIL_ADDRESS FROM PER_ALL_PEOPLE_F PAPF
WHERE PAPF.PERSON_ID=AERHA.EXPENSE_CURRENT_APPROVER_ID
AND PAPF.EFFECTIVE_END_DATE>SYSDATE) "Current Approver Email",
AERHA.REPORT_SUBMITTED_DATE
from ap_expense_report_headers_all AERHA
where org_id=:P_ORG_ID
and EXPENSE_STATUS_CODE='PENDMGR'
order by AERHA.CREATION_DATE