[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select meaning into l_lookup_meaning
from hr_lookups
where lookup_type=p_lookup_type
and lookup_code=p_lookup_code;
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));
select hsa.include_or_exclude ioe
from hr_assignment_set_amendments hsa
where hsa.assignment_set_id =p_assignment_set_id;
'select distinct paf.person_id person_id ,pef.employee_number employee_number,pef.full_name full_name
from per_all_assignments_f paf,
hr_assignment_sets hs,
per_all_people_f pef,
per_periods_of_service ppos
where hs.assignment_set_id=:p_assignment_set_id
and nvl(hs.payroll_id,paf.payroll_id)=paf.payroll_id
and hs.business_group_id=paf.business_group_id
and pef.person_id=paf.person_id
and paf.business_group_id=:p_business_group_id
and paf.business_group_id=pef.business_group_id
and paf.assignment_id in ( select assignment_id
from hr_assignment_set_amendments hsa
where hsa.assignment_set_id =:p_assignment_set_id
and hsa.include_or_exclude=''I'')
and ppos.person_id=pef.person_id
and ppos.period_of_service_id=paf.period_of_service_id
and :l_date_from <=nvl(ppos.actual_termination_date,to_date(''31-12-4712'',''DD-MM-YYYY''))
and :l_date_to between pef.effective_start_date and pef.effective_end_date
and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
or
(:l_date_to >= (select max(effective_end_date)
from per_all_assignments_f p1 /*To include TERMINATED employee*/
where p1.person_id=pef.person_id))
)
ORDER BY '||p_sort_order
using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
'select distinct paf.person_id person_id ,pef.employee_number employee_number,pef.full_name full_name
from per_all_assignments_f paf,
hr_assignment_sets hs,
per_all_people_f pef,
per_periods_of_service ppos
where hs.assignment_set_id=:p_assignment_set_id
and hs.business_group_id=paf.business_group_id
and nvl(hs.payroll_id,paf.payroll_id)=paf.payroll_id
and pef.person_id=paf.person_id
and paf.business_group_id=:p_business_group_id
and paf.business_group_id=pef.business_group_id
and paf.assignment_id not in ( select assignment_id
from hr_assignment_set_amendments hsa
where hsa.assignment_set_id =:p_assignment_set_id
and hsa.include_or_exclude=''E'')
and ppos.person_id=pef.person_id
and ppos.period_of_service_id=paf.period_of_service_id
and :l_date_from <=nvl(ppos.actual_termination_date,to_date(''31-12-4712'',''DD-MM-YYYY''))
and :l_date_to between pef.effective_start_date and pef.effective_end_date
and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
or
(:l_date_to >= (select max(effective_end_date)
from per_all_assignments_f p1 /*To include TERMINATED employee*/
where p1.person_id=pef.person_id))
)
ORDER BY '||p_sort_order
using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
'select distinct paf.person_id ,pef.employee_number employee_number,pef.full_name full_name
from per_all_assignments_f paf,
hr_assignment_sets hs,
per_all_people_f pef,
per_periods_of_service ppos
where hs.assignment_set_id=:p_assignment_set_id
and hs.business_group_id=paf.business_group_id
and nvl(hs.payroll_id,paf.payroll_id)=paf.payroll_id
and pef.person_id=paf.person_id
and paf.business_group_id=:p_business_group_id
and paf.business_group_id=pef.business_group_id
and paf.assignment_id not in ( select assignment_id
from hr_assignment_set_amendments hsa
where hsa.assignment_set_id =:p_assignment_set_id
and hsa.include_or_exclude=''E'')
and ppos.person_id=pef.person_id
and ppos.period_of_service_id=paf.period_of_service_id
and :l_date_from <=nvl(ppos.actual_termination_date,to_date(''31-12-4712'',''DD-MM-YYYY''))
and :l_date_to between pef.effective_start_date and pef.effective_end_date
and ((:l_date_to between paf.effective_start_date and paf.effective_end_date)
or
(:l_date_to >= (select max(effective_end_date)
from per_all_assignments_f p1 /*To include TERMINATED employee*/
where p1.person_id=pef.person_id))
)
ORDER BY '||p_sort_order
using p_assignment_set_id,p_business_group_id,p_assignment_set_id,l_date_from,l_date_to,l_date_to,l_date_to;
'Select DISTINCT pap.person_id person_id,pap.employee_number employee_number,pap.full_name full_name
from per_all_people_f pap,
per_all_assignments_f paa,
hr_all_organization_units hao,
hr_organization_information hoi,
per_periods_of_service ppos
where pap.person_id =paa.person_id
and pap.business_group_id=:l_business_group_id
and pap.business_group_id=paa.business_group_id
and pap.person_id=nvl(:l_person_id,pap.person_id)
and paa.establishment_id=nvl(:l_establishment_id,paa.establishment_id)
and paa.establishment_id=hao.organization_id
and hoi.organization_id=hao.organization_id
and hoi.org_information_context =''FR_ESTAB_INFO''
and hoi.org_information1=nvl(:l_company_id,hoi.org_information1)
and ppos.person_id=pap.person_id
and ppos.period_of_service_id=paa.period_of_service_id
and :l_date_from <=nvl(ppos.actual_termination_date,to_date(''31-12-4712'',''DD-MM-YYYY''))
and :l_date_to between pap.effective_start_date and pap.effective_end_date
and ((:l_date_to between paa.effective_start_date and paa.effective_end_date)
or
(:l_date_to >= (select max(effective_end_date)
from per_all_assignments_f p1 /*To include TERMINATED employee*/
where p1.person_id=pap.person_id))
)
ORDER BY '||p_sort_order
using
p_business_group_id,p_person_id,p_establishment_id,p_company_id,l_date_from,l_date_to,l_date_to,l_date_to;
select distinct papf.full_name EMP_NAME,
papf.employee_number EMP_NUM,
pa.address_line1||' '||pa.address_line2||' '|| pa.address_line3 ||' '|| pa.region_2 ||' '||
pa.region_3 ||' '|| pa.postal_code ||' '|| pa.town_or_city||' '|| pa.region_1 ||' '||pa.country
||' '||pa.telephone_number_1||' '|| pa.telephone_number_2||' '||pa.telephone_number_3 E_ADDRESS,
to_char(papf.original_date_of_hire,'YYYY-MM-DD') HIRE_DATE,
to_char(ppos.adjusted_svc_date,'YYYY-MM-DD') ADJUSTED_SVC_DATE,
to_char(ppos.actual_termination_date,'YYYY-MM-DD') TERM_DATE,
OTA_FR_TRG_SUM.get_lookup_value('FR_CONTRACT_CATEGORY',pcf.ctr_information2) CONTRACT_CATEGORY,
pcf.type CONTRACT_TYPE,
case
when pcf.ctr_information11 is null then
paaf.normal_hours
else
fnd_number.canonical_to_number(pcf.ctr_information11)
end CONT_WRK_HRS,---CONTRACTUAL_WORKING_HOURS --It is the hours in the DDF segment with unit ,display unit also
case
when pcf.ctr_information11 is null then
OTA_FR_TRG_SUM.get_lookup_value('FREQUENCY',paaf.frequency)
else
OTA_FR_TRG_SUM.get_lookup_value('FR_FIXED_TIME_UNITS',pcf.ctr_information12)
end CONT_WRK_UNT, --Contractual working hours unit
pca.name COLLECTIVE_AGREEMENT,
paaf.assignment_category ASSGN_CATEGORY,
to_char(L_P_DATE_FROM,'YYYY-MM-DD') PERIOD_FROM,
to_char(L_P_DATE_TO,'YYYY-MM-DD') PERIOD_TO,
hout1.name EST_NAME,
hla1.address_line_1||' '||hla1.address_line_2||' '||hla1.address_line_3||' '||hla1.region_2
||' '||hla1.region_3||' '||hla1.postal_code||' '||hla1.town_or_city||' '||hla1.region_1||' '||
hla1.country||' '||hla1.telephone_number_1||' '||hla1.telephone_number_2||' '||
hla1.telephone_number_3 EST_ADDRESS,
hoi1.org_information2 SIRET,
hout2.name COM_NAME,
hla2.address_line_1||' '||hla2.address_line_2||' '||hla2.address_line_3||' '||
hla2.region_2||' '||hla2.region_3||' '||hla2.postal_code||' '||hla2.town_or_city||' '||
hla2.region_1||' '||hla2.country||' '||hla2.telephone_number_1||' '||hla2.telephone_number_2||' '||
hla2.telephone_number_3 COM_ADDRESS
from per_all_people_f papf,
per_addresses pa,
per_all_assignments_f paaf,
per_periods_of_service ppos,
per_contracts_f pcf,
per_collective_agreements pca,
hr_all_organization_units hou1,
hr_all_organization_units_tl hout1,
hr_locations_all hla1,
hr_organization_information hoi1,
hr_all_organization_units hou2,
hr_all_organization_units_tl hout2,
hr_locations_all hla2
where papf.person_id=l_p_person_id
and pa.person_id(+)=papf.person_id
and pa.primary_flag(+)='Y'
and pa.business_group_id(+)=papf.business_group_id
and paaf.business_group_id=paaf.business_group_id
and paaf.person_id=papf.person_id
and ppos.person_id=papf.person_id
and ppos.period_of_service_id=paaf.period_of_service_id
and paaf.contract_id=pcf.contract_id(+)
and paaf.person_id=pcf.person_id(+)
and nvl(paaf.collective_agreement_id,-1)=pca.collective_agreement_id(+)
and paaf.establishment_id=hou1.organization_id
and hout1.organization_id=hou1.organization_id
and hout1.language=userenv('lang')
and nvl(hou1.location_id,-1)=hla1.location_id
and hoi1.organization_id=hou1.organization_id
and hoi1.org_information_context='FR_ESTAB_INFO'
and hoi1.org_information1=hout2.organization_id
and hout2.language=userenv('lang')
and hou2.organization_id=hout2.organization_id
and nvl(hou2.location_id,-1)=hla2.location_id(+)
and l_p_date_to between papf.effective_start_date and papf.effective_end_date
and ((l_p_date_to between paaf.effective_start_date and paaf.effective_end_date)
or
(l_p_date_to >= (select max(effective_end_date)
from per_all_assignments_f p1 /*To include TERMINATED employee*/
where p1.person_id=papf.person_id))
)
and l_p_date_to between pcf.effective_start_date and pcf.effective_end_date;
select distinct odb.booking_id C_W_REF, -- Enrollment reference
obst.name ENR_STATUS ,--Enrollment Status
oeventl.title C_W_NAME , --Class Name
to_char(oevent.course_start_date,'YYYY-MM-DD') C_W_S_DAT ,--Course Start Date
to_char(oevent.course_end_date,'YYYY-MM-DD') C_W_E_DAT , --Course End date
odb.source_of_booking ENR_SOURCE , --Source
odb.failure_reason FAIL_REASON, --Failure Reason of Attendance
decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',
case otpc.tp_cost_information3
when 'JOB_ADAPT' then
'I-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',otpc.tp_cost_information3)
when 'JOB_EVOL' then
'II-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',otpc.tp_cost_information3)
when 'COMP_DEV' then
'III-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',otpc.tp_cost_information3)
end) C_W_L_CAT,--Legal category
fnd_number.canonical_to_number(otpc.tp_cost_information4) C_W_O_HR , --Hours Outside working
decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',nvl(otpc.amount,0),
nvl(fnd_number.canonical_to_number(otpc.tp_cost_information3),0))C_W_TOT_HR --Total Hours
from per_all_people_f papf,
ota_delegate_bookings odb,
ota_booking_status_types obst,
ota_events oevent,
ota_events_tl oeventl,
ota_training_plan_costs otpc,
ota_tp_measurement_types otmt
where papf.person_id=l_p_person_id
and papf.person_id=odb.delegate_person_id
and odb.booking_status_type_id=obst.booking_status_type_id
and oevent.event_id=odb.event_id
and oeventl.event_id=oevent.event_id
and oeventl.language=userenv('lang')
and odb.booking_id=otpc.booking_id
--and otpc.training_plan_id=otp.training_plan_id
and otpc.tp_measurement_type_id = otmt.tp_measurement_type_id
and otmt.business_group_id=odb.business_group_id
and otmt.tp_measurement_code in ('FR_ACTUAL_HOURS','FR_SKILLS_ASSESSMENT','FR_VAE')
and oevent.course_start_date between L_P_DATE_FROM and L_P_DATE_TO
-- or oevent.course_end_date<=L_P_DATE_TO
and l_p_date_to between papf.effective_start_date and papf.effective_end_date
order by C_W_S_DAT asc;
select sum(decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',otpc.amount,
fnd_number.canonical_to_number(nvl(otpc.tp_cost_information3,0)))) W_TOT_HOURS ,
sum(fnd_number.canonical_to_number(nvl(otpc.tp_cost_information4,0))) W_TOT_OUT_WK_HR,
decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',
otpc.tp_cost_information3,
' ') LEGAL_CATG --i)JOB_ADAPT ii) JOB_EVOL , iii)COMP_DEV
from per_all_people_f papf,
ota_delegate_bookings odb,
ota_training_plan_costs otpc,
ota_tp_measurement_types otmt,
ota_events oevent
where papf.person_id=l_p_person_id
and papf.person_id=odb.delegate_person_id
and odb.booking_id=otpc.booking_id
and otpc.tp_measurement_type_id = otmt.tp_measurement_type_id
and otmt.tp_measurement_code in ('FR_ACTUAL_HOURS','FR_SKILLS_ASSESSMENT','FR_VAE')
and oevent.event_id=odb.event_id
and oevent.course_start_date between L_P_DATE_FROM and L_P_DATE_TO
--and oevent.course_end_date<=L_P_DATE_TO
and l_p_date_to between papf.effective_start_date and papf.effective_end_date
group by decode(otmt.tp_measurement_code,'FR_ACTUAL_HOURS',
otpc.tp_cost_information3,
' ');
select distinct paat.name ABS_TYPE,-- Absence Type
to_char(paa.DATE_START,'YYYY-MM-DD') C_O_S_DAT, --Start Date
to_char(paa.DATE_END,'YYYY-MM-DD') C_O_E_DAT, --End date
fnd_number.canonical_to_number(nvl(paa.ABSENCE_DAYS,0)) DURATION_DAYS, --Duration days
fnd_number.canonical_to_number(nvl(paa.ABSENCE_HOURS,0)) C_O_TOT_HR, --Duration hours
OTA_FR_TRG_SUM.get_lookup_value('FR_TRAINING_LEAVE_CATEGORY',paa.ABS_INFORMATION1) TRG_LEAV_CATG,-- Training Leave Category
paa.ABS_INFORMATION2 C_O_NAME, --Course
pv.VENDOR_NAME TRG_PROV,--Training Provider
OTA_FR_TRG_SUM.get_lookup_value('FR_TRAINING_TYPE',paa.ABS_INFORMATION4) TYPE_OF_TRG, --Type Of Training
paa.ABS_INFORMATION17 C_O_REF, --Training reference
paa.ABS_INFORMATION18 WITH_TRG_PLAN, --Within Training Plan
case paa.ABS_INFORMATION19
when 'JOB_ADAPT' then
'I-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',paa.ABS_INFORMATION19)
when 'JOB_EVOL' then
'II-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',paa.ABS_INFORMATION19)
when 'COMP_DEV' then
'III-'||OTA_FR_TRG_SUM.get_lookup_value('FR_LEGAL_TRG_CATG',paa.ABS_INFORMATION19)
end C_O_L_CAT, ---Legal Category
fnd_number.canonical_to_number(nvl(paa.ABS_INFORMATION20,0)) C_O_O_HR, --Hours Outside Working Hours
OTA_FR_TRG_SUM.get_lookup_value('FR_TRAINING_SUBSIDY_TYPE',paa.ABS_INFORMATION5) SUBSI_TYPE, --Subsidized Type
hfov.name SUBSI_ORG --Subsidizing Organization
from per_all_people_f papf,
per_absence_attendances paa,
per_absence_attendance_types paat,
po_vendors pv,
HR_FR_OPCA_V hfov
where papf.person_id=l_p_person_id
and papf.person_id=paa.person_id
and papf.business_group_id=paa.business_group_id
and paa.ABSENCE_ATTENDANCE_TYPE_ID=paat.ABSENCE_ATTENDANCE_TYPE_ID
and paat.ABSENCE_CATEGORY='TRAINING_ABSENCE'
and paa.ABS_INFORMATION_CATEGORY='FR_TRAINING_ABSENCE'
and paa.ABS_INFORMATION18='N'
and paa.ABS_INFORMATION19 is not null
and paa.ABS_INFORMATION3=pv.vendor_id(+)
and paa.ABS_INFORMATION6=hfov.organization_id(+)
and paa.date_start between L_P_DATE_FROM and L_P_DATE_TO
--and paa.date_end<=L_P_DATE_TO
and l_p_date_to between papf.effective_start_date and papf.effective_end_date
order by C_O_S_DAT asc;
Select sum(fnd_number.canonical_to_number(nvl(paa.ABSENCE_HOURS,0))) O_TOT_HOURS ,
sum(fnd_number.canonical_to_number(nvl(paa.ABS_INFORMATION20,0))) O_TOT_OUT_WK_HR,
(paa.ABS_INFORMATION19) LEGAL_CATG --i)JOB_ADAPT ii) JOB_EVOL , iii)COMP_DEV
from per_all_people_f papf,
per_absence_attendances paa
where papf.person_id=l_p_person_id
and papf.person_id=paa.person_id
and papf.business_group_id=paa.business_group_id
and paa.ABS_INFORMATION_CATEGORY='FR_TRAINING_ABSENCE' -- Absence Should be Training Leave category
and paa.ABS_INFORMATION18='N' -- Within Training Plan should be 'NO'
and paa.ABS_INFORMATION19 is not null --Legal category can not be null
and paa.date_start between L_P_DATE_FROM and L_P_DATE_TO
-- and paa.date_end<=L_P_DATE_TO
and l_p_date_to between papf.effective_start_date and papf.effective_end_date
group by paa.ABS_INFORMATION19;