create or replace procedure ctthbpreba.a_payment_all as
  date_day varchar2(16);
begin
  --于11點采集數據，凌晨2點運行該存儲過程，
  --od、ods表中放單日記錄
  --dw、dm表中放多日記錄
  --使用時只需將日進行更改即可，以后將日做成自動的
  date_day := to_char(sysdate - 1, 'yyyymmdd');
  execute immediate 'truncate table ods_a_payment';
  insert into ods_a_payment
    select b.pay_serial_nbr,
           b.batch_id,
           b.acct_id,
           f.serv_id,
           b.partner_id,
           f.CITY_ID,
           f.DISTRICT_ID,
           f.OFFICE_ID,
           f.exchange_id,
           f.business_area_id,
           f.acc_nbr,
           b.payment_method,
           b.operation_type,
           b.oper_serial_nbr,
           b.amount,
           b.fee_type,
           date_day pay_date,
           b.staff_id,
           b.staff_area_id,
           b.station_id,
           b.EXTERN_SERIAL_NBR,
           b.EXTERN_TIME,
           b.STATE,
           to_char(b.STATE_DATE, 'yyyymmdd') STATE_DATE,
           to_char(sysDATE, 'yyyymmdd') insert_date
      from (select pay_serial_nbr
              from od_a_payment@db_ctthbeba_od t, ods_f_acct a, ods_f_serv f
             where t.acct_id = a.acct_id(+)
               and a.acct_id = f.acct_id(+)
               and a.acc_nbr = f.acc_nbr(+)
               and a.area_code = f.area_code(+)
             group by pay_serial_nbr
            having count(pay_serial_nbr) = 1) a,
           od_a_payment@db_ctthbeba_od b,
           ods_f_acct t,
           ods_f_serv f
     where a.pay_serial_nbr = b.pay_serial_nbr
       and b.acct_id = t.acct_id(+)
       and t.acct_id = f.acct_id(+)
       and t.acc_nbr = f.acc_nbr(+)
       and t.area_code = f.area_code(+)
       and pay_date >= trunc(sysdate - 1, 'dd')
       and pay_date < trunc(sysdate, 'dd');
  insert into ods_a_payment
    select b.pay_serial_nbr,
           b.batch_id,
           b.acct_id,
           f.serv_id,
           b.partner_id,
           f.CITY_ID,
           f.DISTRICT_ID,
           f.OFFICE_ID,
           f.exchange_id,
           f.business_area_id,
           f.acc_nbr,
           b.payment_method,
           b.operation_type,
           b.oper_serial_nbr,
           b.amount,
           b.fee_type,
           date_day pay_date,
           b.staff_id,
           b.staff_area_id,
           b.station_id,
           b.EXTERN_SERIAL_NBR,
           b.EXTERN_TIME,
           b.STATE,
           to_char(b.STATE_DATE, 'yyyymmdd') STATE_DATE,
           to_char(SYSDATE, 'yyyymmdd') insert_date
      from (select pay_serial_nbr,
                   count(pay_serial_nbr),
                   max(f.serv_seq_nbr) serv_seq_nbr
              from od_a_payment@db_ctthbeba_od t, ods_f_acct a, ods_f_serv f
             where t.acct_id = a.acct_id(+)
               and a.acct_id = f.acct_id(+)
               and a.acc_nbr = f.acc_nbr(+)
               and a.area_code = f.area_code(+)
             group by pay_serial_nbr
            having count(pay_serial_nbr) > 1) a,
           od_a_payment@db_ctthbeba_od b,
           ods_f_acct t,
           ods_f_serv f
     where a.pay_serial_nbr = b.pay_serial_nbr
       and a.serv_seq_nbr = f.serv_seq_nbr
       and b.acct_id = t.acct_id(+)
       and t.acct_id = f.acct_id(+)
       and t.acc_nbr = f.acc_nbr(+)
       and t.area_code = f.area_code(+)
       and b.pay_date >= trunc(sysdate - 1, 'dd')
       and b.pay_date < trunc(sysdate, 'dd');

  commit;
  --刪除dw表中和日期一樣的舊數據
  delete dw_a_payment_day where day_id = date_day;
  --由ods到dw的匯總，同時完成將amount的單位由分到元的轉換
  insert into dw_a_payment_day
    select date_day,
           partner_id,
           CITY_ID,
           DISTRICT_ID,
           OFFICE_ID,
           EXCHANGE_ID,
           area_id,
           payment_method,
           operation_type,
           sum(amount) / 100,
           fee_type,
           staff_area_id,
           station_id,
           STATE,
           count(distinct serv_id)
      from ods_a_payment
     where pay_date = date_day
     group by partner_id,
              CITY_ID,
              DISTRICT_ID,
              OFFICE_ID,
              EXCHANGE_ID,
              area_id,
              payment_method,
              operation_type,
              fee_type,
              staff_area_id,
              station_id,
              STATE;

  --運行完a_payment_ods_dw完畢后，再調用該存儲過程，進行由dw到dm的匯總
  delete dm_a_payment_day where day_id = date_day;

  insert into dm_a_payment_day
    select date_day,
           CITY_ID,
           DISTRICT_ID,
           OFFICE_ID,
           EXCHANGE_ID,
           area_id,
           payment_method,
           sum(amount),
           sum(user_num)
      from dw_a_payment_day
     where day_id = date_day
     group by CITY_ID,
              DISTRICT_ID,
              OFFICE_ID,
              EXCHANGE_ID,
              area_id,
              payment_method;

  commit;

exception

  when others then
    p_error_log('dm_a_payment_day', 'a_payment_all', sqlerrm);
    rollback;
end;
/