[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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;
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);
SELECT bst.type status_code,
btt.name Status,
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,
ota_booking_status_types_tl btt
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
AND bst.booking_status_type_id = btt.booking_status_type_id
AND btt.LANGUAGE = USERENV('LANG')
ORDER BY mandatory_enrollment_flag desc, status_number desc;
FUNCTION learner_is_notSelected(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';
l_proc varchar2(72) := g_package||'learner_is_notSelected';
OPEN lrnr_already_selected;
FETCH lrnr_already_selected INTO l_lrnr_assignment_id;
IF lrnr_already_selected%NOTFOUND THEN
CLOSE lrnr_already_selected;
CLOSE lrnr_already_selected;
CLOSE lrnr_already_selected;
END learner_is_notSelected;
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');
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'));
--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);
IF learner_is_notSelected(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';
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);
END IF;--learner_is_notSelected
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
,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(p_event_start_date, 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(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 = 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 learner_can_enroll_in_class(p_event_id,ppf.person_id) = 'Y'
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))
AND nvl(p_person_id,-1) = decode(p_person_id,NULL,-1,paf.person_id);
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;
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;
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));
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;
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;
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;
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));
SELECT ppf.full_name FROM per_all_people_f ppf WHERE person_id = p_person_id;
select hr_workflow_item_key_s.nextval into l_item_key from sys.dual;
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 ;
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);
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);