ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 关于财务报表的相关案例实现(一)

关于财务报表的相关案例实现(一)

原创 Linux操作系统 作者:golden_zhou 时间:2012-07-02 13:56:22 0 删除 编辑
1. 财务审计包实现
CREATE OR REPLACE PACKAGE pkg_finance_agent_supply_audit
IS
   --代理商户加款统计
   PROCEDURE sp_finance_agent_addmoney (currentdate   IN DATE,
                                        numdays       IN NUMBER);
   --代理商财务统计
   PROCEDURE sp_finance_agent_audit (currentdate IN DATE, numdays IN NUMBER);
   --代充商财务统计(充值卡)
   PROCEDURE sp_finance_supply_audit (currentdate IN DATE, numdays IN NUMBER);
   --代充商财务统计(天瑞/太初/欧飞/19pay)
   PROCEDURE sp_finance_recharge_audit (currentdate   IN DATE,
                                        numdays       IN NUMBER);
END pkg_finance_agent_supply_audit;

--创建包体
/
 
CREATE OR REPLACE PACKAGE BODY pkg_finance_agent_supply_audit
IS
   PROCEDURE sp_finance_agent_addmoney (currentdate   IN DATE,
                                        numdays       IN NUMBER)
   AS
      v_group_id       NUMBER (19);                                   --商户id
      v_addmoney       NUMBER (10);                                 --加款金额
      v_balance        NUMBER (10);                         ----统计日商户余额
      v_money          NUMBER (10);                         --统计时刻当天余额
      v_account        NUMBER (10);                       --统计时刻商户总余额
      v_statdate       DATE;                                    --加款统计时间
      v_date           DATE;                                    --创建更新时间
      auditbegindate   DATE;                                    --审核开始日期
      auditenddate     DATE;                                    --审核结束日期
   BEGIN
      v_group_id := 0;                                                --商户id
      v_addmoney := 0;                                              --加款金额
      v_balance := 0;                                         --统计日商户余额
      v_money := 0;                                         --统计时刻当天余额
      v_account := 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 money_audit
         IS
            SELECT   id
              FROM   tab_base_group g
             WHERE   g.ctype = 'Agent' AND g.VALID = 1;
      /*
           SELECT   GROUP_ID, SUM (ml.money)
             FROM   tab_base_moneylist ml
            WHERE       ml.tradetype_id = 1031
                    AND ml.createdate >= auditbegindate
                    AND ml.createdate < auditenddate
         GROUP BY   GROUP_ID;
       */
      --开始使用游标取数据
      BEGIN
         OPEN money_audit;
         LOOP
            FETCH money_audit INTO   v_group_id;
            --游标取不到数据则退出
            EXIT WHEN money_audit%NOTFOUND;

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

            /*
             --统计日商户余额
              SELECT   SUM (ml.money), ma.money_account
                INTO   v_money, v_account
                FROM   tab_base_moneylist ml, tab_base_moneylist_account ma
               WHERE       ml.createdate >= TRUNC (SYSDATE)
                       AND ml.GROUP_ID = ma.GROUP_ID
                       AND ml.GROUP_ID = v_group_id
            GROUP BY   ma.GROUP_ID, ma.money_account;

            IF v_money IS NULL
            THEN
               v_money := 0;
            END IF;
            IF v_account IS NULL
            THEN
               v_account := 0;
            END IF;
            v_balance := v_account - v_money;
            */
            --统计代理加款
            SELECT   SUM (ml.money)
              INTO   v_addmoney
              FROM   tab_base_moneylist ml
             WHERE       ml.tradetype_id = 1031
                     AND ml.createdate >= auditbegindate
                     AND ml.createdate < auditenddate
                     AND GROUP_ID = v_group_id;
            IF v_addmoney IS NULL
            THEN
               v_addmoney := 0;
            END IF;

            --统计日商户余额
            SELECT   SUM (ml.money) * 100
              INTO   v_balance
              FROM   tab_base_moneylist ml
             WHERE   ml.createdate < auditenddate
                     AND ml.GROUP_ID = v_group_id;
            IF v_balance IS NULL
            THEN
               v_balance := 0;
            END IF;
            IF (v_addmoney > 0 OR v_balance != 0)
            THEN
               INSERT INTO tab_topup_stat_money (id,
                                                 GROUP_ID,
                                                 addmoney,
                                                 createdate,
                                                 lastdate,
                                                 statdate,
                                                 balance)
                 VALUES   (seq_tab_topup_stat_money.NEXTVAL,
                           v_group_id,
                           v_addmoney,
                           v_date,
                           v_date,
                           v_statdate,
                           v_balance);
            END IF;
            COMMIT;
         END LOOP;
         CLOSE money_audit;
      END;
   --发生异常时返回错误码
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'MONEY_AUDIT ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!'
         );
         ROLLBACK;
   END sp_finance_agent_addmoney;
   PROCEDURE sp_finance_agent_audit (currentdate IN DATE, numdays IN NUMBER)
   AS
      v_parvalue           NUMBER (10);                             --交易面额
      v_parvalue_id        NUMBER (19);                               --面额id
      v_code               VARCHAR2 (50);                           --面额代码
      v_area_id            NUMBER (19);                               --地区id
      v_operator_id        NUMBER (19);           --充值类型(移动,联通,电信)
      v_agent_id           NUMBER (19);                              -- 商户id
      v_time_id            NUMBER (19);            -- 到账时间(10分钟,24小时)
      v_total_parvalue     NUMBER (19);                             --充值面额
      v_price              NUMBER (19);                             --到账金额
      v_out_price          NUMBER (19);                             --出货价格
      v_agio               NUMBER (19);                             --产品费率
      v_stock_money        NUMBER (19);                             --出货价格
      v_stock_money_card   NUMBER (10);                           --外购卡价格
      v_stock_money_sky    NUMBER (19);                             --空充价格
      v_statdate           DATE;                                    --统计时间
      v_date               DATE;                                --创建更新时间
      auditbegindate       DATE;                                --审核开始日期
      auditenddate         DATE;                                --审核结束日期
   BEGIN
      v_parvalue := 0;                                              --交易面额
      v_parvalue_id := 0;                                             --面额id
      v_code := '0';                                                --面额代码
      v_area_id := 0;                                                 --地区id
      v_operator_id := 1000;                      --充值类型(移动,联通,电信)
      v_agent_id := 0;                                                --商户id
      v_time_id := 0;                              -- 到账时间(10分钟,24小时)
      v_total_parvalue := 0;                                        --充值面额
      v_price := 0;                                                 --到账金额
      v_out_price := 0;                                             --出货价格
      v_agio := 0;                                                  --产品费率
      v_stock_money := 0;                                           --出货价格
      v_stock_money_card := 0;                                    --外购卡价格
      v_stock_money_sky := 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 agent_audit
         IS
              SELECT   oi.parvalue,
                       oi.area_id,
                       oi.operator_id,
                       o.agent_id,
                       oi.time_id,
                       SUM (oi.parvalue),
                       SUM (oi.price)                            /* 到账金额*/
                FROM   tab_topup_orderitem oi, tab_topup_order o
               WHERE       oi.order_id = o.id
                       AND (oi.status_id = 82 OR oi.status_id = 81)
                       AND oi.stat_agent_date IS NULL
                       AND oi.successdate >= auditbegindate
                       AND oi.successdate < auditenddate
            GROUP BY   oi.parvalue,
                       oi.area_id,
                       oi.operator_id,
                       o.agent_id,
                       oi.time_id;
      --开始使用游标取数据
      BEGIN
         OPEN agent_audit;
         LOOP
            FETCH agent_audit
               INTO
                         v_parvalue, v_area_id, v_operator_id, v_agent_id, v_time_id, v_total_parvalue, v_price;
            --游标取不到数据则退出
            EXIT WHEN agent_audit%NOTFOUND;
            /*
            --面额id
            v_code := TO_CHAR (v_parvalue);
            SELECT   id
              INTO   v_parvalue_id
              FROM   tab_base_dictionary
             WHERE       category = 'ParValue'
                     AND operator_id = v_operator_id
                     AND code = v_code;

            SELECT   ao.agio
              INTO   v_agio
              FROM   tab_topup_agent_agio ao
             WHERE       ao.parvalue_id = v_parvalue_id
                     AND ao.area_id = v_area_id
                     AND ao.operator_id = v_operator_id
                     AND ao.agent_id = v_agent_id
                     AND ao.topuptime_id = v_time_id;

            --出货价格
            v_out_price := v_price * v_agio;
            */
            --出货价格
            SELECT   ABS (SUM (bml.money) * 100)
              INTO   v_out_price
              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 = 226 OR bml.tradetype_id = 225)
                     AND oi.parvalue = v_parvalue
                     AND oi.area_id = v_area_id
                     AND oi.operator_id = v_operator_id
                     AND oi.agent_id = v_agent_id
                     AND oi.time_id = v_time_id
                     AND oi.stat_agent_date IS NULL
                     AND oi.successdate >= auditbegindate
                     AND oi.successdate < auditenddate;

            IF v_out_price IS NULL
            THEN
               v_out_price := 0;
            END IF;
            --外购卡价格
            SELECT   SUM (cp.money)
              INTO   v_stock_money_card
              FROM   tab_topup_mobile_cardpay cp,
                     tab_topup_mobile_cardpay_item cpi,
                     tab_topup_mobile_carditem ci,
                     tab_topup_orderitem oi
             WHERE       cp.id = cpi.pay_id
                     AND cpi.carditem_id = ci.id
                     AND ci.item_id = oi.id
                     AND oi.parvalue = v_parvalue
                     AND oi.area_id = v_area_id
                     AND oi.operator_id = v_operator_id
                     AND oi.agent_id = v_agent_id
                     AND oi.time_id = v_time_id
                     AND oi.status_id = 82
                     AND oi.stat_agent_date IS NULL
                     AND oi.successdate >= auditbegindate
                     AND oi.successdate < auditenddate;

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

            /*
            --空充价格
            SELECT   SUM (bml.money)
              INTO   v_stock_money_sky
              FROM   tab_base_moneylist bml,
                     tab_topup_moneylist tml,
                     tab_topup_orderitem oi
             WHERE       bml.id = tml.moneylist_id
                     AND tml.item_id = oi.id
                     AND (bml.tradetype_id = 338 OR bml.tradetype_id = 226)
                     AND bml.GROUP_ID = v_agent_id
                     AND oi.parvalue = v_parvalue
                     AND oi.area_id = v_area_id
                     AND oi.operator_id = v_operator_id
                     AND status_id = 82
                     AND oi.createdate >= auditbegindate
                     AND oi.createdate < auditenddate
                     AND oi.stat_agent_date IS NULL;
                     */
 
            --空充进货价
            SELECT   ABS (SUM (bml.money) * 100)
              INTO   v_stock_money_sky
              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 oi.parvalue = v_parvalue
                     AND oi.area_id = v_area_id
                     AND oi.operator_id = v_operator_id
                     AND oi.agent_id = v_agent_id
                     AND oi.time_id = v_time_id
                     AND oi.status_id = 82
                     AND (   REMOTE = 1
                          OR YIPIAOLIAN = 1
                          OR LIANLIAN = 1
                          OR OFFER_ID IS NOT NULL)
                     AND bml.tradetype_id = 338
                     AND oi.stat_agent_date IS NULL
                     AND oi.successdate >= auditbegindate
                     AND oi.successdate < auditenddate;
            IF v_stock_money_sky IS NULL
            THEN
               v_stock_money_sky := 0;
            END IF;

            --进货价格=外购卡价格+空充价格
            v_stock_money := v_stock_money_card + v_stock_money_sky;

            INSERT INTO tab_topup_stat_agent (id,
                                              GROUP_ID,
                                              parmoney,
                                              actualmoney,
                                              inmoney,
                                              outmoney,
                                              correctpar,
                                              correctprice,
                                              status_id,
                                              batch,
                                              createdate,
                                              lastdate,
                                              area_id,
                                              parvalue,
                                              operator_id,
                                              time_id,
                                              statdate)
              VALUES   (seq_tab_topup_stat_agent.NEXTVAL,
                        v_agent_id,
                        v_total_parvalue,
                        v_price,
                        v_stock_money,
                        v_out_price,
                        NULL,
                        NULL,
                        '',
                        NULL,
                        v_date,
                        v_date,
                        v_area_id,
                        v_parvalue,
                        v_operator_id,
                        v_time_id,
                        v_statdate);
            -- 更新统计日期
            UPDATE   tab_topup_orderitem oi
               SET   oi.stat_agent_date = v_statdate
             WHERE       oi.parvalue = v_parvalue
                     AND oi.area_id = v_area_id
                     AND oi.operator_id = v_operator_id
                     AND oi.agent_id = v_agent_id
                     AND oi.time_id = v_time_id
                     AND (oi.status_id = 82 OR oi.status_id = 81)
                     AND oi.stat_agent_date IS NULL
                     AND oi.createdate >= auditbegindate
                     AND oi.createdate < auditenddate;
            COMMIT;
         END LOOP;
         CLOSE agent_audit;
      END;
   --发生异常时返回错误码
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'AGENT_AUDIT ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!'
         );
         ROLLBACK;
   END sp_finance_agent_audit;

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

请登录后发表评论 登录
全部评论

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    292270