SQL SERVER 2008 EXP VERİ TABANI HIZLANDIRMA

Merhaba,

Aşağıda göndermiş olduğum kodlama sisteminde stok envanteri kısmı çok yavaş gelmektedir.Bu kodlama sisteminde daha hızlı bir şekilde gelmesi için ne gibi değişiklik yapmam lazım.İndexleme yaptık ama sorunu çözemedik.Sql server 2008 exp kulanıyoruz fakat bunu sql server 2012 exp çalıştıramadık.Veri tabanı oluşturuyor ama işlem yapamıyoruz.Programa uyarlama için herhangi bir işlem yapmamız gerekirmi?Konu biraz acil yardımcı olursanız sevinirim.

Toplam Stok= 20.000

---------------------------------------------------------------------------------------------------------------------------------------------------------------------

USE [***]
GO
/ Object: StoredProcedure [dbo].[Sp_StokCariHareketleri] Script Date: 07/21/2016 14:24:10 ***/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Sp_StokCariHareketleri]
(@Evrak as nvarchar(100),
@sckod as int,
@tarih as datetime,
@tarih1 as datetime)
AS
BEGIN
Declare
@GirenParca as float,
@GirenMiktar as float,
@CikanParca as float,
@CikanMiktar as float,
@GirenTutar as money,
@CikanTutar as money,
@MinMiktar as float,
@KayitSayisi int
IF @Evrak='Stok Girişi'
Begin
---------- stokcaridurum=100 --- >>Stok Girişleridir
SELECT [dbo].[fn_faEvrakturEx](isnull(StokCari.Evrak,0)) as Evraktur,St.OzelKod,StokCari.ekod,StokCari.ckod,StokCari.tarih,StokCari.seri,StokCari.no,
[dbo].[fn_faislemEx](isnull(StokCari.islem,0)) as islem,[dbo].[fn_CariHesapTurEx](isnull(StokCari.hesaptur,0)) as carihesaptur,
[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)) as kdvtur,[dbo].[fn_faodemturEx](isnull(StokCari.odemetur,0)) as odeme,
StokCari.odemetarihi,StokCari.Unvan,StokCari.tel,StokCari.gsm,StokCari.depo,StokCari.plaka, StokCari.nakliyeci,StokCari.barkod,
StokCari.cinsi,StokCari.ambalaj,StokCari.miktar as GirenMiktar,StokCari.birim,miktar2 as GirenMiktar2,
StokCari.altbirim,StokCari.imaltarih,StokCari.sonkulanmatarih,StokCari.urunserino,StokCari.musterisi,StokCari.altaciklama as aciklama,
StokCari.koliicibirimfiyat,StokCari.fiyat,StokCari.fiyat2,StokCari.para,StokCari.isk1,StokCari.isk2,StokCari.isk3,
StokCari.isk4,StokCari.isk5,
StokCari.kdv,StokCari.otv,StokCari.tutar,
[dbo].[fn_@isk123] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)
) as Toplamiskonto,
[dbo].[fn_@AraToplam] (isnull(StokCari.tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)) as AraToplam,
[dbo].[fn_@KdvOtv] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.kdv,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))
as ToplamKdv,
[dbo].[fn_@KdvOtv] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.Otv,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))
as ToplamOtv,
GenelToplam
FROM dbo.CariHesaplarGenel as StokCari Left Outer Join StokTanim as St ON StokCari.ckod=St.ckod
WHERE (StokCari.stokcaridurum=100) and (StokCari.altevrak=118)
and (StokCari.sckod=isnull(@sckod,0))
and (StokCari.tarih Between @tarih and @tarih1)
ORDER BY Tarih
end
Else IF @Evrak='Stok Çıkışı'
Begin
---------- stokcaridurum=101 --- >>Stok Çıkışları
SELECT [dbo].[fn_faEvrakturEx](isnull(StokCari.Evrak,0)) as Evraktur,St.OzelKod,StokCari.ekod,StokCari.ckod,StokCari.tarih,StokCari.seri,StokCari.no,
[dbo].[fn_faislemEx](isnull(StokCari.islem,0)) as islem,[dbo].[fn_CariHesapTurEx](isnull(StokCari.hesaptur,0)) as carihesaptur,
[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)) as kdvtur,[dbo].[fn_faodemturEx](isnull(StokCari.odemetur,0)) as odeme,
StokCari.odemetarihi,StokCari.Unvan,StokCari.tel,StokCari.gsm,StokCari.depo,StokCari.plaka, StokCari.nakliyeci,StokCari.barkod,
StokCari.cinsi,StokCari.ambalaj,StokCari.miktar as CikanMiktar,StokCari.birim,miktar2 as CikanMiktar2,
StokCari.altbirim,StokCari.imaltarih,StokCari.sonkulanmatarih,StokCari.urunserino,StokCari.musterisi,StokCari.altaciklama as aciklama,
StokCari.koliicibirimfiyat,StokCari.fiyat,StokCari.fiyat2,StokCari.para,StokCari.isk1,StokCari.isk2,StokCari.isk3,
StokCari.isk4,StokCari.isk5,
StokCari.kdv,StokCari.otv,StokCari.tutar,
[dbo].[fn_@isk123] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)) as Toplamiskonto,
[dbo].[fn_@AraToplam] (isnull(StokCari.tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)) as AraToplam,
[dbo].[fn_@KdvOtv] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.kdv,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))
as ToplamKdv,
[dbo].[fn_@KdvOtv] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.Otv,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))
as ToplamOtv,
GenelToplam
FROM dbo.CariHesaplarGenel as StokCari Left Outer Join StokTanim as St ON StokCari.ckod=St.ckod
WHERE (StokCari.stokcaridurum=101) and (StokCari.altevrak=118)
and (StokCari.sckod=isnull(@sckod,0))
and (StokCari.tarih Between @tarih and @tarih1)
ORDER BY Tarih
end
Else IF @Evrak='Tüm Stok Girişleri'
Begin
---------- stokcaridurum=100 --- >>Stok Girişleridir
SELECT [dbo].[fn_faEvrakturEx](isnull(StokCari.Evrak,0)) as Evraktur,St.OzelKod,StokCari.ekod,StokCari.ckod,StokCari.tarih,StokCari.seri,StokCari.no,
[dbo].[fn_faislemEx](isnull(StokCari.islem,0)) as islem,[dbo].[fn_CariHesapTurEx](isnull(StokCari.hesaptur,0)) as carihesaptur,
[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)) as kdvtur,[dbo].[fn_faodemturEx](isnull(StokCari.odemetur,0)) as odeme,
StokCari.odemetarihi,StokCari.Unvan,StokCari.tel,StokCari.gsm,StokCari.depo,StokCari.plaka, StokCari.nakliyeci,StokCari.barkod,
StokCari.cinsi,StokCari.ambalaj,StokCari.miktar as GirenMiktar,StokCari.birim,miktar2 as GirenMiktar2,
StokCari.altbirim,StokCari.imaltarih,StokCari.sonkulanmatarih,StokCari.urunserino,StokCari.musterisi,StokCari.altaciklama as aciklama,
StokCari.koliicibirimfiyat,StokCari.fiyat,StokCari.fiyat2,StokCari.para,StokCari.isk1,StokCari.isk2,StokCari.isk3,
StokCari.isk4,StokCari.isk5,
StokCari.kdv,StokCari.otv,StokCari.tutar,
[dbo].[fn_@isk123] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)) as Toplamiskonto,
[dbo].[fn_@AraToplam] (isnull(StokCari.tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)) as AraToplam,
[dbo].[fn_@KdvOtv] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.kdv,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))
as ToplamKdv,
[dbo].[fn_@KdvOtv] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.Otv,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))
as ToplamOtv,
GenelToplam
FROM dbo.CariHesaplarGenel as StokCari Left Outer Join StokTanim as St ON StokCari.ckod=St.ckod
WHERE (StokCari.stokcaridurum=100) and (StokCari.altevrak=118)
and (StokCari.tarih Between @tarih and @tarih1)
ORDER BY Tarih
end
Else IF @Evrak='Tüm Stok Çıkışları'
Begin
---------- stokcaridurum=101 --- >>Stok Girişleridir
SELECT [dbo].[fn_faEvrakturEx](isnull(StokCari.Evrak,0)) as Evraktur,St.OzelKod,StokCari.ekod,StokCari.ckod,StokCari.tarih,StokCari.seri,StokCari.no,
[dbo].[fn_faislemEx](isnull(StokCari.islem,0)) as islem,[dbo].[fn_CariHesapTurEx](isnull(StokCari.hesaptur,0)) as carihesaptur,
[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)) as kdvtur,[dbo].[fn_faodemturEx](isnull(StokCari.odemetur,0)) as odeme,
StokCari.odemetarihi,StokCari.Unvan,StokCari.tel,StokCari.gsm,StokCari.depo,StokCari.plaka, StokCari.nakliyeci,StokCari.barkod,
StokCari.cinsi,StokCari.ambalaj,StokCari.miktar as CikanMiktar,StokCari.birim,miktar2 as CikanMiktar2,
StokCari.altbirim,StokCari.imaltarih,StokCari.sonkulanmatarih,StokCari.urunserino,StokCari.musterisi,StokCari.altaciklama as aciklama,
StokCari.koliicibirimfiyat,StokCari.fiyat,StokCari.fiyat2,StokCari.para,StokCari.isk1,StokCari.isk2,StokCari.isk3,
StokCari.isk4,StokCari.isk5,
StokCari.kdv,StokCari.otv,StokCari.tutar,
[dbo].[fn_@isk123] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)) as Toplamiskonto,
[dbo].[fn_@AraToplam] (isnull(StokCari.tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)) as AraToplam,
[dbo].[fn_@KdvOtv] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.kdv,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))
as ToplamKdv,
[dbo].[fn_@KdvOtv] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.Otv,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))
as ToplamOtv,
GenelToplam
FROM dbo.CariHesaplarGenel as StokCari Left Outer Join StokTanim as St ON StokCari.ckod=St.ckod
WHERE (StokCari.stokcaridurum=101) and (StokCari.altevrak=118)
and (StokCari.tarih Between @tarih and @tarih1)
ORDER BY Tarih
end
Else If @Evrak='Stok Envanteri'
Begin
---- Stok Envanteri ve Fifo ya
-- Muhtelif Mal Alış Satış tentan Kayıt Gelmeyecek
-- Yani Resmi Evraklardır
Select St.ckod,St.kod as barkod,St.stokad,st.grubu,St.ambalaj,St.birim,
St.altbirim,St.OzelKod,isnull(St.sonalisfiyat,0) as sonalisfiyat,
st.dmevcut,
isnull(st.dmevcut,0) dbmiktar, isnull(st.dmevcut*isnull(st.koliicimiktar,1),0) dbmiktar2,
isnull(GirenParca,0) as GirenParca,isnull(GirenMiktar,0) as GirenMiktar,
(isnull(AlisNetfiyat,0)/case when isnull(GirenParca,0)=0 then 1 else isnull(GirenParca,1) end)
as AlisOrtalamaFiyati,
isnull(dmevcut,0)+isnull(GirenParca,0)-isnull(CikanParca,0) as KalanParca,
isnull(st.dmevcut*isnull(st.koliicimiktar,1),0)+isnull(GirenMiktar,0)-isnull(CikanMiktar,0) as KalanMiktar,
isnull(AlisIskonto,0) as AlisIskonto,isnull(AlisAraToplam,0) as AlisAraToplam,isnull(AlisKdv,0) as AlisKdv,
isnull(AlisOtv,0) as AlisOtv,isnull(AlisGenelToplam,0) as AlisGenelToplam,
isnull(CikanParca,0) as CikanParca,
isnull(CikanMiktar,0) as CikanMiktar,
(isnull(SatisNetfiyat,0)/case when isnull(CikanParca,0)=0 then 1 else isnull(CikanParca,1) end)
as SatisOrtalamaFiyati,
isnull(SatisIskonto,0) as SatisIskonto,isnull(SatisAraToplam,0) as SatisAraToplam,isnull(SatisKdv,0) as SatisKdv,
isnull(SatisOtv,0) as SatisOtv,isnull(SatisGenelToplam,0) as SatisGenelToplam
-------------------Kar ------------------------------------------------------
From StokTanim as St Left Outer Join
(Select sckod,sum(isnull(CikanMiktar,0)) as CikanParca,sum(isnull(CikanMiktar2,0)) as CikanMiktar,
sum( [dbo].[fn_@NetFiyat] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(kdv,0),isnull(Otv,0),isnull(miktar,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0)))
*isnull(CikanMiktar,0)) as SatisNetfiyat,
sum(tutar) as SatisTutar,
Sum([dbo].[fn_@isk123] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0))) as SatisIskonto,
Sum([dbo].[fn_@AraToplam] (isnull(tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0))) as SatisAraToplam,
Sum([dbo].[fn_@KdvOtv] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(kdv,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0))))
as SatisKdv,
Sum([dbo].[fn_@KdvOtv] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(Otv,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0))))
as SatisOtv,
sum(GenelToplam) as SatisGenelToplam
From dbo.CariHesaplarGenel as StokCikis
---- (ltrim(rtrim(Evraktur))='Fatura Satış' or ltrim(rtrim(Evraktur))='Perakende Satış' or ltrim(rtrim(Evraktur))='Üretim' )
WHERE (stokcaridurum=101) and (Evrak in (102,127,129,130))and (altevrak=118) and (tarih Between @tarih and @tarih1)
Group by sckod
) as StokCikisleri On
StokCikisleri.sckod =St.ckod
Left Outer Join
(
Select sckod,
sum(case when islem=61 then 0 else isnull(girenmiktar,0) end) as GirenParca,
sum(case when islem=61 then 0 else isnull(girenmiktar2,0) end) as GirenMiktar,
sum( [dbo].[fn_@NetFiyat] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(kdv,0),isnull(Otv,0),isnull(miktar,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0)))
*isnull(CikanMiktar,0)) as AlisNetfiyat,
sum(tutar) as AlisTutar,
Sum([dbo].[fn_@isk123] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0))) as AlisIskonto,
Sum([dbo].[fn_@AraToplam] (isnull(tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0))) as AlisAraToplam,
Sum([dbo].[fn_@KdvOtv] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(kdv,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0))))
as AlisKdv,
Sum([dbo].[fn_@KdvOtv] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(Otv,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0))))
as AlisOtv,
sum(GenelToplam) as AlisGenelToplam
From dbo.CariHesaplarGenel as StokCikis
------(ltrim(rtrim(Evraktur))='Fatura Alış' rim(rtrim(Evraktur))='Makbuz' r ltrim(rtrim(Evraktur))='Üretim' or ltrim(rtrim(Evraktur))='Dönem Başı Mal Mevcudu')
WHERE (stokcaridurum=100) and (Evrak in (100,101,128,129,130))and (altevrak=118) and (tarih Between @tarih and @tarih1)
Group by sckod
) as StokGirisleri On
StokGirisleri.sckod =St.ckod
where ltrim(rtrim(St.statu))='Aktif'
End
Else If @Evrak='Muhtelif Stok Envanteri'
Begin
Select St.ckod,St.kod as barkod,St.stokad,St.ambalaj,St.birim,
St.altbirim,St.OzelKod,isnull(St.sonalisfiyat,0) as sonalisfiyat,
isnull(GirenParca,0) as GirenParca,isnull(GirenMiktar,0) as GirenMiktar,
(isnull(AlisNetfiyat,0)/case when isnull(GirenParca,0)=0 then 1 else isnull(GirenParca,1) end)
as AlisOrtalamaFiyati,
isnull(GirenParca,0)-isnull(CikanParca,0) as KalanParca,isnull(GirenMiktar,0)-isnull(CikanMiktar,0) as KalanMiktar,
isnull(AlisIskonto,0) as AlisIskonto,isnull(AlisAraToplam,0) as AlisAraToplam,isnull(AlisKdv,0) as AlisKdv,
isnull(AlisOtv,0) as AlisOtv,isnull(AlisGenelToplam,0) as AlisGenelToplam,isnull(CikanParca,0) as CikanParca,
isnull(CikanMiktar,0) as CikanMiktar,
(isnull(SatisNetfiyat,0)/case when isnull(CikanParca,0)=0 then 1 else isnull(CikanParca,1) end)
as SatisOrtalamaFiyati,
isnull(SatisIskonto,0) as SatisIskonto,isnull(SatisAraToplam,0) as SatisAraToplam,isnull(SatisKdv,0) as SatisKdv,
isnull(SatisOtv,0) as SatisOtv,isnull(SatisGenelToplam,0) as SatisGenelToplam
-------------------Kar ------------------------------------------------------
From StokTanim as St Left Outer Join
(Select sckod,sum(isnull(CikanMiktar,0)) as CikanParca,sum(isnull(CikanMiktar2,0)) as CikanMiktar,
sum( [dbo].[fn_@NetFiyat] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(kdv,0),isnull(Otv,0),isnull(miktar,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0)))
*isnull(CikanMiktar,0)) as SatisNetfiyat,
sum(tutar) as SatisTutar,
Sum([dbo].[fn_@isk123] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0))) as SatisIskonto,
Sum([dbo].[fn_@AraToplam] (isnull(tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0))) as SatisAraToplam,
Sum([dbo].[fn_@KdvOtv] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(kdv,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0))))
as SatisKdv,
Sum([dbo].[fn_@KdvOtv] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(Otv,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0))))
as SatisOtv,
sum(GenelToplam) as SatisGenelToplam
From dbo.CariHesaplarGenel as StokCikis
---- (ltrim(rtrim(Evraktur))='Muhtelif Mal Satış'
WHERE (stokcaridurum=101) and (Evrak in (104))and (altevrak=118) and (tarih Between @tarih and @tarih1)
Group by sckod
) as StokCikisleri On
StokCikisleri.sckod =St.ckod
Left Outer Join
(
Select sckod,
sum(case when islem = 61 then 0 else GirenMiktar end) as GirenParca,sum(
case when islem = 61 then 0 else GirenMiktar2 end) as GirenMiktar,
sum( [dbo].[fn_@NetFiyat] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(kdv,0),isnull(Otv,0),isnull(miktar,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0)))
*isnull(CikanMiktar,0)) as AlisNetfiyat,
sum(tutar) as AlisTutar,
Sum([dbo].[fn_@isk123] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0))) as AlisIskonto,
Sum([dbo].[fn_@AraToplam] (isnull(tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0))) as AlisAraToplam,
Sum([dbo].[fn_@KdvOtv] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(kdv,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0))))
as AlisKdv,
Sum([dbo].[fn_@KdvOtv] (isnull(Tutar,0),isnull(isk1,0),isnull(isk2,0),isnull(isk3,0),
isnull(isk4,0),isnull(isk5,0),
isnull(Otv,0),[dbo].[fn_KdvTurEx](isnull(kdvtur,0))))
as AlisOtv,
sum(GenelToplam) as AlisGenelToplam
From dbo.CariHesaplarGenel as StokCikis
------(ltrim(rtrim(Evraktur))='Muhtelif Mal Alış'
WHERE (stokcaridurum=100) and (Evrak in (105))and (altevrak=118) and (tarih Between @tarih and @tarih1)
Group by sckod
) as StokGirisleri On
StokGirisleri.sckod =St.ckod
where ltrim(rtrim(St.statu))='Aktif'
End
Else IF @Evrak='FiFo Girişler'
Begin
---------------------------------Stok Girişler
------(ltrim(rtrim(Evraktur))='Fatura Alış' rim(rtrim(Evraktur))='Makbuz' r ltrim(rtrim(Evraktur))='Üretim' or ltrim(rtrim(Evraktur))='Dönem Başı Mal Mevcudu')
SELECT
StokCari.Tarih,StokCari.altekod,StokCari.Evrak,StokCari.ekod,St.ckod as sckod,
isnull(StokCari.girenmiktar,0) as GirenParca
FROM dbo.CariHesaplarGenel as StokCari INNER JOIN dbo.StokTanim as St ON StokCari.sckod=St.ckod
WHERE (StokCari.stokcaridurum=100) and (StokCari.Evrak in (100,101,128,129,130))and (StokCari.altevrak=118) and (ltrim(rtrim(St.statu))='Aktif')
Order By St.ckod,St.stokad,StokCari.Tarih,StokCari.id
End
Else IF @Evrak='FiFo Çıkışlar'
Begin
---- (ltrim(rtrim(Evraktur))='Fatura Satış' or ltrim(rtrim(Evraktur))='Perakende Satış' or ltrim(rtrim(Evraktur))='Üretim'
SELECT
StokCikis.Tarih,StokCikis.sckod,
[dbo].[fn_@NetFiyat] (isnull(StokCikis.Tutar,0),isnull(StokCikis.isk1,0),isnull(StokCikis.isk2,0),isnull(StokCikis.isk3,0),
isnull(StokCikis.isk4,0),isnull(StokCikis.isk5,0)
,isnull(StokCikis.kdv,0),isnull(StokCikis.Otv,0),isnull(StokCikis.miktar,0),[dbo].[fn_KdvTurEx](isnull(StokCikis.kdvtur,0)))
as NetFiyat,
isnull(StokCikis.CikanMiktar,0) as CikanParca
FROM dbo.CariHesaplarGenel as StokCikis INNER JOIN dbo.StokTanim as St ON StokCikis.sckod=St.ckod
INNER JOIN (Select StokCari.sckod FROM dbo.CariHesaplarGenel as StokCari INNER JOIN dbo.StokTanim as St ON StokCari.sckod=St.ckod
WHERE (StokCari.stokcaridurum=100) and (Evrak in (100,101,128,129,130))and (altevrak=118) and (ltrim(rtrim(St.statu))='Aktif')
Group By StokCari.sckod
) as StokGiris
ON (StokCikis.sckod=StokGiris.sckod)
WHERE (StokCikis.stokcaridurum=101) and (StokCikis.Evrak in (102,127,129,130))and (StokCikis.altevrak=118) and (ltrim(rtrim(St.statu))='Aktif')
ORDER BY StokCikis.Tarih,St.ckod
End
Else IF @Evrak='FiFo'
Begin
SELECT
[dbo].[fn_faEvrakturEx](isnull(Evrak,0)) as Evraktur,StokCari.ekod,StokCari.Tarih,StokCari.Seri,StokCari.No,
StokCari.sckod,StokCari.barkod,StokTanim.OzelKod,StokCari.cinsi,StokCari.ambalaj,StokCari.birim,StokCari.altbirim,
isnull(StokCari.girenmiktar,0) as GirenParca,isnull(StokCari.SatilanParca,0) as SatilanParca,
case when isnull(StokCari.satilanparca,0)=0 then 0 else Round(isnull(StokCari.OrtTutar,0)/isnull(StokCari.satilanparca,1),2) end
as OrtFiyat,
Round((StokCari.GirenMiktar-StokCari.satilanparca),3) as KalanParca,
([dbo].[fn_@NetFiyat] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0)
,isnull(StokCari.kdv,0),isnull(StokCari.Otv,0),isnull(StokCari.miktar,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0))))
as fiyat1,
[dbo].[fn_@AraToplam] (isnull(StokCari.tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0))
as AlisTutar,
ROUND(isnull(StokCari.OrtTutar,0),0) as SatisTutar,
ROUND(isnull(StokCari.OrtTutar,0)-
(isnull(StokCari.satilanparca,0)*[dbo].[fn_@NetFiyat] (isnull(StokCari.Tutar,0),isnull(StokCari.isk1,0),isnull(StokCari.isk2,0),isnull(StokCari.isk3,0),
isnull(StokCari.isk4,0),isnull(StokCari.isk5,0),
isnull(StokCari.kdv,0),isnull(StokCari.Otv,0),isnull(StokCari.miktar,0),[dbo].[fn_KdvTurEx](isnull(StokCari.kdvtur,0)))),2)
as Kar
FROM dbo.CariHesaplarGenel as StokCari INNER JOIN dbo.StokTanim ON StokCari.sckod=StokTanim.ckod
WHERE (StokCari.stokcaridurum=100) and (StokCari.Evrak in (100,101,128,129,130))and (StokCari.altevrak=118) and (ltrim(rtrim(StokTanim.statu))='Aktif')
and (StokCari.tarih Between @tarih and @tarih1)
Order By StokCari.sckod,StokCari.cinsi,StokCari.Tarih,StokCari.id
end
Else If @Evrak='Satışa Göre Stok Değeri'
Begin
SELECT
St.ckod as sckod,St.kod as barkod,St.OzelKod,St.stokad as cinsi,st.grubu,St.ambalaj,
St.birim,St.altbirim,StokCari.depo,StokCari.depockod,
sum(isnull(StokCari.girenmiktar,0)) as GirenParca,sum(isnull(StokCari.cikanmiktar,0)) as CikanParca,
sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0)) as KalanParca,
sum(isnull(StokCari.girenMiktar2,0)) as GirenMiktar,sum(isnull(StokCari.cikanmiktar2,0)) as CikanMiktar,
sum(isnull(StokCari.girenMiktar2,0))-sum(isnull(StokCari.cikanmiktar2,0)) as KalanMiktar,
isnull(St.fiyat1,0) as Fiyat1,
isnull(St.fiyat1,0)*(sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0))) as Tutar1,
isnull(St.fiyat2,0) as Fiyat2,
isnull(St.fiyat2,0)*(sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0))) as Tutar2,
isnull(St.fiyat3,0) as Fiyat3,
isnull(St.fiyat3,0)*(sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0))) as Tutar3,
isnull(St.fiyat4,0) as Fiyat4,
isnull(St.fiyat4,0)*(sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0))) as Tutar4
FROM dbo.CariHesaplarGenel as StokCari INNER JOIN dbo.StokTanim as St ON StokCari.sckod=St.ckod
WHERE (StokCari.stokcaridurum in (100,101)) and (StokCari.altevrak=118) and (ltrim(rtrim(St.statu))='Aktif')
Group by St.ckod,St.kod,St.OzelKod,St.stokad,St.ambalaj,St.birim,St.altbirim,
StokCari.depo,StokCari.depockod,St.fiyat1,St.fiyat2,St.fiyat3,St.fiyat4,st.grubu
Having (sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0)))>0
order by St.stokad
END
Else If @Evrak='Alışa Göre Stok Değeri'
Begin
SELECT
St.ckod as sckod,St.kod as barkod,St.OzelKod,St.stokad as cinsi,st.grubu,St.ambalaj,St.birim,St.altbirim,StokCari.urunserino,
StokCari.depo,StokCari.depockod,StokCari.imaltarih,StokCari.sonkulanmatarih,isnull(St.sonalisfiyat,0) As SonAlisFiyat,
sum(isnull(StokCari.girenmiktar,0)) as GirenParca,sum(isnull(StokCari.cikanmiktar,0)) as CikanParca,
sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0)) as KalanParca,
sum(isnull(StokCari.girenMiktar2,0)) as GirenMiktar,sum(isnull(StokCari.cikanmiktar2,0)) as CikanMiktar,
sum(isnull(StokCari.girenMiktar2,0))-sum(isnull(StokCari.cikanmiktar2,0)) as KalanMiktar,
(sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0))) * st.SonAlisFiyat StokDegeri,
(sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0))) * (st.SonAlisFiyat + st.SonAlisFiyat*st.kdv/100) StokDegeriKDVli
FROM dbo.CariHesaplarGenel as StokCari INNER JOIN dbo.StokTanim as St ON StokCari.sckod=St.ckod
WHERE (StokCari.stokcaridurum in (100,101)) and (StokCari.altevrak=118) and (ltrim(rtrim(St.statu))='Aktif')
Group by
St.ckod,st.kdv,St.kod,St.OzelKod,St.stokad,St.ambalaj,St.birim,St.altbirim,StokCari.urunserino,
StokCari.depo,StokCari.depockod,StokCari.imaltarih,StokCari.sonkulanmatarih,St.sonalisfiyat,st.grubu
Having (sum(isnull(StokCari.girenmiktar,0))-sum(isnull(StokCari.cikanmiktar,0)))>0
order by St.stokad
END
Else If @Evrak='Stok Kalan Mevcut'
Begin
SET @GirenParca=0 SET @GirenMiktar=0 SET @CikanParca=0 SET @CikanMiktar=0 SET @GirenTutar=0 SET @CikanTutar=0
Select
@GirenParca=sum(isnull(StokCari.girenmiktar,0)),@CikanParca=sum(isnull(StokCari.cikanmiktar,0)),
@GirenMiktar=sum(isnull(StokCari.girenMiktar2,0)),@CikanMiktar=sum(isnull(StokCari.cikanmiktar2,0)),
@GirenTutar=sum(Case When stokcaridurum=100 then isnull(GenelToplam,0) Else 0 End),
@CikanTutar=sum(Case When stokcaridurum=101 then isnull(GenelToplam,0) Else 0 End)
FROM dbo.CariHesaplarGenel as StokCari INNER JOIN dbo.StokTanim as St ON StokCari.sckod=St.ckod
WHERE (StokCari.stokcaridurum in (100,101)) and (StokCari.altevrak=118) and (ltrim(rtrim(St.statu))='Aktif')
and (StokCari.sckod=isnull(@sckod,0))
SELECT @KayitSayisi as KayitSayisi,isnull(@MinMiktar,0) as MinMiktar,
isnull(@GirenParca,0) as GirenParca, isnull(@CikanParca,0) as CikanParca,
(isnull(@GirenParca,0)-isnull(@CikanParca,0)) as KalanParca,
isnull(@GirenMiktar,0) as GirenMiktar, isnull(@CikanMiktar,0) as CikanMiktar,
(isnull(@GirenMiktar,0)-isnull(@CikanMiktar,0)) as KalanMiktar,
isnull(@GirenTutar,0) as GirenTutar,isnull(@CikanTutar,0) as CikanTutar,
(isnull(@GirenTutar,0)-isnull(@CikanTutar,0)) as KalanTutar
End
End