Monday, 4 March 2019

Fusion Query to get Revenue and billable hours of a Employee

SELECT pcdl.org_id employee_cost_org_id, pcdl.prvdr_gl_period_name gl_period,
       pcdl.prvdr_pa_period_name,
       NVL (pei.override_to_organization_id,
            pei.incurred_by_organization_id
           ) expenditure_org_id,
       trx_org.NAME expenditure_org, fnbu.bu_name AS exp_bu_name,
       prb.carrying_out_organization_id project_owning_org_id,
       (SELECT NAME
          FROM hr_organization_units
         WHERE organization_id =
                          prb.carrying_out_organization_id)
                                                           project_owning_org,
       prb.project_id, prb.segment1 project_number, prl.NAME project_name,
       (SELECT s.project_status_name
          FROM pjf_project_statuses_tl s
         WHERE prb.project_status_code = s.project_status_code)
                                                               project_status,
       ptv.task_id, ptv.task_name, NVL (ptv.billable_flag, 'N') billable_task,
       NVL (pei.billable_flag, 'N') billable_expenditure_item,
       pei.expenditure_item_id, pei.expenditure_item_date,
       pei.expenditure_ending_date,
       pet.expenditure_type_name expenditure_type,
       pec.expenditure_category_name expenditure_category,
       pei.incurred_by_person_id, ppn.full_name employee_name,
       papf.person_number employee_number, rev_det.usd_revenue_amount,
       NVL (pei.project_currency_code,
            prb.project_currency_code
           ) project_currency_code,
       rev_det.revenue_curr_amt project_revenue_amount,
       NVL (pei.projfunc_currency_code,
            prb.projfunc_currency_code
           ) projfunc_currency_code,
       rev_det.ledger_curr_revenue_amt projfunc_revenue_amount,
       gcc.segment1 company, gcc.segment2 ACCOUNT, gcc.segment3 department,
       gcc.segment4 region, gcc.segment5 FUNCTION, gcc.segment6 intercompany,
       pei.bill_trans_currency_code bill_transaction_currency,
       rev_det.trns_curr_revenue_amt bill_transaction_currency_rev,
       rev_det.usd_daily_revenue_amount, pei.bill_hold_flag,
       DECODE ((SELECT '1'
                  FROM pjb_bill_trxs a
                 WHERE pei.expenditure_item_id = a.transaction_id
                   AND NVL (inv_curr_billed_amt, 0) <> 0
                   AND ROWNUM = 1),
               '1', 'Y',
               'N'
              ) billed_flag,
       prb.attribute4,
       (SELECT project_type
          FROM pjf_project_types_tl ppt
         WHERE ppt.project_type_id = prb.project_type_id) project_type,
       (SELECT class_code
          FROM pjf_project_classes prc, pjf_class_codes_tl pct
         WHERE prc.class_code_id = pct.class_code_id
           AND prc.project_id = prb.project_id) region_code,
       pcdl.org_id, rev_det.ledger_curr_revenue_amt functrevenu,
       rev_det.contract_org, rev_det.contract_status, rev_det.contract_number,
       rev_det.contract_type, rev_det.contract_currency_code,
       rev_det.cont_curr_revenue_amt, rev_det.revenue_currency_code,
       rev_det.ledger_currency_code ledger_currency_code,
       rev_det.project_curr_revenue_amt, rev_det.revenue_curr_amt,
       rev_det.ledger_curr_revenue_amt, pei.revenue_hold_flag,
       pei.person_type,
       (SELECT NAME
          FROM per_jobs_f_vl pjb
         WHERE pjb.job_id = pei.person_job_id
           AND SYSDATE BETWEEN NVL (pjb.effective_start_date, SYSDATE - 1)
                           AND NVL (pjb.effective_end_date, SYSDATE + 1)
           AND active_status = 'A') job_name,
       pei.expenditure_type_id, rev_det.bill_rate,
       pei.project_raw_cost project_currency_amount, pei.quantity,
       pei.org_id recvr_org_id, pei.burden_cost_rate, rev_det.rev_bu_name,
       (SELECT hourly_cost_rate
          FROM pjf_comp_details_all pcd
         WHERE pei.person_job_id = pcd.person_job_id(+)
           AND pcd.person_id = pei.incurred_by_person_id
           AND pei.incurred_by_organization_id = pcd.organization_id(+)
           AND SYSDATE BETWEEN NVL (pcd.start_date_active, SYSDATE - 1)
                           AND NVL (pcd.end_date_active, SYSDATE + 1))
                                                      subcontractor_cost_rate,
       (SELECT fnbu.bu_name
          FROM fun_names_business_units_v fnbu
         WHERE fnbu.bu_id = prb.org_id) project_bu_name
  FROM pjc_cost_dist_lines_all pcdl,
       pjc_exp_items_all pei,
       pjf_tasks_v ptv,
       pjf_projects_all_b prb,
       pjf_projects_all_tl prl,
       pjc_exp_comments pec1,
       pjf_exp_types_vl pet,
       pjf_exp_categories_tl pec,
       fun_names_business_units_v fnbu,
       hr_all_organization_units_tl trx_org,
       gl_ledgers gl,
       gl_code_combinations gcc,
       per_person_names_f ppn,
       per_all_people_f papf,
       (SELECT xda2.event_id, xda2.source_distribution_id_num_1,
               xda2.rounding_class_code, xda2.source_distribution_id_num_2,
               xda2.ae_line_num, xda2.ae_header_id
          FROM xla_distribution_links xda2
         WHERE xda2.ref_ae_line_num IS NULL
           AND xda2.rounding_class_code = 'RAW_COST_CLEARING'
           AND NOT EXISTS (
                  SELECT 1
                    FROM xla_distribution_links xda1
                   WHERE xda1.ref_ae_line_num = xda2.ae_line_num
                     AND xda1.ref_ae_header_id = xda2.ae_header_id)) xda,
       xla_ae_lines xal,
       pjf_txn_sources_vl ts,
       (WITH gl_translation_rates_cte AS
             (SELECT DISTINCT UPPER (r.period_name) period_name,
                              r.to_currency_code to_currency_code,
                              r.avg_rate avg_rate, r.eop_rate eop_rate,
                              s.currency_code functional_currency
                         FROM gl_translation_rates r, gl_sets_of_books s
                        WHERE s.set_of_books_id = r.set_of_books_id
                          AND r.to_currency_code = 'USD')
        SELECT   SUM
                    (DECODE
                           (prd1.ledger_currency_code,
                            'USD', NVL (prd1.ledger_curr_revenue_amt, 0),
                            (  NVL
                                  (gtrc.avg_rate,
                                   NVL
                                      ((SELECT gdr.conversion_rate
                                          FROM gl_daily_rates gdr
                                         WHERE gdr.conversion_type =
                                                                   'Corporate'
                                           AND gdr.to_currency = 'USD'
                                           AND gdr.from_currency =
                                                    prd1.revenue_currency_code
                                           AND gdr.conversion_date =
                                                                  prd1.gl_date),
                                       1
                                      )
                                  )
                             * NVL (prd1.ledger_curr_revenue_amt, 0)
                            )
                           )
                    ) usd_revenue_amount,
                 SUM
                    (DECODE ('USD',
                             prd1.revenue_currency_code, prd1.revenue_curr_amt,
                             prd1.ledger_currency_code, prd1.ledger_curr_revenue_amt,
                             prd1.contract_currency_code, prd1.cont_curr_revenue_amt,
                             prd1.project_currency_code, prd1.project_curr_revenue_amt,
                               prd1.revenue_curr_amt
                             * NVL ((SELECT gdr.conversion_rate
                                       FROM gl_daily_rates gdr
                                      WHERE gdr.conversion_type = 'Corporate'
                                        AND gdr.to_currency = 'USD'
                                        AND gdr.from_currency =
                                                    prd1.revenue_currency_code
                                        AND gdr.conversion_date = prd1.gl_date),
                                    1
                                   )
                            )
                    ) usd_daily_revenue_amount,
                 SUM (prd1.revenue_curr_amt) revenue_curr_amt,
                 SUM (prd1.ledger_curr_revenue_amt) ledger_curr_revenue_amt,
                 SUM (prd1.trns_curr_revenue_amt) trns_curr_revenue_amt,
                 SUM (prd1.cont_curr_revenue_amt) cont_curr_revenue_amt,
                 SUM (prd1.project_curr_revenue_amt) project_curr_revenue_amt,
                 prd1.bill_rate, prd1.revenue_currency_code,
                 cont_org.NAME contract_org, okh.sts_code contract_status,
                 okh.contract_number contract_number, oct.NAME contract_type,
                 prd1.contract_currency_code, prd1.trns_currency_code,
                 prd1.ledger_currency_code,
                 UPPER (prd1.gl_period_name) gl_period_name,
                 prd1.transaction_id,
                 (SELECT fnbu.bu_name
                    FROM fun_names_business_units_v fnbu
                   WHERE fnbu.bu_id = prd1.org_id) rev_bu_name
            FROM pjb_rev_distributions prd1,
                 gl_translation_rates_cte gtrc,
                 okc_k_headers_all_b okh,
                 hr_all_organization_units cont_org,
                 okc_contract_types_vl oct
           WHERE 1 = 1
             AND okh.ID = prd1.contract_id
             AND okh.version_type = 'C'
             AND okh.owning_org_id = cont_org.organization_id
             AND okh.contract_type_id = oct.contract_type_id
             AND prd1.ledger_currency_code = gtrc.functional_currency(+)
             AND UPPER (prd1.gl_period_name) = gtrc.period_name(+)
        GROUP BY prd1.trns_currency_code,
                 cont_org.NAME,
                 okh.sts_code,
                 okh.contract_number,
                 oct.NAME,
                 prd1.contract_currency_code,
                 prd1.revenue_currency_code,
                 prd1.bill_rate,
                 UPPER (prd1.gl_period_name),
                 prd1.transaction_id,
                 prd1.ledger_currency_code,
                 prd1.org_id) rev_det
 WHERE 1 = 1
   AND pei.expenditure_item_id = rev_det.transaction_id(+)
   AND UPPER (pcdl.prvdr_gl_period_name) = rev_det.gl_period_name(+)
   AND pei.transaction_source_id = ts.transaction_source_id
   AND pcdl.expenditure_item_id = pei.expenditure_item_id
   AND pei.project_id = ptv.project_id(+)
   AND pei.task_id = ptv.task_id(+)
   AND pei.project_id = prb.project_id
   AND pei.project_id = prl.project_id
   AND pei.expenditure_item_id = pec1.expenditure_item_id(+)
   AND pei.expenditure_type_id = pet.expenditure_type_id
   AND pec.expenditure_category_id = pet.expenditure_category_id
   AND fnbu.bu_id = pei.org_id
   AND fnbu.primary_ledger_id = gl.ledger_id
   AND pcdl.acct_event_id = xda.event_id(+)
   AND pcdl.expenditure_item_id = xda.source_distribution_id_num_1(+)
   AND pcdl.line_num = xda.source_distribution_id_num_2(+)
   AND xda.ae_header_id = xal.ae_header_id(+)
   AND xda.ae_line_num = xal.ae_line_num(+)
   AND gcc.code_combination_id =
          NVL (NVL (xal.code_combination_id,
                    NVL (pcdl.raw_cost_dr_ccid, pcdl.raw_cost_cr_ccid)
                   ),
               (SELECT pas.default_code_comb_id
                  FROM per_all_assignments_m pas
                 WHERE SYSDATE BETWEEN NVL (pas.effective_start_date,
                                            SYSDATE - 1
                                           )
                                   AND NVL (pas.effective_end_date,
                                            SYSDATE + 1
                                           )
                   AND pas.person_id = pei.incurred_by_person_id
                   AND pas.assignment_id = pei.hcm_assignment_id)
              )
   AND trx_org.organization_id =
          NVL (pei.override_to_organization_id,
               pei.incurred_by_organization_id
              )
   AND pcdl.line_num_reversed IS NULL
   AND pcdl.reversed_flag IS NULL
   AND ppn.person_id = pei.incurred_by_person_id
   AND SYSDATE BETWEEN NVL (ppn.effective_start_date, SYSDATE - 1)
                   AND NVL (ppn.effective_end_date, SYSDATE + 1)
   AND ppn.name_type(+) = 'GLOBAL'
   AND papf.person_id(+) = pei.incurred_by_person_id
   AND SYSDATE BETWEEN NVL (papf.effective_start_date, SYSDATE - 1)
                   AND NVL (papf.effective_end_date, SYSDATE + 1)
   AND UPPER (pcdl.prvdr_gl_period_name) IN (:p_period_name)
   AND pec.expenditure_category_name IN ('Labor')

No comments:

Post a Comment