tsql-parser/query.sql at main · shellord/tsql-parser · GitHub

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
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
SELECT
    BRANCHES.BRIEF_DESC_ENG,
    BRANCHES.BRIEF_DESC_ARAB,
    CTSTRS.TRS_DATE,
    CTSTRS.VALUE_DATE,
    CURRENCIES_A.BRIEF_DESC_ENG CURRENCIES_A_BRIEF_DESC_ENG,
    CURRENCIES_A.LONG_DESC_ENG CURRENCIES_A_LONG_DESC_ENG,
    CURRENCIES_A.BRIEF_DESC_ARAB CURRENCIES_A_BRIEF_DESC_ARAB,
    CURRENCIES_A.DECIMAL_POINTS CURRENCIES_A_DECIMAL_POINTS,
    CURRENCIES_B.BRIEF_DESC_ENG CURRENCIES_B_BRIEF_DESC_ENG,
    CURRENCIES_B.LONG_DESC_ENG CURRENCIES_B_LONG_DESC_ENG,
    CURRENCIES_B.BRIEF_DESC_ARAB CURRENCIES_B_BRIEF_DESC_ARAB,
    CURRENCIES_B.DECIMAL_POINTS CURRENCIES_B_DECIMAL_POINTS,
    CURRENCIES_D.BRIEF_DESC_ENG CURRENCIES_D_BRIEF_DESC_ENG,
    CURRENCIES_D.LONG_DESC_ENG CURRENCIES_D_LONG_DESC_ENG,
    CURRENCIES_D.BRIEF_DESC_ARAB CURRENCIES_D_BRIEF_DESC_ARAB,
    CURRENCIES_D.DECIMAL_POINTS CURRENCIES_D_DECIMAL_POINTS,
    CTSTRS.DEPOSITER_NAME,
    CTSTRS.DEPOSITER_ID,
    CTSTRS.EXCHANGE_RATE,
    CTSTRS.CREATED_BY,
    CTSTRS.AUTH_BY,
    CTSTRS.DATE_AUTH,
    CTSTRS.TRS_NO,
    CTSTRS.TRS_AC_AMOUNT,
    CTSTRS.AMOUNT,
    CTSTRS.CV_AMOUNT,
    CTSTRS.TRS_AC_BR FR_AC_BR,
    CTSTRS.TRS_AC_CY FR_AC_CY,
    CTSTRS.TRS_AC_GL FR_AC_GL,
    CTSTRS.TRS_AC_CIF FR_AC_CIF,
    CTSTRS.TRS_AC_SL FR_AC_SL,
    CTSTRS.TO_TRS_AC_BR TO_AC_BR,
    CTSTRS.TO_TRS_AC_CY TO_AC_CY,
    CTSTRS.TO_TRS_AC_GL TO_AC_GL,
    CTSTRS.TO_TRS_AC_CIF TO_AC_CIF,
    CTSTRS.TO_TRS_AC_SL TO_AC_SL,
    CTSTRS.TRS_CY,
    CTSTRS.TRS_AC_CY,
    CTSTRS.DEPOSITER_ID AS CTSTRS_DEPOSITER_ID_1,
    CTSTRS.DEPOSITER_NAME AS CTSTRS_DEPOSITER_NAME_2,
    CTSTRS.RELEASE_DATE,
    CTSTRS.DATE_CLEARED,
    CTSTRS.CLEARED_BY,
    CASE
        WHEN CTSREASONS.BRIEF_NAME_ENG IS NULL THEN CTSTRS.VOID_REASON
        ELSE CTSREASONS.BRIEF_NAME_ENG
    END REASON,
    AMF_A.LONG_NAME_ENG FR_AC_ENG_NAME,
    AMF_A.LONG_NAME_ARAB FR_AC_ARA_NAME,
    GEN_LEDGER.BRIEF_DESC_ENG AS GEN_LEDGER_BRIEF_DESC_EN_3,
    GEN_LEDGER.LONG_DESC_ENG,
    GEN_LEDGER.BRIEF_DESC_ARAB AS GEN_LEDGER_BRIEF_DESC_AR_4,
    CIF.SHORT_NAME_ENG,
    CIF.SHORT_NAME_ARAB,
    CIF.LONG_NAME_ENG,
    CIF.LONG_NAME_ARAB,
    CIF.ID_NO,
    CASE CTSTRS.CHARGE
        WHEN NULL THEN 0
        ELSE CTSTRS.CHARGE
    END CTSTRS_CHARGE,
    RIFMESSAGE.MSG_ENG,
    RIFMESSAGE.MSG_ARAB,
    CTSTRS.INSTRUCTIONS1,
    CTSTRS.INSTRUCTIONS2,
    CASE AMF_A.ADDITIONAL_REFERENCE
        WHEN NULL THEN ''
        ELSE AMF_A.ADDITIONAL_REFERENCE
    END ACC_ADD_REF,
    CASE
        WHEN CTSTRS.TRS_CY = COMPANIES.BASE_CURRENCY THEN CASE CTSTRS.CHARGE
            WHEN NULL THEN 0
            ELSE CTSTRS.CHARGE
        END
        ELSE CASE CTSTRS.FC_CHARGES
            WHEN NULL THEN 0
            ELSE CTSTRS.FC_CHARGES
        END
    END CHARGES,
    CASE
        WHEN CTSTRS.TRS_AC_CY = COMPANIES.BASE_CURRENCY THEN CASE CTSTRS.CHARGE
            WHEN NULL THEN 0
            ELSE CTSTRS.CHARGE
        END
        ELSE CASE CTSTRS.FC_CHARGES
            WHEN NULL THEN 0
            ELSE CTSTRS.FC_CHARGES
        END
    END CHARGES_AC_CY,
    CTSTRS.DEAL_NUMBER,
    CTSTRS.STATUS,
    CASE CTSTRS.STATUS
        WHEN 'R' THEN ' /' + CONVERT(VARCHAR, CTSTRS.DATE_REVERSED, 103) + '/ ' + 'REVERSED BY :' + CTSTRS.REVERSED_BY
        WHEN 'V' THEN ' /' + CONVERT(VARCHAR, CTSTRS.DATE_REVERSED, 103) + '/ ' + 'REVERSED BY :' + CTSTRS.REVERSED_BY
        ELSE ''
    END REVERSED
FROM
    CTSTRS,
    BRANCHES,
    CURRENCIES CURRENCIES_A,
    CURRENCIES CURRENCIES_B,
    CURRENCIES CURRENCIES_D,
    COMPANIES,
    CTSREASONS,
    AMF AMF_A,
    GEN_LEDGER,
    CIF,
    RIFMESSAGE
WHERE
    (COMPANIES.COMP_CODE = PLACEHOLDER)
    AND (COMPANIES.COMP_CODE = CURRENCIES_D.COMP_CODE)
    AND (
        COMPANIES.BASE_CURRENCY = CURRENCIES_D.CURRENCY_CODE
    )
    AND (BRANCHES.COMP_CODE = CTSTRS.COMP_CODE)
    AND (CTSTRS.BRANCH_CODE = BRANCHES.BRANCH_CODE)
    AND (CTSTRS.COMP_CODE = CURRENCIES_A.COMP_CODE)
    AND (CTSTRS.TRS_AC_CY = CURRENCIES_A.CURRENCY_CODE)
    AND (CTSTRS.COMP_CODE = CURRENCIES_B.COMP_CODE)
    AND (CTSTRS.TRS_CY = CURRENCIES_B.CURRENCY_CODE)
    AND (CTSTRS.COMP_CODE = CURRENCIES_B.COMP_CODE)
    AND (CTSTRS.TRS_CY = CURRENCIES_B.CURRENCY_CODE)
    AND (CTSTRS.COMP_CODE = AMF_A.COMP_CODE)
    AND (CTSTRS.TRS_AC_BR = AMF_A.BRANCH_CODE)
    AND (CTSTRS.TRS_AC_CY = AMF_A.CURRENCY_CODE)
    AND (CTSTRS.TRS_AC_GL = AMF_A.GL_CODE)
    AND (CTSTRS.TRS_AC_CIF = AMF_A.CIF_SUB_NO)
    AND (CTSTRS.TRS_AC_SL = AMF_A.SL_NO)
    AND (AMF_A.GMI_FLAG <> 'I')
    AND (CTSTRS.COMP_CODE = PLACEHOLDER)
    AND (CTSTRS.BRANCH_CODE = PLACEHOLDER)
    AND (CTSTRS.CB_IND = 'C')
    AND (CTSTRS.TRS_NO = PLACEHOLDER)
    AND (CTSTRS.STATUS = 'L')
    AND (CTSTRS.TRS_TYPE = 'R')
    AND (CTSTRS.COMP_CODE * = CTSREASONS.COMP_CODE)
    AND (
        CTSTRS.VOID_REASON * = CONVERT(VARCHAR, CTSREASONS.CODE)
    )
    AND (CTSREASONS.REASON_TYPE = '3')
    AND (CTSTRS.COMP_CODE = GEN_LEDGER.COMP_CODE)
    AND (CTSTRS.TRS_AC_GL = GEN_LEDGER.GL_CODE)
    AND (CTSTRS.COMP_CODE = CIF.COMP_CODE)
    AND (CTSTRS.TRS_AC_CIF = CIF.CIF_NO)
    AND (COMPANIES.COMP_CODE * = RIFMESSAGE.COMP_CODE)
    AND (RIFMESSAGE.MSG_CODE = 1)