存储过程etl_bas_asset_call_x_new.prc

包括两个关于java高级的小程序工程项目

      ERROR_MSG := sqlcode || ':插入BAS_ASSET_CALL_X表出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,插入BAS_ASSET_CALL_X表出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --本地通话次数前3
  begin
    insert into tmp_call_cnt_top3_new
      (area_code, stats_month, call_cnt, serv_id)
      select area_code, stats_month, sum(call_cnt), serv_id
        from (select area_code,
                     stats_month,
                     sum(call_cnt) call_cnt,
                     serv_id,
                     called_nbr,
                     row_number() over(partition by serv_id order by sum(call_cnt) desc) rn
                from tmp_asset_call_bill_new                 p1,
                     zdyx_inf.inf_njf_sta_tree_struct_item_t p2
               where p1.call_type = p2.item_id
                 and p2.tree_type_id = 2123
                 and substr(p2.node_id, 1, 4) between '0204' and '0205'
                 and p2.item_name not like '%优惠%'
                 and p1.area_code = var_area_code
                 and p1.stats_month = var_etl_month
                 and p2.data_month = var_etl_month
               group by area_code, stats_month, called_nbr, serv_id)
       where rn <= 3
       group by area_code, stats_month, serv_id;
  exception
    when others then
      ERROR_MSG := sqlcode || ':更新BAS_ASSET_CALL_X表集中度出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,更新BAS_ASSET_CALL_X表集中度出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --本地计费时长前3
  begin
    insert into tmp_call_dur_top3_new
      (area_code, stats_month, call_dur, serv_id)
      select area_code, stats_month, sum(call_dur), serv_id
        from (select area_code,
                     stats_month,
                     sum(call_dur) call_dur,
                     serv_id,
                     called_nbr,
                     row_number() over(partition by serv_id order by sum(call_dur) desc) rn
                from tmp_asset_call_bill_new                 p1,
                     zdyx_inf.inf_njf_sta_tree_struct_item_t p2
               where p1.call_type = p2.item_id
                 and p2.tree_type_id = 2123
                 and substr(p2.node_id, 1, 4) between '0204' and '0205'
                 and p2.item_name not like '%优惠%'
                 and p1.area_code = var_area_code
                 and p1.stats_month = var_etl_month
                 and p2.data_month = var_etl_month
               group by area_code, stats_month, called_nbr, serv_id)
       where rn <= 3
       group by area_code, stats_month, serv_id;
  exception
    when others then
      ERROR_MSG := sqlcode || ':更新BAS_ASSET_CALL_X表集中度出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,更新BAS_ASSET_CALL_X表集中度出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --长途通话次数前3
  begin
    insert into tmp_tol_call_cnt_top3_new
      (area_code, stats_month, call_cnt, serv_id)
      select area_code, stats_month, sum(call_cnt), serv_id
        from (select area_code,
                     stats_month,
                     sum(call_cnt) call_cnt,
                     serv_id,
                     called_nbr,
                     row_number() over(partition by serv_id order by sum(call_cnt) desc) rn
                from tmp_asset_call_bill_new                 p1,
                     zdyx_inf.inf_njf_sta_tree_struct_item_t p2
               where p1.call_type = p2.item_id
                 and p2.tree_type_id = 2123
                 and substr(p2.node_id, 1, 4) between '0207' and '0209'
                 and p2.item_name not like '%优惠%'
                 and p1.area_code = var_area_code
                 and p1.stats_month = var_etl_month
                 and p2.data_month = var_etl_month
               group by area_code, stats_month, called_nbr, serv_id)
       where rn <= 3
       group by area_code, stats_month, serv_id;
  exception
    when others then
      ERROR_MSG := sqlcode || ':更新BAS_ASSET_CALL_X表集中度出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,更新BAS_ASSET_CALL_X表集中度出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --长途计费时长前3
  begin
    insert into tmp_tol_call_dur_top3_new
      (area_code, stats_month, call_dur, serv_id)
      select area_code, stats_month, sum(call_dur), serv_id
        from (select area_code,
                     stats_month,
                     sum(call_dur) call_dur,
                     serv_id,
                     called_nbr,
                     row_number() over(partition by serv_id order by sum(call_dur) desc) rn
                from tmp_asset_call_bill_new                 p1,
                     zdyx_inf.inf_njf_sta_tree_struct_item_t p2
               where p1.call_type = p2.item_id
                 and p2.tree_type_id = 2123
                 and substr(p2.node_id, 1, 4) between '0207' and '0209'
                 and p2.item_name not like '%优惠%'
                 and p1.area_code = var_area_code
                 and p1.stats_month = var_etl_month
                 and p2.data_month = var_etl_month
               group by area_code, stats_month, called_nbr, serv_id)
       where rn <= 3
       group by area_code, stats_month, serv_id;
  exception
    when others then
      ERROR_MSG := sqlcode || ':更新BAS_ASSET_CALL_X表集中度出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,更新BAS_ASSET_CALL_X表集中度出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --本地通话次数集中度
  begin
    update bas_asset_call_x p1
       set lcl_cnt_cnct = (select trunc(call_cnt / lcl_cnt_cnct_total, 6)
                             from tmp_call_cnt_top3_new p2
                            where p1.serv_id = p2.serv_id
                              and p2.area_code = var_area_code
                              and p2.stats_month = var_etl_month
                              and rownum = 1)
     where exists (select serv_id
              from tmp_call_cnt_top3_new p2
             where p1.serv_id = p2.serv_id
               and p2.area_code = var_area_code
               and p2.stats_month = var_etl_month)
       and p1.area_code = var_area_code
       and p1.pid_12 = var_pid_12;
  exception
    when others then
      ERROR_MSG := sqlcode || ':更新BAS_ASSET_CALL_X表集中度出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,更新BAS_ASSET_CALL_X表集中度出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --本地计费时长集中度
  begin
    update bas_asset_call_x p1
       set lcl_dur_cnct = (select trunc(Call_Dur / lcl_dur_cnct_total, 6)
                             from tmp_call_cnt_top3_new p2
                            where p1.serv_id = p2.serv_id
                              and p2.area_code = var_area_code
                              and p2.stats_month = var_etl_month
                              and rownum = 1)
     where exists (select serv_id
              from tmp_call_dur_top3_new p2
             where p1.serv_id = p2.serv_id
               and p2.area_code = var_area_code
               and p2.stats_month = var_etl_month)
       and p1.area_code = var_area_code
       and p1.pid_12 = var_pid_12;
  exception
    when others then
      ERROR_MSG := sqlcode || ':更新BAS_ASSET_CALL_X表集中度出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,更新BAS_ASSET_CALL_X表集中度出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --长途通话次数集中度
  begin
    update bas_asset_call_x p1
       set tol_cnt_cnct = (select trunc(call_cnt / tol_cnt_cnct_total, 6)
                             from tmp_tol_call_cnt_top3_new p2
                            where p1.serv_id = p2.serv_id
                              and p2.area_code = var_area_code
                              and p2.stats_month = var_etl_month
                              and rownum = 1)
     where exists (select serv_id
              from tmp_tol_call_cnt_top3_new p2
             where p1.serv_id = p2.serv_id
               and p2.area_code = var_area_code
               and p2.stats_month = var_etl_month)
       and p1.area_code = var_area_code
       and p1.pid_12 = var_pid_12;
  exception
    when others then
      ERROR_MSG := sqlcode || ':更新BAS_ASSET_CALL_X表集中度出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,更新BAS_ASSET_CALL_X表集中度出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --长途计费时长集中度
  begin
    update bas_asset_call_x p1
       set tol_dur_fact_cnct = (select trunc(Call_Dur /
                                             tol_dur_fact_cnct_total,
                                             6)
                                  from tmp_tol_call_cnt_top3_new p2
                                 where p1.serv_id = p2.serv_id
                                   and p2.area_code = var_area_code
                                   and p2.stats_month = var_etl_month
                                   and rownum = 1)
     where exists (select serv_id
              from tmp_tol_call_cnt_top3_new p2
             where p1.serv_id = p2.serv_id
               and p2.area_code = var_area_code
               and p2.stats_month = var_etl_month)
       and p1.area_code = var_area_code
       and p1.pid_12 = var_pid_12;
  exception
    when others then
      ERROR_MSG := sqlcode || ':更新BAS_ASSET_CALL_X表集中度出错';
      --写日志
      pro_execute_log('zdyx_analyze',
                      '模块：话单,更新BAS_ASSET_CALL_X表集中度出错',
                      null,
                      'ETL_BAS_ASSET_CALL_X',
                      var_start_time,
                      1,
                      var_area_code,
                      var_etl_month);
      rollback;
      return;
  end;
  commit;
  --写日志
  pro_execute_log('zdyx_analyze',
                  '模块：话单,插入BAS_ASSET_CALL_X表成功',
                  null,
                  'ETL_BAS_ASSET_CALL_X',
                  var_start_time,
                  0,
                  var_area_code,
                  var_etl_month);
end ETL_BAS_ASSET_CALL_X_NEW;
/