· 6 years ago · May 06, 2019, 03:30 AM
1USE [Ad1sys_Development_Pusat]
2GO
3/****** Object: StoredProcedure [dbo].[PR_SUM_DEFERRED_INT_REPORT] Script Date: 05/03/2019 15:14:46 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8ALTER PROCEDURE [dbo].[PR_SUM_DEFERRED_INT_REPORT]
9--DECLARE
10 @P_BANKID VARCHAR(4) ,
11 @P_CABANG VARCHAR(4) ,
12 @P_STARTDATE VARCHAR(12) ,
13 @P_ENDDATE VARCHAR(12),
14 @P_NIK VARCHAR(25)
15AS
16/************************************************************************
17
18 NAMA : PR_SUM_DEFERRED_INT_REPORT
19 TUJUAN : UNTUK MENAMPILKAN REPORT SUMMARY DEFERRED INTEREST DARI EARLY PAYMENT
20
21 REVISIONS:
22 VER DATE AUTHOR DESCRIPTION
23 --------- ---------- --------------- ---------------------------
24 1.0 02/05/2019 FAUZAN A UMSOHI Create new procedure
25
26 NOTES:
27
28 AUTOMATICALLY AVAILABLE AUTO REPLACE KEYWORDS:
29
30 TABLE NAME:
31
32 ************************************************************************/
33
34 DECLARE @V_STARTDATE DATE ,
35 @V_LASTDATE DATE ,
36 @V_STARTDATEP DATE
37
38--SET @P_STARTDATE = '01-APR-2019'
39--SET @P_ENDDATE = '01-APR-2019'
40--SET @P_CABANG = '0000'
41--SET @P_BANKID = '124'
42 DECLARE @V_NIK VARCHAR(15)
43 SET @V_STARTDATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE, @P_STARTDATE, 12)) - 1, 0)
44 SET @V_LASTDATE = DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE, @P_STARTDATE, 12)), -1) + 1
45 SET @V_STARTDATEP = DATEADD(MONTH, DATEDIFF(MONTH, 0, CONVERT(DATE, @P_STARTDATE, 12)) - 1, 0)
46
47 SET @V_NIK = ( SELECT MAX(NIK)
48 FROM dbo.TBL_NIK_AD1REPORT
49 WHERE NIK = @P_NIK
50 )
51 IF @V_NIK IS NULL
52 BEGIN
53 RAISERROR ( 'Mohon Untuk Menarik Report Ini Dari Aplikasi', 16, 1 )
54 GOTO outSP
55 END
56
57 ELSE
58 BEGIN
59
60 IF ( OBJECT_ID('TEMPDB..##JFDTL') IS NOT NULL )
61 DROP TABLE ##JFDTL
62 IF ( OBJECT_ID('TEMPDB..##RPTJF') IS NOT NULL )
63 DROP TABLE ##RPTJF
64
65 SELECT PERIODE ,
66 BRANCHID ,
67 CONTRACTNO ,
68 KEGIATAN_USAHA ,
69 CARA_PEMBIAYAAN ,
70 JENIS_ANGSURAN ,
71 PC_CODE ,
72 INSTL_NO ,
73 JML_INSTL ,
74 INSTL_DATE ,
75 INST_DATE_PREV ,
76 PAID_DATE ,
77 JML_HARI_DITANGHKN ,
78 SELISIH_INST_DATE ,
79 POKOK_AR ,
80 POKOK_AP ,
81 BUNGA_AR ,
82 BUNGA_AP ,
83 BUNGA_AR_DITANGHKN ,
84 BUNGA_AP_DITANGHKN ,
85 bunga_ar_ditanghkn - BUNGA_AP_ditanghkn NET_BUNGA_DITAHGGUHKAN ,
86 BANK_ID ,
87 OBJT_CODE ,
88 OBJT_GROUP_CODE
89 INTO ##JFDTL
90 FROM ( SELECT PERIODE ,
91 BRANCHID ,
92 CONTRACTNO ,
93 KEGIATAN_USAHA ,
94 CARA_PEMBIAYAAN ,
95 JENIS_ANGSURAN ,
96 PC_CODE ,
97 INSTL_NO ,
98 JML_INSTL ,
99 INSTL_DATE ,
100 INST_DATE_PREV ,
101 PAID_DATE ,
102 JML_HARI_DITANGHKN ,
103 SELISIH_INST_DATE ,
104 POKOK_AR ,
105 POKOK_AP ,
106 BUNGA_AR ,
107 BUNGA_AP ,
108 BUNGA_AR_DITANGHKN ,
109 CASE WHEN JML_HARI_DITANGHKN < ACTUAL_DAYS THEN ( BUNGA_AP * JML_HARI_DITANGHKN ) / SELISIH_INST_DATE
110 ELSE BUNGA_AP
111 END BUNGA_AP_ditanghkn ,
112 BANK_ID ,
113 OBJT_CODE ,
114 OBJT_GROUP_CODE
115 FROM ( SELECT TEP.PERIODE ,
116 TEP.BRANCHID ,
117 TEP.CONTRACTNO ,
118 TEP.KEGIATAN_USAHA ,
119 TEP.CARA_PEMBIAYAAN ,
120 TEP.JENIS_ANGSURAN ,
121 TEP.PC_CODE ,
122 TEP.INSTL_NO ,
123 TEP.JML_INSTL ,
124 TEP.INSTL_DATE ,
125 TEP.INST_DATE_PREV ,
126 TEP.PAID_DATE ,
127 TEP.JML_HARI_DITANGHKN ,
128 TEP.SELISIH_INST_DATE ,
129 TEP.POKOK_AR ,
130 CASE WHEN POKOK_AR < 0 THEN ISNULL(FAS.FUND_BANK_INST_PRIN, 0) * -1
131 ELSE ISNULL(FAS.FUND_BANK_INST_PRIN, 0)
132 END POKOK_AP ,
133 TEP.BUNGA_AR ,
134 CASE WHEN BUNGA_AR < 0 THEN ISNULL(FAS.FUND_BANK_INST_INTR, 0) * -1
135 ELSE ISNULL(FAS.FUND_BANK_INST_INTR, 0)
136 END BUNGA_AP ,
137 BUNGA_AR_DITANGHKN ,
138 DAY(DATEADD(d, -( DAY(GETDATE()) - 1 ), GETDATE()) - 1) ACTUAL_DAYS ,
139 FAM.FUND_BANK_ID BANK_ID ,
140 TEP.OBJT_CODE ,
141 TEP.OBJT_GROUP_CODE
142 --SELECT *
143 FROM TBL_STG_ADVANCE_PAYMENT_HQ TEP ( NOLOCK )
144 INNER JOIN FUND_ASSIGN_MASTER FAM ( NOLOCK ) ON FAM.FUND_CONT_NO = TEP.CONTRACTNO
145 AND FAM.FUND_BR_ID = TEP.BRANCHID
146 INNER JOIN PARA_BANK PB ( NOLOCK ) ON PB.BANK_ID = FAM.FUND_BANK_ID
147 LEFT JOIN FUND_AP_SCHED FAS ( NOLOCK ) ON FAS.FUND_BR_ID = TEP.BRANCHID
148 AND FAS.FUND_CONT_NO = TEP.CONTRACTNO
149 AND FAS.FUND_INST_NO = TEP.INSTL_NO
150 WHERE PB.BANK_GROUP IN ( '014', '046' )
151 AND EXISTS ( SELECT 1
152 FROM FUND_CONT_HISTORY FCH ( NOLOCK )
153 WHERE FCH.FUND_BR_ID = TEP.BRANCHID
154 AND FCH.FUND_CONT_NO = TEP.CONTRACTNO
155 AND FCH.FUND_INST_NO = TEP.INSTL_NO
156 AND fch.FUND_BANK_REF_DATE >= CONVERT(DATE, DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0)) --'01-jan-2017' --selalu jan tahun penarikan
157 AND fch.FUND_BANK_REF_DATE < CONVERT(DATE, DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)) )
158 ) B
159 ) C
160
161 SELECT a.*
162 INTO ##RPTJF
163 FROM ( SELECT PERIODE ,
164 BRANCHID ,
165 CONTRACTNO ,
166 KEGIATAN_USAHA ,
167 CARA_PEMBIAYAAN ,
168 JENIS_ANGSURAN ,
169 PC_CODE ,
170 INSTL_NO ,
171 JML_INSTL ,
172 INSTL_DATE ,
173 INST_DATE_PREV ,
174 PAID_DATE ,
175 JML_HARI_DITANGHKN ,
176 SELISIH_INST_DATE ,
177 POKOK_AR ,
178 POKOK_AP ,
179 BUNGA_AR ,
180 BUNGA_AP ,
181 BUNGA_AR_DITANGHKN ,
182 BUNGA_AP_DITANGHKN ,
183 bunga_ar_ditanghkn - BUNGA_AP_ditanghkn NET_BUNGA_DITAHGGUHKAN ,
184 BANK_ID ,
185 OBJT_CODE ,
186 OBJT_GROUP_CODE
187 FROM ( SELECT PERIODE ,
188 BRANCHID ,
189 CONTRACTNO ,
190 KEGIATAN_USAHA ,
191 CARA_PEMBIAYAAN ,
192 JENIS_ANGSURAN ,
193 PC_CODE ,
194 INSTL_NO ,
195 JML_INSTL ,
196 INSTL_DATE ,
197 INST_DATE_PREV ,
198 PAID_DATE ,
199 JML_HARI_DITANGHKN ,
200 SELISIH_INST_DATE ,
201 POKOK_AR ,
202 POKOK_AP ,
203 BUNGA_AR ,
204 BUNGA_AP ,
205 BUNGA_AR_DITANGHKN ,
206 CASE WHEN JML_HARI_DITANGHKN < ACTUAL_DAYS THEN ( BUNGA_AP * JML_HARI_DITANGHKN ) / SELISIH_INST_DATE
207 ELSE BUNGA_AP
208 END BUNGA_AP_ditanghkn ,
209 BANK_ID ,
210 OBJT_CODE ,
211 OBJT_GROUP_CODE
212 FROM ( SELECT TEP.PERIODE ,
213 TEP.BRANCHID ,
214 TEP.CONTRACTNO ,
215 TEP.KEGIATAN_USAHA ,
216 TEP.CARA_PEMBIAYAAN ,
217 TEP.JENIS_ANGSURAN ,
218 TEP.PC_CODE ,
219 TEP.INSTL_NO ,
220 TEP.JML_INSTL ,
221 TEP.INSTL_DATE ,
222 TEP.INST_DATE_PREV ,
223 TEP.PAID_DATE ,
224 TEP.JML_HARI_DITANGHKN ,
225 TEP.SELISIH_INST_DATE ,
226 TEP.POKOK_AR ,
227 0 POKOK_AP ,
228 TEP.BUNGA_AR ,
229 0 BUNGA_AP ,
230 BUNGA_AR_DITANGHKN ,
231 DAY(DATEADD(d, -( DAY(GETDATE()) - 1 ), GETDATE()) - 1) ACTUAL_DAYS ,
232 FAM.FUND_BANK_ID BANK_ID ,
233 TEP.OBJT_CODE ,
234 TEP.OBJT_GROUP_CODE
235 FROM TBL_STG_ADVANCE_PAYMENT_HQ TEP ( NOLOCK )
236 LEFT JOIN FUND_ASSIGN_MASTER FAM ( NOLOCK ) ON FAM.FUND_CONT_NO = TEP.CONTRACTNO
237 AND FAM.FUND_BR_ID = TEP.BRANCHID
238 WHERE NOT EXISTS ( SELECT 1
239 FROM ##JFDTL A
240 WHERE A.CONTRACTNO = TEP.CONTRACTNO
241 AND A.INSTL_NO = TEP.INSTL_NO
242 AND A.BRANCHID = TEP.BRANCHID )
243 ) B
244 ) C
245 UNION ALL
246 SELECT *
247 FROM ##JFDTL
248 ) a
249
250 SELECT MTR.FUND_PARA_BANK_NAME ,
251 MTR.FUND_PARA_BANK_ID ,
252 ISNULL(MAX(MTR.NOA), 0) NOA_MOTOR ,
253 ISNULL(MAX(MTR.DEF_INTRM), 0) DEF_INTRM_MOTOR ,
254 ISNULL(MAX(MTR.NOA_PREV), 0) NOA_PREV_MOTOR ,
255 ISNULL(MAX(MTR.DEF_INTRM_PREV), 0) DEF_INTRM_PREV_MOTOR ,
256 ISNULL(MAX(MBL.NOA), 0) NOA_MOBIL ,
257 ISNULL(MAX(MBL.DEF_INTRM), 0) DEF_INTRM_MOBIL ,
258 ISNULL(MAX(MBL.NOA_PREV), 0) NOA_PREV_MOBIL ,
259 ISNULL(MAX(MBL.DEF_INTRM_PREV), 0) DEF_INTRM_PREV_MOBIL,
260 ISNULL(MAX(DRB.NOA), 0) NOA_DURABLE ,
261 ISNULL(MAX(DRB.DEF_INTRM), 0) DEF_INTRM_DURABLE ,
262 ISNULL(MAX(DRB.NOA_PREV), 0) NOA_PREV_DURABLE ,
263 ISNULL(MAX(DRB.DEF_INTRM_PREV), 0) DEF_INTRM_PREV_DURABLE
264 FROM ( SELECT COUNT(1) NOA ,
265 SUM(A.BUNGA_AP_DITANGHKN) DEF_INTRM ,
266 0 NOA_PREV ,
267 0 DEF_INTRM_PREV ,
268 A.OBJT_GROUP_CODE ,
269 FPI.FUND_PARA_BANK_NAME ,
270 FPI.FUND_PARA_BANK_ID
271 FROM ##RPTJF A
272 INNER JOIN FUND_PARA_BANK_INFO (NOLOCK) fpi ON fpi.FUND_PARA_BR_ID = a.branchid
273 AND fpi.FUND_BANK_ID = a.bank_id
274 WHERE A.BANK_ID = @P_BANKID
275 AND A.PERIODE >= @V_STARTDATE
276 AND A.PERIODE < @V_LASTDATE
277 AND A.OBJT_GROUP_CODE = '001'
278 GROUP BY A.OBJT_GROUP_CODE ,
279 FPI.FUND_PARA_BANK_NAME ,
280 FPI.FUND_PARA_BANK_ID
281 UNION ALL
282 SELECT 0 ,
283 0 ,
284 COUNT(1) NOA ,
285 SUM(A.BUNGA_AP_DITANGHKN) DEF_INTRM ,
286 A.OBJT_GROUP_CODE ,
287 FPI.FUND_PARA_BANK_NAME ,
288 FPI.FUND_PARA_BANK_ID
289 FROM ##RPTJF A
290 INNER JOIN FUND_PARA_BANK_INFO (NOLOCK) fpi ON fpi.FUND_PARA_BR_ID = a.branchid
291 AND fpi.FUND_BANK_ID = a.bank_id
292 WHERE A.BANK_ID = @P_BANKID
293 AND A.PERIODE >= @V_STARTDATEP
294 AND A.PERIODE < @V_STARTDATE
295 AND A.OBJT_GROUP_CODE = '001'
296 GROUP BY A.OBJT_GROUP_CODE ,
297 FPI.FUND_PARA_BANK_NAME ,
298 FPI.FUND_PARA_BANK_ID
299 ) MTR
300 LEFT JOIN ( SELECT COUNT(1) NOA ,
301 SUM(A.BUNGA_AP_DITANGHKN) DEF_INTRM ,
302 0 NOA_PREV ,
303 0 DEF_INTRM_PREV ,
304 A.OBJT_GROUP_CODE ,
305 FPI.FUND_PARA_BANK_NAME ,
306 FPI.FUND_PARA_BANK_ID
307 FROM ##RPTJF A
308 INNER JOIN FUND_PARA_BANK_INFO (NOLOCK) fpi ON fpi.FUND_PARA_BR_ID = a.branchid
309 AND fpi.FUND_BANK_ID = a.bank_id
310 WHERE A.BANK_ID = @P_BANKID
311 AND A.PERIODE >= @V_STARTDATE
312 AND A.PERIODE < @V_LASTDATE
313 AND A.OBJT_GROUP_CODE = '002'
314 GROUP BY A.OBJT_GROUP_CODE ,
315 FPI.FUND_PARA_BANK_NAME ,
316 FPI.FUND_PARA_BANK_ID
317 UNION ALL
318 SELECT 0 ,
319 0 ,
320 COUNT(1) NOA ,
321 SUM(A.BUNGA_AP_DITANGHKN) DEF_INTRM ,
322 A.OBJT_GROUP_CODE ,
323 FPI.FUND_PARA_BANK_NAME ,
324 FPI.FUND_PARA_BANK_ID
325 FROM ##RPTJF A
326 INNER JOIN FUND_PARA_BANK_INFO (NOLOCK) fpi ON fpi.FUND_PARA_BR_ID = a.branchid
327 AND fpi.FUND_BANK_ID = a.bank_id
328 WHERE A.BANK_ID = @P_BANKID
329 AND A.PERIODE >= @V_STARTDATEP
330 AND A.PERIODE < @V_STARTDATE
331 AND A.OBJT_GROUP_CODE = '002'
332 GROUP BY A.OBJT_GROUP_CODE ,
333 FPI.FUND_PARA_BANK_NAME ,
334 FPI.FUND_PARA_BANK_ID
335 ) MBL ON MTR.FUND_PARA_BANK_NAME = MBL.FUND_PARA_BANK_NAME
336 AND MTR.FUND_PARA_BANK_ID = MBL.FUND_PARA_BANK_ID
337 LEFT JOIN ( SELECT COUNT(1) NOA ,
338 SUM(A.BUNGA_AP_DITANGHKN) DEF_INTRM ,
339 0 NOA_PREV ,
340 0 DEF_INTRM_PREV ,
341 A.OBJT_GROUP_CODE ,
342 FPI.FUND_PARA_BANK_NAME ,
343 FPI.FUND_PARA_BANK_ID
344 FROM ##RPTJF A
345 INNER JOIN FUND_PARA_BANK_INFO (NOLOCK) fpi ON fpi.FUND_PARA_BR_ID = a.branchid
346 AND fpi.FUND_BANK_ID = a.bank_id
347 WHERE A.BANK_ID = @P_BANKID
348 AND A.PERIODE >= @V_STARTDATE
349 AND A.PERIODE < @V_LASTDATE
350 AND A.OBJT_GROUP_CODE = '003'
351 GROUP BY A.OBJT_GROUP_CODE ,
352 FPI.FUND_PARA_BANK_NAME ,
353 FPI.FUND_PARA_BANK_ID
354 UNION ALL
355 SELECT 0 ,
356 0 ,
357 COUNT(1) NOA ,
358 SUM(A.BUNGA_AP_DITANGHKN) DEF_INTRM ,
359 A.OBJT_GROUP_CODE ,
360 FPI.FUND_PARA_BANK_NAME ,
361 FPI.FUND_PARA_BANK_ID
362 FROM ##RPTJF A
363 INNER JOIN FUND_PARA_BANK_INFO (NOLOCK) fpi ON fpi.FUND_PARA_BR_ID = a.branchid
364 AND fpi.FUND_BANK_ID = a.bank_id
365 WHERE A.BANK_ID = @P_BANKID
366 AND A.PERIODE >= @V_STARTDATEP
367 AND A.PERIODE < @V_STARTDATE
368 AND A.OBJT_GROUP_CODE = '003'
369 GROUP BY A.OBJT_GROUP_CODE ,
370 FPI.FUND_PARA_BANK_NAME ,
371 FPI.FUND_PARA_BANK_ID
372 ) DRB ON MTR.FUND_PARA_BANK_NAME = MBL.FUND_PARA_BANK_NAME
373 AND MTR.FUND_PARA_BANK_ID = MBL.FUND_PARA_BANK_ID
374 GROUP BY MTR.FUND_PARA_BANK_NAME ,
375 MTR.FUND_PARA_BANK_ID
376 ORDER BY 1 ,
377 2
378
379 END
380 --DELETE FROM dbo.TBL_NIK_AD1REPORT
381 -- WHERE NIK = @P_NIK
382 outSP: