Monday 4 March 2019

Fusion Query to get Employee Allocated and Utilization hours

select Entity,Business_unit,reporting_organization,EMPLOYEE_NUMBER,EMPLOYEE_NAME,EFFECTIVE_START_DATE,TOTAL_HOURS,BILLABLE_HOURS,CHARGEABLE_HOURS,EXPENDITURE_ENDING_DATE,YEAR_START_DATE,
round((BILLABLE_HOURS/TOTAL_HOURS)*100,2) BILLABLE_PCT,round((CHARGEABLE_HOURS/TOTAL_HOURS)*100,2) CHARGEABLE_PCT ,normal_hours,effective_end_date
from ( SELECT Entity,Business_unit,
            EMPLOYEE_NUMBER,EMPLOYEE_NAME,EFFECTIVE_START_DATE,TOTAL_HOURS,BILLABLE_HOURS,CHARGEABLE_HOURS,EXPENDITURE_ENDING_DATE, YEAR_START_DATE,
round((BILLABLE_HOURS/TOTAL_HOURS)*100,2) BILLABLE_PCT,round((CHARGEABLE_HOURS/TOTAL_HOURS)*100,2) CHARGEABLE_PCT ,normal_hours,effective_end_date
 from ( 
select  (SELECT fv.description
            FROM fnd_flex_values_vl fv,
                 fnd_flex_value_sets fvs
           WHERE 1 = 1
             AND fv.flex_value_set_id = fvs.flex_value_set_id
             AND fvs.flex_value_set_name = 'Department'
             AND fv.flex_value = gcc.segment3) Entity,
            (select distinct hou.name
            from per_all_people_f papf1,per_all_assignments_m paam,
             hr_all_organization_units hou
            where 1=1
            and papf1.person_id=paam.person_id
            and paam.BUSINESS_UNIT_ID = hou.organization_id
            and paam.ASSIGNMENT_STATUS_TYPE ='ACTIVE'
            and sysdate between papf1.effective_start_date and papf1.effective_end_date
            and sysdate between paam.effective_start_date and paam.effective_end_date
            and papf1.person_id = papf.person_id)   Business_unit ,
             gcc.segment3 Department,
               papf.person_number employee_number,
         NVL (ppn.known_as, ppn.full_name) employee_name,
        ppos.date_start effective_start_date,
        ppos.actual_termination_date effective_end_date,
        pasm.normal_hours ,
         (SELECT SUM (quantity)
            FROM pjc_exp_items_all pei,
                 pjf_exp_types_vl pet,
                 pjf_exp_categories_tl pec
           WHERE NVL (pei.billable_flag, 'N') = 'Y'
             AND pei.expenditure_type_id = pet.expenditure_type_id
             AND pec.expenditure_category_id = pet.expenditure_category_id
             AND pec.expenditure_category_name IN ('Labor')
             AND expenditure_item_date >= :P_From_Date
             and expenditure_item_date <= :P_To_Date                 
             AND pei.incurred_by_person_id = papf.person_id) billable_hours,
         (SELECT SUM (quantity)
            FROM pjc_exp_items_all pei,
                 pjf_exp_types_vl pet,
                 pjf_exp_categories_tl pec
           WHERE 1=1--NVL (pei.billable_flag, 'N') = 'N'
             AND pei.expenditure_type_id = pet.expenditure_type_id
             AND pec.expenditure_category_id = pet.expenditure_category_id
             AND pec.expenditure_category_name IN ('Labor')
             AND expenditure_item_date >= :P_From_Date
             and expenditure_item_date <= :P_To_Date   
             AND pei.incurred_by_person_id = papf.person_id) CHARGEABLE_HOURS,
         (SELECT MAX (expenditure_ending_date)
            FROM pjc_exp_items_all pei,
                 pjf_exp_types_vl pet,
                 pjf_exp_categories_tl pec
           WHERE 1 = 1                    --NVL (pei.billable_flag, 'N') = 'Y'
             AND pei.expenditure_type_id = pet.expenditure_type_id
             AND pec.expenditure_category_id = pet.expenditure_category_id
             AND pec.expenditure_category_name IN ('Labor')
             AND expenditure_item_date >= :P_From_Date
             and expenditure_item_date <= :P_To_Date 
             AND pei.incurred_by_person_id = papf.person_id)
                                                      expenditure_ending_date,
         to_char(:P_From_Date,'DD-MON-YY') year_start_date,
  (((((TO_DATE(TO_CHAR(TRUNC(NEXT_DAY(:P_To_Date, 6)) - INTERVAL '7' DAY,'MM-DD-RRRR'),'MM-DD-RRRR'))-
TO_DATE(TO_CHAR(:P_From_Date,'MM-DD-RRRR'),'MM-DD-RRRR') ) -
(select sum(total)  weekends from (
SELECT  COUNT (*) total
    FROM (SELECT :P_To_Date + num dat
            FROM (SELECT    LEVEL - 1 num
                        FROM DUAL
                  CONNECT BY LEVEL <=
                                ABS(TO_DATE(TO_CHAR(:P_From_Date,'MM-DD-RRRR'),'MM-DD-RRRR') -
TO_DATE(TO_CHAR(TRUNC(NEXT_DAY(:P_To_Date, 6)) - INTERVAL '7' DAY,'MM-DD-RRRR'),'MM-DD-RRRR') - 1)))
   WHERE TO_CHAR (dat, 'DY') IN ('1', '7')
GROUP BY TO_CHAR (dat, 'DY'))))+1)*(pasm.normal_hours/5))  total_hours
     FROM per_all_people_f papf,
         per_person_names_f ppn,
         per_all_assignments_m pasm,
         gl_code_combinations gcc,
         per_periods_of_service ppos
   WHERE 1 = 1
--     AND person_number IN ('13549')       --'15370'--'10165'
     AND pasm.period_of_service_id = ppos.period_of_service_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 = ppn.person_id
     AND pasm.primary_flag = 'Y'
     AND SYSDATE BETWEEN NVL (pasm.effective_start_date, SYSDATE - 1)
                     AND NVL (pasm.effective_end_date, SYSDATE + 1)
     AND pasm.assignment_status_type = 'ACTIVE'
     AND pasm.person_id = papf.person_id
     AND pasm.default_code_comb_id = gcc.code_combination_id
     AND SYSDATE >= NVL (papf.effective_start_date, SYSDATE - 1)
     AND NVL (papf.effective_end_date, SYSDATE + 1) >=:P_From_Date   
     )) emp_util
 WHERE  (BUSINESS_UNIT in (:P_Entity) OR least(:P_Entity) is NULL)
     and (reporting_organization in (:P_Practice) OR least(:P_Practice) is NULL)
     

No comments:

Post a Comment