SQL Loader (SQLLDR) in Oracle Applications

One of the executable type in Oracle applications is SQL*Loader. In this post we will discuss more about how to define, create and use this type of Execution Method.

Execution Method SQL*Loader is used to load data into database table using SQL*Loader. Below are the steps that needs to completed
1) Create Control File and copy it in the TOP/bin directory. E.g. if your custom application top directory is $XX_TOP, then the file should be copied in $XX_TOP/bin directory

OPTIONS (SKIP=1)
LOAD DATA infile '%1'
APPEND INTO TABLE sv_test_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
(
   item_number     "trim(:item_number)"
 , description     "trim(:description)"
 , attribute1      "trim(:attribute1)"
 , process_flag    Constant 'UNPROCESSED'
)

2) Create an executable with execution method as SQL*Loader and use the control file Name as Execution file Name. The Application should same as where the file is copied.

3) Create a concurrent program definition and use the executable created above.
It is optional to have any parameters to the program, but in this case the data file name should be same as control file name except the extension for the data file being .dat
If you notice in the control file definition above I have used %1 as the infile name. This is how parameters are referenced in control file.
So now we can create conc. program with a file name as a parameter(Full file name with path should be entered and this is case sensitive.)

4) Assign this program to the request group and it is ready to be used.

Error in RCV Transactions Interface (Receiving Transaction Processor)

When trying to use RCV interfaces and ran Receiving transaction Processor import program, the transaction errored out with message "The parameters passed to procedure populate_cost_details are invalid."

In the documentation, the column name LAST_UPDATE_LOGIN is noted as "optional".
However, the columns user_id and login_id in package
inv_wwacst.populate_cost_details are selected from
mtl_material_transactions_temp table in inltpu, which is populated by the
Receiving Interface Manager. These columns are expected to be NOT null and
will error if they are null. The solution is to populate the LAST_UPDATE_LOGIN
with a value (type is Number. Even though Purchasing does not require this
field to be populated, this is required in Inventory when the items are
delivered.

Refer Metalink Note: 99533.1

Keywords: Receiving Open Interface, RVCTP, PO Receipts, populate_cost_details

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.

Reference: Oracle Metalink

SQL Loader limit number of rows

Based on a query from one of our reader here is an example to illustrate how to limit number of rows to be loaded in SQL Loader. This can be done using option LOAD

OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
    item_number     "trim(:item_number)"
  , vendor_name "trim(:vendor_name)"
  , vendor_site_name "trim(:vendor_site_name)"
  , supplier_item   "trim(:supplier_item)"
  , process_flag  Constant 'UNPROCESSED'
)

In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.

These options can also be given with sqlldr command as follows

sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5  skip=1

Keywords: SQL*LOADER, ERRORS, SKIP, LOAD

Query to Link between Order Management and Account Receivables

On request I continue to write further on Post Relation between AR Invoice and Sales Order tables and based on the standard assumption, provide a query that can be used to link Invoice and Sales Order.

Query below can be handy

SELECT ooha.order_number
     , oola.line_number so_line_number
     , oola.ordered_item
     , oola.ordered_quantity * oola.unit_selling_price so_extended_price
     , rcta.trx_number invoice_number
     , rcta.trx_date
     , rctla.line_number inv_line_number
     , rctla.unit_selling_price inv_unit_selling_price
FROM   oe_order_headers_all ooha
     , oe_order_lines_all oola
     , ra_customer_trx_all rcta
     , ra_customer_trx_lines_all rctla
WHERE  ooha.header_id = oola.header_id
AND    rcta.customer_trx_id = rctla.customer_trx_id
AND    rctla.interface_line_attribute6 = TO_CHAR (oola.line_id)
AND    rctla.interface_line_attribute1 = TO_CHAR (ooha.order_number)
AND    order_number = :p_order_number

Keywords: Sales Order, AR, OM

Query to find locked objects in Oracle

Sometimes program takes a little longer time then expected, one of the reason to this could be that the table/object you are trying to manipulate is locked by other program and hence it is waiting for the resource to be released.
Below query can be handy to find the objects that are locked

SELECT   c.owner
       , c.object_name
       , c.object_type
       , b.SID
       , b.serial#
       , b.status
       , b.osuser
       , b.machine
       , b.program
       , b.module
       , b.action
FROM     v$locked_object a
       , v$session b
       , dba_objects c
WHERE    b.SID = a.session_id 
AND a.object_id = c.object_id
ORDER BY module

If you want to forcefully kill any session then it can be done using

alter system kill session 'sid,serial#'

e.g. 
altery system kill session '123,5325'

Restart or Bounce Apache Server

Following command can be used to bounce apache. An apache bounce is needed to reflect any changes in the self service pages.

To Stop

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh stop

To Start

$COMMON_TOP/admin/scripts/$TWO_TASK*/adapcctl.sh start

Relation between AR Invoice and Sales Order tables

Many times I have seen a question being asked in the forums about the relationship between AR Invoice and Sales Order.
There are several interface_line_attribute and interface_header_attribute columns in RA_CUSTOMER_TRX_ALL and RA_CUSTOMER_TRX_LINES_ALL table respectively which are used to map it with other modules.
The relationship is actually mapped using the descriptive flexfield.

Query for following
Application: Receivables
Title: Line Transaction Flexfield

and then in the context field is the listed different modules.

For Sales order search for Order Management or Order Entry and click on segments to see how they are mapped. Screenshots below

 

Click on the segments button to see column mappings.

 

The above method can also be used to find mapping of Receivables with other modules like Oracle Projects, Services, Contracts etc.

P.S. Please click on the image to zoom it.

Command/Code to get ASCII value in UNIX

Found a method to ASCII to character and vice versa in unix and thought of sharing it.
Below is the code.

## Character to ASCII
printf "%d\n" "'A"

## ASCII number to character
awk -v char=65 'BEGIN { printf "%c\n", char; exit }'

Find Table and Column Name with table and column description

Many times we refer to TRM to get the description or know more about the columns in a table. The query below can be handy to know which tables use the column and get a small description about the table and the column.
Note if there is a custom table created and not registered in oracle apps then those tables and columns will not be listed by the query below.

SELECT fa.application_id
, fa.application_short_name
, fat.application_name
, table_name
, column_name
, ft.description table_description
, fc.description column_description
FROM   fnd_tables ft
, fnd_columns fc
, fnd_application_tl fat
, fnd_application fa
WHERE  ft.table_id = fc.table_id
AND    fc.column_name = :column_name
AND    fat.application_id = ft.application_id
AND    fat.LANGUAGE = USERENV ('LANG')
AND    fa.application_id = fat.application_id

Print Image using Reports 6i using the file path

To add further to my earlier post Display image using Reports 6i, here we will discuss how to display image when file path is stored in the table. In our example the file directory is a constant directory and only the file name is stored in the table.
The person requested this post had following problem
1) Need to store image in one of the directory and display that using Reports 6i
2) If file does not exists in the directory, the report should complete normal and do not display any image
3) Psuedo code
If file exists then
display image
else
display blank (no image)
end if;

Most of the steps discussed in earlier post are same with few changes as follows
Create a new formula column say CF_URL and write following code in the format trigger

function CF_URLFormula return Char is
 v_handle  utl_file.file_type;
 v_file_dir VARCHAR2(60) := '/u002/app/applmgr/temp/';
begin
-- The UTL_FILE is used to check if the file exists in the directory.
-- If it is unable to open the file then an exception will thrown.
-- We will catch the exception and return null as the final URL.
-- Without this exception for any invalid path, the report will error out 
-- and not work as expected.
 v_handle := utl_file.fopen(v_file_dir, :photo_name, 'R');
 utl_file.fclose(v_handle);
 RETURN v_file_dir||:photo_name;
exception
 when others then
 srw.message(100,SQLERRM);
 return null;
end;

Another change is in the layout. Screenshot below


Related Post:
Display image using Reports 6i
Insert BLOB Image file into Oracle Database Table

Display images in Reports 6i

On request from one of our reader, Below is the post on how to display and dynamically change image in reports 6i.
Please refer to my post Insert BLOB image file into oracle database table where I have discussed the steps on how to insert a record into BLOB table.
We will use same SV_EMP_TABLE as a reference and using Reports 6i create a report that prints ID Card. For simplicity there are no parameters to the report and it will create ID cards for all the employees for which record exists in the table.
In the data model create following SQL Query

SELECT ID, PHOTO_NAME, PHOTO_RAW ,emp_name
FROM SV_EMP_PHOTO

The next step is to create a layout for the report. Screenshot below


Complete necessary concurrent program setup to run this report from Oracle Apps. Below is the screenshot of the report output.


Related Post:
Insert BLOB Image file into Oracle Database Table

P.S. Click on the image to zoom it.

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')

Following is how data is stored in the database

NVL2 Function in Oracle

In Oracle, the NVL2 function extends the functionality found in the NVL function. It can be used to substitute a value when a null value is encountered as well as when a non-null value is encountered.

The syntax for the NVL2 function is:

    NVL2( string1, value_if_NOT_null, value_if_null )
string1 is the string to test for a null value.
value_if_NOT_null is the value returned if string1 is not null.
value_if_null is the value returned if string1 is null.

Oracle Report 6i closes with Error when trying to compile some reports

Sometimes when we try to compile a standard (complicated) oracle reports using Reports 6i, the report builder closes with following error message
Reports Builder has encountered a problem and needs to close. We are sorry for the inconvenience.


One of the fix is to check the version of oracle report and apply a patch and get Reports/Forms to the latest version.

Download patch from metalink

Set use of VI Commands at the UNIX prompt

VI commands can be used at the UNIX prompt to retrieve history commands or modify already typed command using VI editor keys. This can be done by executing following command

set -o vi

One of the option is to enter and save this command in .profile file, so that everytime we login in UNIX this command is executed.

Enable Created By Field in the Find Move Order Screen

To implement the solution, execute the following steps to enable the INV_INVTOMAI_CREATOR function:

A. Define the INV_INVTOMAI_CREATOR function if it does not already exist:

1. Navigate: System Administrator > Application > Function
2. Enter Function Name: INV_INVTOMAI_CREATOR
3. Enter User Function Name: 'Move Orders Creator'
4. Under the Properties tab, enter Type: Subfunction
5. Enter Maintenance Mode support: None
6. Enter Context Dependence: Responsibility
7. Save

B. Assign the function to the Menu you are using:

1. Navigate System Administrator > Application > Menu
2. Query for menu 'INV_MOVE_ORDER'
3. Add a new line to the menu:
Prompt: 'Move Order Creator'
Function: select 'Move Orders Creator' from the LOV
Description: 'Move Orders Creator'
4. Save

The Created By field in the Move Order find form should now be enabled and modifiable. You may need to sign out and back in for the change to take affect.

Reference: Metalink Note: 280131.1

Retrieve IP Address and Host Name usingPL/SQL

On request from one of our reader.
Using the UTL_INADDR package, a PL/SQL subprogram can determine the host name of the local system or the IP address of a given host name.

E.g. Retrieve the local host name and IP address.

SET serveroutput on
BEGIN
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME);  -- get local host name
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS);  -- get local IP addr
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_ADDRESS('www.oracle.com'));
  DBMS_OUTPUT.PUT_LINE(UTL_INADDR.GET_HOST_NAME('141.146.9.91')); 
END;

Reference: www.oracle.com

Changing the color of an Item in Forms using Forms Personalization

On request here are the steps to change the Font colors of an item using Forms Personalization.
I am using Item Screen for an example and displaying Item number in Red Color.
This is how form looks before customization. Note that Item number is displayed in its default color.

Now lets change the color. Select Help->Diagnostics->Custom Code->Personalize to open the forms personalization screen.
At this point of time there is no condition specified and the Item number is displayed in red in all the cases. So the trigger is written in WHEN-NEW_FORMS-INSTANCE

Click on Actions tab and enter as shown in the screen shot

Save your changes and re-open and query for an item


Note the change in the font color of Item Number.

The Foreground color used in above example is r255g0b0, which can be explained as follows

Any Color is made using combination of Red(r), Green(g) and Blue(b) color with a value ranging from 0 to 255. To create a red color give the value of r to maximum 255 and leave g and b to zero.
Similarly green color can be defined as r0g255b0

P.S. Click on the image to zoom it.

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;

Profile Option to View Output of Request submitted by other User

Set the value of profile Concurrent:Report Access Level to Responsibility.
The output submitted by other users can now be viewed from the responsibility through which the request was submitted.
The other option available is User,which restricts the output only to the user who submitted the request.

Load multiple user datafiles into multiple tables using SQL*LOADER

On request from one of the reader, below are the steps to load multiple tables using multiple datafiles.
Problem Description
-------------------
You have multiple datafiles to be loaded using SQL*Loader. Each of the data
files contains records that must be loaded into one of several tables. The
following is an example that uses the INFILE clause for each datafile and a
WHEN clause for each table to do this.

Records in a datafile that are to be loaded into multiple table can be loaded
based on a check for a value that distinguishes each record. This check can be
on an entire column or on a specific position within the data file.

Solution Description
--------------------
Example:
Datafiles are generated each day that contain multiple employees and the
projects those employees worked on that day. Projects can be worked on from
many locations, and an employee can work on many projects each day. Each week
all the daily files are gathered and loaded into project tables based on
location.
-----------------------Table Create Statements---------------
CREATE TABLE DENVER_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE ORLANDO_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

CREATE TABLE MISC_PRJ
( PROJNO CHAR(3),
EMPNO NUMBER(5),
PROJHRS NUMBER(2) );

-------------------Control File - MFILES.CTL------------------
LOAD DATA
INFILE '/u01/projs/denver.dat'
INFILE '/u01/projs/orlando.dat'
APPEND

INTO TABLE DENVER_PRJ
WHEN PROJNO = '101'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE ORLANDO_PRJ
WHEN PROJNO = '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )

INTO TABLE MISC_PRJ
WHEN PROJNO != '101' AND PROJNO != '202'
( PROJNO POSITION(1:3) CHAR,
EMPNO POSITION(4:8) INTEGER EXTERNAL,
PROJHRS POSITION(9:10) INTEGER EXTERNAL )
--------------------Datafiles: DENVER.DAT---------------------
1011234515
1015432140
1012345620
3032345610
--------------------Datafiles: ORLANDO.DAT--------------------
2021234515
2022345610
4041234510
--------------------------------------------------------------
SQL*Loader will read all the input files together. It will parse each record
and then based on the condition(s) in a WHEN clause will evaluate whether the
record can be loaded into that table. Care should be taken as to how WHEN
conditions are constructed because each record is evaluated against every WHEN
clause and loaded into all tables that match the condition.

Reference: Metalink Note: 1023792.6

Discoverer Query to Display Document Name, Folder, Business Unit etc.

Following query can be used to display Document Name, Document Key, Folder Name, Folder Key, Documents Sheets, Business Unit, Document Created By in discoverer.
Please note that the query is in crude format and not tested, any suggestion to improve this is appreciated.

 SELECT DISTINCT b.doc_developer_key Document_developer_key
              , b.doc_name document_name
              , a.qs_doc_details worksheet_name
              , c.obj_name folder_name
              , c.obj_developer_key folder_developer_key
              , d.ba_name Business_unit
              , fu.user_name created_by
 FROM           eul4_us.eul4_qpp_stats a
              , eul4_us.eul4_documents b
              , eul4_us.eul4_objs c
              , eul4_us.eul4_bas d
              , eul4_us.eul4_eul_users e
              , eul4_us.eul4_ba_obj_links f
              , fnd_user fu
 WHERE          a.qs_doc_name = b.doc_name
 AND            a.qs_doc_owner = e.eu_username
 AND            b.doc_eu_id = e.eu_id
 AND            c.obj_id = SUBSTR (a.qs_object_use_key, 1, 6)
 AND            c.obj_id = f.bol_obj_id
 AND            d.ba_id = f.bol_ba_id
 AND            TO_CHAR (fu.user_id) = SUBSTR (b.doc_updated_by, 2)

Using 3 of 9 Barcodes in XML Publisher

I am using Code 39(3 of 9) Barcode font for my report. Every thing seems to be fine and barcode seems to be displayed correctly, but not recognized by the barcode reader/gun?
          For code 39 barcode fonts asterisk(*) is used as a start and end delimeter. So any text which is displayed as barcode should have * at the start and end.

E.g. 12345 should be displayed as *12345*

How to query/find discoverer reports in Oracle using APPS Schema

The table to store discoverer is stored in the EUL Schema. For e.g. if the name of EUL is EUL4_US then a schema with that name will be created in oracle database. Run following query to know the name of table

 SELECT owner,table_name
 FROM   all_tables
 WHERE  owner = 'EUL4_US' AND table_name LIKE '%DOCUMENT%'

The above query in this case will list table_name EUL4_DOCUMENTS

All the discoverer report names are stored in this table, so

SELECT * FROM EUL4_US.EUL4_DOCUMENTS

should display name, owner etc of discoverer reports

Preserve Timestamp, mode, owner while using copy command in Unix

In Unix, Sometimes when we need to take a backup of the file we generally add timestamp along with the file and create a backup, however the new file created has the creation date as system date.
This can be avoided using preserve option in unix.
For e.g. I will copy ARXSGPO.rdf from $AR_TOP/reports/US directory. The file signature looks like

-rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO.rdf

Now the following files are created using preserve and no preserve option

 cp ARXSGPO.rdf ARXSGPO_np.rdf   # without preserve option
 cp -p ARXSGPO.rdf ARXSGPO_p.rdf # with preserve option

This is how the signature of file looks like

 -rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO.rdf
 -rw-r--r--  1 applmgr oracle 647168 Apr 21 13:31 ARXSGPO_p.rdf
 -rw-r--r--  1 applmgr oracle 647168 May  3 23:43 ARXSGPO_np.rdf

It can be noted that ARXSGPO_np.rdf has timestamp as May 3, whereas file ARXSGPO_p.rdf created using preserve mode has timestamp as Apr 21. So using preserve option we can save the original information of the file from where the file is copied.

Sales Order Import using API

Please note that this is just the sample code and should be used only for reference.
The code below creates a sales order in entered status with one line record. Also the price adjustment is done for the line.

DECLARE
l_api_version_number           NUMBER  := 1;
l_return_status                VARCHAR2 (2000);
l_msg_count                    NUMBER;
l_msg_data                     VARCHAR2 (2000);
/*****************PARAMETERS****************************************************/
l_debug_level                  NUMBER  := 1;    -- OM DEBUG LEVEL (MAX 5)
l_org                          NUMBER  := 5283;         -- OPERATING UNIT
l_no_orders                    NUMBER  := 1;              -- NO OF ORDERS
l_user                         NUMBER  := 28573;          -- USER
l_resp                         NUMBER  := 53073;        -- RESPONSIBLILTY
l_appl                         NUMBER  := 660;        -- ORDER MANAGEMENT
/*****************INPUT VARIABLES FOR PROCESS_ORDER API*************************/
l_header_rec                   oe_order_pub.header_rec_type;
l_line_tbl                     oe_order_pub.line_tbl_type;
l_action_request_tbl           oe_order_pub.request_tbl_type;
l_line_adj_tbl                 oe_order_pub.line_adj_tbl_type;
/*****************OUT VARIABLES FOR PROCESS_ORDER API***************************/
l_header_rec_out               oe_order_pub.header_rec_type;
l_header_val_rec_out           oe_order_pub.header_val_rec_type;
l_header_adj_tbl_out           oe_order_pub.header_adj_tbl_type;
l_header_adj_val_tbl_out       oe_order_pub.header_adj_val_tbl_type;
l_header_price_att_tbl_out     oe_order_pub.header_price_att_tbl_type;
l_header_adj_att_tbl_out       oe_order_pub.header_adj_att_tbl_type;
l_header_adj_assoc_tbl_out     oe_order_pub.header_adj_assoc_tbl_type;
l_header_scredit_tbl_out       oe_order_pub.header_scredit_tbl_type;
l_header_scredit_val_tbl_out   oe_order_pub.header_scredit_val_tbl_type;
l_line_tbl_out                 oe_order_pub.line_tbl_type;
l_line_val_tbl_out             oe_order_pub.line_val_tbl_type;
l_line_adj_tbl_out             oe_order_pub.line_adj_tbl_type;
l_line_adj_val_tbl_out         oe_order_pub.line_adj_val_tbl_type;
l_line_price_att_tbl_out       oe_order_pub.line_price_att_tbl_type;
l_line_adj_att_tbl_out         oe_order_pub.line_adj_att_tbl_type;
l_line_adj_assoc_tbl_out       oe_order_pub.line_adj_assoc_tbl_type;
l_line_scredit_tbl_out         oe_order_pub.line_scredit_tbl_type;
l_line_scredit_val_tbl_out     oe_order_pub.line_scredit_val_tbl_type;
l_lot_serial_tbl_out           oe_order_pub.lot_serial_tbl_type;
l_lot_serial_val_tbl_out       oe_order_pub.lot_serial_val_tbl_type;
l_action_request_tbl_out       oe_order_pub.request_tbl_type;
l_msg_index                    NUMBER;
l_data                         VARCHAR2 (2000);
l_loop_count                   NUMBER;
l_debug_file                   VARCHAR2 (200);
b_return_status                VARCHAR2 (200);
b_msg_count                    NUMBER;
b_msg_data                     VARCHAR2 (2000);
BEGIN
DBMS_APPLICATION_INFO.set_client_info (l_org);
MO_GLOBAL.SET_POLICY_CONTEXT('S',l_org);
mo_global.init('ONT');

/*****************INITIALIZE DEBUG INFO*************************************/
IF (l_debug_level > 0)
THEN
l_debug_file := oe_debug_pub.set_debug_mode ('FILE');
oe_debug_pub.initialize;
oe_debug_pub.setdebuglevel (l_debug_level);
oe_msg_pub.initialize;
END IF;

/*****************INITIALIZE ENVIRONMENT*************************************/
fnd_global.apps_initialize (l_user, l_resp, l_appl);-- pass in user_id, responsibility_id, and application_id
/*****************INITIALIZE HEADER RECORD******************************/
l_header_rec := oe_order_pub.g_miss_header_rec;
/*****************POPULATE REQUIRED ATTRIBUTES **********************************/
l_header_rec.operation := oe_globals.g_opr_create;
l_header_rec.order_type_id := 2159;                                                                          -- domestic return
l_header_rec.sold_to_org_id := 659018;
l_header_rec.ship_to_org_id := 635775;
l_header_rec.invoice_to_org_id := 635776;
l_header_rec.order_source_id := 9;
l_header_rec.booked_flag := 'N';
l_header_rec.price_list_id := 39825;
l_header_rec.pricing_date := SYSDATE;
l_header_rec.transactional_curr_code := 'USD';
l_header_rec.flow_status_code := 'ENTERED';
l_header_rec.cust_po_number := '1211314AFA';


/*****************INITIALIZE ACTION REQUEST RECORD*************************************/
l_action_request_tbl (1) := oe_order_pub.g_miss_request_rec;
/*****************INITIALIZE LINE RECORD********************************/
l_line_tbl (1) := oe_order_pub.g_miss_line_rec;
l_line_tbl (1).operation := oe_globals.g_opr_create;
l_line_tbl (1).inventory_item_id := 826543;
l_line_tbl (1).ordered_quantity := 1;
--l_line_tbl(1).unit_selling_price := 2000; -- The price is done using adjustments
--l_line_tbl(1).unit_list_price := 2000;
l_line_tbl (1).calculate_price_flag := 'Y';
l_line_tbl (1).return_reason_code := 'B2';
--l_line_tbl(1).line_number := 1;
l_line_adj_tbl (1) := oe_order_pub.g_miss_line_adj_rec;
l_line_adj_tbl (1).operation := oe_globals.g_opr_create;
l_line_adj_tbl (1).list_header_id := 148129;
l_line_adj_tbl (1).list_line_id := 651550;
l_line_adj_tbl (1).change_reason_code := 'MANUAL';
l_line_adj_tbl (1).change_reason_text := 'Manually applied adjustments';
l_line_adj_tbl (1).operand := 2000;
l_line_adj_tbl (1).pricing_phase_id := 2;
l_line_adj_tbl (1).updated_flag := 'Y';
l_line_adj_tbl (1).applied_flag := 'Y';
l_line_adj_tbl (1).line_index := 1;

FOR i IN 1 .. l_no_orders   
LOOP                                        
/*****************CALLTO PROCESS ORDER API*********************************/
oe_order_pub.process_order (
p_api_version_number          => l_api_version_number
, p_header_rec                  => l_header_rec
, p_line_tbl                    => l_line_tbl
, p_action_request_tbl          => l_action_request_tbl
, p_line_adj_tbl                => l_line_adj_tbl
-- OUT variables
, x_header_rec                  => l_header_rec_out
, x_header_val_rec              => l_header_val_rec_out
, x_header_adj_tbl              => l_header_adj_tbl_out
, x_header_adj_val_tbl          => l_header_adj_val_tbl_out
, x_header_price_att_tbl        => l_header_price_att_tbl_out
, x_header_adj_att_tbl          => l_header_adj_att_tbl_out
, x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl_out
, x_header_scredit_tbl          => l_header_scredit_tbl_out
, x_header_scredit_val_tbl      => l_header_scredit_val_tbl_out
, x_line_tbl                    => l_line_tbl_out
, x_line_val_tbl                => l_line_val_tbl_out
, x_line_adj_tbl                => l_line_adj_tbl_out
, x_line_adj_val_tbl            => l_line_adj_val_tbl_out
, x_line_price_att_tbl          => l_line_price_att_tbl_out
, x_line_adj_att_tbl            => l_line_adj_att_tbl_out
, x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl_out
, x_line_scredit_tbl            => l_line_scredit_tbl_out
, x_line_scredit_val_tbl        => l_line_scredit_val_tbl_out
, x_lot_serial_tbl              => l_lot_serial_tbl_out
, x_lot_serial_val_tbl          => l_lot_serial_val_tbl_out
, x_action_request_tbl          => l_action_request_tbl_out
, x_return_status               => l_return_status
, x_msg_count                   => l_msg_count
, x_msg_data                    => l_msg_data
);

/*****************CHECK RETURN STATUS***********************************/
IF l_return_status = fnd_api.g_ret_sts_success
THEN
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('success');
END IF;

COMMIT;
ELSE
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('failure');
END IF;

ROLLBACK;
END IF;
END LOOP; -- END LOOP
/*****************DISPLAY RETURN STATUS FLAGS******************************/
IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('process ORDER ret status IS: ' 
|| l_return_status);
DBMS_OUTPUT.put_line ('process ORDER msg data IS: ' 
|| l_msg_data);
DBMS_OUTPUT.put_line ('process ORDER msg COUNT IS: ' 
|| l_msg_count);
DBMS_OUTPUT.put_line ('header.order_number IS: ' 
|| TO_CHAR (l_header_rec_out.order_number));
DBMS_OUTPUT.put_line ('adjustment.return_status IS: ' 
|| l_line_adj_tbl_out (1).return_status);
DBMS_OUTPUT.put_line ('header.header_id IS: ' 
|| l_header_rec_out.header_id);
DBMS_OUTPUT.put_line ('line.unit_selling_price IS: ' 
|| l_line_tbl_out (1).unit_selling_price);
END IF;

/*****************DISPLAY ERROR MSGS*************************************/
IF (l_debug_level > 0)
THEN
FOR i IN 1 .. l_msg_count
LOOP
oe_msg_pub.get (p_msg_index => i, p_encoded => fnd_api.g_false, p_data => l_data, p_msg_index_out => l_msg_index);
DBMS_OUTPUT.put_line ('message is: ' || l_data);
DBMS_OUTPUT.put_line ('message index is: ' || l_msg_index);
END LOOP;
END IF;

IF (l_debug_level > 0)
THEN
DBMS_OUTPUT.put_line ('Debug = ' || oe_debug_pub.g_debug);
DBMS_OUTPUT.put_line ('Debug Level = ' || TO_CHAR (oe_debug_pub.g_debug_level));
DBMS_OUTPUT.put_line ('Debug File = ' || oe_debug_pub.g_dir || '/' || oe_debug_pub.g_file);
DBMS_OUTPUT.put_line ('****************************************************');
oe_debug_pub.debug_off;
END IF;
END;
/

Introduction to Oracle Alerts

Introduction:
Oracle Alerts is something that can be used to Notify/Alert to one or multiple persons about an activity or change that occurs in the system. The alerts can also be used to call a procedure, run some sql script etc.
There are 2 types of alert
1) Periodic Alert
2) Event Alert

Periodic Alerts:
These alerts are trigger periodically, hourly, daily, weekly, monthly etc based upon how it is setup to be triggered. When alert runs and the condition(SQL Query etc.) in the alerts fetches record, then the events specified in the alert are triggered.
Ex. 1) Daily alert to send notification on the sales order on which credit check hold is applied for a day
2) Hourly alert to send notification on all the concurrent request that completed with error

Event Alerts:
These Alerts are fired/triggered based on some change in data in the database. This is very similar to the triggers written on the table. Unlikely, event alerts can only fire on After Insert or After Update.
Ex. 1) An alert that sends notification when new item is created.

In next post we will discuss steps to create Periodic and Event alert.

Customer Import using API's

Following API's are used for creating customers
1) The first step is to create Party. hz_party_v2pub.create_organization is used to create a party.
2) Once party is created then the customer accounts should be created. hz_cust_account_v2pub.create_cust_account API is used to create Customer Accounts. The p_organization_rec should have party information. The orig_system_reference for p_cust_account_Rec should be same as parties orig_system_reference.
3) Now that party and accounts are created, customer account sites and its uses should be created. But before that location and party_sites should be created and attached to party.
4) hz_location_v2pub.create_location API is used to create location. This is a simple API that takes address table type as input and returns location_id as a OUT parameter.
5) API hz_party_site_v2pub.create_party_site is used to create party_site. The party_id created in step 1 and location_id created in step 4 is passed in the party_site_rec parameter. This will return party_site_id as a OUT parameter.
6) Now that we have created the party_sites, its time to create customer site using API hz_cust_account_site_v2pub.create_cust_acct_site. The cust_account_id created in step 2 and party_site_id created in step 5 is inputted in the cust_acct_site_rec record type. This returns cust_acct_site_id as a OUT parameter.
7) The site use(SHIP_TO, BILL_TO etc.) can be created using API hz_cust_account_site_v2pub.create_cust_site_use. The cust_acct_site_id created in step 6
8) For BILL_TO the customer profiles can be created using API hz_customer_profile_v2pub.create_customer_profile

Concurrent Program Name with Parameter, Value set

On request here is the query to list concurrent program name with its parameter, values set and default value/type

  SELECT fcpl.user_concurrent_program_name
      , fcp.concurrent_program_name
      , par.column_seq_num      
      , par.end_user_column_name
      , par.form_left_prompt prompt
      , par.enabled_flag
      , par.required_flag
      , par.display_flag
      , par.flex_value_set_id
      , ffvs.flex_value_set_name
      , flv.meaning default_type
      , par.DEFAULT_VALUE
 FROM   fnd_concurrent_programs fcp
      , fnd_concurrent_programs_tl fcpl
      , fnd_descr_flex_col_usage_vl par
      , fnd_flex_value_sets ffvs
      , fnd_lookup_values flv
 WHERE  fcp.concurrent_program_id = fcpl.concurrent_program_id
 AND    fcpl.user_concurrent_program_name = :conc_prg_name
 AND    fcpl.LANGUAGE = 'US'
 AND    par.descriptive_flexfield_name = '$SRS$.' || fcp.concurrent_program_name
 AND    ffvs.flex_value_set_id = par.flex_value_set_id
 AND    flv.lookup_type(+) = 'FLEX_DEFAULT_TYPE'
 AND    flv.lookup_code(+) = par.default_type
 AND    flv.LANGUAGE(+) = USERENV ('LANG')
 ORDER BY par.column_seq_num

Query to get Customer Name, Number and Address

Below query can be handy to get customer related information.
The query will list Party Name, Number, Customer Number and there Bill To and Ship Addresses.

SELECT hp.party_name
     , hp.party_number
     , hca.account_number
     , hca.cust_account_id
     , hp.party_id
     , hps.party_site_id
     , hps.location_id
     , hl.address1
     , hl.address2
     , hl.address3
     , hl.city
     , hl.state
     , hl.country
     , hl.postal_code
     , hcsu.site_use_code
     , hcsu.site_use_id
     , hcsa.bill_to_flag
FROM   hz_parties hp
     , hz_party_sites hps
     , hz_locations hl
     , hz_cust_accounts_all hca
     , hz_cust_acct_sites_all hcsa
     , hz_cust_site_uses_all hcsu
WHERE  hp.party_id = hps.party_id
AND    hps.location_id = hl.location_id
AND    hp.party_id = hca.party_id
AND    hcsa.party_site_id = hps.party_site_id
AND    hcsu.cust_acct_site_id = hcsa.cust_acct_site_id
AND    hca.cust_account_id = hcsa.cust_account_id
AND    hca.account_number = :customer_number

P.S. The query is not completely tested. Please let me know if you find any problem

Query to List all the responsibilities attached to a User

Based on a request from one of the reader here is the query which he was looking for.

He needed query that can list all the responsibilities attached to a user.

select fu.user_name, fr.responsibility_name, furg.START_DATE, furg.END_DATE
from fnd_user_resp_groups_direct furg, fnd_user fu, fnd_responsibility_tl fr
where fu.user_user_name = :user_name
and furg.user_id = fu.user_id
and furg.responsibility_id = fr.responsibility_id
and fr.language = userenv('LANG')

Unable to Retrieve All Rows Message in Discoverer

Problem: Trying to export report data into excel or when clicking on Tools--> Retrieve all Rows, Not all Rows have been Retrieved. Data may be inaccurate. error message is displayed.


Resolution: By Default in discoverer only 10000 records are set to be retrieved. This needs to be changed to a higher number in order to avoid this problem.
Click on Tools-->Options. Select Query Governer Tab and change the value in Limit retrieved query to 99999 to allow maximum number of records to be fetched.


Keywords: Discoverer 10g

Printing Barcode on a zebra Printer using ZPL

Here we will discuss on how to automatically print Barcodes on a zebra printer from Oracle concurrent program.
Below are the 3 approaches that we tried and was successful with the third approach.
1) Print Labels on a Zebra printer using BI Publisher
The first approach we tried was to print barcodes using BI(formerly XML) Publisher. The report generates a PDF output and should print directly on a Zebra printer using PASTA driver. For some reason the report was not printing directly on a printer. The PDF opened in window and printed seperately/manually using Windows driver was working fine but not meeting our requirement of automatic printing. Hence we had to try second approach.
2) Print Labels using Zebra Enterprise Connector Solution
The Zebra Enterprise Connector Solution is designed to streamline the printing
process for companies using ERP systems such as Oracle BI Publisher. The Zebra
Enterprise Connector Solution helps to lower middleware costs, overhead, and
pre-printer licensing fees. It can be used with an unlimited number of
Zebra ZPL-II printers without additional per-printer licensing fees. We didnt do much research on this as were looking for an approach with minimal cost. More details on this is available at the website of Zebra
3) Print Lables on Zebra printers using Zebra Programming Language(ZPL)
The second and successful approach was to print labels using ZPL. A text output with all the zebra codes was created and which would ultimately print Barcode images on a printer. A normal regular lp command for printing text is used to print over Zebra Printers.
Below is an example of how to print Barcodes

^XA
^FO40,35^AR,10,10^FDItem :^FS
^FO40,115^AR,10,10^FDItem Description:^FS
^FO40,165^AR,10,10^FDQuantity :^FS
^FO40,245^AR,10,10^FDDiscrete Job# :^FS
^FO40,295^AR,10,10^FDSupply Sub :^FS
^FO40,375^AR,10,10^FDLocator :^FS
^FO40,455^AR,10,10^FDDestination Sub :^FS
^FO40,505^AR,10,10^FDLocator :^FS
^FO40,555^AR,10,10^FDOrder # :^FS
^FO40,635^AR,10,10^FDLine # :^FS
^FO40,715^AR,10,10^FDOrder Type :^FS
^FO40,765^AR,10,10^FDDepartment :^FS^FO305,765^AR,10,10^FDResource Group :^FS
^FO40,845^AR,10,10^FDWork Center :^FS^FO305,845^AR,10,10^FDItem Sequence :^FS
^FO40,925^AR,10,10^FDAsm Item # :^FS^FO305,925^AR,10,10^FDAsm Item Desc :^FS

^FO265,35^BY3^B3N,N,50,Y,Y^FD199395B^FS
^FO265,115^AR,10,10^FDFILTER, LUBE OIL.^FS
^FO265,165^BY3^B3N,N,55,Y,N^FD2^FS
^FO265,245^AR,10,10^FD613467^FS
^FO265,295^BY3^B3N,N,50,Y,N^FDASM^FS
^FO265,375^BY3^B3N,N,50,Y,N^FDASM.REW..^FS
^FO265,555^BY3^B3N,N,50,Y,N^FD1264076^FS
^FO265,635^BY3^B3N,N,55,Y,N^FD1^FS
^FO265,715^AR,10,10^FDMove Order^FS
^FO50,795^AR,10,10^FD6400^FS
^FO375,795^AR,10,10^FD0047^FS
^FO70,875^AR,10,10^FDASMVH12.4.5.A^FS
^FO335,875^AR,10,10^FD3^FS
^FO70,955^AR,10,10^FDL7044GSI-1021598^FS
^FO335,955^AR,10,10^FDP2212J13 L7044 GSI W/ESM & AFR^FS
^XZ

More details on how to write codes using ZPL can be found in the website of zebra
The output of the above code is shown below.

Set the Low and High Date range parameter in the concurrent program

On request to one of the reader...
Below is the scenario,
The concurrent program has 2 date parameter e.g. Start Date and End date. The requirement is that start date should always be less than end date.

Solution:
1) In the concurrent program definition parameters, select Range as Low for Start Date. See Screenshot below
2) In the concurrent program definition parameters, select Range as high for End Date. See Screenshot below. Note that the value set being used for Start date and End date should be same.

3) Test program entering incorrect value for the parameter.

4) Test again by entering correct value.

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

Keywords: angle, angular, negative, brackets

ORA-24323: Value not Allowed error in Oracle Reports

Sometimes while working in Oracle Reports whenever we are trying to modify any query in the data model
ORA-24323: Value not allowed error is raised.

This happens when we are connected to database and for some reason the connection is dropped/disconnected. When we click on file menu, we feel like the connection still exists, but in reality this is not true. To overcome this error, completely close report builder and reopen it.