Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
simpliienigmatic
Starting Member
2 Posts |
 Posted - 2013-03-06 : 22:46:50
|
| Hi,I got a requirement where my client asked me to give the list of inventory items as mentioned belowIn 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
2 Posts |
Posted - 2013-03-06 : 22:49:37
|
| organization_id inventory_item_id status100 32412 Active101 32412 Active102 32412 Active103 32412 Inactive104 32412 Inactive105 32412 Inactive106 32412 Active107 32412 Inactive-----------------------------------100 76146 Active101 76146 Inactive102 76146 Inactive103 76146 Inactive104 76146 Inactive105 76146 Inactive106 76146 Inactive107 76146 InactiveSorry,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 |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-03-06 : 23:13:59
|
| 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Master and child item