VX群里说没有物料的解决方法

1、根据用户提供的工单号查询出对应的记录

SELECT A.WO,A.DEVICE,B.*

FROM MES_WPC_WO A

LEFT JOIN AOI_WO_BOM B ON A.WPC_WO_SID = B.WPC_WO_SID

--where device = '341701000747'

where wo = '420000281'

 

2、根据群里面提供的 WO和料号的ID，分别添加，SQL语句如下（数据库为133）

insert into AOI_WO_BOM (WO_BOM_SID, WPC_WO_SID,STLNR,STLAN,RSPOS,MATNR,MAKTX,BDMNG,MEINS,KEYCOMP,STORAGE) 

values

('A2020091008331413927','A2020082610350115354','00069196','A','0037','141812000054','H T_RC_6D_TFC_001','0.00','ST','N','3210')

插入的数据并非为定值，需要根据实际情况插入。

其中WO_BOM_SID栏位获取的方式为：

DECLARE @SID VARCHAR(30)

EXEC DBO.GET_SYSID @SID OUTPUT

SELECT @SID

MAKTX栏位的获取方式：

SELECT TOP 1 * FROM AOI_WO_BOM WHERE MATNR = ''

MATNR为用户需要添加的料号

3、还需要看SAP_BOM里面有没有该条记录

查询语句如下：

select * from AOI_PN_SAP_BOM where PN = '341111000131' and COMPONENT = '141812000054'

其中PN为总表的DEVICE栏位，COMPONENT 为用户要添加的料号

 

 

其中大概需要用到的语句

SELECT A.WO,A.DEVICE,B.*
FROM MES_WPC_WO A
LEFT JOIN AOI_WO_BOM B ON A.WPC_WO_SID = B.WPC_WO_SID
--where device = '341701000747'
where wo = '420000252'

select * from AOI_PN_SAP_BOM where PN = '341111000195' and COMPONENT = '141814000101'

insert into AOI_PN_SAP_BOM(PN_SAP_BOM_SID,PN_SID,PN,ITEM_NO,COMPONENT,COMP_QTY,COMP_UNIT,ITEM_TEXT1,BOM_NO,ITEM_COUNT,CREATED_ON,VALID_TO,AI_PRIO,USERID,UPDATETIME)
values('A2020091715302119371','A2020091714255903758','341111000195','0230','141814000101','1.000','PCS','H T_RC_6D_TFC_001','00080138','00000167','2020-08-30','9999-12-31','00','191440','2020/09/17 14:26:18')

insert into AOI_PN_SAP_BOM(PN_SAP_BOM_SID,PN_SID,PN,ITEM_NO,COMPONENT,COMP_QTY,COMP_UNIT,ITEM_TEXT1,BOM_NO,ITEM_COUNT,CREATED_ON,VALID_TO,AI_PRIO,USERID,UPDATETIME)
values('A2020091715303521857','A2020091714255903758','341111000195','0240','143202000504','1.000','PCS','H Ring for CWDM Mini TOSA-1.2mm','00080138','00000170','2020-08-30','9999-12-31','00','191440','2020/09/17 14:26:18')

delete from AOI_WO_BOM where WO_BOM_SID = 'A2020090917014556393'
delete from AOI_WO_BOM where WO_BOM_SID = 'A2020090917014956417'



DECLARE @SID VARCHAR(30)
EXEC DBO.GET_SYSID @SID OUTPUT
SELECT @SID