ctthbpreba_dm_arrcallbackmon.prc

每天调用存储过程的列表

create or replace procedure ctthbpreba.ctthbpreba_dm_arrcallbackmon(month_id1 in varchar2) as
  year_id varchar2(8);
  --month_id varchar2(18);

begin
  year_id := substr(month_id1, 0, 4);
  --month_id := to_char(add_months(sysdate,-1),'yyyymm');

  --欠费回收月表
  delete dm_arrear_callback_mon
   where substr(month_id, 0, 4) =
         to_char(to_date('' || year_id || '', 'yyyy'), 'yyyy') - 3;
  --保留2年的
  insert into dm_arrear_callback_mon
    select substr(day_id, 0, 6),
           city_id,
           district_id,
           office_id,
           exchange_id,
           serv_type_id,
           arr_duration_id,
           sum(callback_fee),
           billing_type_id
      from dm_arrear_callback_day
     where substr(day_id, 0, 6) = month_id1
     group by substr(day_id, 0, 6),
              city_id,
              district_id,
              office_id,
              exchange_id,
              serv_type_id,
              arr_duration_id,
              billing_type_id;
  commit;
end;
/