USE [logo]
GO
/****** Object: StoredProcedure [dbo].[sp_GetItemExtre] Script Date: 09/15/2009 11:14:42 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_GetItemExtre]
@t_BegDate DATETIME,
@t_EndDate DATETIME,
@n_WhNo INT,
@s_ItmName nvarchar(51)
AS
SELECT
CASE
WHEN STL.TRCODE = 1 THEN 'Satın Alma İrsaliyesi'
WHEN STL.TRCODE = 2 THEN 'Perakende Satış İade İrsaliyesi'
WHEN STL.TRCODE = 3 THEN 'Toptan Satış İade İrsaliyesi'
WHEN STL.TRCODE = 4 THEN 'Konsinye Çıkış İade İrsaliyesi'
WHEN STL.TRCODE = 5 THEN 'Konsinye Giriş İrsaliyesi'
WHEN STL.TRCODE = 6 THEN 'Alım İade İrsaliyesi'
WHEN STL.TRCODE = 7 THEN 'Perakende Satış İrsaliyesi'
WHEN STL.TRCODE = 8 THEN 'Toptan Satış İrsaliyesi'
WHEN STL.TRCODE = 9 THEN 'Konsinye Çıkış İrsaliyesi'
WHEN STL.TRCODE = 10 THEN 'Konsinye Giriş İade İrsaliyesi'
WHEN STL.TRCODE = 26 THEN 'Müstahsil İrsaliyesi'
WHEN STL.TRCODE = 11 THEN 'Fire Fişi'
WHEN STL.TRCODE = 12 THEN 'Sarf Fişi'
WHEN STL.TRCODE = 13 THEN 'Üretimden Giriş Fişi'
WHEN STL.TRCODE = 14 THEN 'Devir Fişi'
WHEN STL.TRCODE = 25 THEN 'Ambar Fişi'
WHEN STL.TRCODE = 50 THEN 'Sayım Fazlası Fişi'
WHEN STL.TRCODE = 51 THEN 'Sayım Eksiği Fişi'
WHEN STL.TRCODE = 15 THEN 'Sair Giriş Fişi'
WHEN STL.TRCODE = 20 THEN 'Sair Çıkış Fişi'
WHEN STL.TRCODE = 21 THEN 'Şirket Kullanıma Çıkış Fişi'
END AS [HARAKET TURU],
STL.DATE_ AS [TARIH],
STF.FICHENO AS [FIS NO],
ITM.CODE AS [MALZEME KODU],
ITM.NAME AS [MALZEME ADI],
CASE
WHEN STL.IOCODE IN (1,3) THEN CONVERT(FLOAT,CONVERT(DECIMAL(10,3),AMOUNT))
ELSE 0
END AS [GIRIS MIKTAR],
CASE
WHEN STL.IOCODE IN (2,4) THEN CONVERT(FLOAT,CONVERT(DECIMAL(10,3),AMOUNT))
ELSE 0
END AS [CIKIS MIKTAR],
CONVERT(FLOAT,CONVERT(DECIMAL(10,3),PRICE)) AS [FIYAT],
CONVERT(FLOAT,CONVERT(DECIMAL(10,3),TOTAL)) AS [TUTAR],
ISNULL((SELECT SUM(ONHAND)FROM LG_130_01_STINVTOT WHERE STOCKREF = ITM.LOGICALREF AND INVENNO = @n_WhNo AND DATE_ <= STL.DATE_ ),0) AS [STOK MIKTARI]
FROM LG_130_01_STLINE AS STL LEFT JOIN LG_130_ITEMS ITM ON ITM.LOGICALREF = STL.STOCKREF
LEFT JOIN LG_130_01_STFICHE STF ON STF.LOGICALREF = STL.STFICHEREF
WHERE STL.LINETYPE = 0 AND ((STL.SOURCEINDEX = @n_WhNo AND STL.IOCODE <> 2 )OR STL.DESTINDEX = @n_WhNo)
AND STL.DATE_ >= @t_BegDate
AND STL.DATE_ <= @t_EndDate
AND ITM.NAME = @s_ItmName
AND NOT(STL.TRCODE = 14 AND STL.INVOICEREF > 0)
ORDER BY STL.DATE_