c_commissionamt_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER C_CommissionAmt_Trg
BEFORE INSERT OR UPDATE OR DELETE
	ON C_CommissionAmt
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 Header GrandTotal
 *	Convert Amount to Commission Currrency
 *	Calculate Commission Amount
 */
	v_AmtSubtract				NUMBER;
	v_AmtMultiplier				NUMBER;
	v_QtySubtract				NUMBER;
	v_QtyMultiplier				NUMBER;
	v_IsPositiveOnly			CHAR(1);
	--
	v_Result					NUMBER;
BEGIN
	IF (UPDATING OR DELETING) THEN
	--	DBMS_OUTPUT.PUT_LINE('C_CommissionAmt_Trg - Subtract');
		--	Subtract old Amount from Header
		UPDATE	C_CommissionRun
		  SET	GrandTotal = GrandTotal - :old.CommissionAmt
	  	WHERE	C_CommissionRun_ID = :old.C_CommissionRun_ID;
	END IF;

	IF (INSERTING OR UPDATING) THEN
	--	DBMS_OUTPUT.PUT_LINE('C_CommissionAmt_Trg - Get Info');
		--	Get Info From CommissionLine
		SELECT	AmtSubtract, AmtMultiplier, QtySubtract, QtyMultiplier, IsPositiveOnly
		  INTO	v_AmtSubtract, v_AmtMultiplier, v_QtySubtract, v_QtyMultiplier, v_IsPositiveOnly
	  	FROM	C_CommissionLine
		WHERE	C_CommissionLine_ID = :new.C_CommissionLine_ID;

		--	Quantities
		v_Result := (:new.ActualQty - v_QtySubtract) * v_QtyMultiplier;
		IF (v_IsPositiveOnly = 'Y' AND v_Result < 0) THEN
			v_Result := 0;
		END IF;
		--	Amount
		:new.CommissionAmt := v_Result;
		v_Result := (:new.ConvertedAmt - v_AmtSubtract) * v_AmtMultiplier;
		IF (v_IsPositiveOnly = 'Y' AND v_Result < 0) THEN
			v_Result := 0;
		END IF;
		:new.CommissionAmt := :new.CommissionAmt + v_Result;

		--	Add new Amount to Header
	--	DBMS_OUTPUT.PUT_LINE('C_CommissionAmt_Trg - Add');
		UPDATE	C_CommissionRun
		  SET	GrandTotal = GrandTotal + :new.CommissionAmt
	  	WHERE	C_CommissionRun_ID = :new.C_CommissionRun_ID;
	END IF;
END C_CommissionAmt_Trg;
/