temp

--插入当天股份余额表   

 delete from audmon.H_stkasset where busi_date=v_busi_date and rgid = i_data_unit_code;
insert into audmon.H_stkasset
  (BUSI_DATE,
   BRANCH_CODE,
   SERVERID,
   CUSTID,
   ORGID,
   FUNDID,
   MONEYTYPE,
   MARKET,
   SECUID,
   SEAT,
   STKCODE,
   STKLASTBAL,
   STKBAL,
   STKAVL,
   STKBUY,
   STKSALE,
   STKBUYSALE,
   STKUNCOMEBUY,
   STKUNCOMESALE,
   STKFRZ,
   STKUNFRZ,
   STKNIGHTFRZ,
   STKTRDFRZ,
   STKTRDUNFRZ,
   STKFLAG,
   LASTBUYCOST,
   LASTPROFITCOST,
   BUYCOST,
   PROFITCOST,
   MKTVAL
  
   )
  select /*+ ordered*/ a.busi_date,
         a.BRANCH_CODE,
         a.SERVERID,
         a.CUSTID,
         a.ORGID,
         a.FUNDID,
         a.MONEYTYPE,
         a.MARKET,
         a.SECUID,
         a.SEAT,
         a.STKCODE,
         a.STKLASTBAL,
         a.STKBAL,
         a.STKAVL,
         a.STKBUY,
         a.STKSALE,
         a.STKBUYSALE,
         a.STKUNCOMEBUY,
         a.STKUNCOMESALE,
         a.STKFRZ,
         a.STKUNFRZ,
         a.STKNIGHTFRZ,
         a.STKTRDFRZ,
         a.STKTRDUNFRZ,
         a.STKFLAG,
         a.LASTBUYCOST,
         a.LASTPROFITCOST,
         a.BUYCOST,
         a.PROFITCOST,
         a.MKTVAL
    from jz31.tcl_stkasset a
   where exists (select 1
            from audmon.tb_core_account b
           where a.orgid = b.branch_code
             and a.fundid = to_number(b.capital_account))
     and a.busi_date = v_busi_date
     and a.orgid = i_data_unit_code;
 commit;
--循环补历史数据     --------------------------------------------------------------------------------
v_tradedate:=to_number(i_data_begin_date);
 delete from audmon.h_logasset where busi_date=v_tradedate and rgid = i_data_unit_code;
  delete from audmon.h_orderrec where busi_date=v_tradedate and rgid = i_data_unit_code;
  delete from audmon.h_loguser where busi_date=v_tradedate and rgid = i_data_unit_code;
    delete from audmon.h_logdetail where busi_date=v_tradedate and rgid = i_data_unit_code;
 

  loop
 
    delete from venture.tmp_32000020;
    insert into venture.tmp_32000020(orgid,fundid,capital_account)
    select branch_code,
           to_number(capital_account),
           capital_account
      from audmon.tb_core_account
     where (coalesce(f_tradedate,i_data_begin_date)>v_tradedate or v_tradedate=i_data_begin_date)
     and  branch_code = i_data_unit_code;

    insert into AUDMON.H_LOGASSET
      (BUSI_DATE,
       BRANCH_CODE,
       SERVERID,
       OPERDATE,
       CLEARDATE,
       BIZDATE,
       SNO,
       RELATIVESNO,
       CUSTID,
       CUSTNAME,
       FUNDID,
       MONEYTYPE,
       ORGID,
       BRHID,
       CUSTKIND,
       CUSTGROUP,
       FUNDKIND,
       FUNDLEVEL,
       FUNDGROUP,
       DIGESTID,
       FUNDEFFECT,
       FUNDBAL,
       SECUID,
       MARKET,
       BIZTYPE,
       STKCODE,
       STKTYPE,
       BANKCODE,
       STKNAME,
       STKEFFECT,
       STKBAL,
       ORDERID,
       TRDID,
       ORDERQTY,
       ORDERPRICE,
       BONDINTR,
       ORDERDATE,
       ORDERTIME,
       MATCHQTY,
       MATCHAMT,
       SEAT,
       MATCHTIMES,
       MATCHPRICE,
       MATCHTIME,
       MATCHCODE,
       FEE_JSXF,
       FEE_SXF,
       FEE_YHS,
       FEE_GHF,
       FEE_QSF,
       FEE_JYGF,
       FEE_JSF,
       FEE_ZGF,
       FEE_QTF,
       BSFLAG,
       FEEFRONT,
       SOURCETYPE,
       BANKID,
       AGENTID,
       OPERID,
       OPERWAY,
       OPERORG,
       OPERLEVEL,
       NETADDR,
       CHKOPER,
       PRIVILEGE,
       REMARK,
       ORDERSNO,
       PATHID,
       CANCELFLAG)
      select a.BUSI_DATE,
             a.BRANCH_CODE,
             a.SERVERID,
             a.OPERDATE,
             a.CLEARDATE,
             a.BIZDATE,
             a.SNO,
             a.RELATIVESNO,
             a.CUSTID,
             a.CUSTNAME,
             a.FUNDID,
             a.MONEYTYPE,
             a.ORGID,
             a.BRHID,
             a.CUSTKIND,
             a.CUSTGROUP,
             a.FUNDKIND,
             a.FUNDLEVEL,
             a.FUNDGROUP,
             a.DIGESTID,
             a.FUNDEFFECT,
             a.FUNDBAL,
             a.SECUID,
             a.MARKET,
             a.BIZTYPE,
             a.STKCODE,
             a.STKTYPE,
             a.BANKCODE,
             a.STKNAME,
             a.STKEFFECT,
             a.STKBAL,
             a.ORDERID,
             a.TRDID,
             a.ORDERQTY,
             a.ORDERPRICE,
             a.BONDINTR,
             a.ORDERDATE,
             a.ORDERTIME,
             a.MATCHQTY,
             a.MATCHAMT,
             a.SEAT,
             a.MATCHTIMES,
             a.MATCHPRICE,
             a.MATCHTIME,
             a.MATCHCODE,
             a.FEE_JSXF,
             a.FEE_SXF,
             a.FEE_YHS,
             a.FEE_GHF,
             a.FEE_QSF,
             a.FEE_JYGF,
             a.FEE_JSF,
             a.FEE_ZGF,
             a.FEE_QTF,
             a.BSFLAG,
             a.FEEFRONT,
             a.SOURCETYPE,
             a.BANKID,
             a.AGENTID,
             a.OPERID,
             a.OPERWAY,
             a.OPERORG,
             a.OPERLEVEL,
             a.NETADDR,
             a.CHKOPER,
             a.PRIVILEGE,
             a.REMARK,
             a.ORDERSNO,
             a.PATHID,
             a.CANCELFLAG
        from history.tb_H_LOGASSET a
       where a.busi_date = v_tradedate
         and exists(select 1 from venture.tmp_32000020 b
                     where a.orgid=b.orgid
                       and a.fundid=b.fundid
                   )
      ;
     
      insert into AUDMON.H_ORDERREC
      (BUSI_DATE,
       BRANCH_CODE,
       SERVERID,
       ORDERSNO,
       ORDERGROUP,
       ORDERID,
       ORDERDATE,
       OPERDATE,
       OPERTIME,
       CUSTID,
       CUSTKIND,
       CUSTGROUP,
       CUSTNAME,
       ORGID,
       BRHID,
       FUNDID,
       MONEYTYPE,
       FUNDKIND,
       FUNDLEVEL,
       FUNDGROUP,
       SECUID,
       RPTSECUID,
       MARKET,
       SEAT,
       STKCODE,
       STKNAME,
       STKTYPE,
       ORDERPRICE,
       BONDINTR,
       ORDERQTY,
       REPORTQTY,
       FUNDAVL,
       MATCHQTY,
       CANCELQTY,
       TRADEFEE,
       ORDERFRZAMT,
       CLEARAMT,
       MATCHAMT,
       ORDERTYPE,
       NIGHTFLAG,
       BSFLAG,
       INPUTBS,
       CANCELFLAG,
       COMBTYPE,
       REPORTTIME,
       ORDERSTATUS,
       RECNUM,
       SOURCETYPE,
       BANKCODE,
       BANKORDERID,
       BANKID,
       BANKFRZSNO,
       EXTDBFSNO,
       AGENTID,
       OPERID,
       OPERLEVEL,
       OPERORG,
       NETADDR,
       OPERWAY,
       REMARK)
      select A.BUSI_DATE,
             A.BRANCH_CODE,
             A.SERVERID,
             A.ORDERSNO,
             A.ORDERGROUP,
             A.ORDERID,
             A.ORDERDATE,
             A.OPERDATE,
             A.OPERTIME,
             A.CUSTID,
             A.CUSTKIND,
             A.CUSTGROUP,
             A.CUSTNAME,
             A.ORGID,
             A.BRHID,
             A.FUNDID,
             A.MONEYTYPE,
             A.FUNDKIND,
             A.FUNDLEVEL,
             A.FUNDGROUP,
             A.SECUID,
             A.RPTSECUID,
             A.MARKET,
             A.SEAT,
             A.STKCODE,
             A.STKNAME,
             A.STKTYPE,
             A.ORDERPRICE,
             A.BONDINTR,
             A.ORDERQTY,
             A.REPORTQTY,
             A.FUNDAVL,
             A.MATCHQTY,
             A.CANCELQTY,
             A.TRADEFEE,
             A.ORDERFRZAMT,
             A.CLEARAMT,
             A.MATCHAMT,
             A.ORDERTYPE,
             A.NIGHTFLAG,
             A.BSFLAG,
             A.INPUTBS,
             A.CANCELFLAG,
             A.COMBTYPE,
             A.REPORTTIME,
             A.ORDERSTATUS,
             A.RECNUM,
             A.SOURCETYPE,
             A.BANKCODE,
             A.BANKORDERID,
             A.BANKID,
             A.BANKFRZSNO,
             A.EXTDBFSNO,
             A.AGENTID,
             A.OPERID,
             A.OPERLEVEL,
             A.OPERORG,
             A.NETADDR,
             A.OPERWAY,
             A.REMARK
        from history.tb_H_ORDERREC a
       where a.busi_date = v_tradedate
         and exists(select 1 from venture.tmp_32000020 b
                     where a.orgid=b.orgid
                       and a.fundid=b.fundid
                   )
      ;
     
      insert into audmon.h_loguser (
      BUSI_DATE    ,
      BRANCH_CODE  ,
      SERVERID     ,
      OPERDATE     ,
      S_SYSDATE    ,
      SNO          ,
      RELATIVEDATE ,
      RELATIVESNO  ,
      ORGID        ,
      BRHID        ,
      OPERTIME     ,
      DIGESTID     ,
      CUSTID       ,
      NAME         ,
      FUNDID       ,
      MONEYTYPE    ,
      BUSITYPE     ,
      CUSTKIND     ,
      CUSTGROUP    ,
      FUNDKIND     ,
      FUNDLEVEL    ,
      FUNDGROUP    ,
      FUNDEFFECT   ,
      STKEFFECT    ,
      MARKET       ,
      BIZTYPE      ,
      STKCODE      ,
      SECUID       ,
      SOURCETYPE   ,
      BANKCODE     ,
      BANKID       ,
      BANKBRANCH   ,
      BANKNETPLACE ,
      CHECKFLAG    ,
      AGENTID      ,
      OPERID       ,
      OPERWAY      ,
      OPERORG      ,
      OPERLEVEL    ,
      NETADDR      ,
      CHKOPER      ,
      AGENTOPER    ,
      REMARK       )
      select  a.BUSI_DATE    ,
              a.BRANCH_CODE  ,
              a.SERVERID     ,
              a.OPERDATE     ,
              a.S_SYSDATE    ,
              a.SNO          ,
              a.RELATIVEDATE ,
              a.RELATIVESNO  ,
              a.ORGID        ,
              a.BRHID        ,
              a.OPERTIME     ,
              a.DIGESTID     ,
              a.CUSTID       ,
              a.NAME         ,
              a.FUNDID       ,
              a.MONEYTYPE    ,
              a.BUSITYPE     ,
              a.CUSTKIND     ,
              a.CUSTGROUP    ,
              a.FUNDKIND     ,
              a.FUNDLEVEL    ,
              a.FUNDGROUP    ,
              a.FUNDEFFECT   ,
              a.STKEFFECT    ,
              a.MARKET       ,
              a.BIZTYPE      ,
              a.STKCODE      ,
              a.SECUID       ,
              a.SOURCETYPE   ,
              a.BANKCODE     ,
              a.BANKID       ,
              a.BANKBRANCH   ,
              a.BANKNETPLACE ,
              a.CHECKFLAG    ,
              a.AGENTID      ,
              a.OPERID       ,
              a.OPERWAY      ,
              a.OPERORG      ,
              a.OPERLEVEL    ,
              a.NETADDR      ,
              a.CHKOPER      ,
              a.AGENTOPER    ,
              a.REMARK      
        from history.tb_h_loguser a
       where  a.busi_date = v_tradedate
         and exists(select 1 from venture.tmp_32000020 b
                     where a.orgid=b.orgid
                       and a.fundid=b.fundid
                   )
      ;
     
      insert into audmon.h_logdetail (
      BUSI_DATE   ,
      BRANCH_CODE ,
      SERVERID    ,
      OPERDATE    ,
      S_SYSDATE   ,
      SNO         ,
      KEYVALUE    ,
      OLDVALUE    ,
      NEWVALUE    ,
      REMARK      ,
      orgid    
      )
      select  a.BUSI_DATE   ,
              a.BRANCH_CODE ,
              a.SERVERID    ,
              a.OPERDATE    ,
              a.S_SYSDATE   ,
              a.SNO         ,
              a.KEYVALUE    ,
              a.OLDVALUE    ,
              a.NEWVALUE    ,
              a.REMARK      ,
              b.orgid
        from history.tb_h_logdetail a,
             audmon.h_loguser b
       where a.busi_date = v_tradedate
         and a.busi_date=b.busi_date
         and a.serverid=b.serverid
         and a.operdate=b.operdate
         and a.sno=b.sno
         and exists(select 1 from venture.tmp_32000020 c
                     where b.orgid=c.orgid
                       and b.fundid=c.fundid
                   )
      ;
  -------------------------------------------------
--这里的资金与股份余额表
 
for rt_1 in (select orgid,capital_account from  venture.tmp_32000020 where v_tradedate<>i_data_begin_date order by 1)--上面已经插入当天数据所以这里不补当天数据
 loop  
 delete from venture.tmp_32000020_fund_his;
 insert into venture.tmp_32000020_fund_his select /*+ ordered*/* from history.tb_increment_balance_his a where  a.branch_code = rt_1.orgid and a.capital_account = rt_1.capital_account
  and    v_tradedate between a.startdate and a.enddate;
insert into AUDMON.H_FUNDASSET
      (busi_date,
       BRANCH_CODE,
       SERVERID,
       ORGID,
       FUNDSEQ,
       FUNDID,
       MONEYTYPE,
       CUSTID,
       FUNDLASTBAL,
       FUNDBAL,
       FUNDAVL,
       OVERDRAW,
       FUNDBUY,
       FUNDSALE,
       FUNDUNCOMEBUY,
       FUNDUNCOMESALE,
       FUNDFRZ,
       FUNDUNFRZ,
       FUNDTRDFRZ,
       FUNDTRDUNFRZ,
       FUNDNIGHTFRZ,
       FUNDLOAN,
       FUNDFLAG,
       MARKETVALUE,
       FUNDSTANDBY,
       FUNDBUYSALE)
 select
         v_tradedate,
         '0000',
         0,
         a.branch_code,
         0 as fundseq,
         to_number(a.capital_account) as fundid,
         trim(b.bran_entry_value) as moneytype,
         to_number(a.customer_no) as custid,
         0 as FUNDLASTBAL,
         a.balance,
         0 as FUNDAVL,
         0 as OVERDRAW,
         0 as FUNDBUY,
         0 as FUNDSALE,
         0 as FUNDUNCOMEBUY,
         0 as FUNDUNCOMESALE,
         0 as FUNDFRZ,
         0 as FUNDUNFRZ,
         0 as FUNDTRDFRZ,
         0 as FUNDTRDUNFRZ,
         0 as FUNDNIGHTFRZ,
         0 as FUNDLOAN,
         0 as FUNDFLAG,
         0 as MARKETVALUE,
         0 as FUNDSTANDBY,
         0 as FUNDBUYSALE
     from venture.tmp_32000020_fund_his  a,
          params.tb_dict_entry_map  b
  where  a.currency_code = trim(b.entry_value)
  and    b.version_id = '31' and b.entry_code = '1304';
 
  --股份余额表历史补采
end loop;
 
 for rt_1 in (select orgid,capital_account from  venture.tmp_32000020 where v_tradedate<>i_data_begin_date order by 1)--上面已经插入当天数据所以这里不补当天数据
 loop  
delete from venture.tmp_32000020_stock_his;
 insert into venture.tmp_32000020_stock_his select /*+ ordered*/* from history.tb_increment_stock_balance_his a where  a.branch_code = rt_1.orgid and a.capital_account = rt_1.capital_account
  and    v_tradedate between a.startdate and a.enddate;
  insert into audmon.H_stkasset
  (BUSI_DATE,
   BRANCH_CODE,
   SERVERID,
   CUSTID,
   ORGID,
   FUNDID,
   MONEYTYPE,
   MARKET,
   SECUID,
   SEAT,
   STKCODE,
   STKLASTBAL,
   STKBAL,
   STKAVL,
   STKBUY,
   STKSALE,
   STKBUYSALE,
   STKUNCOMEBUY,
   STKUNCOMESALE,
   STKFRZ,
   STKUNFRZ,
   STKNIGHTFRZ,
   STKTRDFRZ,
   STKTRDUNFRZ,
   STKFLAG,
   LASTBUYCOST,
   LASTPROFITCOST,
   BUYCOST,
   PROFITCOST,
   MKTVAL
  
   )
  select
         v_tradedate as busi_date,
         '0000' as branch_code,
         0 as serverid,
         to_number(a.customer_no) as custid,
         a.branch_code as orgid,
         to_number(a.capital_account) as fundid,
         ' ' as MONEYTYPE,
         trim(b.bran_entry_value) as MARKET,
         trim(a.stockholder) as SECUID,
         ' ' as SEAT,
         trim(a.stock_code) as STKCODE,
         a.last_qty as STKLASTBAL,
         a.qty as STKBAL,
         0 as STKAVL,
         0 as STKBUY,
         0 as STKSALE,
         0 as STKBUYSALE,
         0 as STKUNCOMEBUY,
         0 as STKUNCOMESALE,
         0 as STKFRZ,
         0 as STKUNFRZ,
         0 as STKNIGHTFRZ,
         0 as STKTRDFRZ,
         0 as STKTRDUNFRZ,
         0 as STKFLAG,
         0 as LASTBUYCOST,
         0 as LASTPROFITCOST,
         0 as BUYCOST,
         0 as PROFITCOST,
         0 as MKTVAL
    from venture.tmp_32000020_stock_his a,
         params.tb_dict_entry_map  b
   where a.market_code = trim(b.entry_value)
     and b.version_id = '31'
     and b.entry_code = '1301';    
end loop;
     
      update audmon.tb_core_account a
         set a.f_tradedate=v_tradedate
       where coalesce(a.f_tradedate,i_data_begin_date)>to_char(v_tradedate) and branch_code = i_data_unit_code
      ;
      commit;
      v_tradedate:=to_number(get_last_n_tradedate(v_tradedate,-1));
    exit when v_tradedate  end loop;

  o_return_code := 0;
  o_return_msg  := '执行成功';

Exception
  when others then
    o_return_code := -1 * sqlcode;
    o_return_msg  := sqlerrm;
    rollback;
    --记录错误日志
    kingstar.pr_audit_write_errlog(i_task_id, -- 任务标识
                                   i_data_unit_code, -- 单位编号
                                   i_data_begin_date, -- 业务日期
                                   v_function_id, -- 功能标识
                                   v_program_name, -- 程序名
                                   v_error_line_num, -- 程序行号
                                   v_err_msg, -- 错误信息
                                   v_sqlcode, -- SQLCODE
                                   v_sqlstate -- SQLSTATE
                                   );
    commit;
END;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/11079750/viewspace-438949/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11079750/viewspace-438949/