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.