· 8 years ago · Feb 02, 2018, 04:02 AM
1CREATE OR REPLACE FUNCTION f_crosscheck_cashbank_docs(character varying, bigint, bigint, bigint, character varying, character varying)
2 RETURNS void AS
3$BODY$
4DECLARE
5 pSessionId ALIAS FOR $1;
6 pTenantId ALIAS FOR $2;
7 pOuId ALIAS FOR $3;
8 pCashbankId ALIAS FOR $4; -- bisa -99
9 pDateFrom ALIAS FOR $5;
10 pDateTo ALIAS FOR $6;
11
12 vStatusRelease character varying := 'R';
13 vRecTypeDebit character varying := 'D';
14 vRecTypeKredit character varying := 'K';
15 vTypeIn character varying := 'I';
16 vTypeOut character varying := 'O';
17 vStatusAccept character varying := 'ACCEPT';
18
19 vEmptyId bigint := -99;
20 vDocTypeCBOut bigint := 611;
21 vDocTypeFollowUpCashAdvanceSettlement bigint := 641;
22 vDocTypeCBTransfer bigint := 631;
23 vDocTypeCBInPartner bigint := 621;
24 vDocTypeCBInOther bigint := 623;
25 vDocTypeCGRealization bigint := 625;
26 vDocTypeEDCSettlement bigint := 622;
27 vDocTypePOSShop bigint := 401;
28 vDocTypeReturnPOSShop bigint := 402;
29
30 vFilterCashbankIdA text := '';
31 vFilterCashbankIdB text := '';
32 vFilterCashbankIdD text := '';
33 vFilterCashbankToId text := '';
34 vFilterOuId text := '';
35 vFilterOuToId text := '';
36
37BEGIN
38
39 /*
40 * 1. Ambil data transaksi Cash/Bank Out (K)
41 * 2.a. Ambil data transaksi Follow Up Cash Advance Settlement (K)
42 * 2.b. Ambil data transaksi Follow Up Cash Advance Settlement (D)
43 * 3.a. Ambil data transaksi Cash/Bank Transfer (K)
44 * 3.b. Ambil data transaksi Cash/Bank Transfer (D)
45 * 4. Ambil data transaksi Cash/Bank In Partner Receive (D)
46 * 5. Ambil data transaksi Cash/Bank In Other Receive (D)
47 * 6. Ambil data transaksi Cheque/Giro Realization (D)
48 * 7. Ambil data transaksi EDC Settlement (D)
49 * 8. Ambil data transaksi POS Shop (D)
50 * 9. Ambil data transaksi Return POS Shop (D)
51 * 10. Ambil data transaksi Void POS Shop (K)
52 *
53 * 11. Buat data recap
54 * 12. Ambil data dari cashbank balance
55 * 13. Cari data recap yang tidak ada dalam cashbank balance
56 * 14. Cari data cashbank balance yang tidak ada dalam recap
57 * 15. Simpan selisih amount recap terhadap cashbank balance
58 *
59 * 16. Cari data yang tidak ada di jurnal
60 * 17. Cari data yang tidak ada di detail jurnal
61 * */
62
63 IF ( pCashbankId <> vEmptyId ) THEN
64 vFilterCashbankIdA := ' AND A.cashbank_id = ' || pCashbankId ;
65 vFilterCashbankIdB := ' AND B.cashbank_id = ' || pCashbankId ;
66 vFilterCashbankIdD := ' AND D.cashbank_id = ' || pCashbankId ;
67 vFilterCashbankToId := ' AND A.cashbank_to_id = ' || pCashbankId ;
68 END IF;
69
70 IF ( pOuId <> vEmptyId ) THEN
71 vFilterOuId := ' AND A.ou_id = ' || pOuId ;
72 vFilterOuToId := ' AND A.ou_to_id = ' || pOuId ;
73 END IF;
74
75 DELETE FROM tt_cb_check_balance_for_trx WHERE session_id = pSessionId;
76 DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;
77 DELETE FROM tt_cb_check_balance_for_recap WHERE session_id = pSessionId;
78 DELETE FROM tt_cb_check_balance_for_cashbank_balance WHERE session_id = pSessionId;
79 DELETE FROM tt_cb_check_balance_for_result WHERE session_id = pSessionId;
80 DELETE FROM tt_cb_check_balance_for_gl_journal_trx WHERE session_id = pSessionId;
81 DELETE FROM tt_cb_check_balance_for_gl_journal_trx_details WHERE session_id = pSessionId;
82
83
84 -- 1. Ambil data transaksi Cash/Bank Out (K)
85 EXECUTE '
86 INSERT INTO tt_cb_check_balance_for_trx
87 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
88 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
89 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
90 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
91 FROM cb_in_out_cashbank A
92 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
93 WHERE A.tenant_id = $2
94 AND A.doc_date BETWEEN $4 AND $5
95 AND A.doc_type_id = $7 '
96 || vFilterOuId
97 || vFilterCashbankIdB ||'
98 GROUP BY A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id, A.type_in_out_cashbank, A.status_doc'
99 USING pSessionId, pTenantId, vRecTypeKredit, pDateFrom, pDateTo, vStatusRelease, vDocTypeCBOut;
100
101
102 -- 2.a. Ambil data transaksi Follow Up Cash Advance Settlement (K)
103 EXECUTE '
104 INSERT INTO tt_cb_check_balance_for_trx
105 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
106 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
107 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
108 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
109 FROM cb_in_out_cashbank A
110 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
111 WHERE A.tenant_id = $2
112 AND A.doc_date BETWEEN $4 AND $5
113 AND A.doc_type_id = $6
114 AND A.type_in_out_cashbank = $7 '
115 || vFilterOuId
116 || vFilterCashbankIdB || '
117 GROUP BY A.type_in_out_cashbank, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
118 A.type_in_out_cashbank, A.status_doc'
119 USING pSessionId, pTenantId, vRecTypeKredit, pDateFrom, pDateTo, vDocTypeFollowUpCashAdvanceSettlement, vTypeOut;
120
121 -- 2.b. Ambil data transaksi Follow Up Cash Advance Settlement (D)
122 EXECUTE '
123 INSERT INTO tt_cb_check_balance_for_trx
124 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
125 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
126 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
127 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
128 FROM cb_in_out_cashbank A
129 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
130 WHERE A.tenant_id = $2
131 AND A.doc_date BETWEEN $4 AND $5
132 AND A.status_doc = $6
133 AND A.doc_type_id = $7
134 AND A.type_in_out_cashbank = $8 '
135 || vFilterOuId
136 || vFilterCashbankIdB || '
137 GROUP BY A.type_in_out_cashbank, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
138 A.type_in_out_cashbank, A.status_doc'
139 USING pSessionId, pTenantId, vRecTypeDebit, pDateFrom, pDateTo, vStatusRelease, vDocTypeFollowUpCashAdvanceSettlement, vTypeIn;
140
141
142 -- 3.a. Ambil data transaksi Cash/Bank Transfer (K)
143 EXECUTE '
144 INSERT INTO tt_cb_check_balance_for_trx
145 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
146 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
147 SELECT $1, $2, A.doc_type_id, A.transfer_cashbank_id, A.doc_no, A.doc_date, $3, A.cashbank_id,
148 $4, $5, A.transfer_amount + COALESCE(B.cost_amount, 0), 0, A.status_doc
149 FROM cb_transfer_cashbank A
150 LEFT OUTER JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id = B.transfer_cashbank_id
151 WHERE A.tenant_id = $2
152 AND A.doc_date BETWEEN $6 AND $7
153 -- AND A.status_doc = $8
154 AND A.doc_type_id = $9 '
155 || vFilterOuId
156 || vFilterCashbankIdA || '
157 GROUP BY A.doc_type_id, A.transfer_cashbank_id, A.doc_no, A.doc_date, A.cashbank_id, A.transfer_amount, B.cost_amount, A.status_doc '
158 USING pSessionId, pTenantId, vEmptyId, vTypeOut, vRecTypeKredit, pDateFrom, pDateTo, vStatusRelease, vDocTypeCBTransfer;
159
160 -- 3.b. Ambil data transaksi Cash/Bank Transfer (D)
161 EXECUTE '
162 INSERT INTO tt_cb_check_balance_for_trx
163 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
164 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
165 SELECT $1, $2, B.doc_type_id, B.transfer_cashbank_id, B.doc_no, B.doc_date, $3, A.cashbank_to_id,
166 $4, $5, A.receive_amount, 0, B.status_doc
167 FROM cb_transfer_cashbank_receive A
168 INNER JOIN cb_transfer_cashbank B ON B.transfer_cashbank_id = A.transfer_cashbank_id
169 WHERE A.tenant_id = $2
170 AND B.doc_date BETWEEN $6 AND $7
171 AND B.status_doc = $8
172 AND B.doc_type_id = $9 '
173 || vFilterOuToId
174 || vFilterCashbankToId
175 USING pSessionId, pTenantId, vEmptyId, vTypeIn, vRecTypeDebit, pDateFrom, pDateTo, vStatusRelease, vDocTypeCBTransfer;
176
177
178 -- 4. Ambil data transaksi Cash/Bank In Partner Receive (D)
179 EXECUTE '
180 INSERT INTO tt_cb_check_balance_for_trx
181 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
182 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
183 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
184 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount - COALESCE(C.cost_amount, 0)), 0, A.status_doc
185 FROM cb_in_out_cashbank A
186 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
187 LEFT OUTER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
188 WHERE A.tenant_id = $2
189 AND A.doc_date BETWEEN $4 AND $5
190 AND A.status_doc = $6
191 AND A.doc_type_id = $7 '
192 || vFilterOuId
193 || vFilterCashbankIdB || '
194 GROUP BY A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id, A.type_in_out_cashbank,
195 C.cost_amount, A.status_doc'
196 USING pSessionId, pTenantId, vRecTypeDebit, pDateFrom, pDateTo, vStatusRelease, vDocTypeCBInPartner;
197
198
199 -- 5. Ambil data transaksi Cash/Bank In Other Receive (D)
200 EXECUTE '
201 INSERT INTO tt_cb_check_balance_for_trx
202 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
203 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
204 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
205 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
206 FROM cb_in_out_cashbank A
207 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
208 WHERE A.tenant_id = $2
209 AND A.doc_date BETWEEN $4 AND $5
210 AND A.status_doc = $6
211 AND A.doc_type_id = $7 '
212 || vFilterOuId
213 || vFilterCashbankIdB || '
214 GROUP BY A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id, A.type_in_out_cashbank, A.status_doc'
215 USING pSessionId, pTenantId, vRecTypeDebit, pDateFrom, pDateTo, vStatusRelease, vDocTypeCBInOther;
216
217
218 -- 6. Ambil data transaksi Cheque/Giro Realization (D)
219 EXECUTE '
220 INSERT INTO tt_cb_check_balance_for_trx
221 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
222 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
223 SELECT $1, A.tenant_id, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, B.partner_id, B.cashbank_id,
224 $2, $3, SUM(B.cheque_giro_amount), 0, A.status_doc
225 FROM cb_in_out_cashbank A
226 INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
227 WHERE A.status_doc = $4
228 AND A.doc_date BETWEEN $5 AND $6
229 AND B.realization_status = $7
230 AND A.doc_type_id = $8 '
231 || vFilterOuId
232 || vFilterCashbankIdB || '
233 GROUP BY A.tenant_id, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, B.partner_id, B.cashbank_id, A.status_doc'
234 USING pSessionId, vTypeIn, vRecTypeDebit, vStatusRelease, pDateFrom, pDateTo, vStatusAccept, vDocTypeCGRealization;
235
236
237 -- 7. Ambil data transaksi EDC Settlement (D)
238 EXECUTE '
239 INSERT INTO tt_cb_check_balance_for_trx
240 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
241 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
242 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
243 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
244 FROM cb_in_out_cashbank A
245 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
246 WHERE A.tenant_id = $2
247 AND A.doc_date BETWEEN $4 AND $5
248 AND A.status_doc = $6
249 AND A.doc_type_id = $7 '
250 || vFilterOuId
251 || vFilterCashbankIdB || '
252 GROUP BY A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id, A.type_in_out_cashbank, A.status_doc'
253 USING pSessionId, pTenantId, vRecTypeDebit, pDateFrom, pDateTo, vStatusRelease, vDocTypeEDCSettlement;
254
255
256 -- 8. Ambil data transaksi POS Shop (D)
257 EXECUTE '
258 INSERT INTO tr_kartu_kas_by_doc_type
259 (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
260 doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
261 amount_debit, amount_credit)
262 SELECT $1, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
263 A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
264 SUM(B.conversion_amount), 0
265 FROM i_trx_pos A
266 INNER JOIN i_trx_pos_cash_payment B ON A.trx_pos_id = B.trx_pos_id AND A.process_no = B.process_no
267 INNER JOIN m_document C ON A.doc_type_id = C.doc_type_id
268 INNER JOIN m_cashbank D ON B.curr_payment_code = D.curr_code
269 INNER JOIN m_cashbank_ou E ON D.cashbank_id = E.cashbank_id AND A.ou_id = E.ou_id
270 WHERE A.tenant_id = $2
271 AND A.doc_date BETWEEN $3 AND $4
272 AND A.doc_type_id = $5
273 AND A.tenant_id = B.tenant_id
274 AND D.flg_cash_bank = '''|| 'C' ||''''
275 || vFilterOuId
276 || vFilterCashbankIdD || '
277 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id,
278 D.cashbank_id, D.curr_code'
279 USING pSessionId, pTenantId, pDateFrom, pDateTo, vDocTypePOSShop;
280
281
282 -- 9. Ambil data transaksi Return POS Shop (D)
283 EXECUTE '
284 INSERT INTO tr_kartu_kas_by_doc_type
285 (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
286 doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
287 amount_debit, amount_credit)
288 SELECT $1, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
289 A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
290 0, SUM(A.total_refund)
291 FROM i_trx_pos A
292 INNER JOIN m_document C ON A.doc_type_id = C.doc_type_id
293 INNER JOIN m_cashbank D ON A.curr_code = D.curr_code
294 INNER JOIN m_cashbank_ou E ON D.cashbank_id = E.cashbank_id AND A.ou_id = E.ou_id
295 WHERE A.tenant_id = $2
296 AND A.doc_date BETWEEN $3 AND $4
297 AND A.doc_type_id = $5
298 AND D.flg_cash_bank = '''|| 'C' ||''''
299 || vFilterOuId
300 || vFilterCashbankIdD || '
301 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id, A.doc_no, A.doc_date, A.partner_id,
302 D.cashbank_id, D.curr_code'
303 USING pSessionId, pTenantId, pDateFrom, pDateTo, vDocTypeReturnPosShop;
304
305
306 -- Ambil data POS dari table temporary tr_kartu_kas_by_doc_type,
307 -- Masukkan ke table tt_cb_check_balance_for_trx
308 INSERT INTO tt_cb_check_balance_for_trx
309 (session_id, tenant_id, doc_type_id,
310 doc_id, doc_no, doc_date, partner_id, cashbank_id,
311 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
312 SELECT pSessionId, pTenantId, A.doc_type_id,
313 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
314 vTypeIn, vRecTypeDebit, SUM(A.amount_debit - A.amount_credit), 0, ''
315 FROM tr_kartu_kas_by_doc_type A
316 WHERE A.tenant_id = pTenantId
317 AND A.session_id = pSessionId
318 AND A.doc_date BETWEEN pDateFrom AND pDateTo
319 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
320
321
322 -- 10. Ambil data transaksi Void POS Shop (K)
323 INSERT INTO tt_cb_check_balance_for_trx
324 (session_id, tenant_id, doc_type_id,
325 doc_id, doc_no, doc_date, partner_id, cashbank_id,
326 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
327 SELECT pSessionId, pTenantId, A.doc_type_id,
328 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
329 vTypeIn, vRecTypeKredit, SUM(A.amount_debit - A.amount_credit), 0, ''
330 FROM tr_kartu_kas_by_doc_type A
331 WHERE A.tenant_id = pTenantId
332 AND A.session_id = pSessionId
333 AND A.doc_date BETWEEN pDateFrom AND pDateTo
334 AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
335 WHERE A.tenant_id = B.tenant_id AND
336 A.doc_no = B.doc_no AND
337 A.doc_date = B.doc_date AND
338 A.ou_id = B.ou_id)
339 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
340
341
342 -- 11. Buat data recap
343 EXECUTE '
344 INSERT INTO tt_cb_check_balance_for_recap(session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, amount)
345 SELECT $1, $2, A.cashbank_id, A.doc_date, A.sign_cashbank, B.curr_code, SUM(A.amount)
346 FROM tt_cb_check_balance_for_trx A
347 INNER JOIN m_cashbank B ON A.cashbank_id = B.cashbank_id
348 WHERE A.session_id = $1 '
349 || vFilterCashbankIdA || '
350 GROUP BY A.cashbank_id, A.doc_date, A.sign_cashbank, B.curr_code '
351 USING pSessionId, pTenantId;
352
353
354 -- 12. Ambil data dari cashbank balance
355 EXECUTE '
356 INSERT INTO tt_cb_check_balance_for_cashbank_balance
357 (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, amount)
358 SELECT $1, $2, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, SUM(A.amount)
359 FROM cb_cashbank_balance A
360 WHERE A.tenant_id = $2
361 AND A.cash_bank_date BETWEEN $3 AND $4
362 AND A.rec_type <> ''' || 'A' || ''''
363 || vFilterOuId
364 || vFilterCashbankIdA || '
365 GROUP BY A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code'
366 USING pSessionId, pTenantId, pDateFrom, pDateTo;
367
368
369 -- 13. Cari data recap yang tidak ada dalam cashbank balance
370 INSERT INTO tt_cb_check_balance_for_result
371 (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
372 SELECT A.session_id, A.tenant_id, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, A.amount, 0, A.amount
373 FROM tt_cb_check_balance_for_recap A
374 WHERE NOT EXISTS (
375 SELECT 1 FROM tt_cb_check_balance_for_cashbank_balance B
376 WHERE A.session_id = B.session_id
377 AND A.tenant_id = B.tenant_id
378 AND A.cashbank_id = B.cashbank_id
379 AND A.cash_bank_date = B.cash_bank_date
380 AND A.rec_type = B.rec_type
381 AND A.curr_code = B.curr_code
382 ) AND A.session_id = pSessionId;
383
384
385 -- 14. Cari data cashbank balance yang tidak ada dalam recap
386 INSERT INTO tt_cb_check_balance_for_result
387 (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
388 SELECT A.session_id, A.tenant_id, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, 0, A.amount, A.amount
389 FROM tt_cb_check_balance_for_cashbank_balance A
390 WHERE NOT EXISTS (
391 SELECT 1 FROM tt_cb_check_balance_for_recap B
392 WHERE A.session_id = B.session_id
393 AND A.tenant_id = B.tenant_id
394 AND A.cashbank_id = B.cashbank_id
395 AND A.cash_bank_date = B.cash_bank_date
396 AND A.rec_type = B.rec_type
397 AND A.curr_code = B.curr_code
398 ) AND A.session_id = pSessionId;
399
400
401 -- 15. Simpan selisih amount recap terhadap cashbank balance
402 INSERT INTO tt_cb_check_balance_for_result
403 (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
404 SELECT A.session_id, A.tenant_id, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, B.amount, A.amount, B.amount - A.amount
405 FROM tt_cb_check_balance_for_cashbank_balance A
406 INNER JOIN tt_cb_check_balance_for_recap B
407 ON A.session_id = B.session_id
408 AND A.tenant_id = B.tenant_id
409 AND A.cashbank_id = B.cashbank_id
410 AND A.cash_bank_date = B.cash_bank_date
411 AND A.rec_type = B.rec_type
412 AND A.curr_code = B.curr_code
413 AND A.session_id = pSessionId;
414
415
416 -- 16. Cari data yang tidak ada di jurnal
417 INSERT INTO tt_cb_check_balance_for_gl_journal_trx
418 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, curr_code)
419 SELECT pSessionId, pTenantId, A.doc_type_id, A.doc_id, A.doc_no, A.doc_date, C.curr_code
420 FROM tt_cb_check_balance_for_trx A
421 INNER JOIN m_cashbank C ON A.cashbank_id = C.cashbank_id
422 WHERE A.session_id = pSessionId
423 AND A.status_doc = vStatusRelease
424 AND NOT EXISTS (
425 SELECT 1 FROM gl_journal_trx B
426 WHERE B.tenant_id = A.tenant_id
427 AND B.doc_type_id = A.doc_type_id
428 AND B.doc_id = A.doc_id
429 AND B.doc_no = A.doc_no
430 AND B.doc_date = A.doc_date
431 );
432
433
434 -- 17. Cari data yang tidak ada di detail jurnal
435 INSERT INTO tt_cb_check_balance_for_gl_journal_trx_details
436 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, cashbank_id, curr_code, amount, gl_detail_curr_code, gl_detail_amount, journal_trx_id)
437 SELECT pSessionId, pTenantId, A.doc_type_id, A.doc_id, A.doc_no, A.doc_date, A.cashbank_id, D.curr_code, A.amount, ' ', 0, -99
438 FROM tt_cb_check_balance_for_trx A
439 INNER JOIN m_cashbank D ON A.cashbank_id = D.cashbank_id
440 WHERE A.session_id = pSessionId
441 AND A.status_doc = vStatusRelease
442 AND NOT EXISTS (
443 SELECT 1 FROM vw_gl_journal_trx_details B
444 INNER JOIN gl_journal_trx C ON B.journal_trx_id = C.journal_trx_id
445 WHERE A.tenant_id = B.tenant_id
446 AND C.doc_type_id = A.doc_type_id
447 AND C.doc_id = A.doc_id
448 AND C.doc_no = A.doc_no
449 AND C.doc_date = A.doc_date
450 AND A.amount = B.amount
451 );
452
453 UPDATE tt_cb_check_balance_for_gl_journal_trx_details A
454 SET gl_detail_curr_code = B.curr_code, gl_detail_amount = B.amount, journal_trx_id = B.journal_trx_id
455 FROM vw_gl_journal_trx_details B
456 INNER JOIN gl_journal_trx C ON B.journal_trx_id = C.journal_trx_id
457 WHERE A.tenant_id = B.tenant_id
458 AND C.doc_type_id = A.doc_type_id
459 AND C.doc_id = A.doc_id
460 AND C.doc_no = A.doc_no
461 AND C.doc_date = A.doc_date
462 AND A.cashbank_id = B.cashbank_id;
463
464END;
465$BODY$
466 LANGUAGE plpgsql VOLATILE
467 COST 100;
468 /