DBA Data[Home] [Help]

APPS.IRC_JPP_GENERATOR SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 23

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'
        ||' , vac.attribute1'
        ||' , vac.attribute2'
        ||' , vac.attribute3'
        ||' , vac.attribute4'
        ||' , vac.attribute5'
        ||' , vac.attribute6'
        ||' , vac.attribute7'
        ||' , vac.attribute8'
        ||' , vac.attribute9'
        ||' , vac.attribute10'
        ||' , vac.attribute11'
        ||' , vac.attribute12'
        ||' , vac.attribute13'
        ||' , vac.attribute14'
        ||' , vac.attribute15'
        ||' , vac.attribute16'
        ||' , vac.attribute17'
        ||' , vac.attribute18'
        ||' , vac.attribute19'
        ||' , vac.attribute20'
        ||' , vac.attribute_category'
        ||' , 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)||'
Line: 163

        ||' , 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 meaning var_comp from fnd_lookup_values_vl where lookup_type=''IRC_VARIABLE_COMP_ELEMENT'''
        ||'             and lookup_code in ( select variable_comp_lookup vce 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';
Line: 247

'&'||'lt;?xml version = "1.0"?>
Line: 256

'&'||'lt;Id>&'||'lt;/Id>'||
Line: 257

'&'||'lt;Credential>&'||'lt;/Credential>'||
Line: 260

'&'||'lt;Id>&'||'lt;/Id>'||
Line: 263

'&'||'lt;TransactId>&'||'lt;/TransactId>'||
Line: 264

'&'||'lt;TimeStamp>&'||'lt;/TimeStamp>'||
Line: 268

'&'||'lt;PacketId>&'||'lt;/PacketId>'||
Line: 269

'&'||'lt;Action>&'||'lt;/Action>'||
Line: 280

'&'||'lt;JobPositionPostingId idOwner="oracle.com">&'||'lt;/JobPositionPostingId>'||
Line: 282

'&'||'lt;HiringOrgName>&'||'lt;/HiringOrgName>'||
Line: 284

'&'||'lt;SummaryText>&'||'lt;/SummaryText>'||
Line: 289

  '&'||'lt;FormattedName>&'||'lt;/FormattedName>'||
Line: 290

  '&'||'lt;GivenName>&'||'lt;/GivenName>'||
Line: 291

  '&'||'lt;FamilyName>&'||'lt;/FamilyName>'||
Line: 294

  '&'||'lt;TelNumber>&'||'lt;/TelNumber>'||
Line: 297

  '&'||'lt;TelNumber>&'||'lt;/TelNumber>'||
Line: 299

  '&'||'lt;E-mail>&'||'lt;/E-mail>'||
Line: 303

'&'||'lt;Description>&'||'lt;/Description>'||
Line: 308

'&'||'lt;Date>&'||'lt;/Date>'||
Line: 311

'&'||'lt;Date>&'||'lt;/Date>'||
Line: 316

'&'||'lt;GivenName>&'||'lt;/GivenName>'||
Line: 317

'&'||'lt;FamilyName>&'||'lt;/FamilyName>'||
Line: 319

'&'||'lt;E-mail>&'||'lt;/E-mail>'||
Line: 324

'&'||'lt;JobPositionTitle>&'||'lt;/JobPositionTitle>'||
Line: 326

'&'||'lt;JobPositionPurpose>&'||'lt;/JobPositionPurpose>'||
Line: 329

'&'||'lt;CountryCode>&'||'lt;/CountryCode>'||
Line: 330

'&'||'lt;PostalCode> &'||'lt;/PostalCode>'||
Line: 331

'&'||'lt;Region>&'||'lt;/Region>'||
Line: 332

'&'||'lt;Region>&'||'lt;/Region>'||
Line: 333

'&'||'lt;Region>&'||'lt;/Region>'||
Line: 334

'&'||'lt;Municipality>&'||'lt;/Municipality>'||
Line: 336

'&'||'lt;AddressLine>&'||'lt;/AddressLine>'||
Line: 337

'&'||'lt;AddressLine>&'||'lt;/AddressLine>'||
Line: 338

'&'||'lt;AddressLine>&'||'lt;/AddressLine>'||
Line: 376

    '&'||'lt;SummaryText>&'||'lt;/SummaryText>'||
Line: 379

    '&'||'lt;SummaryText>&'||'lt;/SummaryText>'||
Line: 398

        '&'||'lt;SalaryMonthly currency="">'||
Line: 399

        '&'||'lt;/SalaryMonthly>'||
Line: 402

        '&'||'lt;RatePerHour currency="">'||
Line: 403

        '&'||'lt;/RatePerHour>  '||
Line: 404

        '&'||'lt;SalaryAnnual currency="">'||
Line: 405

        '&'||'lt;/SalaryAnnual> '||
Line: 413

  '&'||'lt;P>&'||'lt;/P>'||
Line: 415

    ''||
    '&'||'lt;LI>&'||'lt;/LI>'||
Line: 426

' '||
' '||
'&'||'lt;/SummaryText>'||
Line: 437

''||
'&'||'lt;LI>&'||'lt;Qualification>&'||'lt;/Qualification>&'||'lt;/LI>'||
Line: 445

''||
'&'||'lt;LI>&'||'lt;Qualification>&'||'lt;/Qualification>&'||'lt;/LI>'||
Line: 449

'&'||'lt;PercentageOfTime>'||
Line: 452

'&'||'lt;SummaryText>&'||'lt;/SummaryText>'||
Line: 461

'&'||'lt;URL>&'||'lt;/URL>'||
Line: 464

'&'||'lt;SummaryText>&'||'lt;/SummaryText>'||
Line: 470

'&'||'lt;NumberToFill>&'||'lt;/NumberToFill>'||
Line: 562

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.business_group_id'
        ||' , 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'
        ||' , (select name from hr_all_organization_units where organization_id=vac.business_group_id) organization_name'
        ||' , 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)||'
        ||' ''&p_spid=''||to_char(ipc.posting_content_id)||';
Line: 657

        ||'      , cursor (select irc_utilities_pkg.removeTags(pc.name) name '
        ||'                     , pce.mandatory '
        ||'                     , pc.competence_id '
        ||'                     , pce.competence_element_id '
        ||'                     , pc.*'
        ||'                     , pce.*'
        ||'                , 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 meaning var_comp from fnd_lookup_values_vl where lookup_type=''IRC_VARIABLE_COMP_ELEMENT'''
        ||'             and lookup_code in ( select variable_comp_lookup vce 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';