SQL Sorgu Tevkifatlı Faturalar Lİstesi

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],

CASE
WHEN ST.LINETYPE = 0 THEN IT.CODE
WHEN ST.LINETYPE = 4 THEN SRVCARD.CODE
WHEN ST.LINETYPE = 8 THEN IT.CODE
ELSE ' '
END AS [STOK KODU],

CASE
WHEN ST.LINETYPE = 0 THEN IT.NAME
WHEN ST.LINETYPE = 4 THEN SRVCARD.DEFINITION_
WHEN ST.LINETYPE = 8 THEN IT.NAME
ELSE ' '
END AS [STOK ADI],

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],

CASE
WHEN ISNULL(ST.DEDUCTIONPART1, 0) > 0
OR ISNULL(ST.DEDUCTIONPART2, 0) > 0
THEN CONVERT(VARCHAR, ST.DEDUCTIONPART1) + ' / ' + CONVERT(VARCHAR, ST.DEDUCTIONPART2)
ELSE ''
END AS [TEVKİFAT ORANI],

ST.DEDUCTCODE AS [TEVKİFAT KODU],

((ST.VATMATRAH * ST.VAT) / 100) -
(
CASE
WHEN ISNULL(ST.DEDUCTIONPART1, 0) > 0
OR ISNULL(ST.DEDUCTIONPART2, 0) > 0
THEN ST.VATAMNT
ELSE 0
END
) AS [TEVKİFAT],

CASE
WHEN ISNULL(ST.DEDUCTIONPART1, 0) > 0
OR ISNULL(ST.DEDUCTIONPART2, 0) > 0
THEN ST.VATAMNT
ELSE 0
END AS [TEVKİFAT 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Ü],

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İ],

EMUHACC.CODE AS [MUHASEBE HESABI KODU],
EMUHACC.DEFINITION_ AS [MUHASEBE HESABI ADI]

FROM LG_051_01_STLINE AS ST
LEFT OUTER JOIN LG_051_ITEMS AS IT
ON ST.STOCKREF = IT.LOGICALREF
LEFT OUTER JOIN LG_051_CLCARD AS CL
ON ST.CLIENTREF = CL.LOGICALREF
LEFT OUTER JOIN LG_051_01_INVOICE AS INV
ON ST.INVOICEREF = INV.LOGICALREF
LEFT OUTER JOIN LG_051_EMCENTER AS EMCENTER
ON ST.CENTERREF = EMCENTER.LOGICALREF
LEFT OUTER JOIN LG_051_EMUHACC AS EMUHACC
ON INV.ACCOUNTREF = EMUHACC.LOGICALREF
LEFT OUTER JOIN LG_051_SRVCARD AS 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.TRCODE = 13
AND INV.NETTOTAL <> 0
AND INV.CLIENTREF <> 0
AND INV.CANCELLED = 0
AND INV.DECPRDIFF = 0
)
OR
(
INV.TRCODE = 14
AND INV.NETTOTAL <> 0
AND INV.CLIENTREF <> 0
AND INV.CANCELLED = 0
AND INV.DECPRDIFF = 1
)
)

-- SADECE TEVKİFATLI SATIRLAR
AND
(
ISNULL(ST.DEDUCTIONPART1, 0) > 0
OR ISNULL(ST.DEDUCTIONPART2, 0) > 0
)

ORDER BY
INV.DATE_,
INV.FICHENO;