--USE UFDATA_016_2016
GO
if exists(select name from sysobjects where name = 'Def_InvCommittments_Info' and type = 'V')
BEGIN
DROP VIEW Def_InvCommittments_Info
END
GO
Create View Def_InvCommittments_Info
with encryption
as
--仓库现存量数据
select 'Stocks' AS cType,cast('' as nvarchar(60)) AS cSource,'' as ccode,'' as RowNo,a.cInvCode,a.cWhCode,sum(a.iQuantity) as iQuantity,0 as iSafeNum from currentstock a group by a.cInvCode,a.cWhCode
UNION ALL
--anquan kucun
select 'Safty Stock' AS cType,cast('' as nvarchar(60)) AS cSource,'' as ccode,'' as RowNo,a.cInvCode,a.cWhCode,isnull(A.iSafeNum,0)*(-1) as iQuantity,0 as iSafeNum from
(select cinvcode,isnull(cdefwarehouse,'Blank') as cWhCode,iSafeNum from inventory where 1=1 OR isnull(iSafeNum,0) <>0) A
WHERE isnull(A.iSafeNum,0)<>0
--order by a.cInvCode,a.cwhcode
--生产订单 +
union all
SELECT 'Production Order' AS cType,RTRIM( B.MoCode+'|'+CONVERT(nvarchar(4),A.SortSeq)) AS cSource,B.MoCode as ccode,CONVERT(nvarchar(4),A.SortSeq) as RowNo,A.InvCode AS cInvCode,isnull(C.cdefwarehouse,'Blank') AS cWhCode,
ISNULL(A.Qty,0) - ISNULL(A.QualifiedInQty,0) AS iQuantity,0 AS iSafeNum
FROM mom_orderdetail a
left join mom_order b on a.MoId = b.moid
LEFT JOIN (select cinvcode,cdefwarehouse,iSafeNum from inventory where 1=1 OR isnull(iSafeNum,0) <>0) C ON A.INVCODE = C.CINVCODE
WHERE A.Status = '3' AND ISNULL(A.Qty,0) - ISNULL(A.QualifiedInQty,0) > 0
union all
--委外订单 +
SELECT 'OutSourcing Order' AS cType, RTRIM(b.cCode+'|'+cast(a.iVouchRowNo as nvarchar(4))) AS cSource,b.cCode as ccode,cast(a.iVouchRowNo as nvarchar(4)) as RowNo,a.cInvCode AS cInvCode,isnull(C.cdefwarehouse,'Blank') AS cWhCode,
ISNULL(a.iQuantity,0) - ISNULL(a.iReceivedQTY,0)-ISNULL(a.iArrQTY,0) AS iQuantity, 0 AS iSafeNum
FROM
OM_MODetails a
left join OM_MOMain b on a.moid = b.moid
LEFT JOIN (select cinvcode,cdefwarehouse,iSafeNum from inventory where 1=1 OR isnull(iSafeNum,0) <>0) C ON A.CINVCODE = C.CINVCODE
WHERE ISNULL(A.cbCloser,'') = ''
AND ISNULL(A.iQuantity,0) - ISNULL(A.iReceivedQTY,0) > 0
union all
--采购订单 +
SELECT 'Purchase Order' AS cType, RTRIM(B.cPOID+'|'+cast(a.iVouchRowNo as nvarchar(4))) AS cSource,B.cPOID as ccode,cast(a.iVouchRowNo as nvarchar(4)) as RowNo,A.cInvCode AS cInvCode, isnull(C.cdefwarehouse,'Blank') AS cWhCode,
ISNULL(A.iQuantity,0) - ISNULL(A.iReceivedQTY,0) - ISNULL(A.iArrQTY,0) AS iQuantity, 0 AS iSafeNum
FROM PO_Podetails a
left join PO_Pomain b on a.poid =b.poid
LEFT JOIN (select cinvcode,cdefwarehouse,iSafeNum from inventory where 1=1 OR isnull(iSafeNum,0) <>0) C ON A.CINVCODE = C.CINVCODE
WHERE ISNULL(A.cbCloser,'') = ''
AND ISNULL(A.iQuantity,0) - ISNULL(A.iReceivedQTY,0) - ISNULL(A.iArrQTY,0) > 0
UNION ALL
--采购在检量
SELECT 'Purchase Inspection' as cType,'' as cSource,'' as ccode,'' as RowNo,A.CINVCODE as cInvCode,isnull(E.cdefwarehouse,'Blank') as cWhCode,
A.FQUANTITY-ISNULL(C.FsumQuantity,0)-ISNULL(C.FDISQUANTITY,0) as iQuantity,0 as iSafeNum
FROM QMINSPECTVOUCHERS A
LEFT JOIN QMINSPECTVOUCHER B ON A.ID = B.ID
LEFT JOIN QMCHECKVOUCHER C ON A.AUTOID = C.INSPECTAUTOID
LEFT JOIN QMREJECTVOUCHER D ON D.CHECKID = C.ID
LEFT JOIN (select cinvcode,cdefwarehouse,iSafeNum from inventory where 1=1 OR isnull(iSafeNum,0) <>0) E ON A.CINVCODE = E.CINVCODE
WHERE B.CVOUCHTYPE = 'QM01' AND A.FQUANTITY-ISNULL(C.FsumQuantity,0)-ISNULL(C.FDISQUANTITY,0)>0
union all
--销售订单 -
SELECT 'Sales Committments' AS cType, RTRIM(a.cSOCode+'|'+CONVERT(nvarchar,a.iRowNo)) AS cSource,a.cSOCode as ccode,CONVERT(nvarchar(4),a.iRowNo) as RowNo,a.cInvCode AS cInvCode, isnull(C.cdefwarehouse,'Blank') AS cWhCode,
(ISNULL(a.iQuantity,0) - ISNULL(a.iFHQuantity,0))*(-1) AS iQuantity, 0 AS iSafeNum
FROM SO_SODetails a
LEFT JOIN (select cinvcode,cdefwarehouse,iSafeNum from inventory where 1=1 OR isnull(iSafeNum,0) <>0) C ON A.CINVCODE = C.CINVCODE
WHERE ISNULL(a.cSCloser,'') = ''
AND ISNULL(a.iQuantity,0) - ISNULL(a.iFHQuantity,0) > 0
union all
--生产订单子件 -
SELECT
'Production Committments' AS cType,RTRIM(d.MoCode+'|'+CONVERT(nvarchar(4),b.SortSeq)+'|'+cast(a.sortSeq as nvarchar(4)))AS cSource, d.MoCode as ccode,CONVERT(nvarchar(4),b.SortSeq) as RowNo,a.InvCode AS cInvCode, isnull(a.whcode,isnull(C.cdefwarehouse,'Blank')) AS cWhCode,
(ISNULL(a.Qty,0) - ISNULL(a.IssQty,0))*(-1) AS iQuantity, 0 AS iSafeNum
FROM mom_moallocate a
left join mom_orderdetail b on a.modid = b.modid
LEFT JOIN (select cinvcode,cdefwarehouse,iSafeNum from inventory where 1=1 OR isnull(iSafeNum,0) <>0) C ON A.INVCODE = C.CINVCODE
left join mom_order d on d.moid = b.moid
WHERE b.Status = '3'
AND ISNULL(a.Qty,0) - ISNULL(a.IssQty,0) > 0
union all
--委外订单子件 -
SELECT
'OutSourcing Committments' AS cType,RTRIM(d.cCode+'|'+cast(b.iVouchRowNo as nvarchar(4))+'|'+cast(isnull(a.iRowNo,10) as nvarchar(4))) AS cSource,d.cCode as ccode,cast(b.iVouchRowNo as nvarchar(4)) as RowNo, a.cInvCode AS cInvCode, isnull(a.cwhcode,isnull(C.cdefwarehouse,'Blank')) AS cWhCode,
(ISNULL(a.iQuantity,0) - ISNULL(a.iSendQTY,0))*(-1) AS iQuantity, 0 AS iSafeNum
FROM OM_MOMaterials a
left join OM_MODetails b on a.MoDetailsID = b.MODetailsID
LEFT JOIN (select cinvcode,cdefwarehouse,iSafeNum from inventory where 1=1 OR isnull(iSafeNum,0) <>0) C ON A.cINVCODE = C.CINVCODE
left join OM_MOMain d on b.MOID = d.MOID
WHERE ISNULL(b.cbCloser,'') = ''
AND ISNULL(a.iQuantity,0) - ISNULL(a.iSendQTY ,0) > 0
go
/*
exec Def_InvCommittments_Summary '',''
select * from Temp_InvCommittmentsSum_34730
*/
GO
if exists(select name from sysobjects where name = 'Def_InvCommittments_Summary' and type = 'P')
BEGIN
DROP PROCEDURE Def_InvCommittments_Summary
END
go
Create Procedure Def_InvCommittments_Summary
@cSInvcode as nvarchar(60) = '',
@cEInvcode as nvarchar(60) = ''
with encryption
as
--清除临时表记录
BEGIN
if exists(SELECT name FROM SYSOBJECTS WHERE NAME LIKE 'TEMP_InvCommittmentsSum%' and DATEDIFF(mi, crdate, getdate())>=2)
BEGIN
DECLARE MyCursor CURSOR
FOR SELECT name FROM SYSOBJECTS WHERE NAME LIKE 'TEMP_InvCommittmentsSum%' and DATEDIFF(mi, crdate, getdate())>=2
--打开一个游标
OPEN MyCursor
--循环一个游标
DECLARE @TableName as nvarchar(60)
declare @sqlStr as nvarchar(2000)
FETCH NEXT FROM MyCursor INTO @TableName
WHILE @@FETCH_STATUS =0
BEGIN
set @sqlStr = ''
set @sqlStr = 'Drop table '+ @TableName
exec(@sqlStr)
FETCH NEXT FROM MyCursor INTO @TableName
END
--关闭游标
CLOSE MyCursor
--释放资源
DEALLOCATE MyCursor
END
declare @cTableName as nvarchar(60)
SET @cTableName='Temp_InvCommittmentsSum_'+RTRIM(cast(ceiling(rand(checksum(newid()))*100000) as nvarchar(7)))
DECLARE @STRSQL AS NVARCHAR(2000)
if exists(select name from sysobjects where name = @cTableName)
begin
set @STRSQL = N'Drop table '+@cTableName
exec(@STRSQL)
end
SET @STRSQL = ''
SET @STRSQL = N' Declare @iQty as decimal(12,2) SET @iQty = 0 '
SET @STRSQL =@STRSQL +N'select distinct cinvCode,cWhcode,@iQty as QTY1,@iQty as QTY2,@iQty as QTY3,@iQty as QTY4,@iQty as QTY5,@iQty as QTY6,'+
'@iQty as QTY7,@iQty as QTY8,@iQty as QTY9,@iQty as QTY10,@iQty as QTY11,@iQty as QTY12,@iQty as QTY13,@iQty as QTY14,@iQty as QTY15 into '+@cTableName+' from Def_InvCommittments_Info order by cinvCode,cWhcode'
exec
U8-MRP库存及供需平衡分析报表.rar （1个子文件） 
U8-MRP库存及供需平衡分析报表.sql 52KB资源评论
sooxwq
- 粉丝: 1
- 资源: 13
