Infor XA ERP运维常用SQL

最新推荐文章于 2024-09-23 16:54:21 发布

原创最新推荐文章于 2024-09-23 16:54:21 发布 · 3.8k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#ERP #sql #Infor #Infor XA

Infor XA ERP 专栏收录该内容

4 篇文章

订阅专栏

本文提供了解决Infor XA ERP中绿屏shipment异常退出导致的问题的SQL解锁脚本，并分享了如何通过SQL查询获取无工艺序的最早订单及Forcast明细数据的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

记录Infor XA ERP工作用SQL

1、绿屏shipment操作不正常退出，造成shipment状态异常，无法继续任何操作；解锁SQL

--绿屏异常退出,shipment异常
UPDATE 环境库.MBADRES0 SET ADIIST = '50' WHERE ADIIST = '99' AND ADHFCD = '50';                  
UPDATE 环境库.MBADRES0 SET ADIIST = '00' WHERE ADIIST = '99' and ADHFCD = '20';
UPDATE 环境库.MBKJREP  SET KJABTM = 121212 WHERE KJABTM = 999999;		 	
UPDATE 环境库.MBDHREP  SET DHABTM = 121212 WHERE DHABTM = 999999;

10、无工艺序最早订单、Forcast明细

--建议工艺完成日期、CO交货承若日期、CO号码、CO成品、成品下阶、项目说明、项目长描述、项目长规格、项目类型、项目类别、生产部门
SELECT DISTINCT
  CASE
	WHEN itemF.itcls = 'A001' THEN TO_CHAR(to_date(char(CO.coDate+19000000),'YYYYMMDD') - 35 days,'YYYYMMDD')-19000000
	WHEN itemF.itcls LIKE 'A00%' AND itemF.itcls != 'A001' THEN TO_CHAR(to_date(char(CO.coDate+19000000),'YYYYMMDD') - 42 days,'YYYYMMDD')-19000000
	WHEN itemF.itcls LIKE 'J%' THEN TO_CHAR(to_date(char(CO.coDate+19000000),'YYYYMMDD') - 56 days,'YYYYMMDD')-19000000
	ELSE TO_CHAR(to_date(char(CO.coDate+19000000),'YYYYMMDD') - 49 days,'YYYYMMDD')-19000000
  END adviceDate, 
  CO.coDate,CO.coNum,CO.fItem,
  lvBom.pitm,itemF.itdsc, itemZ.LDESP, itemZ.SDESP,itemF.ittyp,itemF.itcls,itemF.dptno
  FROM (SELECT concat('CO',coLineRe.ADCVNB) coNum,coLineRe.ADBIDT coDate,coLineRe.ADAITX fItem
		  FROM AMFLIBF.MBADREP coLineRe
		 WHERE coLineRe.ADHFCD != '50' AND coLineRe.ADAGNV > 0
		   AND coLineRe.ADBIDT = (SELECT min(minCoLineRe.ADBIDT) 
		                            FROM AMFLIBF.MBADREP minCoLineRe
		                           WHERE minCoLineRe.ADAITX = coLineRe.ADAITX
		                             AND minCoLineRe.ADHFCD != '50' AND minCoLineRe.ADAGNV > 0   
		                         )
		union
		SELECT 'FORECAST' coNum,forecast.RQDUD coDate,forecast.RQCOM fItem
		  FROM AMFLIBF.REQMTS forecast
		 WHERE forecast.RQSOR = '6'
		   AND forecast.RQDUD = (SELECT min(minForecast.rqdud)
		                            FROM AMFLIBF.REQMTS minForecast
		                           WHERE minForecast.RQCOM = forecast.RQCOM
		                             AND minForecast.RQSOR = '6'
		   						 )
		) CO
  JOIN MWLIBF.BOMLVLPF lvBom ON lvBom.fitm = CO.fItem
  JOIN MWLIBF.ZITMEXT itemZ ON itemZ.itnbr = lvBom.pitm
  JOIN AMFLIBF.ITMRVA itemF ON itemF.itnbr = lvBom.pitm 
                           AND itemF.CFST != 'STOP' 
                           AND itemF.ittyp in ('1','2') 
  WHERE not EXISTS (select 1 from AMFLIBF.RTGOPR routOpr
                     WHERE routOpr.rtid = lvBom.pitm 
                       AND routOpr.wkctr != 'QC'
                   ) 
 ORDER BY CO.coDate,CO.fItem,lvBom.pitm