bir müşterim için %1, %8, %18 kdv oranları ve matrahlarını gösteren bir rapor yapmıştım. belki işinize yarar. fakat diğer fatura türleri için sorguda değişiklik yapmanız gerekecek.
SELECT
FATURA.FICHENO [FATURA NUMARASI],
FATURA.DATE_ [FATURA TARIHI],
CARI.CODE [CARI HESAP KODU],
CARI.DEFINITION_ [CARI HESAP UNVANI],
'%1 KDV MATRAHI '= CASE WHEN (KDV1MATRAH.TUTAR IS NULL) THEN '0' WHEN (KDV1MATRAH.TUTAR IS NOT NULL) THEN KDV1MATRAH.TUTAR END,
'%1 KDV TUTARI '= CASE WHEN (KDV1TUTAR.TUTAR IS NULL) THEN '0' WHEN (KDV1TUTAR.TUTAR IS NOT NULL) THEN KDV1TUTAR.TUTAR END,
'%8 KDV MATRAHI '= CASE WHEN (KDV8MATRAH.TUTAR IS NULL) THEN '0' WHEN (KDV8MATRAH.TUTAR IS NOT NULL) THEN KDV8MATRAH.TUTAR END,
'%8 KDV TUTARI '= CASE WHEN (KDV8TUTAR.TUTAR IS NULL) THEN '0' WHEN (KDV8TUTAR.TUTAR IS NOT NULL) THEN KDV8TUTAR.TUTAR END,
'%18 KDV MATRAHI'= CASE WHEN (KDV18MATRAH.TUTAR IS NULL) THEN '0' WHEN (KDV18MATRAH.TUTAR IS NOT NULL) THEN KDV18MATRAH.TUTAR END,
'%18 KDV TUTARI '= CASE WHEN (KDV18TUTAR.TUTAR IS NULL) THEN '0' WHEN (KDV18TUTAR.TUTAR IS NOT NULL) THEN KDV18TUTAR.TUTAR END,
FATURA.TOTALDISCOUNTS [TOPLAM INDIRIM],
FATURA.GROSSTOTAL [KDV HARIC TOPLAM],
FATURA.NETTOTAL [GENEL TOPLAM]
FROM
{oj
LG_001_01_INVOICE AS FATURA
LEFT JOIN (SELECT SUM(VATAMNT) TUTAR,INVOICEREF FROM LG_001_01_STLINE WHERE VAT=18 GROUP BY INVOICEREF)
KDV18TUTAR ON KDV18TUTAR.INVOICEREF=STR(FATURA.LOGICALREF)
LEFT JOIN (SELECT SUM(VATMATRAH) TUTAR,INVOICEREF FROM LG_001_01_STLINE WHERE VAT=18 GROUP BY INVOICEREF)
KDV18MATRAH ON KDV18MATRAH.INVOICEREF=STR(FATURA.LOGICALREF)
LEFT JOIN (SELECT SUM(VATAMNT) TUTAR,INVOICEREF FROM LG_001_01_STLINE WHERE VAT=8 GROUP BY INVOICEREF)
KDV8TUTAR ON KDV8TUTAR.INVOICEREF=STR(FATURA.LOGICALREF)
LEFT JOIN (SELECT SUM(VATMATRAH) TUTAR,INVOICEREF FROM LG_001_01_STLINE WHERE VAT=8 GROUP BY INVOICEREF)
KDV8MATRAH ON KDV8MATRAH.INVOICEREF=STR(FATURA.LOGICALREF)
LEFT JOIN (SELECT SUM(VATAMNT) TUTAR,INVOICEREF FROM LG_001_01_STLINE WHERE VAT=1 GROUP BY INVOICEREF)
KDV1TUTAR ON KDV1TUTAR.INVOICEREF=STR(FATURA.LOGICALREF)
LEFT JOIN (SELECT SUM(VATMATRAH) TUTAR,INVOICEREF FROM LG_001_01_STLINE WHERE VAT=1 GROUP BY INVOICEREF)
KDV1MATRAH ON KDV1MATRAH.INVOICEREF=STR(FATURA.LOGICALREF)
LEFT JOIN (SELECT * FROM LG_001_CLCARD)
CARI ON FATURA.CLIENTREF=STR(CARI.LOGICALREF)
}
WHERE
FATURA.CANCELLED=0
AND
FATURA.TRCODE IN (7,8)
AND
FATURA.CLIENTREF<>''
ORDER BY FATURA.FICHENO
