123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675 |
- 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;
|