Hi All,
I have the following function which calculates and returns a figure.
What I am trying to do is to put an 'IF' statement in the function to
say if the returning value is > 28 then update a table in the
database. I still want the returning figure to be calculated but for
some reason I am getting the following error:
"report error:
ORA-14551: cannot perform a DML operation inside a query "
/*This function calculates a value and if the acknowledgment takes
longer than 28 days the vendor gets 2 penalty points and a
acknowledgement score of 0*/
FUNCTION car_acknowledged_score
(p_vendor_id IN NUMBER
,p_month IN VARCHAR2
,p_year IN VARCHAR2)
RETURN NUMBER
IS
v_acknowledged_result NUMBER;
v_acknowledged_score NUMBER;
BEGIN
SELECT xxmel_apex_vendor_rating_pkg.car_acknowledged_result
(p_vendor_id, p_month, p_year)
INTO v_acknowledged_result
FROM dual;
IF v_acknowledged_result <= 4 THEN v_acknowledged_score := 6;
ELSIF v_acknowledged_result BETWEEN 4 AND 7 THEN
v_acknowledged_score := 4;
ELSIF v_acknowledged_result BETWEEN 7 AND 10 THEN
v_acknowledged_score := 2;
ELSIF v_acknowledged_result BETWEEN 10 AND 28 THEN
v_acknowledged_score := 0;
ELSIF v_acknowledged_result > 28 THEN v_acknowledged_score := 0;
--2 points deducted for being greater than 28
UPDATE xxmel_apex_vrd vrd
SET vrd.PENATLY_POINTS = (NVL(vrd.PENATLY_POINTS,0) + 2)
WHERE vrd.DATE_PERIOD_MONTH = p_month
AND vrd.DATE_PERIOD_YEAR = p_year
AND vrd.SUPPLIER_ID = p_vendor_id;
COMMIT;
END IF;
RETURN ROUND(v_acknowledged_score,1);
END car_acknowledged_score;
Any help would be great, thanks
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
