Move Order Transfer vs Sub-Inventory transfer

The difference between move order transfer and subinventory transfer is
In move orders, you can have an approval so you are not simply "taking" material from a subinventory that is under someone else's control.

Also, move orders create allocations. So you can place hold on the material with the intention of picking it up a little later. In subinventory transfer, there is no reservation / allocation. So until you actually pick up the material and perform the transaction, someone else can claim the material.

Script to transfer a file from one UNIX box to another

HOST = '192.168.100.2'
USER = 'username'
PASSWD = 'password'
FILE = 'myfile.log'

ftp -n $HOST   <<  END_SCRIPT
quote USER $USER
quote PASS $PASSWD
get $FILE
quit
END_SCRIPT
exit 0

Script to check a directory and file

if [ -d /home/r12oravis/test ]
then
echo "Directory /home/r12oravis/test is available"
cd //home/r12oravis/test
if [ -f aaa.prg ]
then
  echo "File exists"
  if [ -r aaa.prg ]
  then
     echo "File readable"
     if [ -s aaa.prg ]
     then
        echo "File is not empty"
     else
        echo "File is empty"
     fi
  else
     echo "File is not readable"
  fi
else
  echo "File doesn't exists"
fi
else
echo "Directory doesn't exists"
fi

Running a simple Shell Script as a HOST program

There are basically 12 types of executable available in Oracle applications. They are
(Concurrent -> Program -> Executable)

1. Oracle Reports
2. Host
3. Immediate
4. PL/SQL Stored Procedure
5. SQL * Loader
6. SQL * Plus
7. Spawned
8. Java Concurrent Program
9. Java Stored Procedure
10. Perl Concurrent Program
11. Request Set Stage Function
12. Multi Language Function

We are going to see about HOST type of executable. Its generally used to run a Shell Script.

What is meant by Shell Script?
Shell Script is a collection of shell commands(UNIX commands) which will be executed sequentially. Using a shell script you can create a directory, move files, check the file availability, run a SQL command, etc....

Example for a shell script:
echo "This is a sample script run by bala kaarthik"
echo $1
echo $2
echo $3
echo $4
exit 0

In the above script the first line used to print a string. '
Note: I have used the word 'shell' many times. What is a shell actually? Its nothing but a compiler which is used to run a command. UNIX have different type of compilers(shells) like bash shell, C shell, etc... For more details on shell refer http://www.wikipedia.org/


How to register a HOST program?

1. Create a Shell program (VBBKSH.prog)
Use the above script and save it as VBBKSH.prog (case sensitive)

2.Move the shell program to the application top bin directory.
Find your application top XX_TOP. In that you can find bin directory ( if not create one and give 777 permission (read write executable for all) ). Move the file to that directory and give 777 permission to the file

3. Create a soft link
Its essential to create a soft link for your shell program. To create a soft link use the following shell command
ln -s $FND_TOP/bin/fndcpesr $XX_TOP/bin/VBBKSH

Where XX_TOP is your application top. Don't include the file extension (.prog) while creating the soft link. After executing the shell command a soft link will be created in the name of the shell program without any file extension.

4. Create Executable
Change your responsibility to "System Administrator" or "Application Developer"
Create a executable with "Execution method" as HOST and "Execution file name" as VBBKSH.
Don't forget to give the correct custom application name.

5.Create a Concurrent Program
Create the concurrent program and give the executable name which was created in the previous step

Parameters:
Regarding the parameters basically 4 parameters will be sent to the shell program by default. They are
$1 - database username/password
$2 - loginid
$3 - application username
$4 - concurrent request ID

If you want to send some parameters to the shell program that will be used as $5,$6,etc.... inside the shell program.


6.Register the concurrent program to the custom application's responsibility

7. Change to your application responsibility and run the concurrent program

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

Important Tables

Table Name : ALL_TAB_COLUMNS
This table is used to get the column details of the given table

select column_name,data_type,data_length,nullable from ALL_TAB_COLUMNS where TABLE_NAME='OE_ORDER_HEADERS_ALL'


Table Name : ALL_IND_COLUMNS
This table is used to fetch the indexes and the columns to which the index was applied.

select index_name,column_name from all_ind_columns where TABLE_NAME='OE_ORDER_HEADERS_ALL'



.

Distinct Values in Value Set

To get distinct values in a valueset (without creating a view), the following steps can be followed

In the place of table name use the following
(select distinct column_name from table_name) a

where 'a' is the alias nameIn the value field give
a.column_name

Some Functions in Report Builder

To Print in log file

SRW.MESSAGE(1,'String to be printed in log file');

Here first parameter indicates the log file(1).
Second parameter is the string that to be printed in log file.

To Abort from the report

RAISE SRW.program_abort;

Use of Analytical Function

Requirement : I need list of items purchased in a month. If a item purchased more than once in a month then i need the recent purchase order number.

Solution :

SELECT *
  FROM (SELECT segment1, description, ponumber,
               ROW_NUMBER () OVER (PARTITION BY segment1 ORDER BY pocreationdate DESC) mycol
          FROM base_table
         WHERE TO_CHAR (pocreationdate, 'MM YYYY') = '04 2008')
         WHERE mycol = 1

DAY Validation

Hope you all know about SQL functions 'to_char' , 'trim' and 'length'

'to_char' used to convert a date or number type to a character type
'trim' mostly used to delete white spaces in a given string.(we can also trim character from a string . check the SQL oracle user guide)
'length' is used to get the length(no. of characters) of the given string

Please check the following queries to understand well about this

Assume today is MONDAY
select to_char(sysdate,'DAY') from dual;

will return MONDAY. we all know that

select length(to_char(sysdate,'DAY')) from dual;

should return 6. but it will return 9. the reason behind this is to_char(sysdate,'DAY') always return a string with 9 characters. so the MONDAY will have three white spaces at the end.

why its returning always the length of 9?
the answer is the maximum character a DAY can hold is 9(WEDNESDAY). so it always pad white spaces for the remaining length

to get the correct length use the following query

select length(trim(to_char(sysdate,'DAY'))) from dual;

Hope you can use these query in day validation.

Assume today is MONDAY
The following query will return 'Today is not Monday'.

set serveroutput on;
begin
if to_char(sysdate,'DAY')='MONDAY' then
dbms_output.put_line('Today is Monday');
else
dbms_output.put_line('Today is not Monday');
end if;
end;
/

To get the correct result ('Today is Monday') use the following query

set serveroutput on;
begin
if trim(to_char(sysdate,'DAY'))='MONDAY' then
dbms_output.put_line('Today is Monday');
else
dbms_output.put_line('Today is not Monday');
end if;
end;
/

Delete duplicate rows in a table

To delete the duplicate rows from a table.

Method 1 :

create table newtable as (select distinct * from oldtable);

drop table oldtable;

rename newtable to oldtable;

Method 2:

DELETE FROM mytable t1
      WHERE ROWID < (SELECT MAX (ROWID)
                       FROM mytable t2
                      WHERE t1.col1 = t2.col1 AND t1.col2 = t2.col2)