6 Replies Latest reply on Feb 12, 2016 4:21 PM by 3122801

    Value Set for a LOV based on the responsibility

    3122801

      Friends,

       

      I am working on a concurrent program where one of the parameter is using a value Set (LOV). I came up with a table based value set which does not work as intended. Below is the logic and the code so far... I would like to get some help to make this work.

       

      Logic:

      =====

      1) fnd user when logged in with "Self Service" responsibility : He should be able to look up his full name only with employee number and run the report.

      2) The above user when logged in as say "HR mgr" responsibility : He should be able to look up all his employees name only with employee number and run the report for them.

       

      Code Below ( So far):

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

      SELECT

         papf2.full_name ||' ('||papf2.EMPLOYEE_NUMBER||')', papf2.person_id

         from

         per_people_f  papf1

        ,per_assignments_f  paaf1

        ,per_assignments_f   paaf2

        ,per_people_f  papf2

        ,FND_SESSIONS  FS

        WHERE  papf1.person_id = paaf1.person_id

        AND paaf1.supervisor_id = papf2.person_id(+)

        AND papf2.person_id = paaf2.person_id

        AND papf1.business_group_id +0 = :$PROFILES$.PER_BUSINESS_GROUP_ID

        AND   papf1.current_employee_flag = 'Y'

        AND COALESCE(fs.effective_date(+), TRUNC(sysdate)) BETWEEN papf1.effective_start_date AND papf1.effective_end_date 

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

      AND (

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

           or  

            ((papf1.PERSON_ID = papf1.PERSON_ID ) AND (:$PROFILES$.RESP_ID = :$PROFILES$.RESP_ID))  

      )

       

       

      Thanks In Advance,

      Jim

        • 1. Re: Value Set for a LOV based on the responsibility
          pillair

          What is the issue that you are facing?

          • 2. Re: Value Set for a LOV based on the responsibility
            Jagadekara

            Hi,

             

            You hard coded resp id.

             

            Also what is the exact problem now?

            • 3. Re: Value Set for a LOV based on the responsibility
              3122801

              The issue I am facing :  The LOV is not fetching me any data.

              • 4. Re: Value Set for a LOV based on the responsibility
                3122801

                Hard Code reason : The resp id : 23220 is Self Service Time where the report is registered.

                • 5. Re: Value Set for a LOV based on the responsibility
                  3122801

                  Ok, looks like I am explaining things clearly here,

                  -- Parameter : Employee Name||PERSON_ID

                   

                  When employee_name and PERSON_ID is selected in the parameter, the person id is passed as a value to the PL/SQL Stored Procedure

                   

                  The issue I am facing :  The LOV is not fetching me any data.

                  Hard Code reason : The resp id : 23220 is Self Service Time where the report is registered.

                   

                  Again, The code I gave was still under development so sorry if that is miss guiding. Let me try to phrase the value set requirement in a different way. Let say I am an employee and also a mgr for 5 other employees

                   

                   

                  case 1: If I log into apps and try to run the report under "Self Service time" responsibility the Parameter : Employee Name, should show or let me query only my full name in the LOV. this report will extract my data for the employee pay rate report.

                   

                   

                  case 2: If I log into apps and try to run the report under "HR mgr" responsibility I should be able to see all my 5 employees in the LOV and I should be able to extract the employee pay rate report for only my 5 employees whom i supervise.

                   

                   

                  Please let me know if I made it clear, any idea's or solution will be greatly appreciated.

                   

                   

                  Jim

                  • 6. Re: Value Set for a LOV based on the responsibility
                    3122801

                    Friends,

                    Below is another query I came up with just now, timebeing If this query created as a value set for the (parameter:employee_name||Person_Id) it will fetch me all the employee.

                    Now, If I log in with "Seld Servie time" responsibility and try to run the report the lov should show only my name and  it should show only my name based on the my session_id.

                    When I log in as "HR Manager" responsibility along with my session_id the lov should show all my 5 employees whom i supervise and i should be able to run the report for them.

                     

                    -----------------------------------------------------------------------------------------------

                    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');

                    -----------------------------------------------------------------------------------------------


                    Thanks in advance,

                    Jim