[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_query:= 'select per.*'
||' ,per.full_name personname'
||' ,per.last_name personlastname'
||' ,decode(per.sex, ''M'',''1'',''F'',''2'',''9'') gender'
||' ,translate(to_char(per.date_of_birth,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') dateofbirth'
--
-- This cursor retrieves all the Recruiting addresses associated
-- with the person
--
||' ,cursor (select address_line1 addressline1'
||' ,address_line2 addressline2'
||' ,address_line3 addressline3'
||' ,region_1 region1'
||' ,region_2 region2'
||' ,region_3 region3'
||' ,postal_code postalcode'
||' ,town_or_city townorcity'
||' ,country country'
||' ,add_information13'
||' ,add_information14'
||' ,add_information15'
||' ,add_information16'
||' ,add_information17'
||' ,add_information18'
||' ,add_information19'
||' ,add_information20'
||' from per_addresses'
||' where address_type = ''REC'''
||' and person_id=per.person_id) as address'
--
-- This cursor retrieves the telephone numbers that can be passed
-- The decode statement is used to get the correct order by :
-- the translate removes H and W, and changes 1,2 and 3 to Vs
-- as they are irrelevant to the numbering
--
||' ,cursor ( select phn.*'
||' , substr(phone_type,1,1) phonelabel'
||' , decode (translate(phone_type,''123HW'',''VVV'')'
||' ,''V'', ''1'''
||' ,''F'', ''2'''
||' ,''P'', ''3'''
||' ,''1'') phonetype'
||' from per_phones phn'
||' where parent_id=per.person_id'
||' and substr(phone_type, 1,1) in (''H'',''W'',''P'',''M'')) as phone'
||' ,usr.email_address emailaddress'
||' ,cursor(select ppe.*'
||' ,ppj.job_name job_name,ppj.*'
||' ,ppe.employer_name employername'
||' ,ppe.employer_address employeraddress'
||' ,ppj.description descriptionText'
||' ,to_char(ppe.start_date,''RRRR-MM-DD'') attendedstartdate'
||' ,to_char(ppe.end_date,''RRRR-MM-DD'') attendedenddate'
||' from per_previous_employers ppe'
||' ,per_previous_jobs ppj'
||' where ppe.previous_employer_id = ppj.previous_employer_id'
||' and ppe.person_id=per.person_id) as emphistory'
--
-- This cursor retrieves the qualifications held in per_qualifications.
-- 'attendanceid' is used in the stylesheet to determine the section
-- of the generated XML document to place the data.
--
||' ,cursor(select qua.title title,qua.*'
||' ,esa.establishment establishment'
||' ,esa.address address'
||' ,esa.*'
||' ,qtytl.name type'
||' ,decode(qty.category '
||' ,''SKILL'', ''skill'''
||' ,''EXPERIENCE'', ''experience'''
||' ,''EDUCATION'', ''education'''
||' ,''LICENSE'', ''license'''
||' ,''CERTIFICATION'', ''certification'''
||' ,''EQUIPMENT'', ''equipment'''
||' ,''other'') categoryCode'
||' ,to_char(esa.attended_start_date,''RRRR-MM-DD'') attendedstartdate'
||' ,to_char(esa.attended_end_date,''RRRR-MM-DD'') attendedenddate'
||' from per_qualifications_vl qua'
||' ,per_qualification_types qty'
||' ,per_qualification_types_tl qtytl'
||' ,per_establishment_attendances esa'
||' where qua.qualification_type_id = qty.qualification_type_id'
||' and qua.qualification_type_id = qtytl.qualification_type_id'
||' and ppttl.language = qtytl.language'
||' and qua.attendance_id = esa.attendance_id'
||' and qua.person_id = per.person_id'
||' and esa.person_id=per.person_id) as education'
||' ,cursor(select qua.title title,qua.*'
||' ,qty.name type'
||' ,decode(qty.category '
||' ,''SKILL'', ''skill'''
||' ,''EXPERIENCE'', ''experience'''
||' ,''EDUCATION'', ''education'''
||' ,''LICENSE'', ''license'''
||' ,''CERTIFICATION'', ''certification'''
||' ,''EQUIPMENT'', ''equipment'''
||' ,''other'') categoryCode'
||' from per_qualifications_vl qua'
||' ,per_qualification_types qty'
||' ,per_qualification_types_tl qtytl'
||' where qua.qualification_type_id = qty.qualification_type_id'
||' and qua.qualification_type_id = qtytl.qualification_type_id'
||' and ppttl.language = qtytl.language'
||' and qua.attendance_id is null'
||' and qua.person_id=per.person_id) as non_edu_quals'
--
-- This cursor retrieves the skills that are held the competences
-- tables. Only skills that have an associated rating_level are
-- required
--
||' ,cursor(select /*+ INDEX (rtl PER_RATING_LEVELS_PK)'
||' INDEX(cmp PER_COMPETENCES_PK) INDEX(rsc PER_RATING_SCALES_PK)*/ '
||' cel.*,cmpt.name name'
||' , decode(round((rtl.step_value/decode(nvl(nvl(rsc.max_scale_step, cmp.max_level)'
||' ,rtl.step_value),0,1,nvl(nvl(rsc.max_scale_step, cmp.max_level)'
||' ,rtl.step_value)))*5),0,1'
||' , round((rtl.step_value/decode(nvl(nvl(rsc.max_scale_step, cmp.max_level)'
||' ,rtl.step_value),0,1,nvl(nvl(rsc.max_scale_step, cmp.max_level)'
||' ,rtl.step_value)))*5)) lvl'
||' from per_competence_elements cel'
||' , per_competences cmp'
||' , per_competences_tl cmpt'
||' , per_rating_levels rtl'
||' , per_rating_scales rsc'
||' where cel.competence_id = cmp.competence_id'
||' and cel.competence_id = cmpt.competence_id'
||' and ppttl.language= cmpt.language'
||' and cel.proficiency_level_id = rtl.rating_level_id'
||' and rtl.rating_scale_id = rsc.rating_scale_id(+)'
||' and cel.type = ''PERSONAL'''
||' and cel.person_id = per.person_id) as skills'
||' ,cursor(select cel.*,cmp.name name'
||' from per_competence_elements cel'
||' , per_competences_tl cmp'
||' where cel.competence_id = cmp.competence_id'
||' and cmp.language=ppttl.language'
||' and cel.proficiency_level_id is null'
||' and cel.type = ''PERSONAL'''
||' and cel.person_id = per.person_id) as skills_no_level'
||' , cursor(select isc.* '
||' ,cursor(select * from irc_location_criteria_values ilcv'
||' where ilcv.search_criteria_id=isc.search_criteria_id) as locations'
||' ,cursor(select * from irc_prof_area_criteria_values ipacv'
||' where ipacv.search_criteria_id=isc.search_criteria_id) as professional_areas'
||' from irc_search_criteria isc'
||' where isc.object_id=per.person_id'
||' and isc.object_type=''WPREF'') as work_preferences'
||' from per_all_people_f per '
||' ,fnd_user usr '
||' ,per_person_types_tl ppttl '
||' where per.person_id = :1'
||' and usr.employee_id(+)=per.person_id'
||' and trunc(sysdate) between nvl(usr.start_date(+),trunc(sysdate))'
||' and nvl(usr.end_date(+),trunc(sysdate))'
||' and trunc(sysdate) between per.effective_start_date'
||' and per.effective_end_date'
||' and per.person_type_id=ppttl.person_type_id'
||' and ppttl.language=userenv(''LANG'')'
||' and rownum=1';
'
';
'||'
'||'
'||'
'||'
'||'
'||''||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||''||'
'||'
'||'
'||''||'
'||'
'||'
'||''||'
'||'
'||'
'||'
'||' '||'
'||'
'||'
'||'
'||'
'||'
'||''||'
'||'
'||'
'||'
'||'
'||'
'||''||'
'||''||'
'||''||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||' '||'
'||'
'||'
'||' '||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||' '||'
'||'
'||'
'||'
'||'
'||'
'||'
'||'
'||' '||'
'||'
'||'
'||'
'||'
';
select document_id
from irc_documents
where person_id=p_person_id
and type='AUTO_RESUME'
and nvl(assignment_id,-1)=nvl(p_assignment_id,-1)
order by creation_date asc;
select substr(replace(initcap(full_name),' '),1,120)
from per_all_people_f
where person_id=p_person_id
and sysdate between effective_start_date and effective_end_date;
select meaning
from hr_lookups
where lookup_type=p_type
and lookup_code=p_stylesheet;
update irc_documents
set binary_doc=l_binary_doc
,character_doc=empty_clob()
,parsed_xml=l_parsed_xml
,mime_type='text/html'
,file_format='TEXT'
,file_name=l_file_name
,description=l_description
where document_id=l_document_id;
update irc_documents
set binary_doc=l_binary_doc
,parsed_xml=l_parsed_xml
where document_id=l_document_id;