Script perbaikan data po balance item

/* Perbaiki qty_rcv dan qty_int_rcv data pu_po_balance_item */

WITH get_po_balance_item AS(
    -- Ambil data po balance item yg sudah di GR
    SELECT A.po_item_id, A.qty_int_po, A.qty_int_rcv
    FROM pu_po_balance_item A
    WHERE A.qty_int_rcv <> 0
), get_data_qty_rcv_invalid AS(

    -- Dapatkan data receive goods dimana qty_rcv antara yg di dokumen GR dan di po balance item beda (pastikan summary qty rcv di dokumen GR <= qty po)
    -- Ambil semua data GR apapun status doc nya
    SELECT A.ref_id AS po_id, B.ref_id AS po_item_id,
           SUM(B.qty_rcv_int) AS sum_qty_rcv, C.qty_int_po,
           C.qty_int_rcv
    FROM pu_receive_goods A
    INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
    INNER JOIN get_po_balance_item C ON B.ref_id = C.po_item_id
    GROUP BY A.ref_id, B.ref_id, C.qty_int_po, C.qty_int_rcv
    HAVING SUM(B.qty_rcv_int) <> C.qty_int_rcv AND
           SUM(B.qty_rcv_int) <= C.qty_int_po
    ORDER BY A.ref_id, B.ref_id
)
UPDATE pu_po_balance_item A
SET qty_rcv = B.sum_qty_rcv,
    qty_int_rcv = B.sum_qty_rcv,
    update_user_id = -3,
    update_datetime = '20180119100000'
FROM get_data_qty_rcv_invalid B
WHERE A.po_item_id = B.po_item_id;

-------------------------------------------------------------------------------------------------
/* Update semua data PO balance item yg nggak ada dokumen RG O/S dan Manage PO O/S */
UPDATE pu_po_balance_item A
SET status_item = 'R',
    update_user_id = -3,
    update_datetime = '20180119100000'
WHERE NOT EXISTS(SELECT 1 FROM pu_receive_goods B, pu_receive_goods_item C
             WHERE B.receive_goods_id = C.receive_goods_id AND
               C.ref_id = A.po_item_id AND
               B.status_doc <> 'R')
      AND NOT EXISTS(SELECT 1 FROM pu_manage_po B, pu_manage_po_item C
                 WHERE B.manage_po_id = C.manage_po_id AND
               C.po_item_id = A.po_item_id AND
                   B.status_doc <> 'R')

/* update data PO balance item menjadi F, tuk semua item yg status item = R, dan (A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv) <= 0 */
UPDATE pu_po_balance_item A
SET status_item = 'F',
    update_user_id = -3,
    update_datetime = '20180119100000'
WHERE A.status_item = 'R' AND
      (A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv) <= 0;

-------------------------------------------------------------------------------------------------