[Home] [Help]
242: l_execution_stmt :=
243: 'select 1
244: from per_all_assignments_f ASSIGNMENT,
245: per_all_people_f PERSON,
246: per_person_type_usages_f PERSON_TYPE
247: where ASSIGNMENT.assignment_id = :asg_id
248: and to_date(:asg_eff_date,''dd-mon-yyyy'')
249: between ASSIGNMENT.effective_start_date
250: and ASSIGNMENT.effective_end_date
4035: from per_contact_relationships pcr
4036: where pcr.contact_person_id = papf.person_id)
4037: and not exists
4038: (select null
4039: from per_person_type_usages_f ptuf,
4040: per_person_types ppt
4041: where ppt.system_person_type = 'IRC_REG_USER'
4042: and ptuf.person_type_id = ppt.person_type_id
4043: and ptuf.person_id = papf.person_id);
4148: -- Get all candidates (registered through iRecruitment) and excluding
4149: -- related contacts if any.
4150: cursor csr_candidates is
4151: select ptuf.person_id
4152: from per_person_type_usages_f ptuf,
4153: per_person_types ppt
4154: where ppt.system_person_type = 'IRC_REG_USER'
4155: and ptuf.person_type_id = ppt.person_type_id
4156: and ppt.business_group_id + 0 = nvl(p_business_group_id,ppt.business_group_id)--fix for bug 5222441.
4826: l_temp_clause := and_clause(l_temp_clause, and_clause(
4827: 'EXISTS (SELECT NULL
4828: FROM per_all_assignments_f ASSIGNMENT,
4829: per_all_people_f PERSON,
4830: per_person_type_usages_f PERSON_TYPE
4831: WHERE ASSIGNMENT.rowid = hr_asg.rowid
4832: AND ASSIGNMENT.ASSIGNMENT_ID = hr_asg.assignment_id
4833: AND ASSIGNMENT.ASSIGNMENT_TYPE IN (''A'',''C'',''E'')
4834: /* For the bug 6196437 */
5147: l_temp_clause := and_clause(l_temp_clause, and_clause(
5148: 'EXISTS (SELECT NULL
5149: FROM per_all_assignments_f ASSIGNMENT,
5150: per_all_people_f PERSON,
5151: per_person_type_usages_f PERSON_TYPE
5152: WHERE ASSIGNMENT.rowid = hr_asg.rowid
5153: AND ASSIGNMENT.ASSIGNMENT_ID = hr_asg.assignment_id /* For the bug 6196437 */
5154: AND PERSON.person_id = ASSIGNMENT.person_id
5155: AND :effective_date