/****** Object:  View [dbo].[QMUH_EFATISL]    Script Date: 12/06/2017 18:02:25 ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER VIEW [dbo].[QMUH_EFATISL]
AS
     SELECT *,
            CAST(CASE
                     WHEN TEV_CARPAN = 1
                          AND TEV_BOLEN = 1
                     THEN 0.00
                     ELSE((SATIR.SATIRKDVTUTAR * TEV_CARPAN) / TEV_BOLEN)
                 END AS FLOAT) AS TEV_TUTAR,
            CAST(CASE
                     WHEN TEV_CARPAN = 1
                          AND TEV_BOLEN = 1
                     THEN 0.00
                     ELSE(((100.00 * TEV_CARPAN) / TEV_BOLEN))
                 END AS FLOAT) AS TEV_ORAN
     FROM
(
    SELECT ROW_NUMBER() OVER(PARTITION BY FATISL.FATID ORDER BY FATISL.FATISLID) AS SATIRNO,
           FATISL.FATISLID,
           FATISL.FATID,
           IsNull(FATISL.FADET, 1.00) AS SATIRADET,
           FATISL.FBFIYAT AS SATIRBFIYAT,
           FATISL.KDV AS SATIRKDVORAN,
           CASE
               WHEN IsNull(STOK.STOKID, 0) < 0
               THEN FATISL.ACIKLAMA
               ELSE STOK.ADI
           END AS SATIRACIKLAMA,
           IsNull(FI.SATIRIND, 0) AS SATIRIND,
           IsNull(FI.SATIRIND2, 0) AS SATIRIND2,
           IsNull(FBIRIM, 'Adet') AS FBIRIM,
           FATISL.NSATISKDV AS SATIRKDVTUTAR,
           FATISL.KDV2,
           FATISL.ARATOPLAM AS ARATOPLAM,
           FI.KDVACIKLAMA,
           FI.KDVSIZLIKKODU,
           FI.TEVKANUNKODU,
           FI.TEVKANUNACIKLAMA,
           CAST(CASE
                    WHEN CHARINDEX('/', IsNull(FATISL.TEVORAN, ''), 1) < 1
                    THEN 1
                    ELSE LEFT(FATISL.TEVORAN, CHARINDEX('/', FATISL.TEVORAN, 1)-1)
                END AS FLOAT) AS TEV_CARPAN,
           CAST(CASE
                    WHEN CHARINDEX('/', IsNull(FATISL.TEVORAN, ''), 1) < 1
                    THEN 1
                    ELSE LEN(FATISL.TEVORAN)-RIGHT(FATISL.TEVORAN, CHARINDEX('/', FATISL.TEVORAN, 1))
                END AS FLOAT) AS TEV_BOLEN
    FROM VWFATURAISL AS FATISL
         INNER JOIN FATISL AS FI ON FI.FATISLID = FATISL.FATISLID
         LEFT JOIN STOK ON FATISL.STOKID = STOK.STOKID
) AS SATIR;
GO