Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /**
- * Adrian, Aug 3, 2018
- */
- CREATE OR REPLACE FUNCTION f_cleanup_order_blibli(bigint, bigint, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pOrderId ALIAS FOR $1;
- pUserId ALIAS FOR $2;
- pDatetime ALIAS FOR $3;
- vChannelId bigint;
- vChannelCode character varying;
- vEmptyString character varying(1);
- vKeyLastDate character varying;
- vKeyLastItemNo character varying;
- vKeyLastNo character varying;
- BEGIN
- vEmptyString := '';
- vKeyLastDate := 'blibli.newOrder.lastDate';
- vKeyLastItemNo := 'blibli.newOrder.lastItemNo';
- vKeyLastNo := 'blibli.newOrder.lastNo';
- vChannelCode := 'BLIBLI';
- SELECT channel_id FROM m_channel WHERE channel_code = vChannelCode INTO vChannelId;
- --Update stock available
- WITH tt_product_qty AS (
- SELECT A.product_id, SUM(A.qty) AS qty
- FROM sdp_order_item A
- INNER JOIN sdp_order B ON A.order_id = B.order_id
- WHERE B.order_id >= pOrderId
- AND B.channel_id = vChannelId
- GROUP BY A.product_id
- )
- UPDATE sdp_product_balance_available_stock A
- SET qty = A.qty + B.qty,
- update_datetime = pDatetime,
- update_user_id = pUserId
- FROM tt_product_qty B
- WHERE A.product_id = B.product_id;
- --Hapus log stock
- DELETE FROM sdp_log_product_balance_stock Z WHERE EXISTS (
- SELECT 1
- FROM sdp_order_item A
- INNER JOIN sdp_order B ON A.order_id = B.order_id
- WHERE B.order_id >= pOrderId
- AND B.channel_id = vChannelId
- AND Z.ref_id = A.order_id
- AND Z.ref_doc_no = B.order_no
- );
- --Hapus SDP Order Shipment
- DELETE FROM sdp_order_shipment A WHERE EXISTS (
- SELECT 1 FROM sdp_order B
- WHERE B.order_id >= pOrderId
- AND B.channel_id = vChannelId
- AND A.order_id = B.order_id
- );
- --Hapus SDP Order Item
- DELETE FROM sdp_order_item A WHERE EXISTS (
- SELECT 1 FROM sdp_order B
- WHERE B.order_id >= pOrderId
- AND B.channel_id = vChannelId
- AND A.order_id = B.order_id
- );
- --Hapus SDP Order Ext
- DELETE FROM sdp_order_ext A WHERE EXISTS (
- SELECT 1 FROM sdp_order B
- WHERE B.order_id >= pOrderId
- AND B.channel_id = vChannelId
- AND A.order_id = B.order_id
- );
- --Hapus SDP Order
- DELETE FROM sdp_order A WHERE A.order_id >= pOrderId AND A.channel_id = vChannelId;
- --Update system config
- PERFORM f_set_system_config(pUserId, pDatetime, A.tenant_id, vKeyLastDate, vEmptyString)
- FROM t_tenant A;
- PERFORM f_set_system_config(pUserId, pDatetime, A.tenant_id, vKeyLastItemNo, vEmptyString)
- FROM t_tenant A;
- PERFORM f_set_system_config(pUserId, pDatetime, A.tenant_id, vKeyLastNo, vEmptyString)
- FROM t_tenant A;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement