f_cleanup_order_blibli

/**
 * 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;
/