85 lines
1.5 KiB
Transact-SQL
85 lines
1.5 KiB
Transact-SQL
|
|
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.';
|