[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select distinct
apf.person_id,
paf.establishment_id est_id,
paf.contract_id,
apf.full_name ,
apf.date_of_birth,
apf.middle_names,
apf.per_information1 maiden,
apf.last_name ,
apf.first_name,
apf.national_identifier,
apf.original_date_of_hire,
hout.name establishment_name,
hla.address_line_1 Number_Road ,
hla.address_line_2 Complement ,
hla.address_line_3 Other ,
hla.region_2 INSEE_Code ,
hla.region_3 Small_Town ,
hla.postal_code Postal_Code ,
hla.town_or_city City ,
hla.region_1 Department ,
hla.country Country ,
hla.telephone_number_1 Telephone,
hla.telephone_number_2 Fax,
hla.telephone_number_3 Telephone3,
pav.address_line1 PNumber_Road,
pav.address_line2 PComplement,
pav.address_line3 POther,
pav.region_2 PINSEE_Code,
pav.region_3 PSmall_Town,
pav.postal_code PPostal_Code,
pav.town_or_city PCity,
pav.region_1 PDepartment,
pav.country PCountry,
pav.telephone_number_1 PTelephone,
pav.telephone_number_2 PFax,
pav.telephone_number_3 PTelephone3,
hoi.org_information2 siret,
hoi.org_information3 NAF,
hoi.org_information19 trg_bd_id,
pcf.ctr_information3 proposed_end_date,
pcf.ctr_information11 durationF,
pcf.ctr_information12 unitsF ,
pcf.duration duration,
pcf.duration_units units,
pcf.status status,
pcf.effective_start_date c_start_date,
pcf.effective_end_date c_end_date,
ppos.actual_termination_date actual_termination_date
from
hr_locations_all hla,
hr_all_organization_units hou,
hr_all_organization_units_tl hout,
per_all_assignments_f paf ,
per_all_people_f apf,
per_addresses pav,
hr_organization_information hoi,
per_contracts_f pcf,
hr_soft_coding_keyflex hsck,
per_periods_of_service ppos
where hou.organization_id= paf.establishment_id
and hout.organization_id=hou.organization_id
and hout.language=userenv('lang')
and hla.location_id(+)=hou.location_id
and hoi.organization_id(+)=paf.establishment_id
and hoi.org_information_context(+) ='FR_ESTAB_INFO'
and hsck.soft_coding_keyflex_id=paf.soft_coding_keyflex_id
and hsck.segment2 <> 'STUDENT'
and ppos.person_id=paf.person_id
and ppos.period_of_service_id=paf.period_of_service_id
and ppos.actual_termination_date is not null
and paf.person_id=apf.person_id
and paf.contract_id is not null
and paf.contract_id = pcf.contract_id
and paf.person_id=pcf.person_id
and pcf.ctr_information2 like 'FIXED_TERM'
and pcf.type not in ('APPRENTICESHIP','ORIENTATION','ADAPTATION','QUALIFICATION')
and pav.person_id(+)=apf.person_id
and pav.primary_flag(+)='Y'
and pav.business_group_id(+)=apf.business_group_id
and apf.employee_number=lp_employee_number
and apf.business_group_id=lp_bg_id
and c_effective_date between apf.effective_start_date and apf.effective_end_date
and c_effective_date between pcf.effective_start_date and pcf.effective_end_date
and c_effective_date >= paf.effective_end_date
and paf.effective_end_date=ppos.actual_termination_date;
select hrvt.name trg_bd_name,
hla.address_line_1 Number_Road ,
hla.address_line_2 Complement ,
hla.address_line_3 Other ,
hla.region_2 INSEE_Code ,
hla.region_3 Small_Town ,
hla.postal_code Postal_Code ,
hla.town_or_city City ,
hla.region_1 Department ,
hla.country Country ,
hla.telephone_number_1 Telephone,
hla.telephone_number_2 Fax,
hla.telephone_number_3 Telephone3
from hr_organization_information hoi,
hr_all_organization_units hrv,
hr_all_organization_units_tl hrvt,
hr_locations_all hla
where hoi.ORG_INFORMATION_CONTEXT='CLASS'
and hoi.org_information1='FR_OPAC'
and hrv.business_group_id=l_bg_id
and hrv.organization_id=hoi.organization_id
and hrv.organization_id=l_tr_bd_id
and hla.location_id(+)=hrv.location_id
and hrvt.organization_id=hrv.organization_id
and hrvt.language=userenv('lang');
select lookup_code,meaning from hr_lookups
where lookup_type='BIAF_LOOKUP_CODE';
select meaning into l_naf_meaning from fnd_common_lookups where lookup_type='FR_NAF_CODE'
and lookup_code=l_cursor_get_data.NAF;
SELECT COUNT(asg.assignment_id)
FROM per_all_assignments_f asg,
per_assignment_status_types ast,
-- per_person_types_v pt,
per_all_people_f peo
WHERE asg.establishment_id=p_est_id
AND asg.person_id = peo.person_id
AND (ast.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN')) -- AND p_include_suspended = 'Y')
AND asg.assignment_status_type_id = ast.assignment_status_type_id
AND asg.primary_flag = 'Y'
and exists ( select null
from per_person_type_usages_f pf,
per_person_types pt
where pf.person_id=peo.person_id
and pf.person_type_id = pt.person_type_id
--and pt.language=userenv('lang')
and 'Y' = pefrusdt.get_table_value(peo.business_group_id
,'FR_USER_PERSON_TYPE'
,p_udt_column
,pt.user_person_type
,p_effective_date)
and p_effective_date between pf.effective_start_date and pf.effective_end_date
)
/*AND peo.person_type_id = pt.person_type_id
AND 'Y' = pefrusdt.get_table_value(peo.business_group_id
,'FR_USER_PERSON_TYPE'
,p_udt_column
,pt.user_person_type
,p_effective_date)*/
AND p_effective_date >= asg.effective_start_date
AND p_effective_date <= asg.effective_end_date
AND p_effective_date >= peo.effective_start_date
AND p_effective_date <= peo.effective_end_date;
OR EXISTS (SELECT null
FROM per_all_assignments_f asg2,
per_assignment_status_types ast2,
-- per_person_types_v pt2,
per_all_people_f peo2
WHERE asg2.establishment_id =p_est_id
AND asg2.person_id = peo.person_id
AND asg2.person_id = peo2.person_id
AND (ast2.per_system_status IN ('ACTIVE_ASSIGN','SUSP_ASSIGN') AND p_include_suspended = 'Y')
AND asg2.assignment_status_type_id = ast2.assignment_status_type_id
AND asg2.primary_flag = 'Y'
and exists ( select null
from per_person_type_usages_f pf2, per_person_types pt2
where pf2.person_id=peo2.person_id
and pf2.person_type_id = pt2.person_type_id
and 'Y' = pefrusdt.get_table_value(peo2.business_group_id
,'FR_USER_PERSON_TYPE'
,p_udt_column
,pt2.user_person_type
,p_effective_date)
)
/*AND peo2.person_type_id = pt2.person_type_id
AND 'Y' = pefrusdt.get_table_value(peo2.business_group_id
,'FR_USER_PERSON_TYPE'
,p_udt_column
,pt2.user_person_type
,p_effective_date)
AND p_effective_date+1 >= asg2.effective_start_date
AND p_effective_date+1 <= asg2.effective_end_date
AND p_effective_date+1 >= peo2.effective_start_date
AND p_effective_date+1 <= peo2.effective_end_date)
); */
select pcf.effective_start_date,
pcf.effective_end_date
from per_contracts_f pcf ,
per_all_assignments_f paf,
per_all_people_f ppf
where ppf.person_id=f_person_id
and paf.person_id=ppf.person_id
and paf.contract_id(+)=pcf.contract_id
and pcf.effective_start_date=(select min(pcf1.effective_start_date)
from per_contracts_f pcf1
where pcf1.contract_id=pcf.contract_id);
select active_end_date
from per_contracts
where person_id=f_person_id
and active_start_date=( select max(active_end_date) from
per_contracts where person_id=f_person_id);
select apf.full_name ,apf.last_name ,apf.first_name, apf.national_identifier,apf.original_date_of_hire from
per_all_people_f apf where apf.person_id=lp_person_id;*/
select distinct paf.person_id ,l_asg_id ,pef.employee_number
from per_all_assignments_f paf,
hr_assignment_sets hs,
per_all_people_f pef
where hs.assignment_set_id=l_asg_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=l_bg_id
and paf.assignment_id in ( select assignment_id
from hr_assignment_set_amendments hsa
where hsa.assignment_set_id =l_asg_id
and hsa.include_or_exclude='I')
and p_effective_date between pef.effective_start_date and pef.effective_end_date ;
select distinct paf.person_id ,l_asg_id ,pef.employee_number
from per_all_assignments_f paf,
hr_assignment_sets hs,
per_all_people_f pef
where hs.assignment_set_id=l_asg_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=l_bg_id
and paf.assignment_id not in ( select assignment_id
from hr_assignment_set_amendments hsa
where hsa.assignment_set_id =l_asg_id
and hsa.include_or_exclude='E')
and p_effective_date between pef.effective_start_date and pef.effective_end_date ;
select /*count(hsa.include_or_exclude) cnt , */ hsa.include_or_exclude ioe
from hr_assignment_set_amendments hsa
where hsa.assignment_set_id =p_asg_id;
select paf.person_id ,l_asg_id ,pef.employee_number
from per_all_assignments_f paf,hr_assignment_sets hs,per_all_people_f pef
where hs.assignment_set_id=l_asg_id
and hs.payroll_id is null
and hs.business_group_id=paf.business_group_id
and pef.person_id=paf.person_id
and paf.business_group_id= l_bg_id
and paf.assignment_id not in ( select assignment_id from hr_assignment_set_amendments hsa
where hsa.assignment_set_id =hs.assignment_set_id
and hsa.include_or_exclude='E'
)
and p_effective_date between pef.effective_start_date and pef.effective_end_date
and p_effective_date between paf.effective_start_date and paf.effective_end_date ;
PER_FR_BIAF_REPORT.vXMLTable.DELETE;
select userenv('LANGUAGE') into g_nls_db_char from dual;