Script Update Data RN For Finance PPn 11%

BEGIN;
--> buat data temporary
INSERT INTO tt_in_po_balance_item
        (session_id, tenant_id, ou_id, doc_type_id,
        doc_no, doc_date, inventory_id, partner_id,
        inventory_item_id, po_id, receive_goods_id, receive_goods_item_id,
        qty_return, base_uom_id, remark,
        po_item_id, curr_code, price,
        flg_tax_amount, qty_po, qty_int_po,
        po_uom_id, tax_id, tax_percentage)
        SELECT 'SESI1', A.tenant_id, A.ou_from_id, A.doc_type_id,
                A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
                B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
                SUM(B.qty_realization), B.base_uom_id, A.remark,
                D.po_item_id, D.curr_code, D.gross_price_po - D.discount_amount,
                D.flg_tax_amount, D.qty_po, D.qty_int,
                D.po_uom_id, D.tax_id, 11.00
        FROM in_inventory A, in_inventory_item B, in_balance_receive_goods_item C, pu_po_item D
        WHERE A.inventory_id = 42039 AND
              A.inventory_id = B.inventory_id AND
              B.ref_item_id = C.receive_goods_item_id AND
              C.po_item_id = D.po_item_id
        GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
                A.doc_no, A.doc_date, A.inventory_id, A.partner_id,
                B.inventory_item_id, A.ref_id, B.ref_id, B.ref_item_id,
        B.base_uom_id, D.po_item_id, D.curr_code, D.nett_price_po, D.qty_po, D.qty_int, D.po_uom_id, D.tax_id ;

WITH item_amount AS (
SELECT A.session_id, SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po,
    A.flg_tax_amount, 11.00, f_get_digit_decimal_doc_curr(511, A.curr_code), 'RHU')) AS item_amount
FROM tt_in_po_balance_item A
WHERE A.session_id = 'SESI1'
GROUP BY A.session_id, A.flg_tax_amount
)

--> pu_po_balance_invoice
UPDATE pu_po_balance_invoice Z SET item_amount = B.item_amount
FROM tt_in_po_balance_item A
INNER JOIN item_amount B ON B.session_id = A.session_id
WHERE A.session_id = 'SESI1'
AND Z.tenant_id = A.tenant_id
AND Z.ou_id = A.ou_id
AND Z.po_id = A.po_id
AND Z.ref_doc_type_id = A.doc_type_id
AND Z.ref_id = A.inventory_id
AND Z.ref_item_id = A.receive_goods_item_id;

WITH base_amount AS (
SELECT A.session_id, SUM(f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po,
    A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(511, A.curr_code), 'RHU')) AS item_amount
FROM tt_in_po_balance_item A
WHERE A.session_id = 'SESI1'
GROUP BY A.session_id
)
--> pu_po_balance_invoice_tax
UPDATE pu_po_balance_invoice_tax Z SET
    base_amount = B.item_amount,
    tax_amount = f_tax_rounding(A.tenant_id, B.item_amount, A.tax_percentage),
    tax_percentage = A.tax_percentage
FROM tt_in_po_balance_item A
INNER JOIN base_amount B ON B.session_id = A.session_id
WHERE A.session_id = 'SESI1'
AND Z.tenant_id = A.tenant_id
AND Z.ou_id = A.ou_id
AND Z.po_id = A.po_id
AND Z.ref_doc_type_id = A.doc_type_id
AND Z.ref_id = A.inventory_id
AND Z.ref_item_id = A.receive_goods_item_id
AND Z.tax_id = A.tax_id;

--> gl_journal_trx_item
UPDATE gl_journal_trx_item Z SET
    amount = f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(511, A.curr_code), 'RHU')
FROM tt_in_po_balance_item A
WHERE A.session_id = 'SESI1'
AND Z.journal_trx_id  = 725573;

--> gl_journal_trx_mapping
UPDATE gl_journal_trx_mapping Z SET
    amount = f_get_amount_before_tax((A.qty_return * A.qty_po * A.price) / A.qty_int_po, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(511, A.curr_code), 'RHU')
FROM tt_in_po_balance_item A
WHERE A.session_id = 'SESI1'
AND Z.journal_trx_id  = 725573;

DELETE FROM tt_in_po_balance_item WHERE session_id = 'SESI1';

ROLLBACK
END;