· 5 years ago · May 19, 2020, 10:30 AM
1CREATE OR REPLACE FUNCTION in_submit_transfer_in(BIGINT, CHARACTER VARYING, CHARACTER VARYING)
2 RETURNS void AS
3$BODY$
4DECLARE
5 pTenantId ALIAS FOR $1;
6 pSessionId ALIAS FOR $2;
7 pProcessNo ALIAS FOR $3;
8
9 vProcessId BIGINT;
10 vTrfInId BIGINT;
11 vTrfOutId BIGINT;
12 vUserId BIGINT;
13 vEmptyId BIGINT;
14 vDatetime CHARACTER varying(14);
15 vFlagYes CHARACTER varying(1);
16 vFlagNo CHARACTER varying(1);
17 vStatusRelease CHARACTER varying(1);
18 vDocTypeRgto BIGINT;
19 vDocTypeSo BIGINT;
20 vDocTypeGto BIGINT;
21 vDocTypeSoPS BIGINT;
22 vDocTypeGti BIGINT;
23
24 vDocJournal DOC_JOURNAL%ROWTYPE;
25 vOuStructureWhsPengirim OU_BU_STRUCTURE%ROWTYPE;
26 vOuStructureWhsPenerima OU_BU_STRUCTURE%ROWTYPE;
27 result RECORD;
28
29 vNullLongId BIGINT:= -99;
30 vActivityCOA CHARACTER varying(20);
31 vProductCOA CHARACTER varying(10);
32 vStatusDraft CHARACTER varying(1);
33 vSignDebit CHARACTER varying(1);
34 vSignCredit CHARACTER varying(1);
35 vTypeRate CHARACTER varying(3);
36 vJournalTrxId BIGINT;
37 vEmptyValue CHARACTER VARYING;
38 vZero BIGINT;
39BEGIN
40
41 vFlagYes := 'Y';
42 vFlagNo := 'N';
43 vStatusRelease := 'R';
44 vEmptyId := -99;
45 vDocTypeRgto := 538;
46 vDocTypeSo := 301;
47 vDocTypeGto := 533;
48 vEmptyValue := '';
49 vProductCOA := 'PRODUCT';
50 vActivityCOA := 'ACTIVITY';
51 vStatusDraft := 'D';
52 vSignDebit := 'D';
53 vSignCredit := 'C';
54 vTypeRate := 'COM';
55 vDocTypeSoPS := 370;
56 vDocTypeGti := 535;
57 vZero := 0;
58
59 DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
60 DELETE FROM tt_for_so_item_rgto WHERE session_id = pSessionId;
61 DELETE FROM tt_gti_for_reserved_stock WHERE session_id = pSessionId;
62
63 SELECT A.process_message_id INTO vProcessId
64 FROM t_process_message A
65 WHERE A.tenant_id = pTenantId AND
66 A.process_name = 'in_submit_transfer_in' AND
67 A.process_no = pProcessNo;
68
69 SELECT CAST(A.process_parameter_value AS BIGINT) INTO vTrfInId
70 FROM t_process_parameter A
71 WHERE A.process_message_id = vProcessId AND
72 A.process_parameter_key = 'trfInId';
73
74 SELECT CAST(A.process_parameter_value AS BIGINT) INTO vUserId
75 FROM t_process_parameter A
76 WHERE A.process_message_id = vProcessId AND
77 A.process_parameter_key = 'userId';
78
79 SELECT CAST(A.process_parameter_value AS CHARACTER varying(14)) INTO vDatetime
80 FROM t_process_parameter A
81 WHERE A.process_message_id = vProcessId AND
82 A.process_parameter_key = 'datetime';
83
84 SELECT f_get_ou_bu_structure(B.ou_id) AS ou_from,f_get_ou_bu_structure(C.ou_id) AS ou_to, f_get_document_journal(A.doc_type_id) AS doc
85 FROM in_inventory A
86 JOIN m_warehouse_ou B ON A.warehouse_from_id = B.warehouse_id
87 JOIN m_warehouse_ou C ON A.warehouse_to_id = C.warehouse_id
88 WHERE A.inventory_id = vTrfInId INTO result;
89
90 vOuStructureWhsPengirim := result.ou_from;
91 vOuStructureWhsPenerima := result.ou_to;
92 vDocJournal := result.doc;
93
94 /*
95 * 1.update status doc in_inventory
96 * 2.update data ke in_product_balance_stock
97 * 3.insert data ke in_log_product_balance_stock
98 * 4.update data in_balance_transfer_out
99 */
100
101 UPDATE in_inventory SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
102 WHERE inventory_id = vTrfInId;
103
104 /*
105 * 1.insert data ke in_log_product_balance_stock
106 */
107 INSERT INTO in_log_product_balance_stock
108 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
109 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
110 "version", create_datetime, create_user_id, update_datetime, update_user_id)
111 SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
112 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
113 0, vDatetime, vUserId, vDatetime, vUserId
114 FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
115 WHERE A.inventory_id = vTrfInId AND
116 A.inventory_id = B.inventory_id AND
117 A.warehouse_to_id = C.warehouse_id
118 GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
119 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
120
121 /*
122 * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
123 */
124 INSERT INTO tt_in_product_balance_summary_stock
125 (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
126 SELECT pSessionId, A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status,
127 B.base_uom_id, SUM(B.qty_realization)
128 FROM in_inventory A, in_inventory_item B
129 WHERE A.inventory_id = B.inventory_id
130 AND A.inventory_id = vTrfInId
131 GROUP BY A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
132
133 UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId,
134 VERSION = VERSION + 1
135 FROM tt_in_product_balance_summary_stock A
136 WHERE A.session_id = pSessionId AND
137 A.inventory_id = vTrfInId AND
138 in_product_balance_stock.tenant_id = A.tenant_id AND
139 in_product_balance_stock.warehouse_id = A.warehouse_id AND
140 in_product_balance_stock.product_id = A.product_id AND
141 in_product_balance_stock.product_balance_id = A.product_balance_id AND
142 in_product_balance_stock.product_status = A.product_status;
143
144 /*
145 * insert data in_product_balance_stock
146 */
147 INSERT INTO in_product_balance_stock
148 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
149 "version", create_datetime, create_user_id, update_datetime, update_user_id)
150 SELECT A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
151 0, vDatetime, vUserId, vDatetime, vUserId
152 FROM in_inventory A
153 INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
154 WHERE A.inventory_id = vTrfInId AND
155 NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
156 WHERE A.tenant_id = C.tenant_id AND
157 A.warehouse_to_id = C.warehouse_id AND
158 B.product_id = C.product_id AND
159 B.product_balance_id = C.product_balance_id AND
160 B.product_status = C.product_status)
161 GROUP BY A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
162
163 SELECT ref_id INTO vTrfOutId
164 FROM in_inventory
165 WHERE inventory_id = vTrfInId;
166
167 UPDATE in_balance_transfer_out SET flg_real = vFlagYes, update_datetime = vDatetime, update_user_id = vUserId
168 WHERE inventory_id = vTrfOutId;
169
170 INSERT INTO in_balance_transfer_in_item
171 (inventory_item_id, tenant_id, inventory_id, line_no, ou_from_id, ou_to_id, qty_out, qty_in,
172 base_uom_id, warehouse_from_id, warehouse_to_id, product_id, product_balance_id, product_status,
173 ref_doc_type_id, ref_id, ref_item_id, flg_receipt, receipt_id,
174 VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
175 SELECT A.inventory_item_id, A.tenant_id, A.inventory_id, A.line_no, B.ou_from_id, B.ou_to_id, A.qty_request, A.qty_realization,
176 A.base_uom_id, B.warehouse_from_id, B.warehouse_to_id, A.product_id, A.product_balance_id, A.product_status,
177 A.ref_doc_type_id, A.ref_id, A.ref_item_id, vFlagNo, vEmptyId,
178 0, vDatetime, vUserId, vDatetime, vUserId
179 FROM in_inventory_item A
180 INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
181 WHERE B.inventory_id = vTrfInId
182 AND A.qty_request <> A.qty_realization;
183
184 INSERT INTO in_balance_transfer_in_item
185 (inventory_item_id, tenant_id, inventory_id, line_no, ou_from_id, ou_to_id, qty_out, qty_in,
186 base_uom_id, warehouse_from_id, warehouse_to_id, product_id, product_balance_id, product_status,
187 ref_doc_type_id, ref_id, ref_item_id, flg_receipt, receipt_id,
188 VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
189 SELECT A.inventory_item_id, A.tenant_id, A.inventory_id, A.line_no, B.ou_from_id, B.ou_to_id, A.qty_request, A.qty_realization,
190 A.base_uom_id, B.warehouse_from_id, B.warehouse_to_id, A.product_id, A.product_balance_id, A.product_status,
191 A.ref_doc_type_id, A.ref_id, A.ref_item_id, vFlagYes, vEmptyId,
192 0, vDatetime, vUserId, vDatetime, vUserId
193 FROM in_inventory_item A
194 INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
195 WHERE B.inventory_id = vTrfInId
196 AND A.qty_request = A.qty_realization;
197
198 DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
199
200 IF EXISTS(
201 SELECT 1
202 FROM in_inventory A
203 INNER JOIN in_inventory B ON A.ref_id = B.inventory_id AND B.doc_type_id = vDocTypeGto
204 INNER JOIN in_inventory C ON B.ref_id = C.inventory_id AND C.doc_type_id = vDocTypeRgto
205 INNER JOIN in_balance_so_item_for_rgto D ON C.inventory_id = D.rgto_id
206 WHERE A.inventory_id = vTrfInId
207 )
208 THEN
209 --insert kedalam tt_for_so_item_rgto dari tabel in_balance_so_item_for_rgto
210 INSERT INTO tt_for_so_item_rgto(
211 session_id, so_id, product_id, qty_order, qty_reserve, warehouse_id)
212 SELECT pSessionId, A.so_id, A.product_id, A.qty, A.qty, A.warehouse_to_id
213 FROM in_balance_so_item_for_rgto A
214 INNER JOIN in_inventory B ON A.rgto_id = B.inventory_id AND B.doc_type_id = vDocTypeRgto
215 INNER JOIN in_inventory C ON B.inventory_id = C.ref_id AND C.doc_type_id = vDocTypeGto
216 INNER JOIN in_inventory D ON C.inventory_id = D.ref_id AND D.doc_type_id = vDocTypeGti
217 WHERE D.inventory_id = vTrfInId;
218
219 UPDATE in_product_balance_stock_reserved A
220 SET qty = qty + C.qty_reserve
221 FROM tt_for_so_item_rgto C
222 WHERE A.product_id = C.product_id
223 AND C.session_id = pSessionId
224 AND A.warehouse_id = C.warehouse_id;
225
226 --ambil dan masukan kedalam tabel in_product_balance_stock_reserved
227 --untuk mendapatkan informasi" dari table temporary rgto otomatis generate dari SO
228 INSERT INTO in_product_balance_stock_reserved (
229 product_id, qty,warehouse_id,create_datetime)
230 SELECT product_id, qty_reserve, warehouse_id, create_datetime
231 FROM tt_for_so_item_rgto
232 WHERE session_id = pSessionId
233
234 INSERT INTO in_log_product_balance_stock_reserved(
235 tenant_id, ou_id, main_ref_doc_type_id,
236 main_ref_id, main_doc_no, main_doc_date, ref_doc_type_id, ref_id,
237 doc_no, doc_date, partner_id, product_id, warehouse_id, base_uom_id,
238 qty, remark, VERSION, create_datetime, create_user_id, update_datetime,
239 update_user_id)
240 SELECT pTenantId, B.ou_id, B.doc_type_id,
241 A.so_id, B.doc_no, B.doc_date, B.ref_doc_type_id, B.ref_id,
242 B.doc_no, B.doc_date, B.partner_id, A.product_id , A.warehouse_id, E.base_uom_id,
243 A.qty_reserve,'', 0, vDatetime, vUserId, vDatetime, vUserId
244 FROM tt_for_so_item_rgto A
245 INNER JOIN sl_so B ON A.so_id = B.so_id
246 INNER JOIN in_balance_so_item_for_rgto C ON A.so_id = C.so_id AND A.product_id = C.product_id
247 INNER JOIN in_inventory D ON C.rgto_id = D.inventory_id AND D.doc_type_id = vDocTypeRgto
248 INNER JOIN in_inventory_item E ON D.inventory_id = E.inventory_id AND A.product_id = E.product_id
249 WHERE A.session_id = pSessionId
250 AND A.qty_reserve > 0;
251
252 UPDATE in_balance_so_item_for_rgto A
253 SET qty_reserved_gti = B.qty_reserve
254 FROM tt_for_so_item_rgto B
255 WHERE B.session_id = pSessionId
256 AND A.so_id = B.so_id
257 AND A.product_id = B.product_id;
258 END IF;
259
260 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructureWhsPenerima).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
261 FROM in_inventory A
262 WHERE A.inventory_id = vTrfInId;
263
264 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
265
266 INSERT INTO gl_journal_trx
267 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
268 ou_bu_id,
269 ou_branch_id, ou_sub_bu_id,
270 partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
271 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
272 "version", create_datetime, create_user_id, update_datetime, update_user_id)
273 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
274 (vOuStructureWhsPenerima).ou_bu_id,
275-- (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id,
276 vNullLongId, vNullLongId,
277 A.partner_id, vNullLongId, A.warehouse_to_id, A.ext_doc_no, A.ext_doc_date,
278 A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
279 0, vDatetime, vUserId, vDatetime, vUserId
280 FROM in_inventory A
281 WHERE A.inventory_id = vTrfInId;
282
283 INSERT INTO tt_journal_trx_item
284 (session_id, tenant_id, journal_trx_id, line_no,
285 ref_doc_type_id, ref_id,
286 partner_id, product_id, cashbank_id, ou_rc_id,
287 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
288 coa_id, curr_code, qty, uom_id,
289 amount, journal_date, type_rate,
290 numerator_rate, denominator_rate, journal_desc, remark)
291 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
292 A.doc_type_id, B.inventory_item_id,
293 A.partner_id, B.product_id, vNullLongId, vNullLongId,
294 vNullLongId, vSignDebit, vProductCOA, vNullLongId,
295 f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
296 0 , A.doc_date, vTypeRate,
297 1, 1, 'PRODUCT_STOCK', B.remark
298 FROM in_inventory A, in_inventory_item B
299 WHERE A.inventory_id = vTrfInId
300 AND A.inventory_id = B.inventory_id;
301
302 INSERT INTO gl_journal_trx_item
303 (tenant_id, journal_trx_id, line_no,
304 ref_doc_type_id, ref_id,
305 partner_id, product_id, cashbank_id, ou_rc_id,
306 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
307 coa_id, curr_code, qty, uom_id,
308 amount, journal_date, type_rate,
309 numerator_rate, denominator_rate, journal_desc, remark,
310 "version", create_datetime, create_user_id, update_datetime, update_user_id,
311 ou_branch_id, ou_sub_bu_id)
312 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
313 A.ref_doc_type_id, A.ref_id,
314 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
315 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
316 A.coa_id, A.curr_code, A.qty, A.uom_id,
317 A.amount, A.journal_date, A.type_rate,
318 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
319 0, vDatetime, vUserId, vDatetime, vUserId,
320 (vOuStructureWhsPenerima).ou_branch_id, (vOuStructureWhsPenerima).ou_sub_bu_id
321 FROM tt_journal_trx_item A
322 WHERE A.session_id = pSessionId;
323
324 INSERT INTO gl_journal_trx_mapping
325 (tenant_id, journal_trx_id, line_no,
326 ref_doc_type_id, ref_id,
327 partner_id, product_id, cashbank_id, ou_rc_id,
328 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
329 coa_id, curr_code, qty, uom_id,
330 amount, journal_date, type_rate,
331 numerator_rate, denominator_rate, journal_desc, remark,
332 "version", create_datetime, create_user_id, update_datetime, update_user_id,
333 ou_branch_id, ou_sub_bu_id)
334 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
335 vEmptyId, vEmptyId,
336 vEmptyId, vEmptyId, vEmptyId, vEmptyId,
337 vEmptyId, vSignCredit, vActivityCOA, vEmptyId,
338 f_get_system_coa_by_group_coa(A.tenant_id, 'PersediaanInTransit'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
339 0, A.journal_date, A.type_rate,
340 1, 1, 'PRODUCT_STOCK_IN_TRANSIT', vEmptyValue,
341 0, vDatetime, vUserId, vDatetime, vUserId,
342 (vOuStructureWhsPenerima).ou_branch_id, (vOuStructureWhsPenerima).ou_sub_bu_id
343 FROM tt_journal_trx_item A
344 WHERE A.session_id = pSessionId
345 GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
346
347 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
348 DELETE FROM tt_for_so_item_rgto WHERE session_id = pSessionId;
349 DELETE FROM tt_gti_for_reserved_stock WHERE session_id = pSessionId;
350
351END;
352$BODY$
353 LANGUAGE plpgsql VOLATILE
354 COST 100;
355 /