ctthbpreba_acct_charge.prc

每天调用存储过程的列表

create or replace procedure ctthbpreba.ctthbpreba_acct_charge(date_month in varchar2) as

  --date_month varchar2(16);
  /*
  modified 2006-05-25
  */
begin
  -- date_month := to_char(add_months(sysdate,-1),'yyyymm');
  --应收用户事实表
  delete dm_a_acct_serv where acct_month = date_month;

  insert into dm_a_acct_serv
    select acct_month,
           city_id,
           district_id,
           office_id,
           business_area_id,
           exchange_id,
           serv_type_id,
           billing_type_id,
           bill_area_id,
           time_seg_id,
           serv_state_id,
           consume_lvl_id,
           count(distinct serv_id),
           sum(charge),
           sum(due_charge)
      from ods_a_acct_serv
     where serv_id <> 0
       and acct_month = date_month
     group by acct_month,
              city_id,
              district_id,
              office_id,
              business_area_id,
              exchange_id,
              serv_type_id,
              billing_type_id,
              bill_area_id,
              time_seg_id,
              serv_state_id,
              consume_lvl_id;
  commit;

  --应收帐目事实表
  delete dm_a_acct_item2 where acct_month = date_month;

  insert into dm_a_acct_item2
    select month_id,
           city_id,
           district_id,
           exchange_id,
           serv_type_id,
           billing_type_id,
           acct_item_type_cd,
           sum(charge) / 100,
           sum(due_charge) / 100,
           office_id
      from ods_a_acct_item
     where month_id = date_month
     group by month_id,
              city_id,
              district_id,
              exchange_id,
              serv_type_id,
              billing_type_id,
              acct_item_type_cd,
              office_id;

  --应收优惠事实表
  delete dm_a_acct_disct where acct_month = date_month;

  insert into dm_a_acct_disct
    select acct_month,
           city_id,
           district_id,
           office_id,
           business_area_id,
           exchange_id,
           count(serv_id),
           count(case
                   when disct_charge <> 0 then
                    1
                 end),
           sum(charge) before_charge,
           sum(disct_charge),
           sum(due_charge) after_charge,
           sum(adjust_charge)
      from ods_a_acct_serv
     where acct_month = date_month
     group by acct_month,
              city_id,
              district_id,
              office_id,
              business_area_id,
              exchange_id;

  commit;
end;
/