ctthbpreba_dm_arrear.prc

每天调用存储过程的列表

create or replace procedure ctthbpreba.ctthbpreba_dm_arrear as

  day_id   varchar2(16);
  last_day varchar2(16);
begin

  day_id   := to_char(sysdate, 'mm/dd/yyyy');
  last_day := to_char(sysdate - 1, 'yyyymmdd');
  --当前累计欠费
  delete dm_currarrear_sum;
  insert into dm_currarrear_sum
    select day_id,
           city_id,
           pure_arr_duration_id,
           serv_type_id,
           serv_state_id,
           arrear_seg_id,
           count(distinct serv_id),
           count(distinct acct_id),
           sum(due_charge),
           billing_type_id,
           district_id,
           office_id,
           exchange_id,
           bill_area_id
      from ods_arrear_sum
     group by day_id,
              city_id,
              pure_arr_duration_id,
              serv_type_id,
              serv_state_id,
              arrear_seg_id,
              billing_type_id,
              district_id,
              office_id,
              exchange_id,
              bill_area_id;
  --当前每月欠费
  delete dm_currarrear_mon;
  insert into dm_currarrear_mon
    select day_id,
           acct_month,
           city_id,
           serv_type_id,
           serv_state_id,
           arrear_seg_id,
           count(distinct serv_id),
           count(distinct acct_id),
           sum(due_charge),
           billing_type_id,
           district_id,
           office_id,
           exchange_id
      from ods_arrear_mon
     group by day_id,
              acct_month,
              city_id,
              serv_type_id,
              serv_state_id,
              arrear_seg_id,
              billing_type_id,
              district_id,
              office_id,
              exchange_id;
  --当前欠费时长
  --需要保存2个月的数据
  delete dm_currarrear_duration
   where substr(day_id, 0, 6) =
         substr(to_char(add_months(last_day(sysdate) + 0, -3), 'yyyymmdd'),
                0,
                6);
  delete dm_currarrear_duration where day_id = last_day;
  insert into dm_currarrear_duration
    select day_id,
           city_id,
           district_id,
           office_id,
           BUSINESS_AREA_ID,
           EXCHANGE_ID,
           BILL_AREA_ID,
           serv_type_id,
           arr_duration_id,
           count(distinct serv_id),
           count(distinct acct_id),
           sum(due_charge),
           billing_type_id
      from ods_arrear_mon
     group by day_id,
              city_id,
              district_id,
              office_id,
              BUSINESS_AREA_ID,
              EXCHANGE_ID,
              BILL_AREA_ID,
              serv_type_id,
              arr_duration_id,
              billing_type_id;

  --arrear_seg_id欠费层次(01-08) dim_arrear_seg
  --pure_arr_duration_id欠费时长(01-05)
  --dim_arrear_time_seg欠费时长表
  --帐户状态dim_acct_state
  --帐户类型dim_acct_class*/ 

  commit;

exception

  when others then
    p_error_log('dm_currarrear_duration', 'ctthbpreba_dm_arrear', sqlerrm);
    rollback;
  
end;
/