[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning,tag
FROM fnd_lookup_values flv
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code
AND language = userenv('LANG')
AND view_application_id = p_view_app_id
and SECURITY_GROUP_ID = decode(substr(userenv('CLIENT_INFO'),55,1),
' ', 0,
NULL, 0,
'0', 0,
fnd_global.lookup_security_group(
FLV.LOOKUP_TYPE,FLV.VIEW_APPLICATION_ID));
L_SELECT_OUTER varchar2(3000);
L_SELECT_INNER1 varchar2(10000);
L_SELECT_INNER2 varchar2(3000);
select
max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_EXT_TRN_PLAN',TMT.tp_measurement_type_id))
DEDUCTIBLE_EXT_TRN_PLAN
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_EXT_TRN_PLAN_SA',TMT.tp_measurement_type_id))
DEDUCTIBLE_EXT_TRN_PLAN_SA
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_EXT_TRN_PLAN_VAE',TMT.tp_measurement_type_id))
DEDUCTIBLE_EXT_TRN_PLAN_VAE
,max(decode(tmt.tp_measurement_code,
'FR_OTHER_PLAN_DEDUCT_COSTS',TMT.tp_measurement_type_id))
OTHER_PLAN_DEDUCTIBLE_COSTS
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_TRAINER_SALARY',TMT.tp_measurement_type_id))
DEDUCTIBLE_TRAINER_SALARY
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_ADMIN_SALARY',TMT.tp_measurement_type_id))
DEDUCTIBLE_ADMIN_SALARY
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_RUNNING_COSTS',TMT.tp_measurement_type_id))
DEDUCTIBLE_RUNNING_COSTS
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_TRAINER_TRANSPRT',TMT.tp_measurement_type_id))
DEDUCTIBLE_TRAINER_TRANSPRT
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_TRAINER_ACCOM',TMT.tp_measurement_type_id))
DEDUCTIBLE_TRAINER_ACCOM
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_EXT_TRN_CLASS',TMT.tp_measurement_type_id))
DEDUCTIBLE_EXT_TRN_CLASS
,max(decode(tmt.tp_measurement_code,
'FR_OTHER_CLASS_DEDUCT_COST',TMT.tp_measurement_type_id))
OTHER_CLASS_DEDUCTIBLE_COST
,max(decode(tmt.tp_measurement_code,
'FR_ACTUAL_HOURS',TMT.tp_measurement_type_id)) ACTUAL_HOURS
,max(decode(tmt.tp_measurement_code,
'FR_SKILLS_ASSESSMENT',TMT.tp_measurement_type_id))
SKILLS_ASSESSMENT
,max(decode(tmt.tp_measurement_code,
'FR_VAE',TMT.tp_measurement_type_id)) VAE
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_LEARNER_SALARY',TMT.tp_measurement_type_id))
DEDUCTIBLE_LEARNER_SALARY
,max(decode(tmt.tp_measurement_code,
'FR_DEDUCT_TRN_ALLOWANCE',TMT.tp_measurement_type_id))
DEDUCTIBLE_TRN_ALLOWANCE
,max(decode(tmt.tp_measurement_code,
'FR_OTHER_LEARN_DEDUCT_INT',TMT.tp_measurement_type_id))
OTHER_LEARN_DEDUCT_COST_INT
,max(decode(tmt.tp_measurement_code,
'FR_OTHER_LEARN_DEDUCT_EXT',TMT.tp_measurement_type_id))
OTHER_LEARN_DEDUCT_COST_EXT
from ota_tp_measurement_types tmt
where TMT.business_group_id = p_bg_id
and tmt.tp_measurement_code in ('FR_DEDUCT_EXT_TRN_PLAN',
'FR_DEDUCT_EXT_TRN_PLAN_SA',
'FR_DEDUCT_EXT_TRN_PLAN_VAE',
'FR_OTHER_PLAN_DEDUCT_COSTS',
'FR_DEDUCT_TRAINER_SALARY',
'FR_DEDUCT_ADMIN_SALARY',
'FR_DEDUCT_RUNNING_COSTS',
'FR_DEDUCT_TRAINER_TRANSPRT',
'FR_DEDUCT_TRAINER_ACCOM',
'FR_DEDUCT_EXT_TRN_CLASS',
'FR_OTHER_CLASS_DEDUCT_COST',
'FR_ACTUAL_HOURS',
'FR_SKILLS_ASSESSMENT',
'FR_VAE',
'FR_DEDUCT_LEARNER_SALARY',
'FR_DEDUCT_TRN_ALLOWANCE',
'FR_OTHER_LEARN_DEDUCT_INT',
'FR_OTHER_LEARN_DEDUCT_EXT')
and ((tmt.tp_measurement_code in ('FR_DEDUCT_EXT_TRN_PLAN',
'FR_DEDUCT_EXT_TRN_PLAN_SA',
'FR_DEDUCT_EXT_TRN_PLAN_VAE',
'FR_OTHER_PLAN_DEDUCT_COSTS') and
TMT.cost_level = 'PLAN') or
(tmt.tp_measurement_code in ('FR_DEDUCT_TRAINER_SALARY',
'FR_DEDUCT_ADMIN_SALARY',
'FR_DEDUCT_RUNNING_COSTS',
'FR_DEDUCT_TRAINER_TRANSPRT',
'FR_DEDUCT_TRAINER_ACCOM',
'FR_DEDUCT_EXT_TRN_CLASS',
'FR_OTHER_CLASS_DEDUCT_COST') and
TMT.cost_level = 'EVENT') or
(tmt.tp_measurement_code in ('FR_ACTUAL_HOURS',
'FR_SKILLS_ASSESSMENT',
'FR_VAE',
'FR_DEDUCT_LEARNER_SALARY',
'FR_DEDUCT_TRN_ALLOWANCE',
'FR_OTHER_LEARN_DEDUCT_INT',
'FR_OTHER_LEARN_DEDUCT_EXT') and
TMT.cost_level = 'DELEGATE'))
and ((tmt.tp_measurement_code = 'FR_ACTUAL_HOURS' and
tmt.unit = 'N') or
(tmt.tp_measurement_code <> 'FR_ACTUAL_HOURS' and
tmt.unit = 'M'));
select
max(decode(pri_class.classification_name
,'Information',pri_class.classification_id)) inf_pri_cls_id
,max(decode(pri_class.classification_name
,'Absence',pri_class.classification_id)) abs_pri_cls_id
,max(decode(pri_class.classification_name
,'Information',sub_class.classification_id)) dif_inf_sub_cls_id
,max(decode(pri_class.classification_name
,'Absence',sub_class.classification_id)) dif_abs_sub_cls_id
from pay_element_classifications pri_class,
pay_element_classifications sub_class
where pri_class.classification_name in ('Information','Absence')
and pri_class.business_group_id is null
and pri_class.legislation_code = 'FR'
and sub_class.parent_classification_id = pri_class.classification_id
and sub_class.classification_name = 'DIF Absence : '||
pri_class.classification_name
and sub_class.business_group_id is null
and sub_class.legislation_code = 'FR';
select
greatest(comp.date_from,l_year_start) date1
,least(nvl(comp.date_to,l_year_end),l_year_end) date2
,substr(tax_office_loc.address_line_1,1,45) ad1
,substr(tax_office_loc.address_line_2,1,45) ad2
,substr(tax_office_loc.region_3,1,45) ad3
,substr(tax_office_loc.postal_code||' '||
tax_office_loc.town_or_city,1,45) ad4
,substr(comp_tl.name,1,45) ad5
,substr(comp_loc.address_line_1,1,45) ad6
,substr(ltrim(rtrim(comp_loc.address_line_2||', '||
comp_loc.region_3,', '),', '),1,45) ad7
,substr(comp_loc.postal_code||' '||
comp_loc.town_or_city,1,45) ad8
,comp_2483_info.org_information2 recette
,comp_2483_info.org_information3 dossier
,comp_2483_info.org_information4 cle
,comp_2483_info.org_information5 regime
,comp_2483_info.org_information6 impot
,substr(hq_info.org_information2,1,9) siret1
,substr(hq_info.org_information2,10,5) code
,nvl(comp_info.org_information2,hq_info.org_information3) ape
,comp_2483_info.org_information7 intermittent_and_homeworkers
,comp_2483_info.org_information8 tp_level
,comp.business_group_id bg_id
,ceil(months_between(
decode(
least(nvl(comp.date_to,l_year_end),l_year_end),
last_day(least(nvl(comp.date_to,l_year_end),l_year_end)),
least(nvl(comp.date_to,l_year_end),l_year_end),
trunc(least(nvl(comp.date_to,l_year_end),l_year_end),'MM')),
greatest(comp.date_from,l_year_start))) comp_active_mths_in_yr
from
hr_all_organization_units comp,
hr_organization_information comp_2483_info,
hr_all_organization_units tax_office,
hr_locations_all tax_office_loc,
hr_all_organization_units_TL comp_tl,
hr_locations_all comp_loc,
hr_organization_information comp_info,
hr_organization_information HQ_info
where comp.organization_id = p_company_id
and comp.date_from <= l_year_end
and (comp.date_to is null or
comp.date_to >= l_year_start)
and comp_2483_info.org_information_context(+) = 'FR_COMP_2483_INFO'
and comp_2483_info.organization_id (+) = comp.organization_id
and tax_office.organization_id(+) = comp_2483_info.org_information1
and tax_office_loc.location_id(+) = tax_office.location_id
and comp_tl.organization_id = comp.organization_id
and comp_tl.language = USERENV('LANG')
and comp_loc.location_id(+) = comp.location_id
and comp_info.org_information_context(+) = 'FR_COMP_INFO'
and comp_info.organization_id (+) = comp.organization_id
and HQ_info.organization_id (+) = comp_info.org_information5
and HQ_info.org_information_context(+) = 'FR_ESTAB_INFO';
select org_information3 reduction_chr
from hr_organization_information tng_cntrib
where tng_cntrib.organization_id = p_company_id
and tng_cntrib.org_information_context = 'FR_COMP_TRAINING_CONTRIB'
and p_effective_date_chr between tng_cntrib.org_information1
and nvl(tng_cntrib.org_information2
,p_effective_date_chr);
L_SELECT_OUTER := 'select
round((
trunc(nvl(sum(decode(emp_mth.mth_num,1 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,2 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,3 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,4 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,5 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,6 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,7 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,8 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,9 ,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,10,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,11,mth_count)),0))+
trunc(nvl(sum(decode(emp_mth.mth_num,12,mth_count)),0)))/
:num_comp_months) NOMBRE
FROM (';
L_SELECT_OUTER := 'select
emp_mth.full_name,
emp_mth.order_name,
emp_mth.employee_number,
sum(decode(emp_mth.mth_num,1 ,mth_count)) m1,
sum(decode(emp_mth.mth_num,2 ,mth_count)) m2,
sum(decode(emp_mth.mth_num,3 ,mth_count)) m3,
sum(decode(emp_mth.mth_num,4 ,mth_count)) m4,
sum(decode(emp_mth.mth_num,5 ,mth_count)) m5,
sum(decode(emp_mth.mth_num,6 ,mth_count)) m6,
sum(decode(emp_mth.mth_num,7 ,mth_count)) m7,
sum(decode(emp_mth.mth_num,8 ,mth_count)) m8,
sum(decode(emp_mth.mth_num,9 ,mth_count)) m9,
sum(decode(emp_mth.mth_num,10,mth_count)) m10,
sum(decode(emp_mth.mth_num,11,mth_count)) m11,
sum(decode(emp_mth.mth_num,12,mth_count)) m12,
sum(mth_count) EMP_TOT
from (';
l_sql := L_SELECT_OUTER||' /* emp_mth */
Select
per.full_name,
per.order_name,
per.employee_number,
month.num mth_num,
decode(
substr(hruserdt.get_table_value(org_comp.business_group_id,''FR_CIPDZ'',
''CIPDZ'',
nvl(ass.employment_category,''FR''),
month.end_date),1,1)
,''C'',decode(
sign(greatest(sign(pos.date_start- month.start_date) ,0) +
GREATEST(sign(nvl(month.end_date -
pos.actual_termination_date,0)),0))
,1,/* Starter or Leaver*/
decode(
length(scl.segment5)+length(scl.segment11)
,null,/*No work pattern; use Cal days*/
(select
to_number(hlu.lookup_code) num,
to_date(''01''||hlu.lookup_code||:p_year,''DDMMYYYY'') start_date,
last_day(to_date(''01''||hlu.lookup_code||:p_year,''DDMMYYYY''))
end_date
from hr_lookups hlu
where lookup_type = ''MONTH_OF_YEAR'') month,
per_contracts_f ctr,
hr_soft_coding_keyflex scl,
per_periods_of_service pos,
per_all_people_f per
where org_comp.organization_id = :p_company_id
and org_comp.date_from <= :p_year_end
and (org_comp.date_to is null or
org_comp.date_to >= :p_year_start)
and org_info_estab.org_information1 = org_comp.organization_id
and org_info_estab.org_information_context = ''FR_ESTAB_INFO''
and org_info_estab.organization_id = org_estab.organization_id
and org_estab.organization_id = ass.establishment_id
and org_estab.date_from <= :p_year_end
and (org_estab.date_to is null or
org_estab.date_to >= :p_year_start)
and ass.primary_flag = ''Y''
/* rough filter on asg dates: */
and ass.effective_start_date <= :p_year_end
and ass.effective_end_date >= :p_year_start
/* Get 1 asg row for each month. Assumes final close would be at end of
month of ATD or beyond */
and month.end_date between ass.effective_start_date
and ass.effective_end_date
and month.end_date between org_comp.date_from
and nvl(org_comp.date_to,:p_year_end)
and month.end_date between org_estab.date_from
and nvl(org_estab.date_to,:p_year_end)
and pos.period_of_service_id = ass.period_of_service_id
/* filter out months where no overlapping active period of service */
/* Also exclude CWKs; they wont have a period of service*/
L_SELECT_OUTER := 'select
nvl(sum(decode(emp_cat,2,mcnt)),0) b11
,nvl(sum(decode(emp_cat,2,fcnt)),0) b12
,count(distinct decode(emp_cat,2,mtrn_id)) b13
,count(distinct decode(emp_cat,2,ftrn_id)) b14
,round(nvl(sum(decode(emp_cat,2,trn_hrs)),0)) b15
,count(distinct decode(emp_cat,2,dif_trn_id)) b16
,round(nvl(sum(decode(emp_cat,2,dif_hrs)),0)) b17
,round(nvl(sum(decode(emp_cat,2,dif_bal)),0)) b18
,nvl(sum(decode(emp_cat,3,mcnt)),0) b21
,nvl(sum(decode(emp_cat,3,fcnt)),0) b22
,count(distinct decode(emp_cat,3,mtrn_id)) b23
,count(distinct decode(emp_cat,3,ftrn_id)) b24
,round(nvl(sum(decode(emp_cat,3,trn_hrs)),0)) b25
,count(distinct decode(emp_cat,3,dif_trn_id)) b26
,round(nvl(sum(decode(emp_cat,3,dif_hrs)),0)) b27
,round(nvl(sum(decode(emp_cat,3,dif_bal)),0)) b28
,nvl(sum(decode(emp_cat,4,mcnt)),0) b31
,nvl(sum(decode(emp_cat,4,fcnt)),0) b32
,count(distinct decode(emp_cat,4,mtrn_id)) b33
,count(distinct decode(emp_cat,4,ftrn_id)) b34
,round(nvl(sum(decode(emp_cat,4,trn_hrs)),0)) b35
,count(distinct decode(emp_cat,4,dif_trn_id)) b36
,round(nvl(sum(decode(emp_cat,4,dif_hrs)),0)) b37
,round(nvl(sum(decode(emp_cat,4,dif_bal)),0)) b38
,nvl(sum(decode(emp_cat,5,mcnt)),0) b41
,nvl(sum(decode(emp_cat,5,fcnt)),0) b42
,count(distinct decode(emp_cat,5,mtrn_id)) b43
,count(distinct decode(emp_cat,5,ftrn_id)) b44
,round(nvl(sum(decode(emp_cat,5,trn_hrs)),0)) b45
,count(distinct decode(emp_cat,5,dif_trn_id)) b46
,round(nvl(sum(decode(emp_cat,5,dif_hrs)),0)) b47
,round(nvl(sum(decode(emp_cat,5,dif_bal)),0)) b48
FROM (
';
L_SELECT_INNER1:= '
decode(substr(job.job_information1,1,1)
,''5'',3
,''4'',4
,''3'',5
,''6'',2) emp_cat
,';
L_SELECT_OUTER := 'select
full_name
,order_name
,employee_number
,sum(mcnt) mcnt
,sum(fcnt) fcnt
,count(distinct mtrn_id) mtrn
,count(distinct ftrn_id) ftrn
,sum(trn_hrs) trn_hrs
,count(distinct dif_trn_id) dif_trn
,sum(dif_hrs) dif_hrs
,sum(dif_bal) dif_bal
FROM (
';
L_SELECT_INNER1:= '
per.full_name full_name
,per.order_name order_name
,per.employee_number employee_number
,';
l_sql := L_SELECT_OUTER||'SELECT /* a and b */'||L_SELECT_INNER1||
'decode(per.sex, ''M'',1, 0) mcnt
,decode(per.sex, ''F'',1, 0) fcnt
,to_number(NULL) mtrn_id
,to_number(NULL) ftrn_id
,to_number(NULL) trn_hrs
,to_number(NULL) dif_trn_id
,to_number(NULL) dif_hrs
,to_number(NULL) dif_bal
FROM hr_all_organization_units comp,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_jobs job,
per_periods_of_service ppos,
per_all_people_f per
WHERE comp.organization_id = :p_company_id
AND comp.date_from <= :p_year_end
AND (comp.date_to IS NULL OR
comp.date_to >= :p_year_start)
AND estab_info.org_information1 = to_char(comp.organization_id)
AND estab_info.org_information_context = ''FR_ESTAB_INFO''
AND estab.organization_id = estab_info.organization_id
AND estab.date_from <= :p_year_end
AND (estab.date_to IS NULL OR
estab.date_to >= :p_year_start)
AND estab.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
AND :p_comp_end BETWEEN ass.effective_start_date
AND ass.effective_end_date
AND ass.job_id = job.job_id
AND job.job_information_category = ''FR'' '||L_WHERE_INNER1||'
AND ass.person_id = per.person_id
AND :p_comp_end BETWEEN per.effective_start_date
AND per.effective_end_date
/* exclude contingent workers by joining with periods of service
and also check for active employees */
AND ppos.period_of_service_id = ass.period_of_service_id
and ppos.date_start <= :p_comp_end
and (ppos.actual_termination_date is null or
ppos.actual_termination_date >= :p_comp_end)
UNION ALL
SELECT /* c, d, e, f, and g absences */'||L_SELECT_INNER1||
'TO_NUMBER(NULL) mcnt
,TO_NUMBER(NULL) fcnt
,DECODE(pabs.abs_information18 /* Within Training Plan */
,''N'',DECODE(per.sex, ''M'', per.person_id)) mtrn_id
,DECODE(pabs.abs_information18 /* Within Training Plan */
,''N'',DECODE(per.sex, ''F'', per.person_id)) ftrn_id
,DECODE(pabs.abs_information18 /* Within Training Plan */
,''N'',nvl(pabs.absence_hours,0)) trn_hrs
,DECODE(sub_class.classification_id
,NULL,TO_NUMBER(NULL)
,decode(pabs.abs_information1
,''OTHER'',per.person_id)) dif_trn_id
,DECODE(sub_class.classification_id
,NULL,TO_NUMBER(NULL)
,decode(pabs.abs_information1
,''OTHER'',nvl(pabs.absence_hours,0))) dif_hrs
,to_number(NULL) dif_bal
FROM hr_all_organization_units comp,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_jobs job,
per_all_people_f per,
per_absence_attendances pabs,
per_absence_attendance_types pabt,
per_contracts_f con,
pay_input_values_f piv,
pay_element_types_f ele,
pay_sub_classification_rules_f sub_class
WHERE comp.organization_id = :p_company_id
AND comp.date_from <= :p_year_end
AND (comp.date_to IS NULL OR
comp.date_to >= :p_year_start)
AND estab_info.org_information1 = to_char(comp.organization_id)
AND estab_info.org_information_context = ''FR_ESTAB_INFO''
AND estab.organization_id = estab_info.organization_id
AND estab.date_from <= :p_year_end
AND (estab.date_to IS NULL OR
estab.date_to >= :p_year_start)
AND estab.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
/* rough filter on asg dates: */
and ass.effective_start_date <= :p_comp_end
and ass.effective_end_date >= :p_comp_start
AND ass.job_id = job.job_id
AND job.job_information_category = ''FR'' '||L_WHERE_INNER1||'
AND ass.person_id = per.person_id
AND :p_comp_end BETWEEN per.effective_start_date
AND per.effective_end_date
AND ass.person_id = pabs.person_id
AND pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
AND pabs.date_end BETWEEN ass.effective_start_date
AND ass.effective_end_date
AND pabs.date_end BETWEEN :p_comp_start
AND :p_comp_end
AND pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
AND pabt.absence_category = ''TRAINING_ABSENCE''
AND con.contract_id = ass.contract_id
AND pabs.date_end BETWEEN con.effective_start_date
AND con.effective_end_date
AND pabt.input_value_id = piv.input_value_id (+)
AND pabt.date_effective BETWEEN piv.effective_start_date (+)
AND piv.effective_end_date (+)
AND ele.element_type_id(+) = piv.element_type_id
AND piv.effective_start_date BETWEEN ele.effective_start_date (+)
AND ele.effective_end_date (+)
AND sub_class.element_type_id(+) = ele.element_type_id
AND ele.effective_start_date BETWEEN sub_class.effective_start_date(+)
AND sub_class.effective_end_date (+)
AND sub_class.classification_id(+) = decode(ele.classification_id
,:inf_pri_cls,:dif_inf_sub_cls
,:abs_pri_cls,:dif_abs_sub_cls)
AND con.type NOT IN (''APPRENTICESHIP'',
''ADAPTATION'',
''QUALIFICATION'',
''PROFESSIONALISATION'')
AND ((/*c, d and e*/
pabs.abs_information1 IN (''VAE'',
''OTHER'',
''SKILLS_ASSESSMENT'',
''PP'') AND
/*Not Within Training Plan */
pabs.abs_information18 = ''N''/* nullable */) OR
(/*f and g*/
pabs.abs_information1 = ''OTHER'' AND
/* DIF absences only */
sub_class.classification_id IS NOT NULL))
UNION ALL
SELECT /* c, d, and e OTA costs */'||L_SELECT_INNER1||
'TO_NUMBER(NULL) mcnt
,TO_NUMBER(NULL) fcnt
,DECODE(per.sex, ''M'', per.person_id) mtrn_id
,DECODE(per.sex, ''F'', per.person_id) ftrn_id
,decode(tmt.tp_measurement_code
,''FR_ACTUAL_HOURS'',tpc.amount
,nvl(fnd_number.canonical_to_number(tpc.tp_cost_information3)
,0)) trn_hrs
,to_number(NULL) dif_trn_id
,to_number(NULL) dif_hrs
,to_number(NULL) dif_bal
from
hr_all_organization_units comp,
hr_organization_information tp_org_info,
hr_all_organization_units org,
ota_training_plans TP,
per_time_periods PTP,
ota_training_plan_costs TPC,
ota_tp_measurement_types TMT,
ota_delegate_bookings ODB,
per_all_people_f PER,
ota_events EVT,
per_all_assignments_f ass,
per_jobs job,
per_contracts_f con
where comp.organization_id = :p_company_id
and comp.date_from <= :p_year_end
and (comp.date_to is null or
comp.date_to >= :p_year_start) '
||L_WHERE_TP_ORG||'
and org.organization_id = tp_org_info.organization_id
and org.date_from <= :p_year_end
and (org.date_to is null or
org.date_to >= :p_year_start)
and org.organization_id = TP.organization_id
/*and TP.plan_status_type_id <> ''CANCELLED''*/
and TP.time_period_id = PTP.time_period_id
and PTP.period_type = ''Year''
and PTP.start_date = :p_year_start
and TP.training_plan_id = TPC.training_plan_id
and TPC.tp_measurement_type_id = TMT.tp_measurement_type_id
and TMT.business_group_id = org.business_group_id
and TPC.tp_measurement_type_id IN (:ACTUAL_HOURS,
:SKILLS_ASSESSMENT,
:VAE)
AND TPC.booking_id = ODB.booking_id
and ODB.delegate_person_id = PER.person_id
and :p_comp_end between PER.effective_start_date
AND PER.effective_end_date
AND ODB.event_id = EVT.event_id
AND ass.person_id = per.person_id
AND ass.primary_flag = ''Y''
and evt.course_end_date between ass.effective_start_date
and ass.effective_end_date
AND ass.job_id = job.job_id
AND job.job_information_category= ''FR'' '||L_WHERE_INNER1||'
AND con.contract_id = ass.contract_id
AND evt.course_end_date BETWEEN con.effective_start_date
AND con.effective_end_date
AND con.type NOT IN (''APPRENTICESHIP'',
''ADAPTATION'',
''QUALIFICATION'',
''PROFESSIONALISATION'')
UNION ALL
SELECT /* DIF balance */'||L_SELECT_INNER1||
'to_number(NULL) mcnt
,to_number(NULL) fcnt
,to_number(NULL) mtrn_id
,to_number(NULL) ftrn_id
,to_number(NULL) trn_hrs
,to_number(NULL) dif_trn_id
,to_number(NULL) dif_hrs
,otfr2483.get_dif_balance(ass.assignment_id,
acc.accrual_plan_id,
ass.payroll_id,
comp.business_group_id,
:p_comp_end) dif_bal
FROM hr_all_organization_units comp,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_jobs job,
pay_element_entries_f ent,
pay_accrual_plans acc,
pay_input_values_f piv,
pay_sub_classification_rules_f sub_class,
per_all_people_f per
WHERE comp.organization_id = :p_company_id
AND comp.date_from <= :p_year_end
AND (comp.date_to IS NULL OR
comp.date_to >= :p_year_start)
AND estab_info.org_information1 = to_char(comp.organization_id)
AND estab_info.org_information_context = ''FR_ESTAB_INFO''
AND estab.organization_id = estab_info.organization_id
AND estab.date_from <= :p_year_end
AND (estab.date_to IS NULL OR
estab.date_to >= :p_year_start)
AND estab.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
AND :p_comp_end BETWEEN ass.effective_start_date
AND ass.effective_end_date
AND ass.job_id = job.job_id
AND job.job_information_category = ''FR'' '||L_WHERE_INNER1||'
AND ass.assignment_id = ent.assignment_id
and :p_comp_end BETWEEN ent.effective_start_date
AND ent.effective_end_date
and ent.element_type_id = acc.accrual_plan_element_type_id
AND acc.business_group_id = comp.business_group_id
AND piv.input_value_id = acc.pto_input_value_id
and :p_comp_end BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND sub_class.element_type_id = piv.element_type_id
AND :p_comp_end BETWEEN sub_class.effective_start_date
AND sub_class.effective_end_date
AND sub_class.classification_id IN (:dif_inf_sub_cls,
:dif_abs_sub_cls)
AND ass.person_id = per.person_id
AND :p_comp_end BETWEEN per.effective_start_date
AND per.effective_end_date'||L_ORDER_BY;
L_SELECT_OUTER := 'select
count(distinct per.person_id) c1
,round(nvl(sum(pabs.absence_hours),0)) c2
';
L_SELECT_INNER1:= null;
L_SELECT_OUTER := null;
L_SELECT_INNER1:= 'select
per.order_name order_name
,per.full_name full_name
,per.employee_number employee_number
,pabs.date_start abs_start
,pabs.date_end abs_end
,nvl(pabs.absence_hours,0) abs_hrs
';
l_sql := L_SELECT_OUTER||L_SELECT_INNER1||'FROM
hr_all_organization_units comp,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_all_people_f per,
per_absence_attendances pabs,
per_absence_attendance_types pabt
WHERE comp.organization_id = :p_company_id
AND comp.date_from <= :p_year_end
AND (comp.date_to IS NULL OR
comp.date_to >= :p_year_start)
AND estab_info.org_information1 = to_char(comp.organization_id)
AND estab_info.org_information_context = ''FR_ESTAB_INFO''
AND estab.organization_id = estab_info.organization_id
AND estab.date_from <= :p_year_end
AND (estab.date_to IS NULL OR
estab.date_to >= :p_year_start)
AND estab.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
/* rough filter on asg dates: */
and ass.effective_start_date <= :p_comp_end
and ass.effective_end_date >= :p_comp_start
AND ass.person_id = per.person_id
AND :p_comp_end BETWEEN per.effective_start_date
AND per.effective_end_date
AND per.person_id = pabs.person_id
AND pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
AND nvl(pabs.date_end,pabs.date_start) BETWEEN ass.effective_start_date
AND ass.effective_end_date
AND pabs.date_start <= :p_comp_end
AND (pabs.date_end IS NULL OR
pabs.date_end >= :p_comp_start)
AND pabs.abs_information1 = ''PP''
AND pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
AND pabt.absence_category = ''TRAINING_ABSENCE'' '||L_ORDER_BY;
L_SELECT_OUTER := 'select
count(distinct person_id) c3
,round(nvl(sum(out_hrs),0)) c4
,round(nvl(sum(trn_al),0)) C121
from (
';
L_SELECT_INNER1:= 'select
per.person_id
,fnd_number.canonical_to_number(tp_cost_hrs.tp_cost_information4) out_hrs
,decode(tp_cost.currency_code
,''EUR'',tp_cost.amount
,hr_currency_pkg.convert_amount_sql(
tp_cost.currency_code
,''EUR''
,sysdate
,tp_cost.amount
,:CURRENCY_RATE_TYPE)) trn_al
';
L_SELECT_INNER2:= 'select
per.person_id
,fnd_number.canonical_to_number(pabs.abs_information20) out_hrs
,decode(bg_info.org_information10
,''EUR'',fnd_number.canonical_to_number(pabs.abs_information22)
,hr_currency_pkg.convert_amount_sql(
bg_info.org_information10
,''EUR''
,sysdate
,nvl(fnd_number.canonical_to_number(pabs.abs_information22),0)
,:CURRENCY_RATE_TYPE)) trn_al
';
L_SELECT_OUTER := null;
L_SELECT_INNER1:= 'select
per.order_name order_name
,per.full_name full_name
,per.employee_number employee_number
,evt.course_start_date trn_start
,evt.course_end_date trn_end
,tp.name plan
,evt_tl.title class
,hlu_legal.meaning legal_cat
,tp_cost_hrs.amount act_hrs
,nvl(tp_cost_hrs.tp_cost_information4,''0'') out_hrs
,tp_cost.amount trn_al
,tp_cost.currency_code trn_al_cc
';
L_SELECT_INNER2:= 'select
per.order_name order_name
,per.full_name full_name
,per.employee_number employee_number
,pabs.date_start trn_start
,pabs.date_end trn_end
,null plan
,null class
,hlu_legal.meaning legal_cat
,nvl(pabs.absence_hours,0) act_hrs
,nvl(pabs.abs_information20,''0'') out_hrs
,fnd_number.canonical_to_number(pabs.abs_information22) trn_al
,bg_info.org_information10 trn_al_cc
';
l_sql := L_SELECT_OUTER||L_SELECT_INNER1||'FROM
hr_all_organization_units comp,
hr_organization_information tp_org_info,
hr_all_organization_units org,
ota_training_plans tp,
per_time_periods ptp,
ota_training_plan_costs tp_cost,
ota_training_plan_costs tp_cost_hrs,
ota_delegate_bookings delegate,
per_all_people_f per,
ota_events evt,
ota_events_tl evt_tl,
hr_lookups hlu_legal
WHERE comp.organization_id = :p_company_id
AND comp.date_from <= :p_end_year
AND (comp.date_to IS NULL OR
comp.date_to >= :p_start_year) '
||L_WHERE_TP_ORG||'
AND org.organization_id = tp_org_info.organization_id
AND org.date_from <= :p_end_year
AND (org.date_to IS NULL OR
org.date_to >= :p_start_year)
AND tp.organization_id = org.organization_id
AND ptp.time_period_id = tp.time_period_id
AND ptp.period_type = ''Year''
AND PTP.start_date = :p_start_year
AND tp.training_plan_id = tp_cost.training_plan_id
AND tp_cost.tp_measurement_type_id = :DEDUCTIBLE_TRN_ALLOWANCE
AND tp_cost.training_plan_id = tp_cost_hrs.training_plan_id
AND tp_cost_hrs.booking_id = tp_cost.booking_id
AND tp_cost_hrs.tp_measurement_type_id= :ACTUAL_HOURS
AND tp_cost.booking_id = delegate.booking_id
AND delegate.delegate_person_id = per.person_id
AND :p_end_comp BETWEEN PER.effective_start_date
AND PER.effective_end_date
AND delegate.event_id = evt.event_id
and EVT_tl.event_id = EVT.event_id
and EVT_tl.language = userenv(''LANG'')
AND hlu_legal.lookup_type(+) = ''FR_LEGAL_TRG_CATG''
AND hlu_legal.lookup_code(+) = tp_cost_hrs.tp_cost_information3
UNION ALL
'||L_SELECT_INNER2||'FROM
hr_all_organization_units COMP,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_all_people_f per,
per_absence_attendances pabs,
per_absence_attendance_types pabt,
hr_lookups hlu_legal,
hr_organization_information bg_info
WHERE comp.organization_id = :p_company_id
AND comp.date_from <= :p_year_end
AND (comp.date_to IS NULL OR
comp.date_to >= :p_year_start)
AND estab_info.org_information1 = to_char(comp.organization_id)
AND estab_info.org_information_context = ''FR_ESTAB_INFO''
AND estab.organization_id = estab_info.organization_id
AND estab.date_from <= :p_year_end
AND (estab.date_to IS NULL OR
estab.date_to >= :p_year_start)
AND estab.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
/* rough filter on asg dates: */
and ass.effective_start_date <= :p_comp_end
and ass.effective_end_date >= :p_comp_start
AND ass.person_id = per.person_id
AND :p_comp_end BETWEEN per.effective_start_date
AND per.effective_end_date
AND per.person_id = pabs.person_id
AND pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
AND pabs.date_end BETWEEN ass.effective_start_date
AND ass.effective_end_date
AND pabs.date_end BETWEEN :p_comp_start
AND :p_comp_end
/*Not Within Training Plan */
AND pabs.abs_information18 = ''N''/* nullable */
AND pabs.abs_information22 <> ''0''
AND pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
AND pabt.absence_category = ''TRAINING_ABSENCE''
AND hlu_legal.lookup_code(+) = pabs.abs_information19 /*NULLABLE*/
AND hlu_legal.lookup_type(+) = ''FR_LEGAL_TRG_CATG''
AND bg_info.organization_id = comp.business_group_id
and bg_info.org_information_context = ''Business Group Information'' '||
L_ORDER_BY;
L_SELECT_OUTER := 'select
round(nvl(sum(decode(trn_type,''SA'', num_courses)),0)) c5,
round(nvl(sum(decode(trn_type,''VAE'',num_courses)),0)) c6
from (
';
L_SELECT_INNER1:= 'select
decode(meas_type.tp_measurement_code,
''FR_DEDUCT_EXT_TRN_PLAN_VAE'',''VAE'',
''FR_DEDUCT_EXT_TRN_PLAN_SA'', ''SA'',
''FR_SKILLS_ASSESSMENT'', ''SA'',
''FR_VAE'', ''VAE'') trn_type
,decode(tp_cost.booking_id,
NULL,fnd_number.canonical_to_number(tp_cost.tp_cost_information1),
1) num_courses
';
L_SELECT_INNER2:= 'select
decode(pabs.abs_information1,
''SKILLS_ASSESSMENT'',''SA'',
''VAE'',''VAE'') trn_type
,1 num_courses
';
L_SELECT_OUTER := null;
L_SELECT_INNER1:= 'select
per.order_name order_name
,per.full_name full_name
,per.employee_number employee_number
,event.course_start_date trn_start
,event.course_end_date trn_end
,tp.name plan
,event_tl.title class
,decode(tp_cost.booking_id
,NULL,fnd_number.canonical_to_number(tp_cost.tp_cost_information1)
,1) num_courses
';
L_SELECT_INNER2:= 'select
per.order_name order_name
,per.full_name full_name
,per.employee_number employee_number
,pabs.date_start trn_start
,pabs.date_end trn_end
,null plan
,null class
,1 num_courses
';
l_sql := L_SELECT_OUTER||L_SELECT_INNER1||'FROM
hr_all_organization_units comp,
hr_organization_information tp_org_info,
hr_all_organization_units org,
ota_training_plans tp,
per_time_periods ptp,
ota_training_plan_costs tp_cost,
ota_tp_measurement_types meas_type,
ota_delegate_bookings delegate,
ota_events event,
ota_events_tl event_tl,
per_all_people_f per
WHERE comp.organization_id = :p_company_id
AND comp.date_from <= :p_end_year
AND (comp.date_to IS NULL OR
comp.date_to >= :p_start_year) '
||L_WHERE_TP_ORG||'
AND org.organization_id = tp_org_info.organization_id
AND org.date_from <= :p_end_year
AND (org.date_to IS NULL OR
org.date_to >= :p_start_year)
AND tp.organization_id = org.organization_id
AND ptp.time_period_id = tp.time_period_id
AND ptp.period_type = ''Year''
AND ptp.start_date = :p_start_year
AND tp.training_plan_id = tp_cost.training_plan_id
AND ((tp_cost.tp_measurement_type_id IN (:FR_SKILLS_ASSESSMENT,:FR_VAE) AND
meas_type.cost_level = ''DELEGATE'' AND
tp_cost.tp_cost_information1 = ''EMPLOYER'') OR
(tp_cost.tp_measurement_type_id IN (:FR_DEDUCTIBLE_EXT_TRN_PLAN_SA,
:FR_DEDUCTIBLE_EXT_TRN_PLAN_VAE) AND
meas_type.cost_level = ''PLAN'' AND
tp_cost.tp_cost_information1 <> ''0''))
AND tp_cost.tp_measurement_type_id = meas_type.tp_measurement_type_id
AND meas_type.unit = ''M''
AND tp_cost.information_category =''FR_''||meas_type.tp_measurement_code
AND tp_cost.booking_id = delegate.booking_id(+)
AND delegate.delegate_person_id = per.person_id(+)
AND :p_comp_end BETWEEN per.effective_start_date(+)
AND per.effective_end_date(+)
AND delegate.event_id = event.event_id(+)
AND event_tl.event_id(+) = event.event_id
AND event_tl.language(+) = userenv (''LANG'')
UNION ALL
'||L_SELECT_INNER2||'FROM
hr_all_organization_units COMP,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_all_people_f per,
per_absence_attendances pabs,
per_absence_attendance_types pabt
WHERE comp.organization_id = :p_company_id
AND comp.date_from <= :p_year_end
AND (comp.date_to IS NULL OR
comp.date_to >= :p_year_start)
AND estab_info.org_information_context = ''FR_ESTAB_INFO''
AND estab_info.org_information1 = to_char(comp.organization_id)
AND estab.organization_id = estab_info.organization_id
AND estab.date_from <= :p_year_end
AND (estab.date_to IS NULL OR
estab.date_to >= :p_year_start)
AND estab.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
/* rough filter on asg dates: */
and ass.effective_start_date <= :p_comp_end
and ass.effective_end_date >= :p_comp_start
AND ass.person_id = per.person_id
AND :p_comp_end BETWEEN per.effective_start_date
AND per.effective_end_date
AND per.person_id = pabs.person_id
AND pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
AND pabs.date_end BETWEEN ass.effective_start_date
AND ass.effective_end_date
AND pabs.date_end BETWEEN :p_comp_start
AND :p_comp_end
/*Not Within Training Plan */
AND pabs.abs_information18 = ''N''/* nullable */
/* include some training categories */
AND pabs.abs_information1 IN (:SKILLS_ASSESSMENT,:VAE)
/*and pabs.abs_information3 = ota_pv.vendor_id Training provider*/
AND pabs.abs_information5 = ''EMPLOYER'' /* Subsidized type */
AND pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
AND pabt.absence_category = ''TRAINING_ABSENCE'''||L_ORDER_BY;
L_SELECT_OUTER := 'select round(nvl(sum(tot),0)) C91 from (';
L_SELECT_INNER1:= 'select sum(decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))) tot ';
L_SELECT_INNER2:= 'select
sum(decode(nvl(pabs.abs_information8,bg_info.org_information10)
,''EUR'',fnd_number.canonical_to_number(pabs.abs_information11)
,hr_currency_pkg.convert_amount_sql(
nvl(pabs.abs_information8,bg_info.org_information10)
,''EUR''
,sysdate
,nvl(fnd_number.canonical_to_number(pabs.abs_information11),0)
,:CURR_RATE_TYPE))) tot
';
L_SELECT_OUTER := null;
L_SELECT_INNER1:= 'select
costs.full_name
,costs.order_name
,costs.employee_number
,decode(costs.full_name,
null,to_date(null),
evt.course_start_date) trn_start
,decode(costs.full_name,
null,to_date(null),
evt.course_end_date) trn_end
,EVT_tl.title class_name
,costs.plan_name plan_name
,costs.trn_sal
,costs.admin_sal
,costs.running_costs
,costs.trn_tran
,costs.trn_accom
,costs.other
,costs.emp_tot
from
(select /*+ORDERED*/
PER.full_name full_name
,PER.order_name order_name
,PER.employee_number employee_number
,nvl(odb.event_id,tpc.event_id) event_id
,tp.name plan_name
,sum(decode(TMT.tp_measurement_code
,''FR_DEDUCT_TRAINER_SALARY''
,decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))
,0)) trn_sal
,sum(decode(TMT.tp_measurement_code
,''FR_DEDUCT_ADMIN_SALARY''
,decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))
,0)) admin_sal
,sum(decode(TMT.tp_measurement_code
,''FR_DEDUCT_RUNNING_COSTS''
,decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))
,0)) running_costs
,sum(decode(TMT.tp_measurement_code
,''FR_DEDUCT_TRAINER_TRANSPRT''
,decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))
,0)) trn_tran
,sum(decode(TMT.tp_measurement_code
,''FR_DEDUCT_TRAINER_ACCOM''
,decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))
,0)) trn_accom
,sum(decode(TMT.tp_measurement_code
,''FR_OTHER_CLASS_DEDUCT_COST''
,decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))
,0)) other
,sum(decode(TMT.tp_measurement_code
,''FR_OTHER_LEARN_DEDUCT_INT''
,decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))
,0)) emp_tot
';
L_SELECT_INNER2:= 'select
per.full_name
,per.order_name
,per.employee_number
,pabs.date_start trn_start
,pabs.date_end trn_end
,null class_name
,null plan_name
,null trn_sal
,null admin_sal
,null running_costs
,null trn_tran
,null trn_accom
,null other
,decode(nvl(pabs.abs_information8,bg_info.org_information10)
,''EUR'',fnd_number.canonical_to_number(pabs.abs_information11)
,hr_currency_pkg.convert_amount_sql(
nvl(pabs.abs_information8,bg_info.org_information10)
,''EUR''
,sysdate
,nvl(fnd_number.canonical_to_number(pabs.abs_information11),0)
,:CURR_RATE_TYPE)) emp_tot
';
l_sql := L_SELECT_OUTER||L_SELECT_INNER1||' from
hr_all_organization_units comp,
hr_organization_information tp_org_info,
hr_all_organization_units org,
ota_training_plans TP,
per_time_periods PTP,
ota_training_plan_costs TPC,
ota_tp_measurement_types TMT,
ota_delegate_bookings ODB,
per_all_people_f PER
where comp.organization_id = :p_company_id
and comp.date_from <= :p_year_end
and (comp.date_to is null or
comp.date_to >= :p_year_start) '
||L_WHERE_TP_ORG||'
and org.organization_id = tp_org_info.organization_id
and org.date_from <= :p_year_end
and (org.date_to is null or
org.date_to >= :p_year_start)
and org.organization_id = TP.organization_id
/*and TP.plan_status_type_id <> ''CANCELLED''*/
and TP.time_period_id = PTP.time_period_id
and PTP.period_type = ''Year''
and PTP.start_date = :p_year_start
and TP.training_plan_id = TPC.training_plan_id
and TPC.tp_measurement_type_id = TMT.tp_measurement_type_id
and TMT.business_group_id = org.business_group_id
and ((TPC.tp_measurement_type_id in (:DEDUCTIBLE_TRAINER_SALARY,
:DEDUCTIBLE_ADMIN_SALARY,
:DEDUCTIBLE_RUNNING_COSTS,
:DEDUCTIBLE_TRAINER_TRANSPRT,
:DEDUCTIBLE_TRAINER_ACCOM,
:OTHER_CLASS_DEDUCTIBLE_COST) AND
TMT.cost_level = ''EVENT'') or
(TPC.tp_measurement_type_id= :OTHER_LEARN_DEDUCT_COST_INT AND
TMT.cost_level = ''DELEGATE''))
AND TMT.unit = ''M''
AND TPC.booking_id = ODB.booking_id(+)
and ODB.delegate_person_id = PER.person_id(+)
and :p_comp_end between PER.effective_start_date(+)
AND PER.effective_end_date (+) '||
L_GROUP_INNER1||' UNION ALL '||L_SELECT_INNER2||' from
hr_all_organization_units comp,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_all_people_f per,
per_absence_attendances pabs,
per_absence_attendance_types pabt,
hr_organization_information bg_info
where comp.organization_id = :p_company_id
and comp.date_from <= :p_year_end
and (comp.date_to is null or
comp.date_to >= :p_year_start)
and estab_info.org_information_context = ''FR_ESTAB_INFO''
and estab_info.org_information1 = to_char(comp.organization_id)
and estab.organization_id = estab_info.organization_id
and estab.date_from <= :p_year_end
and (estab.date_to is null or
estab.date_to >= :p_year_start)
and estab.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
/* rough filter on asg dates: */
and ass.effective_start_date <= :p_comp_end
and ass.effective_end_date >= :p_comp_start
and ass.person_id = per.person_id
and :p_comp_end between per.effective_start_date
and per.effective_end_date
and per.person_id = pabs.person_id
and pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
and pabs.date_end between ass.effective_start_date
and ass.effective_end_date
and pabs.date_end between :p_comp_start
and :p_comp_end
/*Not Within Training Plan*/
and pabs.abs_information18 = ''N''/* nullable */
/* Training leave category */
and (pabs.abs_information1 is null or
pabs.abs_information1 not in (''TRAINING_CREDIT'',
''TRAINING_LEAVE''))
and pabs.abs_information3 is null /* Training provider */
and pabs.abs_information5 = ''EMPLOYER'' /* Subsidized type */
and pabs.abs_information11 <> ''0''
and pabs.absence_attendance_type_id= pabt.absence_attendance_type_id
and pabt.absence_category = ''TRAINING_ABSENCE''
AND bg_info.organization_id = comp.business_group_id
and bg_info.org_information_context = ''Business Group Information'' '||
L_ORDER_BY;
L_SELECT_OUTER := 'select
round(nvl(sum(decode(trn_type,''CONTRACTED'',trn_cost)),0)) x1,
round(nvl(sum(decode(trn_type,''SA'', trn_cost)),0)) x2,
round(nvl(sum(decode(trn_type,''VAE'', trn_cost)),0)) x3
from (
';
L_SELECT_INNER1:= 'select
decode(TMT.tp_measurement_code,
''FR_DEDUCT_EXT_TRN_PLAN_VAE'',''VAE'',
''FR_DEDUCT_EXT_TRN_PLAN_SA'', ''SA'',
''FR_SKILLS_ASSESSMENT'', ''SA'',
''FR_VAE'', ''VAE'',
''CONTRACTED'') trn_type
,decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE)) trn_cost
';
L_SELECT_INNER2:= 'select
decode(pabs.abs_information1,
''SKILLS_ASSESSMENT'',''SA'',
''VAE'', ''VAE'',
''CONTRACTED'') trn_type
,decode(nvl(pabs.abs_information8,bg_info.org_information10)
,''EUR'',fnd_number.canonical_to_number(pabs.abs_information11)
,hr_currency_pkg.convert_amount_sql(
nvl(pabs.abs_information8,bg_info.org_information10)
,''EUR''
,sysdate
,nvl(fnd_number.canonical_to_number(pabs.abs_information11),0)
,:CURR_RATE_TYPE)) trn_cost
';
L_SELECT_OUTER := null;
L_SELECT_INNER1:= 'select
costs.full_name
,costs.order_name
,costs.employee_number
,decode(costs.full_name,
null,to_date(null),
evt.course_start_date) trn_start
,decode(costs.full_name,
null,to_date(null),
evt.course_end_date) trn_end
,EVT_tl.title class_name
,ota_pv.vendor_name supplier_name
,costs.plan_name plan_name
,costs.trn_cost trn_cost
,costs.trn_cost_cc trn_cost_cc
from
(select /*+ORDERED*/
PER.full_name full_name
,PER.order_name order_name
,PER.employee_number employee_number
,nvl(ODB.event_id,TPC.event_id) EVENT_ID
,tp.name plan_name
,tpc.amount trn_cost
,tpc.currency_code trn_cost_cc
';
L_SELECT_INNER2:= 'select
per.full_name
,per.order_name
,per.employee_number
,pabs.date_start trn_start
,pabs.date_end trn_end
,null class_name
,ota_pv.vendor_name supplier_name
,null plan_name
,nvl(fnd_number.canonical_to_number(pabs.abs_information11),0) trn_cost
,nvl(pabs.abs_information8,bg_info.org_information10) trn_cost_cc
';
l_sql := L_SELECT_OUTER||L_SELECT_INNER1||' from
hr_all_organization_units comp,
hr_organization_information tp_org_info,
hr_all_organization_units org,
ota_training_plans TP,
per_time_periods PTP,
ota_training_plan_costs TPC,
ota_tp_measurement_types TMT,
ota_delegate_bookings ODB,
per_all_people_f PER
where comp.organization_id = :p_company_id
and comp.date_from <= :p_year_end
and (comp.date_to is null or
comp.date_to >= :p_year_start) '
||L_WHERE_TP_ORG||'
and org.organization_id = tp_org_info.organization_id
and org.date_from <= :p_year_end
and (org.date_to is null or
org.date_to >= :p_year_start)
and org.organization_id = TP.organization_id
/*and TP.plan_status_type_id <> ''CANCELLED''*/
and TP.time_period_id = PTP.time_period_id
and PTP.period_type = ''Year''
and PTP.start_date = :p_year_start
and TP.training_plan_id = TPC.training_plan_id
and TPC.tp_measurement_type_id = TMT.tp_measurement_type_id
and TMT.business_group_id = org.business_group_id
and ((TPC.tp_measurement_type_id in (:DEDUCTIBLE_EXT_TRN_PLAN,
:DEDUCTIBLE_EXT_TRN_PLAN_SA,
:DEDUCTIBLE_EXT_TRN_PLAN_VAE) AND
TMT.cost_level = ''PLAN'') or
(TPC.tp_measurement_type_id= :DEDUCTIBLE_EXT_TRN_CLASS AND
TMT.cost_level = ''EVENT'') or
(TPC.tp_measurement_type_id in (:SKILLS_ASSESSMENT,
:VAE,
:OTHER_LEARN_DEDUCT_COST_EXT) AND
TMT.cost_level = ''DELEGATE''))
AND TMT.unit = ''M''
AND TPC.booking_id = ODB.booking_id(+)
and ODB.delegate_person_id = PER.person_id(+)
and :p_comp_end between PER.effective_start_date(+)
AND PER.effective_end_date (+) '||
L_GROUP_INNER1||'UNION ALL '||L_SELECT_INNER2||' from
hr_all_organization_units comp,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_all_people_f per,
per_absence_attendances pabs,
per_absence_attendance_types pabt,
po_vendors ota_pv,
hr_organization_information bg_info
where comp.organization_id = :p_company_id
and comp.date_from <= :p_year_end
and (comp.date_to is null or
comp.date_to >= :p_year_start)
and estab_info.org_information_context = ''FR_ESTAB_INFO''
and estab_info.org_information1 = to_char(comp.organization_id)
and estab.organization_id = estab_info.organization_id
and estab.date_from <= :p_year_end
and (estab.date_to is null or
estab.date_to >= :p_year_start)
and estab.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
/* rough filter on asg dates: */
and ass.effective_start_date <= :p_comp_end
and ass.effective_end_date >= :p_comp_start
and ass.person_id = per.person_id
and :p_comp_end between per.effective_start_date
and per.effective_end_date
and per.person_id = pabs.person_id
and pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
and pabs.date_end between ass.effective_start_date
and ass.effective_end_date
and pabs.date_end between :p_comp_start
and :p_comp_end
/*Not Within Training Plan*/
and pabs.abs_information18 = ''N''/* nullable */
/* Training leave category */ '||L_WHERE_INNER2||'
and (pabs.abs_information1 is null or
pabs.abs_information1 not in (''TRAINING_CREDIT'',
''TRAINING_LEAVE''))
and pabs.abs_information3 = ota_pv.vendor_id /* Training provider*/
and pabs.abs_information5 = ''EMPLOYER'' /* Subsidized type */
and pabs.abs_information11 <> ''0''
and pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
and pabt.absence_category = ''TRAINING_ABSENCE''
AND bg_info.organization_id = comp.business_group_id
and bg_info.org_information_context = ''Business Group Information'' '||
L_ORDER_BY;
L_SELECT_OUTER := 'select round(nvl(sum(sal),0)) C111
from (
';
L_SELECT_INNER1:= 'select
decode(tpc_sal.currency_code
,''EUR'',TPC_sal.amount
,hr_currency_pkg.convert_amount_sql(tpc_sal.currency_code
,''EUR''
,sysdate
,tpc_sal.amount
,:CURR_RATE_TYPE)) sal
';
L_SELECT_INNER2:= 'select
decode(bg_info.org_information10
,''EUR'',fnd_number.canonical_to_number(pabs.abs_information21)
,hr_currency_pkg.convert_amount_sql(
bg_info.org_information10
,''EUR''
,sysdate
,nvl(fnd_number.canonical_to_number(pabs.abs_information21),0)
,:CURR_RATE_TYPE)) sal
';
L_SELECT_OUTER := null;
L_SELECT_INNER1:= 'select /*+ORDERED*/
decode(tmt.tp_measurement_code,
''FR_SKILLS_ASSESSMENT'',2,
''FR_VAE'',3,
1) class_order
,decode(tmt.tp_measurement_code,
''FR_SKILLS_ASSESSMENT'',HLK_tmt.meaning,
''FR_VAE'',HLK_tmt.meaning,
EVT_tl.title) class_name
,PER.full_name full_name
,PER.order_name order_name
,PER.employee_number emp_num
,null leav_cat
,to_date(null) abs_st
,to_date(null) abs_en
,tp.name plan_name
,decode(tmt.tp_measurement_code,
''FR_ACTUAL_HOURS'',fnd_number.number_to_canonical(TPC_hrs.amount),
''FR_SKILLS_ASSESSMENT'',TPC_hrs.tp_cost_information3,
''FR_VAE'',TPC_hrs.tp_cost_information3) act_hrs
,decode(tmt.tp_measurement_code,
''FR_ACTUAL_HOURS'',TPC_hrs.tp_cost_information4,
''FR_SKILLS_ASSESSMENT'',TPC_hrs.tp_cost_information4,
''FR_VAE'',TPC_hrs.tp_cost_information4) out_hrs
,decode(tmt.tp_measurement_code,
''FR_ACTUAL_HOURS'',hlk_lcat.meaning) legal_cat
,tpc_sal.amount sal
,tpc_sal.currency_code sal_cc
';
L_SELECT_INNER2:= 'select
4 class_order
,''ABSENCE'' class_name
,PER.full_name full_name
,PER.order_name order_name
,PER.employee_number emp_num
,leavecat.meaning leav_cat
,pabs.date_start abs_st
,pabs.date_end abs_en
,null plan_name
,fnd_number.number_to_canonical(pabs.absence_hours) act_hrs
,pabs.abs_information20 out_hrs
,legalcat.meaning legal_cat
,nvl(fnd_number.canonical_to_number(pabs.abs_information21),0) sal
,bg_info.org_information10 sal_cc
';
l_sql := L_SELECT_OUTER||L_SELECT_INNER1||' from
hr_all_organization_units comp,
hr_organization_information tp_org_info,
hr_all_organization_units org,
ota_training_plans TP,
per_time_periods PTP,
ota_training_plan_costs TPC_sal,
ota_training_plan_costs TPC_hrs,
ota_tp_measurement_types TMT,
ota_delegate_bookings ODB,
ota_events EVT,
per_all_people_f PER,
hr_lookups HLK_tmt,
hr_lookups HLK_lcat,
ota_events_tl evt_tl
where comp.organization_id = :p_company_id
and comp.date_from <= :p_end_year
and (comp.date_to is null or
comp.date_to >= :p_start_year) '
||L_WHERE_TP_ORG||'
and org.organization_id = tp_org_info.organization_id
and org.date_from <= :p_end_year
and (org.date_to is null or
org.date_to >= :p_start_year)
and org.organization_id = TP.organization_id
/*and TP.plan_status_type_id <> ''CANCELLED''*/
and TP.time_period_id = PTP.time_period_id
and PTP.period_type = ''Year''
and PTP.start_date = :p_start_year
and TP.training_plan_id = TPC_sal.training_plan_id
and TPC_sal.tp_measurement_type_id = :DEDUCTIBLE_LEARNER_SALARY
and TPC_sal.booking_id = TPC_hrs.booking_id
and TPC_sal.training_plan_id = TPC_hrs.training_plan_id
and TPC_hrs.tp_measurement_type_id in (:ACTUAL_HOURS,
:SKILLS_ASSESSMENT,
:VAE)
and TMT.tp_measurement_type_id = TPC_hrs.tp_measurement_type_id
and TMT.cost_level = ''DELEGATE''
and TMT.unit in (''M'',''N'')
AND TPC_sal.booking_id = ODB.booking_id
and ODB.delegate_person_id = PER.person_id
and :p_end_comp between PER.effective_start_date
AND PER.effective_end_date
AND ODB.event_id = EVT.event_id
/*and EVT.event_type = ''SCHEDULED''*/
/*and evt.event_status <> ''A'' A=Cancelled. Nb. event_status is
not null for SCHEDULED events*/
/*and evt.course_start_date between p_start_year
and p_end_year*/
/* COURSE_START_DATE is only not null for SCHEDULED events where they are
Normal or Full*/
and hlk_tmt.lookup_type = ''OTA_PLAN_MEASUREMENT_TYPE''
and hlk_tmt.lookup_code = TMT.tp_measurement_code
and hlk_lcat.lookup_type(+) = ''FR_LEGAL_TRG_CATG''
and hlk_lcat.lookup_code(+) = TPC_hrs.tp_cost_information3
and EVT_tl.event_id = EVT.event_id
and EVT_tl.language = userenv(''LANG'')
UNION ALL '||L_SELECT_INNER2||' from
hr_all_organization_units comp,
hr_organization_information estab_info,
hr_all_organization_units estab,
per_all_assignments_f ass,
per_all_people_f per,
per_absence_attendances pabs,
per_absence_attendance_types pabt,
hr_lookups leavecat,
hr_lookups legalcat,
hr_organization_information bg_info
where comp.organization_id = :p_company_id
and comp.date_from <= :p_end_year
and (comp.date_to is null or
comp.date_to >= :p_start_year)
and pabt.absence_category = ''TRAINING_ABSENCE''
and pabs.absence_attendance_type_id = pabt.absence_attendance_type_id
and pabs.abs_information_category = ''FR_TRAINING_ABSENCE''
/* Not Within Training Plan */
and pabs.abs_information18 = ''N''/*nullable*/
and pabs.date_end between ass.effective_start_date
and ass.effective_end_date
and pabs.date_end between :p_start_comp
and :p_end_comp
and pabs.abs_information21 <> ''0''
and per.person_id = pabs.person_id
and ass.person_id = per.person_id
and :p_end_comp between per.effective_start_date
and per.effective_end_date
and estab_info.organization_id = ass.establishment_id
AND ass.primary_flag = ''Y''
and estab.date_from <= :p_end_year
and (estab.date_to is null or
estab.date_to >= :p_start_year)
and estab.organization_id = estab_info.organization_id
and estab_info.org_information_context = ''FR_ESTAB_INFO''
and estab_info.org_information1 = to_char(comp.organization_id)
and leavecat.lookup_code(+) = pabs.abs_information1
and leavecat.lookup_type(+) = ''FR_TRAINING_LEAVE_CATEGORY''
and legalcat.lookup_code(+) = pabs.abs_information19
and legalcat.lookup_type(+) = ''FR_LEGAL_TRG_CATG''
/* rough filter on asg dates: */
and ass.effective_start_date <= :p_comp_end
and ass.effective_end_date >= :p_comp_start
AND bg_info.organization_id = comp.business_group_id
and bg_info.org_information_context = ''Business Group Information'' '||
L_ORDER_BY;
L_SELECT_OUTER := 'select round(nvl(sum(decode(tpc.currency_code
,''EUR'',TPC.amount
,hr_currency_pkg.convert_amount_sql(tpc.currency_code
,''EUR''
,sysdate
,tpc.amount
,:CURR_RATE_TYPE))),0)) C151
';
L_SELECT_INNER1:= null;
L_SELECT_OUTER := 'select /*+ORDERED*/
TP.name plan_name
,tpc.amount amount
,tpc.currency_code cc
';
L_SELECT_INNER1:= null;
l_sql := L_SELECT_OUTER||' from
hr_all_organization_units comp,
hr_organization_information tp_org_info,
hr_all_organization_units org,
ota_training_plans TP,
per_time_periods PTP,
ota_training_plan_costs TPC
where comp.organization_id = :p_company_id
and comp.date_from <= :p_end_year
and (comp.date_to is null or
comp.date_to >= :p_start_year) '
||L_WHERE_TP_ORG||'
and org.organization_id = tp_org_info.organization_id
and org.date_from <= :p_end_year
and (org.date_to is null or
org.date_to >= :p_start_year)
and org.organization_id = TP.organization_id
and TP.time_period_id = PTP.time_period_id
and PTP.period_type = ''Year''
and TP.training_plan_id = TPC.training_plan_id
and TPC.tp_measurement_type_id = :OTHER_PLAN_DEDUCTIBLE_COSTS
and PTP.start_date = :p_start_year
and tpc.event_id is null
and tpc.booking_id is null'||L_ORDER_BY;
l_select2 VARCHAR2(200);
l_select2 := '(SELECT organization_id establishment_id FROM hr_fr_establishments_v WHERE company_org_id = ''';
l_select2 := l_select2 || to_char(p_company_id) || '''' || ' OR organization_id = ';
l_select2 := l_select2 || to_char(p_company_id) || ') v ';
l_new_tp_string := '(SELECT training_plan_id FROM ota_training_plans WHERE time_period_id = ';
l_new_tp_string := l_new_tp_string || to_char(p_time_period_id) || ' and ( ( organization_id in ( select organization_id ';
l_new_est_string := '(SELECT organization_id organization_id FROM hr_fr_establishments_v WHERE company_org_id = ''';
SELECT 'to_date('''||to_char(ptp.start_date,'YYYYMMDD')||''',''YYYYMMDD'')'
, 'to_date('''||to_char(ptp.end_date,'YYYYMMDD')||''',''YYYYMMDD'')'
INTO l_start_of_plan,
l_end_of_plan
FROM per_time_periods ptp
WHERE ptp.time_period_id = p_time_period_id;
l_prmrec(8).value := l_select2;