PV_RECV_PO_R4

(SQL View)
 Index Back

PO Line Ship Sub-View -- Recv

For SP2 Performance tuning: Removed Category Cd

SELECT DISTINCT S.BUSINESS_UNIT , S.PO_ID , S.LINE_NBR , S.SCHED_NBR , S.DUE_DT , S.DUE_TIME , S.ORIG_PROM_DT , H.PO_DT FROM PS_PO_HDR H , PS_PO_LINE L , PS_PO_LINE_SHIP S WHERE (H.PO_STATUS = 'D' OR (H.CHNG_ORD_BATCH > 0 AND H.PO_STATUS = 'A' )) AND H.BUSINESS_UNIT = L.BUSINESS_UNIT AND H.PO_ID = L.PO_ID AND L.BUSINESS_UNIT = S.BUSINESS_UNIT AND L.PO_ID = S.PO_ID AND L.LINE_NBR = S.LINE_NBR AND L.CANCEL_STATUS = 'A' AND S.CANCEL_STATUS = 'A' AND S.SHIP_TO_CUST_ID = ' ' AND (((((L.AMT_ONLY_FLG = 'N' OR L.AMT_ONLY_FLG = ' ') AND(S.QTY_PO > ( SELECT SUM(D.QTY_SH_NETRCV_VUOM) FROM PS_RECV_LN_SHIP D WHERE S.BUSINESS_UNIT = D.BUSINESS_UNIT_PO AND S.PO_ID = D.PO_ID AND S.LINE_NBR = D.LINE_NBR AND S.SCHED_NBR = D.SCHED_NBR AND D.RECV_SHIP_STATUS <> 'X'))) OR (L.AMT_ONLY_FLG = 'Y' AND (S.MERCHANDISE_AMT > ( SELECT SUM(E.MERCHANDISE_AMT) FROM PS_RECV_LN_SHIP E WHERE S.BUSINESS_UNIT = E.BUSINESS_UNIT_PO AND S.PO_ID = E.PO_ID AND S.LINE_NBR = E.LINE_NBR AND S.SCHED_NBR = E.SCHED_NBR AND E.RECV_SHIP_STATUS <> 'X'))) OR NOT EXISTS ( SELECT 'X'FROM PS_RECV_LN_SHIP D WHERE S.BUSINESS_UNIT = D.BUSINESS_UNIT_PO AND S.PO_ID = D.PO_ID AND S.LINE_NBR = D.LINE_NBR AND S.SCHED_NBR = D.SCHED_NBR AND D.RECV_SHIP_STATUS <> 'X'))) OR 0 <( SELECT RMN.REMAIN_RCPT_QTY FROM PS_PV_RECV_REMAIN RMN WHERE RMN.BUSINESS_UNIT_PO = S.BUSINESS_UNIT AND RMN.PO_ID = S.PO_ID AND RMN.LINE_NBR = S.LINE_NBR AND RMN.SCHED_NBR = S.SCHED_NBR))

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
2 PO_ID Character(10) VARCHAR2(10) NOT NULL Purchase Order
3 LINE_NBR Number(5,0) INTEGER NOT NULL Line Number: 11/24/08 - Added TARGET label [PC product]
4 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number
5 DUE_DT Date(10) DATE Specifies the date that a transaction is due. It is a generic field used in multiple PeopleSoft applications including Receivables Payables and Purchasing. For example it represents the date that payment is due for a voucher or the date that a scheduled shipment is due to be received.
6 DUE_TIME Time(15) TIMESTAMP Due Time
7 ORIG_PROM_DT Date(10) DATE Original Promise Date
8 PO_DT Date(10) DATE Date