对应四班MOMT界面按ALT+F8查询缺料如何做成报表

xxsdyq

如题，我想做一个报表，条件是输入订单号和行号来查对应的缺料情况，目前我只知道Demand表和Supply表以及其它表，可从这几张表怎么得出缺料情况呢，而且，每张订单的需求时间又不一样，如何根据需求时间和现有库存逐一计算缺料情况呢，请哪位大虾指点一下，我现在头都大了！

xxsdyq

大虾们出出主意呢？有谁做过按订单查询缺料情况的报表的给点建议啊？不胜感激！

yanwei82123300

xxsdyq   没办法！让IT导出audt数据就可以查欠料了

yanwei82123300

我用ACCESS做了欠料查询就是比较麻烦，只支持4状态，不包含2状态

xxsdyq

yanwei82123300 发表于 2012/10/25 12:45
我用ACCESS做了欠料查询就是比较麻烦，只支持4状态，不包含2状态

谢谢，我想做的就是即要支持4状态的也要支持2状态的MO，可惜理不清头绪，无从下手。

xxsdyq

yanwei82123300 发表于 2012/10/25 12:44
xxsdyq   没办法！让IT导出audt数据就可以查欠料了

AUDT怎么查缺料，不明白，请指教！

yanwei82123300

错了，是SDAL 导出，使用递减即可

cw1632000

如果你要这样做，相当于模拟了MRP的运算了。很难，我做了一个简单的，将所 4和5状态的订单，全部提出来，汇总需求。然后再与现有库存对比，还有带出在订量。如下图：

然后分别点，需求量、库存量、在订单，弹出相关明细。

yanwei82123300

cw1632000 发表于 2012/10/29 16:37
如果你要这样做，相当于模拟了MRP的运算了。很难，我做了一个简单的，将所 4和5状态的订单，全部提出来，汇 ...

cw1632000老师，谢谢分享你的方法！我利用的是SDAL中的界面依照前面的日期进行排序，通过递减来反应订单的欠料情况：
第一步：先生成SDAL表：
SELECT dbo_SFRM_DEMANDS.REQD_DATE, dbo_SFRM_DEMANDS.ITEM, IIf(IsNull([LN_NO]),[seqn],[LN_NO]) AS LN_NO1, dbo_SFRM_DEMANDS.P_NEED_DT, dbo_SFRM_DEMANDS.ORDER_NO1 AS [P & M_Order], dbo_SFRM_DEMANDS.ITEM_DESC, dbo_SFRM_DEMANDS.ORDER_NO3, dbo_SFRM_DEMANDS.ORDER_NO1, dbo_SFRM_DEMANDS.ORDER_NO2, IIf(IsNull([ORDER_NO2])=True,[ORDER_NO1],[ORDER_NO2]) AS CO_No, dbo_SFRM_DEMANDS.LN_STA, IIf([ORDER_QTY]=0 Or IsNull([ORDER_QTY])=True,[PARENT_QTY],[ORDER_QTY]) AS DM, dbo_SFRM_DEMANDS.P_SCHED_DT, dbo_SFRM_DEMANDS.P_START_DT, dbo_SFRM_DEMANDS.LN_TYPE, dbo_SFRM_DEMANDS.COMP_STA, dbo_ITEM_ITEMLIST.OHND_QTY, dbo_SFRM_DEMANDS.COM_TYP, dbo_SFRM_DEMANDS.FRM_FLAG, dbo_SFRM_DEMANDS.DMND_TYP, dbo_ITEM_ITEMLIST.MB, dbo_ITEM_ITEMLIST.BUYR, dbo_ITEM_ITEMLIST.ITEM_CLAS7 AS VenderID, dbo_FS_Item.SafetyStockQuantity AS SafetyStock, dbo_ITEM_ITEMLIST.ON_ORD_QTY, dbo_SFRM_DEMANDS.PARENT1, dbo_SFRM_DEMANDS.PAR_DESC1, dbo_ITEM_ITEMLIST.ITEM_CLAS7, dbo_SFRM_DEMANDS.ISSUED_QTY, dbo_SFRM_DEMANDS.PARENT_QTY, dbo_SFRM_DEMANDS.SEQN
FROM (dbo_SFRM_DEMANDS LEFT JOIN dbo_ITEM_ITEMLIST ON dbo_SFRM_DEMANDS.ITEM = dbo_ITEM_ITEMLIST.ITEM) LEFT JOIN dbo_FS_Item ON dbo_SFRM_DEMANDS.ITEM = dbo_FS_Item.ItemNumber
GROUP BY dbo_SFRM_DEMANDS.REQD_DATE, dbo_SFRM_DEMANDS.ITEM, IIf(IsNull([LN_NO]),[seqn],[LN_NO]), dbo_SFRM_DEMANDS.P_NEED_DT, dbo_SFRM_DEMANDS.ORDER_NO1, dbo_SFRM_DEMANDS.ITEM_DESC, dbo_SFRM_DEMANDS.ORDER_NO3, dbo_SFRM_DEMANDS.ORDER_NO1, dbo_SFRM_DEMANDS.ORDER_NO2, IIf(IsNull([ORDER_NO2])=True,[ORDER_NO1],[ORDER_NO2]), dbo_SFRM_DEMANDS.LN_STA, IIf([ORDER_QTY]=0 Or IsNull([ORDER_QTY])=True,[PARENT_QTY],[ORDER_QTY]), dbo_SFRM_DEMANDS.P_SCHED_DT, dbo_SFRM_DEMANDS.P_START_DT, dbo_SFRM_DEMANDS.LN_TYPE, dbo_SFRM_DEMANDS.COMP_STA, dbo_ITEM_ITEMLIST.OHND_QTY, dbo_SFRM_DEMANDS.COM_TYP, dbo_SFRM_DEMANDS.FRM_FLAG, dbo_SFRM_DEMANDS.DMND_TYP, dbo_ITEM_ITEMLIST.MB, dbo_ITEM_ITEMLIST.BUYR, dbo_ITEM_ITEMLIST.ITEM_CLAS7, dbo_FS_Item.SafetyStockQuantity, dbo_ITEM_ITEMLIST.ON_ORD_QTY, dbo_SFRM_DEMANDS.PARENT1, dbo_SFRM_DEMANDS.PAR_DESC1, dbo_ITEM_ITEMLIST.ITEM_CLAS7, dbo_SFRM_DEMANDS.ISSUED_QTY, dbo_SFRM_DEMANDS.PARENT_QTY, dbo_SFRM_DEMANDS.SEQN, dbo_SFRM_DEMANDS.ITEM, dbo_SFRM_DEMANDS.ORDER_QTY
HAVING (((IIf([ORDER_QTY]=0 Or IsNull([ORDER_QTY])=True,[PARENT_QTY],[ORDER_QTY]))>0) AND ((dbo_SFRM_DEMANDS.COMP_STA)="4") AND ((dbo_SFRM_DEMANDS.COM_TYP)<>"X") AND ((dbo_SFRM_DEMANDS.ISSUED_QTY)=0) AND ((dbo_SFRM_DEMANDS.ORDER_QTY)>0))
ORDER BY dbo_SFRM_DEMANDS.REQD_DATE, dbo_SFRM_DEMANDS.ITEM, IIf(IsNull([LN_NO]),[seqn],[LN_NO]), dbo_SFRM_DEMANDS.P_NEED_DT, dbo_SFRM_DEMANDS.ITEM;
第二步：生成CO订单表：CO LINE COMP_STA
第三步：依据日期进行递减生成需求排序（排队，就像排队买票一样）

SELECT tblSDAL.REQD_DATE, tblSDAL.ITEM, Format(CStr([tblCODetail]![LN_NO]),"000") AS LN, tblCODetail.CO_NUMBER, tblSDAL.DM, tblSDAL.OHND_QTY, tblSDAL.COM_TYP, [tblSDAL].[OHND_QTY]-(select sum(b.DM) from tblSDAL as b where b.ITEM=tblSDAL.ITEM and b.id<=tblSDAL.id) AS ATP, tblSDAL.ITEM_DESC, [ITEM] & ";" & [atp] AS SHORTAGE, tblSDAL.COMP_STA
FROM tblCODetail INNER JOIN tblSDAL ON (tblCODetail.CO_NUMBER = tblSDAL.CO_No) AND (tblCODetail.LN_NO = tblSDAL.LN_NO)
WHERE (((tblCODetail.CO_NUMBER) Not Like "P*") AND ((tblSDAL.COM_TYP)<>"y") AND (([tblSDAL].[OHND_QTY]-(select sum(b.DM) from tblSDAL as b where b.ITEM=tblSDAL.ITEM and b.id<=tblSDAL.id))<0))
ORDER BY tblSDAL.REQD_DATE, tblSDAL.ITEM, Format(CStr([tblCODetail]![LN_NO]),"000");

cw1632000

yanwei82123300 发表于 2012/10/30 08:11
cw1632000老师，谢谢分享你的方法！我利用的是SDAL中的界面依照前面的日期进行排序，通过递减来反应订单的 ...

我们的生产订单只是表示本周的需求总量，而每天的生产量要根据物料和设备情况去调整。所以如果将每个订单按先后缺料都算出来，必须每个订单都按订单日期来生产，目前我们公司做不好。所以只有给一个一周的汇总量给采购，让他们去催物料就行了。

yanwei82123300

cw1632000老师，明白您公司的方法了，我们公司使用3VA表来拉动物料，本周的需求物料在上周到货！能否分享你的代码吗？

cw1632000

yanwei82123300 发表于 2012/10/30 11:46
cw1632000老师，明白您公司的方法了，我们公司使用3VA表来拉动物料，本周的需求物料在上周到货！能否分享你 ...

Create Proc  SP_REP_MO_PICK4
as
SELECT MakeBuyCode,a.ItemNumber,ItemDescription,ALLOCATION1,QTY,ALLOCATION1-QTY AS QTY1,ON_ORD_QTY FROM
(
(SELECT ItemNumber,sum(RequiredQuantity-IssuedQuantity) AS ALLOCATION1 FROM dbo.FS_Demand h
Inner join FS_Item k on h.ItemKey=k.ItemKey WHERE LineStatus IN (4,5) and OrderType='M' and ComponentType in ('N','Z')
Group by ItemNumber,ItemDescription) a
LEFT JOIN   
(SELECT ITEM,SUM(QTY_BY_LOC) AS QTY FROM dbo.ITEM_INVENTORY WHERE INV_CATGRY IN ('O','I') GROUP BY ITEM) b ON a.ItemNumber=b.ITEM
LEFT JOIN (SELECT DISTINCT ITEM,ALLOCATION,ON_ORD_QTY,MB FROM dbo.ITEM_INVENTORY WHERE ON_ORD_QTY>0) c on a.ItemNumber=c.ITEM
INNER JOIN FS_Item d on a.ItemNumber=d.ItemNumber)
WHERE isnull(ALLOCATION1,0)-isnull(QTY,0)>0
ORDER BY MakeBuyCode,ALLOCATION1-QTY DESC


这是主表的代码，副表的代码更简单，只要将这段代码的物料号传给子报表，就自动生成了。

yanwei82123300

cw1632000 发表于 2012/11/1 11:01
Create Proc  SP_REP_MO_PICK4
as
SELECT MakeBuyCode,a.ItemNumber,ItemDescription,ALLOCATION1,QTY, ...

cw1632000老师谢谢分享！您的代码，请问如果没有办法登陆SQL后台能否将您做的代码转换成ACCESS做的代码吗？谢谢

cw1632000

yanwei82123300 发表于 2012/11/1 12:23
cw1632000老师谢谢分享！您的代码，请问如果没有办法登陆SQL后台能否将您做的代码转换成ACCESS做的代码吗 ...

SELECT MakeBuyCode,a.ItemNumber,ItemDescription,ALLOCATION1,QTY,ALLOCATION1-QTY AS QTY1,ON_ORD_QTY FROM
(
(SELECT ItemNumber,sum(RequiredQuantity-IssuedQuantity) AS ALLOCATION1 FROM dbo.FS_Demand h
Inner join FS_Item k on h.ItemKey=k.ItemKey WHERE LineStatus IN (4,5) and OrderType='M' and ComponentType in ('N','Z')
Group by ItemNumber,ItemDescription) a
LEFT JOIN   
(SELECT ITEM,SUM(QTY_BY_LOC) AS QTY FROM dbo.ITEM_INVENTORY WHERE INV_CATGRY IN ('O','I') GROUP BY ITEM) b ON a.ItemNumber=b.ITEM
LEFT JOIN (SELECT DISTINCT ITEM,ALLOCATION,ON_ORD_QTY,MB FROM dbo.ITEM_INVENTORY WHERE ON_ORD_QTY>0) c on a.ItemNumber=c.ITEM
INNER JOIN FS_Item d on a.ItemNumber=d.ItemNumber)
WHERE isnull(ALLOCATION1,0)-isnull(QTY,0)>0
ORDER BY MakeBuyCode,ALLOCATION1-QTY DESC

yanwei82123300

您好！您写的是SQL 服务器上的代码吧？（里面的一些字母 a b c 不明白！），看看能否修改成！access格式，谢谢

yanwei82123300

cw1632000 发表于 2012/11/1 13:37
SELECT MakeBuyCode,a.ItemNumber,ItemDescription,ALLOCATION1,QTY,ALLOCATION1-QTY AS QTY1,ON_ORD_QTY ...

您好！您写的是SQL 服务器上的代码吧？（里面的一些字母 a b c 不明白！），看看能否修改成！access格式，谢谢