|
SELECT C.SETID , D.BUSINESS_UNIT ,C.CUST_ID ,C.SHIP_TO_CUST_ID , COUNT(*) , ( SELECT COUNT(*) FROM PS_IN_DEMAND IN_DEMAND , PS_ORD_SCHEDULE S WHERE IN_DEMAND.DEMAND_SOURCE = 'OM' AND S.SHIP_FROM_BU = IN_DEMAND.BUSINESS_UNIT AND S.BUSINESS_UNIT = IN_DEMAND.SOURCE_BUS_UNIT AND S.ORDER_NO = IN_DEMAND.ORDER_NO AND S.ORDER_INT_LINE_NO = IN_DEMAND.ORDER_INT_LINE_NO AND S.SCHED_LINE_NBR = IN_DEMAND.SCHED_LINE_NBR AND IN_DEMAND.BUSINESS_UNIT = D.BUSINESS_UNIT AND IN_DEMAND.SHIP_TO_CUST_ID = C.SHIP_TO_CUST_ID GROUP BY IN_DEMAND.BUSINESS_UNIT,S.SHIP_TO_CUST_ID) FROM PS_CUST_SHIPTO C , PS_IN_DEMAND D , PS_ORD_SCHEDULE S WHERE D.IN_FULFILL_STATE IN ('60', '70') AND D.SHIP_DTTM IS NOT NULL AND C.CUST_ID = D.CUST_ID AND C.SHIP_TO_CUST_ID = D.SHIP_TO_CUST_ID AND D.DEMAND_SOURCE = 'OM' AND S.SHIP_FROM_BU = D.BUSINESS_UNIT AND S.BUSINESS_UNIT = D.SOURCE_BUS_UNIT AND S.ORDER_NO = D.ORDER_NO AND S.ORDER_INT_LINE_NO = D.ORDER_INT_LINE_NO AND S.SCHED_LINE_NBR = D.SCHED_LINE_NBR AND %DateDiff(%Datepart(SHIP_DTTM),%DatePart(REQ_SHIP_DTTM)) >= 0 GROUP BY C.SETID, D.BUSINESS_UNIT,C.CUST_ID,C.SHIP_TO_CUST_ID
|