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 745: from per_all_assignments_f asg

741: p_organization_id ota_event_associations.organization_id%type,
742: p_course_start_date otv_scheduled_events.course_start_date%type,
743: p_now date) is
744: select asg.assignment_id
745: from per_all_assignments_f asg
746: where asg.person_id = p_person_id and
747: nvl(p_course_start_date, trunc(p_now)) between asg.effective_start_date and asg.effective_end_date and
748: p_organization_id = asg.organization_id and
749: asg.assignment_type in ('E','A','C');

Line 776: FROM per_all_assignments_f asg,

772: RETURN VARCHAR2 IS
773:
774: CURSOR csr_lrnr_belongs_to_org IS
775: SELECT asg.assignment_id
776: FROM per_all_assignments_f asg,
777: (
778: SELECT p_organization_id AS organization_id
779: FROM dual
780: UNION ALL

Line 798: l_assignment_id per_all_assignments_f.assignment_id%type;

794: WHERE asg.person_id = p_person_id AND
795: asg.organization_id = orgs.organization_id AND
796: asg.assignment_type in ('E','A','C');
797:
798: l_assignment_id per_all_assignments_f.assignment_id%type;
799: BEGIN
800:
801: OPEN csr_lrnr_belongs_to_org;
802: FETCH csr_lrnr_belongs_to_org INTO l_assignment_id;

Line 932: l_assignment_id per_all_assignments_f.assignment_id%TYPE;

928: FUNCTION emp_has_valid_dates(p_person_id per_all_people_f.person_id%TYPE
929: ,p_event_id ota_events.event_id%TYPE)
930: RETURN VARCHAR2 IS
931: l_event_start_date DATE;
932: l_assignment_id per_all_assignments_f.assignment_id%TYPE;
933: BEGIN
934:
935: BEGIN
936: l_event_start_date := get_event_start_date(p_event_id, sysdate);