patch_countchild_dm.prc

数据迁移使用的ETL程序包

create or replace procedure patch_CountChild_DM(p_RELEASE in varchar2) is

  l_parent_id  number(10);
  l_CountChild number;
  l_DbUserSid  varchar2(10);
  l_ServerId   number(20);
  l_Count      number(10) := 0;
  l_KenanId    number(20);
  l_ExternalID varchar2(50);
  l_DbCatSid   varchar2(10);
  l_ACCOUNT_NO number(10);

begin

  for c_sad in (select distinct city
                  from cfg_ken_ms
                 where release = p_RELEASE) loop
    -- get dblink              
    select db_sid
      into l_DbCatSid
      from cfg_ken_msa
     where mig_release = 'CAT';
  
    for c_UpChild in (select *
                        from src_acct_dm
                       where src_id like c_sad.city || '%'
                         and is_processed in ('T', 'P')) loop
    
      EXECUTE IMMEDIATE 'select server_id from src_acct_status where old_id =:1 and src_id =:2'
        into l_ServerId
        using c_UpChild.acct_nbr_97, c_UpChild.src_id;
    
      EXECUTE IMMEDIATE 'select db_sid from cfg_ken_msa where server_id =:1 and mig_release in(''VIP'',:2)'
        into l_DbUserSid
        using l_ServerId, p_RELEASE;
    
      l_ExternalID := pkg_ken_util.FUN_ENCODE(c_UpChild.acct_nbr_97,
                                              c_sad.city,
                                              'BA');
    
      EXECUTE IMMEDIATE 'select distinct account_no from arbor.external_id_acct_map' || '@' ||
                        l_DbCatSid ||
                        ' where external_id = :l_ExternalID and external_id_type = 2'
        into l_ACCOUNT_NO
        using l_ExternalID;
    
      --get parent_id 
      EXECUTE IMMEDIATE 'select parent_id from arbor.cmf' || '@' || l_DbUserSid ||
                        ' where account_type = 1 and account_no = :account_no'
        into l_parent_id
        using l_ACCOUNT_NO;
    
      IF c_UpChild.Is_Processed = 'P' THEN
        --get new CA
        EXECUTE IMMEDIATE 'select kenan_id from src_cust_status where old_id =:1 and src_id =:2'
          into l_KenanId
          using c_UpChild.Cust_Id, c_UpChild.Src_Id;
      
        if l_parent_id <> l_KenanId then
        
          --update parent_id and HIERARCHY_ID
          EXECUTE IMMEDIATE 'update arbor.cmf' || '@' || l_DbUserSid ||
                            ' set parent_id =:1, HIERARCHY_ID =:2 where parent_id =:3'
            using l_KenanId, l_KenanId, l_parent_id;
        
          --patch child count for NEW CA
          EXECUTE IMMEDIATE 'select count(*) from arbor.cmf' || '@' ||
                            l_DbUserSid ||
                            ' where parent_id = :l_parent_id and account_type =1'
            into l_CountChild
            using l_KenanId;
        
          EXECUTE IMMEDIATE 'update arbor.cmf' || '@' || l_DbUserSid ||
                            ' set CHILD_COUNT = :l_CountChild  where account_no = :l_parent_id and account_type =0'
            using l_CountChild, l_KenanId;
        
          --patch child count for OLD CA      
          EXECUTE IMMEDIATE 'select count(*) from arbor.cmf' || '@' ||
                            l_DbUserSid ||
                            ' where parent_id = :l_parent_id and account_type =1'
            into l_CountChild
            using l_parent_id;
        
          EXECUTE IMMEDIATE 'update arbor.cmf' || '@' || l_DbUserSid ||
                            ' set CHILD_COUNT = :l_CountChild  where account_no = :l_parent_id and account_type =0'
            using l_CountChild, l_parent_id;
        
        else
          null;
        end if;
      
      ELSIF c_UpChild.Is_Processed = 'T' THEN
        EXECUTE IMMEDIATE 'select count(*) from arbor.cmf' || '@' || l_DbUserSid ||
                          ' where parent_id = :l_parent_id and account_type =1'
          into l_CountChild
          using l_parent_id;
      
        EXECUTE IMMEDIATE 'update arbor.cmf' || '@' || l_DbUserSid ||
                          ' set CHILD_COUNT = :l_CountChild  where account_no = :l_parent_id and account_type =0'
          using l_CountChild, l_parent_id;
      
      END IF;
    
      update src_acct_dm
         set is_processed = 'S'
       where acct_nbr_97 = c_UpChild.acct_nbr_97
         and src_id = c_UpChild.src_id;
    
      l_Count := l_Count + 1;
      if mod(l_Count, 5000) = 0 then
        commit;
      end if;
    end loop;
  end loop;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('Error ACCOUNT_NO is:' || l_ACCOUNT_NO);
    DBMS_OUTPUT.put_line(SUBSTR(SQLERRM, 1, 200));
    ROLLBACK;
END patch_CountChild_DM;
/