CREATE OR REPLACE PROCEDURE TOPUP.SP_MONEYLIST_ADD_PAYRECORD (
   P_ITEMNUMBER   IN VARCHAR,                                       --订单编号
   P_MONEY        IN NUMBER                                         --实充金额
)
IS
   O_ITEMNUMBER         VARCHAR2 (50);                              --订单编号
   O_MONEY              NUMBER (19);                                --实充金额
   V_ITEM_ID            NUMBER (19);                                --订单表ID
   V_PARVALUE           NUMBER (10);                                --订单面额
   V_AREA_ID            NUMBER (19);                                  --区域ID
   V_TIME_ID            NUMBER (19);                              --充值时间ID
   V_OFFER_ID           NUMBER (19);                                --代充商ID
   V_OPERATOR_ID        NUMBER (19);                 --充值类型:移动/联通/电信
   V_AGENT_ID           NUMBER (19);                                --代理商ID
   V_DNAME              VARCHAR2 (50 CHAR);                     --充值时间名称
   V_AGENTRECHARGE_ID   NUMBER (19);                        --充值通道表产品ID
   V_PRICE              NUMBER (10);                                --实充金额
   V_AGIO               FLOAT (126);                --充值产品表订单对应的折扣
   V_TOTLEMONEY         FLOAT (126);                    --充值金额表代充商余额
   V_MONEYLIST_ID       NUMBER (19);                            --充值金额表ID
   V_MEMO               VARCHAR2 (200 CHAR);              --充值金额表订单备注
   V_SERIALNUMBER       VARCHAR2 (100 CHAR);            --充值金额表订单序列号
   V_URL                VARCHAR2 (150 CHAR);              --充值金额表回调地址
BEGIN
   O_ITEMNUMBER := P_ITEMNUMBER;                                    --订单编号
   O_MONEY := P_MONEY;                                              --充值类型
   V_ITEM_ID := 0;                                                  --订单表ID
   V_PARVALUE := 0;                                                 --订单面额
   V_AREA_ID := 0;                                                    --区域ID
   V_TIME_ID := 0;                                                --充值时间ID
   V_OFFER_ID := 0;                                                 --代充商ID
   V_OPERATOR_ID := 0;                               --充值类型:移动/联通/电信
   V_AGENT_ID := 0;                                                 --代理商ID
   V_DNAME := '0';                                              --充值时间名称
   V_AGENTRECHARGE_ID := 0;                                 --充值通道表产品ID
   V_PRICE := 0;                                                    --实充金额
   V_AGIO := 0;                                     --充值产品表订单对应的折扣
   V_TOTLEMONEY := 0;                                   --充值金额表代充商余额
   V_MONEYLIST_ID := 0;                                         --充值金额表ID
   V_MEMO := '0';                                         --充值金额表订单备注
   V_SERIALNUMBER := '0';                               --充值金额表订单序列号
   V_URL := '0';                                          --充值金额表回调地址

 

   --------------------------------------------------------------------------------------------------
   -- 订单号指订单编号或供应商编号
   -- 充值类型:手机充值 -> mobile, 充值卡 -> card
   --目前状态:手机充值处理中 -> 80, 手机充值状态不确定 -> 78,充值卡状态不确定  -> 65,充值卡无效 ->47
   --订正状态:手机充值成功 -> 82, 手机充值失败 -> 81,充值卡成功完成 -> 66,充值卡无效 ->47
   --代充商列表:浙江连连/捷贝/欧飞/捷易通等
   --到账类型:根据订单面额全部到账或根据用户提交金额部分到账
   --实充金额:手机充值成功,若全部成功,则实充金额 = parvalue;若部分成功,则实充金额 = p_money;
   --------------------------------------------------------------------------------------------------

  
   ------------------------------------------Start here----------------------------------------------
   SELECT   o.id,
            o.parvalue,
            o.area_id,
            o.time_id,
            o.offer_id,
            o.operator_id,
            o.agent_id,
            d.dname
     INTO   v_item_id,
            v_parvalue,
            v_area_id,
            v_time_id,
            v_offer_id,
            v_operator_id,
            v_agent_id,
            v_dname
     FROM   tab_topup_orderitem o, tab_base_dictionary d
    WHERE   O.TIME_ID = d.id AND o.itemnumber = O_ITEMNUMBER;

   SELECT   id
     INTO   v_agentrecharge_id
     FROM   tab_topup_agent_recharge
    WHERE       GROUP_ID = v_offer_id
            AND area_id = v_area_id
            AND operator_id = v_operator_id;

   SELECT   agio
     INTO   v_agio
     FROM   tab_topup_mobile_product
    WHERE       agentrecharge_id = v_agentrecharge_id
            AND parvalue = v_parvalue
            AND area = (SELECT   code
                          FROM   tab_base_dictionary
                         WHERE   id = v_area_id)
            AND operator_id = v_operator_id
            AND delaytime = v_dname;

   SELECT   (v_agio * o_money)/100 INTO v_price FROM DUAL;

   SELECT   money_account
     INTO   v_totlemoney
     FROM   tab_base_moneylist_account
    WHERE   GROUP_ID = v_offer_id;

   SELECT   seq_tab_base_moneylist.NEXTVAL INTO v_moneylist_id FROM DUAL;

   SELECT   'OrderItemMobile=' || v_item_id INTO v_memo FROM DUAL;

   SELECT   SUBSTR (
               TO_CHAR (SYSDATE, 'yyyymmdd')
               || LPAD (seq_tab_base_serial.NEXTVAL, 10, '0'),
               1,
               18
            )
               serialnumber
     INTO   v_serialnumber
     FROM   DUAL;

   SELECT   '/topup/orderItemMobileAdminLoad.php?id=' INTO v_url FROM DUAL;

   INSERT INTO tab_base_moneylist (id,
                                   createdate,
                                   lastdate,
                                   memo,
                                   money,
                                   pk,
                                   serialnumber,
                                   totlemoney,
                                   url,
                                   valid,
                                   tradetype_id,
                                   GROUP_ID,
                                   closeoff)
     VALUES   (v_moneylist_id,
               SYSDATE,
               SYSDATE,
               v_memo,
               v_price,
               v_item_id,
               v_serialnumber,
               v_totlemoney,
               v_url,
               1,
               338,
               v_offer_id,
               0);

   INSERT INTO tab_topup_moneylist (moneylist_id, item_id)
     VALUES   (v_moneylist_id, v_item_id);

   COMMIT;
------------------------------------------End here------------------------------------------------
END;
/