Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION in_submit_put_away(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- vFlagNo character varying(1);
- vFlagYes character varying(1);
- vStatusRelease character varying(1);
- BEGIN
- vFlagNo := 'N';
- vFlagYes := 'Y';
- vStatusRelease := 'R';
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'in_submit_put_away' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'id';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'userId';
- SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'datetime';
- /*
- * 1.update status doc in_inventory
- * 2.insert data ke in_log_product_balance_stock
- * 3.add data in_balance_transfer_out
- */
- UPDATE in_inventory SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
- WHERE inventory_id = vId;
- INSERT INTO in_log_product_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
- product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
- WHERE A.inventory_id = vId AND
- A.inventory_id = B.inventory_id AND
- A.warehouse_from_id = C.warehouse_id
- GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
- B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
- -- INSERT kan ke in_balance_transfer_out langsung dengan flg_real = Y
- INSERT INTO in_balance_transfer_out
- (inventory_id, tenant_id, ou_id, warehouse_id, flg_real, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.inventory_id, A.tenant_id, A.ou_to_id, A.warehouse_to_id, vFlagYes, A.remark,
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A
- WHERE A.inventory_id = vId;
- ---------------------------------------------------------------------------
- /*
- * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
- */
- INSERT INTO tt_in_product_balance_summary_stock
- (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status,
- B.base_uom_id, SUM(B.qty_realization)
- FROM in_inventory A, in_inventory_item B
- WHERE A.inventory_id = B.inventory_id
- AND A.inventory_id = vId
- 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;
- UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId
- FROM tt_in_product_balance_summary_stock A
- WHERE A.session_id = pSessionId AND
- A.inventory_id = vId AND
- in_product_balance_stock.tenant_id = A.tenant_id AND
- in_product_balance_stock.warehouse_id = A.warehouse_id AND
- in_product_balance_stock.product_id = A.product_id AND
- in_product_balance_stock.product_balance_id = A.product_balance_id AND
- in_product_balance_stock.product_status = A.product_status;
- /*
- * insert data in_product_balance_stock
- */
- INSERT INTO in_product_balance_stock
- (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- 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),
- 0, vDatetime, vUserId, vDatetime, vUserId
- FROM in_inventory A
- INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
- WHERE A.inventory_id = vId AND
- NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
- WHERE A.tenant_id = C.tenant_id AND
- A.warehouse_to_id = C.warehouse_id AND
- B.product_id = C.product_id AND
- B.product_balance_id = C.product_balance_id AND
- B.product_status = C.product_status)
- GROUP BY A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement