Can some one help me in Cursor in sql. Need this urgent.

Sumit Prakash
Sumit Prakash used Ask the Experts™
on
Hello All,

I wrote small query where select query will fetch the status of one ORder . If the order status is not 1 it will update the status to another status. If not found it will handle the exception.
I am made one script.In the comment can you help in recitfiying the script asap. Need this script in this format only.



DECLARE
       
        v_REFERENCE_CODE varchar2(30);
        v_status_lkp varchar2(30);
        cursor ObsoleteSalesOrder is
               select a.REFERENCE_CODE,a.status_lkp from BASE_SALES_ORDER a,BASE_SALES_ORDER_LINE b where
                    a.SALES_ORDER_ID =b.SALES_ORDER_ID
                    and a.REFERENCE_CODE ='10132834';
       
BEGIN
open ObsoleteSalesOrder;
loop
fetch ObsoleteSalesOrder into v_REFERENCE_CODE,v_status_lkp;
exit when ObsoleteSalesOrder%notfound;
loop
if v_status_lkp !=1

--Do Below update
update base_sales_order set obsolete_flag = 1 where reference_code IN ('10132834');
update base_sales_order_line set obsolete_flag = 1 where sales_order_id
IN (select sales_order_id from base_sales_order where reference_code IN ('10132834'));
commit;
DBMS_OUTPUT.PUT_LINE('SalesOrder MArked it obsolete');

else if v_status = 1
DBMS_OUTPUT.PUT_LINE('SalesOrder is in obsolete');

else
DBMS_OUTPUT.PUT_LINE ('No sales ORder Found');

--Exception handling
end loop;
--Exception handling
else
end loop;
--Exception Handling
close ObsoleteSalesOrder;            
end;
/
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Commented:
You have two IF statements that are not closed with END IF  statements
DECLARE        
 v_reference_code VARCHAR2(30);
 v_status_lkp VARCHAR2(30);
 CURSOR ObsoleteSalesOrder is
        select a.reference_code,a.status_lkp from base_sales_order a,base_sales_order_line b
         WHERE a.sales_order_id =b.sales_order_id
           AND a.reference_code ='10132834';
 
 BEGIN
   OPEN ObsoleteSalesOrder;
     LOOP
       FETCH ObsoleteSalesOrder INTO v_REFERENCE_CODE,v_status_lkp;
       EXIT WHEN ObsoleteSalesOrder%notfound;
 
       IF v_status_lkp <>1 THEN
         /*Updation Part*/
         UPDATE base_sales_order
                 SET obsolete_flag = 1
          WHERE reference_code IN ('10132834');
         UPDATE base_sales_order_line
                 SET obsolete_flag = 1
          WHERE sales_order_id IN (SELECT sales_order_id
                                                          FROM base_sales_order
                                                        WHERE reference_code IN ('10132834'));
        /*Updation Part*/
       DBMS_OUTPUT.PUT_LINE('SalesOrder MArked it obsolete');
 
       ELSIF v_status = 1 THEN
         DBMS_OUTPUT.PUT_LINE('SalesOrder is in obsolete');
       ELSE
         DBMS_OUTPUT.PUT_LINE ('No sales ORder Found');
       END IF;  
     END LOOP;
  CLOSE ObsoleteSalesOrder;
    COMMIT;
 EXCEPTION
    WHEN OTHERS
      THEN
       ROLLBACK;
       CLOSE ObsoleteSalesOrder;
      DBMS_OUTPUT.PUT_LINE (Sqlcode||Sqlerrm||''||'Error At Line Number '||dbms_utility.format_error_backtrace);              
 END;
johnsoneSenior Oracle DBA

Commented:
You don't say what you want the error handling to do.  You probably only need one error handler at the end, but without knowing what it should do nobody knows.  So, I put in a generic error handler that ignores all errors, you should change that.

The query in the cursor has 2 tables in it, but one isn't used so that was removed.

Simplified the code by using a cursor for loop.  Realistically, if the code is only going to run for one value at a time, you don't need a loop at all.  A loop isn't required to go through only one record.

Used the selected value in the update statements rather than hard code it.

There is an else statement probably isn't needed (see comment in the code).  If it is there to find a condition where the given REFERENCE_CODE doesn't exist, that won't work as it will never get inside the loop if there is no record.

I am assuming that the DBMS_OUTPUT statement is there for testing.  Any references to DBMS_OUTPUT should be removed before putting this into production.
DECLARE 
  CURSOR obsoletesalesorder(v_ref_code) 
  IS 
    SELECT a.reference_code, 
           a.status_lkp 
    FROM   base_sales_order a 
    WHERE  a.reference_code = v_ref_code; 

BEGIN 
  FOR osboletesalesorder_cur IN obsoletesalesorder('10132834') 
  LOOP 
    IF osboletesalesorder_cur.status_lkp !=1 
      --Do Below update 
      UPDATE base_sales_order 
      SET    obsolete_flag = 1 
      WHERE  reference_code = osboletesalesorder_cur.reference_code; 
       
      UPDATE base_sales_order_line 
      SET    obsolete_flag = 1 
      WHERE  sales_order_id IN 
             ( 
                    SELECT sales_order_id 
                    FROM   base_sales_order 
                    WHERE  reference_code = osboletesalesorder_cur.reference_code); 
       
      COMMIT; 
      dbms_output.Put_line('SalesOrder MArked it obsolete'); 
    ELSE 
      IF osboletesalesorder_cur.status_lkp = 1 dbms_output.put_line('SalesOrder is in obsolete');
      ELSE 
        -- Not sure how you could possibly get here, about the only way is if STATUS_LKP is null 
        dbms_output.put_line ('No sales ORder Found'); 
      END IF; 
    END LOOP; 
  --Exception handling 
  WHEN OTHERS THEN 
    NULL; 
  END; 
/

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi Johnsone,

I appreciate your help as always. Can you help me again, If we can make this block for multiple SalesOrder and that should pass through as an argument.


DECLARE
  CURSOR obsoletesalesorder(v_ref_code)
  IS
    SELECT a.reference_code,
           a.status_lkp
    FROM   base_sales_order a
    WHERE  a.reference_code = v_ref_code;

BEGIN
  FOR osboletesalesorder_cur IN obsoletesalesorder('10132834')
  LOOP
    IF osboletesalesorder_cur.status_lkp !=1
      --Do Below update
      UPDATE base_sales_order
      SET    obsolete_flag = 1
      WHERE  reference_code = osboletesalesorder_cur.reference_code;
       
      UPDATE base_sales_order_line
      SET    obsolete_flag = 1
      WHERE  sales_order_id IN
             (
                    SELECT sales_order_id
                    FROM   base_sales_order
                    WHERE  reference_code = osboletesalesorder_cur.reference_code);
       
      COMMIT;
      dbms_output.Put_line('SalesOrder MArked it obsolete');
    ELSE
      IF osboletesalesorder_cur.status_lkp = 1 dbms_output.put_line('SalesOrder is in obsolete');
      ELSE
        dbms_output.put_line ('No sales ORder Found');
      END IF;
    END LOOP;
  --Exception handling
  WHEN OTHERS THEN
    NULL;
  END;
/

Author

Commented:
Can i use this below like this :-

 v_reference_code VARCHAR2(30);
 v_status_lkp VARCHAR2(30);
 
 CURSOR obsoletesalesorder  IS
    SELECT a.reference_code,
           a.status_lkp
    FROM   base_sales_order a
    WHERE  a.reference_code IN (&1);

BEGIN
  FOR osboletesalesorder_cur IN obsoletesalesorder
johnsoneSenior Oracle DBA

Commented:
You could do that, but it could only be run in SQL*Plus.  The & as a substitution character is a SQL*Plus feature (other tools may recognize this, but you couldn't use it outside a tool that recognized the substitution variable).  Save the code as a script and you can pass the parameter to the script through SQL*Plus.

Other way would be to create a temporary table (one time create), then load the ids into that temporary table and then the cursor joins to the temporary table just gets those ids.

Author

Commented:
Hello, Can you quickly check this .Error in End Loop:-

DECLARE
 v_reference_code VARCHAR2(30);
 v_status_lkp VARCHAR2(30);
  CURSOR obsoletesalesorder
  IS
    SELECT a.reference_code,
           a.status_lkp
    FROM   base_sales_order a
    WHERE  a.reference_code = (&1);

BEGIN
  FOR osboletesalesorder_cur IN obsoletesalesorder
  LOOP
    IF osboletesalesorder_cur.v_status_lkp !='1'
      --Do Below update
      --UPDATE base_sales_order
      --SET    obsolete_flag = 1
      --WHERE  reference_code = osboletesalesorder_cur.reference_code;
       
      --UPDATE base_sales_order_line
      --SET    obsolete_flag = 1
      --WHERE  sales_order_id IN
            -- (
                   -- SELECT sales_order_id
                    --FROM   base_sales_order
                    --WHERE  reference_code = osboletesalesorder_cur.reference_code);
       
     -- COMMIT;
     Then
      dbms_output.Put_line('SalesOrder MArked it obsolete');
    ELSE
      IF osboletesalesorder_cur.v_status_lkp = '1'
      then
        dbms_output.put_line('SalesOrder is in obsolete');
      ELSE
                dbms_output.put_line ('No sales ORder Found');
      END IF;
     
      END LOOP;
    --Exception handling
  WHEN OTHERS THEN
    NULL;
  END;
  /









error below:-

Error report -
ORA-06550: line 39, column 11:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   if
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
Top Expert 2008

Commented:
DECLARE
  v_reference_code VARCHAR2(30);
  v_status_lkp VARCHAR2(30);

   CURSOR obsoletesalesorder
   IS
     SELECT a.reference_code,
            a.status_lkp
     FROM   base_sales_order a
     WHERE  a.reference_code =  '&1';

 BEGIN
   FOR osboletesalesorder_cur IN obsoletesalesorder
   LOOP
     IF osboletesalesorder_cur.v_status_lkp !='1'
       --Do Below update
       --UPDATE base_sales_order
       --SET    obsolete_flag = 1
       --WHERE  reference_code = osboletesalesorder_cur.reference_code;
       
       --UPDATE base_sales_order_line
       --SET    obsolete_flag = 1
       --WHERE  sales_order_id IN
             -- (
                    -- SELECT sales_order_id
                     --FROM   base_sales_order
                     --WHERE  reference_code = osboletesalesorder_cur.reference_code);
       
      -- COMMIT;
      Then
       dbms_output.Put_line('SalesOrder MArked it obsolete');
     ELSE
       IF osboletesalesorder_cur.v_status_lkp = '1'
       then
         dbms_output.put_line('SalesOrder is in obsolete');
       ELSE
                 dbms_output.put_line ('No sales ORder Found');
       END IF;
       
       END LOOP;
     --Exception handling
   WHEN OTHERS THEN
     NULL;
   END;
   /

OR


DECLARE
  v_reference_code VARCHAR2(30);
  p_ reference_code VARCHAR2(30) = '&REF_CODE';
  v_status_lkp VARCHAR2(30);
   CURSOR obsoletesalesorder
   IS
     SELECT a.reference_code,
            a.status_lkp
     FROM   base_sales_order a
     WHERE  a.reference_code = p_ reference_code ;

 BEGIN
   FOR osboletesalesorder_cur IN obsoletesalesorder
   LOOP
     IF osboletesalesorder_cur.v_status_lkp !='1'
       --Do Below update
       --UPDATE base_sales_order
       --SET    obsolete_flag = 1
       --WHERE  reference_code = osboletesalesorder_cur.reference_code;
       
       --UPDATE base_sales_order_line
       --SET    obsolete_flag = 1
       --WHERE  sales_order_id IN
             -- (
                    -- SELECT sales_order_id
                     --FROM   base_sales_order
                     --WHERE  reference_code = osboletesalesorder_cur.reference_code);
       
      -- COMMIT;
      Then
       dbms_output.Put_line('SalesOrder MArked it obsolete');
     ELSE
       IF osboletesalesorder_cur.v_status_lkp = '1'
       then
         dbms_output.put_line('SalesOrder is in obsolete');
       ELSE
                 dbms_output.put_line ('No sales ORder Found');
       END IF;
       
       END LOOP;
     --Exception handling
   WHEN OTHERS THEN
     NULL;
   END;
   /

Author

Commented:
Still same error:-

DECLARE
  v_reference_code VARCHAR2(30);
  v_status_lkp VARCHAR2(30);

   CURSOR obsoletesalesorder
   IS
     SELECT a.reference_code,
            a.status_lkp
     FROM   base_sales_order a
     WHERE  a.reference_code =  '&1';

 BEGIN
   FOR osboletesalesorder_cur IN obsoletesalesorder
   LOOP
     IF osboletesalesorder_cur.v_status_lkp !='1'
       --Do Below update
       --UPDATE base_sales_order
       --SET    obsolete_flag = 1
       --WHERE  reference_code = osboletesalesorder_cur.reference_code;
       
       --UPDATE base_sales_order_line
       --SET    obsolete_flag = 1
       --WHERE  sales_order_id IN
             -- (
                    -- SELECT sales_order_id
                     --FROM   base_sales_order
                     --WHERE  reference_code = osboletesalesorder_cur.reference_code);
       
      -- COMMIT;
      Then
       dbms_output.Put_line('SalesOrder MArked it obsolete');
     ELSE
       IF osboletesalesorder_cur.v_status_lkp = '1'
       then
         dbms_output.put_line('SalesOrder is in obsolete');
       ELSE
                 dbms_output.put_line ('No sales ORder Found');
       END IF;
       
       END LOOP;
     --Exception handling
   WHEN OTHERS THEN
     NULL;
   END;
   /


Error report -
ORA-06550: line 40, column 12:
PLS-00103: Encountered the symbol "LOOP" when expecting one of the following:

   if
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

Can you please look.
johnsoneSenior Oracle DBA

Commented:
Your missing an end if and the word exception in the exception block.
DECLARE 
  v_reference_code VARCHAR2(30);
  v_status_lkp VARCHAR2(30);

   CURSOR obsoletesalesorder
   IS 
     SELECT a.reference_code, 
            a.status_lkp 
     FROM   base_sales_order a 
     WHERE  a.reference_code =  '&1'; 

 BEGIN 
   FOR osboletesalesorder_cur IN obsoletesalesorder 
   LOOP 
     IF osboletesalesorder_cur.v_status_lkp !='1' 
       --Do Below update 
       --UPDATE base_sales_order 
       --SET    obsolete_flag = 1 
       --WHERE  reference_code = osboletesalesorder_cur.reference_code; 
        
       --UPDATE base_sales_order_line 
       --SET    obsolete_flag = 1 
       --WHERE  sales_order_id IN 
             -- ( 
                    -- SELECT sales_order_id 
                     --FROM   base_sales_order 
                     --WHERE  reference_code = osboletesalesorder_cur.reference_code); 
        
      -- COMMIT; 
      Then
       dbms_output.Put_line('SalesOrder MArked it obsolete'); 
     ELSE 
       IF osboletesalesorder_cur.v_status_lkp = '1' 
       then
         dbms_output.put_line('SalesOrder is in obsolete');
       ELSE 
                 dbms_output.put_line ('No sales ORder Found'); 
       END IF;
       
     END IF;
       END LOOP;
     --Exception handling 
   EXCEPTION WHEN OTHERS THEN 
     NULL; 
   END; 
/

Open in new window

Author

Commented:
Hello All,

For
Now script is working fine , i need to modify it so if that works fine i will close the questions . Please give me some time.

Author

Commented:
I am still getting this issue:-



DECLARE
  v_reference_code VARCHAR2(30);
  v_status_lkp VARCHAR2(30);

   CURSOR obsoletesalesorder
   IS
     SELECT a.reference_code,
            a.status_lkp
     FROM   base_sales_order a
     WHERE  a.reference_code =  '&1';

 BEGIN
   FOR osboletesalesorder_cur IN obsoletesalesorder
   LOOP
     If osboletesalesorder_cur.v_status_lkp <>'1'
       --Do Below update
       then
       UPDATE base_sales_order
       SET    obsolete_flag = 1
       WHERE  reference_code = osboletesalesorder_cur.v_reference_code;
       
       UPDATE base_sales_order_line
       SET    obsolete_flag = 1
       WHERE  sales_order_id IN
              (
                     SELECT sales_order_id
                     FROM   base_sales_order
                     WHERE  reference_code = osboletesalesorder_cur.v_reference_code);
       
       COMMIT;
     
       dbms_output.Put_line('SalesOrder MArked it obsolete');
     ELSE
       IF osboletesalesorder_cur.v_status_lkp = '1'
       then
         dbms_output.put_line('SalesOrder is in obsolete');
       ELSE
                 dbms_output.put_line ('No sales ORder Found');
       END IF;
       
     END IF;
       END LOOP;
     --Exception handling
  EXCEPTION
     WHEN OTHERS
       THEN
        ROLLBACK;
        CLOSE ObsoleteSalesOrder;
       DBMS_OUTPUT.PUT_LINE (Sqlcode||Sqlerrm||''||'Error At Line Number '||dbms_utility.format_error_backtrace);
   END;
/



Error is below:-

Error report -
ORA-06550: line 15, column 32:
PLS-00302: component 'V_STATUS_LKP' must be declared
ORA-06550: line 15, column 6:
PL/SQL: Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:


Can some one run this script on local system and provide a final output please. Its very urgent.
Senior Oracle DBA
Commented:
We don't have your system and your tables.  If you want to provide create statements and sample data then someone can run it.  A quick run through the code would say this should work:
DECLARE 
   CURSOR obsoletesalesorder
   IS 
     SELECT a.reference_code, 
            a.status_lkp 
     FROM   base_sales_order a 
     WHERE  a.reference_code =  '&1'; 

 BEGIN 
   FOR osboletesalesorder_cur IN obsoletesalesorder 
   LOOP 
     If osboletesalesorder_cur.status_lkp <>'1' 
       --Do Below update 
       then 
       UPDATE base_sales_order 
       SET    obsolete_flag = 1 
       WHERE  reference_code = osboletesalesorder_cur.reference_code; 
        
       UPDATE base_sales_order_line 
       SET    obsolete_flag = 1 
       WHERE  sales_order_id IN 
              ( 
                     SELECT sales_order_id 
                     FROM   base_sales_order 
                     WHERE  reference_code = osboletesalesorder_cur.reference_code); 
        
       COMMIT; 
      
       dbms_output.Put_line('SalesOrder MArked it obsolete'); 
     ELSE 
       IF osboletesalesorder_cur.status_lkp = '1' 
       then
         dbms_output.put_line('SalesOrder is in obsolete');
       ELSE 
                 dbms_output.put_line ('No sales ORder Found'); 
       END IF;
       
     END IF;
       END LOOP;
     --Exception handling 
  EXCEPTION 
     WHEN OTHERS 
       THEN
        ROLLBACK;
        CLOSE ObsoleteSalesOrder;
       DBMS_OUTPUT.PUT_LINE (Sqlcode||Sqlerrm||''||'Error At Line Number '||dbms_utility.format_error_backtrace);
   END; 
/

Open in new window

You aren't selecting a column called V_STATUS_LKP or V_REFERENCE_CODE so you cannot reference those.

As shown before, I certainly wouldn't do it this way, but if that is the way you want to do it, go for it.  You really should parameterize the cursor.
Helena Markováprogrammer-analyst

Commented:
Here is a problem:
If osboletesalesorder_cur.v_status_lkp <>'1'

There is no v_status_lkp  in cursor obsoletesalesorder, there is status_lkp.

You have to modify
If osboletesalesorder_cur.v_status_lkp to
 If osboletesalesorder_cur.status_lkp
in all places of your code.

Author

Commented:
Hello all,

I just modified my scrip and facing one silly issue . Can some one help. there is one error and other is one requirement that i need inside else i want to append this thing:-

IF(osboletesalesorder_cur.MNP_FORM_SIGNED_FLAG = '1')
                                    Then
                                          DBMS_OUTPUT.Put_line ('This is MNP Case ,Please get in touch with BSCS and comptel for full cleanup of request');
                              
                              End IF;

Error for below script is given below:-




set serveroutput on;
Declare
 counter number :=0;      

    CURSOR obsoletesalesorder IS SELECT reference_code,status_lkp,obsolete_flag,MNP_FORM_SIGNED_FLAG FROM  base_sales_order WHERE reference_code IN ('10111379');
      Begin
            FOR osboletesalesorder_cur IN obsoletesalesorder
                  Loop
                        BEGIN
                        Select reference_code FROM  base_sales_order WHERE reference_code=obsoletesalesorder.reference_code;
                        /*************** Check Status of Sales Order Not in Complete State ************/
                        counter:=counter+1;
                              If (( osboletesalesorder_cur.obsolete_flag ='1') or (osboletesalesorder_cur.status_lkp = '1'))

                        /************ Obsolete Cannot be done*********/
 
                                    Then
                                          dbms_output.put_line('Sales Order  '|| osboletesalesorder_cur.reference_code  || '  Is Either already completed or already in obsolete state');
                              else

                                          UPDATE base_sales_order SET obsolete_flag =1 WHERE  reference_code = osboletesalesorder_cur.reference_code;
                                          dbms_output.Put_line('Sales Order '|| osboletesalesorder_cur.reference_code || ' marked obsolete in Epiphany');       
                                          
                                          UPDATE base_sales_order_line SET obsolete_flag = 1 WHERE  sales_order_id IN (SELECT sales_order_id FROM base_sales_order WHERE  reference_code = osboletesalesorder_cur.reference_code);
                                          dbms_output.Put_line('Sales Order '|| osboletesalesorder_cur.reference_code || ' marked obsolete from Sales Order Console');
          
                                          COMMIT;
             
                                                
                                          
           END IF;            
                              
                              
              Exception
                                          When others then
                                          dbms_output.put_line('No Sales Order Found');
     
      End;
 
     End Loop;
     
       /********display counter as how many orders were marked obsolete*******/
      
                        dbms_output.put_line('Number of Orders marked obsolete are ' );
   
     EXCEPTION
            WHEN OTHERS THEN
                        DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
            END;
            /




Error report -
ORA-06550: line 9, column 56:
PLS-00225: subprogram or cursor 'OBSOLETESALESORDER' reference is out of scope
ORA-06550: line 9, column 56:
PL/SQL: ORA-00904: "OBSOLETESALESORDER"."REFERENCE_CODE": invalid identifier
ORA-06550: line 9, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:
johnsoneSenior Oracle DBA

Commented:
I really recommend that you read the documentation.  At least to get a basic understanding of what is going on.  Even if you fix the error, I can see other ones that will come when you fix those.

Check out the doucmentation on how to do a SELECT INTO.
And the documenatation on how to use a CURSOR FOR LOOP.

You appear to have added a select statement in the code, but it seems to be selecting the same record that the cursor already selected, which seems pretty unnecessary.

Author

Commented:
DECLARE
        v_ref                   base_sales_order.reference_code%type := '&1';
        v_order_ref             base_sales_order.reference_code%type;

        --reference_code1 varchar2(2000);
        CURSOR obsoletesalesorder IS SELECT reference_code,status_lkp,obsolete_flag,MNP_FORM_SIGNED_FLAG
                                                                FROM  base_sales_order WHERE reference_code = v_ref;
BEGIN
        /* CHECK IF THE SALES ORDER EXIST IN DATABASE OR NOT */
        select reference_code into v_order_ref from base_sales_order where reference_code = v_ref;

        FOR V_cur IN obsoletesalesorder
        Loop
                BEGIN
                If (( v_cur.obsolete_flag ='1') or (v_cur.status_lkp = '1'))
                        /************ Obsolete Cannot be done*********/
                        THEN
                                dbms_output.put_line(v_cur.reference_code||': Sales Order Is Either already completed or already in obsolete state');
                        ELSE
                                IF (v_cur.MNP_FORM_SIGNED_FLAG ='1') THEN
                                        UPDATE base_sales_order SET obsolete_flag =1 WHERE  reference_code = v_cur.reference_code;
                                        UPDATE base_sales_order_line SET obsolete_flag = 1 WHERE  sales_order_id IN (SELECT sales_order_id FROM base_sales_order WHERE  reference_code = v_cur.reference_code);
                                        COMMIT;
                                        DBMS_OUTPUT.PUT_LINE(v_cur.reference_code ||' : Order marked obsolete in Epiphany.Please contact BSCS and Comptel for cleanup as this is MNP order');
                                ELSE
                                        UPDATE base_sales_order SET obsolete_flag =1 WHERE  reference_code = v_cur.reference_code;
                                        UPDATE base_sales_order_line SET obsolete_flag = 1 WHERE  sales_order_id IN (SELECT sales_order_id FROM base_sales_order WHERE  reference_code = v_cur.reference_code);
                                        COMMIT;
                                        DBMS_OUTPUT.PUT_LINE(v_cur.reference_code ||' : Sales Order marked obsolete from Sales Order Console');
                                END IF;
                        END IF;
                EXCEPTION
                        WHEN  OTHERS THEN
                                ROLLBACK;
                                DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
                END;
        END LOOP;
EXCEPTION
        WHEN NO_DATA_FOUND THEN
                DBMS_OUTPUT.PUT_LINE(V_REF ||' : NOT A VALID SALES ORDER REFERENCE');
        WHEN  OTHERS THEN
                DBMS_OUTPUT.PUT_LINE(SQLCODE||','||SQLERRM);
END;
/

Author

Commented:
issue solved now . Thanks all for your help.

Author

Commented:
Thanks for your immense support.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial