update_tax_so

Update data SO -  SI

/* A. Update data SO Item */

-- tax_id = 10 (Ppn)
-- Update flg_tax
UPDATE sl_so_item B
    SET flg_tax_amount = 'Y', tax_id = 10, tax_percentage = 10.00
FROM sl_so A
WHERE A.so_id = B.so_id
AND A.doc_no = 'XXXX';

-- get data rounding mode
SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax'); --> vRoundingMode

-- get data rounding scale
SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'rounding.scale.print.total') AS integer); --> vRoundingScale

-- update dpp dan ppn
UPDATE sl_so_item B
    SET tax_price = f_calculate_tax_amount_from_gross_price(B.gross_sell_price, 1, 0, B.flg_tax_amount, B.tax_percentage, vRoundingScale, vRoundingMode),
    net_sell_price = f_calculate_dpp_amount_from_gross_price(B.gross_sell_price, 1, 0, B.flg_tax_amount, B.tax_percentage, vRoundingScale, vRoundingMode)
FROM sl_so A
INNER JOIN temp_sl_so_progress_show_data_20210825 C ON A.so_id = C.so_id
WHERE B.so_item_id = C.so_item_id;

-- update sub total tax + nett sub total
UPDATE sl_so_item B
    SET tax_amount = B.tax_price * B.qty_so,
    nett_item_amount = B.net_sell_price * B.qty_so
FROM sl_so A
INNER JOIN temp_sl_so_progress_show_data_20210825 C ON A.so_id = C.so_id
WHERE B.so_item_id = C.so_item_id;

/* B. Update data SO Tax */

-- get data rounding mode
SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax'); --> 'RHU'

WITH get_data_tax AS (
    SELECT Z.so_id, Z.doc_type_id, SUM(f_get_amount_before_tax(
                (A.qty_so * (A.gross_sell_price - A.discount_amount)), A.flg_tax_amount,
                A.tax_percentage,f_get_digit_decimal_doc_curr(Z.doc_type_id, A.curr_code),
                'RHU')) AS data_base_amount,
        f_tax_rounding(
                Z.tenant_id, SUM(f_get_amount_before_tax(
                    (A.qty_so * (A.gross_sell_price - A.discount_amount)), A.flg_tax_amount,
                    A.tax_percentage,f_get_digit_decimal_doc_curr(Z.doc_type_id, A.curr_code),
                    'RHU')),
                A.tax_percentage) AS data_tax_amount
    -- SELECT Z.doc_no, Z.doc_date,
    FROM sl_so Z
    INNER JOIN sl_so_item A ON A.so_id = Z.so_id
    INNER JOIN m_tax B ON B.tax_id = A.tax_id
    WHERE Z.doc_no = 'XXXX'
    GROUP BY Z.so_id, Z.doc_type_id, Z.tenant_id, A.tax_percentage
)
UPDATE sl_so_tax X
        SET base_amount = A.data_base_amount,
        tax_amount = data_tax_amount
    FROM get_data_tax A
    INNER JOIN temp_sl_so_progress_show_data_20210825 C ON A.so_id = C.so_id
    WHERE A.so_id = X.so_id
    AND A.tenant_id = X.tenant_id;

/* C. Update data DO */

-- update sl_so_balance_invoice
    -- vDeliveryOrderDocTypeId --> 311
    -- vRoundingModeNonTax --> 'RHU'
    UPDATE sl_so_balance_invoice X
        SET price_so = C.item_amount,
            item_amount = f_get_amount_before_tax_and_disc(
            B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount,
            C.flg_tax_amount, C.tax_percentage,
            f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
            vRoundingModeNonTax),
        regular_disc_amount = C.discount_amount * B.qty_dlv_so

    SELECT A.*
    FROM temp_sl_so_progress_show_data_20210825 A
    INNER JOIN sl_do_item B ON A.do_id = B.do_id AND A.so_item_id = B.ref_id
    INNER JOIN sl_so_item C ON A.so_item_id = C.so_item_id
    -- WHERE A.doc_no = 'MEL-SO21.08.0240';
    WHERE X.so_id = A.so_id AND X.ref_id = A.do_id
    AND X.ref_doc_type_id = vDeliveryOrderDocTypeId
    AND X.ref_item_id = B.do_item_id;

/* -- Tidak jadi dipakai, karena item SO yang tidak ada tax, tidak akan ada data di sl_so_balance_invoice_tax
-- update sl_so_balance_invoice_tax
    -- vDeliveryOrderDocTypeId --> 311
    -- vRoundingModeNonTax --> 'RHU'
    UPDATE sl_so_balance_invoice_tax X
        SET tax_id = C.tax_id,
            flg_amount = D.flg_amount,
            tax_percentage = C.tax_percentage,
            base_amount = f_get_amount_before_tax_and_disc(
                B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount,
                C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
                vRoundingModeNonTax),
            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, C.tax_percentage,
                    f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
                    vRoundingModeNonTax),
                C.tax_percentage)
    -- SELECT A.*
    FROM temp_sl_so_progress_show_data_20210825 A
    INNER JOIN sl_do_item B ON A.do_id = B.do_id AND A.so_item_id = B.ref_id
    INNER JOIN sl_so_item C ON A.so_item_id = C.so_item_id
    INNER JOIN m_tax D ON C.tax_id = D.tax_id
    -- WHERE A.doc_no = 'MEL-SO21.08.0240';
    WHERE X.so_id = A.so_id AND X.ref_id = A.do_id
    AND X.ref_doc_type_id = vDeliveryOrderDocTypeId
    AND X.ref_item_id = B.do_item_id;
*/

-- insert data ke sl_so_balance_invoice_tax
    INSERT INTO sl_so_balance_invoice_tax
        (tenant_id, ou_id, partner_id, so_id,
        ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
        tax_percentage, curr_code,
        base_amount,
        tax_amount,
        flg_invoice, invoice_id,
        "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, E.partner_bill_to_id, A.ref_id,
        A.doc_type_id, A.do_id, B.do_item_id, C.tax_id, D.flg_amount,
        C.tax_percentage, C.curr_code,
        f_get_amount_before_tax_and_disc(
            B.qty_dlv_so * C.gross_sell_price, B.qty_dlv_so * C.discount_amount,
            C.flg_tax_amount, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
            vRoundingModeNonTax),
        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, C.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, C.curr_code),
                vRoundingModeNonTax),
            C.tax_percentage),
        vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_do A
    INNER JOIN sl_do_item B ON A.do_id = B.do_id
    INNER JOIN sl_so_item C ON B.ref_id = C.so_item_id
    INNER JOIN m_tax D ON C.tax_id = D.tax_id
    INNER JOIN sl_so E ON C.so_id = E.so_id
    WHERE EXISTS (
        SELECT 1
        FROM temp_sl_so_progress_show_data_20210825 X
        WHERE X.so_id = E.so_id AND C.so_item_id = X.so_item_id
        AND X.do_id = A.do_id
    );

/* D. Update data Sales Invoice */

-- update sl_invoice_item
    UPDATE sl_invoice_item X
        SET ref_item_amount = A.item_amount
    -- SELECT A.*
    FROM sl_so_balance_invoice A
    INNER JOIN sl_do B ON A.ref_id = B.do_id AND B.doc_type_id = A.ref_doc_type_id AND A.so_id = B.ref_id
    INNER JOIN sl_do_item C ON C.do_item_id = A.ref_item_id AND B.do_id = C.do_id
    INNER JOIN temp_sl_so_progress_show_data_20210825 D ON D.do_id = B.do_id AND D.so_item_id = C.ref_id
    -- WHERE D.doc_no = 'MEL-SO21.08.0240';
    WHERE X.invoice_id = D.invoice_id AND X.ref_id = B.do_id AND A.ref_item_id = C.do_item_id;


-- update sl_invoice_tax
    UPDATE sl_invoice_tax X
        SET base_amount = SUM(A.base_amount),
        tax_amount = SUM(A.tax_amount),
        gov_tax_amount = SUM(A.tax_amount),
        total_tax_disc_amount = ((SUM(C.regular_disc_amount)+SUM(C.promo_disc_amount))*A.tax_percentage/100)
    FROM sl_so_balance_invoice_tax A
    INNER JOIN sl_do_item B ON B.tenant_id = A.tenant_id AND B.do_item_id = A.ref_item_id
    INNER JOIN sl_so_balance_invoice C ON A.so_id = C.so_id AND A.ref_doc_type_id = C.ref_doc_type_id
        AND A.ref_id = C.ref_id AND A.ref_item_id= C.ref_item_id
    WHERE EXISTS (
        SELECT 1
        FROM temp_sl_so_progress_show_data_20210825 Z
        WHERE B.do_id = Z.do_id AND B.ref_id = Z.so_item_id
        AND X.invoice_id = Z.invoice_id AND A.so_id = Z.so_id
    )
    GROUP BY A.tax_percentage;

-- update sl_invoice
    WITH tax_amount AS (
        SELECT A.ref_id AS so_id, SUM(B.tax_amount) AS total_tax_amount
        FROM sl_do A
        INNER JOIN sl_so_balance_invoice_tax B ON A.do_id = B.ref_id
            AND A.doc_type_id = B.ref_doc_type_id   AND A.ref_id = B.so_id
        WHERE EXISTS (
            SELECT 1
            FROM temp_sl_so_progress_show_data_20210825 X
            WHERE X.so_id = A.ref_id
        )
        GROUP BY A.ref_id
    ), data_amount AS (
        SELECT A.ref_id AS so_id, ((SUM(B.item_amount)) - (SUM(B.regular_disc_amount)) - (SUM(B.promo_disc_amount))) AS total_amount,
        SUM(B.regular_disc_amount) AS total_reg_disc_amount,
        SUM(B.promo_disc_amount) AS total_promo_disc_amount,
        ((SUM(B.item_amount)) - (SUM(B.regular_disc_amount)) - (SUM(B.promo_disc_amount))) AS gross_amount_exclude_tax
        FROM sl_do A
        INNER JOIN sl_so_balance_invoice B ON A.do_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id AND A.ref_id = B.so_id
        WHERE EXISTS (
            SELECT 1
            FROM temp_sl_so_progress_show_data_20210825 X
            WHERE X.so_id = A.ref_id
        )
        GROUP BY A.ref_id
    )
    UPDATE sl_invoice X
        SET gross_amount = B.gross_amount_exclude_tax + A.total_tax_amount,
        tax_amount = C.total_tax_amount,
        total_amount = B.total_amount,
        discount_amount = B.total_reg_disc_amount + B.total_promo_disc_amount
    FROM temp_sl_so_progress_show_data_20210825 A
    INNER JOIN data_amount B ON A.so_id = B.so_id
    INNER JOIN tax_amount C ON A.so_id = C.so_id
    WHERE X.ref_id = A.so_id AND X.ref_doc_type_id = 301
    AND X.invoice_id = A.invoice_id

-- update sl_invoice_cost
/* tidak perlu update, karena semua dokumen invoice yang berkaitan tidak ada data invoice cost. Bisa cek di:
*   SELECT A.doc_no, A.invoice_doc_no, B.*
*   FROM temp_sl_so_progress_show_data_20210825 A
*   INNER JOIN sl_invoice_cost B ON A.invoice_id = B.invoice_id;
*
*/

-- update fi_invoice_ar_balance

    UPDATE fi_invoice_ar_balance X
        SET amount = A.total_amount + COALESCE(B.gov_tax_amount, 0)
    FROM sl_invoice A
    LEFT OUTER JOIN sl_invoice_tax B ON A.invoice_id = B.invoice_id
    WHERE EXISTS (
        SELECT 1
        FROM temp_sl_so_progress_show_data_20210825 Z
        WHERE Z.invoice_id = A.invoice_id
        AND A.ref_id = Z.so_id
    ) AND A.invoice_id = X.invoice_ar_id AND A.doc_type_id = X.doc_type_id;


/* E. Update data jurnal Sales Invoice */
/*
* update gl_journal_trx untuk yang tipenya `SALES_INCOOME` dan `VAT_OUT`
*/

-- update gl_journal_trx_item untuk item yang tidak ada pajak

    UPDATE gl_journal_trx_item X
        SET amount = C.ref_item_amount,
        update_datetime = vDateTime,
        update_user_id = vUserId
    --SELECT A.doc_no, C.*
    FROM gl_journal_trx A
    INNER JOIN sl_invoice B ON A.doc_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
    INNER JOIN sl_invoice_item C ON C.invoice_id = B.invoice_id
    WHERE EXISTS (
        SELECT 1
        FROM temp_sl_so_progress_show_data_20210825 D
        INNER JOIN sl_do_item E ON D.do_id = E.do_id AND E.ref_id = D.so_item_id
        WHERE B.invoice_id = D.invoice_id AND C.ref_id = E.do_item_id
    ) AND X.journal_trx_id = A.journal_trx_id AND X.ref_id = C.invoice_item_id
    AND X.journal_desc = 'SALES_INCOME';

-- update gl_journal_trx_item untuk data pajak (vat out)
    UPDATE gl_journal_trx_item X
        SET amount = C.gov_tax_amount,
        update_datetime = vDateTime,
        update_user_id = vUserId
    --SELECT B.doc_no, COALESCE(C.invoice_tax_id, -99) AS invoice_tax_id, A.*
    FROM gl_journal_trx A
    INNER JOIN sl_invoice B ON A.doc_id = B.invoice_id AND A.doc_type_id = B.doc_type_id
    INNER JOIN sl_invoice_tax C ON C.invoice_id = B.invoice_id
    INNER JOIN temp_sl_so_progress_show_data_20210825 D ON B.invoice_id = D.invoice_id AND B.ref_id = D.so_id
    WHERE X.journal_trx_id = A.journal_trx_id AND X.ref_id = C.invoice_tax_id
    AND X.journal_desc = 'VAT_OUT';

-- update fi_vat_out_balance_efaktur_gunggung
    UPDATE fi_vat_out_balance_efaktur_gunggung X
        SET invoice_dpp_amount = B.base_amount,
        invoice_tax_amount = B.tax_amount,
        invoice_gov_tax_amount = B.gov_tax_amount
    FROM sl_invoice A, sl_invoice_tax B
    WHERE A.invoice_id = B.invoice_id
    AND X.invoice_doc_type_id = A.doc_type_id
    AND X.invoice_id = A.invoice_id AND X.invoice_tax_id = B.invoice_tax_id
    AND EXISTS (
        SELECT 1
        FROM temp_sl_so_progress_show_data_20210825 C
        WHERE C.invoice_id = A.invoice_id
        AND A.ref_id = C.so_id
    );


-- hapus & generate ulang vat out efaktur

/* tidak jadi dipakai, ada func yang bisa auto backup + generate ulang
*   WITH data_invoice_ar AS (
*       SELECT A.invoice_ar_id, A.doc_type_id, A.ou_id, A.doc_no
*       -- SELECT A.*
*       FROM fi_invoice_ar_balance A --ON A.invoice_ar_id = X.doc_id AND X.doc_type_id = A.doc_type_id
*       INNER JOIN sl_invoice B ON B.doc_type_id = A.doc_type_id AND B.invoice_id = A.invoice_ar_id
*       WHERE EXISTS (
*           SELECT 1
*           FROM temp_sl_so_progress_show_data_20210825 C
*           WHERE C.invoice_id = B.invoice_id
*       )
*   ) DELETE FROM fi_vat_out_for_efaktur A
*   WHERE EXISTS (
*       SELECT 1
*       FROM data_invoice_ar B
*       WHERE A.doc_id = B.invoice_ar_id AND A.doc_type_id = B.doc_type_id
*   );
*/

    SELECT fi_check_insert_vat_out_for_efaktur(A.tenant_id, 'regenerate_vat_out', A.invoice_ar_balance_id, vUserId, vDatetime)
    -- SELECT A.*
    FROM fi_invoice_ar_balance A --ON A.invoice_ar_id = X.doc_id AND X.doc_type_id = A.doc_type_id
    INNER JOIN sl_invoice B ON B.doc_type_id = A.doc_type_id AND B.invoice_id = A.invoice_ar_id
    WHERE EXISTS (
        SELECT 1
        FROM temp_sl_so_progress_show_data_20210825 C
        WHERE C.invoice_id = B.invoice_id
    );