Query for Outstanding Card Transactions Report (Doc ID 2755945.1) in Fusion Expenses

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