EBS – SSHR – AME Transaction Approver List

By | October 2, 2012

EBS – SSHR – AME Transaction Approver List

Requirement

We need to get the dynamic approvers list for a transaction enetered in SSHR. We want to show this information on a custom OAF page when user submits the button, he should be able to see the approvers list in the queue.

Solution

Oracle has provided an API to get this information ame_dynamic_approval_pkg.get_ame_apprs_and_ins_list. You just need to pass the transaction type id and transaction number. You can find the transaction Id from HR_API_TRANSACTION_TABLE whereas for transaction_type_id you need to use 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;

 How to Use

How to use the Procedure created above, I am also giving the runner procedure coz many people are not familiar how to use the collections.

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;

You can read more on self service sql queries from my article Self Service SQL Queries

Related Posts:

Question of the Day
HRMS Interview Questions
9 Steps to become Oracle Apps Consultant
Hierarchy of OLM Tables
How to create backend logging mechanism
Step to Register Discoverer Report
Steps for creating custom form in Oracle Apps
Clear Global Cache in Oracle Apps
Profile Values in Oracle Forms Personalization
2 simple Steps to make a responsibility read only

  • nice post

  • Thanks for the help and small doubt how we are getting the data into OTA_NOTRNG_HISTORIES table.

    Thanks in advance..