Dynamic Approver List for AME

Oracle has provided an API to get the dynamic list of approvers for AME. ame_dynamic_approval_pkg.get_ame_apprs_and_ins_list.
We need to pass the transaction type id and transaction number. Rransaction Id is available in HR_API_TRANSACTION_TABLE and transaction_type_id is available in AME_CALLING_APPS table.

API Usage Code

CREATE OR REPLACE PROCEDURE GET_AME_TRANSACTION_APPROVERS

(P_AME_TRANSACTION_ID IN NUMBER,
 P_TRANSACTION_TYPE_ID IN VARCHAR2 DEFAULT ‘LSGEXLR’,
P_AME_APPROVERS_LIST OUT AME_APPROVER_RECORD2_TABLE_SS )

IS
 p_apprs_view_type varchar2(100):=’Active’;
p_coa_insertions_flag varchar2(100):= ‘N’;
v_ame_approvers_list ame_approver_record2_table_ss;
 v_ame_order_type_list ame_insertion_record2_table_ss:= ame_insertion_record2_table_ss();
 v_all_approvers_count varchar2(100);
 v_warning_msg_name varchar2(100);
 v_error_msg_text varchar2(100);

BEGIN

–Reference to get the Transaction Type ID, Note that LSG is my custom Application Name
–select * from ame_calling_apps
–where fnd_application_id = 800
–and application_name = ‘LSG External Learning Request’

v_ame_approvers_list := ame_approver_record2_table_ss();

AME_DYNAMIC_APPROVAL_PKG.GET_AME_APPRS_AND_INS_LIST

(

p_application_id => 800
 ,p_transaction_type =>P_TRANSACTION_TYPE_ID
 ,p_transaction_id => P_AME_TRANSACTION_ID
 ,p_ame_approvers_list => v_ame_approvers_list
 ,p_ame_order_type_list => v_ame_order_type_list
 ,p_all_approvers_count => v_all_approvers_count
 ,p_warning_msg_name => v_warning_msg_name
 ,p_error_msg_text => v_error_msg_text

);

P_AME_APPROVERS_LIST := v_ame_approvers_list;

EXCEPTION WHEN OTHERS THEN

dbms_output.put_line(‘exception ‘sqlerrm);

END;

Sample procedure given below:

DECLARE

v_AME_TRANSACTION_ID NUMBER:=21610630 ;
 v_TRANSACTION_TYPE_ID VARCHAR2(20):=’LSGEXLR’;
v_AME_APPROVERS_LIST AME_APPROVER_RECORD2_TABLE_SS ;

BEGIN

GET_AME_TRANSACTION_APPROVERS

(

P_AME_TRANSACTION_ID =>v_AME_TRANSACTION_ID

,P_AME_APPROVERS_LIST =>V_AME_APPROVERS_LIST

);

IF(v_ame_approvers_list.count() ) > 0THEN

FOR i IN 1..v_ame_approvers_list.count() LOOP

dbms_output.put_line(‘ approver_order_number =>‘ v_ame_approvers_list(i).approver_order_number
‘ **** display_name =>‘v_ame_approvers_list(i).display_name
‘ **** Approver Category =>‘v_ame_approvers_list(i).approver_category
‘ **** approval_status =>‘v_ame_approvers_list(i).approval_status
‘ **** source =>‘v_ame_approvers_list(i).source );

END LOOP;

ELSE

dbms_output.put_line(‘ No Approver Found’);

END IF;

END;