Проблемы слияния записей в сложносвязанной таблице Oracle

С декабря прошлого года, нам наконец внедрили дорогущую умную систему управления отелем.
Каждый элемент имеет свой собственный сервер и соответственно свою базу данных, функциональные части могут обмениваться между собой данными о клиентах и продажах.
Система состоит из трех модулей:

• Fidelio V8 — модуль управления гостиницей, вполне состоявшееся приложение. Имеет систему бронирования через сайт и от стойки. занимает приоритетное место слегка капризен, кучу отчетов, ведет статистику. В качестве базы данных используется Oracle 11gR2
• Micros 3700-модуль управления рестораном(работа на местах ведется через типовые POS моноблоки), интерактивная рассадка гостей, разделение чеков и много разных полезных вещей которые облегчают жизнь. Работает на Sybase.
• TNG- довольно молодой модуль для продажи услуг медицинского центра и спортивно-развлекательного комплекса, своя собственная система бронирования, клиентская база, регистрация клиентов etc. Работает на Oracle 11gR2. Весь пост, собственно и связан с этим модулем.

SELECT card_id,first_name,last_name,created_person,card_type_id,card_status_id,creation_date FROM cards
where 
last_name like '%ва' or
last_name like'%ая' or
last_name like '%а' 
order by last_name;

После теста выборки обновляем, поле gender

update cards 
set gender = "ж"
where
last_name like '%ва' or
last_name like'%ая' or
last_name like '%а' ;
commit;

update cards
set gender = 'ю'
where gender is null;
commit;

Карты объединяются по критерию:
UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name)

create or replace
PROCEDURE cards_merge AS 

vLast VARCHAR2(40);
vFirst VARCHAR2(40);
vSecond VARCHAR2(40);
vBirth DATE;
vCardId NUMBER;
vOrdr NUMBER;
vTargetCardId NUMBER;

-- ACC
vAcDefId NUMBER(6,0);
vLoanFlg CHAR(1);
vAmount NUMBER(19,4);
vCrdTtl NUMBER(19,4);
vLoanTtl NUMBER(19,4);
cntAccBallanceTargetCard NUMBER;
vBallance NUMBER(19,4);
vCredit NUMBER(19,4);
vLoan NUMBER(19,4);
--
cntBS NUMBER;

CURSOR curCards  IS 
select UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name) from cards group by UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name) having count(*) >1 order by UPPER(last_name);
CURSOR curMerge(pLast IN VARCHAR2, pFirst IN VARCHAR2, pSecond IN VARCHAR2, pBirth IN DATE)  IS  
select card_id, ROW_NUMBER() OVER (PARTITION BY '' ORDER BY card_id DESC) as ordr from CARDS
WHERE 
(UPPER(last_name)=pLast or (pLast is NULL and last_name is NULL)) 
and (UPPER(first_name)=pFirst or (pFirst is NULL and first_name is NULL)) 
and (birth_date = pBirth or (pBirth is NULL and birth_date is NULL)) 
and (UPPER(second_name)=pSecond or (pSecond is NULL and second_name is NULL)) 
ORDER BY card_id desc;

CURSOR curAcc(pSourceCardId IN NUMBER) IS  
select ac.ACCOUNT_DEF_ID, ac.loan_flag, ac.amount from ACCOUNTING ac, DOCS dc WHERE ac.doc_id=dc.doc_id 
and dc.COFIRMATION='T' and ac.CARD_ID=pSourceCardId;                                                                                                                             

BEGIN
OPEN curCards;
LOOP
FETCH curCards INTO VLAST, VFIRST, VBIRTH, VSECOND;
EXIT WHEN NOT curCards%FOUND;
 
OPEN curMerge(VLAST, VFIRST, VSECOND, VBIRTH);
LOOP
FETCH curMerge INTO vCardId, vOrdr;
EXIT WHEN NOT curMerge%FOUND;
              
 if vOrdr=1 then
 -- target card;
    vTargetCardId := vCardId;
    else
                  
  -- ACCOUNTING
     OPEN curAcc(vCardId);
     LOOP
     FETCH curAcc INTO vAcDefId, vLoanFlg, vAmount;
     EXIT WHEN NOT curAcc%FOUND;
                               
     if vAmount < 0 then
     vCrdTtl:=vAmount;
     else
     vCrdTtl:=0;
     end if;
                               
     if vLoanFlg='T' then
        vLoanTtl:=vAmount;
    else
        vLoanTtl:=0;
    end if;
 
select count(*) into cntAccBallanceTargetCard FROM accounting_balance ab 
  WHERE ab.card_id=vTargetCardId and ab.account_def_id=vAcDefId;
 if cntAccBallanceTargetCard =0 then
 INSERT INTO accounting_balance( ID, ACCOUNT_DEF_ID, CARD_ID, AMOUNT, CRD_TTL, LAST_EXPIRY, LOAN )
 values 
(ACCOUNTING_BALANCE_SEQUENCE.nextval, vAcDefId, vTargetCardId, vAmount, vCrdTtl, NULL, vLoanTtl);
 else
 select ab.amount, ab.crd_ttl, ab.loan into vBallance, vCredit, vLoan  FROM accounting_balance ab 
    WHERE ab.card_id=vTargetCardId and ab.account_def_id=vAcDefId;
    
UPDATE accounting_balance SET AMOUNT= vBallance+ vAmount, CRD_TTL=vCredit + vCrdTtl, LOAN=vLoan+ vLoanTtl 
     WHERE card_id=vTargetCardId and account_def_id=vAcDefId;
     end if;
     end loop;
     CLOSE curAcc;
                    
 UPDATE ACCOUNTING SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
 DELETE ACCOUNTING_BALANCE
 WHERE CARD_ID=vCardId;
 -- END ACCOUNTING 
 -- BONUS_TRIGGER_HISTORY
 UPDATE BONUS_TRIGGER_HISTORY SET CARD_ID=vTargetCardId 
WHERE CARD_ID=vCardId;
 -- END BONUS_TRIGGER_HISTORY
 -- BOOKING_RESOURCES
UPDATE BOOKING_RESOURCES SET RESOURCE_ID=vTargetCardId 
WHERE RESOURCE_ID=vCardId and RESOURCE_TYPE='P';
 -- END BOOKING_RESOURCES
-- CARD_FOREIGN_IDS

DELETE FROM CARD_FOREIGN_IDS WHERE CARD_ID=vCardId;
-- END CARD_FOREIGN_IDS
-- DOCS
UPDATE DOCS SET DEF_CARD_ID=vTargetCardId WHERE DEF_CARD_ID=vCardId;
 -- END DOCS
-- DSC_HISTORY
UPDATE DSC_HISTORY SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
-- END DSC_HISTORY
-- ITEM_ACCOUNTING
UPDATE ITEM_ACCOUNTING SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
update item_balance ib set ib.count= nvl(( select sum(ia.mi_count) from item_accounting ia, docs d 
where ia.doc_id = d.doc_id and ia.subscription_item_id=ib.subscription_item_id
and ia.subscription_accounting_id=ib.subscription_accounting_id  and d.cofirmation='T' and ia.card_id=vTargetCardId),0);
DELETE  item_balance WHERE CARD_ID=vCardId;            
                               
-- END ITEM_ACCOUNTING
-- ITEM_PERIOD_ACC
UPDATE ITEM_PERIOD_ACC SET CARD_ID =vTargetCardId WHERE CARD_ID=vCardId;
-- END ITEM_PERIOD_ACC
-- REGISTRATION
UPDATE REGISTRATION SET card_id=vTargetCardId WHERE CARD_ID=vCardId;
-- END REGISTRATION
-- SUBSCRIPTION_ACCOUNTING
UPDATE SUBSCRIPTION_ACCOUNTING SET card_id=vTargetCardId WHERE CARD_ID=vCardId;
-- END SUBSCRIPTION_ACCOUNTING
 -- TIMER
                  UPDATE TIMER SET card_id=vTargetCardId WHERE CARD_ID=vCardId;
                  -- END TIMER
                  -- RULESET_PERIOD_ACC
                  UPDATE RULESET_PERIOD_ACC SET card_id=vTargetCardId WHERE CARD_ID=vCardId;
                  -- END RULESET_PERIOD_ACC
                  -- CARD_XTRA
                  DELETE CARD_XTRA WHERE CARD_ID=vCardId;
                  -- END CARD_XTRA
                  -- CLIENT_RELATIONS
                  UPDATE CLIENT_RELATIONS SET CARD_ID_1=vTargetCardId WHERE CARD_ID_1=vCardId;
                  UPDATE CLIENT_RELATIONS SET CARD_ID_2=vTargetCardId WHERE CARD_ID_2=vCardId;
                  DELETE CLIENT_RELATIONS WHERE CARD_ID_1=vTargetCardId and CARD_ID_2=vTargetCardId;
                  -- END CLIENT_RELATIONS
                  -- CLIENT_PROFILE_DATA
                  DELETE CLIENT_PROFILE_DATA WHERE card_id=vCardId;
                  -- END CLIENT_PROFILE_DATA
                  -- BS_TASK
                  UPDATE BS_TASK SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
                  -- END BS_TASK
                  -- BS_XXX
                  SELECT COUNT(*) into cntBS FROM ALL_TABLES WHERE TABLE_NAME = 'BS_BOOKING';
                  if cntBS >0 then
                      UPDATE BS_BOOKING SET CARD_ID=vTargetCardId WHERE CARD_ID=vCardId;
                  end if;
                  -- END BS_XXX
                  -- CARDS
                  UPDATE CARDS SET MASTER_CARD_ID=vTargetCardId WHERE MASTER_CARD_ID=vCardId;
                  DELETE CARDS WHERE card_id=vCardId;
                  -- END CARDS
 -- CARDS_MERGE_HISTORY
INSERT INTO CARDS_MERGE_HISTORY 
(CARDS_MERGE_HISTORY_ID, empl_id, 
operator, source_card_id, source_name, target_card_id, target_name, tm) 
values
 (CARDS_MERGE_HISTORY_SEQUENCE.nextval, NULL, 'UNKNOWN', 
vCardId, VLAST ||' '|| VFIRST, vTargetCardId, VLAST ||' '|| VFIRST,sysdate);
                  -- END CARDS_MERGE_HISTORY
                  
               end if;

end loop;
 CLOSE curMerge;

end loop;
CLOSE curCards;
 
END cards_merge;
/
quit;

select UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name), count(*)
from cards group by UPPER(last_name), UPPER(first_name), birth_date, UPPER(second_name) having count(*) >1
 order by UPPER(last_name);

Процедуру ставим в задачи, на 02.00 am понедельника, чтобы исключить влияние пользователей на выполнение скрипта.