c_invoice_paid.sql

Java写的ERP系统

CREATE OR REPLACE FUNCTION C_Invoice_Paid
(
	p_C_Invoice_ID		IN	NUMBER,
	p_C_Currency_ID	IN	NUMBER,
	p_Multiplier		IN	NUMBER	-- DEFAULT 1
)
RETURN NUMBER
/*************************************************************************
 * 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_Invoice_Paid.sql,v 1.7 2003/03/17 20:32:25 jjanke Exp $
 ***
 * Title:	Calculate Paid/Allocated amount in Currency
 * Description:
 *	Add up for for C_Invoice_ID
 *  all allocation amounts  converted to C_Currency_ID
 *	round it to the nearest cent
 *	and adjust for CreditMemos with the multiplier (-1, 1)
 *
 *	As C_Invoice_Paid is mutating, use:
 *		IsPaid = DECODE(C_Invoice_Paid(C_Invoice_ID, C_Currency_ID, Multiplier), 
 *			GrandTotal, 'Y', 'N')
 *	
 ************************************************************************/
AS
	v_Multiplier		NUMBER := 1;
	v_PaymentAmt		NUMBER := 0;
	CURSOR	Cur_Alloc	IS
		SELECT	AD_Client_ID, AD_Org_ID, Amount, DisCountAmt, WriteOffAmt, C_Currency_ID, DateTrx
		FROM		C_Allocation
		WHERE	C_Invoice_ID = p_C_Invoice_ID;
BEGIN
	--	Default
	IF (p_Multiplier IS NOT NULL) THEN
		v_Multiplier := p_Multiplier;
	END IF;
	--	Calculate Allocated Amount
	FOR a IN Cur_Alloc LOOP
		v_PaymentAmt := v_PaymentAmt
			+ C_Currency_Convert(a.Amount + a.DisCountAmt + a.WriteOffAmt,
				a.C_Currency_ID, p_C_Currency_ID, a.DateTrx, null, a.AD_Client_ID, a.AD_Org_ID);
	END LOOP;
	--
	RETURN	ROUND(NVL(v_PaymentAmt,0), 2) * v_Multiplier;
END C_Invoice_Paid;
/