Can anyone suggest how I can use ole2 to perform an excel function (e.g. PMT()), from oracle forms and get back the result??
1
-
which version of Forms are you using? This sort of integration was pretty straightforward in the older client/server implementation (4.5 - 6.5) but trickier in the web-delivered versions.APC– APC2010-09-29 03:30:19 +00:00Commented Sep 29, 2010 at 3:30
Add a comment
|
2 Answers
I created PL/SQL functions that will calculate the PMT function per the formula that is in MS Excel.
This first function doesn't factor in the future value, the second does.
/* No future value factor */
FUNCTION pmt (APR IN NUMBER /* Rate (APR) */,
pv IN NUMBER /* Loan amount */,
nper IN NUMBER /* number of payments */
) RETURN NUMBER IS /* periodic payment amount */
BEGIN
RETURN (APR / (1 - power((1 + APR),-nper)) * pv );
END pmt;
/* With future value factor */
FUNCTION pmt (APR IN NUMBER, /* Rate (APR) */
nper IN NUMBER, /* Number of payments */
pv IN NUMBER, /* present value */
fv IN NUMBER /*future value */
) RETURN NUMBER IS /* periodic payment amount */
calcpmt NUMBER := 0;
powercalc NUMBER:=0;
BEGIN
powercalc:= POWER(1+APR, nper);
RETURN APR / (powercalc - 1) * -(pv * powercalc + fv);
END;
Comments
I would just recreate the PMT function in PL/SQL, the native language of forms. E.g. based on http://forums.contractoruk.com/technical/28716-calculate-apr-loan-repayment-using-pl-sql-java.html:
CREATE
FUNCTION pmt (rate IN NUMBER /* Rate (APR) */
,amt IN NUMBER /* Loan amount */
,payments IN NUMBER /* number of payments */
) RETURN NUMBER IS /* periodic payment amount */
BEGIN
RETURN (rate/12*amt*power((1+rate/12),payments))/(power((1+rate/12),payments)-1);
END pmt;
e.g.
BEGIN
DBMS_OUTPUT.put_line(
'PMT = $' || TRUNC( pmt(0.249, 5000, 11), 2)
);
END;
/
PMT = $513.07
