3,499
社区成员
发帖
与我相关
我的任务
分享急急急小白求大佬帮忙看下存储过程怎么优化
CREATE OR REPLACE PACKAGE body ONE_YQLOAD_EXP_VALID_PKG IS
v_blStatus char(1);
v_orgId varchar2(100);
v_feeder varchar2(10);
--执行验证的方法：one驳船公司导入excel
PROCEDURE valid_method(blStatus varchar2,
orgId varchar2,
feeder varchar2) is
begin
/*赋值查询是进口约桥：还是出口约桥*/
v_blStatus := blStatus;
v_orgId := orgId;
v_feeder := feeder;
YQLOAD_COMMON_VALID_PKG.split_SPCI_CNT_SIZE_TYPE; -- /*分割装箱类型/大小*/
/*代码转换*/
YQLOAD_COMMON_VALID_PKG.valid_CNT_OPERATOR(v_orgId,'N'); --箱主没有维护
YQLOAD_COMMON_VALID_PKG.valid_ct_accounts(v_orgId,'N'); --供应商
YQLOAD_COMMON_VALID_PKG.valid_ct_cctm('N'); --箱型尺码不存在
YQLOAD_COMMON_VALID_PKG.valid_transfer_coDe('N'); /*中转类型判断*/
/*用于one校验；Begin*/
YQLOAD_COMMON_VALID_PKG.valid_exp_so(v_blStatus, v_orgId,'N'); --判断so是否存在
--页面大船装货港与大船船名航次的码头不一致：one用到。
/*YQLOAD_COMMON_VALID_PKG.valid_exp_berth_code(v_blStatus, v_orgId, 'N');*/
YQLOAD_COMMON_VALID_PKG.judge_exist_schedule; --相当于锁
If v_feeder is not null Then
YQLOAD_COMMON_VALID_PKG.valid_exp_so_feeder(v_blStatus,
v_orgId,
v_feeder,'N'); /*判断这个箱是不是这个CCA的*/
End If;
YQLOAD_COMMON_VALID_PKG.judge_exist_schedule; --相当于锁
/*导入校验*/
--如果船期没找到，以下校验就不需要了
--valid_imp_schedule;
YQLOAD_COMMON_VALID_PKG.VALID_DISCHARGE_PORT_SAME(v_blStatus,
v_orgId,
'Y'); --one卸货港与配置管理一致需要进行提示。
YQLOAD_COMMON_VALID_PKG.VALID_DESTINAL_PORT_SAME(v_blStatus,
v_orgId,
'Y'); --one目的港与配置管理一致需要进行提示。
YQLOAD_COMMON_VALID_PKG.VALID_VGM_HEAVY(v_blStatus, v_orgId, 'Y'); --是否VGM超过了30480kgs，进行提示
YQLOAD_COMMON_VALID_PKG.VALID_DRY_RF(v_blStatus, v_orgId, 'Y'); --\*是否是干冻柜，如果是则进行备注提示 ，允许导入*\
YQLOAD_COMMON_VALID_PKG.valid_exp_cnt_num_lt(v_blStatus, v_orgId, 'Y'); --集装箱数量小于订舱数量：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_spci_over(v_blStatus, v_orgId, 'N'); --/*超限*/：允许
-- YQLOAD_COMMON_VALID_PKG.valid_exp_cargo_name(v_blStatus, v_orgId, 'N'); -- /*危险品货物必填*/允许
YQLOAD_COMMON_VALID_PKG.valid_exp_so_vessel_voyage(v_blStatus, v_orgId,'N'); ----so号对应的船名航次跟excel不对应:
--YQLOAD_COMMON_VALID_PKG.valid_exp_special_port_same(v_blStatus, v_orgId, 'N'); --/*S/O上的港口信息（卸货港、目的港），属于特殊港口，请认真核对*/：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_block_code_same(v_blStatus,
v_orgId,
'N'); --BLOCK CODE跟系统不一致
YQLOAD_COMMON_VALID_PKG.valid_spgi_temperature_same(v_blStatus,
v_orgId,
'N'); --冻柜设定温度与系统不一致
YQLOAD_COMMON_VALID_PKG.valid_exp_spci_danger_same(v_blStatus,
v_orgId,
'N'); --危险品信息（IMCO/UNDG）与系统不一致
YQLOAD_COMMON_VALID_PKG.valid_exp_handling(v_blStatus, v_orgId, 'N'); --\*特殊配载填写不一致*\
YQLOAD_COMMON_VALID_PKG.valid_exp_port(v_blStatus, v_orgId,'N'); --/*S/O上的港口信息（卸货港、目的港），与系统港口不一致*/：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_Load_Area_Code(v_blStatus,
v_orgId,
'N'); --one大船装货码头与勾选装货码头不一致
YQLOAD_COMMON_VALID_PKG.valid_exp_PRE_DISC_AREA_CODE(v_blStatus,
v_orgId,
'N'); --one系统勾选大船装货码头与系统码头不一致
YQLOAD_COMMON_VALID_PKG.valid_exp_soAndCntNo(v_blStatus, v_orgId, 'N'); --已经驳船卸船：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_cut_time(v_blStatus, v_orgId,'N'); --/*已超过截重时间*/
YQLOAD_COMMON_VALID_PKG.valid_exp_excel_repeat_cntNo(v_blStatus,
v_orgId,'N'); --excel中存在箱号相同的数据
--YQLOAD_COMMON_VALID_PKG.valid_exp_spci_temperature(v_blStatus, v_orgId, 'N'); --冻柜设定温度必填:允许
--YQLOAD_COMMON_VALID_PKG.valid_exp_spci_danger(v_blStatus, v_orgId, 'N'); --危险品信息（IMCO/UNDG）必填：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_spci_cnt_not_eq(v_blStatus,
v_orgId,
'N'); --订舱信息的箱型尺码不匹配：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_spci_cnt_num_gt(v_blStatus, v_orgId,'N'); --集装箱数量大于订舱数量
YQLOAD_COMMON_VALID_PKG.valid_exp_yq_send(v_blStatus, v_orgId); --是否已经约桥
/*不需要校验 valid_spci_cnt_size_type_3;*/
YQLOAD_COMMON_VALID_PKG.valid_exp_upload_time(v_blStatus, v_orgId); --已超过驳船上传资料时间：允许导入
YQLOAD_COMMON_VALID_PKG.get_ids(v_blStatus, v_orgId); --/*获取id*/
YQLOAD_COMMON_VALID_PKG.ltrim_special; -- /*去掉空格*/
/*用于one校验；Begin*/
end;
END ONE_YQLOAD_EXP_VALID_PKG;
v_blStatus char(1);
v_orgId varchar2(100);
v_feeder varchar2(10);
--执行验证的方法：one驳船公司导入excel
PROCEDURE valid_method(blStatus varchar2,
orgId varchar2,
feeder varchar2) is
begin
/*赋值查询是进口约桥：还是出口约桥*/
v_blStatus := blStatus;
v_orgId := orgId;
v_feeder := feeder;
YQLOAD_COMMON_VALID_PKG.split_SPCI_CNT_SIZE_TYPE; -- /*分割装箱类型/大小*/
/*代码转换*/
YQLOAD_COMMON_VALID_PKG.valid_CNT_OPERATOR(v_orgId,'N'); --箱主没有维护
YQLOAD_COMMON_VALID_PKG.valid_ct_accounts(v_orgId,'N'); --供应商
YQLOAD_COMMON_VALID_PKG.valid_ct_cctm('N'); --箱型尺码不存在
YQLOAD_COMMON_VALID_PKG.valid_transfer_coDe('N'); /*中转类型判断*/
/*用于one校验；Begin*/
YQLOAD_COMMON_VALID_PKG.valid_exp_so(v_blStatus, v_orgId,'N'); --判断so是否存在
--页面大船装货港与大船船名航次的码头不一致：one用到。
/*YQLOAD_COMMON_VALID_PKG.valid_exp_berth_code(v_blStatus, v_orgId, 'N');*/
YQLOAD_COMMON_VALID_PKG.judge_exist_schedule; --相当于锁
If v_feeder is not null Then
YQLOAD_COMMON_VALID_PKG.valid_exp_so_feeder(v_blStatus,
v_orgId,
v_feeder,'N'); /*判断这个箱是不是这个CCA的*/
End If;
YQLOAD_COMMON_VALID_PKG.judge_exist_schedule; --相当于锁
/*导入校验*/
--如果船期没找到，以下校验就不需要了
--valid_imp_schedule;
YQLOAD_COMMON_VALID_PKG.VALID_DISCHARGE_PORT_SAME(v_blStatus,
v_orgId,
'Y'); --one卸货港与配置管理一致需要进行提示。
YQLOAD_COMMON_VALID_PKG.VALID_DESTINAL_PORT_SAME(v_blStatus,
v_orgId,
'Y'); --one目的港与配置管理一致需要进行提示。
YQLOAD_COMMON_VALID_PKG.VALID_VGM_HEAVY(v_blStatus, v_orgId, 'Y'); --是否VGM超过了30480kgs，进行提示
YQLOAD_COMMON_VALID_PKG.VALID_DRY_RF(v_blStatus, v_orgId, 'Y'); --\*是否是干冻柜，如果是则进行备注提示 ，允许导入*\
YQLOAD_COMMON_VALID_PKG.valid_exp_cnt_num_lt(v_blStatus, v_orgId, 'Y'); --集装箱数量小于订舱数量：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_spci_over(v_blStatus, v_orgId, 'N'); --/*超限*/：允许
-- YQLOAD_COMMON_VALID_PKG.valid_exp_cargo_name(v_blStatus, v_orgId, 'N'); -- /*危险品货物必填*/允许
YQLOAD_COMMON_VALID_PKG.valid_exp_so_vessel_voyage(v_blStatus, v_orgId,'N'); ----so号对应的船名航次跟excel不对应:
--YQLOAD_COMMON_VALID_PKG.valid_exp_special_port_same(v_blStatus, v_orgId, 'N'); --/*S/O上的港口信息（卸货港、目的港），属于特殊港口，请认真核对*/：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_block_code_same(v_blStatus,
v_orgId,
'N'); --BLOCK CODE跟系统不一致
YQLOAD_COMMON_VALID_PKG.valid_spgi_temperature_same(v_blStatus,
v_orgId,
'N'); --冻柜设定温度与系统不一致
YQLOAD_COMMON_VALID_PKG.valid_exp_spci_danger_same(v_blStatus,
v_orgId,
'N'); --危险品信息（IMCO/UNDG）与系统不一致
YQLOAD_COMMON_VALID_PKG.valid_exp_handling(v_blStatus, v_orgId, 'N'); --\*特殊配载填写不一致*\
YQLOAD_COMMON_VALID_PKG.valid_exp_port(v_blStatus, v_orgId,'N'); --/*S/O上的港口信息（卸货港、目的港），与系统港口不一致*/：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_Load_Area_Code(v_blStatus,
v_orgId,
'N'); --one大船装货码头与勾选装货码头不一致
YQLOAD_COMMON_VALID_PKG.valid_exp_PRE_DISC_AREA_CODE(v_blStatus,
v_orgId,
'N'); --one系统勾选大船装货码头与系统码头不一致
YQLOAD_COMMON_VALID_PKG.valid_exp_soAndCntNo(v_blStatus, v_orgId, 'N'); --已经驳船卸船：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_cut_time(v_blStatus, v_orgId,'N'); --/*已超过截重时间*/
YQLOAD_COMMON_VALID_PKG.valid_exp_excel_repeat_cntNo(v_blStatus,
v_orgId,'N'); --excel中存在箱号相同的数据
--YQLOAD_COMMON_VALID_PKG.valid_exp_spci_temperature(v_blStatus, v_orgId, 'N'); --冻柜设定温度必填:允许
--YQLOAD_COMMON_VALID_PKG.valid_exp_spci_danger(v_blStatus, v_orgId, 'N'); --危险品信息（IMCO/UNDG）必填：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_spci_cnt_not_eq(v_blStatus,
v_orgId,
'N'); --订舱信息的箱型尺码不匹配：允许
YQLOAD_COMMON_VALID_PKG.valid_exp_spci_cnt_num_gt(v_blStatus, v_orgId,'N'); --集装箱数量大于订舱数量
YQLOAD_COMMON_VALID_PKG.valid_exp_yq_send(v_blStatus, v_orgId); --是否已经约桥
/*不需要校验 valid_spci_cnt_size_type_3;*/
YQLOAD_COMMON_VALID_PKG.valid_exp_upload_time(v_blStatus, v_orgId); --已超过驳船上传资料时间：允许导入
YQLOAD_COMMON_VALID_PKG.get_ids(v_blStatus, v_orgId); --/*获取id*/
YQLOAD_COMMON_VALID_PKG.ltrim_special; -- /*去掉空格*/
/*用于one校验；Begin*/
end;
END ONE_YQLOAD_EXP_VALID_PKG;
...全文
请发表友善的回复…
发表回复
黄硕。 2019-05-10
- 打赏
- 举报
/*新需求Begin*/
--验证超限是否填写：公共
PROCEDURE valid_exp_spci_over(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '超限信息必填';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where (tt.SPCI_OVER_WIDTH_LEFT is null and
tt.SPCI_OVER_WIDTH_RIGHT is null and
tt.SPCI_OVER_LENGTH_FRONT is null and
tt.SPCI_OVER_LENGTH_BACK is null and
tt.SPCI_OVER_HEIGHT is null)
and exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and m.SPGI_IS_OVER_FLAG = 'Y' /*是否包含冷藏货物*/
)
and tt.STOP_VALID = 'N';
end;
--验证超限是否填写：one
PROCEDURE valid_exp_spci_over_byremark(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '超限信息必填';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where tt.SPCI_REMARK is null
and exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and m.SPGI_IS_OVER_FLAG = 'Y' /*是否包含冷藏货物*/
)
and tt.STOP_VALID = 'N';
end;
--验证危险品是否必填，ONE需求
PROCEDURE valid_exp_spci_danger_same(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '危险品信息（IMCO/UNDG）与系统不一致';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and upper(nvl(m.spgi_danger_class, 'null')) =
upper(nvl(t.SPCI_DANGER_CLASS, 'null')) and
upper(nvl(m.spgi_danger_un_no, 'null')) =
upper(nvl(t.SPCI_DANGER_UN_NO, 'null'))
) /*危险品信息*/
and tt.STOP_VALID = 'N';
end;
--验证危险品是否必填
PROCEDURE valid_exp_spci_danger(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '危险品信息（IMCO/UNDG）必填';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where (tt.SPCI_DANGER_CLASS is null or tt.SPCI_DANGER_UN_NO is null)
and exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and m.SPGI_IS_DANGER_CARGO_FLAG = 'Y' /*是否危险品*/
)
and tt.STOP_VALID = 'N';
end;
--验证危险品是否必填
PROCEDURE valid_exp_cargo_name(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '货物信息必填';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and t.CARGO_NAME is null
and (t.IS_LITHIUM_BATTERY = 'Y' or
t.SPCI_DANGER_CLASS is not null or
t.SPCI_DANGER_UN_NO is not null))
and tt.STOP_VALID = 'N';
end;
--箱型，尺码验证1：订舱信息
PROCEDURE valid_exp_spci_cnt_not_eq(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '订舱信息的箱型尺码不匹配';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexpscon_view m /*出口约桥预装载视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and m.SECR_CNT_TYPE = t.spci_cnt_type /*箱型*/
and m.SECR_CNT_SIZE = t.spci_cnt_size /*尺码*/
)
and tt.STOP_VALID = 'N';
end;
--箱型，尺码数量匹配验证2：少个校验（集装箱数量大于订舱数量）
PROCEDURE valid_exp_spci_cnt_num_gt(v_blStatus in varchar2,
v_orgId in varchar2,v_isOperate in varchar2) is
begin
v_errorMes := '集装箱数量大于订舱数量';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where exists
( select 1
from msc_yqexpscon_sum_view mc,TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexpscon_view m /*出口约桥预装载视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表进口舱单*/
and tt.rowid = t.rowid
and (t.error_msg is null or t.is_operate = 'Y')
and m.SECR_CNT_TYPE = t.spci_cnt_type /*箱型*/
and m.SECR_CNT_SIZE = t.spci_cnt_size /*尺码*/
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and m.SECR_CNT_QUANLITY <mc.ct_Num
and mc.org_id =v_orgId
and mc.bl_no = t.bl_no
and mc.spci_cnt_size = t.spci_cnt_size
and mc.spci_cnt_type =t.spci_cnt_type
)
and tt.STOP_VALID = 'N';
end;
--箱型，尺码数量匹配验证2：少个校验（集装箱数量少于订舱数量）
PROCEDURE valid_exp_cnt_num_lt(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '集装箱数量小于订舱数量';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where exists
( select 1
from msc_yqexpscon_sum_view mc,TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexpscon_view m /*出口约桥预装载视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表进口舱单*/
and tt.rowid = t.rowid
and (t.error_msg is null or t.is_operate = 'Y')
and m.SECR_CNT_TYPE = t.spci_cnt_type /*箱型*/
and m.SECR_CNT_SIZE = t.spci_cnt_size /*尺码*/
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and m.SECR_CNT_QUANLITY >mc.ct_Num
and mc.org_id =v_orgId
and mc.bl_no = t.bl_no
and mc.spci_cnt_size = t.spci_cnt_size
and mc.spci_cnt_type =t.spci_cnt_type
)
and tt.STOP_VALID = 'N';
end;
PROCEDURE valid_exp_yq_Send(v_blStatus in varchar2, v_orgId in varchar2) is
begin
v_errorMes := '已约桥数据,请重发约桥资料';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = 'Y'
where exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥预装载视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表进口舱单*/
and (t.error_msg is null or t.is_operate = 'Y')
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and m.SPCI_YQ_STATUS = 'Y'
and tt.rowid = t.rowid)
and tt.STOP_VALID = 'N';
end;
黄硕。 2019-05-10
- 打赏
- 举报
--xu:检测到目的港与系统匹配一致，则进行提示，one需求
PROCEDURE valid_exp_special_port_same(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '请核对';
/* update TMP_YQLOAD_VALID tt
set tt.error_msg =tt.error_msg || ';' || chr(13) || chr(10) ||
'请核对卸货港'||tt.DISCHARGE_PORT_C_N||'目的港'||tt.DESTINAL_PORT_C_N||'与系统配置特殊港口一致，港口请放到备注栏',
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where exists
--只通过so号进行判断，没有通过船名航次判断
--通过so号进行判断，并且通过船名航次判断是否相同
(select 1
from TMP_YQLOAD_VALID t \*临时表*\,
shsailingscheduleview m \*船视图*\,
sexportmanifest ssem \*出口舱单表*\
where t.cshc_en_vessel = m.cshc_en_vessel \*船名*\
and t.SHSS_VOYAGE_CODE = m.SHSS_EXP_VOYAGE_CODE \*航次*\
and m.SHSS_ORG_ID = v_orgId \*orgId*\
and ssem.ssem_voyage_id = m.shss_voyage_id \*船期表*\
and t.BL_NO = ssem.ssem_bl_no \*so号*\
and t.bl_status = v_blStatus \*0代表出口舱单*\
and tt.rowid = t.rowid
and (decode(ssem.ssem_destinal_port_code,
null,
'null',
ssem.ssem_destinal_port_code) in
(select this_.code_value
from bco_code_master this_
where this_.code_type = 'YQ_SPECIAL_PORT'
and this_.code_config = v_orgId)) \*目的港*\
and tt.rowid = t.rowid)
and tt.STOP_VALID = 'N';*/
end;
--校验S/O上的港口信息（装货港、卸货港、目的港），如港口不一致，提示港口不一致，不导入ONE用到
PROCEDURE valid_exp_port(v_blStatus in varchar2, v_orgId in varchar2,v_isOperate in varchar2) is
--需要具体提示什么港口
begin
v_errorMes := 'S/O上的港口信息(卸货港/目的港),与系统港口不一致';
UPDATE TMP_YQLOAD_VALID tt
SET tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
WHERE NOT EXISTS
(SELECT 1
FROM TMP_YQLOAD_VALID t,
shsailingscheduleview m,
sexportmanifest ssem
WHERE m.SHSS_ORG_ID = v_orgId /*orgId*/
AND ssem.ssem_voyage_id = m.shss_voyage_id /*船期表*/
AND t.BL_NO = ssem.ssem_bl_no /*so号*/
AND t.bl_status = v_blStatus /*0代表出口舱单*/
AND ssem.ssem_valid_flag = 'Y'
AND tt.rowid = t.rowid
AND ((Get_StrOfIndex(ssem.ssem_DISCHARGE_PORT_CODE, ',', 0) =
nvl(Get_StrOfIndex(t.DISCHARGE_PORT_C_N, ',', 0),
'null') OR
Get_StrOfIndex(ssem.ssem_DISCHARGE_PORT_name, ',', 0) =
nvl(Get_StrOfIndex(t.DISCHARGE_PORT_C_N, ',', 0),
'null') AND
(Get_StrOfIndex(ssem.ssem_destinal_port_code, ',', 0) =
nvl(Get_StrOfIndex(t.DESTINAL_PORT_C_N, ',', 0),
'null') OR
Get_StrOfIndex(ssem.ssem_destinal_port_name, ',', 0) =
nvl(Get_StrOfIndex(t.DESTINAL_PORT_C_N, ',', 0),
'null')))))
AND tt.STOP_VALID = 'N';
end;
--以S/O + 集装箱号为唯一原则，如有重复，判断，如已经配船或已经驳船卸船，则提示不允许更改数据
PROCEDURE valid_exp_soAndCntNo(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '已经驳船卸船';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where exists
--通过so+集装箱号，
(select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.ssem_bl_no /*so号*/
and t.spci_cnt_no = m.spci_cnt_no /*箱号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
/*and m.SPCI_LODING_STATUS in ('1')*/
and m.SPCI_FULL_IN_TIME is not null)
and tt.STOP_VALID = 'N';
--通过so+集装箱号+船名航次
/* (select 1
from TMP_YQLOAD_VALID t \*临时表*\
inner join msc_yqimploading_view m \*进口约桥查询视图*\
on t.SSEM_BL_NO = m.siim_bl_no \*so号*\
and t.spci_cnt_no = m.spci_cnt_no \*箱号*\
where t.bl_status = v_blStatus \*0代表进口舱单*\
and tt.rowid = t.rowid
and m.SPCI_LODING_STATUS in ('已经装船')
and m.SPCI_STOWAGE_STATUS in ('已经配船')); */
end;
--校验航线结关时间
PROCEDURE valid_exp_cut_time(v_blStatus in varchar2, v_orgId in varchar2,v_isOperate in varchar2) is
begin
v_errorMes := '已超过截重时间';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
/*and m.SHVS_CNT_CUSTOM_CUT_TIME > sysdate \*精确到当前时间*\*/
and m.shvs_cyclose_time < sysdate /*精确到当前时间*/
)
and tt.STOP_VALID = 'N';
end;
--这个校验最后执行：截止驳船上传时间，如果已经过了，给予提示？没找到驳船的上传时间：
PROCEDURE valid_exp_upload_time(v_blStatus in varchar2,
v_orgId in varchar2) is
begin
v_errorMes := '已超过驳船上传资料时间';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = 'E'
where exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and (t.error_msg is null or t.is_operate = 'Y')
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and (m.shvs_Cnt_Upload_Time < sysdate or
m.shvs_Cnt_Upload_Time is null) /*精确到当前时间*/
)
and tt.STOP_VALID = 'N';
end;
/*新需求Begin*/
--BLOCK CODE必须一致，不一致进行提示。ONE需求
PROCEDURE valid_exp_block_code_same(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := 'BLOCK CODE跟系统不一致';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and (
upper(m.SSEM_DISCHARGE_INSTRUC_CODE)=upper(t.DISCHARGE_INSTRUC_C_N) or
upper(m.SSEM_DISCHARGE_INSTRUC_NAME)=upper(t.DISCHARGE_INSTRUC_C_N) or
(m.SSEM_DISCHARGE_INSTRUC_CODE is null and m.SSEM_DISCHARGE_INSTRUC_NAME is null and t.DISCHARGE_INSTRUC_C_N is null)
) /*bblock code*/
)
and tt.STOP_VALID = 'N';
end;
--温度与系统一致：one需求
PROCEDURE valid_spgi_temperature_same(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '冻柜设定温度与系统不一致';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and nvl(m.spgi_temperature, '-1') =
nvl(t.SPCI_TEMPERATURE, '-1') /*bblock code*/
)
and tt.STOP_VALID = 'N';
end;
--验证温度是否必填
PROCEDURE valid_exp_spci_temperature(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '冻柜设定温度必填';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where (tt.Spci_Temperature is null)
and exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.SSEM_BL_NO /*so号*/
where t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and m.SPGI_IS_FREEZE_CARGO_FLAG = 'Y' /*是否包含冷藏货物*/
)
and tt.STOP_VALID = 'N';
end;
黄硕。 2019-05-10
- 打赏
- 举报
CREATE OR REPLACE PACKAGE body YQLOAD_COMMON_VALID_PKG IS
v_errorMes varchar(500);
--代码转换：不用考虑进出口舱单信息Begin
--中转类型校验
PROCEDURE valid_transfer_code(v_isOperate in varchar2) is
begin
v_errorMes := '中转类型有误';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes ,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where nvl(tt.transfer_code, '$') != '1'
and nvl(tt.transfer_code, '$') != '2'
and tt.STOP_VALID = 'N';
end;
--通过驳船公司判断是否存在供应商
PROCEDURE valid_ct_accounts(v_orgId in varchar2,v_isOperate in varchar2) is
--添加错误
begin
v_errorMes := '驳船公司供应商不存在';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists
(select 1
from TMP_YQLOAD_VALID t, hz_cust_accounts hz
where tt.rowid = t.rowid
and hz.org_id = v_orgId /*orgId*/
and ((t.SPCI_SECOND_CARRIER_NAME = hz.attribute2 or
t.SPCI_SECOND_CARRIER_NAME = hz.account_Number or
t.SPCI_SECOND_CARRIER_NAME = hz.attribute20 or
t.SPCI_SECOND_CARRIER_NAME = hz.account_Name) or
t.spci_second_carrier_id is null))
and tt.STOP_VALID = 'N';
end;
--通过驳船公司判断是否存在供应商
PROCEDURE valid_cnt_operator(v_orgId in varchar2,v_isOperate in varchar2) is
--添加错误
begin
v_errorMes := '箱主没有维护';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t, hz_cust_accounts hz
where tt.rowid = t.rowid
and hz.org_id = v_orgId /*orgId*/
and (t.SPCI_CNT_OPERATOR_NAME = hz.account_Name or
t.SPCI_CNT_OPERATOR_NAME = hz.attribute2 or
t.SPCI_CNT_OPERATOR_NAME = hz.account_number)
and hz.attribute6 like '%BB%')
and tt.STOP_VALID = 'N';
end;
--箱型尺码，需要维护常见的箱型尺码转换
PROCEDURE valid_ct_cctm(v_isOperate in varchar2) is
--添加错误
begin
v_errorMes := '箱型尺码不存在';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t, CSUBCONTAINERMEASURE cc
where tt.rowid = t.rowid
and cc.csctm_cnt_code = t.spci_cnt_size_type)
and tt.STOP_VALID = 'N';
end;
--End
--导入校验：需要判断进出口舱单
--Begin
--校验S/O号是否存在，如不存在，提示S/O不存在，不导入。ssem_bl_no
PROCEDURE valid_exp_so(v_blStatus in varchar2, v_orgId in varchar2,v_isOperate in varchar2) is
begin
v_errorMes := 's/o号不存在';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/
inner join msc_yqexploading_view m /*出口约桥查询视图*/
on t.BL_NO = m.ssem_bl_no
where t.bl_status = v_blStatus /*0代表出口舱单*/
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and tt.rowid = t.rowid)
and tt.STOP_VALID = 'N';
end;
--校验这个单是不是这个feeder的
PROCEDURE valid_exp_so_feeder(v_blStatus in varchar2,
v_orgId in varchar2,
v_feeder in varchar2,v_isOperate in varchar2) is
begin
v_errorMes := '这票单已经被其它驳船公司约桥';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where exists (select 1
from sexportmanifest ssem,
scontainerinfo si,
SYUEQIAO syq,
TMP_YQLOAD_VALID t
where ssem.ssem_exp_bl_id = si.spci_exp_bl_id
and syq.YQ_BATCHNO = si.SPCI_YQ_BATCHNO
and syq.feeder_id != v_feeder
and ssem.ssem_bl_no = t.BL_NO
and si.spci_cnt_no = t.spci_cnt_no
and ssem.ssem_org_id = v_orgId
and tt.rowid = t.rowid)
and tt.STOP_VALID = 'N';
end;
--校验S/O号上的大船船名航次与系统中的船名航次是否一致，不一致，提示船名航次不一致，不导入。
PROCEDURE valid_exp_so_vessel_voyage(v_blStatus in varchar2,
v_orgId in varchar2,v_isOperate in varchar2) is
begin
v_errorMes := 'S/O号上的大船船名航次与系统中的船名航次不一致';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes
,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/,
shsailingscheduleview m /*船视图*/,
sexportmanifest ssem /*出口舱单表*/
where t.cshc_en_vessel = m.cshc_en_vessel /*船名*/
and t.SHSS_VOYAGE_CODE = m.SHSS_EXP_VOYAGE_CODE /*航次*/
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and ssem.ssem_voyage_id = m.shss_voyage_id /*船期表*/
and t.BL_NO = ssem.ssem_bl_no /*so号*/
and t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid)
and tt.STOP_VALID = 'N';
end;
--
PROCEDURE valid_exp_berth_code(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '大船装货码头与勾选装货码头不一致';
/*update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t \*临时表*\,
shsailingscheduleview m \*船视图*\,
sexportmanifest ssem \*出口舱单表*\
where t.LOAD_AREA_CODE = ssem.ssem_Load_Area_Code \*大船装货码头*\
and m.SHSS_ORG_ID = v_orgId \*orgId*\
and ssem.ssem_voyage_id = m.shss_voyage_id \*船期表*\
and t.BL_NO = ssem.ssem_bl_no \*so号*\
and t.bl_status = v_blStatus \*0代表出口舱单*\
and tt.rowid = t.rowid)
and tt.STOP_VALID = 'N';*/
end;
--系统勾选大船装货码头与系统码头不一致
PROCEDURE valid_exp_Load_Area_Code(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '选择的大船装货码头与系统不一致';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/,
shsailingscheduleview m /*船视图*/,
sexportmanifest ssem /*出口舱单表*/
where t.LOAD_AREA_CODE = ssem.ssem_Load_Area_Code /*大船装货码头*/
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and ssem.ssem_org_id=v_orgId /*orgId*/
and ssem.ssem_voyage_id = m.shss_voyage_id /*船期表*/
and t.BL_NO = ssem.ssem_bl_no /*so号*/
and t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid)
and tt.STOP_VALID = 'N';
end;
--系统勾选大船装货码头与系统码头不一致
PROCEDURE valid_exp_PRE_DISC_AREA_CODE(v_blStatus in varchar2,
v_orgId in varchar2,
v_isOperate in varchar2) is
begin
v_errorMes := '选择的驳船装货码头与系统不一致';
update TMP_YQLOAD_VALID tt
set tt.error_msg = tt.error_msg || ';' || chr(13) || chr(10) ||
v_errorMes,
tt.is_operate = decode(v_isOperate, 'Y', 'Y', 'N')
where not exists (select 1
from TMP_YQLOAD_VALID t /*临时表*/,
shsailingscheduleview m /*船视图*/,
sexportmanifest ssem /*出口舱单表*/
where ssem.ssem_PRE_DISC_AREA_CODE /*驳船装货码头*/
in ( select column_value from table(fn_split(t.Pre_Disc_Area_Code,';')))
and m.SHSS_ORG_ID = v_orgId /*orgId*/
and ssem.ssem_org_id = v_orgId /*orgId*/
and ssem.ssem_voyage_id = m.shss_voyage_id /*船期表*/
and t.BL_NO = ssem.ssem_bl_no /*so号*/
and t.bl_status = v_blStatus /*0代表出口舱单*/
and tt.rowid = t.rowid)
and tt.STOP_VALID = 'N';
end;
