APPS.OTA_LEARNER_ACCESS_UTIL dependencies on PER_ALL_ASSIGNMENTS_F

Line 142: per_all_assignments_f paf

138: ) is
139: select ea.event_association_id
140: from
141: ota_event_associations ea,
142: per_all_assignments_f paf
143: where ea.event_id = p_event_id
144: and paf.person_id = p_person_id
145: AND nvl(ea.mandatory_enrollment_flag,'N') = 'Y'
146: AND paf.assignment_type in ('E','A','C')

Line 167: per_all_assignments_f paf

163: ) is
164: select ea.event_association_id
165: from
166: ota_event_associations ea,
167: per_all_assignments_f paf
168: where ea.event_id = p_event_id
169: and paf.person_id = p_person_id
170: AND nvl(ea.mandatory_enrollment_flag,'N') = 'Y'
171: AND paf.assignment_type in ('E','A','C')

Line 386: /*Bug6679100-As exemployees have 2 records in per_all_assignments_f

382: (
383: ea.certification_id = p_certification_id or
384: ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
385:
386: /*Bug6679100-As exemployees have 2 records in per_all_assignments_f
387: modified csr_asg_details and csr_asg_details_recursive to use system_person_type while
388: validating the active assignment record.
389: cursor csr_asg_details(
390: p_person_id per_people_f.person_id%type,

Line 396: from per_all_assignments_f asg

392: p_job_id ota_event_associations.job_id%type,
393: p_position_id ota_event_associations.position_id%type,
394: p_as_of date) is
395: select asg.assignment_id
396: from per_all_assignments_f asg
397: where asg.person_id = p_person_id
398: AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
399: AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
400: OR nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date )

Line 416: from per_all_assignments_f asg,

412: p_job_id ota_event_associations.job_id%type,
413: p_position_id ota_event_associations.position_id%type,
414: p_as_of date) is
415: select asg.assignment_id
416: from per_all_assignments_f asg,
417: (
418: select p_organization_id as organization_id
419: from dual
420: union all

Line 451: from per_all_assignments_f asg

447: p_job_id ota_event_associations.job_id%type,
448: p_position_id ota_event_associations.position_id%type,
449: p_as_of date) is
450: select asg.assignment_id
451: from per_all_assignments_f asg
452: ,per_person_types ppt
453: ,per_all_people_f perp
454: ,per_person_type_usages_f ptu
455: where asg.person_id = p_person_id

Line 487: from per_all_assignments_f asg,

483: p_job_id ota_event_associations.job_id%type,
484: p_position_id ota_event_associations.position_id%type,
485: p_as_of date) is
486: select asg.assignment_id
487: from per_all_assignments_f asg,
488: (
489: select p_organization_id as organization_id
490: from dual
491: union all

Line 727: from per_all_assignments_f asg

723: p_organization_id ota_event_associations.organization_id%type,
724: p_course_start_date otv_scheduled_events.course_start_date%type,
725: p_now date) is
726: select asg.assignment_id
727: from per_all_assignments_f asg
728: where asg.person_id = p_person_id and
729: nvl(p_course_start_date, trunc(p_now)) between asg.effective_start_date and asg.effective_end_date and
730: p_organization_id = asg.organization_id and
731: asg.assignment_type in ('E','A','C');

Line 758: FROM per_all_assignments_f asg,

754: RETURN VARCHAR2 IS
755:
756: CURSOR csr_lrnr_belongs_to_org IS
757: SELECT asg.assignment_id
758: FROM per_all_assignments_f asg,
759: (
760: SELECT p_organization_id AS organization_id
761: FROM dual
762: UNION ALL

Line 780: l_assignment_id per_all_assignments_f.assignment_id%type;

776: WHERE asg.person_id = p_person_id AND
777: asg.organization_id = orgs.organization_id AND
778: asg.assignment_type in ('E','A','C');
779:
780: l_assignment_id per_all_assignments_f.assignment_id%type;
781: BEGIN
782:
783: OPEN csr_lrnr_belongs_to_org;
784: FETCH csr_lrnr_belongs_to_org INTO l_assignment_id;

Line 945: l_assignment_id per_all_assignments_f.assignment_id%TYPE;

941: FUNCTION emp_has_valid_dates(p_person_id per_all_people_f.person_id%TYPE
942: ,p_event_id ota_events.event_id%TYPE)
943: RETURN VARCHAR2 IS
944: l_event_start_date DATE;
945: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
946: BEGIN
947:
948: BEGIN
949: l_event_start_date := get_event_start_date(p_event_id, sysdate);