c_invoiceline_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER C_InvoiceLine_Trg
AFTER INSERT OR DELETE OR UPDATE 
	OF QtyInvoiced, LineNetAmt, ChargeAmt, C_Tax_ID
	ON C_InvoiceLine
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_InvoiceLine_Trg.sql,v 1.8 2003/01/27 06:22:12 jjanke Exp $
 ***
 * Title:	Insert Invoice Line
 * Description:
 *		- Check ReadOnly
 *		- Calculate Taxes and Totals
 ************************************************************************/
DECLARE
	v_newTaxBaseAmt 	NUMBER;
	v_oldTaxBaseAmt 	NUMBER;
	v_oldLine			NUMBER := 0;
	v_newLine			NUMBER := 0;
	v_taxAmt			NUMBER := 0;
	v_HasTaxLine		BOOLEAN := FALSE;
	v_isPosted			CHAR(1);
	v_ID				NUMBER;
	v_RO				NUMBER;
	v_Prec				NUMBER := 2;

BEGIN
	--	Differnce, ID
	IF (UPDATING OR INSERTING) THEN
		v_ID := :new.C_Invoice_ID;
	ELSE
		v_ID := :old.C_Invoice_ID;
	END IF;

	--	ReadOnly Check
	SELECT 	COUNT(*)
	  INTO	v_RO
	FROM	C_Invoice
	WHERE	C_Invoice_ID=v_ID
	  AND	Processed='Y';
	IF (v_RO > 0) THEN
		IF (INSERTING OR DELETING) THEN
			RAISE_APPLICATION_ERROR(-20501, 'Document processed/posted');
		ELSIF (:new.LineNetAmt+:new.ChargeAmt<>:old.LineNetAmt+:old.ChargeAmt) THEN
			RAISE_APPLICATION_ERROR(-20501, 'Document processed/posted');
	   	END IF;
	END IF;

	--	Only manual entries
	IF ((INSERTING OR UPDATING) AND :new.CreatedBy = 0)	THEN
		RETURN;
	END IF;


	/**
	 *	Round Base - Should do Rounding here
	 *
	v_Prec := 2;
	IF (INSERTING OR UPDATING) THEN
		:new.LineNetAmt := ROUND(:new.LineNetAmt, v_Prec);
		:new.ChargeAmt := ROUND(:new.ChargeAmt, v_Prec);
	END IF;
	*/

	--	Subtract old Amount
	IF (DELETING OR UPDATING) THEN
		--	Get old Tax Info
		SELECT 	SUM(TaxBaseAmt)
		  INTO	v_oldTaxBaseAmt
		FROM 	C_InvoiceTax
		WHERE	C_Invoice_ID = :old.C_Invoice_ID
		  AND	C_Tax_ID = :old.C_Tax_ID;
		DBMS_OUTPUT.PUT_LINE('Old TaxBaseAmt=' || v_oldTaxBaseAmt);
		--
		v_oldLine := NVL(:old.LineNetAmt,0) + NVL(:old.ChargeAmt,0);
		IF (v_oldTaxBaseAmt IS NOT NULL) THEN
			DBMS_OUTPUT.PUT_LINE('Update NewLineAmt -= ' || v_oldLine);
			UPDATE 	C_InvoiceTax
			  SET	TaxBaseAmt = TaxBaseAmt - v_oldLine
			WHERE	C_Invoice_ID = :old.C_Invoice_ID
	  		  AND	C_Tax_ID = :old.C_Tax_ID;
		END IF;
		--	Calculate old tax with 2 digits precision
		UPDATE C_InvoiceTax ot
		  SET	TaxAmt = (SELECT ROUND(ot.TaxBaseAmt * t.Rate / 100, 2) 
  						FROM C_Tax t WHERE ot.C_Tax_ID=t.C_Tax_ID),
   				Updated = SysDate,
				UpdatedBy = 0
		WHERE	C_Invoice_ID = :old.C_Invoice_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	v_newTaxBaseAmt
		FROM 	C_InvoiceTax
		WHERE	C_Invoice_ID = :new.C_Invoice_ID
		  AND	C_Tax_ID = :new.C_Tax_ID;
		DBMS_OUTPUT.PUT_LINE('New TaxBaseAmt=' || v_newTaxBaseAmt);
		--
		v_newLine := NVL(:new.LineNetAmt,0) + NVL(:new.ChargeAmt,0);
		IF (v_newTaxBaseAmt IS NULL) THEN
			IF (:new.C_Tax_ID IS NOT NULL) THEN		--	comment line has tax == null
				DBMS_OUTPUT.PUT_LINE('Insert NewLineAmt = ' || v_newLine);
				INSERT INTO C_InvoiceTax
					(AD_Client_ID, AD_Org_ID, IsActive, Created, CreatedBy, Updated, UpdatedBy,
					C_Invoice_ID, C_Tax_ID, TaxBaseAmt, TaxAmt)
				VALUES
					(:new.AD_Client_ID, :new.AD_Org_ID, 'Y', SysDate, :new.UpdatedBy, SysDate, :new.UpdatedBy,
					:new.C_Invoice_ID, :new.C_Tax_ID, v_newLine, 0);
	 		END IF;
		ELSE
			DBMS_OUTPUT.PUT_LINE('Update NewLineAmt += ' || v_newLine);
			UPDATE 	C_InvoiceTax
			  SET	TaxBaseAmt = TaxBaseAmt + v_newLine
			WHERE	C_Invoice_ID=:new.C_Invoice_ID
	  		  AND	C_Tax_ID=:new.C_Tax_ID;
		END IF;
		--	Calculate new tax with 2 digits precision
		UPDATE C_InvoiceTax ot
		  SET	TaxAmt = (SELECT ROUND(ot.TaxBaseAmt * t.Rate / 100, 2) 
		  				FROM C_Tax t WHERE ot.C_Tax_ID=t.C_Tax_ID),
	   			Updated = SysDate,
				UpdatedBy = :new.UpdatedBy
		WHERE	C_Invoice_ID=:new.C_Invoice_ID
		  AND	C_Tax_ID=:new.C_Tax_ID;
	END IF;
	
	--	Get Total Tax
	IF (INSERTING OR UPDATING) THEN
		SELECT 	SUM(TaxAmt)		--	might be null
		  INTO	v_taxAmt
		FROM	C_InvoiceTax
		WHERE	C_Invoice_ID = :new.C_Invoice_ID;
		DBMS_OUTPUT.PUT_LINE('TaxAmt = ' || v_taxAmt);

		--	Update Header
		UPDATE 	C_Invoice
		  SET	TotalLines = TotalLines - v_oldLine + v_newLine,
				GrandTotal = TotalLines - v_oldLine + v_newLine + NVL(v_taxAmt, 0)
		--		Updated = SysDate	--	Don't update as otherwise it does not save changes
		WHERE	C_Invoice_ID = :new.C_Invoice_ID;
	ELSE	--	DELETING
		SELECT 	SUM(TaxAmt)		--	might be null
		  INTO	v_taxAmt
		FROM	C_InvoiceTax
		WHERE	C_Invoice_ID = :old.C_Invoice_ID;
		DBMS_OUTPUT.PUT_LINE('TaxAmt = ' || v_taxAmt);

		--	Update Header
		UPDATE 	C_Invoice
		  SET	TotalLines = TotalLines - v_oldLine + v_newLine,
				GrandTotal = TotalLines - v_oldLine + v_newLine + NVL(v_taxAmt, 0)
		--		Updated = SysDate	--	Don't update as otherwise it does not save changes
		WHERE	C_Invoice_ID = :old.C_Invoice_ID;
	END IF;

END C_InvoiceLine_Trg;
/