Simple Query To Fetch Item Information

SELECT

     substr(inv.segment1,1,7) "Item",

     substr(inv.inventory_item_id,1,4) "Id",

     inv.bom_enabled_flag,

     inv.build_in_wip_flag,

     inv.customer_order_enabled_flag,

     inv.internal_order_enabled_flag,

     inv.invoiceable_item_flag,

     inv.mtl_transactions_enabled_flag,

     inv.purchasing_item_flag,

     inv.stock_enabled_flag,

     substr(inv.receiving_routing_id,1,2) "Rte ID",

     substr(inv.list_price_per_unit,1,2) "Lst Pr"

FROM

     mtl_system_items_B inv

WHERE

     inv.segment1 LIKE 'XX%' AND

     inv.organization_id = "Org_Id" 

Creating Different Type of Items

Finished Good Item

1.    Enter the following information, within the Master Item window:

Field Name	Value
Item Number	XX 1000
Description	FINISHED GOOD

2.    Apply the Finished Good template.

3.    Select the Work In Process tab. Notice that the template applied the following attributes:

-        Build in WIP = Yes

-        Supply Type = Push

4.    Assign the Finished Good Item to the M1 Seattle Manufacturing Organization

Phantom Item

5.    Enter the following data, within the Master Item window:

Field Name	Value
Item Number	XX 2000
Description	PHANTOM

6.    Apply the Phantom Item template.

7.    Select the Work In Process tab. Notice that the template applied the following attributes:

-        Build in WIP = Yes

-        Supply Type = Phantom

8.    Assign the Phantom Item to the M1 Seattle Manufacturing Organization.

Purchased Item (Push)

9.    Enter the following data, within the Master Item window:

Field Name	Value
Item Number	XX 1001
Description	XX BUY 1

10.  Apply the Purchased Item template.

11.  Select the Purchasing tab and enter the following information:

-        List Price = 40

12.  Select the Receiving tab and enter the following information:

-        Receipt Routing = Inspection

13.  Select the Work In Process tab and enter the following information:

-        Build In WIP = Yes

-        Supply Type = Push

14.  Assign the Purchased Item to the M1 Seattle Manufacturing Organization.

Purchased Item (Operation Pull)

15.  Enter the following data, within the Master Item window:

Field Name	Value
Item Number	XX 2002
Description	XX BUY 2

16.  Apply the Purchased Item template.

17.  Select the Purchasing tab and enter the following information:

-        List Price = 80

18.  Select the Work In Process tab and enter the following information:

-        Build In WIP = No

-        Supply Type = Operation Pull

19.  Assign the Purchased Item to the M1 Seattle Manufacturing Organization.

Thanks & Regards,

S.Grace Paul Regan

Simple Query to fetch Subinventory & Stock Locator Information

SELECT

     substr(matsub.secondary_inventory_name,1,10) "SubInv",

     substr(matsub.description,1,20) "Descripton",

     substr(matloc.description,1,20) "Locator",

         substr(matloc.inventory_location_type,1,1) "Locator Type",

     substr(matloc.inventory_location_ID,1,4) "Locator ID"

FROM

     mtl_secondary_inventories matsub,

     mtl_item_locations matloc

WHERE

     matsub.secondary_inventory_name LIKE ‘&XX_SUBINVENTORY%' AND

     matsub.organization_id = 207 AND

     matloc.organization_id = 207 AND

     matsub.secondary_inventory_name = matloc.subinventory_code

Thanks & Regards,

S.Grace Paul Regan