-
Notifications
You must be signed in to change notification settings - Fork 0
/
application.properties
327 lines (264 loc) · 37.4 KB
/
application.properties
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
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
##Port in which server is runnuning
server.port = 8022
spring.batch.job.enabled=false
#Live Oracle server
spring.datasource.url=jdbc:oracle:thin:@10.10.127.33:1521:FGMWUAT
spring.datasource.username=FGMWUAT
spring.datasource.password=FGMWUAT_123
# #Live Oracle server
# spring.datasource.url=jdbc:oracle:thin:@ucpprddb01.mahindrafs.com:1621:UCPPRODDB1
# spring.datasource.username=MWDBADMIN
# spring.datasource.password=MWDBADMIN123
##Local Oracle Server
#spring.datasource.url=jdbc:oracle:thin:@localhost:1680:orcl
#spring.datasource.username=MMFSL302
#spring.datasource.password=MMFSL301
spring.jpa.hibernate.dialect=org.hibernate.dialect.Oracle12cDialect
#spring.jpa.properties.hibernate.hbm2ddl.auto=create
spring.jpa.show-sql=true
##token expiry time
token.expire-length=1440
jwt-validation-required=false
##Validation required
isHeaderValidation=false
##Custom Dashboard queries
LOAN_LIST_INQUIRY_QUERY=SELECT CLUSTER_ID, SUM(Released_Amount) as Total_Realeased_Amount, SUM(Utilized_Amount) as Total_Utilized_Amount, SUM(Available_Amount) as Total_Available_Amount, SUM(Outstanding_Amount) as Total_Outstanding_Amount FROM vw_Loan_List_Enquiry where CLUSTER_ID='%s' GROUP BY CLUSTER_ID;
INSTITITION_LENDING_PAYMENT_QUERY= select CLUSTERID as clusterId , CIFID as cifId , DUE_DATE as dueDate , TOTAL_OVERDUE_AMOUNT as totalOverDueAmount , FINANCEDAMOUNT as financedAmount , AVAILABLE_AMOUNT as availableAmount , DUE_AMOUNT as dueAmount, LOAN_EXPIRY_DATE as loanExpiryDate , OUTSTANDING_AMOUNT as outstandingAmount , PRODUCT_TYPE as productType , VIRACCNUMBER as virAccNumber , FACILITY_ID as facilityId , STATUS as loanStatus , DISBURSEDAMOUNT as disbursedAmount , LOANNUMBER as loanNumber FROM vw_IL_Payment_Inquiry where LOANNUMBER = '%s'
IL_REPAYMENT_SCHEDULE_QUERY = select INSTALLMENT_DATE as installmentDueDate , INSTALLMENT_NO as installmentNumber , INSTALLMENT_AMOUNT as installmentDue , PRINCIPAL_AMOUNT as principleAmount , INTEREST_AMOUNT as interestAmount , PRINCIPAL_OUTSTANDING as principal_os FROM vw_IL_Repayment_Schedule where Contract_NUMBER = '%s' order by INSTALLMENT_DATE
#INTELLECT_REPAYMENT_SCHEDULE_QUERY = select Instalment_date as installmentDueDate, Instalment_Number as installmentNumber , Instalment_Amount as installmentDue , Principal_Amount as principleAmount , Interest_Amount as interestAmount , principal_os as principal_os FROM vw_IDAL_Repayment_Schedule_Details where loan_number = '%s' order by Instalment_Number
INTELLECT_REPAYMENT_SCHEDULE_QUERY = select Instalment_date as installmentDueDate, Instalment_Number as installmentNumber , Instalment_Amount as installmentDue , Principal_Amount as principleAmount , Interest_Amount as interestAmount , principal_os as principal_os FROM vw_IDAL_Repayment_Schedule_Details where loan_number = '%s' order by Instalment_date
LOAN_LIST_REPORT_INQUIRY_QUERY = select * FROM vw_Loan_List_Enquiry where DataSource <> 'Misys' and Loan_Status='ACTIVE' and CLUSTER_ID ='%s'
INTELLECT_SOA_REPORTS_QUERY = select tsr_slno as tnxSlNo , tsr_tran_dt as tnxDate , tsr_tran_dlts as tnxDtls , tsr_value_dt as tnxValueDate , cheque_no as tnxReference , credit_debit as credit_debit, tsr_tran_amt as tnxAmount , tsr_balance as tnxBalance FROM vw_IDAL_SubSOA_Report where Loan_Number = '%s' order by tnxSlNo
vw_IDAL_SubSOA_Report_QUERY=select tsr_cust_id "tsr_cust_id", tsr_con_id "tsr_con_id", tsr_proj_id "tsr_proj_id", tsr_rep_id "tsr_rep_id", tsr_slno "tsr_slno", to_char(tsr_tran_dt, 'DD-MON-YYYY') "tsr_tran_dt", to_char(tsr_value_dt, 'DD-MON-YYYY') "tsr_value_dt" , tsr_ref_no "cheque_no", tsr_tran_dlts "tsr_tran_dlts", decode(sign(nvl(tsr_tran_amt, 0)), -1, 'Dr', 1, 'Cr', 0, null) "credit_debit", tsr_tran_amt "tsr_tran_amt", tsr_balance "tsr_balance", mpi_legacy_id "Loan_Number" from sme_ucp.T_SOA_REPORT_UCP, mflms.m_proj_info where tsr_cust_id = mpi_cust_id and tsr_con_id = mpi_con_id and tsr_proj_id = mpi_proj_id and mpi_legacy_id = '{#loan_account_no}' and tsr_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' order by tsr_cust_id, tsr_con_id, tsr_proj_id, tsr_slno
vw_IDAL_ACH_Bounced_QUERY=select customer_id "customer_id", process_code "process_code", payment_mode "payment_mode", city "city", bank_id "bank_id", bank_name "bank_name", branch_name "branch_name", cheque_status "cheque_status", cheque_no "cheque_no", to_char(cheque_date, 'DD-MON-YYYY') "cheque_date", cheque_amt "cheque_amt" from ( select customer_id, process_code, payment_mode, city, bank_id, bank_name, branch_name, cheque_status, cheque_no, to_date(cheque_date, 'DD-MON-YYYY') cheque_date, cheque_amt from ( select customer_id, process_code, payment_mode, city, bank_id, bank_name, branch_name, cheque_status, cheque_no, cheque_date, cheque_amt from ( select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(hcp_instr_status, 'C', 'Cleared') cheque_status, hcp_instr_no cheque_no, to_char(hcp_instr_dt, 'DD-MON-YYYY') cheque_date, hcp_pmt_amt cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id and hpp_proj_id = mpi_proj_id and hcp_bank_cd = mbd_bank_cd and hcp_pmt_mode = 'Q' and hcp_instr_status = 'R' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' union select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(iapd_paid_ind, 1, 'Paid') ach_status, to_char(IAPD_TRANS_REF_NO) ach_no, to_char(IAPD_INST_DUE_DT, 'DD-MON-YYYY') ach_date, iapd_tot_due cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.intf_ach_pymt_dtls_hist, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id AND hpp_proj_id = mpi_proj_id and hpp_cust_id = iapd_cust_id and hpp_con_id = iapd_con_id and hpp_proj_id = iapd_proj_id and mbd_bank_cd = iapd_bank_cd and hcp_tran_dt = trunc(iapd_response_timestamp) and iapd_paid_ind <> 1 and hcp_process_cd = 'ACH' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' and IAPD_RESPONSE_TIMESTAMP is not null) union select null customer_id, null process_code, null payment_mode, null city, null bank_id, null bank_name, null branch_name, null cheque_status, null cheque_no, null cheque_date, null cheque_amt from dual where not exists (select customer_id, process_code, payment_mode, city, bank_id, bank_name, branch_name, cheque_status, cheque_no, cheque_date, cheque_amt from ( select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(hcp_instr_status, 'C', 'Cleared') cheque_status, hcp_instr_no cheque_no, to_char(hcp_instr_dt, 'DD-MON-YYYY') cheque_date, hcp_pmt_amt cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id and hpp_proj_id = mpi_proj_id and hcp_bank_cd = mbd_bank_cd and hcp_pmt_mode = 'Q' and hcp_instr_status = 'R' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' union select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(iapd_paid_ind, 1, 'Paid') ach_status, to_char(IAPD_TRANS_REF_NO) ach_no, to_char(IAPD_INST_DUE_DT, 'DD-MON-YYYY') ach_date, iapd_tot_due cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.intf_ach_pymt_dtls_hist, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id AND hpp_proj_id = mpi_proj_id and hpp_cust_id = iapd_cust_id and hpp_con_id = iapd_con_id and hpp_proj_id = iapd_proj_id and mbd_bank_cd = iapd_bank_cd and hcp_tran_dt = trunc(iapd_response_timestamp) and iapd_paid_ind <> 1 and hcp_process_cd = 'ACH' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' and IAPD_RESPONSE_TIMESTAMP is not null))) ORDER BY 10)
vw_IDAL_PDC_Cheque_QUERY=select customer_id "customer_id", process_code "process_code", payment_mode "payment_mode", city "city", bank_id "bank_id", bank_name "bank_name", branch_name "branch_name", cheque_status "cheque_status", cheque_no "cheque_no", to_char(cheque_date, 'DD-MON-YYYY') "cheque_date", cheque_amt "cheque_amt" FROM ( select customer_id, process_code, payment_mode, city, bank_id, bank_name, branch_name, cheque_status, cheque_no, TO_DATE(cheque_date, 'DD-MON-YYYY') cheque_date, cheque_amt FROM ( select customer_id, process_code, payment_mode, city, bank_id, bank_name, branch_name, cheque_status, cheque_no, cheque_date, cheque_amt from ( select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(hcp_instr_status, 'C', 'Cleared') cheque_status, hcp_instr_no cheque_no, to_char(hcp_instr_dt, 'DD-MON-YYYY') cheque_date, hcp_pmt_amt cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id and hpp_proj_id = mpi_proj_id and hcp_bank_cd = mbd_bank_cd and hcp_pmt_mode = 'Q' and hcp_instr_status = 'C' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' union select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(hcp_instr_status, 'C', 'Cleared') cheque_status, hcp_instr_no cheque_no, to_char(hcp_instr_dt, 'DD-MON-YYYY') cheque_date, hcp_pmt_amt cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id and hpp_proj_id = mpi_proj_id and hcp_bank_cd = mbd_bank_cd and hcp_pmt_mode = 'D' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' union select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(iapd_paid_ind, 1, 'Paid') ach_status, to_char(IAPD_TRANS_REF_NO) ach_no, to_char(IAPD_INST_DUE_DT, 'DD-MON-YYYY') ach_date, iapd_tot_due cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.intf_ach_pymt_dtls_hist, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id and hpp_proj_id = mpi_proj_id and hpp_cust_id = iapd_cust_id and hpp_con_id = iapd_con_id and hpp_proj_id = iapd_proj_id and mbd_bank_cd = iapd_bank_cd and hcp_tran_dt = trunc(iapd_response_timestamp) and iapd_paid_ind = 1 and hcp_process_cd = 'ACH' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' and IAPD_RESPONSE_TIMESTAMP is not null) union select null customer_id, null process_code, null payment_mode, null city, null bank_id, null bank_name, null branch_name, null cheque_status, null cheque_no, null cheque_date, null cheque_amt from dual where not exists (select customer_id, process_code, payment_mode, city, bank_id, bank_name, branch_name, cheque_status, cheque_no, cheque_date, cheque_amt from ( select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(hcp_instr_status, 'C', 'Cleared') cheque_status, hcp_instr_no cheque_no, to_char(hcp_instr_dt, 'DD-MON-YYYY') cheque_date, hcp_pmt_amt cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id and hpp_proj_id = mpi_proj_id and hcp_bank_cd = mbd_bank_cd and hcp_pmt_mode = 'Q' and hcp_instr_status = 'C' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' union select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(hcp_instr_status, 'C', 'Cleared') cheque_status, hcp_instr_no cheque_no, to_char(hcp_instr_dt, 'DD-MON-YYYY') cheque_date, hcp_pmt_amt cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id and hpp_proj_id = mpi_proj_id and hcp_bank_cd = mbd_bank_cd and hcp_pmt_mode = 'D' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' union select hcp_cust_id customer_id, hcp_process_cd process_code, hcp_pmt_mode payment_mode, mbd_br_name city, hcp_bank_cd bank_id, mbd_bank_nm bank_name, mbd_br_name branch_name, decode(iapd_paid_ind, 1, 'Paid') ach_status, to_char(IAPD_TRANS_REF_NO) ach_no, to_char(IAPD_INST_DUE_DT, 'DD-MON-YYYY') ach_date, iapd_tot_due cheque_amt from mflms.h_cust_payment, mflms.h_proj_payment, mflms.m_proj_info, mflms.intf_ach_pymt_dtls_hist, mflms.m_bank_dtls where hcp_pkey = hpp_fkey and hpp_cust_id = mpi_cust_id and hpp_con_id = mpi_con_id and hpp_proj_id = mpi_proj_id and hpp_cust_id = iapd_cust_id and hpp_con_id = iapd_con_id and hpp_proj_id = iapd_proj_id and mbd_bank_cd = iapd_bank_cd and hcp_tran_dt = trunc(iapd_response_timestamp) and iapd_paid_ind = 1 and hcp_process_cd = 'ACH' and mpi_legacy_id = '{#loan_account_no}' and hcp_tran_dt between '{#tsr_tran_dt}' and '{#tsr_value_dt}' and IAPD_RESPONSE_TIMESTAMP is not null))) ORDER BY 10)
vw_IDAL_ROI_QUERY= select 'Rate Of interest applied ' "description", tsir_cust_id "cust_id", tsir_con_id "con_id", tsir_proj_id "proj_id", to_char(tsir_from_dt, 'DD-MON-YYYY') "from_dt", to_char(tsir_to_dt, 'DD-MON-YYYY') "to_dt", tsir_int_rate "interest_rate", tsir_from_dt "tsir_from_dt" from sme_ucp.T_SOA_INT_RATE_ucp, mflms.m_proj_info where tsir_cust_id = mpi_cust_id and tsir_con_id = mpi_con_id and tsir_proj_id = mpi_proj_id and mpi_legacy_id = '{#loan_account_no}' union select 'Rate Of interest applied ' "description", null cust_id, null con_id, null proj_id, null from_dt, null to_dt, null interest_rate, null tsir_from_dt from dual where not exists(select 'Rate Of interest applied ' "description", tsir_cust_id "cust_id", tsir_con_id "con_id", tsir_proj_id "proj_id", to_char(tsir_from_dt, 'DD-MON-YYYY') "from_dt", to_char(tsir_to_dt, 'DD-MON-YYYY') "to_dt", tsir_int_rate "interest_rate", tsir_from_dt "tsir_from_dt" from sme_ucp.T_SOA_INT_RATE_ucp, mflms.m_proj_info where tsir_cust_id = mpi_cust_id and tsir_con_id = mpi_con_id and tsir_proj_id = mpi_proj_id and mpi_legacy_id = '{#loan_account_no}') order by 8
vw_IDAL_New_SOAReport_QUERY=SELECT mlvq_cust_id "customer_id", mlvq_con_id "con_id", mlvq_proj_id "proj_id", mlvq_legacy_id "loan_account_no", mlvq_rec_st "loan_status", mflms.FN_GET_CUST_NAME(NULL, mlvq_legacy_id) "customer_name", mflms.FN_GET_CUST_INFO(MLVQ_CUST_ID, NULL, 'MN') "PHONE_NO", mflms.FN_GET_CUST_ADDRESS(NULL, mlvq_legacy_id, 'V', 'ADDRESS') "address", mflms.SP_FUNCTIONS.FN_GET_CSD_NAME(mflms.FN_GET_CUST_ADDRESS(NULL, mlvq_legacy_id, 'H', 'CITY'), 'C') "city", mflms.SP_FUNCTIONS.FN_GET_CSD_NAME(mflms.FN_GET_CUST_ADDRESS(NULL, mlvq_legacy_id, 'H', 'CITY'), 'S') "state", mflms.FN_GET_CUST_ADDRESS(NULL, mlvq_legacy_id, 'H', 'PINCODE') "pincode", mflms.FN_GET_CUST_ADDRESS(NULL, mlvq_legacy_id, 'H', 'CLUSTER') "ucic", TO_CHAR(pc_business_dt,'DD-MON-YYYY') "statement_issue_dt", UPPER('{#tsr_tran_dt}') || ' - ' || UPPER('{#tsr_value_dt}') "st_period", '{#tsr_tran_dt}' START_DATE, '{#tsr_value_dt}' END_DATE, mlvq_pos_cd "branch", mlvq_spec_desc "facility", mlvq_disb_dt "disbursal_date", TO_CHAR( mflms.FN_GET_EMI_DATE(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, 'F'), 'DD-MON-YYYY') "installment_start_date", TO_CHAR( mflms.FN_GET_EMI_DATE(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, 'L'), 'DD-MON-YYYY') "installment_end_date", mlvq_int_type "interest_rate_type", mlvq_proj_cost "amount_financed", mlvq_disb_amt "amount_disbursed", mlvq_repmt_tnr "total_tenure", DECODE(mlvq_repmt_frq,NULL,'Varying',mlvq_repmt_frq) "frequency_of_emi", mflms.FN_GET_EMI_AMOUNT(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, pc_business_dt) "emi_amount", mflms.FN_GET_BAL_ASON(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_tran_dt}', 'PRCLBAL') "bal_prin_os_from", mflms.FN_GET_PRIN_INT_PAID(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_tran_dt}', '{#tsr_value_dt}', 'PRIN') "principal_paid", mflms.FN_GET_PRIN_INT_PAID(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_tran_dt}', '{#tsr_value_dt}', 'INT') "interest_paid", mflms.FN_GET_PRIN_INT_PAID(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_tran_dt}', '{#tsr_value_dt}', 'PRININT') "total_prin_int_paid", mflms.FN_GET_BAL_ASON(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_value_dt}', 'PRCLBAL') "bal_prin_os_to", mflms.FN_GET_OS_BAL_ASON(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_value_dt}') + mflms.FN_GET_AFC_OS(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_value_dt}') + mflms.FN_GET_BOUNCING_CHARGES(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_tran_dt}', '{#tsr_value_dt}') "total_os_dues_as_on", mflms.FN_GET_OS_BAL_ASON(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_value_dt}') "total_installments_overdue", mflms.FN_GET_AFC_OS(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_value_dt}') "total_afc_os_charges", mflms.FN_GET_BOUNCING_CHARGES(mlvq_cust_id, mlvq_con_id, mlvq_proj_id, '{#tsr_tran_dt}', '{#tsr_value_dt}') "total_bouncing_charges", mpf_tot_os "total_os", CASE WHEN mflms.Fn_Get_Los_Details(mlvq_sanc_ref_no, 'CD') = 'SECURITY DEPOSIT' THEN mflms.Fn_Get_Los_Details(mlvq_sanc_ref_no, 'CV') ELSE 'N.A.' END "SD_collateral_value", /*NVL(TRIM(TO_CHAR(NVL(Fn_Get_Los_Details(mlvq_sanc_ref_no, 'CV'),0),'B99,999,999,999,999.99')), '0.00')*/ MPF_INST_TDS_DUE - MPF_TDS_CERT_AMT "TDS_Payable_amount" FROM mflms.m_loanbasic_view_query, mflms.m_proj_finbal_view, mflms.p_ctrl WHERE mlvq_cust_id = mpf_cust_id AND mlvq_con_id = mpf_con_id AND mlvq_proj_id = mpf_proj_id AND mlvq_legacy_id = '{#loan_account_no}'
IL_SOA_REPORTS_QUERY=select ACCOUNT as tnxSlNo , Receipt_Date as tnxDate , Due_Date as tnxValueDate , Receipt_No as tnxReference , Receipt_Amount as tnxAmount , Balance as tnxBalance , Particulars +' - '+ Instr_Details +' - '+ Instr_U_R +' - '+ Voucher_Description as tnxDtls FROM vw_IL_SOA_Loan_List_Details where Contract_NUMBER = '%s' order by Receipt_Date
IL_SOA_REPORTS_WHERE_CLAUSE=loanNumber
##IL_SOA_REPORT
vw_IL_SOA_Header_Details_QUERY= select * from vw_IL_SOA_Header_Details where contract_number ='{#ContractNo}'
vw_IL_SOA_Loan_List_Details_QUERY= select * from vw_IL_SOA_Loan_Account_Details where contract_number ='{#ContractNo}' order by Serial_No asc
vw_IL_SOA_ADDITIONAL_INTEREST_AFC_QUERY= select * from vw_IL_SOA_ADDITIONAL_INTEREST_AFC where contract_number ='{#ContractNo}'
vw_IL_SOA_CLOSED_CONTRACT_BALANCE_QUERY=Select * from vw_IL_SOA_CLOSED_CONTRACT_BALANCE where contract_number = '{#ContractNo}'
vw_IL_INTEREST_PAID_ON_SECURITY_DEPOSIT_QUERY= Select * from vw_IL_INTEREST_PAID_ON_SECURITY_DEPOSIT where contract_number = '{#ContractNo}'
vw_IL_SOA_Initial_Account_QUERY = Select * from vw_IL_SOA_Initial_Account where contract_number = '{#ContractNo}' order by Serial_No asc
##Product Type for loans
intellectProductType=Inventory Funding,Machinery Finance,Project finance,Secured Business Loan,Term Loan,Unsecured Loan,Working Capital Finance,Ad-hoc Inventory,Dealer unsecured loan,NA,New Equipment Finance,Old Equipment Finance,Pre-shipment Finance,STL-V,Working Capital Funding,World of SUV,ECLGS
fbtiProductType=Bill Discounting,Inventory Funding,Ad-hoc Inventory,Dealer unsecured loan,Invoice discounting,NA,Secured Business Loan,Term Loan,Working Capital Funding
instititionLendingProductType=PTC,TL
##Mandatory fields for detail services
LOAN_DETAIL_MANDATORY_FIELD=Loan_No
INTELLECT_REAL_TIME_LOAN_WHERE_CLAUSE=loanNumber
INTELLECT_SOA_REPORTS_WHERE_CLAUSE=loanNumber
COLLATERAL_DETAIL_MANDATORY_FIELD=CLUSTER_ID
CONFIG_MANDATORY_FIELD=configSystem
CONFIG_DETAIL_MANDATORY_FIELD=configCode
CONFIG_SYSTEM_MANDATORY_FIELD=PURPOSE_CODE
LOAN_LIST_INQUIRY_WHERE_CLAUSE=clusterID
INSTITITION_LENDING_PAYMENT_WHERE_CLAUSE=loanNumber
IL_REPAYMENT_SCHEDULE_WHERE_CLAUSE=loanNumber
INTELLECT_REPAYMENT_SCHEDULE_WHERE_CLAUSE=loanNumber
LOAN_LIST_REPORT_INQUIRY_WHERE_CLAUSE=CLUSTER_ID
vw_IDAL_SubSOA_Report_WHERE_CLAUSE=loan_account_no,tsr_tran_dt,tsr_value_dt
vw_IDAL_New_SOAReport_WHERE_CLAUSE=tsr_tran_dt,tsr_value_dt,loan_account_no
vw_IDAL_ACH_Bounced_WHERE_CLAUSE=loan_account_no,tsr_tran_dt,tsr_value_dt
vw_IDAL_PDC_Cheque_WHERE_CLAUSE=loan_account_no,tsr_tran_dt,tsr_value_dt
vw_IDAL_ROI_WHERE_CLAUSE=loan_account_no,tsr_tran_dt,tsr_value_dt
vw_IL_SOA_Loan_List_Details_WHERE_CLAUSE=ContractNo
vw_IL_SOA_Header_Details_WHERE_CLAUSE=ContractNo
vw_IL_SOA_ADDITIONAL_INTEREST_AFC_WHERE_CLAUSE=ContractNo
vw_IL_SOA_CLOSED_CONTRACT_BALANCE_WHERE_CLAUSE=ContractNo
vw_IL_INTEREST_PAID_ON_SECURITY_DEPOSIT_WHERE_CLAUSE=ContractNo
vw_IL_SOA_Initial_Account_WHERE_CLAUSE=ContractNo
##Driver class
sqlserver.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
spring.datasource.driverClassName=oracle.jdbc.OracleDriver
##Ehcache enable
ecache_enable = true
##Batch Table creation
##spring.batch.initialize-schema=always
##Soap Service
isWebServiceRequired = false
##Health Check required
dwh_health_Check=true
dms_health_Check=true
sms_health_Check=true
email_health_Check=true
crm_health_check=true
fbti_health_check=true
intellect_health_check=true
fd_health_check=false
##Health Check queries
dwh_health_check_query=SELECT 1+6 as value
fbti_health_check_query=select 1+6 as value from dual
intellect_health_check_query=select 1+6 as value from dual
dwh_health_check_query_result=7
fbti_health_check_query_result=7
intellect_health_check_query_result=7
##Health Check URL
dms_health_check_url=https://uatdms.mahindrafs.com/OmniDocsRestWS/
fd_health_check_url=https://esbuat.mmfsl.com/mahindrafinance/uat/fd/getaccountsummary
crm_health_check_url=172.30.9.85:8085
#sms_health_check_url=http://api.mvaayoo.com/mvaayooapi/MessageCompose
sms_health_check_url=http://crm.mahindrafs.com:9070/SMS_S/sms.aspx
email_health_check_url=172.30.2.6:25
#email_health_check_url=172.30.24.156:25 (28-08-2021 LAWRENCE)
##Health Check Email Messages
health_check_mail_trigger=true
heath_check_email_subject = MMFSL Middleware Health Check Status
heath_check_email_from [email protected]
##Service Request
isInternalServiceRequestEnquiryAllowed =false
##Job configurations
audit_archieve_enable_limit_in_months=1
audit_purge_enable_limit_in_months=1
##SOA Report miniStatement Months
months_durations=3
##SingleTon approach for Cahce
ifAnotherMWServerExsist=false
anotherMWServerIp=http://localhost:8022/
##Intellect limit Check URL
FG_INTELLECT_LIMIT_CHECK_URL=http://172.30.0.253:9084/MFINTF/MFBAL_Check_service?wsdl
FG_FBTI_QUERY_FOR_LIMIT=SELECT PRGID,limit_acct "limit_acct",facility_id "facility_id" ,cparty "cif_id" FROM portal_cparty_fetch WHERE cp_cluster='{#clusterId}' AND prgid ='{#programId}'
QUERY_TI_IF_DEALER_LIMIT_DETAILS=select CP_CLUSTER,PROGRAM_NAME,PROGRAM_ID,PROG_LIMIT_LMNO,PROG_REPAYMENT_ACCOUNT,SANCTIONED_LIMIT,UTILISATION,AVAILABLE_LIMIT,INTELLECT_LIMIT,OVER_DUE_AMT,CIA_OS_AMT,OVER_DUE_AGING,STATUS from ETT_IFOEM_DEALER_DB where trim(prg_type)='S' and (trim(PRG_CPARTY)='{#PRG_CPARTY}' OR trim(CP_CLUSTER)='{#CP_CLUSTER}')
QUERY_TI_BD_VENDOR_LIMIT_DETAILS=Select CP_CLUSTER,PROGRAM_NAME,PROGRAM_ID,PROG_LIMIT_LMNO,PROG_REPAYMENT_ACCOUNT,SANCTIONED_LIMIT,UTILISATION,AVAILABLE_LIMIT,OVER_DUE_AMT,OVER_DUE_AGING,STATUS from ETT_IFOEM_DB where trim (prg_type)='B' and (trim(PRG_CPARTY)='{#PRG_CPARTY}' OR trim(CP_CLUSTER)='{#CP_CLUSTER}')
###Block limit Queries
FG_UCP_SCF_FG_INVOICE=select Sum(Distinct(fg.invoice_amount)) "invoice_amount" from fg_invoice fg , fg_party party where NVL(fg.bo_status_code,'N') NOT IN('FINANCE', 'COMPLIANCE REJECTED', 'FAILED','SUCCESS') AND fg.status_code NOT IN ('RETURNED','REJECTED','NEW') AND (fg.vendor_code ='{#clusterId}' or( party.abbv_name = '{#clusterId}' and fg.counterparty_id = party.id ))AND fg.program_code='{#programId}'
FG_UCP_SCF_FG_ORDER=SELECT SUM(fg.order_amount) "invoice_amount" FROM fg_order fg, fg_party pt WHERE fg.counterparty_id = pt.id AND NVL(fg.status_code,'Null') NOT IN('RETURNED','REJECTED', 'FAILED' ,'Decline','COMPLIANCE REJECTED') AND pt.abbv_name ='{#clusterId}' AND fg.program_code ='{#programId}'
FG_UCP_INVOICE_DEALER=SELECT SUM(fg.invoice_amount) "invoice_amount" FROM fg_invoice fg , fg_party party WHERE NVL(fg.bo_status_code,'N') NOT IN('FINANCE', 'COMPLIANCE REJECTED', 'FAILED','SUCCESS') AND fg.status_code NOT IN ('RETURNED','REJECTED','NEW') AND (fg.vendor_code ='{#clusterId}' OR( party.abbv_name = '{#clusterId}' AND fg.counterparty_id = party.id )) AND INVOICE_NUMBER NOT IN (SELECT DISTINCT(INVOICE_REFERENCE_ID) FROM FG_PO_INVOICE_RELATION WHERE ORDER_REFERENCE_ID IN (SELECT fg.reference_id FROM fg_order fg, fg_party pt WHERE fg.counterparty_id = pt.id AND NVL(fg.status_code,'Null') IN('RETURNED','REJECTED', 'FAILED' ,'Decline','EXPIRED') AND pt.abbv_name ='{#clusterId}' AND fg.program_code ='{#programId}' ) ) AND fg.program_code ='{#programId}'
FG_UCP_SCF_FG_ORDER_DECLINE=SELECT sum(source_amount) "invoice_amount" FROM FG_PO_INVOICE_RELATION WHERE ORDER_REFERENCE_ID IN (SELECT fg.reference_id FROM fg_order fg, fg_party pt WHERE fg.counterparty_id = pt.id AND NVL(fg.status_code,'Null') NOT IN('RETURNED','REJECTED', 'FAILED' ,'Decline') AND pt.abbv_name ='{#clusterId}' AND fg.program_code ='{#programId}')
FG_UCP_SCF_FG_PARTIAL_DECLINE_AND_EXPIRED=SELECT (SELECT SUM(order_amount) FROM fg_order fg, fg_party pt WHERE fg.counterparty_id = pt.id AND NVL(fg.status_code,'Null') IN('Partial Decline','EXPIRED') AND pt.abbv_name ='{#clusterId}' AND fg.program_code ='{#programId}' ) - (SELECT SUM(source_amount) FROM FG_PO_INVOICE_RELATION WHERE ORDER_REFERENCE_ID IN (SELECT fg.reference_id FROM fg_order fg, fg_party pt WHERE fg.counterparty_id = pt.id AND NVL(fg.status_code,'Null') IN('Partial Decline','EXPIRED') AND pt.abbv_name ='{#clusterId}' AND fg.program_code ='{#programId}' ) ) "invoice_amount" from dual
FG_UCP_SCF_TI_COMPLAINCE_REJECTED_VENDOR_INVOICE=SELECT SUM(DISTINCT(fg.invoice_amount)) "invoice_amount" FROM fg_invoice fg , Fg_Party Party WHERE NVL(fg.bo_status_code,'N') IN('COMPLIANCE PENDING') AND fg.status_code NOT IN ('RETURNED','REJECTED','NEW') AND (fg.vendor_code ='{#clusterId}' OR( party.abbv_name = '{#clusterId}' And Fg.Counterparty_Id = Party.Id )) And Fg.Bo_Comments Not In ('Transaction went to Compliance check due to Overdue/ Non STP.','Drawing power Amount Exceeded;','Drawing power is not available;','DP is not Available;') AND fg.program_code ='{#programId}'
FG_UCP_SCF_TI_COMPLAINCE_REJECTED_DEALER_INVOICE=SELECT SUM(DISTINCT(fg.invoice_amount)) "invoice_amount" FROM fg_invoice fg , Fg_Party Party WHERE NVL(fg.bo_status_code,'N') IN('COMPLIANCE PENDING') AND fg.status_code NOT IN ('RETURNED','REJECTED','NEW') AND (fg.vendor_code ='{#clusterId}' OR( party.abbv_name = '{#clusterId}' And Fg.Counterparty_Id = Party.Id )) And Fg.Bo_Comments Not In ('Transaction went to Compliance check due to Overdue/ Non STP.','Drawing power Amount Exceeded;','Drawing power is not available;','DP is not Available;') AND INVOICE_NUMBER NOT IN (SELECT DISTINCT(INVOICE_REFERENCE_ID) FROM FG_PO_INVOICE_RELATION WHERE ORDER_REFERENCE_ID IN (SELECT fg.reference_id FROM fg_order fg, fg_party pt WHERE fg.counterparty_id = pt.id AND NVL(fg.status_code,'Null') IN('RETURNED','REJECTED', 'FAILED' ,'Decline','EXPIRED') AND pt.abbv_name ='{#clusterId}' AND fg.program_code ='{#programId}' ) )
FG_UCP_SCF_TI_COMPLAINCE_REJECTED_DEALER_ORDER=SELECT SUM(fg.order_amount) "invoice_amount" FROM fg_order fg, fg_party pt Where Fg.Counterparty_Id = Pt.Id AND NVL(fg.status_code,'Null') IN('COMPLIANCE PENDING') AND pt.abbv_name ='{#clusterId}' And Fg.Program_Code ='{#programId}' AND Fg.Bo_Comments NOT IN ('Transaction went to Compliance check due to Overdue/ Non STP.','Drawing power Amount Exceeded;','Drawing power is not available;','DP is not Available;')
## AND fg.reference_id = relation.order_reference_id - SUM(relation.source_amount)
##DefaultSize of the List
SIZE=1000
# Logging pattern for the console
logging.pattern.console= "%d{yyyy-MM-dd HH:mm:ss} %M - %msg%n"
# Logging pattern for file
logging.pattern.file= "%d{yyyy-MM-dd HH:mm:ss} [%thread] %-5level %logger{36} %M - %msg%n"
logging.file=middlewareLog/Middleware.log
logging.file.max-history=7
logging.file.max-size=10MB
logging.level.org.springframework.web=DEBUG
logging.level.org.springframework.ws.client.MessageTracing.sent=DEBUG
logging.level.org.springframework.ws.server.MessageTracing.sent=DEBUG
logging.level.org.springframework.ws.client.MessageTracing.received=TRACE
logging.level.org.springframework.ws.server.MessageTracing.received=TRACE
## Spring thymleaf properties
spring.thymeleaf.prefix=/app/
spring.thymeleaf.suffix=.html
spring.thymeleaf.mode=HTML5
spring.thymeleaf.encoding=UTF-8
spring.thymeleaf.servlet.content-type=text/html
spring.servlet.multipart.enabled=true
# Threshold after which files are written to disk.
spring.servlet.multipart.file-size-threshold=2KB
# Max file size.
spring.servlet.multipart.max-file-size=200MB
# Max Request Size
spring.servlet.multipart.max-request-size=215MB
## Mock responce for UCP
mock_service_response=false
spring.main.allow-bean-definition-overriding=true
##FD client key(Enable in PRE-PRODUCTION Only)
#fd_client_key=34825cb4-602d-4f42-860c-1c0ad4ba499a
fd_client_key=135ef1fd-5281-4a9f-9119-967f8ed2f45e
#BD SOA Report
BD_SOA_REPORT_QUERY = SELECT \
ESA.CPARTYNAME AS CUSTOMER_NAME,\
EAC.CUSTOMERMNEMONIC AS CUSTOMER_ID,\
ESA.SVNA2 AS CUSTOMER_ADDRESS,\
ESA.SVNA3 AS CITY,\
ESA.SVNA4 AS STATE,\
ESA.SVPZIP AS PIN_CODE,\
ESA.PHONE AS PHONE_NO,\
'{#fromDate}' AS STATEMENT_PRD_FROM,\
'{#toDate}' AS STATEMENT_PRD_TO,\
ESA.REL_NAME AS RM_NAME,\
ESA.REL_PHONE AS RM_PHONE,\
ESA.REL_EMAIL AS RM_EMAIL,\
ESA.CUST_SERV_EMAIL AS CUSTOMER_SERV_EMAIL,\
EAC.REPAYMENT_ACC_NO AS REPAYMENT_ACC_NO,\
EAC.REPAYMENT_BANK AS REPAYMENT_BANK,\
EAC.REPAYMENT_BRNCH AS REPAYMENT_BRNCH,\
EAC.REPAYMENT_BRN_IFSC_CODE AS REPAYMENT_BRN_IFSC_CODE,\
ESA.REL_NAME AS RM_NAME_1,\
EAC.FACILITY AS FACILITY_TYPE,\
EAC.INTEREST_RATE_TYPE AS INTEREST_RATE_TYPE,\
EAC.INTEREST_RATE AS INTEREST_RATE,\
EAC.INTEREST_RATE_1 AS INTEREST_RATE_1,\
EAC.INTEREST_RATE_2 AS INTEREST_RATE_2,\
EAC.INTEREST_POST_DAYS AS INTEREST_POST_DAYS,\
EAC.INTEREST_POST_DAYS_1 AS INTEREST_POST_DAYS_1,\
EAC.EFFECTIVE_DATE AS EFFECTIVE_DATE,\
EAC.EFFECTIVE_DATE_1 AS EFFECTIVE_DATE_1,\
EAC.EFFECTIVE_DATE_2 AS EFFECTIVE_DATE_2,\
EAC.PRG_ID AS PROGRAM_ID,\
EAC.PRG_NAME AS PROGRAM_NAME,\
ESA.UCIC AS UCIC,\
EAC.LIMIT_EXP_DATE AS LIMIT_EXP_DATE,\
NVL(EAC.PRG_SANC_LM,0) AS SANCTIONED_LIMIT,\
NVL(EAC.UTILISED_AMT,0) AS UTILISED_LIMIT,\
EAC.TOTALAMOUNT AS TOTAL_OUTSTANDING, \
(NVL(EAC.PRG_SANC_LM,0) - NVL(EAC.UTILISED_AMT,0)) AS AVAILABLE_LIMIT,\
EAC.LIMIT_STATUS AS LIMIT_STATUS,\
EAC.TOT_DIB_AMT AS GROSS_DISBURSEMENT,\
(EAC.TOT_INT_REPAY + EAC.TOT_PEN_REPAY) AS PENAL_AND_INTEREST_REPAYMENT,\
EAC.TOT_PRIN_REPAY AS PRINCIPAL_REPAYMENT,\
EAC.TOT_REFUND_AMT AS REFUND,\
(EAC.TOT_DIB_AMT - EAC.TOT_PRIN_REPAY) AS POS,\
CASE WHEN (SOD.INTDATE IS NULL OR SOD.INTDATE=TO_DATE('{#toDate}','DD-MM-YYYY')) THEN NVL(SOD.PRINCIPAL_OD,0) ELSE 0 END AS TOT_PRIN_OVERDUE,\
(CASE WHEN (SOD.INTDATE IS NULL OR SOD.INTDATE=TO_DATE('{#toDate}','DD-MM-YYYY')) THEN NVL(SOD.INTEREST_OD,0) ELSE 0 END \
+ \
CASE WHEN (SOD.INTDATE IS NULL OR SOD.INTDATE=TO_DATE('{#toDate}','DD-MM-YYYY')) THEN NVL(SOD.PENAL_OD,0) ELSE 0 END) AS \ TOT_PENAL_AND_INTEREST_OVERDUE,\
CASE WHEN (SOD.INTDATE IS NULL OR SOD.INTDATE=TO_DATE('{#toDate}','DD-MM-YYYY')) THEN NVL(SOD.PRINCIPAL_OD,0) + NVL(SOD.INTEREST_OD,0)+ NVL(SOD.PENAL_OD,0)+NVL(SOD.CHARGE_OD,0)\
ELSE 0 END AS TOTAL_OS,\
CASE WHEN (SELECT DLY.PROCDATE FROM DLYPRCCYCL DLY) > EAC.DUE_DATE THEN ((SELECT DLY.PROCDATE FROM DLYPRCCYCL DLY) - \ EAC.DUE_DATE)\
ELSE 0 END AS PEAK_DPD \
FROM BD_ETT_ACC_STATEMENT EAC,\
ETT_SCF_ADDRESS ESA,\
ETT_ACC_STATEMENT_OD_VIEW SOD \
WHERE EAC.PRG_ID =ESA.PRG \
AND EAC.CUSTOMERMNEMONIC =ESA.CUSTOMER \
AND (SOD.INTDATE IS NULL OR SOD.INTDATE=TO_DATE('{#toDate}','DD-MM-YYYY')) \
AND EAC.CUSTOMERMNEMONIC =SOD.CPARTY (+) \
AND EAC.PRG_ID =SOD.PROGRAM_ID (+)\
AND (EAC.CUSTOMERMNEMONIC)='{#CP_CIF_ID}' \
AND (EAC.PRG_ID) = '{#programCode}' \
AND EAC.POSTDATE >= TO_DATE('{#fromDate}','DD-MM-YYYY') AND EAC.POSTDATE <=TO_DATE('{#toDate}','DD-MM-YYYY') \
ORDER BY EAC.POSTDATE,EAC.VALUEDATE ASC \
BD_SOA_TABLE_REPORT_QUERY = SELECT \
EAC.POSTDATE AS DTE,\
EAC.EVENT AS PARTICULARS,\
EAC.VALUEDATE AS VALUE_DATE,\
EAC.PAYMENT_REF_NO AS PAYMENT_REFERENCE_NO,\
EAC.DEBIT AS DEBIT,\
EAC.CREDIT AS CREDIT,\
EAC.TOTALAMOUNT AS TOTAL_OUTSTANDING \
FROM BD_ETT_ACC_STATEMENT EAC,\
ETT_SCF_ADDRESS ESA,\
ETT_ACC_STATEMENT_OD_VIEW SOD \
WHERE EAC.PRG_ID =ESA.PRG \
AND EAC.CUSTOMERMNEMONIC =ESA.CUSTOMER \
AND (SOD.INTDATE IS NULL OR SOD.INTDATE=TO_DATE('{#toDate}','DD-MM-YYYY')) \
AND EAC.CUSTOMERMNEMONIC =SOD.CPARTY (+) \
AND EAC.PRG_ID =SOD.PROGRAM_ID (+) \
AND (EAC.CUSTOMERMNEMONIC)='{#CP_CIF_ID}' \
AND (EAC.PRG_ID) = '{#programCode}' \
AND EAC.POSTDATE >= TO_DATE('{#fromDate}','DD-MM-YYYY') AND EAC.POSTDATE <=TO_DATE('{#toDate}','DD-MM-YYYY') \
ORDER BY EAC.POSTDATE,EAC.VALUEDATE ASC \
BD_SOA_CIF_REPORT_QUERY = select distinct(cparty) AS CP_CIF_ID from portal_cparty_fetch where Cp_Cluster='{#CLUSTERID}'
BD_SOA_REPORT_WHERE_CLAUSE = fromDate,toDate,CLUSTERID,programCode
###### DMS NEW API #######################
DMS_SERVICE_UPLOAD_LAYOUT={"columns":[{"key":"1","value":"Cluster_ID","required":"true","defaultValue":" "},{"key":"2","value":"VERTICAL_NAME","defaultValue":"SME"},{"key":"3","value":"Document_Type","required":"true","defaultValue":" "},{"key":"4","value":"Document_Sub_Type","defaultValue":" "},{"key":"5","value":"Customer_ID","required":"true","defaultValue":" "},{"key":"6","value":"Customer_Name","required":"true","defaultValue":" "},{"key":"7","value":"Product_Type","required":"true","defaultValue":" "},{"key":"8","value":"Product_Sub_Type","required":"true","defaultValue":" "},{"key":"9","value":"TYPE","defaultValue":" "},{"key":"10","value":"SUB_TYPE","defaultValue":" "},{"key":"11","value":"Created_On","defaultValue":"sysdate"},{"key":"12","value":"Created_By","required":"true","defaultValue":" "},{"key":"13","value":"CreatedByEMPCode","defaultValue":"MMFSL"},{"key":"14","value":"DocumentComments","defaultValue":" "},{"key":"15","value":"Reviewed_On","defaultValue":"sysdate"},{"key":"16","value":"Reviewed_By","defaultValue":"UCP"},{"key":"17","value":"ReviewedByEMPCode","defaultValue":"UCP"},{"key":"18","value":"Reviewer_Remarks","defaultValue":" "},{"key":"19","value":"isActive","required":"true","defaultValue":"true"},{"key":"20","value":"uploadStatus","required":"true","defaultValue":"true"},{"key":"21","value":"Loan_Number","defaultValue":" "},{"key":"22","value":"FINANCIAL_YEAR","defaultValue":" "},{"key":"24","value":"System_Ref","defaultValue":" "},{"key":"27","value":"Pan","defaultValue":" "},{"key":"28","value":"DrivingLicense_Number","defaultValue":" "},{"key":"29","value":"Passport_Number","defaultValue":" "},{"key":"30","value":"Voter_ID","defaultValue":" "},{"key":"31","value":"Document_IssueDate","defaultValue":"sysdate"},{"key":"32","value":"Document_ExpiryDate","defaultValue":"sysdate"},{"key":"33","value":"Source_System","required":"true","defaultValue":"UCP"},{"key":"34","value":"Source_AuthEmail","required":"true","defaultValue":"[email protected]"},{"key":"35","value":"documentName","required":"true","defaultValue":""}]}
DMS_SERVICE_URL_UPLOAD=https://api-uat.mmfsl.com/dms/v1/documents
DMS_SERVICE_URL_TOKEN=https://api-uat.mmfsl.com/oauth/cc/v1/token
DMS_SERVICE_URL_DOWNLOAD=https://api-uat.mmfsl.com/dms/v1/documents/$DOCUMENT_ID
ACCESS_TOKEN_SERVICE_AUTHORIZATION=Basic c0VxZjJTY3JHNDg5aVp4dWxIeTduZWZJSHNGWG5XTURQODdaczhEWEE1TktVU1dBOnN2VDJMN050V212QUgzT28zZEFCWmlsMWNZU2pyYjY0azRMY3loTUYyYUVBQ092VzdHQU5zQVp4QXFsOU01UmY=
#DMS Default Value:
Business_Vertical=DEMO_VERTICALS