业务数据订正存储三

         -- 如果订单之前有成功或失败过,并且在报表中已经统计过,需要改失败或成功的,则需要在订正表中插入一笔记录
         IF O_OSTATUS = 82
            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;
      --充值卡处理部分
      ----------------------------------------start here--------------------------------------
      WHEN O_ITEMTYPE = 'card' AND O_NSTATUS = 81 AND P_ACCOUNTTYPE = '没有到账' AND O_MONEY = 0
      --充值卡配对手机充值:卡充无效,充值状态改失败,没有到账
      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   status_id = O_NSTATUS,
                  successdate = SYSDATE,
                  send = 0,
                  sendcount = 0,
                  sendtime = NULL
          WHERE   itemnumber = O_ITEMNUMBER;

         COMMIT;

         ------------------------------------------End here--------------------------------------

         -- 如果订单之前有成功或失败过,并且在报表中已经统计过,需要改失败或成功的,则需要在订正表中插入一笔记录
         IF O_OSTATUS = 82
            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;
      --充值卡处理部分
      ----------------------------------------start here--------------------------------------
      WHEN O_ITEMTYPE = 'card' AND O_NSTATUS = 66 AND P_ACCOUNTTYPE = '全部到账' AND O_MONEY = O_PARVALUE
      --充值卡:充值状态改成功且根据订单面额全部到账
      THEN
         SELECT   cp.id,
                  cp.balancepay,
                  cpi.carditem_id,
                  ci.balance,
                  cp.orderid,
                  cp.createdate,
                  cp.lastdate,
                  cp.paymoney,
                  cp.price,
                  cp.money,
                  cp.send,
                  cp.sendcount,
                  cp.sendtime,
                  o.itemnumber,
                  o.status_id
           INTO   v_pay_id,
                  v_balancepay,
                  v_carditem_id,
                  v_balance,
                  v_orderid,
                  v_createdate,
                  v_lastdate,
                  v_parvalue,
                  v_price,
                  v_money,
                  v_send,
                  v_sendcount,
                  v_sendtime,
                  c_itemnumber,
                  v_status_id
           FROM   tab_topup_mobile_cardpay cp,
                  tab_topup_mobile_cardpay_item cpi,
                  tab_topup_mobile_carditem ci,
                  tab_topup_orderitem o
          WHERE       cp.id = cpi.pay_id
                  AND cpi.carditem_id = ci.id
                  AND ci.item_id = o.id
                  AND cp.orderid = O_ITEMNUMBER;

         INSERT INTO TAB_DATA_CORRECTION_RECORD (ID,
                                                 CREATEDATE,
                                                 LASTDATE,
                                                 ITEMNUMBER,
                                                 PARVALUE,
                                                 PRICE,
                                                 SEND,
                                                 SENDCOUNT,
                                                 SENDTIME,
                                                 MONEY,
                                                 AIR,
                                                 OFFER_ID,
                                                 AGENT_ID,
                                                 REFUNDMENTNUMBER,
                                                 CORRECTIONDATE)
           VALUES   (SEQ_DATA_CORRECTION_RECORD.NEXTVAL,
                     v_createdate,
                     v_lastdate,
                     v_orderid,
                     v_parvalue,
                     v_price,
                     v_send,
                     v_sendcount,
                     v_sendtime,
                     v_money,
                     NULL,
                     NULL,
                     NULL,
                     NULL,
                     SYSDATE);

         COMMIT;

         IF v_balancepay = 0 AND v_balance = 0
         THEN
            SELECT   status_id
              INTO   cp_status_id
              FROM   tab_topup_mobile_cardpay
             WHERE   rderid = O_ITEMNUMBER;

            IF cp_status_id != 66
            THEN
               UPDATE   tab_topup_mobile_cardpay
                  SET   send = 0,
                        sendcount = 0,
                        price = paymoney,
                        status_id = 66
                WHERE   rderid = O_ITEMNUMBER;

               COMMIT;
            END IF;

            SELECT   status_id
              INTO   cdi_status_id
              FROM   tab_topup_mobile_carditem
             WHERE   id = v_carditem_id;

            IF cdi_status_id != 66
            THEN
               UPDATE   tab_topup_mobile_carditem
                  SET   price = parvalue, status_id = 66
                WHERE   id = v_carditem_id;

               COMMIT;

               UPDATE   tab_topup_mobile_cardpay
                  SET   finisheddate = SYSDATE,
                        sendtime = SYSDATE,
                        stat_date = NULL
                WHERE   rderid = O_ITEMNUMBER;

               COMMIT;
            END IF;
         END IF;

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

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