c_orderline_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER C_OrderLine_Trg
BEFORE INSERT OR DELETE OR UPDATE 
	OF M_Product_ID, LineNetAmt, FreightAmt, ChargeAmt, C_Tax_ID
	ON C_OrderLine
FOR EACH ROW
/*************************************************************************
 * The contents of this file are subject to the Compiere License.  You may
 * obtain a copy of the License at    http://www.compiere.org/license.html
 * Software is on an  "AS IS" basis,  WITHOUT WARRANTY OF ANY KIND, either
 * express or implied. See the License for details. Code: Compiere ERP+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_OrderLine_Trg.sql,v 1.8 2003/03/10 05:39:07 jjanke Exp $
 ***
 * Title:	Insert Order Line	 
 * Description:
 *	- Check Product changes
 *	- Rounding
 *	- Update of Tax
 ************************************************************************/
DECLARE
	newTaxBaseAmt	NUMBER;
	oldTaxBaseAmt	NUMBER;
	oldLine			NUMBER := 0;
	newLine			NUMBER := 0;
	taxAmt			NUMBER := 0;
	HasTaxLine		BOOLEAN := FALSE;
	Prec			NUMBER;
	v_ID			NUMBER;
	v_RO			NUMBER;

BEGIN
	/**
	 *	Check Product changes	= not possible when reservation, invoice or delivery exists
	 */
	IF (DELETING OR (UPDATING AND :old.M_Product_ID <> :new.M_Product_ID)) THEN
		IF (:old.QtyReserved <> 0) THEN
			RAISE_APPLICATION_ERROR(-20200, 'Changed Product had Reservation=' || :old.QtyReserved);
		ELSIF (:old.QtyDelivered <> 0) THEN
			RAISE_APPLICATION_ERROR(-20200, 'Changed Product had Delieveries=' || :old.QtyDelivered);
		ELSIF (:old.QtyInvoiced <> 0) THEN
			RAISE_APPLICATION_ERROR(-20200, 'Changed Product was Invoiced=' || :old.QtyInvoiced);
		END IF;
	END IF;

	--	Get ID
	IF (UPDATING OR INSERTING) THEN
		v_ID := :new.C_Order_ID;
	ELSE
		v_ID := :old.C_Order_ID;
	END IF;

	--	ReadOnly Check
	SELECT	COUNT(*)
	  INTO	v_RO
	FROM	C_Order
	WHERE	C_Order_ID=v_ID
	  AND	(Processed='Y' OR Posted='Y');
	IF (v_RO > 0) THEN
		IF (INSERTING OR DELETING) THEN	  --   ?? updated >> posting invalid
			RAISE_APPLICATION_ERROR(-20501, 'Document already processed/posted - invalid opreation');
		ELSIF (:new.LineNetAmt+:new.ChargeAmt<>:old.LineNetAmt+:old.ChargeAmt) THEN
			RAISE_APPLICATION_ERROR(-20501, 'Document already processed/posted - invalid charge operation');
		END IF;
	END IF;

	/**
	 *	Round Base
	 */
	Prec := 2;
	IF (INSERTING OR UPDATING) THEN
		:new.LineNetAmt := ROUND(:new.LineNetAmt, Prec);
		:new.FreightAmt := ROUND(:new.FreightAmt, Prec);
		:new.ChargeAmt := ROUND(:new.ChargeAmt, Prec);
	END IF;

	/**************************************************************************
	 *	Calculate Tax, etc.
	 */
	--	Subtract old Amount
	IF (DELETING OR UPDATING) THEN
		--	Get old Tax Info
		SELECT	SUM(TaxBaseAmt)
		  INTO	oldTaxBaseAmt
		FROM	C_OrderTax
		WHERE	C_Order_ID=:old.C_Order_ID
		  AND	C_Tax_ID=:old.C_Tax_ID;
	--	DBMS_OUTPUT.PUT_LINE('Old TaxBaseAmt=' || oldTaxBaseAmt);
		--
		oldLine := NVL(:old.LineNetAmt,0) + NVL(:old.FreightAmt,0) + NVL(:old.ChargeAmt,0);
		IF (oldTaxBaseAmt IS NOT NULL) THEN
		--	DBMS_OUTPUT.PUT_LINE('Update NewLineAmt -= ' || oldLine);
			UPDATE	C_OrderTax
			  SET	TaxBaseAmt = TaxBaseAmt - oldLine
			WHERE	C_Order_ID=:old.C_Order_ID
			  AND	C_Tax_ID=:old.C_Tax_ID;
		END IF;

		--	Calculate old tax with 2 digits precision
		UPDATE C_OrderTax ot
		  SET	TaxAmt = (SELECT ROUND(ot.TaxBaseAmt * t.Rate / 100, Prec) 
						FROM C_Tax t WHERE ot.C_Tax_ID=t.C_Tax_ID),
				Updated = SysDate,
				UpdatedBy = 0
		WHERE	C_Order_ID=:old.C_Order_ID
		  AND	C_Tax_ID=:old.C_Tax_ID;
	END IF;

	--	Add new Amount
	IF (INSERTING OR UPDATING) THEN
		--	Get new Tax Info
		SELECT	SUM(TaxBaseAmt)
		  INTO	newTaxBaseAmt
		FROM	C_OrderTax
		WHERE	C_Order_ID=:new.C_Order_ID
		  AND	C_Tax_ID=:new.C_Tax_ID;
	--	DBMS_OUTPUT.PUT_LINE('New TaxBaseAmt=' || newTaxBaseAmt);
		--
		newLine := NVL(:new.LineNetAmt,0) + NVL(:new.FreightAmt,0) + NVL(:new.ChargeAmt,0);
		IF (newTaxBaseAmt IS NULL) THEN
		--	DBMS_OUTPUT.PUT_LINE('Insert NewLineAmt = ' || newLine);
			INSERT INTO C_OrderTax
				(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
				C_Order_ID, C_Tax_ID, TaxBaseAmt, TaxAmt)
			VALUES
				(:new.AD_Client_ID, :new.AD_Org_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy,
				:new.C_Order_ID, :new.C_Tax_ID, newLine, 0);
		ELSE
		--	DBMS_OUTPUT.PUT_LINE('Update NewLineAmt += ' || newLine);
			UPDATE	C_OrderTax
			  SET	TaxBaseAmt = TaxBaseAmt + newLine
			WHERE	C_Order_ID=:new.C_Order_ID
			  AND	C_Tax_ID=:new.C_Tax_ID;
		END IF;

		--	Calculate new tax with 2 digits precision
		UPDATE C_OrderTax ot
		  SET	TaxAmt = (SELECT ROUND(ot.TaxBaseAmt * t.Rate / 100, Prec) 
						FROM C_Tax t WHERE ot.C_Tax_ID=t.C_Tax_ID),
				Updated = SysDate,
				UpdatedBy = :new.UpdatedBy
		WHERE	C_Order_ID=:new.C_Order_ID
		  AND	C_Tax_ID=:new.C_Tax_ID;
	END IF;

	
	--	Get Total Tax Amt
	IF (INSERTING OR UPDATING) THEN
		SELECT	SUM(TaxAmt)
		  INTO	taxAmt
		FROM	C_OrderTax
		WHERE	C_Order_ID=:new.C_Order_ID;
	--	DBMS_OUTPUT.PUT_LINE('TaxAmt = ' || taxAmt);
		--	Update Header
		UPDATE	C_Order
		  SET	TotalLines = TotalLines - oldLine + newLine,
				GrandTotal = TotalLines - oldLine + newLine + NVL(taxAmt, 0)
		--		Updated = SysDate	--	Don't update as otherwise it does not save changes
		WHERE	C_Order_ID = :new.C_Order_ID;
	ELSE	--	DELETING
		SELECT	SUM(TaxAmt)
		  INTO	taxAmt
		FROM	C_OrderTax
		WHERE	C_Order_ID=:old.C_Order_ID;
	--	DBMS_OUTPUT.PUT_LINE('TaxAmt = ' || taxAmt);
		--	Update Header
		UPDATE	C_Order
		  SET	TotalLines = TotalLines - oldLine + newLine,
				GrandTotal = TotalLines - oldLine + newLine + NVL(taxAmt, 0)
		--		Updated = SysDate	--	Don't update as otherwise it does not save changes
		WHERE	C_Order_ID=:old.C_Order_ID;
	END IF;
	
END C_OrderLine_Trg;
/