SET SERVEROUTPUT ON
declare cursor am
is
select mo.id as obj_id, m.id as msg_id, mo.MASTEROBJECT_ID,mo.status as obj_stats,m.status as msg_stats,m.REPLYSTATUS, m.isobjectsfinished,
m.CADEPENDENTMESSAGE_ID , m.DLDEPENDENTMESSAGE_ID, mo.depositorid, mo.sourceid, m.operation,m.MESSAGEDATE,m.body, mo.responsemessagebody
,mo.RESPONSEMESSAGEDATE--, m.MESSAGEID
from cms_am.cms_MSG_OBJECTS mo
right join cms_am.cms_messages m on mo.message_id=m.id
where 1=1
and mo.status in ('ERROR','WORKING', 'DEPENDENT', 'WAITING', 'TIMEOUT', 'FINISHED' ) 
and mo.DEPOSITORID not in ('916238','Z94594')
and length(mo.responsemessagebody) > 0
and mo.id > 77726372
and mo.sourceid not like 'createSession_%'
and mo.sourceid not like 'sendPosting_%'
and mo.sourceid not like 'getBalance_%'
order by m.MESSAGEDATE ;

--Объявление переменных
am_entry  am%ROWTYPE; 
v_col  VARCHAR(10) := '';
npe_col VARCHAR(10):='';
npe_counteraccount_id VARCHAR(10) := '';
npe_mainaccount_id VARCHAR(10) :='';
entry_id NUMBER ;

BEGIN 
DBMS_OUTPUT.PUT_LINE ('STARTING');
  OPEN am; 
  loop
    FETCH am INTO am_entry; 
    exit when am%NOTFOUND;
    v_col := am_entry.obj_id;

--Блок для обработки ошибки NullPointerException

    if am_entry.responsemessagebody like '%NullPointerException%'
    then
       npe_col := am_entry.sourceid;
       DBMS_OUTPUT.PUT_LINE( 'NullPointerException_COL = ' || npe_col );
       
        -- присваеваем значение CONTRAACCOUNT_ID
       select distinct ac.id into npe_counteraccount_id                         
        from cms_operations o  
        left join cms_accounts ac on ac.id=o.creditaccount_id
        left join cms_msg_objects ob on ob.id=o.object_id
        where
        o.ISDEBITACCOUNTMAIN =1 and
        ob.sourceid in (
        select SOURCEID from CMS_OBJECTS where
        ISACCOUNTED =1 and CONTRAACCOUNT_ID is null and SOURCEID = npe_col
        )
        order by 1;
          
        -- присваеваем значение MAINACCOUNT_ID
         select acc.id into npe_mainaccount_id
         from CMS_OBJECTS ob
         join cms_agreements agr on agr.ID = ob.ACTUALAGREEMENT_ID
         join cms_accounts acc on acc.NAMEENG = agr.SOURCEID
         where 1=1
         and ob.ISACCOUNTED =1
         and ob.MAINACCOUNT_ID is null
         and ob.SOURCEID = npe_col 
         order by 1;
         
         --обновление значений в таблице CMS_OBJECTS
         update CMS_OBJECTS
         set CONTRAACCOUNT_ID = npe_counteraccount_id, MAINACCOUNT_ID = npe_mainaccount_id
         where ISACCOUNTED =1
         and CONTRAACCOUNT_ID is null
         and MAINACCOUNT_ID is null
         and SOURCEID in (npe_col);
             
     end if;     
  end loop;
  EXCEPTION WHEN NO_DATA_FOUND   THEN DBMS_OUTPUT.PUT_LINE ('Не найдено данных для обработки.');
  CLOSE am; 
END;

EXCEPTION WHEN NO_DATA_FOUND   THEN DBMS_OUTPUT.PUT_LINE ('Не найдено данных в одной из проводок по ошибке NullPoINterException');