2 Replies Latest reply on Mar 6, 2012 2:54 PM by Vesuti

    How can AME approval w/ position hierarchy  check the approval limit

    user629232
      Dear all,

      In the standard approval process by position hierarchy, system will check against the approval assignment to the position , e.g. the limit, item category, document total allowed for this position.

      However, if we change to use AME approval with position hierarchy, it seems that it can only check the no. of positions required for the approval. E.g. we can set to required at least 3 position level for approval.; However it seems that it did not look at the approval assignment to this position. The approval process will be completed if all required positions are passed, even if the position does not have enough approval limit.


      Could any one please advise how to setup the AME , so that it can work similar to the standard position hierarchy? Especially we must check the approval limit of each position.
        • 1. Re: How can AME approval w/ position hierarchy  check the approval limit
          827866
          Dear,


          I already have this problem in AME, but I found a better solution for that. First of all, AME doesn't check the approval Groups/Assignments as well the approval limit. Therefore I build a functions (with some help in this site) that help me to retrieve the final approver based on the approval limit with approval group.

          FUNCTION Get_PR_Final_Approver_Pos_Id (p_transactionId IN NUMBER,
          p_start_position_id IN NUMBER)
          RETURN NUMBER
          IS
          l_ret_value NUMBER;
          l_curr_position_id NUMBER;
          l_top_position_id NUMBER;
          l_curr_appr_limit NUMBER;
          l_curr_req_total NUMBER;
          l_req_org_id NUMBER;
          l_counter1 NUMBER;
          l_curr_appr_counter1 NUMBER;
          valid_flag_ini VARCHAR2(2);
          valid_flag VARCHAR2(1);
          l_pm_flag NUMBER;

          BEGIN
          /*select APPS.PO_AEC.Get_Check_PM (p_transactionId) into l_pm_flag from dual;
          if l_pm_flag=0 then
          l_curr_position_id := p_start_position_id;
          else
          SELECT APPS.PO_AEC.Get_PR_Parent_Pos_Id (
          (SELECT position_id
          FROM APPS.PER_ASSIGNMENTS_F f
          WHERE person_id in (l_pm_flag)
          AND sysdate between F.EFFECTIVE_START_DATE and F.EFFECTIVE_END_DATE
          AND F.ASSIGNMENT_TYPE='E'),APPS.PO_AEC.Get_PR_Hierarchy_id (p_transactionId))
          INTO l_curr_position_id
          FROM DUAL;
          end if;*/
          SELECT apps.PO_AEC.Get_PR_First_Approver_Pos_Id(p_transactionId,p_start_position_id)
          INTO l_curr_position_id
          FROM DUAL;
          --need to be replaced with real top position of hierarchy
          l_top_position_id := -9999;

          SELECT prha.org_id
          INTO l_req_org_id
          FROM po_requisition_headers_all prha
          WHERE prha.requisition_header_id = p_transactionId;

          --starting search from specified position

          --l_curr_position_id := p_start_position_id;

          SELECT PO_AME_SETUP_PVT.get_changed_req_total (p_transactionId)
          INTO l_curr_req_total
          FROM DUAL;

          -- declare the cursor--
          DECLARE
          CURSOR CUR_SEG IS
          SELECT DISTINCT C.SEGMENT3 C3,C.SEGMENT4 C4
          FROM PO.po_requisition_headers_all H,PO.po_requisition_lines_all L,PO.po_req_distributions_all D,GL.gl_code_combinations C
          WHERE
          H.REQUISITION_HEADER_ID=L.REQUISITION_HEADER_ID
          AND L.REQUISITION_LINE_ID=D.REQUISITION_LINE_ID
          AND D.CODE_COMBINATION_ID=C.CODE_COMBINATION_ID
          AND H.REQUISITION_HEADER_ID=p_transactionId;
          CUR_SEG_REC CUR_SEG%ROWTYPE;
          BEGIN
          valid_flag_ini := 'GO';
          for CUR_SEG_REC in CUR_SEG
          loop
          BEGIN
          EXIT WHEN CUR_SEG%NOTFOUND;
          SELECT --DISTINCT
          ER.AMOUNTLIMIT
          INTO l_curr_appr_limit--ER.POSITION_ID,ER.APPROVER_NAME,ER.CC_LOW,ER.CC_HIGH,ER.ACC_LOW,ER.ACC_HIGH
          FROM
          APPS.ER_APPROVAL_LIMITS ER
          WHERE
          ER.POSITION_ID=l_curr_position_id
          AND ER.AMOUNTLIMIT is not null
          AND CUR_SEG_REC.C3 BETWEEN ER.CC_LOW AND ER.CC_HIGH
          AND CUR_SEG_REC.C4 BETWEEN ER.ACC_LOW AND ER.ACC_HIGH
          AND NOT EXISTS (
          SELECT 1--PCR.SEGMENT3_LOW,PCR.SEGMENT3_HIGH,PCR.SEGMENT4_LOW,PCR.SEGMENT4_HIGH
          FROM po.po_control_rules pcr,
          po.po_control_groups_all pcga,
          po.po_position_controls_all ppca
          WHERE 1 = 1
          AND pcr.control_group_id = pcga.control_group_id
          AND ppca.control_group_id = pcga.control_group_id
          AND ppca.position_id = l_curr_position_id
          AND ppca.control_function_id = 8 --purchase requisition
          AND ppca.org_id = pcga.org_id
          AND ppca.org_id = 1
          AND pcga.enabled_flag = 'Y'
          AND pcr.rule_type_code = 'EXCLUDE'
          AND pcr.object_code in ('ACCOUNT_RANGE')
          AND CUR_SEG_REC.C3 between pcr.segment3_low and pcr.segment3_high
          AND CUR_SEG_REC.C4 between pcr.segment4_low and pcr.segment4_high
          )
          AND ROWNUM = 1
          ORDER BY ER.AMOUNTLIMIT ASC;
          EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
          valid_flag_ini := 'ST';
          l_curr_appr_limit := 0;
          END;

          END LOOP;
          IF valid_flag_ini = 'GO' AND l_curr_appr_limit >= l_curr_req_total THEN
          valid_flag := 'Y';
          ELSE
          valid_flag := 'N';
          END IF;
          END;
          --counter to avoid unlimited loop
          l_counter1 := 15;

          --intiate the cursor again
          DECLARE
          CURSOR CUR_SEG IS
          SELECT DISTINCT C.SEGMENT3 C3,C.SEGMENT4 C4
          FROM PO.po_requisition_headers_all H,PO.po_requisition_lines_all L,PO.po_req_distributions_all D,GL.gl_code_combinations C
          WHERE
          H.REQUISITION_HEADER_ID=L.REQUISITION_HEADER_ID
          AND L.REQUISITION_LINE_ID=D.REQUISITION_LINE_ID
          AND D.CODE_COMBINATION_ID=C.CODE_COMBINATION_ID
          AND H.REQUISITION_HEADER_ID=p_transactionId;
          CUR_SEG_REC CUR_SEG%ROWTYPE;
          --go upper by hierarchy till top position is reached or till approval limit is enough for requisition
          BEGIN
          WHILE 1 = 1
          --AND valid_flag_ini = 'ST'
          AND valid_flag != 'Y'
          AND l_top_position_id != l_curr_position_id
          --AND l_curr_appr_limit < l_curr_req_total
          AND l_counter1 > 0
          LOOP
          SELECT apps.PO_AEC.Get_PR_Parent_Pos_Id (l_curr_position_id,
          apps.PO_AEC.Get_PR_Hierarchy_id (p_transactionId))
          INTO l_curr_position_id
          FROM DUAL;

          BEGIN
          valid_flag_ini := 'GO';
          for CUR_SEG_REC in CUR_SEG
          loop
          BEGIN
          EXIT WHEN CUR_SEG%NOTFOUND;
          SELECT --DISTINCT
          ER.AMOUNTLIMIT
          INTO l_curr_appr_limit--ER.POSITION_ID,ER.APPROVER_NAME,ER.CC_LOW,ER.CC_HIGH,ER.ACC_LOW,ER.ACC_HIGH
          FROM
          APPS.ER_APPROVAL_LIMITS ER
          WHERE
          ER.POSITION_ID=l_curr_position_id
          AND ER.AMOUNTLIMIT is not null
          AND CUR_SEG_REC.C3 BETWEEN ER.CC_LOW AND ER.CC_HIGH
          AND CUR_SEG_REC.C4 BETWEEN ER.ACC_LOW AND ER.ACC_HIGH
          AND NOT EXISTS (
          SELECT 1--PCR.SEGMENT3_LOW,PCR.SEGMENT3_HIGH,PCR.SEGMENT4_LOW,PCR.SEGMENT4_HIGH
          FROM po.po_control_rules pcr,
          po.po_control_groups_all pcga,
          po.po_position_controls_all ppca
          WHERE 1 = 1
          AND pcr.control_group_id = pcga.control_group_id
          AND ppca.control_group_id = pcga.control_group_id
          AND ppca.position_id = l_curr_position_id
          AND ppca.control_function_id = 8 --purchase requisition
          AND ppca.org_id = pcga.org_id
          AND ppca.org_id = 1
          AND pcga.enabled_flag = 'Y'
          AND pcr.rule_type_code = 'EXCLUDE'
          AND pcr.object_code in ('ACCOUNT_RANGE')
          AND CUR_SEG_REC.C3 between pcr.segment3_low and pcr.segment3_high
          AND CUR_SEG_REC.C4 between pcr.segment4_low and pcr.segment4_high
          )
          AND ROWNUM = 1
          ORDER BY ER.AMOUNTLIMIT ASC;
          EXCEPTION
          WHEN NO_DATA_FOUND
          THEN
          valid_flag_ini := 'ST';
          l_curr_appr_limit := 0;
          END;
          END LOOP;
          IF valid_flag_ini = 'GO' AND l_curr_appr_limit >= l_curr_req_total THEN
          valid_flag := 'Y';
          ELSE
          valid_flag := 'N';
          END IF;
          END;
          l_counter1 := l_counter1 - 1;
          END LOOP;
          END;
          l_ret_value := l_curr_position_id;
          RETURN l_ret_value;
          END Get_PR_Final_Approver_Pos_Id;
          • 2. Re: How can AME approval w/ position hierarchy  check the approval limit
            Vesuti
            Hi Ahmed, please can you give more details how and where you used this function.

            Regards

            SK