All about Host Concurrent Programs

One of the things to remember while registering a host concurrent program is that the first five parameters are reserved by Oracle E-Business Suite for its own use. The sixth parameter onwards is used for user-defined concurrent program parameters. The first five parameters refer to the following:

$0: The shell script to be executed
$1: Oracle user/password
$2: Applications user_id
$3: Application user_name
$4: Concurrent program request_id

In addition to these, the environment variable FCP_LOGIN is also used to store the Oracle user/password. The steps required to register a shell script as a concurrent program are:

1. Create the shell script in the $APPLBIN directory of the specific application top. In this example, I have created the script under $CS_TOP/bin and named it myscr.prog(Oracle documentation mentions that the extension should be .prog). Its contents are listed below:

1 2 3 4 5 6 7 8 9 10 11

#!/bin/sh echo 'Printing parameters....' echo '0:'$0 echo '1:'$1 echo '2:'$2 echo '3:'$3 echo '4:'$4 echo '5:'$5 echo 'FCPLOGIN:'$FCP_LOGIN echo 'Finished printing parameters.'

2. Define the concurrent executable with Execution Method ‘Host’. In the Execution File Name field, specify the name of the shell script without extension(‘myscr’ for this example)

3. Define the concurrent program with parameters as required. For this example, I have defined one parameter with default value ‘ABCDEF’

4. Add the concurrent program to a request group

5. Make a symbolic link using the execution file name to fndcpesr, which is located in the $FND_TOP/$APPLBIN directory. The symbolic link is created in the same directory as the shell script.

1	[oracle@myapps bin]$ ln -s $FND_TOP/bin/fndcpesr  myscr

Ensure that all files have execute permissions to prevent any ‘Permission denied’ errors. You should now be able to execute the concurrent program which in turn will run the shell script. Executing the concurrent program for this example results in a log file containing the following output. Note that the values of FCP_LOGIN, the reserved parameters and the user-defined parameter are all printed.

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721361
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

Return codes for host concurrent program

If the script traps an error, use the UNIX exit command ‘exit 1′ to return failure (status code 1) to the Concurrent Manager running the program. Of course, code sections after the exit command will not be executed.

1 2 3 4 5 6 7 8 9 10 11 12 13

#!/bin/sh echo 'Printing parameters....' echo '0:'$0 echo '1:'$1 echo '2:'$2 echo '3:'$3 echo '4:'$4 echo '5:'$5 exit 1 echo 'FCPLOGIN:'$FCP_LOGIN echo 'Finished printing parameters.'

The concurrent program will complete with status ‘Error’ and the log file will contain the following:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721405
5:ABCDEF
/u01/oracle/visappl/cs/11.5.0/bin/myscr
Program exited with status 1

There are no defined exit commands to return a warning status. However, it can be done by using the FND_CONCURRENT.SET_COMPLETION_STATUS API to set the completion status of the request to ‘Warning’. Gareth Roberts deserves a big ‘Thank You’ for posting this on Oracle Forums.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

#!/bin/sh echo 'Printing parameters....' echo '0:'$0 echo '1:'$1 echo '2:'$2 echo '3:'$3 echo '4:'$4 echo '5:'$5 MYSTATUS=`sqlplus -s $1 <<! SET HEADING FEEDBACK OFF PAGESIZE 0 declare l_result boolean; l_session_id number; begin fnd_global.INITIALIZE(l_session_id, null, null, null,null, -1, null, null, null, null, $4, null,null,null,null,null,null,-1); l_result := fnd_concurrent.set_completion_status('WARNING','Review log file for details.'); commit; end; / exit; !` echo 'FCPLOGIN:'$FCP_LOGIN echo 'Finished printing parameters.'

This solution makes use of a SQL script to initialize a session with the request_id of the concurrent program using FND_GLOBAL.INITIALIZE and then sets the completion status. Upon execution, the concurrent program ends with a ‘Warning’ status and generates the following output:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/APPS
2:1001530
3:EBUSINESS
4:2721408
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

One important thing to notice is that echoing the parameters $1 and $FCP_LOGIN leads to the Oracle user/password being written to the log file. This can be prevented by using the options ENCRYPT and SECURE while defining the concurrent program. ENCRYPT signals the Concurrent Manager to pass the Oracle password in the environment variable FCP_LOGIN. The Concurrent Manager leaves the password in the argument $1 blank. To prevent the password from being passed, enter SECURE in the Execution Options field. With this change, Concurrent Manager does not pass the password to the program.

For this example specifying SECURE in the concurrent program options:

and then running the concurrent program does not set the completion status to ‘Warning’ since the Oracle user/password is not passed and the SQL script cannot run. This can be observed from the contents of the log file.

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/
2:1001530
3:EBUSINESS
4:2721412
5:ABCDEF
FCPLOGIN:
Finished printing parameters.

If we set the options field in the concurrent program to ENCRYPT

then the Oracle user/password will be passed only to $FCP_LOGIN and not to $1. We can change the SQL script to use $FCP_LOGIN instead of $1 and execute the concurrent program. It will now complete with a ‘Warning’ status since the Oracle user/password was passed to the script through $FCP_LOGIN. This can be verified from the contents of the log file:

Printing parameters….
0:/u01/oracle/visappl/cs/11.5.0/bin/myscr.prog
1:APPS/
2:1001530
3:EBUSINESS
4:2721409
5:ABCDEF
FCPLOGIN:APPS/APPS
Finished printing parameters.

Note:

1. Use ${10}, ${11} instead of $10,$11 to refer to double-digit parameters
2. The FND_GLOBAL.INITIALIZE procedure is used to set new values for security globals during login or when the responsibility is changed, it accepts the following parameters:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

session_id in out number, user_id in number, resp_id in number, resp_appl_id in number, security_group_id in number, site_id in number, login_id in number, conc_login_id in number, prog_appl_id in number, conc_program_id in number, conc_request_id in number, conc_priority_request in number, form_id in number default null, form_appl_id in number default null, conc_process_id in number default null, conc_queue_id in number default null, queue_appl_id in number default null, server_id in number default -1

Materialized Views

Creating materialized view that refreshes every 1 min

I have demonstrated in steps where a materialized view refresh after every one minute ,for having a mv which refresh after 5 minute use next(sysdate+1/1440)

Step1:

Create table temp (A int);

Step2:

Create Materialized view temp_mv
      refresh complete start with (sysdate) next  (sysdate+1/1440) with rowid
        as select * from temp;

Step3:

select count(*) from temp;

       COUNT(*)
      ----------
          0

Step4:

select count(*) from temp_mv;

       COUNT(*)
      ----------
          0

Step5:

begin
      for i in 1..10 loop
         insert into temp values (i+1);
      end loop;
end;
/

Step6:

commit;

Step7:

select count(*) from temp;

       COUNT(*)
     ----------
        10

Step8:

select count(*) from temp_mv;

       COUNT(*)
       ----------
          0

Step9:

select to_char(sysdate,'hh:mi') from dual;

       TO_CH
       -----
       04:28

Step10:

select to_char(sysdate,'hh:mi') from dual;

       TO_CH
        -----
       04:29

Step11:

select count(*) from temp;

      COUNT(*)
     ----------
        10

Step12:

select count(*) from temp_mv;

      COUNT(*)
      ----------
         10

Script to reset Oracle Apps frontend User ID Password

Here is a  sample anonymous PL/SQL block which will reset the Oracle Apps front end password for a given user from back end.

DECLARE
      flag_value BOOLEAN;
BEGIN
      flag_value :=
           fnd_user_pkg.changepassword(username=> 'SXMARGAM'
                                                 ,newpassword => 'welcome1');
     IF flag_value
     THEN
           DBMS_OUTPU.PUT_LINE('The password reset successfully');
     ELSE
           DBMS_OUTPUT.PUT_LINE('The password reset has failed');
     END IF;

END;
/
COMMIT;

How to add or remove the "About This Page" link on E-Business Suite Framework pages?

This document describes:-

1. How to remove the 'About this page' link from OA Framework based Self Service pages

2. How to add the 'About this page' link to OA Framework based Self Service pages

Solution

1. How to remove the 'About this page' link
In order to hide the 'About this Page' link, BOTH of the following system Profile options need to be set to a value of "No"
"FND: Diagnostics" (Short name : FND_DIAGNOSTICS) and "Personalize Self-Service Defn" (Short name : FND_CUSTOM_OA_DEFINTION)
Once these profile options have been changed, users will need to logout and re-login for the change to take effect
If after making the above changes the pages still show the 'About this Page' link, then you can take these steps to confirm the issue:-

a. Check the profile options are not set to "Yes" at any level
The profile options can be set at different levels, so need to be sure the profile option values have been changed at all levels that may effect the users

Run this script two times, once for each of the following profile values FND%Diagnostics Personalize

b. Restart Apache
If all profile options are set correctly, then restarting Apache will ensure the profile options are not cached in Apache's Java Cache

2. How to add the 'About this page' link
To enable the 'About this Page' link on OA Framework pages, you should set EITHER or BOTH of the following system Profile options to a value of "Yes"
For this purpose of enabling the 'About this page' link' it does not matter which profile option you enable, but be sure you are aware of the additional effects of the profile option you decide to enable. Review the Profile Option documentation listed in the references section below if you need explanations of these effects
You can set these profile options at different levels to effect all or some users as appropriate for your requirement
"FND: Diagnostics" (Short name : FND_DIAGNOSTICS) and/or"Personalize Self-Service Defn" (Short name : FND_CUSTOM_OA_DEFINTION)
Once these profile options have been changed, users will need to logout and re-login for the change to take effect
If after making the above changes the pages still do not show the 'About this Page' link, then you can take the same steps described above to investigate the issue

Setup user as a worker to access Purchase Order form

Following is the common note shown by application while normal user trying to access Purchase Orders form.
"You are not setup as a worker. To access this form you need to be a worker."

Follow below steps to setup user as worker/buyer.

1. Navigate to Human Resources->People->Enter and Maintain
Provide all mandatory details in People form.
Select Person Types as Employee(Use Action lov to get this)
Save the changes.

2. Navigate to Purchasing->Setup->Personnel->Buyers
This opens Buyers OA page.
Click on Add Buyer button and add the person created in last step as a Buyer.
Save Changes.

3.Navigate to System Administrator->Security->User->Define
It opens Users form.
Query your preferred user details.

Add person created in the previous steps to this user.
Save Changes.

4.Navigate back to Purchasing->Purchase Orders->Purchase Orders
Now application allow you to see Purchase Order form with out any notes.