c_payment_allocated.sql

Java写的ERP系统

CREATE OR REPLACE FUNCTION C_Payment_Allocated
(
	p_C_Payment_ID	IN	NUMBER,
	p_C_Currency_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+CRM
 * Copyright (C) 1999-2001 Jorg Janke, ComPiere, Inc. All Rights Reserved.
 *************************************************************************
 * $Id: C_Payment_Allocated.sql,v 1.3 2003/03/17 20:32:25 jjanke Exp $
 ***
 * Title:	Calculate Allocated Payment Amount in Payment Currency
 * Description:
 *	Use
 *	SET IsAllocated = DECODE(C_Payment_Allocated(C_Payment_ID, C_Currency_ID), PayAmt, 'Y', 'N')
 *	similar to C_Invoice_Paid
 ************************************************************************/
AS
	v_AllocatedAmt		NUMBER := 0;
	CURSOR	Cur_Alloc	IS
		SELECT	AD_Client_ID, AD_Org_ID, Amount, C_Currency_ID, DateTrx
		FROM		C_Allocation
		WHERE	C_Payment_ID = p_C_Payment_ID
		  AND	C_Invoice_ID IS NOT NULL;
BEGIN
	--	Calculate Allocated Amount
	FOR a IN Cur_Alloc LOOP
		v_AllocatedAmt := v_AllocatedAmt
			+ C_Currency_Convert(a.Amount, a.C_Currency_ID, p_C_Currency_ID, a.DateTrx, null, a.AD_Client_ID, a.AD_Org_ID);
	END LOOP;
	--	Round to penny
	v_AllocatedAmt := ROUND(NVL(v_AllocatedAmt,0), 2);
	RETURN	v_AllocatedAmt;
END C_Payment_Allocated;
/