Add PQH Roles and add users to it

Complete procedure to add PQH Roles and add users to it

PROCEDURE mpl_cse_create_roles(
errbuf OUT NOCOPY  VARCHAR2,
RETCODE OUT NOCOPY VARCHAR2)
IS
/*************************************************************************************
**  Name       : MPL_CSE_CREATE_ROLES
**  DESCRIPTION: This procedure used to define roles for AME
**************************************************************************************/
l_num_role_id      pqh_roles.role_id%TYPE;
l_num_object_version_number   pqh_roles.object_version_number%type;
l_num_person_extra_info_id   per_people_extra_info.person_extra_info_id%type;
l_num_ovn_pie      per_people_extra_info.object_version_number%type;
l_num_person_id      per_people_extra_info.person_id%type;
l_num_loop_count        NUMBER;
l_var_user              VARCHAR2(1000);
l_var_err               VARCHAR2(1000);
l_num_addusrrole_id     NUMBER;
l_var_process           VARCHAR2(10);
CURSOR cur_role
IS
SELECT *
FROM mpl_cse_role_conv_stg
WHERE process_flag IS NULL;
BEGIN
l_num_object_version_number := NULL;
l_num_role_id               := NULL;
l_num_person_id             := NULL;
l_num_person_extra_info_id  := NULL;
l_num_ovn_pie               := NULL;
FOR cur_role_rec IN cur_role
LOOP
BEGIN
pqh_roles_api.create_role
( p_validate          => FALSE ,
p_role_id           => l_num_role_id ,
p_role_name         => cur_role_rec.role_name ,
p_business_group_id   => 0 ,
p_role_type_cd    => ‘EMP’ ,
p_enable_flag    => ‘Y’ ,
p_object_version_number  => l_num_object_version_number ,
p_effective_date    => SYSDATE ,
p_information_category  => cur_role_rec.location
);
COMMIT;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
l_var_err     := SQLERRM;
l_var_process :=’N’;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = ‘Role Could not be  created ‘
|| l_var_err
WHERE role_name = cur_role_rec.role_name;
FND_FILE.PUT_LINE(FND_FILE.LOG,’Role Could not be  created’);
END;
/*adding users to role*******/
/*checking number of user in a role*/
BEGIN
SELECT DECODE(cur_role_rec.member_1, NULL, 0, 1) + DECODE(cur_role_rec.member_2, NULL, 0, 1) + DECODE(cur_role_rec.member_3, NULL, 0, 1)
INTO l_num_loop_count
FROM dual;
EXCEPTION
WHEN OTHERS THEN
l_num_loop_count := 0;
l_var_process    :=’N’;
END;
IF cur_role_rec.member_1 IS NOT NULL OR cur_role_rec.member_2 IS NOT NULL OR cur_role_rec.member_3 IS NOT NULL THEN
FOR i IN 1..l_num_loop_count
LOOP
l_var_user          := NULL;
l_num_person_id     := NULL;
l_num_addusrrole_id := NULL;
l_var_process       := NULL;
IF i                 =1 THEN
l_var_user        := cur_role_rec.member_1_emp_num;
ELSIF i              =2 THEN
l_var_user        := cur_role_rec.member_2_emp_num;
ELSIF i              =3 THEN
l_var_user        := cur_role_rec.member_3_emp_num;
END IF;
BEGIN
SELECT person_id
INTO l_num_person_id
FROM per_all_people_f
WHERE employee_number =l_var_user
AND SYSDATE BETWEEN effective_start_date AND effective_end_date;
EXCEPTION
WHEN OTHERS THEN
l_var_process :=’N’;
l_var_user    := NULL;
dbms_output.put_line(‘person_id:’||SQLERRM);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ User could not be added to Role. Employee Number: ‘
||cur_role_rec.member_1_emp_num
WHERE role_name = cur_role_rec.role_name;
END;
BEGIN
SELECT role_id
INTO l_num_addusrrole_id
FROM pqh_roles
WHERE role_name = cur_role_rec.role_name;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘role_id:’||SQLERRM);
l_var_process       :=’N’;
l_num_addusrrole_id := NULL;
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
||’ Role not valid’
WHERE role_name = cur_role_rec.role_name;
END;
IF NVL(l_var_process,’Y’) !=’N’ THEN
BEGIN
hr_person_extra_info_api.create_person_extra_info
( p_validate       => FALSE ,
p_person_id      => l_num_person_id ,
p_information_type     => ‘PQH_ROLE_USERS’ ,
p_pei_information_category   => ‘PQH_ROLE_USERS’ ,
p_pei_information3     => l_num_addusrrole_id ,
p_pei_information4     => ‘N’ ,
p_pei_information5     => ‘Y’ ,
p_pei_information9     => ‘N’ ,
p_person_extra_info_id    =>l_num_person_extra_info_id ,
p_object_version_number   =>l_num_ovn_pie
);
IF l_num_person_extra_info_id IS NOT NULL THEN
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘S’
WHERE role_name  = cur_role_rec.role_name;
ELSE
DBMS_OUTPUT.PUT_LINE(‘USER NOT LOADED :’||l_num_person_id);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘Seeded api ERROR hr_person_extra_info_api.create_person_extra_info ‘
WHERE role_name = cur_role_rec.role_name;
END IF;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
fnd_file.put_line(fnd_file.log,’user Could not be  be added to role’);
dbms_output.put_line(‘user Could not be  be added to role’||SQLERRM);
l_var_err := SUBSTR(SQLERRM,1000);
UPDATE mpl_cse_role_conv_stg
SET process_flag = ‘E’,
error_message  = error_message
|| ‘user could not be adde for perosn_id’
||l_num_person_id
WHERE role_name = cur_role_rec.role_name;
END;
END IF;
END LOOP;
END IF ;
COMMIT;
END LOOP;
END mpl_cse_create_roles;

Advertisements

Create Position Control roles(pqh_roles)

The following script can be used for creating Position Control roles used for AME.

Table : PQH_ROLES

Navigation : Global HRMS Manger –> Ctrl + L –> Maintain Roles

 

Capture

 

DECLARE
L_NUM_ROLE_ID  pqh_roles.role_id%TYPE;
l_num_object_version_number  pqh_roles.object_version_number%TYPE;
BEGIN
pqh_roles_api.create_role
(
P_VALIDATE                      => FALSE
,P_ROLE_ID                       => L_NUM_ROLE_ID
,P_ROLE_NAME                    => ‘DEMO_ROLE’
,P_ROLE_TYPE_CD                  =>’EMP’
,P_ENABLE_FLAG                   => ‘Y’
,P_OBJECT_VERSION_NUMBER         =>L_NUM_OBJECT_VERSION_NUMBER
— ,p_business_group_id              in  number    default null
,P_EFFECTIVE_DATE                =>SYSDATE
, P_INFORMATION_CATEGORY        =>NULL
,p_information4=> ‘IN-BAN19’
);
COMMIT;
END;

/***********************END********************************/

 

 

 

How to check issues in AME ?

Log into SQL plus/ SQL Developer. and run the following script as :

edit @1

> @1

We can use  the following file to check issues in AME.

set serveroutput on size 100000
declare 
  cursor getApplications is
  select application_id,application_name
    from apps.ame_calling_apps aca
   where sysdate < nvl(aca.end_date-(1/86400),sysdate+1);
 
  cursor checkConfigVars(appId in number) is
    select ‘Transaction Type’ configType,variable_name
      from apps.AME_CONFIG_VARS
     where application_id  = appId
       and sysdate < nvl(end_date,sysdate + 1)
       and variable_name in (‘allowAllApproverTypes’,’allowAllItemClassRules’,’allowFyiNotifications’,’productionFunctionality’)
       and variable_value not in (‘no’,’none’)
     union
    select ‘Default’,variable_name
      from apps.AME_CONFIG_VARS
     where nvl(application_id, 0) = 0
       and sysdate < nvl(end_date,sysdate + 1)
       and variable_name in (‘allowAllApproverTypes’,’allowAllItemClassRules’,’allowFyiNotifications’,’productionFunctionality’)
       and variable_value not in (‘no’,’none’)
       and not exists (select null from apps.AME_CONFIG_VARS B
         where application_id  = appId
                         and sysdate < nvl(B.end_date,sysdate + 1)
                         and variable_name = B.variable_name);

  cursor checkActionTypesVR(appId in number) is                        
 select name
 from apps.AME_ACTION_TYPE_CONFIG atc
     ,apps.AME_ACTION_TYPES atyp
 where application_id = appId
   and voting_regime <> ‘S’
   and sysdate between atc.start_date and nvl(atc.end_date,sysdate + 1)
   and sysdate between atyp.start_date and nvl(atyp.end_date,sysdate + 1) 
   and atc.action_type_id = atyp.action_type_id;   
  
  cursor checkActionTypesON(appId in number) is
    select name
 from apps.AME_ACTION_TYPE_CONFIG atc
     ,apps.AME_ACTION_TYPES atyp
     ,apps.AME_ACTION_TYPE_USAGES atu
 where application_id = appId
   and sysdate between atu.start_date and nvl(atu.end_date,sysdate + 1)
   and sysdate between atc.start_date and nvl(atc.end_date,sysdate + 1)
   and sysdate between atyp.start_date and nvl(atyp.end_date,sysdate + 1) 
   and atc.action_type_id = atyp.action_type_id
   and atu.action_type_id = atyp.action_type_id
   and (atu.rule_type, atc.order_number) in (select rule_type,order_number
                                               from apps.ame_action_type_config B,apps.ame_action_type_usages C
                                              where B.action_type_id = C.action_type_id
                                                and application_id = appId
                                                and sysdate between C.start_date and nvl(C.end_date,sysdate + 1)                
                                                and sysdate between B.start_date and nvl(B.end_date,sysdate + 1)
                                              group by rule_type,order_number
                                             having count(*) > 1);  
                                            
  cursor checkApprovalGroupsVR(appId in number) is                                             
 select name
   from apps.AME_APPROVAL_GROUPS apg
        ,apps.AME_APPROVAL_GROUP_CONFIG apgc
  where apg.approval_group_id = apgc.approval_group_id
    and apgc.application_id = appId
    and apgc.voting_regime not in (‘S’,’O’)
    and sysdate between apgc.start_date and nvl(apgc.end_date,sysdate + 1)
    and sysdate between apg.start_date and nvl(apg.end_date,sysdate + 1);   
   
  cursor checkApprovalGroupsON(appId in number) is     
 select name
   from apps.AME_APPROVAL_GROUPS apg
        ,apps.AME_APPROVAL_GROUP_CONFIG apgc
  where apg.approval_group_id = apgc.approval_group_id
    and apgc.application_id = appId
    and sysdate between apgc.start_date and nvl(apgc.end_date,sysdate + 1)
    and sysdate between apg.start_date and nvl(apg.end_date,sysdate + 1) 
    and order_number in (select order_number
                            from apps.AME_APPROVAL_GROUP_CONFIG apgc
                           where sysdate between apgc.start_date and nvl(apgc.end_date,sysdate + 1)
                             and apgc.application_id = appId
                         group by order_number
                           having count(*) > 1);                                              

  cursor checkApprovalGroupItems(appId in number) is
   select name
   from apps.AME_APPROVAL_GROUPS apg
  where sysdate between apg.start_date and nvl(apg.end_date,sysdate + 1) 
    and apg.approval_group_id in (  select apgi.approval_group_id
                             from apps.AME_APPROVAL_GROUP_ITEMS apgi
                                  ,apps.AME_APPROVAL_GROUP_CONFIG apgc
                            where sysdate between apgi.start_date and nvl(apgi.end_date,sysdate + 1)
                              and sysdate between apgc.start_date and nvl(apgc.end_date,sysdate + 1)
                              and apgc.application_id = appId
                              and apgi.approval_group_id = apgc.approval_group_id                            
                         group by apgi.approval_group_id,apgi.order_number
                           having count(*) > 1 );                        

  cursor checkItemClassSM(appId in number) is
    select itc.name
      from ame_item_classes itc
      where sysdate between itc.start_date and nvl(itc.end_date,sysdate+1)
        and itc.item_class_id in (select itu.item_class_id
                                    from ame_item_class_usages itu
                                   where itu.application_id = appId
                                     and sysdate between itu.start_date and nvl(itu.end_date,sysdate+1)
                                     and item_class_sublist_mode <> ‘S’
                                );

    isTitlePrinted boolean;
   
    currentAppId number;
    applicationRow getApplications%ROWTYPE;
    configVarsRow checkConfigVars%ROWTYPE;      
    actionTypesVRRow checkActionTypesVR%ROWTYPE;
    actionTypesONRow checkActionTypesON%ROWTYPE;
    checkApprovalGroupsVRRow checkApprovalGroupsVR%ROWTYPE;
    checkApprovalGroupsONRow checkApprovalGroupsON%ROWTYPE;
    checkApprovalGroupItemsRow checkApprovalGroupItems%ROWTYPE;   
  begin
    dbms_output.put_line(‘Setup causing Parallelization Errors … ‘);
    for applicationRow in getApplications loop
      isTitlePrinted := false;
      currentAppId := applicationRow.application_id;
      for configVarsRow in checkConfigVars(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Incorrect value for ‘ || configVarsRow.configType || ‘ Configuration Variable :: ‘ || configVarsRow.variable_name);
      end loop;
      dbms_output.put_line(‘ ‘);
      for actionTypesVRRow in checkActionTypesVR(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non serial Voting Regime for Action Type :: ‘ || actionTypesVRRow.name);
      end loop;
      dbms_output.put_line(‘ ‘);
      for actionTypesONRow in checkActionTypesON(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non unique Order Numbers for Action Type :: ‘ || actionTypesONRow.name);
      end loop;
      dbms_output.put_line(‘ ‘);     
      for checkApprovalGroupsVRRow in checkApprovalGroupsVR(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non serial or Order Number Voting Regime for Approval Group :: ‘ || checkApprovalGroupsVRRow.name);
      end loop;
      dbms_output.put_line(‘ ‘);         
      for checkApprovalGroupsONRow in checkApprovalGroupsON(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non unique Order Numbers for Approval Group Config :: ‘ || checkApprovalGroupsONRow.name);
      end loop;
      dbms_output.put_line(‘ ‘);       
      for checkApprovalGroupItemsRow in checkApprovalGroupItems(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
 end if;
        dbms_output.put_line(‘Non unique Order Numbers for Items of Approval Group :: ‘ || checkApprovalGroupItemsRow.name);
      end loop;
      dbms_output.put_line(‘ ‘); 
      for checkItemClassSMRow in checkItemClassSM(currentAppId) loop 
        if not isTitlePrinted then
       dbms_output.put_line(‘ ‘);
       dbms_output.put_line(‘————————————————————— ‘);
       dbms_output.put_line(‘Transaction Type :: ‘ || applicationRow.application_name);
       dbms_output.put_line(‘————————————————————— ‘); 
       dbms_output.put_line(‘ ‘); 
       isTitlePrinted := true;
       end if;
        dbms_output.put_line(‘Non Serial Sublist Modes for Item Class Usages :: ‘ || checkItemClassSMRow.name);
      end loop;
    end loop;
  end;
/
rollback;

AME basics

What is AME?
AME by definition is a Approval Management Engine that Builds a list of approvers for a specific transaction using business rules. However in simple words, AME is a “IF THEN….ELSE….END IF” condition – wrapped into an API.
That’s exactly what AME is. It is a IF THEN ELSE CONDITION wrapped up into a lets say Function Call to produce a list of approvers. The complexities are taken care of by the Approval Management Engine itself. However this “IF CONDITION” definition holds true when talking about the simplest possible definition of AME. Therefore if you can understand “IF Condition”, then you can understand AME. Further to that, if you know how to write IF Conditions and how to write SQL, then you will be able to implement most of the AME Business requirements with ease. Lets have a look into IF CONDITION from another perspective.

The example below is for explanation purpose only, not the exact definition of AME.
Imagine there is a FUNCTION that returns the approver
FUNCTION get_requisition_approver_id (transaction_id IN INTEGER )  RETURN INTEGER IS
BEGIN
IF get_requisition_amount(transaction_id) < 1000000 THEN
return 1000 ;– calculated from person_id from the supervisor_id of the person that created requisition
ELSE
return 1001,1003 ; –person_id from a static approver group that contains list of people, like Director followed by a CEO to approve orders over 1Million
END IF ;
END get_requisition_approver_id ;

The above function builds a list of approvers for a specific transaction using business rules, and returns one of more approvers.

Understanding the basic building blocks of AME
To understand the building blocks of AME, in the above example, consider the complete FUNCTION definition get_requisition_approver_id as “AME Transaction Type”. Let us discuss the basic building blocks of AME using the Function get_requisition_approver_id as listed above.

To this “AME Transaction Type” a parameter named Transaction Id is passed that helps identification of the underlying business transaction, for example this could be requisition_header_id from po_requisition_headers_all.
Consider the IF Statement as “AME CONDITION”

The IF statement will rely upon some variables. Consider that these variables are “AME ATTRIBUTES” for the transaction types. The value for these attributes can be calculated by executing some SQL Statements. Alternately the AME Attribute can be defined with a static value too. In AME Terminology, for a  variable to be used in IF condition you must ensure that its corresponding AME Attribute is registered against the AME Transaction Type. [Note – Some AME Attributes used by AME Engine are globally available to all AME Transaction Types]. The value of the AME ATTRIBUTE can either be static value or dynamic when derived by firing a SQL Statement.

The return statement in above function is nothing but an “AME Action”. As per the example above, the “AME Action Type” will return the Supervisor of the person if Requisition amount is less than USD 1 Million. However, the “AME Action Type” will return the person_id of Directory followed by the CEO of the company when requisition amount is greater than USD 1 Million.

The Combination of the IF CONDITION and the RETURN Statement is called “AME Rule”. In other words each AME rule is a condition with a result returned.
However it must be noted that you can define an “AME Rule” without any condition. At the time of creating the “AME Rule” you will get a option to wrap a condition around it. You may decide to skip the step of attaching a condition to the rule. Fictitiously if all the Requisitions must be approved by the CEO, then you simply define a AME Rule without any “IF Condition”.

The entire return statement i.e. “return 1000” may be deemed as AME Action Type, however the value 1000 or 1001 or 1003 is derived from something known as “AME Approval Group”. As you might have guessed, the “AME Approval Group” can either be a static list of people or a dynamically calculated list of people. The dynamic list is of course generated using the results of a SQL Statement.

Therefore “AME Transaction Type” is the encapsulation of the approval business rules relating to a business transaction like Requisition.
You can have as many IF Conditions as you desire to build the logic for approval. Therefore “One AME Transaction Type” can consist of many “AME Rules”. In other words “One AME Transaction Type” can consist of various “IF THEN ELSE Statements” and their corresponding actions.

Therefore, by reading the above function based example, it is clear that AME consists of certain building blocks that are

Building Block Name
Purpose
AME Transaction Type Think of this to be the entire Approval Business Process for a specific transaction type, for example PO Approval or iRecruitment Vacancy Approval. Of course a business process will consist of various rules.
AME Attributes Think of these as global variables whose values can either be static or derived from SQL Statements. The SQL Statements can reference the transaction id, which for example could be “requisition header id” or “vacancy_id”. These Attributes can be used for building the AME Conditions.
AME Conditions These are IF Conditions that use variables, i.e. AME Attributes
AME Action Type Action type defines how the action is taken to calculate the result. For example- the action be to use a specific Approval Group based on static list of employees.
Alternately the action can be to use a dynamically built list of Approvers.
AME Approval Groups Approval groups are used to build the list of approvers, either from Static list or from SQL Statements. Calls to Approval Groups are made from “AME Action Type”.
AME Rules Combines the “AME Condition” and “AME Approval Groups”. Indirectly though it references all the building blocks of AME. You can say that every piece of AME building block is glued together in an “AME Rule”.

Profile option “AME:Installed” must be set to Yes, at application level to the calling application to be able to use AME.. For example, to make iExpenses use AME, you should set profile to Yes for “Payables” Application.

The “AME Transaction Type” can be defined using a responsibility named “Approvals Management Administrator”.
Remainder of the building blocks can be defined and configured using responsibility named “Approvals Management Business Analyst”. Oracle eBusiness suite comes seeded with