Receiving in Oracle, receipt routing method

When a Purchase Order , Internal Requisition or Return Order is created in the system once the records are approved/processed and eligible for receiving then business can perform the receiving of the Transaction in Oracle from the Receiving Screen,

Depending on the System setup and the Receipt routing method on the transaction the receving of the record ca be performed in one of the below ways :
1. Direct Receiving
2. Standard Receiving
3. Inspected Receiving

Direct Receipt :
Direct Receipt or direct receiving is a form of receiving which has a single step of receiving. Once the goods a arrive at the warehouse the the business will perform a receiving in which the goods will directly be transferred to the destination sub-inventory and warehouse .this is a single point receving
, For such records we have 2 records in the are created in the table rcv_transactions with type as  : RECEIVE and DELIVER by a single act of receiving

Standard Receipt : 
This is 2 way/step receiving process . when the goods are received at the warehouse gate a Receiving will be performed at the warehouse gate. In this step an entry is made in the table rcv_transactions with the type : RECEIVE , Once the receipt of goods is completed here , the goods will be received in the destination sub-inventory then the goods are received once more in the system. In this stage the goods are moved to the sub-inventory in the system and the receving is completed, An entry with TYPE : DELIVER is created in the system

Inspected Receiving

This is 3 way/step receiving process . when the goods are received at the warehouse gate a Receiving will be performed at the warehouse gate. In this step an entry is made in the table rcv_transactions with the type : RECEIVE , Once the receipt of goods is completed here , the goods will be inspected and depending on that the goods either pass inspection or are rejected . Accordingly the an entry is made in the table with type : INSPECTED/REJECTED.
If the goods are Inspected then the goods can be received in the warehouse sub-inventory. the goods will be received in the destination sub-inventory then the goods are received once more in the system. In this stage the goods are moved to the sub-inventory in the system and the receving is completed, An entry with TYPE : DELIVER is created in the system
For successful receipt 3 entries are made in rcv transactions table with type : RECEIVE,INSPECTED and DELIVER
For failed Records we have 2 entires : RECEIVE and REJECTED
 

Released_status in wsh_delivery_details (wsh_delivery_details.released_status)

When a Sales Order line is booked , for Shippable item lines have an entry in the table wsh_delivery_Details. The column released status has status values which provide information of the Sales Order shipment status

B: Backordered- Line failed to be allocated in Inventory
C: Shipped -Line has been shipped
D: Cancelled -Line is Cancelled
N: Not Ready for Release -Line is not ready to be released
R: Ready to Release: Line is ready to be released
S: Released to Warehouse: Line has been released to Inventory for processing
X: Not Applicable- Line is not applicable for Pick Release
Y: Staged- Line has been picked and staged by Inventory 

Detailed Explanation : 

B : Backordered : when we try to pick release a Sales Order but On-hand is not available for the order line , in such scenarios the order line is back ordered and the released status  = 'B'

C: Shipped :Once the Order line is shipped the associated delivery details Released Status is changed to 'C' .OM interface and Inventory interface have processed and the trip is closed.

D: Cancelled : When the Order line is cancelled the released status = 'D'

N: Not Ready for Release -Line is not ready to be released . this can happen when the information is available in wsh_delivery_details but order line has not reached the Awaiting Shipping status , might be due to some workflow customization's

R: Ready to Release: The Order line has been booked and the line is ready to be pick released . Line status is Awaiting Shipping under such scenarios

S: Released to Warehouse : Depending on the Pick release process in the business it moght be that Pick release is performed with Auto Pick confirm Set to No.

In such scenarios when we do pick release the Move Order will be created

Move Order allocation is done , but since Auto Pick Confirm is set to No the Mover Order will not be transacted.

The Delivery Detail will be in state : Pick Wave

X: Not Applicable- Line is not applicable for Pick Release , for the non-shippable item lines eg : Warranty orders

Y: Staged- Line has been picked and staged by Inventory .In this Case the line has been pick released. the Move Order Creation,Allocation and transaction is completed . Goods have be transferred from the Source Sub-invenotry to Staging area of the warehouse and the line is ready to be shipped

AR Invoice Dispute amount Update

In an AR Invoice we can update the Dispute Amount Information from a number of places :

1. More TAB on the AR Invoice Screen
2. Action --> Installments  from here we can update the Dispute amount and Dispute date details
3. Account Details Screen

From either of the 3 places when Dispute information is updated the information is reflected in the table ar_payment_schedules_all

However when the information is updated from the MORE TAB on the AR Invoice Screen in such a case the timestamp information od ar_payment_schedules_all is NOT Updated . 

In scenarios where we have a data warehouse which is based on the Timestamp update of WHO columns in such scenarios we need to ensure that Option 1 is not followed for updates and business follows Option 2/3.

Prevent Option 1 by using personalization or changes to Custom..pll

Item Categories -Category Set and Assignment

When a business maintains an inventory an essential requirement is the assignment of the items to specific categories

To understand this we need to have information of what these terminologies exactly represent

We will try to present here a brief overview of the item categories

Category Set : A category set can be said to a template or a schema on the basis of which we want to categorize items ,
Eg : A business may be involved in manufacturing of good which it wants to categorize on the basis of the countries where it wants to sell a product .In such a scenario the business will define a Category : SELL_COUNTRY and then assign items to this category set specifying to which Country the item is sold.

in order to perform this we need to perform some Steps :

1. Define Flex filed Structures for Item Category Sets : i.e,. If the business wishes to categrize items on the basis of SELL_COUNTRY , they need to create a KFF with specifying the details of the segments the combination of which will specify a valid value
2. Define Categories : Once the Flex-field  is defined we need to navigate to the item categories window and specify the values of categories which can be assigned under this category set
3. Once Item categories have been set up we need to navigate to the Category set window and create the Category set with details of the item categories
4. Once the setup is done the individual Items can be assigned to this categories

If we try to portray this scenario in the above sense take the example as below

Business decides to categorize items on the basis of the Regions :

1. Create a Flexfield with say 3 Segments : Continent - Country - State/Region
2. Specify the values for this Flexfield by Defining the Values Eg : Asia - India-Maharashtra 
3. Create a Category Set SELL_COUNTRY and then specify these values , default  values 
4. Assign the items to a valid category

Once this is done business can build reports to identify which goods are available for selling in Particular regions

One of the main uses of this is Item Assignment.

In a business we will have a number of warehouses , Depending on the Item categories we can run the warehouse assignment for a specific set of Category values thus assigning the items to Warehouses

In the above Example if a business has Multiple warehouses in India for which are under a hierarchy Eg : India-Mahrashtra

In this now if there are many items which need to be available to this warehouses , Item Assignment can be run for this hierarchy, Item Category Sell_COUNTRY with Item category as : Asia-India - Maharashtra. Thus all the items under this category will be picked and then assigned to the warehouses in the hierarchy

Going by business operations it is not possible to perform this activity manually for all items, such a categorization makes the business work efficiently and free from manual errors

API to Create External Bank Branch

When performing Master Data Migration for Supplier or Customer business also needs the migration of Associated Banks and Bank Branches .

In order to perform this we need to migrate the Banks , Bank Branches and associated Bank Branches

In R12 to create an external bank branch Oracle has given an API :

IBY_EXT_BANKACCT_PUB.create_ext_bank_branch

API Signature :

create_ext_bank_branch (
   p_api_version                IN   NUMBER,
   p_init_msg_list              IN   VARCHAR2,
   p_ext_bank_branch_rec        IN   ExtBankBranch_rec_type,
   x_branch_id                  OUT  NOCOPY  NUMBER,
   x_return_status              OUT  NOCOPY  VARCHAR2,
   x_msg_count                  OUT  NOCOPY  NUMBER,
   x_msg_data                   OUT  NOCOPY  VARCHAR2,
   x_response                   OUT  NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  )

p_ext_bank_branch_rec         Bank branch record type
x_branch_id                     Id of the branch created
x_return_status                Return status of API

Sample code :

                         IBY_EXT_BANKACCT_PUB.create_ext_bank_branch (
                            p_api_version           => 1.0,
                            p_init_msg_list         => FND_API.G_TRUE,
                            p_ext_bank_branch_rec   => l_bank_branch_rec,
                            x_branch_id             => l_num_branch_id,
                            x_return_status         => l_chr_return_status,
                            x_msg_count             => l_num_count,
                            x_msg_data              => l_msg_data,
                            x_response              => x_response_rec);


                         writelog (
                            'l_chr_return_status in bank branch '
                            || l_chr_return_status);
                         writelog ('Branch Id: ' || l_num_branch_id || SQLERRM);

                         --Check for Sucess API
                         IF (l_chr_return_status <> 'S')
                         THEN
                            FOR i IN 1 .. l_num_count
                            LOOP
                               fnd_msg_pub.get (
                                  p_msg_index       => -1,
                                  p_encoded         => 'F',
                                  p_data            => l_msg_data,
                                  p_msg_index_out   => l_msg_index_out);

                               writelog (
                                  'Error in Bank Branch Creation  ' || l_msg_data);
                               l_chr_val_ret_msg :=
                                  l_chr_val_ret_msg || '  ' || l_msg_data;
                               RAISE e_error;
                            END LOOP;
                         ELSE
                            writelog ('  Branch created successfully. ');
                         END IF;

Parameters to pass :

Bank Id
branch name
branch number
branch type
Swift code
alt_branch_name

Note : Branch can only be created if a bank exists make sure to use create bank before calling this API

A post for that too is available in the blog

Once the record is created information can be found in the view : iby_ext_bank_branches_v

API to Create External Bank

When performing Master Data Migration for Supplier or Customer business also needs the migration of Associated Banks and Bank Branches .

In order to perform this we need to migrate the Banks , Bank Branches and associated Bank Branches

In R12 to create an external bank Oracle has given an API :

IBY_EXT_BANKACCT_PUB.create_ext_bank

API signature :

PROCEDURE create_ext_bank (
        p_api_version              IN  NUMBER,
p_init_msg_list            IN  VARCHAR2,
p_ext_bank_rec             IN  ExtBank_rec_type,
x_bank_id                  OUT NOCOPY NUMBER,
x_return_status            OUT NOCOPY VARCHAR2,
x_msg_count                OUT NOCOPY NUMBER,
x_msg_data                 OUT NOCOPY VARCHAR2,
x_response                 OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
  )

p_ext_bank_rec   is the record type variable with the infromation of the bank to be created in the system

x_bank_id                  is the id of the bank to be created
x_return_status           return status

Sample Code :

IBY_EXT_BANKACCT_PUB.create_ext_bank (
                            p_api_version     => 1.0,
                            p_init_msg_list   => FND_API.G_TRUE,
                            p_ext_bank_rec    => l_bank_rec,
                            x_bank_id         => l_num_bank_id,
                            x_return_status   => l_chr_return_status,
                            x_msg_count       => l_num_count,
                            x_msg_data        => l_msg_data,
                            x_response        => x_response_rec);

                         IF (l_chr_return_status <> 'S')
                         THEN
                            FOR i IN 1 .. l_num_count
                            LOOP
                               fnd_msg_pub.get (
                                  p_msg_index       => -1,
                                  p_encoded         => 'F',
                                  p_data            => l_msg_data,
                                  p_msg_index_out   => l_msg_index_out);
                               fnd_file.put_line (
                                  fnd_file.LOG,
                                  'Error in Bank  Creation  ' || l_msg_data);
                               l_chr_val_ret_msg :=
                                  l_chr_val_ret_msg || '  ' || l_msg_data;
                               RAISE e_error;
                            END LOOP;
                         ELSE
                            fnd_file.put_line (fnd_file.LOG,
                                               'Bank Created Sucessfully  ');
                         END IF;

Mandatory Parameters :
Bank Name
Bank Country
Bank Number
Currency Code

Combination of these is an Unique combination and API gives an error if we try to create duplicate records

Once the bank is created information can be searched in the view iby_ext_banks_v

IR-ISO cycle Techno Functional Overview

One of the cycles being setup and used in the business is the Internal Requisition and Internal Sales Order Cycle commonly known as IR-ISO Cycle.

The complete setup of the system needs to be done when doing the IR-ISO cycle . below we are highlighting some of the basic Setups to be done for an IR-ISO cycle, then we will give a brief overview of the Steps occurring in the IR-ISO Sycle
1. Item Setup : The items we wish to be ordered for IR-ISO need to have the Internal Order and Internal Order Enabled Flag checked for the Transaction , Item Validation and Item Master Warehouse
2.Shipping Networks need to be setup  between the Shipping and the Receiving Warehouse

3. The customer Setup should be completed : The customer for whom the internal Order is created the SHIP TO site must be setup with the Internal Location and Internal Warehouse of of the Shipping Warehouse

4. Purchasing Option Setup need to be updated with the Internal Order Type

Once the Setup is completed  we can perform the IR-ISO cycle

1. Create the Internal Requisition and save It
      > Entry is made in the table po_requistion_headers and po_requisition_lines table , Current Status          of the the Requisition is Incomplete
2. Approve the Requisition
     > The Requisition is now Approved
3. Run the Standard Oracle Program "Create Internal Order"
     > This program creates the Sales Order from the Internal Requisition , the program picks up the             information of the Requisition and Populates the information in the Order in the Order Interface         table oe_headers_iface and oe_lines_iface
4. Run the Program "Order Import"
    > This Program will complete the Order Import and Sales Order will be available in Oracle
5. Query the Sales Order using requisition information and Complete the Order cycle
   > From back end the column : source_document_line_id of oe_order_lines table will be have the po_requisition line id
6. Once the Order is picked and Ship the Internal Requisition is now eligible for receiving
7. Perform the Requisition receiving
   > Information comes in the Table : rcv_shipment_headers , rcv_shipment_lines, rcv_transactions

This is the total flow of information in IR-ISO and overview of the basic tables affected