OTL Timekeepers Not Linked to OTL Responsibility
SELECT tg.tk_group_name timekeepr_group_name, papf2.employee_number,papf2.full_name
FROM hxc_tk_groups tg, per_all_people_f papf2, per_all_assignments_f paaf, hr_all_organization_units haou
WHERE tg.tk_resource_id = papf2.person_id
AND paaf.person_id = papf2.person_id
AND SYSDATE BETWEEN papf2.effective_start_date AND papf2.effective_end_date
AND papf2.current_employee_flag = 'Y'
AND haou.organization_id = paaf.organization_id
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.organization_id IN (SELECT org.organization_id
FROM hr_all_organization_units org, per_org_structure_elements pose
WHERE 1 = 1 AND org.organization_id = pose.organization_id_child
START WITH pose.organization_id_parent = :p_org_id --entity AT
CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent)
AND NOT EXISTS (
SELECT 1
FROM fnd_user fu, fnd_user_resp_groups furg, fnd_responsibility_tl fr
WHERE 1 = 1
AND fr.responsibility_name = <'OTL Timekeeper Entry'>
AND fu.employee_id = papf2.person_id
AND furg.user_id = fu.user_id
AND furg.responsibility_id = fr.responsibility_id
AND furg.responsibility_application_id = fr.application_id
AND fr.LANGUAGE = 'US')
ORDER BY 1,2,3;
FROM hxc_tk_groups tg, per_all_people_f papf2, per_all_assignments_f paaf, hr_all_organization_units haou
WHERE tg.tk_resource_id = papf2.person_id
AND paaf.person_id = papf2.person_id
AND SYSDATE BETWEEN papf2.effective_start_date AND papf2.effective_end_date
AND papf2.current_employee_flag = 'Y'
AND haou.organization_id = paaf.organization_id
AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date
AND paaf.organization_id IN (SELECT org.organization_id
FROM hr_all_organization_units org, per_org_structure_elements pose
WHERE 1 = 1 AND org.organization_id = pose.organization_id_child
START WITH pose.organization_id_parent = :p_org_id --entity AT
CONNECT BY PRIOR pose.organization_id_child = pose.organization_id_parent)
AND NOT EXISTS (
SELECT 1
FROM fnd_user fu, fnd_user_resp_groups furg, fnd_responsibility_tl fr
WHERE 1 = 1
AND fr.responsibility_name = <'OTL Timekeeper Entry'>
AND fu.employee_id = papf2.person_id
AND furg.user_id = fu.user_id
AND furg.responsibility_id = fr.responsibility_id
AND furg.responsibility_application_id = fr.application_id
AND fr.LANGUAGE = 'US')
ORDER BY 1,2,3;
0 Comments:
Post a Comment
Subscribe to Post Comments [Atom]
<< Home