[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 1
from wf_item_attribute_values wiav
where wiav.item_type = p_item_type
and wiav.item_key = p_item_key
and wiav.name = p_name;
SELECT wrpv.display_name displayName
FROM wf_runnable_processes_v wrpv
WHERE wrpv.item_type = l_item_type
AND wrpv.process_name = l_process;
SELECT user_name
FROM fnd_user
WHERE employee_id=p_person_id;
SELECT lpt.name path_name,
lpe.person_id person_id,
lpe.contact_id contact_id,
lpe.completion_target_date completion_target,
lpe.completion_date,
lps.notify_days_before_target,
lpe.creator_person_id
FROM ota_learning_paths lps,
ota_learning_paths_tl lpt,
ota_lp_enrollments lpe
WHERE lpt.language = USERENV('LANG')
AND lps.learning_path_id = lpt.learning_path_id
AND lpe.lp_enrollment_id = p_lp_enrollment_id
AND lpt.learning_path_id = lpe.learning_path_id;
SELECT avt.version_name course_name,
lpe.person_id person_id,
lpe.contact_id contact_id,
lpme.completion_target_date completion_target,
lpt.name path_name,
lpe.creator_person_id,
lpm.notify_days_before_target
FROM ota_lp_member_enrollments lpme,
ota_learning_path_members lpm,
ota_activity_versions_tl avt,
ota_lp_enrollments lpe,
ota_learning_paths lps,
ota_learning_paths_tl lpt
WHERE lpme.lp_member_enrollment_id = p_lp_member_enrollment_id
AND lps.learning_path_id = lpt.learning_path_id
AND avt.language = USERENV('LANG')
AND avt.activity_version_id = lpm.activity_version_id
AND lpm.learning_path_member_id = lpme.learning_path_member_id
AND lpe.lp_enrollment_id = lpme.lp_enrollment_id
AND lpt.learning_path_id = lpm.learning_path_id
AND lpt.language = USERENV('LANG');
SELECT PARTY.party_name contact_name
FROM HZ_CUST_ACCOUNT_ROLES acct_role,
HZ_PARTIES party,
HZ_RELATIONSHIPS rel,
HZ_ORG_CONTACTS org_cont,
HZ_CUST_ACCOUNTS role_acct
WHERE acct_role.party_id = rel.party_id
and acct_role.role_type = 'CONTACT'
and org_cont.party_relationship_id = rel.relationship_id
and rel.subject_id = party.party_id
and rel.subject_table_name = 'HZ_PARTIES'
and rel.object_table_name = 'HZ_PARTIES'
and acct_role.cust_account_id = role_acct.cust_account_id
and role_acct.party_id = rel.object_id
AND acct_role.cust_account_role_id = l_contact_id;
SELECT ppf.full_name
FROM per_all_people_f ppf
WHERE person_id = p_person_id
AND TRUNC(SYSDATE) BETWEEN effective_start_date AND effective_end_date;--Bug 13826398
SELECT lpst.name
FROM ota_lp_sections_tl lpst,
ota_lp_member_enrollments lpme,
ota_learning_path_members lpm
WHERE lpme.learning_path_member_id = lpm.learning_path_member_id
AND lpst.learning_path_section_id = lpm.learning_path_section_id
AND lpst.language = USERENV('LANG')
AND lpme.lp_member_enrollment_id = p_lp_member_enrollment_id;
SELECT user_name
FROM fnd_user
WHERE employee_id=p_person_id;
SELECT usr.user_name
FROM
hz_parties party,
fnd_user usr,
hz_cust_account_roles rol
WHERE
rol.party_id = party.party_id
AND rol.party_id = usr.customer_id
AND rol.cust_account_role_id = p_contact_id;
select hr_workflow_item_key_s.nextval
into l_item_key
from sys.dual;
/*SELECT user_name INTO l_user_name
FROM fnd_user
WHERE employee_id=l_person_id
AND ROWNUM =1 ; */
/*SELECT usr.user_name INTO l_user_name
FROM
hz_parties party,
fnd_user usr,
hz_cust_account_roles rol
WHERE
rol.party_id = party.party_id
AND rol.party_id = usr.customer_id
AND rol.cust_account_role_id = l_contact_id;*/
SELECT ppf.full_name, ppf.person_id
FROM ota_lp_enrollments lpe,
ota_learning_paths lps,
per_all_people_f ppf,
per_all_assignments_f paf
WHERE 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 paf.person_id = lpe.person_id
AND paf.supervisor_id = ppf.person_id
AND paf.primary_flag = 'Y'
AND lps.learning_path_id = lpe.learning_path_id
AND lpe.person_id <> lpe.creator_person_id
AND lpe.lp_enrollment_id = l_lp_enrollment_id;
SELECT user_name
FROM fnd_user
WHERE employee_id=l_person_id
and trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
SELECT ppf.full_name, ppf.person_id
FROM ota_lp_enrollments lpe,
ota_learning_paths lps,
per_all_people_f ppf
WHERE trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date
AND ppf.person_id = lpe.creator_person_id
AND lpe.lp_enrollment_id = l_lp_enrollment_id
AND lpe.learning_path_id = lps.learning_path_id;
SELECT user_name
FROM fnd_user
WHERE employee_id=l_person_id
and trunc(sysdate) between start_date and nvl(end_date,to_date('4712/12/31', 'YYYY/MM/DD')); --Bug 5676892
SELECT 1
FROM wf_items
WHERE item_type = 'OTWF'
AND item_key = p_itemkey;
SELECT lpe.lp_enrollment_id
,lpe.person_id
,lpe.contact_id
,lpe.creator_person_id
,lps.notify_days_before_target
FROM ota_learning_paths lps,
ota_lp_enrollments lpe
WHERE lps.learning_path_id = lpe.learning_path_id
AND lpe.path_status_code = 'ACTIVE'
AND trunc(sysdate) + lps.notify_days_before_target = trunc(lpe.completion_target_date)
AND lps.business_group_id = ota_general.get_business_group_id;
SELECT lpme.lp_member_enrollment_id
,lpe.person_id
,lpe.creator_person_id
,lps.path_source_code
,lpm.notify_days_before_target
FROM ota_learning_path_members lpm,
ota_lp_member_enrollments lpme,
ota_learning_paths lps,
ota_lp_enrollments lpe,
ota_lp_sections lpc
WHERE lpe.learning_path_id = lps.learning_path_id
AND lpc.learning_path_id = lps.learning_path_id
AND lpc.learning_path_section_id = lpm.learning_path_section_id
AND lpme.learning_path_member_id = lpm.learning_path_member_id
AND lpme.lp_enrollment_id = lpe.lp_enrollment_id
AND lpe.path_status_code NOT IN ('CANCELLED', 'COMPLETED')
AND lpme.member_status_code NOT IN ('CANCELLED','COMPLETED')
AND trunc(sysdate) + lpm.notify_days_before_target = trunc(lpme.completion_target_date)
AND (lpc.completion_type_code = 'M'
OR (lpc.completion_type_code = 'S'
AND lpc.no_of_mandatory_courses < (SELECT count(lp_member_enrollment_id)
FROM ota_lp_member_enrollments
WHERE learning_path_section_id = lpc.learning_path_section_id
AND lp_enrollment_id = lpe.lp_enrollment_id
AND member_status_code = 'COMPLETED')))
AND lps.business_group_id = ota_general.get_business_group_id;