Download - Inventory Finalpdf
Inventory
1
Sno Table Of Contents Sub Topics Page NO.
1 Hierarchy Organization Levels1.1 Creation of Master Organization 21.2 Creation of Child Organization 2
2 Item Creation2.1 Item Creation without Template 32.2 Assigning Item to Child Organization 42.3 Item Creation with Predefined Template 72.4 Creating Item with an existing Item 8
3 Creating Template3.1 Creating template using existing Template 93.2 Creating template Manually 10
4 Inventory Controls 11Master Level Organization Level
5 Types of Inventory Controls5.1 Serial Control 125.2 Lot Control 16 5.2.1 Lot control with user defined lot expiration 18
205.3 Locator Control 5.3.1 Prespecified Locator Control 23 5.3.2 Dynamic Entry Locator Control 245.4 Revision Control 26
6 Creating Sub Inventory 287 On-Hand, Availability 308 Inventory Transaction Flow
8.1 Sub-Inventory Transfer 328.2 Inter Organization Transfer 348.3 Miscellaneous Transfer 368.4 Move Order 38
9 Bill Of Materials (BOM)9.1 Creating items 409.2 Assigning Cost to Items 41
10 Creating Bill of Material (BOM) 4210.1 Intended Bill 4410.2 Cost Roll-up 4510.3 Standard Cost roll-up 4610.4 Alternate Bill 4810.5 Common Bill 5010.6 Copy Bill from one organization to another 51
11 Routings11.1 Resources 5211.2 Departments 5311.3 Department Class 5411.4 Routing 5511.5 Standard Cost Update 5811.6 Alternate Routing 6011.7 Copy Routing 62
Techiniacal part of inventory 63Bill Of Materials (BOM) 66Routing 67
5.2.2 Lot control with shelf life day control
1. HIERARCHY ORGANIZATION LEVELS:
1.1 Creation of master organization1.2 Creation of child organization
Responsibility : Inventory Navigation : Setup->Organization->Organizations.
→ Click on New to create Master Organization Give the name of the organization and organization classifications
2
2. ITEM CREATION
2.1 Creation of item without Template
Navigation: Items->Master Items
3
2.2 Assignment of master item to organization level.
Tools->Organization Assignments Check Assigned check box for M1 and M2 (Save)
4
→ Identify the item in organization level.
Navigation:Items->Organization Item
Click on FIND
5
6
2.3 Item creation with predefined template
Navigation : Items->Master Items
After giving the item and description click on Tab Tools->Copy From Select Template: Finished Good
→ Then click on Apply and Done
→ You can see User ItemType: Finished Good in above screen shot
→ Save the item (CTRL+S)
→ Assign The item to Child Organization as shown above.
7
2.4 creation of item with an existing item:
Navigation : Items->Master Items
→ After giving the Item and Description → Tools->Copy From
→ select Item : select an existing item
→ Click on Apply and done
→ Save the item (Ctrl+s)
→ Assign the above item to Organization as above
8
3. CREATING TEMPLATE
3.1 creating user defined template using existing template.
→ Navigation :setup->items->Templates
→ click on new
→ type the template name in template text field and Description about template → Tools -> Copy Template. Click Apply and Done
→ now select existing
→ Save it(Ctrl+S).
9
3.2 Creating Template manually
→ Navigation :Setup->items->Templates
→ Click on New
→ Fill the Template and Description
→ And Select the Attributes which ever you want by enabling it.
→ Save it(Ctrl+S).
10
4. INVENTORY CONTROLS
→ There are Different types of Controls, which categorized into two types
Master Level Inventory Controls
Organization Level Controls
→ We can see the different levels in below screen shot.
11
5. TYPES OF INVENTORY CONTROL
5.1 Serial control
→ Create an item Invetory_item1 at Master item level ans assingn it to M1 Go to Items --> Organization Items Find Item : Inventory_item1. Below screen will be open . Select Inventory tab. In serial, select Generation : Predefined
Starting Prefix : AAStarting Number: 001
and save it(Ctrl+s).
Close the window and after this we need to generate the serial numbers
→ Go to On-Hand Availability -> Generate Serial Number. Enter your item name and quantity and press Ok and then submit . A request will be sent for the generation of serial number.
12
→ You can check the status of the request by clicking View -> Request-> find.
Now go to Transactions -> Miscellaneous Transactions
following screen appears , select Type: Miscellaneous Receipts and click the button Transaction Lines
→ the following screen appears, select your item , sub inventory and enter quantity, select account as Miscellaneous.
13
→ Click Lot/Serial button in the bottom Select the Starting serial number (search for the serial number you created ) and click Done
→ Check for the on hand available quantity.
→ Go to On-hand Availability -> On-hand Quantity in Item tab, enter your item name and click find. The following screen appears check the availabilty quantity and serial numbers .
14
15
5.2 lot control
→ create an item inventory_item_lot and assign in to M1 at Master item level
→ Go to Items --> Organization Items
Find Item : in ventory_item_lot. Below screen will be open . Select Inventory tab. In Lot, select Full Control and save it.
→ Now go to Transactions -> Miscellaneous Transactions
following screen appears , select Type: Miscellaneous Receipts and click the button Transaction Lines
→ Then the following screen appears, select your item , sub inventory and enter quantity, select account as Missellaneous
16
→ Click Lot/Serial button in the bottom Enter the Lot number AA(in this example) , enter the quantity and click Done and save(Ctrl+s)
→ Check for the online available quantity.
Go to On-hand Availability -> On-hand Quantity in Item tab, enter your item name and click find. The following screen appears check the available quantity and observe the Lot number.
17
5.2.1 Lot control with user defined lot expiration
create an item inventory_item_lot1 and assign in to M1 at Master item level Go to Items --> Organization Items
Find Item : inventory_item_lot1. Select Inventory tab. In Lot, select Full Control and in Lot Expiration(Shelf Life) tab, select control : User define and save it(Ctrl+s).
Now go to Transactions -> Miscellaneous Transactions
select Type: Miscellaneous Receipts and click the button Transaction Lines
select your item , sub inventory and enter quantity, select account as Missellaneous
Click Lot/Serial button in the bottom
Enter the Lot number AA1(in this example) , enter expiration date, enter the quantity and click Done and save.
18
Enter the Lot number AA1(in this example) , enter expiration date, enter the quantity and click Done and save.
Check for the on hand available quantity.
Go to On-hand Availability -> On-hand Quantity in Item tab, enter your item name and click find. The following screen appears check the available quantity and observe the Lot number and expiry date
19
5.2.2 Lot control with shelf life day control
create an item inventory_item_lot2 and assign in to M1 at Master item level Go to Items --> Organization Items
Find Item : inventory_item_lot2. Select Inventory tab.In Lot, select Full Control and in Lot Expiration(Shelf Life) tab, select control : Shelf Life days Shelf Life Days : 30 and save it.
Now go to Transactions -> Miscellaneous Transactions
select Type: Miscellaneous Receipts and click the button Transaction Lines
select your item , sub inventory and enter quantity, select account as Missellaneous
Click Lot/Serial button in the bottom
Enter the Lot number AA1(in this example) , here the expiration date will auotmatically be generated which is 30 days after date, enter the quantity and click Done and save(Ctrl+s).
20
→ Check for the on hand available quantity.
Go to On-hand Availability -> On-hand Quantity in Item tab, enter your item name and click find. The following screen appears check the available quantity and observe the Lot number and expiry date.
21
22
5.3 locator control
5.3.1 Prespecified Locator control
Change Organization to M1 Create an item inventory_item_loc and assign in to M1 at Master item level Go to Items --> Organization Items Find Item : inventory_item_loc. Select Inventory tab. select Locator Control : Prespecified
Now go to Transactions -> Miscellaneous Transactions
select Type: Miscellaneous Receipts and click the button Transaction Linesselect your item , sub inventory (FGI), click on the locator the following screen appears
click on the combinations. Search for the available locators by entering % , a list of available locators will be displayed, select any one. Enter quantity and account
→ Check for the on hand available quantity.
Go to On-hand Availability -> On-hand Quantityin Item tab, enter your item name and click find. The following screen appears check the available quantity and observe the Locator
23
5.3.2 Dynamic Entry Locator
Change organization to P3
Create an item inventory_dynamic_loc and assign in to P3 at Master item level Go to Items --> Organization Items Find Item : inventory_dynamic_loc. Select Inventory tab. select Locator Control : Dynamic Entry
Now go to Transactions -> Miscellaneous Transactions
select Type: Miscellaneous Receipts and click the button Transaction Lines select your item , sub inventory (FGI), click on the locator the following screen appears, here we can enter the locator manually and click ok. Enter quantity and account as inventory adjustments
24
→ Check for the on hand available quantity.
Go to On-hand Availability -> On-hand Quantityin Item tab, enter your item name and click find. The following screen appears check the available quantity and observe the Locator
25
5.4 REVISION CONTROL
→ Change organization to M1
Create an item inventory_sample_rev and assign in to M1 at Master item levelGo to Items --> Organization Items Find Item : inventory_sample_rev. Select Inventory tab.Check the Revision Control box and click the revision icon beside it , the following screen appears
give a new name to revision as A1 and effective date, save and exit
Now go to Transactions -> Miscellaneous Transactions
select Type: Miscellaneous Receipts and click the button Transaction Linesselect your item , click the revision button and select the revision , sub inventory , enter quantity and account . Save(Ctrl+S) and exit
Check for the on hand available quantity.
Go to On-hand Availability -> On-hand Quantityin Item tab, enter your item name and click find. The following screen appears check the available quantity and observe the revision
26
27
6. CREATING SUB INVENTORY
Navigation: Setup ->Organisations->Subinventories
Type the Subinventory name in name field save it(Ctrl+s)
28
29
7. ON HAND AVAILABILITY
→ Navigation: On-hand,Availability -> On-hand Quantity
→ Give the Item Name item/Revision Field.
30
→ click on Availability to check the availability.
31
8. INVENTORY TRANSACTION FLOW
8.1 Sub-Inventory Transfer
→ Navigation :Transactions->Subinventory Transfer
→ select Type : SubInventory Transfer
→ Click on Transaction Lines
32
→ Fill the details : Item,Subinv,To Subinv,UOM,Quantity and Save it(Ctrl+s).
→ By Using On hand Availability,We can see the Received Items.
33
8.2 Inter Organization Transfers
→ Navigation: Transactions->Inter-organization Transfer
→ Fill the details: Date,To Org,Type,Number(Any Unique Number).
→ Click on Transaction Lines
34
→ Fill the Fields: Item,Sub inventory,To-Sub inventory,UOM,Quantity and Save it(Ctrl+s).
→ switch to organization to which you have made the transaction( M2 in this example)
→ By Using On hand Availability,We can see the Received Items.
35
8.3 Miscellaneous Transfer
→ Navigation: Transactions->Miscellaneous Transaction
→ select Type : miscellaneous receipts
→ Click on Transaction Lines
→ Fill the details:Item,Sub inventory,UOM,Quantity,Account As below
36
→ By Using On hand Availability,We can see the Received Items.
37
8.4 MOVE ORDER
→ Navigation : Move Orders->Move Orders
→ Fill the details as above Number(any unique number) and Click on Approve.
→ After click on the approve we can see status changed to Approved as below
38
→ By Using On hand Availability,We can see the Received Items.
39
9. BILL OF MATERIALS
BOM:Collection of items to make one finished good
9.1 Create a item (organization V1)
Navigation: Inventory -> Items -> Master Item Items:Computer Similarly Create items CPU,Floppy,Hard Drive,Monitor,Key Board,Mouse,Cabinet,CD Drive
40
9.2 Assigning Cost to Items
Assigning Cost will be at org level, change to M1 org
Inventory-->Items->Orgnization Item
Find Item :FLOPPY
Goto Tools->Cost items->Cost
Similary define cost for CD Drive,Cabinet,Mother Board,Processor,Mouse,Key Board and Monitor
41
10. Create Bill of Material
Select M1 Organization
Bills of Material --> Prototypes -> Bills -> Bills Item:CPU
To the item CPU assign components Floppy Drive,CD Drive,Hard Disk,Mother Board and Processor
Similarly ,for Item Computer assign components CPU,Mouse,Key Board,Monitor.
42
43
10.1 Intended Bill
Intended bill displays the structure of a manufacturing or engineering item.
Navigation: BOM-->Bills->Bills
Select a item:Computer
Tools-> Intended Bills
44
10.2 Cost Roll-up
BOM-->Bills->Bills
Item:CPU
Tools->Roll up cost->Cost Type : Pending (OK)
Similarly do Roll up Cost for Computer
45
10.3 Standard Cost roll up
Inventory ->Costs->Standard Cost Update->Update
Press Ok and submit it...
Similarly Update Frozen cost of Computer
46
To Check Frozen Cost(M1 org)
Inventory-->Items->Organizations Items Item:CPU(Find) Tools->Item Costs
Similary Find the Frozen Cost for Computer
47
10.4 Creating Alternate Bill
An alternate bill describes an alternate list of component items that produce an assembly.
BOM-->Setup->Alternates
Enter an alternate name , Description and save it
48
BOM-->Bills->Bills
Give, Item :Computer Alternate:ALT( the alternate name which we created above)
Now,Assign components CPU and Monitor to the item Computer. Go to Material tab and give Supply type as Phantom for component CPU
Now do Cost Roll Up , Standard Cost Roll up and Update Frozen Cost with Pending Cost same as the above
49
10.5 Create a Common Bill
A BOM contains information on the parent item, components, attachments, and descriptive elements. Each standard component on a bill can have multiple reference designators and substitute components. BOM-->Bills->Bills
Give, Item:Computer (query it) Alt :ALT Tools->Create Commomn Bills Organization :M2 Item :Computer(Press Ok)
50
Now change the organization to M2 and goto
BOM-->Bills->Bills Give Item: Computer and check
10.6 Copy Bill from M1 organization to M2 organization
Change organization to M2
BOM-->Bills->Bills Item:CPU Tools->Copy Bill from Orgnization :M1 Item :CPU (Press Copy then Ok)
51
11. ROUTING
11.1 Creating Resource:
Select Bill of Material responsibility
Go to Routings - > Resources . The following screen appears. 1. Enter Resource name, Type and UOM
similarly you can add any no. of resources
52
11.2 Assigning Resource to Department: Go to Routings - > Departments. The following screen appears. Query for department. Click on Resources for assigning resources to Department.
Already 3 existing resources are displayed enter your resource by searching it. And enter the Units
53
similarly we can assign any no. of resources to any no. of departments
11.3 Department Classes
Go to Setup - > Department Classes. Select any one class and click Departments.
We can see the departments available in this class.
54
11.4 Creating routing:
Go to Routings - > Routings. The following screen appears. Query for your item .In the Main tab, enter the Department details
add department and add resources by clicking Operation resources
55
similarly add remaining departments after adding departments, click on Operation Resources and enter the resources details .Following the same for each department.
Now Roll up the cost Go to Bills - > Bills. select your item and go to tools:Roll up costs Select your item and go tools - > rollup costs
56
select cost type as Pending and then click on OK
check whether your item Pending cost updated or not:
Tools - > Item costs
here we can see the Pending cost and frozen cost.
57
11.5 Standard Cost Update
Go Back to Inventory Responsibility: Costs - > Standard Cost Update - > Update Costs select cost type : Pending. Give Description Item Range : Specific Item Update Option: Item costs only and Click on OK and Submit
58
Now run the standard Update cost program . Cost - > standard Cost Update - > Update Cost. Query your item and see that the frozen cost and pending cost are updated as follows
59
11.6 Create Alternate Routing
Go to Bill Of Material Responsibility Setup - > Alternates Give Your Alternate Name and Description and save it
60
Go back to Routings and Give Your item name and Alternative name ,then add your departments:
Here we will add only one department or you can add many department to make it different from the original routing.Then Roll up Costs and go to inventory responsibility and Run Standard Cost Update Program as said above for Updating Your Frozen Cost to Pending Cost.
61
11.7 Copy Routing
Change organization to M2
Bill Of Material (Responsibility)
Go to Routing - > Routing. Enter the item name for which you want to copy the routine Go to Tools - > copy routing from. The following screen appears. Select Organization: M1 Item : item name for which you have already created routing select copy option: current . Click Copy
62
TECHNICAL PART OF INVENTORY
MTL_SYSTEM_ITEMS_B : Base Table for inventory stores.,It stores all details related to an item created.Segment1 stores the item name, inventory_item_id is the primary_id column
➔ SELECT * FROM MTL_SYSTEM_ITEMS_B;
MTL_PARAMETERS : It Stores the details of organizations,organization_id is primary_id
➔ SELECT * FROM MTL_PARAMETERS;
Relation between MTL_SYSTEM_ITEMS_B & MTL_PARAMETERS
➔SELECT mp.organization_code,msib.* FROM mtl_system_items_b msib, mtl_parameters mp WHERE 1=1 AND segment1='computer' AND msib.organization_id=mp.organization_id
MTL_MATERIAL_TRANSACTIONS: This Table Stores the details of all the Transactions. Transaction_id is the main Column.
➔SELECT * FROM mtl_material_transactions mmt
MTL_MATERIAL_TRANSACTION_TYPES: This table stores the details about all the Transaction types,Transaction_id is the main column.
63
➔SELECT transaction_type_name,a.* FROM mtl_transaction_types a WHERE transaction_type_id=32
MTL_SERIAL_NUMBERS: stores details of all serail numbers assigned to items, join b/w mtl_system_items_b and mtl_serial_numbers is based on inventory_item_id.
➔SELECT * FROM mtl_serial_numbers;
Pulls the details an item, organization and all serial numbers and shows joins used
➔SELECT mmt.transaction_id, msib.inventory_item_id, msn.serial_number, mtt.transaction_type_name, msib.segment1 "Item Name", mp.organization_code "Organization Code" FROM mtl_material_transactions mmt, mtl_system_items_b msib, mtl_parameters mp, mtl_transaction_types mtt, mtl_serial_numbers msn WHERE mmt.transaction_type_id = 32 AND mmt.inventory_item_id = msib.inventory_item_id AND mmt.organization_id = msib.organization_id AND msib.organization_id = mp.organization_id AND mmt.transaction_type_id = mtt.transaction_type_id AND mmt.transaction_id = msn.last_transaction_id AND TRUNC (mmt.creation_date) >= '16-MAY-2011'
MTL_LOT_NUMBERS: This Table Stores the Lot numbers Assigned to Item
➔SELECT * FROM mtl_lot_numbers
MTL_ONHAND_QUANTITIES: This table Stores the available on hand quantities of Items.➔SELECT * FROM mtl_onhand_quantities
➔SELECT * FROM mtl_item_locations WHERE TRUNC(creation_date)>='15-MAY-2011'MFG_LOOKUPS: This table stores all data about lookup informations this shows type of locator control
64
➔SELECT * FROM mfg_lookups WHERE lookup_type LIKE 'MTL_LOCATION_CONTROL'
MTL_SECONDARY_INVENTORIES: This Table stores the details of all sub inventories.
➔SELECT * FROM mtl_secondary_inventories
MTL_ITEM_REVISIONS: This table Stores the details of all the item revisions.
➔SELECT * FROM mtl_item_revisions WHERE TRUNC(creation_date)=TRUNC(SYSDATE) AND inventory_item_id=11881
65
BOM TABLES:
BOM_BILL_OF_MATERIALS: This Table Stores the Information about all the Bills.
➔SELECT * FROM bom_bill_of_materials
BOM_INVENTORY_COMPONENTS: This Table Stores the data about all the components which a are used in the bills.
➔SELECT * FROM bom_inventory_components WHERE bill_sequence_id=45255
CST_ITEM_COSTS: This Table Stores the data about all the item costs, cost_type_id and inventory_id are main Columns.
➔SELECT * FROM cst_Item_costs WHERE inventory_item_id=11882 AND organization_id=207
CST_COST_TYPES: This Table Stores the Data about different Cost Types.
➔SELECT * FROM cst_cost_types
66
ROUTING TABLES
BOM_OPERATIONAL_ROUTINGS: This Table stores the details of routing for an assembly item,assembly_item_id is same as inventory_item_id , routing_sequence_id is a primary column.
➔ SELECT * FROM bom_operational_routings
BOM_OPERATION_SEQUENCES: This Table store the details of routing sequences. routing_sequence_id is used to join with bom_operational_routings.
➔SELECT * FROM bom_operation_sequences
BOM_OPERATION_RESOURCES: This Table store the details of operation resources for a routing operation_sequence_id is used to join bom_operation_sequences.
➔SELECT * FROM bom_operation_resources
BOM_DEPARTMENTS: This Table stores the details of bom departments
➔SELECT * FROM bom_departments
BOM_RESOURCES: This Table Stores the details of resources
➔SELECT * FROM bom_resources
BOM_DEPARTMENT_CLASSES: This Table Stores details of department_classes
67
➔SELECT * FROM bom_department_classes
BOM_STANDARD_OPERATIONS: This Table Stores details of standard bom operations
➔SELECT * FROM bom_standard_operations
68