patch_gd_dm.prc

数据迁移使用的ETL程序包

create or replace procedure patch_gd_dm(P_MODE in varchar2) is
  v_ErrorCode      NUMBER; --Code for the error
  v_ErrorMsg       VARCHAR2(1000); --Message text for the error
  v_currentUser    VARCHAR2(20); --Current database user
  v_Information    VARCHAR2(100);
  l_subscrno       external_id_equip_map.subscr_no%type;
  l_subscrnoresets external_id_equip_map.subscr_no_resets%type := 0;
  l_accountNo_main external_id_equip_map.account_no%type;
  l_accountNo      external_id_equip_map.account_no%type;
  l_serverID       external_id_equip_map.server_id%type;
  l_trackingid     customer_contract.tracking_id%type;
  l_trackingidserv customer_contract.tracking_id_serv%type;
  l_acctnbr97      src_serv.acct_nbr_97%type;
  l_startdtsi      src_serv.start_dt%type;
  l_enddtsi        src_serv.end_dt%type;
  l_serviceinactivedtpatch date;
  l_serviceactivedtpacht date;

  l_admid varchar2(10) := pkg_ken_util.g_MIGADM_sid;
  l_catid varchar2(10) := pkg_ken_util.g_MIGCAT_Sid;

  --v_tablename varchar2(40);
  l_count smallint := 0;

  cur_si SRC_CAH_EXT_DATA_PATCH%rowtype;

  v_NextBillDate      date;
  v_PrevCutDate       date;
  l_IsDesignatedAccct number;
  l_GroupId           SRC_CAH_EXT_DATA_PATCH.GROUP_ID%type;
  l_ServId            SRC_CAH_EXT_DATA_PATCH.Serv_Id%type;
  l_KenanDiscountId   SRC_CAH_EXT_DATA_PATCH.Kenan_Discount_Id%type;

  type rec_cah is ref cursor;
  l_cah rec_cah;

  --DEFINE EXCEPTION
  e_GroupIdErr EXCEPTION;
  e_OldIdErr EXCEPTION;
  e_ServIdErr EXCEPTION;
  pragma EXCEPTION_INIT(e_GroupIdErr, -20101);
  pragma EXCEPTION_INIT(e_OldIdErr, -20102);
  pragma EXCEPTION_INIT(e_ServIdErr, -20103);
begin
  v_NextBillDate := ADD_MONTHS(pkg_ken_util.g_Cutoff_Date, 2) + 4;
  v_PrevCutDate  := pkg_ken_util.g_Cutoff_Date;

  OPEN l_cah FOR
    select *
      from src_cah_ext_data_patch a
     where not exists (select 1
              from src_gd_status b
             where b.group_id = a.group_id
               and b.acct_nbr_97 = a.acct_nbr_97
               and b.serv_id = a.serv_id
               and b.kenan_discount_id = a.kenan_discount_id
               and b.src_id = a.src_id
               and b.is_processed = 'S');
  <<NEXTLOOP01555>>
  LOOP
    FETCH l_cah
      INTO cur_si;
    EXIT WHEN l_cah%NOTFOUND;
    BEGIN
      l_count           := 0; ---initializing  
      l_GroupId         := cur_si.group_id;
      l_ServId          := cur_si.serv_id;
      l_KenanDiscountId := cur_si.kenan_discount_id;
      BEGIN
        execute IMMEDIATE 'select account_no, tracking_id, tracking_id_serv from R2LOAD.BAK_KB_BIL_GD_TBL' ||
                          l_catid ||
                          ' where agreement_id = :1 and component_id =:2'
          into l_accountNo_main, l_trackingid, l_trackingidserv
          using cur_si.group_id, cur_si.kenan_discount_id;
      EXCEPTION
        WHEN OTHERS THEN
          raise_application_error(-20101,
                                  'Not Data Found in BAK_KB_BIL_GD_TBL');
      END;
      BEGIN
        select kenan_id, ext_data_4, server_id
          into l_subscrno, l_accountNo, l_ServerId
          from src_serv_status
         where old_id = cur_si.serv_id;
        --and src_id = cur_si.src_id; ---or src_id.....
      EXCEPTION
        WHEN OTHERS THEN
          raise_application_error(-20102,
                                  'Not Data Found in SRC_SERV_STATUS');
      END;
    
      BEGIN
        select si.acct_nbr_97, si.start_dt, si.end_dt
          into l_acctnbr97, l_startdtsi, l_enddtsi
          from src_serv si
         where serv_id = cur_si.serv_id;
        --and src_id = cur_si.src_id;
      exception
        WHEN OTHERS THEN
          raise_application_error(-20103, 'Not Data Found in SRC_SERV');
      END;
    
      if l_enddtsi is null then
        l_enddtsi := to_date('3000-1-1', 'YYYY-MM-DD');
      end if;
      
      l_serviceactivedtpacht := trunc(cur_si.service_start_dt);
      
      if trunc(cur_si.service_start_dt) = trunc(cur_si.service_end_dt) then
        l_serviceinactivedtpatch := trunc(cur_si.service_end_dt);
      else
        l_serviceinactivedtpatch := trunc(least(l_enddtsi, cur_si.service_end_dt));
      end if;
    
      execute immediate 'select count(1) from arbor.CT_CAH_EXT_DATA' ||
                        l_admid ||
                        ' where subscr_no = :1 and subscr_no_resets = :2 and tracking_id = :3 and tracking_id_serv =:4'
        into l_count
        using l_subscrno, l_subscrnoresets, l_trackingid, l_trackingidserv;
    
      if l_count = 0 then
        execute immediate 'insert into arbor.CT_CAH_EXT_DATA' || l_admid ||
                          ' values
        (:l_AccountNo,
         :l_TrackingID,
         :l_trackingidserv,
         :l_SubscrNo,
         :l_SubscrNoResets,
         :is_service_exclude,
         :l_startdtsi,
         :l_enddtsi) '
          using l_AccountNo, l_TrackingID, l_trackingidserv, l_SubscrNo, l_SubscrNoResets, cur_si.is_service_exclude, l_serviceactivedtpacht, l_serviceinactivedtpatch;
      end if;
    
      execute immediate 'select count(1) from arbor.CT_CONTRACT_HQ_GROUP_MAP' ||
                        l_admid ||
                        ' where account_no = :1 and tracking_id = :2 and tracking_id_serv = :3 '
        into l_count
        using l_accountNo, l_trackingid, l_trackingidserv;
    
      if l_count = 0 then
        if l_accountNo = l_accountNo_main then
          l_IsDesignatedAccct := 1;
        else
          l_IsDesignatedAccct := 0;
        end if;
        execute immediate ' insert into arbor.CT_CONTRACT_HQ_GROUP_MAP' ||
                          l_admid || ' (Account_No,
         Tracking_Id,
         tracking_id_serv,
         start_dt,
         end_dt,
         proportion,
         exclude,
         BILL_PERIOD,
         NEXT_BILL_DATE,
         PREV_CUTOFF_DATE,
         CUST_SERV_ID,
         IS_DESIGNATED_ACCT)
      values
        (:l_AccountNo,
         :l_TrackingId,
         :l_trackingidserv,
         :l_StartDt,
         :l_EndDt,
         -1,
         :l_Exclude,
         :l_BP,
         :l_NBD,
         :l_PCD,
         :l_CUST_SERV_ID,
         :l_IsDesignatedAccct)'
          using l_AccountNo, l_TrackingId, l_trackingidserv, l_serviceactivedtpacht, l_serviceinactivedtpatch, 0, 'M01', v_NextBillDate, v_PrevCutDate, l_ServerId, l_IsDesignatedAccct;
      elsif l_count > 0 then
        execute immediate ' update arbor.CT_CONTRACT_HQ_GROUP_MAP' ||
                          l_admid || ' set start_dt = :1' ||
                          ' where tracking_id =:2 and tracking_id_serv = :3 and account_no = :4 and start_dt > :5'
          Using l_serviceactivedtpacht, l_TrackingId, l_trackingidserv, l_AccountNo, l_serviceactivedtpacht;
      
        execute immediate ' update arbor.CT_CONTRACT_HQ_GROUP_MAP' ||
                          l_admid || ' set end_dt = :1' ||
                          ' where tracking_id = :2 and tracking_id_serv = :3 and account_no = :4 and end_dt < :5'
          Using l_serviceinactivedtpatch, l_TrackingId, l_trackingidserv, l_AccountNo, l_serviceinactivedtpatch;
      end if;
    exception
      WHEN OTHERS THEN
        v_ErrorCode := SQLCODE;
        v_ErrorMsg  := SUBSTR(SQLERRM, 1, 1000);
        IF P_MODE = 'NEW' THEN
          insert into src_gd_status
          values
            (cur_si.GROUP_ID,
             cur_si.ACCT_NBR_97,
             cur_si.SERV_ID,
             cur_si.KENAN_DISCOUNT_ID,
             cur_si.SRC_ID,
             v_ErrorCode,
             v_ErrorMsg,
             'F',
             NULL,
             NULL);
        elsif P_MODE = 'FAIL' then
          update src_gd_status
             set is_processed = 'F',
                 ERR_CODE     = v_ErrorCode,
                 ERR_MSG      = v_ErrorMsg
           where GROUP_ID = cur_si.group_id
             and ACCT_NBR_97 = cur_si.acct_nbr_97
             and SERV_ID = cur_si.serv_id
             and KENAN_DISCOUNT_ID = cur_si.kenan_discount_id
             and SRC_ID = cur_si.src_id;
        END IF;
        if sqlcode <= -20000 then
          GOTO NEXTLOOP01555;
        else
          raise;
        end if;
    end;
  
    IF P_MODE = 'NEW' THEN
      insert into src_gd_status
      values
        (cur_si.GROUP_ID,
         cur_si.ACCT_NBR_97,
         cur_si.SERV_ID,
         cur_si.KENAN_DISCOUNT_ID,
         cur_si.SRC_ID,
         null,
         null,
         'S',
         l_trackingid,
         l_trackingidserv);
    elsif P_MODE = 'FAIL' then
      update src_gd_status
         set is_processed     = 'S',
             tracking_id      = l_trackingid,
             tracking_id_serv = l_trackingidserv
       where GROUP_ID = cur_si.group_id
         and ACCT_NBR_97 = cur_si.acct_nbr_97
         and SERV_ID = cur_si.serv_id
         and KENAN_DISCOUNT_ID = cur_si.kenan_discount_id
         and SRC_ID = cur_si.src_id;
    END IF;
  
  END LOOP;
  commit;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    v_ErrorCode   := SQLCODE;
    v_ErrorMsg    := SUBSTR(SQLERRM, 1, 1000);
    v_CurrentUser := USER;
    v_Information := 'SUMMARY: Error encoutered on ' ||
                     TO_CHAR(SYSDATE, 'DD-MON-YY HH24:MI:SS') ||
                     ' by database user ' || v_CurrentUser;
    dbms_output.put_line(v_Information);
    dbms_output.put_line('ERRORCODE: ' || v_ErrorCode);
    dbms_output.put_line('ERRORMSG: ' || v_ErrorMsg);
  
    dbms_output.put_line('Group_Id: ' || l_GroupId);
    dbms_output.put_line('Serv_Id: ' || l_ServId);
    dbms_output.put_line('Kenan_Discount_Id: ' || l_KenanDiscountId);
END patch_gd_dm;
/