
GO
PRINT N'Creazione di [dbo].[v_ItemRequestOnKit]...';


GO





CREATE VIEW [dbo].[v_ItemRequestOnKit]
AS
	SELECT 
		--IL.OrdID
		OrderExtCode
		,IL.CartID
		,CartDtmx
		--,CartIndex
		--,BinID
		--,BinDtmx
		--,BinIndex
		,IL.KitID
		,IL.KitExtCode
		,ItemID
		,IL.StatusID
		,CSV.OutValue AS StatusDesc
		,ItemDtmx
		,ItemExtCode
		,ItemDesc
		,ItemNote
		,ItemQty as QtyReq
		,CASE WHEN OnCartDate IS NULL THEN 0 ELSE  ItemQty END as QtyCur
		--,MatID
		,MatExtCode
		,MatDesc
		--,MachiningReq
		--,PostProcList
		--,ProcessesReq
		--,CadFilePath
		--,PdfFilePath
		,OnCartDate
	FROM v_ItemSearchDetail AS IL
	INNER JOIN KitList KL ON IL.KitID = KL.KitID
	INNER JOIN ConfSetValues CSV ON IL.StatusID = CSV.IntVal
	WHERE	CSV.SetName = 'ItemStatus'

	UNION

	-- recupero Other Items
	SELECT	
		--OI.OrdID
		OI.OrderExtCode
		,OI.CartID
		,CartDtmx
		--,CartIndex
		--,0 AS BinID
		--,'' AS BinDtmx
		--,0 AS BinIndex
		,OI.KitID
		,OI.KitExtCode
		,OI.OtherItemID AS ItemID
		,50 AS StatusID
		,'Other PART' AS StatusDesc
		,OI.OtherItemDtmx AS ItemDtmx
		,OI.OtherItemExtCode AS ItemExtCode
		,OI.OtherItemDesc AS ItemDesc
		,'' AS ItemNote
		,QtyReq
		,QtyCur
		--,0 AS MatID
		,'' AS MatExtCode
		,'' AS MatDesc
		--,0 AS MachiningReq
		--,'' AS PostProcList
		--,'' AS ProcessesReq
		--,'' AS CadFilePath
		--,'' AS PdfFilePath
		,OnCartDate
	FROM v_OtherItemKitDetail AS OI
	INNER JOIN KitList KL ON OI.KitID = KL.KitID
	INNER JOIN Carts CL ON CL.CartID = KL.CartID
GO
PRINT N'Aggiornamento completato.';
