SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

simpliienigmatic
Starting Member

Canada
2 Posts

Posted - 03/06/2013 :  22:46:50  Show Profile  Reply with Quote
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)

simpliienigmatic
Starting Member

Canada
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

visakh16
Very Important crosS Applying yaK Herder

India
52325 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
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.56 seconds. Powered By: Snitz Forums 2000