Testing the transaction open interface with minimum columns populated.
Account alias receipt for an item that is neither Lot or Serial control.
We have defined an item bmw and we have assigned the item to W1 Cherry hill organization.
We are going to receive 10 quantity into subinventory FGI and locator '1.1.1'.
Below is the sql to check organization id for organization W1 .
SQL> select organization_code,organization_id from org_organization_definitions
where organization_code = 'W1';
ORG ORGANIZATION_ID
--- ---------------
W1 1884
Below is the sql to check the inventory_item_id for out item bmw .
SQL> select inventory_item_id , segment , organization_id from mtl_system_items_b
where segment = 'bmw'
and organization_id = 1884;
INVENTORY_ITEM_ID SEGMENT ORGANIZATION_ID
----------------- ---------------------------------------- ---------------
10531 bmw 1884
Below is the sql to find out the transaction_type_id for transaction type Account alias receipt.
SQL> select transaction_type_id,transaction_type_name,description
from mtl_transaction_types
where transaction_type_name = 'Account alias receipt';
TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME
------------------- ------------------------------
DESCRIPTION
----------------------------------------------------------------------------------------------------
41 Account alias receipt
Receive material against account alias
Below is the script to get the alias account name and the account id it is linked to.
Segment is the account alias name.
Below is the sql to get the locator id for locator ‘1.1.1’ associated with subinventory ‘FGI’.
SQL> select inventory_location_id,segment1, segment2, segment3,subinventory_code from mtl_item_locations
where organization_id = 1884
and subinventory_code = 'FGI'
and segment1 = '1'
and segment2 = '1'
and segment3 = '1';
Below is the script to find the segments(account c) for the distribution acc021. to account
alias screen in Inventory and verify the account for the alias that is defined.
Miscellaneous receipt using LPN for an item under serial control
Overview of License Plate Numbers (LPN):
LPN's are used in Oracle Warehouse Management System (WMS).Oracle WMS provides full visibility to inventory items that are stored in LPNs. An LPN in Oracle WMS is any object that
exists in a location and holds items. Although LPNs are associated with containers, they do not need to represent a physical identity, such as a box.
Thus, a LPN might be defined as just a label or a collection of items. Oracle WMS enables you to track, transact and nest LPNs and their contents.
Using License Plate Numbers:
The Oracle WMS LPN features enable you to do the following:
a. Receive, store, and pick material by LPN
b. View on-hand balances by LPN
c. View contents of LPN, including item number, serial number, quantity, and so on
d. Move many items in a transaction using the LPN
e. Print labels and reports for referencing container contents
f. Track nested LPNs (for e ample, cartons on a pallet)
G. Pack, unpack, consolidate, split, and update LPNs
h. Reuse empty LPNs
Points to remember when using LPN s (License Plate Numbers):
LPN_ID : LPN from which an Unpack operation is to be done
TRANSFER_LPN_ID : LPN to which a Pack operation is to be done
CONTENT_LPN_ID field is populated for Issue transactions and the TRANSFER_LPN_ID for Receipt transactions (LPN_ID field in MTI should NOT be used for these transactions)
We cannot issue individual serials from an LPN through an Issue transaction. If an Misc. Issue is done for an LPN the ENTIRE LPN and ALL ITS CONTENTS are issued out. If the you want to issue out a single serial from an LPN, the you must first unpack the serial from thesting an unpack transaction and then issue the individual serial.
In our scenario we will receive item bmwlpnserial into LPN A using miscellaneous receipt. We are going to receive 10 quantity of the item into subinventory FGI and locator'1.1.1'. We have generated the LPN using Generate LPN program through Warehouse Manger responsibility and the concurrent program is under Other> Requests > Run. You can check the LPN number in the log file of the Generate LPN program.
Below is the sql to check the LPN information then Generate LPN program.
SQL> select lpn_id,license_plate_number,lpn_content
2 from wms_license_plate_numbers
3 where license_plate_number = 'LPN1A';
LPN_ID LICENSE_PLATE_NUMBER LPN_CONTEXT
--------- ------------------------------ -----------
128 LPN1A 5
Below is the sql to find out the meaning for the LPN content.
SQL> select lookup_code, meaning
from mfg_lookups
where lookup_type ='WMS_LPN_CONTEXT'
order by lookup_code;
LOOKUP_CODE MEANING
----------- --------------------------------------------------------------------------------
1 Resides in Inventory
2 Resides in WIP
3 Resides in Receiving
4 Issued out of Stores
5 Defined but not used
6 Resides in Intransit
7 Resides in Vendor
8 Packing content
9 Loaded to Dock
10 Prepack for WIP
11 Picked
We are going to use LPN A which has a content 5 Defined but not used .
SQL> select transaction_type_id,transaction_type_name,description from mtl_transaction_types
where transaction_type_name = 'Miscellaneous receipt';
TRANSACTION_TYPE_ID TRANSACTION_TYPE_NAME
------------------- ------------------------------
DESCRIPTION
----------------------------------------------------------------------------------------------------
42 Miscellaneous receipt
Perform miscellaneous receipt of material
SQL> select inventory_item_id , segment , organization_id
from mtl_system_items_b
where segment1 = 'bmwlpnserial'
and organization_id = 1884;
Insert into mtl_transactions_interface(
transaction_uom,
transaction_date,
source_code,
source_line_id,
source_header_id,
process_flag ,
transaction_mode ,
lock_flag ,
locator_id ,
last_update_date ,
last_updated_by ,
creation_date ,
created_by ,
inventory_item_id ,
subinventory_code,
organization_id,
transaction_quantity ,
primary_quantity ,
transaction_type_id ,
dst_segment1,
dst_segment2,
dst_segment3,
dst_segment4,
dst_segment5,
transaction_interface_id,
transfer_lpn_id
)
VALUES (
'Ea', --transaction uom
SYSDATE, --transaction date
'Miscellaneous Receipt', --source code
99, --source line id
99, --source header id
1, --process flag
3 , --transaction mode
2 , --lock flag
1291 , --locator id
SYSDATE , --last update date
0, --last updated by
SYSDATE , --creation date
0, --created by
11821 , --inventory item id
'FGI',--From subinventory code
1884, --organization id
10, --transaction quantity
10, --Primary quantity
42, --transaction type id
'01', --segment1 account combination
'520', --segment2 account combination
'5250', --segment3 account combination
'0000', --segment4 account combination
'000', --segment5 account combination
999, --transaction interface id
128); --transfer lpn id
Insert into mtl_serial_numbers_interface
(transaction_interface_id,
fm_serial_number,
to_serial_number,
last_update_date,
last_updated_by,
creation_date,
created_by)
values
(999, --transaction interface_id
'SR00001' , --from serial number
'SR00010', --to serial number
sysdate, --last update date
0, --last updated by
sysdate, --creation date
0 --created by
);
commit;
After the import when you check the content of lpn LPN1A you will see that the content has changed to Resides in Inventory .
