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(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 item jurnal
- 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;
- --Update mapping jurnal
- 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;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement