Miscellaneous SQL/ PLSQLs

Finding Patch Set Level

SELECT * FROM (
SELECT '(Patch: ' || bug_number || ') applied ' || LAST_UPDATE_DATE PFbugDate
FROM ad_bugs
WHERE BUG_NUMBER IN (
'2115771',
'2268451',
'2502761',
'2632500',
'2803988',
'2968701', -- F
'3116666', -- G
'3233333', -- H
'3127777', -- I = 11.5.10 base
'3333633', -- J
'3500000', -- K
'3800000', -- L
'5055050') -- Rup1
ORDER BY LAST_UPDATE_DATE desc
) WHERE ROWNUM = 1;






Find the key flex field structure
SELECT id_flex_structure_code structure_code,
DECODE (dynamic_inserts_allowed_flag,
'Y', 'OK: Dynamic',
'ERROR: No Dynamic'
) dynamic,
DECODE (freeze_flex_definition_flag,
'Y', 'OK: Freezed',
'ERROR: No Freezed'
) freeze,
segment_num seg_number, segment_name seg_name,
DECODE (required_flag,
'Y', 'OK: Required',
'ERROR: No required'
) required,
DECODE (fvs.flex_value_set_name,
NULL, 'NULL',
fvs.flex_value_set_name
) value_set,
DECODE (fvs.validation_type,
'N', 'OK: No validation',
'ERROR: ' || fvs.validation_type
) VALIDATION,
DECODE (fvs.uppercase_only_flag,
'N', 'OK: No',
'ERROR: Uppercase Only'
) uppercase_only,
DECODE (alphanumeric_allowed_flag,
'Y', 'OK: Allowed',
'ERROR: Not Allowed'
) alphanumeric,
DECODE (numeric_mode_enabled_flag,
'N', 'OK:Not justified',
'ERROR: Justified'
) right_justify,
DECODE (format_type,
'C', 'OK: Char',
'ERROR: ' || format_type
) format_type
FROM fnd_id_flex_structures_vl ffst,
fnd_id_flex_segments_vl ffsg,
fnd_flex_value_sets fvs
WHERE ffst.application_id = 800
AND ffst.id_flex_code = 'GRD'
AND ffst.enabled_flag = 'Y'
AND ffst.application_id = ffsg.application_id
AND ffst.id_flex_code = ffsg.id_flex_code
AND ffst.id_flex_num = ffsg.id_flex_num
AND ffsg.enabled_flag = 'Y'
AND fvs.flex_value_set_id(+) = ffsg.flex_value_set_id;

Find the Descriptive flex field structure
SELECT apps.application_name "App",
d_flex.title "Title",
d_flex.application_table_name "Table",
d_flex.context_user_override_flag "DFF Disp",
d_flex.freeze_flex_definition_flag "Frozen",
d_flex.default_context_value "Default Ctx",
d_flex.context_required_flag "DFF Required",
ctx_val_s.flex_value_set_name "Ctx ValueSet",
ctx.descriptive_flex_context_name "Ctx",
ctx.enabled_flag "Ctx Enabled",
col.form_left_prompt "Prompt",
col.end_user_column_name "Column",
val_s.flex_value_set_name "Value Set",
col.enabled_flag " Col Enabled",
col.required_flag "Required",
col.application_column_name "App Column",
col.display_flag " Col Display",
col.default_value "Default Val",
col.security_enabled_flag "Security",
col.display_size,
col.maximum_description_len,
col.concatenation_description_len,
col.application_id,
d_flex.concatenated_segs_view_name,
col.descriptive_flexfield_name,
col.form_above_prompt,
col.descriptive_flex_context_code,
col.range_code,
col.flex_value_set_id,
col.default_type,
col.srw_param
FROM fnd_descr_flex_col_usage_vl col,
fnd_descr_flex_contexts_vl ctx,
fnd_descriptive_flexs_vl d_flex,
fnd_flex_value_sets val_s,
fnd_flex_value_sets ctx_val_s,
fnd_application_tl apps
WHERE d_flex.application_id IN (809,
808)
--AND Upper(d_flex.title) LIKE Upper('%Addtional Ben Pl F Details%')
AND d_flex.descriptive_flexfield_name = ctx.descriptive_flexfield_name
AND ctx.descriptive_flexfield_name = col.descriptive_flexfield_name (+)
AND col.descriptive_flex_context_code (+) = ctx.descriptive_flex_context_code
AND val_s.flex_value_set_id (+) = col.flex_value_set_id
AND ctx_val_s.flex_value_set_id (+) = d_flex.context_override_value_set_id
AND apps.application_id = d_flex.application_id
AND d_flex.application_table_name <> 'FND_SRS_MASTER'
ORDER BY apps.application_name,
d_flex.title,
ctx.descriptive_flex_context_name,
col.column_seq_num;



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



Retrieve Supervisory Hierarchy (CONNECT BY PRIOR)

SELECT lpad(' ',level*3)
||
(SELECT full_name
FROM per_all_people_f ppl
WHERE person_id = paf.person_id
AND rownum =1
) full_name,
(SELECT full_name
FROM per_all_people_f ppl
WHERE person_id = paf.supervisor_id
AND rownum =1
) sup ,
assignment_number,
position_id
FROM per_all_assignments_f paf
WHERE business_group_id = 81
AND assignment_type = 'E'
AND assignment_status_type_id = 1
AND sysdate BETWEEN effective_start_date AND effective_end_date START
WITH assignment_number = '2748' CONNECT BY prior person_id = supervisor_id;



Initialize Apps (FND_GLOBAL.APPS_INTIALIZE)


declare
l_user_id number;
l_resp_id number;
l_resp_app_id number;

cursor get_user is
select user_id
from fnd_user
where user_name = ‘GSEHGAL’;

cursor get_resp is
select responsibility_id, application_id
from fnd_responsibility_vl
where responsibility_key = 'HIRING_MANAGER';


begin
open get_user;
fetch get_user into l_user_id;
close get_user;

open get_resp;
fetch get_resp into l_resp_id, l_resp_app_id;
close get_resp;

fnd_global.apps_initialize(l_user_id, l_resp_id,l_resp_app_id);
end;

Miscellaneous

Using Benefits Personal Actions
If you want to add an eligibility criteria for any function in the menu for different employees then personal actions feature of benefits can be used.

This is possible using the SSHR Actions eligibility model.

You need to create an eligibility profile for your Change Grade action using the Employment > Grade criteria for grades D, E and F, i.e. not including grades A, B, and C.

This eligibility profile then needs to be attached to a performance plan linked to your SSHR function for Change Grade; this is done in the 'Plan Eligibility' form task flowed from the 'Plan' form.

Note: The Miscellaneous tab in the Plan form is where you enter the SSHR function name for your Change Grade action. You need to ensure Plan Type has an 'Option Type' of 'Personnel Actions'.

Create a Reporting Group with 'Purpose' of 'Personnel Action' and a 'Function' equal to your Change Grade function name. (Note: If you are using one launch, points for all your Manager Actions then instead enter that function name here for the launching function from the menu.) In the components section enter the plan name you previously created.

Last step you need to set the system profiles:

HR:Allow use of eligibility for Self Service actions - Yes
HR:Allow processing of ineligible Self Service actions - No
HR:Run BENMNGLE when processing a Self Service action – Yes



Notes for Forms Personalization
Note: 279034.1 - Information About the Oracle Applications Form Personalization Feature in 11i
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=279034.1

Note: 468657.1 - How To Do Forms Personalization
https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=468657.1

Forms Personalization - Get It While It's Hot!
http://blogs.oracle.com/schan/2006/07/18

Personalization Metalink Notes
429011.1
429136.1
550349.1








Add a security Profile to a responsibility
a) Create Security Profile.
b) Attach it to the responsibility using HR: Security Profile system profile option.
c) Set Profile option HR: User Type to HR with payroll user
d) Run Security List Maintenance Concurrent Program. If concurrent manager is down run following procedure
DECLARE
L_ERRBUF VARCHAR2(100);
L_RETCODE NUMBER;
BEGIN
PAY_PYUCSLIS_PKG.SUBMIT_SECURITY
(ERRBUF => L_ERRBUF,RETCODE => L_RETCODE,
P_EFFECTIVE_DATE => FND_DATE.DATE_TO_CANONICAL(SYSDATE),
P_GENERATION_SCOPE => 'SINGLE_PROF', -- FOR SINGLE PROFILE
P_BUSINESS_GROUP_ID => NULL,
P_SECURITY_PROFILE_ID => 6064, -- PROFILE ID OF THE SECURITY PROFILE YOU CREATED
P_WHO_TO_PROCESS => 'ALL', -- FOR CURRENT AND TERMINATED EMPLOYEES
P_ACTION_PARAMETER_GROUP_ID => NULL);
END;

e) Following tables will be populated
PER_PERSON_LIST
PER_ORGANIZATION_LIST

For complete reference on Security Profile, refer to the metalink note: 394083.1

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.

Steps to Setup Basic Bonus Plan

Steps to Setup Basic ICD Plan for Bonus

a) Create a plan type of type Individual Compensation Distribution (Total Compensation => Programs and Plans => Plan Types)
b) Create a Plan using the above plan type and following information (Total Compensation => Programs and Plans => Plans)

Usage: May not be in program
Go to Not in Program Tab and enter details Enrollment Rate/ Frequency as per pay period and activity Reference Period as Annually

Go to the Details button and attach plan year period (plan will be available only in those time periods)

c) Define Plan Enrollment Requirements (Total Compensation => Programs and Plans => Plan Enrollment Requirements)

Enrollment Tab
Enrollment Type: Explicit
Allow Unrestricted => Checked
Enroll in Plan and Option => Checked
Enrollment Code => Current, Can Keep or Choose; New, Can Choose

Coverage Tab
Coverage Start Date=> Event
Coverage End Date => Event

Rates Tab
Rate Start Date=> Event
Rate End Date => Event

d) Define a Standard Rate for the Plan (Total Compensation => Rate/ Coverage Definitions => Standard Rates)

General Tab
Level: Plan
Compensation Object:
Activity Type: See from the list you require (for Bonus: Employer Payroll Contribution)
Tax Type: Any
Element Name and Element Input Value (Check Element and input value required if you attach the element here)

Calculation Tab
Enter Value at enrollment: Checked
Calculate for Enrollment: Checked
Give min, max values

Processing Information
Assign on Enrollment: Checked
Display on Enrollment: Checked
Compensation Category: Bonus
Value passed to Payroll: Defined Amount


That is all for setup.

Go to self-service and go to Individual Compensation Distribution and then go as application says . .. you can see this bonus. If you have attached element entry in standard rate, you can see the same in the bonus history also.

What happens internally
it process the unrestricted life event (Advanced Benefits) on the person and create enrollment and rates (and elements if defined).

njoy
Gaurav Sehgal

IE Crash while Opening Forms

Hi All
Issue: While opening forms from IE, IE crashes.
I have faced this issue many times. First after upgrading to IE7 .. then upgrading to Vista .. recently installed a proprietry software using JRE ..

Also Oracle Applications Forms client is not supported on vista unless you apply some recommended paches. You can refer metalink note: 285218.1 for all the details on all patchset levels.

So whats the easiest way
Copy the jvm.dll file from you JRE - bin - client to Jinitiator jvm.dll
i.e.
C:\Program Files\Java\ \bin\client\jvm.dll file to C:\Program Files\Oracle\\bin\hotspot\jvm.dll

It solved the problem for me in all above cases on lot of machines.

Another easy way is use firefox when you need to open the forms. Some times when you are using IE for long your forms will not get opened from Firefox. It will install jinitiator again and again when you open the forms.
Go to Control panell and remove all the versions of Jinitiator you have there. Now open firefox. Open the form you want to open. It will install Jinitiator and you can open the forms.

But you cant go to every user and ask them to install and use firefox for using Oracle Apps .. so i prefer the first workaround.

Hope this will help you as well.


njoy
Gaurav Sehgal