· 8 years ago · Feb 05, 2018, 05:22 AM
1CREATE OR REPLACE FUNCTION f_crosscheck_cashbank_docs(character varying, bigint, bigint, character varying, bigint)
2 RETURNS void AS
3$BODY$
4DECLARE
5 pSessionId ALIAS FOR $1;
6 pTenantId ALIAS FOR $2;
7 pOuId ALIAS FOR $3; -- bisa -99
8 pYearMonth ALIAS FOR $4;
9 pCashbankId ALIAS FOR $5; -- bisa -99
10
11 vStatusRelease character varying := 'R';
12 vRecTypeDebit character varying := 'D';
13 vRecTypeKredit character varying := 'K';
14 vTypeIn character varying := 'I';
15 vTypeOut character varying := 'O';
16 vStatusAccept character varying := 'ACCEPT';
17
18 vEmptyId bigint := -99;
19 vDocTypeCBOut bigint := 611;
20 vDocTypeFollowUpCashAdvanceSettlement bigint := 641;
21 vDocTypeCBTransfer bigint := 631;
22 vDocTypeCBInPartner bigint := 621;
23 vDocTypeCBInOther bigint := 623;
24 vDocTypeCGRealization bigint := 625;
25 vDocTypeEDCSettlement bigint := 622;
26 vDocTypePOSShop bigint := 401;
27 vDocTypeReturnPOSShop bigint := 402;
28
29 vFilterCashbankIdA text := '';
30 vFilterCashbankIdB text := '';
31 vFilterCashbankIdC text := '';
32 vFilterCashbankIdD text := '';
33 vFilterCashbankToId text := '';
34 vFilterOuId text := '';
35 vFilterOuToId text := '';
36 vFilterOuBuId text := '';
37 vSpaceValue text := ' ';
38
39BEGIN
40
41 /*
42 * 1. Ambil data transaksi Cash/Bank Out (K)
43 * 2.a. Ambil data transaksi Follow Up Cash Advance Settlement (K)
44 * 2.b. Ambil data transaksi Follow Up Cash Advance Settlement (D)
45 * 3.a. Ambil data transaksi Cash/Bank Transfer (K)
46 * 3.b. Ambil data transaksi Cash/Bank Transfer (D)
47 * 4. Ambil data transaksi Cash/Bank In Partner Receive (D)
48 * 5. Ambil data transaksi Cash/Bank In Other Receive (D)
49 * 6. Ambil data transaksi Cheque/Giro Realization (D)
50 * 7. Ambil data transaksi EDC Settlement (D)
51 * 8. Ambil data transaksi POS Shop (D)
52 * 9. Ambil data transaksi Return POS Shop (D)
53 * 10. Ambil data transaksi Void POS Shop (K)
54 *
55 * 11. Buat data recap
56 * 12. Ambil data dari cashbank balance
57 * 13. Cari data recap yang tidak ada dalam cashbank balance
58 * 14. Cari data cashbank balance yang tidak ada dalam recap
59 * 15. Simpan selisih amount recap terhadap cashbank balance
60 *
61 * 16. Cari data yang tidak ada di jurnal
62 * 17. Cari data yang tidak ada di detail jurnal
63 * */
64
65 IF ( pCashbankId <> vEmptyId ) THEN
66 vFilterCashbankIdA := ' AND A.cashbank_id = ' || pCashbankId ;
67 vFilterCashbankIdB := ' AND B.cashbank_id = ' || pCashbankId ;
68 vFilterCashbankIdC := ' AND C.cashbank_id = ' || pCashbankId ;
69 vFilterCashbankIdD := ' AND D.cashbank_id = ' || pCashbankId ;
70 vFilterCashbankToId := ' AND A.cashbank_to_id = ' || pCashbankId ;
71 END IF;
72
73 IF ( pOuId <> vEmptyId ) THEN
74 vFilterOuId := ' AND A.ou_id = ' || pOuId ;
75 vFilterOuToId := ' AND A.ou_to_id = ' || pOuId ;
76 vFilterOuBuId := ' AND C.ou_bu_id = ' || pOuId ;
77 END IF;
78
79 DELETE FROM tt_cb_check_balance_for_trx WHERE session_id = pSessionId;
80 DELETE FROM tr_kartu_kas_by_doc_type WHERE session_id = pSessionId;
81 DELETE FROM tt_cb_check_balance_for_recap WHERE session_id = pSessionId;
82 DELETE FROM tt_cb_check_balance_for_cashbank_balance WHERE session_id = pSessionId;
83 DELETE FROM tt_cb_check_balance_for_result WHERE session_id = pSessionId;
84 DELETE FROM tt_cb_check_balance_for_gl_journal_trx WHERE session_id = pSessionId;
85 DELETE FROM tt_cb_check_balance_for_gl_journal_trx_details WHERE session_id = pSessionId;
86
87
88 -- 1. Ambil data transaksi Cash/Bank Out (K)
89 EXECUTE '
90 INSERT INTO tt_cb_check_balance_for_trx
91 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
92 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
93 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
94 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
95 FROM cb_in_out_cashbank A
96 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
97 WHERE A.tenant_id = $2
98 AND SUBSTR(A.doc_date, 1, 6) = $4
99 AND A.doc_type_id = $5 '
100 || vFilterOuId
101 || vFilterCashbankIdB ||'
102 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'
103 USING pSessionId, pTenantId, vRecTypeKredit, pYearMonth, vDocTypeCBOut;
104
105
106 -- 2.a. Ambil data transaksi Follow Up Cash Advance Settlement (K)
107 EXECUTE '
108 INSERT INTO tt_cb_check_balance_for_trx
109 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
110 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
111 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
112 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
113 FROM cb_in_out_cashbank A
114 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
115 WHERE A.tenant_id = $2
116 AND SUBSTR(A.doc_date, 1, 6) = $4
117 AND A.doc_type_id = 5
118 AND A.type_in_out_cashbank = $6 '
119 || vFilterOuId
120 || vFilterCashbankIdB || '
121 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,
122 A.type_in_out_cashbank, A.status_doc'
123 USING pSessionId, pTenantId, vRecTypeKredit, pYearMonth, vDocTypeFollowUpCashAdvanceSettlement, vTypeOut;
124
125 -- 2.b. Ambil data transaksi Follow Up Cash Advance Settlement (D)
126 EXECUTE '
127 INSERT INTO tt_cb_check_balance_for_trx
128 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
129 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
130 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
131 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
132 FROM cb_in_out_cashbank A
133 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
134 WHERE A.tenant_id = $2
135 AND SUBSTR(A.doc_date, 1, 6) = $4
136 AND A.status_doc = $5
137 AND A.doc_type_id = $6
138 AND A.type_in_out_cashbank = $7 '
139 || vFilterOuId
140 || vFilterCashbankIdB || '
141 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,
142 A.type_in_out_cashbank, A.status_doc'
143 USING pSessionId, pTenantId, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeFollowUpCashAdvanceSettlement, vTypeIn;
144
145
146 -- 3.a. Ambil data transaksi Cash/Bank Transfer (K)
147 EXECUTE '
148 INSERT INTO tt_cb_check_balance_for_trx
149 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
150 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
151 SELECT $1, $2, A.doc_type_id, A.transfer_cashbank_id, A.doc_no, A.doc_date, $3, A.cashbank_id,
152 $4, $5, A.transfer_amount + COALESCE(B.cost_amount, 0), 0, A.status_doc
153 FROM cb_transfer_cashbank A
154 LEFT OUTER JOIN cb_transfer_cashbank_cost B ON A.transfer_cashbank_id = B.transfer_cashbank_id
155 WHERE A.tenant_id = $2
156 AND SUBSTR(A.doc_date, 1, 6) = $6
157 AND A.doc_type_id = $7 '
158 || vFilterOuId
159 || vFilterCashbankIdA || '
160 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 '
161 USING pSessionId, pTenantId, vEmptyId, vTypeOut, vRecTypeKredit, pYearMonth, vDocTypeCBTransfer;
162
163 -- 3.b. Ambil data transaksi Cash/Bank Transfer (D)
164 EXECUTE '
165 INSERT INTO tt_cb_check_balance_for_trx
166 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
167 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
168 SELECT $1, $2, B.doc_type_id, B.transfer_cashbank_id, B.doc_no, B.doc_date, $3, A.cashbank_to_id,
169 $4, $5, A.receive_amount, 0, B.status_doc
170 FROM cb_transfer_cashbank_receive A
171 INNER JOIN cb_transfer_cashbank B ON B.transfer_cashbank_id = A.transfer_cashbank_id
172 WHERE A.tenant_id = $2
173 AND SUBSTR(B.doc_date, 1, 6) = $6
174 AND B.status_doc = $7
175 AND B.doc_type_id = $8 '
176 || vFilterOuToId
177 || vFilterCashbankToId
178 USING pSessionId, pTenantId, vEmptyId, vTypeIn, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeCBTransfer;
179
180
181 -- 4. Ambil data transaksi Cash/Bank In Partner Receive (D)
182 EXECUTE '
183 WITH tt_in_out_cashbank_cost AS (
184 SELECT A.in_out_cashbank_id, SUM(C.cost_amount) AS cost_amount
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 INNER 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 SUBSTR(A.doc_date, 1, 6) = $4
190 AND A.status_doc = $5
191 AND A.doc_type_id = $6 '
192 || vFilterOuId
193 || vFilterCashbankIdB || '
194 GROUP BY A.in_out_cashbank_id
195 )
196 INSERT INTO tt_cb_check_balance_for_trx
197 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
198 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
199 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
200 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount) - COALESCE(C.cost_amount, 0), 0, A.status_doc
201 FROM cb_in_out_cashbank A
202 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
203 LEFT OUTER JOIN tt_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
204 WHERE A.tenant_id = $2
205 AND SUBSTR(A.doc_date, 1, 6) = $4
206 AND A.status_doc = $5
207 AND A.doc_type_id = $6 '
208 || vFilterOuId
209 || vFilterCashbankIdB || '
210 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,
211 C.cost_amount, A.status_doc'
212 USING pSessionId, pTenantId, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeCBInPartner;
213
214
215 -- 5. Ambil data transaksi Cash/Bank In Other Receive (D)
216 EXECUTE '
217 INSERT INTO tt_cb_check_balance_for_trx
218 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
219 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
220 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
221 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
222 FROM cb_in_out_cashbank A
223 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
224 WHERE A.tenant_id = $2
225 AND SUBSTR(A.doc_date, 1, 6) = $4
226 AND A.status_doc = $5
227 AND A.doc_type_id = $6 '
228 || vFilterOuId
229 || vFilterCashbankIdB || '
230 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'
231 USING pSessionId, pTenantId, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeCBInOther;
232
233
234 -- 6. Ambil data transaksi Cheque/Giro Realization (D)
235 EXECUTE '
236 WITH tt_in_out_cashbank_cost AS (
237 SELECT A.in_out_cashbank_id, SUM(C.cost_amount) AS cost_amount
238 FROM cb_in_out_cashbank A
239 INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
240 INNER JOIN cb_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
241 WHERE A.status_doc = $4
242 AND SUBSTR(A.doc_date, 1, 6) = $5
243 AND B.realization_status = $6
244 AND A.doc_type_id = $7 '
245 || vFilterOuId
246 || vFilterCashbankIdB || '
247 GROUP BY A.in_out_cashbank_id
248 )
249 INSERT INTO tt_cb_check_balance_for_trx
250 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
251 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
252 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,
253 $2, $3, SUM(B.cheque_giro_amount) - COALESCE(C.cost_amount, 0), 0, A.status_doc
254 FROM cb_in_out_cashbank A
255 INNER JOIN cb_cheque_giro_realization B ON A.in_out_cashbank_id = B.in_out_cashbank_id
256 LEFT OUTER JOIN tt_in_out_cashbank_cost C ON A.in_out_cashbank_id = C.in_out_cashbank_id
257 WHERE A.status_doc = $4
258 AND SUBSTR(A.doc_date, 1, 6) = $5
259 AND B.realization_status = $6
260 AND A.doc_type_id = $7 '
261 || vFilterOuId
262 || vFilterCashbankIdB || '
263 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,
264 C.cost_amount, A.status_doc'
265 USING pSessionId, vTypeIn, vRecTypeDebit, vStatusRelease, pYearMonth, vStatusAccept, vDocTypeCGRealization;
266
267
268 -- 7. Ambil data transaksi EDC Settlement (D)
269 EXECUTE '
270 INSERT INTO tt_cb_check_balance_for_trx
271 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, partner_id, cashbank_id,
272 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
273 SELECT $1, $2, A.doc_type_id, A.in_out_cashbank_id, A.doc_no, A.doc_date, A.partner_id, B.cashbank_id,
274 A.type_in_out_cashbank, $3, SUM(B.cashbank_amount), 0, A.status_doc
275 FROM cb_in_out_cashbank A
276 INNER JOIN cb_in_out_cashbank_payment B ON A.in_out_cashbank_id = B.in_out_cashbank_id
277 WHERE A.tenant_id = $2
278 AND SUBSTR(A.doc_date, 1, 6) = $4
279 AND A.status_doc = $5
280 AND A.doc_type_id = $6 '
281 || vFilterOuId
282 || vFilterCashbankIdB || '
283 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,
284 A.status_doc'
285 USING pSessionId, pTenantId, vRecTypeDebit, pYearMonth, vStatusRelease, vDocTypeEDCSettlement;
286
287
288 -- 8. Ambil data transaksi penjualan POS Shop (D)
289 EXECUTE '
290 INSERT INTO tr_kartu_kas_by_doc_type
291 (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
292 doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
293 amount_debit,
294 amount_credit)
295 SELECT $1, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
296 A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
297 CASE WHEN ((A.trx_pos_id <> $5) AND (A.doc_no <> A.process_no)) THEN
298 SUM(B.payment_amount)
299 ELSE
300 SUM(B.conversion_amount)
301 END AS amount_debit,
302 SUM(A.total_refund) AS amount_credit
303 FROM i_trx_pos A
304 INNER JOIN i_trx_pos_cash_payment B ON A.trx_pos_id = B.trx_pos_id AND A.process_no = B.process_no
305 INNER JOIN m_document C ON A.doc_type_id = C.doc_type_id
306 INNER JOIN m_cashbank D ON B.curr_payment_code = D.curr_code
307 INNER JOIN m_cashbank_ou E ON D.cashbank_id = E.cashbank_id AND A.ou_id = E.ou_id
308 WHERE A.tenant_id = $2
309 AND SUBSTR(A.doc_date, 1, 6) = $3
310 AND A.doc_type_id = $4
311 AND A.tenant_id = B.tenant_id
312 AND D.flg_cash_bank = '''|| 'C' ||''''
313 || vFilterOuId
314 || vFilterCashbankIdD || '
315 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,
316 D.cashbank_id, D.curr_code, A.process_no'
317 USING pSessionId, pTenantId, pYearMonth, vDocTypePOSShop, vEmptyId;
318
319
320 -- 9. Ambil data transaksi Return POS Shop (D)
321 EXECUTE '
322 INSERT INTO tr_kartu_kas_by_doc_type
323 (session_id, sort_no, tenant_id, ou_id, doc_type_id, doc_desc, ref_id,
324 doc_no, doc_date, partner_id, cashbank_id, cashbank_target_id, curr_code,
325 amount_debit, amount_credit)
326 SELECT $1, 2, A.tenant_id, A.ou_id, A.doc_type_id, C.doc_desc, A.trx_pos_id,
327 A.doc_no, A.doc_date, A.partner_id, D.cashbank_id, D.cashbank_id, D.curr_code,
328 0, SUM(A.total_refund)
329 FROM i_trx_pos A
330 INNER JOIN m_document C ON A.doc_type_id = C.doc_type_id
331 INNER JOIN m_cashbank D ON A.curr_code = D.curr_code
332 INNER JOIN m_cashbank_ou E ON D.cashbank_id = E.cashbank_id AND A.ou_id = E.ou_id
333 WHERE A.tenant_id = $2
334 AND SUBSTR(A.doc_date, 1, 6) = $3
335 AND A.doc_type_id = $4
336 AND D.flg_cash_bank = '''|| 'C' ||''''
337 || vFilterOuId
338 || vFilterCashbankIdD || '
339 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,
340 D.cashbank_id, D.curr_code'
341 USING pSessionId, pTenantId, pYearMonth, vDocTypeReturnPosShop;
342
343
344 -- Ambil data POS dari table temporary tr_kartu_kas_by_doc_type,
345 -- Masukkan ke table tt_cb_check_balance_for_trx
346 -- Untuk transaksi penjualan dari ERP (D)
347 INSERT INTO tt_cb_check_balance_for_trx
348 (session_id, tenant_id, doc_type_id,
349 doc_id, doc_no, doc_date, partner_id, cashbank_id,
350 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
351 SELECT pSessionId, pTenantId, A.doc_type_id,
352 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
353 vTypeIn, vRecTypeDebit, SUM(A.amount_debit), 0, ''
354 FROM tr_kartu_kas_by_doc_type A
355 WHERE A.tenant_id = pTenantId
356 AND A.session_id = pSessionId
357 AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
358 AND A.ref_id = vEmptyId
359 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
360
361 -- Ambil data POS dari table temporary tr_kartu_kas_by_doc_type,
362 -- Masukkan ke table tt_cb_check_balance_for_trx
363 -- Untuk transaksi refund dari ERP (K)
364 INSERT INTO tt_cb_check_balance_for_trx
365 (session_id, tenant_id, doc_type_id,
366 doc_id, doc_no, doc_date, partner_id, cashbank_id,
367 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
368 SELECT pSessionId, pTenantId, A.doc_type_id,
369 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
370 vTypeIn, vRecTypeKredit, SUM(A.amount_credit), 0, ''
371 FROM tr_kartu_kas_by_doc_type A
372 WHERE A.tenant_id = pTenantId
373 AND A.session_id = pSessionId
374 AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
375 AND A.ref_id = vEmptyId
376 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
377
378 -- Ambil data POS dari table temporary tr_kartu_kas_by_doc_type,
379 -- Masukkan ke table tt_cb_check_balance_for_trx
380 -- Untuk transaksi dari POS
381 INSERT INTO tt_cb_check_balance_for_trx
382 (session_id, tenant_id, doc_type_id,
383 doc_id, doc_no, doc_date, partner_id, cashbank_id,
384 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
385 SELECT pSessionId, pTenantId, A.doc_type_id,
386 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
387 vTypeIn, vRecTypeDebit, SUM(A.amount_debit - A.amount_credit), 0, ''
388 FROM tr_kartu_kas_by_doc_type A
389 WHERE A.tenant_id = pTenantId
390 AND A.session_id = pSessionId
391 AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
392 AND A.ref_id <> vEmptyId
393 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
394
395
396 -- 10. Ambil data transaksi Void POS Shop
397 -- Penjualan POS dari ERP (K)
398 INSERT INTO tt_cb_check_balance_for_trx
399 (session_id, tenant_id, doc_type_id,
400 doc_id, doc_no, doc_date, partner_id, cashbank_id,
401 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
402 SELECT pSessionId, pTenantId, A.doc_type_id,
403 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
404 vTypeIn, vRecTypeKredit, SUM(A.amount_debit), 0, ''
405 FROM tr_kartu_kas_by_doc_type A
406 WHERE A.tenant_id = pTenantId
407 AND A.session_id = pSessionId
408 AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
409 AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
410 WHERE A.tenant_id = B.tenant_id AND
411 A.doc_no = B.doc_no AND
412 A.doc_date = B.doc_date AND
413 A.ou_id = B.ou_id)
414 AND A.ref_id = vEmptyId
415 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
416
417 -- 10. Ambil data transaksi Void POS Shop
418 -- Refund POS dari ERP (D)
419 INSERT INTO tt_cb_check_balance_for_trx
420 (session_id, tenant_id, doc_type_id,
421 doc_id, doc_no, doc_date, partner_id, cashbank_id,
422 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
423 SELECT pSessionId, pTenantId, A.doc_type_id,
424 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
425 vTypeIn, vRecTypeDebit, SUM(A.amount_credit), 0, ''
426 FROM tr_kartu_kas_by_doc_type A
427 WHERE A.tenant_id = pTenantId
428 AND A.session_id = pSessionId
429 AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
430 AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
431 WHERE A.tenant_id = B.tenant_id AND
432 A.doc_no = B.doc_no AND
433 A.doc_date = B.doc_date AND
434 A.ou_id = B.ou_id)
435 AND A.ref_id = vEmptyId
436 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
437
438 -- 10. Ambil data transaksi Void POS Shop
439 -- Penjualan POS dari POS (K)
440 INSERT INTO tt_cb_check_balance_for_trx
441 (session_id, tenant_id, doc_type_id,
442 doc_id, doc_no, doc_date, partner_id, cashbank_id,
443 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
444 SELECT pSessionId, pTenantId, A.doc_type_id,
445 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
446 vTypeIn, vRecTypeKredit, SUM(A.amount_debit), 0, ''
447 FROM tr_kartu_kas_by_doc_type A
448 WHERE A.tenant_id = pTenantId
449 AND A.session_id = pSessionId
450 AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
451 AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
452 WHERE A.tenant_id = B.tenant_id AND
453 A.doc_no = B.doc_no AND
454 A.doc_date = B.doc_date AND
455 A.ou_id = B.ou_id)
456 AND A.ref_id <> vEmptyId
457 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
458
459 -- 10. Ambil data transaksi Void POS Shop
460 -- Refund POS dari POS (D)
461 INSERT INTO tt_cb_check_balance_for_trx
462 (session_id, tenant_id, doc_type_id,
463 doc_id, doc_no, doc_date, partner_id, cashbank_id,
464 type_cashbank, sign_cashbank, amount, cost_amount, status_doc)
465 SELECT pSessionId, pTenantId, A.doc_type_id,
466 A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id,
467 vTypeIn, vRecTypeDebit, SUM(A.amount_credit), 0, ''
468 FROM tr_kartu_kas_by_doc_type A
469 WHERE A.tenant_id = pTenantId
470 AND A.session_id = pSessionId
471 AND SUBSTR(A.doc_date, 1, 6) = pYearMonth
472 AND EXISTS (SELECT 1 FROM i_trx_log_voided_pos B
473 WHERE A.tenant_id = B.tenant_id AND
474 A.doc_no = B.doc_no AND
475 A.doc_date = B.doc_date AND
476 A.ou_id = B.ou_id)
477 AND A.ref_id <> vEmptyId
478 GROUP BY A.doc_type_id, A.ref_id, A.doc_no, A.doc_date, A.partner_id, A.cashbank_id;
479
480
481 -- 11. Buat data recap
482 EXECUTE '
483 INSERT INTO tt_cb_check_balance_for_recap(session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, amount)
484 SELECT $1, $2, A.cashbank_id, A.doc_date, A.sign_cashbank, B.curr_code, SUM(A.amount)
485 FROM tt_cb_check_balance_for_trx A
486 INNER JOIN m_cashbank B ON A.cashbank_id = B.cashbank_id
487 WHERE A.session_id = $1 '
488 || vFilterCashbankIdA || '
489 GROUP BY A.cashbank_id, A.doc_date, A.sign_cashbank, B.curr_code '
490 USING pSessionId, pTenantId;
491
492
493 -- 12. Ambil data dari cashbank balance
494 EXECUTE '
495 INSERT INTO tt_cb_check_balance_for_cashbank_balance
496 (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, amount)
497 SELECT $1, $2, A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code, SUM(A.amount)
498 FROM cb_cashbank_balance A
499 WHERE A.tenant_id = $2
500 AND SUBSTR(A.cash_bank_date, 1, 6) = $3
501 AND A.rec_type <> ''' || 'A' || ''''
502 || vFilterOuId
503 || vFilterCashbankIdA || '
504 GROUP BY A.cashbank_id, A.cash_bank_date, A.rec_type, A.curr_code'
505 USING pSessionId, pTenantId, pYearMonth;
506
507
508 -- 13. Cari data recap yang tidak ada dalam cashbank balance
509 INSERT INTO tt_cb_check_balance_for_result
510 (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
511 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
512 FROM tt_cb_check_balance_for_recap A
513 WHERE NOT EXISTS (
514 SELECT 1 FROM tt_cb_check_balance_for_cashbank_balance B
515 WHERE A.session_id = B.session_id
516 AND A.tenant_id = B.tenant_id
517 AND A.cashbank_id = B.cashbank_id
518 AND A.cash_bank_date = B.cash_bank_date
519 AND A.rec_type = B.rec_type
520 AND A.curr_code = B.curr_code
521 ) AND A.session_id = pSessionId;
522
523
524 -- 14. Cari data cashbank balance yang tidak ada dalam recap
525 INSERT INTO tt_cb_check_balance_for_result
526 (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
527 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
528 FROM tt_cb_check_balance_for_cashbank_balance A
529 WHERE NOT EXISTS (
530 SELECT 1 FROM tt_cb_check_balance_for_recap B
531 WHERE A.session_id = B.session_id
532 AND A.tenant_id = B.tenant_id
533 AND A.cashbank_id = B.cashbank_id
534 AND A.cash_bank_date = B.cash_bank_date
535 AND A.rec_type = B.rec_type
536 AND A.curr_code = B.curr_code
537 ) AND A.session_id = pSessionId;
538
539
540 -- 15. Simpan selisih amount recap terhadap cashbank balance
541 INSERT INTO tt_cb_check_balance_for_result
542 (session_id, tenant_id, cashbank_id, cash_bank_date, rec_type, curr_code, trx_amount, balance_amount, diff_amount)
543 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
544 FROM tt_cb_check_balance_for_cashbank_balance A
545 INNER JOIN tt_cb_check_balance_for_recap B
546 ON A.session_id = B.session_id
547 AND A.tenant_id = B.tenant_id
548 AND A.cashbank_id = B.cashbank_id
549 AND A.cash_bank_date = B.cash_bank_date
550 AND A.rec_type = B.rec_type
551 AND A.curr_code = B.curr_code
552 AND A.session_id = pSessionId;
553
554
555 -- 16. Cari data yang tidak ada di jurnal
556 INSERT INTO tt_cb_check_balance_for_gl_journal_trx
557 (session_id, tenant_id, doc_type_id, doc_id, doc_no, doc_date, curr_code)
558 SELECT pSessionId, pTenantId, A.doc_type_id, A.doc_id, A.doc_no, A.doc_date, C.curr_code
559 FROM tt_cb_check_balance_for_trx A
560 INNER JOIN m_cashbank C ON A.cashbank_id = C.cashbank_id
561 WHERE A.session_id = pSessionId
562 AND A.status_doc = vStatusRelease
563 AND NOT EXISTS (
564 SELECT 1 FROM gl_journal_trx B
565 WHERE B.tenant_id = A.tenant_id
566 AND B.doc_type_id = A.doc_type_id
567 AND B.doc_id = A.doc_id
568 AND B.doc_no = A.doc_no
569 AND B.doc_date = A.doc_date
570 );
571
572 -- 17. Cari data yang tidak ada di detail jurnal
573 EXECUTE '
574 INSERT INTO tt_cb_check_balance_for_gl_journal_trx_details
575 (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)
576 SELECT $1, $2, A.doc_type_id, A.doc_id, A.doc_no, A.doc_date, A.cashbank_id, D.curr_code, A.amount, $5, 0, -99
577 FROM tt_cb_check_balance_for_trx A
578 INNER JOIN m_cashbank D ON A.cashbank_id = D.cashbank_id
579 WHERE A.session_id = $1
580 AND A.status_doc = $4
581 AND NOT EXISTS (
582 SELECT 1 FROM vw_gl_journal_trx_details B
583 INNER JOIN gl_journal_trx C ON B.journal_trx_id = C.journal_trx_id
584 WHERE A.tenant_id = B.tenant_id
585 AND C.doc_type_id = A.doc_type_id
586 AND C.doc_id = A.doc_id
587 AND C.doc_no = A.doc_no
588 AND C.doc_date = A.doc_date
589 AND A.amount = B.amount
590 AND C.tenant_id = $2
591 AND SUBSTR(C.doc_date, 1, 6) = $3'
592 || vFilterOuBuId
593 || vFilterCashbankIdC || '
594 )'
595 USING pSessionId, pTenantId, pYearMonth, vStatusRelease, vSpaceValue;
596
597 UPDATE tt_cb_check_balance_for_gl_journal_trx_details A
598 SET gl_detail_curr_code = B.curr_code, gl_detail_amount = B.amount, journal_trx_id = B.journal_trx_id
599 FROM vw_gl_journal_trx_details B
600 INNER JOIN gl_journal_trx C ON B.journal_trx_id = C.journal_trx_id
601 WHERE A.tenant_id = B.tenant_id
602 AND C.doc_type_id = A.doc_type_id
603 AND C.doc_id = A.doc_id
604 AND C.doc_no = A.doc_no
605 AND C.doc_date = A.doc_date
606 AND A.cashbank_id = B.cashbank_id;
607
608END;
609$BODY$
610 LANGUAGE plpgsql VOLATILE
611 COST 100;
612 /