Friday, April 25, 2014

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;

0 Comments:

Post a Comment

Subscribe to Post Comments [Atom]

<< Home