KPS_PERBAIKI_DATA_LOG

BEGIN
CREATE TABLE in_log_product_balance_stock_20180221 AS SELECT * FROM in_log_product_balance_stock

 SELECT COUNT(1) FROM in_log_product_balance_stock

 SELECT COUNT(1) FROM in_log_product_balance_stock_20180221

CREATE TABLE in_log_product_consignment_balance_stock_20180221 AS SELECT * FROM in_log_product_consignment_balance_stock

SELECT COUNT(1) FROM in_log_product_consignment_balance_stock

SELECT COUNT(1) FROM in_log_product_consignment_balance_stock_20180221


SELECT
 * FROM in_log_product_balance_stock A WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2016%' AND NOT EXISTS (
    SELECT 1 FROM pu_receive_goods B
    INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
    WHERE B.doc_no = A.doc_no
    AND B.doc_date = A.doc_date
    AND B.doc_type_id = A.doc_type_id
    AND B.receive_goods_id =A.ref_id
    AND C.product_id = A.product_id
    AND C.qty_rcv_int = A.qty
)

SELECT * FROM in_log_product_balance_stock A WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2017%' AND NOT EXISTS (
    SELECT 1 FROM pu_receive_goods B
    INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
    WHERE B.doc_no = A.doc_no
    AND B.doc_date = A.doc_date
    AND B.doc_type_id = A.doc_type_id
    AND B.receive_goods_id =A.ref_id
    AND C.product_id = A.product_id
    AND C.qty_rcv_int = A.qty
)



BEGIN

    --DELETE FROM in_log_product_balance_stock A WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2016%' AND NOT EXISTS (
        SELECT 1 FROM pu_receive_goods B
        INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
        WHERE B.doc_no = A.doc_no
        AND B.doc_date = A.doc_date
        AND B.doc_type_id = A.doc_type_id
        AND B.receive_goods_id =A.ref_id
        AND C.product_id = A.product_id
        AND C.qty_rcv_int = A.qty
    )


    WITH tt_not_found AS (
        SELECT * FROM in_log_product_balance_stock_20180221 A
        WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2016%'
            AND NOT EXISTS (
                SELECT 1 FROM pu_receive_goods B
                INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
                WHERE B.doc_no = A.doc_no
                AND B.doc_date = A.doc_date
                AND B.doc_type_id = A.doc_type_id
                AND B.receive_goods_id =A.ref_id
                AND C.product_id = A.product_id
                AND C.qty_rcv_int = A.qty
            )
    )
    INSERT INTO in_log_product_balance_stock(
            tenant_id, ou_id, doc_type_id,
            ref_id, doc_no, doc_date, partner_id, product_id, warehouse_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.ou_id, A.doc_type_id,
            A.receive_goods_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, A.warehouse_id,
            D.product_balance_id, 'GOOD', B.base_uom_id, SUM(B.qty_rcv_int), 0,
            '20180131132801', -1, '20180131132801', -1
        FROM pu_receive_goods A
        INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
        INNER JOIN in_product_balance D ON
        A.tenant_id = D.tenant_id AND
        B.product_id = D.product_id
        AND EXISTS(
        SELECT 1
        FROM tt_not_found Z
        WHERE A.receive_goods_id = Z.ref_id
            AND A.doc_type_id = Z.doc_type_id
            AND A.doc_no = Z.doc_no
            AND A.doc_date = Z.doc_date
        )
        AND NOT EXISTS(
        SELECT 1
        FROM in_log_product_balance_stock Y
        WHERE A.tenant_id = Y.tenant_id
            AND A.ou_id = Y.ou_id
            AND A.doc_type_id = Y.doc_type_id
            AND A.receive_goods_id =  Y.ref_id
            AND A.doc_no = Y.doc_no
            AND A.doc_date = Y.doc_date
            AND A.partner_id = Y.partner_id
            AND B.product_id = Y.product_id
            AND A.warehouse_id = Y.warehouse_id
            AND D.product_balance_id = Y.product_balance_id
            AND B.base_uom_id = Y.base_uom_id
            AND Y.product_status = 'GOOD'
        )
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id,
            A.receive_goods_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, A.warehouse_id,
            D.product_balance_id, B.base_uom_id



    --DELETE FROM in_log_product_balance_stock A WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2017%' AND NOT EXISTS (
        SELECT 1 FROM pu_receive_goods B
        INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
        WHERE B.doc_no = A.doc_no
        AND B.doc_date = A.doc_date
        AND B.doc_type_id = A.doc_type_id
        AND B.receive_goods_id =A.ref_id
        AND C.product_id = A.product_id
        AND C.qty_rcv_int = A.qty
    )


    WITH tt_not_found AS (
        SELECT * FROM in_log_product_balance_stock_20180221 A
        WHERE A.doc_no LIKE 'GRR%' AND A.doc_date LIKE '2017%'
            AND NOT EXISTS (
                SELECT 1 FROM pu_receive_goods B
                INNER JOIN pu_receive_goods_item C ON B.receive_goods_id = C.receive_goods_id
                WHERE B.doc_no = A.doc_no
                AND B.doc_date = A.doc_date
                AND B.doc_type_id = A.doc_type_id
                AND B.receive_goods_id =A.ref_id
                AND C.product_id = A.product_id
                AND C.qty_rcv_int = A.qty
            )
    )
    INSERT INTO in_log_product_balance_stock(
            tenant_id, ou_id, doc_type_id,
            ref_id, doc_no, doc_date, partner_id, product_id, warehouse_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.ou_id, A.doc_type_id,
            A.receive_goods_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, A.warehouse_id,
            D.product_balance_id, 'GOOD', B.base_uom_id, SUM(B.qty_rcv_int), 0,
            '20180131132801', -1, '20180131132801', -1
        FROM pu_receive_goods A
        INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
        INNER JOIN in_product_balance D ON
        A.tenant_id = D.tenant_id AND
        B.product_id = D.product_id
        AND EXISTS(
        SELECT 1
        FROM tt_not_found Z
        WHERE A.receive_goods_id = Z.ref_id
            AND A.doc_type_id = Z.doc_type_id
            AND A.doc_no = Z.doc_no
            AND A.doc_date = Z.doc_date
        )
         AND NOT EXISTS(
        SELECT 1
        FROM in_log_product_balance_stock Y
        WHERE A.tenant_id = Y.tenant_id
            AND A.ou_id = Y.ou_id
            AND A.doc_type_id = Y.doc_type_id
            AND A.receive_goods_id =  Y.ref_id
            AND A.doc_no = Y.doc_no
            AND A.doc_date = Y.doc_date
            AND A.partner_id = Y.partner_id
            AND B.product_id = Y.product_id
            AND A.warehouse_id = Y.warehouse_id
            AND D.product_balance_id = Y.product_balance_id
            AND B.base_uom_id = Y.base_uom_id
            AND Y.product_status = 'GOOD'
        )
        GROUP BY A.tenant_id, A.ou_id, A.doc_type_id,
            A.receive_goods_id, A.doc_no, A.doc_date, A.partner_id, B.product_id, A.warehouse_id,
            D.product_balance_id, B.base_uom_id

ROLLBACK



BEGIN

    UPDATE in_log_product_consignment_balance_stock SET doc_date = '20171103' WHERE ref_id = 4485556 AND doc_type_id = 311 AND doc_no = 'DO/000170/KPS/201709' AND ou_id = 10 AND tenant_id = 10 AND doc_date = '20170929'

    UPDATE in_log_product_consignment_balance_stock SET doc_date = '20171103' WHERE ref_id = 4485699 AND doc_type_id = 311 AND doc_no = 'DO/000001/KPS/201710' AND ou_id = 10 AND tenant_id = 10 AND doc_date = '20171002'

ROLLBACK

END