create table test1(sno number,old Varchar2(15),new varchar2(15),org varchar2(10),entity varchar2(15))

create table test2(sno number,name Varchar2(15),org varchar2(10),entity varchar2(15),err_msg varchar2(200))

insert all 
into test2 values (1,'Donald','ERP','PD',NULL) 
into test2 values (2,'Obama','ERP','PD',NULL) 
into test2 values (3,'Hillary','ERP','PD',NULL) 
into test2 values (4,'John','ERP','PD',NULL) 
into test2 values (5,'Hoper','ERP','PD',NULL) 
select * from dual

insert all 
into test1 values (1,'Donald','Trump','ERP','PD') 
into test1 values (2,'Obama','Barack','ERP','PD') 
into test1 values (3,'Hillary','Clinton','ERP','PD') 
into test1 values (4,'John Hoper','JHoper','ERP','PD') 
into test1 values (5,'TIM','T Martin','ERP','PD') 
select * from dual

create table test3(sno number,name Varchar2(15),org varchar2(10),entity varchar2(15),err_msg varchar2(200))

select * from test1

select * from test3

select * from test2

select * from test3

alter table test3 drop column err_msg

insert all 
into test3 values (1,'Will Smith','ERP','PD') 
into test3 values (2,'Tiger Woods','ERP','PD') 
into test3 values (3,'Modi','ERP','PD') 
into test3 values (4,'John','ERP','PD') 
into test3 values (5,'TIM','ERP','PD') 
select * from dual

select * from test3

select * from test1

select * from test2

select * from test3

select * from test2

CREATE OR REPLACE PROCEDURE Update_Prc(p_Templ IN VARCHAR2,  
                                                           P_ORG    IN VARCHAR2) IS  
  L_TEMPLATE_NAME VARCHAR2(400);  
  CURSOR CUR_OU IS  
    SELECT ROWID ROW_ID, XMSI.*  
      FROM Test2 XMSI  
     WHERE XMSI.org = P_ORG  
     --  AND XMSI.entity = p_Templ 
     ;  
BEGIN  
  FOR CUR_GETR IN CUR_OU LOOP  
    BEGIN  
      SELECT NEW  
        INTO L_TEMPLATE_NAME  
        FROM Test1 LDM  
       WHERE 1 = 1  
         AND LDM.old = CUR_GETR.Name  
         AND LDM.org = CUR_GETR.ORG 
         AND LDM.ENTITY = 'PD'; --add org conditions  
      UPDATE Test2  
         SET TEMPLATE_NAME = L_TEMPLATE_NAME  
       WHERE ROWID = CUR_GETR.ROW_ID  
         AND L_TEMPLATE_NAME IS NOT NULL;  
    EXCEPTION  
      WHEN NO_DATA_FOUND THEN  
        BEGIN  
          SELECT TEMPLATE_NAME  
            INTO L_TEMPLATE_NAME  
            FROM test3 MIT  
           WHERE MIT.name =CUR_GETR.Name;  
        EXCEPTION  
          WHEN NO_DATA_FOUND THEN  
            UPDATE Test2 XMSI  
               SET ERR_MsG = 'Template Not found in System'  
             WHERE ROWID = CUR_GETR.ROW_ID;  
        END;  
    END;  
  END LOOP;  
  COMMIT;  
EXCEPTION  
  WHEN OTHERS THEN  
    DBMS_OUTPUT.PUT_LINE('The Out sode loop  exception');  
END;

select rowid from test1