ITPub博客

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

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

原创 Linux操作系统 作者:golden_zhou 时间:2012-07-02 14:27:19 0 删除 编辑
   PROCEDURE sp_finance_supply_audit (currentdate IN DATE, numdays IN NUMBER)
   AS
      v_parvalue         NUMBER (10);                               --交易面额
      v_price            NUMBER (19);                               --结算金额
      v_price_66         NUMBER (19);               --结算金额成功或小充大部分
      v_price_49         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_price_66 := 0;                              --结算金额成功或小充大部分
      v_price_49 := 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 supply_audit
         IS
              SELECT   SUM (ci.parvalue),
                       cp.supply_id,
                       ci.operator_id,
                       ci.parvalue
                FROM   tab_topup_mobile_cardpay cp,
                       tab_topup_mobile_cardpay_item cpi,
                       tab_topup_mobile_carditem ci
               WHERE   cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
                       AND (   ci.status_id = 66
                            OR ci.status_id = 48
                            OR ci.status_id = 49
                            OR ci.status_id = 47)
                       AND cp.balancepay = 0
                       AND cp.stat_date IS NULL
                       AND cp.finisheddate >= auditbegindate
                       AND cp.finisheddate < auditenddate
            GROUP BY   cp.supply_id, ci.operator_id, ci.parvalue;
      --开始使用游标取数据
      BEGIN
         OPEN supply_audit;
         LOOP
            FETCH supply_audit
               INTO   v_total_parvalue, v_supply_id, v_operator_id, v_parvalue;
            --游标取不到数据则退出
            EXIT WHEN supply_audit%NOTFOUND;
            --结算面额 充值卡状态正常+成功金额小于申报金额
            SELECT   SUM (ci.price)
              INTO   v_price_66
              FROM   tab_topup_mobile_cardpay cp,
                     tab_topup_mobile_cardpay_item cpi,
                     tab_topup_mobile_carditem ci
             WHERE   cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
                     AND (   ci.status_id = 66
                          OR ci.status_id = 47
                          OR ci.status_id = 48)
                     AND cp.status_id = 66
                     AND cp.balancepay = 0
                     AND cp.stat_date IS NULL
                     AND cp.finisheddate >= auditbegindate
                     AND cp.finisheddate < auditenddate
                     AND cp.supply_id = v_supply_id
                     AND ci.operator_id = v_operator_id
                     AND ci.parvalue = v_parvalue;
            IF v_price_66 IS NULL
            THEN
               v_price_66 := 0;
            END IF;

            --结算面额 充值卡状态:成功金额大于申报金额
            SELECT   SUM (ci.parvalue)
              INTO   v_price_49
              FROM   tab_topup_mobile_cardpay cp,
                     tab_topup_mobile_cardpay_item cpi,
                     tab_topup_mobile_carditem ci
             WHERE       cp.id = cpi.pay_id
                     AND cpi.carditem_id = ci.id
                     AND ci.status_id = 49
                     AND cp.status_id = 66
                     AND cp.balancepay = 0
                     AND cp.stat_date IS NULL
                     AND cp.finisheddate >= auditbegindate
                     AND cp.finisheddate < auditenddate
                     AND cp.supply_id = v_supply_id
                     AND ci.operator_id = v_operator_id
                     AND ci.parvalue = v_parvalue;
            IF v_price_49 IS NULL
            THEN
               v_price_49 := 0;
            END IF;

            v_price := v_price_66 + v_price_49;

            --进货成本(用户已经使用的)
            SELECT   SUM (cp.money)
              INTO   v_inmoney
              FROM   tab_topup_mobile_cardpay cp,
                     tab_topup_mobile_cardpay_item cpi,
                     tab_topup_mobile_carditem ci
             WHERE   cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
                     AND (   ci.status_id = 66
                          OR ci.status_id = 48
                          OR ci.status_id = 49)
                     AND cp.status_id = 66
                     AND cp.stat_date IS NULL
                     AND ci.operator_id = v_operator_id
                     AND ci.parvalue = v_parvalue
                     AND cp.supply_id = v_supply_id
                     AND cp.finisheddate >= auditbegindate
                     AND cp.finisheddate < auditenddate;
            IF v_inmoney IS NULL
            THEN
               v_inmoney := 0;
            END IF;

            --出货金额
            SELECT   ABS (SUM (bml.money * ci.price / oi.parvalue) * 100)
              INTO   v_outmoney
              FROM   tab_base_moneylist bml,
                     tab_topup_moneylist tml,
                     tab_topup_orderitem oi,
                     tab_topup_mobile_carditem ci,
                     tab_topup_mobile_cardpay_item cpi,
                     tab_topup_mobile_cardpay cp
             WHERE       cp.id = cpi.pay_id
                     AND cpi.carditem_id = ci.id
                     AND bml.id = tml.moneylist_id
                     AND tml.item_id = oi.id
                     AND oi.id = ci.item_id
                     AND (   ci.status_id = 66
                          OR ci.status_id = 48
                          OR ci.status_id = 49)
                     AND ci.status_id = 66
                     AND cp.stat_date IS NULL
                     AND bml.tradetype_id = 225                  /* 购物类型*/
                     AND cp.balancepay = 0
                     AND cp.supply_id = v_supply_id
                     AND ci.operator_id = v_operator_id
                     AND ci.parvalue = v_parvalue
                     AND cp.finisheddate >= auditbegindate
                     AND cp.finisheddate < 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.status_id = 82
                      AND oi.stat_sky_date IS NULL
                      AND oi.createdate >= auditbegindate
                      AND oi.createdate < auditenddate;
            */
            -- 更新统计日期
            UPDATE   tab_topup_mobile_cardpay cp
               SET   cp.stat_date = v_statdate
             WHERE   EXISTS
                        (SELECT   1
                           FROM   tab_topup_mobile_cardpay_item cpi,
                                  tab_topup_mobile_carditem ci
                          WHERE   cp.id = cpi.pay_id
                                  AND cpi.carditem_id = ci.id
                                  AND (   ci.status_id = 66
                                       OR ci.status_id = 48
                                       OR ci.status_id = 49
                                       OR ci.status_id = 47)
                                  AND cp.balancepay = 0
                                  AND cp.supply_id = v_supply_id
                                  AND ci.operator_id = v_operator_id
                                  AND ci.parvalue = v_parvalue
                                  AND cp.stat_date IS NULL
                                  AND cp.finisheddate >= auditbegindate
                                  AND cp.finisheddate < auditenddate);
            /*
         -- 更新统计日期
         UPDATE   tab_topup_mobile_cardpay cp,
                    tab_topup_mobile_cardpay_item cpi,
                    tab_topup_mobile_carditem ci
            SET   cp.stat_date = v_statdate
            WHERE   cp.id = cpi.pay_id AND cpi.carditem_id = ci.id
                    AND (   ci.status_id = 66
                         OR ci.status_id = 48
                         OR ci.status_id = 49)
                    AND cp.status_id = 66
                    AND cp.balancepay = 0
                    AND cp.finisheddate >= auditbegindate
                    AND cp.finisheddate < auditenddate
                    AND cp.supply_id = v_supply_id
                    AND ci.operator_id = v_operator_id
                    AND ci.parvalue = v_parvalue;
         */
            COMMIT;
         END LOOP;
         CLOSE supply_audit;
      END;
   --发生异常时返回错误码
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line (
            'SUPPLY_AUDIT ERROR,PLEASE CHECK OPERATION AGAIN,THANKS!'
         );
         ROLLBACK;
   END sp_finance_supply_audit;

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

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

注册时间:2011-03-09

  • 博文量
    238
  • 访问量
    292342