APPS.OTA_MANDATORY_ENROLL_UTIL SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 51

  SELECT
	oea.MANDATORY_ENROLLMENT_PREREQ enr_prereq_type,
	oea.EVENT_ID event_id,
	oea.PERSON_ID  person_id,
	oea.ORGANIZATION_ID organization_id,
	oea.JOB_ID job_id,
	oea.POSITION_ID position_id,
	oea.ORG_STRUCTURE_VERSION_ID org_structure_version_id,
	oea.USER_GROUP_ID user_group_id,
	evt.OWNER_ID requestor_id
  BULK COLLECT INTO
    l_rec.enr_prereq_type,l_rec.event_id,l_rec.person_id,l_rec.organization_id,l_rec.job_id,l_rec.position_id,
    l_rec.org_structure_version_id,l_rec.user_group_id,l_rec.requestor_id
           FROM
            ota_event_associations  oea ,
            ota_events evt
	        WHERE oea.event_id = evt.event_id
	        AND  oea.MANDATORY_ENROLLMENT_FLAG = 'Y'
	        AND ota_timezone_util.convert_date(trunc(sysdate),to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code , evt.timezone)
	           BETWEEN decode(evt.enrolment_start_date, NULL, to_date('0001/01/01','YYYY/MM/DD'),
	                       to_date( to_char(evt.enrolment_start_date, 'YYYY/MM/DD') || ' ' || '00:00', 'YYYY/MM/DD HH24:MI'))
               AND decode(evt.enrolment_end_date, NULL, to_date('4712/12/31','YYYY/MM/DD'),
	                       to_date( to_char(evt.enrolment_end_date, 'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI'))
           AND evt.event_status IN ('P','N')
           AND nvl(p_event_id,-1)= decode(p_event_id,NULL,-1,evt.EVENT_ID)
           AND evt.business_group_id = OTA_GENERAL.get_business_group_id
	 ORDER BY evt.event_id;

Line: 81

  INSERT INTO ota_mandatory_enr_requests(
    MANDATORY_ENR_REQUEST_ID ,
    REQUESTOR_ID,
    EVENT_ID,
    ENR_PREREQ_TYPE,
    PERSON_ID,
    ORGANIZATION_ID,
    ORG_STRUCTURE_VERSION_ID,
    JOB_ID,
    POSITION_ID,
    USERGROUP_ID,
    CONC_PROGRAM_REQUEST_ID,
    CREATION_DATE)
    VALUES(OTA_MANDATORY_ENR_REQUESTS_S.NEXTVAL,
    l_rec.requestor_id(i),
    l_rec.event_id(i),
    l_rec.enr_prereq_type(i),
    l_rec.person_id(i),
    l_rec.organization_id(i),
    l_rec.org_structure_version_id(i),
    l_rec.job_id(i),
    l_rec.position_id(i),
    l_rec.user_group_id(i),
    l_conc_request_id,
    sysdate);

Line: 139

  select 1 from fnd_concurrent_requests fcr, fnd_concurrent_programs fcp
  where fcp.CONCURRENT_PROGRAM_NAME = 'OTA_MAND_ENR_MULTI'
  and fcp.CONCURRENT_PROGRAM_ID = fcr.CONCURRENT_PROGRAM_ID
  and fcr.STATUS_CODE  = 'R' and nvl(PARENT_REQUEST_ID,-1) = -1
  and fcr.REQUEST_ID <> c_conc_req_id;

Line: 194

  SELECT
	oea.MANDATORY_ENROLLMENT_PREREQ enr_prereq_type,
	oea.EVENT_ID event_id,
	oea.PERSON_ID  person_id,
	oea.ORGANIZATION_ID organization_id,
	oea.JOB_ID job_id,
	oea.POSITION_ID position_id,
	oea.ORG_STRUCTURE_VERSION_ID org_structure_version_id,
	oea.USER_GROUP_ID user_group_id,
	evt.OWNER_ID requestor_id
  BULK COLLECT INTO
    l_rec.enr_prereq_type,l_rec.event_id,l_rec.person_id,l_rec.organization_id,l_rec.job_id,l_rec.position_id,
    l_rec.org_structure_version_id,l_rec.user_group_id,l_rec.requestor_id
           FROM
            ota_event_associations  oea ,
            ota_events evt
	        WHERE oea.event_id = evt.event_id
	        AND  oea.MANDATORY_ENROLLMENT_FLAG = 'Y'
	        AND ota_timezone_util.convert_date(trunc(sysdate),to_char(sysdate,'HH24:MI'), ota_timezone_util.get_server_timezone_code , evt.timezone)
	           BETWEEN decode(evt.enrolment_start_date, NULL, to_date('0001/01/01','YYYY/MM/DD'),
	                       to_date( to_char(evt.enrolment_start_date, 'YYYY/MM/DD') || ' ' || '00:00', 'YYYY/MM/DD HH24:MI'))
               AND decode(evt.enrolment_end_date, NULL, to_date('4712/12/31','YYYY/MM/DD'),
	                       to_date( to_char(evt.enrolment_end_date, 'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI'))
           AND evt.event_status IN ('P','N')
           AND nvl(p_event_id,-1)= decode(p_event_id,NULL,-1,evt.EVENT_ID)
           AND evt.business_group_id = OTA_GENERAL.get_business_group_id
	 ORDER BY evt.event_id;

Line: 226

  INSERT INTO OTA_MAND_MULTI_ENR_REQUESTS(
    MANDATORY_ENR_REQUEST_ID ,
    REQUESTOR_ID,
    EVENT_ID,
    ENR_PREREQ_TYPE,
    PERSON_ID,
    ORGANIZATION_ID,
    ORG_STRUCTURE_VERSION_ID,
    JOB_ID,
    POSITION_ID,
    USERGROUP_ID,
    CONC_PROGRAM_REQUEST_ID)
    VALUES(OTA_MAND_MULTI_ENR_REQUESTS_S.NEXTVAL,
    l_rec.requestor_id(i),
    l_rec.event_id(i),
    l_rec.enr_prereq_type(i),
    l_rec.person_id(i),
    l_rec.organization_id(i),
    l_rec.org_structure_version_id(i),
    l_rec.job_id(i),
    l_rec.position_id(i),
    l_rec.user_group_id(i),
    l_conc_request_id);

Line: 321

        SELECT booking_id
        FROM   ota_delegate_bookings
        WHERE  delegate_person_id = p_learner_id
              AND event_id = p_event_id;

Line: 326

	 SELECT bst.type status_code,
                bst.booking_status_type_id  status_id,
	        decode(bst.type, 'C', 0,'R',1, 'W',2, 'P',3,'E',4, 'A',5) status_number,
 	        nvl(tdb.is_mandatory_enrollment,'N') mandatory_enrollment_flag
	   FROM ota_delegate_bookings tdb,
 	        ota_booking_status_types bst
           WHERE  tdb.delegate_person_id = p_learner_id
	          AND  tdb.event_id = p_event_id
	          AND tdb.booking_status_type_id = bst.booking_status_type_id
	          ORDER BY mandatory_enrollment_flag desc, status_number desc;

Line: 377

FUNCTION learner_is_notSelected_inClass(p_person_id IN per_all_people_f.person_id%type
                                ,p_assignment_id per_all_assignments_f.assignment_id%type
                                ,p_event_id IN ota_events.event_id%type)
                          RETURN Boolean IS

    CURSOR lrnr_already_selected IS
	SELECT assignment_id
    FROM ota_mandatory_enr_req_members
    WHERE
	person_id = p_person_id
    AND event_id = p_event_id
    AND create_enrollment = 'Y';

Line: 392

  l_proc  varchar2(72) := g_package||'learner_is_notSelected_inClass';

Line: 398

	OPEN lrnr_already_selected;

Line: 399

	FETCH lrnr_already_selected INTO l_lrnr_assignment_id;

Line: 401

	IF lrnr_already_selected%NOTFOUND THEN
	   CLOSE lrnr_already_selected;

Line: 406

	      CLOSE lrnr_already_selected;

Line: 418

	      CLOSE lrnr_already_selected;

Line: 426

END learner_is_notSelected_inClass;

Line: 428

FUNCTION lrn_is_notSelected_inClass_mul(p_person_id IN per_all_people_f.person_id%type
                                ,p_assignment_id per_all_assignments_f.assignment_id%type
                                ,p_event_id IN ota_events.event_id%type)
                          RETURN Boolean IS

    CURSOR lrnr_already_selected IS
	SELECT assignment_id
    FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS
    WHERE
	person_id = p_person_id
    AND event_id = p_event_id
    AND create_enrollment = 'Y';

Line: 443

  l_proc  varchar2(72) := g_package||'lrn_is_notSelected_inClass_mul';

Line: 449

	OPEN lrnr_already_selected;

Line: 450

	FETCH lrnr_already_selected INTO l_lrnr_assignment_id;

Line: 452

	IF lrnr_already_selected%NOTFOUND THEN
	   CLOSE lrnr_already_selected;

Line: 457

	      CLOSE lrnr_already_selected;

Line: 469

	      CLOSE lrnr_already_selected;

Line: 477

END lrn_is_notSelected_inClass_mul;

Line: 480

FUNCTION learner_is_notSelected_inCert(p_person_id IN per_all_people_f.person_id%type
                                ,p_assignment_id per_all_assignments_f.assignment_id%type
                                ,p_certification_id IN ota_certifications_b.certification_id%type default NULL)
                          RETURN Boolean IS

    CURSOR lrnr_already_selected IS
	  SELECT assignment_id
    FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS
    WHERE
	  person_id = p_person_id
    AND certification_id = p_certification_id
    AND create_enrollment = 'Y';

Line: 496

  l_proc  varchar2(72) := g_package||'learner_is_notSelected_inCert';

Line: 504

	OPEN lrnr_already_selected;

Line: 505

	FETCH lrnr_already_selected INTO l_lrnr_assignment_id;

Line: 507

	IF lrnr_already_selected%NOTFOUND THEN
	   CLOSE lrnr_already_selected;

Line: 513

	      CLOSE lrnr_already_selected;

Line: 526

	      CLOSE lrnr_already_selected;

Line: 535

END learner_is_notSelected_inCert;

Line: 545

  SELECT  asg.assignment_id
  FROM    per_all_assignments_f asg,
          (
            SELECT  p_organization_id AS organization_id
            FROM  dual
            UNION ALL
            SELECT x.sub_organization_id AS organization_id
            FROM   per_org_hrchy_summary x,
                   per_org_structure_versions v,
                   per_org_structure_versions currv
            WHERE  v.org_structure_version_id = p_org_structure_version_id AND
                   v.organization_structure_id = currv.organization_structure_id AND
                   (currv.date_to IS NULL OR
                    sysdate BETWEEN currv.date_from AND currv.date_to) AND
                   x.organization_structure_id = currv.organization_structure_id AND
                   x.org_structure_version_id = currv.org_structure_version_id AND
                   x.organization_id = p_organization_id AND
                   x.sub_org_relative_level > 0
           ) orgs
  WHERE    asg.person_id = p_person_id  AND
           asg.organization_id = orgs.organization_id AND
           asg.assignment_type in ('E','A','C');*/

Line: 570

  SELECT  asg.assignment_id
  FROM    per_all_assignments_f asg,
(
 SELECT o.organization_id_child as organization_id
	 FROM (SELECT     o.organization_id_child
				 FROM per_org_structure_elements o
		   CONNECT BY o.organization_id_parent = PRIOR o.organization_id_child
				  AND o.org_structure_version_id = PRIOR o.org_structure_version_id
			  START WITH o.organization_id_parent = p_organization_id
				  AND o.org_structure_version_id =  p_org_structure_version_id
		   UNION
		   SELECT p_organization_id organization_id_child
			 FROM DUAL) o,
		  hr_organization_units org
	WHERE o.organization_id_child = org.organization_id)orgs
  WHERE    asg.person_id = p_person_id  AND
           asg.organization_id = orgs.organization_id AND
           asg.assignment_type in ('E','A','C');

Line: 621

    SELECT paf.organization_id,
    paf.business_group_id,
    paf.assignment_id
    FROM
    per_all_assignments_f paf,
    per_person_types ppt,
    per_all_people_f perp,
    per_person_type_usages_f ptu
    WHERE
    paf.person_id = l_person_id
    AND perp.person_id =paf.person_id
    AND perp.person_id = ptu.person_id
    AND ptu.person_type_id = ppt.person_type_id
    AND ((paf.primary_flag = 'Y' AND ppt.system_person_type IN ('EMP','CWK'))
         OR (paf.assignment_type = 'A' AND ppt.system_person_type ='APL'))
    AND paf.assignment_type IN ('A','E','C')
    AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
    AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
    AND ((ppt.system_person_type = 'APL'
        AND NOT EXISTS (SELECT person_id
        FROM per_person_type_usages_f ptf,
        per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
        AND ptf.person_type_id = ptp.person_type_id
        AND ptp.system_person_type  IN ('EMP', 'CWK')
        AND ptf.person_id = paf.PERSON_ID)
        )OR ppt.system_person_type IN ('EMP', 'CWK'));

Line: 657

	--As learners are selected based on primary or secondary assignment criteria,but enrollments must be created
    -- based on primary assignment we need to retreive the primary assignment before validations.
           OPEN csr_get_assignment_info(l_person_id);

Line: 663

	      IF learner_is_notSelected_inClass(l_person_id,l_assignment_info.assignment_id,req_event_id) THEN
	--perform the above check to avoid multiple entries for the same learner into same class AND duplicate assignments

	                 IF req_enr_prereq_type = 'N' THEN --Prereq=None
	                   l_create_enrollment := 'Y';

Line: 693

                    INSERT INTO ota_mandatory_enr_req_members(mandatory_enr_request_id,person_id,assignment_id,error_message,creation_date,
                     completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,business_group_id)
                    VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,sysdate,l_completed_crs_prereq,
                     l_completed_comp_prereq, l_create_enrollment,req_event_id,l_assignment_info.organization_id,l_assignment_info.business_group_id);

Line: 698

             END IF;--learner_is_notSelected_inClass

Line: 714

    SELECT paf.organization_id,
    paf.business_group_id,
    paf.assignment_id
    FROM
    per_all_assignments_f paf,
    per_person_types ppt,
    per_all_people_f perp,
    per_person_type_usages_f ptu
    WHERE
    paf.person_id = l_person_id
    AND perp.person_id =paf.person_id
    AND perp.person_id = ptu.person_id
    AND ptu.person_type_id = ppt.person_type_id
    AND ((paf.primary_flag = 'Y' AND ppt.system_person_type IN ('EMP','CWK'))
         OR (paf.assignment_type = 'A' AND ppt.system_person_type ='APL'))
    AND paf.assignment_type IN ('A','E','C')
    AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
    AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
    AND ((ppt.system_person_type = 'APL'
        AND NOT EXISTS (SELECT person_id
        FROM per_person_type_usages_f ptf,
        per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
        AND ptf.person_type_id = ptp.person_type_id
        AND ptp.system_person_type  IN ('EMP', 'CWK')
        AND ptf.person_id = paf.PERSON_ID)
        )OR ppt.system_person_type IN ('EMP', 'CWK'));

Line: 749

   Cursor C_Sel1 is select OTA_PERSON_ACTIONS_S.nextval from sys.dual;

Line: 754

	--As learners are selected based on primary or secondary assignment criteria,but enrollments must be created
    -- based on primary assignment we need to retreive the primary assignment before validations.
           OPEN csr_get_assignment_info(l_person_id);

Line: 760

	      IF lrn_is_notSelected_inClass_mul(l_person_id,l_assignment_info.assignment_id,req_event_id) THEN
	--perform the above check to avoid multiple entries for the same learner into same class AND duplicate assignments

	                 IF req_enr_prereq_type = 'N' THEN --Prereq=None
	                   l_create_enrollment := 'Y';

Line: 794

                    INSERT INTO OTA_MAND_MULTI_ENR_REQ_MEMBERS(mandatory_enr_request_id,person_id,assignment_id,error_message,
                     completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,mbr_bg_id,
                     person_action_id,action_status_cd)
                    VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,l_completed_crs_prereq,
                     l_completed_comp_prereq, l_create_enrollment,req_event_id,l_assignment_info.organization_id,l_assignment_info.business_group_id,
                     l_person_action_id,'U');

Line: 801

             END IF;--lrn_is_notSelected_inClass_mul

Line: 835

	SELECT ppf.person_id
	     ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
	     ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
	   	, paf.organization_id
    	, paf.assignment_id
    	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,per_business_groups pbg
	WHERE
  	  paf.person_id = p_person_id
    AND ppf.person_id = paf.person_id
    AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag= 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND ((p_future_enddated_prof_val = 'Y'
	           AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
		   OR  nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
       AND((p_future_enddated_prof_val = 'Y'
	           AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
		   OR  nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
	   AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	  -- AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
	   AND paf.assignment_type IN ('A','E','C')
AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = 'APL'
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN ('EMP', 'CWK')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN ('EMP', 'CWK'));

Line: 890

	SELECT ppf.person_id
	     , pjt.job_id Job_Id
	     , pps.position_id
	     ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
	     ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
	   	, paf.organization_id
    	, paf.assignment_id
    	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_jobs_tl pjt
	    ,per_all_positions pps
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,hr_all_organization_units_tl orgTl
	    ,per_business_groups pbg
	WHERE  ppf.person_id = paf.person_id
	   AND (pts.system_person_type IN ('EMP','CWK') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND((p_future_enddated_prof_val = 'Y'
	           AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
		   OR  nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
     AND((p_future_enddated_prof_val = 'Y'
	           AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
		   OR  nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
     AND paf.job_id = pjt.job_id(+)
	   AND pjt.language(+) = USERENV('LANG')
	   AND pps.position_id(+) = paf.position_id
	   AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	   AND paf.organization_id = orgtl.organization_id
	   AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
	   AND paf.assignment_type IN ('A','E','C')
	   AND orgtl.language = USERENV('LANG')
    AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = 'APL'
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN ('EMP', 'CWK')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN ('EMP', 'CWK'))
	AND
	(
	( nvl(p_organization_id, -1) = decode(p_organization_id, NULL, -1, nvl(paf.organization_id,-1))) OR

	( p_org_structure_version_id IS NOT NULL AND learner_belongs_to_child_org(p_org_structure_version_id,p_organization_id,ppf.person_id)='Y')
    )
	AND nvl(p_job_id, -1) = decode(p_job_id, NULL, -1, nvl(paf.job_id, -1))
	AND nvl(p_position_id,-1) = decode(p_position_id, NULL, -1, nvl(paf.position_id, -1));

Line: 1011

         sql_stmnt :='SELECT * FROM(
          SELECT
	      ppf.person_id person_id
	     , pjt.job_id job_id
	     , pps.position_id position_id
	     ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,''E'',:1 ) completed_crs_prereq
	     ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,:2) completed_comp_prereq
	    , paf.organization_id organization_id
	, paf.assignment_id assignment_id
	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_jobs_tl pjt
	    ,per_all_positions pps
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,per_person_types_tl ptt
	    ,hr_all_organization_units_tl orgTl
	    ,per_business_groups pbg
	WHERE  ppf.person_id = paf.person_id
	   AND (pts.system_person_type IN (''EMP'',''CWK'') OR (paf.assignment_type = ''A'' AND pts.system_person_type =''APL''))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
       AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
	           AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
		   OR  nvl(:3, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
	   AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
	           AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
		   OR  nvl(:4, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
       AND paf.job_id = pjt.job_id(+)
	   AND pjt.language(+) = USERENV(''LANG'')
	   AND pps.position_id(+) = paf.position_id
	   AND pts.person_type_id = ptt.person_type_id
	   AND ptt.language = USERENV(''LANG'')
	   AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	   AND paf.organization_id = orgtl.organization_id
	   AND pts.system_person_type IN (''EMP'', ''CWK'', ''APL'')
	   AND paf.assignment_type IN (''A'',''E'',''C'')
	   AND orgtl.language = USERENV(''LANG'')
	AND (fnd_profile.value(''OTA_HR_GLOBAL_BUSINESS_GROUP_ID'') IS NOT NULL OR pbg.business_group_id = fnd_profile.value(''PER_BUSINESS_GROUP_ID''))
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = ''APL''
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN (''EMP'', ''CWK'')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN (''EMP'', ''CWK''))
    AND OTA_MANDATORY_ENROLL_UTIL.learner_can_enroll_in_class(:5,ppf.person_id) = ''Y''
    )QRSLT WHERE'|| usergroup_whereclause;

Line: 1218

	SELECT ppf.person_id
	     ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
	     ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
	   	, paf.organization_id
    	, paf.assignment_id
    	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,per_business_groups pbg
	WHERE
  	  paf.person_id = p_person_id
    AND ppf.person_id = paf.person_id
    AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag= 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND ((p_future_enddated_prof_val = 'Y'
	           AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
		   OR  nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
       AND((p_future_enddated_prof_val = 'Y'
	           AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
		   OR  nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
	   AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	  -- AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
	   AND paf.assignment_type IN ('A','E','C')
AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = 'APL'
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN ('EMP', 'CWK')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN ('EMP', 'CWK'));

Line: 1273

	SELECT ppf.person_id
	     , pjt.job_id Job_Id
	     , pps.position_id
	     ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,'E',p_event_id ) completed_crs_prereq
	     ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,p_event_id) completed_comp_prereq
	   	, paf.organization_id
    	, paf.assignment_id
    	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_jobs_tl pjt
	    ,per_all_positions pps
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,hr_all_organization_units_tl orgTl
	    ,per_business_groups pbg
	WHERE  ppf.person_id = paf.person_id
	   AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag= 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND((p_future_enddated_prof_val = 'Y'
	           AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
		   OR  nvl(p_event_start_date, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
     AND((p_future_enddated_prof_val = 'Y'
	           AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
		   OR  nvl(p_event_start_date, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
     AND paf.job_id = pjt.job_id(+)
	   AND pjt.language(+) = USERENV('LANG')
	   AND pps.position_id(+) = paf.position_id
	   AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	   AND paf.organization_id = orgtl.organization_id
	   AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
	   AND paf.assignment_type IN ('A','E','C')
	   AND orgtl.language = USERENV('LANG')
    AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = 'APL'
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN ('EMP', 'CWK')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN ('EMP', 'CWK'))
	AND
	(
	( nvl(p_organization_id, -1) = decode(p_organization_id, NULL, -1, nvl(paf.organization_id,-1))) OR

	( p_org_structure_version_id IS NOT NULL AND learner_belongs_to_child_org(p_org_structure_version_id,p_organization_id,ppf.person_id)='Y')
    )
	AND nvl(p_job_id, -1) = decode(p_job_id, NULL, -1, nvl(paf.job_id, -1))
	AND nvl(p_position_id,-1) = decode(p_position_id, NULL, -1, nvl(paf.position_id, -1));

Line: 1466

         sql_stmnt :='SELECT * FROM(
          SELECT
	      ppf.person_id person_id
	     , pjt.job_id job_id
	     , pps.position_id position_id
	     ,ota_cpr_utility.is_mand_crs_prereqs_comp_evt(ppf.person_id,NULL, ppf.person_id,''E'',:1 ) completed_crs_prereq
	     ,ota_cpr_utility.is_mand_comp_prereqs_comp_evt(ppf.person_id,:2) completed_comp_prereq
	    , paf.organization_id organization_id
	, paf.assignment_id assignment_id
	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_jobs_tl pjt
	    ,per_all_positions pps
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,per_person_types_tl ptt
	    ,hr_all_organization_units_tl orgTl
	    ,per_business_groups pbg
	WHERE  ppf.person_id = paf.person_id
	   AND ((pts.system_person_type IN (''EMP'',''CWK'') AND paf.primary_flag = ''Y'') OR (paf.assignment_type = ''A'' AND pts.system_person_type =''APL''))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
       AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
	           AND trunc(sysdate) BETWEEN paf.effective_start_date and paf.effective_end_date)
		   OR  nvl(:3, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
	   AND ( (nvl(fnd_profile.value(''OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS''),''N'') = ''Y''
	           AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date)
		   OR  nvl(:4, trunc(sysdate)) between ptu.effective_start_date and ptu.effective_end_date )
       AND paf.job_id = pjt.job_id(+)
	   AND pjt.language(+) = USERENV(''LANG'')
	   AND pps.position_id(+) = paf.position_id
	   AND pts.person_type_id = ptt.person_type_id
	   AND ptt.language = USERENV(''LANG'')
	   AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	   AND paf.organization_id = orgtl.organization_id
	   AND pts.system_person_type IN (''EMP'', ''CWK'', ''APL'')
	   AND paf.assignment_type IN (''A'',''E'',''C'')
	   AND orgtl.language = USERENV(''LANG'')
	AND (fnd_profile.value(''OTA_HR_GLOBAL_BUSINESS_GROUP_ID'') IS NOT NULL OR pbg.business_group_id = fnd_profile.value(''PER_BUSINESS_GROUP_ID''))
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = ''APL''
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN (''EMP'', ''CWK'')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN (''EMP'', ''CWK''))
    AND OTA_MANDATORY_ENROLL_UTIL.learner_can_enroll_in_class(:5,ppf.person_id) = ''Y''
    )QRSLT WHERE'|| usergroup_whereclause;

Line: 1723

    SELECT
    reqmembers.MANDATORY_ENR_REQUEST_ID,
    reqmembers.PERSON_ID,
    reqmembers.ASSIGNMENT_ID,
    reqmembers.EVENT_ID,
    reqmembers.ERROR_MESSAGE,
    reqmembers.ORGANIZATION_ID,
    reqmembers.BUSINESS_GROUP_ID
    FROM
    ota_mandatory_enr_req_members reqmembers,
    ota_mandatory_enr_requests  requests
    WHERE
    requests.conc_program_request_id = l_conc_reqId
    AND requests.mandatory_enr_request_id = reqmembers.mandatory_enr_request_id
    AND reqmembers.create_enrollment = 'Y'
    ORDER BY event_id;

Line: 1743

    SELECT pcak.cost_allocation_keyflex_id
    FROM per_all_assignments_f assg,
    pay_cost_allocations_f pcaf,
    pay_cost_allocation_keyflex pcak
    WHERE assg.assignment_id = pcaf.assignment_id
    AND assg.assignment_id = l_assignment_id
    AND assg.Primary_flag = 'Y'
    AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
    AND pcak.enabled_flag = 'Y'
    AND sysdate BETWEEN nvl(pcaf.effective_start_date,sysdate)
    AND nvl(pcaf.effective_end_date,sysdate+1)
    AND trunc(sysdate) BETWEEN nvl(assg.effective_start_date,trunc(sysdate))
    AND nvl(assg.effective_end_date,trunc(sysdate+1));

Line: 1824

       UPDATE ota_mandatory_enr_req_members
       SET error_message = l_error_message
       WHERE person_id = l_req_member_rec.person_id
       AND event_id = l_req_member_rec.event_id
       AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line: 1838

       UPDATE ota_mandatory_enr_req_members
       SET  error_message = NULL
       WHERE person_id = l_req_member_rec.person_id
	   AND assignment_id = l_req_member_rec.assignment_id
	   AND event_id = l_req_member_rec.event_id
       AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line: 1850

       UPDATE ota_mandatory_enr_req_members
       SET error_message = l_error_message
       WHERE person_id = l_req_member_rec.person_id
       AND event_id = l_req_member_rec.event_id
       AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line: 1874

    SELECT
    reqmembers.MANDATORY_ENR_REQUEST_ID,
    reqmembers.PERSON_ID,
    reqmembers.ASSIGNMENT_ID,
    reqmembers.EVENT_ID,
    reqmembers.ERROR_MESSAGE,
    reqmembers.ORGANIZATION_ID,
    reqmembers.MBR_BG_ID,
    reqmembers.CERTIFICATION_ID,
    reqmembers.CREATE_ENROLLMENT
    FROM
    OTA_MAND_MULTI_ENR_REQ_MEMBERS reqmembers
    WHERE
    reqmembers.create_enrollment = 'Y'
		AND reqmembers.person_action_id BETWEEN l_start_person_action_id   AND  l_end_person_action_id
    ORDER BY certification_id;

Line: 1892

    SELECT pcak.cost_allocation_keyflex_id
    FROM per_all_assignments_f assg,
    pay_cost_allocations_f pcaf,
    pay_cost_allocation_keyflex pcak
    WHERE assg.assignment_id = pcaf.assignment_id
    AND assg.assignment_id = l_assignment_id
    AND assg.Primary_flag = 'Y'
    AND pcaf.cost_allocation_keyflex_id = pcak.cost_allocation_keyflex_id
    AND pcak.enabled_flag = 'Y'
    AND sysdate BETWEEN nvl(pcaf.effective_start_date,sysdate)
    AND nvl(pcaf.effective_end_date,sysdate+1)
    AND trunc(sysdate) BETWEEN nvl(assg.effective_start_date,trunc(sysdate))
    AND nvl(assg.effective_end_date,trunc(sysdate+1));

Line: 1907

    SELECT  ran.range_id
           ,ran.starting_person_action_id
           ,ran.ending_person_action_id
    FROM    OTA_BATCH_RANGES ran
    WHERE   ran.range_status_cd = 'U'
    AND     ran.batch_source_cd = 'ME'
    AND     ran.BATCH_ACTION_ID = p_conc_reqId
    AND     rownum < 2
    FOR UPDATE OF ran.range_status_cd;

Line: 1960

    UPDATE  OTA_BATCH_RANGES ran
    SET     ran.range_status_cd = 'P'
    WHERE   ran.range_id = l_range_id;

Line: 1998

       UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
       SET error_message = l_error_message
       WHERE person_id = l_req_member_rec.person_id
       AND event_id = l_req_member_rec.event_id
       AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line: 2013

	  UPDATE  OTA_MAND_MULTI_ENR_REQ_MEMBERS
	  SET     action_status_cd = 'P',error_message = NULL
	  WHERE   person_id = l_req_member_rec.person_id
    AND assignment_id = l_req_member_rec.assignment_id
	  AND     mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id
	  AND     action_status_cd not in ('P','E');

Line: 2026

       UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
       SET error_message = l_error_message, action_status_cd = 'E'
       WHERE person_id = l_req_member_rec.person_id
       AND event_id = l_req_member_rec.event_id
       AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line: 2064

SELECT user_name FROM fnd_user WHERE employee_id=p_person_id
and trunc(sysdate) between trunc(start_date) and trunc(nvl(end_date, sysdate+1));

Line: 2069

SELECT ppf.full_name FROM per_all_people_f ppf WHERE person_id = p_person_id;

Line: 2075

  select hr_workflow_item_key_s.nextval into l_item_key from sys.dual;

Line: 2134

SELECT distinct event_id,requestor_id
from ota_mandatory_enr_requests
where requestor_id IS NOT NULL
and conc_program_request_id = p_conc_reqId ;

Line: 2140

SELECT COUNT(distinct person_id)
FROM ota_mandatory_enr_req_members reqm
WHERE reqm.event_id = p_event_id
AND(reqm.create_enrollment  = 'N' or reqm.error_message IS NOT NULL);

Line: 2147

SELECT COUNT( distinct person_id)
FROM ota_mandatory_enr_req_members reqm
WHERE reqm.event_id = p_event_id
AND(reqm.create_enrollment  = 'Y' and reqm.error_message IS NULL);

Line: 2180

SELECT distinct event_id,requestor_id
from OTA_MAND_MULTI_ENR_REQUESTS
where requestor_id IS NOT NULL
and conc_program_request_id = p_conc_reqId ;

Line: 2186

SELECT COUNT(distinct person_id)
FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm
WHERE reqm.event_id = p_event_id
AND(reqm.create_enrollment  = 'N' or reqm.error_message IS NOT NULL);

Line: 2193

SELECT COUNT( distinct person_id)
FROM OTA_MAND_MULTI_ENR_REQ_MEMBERS reqm
WHERE reqm.event_id = p_event_id
AND(reqm.create_enrollment  = 'Y' and reqm.error_message IS NULL);

Line: 2280

  SELECT
	'N' prereq_type,
  NULL event_id,
	oea.PERSON_ID  person_id,
	oea.ORGANIZATION_ID organization_id,
	oea.JOB_ID job_id,
	oea.POSITION_ID position_id,
	oea.ORG_STRUCTURE_VERSION_ID org_structure_version_id,
	oea.USER_GROUP_ID user_group_id,
  NULL requestor_id,
  oea.CERTIFICATION_ID cert_id
  BULK COLLECT INTO
    l_rec.enr_prereq_type,l_rec.event_id,l_rec.person_id,l_rec.organization_id,l_rec.job_id,l_rec.position_id,
    l_rec.org_structure_version_id,l_rec.user_group_id,l_rec.requestor_id,l_rec.certification_id
           FROM
            ota_event_associations  oea ,
            ota_certifications_b cert
	    WHERE oea.certification_id = cert.certification_id
	        AND  oea.MANDATORY_ENROLLMENT_FLAG = 'Y'
	        AND  trunc(sysdate) BETWEEN trunc(cert.start_date_active) and nvl(cert.end_date_active, trunc(sysdate))
          AND nvl(p_cert_id,-1)= decode(p_cert_id,NULL,-1,cert.certification_id)
          AND ((cert.renewable_flag = 'N' and trunc(sysdate) <= nvl( cert.INITIAL_COMPLETION_DATE ,trunc(sysdate)))
          or cert.renewable_flag = 'Y')
          AND cert.business_group_id = OTA_GENERAL.get_business_group_id
          AND exists (select 1 from ota_certification_members crm where cert.certification_id = crm.certification_id)
	 ORDER BY cert.certification_id;

Line: 2312

  INSERT INTO OTA_MAND_MULTI_ENR_REQUESTS(
    MANDATORY_ENR_REQUEST_ID ,
    REQUESTOR_ID,
    EVENT_ID,
    ENR_PREREQ_TYPE,
    PERSON_ID,
    ORGANIZATION_ID,
    ORG_STRUCTURE_VERSION_ID,
    JOB_ID,
    POSITION_ID,
    USERGROUP_ID,
    CONC_PROGRAM_REQUEST_ID,
    CERTIFICATION_ID)
    VALUES(OTA_MAND_MULTI_ENR_REQUESTS_S.NEXTVAL,
    l_rec.requestor_id(i),
    NULL,
    'N',
    l_rec.person_id(i),
    l_rec.organization_id(i),
    l_rec.org_structure_version_id(i),
    l_rec.job_id(i),
    l_rec.position_id(i),
    l_rec.user_group_id(i),
    l_conc_request_id,
    l_rec.certification_id(i));

Line: 2412

	SELECT ppf.person_id
	    , paf.organization_id
    	, paf.assignment_id
    	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,per_business_groups pbg
	WHERE
  	  paf.person_id = p_person_id
    AND ppf.person_id = paf.person_id
	   AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag= 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
     AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
     AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	   AND paf.assignment_type IN ('A','E','C')
AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = 'APL'
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN ('EMP', 'CWK')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN ('EMP', 'CWK'));

Line: 2453

	SELECT ppf.person_id
	     , pjt.job_id Job_Id
	     , pps.position_id
	   	, paf.organization_id
    	, paf.assignment_id
    	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_jobs_tl pjt
	    ,per_all_positions pps
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,hr_all_organization_units_tl orgTl
	    ,per_business_groups pbg
	WHERE  ppf.person_id = paf.person_id
	   AND ((pts.system_person_type IN ('EMP','CWK') AND paf.primary_flag = 'Y') OR (paf.assignment_type = 'A' AND pts.system_person_type ='APL'))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
     AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
     AND paf.job_id = pjt.job_id(+)
	   AND pjt.language(+) = USERENV('LANG')
	   AND pps.position_id(+) = paf.position_id
	   AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	   AND paf.organization_id = orgtl.organization_id
	   AND pts.system_person_type IN ('EMP', 'CWK', 'APL')
	   AND paf.assignment_type IN ('A','E','C')
	   AND orgtl.language = USERENV('LANG')
    AND (p_ota_global_bg_prof_val IS NOT NULL OR pbg.business_group_id = p_per_bg_grp_prof_val)
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = 'APL'
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN ('EMP', 'CWK')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN ('EMP', 'CWK'))
	AND
	(
	( nvl(p_organization_id, -1) = decode(p_organization_id, NULL, -1, nvl(paf.organization_id,-1))) OR

	( p_org_structure_version_id IS NOT NULL AND learner_belongs_to_child_org(p_org_structure_version_id,p_organization_id,ppf.person_id)='Y')
    )
	AND nvl(p_job_id, -1) = decode(p_job_id, NULL, -1, nvl(paf.job_id, -1))
	AND nvl(p_position_id,-1) = decode(p_position_id, NULL, -1, nvl(paf.position_id, -1));

Line: 2573

                           update ota_cert_enrollments set is_automatic_subscription = 'Y'
                           where cert_enrollment_id = l_cert_enrollment_id;

Line: 2629

                           update ota_cert_enrollments set is_automatic_subscription = 'Y'
                           where cert_enrollment_id = l_cert_enrollment_id;

Line: 2675

         sql_stmnt :='SELECT * FROM(
          SELECT
	      ppf.person_id person_id
	     , pjt.job_id job_id
	     , pps.position_id position_id
	    , paf.organization_id organization_id
	, paf.assignment_id assignment_id
	FROM per_all_people_f ppf
	    ,per_all_assignments_f paf
	    ,per_jobs_tl pjt
	    ,per_all_positions pps
	    ,per_person_type_usages_f ptu
	    ,per_person_types pts
	    ,per_person_types_tl ptt
	    ,hr_all_organization_units_tl orgTl
	    ,per_business_groups pbg
	WHERE  ppf.person_id = paf.person_id
	   AND ((pts.system_person_type IN (''EMP'',''CWK'') AND paf.primary_flag = ''Y'') OR (paf.assignment_type = ''A'' AND pts.system_person_type =''APL''))
	   AND trunc(sysdate) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
     AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
     AND trunc(sysdate) BETWEEN ptu.effective_start_date and ptu.effective_end_date
       AND paf.job_id = pjt.job_id(+)
	   AND pjt.language(+) = USERENV(''LANG'')
	   AND pps.position_id(+) = paf.position_id
	   AND pts.person_type_id = ptt.person_type_id
	   AND ptt.language = USERENV(''LANG'')
	   AND pts.person_type_id = ptu.person_type_id
	   AND ptu.person_id = ppf.person_id
	   AND paf.organization_id = orgtl.organization_id
	   AND pts.system_person_type IN (''EMP'', ''CWK'', ''APL'')
	   AND paf.assignment_type IN (''A'',''E'',''C'')
	   AND orgtl.language = USERENV(''LANG'')
	AND (fnd_profile.value(''OTA_HR_GLOBAL_BUSINESS_GROUP_ID'') IS NOT NULL OR pbg.business_group_id = fnd_profile.value(''PER_BUSINESS_GROUP_ID''))
	AND paf.business_group_id = pbg.business_group_id
	AND
	((pts.system_person_type = ''APL''
	AND NOT EXISTS (SELECT person_id
	 FROM per_person_type_usages_f ptf,
	per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
	AND ptf.person_type_id = ptp.person_type_id
	AND ptp.system_person_type IN (''EMP'', ''CWK'')
	AND ptf.person_id = ppf.PERSON_ID)
	)
	OR pts.system_person_type IN (''EMP'', ''CWK''))
    )QRSLT WHERE'|| usergroup_whereclause;

Line: 2762

                 update ota_cert_enrollments set is_automatic_subscription = 'Y'
                 where cert_enrollment_id = l_cert_enrollment_id;

Line: 2827

                 update ota_cert_enrollments set is_automatic_subscription = 'Y'
                 where cert_enrollment_id = l_cert_enrollment_id;

Line: 2953

        SELECT cert_enrollment_id,
               certification_status_code,
               nvl(is_automatic_subscription, 'N')
        FROM   ota_cert_enrollments
        WHERE  person_id = p_learner_id
        AND    certification_id = p_certification_id;

Line: 3023

    SELECT paf.organization_id,
    paf.business_group_id,
    paf.assignment_id
    FROM
    per_all_assignments_f paf,
    per_person_types ppt,
    per_all_people_f perp,
    per_person_type_usages_f ptu
    WHERE
    paf.person_id = l_person_id
    AND perp.person_id =paf.person_id
    AND perp.person_id = ptu.person_id
    AND ptu.person_type_id = ppt.person_type_id
    AND ((paf.primary_flag = 'Y' AND ppt.system_person_type IN ('EMP','CWK'))
         OR (paf.assignment_type = 'A' AND ppt.system_person_type ='APL'))
    AND paf.assignment_type IN ('A','E','C')
    AND trunc(sysdate) BETWEEN paf.effective_start_date AND paf.effective_end_date
    AND trunc(sysdate) BETWEEN ptu.effective_start_date AND ptu.effective_end_date
    AND ((ppt.system_person_type = 'APL'
        AND NOT EXISTS (SELECT person_id
        FROM per_person_type_usages_f ptf,
        per_person_types ptp WHERE trunc(sysdate) BETWEEN trunc(ptf.effective_start_date) AND trunc(ptf.effective_end_date)
        AND ptf.person_type_id = ptp.person_type_id
        AND ptp.system_person_type  IN ('EMP', 'CWK')
        AND ptf.person_id = paf.PERSON_ID)
        )OR ppt.system_person_type IN ('EMP', 'CWK'));

Line: 3057

   Cursor C_Sel1 is select OTA_PERSON_ACTIONS_S.nextval from sys.dual;

Line: 3064

	--As learners are selected based on primary or secondary assignment criteria,but enrollments must be created
    -- based on primary assignment we need to retreive the primary assignment before validations.
           OPEN csr_get_assignment_info(l_person_id);

Line: 3070

	      IF learner_is_notSelected_inCert(l_person_id,l_assignment_info.assignment_id,req_cert_id) THEN
	                --FND_FILE.PUT_LINE(FND_FILE.LOG,'Inserting into req members');

Line: 3075

                  INSERT INTO OTA_MAND_MULTI_ENR_REQ_MEMBERS(mandatory_enr_request_id,person_id,assignment_id,error_message,
                   completed_course_prereq,completed_competence_prereq,create_enrollment,event_id,organization_id,mbr_bg_id,certification_id,
                   person_action_id,action_status_cd)
                  VALUES(req_mandatory_enr_request_id,l_person_id,l_assignment_info.assignment_id,NULL,l_completed_crs_prereq,l_completed_comp_prereq,
                   l_create_enrollment,NULL,l_assignment_info.organization_id,ota_general.get_business_group_id,req_cert_id,
                   l_person_action_id,'U');

Line: 3082

        END IF;--learner_is_notSelected_inCert

Line: 3097

    SELECT
    reqmembers.MANDATORY_ENR_REQUEST_ID,
    reqmembers.PERSON_ID,
    reqmembers.ASSIGNMENT_ID,
    reqmembers.EVENT_ID,
    reqmembers.ERROR_MESSAGE,
    reqmembers.ORGANIZATION_ID,
    reqmembers.MBR_BG_ID,
    reqmembers.CERTIFICATION_ID,
    reqmembers.CREATE_ENROLLMENT
    FROM
    OTA_MAND_MULTI_ENR_REQ_MEMBERS reqmembers--,
    --OTA_MAND_MULTI_ENR_REQUESTS  requests
    WHERE
--    requests.conc_program_request_id = l_conc_reqId
--    AND requests.mandatory_enr_request_id = reqmembers.mandatory_enr_request_id
--    AND
    reqmembers.create_enrollment in ('Y', 'U')
		AND reqmembers.person_action_id BETWEEN l_start_person_action_id   AND  l_end_person_action_id
    ORDER BY certification_id;

Line: 3119

    SELECT  ran.range_id
           ,ran.starting_person_action_id
           ,ran.ending_person_action_id
    FROM    OTA_BATCH_RANGES ran
    WHERE   ran.range_status_cd = 'U'
    AND     ran.batch_source_cd = 'AS'
    AND     ran.BATCH_ACTION_ID = p_conc_reqId
    AND     rownum < 2
    FOR UPDATE OF ran.range_status_cd;

Line: 3160

    UPDATE  OTA_BATCH_RANGES ran
    SET     ran.range_status_cd = 'P'
    WHERE   ran.range_id = l_range_id;

Line: 3195

       UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
       SET error_message = l_error_message
       WHERE person_id = l_req_member_rec.person_id
       AND certification_id = l_req_member_rec.certification_id
       AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line: 3210

          update ota_cert_enrollments set is_automatic_subscription = 'Y'
           where cert_enrollment_id = l_cert_enrollment_id;

Line: 3214

	  UPDATE  OTA_MAND_MULTI_ENR_REQ_MEMBERS
	  SET     action_status_cd = 'P'
	  WHERE   person_id = l_req_member_rec.person_id
	  AND     mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id
	  AND     action_status_cd not in ('P','E');

Line: 3227

       UPDATE OTA_MAND_MULTI_ENR_REQ_MEMBERS
       SET error_message = l_error_message, action_status_cd = 'E'
       WHERE person_id = l_req_member_rec.person_id
       AND certification_id = l_req_member_rec.certification_id
       AND mandatory_enr_request_id = l_req_member_rec.mandatory_enr_request_id;

Line: 3272

    Select null
      From fnd_concurrent_requests fnd
     Where fnd.phase_code <> 'C'
       And fnd.request_id = p_request_id;

Line: 3317

select omr.CONC_PROGRAM_REQUEST_ID, omr.MANDATORY_ENR_REQUEST_ID
bulk collect into mand_enr_req_rec
from OTA_MAND_MULTI_ENR_REQUESTS omr, fnd_concurrent_requests fcr
where fcr.REQUEST_ID (+) = omr.CONC_PROGRAM_REQUEST_ID
and nvl(fcr.STATUS_CODE,'C') in ('C','E');

Line: 3329

		delete from OTA_MAND_MULTI_ENR_REQ_MEMBERS
		where MANDATORY_ENR_REQUEST_ID = mand_enr_req_rec(l_conc_req).mandatory_enr_req_id;

Line: 3336

		delete from ota_batch_ranges
		where BATCH_ACTION_ID = mand_enr_req_rec(l_conc_req).conc_req_id
		and BATCH_SOURCE_CD <> 'EP';

Line: 3344

                delete from    ota_mand_multi_enr_requests
                where   mandatory_enr_request_id = mand_enr_req_rec(l_conc_req).mandatory_enr_req_id;