|
simpliienigmatic
Starting Member
Canada
2 Posts |
 Posted - 03/06/2013 : 22:46:50
|
Hi,
I got a requirement where my client asked me to give the list of inventory items as mentioned below
In Oracle Item Master,the item is registered at master level(MAT) and is being assigned to the child organization. The records are created in MTL_SYSTEM_ITEMS_B. The unique record is determined based on the combination of inventory_item_id and organization_id.
There are 7 child organizations and 1 master organization created in our test instance. There are approx 5500 items in the system(created once at master level,but assigned to 1 or many child organizations, so the max count of records will be (7+1)*5500=44000. There is one column called inventory_item_status_code.The Status of each item at Master level and child level may be same or different.
My client wants the list of all items which are with active status at Master level,but not active in any of the child organizations. If the item is active in any of the child org level,then the record should not be considered. Can somebody help me in building the query..??
Attached is the list of two items at different level(master and child).inventory_item_id=100 is the master organization while rest are child orgs.
The query should fetch me the record in blue color only at master level(=100) |
|
Master and child item
