f_update_po_item_price_20180507_1

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

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

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

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

BEGIN

    vYes := 'Y';
    vSpaceValue := ' ';
    vEmptyId := -99;
    vRoundingModeDown := 'RD';
    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;

    SELECT f_get_value_system_config_by_param_code(pTenantId, vParamCodeRoundingModeNonTax) INTO vRoundingMode;
    SELECT f_get_digit_decimal_doc_curr(vPurchaseOrderDocTypeId, curr_code) 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;


    --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
    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 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,
            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,
            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
                ELSE
                    ROUND(vUsedGrossPricePo * A.percentage / (100 + A.percentage), vDecimalForRounding) * Z.qty
                END
            tax_percentage = A.percentage,
            flg_tax_amount = pNewFlgTaxAmount,
            tax_id = pNewTaxId,
            update_user_id = pUserId,
            update_datetime = pDatetime
        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,
                nett_item_amount = vUsedGrossPricePo * Z.qty,
                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
                    ELSE
                        ROUND(vUsedGrossPricePo * A.percentage / 100, vDecimalForRounding) * Z.qty
                    END
                tax_percentage = A.percentage,
                flg_tax_amount = pNewFlgTaxAmount,
                tax_id = pNewTaxId,
                update_user_id = pUserId,
                update_datetime = pDatetime
            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,
                nett_item_amount = vUsedGrossPricePo * Z.qty,
                tax_price = 0,
                tax_amount = 0,
                tax_percentage = 0,
                flg_tax_amount = pNewFlgTaxAmount,
                tax_id = vEmptyId,
                update_user_id = pUserId,
                update_datetime = pDatetime
            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
    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
     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;

    --Update balance invoice tax dengan harga baru
     UPDATE pu_po_balance_invoice_tax Z
     SET base_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),
        tax_amount = f_tax_rounding(C.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,vDigitDppRoundingTax, vRoundingMode), A.tax_percentage),
        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.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 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
    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 = A.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
    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 = C.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 = A.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
    UPDATE pu_monthly_price_product
    SET amount = A.amount,
        source_price = A.source_price,
        flg_tax_amount = A.flg_tax_amount,
        tax_id = A.tax_id,
        tax_percentage = A.tax_percentage
    FROM tt_pu_monthly_price_product A
    WHERE A.session_id = pSessionId AND
        pu_monthly_price_product.tenant_id = A.tenant_id AND
        pu_monthly_price_product.ou_id = A.ou_id AND
        pu_monthly_price_product.year_month_date = A.year_month_date AND
        pu_monthly_price_product.product_id = A.product_id;

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