Issue with PLSQL

  • 4 August 2021
  • 9 replies
  • 389 views
A
Rank
Userlevel 4
Badge +7

Hi Folks,

I am working on a problem to modify delivery dates for all the PO lines that have same doc ID using below script-

 

declare
info_ varchar2(4000);
n1 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
OPEN c1;  
   FETCH c1  INTO n1;
CLOSE c1;    


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

end;

 

now the only problem is that I get the error saying the ORA-06550: line 9, column 241:
PLS-00456: item 'N1' is not a cursor.

 

I need to know why am I getting this error this should not be an issue.

I already tried using n1 as number in DECLARATION but that didnt solve the issue either.

A little guidance or help would be much appreciated.

 

 

This topic has been closed for comments

9 replies

Chamika Dilhani
Rank
Userlevel 6
Badge +7

Hi, 

You have declared cursor C1. 

So use C1 instead of n1.

For i1 in C1 loop

~Cheers - Chamika
A
Rank
Userlevel 7

Hi Folks,

I am working on a problem to modify delivery dates for all the PO lines that have same doc ID using below script-

 

declare
info_ varchar2(4000);
n1 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
OPEN c1;  
   FETCH c1  INTO n1;
CLOSE c1;    


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

end;

 

now the only problem is that I get the error saying the ORA-06550: line 9, column 241:
PLS-00456: item 'N1' is not a cursor.

 

I need to know why am I getting this error this should not be an issue.

I already tried using n1 as number in DECLARATION but that didnt solve the issue either.

A little guidance or help would be much appreciated.

 

 

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;

Sangeetha Jayamaha
Rank
Userlevel 5
Badge +7

@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

Sangeetha Jayamaha
Sangeetha Jayamaha
Rank
Userlevel 5
Badge +7

@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.

/

Sangeetha Jayamaha
A
Rank
Userlevel 4
Badge +7

@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

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

Sangeetha Jayamaha
Rank
Userlevel 5
Badge +7

@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.

/

 

 

Sangeetha Jayamaha
L
Rank
Userlevel 3
Badge +6

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;

A
Rank
Userlevel 4
Badge +7

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

Sangeetha Jayamaha
Rank
Userlevel 5
Badge +7

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;

 

 

Sangeetha Jayamaha
Terms & ConditionsCookie settings