Terminated Employee Pending Expenses with Manager details in the Cloud

Problem :

Terminated Employee Pending Expenses with Manager details in the Cloud.

Solution :

Query created to find the inactive employee’s expense report

select CARD_PROGRAM_NAME,EXPENSE_REPORT_NUM,EXPENSE_STATUS_CODE,ORIG_REIMBURSABLE_AMOUNT,DESCRIPTION,EXP_CREATION_DATE,ACTUAL_TERMINATION_DATE,PERSON_NUMBER,FULL_NAME,Manager_Emp_number,Manager_Name

from

(select ecp.CARD_PROGRAM_NAME,

eer.EXPENSE_REPORT_NUM,

eer.EXPENSE_STATUS_CODE,

nvl(ee.ORIG_REIMBURSABLE_AMOUNT,ee.REIMBURSABLE_AMOUNT)

–ee.FUNC_CURRENCY_AMOUNT

ORIG_REIMBURSABLE_AMOUNT,

ee.DESCRIPTION,

to_char(ee.CREATION_DATE,’DD-MON-YYYY’) EXP_CREATION_DATE,

to_char(ppos.ACTUAL_TERMINATION_DATE,’DD-MON-YYYY’) ACTUAL_TERMINATION_DATE,

papf.PERSON_NUMBER,

ppn.FULL_NAME,

a.person_number Manager_Emp_number,

a.full_name Manager_Name

from EXM_CARD_PROGRAMS ecp,

EXM_EXPENSES ee,

EXM_EXPENSE_REPORTS eer,

EXM_CREDIT_CARD_TRXNS ecct,

per_all_people_f papf,

per_periods_of_service ppos,

PER_PERSON_NAMES_F ppn,

(select ppn1.Full_Name,pasf.person_id,papf1.person_number

from PER_PERSON_NAMES_F ppn1,

per_all_people_f papf1,

per_assignment_supervisors_f pasf

where papf1.person_id = ppn1.person_id

and ppn1.NAME_TYPE = ‘GLOBAL’

AND TRUNC(SYSDATE) BETWEEN ppn1.effective_start_Date AND ppn1.effective_end_Date

and pasf.manager_type = ‘LINE_MANAGER’

and pasf.manager_id = papf1.person_id

and pasf.object_version_number = (select max(object_version_number ) from

per_assignment_supervisors_f pasf1

where pasf.person_id = pasf1.person_id)) a

where ecp.card_program_id = ecct.card_program_id

and ee.credit_card_trxn_id = ecct.credit_card_trxn_id

and ee.expense_report_id = eer.expense_report_id(+)

and ee.EXPENSE_SOURCE = ‘CREDIT_CARD’

and nvl(eer.EXPENSE_STATUS_CODE,’X’) <> ‘PAID’

and eer.person_id(+) = ee.person_id

and ee.person_id = papf.person_id

AND papf.person_id = ppos.person_id

AND papf.person_id = ppn.person_id

and ppn.NAME_TYPE = ‘GLOBAL’

AND TRUNC(SYSDATE) BETWEEN ppn.effective_start_Date AND ppn.effective_end_Date

AND ppos.period_of_service_id = (SELECT MAX(period_of_service_id)

FROM per_periods_of_service

WHERE person_id = papf.person_id)

AND ppos.actual_termination_date <= TRUNC(SYSDATE + 1)

and papf.person_id = a.person_id(+)

union

select ‘CASH’ CARD_PROGRAM_NAME,

–ecp.CARD_PROGRAM_NAME,