Function Manage SO Item

CREATE OR REPLACE FUNCTION sl_submit_modify_item_so(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId              bigint;
    vSoId                   bigint;
    vManageSoId             bigint;
    vUserId                 bigint;
    vEmptyId                bigint;
    vUnfinishedItem         bigint;
    vManageSoItemDocTypeId  bigint;
    vDeliveryOrderDocTypeId bigint;
    vDoReceiptDocTypeId     bigint;
    vNol                    bigint;
    vSoStatusDoc            character varying(5);
    vRoundingModeNonTax     character varying(5);
    vDatetime               character varying(14);
    vStatusRelease          character varying(1);
    vStatusCancel           character varying(1);
    vStatusFinal            character varying(1);
    vStatusDraft            character varying(1);
    vFlagNo                 character varying(1);
    vFlagYes                character varying(1);
    vFlagInvoice            character varying(1);
BEGIN

    vStatusRelease  := 'R';
    vStatusCancel   := 'C';
    vFlagNo         := 'N';
    vFlagYes        := 'Y';
    vEmptyId        := -99;
    vManageSoItemDocTypeId := 305;
    vUnfinishedItem := 0;
    vNol            := 0;
    vStatusDraft    := 'D';
    vStatusFinal    := 'F';
    vDeliveryOrderDocTypeId := 311;
    vDoReceiptDocTypeId := 525;
    vFlagInvoice := 'N';

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;

    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'sl_submit_modify_item_so' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vManageSoId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'modItemSoId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    DELETE FROM tt_sl_so_tax_for_modify_so WHERE session_id = pSessionId;

    SELECT A.so_id INTO vSoId FROM sl_manage_so A WHERE A.manage_so_id = vManageSoId;
    SELECT A.so_status_doc INTO vSoStatusDoc FROM sl_manage_so A WHERE A.manage_so_id = vManageSoId;

    UPDATE sl_so SET update_datetime = vDatetime, update_user_id = vUserId, version = sl_so.version + 1, status_doc = vSoStatusDoc
    WHERE sl_so.so_id = vSoId;

    -- update so item untuk item lama
    UPDATE sl_so_item
    SET update_datetime = vDatetime,
        update_user_id = vUserId,
        version = sl_so_item.version + 1,
        gross_sell_price = A.gross_sell_price,
        flg_tax_amount = A.flg_tax_amount,
        tax_id = A.tax_id,
        tax_percentage = A.tax_percentage,
        tax_price = A.tax_price,
        nett_sell_price = A.nett_sell_price,
        qty_so = sl_so_item.qty_so + A.qty_so,
        qty_int = sl_so_item.qty_int + A.qty_int,
        nett_item_amount = A.nett_item_amount,
        tax_amount = A.tax_amount,
        flg_disc = A.flg_disc,
        discount_amount = A.discount_amount,
        discount_percentage = A.discount_percentage
    FROM sl_manage_so_item A, sl_manage_so B
    WHERE A.manage_so_id = vManageSoId
    AND A.manage_so_id = B.manage_so_id
    AND sl_so_item.so_item_id = A.so_item_id
    AND A.flg_new_item = vFlagNo;

    -- insert so item untuk item baru (ref_doc_type_id dan ref_id diisi referensi dulu supaya bisa untuk join waktu insert sl_so_balance_item)
    INSERT INTO sl_so_item(
            tenant_id, so_id, line_no, ref_doc_type_id, ref_id,
            partner_ship_to_id, partner_ship_address_id, product_id, curr_code, gross_sell_price,
            flg_tax_amount, tax_id, tax_percentage, tax_price, promo_code, nett_sell_price,
            qty_so, so_uom_id, qty_int, base_uom_id, discount_percentage,
            discount_amount, nett_item_amount, tax_amount,
            eta, tolerance_dlv_qty, flg_disc,
            remark, version, create_datetime, create_user_id, update_datetime,
            update_user_id, eta_day)
    SELECT pTenantId, B.so_id, A.line_no, vManageSoItemDocTypeId, A.manage_so_item_id,
           C.partner_ship_to_id, C.partner_ship_address_id, A.product_id, A.curr_code, A.gross_sell_price,
           A.flg_tax_amount, A.tax_id, A.tax_percentage, A.tax_price, A.promo_code, A.nett_sell_price,
           A.qty_so, A.so_uom_id, A.qty_int, A.base_uom_id, A.discount_percentage,
           A.discount_amount, A.nett_item_amount, A.tax_amount,
           A.eta, A.tolerance_dlv_qty, A.flg_disc,
           A.remark, 0, vDatetime, vUserId, vDatetime,
           vUserId, A.eta_day
    FROM sl_manage_so_item A, sl_manage_so B, sl_so C
    WHERE A.manage_so_id = vManageSoId AND
        A.manage_so_id = B.manage_so_id AND
        B.so_id = C.so_id AND
        A.flg_new_item = vFlagYes;

    INSERT INTO sl_so_item_purchasing(
        so_item_id, partner_id, tenant_id, curr_code,
        flg_tax_amount, price, flg_ready_stock, indent_days, remark,
        version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT C.so_item_id, A.partner_id, pTenantId, A.curr_code,
           A.flg_tax_amount, A.price, A.flg_ready_stock, A.indent_days, A.remark,
           0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_manage_so_item_purchasing A
    INNER JOIN sl_manage_so B ON B.manage_so_id = A.manage_so_id
    INNER JOIN sl_so_item C ON A.manage_so_item_id = C.ref_id AND C.ref_doc_type_id = vManageSoItemDocTypeId
    INNER JOIN sl_manage_so_item D ON A.manage_so_item_id = D.manage_so_item_id
    WHERE B.manage_so_id = vManageSoId AND
          D.flg_new_item = vFlagYes;



    INSERT INTO sl_so_activity_wo(
        so_item_id, line_no, partner_ship_cp_id,
        flg_activity, group_activity, remark, tenant_id,
        version, create_datetime, create_user_id,
        update_datetime, update_user_id)
    SELECT C.so_item_id, A.line_no, A.partner_ship_cp_id,
           A.flg_activity, A.group_activity, A.remark, pTenantId,
           0, vDatetime, vUserId,
           vDatetime, vUserId
    FROM sl_manage_so_item_activity_wo A
    INNER JOIN sl_manage_so B ON B.manage_so_id = A.manage_so_id
    INNER JOIN sl_so_item C ON A.manage_so_item_id = C.ref_id AND C.ref_doc_type_id = vManageSoItemDocTypeId
    INNER JOIN sl_manage_so_item D ON A.manage_so_item_id = D.manage_so_item_id
    WHERE B.manage_so_id = vManageSoId AND
          D.flg_new_item = vFlagYes;

    -- update so balance item untuk item lama
    UPDATE sl_so_balance_item
    SET update_datetime = vDatetime,
        update_user_id = vUserId,
        version = sl_so_balance_item.version + 1,
        status_item = vStatusRelease,
        qty_add = sl_so_balance_item.qty_add + A.qty_so,
        qty_add_int = sl_so_balance_item.qty_add_int + A.qty_int
    FROM sl_manage_so_item A, sl_manage_so B
    WHERE A.manage_so_id = vManageSoId
    AND A.manage_so_id = B.manage_so_id
    AND sl_so_balance_item.so_item_id = A.so_item_id
    AND A.flg_new_item = vFlagNo;


    -- Update status_item pada sl_so_balance_item menjadi F, apabila tidak ada lagi o/s qty
    UPDATE sl_so_balance_item
    SET status_item = vStatusFinal
    FROM sl_manage_so_item A, sl_manage_so B
    WHERE A.manage_so_id = vManageSoId
    AND A.manage_so_id = B.manage_so_id
    AND sl_so_balance_item.so_item_id = A.so_item_id
    AND A.flg_new_item = vFlagNo
    AND sl_so_balance_item.qty_so - sl_so_balance_item.qty_cancel + sl_so_balance_item.qty_add - sl_so_balance_item.qty_dlv <= 0;

    -- insert so balance item untuk item baru
    INSERT INTO sl_so_balance_item
    (so_item_id, tenant_id, ou_id, qty_so, qty_dlv, qty_return, qty_cancel, qty_add, so_uom_id,
     qty_so_int, qty_dlv_int, qty_return_int, qty_cancel_int, qty_add_int, base_uom_id,
     tolerance_dlv_qty, status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.so_item_id, A.tenant_id, B.ou_id, A.qty_so, 0, 0, 0, 0, A.so_uom_id,
            A.qty_int, 0, 0, 0, 0, A.base_uom_id,
            A.tolerance_dlv_qty, vStatusRelease, 0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_so_item A, sl_so B, sl_manage_so_item C
    WHERE A.so_id = vSoId AND
        A.so_id = B.so_id AND
        C.manage_so_item_id = A.ref_id AND
        A.ref_doc_type_id = vManageSoItemDocTypeId AND
        C.flg_new_item = vFlagYes;


    -- WTC, 20141227, insert dan update juga ke table saldo qty SO terhadap PO, yang akan digunakan/diupdate saat input PO dari SO
    -- TODO: insert to sl_log_so_po_balance_item
    -- Update status menjadi sama dengan status sl_so_balance_item, untuk item-item lama
    UPDATE sl_so_po_balance_item A
    SET update_datetime = vDatetime, update_user_id = vUserId, version = A.version + 1, status_item = D.status_item
    FROM sl_manage_so_item B, sl_manage_so C, sl_so_balance_item D
    WHERE A.so_item_id = B.so_item_id
        AND B.manage_so_id = C.manage_so_id
        AND A.so_item_id = D.so_item_id
        AND B.manage_so_id = vManageSoId
        AND B.flg_new_item = vFlagNo;

    -- Update status menjadi F, untuk item-item lama yang tidak memiliki o/s qty PO terhadap SO
    UPDATE sl_so_po_balance_item A
    SET status_item = vStatusFinal
    FROM sl_manage_so_item B, sl_manage_so C, sl_so_balance_item D
    WHERE A.so_item_id = B.so_item_id
        AND B.manage_so_id = C.manage_so_id
        AND A.so_item_id = D.so_item_id
        AND B.manage_so_id = vManageSoId
        AND B.flg_new_item = vFlagNo
        AND A.status_item <> vStatusFinal
        AND (D.qty_so_int - D.qty_cancel_int + D.qty_add_int) <= (A.qty_po_int - A.qty_po_int_cancel + A.qty_po_int_add);

    -- insert so sl_so_po_balance_item untuk item baru
    INSERT INTO sl_so_po_balance_item
    (so_item_id, tenant_id, ou_id, so_id, qty_po_int, qty_po_int_return, qty_po_int_cancel, qty_po_int_add, base_uom_id,
     status_item, version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.so_item_id, A.tenant_id, B.ou_id, B.so_id, 0, 0, 0, 0, A.base_uom_id,
            vStatusRelease, 0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_so_item A, sl_so B, sl_manage_so_item C
    WHERE A.so_id = vSoId AND
        A.so_id = B.so_id AND
        C.manage_so_item_id = A.ref_id AND
        A.ref_doc_type_id = vManageSoItemDocTypeId AND
        C.flg_new_item = vFlagYes;

    -- update nilai sl_manage_so_item untuk so_item_id (untuk item baru)
    UPDATE sl_manage_so_item
    SET so_item_id = A.so_item_id
    FROM sl_so_item A
    INNER JOIN sl_so B ON A.so_id = B.so_id
    WHERE sl_manage_so_item.manage_so_item_id = A.ref_id AND
          A.ref_doc_type_id = vManageSoItemDocTypeId AND
          A.so_id = vSoId AND
          sl_manage_so_item.flg_new_item = vFlagYes;

    -- kembalikan nilai ref_doc_type_id dan ref_id sesuai dengan aslinya dari sl_manage_so_item
    UPDATE sl_so_item Z SET ref_doc_type_id = A.ref_doc_type_id, ref_id = A.ref_item_id
    FROM sl_manage_so_item A, sl_manage_so B
    WHERE A.manage_so_id = vManageSoId AND
        A.manage_so_id = B.manage_so_id AND
        A.flg_new_item = vFlagYes AND
        Z.ref_id = A.manage_so_item_id AND
        Z.ref_doc_type_id = B.doc_type_id;

    SELECT COUNT(1) INTO vUnfinishedItem
    FROM sl_so_balance_item A, sl_so_item B
    WHERE A.so_item_id = B.so_item_id AND
        B.so_id = vSoId AND
        A.status_item = vStatusRelease;

    IF vUnfinishedItem = 0 THEN
        UPDATE sl_so SET status_doc = vStatusFinal, update_datetime = vDatetime, update_user_id = vUserId
        WHERE so_id = vSoId;
    END IF;

    INSERT INTO sl_log_so_balance_item
    (tenant_id, so_id, so_item_id, ref_doc_type_id, ref_id, ref_item_id,
     qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
    version, create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.so_id, B.so_item_id, A.doc_type_id, A.manage_so_id, B.manage_so_item_id,
        B.qty_so, B.so_uom_id, B.qty_int, B.base_uom_id, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM sl_manage_so A, sl_manage_so_item B
    WHERE A.manage_so_id = vManageSoId AND
        A.manage_so_id = B.manage_so_id;

    -- update tax di so_item
    INSERT INTO tt_sl_so_tax_for_modify_so
    (session_id, tenant_id, so_id, tax_id, flg_amount,
    tax_percentage, base_amount, tax_amount, remark)
    SELECT pSessionId, A.tenant_id, A.so_id, A.tax_id, B.flg_amount,
        A.tax_percentage, SUM(f_get_amount_before_tax((A.gross_sell_price - A.discount_amount) * A.qty_so, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vManageSoItemDocTypeId, A.curr_code), vRoundingModeNonTax)),
        f_tax_rounding(A.tenant_id, SUM(f_get_amount_before_tax((A.gross_sell_price - A.discount_amount) * A.qty_so, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vManageSoItemDocTypeId, A.curr_code), vRoundingModeNonTax)), A.tax_percentage), B.tax_name
    FROM sl_so_item A, m_tax B
    WHERE A.tax_id = B.tax_id AND
          A.so_id = vSoId AND
          A.tax_id <> vEmptyId
    GROUP BY A.tenant_id, A.so_id, A.tax_id, B.flg_amount,
            A.tax_percentage, B.tax_name;

    UPDATE sl_so_tax SET base_amount = A.base_amount, tax_amount = A.tax_amount, update_datetime = vDatetime, update_user_id = vUserId, version = sl_so_tax.version + 1
    FROM tt_sl_so_tax_for_modify_so A
    WHERE sl_so_tax.tenant_id = A.tenant_id
    AND A.session_id = pSessionId
    AND sl_so_tax.so_id = A.so_id
    AND sl_so_tax.tax_id = A.tax_id;

    /*
     * jika flg os change do=y, maka update:
     * 1. sl_so_balance_invoice
     * 2. sl_so_balance_invoice_tax (bisa insert / update)
     */
    INSERT INTO tt_os_delivery_order_item_manage_item_so(
            session_id, tenant_id,
            ou_id, delivery_order_doc_type_id, delivery_order_item_id, delivery_order_id,
            so_id, so_item_id, delivery_order_no, delivery_order_date, curr_code,
            partner_id, gross_sell_price, discount_amount, nett_sell_price, flg_tax_amount,
            tax_id, tax_percentage, qty_dlv_so, so_balance_invoice_id,

            flg_change_do, discount_percentage)

    SELECT pSessionId, pTenantId,
           F.ou_id, C.doc_type_id, D.do_item_id, C.do_id,
           B.so_id, A.so_item_id, C.doc_no, C.doc_date, A.curr_code,
           F.partner_bill_to_id, A.gross_sell_price, A.discount_amount, A.nett_sell_price, A.flg_tax_amount,
           A.tax_id, A.tax_percentage, D.qty_dlv_so, E.so_balance_invoice_id,

           A.flg_change_do, A.discount_percentage

    FROM sl_manage_so_item A
    INNER JOIN sl_manage_so B ON A.manage_so_id = B.manage_so_id
    INNER JOIN sl_do C ON C.ref_id = B.so_id
    INNER JOIN sl_do_item D ON C.do_id = D.do_id AND A.so_item_id = D.ref_id
    INNER JOIN sl_so F ON F.so_id = B.so_id
    INNER JOIN sl_so_balance_invoice E
        ON E.ref_doc_type_id = C.doc_type_id
        AND E.ref_id = C.do_id
        AND E.ref_item_id = D.do_item_id
        AND E.so_id = B.so_id
        AND E.tenant_id = pTenantId
        AND E.ou_id = B.ou_id
        AND E.partner_id = F.partner_bill_to_id
        AND E.do_receipt_item_id = vEmptyId
    WHERE E.flg_invoice = vFlagNo
        AND E.invoice_id = vEmptyId
        AND A.manage_so_id = vManageSoId
        AND A.flg_change_do = vFlagYes
        AND A.nett_sell_price > 0;

    UPDATE sl_so_balance_invoice A
    SET price_so = B.nett_sell_price,
        item_amount = f_get_amount_before_tax_and_disc(B.qty_dlv_so * B.gross_sell_price, B.qty_dlv_so * B.discount_amount, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, B.curr_code), vRoundingModeNonTax),

        regular_disc_amount = B.discount_amount * B.qty_dlv_so,

        gross_sell_price_so = B.gross_sell_price,
        tax_percentage = B.tax_percentage,
        flg_tax_amount = B.flg_tax_amount,
        discount_percentage = B.discount_percentage,
        discount_amount = B.discount_amount,

        version = A.version + 1,
        update_datetime = vDatetime,
        update_user_id = vUserId
    FROM tt_os_delivery_order_item_manage_item_so B
    WHERE B.session_id = pSessionId
    AND B.so_balance_invoice_id = A.so_balance_invoice_id;

    DELETE FROM sl_so_balance_invoice_tax A
    WHERE A.tenant_id = pTenantId
    AND EXISTS (
        SELECT 1 FROM tt_os_delivery_order_item_manage_item_so B
        WHERE A.tenant_id = B.tenant_id
        AND A.ou_id = B.ou_id
        AND A.partner_id = B.partner_id
        AND A.so_id = B.so_id
        AND A.ref_doc_type_id = B.delivery_order_doc_type_id
        AND A.ref_id = B.delivery_order_id
        AND A.ref_item_id = B.delivery_order_item_id
        AND A.do_receipt_item_id = vEmptyId
        AND B.session_id = pSessionId
    );

    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, A.partner_id, A.so_id,
        A.delivery_order_doc_type_id, A.delivery_order_id, A.delivery_order_item_id, A.tax_id, B.flg_amount,
        A.tax_percentage, A.curr_code,
        f_get_amount_before_tax_and_disc(A.qty_dlv_so * A.gross_sell_price, A.qty_dlv_so * A.discount_amount, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, A.curr_code), vRoundingModeNonTax),
        f_tax_rounding(A.tenant_id, f_get_amount_before_tax(A.qty_dlv_so * (A.gross_sell_price - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderDocTypeId, A.curr_code), vRoundingModeNonTax), A.tax_percentage), vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId

    FROM tt_os_delivery_order_item_manage_item_so A
    INNER JOIN m_tax B ON A.tax_id = B.tax_id
    WHERE A.session_id = pSessionId;

    /**
     * Update balance DO Receipt
     */
    INSERT INTO tt_os_delivery_order_receipt_item_manage_item_so(
            session_id, tenant_id,
            ou_id, delivery_order_doc_type_id, delivery_order_item_id, delivery_order_id,
            so_id, so_item_id, delivery_order_receipt_no, delivery_order_receipt_date, curr_code,
            partner_id, gross_sell_price, discount_amount, nett_sell_price,
            regular_disc_amount, promo_disc_amount, flg_tax_amount,
            tax_id, tax_percentage, qty_return, qty_so, qty_int, qty_dlv_so,
            so_balance_invoice_id, do_receipt_item_id,
            flg_change_do, discount_percentage)
    SELECT pSessionId, pTenantId,
           F.ou_id, C.doc_type_id, E.ref_id, C.do_id,
           F.so_id, G.so_item_id, D.doc_no, D.doc_date, A.curr_code,
           F.partner_bill_to_id, A.gross_sell_price, A.discount_amount, A.nett_sell_price,
           I.regular_disc_amount, I.promo_disc_amount, A.flg_tax_amount,
           A.tax_id, A.tax_percentage, E.qty_return, G.qty_so, G.qty_int, I.qty_dlv_so,
           H.so_balance_invoice_id, E.do_receipt_item_id,
           A.flg_change_do, A.discount_percentage
    FROM sl_manage_so_item A
    INNER JOIN sl_manage_so B ON A.manage_so_id = B.manage_so_id
    INNER JOIN sl_do C ON C.ref_id = B.so_id
    INNER JOIN sl_do_item J ON C.do_id = J.do_id AND A.so_item_id = J.ref_id
    INNER JOIN in_do_receipt D ON D.ref_id = C.do_id
    INNER JOIN in_do_receipt_item E ON E.do_receipt_id = D.do_receipt_id AND E.ref_id = J.do_item_id
    INNER JOIN sl_so F ON B.so_id = F.so_id
    INNER JOIN sl_so_item G ON F.so_id = G.so_id AND G.so_item_id = J.ref_id
    INNER JOIN sl_so_balance_invoice H
        ON H.ref_doc_type_id = C.doc_type_id
        AND H.ref_id = C.do_id
        AND H.ref_item_id = E.ref_id
        AND H.tenant_id = pTenantId
        AND H.ou_id = D.ou_id
        AND H.partner_id = F.partner_bill_to_id
        AND H.do_receipt_item_id = E.do_receipt_item_id
    INNER JOIN sl_so_balance_invoice I
        ON I.ref_doc_type_id = C.doc_type_id
        AND I.ref_id = C.do_id
        AND I.ref_item_id = E.ref_id
        AND I.tenant_id = pTenantId
        AND I.ou_id = D.ou_id
        AND I.partner_id = F.partner_bill_to_id
        AND I.do_receipt_item_id = vEmptyId
    WHERE H.flg_invoice = vFlagNo
        AND H.invoice_id = vEmptyId
        AND A.manage_so_id = vManageSoId
        AND A.flg_change_do = vFlagYes
        AND G.qty_int > 0;

    UPDATE sl_so_balance_invoice A
    SET price_so = B.nett_sell_price,
        item_amount = -1 * f_get_amount_before_tax_and_disc((B.qty_return * B.qty_so / B.qty_int) * B.gross_sell_price, (B.regular_disc_amount + B.promo_disc_amount) * (B.qty_return * B.qty_so / B.qty_int) / B.qty_dlv_so, B.flg_tax_amount, B.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, B.curr_code), vRoundingModeNonTax),

        regular_disc_amount = -1 * (B.discount_amount * B.qty_return),

        gross_sell_price_so = B.gross_sell_price,
        tax_percentage = B.tax_percentage,
        flg_tax_amount = B.flg_tax_amount,
        discount_percentage = B.discount_percentage,
        discount_amount = B.discount_amount,

        -- flg_change_do = B.flg_change_do,

        version = A.version + 1,
        update_datetime = vDatetime,
        update_user_id = vUserId
    FROM tt_os_delivery_order_receipt_item_manage_item_so B
    WHERE B.session_id = pSessionId
    AND B.so_balance_invoice_id = A.so_balance_invoice_id;

    DELETE FROM sl_so_balance_invoice_tax A
    WHERE A.tenant_id = pTenantId
    AND EXISTS (
        SELECT 1 FROM tt_os_delivery_order_receipt_item_manage_item_so B
        WHERE A.tenant_id = B.tenant_id
        AND A.ou_id = B.ou_id
        AND A.partner_id = B.partner_id
        AND A.so_id = B.so_id
        AND A.ref_doc_type_id = B.delivery_order_doc_type_id
        AND A.ref_id = B.delivery_order_id
        AND A.ref_item_id = B.delivery_order_item_id
        AND A.do_receipt_item_id = B.do_receipt_item_id
        AND B.session_id = pSessionId
    );

    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,
    do_receipt_item_id)
    SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
        A.delivery_order_doc_type_id, A.delivery_order_id, A.delivery_order_item_id, A.tax_id, B.flg_amount,
        A.tax_percentage, A.curr_code,
        -1 * f_get_amount_before_tax_and_disc((A.qty_return * A.qty_so / A.qty_int) * A.gross_sell_price, (A.qty_return * A.qty_so / A.qty_int) * A.discount_amount, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, A.curr_code), vRoundingModeNonTax),
        -1 * f_tax_rounding(A.tenant_id, f_get_amount_before_tax((A.qty_return * A.qty_so / A.qty_int) * (A.gross_sell_price - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDoReceiptDocTypeId, A.curr_code), vRoundingModeNonTax), A.tax_percentage), vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId,
        A.do_receipt_item_id
    FROM tt_os_delivery_order_receipt_item_manage_item_so A
    INNER JOIN m_tax B ON A.tax_id = B.tax_id
    WHERE A.session_id = pSessionId;

    UPDATE sl_manage_so SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
    WHERE manage_so_id = vManageSoId;

    DELETE FROM tt_sl_so_tax_for_modify_so WHERE session_id = pSessionId;
    DELETE FROM tt_os_delivery_order_item_manage_item_so WHERE session_id = pSessionId;
    DELETE FROM tt_os_delivery_order_receipt_item_manage_item_so WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /