CREATE    VIEW [dbo].[WMS_SIPARIS_KONTROL]
AS
WITH DistinctRaf AS (
    SELECT DISTINCT
        ST.STFICHEREF,
        CASE 
            WHEN LTRIM(RTRIM(IT.ISONR)) = '' OR IT.ISONR IS NULL THEN 'X'
            ELSE IT.ISONR 
        END AS ISONR
    FROM LG_011_08_STLINE ST
    INNER JOIN LG_011_ITEMS IT ON ST.STOCKREF = IT.LOGICALREF
    WHERE ST.LINETYPE = 0 
      AND ST.CANCELLED = 0
	  AND ST.TRCODE=8
),

IrsaliyeRafGruplari AS (
    SELECT 
        DR.STFICHEREF,
        STUFF((SELECT ', ' + DR2.ISONR
               FROM DistinctRaf DR2
               WHERE DR2.STFICHEREF = DR.STFICHEREF
               ORDER BY DR2.ISONR
               FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS RafKoduListesi
    FROM DistinctRaf DR
    GROUP BY DR.STFICHEREF
)

SELECT 
    T1.LOGICALREF AS STFICHE_REF,
    T1.FICHENO AS REFERANS_NUMARASI, 
    T1.DATE_ AS IRSALIYE_TARIHI,
    T3.DEFINITION_ AS FIRMA_ADI,
    UPPER(T3.CITY) AS SEHIR,
    COUNT(T2.LOGICALREF) AS TOPLAM_KALEM_SAYISI,
    ISG.RafKoduListesi AS RAF_GRUPLU_BLOK_BILGILERI,
    T1.CYPHCODE AS ODEME_PLAN_KODU,
    T1.BILLED AS FATURALANDI_DURUMU,
    T1.SPECODE AS OZEL_KOD_1, 
    T1.DOCODE AS BELGE_NO, 
    T1.CAPIBLOCK_CREATEDBY AS OLUSTURAN_KULLANICI,
	T1.ISLEM_DURUMU,
	T1.ISLEM_SAATI,
	T1.PORDERFICHENO AS KONTROL_EDEN

FROM LG_011_08_STFICHE T1
JOIN LG_011_08_STLINE T2 
    ON T1.LOGICALREF = T2.STFICHEREF 
JOIN LG_011_CLCARD T3 
    ON T1.CLIENTREF = T3.LOGICALREF
JOIN IrsaliyeRafGruplari ISG
    ON T1.LOGICALREF = ISG.STFICHEREF

WHERE T1.TRCODE = 8 
  AND T2.LINETYPE = 0

GROUP BY 
    T1.LOGICALREF, T1.FICHENO, T1.DATE_, T3.DEFINITION_, T3.CITY, 
    ISG.RafKoduListesi, T1.CYPHCODE, T1.BILLED, T1.SPECODE, 
    T1.DOCODE, T1.CAPIBLOCK_CREATEDBY, 
	T1.ISLEM_DURUMU,
	T1.ISLEM_SAATI,
	T1.PORDERFICHENO