FIX_LOG_STOCK_GENERATE_PO_DAN_RG

CREATE TABLE in_log_product_balance_stock_20180131 AS SELECT * FROM in_log_product_balance_stock

SELECT * FROM pu_receive_goods LIMIT 10

SELECT * FROM pu_receive_goods_item LIMIT 10

SELECT product_status_code --INTO vProductStatus
    FROM m_product_status
    WHERE flg_buy = 'Y'


SELECT * FROM pu_receive_goods WHERE doc_no = 'GRR/201610/010/000012'
SELECT * FROM pu_receive_goods_item WHERE receive_goods_id = 774

SELECT * FROM pu_po WHERE po_id = 1134

SELECT * FROM pu_po_item WHERE po_id = 1134

SELECT * FROM pu_po_


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_20180131 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
)

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.tenant_id = A.tenant_id
                    AND B.ou_id = A.ou_id
                    AND B.doc_type_id = A.doc_type_id
                    AND B.receive_goods_id = A.ref_id
                    AND B.doc_no = A.doc_no
                    AND B.doc_date = A.doc_date
                    AND B.warehouse_id = A.warehouse_id
                    AND C.product_id = A.product_id
                    --AND C.product_balance_id = A.product_balance_id
                    --AND C.product_status = A.product_status
                    AND C.qty_rcv_int = A.qty
    )

BEGIN

        /*INSERT INTO tr_report_sales_supplier(
                   session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                   doc_date, payment_method, product_id, product_code, supplier_product_code,
                   product_name, product_style, color, psize, normal_price, sold_price,
                   discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)*/
            SELECT 'ADR123', 10, 10, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
                   A.doc_date, $4, A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
                   f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
                   F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
            SELECT A.product_balance_id
            FROM in_log_product_balance_stock A
            INNER JOIN m_product_custom B ON A.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
            INNER JOIN in_inventory D ON A.ref_id = D.inventory_id
            INNER JOIN in_inventory_item G ON D.inventory_id = G.inventory_id AND A.product_id = G.product_id
            INNER JOIN sl_do_item E ON G.ref_item_id = E.do_item_id AND G.product_id = E.product_id
            INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
            WHERE A.tenant_id = 10
                --AND A.doc_date BETWEEN '201601' AND $6
                AND A.doc_date LIKE '2016%'
                AND A.ou_id = 10
                AND B.flg_buy_konsinyasi = 'Y'
                AND A.doc_type_id IN (502)
            AND F.from_manual = 'N'
            GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id


        /*INSERT INTO tr_report_sales_supplier(
                   session_id, tenant_id, ou_id, partner_id, so_item_id, transaction_type, doc_no,
                   doc_date, payment_method, product_id, product_code, supplier_product_code,
                   product_name, product_style, color, psize, normal_price, sold_price,
                   discount, margin_supp, sold_price_after_margin, qty_so, product_balance_id)*/
            SELECT 'ADR123', 10, 10, C.supplier_id, E.ref_id, f_get_doc_desc(A.doc_type_id), A.doc_no,
                   A.doc_date, '', A.product_id, f_get_product_code(A.product_id), C.supplier_product_code,
                   f_get_product_name(A.product_id), B.style_product, B.color, B.size, F.normal_price_correction, F.sold_price_used,
                   F.discount, F.margin_supp_correction, F.sold_price_after_margin, SUM(A.qty) * -1, A.product_balance_id
            FROM in_log_product_balance_stock A
            INNER JOIN m_product_custom B ON A.product_id = B.product_id
            INNER JOIN m_product_consignment_supp_info C ON A.product_id = C.product_id
            INNER JOIN in_do_receipt D ON A.ref_id = D.do_receipt_id
            INNER JOIN in_do_receipt_item G ON D.do_receipt_id = G.do_receipt_id AND A.product_id = G.product_id
            INNER JOIN sl_do_item E ON G.ref_id = E.do_item_id AND G.product_id = E.product_id
            INNER JOIN pu_po_balance_item_consignment_sold F ON E.ref_id = F.so_item_id AND C.supplier_id = F.supplier_id
            WHERE A.tenant_id = 10
                AND A.doc_date LIKE '2016%'
                AND A.ou_id = 10
                AND B.flg_buy_konsinyasi = 'Y'
                AND A.doc_type_id IN (526)
            AND F.from_manual = 'N'
            GROUP BY C.supplier_id, E.ref_id, A.doc_type_id, A.doc_no, A.doc_date, A.product_id, C.supplier_product_code, B.style_product, B.color, B.size,
            F.normal_price_correction, F.sold_price_used, F.discount, F.margin_supp_correction, F.sold_price_after_margin, A.product_balance_id

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
    )

    SELECT * FROM in_log_product_balance_stock WHERE create_datetime = '20180131132801' ORDER BY doc_date

    DELETE FROM in_log_product_balance_stock WHERE create_datetime = '20180131132801'

    SELECT * FROM in_log_product_balance_stock WHERE create_datetime = '20180131132801'
    WHERE EXISTS (
        SELECT 1
        FROM in_log_product_balance_stock
        WHERE create_datetime = '20180131132801'
    )

    SELECT tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, warehouse_id, product_id, product_balance_id, product_status FROM in_log_product_balance_stock WHERE create_datetime = '20180131132801'
    GROUP BY tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, warehouse_id, product_id, product_balance_id, product_status
    ORDER BY doc_date

    WITH tt_update_stock AS (
        WITH tt_not_found AS (
            SELECT * FROM in_log_product_balance_stock_20180131 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 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) AS qty_rcv_int
        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
        )
        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
    )
    UPDATE in_log_product_balance_stock Y
    SET qty = Y.qty + A.qty_rcv_int
    FROM tt_update_stock A
        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 A.product_id = Y.product_id
        AND A.warehouse_id = Y.warehouse_id
        AND A.product_balance_id = Y.product_balance_id
        AND A.base_uom_id = Y.base_uom_id
        AND A.product_status = Y.product_status

    WITH tt_not_found AS (
        SELECT * FROM in_log_product_balance_stock_20180131 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
    )


        --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_update_stock AS (
        WITH tt_not_found AS (
            SELECT * FROM in_log_product_balance_stock_20180131 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
                )
        )
        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) AS qty_rcv_int
        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
        )
        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
    )
    UPDATE in_log_product_balance_stock Y
    SET qty = Y.qty + A.qty_rcv_int
    FROM tt_update_stock A
        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 A.product_id = Y.product_id
        AND A.warehouse_id = Y.warehouse_id
        AND A.product_balance_id = Y.product_balance_id
        AND A.base_uom_id = Y.base_uom_id
        AND A.product_status = Y.product_status

    WITH tt_not_found AS (
        SELECT * FROM in_log_product_balance_stock_20180131 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


END


    WITH tt_not_found AS (
        SELECT * FROM in_log_product_balance_stock_20180131 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, B.qty_rcv_int, 0,
            '20180131132801', -1, '20180131132801', -1*/
        SELECT A.*
        FROM pu_receive_goods A
        INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
        --INNER JOIN pu_receive_goods_product C ON B.receive_goods_item_id = C.receive_goods_item_id
        /*INNER JOIN in_product_balance D ON
        A.tenant_id = D.tenant_id AND
        B.product_id = D.product_id */
        --AND
        --C.serial_number = D.serial_number AND
        --C.lot_number = D.lot_number
        AND EXISTS(
        SELECT 1
        FROM tt_not_found Z
        WHERE A.receive_goods_id = Z.ref_id
        )


SELECT * FROM pu_receive_goods_product WHERE receive_goods_item_id = 21010

ROLLBACK


SELECT log_product_balance_stock_id, 'N', tenant_id, A.ou_id, doc_type_id, ref_id, doc_no,
       doc_date, partner_id, A.product_id, D.flg_buy_konsinyasi, warehouse_id, product_balance_id,
       product_status, base_uom_id, qty, ' ', -99, B.ou_bu_id, '201712'
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, m_product_custom D
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date = '201712' AND
        A.product_id = D.product_id AND
        A.tenant_id = 10

    SELECT *
    FROM in_log_product_balance_stock A, m_ou_structure B, dt_date C, m_product_custom D
    WHERE A.ou_id = B.ou_id AND
        B.ou_bu_id = 10 AND
        A.doc_date = C.string_date AND
        C.year_month_date = '201712' AND
        A.product_id = D.product_id AND
        A.tenant_id = 10
    AND 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.ref_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 A.product_id = Y.product_id
            AND A.warehouse_id = Y.warehouse_id
            AND A.product_balance_id = Y.product_balance_id
            AND A.base_uom_id = Y.base_uom_id
            AND A.product_status = Y.product_status
            AND A.log_product_balance_stock_id <> Y.log_product_balance_stock_id
    )