c_cashline_trg.sql

Java写的ERP系统

CREATE OR REPLACE TRIGGER C_CashLine_Trg
BEFORE DELETE OR INSERT OR UPDATE
	ON C_CashLine
FOR EACH ROW
DECLARE
/*************************************************************************
 * 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+CPM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_CashLine_Trg.sql,v 1.18 2003/03/17 20:32:24 jjanke Exp $
 ***
 * Title:	 Manage Allocations
 * Description:
 *		Prevent changing invoice lines
 *		When Inserting and we have an invoice
 *			Create Allocation Line (Trigger updates C_BPartner.SO_CreditUsed)
 *		Update C_Currency_ID of line
 *		Update Cash Balance (Header)
 ****
	UPDATE C_CashLine cl SET C_Currency_ID = (SELECT C_Currency_ID FROM C_Invoice i WHERE i.C_Invoice_ID=cl.C_Invoice_ID) WHERE C_Currency_ID IS NULL AND C_Invoice_ID IS NOT NULL;
	UPDATE C_CashLine cl SET C_Currency_ID = (SELECT C_Currency_ID FROM C_BankAccount b WHERE b.C_BankAccount_ID=cl.C_BankAccount_ID) WHERE C_Currency_ID IS NULL AND C_BankAccount_ID IS NOT NULL;
	UPDATE C_CashLine cl SET C_Currency_ID = (SELECT b.C_Currency_ID FROM C_Cash c, C_CashBook b WHERE c.C_Cash_ID=cl.C_Cash_ID AND c.C_CashBook_ID=b.C_CashBook_ID) WHERE C_Currency_ID IS NULL;
 ************************************************************************/

	v_NextNo				NUMBER(10);
	v_BPartner_ID			NUMBER(10) := NULL;
	v_Currency_ID			NUMBER(10) := NULL;
	--	CashBook
	v_CB_Currency_ID		NUMBER(10);
	v_CB_Date				DATE;
	--
	v_Difference			NUMBER := 0;
	v_Client_ID			NUMBER(10);
	v_Org_ID				NUMBER(10);
	v_Cash_ID				NUMBER(10);
	v_Invoice_ID			NUMBER(10) := NULL;
	v_BankAccount_ID		NUMBER(10) := NULL;
	v_RO					NUMBER;
	--
	v_Trace					VARCHAR2(2000) := 'CashLine: ';
BEGIN
	/**	Cannot change generated invoice data		*/
	IF (DELETING 
			AND :old.C_Invoice_ID IS NOT NULL 
			AND :old.IsGenerated = 'Y') THEN
		RAISE_APPLICATION_ERROR(-20102, 'Cannot delete line with generated Invoice');
	END IF;
	IF (UPDATING
			AND :old.C_Invoice_ID IS NOT NULL 
			AND :old.IsGenerated='Y' AND :new.IsGenerated='Y') THEN
		RAISE_APPLICATION_ERROR(-20102, 'Cannot change line with generated Invoice');
	END IF;

	/**
	 *	Fill Variables
	 */
	IF (DELETING OR UPDATING) THEN
		v_Difference := v_Difference - :old.Amount;
		v_Client_ID := :old.AD_Client_ID;
		v_Org_ID := :old.AD_Org_ID;
		v_Cash_ID := :old.C_Cash_ID;
		v_Invoice_ID := :old.C_Invoice_ID;
		v_BankAccount_ID := :old.C_BankAccount_ID;
	END IF;
	IF (UPDATING OR INSERTING) THEN
		v_Difference := v_Difference + :new.Amount;
		v_Client_ID := :new.AD_Client_ID;
		v_Org_ID := :new.AD_Org_ID;
		v_Cash_ID := :new.C_Cash_ID;
		v_Invoice_ID := :new.C_Invoice_ID;
		v_BankAccount_ID := :new.C_BankAccount_ID;
	END IF;

	--	ReadOnly Check
	SELECT	COUNT(*)
	  INTO	v_RO
	FROM		C_Cash
	WHERE	C_Cash_ID=v_Cash_ID
	  AND	Processed='Y';
	IF (v_RO > 0) THEN
		RAISE_APPLICATION_ERROR(-20501, 'Document processed/posted');
	END IF;
	


	--	Get CashBook Currenncy, Date
	SELECT	cb.C_Currency_ID, c.DateAcct 
	  INTO	v_CB_Currency_ID, v_CB_Date
	FROM	C_CashBook cb, C_Cash c
	WHERE	cb.C_CashBook_ID=c.C_CashBook_ID
	  AND	c.C_Cash_ID=v_Cash_ID;
	--	Get BPartner_ID and Invoice Currency
	IF (v_Invoice_ID IS NOT NULL) THEN
		SELECT	C_BPartner_ID, C_Currency_ID
		  INTO	v_BPartner_ID, v_Currency_ID
		FROM	C_Invoice
		WHERE	C_Invoice_ID = v_Invoice_ID;
	END IF;
	--	Get BankAccount Currency
	IF (v_BankAccount_ID IS NOT NULL) THEN
		SELECT	C_Currency_ID 
		  INTO	v_Currency_ID
		FROM	C_BankAccount
		WHERE	C_BankAccount_ID=v_BankAccount_ID;
	END IF;
	--	Assume CashBook Currency for Charge
	IF (v_Currency_ID IS NULL) THEN
		v_Currency_ID := v_CB_Currency_ID;
	END IF;

--	v_Trace := v_Trace || ' Difference=' || v_Difference || ', Cash_ID=' || v_Cash_ID || ', Invoice_ID=' || v_Invoice_ID || ', Currency_ID=' || v_Currency_ID	|| ', CB_Currency_ID=' || v_CB_Currency_ID;
	/**
	 *	Update CashBook Line
	 */
	IF (NOT DELETING) THEN
		:new.C_Currency_ID := v_Currency_ID;
	END IF;

	/**
	 *	Update CashBook Header 
	 */
	--	Convert to CashBook Currency
	IF (v_Currency_ID <> v_CB_Currency_ID) THEN
		v_Difference := C_Currency_Convert(v_Difference, v_Currency_ID, v_CB_Currency_ID, v_CB_Date, null, v_Client_ID, v_Org_ID);
	END IF;
	--	Update header
	UPDATE	C_Cash
	  SET	StatementDifference = StatementDifference + v_Difference,
			EndingBalance = EndingBalance + v_Difference
	WHERE	C_Cash_ID = v_Cash_ID;

--	v_Trace := v_Trace || ' > ' || SQL%ROWCOUNT || ' < Difference=' || v_Difference;
--	INSERT INTO AD_TRACE (What) VALUES (v_Trace);

END C_CashLine_Trg;
/