Monday, July 28, 2014

SQL query to find expense approver in Oracle Internet Expenses

Oracle I-Expenses

Query built to pull data of active employees and expenses created after 1-jan-2013

--Below sql to give expense details along with approver from workflow

SELECT
AERAA.REPORT_HEADER_ID REPORT_#,
AERAA.SOURCE SOURCE,
ALC.DISPLAYED_FIELD STATUS,
PAPF.FULL_NAME EMPLOYEE,
AERAA.FLEX_CONCATENATED COST_CENTER,
AERAA.REPORT_SUBMITTED_DATE SUBMITTED_ON,
NVL (PAPF1.FULL_NAME ,AERAA.OVERRIDE_APPROVER_NAME) APPROVER,
WIAS.END_DATE APPROVED_ON
FROM AP.AP_EXPENSE_REPORT_HEADERS_ALL AERAA
LEFT OUTER JOIN APPS.AP_LOOKUP_CODES ALC ON AERAA.EXPENSE_STATUS_CODE = ALC.LOOKUP_CODE AND ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
LEFT OUTER JOIN APPS.WF_ITEM_ACTIVITY_STATUSES WIAS ON AERAA.REPORT_HEADER_ID=WIAS.ITEM_KEY AND (WIAS.DUE_DATE IS NOT NULL OR  WIAS.NOTIFICATION_ID IS NOT NULL)  AND WIAS.ACTIVITY_RESULT_CODE='APPROVED' AND WIAS.ITEM_TYPE='APEXP'
LEFT OUTER JOIN HR.PER_ALL_PEOPLE_F PAPF1 ON WIAS.ASSIGNED_USER=PAPF1.EMPLOYEE_NUMBER AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE AND PAPF1.CURRENT_EMPLOYEE_FLAG = 'Y'  ,
HR.PER_ALL_PEOPLE_F PAPF
WHERE AERAA.CREATION_DATE >= '01-JAN-2013'
AND AERAA.EMPLOYEE_ID = PAPF.PERSON_ID
AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE ;

Another way-
Pulling arpprover from expense table as well from workflow

SELECT
AERAA.REPORT_HEADER_ID REPORT_#,
AERAA.SOURCE SOURCE,
ALC.DISPLAYED_FIELD STATUS,
PAPF.FULL_NAME EMPLOYEE,
AERAA.FLEX_CONCATENATED COST_CENTER,
AERAA.REPORT_SUBMITTED_DATE SUBMITTED_ON,
AERAA.OVERRIDE_APPROVER_NAME APPROVER,
PAPF1.FULL_NAME WORKFLOW,
NVL (PAPF1.FULL_NAME ,AERAA.OVERRIDE_APPROVER_NAME) WORKFLOW_OVERRIDE,
WIAS.END_DATE APPROVED_ON
FROM AP.AP_EXPENSE_REPORT_HEADERS_ALL AERAA
LEFT OUTER JOIN APPS.AP_LOOKUP_CODES ALC ON AERAA.EXPENSE_STATUS_CODE = ALC.LOOKUP_CODE AND ALC.LOOKUP_TYPE = 'EXPENSE REPORT STATUS'
LEFT OUTER JOIN APPS.WF_ITEM_ACTIVITY_STATUSES WIAS ON AERAA.REPORT_HEADER_ID=WIAS.ITEM_KEY AND (WIAS.DUE_DATE IS NOT NULL OR  WIAS.NOTIFICATION_ID IS NOT NULL)  AND WIAS.ACTIVITY_RESULT_CODE='APPROVED' AND WIAS.ITEM_TYPE='APEXP'
LEFT OUTER JOIN HR.PER_ALL_PEOPLE_F PAPF1 ON WIAS.ASSIGNED_USER=PAPF1.EMPLOYEE_NUMBER AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE AND PAPF1.CURRENT_EMPLOYEE_FLAG = 'Y'  ,
HR.PER_ALL_PEOPLE_F PAPF
WHERE AERAA.CREATION_DATE >= '01-JAN-2013'
AND AERAA.EMPLOYEE_ID = PAPF.PERSON_ID
AND PAPF.CURRENT_EMPLOYEE_FLAG = 'Y'
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE );
 ;

2 comments:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at training@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete
  2. Is there any way to show the final approver, like in the case where an approval is delegated? This seems to show the first approver it was sent to and not the final approver.

    ReplyDelete