出站_完工下线

出站_完工下线fs

1.打开子应用后光标定位到设备码文本框,扫设备码,获取资源对象resrce对象所有属性,查询条件为RESRCE属性=扫码值

2. 若步骤1实体ENABLED =false 则报错"1028:资源xxx已失效请联系it",若ALLOW_SCANNING=false,报错:" 10031:设备不允许扫码,请联系IT部门注册设备"

3. 使用第一步获取的OPERATION属性到 operation表查询 描述DESCRIPTION

select operation || ':' || description from imes.operation x where operation = 'YG039' /*替换为画面工序*/ 显示于 画面

3.1 获取产线信息

select x.* from work_center x where work_center = '11300101' /*替换为画面产线*/

3.2获取 site 对象

select x.* from imes.site x where site = '1130' /*替换为当前工厂*/

4.画面 agv 预设值 = 步骤3.2 获取的 agv字段

到此步骤无报错 则 显示 设备名称 工序 和工序编码

5.点工单查询

V15-1 start

with a as

(select distinct x.shop_order

from imes.shop_order x , routing r

where status = '501' and r.shop_order = x.shop_order

and r.work_center = '11300102' /*替换为产线*/

and qty_ordered > nvl(qty_done, 0)),

b as

(select distinct shop_order, erp_control_key_group

from item_order_bind_new io

where io.work_center = '11300102' /*替换为产线*/

and io.erp_control_key_group = 1

and exists (select 1 from a where io.shop_order = a.shop_order)),

t1 as /*非首段工序找工单按在制进站数*/

(select shop_order, erp_control_key_group

from shop_order_manufacture_report t

where exists (select 1

from shop_order s

where s.status = '501'

and s.shop_order = t.shop_order)

and t.work_center = '11300102' /*替换为产线*/

and erp_control_key_group > 1

v42start and ( IN_OUT = -1 or (IN_OUT = 1 and t. CREATED_DATE_TIME<sysdate-8/1440 ) ) v42end

group by shop_order, erp_control_key_group

having sum(in_out * qty) > 0),

c as

(select r.shop_order

,s.planned_item

,i.description itemdesc

,s.qty_ordered

,s.qty_done

,work_center

,operation

,erp_step

,r.erp_control_key_group

,r.erp_control_key

from (select *

from b

union

select * from t1) b

,routing r

,shop_order s

,item i

where r.shop_order = b.shop_order

and r.erp_control_key_group = b.erp_control_key_group

and r.erp_control_key = 'SP01'

and s.shop_order = r.shop_order

and i.item = s.planned_item

and i.site = r.site)

select * from c where operation = 'YG014' /*替换为当前工序*/

v15-1 end

v21 start

废除 v15-1 找工单sql,以下面的替换

with a as

(select distinct x.shop_order

from imes.shop_order x, routing r

where status = '501'

and r.shop_order = x.shop_order

and r.work_center = '11300102' /*替换为产线*/

),

b as

(select distinct shop_order, erp_control_key_group

from item_order_bind_new io

where io.work_center = '11300102' /*替换为产线*/

and io.erp_control_key_group = 1

and exists (select 1 from a where io.shop_order = a.shop_order)),

t1 as

(select shop_order, erp_control_key_group

from shop_order_manufacture_report t

where exists (select 1

from shop_order s

where s.status = '501'

and s.shop_order = t.shop_order)

and t.work_center = '11300102' /*替换为产线*/

and erp_control_key_group > 1

group by shop_order, erp_control_key_group

having sum(in_out * qty) > 0),

t2 as

(select shop_order, 1 erp_control_key_group

from imes.shop_order x

where planned_work_center = '11300601' /*替换为产线*/

and scanitem = 0

and qty_ordered > nvl(x.qty_done, 0)

and status = '501'),

c as

(select r.shop_order, s.planned_item, i.description itemdesc, s.qty_ordered, s.qty_done, work_center, operation, erp_step, r.erp_control_key_group, r.erp_control_key

from (select *

from b

union

select *

from t1

union

select * from t2) b, routing r, shop_order s, item i

where r.shop_order = b.shop_order

and r.erp_control_key_group = b.erp_control_key_group

and r.erp_control_key = 'SP01'

and s.shop_order = r.shop_order

and i.item = s.planned_item

and i.site = r.site)

select * from c where operation = 'YG014' /*替换为当前工序*/

v21 end

V40start

V40end

查询到的数据显示到 lov ,用户选择的时候判断 当前工序是否= operation 不等于则报错 "1179所选工单在当前工序不可出站"

如果 一致 则 物料号 描述 显示于画面 erp_control_key_group 记录于画面变量

V32 start

select item

,lot_num_rule

,work_center

from imes.lot_number_rule x

where item = '1000020772' /*替换为头物料*/

and enabled = 1

AND LOT_NUM_RULE = 'B'

只有在 返回行数>0 才执行 v20 start

V32end

V42 备注 最新的找工单SQL 20240122 start

with a as

(select distinct x.shop_order

from shop_order x, routing r

where status = '501'

and r.shop_order = x.shop_order

and r.work_center = '11300105'),

b as

(select distinct shop_order, erp_control_key_group

from item_order_bind_new io

where io.work_center = '11300105'

and io.erp_control_key_group = 1

and exists (select 1 from a where io.shop_order = a.shop_order)),

t1 as

(select shop_order, erp_control_key_group

from shop_order_manufacture_report t

where exists (select 1

from shop_order s

where s.status = '501'

and s.shop_order = t.shop_order)

and t.work_center = '11300105'

v42start and (t.in_out = -1 or (t.in_out = 1 and t.created_date_time < sysdate - 8 / 1440)) v42end

and erp_control_key_group > 1

group by shop_order, erp_control_key_group

having sum(in_out * qty) > 0),

t2 as

(select shop_order, 1 erp_control_key_group

from shop_order x

where planned_work_center = '11300105'

and scanitem = 0

and qty_ordered > nvl(x.qty_done, 0)

and status = '501'),

c as

(select r.shop_order

,s.planned_item

,i.description itemdesc

,s.qty_ordered

,s.qty_done

,work_center

,operation

,erp_step

,r.erp_control_key_group

,r.erp_control_key

from (select *

from b

union

select *

from t1

union

select * from t2) b

,routing r

,shop_order s

,item i

where r.shop_order = b.shop_order

and r.erp_control_key_group = b.erp_control_key_group

and r.erp_control_key = 'SP01'

and s.shop_order = r.shop_order

and i.item = s.planned_item

and i.site = r.site)

select * from c where operation = 'YG027'

V42 备注 最新的找工单SQL 20240122 end

V20 start

选定工单号 执行

select i.scan_check, x.component_gbo

from imes.bom x, shop_order s, item i

where x.shop_order = '232400003910' /*需替换*/

and s.shop_order = x.shop_order

and exists (select 1

from lot_number_rule c

where c.item = s.planned_item

and c.enabled = 1)

and exists (select 1

from lot_number_rule c

where c.item = x.component_gbo v30: and (lot_num_rule = 'A' or lot_num_rule = 'B')

and c.enabled = 1)

and i.item = x.component_gbo

and i.site = x.site

若行数>1 则报错 1197资料错误 ,发现多个需要选批次的料号

若行数=1 则 缓存 v22: component_gbo 到画面变量 并继续查询

如果 scan_check =0 sql:

V25.1 替换后的sql: with a as

(select i.scan_check, x.component_gbo, x.wbs, x.sales_order, x.so_line, r.operation, x.site, r.work_center

from imes.bom x, shop_order s, item i, routing r

where x.shop_order = '232400003910' /*需替换*/

and s.shop_order = x.shop_order

and r.shop_order = x.shop_order

and r.erp_step = x.erp_step

and exists (select 1

from lot_number_rule c

where c.item = s.planned_item

and c.enabled = 1)

and exists (select 1

from lot_number_rule c

where c.item = x.component_gbo v30: and (lot_num_rule = 'A' or lot_num_rule = 'B')

and c.enabled = 1)

and i.item = x.component_gbo

and i.site = x.site),

b as

(select inv.lot_number, inv.qty_on_hand, inv.warehouse, inv.inventory_type, inv.inventory_status, inv.inventory

from inventory inv, a

where inv.site = a.site

and inv.item = a.component_gbo

and inv.lot_number is not null

and inv.qty_on_hand > 0

and inv.enabled = 1

and inv.inventory_status = '102'

and inv.inventory_type = '122'

and nvl(inv.so_num, ' ') = nvl(a.sales_order, ' ')

and nvl(inv.so_line_num, 0) = nvl(a.so_line, 0)

and nvl(inv.wbs_num, ' ') = nvl(a.wbs, ' ')

and inv.warehouse in (select warehouse

from operation_warehouse tt

where tt.work_center = a.work_center

and tt.operation = a.operation))

select lot_number, sum(qty_on_hand) qty from b group by lot_number order by 2

如果 scan_check=1 sql:

V25.2 替换后的sql: with a as

(select i.scan_check, x.component_gbo, x.wbs, x.so_line, x.sales_order

from imes.bom x, shop_order s, item i

where x.shop_order = '232400003910' /*需替换*/

and s.shop_order = x.shop_order

and exists (select 1

from lot_number_rule c

where c.item = s.planned_item

and c.enabled = 1)

and exists (select 1

from lot_number_rule c

where c.item = x.component_gbo v30: and (lot_num_rule = 'A' or lot_num_rule = 'B')

and c.enabled = 1)

and i.item = x.component_gbo

and i.site = x.site),

b as

(select inv.lot_number, inv.qty_on_hand, t.created_date_time bindtime, t.key, t.operation, t.item, t.so_line_num, t.so_num, t.wbs_num, max(key) over(partition by t.operation, t.item, t.so_line_num, t.so_num, t.wbs_num) maxkey

from item_resource_bind_new t, inventory inv, a

where inv.inventory = t.inventory

and inv.site = t.site

and t.item = a.component_gbo

and inv.qty_on_hand > 0

and inv.enabled = 1

and inv.inventory_status = '102'

and inv.inventory_type = '122'

and nvl(t.so_num, ' ') = nvl(a.sales_order, ' ')

and nvl(t.so_line_num, 0) = nvl(a.so_line, 0)

and nvl(t.wbs_num, ' ') = nvl(a.wbs, ' ')

and exists (select 1

from item_order_bind_new tt

where tt.shop_order = '232400003910' /*需替换*/

and tt.bindkey = t.key)

and t.work_center = '11300110' /*需替换*/

)

select lot_number, sum(qty_on_hand) qty from b where maxkey = key group by lot_number

然后弹出数据 批次号 数量 等待用户选择

用户选择后 记录于画面缓存 LOT_NUMBER    maxoutQTY

在后面输入 下线数量的时候 前端判断 如果输入数量> maxoutQTY 则报错 "下线数量>批次可出库量"

点确定按钮的时候 需要传入 LOT_NUMBER 到后端

V20 end

V22start: 选择工单后

select con.convertion_data

from shop_order x, item i, item_group_pro_convertion con

where shop_order = '232400003210' /*需替换选择的工单*/

and i.item = x.planned_item

and i.site = x.site

and con.work_center = x.planned_work_center

and con.operation = 'YG005' /*需替换画面工序*/

and con.item_group = i.category3

显示于 画面 系数文本框

点确定按钮后 发送到后端

V22end

6. 判断末工序否

6.1查询最大工序序号

select max(sequence) sequence from routing x where shop_order = '210700607400' /*替换为所选工单号*/

6.2 查询本工序的工序序号

select sequence, description, work_center, erp_step, erp_control_key,末工序否

from routing x

where shop_order = '220600556710' /*替换为工单号*/

and operation = 'YG039' /*替换为画面工序*/

and erp_control_key_group = 1 /*替换为选择的erp_control_key_group*/

如果本查询 有多行记录 则报错 "1180系统不支持重复工序"

只有一行记录 则 判断步骤6.1 SEQUENCE 和步骤6.2 SEQUENCE, 相同则表示为末工序 , 否则为 中间工序

本sql查询的所有字段 缓存于页面

当判断为 末工序时候 则画面 入库否 和agv 才显示 否则 值均设置为 否 并且隐藏

7.扫描容器码

容器平台查询

http://10.30.35.83:8088/services/containerQueryWebService?wsdl

{
    "code":"0",
    "data":[
        {
            "containerCode":"",
            "level":0,
            "levelSpecified":true,
            "loadObjectCode":"WMBI00369",
            "loadObjectType":"CONTAINER",
            "path":"WMBI00369",
            "qty":null
        },
        {
            "containerCode":"WMBI00369",
            "level":1,
            "levelSpecified":true,
            "loadObjectCode":"SPBZ05710",
            "loadObjectType":"CONTAINER",
            "path":"WMBI00369\SPBZ05710",
            "qty":null
        },
        {
            "containerCode":"SPBZ05710",
            "level":2,
            "levelSpecified":true,
            "loadObjectCode":"1000043191103902A2211250022",
            "loadObjectType":"MATERIAL_LOT",
            "path":"WMBI00369\SPBZ05710\1000043191103902A2211250022",
            "qty":"0"
        }
    ],
    "message":"SUCCESS",
    "status":true,
    "statusSpecified":true
}

从返回 集合中 取出 "loadObjectType"="MATERIAL_LOT", 所有的 loadObjectCode ,

查询每个条码的信息

select enabled, inventory_status, inventory_type, x.source_shop_order, x.item

from inventory x

where site = '1130' /*替换为当前工厂*/

and inventory = '100002750110393202110220016113' /*替换为条码 loadObjectCode */

如果是末工序 inventory_type<>121 则报错 1086容器内条码非下线条码

如果是中间工序 inventory_type<>123 则报错 1105容器内条码非在制条码

若步骤3.1获取的混装参数 MERGE_LOAD =1 并且source_shop_order<>画面工单号 则报错 1107不允许不同工单混装

若步骤3.1获取的混装参数 MERGE_LOAD =2 并且 item <> 画面料号 则报错" 1083不允许不同物料混装"

8.输入 下线数量

9.点确定按钮 执行9.1-9.x的check

V39 start

SELECT STEEL_SEAL_NUM

FROM SHOP_ORDER x

WHERE SHOP_ORDER = '232800703010'/*需替换*/

and STEEL_SEAL_NUM IS NOT NULL

如果返回行数>0 则 继续判断有无钢板印领用记录

SELECT count(*) FROM SHOP_ORDER_STEEL x

WHERE SHOP_ORDER = '232200001010'/*需替换*/

如果返回 0 则报错 1225没有领用钢板印记录,不可下线

检查有无领用钢板印

SELECT sos.SHOP_ORDER

FROM SHOP_ORDER_STEEL sos

JOIN SHOP_ORDER so

ON sos.SITE = so.SITE

AND sos.SHOP_ORDER = so.SHOP_ORDER

WHERE sos.SHOP_ORDER = '232300759610' /*需替换*/

AND so.STEEL_SEAL_NUM IS NOT NULL

返回行数0 则报错 1225没有领用钢板印记录,不可下线

V39 end

V41start

判断是否包含橡皮印物料

select b.component_gbo 橡皮印料号

from bom b, item t

where b.shop_order = '232300618310' /*需替换*/

and t.category3 = 'O0002'

and t.item = b.component_gbo

and t.site = b.site

AND NOT EXISTS (SELECT 1 FROM ROUTING r WHERE r.shop_order=b.SHOP_ORDER AND r.WORK_CENTER<>'11300101')

如果返回行数>0 则 继续判断有无橡皮印投料记录

select 1

from inventory_transaction inv

where inv.transaction_reason_code = '261' and inv. CREATED_DATE_TIME<sysdate-10/1440

and shop_order = '232300618310' /*需替换*/

and inv.item= 橡皮印料号 /*需替换*/

返回行数0 则报错 1252 bom有橡皮印物料,无10分钟前投料记录,不可下线

V41end

V35start

出站完工下线  点确认 按钮 

select x.container_code
from shop_order_manufacture_report x
where work_center = '11300328' /*需替换为画面产线*/
      and operation = 'BZ019' /*需替换为画面工序*/
      and container_code <> 'WMA100044A' /*需替换为画面容器*/
      and x.created_date_time >= sysdate - 25 /*需替换为sysset.OUTTIME*/
      / 1440
      and in_out = -1
      and container_code is not null

返回行数>0 则报错  1208和上托盘出站间隔小于%s分钟

V35end

V16-1 start

9.1 如果 画面 erp_control_key_group >1 则

select sum(in_out * qty)

from shop_order_manufacture_report t

where exists (select 1

from shop_order s

where s.status = '501'

and s.shop_order = t.shop_order)

and t.work_center = '11300102' /*替换为产线*/

and erp_control_key_group = 2 /*替换为选择的erp_control_key_group*/

and t.shop_order = '232400001110' /*替换为当前工单*/

group by shop_order, erp_control_key_group

如果返回的数据< 画面下线数量 则报错 1184 输入的出站数量过大,剩余可出站数量%s,

V16-1 end

V43 start 点确定按钮后

v43.1 执行 班次检查

最终执行的sql如下:

SELECT count(1) FROM class_record_master m JOIN class_record_line c ON m.id = c.master_id WHERE m.work_center = '11200403' AND c.user_id = '20170311' AND m.end_time IS NULL AND m.start_time > sysdate - 19 / 24

下方会抛异常 1236 外套条件判断

 if site.systemerr=0  then   

抛异常  1236

end if

v43.2执行 质检单处理完毕检查

实际执行的sql

SELECT TASK_ORDER FROM QM_TASK_MASTER qtm WHERE qtm.TASK_STATUS IN ('560', '561') AND NVL(NVL(qtm.RE_TASK_RESULT, qtm.TASK_RESULT), 0) <> 1 AND qtm.SITE = '1130' AND qtm.SHOP_ORDER = '242100431110' AND EXISTS (SELECT * FROM QM_BASIC_DATA qbd WHERE qbd.SITE = qtm.SITE AND qbd.WORK_CENTER = qtm.WORK_CENTER AND qbd.TASK_TYPE = qtm.TASK_TYPE AND qbd.TEST_POST = qtm.TEST_POST AND qbd.CONTROL = 1)

下方会抛异常 1196 外套条件判断

 if site.systemerr=0  then   

抛异常  1196    zh_CN    存在质检单%s未处理,请到质检任务平台对该单据做完成或关闭

end if

v43.3 系统异常情况下 跳过异常 未找到工单232900558310未关闭的FQC，请先生成FQC(1193)

if site.systemerr=0  then   

抛异常   1193

end if

v43.4 系统异常情况下 跳过异常 未找到工单232900558310未关闭的FQC，请先生成FQC(1193)

if site.systemerr=0  then   

抛异常  1193

end if

v43 end

V17 start

9.2

SELECT COUNT(*)  FROM QM_TASK_MASTER qtm 
WHERE  qtm.TASK_STATUS IN ('560','561')
AND NVL(NVL(qtm.RE_TASK_RESULT,qtm.TASK_RESULT),0) <> 1 
AND qtm.SITE = '1130' /*替换为工厂*/
AND qtm.SHOP_ORDER = '220600411510' /*替换为下线工单*/
AND EXISTS (SELECT * FROM QM_BASIC_DATA qbd WHERE qbd.SITE = qtm.SITE AND qbd.WORK_CENTER = qtm.WORK_CENTER AND qbd.TASK_TYPE = qtm.TASK_TYPE AND qbd.TEST_POST = qtm.TEST_POST AND qbd.CONTROL = 1)

返回 >0 则报错 "1196存在质检单未处理"

V17 end

V26 start

设置警报数量 最大超入数量

V42start

超入规则 抓取1 start

select x.qty_ordered 警报数量, x.qty_ordered * m.proportion 最大超入数量

from shop_order x

join multistep_excess_rules m on m.work_center = x.planned_work_center

and x.qty_ordered between lower_limit and upper_limit

where shop_order = '233200659010' /*需替换为工单号*/

and m.proportion > 0

and m.enabled = 1

超入规则 抓取1 end

如果 返回0行 则 执行 超入规则抓取 2 (物料 物料组 产线 等超入规则抓取)

返回1 行 则 不执行超入规则抓取 2

V42end

超入规则抓取 2 start

--物料

select warning_qty, max_qty

from imes.multistep_excess_rules x

where material_object_type = 'MATERIAL'

and material_object_code = '1000016875' /*需替换*/

and work_center = '11300101' /*需替换*/

and 5 /*需替换*/

between lower_limit and upper_limit;

如果行数=1 则 警报数量 = warning_qty 最大超入数量 = max_qty 并且不在继续抓取超量设置

如果行数=0 则 抓取物料组 超入率

--物料组

select warning_qty, max_qty

from imes.multistep_excess_rules x

where work_center = '11300401' /*需替换*/

and material_object_type = 'CATEGORY'

and material_object_code = 'R0037' /*需替换*/

and 5 between lower_limit and upper_limit;

如果行数=1 则 警报数量 = warning_qty 最大超入数量 = max_qty 并且不在继续抓取超量设置

如果行数=0 则 抓取产线超入率

--产线

select warning_qty, max_qty

from imes.multistep_excess_rules x

where work_center = '11300102' /*需替换*/

and 5 between lower_limit and upper_limit;

如果行数=0 则 警报数量 = 0 最大超入数量 = 0 跳出

如果行数=1 则 警报数量 = warning_qty 最大超入数量 = max_qty 并且不在继续抓取超量设置

超入规则抓取 2 end

已出站数量 = select sum(qty) qty

From shop_order_manufacture_report x

where shop_order = '232400011410' 需替换

and erp_control_key_group = 1 需替换

and in_out = -1

工单计划量 = SELECT QTY_ORDERED FROM SHOP_ORDER x WHERE SHOP_ORDER = '220600295800'

如果 警报数量>0 则 检查

1. 已出站数量3000+ 输入数量10518 >工单量+最大超入数量 则报错 1198超过允许超入量%s
2. 已出站数量+ 输入数量 >工单量 and 已出站数量+ 输入数量 < 工单量+警报数量 提示" 1199已超过工单计划量,小于警报数量,是否继续入库?" 选是 继续往下走 否则退出过程
3. 已出站数量1702+ 输入数量947 >工单量 and 已出站数量1702+ 输入数量927 > 工单量1648+警报数量50 and 已出站数量1702+ 输入数量947 > 工单量1608+最大超入数量1000

提示" 1200已超过工单计划量,大于警报数量,是否继续入库?" 选是 继续往下走 否则退出过程

V26end

10. 非末工序 则 生成在制条码. 在制条码生成规则

select nvl(max(to_number(regexp_substr(inventory_bo, '[^-]+', 1, 4))), 0) + 1

from imes.shop_order_manufacture_report x

where in_out = '-1'

and shop_order = '220600554510'

and erp_control_key_group = 1

最终 在制条码格式为 220600554510-1/3-YG039-0001

220600554510 当前工单号

1 是 erp_control_key_group

3 是 select max(erp_control_key_group) from routing r where shop_order = '220600554510'

YG039 提取 select operation

from routing r

where shop_order = '220600554510'

and sequence = (select max(sequence)

from routing r

where shop_order = '220600554510'

and erp_control_key_group = 1)

0001 最上语句生成 拼接为4位长度

V16-2 start

末工序 则生成完工条码 规则 执行 语句

/*下线条码生成*/

select max(substr('0000' || to_char(to_number(regexp_substr(inventory_bo, '[^-]+', 1, 2)) + 1), -4))

from shop_order_manufacture_report

where inv_type = 'F'

and shop_order = '222700094510' /*替换为工单号*/

如果返回行数0 则 条码编号= '220600556710-0001'

如果返回行数>0 则条码编号 = inventory_bo

V16-2 end

v37 出站卡配方 start

不管末工序还是 中间工序 均执行以下check

select sum(request_formula) request_formula

from wc_operation_section x, routing x1, routing x2

where x2.shop_order = '232700000910' /*需替换*/

and x2.operation = 'ZP009' /*需替换*/

and x2.shop_order = x1.shop_order

and x1.erp_control_key_group = x2.erp_control_key_group

and x1.work_center = x.work_center and x1.operation = x.operation

如果返回0 则 跳出逻辑

如果返回>0 则 继续执行check

SELECT count(*) 配方单个数 FROM SHOP_ORDER_SLURRY x

WHERE SHOP_ORDER = '232700000910'/*需替换*/

如果 配方单个数=0 则报错 "1223系统配置工单出站需要配方单,实际没有做关联"

否则 继续执行流程

v37 出站卡配方 end

11.写条码表-在制 inventory

如果上述语句返回行数>0 则 新在制条码= 220600554510-1/3-YG039-0001 下线条码 220600554510-0001

12工单出站记录 写报工接口表 shop_order_manufacture_report

13.1 v16 -5 如果非末工序 或者末工序 但是画面 生成入库=false 则 对 条码和容器 调用 容器平台进行绑定

http://10.30.35.155:8088/services/containerIfaceService?wsdl

对象类型：①EO 在制 非工序 v16
        ②CONTAINER 容器
        ③MATERIAL_LOT 条码 末工序 v16

V19 start

如果是末工序 新增写表 INVENTORY_TRANSACTION

V19 end

V16-6 start

13.2.1 如果末工序并且 画面入库申请=true 则 先写me入库表 并且 调用wms 接口生成入库单

插表 INSTRUCTION_MASTER

插表INSTRUCTION_LINE

13.2.2调用 入库单接口推送到wms：
http://10.30.35.83:8088/services/productApplicationWebService?wsdl
(有单据头有条码明细，则是正常提交单据和生成条码；没有单据头只有条码明细，则只生成条码)

V31:新增传递 INVENTORY. SO_LINE_NUM SO_NUM WBS_NUM .3个 字段 到wms

13.2.3 更新工单完工数量Update SHOP_ORDER x set x. QTY_DONE=nvl(QTY_DONE,0)+下线数量 WHERE SHOP_ORDER = '220600295800'

V16-6 end

V28start 末工序执行

select QTY_ORDERED

from item_group x, item i, shop_order s

where auto_close_order = '1'

and i.category3 = x.item_group

and item = s.planned_item

and i.site = s.site

and s.shop_order = '220600473910' 需替换

如果返回0行 则啥也不做

返回1 行 则 查询工单已出站数= select sum(qty) from SHOP_ORDER_MANUFACTURE_REPORT where SHOP_ORDER = '232400002110' 需替换AND IN_OUT = -1 and ERP_CONTROL_KEY_GROUP=2 需替换

工单已出站数>= 工单计划量 则 更新 Update SHOP_ORDER x set STATUS='503' WHERE SHOP_ORDER = '220600295800'

还需要发送 状态到nps

测试环境：http://poqas01.super.local:50000/dir/wsdl?p=ic/27b2e2f813cd366b99e2ba82a9c2d3df

入参 工单号 , F

生产环境：http://poprd01.super.local:50000/dir/wsdl?p=ic/cdd2577b44c33d94beb6081fb28efc32

V28end

V29start

select template_describe, template_file_name, printer_name, DEFAULT_PRINTER from print_template_info x where work_center = '11300102'

返回行数>0 则 确定按钮 变成 确定并打印

返回行数>1 则判断 DEFAULT_PRINTER 是否=1 直接选择DEFAULT_PRINTER=1的这一行的template_file_name

如果都设置 DEFAULT_PRINTER=0 DEFAULT_PRINTER=1 则 弹出lov 用户选择后 填充下面的打印服务的入库参数

点确定并打印 则 调用接口打印

http://10.30.35.116:8080/imes-print/v1/12/inventory-print/print

报文

[
    {
        "inventory": "222700098410-0006", 条码号
        "container": "MELED2091", 容器
        "site": "1130", 工厂
        "printType":"glassTag"
    }
]

V29end

14 本批批次生成

15.1 SWR_NUM 生成 null

15.2 wbs生成 默认 null

16.

V16-7 99. 本批批次规则 此处细节步骤从0开始编号

0.批次变量默认=20230301 4位年 2位月 2位日

1.获取本批批次 规则

select item

,lot_num_rule

,work_center

from imes.lot_number_rule x

where item = '1000020772' /*替换为头物料*/

and enabled = 1

返回行数0 则 返回默认批次变量

2.获取到行数=1 则 判断lot_num_rule值

2.1 A : 批次变量= 年最后一位 + 两位月 + 两位日期 + 产线编号 + 工单的规格顺序

产线编号 从值集 ME.Z.LOT_NUM_PRODLINE 过滤后取值 查询条件为 当前产线

工单的规则顺序 数据提取

with a as

(select id

,created_date_time

,item

,lead(item) over(order by created_date_time) n

,lag(item) over(order by created_date_time) p

from imes.shop_order_manufacture_report x

where inv_type = 'F'

and created_date_time >= trunc(sysdate) /* - 30*/

and in_out = -1

v34: and exists (SELECT 1 FROM IMES.LOT_NUMBER_RULE lot WHERE lot.LOT_NUM_RULE = 'A' and lot.ITEM = x.item)

and work_center = '11300201' /*替换为当前产线*/

order by created_date_time),

b as

(select id

,created_date_time

,item

from a

where p <> item

or p is null

or n = null)

select b.*

,row_number() over(order by created_date_time) 切换顺序

from b

返回行数0 则 设置值=1

返回行数>0 取最后一行 的 item 和当前下线料号比较,一致则 设置值=切换顺序

返回行数>0 取最后一行 的 item 和当前下线料号比较,不一致则 设置值=切换顺序+1

循环返回的记录集 对比 item和当前物料 首个一致的 行 切换顺序作为 设置值

如果没有找到相同的记录设置值=最大切换顺序+1

V34: 如果生成的批次超过9 如10则 又从1 开始

2.2 B: 后续提供 按默认值返回

with a as

(select i.scan_check, x.component_gbo, x.wbs, x.so_line, x.sales_order, s.planned_work_center work_center

from bom x, shop_order s, item i

where x.shop_order = '232900113410'

and s.shop_order = x.shop_order

and exists (select 1

from lot_number_rule c

where c.item = s.planned_item

and c.enabled = 1)

and exists (select 1

from lot_number_rule c

where c.item = x.component_gbo

and (lot_num_rule = 'A' or lot_num_rule = 'B')

and c.enabled = 1)

and i.item = x.component_gbo

and i.site = x.site),

b as

(select t.inventory

,inv.lot_number

,inv.qty_on_hand

,t.created_date_time bindtime

,t.key

,t.operation

,t.item

,t.so_line_num

,t.so_num

,t.wbs_num

,max(key) over(partition by t.operation, t.item, t.so_line_num, t.so_num, t.wbs_num) maxkey

from item_resource_bind_new t, inventory inv, a

where inv.inventory = t.inventory

and inv.site = t.site

and t.item = a.component_gbo

and inv.qty_on_hand > 0

and inv.enabled = 1

and inv.inventory_status = '102'

and inv.inventory_type = '122'

and nvl(t.so_num, ' ') = nvl(a.sales_order, ' ')

and nvl(t.so_line_num, 0) = nvl(a.so_line, 0)

and nvl(t.wbs_num, ' ') = nvl(a.wbs, ' ')

and exists (select 1

from item_order_bind_new tt

where tt.work_center = a.work_center

and tt.bindkey = t.key)

and t.work_center = '11300112')

select lot_number, sum(qty_on_hand) maxoutqty from b where maxkey = key group by lot_number

V23-1 start: 继承 画面选择的 批次号

V23-1 end

V36start

针对c和d类 加入如下规则

SELECT x.*

FROM IMES.LOT_NUMBER_RULE x

WHERE ITEM = '1000106531'

AND (LOT_NUM_RULE = 'C' or LOT_NUM_RULE = 'D')

返回行数>0 则 不执行 2.3 2.4的批次生成规则

出站批次号 灰色 只读  后面带个编辑按钮  点按钮 弹出对话框    对话框确定点击后  对话框 的内容(可空白 直接点确定)  去掉收尾空格  写入 出站批次号文本框  

点确定后 输入的批次号 则替代 2.3 /2.4 生成的结果

V36end

2.3 C: 批次变量= 年最后一位 + 两位月 + 两位日期 + 产线编号 + 工单的规格顺序

产线编号 从1 中取到的 work_center

工单的规则顺序 数据提取

with a as

(select id

,created_date_time

,item

,lead(item) over(order by created_date_time) n

,lag(item) over(order by created_date_time) p

from imes.shop_order_manufacture_report x

where inv_type = 'F'

and created_date_time >= trunc(sysdate) /* - 30*/

and in_out = -1

and work_center = '11300201' /*替换为当前产线*/

v33: and exists (SELECT 1 FROM IMES.LOT_NUMBER_RULE lot WHERE lot.LOT_NUM_RULE = 'C' and lot.ITEM = x.item)

order by created_date_time),

b as

(select id

,created_date_time

,item

from a

where p <> item

or p is null

or n = null)

select b.*

,row_number() over(order by created_date_time) 切换顺序

from b

返回行数0 则 设置值=1

返回行数>0 取最后一行 的 item 和当前下线料号比较,一致则 设置值=切换顺序

返回行数>0 取最后一行 的 item 和当前下线料号比较,不一致则 设置值=切换顺序+1

循环返回的记录集 对比 item和当前物料 首个一致的 行sn 作为 设置值

如果没有找到相同的记录设置值=最大sn +1

2.4 D: 批次变量= 年后2位 + 2位月 + 2位日

2.5 E: 批次变量= 年4位 + 2位月 + 2位日 + 白夜班 (1白班 2夜班) + GU9(固定字符)

SELECT CLASSES FROM IMES.CLASS_RECORD_MASTER x WHERE WORK_CENTER = '11300424' and END_TIME is NULL

CLASSES = A : 白夜班 =1 ;

CLASSES = C : 白夜班=2

2.6 F: 批次变量= 前缀 + 流水码

前缀规则: 按头物料

1000017773：批次SP-981-68-000

1000017772：批次SP-981-128-000

1000017775：批次SP-981-128-山蒲/Φ2.0-000 

1000017774：批次1322-500-37504-PHILIPS/Φ2.0-000

流水码 : 基准数字 + 工单开工次序

基准数字 维护在 Z_LOT_NUMBER_ASSY_DATA表

工单开工次序:

with a as

(select shop_order, min(created_date_time) mincreated_date_time

from imes.shop_order_manufacture_report x

where item = '1000101731' /*替换为头物料*/

group by shop_order)

select a.*, row_number() over(order by mincreated_date_time) sn from a

返回行数0 则 开工次序=1

返回行数>0 则过滤返回记录集 x=>x.shop_order==画面工单 得到行数 =1 则开工次序=sn

的到行数=0 则开工次序=max(sn)+1

2.7 G: 批次变量=产线编号 +年4位 + 2位月 + 白夜班 (白班=日期*2-1 夜班=日期*2)

产线编号 : 从值集 ME.Z.LOT_NUM_PRODLINE 过滤后取值 查询条件为 当前产线

白夜班: eg: 4月1日 1*2-1 前面补0 变为2位 01 (白班默认值)

4/1夜班=1*2 面补0 变为2位 02

4月8日 8*2-1 前面补0 变为2位 15 (白班默认值)

4/8夜班=8*2 面补0 变为2位 16

时间在 当日 8:00前 属于 前一天夜班 8点后属于本日白班 , 4/1 9:00 白夜班=01 4/8 7:00白夜班=14