kırılım yapısı(3 alt kırılım-FERRARI) : Tüm Malzemeler -> Kırılım1-> Kırılım2-> Kırılım3 -> Kırılım3 Malzemeleri
Kampanya yapısı(2 alt kırılım-KMP) : Tüm Malzemeler -> Kırılım1-> Kırılım2 -> Kırılım2 Malzemeleri
create view [RM_FERRARI_MALZEME_SINIFLARI]
as
select top 100 percent [Type], [Level1Ref] = case when [Level1Ref] = 1 then Level2Ref else Level1Ref end, [Level1] = case when [Level1Ref] = 1 then Level2 else Level1 end, [Level2Ref] = case when [Level1Ref] = 1 then Level3Ref else Level2Ref end, [Level2] = case when [Level1Ref] = 1 then Level3 else Level2 end, [Level3Ref] = case when [Level1Ref] = 1 then 0 else [Level3Ref] end, [Level3] = case when [Level1Ref] = 1 then '' else [Level3] end, [ItemRef], [Code], [Name], [Active]
from (
select [Type] = case when ITMSC.CARDTYPE = 20 then 'Genel Malzeme Sınıfı' else 'Malzeme' end, [Level1Ref] = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
), [Level1] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
)
), [Level2Ref] = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
), [Level2] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = (
select top 1 LGMAIN2.PARENTREF
from LG_101_ITMCLSAS LGMAIN2
where LGMAIN2.CHILDREF = LGMAIN.PARENTREF
)
), [Level3Ref] = LGMAIN.PARENTREF, [Level3] = (
select I2.CODE + '-' + I2.name
from LG_101_ITEMS I2
where I2.LOGICALREF = LGMAIN.PARENTREF
), [ItemRef] = ITMSC.LOGICALREF, [Code] = ITMSC.CODE, [Name] = ITMSC.name, [Active] = ITMSC.ACTIVE
from LG_101_ITMCLSAS LGMAIN with (nolock)
left outer join LG_101_ITEMS ITMSC with (nolock) on (LGMAIN.CHILDREF = ITMSC.LOGICALREF)
) as T
where [Level1] = 'MS01-FERRARI' or Level3 = 'MS01.16-KMP'
order by Level1, Level2, Level3
Blog of Sem 