SQL Sorgu Borç - Alacak Durum Raporu

SELECT
SPC3.DEFINITION_ AS 'CH Özel Kod 3', --ŞİRKETİMİZDE BORÇ ALACAK TAKİBİ CARİ KARTLAR İÇİNDE TANIMLANAN CARİ HESAP 3 ALANINA GÖRE YAPILMAKTADIR. BAZI REVİZELER YAPILDIKTAN SONRA SALESMAN TABLOSUNDAN TAKİP YAPILACAKTIR.
CL.DEFINITION_ AS 'Cari Ünvanı',
CL.CODE AS 'Cari Kodu',

CONVERT(MONEY,(SELECT ISNULL(SUM(TMN.AMOUNT),0) FROM LG_069_01_COLLATRLCARD TMN WHERE TMN.CLCARDREF=CL.LOGICALREF AND TMN.CURRSTAT IN (1,2) AND (TMN.ENDDATE>GETDATE() OR ISNULL(TMN.ENDDATE,0)=0))*-1
) AS [Teminat Mektubu],

CONVERT(MONEY,(SELECT ISNULL(SUM(ORL.VATMATRAH+ORL.VATAMNT),0) FROM LG_069_01_ORFLINE ORL WHERE ORL.CLIENTREF=CL.LOGICALREF AND ORL.STOCKREF>0 AND ORL.STATUS IN (1,2) AND ORL.SHIPPEDAMOUNT=0)
) AS [Sipariş Riski],

CONVERT(MONEY,(SELECT ISNULL(SUM(STL.VATMATRAH+VATAMNT),0) FROM LG_069_01_STLINE STL WHERE STL.CLIENTREF=CL.LOGICALREF AND STL.INVOICEREF=0 AND STL.CANCELLED=0)
) AS [Faturalanmamış İrsaliye Riski],

CONVERT(MONEY,(SELECT ISNULL(SUM(CLF.AMOUNT*(1-CLF.SIGN)-(CLF.SIGN*CLF.AMOUNT)),0) FROM LG_069_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND CLF.CANCELLED=0 AND CLF.PAIDINCASH=0)
) AS [Açık Bakiye Riski],

CONVERT(MONEY,(SELECT ISNULL(SUM(CSC.AMOUNT),0) FROM LG_069_01_CSCARD CSC WHERE CSC.CURRSTAT IN (1,2,3,4,5,7,12,14) AND CSC.LOGICALREF IN (SELECT CST.CSREF FROM LG_069_01_CSTRANS CST WHERE CST.CARDREF=CL.LOGICALREF))
) AS [Vadesi Gelmemiş Çekler Riski],

CONVERT(MONEY,((SELECT ISNULL(SUM(CLF.AMOUNT*(1-CLF.SIGN)-(CLF.SIGN*CLF.AMOUNT)),0) FROM LG_069_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND CLF.CANCELLED=0 AND CLF.PAIDINCASH=0)+(SELECT ISNULL(SUM(CSC.AMOUNT),0) FROM LG_069_01_CSCARD CSC WHERE CSC.CURRSTAT IN (1,2,3,4,5,7,12,14) AND CSC.LOGICALREF IN (SELECT CST.CSREF FROM LG_069_01_CSTRANS CST WHERE CST.CARDREF=CL.LOGICALREF))
)) AS [TOPLAM]

FROM LG_069_CLCARD CL
LEFT JOIN LG_069_SPECODES SPC3 ON SPC3.SPECODE=CL.SPECODE3 AND SPC3.SPECODETYPE=26

WHERE
CONVERT(MONEY,((SELECT ISNULL(SUM(CLF.AMOUNT*(1-CLF.SIGN)-(CLF.SIGN*CLF.AMOUNT)),0) FROM LG_069_01_CLFLINE CLF WHERE CLF.CLIENTREF=CL.LOGICALREF AND CLF.CANCELLED=0 AND CLF.PAIDINCASH=0)+(SELECT ISNULL(SUM(CSC.AMOUNT),0) FROM LG_069_01_CSCARD CSC WHERE CSC.CURRSTAT IN (1,2,3,4,5,7,12,14) AND CSC.LOGICALREF IN (SELECT CST.CSREF FROM LG_069_01_CSTRANS CST WHERE CST.CARDREF=CL.LOGICALREF))
)) <> '0' --TOPLAMI SIFIR OLMAYANLAR

ORDER BY 'CH Özel Kod 3','TOPLAM' DESC -- CH ÖZEL KOD 3 ALANINA GÖRE KÜÇÜKTEN BÜYÜĞE VE TOPLAM ALANINA GÖRE BÜYÜKTEN KÜÇÜĞE SIRALAMA