SQL STUFF

USE [LEVENT]
GO

/****** Object:  UserDefinedFunction [dbo].[BM_210_AYRINTILI_SARFET_FNC]    Script Date: 6.04.2019 12:05:50 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO





ALTER FUNCTION [dbo].[BM_210_AYRINTILI_SARFET_FNC]
(
@DATE DATETIME
)
RETURNS @RESULTTABLE TABLE
(
  TARIH NVARCHAR(MAX),
LOGICALREF INT,
PROJECTREF INT,
SOURCEINDEX INT,
Ambar NVARCHAR(50),
Proje NVARCHAR(50),
[Proje Açıklaması] NVARCHAR(150),
[Proje L2] NVARCHAR(150),
[Proje L2 Açıklaması] NVARCHAR(150),
[Proje L3] NVARCHAR(150),
[Proje L3 Açıklaması] NVARCHAR(150),
[Proje L4] NVARCHAR(150),
[Proje L4 Açıklaması] NVARCHAR(150),
[Aktivite Kodu] NVARCHAR(150),
[Aktivite Adı] NVARCHAR(150),
[Malzeme Özel Kodu] NVARCHAR(150),
[Malzeme Özel Kodu Açıklaması] NVARCHAR(150),
[Malzeme Kodu] NVARCHAR(150),
[Malzeme Adı] NVARCHAR(150),
[Birim] NVARCHAR(150),
[Sarf Miktar] FLOAT,
[Sarf Matrah] FLOAT,
KDV FLOAT
)
AS
BEGIN

INSERT INTO @RESULTTABLE
SELECT
STUFF((SELECT DISTINCT ',' + QUOTENAME( FORMAT( S2.DATE_, 'dd/MM/yyyy', 'tr-TR')) FROM
BV_210_01_STINVTOT S2
WHERE S2.INVENNO > -1
AND S2.DATE_ <= @DATE
AND S2.PROJECTREF = BV_210_01_STINVTOT.PROJECTREF AND S2.STOCKREF=BV_210_01_STINVTOT.STOCKREF 

            FOR XML PATH(''), TYPE

            ).value('.', 'NVARCHAR(MAX)')

        ,1,1,''),
0 AS LOGICALREF
,PROJECTREF
,INVENNO AS SOURCEINDEX
,(SELECT NAME FROM L_CAPIWHOUSE WHERE NR = INVENNO AND FIRMNR = 201) AS Ambar
--,(SELECT LEFT(CODE, 4) FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Proje]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL1 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL1 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL2 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L2]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL2 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L2 Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL3 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L3]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL3 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L3 Açıklaması]
,(SELECT CODE FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL4 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L4]
,(SELECT NAME FROM BM_210_PROJECT WHERE LOGICALREF = (SELECT LEVEL4 FROM BM_210_PROJECT WHERE LOGOPROJECTREF = PROJECTREF AND LOGOPROJECTREF > 0)) AS [Proje L4 Açıklaması]
,(SELECT CODE FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Aktivite Kodu]
,(SELECT NAME FROM LG_210_PROJECT WHERE LOGICALREF = PROJECTREF) AS [Aktivite Adı]
,(SELECT SPECODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Özel Kodu]
,(SELECT TOP 1 DEFINITION_ FROM LG_210_SPECODES WHERE CODETYPE = 1 AND SPECODETYPE = 1 AND SPECODE = (SELECT SPECODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF)) AS [Malzeme Özel Kodu Açıklaması]
,(SELECT CODE FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Kodu]
,(SELECT NAME FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF) AS [Malzeme Adı]
,(SELECT CODE FROM LG_210_UNITSETF WHERE LOGICALREF = (SELECT UNITSETREF FROM LG_210_ITEMS WHERE LOGICALREF = STOCKREF)) AS [Birim]
,ROUND((SUM(ONHAND)), 4) AS [Sarf Miktar]
,ROUND((SUM(VATMATRAH)), 4) [Sarf Matrah]
,ISNULL((SELECT TOP 1 VAT FROM LG_210_01_STLINE WHERE TRCODE = 1 AND CANCELLED = 0 AND LG_210_01_STLINE.STOCKREF = BV_210_01_STINVTOT.STOCKREF ORDER BY DATE_ DESC), -1) AS KDV
FROM
BV_210_01_STINVTOT
WHERE INVENNO > -1
AND DATE_ <= @DATE
AND PROJECTREF > 0
AND (SELECT  S2.STFICHEREF FROM LG_201_01_STLINE S2 WHERE S2.TRCODE = 6 AND S2.STOCKREF = BV_201_01_STINVTOT.STOCKREF AND S2.DATE_ <= @DATE),

AND STFICHEREF IN (SELECT F.LOGICALREF FROM  LG_210_01_STFICHE F WHERE F.SPECODE<>'IFS')
GROUP BY INVENNO, STOCKREF, PROJECTREF , DATE_
HAVING SUM(ONHAND) > 0

RETURN
END


GO