c_commissiondetail_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER C_CommissionDetail_Trg
BEFORE INSERT OR UPDATE OR DELETE
	ON C_CommissionDetail
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 Commission Amount Line
 *	Convert Amount to Commission Currrency
 */
	v_C_Currency_ID				NUMBER;
	v_ConvDate					DATE;
BEGIN
	IF (UPDATING OR DELETING) THEN
	--	DBMS_OUTPUT.PUT_LINE('C_CommissionDetail_Trg - Subtract');
		--	Subtract old Amount/Qty from Amount
		UPDATE	C_CommissionAmt
		  SET	ConvertedAmt = ConvertedAmt - :old.ConvertedAmt,
				ActualQty = ActualQty - :old.ActualQty
		WHERE	C_CommissionAmt_ID = :old.C_CommissionAmt_ID;
	END IF;

	IF (INSERTING OR UPDATING) THEN
	--	DBMS_OUTPUT.PUT_LINE('C_CommissionDetail_Trg - Get Info');
		--	Get Info From CommissionRun
		SELECT	cr.StartDate, c.C_Currency_ID
		  INTO	v_ConvDate, v_C_Currency_ID
		FROM	C_Commission c, C_CommissionRun cr, C_CommissionAmt ca
		WHERE	ca.C_CommissionAmt_ID = :new.C_CommissionAmt_ID
		  AND	cr.C_CommissionRun_ID=ca.C_CommissionRun_ID
		  AND	cr.C_Commission_ID=c.C_Commission_ID;

		--	Convert
		:new.ConvertedAmt := C_Currency_Convert (:new.ActualAmt, :new.C_Currency_ID, 
					v_C_Currency_ID, v_ConvDate, 'S', :new.AD_Client_ID, :new.AD_Org_ID);

		--	Add new Amount/Qty to Amount
	--	DBMS_OUTPUT.PUT_LINE('C_CommissionDetail_Trg - Add');
		UPDATE	C_CommissionAmt
		  SET	ConvertedAmt = ConvertedAmt + :new.ConvertedAmt,
				ActualQty = ActualQty + :new.ActualQty
		WHERE	C_CommissionAmt_ID = :new.C_CommissionAmt_ID;
	END IF;

END C_CommissionDetail_Trg;
/