2 Replies Latest reply on Feb 17, 2016 6:12 PM by 3122801

    Value Set creation using :$PROFILES$ values and Union

    3122801

      Friends,

       

      How or what kind of value set can be created from the below query, not sure about the creating a view on top of this query. any suggestions/ideas are greatly appreciated, query below...

      ========================================================

      select FULL_NAME||' ('||EMPLOYEE_NUMBER||')', PERSON_ID

      from PER_PEOPLE_F   P

         , FND_SESSIONS   FS

      where 1=1

         and p.business_group_id +0 = 102  --:$PROFILES$.PER_BUSINESS_GROUP_ID

        and   coalesce(fs.effective_date(+), trunc(sysdate)) between

        p.effective_start_date and p.effective_end_date

        and   p.current_employee_flag = 'Y'

        and fs.session_id(+) = USERENV('SESSIONID')

        and ((P.PERSON_ID = (select employee_id from fnd_user where user_id = :$PROFILES$.USER_ID)) and  (:$PROFILES$.RESP_ID = 23220))

      UNION

      SELECT

      distinct papf2.full_name ||' ('||papf2.EMPLOYEE_NUMBER||')' VALUE

           ,papf2.person_id    

        FROM hr.per_all_people_f           papf1

           , hr.per_all_assignments_f      paaf1

           , hr.per_all_assignments_f      paaf2

           , hr.per_all_people_f           papf2

           , fnd_user                      fu

           , fnd_user_resp_groups          furg

           , FND_RESPONSIBILITY            fr

           , fnd_responsibility_tl         frt

        WHERE  papf1.person_id = paaf1.person_id

        AND paaf1.supervisor_id = papf2.person_id(+)

        AND papf2.person_id = paaf2.person_id

        and fu.EMPLOYEE_ID  = papf1.person_id

        AND fu.user_id = furg.user_id

      AND furg.responsibility_id = fr.RESPONSIBILITY_ID

      AND frt.responsibility_id = fr.RESPONSIBILITY_ID

      AND fr.RESPONSIBILITY_ID  <> 23220    -- reject self service

        and (papf1.PERSON_ID = (select employee_id from fnd_user where user_id = :$PROFILES$.USER_ID))

        and  fr.RESPONSIBILITY_ID  = :$PROFILES$.RESP_ID;

      ========================================================


      Thanks,

      Jim