USE [PLM_228]
GO
/****** Object: UserDefinedFunction [dbo].[f_GetRoughRouteOrderItem] Script Date: 11/23/2018 17:55:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[f_GetRoughRouteOrderItem]
(
@ForgingRMOI uniqueidentifier, --锻造路线点物料订货OBJID
@RoughTaskid nvarchar, --任务类型
@qualification_id uniqueidentifier
)
RETURNS uniqueidentifier
AS
BEGIN
declare @RoughRMOI uniqueidentifier = null;
WITH GetChildOrderItem1 AS
(
SELECT roleA_id,roleB_id FROM T_OrderItemOrder
WHERE roleA_id=@ForgingRMOI AND QUALIFICATION_ID = @qualification_id
UNION ALL
SELECT a.roleA_id,a.roleB_id FROM T_OrderItemOrder a
INNER JOIN GetChildOrderItem1 b ON a.roleA_id = b.roleB_id
WHERE a.QUALIFICATION_ID = @qualification_id
),
GetChildOrderItem2 as(
SELECT roleA_id,roleB_id FROM T_OrderItemOrder
WHERE roleB_id=@ForgingRMOI AND QUALIFICATION_ID = @qualification_id
UNION ALL
SELECT a.roleA_id,a.roleB_id FROM T_OrderItemOrder a
INNER JOIN GetChildOrderItem2 b ON a.roleB_id = b.roleA_id
WHERE a.QUALIFICATION_ID = @qualification_id
)
select top 1 @RoughRMOI = a.roleb_id from
(select a2.roleb_id as roleb_id from GetChildOrderItem2 as a2
join t_taskorderitem b on a2.roleb_id=b.roleb_id AND b.QUALIFICATION_ID=@qualification_id
join t_task c on b.rolea_id=c.obj_id and c.type=@RoughTaskid
union all
select a1.roleb_id as roleb_id from GetChildOrderItem1 as a1
join t_taskorderitem b on a1.roleb_id=b.roleb_id AND b.QUALIFICATION_ID=@qualification_id
join t_task c on b.rolea_id=c.obj_id and c.type=@RoughTaskid) a
return @RoughRMOI;
END
