c_projectline_trg.sql

来自「Java写的ERP系统」· SQL 代码 · 共 49 行

CREATE OR REPLACE TRIGGER C_ProjectLine_Trg
BEFORE INSERT OR UPDATE OR UPDATE
	ON C_ProjectLine
FOR EACH ROW
DECLARE
/******************************************************************************
 * ** Compiere Product **             Copyright (c) 1999-2001 Accorto, Inc. USA
 * Open  Source  Software        Provided "AS IS" without warranty or liability
 * When you use any parts (changed or unchanged), add  "Powered by Compiere" to
 * your product name;  See license details http://www.compiere.org/license.html
 ******************************************************************************
 *	Update Line Totals
 *  Update Project Totals
 */
	v_PriceLimit				NUMBER := NULL;
	v_PriceStd					NUMBER := NULL;
	v_PlannedMargin				NUMBER := NULL;
BEGIN
	--	Calculate PlannedAmt
	IF (:new.PlannedQty <> 0 AND :new.PlannedPrice <> 0) THEN
		:new.PlannedAmt := :new.PlannedQty * :new.PlannedPrice;
	END IF;

	--	Get Margin from Product
	IF (:new.M_Product_ID IS NOT NULL AND :new.PlannedPrice <> 0) THEN
		SELECT 	NVL(MAX(PriceLimit), 0), NVL(MAX(PriceStd), 0)
		  INTO	v_PriceLimit, v_PriceStd
		FROM 	M_ProductPrice
		WHERE 	M_Product_ID = :new.M_Product_ID
		  AND 	M_PriceList_Version_ID =
		  	(SELECT M_PriceList_Version_ID FROM C_Project WHERE C_Project_ID=:new.C_Project_ID);
		IF (v_PriceLimit <> 0) THEN
			:new.PlannedMarginAmt := :new.PlannedQty * (:new.PlannedPrice - v_PriceLimit);
		END IF;

	--	Get Margin from Product Category
	ELSIF (:new.M_Product_Category_ID IS NOT NULL AND :new.PlannedPrice <> 0) THEN
		SELECT 	NVL(MAX(PlannedMargin), 0)
		  INTO	v_PlannedMargin
		FROM 	M_Product_Category
		WHERE	M_Product_Category_ID = :new.M_Product_Category_ID;
		IF (v_PlannedMargin <> 0) THEN
			:new.PlannedMarginAmt := :new.PlannedAmt * v_PlannedMargin;
		END IF;
	END IF;

END C_ProjectLine_Trg;
/