c_currency_conversion_test.sql

Java写的ERP系统

/**
 *	Test Currency Conversion
 *	(A)verage, (U)ser, (S)pot, (P)eriodEnd, (C)ompany
 */
DECLARE
	Amt			Number 		:= 100.00;
	CurFrom		VarChar(3) 	:= 'USD';
	CurFrom_ID	Number		:= 100;
	--
	CurTo		VarChar(3) 	:= 'DEM';
	CurTo_ID	Number		:= 103;
	ConvDate	Date 		:= SysDate;
	RateType	Char(1)		:= 'S';
	--
	Rate		Number;
	Amount		Number;
	Rounded		Number;

BEGIN
	--
	INSERT INTO C_CONVERSION_RATE
		(C_CONVERSION_RATE_ID,AD_CLIENT_ID,AD_ORG_ID,
		ISACTIVE,CREATED,CREATEDBY,UPDATED,UPDATEDBY,
		C_CURRENCY_ID,C_CURRENCY_ID_TO,
		VALIDFROM,VALIDTO,
		CONVERSIONRATETYPE,
		MULTIPLYRATE,DIVIDERATE)
	VALUES
		(123456,0,0,
		'Y',SysDate,0,SysDate,0,
		100,102,	--From USD to EUR
		SysDate-1, SysDate+1,
		'S', 1.1, 1/1.1);

	CurTo := 'EUR';
	CurTo_ID := 102;
	--	100 USD to EUR on 09-MAY-02 Type=S Amount=110 Rounded=110 Rate=1.1
	Rate := C_Currency_Rate (CurFrom_ID, CurTo_ID, ConvDate, RateType);
	Amount := C_Currency_Convert0 (Amt, CurFrom, CurTo, ConvDate, RateType);
	Rounded := C_Currency_Round (Amount, CurTo_ID, 'N');
	DBMS_OUTPUT.PUT_LINE(Amt || ' ' || CurFrom || ' to ' || CurTo
		|| ' on ' || ConvDate || ' Type=' || RateType
 	    || ' Amount=' || Amount || ' Rounded=' || Rounded
		|| ' Rate=' || Rate);

	CurTo := 'DEM';
	CurTo_ID := 103;
	--	100 USD to DEM on 09-MAY-02 Type=S Amount=215.1413 Rounded=215.14 Rate=2.151413  
	Rate := C_Currency_Rate (CurFrom_ID, CurTo_ID, ConvDate, RateType);
	Amount := C_Currency_Convert0 (Amt, CurFrom, CurTo, ConvDate, RateType);
	Rounded := C_Currency_Round (Amount, CurTo_ID, 'N');
	DBMS_OUTPUT.PUT_LINE(Amt || ' ' || CurFrom || ' to ' || CurTo
		|| ' on ' || ConvDate || ' Type=' || RateType
 	    || ' Amount=' || Amount || ' Rounded=' || Rounded
		|| ' Rate=' || Rate);


	CurFrom_ID := 116;
	CurTo_ID := 100;
--	ConvDate := TO_DATE('26-APR-2001');
	-- 100 116 to 100 on 09-MAY-02 Type=S Amount=64.7169 Rounded=64.72 Rate=.647169
	Rate := C_Currency_Rate (CurFrom_ID, CurTo_ID, ConvDate, RateType);
	Amount := C_Currency_Convert (Amt, CurFrom_ID, CurTo_ID, ConvDate, RateType);
	Rounded := C_Currency_Round (Amount, CurTo_ID, 'N');
	DBMS_OUTPUT.PUT_LINE(Amt || ' ' || CurFrom_ID || ' to ' || CurTo_ID
		|| ' on ' || ConvDate || ' Type=' || RateType
 	    || ' Amount=' || Amount || ' Rounded=' || Rounded
		|| ' Rate=' || Rate);

	--	Fini
	ROLLBACK;
END;
/