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

最新推荐文章于 2024-07-12 16:10:15 发布

congkuang7197

最新推荐文章于 2024-07-12 16:10:15 发布

阅读量261

点赞数

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25198367/viewspace-734323/