Setting up AME (Approval Management) in R12

I am working with a customer for upgrading there HCM to R12 from 11.5.10 . R12 include lot of technology changes and one of them includes AME. Newer version of AME, AME.B is also available in latest 11i releases.
AME.A was built in mod PL/SQL and AME.B is developed in OA Framework. Another major change in AME is that it uses RBAC (Role Based Access Control) mechanism to assign responsibilities to a user.

So if you open AME Approvals function with old responsibilities you might get one of the following errors.

The requested URL /pls/VIS/OracleSSWA.Execute was not found on this server.
OR
Can not access function AME_WEB_APPROVALS. This installation does not manage mode pl/sql. Please contact your System Administrator.

Both the errors saying that mod PL/SQL is not available (OracleSSWA is a program that executes the PL/SQL Package to generate the html output for pages).

Following are the steps to implement AME in R12 instance.
1. Login with SYSADMIN User.
2. Go to User Management Responsibility.
3. Click on Users and query for the username to which you want to assign AME Responsibilities.
4. One you query the User, Update the User from search results region and then click on Assign Roles.
5. Assign following Roles to the User
a) Approval Management Administrator
b) Approval Management System Administrator
c) Approval Management Process Owner
d) Approval Management System Viewer
e) Approval Management Business Analyst

Once finished with the Roles we will grant a permission set to the user from Functional Administrator Responsibility. Following are the steps for that

6. Go to Functional Administrator Responsibility => Security => Grants => Create Grant.
7. Enter Name of the "Grant" and effective start date. In Security Context select Grantee Type as Specific User and enter the User name whom you want to assign AME Responsibilities (Same user to which we assigned AME Roles).
8. Select Object as AME Transaction Types and Press Next.
9. Select Data Context as All Rows and assign "AME Calling Applications" permission set and Save the Information.

We need to set the AME: Installed Profile option as well to make it work. Set this profile option to Yes at application level (Human Resources).

Try to login with the user and you can see the following responsibilities listed there
Approvals Management Administrator
Approvals Management Business Analyst

What should existing AME Users do to make it work ?
It is pretty easy. Oracle provides a concurrent program "Approvals Management Post Upgrade Process". Run this program and thats all.

Drop a comment if you have any question regarding the above article. Hope this will help.

njoy
Gaurav

Useful SQLs/ PLSQLs

I am listing here some of the SQL Queries and PLSQL blocks which we use very often in Oracle Applications. It is cumbersome to see this data in application through delivered screens.

1. Finding the value for a profile


SELECT fpot.user_profile_option_name profile,
fpov.profile_option_value VALUE,
decode(fpov.level_id,
10001,
'SITE',
10002,
'APPLICATION',
10003,
'RESPONSIBILITY',
10004,
'USER') "Apply On",
fa.application_name application,
fr.responsibility_name responsibility,
fu.user_name "USER"
FROM fnd_profile_option_values fpov,
fnd_profile_options_tl fpot,
fnd_profile_options fpo,
fnd_application_tl fa,
fnd_responsibility_vl fr,
fnd_user fu,
fnd_logins fl
WHERE fpo.profile_option_id = fpov.profile_option_id
AND TRIM(fpot.profile_option_name) = TRIM(fpo.profile_option_name)
AND fa.application_id(+) = fpov.level_value
AND fr.application_id(+) = fpov.level_value_application_id
AND fr.responsibility_id(+) = fpov.level_value
AND fu.user_id(+) = fpov.level_value
AND fl.login_id(+) = fpov.last_update_login
-- AND fpo.profile_option_name = 'FND_CUSTOM_OA_DEFINTION'
-- AND fpov.profile_option_value = 1067
-- AND fr.responsibility_name like '%Manager%'
AND fpot.user_profile_option_name = 'MO: Operating Unit'
ORDER BY 1;


2. Element Entries


SELECT ee.element_type_id '-' elt.element_name el_name,
to_char(ee.assignment_id) asId,
to_char(ee.element_entry_id) eeId,
ee.effective_start_date esd,
ee.effective_end_date eed,
to_char(ee.creator_id) Creator,
to_char(ee.element_link_id) el_id,
to_char(ee.object_version_number) ovn
FROM apps.pay_element_entries_f ee, pay_element_types_f elt
WHERE ee.assignment_id IN (SELECT assignment_id
FROM per_all_assignments_f
WHERE person_id = &v_personId AND primary_flag = 'Y')
AND elt.element_type_id(+) = ee.element_type_id
AND ee.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date;


3. Element Values


SELECT to_char(elt.element_type_id) '-'elt.element_name el_name,
to_char(eev.input_value_id) '-' piv.NAME iv_name,
to_char(eev.element_entry_id) eeId,
to_char(eev.element_entry_value_id) eevId,
eev.screen_entry_value,
eev.effective_start_date esd,
eev.effective_end_date eed
FROM pay_element_entry_values_f eev,
pay_input_values_f piv,
pay_element_types_f elt
WHERE eev.element_entry_id IN (
SELECT element_entry_id
FROM apps.pay_element_entries_f ee
WHERE assignment_id IN
(SELECT assignment_id
FROM per_all_assignments_f
WHERE person_id = &v_personId AND primary_flag = 'Y'))
AND elt.element_type_id(+) = piv.element_type_id
AND eev.effective_start_date BETWEEN elt.effective_start_date AND elt.effective_end_date
AND eev.input_value_id = piv.input_value_id
AND eev.effective_start_date BETWEEN piv.effective_start_date AND piv.effective_end_date
ORDER BY 1, 2;


4. Finding User name against a responsibility


SELECT usr.user_name,
resp.responsibility_name
FROM fnd_user usr,
fnd_responsibility_vl resp,
fnd_user_resp_groups_direct usr_resp
WHERE usr.user_id = usr_resp.user_id
AND usr_resp.responsibility_id = resp.responsibility_id
AND Upper(resp.responsibility_name) LIKE Upper('%HRMS%Workbench%');


5. Finding menu for a given function


SELECT fn.PROMPT ,
fnf.user_function_name,
men.user_menu_name ,
fn.DESCRIPTION ,
fn.GRANT_FLAG
FROM FND_MENU_ENTRIES_VL fn ,
fnd_form_functions_vl fnf,
fnd_menus_vl men
WHERE fn.function_id = fnf.function_id
AND fn.menu_id = men.menu_id
AND fn.prompt = 'Change Job'
ORDER BY ENTRY_SEQUENCE;


6. Compile Menus through PLSQL (rather than concurrent program)


declare
l_r number;
l_e varchar2(1000);
begin
fnd_function.compile(l_e,l_r,'N');
end;


Lot of more stuff following this post. Any doubts regarding any query drop a comment. I will try to see if I have the right answer.

How to run FNDLOAD Command

I have seen some questions many times like how can I run FNDLOAD Command? What are all the entities I can upload/ download with FNDLOAD? What is the syntax for FNDLOAD.

Infact we can find out answers to all our questions by knowing how FNDLOAD works?

FNDLOAD use an lct file (kind of a conguration file) which contains which entity it is used for, what all attributes of the entity it will populate/ download. LCT files also have the SQL Statement using which it will download all the data and API using which it will upload the data into ORACLE APPLICATIONS. So if i want to find out that for which table and entity I can use FNDLOAD, I can search in the ".lct" files in the product code tree and search for table or my entity.

lct files are located in $PROD_TOP/patch/115/import/

Usage: FNDLOAD logon 0 Y mode configfile datafile [ entity [ param ... ] ]


where logon is username/password[@connect] mode is either UPLOAD or DOWNLOAD configfile is the configuration file (lct file) datafile is the data file (for downloading specify any name. A file will be created with that name. For uploading specify you filename) entity is an entity name, or - to specify all values in an upload (Entity name) param is a NAME=VALUE string used for parameter substitution


Heres you can see lct files and choose what all parameters you should pass to run FNDLOAD. I am taking a simple example of a concurrent program.

DEFINE PROGRAM
KEY CONCURRENT_PROGRAM_NAME VARCHAR2(30)
KEY APPLICATION_SHORT_NAME VARCHAR2(50)
CTX OWNER VARCHAR2(4000)
BASE LAST_UPDATE_DATE VARCHAR2(75)
TRANS USER_CONCURRENT_PROGRAM_NAME VARCHAR2(240)
BASE EXEC REFERENCES EXECUTABLE
BASE EXECUTION_METHOD_CODE VARCHAR2(1)
END PROGRAM

PROGRAM is an entity.

KEY Attributes in the entity should be passed for downloading the entity.

CTX Attributes are context parameters which are generally OWNER and LAST_UPDATE_DATE to determine whether to overwrite data or not.
These are generally used for uploadig the data.If owner is other than seed then data will be overriddenif Last_udpate_date is greater than the last_update_date in the database then data will be overridden.

TRANS: THis reperesent that this is transalatable attribute.

So for downloading program "BENLIMOD" I can use following ommand

$FND_TOP\bin\FNDLOAD apps/@ 0 Y DOWNLOAD $FND_TOP\\patch\115\import\afcpprog.lct benmngle.ldt PROGRAM APPLICATION_SHORT_NAME=BEN CONCURRENT_PROGRAM_NAME=BENLIMOD

Using lct file FNDLOAD create a LDT file which contains data. You can find these files in "$PROD_TOP/patch/115/import/US/". You can find LDT files at the above locations. These files contain the delivered data by oracle like concurrent program definitions, request groups etc.
Now I think we can ourself define our own FNDLOAD command for any of the entities for which FNDLOAD is available.

Hope this helps.