IF  EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[QMUH_EFATISL]') AND OBJECTPROPERTY(id,N'IsVIew') = 1)
BEGIN
    INSERT INTO SCRIPT_BACKUPS (SCRIPTNAME,SCRIPT,SCRIPTDATE) SELECT '[QMUH_EFATISL]' SCRIPTNAME,OBJECT_DEFINITION(OBJECT_ID('[QMUH_EFATISL]')) AS SCRIPT,GETDATE() AS SCRIPTDATE
    DROP VIEW [QMUH_EFATISL]
END
GO  

CREATE view [dbo].[QMUH_EFATISL]
as 
/* QMUH_EFATISL ETS */
select    Row_Number() over ( partition by FI.FATID
                                  order by FI.FATISLID
                            )
       as SATIRNO,

       FI.FATISLID,
       FI.FATID,

          OZET.T
       as OZET,

          OZETISL.T
       as OZETISL,

          DOVIZLI.T
       as DOVIZLI,

          KUR.T
       as KUR,

          ACIKLAMA.T
       as ACIKLAMA,

       FI.FADET,
       FI.FBIRIM,

          FBFIYAT.T
       as FBFIYAT,

          ARATOPLAM.T
       as ARATOPLAM,

       FI.SATIRIND,
       FI.SATIRIND2,
       FI.SATIRINDT,

          INDTOPLAM.T
       as INDTOPLAM,

          NETTOPLAM.T
       as NETTOPLAM,

          FTINDEK.T
       as FATURA_TINDEK,

          FNETTOPLAM.T
       as FATURA_NETTOPLAM,

          dbo.fn_IndirimTutar( NETTOPLAM.T,
                               FATALTINDORAN.T,
                               0
                             )
       as FATALTIND,

          NTOPLAM.T
       as NTOPLAM,

          OTVMATRAH.T
       as OTVMATRAH,

       FI.OTVORAN,

          OTVTUTAR.T
       as OTVTUTAR,

       FI.OIVORAN,

          OIVTUTAR.T
       as OIVTUTAR,

       FI.VERGIKOD,

          V.ADI
       as VERGIADI,

          FI.KDVDAHILMI
       as KDVDAHILMI,

          KDVMATRAH.T
       as KDVMATRAH,

          NKDVMATRAH.T
       as NKDVMATRAH,

          FI.KDV
       as KDVORAN,

          KDVTUTARI.T
       as KDVTUTAR,

          NKDVTUTAR.T
       as NKDVTUTAR,

            KDVMATRAH.T
          + KDVTUTARI.T
       as KDVLITUTAR,

            NKDVMATRAH.T
          + NKDVTUTAR.T
       as NKDVLITUTAR,

       FI.KDVSIZLIKKODU,
       FI.KDVACIKLAMA,

       FI.TEVKANUNKODU,
       FI.TEVKANUNACIKLAMA,

          FI.KDV2
       as KDVORAN2,

          FI.KDVTUTARI2
       as KDVTUTAR2,

          FI.TEVORAN
       as TEVKIFATKESIR,

            dbo.fn_KesirBolunen( FI.TEVORAN )
          / dbo.fn_KesirBolen( FI.TEVORAN )
       as TEV_ORAN,

          TEV_TUTAR.T
       as TEV_TUTAR,

          NTEV_TUTAR.T
       as NTEV_TUTAR,

            NKDVTUTAR.T
          - NTEV_TUTAR.T
       as ODENECEKKDV

  from FATISL
    as FI left outer join FATURA
                       as F
                       on F.FATID = FI.FATID

          left outer join STOK
                       as S
                       on S.STOKID = FI.STOKID

          left outer join TALYASETUP.dbo.VERGIKODLARI
                       as V
                       on V.KODU = FI.VERGIKOD

          cross apply ( select    F.FARKFATURASI
                               as T
                      )
                   as FARKFATURASI

          cross apply ( select    Cast( 0 as bit )
                               as T
                      )
                   as OZET

          cross apply ( select    Cast( 0 as bit )
                               as T
                      )
                   as OZETISL

          cross apply ( select    IsNull( F.DOVIZLI,
                                          0
                                        )
                               as T
                      )
                   as DOVIZLI
                          
          cross apply ( select    IsNull( NullIf( F.KUR,
                                                  0
                                                ),
                                          1
                                        )
                               as T
                      )
                   as KUR
                                  
          cross apply ( select    case
                                    when DOVIZLI.T = 1
                                    then cast( ( F.TINDEK/KUR.T ) as Decimal( 18, 2 ) )

                                    else F.TINDEK
                                  end
                               as T
                       )
                    as FTINDEK

           cross apply ( select   case
                                    when DOVIZLI.T = 1
                                    then cast( ( F.NETTOPLAM/KUR.T ) as Decimal( 18, 2 ) )

                                    else F.NETTOPLAM
                                  end
                               as T
                       )
                    as FNETTOPLAM

          cross apply ( select    case
                                    when LTrim( RTrim ( IsNull( S.ADI,
                                                                ''
                                                              )
                                                      )
                                              ) = ''

                                      or IsNull( FI.STOKID,
                                                 -1
                                               ) < 0

                                      or S.EK1 = 'Bilet'

                                      or S.EK1 = '[ACIKLAMA]'

                                      or (     S.EK1 = 'Fark'
                                           and FARKFATURASI.T = 1
                                         )
                                    then FI.ACIKLAMA

                                    else S.ADI
                                  end
                               as T
                      )
                   as ACIKLAMA

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                   then dbo.fn_VergiMatrahi( case 
                                                               when DOVIZLI.T = 1
                                                               then FI.KDVLIFIYAT / KUR.T
                                                               
                                                               else FI.KDVLIFIYAT
                                                             end,
                                                             FI.KDV
                                                           )

                                   else case 
                                          when DOVIZLI.T = 1
                                          then FI.FBFIYAT / KUR.T
                                          
                                          else FI.FBFIYAT
                                        end
                                 end
                              as T
                      )
                   as FBFIYAT

          cross apply ( select   Cast ( ( case
                                            when KDVDAHILMI = 1
                                            then case 
                                                   when DOVIZLI.T = 1
                                                   then dbo.fn_VergiliTutar( FBFIYAT.T,
                                                                             FI.KDV
                                                                           ) 
                                                   
                                                   else FI.KDVLIFIYAT
                                                 end
                                                 * FI.FADET
                                          
                                            else dbo.fn_VergiliTutar( case 
                                                                        when DOVIZLI.T = 1
                                                                        then cast( ( FBFIYAT.T * FI.FADET ) as Decimal( 18, 2 ) )
                                          
                                                                        else FI.ARATOPLAM
                                                                      end,
                                                                      FI.KDV
                                                                    )
                                          end
                                        ) as Decimal( 18, 2 )
                                      )
                              as T
                      )
                   as KDVLIARATOPLAM

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                   then cast( ( dbo.fn_VergiMatrahi( KDVLIARATOPLAM.T,
                                                                     FI.KDV
                                                                   )
                                              ) as Decimal( 18, 2 )
                                            )

                                   else  case 
                                           when DOVIZLI.T = 1
                                           then cast( ( FBFIYAT.T * FI.FADET ) as Decimal( 18, 2 ) )

                                           else FI.ARATOPLAM
                                         end
                                 end
                              as T
                      )
                   as ARATOPLAM

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                   then   ARATOPLAM.T
                                        - dbo.fn_VergiMatrahi( dbo.fn_IndirimliFiyat3lu( KDVLIARATOPLAM.T,
                                                                                         FI.SATIRIND,
                                                                                         0,
                                                                                         FI.SATIRIND2,
                                                                                         0,
                                                                                         0,
                                                                                         SATIRINDT
                                                                                       ),
                                                               FI.KDV
                                                             )

                                   else case 
                                           when DOVIZLI.T = 1
                                           then dbo.fn_IndirimTutar3lu( ARATOPLAM.T,
                                                                        cast( ( FI.SATIRIND / KUR.T ) as Decimal( 18, 2 ) ),
                                                                        0,
                                                                        cast( ( FI.SATIRIND2 / KUR.T ) as Decimal( 18, 2 ) ),
                                                                        0,
                                                                        0,
                                                                        cast( ( FI.SATIRINDT / KUR.T ) as Decimal( 18, 2 ) )
                                                                      )

                                           else FI.INDTOPLAM
                                         end
                                 end
                              as T
                      )
                   as INDTOPLAM

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                     or DOVIZLI.T  = 1
                                   then   ARATOPLAM.T
                                        - INDTOPLAM.T

                                   else FI.NETTOPLAM
                                 end
                              as T
                      )
                   as NETTOPLAM

           cross apply ( select   case
                                   when KDVDAHILMI = 1
                                   then dbo.fn_IndirimTutar3lu( KDVLIARATOPLAM.T,
                                                                FI.SATIRIND,
                                                                0,
                                                                FI.SATIRIND2,
                                                                0,
                                                                0,
                                                                SATIRINDT
                                                              )

                                   else   dbo.fn_VergiliTutar( ARATOPLAM.T,
                                                               FI.KDV
                                                             )
                                        - dbo.fn_VergiliTutar( NETTOPLAM.T,
                                                               FI.KDV
                                                             )
                                 end
                              as T
                      )
                   as KDVLIINDTOPLAM

          cross apply ( select     KDVLIARATOPLAM.T
                                 - KDVLIINDTOPLAM.T

                              as T
                      )
                   as KDVLINETTOPLAM

          cross apply ( select      dbo.fn_Oran( - FTINDEK.T,
                                                 FNETTOPLAM.T
                                               )
                                  * 100
                               as T
                      )
                   as FATALTINDORAN

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                   then dbo.fn_VergiMatrahi( dbo.fn_IndirimliFiyat( KDVLINETTOPLAM.T,
                                                                                    FATALTINDORAN.T,
                                                                                    0
                                                                                  ),
                                                             FI.KDV
                                                           )

                                   else dbo.fn_IndirimliFiyat( NETTOPLAM.T,
                                                               FATALTINDORAN.T,
                                                               0
                                                             )
                                 end
                              as T
                      )
                   as NTOPLAM

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                   then dbo.fn_IndirimliFiyat( KDVLINETTOPLAM.T,
                                                               FATALTINDORAN.T,
                                                               0
                                                             )

                                   else dbo.fn_VergiliTutar( NTOPLAM.T,
                                                             FI.KDV
                                                           )
                                 end
                              as T
                      )
                   as KDVLINTOPLAM

          cross apply ( select   case
                                   when DOVIZLI.T  = 1
                                   then cast( ( IsNull( FI.OTVMATRAH,
                                                        0
                                                      ) / KUR.T 
                                              ) as Decimal( 18, 2 ) 
                                            )

                                   else IsNull( FI.OTVMATRAH,
                                                0
                                              )
                                 end
                                 
                              as T
                      )
                   as OTVMATRAH

          cross apply ( select   case
                                   when DOVIZLI.T  = 1
                                   then cast( ( IsNull( OTVMATRAH.T * FI.OTVORAN,
                                                        0
                                                      )
                                              ) as Decimal( 18, 2 ) 
                                            )

                                   else IsNull( FI.OTVTUTAR,
                                                0
                                              )
                                 end
                                 
                              as T
                      )
                   as OTVTUTAR

          cross apply ( select   case
                                   when DOVIZLI.T  = 1
                                   then cast( ( IsNull( NETTOPLAM.T * FI.OIVORAN,
                                                        0
                                                      )
                                              ) as Decimal( 18, 2 ) 
                                            )

                                   else IsNull( FI.OIVTUTAR,
                                                0
                                              )
                                 end
                                 
                              as T
                      )
                   as OIVTUTAR

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                     or DOVIZLI.T  = 1
                                   then   NETTOPLAM.T
                                        + OTVTUTAR.T

                                   else FI.KDVMATRAH
                                 end
                              as T
                      )
                   as KDVMATRAH

          cross apply ( select      NTOPLAM.T
                                  + OTVTUTAR.T
                               as T
                      )
                   as NKDVMATRAH

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                     or DOVIZLI.T  = 1
                                   then   (   KDVLINETTOPLAM.T
                                            + OTVTUTAR.T
                                          )
                                        - KDVMATRAH.T

                                   else FI.KDVTUTARI
                                 end
                              as T
                      )
                   as KDVTUTARI

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                   then   (   KDVLINTOPLAM.T
                                            + FI.OTVMATRAH
                                          )
                                        - NKDVMATRAH.T

                                   else dbo.fn_VergiTutar( NKDVMATRAH.T,
                                                           FI.KDV
                                                         )
                                 end
                              as T
                      )
                   as NKDVTUTAR

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                     or DOVIZLI.T  = 1
                                   then dbo.fn_TevkifatTutar( KDVTUTARI.T,
                                                              FI.TEVORAN
                                                            )

                                   else FI.TEVKIFATTUTARI
                                 end
                              as T
                      )
                   as TEV_TUTAR

          cross apply ( select   case
                                   when KDVDAHILMI = 1
                                     or DOVIZLI.T  = 1
                                   then dbo.fn_TevkifatTutar( NKDVTUTAR.T,
                                                              FI.TEVORAN
                                                            )

                                   else FI.TEVKIFATTUTARI
                                 end
                              as T
                      )
                   as NTEV_TUTAR



 where 1 = 1

   and OZETISL.T = OZET.T
   and IsNull( FI.SILINDI,
               0
             ) = 0
GO


