WITH CTE AS

(

 

SELECT     ROW_NUMBER() OVER(PARTITION BY dbo.TBLSIPATRA.FISNO,dbo.TBLSIPATRA.STRA_SIPKONT ORDER BY dbo.TBLSTHAR.STHAR_TARIH DESC) rownum,dbo.TBLSIPATRA.STOK_KODU, dbo.TBLSTSABIT.STOK_ADI, dbo.TBLSIPATRA.STHAR_GCMIK, dbo.TBLSIPATRA.FIRMA_DOVTUT, dbo.TBLSIPATRA.F_YEDEK3, 

                      dbo.TBLSIPATRA.STHAR_CARIKOD AS CARI_KODU, dbo.TBLSIPATRA.STHAR_FTIRSIP / 6 AS FTIRSIP, 

                      dbo.TBLSIPATRA.STHAR_GCMIK + dbo.TBLSIPATRA.F_YEDEK3 - dbo.TBLSIPATRA.FIRMA_DOVTUT AS BAKIYE, null AS FATIRS_NO

FROM         dbo.TBLSIPATRA INNER JOIN

                      dbo.TBLSTSABIT ON dbo.TBLSIPATRA.STOK_KODU = dbo.TBLSTSABIT.STOK_KODU LEFT OUTER JOIN

                      dbo.TBLSTHAR ON dbo.TBLSIPATRA.FISNO = dbo.TBLSTHAR.STHAR_SIPNUM AND dbo.TBLSIPATRA.STRA_SIPKONT = dbo.TBLSTHAR.STRA_SIPKONT

WHERE     (dbo.TBLSIPATRA.STHAR_HTUR = 'H') AND (dbo.TBLSIPATRA.STHAR_FTIRSIP = 6) AND 

                      (dbo.TBLSIPATRA.STHAR_GCMIK + dbo.TBLSIPATRA.F_YEDEK3 - dbo.TBLSIPATRA.FIRMA_DOVTUT > 0)

               )

SELECT *

FROM CTE

WHERE rownum=1

SELECT 1 AS G,F.FTIRSIP,F.FATIRS_NO,F.CARI_KODU,F.TARIH,A.STOK_KODU,B.STOK_ADI,A.EKALAN,B.OLCU_BR1 BR,STHAR_GCMIK MIKTAR,STHAR_GCMIK2 MIKTAR2,

STHAR_KDV SAT_KDV,STHAR_BF,STHAR_NF,STHAR_BF*STHAR_GCMIK S_BRUT,STHAR_NF*STHAR_GCMIK S_NETTUT,A.STHAR_SIPNUM,F.BRUTTUTAR,F.SAT_ISKT,GEN_ISK1T,KDV TOPKDV,FAT_ALTM1,

ACIKLAMA,STHAR_SATISK*1000000 SATISK1,A.STHAR_SATISK2 SATISK2,STRA_SATISK3 SATISK3,STRA_SATISK4 SATISK4,A.DEPO_KODU,STHAR_DOVTIP DVZ,

STHAR_DOVFIAT DVZ_FIAT,STHAR_ODEGUN VGUN,E.ACIK1,E.ACIK2,E.ACIK3,E.ACIK4,E.ACIK5,E.ACIK6,E.ACIK7,E.ACIK8,E.ACIK9,E.ACIK10,

C.CARI_ISIM MUSTERI,C.CARI_ADRES ADRES1,C.CARI_ILCE ILCE1,C.CARI_IL IL1,C.POSTAKODU PKODU1,C.VERGI_DAIRESI VDAIRESI,COALESCE(TCKIMLIKNO,C.VERGI_NUMARASI) VERGITCK_NO,

C.FAX FAX1,C.CARI_TEL TEL1,A.PLASIYER_KODU PL_KOD,P.PLASIYER_ACIKLAMA,

STHAR_CARIKOD TESCKOD,C2.CARI_ISIM TESLIM_CARI,C2.CARI_ADRES ADRES2,C2.CARI_ILCE ILCE2,C2.CARI_IL IL2,C2.POSTAKODU PKODU2,C2.FAX FAX2,C2.CARI_TEL TEL2

FROM TBLSTHAR A (NOLOCK)

JOIN TBLSTSABIT B (NOLOCK) ON A.STOK_KODU=B.STOK_KODU

JOIN TBLCASABIT C (NOLOCK) ON A.STHAR_ACIKLAMA=C.CARI_KOD

JOIN TBLCASABIT C2 (NOLOCK) ON A.STHAR_CARIKOD=C2.CARI_KOD

JOIN TBLCASABITEK D (NOLOCK) ON C.CARI_KOD=D.CARI_KOD

JOIN TBLFATUIRS F (NOLOCK) ON A.FISNO=F.FATIRS_NO AND A.STHAR_ACIKLAMA=F.CARI_KODU AND F.FTIRSIP='3'

JOIN TBLFATUEK E (NOLOCK) ON A.FISNO=E.FATIRSNO AND A.STHAR_ACIKLAMA=E.CKOD AND E.FKOD='3'

LEFT JOIN TBLCARIPLASIYER P (NOLOCK) ON A.PLASIYER_KODU=P.PLASIYER_KODU

WHERE STHAR_FTIRSIP='3' AND STHAR_HTUR='H'

UNION ALL

--BEKLEYEN SIPARISLER

SELECT 2 G,X.FTIRSIP,X.FATIRS_NO,X.CARI_KODU,F.TARIH,A.STOK_KODU,STOK_ADI,A.EKALAN,B.OLCU_BR1 BR,STHAR_GCMIK SIPMIK,STHAR_GCMIK - FIRMA_DOVTUT BEKLEYEN,

STHAR_KDV SAT_KDV,STHAR_BF,STHAR_NF,STHAR_BF*(STHAR_GCMIK-FIRMA_DOVTUT) S_BRUT,STHAR_NF*(STHAR_GCMIK-FIRMA_DOVTUT) S_NETTUT,FISNO SIPARISNO,0 BRUTTUT,0 SAT_ISKT,0 GENISK1,0 TOPKDV,F.FAT_ALTM1,

F.ACIKLAMA,STHAR_SATISK*1000000 SIP_SISK1,STHAR_SATISK2 SIP_SISK2,STRA_SATISK3 SIP_SISK3,STRA_SATISK4 SIP_SISK4,A.DEPO_KODU,STHAR_DOVTIP DVZ,STHAR_DOVFIAT DVZ_FIAT,

STHAR_ODEGUN VGUN,E.ACIK1,E.ACIK2,E.ACIK3,E.ACIK4,E.ACIK5,E.ACIK6,E.ACIK7,E.ACIK8,E.ACIK9,E.ACIK10,C.CARI_ISIM MUSTERI,C.CARI_ADRES ADRES1,C.CARI_ILCE ILCE1,

C.CARI_IL IL1,C.POSTAKODU PKODU1,C.VERGI_DAIRESI VDAIRESI,COALESCE(TCKIMLIKNO,C.VERGI_NUMARASI) VERGITCK_NO,C.FAX FAX1,C.CARI_TEL TEL1,A.PLASIYER_KODU,P.PLASIYER_ACIKLAMA,

NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL

FROM TBLSIPATRA A (NOLOCK)

JOIN TBLSTSABIT B (NOLOCK) ON A.STOK_KODU=B.STOK_KODU

JOIN TBLCASABIT C (NOLOCK) ON A.STHAR_ACIKLAMA=C.CARI_KOD

JOIN TBLCASABITEK D (NOLOCK) ON C.CARI_KOD=D.CARI_KOD

JOIN TBLFATUEK E (NOLOCK) ON A.FISNO=E.FATIRSNO AND A.STHAR_ACIKLAMA=E.CKOD AND E.FKOD='6'

JOIN TBLSIPAMAS F (NOLOCK) ON A.FISNO=F.FATIRS_NO AND A.STHAR_ACIKLAMA=F.CARI_KODU AND F.FTIRSIP='6'

LEFT JOIN TBLCARIPLASIYER P (NOLOCK) ON A.PLASIYER_KODU=P.PLASIYER_KODU

CROSS JOIN TBLFATUIRS X (NOLOCK) 

WHERE STHAR_FTIRSIP='6' AND STHAR_GCMIK - FIRMA_DOVTUT > 0 AND STHAR_HTUR <> 'K' AND STHAR_ACIKLAMA IN (SELECT DISTINCT CARI_KODU FROM TBLFATUIRS WHERE FTIRSIP='3')

AND F.CARI_KODU=X.CARI_KODU AND X.FTIRSIP='3'