CREATE    VIEW [dbo].[WMS_SIPARIS_SATIRLARI_KONTROL] AS
SELECT 
    T1.LOGICALREF AS [STFICHE_REF],
    T1.FICHENO AS [FIS NO],
    T1.DATE_ AS [TARIH],

    T2.LOGICALREF AS [SATIR REF],
    T2.LOGICALREF AS [SIRA NO],

    T4.CODE AS [ÜRÜN KODU],
    T4.NAME AS [ÜRÜN ADI],
    T4.EXPCTGNO AS [BARKOD],

    T2.AMOUNT AS [SIPARIS ADEDI],

    ISNULL(GT.ONHAND, 0) AS [STOK ADEDI],

    CASE 
        WHEN T4.SPECODE IS NULL OR LTRIM(RTRIM(T4.SPECODE)) = '' THEN 'X'
        ELSE T4.SPECODE
    END AS [BLOK],

    CASE 
        WHEN T4.ISONR IS NULL OR LTRIM(RTRIM(T4.ISONR)) = '' THEN 'X'
        ELSE T4.ISONR
    END AS [RAF KODU],

    ISNULL(T2.WMS_MIKTAR,0) AS [WMS MIKTAR],
    ISNULL(T2.KALAN_ADET,0) AS [KALAN ADET],

    T2.OWNER AS [KONTROL EDEN],

    T2.DURUM AS [DURUM],
	ISNULL(T2.FAREGBINDDATE,'1983-02-28 03:00:00') AS [KONTROL TARIHI]
FROM LG_011_08_STLINE T2
INNER JOIN LG_011_08_STFICHE T1 
    ON T1.LOGICALREF = T2.STFICHEREF
INNER JOIN LG_011_ITEMS T4
    ON T4.LOGICALREF = T2.STOCKREF
LEFT JOIN LV_011_08_GNTOTST GT 
    ON GT.STOCKREF = T4.LOGICALREF 
    AND GT.INVENNO = -1

WHERE 
    T2.TRCODE = 8
    AND T2.LINETYPE = 0
    AND T2.CANCELLED = 0;