Pages

OracleEBSpro is purely for knowledge sharing and learning purpose, with the main focus on Oracle E-Business Suite Product and other related Oracle Technologies.

I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog.

I share my Oracle knowledge through this blog. All my posts in this blog are based on my experience, reading oracle websites, books, forums and other blogs. I invite people to read and suggest ways to improve this blog.


Monday, July 1, 2013

Serialized Items in OM, Inventory and AR

Item setup falls under two broad categories,
Serialized items
Non-Serialized items.
From domain perspective this setup comes handy when defining Cell phones and its accessories in oracle inventory.
Serialized Item - Cell Phone
Non-Serialized Item - Accessories
All items are stored at Master Org and other child org levels. This information is stored in
MTL_SYSTEM_ITEMS_B. The primary key to this table is INVENTORY_ITEM_ID and all the information pertaining to item attributes can be retrieved by using ORGANIZATION_ID filter.
Each Serialized item is associated with SKU (Stock keeping Unit) which is stored in SEGMENT1 of MTL_SYSTEM_ITEMS_B while the serial numbers under each SKU will be stored in,
MTL_SERIAL_NUMBERS.
Serial number information is stored at the granular level of organization id, which means, a SKU, serial number combination is unique at the organization level.
Ex.
SKU = MY PHONE100
Serial numbers: 1000, 1001, 1002.
Usual industry process recommends that this number is 13-15 characters long and will uniquely identify a handset.
When a serialized item is sold, this information is captured in:
OE_ORDER_HEADERS_ALL, OE_ORDER_LINES_ALL, OE_PRICE_ADJUSTMENTS, WSH_DELIVERY_DETAILS.
All the outgoing serial numbers are captured in WSH_SERIAL_NUMBERS.fm_serial_number. This table is joined to WSH_DELIVERY_DETAILS using DELIVERY_DETAIL_ID, which in turn joins to OE_ORDER_LINES_ALL using SOURCE_LINE_ID and SOURCE_HEADER_ID.
All the Sales invoice details are captured in RA_CUSTOMER_TRX_ALL ,RA_CUSTOMER_TRX_LINES_ALL, RA_CUST_TRX_LINE_GL_DIST_ALL, AR_PAYMENT_SCHEDULES_ALL.
All the information pertaining to an invoice can be tracked using CUSTOMER_TRX_ID.

Incase of returns on serialized items, the information can tracked in
RCV_SERIAL_TRANSACTIONS table, which joins with RCV_TRANSACTIONS using TRANSACTION_ID. RCV_TRANSACTIONS can be joined to OE_ORDER_LINES_ALL using OE_ORDER_LINE_ID.
This will help track the Serialized item sales and returns.

Trackig the items:
When items are received into Inventory at SKU level.
This information is captured in following tables:
MTL_MATERIAL_TRANSACTIONS - SKU (Segment1) level
MTL_UNIT_TRANSACTIONS - Serial number level.
These two tables are joined via, Transaction ID. 
Ex. A serial number enabled item ABC is received into inventory, 
MTL_MATERIAL_TRANSACTIONS
Trx ID Trx Type ID Inventory Item ID Trx Qty 
10000 10 200 5
MTL_UNIT_TRANSACTIONS
Trx ID Inventory Item ID Serial Number 
10000 200 1
10000 200 2
10000 200 3
10000 200 4
10000 200 5

Inventory balances are increased by quantity of 5.
This information is available in:
MTL_ONHAND_QUANTITIES_DETAIL
Serial number enabled items can be tracked using the details in:
MTL_SERIAL_NUMBERS
Latest Subinventory - CURRENT_SUBINVENTORY
Latest Status - CURRENT_STATUS

Non Serialized items cannot be tracked, at SKU level available quantity of any item can be determined but not which ones. These items can be tracked at the LOT NUMBER level so long as lots are enabled.

http://oraappstechfunc.blogspot.com/2010/07/serialized-items-in-om-inventory-and-ar.html

4 comments: