USE PKDEMO5 GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO EXEC sp_DropProc 'sp_GetBlanketSalesOrders' GO CREATE PROCEDURE sp_GetBlanketSalesOrders @ProgMgr VARCHAR(25) = NULL, -- Program Manager @CU_KEY VARCHAR(12) = NULL, -- Customer @IM_KEY VARCHAR(30) = NULL, -- Item key @Outstanding INT = NULL -- If 1, only include records where OpenQty <> 0 AS SELECT CI.TEAM AS ProgramMgr, OH.OH_CUKEY AS Customer, MOC.MOC_PART_NO AS Item, IM.IM_REV AS Rev, OH.OH_CUST_PO_NUM AS CustPONum, OH.OH_NUMBER AS SPNum, MOC.MOC_REF_NO AS SOLine, MOC.MOC_USER_11 AS OrigBlanketQty, ISNULL(v_BlanketSODeliveryQtyByLine.BookedQty, 0)AS QtyOrdered, MOC_USER_11 - ISNULL(v_BlanketSODeliveryQtyByLine.BookedQty, 0) AS RemainingBlanketQty, CASE WHEN MOC_USER_21 = '1901-01-01' THEN NULL ELSE MOC_USER_21 END AS BlanketExpDate, MOC.MOC_PRICE AS LinePrice, RQ.RQ_DATE_REQ AS RequiredDate, RQ.RQ_DATE_PROM AS PromiseDate, RQ.RQ_QTY_REQ AS RequiredQty, RQ.RQ_QTY_ISS AS ShippedQty, CASE WHEN RQ_STATUS = 'C' OR RQ_QTY_ISS > RQ_QTY_REQ THEN 0 ELSE RQ_QTY_REQ - RQ_QTY_ISS END AS OpenQty FROM OH INNER JOIN MOC ON OH.OH_NUMBER = MOC.MOC_OHNUMBER LEFT JOIN RQ ON (MOC.MOC_OHNUMBER = RQ.RQ_MOCNUMBER) AND (MOC.MOC_REF_NO = RQ.RQ_MOCREFNO) LEFT JOIN v_BlanketSODeliveryQtyByLine ON (MOC.MOC_OHNUMBER = v_BlanketSODeliveryQtyByLine.RQ_MOCNUMBER) AND (MOC.MOC_REF_NO = v_BlanketSODeliveryQtyByLine.RQ_MOCREFNO) LEFT JOIN IM ON MOC.MOC_PART_NO = IM.IM_KEY LEFT JOIN ONCORE_SM.dbo.NV_CUST_INFO CI ON IM.IM_CATALOG = CI.CUCODE WHERE ((MOC.MOC_USER_11 <> 0) AND (MOC.MOC_STATUS <> 4)) AND (@ProgMgr IS NULL OR CI.Team = @ProgMgr) AND (@CU_KEY IS NULL OR OH.OH_CUKEY = @CU_KEY) AND (@IM_KEY IS NULL OR IM.IM_KEY = @IM_KEY) AND (@Outstanding IS NULL OR (OpenQty <> 0)) ORDER BY CI.TEAM, OH.OH_CUKEY, MOC.MOC_PART_NO, OH.OH_NUMBER, MOC.MOC_REF_NO, RQ.RQ_DATE_REQ, RQ.RQ_DATE_PROMOpenQty is defined as the last ite in the field list