SELECT LGMAIN.DATE_ AS Tarih, LGMAIN.TRANNO AS [Belge No], BANK.DEFINITION_ AS [Banka Adı], BANK.BRANCH AS [Banka Şubesi], BNACC.DEFINITION_ AS [Hesap Adı],
BNACC.ACCOUNTNO AS [Hesap No], CASE WHEN MODULENR = 7 AND TRCODE = 5 THEN 'Açılış İşlemi' WHEN MODULENR IN (7, 61) AND
TRCODE = 1 THEN 'Banka İşlem Fişi' WHEN MODULENR = 10 AND TRCODE = 1 THEN 'Yatırılan' WHEN MODULENR = 7 AND
TRCODE = 4 THEN 'Gönderilen Havale' WHEN MODULENR = 7 AND TRCODE = 2 THEN 'Virman İşlemi' WHEN MODULENR = 7 AND
TRCODE = 3 THEN 'Gelen Havale' WHEN MODULENR = 6 AND
TRCODE = 10 THEN 'Çek Çıkış (Banka Tahsil)' WHEN TRCODE = 11 THEN 'Senet Çıkış (Banka Tahsil)' WHEN TRCODE = 14 THEN 'Personel Borçlanması' WHEN TRCODE
= 15 THEN 'Personel Geri Ödemesi' WHEN TRCODE = 16 THEN 'Banka Alınan Hizmet Faturası' WHEN TRCODE = 17 THEN 'Banka Verilen Hizmet Faturası' END AS [İşlem Cinsi],
CASE WHEN LGMAIN.SIGN = 0 THEN LGMAIN.AMOUNT ELSE 0 END AS Borç, CASE WHEN LGMAIN.SIGN = 1 THEN LGMAIN.AMOUNT * - 1 ELSE 0 END AS Alacak,
(CASE WHEN LGMAIN.SIGN = 0 THEN LGMAIN.AMOUNT ELSE 0 END) - (CASE WHEN LGMAIN.SIGN = 1 THEN LGMAIN.AMOUNT ELSE 0 END) AS Kalan,
CASE WHEN TRANSTYPE = 1 THEN 'Cari Hesap' WHEN TRANSTYPE = 2 THEN 'Tahsil Senetleri' WHEN TRANSTYPE = 3 THEN 'Takas Çekleri' WHEN TRANSTYPE = 4 THEN
'Kesilen Çekler' WHEN TRANSTYPE = 5 THEN 'Teminat Senetleri' WHEN TRANSTYPE = 6 THEN 'Teminat Çekleri' WHEN TRANSTYPE = 7 THEN 'Senet Karşılığı Kredi'
WHEN TRANSTYPE = 8 THEN 'Çek Karşılığı Kredi' WHEN TRANSTYPE = 9 THEN 'Teminat Mektubu' END AS [Hesap Detayı], LGMAIN.LINEEXP AS Açıklama,
LGMAIN.SPECODE AS [Özel Kodu], CASE LGMAIN.TRCURR WHEN 0 THEN 'TL' ELSE
(SELECT TOP 1 C.CURCODE
FROM L_CURRENCYLIST C
WHERE C.FIRMNR = 004 AND C.CURTYPE = LGMAIN.TRCURR) END AS [İşlem Dövizi], LGMAIN.TRRATE AS [İşlem Döviz Kuru],
CASE WHEN LGMAIN.SIGN = 0 THEN LGMAIN.TRNET ELSE 0 END AS [İşlem Dövizi Borç],
CASE WHEN LGMAIN.SIGN = 1 THEN LGMAIN.TRNET * - 1 ELSE 0 END AS [İşlem Dövizi Alacak], (CASE WHEN LGMAIN.SIGN = 0 THEN LGMAIN.TRNET ELSE 0 END)
- (CASE WHEN LGMAIN.SIGN = 1 THEN LGMAIN.TRNET ELSE 0 END) AS [İşlem Dövizi Kalan], LGMAIN.REPORTRATE AS [Raporlama Döviz Kuru],
CASE WHEN LGMAIN.SIGN = 0 THEN LGMAIN.REPORTNET ELSE 0 END AS [Raporlama Dövizi Borç],
CASE WHEN LGMAIN.SIGN = 1 THEN LGMAIN.REPORTNET * - 1 ELSE 0 END AS [Raporlama Dövizi Alacak],
(CASE WHEN LGMAIN.SIGN = 0 THEN LGMAIN.REPORTNET ELSE 0 END) - (CASE WHEN LGMAIN.SIGN = 1 THEN LGMAIN.REPORTNET ELSE 0 END)
AS [Raporlama Dövizi Kalan],
CASE BNACC.CARDTYPE WHEN 1 THEN '(TH) Ticari Hesap' WHEN 2 THEN '(KH) Kredi Hesabı' WHEN 3 THEN '(DT) Dövizli Ticari Hesap' WHEN 4 THEN '(DK) Dövizli Kredi Hesabı'
END AS [Hesap Türü], CASE BNACC.CURRENCY WHEN 0 THEN 'TL' ELSE
(SELECT TOP 1 C.CURCODE
FROM L_CURRENCYLIST C
WHERE C.FIRMNR = 004 AND C.CURTYPE = BNACC.CURRENCY) END AS [Banka İşlem Dövizi], BANK.SPECODE AS [Banka Özel Kodu],
BANK.CYPHCODE AS [Banka Yetki Kodu], BANK.BRANCHNO AS [Banka Şube Kodu], BANK.TELNRS1 + ' ' + BANK.TELNRS2 AS [Banka Telefonları],
BANK.FAXNR AS [Banka Fax No], BANK.INCHARGE AS [Banka İlgilisi],
CASE CLC.CARDTYPE WHEN 1 THEN 'Alıcı' WHEN 2 THEN 'Satıcı' WHEN 3 THEN 'Alıcı + Satıcı' WHEN 4 THEN 'Grup Şirketi' END AS [Cari Türü],
CLC.CODE AS [Cari Kodu], CLC.DEFINITION_ AS [Cari Adı], SUBSTRING(CLC.CODE, 1, 3) AS [Cari Kod Grubu 1], SUBSTRING(CLC.CODE, 5, 2) AS [Cari Kod Grubu 2],
SUBSTRING(CLC.CODE, 8, 2) AS [Cari Kod Grubu 3], CLC.SPECODE AS [Cari Özel Kodu], CLC.CYPHCODE AS [Cari Yetki Kodu], CLC.TRADINGGRP AS [Ticari Grubu],
LTRIM(ISNULL(CLC.ADDR1, '') + ' ' + ISNULL(CLC.ADDR2, '')) AS Adresi, CLC.CITY AS Şehir, CLC.COUNTRY AS Ülke, CLC.POSTCODE AS [Posta Kodu],
CLC.TELNRS1 AS [Telefon 1], CLC.TELNRS2 AS [Telefon 2], CLC.FAXNR AS [Fax Numarası], CLC.TAXNR AS [Vergi No], CLC.TAXOFFICE AS [Vergi Dairesi],
CLC.INCHARGE AS [Yetkili Kişi], CASE CLC.CCURRENCY WHEN 0 THEN 'TL' ELSE
(SELECT TOP 1 C.CURCODE
FROM L_CURRENCYLIST C
WHERE C.FIRMNR = 004 AND C.CURTYPE = CLC.CCURRENCY) END AS [Cari İşlem Dövizi],
(SELECT ISNULL(ROUND(DEBIT, 2), 0) AS Expr1
FROM dbo.LG_004_01_GNTOTCL
WHERE (CARDREF = CLC.LOGICALREF) AND (TOTTYP = 1)) AS [Cari Güncel Borç],
(SELECT ISNULL(ROUND(CREDIT, 2), 0) AS Expr1
FROM dbo.LG_004_01_GNTOTCL
WHERE (CARDREF = CLC.LOGICALREF) AND (TOTTYP = 1)) AS [Cari Güncel Alacak],
(SELECT ISNULL(ROUND(DEBIT - CREDIT, 2), 0) AS Expr1
FROM dbo.LG_004_01_GNTOTCL
WHERE (CARDREF = CLC.LOGICALREF) AND (TOTTYP = 1)) AS [Cari Güncel Bakiye], DATEPART(dd, LGMAIN.DATE_) AS [İşlem Gün], DATEPART(ww,
LGMAIN.DATE_) AS [İşlem Hafta], DATEPART(mm, LGMAIN.DATE_) AS [İşlem Ay], DATENAME(month, LGMAIN.DATE_) AS [İşlem Ayı], DATENAME(qq, LGMAIN.DATE_)
AS [İşlem Çeyrek], DATEPART(yyyy, LGMAIN.DATE_) AS [İşlem Yıl]
FROM dbo.LG_004_01_BNFLINE AS LGMAIN LEFT OUTER JOIN
dbo.LG_004_BANKACC AS BNACC ON LGMAIN.BNACCREF = BNACC.LOGICALREF LEFT OUTER JOIN
dbo.LG_004_BNCARD AS BANK ON BANK.LOGICALREF = LGMAIN.BANKREF LEFT OUTER JOIN
dbo.LG_004_CLCARD AS CLC ON CLC.LOGICALREF = LGMAIN.CLIENTREF