[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_query:= 'select hr_xml_packet_id_s.nextval packetId'
||' , ''CREATE'' action'
||' , hr_xml_transaction_id_s.nextval transactionid'
||' , translate(to_char(sysdate,''RRRR-MM-DD HH:MM:SS''),'' '',''T'') timestamp'
||' , rse.posting_url vendorURL'
||' , decode(rse.posting_username '
||' ,''#USERID'',to_char(sndr_usr.user_id) '
||' ,''#USERNAME'',sndr_usr.user_name '
||' ,''#EMAIL'',nvl(sndr_per.email_address,sndr_usr.email_address)'
||' , rse.posting_username) posting_username'
||' , decode(fnd_vault.get(''IRC_SITE'',rse.recruiting_site_id )'
||' ,''#USERID'',to_char(sndr_usr.user_id) '
||' ,''#USERNAME'',sndr_usr.user_name '
||' ,''#EMAIL'',nvl(sndr_per.email_address,sndr_usr.email_address)'
||' , fnd_vault.get(''IRC_SITE'',rse.recruiting_site_id) )posting_password'
||' , rse.stylesheet '
||' , sndr_per.last_name personlastname'
||' , sndr_per.first_name personfirstname'
||' , sndr_per.email_address emailaddress'
||' , sndr_usr.user_id userid'
||' , recr_pp.first_name recr_first_name'
||' , recr_pp.last_name recr_last_name'
||' , recr_pp.full_name recr_full_name'
||' , recr_pp.email_address recr_email_address'
||' , recr_phn.phone_number recr_phone_number'
||' , recr_phn_fax.phone_number recr_fax_number'
||' , recr_pp.person_id recr_person_id'
||' , vac.number_of_openings'
||' , vac.name vacancy_name'
||' , vac.budget_measurement_type'
||' , vac.budget_measurement_value'
||' , to_char(vac.date_from,''RRRR-MM-DD'') vacancy_start_date'
||' , to_char(vac.date_to,''RRRR-MM-DD'') vacancy_end_date'
||' , rtrim(nvl(fnd_profile.value(''IRC_FRAMEWORK_AGENT'')'
||' ,fnd_profile.value(''APPS_FRAMEWORK_AGENT''))||'
||' fnd_profile.value(''ICX_PREFIX''),''/'')||''/OA_HTML/OA.jsp?OAFunc=''||'
||' fnd_profile.value(''IRC_JOB_NOTIFICATION_URL'')||'
||' ''&p_svid=''||to_char(vac.vacancy_id)||'
||' , cursor (select pc.name '
||' , pce.mandatory '
||' , pc.competence_id '
||' , pce.competence_element_id '
||' , prl1.step_value min_level_id '
||' , prl2.step_value max_level_id '
||' , prl1.name min_level '
||' , prl2.name max_level'
||' from per_competences_tl pc'
||' , per_competence_elements pce'
||' , per_rating_levels prl1'
||' , per_rating_levels prl2'
||' where pc.language=ipctl.source_language'
||' and pc.competence_id = pce.competence_id '
||' and pce.type = ''REQUIREMENT'''
||' and pce.object_name = ''VACANCY'''
||' and pce.object_id = vac.vacancy_id'
||' and prl1.rating_level_id(+) = pce.proficiency_level_id'
||' and prl2.rating_level_id(+) = pce.high_proficiency_level_id'
||' and vac.date_from '
||' between nvl(pce.effective_date_from, vac.date_from)'
||' and nvl(pce.effective_date_to, vac.date_from)'
||' ORDER BY pce.mandatory, pc.name DESC'
||' ) competences'
-- cursor to get the variable comp element
||' , cursor (select hr_general.decode_lookup(''IRC_VARIABLE_COMP_ELEMENT'' ,vce.variable_comp_lookup) var_comp'
||' from irc_variable_comp_elements vce'
||' where vac.vacancy_id = vce.vacancy_id'
||' ) comp_elements'
||' from irc_posting_contents_tl ipctl'
||' , irc_posting_contents ipc'
||' , per_recruitment_activities rec'
||' , per_recruitment_activity_for recf'
||' , per_all_vacancies vac'
||' , hr_locations_all_vl loc'
||' , irc_search_criteria irc_isc'
||' , irc_all_recruiting_sites rse'
||' , fnd_user sndr_usr'
||' , per_all_people_f sndr_per'
||' , per_all_people_f recr_pp'
||' , per_phones recr_phn'
||' , per_phones recr_phn_fax'
||' where rec.recruitment_activity_id=:1'
||' and recf.vacancy_id = vac.vacancy_id'
||' and not exists (select 1 from per_recruitment_activity_for recf2'
||' where recf2.recruitment_activity_id =rec.recruitment_activity_id'
||' and recf2.recruitment_activity_for_id>recf.recruitment_activity_for_id)'
||' and rec.recruitment_activity_id = recf.recruitment_activity_id'
||' and ipc.posting_content_id = rec.posting_content_id'
||' and ipctl.posting_content_id = ipc.posting_content_id '
||' and ipctl.source_language = userenv(''LANG'')'
||' and rse.recruiting_site_id = rec.recruiting_site_id'
||' and sndr_usr.user_id = :2'
||' and sndr_usr.employee_id=sndr_per.person_id'
||' and trunc(sysdate)'
||' between sndr_per.effective_start_date and '
||' sndr_per.effective_end_date'
||' and vac.recruiter_id = recr_pp.person_id(+) '
||' and vac.recruiter_id = recr_phn.parent_id(+) '
||' and recr_phn.parent_table(+) = ''PER_ALL_PEOPLE_F'''
||' and recr_phn.phone_type(+) = ''W1'''
||' and vac.recruiter_id = recr_phn_fax.parent_id(+) '
||' and recr_phn_fax.parent_table(+) = ''PER_ALL_PEOPLE_F'''
||' and recr_phn_fax.phone_type(+) = ''WF'''
||' and trunc(sysdate)'
||' between nvl(recr_pp.effective_start_date,trunc(sysdate)) '
||' and nvl(recr_pp.effective_end_date,trunc(sysdate))'
||' and trunc(sysdate)'
||' between nvl(recr_phn.date_from,trunc(sysdate)) '
||' and nvl(recr_phn.date_to, trunc(sysdate)) '
||' and trunc(sysdate)'
||' between nvl(recr_phn_fax.date_from,trunc(sysdate)) '
||' and nvl(recr_phn_fax.date_to, trunc(sysdate)) '
||' and vac.location_id = loc.location_id (+)'
||' and vac.vacancy_id = irc_isc.object_id (+)'
||' and irc_isc.object_type(+) = ''VACANCY'''
||' and rownum=1';
'&'||'lt;?xml version = "1.0"?>
'&'||'lt;Id>
'&'||'lt;Credential>
'&'||'lt;Id>
'&'||'lt;TransactId>
'&'||'lt;TimeStamp>
'&'||'lt;PacketId>
'&'||'lt;Action>
'&'||'lt;JobPositionPostingId idOwner="oracle.com">
'&'||'lt;HiringOrgName>
'&'||'lt;SummaryText>
'&'||'lt;FormattedName>
'&'||'lt;GivenName>
'&'||'lt;FamilyName>
'&'||'lt;TelNumber>
'&'||'lt;TelNumber>
'&'||'lt;E-mail>
'&'||'lt;Description>
'&'||'lt;Date>
'&'||'lt;Date>
'&'||'lt;GivenName>
'&'||'lt;FamilyName>
'&'||'lt;E-mail>
'&'||'lt;JobPositionTitle>
'&'||'lt;JobPositionPurpose>
'&'||'lt;CountryCode>
'&'||'lt;PostalCode>
'&'||'lt;Region>
'&'||'lt;Region>
'&'||'lt;Region>
'&'||'lt;Municipality>
'&'||'lt;AddressLine>
'&'||'lt;AddressLine>
'&'||'lt;AddressLine>
'&'||'lt;SummaryText>
'&'||'lt;SummaryText>
'&'||'lt;SalaryMonthly currency="
'
'&'||'lt;RatePerHour currency="
''||
'&'||'lt;SalaryAnnual currency="
'
'&'||'lt;P>
''||
'&'||'lt;LI> '||
' '||
' '||
'&'||'lt;/SummaryText>'||
''||
'&'||'lt;LI>&'||'lt;Qualification> '||
''||
'&'||'lt;LI>&'||'lt;Qualification> '||
'&'||'lt;PercentageOfTime>
'&'||'lt;SummaryText>
'&'||'lt;URL>
'&'||'lt;SummaryText>
'&'||'lt;NumberToFill>