· 5 years ago · May 19, 2020, 09:52 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_reserve,warehouse_id,create_datetime)
230 SELECT product_id, qty, warehouse_id, create_datetime
231 FROM tt_for_so_item_rgto
232 WHERE A.product_id = C.product_id
233 AND C.session_id = pSessionId
234 AND A.warehouse_id = C.warehouse_id;
235
236 INSERT INTO in_log_product_balance_stock_reserved(
237 tenant_id, ou_id, main_ref_doc_type_id,
238 main_ref_id, main_doc_no, main_doc_date, ref_doc_type_id, ref_id,
239 doc_no, doc_date, partner_id, product_id, warehouse_id, base_uom_id,
240 qty, remark, VERSION, create_datetime, create_user_id, update_datetime,
241 update_user_id)
242 SELECT pTenantId, B.ou_id, B.doc_type_id,
243 A.so_id, B.doc_no, B.doc_date, B.ref_doc_type_id, B.ref_id,
244 B.doc_no, B.doc_date, B.partner_id, A.product_id , A.warehouse_id, E.base_uom_id,
245 A.qty_reserve,'', 0, vDatetime, vUserId, vDatetime, vUserId
246 FROM tt_for_so_item_rgto A
247 INNER JOIN sl_so B ON A.so_id = B.so_id
248 INNER JOIN in_balance_so_item_for_rgto C ON A.so_id = C.so_id AND A.product_id = C.product_id
249 INNER JOIN in_inventory D ON C.rgto_id = D.inventory_id AND D.doc_type_id = vDocTypeRgto
250 INNER JOIN in_inventory_item E ON D.inventory_id = E.inventory_id AND A.product_id = E.product_id
251 WHERE A.session_id = pSessionId
252 AND A.qty_reserve > 0;
253
254 UPDATE in_balance_so_item_for_rgto A
255 SET qty_reserved_gti = B.qty_reserve
256 FROM tt_for_so_item_rgto B
257 WHERE B.session_id = pSessionId
258 AND A.so_id = B.so_id
259 AND A.product_id = B.product_id;
260 END IF;
261
262 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)
263 FROM in_inventory A
264 WHERE A.inventory_id = vTrfInId;
265
266 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
267
268 INSERT INTO gl_journal_trx
269 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
270 ou_bu_id,
271 ou_branch_id, ou_sub_bu_id,
272 partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
273 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
274 "version", create_datetime, create_user_id, update_datetime, update_user_id)
275 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
276 (vOuStructureWhsPenerima).ou_bu_id,
277-- (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id,
278 vNullLongId, vNullLongId,
279 A.partner_id, vNullLongId, A.warehouse_to_id, A.ext_doc_no, A.ext_doc_date,
280 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',
281 0, vDatetime, vUserId, vDatetime, vUserId
282 FROM in_inventory A
283 WHERE A.inventory_id = vTrfInId;
284
285 INSERT INTO tt_journal_trx_item
286 (session_id, tenant_id, journal_trx_id, line_no,
287 ref_doc_type_id, ref_id,
288 partner_id, product_id, cashbank_id, ou_rc_id,
289 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
290 coa_id, curr_code, qty, uom_id,
291 amount, journal_date, type_rate,
292 numerator_rate, denominator_rate, journal_desc, remark)
293 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
294 A.doc_type_id, B.inventory_item_id,
295 A.partner_id, B.product_id, vNullLongId, vNullLongId,
296 vNullLongId, vSignDebit, vProductCOA, vNullLongId,
297 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,
298 0 , A.doc_date, vTypeRate,
299 1, 1, 'PRODUCT_STOCK', B.remark
300 FROM in_inventory A, in_inventory_item B
301 WHERE A.inventory_id = vTrfInId
302 AND A.inventory_id = B.inventory_id;
303
304 INSERT INTO gl_journal_trx_item
305 (tenant_id, journal_trx_id, line_no,
306 ref_doc_type_id, ref_id,
307 partner_id, product_id, cashbank_id, ou_rc_id,
308 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
309 coa_id, curr_code, qty, uom_id,
310 amount, journal_date, type_rate,
311 numerator_rate, denominator_rate, journal_desc, remark,
312 "version", create_datetime, create_user_id, update_datetime, update_user_id,
313 ou_branch_id, ou_sub_bu_id)
314 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
315 A.ref_doc_type_id, A.ref_id,
316 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
317 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
318 A.coa_id, A.curr_code, A.qty, A.uom_id,
319 A.amount, A.journal_date, A.type_rate,
320 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
321 0, vDatetime, vUserId, vDatetime, vUserId,
322 (vOuStructureWhsPenerima).ou_branch_id, (vOuStructureWhsPenerima).ou_sub_bu_id
323 FROM tt_journal_trx_item A
324 WHERE A.session_id = pSessionId;
325
326 INSERT INTO gl_journal_trx_mapping
327 (tenant_id, journal_trx_id, line_no,
328 ref_doc_type_id, ref_id,
329 partner_id, product_id, cashbank_id, ou_rc_id,
330 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
331 coa_id, curr_code, qty, uom_id,
332 amount, journal_date, type_rate,
333 numerator_rate, denominator_rate, journal_desc, remark,
334 "version", create_datetime, create_user_id, update_datetime, update_user_id,
335 ou_branch_id, ou_sub_bu_id)
336 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
337 vEmptyId, vEmptyId,
338 vEmptyId, vEmptyId, vEmptyId, vEmptyId,
339 vEmptyId, vSignCredit, vActivityCOA, vEmptyId,
340 f_get_system_coa_by_group_coa(A.tenant_id, 'PersediaanInTransit'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
341 0, A.journal_date, A.type_rate,
342 1, 1, 'PRODUCT_STOCK_IN_TRANSIT', vEmptyValue,
343 0, vDatetime, vUserId, vDatetime, vUserId,
344 (vOuStructureWhsPenerima).ou_branch_id, (vOuStructureWhsPenerima).ou_sub_bu_id
345 FROM tt_journal_trx_item A
346 WHERE A.session_id = pSessionId
347 GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
348
349 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
350 DELETE FROM tt_for_so_item_rgto WHERE session_id = pSessionId;
351 DELETE FROM tt_gti_for_reserved_stock WHERE session_id = pSessionId;
352
353END;
354$BODY$
355 LANGUAGE plpgsql VOLATILE
356 COST 100;
357 /