Code Convert Data

mail@pastecode.io avatar
unknown
sql
a year ago
2.6 kB
2
Indexable
Never
create or replace procedure SYNC_DATA_ACCOUNT IS
writeErrorSuccess boolean;
BEGIN
  -- bang balanceservices
  FOR REC IN (SELECT CID,CONVACN,ACN, GRP FROM qt2020.TT_MAP_DDCDMAST_DEP@syncdata bt JOIN balanceservices b ON b.cif=bt.acn)
    LOOP
      BEGIN
        UPDATE balanceservices b SET
        b.accid = rec.cid
        WHERE b.accid = rec.convacn AND b.cif = rec.acn AND b.accid IS NOT NULL AND rec.grp='DDA';
        COMMIT;
      exception
        when others then
          writeErrorSuccess := commonfunctions_pkg.GHI_LOI('procedure SYNC_DATA_ACCOUNT:Balanceservices ' ||
                                                           substr(sqlerrm,
                                                                  1,
                                                                  200));
      end;
    END LOOP;
    
   
  -- bang balances
  FOR REC IN (SELECT CID,CONVACN,ACN, grp FROM qt2020.TT_MAP_DDCDMAST_DEP@syncdata bt JOIN balances b ON b.cif=bt.acn ) 
    LOOP
      BEGIN
        
        UPDATE balances b SET
        b.accid = rec.cid
        WHERE b.accid = rec.convacn AND b.cif = rec.acn AND b.accid IS NOT NULL AND rec.grp='DDA';
        COMMIT;
      exception
        when others then
          writeErrorSuccess := commonfunctions_pkg.GHI_LOI('procedure SYNC_DATA_ACCOUNT:Balances ' ||
                                                           substr(sqlerrm,
                                                                  1,
                                                                  200));
      end;
    END LOOP;
    
   --- bang customers: accdefautl
   FOR REC IN (SELECT CID,CONVACN,ACN FROM qt2020.TT_MAP_DDCDMAST_DEP@syncdata bt JOIN customers ct ON ct.cif=bt.acn ) 
    LOOP
      BEGIN
        UPDATE customers ct SET
        ct.accdefautl = rec.cid
        WHERE ct.accdefautl = rec.convacn AND ct.cif = rec.acn AND ct.accdefautl IS NOT NULL;
        COMMIT;
        
        UPDATE customers ct SET
        ct.accfeedefault = rec.cid
        WHERE ct.accfeedefault = rec.convacn AND ct.cif = rec.acn AND ct.accfeedefault IS NOT NULL;
        COMMIT;
      exception
        when others then
          writeErrorSuccess := commonfunctions_pkg.GHI_LOI('procedure SYNC_DATA_ACCOUNT:customers accdefautl ' ||
                                                           substr(sqlerrm,
                                                                  1,
                                                                  200));
      end;
    END LOOP;
end SYNC_DATA_ACCOUNT;