1645 lines
46 KiB
Transact-SQL
1645 lines
46 KiB
Transact-SQL
USE [AIS20180209140748] go
|
||
/****** Object: StoredProcedure [dbo].[SeorderBomExpAND] Script Date: 2024/1/24 15:09:53 ******/
|
||
SET ansi_nulls ON go SET quoted_identifier ON go
|
||
--SELECT * FROM seorder WHERE FBillNo='test201706201'
|
||
--SELECT * FROM SEOrderEntry WHERE FInterID=19907
|
||
--UPDATE SEOrder SET FHeadSelfS0153='N' WHERE FInterID=19422
|
||
--[SeorderBomExpAND] 'KLSO-20/0032/1-D7','mornINgstar',0,16393
|
||
--USE AIS20180209140748
|
||
-- =============================================
|
||
-- Author: 汤道勇
|
||
-- Create date: 2019-10-18
|
||
-- Description: 订单需求计算
|
||
-- =============================================
|
||
ALTER PROCEDURE [dbo].[SeorderBomExpAND] ( @BillNo AS VARCHAR(100),
|
||
--销售订单编号
|
||
@UseName AS VARCHAR(50),
|
||
--评审用户名称
|
||
@FInterID INT = 0,
|
||
--销售订单内码
|
||
@UserID INT = 16393 --评审用户ID
|
||
) AS begin SET nocount ON SET ansi_warnings off DECLARE @FID INT
|
||
--declare @BillNo as varchar(100)
|
||
--declare @FInterID INT=0
|
||
--set @BillNo='LSO-0782/18/2-D9-1'
|
||
IF @FInterID = 0
|
||
SELECT top 1 @FID = finterid
|
||
FROM seorder
|
||
WHERE fbillno = @BillNo AND fcancellation = 0
|
||
ELSE
|
||
SET @FID = @FInterID --DECLARE @FID INT
|
||
--SET @FID=19917
|
||
DELETE FROM mrp WHERE fid = @FID
|
||
DELETE FROM mrpentry WHERE fid = @FID
|
||
DELETE FROM mrpentry1 WHERE fid = @FID
|
||
DELETE FROM mrpentry2 WHERE fid = @FID
|
||
DELETE FROM mrpentry3 WHERE fid = @FID
|
||
DELETE FROM mrpentry4 WHERE fid = @FID
|
||
CREATE TABLE #mutidata(
|
||
findex INT identity(1,1)
|
||
,fbominterid INT DEFAULT(0)
|
||
--BOM内码
|
||
,fitemid INT NULL --物料内码
|
||
,fneedqty DECIMAL(28,14) DEFAULT(0) NULL,fbomlevel INT NULL
|
||
--BOM层级 0为成品 ，1为成品下一层，依次类推)
|
||
,fparentid INT DEFAULT(0) NULL,frate DECIMAL(28,14) DEFAULT(0) NULL,
|
||
flevelstring VARCHAR(200) NULL,fbom INT,frootbomid INT DEFAULT(0),
|
||
forderinterid INT --订单内码
|
||
,forderentryid INT --订单行号
|
||
,f1 DECIMAL(28,14) DEFAULT(0) NULL -- --直接上级对当前物料的用量
|
||
,f2 DECIMAL(28,14) DEFAULT(0) NULL --直接上级物料的需求量
|
||
,f3 nvarchar(255) --物料长代码
|
||
,fnumber nvarchar(255) --物料长代码前3位
|
||
,fentryselfz0142 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0142 变动损耗%（一万以下）
|
||
,fentryselfz0143 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0143 变动损耗%（一至五万）
|
||
,fentryselfz0144 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0144 变动损耗%（五万以上）
|
||
,fentryselfz0145 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0145 粘锅料或5K以下固定值
|
||
,fentryselfz0151 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0151 其他固定值损耗
|
||
,fentryselfz0152 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0152 5000以下订单
|
||
,fentryselfz0153 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0153 变动损耗%（10万以上）
|
||
,forderdate datetime --销售订单日期
|
||
,funitid INT --BOM中用到的计量单位内码
|
||
,ferpclsid INT DEFAULT(0) --物料属性
|
||
,即时库存 DECIMAL(28,14) DEFAULT(0) --即时库存
|
||
,[在制量(未入库) ] DECIMAL(28,14) DEFAULT(0)
|
||
,已评审未出货的销售订单 DECIMAL(28,14) DEFAULT(0)
|
||
,投料单需求量 DECIMAL(28,14) DEFAULT(0)
|
||
,可用量 DECIMAL(28,14) DEFAULT(0)
|
||
,需生产数量 DECIMAL(28,14) DEFAULT(0)
|
||
,齐料日期 datetime
|
||
,开工日期 datetime
|
||
,完工日期 datetime
|
||
,要求到货日期 datetime
|
||
);
|
||
CREATE TABLE #mutidata2(
|
||
findex INT identity(1,1)
|
||
,fitemid INT NULL --物料内码
|
||
,fneedqty DECIMAL(28,14) DEFAULT(0) NULL
|
||
,frate DECIMAL(28,14) DEFAULT(0)NULL
|
||
,fbom INT,frootbomid INT DEFAULT(0),forderinterid INT --订单内码
|
||
,forderentryid INT --订单行号
|
||
,f1 DECIMAL(28,14) DEFAULT(0) NULL -- --直接上级对当前物料的用量
|
||
,f2 DECIMAL(28,14) DEFAULT(0) NULL --直接上级物料的需求量
|
||
,f3 nvarchar(255) --物料长代码
|
||
,fnumber nvarchar(255) --物料长代码前3位
|
||
,fentryselfz0142 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0142 变动损耗%（一万以下）
|
||
,fentryselfz0143 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0143 变动损耗%（一至五万）
|
||
,fentryselfz0144 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0144 变动损耗%（五万以上）
|
||
,fentryselfz0145 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0145 粘锅料或5K以下固定值
|
||
,fentryselfz0151 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0151 其他固定值损耗
|
||
,fentryselfz0152 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0152 5000以下订单
|
||
,fentryselfz0153 DECIMAL(28,14) DEFAULT(0)
|
||
--FEntrySelfZ0153 变动损耗%（10万以上）
|
||
,forderdate datetime --销售订单日期
|
||
,funitid INT --BOM中用到的计量单位内码
|
||
,ferpclsid INT DEFAULT(0) --物料属性
|
||
,齐料日期 datetime
|
||
,开工日期 datetime
|
||
,完工日期 datetime
|
||
,要求到货日期 datetime
|
||
)
|
||
CREATE UNIQUE clustered INDEX idx_findex ON #mutidata(findex);
|
||
CREATE nonclustered INDEX idx_fitemid ON #mutidata(fitemid);
|
||
CREATE nonclustered INDEX idx_flevelstring ON #mutidata(fbomlevel);
|
||
CREATE nonclustered INDEX idx_f3 ON #mutidata(f3);
|
||
CREATE nonclustered INDEX idx_fnumber ON #mutidata(fnumber);
|
||
CREATE UNIQUE clustered INDEX idx_findex ON #mutidata2(findex);
|
||
CREATE nonclustered INDEX idx_fitemid ON #mutidata2(fitemid);
|
||
CREATE nonclustered INDEX idx_fnumber ON #mutidata2(fnumber);
|
||
/*更新半成品的即时库存数量*/
|
||
SELECT a.fitemid,
|
||
Sum(fqty) fqty
|
||
INTO #icinventory
|
||
FROM icinventory a WITH ( nolock )
|
||
LEFT JOIN t_stock b WITH (nolock ) ON a.fstockid = b.fitemid
|
||
LEFT JOIN t_icitem c WITH (nolock ) ON a.fitemid = c.fitemid
|
||
WHERE
|
||
b.fproperty = 10
|
||
AND c.fnumber NOT LIKE '05.%'
|
||
GROUP BY a.fitemid
|
||
HAVING Sum(fqty) <> 0
|
||
/*更新半成品预计入库数量,只考虑已审核的产品入库单*/
|
||
|
||
SELECT
|
||
v1.fitemid
|
||
,Sum(v1.fauxqty - v1.fauxstockqty) AS fqty --计划生产数量-入库数量
|
||
INTO #yjrk
|
||
FROM
|
||
icmo v1 WITH(nolock)
|
||
INNER JOIN t_icitem t9 WITH(nolock) ON v1.fitemid = t9.fitemid
|
||
WHERE 1 = 1
|
||
AND t9.fitemid <> 0
|
||
AND v1.fauxqty - v1.fauxstockqty > 0
|
||
AND (
|
||
v1.ftrantype = 85
|
||
AND v1.ftype <> 11060
|
||
AND (v1.fstatus IN (1,2,5) --确认,下达状态的任务单
|
||
AND t9.ferpclsid IN (2,3)
|
||
AND v1.fcancellation = 0))
|
||
AND (t9.fnumber LIKE '03.%' OR t9.fnumber LIKE '04.%')
|
||
GROUP BY
|
||
v1.fitemid
|
||
|
||
/*投料单已投未领数量,05.开头的物料不需要考虑库存信息*/
|
||
SELECT
|
||
c.fitemid
|
||
,sum(
|
||
isnull(c.fauxqtymust * m.fcoefficient,0)+ isnull(
|
||
c.fauxqtysupply * m.fcoefficient,
|
||
0
|
||
)- isnull(
|
||
c.fauxstockqty * m.fcoefficient,0
|
||
)
|
||
) AS 'FQty' INTO #yjck--应发数量-已领数量
|
||
FROM
|
||
icmo a WITH(nolock)
|
||
INNER JOIN ppbom b WITH(nolock) ON a.finterid = b.ficmointerid
|
||
INNER JOIN ppbomentry c WITH(nolock) ON b.finterid = c.finterid
|
||
INNER JOIN t_measureunit m WITH(nolock) ON c.funitid = m.fmeasureunitid
|
||
INNER JOIN t_icitem n WITH(nolock) ON n.fitemid = c.fitemid
|
||
WHERE
|
||
1 = 1
|
||
AND a.fstatus IN(1,2,5)
|
||
AND (
|
||
isnull(c.fauxqtymust,0)+ isnull(c.fauxqtysupply,0)- isnull(c.fauxstockqty,0)
|
||
)> 0
|
||
AND n.fnumber NOT LIKE '05.%'
|
||
GROUP BY
|
||
c.fitemid
|
||
|
||
/*已审核未关闭的销售订单已评审未转任务单的成品和半成品*/
|
||
SELECT
|
||
fbase fitemid
|
||
,sum(CONVERT(FLOAT,ftext9)) fqty
|
||
INTO #seorder
|
||
FROM
|
||
mrpentry2 t1 WITH(nolock)
|
||
INNER JOIN seorder v1 WITH(nolock) ON t1.finteger2 = v1.finterid
|
||
WHERE 1 = 1
|
||
AND (v1.fchangemark = 0
|
||
AND (isnull(v1.fclasstypeid,0)<> 1007100)
|
||
AND (v1.fcancellation = 0 AND v1.fstatus > 2)
|
||
)
|
||
AND CONVERT(FLOAT,ftext9)> 0
|
||
AND t1.fcheckbox3 = 0
|
||
AND fbase > 0
|
||
GROUP BY
|
||
fbase
|
||
|
||
/**************************************************************1级 START ***************************************************************/
|
||
INSERT INTO #mutidata (
|
||
fbominterid
|
||
,fitemid
|
||
,fneedqty
|
||
,fbomlevel
|
||
,fparentid
|
||
,frate
|
||
,flevelstring
|
||
,fbom
|
||
,frootbomid
|
||
,forderinterid
|
||
,forderentryid
|
||
,forderdate
|
||
,funitid
|
||
,齐料日期
|
||
,开工日期
|
||
,完工日期
|
||
)
|
||
SELECT
|
||
a.finterid 'FBomInterid'
|
||
,se.fitemid 'FItemID'
|
||
,se.fqty *(m.fcoefficient / n.fcoefficient) 'FNeedQty'
|
||
,0 'FBomLevel'
|
||
,0 'FParentID'
|
||
,0 'FRate'
|
||
,0 'FLevelStrIng'
|
||
,a.fitemid 'FBom'
|
||
,a.finterid 'FRootBOMID'
|
||
,se.finterid 'FOrderInterID'
|
||
,se.fentryid 'FOrderEntryID'
|
||
,se.fdate '交货日期'
|
||
,a.funitid 'FUnitID'
|
||
,se.fentryselfs0177 '齐料日期'
|
||
,dateadd(day,2,se.fentryselfs0177) '开工日期'
|
||
,dateadd(day,-7,se.fdate) '完工日期'
|
||
FROM
|
||
seorder s WITH(nolock)
|
||
INNER JOIN seorderentry se WITH(nolock) ON s.finterid = se.finterid
|
||
LEFT JOIN icbom a WITH(nolock) ON a.fitemid = se.fitemid
|
||
INNER JOIN t_measureunit m WITH(nolock) ON se.funitid = m.fmeasureunitid
|
||
INNER JOIN t_measureunit n WITH(nolock) ON a.funitid = n.fmeasureunitid
|
||
WHERE
|
||
s.fcancellation = 0
|
||
AND isnull(s.fcheckerid,0)> 0
|
||
AND s.fbillno = '' --'20180418test'--
|
||
AND a.fusestatus = 1072 --使用状态的BOM
|
||
|
||
/*截取成品物料代码前4位编码，更新物料的属性字段*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
fnumber = CASE WHEN b.fnumber LIKE '04.%' THEN substring (b.fnumber,1,4) ELSE substring (b.fnumber,1,3) end
|
||
,f3 = b.fnumber
|
||
,a.ferpclsid = b.ferpclsid
|
||
FROM
|
||
#mutidata a WITH(nolock)
|
||
INNER JOIN t_icitem b WITH(nolock) ON a.fitemid = b.fitemid
|
||
|
||
/**************************************************************1级 END***************************************************************/
|
||
/**************************************************************2级 START ***************************************************************/
|
||
INSERT INTO #mutidata2 (
|
||
fitemid
|
||
,fneedqty
|
||
,frate
|
||
,fbom
|
||
,frootbomid
|
||
,forderinterid
|
||
,forderentryid
|
||
,f1
|
||
,f2
|
||
,b.fentryselfz0142
|
||
,b.fentryselfz0143
|
||
,b.fentryselfz0144
|
||
,b.fentryselfz0145
|
||
,b.fentryselfz0151
|
||
,b.fentryselfz0152
|
||
,b.fentryselfz0153
|
||
,forderdate
|
||
,funitid
|
||
,齐料日期 --,开工日期,完工日期,要求到货日期
|
||
)
|
||
SELECT
|
||
b.fitemid 'FItemID'
|
||
,0 'FNeedQty'
|
||
,0 'FRate'
|
||
,a.fbom 'FBom'
|
||
,frootbomid 'FRootBOMID'
|
||
,forderinterid 'FOrderInterID'
|
||
,forderentryid 'FOrderEntryID'
|
||
,b.fauxqty 'F1'
|
||
,a.fneedqty 'F2'
|
||
,b.fentryselfz0142
|
||
,b.fentryselfz0143
|
||
,b.fentryselfz0144
|
||
,b.fentryselfz0145
|
||
,b.fentryselfz0151
|
||
,b.fentryselfz0152
|
||
,b.fentryselfz0153
|
||
,a.forderdate 'FOrderDate'
|
||
,b.funitid 'FUnitID'
|
||
,a.齐料日期 '齐料日期'
|
||
FROM
|
||
#mutidata a
|
||
INNER JOIN icbomchild b WITH(nolock) ON a.fbominterid = b.finterid
|
||
WHERE
|
||
a.fbomlevel = 0
|
||
ORDER BY
|
||
b.fitemid,
|
||
forderentryid
|
||
|
||
--新增订单里的产品属性为外购不加控制
|
||
--**************************2020-08-04 add wfm**********************************************--
|
||
INSERT INTO #mutidata2 (
|
||
fitemid
|
||
,fneedqty
|
||
,frate
|
||
,fbom
|
||
,frootbomid
|
||
,forderinterid
|
||
,forderentryid
|
||
,f1
|
||
,f2
|
||
,b.fentryselfz0142
|
||
,b.fentryselfz0143
|
||
,b.fentryselfz0144
|
||
,b.fentryselfz0145
|
||
,b.fentryselfz0151
|
||
,b.fentryselfz0152
|
||
,b.fentryselfz0153
|
||
,forderdate
|
||
,funitid
|
||
,齐料日期 --,开工日期,完工日期,要求到货日期
|
||
)
|
||
SELECT
|
||
a.fitemid 'FItemID',
|
||
se.fqty 'FNeedQty',
|
||
0 'FRate',
|
||
0 'FBom',
|
||
0 'FRootBOMID',
|
||
se.finterid 'FOrderInterID',
|
||
se.fentryid 'FOrderEntryID',
|
||
1 'F1',
|
||
se.fqty 'F2',
|
||
0 fentryselfz0142,
|
||
0 fentryselfz0143,
|
||
0 fentryselfz0144,
|
||
0 fentryselfz0145,
|
||
0 fentryselfz0151,
|
||
0 fentryselfz0152,
|
||
0 fentryselfz0153,
|
||
s.fdate 'FOrderDate',
|
||
n.fmeasureunitid 'FUnitID',
|
||
se.fentryselfs0177 '齐料日期'
|
||
FROM
|
||
seorder s WITH(nolock)
|
||
INNER JOIN seorderentry se WITH(nolock) ON s.finterid = se.finterid
|
||
INNER JOIN t_icitem a WITH(nolock) ON a.fitemid = se.fitemid
|
||
INNER JOIN t_measureunit m WITH(nolock) ON se.funitid = m.fmeasureunitid
|
||
INNER JOIN t_measureunit n WITH(nolock) ON a.funitid = n.fmeasureunitid
|
||
WHERE
|
||
s.fcancellation = 0
|
||
AND isnull(s.fcheckerid,0)> 0
|
||
AND s.fbillno = @BillNo --''KLSO-20/0032/1-D7''--
|
||
AND a.ferpclsid = 1 --外购属性
|
||
|
||
--******************************end******************************************--
|
||
/*截取物料代码前3位(04.开头就截取4位)编码，更新物料的属性字段*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
fnumber = CASE WHEN b.fnumber LIKE '04.%' THEN substring (b.fnumber,1,4) ELSE substring (b.fnumber,1,3) end,
|
||
f3 = b.fnumber,
|
||
a.ferpclsid = b.ferpclsid
|
||
FROM
|
||
#mutidata2 a INNER JOIN t_icitem b WITH(nolock) ON a.fitemid = b.fitemid
|
||
|
||
--用于成品的材料要求到货日期=成品的齐料日期-3
|
||
UPDATE #mutidata2 SET 要求到货日期=dateadd(day,-3,齐料日期) WHERE ferpclsid=1
|
||
|
||
--樽盖(03.、04.R)半成品开工日期=齐料日期-5，完工日期=齐料日期-2
|
||
--UPDATE #Mutidata2 SET 开工日期=DATEADD(DAY,-5,齐料日期),完工日期=DATEADD(DAY,-2,齐料日期) WHERE FErpClsID<>1 AND FNumber IN ('03.','04.R')
|
||
--2019-10-15 汤工，之前设定的樽盖半成品现在帮忙改成开工日期＝齐料日期-7；完工日期＝齐料日期-3；其他的暂不需要变更
|
||
UPDATE #mutidata2 SET 开工日期=dateadd(day,-7,齐料日期),完工日期= dateadd(day,-3,齐料日期) WHERE ferpclsid<>1 AND fnumber IN ('03.','04.R')
|
||
--水剂(05.)半成品开工日期=齐料日期-7，完工日期=齐料日期-4
|
||
UPDATE #mutidata2 SET 开工日期=dateadd(day,-7,齐料日期),完工日期= dateadd(day,-4,齐料日期) WHERE ferpclsid<>1 AND fnumber ='05.'
|
||
/*第二层的损耗数根据第一层的成品数来判断*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
frate = (
|
||
CASE WHEN f2 <= 10000
|
||
AND f2 > 5000 THEN f2 * f1 * isnull (a.fentryselfz0142,0)/ 100.00 + isnull(a.fentryselfz0145,0)+ isnull(a.fentryselfz0151,0) WHEN f2 <= 5000 THEN f2 * f1 * isnull(a.fentryselfz0142,0)/ 100.00 + isnull(a.fentryselfz0145,0)+ isnull(a.fentryselfz0151,0)+ isnull(a.fentryselfz0152,0) WHEN f2 >= 100000 THEN f2 * f1 * isnull(a.fentryselfz0153,0)/ 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) WHEN f2 > 10000
|
||
AND f2 <= 50000 THEN isnull(a.fentryselfz0143,0)* f2 * f1 / 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) WHEN f2 > 50000
|
||
AND f2 < 100000 THEN isnull(a.fentryselfz0144,0)* f2 * f1 / 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) ELSE 0 end
|
||
)
|
||
FROM
|
||
#mutidata2 a WHERE 1=1
|
||
|
||
/*第二层的需求量=第一层的数量*第二层的用量+第一层数量对应的损耗*/
|
||
--******2020-08-04 ADD FBom<>0***********************
|
||
UPDATE #mutidata2 SET fneedqty=f2*f1+frate WHERE 1=1 AND fbom<>0
|
||
/*相同成品、原材料进行汇总*/
|
||
INSERT INTO #mutidata ( fitemid,fneedqty,fbomlevel,frate,flevelstring,fbom,
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
f1,
|
||
f2,
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
b.fentryselfz0153,
|
||
forderdate,
|
||
funitid,
|
||
ferpclsid,
|
||
fnumber,
|
||
f3,
|
||
齐料日期,
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
)
|
||
SELECT
|
||
fitemid,
|
||
sum(fneedqty) 'FNeedQty',
|
||
1 'FBomLevel',
|
||
sum(frate) 'FRate',
|
||
'.1' 'FLevelStrIng',
|
||
max(fbom) 'FBom',
|
||
max(frootbomid) 'FRootBOMID',
|
||
max(forderinterid) 'FOrderInterID',
|
||
min(forderentryid) 'FOrderEntryID',
|
||
max(f1) 'F1',
|
||
max(f2) 'F2',
|
||
max(fentryselfz0142) 'FEntrySelfZ0142',
|
||
max(fentryselfz0143) 'FEntrySelfZ0143',
|
||
max(fentryselfz0144) 'FEntrySelfZ0144',
|
||
max(fentryselfz0145) 'FEntrySelfZ0145',
|
||
max(fentryselfz0151) 'FEntrySelfZ0151',
|
||
max(fentryselfz0152) 'FEntrySelfZ0152',
|
||
max(fentryselfz0153) 'FEntrySelfZ0153',
|
||
min(forderdate) 'FOrderDate',
|
||
funitid 'FUnitID',
|
||
ferpclsid 'FErpClsID',
|
||
max(fnumber) 'FNumber',
|
||
max(f3) 'F3',
|
||
min(齐料日期) '齐料日期',
|
||
min(开工日期) '开工日期',
|
||
min(完工日期) '完工日期',
|
||
min(要求到货日期) '要求到货日期'
|
||
FROM
|
||
#mutidata2
|
||
WHERE
|
||
(
|
||
f3 NOT LIKE '02.86%'
|
||
AND f3 NOT LIKE '02.87%'
|
||
AND f3 NOT LIKE '05.%'
|
||
)
|
||
GROUP BY
|
||
fitemid,
|
||
funitid,
|
||
ferpclsid,
|
||
fnumber
|
||
UNION ALL
|
||
SELECT
|
||
fitemid,
|
||
sum(fneedqty) 'FNeedQty',
|
||
1 'FBomLevel',
|
||
sum(frate) 'FRate',
|
||
'.1' 'FLevelStrIng',
|
||
max(fbom) 'FBom',
|
||
max(frootbomid) 'FRootBOMID',
|
||
max(forderinterid) 'FOrderInterID',
|
||
min(forderentryid) 'FOrderEntryID',
|
||
max(f1) 'F1',
|
||
max(f2) 'F2',
|
||
max(fentryselfz0142),
|
||
max(fentryselfz0143),
|
||
max(fentryselfz0144),
|
||
max(fentryselfz0145),
|
||
max(fentryselfz0151),
|
||
max(fentryselfz0152),
|
||
max(fentryselfz0153) 'FEntrySelfZ0153',
|
||
min(forderdate) 'FOrderDate',
|
||
funitid 'FUnitID',
|
||
ferpclsid 'FErpClsID',
|
||
max(fnumber) 'FNumber',
|
||
max(f3) 'F3',
|
||
min(齐料日期) '齐料日期',
|
||
min(开工日期) '开工日期',
|
||
min(完工日期) '完工日期',
|
||
min(要求到货日期) '要求到货日期'
|
||
FROM
|
||
#mutidata2
|
||
WHERE
|
||
f3 LIKE '02.86%'
|
||
OR f3 LIKE '02.87%'
|
||
OR f3 LIKE '05.%'
|
||
GROUP BY
|
||
fitemid,
|
||
funitid,
|
||
ferpclsid,
|
||
fnumber,
|
||
forderinterid,
|
||
forderentryid
|
||
|
||
/*更新半成品的即时库存数量*/
|
||
UPDATE a SET a.即时库存 = b.fqty FROM #mutidata a INNER JOIN #icinventory b ON a.fitemid=b.fitemid WHERE a.fbomlevel=1 --AND FErpClsID=2
|
||
/*更新半成品预计入库数量*/
|
||
UPDATE a SET a.[在制量(未入库) ] = b.fqty FROM #mutidata a INNER JOIN #yjrk b ON a.fitemid=b.fitemid WHERE a.fbomlevel=1 --AND FErpClsID=2
|
||
/*投料单已投未领数量*/
|
||
UPDATE a SET a.投料单需求量 = b.fqty FROM #mutidata a INNER JOIN #yjck b ON a.fitemid=b.fitemid WHERE a.fbomlevel=1 --AND FErpClsID=2
|
||
/**************************************************************2级 END***************************************************************/
|
||
/**************************************************************3级 START ***************************************************************/
|
||
DELETE FROM #mutidata2
|
||
INSERT INTO #mutidata2 ( fitemid,fneedqty,frate,fbom,
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
f1,
|
||
f2,
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
b.fentryselfz0153,
|
||
forderdate,
|
||
funitid,
|
||
齐料日期,
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
)
|
||
SELECT
|
||
b.fitemid,
|
||
0 'FNeedQty',
|
||
0 'FRate',
|
||
a.fbom 'FBom',
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
b.fauxqty 'F1',
|
||
CASE WHEN isnull(a.fneedqty,0)+ isnull(a.投料单需求量,0)- isnull(a.即时库存,0)- isnull(
|
||
a.[在制量(未入库) ],
|
||
0
|
||
)> 0 THEN isnull(a.fneedqty,0)+ isnull(a.投料单需求量,0)- isnull(a.即时库存,0)- isnull(
|
||
a.[在制量(未入库) ],
|
||
0
|
||
) ELSE 0 end 'F2',
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
b.fentryselfz0153,
|
||
a.forderdate,
|
||
b.funitid,
|
||
齐料日期 '齐料日期',
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
FROM
|
||
#mutidata a INNER JOIN icbom t WITH(nolock) ON a.fitemid = t.fitemid INNER
|
||
JOIN icbomchild b WITH(nolock) ON t.finterid = b.finterid
|
||
WHERE
|
||
t.fusestatus = 1072
|
||
AND a.fbomlevel = 1
|
||
|
||
--SELECT * FROM #Mutidata2 where FErpClsID<>1
|
||
/*截取物料代码前3位(04.开头就截取4位)编码，更新物料的属性字段*/
|
||
UPDATE a SET fnumber = CASE WHEN b.fnumber LIKE '04.%' THEN substring(b.fnumber,1,4) ELSE substring (b.fnumber,1,3) end,f3 = b.fnumber,a.ferpclsid = b.ferpclsid FROM #mutidata2 a INNER JOIN t_icitem b WITH(nolock) ON a.fitemid=b.fitemid WHERE 1=1
|
||
--用于半成品的材料要求到货日期=半成品的开工日期-3
|
||
UPDATE #mutidata2 SET 要求到货日期=dateadd(day,-3,开工日期) WHERE ferpclsid=1
|
||
UPDATE #mutidata2 SET 开工日期=NULL,完工日期=NULL WHERE ferpclsid=1
|
||
--樽盖(03.、04.R)半成品开工日期=齐料日期-5，完工日期=齐料日期-2
|
||
--UPDATE #Mutidata2 SET 开工日期=DATEADD(DAY,-5,齐料日期),完工日期=DATEADD(DAY,-2,齐料日期) WHERE FErpClsID<>1 AND FNumber IN ('03.','04.R')
|
||
--2019-10-15 汤工，之前设定的樽盖半成品现在帮忙改成开工日期＝齐料日期-7；完工日期＝齐料日期-3；其他的暂不需要变更
|
||
UPDATE #mutidata2 SET 开工日期=dateadd(day,-7,齐料日期),完工日期=dateadd(day,-3,齐料日期) WHERE ferpclsid<>1 AND fnumber IN ('03.','04.R')
|
||
--水剂(05.)半成品开工日期=齐料日期-7，完工日期=齐料日期-4
|
||
UPDATE #mutidata2 SET 开工日期=dateadd(day,-7,齐料日期),完工日期=dateadd(day,-4,齐料日期) WHERE ferpclsid<>1 AND fnumber ='05.'
|
||
UPDATE #mutidata2 SET 要求到货日期=NULL WHERE ferpclsid<>1
|
||
--SELECT * FROM #Mutidata2
|
||
/*第三层的损耗数根据第二层的成品数来判断*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
frate = (
|
||
CASE WHEN f2 <= 10000
|
||
AND f2 > 5000 THEN f2 * f1 * isnull (a.fentryselfz0142,0)/ 100.00 + isnull(a.fentryselfz0145,0)+ isnull(a.fentryselfz0151,0) WHEN f2 <= 5000 THEN f2 * f1 * isnull(a.fentryselfz0142,0)/ 100.00 + isnull(a.fentryselfz0145,0)+ isnull(a.fentryselfz0151,0)+ isnull(a.fentryselfz0152,0) WHEN f2 >= 100000 THEN f2 * f1 * isnull(a.fentryselfz0153,0)/ 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) WHEN f2 > 10000
|
||
AND f2 <= 50000 THEN isnull(a.fentryselfz0143,0)* f2 * f1 / 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) WHEN f2 > 50000
|
||
AND f2 < 100000 THEN isnull(a.fentryselfz0144,0)* f2 * f1 / 100.00 + isnull(a.fentryselfz0151,0) +(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) ELSE 0 end
|
||
)
|
||
FROM
|
||
#mutidata2 a WHERE 1=1
|
||
|
||
/*第三层的需求量=第二层的数量*第三层的用量+第二层数量对应的损耗*/
|
||
UPDATE a SET fneedqty = f2 * f1 + frate FROM #mutidata2 a WHERE 1=1
|
||
/*相同成品、原材料进行汇总*/
|
||
INSERT INTO #mutidata ( fitemid,fneedqty,fbomlevel,frate,flevelstring,fbom,
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
f1,
|
||
f2,
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
b.fentryselfz0153,
|
||
forderdate,
|
||
funitid,
|
||
ferpclsid,
|
||
fnumber,
|
||
f3,
|
||
齐料日期,
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
)
|
||
SELECT
|
||
fitemid 'FItemID',
|
||
sum(fneedqty) 'FNeedQty',
|
||
2 'FBomLevel',
|
||
sum(frate) 'FRate',
|
||
'.2' 'FLevelStrIng',
|
||
max(fbom) 'FBom',
|
||
max(frootbomid) 'FRootBOMID',
|
||
max(forderinterid) 'FOrderInterID',
|
||
min(forderentryid) 'FOrderEntryID',
|
||
max(f1) 'F1',
|
||
max(f2) 'F2',
|
||
max(fentryselfz0142),
|
||
max(fentryselfz0143),
|
||
max(fentryselfz0144),
|
||
max(fentryselfz0145),
|
||
max(fentryselfz0151),
|
||
max(fentryselfz0152),
|
||
max(fentryselfz0153) 'FEntrySelfZ0153',
|
||
min(forderdate),
|
||
funitid 'FUnitID',
|
||
ferpclsid 'FErpClsID',
|
||
fnumber 'FNumber',
|
||
max(f3) 'F3',
|
||
min(齐料日期),
|
||
min(开工日期) 开工日期,
|
||
min(完工日期) 完工日期,
|
||
min(要求到货日期) 要求到货日期
|
||
FROM
|
||
#mutidata2 WHERE f3 NOT LIKE '02.86%' AND f3 NOT LIKE '02.87%' AND f3 NOT LIKE
|
||
'05.%'
|
||
GROUP BY
|
||
fitemid,
|
||
funitid,
|
||
ferpclsid,
|
||
fnumber
|
||
UNION ALL
|
||
SELECT
|
||
fitemid,
|
||
sum(fneedqty) fneedqty,
|
||
2 'FBomLevel',
|
||
sum(frate) frate,
|
||
'.2' flevelstring,
|
||
max(fbom) fbom,
|
||
max(frootbomid) frootbomid,
|
||
max(forderinterid) forderinterid,
|
||
min(forderentryid) forderentryid,
|
||
max(f1) f1,
|
||
max(f2) f2,
|
||
max(fentryselfz0142),
|
||
max(fentryselfz0143),
|
||
max(fentryselfz0144),
|
||
max(fentryselfz0145),
|
||
max(fentryselfz0151),
|
||
max(fentryselfz0152),
|
||
max(fentryselfz0153) 'FEntrySelfZ0153',
|
||
min(forderdate),
|
||
max(funitid),
|
||
max(ferpclsid),
|
||
max(fnumber),
|
||
max(f3),
|
||
min(齐料日期),
|
||
min(开工日期) 开工日期,
|
||
min(完工日期) 完工日期,
|
||
min(要求到货日期) 要求到货日期
|
||
FROM
|
||
#mutidata2 WHERE f3 LIKE
|
||
'02.86%'
|
||
OR f3 LIKE '02.87%'
|
||
OR f3 LIKE '05.%'
|
||
GROUP BY
|
||
fitemid,
|
||
ferpclsid,
|
||
forderinterid,
|
||
forderentryid
|
||
|
||
/*更新半成品的即时库存数量*/
|
||
UPDATE a SET a.即时库存 = b.fqty FROM #mutidata a INNER JOIN #icinventory b ON a.fitemid=b.fitemid WHERE a.fbomlevel=2 --AND FErpClsID=2
|
||
/*更新半成品预计入库数量*/
|
||
UPDATE a SET a.[在制量(未入库) ] = b.fqty FROM #mutidata a INNER JOIN #yjrk b ON a.fitemid=b.fitemid WHERE a.fbomlevel=2 --AND FErpClsID=2
|
||
/*投料单已投未领数量*/
|
||
UPDATE a SET a.投料单需求量 = b.fqty FROM #mutidata a INNER JOIN #yjck b ON a.fitemid=b.fitemid WHERE a.fbomlevel=2 --AND FErpClsID=2
|
||
/**************************************************************3级 END***************************************************************/
|
||
/**************************************************************4级 START ***************************************************************/
|
||
DELETE FROM #mutidata2
|
||
INSERT INTO #mutidata2 ( fitemid,fneedqty,frate,fbom,
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
f1,
|
||
f2,
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
fentryselfz0153,
|
||
forderdate,
|
||
funitid,
|
||
齐料日期,
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
)
|
||
SELECT
|
||
b.fitemid,
|
||
0 fneedqty,
|
||
0 frate,
|
||
a.fbom fbom,
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
b.fauxqty f1,
|
||
CASE WHEN isnull(a.fneedqty,0)+ isnull(a.投料单需求量,0)- isnull(a.即时库存,0)- isnull(
|
||
a.[在制量(未入库) ],
|
||
0
|
||
)> 0 THEN isnull(a.fneedqty,0)+ isnull(a.投料单需求量,0)- isnull(a.即时库存,0)- isnull(
|
||
a.[在制量(未入库) ],
|
||
0
|
||
) ELSE 0 end f2,
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
b.fentryselfz0153,
|
||
a.forderdate,
|
||
b.funitid,
|
||
齐料日期 '齐料日期',
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
FROM
|
||
#mutidata a INNER JOIN icbom t WITH(nolock) ON a.fitemid = t.fitemid INNER
|
||
JOIN icbomchild b WITH(nolock) ON t.finterid = b.finterid
|
||
WHERE
|
||
t.fusestatus = 1072
|
||
AND a.fbomlevel = 2
|
||
|
||
/*截取物料代码前3位(04.开头就截取4位)编码，更新物料的属性字段*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
fnumber = CASE WHEN b.fnumber LIKE '04.%' THEN substring(b.fnumber,1,4) ELSE substring(b.fnumber,1,3) end,
|
||
f3 = b.fnumber,
|
||
a.ferpclsid = b.ferpclsid
|
||
FROM
|
||
#mutidata2 a INNER JOIN t_icitem b WITH(nolock) ON a.fitemid=b.fitemid
|
||
|
||
--用于半成品的材料要求到货日期=半成品的开工日期-3
|
||
UPDATE
|
||
#mutidata2 SET 要求到货日期=dateadd(day,-3,开工日期) WHERE
|
||
ferpclsid = 1
|
||
UPDATE
|
||
#mutidata2 SET 开工日期=NULL,完工日期=NULL WHERE
|
||
ferpclsid = 1
|
||
|
||
--樽盖(03.、04.R)半成品开工日期=齐料日期-5，完工日期=齐料日期-2
|
||
--UPDATE #Mutidata2 SET 开工日期=DATEADD(DAY,-5,齐料日期),完工日期=DATEADD(DAY,-2,齐料日期) WHERE FErpClsID<>1 AND FNumber IN ('03.','04.R')
|
||
--2019-10-15 汤工，之前设定的樽盖半成品现在帮忙改成开工日期＝齐料日期-7；完工日期＝齐料日期-3；其他的暂不需要变更
|
||
UPDATE
|
||
#mutidata2 SET 开工日期=dateadd(day,-7,齐料日期),完工日期=
|
||
dateadd(day,-3,齐料日期)
|
||
WHERE
|
||
ferpclsid <> 1
|
||
AND fnumber IN ('03.','04.R')
|
||
|
||
--水剂(05.)半成品开工日期=齐料日期-7，完工日期=齐料日期-4
|
||
UPDATE
|
||
#mutidata2 SET 开工日期=dateadd(day,-7,齐料日期),完工日期=
|
||
dateadd(day,-4,齐料日期)
|
||
WHERE
|
||
ferpclsid <> 1
|
||
AND fnumber = '05.'
|
||
|
||
UPDATE
|
||
#mutidata2 SET 要求到货日期=NULL WHERE ferpclsid<>1
|
||
|
||
/*第四层的损耗数根据第三层的成品数来判断*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
frate = (
|
||
CASE WHEN f2 <= 10000
|
||
AND f2 > 5000 THEN f2 * f1 * isnull (a.fentryselfz0142,0)/ 100.00 + isnull(a.fentryselfz0145,0)+ isnull(a.fentryselfz0151,0) WHEN f2 <= 5000 THEN f2 * f1 * isnull(a.fentryselfz0142,0)/ 100.00 + isnull(a.fentryselfz0145,0)+ isnull(a.fentryselfz0151,0)+ isnull(a.fentryselfz0152,0) WHEN f2 >= 100000 THEN f2 * f1 * isnull(a.fentryselfz0153,0)/ 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) WHEN f2 > 10000
|
||
AND f2 <= 50000 THEN isnull(a.fentryselfz0143,0)* f2 * f1 / 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) WHEN f2 > 50000
|
||
AND f2 < 100000 THEN isnull(a.fentryselfz0144,0)* f2 * f1 / 100.00 + isnull(a.fentryselfz0151,0) +(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) ELSE 0 end
|
||
)
|
||
FROM
|
||
#mutidata2 a
|
||
--WHERE a.FBomLevel=3
|
||
/*相同成品、原材料进行汇总*/
|
||
INSERT INTO #mutidata ( fitemid,fneedqty,fbomlevel,frate,flevelstring,fbom,
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
f1,
|
||
f2,
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
b.fentryselfz0153,
|
||
forderdate,
|
||
funitid,
|
||
ferpclsid,
|
||
fnumber,
|
||
f3,
|
||
齐料日期,
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
)
|
||
SELECT
|
||
fitemid,
|
||
sum(fneedqty) fneedqty,
|
||
3 'FBomLevel',
|
||
sum(frate) frate,
|
||
'.3' flevelstring,
|
||
max(fbom) fbom,
|
||
max(frootbomid) frootbomid,
|
||
max(forderinterid) forderinterid,
|
||
min(forderentryid) forderentryid,
|
||
max(f1) f1,
|
||
max(f2) f2,
|
||
max(fentryselfz0142),
|
||
max(fentryselfz0143),
|
||
max(fentryselfz0144),
|
||
max(fentryselfz0145),
|
||
max(fentryselfz0151),
|
||
max(fentryselfz0152),
|
||
max(fentryselfz0153),
|
||
min(forderdate),
|
||
max(funitid),
|
||
max(ferpclsid),
|
||
max(fnumber),
|
||
max(f3),
|
||
min(齐料日期),
|
||
min(开工日期) 开工日期,
|
||
min(完工日期) 完工日期,
|
||
min(要求到货日期) 要求到货日期
|
||
FROM
|
||
#mutidata2 WHERE f3 NOT LIKE '02.86%' AND f3 NOT LIKE
|
||
'02.87%'
|
||
AND f3 NOT LIKE '05.%'
|
||
GROUP BY
|
||
fitemid,
|
||
ferpclsid
|
||
UNION ALL
|
||
SELECT
|
||
fitemid,
|
||
sum(fneedqty) fneedqty,
|
||
3 'FBomLevel',
|
||
sum(frate) frate,
|
||
'.3' flevelstring,
|
||
max(fbom) fbom,
|
||
max(frootbomid) frootbomid,
|
||
max(forderinterid) forderinterid,
|
||
min(forderentryid) forderentryid,
|
||
max(f1) f1,
|
||
max(f2) f2,
|
||
max(fentryselfz0142),
|
||
max(fentryselfz0143),
|
||
max(fentryselfz0144),
|
||
max(fentryselfz0145),
|
||
max(fentryselfz0151),
|
||
max(fentryselfz0152),
|
||
max(fentryselfz0153),
|
||
min(forderdate),
|
||
max(funitid),
|
||
max(ferpclsid),
|
||
max(fnumber),
|
||
max(f3),
|
||
min(齐料日期),
|
||
min(开工日期) 开工日期,
|
||
min(完工日期) 完工日期,
|
||
min(要求到货日期) 要求到货日期
|
||
FROM
|
||
#mutidata2 WHERE
|
||
f3 LIKE '02.86%'
|
||
OR f3 LIKE '02.87%'
|
||
OR f3 LIKE '05.%'
|
||
GROUP BY
|
||
fitemid,
|
||
ferpclsid,
|
||
forderinterid,
|
||
forderentryid
|
||
|
||
/*第四层的需求量=第三层的数量*第四层的用量+第三层数量对应的损耗*/
|
||
UPDATE a SET fneedqty = f2 * f1 + frate FROM #mutidata a WHERE fbomlevel=3
|
||
/*更新半成品的即时库存数量*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
a.即时库存 = b.fqty
|
||
FROM
|
||
#mutidata a INNER JOIN #icinventory b
|
||
ON a.fitemid = b.fitemid
|
||
WHERE
|
||
a.fbomlevel = 3
|
||
--AND FErpClsID=2
|
||
/*更新半成品预计入库数量*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
a.[在制量(未入库) ] = b.fqty
|
||
FROM
|
||
#mutidata a INNER JOIN
|
||
#yjrk b ON a.fitemid=b.fitemid WHERE a.fbomlevel=3
|
||
--AND FErpClsID=2
|
||
/*投料单已投未领数量*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
a.投料单需求量 = b.fqty
|
||
FROM
|
||
#mutidata a INNER JOIN #yjck b
|
||
ON a.fitemid = b.fitemid
|
||
WHERE
|
||
a.fbomlevel = 3
|
||
--AND FErpClsID=2
|
||
/**************************************************************4级 END***************************************************************/
|
||
/**************************************************************5级 START ***************************************************************/
|
||
INSERT INTO #mutidata ( fitemid,fneedqty,fbomlevel,frate,flevelstring,fbom,
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
f1,
|
||
f2,
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
b.fentryselfz0153,
|
||
forderdate,
|
||
funitid,
|
||
齐料日期,
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
)
|
||
SELECT
|
||
b.fitemid,
|
||
0 fneedqty,
|
||
4 'FBomLevel',
|
||
0 frate,
|
||
'..4' flevelstring,
|
||
a.fbom fbom,
|
||
frootbomid,
|
||
forderinterid,
|
||
forderentryid,
|
||
b.fauxqty f1,
|
||
CASE WHEN isnull(a.fneedqty,0)+ isnull(a.投料单需求量,0)- isnull(a.即时库存,0)- isnull(
|
||
a.[在制量(未入库) ],
|
||
0
|
||
)> 0 THEN isnull(a.fneedqty,0) + isnull(a.投料单需求量,0)- isnull(a.即时库存,0)- isnull(
|
||
a.[在制量(未入库) ],
|
||
0
|
||
) ELSE 0 end f2,
|
||
b.fentryselfz0142,
|
||
b.fentryselfz0143,
|
||
b.fentryselfz0144,
|
||
b.fentryselfz0145,
|
||
b.fentryselfz0151,
|
||
b.fentryselfz0152,
|
||
b.fentryselfz0153,
|
||
a.forderdate,
|
||
b.funitid,
|
||
齐料日期,
|
||
开工日期,
|
||
完工日期,
|
||
要求到货日期
|
||
FROM
|
||
#mutidata a
|
||
INNER JOIN icbom t WITH(nolock) ON a.fitemid = t.fitemid
|
||
INNER JOIN icbomchild b WITH(nolock) ON t.finterid = b.finterid
|
||
WHERE
|
||
t.fusestatus = 1072
|
||
AND a.fbomlevel = 3
|
||
|
||
/*截取物料代码前3位编码，更新物料的属性字段*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
fnumber = CASE WHEN b.fnumber LIKE '04.%' THEN substring(b.fnumber,1,4) ELSE substring(b.fnumber,1,3) end,
|
||
f3 = b.fnumber,
|
||
a.ferpclsid = b.ferpclsid
|
||
FROM
|
||
#mutidata a INNER JOIN t_icitem b WITH(nolock) ON a.fitemid=b.fitemid
|
||
WHERE
|
||
a.fbomlevel = 4
|
||
|
||
/*第四层的损耗数根据第三层的成品数来判断*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
frate = (
|
||
CASE WHEN f2 <= 10000
|
||
AND f2 > 5000 THEN f2 * f1 * isnull (a.fentryselfz0142,0)/ 100.00 + isnull(a.fentryselfz0145,0)+ isnull(a.fentryselfz0151,0) WHEN f2 <= 5000 THEN f2 * f1 * isnull(a.fentryselfz0142,0)/ 100.00 + isnull(a.fentryselfz0145,0)+ isnull(a.fentryselfz0151,0)+ isnull(a.fentryselfz0152,0) WHEN f2 >= 100000 THEN f2 * f1 * isnull(a.fentryselfz0153,0)/ 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) WHEN f2 > 10000
|
||
AND f2 <= 50000 THEN isnull(a.fentryselfz0143,0)* f2 * f1 / 100.00 + isnull(a.fentryselfz0151,0)+(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) WHEN f2 > 50000
|
||
AND f2 < 100000 THEN isnull(a.fentryselfz0144,0)* f2 * f1 / 100.00 + isnull(a.fentryselfz0151,0) +(
|
||
CASE WHEN a.fnumber = '05.' THEN isnull(a.fentryselfz0145,0) ELSE 0 end
|
||
) ELSE 0 end
|
||
)
|
||
FROM
|
||
#mutidata a WHERE a.fbomlevel=4
|
||
|
||
/*第四层的需求量=第三层的数量*第四层的用量+第三层数量对应的损耗*/
|
||
UPDATE a SET fneedqty = f2 * f1 + frate FROM #mutidata a WHERE fbomlevel=4
|
||
/*更新半成品的即时库存数量*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
a.即时库存 = b.fqty
|
||
FROM
|
||
#mutidata a INNER JOIN #icinventory b
|
||
ON a.fitemid = b.fitemid
|
||
WHERE
|
||
a.fbomlevel = 4
|
||
--AND FErpClsID=2
|
||
/*更新半成品预计入库数量*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
a.[在制量(未入库) ] = b.fqty
|
||
FROM
|
||
#mutidata a INNER JOIN
|
||
#yjrk b ON a.fitemid=b.fitemid WHERE a.fbomlevel=4
|
||
--AND FErpClsID=2
|
||
/*投料单已投未领数量*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
a.投料单需求量 = b.fqty
|
||
FROM
|
||
#mutidata a INNER JOIN #yjck b
|
||
ON a.fitemid = b.fitemid
|
||
WHERE
|
||
a.fbomlevel = 3
|
||
--AND FErpClsID=2
|
||
/**************************************************************5级 END***************************************************************/
|
||
UPDATE
|
||
a
|
||
SET
|
||
a.已评审未出货的销售订单 = b.fqty
|
||
FROM
|
||
#mutidata a INNER
|
||
JOIN #seorder b ON a.fitemid=b.fitemid WHERE 1=1
|
||
|
||
CREATE TABLE [dbo].[ #MrpEntry1]( [FIndex] [INT] identity(1,1),[FID] [INT] NOT NULL,[FQty11] [DECIMAL](23,10) DEFAULT (0),
|
||
--订单数量
|
||
[FDate2] [DATETIME] NULL,
|
||
[FDate3] [DATETIME] NULL,
|
||
[FitemID1] [INT],
|
||
--成品ID
|
||
[FitemID2] [INT],
|
||
--材料长代码
|
||
[FNumber3] nvarchar(50),
|
||
[FItemID3] [INT],
|
||
--材料短代码
|
||
[FUnitID] [INT],
|
||
[FQty1] [DECIMAL](23,10) DEFAULT (0),
|
||
--毛需求
|
||
[FQty2] [DECIMAL](23,10) DEFAULT (0),
|
||
--即时库存
|
||
[FQty3] [DECIMAL](23,10) DEFAULT (0),
|
||
--采购申请单数量
|
||
[FQty4] [DECIMAL](23,10) DEFAULT (0),
|
||
--采购订单数量
|
||
[FQty5] [DECIMAL](23,10) DEFAULT (0),
|
||
--请检单数量
|
||
[FQty6] [DECIMAL](23,10) DEFAULT (0),
|
||
--在途数量
|
||
[FQty7] [DECIMAL](23,10) DEFAULT (0),
|
||
--可用数量
|
||
[FQty8] [DECIMAL](23,10) DEFAULT (0),
|
||
--欠料数量
|
||
[FQty9] [DECIMAL](23,10) DEFAULT (0),
|
||
--已评审未转单数量
|
||
[FQty10] [DECIMAL](23,10) DEFAULT (0),
|
||
--需申购数量
|
||
[FQty12] [DECIMAL](23,10) DEFAULT (0),
|
||
--已分配量
|
||
[FOrderInterID] INT,
|
||
[FOrderEntryID] INT,
|
||
[FNumber2] nvarchar(50),
|
||
[FOrderDate] datetime
|
||
) CREATE TABLE [dbo].[ #MrpEntry2]( [FID] [INT] NOT NULL,[FIndex]
|
||
[INT] identity(1,1),
|
||
[FItemID] [INT],
|
||
[FUnitID] [INT],
|
||
[FQty] [DECIMAL](23,10) DEFAULT (0),
|
||
--需求数量
|
||
[FDate] datetime,
|
||
--生产开工日期
|
||
[FDate1] datetime,
|
||
--生产完工日期
|
||
[FCustOrderNo] VARCHAR(20),
|
||
--客户订单号
|
||
[FOrderInterID] INT,
|
||
[FOrderEntryID] INT
|
||
)
|
||
|
||
|
||
/*新增表头内容*/
|
||
INSERT INTO [dbo].[Mrp](
|
||
[FID],[FClassTypeID],[FBillNo],
|
||
[FText4],fuser,ftime2
|
||
)
|
||
SELECT
|
||
@FID,
|
||
200000002,
|
||
@BillNo,
|
||
@BillNo,
|
||
@UserID,
|
||
getdate()
|
||
|
||
/*****************START 自制属性物料*************************/
|
||
INSERT INTO [dbo].[MrpEntry2] (
|
||
[FID],
|
||
[FIndex],
|
||
[FBase],
|
||
[FBase1],
|
||
finteger2,
|
||
finteger3,
|
||
fdate4,
|
||
fdate5,
|
||
ftext8,
|
||
ftext9,
|
||
fdecimal --即时库存
|
||
,
|
||
fdecimal1 --在制量(未入库)
|
||
,
|
||
fdecimal2 --已评审未出货的销售订单
|
||
,
|
||
fdecimal3 --投料单需求量
|
||
)
|
||
SELECT
|
||
a.forderinterid,
|
||
row_number() over(
|
||
ORDER BY
|
||
forderinterid,
|
||
forderentryid,
|
||
fbomlevel
|
||
) AS findex,
|
||
a.fitemid,
|
||
a.funitid,
|
||
forderinterid,
|
||
forderentryid,
|
||
a.开工日期,
|
||
a.完工日期,
|
||
a.fneedqty,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
(
|
||
CASE WHEN isnull(a.fneedqty,0)+ isnull(a.投料单需求量,0)- isnull(a.即时库存,0)- isnull(
|
||
a.[在制量(未入库) ],
|
||
0
|
||
)< 0 THEN 0 ELSE isnull(a.fneedqty,0)+ isnull(a.投料单需求量,0)- isnull(a.即时库存,0)- isnull(
|
||
a.[在制量(未入库) ],
|
||
0
|
||
) end
|
||
)
|
||
),
|
||
a.即时库存 --即时库存数量
|
||
,
|
||
a.[在制量(未入库) ],
|
||
a.已评审未出货的销售订单,
|
||
a.投料单需求量
|
||
FROM
|
||
#mutidata a WHERE a.ferpclsid IN(2,3) ORDER BY a.forderinterid,
|
||
forderentryid,
|
||
fbomlevel
|
||
|
||
/**************************************************END 自制属性物料**************************************************/
|
||
/**************************************************START 外购属性物料,不包含纸箱和化工原料**************************************************/
|
||
INSERT INTO #mrpentry1(fid,fitemid1,fqty11,fitemid2,fitemid3,fnumber3,
|
||
funitid,
|
||
fqty1,
|
||
fdate2,
|
||
fdate3,
|
||
forderinterid,
|
||
forderentryid,
|
||
[FNumber2],
|
||
fqty2
|
||
)
|
||
SELECT
|
||
a.forderinterid fid,
|
||
(
|
||
CASE WHEN a.fbom <> 0 THEN a.fbom ELSE a.fitemid end
|
||
) fitemid1,
|
||
a.fneedqty,
|
||
a.fitemid fitemid2,
|
||
a.fitemid fitemid3,
|
||
b.fnumber,
|
||
b.funitid,
|
||
a.fneedqty * e.fcoefficient fqty1,
|
||
a.要求到货日期,
|
||
--dateadd(day,-3,C.FOrderDate),--(CASE WHEN b.fnumber like '02.86.%' THEN dateadd(day,-20,C.FOrderDate) WHEN b.fnumber like '02.87.%' THEN dateadd(day,-20,C.FOrderDate) WHEN b.fnumber like '02.71.%' THEN dateadd(day,-25,C.FOrderDate) ELSE C.FOrderDate END) FDate2,--物料需求日期=确认发货日期-15
|
||
a.要求到货日期,
|
||
--dateadd(day,-3,C.FOrderDate),--(CASE WHEN b.fnumber like '02.86.%' THEN dateadd(day,-20,C.FOrderDate) WHEN b.fnumber like '02.87.%' THEN dateadd(day,-20,C.FOrderDate) WHEN b.fnumber like '02.71.%' THEN dateadd(day,-25,C.FOrderDate) ELSE C.FOrderDate END) FDate3,--材料到货日期
|
||
a.forderinterid,
|
||
a.forderentryid,
|
||
substring(b.fnumber,1,5) fnumber2,
|
||
a.即时库存
|
||
FROM
|
||
(
|
||
SELECT
|
||
t.forderinterid,
|
||
t.fitemid,
|
||
sum(t.fneedqty) fneedqty,
|
||
min(t.forderentryid) forderentryid,
|
||
funitid,
|
||
avg(t.即时库存) 即时库存,
|
||
ferpclsid,
|
||
min(要求到货日期) '要求到货日期',
|
||
max(fbom) 'FBom'
|
||
FROM
|
||
#mutidata t
|
||
WHERE
|
||
1 = 1
|
||
AND (
|
||
t.f3 NOT LIKE '02.86%'
|
||
AND t.f3 NOT LIKE '02.87%'
|
||
)
|
||
GROUP BY
|
||
t.forderinterid,
|
||
t.fitemid,
|
||
funitid,
|
||
ferpclsid,
|
||
fbom
|
||
) a
|
||
LEFT JOIN t_icitem b ON a.fitemid = b.fitemid --LEFT JOIN #Mutidata c ON c.FOrderInterID=a.FOrderInterID AND c.FOrderEntryID=a.FOrderEntryID AND a.FItemID=c.FItemID --AND c.FLevelStrIng='0'
|
||
LEFT JOIN t_measureunit e ON e.fmeasureunitid = a.funitid
|
||
WHERE
|
||
1 = 1
|
||
AND b.ferpclsid = 1
|
||
ORDER BY
|
||
a.fitemid
|
||
|
||
--------------------------------------------------------02.86、02.86不合并------------------------------------------------------------------
|
||
INSERT INTO #mrpentry1(fid,fitemid1,fqty11,fitemid2,fitemid3,fnumber3,
|
||
funitid,
|
||
fqty1,
|
||
fdate2,
|
||
fdate3,
|
||
forderinterid,
|
||
forderentryid,
|
||
[FNumber2],
|
||
fqty2
|
||
)
|
||
SELECT
|
||
a.forderinterid fid,
|
||
a.fbom fitemid1,
|
||
a.fneedqty,
|
||
a.fitemid fitemid2,
|
||
a.fitemid fitemid3,
|
||
b.fnumber,
|
||
b.funitid,
|
||
a.fneedqty * e.fcoefficient fqty1,
|
||
a.要求到货日期,
|
||
--dateadd(day,-20,C.FOrderDate) FDate2,--物料需求日期=确认发货日期-15
|
||
a.要求到货日期,
|
||
--dateadd(day,-20,C.FOrderDate) FDate3,--材料到货日期
|
||
a.forderinterid,
|
||
a.forderentryid,
|
||
substring(b.fnumber,1,5) fnumber2,
|
||
a.即时库存
|
||
FROM
|
||
#mutidata a LEFT JOIN t_icitem b ON a.fitemid = b.fitemid LEFT JOIN
|
||
t_measureunit e ON e.fmeasureunitid = a.funitid
|
||
WHERE
|
||
1 = 1
|
||
AND b.ferpclsid = 1
|
||
AND (
|
||
a.f3 LIKE '02.86%'
|
||
OR a.f3 LIKE '02.87%'
|
||
)
|
||
ORDER BY
|
||
a.fitemid
|
||
/*采购申请单数量*/
|
||
SELECT
|
||
v1.fitemid,
|
||
isnull(
|
||
sum(v1.fqty),
|
||
0
|
||
)- isnull(
|
||
sum(v2.fqty),
|
||
0
|
||
) fqty INTO #porequest FROM porequest v LEFT JOIN porequestentry v1 ON v.finterid =
|
||
v1.finterid
|
||
LEFT JOIN poorderentry v2 ON v2.fsourceinterid = v1.finterid
|
||
AND v2.fsourceentryid = v1.fentryid
|
||
WHERE
|
||
v.fcancellation = 0
|
||
AND v.fstatus < 3
|
||
AND v1.fmrpclosed = 0
|
||
GROUP BY
|
||
v1.fitemid
|
||
|
||
UPDATE a SET fqty3 = b.fqty FROM #mrpentry1 a LEFT JOIN #porequest b ON a.fitemid2=b.fitemid
|
||
/*采购订单数量*/
|
||
SELECT
|
||
fitemid,
|
||
sum(fqty - fauxreceiptqty) fqty INTO #poorder FROM poorder v
|
||
LEFT JOIN poorderentry v1 ON v.finterid = v1.finterid
|
||
WHERE
|
||
v.fcancellation = 0
|
||
AND v.fstatus < 3
|
||
GROUP BY
|
||
fitemid
|
||
HAVING
|
||
sum(fqty - fauxreceiptqty) > 0
|
||
UPDATE
|
||
a
|
||
SET
|
||
fqty4 = b.fqty
|
||
FROM
|
||
#mrpentry1 a LEFT JOIN #poorder b ON
|
||
a.fitemid2 = b.fitemid
|
||
|
||
/*收料通知/请检单*/
|
||
SELECT
|
||
fitemid,
|
||
sum(
|
||
(fqty - fauxconcommitqty)
|
||
) fqty INTO #poinstock FROM
|
||
poinstock v
|
||
LEFT JOIN poinstockentry v1 ON v.finterid = v1.finterid
|
||
WHERE
|
||
v.fcancellation = 0
|
||
AND v.fstatus < 3
|
||
AND v.ftrantype = 72
|
||
GROUP BY
|
||
fitemid
|
||
HAVING
|
||
sum(fqty - fauxconcommitqty) > 0
|
||
UPDATE
|
||
a
|
||
SET
|
||
fqty5 = b.fqty,
|
||
fqty6 = isnull(fqty3,0)+ isnull(fqty4,0)+ isnull(b.fqty,0)
|
||
FROM
|
||
#mrpentry1 a LEFT JOIN #poinstock b ON
|
||
a.fitemid2 = b.fitemid
|
||
|
||
/*在途数量*/
|
||
--UPDATE a SET FQty6=ISNULL(FQty3,0)+ISNULL(FQty4,0)+ISNULL(FQty5,0) FROM #MrpEntry1 a
|
||
/*已分配量,未结案生产任务单未领数*/
|
||
UPDATE
|
||
a
|
||
SET
|
||
fqty12 = isnull(b.fqty,0)
|
||
FROM
|
||
#mrpentry1 a LEFT JOIN #yjck b ON
|
||
a.fitemid2 = b.fitemid
|
||
UPDATE
|
||
a
|
||
SET
|
||
a.fnumber3 = 'P'
|
||
FROM
|
||
#mrpentry1 a WHERE a.findex IN (
|
||
SELECT
|
||
min(findex)
|
||
FROM
|
||
#mrpentry1 GROUP BY fitemid2) UPDATE t SET t.fqty8 =
|
||
t2.累计用量
|
||
FROM
|
||
#mrpentry1 t LEFT JOIN ( SELECT ( SELECT sum( isnull(fqty1,0) + isnull(
|
||
CASE WHEN fnumber3 = 'P' THEN fqty12 ELSE 0 end,
|
||
0
|
||
)
|
||
)
|
||
FROM
|
||
#mrpentry1 t1 WHERE
|
||
t1.fitemid2 = t.fitemid2
|
||
AND t1.findex <= t.findex
|
||
) 累计用量,
|
||
t.fitemid2,
|
||
t.findex
|
||
FROM
|
||
#mrpentry1 t ) t2 ON t.fitemid2 = t2.fitemid2 AND t.findex = t2.findex
|
||
UPDATE
|
||
t
|
||
SET
|
||
t.fqty8 = isnull(fqty2,0)+ isnull(fqty6,0)- isnull(fqty8,0),
|
||
t.fqty10 = CASE WHEN isnull(fqty2,0)+ isnull(fqty6,0)- isnull(fqty8,0) < 0 THEN -(
|
||
isnull(fqty2,0)+ isnull(fqty6,0)- isnull(fqty8,0)
|
||
) ELSE 0 end,
|
||
t.fqty7 = isnull(fqty1,0)+ isnull(fqty2,0)+ isnull(fqty6,0)- isnull(fqty8,0)+ isnull(
|
||
CASE WHEN fnumber3 = 'P' THEN fqty12 ELSE 0 end,
|
||
0
|
||
)
|
||
FROM
|
||
#mrpentry1 t
|
||
|
||
INSERT INTO [dbo].[MrpEntry1] (
|
||
[FID],
|
||
[FIndex],
|
||
[FDate2],
|
||
[FDate3],
|
||
ftext49,
|
||
ftext50,
|
||
ftext51,
|
||
[FitemID2],
|
||
[FUnitID],
|
||
finteger4,
|
||
finteger5,
|
||
ftext10 --订单数量
|
||
,
|
||
ftext11 --毛需求T
|
||
,
|
||
ftext12 --即时库存T
|
||
,
|
||
ftext13 --采购申请单数量T
|
||
,
|
||
ftext14 --采购订单数量T
|
||
,
|
||
ftext15 --请检单数量T
|
||
,
|
||
ftext16 --在途数量T
|
||
,
|
||
ftext17 --已分配量T
|
||
,
|
||
ftext18 --可用数量T
|
||
,
|
||
ftext19 --欠料数量T
|
||
--,ftext20--已评审未转单数量T
|
||
,
|
||
ftext21 --需申购数量
|
||
)
|
||
SELECT
|
||
forderinterid,
|
||
row_number() over(
|
||
ORDER BY
|
||
findex
|
||
) findex,
|
||
fdate2,
|
||
fdate3,
|
||
c.fnumber,
|
||
c.fname,
|
||
c.fmodel,
|
||
fitemid2,
|
||
a.funitid,
|
||
forderinterid,
|
||
forderentryid,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty11,0)
|
||
) --订单数量
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty1,0)
|
||
) --毛需求
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty2,0)
|
||
) --即时库存
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty3,0)
|
||
) --采购申请单数量
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty4,0)
|
||
) --采购订单数量
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty5,0)
|
||
) --请检单数量
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty6,0)
|
||
) --在途数量
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty12,0)
|
||
) --已分配量
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty7,0)
|
||
) --可用数量
|
||
,
|
||
CONVERT(
|
||
DECIMAL(18,4),
|
||
isnull(fqty8,0)
|
||
) --欠料数量
|
||
--,CONVERT(DECIMAL(18,4),ISNULL(FQty9,0))--已评审未转单数量
|
||
,
|
||
CASE WHEN isnull(a.fqty8,0) < 0 THEN CONVERT(
|
||
DECIMAL(18,4),
|
||
- isnull(a.fqty8,0)
|
||
) ELSE 0 end -- 需申购数量
|
||
FROM
|
||
#mrpentry1 a INNER JOIN t_icitem b ON a.fitemid2 = b.fitemid INNER JOIN
|
||
t_icitem c ON a.fitemid1 = c.fitemid
|
||
WHERE
|
||
a.fnumber2 NOT IN (
|
||
'02.86','02.87','01.A.','01.B.',
|
||
'01.C.','01.D.','01.E.','01.H.'
|
||
)
|
||
|
||
/**************************************************END 外购属性物料**************************************************/
|
||
/**************************************************START 纸箱物料**************************************************/
|
||
INSERT INTO [dbo].[MrpEntry3] ( [FID],[FIndex],[FBase5] --成品代码
|
||
,[FText23] --订单数量
|
||
,[FBase6] --纸箱短代码
|
||
,[FText24] --毛需求
|
||
,[FText25] --即时库存
|
||
,[FText26] --采购申请数量
|
||
,[FText27] --采购订单数量
|
||
,[FText28] --请检单数量
|
||
,[FText29] --在途数
|
||
,[FText30] --已分配量
|
||
,[FText31] --欠料数量
|
||
,[FText32] --可用量
|
||
,[FText33] --需采购数量
|
||
,[FDate6] --纸箱需求日期
|
||
,[FDate7] --建议到货日期
|
||
,[FCheckBox4] --确认转单
|
||
,[FCheckBox5] --转单标识
|
||
,[FText34] --采购申请单编号
|
||
,[FText35] --备注
|
||
,[FINteger6] --订单内码
|
||
,[FINteger7] --订单行号
|
||
,[FDate8] --转单日期
|
||
,[FUser3] ) --转单人
|
||
SELECT forderinterid,row_number() over( ORDER BY findex ) findex,fitemid1,
|
||
isnull(fqty11,0),fitemid2,CONVERT( DECIMAL(18,4),isnull([FQty1],0) )
|
||
--毛需求
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty2],0) ) --即时库存
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty3],0) ) --采购申请单数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty4],0) ) --采购订单数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty5],0) ) --请检单数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty6],0) ) --在途数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty12],0) ) --已分配量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty8],0) ) --欠料数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty7],0) ) --可用数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty10],0) ) --需申购数量
|
||
,fdate2,fdate3,0,0,'','',forderinterid,forderentryid,getdate(),0 FROM
|
||
#mrpentry1 a WHERE 1 = 1 AND ( fnumber2 LIKE '02.86%' OR fnumber2 LIKE '02.87%'
|
||
)
|
||
/**************************************************END 纸箱物料**************************************************/
|
||
/**************************************************START 化工原料**************************************************/
|
||
INSERT INTO [dbo].[MrpEntry4] ( [FID],[FIndex],[FBase7],[FText36],[FBase8],
|
||
[FText37] --毛需求
|
||
,[FText38] --即时库存
|
||
,[FText39] --采购申请单数量
|
||
,[FText40] --采购订单数量
|
||
,[FText41] --请检单数量
|
||
,[FText42] --在途数量
|
||
,[FText43] --已分配量
|
||
,[FText44] --欠料数量
|
||
,[FText45] --可用数量
|
||
,[FText46] --需申购数量
|
||
,[FDate9],[FDate10],[FCheckBox6],[FCheckBox7],[FText47],[FText48],
|
||
[FINteger8],[FINteger9],[FDate11],[FUser4] ) SELECT forderinterid,row_number
|
||
() over(ORDER BY findex) findex,fitemid1,isnull(fqty11,0),fitemid2,CONVERT(
|
||
DECIMAL(18,4),isnull([FQty1],0) ) --毛需求
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty2],0) ) --即时库存
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty3],0) ) --采购申请单数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty4],0) ) --采购订单数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty5],0) ) --请检单数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty6],0) ) --在途数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty12],0) ) --已分配量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty8],0) ) --欠料数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty7],0) ) --可用数量
|
||
,CONVERT( DECIMAL(18,4),isnull([FQty10],0) ) --需申购数量
|
||
,fdate2 --GETDATE(),
|
||
,fdate3 --GETDATE()
|
||
,0,0,'','',forderinterid,forderentryid,getdate(),0 FROM #mrpentry1 a
|
||
WHERE 1 = 1 AND a.fnumber2 IN ( '01.A.','01.B.','01.C.','01.D.','01.E.',
|
||
'01.H.' )
|
||
/**************************************************END 化工原料**************************************************/
|
||
UPDATE seorder SET fheadselfs0153 = 'Y' WHERE finterid = @FID
|
||
--FHeadSelfS0153 评审标志
|
||
SELECT @FID,@BillNo DROP TABLE #mrpentry1 DROP TABLE #mrpentry2 DROP TABLE
|
||
#mutidata DROP TABLE #mutidata2 DROP TABLE #icinventory DROP TABLE #yjck DROP
|
||
TABLE #yjrk DROP TABLE #seorder DROP TABLE #poinstock DROP TABLE #porequest DROP
|
||
TABLE #poorder end |