Script Update Issue KAT-778

--Script Update Data KAT-778
-- pu_receive_goods_item
UPDATE pu_receive_goods_item A SET qty_rcv_po = B.qty_po
FROM pu_po_item B
WHERE EXISTS (SELECT 1 FROM pu_receive_goods B WHERE A.receive_goods_id = B.receive_goods_id AND B.doc_no = 'RG-1903-00001')
AND B.qty_po < A.qty_rcv_po
AND A.ref_id = B.po_item_id;

-- pu_po_balance_item
UPDATE pu_po_balance_item A SET qty_rcv = qty_po, qty_int_rcv = qty_int_po
WHERE EXISTS (SELECT 1 FROM pu_po_item B
        INNER JOIN pu_receive_goods_item C ON B.po_item_id = C.ref_id
        INNER JOIN pu_receive_goods D ON C.receive_goods_id = D.receive_goods_id
        WHERE D.doc_no = 'RG-1903-00001'
        AND A.po_item_id = B.po_item_id
        AND B.qty_po < C.qty_rcv_po);

-- pu_po_balance_invoice
UPDATE pu_po_balance_invoice A SET qty_rcv_po = C.qty_po
FROM pu_po_item C
WHERE A.ref_doc_no = 'RG-1903-00001'
AND A.po_id = C.po_id
AND EXISTS (SELECT 1 FROM pu_receive_goods_item B
        WHERE A.ref_item_id = B.receive_goods_item_id
        AND C.po_item_id = B.ref_id
        AND C.qty_po < B.qty_rcv_po);

-- pu_receive_goods_product
UPDATE pu_receive_goods_product A SET qty_rcv_po = D.qty_po, qty_rcv_int = D.qty_po
FROM pu_receive_goods_item B
INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
INNER JOIN pu_po_item D ON B.ref_id = D.po_item_id
WHERE A.receive_goods_item_id = B.receive_goods_item_id
AND C.doc_no = 'RG-1903-00001'
AND D.qty_po < B.qty_rcv_po
AND A.receive_goods_item_id = B.receive_goods_item_id

-- in_product_price_balance
UPDATE in_product_price_balance A SET amount = ((A.amount / A.qty) * C.qty_po), qty = C.qty_po
FROM pu_receive_goods_item B
INNER JOIN pu_po_item C ON B.ref_id = C.po_item_id
WHERE A.doc_no  = 'RG-1903-00001'
AND C.qty_po < B.qty_rcv_po
AND A.ref_id = B.receive_goods_item_id

-- in_product_balance_stock
WITH tt_product_balance_id AS (
SELECT A.product_id, B.product_balance_id, E.po_item_id
FROM in_product_balance_stock A
INNER JOIN in_product_balance B ON B.product_id = A.product_id AND A.product_balance_id = B.product_balance_id
INNER JOIN pu_receive_goods_product C ON B.serial_number = C.serial_number AND B.lot_number = C.lot_number
INNER JOIN pu_receive_goods_item D ON C.receive_goods_item_id = D.receive_goods_item_id
INNER JOIN pu_po_item E ON D.ref_id = E.po_item_id
INNER JOIN pu_receive_goods F ON D.receive_goods_id = F.receive_goods_id
WHERE F.doc_no  = 'RG-1903-00001'
AND E.qty_po < D.qty_rcv_po

)
UPDATE in_product_balance_stock A SET qty = (A.qty - (D.qty_rcv_po - C.qty_po))
FROM tt_product_balance_id B
INNER JOIN pu_po_item C ON B.po_item_id = C.po_item_id
INNER JOIN pu_receive_goods_item D ON C.po_item_id = D.ref_id
WHERE A.product_balance_id = B.product_balance_id


-- in_product_price_balance_stock
WITH tt_product_balance_id AS (
SELECT A.product_id, B.product_balance_id, E.po_item_id
FROM in_product_balance_stock A
INNER JOIN in_product_balance B ON B.product_id = A.product_id AND A.product_balance_id = B.product_balance_id
INNER JOIN pu_receive_goods_product C ON B.serial_number = C.serial_number AND B.lot_number = C.lot_number
INNER JOIN pu_receive_goods_item D ON C.receive_goods_item_id = D.receive_goods_item_id
INNER JOIN pu_po_item E ON D.ref_id = E.po_item_id
INNER JOIN pu_receive_goods F ON D.receive_goods_id = F.receive_goods_id
WHERE F.doc_no  = 'RG-1903-00001'
AND E.qty_po < D.qty_rcv_po

)
UPDATE in_product_price_balance_stock A SET qty = C.qty_po
FROM tt_product_balance_id B
INNER JOIN pu_po_item C ON B.po_item_id = C.po_item_id
WHERE A.product_balance_id = B.product_balance_id

-- in_balance_receive_goods_item
UPDATE in_balance_receive_goods_item A SET qty_rcv = D.qty_po, qty_int_rcv = D.qty_po
FROM pu_receive_goods_item B
INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
INNER JOIN pu_po_item D ON A.po_item_id = D.po_item_id
WHERE C.doc_no = 'RG-1903-00001'
AND D.qty_po < B.qty_rcv_po
AND A.receive_goods_item_id = B.receive_goods_item_id

SELECT * FROM pu_monthly_price_product WHERE ref_doc_no  = 'RG-1903-00001'

-- in_product_price_balance
UPDATE pu_monthly_price_product A SET amount = ((A.amount / A.qty) * C.qty_po), qty = C.qty_po
FROM pu_receive_goods_item B
INNER JOIN pu_po_item C ON B.ref_id = C.po_item_id
WHERE A.doc_no  = 'RG-1903-00001'
AND C.qty_po < B.qty_rcv_po
AND A.ref_id = B.receive_goods_item_id

-- pu_monthly_price_product
WITH tt_product_for_pu_monthly_price_product AS (
    SELECT B.product_id, C.qty_po
    FROM pu_receive_goods_item B
    INNER JOIN pu_po_item C ON B.ref_id = C.po_item_id
    AND C.qty_po < B.qty_rcv_po
)
UPDATE pu_monthly_price_product A SET amount =((A.amount / A.qty) * B.qty_po), qty = B.qty_po
FROM tt_product_for_pu_monthly_price_product B
WHERE A.ref_doc_no = 'RG-1903-00001'
AND A.product_id = B.product_id

-- gl_journal_trx_item
WITH tt_product_for_gl_journal_trx_item AS (
    SELECT B.product_id, C.qty_po, E.journal_trx_id
    FROM pu_receive_goods_item B
    INNER JOIN pu_po_item C ON B.ref_id = C.po_item_id
    INNER JOIN pu_receive_goods D ON B.receive_goods_id = D.receive_goods_id
    INNER JOIN gl_journal_trx E ON D.receive_goods_id = E.doc_id AND E.doc_type_id = 111
    AND C.qty_po < B.qty_rcv_po
    AND D.doc_no = 'RG-1903-00001'
)
UPDATE gl_journal_trx_item A SET amount =((A.amount / A.qty) * B.qty_po), qty = B.qty_po
FROM tt_product_for_gl_journal_trx_item B
WHERE A.product_id = B.product_id
AND A.journal_trx_id = B.journal_trx_id

-- gl_journal_trx_mapping
WITH tt_journal_trx_item AS (
    SELECT D.journal_trx_id, SUM (f_get_amount_before_tax_for_ktmt(C.gross_price_po, C.flg_tax_amount, C.tax_percentage,f_get_digit_decimal_doc_curr(111, C.curr_code), 'RHU', C.discount_amount, B.qty_rcv_po)) AS amount
    FROM pu_receive_goods A, pu_receive_goods_item B, pu_po_item C, gl_journal_trx D
    WHERE A.doc_no = 'RG-1903-00001'
    AND A.receive_goods_id = B.receive_goods_id
    AND B.ref_id = C.po_item_id
    AND C.flg_stock = 'Y'
    AND A.receive_goods_id = D.doc_id AND D.doc_type_id = 111
    GROUP BY D.journal_trx_id
    )
UPDATE gl_journal_trx_mapping A SET amount = B.amount
FROM tt_journal_trx_item B
WHERE A.journal_trx_id = B.journal_trx_id