Update 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 = '20180118154300'
FROM get_data_qty_rcv_invalid B
WHERE A.po_item_id = B.po_item_id;

-- Update status item = I, jika dok GR nya ada yg status_doc nya <>  R
UPDATE pu_po_balance_item A
SET status_item = 'I',
    update_user_id = -3,
    update_datetime = '20180118154300'
WHERE EXISTS(SELECT 1 FROM pu_receive_goods B, pu_receive_goods C
         WHERE B.receive_goods_id = C.receive_goods_id AND
           C.ref_id = A.po_item_id AND
           B.status_doc <> 'R');

-- Update status item = R, jika dok GR nya status_doc nya =  R semua dan masih ada outstanding qty PO
UPDATE pu_po_balance_item A
SET status_item = 'R',
    update_user_id = -3,
    update_datetime = '20180118154300'
WHERE NOT EXISTS(SELECT 1 FROM pu_receive_goods B, pu_receive_goods C
             WHERE B.receive_goods_id = C.receive_goods_id AND
               C.ref_id = A.po_item_id AND
               B.status_doc <> 'R')
      AND (A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv) > 0;

-- Update status item = F, jika dok GR nya status_doc nya =  R semua dan outstanding qty PO = 0
UPDATE pu_po_balance_item A
SET status_item = 'F',
    update_user_id = -3,
    update_datetime = '20180118154300'
WHERE NOT EXISTS(SELECT 1 FROM pu_receive_goods B, pu_receive_goods C
             WHERE B.receive_goods_id = C.receive_goods_id AND
               C.ref_id = A.po_item_id AND
               B.status_doc <> 'R')
      AND (A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv) = 0;