业务数据订正存储过程一

于 2012-12-14 16:42:14 发布
阅读量148 收藏
点赞数
CREATE OR REPLACE PROCEDURE TOPUP.SP_MC_DATA_CORRECTION (
   P_ITEMNUMBER    IN VARCHAR,                           --订单编号/供应商编号
   P_ITEMTYPE      IN VARCHAR,                                      --充值类型
   P_OSTATUS       IN NUMBER,                               --充值订单目前状态
   P_NSTATUS       IN NUMBER,                               --充值订单订正状态
   P_LOGOGRAM      IN VARCHAR,                                --代充商机构名称
   P_PARVALUE      IN NUMBER,                                   --充值订单面额
   P_ACCOUNTTYPE   IN VARCHAR,                                  --充值到账类型
   P_MONEY         IN NUMBER                                        --实充金额
)
IS
   O_ITEMNUMBER         VARCHAR2 (50);                   --订单编号/供应商编号
   O_ITEMTYPE           VARCHAR2 (50);                              --充值类型
   O_OSTATUS            NUMBER (19);                        --充值订单目前状态
   O_NSTATUS            NUMBER (19);                        --充值订单订正状态
   O_LOGOGRAM           VARCHAR2 (50);                        --代充商机构名称
   O_PARVALUE           NUMBER (19);                            --充值订单面额
   O_ACCOUNTTYPE        VARCHAR2 (50);                          --充值到账类型
   O_MONEY              NUMBER (19);                                --实充金额
   V_ITEMNUMBER         VARCHAR2 (100 CHAR);                          --订单号
   C_ITEMNUMBER         VARCHAR2 (100 CHAR);              --充值卡对应的订单号
   CP_STATUS_ID         NUMBER (19);                          --充值卡支付状态
   CDI_STATUS_ID        NUMBER (19);                          --充值卡订单状态
   V_CREATEDATE         TIMESTAMP (6);                              --创建日期
   V_LASTDATE           TIMESTAMP (6);                          --最后修改日期
   V_PARVALUE           NUMBER (10);                                --订单面额
   V_PRICE              NUMBER (10);                                --实充金额
   V_MONEY              NUMBER (10);                                --交易金额
   V_REFUNDPRICE        NUMBER (10);                                --退款金额
   V_SEND               NUMBER (1);                                 --是否发送
   V_SENDCOUNT          NUMBER (10);                                --发送次数
   V_SENDTIME           TIMESTAMP (6);                              --发送时间
   V_AIR                NUMBER (10);                                --是否空充
   V_OFFER_ID           NUMBER (19);                            --代充商机构ID
   V_GROUP_ID           NUMBER (19);                                --代充商ID
   V_OFFERNAME          VARCHAR2 (50);                        --代充商机构名称
   V_AGENT_ID           NUMBER (19);                            --代理商机构ID
   V_REFUNDMENTNUMBER   VARCHAR2 (50 BYTE);                       --退款流水号
   V_BALANCEPAY         NUMBER (1);                             --是否余额支付
   V_ITEM_ID            NUMBER (19);                            --订单表对应ID
   V_PAY_ID             NUMBER (19);                            --充值卡对应ID
   V_CARDITEM_ID        NUMBER (19);                            --充值卡对应ID
   V_BALANCE            NUMBER (10);                                    --余额
   V_STATUS_ID          NUMBER (19);                  --充值卡对应手机充值状态
   V_ORDERID            VARCHAR2 (100 CHAR);                    --供应商订单号
   V_COUNT              NUMBER (19);            --充值金额表订单支付对应的记录
   V_STAT_AGENT_DATE    TIMESTAMP (6);                          --卡充统计时间
   V_STAT_SKY_DATE      TIMESTAMP (6);                          --空充统计时间
BEGIN
   O_ITEMNUMBER := P_ITEMNUMBER;                         --订单编号/供应商编号
   O_ITEMTYPE := P_ITEMTYPE;                                        --充值类型
   O_OSTATUS := P_OSTATUS;                                  --充值订单目前状态
   O_NSTATUS := P_NSTATUS;                                  --充值订单订正状态
   O_LOGOGRAM := P_LOGOGRAM;                                  --代充商机构名称
   O_PARVALUE := P_PARVALUE;                                    --充值订单面额
   O_ACCOUNTTYPE := P_ACCOUNTTYPE;                              --充值到账类型
   O_MONEY := P_MONEY;                                              --实充金额
   V_ITEMNUMBER := '0';                                               --订单号
   C_ITEMNUMBER := '0';                                   --充值卡对应的订单号
   CP_STATUS_ID := 0;                                         --充值卡支付状态
   CDI_STATUS_ID := 0;                                        --充值卡订单状态
   V_CREATEDATE := TO_DATE ('2012-01-01', 'YYYY-MM-DD');            --创建日期
   V_LASTDATE := TO_DATE ('2012-01-01', 'YYYY-MM-DD');          --最后修改日期
   V_PARVALUE := 0;                                                 --订单面额
   V_PRICE := 0;                                                    --实充金额
   V_MONEY := 0;                                                    --交易金额
   V_REFUNDPRICE := 0;                                              --退款金额
   V_SEND := 0;                                                     --是否发送
   V_SENDCOUNT := 0;                                                --发送次数
   V_SENDTIME := TO_DATE ('2012-01-01', 'YYYY-MM-DD');              --发送时间
   V_AIR := 0;                                                      --是否空充
   V_OFFER_ID := 0;                                             --代充商机构ID
   V_GROUP_ID := 0;                                                 --代充商ID
   V_OFFERNAME := '0';                                        --代充商机构名称
   V_AGENT_ID := 0;                                             --代理商机构ID
   V_REFUNDMENTNUMBER := '0';                                     --退款流水号
   V_BALANCEPAY := 0;                                           --是否余额支付
   V_ITEM_ID := 0;                                              --订单表对应ID
   V_PAY_ID := 0;                                               --充值卡对应ID
   V_CARDITEM_ID := 0;                                          --充值卡对应ID
   V_BALANCE := 0;                                                      --余额
   V_STATUS_ID := 0;                                  --充值卡对应手机充值状态
   V_ORDERID := '0';                                            --供应商订单号
   V_COUNT := 0;                                --充值金额表订单支付对应的记录
   V_STAT_AGENT_DATE := TO_DATE ('2012-01-01', 'YYYY-MM-DD');   --卡充统计时间
   V_STAT_SKY_DATE := TO_DATE ('2012-01-01', 'YYYY-MM-DD');     --空充统计时间

   --------------------------------------------------------------------------------------------------
   -- 订单号指订单编号或供应商编号
   -- 充值类型:手机充值 -> mobile, 充值卡 -> card
   --目前状态:手机充值处理中 -> 80, 手机充值状态不确定 -> 78,充值卡状态不确定  -> 65,充值卡无效 ->47
   --订正状态:手机充值成功 -> 82, 手机充值失败 -> 81,充值卡成功完成 -> 66,充值卡无效 ->47
   --代充商列表:浙江连连/捷贝/欧飞/捷易通等
   --到账类型:根据订单面额全部到账或根据用户提交金额部分到账
   --实充金额:手机充值成功,若全部成功,则实充金额 = parvalue;若部分成功,则实充金额 = p_money;
   --------------------------------------------------------------------------------------------------
   --记录客服订正明细
   ------------------------------------------Start here----------------------------------------------
   INSERT INTO TAB_CSD_CORRECTION_RECORD (ID,
                                          CREATEDATE,
                                          LASTDATE,
                                          ITEMNUMBER,
                                          ITEMTYPE,
                                          OSTATUS,
                                          NSTATUS,
                                          LOGOGRAM,
                                          PARVALUE,
                                          ACCOUNTTYPE,
                                          MONEY)
     VALUES   (SEQ_DATA_CORRECTION_RECORD.NEXTVAL,
               SYSDATE,
               SYSDATE,
               O_ITEMNUMBER,
               O_ITEMTYPE,
               O_OSTATUS,
               O_NSTATUS,
               O_LOGOGRAM,
               O_PARVALUE,
               O_ACCOUNTTYPE,
               O_MONEY);
   COMMIT;
   ------------------------------------------End here------------------------------------------------

   --------------------------------------------------------------------------------------------------
   -- 数据订正分类:
   --手机充值根据订单面额改成功
   --exec SP_MC_DATA_CORRECTION('T201211290026944153_1','mobile',80,82,'捷贝',10000,'全部到账',10000);
   --手机充值根据实充金额改成功
   --exec SP_MC_DATA_CORRECTION('T201211290026944187_1','mobile',80,82,'捷贝',5000,'部分到账',3000);
   --手机充值改失败
   --exec SP_MC_DATA_CORRECTION('T201211290026944177_1','mobile',80,81,'捷贝',10000,'没有到账',0);
   --充值卡无效改成功，若对应的手机充值为失败也改成功
   --exec SP_MC_DATA_CORRECTION('871210174252','card',47,66,'欧飞',10000,'全部到账',10000);
   --充值卡无效改,配对的手机充值状态不确定或处理中改失败
   --exec SP_MC_DATA_CORRECTION('T201212130027309858_1','card',80,81,'欧飞',1000,'没有到账',0);
   --------------------------------------------------------------------------------------------------
   -- 订单检查SQL:
   /*
   select o.* from tab_base_moneylist a ,tab_topup_moneylist b, tab_topup_orderitem o
   where a.id=b.moneylist_id-- and TRADETYPE_ID=338
   and o.id=b.item_id
   and o.itemnumber in(
   'T201211290026951234_1');
   select o.rowid,o.* from tab_topup_orderitem o where itemnumber in
    ('T201211290026951234_1','T201211290026951233_1','T201211290026951220_1',
    'T201211300026962743_1','T201211300026962747_1','T201211300026962775_1','T201211300026962772_1');
    --充值卡
    select cp.rowid,cp.* from TAB_TOPUP_MOBILE_CARDPAY cp where rderid='1077712'
    */
   --------------------------------------------------------------------------------------------------

   --数据订正主体代码
   --------------------------------------------------------------------------------------------------
   --手机充值:充值状态改成功且根据订单面额全部到账,返回:OK001
   --手机充值:充值状态改成功且只有部分到账,返回:OK002
   --手机充值:充值状态改失败,没有到账,返回:OK003
   --充值卡:充值状态改成功且根据订单面额全部到账,返回:OK004
   --------------------------------------------------------------------------------------------------
   --数据订正主体代码
   ------------------------------------------Start here----------------------------------------------
   CASE
      --手机充值处理部分
      ----------------------------------------start here--------------------------------------
      WHEN O_ITEMTYPE = 'mobile' AND O_NSTATUS = 82 AND P_ACCOUNTTYPE = '全部到账' AND O_MONEY = O_PARVALUE
      --手机充值:充值状态改成功且根据订单面额全部到账
      THEN
         SELECT   id,
                  itemnumber,
                  createdate,
                  lastdate,
                  parvalue,
                  price,
                  money,
                  status_id,
                  send,
                  sendcount,
                  sendtime,
                  air,
                  offer_id,
                  agent_id,
                  refundmentnumber,
                  v_orderid,
                  stat_agent_date,
                  stat_sky_date
           INTO   v_item_id,
                  v_itemnumber,
                  v_createdate,
                  v_lastdate,
                  v_parvalue,
                  v_price,
                  v_money,
                  v_status_id,
                  v_send,
                  v_sendcount,
                  v_sendtime,
                  v_air,
                  v_offer_id,
                  v_agent_id,
                  v_refundmentnumber,
                  v_orderid,
                  v_stat_agent_date,
                  v_stat_sky_date
           FROM   tab_topup_orderitem o
          WHERE   o.itemnumber = O_ITEMNUMBER;

         INSERT INTO TAB_DATA_CORRECTION_RECORD (ID,
                                                 CREATEDATE,
                                                 LASTDATE,
                                                 ITEMNUMBER,
                                                 PARVALUE,
                                                 PRICE,
                                                 SEND,
                                                 SENDCOUNT,
                                                 SENDTIME,
                                                 MONEY,
                                                 STATUS_ID,
                                                 AIR,
                                                 OFFER_ID,
                                                 AGENT_ID,
                                                 REFUNDMENTNUMBER,
                                                 CORRECTIONDATE)
           VALUES   (SEQ_DATA_CORRECTION_RECORD.NEXTVAL,
                     v_createdate,
                     v_lastdate,
                     v_itemnumber,
                     v_parvalue,
                     v_price,
                     v_send,
                     v_sendcount,
                     v_sendtime,
                     v_money,
                     v_status_id,
                     v_air,
                     v_offer_id,
                     v_agent_id,
                     v_refundmentnumber,
                     SYSDATE);
         COMMIT;
         UPDATE   tab_topup_orderitem
            SET   price = parvalue, status_id = O_NSTATUS, llprice = parvalue
          WHERE   itemnumber = O_ITEMNUMBER;
         COMMIT;
         UPDATE   tab_topup_orderitem
            SET   successdate = SYSDATE
          WHERE   itemnumber = O_ITEMNUMBER;
         COMMIT;
         --检查订单有否有对应的代充商,没有则指定代充商
         ------------------------------------------Start here-------------------------------
         SELECT   '%' || O_LOGOGRAM || '%' INTO v_offername FROM DUAL;
         SELECT   id
           INTO   v_group_id
           FROM   tab_base_group
          WHERE   CTYPE = 'Supply' AND logogram LIKE v_offername;
         IF v_group_id = 2283 AND v_offer_id IS NULL
         THEN
            UPDATE   tab_topup_orderitem
               SET   LIANLIAN = 1, AIR = 1, OFFER_ID = 2283, FFERRES = 1
             WHERE   itemnumber = O_ITEMNUMBER;
            COMMIT;
         ELSIF v_group_id != 2283 AND v_offer_id IS NULL
         THEN
            UPDATE   tab_topup_orderitem
               SET   AIR = 1, OFFER_ID = v_group_id, FFERRES = 1
             WHERE   itemnumber = O_ITEMNUMBER;
            COMMIT;
         END IF;
         ------------------------------------------End here------------------------------------
         --如果订单在资金流水中没有向代充商支付的记录，则加入支付记录
         ------------------------------------------Start here-------------------------------
         SELECT   COUNT ( * )
           INTO   v_count
           FROM   tab_base_moneylist a,
                  tab_topup_moneylist b,
                  tab_topup_orderitem o
          WHERE       a.id = b.moneylist_id
                  AND o.id = b.item_id
                  AND a.tradetype_id = 338
                  AND o.itemnumber = O_ITEMNUMBER;
         IF v_count IS NULL
         THEN
            v_count := 0;
         END IF;

         IF v_count < 1
         THEN
            SP_MONEYLIST_ADD_PAYRECORD (O_ITEMNUMBER, O_MONEY);
         END IF;
         ------------------------------------------End here------------------------------------
         -- 如果订单之前有成功或失败过,并且在报表中已经统计过,需要改失败或成功的,则需要在订正表中插入一笔记录
         IF O_OSTATUS = 81
            AND (v_stat_agent_date IS NOT NULL OR v_stat_sky_date IS NOT NULL)
         THEN
            INSERT INTO tab_topup_auditorderitem (id,
                                                  status_id,
                                                  employee_id,
                                                  itemnumber,
                                                  orderid,
                                                  price,
                                                  refundmoney,
                                                  operator_id,
                                                  mobile,
                                                  createdate,
                                                  lastdate,
                                                  auditt,
                                                  item_id,
                                                  auditor_id,
                                                  auditdate,
                                                  checkdate,
                                                  checked,
                                                  statdate,
                                                  refundprice,
                                                  agent_id)
              VALUES   (seq_tab_topup_auditorderitem.NEXTVAL,
                        O_NSTATUS,
                        NULL,
                        O_ITEMNUMBER,
                        v_orderid,
                        v_parvalue,
                        v_parvalue,
                        12887,
                        '15814462718',
                        SYSDATE,
                        SYSDATE,
                        1,
                        v_item_id,
                        12887,
                        SYSDATE,
                        NULL,
                        0,
                        TRUNC (SYSDATE),
                        v_money,
                        v_offer_id);
            COMMIT;
         END IF;

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

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

congkuang7197
关注
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值