需求运算来源配置

一、功能介绍

本功能主要用于配置需求运算的来源视图。

二、操作说明

1、菜单路径：导航菜单进入“系统管理\系统配置\业务控制方案”。

2、进入业务控制方案界面，新增下面图示项，在【参数值】里创建SQL语句。

三、配置说明

1、基础资料配置

1）物料清单信息视图

• select
  母件编码 as mmatecode, —字符型
  母件名称 as matepart, —字符型
  母件型号 as matestvd, —字符型
  单位 as mainunit, —字符型
  版本号 as Visoncode, —字符型
  版本说明 as Visonname, —字符型
  版本日期 as Visondate, —日期型
  子件编码 as zmatecode, —字符型
  子件名称 as zmatepart, —字符型
  子件型号 as zmatestvd, —字符型
  子单位 as zmainunit, —字符型
  基本用量 as molqty, —数值型
  基础数量 as denqty, —数值型
  损耗率 as lossrate, —整数型
  子件版本 as zvisoncode, —字符型
  定位符 as locator, —数值型
  毛需求 as mmrp, —整数型
  备注 as Remarkss, —字符型
  母件+版本号 as mjid, —字符型
  子件+版本号 as zjid, —字符型
  母件描述 as mmatename, —字符型
  子件描述 as zmatename, —字符型
  BOM子表ID as ZID,—整数型
  BOM主表ID as ID,—整数型
  状态 as ZTID —整数型
  from 第三方ERP系统BOM表
• 第三方ERP示例语句，软件名称：用友ERPU8

select a.invcode as mmatecode,a.invname as mmatepart,a.invstd as mmatestvd,a.invunitname as mainunit,a.[version] as Visoncode,
a.versiondesc as Visonname,a.versioneffdate as Visondate,b.dinvcode as zmatecode,b.dinvname as zmatepart,b.dinvstd as zmatestvd,
b.dinvunitname as zmainunit,b.dbaseqtyn as Molqty,b.dbaseqtyd as Denqty,c.[version] as zvisoncode,b.dcompscrap as Lossrate,
B.DDefine_31 AS locator,d.plansx,b.dremark as Remarks,a.bomstate as cstate,a.BOMID AS ID,b.OpComponentId AS ZID,
a.invname+’  ‘+a.invstd as mmatename,b.dinvname+’  ‘+isnull(b.dinvstd,”) as zmatename,x.PartId as mjid,y.PartId as zjid
from v_bom_head as a inner join v_bom_detail b on a.bomid = b.bomid
inner join bas_part x on a.InvCode=x.InvCode
inner join bas_part y on b.dinvcode=y.InvCode
LEFT OUTER JOIN
(select a.invcode,b.[version] from (select MAX(BOMID) as bomid,InvCode from v_bom_head GROUP BY InvCode) a
left outer join v_bom_head b on a.bomid=b.bomid) as c on b.DInvCode=c.InvCode
left outer join
(select cinvcode,case when iPlanDefault=1 then ‘自制件’ when iPlanDefault=2 then ‘委外件’  else ‘采购件’ end as plansx
from inventory ) as d on b.dinvcode= d.cinvcode

2）最新物料清单视图

• select
  物料编码 as matecode, —字符型
  版本代号 as visoncode, —字符型
  版本说明 as visonname, —字符型
  BOM类型 as botype  —字符型
  from 第三方ERP系统BOM表
• 第三方ERP示例语句，软件名称：用友ERPU8

select a.invcode as matecode,cast(b.[version] as nvarchar(50)) as visoncode,VersionDesc as visonname,cast(case when bomtype = 1 then ‘标准BOM’ ELSE ‘配件BOM’ end as nvarchar(50)) as  botype from (select a.invcode,max(a.versioneffdate) vdate
from v_bom_bomparent a inner join bom_bom b on a.bomid=b.bomid
where  a.bomtype=1 and b.status=3 group by a.invcode) a inner join v_bom_bomparent b
on a.invcode=b.invcode and a.vdate= b.versioneffdate

2、需求运算分析-按订单

1）需求运算分析-按订单视图

• select
  订单日期 as djdate, —日期型
  客户编码 as custcode,  —字符型
  销售单号 as socode,  —字符型
  销售订单子表ID as ddzid, —整数型
  物料编码 as matecode, —字符型
  物料名称 as matepart, —字符型
  规格型号 as matestvd, —字符型
  单位 as mainunit, —字符型
  数量 as qty,  —数值型
  交货日期 as repdate, —日期型
  完工日期 as finddate, —日期型
  销售类型 as saletype,  —字符型
  销售部门 as sadepname,  —字符型
  业务员 as saleuser,  —字符型
  制单人 as maker,  —字符型
  审核人 as auditor,  —字符型
  审核日期 as auddate,  —日期型
  备注 as remarks,  —字符型
  毛需求 as mmrp, —整数型
  计划标志 as mrp, —整数型
  状态 as ZTID  —整数型
  from 第三方ERP系统销售订单表
• 第三方ERP示例语句，软件名称：用友ERPU8

select  a.ddate as djdate,a.ccuscode as custcode,a.csocode as socode,b.isosid as ddzid,
b.cinvcode as matecode,b.cinvname matepart,b.cinvstd as matestvd,b.cinvm_unit as mainunit,cast(isnull(b.iquantity,0) as decimal(38, 2)) qty,
b.dpredate as repdate,b.dpremodate as finddate,a.cdepname as sadepname,a.cpersonname as saleuser,a.cmaker as maker,
a.cverifier as auditor,a.dverifydate as auddate, b.cmemo AS remarks,cast(isnull(b.cinvdefine11,0) as bit) as mmrp,
cast(d.[version] as nvarchar(50)) as visoncode,d.VersionDesc as visonname,cast(case when d.bomtype = 1 then ‘标准BOM’ ELSE ‘配件BOM’ end as nvarchar(50)) as  botype,
(case when a.istatus = 0 then ‘未审批’ else ‘已审批’ end ) as cstate
from saleorderq a inner join saleordersq b on a.id=b.id
and ISNULL(b.iQuantity,0) – ISNULL(b.iFHQuantity,0)>0 and (b.cscloser is null or b.cscloser=”) And a.istatus = 1
left outer join saletype c on a.cstcode=c.cstcode  and c.bstmps_mrp = 1
left outer join
(select a.invcode,b.version,b.VersionDesc,BomType from (select a.invcode,max(a.versioneffdate) vdate
from v_bom_bomparent a inner join bom_bom b on a.bomid=b.bomid
where  a.bomtype=1 and b.status=3 group by a.invcode) a
inner join v_bom_bomparent b on a.invcode=b.invcode and a.vdate= b.versioneffdate)d on b.cinvcode= d.invcode
on b.cinvcode=d.invcode

3、需求运算分析-按物料

1）需求运算分析-按物料视图 

• select
  物料分类 as cinvname,  —字符型
  物料编码 as matecode, —字符型
  物料名称 as matepart, —字符型
  规格型号 as matestvd, —字符型
  单位 as mainunit, —字符型
  计划属性 as plansx, —字符型
  安全库存 as safeqty, —数值型
  提前期 as advance, —整数型
  最小批量 as supqty, —数值型
  最小包装 as minqty, —数值型
  生产部门 as prdepname, —字符型
  物料描述 as matename,—字符型
  计划员 as planuser, —字符型
  采购员 as puruser, —字符型
  毛需求 as mmrp,  —整数型
  是否运算as ISMRP –字符型(N表示不能与运算,其他运算)
  from 第三方ERP系统物料信息表
• 第三方ERP示例语句，软件名称：用友ERPU8

select b.cInvCName as cinvname, a.cInvCode as matecode,a.cInvName as matepart,a.cInvStd as matestvd,
c.cComUnitName as mainunit,
(case when a.iPlanDefault=1 then ‘自制件’ when a.iPlanDefault=2 then ‘委外件’  else ‘采购件’ end) as plansx,
(case when a.iSupplyType=3 then ‘虚拟件’ when a.iSupplyType=0 then ‘领用件’ when a.iSupplyType=1 then ‘入库倒冲件’
when a.iSupplyType=2 then ‘工序倒冲件’  else ‘直接供应件’ end) as suptype,isnull(a.iInvAdvance,1) as advance,
isnull(a.iSafeNum,0) as safeqty,isnull(fSupplyMulti,1) as supqty,isnull(fMinSupply,1) as minqty,d.cDepName as prdepname,” as prodname,
cast(isnull(a.cLicence,0) as bit) as mmrp,e.cPsn_Name as  planuser,
cast(case when cPlanMethod =’R’ then 1 else 0 end as bit) as ynmrp,cast(case when dEDate is null then 1 else 2 end as int) as cstate
from inventory a inner join InventoryClass b on a.cInvCCode = b.cInvCCode
left join ComputationUnit c  on a.cComunitCode = c.cComunitCode
left join Department as d on a.cInvDepCode=d.cDepCode< BR> left join hr_hi_person e on a.cInvPersonCode=e.cPsn_Num

4、需求运算分析-按工单

1）需求运算分析-按生产工单视图 

• select
  订单日期 as djdate, —日期型
  生产部门 as prdepname, —字符型
  物料编码 as matecode, —字符型
  物料名称 as matepart, —字符型
  规格型号 as matestvd, —字符型
  单位 as mainunit, —字符型
  数量 as qty,  —数值型
  开工日期 as STARTDATE, —日期型
  完工日期 as finddate, —日期型
  入库数量 as rkqty,  —数值型
  生产在制量 as mozzqty,  —数值型
  销售单号 AS socode, —字符型
  版本号 as visoncode, —字符型
  状态 AS ZTID,  —整数型
  生产订单子表ID AS ZID  —整数型
  from 第三方ERP系统生产订单表
• 第三方ERP示例语句，软件名称：用友ERPU8

SELECT makedate as djdate,deptname as prdepname,MoCode as ddcode, InvCode AS mmatecode, invname as mmatepart,invstd as mmatestvd,Qty AS ddqty,
unitcode as mmainunit,StartDate AS startdate, DueDate AS finddate,ordercode as socode,
case when [status] = 2 then ‘未审批’ when [status] = 3 then ‘已审批’ else ‘已关闭’ end  as cstate,MODID as ddzid
from v_mom_orderdetail where[status]<>4

2）需求运算分析-按生产工单用料视图  

• select
  生产单号 as mocode, —字符型
  物料编码 as matecode, —字符型
  物料名称 as matepart, —字符型
  规格型号 as matestvd, —字符型
  单位 as mainunit, —字符型
  应领数量 as ylqty,   —数值型
  需求日期 as mrpdate, —日期型
  基本用量 as molqty, —数值型
  基础用量 as denqty, —数值型
  损耗率 as lossrate, —数值型
  已领用量 as lyqty, —数值型
  批号 as batch, —字符型
  生产订单子表ID as souid —整数型
  from 第三方ERP系统生产订单用料表

• 第三方ERP示例语句，软件名称：用友ERPU8

SELECT b.MoCode as ddcode,b.Qty AS ddqty,a.InvCode AS matecode,a.invname as matepart,a.InvStd as matestvd,a.Qty AS ylqty, a.UnitName as mainunit,
a.Demdate as mrpdate,a.BaseQtyN as molqty,a.BaseQtyD as denqty,a.CompScrap as lossrate,
a.IssQty AS moylqty, a.Qty – a.IssQty AS mowlqty, a.LotNo as batch,a.MODID as ddzid
from v_mom_moallocate as a left outer join  v_mom_orderdetail as b on b.modid = a.modid
where b.[status]<>4

3）需求运算分析-按委外工单视图  

• select
  委外类型 AS WOTYPE, —字符型
  订单日期 AS djdate, —日期型
  委外单号 as wocode, —字符型
  供应商 AS vendcode, —字符型
  物料编码 as matecode, —字符型
  物料名称 as matepart, —字符型
  规格型号 as matestvd, —字符型
  单位 as mainunit, —字符型
  数量 as qty,  —数值型
  交货日期 as repdate, —日期型
  入库数量 as rkqty, —数值型
  委外在制量 as woztqty, —数值型
  委外部门 as wodepname, —字符型
  计划员 as planuser, —字符型
  制单人 as maker, —字符型
  审核人 as auditor, —字符型
  审核日期 as auddate, —日期型
  关闭人 as hcloseuser, —字符型
  状态 AS ZTID, —整数型
  委外订单主表ID as ID, —整数型
  委外订单子表ID as ZID —整数型
  from 第三方ERP系统委外订单表

• 第三方ERP示例语句，软件名称：用友ERPU8

SELECT a.ddate as djdate,a.cvenabbname as vendabbe,a.ccode as ddcode, b.cinvcode AS mmatecode, b.cinvname as mmatepart,b.cinvstd as mmatestvd,b.iquantity AS ddqty,
b.cinva_unit as mmainunit,b.dstartdate AS startdate, b.darrivedate AS finddate,b.csocode as socode,
case when cstate = 0 then ‘未审批’ when cstate = 1 then ‘已审批’ else ‘已关闭’ end as cstate,b.modetailsid as ddzid
from om_mohead a inner join om_mobody b on a.moid= b.moid where cstate <>2

4）需求运算分析-按委外工单用料视图  

• select
  物料编码 as matecode, —字符型
  物料名称 as matepart, —字符型
  规格型号 as matestvd, —字符型
  单位 as mainunit, —字符型
  应发数量 as yfqty,   —数值型
  需求日期 as mrpdate, —日期型
  基本用量 as molqty, —数值型
  基础用量 as denqty, —数值型
  损耗率 as lossrate, —数值型
  已领用量 as lyqty, —数值型
  批号 as batch, —字符型
  委外订单子表ID as souid —整数型
  from 第三方ERP系统委外订单用料表

• 第三方ERP示例语句，软件名称：用友ERPU8

SELECT a.ccode as ddcode,b.iquantity AS ddqty,c.cinvcode AS matecode,c.cinvname as matepart,c.cInvStd as matestvd,c.iquantity AS ylqty, c.cinvm_unit as mainunit,
c.drequireddate as mrpdate,c.fbaseqtyn as molqty,c.fbaseqtyd as denqty,c.fcompscrp as lossrate,
c.isendqty AS woylqty, c.iquantity – c.isendqty AS wowlqty, c.cbatch as batch,c.modetailsid as ddzid
from om_mohead a inner join om_mobody b on a.moid=b.moid inner join om_momaterialsbody c on b.modetailsid=c.modetailsid
where a.cstate <> 2