>>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_PROM >>>>