PA Invoice Detail Query – FUSION

SELECT   PEI.expenditure_item_id
        ,PPA.segment1 project_number
        ,PPA.name project_name
        ,PPA.segment1||’-‘||pih.invoice_num invoice_number
        ,TO_CHAR(PIH.INVOICE_DATE, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) INVOICE_DATE
        ,(SELECT INV.invoice_num FROM ap_invoices_all INV
           WHERE INV.invoice_id = PEI.original_header_id) expense_report_number
        ,PPN.full_name Consultant
        ,PT.task_number
        ,PT.task_name
        ,PET.expenditure_type_name
        ,TO_CHAR(PEI.expenditure_item_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) EXPENDITURE_ITEM_DATE
        ,DECODE(TO_CHAR(PEI.expenditure_item_date, ‘FmDay’), 5, TO_CHAR(PEI.expenditure_item_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’),
                TO_CHAR(NEXT_DAY(PEI.expenditure_item_date, 6), ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’)) Week_ending_date
        ,PBT.bill_rate
        ,SUM(PILD.invoice_curr_billed_amt) amount
        ,PEC.expenditure_comment
        ,PEI.quantity
        ,PIH.INVOICE_CURRENCY_CODE
    FROM pjc_exp_items_all pei,
         pjf_projects_all_vl ppa,
         pjb_bill_trxs pbt,
         pjb_inv_line_dists pild,
         pjb_invoice_lines pil,
         pjb_invoice_headers pih,
         per_all_people_f ppf,
         per_person_names_f ppn,
         pjf_tasks_v pt,
         pjf_exp_types_vl pet,
         pjc_exp_comments pec
   WHERE 1 = 1
     AND pei.project_id = ppa.project_id
     AND pbt.transaction_id = pei.expenditure_item_id
     AND pild.bill_trx_id = pbt.bill_trx_id
     AND pil.invoice_line_id = pild.invoice_line_id
     AND pih.invoice_id = pil.invoice_id
     AND pei.incurred_by_person_id = ppf.person_id(+)
     AND ppf.person_id = ppn.person_id
     AND ppn.name_type = ‘GLOBAL’
     AND TRUNC (SYSDATE) BETWEEN ppf.effective_start_date
                             AND ppf.effective_end_date
     AND TRUNC (SYSDATE) BETWEEN ppn.effective_start_date
                             AND ppn.effective_end_date
     AND pei.task_id = pt.task_id
     AND pei.expenditure_type_id = pet.expenditure_type_id
     AND pei.expenditure_item_id = pec.expenditure_item_id(+)
     AND (ppa.segment1 IN (:p_project_number) OR ‘ALL’ IN (:p_project_number))
GROUP BY pei.expenditure_item_id,
         ppa.segment1,
         ppa.NAME,
         pih.invoice_num,
         pih.invoice_date,
         ppn.full_name,
         pt.task_number,
         pt.task_name,
         pet.expenditure_type_name,
         pei.expenditure_item_date,
         pbt.bill_rate,
         pec.expenditure_comment,
         pei.original_header_id,
         pei.quantity,
         pih.invoice_currency_code
UNION
SELECT   NULL expenditure_item_id
        ,PPA.segment1 project_number
        ,PPA.name project_name
        ,PPA.segment1||’-‘||pih.invoice_num invoice_number
        –,TO_CHAR(PIH.bill_to_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) bill_through_date
        ,TO_CHAR(PIH.INVOICE_DATE, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) INVOICE_DATE
        ,NULL expense_report_number
        ,NULL Consultant
        ,PT.task_number
        ,PT.task_name
        ,PBE.EVENT_DESC expenditure_type_name
        ,TO_CHAR(PBE.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’) EXPENDITURE_ITEM_DATE
        ,DECODE(TO_CHAR(PBE.completion_date, ‘FmDay’), 5, TO_CHAR(PBE.completion_date, ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’),
                TO_CHAR(NEXT_DAY(PBE.completion_date, 6), ‘DD-MON-YYYY’, ‘NLS_DATE_LANGUAGE = american’)) Week_ending_date
        ,NULL bill_rate
        –,SUM(PBE.bill_trns_amount) amount
        ,SUM(PBE.invoiced_amt) amount
        ,NULL expenditure_comment
        ,PBE.quantity_billed quantity
        ,PIH.INVOICE_CURRENCY_CODE
    FROM pjb_inv_line_dists pild,
         pjb_invoice_lines pil,
         pjb_invoice_headers pih,
         pjb_billing_events pbe,
         pjf_projects_all_vl ppa,
         pjf_tasks_v pt
   WHERE 1 = 1
     AND pil.invoice_line_id = pild.invoice_line_id
     AND pih.invoice_id = pil.invoice_id
     AND pild.bill_transaction_type_code = ‘EVT’
     AND pild.transaction_id = pbe.event_id
     AND pild.transaction_project_id = ppa.project_id
     AND pbe.task_id = pt.task_id
     AND (ppa.segment1 IN (:p_project_number) OR ‘ALL’ IN (:p_project_number))
GROUP BY ppa.segment1,
         ppa.NAME,
         pih.invoice_num,
         pih.invoice_date,
         pt.task_number,
         pt.task_name,
         pbe.event_desc,
         pbe.completion_date,
         pbe.quantity_billed,
         pih.invoice_currency_code
ORDER BY 2, 1