CREATE PROC [dbo].[SP_MALZEME_HAREKETLERI]
(@stockref VARCHAR(MAX), @yil SMALLINT)
AS
BEGIN
SELECT BILLED,DATE_, FICHENO, TUR,  DEFINITION_, AMOUNT, BIRIM, AMBAR, YIL, CAPIBLOCK_CREADEDDATE FROM 
(
(SELECT 
CASE WHEN STL.BILLED = 1 THEN 'F' ELSE '' END AS BILLED,
STF.DATE_,
STF.FICHENO,
[UNIVERSALDB].dbo.fn_trcode('Stline',STL.TRCODE,STL.STFICHEREF,NULL) as TUR,
ISNULL(CC.DEFINITION_,'') AS DEFINITION_,
STL.AMOUNT,
(STL.VATMATRAH / STL.AMOUNT) AS [BIRIM],
(SELECT SUM(ONHAND) FROM LV_011_08_STINVTOT WHERE INVENNO=-1 AND DATE_ < STF.CAPIBLOCK_CREADEDDATE AND STOCKREF=STL.STOCKREF GROUP BY STOCKREF) AS AMBAR,
YEAR(STF.DATE_) AS YIL, STF.CAPIBLOCK_CREADEDDATE
FROM [UNIVERSALDB].dbo.[LG_011_08_STLINE] AS STL WITH (NOLOCK)
LEFT JOIN [UNIVERSALDB].[dbo].LG_011_UNITBARCODE AS BAR WITH (NOLOCK) ON BAR.ITEMREF=STL.STOCKREF AND BAR.LINENR=1 AND BAR.BARCODE<>'' AND BAR.BARCODE IS NOT NULL
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_08_STFICHE] AS STF WITH (NOLOCK) ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_CLCARD] AS CC WITH (NOLOCK) ON CC.LOGICALREF=STL.CLIENTREF
WHERE STL.LINETYPE=0 AND STL.CANCELLED=0 AND STL.STOCKREF=(SELECT LOGICALREF FROM LG_011_ITEMS WHERE CODE=@stockref))
UNION ALL
(SELECT 
CASE WHEN STL.BILLED = 1 THEN 'F' ELSE '' END AS BILLED,
STF.DATE_,
STF.FICHENO,
[UNIVERSALDB].dbo.fn_trcode('Stline',STL.TRCODE,STL.STFICHEREF,NULL) as TUR,
ISNULL(CC.DEFINITION_,'') AS DEFINITION_,
STL.AMOUNT,
(STL.VATMATRAH / STL.AMOUNT) AS [BIRIM],
(SELECT SUM(ONHAND) FROM LV_011_07_STINVTOT WHERE INVENNO=-1 AND DATE_ < STF.CAPIBLOCK_CREADEDDATE AND STOCKREF=STL.STOCKREF GROUP BY STOCKREF) AS AMBAR,
YEAR(STF.DATE_) AS YIL, STF.CAPIBLOCK_CREADEDDATE
FROM [UNIVERSALDB].dbo.[LG_011_07_STLINE] AS STL WITH (NOLOCK)
LEFT JOIN [UNIVERSALDB].[dbo].LG_011_UNITBARCODE AS BAR WITH (NOLOCK) ON BAR.ITEMREF=STL.STOCKREF AND BAR.LINENR=1 AND BAR.BARCODE<>'' AND BAR.BARCODE IS NOT NULL
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_07_STFICHE] AS STF WITH (NOLOCK) ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_CLCARD] AS CC WITH (NOLOCK) ON CC.LOGICALREF=STL.CLIENTREF
WHERE STL.LINETYPE=0 AND STL.CANCELLED=0 AND STL.STOCKREF=(SELECT LOGICALREF FROM LG_011_ITEMS WHERE CODE=@stockref))
UNION ALL
(SELECT 
CASE WHEN STL.BILLED = 1 THEN 'F' ELSE '' END AS BILLED,
STF.DATE_,
STF.FICHENO,
[UNIVERSALDB].dbo.fn_trcode('Stline',STL.TRCODE,STL.STFICHEREF,NULL) as TUR,
ISNULL(CC.DEFINITION_,'') AS DEFINITION_,
STL.AMOUNT,
(STL.VATMATRAH / STL.AMOUNT) AS [BIRIM],
(SELECT SUM(ONHAND) FROM LV_011_06_STINVTOT WHERE INVENNO=-1 AND DATE_ < STF.CAPIBLOCK_CREADEDDATE AND STOCKREF=STL.STOCKREF GROUP BY STOCKREF) AS AMBAR,
YEAR(STF.DATE_) AS YIL, STF.CAPIBLOCK_CREADEDDATE
FROM [UNIVERSALDB].dbo.[LG_011_06_STLINE] AS STL WITH (NOLOCK)
LEFT JOIN [UNIVERSALDB].[dbo].LG_011_UNITBARCODE AS BAR WITH (NOLOCK) ON BAR.ITEMREF=STL.STOCKREF AND BAR.LINENR=1 AND BAR.BARCODE<>'' AND BAR.BARCODE IS NOT NULL
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_06_STFICHE] AS STF WITH (NOLOCK) ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_CLCARD] AS CC WITH (NOLOCK) ON CC.LOGICALREF=STL.CLIENTREF
WHERE STL.LINETYPE=0 AND STL.CANCELLED=0 AND STL.STOCKREF=(SELECT LOGICALREF FROM LG_011_ITEMS WHERE CODE=@stockref))
UNION ALL
(SELECT 
CASE WHEN STL.BILLED = 1 THEN 'F' ELSE '' END AS BILLED,
STF.DATE_,
STF.FICHENO,
[UNIVERSALDB].dbo.fn_trcode('Stline',STL.TRCODE,STL.STFICHEREF,NULL) as TUR,
ISNULL(CC.DEFINITION_,'') AS DEFINITION_,
STL.AMOUNT,
(STL.VATMATRAH / STL.AMOUNT) AS [BIRIM],
(SELECT SUM(ONHAND) FROM LV_011_05_STINVTOT WHERE INVENNO=-1 AND DATE_ < STF.CAPIBLOCK_CREADEDDATE AND STOCKREF=STL.STOCKREF GROUP BY STOCKREF) AS AMBAR,
YEAR(STF.DATE_) AS YIL, STF.CAPIBLOCK_CREADEDDATE
FROM [UNIVERSALDB].dbo.[LG_011_05_STLINE] AS STL WITH (NOLOCK)
LEFT JOIN [UNIVERSALDB].[dbo].LG_011_UNITBARCODE AS BAR WITH (NOLOCK) ON BAR.ITEMREF=STL.STOCKREF AND BAR.LINENR=1 AND BAR.BARCODE<>'' AND BAR.BARCODE IS NOT NULL
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_05_STFICHE] AS STF WITH (NOLOCK) ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_CLCARD] AS CC WITH (NOLOCK) ON CC.LOGICALREF=STL.CLIENTREF
WHERE STL.LINETYPE=0 AND STL.CANCELLED=0 AND STL.STOCKREF=(SELECT LOGICALREF FROM LG_011_ITEMS WHERE CODE=@stockref))
UNION ALL
(SELECT 
CASE WHEN STL.BILLED = 1 THEN 'F' ELSE '' END AS BILLED,
STF.DATE_,
STF.FICHENO,
[UNIVERSALDB].dbo.fn_trcode('Stline',STL.TRCODE,STL.STFICHEREF,NULL) as TUR,
ISNULL(CC.DEFINITION_,'') AS DEFINITION_,
STL.AMOUNT,
(STL.VATMATRAH / STL.AMOUNT) AS [BIRIM],
(SELECT SUM(ONHAND) FROM LV_011_04_STINVTOT WHERE INVENNO=-1 AND DATE_ < STF.CAPIBLOCK_CREADEDDATE AND STOCKREF=STL.STOCKREF GROUP BY STOCKREF) AS AMBAR,
YEAR(STF.DATE_) AS YIL, STF.CAPIBLOCK_CREADEDDATE
FROM [UNIVERSALDB].dbo.[LG_011_04_STLINE] AS STL WITH (NOLOCK)
LEFT JOIN [UNIVERSALDB].[dbo].LG_011_UNITBARCODE AS BAR WITH (NOLOCK) ON BAR.ITEMREF=STL.STOCKREF AND BAR.LINENR=1 AND BAR.BARCODE<>'' AND BAR.BARCODE IS NOT NULL
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_04_STFICHE] AS STF WITH (NOLOCK) ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_CLCARD] AS CC WITH (NOLOCK) ON CC.LOGICALREF=STL.CLIENTREF
WHERE STL.LINETYPE=0 AND STL.CANCELLED=0 AND STL.STOCKREF=(SELECT LOGICALREF FROM LG_011_ITEMS WHERE CODE=@stockref))
UNION ALL
(SELECT 
CASE WHEN STL.BILLED = 1 THEN 'F' ELSE '' END AS BILLED,
STF.DATE_,
STF.FICHENO,
[UNIVERSALDB].dbo.fn_trcode('Stline',STL.TRCODE,STL.STFICHEREF,NULL) as TUR,
ISNULL(CC.DEFINITION_,'') AS DEFINITION_,
STL.AMOUNT,
(STL.VATMATRAH / STL.AMOUNT) AS [BIRIM],
(SELECT SUM(ONHAND) FROM LV_011_03_STINVTOT WHERE INVENNO=-1 AND DATE_ < STF.CAPIBLOCK_CREADEDDATE AND STOCKREF=STL.STOCKREF GROUP BY STOCKREF) AS AMBAR,
YEAR(STF.DATE_) AS YIL, STF.CAPIBLOCK_CREADEDDATE
FROM [UNIVERSALDB].dbo.[LG_011_03_STLINE] AS STL WITH (NOLOCK)
LEFT JOIN [UNIVERSALDB].[dbo].LG_011_UNITBARCODE AS BAR WITH (NOLOCK) ON BAR.ITEMREF=STL.STOCKREF AND BAR.LINENR=1 AND BAR.BARCODE<>'' AND BAR.BARCODE IS NOT NULL
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_03_STFICHE] AS STF WITH (NOLOCK) ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_CLCARD] AS CC WITH (NOLOCK) ON CC.LOGICALREF=STL.CLIENTREF
WHERE STL.LINETYPE=0 AND STL.CANCELLED=0 AND STL.STOCKREF=(SELECT LOGICALREF FROM LG_011_ITEMS WHERE CODE=@stockref))
UNION ALL
(SELECT 
CASE WHEN STL.BILLED = 1 THEN 'F' ELSE '' END AS BILLED,
STF.DATE_,
STF.FICHENO,
[UNIVERSALDB].dbo.fn_trcode('Stline',STL.TRCODE,STL.STFICHEREF,NULL) as TUR,
ISNULL(CC.DEFINITION_,'') AS DEFINITION_,
STL.AMOUNT,
(STL.VATMATRAH / STL.AMOUNT) AS [BIRIM],
(SELECT SUM(ONHAND) FROM LV_011_02_STINVTOT WHERE INVENNO=-1 AND DATE_ < STF.CAPIBLOCK_CREADEDDATE AND STOCKREF=STL.STOCKREF GROUP BY STOCKREF) AS AMBAR,
YEAR(STF.DATE_) AS YIL, STF.CAPIBLOCK_CREADEDDATE
FROM [UNIVERSALDB].dbo.[LG_011_02_STLINE] AS STL WITH (NOLOCK)
LEFT JOIN [UNIVERSALDB].[dbo].LG_011_UNITBARCODE AS BAR WITH (NOLOCK) ON BAR.ITEMREF=STL.STOCKREF AND BAR.LINENR=1 AND BAR.BARCODE<>'' AND BAR.BARCODE IS NOT NULL
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_02_STFICHE] AS STF WITH (NOLOCK) ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_CLCARD] AS CC WITH (NOLOCK) ON CC.LOGICALREF=STL.CLIENTREF
WHERE STL.LINETYPE=0 AND STL.CANCELLED=0 AND STL.STOCKREF=(SELECT LOGICALREF FROM LG_011_ITEMS WHERE CODE=@stockref))
UNION ALL
(SELECT 
CASE WHEN STL.BILLED = 1 THEN 'F' ELSE '' END AS BILLED,
STF.DATE_,
STF.FICHENO,
[UNIVERSALDB].dbo.fn_trcode('Stline',STL.TRCODE,STL.STFICHEREF,NULL) as TUR,
ISNULL(CC.DEFINITION_,'') AS DEFINITION_,
STL.AMOUNT,
(STL.VATMATRAH / STL.AMOUNT) AS [BIRIM],
(SELECT SUM(ONHAND) FROM LV_011_01_STINVTOT WHERE INVENNO=-1 AND DATE_ < STF.CAPIBLOCK_CREADEDDATE AND STOCKREF=STL.STOCKREF GROUP BY STOCKREF) AS AMBAR,
YEAR(STF.DATE_) AS YIL, STF.CAPIBLOCK_CREADEDDATE
FROM [UNIVERSALDB].dbo.[LG_011_01_STLINE] AS STL WITH (NOLOCK)
LEFT JOIN [UNIVERSALDB].[dbo].LG_011_UNITBARCODE AS BAR WITH (NOLOCK) ON BAR.ITEMREF=STL.STOCKREF AND BAR.LINENR=1 AND BAR.BARCODE<>'' AND BAR.BARCODE IS NOT NULL
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_01_STFICHE] AS STF WITH (NOLOCK) ON STF.LOGICALREF=STL.STFICHEREF
LEFT JOIN [UNIVERSALDB].dbo.[LG_011_CLCARD] AS CC WITH (NOLOCK) ON CC.LOGICALREF=STL.CLIENTREF
WHERE STL.LINETYPE=0 AND STL.CANCELLED=0 AND STL.STOCKREF=(SELECT LOGICALREF FROM LG_011_ITEMS WHERE CODE=@stockref))
) HAREKETLER
WHERE YEAR(HAREKETLER.DATE_)=@yil
ORDER BY HAREKETLER.CAPIBLOCK_CREADEDDATE DESC
END