SELECT TRUNC(SYSDATE - e.start_date) "No. of Days"
,papf.person_number employee_number
,hzp.person_first_name emp_first_name
,hzp.person_last_name emp_last_name
,e.assignment_id
,ecp.card_program_name AS card_program_name
,cc.card_program_id AS card_prog_id
,e.org_id AS org_id
,e.emp_default_cost_center AS cost_ctr
,cc.company_account_id AS company_acct_id
,e.merchant_name
,ec.card_reference_id
,icc.masked_cc_number credit_card_num
,cc.description description
,e.emp_default_cost_center AS cost_center
,eed.segment1
,eed.segment2
,eed.segment3
,eed.segment4
,eed.segment5
,cc.billed_date
,cc.billed_amount
,cc.posted_date
,cc.posted_amount
,cc.transaction_date
,cc.transaction_amount
,e.orig_reimbursable_amount
,DECODE(er.expense_status_code, NULL, 'EMPLOYEE',
'PEND_MGR_APPROVAL', 'APPROVER',
'SUBMITTED', 'APPROVER',
'PEND_IND_APPROVAL', 'EMPLOYEE',
'MGR_REJECTED', 'EMPLOYEE',
'IND_REJECTED', 'EMPLOYEE',
'SAVED', 'EMPLOYEE',
'WITHDRAWN', 'EMPLOYEE',
'REQUEST_INFO', 'EMPLOYEE',
'EMPLOYEE') AS pending_action
,ca.payment_currency_code AS currency_code
FROM fusion.exm_expenses e
,fusion.exm_expense_Reports er
,fusion.exm_credit_card_trxns cc
,fusion.exm_cc_company_accounts ca
,fusion.exm_cards ec
,fusion.per_all_people_f papf
,fusion.IBY_CREDITCARD ICC
,fusion.hz_parties hzp
,fusion.PER_USERS pu
,fusion.EXM_EXPENSE_DISTS eed
,fusion.EXM_CARD_PROGRAMS ecp
WHERE (e.expense_source = 'CREDIT_CARD' or e.expense_source = 'BUSINESS_TRAVEL')
AND e.expense_report_id = er.expense_report_id(+)
AND er.expense_status_code(+) NOT IN ('PAID', 'PARTIAL_PAID', 'APPROVAL_COMPLETE', 'INVOICED', 'INVOICE_CANCELED')
AND (e.expense_report_id IS NULL OR er.expense_report_id IS NOT NULL)
AND (e.itemization_parent_expense_id IS NULL OR e.itemization_parent_expense_id = -1)
AND e.credit_card_trxn_id = cc.credit_card_trxn_id
AND cc.company_account_id = ca.cc_company_account_id(+)
AND ec.card_id(+) = cc.card_id
AND NVL(ec.account_type_code,'EMPLOYEE') <> 'COMPANY'
AND papf.person_id = e.person_id
AND pu.person_id = e.person_id
AND ec.card_reference_id = icc.instrid
AND pu.user_guid = hzp.user_guid(+)
AND e.expense_id = eed.expense_id
-- and papf.person_number in ( 1984678,1984999,1978237,1992954,1986569, 1984999 )
AND ecp.card_program_id = cc.card_program_id
AND expense_report_num like 'EXP%6386164%'
GROUP BY TRUNC(SYSDATE - cc.billed_date)
,TRUNC(SYSDATE - e.start_date)
,papf.person_number
,hzp.person_first_name
,hzp.person_last_name
,e.assignment_id
,ecp.card_program_name
,cc.card_program_id
,e.org_id
,e.emp_default_cost_center
,cc.company_account_id
,e.merchant_name
,ec.card_reference_id
,icc.masked_cc_number
,cc.description
,e.emp_default_cost_center
,eed.segment1
,eed.segment2
,eed.segment3
,eed.segment4
,eed.segment5
,cc.billed_date
,cc.billed_amount
,cc.posted_date
,cc.posted_amount
,cc.transaction_date
,cc.transaction_amount
,e.orig_reimbursable_amount
,DECODE(er.expense_status_code, NULL, 'EMPLOYEE',
'PEND_MGR_APPROVAL', 'APPROVER',
'SUBMITTED', 'APPROVER',
'PEND_IND_APPROVAL', 'EMPLOYEE',
'MGR_REJECTED', 'EMPLOYEE',
'IND_REJECTED', 'EMPLOYEE',
'SAVED', 'EMPLOYEE',
'WITHDRAWN', 'EMPLOYEE',
'REQUEST_INFO', 'EMPLOYEE',
'EMPLOYEE')
,ca.payment_currency_code
ORDER BY 1 DESC
