PROCEDURE sp_finance_recharge_audit (currentdate   IN DATE,
                                        numdays       IN NUMBER)
   AS
      v_parvalue         NUMBER (10);                               --交易面额
      v_price            NUMBER (19);                               --到账金额
      v_operator_id      NUMBER (19);             --充值类型(移动，联通，电信)
      v_supply_id        NUMBER (19);                                -- 商户id
      v_total_parvalue   NUMBER (19);                               --充值面额
      v_inmoney          NUMBER (19);                               --进货成本
      v_outmoney         NUMBER (19);                               --出货价格
      v_statdate         DATE;                                      --统计时间
      v_date             DATE;                                  --创建更新时间
      auditbegindate     DATE;                                  --审核开始日期
      auditenddate       DATE;                                  --审核结束日期
   BEGIN
      v_parvalue := 0;                                              --交易面额
      v_price := 0;                                                 --到账金额
      v_operator_id := 1000;                      --充值类型(移动，联通，电信)
      v_supply_id := 0;                                               --商户id
      v_total_parvalue := 0;                                        --充值面额
      v_inmoney := 0;                                               --进货成本
      v_outmoney := 0;                                              --出货价格
      v_statdate := TO_DATE ('2012-05-30', 'YYYY-MM-DD');           --统计时间
      v_date := TO_DATE ('2012-05-30', 'YYYY-MM-DD');           --创建更新时间
      auditbegindate := TO_DATE ('2012-05-30', 'YYYY-MM-DD');   --审核开始日期
      auditenddate := TO_DATE ('2012-05-30', 'YYYY-MM-DD');     --审核结束日期

      --审核开始日期
      SELECT   TRUNC (currentdate - numdays) INTO auditbegindate FROM DUAL;

      --审核结束日期
      SELECT   TRUNC (currentdate - numdays + 1) INTO auditenddate FROM DUAL;

      --创建更新时间
      SELECT   currentdate - numdays + 1 INTO v_date FROM DUAL;

      --统计时间
      SELECT   TRUNC (currentdate - numdays) INTO v_statdate FROM DUAL;

      --定义游标(充值卡面额,进货金额)

      DECLARE
         CURSOR other_audit
         IS
              SELECT   SUM (oi.parvalue),
                       oi.offer_id,
                       oi.operator_id,
                       oi.parvalue
                FROM   tab_topup_orderitem oi
               WHERE       oi.offer_id IS NOT NULL
                       AND oi.stat_sky_date IS NULL
                       AND oi.successdate >= auditbegindate
                       AND oi.successdate < auditenddate
            GROUP BY   oi.offer_id, oi.operator_id, oi.parvalue;
      --开始使用游标取数据

      BEGIN
         OPEN other_audit;

         LOOP
            FETCH other_audit
               INTO   v_total_parvalue, v_supply_id, v_operator_id, v_parvalue;

            --游标取不到数据则退出
            EXIT WHEN other_audit%NOTFOUND;

            --实际到账金额
            SELECT   SUM (oi.llprice)
              INTO   v_price
              FROM   tab_topup_orderitem oi
             WHERE       oi.status_id = 82
                     AND oi.offer_id = v_supply_id
                     AND oi.operator_id = v_operator_id
                     AND oi.parvalue = v_parvalue
                     AND oi.stat_sky_date IS NULL
                     AND oi.successdate >= auditbegindate
                     AND oi.successdate < auditenddate;

            IF v_price IS NULL
            THEN
               v_price := 0;
            END IF;

            --进货金额
            SELECT   SUM (bml.money) * 100
              INTO   v_inmoney
              FROM   tab_topup_orderitem oi,
                     tab_topup_moneylist tml,
                     tab_base_moneylist bml
             WHERE       oi.id = tml.item_id
                     AND tml.moneylist_id = bml.id
                     AND bml.tradetype_id = 338
                     AND oi.status_id = 82
                     AND oi.offer_id = v_supply_id
                     AND oi.operator_id = v_operator_id
                     AND oi.parvalue = v_parvalue
                     AND oi.stat_sky_date IS NULL
                     AND oi.successdate >= auditbegindate
                     AND oi.successdate < auditenddate;

            IF v_inmoney IS NULL
            THEN
               v_inmoney := 0;
            END IF;

            --出货价格
            SELECT   ABS (SUM (bml.money * oi.llprice / oi.parvalue) * 100)
              INTO   v_outmoney
              FROM   tab_topup_orderitem oi,
                     tab_topup_moneylist tml,
                     tab_base_moneylist bml
             WHERE       oi.id = tml.item_id
                     AND tml.moneylist_id = bml.id
                     AND bml.tradetype_id = 225
                     AND oi.status_id = 82
                     AND oi.offer_id = v_supply_id
                     AND oi.operator_id = v_operator_id
                     AND oi.parvalue = v_parvalue
                     AND oi.stat_sky_date IS NULL
                     AND oi.successdate >= auditbegindate
                     AND oi.successdate < auditenddate;

            IF v_outmoney IS NULL
            THEN
               v_outmoney := 0;
            END IF;

            INSERT INTO tab_topup_stat_supply (id,
                                               GROUP_ID,
                                               parmoney,
                                               actualmoney,
                                               inmoney,
                                               outmoney,
                                               correctpar,
                                               correctprice,
                                               status_id,
                                               batch,
                                               createdate,
                                               lastdate,
                                               operator_id,
                                               parvalue,
                                               statdate)
              VALUES   (seq_tab_topup_stat_supply.NEXTVAL,
                        v_supply_id,
                        v_total_parvalue,
                        v_price,
                        v_inmoney,
                        v_outmoney,
                        NULL,
                        NULL,
                        '',
                        NULL,
                        v_date,
                        v_date,
                        v_operator_id,
                        v_parvalue,
                        v_statdate);

            -- 更新统计日期
            UPDATE   tab_topup_orderitem oi
               SET   oi.stat_sky_date = v_statdate
             WHERE       oi.parvalue = v_parvalue
                     -- and oi.area_id = v_area_id
                     AND oi.operator_id = v_operator_id
                     AND oi.offer_id = v_supply_id
                     AND oi.stat_sky_date IS NULL
                     AND oi.successdate >= auditbegindate
                     AND oi.successdate < auditenddate;

            COMMIT;
         END LOOP;

         CLOSE other_audit;
      END;
   --发生异常时返回错误码
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'SUPPLY_AUDIT ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!'
         );
         ROLLBACK;
   END sp_finance_recharge_audit;
END pkg_finance_agent_supply_audit;
/

2. 财务审计存储过程调用

CREATE OR REPLACE PROCEDURE TOPUP.sp_finance_report_audit
AS
BEGIN
   FOR i IN 1 .. 1
   LOOP
      pkg_finance_agent_supply_audit.sp_finance_agent_addmoney (SYSDATE, i);

      pkg_finance_agent_supply_audit.sp_finance_agent_audit (SYSDATE, i);

      pkg_finance_agent_supply_audit.sp_finance_supply_audit (SYSDATE, i);

      pkg_finance_agent_supply_audit.sp_finance_recharge_audit (SYSDATE, i);
     
   -- pkg_finance_agent_supply_audit.sp_finance_gdlianlian_audit(sysdate,i);

   -- pkg_finance_agent_supply_audit.sp_finance_zjlianlian_audit(sysdate,i);

   END LOOP;
END;
/

3.财务审计任务排程

DECLARE
  X NUMBER;
BEGIN
  SYS.DBMS_JOB.SUBMIT
  ( job       => X
   ,what      => 'TOPUP.SP_FINANCE_REPORT_AUDIT;'
   ,next_date => to_date('03/07/2012 03:30:00','dd/mm/yyyy hh24:mi:ss')
   ,interval  => 'TRUNC(SYSDATE+1)+210/1440'
   ,no_parse  => FALSE
  );
  SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
COMMIT;
END;
/