Running Concurrent Program from back end

Here i am going to explain how to execute a concurrent program from the backend.

Let us take a concurrent program "Subinventory quantities report" and run it in the front end. Check whether its executing successfully from the front end.

Login into the vision instance. (Username : OPERATIONS , Password : welcome )

Choose the "Inventory, Vision Operations (USA)" responsibility

Go to View -> Requests

Press "Submit a New Request"

Select "Single Request" and then press "OK"

Give the Request name as "Subinventory quantities report" and press TAB key

Give the parameters

Subinventory From : DropShip
Subinventory To : DropShip
Items From :
To :



Press OK and view the output for the report

Till now you successfully exectued a concurrent program from the front end.

Now we are going to see how this concurrent program can be executed in the back end

"fnd_request.submit_request" is used to execute the concurrent program from back end
before calling this function we need to initialize the Application from the back end.

"fnd_global.apps_initialize" is used to initialize the application from the back end.


procedure APPS_INITIALIZE(user_id in number,
resp_id in number,
resp_appl_id in number,
security_group_id in number default 0,
server_id in number default -1)

Argument : User_id

Its nothing but ID of the User in which the apps was logged in. Here we logged in as "OPERATIONS" as the user. To know

the user id of "OPERATIONS" we use the following query.

SELECT user_id FROM fnd_user WHERE user_name = 'OPERATIONS';

Arguemnt : resp_id

Its nothing but ID of the responsiblity which we selected. Here we are in "Inventory, Vision Operations (USA)" reponsibility.

select responsibility_id from fnd_responsibility_tl
where responsibility_name ='Inventory, Vision Operations (USA)'

If we know the resposbility short name, we can use the following query to get the responsiblity id.

select responsibility_id from fnd_responsibility
where responsibility_key='INVENTORY_VISION_OPERATIONS'

Argument : resp_appl_id

Its the ID for the application for which the responsibility was registered.

select application_id from fnd_responsibility_tl
where responsibility_name ='Inventory, Vision Operations (USA)'


select application_id from fnd_responsibility
where responsibility_key='INVENTORY_VISION_OPERATIONS'


function submit_request ( application IN varchar2 default NULL,
program IN varchar2 default NULL,
description IN varchar2 default NULL,
start_time IN varchar2 default NULL,
sub_request IN boolean default FALSE,
argument1 IN varchar2 default CHR(0),
argument2 IN varchar2 default CHR(0),
argument3 IN varchar2 default CHR(0),
.
.

argument100 IN varchar2 default CHR(0)) return number ;

Argument : application

Its the application to which the concurrent program was registered.

select fa.application_short_name
from fnd_application fa,fnd_concurrent_programs_tl fcp
where fa.application_id=fcp.application_id
and fcp.user_concurrent_program_name='Subinventory quantities report'

Argument : program

Its the short name of the concurrent program.

select fcp.concurrent_program_name
from fnd_concurrent_programs fcp,fnd_concurrent_programs_tl fcpt
where fcp.concurrent_program_id=fcpt.concurrent_program_id
and fcpt.user_concurrent_program_name='Subinventory quantities report'

Argument : description

Its the discription of the concurrent program. Its a optional parameter

select description from fnd_concurrent_programs_tl
where user_concurrent_program_name='Subinventory quantities report'

Argument : start_time
Its used for scheduled concurrent program. Its also a optional parameter

Argument : sub_request
Mostly FALSE

Argument : Argument1,Argument2,Argument3,.....Argument100
These are the parameters passed to the concurrent program. Some of the parameters were hiddend from the user as it

takes the value from profile.

To know the exact parameter values, check the previous concurrent program submission and check the parameter passed.

Otherwise open the Concurrent program from the application developer and see the parameter passed. Otherwise see the log file of the previously executed concurrent program

---log----
Arguments
------------
P_ORGANIZATION_ID='207'
P_QTY_PRECISION='2'
P_SUBINV_LO='DropShip'
P_SUBINV_HI='DropShip'
------------
---log----

In this case the arguments are
(tokens in the parameter window)
P_ORGANIZATION_ID (hidden)
P_QTY_PRECISION (hidden)
P_SUBINV_LO (optional)
P_SUBINV_HI (optional)
P_ITEM_LO (optional)
P_ITEM_HI (optional)

-------------------------------------------------------------------------------------------
Procedure to run the concurrent program from the back end

SET serveroutput on;

DECLARE
  v_user_id     NUMBER;
  v_app_id      NUMBER;
  v_resp_id     NUMBER;
  v_req_id      NUMBER;
  v_exec_mode   VARCHAR2 (1) := 'S';
BEGIN
  SELECT user_id
    INTO v_user_id
    FROM fnd_user
   WHERE user_name = 'OPERATIONS';

  SELECT application_id
    INTO v_app_id
    FROM fnd_application
   WHERE application_short_name = 'INV';

  SELECT responsibility_id
    INTO v_resp_id
    FROM fnd_responsibility
   WHERE application_id = v_app_id
     AND responsibility_key = 'INVENTORY_VISION_OPERATIONS';

  fnd_global.apps_initialize (v_user_id, v_resp_id, v_app_id);
  v_req_id :=
     fnd_request.submit_request (application      => 'INV',
                                 program          => 'INVIRSIQ',
                                 sub_request      => FALSE,
                                 argument1        => 207,
                                 argument2        => 2,
                                 argument3        => 'DropShip',
                                 argument4        => 'DropShip',
                                 argument5        => NULL,
                                 argument6        => NULL
                                );
  DBMS_OUTPUT.put_line ('Request_ID = ' || v_req_id);
  COMMIT;
END;

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

Usefull SQLs

To convert the number in words
select to_char(to_Date(5373484,'J'),'Jsp') Result from dual

The result is : Five Million Three Hundred Seventy-Three Thousand Four Hundred Eighty-Four
Note : This is the maximum number u can convert in words ( 5373484 , because Julian year ranges between 1 and 5373484 )


Procedure to copy values from one column to another column

DROP TABLE xxtest;

CREATE TABLE xxtest(col1 NUMBER,col2 NUMBER);

INSERT INTO xxtest VALUES (1, NULL);
INSERT INTO xxtest VALUES (2, NULL);
INSERT INTO xxtest VALUES (3, NULL);
INSERT INTO xxtest VALUES (4, NULL);

BEGIN
  FOR i IN (SELECT ROWID, col1 FROM xxtest)
  LOOP
     UPDATE xxtest SET col2 = i.col1
      WHERE ROWID = i.ROWID;
  END LOOP;
END;

Procedure to change first 3 character in a column

DROP TABLE xxtest2;

CREATE TABLE xxtest2(NAME VARCHAR2(20));

INSERT INTO xxtest2 VALUES ('abc123');
INSERT INTO xxtest2 VALUES ('abc124');
INSERT INTO xxtest2 VALUES ('abc125');

BEGIN
  FOR i IN (SELECT ROWID, NAME FROM xxtest2
             WHERE SUBSTR (NAME, 1, 3) = 'abc')
  LOOP
     UPDATE xxtest2 SET NAME = 'xyz' || SUBSTR (i.NAME, 4, LENGTH (i.NAME))
      WHERE ROWID = i.ROWID;
  END LOOP;
END;

DENSE_RANK

Here we are going to use the analytical function DENSE_RANK to find the employees who hold the 7th highest salary

select * from (select ename, sal , dense_rank() over(order by sal desc) myrank from emp) where myrank=7

Plan Table

Plan Table is a table which will be created to see the execution method of a SQL script. Using the execution method we can optimize the query. This is mostly used during performance tunning.

EXPLAIN PLAN is the utility used by oracle to know the execution path of the SQL query before actually executing it.

There are many third party to tool to know the performance of the query and the execution plan.
TOAD is one of such tool

Let us see how to view the explain plan in TOAD which is used while performance tunning

1. We should create a plan table to view the explain plan of the query
The script for creating a plan table will be in the following path
$ORACLE_HOME/rdbms/admin/ and the file name is utlxplan.sql
This script is used to create the plan table ( PLAN_TABLE)

2. Change the setting in your TOAD
Open the TOAD and go to View -> Options


Then go to Oracle -> General



In that give the schema and plan table name.
Schema : APPS
Table Name : PLAN_TABLE

3 .To view the explain plan
Create a query and click the explain plan tab in TOAD. Now you can see the execution method of the table which is used to optimize and tune the query


Errors while viewing Explain Plan
We may get two types of errors while viewing explain plan
1. ORA-02404: specified plan table not found
To resolve this error create the PLAN_TABLE in APPS schema

2.ORA-00904: "PARTITION_STOP": invalid identifier.
This indicates that the plan table was not properly created properly. To create the plan table execute the following query from the specified path
$ORACLE_HOME/rdbms/admin/utlxplan.sql

Use this code to create PLAN_TABLE if nothing works

CREATE TABLE PLAN_TABLE
(
  STATEMENT_ID       VARCHAR2(30 BYTE),
  PLAN_ID            NUMBER,
  TIMESTAMP          DATE,
  REMARKS            VARCHAR2(4000 BYTE),
  OPERATION          VARCHAR2(30 BYTE),
  OPTIONS            VARCHAR2(255 BYTE),
  OBJECT_NODE        VARCHAR2(128 BYTE),
  OBJECT_OWNER       VARCHAR2(30 BYTE),
  OBJECT_NAME        VARCHAR2(30 BYTE),
  OBJECT_ALIAS       VARCHAR2(65 BYTE),
  OBJECT_INSTANCE    INTEGER,
  OBJECT_TYPE        VARCHAR2(30 BYTE),
  OPTIMIZER          VARCHAR2(255 BYTE),
  SEARCH_COLUMNS     NUMBER,
  ID                 INTEGER,
  PARENT_ID          INTEGER,
  DEPTH              INTEGER,
  POSITION           INTEGER,
  COST               INTEGER,
  CARDINALITY        INTEGER,
  BYTES              INTEGER,
  OTHER_TAG          VARCHAR2(255 BYTE),
  PARTITION_START    VARCHAR2(255 BYTE),
  PARTITION_STOP     VARCHAR2(255 BYTE),
  PARTITION_ID       INTEGER,
  OTHER              LONG,
  DISTRIBUTION       VARCHAR2(30 BYTE),
  CPU_COST           INTEGER,
  IO_COST            INTEGER,
  TEMP_SPACE         INTEGER,
  ACCESS_PREDICATES  VARCHAR2(4000 BYTE),
  FILTER_PREDICATES  VARCHAR2(4000 BYTE),
  PROJECTION         VARCHAR2(4000 BYTE),
  TIME               INTEGER,
  QBLOCK_NAME        VARCHAR2(30 BYTE),
  OTHER_XML          CLOB
)

Basic UNIX commands

# ls - This command is used to see list of files in the directory and its subdirectories
# ls -all - This is used to see the hidden files and file sizes in a directory

diff - This command is ued to know the difference between two files