Hi, 

You have declared cursor C1. 

So use C1 instead of n1.

For i1 in C1 loop

Your cursor is c1 and not n1. Can you try something like this?

declare
info_ varchar2(4000);

CURSOR c1 IS
SELECT COUNT(ORDER_NO)  from PURCHASE_ORDER_LINE_ALL_CFV
WHERE  CF$_ID_DOCUMENT = &CF$_ID_DOCUMENT

AND ORDER_NO = &ORDER_NO;

BEGIN

FOR rec_ IN C1 LOOP
      ifsapp.purchase_order_line_api.Modify__(info_, &OBJID, &OBJVERSION, rec_,PLANNED_DELIVERY_DATE, 'DO');
END LOOP;

end;

@amrflynn 

For the parameters where the & is added at the beginning, do you want to give values at the time of execution or do you need to fetch those also from the table? 

/Sangeetha Jayamaha

@amrflynn 

I suggest you to use the following SQL.

DECLARE

info_ varchar2(4000);
 

CURSOR c1 IS
SELECT *  
FROM PURCHASE_ORDER_LINE_ALL_CFV
WHERE  CF$_ID_DOCUMENT = &CF$_ID_DOCUMENT
AND ORDER_NO                        = &ORDER_NO;

BEGIN

FOR rec_ IN c1 LOOP
   PLANNED_DELIVERY_DATE := &PLANNED_DELIVERY_DATE;
   ifsapp.purchase_order_line_api.Modify__(info_, rec_.OBJID, rec_.OBJVERSION, PLANNED_DELIVERY_DATE, 'DO');
   COMMIT;                                     
END LOOP;

END;

Please get back if this doesn’t suite your requirement. My understanding is that it’s not needed to get a count() for this.

Thank You,

Best Regards,

Sangeetha Jayamaha.

/

So The thing here is that I want to use the date of the row in which I have document ID and copy that date to all the records with that document ID. I am not completely sure If I am right. Also I used count so that I can use that to catch lets say 5 records with same document id and I can execute it 5 times to update the planned delivery date of all 5 lines and the date it shall choose will be from the row on which I did the right click operation

@amrflynn 

Anyway before performing this data repair, I recommend you to take a backup of the original data. 

Then you can try the SQL as  I have suggested above. There also,

The number of times the loop executes  =  Number of records that the cursor (C1) has fetched.

You might have to change the logic according to the requirement of fetching the PLANNED_DELIVERY_DATE. 

If you have only one record having the PLANNED_DELIVERY_DATE and if you need to copy that value to the rest of the records, the SQL needs to be changed since you need to retrieve that value also from the cursor.

In that case you can fetch that value using a different cursor and store that value in a date variable and use that variable in the Modify__() method. Otherwise if you give the value at the time of execution , the following should be fine. But you have to make sure that you give the value in the correct date format. 

FOR rec_ IN c1 LOOP
   rec_.PLANNED_DELIVERY_DATE := &PLANNED_DELIVERY_DATE;
   ifsapp.purchase_order_line_api.Modify__(info_, rec_.OBJID, rec_.OBJVERSION, rec_.PLANNED_DELIVERY_DATE, 'DO');
   COMMIT;                                     
END LOOP;

Also you can get help from PLSQL help documents found in internet to learn more about the most efficient ways of writing PLSQL statements.

eg: 

https://docs.oracle.com/cd/B19306_01/appdev.102/b14261.pdf

Thank You,

Best Regards,

Sangeetha Jayamaha.

/

Hi, 

Try the below code. I hope this code you are using on custom menu. 

declare
info_ varchar2(4000);
attr_ varchar2(200); 
Date_ date; 

CURSOR c1 IS
SELECT *  from PURCHASE_ORDER_LINE_ALL_CFV
WHERE  CF$_ID_DOCUMENT = &CF$_ID_DOCUMENT
AND ORDER_NO = &ORDER_NO;

rec_  c1%rowtype ; 

BEGIN
  
OPEN c1;  
   FETCH c1  INTO rec_;
CLOSE c1;  

   Date_  := &PLANNED_DELIVERY_DATE;
   
   client_sys.Clear_Attr (attr_) ; 
   client_sys.Add_To_Attr('PLANNED_DELIVERY_DATE',Date_ ,attr_); 
   
   ifsapp.purchase_order_line_api.Modify__(info_,rec_.objid  ,rec_.objversion,  ,'DO');
   
   COMMIT;                                   
end;

This is the most helpful solution as of now with only one complication that this is updating just the first line and when I try to add this solution inside a loop it is not updating

Please try the following,

DECLARE

info_       varchar2(4000);
attr_        varchar2(200); 
Date_      date; 

CURSOR c1 IS
SELECT *  
FROM PURCHASE_ORDER_LINE_ALL_CFV
WHERE  CF$_ID_DOCUMENT = &CF$_ID_DOCUMENT
AND        ORDER_NO                = &ORDER_NO;

BEGIN

     Date_  := &PLANNED_DELIVERY_DATE;
     
     FOR rec_ IN c1 LOOP
       client_sys.Clear_Attr (attr_) ; 
       client_sys.Add_To_Attr('PLANNED_DELIVERY_DATE', Date_ , attr_ ); 
       ifsapp.purchase_order_line_api.Modify__(info_ , rec_.objid  ,rec_.objversion,  attr_ ,'DO');
   
   COMMIT;    

END LOOP;
END;