SET QUOTED_IDENTIFIER OFF
GO
/**
*    $File: //asd/branches/buff/MoversSuite/Common/SQL/Views/MaterialItemCodeMapIDsInUse.sql $
*    $DateTime: 2010/01/20 18:02:09 $
*    $Change: 27813 $
*    $Revision: #2 $
*    $Author: ssaad $
*    
*    Description: This view returns a list of MaterialItemCodeMap primary key's that are referenced elsewhere
*
*/

CREATE view [dbo].[MaterialItemCodeMapIDsInUse]
(
    MaterialItemCodeMapID,
    MaterialTypeID
)
as

select
    MaterialItemCodeMapID = MaterialItemCodeMap.MaterialItemCodeMapID,
    MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
from MaterialItemCodeMap
inner join LSMaterial on LSMaterial.MaterialItemCodeMapFID = MaterialItemCodeMap.MaterialItemCodeMapID

union

select
    MaterialItemCodeMapID = MaterialItemCodeMap.MaterialItemCodeMapID,
    MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
from MaterialItemCodeMap
inner join OrdMaterial on OrdMaterial.MaterialItemCodeMapFID = MaterialItemCodeMap.MaterialItemCodeMapID

union

select
    MaterialItemCodeMapID = MaterialItemCodeMap.MaterialItemCodeMapID,
    MaterialTypeID = MaterialItemCodeMap.MaterialTypeFID
from MaterialItemCodeMap
inner join XmlItemCode on XmlItemCode.MaterialTypeFID = MaterialItemCodeMap.MaterialTypeFID
GO
GRANT SELECT ON  [dbo].[MaterialItemCodeMapIDsInUse] TO [MssExec]
GRANT INSERT ON  [dbo].[MaterialItemCodeMapIDsInUse] TO [MssExec]
GRANT DELETE ON  [dbo].[MaterialItemCodeMapIDsInUse] TO [MssExec]
GRANT UPDATE ON  [dbo].[MaterialItemCodeMapIDsInUse] TO [MssExec]
GO