· 9 years ago · Jan 03, 2017, 04:42 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
19 vTrfInWhsId bigint;
20 vTrfInCreateDate character varying(8);
21 vSupposedGtiDate character varying(8);
22 vStockOpnameDate character varying(8);
23 vIsAffectingStock character varying(1);
24BEGIN
25
26 vFlagYes := 'Y';
27 vFlagNo := 'N';
28 vStatusRelease := 'R';
29 vEmptyId := -99;
30 vIsAffectingStock := 'Y';
31
32 DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
33
34 SELECT A.process_message_id INTO vProcessId
35 FROM t_process_message A
36 WHERE A.tenant_id = pTenantId AND
37 A.process_name = 'in_submit_transfer_in' AND
38 A.process_no = pProcessNo;
39
40 SELECT CAST(A.process_parameter_value AS bigint) INTO vTrfInId
41 FROM t_process_parameter A
42 WHERE A.process_message_id = vProcessId AND
43 A.process_parameter_key = 'trfInId';
44
45 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
46 FROM t_process_parameter A
47 WHERE A.process_message_id = vProcessId AND
48 A.process_parameter_key = 'userId';
49
50 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
51 FROM t_process_parameter A
52 WHERE A.process_message_id = vProcessId AND
53 A.process_parameter_key = 'datetime';
54
55 SELECT ref_id, warehouse_to_id INTO vTrfOutId, vTrfInWhsId
56 FROM in_inventory
57 WHERE inventory_id = vTrfInId;
58
59 /*
60 * ADDED BY Julius Tanuwijaya 30 Dec 2016
61 * Menambahkan proses cek apakah GTI yang disubmit ini sudah melewati lead-time yang sudah ditentukan
62 * stock instan akan berubah jika:
63 * 1. tidak ada stock opname diantara GTO dan GTI tersebut
64 * 2. GTI yang sedang diproses ini belum melewati masa lead-time terhitung dari tanggal GTO
65 *
66 * Step :
67 * 1. mendapatkan tanggal proses stock opname untuk warehouse yang sedang dilakukan GTI di proses submit ini.
68 * a. Jika tanggal stock opname warehouse dari GTI yang disubmit tidak null (berarti sudah pernah dilakukan stock opname), dapatkan tanggal create date GTI
69 * b. Jika tanggal create GTI > tanggal stock opname warehouse yang sedang dilakukan GTI diproses submit ini, dapatkan tanggal GTI yang seharusnya (tanggal GTO + lead-time)
70 * c. Jika create date GTI lebih besar dati tanggal GTI seharusnya, maka GTI ini tidak mempengaruhi stock, maka dianggap GTI ini tidak mempengaruhi stock
71 *
72 */
73
74 -- mendapatkan tanggal proses stock opname terbaru yang sudah diproses untuk warehouse yang sedang dilakukan GTI di proses submit ini.
75 SELECT MAX(doc_date) INTO vStockOpnameDate
76 FROM i_mobile_pos_stock_opname A
77 INNER JOIN in_inventory_adjust_stock_ext B ON a.mobile_pos_stock_opname_id = B.ref_id
78 WHERE A.warehouse_id = vTrfInWhsId;
79
80 -- Jika tanggal stock opname warehouse dari GTI yang disubmit tidak null (berarti sudah pernah dilakukan stock opname)
81 IF vStockOpnameDate IS NOT NULL THEN
82 -- mendapatkan tanggal create date GTI
83 SELECT SUBSTR(create_datetime, 1, 8) INTO vTrfInCreateDate
84 FROM in_inventory where inventory_id = vTrfInId;
85
86 -- Jika tanggal create GTI > tanggal stock opname warehouse yang sedang dilakukan GTI diproses submit ini
87 IF vTrfInCreateDate > vStockOpnameDate THEN
88
89 -- mendapatkan tanggal GTI yang seharusnya (tanggal GTO + lead-time)
90 SELECT to_char(to_date(A.doc_date, 'YYYYMMDD') + COALESCE(lead_time, '1 day')::INTERVAL, 'YYYYMMDD') INTO vSupposedGtiDate
91 FROM in_inventory A
92 LEFT JOIN m_warehouse_lead_time B ON A.warehouse_from_id = B.warehouse_from_id AND A.warehouse_to_id = B.warehouse_to_id
93 where inventory_id = vTrfOutId;
94
95 -- Jika create date GTI lebih besar dati tanggal GTI seharusnya dan stock opname lebih kecil dari tanggal GTI seharusnya,
96 -- maka GTI ini tidak mempengaruhi stock
97 IF vTrfInCreateDate > vSupposedGtiDate AND vStockOpnameDate < vSupposedGtiDate THEN
98 -- GTI tidak mempengaruhi stock
99 RAISE NOTICE 'GTI tidak Mempengaruhi Stock';
100 vIsAffectingStock := 'N';
101 END IF;
102
103 END IF;
104
105 END IF;
106
107
108 /*
109 * 1.update status doc in_inventory
110 * 2.update data ke in_product_balance_stock
111 * 3.insert data ke in_log_product_balance_stock
112 * 4.update data in_balance_transfer_out
113 */
114
115 UPDATE in_inventory SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
116 WHERE inventory_id = vTrfInId;
117
118 /*
119 * 1.insert data ke in_log_product_balance_stock
120 */
121 INSERT INTO in_log_product_balance_stock
122 (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
123 product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
124 "version", create_datetime, create_user_id, update_datetime, update_user_id)
125 SELECT A.tenant_id, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
126 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
127 0, vDatetime, vUserId, vDatetime, vUserId
128 FROM in_inventory A, in_inventory_item B
129 WHERE A.inventory_id = vTrfInId AND
130 A.inventory_id = B.inventory_id
131 GROUP BY A.tenant_id, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
132 B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id;
133
134 /*
135 * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
136 */
137 INSERT INTO tt_in_product_balance_summary_stock
138 (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
139 SELECT pSessionId, A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status,
140 B.base_uom_id, SUM(B.qty_realization)
141 FROM in_inventory A, in_inventory_item B
142 WHERE A.inventory_id = B.inventory_id
143 AND A.inventory_id = vTrfInId
144 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;
145
146 /*
147 * Proses merubah stock instan
148 */
149 -------------------------------------------------
150 IF vIsAffectingStock = vFlagYes THEN
151 RAISE NOTICE 'GTI Mempengaruhi Stock';
152 -- UPDATE stock di table in_product_balance_stock
153 UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId
154 FROM tt_in_product_balance_summary_stock A
155 WHERE A.session_id = pSessionId AND
156 A.inventory_id = vTrfInId AND
157 in_product_balance_stock.tenant_id = A.tenant_id AND
158 in_product_balance_stock.warehouse_id = A.warehouse_id AND
159 in_product_balance_stock.product_id = A.product_id AND
160 in_product_balance_stock.product_balance_id = A.product_balance_id AND
161 in_product_balance_stock.product_status = A.product_status;
162
163 /*
164 * insert data in_product_balance_stock
165 * Untuk product yang belum ada datanya di table in_product_balance_stock
166 */
167 INSERT INTO in_product_balance_stock
168 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
169 "version", create_datetime, create_user_id, update_datetime, update_user_id)
170 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),
171 0, vDatetime, vUserId, vDatetime, vUserId
172 FROM in_inventory A
173 INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
174 WHERE A.inventory_id = vTrfInId AND
175 NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
176 WHERE A.tenant_id = C.tenant_id AND
177 A.warehouse_to_id = C.warehouse_id AND
178 B.product_id = C.product_id AND
179 B.product_balance_id = C.product_balance_id AND
180 B.product_status = C.product_status)
181 GROUP BY A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
182
183 END IF;
184 -------------------------------------------------
185 UPDATE in_balance_transfer_out SET flg_real = vFlagYes, update_datetime = vDatetime, update_user_id = vUserId
186 WHERE inventory_id = vTrfOutId;
187
188 INSERT INTO in_balance_transfer_in_item
189 (inventory_item_id, tenant_id, inventory_id, line_no, ou_from_id, ou_to_id, qty_out, qty_in,
190 base_uom_id, warehouse_from_id, warehouse_to_id, product_id, product_balance_id, product_status,
191 ref_doc_type_id, ref_id, ref_item_id, flg_receipt, receipt_id,
192 version, create_datetime, create_user_id, update_datetime, update_user_id)
193 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,
194 A.base_uom_id, B.warehouse_from_id, B.warehouse_to_id, A.product_id, A.product_balance_id, A.product_status,
195 A.ref_doc_type_id, A.ref_id, A.ref_item_id, vFlagNo, vEmptyId,
196 0, vDatetime, vUserId, vDatetime, vUserId
197 FROM in_inventory_item A
198 INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
199 WHERE B.inventory_id = vTrfInId
200 AND A.qty_request <> A.qty_realization;
201
202 INSERT INTO in_balance_transfer_in_item
203 (inventory_item_id, tenant_id, inventory_id, line_no, ou_from_id, ou_to_id, qty_out, qty_in,
204 base_uom_id, warehouse_from_id, warehouse_to_id, product_id, product_balance_id, product_status,
205 ref_doc_type_id, ref_id, ref_item_id, flg_receipt, receipt_id,
206 version, create_datetime, create_user_id, update_datetime, update_user_id)
207 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,
208 A.base_uom_id, B.warehouse_from_id, B.warehouse_to_id, A.product_id, A.product_balance_id, A.product_status,
209 A.ref_doc_type_id, A.ref_id, A.ref_item_id, vFlagYes, vEmptyId,
210 0, vDatetime, vUserId, vDatetime, vUserId
211 FROM in_inventory_item A
212 INNER JOIN in_inventory B ON A.inventory_id = B.inventory_id
213 WHERE B.inventory_id = vTrfInId
214 AND A.qty_request = A.qty_realization;
215
216 DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
217
218END;
219$BODY$
220LANGUAGE plpgsql VOLATILE
221COST 100;