工单变更信息

处理工单信息

处理工单单头

在作业中，查看工单信息，发现单头的信息缺失，这个时候需要先将单头符合条件的信息进行导出。

--工单单头供应商和库位为空
select distinct sfaa010 生产料号,imaal003 品名,imaal004 规格,sfaa034 预计入库库位,sfaa017 部门供应商,sfaadocdt
from sfaa_t
left join imaal_t
on sfaa010 = imaal001
and sfaaent = imaalent
and imaal002 = 'zh_CN'
where 
sfaaent = 88
and sfaasite = 'JHG1'
and sfaadocdt >= DATE '2024-03-26'
and (sfaa034 is null OR sfaa017 is null)
order by sfaa010

筛选出符合条件的数据。

等待对方填充好对应的信息

然后进行更新。

更新工单单头

--创建工单单头
create table abcc as select sfaa010,sfaa034,sfaa017 from sfaa_t where sfaaent=88 and 1=2

--复制数据进去
select * from abcc for update

--更新工单单头
update sfaa_t set sfaa034=(select sfaa034 from abcc where abcc.sfaa010=sfaa_t.sfaa010),
                  sfaa017=(select sfaa017 from abcc where abcc.sfaa010=sfaa_t.sfaa010)
where sfaa010 = (select sfaa010 from abcc where abcc.sfaa010=sfaa_t.sfaa010)
and  sfaaent = 88
and sfaasite = 'JHG1'
and sfaadocdt >= DATE '2024-03-26'
and (sfaa034 is null OR sfaa017 is null)

--删除临时表
drop table abcc

工单单头更新完毕之后，需要更新基础数据

create table abcc as select imaf001,imaf091 from imaf_t where imafent=88 and 1=2

--更新基础资料  imaf091 默认库位，
--              imae101 默认发料库位,imae041 默认入库库位，imae034 部门供应商
update imaf_t set 
                  imaf091=(select imaf091 from abcc where abcc.imaf001=imaf_t.imaf001)
where imaf001 = (select imaf001 from abcc where abcc.imaf001=imaf_t.imaf001 )
and  imafent= 88 and imafsite='JHG1'


create table abcc as select imae001,imae101,imae041,imae034 from imae_t where imaeent=88 and 1=2


update imae_t set imae101=(select imae101 from abcc where abcc.imae001=imae_t.imae001),
                  imae041=(select imae041 from abcc where abcc.imae001=imae_t.imae001),
                  imae034=(select imae034 from abcc where abcc.imae001=imae_t.imae001)
where imae001 = (select imae001 from abcc where abcc.imae001=imae_t.imae001 )
and  imaeent= 88 and imaesite='JHG1'

---

之后再去更新BOM的基础信息。

--bom仓库
create table abcc as select bmba003,bmba015 from bmba_t where bmbaent=88 and 1=2

--bom资料
update bmba_t set 
                  --bmba003 = (select bmba003 from abcc where abcc.bmba003=imaf_t.bmba003 ),
                  bmba015 = (select bmba015 from abcc where abcc.bmba003=bmba_t.bmba003 )
where bmba003 = (select bmba003 from abcc where abcc.bmba003=bmba_t.bmba003 )
and  bmbaent= 88 and bmbasite='JHG1'

--查看是否主键唯一
SELECT imaf001, COUNT(*)
FROM abcc
GROUP BY imaf001
HAVING COUNT(*) > 1;
   
select a.* from abcc a
left join bmba_t i on a.bmba003 = i.bmba003
where i.bmbaent = 88 and bmbasite = 'JHG2'

--清除表
drop table abcc

处理工单单身

--工单单身库位为空

select distinct sfba005 bom料号,imaal003 品名,imaal004 规格,sfba009 倒扣料,sfba019 指定发料仓库,sfaadocdt
from sfba_t
left join imaal_t
on   imaalent = sfbaent
and  imaal001 = sfba005
and   imaal002 = 'zh_CN'

left join sfaa_t
on sfaadocno = sfbadocno
and sfaaent = sfbaent

--where sfbaent = 88
--and imaal003 = '铝管管材'
--and sfba009 != 'Y'
where sfbaent = 88
and   sfbasite = 'JHG1'
and   (sfba019 = ' ' OR sfba019 is null)
--and   sfbadocno >= 'DMO-24030830'
and   sfaadocdt >= DATE '2024-03-26'
order by sfba005

更新工单单身

--创建工单单身
create table abcc as select sfba005,sfba019 from sfba_t where sfbaent=88 and 1=2




--更新工单单身
update sfba_t set sfba019=(select sfba019 from abcc where abcc.sfba005=sfba_t.sfba005 )
where sfba005 = (select sfba005 from abcc where abcc.sfba005=sfba_t.sfba005 )
and  sfbaent = 88
and   sfbasite = 'JHG1'
and   (sfba019 = ' ' OR sfba019 is null)
--and   (sfba019 = 'PN1' OR sfba019 = 'PN5')
--and   sfbadocno >= 'DMO-24030830'

--工单单身更新完毕之后，同理更新基础资料以及bom资料