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_MANDATORY_ENROLL_UTIL dependencies on OTA_MAND_MULTI_ENR_REQ_MEMBERS

Line 435: FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS

431: RETURN Boolean IS
432:
433: CURSOR lrnr_already_selected IS
434: SELECT assignment_id
435: FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS
436: WHERE
437: person_id = p_person_id
438: AND event_id = p_event_id
439: AND create_enrollment = 'Y';

Line 487: FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS

483: RETURN Boolean IS
484:
485: CURSOR lrnr_already_selected IS
486: SELECT assignment_id
487: FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS
488: WHERE
489: person_id = p_person_id
490: AND certification_id = p_certification_id
491: AND create_enrollment = 'Y';

Line 704: PROCEDURE create_request_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,

700: hr_utility.set_location(' Leaving:'||l_proc, 10);
701:
702: end create_request_member_record;
703:
704: PROCEDURE create_request_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,
705: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
706: req_event_id IN ota_events.event_id%type,
707: req_enr_prereq_type IN varchar2,
708: lrnr_completed_crs_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_course_prereq%type,

Line 705: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,

701:
702: end create_request_member_record;
703:
704: PROCEDURE create_request_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,
705: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
706: req_event_id IN ota_events.event_id%type,
707: req_enr_prereq_type IN varchar2,
708: lrnr_completed_crs_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_course_prereq%type,
709: lrnr_completed_comp_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_competence_prereq%type,

Line 708: lrnr_completed_crs_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_course_prereq%type,

704: PROCEDURE create_request_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,
705: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
706: req_event_id IN ota_events.event_id%type,
707: req_enr_prereq_type IN varchar2,
708: lrnr_completed_crs_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_course_prereq%type,
709: lrnr_completed_comp_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_competence_prereq%type,
710: l_numberof_records_processed IN OUT NOCOPY NUMBER,
711: p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS
712:

Line 709: lrnr_completed_comp_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_competence_prereq%type,

705: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
706: req_event_id IN ota_events.event_id%type,
707: req_enr_prereq_type IN varchar2,
708: lrnr_completed_crs_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_course_prereq%type,
709: lrnr_completed_comp_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_competence_prereq%type,
710: l_numberof_records_processed IN OUT NOCOPY NUMBER,
711: p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS
712:
713: CURSOR csr_get_assignment_info(l_person_id NUMBER) IS

Line 711: p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS

707: req_enr_prereq_type IN varchar2,
708: lrnr_completed_crs_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_course_prereq%type,
709: lrnr_completed_comp_prereq OTA_MAND_MULTI_ENR_REQ_MEMBERS.completed_competence_prereq%type,
710: l_numberof_records_processed IN OUT NOCOPY NUMBER,
711: p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS
712:
713: CURSOR csr_get_assignment_info(l_person_id NUMBER) IS
714: SELECT paf.organization_id,
715: paf.business_group_id,

Line 747: l_person_action_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type := NULL;

743: l_completed_crs_prereq varchar2(1):= null;
744: l_completed_comp_prereq varchar2(1) :=null;
745: l_proc varchar2(72) := g_package||'create_request_member_record';
746:
747: l_person_action_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type := NULL;
748:
749: Cursor C_Sel1 is select OTA_PERSON_ACTIONS_S.nextval from sys.dual;
750:
751: BEGIN

Line 794: INSERT INTO OTA_MAND_MULTI_ENR_REQ_MEMBERS(mandatory_enr_request_id,person_id,assignment_id,error_message,

790: Open C_Sel1;
791: Fetch C_Sel1 Into l_person_action_id;
792: Close C_Sel1;
793:
794: INSERT INTO OTA_MAND_MULTI_ENR_REQ_MEMBERS(mandatory_enr_request_id,person_id,assignment_id,error_message,
795: completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,mbr_bg_id,
796: person_action_id,action_status_cd)
797: VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,l_completed_crs_prereq,
798: l_completed_comp_prereq, l_create_enrollment,req_event_id,l_assignment_info.organization_id,l_assignment_info.business_group_id,

Line 1705: --All the records to be processed are now present IN OTA_MAND_MULTI_ENR_REQ_MEMBERS with create_enrollment = 'Y'.

1701: raise OTA_NO_PERSON_TO_PROCESS;
1702:
1703: --
1704: end if; --2
1705: --All the records to be processed are now present IN OTA_MAND_MULTI_ENR_REQ_MEMBERS with create_enrollment = 'Y'.
1706: --The records which cannot be created due to unfulfilled course/competence prereq have the respective flags set to 'N'
1707: --and create_enrollment IS set to 'N'
1708:
1709:

Line 1885: OTA_MAND_MULTI_ENR_REQ_MEMBERS reqmembers

1881: reqmembers.MBR_BG_ID,
1882: reqmembers.CERTIFICATION_ID,
1883: reqmembers.CREATE_ENROLLMENT
1884: FROM
1885: OTA_MAND_MULTI_ENR_REQ_MEMBERS reqmembers
1886: WHERE
1887: reqmembers.create_enrollment = 'Y'
1888: AND reqmembers.person_action_id BETWEEN l_start_person_action_id AND l_end_person_action_id
1889: ORDER BY certification_id;

Line 1917: l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;

1913: AND ran.BATCH_ACTION_ID = p_conc_reqId
1914: AND rownum < 2
1915: FOR UPDATE OF ran.range_status_cd;
1916:
1917: l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;
1918: l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;
1919: l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
1920: l_booking_status ota_booking_status_types_tl.name%type;
1921:

Line 1918: l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;

1914: AND rownum < 2
1915: FOR UPDATE OF ran.range_status_cd;
1916:
1917: l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;
1918: l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;
1919: l_booking_status_type_id ota_booking_status_types.booking_status_type_id%type;
1920: l_booking_status ota_booking_status_types_tl.name%type;
1921:
1922: l_request_rec get_all_mandatory_enr_requests%rowtype;

Line 1998: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS

1994: ,p_is_mandatory_enrollment => 'Y');
1995: EXCEPTION
1996: WHEN OTHERS THEN
1997: l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating Enrollment ');
1998: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
1999: SET error_message = l_error_message
2000: WHERE person_id = l_req_member_rec.person_id
2001: AND event_id = l_req_member_rec.event_id
2002: AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line 2013: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS

2009: IF l_booking_id IS NOT NULL THEN
2010: FND_FILE.PUT_LINE(FND_FILE.LOG,l_person_name||' | '||l_class_name);
2011: --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
2012:
2013: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
2014: SET action_status_cd = 'P',error_message = NULL
2015: WHERE person_id = l_req_member_rec.person_id
2016: AND assignment_id = l_req_member_rec.assignment_id
2017: AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id

Line 2026: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS

2022: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Error when creating enrollment into class- '||l_class_name);
2023: --FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
2024: --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
2025:
2026: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
2027: SET error_message = l_error_message, action_status_cd = 'E'
2028: WHERE person_id = l_req_member_rec.person_id
2029: AND event_id = l_req_member_rec.event_id
2030: AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line 2187: FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm

2183: and conc_program_request_id = p_conc_reqId ;
2184:
2185: CURSOR csr_get_error_learners(p_event_id IN number) IS
2186: SELECT COUNT(distinct person_id)
2187: FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm
2188: WHERE reqm.event_id = p_event_id
2189: AND(reqm.create_enrollment = 'N' or reqm.error_message IS NOT NULL);
2190:
2191:

Line 2194: FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm

2190:
2191:
2192: CURSOR csr_get_successful_learners(p_event_id IN number) IS
2193: SELECT COUNT( distinct person_id)
2194: FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm
2195: WHERE reqm.event_id = p_event_id
2196: AND(reqm.create_enrollment = 'Y' and reqm.error_message IS NULL);
2197:
2198: l_error_learners NUMBER := 0;

Line 2932: --All the records to be processed are now present IN OTA_MAND_MULTI_ENR_REQ_MEMBERS with create_enrollment = 'Y'.

2928: raise OTA_NO_PERSON_TO_PROCESS;
2929:
2930: --
2931: end if; --2
2932: --All the records to be processed are now present IN OTA_MAND_MULTI_ENR_REQ_MEMBERS with create_enrollment = 'Y'.
2933: --The records which cannot be created due to unfulfilled course/competence prereq have the respective flags set to 'N'
2934: --and create_enrollment IS set to 'N'
2935:
2936: create_cert_subscriptions(p_conc_request_id);

Line 3015: PROCEDURE create_cert_req_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,

3011:
3012: END learner_can_enroll_in_cert;
3013:
3014:
3015: PROCEDURE create_cert_req_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,
3016: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
3017: req_cert_id IN ota_cert_enrollments.certification_id%type,
3018: l_numberof_records_processed IN OUT NOCOPY NUMBER,
3019: l_create_enrollment IN varchar2 default 'Y',

Line 3016: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,

3012: END learner_can_enroll_in_cert;
3013:
3014:
3015: PROCEDURE create_cert_req_member_record(l_person_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.person_id%type,
3016: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
3017: req_cert_id IN ota_cert_enrollments.certification_id%type,
3018: l_numberof_records_processed IN OUT NOCOPY NUMBER,
3019: l_create_enrollment IN varchar2 default 'Y',
3020: p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS

Line 3020: p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS

3016: req_mandatory_enr_request_id IN OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type,
3017: req_cert_id IN ota_cert_enrollments.certification_id%type,
3018: l_numberof_records_processed IN OUT NOCOPY NUMBER,
3019: l_create_enrollment IN varchar2 default 'Y',
3020: p_person_action_id OUT NOCOPY OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type) IS
3021:
3022: CURSOR csr_get_assignment_info(l_person_id NUMBER) IS
3023: SELECT paf.organization_id,
3024: paf.business_group_id,

Line 3055: l_person_action_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type := NULL;

3051: l_completed_crs_prereq varchar2(1):= null;
3052: l_completed_comp_prereq varchar2(1) :=null;
3053: l_proc varchar2(72) := g_package||'create_cert_req_member_record';
3054:
3055: l_person_action_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.PERSON_ACTION_ID%type := NULL;
3056:
3057: Cursor C_Sel1 is select OTA_PERSON_ACTIONS_S.nextval from sys.dual;
3058:
3059: BEGIN

Line 3075: INSERT INTO OTA_MAND_MULTI_ENR_REQ_MEMBERS(mandatory_enr_request_id,person_id,assignment_id,error_message,

3071: --FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting into req members');
3072: Open C_Sel1;
3073: Fetch C_Sel1 Into l_person_action_id;
3074: Close C_Sel1;
3075: INSERT INTO OTA_MAND_MULTI_ENR_REQ_MEMBERS(mandatory_enr_request_id,person_id,assignment_id,error_message,
3076: completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,mbr_bg_id,certification_id,
3077: person_action_id,action_status_cd)
3078: VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,l_completed_crs_prereq,l_completed_comp_prereq,
3079: l_create_enrollment,NULL,l_assignment_info.organization_id,ota_general.get_business_group_id,req_cert_id,

Line 3108: OTA_MAND_MULTI_ENR_REQ_MEMBERS reqmembers--,

3104: reqmembers.MBR_BG_ID,
3105: reqmembers.CERTIFICATION_ID,
3106: reqmembers.CREATE_ENROLLMENT
3107: FROM
3108: OTA_MAND_MULTI_ENR_REQ_MEMBERS reqmembers--,
3109: --OTA_MAND_MULTI_ENR_REQUESTS requests
3110: WHERE
3111: -- requests.conc_program_request_id = l_conc_reqId
3112: -- AND requests.mandatory_enr_request_id = reqmembers.mandatory_enr_request_id

Line 3129: l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;

3125: AND ran.BATCH_ACTION_ID = p_conc_reqId
3126: AND rownum < 2
3127: FOR UPDATE OF ran.range_status_cd;
3128:
3129: l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;
3130: l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;
3131: l_request_rec get_all_mandatory_enr_requests%rowtype;
3132: l_req_member_rec csr_get_request_members%rowtype;
3133: l_person_name per_all_people_f.full_name%type;

Line 3130: l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;

3126: AND rownum < 2
3127: FOR UPDATE OF ran.range_status_cd;
3128:
3129: l_error_message OTA_MAND_MULTI_ENR_REQ_MEMBERS.error_message%type;
3130: l_mandaotory_enr_request_id OTA_MAND_MULTI_ENR_REQ_MEMBERS.mandatory_enr_request_id%type;
3131: l_request_rec get_all_mandatory_enr_requests%rowtype;
3132: l_req_member_rec csr_get_request_members%rowtype;
3133: l_person_name per_all_people_f.full_name%type;
3134: l_cert_enrollment_id ota_cert_enrollments.cert_enrollment_id%TYPE;

Line 3195: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS

3191:
3192: EXCEPTION
3193: WHEN OTHERS THEN
3194: l_error_message := nvl(substr(SQLERRM,1,2000),'Error When creating certification subscription ');
3195: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
3196: SET error_message = l_error_message
3197: WHERE person_id = l_req_member_rec.person_id
3198: AND certification_id = l_req_member_rec.certification_id
3199: AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line 3214: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS

3210: update ota_cert_enrollments set is_automatic_subscription = 'Y'
3211: where cert_enrollment_id = l_cert_enrollment_id;
3212: END IF;
3213:
3214: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
3215: SET action_status_cd = 'P'
3216: WHERE person_id = l_req_member_rec.person_id
3217: AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id
3218: AND action_status_cd not in ('P','E');

Line 3227: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS

3223: -- FND_FILE.PUT_LINE(FND_FILE.LOG,'Error when creating enrollment into class- '||l_class_name);
3224: --FND_FILE.PUT_LINE(FND_FILE.LOG,'ERROR - ' || l_error_message);
3225: --FND_FILE.PUT_LINE(FND_FILE.LOG,'---------------------------------------------');
3226:
3227: UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
3228: SET error_message = l_error_message, action_status_cd = 'E'
3229: WHERE person_id = l_req_member_rec.person_id
3230: AND certification_id = l_req_member_rec.certification_id
3231: AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line 3329: delete from OTA_MAND_MULTI_ENR_REQ_MEMBERS

3325: end if;
3326:
3327: FOR l_conc_req IN mand_enr_req_rec.first .. mand_enr_req_rec.last LOOP
3328:
3329: delete from OTA_MAND_MULTI_ENR_REQ_MEMBERS
3330: where MANDATORY_ENR_REQUEST_ID = mand_enr_req_rec(l_conc_req).mandatory_enr_req_id;
3331:
3332: if sql%rowcount > 0 then
3333: commit;