eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OTA_LEARNER_ACCESS_UTIL dependencies on OTA_EVENT_ASSOCIATIONS

Line 122: from ota_event_associations ea

118: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
119: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
120: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
121: select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
122: from ota_event_associations ea
123: where ea.party_id is null and
124: ea.customer_id is null and
125: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
126: (

Line 136: p_person_id ota_event_associations.person_id%type,

132:
133: /*Bug 7046019- Modified cursor check_mandatory_evt_assoc to include learner groups
134: Cursor check_mandatory_evt_assoc(
135: p_event_id ota_events.event_id%type,
136: p_person_id ota_event_associations.person_id%type,
137: p_as_of in date
138: ) is
139: select ea.event_association_id
140: from

Line 141: ota_event_associations ea,

137: p_as_of in date
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'

Line 161: p_person_id ota_event_associations.person_id%type,

157: );*/
158:
159: Cursor check_mandatory_evt_assoc(
160: p_event_id ota_events.event_id%type,
161: p_person_id ota_event_associations.person_id%type,
162: p_as_of in date
163: ) is
164: select ea.event_association_id
165: from

Line 166: ota_event_associations ea,

162: p_as_of in date
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'

Line 197: from ota_event_associations ea

193: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
194: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
195: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
196: select 1
197: from ota_event_associations ea
198: where ea.party_id = p_party_id and
199: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
200: (
201: ea.event_id = p_event_id or

Line 211: from ota_event_associations ea,

207: cursor csr_category_assoc(
208: p_self_enroll_only varchar2,
209: p_category_usage_id ota_category_usages.category_usage_id%type) is
210: select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
211: from ota_event_associations ea,
212: (
213: select cu.category_usage_id
214: from ota_category_usages cu
215: where cu.type = 'C'

Line 227: p_party_id in ota_event_associations.party_id%type,

223:
224:
225: cursor csr_category_assoc_ext(
226: p_self_enroll_only varchar2,
227: p_party_id in ota_event_associations.party_id%type,
228: p_category_usage_id ota_category_usages.category_usage_id%type) is
229: select 1
230: from ota_event_associations ea,
231: (

Line 230: from ota_event_associations ea,

226: p_self_enroll_only varchar2,
227: p_party_id in ota_event_associations.party_id%type,
228: p_category_usage_id ota_category_usages.category_usage_id%type) is
229: select 1
230: from ota_event_associations ea,
231: (
232: select cu.category_usage_id
233: from ota_category_usages cu
234: where cu.type = 'C'

Line 252: from ota_event_associations ea

248: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
249: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
250: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
251: select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
252: from ota_event_associations ea
253: where ea.customer_id is null and
254: ea.party_id is null and
255: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
256: (

Line 271: from ota_event_associations ea

267: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
268: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
269: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
270: select 1
271: from ota_event_associations ea
272: where ea.party_id = p_party_id and
273: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
274: (
275: ea.learning_path_id = p_learning_path_id or

Line 288: from ota_event_associations ea

284: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
285: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
286: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
287: select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
288: from ota_event_associations ea
289: where ea.customer_id is null and
290: ea.party_id is null and
291: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
292: (

Line 307: from ota_event_associations ea

303: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
304: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
305: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
306: select 1
307: from ota_event_associations ea
308: where ea.party_id = p_party_id and
309: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
310: (
311: ea.forum_id = p_forum_id or

Line 324: from ota_event_associations ea

320: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
321: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
322: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
323: select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
324: from ota_event_associations ea
325: where ea.customer_id is null and
326: ea.party_id is null and
327: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
328: (

Line 343: from ota_event_associations ea

339: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
340: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
341: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
342: select 1
343: from ota_event_associations ea
344: where ea.party_id = p_party_id and
345: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
346: (
347: ea.chat_id = p_chat_id or

Line 360: from ota_event_associations ea

356: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
357: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
358: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
359: select ea.organization_id, ea.org_structure_version_id, ea.job_id, ea.position_id, ea.person_id, ea.self_enrollment_flag, ea.match_type,ea.user_group_id
360: from ota_event_associations ea
361: where ea.customer_id is null and
362: ea.party_id is null and
363: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
364: (

Line 379: from ota_event_associations ea

375: p_parent_cat_usage_id_2 ota_category_usages.category_usage_id%type,
376: p_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type,
377: p_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type) is
378: select 1
379: from ota_event_associations ea
380: where ea.party_id = p_party_id and
381: (p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
382: (
383: ea.certification_id = p_certification_id or

Line 391: p_organization_id ota_event_associations.organization_id%type,

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,
391: p_organization_id ota_event_associations.organization_id%type,
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

Line 392: p_job_id ota_event_associations.job_id%type,

388: validating the active assignment record.
389: cursor csr_asg_details(
390: p_person_id per_people_f.person_id%type,
391: p_organization_id ota_event_associations.organization_id%type,
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

Line 393: p_position_id ota_event_associations.position_id%type,

389: cursor csr_asg_details(
390: p_person_id per_people_f.person_id%type,
391: p_organization_id ota_event_associations.organization_id%type,
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

Line 410: p_organization_id ota_event_associations.organization_id%type,

406:
407:
408: cursor csr_asg_details_recursive(
409: p_person_id per_people_f.person_id%type,
410: p_organization_id ota_event_associations.organization_id%type,
411: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
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

Line 411: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,

407:
408: cursor csr_asg_details_recursive(
409: p_person_id per_people_f.person_id%type,
410: p_organization_id ota_event_associations.organization_id%type,
411: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
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

Line 412: p_job_id ota_event_associations.job_id%type,

408: cursor csr_asg_details_recursive(
409: p_person_id per_people_f.person_id%type,
410: p_organization_id ota_event_associations.organization_id%type,
411: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
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,

Line 413: p_position_id ota_event_associations.position_id%type,

409: p_person_id per_people_f.person_id%type,
410: p_organization_id ota_event_associations.organization_id%type,
411: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
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: (

Line 446: p_organization_id ota_event_associations.organization_id%type,

442: asg.assignment_type in ('E','A','C');*/
443:
444: cursor csr_asg_details(
445: p_person_id per_people_f.person_id%type,
446: p_organization_id ota_event_associations.organization_id%type,
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

Line 447: p_job_id ota_event_associations.job_id%type,

443:
444: cursor csr_asg_details(
445: p_person_id per_people_f.person_id%type,
446: p_organization_id ota_event_associations.organization_id%type,
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

Line 448: p_position_id ota_event_associations.position_id%type,

444: cursor csr_asg_details(
445: p_person_id per_people_f.person_id%type,
446: p_organization_id ota_event_associations.organization_id%type,
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

Line 481: p_organization_id ota_event_associations.organization_id%type,

477:
478:
479: cursor csr_asg_details_recursive(
480: p_person_id per_people_f.person_id%type,
481: p_organization_id ota_event_associations.organization_id%type,
482: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
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

Line 482: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,

478:
479: cursor csr_asg_details_recursive(
480: p_person_id per_people_f.person_id%type,
481: p_organization_id ota_event_associations.organization_id%type,
482: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
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

Line 483: p_job_id ota_event_associations.job_id%type,

479: cursor csr_asg_details_recursive(
480: p_person_id per_people_f.person_id%type,
481: p_organization_id ota_event_associations.organization_id%type,
482: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
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,

Line 484: p_position_id ota_event_associations.position_id%type,

480: p_person_id per_people_f.person_id%type,
481: p_organization_id ota_event_associations.organization_id%type,
482: p_org_structure_version_id ota_event_associations.org_structure_version_id%type,
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: (

Line 610: from ota_event_associations

606: function is_customer_event(p_event_id in ota_events.event_id%type) return boolean is
607:
608: cursor customer_assoc is
609: select 1
610: from ota_event_associations
611: where event_id = p_event_id and
612: customer_id is not null;
613:
614: v_dummy number;

Line 699: p_assoc_person_id in ota_event_associations.person_id%type,

695:
696: function person_matches_assoc(
697: p_person_id in per_people_f.person_id%type,
698: p_as_of in date,
699: p_assoc_person_id in ota_event_associations.person_id%type,
700: p_assoc_organization_id in ota_event_associations.organization_id%type,
701: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
702: p_assoc_job_id in ota_event_associations.job_id%type,
703: p_assoc_position_id in ota_event_associations.position_id%type,

Line 700: p_assoc_organization_id in ota_event_associations.organization_id%type,

696: function person_matches_assoc(
697: p_person_id in per_people_f.person_id%type,
698: p_as_of in date,
699: p_assoc_person_id in ota_event_associations.person_id%type,
700: p_assoc_organization_id in ota_event_associations.organization_id%type,
701: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
702: p_assoc_job_id in ota_event_associations.job_id%type,
703: p_assoc_position_id in ota_event_associations.position_id%type,
704: p_assoc_match_type in ota_event_associations.match_type%type) return boolean is

Line 701: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,

697: p_person_id in per_people_f.person_id%type,
698: p_as_of in date,
699: p_assoc_person_id in ota_event_associations.person_id%type,
700: p_assoc_organization_id in ota_event_associations.organization_id%type,
701: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
702: p_assoc_job_id in ota_event_associations.job_id%type,
703: p_assoc_position_id in ota_event_associations.position_id%type,
704: p_assoc_match_type in ota_event_associations.match_type%type) return boolean is
705: begin

Line 702: p_assoc_job_id in ota_event_associations.job_id%type,

698: p_as_of in date,
699: p_assoc_person_id in ota_event_associations.person_id%type,
700: p_assoc_organization_id in ota_event_associations.organization_id%type,
701: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
702: p_assoc_job_id in ota_event_associations.job_id%type,
703: p_assoc_position_id in ota_event_associations.position_id%type,
704: p_assoc_match_type in ota_event_associations.match_type%type) return boolean is
705: begin
706: if p_assoc_person_id is null then

Line 703: p_assoc_position_id in ota_event_associations.position_id%type,

699: p_assoc_person_id in ota_event_associations.person_id%type,
700: p_assoc_organization_id in ota_event_associations.organization_id%type,
701: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
702: p_assoc_job_id in ota_event_associations.job_id%type,
703: p_assoc_position_id in ota_event_associations.position_id%type,
704: p_assoc_match_type in ota_event_associations.match_type%type) return boolean is
705: begin
706: if p_assoc_person_id is null then
707: if p_assoc_organization_id is not null and p_assoc_match_type = 'CHILD_ORGS' then

Line 704: p_assoc_match_type in ota_event_associations.match_type%type) return boolean is

700: p_assoc_organization_id in ota_event_associations.organization_id%type,
701: p_assoc_org_structure_vrsn_id in ota_event_associations.org_structure_version_id%type,
702: p_assoc_job_id in ota_event_associations.job_id%type,
703: p_assoc_position_id in ota_event_associations.position_id%type,
704: p_assoc_match_type in ota_event_associations.match_type%type) return boolean is
705: begin
706: if p_assoc_person_id is null then
707: if p_assoc_organization_id is not null and p_assoc_match_type = 'CHILD_ORGS' then
708: for an_assignment in csr_asg_details_recursive(p_person_id => p_person_id,

Line 741: p_organization_id ota_event_associations.organization_id%type,

737: p_organization_id in ota_events.organization_id%type) return varchar2 is
738:
739: cursor csr_assignments(
740: p_person_id per_people_f.person_id%type,
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

Line 769: FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,

765:
766: return 'N';
767: end emp_matches_org;
768:
769: FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
770: p_organization_id IN ota_event_associations.organization_id%type,
771: p_person_id IN per_people_f.person_id%type)
772: RETURN VARCHAR2 IS
773:

Line 770: p_organization_id IN ota_event_associations.organization_id%type,

766: return 'N';
767: end emp_matches_org;
768:
769: FUNCTION learner_belongs_to_child_org(p_org_structure_version_id IN ota_event_associations. org_structure_version_id%type,
770: p_organization_id IN ota_event_associations.organization_id%type,
771: p_person_id IN per_people_f.person_id%type)
772: RETURN VARCHAR2 IS
773:
774: CURSOR csr_lrnr_belongs_to_org IS

Line 829: l_event_association_id ota_event_associations.event_association_id%type;

825: l_parent_cat_usage_id_3 ota_category_usages.category_usage_id%type;
826: l_parent_cat_usage_id_4 ota_category_usages.category_usage_id%type;
827:
828: l_is_mandatory_enr_assoc boolean:= false ;
829: l_event_association_id ota_event_associations.event_association_id%type;
830: begin
831: -- Bug 4584737: if no data found, event has expired or does not exist. Return 'N'.
832: begin
833: l_event_start_date := get_event_start_date(p_event_id, l_now);