select t1.IDPrdct, t1.cd, convert(nvarchar(100), t1.Dscr) Dscr, convert(nvarchar(255), t1.Dscr_Uk) Dscr_Uk, case t1.PrdctKind when 11 then null else t1.spCode end spCode, case t1.PrdctKind when 8 then 0 when 9 then 2 when 11 then 1 when 13 then 2 else 0 end tp, case t1.PrdctKind when 1 then 0 when 2 then 0 when 3 then 5 when 4 then 0 when 6 then 2 when 7 then 1 when 8 then 0 when 9 then 7 when 10 then 0 when 11 then 6 when 16 then 3 when 12 then 0 when 31 then 0 end clsfctn, case when isnull(t2.isRtrn, 0) = 1 then 2 when t1.PrdctKind = 1 then 3 when t1.PrdctKind = 12 then 4 else 0 end knd, case when isnull(t2.isRtrn, 0) = 1 then 0 else null end rtrnBhvr, case t1.Sts when 0 then 0 else 1 end actv, case t1.Sale when 0 then 1 else 0 end blckSales, case when t1.PrdctKind in (1, 12) then 1 else case t1.Buy when 0 then 1 else 0 end end blckPurc, isnull(t1.IDVCls, vclsAlter.IDVCls) IDVCls, t1.IDILedg, case t1.SNCls when 0 then 0 else 1 end snTrck, case t1.SNCls when 0 then null else 0 end snCons, case t1.SNCls when 0 then null else 0 end snConsOrdr, case t1.LotCls when 0 then 0 else 1 end lotTrck, case t1.LotCls when 0 then null else 0 end lotCons, case t1.LotCls when 0 then null else 0 end lotConsOrdr, t1.IDMsr, case when t1.SNCls != 0 or t1.PrdctKind = 11 then null else t1.IDScMsr end IDScMsr, case when t1.SNCls != 0 or t1.PrdctKind = 11 then 0 else case when t1.IDScMsr is null then 0 else 3 end end msntrelation, case when t1.SNCls != 0 or t1.PrdctKind = 11 then null else case when t1.IDScMsr is null then null else 0 end end msntCalcType, case when t1.SNCls != 0 or t1.PrdctKind = 11 then null else case when t1.IDScMsr is null then null else t1.Conv end end Conv, isnull(t1.CostModel, 0) stdCst, case when t1.PrdctKind = 11 then null when isnull(t1.Clng, 0) < isnull(t1.ClngOrdr, 0) then isnull(t1.ClngOrdr, 0) else t1.Clng end safetyStck, case when t1.PrdctKind = 11 then null when t1.ClngOrdr is null and t1.Clng is not null then t1.Clng else t1.ClngOrdr end reorderStck, case t1.PrdctKind when 11 then null else t1.Gauge end lngth, case t1.PrdctKind when 11 then null else t1.ItemHeight end hght, case t1.PrdctKind when 11 then null else t1.ItemWidth end wdth, case t1.PrdctKind when 11 then null else t1.FrmlWeight end wght, case t1.PrdctKind when 11 then null else t1.FrmlVolume end vol, case t1.PrdctKind when 11 then null else t1.IDMainSplr end IDMainSplr, case t1.PrdctKind when 11 then null else t1.IDMnfctr end IDMnfctr, case t1.PrdctKind when 11 then null else t1.IDComp end IDComp, t1.IDSeason, t1.AdjustRtl, t1.AdjustWhsl, isnull(t1.Discount, 0) Discount, convert(nvarchar(2000), t1.Cmnt) Cmnt, case t1.VarWeight when 1 then 1 else 0 end scaleTp, t1.CustomDscr1, t1.CustomDscr2, t1.CustomDscr3, t1.CustomDscr4, t1.fmlCd, t1.CustomDate1, t1.CustomDate2, t1.CustomInt1, t1.CustomInt2, t1.CustomDecimal1, t1.CustomDecimal2, case when t1.PrdctKind != 11 then t1.IDFree1 else null end IDFree1, case when t1.PrdctKind != 11 then t1.IDFree2 else null end IDFree2, case when t1.PrdctKind != 11 then t1.IDFree3 else null end IDFree3, case when t1.PrdctKind != 11 then t1.IDFree4 else null end IDFree4, case when t1.PrdctKind = 11 then t1.IDFree1 else null end IDFree1Srv, case when t1.PrdctKind = 11 then t1.IDFree2 else null end IDFree2Srv, case when t1.PrdctKind = 11 then t1.IDFree3 else null end IDFree3Srv, case when t1.PrdctKind = 11 then t1.IDFree4 else null end IDFree4Srv, t1.IDTree1, t1.IDTree2, t1.IDTree3, t1.IDTree4, t1.IDTree5, splrAdds.IDCoin, isnull(rtlNow.Amnt, rtlBefore.Amnt) rtlPrice, isnull(whlNow.Amnt, whlBefore.Amnt) whlPrice, t1.IDCat, t1.IDCatSub, t1.IDGrp, t1.IDGrpSub from (select p.IDPrdct, p.cd, p.Dscr, p.Dscr_Uk, i.spCode, p.PrdctKind, p.Sts, p.Sale, p.Buy, p.IDVCls, p.IDILedg, p.SNCls, p.LotCls, p.IDMsr, p.IDScMsr, p.Conv, p.CostModel, i.Clng, i.ClngOrdr, i.Gauge, i.ItemHeight, i.ItemWidth, i.FrmlWeight, i.FrmlVolume, p.IDMainSplr, i.IDMnfctr, i.IDComp, i.IDSeason, i.AdjustRtl, i.AdjustWhsl, p.Discount, p.Cmnt, p.VarWeight, p.CustomDscr1, p.CustomDscr2, p.CustomDscr3, p.CustomDscr4, f.Cd fmlCd, p.CustomDate1, p.CustomDate2, p.CustomInt1, p.CustomInt2, p.CustomDecimal1, p.CustomDecimal2, p.IDFree1, p.IDFree2, p.IDFree3, p.IDFree4, p.IDTree1, p.IDTree2, p.IDTree3, p.IDTree4, p.IDTree5, i.IDCat, i.IDCatSub, p.IDGrp, p.IDGrpSub from AI_Prdct p join I_Item i on i.IDItem = p.IDPrdct join AIC_FML f on p.IDFML = f.IDFml union all select p.IDPrdct, p.cd, p.Dscr, p.Dscr_Uk, p.spCode, p.PrdctKind, p.Sts, p.Sale, p.Buy, p.IDVCls, p.IDILedg, p.SNCls, p.LotCls, p.IDMsr, p.IDScMsr, p.Conv, p.CostModel, null Clng, null ClngOrdr, null Gauge, null ItemHeight, null ItemWidth, null FrmlWeight, null FrmlVolume, p.IDMainSplr, null IDMnfctr, null IDComp, null IDSeason, null AdjustRtl, null AdjustWhsl, p.Discount, p.Cmnt, p.VarWeight, p.CustomDscr1, p.CustomDscr2, p.CustomDscr3, p.CustomDscr4, f.Cd fmlCd, p.CustomDate1, p.CustomDate2, p.CustomInt1, p.CustomInt2, p.CustomDecimal1, p.CustomDecimal2, p.IDFree1, p.IDFree2, p.IDFree3, p.IDFree4, p.IDTree1, p.IDTree2, p.IDTree3, p.IDTree4, p.IDTree5, null IDCat, null IDCatSub, p.IDGrp, p.IDGrpSub from AI_Prdct p join AI_Set s on s.IDPrdct = p.IDPrdct join AIC_FML f on p.IDFML = f.IDFml) t1 left join (select dcsitm.IDPrdct, 1 isRtrn from SBP_DcsItm dcsitm where dcsitm.PrdType = 2 group by dcsitm.IDPrdct) t2 on t1.IDPrdct = t2.IDPrdct left join (select top 1 v.IDVCls, v.Dscr from FTV_VCls v where v.Dscr like '%23[%]%') vclsAlter on 1 = 1 left join (select adds.IDParty, adds.IDCoin from APA_ADDS adds where adds.ADDTP = 0) splrAdds on t1.IDMainSplr = splrAdds.IDParty left join (select rtl.IDRetailList, sbpdt.DtFrom, sbpdt.DtTo, sbpi.IDprdct, sbpi.Amnt from SBP_Item sbpi join (select g.IDRetailList from SBP_GPrm g where g.IDGPrm = 1) rtl on sbpi.IDList = rtl.IDRetailList join SBP_LstDt sbpdt on sbpi.IDList = sbpdt.IDList and sbpi.DtFrom = sbpdt.DtFrom where getdate() between sbpdt.DtFrom and isnull(sbpdt.DtTo, getdate())) rtlNow on t1.IDPrdct = rtlNow.IDprdct left join (select a.IDRetailList, b.DtFrom, b.DtTo, b.IDprdct, b.Amnt from (select rtl.IDRetailList, max(sbpdt.DtFrom) DtFrom from (select g.IDRetailList from SBP_GPrm g where g.IDGPrm = 1) rtl join SBP_LstDt sbpdt on rtl.IDRetailList = sbpdt.IDList where sbpdt.DtFrom < getdate() and not (getdate() between sbpdt.DtFrom and isnull(sbpdt.DtTo, getdate())) group by rtl.IDRetailList) a join (select rtl.IDRetailList, sbpdt.DtFrom, sbpdt.DtTo, sbpi.IDprdct, sbpi.Amnt from SBP_Item sbpi join (select g.IDRetailList from SBP_GPrm g where g.IDGPrm = 1) rtl on sbpi.IDList = rtl.IDRetailList join SBP_LstDt sbpdt on sbpi.IDList = sbpdt.IDList and sbpi.DtFrom = sbpdt.DtFrom) b on a.IDRetailList = b.IDRetailList and a.DtFrom = b.DtFrom) rtlBefore on t1.IDPrdct = rtlBefore.IDprdct left join (select whl.IDWholeSaleList, sbpdt.DtFrom, sbpdt.DtTo, sbpi.IDprdct, sbpi.Amnt from SBP_Item sbpi join (select g.IDWholeSaleList from SBP_GPrm g where g.IDGPrm = 1) whl on sbpi.IDList = whl.IDWholeSaleList join SBP_LstDt sbpdt on sbpi.IDList = sbpdt.IDList and sbpi.DtFrom = sbpdt.DtFrom where getdate() between sbpdt.DtFrom and isnull(sbpdt.DtTo, getdate())) whlNow on t1.IDPrdct = whlNow.IDprdct left join (select a.IDWholeSaleList, b.DtFrom, b.DtTo, b.IDprdct, b.Amnt from (select whl.IDWholeSaleList, max(sbpdt.DtFrom) DtFrom from (select g.IDWholeSaleList from SBP_GPrm g where g.IDGPrm = 1) whl join SBP_LstDt sbpdt on whl.IDWholeSaleList = sbpdt.IDList where sbpdt.DtFrom < getdate() and not (getdate() between sbpdt.DtFrom and isnull(sbpdt.DtTo, getdate())) group by whl.IDWholeSaleList) a join (select whl.IDWholeSaleList, sbpdt.DtFrom, sbpdt.DtTo, sbpi.IDprdct, sbpi.Amnt from SBP_Item sbpi join (select g.IDWholeSaleList from SBP_GPrm g where g.IDGPrm = 1) whl on sbpi.IDList = whl.IDWholeSaleList join SBP_LstDt sbpdt on sbpi.IDList = sbpdt.IDList and sbpi.DtFrom = sbpdt.DtFrom) b on a.IDWholeSaleList = b.IDWholeSaleList and a.DtFrom = b.DtFrom) whlBefore on t1.IDPrdct = whlBefore.IDprdct