Aşağıdaki kod bloğu ile cari bilgileri çekilebilir
SELECT
F0101TBLCARI.IND,
F0101TBLCARI.FIRMAADI,
F0101TBLCARI.FIRMAKODU,
F0101TBLCARI.FIRMATIPI,
CASE
WHEN F0101TBLCARI.FIRMATIPI <> 5 THEN (ISNULL(F0101TBLCARI.ADI, '') + ' ' + ISNULL(F0101TBLCARI.SOYADI, ''))
ELSE F0101TBLCARI.YETKILI
END AS YETKILI,
F0101TBLCARI.PARABIRIMI,
F0101TBLCARI.AYLIKVADE,
F0101TBLCARI.GECIKMEFAIZI,
F0101TBLCARI.VERGIDAIRESI,
F0101TBLCARI.VERGINO,
CONVERT(nvarchar(250), F0101TBLCARI.ADRESPOSTA) ADRESPOSTA,
CONVERT(nvarchar(250), F0101TBLCARI.ADRESFATURA) ADRESFATURA,
CONVERT(nvarchar(250), F0101TBLCARI.ADRESSEVK) ADRESSEVK,
CONVERT(nvarchar(250), F0101TBLCARI.ISTIHBARAT) ISTIHBARAT,
F0101TBLCARI.ISKONTO,
F0101TBLCARI.KOD1,
F0101TBLCARI.KOD2,
F0101TBLCARI.KOD3,
F0101TBLCARI.KOD4,
F0101TBLCARI.KOD5,
F0101TBLCARI.UNVAN,
(SELECT
SUM(ISNULL(F0101D0004TBLCARIHAREKETLERI.BORC, 0) - ISNULL(F0101D0004TBLCARIHAREKETLERI.ALACAK, 0))
FROM F0101D0004TBLCARIHAREKETLERI
WHERE F0101D0004TBLCARIHAREKETLERI.FIRMANO = F0101TBLCARI.IND
AND OZELKOD <> 'KREDIHESABI')
AS BAKIYE,
F0101TBLCARI.TELEFON1,
F0101TBLCARI.PERSONELNO,
F0101TBLCARI.SATISYAPILMASIN,
F0101TBLCARI.TAHSILATYAPILMASIN,
F0101TBLCARI.IADEFATURASIKESILMESIN,
SUBSTRING(F0101TBLCARI.ISTIHBARAT, 1, 1) AS INFO,
F0101TBLKREMUSCUZDAN.ESININADISOYADI,
F0101TBLCARI.KREDILIMITI,
F0101TBLCARI.RISKLIMITI,
F0101TBLCARI.FIRMATAKIPKODU,
F0101TBLCARI.EFATURAKULLANICISI
FROM F0101TBLCARI
LEFT JOIN F0101TBLKREMUSCUZDAN
ON F0101TBLCARI.IND = F0101TBLKREMUSCUZDAN.MUSTERINO
WHERE (F0101TBLCARI.STATUS <> 2
OR F0101TBLCARI.STATUS IS NULL)
AND F0101TBLCARI.IND >= 100
AND (F0101TBLCARI.FIRMATIPI = 1
OR F0101TBLCARI.FIRMATIPI = 2
OR F0101TBLCARI.FIRMATIPI = 5
OR F0101TBLCARI.FIRMATIPI = 4
OR F0101TBLCARI.FIRMATIPI = 11
OR F0101TBLCARI.FIRMATIPI = 9
OR F0101TBLCARI.FIRMATIPI = 13)
AND FIRMATIPI NOT IN (12, 11)
AND FIRMATIPI NOT IN (12, 11)
Aşağıdaki kod bloğu da kullanılabilir
SELECT
TBLCARI.FIRMAKODU,
TBLCARI.KOD1,
TBLCARI.KOD2,
TBLCARI.KOD3,
CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)) as ADRES,
TBLCARI.FIRMAADI,
TBLCARI.TELEFON1,
TBLCARI.TELEFON2,
TBLCARI.YGSM,
TBLCARI.FAKS,MAX(TCG.TARIH) AS SONISLMTRHI,
CASE WHEN TCG.PARABIRIMI IS NULL THEN TBLCARI.PARABIRIMI ELSE TCG.PARABIRIMI END AS PARABIRIMI,
DBO.ROUNDYTL(ISNULL(SUM(TCG.BORC / case WHEN ISNULL(TCG.KUR,1) = 0 then 1 else TCG.KUR end),0)) as BORC,
DBO.ROUNDYTL(ISNULL(SUM(TCG.ALACAK / case WHEN ISNULL(TCG.KUR,1) = 0 then 1 else TCG.KUR end),0)) as ALACAK,
(SELECT DBO.ROUNDYTL(ISNULL(SUM((BORC - ALACAK) / case WHEN ISNULL(KUR,1) = 0 then 1 else KUR end),0))
FROM F0101D0001TBLCARIHAREKETLERI WHERE FIRMANO = TBLCARI.IND and PARABIRIMI = TCG.PARABIRIMI AND 1=1) as BAKIYE,
DBO.ROUNDYTL(ISNULL(SUM(TCG.BORC),0)) as TOPLAMBORC,DBO.ROUNDYTL(ISNULL(SUM(TCG.ALACAK),0)) AS TOPLAMALACAK,
( SELECT DBO.ROUNDYTL(ISNULL(SUM(BORC - ALACAK),0)) FROM F0101D0001TBLCARIHAREKETLERI
WHERE FIRMANO = TBLCARI.IND AND PARABIRIMI = TCG.PARABIRIMI AND 1=1 ) AS TOPLAMBAKIYE
FROM F0101TBLCARI as TBLCARI LEFT JOIN F0101D0001TBLCARIHAREKETLERI as TCG on(TBLCARI.IND = TCG.FIRMANO)
WHERE(TBLCARI.IND >= 100)
AND TBLCARI.FIRMATIPI NOT IN(5)
GROUP BY TBLCARI.IND,
TCG.PARABIRIMI,
TBLCARI.PARABIRIMI,
TBLCARI.KOD1,
TBLCARI.KOD2,
TBLCARI.KOD3,
CAST(TBLCARI.ADRESFATURA AS NVARCHAR(100)),
TBLCARI.TELEFON2,
TBLCARI.TELEFON1,
TBLCARI.YGSM,
TBLCARI.FAKS,
TBLCARI.FIRMAKODU,
TBLCARI.FIRMAADI
HAVING 1=1
ORDER BY TBLCARI.IND