f_update_po_item_price

--Adrian, May 4, 2018
--Untuk update data harga PO yang dibuat sampai dengan RG

CREATE OR REPLACE FUNCTION f_update_po_item_price(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, NUMERIC, CHARACTER VARYING, BIGINT, NUMERIC)
  RETURNS void AS
$BODY$
DECLARE
    pSessionId                  ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pPoItemId                   ALIAS FOR $3;
    pUserId                     ALIAS FOR $4;
    pDatetime                   ALIAS FOR $5;
    pNewGrossPricePo            ALIAS FOR $6;
    pNewFlgTaxAmount            ALIAS FOR $7;
    pNewTaxId                   ALIAS FOR $8;
    pNewDiscountPercentage      ALIAS FOR $9;

    vDecimalForRounding         integer;
    vRoundingMode               character varying;
    vUsedGrossPricePo           numeric;
    vCurrCode                   character varying;
    vDigitDppRoundingTax        integer;

    vYes                            character varying;
    vEmptyId                        bigint;
    vSpaceValue                     character varying;
    vRoundingModeDown               character varying;
    vFlagNo                         character varying;
    vComboIdYesNo                   character varying;
    vPurchaseOrderDocTypeId         bigint;
    vReceiveGoodsDocTypeId          bigint;
    vParamCodeRoundingModeNonTax    character varying;
    vParamCodeDppRoundingDecimalTax character varying;

BEGIN

    vYes := 'Y';
    vSpaceValue := ' ';
    vEmptyId := -99;
    vRoundingModeDown := 'RD';
    vFlagNo := 'N';
    vComboIdYesNo := 'YESNO';
    vPurchaseOrderDocTypeId := 101;
    vReceiveGoodsDocTypeId := 111;
    vParamCodeRoundingModeNonTax :='rounding.mode.non.tax';
    vParamCodeDppRoundingDecimalTax := 'dpp.rounding.decimal.tax';

    SELECT curr_code FROM pu_po_item A WHERE A.po_item_id = pPoItemId INTO vCurrCode;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Purchase Order Item Id % is not found', pPoItemId;
    END IF;

    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingModeNonTax) INTO vRoundingMode;
    SELECT f_get_digit_decimal_doc_curr(vPurchaseOrderDocTypeId, vCurrCode) INTO vDecimalForRounding;
    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeDppRoundingDecimalTax)::integer INTO vDigitDppRoundingTax;

    --Validasi flg_tax_amount harus sesuai dengan combo YESNO
    IF NOT EXISTS (
        SELECT 1
        FROM t_combo_value A
        WHERE A.combo_id = vComboIdYesNo AND
            A.code = pNewFlgTaxAmount
    ) THEN
        RAISE EXCEPTION 'Value Flag Tax Amount is not valid ,should not have values : %',
            pNewFlgTaxAmount;
    END IF;

    --Validasi jika flg_tax_amount = 'Y', maka tax_id tidak boleh -99
    IF (pNewFlgTaxAmount = vYes AND pNewTaxId = vEmptyId) THEN
        RAISE EXCEPTION 'Gross Price is including tax, so you must choose tax value';
    END IF;

    --Validasi Gross Price harus >= 0
    IF pNewGrossPricePo < 0 THEN
        RAISE EXCEPTION '% must be >= 0 (zero)',
            pNewGrossPricePo;
    END IF;

    --Hitung Gross Price setelah discount
    SELECT CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
            TRUNC(pNewGrossPricePo, vDecimalForRounding) - TRUNC(pNewGrossPricePo * pNewDiscountPercentage / 100, vDecimalForRounding)
        ELSE
            ROUND(pNewGrossPricePo, vDecimalForRounding) - ROUND(pNewGrossPricePo * pNewDiscountPercentage / 100, vDecimalForRounding)
        END
    INTO vUsedGrossPricePo;

    IF EXISTS (
        SELECT 1
        FROM pu_po_balance_invoice Z
        WHERE EXISTS (
            SELECT 1
            FROM pu_po_item A
            INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
            INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
            WHERE A.po_item_id = pPoItemId AND
                Z.tenant_id = C.tenant_id AND
                Z.ou_id = C.ou_id AND
                Z.po_id = C.ref_id AND
                Z.ref_doc_type_id = C.doc_type_id AND
                Z.ref_id = C.receive_goods_id AND
                Z.ref_item_id = B.receive_goods_item_id
        ) AND
        flg_invoice <> vFlagNo
    ) THEN
        RAISE EXCEPTION 'Invoice for Purchase Order already exists';
    END IF;

    IF EXISTS (
        SELECT 1
        FROM pu_po_balance_invoice_tax Z
        WHERE EXISTS (
            SELECT 1
            FROM pu_po_item A
            INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
            INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
            WHERE A.po_item_id = pPoItemId AND
                Z.tenant_id = C.tenant_id AND
                Z.ou_id = C.ou_id AND
                Z.po_id = C.ref_id AND
                Z.ref_doc_type_id = C.doc_type_id AND
                Z.ref_id = C.receive_goods_id AND
                Z.ref_item_id = B.receive_goods_item_id AND
                Z.tax_id = A.tax_id
        ) AND
        flg_invoice <> vFlagNo
    ) THEN
        RAISE EXCEPTION 'Invoice for Purchase Order already exists';
    END IF;

    --Update pajak lama di pu_po_tax
    UPDATE pu_po_tax Z
    SET base_amount = Z.base_amount - f_get_amount_before_tax(A.gross_item_amount, A.flg_tax_amount, A.tax_percentage, vDecimalForRounding, vRoundingMode),
        tax_amount = Z.tax_amount - f_tax_rounding(A.tenant_id, f_get_amount_before_tax(A.gross_item_amount, A.flg_tax_amount, A.tax_percentage, vDecimalForRounding, vRoundingMode), A.tax_percentage),
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = Z.version + 1
    FROM pu_po_item A
    WHERE A.po_item_id = pPoItemId AND
        A.tax_id <> vEmptyId AND
        Z.tenant_id = A.tenant_id AND
        Z.po_id = A.po_id AND
        Z.tax_id = A.tax_id;

    --Hapus data balance invoice tax lama
    DELETE FROM pu_po_balance_invoice_tax Z
    WHERE Z.tenant_id = pTenantId
    AND EXISTS (
        SELECT 1
        FROM pu_po_item A
        INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
        INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
        WHERE A.po_item_id = pPoItemId AND
            Z.tenant_id = C.tenant_id AND
            Z.ou_id = C.ou_id AND
            Z.po_id = C.ref_id AND
            Z.ref_doc_type_id = C.doc_type_id AND
            Z.ref_id = C.receive_goods_id AND
            Z.ref_item_id = B.receive_goods_item_id AND
            Z.tax_id = A.tax_id
    );

    --Update amount di journal trx mapping lama
    WITH tt_grouped_receive_goods_item AS (
        SELECT D.journal_trx_id, B.qty_rcv_po, A.gross_price_po, A.discount_amount, A.flg_tax_amount, A.tax_percentage, A.curr_code
        FROM pu_po_item A
        INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
        INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
        INNER JOIN gl_journal_trx D ON C.tenant_id = D.tenant_id AND
            (f_get_document_journal(C.doc_type_id)).journal_type = D.journal_type AND
            C.doc_type_id = D.doc_type_id AND
            C.receive_goods_id = D.doc_id AND
            C.doc_no = D.doc_no AND
            C. doc_date = D.doc_date AND
            (f_get_ou_bu_structure(C.ou_id)).ou_bu_id = D.ou_bu_id AND
            (f_get_ou_bu_structure(C.ou_id)).ou_branch_id = D.ou_branch_id AND
            (f_get_ou_bu_structure(C.ou_id)).ou_sub_bu_id = D.ou_sub_bu_id
        WHERE A.po_item_id = pPoItemId
        GROUP BY D.journal_trx_id, B.qty_rcv_po, A.gross_price_po, A.discount_amount, A.flg_tax_amount, A.tax_percentage, A.curr_code
    )
    UPDATE gl_journal_trx_mapping Z
    SET amount = Z.amount - f_get_amount_before_tax(A.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingMode),
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = Z.version + 1
    FROM tt_grouped_receive_goods_item A
    WHERE Z.journal_trx_id = A.journal_trx_id;

    --Update harga pada item PO
    IF (pNewFlgTaxAmount = vYes) THEN
        --harga include pajak
        UPDATE pu_po_item Z
        SET gross_price_po = vUsedGrossPricePo,
            nett_price_po = vUsedGrossPricePo - CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                    TRUNC(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding)
                ELSE
                    ROUND(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding)
                END,
            gross_item_amount = vUsedGrossPricePo * Z.qty_po,
            nett_item_amount = (vUsedGrossPricePo - CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                    TRUNC(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding)
                ELSE
                    ROUND(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding)
                END) * Z.qty_po,
            tax_price =
                CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                    TRUNC(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding)
                ELSE
                    ROUND(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding)
                END,
            tax_amount =
                CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                    TRUNC(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding) * Z.qty_po
                ELSE
                    ROUND(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding) * Z.qty_po
                END,
            tax_percentage = A.percentage,
            flg_tax_amount = pNewFlgTaxAmount,
            tax_id = pNewTaxId,
            update_user_id = pUserId,
            update_datetime = pDatetime,
            version = Z.version + 1
        FROM m_tax A
        WHERE A.tax_id = pNewTaxId AND
            Z.po_item_id = pPoItemId;
    ELSE
        IF (pNewTaxId <> vEmptyId) THEN
            --harga exclude pajak,
            --tetapi ada pajak
            UPDATE pu_po_item Z
            SET gross_price_po = vUsedGrossPricePo,
                nett_price_po = vUsedGrossPricePo,
                gross_item_amount = vUsedGrossPricePo * Z.qty_po,
                nett_item_amount = vUsedGrossPricePo * Z.qty_po,
                tax_price =
                    CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                        TRUNC(vUsedGrossPricePo * A.percentage / 100, vDecimalForRounding)
                    ELSE
                        ROUND(vUsedGrossPricePo * A.percentage / 100, vDecimalForRounding)
                    END,
                tax_amount =
                    CASE WHEN (vRoundingMode = vRoundingModeDown) THEN
                        TRUNC(vUsedGrossPricePo * A.percentage / 100, vDecimalForRounding) * Z.qty_po
                    ELSE
                        ROUND(vUsedGrossPricePo * A.percentage / 100, vDecimalForRounding) * Z.qty_po
                    END,
                tax_percentage = A.percentage,
                flg_tax_amount = pNewFlgTaxAmount,
                tax_id = pNewTaxId,
                update_user_id = pUserId,
                update_datetime = pDatetime,
                version = Z.version + 1
            FROM m_tax A
            WHERE A.tax_id = pNewTaxId AND
                Z.po_item_id = pPoItemId;
        ELSE
            --harga exclude pajak dan
            --tanpa pajak
            UPDATE pu_po_item Z
            SET gross_price_po = vUsedGrossPricePo,
                nett_price_po = vUsedGrossPricePo,
                gross_item_amount = vUsedGrossPricePo * Z.qty_po,
                nett_item_amount = vUsedGrossPricePo * Z.qty_po,
                tax_price = 0,
                tax_amount = 0,
                tax_percentage = 0,
                flg_tax_amount = pNewFlgTaxAmount,
                tax_id = vEmptyId,
                update_user_id = pUserId,
                update_datetime = pDatetime,
                version = Z.version + 1
            WHERE Z.po_item_id = pPoItemId;
        END IF;
    END IF;

    --Update pajak baru di pu_po_tax
    UPDATE pu_po_tax Z
    SET base_amount = Z.base_amount + f_get_amount_before_tax(A.gross_item_amount, A.flg_tax_amount, A.tax_percentage, vDecimalForRounding, vRoundingMode),
        tax_amount = Z.tax_amount + f_tax_rounding(A.tenant_id, f_get_amount_before_tax(A.gross_item_amount, A.flg_tax_amount, A.tax_percentage, vDecimalForRounding, vRoundingMode), A.tax_percentage),
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = Z.version + 1
    FROM pu_po_item A
    WHERE A.po_item_id = pPoItemId AND
        A.tax_id <> vEmptyId AND
        Z.tenant_id = A.tenant_id AND
        Z.po_id = A.po_id AND
        Z.tax_id = A.tax_id;

    --Update balance invoice dengan harga baru
    UPDATE pu_po_balance_invoice Z
    SET price_po = A.nett_price_po,
        item_amount = f_get_amount_before_tax(B.qty_rcv_po * (A.gross_price_po - A.discount_amount),A.flg_tax_amount,A.tax_percentage,vDigitDppRoundingTax, vRoundingMode),
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = Z.version + 1
    FROM pu_po_item A
    INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
    INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
    WHERE A.po_item_id = pPoItemId AND
        Z.tenant_id = C.tenant_id AND
        Z.ou_id = C.ou_id AND
        Z.po_id = C.ref_id AND
        Z.ref_doc_type_id = C.doc_type_id AND
        Z.ref_id = C.receive_goods_id AND
        Z.ref_item_id = B.receive_goods_item_id;

    --Insert balance invoice tax dengan harga baru
    INSERT INTO pu_po_balance_invoice_tax
    (tenant_id, ou_id, partner_id, po_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, E.ou_id, E.partner_id, A.po_id,
        C.doc_type_id, C.receive_goods_id, B.receive_goods_item_id, A.tax_id, D.flg_amount,
        A.tax_percentage, A.curr_code,
        f_get_amount_before_tax(B.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingMode),
        f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingMode), A.tax_percentage),
        vFlagNo, vEmptyId,
        0, pDatetime, pUserId, pDatetime, pUserId
    FROM pu_po_item A
    INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
    INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
    INNER JOIN m_tax D ON A.tax_id = D.tax_id
    INNER JOIN pu_po E ON A.po_id = E.po_id
    WHERE A.po_item_id = pPoItemId;

    --Update balance invoice ext
    UPDATE pu_po_balance_invoice_ext Z
        SET discount_percentage = A.discount_percentage,
            discount_amount = A.discount_amount,
            total_discount_amount = B.qty_rcv_po * A.discount_amount,
            update_user_id = pUserId,
            update_datetime = pDatetime
    FROM pu_po_item A
    INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
    INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
    WHERE A.po_item_id = pPoItemId AND
        Z.po_item_id = pPoItemId;



    --Update product price balance (tidak ada data receive goods product)
    UPDATE in_product_price_balance Z
        SET amount = f_get_amount_before_tax(B.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingMode),
            update_user_id = pUserId,
            update_datetime = pDatetime,
            version = Z.version + 1
    FROM pu_po_item A
    INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
    INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
    INNER JOIN in_product_balance E ON C.tenant_id = E.tenant_id AND
        B.product_id = E.product_id AND
        vSpaceValue = E.serial_number AND
        vSpaceValue = E.lot_number
    WHERE A.po_item_id = pPoItemId AND
        NOT EXISTS (SELECT 1 FROM pu_receive_goods_product D
                    WHERE B.receive_goods_item_id = D.receive_goods_item_id) AND
        NOT EXISTS (SELECT 1 FROM pu_receive_goods_product_auto_sn D
                    WHERE B.receive_goods_item_id = D.receive_goods_item_id) AND
        Z.tenant_id = C.tenant_id AND
        Z.ou_id = C.ou_id AND
        Z.product_balance_id = E.product_balance_id AND
        Z.doc_type_id = C.doc_type_id AND
        Z.ref_id = B.receive_goods_item_id;

     --Update product price balance (ada data receive goods product)
    UPDATE in_product_price_balance Z
        SET amount = f_get_amount_before_tax(B.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingMode),
            update_user_id = pUserId,
            update_datetime = pDatetime,
            version = Z.version + 1
    FROM pu_po_item A
    INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
    INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
    INNER JOIN pu_receive_goods_product D ON B.receive_goods_item_id = D.receive_goods_item_id
    INNER JOIN in_product_balance E ON C.tenant_id = E.tenant_id AND
        B.product_id = E.product_id AND
        D.serial_number = E.serial_number AND
        D.lot_number = E.lot_number
    WHERE A.po_item_id = pPoItemId AND
        Z.tenant_id = C.tenant_id AND
        Z.ou_id = C.ou_id AND
        Z.product_balance_id = E.product_balance_id AND
        Z.doc_type_id = C.doc_type_id AND
        Z.ref_id = B.receive_goods_item_id;

    --Update monthly price product
    WITH tt_product_year_month AS(
        SELECT C.receive_goods_id, C.tenant_id, C.ou_id, A.product_id, D.year_month_date
        FROM pu_po_item A
        INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
        INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
        INNER JOIN dt_date D ON C.doc_date = D.string_date
        WHERE A.po_item_id = pPoItemId
    ), tt_monthly_price_product AS(
        SELECT C.tenant_id, C.ou_id, D.year_month_date, B.product_id,
            SUM(f_get_amount_before_tax(B.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingMode)) AS amount,
            A.flg_tax_amount, A.tax_id, A.tax_percentage,
            SUM(B.qty_rcv_int) AS qty, B.base_uom_id,
            C.doc_type_id AS ref_doc_type_id, C.receive_goods_id AS ref_id,
            C.doc_no AS ref_doc_no, C.doc_date AS ref_doc_date,
            'PURCHASING PO NO : '|| F.doc_no || ' DATE : '|| F.doc_date AS source_price
        FROM pu_po_item A
        INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
        INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
        INNER JOIN dt_date D ON C.doc_date = D.string_date
        INNER JOIN pu_po F ON A.po_id = F.po_id
        WHERE EXISTS(
            SELECT 1
            FROM tt_product_year_month E
            WHERE E.receive_goods_id = C.receive_goods_id AND
                E.tenant_id = C.tenant_id AND
                E.ou_id = C.ou_id AND
                E.product_id = A.product_id AND
                E.year_month_date = D.year_month_date
        )
        GROUP BY C.tenant_id, C.ou_id, D.year_month_date, B.product_id,
            A.flg_tax_amount, A.tax_id, A.tax_percentage,
            B.base_uom_id, C.doc_type_id, C.receive_goods_id, C.doc_no, C.doc_date,
            F.doc_no, F.doc_date
    )
    UPDATE pu_monthly_price_product Z
    SET amount = A.amount,
        flg_tax_amount = A.flg_tax_amount,
        tax_id = A.tax_id,
        tax_percentage = A.tax_percentage,
        qty = A.qty,
        ref_doc_type_id = A.ref_doc_type_id,
        ref_id = A.ref_id,
        ref_doc_no = A.ref_doc_no,
        ref_doc_date = A.ref_doc_date,
        source_price = A.source_price,
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = Z.version + 1
    FROM tt_monthly_price_product A
    WHERE Z.tenant_id = A.tenant_id AND
        Z.ou_id = A.ou_id AND
        Z.year_month_date = A.year_month_date AND
        Z.product_id = A.product_id;

    UPDATE gl_journal_trx_item Z
    SET amount = f_get_amount_before_tax(B.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingMode),
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = Z.version + 1
    FROM pu_po_item A
    INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
    INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
    INNER JOIN gl_journal_trx D ON C.tenant_id = D.tenant_id AND
        (f_get_document_journal(C.doc_type_id)).journal_type = D.journal_type AND
        C.doc_type_id = D.doc_type_id AND
        C.receive_goods_id = D.doc_id AND
        C.doc_no = D.doc_no AND
        C. doc_date = D.doc_date AND
        (f_get_ou_bu_structure(C.ou_id)).ou_bu_id = D.ou_bu_id AND
        (f_get_ou_bu_structure(C.ou_id)).ou_branch_id = D.ou_branch_id AND
        (f_get_ou_bu_structure(C.ou_id)).ou_sub_bu_id = D.ou_sub_bu_id
    WHERE A.po_item_id = pPoItemId AND
        Z.journal_trx_id = D.journal_trx_id AND
        Z.ref_doc_type_id = C.doc_type_id AND
        Z.ref_id = B.receive_goods_item_id;

    WITH tt_grouped_receive_goods_item AS (
        SELECT D.journal_trx_id, B.qty_rcv_po, A.gross_price_po, A.discount_amount, A.flg_tax_amount, A.tax_percentage, A.curr_code
        FROM pu_po_item A
        INNER JOIN pu_receive_goods_item B ON A.po_item_id = B.ref_id
        INNER JOIN pu_receive_goods C ON B.receive_goods_id = C.receive_goods_id
        INNER JOIN gl_journal_trx D ON C.tenant_id = D.tenant_id AND
            (f_get_document_journal(C.doc_type_id)).journal_type = D.journal_type AND
            C.doc_type_id = D.doc_type_id AND
            C.receive_goods_id = D.doc_id AND
            C.doc_no = D.doc_no AND
            C. doc_date = D.doc_date AND
            (f_get_ou_bu_structure(C.ou_id)).ou_bu_id = D.ou_bu_id AND
            (f_get_ou_bu_structure(C.ou_id)).ou_branch_id = D.ou_branch_id AND
            (f_get_ou_bu_structure(C.ou_id)).ou_sub_bu_id = D.ou_sub_bu_id
        WHERE A.po_item_id = pPoItemId
        GROUP BY D.journal_trx_id, B.qty_rcv_po, A.gross_price_po, A.discount_amount, A.flg_tax_amount, A.tax_percentage, A.curr_code
    )
    UPDATE gl_journal_trx_mapping Z
    SET amount = Z.amount + f_get_amount_before_tax(A.qty_rcv_po * (A.gross_price_po - A.discount_amount), A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code), vRoundingMode),
        update_user_id = pUserId,
        update_datetime = pDatetime,
        version = Z.version + 1
    FROM tt_grouped_receive_goods_item A
    WHERE Z.journal_trx_id = A.journal_trx_id;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 /