AM_WC_POVALIDAT

(SQL View)
 Index Back

AM WorkCenter PO Validation

PO/AM Integration Validation

SELECT C.BUSINESS_UNIT_PO , C.PO_ID , C.LINE_NBR , C.PO_DIST_LINE_NUM , C.SCHED_NBR , B.BUSINESS_UNIT , B.RECEIVER_ID , B.RECV_LN_NBR , B.RECV_SHIP_SEQ_NBR , B.DISTRIB_LINE_NUM , B.DISTRIB_SEQ_NUM , B.RECV_AM_STATUS , B.BUSINESS_UNIT_AM , B.ASSET_ID , A.RECEIPT_DT , D.DESCR254_MIXED FROM PS_RECV_HDR A , PS_RECV_LN_ASSET B , PS_RECV_LN_DISTRIB C , PS_RECV_LN_SHIP D WHERE A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.RECEIVER_ID = B.RECEIVER_ID AND C.BUSINESS_UNIT = B.BUSINESS_UNIT AND C.RECEIVER_ID = B.RECEIVER_ID AND C.RECV_LN_NBR = B.RECV_LN_NBR AND C.RECV_SHIP_SEQ_NBR = B.RECV_SHIP_SEQ_NBR AND C.DISTRIB_LINE_NUM = B.DISTRIB_LINE_NUM AND D.BUSINESS_UNIT = C.BUSINESS_UNIT AND D.RECEIVER_ID = C.RECEIVER_ID AND D.RECV_LN_NBR = C.RECV_LN_NBR AND D.RECV_SHIP_SEQ_NBR = C.RECV_SHIP_SEQ_NBR AND B.BUSINESS_UNIT_AM <> ' ' AND B.PROFILE_ID <> ' ' AND B.RECV_AM_STATUS IN ('M', 'O', 'R') AND NOT EXISTS ( SELECT 'X' FROM PS_ASSET_ACQ_DET D WHERE D.BUSINESS_UNIT_PO = C.BUSINESS_UNIT_PO AND D.PO_ID = C.PO_ID AND D.PO_LINE_NBR = C.LINE_NBR AND D.PO_DIST_LINE_NUM = C.PO_DIST_LINE_NUM AND D.SCHED_NBR = C.SCHED_NBR AND D.BUSINESS_UNIT_RECV = B.BUSINESS_UNIT AND D.RECEIVER_ID = B.RECEIVER_ID AND D.RECV_LN_NBR = B.RECV_LN_NBR AND D.RECV_SHIP_SEQ_NBR = B.RECV_SHIP_SEQ_NBR AND D.RECV_DIST_LINE_NUM = B.DISTRIB_LINE_NUM AND D.DISTRIB_SEQ_NUM = B.DISTRIB_SEQ_NUM AND D.SYSTEM_SOURCE = 'PPO')

# 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 PO_DIST_LINE_NUM Number(5,0) INTEGER NOT NULL PO Distribution Line Number
5 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number
6 BUSINESS_UNIT_RECV Character(5) VARCHAR2(5) NOT NULL Receiving Business Unit
7 RECEIVER_ID Character(10) VARCHAR2(10) NOT NULL Receiver number
8 RECV_LN_NBR Number(5,0) INTEGER NOT NULL Receipt Line
9 RECV_SHIP_SEQ_NBR Number(3,0) SMALLINT NOT NULL Receiver Shipping Sequence
10 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
11 DISTRIB_SEQ_NUM Number(5,0) INTEGER NOT NULL Distribution Sequence
12 RECV_AM_STATUS Character(1) VARCHAR2(1) NOT NULL Interface Status
C=Closed
H=Hold
M=Moved
O=Open
R=Received
X=Cancelled
13 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
14 ASSET_ID Character(12) VARCHAR2(12) NOT NULL Asset Identification
15 RECEIPT_DT Date(10) DATE Received Date
16 DESCR254_MIXED Character(254) VARCHAR2(254) NOT NULL Description