script update journal pos

-- 1.  query untuk i_trx_pos_item.tax_percentage ubah menjadi 10,00 & update nilai nett_amount_item
UPDATE i_trx_pos_item SET tax_percentage = 10.00, nett_amount_item = ROUND((gross_sell_price * qty) / 1.1, 2);

-- 2. update nilai tax_amount di i_trx_pos_item
UPDATE i_trx_pos_item SET tax_amount = (gross_sell_price * qty) - nett_amount_item;

-- 3. hapus, lalu generate ulang i_trx_pos_tax
WITH data_tax AS (
    SELECT process_no, tenant_id, trx_pos_id, flg_tax_amount,
        tax_percentage, SUM(nett_amount_item) AS base_amount , SUM(tax_amount) AS tax_amount,
        '' AS remark, tax_id
    FROM i_trx_pos_item
    GROUP BY process_no, tenant_id, trx_pos_id, flg_tax_amount, tax_percentage, tax_id
    UNION ALL
    -- kalau ada POS yang isinya cuma barang nempil
    SELECT process_no, tenant_id, trx_pos_id, 'Y',
        0.00, 0, 0,
        '', -99
    FROM i_trx_pos_item_nempil_barang A
    WHERE NOT EXISTS (
        SELECT 1 FROM i_trx_pos_item X WHERE A.process_no = X.process_no
    )
)
INSERT INTO i_trx_pos_tax(process_no, tenant_id, trx_pos_id, flg_amount,
    tax_percentage, base_amount, tax_amount,
    remark, tax_id,
    version, create_datetime, create_user_id,
    update_datetime, update_user_id)
SELECT A.process_no, A.tenant_id, A.trx_pos_id, A.flg_tax_amount,
    A.tax_percentage, SUM(base_amount), SUM(tax_amount),
    A.remark, A.tax_id,
    0 AS version, TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS create_datetime, -1 AS create_user_id,
    TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING AS update_datetime, -1 AS update_user_id
FROM data_tax A
    GROUP BY process_no, tenant_id, trx_pos_id, flg_tax_amount, tax_percentage, remark, tax_id;

-- 4. regenerate journal pos
SELECT f_regenerate_journal_trx_pos_shop(
    'REGENERATE-JOURNALPOS.SHOP', tenant_id, ou_id, trx_pos_id, process_no, -3,
    TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING)
FROM i_trx_pos A;

-- 5. regenerate journal void pos
SELECT f_regenerate_journal_trx_void_pos_shop(
    'REGENERATE-JOURNAL-VOIDPOS.SHOP', tenant_id, ou_id, trx_pos_id, process_no, -3,
    TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING)
FROM i_trx_pos A;