c_invoice_open.sql

Java写的ERP系统

CREATE OR REPLACE FUNCTION C_Invoice_Open
(
	p_C_Invoice_ID	IN	NUMBER
)
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_Open.sql,v 1.6 2003/03/17 20:32:24 jjanke Exp $
 ***
 * Title:	Calculate Open Item Amount in Invoice Currency 
 * Description:
 *	see C_Invoice_Paid
 * TODO: AP Invoices
 ************************************************************************/
AS
	v_Currency_ID		NUMBER(10);
	v_OpenAmt			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
	--	Get Currency
	BEGIN
		SELECT	C_Currency_ID, GrandTotal
		  INTO	v_Currency_ID, v_OpenAmt
		FROM		C_Invoice_v		--	corrected for CM
		WHERE	C_Invoice_ID = p_C_Invoice_ID;
	EXCEPTION	--	Invoice in draft form
		WHEN OTHERS THEN
			RETURN NULL;
	END;

	--	Calculate Allocated Amount
	FOR a IN Cur_Alloc LOOP
		v_OpenAmt := v_OpenAmt
			- C_Currency_Convert(a.Amount + a.DisCountAmt + a.WriteOffAmt,
				a.C_Currency_ID, v_Currency_ID, a.DateTrx, null, a.AD_Client_ID, a.AD_Org_ID);
	END LOOP;
	--	Ignore Rounding
	IF (v_OpenAmt BETWEEN -0.00999 AND 0.00999) THEN
		v_OpenAmt := 0;
	END IF;
	--	Round to penny
	v_OpenAmt := ROUND(NVL(v_OpenAmt,0), 2);
	RETURN	v_OpenAmt;
END C_Invoice_Open;
/