Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --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;
- vRoundingModeDown character varying;
- vPurchaseOrderDocTypeId bigint;
- vParamCodeRoundingModeNonTax character varying;
- vParamCodeDppRoundingDecimalTax character varying;
- BEGIN
- vYes := 'Y';
- vEmptyId := -99;
- vRoundingModeDown := 'RD';
- vPurchaseOrderDocTypeId := 101;
- 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 balance invoice dengan harga lama
- UPDATE pu_po_balance_invoice Z
- SET price_po = Z.price_po - A.nett_price_po,
- item_amount = Z.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 lama
- UPDATE pu_po_balance_invoice_tax Z
- SET base_amount = Z.base_amount - A.nett_price_po,
- tax_amount = Z.tax_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 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 = Z.price_po - A.nett_price_po,
- item_amount = Z.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;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement