Tuesday, 14 October 2014

HRMS Supervisor Query

/* Formatted on 10/14/2014 3:27:07 PM (QP5 v5.115.810.9015) */
SELECT   fu1.user_name
  FROM   apps.per_all_people_f papf1, fnd_user fu1
 WHERE   papf1.current_employee_flag = 'Y'
         AND SYSDATE BETWEEN papf1.effective_start_date
                         AND  papf1.effective_end_date
         AND fu1.employee_id = papf1.person_id
         AND papf1.person_id =
               (SELECT   paaf.supervisor_id
                  FROM   fnd_user fu,
                         apps.per_all_people_f papf,
                         apps.per_all_assignments_f paaf,
                         ogs_oracle_user_access_headers oouah
                 WHERE       oouah.header_id = #DISPOSITION_ID1#
                         AND fu.employee_id = papf.person_id
                         AND fu.user_id = oouah.user_id
                         AND papf.person_id = paaf.person_id
                         AND paaf.primary_flag = 'Y'
                         AND paaf.assignment_type = 'E'
                         AND papf.business_group_id = paaf.business_group_id
                         AND SYSDATE BETWEEN papf.effective_start_date
                                         AND  papf.effective_end_date
                         AND SYSDATE BETWEEN paaf.effective_start_date
                                         AND  paaf.effective_end_date)

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.