test
PROCEDURE PROC_WB_DISTRIBUTE_COMFIRM IS
--*************************************************************
-- Current Version:V7.5
-- AUTHOR : 664525
-- CREATED : 2016-12-17
-- PURPOSE :将确认表超过1个小时还未收到确认的，移到正式表
--*************************************************************
BEGIN
insert into TT_WB_EXPORT_WBEP_TEMP(
WAYBILL_EXPORT_ESB_ID,
WAYBILL_ID,
WAYBILL_NO,
INPUT_TYPE_CODE,
CREATED_TM,
LOCK_VERSION_NO,
OP_TYPE_CODE,
SENT_STATUS,
DEAL_IP,
THREAD_ID,
DEAL_COUNT,
DEAL_TM,
COMFIRM_COUNT
)
select A.Waybill_Export_Esb_Id,
A.WAYBILL_ID,
A.WAYBILL_NO,
A.INPUT_TYPE_CODE,
SYSDATE,
A.LOCK_VERSION_NO,
A.OP_TYPE_CODE,
'0',
NULL,
NULL,
0,
NULL,
A.COMFIRM_COUNT
FROM TT_WBEP_DISTRIBUTE_COMFIRM A
WHERE A.MODIFY_TM < SYSDATE - 1/ 24
AND A.COMFIRM_COUNT < 3
AND ROWNUM < 100000;
INSERT INTO TT_WB_EXPORT_WBEP SELECT * FROM TT_WB_EXPORT_WBEP_TEMP;
--删除本次拉取的COMFIRM数据
/*DELETE FROM TT_WBEP_DISTRIBUTE_COMFIRM T
WHERE EXISTS(SELECT 1 FROM TT_WB_EXPORT_WBEP_TEMP M WHERE M.WAYBILL_EXPORT_ESB_ID=T.WAYBILL_EXPORT_ESB_ID);*/
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
--写错误信息
INSERT INTO TL_PROC_ERR_LOG
(LOG_ID,
PROC_NAME,
TYPE_NO,
ERROR_MESS,
ERROR_NO,
CREATE_TM,
MODIFIED_TM,
MODIFIED_FLG)
VALUES
(SEQ_WEXPUTIL_BASE.NEXTVAL, ---ID都用这个SEQUENCE
L_PROCEDURE_NAME,
2,
L_ERROR_MESS,
1,
SYSDATE,
NULL,
0);
END PROC_WB_DISTRIBUTE_COMFIRM ;
===========================================================================
PROCEDURE PROC_CROSS_VALIDATE (V_PROCESS_NO NUMBER,V_PROCESS_COUNT NUMBER,V_ROW_NO NUMBER) IS
BEGIN
--先更改要进行交叉验证的运单的状态为1
UPDATE TT_CROSS_CHECK_TEMP SET STATUS = V_PROCESS_NO WHERE STATUS = 0 AND MOD(TO_NUMBER(WAYBILL_NO),V_PROCESS_COUNT)+1= V_PROCESS_NO AND ROWNUM < V_ROW_NO;
---去除重复的单号（多次红冲产生的多个交叉验证请求）
--EXECUTE IMMEDIATE 'CREATE TABLE SINGLE_WAYBILL_TEMP AS (SELECT DISTINCT * FROM TT_CROSS_CHECK_TEMP WHERE STATUS = 1)';
--DELETE FROM TT_CROSS_CHECK_TEMP WHERE STATUS = 1;
--EXECUTE IMMEDIATE 'INSERT INTO TT_CROSS_CHECK_TEMP SELECT * FROM SINGLE_WAYBILL_TEMP';
--EXECUTE IMMEDIATE 'DROP TABLE SINGLE_WAYBILL_TEMP';
---删除已存在的本次需要进行交叉验证的旧的验证记录明细（运单资料和代收货款）
DELETE FROM TT_CROSS_CHECK_DETL A
WHERE EXISTS (SELECT 1
FROM TT_CROSS_CHECK B, TT_CROSS_CHECK_TEMP C
WHERE B.WAYBILL_NO = C.WAYBILL_NO
AND B.CROSS_CHECK_ID = A.CROSS_CHECK_ID
AND C.BILL_TYPE = 0
AND C.Status = V_PROCESS_NO
AND B.CHECK_TYPE IN (1, 2));
---删除已存在的本次需要进行交叉验证的旧的验证主记录（运单资料和代收货款）
DELETE FROM TT_CROSS_CHECK B
WHERE EXISTS (SELECT 1
FROM TT_CROSS_CHECK_TEMP C
WHERE C.WAYBILL_NO = B.WAYBILL_NO
AND C.BILL_TYPE = 0
AND C.Status = V_PROCESS_NO)
AND B.CHECK_TYPE IN (1, 2);
--获取运单更改变更记录，获取相应运单的需验证资料进行验证。
--首先获取运单相关费用比较，更新交叉验证主表
--将有变动的运单资料生成临时运单交叉验证主记录(运费费用）
INSERT INTO TT_CROSS_CHECK
(CROSS_CHECK_ID,
WAYBILL_NO,
SRC_EMP_CODE,
DEST_EMP_CODE,
SRC_WEIGHT_QTY,
DEST_WEIGHT_QTY,
CHECK_TM,
PASSED_FLG,
LIST_DEST_ZONE_CODE,
DEST_ZONE_CODE,
SOURCE_ZONE_CODE,
SRC_INPUTER_EMP_CODE,
DEST_INPUTER_EMP_CODE,
CHECK_TYPE,
SRC_TB_ORDER_NO,
DEST_TB_ORDER_NO,
LIST_LONG,
LIST_WIDTH,
LIST_HIGH,
ACK_LONG,
ACK_WIDTH,
ACK_HIGH,
CARGO_TYPE_CODE,
LIMIT_TYPE_CODE,
SRC_VOLUME,
DEST_VOLUME,
LIST_AUTO_FEE_FLAG,
ACK_AUTO_FEE_FLAG,
SRC_PRODUCT_CODE
)
SELECT /*+ parallel(C,2)*/ GET_SEQ_EXP5_BASE(),
C.WAYBILL_NO,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.CONSIGNEE_EMP_CODE, 0)) AS SRC_EMP_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.DELIVER_EMP_CODE, 0)) AS DEST_EMP_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.METERAGE_WEIGHT_QTY, 0)) AS SRC_WEIGHT_QTY,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.METERAGE_WEIGHT_QTY, 0)) AS DEST_WEIGHT_QTY,
SYSDATE AS CHECK_TM,
(CASE
--ADD BY 160564 回单录单员为“WBEP-SYSTEM”,回单类型为：正常1,目的地字段前三位数与清单目的地不一致
WHEN MAX(DECODE(C.INPUT_TYPE_CODE,2,C.INPUTER_EMP_CODE,0))='WBEP-SYSTEM'
AND MAX(DECODE(C.INPUT_TYPE_CODE,2,C.ACKBILL_TYPE_CODE,0))='1'
AND substr(MAX(DECODE(C.INPUT_TYPE_CODE,1, C.DEST_ZONE_CODE,0)),0,3)
<>substr(MAX(DECODE(C.INPUT_TYPE_CODE,2,C.DEST_ZONE_CODE,0)),0,3)
THEN 2
--ADD BY 160564 V3.6 请回单订单号为'LC999999','WD999999','PPCOD999999'不过交叉
WHEN MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.tb_order_no, 0)) in ('LC999999','WD999999','PPCOD999999')
or MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.tb_order_no, 0)) in ('LC999999','WD999999','PPCOD999999')
THEN 2
WHEN MAX(DECODE(C.INPUT_TYPE_CODE,
1,
C.METERAGE_WEIGHT_QTY,
0)) = MAX(DECODE(C.INPUT_TYPE_CODE,
2,
C.METERAGE_WEIGHT_QTY,
0))
--ADD BY 160564 增加长宽高的交叉验证 V2.6 增加体积的交叉验证
AND MAX(DECODE(C.INPUT_TYPE_CODE,1, C.BILL_LONG,0)) = MAX(DECODE(C.INPUT_TYPE_CODE,2,C.BILL_LONG,0))
AND MAX(DECODE(C.INPUT_TYPE_CODE,1, C.BILL_WIDTH,0)) = MAX(DECODE(C.INPUT_TYPE_CODE,2,C.BILL_WIDTH,0))
AND MAX(DECODE(C.INPUT_TYPE_CODE,1, C.Bill_High,0)) = MAX(DECODE(C.INPUT_TYPE_CODE,2,C.Bill_High,0))
-- AND MAX(DECODE(C.INPUT_TYPE_CODE,1, C.VOLUME,0)) = MAX(DECODE(C.INPUT_TYPE_CODE,2,C.VOLUME,0))
THEN 1 ELSE 2 END) AS PASSED_FLG,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.DEST_ZONE_CODE, 0)) AS LIST_DEST_ZONE_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.DEST_ZONE_CODE, 0)) AS DEST_ZONE_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.SOURCE_ZONE_CODE, 0)) AS SOURCE_ZONE_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.INPUTER_EMP_CODE, 0)) AS SRC_INPUTER_EMP_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.INPUTER_EMP_CODE, 0)) AS DEST_INPUTER_EMP_CODE,
1,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.tb_order_no, 0)) AS SRC_TB_ORDER_NO,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.tb_order_no, 0)) AS DEST_TB_ORDER_NO,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.BILL_LONG, 0)) AS SRC_LONG,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.BILL_WIDTH, 0)) AS SRC_WIDTH,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.Bill_High, 0)) AS SRC_HIGHT,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.BILL_LONG, 0)) AS DEST_LONG,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.BILL_WIDTH, 0)) AS DEST_WIDTH,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.Bill_High, 0)) AS DEST_HIGHT,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.CARGO_TYPE_CODE, 0)) AS CARGO_TYPE_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.LIMIT_TYPE_CODE, 0)) AS LIMIT_TYPE_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.VOLUME, 0)) AS SRC_VOLUME,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.VOLUME, 0)) AS DEST_VOLUME,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.AUTO_FEE_FLAG, 0)) AS LIST_AUTO_FEE_FLAG,
MAX(DECODE(C.INPUT_TYPE_CODE, 2, C.AUTO_FEE_FLAG, 0)) AS ACK_AUTO_FEE_FLAG,
MAX(DECODE(C.INPUT_TYPE_CODE, 1, C.PRODUCT_CODE, 0)) AS SRC_PRODUCT_CODE
FROM (SELECT DISTINCT * FROM TT_CROSS_CHECK_TEMP WHERE Status = V_PROCESS_NO) B,
TT_WAYBILL C
WHERE B.WAYBILL_NO = C.WAYBILL_NO
AND C.INPUT_TYPE_CODE IN ('1', '2')
AND B.STATUS =V_PROCESS_NO
AND B.BILL_TYPE = 0 ---- 运单
GROUP BY C.WAYBILL_NO;
--将有变动的运单资料生成临时运单交叉验证主记录（代收货款和代收货款服务费）
INSERT INTO TT_CROSS_CHECK
(CROSS_CHECK_ID,
WAYBILL_NO,
SRC_EMP_CODE,
DEST_EMP_CODE,
SRC_WEIGHT_QTY,
DEST_WEIGHT_QTY,
CHECK_TM,
PASSED_FLG,
DEST_ZONE_CODE,
SOURCE_ZONE_CODE,
SRC_INPUTER_EMP_CODE,
DEST_INPUTER_EMP_CODE,
CHECK_TYPE,
SRC_TB_ORDER_NO,
DEST_TB_ORDER_NO)
SELECT GET_SEQ_EXP5_BASE(),
WAYBILL_NO,
SRC_EMP_CODE,
DEST_EMP_CODE,
SRC_WEIGHT_QTY,
DEST_WEIGHT_QTY,
CHECK_TM,
PASSED_FLG,
DEST_ZONE_CODE,
SOURCE_ZONE_CODE,
SRC_INPUTER_EMP_CODE,
DEST_INPUTER_EMP_CODE,
CHECK_TYPE,
SRC_TB_ORDER_NO,
DEST_TB_ORDER_NO
FROM (SELECT DISTINCT A.WAYBILL_NO,
A.SRC_EMP_CODE,
A.DEST_EMP_CODE,
A.SRC_WEIGHT_QTY,
A.DEST_WEIGHT_QTY,
A.CHECK_TM,
--代收货款主记录永远为1 JACK.QIU 2008-9-5
--A.PASSED_FLG,
1 AS PASSED_FLG,
A.DEST_ZONE_CODE,
A.SOURCE_ZONE_CODE,
A.SRC_INPUTER_EMP_CODE,
A.DEST_INPUTER_EMP_CODE,
2 AS CHECK_TYPE,
SRC_TB_ORDER_NO,
DEST_TB_ORDER_NO
FROM TT_CROSS_CHECK A, TT_WAYBILL_FEE B
WHERE A.WAYBILL_NO = B.WAYBILL_NO
AND B.FEE_TYPE_CODE IN ('4', '5')
AND A.PASSED_FLG >= 1);
--将有运单资料变动的进行交叉验证生成运单交叉验证明细记录（运单运费验证）
INSERT INTO TT_CROSS_CHECK_DETL
(DETL_ID,
SRC_CURRENCY_CODE,
DEST_CURRENCY_CODE,
FEE_TYPE_CODE,
SRC_PAYMENT_TYPE_CODE,
DEST_PAYMENT_TYPE_CODE,
SRC_FEE,
DEST_FEE,
CROSS_CHECK_ID,
SRC_GATHER_ZONE_CODE,
DEST_GATHER_ZONE_CODE,
SRC_PAYER_EMP_CODE,
DEST_PAYER_EMP_CODE,
SRC_PAYMENT_CHANGE_TYPE_CODE,
DEST_PAYMENT_CHANGE_TYPE_CODE,
PASSED_FLG,
CHECK_TM,
SRC_CUSTOMER_ACCT_CODE)--add by 160564
SELECT GET_SEQ_EXP5_BASE(),
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.CURRENCY_CODE, 0)) AS SRC_CURRENCY_CODE, --源单币种编码
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.CURRENCY_CODE, 0)) AS DEST_CURRENCY_CODE, --目的单币种编码
C.FEE_TYPE_CODE, --费用类型编码*********
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.PAYMENT_TYPE_CODE, 0)) AS SRC_PAYMENT_TYPE_CODE, --源单付款方式
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.PAYMENT_TYPE_CODE, 0)) AS DEST_PAYMENT_TYPE_CODE, --目的单付款方式
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) AS SRC_FEE, --源单费用
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AS DEST_FEE, --目的单费用
B.CROSS_CHECK_ID, --交叉验证ID*********
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.GATHER_ZONE_CODE, 0)) AS SRC_GATHER_ZONE_CODE, --清单收付款网点编码
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.GATHER_ZONE_CODE, 0)) AS DEST_GATHER_ZONE_CODE, --回单收付款网点编码
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.GATHER_EMP_CODE, 0)) AS DEST_PAYER_EMP_CODE, --交款人*********
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.GATHER_EMP_CODE, 0)) AS DEST_PAYER_EMP_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE,
'1',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) AS SRC_PAYMENT_CHANGE_TYPE_CODE, --清单付款变更方式
MAX(DECODE(C.INPUT_TYPE_CODE,
'2',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) AS DEST_PAYMENT_CHANGE_TYPE_CODE, --回单付款变更方式
(CASE
WHEN C.FEE_TYPE_CODE NOT IN ( '1','12','17','18') AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0))/* AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.CURRENCY_CODE, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.CURRENCY_CODE, 0))*/ THEN
1
WHEN C.FEE_TYPE_CODE IN('17','18') AND --清回单相等，但是费用少于1（不含）的清回单数值，交叉验证不通过
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) >= 1 THEN
1
WHEN C.FEE_TYPE_CODE ='12' AND --清回单相等，但是费用少于1且，不等于0.5，的清回单数值，交叉验证不通过
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AND
(MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) >= 1 or
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) = 0.5)
THEN
1
WHEN C.FEE_TYPE_CODE = '1' AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AND
--恢复主运费币种的交叉验证 V5.2 20120227 by dengyingxia
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.CURRENCY_CODE, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.CURRENCY_CODE, 0)) AND
MAX(DECODE(C.INPUT_TYPE_CODE,
'1',
C.PAYMENT_TYPE_CODE,
0)) || MAX(DECODE(C.INPUT_TYPE_CODE,
'2',
C.PAYMENT_TYPE_CODE,
0)) ||
MAX(DECODE(C.INPUT_TYPE_CODE,
'1',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) || MAX(DECODE(C.INPUT_TYPE_CODE,
'2',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) IN
('1100', '2200', '3311', '2302') THEN
1
ELSE
0
END) AS PASSED_FLG, --是否已验证通过0不通过，1通过
MAX(B.CHECK_TM),
MAX(DECODE(C.INPUT_TYPE_CODE, '1',C.CUSTOMER_ACCT_CODE, 0)) AS SRC_CUSTOMER_ACCT_CODE --清单月结账号
FROM TT_CROSS_CHECK B, TT_WAYBILL_FEE C
WHERE B.WAYBILL_NO = C.WAYBILL_NO
AND C.INPUT_TYPE_CODE IN ('1', '2')
AND C.FEE_TYPE_CODE IN ('1', '2', /*'3',*/ '7', '8', '9', '10', '11', '12','17','18','19',
'21','22','23','14','26','35','36','37','38','39',/*'41',*/'42','44','47',
'52','53','48','55','56','57','58','59','60','61','62','63','64','65','66','67',
'71','72','73','79','80','81','82','83','49','86','87','90','88')--增加便利店 modify by cc 2012-12-4,代收货款到付 modify by 160564 V2.6 add by 160564 V3.4 add by160564 V3.5
AND B.CHECK_TYPE = 1
AND B.PASSED_FLG >= 1
GROUP BY C.WAYBILL_NO, C.FEE_TYPE_CODE, B.CROSS_CHECK_ID;
--将有运单资料变动的进行交叉验证生成运单交叉验证明细记录
--比较香港偏远件(付款方式，偏远类型 验证)
-- update by lzl 20110816 增加对香港偏远件校验
INSERT INTO TT_CROSS_CHECK_DETL
(DETL_ID,
SRC_CURRENCY_CODE,
DEST_CURRENCY_CODE,
FEE_TYPE_CODE,
SRC_PAYMENT_TYPE_CODE,
DEST_PAYMENT_TYPE_CODE,
SRC_FEE,
DEST_FEE,
CROSS_CHECK_ID,
SRC_GATHER_ZONE_CODE,
DEST_GATHER_ZONE_CODE,
SRC_PAYER_EMP_CODE,
DEST_PAYER_EMP_CODE,
SRC_PAYMENT_CHANGE_TYPE_CODE,
DEST_PAYMENT_CHANGE_TYPE_CODE,
PASSED_FLG,
CHECK_TM)
SELECT GET_SEQ_EXP5_BASE(),
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.CURRENCY_CODE, 0)) AS SRC_CURRENCY_CODE, --源单币种编码
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.CURRENCY_CODE, 0)) AS DEST_CURRENCY_CODE, --目的单币种编码
C.FEE_TYPE_CODE, --费用类型编码*********
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.PAYMENT_TYPE_CODE, 0)) AS SRC_PAYMENT_TYPE_CODE, --源单付款方式
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.PAYMENT_TYPE_CODE, 0)) AS DEST_PAYMENT_TYPE_CODE, --目的单付款方式
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) AS SRC_FEE, --源单费用
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AS DEST_FEE, --目的单费用
B.CROSS_CHECK_ID, --交叉验证ID*********
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.GATHER_ZONE_CODE, 0)) AS SRC_GATHER_ZONE_CODE, --清单收付款网点编码
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.GATHER_ZONE_CODE, 0)) AS DEST_GATHER_ZONE_CODE, --回单收付款网点编码
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.GATHER_EMP_CODE, 0)) AS DEST_PAYER_EMP_CODE, --交款人*********
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.GATHER_EMP_CODE, 0)) AS DEST_PAYER_EMP_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE,
'1',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) AS SRC_PAYMENT_CHANGE_TYPE_CODE, --清单付款变更方式
MAX(DECODE(C.INPUT_TYPE_CODE,
'2',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) AS DEST_PAYMENT_CHANGE_TYPE_CODE, --回单付款变更方式
(CASE
WHEN C.FEE_TYPE_CODE in ('13','34','40','43','74') AND (
---如果清单香港偏远付款方式为到付且回单香港偏远付款方式为到付
--如果清单香港偏远付款方式为寄付，且回单香港偏远付款方式为寄付
--增加对偏远类型校验判断，确认是否一致
(
MAX(DECODE(C.INPUT_TYPE_CODE,'1',S.ATTRIBUTE1,0))<>
MAX(DECODE(C.INPUT_TYPE_CODE,'2',S.ATTRIBUTE1,0))
AND ( (MAX(DECODE(C.INPUT_TYPE_CODE,'1',C.PAYMENT_TYPE_CODE,0))=2
AND MAX(DECODE(C.INPUT_TYPE_CODE,'2',C.PAYMENT_TYPE_CODE,0))=2)
OR ( MAX(DECODE(C.INPUT_TYPE_CODE,'1',C.PAYMENT_TYPE_CODE,0))=1
AND MAX(DECODE(C.INPUT_TYPE_CODE,'2',C.PAYMENT_TYPE_CODE,0))=1 )
)
)
OR (--验证三种情况:1 清单偏远到付，回单偏远寄付
--2. 清单偏远无，回单偏远寄付
--3.清单偏远到付，回单偏远无
(
MAX(DECODE(C.INPUT_TYPE_CODE,'1',C.PAYMENT_TYPE_CODE,0))= 2
AND MAX(DECODE(C.INPUT_TYPE_CODE,'2',C.PAYMENT_TYPE_CODE,0)) = 1
)
OR (
MAX(DECODE(C.INPUT_TYPE_CODE,'1',C.PAYMENT_TYPE_CODE,0))= 0
AND MAX(DECODE(C.INPUT_TYPE_CODE,'2',C.PAYMENT_TYPE_CODE,0)) = 1
)
OR (
MAX(DECODE(C.INPUT_TYPE_CODE,'1',C.PAYMENT_TYPE_CODE,0))= 2
AND MAX(DECODE(C.INPUT_TYPE_CODE,'2',C.PAYMENT_TYPE_CODE,0)) = 0
)
)
)
THEN 0
ELSE
1
END) AS PASSED_FLG, --是否已验证通过0不通过，1通过
MAX(B.CHECK_TM)
FROM TT_CROSS_CHECK B, TT_WAYBILL_FEE C, TT_WAYBILL_SERVICE S
WHERE B.WAYBILL_NO = C.WAYBILL_NO
AND B.WAYBILL_NO = S.WAYBILL_NO
AND C.SERVICE_ID = S.SERVICE_ID
AND C.INPUT_TYPE_CODE IN ('1', '2')
AND C.FEE_TYPE_CODE in ('13','34','40','43','74')--add by160564 V2.6增加存仓费交叉验证
AND B.CHECK_TYPE = 1
AND B.PASSED_FLG >= 1
GROUP BY C.WAYBILL_NO, C.FEE_TYPE_CODE, B.CROSS_CHECK_ID;
--将有运单资料变动的进行交叉验证生成运单交叉验证明细记录
--比较保价(保价费和声明价值验证)
--增加台湾保价 modify by cc 2012-12-4
-- update by dengyingxia 增加对保价声明价值校验
INSERT INTO TT_CROSS_CHECK_DETL
(DETL_ID,
SRC_CURRENCY_CODE,
DEST_CURRENCY_CODE,
FEE_TYPE_CODE,
SRC_PAYMENT_TYPE_CODE,
DEST_PAYMENT_TYPE_CODE,
SRC_FEE,
DEST_FEE,
CROSS_CHECK_ID,
SRC_GATHER_ZONE_CODE,
DEST_GATHER_ZONE_CODE,
SRC_PRO_CONS_VALUE,
DEST_PRO_CONS_VALUE,
SRC_PAYER_EMP_CODE,
DEST_PAYER_EMP_CODE,
SRC_PAYMENT_CHANGE_TYPE_CODE,
DEST_PAYMENT_CHANGE_TYPE_CODE,
PASSED_FLG,
CHECK_TM)
SELECT GET_SEQ_EXP5_BASE(),
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.CURRENCY_CODE, 0)) AS SRC_CURRENCY_CODE, --源单币种编码
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.CURRENCY_CODE, 0)) AS DEST_CURRENCY_CODE, --目的单币种编码
C.FEE_TYPE_CODE, --费用类型编码*********
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.PAYMENT_TYPE_CODE, 0)) AS SRC_PAYMENT_TYPE_CODE, --源单付款方式
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.PAYMENT_TYPE_CODE, 0)) AS DEST_PAYMENT_TYPE_CODE, --目的单付款方式
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) AS SRC_FEE, --源单费用
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AS DEST_FEE, --目的单费用
B.CROSS_CHECK_ID, --交叉验证ID*********
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.GATHER_ZONE_CODE, 0)) AS SRC_GATHER_ZONE_CODE, --清单收付款网点编码
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.GATHER_ZONE_CODE, 0)) AS DEST_GATHER_ZONE_CODE, --回单收付款网点编码
MAX(DECODE(C.INPUT_TYPE_CODE, '1', S.ATTRIBUTE1, 0)) AS SRC_PRO_CONS_VALUE, --清单保价声明价值
MAX(DECODE(C.INPUT_TYPE_CODE, '2', S.ATTRIBUTE1, 0)) AS DEST_PRO_CONS_VALUE, --回单保价声明价值
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.GATHER_EMP_CODE, 0)) AS DEST_PAYER_EMP_CODE, --交款人*********
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.GATHER_EMP_CODE, 0)) AS DEST_PAYER_EMP_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.PAYMENT_CHANGE_TYPE_CODE, 0)) AS SRC_PAYMENT_CHANGE_TYPE_CODE, --清单付款变更方式
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.PAYMENT_CHANGE_TYPE_CODE, 0)) AS DEST_PAYMENT_CHANGE_TYPE_CODE, --回单付款变更方式
(CASE
WHEN C.FEE_TYPE_CODE IN ('27') AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', S.ATTRIBUTE1, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', S.ATTRIBUTE1, 0)) THEN
1
WHEN C.FEE_TYPE_CODE IN ('3') AND --MODIFY BY 160564 相等但声明价值少于1（不含），交叉验证不通过
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', S.ATTRIBUTE1, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', S.ATTRIBUTE1, 0)) AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', S.ATTRIBUTE1, 0)) >= 1 THEN
1
ELSE
0
end) AS PASSED_FLG, --是否已验证通过0不通过，1通过
MAX(B.CHECK_TM)
FROM TT_CROSS_CHECK B, TT_WAYBILL_FEE C, TT_WAYBILL_SERVICE S
WHERE B.WAYBILL_NO = C.WAYBILL_NO
AND B.WAYBILL_NO = S.WAYBILL_NO
AND C.SERVICE_ID = S.SERVICE_ID
AND C.INPUT_TYPE_CODE IN ('1', '2')
AND C.FEE_TYPE_CODE IN ('3','27')
AND B.CHECK_TYPE = 1
AND B.PASSED_FLG >= 1
GROUP BY C.WAYBILL_NO, C.FEE_TYPE_CODE, B.CROSS_CHECK_ID;
--将有运单资料变动的进行交叉验证生成运单交叉验证明细记录（代收货款及代收货款服务费验证）
INSERT INTO TT_CROSS_CHECK_DETL
(DETL_ID,
SRC_CURRENCY_CODE,
DEST_CURRENCY_CODE,
FEE_TYPE_CODE,
SRC_PAYMENT_TYPE_CODE,
DEST_PAYMENT_TYPE_CODE,
SRC_FEE,
DEST_FEE,
CROSS_CHECK_ID,
SRC_GATHER_ZONE_CODE,
DEST_GATHER_ZONE_CODE,
SRC_PAYER_EMP_CODE,
DEST_PAYER_EMP_CODE,
SRC_PAYMENT_CHANGE_TYPE_CODE,
DEST_PAYMENT_CHANGE_TYPE_CODE,
PASSED_FLG,
CHECK_TM,
SRC_CUSTOMER_ACCT_CODE)--增加代收货款的月结账号
SELECT GET_SEQ_EXP5_BASE(),
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.CURRENCY_CODE, 0)) AS SRC_CURRENCY_CODE, --源单币种编码
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.CURRENCY_CODE, 0)) AS DEST_CURRENCY_CODE, --目的单币种编码
C.FEE_TYPE_CODE, --费用类型编码*********
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.PAYMENT_TYPE_CODE, 0)) AS SRC_PAYMENT_TYPE_CODE, --源单付款方式
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.PAYMENT_TYPE_CODE, 0)) AS DEST_PAYMENT_TYPE_CODE, --目的单付款方式
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) AS SRC_FEE, --源单费用
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AS DEST_FEE, --目的单费用
B.CROSS_CHECK_ID, --交叉验证ID*********
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.GATHER_ZONE_CODE, 0)) AS SRC_GATHER_ZONE_CODE, --清单收付款网点编码
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.GATHER_ZONE_CODE, 0)) AS DEST_GATHER_ZONE_CODE, --回单收付款网点编码
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.GATHER_EMP_CODE, 0)) AS DEST_PAYER_EMP_CODE, --交款人*********
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.GATHER_EMP_CODE, 0)) AS DEST_PAYER_EMP_CODE,
MAX(DECODE(C.INPUT_TYPE_CODE,
'1',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) AS SRC_PAYMENT_CHANGE_TYPE_CODE, --清单付款变更方式
MAX(DECODE(C.INPUT_TYPE_CODE,
'2',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) AS DEST_PAYMENT_CHANGE_TYPE_CODE, --回单付款变更方式
(CASE
--add by 160564 V3.6 代收货款的月结账号为“8888888888”，校验不通过
WHEN C.FEE_TYPE_CODE = '5' AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.CUSTOMER_ACCT_CODE, 0))='8888888888' THEN
0
WHEN C.FEE_TYPE_CODE <> '1' AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) /*AND
MAX(DECODE(C.INPUT_TYPE_CODE,
'1',
--C.CURRENCY_CODE
--代收货款时如果金额为0则币种也返回0 Jack.Qiu 2008.10.24
DECODE(C.FEE_AMT, 0, '0', C.CURRENCY_CODE),
0)) =
MAX(DECODE(C.INPUT_TYPE_CODE,
'2',
--C.CURRENCY_CODE
--代收货款时如果金额为0则币种也返回0 Jack.Qiu 2008.10.24
DECODE(C.FEE_AMT, 0, '0', C.CURRENCY_CODE),
0))*/ THEN
1
WHEN C.FEE_TYPE_CODE = '1' AND
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.FEE_AMT, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.FEE_AMT, 0)) AND
--恢复主运费币种的交叉验证 V5.2 20120227 by dengyingxia --这一条语句未执行，因为FEE_TYPE_CODE永远等于5
MAX(DECODE(C.INPUT_TYPE_CODE, '1', C.CURRENCY_CODE, 0)) =
MAX(DECODE(C.INPUT_TYPE_CODE, '2', C.CURRENCY_CODE, 0)) AND
MAX(DECODE(C.INPUT_TYPE_CODE,
'1',
C.PAYMENT_TYPE_CODE,
0)) || MAX(DECODE(C.INPUT_TYPE_CODE,
'2',
C.PAYMENT_TYPE_CODE,
0)) ||
MAX(DECODE(C.INPUT_TYPE_CODE,
'1',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) || MAX(DECODE(C.INPUT_TYPE_CODE,
'2',
C.PAYMENT_CHANGE_TYPE_CODE,
0)) IN
('1100', '2200', '3311', '2302') THEN
1
ELSE
0
END) AS PASSED_FLG, --是否已验证通过0不通过，1通过
MAX(B.CHECK_TM),
MAX(DECODE(C.INPUT_TYPE_CODE, '1',C.CUSTOMER_ACCT_CODE, 0)) AS SRC_CUSTOMER_ACCT_CODE --清单代收货款月结账号
FROM TT_CROSS_CHECK B, TT_WAYBILL_FEE C
WHERE B.WAYBILL_NO = C.WAYBILL_NO
AND C.INPUT_TYPE_CODE IN ('1', '2')
AND C.FEE_TYPE_CODE IN (/*'4',*/ '5')
AND B.CHECK_TYPE = 2
AND B.PASSED_FLG >= 1
GROUP BY C.WAYBILL_NO, C.FEE_TYPE_CODE, B.CROSS_CHECK_ID;
--=======处理税单和海关税单的验证======================================================================
---删除已存在的本次需要进行交叉验证的旧的验证记录明细（税单）
DELETE FROM TT_CROSS_CHECK_DETL A
WHERE EXISTS (SELECT 1
FROM TT_CROSS_CHECK B, TT_CROSS_CHECK_TEMP C
WHERE B.WAYBILL_NO = C.WAYBILL_NO
AND B.CROSS_CHECK_ID = A.CROSS_CHECK_ID
AND C.BILL_TYPE = 1
AND C.status = V_PROCESS_NO
AND B.CHECK_TYPE = 3);
---删除已存在的本次需要进行交叉验证的旧的验证主记录（税单）
DELETE FROM TT_CROSS_CHECK B
WHERE EXISTS (SELECT 1
FROM TT_CROSS_CHECK_TEMP C
WHERE C.WAYBILL_NO = B.WAYBILL_NO
AND C.status = V_PROCESS_NO
AND C.BILL_TYPE = 1)
AND B.CHECK_TYPE = 3;
--添加税单比较的运单交叉验证主记录
INSERT INTO TT_CROSS_CHECK
(CROSS_CHECK_ID,
WAYBILL_NO,
SRC_EMP_CODE,
DEST_EMP_CODE,
SRC_WEIGHT_QTY,
DEST_WEIGHT_QTY,
CHECK_TM,
PASSED_FLG,
DEST_ZONE_CODE,
SOURCE_ZONE_CODE,
SRC_INPUTER_EMP_CODE,
DEST_INPUTER_EMP_CODE,
CHECK_TYPE)
SELECT GET_SEQ_EXP5_BASE(),
C.WAYBILL_NO,
NULL,
NULL,
0,
0,
SYSDATE,
1,
A.GATHER_ZONE_CODE,
A.GATHER_ZONE_CODE,
NULL,
NULL,
3
FROM (SELECT DISTINCT * FROM TT_CROSS_CHECK_TEMP WHERE STATUS =V_PROCESS_NO) C,
TT_WAYBILL_TAXBILL A,
TT_IMPORT_TAXBILL B
WHERE A.TAXBILL_ID = B.TAXBILL_ID
AND A.WAYBILL_NO = C.WAYBILL_NO
AND B.WAYBILL_NO = C.WAYBILL_NO
AND C.STATUS = V_PROCESS_NO
AND C.BILL_TYPE = 1;
--添加税单交叉验证的明细记录
INSERT INTO TT_CROSS_CHECK_DETL
(DETL_ID,
SRC_CURRENCY_CODE,
DEST_CURRENCY_CODE,
FEE_TYPE_CODE,
SRC_PAYMENT_TYPE_CODE,
DEST_PAYMENT_TYPE_CODE,
SRC_FEE,
DEST_FEE,
CROSS_CHECK_ID,
SRC_GATHER_ZONE_CODE,
DEST_GATHER_ZONE_CODE,
SRC_PAYER_EMP_CODE,
DEST_PAYER_EMP_CODE,
SRC_PAYMENT_CHANGE_TYPE_CODE,
DEST_PAYMENT_CHANGE_TYPE_CODE,
PASSED_FLG,
CHECK_TM)
SELECT GET_SEQ_EXP5_BASE(),
B.CURRENCY_CODE AS SRC_CURRENCY_CODE, --源单币种编码
A.CURRENCY_CODE AS DEST_CURRENCY_CODE, --目的单币种编码
'X', --费用类型编码*********
NULL, --源单付款方式
NULL, --目的单付款方式
B.TOTAL_TAXFEE_AMT AS SRC_FEE, --源单费用
A.TAX_FEE_QTY AS DEST_FEE, --目的单费用
C.CROSS_CHECK_ID, --交叉验证ID*********
NULL, --清单收付款网点编码
NULL, --回单收付款网点编码
A.GATHER_EMP_CODE, --交款人*********
A.GATHER_EMP_CODE,
NULL, --清单付款变更方式
NULL, --回单付款变更方式
(CASE
WHEN B.TOTAL_TAXFEE_AMT = A.TAX_FEE_QTY
--税单不需要校验币种 JACK.QIU 2008.09.05
--AND B.CURRENCY_CODE = A.CURRENCY_CODE
THEN
1
ELSE
0
END) AS PASSED_FLG, --是否已验证通过0不通过，1通过
C.CHECK_TM
FROM TT_CROSS_CHECK C, TT_WAYBILL_TAXBILL A, TT_IMPORT_TAXBILL B
WHERE A.TAXBILL_ID = B.TAXBILL_ID
AND A.WAYBILL_NO = C.WAYBILL_NO
AND B.WAYBILL_NO = C.WAYBILL_NO
AND C.CHECK_TYPE = 3
AND C.PASSED_FLG = 1;
--更新本次校验的主表的校验是否通过标记
UPDATE TT_CROSS_CHECK B SET B.PASSED_FLG = 0 WHERE B.PASSED_FLG = 2;
UPDATE TT_CROSS_CHECK A
SET A.PASSED_FLG = 0
WHERE EXISTS (SELECT 1
FROM TT_CROSS_CHECK_DETL B
WHERE A.CROSS_CHECK_ID = B.CROSS_CHECK_ID
AND B.PASSED_FLG = 0)
AND A.PASSED_FLG = 1;
--//TODO 删除交叉验证通过的记录
DELETE FROM TT_CROSS_CHECK_DETL A
WHERE EXISTS (SELECT 1
FROM TT_CROSS_CHECK B
WHERE B.CROSS_CHECK_ID = A.CROSS_CHECK_ID
AND B.PASSED_FLG = 1);
DELETE FROM TT_CROSS_CHECK B WHERE B.PASSED_FLG = 1;
DELETE FROM TT_CROSS_CHECK_TEMP WHERE STATUS = V_PROCESS_NO;
COMMIT;
RETURN;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
--获得ORACLE错误号
L_ERROR_MESS := SQLERRM;
L_PROCEDURE_NAME := 'PROC_CROSS_VALIDATE';
--写错误信息
INSERT INTO TL_PROC_ERR_LOG
(LOG_ID,
PROC_NAME,
TYPE_NO,
ERROR_MESS,
ERROR_NO,
CREATE_TM,
MODIFIED_TM,
MODIFIED_FLG)
VALUES
(SEQ_BILLING_BASE.NEXTVAL, ---ID都用这个SEQUENCE
L_PROCEDURE_NAME,
2,
L_ERROR_MESS,
1,
SYSDATE,
NULL,
0);
COMMIT;
END PROC_CROSS_VALIDATE;
======================================
-- Create table
create table TT_INTERNATIONAL_WAYBILL
(
WAYBILL_ID VARCHAR2(32) not null,
WAYBILL_NO VARCHAR2(30) not null,
SOURCE_ZONE_CODE VARCHAR2(30),
DEST_ZONE_CODE VARCHAR2(30),
ONESELF_PICKUP_FLG INTEGER,
CONSIGNOR_COMP_NAME VARCHAR2(100),
CONSIGNOR_ADDR VARCHAR2(200),
CONSIGNOR_PHONE VARCHAR2(20),
CONSIGNOR_CONT_NAME VARCHAR2(100),
ADDRESSEE_COMP_NAME VARCHAR2(100),
ADDRESSEE_ADDR VARCHAR2(200),
ADDRESSEE_PHONE VARCHAR2(20),
ADDRESSEE_CONT_NAME VARCHAR2(100),
ADDRESSEE_MOBILE VARCHAR2(20),
METERAGE_WEIGHT_QTY NUMBER(17,3) default 0,
REAL_WEIGHT_QTY NUMBER(17,3) default 0,
QUANTITY NUMBER(16,2),
FREE_PARCEL_FLG INTEGER default 0,
TRUST_PARCEL_FLG INTEGER default 0,
PRE_CUSTOMS_DT DATE,
CONSIGNEE_EMP_CODE VARCHAR2(20),
CONSIGNED_TM DATE,
WAYBILL_REMK VARCHAR2(600),
CUSTOMS_BATCHS VARCHAR2(20),
CUSTOMS_TYPE_CODE VARCHAR2(30),
CARGO_TYPE_CODE VARCHAR2(30),
LIMIT_TYPE_CODE VARCHAR2(30),
DISTANCE_TYPE_CODE VARCHAR2(30),
EXPRESS_TYPE_CODE VARCHAR2(30),
NEED_SIGNED_BACK_FLG INTEGER,
SIGNED_BACK_WAYBILL_NO VARCHAR2(30),
HAS_SERVICE_PROD_FLG VARCHAR2(500),
CONS_VALUE NUMBER(16,2),
SOURCEAREA VARCHAR2(50),
CONSIGNOR_POSTAL_CODE VARCHAR2(20),
ADDRESSEE_POSTAL_CODE VARCHAR2(20),
CONS_VALUE_CURRENCY_CODE VARCHAR2(30),
CONSIGNOR_TAX_NO VARCHAR2(30),
CONSIGNOR_MOBILE VARCHAR2(20),
ADDRESSEE_TAX_NUM VARCHAR2(30),
BEGIN_DATE DATE,
CONS_NAME VARCHAR2(500),
QTY_UNIT VARCHAR2(100),
CONSIGNED_TIME DATE,
CUSTOMER_ACCT_CODE VARCHAR2(64),
FEE_AMT NUMBER(16,2),
PAY_AREA VARCHAR2(30),
INPUTED_ZONE_CODE VARCHAR2(30),
OTHER_PAY NUMBER(16,2),
PAYMENT_TYPE_CODE VARCHAR2(30),
ACCOUNT_TYPE VARCHAR2(30),
CREATE_TM DATE not null
)
tablespace WEXP
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64
next 1
minextents 1
maxextents unlimited
);
-- Add comments to the table
comment on table TT_INTERNATIONAL_WAYBILL
is '国际件运单明细报表';
-- Add comments to the columns
comment on column TT_INTERNATIONAL_WAYBILL.WAYBILL_ID
is '运单ID';
comment on column TT_INTERNATIONAL_WAYBILL.WAYBILL_NO
is '运单号';
comment on column TT_INTERNATIONAL_WAYBILL.SOURCE_ZONE_CODE
is '原寄地';
comment on column TT_INTERNATIONAL_WAYBILL.DEST_ZONE_CODE
is '目的地';
comment on column TT_INTERNATIONAL_WAYBILL.ONESELF_PICKUP_FLG
is '是否自取件：1：是，0：否';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNOR_COMP_NAME
is '寄件公司';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNOR_ADDR
is '寄件地址';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNOR_PHONE
is '寄件电话';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNOR_CONT_NAME
is '寄件联系人';
comment on column TT_INTERNATIONAL_WAYBILL.ADDRESSEE_COMP_NAME
is '收件公司';
comment on column TT_INTERNATIONAL_WAYBILL.ADDRESSEE_ADDR
is '收件地址';
comment on column TT_INTERNATIONAL_WAYBILL.ADDRESSEE_PHONE
is '收件电话';
comment on column TT_INTERNATIONAL_WAYBILL.ADDRESSEE_CONT_NAME
is '收件联系人';
comment on column TT_INTERNATIONAL_WAYBILL.ADDRESSEE_MOBILE
is '收件电话';
comment on column TT_INTERNATIONAL_WAYBILL.METERAGE_WEIGHT_QTY
is '计费重量';
comment on column TT_INTERNATIONAL_WAYBILL.REAL_WEIGHT_QTY
is '实际重量';
comment on column TT_INTERNATIONAL_WAYBILL.QUANTITY
is '件数';
comment on column TT_INTERNATIONAL_WAYBILL.FREE_PARCEL_FLG
is '免费件';
comment on column TT_INTERNATIONAL_WAYBILL.TRUST_PARCEL_FLG
is '是否委托件：1：是，0：否';
comment on column TT_INTERNATIONAL_WAYBILL.PRE_CUSTOMS_DT
is '预报关日期';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNEE_EMP_CODE
is '收件员';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNED_TM
is '寄件日期';
comment on column TT_INTERNATIONAL_WAYBILL.WAYBILL_REMK
is '备注';
comment on column TT_INTERNATIONAL_WAYBILL.CUSTOMS_BATCHS
is '报关批次';
comment on column TT_INTERNATIONAL_WAYBILL.CUSTOMS_TYPE_CODE
is '报关类型';
comment on column TT_INTERNATIONAL_WAYBILL.CARGO_TYPE_CODE
is '快件内容';
comment on column TT_INTERNATIONAL_WAYBILL.LIMIT_TYPE_CODE
is '时效类型';
comment on column TT_INTERNATIONAL_WAYBILL.DISTANCE_TYPE_CODE
is '区域类型';
comment on column TT_INTERNATIONAL_WAYBILL.EXPRESS_TYPE_CODE
is '业务类型';
comment on column TT_INTERNATIONAL_WAYBILL.NEED_SIGNED_BACK_FLG
is '是否签回单：1：是 0：否';
comment on column TT_INTERNATIONAL_WAYBILL.SIGNED_BACK_WAYBILL_NO
is '签回单单号';
comment on column TT_INTERNATIONAL_WAYBILL.HAS_SERVICE_PROD_FLG
is '增值产品ID';
comment on column TT_INTERNATIONAL_WAYBILL.CONS_VALUE
is '声明价值';
comment on column TT_INTERNATIONAL_WAYBILL.SOURCEAREA
is '原产地';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNOR_POSTAL_CODE
is '寄方邮政编码';
comment on column TT_INTERNATIONAL_WAYBILL.ADDRESSEE_POSTAL_CODE
is '收方邮政编码';
comment on column TT_INTERNATIONAL_WAYBILL.CONS_VALUE_CURRENCY_CODE
is '声明价值币种';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNOR_TAX_NO
is '寄件人税号';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNOR_MOBILE
is '寄件手机';
comment on column TT_INTERNATIONAL_WAYBILL.ADDRESSEE_TAX_NUM
is '收件人税号';
comment on column TT_INTERNATIONAL_WAYBILL.BEGIN_DATE
is '录单时间';
comment on column TT_INTERNATIONAL_WAYBILL.CONS_NAME
is '品名';
comment on column TT_INTERNATIONAL_WAYBILL.QTY_UNIT
is '数量/单位';
comment on column TT_INTERNATIONAL_WAYBILL.CONSIGNED_TIME
is '寄件时间';
comment on column TT_INTERNATIONAL_WAYBILL.CUSTOMER_ACCT_CODE
is '月结账号';
comment on column TT_INTERNATIONAL_WAYBILL.FEE_AMT
is '运费';
comment on column TT_INTERNATIONAL_WAYBILL.PAY_AREA
is '付款地区';
comment on column TT_INTERNATIONAL_WAYBILL.INPUTED_ZONE_CODE
is '录入区域';
comment on column TT_INTERNATIONAL_WAYBILL.OTHER_PAY
is '其他费用';
comment on column TT_INTERNATIONAL_WAYBILL.PAYMENT_TYPE_CODE
is '付款方式';
comment on column TT_INTERNATIONAL_WAYBILL.ACCOUNT_TYPE
is '结算方式';
comment on column TT_INTERNATIONAL_WAYBILL.CREATE_TM
is '创建时间';
-- Create/Recreate primary, unique and foreign key constraints
alter table TT_INTERNATIONAL_WAYBILL
add constraint PK_TT_INTERNATIONAL_WAYBILL_L1 primary key (WAYBILL_ID, CREATE_TM)
using index
tablespace WEXP
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
-- Create/Recreate indexes
create index IDX_INTERNATIONAL_BEGIN_DATE on TT_INTERNATIONAL_WAYBILL (BEGIN_DATE)
tablespace WEXP
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_INTERNATIONAL_CUSTOMS on TT_INTERNATIONAL_WAYBILL (CUSTOMS_BATCHS, PRE_CUSTOMS_DT, SOURCE_ZONE_CODE)
tablespace WEXP
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
create index IDX_INTERNATIONAL_SIGNED_BACK on TT_INTERNATIONAL_WAYBILL (SIGNED_BACK_WAYBILL_NO)
tablespace WEXP
pctfree 10
initrans 2
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
