四班：内部WIP查询

FrankXu · 发表于 2008/9/25 11:04:09

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 　。

您需要登录才可以下载或查看，没有帐号？注册

x

不含标准成本:
SELECT
      mh.MONumber
      ,mh.Planner  AS PLN
      ,mh.WorkCenter  AS WC
      ,mh.MOCreatedDate AS CreatedDate
      ,ml.MOLineNumber  AS LNO
      ,ml.MOLineType AS LType
      ,ml.MOLineStatus  AS LStat
      ,ml.NeededDate
      ,itemp.ItemNumber
      ,itemp.ItemUM  AS UM
      ,ml.ItemOrderedQuantity AS Qty_Ordered
      ,ml.ReceiptQuantity AS Qty_Recvd
      ,itemc.ItemNumber AS Component
      ,itemc.ItemDescription AS CompDesc
      ,itemc.ItemUM  AS CompUM
      ,ROUND(dd.RequiredQuantity,itemc.DecimalPrecision) AS Qty_TotalReq
      ,dd.IssuedQuantity AS Qty_Issued
      ,CASE WHEN ml.ItemOrderedQuantity<>0 THEN ROUND((dd.IssuedQuantity -dd.RequiredQuantity/ml.ItemOrderedQuantity
                  * ml.ReceiptQuantity), itemc.DecimalPrecision)  ELSE ROUND(dd.IssuedQuantity,itemc.DecimalPrecision) END AS Qty_WIP
      ,dd.PointOfUseID AS PT_USE
      ,dd.DemandLevel  AS NLVS
      ,dd.ComponentType AS COM_TYP
FROM
      FSDBMR.dbo.FS_MOHeader  AS mh,
      FSDBMR.dbo.FS_MOLine AS ml,
      FSDBMR.dbo.FS_Item  AS itemp,
      FSDBMR.dbo.FS_Demand AS dd,
      FSDBMR.dbo.FS_Item  AS itemc
WHERE
      mh.MOHeaderKey = ml.MOHeaderKey
      AND ml.ItemKey = itemp.ItemKey
      AND ml.MOLineKey = dd.TopLevelDemandSupplyKey
      AND (dd.ComponentType<>'Y' AND dd.RequiredQuantity>0)
      AND dd.ItemKey = itemc.ItemKey

含标准成本：
SELECT
      mh.MONumber
      ,mh.Planner  AS PLN
      ,mh.WorkCenter  AS WC
      ,mh.MOCreatedDate AS CreatedDate
      ,ml.MOLineNumber  AS LNO
      ,ml.MOLineType AS LType
      ,ml.MOLineStatus  AS LStat
      ,ml.NeededDate
      ,itemp.ItemNumber
      ,itemp.ItemUM  AS UM
      ,ml.ItemOrderedQuantity AS Qty_Ordered
      ,ml.ReceiptQuantity AS Qty_Recvd
      ,itemc.ItemNumber AS Component
      ,itemc.ItemDescription AS CompDesc
      ,itemc.ItemUM  AS CompUM
      ,ROUND(dd.RequiredQuantity,itemc.DecimalPrecision) AS Qty_TotalReq
      ,dd.IssuedQuantity AS Qty_Issued
      ,CASE WHEN ml.ItemOrderedQuantity<>0 THEN ROUND((dd.IssuedQuantity - dd.RequiredQuantity/ml.ItemOrderedQuantity * ml.ReceiptQuantity),itemc.DecimalPrecision)  ELSE ROUND(dd.IssuedQuantity,itemc.DecimalPrecision) END AS Qty_WIP
      ,dd.PointOfUseID AS PT_USE
      ,dd.DemandLevel  AS NLVS
      ,dd.ComponentType AS COM_TYP
      ,itmc.TotalRolledCost  AS StdCost
FROM
      FSDBMR.dbo.FS_MOHeader  AS mh,
      FSDBMR.dbo.FS_MOLine AS ml,
      FSDBMR.dbo.FS_Item  AS itemp,
      FSDBMR.dbo.FS_Demand AS dd,
      FSDBMR.dbo.FS_Item  AS itemc
      FSDBMR.dbo.FS_ItemCost    AS itmc
WHERE
      mh.MOHeaderKey = ml.MOHeaderKey
      AND ml.ItemKey = itemp.ItemKey
      AND ml.MOLineKey = dd.TopLevelDemandSupplyKey
      AND (dd.ComponentType<>'Y' AND dd.RequiredQuantity>0)
      AND dd.ItemKey = itemc.ItemKey
      AND dd.ItemKey=itmc.ItemKey  AND (itmc.CostType='0' AND itmc.CostCode='4')

注：查询时，要加上限定条件，对于当前WIP，一般是查询行状态=4
   即：AND  ml.MOLineStatus=4

[ 本帖最后由 FrankXu 于 2008-10-9 15:15 编辑 ]

dudusky · 发表于 2008/9/25 22:03:37

看了
不错!!!

Chinan2008 · 发表于 2008/9/26 13:56:11

看了
不错!!!

Y.C.Yang · 发表于 2009/3/13 12:03:10

good ,

pillarxu · 发表于 2009/7/7 21:55:42

不错，看看

zxc300 · 发表于 2009/7/17 11:22:58

很好的东西，多发点，多交流。

clyuan1 · 发表于 2009/7/26 19:51:34

很好！！！

rexman543 · 发表于 2009/8/23 14:13:14

good information~ 3q you~

lfzhl · 发表于 2009/10/16 23:49:57

不错。试一试

jhonlone · 发表于 2010/2/2 16:24:17

试试看

yanwei82123300 · 发表于 2010/5/23 07:57:13

看了
不错!!!

帐号		自动登录	找回密码
密码			注册

社区人物壹佰网重点话题	分享知识原创连载活动正式启动已有673人参加查看全部会员靓照旅行分享文学连载	壹佰读书会开始招收成员了
招聘求职壹佰网信息化选型平台	倾听用户的心声纵横四海谈IT如何提升企业管理管理小说连载《苏州桥》	如何获取努力值
原创连载－我来管管看 ERP分享+	推荐连载：[已结帖][分享知识原创连载]财务供应链项目新手实施手记：记录自己正	入门学习-致所有初学者

四班：内部WIP查询

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 。

浏览过的版块

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。如果您注册时有任何问题请联系客服QQ： 83569622 　。