Create Oracle FND_USER with System Administrator

If you have the Apps Password, its quite easy to create a FND_USER for yourself by using the API.
I find this script very useful when development environment gets cloned from Production(that is when i do not have FND_USER in Production.
Please note that:-
1. You will be allocated System Administrator by this script. Hence you can assign whatever responsibilities that you desire latter, after logging in.
2. The password will be set to oracle
3. You need apps password to run this script. Alternately you need execute permission on fnd_user_pkg from the user where this script will be run. If using some other user, please use apps.fnd_user_pkg.createuser
4. You need to do a COMMIT after this script has run. I have not included the commit within this script.
5. When running this script, you will be prompted to enter a user name.

--------Beging of script--------------
DECLARE
--By: Anil Passi
--When Jun-2001
v_session_id INTEGER := userenv('sessionid');
v_user_name VARCHAR2(30) := upper('&Enter_User_Name');
BEGIN
--Note, can be executed only when you have apps password.
-- Call the procedure to Creaet FND User
fnd_user_pkg.createuser(x_user_name => v_user_name
,x_owner => ''
,x_unencrypted_password => 'oracle'
,x_session_number => v_session_id
,x_start_date => SYSDATE - 10
,x_end_date => SYSDATE + 100
,x_last_logon_date => SYSDATE - 10
,x_description => 'appstechnical.blogspot.com'
,x_password_date => SYSDATE - 10
,x_password_accesses_left => 10000
,x_password_lifespan_accesses => 10000
,x_password_lifespan_days => 10000
,x_employee_id => 30 /*Change this id by running below SQL*/
/*
SELECT person_id
,full_name
FROM per_all_people_f
WHERE upper(full_name) LIKE '%' || upper('full_name') || '%'
GROUP BY person_id
,full_name
*/
,x_email_address => 'appstechnical.blogspot@gmail.com'
,x_fax => ''
,x_customer_id => ''
,x_supplier_id => '');
fnd_user_pkg.addresp(username => v_user_name
,resp_app => 'SYSADMIN'
,resp_key => 'SYSTEM_ADMINISTRATOR'
,security_group => 'STANDARD'
,description => 'Auto Assignment'
,start_date => SYSDATE - 10
,end_date => SYSDATE + 1000);
END;
/

Read Only Schema in Oracle APPS 11i

In this article I have discussed how to create and maintain a read only schema for APPS in Oracle eBusiness Suite. Whilst in the past I have known clients to implement this using synonyms. However the approach discussed below is designed without the need of having to create a single synonym in APPS_QUERYschema.

Step 1
Create the read-only schema, in this case lets call it APPS_QUERY.

Step 2.
Surely, the schema created in above Step 1 will be given read only grants to objects in apps. There will be cases where the grant command might fail. To monitor such failures create a table as below
conn xx_g4g/&2 ;
--For APPS_QUERY. This table will capture the exceptions during Grants
PROMPT create table XX_GRANTS_FAIL_APPS_QUERY
create table XX_GRANTS_FAIL_APPS_QUERY (
object_name VARCHAR2(100)
,sqlerrm varchar2(2000)
,creation_date DATE
);

grant all on XX_GRANTS_FAIL_APPS_QUERY to apps with grant option;

grant select on XX_GRANTS_FAIL_APPS_QUERY to apps_query ;

Step 3
In this step we grant select on all the existing views and synonyms in apps schema to apps_query.

conn apps/&1 ;

PROMPT This can take upto 15-30 minutes
PROMPT Granting SELECT on All synonyms and views to apps_query
DECLARE
--One off script to execute grants to apps_query
v_error VARCHAR2(2000);
BEGIN

FOR p_rec IN (SELECT *
FROM all_objects
WHERE owner = 'APPS'
AND object_type IN ('SYNONYM', 'VIEW')
AND object_name NOT LIKE '%_S')
LOOP
BEGIN
EXECUTE IMMEDIATE 'grant select on ' || p_rec.object_name ||
' to apps_query';
EXCEPTION
WHEN OTHERS THEN
v_error := substr(SQLERRM, 1, 2000);
INSERT INTO bes.XX_GRANTS_FAIL_apps_query
(object_name
,SQLERRM
,creation_date
)
VALUES
(p_rec.object_name
,v_error
,sysdate
);
END;
END LOOP;
COMMIT;
END;
/

Step 4
Write a after logon trigger on apps_query schema. The main purpose of this trigger is to alter the session to apps schema, such that the CurrentSchema will be set to apps for the session(whilst retaining apps_query restrictions).In doing so your logon will retain the permissions of apps_query schema(read_only). Howerver it will be able to reference the apps objects with exactly the same name as does a direct connection to apps schema.


conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_apps_query_logon_trg
CREATE OR REPLACE TRIGGER xx_apps_query_logon_trg
--16Jun2006 By Anil Passi
--Trigger to toggle schema to apps, but yet retaining apps_query resitrictions
--Also sets the org_id
AFTER logon ON apps_query.SCHEMA
DECLARE
BEGIN
EXECUTE IMMEDIATE
'declare begin ' ||
'dbms_application_info.set_client_info ( 101 ); end;';
EXECUTE IMMEDIATE 'ALTER SESSION SET CURRENT_SCHEMA =APPS';
END;
/

Step 5
Create a Trigger on the apps schema to issue select only grants for all new views and synonyms. Please note that I am excluding grants for sequences. SELECT grants for views and synonyms will be provided to apps_query as and when such objects are created in APPS. Please note that, all the APPS objects (views and synonyms) that existed in APPS schema prior to the implementation of this design, would have been granted read-only access to apps_query in Step 2.

conn apps/&1 ;
PROMPT CREATE OR REPLACE TRIGGER xx_grant_apps_query
CREATE OR REPLACE TRIGGER xx_grant_apps_query
--16Jun2006 By Anil Passi
--
AFTER CREATE ON APPS.SCHEMA
DECLARE
l_str VARCHAR2(255);
l_job NUMBER;
BEGIN
IF (ora_dict_obj_type IN ('SYNONYM', 'VIEW'))
AND (ora_dict_obj_name NOT LIKE '%_S')
THEN
l_str := 'execute immediate "grant select on ' || ora_dict_obj_name ||
' to apps_query";';
dbms_job.submit(l_job, REPLACE(l_str, '"', ''''));
END IF;
END;
/


Some notes for this design

Note1
You need to ensure that the schema created in Step 1 has very limited permissions. Most importantly it must not be given grant for “EXECUTE/CREATE ANY PROCEDURE”. You will need to agree with your DBAs upfront for the permissions,

Note 2
Only views and synonyms will be granted access. Objects in your xx_g4g(bespoke) schema should have their synonyms in apps already in place.

Note 3
If your site has multi org enabled, you will then have to set the org I'd after loggiong on to apps query schema. In case you have only one single ORG_ID, then would have been set as in Step 4 above.

Note 4
ALTER SESSION SET CURRENT_SCHEMA =APPS
This facilitates users to run their queries as if they were connected to apps schema. However, their previliges will be restricted to those of apps_query

Note 5
It is assumed that ALTER SESSION privilege will exist for APPS_QUERY schema.

Oracle FNDLOAD Script Examples

In this article I wish to give real working examples of Oracle's FNDLOAD utility.
Besides that, I have included some useful notes on FNDLOAD utility

I have used FNDLOAD successfully in past for several different entities/data types within Oracle 11i for almost all my previous clients, ever since this utility became available.
Some of the examples in this FNDLOAD article include:-
FNDLOAD to transfer Request Groups
FNDLOAD for moving Concurrent Programs
FNDLOAD to download and upload Forms Personalizations ( or Personalisations depending on where you are located )

To FNDLOAD Web ADI, visit the link Web ADI FNDLOAD

Use FNDLOAD for transferring value set definitions.
-->Please note that when transferring Key Flex Fields and Descriptive flex fields the respective value sets against each segment will be extracted and loaded automatically.

Also, FNDLOAD can be used to migrate Key FlexFields, Descriptive Flexfields, Responsibilities and almost every other FND entity.

Please note that the text written down here could get wrapped in the browser.
Hence you may have to use \ to continue the single line command on Unix, in case you find the lines wrapping
In my case I am ensuring that $CLIENT_APPS_PWD has the apps password before running the scripts


------------------------------------------------------------------------------------------


##To FNDLOAD Request groups
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct XX_MY_REPORT_GROUP_NAME.ldt REQUEST_GROUP REQUEST_GROUP_NAME="XX_MY_REPORT_GROUP_NAME" APPLICATION_SHORT_NAME="XXGMS"
##Note that
##---------
## <> will be your Application Shortname where request group is registered
## XX_MY_REPORT_GROUP_NAME
Will be the name of your request group
## ##To upload this Request Group in other environment after having transferred the ldt file

FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpreqg.lct


------------------------------------------------------------------------------------------


##To FNDLOAD Concurrent Programs
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt PROGRAM APPLICATION_SHORT_NAME="XXGMS" CONCURRENT_PROGRAM_NAME="XX_CUSTOM_ORACLE_INTERFACE_PROG"
##Note that
##---------
## XXGMS will be your custom GMS Application Shortname where concurrent program is registered
## XX_CUSTOM_ORACLE_INTERFACE_PROG
Will be the name of your request group
## XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt is the file where concurrent program definition will be extracted
## ##To upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XX_CUSTOM_ORACLE_INTERFACE_PROG.ldt


------------------------------------------------------------------------------------------


##To FNDLOAD Oracle Descriptive Flexfields
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PO DESCRIPTIVE_FLEXFIELD_NAME='PO_REQUISITION_HEADERS'
##Note that
##---------
## PO is the Application Shortname against which descriptive flexfield against PO Headers is registered
## PO_REQUISITION_HEADERS
is the name of Descriptive Flexfield against PO Requisition Headers
## Use the SQL below to find the name of DFF, rather than logging into the screen (ooops via jinitiator)
########----->SELECT
########----->application_id, DESCRIPTIVE_FLEXFIELD_NAME, application_table_name
########----->FROM
########-----> fnd_descriptive_flexs_vl
########----->WHERE
########-----> APPLICATION_TABLE_NAME like '%' || upper('&tab_name') || '%'
########----->ORDER BY APPLICATION_TABLE_NAME
########----->/
## To upload into another environment
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct XX_PO_REQ_HEADERS_DFF.ldt

## OK another example for DFF against FND_LOOKUPS
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_FND_COMMON_LOOKUPS_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=FND DESCRIPTIVE_FLEXFIELD_NAME='FND_COMMON_LOOKUPS'
## OK another example for DFF against Project Accounting Expenditure Types
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct XX_PA_EXPENDITURE_TYPES_DESC_FLEX_DFF.ldt DESC_FLEX APPLICATION_SHORT_NAME=PA DESCRIPTIVE_FLEXFIELD_NAME='PA_EXPENDITURE_TYPES_DESC_FLEX'



------------------------------------------------------------------------------------------


##To FNDLOAD Oracle Menus
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt MENU MENU_NAME="ICX_POR_SSP_HOME"
##Note that
##---------
## Oracle Menus are not attached to applications. Hence no need to include application short name
## ICX_POR_SSP_HOME is the menu name. This can be validated via below SQL
## select user_menu_name from fnd_menus_vl where menu_name = 'ICX_POR_SSP_HOME' ;
## Also note that we do not pass in the User_menu_name in this example
## OK, now to upload this file
$FND_TOP/bin/FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct ICX_POR_SSP_HOME.ldt


----------------------------------------------------------------------------------------------------------------------------

## Well, now for FND Messages to download a single message
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct \
XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='ICX' MESSAGE_NAME=XX_ICX_POR_LIFECYCLE_PAY_TIP

## Or you may as well download all the messages within an application
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afmdmsg.lct \
XX_ALL_GMS_MESSAGES_00.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME='XXGMS'

## now to upload using FNDLOAD
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afmdmsg.lct XX_ICX_POR_LIFECYCLE_PAY_TIP.ldt


----------------------------------------------------------------------------------------------------------------------------


## Now it's the turn of Lookup values. Again, its not a rocket science
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME ='XXGMS' LOOKUP_TYPE="XX_TRX_BATCH_STATUS"
## Note that
## XX_TRX_BATCH_STATUS is the name of FND Lookup Type in this example
## This will download all the lookup codes within the defined lookup
## To upload
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD aflvmlu.lct XX_TRX_BATCH_STATUS.ldt

----------------------------------------------------------------------------------------------------------------------------

## You can also move the User definitions from FND_USER
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct ./XX_FND_USER_PASSI.ldt FND_USER USER_NAME='ANILPASSI'
#Do not worry about your password being extracted, it will be encrypted as below in ldt file
#BEGIN FND_USER "ANILPASSI"
# OWNER = "PASSIA"
# LAST_UPDATE_DATE = "2005/10/19"
# ENCRYPTED_USER_PASSWORD = "ZGE45A8A9BE5CF4339596C625B99CAEDF136C34FEA244DC7A"
# SESSION_NUMBER = "0"
To upload the FND_USER using FNDLOAD command use
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct ./XX_FND_USER_PASSI.ldt
Notes for using FNDLOAD against FND_USER:-
1. After uploading using FNDLOAD, user will be promoted to change their password again during their next signon attempt.
2. All the responsibilities will be extracted by FNDLOAD alongwith User Definition in FND_USER
3. In the Target Environment , make sure that you have done FNDLOAD for new responsibilities prior to running FNDLOAD on users.

----------------------------------------------------------------------------------------------------------------------------


## Now lets have a look at the profile option using oracle's FNDLOAD
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt PROFILE PROFILE_NAME="POR_ENABLE_REQ_HEADER_CUST" APPLICATION_SHORT_NAME="ICX"
## Note that
## POR_ENABLE_REQ_HEADER_CUST is the short name of profile option
## We aren't passing the user profile option name in this case. Validate using ...
########----->select application_id, PROFILE_OPTION_NAME || '==>' || profile_option_id || '==>' ||
########----->USER_PROFILE_OPTION_NAME
########----->from FND_PROFILE_OPTIONS_VL
########----->where PROFILE_OPTION_NAME like '%' || upper('&profile_option_name') || '%'
########----->order by PROFILE_OPTION_NAME
########----->/
## Now to upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct POR_ENABLE_REQ_HEADER_CUST.ldt


----------------------------------------------------------------------------------------------------------------------------


## Now for the request sets that contain the stages and links for underlying concurrent programs
## For this you will be firstly required to download the request set definition.
## Next you will be required to download the Sets Linkage definition
## Well, lets be clear here, the above sequence is more important while uploading
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt REQ_SET REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt REQ_SET_LINKS REQUEST_SET_NAME="FNDRSSUB4610101_Will_look_like_this"
## Note that FNDRSSUB4610101 can be found by doing an examine on the
########----->select request_set_name from fnd_request_sets_vl
########----->where user_request_set_name = 'User visible name for the request set here'
## Now for uploading the request set, execute the below commands
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/afcprset.lct XX_GL_MY_INTERFACE_SET_LINK.ldt


----------------------------------------------------------------------------------------------------------------------------


## Now for the responsibility
FNDLOAD apps/$CLIENT_APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt FND_RESPONSIBILITY RESP_KEY="XX_PERSON_RESPY"
## note that XX_PERSON_RESPY is the responsibility key
## Now to upload
FNDLOAD apps/$CLIENT_APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afscursp.lct XX_PERSON_RESPY.ldt


----------------------------------------------------------------------------------------------------------------------------
## OK, now for the forms personalizations
## For the forms personalizations, I have given three examples as below.
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt FND_FORM_CUSTOM_RULES function_name="PERWSHRG-404"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt FND_FORM_CUSTOM_RULES function_name="HZ_ARXCUDCI_STD"
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt FND_FORM_CUSTOM_RULES function_name="AP_APXVDMVD"
## Note that the function name above is the function short name as seen in the Function Definition Screen
## Now to upload the forms personalizations that are defined against these forms functions....
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_PERWSHRG.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_HZ_ARXCUDCI_STD.ldt
FNDLOAD apps/$CLIENT_APPS_PWD 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XX_AP_APXVDMVD.ldt


----------------------------------------------------------------------------------------------------------------------------


Notes :
1. Give special attention when downloading Menus or Responsibilities.
In case your client has several developers modifying Responsibilities and Menus, then be ultra carefull. Not being carefull will mean that untested Forms and Functions will become available in your clients Production environment besides your tested forms, functions and menus.

2. Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes.
By doing so, you will download and extract all the test data in GL Codes that might not be applicable for production.

3. There are several variations possible for FNDLOAD, for example you can restrict the download and uploads to specific segments within Descriptive Flex Fields. Please amend the above examples as desired for applying appropriate filterations.

4. The list of examples by no mean cover all possible FNDLOAD entities.

5. FNDLOAD is very reliable and stable, if used properly. This happens to by one of my favourite Oracle utilities.

4. Last but not the least, please test your FNDLOAD properly, so as to ensure that you do not get any unexpected data. In past I have noticed undesired results when the Lookup gets modified manually directly on production, and then the FNDLOAD is run for similar changes. If possible, try to follow a good practice of modifying FNDLOADable data only by FNDLOAD on production environment.

5. As the name suggests, FNDLOAD is useful for FND Related objects. However in any implementation, you will be required to migrate the Setups in Financials and Oracle HRMS from one environment to another. For this you can use iSetup. "Oracle iSetup".
Some of the things that can be migrated using Oracle iSetup are
GL Set of Books, HR Organization Structures, HRMS Employees, Profile Options Setup, Suppliers, Customers, Tax Codes
& Tax Rates, Financials Setup, Accounting Calendars, Chart of Accounts, GL Currencies.

Playing with CUSTOM.pll

lease find some commands for CUSTOM.pll

To convert from CUSTOM.pll to CUSTOM.pld
f60gen module_type=LIBRARY module=CUSTOM script=YES userid=apps/apps

To convert back from CUSTOM.pld to CUSTOM.pll ( after having edited the text pld file )
f60gen module_type=LIBRARY module=CUSTOM parse=YES userid=apps/apps

To convert from CUSTOM.pll to CUSTOM.plx
f60gen module_type=LIBRARY module=CUSTOM userid=apps/apps

SSHR Rollout Create FND_USER and Allocate Responsibilities

Every organization that rolls out any Oracle Self Service Application requires a methodology to allocate responsibilities and to create Users.
The pseudo code example taken here has following requirements:-
1. Create a concurrent program, passing to parameter for one Cost Centre/Group or all Cost Centres.
This way, Self Service can be rolled out in Phased manner.
2. Besides a parameter for the cost centre, there will be another parameter to run the process in a Read-Only mode.
3. There are two possibilities for the Employees which are eligible(given their cost centre).
Possibility a. The employee already has an existing FND_USER record
Possibility b. The employee does not have an existing FND_USER record.

Solution for Possibility a.
Identify the FND_USER record, using the fnd_user.employee_id = per_all_people_f.person_id
Use fnd_user_resp_groups_api.insert_assignment to add new Self Service responsibility to that User.
Send the user an Email giving them details of the responsibility that has been added.

Solution for Possibility b.
Create an FND_USER record, using the fnd_user_pkg, attaching this to Person Id.
Use fnd_user_resp_groups_api.insert_assignment to add new Self Service responsibility to that User.
Send them an email to inform the User-Id and Password along with instructions to log on & use the application.


Questions & Answers
How do I create a 8 digit password for FND_USER record?
FUNCTION get_random_password RETURN VARCHAR2 IS
BEGIN
RETURN lower(dbms_random.STRING('X',8));
END get_random_password;

We want users to be enforced to alter their password every 6months, how to ?
Use parameter x_password_lifespan_days
fnd_user_pkg.createuser
(
x_user_name => p_user_name
,x_owner => ''
,x_unencrypted_password => v_password
,x_description => p_person_description
,x_password_lifespan_days => 180
,x_employee_id => p_person_id
,x_email_address => p_email_address
);


The random password that is generated might have repeating characters, which will error if Profile "Signon Password Hard To Guess" is Set to Yes?
You can temporarily set the profile to N in the session during which concurrent program runs.
fnd_profile.put(NAME => 'SIGNON_PASSWORD_HARD_TO_GUESS' ,val => 'N');


Should I use Workflow to inform users?
You might as well use SMTP. Be careful that on Dev environment business users do not receive Emails.
This can be ensured by checking return of select instance_name from v$instance to be PRD Database.
You can use procedure send_html_email in the Code Sample in link provided in the article.


Can I run the source code as is?
The source code provided is for allocating a responsibility named "XX HR Employee Self Service".I have removed all the client specific bits from the code. Hence you will need to alter to make this to be of any use. The idea is to merely provide you a guideline for API Usage.

Validate Email PL/SQL

Oracle delivers an API in EBS that can validate Email Address Format.
However it does not capture the invalidity if Email String were to be say xxx@host
You may use the below pl/sql function for Email Address Validation.


CREATE OR REPLACE FUNCTION xx_check_email(l_user_name IN VARCHAR2)
RETURN VARCHAR2 IS
l_dot_pos NUMBER;
l_at_pos NUMBER;
l_str_length NUMBER;
BEGIN
l_dot_pos := instr(l_user_name
,'.');
l_at_pos := instr(l_user_name
,'@');
l_str_length := length(l_user_name);
IF ((l_dot_pos = 0) OR (l_at_pos = 0) OR (l_dot_pos = l_at_pos + 1) OR
(l_at_pos = 1) OR (l_at_pos = l_str_length) OR
(l_dot_pos = l_str_length))
THEN
RETURN 'FAILURE';
END IF;
IF instr(substr(l_user_name
,l_at_pos)
,'.') = 0
THEN
RETURN 'FAILURE';
END IF;
RETURN 'SUCCESS';
END xx_check_email;

Which FND_USER is locking that table

This is a quick note to share a SQL that will tell you the FND_USER.USER_NAME of the person that has locked a given table in Oracle APPS.

The column named "module" will tell you the name of the Form Function or the Concurrent Program Short name which has aquired a lock onto that table.


SELECT c.owner
,c.object_name
,c.object_type
,fu.user_name locking_fnd_user_name
,fl.start_time locking_fnd_user_login_time
,vs.module
,vs.machine
,vs.osuser
,vlocked.oracle_username
,vs.sid
,vp.pid
,vp.spid AS os_process
,vs.serial#
,vs.status
,vs.saddr
,vs.audsid
,vs.process
FROM fnd_logins fl
,fnd_user fu
,v$locked_object vlocked
,v$process vp
,v$session vs
,dba_objects c
WHERE vs.sid = vlocked.session_id
AND vlocked.object_id = c.object_id
AND vs.paddr = vp.addr
AND vp.spid = fl.process_spid(+)
AND vp.pid = fl.pid(+)
AND fl.user_id = fu.user_id(+)
AND c.object_name LIKE '%' || upper('&tab_name_leaveblank4all') || '%'
AND nvl(vs.status
,'XX') != 'KILLED';

OA Framework Tutorial 01 Script

Please find below the script for the First Audio Video Tutorial in OA Framework

create table xx_person_details
(
PERSON_ID NUMBER NOT NULL
,FIRST_NAME VARCHAR2(50)
,LAST_NAME VARCHAR2(50)
,EMAIL VARCHAR2(100)
,student_flag VARCHAR2(1)
,name_of_university VARCHAR2(100)
,last_update_date DATE
,last_update_login INTEGER
,last_updated_by INTEGER
,creation_date DATE
,created_by INTEGER
) ;

CREATE OR REPLACE PROCEDURE xx_insert_person_prc(p_person_rec IN OUT xx_person_details%ROWTYPE) IS
BEGIN
SELECT per_people_s.NEXTVAL
INTO p_person_rec.person_id
FROM dual;
p_person_rec.last_update_date := SYSDATE;
p_person_rec.creation_date := SYSDATE;
p_person_rec.last_update_login := -1;
p_person_rec.last_updated_by := 71690;
p_person_rec.created_by := 71690;
INSERT INTO xx_person_details
VALUES p_person_rec;
END xx_insert_person_prc;
/



DECLARE
l_person_rec xx_person_details%ROWTYPE;
BEGIN
l_person_rec.first_name := 'Anil';
l_person_rec.last_name := 'Passi';
l_person_rec.email := 'passi.anil@gmail.com';
l_person_rec.student_flag := 'N';
xx_insert_person_prc(l_person_rec);
l_person_rec.first_name := 'Tom';
l_person_rec.last_name := 'Kumar';
l_person_rec.email := 'tom.kumar@gmail.com';
xx_insert_person_prc(l_person_rec);
l_person_rec.first_name := 'Mark';
l_person_rec.last_name := 'Kapoor';
l_person_rec.email := 'tom.kumar@gmail.com';
xx_insert_person_prc(l_person_rec);
commit;
END;
/

SELECT count(*)
FROM xx_person_details ;


commit;

Calling a PL/SQL Concurrent Program in Real-Time

Lets say there is an Oracle Delivered concurrent program of type PL/SQL.

Your requirement is such that you wish to call that PL/SQL Executable [attached to concurrent program] directly from some other process, in real-time mode, in the same session as that of the parent Oracle session[forms or report or some other plsql ]. In order to call that pl/sql program within the same session, your challenge may be that the concurrent program could be writing into log/output file using fnd_file API which you want to display to the user somehow.
To get a handle to the log/output of the API [which is executable of Conc Program], we need to do the following:-
1. Call fnd_global.initialize ensuring you pass it two parameters in bold below
declare
...your variables here
v_session_id INTEGER := userenv('sessionid');
v_trace VARCHAR2(2000) := fnd_trace.get_trace_filename;
n_conc_request_id INTEGER ;
begin
//optionally trace the API Call with bind variable values
execute immediate 'alter session set events=''10046 trace name context forever, level 12''' ;
//the trace file details will be in variable v_trace

//Now call fnd_global.initialize ensuring you pass it two parameters in bold below

fnd_global.initialize
(
session_id => v_session_id
,user_id => 18594 //or pass the current fnd_global.user_id itself
,resp_id => 4650126 //or pass the current fnd_global.resp_id itself
,resp_appl_id => 8405 //or pass the current fnd_global.resp_appl_id itself
,security_group_id => 0
,site_id => NULL
,login_id => 3115003
,conc_login_id => NULL
,prog_appl_id => NULL
,conc_program_id => 229233 //The program id of concurrent request
,conc_request_id => n_conc_request_id //From fnd_concurrent_requests_s.nextval
,conc_priority_request => NULL
);
//Lets assume the nextval from sequence is 14633154
//now call the API, i have arbitrarily typed in just any API
ar_process_trx.place_hold( v_errbuf, v_retcode, v_trx_id ) ;
end ;

2. Run the SQL on fnd_temp_files, as below, using the fictional request_id generated from sequence [ in variable n_conc_request_id ]

3. In order to read those files, ensure that the directory [/usr/tmp in this case] is listed in v$parameter utl_file_dir



In this approach, no change is made to the Original concurrent program. Hence, if need be, users can continue to run the concurrent process as usual from SRS.



Trace file with bind variables
This approach also makes it easy to trace a concurrent program with bind variables. The location of the trace will can be determined by using API fnd_trace.get_trace_filename. The beauty of this API construct is that it takes care of different platforms like Linux, NT, HP,AIX etc , when constructing the name and full path of the trace file for Concurrent Program