SQL Server’da FIFO Maliyetinin T-SQL ile Uygulanması
Herkese merhaba,
Bu yazıda SQL Server’da fifo maliyetinin t-sql ile uygulanması hakkında bilgi vereceğim.
Aslında bu bilgiyi veren ve kodları hazırlayan kişi Vedat Özer Bey. Sorguyu hazırlamış. Gerekli açıklamaları eklemiş. Bana da paylaşması kaldı.
Kendisine bu çalışması için ayrıca teşekkürler.
Aşağıdaki kod bloğunu yeni oluşturduğunuz veritabanı üzerinde deneyebilirsiniz.
/* Bu kısmı yorumdan çıkarıp çalıştırabilirsiniz. Bu sayede yeni database oluşup sonrasında sorgu çalışacaktır. CREATE DATABASE YeniDB USE YeniDB */ SET NOCOUNT ON; /********************************************************************************************************************** Hesaplama Türü : Fifo Maliyet 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ırasyla devam eder. Yani stoklara giren malların yine giriş sırasıyla stoktan çıkarlar. Sorguyu Yazan : VEDAT ÖZER Tablo İsimleri Stok Hareketler Tablosu : STOK_DETAYLI Stok Giriş Çıkış Eşlemesi : FIFOESLEME Envanter Tablosu : FIFOENVANTER Negatif Bilgiler : STOKNEGATIF Stok Negatife düştüğünde maliyet hesabı yapılmıyacak. Eksiğe düştüğü tarihten itibaren aşağıdaki tabloya yazacaz. **********************************************************************************************************************/
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);
/********************************************************************************************************************** Maliyet hesaplamaların doğru bir şekilde yapmamız için Giriş ve Çıkış hareketlerin ilişkilendirmemiz lazım. Aşağıdaki tabloya giriş ve çıkış bilgilerini yazacaz. **********************************************************************************************************************/
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]);
/********************************************************************************************************************** Ambarlara göre elimizdeki stokğun bilgilerini yazacağımız tabloyu oluşturuyoruz. **********************************************************************************************************************/
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]);
/********************************************************************************************************************** Aşağıdaki alanlarda maliyet hesabını yapacağımız satırları ekliyoruz. **********************************************************************************************************************/
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);
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';
/********************************************************************************************************************** Artık Gerekli maliyet hesaplamalarına başlıyacağız. Numaralı şekilde gideceğiz **********************************************************************************************************************/ /********************************************************************************************************************** 1) Gerçek tabloda olan bütün verileri Geçiçi tabloya aktarı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);
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);
/********************************************************************************************************************** 2) Geçiçi tabloda Yürüyen bakiye işlemini yapıyoruz. Eksi değer veren varmı 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);
/********************************************************************************************************************** 3) Döngü ile verileri yazacağımız geçiçi tabloyu 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
);
/********************************************************************************************************************** 4) Negatif olan stokları toplu olarak tabloya aktarıyoruz. **********************************************************************************************************************/
TRUNCATE TABLE STOKNEGATIF;
INSERT INTO STOKNEGATIF
SELECT KODU,
ADI,
TARIH,
AMBAR,
GIRISCIKIS,
FISTURU,
MIKTAR,
KALAN
FROM ##VERIDATA
WHERE KALAN < 0;
/********************************************************************************************************************** 5) Malıyet Hesabını yapıyoruz. **********************************************************************************************************************/
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;
SELECT *
FROM FIFOENVANTER;
SELECT *
FROM FIFOESLEME;
SELECT *
FROM STOK_DETAYLI;Sorguyu çalıştırdığınızda aşağıdaki gibi bir sonuç alacaksınız.
Sorgu ile ilgili detaylı bilgi almak isterseniz Vedat Özer Beye ulaşabilirsiniz.
Herkese çalışma hayatında ve yaşamında başarılar kolaylıklar.
773 Kez Okundu
