--exec dbo.prcKalaView()
alter function dbo.fGetKalaVaredeSadere(@Fc_ID as integer =0, @Pro_ID_VahedMali as integer, @EndDate as integer=0) returns table  
return (  
-----------موجودی کالا در انبارها     
select  
 k.Kal_ID [Kal_ID],  
 case when mojoodiAvaldoreh>0 then mojoodiAvalDoreh else 0 end[Varede],  
 case when mojoodiAvaldoreh<0 then -mojoodiAvalDoreh else 0 end[Sadere],  
 k.Pro_ID_Anbar[Pro_ID_Anbar]  
from  
 kalaDarAnbar k  
where
	(@Fc_ID = 0 or k.Fc_ID = @Fc_ID) and
	(@Pro_ID_VahedMali = 0 or k.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
	(@EndDate = 0 or k.tarikh <= @EndDate)
-----  
union all  
-----تعداد کالاهای وارده در برگه های خرید و برگشت از فروش  
  
select  
 d.kal_ID,  
 d.tedad,  
 0,  
 d.Pro_ID_anbar  
from  
 Bargeh m join BargehDetail d  
 on m.ID=d.Bar_ID  
where  
 d.Dom_ID_Noe = 666 /*iskala = 1*/ and  
 m.Dom_ID_noe in(31,29,791,1162,1276) and /*kharid , bargashAzForoosh , اضافات انبارگرداني , TrustedGoodsPurchase , فاکتور مانده پروژه*/  
 m.Dom_ID_vaziat != 101 and
	(@Fc_ID = 0 or m.Fc_ID = @Fc_ID) and
	(@Pro_ID_VahedMali = 0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
	(@EndDate = 0 or m.tarikh <= @EndDate)
-----  
union all  
-----تعداد کالاهای صادره در برگه های فروش و برگشت از خرید  
  
select  
 d.Kal_ID,  
 0,  
 d.tedad,  
 d.Pro_ID_Anbar  
from  
 Bargeh m join BargehDetail d  
 on m.ID=d.Bar_ID  
where  
 d.Dom_ID_Noe = 666 /*iskala = 1*/ and  
 m.Dom_ID_noe in(28,32,792,1163,1190,1275) and /*foroosh va bargashAzKharid , کسورات انبارگرداني , TrustedGoodsSale , PlayStationFactor , فاکتور مصرفی پروژه*/  
 m.Dom_ID_vaziat != 101  and
 (@Fc_ID = 0 or m.Fc_ID = @Fc_ID) and
	(@Pro_ID_VahedMali = 0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
	(@EndDate = 0 or m.tarikh <= @EndDate)
-----  
union all  
------- کالاهای مصرفی تولید
select  
 d.kal_ID,  
 0,  
 d.tedad,  
 d.Pro_ID_Anbar  
from  
 Product m join ProductDetail d  
 on m.id = d.prod_id  
where  
 m.dom_ID_vaziat != 101 and  
 d.dom_ID_Noe = 260 and-- kalaMasrafi  
    --(@Fc_ID = 0 or d.Fc_ID = @Fc_ID) and
	--(@Pro_ID_VahedMali = 0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
	--(@EndDate = 0 or d.tarikh <= @EndDate)
	(@Fc_ID = 0 or (case when m.Dom_ID_Type in (1582,1584) and m.Fc_ID = @Fc_ID then 1 when m.Dom_ID_Type in (1583) and d.Fc_ID = @Fc_ID then 1 else 0 end) = 1) and
	(@Pro_ID_VahedMali = 0 or (case when m.Dom_ID_Type in (1582,1584) and m.Pro_ID_VahedMali = @Pro_ID_VahedMali then 1 when m.Dom_ID_Type in (1583) and d.Pro_ID_VahedMali = @Pro_ID_VahedMali then 1 else 0 end) = 1) and
	(@EndDate = 0 or (case when m.Dom_ID_Type in (1582,1584) and m.tarikh <= @EndDate then 1 when m.Dom_ID_Type in (1583) and d.tarikh <= @EndDate then 1 else 0 end) = 1) 
-------  
union all  
------ ضایعات و محصول تولید
select  
 d.kal_ID,  
 d.tedad,  
 0,  
 d.Pro_ID_Anbar  
from  
 Product m join ProductDetail d  
 on m.id = d.prod_ID  
where  
 m.dom_ID_vaziat != 101 and  
 d.dom_ID_Noe in (259,463) and-- ضایعات و محصول تولید  
   --(@Fc_ID = 0 or d.Fc_ID = @Fc_ID) and
   --(@Pro_ID_VahedMali = 0 or d.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
   --(@EndDate = 0 or d.tarikh <= @EndDate)
	(@Fc_ID = 0 or (case when m.Dom_ID_Type in (1582,1584) and m.Fc_ID = @Fc_ID then 1 when m.Dom_ID_Type in (1583) and d.Fc_ID = @Fc_ID then 1 else 0 end) = 1) and
	(@Pro_ID_VahedMali = 0 or (case when m.Dom_ID_Type in (1582,1584) and m.Pro_ID_VahedMali = @Pro_ID_VahedMali then 1 when m.Dom_ID_Type in (1583) and d.Pro_ID_VahedMali = @Pro_ID_VahedMali then 1 else 0 end) = 1) and
	(@EndDate = 0 or (case when m.Dom_ID_Type in (1582,1584) and m.tarikh <= @EndDate then 1 when m.Dom_ID_Type in (1583) and d.tarikh <= @EndDate then 1 else 0 end) = 1) 
------- 
union all  
------- کالاهای مصرفی تبدیل
select  
 d.kal_ID,  
 0,  
 d.tedad,  
 d.Pro_ID_Anbar  
from ConvertGood m
	join ConvertGoodDetail d on m.id = d.CG_ID  
where  
 m.dom_ID_vaziat != 101 and  
 d.dom_ID_Noe = 260 and-- kalaMasrafi  
  (@Fc_ID = 0 or m.Fc_ID = @Fc_ID) and
	(@Pro_ID_VahedMali = 0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
	(@EndDate = 0 or m.tarikh <= @EndDate)
-------  
union all 
------- کالاهای تولید شده تبدیل
select  
 d.kal_ID,  
 d.tedad,  
 0,  
 d.Pro_ID_Anbar  
from ConvertGood m
	join ConvertGoodDetail d on m.id = d.CG_ID  
where  
 m.dom_ID_vaziat != 101 and  
 d.dom_ID_Noe = 463 and-- کالاي توليدي
  (@Fc_ID = 0 or m.Fc_ID = @Fc_ID) and
	(@Pro_ID_VahedMali = 0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
	(@EndDate = 0 or m.tarikh <= @EndDate)
-------  
union all  
------ حواله انتقالی بین انبار صادره  
select  
 d.Kal_ID,  
 0,  
 d.tedad,  
 d.Pro_ID_Anbar  
from  
 Bargeh m join BargehDetail d  
 on m.ID=d.Bar_ID  
where  
 d.Dom_ID_Noe = 666 /*iskala = 1*/ and  
 m.Dom_ID_noe = 36 and /*EnteghalBeynAnbar*/  
 m.Dom_ID_vaziat != 101  and
   (@Fc_ID = 0 or m.Fc_ID = @Fc_ID) and
	(@Pro_ID_VahedMali = 0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
	(@EndDate = 0 or m.tarikh <= @EndDate)
  
union all  
------ حواله انتقالی بین انبار وارده  
select  
 d.Kal_ID,  
 d.tedad,  
 0,  
 d.Pro_ID_AnbarMaghsad  
from  
 Bargeh m join BargehDetail d on m.ID=d.Bar_ID  
where  
 d.Dom_ID_Noe = 666 /*iskala = 1*/ and  
 m.Dom_ID_noe = 36 and /*EnteghalBeynAnbar*/  
 m.Dom_ID_vaziat != 101  and
   (@Fc_ID = 0 or m.Fc_ID = @Fc_ID) and
	(@Pro_ID_VahedMali = 0 or m.Pro_ID_VahedMali = @Pro_ID_VahedMali) and
	(@EndDate = 0 or m.tarikh <= @EndDate)
)  
-----------------------------------------------------------------------------------------------