70 lines
2.3 KiB
Transact-SQL
70 lines
2.3 KiB
Transact-SQL
USE [YL_MESDB]
|
|
GO
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
ALTER PROCEDURE [dbo].[机加设备生产运营看板_加急任务查询]
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
[订单编号]
|
|
,[合同号]
|
|
,[订单类型]
|
|
,[订单状态]
|
|
,[物料编号]
|
|
,[物料描述]
|
|
,[计划数量]
|
|
,[计量单位]
|
|
,[下达日期]
|
|
,[计划开始时间]
|
|
,[计划完成]
|
|
,[自制件属性]
|
|
,加急总数 as 加急数量
|
|
,工序名称
|
|
,指派对象
|
|
,外协分组
|
|
,REPLACE(STUFF((
|
|
SELECT
|
|
' -> ' + ISNULL(d2.工序名称, '') + '(' + ISNULL(d2.指派对象, '') + ')'
|
|
+ '|' + ISNULL(CAST(d2.加工状态 AS VARCHAR(10)), '0')
|
|
+ '|' + ISNULL(CAST(CAST(ISNULL((
|
|
SELECT SUM(合格数)
|
|
FROM [dbo].[YL_质量检验_质检记录]
|
|
WHERE TaskAID = d2.TaskAID
|
|
), 0) AS INT) AS VARCHAR(10)), '0')
|
|
+ '|' + ISNULL(CONVERT(VARCHAR(2), MONTH(d2.计划开始时间)) + '月' + CONVERT(VARCHAR(2), DAY(d2.计划开始时间)) + '日', '')
|
|
+ '|' + ISNULL(CAST(d2.订单行号 AS VARCHAR(10)), '')
|
|
+ '|' + ISNULL(CAST(d2.外协分组 AS NVARCHAR(50)), '')
|
|
+ '|' + ISNULL(CAST(CAST(ISNULL((
|
|
SELECT SUM(操作数值)
|
|
FROM [dbo].[YL_加工中心_操作记录表]
|
|
WHERE TaskAID = d2.TaskAID
|
|
AND 操作类别 = '收料'
|
|
), 0) AS INT) AS VARCHAR(10)), '0')
|
|
+ '|' + ISNULL(CAST(CAST(ISNULL((
|
|
SELECT SUM(不合格数)
|
|
FROM [dbo].[YL_质量检验_质检记录]
|
|
WHERE TaskAID = d2.TaskAID
|
|
), 0) AS INT) AS VARCHAR(10)), '0')
|
|
FROM [dbo].[View_生产订单_MES] d2
|
|
WHERE d2.订单编号 = v.订单编号
|
|
ORDER BY d2.订单行号
|
|
FOR XML PATH('')
|
|
), 1, 6, ''), '>', '>') AS 工序进度详情
|
|
,(SELECT COUNT(DISTINCT 订单编号) FROM [YL_MESDB].[dbo].[View_生产订单_MES] WHERE 加急总数 > 0 AND 指派对象 NOT LIKE '%装配%') AS [加急订单总数]
|
|
FROM [YL_MESDB].[dbo].[View_生产订单_MES] v
|
|
WHERE 加急总数 > 0
|
|
AND 指派对象 NOT LIKE '%装配%'
|
|
AND 订单行号 = (
|
|
SELECT MIN(订单行号)
|
|
FROM [YL_MESDB].[dbo].[View_生产订单_MES]
|
|
WHERE 订单编号 = v.订单编号
|
|
)
|
|
|
|
END
|