Delivery in Oracle Applications Shipping Execution

The concept of delivery is of importance to any person understanding the concept of shipping in Oracle Order Management. Once the Order lines are pick released, next step is shipping of the order lines. In oracle shipping is handled by the concept of delivery. When a order line(s) is shipped basically shipping of a delivery is done.

Delivery Overview
A Delivery consists of a set of lines that are scheduled to be shipped to a customers ship to location on a specific date and time. A delivery can include lines from different sales orders as well as back orders.
Deliveries can either be created manually and then lines are assigned to the delivery or users can auto-create deliveries when pick releasing a sales order lines.
If deliveries are auto-created they will be by grouped by oracles mandatory grouping criteria ship from location and ship to location,Location. However, additional grouping criteria can be included such as:
■ Customer
■ Freight Terms
■ FOB Code
■ Intermediate Ship To Location
■ Ship Method

Deliveries are the entities which are regarded as the shipment number of an order line and to the deliveries users will assign the tracking numbers,bill of lading and container information.

Delivery Creation

Manual Delivery Creation:
Deliveries can be manually created by users by navigating to the deliveries window, here the user will key in all the delivery details, once the delivery is created the user needs to assign order lines to the deliveries, in case any of the oracle grouping rules are violated the user will be intimated.


To create a delivery:
1. Navigate to the Delivery window.
2. Enter a Name and Org Code for the delivery.

3. Select the Initial Ship from location and the Ultimate Ship to.
At this point, you can save the delivery.If the user wishes he can enter the additional details
4. Save the Work


Auto- Create Deliveries:
When the Order is pick released the user can auto create the deliveries at that time and by selecting the Auto Create Delivery : Yes ,  Oracle will create the delivery and assign the eligible order lines to the delivery.

Additionally the user can pick release the orders without creating deliveries but then navigate to the shipping Transactions Screen where he can auto-create delivery and assign it to order lines. When auto-creating deliveries in case the order has multiple linesOne or more deliveries can be created depending on the default delivery grouping criteria set up in the Shipping Parameters. For example, if two groups of delivery
lines have different Ship To addresses, a different delivery number is assigned to each group.


To auto-create deliveries:
1. Navigate to the Query Manager window, and find the delivery lines.
The delivery lines are displayed in the Shipping Transactions form.
2. Select the delivery lines for which you want to create a delivery.
3. From the Actions menu, select Auto-create Deliveries.
4. Click Go to create a delivery or deliveries for the selected lines.
You can view the delivery name created for the delivery lines in the Delivery
column in the Lines/LPNs tab.
5. Choose the Delivery tab to view or add additional delivery details.
6. Save your work.


Whenever a delivery is created in the system the status of the delivery is OPEN ,  till the delivery is open user can manipulate the delivery by assigning or un-assigning lines to it.

When the user performs shipping of an Order line by selecting the Option : Ship Confirm from shipping transactions Window , he is basically Ship confirming the Delivery.
When the user ship Confirms a Delivery Line he can perform the following operations

1. Ship all the Quantities to the Order Lines
2. Partially ship some of the quantities of the order lines by updating the same in the shipping transactions window,in case of partial shipment of the delivery line the order line will be split up by the system
3. Backorder the quantities of the order line, In such a case the order line is backordered, the inventory is moved back from Stage to source sub-inventory, the released_status of the line is updated to 'B' and the user is required to pick release the line again
4. The user can specify the Tracking number,bill of lading number, carton details and the serial numbers of the parts in case of serial controlled items.
5. Specify the Shipping documents to be printed e.g : Bill of Lading

Once the user performs Ship Confirm operation on the delivery the delivery status is changed to 'CLOSED', no further changes can be performed on the delivery.

System Changes :

1. When a delivery is created a new row is inserted into the table : wsh_new_deliveries
2. When the user assigns a delivery line to the order line the table wsh_delivery_assignements is updated with the delivery id , this table keeps a track of the delivery assigned to order line as it also tracks the delivery detail id of the order line
3. The delivery id is the shipment number of the order line
4. All the changes can be seen by the user on the front end by navigating to  : Order Line --> Actions Button --> Additional Line Information --> Delivery Tab --> View Delivery Details , this screen will show the user all the delivery and pick release information of the sales order

Process Order Api in Oracle to Create Order

Process Order API is a standard API provided by Oracle to Create/Update/Cancel the Order.
Given below is a sample code which highlights the use of the API

l_msg_index_out            NUMBER;

l_excp_exit                EXCEPTION;

l_num_salesrep_id          NUMBER;

l_line_rec                 oe_order_pub.line_rec_type;

l_line_tbl                 oe_order_pub.line_tbl_type;

l_request_tbl              oe_order_pub.request_tbl_type;

l_header_rec               oe_order_pub.header_rec_type;

l_header_val_rec           oe_order_pub.header_val_rec_type;

l_header_adj_tbl           oe_order_pub.header_adj_tbl_type;

l_header_adj_val_tbl       oe_order_pub.header_adj_val_tbl_type;

l_header_price_att_tbl     oe_order_pub.header_price_att_tbl_type;

l_header_adj_att_tbl       oe_order_pub.header_adj_att_tbl_type;

l_header_adj_assoc_tbl     oe_order_pub.header_adj_assoc_tbl_type;

l_header_scredit_tbl       oe_order_pub.header_scredit_tbl_type;

l_header_scredit_val_tbl   oe_order_pub.header_scredit_val_tbl_type;

l_line_val_tbl             oe_order_pub.line_val_tbl_type;

l_line_adj_tbl             oe_order_pub.line_adj_tbl_type;

l_line_adj_val_tbl         oe_order_pub.line_adj_val_tbl_type;

l_line_price_att_tbl       oe_order_pub.line_price_att_tbl_type;

l_line_adj_att_tbl         oe_order_pub.line_adj_att_tbl_type;

l_line_adj_assoc_tbl       oe_order_pub.line_adj_assoc_tbl_type;

l_line_scredit_tbl         oe_order_pub.line_scredit_tbl_type;

l_line_scredit_val_tbl     oe_order_pub.line_scredit_val_tbl_type;

l_lot_serial_tbl           oe_order_pub.lot_serial_tbl_type;

l_lot_serial_val_tbl       oe_order_pub.lot_serial_val_tbl_type;

l_request_rec              oe_order_pub.request_rec_type;

l_action_request_tbl       oe_order_pub.request_tbl_type;

l_num_tbl_index            NUMBER;

l_chr_return_status        VARCHAR2 (20);           --POA return Status

l_num_msg_count            NUMBER;

l_chr_msg_data             VARCHAR2 (1000);

l_num_msgcntr              NUMBER;

l_num_order_number        NUMBER;

l_num_header_id           NUMBER;

l_num_line_count          NUMBER;

l_chr_error_message       VARCHAR2(1000);

l_num_temp                NUMBER;

l_excp_skip               EXCEPTION;

l_chr_error               VARCHAR2(1000);

BEGIN

   fnd_file.Put_line(fnd_file.LOG,'*********************************************** BOOK ORDER **************************');

   fnd_file.put_line(fnd_file.LOG,'Begining of the procedure Book Order');

   out_chr_errbuf := 'success';

       --Initialize header record

         l_header_rec                   := oe_order_pub.g_miss_header_rec;

        

         l_header_rec.cust_po_number := cust_po_number;

         l_header_rec.ordered_date := g_dte_sysdate;

         l_header_rec.salesrep_id            := -3;

         l_header_rec.order_type_id := 1085; 

         l_header_rec.operation := oe_globals.g_opr_create; --Specifies that Order is getting created

        

         l_header_rec.order_category_code := 'ORDER';

         l_num_tbl_index := 1;

         l_header_rec.booked_flag                    := 'Y';

         l_header_rec.sold_to_org_id                 := customer_id;

         l_header_rec.invoice_to_org_id              := invoice_to_org_id;

         l_header_rec.ship_to_org_id                 := ship_to_org_id;

         l_header_rec.price_list_id                  := price_list_id;

         l_line_tbl (l_num_tbl_index) := oe_order_pub.g_miss_line_rec;

         -- Line attributes

         l_line_tbl (l_num_tbl_index).inventory_item_id      := inventory_item_id;

         l_line_tbl (l_num_tbl_index).ordered_quantity       := quantity;

         l_line_tbl (l_num_tbl_index).line_category_code     := 'ORDER';

         l_line_tbl (l_num_tbl_index).line_type_id           := 1043; --transaction type id

         l_line_tbl (l_num_tbl_index).operation              :=     oe_globals.g_opr_create; -- Create Order Line

         l_action_request_tbl (l_num_tbl_index)              := oe_order_pub.g_miss_request_rec;

         l_action_request_tbl (l_num_tbl_index).request_type := oe_globals.g_book_order;

         l_action_request_tbl (l_num_tbl_index).entity_code  := oe_globals.g_entity_header;

         l_num_tbl_index := l_num_tbl_index + 1;

         oe_order_pub.process_order

                        (p_api_version_number          => 1.0,

                         p_init_msg_list               => fnd_api.g_true,

                         p_return_values               => fnd_api.g_false,

                         x_return_status               => l_chr_return_status,

                         x_msg_count                   => l_num_msg_count,

                         x_msg_data                    => l_chr_msg_data,

                         p_header_rec                  => l_header_rec,

                         p_line_tbl                    => l_line_tbl,

                         p_action_request_tbl          => l_action_request_tbl,

                         x_header_rec                  => l_header_rec,

                         x_header_val_rec              => l_header_val_rec,

                         x_header_adj_tbl              => l_header_adj_tbl,

                         x_header_adj_val_tbl          => l_header_adj_val_tbl,

                         x_header_price_att_tbl        => l_header_price_att_tbl,

                         x_header_adj_att_tbl          => l_header_adj_att_tbl,

                         x_header_adj_assoc_tbl        => l_header_adj_assoc_tbl,

                        x_header_scredit_tbl          => l_header_scredit_tbl,

                         x_header_scredit_val_tbl      => l_header_scredit_val_tbl,

                         x_line_tbl                    => l_line_tbl,

                         x_line_val_tbl                => l_line_val_tbl,

                         x_line_adj_tbl                => l_line_adj_tbl,

                         x_line_adj_val_tbl            => l_line_adj_val_tbl,

                         x_line_price_att_tbl          => l_line_price_att_tbl,

                         x_line_adj_att_tbl            => l_line_adj_att_tbl,

                         x_line_adj_assoc_tbl          => l_line_adj_assoc_tbl,

                         x_line_scredit_tbl            => l_line_scredit_tbl,

                         x_line_scredit_val_tbl        => l_line_scredit_val_tbl,

                         x_lot_serial_tbl              => l_lot_serial_tbl,

                         x_lot_serial_val_tbl          => l_lot_serial_val_tbl,

                         x_action_request_tbl          => l_request_tbl

                        );  

            fnd_file.put_line (fnd_file.LOG,

                                     'Return from the API'

                                  || 'return status'

                                  || l_chr_return_status

                                 );

        COMMIT;

        IF l_chr_return_status <> 'S'

        THEN

           print_api_error_messages (l_chr_error,

                                    'OE_ORDER_PUB.PROCESS_ORDER',

                                     l_chr_return_status,

                                     l_num_msg_count

                                     );

        -- fnd_file.put_line(fnd_file.LOG,'Return from api error Procedure' || l_chr_error);

           l_chr_error_message := l_chr_error;

           RAISE l_excp_skip;

        END IF;

END;

The API can be used to create or update an order. 

In order to create a sales order pass the parameter  l_header_rec.operation := oe_globals.g_opr_create

In order to Update an Order pass the parameter :  l_header_rec.operation := oe_globals.g_opr_update

In order to Cancel an Order Line pass the parameter: l_header_rec.operation := oe_globals.g_opr_update

 when passing the other parameter of the line record,update the Ordered Quantity as 0

When we are calling the Process Order API there are certain mandatory fields which need to be passed eg : order type,customer details and others. and there are other details are derived by the defaulting rules of the Order Management setup, in case any of the values being derived by the defaulting rules is already provided in the API it overrides the defaulting rules value.

Multi-Org Structure in Oracle EBS

Multi Org or multiple organization structure in Oracle is the architecture which allows a single Oracle Application Installation to track the different business operations of all the concerned companies/subsidiaries where each can follow a different set of books for its reporting purpose. The Multi org concept is composed of a number of entities.We will be providing you a brief overview of the multi-org structure available in Oracle.

Multi Org Architecture

A brief description of each of the entities is give below

Business Group:The business group represents the highest level in the organization structure ,such as the consolidated enterprise, a major division or an operational company.The business group secures human resource information of the company. If we list the employees of the company it is seen that all the employees are assigned to the highest business concern ie the business group. Going by Oracles architecture multiple legal entities or set of books can be associated to a single business group.

Set of Books:  A financial reporting entity that uses a particular chart of accounts, functional currency and accounting calender. Oracle General Ledger secures transaction information (journal entries and balances) for a set of books.

A chart of accounts (COA) is a created list of the accounts used by the set of books to define each class of items for which money or the equivalent is spent or received. It is used to organize the finances of the entity and to segregate expenditures, revenue, assets and liabilities in order to give interested parties a better understanding of the financial health of the entity.

The list can be numerical, alphabetic, or alpha-numeric. The structure and headings of accounts should assist in consistent posting of transactions. Each nominal ledger account is unique to allow its ledger to be located. The list is typically arranged in the order of the customary appearance of accounts in the financial statements, balance sheet accounts followed by profit and loss accounts.

Legal Entity: A legal company for which fiscal or tax reports are prepared. Tax  identifiers and other legal entity information are assigned to this organization.Multiple legal entities may share the same set of books.

Operating Unit: Operating unit refers to the single organization that uses cash management,Order Management and Shipping Execution,Oracle Payables, Purchasing and Oracle Receivables. The operating unit can be referred to a particular department of a company for example : In a large organization we may have multiple operating units of which one tracks details of Finished Goods, one for spares and one for raw materials etc. Each operating unit is associated with a legal entity. Data of each operating unit is secured from the other, the users also see the data relevant to their operating units . This is done by via responsibilities in oracle. Data in the system is accessed by the users by querying data by navigating to a particular responsibility which is assigned to a given operating unit.

Inventory Organization: Is an organization for which we track inventory transactions and balances. An Inventory organization may refer to a business warehouse,godown or sales office. The following applications secure information by inventory organization: Oracle Inventory, Bills of Material, Engineering, Work in Process,Master Scheduling/MRP, Capacity, and Purchasing receiving functions.To run any of these applications, you must choose an organization that has been classified as an inventory organization.

The Mutli Org structure of Oracle can be further extended to the level of Subinventory and Stock Locator.

Each Inventory will have one or more sub-inventories.

 A sub-inventory refers to a region within the warehouse to track the goods. For Eg in a inventory organization for finished goods . a Subinventory might be maintained for LCD television, one for Music system etc. 

Each defined sub inventory might have one or more stock locators.

A stock locator is an area within the sub-inventory which helps the exact location of goods. Continuing with the above example , a given sub-inventory for LCD television might have one locator for 26' TV, one for 32' and so on.

Set of books are created by  using the Define set of books window in Oracle General Ledger.All pother types of organization are defined using the define organization window.

• Multiple Organizations in Oracle Applications is enabled by partitioning some database tables by operating unit. Other tables are shared across operating units (and thus across sets of books).
• In general, the following criteria determine if a table would be partitioned:
  • The table contains a GL Account Code (code combination ID).
  • There is a business reason for the table to be partitioned (for example, the entity should not be shared).
  • The table contains transaction data.
  • The table is an interface table where data being loaded is partitioned.

Add Responsibility to a Given User in Oracle Apps

Once a User has been created in the Oracle system, the system administrator needs to assign responsibilities to the user so that he/she can perform the required tasks.By default when a user is created no responsibility is attached to the user, the system administrator needs to assign responsibilities to the user.

There are 2 methods to assign responsibilities to the user


1. Oracle Apps Front End: The user can be queried from the System Administrator Responsibility and the required responsibilities can be added and then save the details. This is a manual process which will require a lot of effort and time in case a number of responsibilities need to be added to a set of users created in the system.

2. Via Oracle API
Moreover method 1 does not seem to be a viable option for large scale business requiring recurring creation of a large number of users. In Order to solve the problem Oracle has provided the users with an API to acheive the functionality

API: fnd_user_pkg.addresp

Given below is a sample code for the API usage
DECLARE

  v_session_id INTEGER := userenv('sessionid');

  v_user_name  VARCHAR2(30) := upper('Ashwini');

BEGIN

fnd_user_pkg.addresp(username       => v_user_name

                      ,resp_app       => 'SYSADMIN'

                      ,resp_key       => 'SYSTEM_ADMINISTRATOR'

                      ,security_group => 'STANDARD'

                      ,description    => 'Auto Assignment'

                      ,start_date     => SYSDATE - 10

                      ,end_date       => SYSDATE + 1000);

COMMIT;

  EXCEPTION

  WHEN OTHERS

  THEN

     dbms_output.put_line('Exception Occurred in Processing ');

     dbms_output.put_line('Oracle Error '||SQLERRM);

END;

If all the details provided are correct , the responsibility System Administrator in the above case gets assigned to the user 'Ashwini' with the for a specified period, since in the API we have populated the end date for the responsibility assignment.

Update Oracle Apps User Details

Once the users have been created in the oracle system, there might be scenarios when there is a requirement to update some properties of the user.
 Oracle Provides an API for the functionality: fnd_user_pkg.updateuser

Given below is a sample script to perform the intended functionality

declare

  v_session_id INTEGER := userenv('sessionid');

  v_user_name  VARCHAR2(30) := upper('test2');

begin

fnd_user_pkg.UpdateUser (

  x_user_name                  => v_user_name,

  x_owner                      => 'CUST',

  x_unencrypted_password       => '456789',  --new password

  x_session_number             => v_session_id,

  x_start_date                 => null,

  x_end_date                   => null, -- populated when disabling the user

  x_last_logon_date            => null,

  x_description                => null,

  x_password_date              => null,

  x_password_accesses_left     => null,

  x_password_lifespan_accesses => null,

  x_password_lifespan_days     => null,

  x_employee_id                             => null,

  x_email_address              => null,

  x_fax                          => null,

  x_customer_id                              => null,

  x_supplier_id                 => null,

  x_old_password               => 'asdfg'  --old password

);

  EXCEPTION

  WHEN OTHERS

  THEN

     dbms_output.put_line('Exception Occurred in Processing ');

     dbms_output.put_line('Oracle Error '||SQLERRM);

END;

In case the user exists in the system the API will successfully update the uses properties. In case no user with the provided username exists in the system Oracle will throw an error.

Create User in Oracle Apps

In a business entity using Oracle Applications as and when new users want to access the system, we need to create individual accounts for the users.

One of the options is to create the user manually from the System Administrator Responsibility, but in most business scenarios the business will have the user details stored in the database which can be picked up by a script , which will subsequently call the API to create multiple users in a single program run.

The generic API provided by Oracle to achieve it is : fnd_user_pkg.createuser , fnd_user_pkg is the Package and create user is the procedure which performs the intended operation.

Given below is a sample anonymous block which can be used to create a user in the system.

DECLARE

  v_session_id INTEGER := userenv('sessionid');

  v_user_name  VARCHAR2(30) := upper('Ashwini');

BEGIN

  --Note, can be executed only when you have apps password.

  -- Call the procedure to Creaet FND User

fnd_user_pkg.createuser(x_user_name                  => v_user_name

                                    ,x_owner                      => 'CUST'

                                    ,x_unencrypted_password       => '123456'

                                    ,x_session_number             => null

                                    ,x_start_date                 => SYSDATE - 10

                                    ,x_end_date                   => null

                                    ,x_last_logon_date            => null

                                    ,x_description                => 'proracleapps.blogspot.com'

                                    ,x_password_date              => null

                                    ,x_password_accesses_left     => null

                                    ,x_password_lifespan_accesses => null

                                    ,x_password_lifespan_days     => null

                                    ,x_employee_id                => null

                                    ,x_email_address => NULL

                                    ,x_fax           => ''

                                    ,x_customer_id   => ''

                                    ,x_supplier_id   => '');

  EXCEPTION

  WHEN OTHERS

  THEN

     dbms_output.put_line('Exception Occurred in Processing ');

     dbms_output.put_line('Oracle Error '||SQLERRM);

END;

The above script create a user with username  'ASHWINI' and password '123456' in the system. In case the username is already in user the API will throw an error.

If no user exists with the username a new user is created with this user name and password.

When the user tries to login with the username he is prompted to change the password and set his new password.

Depending on the business requirements other parameter values can also be passed in.

Oracle Applications File System

Oracle Applications file system architecture provides us information of the way the files are organized in in Oracle Apps. The Oracle applications system consists of a number of different types of files which constitute the whole system. We have pl/sql files,.fmb files for forms,.rdf files for Reports ,.pll library files , java ,html files and other files. The file system Architecture is concerned with the orderly organization of these in the system.

An Oracle Applications system utilizes components from many Oracle products.These product  files are stored below a number of key top-level directories on the database and application server machines
Database Server:
The DATA_TOP directory contains the table spaces, redo log files, data table spaces, index table spaces and database files.The DB  directory is located on the database server and contains Oracle HOME for the Oracle Database
Application Server:
The APPL_TOP directory contains the product directories and files for Oracle Applications.
The ORA directory contains the ORACLE_HOMEs for the Applications technology stack components.
The  COMN_TOP directory contains directories and files used across products.

COMMON_TOP: The COMMON_TOP directory contains files used by many Oracle Applications products.
The admin directory, under the COMMON_TOP directory, is the default location for the concurrent manager log and output directories. When the concurrent managers run Oracle Applications reports, they write the log files and temporary files to the log subdirectory of the admin directory, and the output files to the out subdirectory of the admin directory.

APPL_TOP: The APPL or APPL_TOP directory contains the product directories and files for Oracle Applications.

Appl_Top Directory Structure

Each product has its own subdirectory under APPL_TOP. The subdirectories are named in accordance with the product’s standard abbreviation, such as gl for Oracle General Ledger.
Within each product directory is a subdirectory that is named using the base Applications release number, such as 11.5.0.
Product Files :
Each <PROD>_TOP directory, such as <APPL_TOP>/gl/11.5.0, contains subdirectories for product files. Product files include forms files, reports files, and files used to upgrade the database. To display data entry forms for Oracle General Ledger, for example, Oracle Applications accesses files in the forms subdirectory under the 11.5.0 directory.

Each <PROD>_TOP directory contains different types of files to be used by the <PROD>_TOP application to perform the actions and provide the functionality it is intended to, each of these directories has a number of sub directories created to organize the different files.

Given below are the folders created in each <PROD>_TOP to store the different files

Admin	Contains files used by Autoupgrade to upgrade each separate product
Bin	Contains concurrent programs, control files, shell scripts
Forms	Contains the fmb and fmx files
Help	Contains online help files which are imported suring installation
Html	Contains html,javascript and jsp pages eg : istore
Include	Include C lib header(.h) files
Java	Contains .class java files and .jar files
Lib	Contains lib files to link concurrent program to oracle database server
Log and out	Contains output files of concurrent programs
Media	Contains the graphic files
mesg	It contains .msb files. The messages displayed at the bottom of the screen and in the pop up boxes of Forms are stored here.
patch	Updates to the data or data model use this directory to store the patch files.
Reports	Contains the rdf files
Resource	Contails the pl/sql and .pll files of oracle forms
plsql	Contains PL/SQL library files (.pll) for Oracle Reports
sql	Contains SQL*Plus script files (.sql) for concurrent processing.

 The file system basically specifies which files need to be stored where because the oracle  system is designed to read specific files from the specified locations, for example the system will expect that if a form is created the .fmx file for the form will be found in the forms folder. 

A basic understanding of the file system is essential for the developers as it provides then knowledge of where to store which files in the system thus enabling smooth development of the system.