-
Notifications
You must be signed in to change notification settings - Fork 77
/
cekler.sql
84 lines (82 loc) · 3.09 KB
/
cekler.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
SELECT LG_181_CLCARD_2.CODE AS KİMDEN,
C.DOC AS [çek 1 senet 2],
C.BANKNAME AS BANKA,
Substring(C.BNBRANCHNO, 6, 12) AS ŞUBE,
C.NEWSERINO AS [Seri No],
Substring(CONVERT(varchar(10), C.DUEDATE, 103), 7, 4) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 4, 2) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 1, 2) AS vadesi,
C.AMOUNT AS Tutar,
CASE CURRSTAT
WHEN 1 THEN 'Portföyde'
WHEN 2 THEN 'Ciro Edildi'
WHEN 3 THEN 'Teminata Verildi'
WHEN 4 THEN 'Tahsile Verildi'
WHEN 5 THEN 'Protestolu Tahsile Verildi'
WHEN 6 THEN 'İade Edildi'
WHEN 7 THEN 'Protesto Edildi'
WHEN 8 THEN 'Tahsil Edildi'
WHEN 9 THEN 'Kendi Çekimiz'
WHEN 10 THEN 'Borç Senedimiz'
WHEN 11 THEN 'Karşılığı Yok'
WHEN 12 THEN 'Tahsil Edilemiyor'
ELSE 'Ne Oldugu Belirsiz'
END
AS DURUMU,
C.OWING AS [CİRO EDEN],
C.DUEDATE AS Vade,
LG_181_01_CSROLL.ROLLNO,
(
SELECT DEFINITION_ FROM LG_181_CLCARD AS LG_181_CLCARD_1
WHERE (
LOGICALREF IN (SELECT CARDREF FROM LG_181_01_CSTRANS AS LG_181_01_CSTRANS_1 WHERE ( CSREF = C.LOGICALREF ) AND ( TRCODE = 3 ))
)
) AS [CİRO EDİLEN],
LG_181_01_CSTRANS_2.CARDREF,
LG_181_BANKACC.CODE
BANKA
FROM LG_181_01_CSCARD AS C
INNER JOIN dbo.LG_181_CLCARD AS LG_181_CLCARD_2
INNER JOIN dbo.LG_181_01_CSROLL ON LG_181_CLCARD_2.LOGICALREF = dbo.LG_181_01_CSROLL.CARDREF
INNER JOIN dbo.LG_181_01_CSTRANS AS LG_181_01_CSTRANS_2 ON dbo.LG_181_01_CSROLL.LOGICALREF = LG_181_01_CSTRANS_2.ROLLREF ON C.LOGICALREF = LG_181_01_CSTRANS_2.CSREF
INNER JOIN LG_181_BANKACC ON LG_181_BANKACC.LOGICALREF = LG_181_01_CSTRANS_2.CARDREF
WHERE ( dbo.LG_181_01_CSROLL.TRCODE IN ( 05, 06, 07, 08 ) )
AND CURRSTAT = 4
UNION ALL
SELECT
LG_181_CLCARD_2.CODE AS KİMDEN,
C.DOC AS [çek 1 senet 2],
C.BANKNAME AS BANKA,
Substring(C.BNBRANCHNO, 6, 12) AS ŞUBE,
C.NEWSERINO AS [Seri No],
Substring(CONVERT(varchar(10), C.DUEDATE, 103), 7, 4) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 4, 2) + Substring(CONVERT(varchar(10), C.DUEDATE, 103), 1, 2) AS vadesi,
C.AMOUNT AS Tutar,
CASE CURRSTAT
WHEN 1 THEN 'Portföyde'
WHEN 2 THEN 'Ciro Edildi'
WHEN 3 THEN 'Teminata Verildi'
WHEN 4 THEN 'Tahsile Verildi'
WHEN 5 THEN 'Protestolu Tahsile Verildi'
WHEN 6 THEN 'İade Edildi'
WHEN 7 THEN 'Protesto Edildi'
WHEN 8 THEN 'Tahsil Edildi'
WHEN 9 THEN 'Kendi Çekimiz'
WHEN 10 THEN 'Borç Senedimiz'
WHEN 11 THEN 'Karşılığı Yok'
WHEN 12 THEN 'Tahsil Edilemiyor'
ELSE 'Ne Oldugu Belirsiz'
END
AS DURUMU,
C.OWING AS [CİRO EDEN],
C.DUEDATE AS Vade,
LG_181_01_CSROLL.ROLLNO,
(
SELECT DEFINITION_
FROM LG_181_CLCARD AS LG_181_CLCARD_1
WHERE ( LOGICALREF IN (SELECT CARDREF FROM LG_181_01_CSTRANS AS LG_181_01_CSTRANS_1 WHERE ( CSREF = C.LOGICALREF ) AND ( TRCODE = 3 )) )
) AS [CİRO EDİLEN],
'',
''
FROM LG_181_01_CSCARD AS C
INNER JOIN dbo.LG_181_CLCARD AS LG_181_CLCARD_2
INNER JOIN dbo.LG_181_01_CSROLL ON LG_181_CLCARD_2.LOGICALREF = dbo.LG_181_01_CSROLL.CARDREF
INNER JOIN dbo.LG_181_01_CSTRANS AS LG_181_01_CSTRANS_2 ON dbo.LG_181_01_CSROLL.LOGICALREF = LG_181_01_CSTRANS_2.ROLLREF ON C.LOGICALREF = LG_181_01_CSTRANS_2.CSREF
where currstat = 1