Update Data Penjualan

-- Update data dokumen SO - DO - 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
INNER JOIN temp_sl_so_progress_show_data_20220218 C ON A.so_id = C.so_id
WHERE A.so_id = B.so_id AND B.so_item_id = C.so_item_id;

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

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

-- 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, 2, 'RHU'),
    nett_sell_price = f_calculate_dpp_amount_from_gross_price(B.gross_sell_price, 1, 0, B.flg_tax_amount, B.tax_percentage, 2, 'RHU')
FROM sl_so A
INNER JOIN temp_sl_so_progress_show_data_20220218 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.nett_sell_price * B.qty_so
FROM sl_so A
INNER JOIN temp_sl_so_progress_show_data_20220218 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.tenant_id, 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
    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_20220218 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
    -- 311 --> 311
    -- 'RHU' --> 'RHU'
    UPDATE sl_so_balance_invoice X
        SET price_so = C.nett_sell_price,
            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(311, C.curr_code),
            'RHU'),
        regular_disc_amount = C.discount_amount * B.qty_dlv_so

    -- SELECT A.*
    FROM temp_sl_so_progress_show_data_20220218 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 = 311
    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
    -- 311 --> 311
    -- 'RHU' --> '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(311, C.curr_code),
                'RHU'),
            tax_amount = f_tax_rounding(
                10, 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(311, C.curr_code),
                    'RHU'),
                C.tax_percentage)
    -- SELECT A.*
    FROM temp_sl_so_progress_show_data_20220218 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 = 311
    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(311, C.curr_code),
            'RHU'),
        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(311, C.curr_code),
                'RHU'),
            C.tax_percentage),
        'Y', -99,
        0, '20220218101101', -1, '20220218101101', -1
    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_20220218 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_20220218 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
    WITH data_tax AS (
        SELECT A.invoice_id, SUM(D.base_amount) AS base_amount, SUM(D.tax_amount) AS tax_amount,
            ((SUM(C.regular_disc_amount)+SUM(C.promo_disc_amount))*D.tax_percentage/100) AS total_tax_disc_amount
        -- SELECT *
        FROM temp_sl_so_progress_show_data_20220218 A
        INNER JOIN sl_do_item B ON A.do_id = B.do_id --AND B.ref_id = A.so_item_id
        INNER JOIN sl_so_balance_invoice C ON A.so_id = C.so_id AND C.ref_id = B.do_id AND B.do_item_id = C.ref_item_id
        INNER JOIN sl_so_balance_invoice_tax D ON C.so_id = D.so_id AND C.ref_id = D.ref_id AND C.ref_item_id= D.ref_item_id
        WHERE EXISTS (
            SELECT 1
            FROM sl_invoice_tax Z
            WHERE A.invoice_id = Z.invoice_id
        )
        GROUP BY A.invoice_id, D.tax_percentage
    )
    UPDATE sl_invoice_tax X
            SET base_amount = A.base_amount,
            tax_amount = A.tax_amount,
            gov_tax_amount = A.tax_amount,
            total_tax_disc_amount = A.total_tax_disc_amount
    FROM data_tax A
    WHERE A.invoice_id = X.invoice_id;

-- insert sl_invoice_tax yang belum ada
    INSERT INTO sl_invoice_tax(
                invoice_tax_id, tenant_id, invoice_id,
                tax_id, flg_amount, tax_percentage,
                base_amount, tax_amount, tax_no,
                tax_date, tax_curr_code, gov_tax_amount,
                remark, version, create_datetime, create_user_id, update_datetime, update_user_id,
                advance_amount, total_tax_disc_amount, flg_gunggung)
    SELECT NEXTVAL('sl_invoice_tax_seq'), A.tenant_id , C.invoice_id,
            A.tax_id, A.flg_amount, A.tax_percentage,
            SUM(A.base_amount), SUM(A.tax_amount), '000.000-00.00000000',
            --CASE WHEN vFlgPkp = vNo THEN vTaxNo ELSE vEmptyString END AS tax_no,
            C.do_doc_date, A.curr_code, SUM(A.tax_amount),
            '', 0,
            TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), 29, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), 29,
            0, 0, 'Y'
    -- SELECT A.*
    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 temp_sl_so_progress_show_data_20220218 C ON C.do_id = B.do_id AND A.so_id = C.so_id
    WHERE NOT EXISTS (
        SELECT 1
        FROM sl_invoice_tax X WHERE C.invoice_id = X.invoice_id )
    GROUP BY A.tenant_id, C.invoice_id, C.do_doc_date, A.tax_id, A.flg_amount, A.tax_percentage, A.curr_code;


-- 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_20220218 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_20220218 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 + C.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_20220218 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_20220218 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_20220218 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 = TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'),
        update_user_id = 29
    --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_20220218 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 = TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'),
        update_user_id = 29
    --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_20220218 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';

-- insert data yang tidak ada pajak
    INSERT INTO gl_journal_trx_item
            (tenant_id, journal_trx_id, line_no,
            ref_doc_type_id, ref_id,
            partner_id, product_id, cashbank_id, ou_rc_id,
            segmen_id, sign_journal, flg_source_coa, activity_gl_id,
            coa_id, curr_code, qty, uom_id,
            amount, journal_date, type_rate,
            numerator_rate, denominator_rate, journal_desc, remark,
            "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, 3,
        C.doc_type_id, D.invoice_tax_id,
        C.partner_id, -99, -99, -99,
        -99, 'C', 'TAX', -99,
        E.create_coa_id, E.tax_curr_code, 0, -99,
        D.gov_tax_amount, C.doc_date, 'COM',
        1, 1, 'VAT_OUT', D.remark,
        0, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), 29, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), 29
    --SELECT A.*
    FROM gl_journal_trx A
    INNER JOIN temp_sl_so_progress_show_data_20220218 B ON A.doc_id = B.invoice_id AND A.doc_type_id = 321
    INNER JOIN sl_invoice C ON B.invoice_id = C.invoice_id
    INNER JOIN sl_invoice_tax D ON D.invoice_id = C.invoice_id
    INNER JOIN m_tax E ON D.tax_id = E.tax_id
    WHERE NOT EXISTS (
        SELECT 1
        FROM gl_journal_trx_item X
        WHERE X.journal_trx_id = A.journal_trx_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_20220218 C
        WHERE C.invoice_id = A.invoice_id
        AND A.ref_id = C.so_id
    );

-- insert data yang belum ada di fi_vat_out_balance_efaktur_gunggung
    INSERT INTO fi_vat_out_balance_efaktur_gunggung(
        tenant_id, ou_id, invoice_id,
        invoice_doc_type_id, invoice_tax_id, tax_code, tax_percentage,
        doc_no, doc_date, invoice_dpp_amount,
        invoice_tax_amount, invoice_gov_tax_amount,
        flg_gunggung, flg_realization, realization_doc_type_id,
        realization_id, realization_tax_no, realization_tax_date,
        version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.invoice_id,
        A.doc_type_id, B.invoice_tax_id, C.tax_code, B.tax_percentage,
        A.doc_no, A.doc_date, B.base_amount,
        B.tax_amount, B.gov_tax_amount,
        B.flg_gunggung, 'N', -99,
        -99, '', '',
        0, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), 29, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'), 29
    FROM sl_invoice A
    INNER JOIN sl_invoice_tax B ON A.invoice_id = B.invoice_id
    INNER JOIN m_tax C ON B.tax_id = C.tax_id
    INNER JOIN temp_sl_so_progress_show_data_20220218 D ON A.invoice_id = D.invoice_id
    WHERE NOT EXISTS (
        SELECT 1
        FROM fi_vat_out_balance_efaktur_gunggung X
        WHERE X.invoice_id = A.invoice_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_20220218 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, 29, TO_CHAR(CURRENT_TIMESTAMP, 'YYYYMMDDHH24MISS'))
    -- 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
    INNER JOIN sl_invoice_tax C ON B.invoice_id = C.invoice_id
    WHERE EXISTS (
        SELECT 1
        FROM temp_sl_so_progress_show_data_20220218 C
        WHERE C.invoice_id = B.invoice_id
    ) AND C.flg_gunggung = 'N';