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