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 );
 ;

1 comment:

  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