· 6 years ago · Jan 16, 2020, 03:02 AM
1CREATE OR REPLACE FUNCTION sl_add_do_from_partstation(character varying, bigint, character varying, bigint, bigint,
2character varying, character varying)
3 RETURNS void AS
4$BODY$
5DECLARE
6 pSessionId ALIAS FOR $1;
7 pTenantId ALIAS FOR $2;
8 pDatetime ALIAS FOR $3;
9 pUserId ALIAS FOR $4;
10 pOuId ALIAS FOR $5;
11 pOrderDocNo ALIAS FOR $6;
12 pOrderDocDate ALIAS FOR $7;
13
14 vDocTypeDeliveryOrder bigint;
15 vEmptyValue character varying(1);
16 vStatusDocDraft character varying(1);
17 vWorkFlowStatusDraft character varying;
18 vEmptyId bigint;
19 vStatusError character varying(1);
20 vMessageError character varying(40);
21 vStatusInProgress character varying(1);
22 vProductStatus character varying;
23 vProcessNameDO character varying;
24 vParamKeyDO character varying;
25 vDocTypeSalesInvoice bigint;
26 vDocTypeSalesInvoiceTemp bigint;
27 vFlagYes character varying;
28 vProcessNameSI character varying;
29 vParamKeySI character varying;
30 vFlowDoId bigint;
31 vFlowInvoiceId bigint;
32 vSchemeDeliveryOrder character varying;
33 vSchemeSalesInvoice character varying;
34 vWorkFlowStatusApproved character varying;
35 vStatusDocApproved character varying(1);
36 vMessageErrorSO character varying(40);
37 vMessageErrorQty character varying(40);
38 vDocTypeCartOrder bigint;
39 vSoId bigint;
40 vDoId bigint;
41 vInvoiceId bigint;
42 vInvoiceTempId bigint;
43BEGIN
44 vDocTypeDeliveryOrder := 311;
45 vEmptyValue := '';
46 vStatusDocDraft := 'D';
47 vWorkFlowStatusDraft := 'DRAFT';
48 vEmptyId := -99;
49 vStatusError := 'E';
50 vMessageError := 'Product Not Found';
51 vStatusInProgress := 'I';
52 vProductStatus := 'GOOD';
53 vProcessNameDO := 'sl_submit_do';
54 vParamKeyDO := 'doId';
55 vDocTypeSalesInvoice := 321;
56 vDocTypeSalesInvoiceTemp := 301;
57 vFlagYes := 'Y';
58 vProcessNameSI := 'sl_submit_sales_invoice';
59 vParamKeySI := 'salesInvoiceId';
60 vSchemeDeliveryOrder := 'FB01';
61 vSchemeSalesInvoice := 'FC01';
62 vWorkFlowStatusApproved := 'APPROVED';
63 vStatusDocApproved := 'R';
64 vMessageErrorSO := 'Product Not In SO';
65 vMessageErrorQty := 'Qty > Qty in SO';
66 vDocTypeCartOrder := 370;
67
68 --catet so id ke suatu variabel sebelum mulai
69 --cari SO dengan filter ext doc no ext doc date dan juga ref doc type id yg tipenya CART
70 SELECT so_id INTO vSoId
71 FROM sl_so
72 WHERE ext_doc_no = pOrderDocNo AND ext_doc_date = pOrderDocDate
73 AND ref_doc_type_id = vDocTypeCartOrder;
74
75 --cari product id nya apa yang exists, lalu catat di time table
76 UPDATE tt_delivery_order_partstation A
77 SET product_id = B.product_id
78 FROM m_product B
79 WHERE A.product_code = B.product_code
80 AND A.session_id = pSessionId;
81
82 --jika ada product id yang kosong, maka diubah status nya jadi error
83 UPDATE tt_delivery_order_partstation A
84 SET status = vStatusError, message = vMessageError
85 WHERE A.product_id = vEmptyId
86 AND A.session_id = pSessionId;
87
88 --(validasi produk harus di dalam so item) (update error)
89 --dapetin dulu item di SO itu apa aja dari so id diatas, join ke tt
90 --update time table where product id not in product id (hasil query diatas) (subquery)
91 UPDATE tt_delivery_order_partstation
92 SET status = vStatusError, message = vMessageErrorSO
93 WHERE product_id NOT IN(
94 SELECT product_id
95 FROM sl_so_item WHERE so_id = vSoId
96 ) AND status = vStatusInProgress;
97
98 --(validasi qty tidak boleh lebih besar dari qty so)
99 --UPDATE tt berdasarkan product id yang sesuai dengan sl so item, dimana so id pada so item tsb = variabel, update
100 --dengan kondisi qty(tt) > qty(so item)
101 UPDATE tt_delivery_order_partstation B
102 SET status = vStatusError, message = vMessageErrorQty
103 FROM sl_so_item A
104 where A.so_id = vSoId AND A.product_id = B.product_id
105 AND B.qty > A.qty_so AND B.status = vStatusInProgress;
106
107 --DO tidak akan terbentuk jika ada 1 saja item yang error
108 IF NOT EXISTS
109 (
110 SELECT 1
111 FROM tt_delivery_order_partstation
112 WHERE status = 'E'
113 )
114 THEN
115
116 SELECT NEXTVAL('sl_do_seq') INTO vDoId;
117
118 --insert into sl do'(PERBAIKI)
119 INSERT INTO sl_do(
120 do_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id, ext_doc_no,
121 ext_doc_date, ref_doc_type_id, ref_id, remark, partner_ship_to_id,
122 partner_ship_address_id, warehouse_id, no_vehicle, flg_delivery,
123 delivery_code, status_doc, workflow_status, version, create_datetime,
124 create_user_id, update_datetime, update_user_id, eta, eta_day)
125 SELECT vDoId,pTenantId, vDocTypeDeliveryOrder, f_gen_autonum_with_suffix('DO/'||LEFT(pDatetime,8)||'/', 'DeliveryOrder', vEmptyValue, pTenantId), LEFT(pDatetime,8), pOuId, A.ext_doc_no,
126 A.ext_doc_date, A.doc_type_id, A.so_id, vEmptyValue, A.partner_ship_to_id,
127 A.partner_ship_address_id, B.warehouse_id, vEmptyValue, A.flg_delivery,
128 vEmptyValue, vStatusDocDraft, vWorkFlowStatusDraft, 0, pDatetime,
129 pUserId, pDatetime, pUserId, LEFT(pDatetime,8), 'BEFORE/AFTER LUNCH'
130 FROM sl_so A, sl_so_custom B
131 WHERE A.so_id = vSoId AND A.so_id = B.so_id
132 GROUP BY A.ext_doc_no, A.ext_doc_date, A.doc_type_id, A.so_id, A.partner_ship_to_id, A.partner_ship_address_id, B.warehouse_id, A.flg_delivery;
133
134 --insert into sl do item (JADI 3 )
135 INSERT INTO sl_do_item(
136 tenant_id, do_id, line_no, ref_doc_type_id, ref_id,
137 product_id, qty_dlv_so, so_uom_id, qty_dlv_int, base_uom_id,
138 remark, version, create_datetime, create_user_id, update_datetime,
139 update_user_id, product_status)
140 SELECT pTenantId, vDoId, ROW_NUMBER() OVER (PARTITION BY vDoId), vDocTypeCartOrder, A.so_item_id,
141 A.product_id, ROUND(((B.qty_so * C.qty ) / B.qty_so_int),0) AS qty_dlv_so, A.so_uom_id, C.qty, A.base_uom_id,
142 vEmptyValue, 0, pDatetime, pUserId, pDatetime,
143 pUserId, vProductStatus
144 FROM sl_so_item A, sl_so_balance_item B, tt_delivery_order_partstation C
145 WHERE A.so_id = vSoId AND A.so_item_id = B.so_item_id AND A.product_id = C.product_id
146 AND C.session_id = pSessionId;
147
148 --update sl so balance item (QTY YANG DIKIRIM USER = QTY_DLV_INT)
149 UPDATE sl_so_balance_item A
150 SET status_item = vStatusInProgress, qty_dlv = C.qty,
151 qty_dlv_int = C.qty
152 FROM sl_so_item B, tt_delivery_order_partstation C
153 WHERE A.so_item_id = B.so_item_id AND B.product_id = C.product_id AND B.so_id = vSoId
154 AND C.session_id = pSessionId;
155
156 --update in product balance stock
157 UPDATE in_product_balance_stock A
158 SET qty = A.qty - B.qty, update_datetime = pUserId, update_user_id = pUserId,
159 version = A.version+1
160 FROM tt_delivery_order_partstation B, sl_so_item C, sl_do_item D
161 WHERE A.product_id = B.product_id AND A.warehouse_id IN
162 (
163 SELECT warehouse_id
164 FROM sl_do
165 WHERE ref_id = vSoId)
166 AND B.product_id = C.product_id AND C.so_id = vSoId
167 AND B.product_id = D.product_id AND D.product_status = vProductStatus;
168
169 --perform generate process message for submit doc delivery order
170 --insert process message (DO ID PAKE NEXTVAL)
171 PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessNameDO, vDoId ||'_'||A.doc_no,
172 pDatetime, vParamKeyDO, vDoId::character varying, pUserId)
173 FROM sl_do A
174 WHERE A.do_id = vDoId
175 GROUP BY A.doc_no;
176
177 -- Mendapatkan default approval flow ID yang dipakai dari sysconfig untuk DO
178 SELECT awe_flow_id INTO vFlowDoId
179 FROM awe_flow
180 WHERE scheme = vSchemeDeliveryOrder AND
181 flg_validate = vFlagYes AND
182 active = vFlagYes AND tenant_id = pTenantId;
183
184 --insert awe_currdoc_status Delivery order
185 INSERT INTO awe_currdoc_status(
186 req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
187 remark, current_user_id, current_role_id, flg_user_role, label,
188 data, flow_id, create_datetime, create_user_id, create_role_id,
189 update_datetime, update_user_id, update_role_id, version)
190 SELECT vDoId||'_'||A.doc_no, A.tenant_id, vSchemeDeliveryOrder, A.do_id, A.doc_no, A.doc_date, vWorkFlowStatusApproved,
191 A.remark, A.create_user_id, vEmptyId, 'R', 'DELIVERY ORDER'||A.doc_no,
192 '{}', vFlowDoId, pDatetime, pUserId, vEmptyId,
193 pDatetime, pUserId, vEmptyId, 0
194 FROM sl_do A
195 WHERE A.do_id = vDoId
196 GROUP BY A.doc_no, A.tenant_id, A.doc_date, A.remark, A.create_user_id;
197
198 --CATET KE VARIABEL INVOICE ID BERAPA, DAN INVOICE TEMP ID (2 VARIABEL)
199 SELECT NEXTVAL('sl_invoice_seq') INTO vInvoiceId;
200
201 SELECT invoice_temp_id INTO vInvoiceTempId
202 FROM sl_invoice_temp
203 WHERE ref_id = vSoId;
204
205 --insert ke dalam sl invoice (UBBAH
206 INSERT INTO sl_invoice(
207 invoice_id,tenant_id, doc_type_id, doc_no, doc_date, ou_id,
208 ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id,
209 ou_legal_id, due_date, salesman_id, curr_code, gross_amount,
210 advance_amount, tax_amount, add_amount, total_amount, status_doc,
211 workflow_status, version, create_datetime, create_user_id, update_datetime,
212 update_user_id, ref_inv_temp_id, discount_amount)
213 SELECT vInvoiceId, pTenantId, vDocTypeSalesInvoice, f_gen_autonum_with_suffix('SI/'||LEFT(pDatetime,8)||'/', 'SalesInvoice', vEmptyValue, pTenantId), A.inv_doc_date, A.ou_id,
214 A.ext_doc_no, A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
215 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, ROUND(A.gross_amount,0),
216 A.advance_amount, ROUND(A.tax_amount,0), A.add_amount, ROUND(A.total_amount,0), A.status_doc,
217 A.workflow_status, A.version, pDatetime, pUserId, pDatetime,
218 pUserId, A.invoice_temp_id, A.discount_amount
219 FROM sl_invoice_temp A
220 WHERE A.ref_id = vSoId
221 GROUP BY A.inv_doc_date, A.ou_id, A.ext_doc_no,A.ext_doc_date, A.ref_doc_type_id, A.ref_id, A.remark, A.partner_id,
222 A.ou_legal_id, A.due_date, A.salesman_id, A.curr_code, A.gross_amount, A.advance_amount, A.tax_amount,
223 A.add_amount, A.total_amount, A.status_doc,A.workflow_status, A.version, A.invoice_temp_id, A.discount_amount;
224
225 --insert ke dalam sl invoice item
226 INSERT INTO sl_invoice_item(
227 tenant_id, invoice_id, line_no, ref_doc_type_id,
228 ref_id, ref_item_id, ref_item_amount, remark, version, create_datetime,
229 create_user_id, update_datetime, update_user_id, do_receipt_item_id)
230 SELECT pTenantId, vInvoiceId, ROW_NUMBER() OVER (PARTITION BY vInvoiceId), A.ref_doc_type_id,
231 A.ref_id, A.ref_item_id, ROUND(A.ref_item_amount,0), A.remark, A.version, pDatetime,
232 pUserId, pDatetime, pUserId, A.do_receipt_item_id
233 FROM sl_invoice_temp_item A
234 WHERE A.ref_id = vDoId
235 GROUP BY A.ref_doc_type_id,A.ref_id, A.ref_item_id, A.ref_item_amount, A.remark, A.version, A.do_receipt_item_id;
236
237 --insert ke dalam sl invoice tax
238 INSERT INTO sl_invoice_tax(
239 tenant_id, invoice_id, tax_id, flg_amount, tax_percentage,
240 base_amount, tax_amount, tax_no, tax_date, tax_curr_code, gov_tax_amount,
241 remark, version, create_datetime, create_user_id, update_datetime,
242 update_user_id, advance_amount, total_tax_disc_amount)
243 SELECT pTenantId, vInvoiceId, A.tax_id, A.flg_amount, A.tax_percentage,
244 ROUND(A.base_amount,0), ROUND(A.tax_amount,0), A.tax_no, A.tax_date, A.tax_curr_code, ROUND(A.gov_tax_amount,0),
245 A.remark, A.version, pDatetime, pUserId, pDatetime,
246 pUserId, A.advance_amount, ROUND(A.total_tax_disc_amount,0)
247 FROM sl_invoice_temp_tax A
248 WHERE A.invoice_temp_id = vInvoiceTempId
249 GROUP BY A.tax_id, A.flg_amount, A.tax_percentage, A.base_amount, A.tax_amount, A.tax_no, A.tax_date,
250 A.tax_curr_code, A.gov_tax_amount, A.remark, A.version, A.advance_amount, A.total_tax_disc_amount;
251
252 --perform generate process message for submit doc sales invoice
253 --insert process message
254 PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessNameSI, vInvoiceId ||'_'||A.doc_no,
255 pDatetime, vParamKeySI, vInvoiceId::character varying, pUserId)
256 FROM sl_invoice A
257 WHERE A.invoice_id = vInvoiceId
258 GROUP BY A.doc_no;
259
260 -- Mendapatkan default approval flow ID yang dipakai dari sysconfig untuk DO
261 SELECT awe_flow_id INTO vFlowInvoiceId
262 FROM awe_flow
263 WHERE scheme = vSchemeSalesInvoice AND
264 flg_validate = vFlagYes AND
265 active = vFlagYes AND tenant_id = pTenantId;
266
267 --insert awe_currdoc_status SI
268 INSERT INTO awe_currdoc_status(
269 req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
270 remark, current_user_id, current_role_id, flg_user_role, label,
271 data, flow_id, create_datetime, create_user_id, create_role_id,
272 update_datetime, update_user_id, update_role_id, version)
273 SELECT vInvoiceId||'_'||A.doc_no, A.tenant_id, vSchemeSalesInvoice, vInvoiceId, A.doc_no, A.doc_date, vWorkFlowStatusApproved,
274 A.remark, A.create_user_id, vEmptyId, 'R', 'SALES INVOICE'||A.doc_no,
275 '{}', vFlowInvoiceId, pDatetime, pUserId, vEmptyId,
276 pDatetime, pUserId, vEmptyId, 0
277 FROM sl_invoice A
278 WHERE A.invoice_id = vInvoiceId
279 GROUP BY A.doc_no, A.tenant_id, A.doc_date, A.remark, A.create_user_id;
280
281 --SETELAH SELESAI INSERT, UPDATE STATUS tt_delivery_order_partstation MENJADI R
282 UPDATE tt_delivery_order_partstation
283 SET status = vStatusDocApproved
284 WHERE status = vStatusInProgress;
285 END IF;
286
287END
288$BODY$
289LANGUAGE plpgsql VOLATILE
290COST 100;
291/