· 8 years ago · Aug 16, 2018, 08:26 AM
1/**
2 * Adrian, Aug 16, 2018
3 */
4
5CREATE OR REPLACE FUNCTION gl_process_journal_formula_recurring(character varying, bigint, bigint, character varying, character varying, bigint)
6 RETURNS void AS
7$BODY$
8DECLARE
9 pSessionId ALIAS FOR $1;
10 pTenantId ALIAS FOR $2;
11 pJournalFormulaId ALIAS FOR $3;
12 pYearMonth ALIAS FOR $4;
13 pDatetime ALIAS FOR $5;
14 pUserId ALIAS FOR $6;
15
16 vEmptyId bigint;
17 vRoundingAmount integer;
18 vEmptyValue character varying(1);
19 vYes character varying(1);
20 vFinal character varying(1);
21 vOuId bigint;
22 vGlLedgerCode character varying;
23 vStatusLedgerDone character varying;
24 vExtDocNo character varying;
25 vExtDocDate character varying;
26
27 vJournalTypeJournalVoucher character varying(20);
28 vDocTypeIdJournalVOucher bigint;
29 vStatusDraft character varying(1);
30 vTypeRate character varying(3);
31 vFLAG_SOURCE_COA character varying(3);
32 vEomDate character varying(8);
33 vJournalTrxId bigint;
34
35BEGIN
36
37 vEmptyId := -99;
38 vEmptyValue := ' ';
39 vYes := 'Y';
40 vFinal := 'F';
41 vGlLedgerCode := 'GL';
42 vStatusLedgerDone := '1';
43
44 vTypeRate := 'COM';
45 vStatusDraft := 'D';
46
47 -- variable untuk tulis ke tabel jurnal trx
48 vDocTypeIdJournalVoucher := 721;
49 vFLAG_SOURCE_COA := 'COA';
50
51 vRoundingAmount := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
52
53 vEomDate := TO_CHAR(TO_DATE(pYearMonth||'01','YYYYMMDD') + INTERVAL '1 MONTH - 1 DAY', 'YYYYMMDD');
54
55 SELECT journal_type FROM m_document_journal WHERE doc_type_id = vDocTypeIdJournalVoucher INTO vJournalTypeJournalVoucher;
56
57 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
58
59 -- Validasi Journal Formula ada
60 SELECT A.ou_id, A.ext_doc_no, A.ext_doc_date
61 INTO vOuId, vExtDocNo, vExtDocDate
62 FROM gl_journal_formula A
63 WHERE A.journal_formula_id = pJournalFormulaId
64 AND A.tenant_id = pTenantId
65 AND A.flg_validate = vYes;
66
67 IF NOT FOUND THEN
68 RAISE EXCEPTION 'Journal Formula Recurring is not found or has is not valid yet';
69 END IF;
70
71 IF (SELECT 1 FROM m_admin_process_ledger e, m_ou_structure f
72 WHERE e.tenant_id = pTenantId AND e.ou_id = f.ou_bu_id AND f.ou_id = vOuId AND
73 e.date_year_month = pYearMonth AND e.ledger_code = vGlLedgerCode
74 AND e.status_ledger = vStatusLedgerDone) THEN
75 RAISE EXCEPTION 'Ledger GL for OU % and Period % already closed', vOuId, pYearMonth;
76 END IF;
77
78 -- Insert detail recurring ke table tamp
79 INSERT INTO tt_gl_journal_formula_recurring(
80 session_id, journal_formula_recurring_id, tenant_id, journal_formula_id,
81 line_no, recurring_curr_code, recurring_amount, recurring_rate_date,
82 sign_journal, ou_branch_id, sub_ou_id, coa_id, ou_rc_id, segment_id,
83 amount_factor_x, amount_factor_y, remark, used_recurr_amount, balance_used_amount, rounding_amount,
84 start_periode, end_periode, journal_trx_id)
85 SELECT pSessionId, C.journal_formula_recurring_id, C.tenant_id, C.journal_formula_id,
86 C.line_no, C.recurring_curr_code, C.recurring_amount, C.recurring_rate_date,
87 C.sign_journal, C.ou_branch_id, C.sub_ou_id, C.coa_id, C.ou_rc_id, C.segment_id,
88 C.amount_factor_x, C.amount_factor_y, C.remark,
89 ROUND(C.recurring_amount * C.amount_factor_x / C.amount_factor_y, vRoundingAmount), 0, 0,
90 B.start_periode, B.end_periode, vEmptyId
91 FROM gl_journal_formula A INNER JOIN gl_journal_formula_process B ON A.journal_formula_id = B.journal_formula_id
92 INNER JOIN gl_journal_formula_recurring C ON A.journal_formula_id = C.journal_formula_id
93 WHERE A.tenant_id = pTenantId AND
94 A.flg_validate = vYes AND
95 pYearMonth BETWEEN B.start_periode AND B.end_periode AND
96 A.journal_formula_id = pJournalFormulaId AND
97 NOT EXISTS ( SELECT 1 FROM gl_admin_process_formula E
98 WHERE E.tenant_id = A.tenant_id AND
99 E.journal_formula_id = A.journal_formula_id AND
100 E.year_month = pYearMonth AND
101 E.flag_process = vYes);
102
103 -- Update saldo terakhir ke table temp
104 UPDATE tt_gl_journal_formula_recurring A SET balance_used_amount = B.used_recurr_amount
105 FROM gl_journal_formula_recurring_balance B
106 WHERE A.session_id = pSessionId AND
107 A.journal_formula_recurring_id = B.journal_formula_recurring_id;
108
109 -- Untuk periode terakhir, hitung pembulatan
110 UPDATE tt_gl_journal_formula_recurring A SET rounding_amount = recurring_amount - used_recurr_amount - balance_used_amount
111 WHERE A.session_id = pSessionId AND
112 A.end_periode = pYearMonth;
113
114
115
116 -- Update balance untuk nilai used amount, dan sisa bulan
117 -- untuk process yang sudah pernah dijalankan
118 UPDATE gl_journal_formula_recurring_balance A SET used_recurr_amount = B.used_recurr_amount + B.rounding_amount,
119 rest_of_month = A.rest_of_month - 1,
120 recurr_on_the_road = A.recurr_on_the_road + B.used_recurr_amount + B.rounding_amount,
121 total_rounding_amount = A.total_rounding_amount + B.rounding_amount,
122 update_datetime = pDatetime,
123 update_user_id = pUserId,
124 version = version + 1
125 FROM tt_gl_journal_formula_recurring B
126 WHERE B.session_id = pSessionId AND
127 A.journal_formula_id = B.journal_formula_id AND
128 A.journal_formula_recurring_id = B.journal_formula_recurring_id AND
129 --B.start_periode <> pYearMonth
130 EXISTS (
131 SELECT 1
132 FROM gl_journal_formula_recurring_balance Z
133 WHERE Z.tenant_id = B.tenant_id
134 AND Z.journal_formula_recurring_id = B.journal_formula_recurring_id
135 );
136
137 -- Insert saldo untuk process pertama kali
138 INSERT INTO gl_journal_formula_recurring_balance(
139 tenant_id, journal_formula_recurring_id, journal_formula_id,
140 beg_recurr_amount, used_recurr_amount,
141 rest_of_month,
142 recurr_on_the_road, total_rounding_amount,
143 create_datetime, create_user_id, update_datetime, update_user_id, version)
144 SELECT A.tenant_id, A.journal_formula_recurring_id, A.journal_formula_id,
145 A.recurring_amount - (A.used_recurr_amount + A.rounding_amount), A.used_recurr_amount + A.rounding_amount,
146 DATE_PART('YEAR',
147 AGE(TO_TIMESTAMP(A.end_periode, 'YYYYMM'), TO_TIMESTAMP(A.start_periode, 'YYYYMM'))
148 ) * 12 + DATE_PART('MONTH',
149 AGE(TO_TIMESTAMP(A.end_periode, 'YYYYMM'), TO_TIMESTAMP(A.start_periode, 'YYYYMM'))
150 ),
151 A.used_recurr_amount + A.rounding_amount , A.rounding_amount,
152 pDatetime, pUserId, pDatetime, pUserId, 0
153 FROM tt_gl_journal_formula_recurring A
154 WHERE A.session_id = pSessionId AND
155 --A.start_periode = pYearMonth
156 NOT EXISTS (
157 SELECT 1
158 FROM gl_journal_formula_recurring_balance Z
159 WHERE Z.tenant_id = A.tenant_id
160 AND Z.journal_formula_recurring_id = A.journal_formula_recurring_id
161 );
162
163 -- Insert log recurring
164 INSERT INTO gl_log_journal_formula_recurring_balance
165 (tenant_id, journal_formula_recurring_id,
166 journal_formula_id, year_month, used_recurr_amount, rounding_amount,
167 create_datetime, create_user_id, update_datetime, update_user_id, version)
168 SELECT A.tenant_id, A.journal_formula_recurring_id,
169 A.journal_formula_id, pYearMonth, A.used_recurr_amount + A.rounding_amount, A.rounding_amount,
170 pDatetime, pUserId, pDatetime, pUserId, 0
171 FROM tt_gl_journal_formula_recurring A
172 WHERE A.session_id = pSessionId;
173
174 -- Insert admin process formula
175 INSERT INTO gl_admin_process_formula
176 (tenant_id, ou_id, journal_formula_id, journal_trx_id,
177 year_month, flag_process, process_datetime, process_user_id,
178 version, create_datetime, create_user_id, update_datetime, update_user_id)
179 SELECT pTenantId, vOuId , A.journal_formula_id, vJournalTrxId,
180 pYearMonth, vYes, pDatetime, pUserId,
181 0, pDatetime, pUserId, pDatetime,pUserId
182 FROM tt_gl_journal_formula_recurring A
183 WHERE A.session_id = pSessionId;
184
185 -- Update journal_trx_id pada table temp
186 UPDATE tt_gl_journal_formula_recurring A SET journal_trx_id = vJournalTrxId
187 WHERE A.session_id = pSessionId;
188
189 -- Update gl_journal_formula_process
190 UPDATE gl_journal_formula_process A SET flg_process = vYes, last_process = pYearMonth,
191 update_datetime = pDatetime, update_user_id = pUserId,
192 version = version + 1
193 WHERE A.journal_formula_id = pJournalFormulaId;
194
195 -- Update flg_process pada gl_journal_formula_process menjadi Final
196 -- Jika last_process = end_periode
197 UPDATE gl_journal_formula_process A SET flg_process = vFinal
198 WHERE A.journal_formula_id = pJournalFormulaId
199 AND A.last_process = A.end_periode;
200
201 -- Insert header Journal Voucher
202 INSERT INTO gl_journal_trx
203 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
204 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
205 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
206 "version", create_datetime, create_user_id, update_datetime, update_user_id)
207 SELECT vJournalTrxId, A.tenant_id, vJournalTypeJournalVoucher, vDocTypeIdJournalVoucher, vJournalTrxId,
208 'JR'||'/'||A.journal_formula_id||'/'|| pYearMonth, vEomDate,
209 A.ou_id, A.ou_branch_id, A.sub_ou_id, vEmptyId, vEmptyId, vEmptyId, vExtDocNo, vExtDocDate,
210 vEmptyId, A.journal_formula_id, vEomDate,
211 f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), A.formula_desc || ' - ' || A.formula_desc, vStatusDraft, 'DRAFT',
212 0, pDatetime, pUserId, pDatetime, pUserId
213 FROM gl_journal_formula A
214 WHERE A.journal_formula_id = pJournalFormulaId;
215
216
217 INSERT INTO tt_journal_trx_item
218 (session_id, tenant_id, journal_trx_id, line_no,
219 ref_doc_type_id, ref_id,
220 partner_id, product_id, cashbank_id, ou_rc_id,
221 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
222 coa_id, curr_code, qty, uom_id,
223 amount, journal_date, type_rate,
224 numerator_rate, denominator_rate, journal_desc, remark)
225 SELECT pSessionId, A.tenant_id, A.journal_trx_id, A.line_no,
226 vEmptyId, A.journal_formula_recurring_id,
227 vEmptyId, vEmptyId, vEmptyId, A.ou_rc_id,
228 vEmptyId, A.sign_journal, vFLAG_SOURCE_COA, vEmptyId,
229 A.coa_id, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), 0, vEmptyId,
230 A.used_recurr_amount + A.rounding_amount, vEomDate, vTypeRate,
231 1, 1, 'JF.' || pYearMonth, A.remark
232 FROM tt_gl_journal_formula_recurring A
233 WHERE A.session_id = pSessionId;
234
235 -- Insert item Journal Voucher
236 -- Untuk mapping Journal Formula Recurring
237 INSERT INTO gl_journal_trx_item
238 (tenant_id, journal_trx_id, line_no,
239 ref_doc_type_id, ref_id,
240 partner_id, product_id, cashbank_id, ou_rc_id,
241 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
242 coa_id, curr_code, qty, uom_id,
243 amount, journal_date, type_rate,
244 numerator_rate, denominator_rate, gl_curr_code, gl_amount, journal_desc, remark,
245 "version", create_datetime, create_user_id, update_datetime, update_user_id)
246 SELECT B.tenant_id, B.journal_trx_id, 1,
247 vEmptyId, A.journal_formula_mapping_id,
248 vEmptyId, vEmptyId, vEmptyId, A.ou_rc_id,
249 A.segment_id, A.sign_journal, vFLAG_SOURCE_COA, vEmptyId,
250 A.coa_id, B.recurring_curr_code, 0, vEmptyId,
251 B.amount, vEomDate, vTypeRate,
252 1, 1, B.recurring_curr_code, B.amount, 'JF.' || pYearMonth, A.remark,
253 0, pDatetime, pUserId, pDatetime, pUserId
254 FROM gl_journal_formula_mapping A,
255 (SELECT tenant_id, journal_trx_id, journal_formula_id, recurring_curr_code, SUM(used_recurr_amount + rounding_amount) AS amount
256 FROM tt_gl_journal_formula_recurring
257 WHERE session_id = pSessionId
258 GROUP BY tenant_id, journal_trx_id, journal_formula_id, recurring_curr_code) B
259 WHERE A.journal_formula_id = B.journal_formula_id;
260
261 -- Insert item Journal Voucher
262 -- Untuk detail Journal Formula Recurring
263 INSERT INTO gl_journal_trx_item
264 (tenant_id, journal_trx_id, line_no,
265 ref_doc_type_id, ref_id,
266 partner_id, product_id, cashbank_id, ou_rc_id,
267 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
268 coa_id, curr_code, qty, uom_id,
269 amount, journal_date, type_rate,
270 numerator_rate, denominator_rate, gl_curr_code, gl_amount, journal_desc, remark,
271 "version", create_datetime, create_user_id, update_datetime, update_user_id)
272 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id) + 1,
273 A.ref_doc_type_id, A.ref_id,
274 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
275 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
276 A.coa_id, A.curr_code, A.qty, A.uom_id,
277 A.amount, A.journal_date, A.type_rate,
278 A.numerator_rate, A.denominator_rate, A.curr_code, A.amount, A.journal_desc, A.remark,
279 0, pDatetime, pUserId, pDatetime, pUserId
280 FROM tt_journal_trx_item A
281 WHERE A.session_id = pSessionId;
282
283 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
284
285 DELETE FROM tt_gl_journal_formula_recurring WHERE session_id = pSessionId;
286
287 END;
288$BODY$
289 LANGUAGE plpgsql VOLATILE
290 COST 100;