APPS.IRC_RECRUITMENT_SUMMARY_PKG dependencies on IRC_ASSIGNMENT_STATUSES

Line 525: irc_assignment_statuses ias,

521: union
522: --
523: SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'TO_BE_PROCESSED_APPLICATIONS'
524: FROM per_all_assignments_f pasg ,
525: irc_assignment_statuses ias,
526: per_assignment_status_types past
527: WHERE pasg.vacancy_id = l_vacancy_id
528: AND pasg.assignment_type = 'A'
529: AND pasg.assignment_id = ias.assignment_id

Line 532: from irc_assignment_statuses ias2

528: AND pasg.assignment_type = 'A'
529: AND pasg.assignment_id = ias.assignment_id
530: AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
531: AND not exists (select 1
532: from irc_assignment_statuses ias2
533: where ias2.assignment_id=pasg.assignment_id
534: and ias2.status_change_date>ias.status_change_date)
535: AND past.per_system_status = 'ACTIVE_APL'
536: AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date

Line 542: irc_assignment_statuses ias,

538: union
539: --
540: SELECT DISTINCT pasg.vacancy_id,pasg.assignment_id,'IN_PROGRESS_APPLICATIONS'
541: FROM per_all_assignments_f pasg ,
542: irc_assignment_statuses ias,
543: per_assignment_status_types past
544: WHERE pasg.vacancy_id = l_vacancy_id
545: AND pasg.assignment_type = 'A'
546: AND pasg.assignment_id = ias.assignment_id

Line 549: from irc_assignment_statuses ias2

545: AND pasg.assignment_type = 'A'
546: AND pasg.assignment_id = ias.assignment_id
547: AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
548: AND not exists (select 1
549: from irc_assignment_statuses ias2
550: where ias2.assignment_id=pasg.assignment_id
551: and ias2.status_change_date>ias.status_change_date)
552: AND past.per_system_status IN ('INTERVIEW1', 'INTERVIEW2', 'OFFER','ACCEPTED')
553: AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date

Line 775: irc_assignment_statuses ias,

771: union
772: --
773: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'TO_BE_PROCESSED_APPLICATIONS'
774: FROM per_all_assignments_f pasg ,
775: irc_assignment_statuses ias,
776: per_assignment_status_types past,
777: irc_vac_summary ivs
778: WHERE pasg.vacancy_id = ivs.vacancy_id
779: AND pasg.assignment_type = 'A'

Line 783: from irc_assignment_statuses ias2

779: AND pasg.assignment_type = 'A'
780: AND pasg.assignment_id = ias.assignment_id
781: AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
782: AND not exists (select 1
783: from irc_assignment_statuses ias2
784: where ias2.assignment_id=pasg.assignment_id
785: and ias2.status_change_date>ias.status_change_date)
786: AND past.per_system_status = 'ACTIVE_APL'
787: AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date

Line 794: irc_assignment_statuses ias,

790: union
791: --
792: SELECT DISTINCT ivs.vacancy_id,pasg.assignment_id,'IN_PROGRESS_APPLICATIONS'
793: FROM per_all_assignments_f pasg ,
794: irc_assignment_statuses ias,
795: per_assignment_status_types past,
796: irc_vac_summary ivs
797: WHERE pasg.vacancy_id = ivs.vacancy_id
798: AND pasg.assignment_type = 'A'

Line 802: from irc_assignment_statuses ias2

798: AND pasg.assignment_type = 'A'
799: AND pasg.assignment_id = ias.assignment_id
800: AND ias.assignment_status_type_id = past.assignment_status_type_id(+)
801: AND not exists (select 1
802: from irc_assignment_statuses ias2
803: where ias2.assignment_id=pasg.assignment_id
804: and ias2.status_change_date>ias.status_change_date)
805: AND past.per_system_status IN ('INTERVIEW1', 'INTERVIEW2', 'OFFER','ACCEPTED')
806: AND trunc(sysdate) between pasg.effective_start_date and pasg.effective_end_date