OMOT_HOLD_VW

(SQL View)
 Index Back

Schedule Holds

Schedule Holds 03/02/2010 kf 1959558000 Modified SQL so that Track order page shows orders with multiple holds

SELECT DISTINCT A.BUSINESS_UNIT , A.ORDER_NO , B.ORDER_INT_LINE_NO , B.SCHED_LINE_NBR , A.ORDER_INT_LINE_NO , A.SCHED_LINE_NBR , A.STATUS_DT , C.SOLD_TO_CUST_ID FROM PS_ORD_HOLD A , PS_ORD_SCHEDULE B , PS_ORD_HEADER C WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.ORDER_NO = B.ORDER_NO AND A.BUSINESS_UNIT = C.BUSINESS_UNIT AND A.ORDER_NO = C.ORDER_NO AND ( (A.ORDER_INT_LINE_NO = 0 AND A.SCHED_LINE_NBR = 0) OR (A.ORDER_INT_LINE_NO = B.ORDER_INT_LINE_NO AND A.SCHED_LINE_NBR = 0) OR (A.ORDER_INT_LINE_NO = B.ORDER_INT_LINE_NO AND A.SCHED_LINE_NBR = B.SCHED_LINE_NBR) ) AND A.EFF_STATUS = 'A' AND A.STATUS_DT = ( SELECT MAX(X.STATUS_DT) FROM PS_ORD_HOLD X WHERE X.BUSINESS_UNIT = A.BUSINESS_UNIT AND X.ORDER_NO = A.ORDER_NO AND X.ORDER_INT_LINE_NO = A.ORDER_INT_LINE_NO AND X.SCHED_LINE_NBR = A.SCHED_LINE_NBR AND X.STATUS_DT <= %CurrentDateIn AND X.EFF_STATUS = 'A' )

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 ORDER_NO Character(10) VARCHAR2(10) NOT NULL Identifies a customer order number that appears as a reference on a receivables pending item. Date Initials Issue Description 022701 mv SP2 (F-CJORGENS-3) CSR Desktop
3 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
4 SCHED_LINE_NBR Number(6,0) INTEGER NOT NULL Schedule Line Number
5 REF_LINE_NBR Number(5,0) INTEGER NOT NULL Reference Line Number
6 REF_SCHED_NBR Number(6,0) INTEGER NOT NULL Referenced Schedule Number
7 STATUS_DT Date(10) DATE Status Date
8 SOLD_TO_CUST_ID Character(15) VARCHAR2(15) NOT NULL Sold To Customer