Monday 4 March 2019

Query to Find Project Estimation To Complete in Oracle Fusion

SELECT A.PROJECT_STATUS,
       A.OPERATING_UNIT_NAME,
       A.AGREEMENT_TYPE,
       A.PROJECT_ORGANIZATION_NAME,
       A.PROJECT_NUMBER,
       A.PROJECT_NAME,
       A.START_DATE,
       A.COMPLETION_DATE,
       A.TASK_NUMBER,
       A.TASK_NAME,
       A.PROJECT_CURRENCY,
       ROUND(A.BUDGETED_REV_PROJ_CURR) BUDGETED_REV_PROJ_CURR,
       ROUND(A.ACTUAL_REV_ITD_PRJ_CURR) ACTUAL_REV_ITD_PRJ_CURR,
       ROUND(A.BILLABLE_HOURS_ACTUAL_ITD) BILLABLE_HOURS_ACTUAL_ITD,
       ROUND(A.TOTAL_HOURS_ACTUAL_ITD) TOTAL_HOURS_ACTUAL_ITD,
       A.FUNC_CURRENCY,
       ROUND(A.ACTUAL_REV_ITD_FUNC_CURR) ACTUAL_REV_ITD_FUNC_CURR,
       (ROUND (     ( (A.ACTUAL_REV_ITD_PRJ_CURR / (CASE
                                                   WHEN A.BUDGETED_REV_PROJ_CURR = 0 THEN 0.1
                                                   ELSE A.BUDGETED_REV_PROJ_CURR
                                                END)
                   ) * 100)) || ' %') AS PERC_COMPLETE_PROJ_CURR,
       (ROUND ( ( (A.ACTUAL_REV_ITD_FUNC_CURR
                 / (CASE
                       WHEN (TRUNC ( (TRUNC (NVL(GDR.conversion_rate, 1), 4) * a.Budgeted_Rev_Proj_Curr), 2)) = 0
                       THEN
                          0.1
                       ELSE
                          (TRUNC ( (TRUNC (NVL(GDR.conversion_rate, 1), 4) * a.Budgeted_Rev_Proj_Curr), 2))
                    END)) * 100))
        || ' %') AS PERC_COMPLETE_FUNC_CURR
       ,A.Contract_Status
       ,A.contract_number
       ,A.line_number                                                                      
  FROM gl_daily_rates GDR,
       (SELECT PPA.PROJECT_STATUS_CODE AS Project_Status,
               FUNB.BU_NAME AS Operating_Unit_Name,
               OTYPE.NAME AS Agreement_Type,
               haou.NAME AS project_organization_name,
               ppa.segment1 AS project_number,
               PPA.NAME AS project_name,
               TO_CHAR (ppa.start_date,
                        'DD-MON-YYYY',
                        'NLS_DATE_LANGUAGE = american')
                  AS start_date,
               TO_CHAR (ppa.completion_date,
                        'DD-MON-YYYY',
                        'NLS_DATE_LANGUAGE = american')
                  AS completion_date
              ,PT.TASK_NUMBER
              ,PT.TASK_NAME
               ,OKCH.CURRENCY_CODE AS Project_currency,
               okcl.line_amount AS Budgeted_Rev_Proj_Curr             
              ,(SELECT NVL(SUM(NVL(PRD.project_curr_revenue_amt, 0)),0) project_curr_revenue_amt
                     FROM pjb_rev_distributions PRD
                    WHERE 1=1
                      AND PRD.contract_id = OKCH.id
                      AND PRD.contract_line_id = OKCL.id) Actual_Rev_ITD_Prj_Curr
              ,(SELECT NVL (SUM (projectcostdistributionpeo.quantity), 0)
                  FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
                       pjc_exp_items_all expenditureitempeo,
                       pjf_exp_types_vl pet
                 WHERE (projectcostdistributionpeo.expenditure_item_id =
                           expenditureitempeo.expenditure_item_id
                        AND expenditureitempeo.expenditure_type_id =
                              pet.expenditure_type_id
                        AND projectcostdistributionpeo.TASK_ID = NVL(LINK.proj_element_id, projectcostdistributionpeo.TASK_ID)
                        AND pet.expenditure_type_name IN
                                 ('Professional Labor',
                                  'Contract Professional Labor')
                        AND projectcostdistributionpeo.BILLABLE_FLAG = 'Y')
                       AND expenditureitempeo.project_id = ppa.project_id)
                  AS Billable_Hours_Actual_ITD,
               (SELECT NVL (SUM (projectcostdistributionpeo.quantity), 0)
                  FROM pjc_cost_dist_lines_all projectcostdistributionpeo,
                       pjc_exp_items_all expenditureitempeo,
                       pjf_exp_types_vl pet
                 WHERE (projectcostdistributionpeo.expenditure_item_id =
                           expenditureitempeo.expenditure_item_id
                        AND expenditureitempeo.expenditure_type_id =
                              pet.expenditure_type_id
                        AND projectcostdistributionpeo.TASK_ID = NVL(LINK.proj_element_id, projectcostdistributionpeo.TASK_ID)
                        AND pet.expenditure_type_name IN
                                 ('Professional Labor',
                                  'Contract Professional Labor'))
                       AND expenditureitempeo.project_id = ppa.project_id)
                  AS Total_Hours_Actual_ITD,
               link.proj_element_id
              ,(SELECT LEDGER_CURRENCY_CODE
                  FROM pjb_rev_distributions
                 WHERE linked_project_id = ppa.project_id AND ROWNUM < 2)
                  AS Func_Currency
                 ,(SELECT NVL(SUM(NVL(PRD.ledger_curr_revenue_amt, 0)), 0)
                     FROM pjb_rev_distributions PRD
                    WHERE 1=1
                      AND PRD.contract_id = OKCH.id
                      AND PRD.contract_line_id = OKCL.id) Actual_Rev_ITD_Func_curr     
              ,OKCL.REVENUE_IMPACT_DATE
              ,OKCH.sts_code Contract_Status                                             
              ,OKCH.contract_number
              ,PPA.project_id
              ,PPA.created_from_project_id
              ,OKCL.line_number                                                          
          FROM okc_k_headers_all_b OKCH,
               okc_k_lines_b OKCL,
               okc_contract_types_tl OTYPE,
               pjb_cntrct_proj_links LINK,
               pjf_projects_all_vl PPA,
               pjf_tasks_v PT,
               fun_all_business_units_v FUNB,
               hr_all_organization_units HAOU
         WHERE     1 = 1
           AND OKCH.id                           = OKCL.chr_id
           AND OKCH.version_type                 = 'C'
           AND OKCL.version_type                 = 'C'         
           AND OKCH.sts_code                     <> 'EXPIRED'             --= 'ACTIVE' 
           AND OKCH.id                           = LINK.contract_id
           AND OKCL.id                           = LINK.contract_line_id
           AND LINK.version_type                 = 'C'
           AND LINK.project_id                   = PPA.project_id
           AND LINK.proj_element_id              = PT.task_id (+)            
           AND PPA.carrying_out_organization_id  = HAOU.organization_id                  
           AND FUNB.BU_ID                        = OKCH.org_id
           AND OTYPE.language                    = 'US'
           AND OTYPE.contract_type_id            = OKCH.contract_type_id
           AND PPA.PROJECT_STATUS_CODE <> 'CLOSED'   ) A
 WHERE     GDR.from_currency (+)  = a.project_currency
       AND GDR.to_currency (+)  = a.Func_Currency
       AND UPPER (GDR.conversion_type (+)) = 'CORPORATE'
       AND TRUNC(GDR.conversion_date (+)) = TRUNC(a.revenue_impact_date)      
ORDER BY A.project_number, A.contract_number, A.line_number, A.task_number

1 comment: