SP_AUTO_LEDGER_EXCUTE.sql 20 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675
  1. create or replace procedure SP_AUTO_LEDGER_EXCUTE(P_ORDER_ID IN STRING) is
  2. /**
  3. * 快递计费
  4. * 应付 E001-快递费 E002-操作费
  5. * 应收 C001-快递费 C002-快递抽佣 C003-超订单件数费 C004-操作费
  6. * 2016-03-04 scrazy
  7. */
  8. -- 一个循环处理条数
  9. V_PROC_COUNT NUMBER;
  10. V_CUR_COUNT NUMBER;
  11. -- 费用
  12. V_EXP_CHARG NUMBER; -- 快递金额
  13. V_EXP_CHARG1 NUMBER; -- 快递抽佣费
  14. V_OPT_CHARG NUMBER; --操作金额
  15. V_EXP_EXTRA_WEIGHT NUMBER; -- 续重
  16. V_OVER_COUNT NUMBER; --超订单件数
  17. V_OVER_CHARG NUMBER; -- 超订件数费
  18. V_FIRST_WEIGHT NUMBER;
  19. V_FIRST_PRICE NUMBER;
  20. V_EXTRA_PRICE NUMBER;
  21. V_ITEM_DETAIL STRING(2000);
  22. V_ERROR VARCHAR(2000);
  23. V_START_TIME DATE;
  24. V_END_TIME DATE;
  25. ------------ 游标定义 ------------
  26. -- 查询需要计费的订单 --
  27. cursor CUR_ORDERS is
  28. select D.ORDER_ID,
  29. D.CUSTOMER_REFERENCE_ID,
  30. D.CUSTOMER_ID,
  31. D.CUSTOMER_CODE,
  32. D.CUSTOMER_NAME,
  33. D.CARRIER_CODE,
  34. D.CARRIER_TRACKING_NUMBER,
  35. D.CARRIER_NAME,
  36. D.RECEIVER_PROVINCE,
  37. D.CARRIER_ID,
  38. DECODE(O.OUTBOUND_KIND,
  39. 2301,
  40. '线上销售订单',
  41. 2304,
  42. '批发出库订单',
  43. 2303,
  44. '退货出库订单',
  45. 2302,
  46. '调拨订单') ORDER_TYPE,
  47. D.WAREHOUSE_ID,
  48. D.WAREHOUSE_NAME,
  49. D.RECEIVER_PROVINCE PROVINCE2,
  50. D.RECEIVER_CITY CITY2,
  51. D.RECEIVER_DISTRICT DISTRICT2,
  52. D.PRODUCT_CNT,
  53. D.WEIGHT,
  54. O.SHOP_NAME,
  55. O.ACCOUNT_ID,
  56. O.PAY_CASH,
  57. O.PAY_TIME,
  58. O.TOTAL_FEE,
  59. O.OUT_SERVICE_FIELD8 --货主联系人(真实货主)
  60. from WMS_DELIVER_DOCUMENT D
  61. LEFT JOIN WMS_ORDERS O
  62. ON D.ORDER_ID = O.ORDER_ID
  63. WHERE (P_ORDER_ID IS NULL OR O.ORDER_ID = P_ORDER_ID)
  64. AND O.OUTBOUND_KIND IN ('2301', '2302', '2303', '2304')
  65. --AND O.CUSTOMER_CODE = 'DD01'
  66. AND O.IS_CAL_CHARGE = 0
  67. AND D.CARRIER_CODE IS NOT NULL
  68. AND O.ORDER_STATUS = '5203'
  69. AND ROWNUM < V_PROC_COUNT + 1;
  70. R_ORDERS CUR_ORDERS%rowtype;
  71. -- 查询应付 快递费率
  72. 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
  73. SELECT CR.PRESET_WEIGHT,
  74. CR.FIRST_PRICE,
  75. CR.EXTRA_WEIGHT,
  76. CR.EXTRA_PRICE,
  77. CR.RATE
  78. FROM WMS_CONTRACT_CHARG_RATE CR
  79. LEFT JOIN WMS_CONTRACT_CHARG CC
  80. ON (CR.CONTRACT_CHARGE_ID = CC.ID)
  81. LEFT JOIN CRM_VENDER_CARRIAGE VC
  82. ON (CC.CONTRACT_ID = VC.CONTRACT_ID)
  83. WHERE VC.CONTRACT_STATUS = '9402'
  84. AND VC.EFFECT_DATE < SYSDATE
  85. AND VC.MATURITY_DATE > SYSDATE
  86. AND CC.CHARGE_CODE = V_CHARGE
  87. AND VC.VENDER_CODE = V_CARRIER_CODE
  88. -- AND (CR.ORIG_PROVINCE IS NULL OR CR.ORIG_PROVINCE = V_ORIG_PROVINCE)
  89. AND (V_DEST_PROVINCE IS NULL OR CR.DEST_PROVINCE LIKE '%'||V_DEST_PROVINCE||'%' OR CR.DEST_PROVINCE IS NULL)
  90. AND ROWNUM = 1;
  91. R_RATE CUR_RATES%ROWTYPE;
  92. -- 查询应收
  93. 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
  94. SELECT CR.PRESET_WEIGHT,
  95. CR.FIRST_PRICE,
  96. CR.EXTRA_WEIGHT,
  97. CR.EXTRA_PRICE,
  98. CR.RATE
  99. FROM WMS_CONTRACT_CHARG_RATE CR
  100. LEFT JOIN WMS_CONTRACT_CHARG CC
  101. ON (CR.CONTRACT_CHARGE_ID = CC.ID)
  102. LEFT JOIN CRM_WAREHOUSE_CHARGE WC
  103. ON (CC.CONTRACT_ID = WC.CONTRACT_ID)
  104. WHERE WC.CONTRACT_STATUS = '9402'
  105. AND WC.EFFECT_DATE < SYSDATE
  106. AND WC.MATURITY_DATE > SYSDATE
  107. AND CC.CHARGE_CODE = V_CHARGE
  108. AND WC.CUSTOMER_CODE = V_CUSTOMER_CODE
  109. AND WC.RELATIONED_CUSTOMER_NAME = V_RELATIONED_CUSTOMER_NAME
  110. AND (CR.VENDER_CODE IS NULL
  111. OR CR.VENDER_CODE = V_CARRIER_CODE)
  112. --AND (CR.ORIG_PROVINCE IS NULL OR CR.ORIG_PROVINCE = V_ORIG_PROVINCE)
  113. AND (V_DEST_PROVINCE IS NULL OR CR.DEST_PROVINCE LIKE '%'||V_DEST_PROVINCE||'%' OR CR.DEST_PROVINCE IS NULL) AND ROWNUM = 1;
  114. R_RATE1 CUR_RATES1%ROWTYPE;
  115. BEGIN
  116. V_START_TIME := SYSDATE;
  117. -- 初始化参数
  118. V_PROC_COUNT := 100; -- 处理订单数
  119. LOOP
  120. OPEN CUR_ORDERS; --打开订单游标
  121. V_CUR_COUNT := 0;
  122. LOOP
  123. FETCH CUR_ORDERS INTO R_ORDERS;
  124. EXIT WHEN CUR_ORDERS%NOTFOUND;
  125. V_CUR_COUNT := V_CUR_COUNT + 1;
  126. V_EXP_CHARG := NULL; -- 快递金额
  127. V_EXP_CHARG1 := NULL; -- 快递抽佣费
  128. V_OPT_CHARG := NULL; --操作金额
  129. V_EXP_EXTRA_WEIGHT := NULL; -- 续重
  130. V_OVER_COUNT := NULL; --超订单件数
  131. V_OVER_CHARG := NULL; -- 超订件数费
  132. V_FIRST_WEIGHT := NULL;
  133. V_FIRST_PRICE := NULL;
  134. V_EXTRA_PRICE := NULL;
  135. V_ERROR := NULL;
  136. BEGIN
  137. --应付
  138. -- 快递费
  139. SP_GET_ITEMDETAIL(R_ORDERS.ORDER_ID,V_ITEM_DETAIL);
  140. OPEN CUR_RATES(R_ORDERS.CARRIER_CODE,'E001',R_ORDERS.RECEIVER_PROVINCE);
  141. LOOP
  142. FETCH CUR_RATES INTO R_RATE;
  143. IF CUR_RATES%FOUND THEN
  144. V_FIRST_WEIGHT := R_RATE.PRESET_WEIGHT;
  145. V_FIRST_PRICE := R_RATE.FIRST_PRICE;
  146. V_EXTRA_PRICE := R_RATE.EXTRA_PRICE;
  147. V_EXP_EXTRA_WEIGHT := CEIL(R_ORDERS.WEIGHT - V_FIRST_WEIGHT);
  148. IF V_EXP_EXTRA_WEIGHT > 0.1 THEN
  149. V_EXP_CHARG := (V_FIRST_WEIGHT*V_FIRST_PRICE + R_RATE.EXTRA_PRICE * V_EXP_EXTRA_WEIGHT)*R_RATE.RATE;
  150. ELSE
  151. V_EXP_EXTRA_WEIGHT := 0;
  152. V_EXP_CHARG := V_FIRST_WEIGHT*V_FIRST_PRICE*R_RATE.RATE;
  153. END IF;
  154. --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'快递费:'||V_EXP_CHARG);
  155. ELSE
  156. V_ERROR := ',未找到快递费配置'||V_ERROR;
  157. END IF;
  158. EXIT;
  159. END LOOP;
  160. CLOSE CUR_RATES;
  161. -- 操作费
  162. OPEN CUR_RATES(R_ORDERS.CARRIER_CODE,'E002',NULL);
  163. LOOP
  164. FETCH CUR_RATES INTO R_RATE;
  165. IF CUR_RATES%FOUND THEN
  166. V_OPT_CHARG := R_RATE.FIRST_PRICE * R_RATE.RATE;
  167. --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'操作费:'||V_OPT_CHARG);
  168. ELSE
  169. V_ERROR := ',未找到操作费配置'||V_ERROR;
  170. END IF;
  171. EXIT;
  172. END LOOP;
  173. CLOSE CUR_RATES;
  174. IF V_ERROR IS NOT NULL THEN
  175. V_ERROR := '客户订单号【'||R_ORDERS.CUSTOMER_REFERENCE_ID||'】'||V_ERROR;
  176. END IF;
  177. -- 添加应付费用记录
  178. --DELETE FROM WMS_EXPRESS_CHARGES C WHERE C.LOGISTICS_NO = R_ORDERS.CARRIER_TRACKING_NUMBER AND C.RP_FLAG = 0;
  179. INSERT INTO WMS_EXPRESS_CHARGES
  180. (ID,
  181. ORDER_TYPE,
  182. VENDER_ID,
  183. VENDER_CODE,
  184. VENDER_NAME,
  185. SHOP_NAME,
  186. CUSTOMER_ORDER_NO,
  187. LOGISTICS_NO,
  188. SYSTEM_WEIGHT,
  189. PAYMENT_TIME,
  190. PAY_AMOUNT,
  191. PROVINCE,
  192. CITY,
  193. AREA,
  194. DELIVERY_NUMBER,
  195. FIRST_WEIGHT,
  196. FIRST_PRICE,
  197. ADDITIONAL_WEIGHT,
  198. ADDITIONAL_PRICE,
  199. EXPRESS_CHARGES,
  200. OVERBOOKING_NUMBER,
  201. OVERBOOKING_FEE,
  202. COMMISSION_FEE,
  203. OPERATION_FEE,
  204. TOTAL_FEE,
  205. CREATED_BY,
  206. CREATION_METHOD,
  207. CREATION_TIME,
  208. DELETE_FLAG,
  209. UPDATE_METHOD,
  210. UPDATE_TIME,
  211. UPDATED_BY,
  212. ACCOUNT_ID,
  213. CUSTOMER_ID,
  214. CUSTOMER_CODE,
  215. CUSTOMER_NAME,
  216. WAREHOUSE_ID,
  217. WAREHOUSE_NAME,
  218. RELATIONED_CUSTOMER_ID,
  219. RELATIONED_CUSTOMER_CODE,
  220. RELATIONED_CUSTOMER_NAME,
  221. RP_FLAG,
  222. ITEM_DETAIL,
  223. REMARK)
  224. values
  225. (SYS_GUID(),
  226. R_ORDERS.ORDER_TYPE,
  227. R_ORDERS.CARRIER_ID,
  228. R_ORDERS.CARRIER_CODE,
  229. R_ORDERS.CARRIER_NAME,
  230. R_ORDERS.SHOP_NAME,
  231. R_ORDERS.CUSTOMER_REFERENCE_ID,
  232. R_ORDERS.CARRIER_TRACKING_NUMBER,
  233. R_ORDERS.WEIGHT,
  234. R_ORDERS.PAY_TIME,
  235. R_ORDERS.TOTAL_FEE,
  236. R_ORDERS.PROVINCE2,
  237. R_ORDERS.CITY2,
  238. R_ORDERS.DISTRICT2,
  239. R_ORDERS.PRODUCT_CNT,
  240. V_FIRST_WEIGHT,
  241. V_FIRST_PRICE,
  242. V_EXP_EXTRA_WEIGHT,
  243. V_EXTRA_PRICE,
  244. V_EXP_CHARG,
  245. NULL,
  246. NULL,
  247. NULL,
  248. V_OPT_CHARG,
  249. V_OPT_CHARG+V_EXP_CHARG,
  250. 'SYSTEM',
  251. 'SP',
  252. SYSDATE,
  253. 0,
  254. NULL,
  255. NULL,
  256. NULL,
  257. R_ORDERS.ACCOUNT_ID,
  258. R_ORDERS.CUSTOMER_ID,
  259. R_ORDERS.CUSTOMER_CODE,
  260. R_ORDERS.CUSTOMER_NAME,
  261. R_ORDERS.WAREHOUSE_ID,
  262. R_ORDERS.WAREHOUSE_NAME,
  263. null,
  264. null,
  265. null,
  266. 0,
  267. V_ITEM_DETAIL,
  268. V_ERROR);
  269. -- 更新计费状态
  270. COMMIT;
  271. EXCEPTION
  272. WHEN OTHERS THEN
  273. V_ERROR := '计费异常:'||SQLERRM;
  274. INSERT INTO WMS_EXPRESS_CHARGES
  275. (ID,
  276. ORDER_TYPE,
  277. VENDER_ID,
  278. VENDER_CODE,
  279. VENDER_NAME,
  280. SHOP_NAME,
  281. CUSTOMER_ORDER_NO,
  282. LOGISTICS_NO,
  283. SYSTEM_WEIGHT,
  284. PAYMENT_TIME,
  285. PAY_AMOUNT,
  286. PROVINCE,
  287. CITY,
  288. AREA,
  289. DELIVERY_NUMBER,
  290. FIRST_WEIGHT,
  291. FIRST_PRICE,
  292. ADDITIONAL_WEIGHT,
  293. ADDITIONAL_PRICE,
  294. EXPRESS_CHARGES,
  295. OVERBOOKING_NUMBER,
  296. OVERBOOKING_FEE,
  297. COMMISSION_FEE,
  298. OPERATION_FEE,
  299. TOTAL_FEE,
  300. CREATED_BY,
  301. CREATION_METHOD,
  302. CREATION_TIME,
  303. DELETE_FLAG,
  304. UPDATE_METHOD,
  305. UPDATE_TIME,
  306. UPDATED_BY,
  307. ACCOUNT_ID,
  308. CUSTOMER_ID,
  309. CUSTOMER_CODE,
  310. CUSTOMER_NAME,
  311. WAREHOUSE_ID,
  312. WAREHOUSE_NAME,
  313. RELATIONED_CUSTOMER_ID,
  314. RELATIONED_CUSTOMER_CODE,
  315. RELATIONED_CUSTOMER_NAME,
  316. RP_FLAG,
  317. ITEM_DETAIL,
  318. REMARK)
  319. values
  320. (SYS_GUID(),
  321. R_ORDERS.ORDER_TYPE,
  322. R_ORDERS.CARRIER_ID,
  323. R_ORDERS.CARRIER_CODE,
  324. R_ORDERS.CARRIER_NAME,
  325. R_ORDERS.SHOP_NAME,
  326. R_ORDERS.CUSTOMER_REFERENCE_ID,
  327. R_ORDERS.CARRIER_TRACKING_NUMBER,
  328. R_ORDERS.WEIGHT,
  329. R_ORDERS.PAY_TIME,
  330. R_ORDERS.TOTAL_FEE,
  331. R_ORDERS.PROVINCE2,
  332. R_ORDERS.CITY2,
  333. R_ORDERS.DISTRICT2,
  334. R_ORDERS.PRODUCT_CNT,
  335. NULL,
  336. NULL,
  337. NULL,
  338. NULL,
  339. NULL,
  340. NULL,
  341. NULL,
  342. NULL,
  343. NULL,
  344. NULL,
  345. 'SYSTEM',
  346. 'SP',
  347. SYSDATE,
  348. 0,
  349. NULL,
  350. NULL,
  351. NULL,
  352. R_ORDERS.ACCOUNT_ID,
  353. R_ORDERS.CUSTOMER_ID,
  354. R_ORDERS.CUSTOMER_CODE,
  355. R_ORDERS.CUSTOMER_NAME,
  356. R_ORDERS.WAREHOUSE_ID,
  357. R_ORDERS.WAREHOUSE_NAME,
  358. null,
  359. null,
  360. null,
  361. 0,
  362. V_ITEM_DETAIL,
  363. '计费异常:'||V_ERROR);
  364. -- 更新计费状态
  365. COMMIT;
  366. END;
  367. BEGIN
  368. -- 应收
  369. V_EXP_CHARG := NULL; -- 快递金额
  370. V_EXP_CHARG1 := NULL; -- 快递抽佣费
  371. V_OPT_CHARG := NULL; --操作金额
  372. V_EXP_EXTRA_WEIGHT := NULL; -- 续重
  373. V_OVER_COUNT := NULL; --超订单件数
  374. V_OVER_CHARG := NULL; -- 超订件数费
  375. V_FIRST_WEIGHT := NULL;
  376. V_FIRST_PRICE := NULL;
  377. V_EXTRA_PRICE := NULL;
  378. V_ERROR := NULL;
  379. -- 快递费
  380. OPEN CUR_RATES1(R_ORDERS.CUSTOMER_CODE,R_ORDERS.OUT_SERVICE_FIELD8,R_ORDERS.CARRIER_CODE,'C001',R_ORDERS.RECEIVER_PROVINCE);
  381. LOOP
  382. FETCH CUR_RATES1 INTO R_RATE1;
  383. IF CUR_RATES1%FOUND THEN
  384. V_FIRST_WEIGHT := R_RATE1.PRESET_WEIGHT;
  385. V_FIRST_PRICE := R_RATE1.FIRST_PRICE;
  386. V_EXTRA_PRICE := R_RATE1.EXTRA_PRICE;
  387. V_EXP_EXTRA_WEIGHT := CEIL(R_ORDERS.WEIGHT - V_FIRST_WEIGHT);
  388. IF V_EXP_EXTRA_WEIGHT > 0.1 THEN
  389. V_EXP_CHARG := (V_FIRST_WEIGHT*V_FIRST_PRICE + R_RATE1.EXTRA_PRICE * V_EXP_EXTRA_WEIGHT)*R_RATE1.RATE;
  390. ELSE
  391. V_EXP_EXTRA_WEIGHT := 0;
  392. V_EXP_CHARG := V_FIRST_WEIGHT*V_FIRST_PRICE*R_RATE1.RATE;
  393. END IF;
  394. --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'快递费:'||V_EXP_CHARG);
  395. ELSE
  396. V_ERROR := ',未找到快递费配置'||V_ERROR;
  397. END IF;
  398. EXIT;
  399. END LOOP;
  400. CLOSE CUR_RATES1;
  401. -- 快递抽佣
  402. OPEN CUR_RATES1(R_ORDERS.CUSTOMER_CODE,R_ORDERS.OUT_SERVICE_FIELD8,R_ORDERS.CARRIER_CODE,'C002',NULL);
  403. LOOP
  404. FETCH CUR_RATES1 INTO R_RATE1;
  405. IF CUR_RATES1%FOUND THEN
  406. V_EXP_CHARG1 := V_EXP_CHARG*R_RATE1.RATE;
  407. --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'快递抽佣:'||V_EXP_CHARG1);
  408. ELSE
  409. V_ERROR := ',未找到快递抽佣配置'||V_ERROR;
  410. END IF;
  411. EXIT;
  412. END LOOP;
  413. CLOSE CUR_RATES1;
  414. -- 操订单件数费
  415. OPEN CUR_RATES1(R_ORDERS.CUSTOMER_CODE,R_ORDERS.OUT_SERVICE_FIELD8,R_ORDERS.CARRIER_CODE,'C003',NULL);
  416. LOOP
  417. FETCH CUR_RATES1 INTO R_RATE1;
  418. IF CUR_RATES1%FOUND THEN
  419. V_OVER_COUNT := CEIL(R_ORDERS.PRODUCT_CNT - R_RATE1.PRESET_WEIGHT);
  420. IF V_OVER_COUNT > 0 THEN
  421. V_OVER_CHARG := (R_RATE1.FIRST_PRICE + R_RATE1.EXTRA_PRICE * V_OVER_COUNT)*R_RATE1.RATE;
  422. ELSE
  423. V_OVER_COUNT := 0;
  424. V_OVER_CHARG := 0;
  425. END IF;
  426. --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'超订单件数费:'||V_OVER_CHARG);
  427. ELSE
  428. V_ERROR := ',未找到超订单件数费配置'||V_ERROR;
  429. END IF;
  430. EXIT;
  431. END LOOP;
  432. CLOSE CUR_RATES1;
  433. -- 操作费
  434. OPEN CUR_RATES1(R_ORDERS.CUSTOMER_CODE,R_ORDERS.OUT_SERVICE_FIELD8,R_ORDERS.CARRIER_CODE,'C004',NULL);
  435. LOOP
  436. FETCH CUR_RATES1 INTO R_RATE1;
  437. IF CUR_RATES1%FOUND THEN
  438. V_OPT_CHARG := R_RATE1.FIRST_PRICE * R_RATE1.RATE;
  439. --dbms_output.put_line('客户单号'||R_ORDERS.CUSTOMER_REFERENCE_ID||'操作费:'||V_OPT_CHARG);
  440. ELSE
  441. V_ERROR := ',未找到操作费配置'||V_ERROR;
  442. END IF;
  443. EXIT;
  444. END LOOP;
  445. CLOSE CUR_RATES1;
  446. IF V_ERROR IS NOT NULL THEN
  447. V_ERROR := '客户订单号【'||R_ORDERS.CUSTOMER_REFERENCE_ID||'】'||V_ERROR;
  448. END IF;
  449. --DELETE FROM WMS_EXPRESS_CHARGES C WHERE C.LOGISTICS_NO = R_ORDERS.CARRIER_TRACKING_NUMBER AND C.RP_FLAG = 1;
  450. INSERT INTO WMS_EXPRESS_CHARGES
  451. (ID,
  452. ORDER_TYPE,
  453. VENDER_ID,
  454. VENDER_CODE,
  455. VENDER_NAME,
  456. SHOP_NAME,
  457. CUSTOMER_ORDER_NO,
  458. LOGISTICS_NO,
  459. SYSTEM_WEIGHT,
  460. PAYMENT_TIME,
  461. PAY_AMOUNT,
  462. PROVINCE,
  463. CITY,
  464. AREA,
  465. DELIVERY_NUMBER,
  466. FIRST_WEIGHT,
  467. FIRST_PRICE,
  468. ADDITIONAL_WEIGHT,
  469. ADDITIONAL_PRICE,
  470. EXPRESS_CHARGES,
  471. OVERBOOKING_NUMBER,
  472. OVERBOOKING_FEE,
  473. COMMISSION_FEE,
  474. OPERATION_FEE,
  475. TOTAL_FEE,
  476. CREATED_BY,
  477. CREATION_METHOD,
  478. CREATION_TIME,
  479. DELETE_FLAG,
  480. UPDATE_METHOD,
  481. UPDATE_TIME,
  482. UPDATED_BY,
  483. ACCOUNT_ID,
  484. CUSTOMER_ID,
  485. CUSTOMER_CODE,
  486. CUSTOMER_NAME,
  487. WAREHOUSE_ID,
  488. WAREHOUSE_NAME,
  489. RELATIONED_CUSTOMER_ID,
  490. RELATIONED_CUSTOMER_CODE,
  491. RELATIONED_CUSTOMER_NAME,
  492. RP_FLAG,
  493. ITEM_DETAIL,
  494. REMARK)
  495. values
  496. (SYS_GUID(),
  497. R_ORDERS.ORDER_TYPE,
  498. R_ORDERS.CARRIER_ID,
  499. R_ORDERS.CARRIER_CODE,
  500. R_ORDERS.CARRIER_NAME,
  501. R_ORDERS.SHOP_NAME,
  502. R_ORDERS.CUSTOMER_REFERENCE_ID,
  503. R_ORDERS.CARRIER_TRACKING_NUMBER,
  504. R_ORDERS.WEIGHT,
  505. R_ORDERS.PAY_TIME,
  506. R_ORDERS.TOTAL_FEE,
  507. R_ORDERS.PROVINCE2,
  508. R_ORDERS.CITY2,
  509. R_ORDERS.DISTRICT2,
  510. R_ORDERS.PRODUCT_CNT,
  511. V_FIRST_WEIGHT,
  512. V_FIRST_PRICE,
  513. V_EXP_EXTRA_WEIGHT,
  514. V_EXTRA_PRICE,
  515. V_EXP_CHARG,
  516. V_OVER_COUNT,
  517. V_OVER_CHARG,
  518. V_EXP_CHARG1,
  519. V_OPT_CHARG,
  520. V_OPT_CHARG+V_EXP_CHARG1+V_EXP_CHARG+V_OVER_CHARG,
  521. 'SYSTEM',
  522. 'SP',
  523. SYSDATE,
  524. 0,
  525. NULL,
  526. NULL,
  527. NULL,
  528. R_ORDERS.ACCOUNT_ID,
  529. R_ORDERS.CUSTOMER_ID,
  530. R_ORDERS.CUSTOMER_CODE,
  531. R_ORDERS.CUSTOMER_NAME,
  532. R_ORDERS.WAREHOUSE_ID,
  533. R_ORDERS.WAREHOUSE_NAME,
  534. null,
  535. null,
  536. null,
  537. 1,
  538. V_ITEM_DETAIL,
  539. V_ERROR);
  540. --RAISE_APPLICATION_ERROR('-111','计费错误');
  541. -- 更新计费状态
  542. COMMIT;
  543. EXCEPTION
  544. WHEN OTHERS THEN
  545. ROLLBACK;
  546. V_ERROR := '计费异常:'||SQLERRM;
  547. INSERT INTO WMS_EXPRESS_CHARGES
  548. (ID,
  549. ORDER_TYPE,
  550. VENDER_ID,
  551. VENDER_CODE,
  552. VENDER_NAME,
  553. SHOP_NAME,
  554. CUSTOMER_ORDER_NO,
  555. LOGISTICS_NO,
  556. SYSTEM_WEIGHT,
  557. PAYMENT_TIME,
  558. PAY_AMOUNT,
  559. PROVINCE,
  560. CITY,
  561. AREA,
  562. DELIVERY_NUMBER,
  563. FIRST_WEIGHT,
  564. FIRST_PRICE,
  565. ADDITIONAL_WEIGHT,
  566. ADDITIONAL_PRICE,
  567. EXPRESS_CHARGES,
  568. OVERBOOKING_NUMBER,
  569. OVERBOOKING_FEE,
  570. COMMISSION_FEE,
  571. OPERATION_FEE,
  572. TOTAL_FEE,
  573. CREATED_BY,
  574. CREATION_METHOD,
  575. CREATION_TIME,
  576. DELETE_FLAG,
  577. UPDATE_METHOD,
  578. UPDATE_TIME,
  579. UPDATED_BY,
  580. ACCOUNT_ID,
  581. CUSTOMER_ID,
  582. CUSTOMER_CODE,
  583. CUSTOMER_NAME,
  584. WAREHOUSE_ID,
  585. WAREHOUSE_NAME,
  586. RELATIONED_CUSTOMER_ID,
  587. RELATIONED_CUSTOMER_CODE,
  588. RELATIONED_CUSTOMER_NAME,
  589. RP_FLAG,
  590. ITEM_DETAIL,
  591. REMARK)
  592. values
  593. (SYS_GUID(),
  594. R_ORDERS.ORDER_TYPE,
  595. R_ORDERS.CARRIER_ID,
  596. R_ORDERS.CARRIER_CODE,
  597. R_ORDERS.CARRIER_NAME,
  598. R_ORDERS.SHOP_NAME,
  599. R_ORDERS.CUSTOMER_REFERENCE_ID,
  600. R_ORDERS.CARRIER_TRACKING_NUMBER,
  601. R_ORDERS.WEIGHT,
  602. R_ORDERS.PAY_TIME,
  603. R_ORDERS.TOTAL_FEE,
  604. R_ORDERS.PROVINCE2,
  605. R_ORDERS.CITY2,
  606. R_ORDERS.DISTRICT2,
  607. R_ORDERS.PRODUCT_CNT,
  608. NULL,
  609. NULL,
  610. NULL,
  611. NULL,
  612. NULL,
  613. NULL,
  614. NULL,
  615. NULL,
  616. NULL,
  617. NULL,
  618. 'SYSTEM',
  619. 'SP',
  620. SYSDATE,
  621. 0,
  622. NULL,
  623. NULL,
  624. NULL,
  625. R_ORDERS.ACCOUNT_ID,
  626. R_ORDERS.CUSTOMER_ID,
  627. R_ORDERS.CUSTOMER_CODE,
  628. R_ORDERS.CUSTOMER_NAME,
  629. R_ORDERS.WAREHOUSE_ID,
  630. R_ORDERS.WAREHOUSE_NAME,
  631. null,
  632. null,
  633. null,
  634. 1,
  635. V_ITEM_DETAIL,
  636. '计费异常:'||V_ERROR);
  637. -- 更新计费状态
  638. COMMIT;
  639. END;
  640. UPDATE WMS_ORDERS O SET O.IS_CAL_CHARGE = 1 WHERE O.ORDER_ID = R_ORDERS.ORDER_ID;
  641. COMMIT;
  642. END LOOP;
  643. CLOSE CUR_ORDERS; -- 关闭订单游标
  644. dbms_output.put_line(V_CUR_COUNT ||' '||V_CUR_COUNT);
  645. IF V_CUR_COUNT <> V_PROC_COUNT THEN
  646. EXIT;
  647. END IF;
  648. --EXIT;
  649. END LOOP;
  650. DBMS_OUTPUT.put_line('计费耗时:' ||to_number(V_END_TIME-V_START_TIME));
  651. EXCEPTION
  652. WHEN OTHERS THEN
  653. ROLLBACK;
  654. IF CUR_ORDERS%ISOPEN THEN
  655. CLOSE CUR_ORDERS; -- 关闭订单游标
  656. END IF;
  657. IF CUR_RATES%ISOPEN THEN
  658. CLOSE CUR_RATES; -- 关闭费用游标
  659. END IF;
  660. DBMS_OUTPUT.put_line('sqlerrm : ' ||sqlerrm);
  661. V_END_TIME := SYSDATE;
  662. end SP_AUTO_LEDGER_EXCUTE;