[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select o.offering_id, i.activity_version_id, i.category_usage_id,
c1.parent_cat_usage_id as parent_cat_usage_id_1,
c2.parent_cat_usage_id as parent_cat_usage_id_2,
c3.parent_cat_usage_id as parent_cat_usage_id_3,
c4.parent_cat_usage_id as parent_cat_usage_id_4
from ota_events e,
ota_offerings o,
ota_act_cat_inclusions i,
ota_category_usages c1,
ota_category_usages c2,
ota_category_usages c3,
ota_category_usages c4
where e.event_id = p_event_id and
e.parent_offering_id = o.offering_id and
o.activity_version_id = i.activity_version_id and
i.primary_flag = 'Y' and
i.category_usage_id = c1.category_usage_id and
c1.parent_cat_usage_id = c2.category_usage_id(+) and
c2.parent_cat_usage_id = c3.category_usage_id(+) and
c3.parent_cat_usage_id = c4.category_usage_id(+);
select i.category_usage_id,
c1.parent_cat_usage_id as parent_cat_usage_id_1,
c2.parent_cat_usage_id as parent_cat_usage_id_2,
c3.parent_cat_usage_id as parent_cat_usage_id_3,
c4.parent_cat_usage_id as parent_cat_usage_id_4
from ota_lp_cat_inclusions i,
ota_category_usages c1,
ota_category_usages c2,
ota_category_usages c3,
ota_category_usages c4
where i.learning_path_id = p_learning_path_id and
i.primary_flag = 'Y' and
i.category_usage_id = c1.category_usage_id and
c1.parent_cat_usage_id = c2.category_usage_id(+) and
c2.parent_cat_usage_id = c3.category_usage_id(+) and
c3.parent_cat_usage_id = c4.category_usage_id(+);
select i.object_id,
c1.parent_cat_usage_id as parent_cat_usage_id_1,
c2.parent_cat_usage_id as parent_cat_usage_id_2,
c3.parent_cat_usage_id as parent_cat_usage_id_3,
c4.parent_cat_usage_id as parent_cat_usage_id_4
from ota_frm_obj_inclusions i,
ota_category_usages c1,
ota_category_usages c2,
ota_category_usages c3,
ota_category_usages c4
where i.forum_id = p_forum_id and
i.primary_flag = 'Y' and
i.object_id = c1.category_usage_id and
i.object_type = 'C' and
c1.parent_cat_usage_id = c2.category_usage_id(+) and
c2.parent_cat_usage_id = c3.category_usage_id(+) and
c3.parent_cat_usage_id = c4.category_usage_id(+);
select i.object_id,
c1.parent_cat_usage_id as parent_cat_usage_id_1,
c2.parent_cat_usage_id as parent_cat_usage_id_2,
c3.parent_cat_usage_id as parent_cat_usage_id_3,
c4.parent_cat_usage_id as parent_cat_usage_id_4
from ota_chat_obj_inclusions i,
ota_category_usages c1,
ota_category_usages c2,
ota_category_usages c3,
ota_category_usages c4
where i.chat_id = p_chat_id and
i.primary_flag = 'Y' and
i.object_id = c1.category_usage_id and
i.object_type = 'C' and
c1.parent_cat_usage_id = c2.category_usage_id(+) and
c2.parent_cat_usage_id = c3.category_usage_id(+) and
c3.parent_cat_usage_id = c4.category_usage_id(+);
select i.category_usage_id,
c1.parent_cat_usage_id as parent_cat_usage_id_1,
c2.parent_cat_usage_id as parent_cat_usage_id_2,
c3.parent_cat_usage_id as parent_cat_usage_id_3,
c4.parent_cat_usage_id as parent_cat_usage_id_4
from ota_cert_cat_inclusions i,
ota_category_usages c1,
ota_category_usages c2,
ota_category_usages c3,
ota_category_usages c4
where i.certification_id = p_certification_id and
i.primary_flag = 'Y' and
i.category_usage_id = c1.category_usage_id and
c1.parent_cat_usage_id = c2.category_usage_id(+) and
c2.parent_cat_usage_id = c3.category_usage_id(+) and
c3.parent_cat_usage_id = c4.category_usage_id(+);
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
from ota_event_associations ea
where ea.party_id is null and
ea.customer_id is null and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.event_id = p_event_id or
ea.offering_id = p_offering_id or
ea.activity_version_id = p_activity_version_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
select ea.event_association_id
from
ota_event_associations ea,
per_all_assignments_f paf
where ea.event_id = p_event_id
and paf.person_id = p_person_id
AND nvl(ea.mandatory_enrollment_flag,'N') = 'Y'
AND paf.assignment_type in ('E','A','C')
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_as_of, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
AND nvl(ea.job_id, -1) = decode(ea.job_id, null, -1, paf.job_id)
AND nvl(ea.position_id,-1) = decode(ea.position_id, null, -1,paf.position_id)
AND nvl(ea.person_id,-1) = decode(ea.person_id,null,-1,p_person_id)
AND
(
( nvl(ea.organization_id, -1) = decode(ea.organization_id, null, -1, paf.organization_id)) OR
( ea.org_structure_version_id IS NOT NULL AND learner_belongs_to_child_org(ea.org_structure_version_id,ea.organization_id,paf.person_id)='Y')
);*/
select ea.event_association_id
from
ota_event_associations ea,
per_all_assignments_f paf
where ea.event_id = p_event_id
and paf.person_id = p_person_id
AND nvl(ea.mandatory_enrollment_flag,'N') = 'Y'
AND paf.assignment_type in ('E','A','C')
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_as_of, trunc(sysdate)) between paf.effective_start_date and paf.effective_end_date )
AND ((ea.user_group_id is null
AND nvl(ea.job_id, -1) = decode(ea.job_id, null, -1, paf.job_id)
AND nvl(ea.position_id,-1) = decode(ea.position_id, null, -1,paf.position_id)
AND nvl(ea.person_id,-1) = decode(ea.person_id,null,-1,p_person_id)
AND(( nvl(ea.organization_id, -1) = decode(ea.organization_id, null, -1, paf.organization_id)) OR
( ea.org_structure_version_id IS NOT NULL AND learner_belongs_to_child_org(ea.org_structure_version_id,ea.organization_id,paf.person_id)='Y'))
)OR(ea.user_group_id is not null AND is_learner_in_user_group(p_person_id,ea.user_group_id, ota_general.get_business_group_id)= 'Y')
);
select 1
from ota_event_associations ea
where ea.party_id = p_party_id and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.event_id = p_event_id or
ea.offering_id = p_offering_id or
ea.activity_version_id = p_activity_version_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
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
from ota_event_associations ea,
(
select cu.category_usage_id
from ota_category_usages cu
where cu.type = 'C'
connect by cu.category_usage_id = prior cu.parent_cat_usage_id
start with cu.category_usage_id = p_category_usage_id
) cat
where ea.party_id is null and
ea.customer_id is null and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
ea.category_usage_id = cat.category_usage_id;
select 1
from ota_event_associations ea,
(
select cu.category_usage_id
from ota_category_usages cu
where cu.type = 'C'
connect by cu.category_usage_id = prior cu.parent_cat_usage_id
start with cu.category_usage_id = p_category_usage_id
) cat
where ea.party_id = p_party_id and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
ea.category_usage_id = cat.category_usage_id;
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
from ota_event_associations ea
where ea.customer_id is null and
ea.party_id is null and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.learning_path_id = p_learning_path_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
select 1
from ota_event_associations ea
where ea.party_id = p_party_id and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.learning_path_id = p_learning_path_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
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
from ota_event_associations ea
where ea.customer_id is null and
ea.party_id is null and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.forum_id = p_forum_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
select 1
from ota_event_associations ea
where ea.party_id = p_party_id and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.forum_id = p_forum_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
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
from ota_event_associations ea
where ea.customer_id is null and
ea.party_id is null and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.chat_id = p_chat_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
select 1
from ota_event_associations ea
where ea.party_id = p_party_id and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.chat_id = p_chat_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
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
from ota_event_associations ea
where ea.customer_id is null and
ea.party_id is null and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.certification_id = p_certification_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
select 1
from ota_event_associations ea
where ea.party_id = p_party_id and
(p_self_enroll_only = 'N' or ea.self_enrollment_flag = 'Y') and
(
ea.certification_id = p_certification_id or
ea.category_usage_id in (p_category_usage_id, p_parent_cat_usage_id_1, p_parent_cat_usage_id_2, p_parent_cat_usage_id_3, p_parent_cat_usage_id_4));
select asg.assignment_id
from per_all_assignments_f asg
where asg.person_id = p_person_id
AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
OR nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date )
and
nvl(p_organization_id, -1) = decode(p_organization_id, null, -1, nvl(asg.organization_id,-1)) and
nvl(p_job_id, -1) = decode(p_job_id, null, -1, nvl(asg.job_id, -1)) and
nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
asg.assignment_type in ('E','A','C');
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 ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
OR nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date )
AND nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date and
asg.organization_id = orgs.organization_id and
nvl(p_job_id, -1) = decode(p_job_id, null, -1, nvl(asg.job_id, -1)) and
nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
asg.assignment_type in ('E','A','C');*/
select asg.assignment_id
from per_all_assignments_f asg
,per_person_types ppt
,per_all_people_f perp
,per_person_type_usages_f ptu
where asg.person_id = p_person_id
and asg.person_id = perp.person_id
and perp.person_id =ptu.person_id
and ptu.person_type_id=ppt.person_type_id
and ((asg.primary_flag = 'Y' and ppt.system_person_type in ('EMP','CWK','OTHER'))
OR (asg.assignment_type = 'A' and ppt.system_person_type ='APL'))
AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between asg.effective_start_date and asg.effective_end_date )
AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
AND trunc(sysdate) BETWEEN perp.effective_start_date and perp.effective_end_date)
OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between perp.effective_start_date and perp.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 decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between ptu.effective_start_date and ptu.effective_end_date )
and
nvl(p_organization_id, -1) = decode(p_organization_id, null, -1, nvl(asg.organization_id,-1)) and
nvl(p_job_id, -1) = decode(p_job_id, null, -1, nvl(asg.job_id, -1)) and
nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
asg.assignment_type in ('E','A','C');
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
,per_person_types ppt
,per_all_people_f perp
,per_person_type_usages_f ptu
where asg.person_id = p_person_id
and asg.person_id = perp.person_id
and perp.person_id =ptu.person_id
and ptu.person_type_id=ppt.person_type_id
and ((asg.primary_flag = 'Y' and ppt.system_person_type in ('EMP','CWK','OTHER'))
OR (asg.assignment_type = 'A' and ppt.system_person_type ='APL'))
AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
AND trunc(sysdate) BETWEEN asg.effective_start_date and asg.effective_end_date)
OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between asg.effective_start_date and asg.effective_end_date )
AND ( (nvl(fnd_profile.value('OTA_ALLOW_FUTURE_ENDDATED_EMP_ENROLLMENTS'),'N') = 'Y'
AND trunc(sysdate) BETWEEN perp.effective_start_date and perp.effective_end_date)
OR decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between perp.effective_start_date and perp.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 decode(p_as_of,NULL, trunc(sysdate),trunc(p_as_of)) between ptu.effective_start_date and ptu.effective_end_date )
-- AND nvl(p_as_of, trunc(sysdate)) between asg.effective_start_date and asg.effective_end_date and
and asg.organization_id = orgs.organization_id and
nvl(p_job_id, -1) = decode(p_job_id, null, -1, nvl(asg.job_id, -1)) and
nvl(p_position_id,-1) = decode(p_position_id, null, -1, nvl(asg.position_id, -1)) and
asg.assignment_type in ('E','A','C');
SELECT child_user_group_id
FROM ( SELECT a.user_group_id user_group_id,
a.child_user_group_id child_user_group_id,
level UG_Level,
b.user_group_operator user_group_operator
FROM ota_user_group_elements a, ota_user_groups_b b
WHERE a.user_group_id = b.user_group_id
and (p_ignore_ug_date_check = 'Y' or
trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and trunc(nvl(b.end_date_active, sysdate+1)))
and (b.business_group_id = p_business_group_id or p_business_group_id = -1)
START WITH a.user_group_id = p_user_group_id
CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
ORDER by LEVEL desc
) WHERE child_user_group_id is not null;
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;
select uge.organization_id,
uge.org_structure_version_id,
uge.job_id,
uge.position_id,
uge.person_id,
uge.match_type,
uge.child_user_group_id,
ugb.user_group_operator,
uge.elig_prfl_id,
uge.user_group_id
from ota_user_group_elements uge, ota_user_groups_b ugb
where uge.user_group_id = p_user_group_id
and uge.user_group_id = ugb.user_group_id
and (p_ignore_ug_date_check = 'Y' or
trunc(sysdate) between trunc(nvl(ugb.start_date_active, sysdate)) and trunc(nvl(ugb.end_date_active, sysdate+1))) --Bug#7120108
and (uge.business_group_id = p_business_group_id or p_business_group_id = -1)
and (uge.person_id is null OR uge.elig_prfl_id is null);
select 1
from ota_event_associations
where event_id = p_event_id and
customer_id is not null;
select oev.course_start_date, oev.course_end_date,ocu.synchronous_flag, oev.event_status
,oev.timezone , oev.course_end_time
into l_event_start_date,l_event_end_date,l_synchronous_flag, l_event_status
,l_timezone , l_event_end_time
from ota_events oev, ota_offerings ofr, ota_category_usages ocu
Where oev.event_id = p_event_id and
oev.parent_offering_id = ofr.offering_id and
ofr.delivery_mode_id = ocu.category_usage_id and
event_type in ('SCHEDULED','SELFPACED') and
event_status in('P','N','F') and
-- l_date between nvl(enrolment_start_date, l_date) AND nvl(enrolment_end_date, l_date);
select asg.assignment_id
from per_all_assignments_f asg
where asg.person_id = p_person_id and
nvl(p_course_start_date, trunc(p_now)) between asg.effective_start_date and asg.effective_end_date and
p_organization_id = asg.organization_id and
asg.assignment_type in ('E','A','C');
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 1
from ota_delegate_bookings book,
ota_booking_status_types stype
where book.delegate_person_id = p_person_id and
book.event_id = p_event_id and
book.booking_status_type_id = stype.booking_status_type_id and
stype.type in ('P', 'A' ,'E');
Select OCU.synchronous_flag, OEV.event_status, OEV.secure_event_flag,
OEV.organization_id
From ota_events OEV, ota_offerings OFR, ota_category_usages OCU
Where OEV.event_id = p_event_id
And OEV.parent_offering_id = OFR.offering_id
And OFR.delivery_mode_id = OCU.category_usage_id;
select maximum_internal_attendees, public_event_flag
into v_max_internal, v_public_event_flag
from ota_events
where event_id = p_event_id;
select maximum_internal_attendees, public_event_flag, parent_offering_id, course_start_date
into v_max_internal, v_public_event_flag, v_parent_offering_id, v_event_start_date
from ota_events
where event_id = p_event_id;
select maximum_internal_attendees, public_event_flag
into v_max_internal, v_public_event_flag
from ota_events
where event_id = p_event_id;
select maximum_internal_attendees, public_event_flag, parent_offering_id, course_start_date
into v_max_internal, v_public_event_flag, v_parent_offering_id, v_event_start_date
from ota_events
where event_id = p_event_id;
select employee_id, person_party_id
into v_employee_id, v_party_id
from fnd_user
where user_id = p_user_id;
select employee_id, person_party_id
into v_employee_id, v_party_id
from fnd_user
where user_id = p_user_id;
select employee_id, person_party_id
into v_employee_id, v_party_id
from fnd_user
where user_id = p_user_id;
select employee_id, person_party_id
into v_employee_id, v_party_id
from fnd_user
where user_id = p_user_id;
select employee_id, person_party_id
into v_employee_id, v_party_id
from fnd_user
where user_id = p_user_id;
select employee_id, person_party_id
into v_employee_id, v_party_id
from fnd_user
where user_id = p_user_id;
select employee_id, person_party_id
into v_employee_id, v_party_id
from fnd_user
where user_id = p_user_id;
select employee_id, person_party_id
into v_employee_id, v_party_id
from fnd_user
where user_id = p_user_id;
SELECT public_flag, start_date_active
FROM ota_learning_paths
WHERE learning_path_id = p_learning_path_id;
SELECT public_flag, start_date_active
FROM ota_certifications_b
WHERE certification_id = p_certification_id;
SELECT OEV.event_id,
OEV.public_event_flag,
OEV.maximum_internal_attendees,
OEV.course_start_date
FROM ota_category_usages OCU,
ota_events OEV,
ota_offerings OFR,
ota_activity_versions OAV
WHERE OAV.activity_version_id = p_activity_version_id
AND OFR.activity_version_id = OAV.activity_version_id
AND OEV.parent_offering_id = OFR.offering_id
AND OEV.business_group_id = p_business_group_id
AND OEV.event_type IN ('SCHEDULED','SELFPACED')
AND OEV.book_independent_flag = 'N'
AND OEV.Event_status in('N','P','F')
AND OFR.delivery_mode_id = OCU.category_usage_id
AND OCU.type ='DM'
AND trunc(sysdate) BETWEEN nvl(OAV.start_date, trunc(sysdate)) AND nvl(OAV.end_date, trunc(sysdate+1))
AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), p_server_timezone_code, OEV.timezone)
BETWEEN to_date(to_char(nvl(OEV.enrolment_start_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
AND to_date(to_char(nvl(OEV.enrolment_end_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI')
ORDER BY OEV.public_event_flag DESC;
SELECT OEV.event_id,
OEV.public_event_flag,
OEV.maximum_internal_attendees,
OEV.course_start_date
FROM ota_category_usages OCU,
ota_events OEV,
ota_offerings OFR,
ota_activity_versions OAV
WHERE OAV.activity_version_id = p_activity_version_id
AND OFR.activity_version_id = OAV.activity_version_id
AND OEV.parent_offering_id = OFR.offering_id
AND OEV.business_group_id = p_business_group_id
AND OEV.event_type IN ('SCHEDULED','SELFPACED')
AND OEV.book_independent_flag = 'N'
AND OEV.Event_status in('N','P','F')
AND OFR.delivery_mode_id = OCU.category_usage_id
AND OCU.type ='DM'
AND trunc(sysdate) BETWEEN nvl(OAV.start_date, trunc(sysdate)) AND nvl(OAV.end_date, trunc(sysdate+1))
AND ota_timezone_util.convert_date(sysdate, to_char(sysdate,'HH24:MI'), p_server_timezone_code, OEV.timezone)
BETWEEN to_date(to_char(nvl(OEV.enrolment_start_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '00:00' , 'YYYY/MM/DD HH24:MI')
AND to_date(to_char(nvl(OEV.enrolment_end_date,to_date('4712/12/31','YYYY/MM/DD')),'YYYY/MM/DD') || ' ' || '23:59', 'YYYY/MM/DD HH24:MI')
AND nvl(OEV.event_availability, 'ALL') = 'ALL'
ORDER BY OEV.public_event_flag DESC;
whereclause := whereclause || ' person_id in (select person_id from ota_user_group_elements where ';
SELECT count(user_group_element_id) into total_elements_count
FROM OTA_USER_GROUP_ELEMENTS
WHERE user_group_id in ( SELECT child_user_group_id
FROM ( SELECT a.child_user_group_id
FROM ota_user_group_elements a,
ota_user_groups_b b
WHERE a.user_group_id = b.user_group_id
and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and
trunc(nvl(b.end_date_active, sysdate+1))
START WITH a.user_group_id = p_user_group_id
CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
UNION ALL SELECT p_user_group_id FROM dual
) WHERE child_user_group_id is not null ) and
(fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null
or business_group_id = p_business_group_id)
and( person_id is null or elig_prfl_id is null);
SELECT count(a.user_group_element_id) into restricted_elements_count
FROM OTA_USER_GROUP_ELEMENTS a,
PER_PEOPLE_F e,
PER_JOBS_VL job,
HR_ORGANIZATION_UNITS org,
PER_POSITIONS pos,
BEN_ELIGY_PRFL_F elig_profile
WHERE a.user_group_id in ( SELECT child_user_group_id
FROM ( SELECT a.child_user_group_id
FROM ota_user_group_elements a,
ota_user_groups_b b
WHERE a.user_group_id = b.user_group_id
and trunc(sysdate) between trunc(nvl(b.start_date_active, sysdate)) and
trunc(nvl(b.end_date_active, sysdate+1))
START WITH a.user_group_id = p_user_group_id
CONNECT BY PRIOR a.child_user_group_id = a.user_group_id
UNION ALL SELECT p_user_group_id FROM dual
) WHERE child_user_group_id is not null ) and
e.person_id(+) = a.person_id and
(e.effective_start_date is null or e.effective_start_date <= trunc(sysdate)) and
(e.effective_end_date is null or trunc(sysdate) <= e.effective_end_date) and
job.job_id(+) = a.job_id and
org.organization_id(+) = a.organization_id and
pos.position_id(+) = a.position_id and
(fnd_profile.value('OTA_HR_GLOBAL_BUSINESS_GROUP_ID') is not null or
a.business_group_id = p_business_group_id) and
-- decode (a.position_id, null, -1, pos.organization_id) = nvl(org.organization_id,-1) and --7157831
decode (a.position_id, null, -1, pos.organization_id) = decode(a.position_id, null, -1, org.organization_id) and --7248298
(e.person_id is not null or job.job_id is not null or pos.position_id is not null or
org.organization_id is not null or a.child_user_group_id is not null or elig_profile.eligy_prfl_id is not null)
and elig_profile.eligy_prfl_id(+) = a.elig_prfl_id
and (elig_profile.effective_start_date is null or trunc(elig_profile.effective_start_date) <= trunc(sysdate)) and
(elig_profile.effective_end_date is null or trunc(sysdate) <= trunc(elig_profile.effective_end_date))
and (a.person_id is null or a.elig_prfl_id is null);