Issue KPS-1044

BEGIN;
--sl_so_item(tax_percentage,tax_price,nett_sell_price,tax_amount,nett_item_amount)
CREATE TABLE sl_so_item_20220408 AS (
SELECT *
FROM sl_so_item
WHERE tax_id <> -99
AND so_id IN (4647679,4647817,4647478,4647680,4646602,
                4646997,4646856,4647668,4647302,4647551,
                4645593,4646250,4647552,4645644,4646597,
                4647016,4646598,4647018,4647602,4646876,4646764)
                );

UPDATE sl_so_item A
SET tax_percentage = 11.0,
tax_price =
f_tax_rounding(A.tenant_id, f_get_amount_before_tax((A.gross_sell_price - A.discount_amount),A.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(301, A.curr_code), 'RHU'), 11.0),
nett_sell_price =
f_get_amount_before_tax((A.gross_sell_price - A.discount_amount), A.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(301, A.curr_code), 'RHU'),
tax_amount =
f_tax_rounding(A.tenant_id, f_get_amount_before_tax(A.qty_int*(A.gross_sell_price - A.discount_amount),A.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(301, A.curr_code), 'RHU'), 11.0),
nett_item_amount =
f_get_amount_before_tax(A.qty_int*(A.gross_sell_price - A.discount_amount), A.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(301, A.curr_code), 'RHU')
WHERE A.tax_id <> -99
AND A.so_id IN (4647679,4647817,4647478,4647680,4646602,
                4646997,4646856,4647668,4647302,4647551,
                4645593,4646250,4647552,4645644,4646597,
                4647016,4646598,4647018,4647602,4646876,4646764);

--sl_so_balance_invoice(price_so,item_amount)
CREATE TABLE  sl_so_balance_invoice_20220408 AS (
SELECT *
FROM sl_so_balance_invoice
WHERE so_id IN (4647679,4647817,4647478,4647680,4646602,
                4646997,4646856,4647668,4647302,4647551,
                4645593,4646250,4647552,4645644,4646597,
                4647016,4646598,4647018,4647602,4646876,4646764)
                );

UPDATE sl_so_balance_invoice D
SET price_so = C.nett_sell_price,
item_amount = f_get_amount_before_tax(B.qty_dlv_so * (C.gross_sell_price - C.discount_amount), C.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(311, C.curr_code), 'RHU')
FROM sl_do A, sl_do_item B, sl_so_item C
WHERE A.do_id = B.do_id AND
B.ref_id = C.so_item_id AND
C.tax_id <> -99 AND
C.so_id IN (4647679,4647817,4647478,4647680,4646602,
            4646997,4646856,4647668,4647302,4647551,
            4645593,4646250,4647552,4645644,4646597,
            4647016,4646598,4647018,4647602,4646876,4646764) AND
C.so_id = D.so_id AND
A.do_id = D.ref_id AND
A.doc_type_id = D.ref_doc_type_id AND
B.do_item_id = D.ref_item_id;

--sl_so_balance_invoice_tax(tax_percentage,base_amount,tax_amount)
CREATE TABLE  sl_so_balance_invoice_tax_20220408 AS (
SELECT *
FROM sl_so_balance_invoice_tax
WHERE so_id IN (4647679,4647817,4647478,4647680,4646602,
                4646997,4646856,4647668,4647302,4647551,
                4645593,4646250,4647552,4645644,4646597,
                4647016,4646598,4647018,4647602,4646876,4646764)
                );

UPDATE sl_so_balance_invoice_tax D
SET tax_percentage = 11.0,
base_amount = f_get_amount_before_tax(B.qty_dlv_so * (C.gross_sell_price - C.discount_amount), C.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(311, C.curr_code), 'RHU'),
tax_amount = f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_dlv_so * (C.gross_sell_price - C.discount_amount), C.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(311, C.curr_code), 'RHU'), 11.0)
FROM sl_do A, sl_do_item B, sl_so_item C
WHERE A.do_id = B.do_id AND
B.ref_id = C.so_item_id AND
C.tax_id <> -99 AND
C.so_id IN (4647679,4647817,4647478,4647680,4646602,
            4646997,4646856,4647668,4647302,4647551,
            4645593,4646250,4647552,4645644,4646597,
            4647016,4646598,4647018,4647602,4646876,4646764) AND
C.so_id = D.so_id AND
A.do_id = D.ref_id AND
A.doc_type_id = D.ref_doc_type_id AND
B.do_item_id = D.ref_item_id;

--sl_so_tax(tax_percentage,base_amount,tax_amount)
CREATE TABLE sl_so_tax_20220408 AS (
SELECT *
FROM sl_so_tax
WHERE so_id IN (4647679,4647817,4647478,4647680,4646602,
                4646997,4646856,4647668,4647302,4647551,
                4645593,4646250,4647552,4645644,4646597,
                4647016,4646598,4647018,4647602,4646876,4646764)
                );

WITH temp_update_sl_so_tax AS(
SELECT A.so_id,
SUM(f_get_amount_before_tax((A.qty_so * (A.gross_sell_price - A.discount_amount)), A.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(301, A.curr_code), 'RHU')) AS base_amount,
f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.qty_so * (A.gross_sell_price - A.discount_amount)), A.flg_tax_amount, 11.0,
f_get_digit_decimal_doc_curr(301, A.curr_code), 'RHU')), 11.0) AS tax_amount
FROM sl_so_item A
WHERE A.so_id IN (4647679,4647817,4647478,4647680,4646602,
                    4646997,4646856,4647668,4647302,4647551,
                    4645593,4646250,4647552,4645644,4646597,
                    4647016,4646598,4647018,4647602,4646876,4646764)
GROUP BY A.so_id,A.tenant_id
)
UPDATE sl_so_tax A
SET tax_percentage = 11.0,
base_amount = B.base_amount,
tax_amount = B.tax_amount,
remark = 'PPN 11%'
FROM temp_update_sl_so_tax B
WHERE A.so_id = B.so_id;

--rollback

END;