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 );
;
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 );
;
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.
ReplyDeleteCould someone help. I need to get the Expense location field information, but I don't know what table to get it. Thank you
ReplyDelete