none
Fıfo Maliyet Hesaplama T-Sql (Çözümü) RRS feed

  • Soru

  • Merhaba,

    Fıfo maliyet ile yazmış olduğum bir çözümü sizlerle paylaşıyorum. Çözüm esasında farklı yollar olabilir , belki de ileri seviye bilgisi olan arkadaşlar daha iyisini yazabilir.

    USE TestDB
    
    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
    
    


    6 Ekim 2019 Pazar 07:18