FIFO Maliyet TSQL Uygulaması

[turkmedia]

Merhaba,

Konuya eklenmiş linkleri görmek için kayıt olmalısınız.

Fifo Yöntemi (İlk Giren İlk Çıkar): Fifo değerleme yöntemi, üretime verilecek olan veya satılacak malların stoklara ilk önce giren mallardan olması gerektiği varsayımına dayanır. Stoktaki malların kullanılma sırası ilk alınan mallardan başlanarak sırasıyla devam eder. Yani stoklara giren malların yine giriş sırasıyla stoktan çıkarlar.



Sorgumuzda çıkacak sonuçlar;

1- Envanter

2- Giriş ve Çıkış hareketlerinin eşleştirilmesi

3- Oluşan maliyetin çıkış hareketlerine Update edilmesi.



Öncelikle işlem oluşturacağımız tablo isimlerini belirtiyorum.

Tablo Adı : STOK_DETAYLI

Açıklama : Stok hareket bilgilerinin tutulduğu tablo adıdır. Bu tabloya verilerimizi toplu olarak ekleme işlemi yapacağız sonraki işlemlerde.

Tablo Adı : FIFOESLEME

Açıklama : Giriş ve çıkış hareketlerin birbiriyle eşleştirilip ilgili tabloya yazıyoruz.

Tablo Adı : FIFOENVANTER

Açıklama : Elde kalan malzemelerin ilgili tabloya yazacağız.



Tablo Adı : STOKNEGATIF

Açıklama : İlgili stok kalemlerin Negatif düştüğü zaman maliyet hesabı yapılmayacak. Negatif bilgileri tabloya aktarma işlemi yapacağız.



Gerekli tabloları oluşturmaya başlıyoruz.

Negatif tablo oluşturma.

IF (SELECT COUNT(*) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'STOKNEGATIF') AND OBJECTPROPERTY(id,'IsUserTable')=1)>0 BEGIN
DROP TABLE [dbo].STOKNEGATIF
END
CREATE TABLE STOKNEGATIF
([KODU] VARCHAR(150) NOT NULL,
[ADI] varchar(250) NOT NULL,
[TARIH] datetime NOT NULL,[AMBAR] varchar(150) NOT NULL,
[GIRISCIKIS] VARCHAR(150),
[FISTURU] VARCHAR(200),
[MIKTAR] FLOAT NOT NULL,
KALAN DECIMAL(38,2))

CREATE CLUSTERED INDEX TANIMLAMA ON STOKNEGATIF(KODU,AMBAR,TARIH)
CREATE INDEX DENEME ON STOKNEGATIF(KODU)

Fifo eşleme tablo oluşturma.

IF (SELECT COUNT(*) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'FIFOESLEME') AND OBJECTPROPERTY(id,'IsUserTable')=1)>0 BEGIN
DROP TABLE [dbo].FIFOESLEME
END
CREATE TABLE [dbo].FIFOESLEME
([KODU] varchar(150),
[AMBAR] varchar(50),
GIRISID INT,CIKISID INT,
MIKTAR FLOAT,
GIRISFIYAT FLOAT,
CIKISFIYAT FLOAT,
ALIMTARIHI DATE,
SATISTARIHI DATE)

CREATE CLUSTERED INDEX ITANIMLAMADX11 ON FIFOESLEME([KODU],AMBAR,ALIMTARIHI,GIRISID)
CREATE INDEX KART ON FIFOESLEME([KODU])

Envanter tablo oluşturma

IF (SELECT count(*) FROM dbo.sysobjects WHERE id = OBJECT_ID(N'FIFOENVANTER') AND OBJECTPROPERTY(id,'IsUserTable')=1)>0 BEGIN
DROP TABLE [dbo].FIFOENVANTER
END
CREATE TABLE [dbo].FIFOENVANTER
([KODU] varchar(150),[AMBAR] varchar(100),
GIRISID INT,
MIKTAR FLOAT,
GIRISFIYAT FLOAT,
ALIMTARIHI DATE)

CREATE CLUSTERED INDEX IDX11 ON FIFOENVANTER([KODU],AMBAR,ALIMTARIHI,GIRISID)
CREATE INDEX DENEME ON FIFOENVANTER([KODU])

Stok Detaylı tablosu oluşturma.

IF (SELECT COUNT(*) FROM sys.tables WHERE name= 'STOK_DETAYLI')>0 BEGIN
DROP TABLE STOK_DETAYLI
END
;
CREATE TABLE
[dbo].[STOK_DETAYLI](
[ID] [int] IDENTITY(1,1) NOT NULL,
[KODU] varchar(150) NOT NULL,
[ADI] varchar(250) NOT NULL,
[TARIH] datetime NOT NULL,
[AMBAR] varchar(100) NOT NULL,
[GIRISCIKIS] VARCHAR(100),
[FISTURU] VARCHAR(200),
[MIKTAR] FLOAT NOT NULL,
[BIRIMFIYAT] FLOAT ,
[TUTAR] FLOAT,
[MALIYET] FLOAT)
CREATE CLUSTERED INDEX STOK ON [STOK_DETAYLI](KODU,TARIH,AMBAR)
CREATE INDEX SIRA ON [STOK_DETAYLI](KODU)

Verilerimizi ekliyoruz.

INSERT INTO [STOK_DETAYLI]

SELECT 'STOK.001','Deneme Stok','2018-01-01 09:34:00.000','Merkez','GIRIS','Devir','15','1.25','18.75','0' UNION ALL
SELECT 'STOK.001','Deneme Stok','2018-01-02 09:35:00.000','Merkez','GIRIS','SatınAlma','15','2','30','0' UNION ALL
SELECT 'STOK.001','Deneme Stok','2018-01-02 09:36:00.000','Merkez','CIKIS','Satış','22','3','66','0' UNION ALL
SELECT 'STOK.001','Deneme Stok','2018-01-02 09:38:00.000','Merkez','GIRIS','SatınAlma','20','1.75','35','0' UNION ALL
SELECT 'STOK.001','Deneme Stok','2018-01-03 10:35:00.000','Antalya','GIRIS','SatınAlma','15','2.50','37.50','0' UNION ALL
SELECT 'STOK.001','Deneme Stok','2018-01-03 10:36:00.000','Antalya','CIKIS','Satış','12','3','36','0' UNION ALL
SELECT 'STOK.001','Deneme Stok','2018-01-04 11:35:00.000','Merkez','GIRIS','SatınAlma','20','1.75','35','0' UNION ALL
SELECT 'STOK.001','Deneme Stok','2018-01-04 11:40:00.000','Merkez','CIKIS','Satış','15','3','45','0' UNION ALL
SELECT 'STOK.001','Deneme Stok','2018-01-05 11:41:00.000','Merkez','GIRIS','SatınAlma','8','2.50','20','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-01 09:34:00.000','Merkez','GIRIS','Devir','15','1.25','18.75','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-02 09:35:00.000','Merkez','GIRIS','SatınAlma','15','2','30','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-02 09:36:00.000','Merkez','CIKIS','Satış','22','3','66','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-02 09:38:00.000','Merkez','GIRIS','SatınAlma','20','1.75','35','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-03 10:35:00.000','Antalya','GIRIS','SatınAlma','15','2.50','37.50','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-03 10:36:00.000','Antalya','CIKIS','Satış','12','3','36','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-04 11:35:00.000','Merkez','GIRIS','SatınAlma','20','1.75','35','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-04 11:40:00.000','Merkez','CIKIS','Satış','15','3','45','0' UNION ALL
SELECT 'STOK.002','Deneme','2018-02-05 11:41:00.000','Merkez','GIRIS','SatınAlma','8','2.50','20','0'

Bu işlemden sonra gerçek tabloda olan bütün verilerimi geçiçi tabloya aktarma işlemin yapıyoruz.

IF(OBJECT_ID('tempdb..##VERIDATA') IS NOT NULL) BEGIN DROP TABLE ##VERIDATA END
SELECT *,CAST(0 AS DECIMAL(38,2)) AS KALAN INTO ##VERIDATA
FROM [STOK_DETAYLI] WITH(NOLOCK, INDEX(STOK))
CREATE INDEX STOK ON ##VERIDATA(KODU)

Döngü içinde eşleşmeleri geçiçi tabloya aktarıyoruz. Normal şartlarda direk normal tabloya yazabilir'dik fakat geçiçi tablo çalışmak hız bakımından her zaman için performans açısından iyidir.

IF(OBJECT_ID('tempdb..##FIFOESLEME') IS NOT NULL) BEGIN DROP TABLE ##FIFOESLEME END
CREATE TABLE ##FIFOESLEME
([KODU] varchar(150),
[AMBAR] varchar(50),
GIRISID INT,
CIKISID INT,
MIKTAR FLOAT,
GIRISFIYAT FLOAT,
CIKISFIYAT FLOAT,
ALIMTARIHI DATE,
SATISTARIHI DATE)

CREATE CLUSTERED INDEX ITANIMLAMADX11 ON ##FIFOESLEME([KODU],AMBAR,ALIMTARIHI,GIRISID)
CREATE INDEX KART ON ##FIFOESLEME(GIRISID)



Geçiçi tabloda Yürüyen bakiye işlemini yapıyoruz. Eksi değer veren var mı diye
Normal şartlarda 2012 versiyonda Partıtıon By fonksiyonu ile gidilebilir.


DECLARE @STOKKODU VARCHAR(150),@AMBAR VARCHAR(50),@KALAN DECIMAL(38,2)
SET @STOKKODU = ''
SET @AMBAR = ''
SET @KALAN =0
UPDATE a
SET @KALAN = KALAN = CASE WHEN GIRISCIKIS ='GIRIS' THEN MIKTAR ELSE -MIKTAR END + CASE WHEN KODU = @STOKKODU AND AMBAR = @AMBAR THEN
@KALAN ELSE 0 END,
@STOKKODU = KODU,
@AMBAR =AMBAR
FROM ##VERIDATA a WITH (TABLOCKX,INDEX(STOK))
OPTION (MAXDOP 5);


Verilerimizi döngüde ekliyeceğimiz tablomuzu oluşturuyoruz.


IF(OBJECT_ID('tempdb..#GECICI') IS NOT NULL) BEGIN DROP TABLE #GECICI END
CREATE TABLE #GECICI
             (ID INT,
              TARIH DATETIME,
              [KODU] varchar(150),
              [AMBAR] varchar(50),
              GIRISCIKIS VARCHAR(35),
              BIRIMFIYAT FLOAT,
              MIKTAR DECIMAL(38,2))

CREATE CLUSTERED INDEX SIRALAMA ON #GECICI([KODU],AMBAR,TARIH)
CREATE INDEX VEDAT ON #GECICI([KODU])
CREATE INDEX SATIR ON #GECICI(ID) INCLUDE([KODU],AMBAR,TARIH,MIKTAR)




Negatif olan stok kontrolü yapıyoruz.

TRUNCATE TABLE STOKNEGATIF
INSERT INTO STOKNEGATIF
SELECT KODU,ADI,TARIH,AMBAR,GIRISCIKIS,FISTURU,MIKTAR,KALAN FROM ##VERIDATA
WHERE KALAN<0

Hesaplama yapan döngü ;

DECLARE @KODU VARCHAR(150) , @AMBARR VARCHAR(50)
DECLARE STOK CURSOR FOR
SELECT DISTINCT KODU,AMBAR FROM ##VERIDATA

ORDER BY KODU,AMBAR

OPEN STOK
FETCH NEXT FROM STOK
INTO @KODU,@AMBAR
WHILE @@FETCH_STATUS=0
BEGIN

DELETE #GECICI WITH(TABLOCK) WHERE KODU=@KODU AND AMBAR=@AMBAR

IF (SELECT COUNT(*) FROM STOKNEGATIF WITH(NOLOCK,INDEX(DENEME)) WHERE KODU=@KODU AND AMBAR=@AMBAR AND KALAN<0)=0 BEGIN

DECLARE @ID INT,@TARIH DATETIME,@GIRISCIKIS VARCHAR(10),@FISTUR VARCHAR(60),@MIKTAR DECIMAL(38,2),@BIRIMFIYAT DECIMAL(38,2)
DECLARE @GIRENID INT,@GIRENTARIH DATETIME,@GIRENFISTUR VARCHAR(60),@GIRENMIKTAR DECIMAL(38,2),@GIRENBIRIMFIYAT DECIMAL(38,2)
DECLARE FIFOMALIYET CURSOR FOR
SELECT ID,TARIH,GIRISCIKIS,FISTURU,MIKTAR,BIRIMFIYAT FROM ##VERIDATA
WHERE KODU=@KODU AND AMBAR=@AMBAR
OPEN FIFOMALIYET
FETCH NEXT FROM FIFOMALIYET INTO @ID,@TARIH,@GIRISCIKIS,@FISTUR,@MIKTAR,@BIRIMFIYAT
WHILE @@FETCH_STATUS=0 BEGIN

IF @GIRISCIKIS = 'GIRIS' BEGIN
INSERT INTO #GECICI
VALUES(@ID,@TARIH,@KODU,@AMBAR,@GIRISCIKIS,@BIRIMFIYAT,@MIKTAR)
END

IF @GIRISCIKIS='CIKIS'
BEGIN

WHILE (1=1)

BEGIN

SELECT TOP 1 @GIRENID=ID,@GIRENTARIH=TARIH,@GIRENMIKTAR=MIKTAR,@GIRENBIRIMFIYAT=BIRIMFIYAT FROM #GECICI
WHERE KODU=@KODU AND AMBAR=@AMBAR AND GIRISCIKIS='GIRIS' AND MIKTAR>0
ORDER BY TARIH ASC
IF @GIRENMIKTAR>=@MIKTAR AND @MIKTAR<>0
BEGIN
INSERT INTO ##FIFOESLEME (KODU,AMBAR,GIRISID,CIKISID,MIKTAR,GIRISFIYAT,CIKISFIYAT,ALIMTARIHI,SATISTARIHI)
VALUES(@KODU,@AMBAR,@GIRENID,@ID,@MIKTAR,@GIRENBIRIMFIYAT,@BIRIMFIYAT,@GIRENTARIH,@TARIH)
UPDATE #GECICI WITH(TABLOCK) SET MIKTAR = COALESCE(MIKTAR,0) - COALESCE(@MIKTAR,0) WHERE ID=@GIRENID
BREAK
END
ELSE
BEGIN
INSERT INTO ##FIFOESLEME (KODU,AMBAR,GIRISID,CIKISID,MIKTAR,GIRISFIYAT,CIKISFIYAT,ALIMTARIHI,SATISTARIHI)
VALUES(@KODU,@AMBAR,@GIRENID,@ID,@GIRENMIKTAR,@GIRENBIRIMFIYAT,@BIRIMFIYAT,@GIRENTARIH,@TARIH)
UPDATE #GECICI WITH(TABLOCK) SET MIKTAR = COALESCE(MIKTAR,0) - COALESCE(@GIRENMIKTAR,0) WHERE ID=@GIRENID
SET @MIKTAR = @MIKTAR - @GIRENMIKTAR
END



END

end

FETCH NEXT FROM FIFOMALIYET INTO @ID,@TARIH,@GIRISCIKIS,@FISTUR,@MIKTAR,@BIRIMFIYAT

END

/*******************************************************************************************************************
5) Malıyet Hesabını bitti. Elimizde kalan malzemeleri envanter bölümüne aktarıyoruz.
****************************************************************************************************************/
DELETE FIFOENVANTER WITH(TABLOCK) WHERE KODU=@KODU AND AMBAR=@AMBAR
INSERT INTO FIFOENVANTER(KODU,AMBAR,GIRISID ,MIKTAR ,GIRISFIYAT,ALIMTARIHI)
SELECT KODU,AMBAR,ID ,MIKTAR ,BIRIMFIYAT,TARIH FROM #GECICI WHERE ISNULL(MIKTAR,0)>0 AND GIRISCIKIS='GIRIS'
AND KODU=@KODU AND AMBAR=@AMBAR

/*******************************************************************************************************************
6) Çıkışların maliyetini hesaplaması aşağıdaki şekilde yapılmaktadır.
İlk İşlemde ; (MIKTAR * GIRISFIYAT) TUTAR
Sonrasında ; (TUTAR / MIKTAR)
**********************************************************************************************************************/
UPDATE V
SET V.MALIYET=CAST(F.MALIYET AS decimal(38,8))
FROM STOK_DETAYLI V
LEFT JOIN(
SELECT CIKISID,AMBAR,KODU,ISNULL(SUM(TUTAR)/NULLIF(SUM(MIKTAR),0),0) MALIYET FROM (
SELECT CIKISID,AMBAR,KODU,CAST(MIKTAR*GIRISFIYAT AS decimal(38,8))TUTAR,(MIKTAR) AS MIKTAR
FROM ##FIFOESLEME WHERE KODU=@KODU AND AMBAR=@AMBAR ) AS YHP
GROUP BY CIKISID,KODU,AMBAR
) AS F ON F.CIKISID=V.ID
WHERE GIRISCIKIS='CIKIS' AND V.KODU=@KODU AND V.AMBAR=@AMBAR

/*******************************************************************************************************************
7) Geçiçi tabloda olan Giriş ve Çıkış eşleşmelerini gerçek tabloya yazıyoruz.
*******************************************************************************************************************/
DELETE FIFOESLEME WITH(TABLOCK) WHERE KODU=@KODU AND AMBAR=@AMBAR
INSERT INTO FIFOESLEME
SELECT * FROM ##FIFOESLEME WHERE KODU=@KODU AND AMBAR=@AMBAR
DELETE ##FIFOESLEME WITH(TABLOCK) WHERE KODU=@KODU AND AMBAR=@AMBAR

CLOSE FIFOMALIYET
DEALLOCATE FIFOMALIYET

END

FETCH NEXT FROM STOK INTO @KODU,@AMBAR
END

CLOSE STOK
DEALLOCATE STOK