CREATE OR REPLACE PROCEDURE CMS_RBRU.CMS_RBRU_ACCOUNTS_UPDATE
IS
cursor am is SELECT DISTINCT
CASE
WHEN co.CONTRACTGROUPID = 'G4' THEN 'UPDATE CMS_RBRU.T_PE_COBJECT SET CURRENTACCOUNTBALANCE = '''|| regexp_replace(co.amount, '^(-?)([.,])', '\10\2') ||''', ACCOUNTCREDITED = '''|| cra.ACCNO || ''', ACCOUNTDEBITED = '''|| dea.ACCNO ||''' where CMSID = '''|| co.SOURCEID || ''' '
WHEN co.CONTRACTGROUPID != 'G4' THEN 'UPDATE CMS_RBRU.T_PE_COBJECT SET CURRENTACCOUNTBALANCE = '''|| regexp_replace(co.amount, '^(-?)([.,])', '\10\2') ||''', ACCOUNTDEBITED = '''|| cra.ACCNO || ''', ACCOUNTCREDITED = '''|| dea.ACCNO ||''' where CMSID = '''|| co.SOURCEID || ''' '
END AS "script"
FROM CMS_AM.CMS_OBJECTS co
INNER JOIN CMS_AM.CMS_ACCOUNTS cra ON cra.id = co.CONTRAACCOUNT_ID
INNER JOIN CMS_AM.CMS_ACCOUNTS dea ON dea.id = co.MAINACCOUNT_ID
INNER JOIN CMS_AM.CMS_MSG_OBJECTS cmo ON cmo.SOURCEID = co.SOURCEID
WHERE cmo.MESSAGE_ID in (select m.id
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.id > (select (max (mm.id)-150000) from cms_am.cms_MSG_OBJECTS mm)
and mo.RESPONSEMESSAGEDATE >= TRUNC(SYSDATE)
and mo.status ='SENT'
and m.operation in ('bindToDeal','unbindOffDeal','changeAccAttr'));
current_upd am%ROWTYPE;
obj_counter NUMBER:=0;
BEGIN
OPEN am;
LOOP
FETCH am INTO current_upd;
EXIT WHEN am%NOTFOUND;
EXECUTE IMMEDIATE current_upd."script" ;
DBMS_OUTPUT.PUT_LINE('upd= '|| current_upd."script");
obj_counter:=obj_counter+1;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE('Updates count  = ' || obj_counter);
CLOSE am;
END CMS_RBRU_ACCOUNTS_UPDATE;