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 );
;
Regards
ReplyDeleteSridevi 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.
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.
ReplyDeleteThanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com
ReplyDeleteGreat article and extremely informative. It would definitely help me and many others. Had a great learning. Thanks for sharing! Expense Report Software | Expense Tracker
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