Solved

Looping a call to a Modify__()

  • 11 January 2024
  • 5 replies
  • 73 views
M
Rank
Userlevel 1
Badge +5

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)…

 

PRD_08_LOG after executing the script

… 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?

icon

Best answer by Tomas Ruderfelt 11 January 2024, 10:47

View original

5 replies

T
Rank
Userlevel 7
Badge +19

You have used variable p3_ as parameter attr_ in to the Modify__ method.

In the logic the Modify__ method calls that parameter is cleared out (if you send in valid attributes). It may also add other attributes you will get in return from the updated record, maybe not in this case but you are never sure without checking.

So after the first call p3_ is empty and since you set a value to it before the loop it has no value for 2nd loop etc.

So you need to set p3_ inside the loop, before you call Modify__.

You also need to clear the variable before adding things to it so you are 100% sure it only contains the attributes you want to change.

 

       Client_SYS.Clear_Attr(p3_);
       Client_SYS.Add_To_Attr('SAFETY_LEAD_TIME', 5, p3_);
       IFSAPP.INVENTORY_PART_PLANNING_API.MODIFY__( p0_ , p1_ , p2_ , p3_ , p4_ );

 

MatthewConk
Rank
Badge +4

See if something like this works:

 

DECLARE

  info_ VARCHAR2(32000);
  attr_ VARCHAR2(32000);

  CURSOR get_data_ IS
    SELECT ip.part_no,
           ip.objid,
           ip.objversion
      FROM prd_08 p
     INNER JOIN inventory_part_planning ip
        ON p.part_no = ip.part_no
     WHERE p.contract = 'ABCD'
           AND p.part_no IN ('[a_bunch_of_Part_Numbers]')
     ORDER BY ip.part_no;

BEGIN

  client_sys.add_to_attr(name_ => 'SAFETY_LEAD_TIME', value_ => 5, attr_ => attr_);

  FOR rec_ IN get_data_
  LOOP
    info_ := null;

    inventory_part_planning_api.modify__(info_       => info_,
                                         objid_      => rec_.objid,
                                         objversion_ => rec_.objversion,
                                         attr_       => attr_,
                                         action_     => 'DO');

    COMMIT;
  END LOOP;
END;

 

M
Rank
Userlevel 1
Badge +5

Thanks guys, you really hit the nail on the head with your feedback. 

 

Out of curiosity (and since I see how @MatthewConk has suggested slight differences in code) I have this question. Is semantically equivalent to create the attr_ value using just as string “as in my case” or the add_to_attr function?

Do you see cases where add_to_attr not only is the “best practice” but delivers results than just crafting our attribute by constructing a string formatted the way it is expected by just assigning a string literal?

I really appreciate all the feedback, this community is VERY helpful!

MatthewConk
Rank
Badge +4

@msurasky-price There is no real difference between your approach and using the add_to_attr method, so this should not have an impact on your code. 

PROCEDURE Add_To_Attr (
   name_  IN     VARCHAR2,
   value_ IN     VARCHAR2,
   attr_  IN OUT NOCOPY VARCHAR2 )
IS
BEGIN
   attr_ := attr_||name_||field_separator_||value_||record_separator_;
END Add_To_Attr;

I do it this way because I like cleaner code and I think it is easier to maintain this way. For one value it would not matter, but if you had twenty it could become harder to maintain/update. I want to make sure when I come back to it six or twelve months later it is easy for me to read, understand, and update. 

It matters even more if I have additional API calls to pull the value. This is a bad example because we would not need Payment Terms in Inventory Part Planning, but I think you will get what I am doing here:

  FOR rec_ IN get_data_
  LOOP
    info_ := null;
    client_sys.clear_attr(attr_ => attr_);

    client_sys.add_to_attr(name_ => 'SAFETY_LEAD_TIME', value_ => 5, attr_ => attr_);
    client_sys.add_to_attr(name_  => 'PAYMENT_TERMS',
                           value_ => identity_invoice_info_api.get_pay_term_id(company_   => rec_.company,
                                                                               identity_  => rec_.customer_id,
                                                                               party_type_  => 'CUSTOMER'),
                           attr_  => attr_);

    inventory_part_planning_api.modify__(info_       => info_,
                                         objid_      => rec_.objid,
                                         objversion_ => rec_.objversion,
                                         attr_       => attr_,
                                         action_     => 'DO');

    COMMIT;
  END LOOP;

No real difference in what is happening, just a different approach. 

M
Rank
Userlevel 1
Badge +5

Your explanation and example is super-clear @MatthewConk. Thanks for that!

Reply


Terms & ConditionsCookie settings