12 Replies Latest reply: Jun 18, 2014 3:11 AM by 801619 RSS

    AME Setup with Supervisor Hierarchy

    801619

      Hi Experts,

       

      I'm working on EBS R12, i'm working on AME set up.

       

      1.Created one transaction type

      2.Created required attributes, conditions.

      3.Assigned required Action Type and Action.

       

      I'm using 'Super Level Atmost 2 superiors' Action. I'm using procedure 'ame_api2.getNextApprovers1' to pick up the list of users which are set in AME.

       

      Query: Above procedure is picking up the list, but it's not stopping the supervisor list at 2 levels, it's keep on going to next levels also.

       

      Could somebody help me why it's not stopping at 2 levels, anything else is missing, please help.

       

      Thanks

        • 1. Re: AME Setup with Supervisor Hierarchy
          801619

          Hi Experts,

           

          Could somebody help on this.

           

           

          Thanks

          • 2. Re: AME Setup with Supervisor Hierarchy
            Forenoon

            did you create a new AME handler for new action type ?

            you mentioned in point 3 :

            3.Assigned required Action Type and Action.

             

            did you use an existed action type or you create a new one ? if you create a new one did you create its new handler ?

             

            regards

            • 3. Re: AME Setup with Supervisor Hierarchy
              801619

              Thanks for your reply.

               

              I have used existing action type, please help.

               

              Thanks

              • 4. Re: AME Setup with Supervisor Hierarchy
                Arif Hussain

                I think creating a new approver group/action combination using this query should help you.You need to write a separate function to get the persons supervisor_id from the transaction. Again, this is a customized method, the seeded action may work for you, but I find it simple to use my own action types, rules, conditions and groups.

                 

                SELECT level, 

                (

                select distinct person_id from per_all_people_f

                where person_id = paf.person_id

                and sysdate between effective_start_date and effective_end_date

                ) super_person_id

                FROM per_all_assignments_f paf

                START WITH paf.person_id = <get first supervisor id of the person from transaction>

                AND paf.primary_flag = 'Y'

                AND paf.assignment_type='E'

                AND SYSDATE BETWEEN paf.effective_start_date AND  paf.effective_end_date

                CONNECT BY paf.person_id = PRIOR paf.supervisor_id

                AND paf.primary_flag = 'Y'

                AND SYSDATE BETWEEN paf.effective_start_date AND  paf.effective_end_date

                and level < 3

                 

                Thanks,

                Arif.

                • 5. Re: AME Setup with Supervisor Hierarchy
                  Forenoon

                  hi , where did you use the function exactly ?

                  • 6. Re: AME Setup with Supervisor Hierarchy
                    801619

                    HI Arif,

                     

                    Sorry for belated reply....I tried creating approval group with dynamic query which picks 2 levels of approval and attached to the rule, but it's still going to beyond 2 levels of approvals, i don't know where it's going wrong; just to test, i created Approval group with static values (assigned 2 users), then it's working fine. I don't know where it's going on, am i missing any setup, please help.

                     

                    Thanks

                    • 7. Re: AME Setup with Supervisor Hierarchy
                      Forenoon

                      could you please paste here what the code you had used in the dynamic part ?

                      • 8. Re: AME Setup with Supervisor Hierarchy
                        801619

                        Hi,

                         

                        Below is the query i used in approval group, when i check the query in database it's returning only 2 levels, please check and help.

                         

                        SELECT

                        (

                        select distinct 'person_id:'||person_id

                        from per_all_people_f

                        where person_id = paf.person_id

                        and sysdate between effective_start_date and effective_end_date

                        )person_id

                        FROM per_all_assignments_f paf

                        START WITH paf.person_id =(select supervisor_id from per_all_assignments_f papf where person_id in (select employee_id

                                  from fnd_user

                                 where user_name =FND_GLOBAL.USER_NAME)

                                   and trunc(sysdate) between effective_start_date and effective_end_date

                                  AND papf.primary_flag = 'Y')

                        AND paf.assignment_type='E'

                        AND SYSDATE BETWEEN paf.effective_start_date AND  paf.effective_end_date

                        CONNECT BY paf.person_id = PRIOR paf.supervisor_id

                        AND paf.primary_flag = 'Y'

                        AND SYSDATE BETWEEN paf.effective_start_date AND  paf.effective_end_date

                        and level < =2

                         

                        Thanks

                        • 9. Re: AME Setup with Supervisor Hierarchy
                          Forenoon

                          you confused me, at the first post you say :

                          "

                          Query: Above procedure is picking up the list, but it's not stopping the supervisor list at 2 levels, it's keep on going to next levels also.

                           

                          Could somebody help me why it's not stopping at 2 levels, anything else is missing, please help.

                           

                          and now you say :

                              

                          Below is the query i used in approval group, when i check the query in database it's returning only 2 levels

                           

                          any way, if you want the last query to retrieve more than 2 level , just remove the last condition :


                          (and level < = 2 )   if you remove this it will bring all the supervisory chain starting from the user .

                          regards.

                          • 10. Re: AME Setup with Supervisor Hierarchy
                            801619

                            Hi,

                             

                            Sorry for the confusion, my requirement is always to send approval for first 2 supervisor level, initially i used standard action type 'supervisor hierarchy with at most 2 levels', but it's not stopping at 2 levels and keep on sending to 3rd level, 4th level etc....so as Arif suggested, instead of using standard action type, i tried using approval group with dynamic query which will pick only 2 level of supervisor, but this also keeping on sending to 3rd level, 4th level etc., for testing purpose whether AME is working or not, instead of dynamic query in approval group, i used static values with 2 users added, then it's working fine (sending to those 2 users added).

                             

                            I didn't understand if the approval group is working with static values, why not with dynamic query which also picks supervisor list till 2 levels, am i missing any setup? Kindly help.

                             

                            Thanks

                            • 11. Re: AME Setup with Supervisor Hierarchy
                              Forenoon

                              Hi,

                              i get your point now.the previous query depend on the connect user, i am not sure if this make the query will get the two top level at every time the new user connect to approve.

                               

                              try the following query it will bring the two top level depending on the first person only :

                               

                               

                              SELECT

                              (

                              select distinct 'person_id:'||person_id

                              from per_all_people_f

                              where person_id = paf.person_id

                              and sysdate between effective_start_date and effective_end_date

                              )person_id

                              FROM per_all_assignments_f paf

                              START WITH paf.person_id =(SELECT hr_ameutil_ss.get_selected_person_id(:transactionId) FROM DUAL)

                              AND paf.assignment_type='E'

                              AND SYSDATE BETWEEN paf.effective_start_date AND  paf.effective_end_date

                              CONNECT BY paf.person_id = PRIOR paf.supervisor_id

                              AND paf.primary_flag = 'Y'

                              AND SYSDATE BETWEEN paf.effective_start_date AND  paf.effective_end_date

                              and level < =2

                              =======

                               

                              i which this could help!

                              regards

                              • 12. Re: AME Setup with Supervisor Hierarchy
                                801619

                                Hi,

                                 

                                I tried your query, but it's not picking up any approvers, i don't know what could be reason, i think it's not passing the transaction id. But using my previous query i found below error message in exception log.

                                 

                                Error: The approver passed to ame_api2.updateApprovalStatus does not match anyone on the list of approvers.

                                 

                                For static values, no error is coming, i think for dynamic query, something is wrong, not able to catch exact reason, could somebody help on this.

                                 

                                Thanks