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

CREATE view QMUH_EFATURA  
as   
select F.FATID,  
       F.MFATID,  
       F.TARIH,  
       F.SAAT,  
       F.VADE,  
       F.SUBEID,  
       F.FISTURID,  
       F.FISDURUMID,  
       F.EFATURADURUM,  
       F.SIPARISNO,  
       F.SIPARISTARIHI,  
       F.IRSALIYE,  
       F.IRSALIYENO,  
       F.IRSALIYETARIHI,  
       F.IRSALIYESEVKADRES,  
       F.IRSALIYESEVKILCE,  
       F.IRSALIYESEVKSEHIR,  
       F.IRSALIYESEVKPOSTAKODU,  
  
          IsNull( V.ISO2,  
                  'TR'  
                )  
       as IRSALIYESEVKULKEKODU,  
  
          IsNull( V.ADI,  
                  'Türkiye'  
                )  
       as IRSALIYESEVKULKEADI,  
  
       F.FIILISEVKTARIH,  
       F.FIILISEVKSAAT,  
       F.SEVKPLAKA,  
       F.KARGONO,  
       F.EFATURAINDYOK,  
       F.FATNO,  
       F.EFATNO,  
       F.UUID,  
       F.EFATURA_ENTREFNO,  
       F.EFATURAZARF,  
  
          IsNull( F.EDEVLETDURUM,  
                  0  
                )  
       AS EDEVLETDURUM,  
  
          IsNull( F.EFATTURU,  
                  0  
                )  
       as EFATTURU,  
  
       F.EDEVLET_ILETIMYONTEMI,  
  
          Cast( 0 as bit )  
       as OZET,  
  
          DOVIZLI.T  
       as DOVIZLI,  
  
          IsNull( F.DOVIZ,  
                  'TL'  
                )  
       as DOVIZ,  
  
          KUR.T  
       as KUR,  
  
          F.FISHESAPID  
       as CARIID,  
  
       F.UNVANI,  
       F.VD,  
       F.VDNO,  
       F.EFATURA_PKUTUSU,  
  
          IsNull( LTrim( RTrim( F.ADRES1 )  
                       ),  
                  ''  
                )  
       as ADRES1,  
  
          IsNull( LTrim( RTrim( F.ADRES2 )  
                       ),  
                  ''  
                )  
       as ADRES2,  
  
          IsNull( LTrim( RTrim( F.ADRES3 )  
                       ),  
                  ''  
                )  
       as ADRES3,  
  
          IsNull( LTrim( RTrim( F.ILCE )  
                       ),  
                  ''  
                )  
       as ILCE,  
  
          IsNull( LTrim( RTrim( F.SEHIR )  
                       ),  
                  ''  
                )  
       as SEHIR,  
  
          IsNull( U.ISO2,  
                  'TR'  
                )  
       as ULKEKODU,  
  
          IsNull( U.ADI,  
                  'Türkiye'  
                )  
       as ULKEADI,  
  
       POSTAKODU,  
  
          F.TEL  
       as TEL1,  
  
       F.FAX,  
       F.EMAIL,  
       F.WEB,  
         
          TOPLAM.T  
       as TOPLAM,  
  
          SIND.T  
       as SIND,  
  
          NETTOPLAM.T  
       as NETTOPLAM,  
  
       F.INDORAN1,  
  
          IND1.T  
       as IND1,  
  
       F.INDORAN2,  
         
          IND2.T  
       as IND2,  
  
       F.INDORAN3,  
         
          IND3.T  
       as IND3,  
  
       F.EKORAN1,  
         
          EK1.T  
       as EK1,  
  
       F.EKORAN2,  
         
          EK2.T  
       as EK2,  
  
       F.EKORAN3,  
         
          EK3.T  
       as EK3,  
  
          TINDEK.T  
       as TINDEK,  
  
          NTOPLAM.T  
       as NTOPLAM,  
  
          VERGITOPLAM.T  
       as VERGITOPLAM,  
  
          OTVMATRAH.T  
       as OTVMATRAH,  
  
          OTVOIV.T  
       as OTVOIV,  
  
          TEVKIFATTOPLAM.T  
       as TEVKIFATTOPLAM,  
  
          KDV.T  
       as KDV,  
  
       F.KDVSIZLIKKODU,  
       F.KDVACIKLAMA,  
  
          GTOPLAM.T  
       as GTOPLAM,  
  
          DGTOPLAM.T  
       as DGTOPLAM,  
  
       F.EFATURA_LOG,  
       F.EFATURA_SONGONTAR,  
       F.EFATURA_CEVAPNO,  
       F.EFATURA_CEVAPTARIHI,  
       F.EFATURA_CEVAP_UUID,  
       F.EFATURA_CEVAPREFNO,  
       F.EFATURA_CEVAPACK,  
       F.EFATURA_KABULREDNOTU,  
       F.IBAN,  
  
          case  
            when IsNull( F.ACIKLAMA,  
                         ''  
                       )  
               = ''  
            then IsNull( F.EFATURAACIKLAMA,  
                         ''  
                       )  
  
            when IsNull( F.EFATURAACIKLAMA,  
                         ''  
                       )  
               = ''  
            then F.ACIKLAMA  
  
            else   F.ACIKLAMA + Char(13) + Char(10)  
                 + F.EFATURAACIKLAMA  
          end  
       as ACIKLAMA,  
  
       F.EFATURAACIKLAMA,  
       F.EFATURAACIKLAMA2,  
  
            '|'  
          + '"'  
          + IsNull( F.ODEMEYONTEMI,  
                    'ACIK'  
                  )  
          + '"'  
          + ','  
          + '"'  
          + IsNull( F.ODEMEYONTEMI,  
                    'ACIK'  
                  )  
          + '"'  
          + ','  
          + Cast( F.GTOPLAM as nVarChar )  
          + ','  
          + '""'  
          + '|'  
       as ODEME,  
  
       F.ONLINE,  
       F.ONLINE_WEBADRES,  
       F.ONLINE_ODEMESEKLI,  
       F.ONLINE_ODEME_TARIHI,  
       F.ONLINE_GONDERIM_TARIHI,  
       F.ONLINE_GONDERIM_TASIYANTURU,  
       F.ONLINE_GONDERIM_TASIYANKIMLIKNO,  
       F.ONLINE_GONDERIM_TASIYANADI,  
       F.DIZAYNXSLT,
	   F.EVRAKREFNO,
	   F.MUKELLEFKODU,
	   F.MUKELLEFADI,
	   F.DONEMBASI,
	   F.DONEMSONU,
	   F.IADEEDILENFATURATARIHI,
	   F.IADEEDILENFATURANO 
    from FATURA  (NOLOCK)
    as F left outer join TALYASETUP..ULKE  
                      as U  
  
                      on (     U.ISO2 = F.ULKEKODU  
                           and Len( F.ULKEKODU ) = 2  
                         )  
  
                      or (     U.ISO3 = F.ULKEKODU  
                           and Len( F.ULKEKODU ) = 3  
                         )  
  
                      or (     U.GIBKODU = F.ULKEKODU  
                           and IsNumeric( F.ULKEKODU ) = 1  
                         )  
  
                      or (     Left( U.ADI,  
                                     5  
                                   ) = Left( F.ULKEKODU,  
                                             5  
                                           )  
                           and Len( F.ULKEKODU ) > 3  
                         )  
  
                      or (     U.ISO2 = 'TR'  
                           and NullIf( F.ULKEKODU,  
                                       ''  
                                     ) is null  
                         )  
  
         left outer join TALYASETUP..ULKE  
                      as V  
  
                      on (     V.ISO2 = F.IRSALIYESEVKULKE  
                           and Len( F.IRSALIYESEVKULKE ) = 2  
                         )  
  
                      or (     V.ISO3 = F.IRSALIYESEVKULKE  
                           and Len( F.IRSALIYESEVKULKE ) = 3  
                         )  
  
                      or (     V.GIBKODU = F.IRSALIYESEVKULKE  
                           and IsNumeric( F.IRSALIYESEVKULKE ) = 1  
                         )  
  
                      or (     Left( V.ADI,  
                                     5  
                                   ) = Left( F.IRSALIYESEVKULKE,  
                                             5  
                                           )  
                           and Len( F.IRSALIYESEVKULKE ) > 3  
                         )  
  
                      or (     V.ISO2 = 'TR'  
                           and NullIf( F.IRSALIYESEVKULKE,  
                                       ''  
                                     ) is null  
                         )  
  
          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    IsNull( Sum( KDV.ARATOPLAM ),  
                                          0  
                                        )  
                               as TOPLAM,  
  
                                   IsNull( Sum( INDTOPLAM ),  
                                          0  
                                        )  
                               as SIND,  
  
                                   IsNull( Sum( OTVMATRAH ),  
                                          0  
                                        )  
                               as OTVMATRAH,  
  
                                   IsNull( Sum( OTVOIV ),  
                                          0  
                                        )  
                               as OTVOIV,  
  
                                   IsNull( Sum( KDV ),  
                                          0  
                                        )  
                               as KDV,  
  
                                   IsNull( Sum( TEVKIFATTOPLAM ),  
                                          0  
                                        )  
                               as TEVKIFATTOPLAM  
  
                          from ( select IsNull( Sum( TEV.ARATOPLAM ),  
                                                0  
                                              )  
                                        as ARATOPLAM,  
  
                                        IsNull( Sum( TEV.INDTOPLAM ),  
                                                0  
                                              )  
                                        as INDTOPLAM,  
  
                                        IsNull( Sum( TEV.OTVMATRAH ),  
                                                0  
                                              )  
                                        as OTVMATRAH,  
  
                                        IsNull( Sum( TEV.OTVOIV ),  
                                                0  
                                              )  
                                        as OTVOIV,  
  
                                        IsNull( Sum( TEV.NKDVMATRAH ),  
                                                0  
                                              )  
                                        as NKDVMATRAH,  
  
                                        IsNull( Sum( dbo.fn_VergiTutar( TEV.NKDVMATRAH,  
                                                                        TEV.KDVORAN  
                                                                      )   
                                                   ),  
                                                0  
                                              )  
                                        as KDV,  
  
                                        IsNull( Sum( dbo.fn_TevkifatTutar( TEV.NKDVMATRAH,  
                                                                           TEV.TEV_ORAN  
                                                                         )  
                                                   ),  
                                                0  
                                              )  
                                        as TEVKIFATTOPLAM  
  
                                   from ( select IsNull( Sum( FI.ARATOPLAM ),  
                                                         0  
                                                       )  
                                                 as ARATOPLAM,  
  
                                                    IsNull( Sum( FI.INDTOPLAM ),  
                                                            0  
                                                          )  
                                                 as INDTOPLAM,  
  
                                                    IsNull( Sum( FI.OTVMATRAH ),  
                                                            0  
                                                          )  
                                                 as OTVMATRAH,  
  
                                                    IsNull( Sum( FI.OTVTUTAR + FI.OIVTUTAR ),  
                                                            0  
                                                          )  
                                                 as OTVOIV,  
  
                                                    IsNull( Sum( FI.NKDVMATRAH ),  
                                                            0  
                                                          )  
                                                 as NKDVMATRAH,  
  
                                                 FI.KDVORAN,  
                                                 FI.TEV_ORAN  
  
                                            from QMUH_EFATISL  
                                              as FI  
                                           where FI.FATID = F.FATID  
  
                                           group  
                                              by FI.KDVORAN,  
                                                 FI.TEV_ORAN  
                                        )  
                                     as TEV  
  
                                  group  
                                     by TEV.KDVORAN  
                              )  
                           as KDV  
                      )  
                   as T  
  
          cross apply ( select    case  
                                    when DOVIZLI.T = 1  
                                    then T.TOPLAM  
  
                                    else F.TOPLAM  
                                  end  
                               as T  
                      )  
                   as TOPLAM  
  
          cross apply ( select    case  
                                    when DOVIZLI.T = 1  
                                    then T.SIND  
  
                                    else F.SIND  
                                  end  
                               as T  
                      )  
                   as SIND  
  
          cross apply ( select    case  
                                    when DOVIZLI.T = 1  
                                    then T.TOPLAM - T.SIND  
  
                                    else F.NETTOPLAM  
                                  end  
                               as T  
                      )  
                   as NETTOPLAM  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then cast( ( F.IND1 / KUR.T ) as Decimal( 18, 2 ) )  
  
                                    else F.IND1  
                                  end  
                               as T  
                       )  
                    as IND1  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then cast( ( F.IND2 / KUR.T ) as Decimal( 18, 2 ) )  
  
                                    else F.IND2  
                                  end  
                               as T  
                       )  
                    as IND2  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then cast( ( F.IND3 / KUR.T ) as Decimal( 18, 2 ) )  
  
                                    else F.IND3  
                                  end  
                               as T  
                       )  
                    as IND3  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then cast( ( F.EK1 / KUR.T ) as Decimal( 18, 2 ) )  
  
                                    else F.EK1  
                                  end  
                               as T  
                       )  
                    as EK1  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then cast( ( F.EK2 / KUR.T ) as Decimal( 18, 2 ) )  
  
                                    else F.EK2  
                                  end  
                               as T  
                       )  
                    as EK2  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then cast( ( F.EK3 / KUR.T ) as Decimal( 18, 2 ) )  
  
                                    else F.EK3  
                                  end  
                               as T  
                       )  
                    as EK3  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then dbo.fn_IndirimTutar3lu( dbo.fn_IndirimTutar3lu( NETTOPLAM.T,  
                                                                                         F.INDORAN1,  
                                                                                         IND1.T,  
                                                                                         F.INDORAN2,  
                                                                                         IND2.T,  
                                                                                         F.INDORAN3,  
                                                                                         IND3.T  
                                                                                       ),  
                                                                 F.EKORAN1,  
                                                                 EK1.T,  
                                                                 F.EKORAN2,  
                                                                 EK2.T,  
                                                                 F.EKORAN3,  
                                                                 EK3.T  
                                                               )  
  
                                    else F.TINDEK  
                                  end  
                               as T  
                       )  
                    as TINDEK  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then NETTOPLAM.T - TINDEK.T  
  
                                    else F.NTOPLAM  
                                  end  
                               as T  
                       )  
                    as NTOPLAM  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then cast( ( F.VERGITOPLAM / KUR.T ) as Decimal( 18, 2 ) )  
  
                                    else F.VERGITOPLAM  
                                  end  
                               as T  
                       )  
                    as VERGITOPLAM  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then T.OTVMATRAH  
  
                                    else F.OTVMATRAH  
                                  end  
                               as T  
                       )  
                    as OTVMATRAH  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then T.OTVOIV  
  
                                    else F.OTVOIV  
                                  end  
                               as T  
                       )  
                    as OTVOIV  
         
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then T.TEVKIFATTOPLAM  
  
                                    else F.TEVKIFATTOPLAM  
                                  end  
                               as T  
                       )  
                    as TEVKIFATTOPLAM  
         
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then T.KDV - TEVKIFATTOPLAM.T  
  
                                    else F.KDV  
                                  end  
                               as T  
                       )  
                    as KDV  
  
           cross apply ( select   case  
                         when DOVIZLI.T = 1  
                                    then ( NTOPLAM.T + OTVOIV.T + KDV.T ) - IsNull( VERGITOPLAM.T, 0 )   
  
                                    else F.GTOPLAM  
                                  end  
                               as T  
                       )  
                    as GTOPLAM  
  
           cross apply ( select   case  
                                    when DOVIZLI.T = 1  
                                    then GTOPLAM.T  
  
                                    else F.DTOPLAM  
                                  end  
                               as T  
                       )  
                    as DGTOPLAM  
  
 where 1 = 1  