330 lines
6.6 KiB
Transact-SQL
330 lines
6.6 KiB
Transact-SQL
|
|
PRINT N'Creazione di [dbo].[stp_CKD_getByKey]...';
|
|
|
|
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Create date: 2020.08.25
|
|
-- Description: Recupera CART da Key
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_CKD_getByKey]
|
|
(
|
|
@CartID INT = 0
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT *
|
|
FROM v_CartKitDetailAll
|
|
WHERE CartID = @CartID
|
|
|
|
END
|
|
GO
|
|
PRINT N'Creazione di [dbo].[stp_IL_invalidateByCart]...';
|
|
|
|
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Create date: 2020.08.25
|
|
-- Description: Aggiorna l'OnCartDate a null per tutti gli item di un CART invalidando
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_IL_invalidateByCart]
|
|
(
|
|
@CartID INT = 0
|
|
,@PlaceCod NVARCHAR(50) = ''
|
|
)
|
|
AS
|
|
BEGIN
|
|
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
BEGIN TRAN
|
|
|
|
-- Faccio registrazione in EventList
|
|
INSERT INTO ItemEvents(ItemID, StatusID, DateStart, DateEnd, placeCod)
|
|
SELECT ItemID, StatusID, GETDATE(), GETDATE(), @PlaceCod
|
|
FROM ItemList IL
|
|
INNER JOIN KitList KL ON IL.KitID = KL.KitID
|
|
WHERE KL.CartID = @CartID
|
|
AND NOT (OnCartDate IS NULL)
|
|
|
|
-- invalido su PackLog
|
|
DELETE
|
|
FROM PackLog
|
|
WHERE CartID = @CartID
|
|
|
|
-- aggiorno status
|
|
UPDATE IL
|
|
SET OnCartDate = NULL
|
|
FROM ItemList IL
|
|
INNER JOIN KitList KL ON IL.KitID = KL.KitID
|
|
WHERE KL.CartID = @CartID
|
|
AND NOT (OnCartDate IS NULL)
|
|
|
|
|
|
COMMIT tran
|
|
END
|
|
GO
|
|
PRINT N'Creazione di [dbo].[stp_IL_invalidateByPartCart]...';
|
|
|
|
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Create date: 2020.08.25
|
|
-- Description: Aggiorna l'OnCartDate a null per tutti gli item di un CART invalidando
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_IL_invalidateByPartCart]
|
|
(
|
|
@CartID INT = 0
|
|
,@ItemExtCode NVARCHAR(250) = ''
|
|
,@PlaceCod NVARCHAR(50) = ''
|
|
)
|
|
AS
|
|
BEGIN
|
|
|
|
SET NOCOUNT ON;
|
|
SET XACT_ABORT ON;
|
|
|
|
BEGIN TRAN
|
|
|
|
-- Faccio registrazione in EventList
|
|
INSERT INTO ItemEvents(ItemID, StatusID, DateStart, DateEnd, placeCod)
|
|
SELECT ItemID, StatusID, GETDATE(), GETDATE(), @PlaceCod
|
|
FROM ItemList IL
|
|
INNER JOIN KitList KL ON IL.KitID = KL.KitID
|
|
WHERE KL.CartID = @CartID
|
|
AND ItemExtCode = @ItemExtCode
|
|
AND NOT (OnCartDate IS NULL)
|
|
|
|
-- invalido su PackLog
|
|
DELETE
|
|
FROM PackLog
|
|
WHERE CartID = @CartID
|
|
AND MRP_PART = @ItemExtCode
|
|
|
|
-- aggiorno status
|
|
UPDATE IL
|
|
SET OnCartDate = NULL
|
|
FROM ItemList IL
|
|
INNER JOIN KitList KL ON IL.KitID = KL.KitID
|
|
WHERE KL.CartID = @CartID
|
|
AND ItemExtCode = @ItemExtCode
|
|
AND NOT (OnCartDate IS NULL)
|
|
|
|
|
|
COMMIT tran
|
|
END
|
|
GO
|
|
PRINT N'Creazione di [dbo].[stp_IRK_getByCart]...';
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Description: Recupera TUTTI gli items RICHIESTi al KITTING dato un CART
|
|
-- Create date: 2020.08.21
|
|
--
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_IRK_getByCart]
|
|
(
|
|
@CartID INT = 0 -- 0 NON ESISTE
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT *
|
|
FROM v_ItemRequestOnKit
|
|
WHERE CartID = @CartID
|
|
|
|
END
|
|
GO
|
|
PRINT N'Creazione di [dbo].[stp_IRKSum_getByCart]...';
|
|
|
|
|
|
GO
|
|
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Description: Recupera TUTTI gli items RICHIESTi al KITTING dato un CART in modalità SUMMED
|
|
-- Create date: 2020.08.21
|
|
--
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_IRKSum_getByCart]
|
|
(
|
|
@CartID INT = 0 -- 0 NON ESISTE
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT
|
|
OrderExtCode
|
|
,CartDtmx
|
|
,ItemExtCode
|
|
,ItemDesc
|
|
,ItemNote
|
|
,SUM(QtyReq) AS TotReq
|
|
,SUM(QtyCur) AS TotCur
|
|
,MatExtCode
|
|
,MatDesc
|
|
FROM v_ItemRequestOnKit
|
|
WHERE CartID = @CartID
|
|
GROUP BY
|
|
OrderExtCode
|
|
,CartDtmx
|
|
,ItemExtCode
|
|
,ItemDesc
|
|
,ItemNote
|
|
,MatExtCode
|
|
,MatDesc
|
|
|
|
END
|
|
GO
|
|
PRINT N'Creazione di [dbo].[stp_ISD_getMissingByBatch]...';
|
|
|
|
|
|
GO
|
|
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Description: Recupera Items Missing In Action del BATCH
|
|
-- Create date: 2020.08.25
|
|
--
|
|
-- =============================================
|
|
CREATE PROCEDURE [dbo].[stp_ISD_getMissingByBatch]
|
|
(
|
|
@BatchID INT = 0 -- 0 NON ESISTE
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
SELECT IL.*
|
|
FROM v_ItemSearchDetail IL
|
|
INNER JOIN KitList KL ON IL.KitID = KL.KitID
|
|
INNER JOIN Nesting NE ON NE.ItemID = IL.ItemID
|
|
INNER JOIN SheetList SL ON SL.SheetID = NE.SheetID
|
|
WHERE KL.BatchID = @BatchID -- mi interessa solo il batch
|
|
AND IL.StatusID = 1 -- status 1 = lavorati ma NON scaricati su BIN/CART o in fasi di SCRAP/offline
|
|
AND NOT (SL.UnlEnd IS NULL) -- fogli già scaricati
|
|
|
|
END
|
|
GO
|
|
PRINT N'Creazione di [prt].[stp_prt_IRK]...';
|
|
|
|
|
|
GO
|
|
|
|
|
|
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Description: Recupera TUTTI gli items RICHIESTI al KITTING dato un CART (per report)
|
|
|
|
-- Create date: 2020.08.24
|
|
--
|
|
-- =============================================
|
|
CREATE PROCEDURE [prt].[stp_prt_IRK]
|
|
(
|
|
@CartID AS INT = 0 -- 0 NON ESISTE
|
|
,@QRCodeBaseUrl AS NVARCHAR(400) = '' -- URL per la creazione QR code
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- se non passati imposto da config
|
|
IF ISNULL( @QRCodeBaseUrl,'' ) = ''
|
|
SELECT @QRCodeBaseUrl = Valore FROM dbo.Config WHERE chiave = 'prt_QRCodeBaseUrl'
|
|
|
|
SELECT OrderExtCode
|
|
--,CartID
|
|
,CartDtmx
|
|
--,KitID
|
|
,KitExtCode
|
|
--,ItemID
|
|
--,StatusID
|
|
,StatusDesc
|
|
,ItemDtmx
|
|
,ItemExtCode
|
|
,ItemDesc
|
|
,ItemNote
|
|
,QtyReq
|
|
,QtyCur
|
|
,MatExtCode
|
|
,MatDesc
|
|
,OnCartDate
|
|
,@QRCodeBaseUrl AS QRCodeBaseUrl
|
|
FROM v_ItemRequestOnKit
|
|
WHERE CartID = @CartID
|
|
END
|
|
GO
|
|
PRINT N'Creazione di [prt].[stp_prt_IRK_Sum]...';
|
|
|
|
|
|
GO
|
|
|
|
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Description: Recupera TUTTI gli items RICHIESTi al KITTING dato un CART in modalità SUMMED
|
|
--
|
|
-- Create date: 2020.08.24
|
|
|
|
-- =============================================
|
|
CREATE PROCEDURE [prt].[stp_prt_IRK_Sum]
|
|
(
|
|
@CartID AS INT = 0 -- 0 NON ESISTE
|
|
,@QRCodeBaseUrl AS NVARCHAR(400) = '' -- URL per la creazione QR code
|
|
)
|
|
AS
|
|
BEGIN
|
|
SET NOCOUNT ON;
|
|
|
|
-- se non passati imposto da config
|
|
IF ISNULL( @QRCodeBaseUrl,'' ) = ''
|
|
SELECT @QRCodeBaseUrl = Valore FROM dbo.Config WHERE chiave = 'prt_QRCodeBaseUrl'
|
|
|
|
SELECT
|
|
OrderExtCode
|
|
,CartDtmx
|
|
,ItemExtCode
|
|
,ItemDesc
|
|
--,StatusDesc
|
|
,ItemNote
|
|
,SUM(QtyReq) AS TotReq
|
|
,SUM(QtyCur) AS TotCur
|
|
,MatExtCode
|
|
,MatDesc
|
|
,@QRCodeBaseUrl AS QRCodeBaseUrl
|
|
FROM dbo.v_ItemRequestOnKit
|
|
WHERE CartID = @CartID
|
|
GROUP BY
|
|
OrderExtCode
|
|
,CartDtmx
|
|
,ItemExtCode
|
|
,ItemDesc
|
|
--,StatusDesc
|
|
,ItemNote
|
|
,MatExtCode
|
|
,MatDesc
|
|
|
|
END
|
|
GO
|
|
PRINT N'Aggiornamento completato.';
|
|
|