XYSH-WMS/doc/db/procedure/SP_AUTO_LEDGER_EXCUTE.sql

create or replace procedure SP_AUTO_LEDGER_EXCUTE(P_ORDER_ID IN STRING) is
  /**
  * 快递计费
  * 应付  E001-快递费 E002-操作费
  * 应收  C001-快递费 C002-快递抽佣 C003-超订单件数费 C004-操作费
  * 2016-03-04  scrazy
  */
  
  -- 一个循环处理条数
  V_PROC_COUNT NUMBER;
  V_CUR_COUNT NUMBER;
  -- 费用
  V_EXP_CHARG NUMBER; -- 快递金额
  V_EXP_CHARG1 NUMBER; -- 快递抽佣费
  V_OPT_CHARG NUMBER; --操作金额
  V_EXP_EXTRA_WEIGHT NUMBER; -- 续重
  V_OVER_COUNT NUMBER; --超订单件数
  V_OVER_CHARG NUMBER; -- 超订件数费
  
  V_FIRST_WEIGHT NUMBER;
  V_FIRST_PRICE NUMBER;
  V_EXTRA_PRICE NUMBER;
  
  V_ITEM_DETAIL STRING(2000);
  V_ERROR VARCHAR(2000);

  V_START_TIME DATE;
  V_END_TIME DATE;
  ------------ 游标定义 ------------

  -- 查询需要计费的订单 --
  cursor CUR_ORDERS is
    select D.ORDER_ID,
           D.CUSTOMER_REFERENCE_ID,
           D.CUSTOMER_ID,
           D.CUSTOMER_CODE,
           D.CUSTOMER_NAME,
           D.CARRIER_CODE,
           D.CARRIER_TRACKING_NUMBER,
           D.CARRIER_NAME,
           D.RECEIVER_PROVINCE,
           D.CARRIER_ID,       
           DECODE(O.OUTBOUND_KIND,
                  2301,
                  '线上销售订单',
                  2304,
                  '批发出库订单',
                  2303,
                  '退货出库订单',
                  2302,
                  '调拨订单') ORDER_TYPE,
           D.WAREHOUSE_ID,
           D.WAREHOUSE_NAME,
           D.RECEIVER_PROVINCE PROVINCE2,
           D.RECEIVER_CITY CITY2,
           D.RECEIVER_DISTRICT DISTRICT2,
           D.PRODUCT_CNT,
           D.WEIGHT,
           O.SHOP_NAME,
           O.ACCOUNT_ID,
           O.PAY_CASH,
           O.PAY_TIME,
           O.TOTAL_FEE,
           O.OUT_SERVICE_FIELD8 --货主联系人（真实货主）
      from WMS_DELIVER_DOCUMENT D
      LEFT JOIN WMS_ORDERS O
        ON D.ORDER_ID = O.ORDER_ID
     WHERE (P_ORDER_ID IS NULL OR O.ORDER_ID = P_ORDER_ID)
       AND O.OUTBOUND_KIND IN ('2301', '2302', '2303', '2304')
       --AND O.CUSTOMER_CODE = 'DD01'
       AND O.IS_CAL_CHARGE = 0
       AND D.CARRIER_CODE IS NOT NULL
       AND O.ORDER_STATUS = '5203'
       AND ROWNUM < V_PROC_COUNT + 1;
  R_ORDERS CUR_ORDERS%rowtype;
  
  -- 查询应付 快递费率
  CURSOR CUR_RATES(V_CARRIER_CODE IN CRM_VENDER_CARRIAGE.CONTRACT_CODE%TYPE,V_CHARGE IN WMS_CONTRACT_CHARG.CHARGE_NAME%TYPE,V_DEST_PROVINCE IN WMS_CONTRACT_CHARG_RATE.DEST_PROVINCE%TYPE) IS
 SELECT CR.PRESET_WEIGHT,
        CR.FIRST_PRICE,
        CR.EXTRA_WEIGHT,
        CR.EXTRA_PRICE,
        CR.RATE
   FROM WMS_CONTRACT_CHARG_RATE CR
   LEFT JOIN WMS_CONTRACT_CHARG CC
     ON (CR.CONTRACT_CHARGE_ID = CC.ID)
   LEFT JOIN CRM_VENDER_CARRIAGE VC
     ON (CC.CONTRACT_ID = VC.CONTRACT_ID)
  WHERE VC.CONTRACT_STATUS = '9402'
    AND VC.EFFECT_DATE < SYSDATE
    AND VC.MATURITY_DATE > SYSDATE
    AND CC.CHARGE_CODE = V_CHARGE
    AND VC.VENDER_CODE = V_CARRIER_CODE
       -- AND (CR.ORIG_PROVINCE IS NULL OR CR.ORIG_PROVINCE = V_ORIG_PROVINCE)
    AND (V_DEST_PROVINCE IS NULL OR CR.DEST_PROVINCE LIKE '%'||V_DEST_PROVINCE||'%' OR CR.DEST_PROVINCE IS NULL)
    AND ROWNUM = 1;
  R_RATE CUR_RATES%ROWTYPE;
  -- 查询应收
   CURSOR CUR_RATES1(V_CUSTOMER_CODE IN CRM_CUSTOMER_CARRIAGE.CUSTOMER_CODE%TYPE,V_RELATIONED_CUSTOMER_NAME IN CRM_WAREHOUSE_CHARGE.RELATIONED_CUSTOMER_NAME%TYPE, V_CARRIER_CODE IN CRM_VENDER_CARRIAGE.CONTRACT_CODE%TYPE,V_CHARGE IN WMS_CONTRACT_CHARG.CHARGE_NAME%TYPE,V_DEST_PROVINCE IN WMS_CONTRACT_CHARG_RATE.DEST_PROVINCE%TYPE) IS
  SELECT CR.PRESET_WEIGHT,
         CR.FIRST_PRICE,
         CR.EXTRA_WEIGHT,
         CR.EXTRA_PRICE,
         CR.RATE
    FROM WMS_CONTRACT_CHARG_RATE CR
    LEFT JOIN WMS_CONTRACT_CHARG CC
      ON (CR.CONTRACT_CHARGE_ID = CC.ID)
    LEFT JOIN CRM_WAREHOUSE_CHARGE WC
      ON (CC.CONTRACT_ID = WC.CONTRACT_ID)
   WHERE WC.CONTRACT_STATUS = '9402'
     AND WC.EFFECT_DATE < SYSDATE
     AND WC.MATURITY_DATE > SYSDATE
     AND CC.CHARGE_CODE = V_CHARGE
     AND WC.CUSTOMER_CODE = V_CUSTOMER_CODE
     AND WC.RELATIONED_CUSTOMER_NAME = V_RELATIONED_CUSTOMER_NAME
     AND （CR.VENDER_CODE IS NULL
      OR CR.VENDER_CODE = V_CARRIER_CODE)
  --AND (CR.ORIG_PROVINCE IS NULL OR CR.ORIG_PROVINCE = V_ORIG_PROVINCE)
   AND (V_DEST_PROVINCE IS NULL OR CR.DEST_PROVINCE LIKE '%'||V_DEST_PROVINCE||'%' OR CR.DEST_PROVINCE IS NULL) AND ROWNUM = 1;
  R_RATE1 CUR_RATES1%ROWTYPE;
  
BEGIN
  V_START_TIME := SYSDATE;
  -- 初始化参数
  V_PROC_COUNT := 100; -- 处理订单数 
    
    
    LOOP
      OPEN CUR_ORDERS; --打开订单游标
      V_CUR_COUNT := 0;
      LOOP 
        FETCH CUR_ORDERS INTO R_ORDERS;
      EXIT WHEN CUR_ORDERS%NOTFOUND;
      
      V_CUR_COUNT := V_CUR_COUNT + 1;
      
      V_EXP_CHARG := NULL; -- 快递金额
      V_EXP_CHARG1 := NULL; -- 快递抽佣费
      V_OPT_CHARG := NULL; --操作金额
      V_EXP_EXTRA_WEIGHT := NULL; -- 续重
      V_OVER_COUNT := NULL; --超订单件数
      V_OVER_CHARG := NULL; -- 超订件数费
    
      V_FIRST_WEIGHT := NULL;
      V_FIRST_PRICE := NULL;
      V_EXTRA_PRICE := NULL;
      V_ERROR := NULL;
      BEGIN
      --应付
       -- 快递费
       SP_GET_ITEMDETAIL(R_ORDERS.ORDER_ID,V_ITEM_DETAIL);
       OPEN CUR_RATES(R_ORDERS.CARRIER_CODE,'E001',R_ORDERS.RECEIVER_PROVINCE);
       LOOP
       FETCH CUR_RATES INTO R_RATE;
       IF CUR_RATES%FOUND THEN
         V_FIRST_WEIGHT := R_RATE.PRESET_WEIGHT;
         V_FIRST_PRICE := R_RATE.FIRST_PRICE;
         V_EXTRA_PRICE := R_RATE.EXTRA_PRICE;
         V_EXP_EXTRA_WEIGHT := CEIL(R_ORDERS.WEIGHT - V_FIRST_WEIGHT);
         IF V_EXP_EXTRA_WEIGHT > 0.1 THEN
           V_EXP_CHARG := (V_FIRST_WEIGHT*V_FIRST_PRICE + R_RATE.EXTRA_PRICE * V_EXP_EXTRA_WEIGHT)*R_RATE.RATE;
         ELSE
           V_EXP_EXTRA_WEIGHT := 0;
           V_EXP_CHARG := V_FIRST_WEIGHT*V_FIRST_PRICE*R_RATE.RATE;
         END IF;
        --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'快递费：'||V_EXP_CHARG);
       ELSE
            V_ERROR := ',未找到快递费配置'||V_ERROR;
       END IF;
       EXIT;
       END LOOP;
       CLOSE CUR_RATES;
       -- 操作费
       OPEN CUR_RATES(R_ORDERS.CARRIER_CODE,'E002',NULL);
       LOOP
       FETCH CUR_RATES INTO R_RATE;
       IF CUR_RATES%FOUND THEN
         V_OPT_CHARG := R_RATE.FIRST_PRICE * R_RATE.RATE;
         --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'操作费：'||V_OPT_CHARG);
       ELSE
             V_ERROR := ',未找到操作费配置'||V_ERROR;
       END IF;
       EXIT;
       END LOOP;
       CLOSE CUR_RATES;
       IF V_ERROR IS NOT NULL THEN 
         V_ERROR := '客户订单号【'||R_ORDERS.CUSTOMER_REFERENCE_ID||'】'||V_ERROR;
       END IF;
      -- 添加应付费用记录
      --DELETE FROM WMS_EXPRESS_CHARGES C WHERE C.LOGISTICS_NO = R_ORDERS.CARRIER_TRACKING_NUMBER AND C.RP_FLAG = 0;
      INSERT INTO WMS_EXPRESS_CHARGES
        (ID,
         ORDER_TYPE,
         VENDER_ID,
         VENDER_CODE,
         VENDER_NAME,
         SHOP_NAME,
         CUSTOMER_ORDER_NO,
         LOGISTICS_NO,
         SYSTEM_WEIGHT,
         PAYMENT_TIME,
         PAY_AMOUNT,
         PROVINCE,
         CITY,
         AREA,
         DELIVERY_NUMBER,
         FIRST_WEIGHT,
         FIRST_PRICE,
         ADDITIONAL_WEIGHT,
         ADDITIONAL_PRICE,
         EXPRESS_CHARGES,
         OVERBOOKING_NUMBER,
         OVERBOOKING_FEE,
         COMMISSION_FEE,
         OPERATION_FEE,
         TOTAL_FEE,
         CREATED_BY,
         CREATION_METHOD,
         CREATION_TIME,
         DELETE_FLAG,
         UPDATE_METHOD,
         UPDATE_TIME,
         UPDATED_BY,
         ACCOUNT_ID,
         CUSTOMER_ID,
         CUSTOMER_CODE,
         CUSTOMER_NAME,
         WAREHOUSE_ID,
         WAREHOUSE_NAME,
         RELATIONED_CUSTOMER_ID,
         RELATIONED_CUSTOMER_CODE,
         RELATIONED_CUSTOMER_NAME,
         RP_FLAG,
         ITEM_DETAIL,
         REMARK)
      values
        (SYS_GUID(),
         R_ORDERS.ORDER_TYPE,
         R_ORDERS.CARRIER_ID,
         R_ORDERS.CARRIER_CODE,
         R_ORDERS.CARRIER_NAME,
         R_ORDERS.SHOP_NAME,
         R_ORDERS.CUSTOMER_REFERENCE_ID,
         R_ORDERS.CARRIER_TRACKING_NUMBER,
         R_ORDERS.WEIGHT,
         R_ORDERS.PAY_TIME,
         R_ORDERS.TOTAL_FEE,
         R_ORDERS.PROVINCE2,
         R_ORDERS.CITY2，
         R_ORDERS.DISTRICT2,
         R_ORDERS.PRODUCT_CNT,
         V_FIRST_WEIGHT,
         V_FIRST_PRICE,
         V_EXP_EXTRA_WEIGHT,
         V_EXTRA_PRICE,
         V_EXP_CHARG,
         NULL,
         NULL,
         NULL,
         V_OPT_CHARG,
         V_OPT_CHARG+V_EXP_CHARG,
         'SYSTEM',
         'SP',
         SYSDATE,
         0,
         NULL,
         NULL,
         NULL,
         R_ORDERS.ACCOUNT_ID,
         R_ORDERS.CUSTOMER_ID,
         R_ORDERS.CUSTOMER_CODE,
         R_ORDERS.CUSTOMER_NAME,
         R_ORDERS.WAREHOUSE_ID,
         R_ORDERS.WAREHOUSE_NAME,
         null,
         null,
         null,
         0,
         V_ITEM_DETAIL,
         V_ERROR);
         -- 更新计费状态
         COMMIT;
      EXCEPTION 
        WHEN OTHERS THEN
          V_ERROR := '计费异常：'||SQLERRM;
          INSERT INTO WMS_EXPRESS_CHARGES
            (ID,
             ORDER_TYPE,
             VENDER_ID,
             VENDER_CODE,
             VENDER_NAME,
             SHOP_NAME,
             CUSTOMER_ORDER_NO,
             LOGISTICS_NO,
             SYSTEM_WEIGHT,
             PAYMENT_TIME,
             PAY_AMOUNT,
             PROVINCE,
             CITY,
             AREA,
             DELIVERY_NUMBER,
             FIRST_WEIGHT,
             FIRST_PRICE,
             ADDITIONAL_WEIGHT,
             ADDITIONAL_PRICE,
             EXPRESS_CHARGES,
             OVERBOOKING_NUMBER,
             OVERBOOKING_FEE,
             COMMISSION_FEE,
             OPERATION_FEE,
             TOTAL_FEE,
             CREATED_BY,
             CREATION_METHOD,
             CREATION_TIME,
             DELETE_FLAG,
             UPDATE_METHOD,
             UPDATE_TIME,
             UPDATED_BY,
             ACCOUNT_ID,
             CUSTOMER_ID,
             CUSTOMER_CODE,
             CUSTOMER_NAME,
             WAREHOUSE_ID,
             WAREHOUSE_NAME,
             RELATIONED_CUSTOMER_ID,
             RELATIONED_CUSTOMER_CODE,
             RELATIONED_CUSTOMER_NAME,
             RP_FLAG,
             ITEM_DETAIL,
             REMARK)
          values
            (SYS_GUID(),
             R_ORDERS.ORDER_TYPE,
             R_ORDERS.CARRIER_ID,
             R_ORDERS.CARRIER_CODE,
             R_ORDERS.CARRIER_NAME,
             R_ORDERS.SHOP_NAME,
             R_ORDERS.CUSTOMER_REFERENCE_ID,
             R_ORDERS.CARRIER_TRACKING_NUMBER,
             R_ORDERS.WEIGHT,
             R_ORDERS.PAY_TIME,
             R_ORDERS.TOTAL_FEE,
             R_ORDERS.PROVINCE2,
             R_ORDERS.CITY2，
             R_ORDERS.DISTRICT2,
             R_ORDERS.PRODUCT_CNT,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             'SYSTEM',
             'SP',
             SYSDATE,
             0,
             NULL,
             NULL,
             NULL,
             R_ORDERS.ACCOUNT_ID,
             R_ORDERS.CUSTOMER_ID,
             R_ORDERS.CUSTOMER_CODE,
             R_ORDERS.CUSTOMER_NAME,
             R_ORDERS.WAREHOUSE_ID,
             R_ORDERS.WAREHOUSE_NAME,
             null,
             null,
             null,
             0,
             V_ITEM_DETAIL,
             '计费异常：'||V_ERROR);
             -- 更新计费状态
             COMMIT;
       END;   
      BEGIN
      -- 应收
      V_EXP_CHARG := NULL; -- 快递金额
      V_EXP_CHARG1 := NULL; -- 快递抽佣费
      V_OPT_CHARG := NULL; --操作金额
      V_EXP_EXTRA_WEIGHT := NULL; -- 续重
      V_OVER_COUNT := NULL; --超订单件数
      V_OVER_CHARG := NULL; -- 超订件数费
    
      V_FIRST_WEIGHT := NULL;
      V_FIRST_PRICE := NULL;
      V_EXTRA_PRICE := NULL;
      V_ERROR := NULL;
       -- 快递费
       OPEN CUR_RATES1(R_ORDERS.CUSTOMER_CODE,R_ORDERS.OUT_SERVICE_FIELD8,R_ORDERS.CARRIER_CODE,'C001',R_ORDERS.RECEIVER_PROVINCE);
       LOOP
       FETCH CUR_RATES1 INTO R_RATE1;
       IF CUR_RATES1%FOUND THEN
         V_FIRST_WEIGHT := R_RATE1.PRESET_WEIGHT;
         V_FIRST_PRICE := R_RATE1.FIRST_PRICE;
         V_EXTRA_PRICE := R_RATE1.EXTRA_PRICE;
         V_EXP_EXTRA_WEIGHT := CEIL(R_ORDERS.WEIGHT - V_FIRST_WEIGHT);
         IF V_EXP_EXTRA_WEIGHT > 0.1 THEN
           V_EXP_CHARG := (V_FIRST_WEIGHT*V_FIRST_PRICE + R_RATE1.EXTRA_PRICE * V_EXP_EXTRA_WEIGHT)*R_RATE1.RATE;
         ELSE
           V_EXP_EXTRA_WEIGHT := 0;
           V_EXP_CHARG := V_FIRST_WEIGHT*V_FIRST_PRICE*R_RATE1.RATE;
         END IF;
         --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'快递费：'||V_EXP_CHARG);
       ELSE
             V_ERROR := ',未找到快递费配置'||V_ERROR;
       END IF;
       EXIT;
       END LOOP;
       CLOSE CUR_RATES1;
       -- 快递抽佣
       OPEN CUR_RATES1(R_ORDERS.CUSTOMER_CODE,R_ORDERS.OUT_SERVICE_FIELD8,R_ORDERS.CARRIER_CODE,'C002',NULL);
       LOOP
       FETCH CUR_RATES1 INTO R_RATE1;
       IF CUR_RATES1%FOUND THEN
         V_EXP_CHARG1 := V_EXP_CHARG*R_RATE1.RATE;
         --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'快递抽佣：'||V_EXP_CHARG1);
       ELSE
             V_ERROR := ',未找到快递抽佣配置'||V_ERROR;
       END IF;
       EXIT;
       END LOOP;
       CLOSE CUR_RATES1;
       -- 操订单件数费
       OPEN CUR_RATES1(R_ORDERS.CUSTOMER_CODE,R_ORDERS.OUT_SERVICE_FIELD8,R_ORDERS.CARRIER_CODE,'C003',NULL);
       LOOP
       FETCH CUR_RATES1 INTO R_RATE1;
       IF CUR_RATES1%FOUND THEN
         V_OVER_COUNT := CEIL(R_ORDERS.PRODUCT_CNT - R_RATE1.PRESET_WEIGHT);
         IF V_OVER_COUNT > 0 THEN
           V_OVER_CHARG := (R_RATE1.FIRST_PRICE + R_RATE1.EXTRA_PRICE * V_OVER_COUNT)*R_RATE1.RATE;
         ELSE
           V_OVER_COUNT := 0;
           V_OVER_CHARG := 0;
         END IF;
         --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'超订单件数费：'||V_OVER_CHARG);
       ELSE
             V_ERROR := ',未找到超订单件数费配置'||V_ERROR;
       END IF;
       EXIT;
       END LOOP;
       CLOSE CUR_RATES1;
       -- 操作费
       OPEN CUR_RATES1(R_ORDERS.CUSTOMER_CODE,R_ORDERS.OUT_SERVICE_FIELD8,R_ORDERS.CARRIER_CODE,'C004',NULL);
       LOOP
       FETCH CUR_RATES1 INTO R_RATE1;
       IF CUR_RATES1%FOUND THEN
         V_OPT_CHARG := R_RATE1.FIRST_PRICE * R_RATE1.RATE;
         --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'操作费：'||V_OPT_CHARG);
       ELSE
          V_ERROR := ',未找到操作费配置'||V_ERROR;
       END IF;
       EXIT;
       END LOOP;
       CLOSE CUR_RATES1;
       IF V_ERROR IS NOT NULL THEN 
         V_ERROR := '客户订单号【'||R_ORDERS.CUSTOMER_REFERENCE_ID||'】'||V_ERROR;
       END IF;
       --DELETE FROM WMS_EXPRESS_CHARGES C WHERE C.LOGISTICS_NO = R_ORDERS.CARRIER_TRACKING_NUMBER AND C.RP_FLAG = 1;
       INSERT INTO WMS_EXPRESS_CHARGES
        (ID,
         ORDER_TYPE,
         VENDER_ID,
         VENDER_CODE,
         VENDER_NAME,
         SHOP_NAME,
         CUSTOMER_ORDER_NO,
         LOGISTICS_NO,
         SYSTEM_WEIGHT,
         PAYMENT_TIME,
         PAY_AMOUNT,
         PROVINCE,
         CITY,
         AREA,
         DELIVERY_NUMBER,
         FIRST_WEIGHT,
         FIRST_PRICE,
         ADDITIONAL_WEIGHT,
         ADDITIONAL_PRICE,
         EXPRESS_CHARGES,
         OVERBOOKING_NUMBER,
         OVERBOOKING_FEE,
         COMMISSION_FEE,
         OPERATION_FEE,
         TOTAL_FEE,
         CREATED_BY,
         CREATION_METHOD,
         CREATION_TIME,
         DELETE_FLAG,
         UPDATE_METHOD,
         UPDATE_TIME,
         UPDATED_BY,
         ACCOUNT_ID,
         CUSTOMER_ID,
         CUSTOMER_CODE,
         CUSTOMER_NAME,
         WAREHOUSE_ID,
         WAREHOUSE_NAME,
         RELATIONED_CUSTOMER_ID,
         RELATIONED_CUSTOMER_CODE,
         RELATIONED_CUSTOMER_NAME,
         RP_FLAG,
         ITEM_DETAIL,
         REMARK)
      values
        (SYS_GUID(),
         R_ORDERS.ORDER_TYPE,
         R_ORDERS.CARRIER_ID,
         R_ORDERS.CARRIER_CODE,
         R_ORDERS.CARRIER_NAME,
         R_ORDERS.SHOP_NAME,
         R_ORDERS.CUSTOMER_REFERENCE_ID,
         R_ORDERS.CARRIER_TRACKING_NUMBER,
         R_ORDERS.WEIGHT,
         R_ORDERS.PAY_TIME,
         R_ORDERS.TOTAL_FEE,
         R_ORDERS.PROVINCE2,
         R_ORDERS.CITY2，
         R_ORDERS.DISTRICT2,
         R_ORDERS.PRODUCT_CNT,
         V_FIRST_WEIGHT,
         V_FIRST_PRICE,
         V_EXP_EXTRA_WEIGHT,
         V_EXTRA_PRICE,
         V_EXP_CHARG,
         V_OVER_COUNT,
         V_OVER_CHARG,
         V_EXP_CHARG1,
         V_OPT_CHARG,
         V_OPT_CHARG+V_EXP_CHARG1+V_EXP_CHARG+V_OVER_CHARG,
         'SYSTEM',
         'SP',
         SYSDATE,
         0,
         NULL,
         NULL,
         NULL,
         R_ORDERS.ACCOUNT_ID,
         R_ORDERS.CUSTOMER_ID,
         R_ORDERS.CUSTOMER_CODE,
         R_ORDERS.CUSTOMER_NAME,
         R_ORDERS.WAREHOUSE_ID,
         R_ORDERS.WAREHOUSE_NAME,
         null,
         null,
         null,
         1,
         V_ITEM_DETAIL,
         V_ERROR);
         --RAISE_APPLICATION_ERROR('-111','计费错误');
         -- 更新计费状态
         COMMIT;
      EXCEPTION 
        WHEN OTHERS THEN
          ROLLBACK;
          V_ERROR := '计费异常：'||SQLERRM;
          INSERT INTO WMS_EXPRESS_CHARGES
            (ID,
             ORDER_TYPE,
             VENDER_ID,
             VENDER_CODE,
             VENDER_NAME,
             SHOP_NAME,
             CUSTOMER_ORDER_NO,
             LOGISTICS_NO,
             SYSTEM_WEIGHT,
             PAYMENT_TIME,
             PAY_AMOUNT,
             PROVINCE,
             CITY,
             AREA,
             DELIVERY_NUMBER,
             FIRST_WEIGHT,
             FIRST_PRICE,
             ADDITIONAL_WEIGHT,
             ADDITIONAL_PRICE,
             EXPRESS_CHARGES,
             OVERBOOKING_NUMBER,
             OVERBOOKING_FEE,
             COMMISSION_FEE,
             OPERATION_FEE,
             TOTAL_FEE,
             CREATED_BY,
             CREATION_METHOD,
             CREATION_TIME,
             DELETE_FLAG,
             UPDATE_METHOD,
             UPDATE_TIME,
             UPDATED_BY,
             ACCOUNT_ID,
             CUSTOMER_ID,
             CUSTOMER_CODE,
             CUSTOMER_NAME,
             WAREHOUSE_ID,
             WAREHOUSE_NAME,
             RELATIONED_CUSTOMER_ID,
             RELATIONED_CUSTOMER_CODE,
             RELATIONED_CUSTOMER_NAME,
             RP_FLAG,
             ITEM_DETAIL,
             REMARK)
          values
            (SYS_GUID(),
             R_ORDERS.ORDER_TYPE,
             R_ORDERS.CARRIER_ID,
             R_ORDERS.CARRIER_CODE,
             R_ORDERS.CARRIER_NAME,
             R_ORDERS.SHOP_NAME,
             R_ORDERS.CUSTOMER_REFERENCE_ID,
             R_ORDERS.CARRIER_TRACKING_NUMBER,
             R_ORDERS.WEIGHT,
             R_ORDERS.PAY_TIME,
             R_ORDERS.TOTAL_FEE,
             R_ORDERS.PROVINCE2,
             R_ORDERS.CITY2，
             R_ORDERS.DISTRICT2,
             R_ORDERS.PRODUCT_CNT,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             NULL,
             'SYSTEM',
             'SP',
             SYSDATE,
             0,
             NULL,
             NULL,
             NULL,
             R_ORDERS.ACCOUNT_ID,
             R_ORDERS.CUSTOMER_ID,
             R_ORDERS.CUSTOMER_CODE,
             R_ORDERS.CUSTOMER_NAME,
             R_ORDERS.WAREHOUSE_ID,
             R_ORDERS.WAREHOUSE_NAME,
             null,
             null,
             null,
             1,
             V_ITEM_DETAIL,
             '计费异常：'||V_ERROR);
             -- 更新计费状态
             COMMIT;
       END;
       UPDATE WMS_ORDERS O SET O.IS_CAL_CHARGE = 1 WHERE O.ORDER_ID = R_ORDERS.ORDER_ID;
       COMMIT;
      END LOOP;
    CLOSE CUR_ORDERS; -- 关闭订单游标
    dbms_output.put_line(V_CUR_COUNT ||'  '||V_CUR_COUNT);
    IF V_CUR_COUNT <> V_PROC_COUNT THEN
      EXIT;
    END IF;
  --EXIT;
  END LOOP;
  DBMS_OUTPUT.put_line('计费耗时：' ||to_number(V_END_TIME-V_START_TIME));
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    IF CUR_ORDERS%ISOPEN THEN
      CLOSE CUR_ORDERS;   -- 关闭订单游标
    END IF;
    IF CUR_RATES%ISOPEN THEN
      CLOSE CUR_RATES;  -- 关闭费用游标
     END IF;
     DBMS_OUTPUT.put_line('sqlerrm : ' ||sqlerrm); 
  V_END_TIME := SYSDATE; 
end SP_AUTO_LEDGER_EXCUTE;