USE [AIS20140414085217]
GO
/****** Object: StoredProcedure [dbo].[pro_GetProductionOrderData] Script Date: 2022/5/26 10:45:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[pro_GetProductionOrderData]
(
@BalanceRatio DECIMAL(20, 2), --结存比
@ProductCategoryName NVARCHAR(50), --产品大类
@ProductDepartment NVARCHAR(50), --责任部门
@ProductClass NVARCHAR(50) --产品分类
)
AS
SET NOCOUNT ON;
/******
数据来源：加急、市场计划、库存、备单
******/
/*--------------------------------------------------------------------------------------*/
/************************创建临时表************************/
----加急
CREATE TABLE #UrgentOrder
(
order_number VARCHAR(50) , --单号
product_number VARCHAR(50) , --产品代码
product_name VARCHAR(100) , --产品名称
creat_date DATETIME , --日期
order_qty DECIMAL(20, 2) , --数量
data_type INT, --数据类型，1加急，2计划，3补库标，4备单，5库存
);
----市场计划
CREATE TABLE #PlanOrder
(
order_number VARCHAR(50) , --单号
product_number VARCHAR(50) , --产品代码
product_name VARCHAR(100) , --产品名称
creat_date DATETIME , --日期，取T_EZ_SalePlanDetail中creat_date
order_qty DECIMAL(20, 2) , --数量
data_type INT, --数据类型，1加急，2计划，3补库标，4备单，5库存
);
----备单
CREATE TABLE #PreOrder
(
order_number VARCHAR(50) , --单号
product_number VARCHAR(50) , --产品代码
product_name VARCHAR(100) , --产品名称
creat_date DATETIME , --日期，取T_EZ_SalePlanDetail中creat_date
order_qty DECIMAL(20, 2) , --数量
data_type INT, --数据类型，1加急，2计划，3补库标，4备单，5库存
);
----库标
CREATE TABLE #InventoryTag
(
order_number VARCHAR(50) , --单号
product_number VARCHAR(50) , --产品代码
product_name VARCHAR(100) , --产品名称
creat_date DATETIME , --日期，取当前日期SYSDATE()
order_qty DECIMAL(20, 2) , --数量
data_type INT, --数据类型，1加急，2计划，3补库标，4备单，5库存
);
----库存
CREATE TABLE #Inventory
(
order_number VARCHAR(50) , --单号
product_number VARCHAR(50) , --产品代码
product_name VARCHAR(100) , --产品名称
creat_date DATETIME , --日期，取当前日期SYSDATE()
order_qty DECIMAL(20, 2) , --数量
data_type INT, --数据类型，1加急，2计划，3补库标，4备单，5库存
);
----所有库存
CREATE TABLE #AllInventory
(
product_number VARCHAR(50) , --产品代码
product_name VARCHAR(100) , --产品名称
creat_date DATETIME , --日期，取当前日期SYSDATE()
order_qty DECIMAL(20, 2) , --数量
balance_ratio DECIMAL(20, 2) ,--结存比
);
/*--------------------------------------------------------------------------------------*/
/************************加急************************/
DECLARE @Parm_UrgentOrder NVARCHAR(MAX) = N'',
@sql_UrgentOrder NVARCHAR(MAX) = N''
SET @sql_UrgentOrder = '
INSERT INTO #UrgentOrder
SELECT DISTINCT
''UrgentOrder'' AS order_number,
a.productnumber AS product_number ,
a.ProductName AS product_name ,
a.FDateMonth AS creat_date ,
a.orderqty AS order_qty ,
1 AS data_type
from [172.16.200.78].[APSBPM].[dbo].[TBL_UrgentPreOrder] a
inner join [172.16.200.78].[APSBPM].[dbo].[MST_BaseItemEntity] e on a.ProductCategory=e.Id
where a.DelFlag=0
AND a.IsPreOrUrgent=1 ----IsPreOrUrgent单据类型：1加急 0备单
AND a.orderqty > 0
AND convert(varchar(10),a.FDateMonth,120)=convert(varchar(10),getdate(),120) --获取当天数据
';
--产品分类不为空时
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '0')--产品分类为PPR
-- SET @sql_UrgentOrder = CONCAT(@sql_UrgentOrder,' AND a.ProductClass=0 ');
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '1')--产品分类为PVC
--SET @sql_UrgentOrder = CONCAT(@sql_UrgentOrder,' AND a.ProductClass=1 ');
DECLARE @sql_UrgentOrder_PPRClass NVARCHAR(MAX) = N''
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('0',@ProductClass)>0)--PPR
SET @sql_UrgentOrder_PPRClass = CONCAT(@sql_UrgentOrder_PPRClass,' OR e.PPRClass=0 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('1',@ProductClass)>0)--PVC
SET @sql_UrgentOrder_PPRClass = CONCAT(@sql_UrgentOrder_PPRClass,' OR e.PPRClass=1 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('2',@ProductClass)>0)--PE
SET @sql_UrgentOrder_PPRClass = CONCAT(@sql_UrgentOrder_PPRClass,' OR e.PPRClass=2 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('3',@ProductClass)>0)--PB
SET @sql_UrgentOrder_PPRClass = CONCAT(@sql_UrgentOrder_PPRClass,' OR e.PPRClass=3 ');
IF @sql_UrgentOrder_PPRClass IS NOT NULL AND @sql_UrgentOrder_PPRClass !=''
SET @sql_UrgentOrder = @sql_UrgentOrder+' AND (' + substring(@sql_UrgentOrder_PPRClass,4,len(@sql_UrgentOrder_PPRClass)-1) + ')';--将产品分类条件SQL追加到查询SQL中
--责任部门不为空时
IF(@ProductDepartment IS NOT NULL AND @ProductDepartment != '')
SET @sql_UrgentOrder = CONCAT(@sql_UrgentOrder,' AND a.ProductDepartmentName = @ProductDepartment ');
SET @Parm_UrgentOrder= '@ProductDepartment NVARCHAR(50)'
EXEC sp_executesql @sql_UrgentOrder,@Parm_UrgentOrder, @ProductDepartment = @ProductDepartment;
----测试获取加急数据
--PRINT '测试获取加急数据'
--SELECT * FROM #UrgentOrder;
--SELECT GETDATE(),'#UrgentOrder';--检测耗时
/*--------------------------------------------------------------------------------------*/
/************************备单************************/
DECLARE @Parm_PreOrder NVARCHAR(MAX) = N'',
@sql_PreOrder NVARCHAR(MAX) = N''
SET @sql_PreOrder = '
INSERT INTO #PreOrder
SELECT DISTINCT
''#PreOrder'' AS order_number,
a.productnumber AS product_number ,
a.ProductName AS product_name ,
a.FDateMonth AS creat_date ,
a.orderqty AS order_qty ,
4 AS data_type
from [172.16.200.78].[APSBPM].[dbo].[TBL_UrgentPreOrder] a
inner join [172.16.200.78].[APSBPM].[dbo].[MST_BaseItemEntity] e on a.ProductCategory=e.Id
where a.DelFlag=0
AND a.IsPreOrUrgent=0 ----IsPreOrUrgent单据类型：1加急 0备单
AND a.orderqty > 0
AND convert(varchar(10),a.FDateMonth,120)=convert(varchar(10),getdate(),120) --获取当天数据
';
--产品分类不为空时
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '0')--产品分类为PPR
-- SET @sql_PreOrder = CONCAT(@sql_PreOrder,' AND a.ProductClass=0 ');
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '1')--产品分类为PVC
-- SET @sql_PreOrder = CONCAT(@sql_PreOrder,' AND a.ProductClass=1 ');
DECLARE @sql_PreOrder_PPRClass NVARCHAR(MAX) = N''
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('0',@ProductClass)>0)--PPR
SET @sql_PreOrder_PPRClass = CONCAT(@sql_PreOrder_PPRClass,' OR e.PPRClass=0 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('1',@ProductClass)>0)--PVC
SET @sql_PreOrder_PPRClass = CONCAT(@sql_PreOrder_PPRClass,' OR e.PPRClass=1 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('2',@ProductClass)>0)--PE
SET @sql_PreOrder_PPRClass = CONCAT(@sql_PreOrder_PPRClass,' OR e.PPRClass=2 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('3',@ProductClass)>0)--PB
SET @sql_PreOrder_PPRClass = CONCAT(@sql_PreOrder_PPRClass,' OR e.PPRClass=3 ');
IF @sql_PreOrder_PPRClass IS NOT NULL AND @sql_PreOrder_PPRClass !=''
SET @sql_PreOrder = @sql_PreOrder+' AND (' + substring(@sql_PreOrder_PPRClass,4,len(@sql_PreOrder_PPRClass)-1) + ')';--将产品分类条件SQL追加到查询SQL中
--责任部门不为空时
IF(@ProductDepartment IS NOT NULL AND @ProductDepartment != '')
SET @sql_PreOrder = CONCAT(@sql_PreOrder,' AND a.ProductDepartmentName = @ProductDepartment ');
SET @Parm_PreOrder= '@ProductDepartment NVARCHAR(50)'
EXEC sp_executesql @sql_PreOrder,@Parm_PreOrder, @ProductDepartment = @ProductDepartment;
/*--------------------------------------------------------------------------------------*/
/************************市场计划************************/
DECLARE @Parm_PlanOrder NVARCHAR(MAX) = N'',
@sql_PlanOrder NVARCHAR(MAX) = N''
SET @sql_PlanOrder = '
INSERT INTO #PlanOrder
SELECT DISTINCT
''PlanOrder'' AS order_number,
a.product_number ,
a.product_name ,
a.creat_date ,
a.order_qty ,
2 AS data_type
FROM dbo.T_EZ_SalePlanDetail a
LEFT JOIN ( SELECT cancel_order_number ,
SUM(order_qty) AS order_qty
FROM dbo.V_EZ_SalePlanCancel
GROUP BY cancel_order_number
) vc ON vc.cancel_order_number = a.order_number
LEFT JOIN ( SELECT t1.order_number ,
( SELECT t2.FName + '',''
FROM ( SELECT n1.order_number ,
n3.FName
FROM dbo.Ral_SalePlan_SEOrder n1
LEFT JOIN dbo.SEOrder n2 ON n2.FBillNo = n1.se_order_number
LEFT JOIN dbo.t_Item n3 ON n3.FItemID = n2.FHeadSelfS0132 --仓库ID
WHERE 1=1
';
--产品分类不为空时
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '0')--产品分类为PPR
SET @sql_PlanOrder = CONCAT(@sql_PlanOrder,' AND n3.FNumber IN ( ''02.046'',''02.047'') ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '1')--产品分类为PVC
SET @sql_PlanOrder = CONCAT(@sql_PlanOrder,' AND n3.FNumber IN ( ''02.039'',''02.042'', ''02.043'', ''02.044'') ');
SET @sql_PlanOrder = CONCAT(@sql_PlanOrder,' ) t2
WHERE t2.order_number = t1.order_number
FOR
XML PATH('''')
) AS fcustomer_name
FROM ( SELECT n1.order_number ,
n3.FName
FROM dbo.Ral_SalePlan_SEOrder n1
LEFT JOIN dbo.SEOrder n2 ON n2.FBillNo = n1.se_order_number
LEFT JOIN dbo.t_Item n3 ON n3.FItemID = n2.FHeadSelfS0132 --仓库ID
WHERE 1=1
');
--产品分类不为空时
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '0')--产品分类为PPR
SET @sql_PlanOrder = CONCAT(@sql_PlanOrder,' AND n3.FNumber IN ( ''02.046'',''02.047'') ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '1')--产品分类为PVC
SET @sql_PlanOrder = CONCAT(@sql_PlanOrder,' AND n3.FNumber IN ( ''02.039'',''02.042'', ''02.043'', ''02.044'') ');
SET @sql_PlanOrder = CONCAT(@sql_PlanOrder,' ) t1
GROUP BY t1.order_number
) d ON d.order_number = a.order_number
AND a.fstatus IN ( 1, 2, 3 )
WHERE 1 = 1
AND a.order_qty > 0
AND a.product_name NOT LIKE ''%管材%''
AND a.product_number NOT IN ( '''', ''冲红'', ''建档'' )--过滤物料代码为空、冲红、建档的数据
--AND a.product_department IN ( ''PE生产部'', ''管件生产部'' )
--AND convert(varchar(10),a.creat_date,120)=convert(varchar(10),getdate(),120) --获取当天数据
AND datediff(day,a.creat_date,getdate())<= 7 AND datediff(day,a.creat_date,getdate())>= 0 --获取7天内数据
AND (a.order_qty > a.finish_qty or a.finish_qty is null or a.finish_qty = 0) --获取未完成的计划信息，完成数量小于计划数量
');
--责任部门不为空时
IF(@ProductDepartment IS NOT NULL AND @ProductDepartment != '')
SET @sql_PlanOrder = CONCAT(@sql_PlanOrder,' AND a.product_department = @ProductDepartment ');
SET @Parm_PlanOrder= '@ProductDepartment NVARCHAR(50)'
EXEC sp_executesql @sql_PlanOrder,@Parm_PlanOrder, @ProductDepartment = @ProductDepartment;
/*--------------------------------------------------------------------------------------*/
/************************补库标、库存、所有库存************************/
DECLARE @CY INT ,--开始年份
@CP INT ,--开始月份
@BDate DATETIME;--开始日期
SELECT @CY = FValue
FROM dbo.t_SystemProfile
WHERE FKey = 'CurrentYear'
AND FCategory = 'IC';--开始年份
SELECT @CP = FValue
FROM dbo.t_SystemProfile
WHERE FKey = 'CurrentPeriod'
AND FCategory = 'IC';--开始月份
SET @BDate = CAST(CAST(@CY AS VARCHAR(4)) + '-' + CAST(@CP AS VARCHAR(2)) + '-01' AS DATETIME);--开始日期
/*--------------------------------------------------------------------------------------*/
----期初余额
CREATE TABLE #yeval
(
FStockID VARCHAR(100) , --仓库ID
FItemID VARCHAR(100) , --物料ID
FQty DECIMAL(20, 2) , --数量
);
----入库合计
CREATE TABLE #rkval
(
FStockID VARCHAR(100) , --仓库ID
FItemID VARCHAR(100) , --物料ID
FQty DECIMAL(20, 2) , --数量
);
----出库合计
CREATE TABLE #ckval
(
FStockID VARCHAR(100) , --仓库ID
FItemID VARCHAR(100) , --物料ID
FQty DECIMAL(20, 2) , --数量
);
----半成品库
CREATE TABLE #bcpkval
(
FStockID VARCHAR(100) , --仓库ID
FItemID VARCHAR(100) , --物料ID
FQty DECIMAL(20, 2) , --数量
);
----飘单入库数据
CREATE TABLE #pdrkval
(
FStockID VARCHAR(100) , --仓库ID
FItemID VARCHAR(100) , --物料ID
FQty DECIMAL(20, 2) , --数量
);
/************************期初余额************************/
DECLARE @Parm_yeval NVARCHAR(MAX) = N'',
@sql_yeval NVARCHAR(MAX) = N''
--SET @sql_yeval = '
--INSERT #yeval
--SELECT FStockID ,
-- FItemID ,
-- SUM(FBegQty) AS FQty
--FROM dbo.ICInvBal WITH ( NOLOCK )
--WHERE FYear = @CY
--AND FPeriod = @CP
--GROUP BY FStockID ,
-- FItemID
--';
--SET @Parm_yeval= '@CY NVARCHAR(50),@CP INT'
--EXEC sp_executesql @sql_yeval,@Parm_yeval, @CY = @CY, @CP = @CP;
SET @sql_yeval = '
INSERT #yeval
SELECT FStockID ,
FItemID ,
FQty
FROM [dbo].[fn_APS_GetStockData](@CY,@CP,@ProductClass,''yeval'')
';
SET @Parm_yeval= '@CY NVARCHAR(50),@CP INT,@ProductClass NVARCHAR(50)'
EXEC sp_executesql @sql_yeval,@Parm_yeval, @CY = @CY, @CP = @CP, @ProductClass = @ProductClass;
/************************入库合计************************/
DECLARE @Parm_rkval NVARCHAR(MAX) = N'',
@sql_rkval NVARCHAR(MAX) = N''
--SET @sql_rkval = '
--INSERT #rkval
--SELECT E.FDCStockID AS FStockID ,
-- E.FItemID ,
-- SUM(FQty) AS FQty
--FROM dbo.ICStockBillEntry E ,
-- dbo.ICStockBill B
--WHERE E.FInterID = B.FInterID
-- AND datediff(month,B.FDate,getdate())=0 --获取当月数据
-- AND (
-- B.FTranType < 20
-- OR B.FTranType IN ( 40, 41 )
-- )
-- AND B.FCancellation = 0
--GROUP BY E.FDCStockID ,
-- E.FItemID
--';
--SET @sql_rkval= ''
--EXEC sp_executesql @sql_rkval,@Parm_rkval;
SET @sql_rkval = '
INSERT #rkval
SELECT FStockID ,
FItemID ,
FQty
FROM [dbo].[fn_APS_GetStockData](@CY,@CP,@ProductClass,''rkval'')
';
SET @Parm_rkval= '@CY NVARCHAR(50),@CP INT,@ProductClass NVARCHAR(50)'
EXEC sp_executesql @sql_rkval,@Parm_rkval, @CY = @CY, @CP = @CP, @ProductClass = @ProductClass;
/************************出库合计************************/
DECLARE @Parm_ckval NVARCHAR(MAX) = N'',
@sql_ckval NVARCHAR(MAX) = N''
--SET @sql_ckval = '
--INSERT #ckval
--SELECT E.FSCStockID AS FStockID ,
-- E.FItemID ,
-- -SUM(FQty) AS FQty
--FROM dbo.ICStockBillEntry E ,
-- dbo.ICStockBill B
--WHERE E.FInterID = B.FInterID
-- AND datediff(month,B.FDate,getdate())=0 --获取当月数据
-- AND (
-- ( B.FTranType > 20 AND B.FTranType < 40 )
-- OR B.FTranType IN ( 41, 43 )
-- )
-- AND B.FCancellation = 0
--GROUP BY E.FSCStockID ,
-- E.FItemID
--';
--SET @sql_ckval= ''
--EXEC sp_executesql @sql_ckval,@Parm_ckval;
SET @sql_ckval = '
INSERT #ckval
SELECT FStockID ,
FItemID ,
FQty
FROM [dbo].[fn_APS_GetStockData](@CY,@CP,@ProductClass,''ckval'')
';
SET @Parm_ckval= '@CY NVARCHAR(50),@CP INT,@ProductClass NVARCHAR(50)'
EXEC sp_executesql @sql_ckval,@Parm_ckval, @CY = @CY, @CP = @CP, @ProductClass = @ProductClass;
/************************半成品库************************/
DECLARE @Parm_bcpkval NVARCHAR(MAX) = N'',
@sql_bcpkval NVARCHAR(MAX) = N''
SET @sql_bcpkval = '
INSERT #bcpkval
SELECT E.FSCStockID AS FStockID ,
E.FItemID ,
-SUM(FQty) AS FQty
FROM [AIS20190214160509].[dbo].[ICStockBillEntry] E ,
[AIS20190214160509].[dbo].[ICStockBill] B
WHERE E.FInterID = B.FInterID
AND datediff(month,B.FDate,getdate())=0 --获取当月数据
AND (
( B.FTranType > 20 AND B.FTranType < 40 )
OR B.FTranType IN ( 41, 43 )
)
AND B.FCancellation = 0
GROUP BY E.FSCStockID ,
E.FItemID
';
SET @sql_bcpkval= ''
EXEC sp_executesql @sql_bcpkval,@Parm_bcpkval;
/************************票单入库数据************************/
DECLARE @Parm_pdrkval NVARCHAR(MAX) = N'',
@sql_pdrkval NVARCHAR(MAX) = N''
SET @sql_pdrkval = '
INSERT #pdrkval
SELECT E.FDCStockID AS FStockID ,
E.FItemID ,
SUM(FQty) AS FQty
FROM dbo.ICStockBillEntry E ,
dbo.ICStockBill B
WHERE E.FInterID = B.FInterID
AND datediff(month,B.FDate,getdate())=0 --获取当月数据
AND (
B.FTranType < 20
OR B.FTranType IN ( 40, 41 )
)
AND B.FCancellation = 0
AND E.FDCStockID IN (42737,50017245)
GROUP BY E.FDCStockID ,
E.FItemID
';
SET @sql_pdrkval= ''
EXEC sp_executesql @sql_pdrkval,@Parm_pdrkval;
/*--------------------------------------------------------------------------------------*/
/*--------------------------------------------------------------------------------------*/
----补库标
DECLARE @Parm_InventoryTag NVARCHAR(MAX) = N'',
@sql_InventoryTag NVARCHAR(MAX) = N''
SET @sql_InventoryTag = '
INSERT #InventoryTag
SELECT
''InventoryTag'' AS order_number,
tbl.product_number ,
tbl.product_name ,
tbl.creat_date ,
tbl.tag_qty AS order_qty ,
3 AS data_type
FROM ( SELECT d.FNumber AS product_number ,
d.FName AS product_name ,
GETDATE() AS creat_date ,
d.FHighLimit AS tag_qty ,--库标
SUM(ISNULL(a.FQty, 0)) AS stock_qty--库存合计
FROM
-------------------------------------------------------------------------
(
SELECT FStockID,FItemID,FQty FROM #yeval AS yeval --上日余额
UNION ALL
SELECT FStockID,FItemID,FQty FROM #rkval AS rkval --入库合计
UNION ALL
SELECT FStockID,FItemID,FQty FROM #ckval AS ckval --出库合计
UNION ALL
SELECT FStockID,FItemID,FQty FROM #bcpkval AS bcpkval --半成品库
) a
-------------------------------------------------------------------------
INNER JOIN dbo.t_ICItem d ON a.FItemID = d.FItemID
--LEFT JOIN dbo.t_Stock e ON a.FStockID = e.FItemID
WHERE 1=1
AND ( d.FHighLimit - ( ISNULL(a.FQty, 0)) ) > 0 --取库标减库存大于零
AND d.FFullName NOT LIKE ''%管材%''--过滤管材数据 ';
----产品分类不为空时
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '0')--产品分类为PPR
-- SET @sql_InventoryTag = CONCAT(@sql_InventoryTag,' AND e.FNumber IN ( ''02.046'',''02.047'') ');
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '1')--产品分类为PVC
--SET @sql_InventoryTag = CONCAT(@sql_InventoryTag,' AND e.FNumber IN ( ''02.039'',''02.042'', ''02.043'', ''02.044'') ');
SET @sql_InventoryTag = CONCAT(@sql_InventoryTag,'
GROUP BY d.FNumber ,
d.FName ,
d.FHighLimit
HAVING SUM(ISNULL(a.FQty, 0)) > 0 --取库存合计大于零
) tbl
');
SET @Parm_InventoryTag= '@CY NVARCHAR(50),@CP INT,@BDate DATETIME'
EXEC sp_executesql @sql_InventoryTag,@Parm_InventoryTag, @CY = @CY, @CP = @CP, @BDate = @BDate;
/*--------------------------------------------------------------------------------------*/
/************************库存************************/
DECLARE @Parm_Inventory NVARCHAR(MAX) = N'',
@sql_Inventory NVARCHAR(MAX) = N''
SET @sql_Inventory = '
INSERT #Inventory
SELECT
''#Inventory'' AS order_number,
tbl.product_number ,
tbl.product_name ,
tbl.creat_date ,
tbl.stock_qty AS order_qty ,
5 AS data_type
FROM ( SELECT d.FNumber AS product_number ,
d.FName AS product_name ,
GETDATE() AS creat_date ,
d.FHighLimit AS tag_qty ,--库标
SUM(ISNULL(a.FQty, 0)) AS stock_qty--库存合计
FROM
-------------------------------------------------------------------------
(
SELECT FStockID,FItemID,FQty FROM #yeval AS yeval --上日余额
UNION ALL
SELECT FStockID,FItemID,FQty FROM #rkval AS rkval --入库合计
UNION ALL
SELECT FStockID,FItemID,FQty FROM #ckval AS ckval --出库合计
UNION ALL
SELECT FStockID,FItemID,FQty FROM #bcpkval AS bcpkval --半成品库
UNION ALL
SELECT FStockID,FItemID,FQty FROM #pdrkval AS pdrkval --票单入库数据
) a
-------------------------------------------------------------------------
INNER JOIN dbo.t_ICItem d ON a.FItemID = d.FItemID
--LEFT JOIN dbo.t_Stock e ON a.FStockID = e.FItemID
WHERE 1=1
AND ( d.FHighLimit - ( ISNULL(a.FQty, 0)) ) > 0 --取库标减库存大于零
AND d.FFullName NOT LIKE ''%管材%''--过滤管材数据 ';
----产品分类不为空时
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '0')--产品分类为PPR
-- SET @sql_Inventory = CONCAT(@sql_Inventory,' AND e.FNumber IN ( ''02.046'',''02.047'') ');
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '1')--产品分类为PVC
-- SET @sql_Inventory = CONCAT(@sql_Inventory,' AND e.FNumber IN ( ''02.039'',''02.042'', ''02.043'', ''02.044'') ');
SET @sql_Inventory = CONCAT(@sql_Inventory,'
GROUP BY d.FNumber ,
d.FName ,
d.FHighLimit
HAVING SUM(ISNULL(a.FQty, 0)) > 0 --取库存合计大于零
) tbl
');
SET @Parm_Inventory= '@CY NVARCHAR(50),@CP INT,@BDate DATETIME'
EXEC sp_executesql @sql_Inventory,@Parm_Inventory, @CY = @CY, @CP = @CP, @BDate = @BDate;
/*--------------------------------------------------------------------------------------*/
/************************所有库存************************/
----获取所有库存数据
DECLARE @Parm_AllInventory NVARCHAR(MAX) = N'',
@sql_AllInventory NVARCHAR(MAX) = N''
SET @sql_AllInventory = '
INSERT #AllInventory
SELECT tbl.product_number ,
tbl.product_name ,
tbl.creat_date ,
tbl.stock_qty AS stock_qty ,
CASE WHEN tbl.tag_qty = 0 THEN -1
WHEN tbl.tag_qty IS NULL THEN -1
ELSE CONVERT(DECIMAL(20, 2), ( CONVERT(DECIMAL(20, 2), tbl.stock_qty) / tbl.tag_qty ))
END AS balance_ratio --结存比=库存/库标
FROM ( SELECT d.FNumber AS product_number ,
d.FName AS product_name ,
GETDATE() AS creat_date ,
d.FHighLimit AS tag_qty ,--库标
SUM(ISNULL(a.FQty, 0)) AS stock_qty--库存合计
FROM
-------------------------------------------------------------------------
(
SELECT FStockID,FItemID,FQty FROM #yeval AS yeval --上日余额
UNION ALL
SELECT FStockID,FItemID,FQty FROM #rkval AS rkval --入库合计
UNION ALL
SELECT FStockID,FItemID,FQty FROM #ckval AS ckval --出库合计
UNION ALL
SELECT FStockID,FItemID,FQty FROM #bcpkval AS bcpkval --半成品库
UNION ALL
SELECT FStockID,FItemID,FQty FROM #pdrkval AS pdrkval --票单入库数据
) a
-------------------------------------------------------------------------
INNER JOIN dbo.t_ICItem d ON a.FItemID = d.FItemID
--LEFT JOIN dbo.t_Stock e ON a.FStockID = e.FItemID
WHERE 1=1
AND d.FFullName NOT LIKE ''%管材%''--过滤管材数据 ';
----产品分类不为空时
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '0')--产品分类为PPR
-- SET @sql_AllInventory = CONCAT(@sql_AllInventory,' AND e.FNumber IN ( ''02.046'',''02.047'') ');
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '1')--产品分类为PVC
-- SET @sql_AllInventory = CONCAT(@sql_AllInventory,' AND e.FNumber IN ( ''02.039'',''02.042'', ''02.043'', ''02.044'') ');
SET @sql_AllInventory = CONCAT(@sql_AllInventory,'
GROUP BY d.FNumber ,
d.FName ,
d.FHighLimit
) tbl
');
SET @Parm_AllInventory= '@CY NVARCHAR(50),@CP INT,@BDate DATETIME'
EXEC sp_executesql @sql_AllInventory,@Parm_AllInventory, @CY = @CY, @CP = @CP, @BDate = @BDate;
-----------------------------------------------------------------------------------------------------------------
----返回查询结果-通过拼接sql语句后执行
---------市场计划汇总
DECLARE @Parm NVARCHAR(MAX) = N'',
@sqlcommand NVARCHAR(MAX) = N''
SET @sqlcommand = '
SELECT --tb.OrderNumber,
''PlanNo'' AS OrderNumber,
tb.ProductNumber,
tb.ProductName,
SUM(tb.UrgentOrder) AS UrgentOrder ,
SUM(tb.PlanOrder) AS PlanOrder ,
SUM(tb.InventoryTag) AS InventoryTag ,
SUM(tb.PreOrder) AS PreOrder ,
SUM(tb.Inventory) AS Inventory ,
----用于数据排序：加急、计划、补库标、备单、库存
SUM(tb.IsUrgentOrder) AS IsUrgentOrder ,
SUM(tb.IsPlanOrder) AS IsPlanOrder ,
SUM(tb.IsInventoryTag) AS IsInventoryTag ,
SUM(tb.IsPreOrder) AS IsPreOrder ,
SUM(tb.IsInventory) AS IsInventory ,
SUM(tb.BalanceRatio) AS BalanceRatio ,
mp.ProductCategory,
mp.ProductCategoryName,
mp.ProductClass
FROM (
SELECT
--''PlanNo'' + CONVERT(VARCHAR, GETDATE(), 112) + RIGHT(''00000'' + CAST(t.row_num AS NVARCHAR(50)), 5) AS OrderNumber , --单号，左边不足位自动补零
--t.order_number AS OrderNumber
t.PlanOrder , --计划
t.InventoryTag , --补库标
t.Inventory , --库存
0 AS UrgentOrder , --加急
0 AS PreOrder , --备单
----用于数据排序：加急、计划、补库标、备单、库存
CASE WHEN t.PlanOrder > 0 THEN 1
ELSE 0
END AS IsPlanOrder ,
CASE WHEN t.InventoryTag > 0 THEN 1
ELSE 0
END AS IsInventoryTag ,
CASE WHEN t.Inventory > 0 THEN 1
ELSE 0
END AS IsInventory ,
0 AS IsUrgentOrder,
0 AS IsPreOrder,
CASE WHEN ait.balance_ratio > 0
THEN ait.balance_ratio * 100
ELSE 0
END AS BalanceRatio ,
t.product_number AS ProductNumber,
t.product_name AS ProductName
FROM ( SELECT
SUM(CASE WHEN tbl.data_type = 2
THEN tbl.order_qty
ELSE 0
END) AS PlanOrder ,
SUM(CASE WHEN tbl.data_type = 3
THEN tbl.order_qty
ELSE 0
END) AS InventoryTag ,
SUM(CASE WHEN tbl.data_type = 5
THEN tbl.order_qty
ELSE 0
END) AS Inventory ,
--ROW_NUMBER() OVER ( ORDER BY tbl.product_number ) AS row_num ,
tbl.product_number ,
tbl.product_name
FROM (
SELECT a.product_number ,
a.product_name ,
a.creat_date ,
a.order_qty ,
a.data_type
FROM #PlanOrder a ----计划
UNION ALL
SELECT a.product_number ,
a.product_name ,
a.creat_date ,
a.order_qty ,
a.data_type
FROM #InventoryTag a ----库标
UNION ALL
SELECT a.product_number ,
a.product_name ,
a.creat_date ,
a.order_qty ,
a.data_type
FROM #Inventory a ----库存
) tbl
GROUP BY
tbl.product_number ,
tbl.product_name
) t
LEFT JOIN #AllInventory ait ON ait.product_number = t.product_number
and ait.balance_ratio*100 <= @BalanceRatio --计划单、库标、库存按结存比过滤
WHERE 1=1
--and ait.balance_ratio*100 <= @BalanceRatio --计划单、库标、库存按结存比过滤
UNION ALL
SELECT
--''PlanNo'' + CONVERT(VARCHAR, GETDATE(), 112) + RIGHT(''00000'' + CAST(t.row_num AS NVARCHAR(50)), 5) AS OrderNumber , --单号，左边不足位自动补零
--t.order_number AS OrderNumber
0 AS PlanOrder , --计划
0 AS InventoryTag , --补库标
0 AS Inventory , --库存
t.UrgentOrder , --加急
t.PreOrder , --备单
----用于数据排序：加急、计划、补库标、备单、库存
0 AS IsPlanOrder,
0 AS IsInventoryTag,
0 AS IsInventory,
CASE WHEN t.UrgentOrder > 0 THEN 1
ELSE 0
END AS IsUrgentOrder ,
CASE WHEN t.PreOrder > 0 THEN 1
ELSE 0
END AS IsPreOrder,
0 AS BalanceRatio ,
t.product_number AS ProductNumber,
t.product_name AS ProductName
FROM (
SELECT
SUM(CASE WHEN tbl.data_type = 1
THEN tbl.order_qty
ELSE 0
END) AS UrgentOrder ,
SUM(CASE WHEN tbl.data_type = 4
THEN tbl.order_qty
ELSE 0
END) AS PreOrder ,
--ROW_NUMBER() OVER ( ORDER BY tbl.product_number ) AS row_num ,
tbl.product_number ,
tbl.product_name
FROM (
SELECT a.product_number ,
a.product_name ,
a.creat_date ,
a.order_qty ,
a.data_type
FROM #UrgentOrder a ----加急
UNION ALL
SELECT a.product_number ,
a.product_name ,
a.creat_date ,
a.order_qty ,
a.data_type
FROM #PreOrder a ----备单
) tbl
GROUP BY
tbl.product_number ,
tbl.product_name
) t
) tb
inner join [172.16.200.78].[APSBPM].[dbo].[MST_MouldProduct] mp on tb.ProductNumber=mp.K3ICFNumber --仅获取APS管件系统中存在的产品信息
inner join [172.16.200.78].[APSBPM].[dbo].[MST_BaseItemEntity] e on mp.ProductCategory=e.Id
WHERE 1=1
';
--产品分类不为空时
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '0')--产品分类为PPR
-- SET @sqlcommand = CONCAT(@sqlcommand,' AND mp.ProductClass = 0 ');
--IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND @ProductClass = '1')--产品分类为PVC
-- SET @sqlcommand = CONCAT(@sqlcommand,' AND mp.ProductClass = 1 ');
DECLARE @sqlcommand_PPRClass NVARCHAR(MAX) = N''
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('0',@ProductClass)>0)--PPR
SET @sqlcommand_PPRClass = CONCAT(@sqlcommand_PPRClass,' OR e.PPRClass=0 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('1',@ProductClass)>0)--PVC
SET @sqlcommand_PPRClass = CONCAT(@sqlcommand_PPRClass,' OR e.PPRClass=1 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('2',@ProductClass)>0)--PE
SET @sqlcommand_PPRClass = CONCAT(@sqlcommand_PPRClass,' OR e.PPRClass=2 ');
IF(@ProductClass IS NOT NULL AND @ProductClass != '' AND CHARINDEX('3',@ProductClass)>0)--PB
SET @sqlcommand_PPRClass = CONCAT(@sqlcommand_PPRClass,' OR e.PPRClass=3 ');
IF @sqlcommand_PPRClass IS NOT NULL AND @sqlcommand_PPRClass !=''
SET @sqlcommand = @sqlcommand+' AND (' + substring(@sqlcommand_PPRClass,4,len(@sqlcommand_PPRClass)-1) + ')';
print @sqlcommand_PPRClass;
--产品大类不为空时
IF(@ProductCategoryName IS NOT NULL AND @ProductCategoryName != '')
SET @sqlcommand = CONCAT(@sqlcommand,' AND mp.ProductCategoryName = @ProductCategoryName ');
SET @sqlcommand = CONCAT(@sqlcommand,'
GROUP BY
tb.ProductNumber,
tb.ProductName,
mp.ProductCategory,
mp.ProductCategoryName,
mp.ProductClass
ORDER BY
SUM(tb.IsUrgentOrder) DESC ,
SUM(tb.IsPlanOrder) DESC ,
SUM(tb.IsInventoryTag) DESC ,
SUM(tb.IsPreOrder) DESC ,
SUM(tb.IsInventory) DESC ,
SUM(tb.BalanceRatio) ASC
');
SET @Parm= '@BalanceRatio DECIMAL(20, 2),@ProductCategoryName NVARCHAR(50),@ProductClass NVARCHAR(50)'
EXEC sp_executesql @sqlcommand,@Parm,@BalanceRatio = @BalanceRatio,@ProductCategoryName = @ProductCategoryName,@ProductClass = @ProductClass;
扫一扫
08-09
1025
1025
06-20
729
729
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
