Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Master and child item
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

2 Posts

Posted - 03/06/2013 :  22:46:50  Show Profile  Reply with Quote

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)

Starting Member

2 Posts

Posted - 03/06/2013 :  22:49:37  Show Profile  Reply with Quote
organization_id inventory_item_id status
100 32412 Active
101 32412 Active
102 32412 Active
103 32412 Inactive
104 32412 Inactive
105 32412 Inactive
106 32412 Active
107 32412 Inactive
100 76146 Active
101 76146 Inactive
102 76146 Inactive
103 76146 Inactive
104 76146 Inactive
105 76146 Inactive
106 76146 Inactive
107 76146 Inactive

Sorry,could not attach the image file.
I would like the query to fetch me the second item(76146)at master level as it is inactive at all other level
Go to Top of Page

Very Important crosS Applying yaK Herder

52326 Posts

Posted - 03/06/2013 :  23:13:59  Show Profile  Reply with Quote
sorry cant understand how you define master child information. There's nothing in above table which determines which is master and which is child. you say 100 represents master level but thats just one value. what about other master level values? or is it that always there's one nad only master which is fixed at 100?

SQL Server MVP

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000