Monday, March 22, 2010

Sales Order Line Status Flow and Meaning


Below are some of the different statuses of Sales Order Line with brief explanation

OM = Order Management Sales order form
SE = Shipping Transactions or execution form

1)
 Entered (OM): Order is saved but not booked

2)
 Booked (OM): Order is Booked.

3)
 Awaiting Shipping (OM): Order is booked but lines are not yet picked.
Navigating to Shipping Execution, the delivery line status flow is:

4)
 Not Ready to Release (SE): A delivery line may be in this status when it is interfaced manually into Shipping, is not scheduled and has no reservations. When lines are imported automatically from Order Management this status is not used

5)
 Released to Warehouse (SE): Pick Release has started but not yet completed. One of the reason could be allocation have not been pick confirmed. The Pick Release process creates a Move Order Header & Mover Order Line in Inventory. This is a common status for users that perform a two-step pick release process. This status indicates that inventory allocation has occurred however pick conformation has not yet taken place.

6)
 Ready to Release (SE): Order Line is booked and passed to shipping execution. The line is now eligible to pick Release.

7)
 Backordered(SE): The status of Backorderd is assigned to a line under the following circumstances.
·         The Pick Release process attempted to allocate inventory to the line and all or a partial quantity of the item was not available. In this case the system automatically backorders the discrepant quantity.
·         At Ship confirm the user enters a shipped quantity for an item that is less than the original requested quantity.
·         The user manually Backorders the entire delivery.
8) Shipped (SE): The delivery line is shipped confirmed.

9)
 Confirmed (SE): The delivery line is shipped or backordered and the trip stops are open.

10)
 Picked (OM): Pick release is complete, both allocations and pick confirm

11)
 Picked Partial (OM): This status occurs when a delivery line is not allocated the full quantity during Pick Release and Ship Confirm has not occurred

12)
 Interfaced (SE): The delivery line is shipped and Inventory interface concurrent process is complete.

13)
 Awaiting Fulfillment (OM): When fulfillment set is used, Not all shippable lines in a fulfillment set or a
configuration are fulfilled

14)
 Fulfilled (OM): All lines in a fulfillment set are fulfilled.

15)
 Interfaced to Receivables (OM): The order is linked with Receivables and the invoice is created.

16)
 Partially Interfaced to Receivables (OM): This status is used in a PTO flow and indicates that the particular PTO item is required for revenue.

17)
 Closed (OM): Closed indicates that the line is closed.

18)
 Canceled (OM): Indicates that the line has been completely canceled. No further processing will occur for this line.

Thanks & Regards,
Anto Joe Natesh

Insert BLOB image file in oracle database table

Here we will discuss how to insert BLOB file in the database. For this we will create a table and then a procedure that will be used to insert records in the table.

Use following script to create an employee table

CREATE TABLE SV_EMP_PHOTO
(
  ID          NUMBER(3) NOT NULL,
  PHOTO_NAME  VARCHAR2(40),
  PHOTO_RAW   BLOB,
  EMP_NAME    VARCHAR2(80)
)


Create a directory where the photos will be stored. I am creating a directory in UNIX as our database is created in UNIX.

Create directory SV_PHOTO_DIR as '/u002/app/applmgr/empphoto'


Script to create a procedure SV_LOAD_IMAGE that will insert records in the table.


CREATE OR REPLACE PROCEDURE sv_load_image (
   p_id                NUMBER
 , p_emp_name     IN   VARCHAR2
 , p_photo_name   IN   VARCHAR2
)
IS
   l_source   BFILE;
   l_dest     BLOB;
   l_length   BINARY_INTEGER;
BEGIN
   l_source := BFILENAME ('SV_PHOTO_DIR', p_photo_name);

   INSERT INTO sv_emp_photo
               (ID
              , photo_name
              , emp_name
              , photo_raw
               )
   VALUES      (p_id
              , p_photo_name
              , p_emp_name
              , EMPTY_BLOB ()
               )
   RETURNING   photo_raw
   INTO        l_dest;

   -- lock record
   SELECT     photo_raw
   INTO       l_dest
   FROM       sv_emp_photo
   WHERE      ID = p_id AND photo_name = p_photo_name
   FOR UPDATE;

   -- open the file
   DBMS_LOB.fileopen (l_source, DBMS_LOB.file_readonly);
   -- get length
   l_length := DBMS_LOB.getlength (l_source);
   -- read the file and store in the destination
   DBMS_LOB.loadfromfile (l_dest, l_source, l_length);

   -- update the blob field with destination
   UPDATE sv_emp_photo
   SET photo_raw = l_dest
   WHERE  ID = p_id AND photo_name = p_photo_name;

   -- close file
   DBMS_LOB.fileclose (l_source);
END --sv_load_image;
/


I have copied few .jpg images in /u002/app/applmgr/empphoto in UNIX.
Execute the procedure as follows to create record in database


exec sv_load_image(1,'Pavki','one.jpg')
exec sv_load_image(2,'Suresh','two.jpg')
exec sv_load_image(3,'Rachna','three.jpg')

Wednesday, March 17, 2010

List of Tables for Forms Personalization



Insert BLOB image file in oracle database table
Below is the list of tables that are populated when any Forms Personalization is done

FND_FORM_CUSTOM_RULES
FND_FORM_CUSTOM_SCOPES
FND_FORM_CUSTOM_ACTIONS
FND_FORM_CUSTOM_PARAMS
FND_FORM_CUSTOM_PROP_VALUES
FND_FORM_CUSTOM_PROP_LIST

You can FNDLOAD utility to move one instance to another.

Or export and import this records from one instance to other. But make sure the sequences are altered :) 

Set Profile Option Value using PL/SQL

On request here is how to set the profile option value using PL/SQL

Function FND_PROFILE.SAVE can be used to set the value of any profile option at any level i.e. Site, Application, Responsibility, User etc.

Below is a sample code of how to use this function

DECLARE
   a   BOOLEAN;
BEGIN
   a := fnd_profile.SAVE ('CONC_REPORT_ACCESS_LEVEL'
                        , 'R'
                        , 'USER'
                        , '22746'
                        , NULL
                        , NULL
                         );

   IF a
   THEN
      DBMS_OUTPUT.put_line ('Success');
      COMMIT;
   ELSE
      DBMS_OUTPUT.put_line ('Error');
   END IF;
END;

Display Negative Number in angle brackets

We often wonder and write all the codes to display negative numbers in brackets. But this can be easily achieved using a TO_CHAR function with the correct format.

TO_CHAR(number, '9999PR') -
Returns negative value in angle brackets.
Returns positive value with a leading and trailing blank.
Restriction: The PR format element can appear only in the last position of a number format model.

Below is an example to achieve this
select to_char(-133133,'99999999999PR') from dual

In above example the number is displayed in brackets without comma seperator.

Following query can be used to display negative numbers in angular bracket with comma seperator.

select to_char(-133133,'999G999G990D99PR') from dual

Tuesday, March 16, 2010

PL/SQL Wrap Utility

The PL/SQL Wrap Utility - Hide source code in Oracle

A company has a code(Package, Procedure, Function etc) with all the proprietary information and logic in it. If this information is leaked out in the market then the competitors can take advantage of it and this can affect the business. One of the way to deal with this is to hide the code from others.
This can be achieved using oracle's WRAP utility. The advantage of WRAP utility is that this converts the source code into some language that is not understood but the code can still be compiled like any other source code.
Using Wrap is very simple. In the bin directory of Oracle Home, the wrap utility is installed. The file name could be WRAP.exe or WRAP80.exe depending on the oracle version installed.
Syntax

 C:\orant\BIN>wrap.exe iname=[inputfilename] oname=[outputfilename]


e.g.
 C:\orant\BIN>wrap.exe iname=wrap_test.sql oname=wrap_test.plb

An example of using WRAP

Create a sample procedure wrap_test using following code

CREATE OR REPLACE PROCEDURE wrap_test
IS
BEGIN
 dbms_output.put_line('Wrap test complete');
END;
/
then call the wrap utility using following
wrap.exe iname=wrap_test.sql oname=wrap_test.plb

Content of new file wrap_test.plb
CREATE OR REPLACE PROCEDURE wrap_test wrapped
a000000
b2
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
abcd
7
4f 8d
LPjE3qKQyH/yQRCK4+efvSyST50wg5nnm7+fMr2ywFznKMB04yhSssvum3SLwMAy/tKGBvVS
m7JK/iiyveeysx0GMCyuJOqygaVR2+EC8XcG0wJd5GisbnfxwzIu9tHqJB/2OabWTW+0
/

It is very clear from this that the new code is not readable and so is completely hidden from others.
Drop your procedure(if already created) and recreate using the the new file wrap_test.plb which can be compiled as any other package. Important point here is that the source code will be hidden and cannot be read.
Another important point to remember is that once wrapped, a code cannot be unwrapped.

Thanks please let me know your feedback.

Thanks & Regards,
Anto Joe Natesh

Password Policy and profile options

On apps there are a lot of profile options that are useful in making apps passwords difficult to guess, the profiles are

1-Signon password failure limit
2-Signon Password Length
3-Signon Password No Reuse
4-Signon Password Hard to Guess

For the first one it means how many time can I try to access the system using wrong password. It is recommended to change this value to 3. The default value is null.

The second one to allow minimum password length. The default value is 5, it is recommended to make it 6 or 7.

The 3rd profile is for not allowing using same password again for specified number of days.

The default value for 4th profile option is No. Following are the password rules if the value is set to Yes
1) The password contains at least one letter and at least one number.
2) The password does not contain the username.
3) The password does not contain consecutively repeating characters.

Reference: Metalink Note 362663.1

Thanks & Regards,
Anto Joe Natesh I

Create Trace File for Concurrent Program

If your program is taking time to complete, then the best way to know what is causing the problem is by creating a trace file.

Navigation:
System Administrator(R) --> Concurrent --> Program --> Define

Query for the concurrent program and check enable trace button.

Now when the concurrent program is executed the trace file is created in the udump directory. The path of udump directory can be found by executing following query.
select * from v$parameter
where name like '%user_dump_dest%'


The trace file can be converted to a readable format by running a tkprof command over the trace file.
Syntax:

tkprof [trace_file_name] [new_file_name]

Understanding TKPROF
The TKPROF program can be used to format the contents of the trace file and convert it into a readable output file.
TKPROF can also be used to generate Explain Plan for the queries.
I will create a seperate post to discuss various options available with TKPROF.

Thanks & Regards,
Anto Joe Natesh I

Secure FTP the files from unix to windows

Securing FTP in shell scripts using .netrc
Often we use FTP in the shell scripts and for security reason it is advisable not to store username and password in the shell scripts.

Here I discuss how can we secure the FTP process and restrict sharing of username and passwords.

File .netrc in the $HOME directory allows file transfers in batch mode. This file stores the machine name, login and passwords. The FTP commands gets information from the file and connects to the FTP server.

Each record has the format:

machine machine_name login login_name password passwd
where machine_name, login_name, passwd refer to a system name with the login and password for that account on the machine
e.g machine xyz.server.com login anonymous password xyz123

There are following 2 ways to execute the FTP commands

1) Create a command file and store all the FTP commands in the file.
e.g. File command_ftp is created and saved. The file has following content

bin
cd /inbound
get abcd.txt
bye 

Write following code in the shell script

ftp xyz.server.com < command_ftp 

2) The FTP commands can also be stored in .netrc file as a macro and the commands will be executed with the FTP command.
The .netrc file content would be something like this

machine xyz.server.com login anonymous password xyz123
macdef bin
cd /inbound
get abcd.txt
bye 

For this case the shell script will have following ftp command
e.g.
ftp xyz.server.com

The other way of securing FTP is by using the sftp(secure FTP) commands which is a network protocol that provides file transfer and transfers file in a secure way.

Thanks & Regards,
Anto Joe Natesh I