SQL Sorgu Logo SQL den Alış-Satış Fatura bilgilerini çekme

Aşağıdaki kod ile Alış-Satış Fatura bilgilerini çekebilirsiniz.



SELECT
YEAR(INV.DATE_) AS 'YIL',
MONTH(INV.DATE_) AS 'AY',
INV.DATE_ AS 'FATURA TARİHİ',
INV.FICHENO AS 'FATURA NO',
CL.CODE AS 'CARİ KODU',
CL.DEFINITION_ AS 'CARİ ÜNVAN',
EMCENTER.CODE AS 'MASRAF MERKEZİ KODU',
EMCENTER.DEFINITION_ AS 'MASRAF MERKEZİ ADI',
CASE ST.LINETYPE WHEN 0 THEN IT.CODE WHEN 4 THEN SRVCARD.CODE WHEN 8 THEN IT.CODE ELSE ' ' END AS 'STOK KODU',
CASE ST.LINETYPE WHEN 0 THEN IT.NAME WHEN 4 THEN SRVCARD.DEFINITION_ WHEN 8 THEN IT.NAME ELSE ' ' END AS 'STOK ADI',
CASE ST.LINETYPE WHEN 0 THEN IT.NAME3 WHEN 4 THEN SRVCARD.DEFINITION2 WHEN 8 THEN IT.NAME3 ELSE ' ' END AS 'STOK ADI_2',
ST.AMOUNT AS 'MİKTAR',
ST.PRICE AS 'BİRİM FİYAT',
ST.VAT AS 'SATIR KDV ORANI',
ST.VATMATRAH AS 'MATRAH',
((ST.VATMATRAH*ST.VAT)/100) AS 'SATIR KDV TUTARI',
(ST.VATMATRAH+((ST.VATMATRAH*ST.VAT)/100)) AS 'SATIR GENEL TOPLAM',
CASE INV.TRCODE WHEN 1 THEN 'Satınalma Faturası' WHEN 8 THEN 'Satış Faturası' WHEN 4 THEN 'Alınan Hizmet Faturası'
WHEN 6 THEN 'Satınalma İade Faturası' WHEN 13 THEN 'Satınalma Fiyat Farkı Faturası' WHEN 3 THEN 'Satış İade Faturası'
WHEN 9 THEN 'Verilen Hizmet Faturası' WHEN 14 THEN 'Satış Fiyat Farkı' ELSE ' ' END AS 'FATURA TÜRÜ',
EMUHACC.CODE AS 'MUHASEBE HESABI KODU',
EMUHACC.DEFINITION_ AS 'MUHASEBE HESABI ADI',
CL.TAXNR AS 'VERGİ NUMARASI',
CL.TCKNO AS 'TC NUMARASI',
CL.TAXOFFICE AS 'VERGİ DAİRESİ',
CL.ADDR1 AS 'ADRES',
CL.TELNRS1 AS 'TELEFON NUMARASI',
CL.EMAILADDR AS 'EMAIL ADRESİ',
INV.DEPARTMENT AS 'BOLUM'

FROM LG_720_01_STLINE AS ST
LEFT OUTER JOIN LG_720_ITEMS as IT ON ST.STOCKREF=IT.LOGICALREF
LEFT OUTER JOIN LG_720_CLCARD as CL ON ST.CLIENTREF=CL.LOGICALREF
LEFT OUTER JOIN LG_720_01_INVOICE as INV ON ST.INVOICEREF=INV.LOGICALREF
LEFT OUTER JOIN LG_720_EMCENTER EMCENTER ON ST.CENTERREF=EMCENTER.LOGICALREF
LEFT OUTER JOIN LG_720_EMUHACC EMUHACC ON INV.ACCOUNTREF=EMUHACC.LOGICALREF
LEFT OUTER JOIN LG_720_SRVCARD SRVCARD ON SRVCARD.LOGICALREF=ST.STOCKREF

WHERE ((INV.TRCODE IN (1, 2, 3, 4, 12,31,8,9,6)) AND (INV.NETTOTAL <> 0) AND (INV.CLIENTREF <> 0) AND (INV.CANCELLED = 0) OR
(INV.NETTOTAL <> 0) AND (INV.CLIENTREF <> 0) AND (INV.CANCELLED = 0) AND (INV.TRCODE = 13) AND (INV.DECPRDIFF = 0) OR
(INV.NETTOTAL <> 0) AND (INV.CLIENTREF <> 0) AND (INV.CANCELLED = 0) AND (INV.TRCODE = 14) AND (INV.DECPRDIFF = 1))
AND YEAR(INV.DATE_) ='2020'