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)
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