/******待处理订单明细表打印(MP05004)**********/
CREATE PROCEDURE [dbo].[P_Quick_MP05004]
@v_PlanCode NVARCHAR(20), /*计划代号*/
@v_InvAttr SMALLINT, /**自制-1,委外-2,采购-4,8-计划品**/
@v_Status SMALLINT, /**逾期-1,提前-2,延后-4,取消-8,冲突-16,审核-32,减少-64**/
@v_AuditDate DATETIME, /**审核日期**/
@v_Qty Udt_QTY, /**减少数量**/
@v_OrderType SMALLINT, /**1-计划订单 2-生产订单 4-委外订单 8-请购单 16-采购订单 32-进口订单**/
@v_SInvCode NVARCHAR(60),
@v_EInvCode NVARCHAR(60)
--@v_WhereString nvarchar(4000)
--with encryption
AS
DECLARE @l_sql1 NVARCHAR(4000),
@l_error INT,
@l_sysdate DATETIME,
@l_projectid INT,
@l_projectid1 INT,
@l_Mom BIT,
@l_Sub BIT,
@l_Pur BIT,
@l_plan BIT,
@l_Status1 BIT, /**逾期**/
@l_Status2 BIT, /**提前**/
@l_Status3 BIT, /**延后**/
@l_Status4 BIT, /**取消**/
@l_Status5 BIT, /**冲突**/
@l_Status6 BIT, /**审核**/
@l_Status7 BIT, /**减少**/
@l_type1 TINYINT, /**计划订单**/
@l_type2 TINYINT, /**生产订单**/
@l_type3 TINYINT, /**委外订单**/
@l_type4 TINYINT, /**请购单**/
@l_type5 TINYINT, /**采购订单**/
@l_type6 TINYINT, /**进口订单**/
@l_factory BIT;
SELECT @l_factory = dbo.MultiFactoryEnable();
SELECT @l_sysdate = CONVERT(DATETIME, CONVERT(NCHAR(10), GETDATE(), 101));
SELECT @l_Mom = CASE
WHEN @v_InvAttr & 1 > 0 THEN
1
ELSE
0
END,
@l_Sub = CASE
WHEN @v_InvAttr & 2 > 0 THEN
1
ELSE
0
END,
@l_Pur = CASE
WHEN @v_InvAttr & 4 > 0 THEN
1
ELSE
0
END,
@l_plan = CASE
WHEN @v_InvAttr & 8 > 0 THEN
1
ELSE
0
END,
@l_Status1 = CASE
WHEN @v_Status & 1 > 0 THEN
1
ELSE
0
END,
@l_Status2 = CASE
WHEN @v_Status & 2 > 0 THEN
1
ELSE
0
END,
@l_Status3 = CASE
WHEN @v_Status & 4 > 0 THEN
1
ELSE
0
END,
@l_Status4 = CASE
WHEN @v_Status & 8 > 0 THEN
1
ELSE
0
END,
@l_Status5 = CASE
WHEN @v_Status & 16 > 0 THEN
1
ELSE
0
END,
@l_Status6 = CASE
WHEN @v_Status & 32 > 0 THEN
1
ELSE
0
END,
@l_Status7 = CASE
WHEN @v_Status & 64 > 0 THEN
1
ELSE
0
END,
@l_type1 = CASE
WHEN @v_OrderType & 1 > 0 THEN
9
ELSE
99
END,
@l_type2 = CASE
WHEN @v_OrderType & 2 > 0 THEN
1
ELSE
99
END,
@l_type3 = CASE
WHEN @v_OrderType & 4 > 0 THEN
2
ELSE
99
END,
@l_type4 = CASE
WHEN @v_OrderType & 8 > 0 THEN
5
ELSE
99
END,
@l_type5 = CASE
WHEN @v_OrderType & 16 > 0 THEN
3
ELSE
99
END,
@l_type6 = CASE
WHEN @v_OrderType & 32 > 0 THEN
4
ELSE
99
END;
IF @v_SInvCode IS NULL
SELECT @v_SInvCode = '';
IF ISNULL(@v_EInvCode, '') = ''
SELECT @v_EInvCode = MAX(cInvCode)
FROM Inventory;
IF @v_PlanCode > ''
SELECT @l_projectid = j.ProjectId,
@l_projectid1 = j.ProjectId
FROM mps_plancode p WITH (NOLOCK),
mps_planproject j WITH (NOLOCK)
WHERE p.PlanCodeId = j.PlanCodeId
AND p.PlanCode = @v_PlanCode;
ELSE
BEGIN
SELECT @l_projectid = ISNULL(j.ProjectId, '')
FROM mps_plancode p WITH (NOLOCK),
mps_planproject j WITH (NOLOCK)
WHERE p.PlanCodeId = j.PlanCodeId
AND j.ActiveFlag = 1
AND p.PlanType = 1;
SELECT @l_projectid1 = ISNULL(j.ProjectId, '')
FROM mps_plancode p WITH (NOLOCK),
mps_planproject j WITH (NOLOCK)
WHERE p.PlanCodeId = j.PlanCodeId
AND j.ActiveFlag = 1
AND p.PlanType = 2;
END;
SELECT ProjectId = PartId,
SchId = PartId,
FactoryCode,
PartId,
InvCode,
Free1,
Free2,
Free3,
Free4,
Free5,
Free6,
Free7,
Free8,
Free9,
Free10,
SafeQty = DemQty,
Status = DemOrgType
INTO #tmp_data
FROM mps_demand
WHERE 1 = 2;
INSERT INTO #tmp_data
SELECT a.ProjectId,
a.SchId,
CASE @l_factory
WHEN 1 THEN
a.FactoryCode
ELSE
''
END,
a.PartId,
b.InvCode,
b.Free1,
b.Free2,
b.Free3,
b.Free4,
b.Free5,
b.Free6,
b.Free7,
b.Free8,
b.Free9,
b.Free10,
b.SafeQty,
Status = CASE
WHEN a.DemType = 9
AND a.AuditDate < @l_sysdate
OR a.DemType < 9
AND i.IsReM = 0
AND a.DemDate < @l_sysdate
OR a.DemType < 9
AND i.IsReM = 1
AND a.LUCD < @l_sysdate THEN
1
WHEN a.DemType = 9
AND a.AuditDate = @l_sysdate THEN
6
WHEN i.IsReM = 0
AND a.DemType = 9
AND a.AuditDate > @l_sysdate
AND a.RushFlag = 1 THEN
5
WHEN i.IsReM = 0
AND a.DemDate < @l_sysdate THEN
1
WHEN i.IsReM = 0
AND a.DemDate < a.RscDate THEN
3
WHEN (a.DocQty + a.RscQty = 0)
AND i.IsReM = 0 THEN
4
WHEN i.IsReM = 0
AND a.DemDate > a.RscDate
AND
(
a.RscDate <> '2000-01-01'
AND a.RscDate IS NOT NULL
) THEN
2
WHEN i.IsReM = 0
AND a.RscQty <> 0 THEN
7
END
FROM mps_planproject j WITH (NOLOCK)
LEFT JOIN mps_schedule a WITH (NOLOCK)
ON j.ProjectId = a.ProjectId
AND a.ProjectId > 0
LEFT JOIN bas_part b WITH (NOLOCK)
ON a.PartId = b.PartId
LEFT OUTER JOIN v_bas_inventory i
ON b.InvCode = i.InvCode
WHERE j.ProjectId IN ( @l_projectid, @l_projectid1 )
AND a.ReplaceRelFlag < 2
AND a.DemType <= 9
AND (a.DocType IN ( @l_type2, @l_type3, @l_type4, @l_type5, @l_type6 ))
AND a.DemType > 0
AND b.InvCode
BETWEEN @v_SInvCode AND @v_EInvCode
AND
(
i.Mom = 1
AND @l_Mom = 1
OR i.Sub = 1
AND @l_Sub = 1
OR i.Pur = 1
AND @l_Pur = 1
OR i.Pln = 1
AND @l_plan = 1
)
AND
(
@v_AuditDate IS NULL
OR @v_AuditDate = '2000-1-1'
OR a.AuditDate IS NULL
OR a.AuditDate <= @v_AuditDate
OR ISNULL(a.RscDate, '1900-1-1') <= @v_AuditDate
)
AND a.BalQty > 0
UNION
SELECT a.ProjectId,
a.SchId,
CASE @l_factory
WHEN 1 THEN
a.FactoryCode
ELSE
''
END,
a.PartId,
b.InvCode,
b.Free1,
b.Free2,
b.Free3,
b.Free4,
b.Free5,
b.Free6,
b.Free7,
b.Free8,
b.Free9,
b.Free10,
b.SafeQty,
Status = CASE
WHEN a.DemType = 9
AND a.AuditDate < @l_sysdate
OR a.DemType < 9
AND i.IsReM = 0
AND a.DemDate < @l_sysdate
OR a.DemType < 9
AND i.IsReM = 1
AND a.LUCD < @l_sysdate THEN
1
WHEN a.DemType = 9
AND a.AuditDate = @l_sysdate THEN
6
WHEN i.IsReM = 0
AND a.DemType = 9
AND a.AuditDate > @l_sysdate
AND a.RushFlag = 1 THEN
5
WHEN i.IsReM = 0
AND a.DemDate < @l_sysdate THEN
1
WHEN i.IsReM = 0
AND a.DemDate < a.RscDate THEN
3
WHEN (a.DocQty + a.RscQty = 0)
AND i.IsReM = 0 THEN
4
WHEN i.IsReM = 0
AND a.DemDate > a.RscDate
AND
(
a.RscDate <> '2000-01-01'
AND a.RscDate IS NOT NULL
) THEN
2
WHEN i.IsReM = 0
AND a.RscQty <> 0 THEN
7
END
FROM mps_planproject j WITH (NOLOCK)
LEFT JOIN mps_schedule a WITH (NOLOCK)
ON j.ProjectId = a.ProjectId
AND a.ProjectId > 0
LEFT JOIN bas_part b WITH (NOLOCK)
ON a.PartId = b.PartId
LEFT OUTER JOIN v_bas_inventory i
ON b.InvCode = i.InvCode
WHERE j.ProjectId IN ( @l_projectid, @l_projectid1 )
AND a.ReplaceRelFlag < 2
AND a.DemType <= 9
AND
(
a.DocType = @l_type1
AND (
(
COALESCE(a.SupplyingRCode, '') = ''
AND COALESCE(a.SupplyingPCode, '') = ''
)
)
)
AND a.DemType > 0
AND b.InvCode
BETWEEN @v_SInvCode AND @v_EInvCode
AND
(
i.Mom = 1
AND @l_Mom = 1
OR i.Sub = 1
AND @l_Sub = 1
OR i.Pur = 1
AND @l_Pur = 1
OR i.Pln = 1
AND @l_plan = 1
)
AND
(
@v_AuditDate IS NULL
OR @v_AuditDate = '2000-1-1'
OR a.AuditDate IS NULL
OR a.AuditDate <= @v_AuditDate
OR ISNULL(a.RscDate, '1900-1-1') <= @v_AuditDate
)
AND a.BalQty > 0
UNION
SELECT a.ProjectId,
a.SchId,
CASE @l_factory
WHEN 1 THEN
a.FactoryCode
ELSE
''
END,
a.PartId,
b.InvCode,
b.Free1,
b.Free2,
b.Free3,
b.Free4,
b.Free5,
b.Free6,
b.Free7,
b.Free8,
b.Free9,
b.Free10,
b.SafeQty,
Status = CASE
WHEN a.DemType = 9
AND a.AuditDate < @l_sysdate
OR a.DemType < 9
AND i.IsReM = 0
AND a.DemDate < @l_sysdate
OR a.DemType < 9
AND i.IsReM = 1
AND a.LUCD < @l_sysdate THEN
1
WHEN a.DemType = 9
AND a.AuditDate = @l_sysdate THEN
6
WHEN i.IsReM = 0
AND a.DemType = 9
AND a.AuditDate > @l_sysdate
AND a.RushFlag = 1 THEN
5
WHEN i.IsReM = 0
AND a.DemDate < @l_sysdate THEN
1
WHEN i.IsReM = 0
AND a.DemDate < a.RscDate THEN
3
WHEN (a.DocQty + a.RscQty = 0)
AND i.IsReM = 0 THEN
4
WHEN i.IsReM = 0
AND a.DemDate > a.RscDate
AND
(
a.RscDate <> '2000-01-01'
AND a.RscDate IS NOT NULL
) THEN
2
WHEN i.IsReM = 0
AND a.RscQty <> 0 THEN
7
END
FROM mps_planproject j WITH (NOLOCK)
LEFT JOIN mps_schedule a WITH (NOLOCK)
ON j.ProjectId = a.ProjectId
AND a.ProjectId > 0
LEFT JOIN bas_part b WITH (NOLOCK)
ON a.PartId = b.PartId
LEFT OUTER JOIN v_bas_inventory i
ON b.InvCode = i.InvCode
WHERE j.ProjectId IN ( @l_projectid, @l_projectid1 )
AND a.ReplaceRelFlag < 2
AND a.DemType <= 9
AND
(
a.DocType = @l_type1
AND
(
a.RefDocCode = a.SupplyingPCode
AND a.RefDocCode = a.SupplyingRCode
)
)
AND a.DemType > 0
AND b.InvCode
BETWEEN @v_SInvCode AND @v_EInvCode
AND
(
i.Mom = 1
AND @l_Mom = 1
OR i.Sub = 1
AND @l_Sub = 1
OR i.Pur = 1
AND @l_Pur = 1
OR i.Pln = 1
AND @l_plan = 1
)
AND
(
@v_AuditDate IS NULL
OR @v_AuditDate = '2000-1-1'
OR a.AuditDate IS NULL
OR a.AuditDate <= @v_AuditDate
OR ISNULL(a.RscDate, '1900-1-1') <= @v_AuditDate
)
AND a.BalQty > 0;
CREATE INDEX tmp_idx_1 ON #tmp_data (PartId);
CREATE INDEX tmp_idx_2 ON #tmp_data (SchId);
DELETE #tmp_data
FROM #tmp_data t,
mps_netdemand d WITH (NOLOCK)
WHERE t.SchId = d.SchId
AND d.DelFlag = 1;
IF @l_factory = 1
UPDATE #tmp_data
SET SafeQty = b.SafeQty
FROM #tmp_data t
INNER JOIN bas_factorypart b
ON t.FactoryCode = b.FactoryCode
AND t.InvCode = b.InvCode;
SELECT DISTINCT
FactoryCode,
PartId,
InvCode,
Free1,
Free2,
Free3,
Free4,
Free5,
Free6,
Free7,
Free8,
Free9,
Free10
INTO #tmp_data1
FROM #tmp_data t;
CREATE INDEX tmp_idx_4
ON #tmp_data1 (
PartId,
InvCode,
Free1,
Free2,
Free3,
Free4,
Free5,
Free6,
Free7,
Free8,
Free9,
Free10
);
SELECT FactoryCode,
PartId = RefDocDId,
Onhand = BalQty
INTO #tmp_onhand
FROM mps_schedule
WHERE 1 = 2;
INSERT INTO #tmp_onhand
(
FactoryCode,
PartId,
Onhand
)
SELECT a.FactoryCode,
a.PartId,
Onhand = SUM( CASE
WHEN d.bMRP = 1
OR RTRIM(c.cwhcode) = ''
OR c.cwhcode IS NULL THEN
c.iquantity
ELSE
0
END
)
FROM #tmp_data1 a
INNER JOIN v_arrivalcurrentstock c WITH (NOLOCK)
ON a.InvCode = c.cinvcode
AND
(
a.Free1 = c.cfree1
OR a.Free1 = ''
)
AND
(
a.Free2 = c.cfree2
OR a.Free2 = ''
)
AND
(
a.Free3 = c.cfree3
OR a.Free3 = ''
)
AND
(
a.Free4 = c.cfree4
OR a.Free4 = ''
)
AND
(
a.Free5 = c.cfree5
OR a.Free5 = ''
)
AND
(
a.Free6 = c.cfree6
OR a.Free6 = ''
)
AND
(
a.Free7 = c.cfree7
OR a.Free7 = ''
)
AND
(
a.Free8 = c.cfree8
OR a.Free8 = ''
)
AND
(
a.Free9 = c.cfree9
OR a.Free9 = ''
)
AND
(
a.Free10 = c.cfree10
OR a.Free10 = ''
)
LEFT OUTER JOIN Warehouse d WITH (NOLOCK)
ON (c.cwhcode = d.cWhCode)
AND
(
a.FactoryCode = ''
OR a.FactoryCode = c.cfactorycode
)
GROUP BY a.FactoryCode,
a.PartId;
IF
(
SELECT IssApplyOnhFlag FROM mom_parameter
) = 1
INSERT INTO #tmp_onhand
(
FactoryCode,
PartId,
Onhand
)
SELECT a.FactoryCode,
a.PartId,
Onhand = SUM( CASE
WHEN d.bMRP = 1
OR RTRIM(c.cWhCode) = ''
OR c.cWhCode IS NULL THEN
ISNULL(c.iQuantity, 0) - ISNULL(c.fOutQuantity, 0)
ELSE
0
END
) * -1
FROM #tmp_data1 a
INNER JOIN MaterialAppVouchs c WITH (NOLOCK)
ON a.InvCode = c.cInvCode
AND ISNULL(c.iMPoIds, 0) = 0
AND ISNULL(c.iOMoDID, 0) = 0
AND ISNULL(c.iQuantity, 0) - ISNULL(c.fOutQuantity, 0) > 0
AND
(
a.Free1 = c.cFree1
OR a.Free1 = ''
)
AND
(
a.Free2 = c.cFree2
OR a.Free2 = ''
)
AND
(
a.Free3 = c.cFree3
OR a.Free3 = ''
)
AND
(
a.Free4 = c.cFree4
OR a.Free4 = ''
)
AND
(
a.Free5 = c.cFree5
OR a.Free5 = ''
)
AND
(
a.Free6 = c.cFree6
OR a.Free6 = ''
)
AND
(
a.Free7 = c.cFree7
OR a.Free7 = ''
)
AND
(
a.Free8 = c.cFree8
OR a.Free8 = ''
)
AND
(
a.Free9 = c.cFree9
OR a.Free9 = ''
)
AND
(
a.Free10 = c.cFree10
OR a.Free10 = ''
)
LEFT OUTER JOIN Warehouse d WITH (NOLOCK)
ON (c.cWhCode = d.cWhCode)
AND
(
a.FactoryCode = ''
OR a.FactoryCode = c.cfactorycode
)
GROUP BY a.FactoryCode,
a.PartId;
INSERT INTO #tmp_onhand
(
FactoryCode,
PartId,
Onhand
)
SELECT a.FactoryCode,
a.PartId,
SUM( CASE c.bMRP
WHEN 1 THEN
ISNULL(b.iQuantity, 0) - ISNULL(fStopQuantity, 0)
ELSE
0
END
) - SUM( CASE c.bMRP
WHEN 1 THEN
COALESCE(b.fOutQuantity, 0)
ELSE
0
END
) + SUM( CASE c.bMRP
WHEN 1 THEN
COALESCE(b.fInQuantity, 0)
ELSE
0
END
) + SUM( CASE c.bMRP
WHEN 1 THEN
COALESCE(b.fTransOutQuantity, 0)
ELSE
0
END
) - SUM( CASE c.bMRP
WHEN 1 THEN
COALESCE(b.fTransInQuantity, 0)
ELSE
0
END
) + SUM( CASE c.bMRP
WHEN 1 THEN
COALESCE(b.fStopQuantity, 0)
ELSE
0
END
)
FROM #tmp_data1 AS a
INNER JOIN CurrentStock AS b
ON (a.InvCode = b.cInvCode)
AND
(
ISNULL(a.Free1, '') = ''
OR a.Free1 = b.cFree1
)
AND
(
ISNULL(a.Free2, '') = ''
OR a.Free2 = b.cFree2
)
AND
(
ISNULL(a.Free3, '') = ''
OR a.Free3 = b.cFree3
)
AND
(
ISNULL(a.Free4, '') = ''
OR a.Free4 = b.cFree4
)
AND
(
ISNULL(a.Free5, '') = ''
OR a.Free5 = b.cFree5
)
AND
(
ISNULL(a.Free6, '') = ''
OR a.Free6 = b.cFree6
)
AND
(
ISNULL(a.Free7, '') = ''
OR a.Free7 = b.cFree7
)
AND
(
ISNULL(a.Free8, '') = ''
OR a.Free8 = b.cFree8
)
AND
(
ISNULL(a.Free9, '') = ''
OR a.Free9 = b.cFree9
)
AND
(
ISNULL(a.Free10, '') = ''
OR a.Free10 = b.cFree10
)
LEFT OUTER JOIN Warehouse AS c
ON (b.cWhCode = c.cWhCode)
AND
(
a.FactoryCode = ''
OR a.FactoryCode = c.cFactoryCode
)
WHERE ISNULL(b.bStopFlag, 0) = 0
GROUP BY a.FactoryCode,
a.PartId;
/* insert into #tmp_onhand (PartId, Onhand)
select v.PartId, Onhand = sum(case c.bMRP when 1 then coalesce(v.iQuantity,0) else 0 end) -
sum(case c.bMRP when 1 then coalesce(v.fOutQuantity,0) else 0 end) +
sum(case c.bMRP when 1 then coalesce(v.fInQuantity,0) else 0 end) -
sum(case c.bMRP when 1 then coalesce(v.fTransOutQuantity,0) else 0 end) +
sum(case c.bMRP when 1 then coalesce(v.fTransInQuantity,0) else 0 end)
from #tmp_data1 t inner join v_CurrentPartStock v with (nolock) on v.PartId = t.PartId
left outer join Warehouse as c with (nolock) on (v.cWhCode = c.cWhCode)
group by v.PartId
*/
CREATE INDEX tmp_idx_3 ON #tmp_onhand (FactoryCode, PartId);
-- select @l_sql1='select i.InvCode,i.InvName, i.InvAddCode, i.InvStd, i.ComUnitCode, i.ComUnitName, t.Free1, t.Free2, t.Free3, t.Free4, t.Free5, t.Free6, t.Free7, t.Free8, t.Free9, t.Free10,
--i.InvDefine1, i.InvDefine2, i.InvDefine3, i.InvDefine4, i.InvDefine5, i.InvDefine6, i.InvDefine7, i.InvDefine8,i.InvDefine9, i.InvDefine10, i.InvDefine11, i.InvDefine12, i.InvDefine13, i.InvDefine14, i.InvDefine15, i.InvDefine16,
--case when p.DemType in (9,10) then i.PlanSupplyType else i.InvAttr end as InvAttr, i.IsRem, case when bf.factorycode is not null then bf.LeadTime else Convert(int,i.LeadTime) end As LeadTime, i.Policy, case when bf.factorycode is not null then bf.Period else i.Period end as Period, t.SafeQty, t1.Onhand,DemDate = case i.IsRem when 0 then p.DemDate else p.LUCD end, p.AuditDate,SoCode = p.SoCode,SoSeq = case when p.SoSeq > 0 then p.SoSeq else null end,
--p.SoType,DocCode = p.RefDocCode,DocSeq = case when p.RefDocSeq > 0 then p.RefDocSeq else null end,
--DemType = case when p.ExternalFlag = 1 and p.DemType = 2 then 21 when p.ExternalFlag = 1 and p.DemType = 5 then 22 else p.DemType end,p.DocQty, p.BalQty,
--RscDate = case when i.IsRem = 1 or p.DemType <> 9 and p.RscDate = p.DemDate then null else p.RscDate end,
--RscQty = case i.IsRem when 0 then p.RscQty else 0 end,t.Status,
--p.RefDocDId,p.MpsFlag, b.PlanCode,p.ExternalFlag ,case when bf.factorycode is not null then bf.MulQty else i1.MulQty end as MulQty,case when bf.factorycode is not null then bf.MinQty else i1.MinQty end as MinQty,i.EmplCode,i.PurEmplCode,person.cpersonname,i.DeptCode,i.VenCode,
--case when bf.factorycode is not null then bf.PeriodType else i.PeriodType end as PeriodType,case when bf.factorycode is not null then bf.AvailableDate else i.AvailableDate end as AvailableDate,case when bf.factorycode is not null then bf.TbCode else tb.TbCode end as TbCode,case when bf.factorycode is not null then bf.Description else tb.Description end as TbDesc, p.FactoryCode, f.cFactoryName as FactoryName
--from #tmp_data t left outer join (select FactoryCode, PartId, Onhand = sum(Onhand) from #tmp_onhand group by FactoryCode, PartId) t1 on t.PartId = t1.PartId and t.FactoryCode = t1.FactoryCode
--left join bas_part i1 with (nolock) on i1.partid = t.partid
--left outer join v_bas_inventory i with (nolock) on t.InvCode = i.InvCode
--left join mps_schedule p with (nolock) on t.SchId = p.SchId left join mps_planproject a on p.ProjectId = a.ProjectId
--left join mps_plancode b on a.PlanCodeId = b.PlanCodeId
--left outer join mps_timebucket tb on i.TbId = tb.TbId left outer join factory f on p.FactoryCode = f.cfactorycode
--left outer join person on i.PurEmplCode = person.cpersoncode
--left outer join (select b.InvCode,b.FactoryCode,b.LeadTime,b.VLeadTime,b.VBase,b.PeriodType,b.Period,tb.TbCode,tb.Description,b.AvailableDate,b.MulQty,b.MinQty,b.FixQty,b.MaxQty,b.SafeQty,b.Rounded
-- from bas_factorypart b Left Outer Join mps_TimeBucket tb on b.TbId=tb.TbId where dbo.multifactoryenable()=1) bf on bf.InvCode = i.InvCode and bf.FactoryCode = p.FactoryCode
--where ( '+convert(nvarchar(1),@l_Status1)+' = 1 and t.Status = 1 or '+convert(nvarchar(1),@l_Status2)+' = 1 and t.Status = 2 or '+convert(nvarchar(1),@l_Status3)+' = 1 and t.Status = 3 or '+convert(nvarchar(1),@l_Status4)+' = 1 and t.Status = 4 or
--'+convert(nvarchar(1),@l_Status5)+' = 1 and t.Status = 5 or '+convert(nvarchar(1),@l_Status6)+' = 1 and t.Status = 6 or '+convert(nvarchar(1),@l_Status7)+' = 1 and t.Status = 7 ) and
--(isnull('+convert(nvarchar(20),@v_Qty)+',0) = 0 or t.Status <> 7 or t.Status = 7 and abs(round(p.RscQty * 100 / p.BalQty,6)) >= '+convert(nvarchar(20),@v_Qty)+')'+'order by i.InvCode, p.DemDate'
--DocCode NVARCHAR(50) NULL,订单号
--RefDocDId NVARCHAR(500) NULL,订单子表ID
--RscDate DATETIME,重规划日
--DocSeq NVARCHAR(50) NULL 订单明细行号
SELECT @l_sql1
= N'
CREATE TABLE #TempQuick(
cInvCode nvarchar(50) NULL,
cInvName NVARCHAR(500) NULL,
DocCode NVARCHAR(50) NULL,
RefDocDId NVARCHAR(500) NULL,
RscDate DATETIME,
DocSeq NVARCHAR(50) NULL
)
insert into #TempQuick
select i.InvCode,i.InvName, p.RefDocCode,p.RefDocDId,(case when i.IsRem = 1 or p.DemType <> 9 and p.RscDate = p.DemDate then null else p.RscDate end) as RscDate,
DocSeq = (case when p.RefDocSeq > 0 then p.RefDocSeq else null end)
from #tmp_data t left outer join (select FactoryCode, PartId, Onhand = sum(Onhand) from #tmp_onhand group by FactoryCode, PartId) t1 on t.PartId = t1.PartId and t.FactoryCode = t1.FactoryCode
left join bas_part i1 with (nolock) on i1.partid = t.partid
left outer join v_bas_inventory i with (nolock) on t.InvCode = i.InvCode
left join mps_schedule p with (nolock) on t.SchId = p.SchId left join mps_planproject a on p.ProjectId = a.ProjectId
left join mps_plancode b on a.PlanCodeId = b.PlanCodeId
left outer join mps_timebucket tb on i.TbId = tb.TbId left outer join factory f on p.FactoryCode = f.cfactorycode
left outer join person on i.PurEmplCode = person.cpersoncode
left outer join (select b.InvCode,b.FactoryCode,b.LeadTime,b.VLeadTime,b.VBase,b.PeriodType,b.Period,tb.TbCode,tb.Description,b.AvailableDate,b.MulQty,b.MinQty,b.FixQty,b.MaxQty,b.SafeQty,b.Rounded
from bas_factorypart b Left Outer Join mps_TimeBucket tb on b.TbId=tb.TbId where dbo.multifactoryenable()=1) bf on bf.InvCode = i.InvCode and bf.FactoryCode = p.FactoryCode
where ( ' + CONVERT(NVARCHAR(1), @l_Status1) + N' = 1 and t.Status = 1 or ' + CONVERT(NVARCHAR(1), @l_Status2)
+ N' = 1 and t.Status = 2 or ' + CONVERT(NVARCHAR(1), @l_Status3) + N' = 1 and t.Status = 3 or '
+ CONVERT(NVARCHAR(1), @l_Status4) + N' = 1 and t.Status = 4 or
' + CONVERT(NVARCHAR(1), @l_Status5) + N' = 1 and t.Status = 5 or ' + CONVERT(NVARCHAR(1), @l_Status6)
+ N' = 1 and t.Status = 6 or ' + CONVERT(NVARCHAR(1), @l_Status7) + N' = 1 and t.Status = 7 ) and
(isnull(' + CONVERT(NVARCHAR(20), @v_Qty)
+ N',0) = 0 or t.Status <> 7 or t.Status = 7 and abs(round(p.RscQty * 100 / p.BalQty,6)) >= '
+ CONVERT(NVARCHAR(20), @v_Qty) + N')'
+ N'order by i.InvCode, p.DemDate
INSERT INTO TempQuick
SELECT cInvCode,
cInvName,
DocCode,
RefDocDId,
RscDate,
DocSeq,
GETDATE()
FROM #TempQuick
WHERE (DocCode LIKE''PD%'' OR DocCode LIKE''CD%'') '
EXEC sp_executesql @l_sql1;
--AND POID IN (SELECT POID FROM dbo.PO_Pomain WHERE cPOID IN (#TempQuick.DocCode) )
--AND OM_MODetails.MOID IN (SELECT MOID FROM dbo.OM_MOMain WHERE cCode IN (#TempQuick.DocCode))
--select InvCode,InvName,DocCode,RefDocDId,CONVERT(VARCHAR(100), RscDate, 23) as RscDate from #TempQuick where DocCode like ''PD%'' OR DocCode like ''CD%''
-- DELETE from TempQuick
-- insert into TempQuick
-- select cInvCode,cInvName,DocCode,RefDocDId,CONVERT(VARCHAR(100), RscDate, 23) as RscDate from #TempQuick where DocCode like ''PD%'' OR DocCode like ''CD%''
--RETURN 0;
--CREATE TABLE TempQuick(
--cInvCode nvarchar(50) NULL,
--cInvName NVARCHAR(500) NULL,
--DocCode NVARCHAR(50) NULL,
--RefDocDId NVARCHAR(500) NULL,
--RscDate DATETIME)
----这部分订单不进行刷新
DELETE FROM dbo.TempQuick WHERE TempQuick.InTime > CONVERT(DATE, GETDATE(), 110)
AND DocCode IN ( 'PD20191120014', 'PD202004140118', 'PD202004152388', 'PD202004152366', 'PD202004172396',
'PD202007180001', 'PD202008075859', 'PD202010050003', 'PD202010085319', 'PD202010216447',
'PD202011040041', 'PD2021020512474', 'PD2021020812583', 'PD2021022013102', 'PD2021031700005',
'PD2021032214600', 'PD2021062519546', 'PD2021072100050', 'PD2021072720412', 'PD2021081321428',
'PD2021081721461', 'PD2021090722370', 'PD2021092723094', 'PD2021102000060', 'PD2021112300073',
'PD2021121600407', 'PD2021121723953', 'PD2022011200064', 'PD2022011300047', 'PD2022030100140',
'PD2022031400339', 'PD2022040800081', 'PD2022041200172', 'PD2022050600048', 'PD2022053100015',
'PD2022072700832', 'PD2022081200001', 'PD2022081800059', 'PD2022102701377', 'PD2023010700026',
'PD2023011300014', 'PD2023022400024', 'PD2023022400026', 'PD2023022400027'
)
UPDATE dbo.PO_Podetails
SET cDefine37 = TempQuick.RscDate
FROM dbo.PO_Podetails,
TempQuick
WHERE PO_Podetails.ID = TempQuick.RefDocDId
AND PO_Podetails.cInvCode = TempQuick.cInvCode
AND TempQuick.DocCode LIKE 'PD%'
AND TempQuick.InTime > CONVERT(DATE, GETDATE(), 110)
---2023年6月16日，例如工单开工日期在5月29日，6月9日ECN新增物料后，
---采购订单在6月9日下单，重新更新后，更新后的交货日期会在5月29日，此时，比
---采购订单制单日期还早，增加如下条件，只有重规划日大于采购订单制单时间的才
---更新
AND DATEDIFF( SECOND,
(
SELECT cmaketime
FROM dbo.PO_Pomain
JOIN dbo.PO_Podetails
ON PO_Podetails.POID = PO_Pomain.POID
AND PO_Podetails.ID = TempQuick.RefDocDId
),
TempQuick.RscDate
) > 0;
UPDATE PO_Pomain
SET cCloser = cCloser
WHERE cPOID IN
(
SELECT DocCode
FROM TempQuick
WHERE TempQuick.InTime > CONVERT(DATE, GETDATE(), 110)
AND TempQuick.DocCode LIKE 'PD%'
AND TempQuick.RscDate IS NOT NULL
)
UPDATE dbo.OM_MODetails
SET cDefine37 = TempQuick.RscDate
FROM OM_MODetails,
TempQuick
WHERE OM_MODetails.MODetailsID = TempQuick.RefDocDId
AND OM_MODetails.cInvCode = TempQuick.cInvCode
AND TempQuick.DocCode LIKE 'CD%'
AND TempQuick.InTime > CONVERT(DATE, GETDATE(), 110)
AND DATEDIFF( SECOND,
(
SELECT dCreateTime
FROM dbo.OM_MOMain
JOIN dbo.OM_MODetails
ON OM_MODetails.MOID = OM_MOMain.MOID
AND MODetailsID = TempQuick.RefDocDId
),
TempQuick.RscDate
) > 0;
UPDATE OM_MOMain
SET cCloser = cCloser
WHERE cCode IN
(
SELECT DocCode
FROM TempQuick
WHERE TempQuick.InTime > CONVERT(DATE, GETDATE(), 110)
AND TempQuick.DocCode LIKE 'CD%'
AND TempQuick.RscDate IS NOT NULL
)
2029
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
