Today I was trying to test a technique to run a MODIFY__() PROCEDURE that will affect a bunch of Inventory Parts in order to update the Safety Lead Time Value. The code I wrote was like this.
-- Use IFS API repeatedly to perform some action
-- (In this case updating Safety Lead Time on Inventory Parts)
DECLARE
-- p0 -> __lsResult
p0_ VARCHAR2(32000) := NULL;
-- p1 -> __sObjid
p1_ VARCHAR2(32000) := 'AAAWhqAAGAAHl+JAAO';
-- p2 -> __lsObjversion
p2_ VARCHAR2(32000) := '20240110114450';
-- p3 -> __lsAttr
p3_ VARCHAR2(32000) := 'SAFETY_LEAD_TIME'||chr(31)||'5'||chr(30);
-- p4 -> __sAction
p4_ VARCHAR2(32000) := 'DO';
sql_stmt VARCHAR2(1024);
tbl_count number;
BEGIN
FOR inventory_part_to_update_rec IN (
SELECT INVENTORY_PART_PLANNING.PART_NO, INVENTORY_PART_PLANNING.OBJID, INVENTORY_PART_PLANNING.OBJVERSION
FROM PRD_08 INNER JOIN INVENTORY_PART_PLANNING ON PRD_08.PART_NO = INVENTORY_PART_PLANNING.PART_NO
WHERE CONTRACT = 'ABCD'
AND PART_NO IN ([a_bunch_of_Part_Numbers]
ORDER BY INVENTORY_PART_PLANNING.PART_NO
) LOOP
p0_ := NULL;
p1_ := inventory_part_to_update_rec.OBJID;
p2_ := inventory_part_to_update_rec.OBJVERSION;
-- dbms_output.put_line ('Updating Inventory Part Safety Lead Time entry with part number: ' || inventory_part_to_update_rec.PART_NO ||', object id: ' || inventory_part_to_update_rec.OBJID || ' and object version: ' || inventory_part_to_update_rec.OBJVERSION);
INSERT INTO PRD_08_LOG (text) VALUES ('Updating Inventory Part Safety Lead Time entry with part number: ' || inventory_part_to_update_rec.PART_NO ||', object id: ' || p1_ || ' and object version: ' || p2_);
IFSAPP.INVENTORY_PART_PLANNING_API.MODIFY__( p0_ , p1_ , p2_ , p3_ , p4_ );
COMMIT;
END LOOP;
COMMIT;
END;
COMMIT;
The challenge is that, even though I see (in the PRD_08_LOG table) my code is looping for each one of the Part Numbers (I got one row in the PRD_08_LOG for every iteration)…
… only the first Part Number on the loop really gets modified. All the other Part Numbers in this loop remains with the value I had for Safety Lead Time before the loop was called.
I can’t understand why this script works when it’s not inside a LOOP (affecting only 1 part) but fails to update all the parts when in the loop (even though the log table assures me that the loop is running just fine).
Any ideas?
Best answer by Tomas Ruderfelt
View original