· 8 years ago · Mar 29, 2018, 08:42 AM
1/**
2 * Adrian, Mar 27, 2018
3 * Function untuk membuat PO Internal dari table temporary
4 */
5CREATE OR REPLACE FUNCTION pu_create_po_internal_from_temporary(bigint, character varying, bigint, bigint, bigint, character varying)
6 RETURNS void AS
7$BODY$
8DECLARE
9 pTenantId ALIAS FOR $1;
10 pSessionId ALIAS FOR $2;
11 pTempPoInternalId ALIAS FOR $3;
12 pUserId ALIAS FOR $4;
13 pRoleId ALIAS FOR $5;
14 pDatetime ALIAS FOR $6;
15
16 vDocTypeIdInternalPurchasing bigint := 151;
17 vDocTypeIdSalesInvoice bigint := 321;
18 vPoInternalScheme character varying := 'CE01';
19 vParamCodeRoundingModeNonTax character varying := 'rounding.mode.non.tax';
20 vParamCodeRoundingScaleNonTax character varying := 'rounding.scale.non.tax';
21 vParamCodeRoundingModeTax character varying := 'rounding.mode.tax';
22 vParamCodeRoundingScaleTax character varying := 'rounding.scale.tax';
23 vParamCodeValutaResmi character varying := 'ValutaResmi';
24 vGroupPartnerCodeCustomer character varying := 'C';
25 vGroupPartnerCodeSupplier character varying := 'S';
26 vTypePartnerCodeCustomerInternal character varying := 'CUSI';
27 vTypePartnerCodeSupplierInternal character varying := 'SUPI';
28 vFlagCashbankCash character varying := 'C';
29 vComboIdYesNo character varying := 'YESNO';
30 vComboIdCurrency character varying := 'CURRENCY';
31 vStatusRelease character varying := 'R';
32 vWorkflowApproved character varying := 'APPROVED';
33 vRoundingModeDown character varying := 'RD';
34 vFlagOuPoSell character varying := 'S';
35 vFlgUserRole character varying := 'R';
36 vYes character varying := 'Y';
37 vNo character varying := 'N';
38 vEmptyId bigint := -99;
39 vEmptyValue character varying := '';
40
41 vTempPoInternalHeader RECORD;
42 vRounding integer;
43 vRoundingMode character varying;
44 vRoundingTax integer;
45 vRoundingModeTax character varying;
46 vValutaResmi character varying;
47 vDecimalForRounding integer;
48 vPoInternalId bigint;
49 vGrandTotal numeric;
50 vCbInsufficientList text;
51 vPoInternalFlowId bigint;
52
53BEGIN
54
55 DELETE FROM cb_insufficient_cashbank_balance WHERE session_id = pSessionId;
56
57 -- Mendapatkan default approval flow ID
58 IF NOT EXISTS (
59 SELECT 1
60 FROM awe_flow
61 WHERE scheme = vPoInternalScheme AND
62 flg_validate = vYes AND
63 active = vYes
64 ) THEN
65 RAISE EXCEPTION 'No Approval Flow for PO Internal';
66 ELSE
67 SELECT awe_flow_id INTO vPoInternalFlowId
68 FROM awe_flow
69 WHERE scheme = vPoInternalScheme AND
70 flg_validate = vYes AND
71 active = vYes;
72 END IF;
73
74 --Validasi data temporary untuk header ada
75 IF NOT EXISTS (
76 SELECT 1
77 FROM tt_po_internal A
78 WHERE A.session_id = pSessionId AND
79 A.tenant_id = pTenantId AND
80 A.tt_po_internal_id = pTempPoInternalId
81 ) THEN
82 RAISE EXCEPTION 'Temporary PO Internal is not found';
83 END IF;
84
85 SELECT A.*
86 FROM tt_po_internal A
87 WHERE A.session_id = pSessionId AND
88 A.tenant_id = pTenantId AND
89 A.tt_po_internal_id = pTempPoInternalId
90 INTO vTempPoInternalHeader;
91
92 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingModeNonTax) INTO vRoundingMode;
93 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingScaleNonTax) INTO vRounding;
94 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingModeTax) INTO vRoundingModeTax;
95 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingScaleTax) INTO vRoundingTax;
96 SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeValutaResmi) INTO vValutaResmi;
97 SELECT f_get_digit_decimal_doc_curr(vDocTypeIdInternalPurchasing, vTempPoInternalHeader.curr_code) INTO vDecimalForRounding;
98
99 --Validasi data temporary untuk item ada
100 IF NOT EXISTS (
101 SELECT 1
102 FROM tt_po_internal A
103 INNER JOIN tt_po_internal_item B
104 ON A.session_id = B.session_id AND
105 A.tenant_id = B.tenant_id AND
106 A.tt_po_internal_id = B.tt_po_internal_id
107 WHERE A.session_id = pSessionId AND
108 A.tenant_id = pTenantId AND
109 A.tt_po_internal_id = pTempPoInternalId
110 ) THEN
111 RAISE EXCEPTION 'Temporary PO Internal item is not found';
112 END IF;
113
114 --Validasi data temporary untuk product ada
115 IF NOT EXISTS (
116 SELECT 1
117 FROM tt_po_internal A
118 INNER JOIN tt_po_internal_item B
119 ON A.session_id = B.session_id AND
120 A.tenant_id = B.tenant_id AND
121 A.tt_po_internal_id = B.tt_po_internal_id
122 INNER JOIN tt_po_internal_product C
123 ON B.session_id = C.session_id AND
124 B.tenant_id = C.tenant_id AND
125 B.tt_po_internal_item_id = C.tt_po_internal_item_id
126 WHERE A.session_id = pSessionId AND
127 A.tenant_id = pTenantId AND
128 A.tt_po_internal_id = pTempPoInternalId
129 ) THEN
130 RAISE EXCEPTION 'Temporary PO Internal product is not found';
131 END IF;
132
133 --Validasi product_id pada item dan product_id pada product sama
134 IF EXISTS (
135 SELECT 1
136 FROM tt_po_internal A
137 INNER JOIN tt_po_internal_item B
138 ON A.session_id = B.session_id AND
139 A.tenant_id = B.tenant_id AND
140 A.tt_po_internal_id = B.tt_po_internal_id
141 INNER JOIN tt_po_internal_product C
142 ON B.session_id = C.session_id AND
143 B.tenant_id = C.tenant_id AND
144 B.tt_po_internal_item_id = C.tt_po_internal_item_id
145 INNER JOIN in_product_balance_stock D
146 ON C.product_balance_stock_id = D.product_balance_stock_id AND
147 C.tenant_id = D.tenant_id
148 WHERE A.session_id = pSessionId AND
149 A.tenant_id = pTenantId AND
150 A.tt_po_internal_id = pTempPoInternalId AND
151 (B.product_id <> D.product_id OR B.product_status <> D.product_status)
152 ) THEN
153 RAISE EXCEPTION 'Temporary PO Internal product is not match with item';
154 END IF;
155
156 --Validasi tanggal dokumen harus <= tanggal sistem
157 IF (vTempPoInternalHeader.doc_date > TO_CHAR(CURRENT_DATE, 'YYYYMMDD')::character varying) THEN
158 RAISE EXCEPTION 'Doc Date must <= Today';
159 END IF;
160
161 --Validasi curr_code adalah valuta resmi
162 IF (vTempPoInternalHeader.curr_code <> vValutaResmi) THEN
163 RAISE EXCEPTION 'PO Internal must use government tax currency';
164 END IF;
165
166 --Validasi ou_buy_id ada
167 IF NOT EXISTS (
168 SELECT 1
169 FROM t_ou A
170 WHERE A.ou_id = vTempPoInternalHeader.ou_buy_id
171 ) THEN
172 RAISE EXCEPTION 'OU Id % not found', vTempPoInternalHeader.ou_buy_id;
173 END IF;
174
175 --Validasi ou_buy_id memiliki tenant yang sesuai dengan tenant_id
176 IF NOT EXISTS (
177 SELECT 1
178 FROM t_ou A
179 WHERE A.ou_id = vTempPoInternalHeader.ou_buy_id AND
180 A.tenant_id = vTempPoInternalHeader.tenant_id
181 ) THEN
182 RAISE EXCEPTION 'Data PO Internal cannot use data OU Buy from different tenant';
183 END IF;
184
185 --Validasi ou_buy_id active
186 IF NOT EXISTS (
187 SELECT 1
188 FROM t_ou A
189 WHERE A.ou_id = vTempPoInternalHeader.ou_buy_id AND
190 A.tenant_id = vTempPoInternalHeader.tenant_id AND
191 A.active = vYes
192 ) THEN
193 RAISE EXCEPTION 'Cannot use selected data OU Buy, because it is not active';
194 END IF;
195
196 --Validasi user-role terautorisasi untuk ou_buy_id
197 IF (f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, vTempPoInternalHeader.ou_buy_id) <> 1) THEN
198 RAISE EXCEPTION 'Current login user and role is not authorized for selected OU in field OU Buy Business Unit';
199 END IF;
200
201 --Validasi ou_buy_id merupakan main BU
202 IF EXISTS (
203 SELECT 1
204 FROM t_ou A
205 INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
206 WHERE A.ou_id = vTempPoInternalHeader.ou_buy_id AND
207 A.ou_parent_id <> vEmptyId AND
208 B.flg_branch = vYes AND
209 B.flg_sub_bu = vYes
210 ) THEN
211 RAISE EXCEPTION 'OU % is not a main business unit (main business unit means
212 cannot have parent ou, and does not have type branch or sub business unit)', vTempPoInternalHeader.ou_buy_id;
213 END IF;
214
215 --Validasi ou_sell_id ada
216 IF NOT EXISTS (
217 SELECT 1
218 FROM t_ou A
219 WHERE A.ou_id = vTempPoInternalHeader.ou_sell_id
220 ) THEN
221 RAISE EXCEPTION 'OU Id % not found', vTempPoInternalHeader.ou_sell_id;
222 END IF;
223
224 --Validasi ou_sell_id memiliki tenant yang sesuai dengan tenant_id
225 IF NOT EXISTS (
226 SELECT 1
227 FROM t_ou A
228 WHERE A.ou_id = vTempPoInternalHeader.ou_sell_id AND
229 A.tenant_id = vTempPoInternalHeader.tenant_id
230 ) THEN
231 RAISE EXCEPTION 'Data PO Internal cannot use data OU Sell from different tenant';
232 END IF;
233
234 --Validasi ou_sell_id active
235 IF NOT EXISTS (
236 SELECT 1
237 FROM t_ou A
238 WHERE A.ou_id = vTempPoInternalHeader.ou_sell_id AND
239 A.tenant_id = vTempPoInternalHeader.tenant_id AND
240 A.active = vYes
241 ) THEN
242 RAISE EXCEPTION 'Cannot use selected data OU Sell, because it is not active';
243 END IF;
244
245 --Validasi ou_sell_id merupakan main BU
246 IF EXISTS (
247 SELECT 1
248 FROM t_ou A
249 INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
250 WHERE A.ou_id = vTempPoInternalHeader.ou_sell_id AND
251 A.ou_parent_id <> vEmptyId AND
252 B.flg_branch = vYes AND
253 B.flg_sub_bu = vYes
254 ) THEN
255 RAISE EXCEPTION 'OU % is not a main business unit (main business unit means
256 cannot have parent ou, and does not have type branch or sub business unit)', vTempPoInternalHeader.ou_sell_id;
257 END IF;
258
259 --Validasi warehouse_buy_id ada
260 IF NOT EXISTS (
261 SELECT 1
262 FROM m_warehouse A
263 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id
264 ) THEN
265 RAISE EXCEPTION 'Warehouse Id % not found', vTempPoInternalHeader.warehouse_buy_id;
266 END IF;
267
268 --Validasi warehouse_buy_id memiliki tenant yang sesuai dengan tenant_id
269 IF NOT EXISTS (
270 SELECT 1
271 FROM m_warehouse A
272 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id
273 AND A.tenant_id = vTempPoInternalHeader.tenant_id
274 ) THEN
275 RAISE EXCEPTION 'Data PO Internal cannot use data Warehouse Buy from different tenant';
276 END IF;
277
278 --Validasi warehouse_buy_id active
279 IF NOT EXISTS (
280 SELECT 1
281 FROM m_warehouse A
282 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id AND
283 A.tenant_id = vTempPoInternalHeader.tenant_id AND
284 A.active = vYes
285 ) THEN
286 RAISE EXCEPTION 'Cannot use selected data Warehouse Buy, because it is not active';
287 END IF;
288
289 --Validasi warehouse_buy_id adalah milik ou_buy_id
290 IF NOT EXISTS (
291 SELECT 1
292 FROM m_warehouse_ou A
293 INNER JOIN t_ou B ON A.ou_id = B.ou_id
294 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id AND
295 (A.ou_id = vTempPoInternalHeader.ou_buy_id OR
296 B.ou_parent_id = vTempPoInternalHeader.ou_buy_id)
297 ) THEN
298 RAISE EXCEPTION 'Warehouse with id % is not belong to ou with id %',
299 vTempPoInternalHeader.warehouse_buy_id, vTempPoInternalHeader.ou_buy_id;
300 END IF;
301
302 --Validasi warehouse_buy_id adalah bukan warehouse outlet
303 IF EXISTS (
304 SELECT 1
305 FROM m_warehouse_ou A
306 INNER JOIN t_ou B ON A.ou_id = B.ou_id
307 INNER JOIN t_ou_type C ON B.ou_type_id = C.ou_type_id
308 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_buy_id AND
309 C.flg_bu = vYes AND
310 C.flg_accounting = vNo AND
311 C.flg_sub_bu = vYes AND
312 C.flg_branch = vNo
313 ) THEN
314 RAISE EXCEPTION 'Warehouse Buy must a warehouse coming from non outlet organization';
315 END IF;
316
317 --Validasi user-role terautorisasi untuk ou_buy_id
318 IF (f_authorize_user_role_policy_warehouse(pTenantId, pUserId, pRoleId, vTempPoInternalHeader.warehouse_buy_id) <> 1) THEN
319 RAISE EXCEPTION 'Current login user and role is not authorized for selected warehouse in field Warehouse Buy';
320 END IF;
321
322 --Validasi warehouse_sell_id ada
323 IF NOT EXISTS (
324 SELECT 1
325 FROM m_warehouse A
326 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id
327 ) THEN
328 RAISE EXCEPTION 'Warehouse Id % not found', vTempPoInternalHeader.warehouse_sell_id;
329 END IF;
330
331 --Validasi warehouse_sell_id memiliki tenant yang sesuai dengan tenant_id
332 IF NOT EXISTS (
333 SELECT 1
334 FROM m_warehouse A
335 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id AND
336 A.tenant_id = vTempPoInternalHeader.tenant_id
337 ) THEN
338 RAISE EXCEPTION 'Data PO Internal cannot use data Warehouse Sell from different tenant';
339 END IF;
340
341 --Validasi warehouse_sell_id active
342 IF NOT EXISTS (
343 SELECT 1
344 FROM m_warehouse A
345 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id AND
346 A.tenant_id = vTempPoInternalHeader.tenant_id AND
347 A.active = vYes
348 ) THEN
349 RAISE EXCEPTION 'Cannot use selected data Warehouse Sell, because it is not active';
350 END IF;
351
352 --Validasi warehouse_sell_id adalah milik ou_sell_id
353 IF NOT EXISTS (
354 SELECT 1
355 FROM m_warehouse_ou A
356 INNER JOIN t_ou B ON A.ou_id = B.ou_id
357 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id AND
358 (A.ou_id = vTempPoInternalHeader.ou_sell_id OR
359 B.ou_parent_id = vTempPoInternalHeader.ou_sell_id)
360 ) THEN
361 RAISE EXCEPTION 'Warehouse with id % is not belong to ou with id %',
362 vTempPoInternalHeader.warehouse_sell_id, vTempPoInternalHeader.ou_sell_id;
363 END IF;
364
365 --Validasi warehouse_sell_id adalah bukan warehouse outlet
366 IF EXISTS (
367 SELECT 1
368 FROM m_warehouse_ou A
369 INNER JOIN t_ou B ON A.ou_id = B.ou_id
370 INNER JOIN t_ou_type C ON B.ou_type_id = C.ou_type_id
371 WHERE A.warehouse_id = vTempPoInternalHeader.warehouse_sell_id AND
372 C.flg_bu = vYes AND
373 C.flg_accounting = vNo AND
374 C.flg_sub_bu = vYes AND
375 C.flg_branch = vNo
376 ) THEN
377 RAISE EXCEPTION 'Warehouse Sell must a warehouse coming from non outlet organization';
378 END IF;
379
380 --Validasi partner_buy_id ada
381 IF NOT EXISTS (
382 SELECT 1
383 FROM m_partner A
384 WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id
385 ) THEN
386 RAISE EXCEPTION 'Partner Id % not found', vTempPoInternalHeader.partner_buy_id;
387 END IF;
388
389 --Validasi partner_buy_id memiliki tenant yang sesuai dengan tenant_id
390 IF NOT EXISTS (
391 SELECT 1
392 FROM m_partner A
393 WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id AND
394 A.tenant_id = vTempPoInternalHeader.tenant_id
395 ) THEN
396 RAISE EXCEPTION 'Data PO Internal cannot use data Customer Internal from different tenant';
397 END IF;
398
399 --Validasi partner_buy_id active
400 IF NOT EXISTS (
401 SELECT 1
402 FROM m_partner A
403 WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id AND
404 A.tenant_id = vTempPoInternalHeader.tenant_id AND
405 A.active = vYes
406 ) THEN
407 RAISE EXCEPTION 'Cannot use selected data Customer Internal, because it is not active';
408 END IF;
409
410 --Validasi partner_buy_id merupakan customer
411 IF (f_authorize_partner_as_specific_group_partner(vTempPoInternalHeader.partner_buy_id, vGroupPartnerCodeCustomer) <> 1) THEN
412 RAISE EXCEPTION 'Selected partner with id % does not have
413 role setting as customer internal (please check Partner Settings)',
414 vTempPoInternalHeader.partner_buy_id;
415 END IF;
416
417 --Validasi partner_buy_id merupakan customer internal
418 IF NOT EXISTS (
419 SELECT 1
420 FROM m_partner_type A
421 INNER JOIN m_type_partner B ON A.type_partner_id = B.type_partner_id
422 WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id AND
423 A.tenant_id = vTempPoInternalHeader.tenant_id AND
424 B.type_partner_code = vTypePartnerCodeCustomerInternal
425 ) THEN
426 RAISE EXCEPTION 'Selected partner with id % does not have
427 role setting as customer internal (please check Partner Settings)',
428 vTempPoInternalHeader.partner_buy_id;
429 END IF;
430
431 --Validasi partner_buy_id ter-mapping ke dalam ou sesuai ou_buy_id
432 IF NOT EXISTS (
433 SELECT 1
434 FROM m_partner_ou A
435 WHERE A.partner_id = vTempPoInternalHeader.partner_buy_id AND
436 A.ou_id = vTempPoInternalHeader.ou_buy_id
437 ) THEN
438 RAISE EXCEPTION 'Selected customer internal with id %
439 does not have authorization for OU with id %',
440 vTempPoInternalHeader.partner_buy_id,
441 vTempPoInternalHeader.ou_buy_id;
442 END IF;
443
444 --Validasi partner_sell_id ada
445 IF NOT EXISTS (
446 SELECT 1
447 FROM m_partner A
448 WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id
449 ) THEN
450 RAISE EXCEPTION 'Partner Id % not found', vTempPoInternalHeader.partner_sell_id;
451 END IF;
452
453 --Validasi partner_sell_id memiliki tenant yang sesuai dengan tenant_id
454 IF NOT EXISTS (
455 SELECT 1
456 FROM m_partner A
457 WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id AND
458 A.tenant_id = vTempPoInternalHeader.tenant_id
459 ) THEN
460 RAISE EXCEPTION 'Data PO Internal cannot use data Supplier Internal from different tenant';
461 END IF;
462
463 --Validasi partner_sell_id active
464 IF NOT EXISTS (
465 SELECT 1
466 FROM m_partner A
467 WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id AND
468 A.tenant_id = vTempPoInternalHeader.tenant_id AND
469 A.active = vYes
470 ) THEN
471 RAISE EXCEPTION 'Cannot use selected data Supplier Internal, because it is not active';
472 END IF;
473
474 --Validasi partner_sell_id merupakan supplier
475 IF (f_authorize_partner_as_specific_group_partner(vTempPoInternalHeader.partner_sell_id, vGroupPartnerCodeSupplier) <> 1) THEN
476 RAISE EXCEPTION 'Selected partner with id % does not have
477 role setting as supplier internal (please check Partner Settings)',
478 vTempPoInternalHeader.partner_sell_id;
479 END IF;
480
481 --Validasi partner_sell_id merupakan supplier internal
482 IF NOT EXISTS (
483 SELECT 1
484 FROM m_partner_type A
485 INNER JOIN m_type_partner B ON A.type_partner_id = B.type_partner_id
486 WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id AND
487 A.tenant_id = vTempPoInternalHeader.tenant_id AND
488 B.type_partner_code = vTypePartnerCodeSupplierInternal
489 ) THEN
490 RAISE EXCEPTION 'Selected partner with id % does not have
491 role setting as supplier internal (please check Partner Settings)',
492 vTempPoInternalHeader.partner_sell_id;
493 END IF;
494
495 --Validasi partner_sell_id ter-mapping ke dalam ou sesuai ou_sell_id
496 IF NOT EXISTS (
497 SELECT 1
498 FROM m_partner_ou A
499 WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id AND
500 A.ou_id = vTempPoInternalHeader.ou_sell_id
501 ) THEN
502 RAISE EXCEPTION 'Selected supplier internal with id %
503 does not have authorization for OU with id %',
504 vTempPoInternalHeader.partner_sell_id,
505 vTempPoInternalHeader.ou_sell_id;
506 END IF;
507
508 --Validasi flg_settle harus sesuai dengan combo YESNO
509 IF NOT EXISTS (
510 SELECT 1
511 FROM m_partner A
512 WHERE A.partner_id = vTempPoInternalHeader.partner_sell_id
513 ) THEN
514 RAISE EXCEPTION 'Partner Id % not found', vTempPoInternalHeader.partner_sell_id;
515 END IF;
516
517 --Validasi flg_settle harus sesuai dengan combo YESNO
518 IF NOT EXISTS (
519 SELECT 1
520 FROM t_combo_value A
521 WHERE A.combo_id = vComboIdYesNo AND
522 A.code = vTempPoInternalHeader.flg_settle
523 ) THEN
524 RAISE EXCEPTION 'Value Settle is not valid ,should not have values : %',
525 vTempPoInternalHeader.flg_settle;
526 END IF;
527
528 --Jika flg_settle = Y, validasi cashbank
529 IF (vTempPoInternalHeader.flg_settle = vYes) THEN
530
531 --Validasi cashbank_buy_id ada
532 IF NOT EXISTS (
533 SELECT 1
534 FROM m_cashbank A
535 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id
536 ) THEN
537 RAISE EXCEPTION 'Cashbank Id % not found', vTempPoInternalHeader.cashbank_buy_id;
538 END IF;
539
540 --Validasi flg_casbank dari cashbank_buy_id adalah cash
541 IF NOT EXISTS (
542 SELECT 1
543 FROM m_cashbank A
544 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id AND
545 A.flg_cash_bank = vFlagCashbankCash
546 ) THEN
547 RAISE EXCEPTION 'PO Internal must use cash for cashbank sell / buy,
548 selected cashbank is not cash = %', vTempPoInternalHeader.cashbank_buy_id;
549 END IF;
550
551 --Validasi curr_code dari cashbank_buy_id sama dengan curr_code header PO Internal
552 IF NOT EXISTS (
553 SELECT 1
554 FROM m_cashbank A
555 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id AND
556 A.curr_code = vTempPoInternalHeader.curr_code
557 ) THEN
558 RAISE EXCEPTION 'PO Internal must use cash currency with same currency for cashbank sell / buy,
559 selected cashbank currency is not match with transaction currency = %', vTempPoInternalHeader.cashbank_buy_id;
560 END IF;
561
562 --Validasi cashbank_buy_id memiliki tenant yang sesuai dengan tenant_id
563 IF NOT EXISTS (
564 SELECT 1
565 FROM m_cashbank A
566 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id AND
567 A.tenant_id = vTempPoInternalHeader.tenant_id
568 ) THEN
569 RAISE EXCEPTION 'Data PO Internal cannot use data Cashbank Buy from different tenant';
570 END IF;
571
572 --Validasi cashbank_buy_id active
573 IF NOT EXISTS (
574 SELECT 1
575 FROM m_cashbank A
576 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_buy_id AND
577 A.tenant_id = vTempPoInternalHeader.tenant_id AND
578 A.active = vYes
579 ) THEN
580 RAISE EXCEPTION 'Cannot use selected data Cashbank Buy, because it is not active';
581 END IF;
582
583 --Validasi user-role terautorisasi untuk cashbank_buy_id
584 IF (f_authorize_user_role_policy_cashbank(pTenantId, pUserId, pRoleId, vTempPoInternalHeader.cashbank_buy_id) <> 1) THEN
585 RAISE EXCEPTION 'Current login user and role is not authorized for selected cash bank in field Cashbank Buy';
586 END IF;
587
588 --Validasi cashbank_sell_id ada
589 IF NOT EXISTS (
590 SELECT 1
591 FROM m_cashbank A
592 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id
593 ) THEN
594 RAISE EXCEPTION 'Cashbank Id % not found', vTempPoInternalHeader.cashbank_sell_id;
595 END IF;
596
597 --Validasi flg_casbank dari cashbank_sell_id adalah cash
598 IF NOT EXISTS (
599 SELECT 1
600 FROM m_cashbank A
601 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id AND
602 A.flg_cash_bank = vFlagCashbankCash
603 ) THEN
604 RAISE EXCEPTION 'PO Internal must use cash for cashbank sell / buy,
605 selected cashbank is not cash = %', vTempPoInternalHeader.cashbank_sell_id;
606 END IF;
607
608 --Validasi curr_code dari cashbank_sell_id sama dengan curr_code header PO Internal
609 IF NOT EXISTS (
610 SELECT 1
611 FROM m_cashbank A
612 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id AND
613 A.curr_code = vTempPoInternalHeader.curr_code
614 ) THEN
615 RAISE EXCEPTION 'PO Internal must use cash currency with same currency for cashbank sell / buy,
616 selected cashbank currency is not match with transaction currency = %', vTempPoInternalHeader.cashbank_sell_id;
617 END IF;
618
619 --Validasi cashbank_sell_id memiliki tenant yang sesuai dengan tenant_id
620 IF NOT EXISTS (
621 SELECT 1
622 FROM m_cashbank A
623 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id AND
624 A.tenant_id = vTempPoInternalHeader.tenant_id
625 ) THEN
626 RAISE EXCEPTION 'Data PO Internal cannot use data Cashbank Sell from different tenant';
627 END IF;
628
629 --Validasi cashbank_sell_id active
630 IF NOT EXISTS (
631 SELECT 1
632 FROM m_cashbank A
633 WHERE A.cashbank_id = vTempPoInternalHeader.cashbank_sell_id AND
634 A.tenant_id = vTempPoInternalHeader.tenant_id AND
635 A.active = vYes
636 ) THEN
637 RAISE EXCEPTION 'Cannot use selected data Cashbank Sell, because it is not active';
638 END IF;
639
640 END IF;
641
642 --Validasi curr_code harus sesuai dengan combo CURRENCY
643 IF NOT EXISTS (
644 SELECT 1
645 FROM t_combo_value A
646 WHERE A.combo_id = vComboIdCurrency AND
647 A.code = vTempPoInternalHeader.curr_code
648 ) THEN
649 RAISE EXCEPTION 'Value Currency is not valid ,should not have values : %',
650 vTempPoInternalHeader.curr_code;
651 END IF;
652
653 --Validasi flg_accept_tax harus sesuai dengan combo YESNO
654 IF NOT EXISTS (
655 SELECT 1
656 FROM t_combo_value A
657 WHERE A.combo_id = vComboIdYesNo AND
658 A.code = vTempPoInternalHeader.flg_accept_tax
659 ) THEN
660 RAISE EXCEPTION 'Value Acknowledge Tax is not valid ,should not have values : %',
661 vTempPoInternalHeader.flg_accept_tax;
662 END IF;
663
664 --Validasi unique key header PO Internal belum ada
665 IF EXISTS (
666 SELECT 1
667 FROM pu_po_internal A
668 WHERE A.tenant_id = vTempPoInternalHeader.tenant_id AND
669 A.doc_type_id = vDocTypeIdInternalPurchasing AND
670 A.doc_date = vTempPoInternalHeader.doc_date AND
671 A.doc_no = vTempPoInternalHeader.doc_no AND
672 A.ou_buy_id = vTempPoInternalHeader.ou_buy_id
673 ) THEN
674 RAISE EXCEPTION 'PO Internal with doc date %, doc no %, and ou buy id % is already exists',
675 vTempPoInternalHeader.doc_date,
676 vTempPoInternalHeader.doc_no,
677 vTempPoInternalHeader.ou_buy_id;
678 END IF;
679
680 --Validasi qty_int item PO Internal > 0
681 IF EXISTS (
682 SELECT 1
683 FROM tt_po_internal A
684 INNER JOIN tt_po_internal_item B
685 ON A.session_id = B.session_id AND
686 A.tenant_id = B.tenant_id AND
687 A.tt_po_internal_id = B.tt_po_internal_id
688 WHERE A.session_id = pSessionId AND
689 A.tenant_id = pTenantId AND
690 A.tt_po_internal_id = pTempPoInternalId AND
691 B.qty_int <= 0
692 ) THEN
693 RAISE EXCEPTION 'Item Qty must > 0';
694 END IF;
695
696 --Validasi product_id item PO Internal ada
697 IF EXISTS (
698 SELECT 1
699 FROM tt_po_internal A
700 INNER JOIN tt_po_internal_item B
701 ON A.session_id = B.session_id AND
702 A.tenant_id = B.tenant_id AND
703 A.tt_po_internal_id = B.tt_po_internal_id
704 WHERE A.session_id = pSessionId AND
705 A.tenant_id = pTenantId AND
706 A.tt_po_internal_id = pTempPoInternalId AND
707 NOT EXISTS (
708 SELECT 1
709 FROM m_product C
710 WHERE B.product_id = C.product_id
711 )
712 ) THEN
713 RAISE EXCEPTION 'Product Id of PO Internal Item not found';
714 END IF;
715
716 --Validasi product_id item PO Internal memiliki tenant yang sesuai
717 IF EXISTS (
718 SELECT 1
719 FROM tt_po_internal A
720 INNER JOIN tt_po_internal_item B
721 ON A.session_id = B.session_id AND
722 A.tenant_id = B.tenant_id AND
723 A.tt_po_internal_id = B.tt_po_internal_id
724 WHERE A.session_id = pSessionId AND
725 A.tenant_id = pTenantId AND
726 A.tt_po_internal_id = pTempPoInternalId AND
727 NOT EXISTS (
728 SELECT 1
729 FROM m_product C
730 WHERE B.product_id = C.product_id AND
731 B.tenant_id = C.tenant_id
732 )
733 ) THEN
734 RAISE EXCEPTION 'Data PO Internal Item cannot use data Product from different tenant';
735 END IF;
736
737 --Validasi user-role terautorisasi untuk product_id item PO Internal
738 IF EXISTS (
739 SELECT 1
740 FROM tt_po_internal A
741 INNER JOIN tt_po_internal_item B
742 ON A.session_id = B.session_id AND
743 A.tenant_id = B.tenant_id AND
744 A.tt_po_internal_id = B.tt_po_internal_id
745 WHERE A.session_id = pSessionId AND
746 A.tenant_id = pTenantId AND
747 A.tt_po_internal_id = pTempPoInternalId AND
748 f_authorize_user_role_policy_product(pTenantId, pUserId, pRoleId, B.product_id) <> 1
749 ) THEN
750 RAISE EXCEPTION 'Current login user and role is not authorized for PO Internal Item product in field Product';
751 END IF;
752
753 --Validasi product_id item PO Internal terautorisasi dalam warehouse buy
754 IF EXISTS (
755 SELECT 1
756 FROM tt_po_internal A
757 INNER JOIN tt_po_internal_item B
758 ON A.session_id = B.session_id AND
759 A.tenant_id = B.tenant_id AND
760 A.tt_po_internal_id = B.tt_po_internal_id
761 INNER JOIN m_product C
762 ON B.product_id = C.product_id AND
763 B.tenant_id = C.tenant_id
764 WHERE A.session_id = pSessionId AND
765 A.tenant_id = pTenantId AND
766 A.tt_po_internal_id = pTempPoInternalId AND
767 NOT EXISTS(
768 SELECT 1
769 FROM m_item_warehouse D
770 WHERE B.tenant_id = D.tenant_id AND
771 A.warehouse_buy_id = D.warehouse_id AND
772 C.sub_ctgr_product_id = D.sub_ctgr_product_id
773 )
774 ) THEN
775 RAISE EXCEPTION 'Product id of PO Internal Item is not authorized in warehouse id';
776 END IF;
777
778 --Validasi tax_id item PO Internal ada
779 IF EXISTS (
780 SELECT 1
781 FROM tt_po_internal A
782 INNER JOIN tt_po_internal_item B
783 ON A.session_id = B.session_id AND
784 A.tenant_id = B.tenant_id AND
785 A.tt_po_internal_id = B.tt_po_internal_id
786 WHERE A.session_id = pSessionId AND
787 A.tenant_id = pTenantId AND
788 A.tt_po_internal_id = pTempPoInternalId AND
789 B.tax_id <> vEmptyId AND
790 NOT EXISTS (
791 SELECT 1
792 FROM m_tax C
793 WHERE B.tax_id = C.tax_id
794 )
795 ) THEN
796 RAISE EXCEPTION 'Tax Id of PO Internal Item not found';
797 END IF;
798
799 --Validasi tax_id item PO Internal memiliki tenant yang sesuai
800 IF EXISTS (
801 SELECT 1
802 FROM tt_po_internal A
803 INNER JOIN tt_po_internal_item B
804 ON A.session_id = B.session_id AND
805 A.tenant_id = B.tenant_id AND
806 A.tt_po_internal_id = B.tt_po_internal_id
807 WHERE A.session_id = pSessionId AND
808 A.tenant_id = pTenantId AND
809 A.tt_po_internal_id = pTempPoInternalId AND
810 B.tax_id <> vEmptyId AND
811 NOT EXISTS (
812 SELECT 1
813 FROM m_tax C
814 WHERE B.tax_id = C.tax_id AND
815 B.tenant_id = C.tenant_id
816 )
817 ) THEN
818 RAISE EXCEPTION 'Data PO Internal Item cannot use data Tax from different tenant';
819 END IF;
820
821 --Validasi tax_id item PO Internal active
822 IF EXISTS (
823 SELECT 1
824 FROM tt_po_internal A
825 INNER JOIN tt_po_internal_item B
826 ON A.session_id = B.session_id AND
827 A.tenant_id = B.tenant_id AND
828 A.tt_po_internal_id = B.tt_po_internal_id
829 WHERE A.session_id = pSessionId AND
830 A.tenant_id = pTenantId AND
831 A.tt_po_internal_id = pTempPoInternalId AND
832 B.tax_id <> vEmptyId AND
833 NOT EXISTS (
834 SELECT 1
835 FROM m_tax C
836 WHERE B.tax_id = C.tax_id AND
837 C.active = vYes
838 )
839 ) THEN
840 RAISE EXCEPTION 'Cannot use selected data Tax, because it is not active';
841 END IF;
842
843 --Validasi flg_tax_amount harus sesuai dengan combo YESNO
844 IF EXISTS (
845 SELECT 1
846 FROM tt_po_internal A
847 INNER JOIN tt_po_internal_item B
848 ON A.session_id = B.session_id AND
849 A.tenant_id = B.tenant_id AND
850 A.tt_po_internal_id = B.tt_po_internal_id
851 WHERE A.session_id = pSessionId AND
852 A.tenant_id = pTenantId AND
853 A.tt_po_internal_id = pTempPoInternalId AND
854 NOT EXISTS (
855 SELECT 1
856 FROM t_combo_value C
857 WHERE C.combo_id = vComboIdYesNo AND
858 C.code = B.flg_tax_amount
859 )
860 ) THEN
861 RAISE EXCEPTION 'Value Flag Tax Amount is not valid';
862 END IF;
863
864 --Validasi tax_id harus diisi jika flg_tax_amount = 'Y'
865 IF EXISTS (
866 SELECT 1
867 FROM tt_po_internal A
868 INNER JOIN tt_po_internal_item B
869 ON A.session_id = B.session_id AND
870 A.tenant_id = B.tenant_id AND
871 A.tt_po_internal_id = B.tt_po_internal_id
872 WHERE A.session_id = pSessionId AND
873 A.tenant_id = pTenantId AND
874 A.tt_po_internal_id = pTempPoInternalId AND
875 B.flg_tax_amount = vYes AND
876 B.tax_id = vEmptyId
877 ) THEN
878 RAISE EXCEPTION 'Gross Price is including tax, so you must choose tax value';
879 END IF;
880
881 --Rounding gross_price_po item PO Internal
882 UPDATE tt_po_internal_item Z
883 SET gross_price_po =
884 CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
885 TRUNC(Z.gross_price_po, vDecimalForRounding)
886 ELSE
887 ROUND(Z.gross_price_po, vDecimalForRounding)
888 END
889 FROM tt_po_internal A
890 WHERE A.session_id = pSessionId AND
891 A.tenant_id = pTenantId AND
892 A.tt_po_internal_id = pTempPoInternalId AND
893 A.session_id = Z.session_id AND
894 A.tenant_id = Z.tenant_id AND
895 A.tt_po_internal_id = Z.tt_po_internal_id;
896
897 --Hitung tax untuk item PO Internal dengan flg_tax_amount = 'Y'
898 UPDATE tt_po_internal_item Z
899 SET tax_percentage = B.percentage,
900 tax_price =
901 CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
902 TRUNC(Z.gross_price_po * B.percentage / (100 + B.percentage), vDecimalForRounding)
903 ELSE
904 ROUND(Z.gross_price_po * B.percentage / (100 + B.percentage), vDecimalForRounding)
905 END,
906 nett_price_po =
907 CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
908 Z.gross_price_po - TRUNC(Z.gross_price_po * B.percentage / (100 + B.percentage), vDecimalForRounding)
909 ELSE
910 Z.gross_price_po - ROUND(Z.gross_price_po * B.percentage / (100 + B.percentage), vDecimalForRounding)
911 END
912 FROM tt_po_internal A, m_tax B
913 WHERE A.session_id = pSessionId AND
914 A.tenant_id = pTenantId AND
915 A.tt_po_internal_id = pTempPoInternalId AND
916 A.session_id = Z.session_id AND
917 A.tenant_id = Z.tenant_id AND
918 A.tt_po_internal_id = Z.tt_po_internal_id AND
919 Z.flg_tax_amount = vYes AND
920 Z.tax_id <> vEmptyId AND
921 Z.tax_id = B.tax_id;
922
923 --Hitung nett price untuk item PO Internal dengan flg_tax_amount = 'N'
924 UPDATE tt_po_internal_item Z
925 SET nett_price_po = Z.gross_price_po
926 FROM tt_po_internal A
927 WHERE A.session_id = pSessionId AND
928 A.tenant_id = pTenantId AND
929 A.tt_po_internal_id = pTempPoInternalId AND
930 A.session_id = Z.session_id AND
931 A.tenant_id = Z.tenant_id AND
932 A.tt_po_internal_id = Z.tt_po_internal_id AND
933 Z.flg_tax_amount = vNo;
934
935 --Hitung tax untuk item PO Internal dengan flg_tax_amount = 'N' dan tax_id <> -99
936 UPDATE tt_po_internal_item Z
937 SET tax_percentage = B.percentage,
938 tax_price =
939 CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
940 TRUNC(Z.gross_price_po * B.percentage / 100, vDecimalForRounding)
941 ELSE
942 ROUND(Z.gross_price_po * B.percentage / 100, vDecimalForRounding)
943 END
944 FROM tt_po_internal A, m_tax B
945 WHERE A.session_id = pSessionId AND
946 A.tenant_id = pTenantId AND
947 A.tt_po_internal_id = pTempPoInternalId AND
948 A.session_id = Z.session_id AND
949 A.tenant_id = Z.tenant_id AND
950 A.tt_po_internal_id = Z.tt_po_internal_id AND
951 Z.flg_tax_amount = vNo AND
952 Z.tax_id <> vEmptyId AND
953 Z.tax_id = B.tax_id;
954
955 --Hitung amount untuk item PO Internal
956 UPDATE tt_po_internal_item Z
957 SET gross_item_amount = Z.gross_price_po * Z.qty_int,
958 nett_item_amount = Z.nett_price_po * Z.qty_int,
959 tax_amount = Z.tax_price * Z.qty_int
960 FROM tt_po_internal A
961 WHERE A.session_id = pSessionId AND
962 A.tenant_id = pTenantId AND
963 A.tt_po_internal_id = pTempPoInternalId AND
964 A.session_id = Z.session_id AND
965 A.tenant_id = Z.tenant_id AND
966 A.tt_po_internal_id = Z.tt_po_internal_id;
967
968 --Jika flg_settle = Y, validasi cashbank balance cukup
969 IF (vTempPoInternalHeader.flg_settle = vYes) THEN
970
971 --Simpan grand total
972 SELECT SUM(B.nett_item_amount) + SUM(B.tax_amount)
973 FROM tt_po_internal A
974 INNER JOIN tt_po_internal_item B
975 ON A.session_id = B.session_id AND
976 A.tenant_id = B.tenant_id AND
977 A.tt_po_internal_id = B.tt_po_internal_id
978 WHERE A.session_id = pSessionId AND
979 A.tenant_id = pTenantId AND
980 A.tt_po_internal_id = pTempPoInternalId
981 INTO vGrandTotal;
982
983 --Validasi cashbank balance cukup
984 PERFORM f_val_sufficient_cashbank_balance(pSessionId, vTempPoInternalHeader.cashbank_buy_id, vTempPoInternalHeader.doc_date, vGrandTotal);
985
986 UPDATE cb_insufficient_cashbank_balance
987 SET tenant_id = pTenantId,
988 ou_id = vTempPoInternalHeader.ou_buy_id,
989 doc_type_id = vDocTypeIdInternalPurchasing,
990 doc_no = vTempPoInternalHeader.doc_no,
991 create_datetime = pDatetime,
992 create_user_id = pUserId
993 WHERE session_id = pSessionId;
994
995 IF (SELECT COUNT(1) FROM cb_insufficient_cashbank_balance WHERE session_id = pSessionId) > 0 THEN
996 SELECT string_agg(cashbank_name||' with credit = '||credit::character varying, ',') INTO vCbInsufficientList
997 FROM cb_insufficient_cashbank_balance WHERE session_id = pSessionId;
998
999 RAISE EXCEPTION 'There is insufficient cashbank balance for cancel approved : %', vCbInsufficientList;
1000 END IF;
1001
1002 END IF;
1003
1004 --Validasi qty_int item PO Internal > 0
1005 IF EXISTS (
1006 SELECT 1
1007 FROM tt_po_internal A
1008 INNER JOIN tt_po_internal_item B
1009 ON A.session_id = B.session_id AND
1010 A.tenant_id = B.tenant_id AND
1011 A.tt_po_internal_id = B.tt_po_internal_id
1012 INNER JOIN tt_po_internal_product C
1013 ON B.session_id = C.session_id AND
1014 B.tenant_id = C.tenant_id AND
1015 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1016 WHERE A.session_id = pSessionId AND
1017 A.tenant_id = pTenantId AND
1018 A.tt_po_internal_id = pTempPoInternalId AND
1019 C.qty_int <= 0
1020 ) THEN
1021 RAISE EXCEPTION 'PO Internal Product Qty must > 0';
1022 END IF;
1023
1024 --Validasi qty_int item PO Internal > 0
1025 IF EXISTS (
1026 SELECT 1
1027 FROM tt_po_internal A
1028 INNER JOIN tt_po_internal_item B
1029 ON A.session_id = B.session_id AND
1030 A.tenant_id = B.tenant_id AND
1031 A.tt_po_internal_id = B.tt_po_internal_id
1032 INNER JOIN tt_po_internal_product C
1033 ON B.session_id = C.session_id AND
1034 B.tenant_id = C.tenant_id AND
1035 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1036 WHERE A.session_id = pSessionId AND
1037 A.tenant_id = pTenantId AND
1038 A.tt_po_internal_id = pTempPoInternalId AND
1039 C.qty_int <= 0
1040 ) THEN
1041 RAISE EXCEPTION 'PO Internal Product Qty must > 0';
1042 END IF;
1043
1044 --Validasi qty_int item PO Internal > 0
1045 IF EXISTS (
1046 SELECT 1
1047 FROM tt_po_internal A
1048 INNER JOIN tt_po_internal_item B
1049 ON A.session_id = B.session_id AND
1050 A.tenant_id = B.tenant_id AND
1051 A.tt_po_internal_id = B.tt_po_internal_id
1052 INNER JOIN tt_po_internal_product C
1053 ON B.session_id = C.session_id AND
1054 B.tenant_id = C.tenant_id AND
1055 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1056 WHERE A.session_id = pSessionId AND
1057 A.tenant_id = pTenantId AND
1058 A.tt_po_internal_id = pTempPoInternalId
1059 GROUP BY B.tt_po_internal_item_id
1060 HAVING SUM(C.qty_int) <> B.qty_int
1061 ) THEN
1062 RAISE EXCEPTION 'Total quantity in detail products not match with total quantity in the item with line no';
1063 END IF;
1064
1065 --Validasi product_balance_stock_id product PO Internal ada
1066 IF EXISTS (
1067 SELECT 1
1068 FROM tt_po_internal A
1069 INNER JOIN tt_po_internal_item B
1070 ON A.session_id = B.session_id AND
1071 A.tenant_id = B.tenant_id AND
1072 A.tt_po_internal_id = B.tt_po_internal_id
1073 INNER JOIN tt_po_internal_product C
1074 ON B.session_id = C.session_id AND
1075 B.tenant_id = C.tenant_id AND
1076 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1077 WHERE A.session_id = pSessionId AND
1078 A.tenant_id = pTenantId AND
1079 A.tt_po_internal_id = pTempPoInternalId AND
1080 NOT EXISTS (
1081 SELECT 1
1082 FROM in_product_balance_stock D
1083 WHERE C.product_balance_stock_id = D.product_balance_stock_id
1084 )
1085 ) THEN
1086 RAISE EXCEPTION 'Product Id of PO Internal Item not found';
1087 END IF;
1088
1089 --Validasi qty product_balance_stock masih cukup
1090 IF EXISTS (
1091 SELECT 1
1092 FROM tt_po_internal A
1093 INNER JOIN tt_po_internal_item B
1094 ON A.session_id = B.session_id AND
1095 A.tenant_id = B.tenant_id AND
1096 A.tt_po_internal_id = B.tt_po_internal_id
1097 INNER JOIN tt_po_internal_product C
1098 ON B.session_id = C.session_id AND
1099 B.tenant_id = C.tenant_id AND
1100 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1101 WHERE A.session_id = pSessionId AND
1102 A.tenant_id = pTenantId AND
1103 A.tt_po_internal_id = pTempPoInternalId AND
1104 EXISTS (
1105 SELECT 1
1106 FROM in_product_balance_stock D
1107 WHERE C.product_balance_stock_id = D.product_balance_stock_id AND
1108 D.qty < C.qty_int
1109 )
1110 ) THEN
1111 RAISE EXCEPTION 'PO Internal Product Qty must <= Stock Qty';
1112 END IF;
1113
1114 --Validasi product_status product_balance_stock ada
1115 IF EXISTS (
1116 SELECT 1
1117 FROM tt_po_internal A
1118 INNER JOIN tt_po_internal_item B
1119 ON A.session_id = B.session_id AND
1120 A.tenant_id = B.tenant_id AND
1121 A.tt_po_internal_id = B.tt_po_internal_id
1122 INNER JOIN tt_po_internal_product C
1123 ON B.session_id = C.session_id AND
1124 B.tenant_id = C.tenant_id AND
1125 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1126 INNER JOIN in_product_balance_stock D
1127 ON C.product_balance_stock_id = D.product_balance_stock_id AND
1128 C.tenant_id = D.tenant_id
1129 WHERE A.session_id = pSessionId AND
1130 A.tenant_id = pTenantId AND
1131 A.tt_po_internal_id = pTempPoInternalId AND
1132 NOT EXISTS (
1133 SELECT 1
1134 FROM m_product_status E
1135 WHERE D.product_status = E.product_status_code AND
1136 D.tenant_id = E.tenant_id
1137 )
1138 ) THEN
1139 RAISE EXCEPTION 'Product Status of PO Internal Product not found';
1140 END IF;
1141
1142 --Validasi product_status product_balance_stock sama dengan product status pada item
1143 IF EXISTS (
1144 SELECT 1
1145 FROM tt_po_internal A
1146 INNER JOIN tt_po_internal_item B
1147 ON A.session_id = B.session_id AND
1148 A.tenant_id = B.tenant_id AND
1149 A.tt_po_internal_id = B.tt_po_internal_id
1150 INNER JOIN tt_po_internal_product C
1151 ON B.session_id = C.session_id AND
1152 B.tenant_id = C.tenant_id AND
1153 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1154 INNER JOIN in_product_balance_stock D
1155 ON C.product_balance_stock_id = D.product_balance_stock_id
1156 WHERE A.session_id = pSessionId AND
1157 A.tenant_id = pTenantId AND
1158 A.tt_po_internal_id = pTempPoInternalId AND
1159 B.product_status <> D.product_status
1160 ) THEN
1161 RAISE EXCEPTION 'Product Status of PO Internal Product not match with item';
1162 END IF;
1163
1164 --Validasi product_status product_balance_stock masih cukup
1165 IF EXISTS (
1166 SELECT 1
1167 FROM tt_po_internal A
1168 INNER JOIN tt_po_internal_item B
1169 ON A.session_id = B.session_id AND
1170 A.tenant_id = B.tenant_id AND
1171 A.tt_po_internal_id = B.tt_po_internal_id
1172 INNER JOIN tt_po_internal_product C
1173 ON B.session_id = C.session_id AND
1174 B.tenant_id = C.tenant_id AND
1175 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1176 INNER JOIN in_product_balance_stock D
1177 ON C.product_balance_stock_id = D.product_balance_stock_id AND
1178 C.tenant_id = D.tenant_id
1179 WHERE A.session_id = pSessionId AND
1180 A.tenant_id = pTenantId AND
1181 A.tt_po_internal_id = pTempPoInternalId AND
1182 NOT EXISTS (
1183 SELECT 1
1184 FROM m_product_status E
1185 WHERE D.product_status = E.product_status_code AND
1186 D.tenant_id = E.tenant_id AND
1187 E.flg_sell = vYes
1188 )
1189 ) THEN
1190 RAISE EXCEPTION 'Product Status of PO Internal Product not found';
1191 END IF;
1192
1193
1194 SELECT NEXTVAL('pu_po_internal_seq') INTO vPoInternalId;
1195
1196 --Insert data header PO Internal sesuai header temporary
1197 INSERT INTO pu_po_internal(
1198 po_internal_id, tenant_id, doc_type_id, doc_no, doc_date, ou_buy_id,
1199 partner_sell_id, purchaser_id, warehouse_buy_id, ou_sell_id,
1200 partner_buy_id, salesman_id, warehouse_sell_id, ext_doc_no, ext_doc_date,
1201 ref_doc_type_id, ref_id, due_date, remark, flg_delivery, status_doc,
1202 workflow_status, version, create_datetime, create_user_id, update_datetime,
1203 update_user_id, flg_accept_tax, curr_code, cashbank_buy_id, cashbank_sell_id,
1204 flg_settle)
1205 SELECT vPoInternalId, A.tenant_id, vDocTypeIdInternalPurchasing, A.doc_no, A.doc_date, A.ou_buy_id,
1206 A.partner_sell_id, vEmptyId, A.warehouse_buy_id, A.ou_sell_id,
1207 A.partner_buy_id, vEmptyId, A.warehouse_sell_id, vEmptyValue, vEmptyValue,
1208 vEmptyId, vEmptyId, vEmptyValue, A.remark, vNo, vStatusRelease,
1209 vWorkflowApproved, 0, pDatetime, pUserId, pDatetime,
1210 pUserId, A.flg_accept_tax, A.curr_code, A.cashbank_buy_id, A.cashbank_sell_id,
1211 A.flg_settle
1212 FROM tt_po_internal A
1213 WHERE A.session_id = pSessionId AND
1214 A.tenant_id = pTenantId AND
1215 A.tt_po_internal_id = pTempPoInternalId;
1216
1217 --Insert data item PO Internal sesuai item temporary
1218 INSERT INTO pu_po_internal_item(
1219 tenant_id, po_internal_id, line_no, ref_doc_type_id,
1220 ref_id, product_id, curr_code, gross_price_po, flg_tax_amount,
1221 tax_id, tax_percentage, discount_percentage, discount_amount,
1222 nett_price_po, qty_int, base_uom_id, gross_item_amount, nett_item_amount,
1223 tax_amount, remark, version, create_datetime, create_user_id,
1224 update_datetime, update_user_id, tax_price, product_status)
1225 SELECT B.tenant_id, vPoInternalId, B.line_no, vEmptyId,
1226 vEmptyId, B.product_id, B.curr_code, B.gross_price_po, B.flg_tax_amount,
1227 B.tax_id, B.tax_percentage, B.discount_percentage, B.discount_amount,
1228 B.nett_price_po, B.qty_int, B.base_uom_id, B.gross_item_amount, B.nett_item_amount,
1229 B.tax_amount, vEmptyValue, 0, pDatetime, pUserId,
1230 pDatetime, pUserId, B.tax_price, B.product_status
1231 FROM tt_po_internal A
1232 INNER JOIN tt_po_internal_item B
1233 ON A.session_id = B.session_id AND
1234 A.tenant_id = B.tenant_id AND
1235 A.tt_po_internal_id = B.tt_po_internal_id
1236 WHERE A.session_id = pSessionId AND
1237 A.tenant_id = pTenantId AND
1238 A.tt_po_internal_id = pTempPoInternalId;
1239
1240 --Insert data tax PO Internal
1241 INSERT INTO pu_po_internal_tax(
1242 tenant_id, po_internal_id, tax_id, flg_amount,
1243 tax_percentage, base_amount,
1244 tax_amount,
1245 tax_no, tax_date, tax_curr_code,
1246 gov_tax_amount, remark, version, create_datetime, create_user_id,
1247 update_datetime, update_user_id)
1248 SELECT B.tenant_id, vPoInternalId, B.tax_id, C.flg_amount,
1249 C.percentage, SUM(B.nett_item_amount),
1250 CASE WHEN (vRoundingModeTax = vRoundingModeDown) THEN
1251 TRUNC(SUM(B.nett_item_amount) * C.percentage / 100, vRoundingTax)
1252 ELSE
1253 ROUND(SUM(B.nett_item_amount) * C.percentage / 100, vRoundingTax)
1254 END,
1255 vEmptyValue, vEmptyValue, C.tax_curr_code,
1256 0, vEmptyValue, 0, pDatetime, pUserId,
1257 pDatetime, pUserId
1258 FROM tt_po_internal A
1259 INNER JOIN tt_po_internal_item B
1260 ON A.session_id = B.session_id AND
1261 A.tenant_id = B.tenant_id AND
1262 A.tt_po_internal_id = B.tt_po_internal_id
1263 INNER JOIN m_tax C
1264 ON B.tax_id = C.tax_id AND
1265 B.tenant_id = C.tenant_id
1266 WHERE A.session_id = pSessionId AND
1267 A.tenant_id = pTenantId AND
1268 A.tt_po_internal_id = pTempPoInternalId
1269 GROUP BY B.tenant_id, B.tax_id, C.flg_amount, C.percentage, C.tax_curr_code;
1270
1271 --Insert data product PO Internal
1272 INSERT INTO pu_po_internal_product(
1273 tenant_id, po_internal_item_id, line_no,
1274 product_id, product_balance_id, product_status, qty_int, base_uom_id,
1275 remark, version, create_datetime, create_user_id, update_datetime,
1276 update_user_id)
1277 SELECT C.tenant_id, po_internal_item_id, C.line_no,
1278 D.product_id, D.product_balance_id, D.product_status, C.qty_int, D.base_uom_id,
1279 vEmptyValue, 0, pDatetime, pUserId, pDatetime,
1280 pUserId
1281 FROM tt_po_internal A
1282 INNER JOIN tt_po_internal_item B
1283 ON A.session_id = B.session_id AND
1284 A.tenant_id = B.tenant_id AND
1285 A.tt_po_internal_id = B.tt_po_internal_id
1286 INNER JOIN tt_po_internal_product C
1287 ON B.session_id = C.session_id AND
1288 B.tenant_id = C.tenant_id AND
1289 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1290 INNER JOIN in_product_balance_stock D
1291 ON C.product_balance_stock_id = D.product_balance_stock_id AND
1292 C.tenant_id = D.tenant_id
1293 INNER JOIN pu_po_internal_item E
1294 ON B.tenant_id = E.tenant_id AND
1295 E.po_internal_id = vPoInternalId AND
1296 B.product_id = E.product_id AND
1297 B.line_no = E.line_no
1298 WHERE A.session_id = pSessionId AND
1299 A.tenant_id = pTenantId AND
1300 A.tt_po_internal_id = pTempPoInternalId;
1301
1302 UPDATE in_product_balance_stock Z
1303 SET qty = Z.qty - C.qty_int,
1304 update_datetime = pDatetime,
1305 update_user_id = pUserId
1306 FROM tt_po_internal A
1307 INNER JOIN tt_po_internal_item B
1308 ON A.session_id = B.session_id AND
1309 A.tenant_id = B.tenant_id AND
1310 A.tt_po_internal_id = B.tt_po_internal_id
1311 INNER JOIN tt_po_internal_product C
1312 ON B.session_id = C.session_id AND
1313 B.tenant_id = C.tenant_id AND
1314 B.tt_po_internal_item_id = C.tt_po_internal_item_id
1315 WHERE A.session_id = pSessionId AND
1316 A.tenant_id = pTenantId AND
1317 A.tt_po_internal_id = pTempPoInternalId AND
1318 Z.product_balance_stock_id = C.product_balance_stock_id;
1319
1320 --Jika ref_doc_no tidak kosong, insert ke pu_po_internal_doc_no
1321 IF (vTempPoInternalHeader.ref_doc_no <> vEmptyValue) THEN
1322
1323 INSERT INTO pu_po_internal_doc_no(
1324 tenant_id, po_internal_id, flg_ou_po,
1325 ref_doc_type_id, ref_doc_no, ref_id, version, create_datetime,
1326 create_user_id, update_datetime, update_user_id, autonum_id)
1327 SELECT A.tenant_id, vPoInternalId, vFlagOuPoSell,
1328 vDocTypeIdSalesInvoice, A.ref_doc_no, vEmptyId, 0, pDatetime,
1329 pUserId, pDatetime, pUserId, vEmptyId
1330 FROM tt_po_internal A
1331 WHERE A.session_id = pSessionId AND
1332 A.tenant_id = pTenantId AND
1333 A.tt_po_internal_id = pTempPoInternalId;
1334
1335 END IF;
1336
1337 -- Generate data awe_currdoc_status
1338 INSERT INTO awe_currdoc_status(
1339 req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
1340 remark, current_user_id, current_role_id, flg_user_role, label,
1341 data, flow_id, create_datetime, create_user_id, create_role_id,
1342 update_datetime, update_user_id, update_role_id, version)
1343 SELECT A.po_internal_id||'_'||A.doc_no, A.tenant_id, vPoInternalScheme, A.po_internal_id, A.doc_no, A.doc_date, vWorkflowApproved,
1344 A.remark, pUserId, pRoleId, vFlgUserRole, 'poInternal'||A.doc_no,
1345 '{}', vPoInternalFlowId, pDatetime, pUserId, pRoleId,
1346 pDatetime, pUserId, pRoleId, 0
1347 FROM pu_po_internal A
1348 WHERE A.po_internal_id = vPoInternalId;
1349
1350 -- Generate data awe_historydoc
1351 INSERT INTO awe_historydoc(
1352 tenant_id, req_id, doc_id, scheme, user_id, role_id,
1353 activity, previous_state, next_state, remark, next_user_id, next_role_id,
1354 flg_user_role, activity_datetime, version)
1355 SELECT pTenantId, B.req_id, A.po_internal_id, vPoInternalScheme, pUserId, pRoleId,
1356 'CREATED FROM FUNCTION', '', '', A.remark, vEmptyId, vEmptyId,
1357 vFlgUserRole, pDateTime, 0
1358 FROM pu_po_internal A
1359 INNER JOIN awe_currdoc_status B ON B.doc_id = A.po_internal_id AND B.doc_no = A.doc_no AND B.doc_date = A.doc_date
1360 WHERE A.tenant_id = pTenantId
1361 AND A.po_internal_id = vPoInternalId;
1362
1363
1364 DELETE FROM cb_insufficient_cashbank_balance WHERE session_id = pSessionId;
1365
1366END;
1367$BODY$
1368 LANGUAGE plpgsql VOLATILE
1369 COST 100;
1370 /