CREATE OR REPLACE PACKAGE BODY pkg_dw_ui_ckd IS
----------------------------------------------------------------------------
-- Author : V00000071
-- Created : 2021/10/18 09:40:00
-- Purpose : 自动创建CKD工单-叫料计划选择
-------------------------------------------------------------------------
/* 1. 说明： 根据查询条件将叫料计划写入后台表1（新增表，存储写入的叫料计划）
显示界面按照国家&物料汇总计算数量
2. add by V53368915 20211130 #1467243 UAT-D-V1-CKD自动开单DGDC直发物料筛选掉 优化
3 * by v00000082 xz 20211213 #1496031 PRD-关于UI表中涉及到生效失效标志逻辑补充
4. add by CGM 2022-02-15 #1571628 UAT-D-V1-CKD工单叫料计划新增项目名称编码/项目名称
5. add by CGM 2022-02-18 #1606137 UAT-D-V1-CKD工单新增剔除子项类别功能
*/
---------------------------------------------------------------------------
procedure p_import_callin_data(p_plan_version in varchar2, ----V0计划版本
p_to_organization_code in varchar2, ----国家组织
p_ckd_item_number in varchar2, ----CKD类别编码
p_sub_category_code in varchar2, ----子项类别
p_exclude_category_code in varchar2, ----排除的物料类别
p_start_date in date,
p_end_date in date,
p_user_id in varchar2,
p_user_name in varchar2,
p_err_code out number) is
v_task_name varchar2(50) := 'p_import_callin_data';
v_sub_task varchar2(100);
v_msg varchar2(2000);
v_plan_version varchar2(50); --计划版本
v_row_count number;
begin
p_err_code := 1;--代表没有生成相关数据
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'Start',
v_msg);
--1. 校验所选V0计划版本是否为最新版本，若不是最新版本直接跳过
select MAX(engine_version || plan_version || run_version)
into v_plan_version
from dwmgr.in_plan_version t
where t.engine_version = 'V0'
and t.is_current_flag = 'Y'
and t.plan_type = gv_plan_type
and t.engine_version || t.plan_version || t.run_version = p_plan_version;
if v_plan_version is not null then
--添加分区
dwmgr.pkg_iplan_util.p_add_partition(null, null, p_err_code, 'UI_CKD_BASE_DATA', 'PART_'||v_plan_version, v_plan_version, 'N');
v_sub_task := '.init call_in data.1';
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Start',
v_msg);
--2.1 插入叫料数量基础数据（非其他）
insert into dwmgr.ui_ckd_base_data
(id,
plan_version,
from_organization_code,
from_organization_name,
to_organization_code,
to_organization_name,
transport_method,
priority,
item_number,
item_desc_cn,
item_desc_en,
--ckd_category_code,
--ckd_category_desc,
ckd_item_number,
ckd_item_desc_cn,
ckd_item_desc_en,
line_code,
line_name,
sub_category_code,
sub_category_desc_cn,
sub_category_desc_en,
call_in_date,
call_in_qty,
onhand_qty,
order_qty,
remaining_qty,
handled_qty,
spq,
unit,
import_date,
sys_created_date,
sys_created_by,
sys_created_by_name,
sys_last_modified_date,
sys_last_modified_by,
sys_last_modified_by_name,
project_code , -- add by CGM 2022-02-15 #1571628 UAT-D-V1-CKD工单叫料计划新增项目名称编码/项目名称
project_name )
select dwmgr.seq_ui_ckd_base_data.nextval,
v_plan_version, ------需增加最新版本校验
from_organization_code,
from_organization_name,
to_organization_code,
to_organization_name,
transport_method,
priority,
item_number,
item_desc_cn,
item_desc_en,
--ckd_category_code,
--ckd_category_desc,
ckd_item_number,
ckd_item_desc_cn,
ckd_item_desc_en,
line_code,
line_name,
sub_category_code,
sub_category_desc_cn,
sub_category_desc_en,
call_in_date,--叫料计划日期
call_in_qty,
null as onhand_qty, --先留空，后面更新
null as order_qty, --先留空，后面更新
null as remaining_qty, --暂不启用，默认值为：叫料计划数量-库存量-已有工单量（前端计算展示）
null as handled_qty, --暂不启用，默认值为0（前端计算展示）
null as spq, --暂不启用，默认值为：冲减后数量-当前已处理数量（前端计算展示）
unit,
trunc(sysdate), --导入日期
sysdate, --创建日期
p_user_id,
p_user_name,
sysdate, --修改日期
p_user_id,
p_user_name,
project_code, -- add by CGM 2022-02-15 #1571628 UAT-D-V1-CKD工单叫料计划新增项目名称编码/项目名称
project_name
from (select /*+parallel(32)*/b.from_organization_code,
b.from_organization_name,
b.to_organization_code,
b.to_organization_name,
h.transmode as transport_method,
h.priority,
id.item_number,
id.item_desc_cn,
id.item_desc_en,
--b.ckd_category_code,
--b.ckd_category_desc,
b.item_number as ckd_item_number,
b.item_desc_cn as ckd_item_desc_cn,
b.item_desc_en as ckd_item_desc_en,
b.line_code,
b.line_name,
b.sub_category_code,
nvl(csc.new_category_desc_cn, b.sub_category_desc_cn) as sub_category_desc_cn,
nvl(csc.new_category_desc_en, b.sub_category_desc_en) as sub_category_desc_en,
l.bucket_date as call_in_date,--叫料计划日期
sum(l.bucket_qty) as call_in_qty,
id.unit,
h.project_code, -- add by CGM 2022-02-15 #1571628 UAT-D-V1-CKD工单叫料计划新增项目名称编码/项目名称
h.project_name
from dwmgr.in_item_detail id
left join dwmgr.ui_ckd_sub_category csc
on id.item_number = csc.item_number
and id.organization_code = csc.organization_code
and id.category_code = csc.old_sub_category_code --找物料修正后的物料类别
and csc.enabled_flag = 'Y'
inner join dwmgr.ui_ckd_category b
on b.sub_category_code = nvl(csc.new_sub_category_code, id.category_code) --拿物料类别找工单创建的类别配置
and b.from_organization_code = id.organization_code
and b.enabled_flag = 'Y'
inner join dwmgr.ui_dy_cp_inner_report_header h
on h.plan_version = v_plan_version
and h.item_number = id.item_number
inner join dwmgr.ui_organization_location_rel ol1
on h.dest_location_code = ol1.location_code
and ol1.default_organization_flag = 'Y'
and ol1.enabled_flag = 'Y'
and ol1.organization_code = b.to_organization_code --工单类别配置需与叫料计划的需求组织保持一致
inner join dwmgr.ui_organization_location_rel ol2
on h.source_location_code = ol2.location_code
and ol2.default_organization_flag = 'Y'
and ol2.enabled_flag = 'Y'
and ol2.organization_code = b.from_organization_code --工单类别配置需与叫料计划的维护组织保持一致
inner join dwmgr.ui_dy_cp_inner_report_line_dtl l
on l.plan_version = v_plan_version
and h.header_id = l.header_id
and l.bucket_date between p_start_date and p_end_date
--and l.bucket_type = 'PLANNED_QTY' --计划到达数量
and l.bucket_type = 'PLAN_QTY' --计划发出数量
and l.bucket_qty > 0
where 1 = 1--id.item_number not like '533%'
--排除指定物料类别，该部分物料类别不允许进CKD工单叫料计划
and (p_exclude_category_code is null or
((p_exclude_category_code is not null and id.category_code not in
(SELECT regexp_substr(p_exclude_category_code, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_exclude_category_code, ',') + 1)) and
(-- add by CGM 2022-02-18 #1606137 UAT-D-V1-CKD工单新增剔除子项类别功能
-- 同时识别ERP原始类别和物料子项类别模块修改后的子项类别，同时进行排除
p_exclude_category_code is not null and not exists
(select 1
from dwmgr.ui_ckd_sub_category sub -- 物料子项类别修正配置表
where sub.new_sub_category_code in (SELECT regexp_substr(p_exclude_category_code, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_exclude_category_code, ',') + 1)
and id.item_number = sub.item_number
and id.organization_code = sub.organization_code
and id.category_code = sub.old_sub_category_code
))
))
and (p_to_organization_code is null or
(p_to_organization_code is not null and b.to_organization_code in
(SELECT regexp_substr(p_to_organization_code, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_to_organization_code, ',') + 1)))
/*and (p_ckd_category_code is null or
(p_ckd_category_code is not null and b.ckd_category_code in
(SELECT regexp_substr(p_ckd_category_code, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_ckd_category_code, ',') + 1)))*/
and (p_ckd_item_number is null or
(p_ckd_item_number is not null and b.item_number in
(SELECT regexp_substr(p_ckd_item_number, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_ckd_item_number, ',') + 1)))
--处理已配置的子项类别（不包含190.其他-N/A）
and (p_sub_category_code is null or
(p_sub_category_code is not null and b.sub_category_code not like '%190.其他-N/A%'
and b.sub_category_code in
(SELECT regexp_substr(p_sub_category_code, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_sub_category_code, ',') + 1)))
--排除直发物料（计划参数维护中是否直发为Y）
and not exists ( -- add by V53368915 20211130 #1467243 UAT-D-V1-CKD自动开单DGDC直发物料筛选掉
select 1
from espmgr.mst_sku_v0 t3
where 1 = 1
and h.source_location_code = t3.loc
and id.item_number = t3.item
and t3.u_direct_flag = 'Y')
and not exists -------插入基础表里没有的数据
(select 1
from dwmgr.ui_ckd_base_data d2
where d2.plan_version = v_plan_version
and d2.to_organization_code = b.to_organization_code
and d2.from_organization_code = b.from_organization_code
and d2.item_number = id.item_number
--and d2.sub_category_code = b.sub_category_code
--and d2.ckd_category_code = b.ckd_category_code
and d2.call_in_date = l.bucket_date)
group by b.from_organization_code,
b.from_organization_name,
b.to_organization_code,
b.to_organization_name,
h.transmode,
h.priority,
id.item_number,
id.item_desc_cn,
id.item_desc_en,
--b.ckd_category_code,
--b.ckd_category_desc,
b.item_number,
b.item_desc_cn,
b.item_desc_en,
b.line_code,
b.line_name,
b.sub_category_code,
nvl(csc.new_category_desc_cn, b.sub_category_desc_cn),
nvl(csc.new_category_desc_en, b.sub_category_desc_en),
l.bucket_date,
id.unit,
h.project_code,
h.project_name);
v_msg := 'rows=' || SQL%ROWCOUNT;
--COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);
v_sub_task := '.init call_in data.2';
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Start',
v_msg);
--2.2 插入叫料数量基础数据（其他）
insert into dwmgr.ui_ckd_base_data
(id,
plan_version,
from_organization_code,
from_organization_name,
to_organization_code,
to_organization_name,
transport_method,
priority,
item_number,
item_desc_cn,
item_desc_en,
--ckd_category_code,
--ckd_category_desc,
ckd_item_number,
ckd_item_desc_cn,
ckd_item_desc_en,
line_code,
line_name,
sub_category_code,
sub_category_desc_cn,
sub_category_desc_en,
call_in_date,
call_in_qty,
onhand_qty,
order_qty,
remaining_qty,
handled_qty,
spq,
unit,
import_date,
sys_created_date,
sys_created_by,
sys_created_by_name,
sys_last_modified_date,
sys_last_modified_by,
sys_last_modified_by_name,
project_code ,-- add by CGM 2022-02-15 #1571628 UAT-D-V1-CKD工单叫料计划新增项目名称编码/项目名称
project_name )
select dwmgr.seq_ui_ckd_base_data.nextval,
v_plan_version, ------需增加最新版本校验
from_organization_code,
from_organization_name,
to_organization_code,
to_organization_name,
transport_method,
priority,
item_number,
item_desc_cn,
item_desc_en,
--ckd_category_code,
--ckd_category_desc,
ckd_item_number,
ckd_item_desc_cn,
ckd_item_desc_en,
line_code,
line_name,
sub_category_code,
sub_category_desc_cn,
sub_category_desc_en,
call_in_date,--叫料计划日期
call_in_qty,
null as onhand_qty, --先留空，后面更新
null as order_qty, --先留空，后面更新
null as remaining_qty, --暂不启用，默认值为：叫料计划数量-库存量-已有工单量（前端计算展示）
null as handled_qty, --暂不启用，默认值为0（前端计算展示）
null as spq, --暂不启用，默认值为：冲减后数量-当前已处理数量（前端计算展示）
unit,
trunc(sysdate), --导入日期
sysdate, --创建日期
p_user_id,
p_user_name,
sysdate, --修改日期
p_user_id,
p_user_name,
project_code ,-- add by CGM 2022-02-15 #1571628 UAT-D-V1-CKD工单叫料计划新增项目名称编码/项目名称
project_name
from (select /*+parallel(32)*/b2.from_organization_code,
b2.from_organization_name,
b2.to_organization_code,
b2.to_organization_name,
h.transmode as transport_method,
h.priority,
id.item_number,
id.item_desc_cn,
id.item_desc_en,
--b.ckd_category_code,
--b.ckd_category_desc,
b2.item_number as ckd_item_number,
b2.item_desc_cn as ckd_item_desc_cn,
b2.item_desc_en as ckd_item_desc_en,
b2.line_code,
b2.line_name,
b2.sub_category_code,
b2.sub_category_desc_cn,
b2.sub_category_desc_en,
l.bucket_date as call_in_date,--叫料计划日期
sum(l.bucket_qty) as call_in_qty,
id.unit,
h.project_code, -- add by CGM 2022-02-15 #1571628 UAT-D-V1-CKD工单叫料计划新增项目名称编码/项目名称
h.project_name
from dwmgr.in_item_detail id
/*left join dwmgr.ui_ckd_sub_category csc
on id.item_number = csc.item_number
and id.organization_code = csc.organization_code
and id.category_code = csc.old_sub_category_code --找物料修正后的物料类别
and csc.enabled_flag = 'Y'
left join dwmgr.ui_ckd_category b1
on b1.sub_category_code = nvl(csc.new_sub_category_code, id.category_code) --拿物料类别找工单创建的类别配置
and b1.from_organization_code = id.organization_code
and b1.enabled_flag = 'Y'*/
inner join dwmgr.ui_ckd_category b2
on b2.sub_category_code = '190.其他-N/A' --拿物料类别找工单创建的类别配置
and b2.from_organization_code = id.organization_code
and b2.enabled_flag = 'Y'
inner join dwmgr.ui_dy_cp_inner_report_header h
on h.plan_version = v_plan_version
and h.item_number = id.item_number
inner join dwmgr.ui_organization_location_rel ol1
on h.dest_location_code = ol1.location_code
and ol1.default_organization_flag = 'Y'
and ol1.enabled_flag = 'Y'
and ol1.organization_code = b2.to_organization_code --工单类别配置需与叫料计划的需求组织保持一致
inner join dwmgr.ui_organization_location_rel ol2
on h.source_location_code = ol2.location_code
and ol2.default_organization_flag = 'Y'
and ol2.enabled_flag = 'Y'
and ol2.organization_code = b2.from_organization_code --工单类别配置需与叫料计划的维护组织保持一致
inner join dwmgr.ui_dy_cp_inner_report_line_dtl l
on l.plan_version = v_plan_version
and h.header_id = l.header_id
and l.bucket_date between p_start_date and p_end_date
--and l.bucket_type = 'PLANNED_QTY' --计划到达数量
and l.bucket_type = 'PLAN_QTY' --计划发出数量
and l.bucket_qty > 0
where 1 = 1--id.item_number not like '533%'
--排除指定物料类别，该部分物料类别不允许进CKD工单叫料计划
and (p_exclude_category_code is null or
((p_exclude_category_code is not null and id.category_code not in
(SELECT regexp_substr(p_exclude_category_code, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_exclude_category_code, ',') + 1)) and
(-- add by CGM 2022-02-18 #1606137 UAT-D-V1-CKD工单新增剔除子项类别功能
-- 同时识别ERP原始类别和物料子项类别模块修改后的子项类别，同时进行排除
p_exclude_category_code is not null and not exists
(select 1
from dwmgr.ui_ckd_sub_category sub -- 物料子项类别修正配置表
where sub.new_sub_category_code in (SELECT regexp_substr(p_exclude_category_code, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_exclude_category_code, ',') + 1)
and id.item_number = sub.item_number
and id.organization_code = sub.organization_code
and id.category_code = sub.old_sub_category_code
)))
)
and (p_to_organization_code is null or
(p_to_organization_code is not null and b2.to_organization_code in
(SELECT regexp_substr(p_to_organization_code, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_to_organization_code, ',') + 1)))
and (p_ckd_item_number is null or
(p_ckd_item_number is not null and b2.item_number in
(SELECT regexp_substr(p_ckd_item_number, '[^,]+', 1, LEVEL, 'i')
FROM dual
CONNECT BY LEVEL <= REGEXP_COUNT(p_ckd_item_number, ',') + 1)))
--处理未配置的子项类别（包含190.其他-N/A）
--and (b1.sub_category_code is null or b1.sub_category_code = '190.其他-N/A') --排除掉已配置的子项类别
and (p_sub_category_code is null or
(p_sub_category_code is not null and p_sub_category_code like '%190.其他-N/A%'))
--排除直发物料（计划参数维护中是否直发为Y）
and not exists ( -- add by V53368915 20211130 #1467243 UAT-D-V1-CKD自动开单DGDC直发物料筛选掉
select 1
from espmgr.mst_sku_v0 t3
where 1 = 1
and h.source_location_code = t3.loc
and id.item_number = t3.item
and t3.u_direct_flag = 'Y')
and not exists -------插入基础表里没有的数据
(select 1
from dwmgr.ui_ckd_base_data d2
where d2.plan_version = v_plan_version
and d2.to_organization_code = b2.to_organization_code
and d2.from_organization_code = b2.from_organization_code
and d2.item_number = id.item_number
--and d2.sub_category_code = b.sub_category_code
--and d2.ckd_category_code = b.ckd_category_code
and d2.call_in_date = l.bucket_date)
group by b2.from_organization_code,
b2.from_organization_name,
b2.to_organization_code,
b2.to_organization_name,
h.transmode,
h.priority,
id.item_number,
id.item_desc_cn,
id.item_desc_en,
--b.ckd_category_code,
--b.ckd_category_desc,
b2.item_number,
b2.item_desc_cn,
b2.item_desc_en,
b2.line_code,
b2.line_name,
b2.sub_category_code,
b2.sub_category_desc_cn,
b2.sub_category_desc_en,
l.bucket_date,
id.unit,
h.project_code, -- add by CGM 2022-02-15 #1571628 UAT-D-V1-CKD工单叫料计划新增项目名称编码/项目名称
h.project_name);
v_msg := 'rows=' || SQL%ROWCOUNT;
--COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);
v_sub_task := '.update onhand_qty';
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Start',
v_msg);
--3. 更新库存数量
merge into dwmgr.UI_CKD_BASE_DATA tar
using (select b.id as id,
t.onhand_quantity as onhand_quantity
from dwmgr.ui_ckd_base_data b,
dwmgr.ui_ckd_category c,
(select item_number, organization_code, subinventory_code, locator_code, sum(onhand_quantity) onhand_quantity
from dwmgr.in_item_inv_quantity
group by item_number, organization_code, subinventory_code, locator_code) t
where b.plan_version = v_plan_version
and b.to_organization_code = c.to_organization_code
and b.sub_category_code = c.sub_category_code
and b.from_organization_code = t.organization_code
and b.item_number = t.item_number
and c.enabled_flag = 'Y' --1496031
and c.secondary_inventory_code = t.subinventory_code
and c.locator_code = t.locator_code
and b.onhand_init_flag = 'N') src
on (tar.plan_version = v_plan_version and tar.id = src.id)
when matched then
update set tar.onhand_qty = src.onhand_quantity;
v_msg := 'rows=' || SQL%ROWCOUNT;
--COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);
v_sub_task := '.update onhand_qty_others';
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Start',
v_msg);
--4. 更新其他仓库库存数量
merge into dwmgr.UI_CKD_BASE_DATA tar
using (select b.id as id,
t.onhand_qty_300,
t.onhand_qty_301,
t.onhand_qty_302,
t.onhand_qty_381
from dwmgr.ui_ckd_base_data b,
(select item_number,
organization_code,
sum(decode(subinventory_code, '300', onhand_quantity, 0)) as onhand_qty_300,
sum(decode(subinventory_code, '301', onhand_quantity, 0)) as onhand_qty_301,
sum(decode(subinventory_code, '302', onhand_quantity, 0)) as onhand_qty_302,
sum(decode(subinventory_code, '381', onhand_quantity, 0)) as onhand_qty_381
from dwmgr.in_item_inv_quantity
where subinventory_code in ('300', '301', '302', '381')
group by item_number, organization_code) t
where b.plan_version = v_plan_version
and b.from_organization_code = t.organization_code
and b.item_number = t.item_number
and b.onhand_init_flag = 'N') src
on (tar.plan_version = v_plan_version and tar.id = src.id)
when matched then
update set tar.onhand_qty_300 = src.onhand_qty_300,
tar.onhand_qty_301 = src.onhand_qty_301,
tar.onhand_qty_302 = src.onhand_qty_302,
tar.onhand_qty_381 = src.onhand_qty_381;
v_msg := 'rows=' || SQL%ROWCOUNT;
--COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);
v_sub_task := '.update onhand_qty';
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Start',
v_msg);
--5. 更新开单数量
merge into dwmgr.UI_CKD_BASE_DATA tar
using (select b.id,
h.order_qty
from dwmgr.ui_ckd_base_data b,
dwmgr.ui_ckd_category p, ----ckd工单创建类别配置表
(select --h.item_number,
h.organization_code,
h.line_code,
l.item_number as l_item_number,
abs(sum(l.open_issued)) as order_qty
from dwmgr.in_discrete_jobs h, --工单头
dwmgr.in_wreq_oprs l --工单行
where h.wip_entity_id = l.wip_entity_id
and h.organization_code = l.organization_code
and l.required_quantity < 0 -----需求数量为负的未发数量之和
and h.status_type in (1, 3)
group by --h.item_number,
h.organization_code,
h.line_code,
l.item_number) h ----取状态是1和3的
where b.plan_version = v_plan_version
and b.onhand_init_flag = 'N'
and b.sub_category_code = p.sub_category_code
and b.to_organization_code = p.to_organization_code
and b.from_organization_code = h.organization_code
and b.line_code = h.line_code
and p.enabled_flag = 'Y' --1496031
--and p.item_number = h.item_number
and b.item_number = h.l_item_number) src
on (tar.plan_version = v_plan_version and tar.id = src.id)
when matched then
update set tar.order_qty = src.order_qty;
v_msg := 'rows=' || SQL%ROWCOUNT;
--COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);
v_sub_task := '.update maintain_custom_flag';
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Start',
v_msg);
update dwmgr.UI_CKD_BASE_DATA t
set t.maintain_custom_flag = 'Y'
where t.plan_version = v_plan_version
and t.onhand_init_flag = 'N'
and exists (select 1
from dwmgr.in_exp_item_hs_info e --组织为主组织，不需要判断
where e.item_number = t.item_number);
v_msg := 'rows=' || SQL%ROWCOUNT;
COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);
v_sub_task := '.update onhand_init_flag';
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Start',
v_msg);
update dwmgr.UI_CKD_BASE_DATA t
set t.onhand_init_flag = 'Y'
where t.plan_version = v_plan_version
and t.onhand_init_flag = 'N';
v_row_count := SQL%ROWCOUNT;
if v_row_count > 0 then
p_err_code := 0;
else
p_err_code := 1;
end if;
v_msg := 'rows=' || v_row_count;
COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);
end if;
v_msg := 'Success';
--p_err_code := 0;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'End',
v_msg);
exception
when others then
rollback;
p_err_code := -1;--sqlcode;
v_msg := substr(dbms_utility.format_error_stack ||
dbms_utility.format_error_backtrace,
1,
2000);
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Failed',
v_msg);
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'Failed',
v_msg);
raise_application_error(-20001, v_msg);
end p_import_callin_data;
--------------------------------------------------------------------------------------------------------
-- Author : 00000071
-- Created : 2021/10/18 09:40:00
-- Purpose : 自动创建CKD工单-创建工单
----------------------------------------------------------------------------------------------------------
/* 说明： 该界面为头-行结构，
1. 头信息为建议工单的头信息，
2. 行信息为建议工单的组件物料,组件物料源于叫料计划选择界面勾选确认选定的数据，
3. 再根据类别维护界面维护的规则进行分组
4. 鼠标点中头数据的时候，将对应行信息显示在下方*/
----------------------------------------------------------------------------------------------------------
PROCEDURE p_confirm_ckd_mo(p_plan_version IN VARCHAR2,
p_batch_id IN NUMBER,
p_err_code OUT NUMBER) IS
v_task_name VARCHAR2(50) := 'p_confirm_ckd_mo.'||p_batch_id;
v_sub_task VARCHAR2(100);
v_msg VARCHAR2(2000);
v_once_task VARCHAR2(100);
v_mo_code VARCHAR2(100);
v_to_organization_code varchar2(3);
v_from_organization_code varchar2(3);
v_line_code varchar2(20);
v_count number := 0;
v_row_count number;
begin
p_err_code := 1;--代表没有生成相关数据
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'Start',
v_msg);
--先初始化好，以便支持重跑
update dwmgr.tmp_ui_comfirm_ckd_mo t
set t.current_spq = t.spq,
t.deduct_spq = 0,
t.sum_deduct_spq = 0
where t.batch_id = p_batch_id
and t.plan_version = p_plan_version;
--计算最大分组量，生成头表数据
for r in (select to_organization_code,
from_organization_code,
--ckd_category_code,
ckd_item_number,
transport_method,
priority,
line_code,
max_mo_qty,
mo_code_num,
mo_count
from (select to_organization_code,
from_organization_code,
--ckd_category_code,
ckd_item_number,
transport_method,
priority,
line_code,
max_mo_qty,
to_number(nvl(mo_code_num, '0')) as mo_code_num, --每个月第一次建议工单流水号会重置
nvl(ceil(sum_spq/max_mo_qty), 0) as mo_count,
row_number() over(partition by to_organization_code, from_organization_code,
ckd_item_number, transport_method, priority
order by nvl(ceil(sum_spq/max_mo_qty), 0) desc) rn
from (select t.from_organization_code,
t.to_organization_code,
--t.ckd_category_code,
t.ckd_item_number,
t.sub_category_code,
t.transport_method,
t.priority,
c.line_code,
c.max_mo_qty,
--substr(h.mo_code, -4, 3)||chr(ascii(substr(h.mo_code, -1)) - 49) mo_code_num, --取已有流水号，并转换为数字
--substr(h.mo_code, -4, 3)||chr(ascii(substr(h.mo_code, -1)) - 17) mo_code_num, --取已有流水号，并转换为数字
h.mo_code as mo_code_num,
sum(t.spq) over(partition by t.to_organization_code, t.from_organization_code, t.ckd_item_number,
t.sub_category_code, transport_method, priority) sum_spq
from dwmgr.tmp_ui_comfirm_ckd_mo t
inner join dwmgr.ui_ckd_category c
on c.to_organization_code = t.to_organization_code
and c.sub_category_code = t.sub_category_code
and c.from_organization_code = t.from_organization_code
and c.enabled_flag = 'Y' --1496031
left join (select h.from_organization_code,
h.line_code,
--空运时倒数第二位也为A-J，转换为数字，以便后续流水号递增
max(substr(h.mo_code, -4, 2)||(case when regexp_like(substr(h.mo_code, length(h.mo_code) - 1, 1), '[0-9]')
then substr(h.mo_code, length(h.mo_code) - 1, 1)
else chr(ascii(substr(h.mo_code, length(h.mo_code) - 1, 1)) - 17)
end)
||chr(ascii(substr(h.mo_code, -1)) - 17)) mo_code
from dwmgr.ui_ckd_suggested_mo_header h
where trunc(h.sys_created_date, 'MM') = trunc(sysdate, 'MM') --只取本月已生成建议工单的流水号
group by h.from_organization_code,
h.line_code) h
on h.from_organization_code = t.from_organization_code
and h.line_code = c.line_code
where t.batch_id = p_batch_id
and t.plan_version = p_plan_version
and t.spq > 0))
where rn = 1
order by to_organization_code,
from_organization_code,
line_code,
mo_count,
mo_code_num) loop
for i in (select level as mo_count
from dual
where r.mo_count > 0
connect by level < r.mo_count + 1) loop
--发出LOCATION的默认组织代码（3位）+“N”(1位)+“----”（4位）+年（2位，如21代表2021年）+月（1位，16进制）+流水号（4位），最后一位数字由0~9替代为a~j的字母
v_mo_code := substr(rpad(r.from_organization_code, 3, '-'), 1, 3)||'N----'||substr(rpad(r.line_code, 3, '-'), 1, 3)||to_char(sysdate, 'YY')||trim(to_char(to_char(sysdate, 'MM'), 'XX'));
if v_to_organization_code is null or v_from_organization_code is null or v_line_code is null
or v_to_organization_code <> r.to_organization_code or v_from_organization_code <> r.from_organization_code
or v_line_code <> r.line_code then
v_count := 1;
v_to_organization_code := r.to_organization_code;
v_from_organization_code := r.from_organization_code;
v_line_code := r.line_code;
else
v_count := v_count + 1;
end if;
--v_mo_code := v_mo_code||substr(lpad(r.mo_code_num + v_count, 4, '0'), 1, 3)||chr(ascii(substr(lpad(r.mo_code_num + v_count, 4, '0'), 4, 1)) + 49);
--小写a-j改为A-J
--v_mo_code := v_mo_code||substr(lpad(r.mo_code_num + v_count, 4, '0'), 1, 3)||chr(ascii(substr(lpad(r.mo_code_num + v_count, 4, '0'), 4, 1)) + 17);
--数字替换为A-J，空运时最后俩位均为字母，其他（理论上只有海运）最后一位为字母
if r.transport_method = 'AIR' then
v_mo_code := v_mo_code||substr(lpad(r.mo_code_num + v_count, 4, '0'), 1, 2)
||chr(ascii(substr(lpad(r.mo_code_num + v_count, 4, '0'), 3, 1)) + 17)
||chr(ascii(substr(lpad(r.mo_code_num + v_count, 4, '0'), 4, 1)) + 17);
else
v_mo_code := v_mo_code||substr(lpad(r.mo_code_num + v_count, 4, '0'), 1, 3)
||chr(ascii(substr(lpad(r.mo_code_num + v_count, 4, '0'), 4, 1)) + 17);
end if;
v_once_task := '.init header';
v_msg := 'MO_CODE:'||v_mo_code||',r.to_organization_code:'||r.to_organization_code
||',r.from_organization_code:'||r.from_organization_code||',r.ckd_item_number:'||r.ckd_item_number
||',r.line_code:'||r.line_code;
dwmgr.pkg_iplan_log.write_task_log_once(gv_module_name, v_task_name || v_once_task, v_msg);
INSERT INTO dwmgr.ui_ckd_suggested_mo_header t
(header_id,
plan_version,
from_organization_code,
from_organization_name,
mo_code,
item_number,
item_desc_cn,
item_desc_en,
line_code,
line_name,
to_organization_code,
to_organization_name,
transport_method,
priority,
import_date,
mo_start_date,
mo_end_date,
status,
error_message,
sys_created_date,
sys_created_by,
sys_created_by_name,
sys_last_modified_date,
sys_last_modified_by,
sys_last_modified_by_name)
SELECT dwmgr.seq_ui_ckd_suggested_mo_header.nextval,
t.plan_version,
t.from_organization_code,
t.from_organization_name,
v_mo_code,
t.item_number,
t.item_desc_cn,
t.item_desc_en,
t.line_code,
t.line_name,
t.to_organization_code,
t.to_organization_name,
t.transport_method,
t.priority,
t.import_date,
trunc(sysdate),
trunc(sysdate) + 7,
'ADD',
NULL,
SYSDATE,
t.sys_created_by,
t.sys_created_by_name,
SYSDATE,
t.sys_last_modified_by,
t.sys_last_modified_by_name
FROM (select t.plan_version,
t.from_organization_code,
t.from_organization_name,
t.to_organization_code,
t.to_organization_name,
t.transport_method,
t.priority,
--t.sub_category_code,
max(p.item_number) as item_number, --随机取一个，以防万一
max(p.item_desc_cn) as item_desc_cn,
max(p.item_desc_en) as item_desc_en,
p.line_code,
p.line_name,
t.import_date,
t.sys_created_by,
t.sys_created_by_name,
t.sys_last_modified_by,
t.sys_last_modified_by_name
from dwmgr.tmp_ui_comfirm_ckd_mo t
inner join dwmgr.ui_ckd_category p
on p.to_organization_code = t.to_organization_code
and p.from_organization_code = t.from_organization_code
and p.sub_category_code = t.sub_category_code
and p.enabled_flag = 'Y'
WHERE t.batch_id = p_batch_id
and t.plan_version = p_plan_version
and t.from_organization_code = r.from_organization_code
and t.to_organization_code = r.to_organization_code
and t.transport_method = r.transport_method
and t.priority = r.priority
--and t.ckd_category_code = r.ckd_category_code
and t.ckd_item_number = r.ckd_item_number
and p.line_code = r.line_code
group by t.plan_version,
t.from_organization_code,
t.from_organization_name,
t.to_organization_code,
t.to_organization_name,
t.transport_method,
t.priority,
--t.sub_category_code,
--p.item_desc_cn,
--p.item_desc_en,
p.line_code,
p.line_name,
t.import_date,
t.sys_created_by,
t.sys_created_by_name,
t.sys_last_modified_by,
t.sys_last_modified_by_name) t;
/*v_msg := 'rows=' || SQL%ROWCOUNT;
--COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);*/
<<top>>
null;
for j in (select *
from (select t.batch_id,
t.plan_version,
t.from_organization_code,
t.from_organization_name,
t.to_organization_code,
t.to_organization_name,
t.item_number,
t.item_desc_cn,
t.item_desc_en,
--t.ckd_category_code,
--t.ckd_category_desc,
t.ckd_item_number,
t.ckd_item_desc_cn,
t.ckd_item_desc_en,
t.sub_category_code,
t.sub_category_desc_cn,
t.sub_category_desc_en,
t.spq,
--sum(t.spq) over(partition by t.sub_category_code) as sum_spq,
t.current_spq,
sum(t.current_spq) over(partition by t.sub_category_code, t.transport_method, t.priority) as sum_current_spq,
t.unit,
t.demand_date,
t.import_date,
t.sys_created_by,
t.sys_created_by_name,
t.sys_last_modified_by,
t.sys_last_modified_by_name,
c.max_mo_qty,
t.deduct_spq,
sum(t.deduct_spq) over(partition by t.sub_category_code, t.transport_method, t.priority) as sum_deduct_spq_sub_category,
t.sum_deduct_spq,
nvl(h.qty, 0) as handled_qty --获取本工单号已拆分的数量
from dwmgr.tmp_ui_comfirm_ckd_mo t
inner join dwmgr.ui_ckd_category c
on c.from_organization_code = t.from_organization_code
and c.to_organization_code = t.to_organization_code
and c.sub_category_code = t.sub_category_code
and c.enabled_flag = 'Y' --1496031
left join (select h.item_number as item_number, l.sub_category_code, transport_method, priority, sum(l.qty) as qty
from dwmgr.ui_ckd_suggested_mo_header h
left join dwmgr.ui_ckd_suggested_mo_line l
on l.plan_version = p_plan_version
and l.header_id = h.header_id
where h.plan_version = p_plan_version
and h.mo_code = v_mo_code
group by h.item_number, l.sub_category_code, transport_method, priority) h
on h.item_number = c.item_number
and h.sub_category_code = c.sub_category_code
and h.transport_method = t.transport_method
and h.priority = t.priority
where t.batch_id = p_batch_id
and t.plan_version = p_plan_version
and t.spq > 0 --防呆设置，只拆正数
and t.from_organization_code = r.from_organization_code
and t.to_organization_code = r.to_organization_code
and t.transport_method = r.transport_method
and t.priority = r.priority
--and t.ckd_category_code = r.ckd_category_code
and t.ckd_item_number = r.ckd_item_number)
where sum_deduct_spq_sub_category <> max_mo_qty --正好拆完的不需要再拆
and current_spq > 0
order by sub_category_code, case when handled_qty > 0 then current_spq else ceil(sum_current_spq/max_mo_qty) end desc, sum_current_spq, current_spq desc) loop
v_once_task := '.init line';
v_msg := 'MO_CODE:'||v_mo_code||',v_mo_code:'||v_mo_code||',j.item_number:'||j.item_number||',j.max_mo_qty:'||j.max_mo_qty||',j.current_spq:'||j.current_spq||')';
dwmgr.pkg_iplan_log.write_task_log_once(gv_module_name, v_task_name || v_once_task, v_msg);
INSERT INTO dwmgr.ui_ckd_suggested_mo_line t
(line_id,
header_id,
plan_version,
mo_code,
from_organization_code,
from_organization_name,
item_number,
item_desc_cn,
item_desc_en,
--ckd_category_code,
--ckd_category_desc,
sub_category_code,
sub_category_desc_cn,
sub_category_desc_en,
qty,
unit,
demand_date,
sys_created_date,
sys_created_by,
sys_created_by_name,
sys_last_modified_date,
sys_last_modified_by,
sys_last_modified_by_name)
values
(dwmgr.seq_ui_ckd_suggested_mo_line.nextval,
dwmgr.seq_ui_ckd_suggested_mo_header.currval,
j.plan_version,
v_mo_code,
j.from_organization_code,
j.from_organization_name,
j.item_number,
j.item_desc_cn,
j.item_desc_en,
--j.ckd_category_code,
--j.ckd_category_desc,
j.sub_category_code,
j.sub_category_desc_cn,
j.sub_category_desc_en,
least(j.max_mo_qty - j.handled_qty, j.current_spq),
j.unit,
j.demand_date,
SYSDATE,
j.sys_created_by,
j.sys_created_by_name,
SYSDATE,
j.sys_last_modified_by,
j.sys_last_modified_by_name);
/*v_row_count := SQL%ROWCOUNT;
if v_row_count > 0 then
p_err_code := 0;
else
p_err_code := 1;
end if;*/
/*v_msg := 'rows=' || v_row_count;
--COMMIT;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);*/
update dwmgr.tmp_ui_comfirm_ckd_mo t
set t.current_spq = t.current_spq - least(j.max_mo_qty - j.handled_qty, j.current_spq), --拆分后的剩余量
t.deduct_spq = least(j.max_mo_qty - j.handled_qty, j.current_spq), --本次已拆分的数量
t.sum_deduct_spq = t.sum_deduct_spq + least(j.max_mo_qty - j.handled_qty, j.current_spq) --累计已拆分的数量
where t.batch_id = p_batch_id
and t.plan_version = p_plan_version
and t.from_organization_code = r.from_organization_code
and t.to_organization_code = r.to_organization_code
and t.transport_method = r.transport_method
and t.priority = r.priority
and t.item_number = j.item_number
--and t.ckd_category_code = j.ckd_category_code
and t.ckd_item_number = j.ckd_item_number
and t.sub_category_code = j.sub_category_code;
goto top;
end loop;
--每个工单号全部拆完之后，将已拆分的数量重置，以便进行下一个工单号的拆分
update dwmgr.tmp_ui_comfirm_ckd_mo t
set t.deduct_spq = 0
where t.batch_id = p_batch_id
and t.plan_version = p_plan_version
and t.from_organization_code = r.from_organization_code
and t.to_organization_code = r.to_organization_code
and t.transport_method = r.transport_method
and t.priority = r.priority;
end loop;
end loop;
v_sub_task := '.update run_status.SUCCESS';
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Start',
v_msg);
update dwmgr.tmp_ui_comfirm_ckd_mo t
set t.run_status = 'SUCCESS'
where t.batch_id = p_batch_id
and t.plan_version = p_plan_version;
v_row_count := SQL%ROWCOUNT;
if v_row_count > 0 then
p_err_code := 0;
else
p_err_code := 1;
end if;
v_msg := 'rows=' || v_row_count;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'End',
v_msg);
COMMIT;
v_msg := 'Success';
--p_err_code := 0;
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'End',
v_msg);
exception
when others then
rollback;
update dwmgr.tmp_ui_comfirm_ckd_mo t
set t.run_status = 'FAILED'
where t.batch_id = p_batch_id
and t.plan_version = p_plan_version;
commit;
p_err_code := -1;--sqlcode;
v_msg := substr(dbms_utility.format_error_stack ||
dbms_utility.format_error_backtrace,
1,
2000);
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name || v_sub_task,
'Failed',
v_msg);
dwmgr.pkg_iplan_log.write_task_log(gv_module_name,
v_task_name,
'Failed',
v_msg);
raise_application_error(-20001, v_msg);
END p_confirm_ckd_mo;
END pkg_dw_ui_ckd;
腾讯公司福利 657人发布
投递美团等公司6个岗位 >
