[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(convert(upper(cp_input_string),'UTF8'),
utl_raw.cast_to_varchar2(hextoraw('C380')),'A'
),
utl_raw.cast_to_varchar2(hextoraw('C38A')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C387')),'C'
),
utl_raw.cast_to_varchar2(hextoraw('C389')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C39C')),'U'
),
utl_raw.cast_to_varchar2(hextoraw('C399')),'U'
),
utl_raw.cast_to_varchar2(hextoraw('C39B')),'U'
),
utl_raw.cast_to_varchar2(hextoraw('C394')),'O'
),
utl_raw.cast_to_varchar2(hextoraw('C38F')),'I'
),
utl_raw.cast_to_varchar2(hextoraw('C38E')),'I'
),
utl_raw.cast_to_varchar2(hextoraw('C388')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C38B')),'E'
),
utl_raw.cast_to_varchar2(hextoraw('C382')),'A'
),
utl_raw.cast_to_varchar2(hextoraw('C592')),'OE'
),
utl_raw.cast_to_varchar2(hextoraw('C386')),'AE'
)
from dual;
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 IN (SELECT hr2.organization_id
FROM hr_organization_information hr1,
hr_organization_information hr2
WHERE hr1.organization_id = p_ent_id
AND hr1.org_information1 = hr2.org_information1
AND hr1.org_information_context = hr2.org_information_context
AND hr1.org_information_context = 'FR_ESTAB_INFO'
AND p_ent_id IS NOT NULL
UNION
SELECT NVL(p_est_id,0)
FROM DUAL
WHERE p_ent_id IS NULL)
AND NVL(peo.sex,'X') = NVL(p_sex,NVL(peo.sex,'X'))
AND asg.person_id = peo.person_id
AND ((ast.per_system_status = 'ACTIVE_ASSIGN' AND p_include_suspended = 'N') OR
(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 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
AND (LEAST(asg.effective_end_date,peo.effective_end_date) > p_effective_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 IN (SELECT hr2.organization_id
FROM hr_organization_information hr1,
hr_organization_information hr2
WHERE hr1.organization_id = p_ent_id
AND hr1.org_information1 = hr2.org_information1
AND hr1.org_information_context = hr2.org_information_context
AND hr1.org_information_context = 'FR_ESTAB_INFO'
AND p_ent_id IS NOT NULL
UNION
SELECT NVL(p_est_id,0)
FROM DUAL
WHERE p_ent_id IS NULL)
AND NVL(peo2.sex,'X') = NVL(p_sex,NVL(peo2.sex,'X'))
AND asg2.person_id = peo.person_id
AND asg2.person_id = peo2.person_id
AND ((ast2.per_system_status = 'ACTIVE_ASSIGN' AND p_include_suspended = 'N') OR
(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 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 UPPER(territory_short_name)
FROM fnd_territories_vl
WHERE territory_code = b_country_code;
SELECT date_start
FROM per_periods_of_service
WHERE period_of_service_id = b_period_id;
SELECT projected_hire_date
FROM per_applications
WHERE application_id = b_appl_id;
SELECT UPPER(hoi2.org_information1)
FROM hr_organization_information hoi,
hr_organization_information hoi2
WHERE hoi2.org_information_context = 'FR_URSSAF_CENTR_INFO'
AND hoi2.organization_id = hoi.org_information1
AND hoi.org_information_context = 'FR_ESTAB_URSSAF'
AND hoi.organization_id = b_est_id;
SELECT /*+index(OI1 HR_ORGANIZATION_INFORMATIO_FK2)*/
ass.assignment_id ass_id,
ass.establishment_id est_id,
ass.organization_id org_id,
ass.person_id person_id,
ass.period_of_service_id,
ass.application_id,
UPPER(ass.time_normal_start) start_time,
UPPER(OI1.ORG_INFORMATION2) siret,
OI1.ORG_INFORMATION4 monthly_hours,
UPPER(OI1.ORG_INFORMATION3) naf_code,
UPPER(ou1.name) est_name,
UPPER(loc.address_line_1) loc_address1,
UPPER(loc.address_line_2) loc_address2,
-- Added for bug #5240132
UPPER(loc.address_line_3) loc_address3,
--
UPPER(loc.postal_code) loc_postal,
UPPER(loc.town_or_city) loc_town,
loc.telephone_number_1 loc_telephone,
UPPER(NVL(peo.per_information1,peo.last_name)) nom_de_naiss,
peo.last_name nom_marital,
UPPER(peo.first_name) app_first_name,
peo.full_name app_full_name,
peo.sex app_sex,
peo.national_identifier app_ni,
DECODE(ass.period_of_service_id,
NULL,peo.applicant_number,peo.employee_number) app_employee_number,
peo.date_of_birth app_dob,
peo.nationality app_nat_code,
peo.title app_tit,
peo.region_of_birth app_dept_code,
UPPER(peo.town_of_birth) app_town_birth,
peo.country_of_birth,
adr.address_line1 app_address1,
adr.address_line2 app_address2,
-- Added for bug #5240132
adr.address_line3 app_address3,
--
adr.postal_code app_pos_code,
adr.town_or_city app_town,
NVL(con.ctr_information6,to_char(ass.probation_period)) app_prob_period,
DECODE(con.ctr_information6,NULL,ass.probation_unit
,con.ctr_information7) app_prob_unit,
-- modified for numerical value error (bug#4106045)
decode(con.ctr_information12, 'HOUR', con.ctr_information13, ass.frequency) frequency,
decode(con.ctr_information12, 'HOUR', con.ctr_information11, ass.normal_hours) app_hours,
--
-- modifying for bug#4083763
--DECODE(con.ctr_information2,'FIXED_TERM','D','PERMANENT','I') app_contract_cat,
DECODE(con.ctr_information2,'FIXED_TERM','1','PERMANENT','2','NEW_HIRE_CONTRACT','4', decode(OI1.ORG_INFORMATION8, 'Y', '3')) app_contract_cat,
--
TO_CHAR(fnd_date.canonical_to_date(con.ctr_information3),'DDMMYYYY') proposed_end_date,
DECODE(hr_contract_api.get_active_end_date(con.contract_id,l_date,con.status),NULL
,TO_CHAR(fnd_date.canonical_to_date(con.ctr_information3),'DDMMYYYY')
,TO_CHAR(hr_contract_api.get_active_end_date(con.contract_id,l_date,con.status),'DDMMYYYY')) con_end_date,
ext.rowid row_id,
ext.aei_information4 app_titulair,
ext.aei_information5 app_status_before_hire,
ext.aei_information6 app_training_level,
ext.aei_information7 app_proposed_monthly_salary,
ext.aei_information2 app_request_ni,
ext.aei_information8 app_assedic,
ext.aei_information9 app_pt_exon,
ext.aei_information10 app_inform_mt,
ext.aei_information1,
DECODE(ext.aei_information13,'FRF','F','EUR','E') ccy_code,
DECODE(ext.aei_information10,'Y',p_contact_name,' ') app_contact_name, -- work initiative contract
DECODE(ext.aei_information10,'Y',p_contact_telephone,' ') app_contact_telephone, -- work initiative contract
SUBSTR(job.job_information1,1,1) app_emp_code,
SUBSTR(job.job_information1,2,2) app_qual_code,
-- Added for bug #5240132
OI1.org_information20 app_corres_location_id
--
FROM per_addresses adr,
per_contracts_f con,
per_jobs job,
hr_locations_all loc,
hr_ALL_ORGANIZATION_UNITS OU1,
HR_ORGANIZATION_INFORMATION OI1,
per_assignment_extra_info ext,
per_all_assignments_f ass,
per_all_people_f peo
WHERE adr.person_id(+) = peo.person_id
AND adr.primary_flag(+) = 'Y'
AND con.contract_id(+) = ass.contract_id
AND l_date BETWEEN con.effective_start_date(+)
AND con.effective_end_date(+)
AND job.job_id(+) = ass.job_id
AND loc.location_id(+) = ou1.location_id
AND ass.establishment_id = ou1.organization_id
AND ext.aei_information11 = 'Y'
AND ass.assignment_id = ext.assignment_id
AND ext.information_type = 'FR_HIRING_DECLARATION'
AND ass.person_id = peo.person_id
AND l_date BETWEEN peo.effective_start_date AND peo.effective_end_date
and ass.establishment_id = p_establishment_id
AND (l_date BETWEEN ass.effective_start_date AND ass.effective_end_date)
and OU1.ORGANIZATION_ID = OI1.ORGANIZATION_ID (+)
AND OI1.ORG_INFORMATION_CONTEXT (+) = 'FR_ESTAB_INFO'
ORDER BY peo.last_name;
SELECT /*+no_index(ass PER_ASSIGNMENTS_F_FK20)
index(OI1 HR_ORGANIZATION_INFORMATIO_FK2) */
ass.assignment_id ass_id,
ass.establishment_id est_id,
ass.organization_id org_id,
ass.person_id person_id,
ass.period_of_service_id,
ass.application_id,
UPPER(ass.time_normal_start) start_time,
UPPER(OI1.ORG_INFORMATION2) siret,
OI1.ORG_INFORMATION4 monthly_hours,
UPPER(OI1.ORG_INFORMATION3) naf_code,
UPPER(ou1.name) est_name,
UPPER(loc.address_line_1) loc_address1,
UPPER(loc.address_line_2) loc_address2,
-- Added for bug #5240132
UPPER(loc.address_line_3) loc_address3,
--
UPPER(loc.postal_code) loc_postal,
UPPER(loc.town_or_city) loc_town,
loc.telephone_number_1 loc_telephone,
UPPER(NVL(peo.per_information1,peo.last_name)) nom_de_naiss,
peo.last_name nom_marital,
UPPER(peo.first_name) app_first_name,
peo.full_name app_full_name,
peo.sex app_sex,
peo.national_identifier app_ni,
DECODE(ass.period_of_service_id,
NULL,peo.applicant_number,peo.employee_number) app_employee_number,
peo.date_of_birth app_dob,
peo.nationality app_nat_code,
peo.title app_tit,
peo.region_of_birth app_dept_code,
UPPER(peo.town_of_birth) app_town_birth,
peo.country_of_birth,
adr.address_line1 app_address1,
adr.address_line2 app_address2,
-- Added for bug #5240132
adr.address_line3 app_address3,
--
adr.postal_code app_pos_code,
adr.town_or_city app_town,
NVL(con.ctr_information6,to_char(ass.probation_period)) app_prob_period,
DECODE(con.ctr_information6,NULL,ass.probation_unit
,con.ctr_information7) app_prob_unit,
-- modified for numerical value error (bug#4106045)
decode(con.ctr_information12, 'HOUR', con.ctr_information13, ass.frequency) frequency,
decode(con.ctr_information12, 'HOUR', con.ctr_information11, ass.normal_hours) app_hours,
--
-- modifying for bug#4083763
--DECODE(con.ctr_information2,'FIXED_TERM','D','PERMANENT','I') app_contract_cat,
DECODE(con.ctr_information2,'FIXED_TERM','1','PERMANENT','2','NEW_HIRE_CONTRACT','4', decode(OI1.ORG_INFORMATION8, 'Y', '3')) app_contract_cat,
--
TO_CHAR(fnd_date.canonical_to_date(con.ctr_information3),'DDMMYYYY') proposed_end_date,
DECODE(hr_contract_api.get_active_end_date(con.contract_id,l_date,con.status),NULL
,TO_CHAR(fnd_date.canonical_to_date(con.ctr_information3),'DDMMYYYY')
,TO_CHAR(hr_contract_api.get_active_end_date(con.contract_id,l_date,con.status),'DDMMYYYY')) con_end_date,
ext.rowid row_id,
ext.aei_information4 app_titulair,
ext.aei_information5 app_status_before_hire,
ext.aei_information6 app_training_level,
ext.aei_information7 app_proposed_monthly_salary,
ext.aei_information2 app_request_ni,
ext.aei_information8 app_assedic,
ext.aei_information9 app_pt_exon,
ext.aei_information10 app_inform_mt,
ext.aei_information1,
DECODE(ext.aei_information13,'FRF','F','EUR','E') ccy_code,
DECODE(ext.aei_information10,'Y',p_contact_name,' ') app_contact_name, -- work initiative contract
DECODE(ext.aei_information10,'Y',p_contact_telephone,' ') app_contact_telephone, -- work initiative contract
SUBSTR(job.job_information1,1,1) app_emp_code,
SUBSTR(job.job_information1,2,2) app_qual_code,
-- Added for bug #5240132
OI1.org_information20 app_corres_location_id
--
FROM per_addresses adr,
per_contracts_f con,
per_jobs job,
hr_locations_all loc,
hr_ALL_ORGANIZATION_UNITS OU1,
HR_ORGANIZATION_INFORMATION OI1,
per_assignment_extra_info ext,
per_all_assignments_f ass,
per_all_people_f peo
WHERE adr.person_id(+) = peo.person_id
AND adr.primary_flag(+) = 'Y'
AND con.contract_id(+) = ass.contract_id
AND l_date BETWEEN con.effective_start_date(+)
AND con.effective_end_date(+)
AND job.job_id(+) = ass.job_id
AND loc.location_id(+) = ou1.location_id
AND ass.establishment_id = ou1.organization_id
AND ext.aei_information11 = 'Y'
AND ass.assignment_id = ext.assignment_id
AND ext.information_type = 'FR_HIRING_DECLARATION'
AND ass.person_id = peo.person_id
AND l_date BETWEEN peo.effective_start_date AND peo.effective_end_date
and peo.person_id = p_person_id
AND (l_date BETWEEN ass.effective_start_date AND ass.effective_end_date)
and OU1.ORGANIZATION_ID = OI1.ORGANIZATION_ID (+)
AND OI1.ORG_INFORMATION_CONTEXT (+) = 'FR_ESTAB_INFO'
ORDER BY peo.last_name;
SELECT address_line_1,
address_line_2,
postal_code,
town_or_city,
telephone_number_1,
telephone_number_2
FROM hr_locations_all
WHERE location_id = corres_loc_id;
UPDATE per_assignment_extra_info
SET aei_information11 = 'N',
aei_information12 = fnd_date.date_to_canonical(sysdate)
WHERE rowid = r_app.row_id;