IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[VWSTOKISLSAYIMTRHARALIK]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
DROP FUNCTION [dbo].[VWSTOKISLSAYIMTRHARALIK]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[FNSTOKISLSAYIMTRHARALIK]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
execute dbo.sp_executesql @statement = N'--DROP FUNCTION [dbo].[VWSTOKISLSAYIMTRHARALIK] 

CREATE FUNCTION [dbo].[FNSTOKISLSAYIMTRHARALIK] 
(
@ILKTARIH AS Datetime,
@SONTARIH AS Datetime
)
RETURNS TABLE
AS 
RETURN
(
SELECT     SI.SAYIMSIRA, SI.FISTIPI, SI.DEPOID, SI.DEPOKODU, SI.DEPOADI, ISNULL(SI.ANADEPO, 0) AS ANADEPO, SI.ISLEM, SI.STOKID, SI.ACIKLAMA, ISNULL(SI.GCADET, 0) 
                      AS GCADET, SI.BFIYAT, SI.SATIRIND, SI.SATIRIND2, SI.FATNO, SI.KODU, SI.CARIUNVANI, ISNULL(SI.NFIYAT, 0) AS NFIYAT, SI.ADI, SI.TOPLAM, SI.INDORAN1, 
                      SI.EKORAN1, SI.INDORAN2, SI.EKORAN2, SI.INDORAN3, SI.EKORAN3, SI.BAYIID, SI.SATISTID, SI.SALISFIYAT, SI.BAYI, SI.SATIST, SI.STOKKODU, SI.BARKOD, 
                      SI.TARIH, SI.VADE, SI.FATID, SI.SATICIKOMORAN, SI.STKOMORAN, SI.BKOMORAN, SI.IRSALIYE, SI.FATGRUP, SI.KDEPOID, SI.SGRUPKODU, 
                      CAST(SI.SUBEID AS INTEGER) AS SUBEID, SI.SUBEKODU, SI.SUBEADI, SI.ISLEMDETAY, SI.SATIRKNO, SI.SBIRIMI, SI.BIRIM2, SI.KATSAYI2, SI.BIRIM3, SI.KATSAYI3, 
                      SI.BIRIM4, SI.KATSAYI4, SI.KDV, SI.FTTOPLAM, SI.FTKDV, SI.FTGTOPLAM, SI.SGRUPADI, SI.ANAGRUPKODU, SI.ANAGRUPADI, SI.USTGRUPKODU, SI.USTGRUPADI, 
                      SI.ALTGRUPKODU, SI.ALTGRUPADI, SI.MUHNHESAPID, SI.NMUHKODU, SI.NMUHADI, SI.MUHGHESAPID, SI.GMUHKODU, SI.GMUHADI, SI.HESAPID, SI.KDEPOKODU, 
                      SI.KDEPOADI, SI.CARIID, SI.TURU, SI.SAYIMBIRIMI, SI.FBIRIM, SI.FADET, SI.FBFIYAT, SI.TEVORAN, SI.SKODU, SI.KKALDIR, SI.OZELKOD1, SI.OZELKOD2, 
                      SI.OZELKOD3, SI.ISLEMKATSAYISI, SI.SDEPART, SI.SDEPARTADI, SI.BIRIMMALIYET, SI.TOPLAMMALIYET, SI.SKTARIHI, SI.SFIYAT1, SI.ISLREFID, SI.MIADZORUNLU, 
                      SI.ALISKDV, SI.FATCOUNTER, SI.MASRAF, SI.STOKBARKODID, HDOSYAID
FROM         VWSTOKISL AS SI WITH (NOLOCK) LEFT OUTER JOIN
                          (SELECT     SUBEID, DEPOID, MAX(TARIH) AS SONTARIH
                            FROM          SAYIMDONEM
                            WHERE      (KAPANDI = 1) AND TARIH < @ILKTARIH
                            GROUP BY SUBEID, DEPOID) AS SONDNM ON SI.SUBEID = SONDNM.SUBEID AND SI.DEPOID = SONDNM.DEPOID
WHERE     (SI.TURU = ''Hammadde'') AND (SI.TARIH > ISNULL(SONDNM.SONTARIH, ''01.01.1900'')) AND SI.TARIH <= @SONTARIH
UNION ALL  
SELECT     0 AS SAYIMSIRA, ''SAYIM'' AS FISTIPI, SYM.DEPOID, SYM.DEPOKODU, SYM.DEPOADI, ISNULL(SYM.ANADEPO, 0) AS ANADEPO, ''SAYIM'' AS ISLEM, SYM.STOKID, 
                      ''SAYIM'' AS ACIKLAMA, SYM.SAYIM AS GCADET, SYM.ORTFIYAT AS BFIYAT, 0.0 AS SATIRIND, 0.0 AS SATIRIND2, '''' AS FATNO, '''' AS KODU, ''SAYIM'' AS CARIUNVANI, 
                      SYM.ORTFIYAT AS NFIYAT, SYM.STOKADI AS ADI, SYM.ORTFIYAT * SYM.SAYIM AS TOPLAM, 0.0 AS INDORAN1, 0.0 AS EKORAN1, 0.0 AS INDORAN2, 
                      0.0 AS EKORAN2, 0.0 AS INDORAN3, 0.0 AS EKORAN3, 0 AS BAYIID, 0 AS SATISTID, SYM.SALISFIYAT, '''' AS BAYI, '''' AS SATIST, SYM.STOKKODU, '''' AS BARKOD, 
                      SYM.TARIH, SYM.TARIH AS VADE, SYM.SAYIMID AS FATID, 0.0 AS SATICIKOMORAN, 0.0 AS STKOMORAN, 0.0 AS BKOMORAN, 0 AS IRSALIYE, '''' AS FATGRUP, 
                      0 AS KDEPOID, SYM.SGRUPKODU, CAST(SYM.SUBEID AS INTEGER) AS SUBEID, SYM.SUBEKODU, SYM.SUBEADI, ''SAYIM'' AS ISLEMDETAY, 
                      SYM.SAYIMID AS SATIRKNO, SYM.STOKBIRIMI AS SBIRIMI, SYM.BIRIM2, SYM.KATSAYI2, SYM.BIRIM3, SYM.KATSAYI3, SYM.BIRIM4, SYM.KATSAYI4, 0.0 AS KDV, 
                      0.0 AS FTTOPLAM, 0.0 AS FTKDV, SYM.ORTFIYAT * SYM.SAYIM AS FTGTOPLAM, SYM.SGRUPADI, SYM.ANAGRUP, SYM.ANAGRUPADI, SYM.USTGRUP, 
                      SYM.USTGRUPADI, SYM.ALTGRUP, SYM.ALTGRUPADI, 0 AS MUHNHESAPID, '''' AS NMUHKODU, '''' AS NMUHADI, 0 AS MUHGHESAPID, '''' AS GMUHKODU, 
                      '''' AS GMUHADI, 0 AS HESAPID, '''' AS KDEPOKODU, '''' AS KDEPOADI, 0 AS CARIID, SYM.TURU, SYM.SAYIMBIRIMI, SYM.SBIRIM AS FBIRIM, SYM.SMIKTAR AS FADET, 
                      SYM.ORTFIYAT AS FBFIYAT, '''' AS TEVORAN, SYM.SKODU, SYM.KKALDIR, NULL AS OZELKOD1, NULL AS OZELKOD2, NULL AS OZELKOD3, 
                      SYM.SAYIMKATSAYISI, NULL AS SDEPART, NULL AS SDEPARTADI, 0 AS BIRIMMALIYET, 0 AS TOPLAMMALIYET, NULL AS SKTARIHI, SYM.SFIYAT1, NULL 
                      AS ISLREFID, SYM.MIADZORUNLU, SYM.ALISKDV, NULL AS FATCOUNTER, SYM.MASRAF, NULL AS STOKBARKODID, NULL AS HDOSYAID
FROM         VWSAYIM2 AS SYM INNER JOIN
                          (SELECT     SUBEID, DEPOID, MAX(TARIH) AS SONTARIH
                            FROM          SAYIMDONEM
                            WHERE      (KAPANDI = 1) AND TARIH < @ILKTARIH
                            GROUP BY SUBEID, DEPOID) AS SONDNM ON SYM.SUBEID = SONDNM.SUBEID AND SYM.DEPOID = SONDNM.DEPOID AND SYM.TARIH = SONDNM.SONTARIH
)                            
' 
END

GO


