PROCEDURE ASACTIVITY_TAXPAYER_DMMY 

IS 
CURSOR ASACTIVITY_cur 
IS 
 select xmldata, activityguid from XML_DMMY where STATUSCODE in ('01', '02', '09') and  TYPECODE in ('01', '04');

TYPE ASACTIVITY_TWO_COLS is RECORD
(
 XMLDATA XML_DMMY.XMLDATA% TYPE
 ACTIVITYGUID XML_DMMY.ACTIVITYGUID%TYPE
 );
 
TYPE  ASACTIVITY_INFO_XMLDATAGUID is TABLE OF ASACTIVITY_TWO_COLS;

ASACTIVITY_INFO ASACTIVITY_INFO_XMLDATAGUID;

v_TaxpayerClientGUID varchar2(36) := null;
par_attr varchar2(36) := 'TaxpayerClientGUID';
par_app Number := 1;
v_TaxId varchar2(15);
v_TaxpayerTaxID varchar2(40) := 'TaxpayerTaxID';
v_OwnerTaxID varchar2(40) := 'OwnerTaxID';
v_AnnuitantTaxID varchar2(40) := 'AnnuitantTaxID';
retval1 clob := null;

BEGIN 
OPEN ASACTIVITY_cur;
LOOP 
FETCH ASACTIVITY_cur 
BULK COLLECT INTO ASACTIVITY_INFO LIMIT 500; 

FORALL indx IN 1 .. ASACTIVITY_INFO.COUNT 
LOOP 
 retval1 := ASACTIVITY_INFO(indx).XMLDATA;

select substr(retval1, INSTR(retval1, par_attr,1, par_app) + length(par_attr) + 1 , INSTR(SUBSTR(retval1, INSTR(retval1, par_attr,1, par_app) + length(par_attr) + 2 ), '<')) into v_TaxpayerClientGUID from dual;
  
select taxid into v_taxid from asclient where clientguid = v_TaxpayerClientGUID;
 
          if regexp_like(substr(retval1,INSTR(retval1,v_TaxpayerTaxID,1, par_app) + length(v_TaxpayerTaxID) + 1, length(v_taxid)), '[[:digit:]]') then
  
Select Replace( retval1, substr(retval1,INSTR(retval1,v_TaxpayerTaxID,1, par_app) + length(v_TaxpayerTaxID) + 1, length(v_taxid)), v_taxid) into retval1 from dual;
  
          end if;
  
  
         if regexp_like(substr(retval1,INSTR(retval1,v_OwnerTaxID,1, par_app) + length(v_OwnerTaxID) + 1 ,length(v_taxid)),'[[:digit:]]') then
  
Select Replace(retval1, substr(retval1,INSTR(retval1,v_OwnerTaxID,1, par_app) + length(v_OwnerTaxID) + 1 ,length(v_taxid)), v_taxid) into retval1 from dual;
    
        end if;
  
  
        if regexp_like(substr(retval1,INSTR(retval1,v_AnnuitantTaxID,1, par_app) + length(v_AnnuitantTaxID) + 1 ,length(v_taxid)),'[[:digit:]]') then
        Select Replace( retval1, substr(retval1,INSTR(retval1,v_AnnuitantTaxID,1, par_app) + length(v_AnnuitantTaxID) + 1, length(v_taxid)), v_taxid) into retval1 from dual;
                 
       end if;
  
  
  update ASACTIVITY
  set 
   xmldata = retval1 where ACTIVITYGUID =ASACTIVITY_INFO(indx).ACTIVITYGUID ;


END LOOP; 
EXIT WHEN ASACTIVITY_INFO.COUNT < 500; 

END LOOP; 
CLOSE ASACTIVITY_cur; 
COMMIT; 
EXCEPTION 
WHEN OTHERS THEN 
NULL; 

END ASACTIVITY_TAXPAYER_DMMY;