50 lines
1.0 KiB
PL/PgSQL
50 lines
1.0 KiB
PL/PgSQL
-- =============================================
|
|
-- Author: S.E.L.
|
|
-- Create date: 2023.03.17
|
|
-- Description: Verifica ordini aperti - EXT schema/special
|
|
-- =============================================
|
|
CREATE PROCEDURE ext.stp_OrdState
|
|
(
|
|
@onlyOpen INT = 0 -- 1 = solo ordini rimasti aperti/validi
|
|
)
|
|
AS
|
|
BEGIN
|
|
-- SET NOCOUNT ON added to prevent extra result sets from
|
|
-- interfering with SELECT statements.
|
|
SET NOCOUNT ON;
|
|
|
|
|
|
|
|
IF (@onlyOpen = 0)
|
|
BEGIN
|
|
;WITH cteOrd AS
|
|
(
|
|
SELECT ProdOrder, MAX(id) as lastID
|
|
FROM syn_M2ERP_PRODORDERS
|
|
GROUP BY ProdOrder
|
|
)
|
|
SELECT m2erp.*
|
|
FROM syn_M2ERP_PRODORDERS m2erp
|
|
INNER JOIN cteord cte ON m2erp.ID=cte.lastID
|
|
ORDER BY 1 DESC
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
;WITH cteOrd AS
|
|
(
|
|
SELECT ProdOrder, MAX(id) as lastID
|
|
FROM syn_M2ERP_PRODORDERS
|
|
GROUP BY ProdOrder
|
|
)
|
|
SELECT m2erp.*
|
|
FROM syn_M2ERP_PRODORDERS m2erp
|
|
INNER JOIN cteord cte ON m2erp.ID=cte.lastID
|
|
WHERE m2erp.ProdOrderState NOT IN (3,4)
|
|
ORDER BY 1 DESC
|
|
END
|
|
|
|
|
|
|
|
END
|
|
GO
|