Subject Rounding, etc rapdirector 2009-01-11T06:36:07Z
Hi Friends!

I have a problem that I cannot solve. I have a procedure that takes
two input parameters, both represent amount of money according to one
order.
One of them represents the full amount, the other represents the
currently paid amount of money. I have to create invoice from the
orderitems based on the two input parameters. The method would be the
following: I create a quotient from the input parameters and each
item's gross value will be multiplied with this quotient. In this
case, summing the amount of multiplied values would be equal to the
currently paid amount. But this is not true. Here is the procedure.
Sorry for the hungarian naming, but the method would be seen from this
code snippet. How can I achieve, that summing the BRUTTO_ERTEK_ARANYOS
field would be equal to the input parameter BEF_OSSZEG?

The full amount of money is the TELJES_OSSZEG, the currently paid is
BEF_OSSZEG.

CREATE PROCEDURE SZOLG_ERTEK_FROM_OSSZEG(
FID INTEGER,
TELJES_OSSZEG INTEGER,
BEF_OSSZEG INTEGER)
RETURNS (
FSZ_ID INTEGER,
FOGLALAS_ID INTEGER,
SZOLG_ID INTEGER,
NEV VARCHAR(50),
VTSZ VARCHAR(20),
BRUTTO_AR NUMERIC(18,2),
NETTO_AR_ARANYOS NUMERIC(18,2),
BRUTTO_AR_ARANYOS NUMERIC(18,2),
MENNYISEG NUMERIC(10,2),
SZAZALEK NUMERIC(10,2),
BRUTTO_ERTEK NUMERIC(18,2),
NETTO_ERTEK_ARANYOS NUMERIC(18,2),
BRUTTO_ERTEK_ARANYOS NUMERIC(18,2),
ME CHAR(10),
PARAM_ID INTEGER,
SZOBA_ID INTEGER,
SZAMLAZVA NUMERIC(18,2),
SORSZAM SMALLINT,
MEGJEGYZES BLOB SUB_TYPE 1 SEGMENT SIZE 80)
AS
DECLARE VARIABLE Q NUMERIC(10,2);
BEGIN
Q=CAST(1.00*BEF_OSSZEG/TELJES_OSSZEG AS NUMERIC(10,2));

FOR SELECT
FSZ_ID,
FOGLALAS_ID,
SZOLG_ID,
NEV,
VTSZ,
BRUTTO_AR,
0,
CAST(1.00*BRUTTO_AR*:Q AS NUMERIC(18,2)),
MENNYISEG,
SZAZALEK,
ERTEK,
0,
CAST(1.00*ERTEK*:Q AS NUMERIC(18,2)),
ME,
PARAM_ID,
SZOBA_ID,
SZAMLAZVA,
SORSZAM,
MEGJEGYZES
FROM FOGLALAS_SZOLG WHERE FOGLALAS_ID=:FID INTO
:FSZ_ID,
:FOGLALAS_ID,
:SZOLG_ID,
:NEV,
:VTSZ,
:BRUTTO_AR,
:NETTO_AR_ARANYOS,
:BRUTTO_AR_ARANYOS,
:MENNYISEG,
:SZAZALEK,
:BRUTTO_ERTEK,
:NETTO_ERTEK_ARANYOS,
:BRUTTO_ERTEK_ARANYOS,
:ME,
:PARAM_ID,
:SZOBA_ID,
:SZAMLAZVA,
:SORSZAM,
:MEGJEGYZES DO
BEGIN
NETTO_AR_ARANYOS=CAST(BRUTTO_AR_ARANYOS/1.20 AS NUMERIC(18,2));
NETTO_ERTEK_ARANYOS=CAST(BRUTTO_ERTEK_ARANYOS/1.20 AS NUMERIC(18,2));
SUSPEND;
END
END

TIA:Alex :-)