Merhaba
SQL sorgusu kullanarak rapor hazırlıyorum raporda faturaların açık/kapalı durumunu yapabiliyorum.Fakat fatura kapalıysa nakit karşılığımı, kredi karşılığımı veya çek karşılığımı kapandığını çözemedim. Paytrans tablosunda TRCODE VE MODULENR arasındaki ilişkide kaldım.
SELECT dbo.LG_007_CLCARD.CODE AS Ch_Kodu, dbo.LG_007_CLCARD.DEFINITION_ AS [Cari Ünvan], dbo.LG_007_01_INVOICE.TRCODE AS [Fatura türü], dbo.LG_007_01_INVOICE.FICHENO AS [Fatura No],
dbo.LG_007_01_INVOICE.DATE_ AS TARIH, dbo.LG_007_ITEMS.STGRPCODE AS [stok Grup kodu], dbo.LG_007_ITEMS.PRODUCERCODE AS [stok üretici Kodu],
dbo.LG_007_ITEMS.SPECODE AS [stok Özel Kod1], dbo.LG_007_ITEMS.CYPHCODE AS [Stok Yetki Kodu], dbo.LG_007_ITEMS.SPECODE2 AS [Stok Özel kod2],
dbo.LG_007_ITEMS.SPECODE3 AS [Stok Özel kod 3], dbo.LG_007_ITEMS.SPECODE4 AS [Stok Özel Kod 4], dbo.LG_007_ITEMS.SPECODE5 AS [Stok Özel kod 5],
dbo.LG_007_ITEMS.CODE AS [Stok Kodu], dbo.LG_007_ITEMS.NAME AS [Stok Adý], dbo.LG_007_01_STLINE.AMOUNT AS Miktar, dbo.LG_007_UNITSETL.CODE AS Birim,
dbo.LG_007_01_STLINE.PRICE AS [Birim fiyat], dbo.LG_007_01_STLINE.TOTAL AS [_Satýr tutarý], dbo.LG_007_01_STLINE.VAT AS [Kdv Oraný], dbo.LG_007_01_STLINE.VATAMNT AS [_Kdv tutarý],
dbo.LG_007_01_STLINE.VATMATRAH AS [_Kdv Matrahý], UNITY.dbo.L_CAPIWHOUSE.NR AS [Ambar No], UNITY.dbo.L_CAPIWHOUSE.NAME AS [Ambar Adý], UNITY.dbo.L_CAPIDIV.NR AS [Ýþyeri No],
UNITY.dbo.L_CAPIDIV.NAME AS [Ýþyeri Adý], dbo.LG_007_SHIPINFO.CODE AS [sevkiyat Adresi No], UNITY.dbo.LG_SLSMAN.CODE AS St_Kodu, UNITY.dbo.LG_SLSMAN.DEFINITION_ AS [Plasiyer Adý],
dbo.LG_007_01_STLINE.LINETYPE AS [Satýr Türü], CASE WHEN CROSSREF <> 0 THEN 'KAPALI' ELSE 'ACIK' END AS ACIK_KAPALI,
CASE WHEN dbo.LG_007_01_PAYTRANS.CROSSREF <> 0 THEN dbo.LG_007_01_PAYTRANS.PROCDATE ELSE NULL END AS KARSI_HAREKET_TARIHI,
CASE WHEN dbo.LG_007_01_PAYTRANS.CROSSREF <> 0 THEN CASE WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 1 THEN 'Mal Alım Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 2 THEN 'Perakende Satış iade Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 3 THEN 'Toptan Satış iade Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 4 THEN 'Alınan Hizmet Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 5 THEN 'Alınan Proforma Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 6 THEN 'Alım iade Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 7 THEN 'Perakende Satış Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 8 THEN 'Toptan Satış Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 9 THEN 'Verilen Hizmet Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 10 THEN 'Verilen Proforma Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 13 THEN 'Alınan Fiyat Farkı Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 14 THEN 'Verilen Fiyat Farkı Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 41 THEN 'Verilen Vade Farkı Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 4 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 42 THEN 'Alınan Vade Farkı Faturası' WHEN dbo.LG_007_01_PAYTRANS.MODULENR IN (5, 61, 62, 63, 64) AND
dbo.LG_007_01_PAYTRANS.TRCODE = 1 THEN 'Nakit Tahsilat' WHEN dbo.LG_007_01_PAYTRANS.MODULENR IN (5, 61, 62, 63, 64) AND
dbo.LG_007_01_PAYTRANS.TRCODE = 2 THEN 'Nakit Ödeme' WHEN dbo.LG_007_01_PAYTRANS.MODULENR IN (5, 61, 62, 63, 64) AND
dbo.LG_007_01_PAYTRANS.TRCODE = 3 THEN 'Borç Dekontu' WHEN dbo.LG_007_01_PAYTRANS.MODULENR IN (5, 61, 62, 63, 64) AND
dbo.LG_007_01_PAYTRANS.TRCODE = 4 THEN 'Alacak Dekontu' WHEN dbo.LG_007_01_PAYTRANS.MODULENR IN (5, 61, 62, 63, 64) AND
dbo.LG_007_01_PAYTRANS.TRCODE = 5 THEN 'Virman işlemi' WHEN dbo.LG_007_01_PAYTRANS.MODULENR IN (5, 61, 62, 63, 64) AND
dbo.LG_007_01_PAYTRANS.TRCODE = 14 THEN 'Açılış işlemi' WHEN dbo.LG_007_01_PAYTRANS.MODULENR IN (5, 61, 62, 63, 64) AND
dbo.LG_007_01_PAYTRANS.TRCODE = 70 THEN 'Kredi Kartı' WHEN dbo.LG_007_01_PAYTRANS.MODULENR IN (5, 61, 62, 63, 64) AND
dbo.LG_007_01_PAYTRANS.TRCODE = 71 THEN 'Kredi Kartı İade' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 6 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 1 THEN 'Çek Girişi' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 6 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 2 THEN 'Senet Girişi' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 6 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 3 THEN 'Çek Çıkış (Cari Hesaba)' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 6 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 4 THEN 'Senet Çıkış (Cari Hesaba)' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 7 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 3 THEN 'Gelen Havaleler' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 7 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 4 THEN 'Gonderilen Havaleler' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 10 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 1 THEN 'Nakit Tahsilat' WHEN dbo.LG_007_01_PAYTRANS.MODULENR = 10 AND
dbo.LG_007_01_PAYTRANS.TRCODE = 2 THEN 'Nakit Odeme' END END AS KARSI_HAREKET_TURU,
CASE WHEN dbo.LG_007_01_PAYTRANS.SIGN = 0 THEN 'BORC' ELSE 'ALACAK' END AS [BORC ALACAK]
FROM dbo.LG_007_01_STLINE INNER JOIN
dbo.LG_007_UNITSETL INNER JOIN
dbo.LG_007_UNITSETF ON dbo.LG_007_UNITSETL.UNITSETREF = dbo.LG_007_UNITSETF.LOGICALREF ON dbo.LG_007_01_STLINE.UOMREF = dbo.LG_007_UNITSETL.LOGICALREF INNER JOIN
dbo.LG_007_ITEMS ON dbo.LG_007_01_STLINE.STOCKREF = dbo.LG_007_ITEMS.LOGICALREF INNER JOIN
dbo.LG_007_01_STFICHE ON dbo.LG_007_01_STLINE.STFICHEREF = dbo.LG_007_01_STFICHE.LOGICALREF INNER JOIN
dbo.LG_007_CLCARD INNER JOIN
dbo.LG_007_01_INVOICE ON dbo.LG_007_CLCARD.LOGICALREF = dbo.LG_007_01_INVOICE.CLIENTREF ON
dbo.LG_007_01_STFICHE.INVOICEREF = dbo.LG_007_01_INVOICE.LOGICALREF INNER JOIN
UNITY.dbo.L_CAPIWHOUSE ON dbo.LG_007_01_INVOICE.SOURCEINDEX = UNITY.dbo.L_CAPIWHOUSE.NR INNER JOIN
UNITY.dbo.L_CAPIDIV ON dbo.LG_007_01_INVOICE.BRANCH = UNITY.dbo.L_CAPIDIV.NR INNER JOIN
dbo.LG_007_01_PAYTRANS ON dbo.LG_007_01_STLINE.LOGICALREF = dbo.LG_007_01_PAYTRANS.LOGICALREF LEFT OUTER JOIN
UNITY.dbo.LG_SLSMAN ON dbo.LG_007_01_INVOICE.SALESMANREF = UNITY.dbo.LG_SLSMAN.LOGICALREF LEFT OUTER JOIN
dbo.LG_007_SHIPINFO ON dbo.LG_007_01_INVOICE.SHIPINFOREF = dbo.LG_007_SHIPINFO.LOGICALREF
WHERE (dbo.LG_007_01_INVOICE.DATE_ > '2014-01-01') AND (dbo.LG_007_01_PAYTRANS.CROSSREF <> 0) AND (dbo.LG_007_01_PAYTRANS.SIGN = 0) OR
(dbo.LG_007_01_PAYTRANS.CROSSREF = 0)