PROJECT UNBILLED BALANCES- FUSION

This report will display all project unbilled details.


SELECT   ppa.project_id, ppa.segment1 project_number, 
         NVL ((SELECT pt.project_type
               FROM pjf_project_types_tl pt, pjf_projects_all_vl p
              WHERE p.project_id(+) = ppa.project_id
                AND p.project_type_id = pt.project_type_id),
            'N/A'
           ) project_type,
ppt.NAME project_name,
         ppt.description description,
         (SELECT ppm.resource_source_name
            FROM pjf_proj_team_members_v ppm
           WHERE 1 = 1
             AND project_role_id = 1
             AND project_id = ppa.project_id) manager_name,
         haou.NAME organization_name, pih.invoice_currency_code currency_code,
         SUM (NVL (prd.revenue_curr_amt, 0)) proj_todate_revenue_amt,
         SUM (NVL (ccdl.acct_amount, 0)) proj_todate_inv_amt,
         SUM (NVL (pild.trns_curr_billed_amt, 0)) unbilled_amt,
         pih.ra_invoice_number Open_Ar_inv,
         (SELECT TO_CHAR (MIN (e.completion_date),
                          'DD-MON-YYYY',
                          'NLS_DATE_LANGUAGE = american'
                         ) next_event_date
            FROM pjb_billing_events e
           WHERE e.project_id = ppa.project_id
             AND e.bill_trns_amount <> 0
             AND (e.bill_hold_flag = 'N' OR e.completion_date >= SYSDATE))
                                                               next_bill_date
                                                               --:p_as_of_date
                                                                             ,
         (SELECT   TO_CHAR (MAX (i.invoice_date),
                            'DD-MON-YYYY',
                            'NLS_DATE_LANGUAGE = american'
                           ) max_proj_inv_date
              FROM pjb_rev_distributions i
             WHERE i.linked_project_id = ppa.project_id
               AND i.invoice_date <= SYSDATE                   --:p_as_of_date
          --AND i.invoice_status_code = 'ACCEPTED'
          GROUP BY linked_project_id) last_date_invoiced,
         TO_CHAR (ppa.completion_date,
                  'DD-MON-YYYY',
                  'NLS_DATE_LANGUAGE = american'
                 ) completion_date
    FROM pjc_exp_items_all pei,
         pjf_exp_types_tl pet,
         pjf_projects_all_b ppa,
         pjf_projects_all_tl ppt,
         pjf_project_statuses_tl pps,
         pjf_proj_elements_b ppeb,
         pjf_proj_elements_tl ppet,
         pjb_bill_trxs pbt,
         pjb_invoice_headers pih,
         pjb_invoice_lines pil,
         pjb_inv_line_dists pild,
         pjb_rev_distributions prd,
         pjc_xla_ccdl_lines_adj_v ccdl,
         hr_all_organization_units haou
   WHERE 1 = 1
     AND pei.expenditure_type_id = pet.expenditure_type_id
     AND pei.project_id = ppa.project_id
     AND pei.project_id = ppt.project_id
     AND ppeb.project_id = ppt.project_id
     AND ppa.project_status_code = pps.project_status_code
     --AND pcpl.proj_element_id    =    ppeb.proj_element_id
     --AND pcpl.proj_element_id    =    ppet.proj_element_id
     AND pbt.linked_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 = pild.invoice_id
     AND pih.invoice_id = pil.invoice_id
     AND prd.bill_trx_id = pbt.bill_trx_id
     AND prd.revenue_rate_source_id = pbt.revenue_rate_source_id
     AND pei.expenditure_item_id = ccdl.expenditure_item_id(+)
     AND ppeb.carrying_out_organization_id = haou.organization_id
     --AND ppa.segment1 = '11287'
GROUP BY ppa.project_id,
         ppa.segment1,
         ppt.NAME,
         ppt.description,
         haou.NAME,
         pih.invoice_currency_code,
         ppa.completion_date,

         pih.ra_invoice_number