计划动态数据配置

一、功能介绍

本功能主要用于配置需求运算中动态数据的来源视图。文章源自云上网-http://www.ysmis.cn/1364.html

二、操作说明文章源自云上网-http://www.ysmis.cn/1364.html

1、菜单路径：导航菜单进入“系统管理\系统配置\业务控制方案”。文章源自云上网-http://www.ysmis.cn/1364.html

2、进入业务控制方案界面，新增下面图示项，在【参数值】里创建SQL语句。文章源自云上网-http://www.ysmis.cn/1364.html

文章源自云上网-http://www.ysmis.cn/1364.html

1）【仓库现存量】配置SQL语句。文章源自云上网-http://www.ysmis.cn/1364.html

• select
  仓库 as warename, ---字符型
  物料编码 as matecode, ---字符型
  物料名称 as matepart, ---字符型
  规格型号 as matestvd, ---字符型
  结存量 as curqty, ---数值型
  辅数量 as lessqty, ---数值型
  批号 as batch  ---字符型
  from 第三方ERP系统现存量表

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

SELECT B.cWhName AS warename, A.cInvCode AS matecode, C.cInvName AS matepart,c.cinvstd as matestvd,CAST(A.iQuantity AS  decimal(38, 2)) AS curqty,
CAST(a.iNum AS  decimal(38, 2)) as lessqty,A.cBatch As batch
FROM  CurrentStock As A INNER JOIN Inventory As C ON A.cInvCode = C.cInvCode INNER JOIN Warehouse As B ON A.cWhCode = B.cWhCode文章源自云上网-http://www.ysmis.cn/1364.html

2）【销售发货量】配置SQL语句。文章源自云上网-http://www.ysmis.cn/1364.html

• select
  销售单号 as socode,  ---字符型
  物料编码 as matecode,  ---字符型
  数量 as qty,  ---数值型
  完工日期 as finddate,  ---日期型
  交货日期 as repdate, ---日期型
  已发货量 as soychqty,  ---数值型
  销售发货量 as sochqty,  ---数值型
  备注 as remarks  ---字符型
  销售订单子表ID as zid, ---整数型
  from 第三方ERP系统销售订单未发货数据表

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

SELECT a.cSOCode AS socode, a.cInvCode AS matecode, CAST(a.iQuantity AS decimal(38, 2)) AS qty, a.dPreMoDate AS finddate, a.dPreDate AS repdate,
CAST(ISNULL(a.iFHQuantity,0) As Decimal(38, 2)) As soychqty, CAST(ISNULL(a.iQuantity,0) - ISNULL(a.iFHQuantity,0) As Decimal(38, 2)) As sochqty, a.cMemo As remarks,iSOsID as zid
from  so_sodetails As a  inner join so_somain b on a.id=b.id left outer join saletype c on b.cstcode=c.cstcode
where ISNULL(a.iQuantity,0) - ISNULL(a.iFHQuantity,0)>0 and (a.cscloser Is null) And istatus = 1 And c.bstmps_mrp = 1文章源自云上网-http://www.ysmis.cn/1364.html

3）【请购在途量】配置SQL语句。文章源自云上网-http://www.ysmis.cn/1364.html

• select
  请购单号 as pucode,  ---字符型
  单据日期 as djdate, ---日期型
  物料编码 as matecode,  ---字符型
  数量 as qty,  ---数值型
  需求日期 as mrpdate, ---日期型
  请购在途量 as puztqty,  ---数值型
  备注 as remarks  ---字符型
  from 第三方ERP系统请购在途数据表
• 第三方ERP示例语句，软件名称：用友ERPU8

SELECT  b.cCode AS pucode, b.dDate AS djdate, a.cInvCode AS matecode,CAST(a.fQuantity  AS decimal(38, 2)) AS qty,a.dRequirDate As mrpdate,
CAST(a.fQuantity - a.iReceivedQTY  As Decimal(38, 2))  As puztqty, a.cDefine31 As remarks
from pu_appvouchs as a inner join  pu_appvouch b on a.id=b.id left outer join PurchaseType c on b.cPTCode=c.cPTCode
where (a.cbcloser is null) and c.bPTMPS_MRP = 1 and (a.fQuantity - ISNULL(a.iReceivedQTY,0) > 0)

4）【采购在途量】配置SQL语句。

• select
  采购单号 AS pocode, ---字符型
  单据日期 as djdate, ---日期型
  物料编码 as matecode,  ---字符型
  数量 AS qty, ---数值型
  入库数量 AS rkqty, ---数值型
  采购在途量 AS poztqty, ---数值型
  交货日期 AS repdate ---日期型
  from 第三方ERP系统采购在途数据表

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

SELECT  b.cPOID AS pocode, b.dPODate AS djdate, a.cInvCode AS matecode,CAST(a.iQuantity AS decimal(38, 2)) AS qty,
isnull(CAST(a.iReceivedQTY AS decimal(38, 2)),0) AS rkqty,
CAST(isnull(a.iQuantity,0) - isnull(a.iReceivedQTY,0) - isnull(a.iArrQTY,0) AS decimal(38, 2)) AS poztqty, a.dArriveDate AS repdate
from po_podetails as a inner join po_pomain b on a.poid=b.poid left outer join PurchaseType c on b.cPTCode=c.cPTCode
where (a.cbcloser Is null) and c.bPTMPS_MRP = 1 and (a.cbCloser IS NULL) AND (isnull(a.iQuantity,0) - isnull(a.iReceivedQTY,0) - isnull(a.iArrQTY,0)> 0)

5）【生产在制量】配置SQL语句。

• select
  生产单号 as mocode,  ---字符型
  物料编码 as matecode,  ---字符型
  数量 as qty, ---数值型
  入库数量 as rkqty, ---数值型
  生产在制量 as mozzqty, ---数值型
  开工日期 as startdate, ---日期型
  完工日期 as finddate, ---日期型
  销售单号 as socode,  ---字符型
  生产部门 as prdepname  ---字符型
  from 第三方ERP系统生产订单在制数据表

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

SELECT  OrderCode AS socode, MoCode AS mocode, InvCode AS matecode, cast(Qty  AS decimal(38, 2)) AS qty,
cast(QualifiedInQty  AS decimal(38, 2)) AS rkqty, cast(Qty - isnull(QualifiedInQty,0)  AS decimal(38, 2)) AS mozzqty,
StartDate AS startdate, DueDate AS finddate, Deptname AS prdepname
from v_mom_orderdetail as a < BR > where a.status <> 4

6）【生产未领量】配置SQL语句。

• select
  生产单号 as mocode, ---字符型
  产品名称 as mmatecode, ---字符型
  订单数量 as ddqty, ---数值型
  物料编码 as matecode, ---字符型
  应领数量 as ylqty, ---数值型
  已领数量 as moylqty, ---数值型
  未领数量 as mowlqty, ---数值型
  需求日期 as mrpdate, ---日期型
  开工日期 as startdate, ---日期型
  完工日期 as finddate, ---日期型
  生产订单子表ID as MODID ---整数型
  from 第三方ERP系统现存量表

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

SELECT b.MoCode AS mocode, b.InvCode AS mmatecode, b.Qty AS ddqty, a.InvCode AS matecode, a.Qty AS ylqty,
a.IssQty AS moylqty, a.Qty - a.IssQty AS mowlqty, a.Demdate AS mrpdate, b.StartDate AS startdate, b.DueDate AS finddate,a.MODID
from v_mom_moallocate as a left outer join  v_mom_orderdetail as b on b.modid = a.modid
where a.ByProductFlag = 0 and  (isnull(a.qty, 0) - isnull(a.issqty, 0) > 0) and (b.status <> 4)

7）【委外在制量】配置SQL语句。

• select
  委外单号 as wocode, ---字符型
  单据日期 as djdate, ---日期型
  物料编码 as matecode, ---字符型
  数量 as qty, ---数值型
  入库数量 as rkqty, ---数值型
  委外在制量 as woztqty, ---数值型
  交货日期 as repdate ---日期型
  from 第三方ERP系统委外订单未入库数据表

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

SELECT b.cCode AS wocode, b.dDate AS djdate, a.cInvCode AS matecode, CAST(a.iQuantity AS decimal(38, 2)) AS qty,
CAST(ISNULL(a.iReceivedQTY,0) AS decimal(38, 2)) AS rkqty,
CAST(ISNULL(a.iQuantity,0) - ISNULL(a.iReceivedQTY,0) AS decimal(38, 2)) AS woztqty, a.dArriveDate AS repdate
from om_modetails a left outer join om_mohead b on a.moid=b.moid left outer join PurchaseType c on b.cPTCode=c.cPTCode
where a.cbcloser Is null and c.bPTMPS_MRP = 1 and (ISNULL(a.iQuantity,0) - ISNULL(a.iReceivedQTY,0) > 0)

8）【委外未发量】配置SQL语句。

• select
  委外单号 as wocode, ---字符型
  单据日期 as djdate, ---日期型
  产品编码 as mmatecode, ---字符型
  数量 as qty, ---数值型
  物料编码 as matecode, ---字符型
  应发数量 AS yfqty, ---数值型
  已发数量 AS woyfqty, ---数值型
  委外未领量 AS wowlqty, ---数值型
  需求日期 AS mrpdate ---日期型
  from 第三方ERP系统委外订单未发料数据表

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

SELECT c.cCode AS wocode, c.dDate AS djdate, b.cInvCode AS mmatecode, CAST(b.iQuantity  AS decimal(38, 2)) AS  qty,
a.cInvCode AS matecode, CAST(a.iQuantity AS decimal(38, 2)) AS yfqty, CAST(isnull(a.iSendQTY,0) AS decimal(38, 2)) AS woyfqty,
CAST(isnull(a.iQuantity,0) - isnull(a.iSendQTY,0) AS decimal(38, 2)) AS wowlqty,a.dRequiredDate AS mrpdate
from om_momaterials as a left outer join om_modetails as b on a.modetailsid = b.modetailsid  left outer join om_mohead c on b.moid=c.moid
left outer join PurchaseType d on c.cPTCode=d.cPTCode
where (isnull(a.iquantity, 0) - isnull(a.isendqty, 0) > 0) and b.cbcloser Is null and d.bPTMPS_MRP = 1

9）【到货在途量】配置SQL语句。

• select
  到货单号 as crcode, ---字符型
  物料编码 as matecode, ---字符型
  物料名称 as matepart, ---字符型
  规格型号 as matestvd, ---字符型
  单位 as mainunit, ---字符型
  数量 as qty, ---数值型
  检验在途量 as qcztqty, ---数值型
  订单号码 as ddcode, ---字符型
  单据日期 as djdate, ---日期型
  供方名称 as vendname ---字符型
  from 第三方ERP系统到货在检验未入库数据表

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

select b.ccode As crcode,a.cinvcode as matecode,a.cinvname as matepart,a.cinvstd as matestvd,a.cinvm_unit as mainunit, a.iQuantity as qty,
ISNULL(a.fininQuantity,0) As qcztqty,a.cordercode As ddcode,b.ddate As djdate,b.cvenabbname As vendname
from pu_ArrBody a INNER JOIN pu_ArrHead B ON A.ID = B.ID WHERE a.fininQuantity > 0

10）【发货在途量】配置SQL语句。

• select
  单据日期 as djdate, ---日期型
  物料编码 as matecode, ---字符型
  物料名称 as matepart, ---字符型
  规格型号 as matestvd, ---字符型
  单位 as mainunit, ---字符型
  数量 as qty,
  发货在途量 as fhztqty,
  销售部门 as sadepname, ---字符型
  批号 as batch, ---字符型
  销售单号 as socode  ---字符型
  from 第三方ERP系统发货单已开单未出库数据表

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

select a.ddate as djdate,b.cinvcode as matecode,b.cinvname as matepart,b.cinvstd as matestvd,b.cinvm_unit as mainunit,
CAST(isnull(b.iquantity,0) As Decimal(38, 2)) qty,isnull(b.iquantity,0)-isnull(b.fOutQuantity,0) as fhztqty,a.cdepname as sadepname,b.iBatch as batch,b.cSoCode as socode
FROM Sales_FHD_T a inner join Sales_FHD_W b on a.DLID = b.DLID

11）【入库在途量】配置SQL语句。

• select
  单据编号 as crcode, ---字符型
  收发类型 as sfname, ---字符型
  物料编码 as matecode, ---字符型
  物料名称 as matepart, ---字符型
  规格型号 as matestvd, ---字符型
  单位 as mainunit, ---字符型
  待入库数量 as norkqty, ---数值型
  订单号码 as ddcode  ---字符型
  from 第三方ERP系统已做入库单未审核的出入库单表

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

select ccode as crcode,crdname as sfname,cinvcode as matecode,cinvname as matepart,cinvstd as matestvd,cinvm_unit as mainunit,iquantity as norkqty,cordercode as ddcode
from zpurrkdlist where chandler is null and bpufirst = 0
union all
select ccode as crcode,crdname as sfname,cinvcode as matecode,cinvname as matepart,cinvstd as matestvd,cinvm_unit as mainunit,iquantity as norkqty,cmocode as ddcode
from recordinlist where chandler is null
union all
select ccode as crcode,crdname as sfname,cinvcode as matecode,cinvname as matepart,cinvstd as matestvd,cinvm_unit as mainunit,iquantity as norkqty,cbuscode as ddcode < BR > from kcotherinlist where chandler is null

12）【出库在途量】配置SQL语句。

• select
  单据编号 as crcode, ---字符型
  收发类型 as sfname, ---字符型
  物料编码 as matecode, ---字符型
  物料名称 as matepart, ---字符型
  规格型号 as matestvd, ---字符型
  单位 as mainunit, ---字符型
  待出库数量 as nockqty, ---数值型
  订单号码 as ddcode  ---字符型
  from 第三方ERP系统已做出库单未审核的出入库单表

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

select ccode as crcode,crdname as sfname,cinvcode as matecode,cinvname as matepart,cinvstd as matestvd,cinvm_unit as mainunit,iquantity as nockqty,cbuscode as ddcode
from kcsaleoutlist where chandler is null
union all
select ccode as crcode,crdname as sfname,cinvcode as matecode,cinvname as matepart,cinvstd as matestvd,cinvm_unit as mainunit,iquantity as nockqty,cmocode as ddcode
from recordoutlist where chandler is null
union all
select ccode as crcode,crdname as sfname,cinvcode as matecode,cinvname as matepart,cinvstd as matestvd,cinvm_unit as mainunit,iquantity as nockqty,cbuscode as ddcode < BR > from kcotheroutlist where chandler is null

13）【申请领料量】配置SQL语句。

• select
  申请单号 as crcode, ---字符型
  单据日期 as djdate, ---日期型
  物料编码 as matecode, ---字符型
  物料名称 as matepart, ---字符型
  规格型号 as matestvd, ---字符型
  未领用数量 as llsqqty, ---数值型
  需求日期 as mrpdate, ---日期型
  审核人 as auditor, ---字符型
  审核日期 auddate ---字符型
  from 第三方ERP系统领料申请单未领用数据表

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

select a.cCode as crcode,a.dDate as djdate,b.cinvcode as matecode,c.cInvName as matepart,c.cinvstd as matestvd,b.iquantity - isnull(fOutQuantity,0) as llsqqty, b.dduedate as mrpdate,
a.cHandler as auditor,a.dVeriDate auddate from MaterialAppVouch a inner join MaterialAppVouchs b on a.ID=b.id
left outer join inventory c on b.cInvCode= c.cInvCode    where b.iquantity - isnull(fOutQuantity,0) >0

14）【调拨入库量】配置SQL语句。

• select
  调拨单号 as crcode,  ---字符型
  调拨日期 as djdate, ---日期型
  调入仓库 as warename,  ---字符型
  物料编码 as matecode,  ---字符型
  待调入数量 as dbrkqty  ---数值型
  from 第三方ERP系统调拨单待调拨入库数据表

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

SELECT ctvcode as crcode,dtvdate as djdate,cwhname_1 as warename,cinvcode as matecode,itvquantity as dbrkqty FROM kctranslist

where isnull(dverifydate,'')= ''

15）【调拨出库量】配置SQL语句。

• select
  调拨单号 as crcode,  ---字符型
  调拨日期 as djdate, ---日期型
  调出仓库 as warename,  ---字符型
  物料编码 as matecode,  ---字符型
  待调出数量 as dbckqty  ---数值型
  from 第三方ERP系统调拨单待调拨入库数据表

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

SELECT ctvcode as crcode,dtvdate as djdate,cwhname as warename,cinvcode as matecode,itvquantity as dbckqty FROM kctranslist

where isnull(dverifydate,'')= ''