SQL BURDA
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],
ÜLGMAIN.REPORTRATE AS [Raporlama Döviz Kuru],
KSC.CODE AS [Kasa Kodu],
KSC.NAME AS [Kasa Adı],
KSC.SPECODE AS [Kasa Özel Kodu],
KSC.CYPHCODE AS [Kasa Yetki Kodu], KSV.CODE AS [Virman Kasa Kodu], KSV.NAME AS [Virman Kasa Adı],
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], CAST(USRC.NR AS CHAR(3)) + ' ' + USRC.NAME AS [Ekleyen Kullanıcı],
LGMAIN.CAPIBLOCK_CREADEDDATE AS [Ekleme Tarihi], CAST(USRM.NR AS CHAR(3)) + ' ' + USRM.NAME AS [Değiştiren Kullanıcı],
LGMAIN.CAPIBLOCK_MODIFIEDDATE AS [Değiştirme Tarihi], 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_KSLINES AS LGMAIN LEFT OUTER JOIN
dbo.LG_004_KSCARD AS KSC ON KSC.LOGICALREF = LGMAIN.CARDREF LEFT OUTER JOIN
dbo.LG_004_KSCARD AS KSV ON KSV.LOGICALREF = LGMAIN.VCARDREF LEFT OUTER JOIN
dbo.LG_004_01_CLFLINE AS CFL ON CFL.LOGICALREF = LGMAIN.TRANSREF AND LGMAIN.TRCODE < 70 LEFT OUTER JOIN
dbo.LG_004_CLCARD AS CLC ON CLC.LOGICALREF = CFL.CLIENTREF LEFT OUTER JOIN
dbo.L_CAPIUSER AS USRC ON USRC.NR = LGMAIN.CAPIBLOCK_CREATEDBY LEFT OUTER JOIN
dbo.L_CAPIUSER AS USRM ON USRM.NR = LGMAIN.CAPIBLOCK_MODIFIEDBY
