Saturday, 22 September 2018

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 

5 comments:

  1. Excellent blog I visit this blog it's really awesome. The important thing is that in this blog content written clearly and understandable. The content of information is very informative.
    Oracle Fusion HCM Online Training
    Oracle Fusion SCM Online Training
    Oracle Fusion Financials Online Training
    Big Data and Hadoop Training In Hyderabad
    Oracle Fusion HCM Training In Hyderabad

    ReplyDelete
  2. Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle PPM Cloud . Actually I was looking for the same information on internet for Oracle Project Portfolio Management (PPM) Cloud Tutorial and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle PPM Cloud . By attending Oracle PPM Cloud Training .

    ReplyDelete
  3. Thanks for sharing this great information I am impressed by the information that you have on this blog. Same as your blog i found another one Oracle Fusion Procurement . Actually I was looking for the same information on internet for Oracle Fusion Supply Chain Management Cloud and came across your blog. I am impressed by the information that you have on this blog. It shows how well you understand this subject, you can learn more aboutOracle Fusion Manufacturing . By attending Oracle Fusion Financials .

    ReplyDelete