SELECT 
  TOP (100) PERCENT CAST(
    LG_101_01_ORFICHE.BRANCH AS VARCHAR(3)
  )+ ' - ' +(
    SELECT 
      NAME 
    FROM 
      L_CAPIDIV 
    WHERE 
      NR = LG_101_01_ORFICHE.BRANCH 
      AND FIRMNR = 101
  ) AS İşyeri, 
  ORFLINE.DUEDATE AS [Sevk Tarihi], 
  LG_101_01_ORFICHE.DATE_ AS Sipariş_Tarihi, 
  LG_101_01_ORFICHE.FICHENO Sipariş_No, 
  CLCARD.CODE AS Müşteri_No, 
  CLCARD.DEFINITION_ AS Müşteri_Adı, 
  ISNULL(
    (
      SELECT 
        LEFT(S.CODE, 7)+ ' - ' +(
          SELECT 
            NAME 
          FROM 
            LG_101_ITEMS 
          WHERE 
            CODE = LEFT(S.CODE, 7)
        ) 
      FROM 
        LG_101_ITEMS I (NOLOCK) 
        LEFT OUTER JOIN LG_101_ITMCLSAS C (NOLOCK) ON I.LOGICALREF = C.CHILDREF 
        AND C.UPLEVEL = 0 
        LEFT OUTER JOIN LG_101_ITEMS S (NOLOCK) ON C.PARENTREF = S.LOGICALREF 
      WHERE 
        S.CODE LIKE 'MS01.%' 
        AND I.CODE NOT LIKE 'MS01.%' 
        AND I.LOGICALREF = ITEMS.LOGICALREF
    ), 
    ''
  ) AS Üst_Sınıf, 
  ISNULL(
    (
      SELECT 
        S.CODE + ' - ' + S.NAME 
      FROM 
        LG_101_ITEMS I (NOLOCK) 
        LEFT OUTER JOIN LG_101_ITMCLSAS C (NOLOCK) ON I.LOGICALREF = C.CHILDREF 
        AND C.UPLEVEL = 0 
        LEFT OUTER JOIN LG_101_ITEMS S (NOLOCK) ON C.PARENTREF = S.LOGICALREF 
      WHERE 
        S.CODE LIKE 'MS01.%' 
        AND I.CODE NOT LIKE 'MS01.%' 
        AND I.LOGICALREF = ITEMS.LOGICALREF
    ), 
    ''
  ) AS Mlz_Sınıfı, 
  ITEMS.PRODUCERCODE Üretici_Kodu, 
  ITEMS.CODE AS Mlz_Kodu, 
  ITEMS.NAME AS Mlz_Açıklaması, 
  'Adet' as Birim, 
  ORFLINE.AMOUNT AS Sipariş_Miktarı, 
  ORFLINE.SHIPPEDAMOUNT AS Sevk_Edilen_Miktar, 
  ROUND(VATMATRAH, 2) AS Net_Tutar, 
  ROUND(VATAMNT, 2) AS KDV_Tutarı, 
  ROUND(VATMATRAH + VATAMNT, 2) AS Brüt_Tutar, 
  CASE (TOTAL - VATMATRAH - VATAMNT) WHEN 0 THEN 0 ELSE ROUND(
    100 *(TOTAL - VATMATRAH - VATAMNT)/ TOTAL, 
    2
  ) END AS İndirim, 
  ROUND(TOTAL - VATMATRAH - VATAMNT, 2) AS İndirim_Tutarı, 
  dbo.Get_Siparis_Masraf_ORFICHE_101(ORDFICHEREF) Masraf, 
  dbo.Get_Satis_Eleman_adi(ORFLINE.SALESMANREF) Satis_Elemani, 
  ORFLINE.CLOSED AS [0(Açık) ] 
FROM 
  LG_101_SPECODES AS SPECODES 
  RIGHT OUTER JOIN LG_101_01_ORFLINE AS ORFLINE ON SPECODES.SPECODE = ORFLINE.SPECODE 
  LEFT OUTER JOIN LG_101_ITEMS AS ITEMS ON ORFLINE.STOCKREF = ITEMS.LOGICALREF FULL 
  OUTER JOIN LG_101_CLCARD AS CLCARD 
  RIGHT OUTER JOIN LG_101_SHIPINFO 
  RIGHT OUTER JOIN LG_101_01_ORFICHE ON LG_101_SHIPINFO.LOGICALREF = LG_101_01_ORFICHE.SHIPINFOREF ON CLCARD.LOGICALREF = LG_101_01_ORFICHE.CLIENTREF ON ORFLINE.ORDFICHEREF = LG_101_01_ORFICHE.LOGICALREF 
WHERE 
  (LG_101_01_ORFICHE.TRCODE = 1) 
  AND LINETYPE = 0 
  AND LG_101_01_ORFICHE.DEPARTMENT = 2 
ORDER BY 
  Sipariş_Tarihi DESC