PV_RTV_REQ_VW1(SQL View) |
Index Back |
|---|
| SELECT A.BUSINESS_UNIT_PO , A.PO_ID , A.LINE_NBR , A.SCHED_NBR , A.BUSINESS_UNIT_REQ , A.REQ_ID , A.REQ_LINE_NBR , A.REQ_SCHED_NBR , A.REMAIN_RCPT_QTY , A.QTY_PO , MAX(R.QTY_SH_ACCPT) , MAX(R.QTY_SH_RTN) FROM PS_PV_RECV_REMN_VW A , PS_RECV_LN_SHIP R WHERE A.BUSINESS_UNIT_PO = R.BUSINESS_UNIT_PO AND A.PO_ID = R.PO_ID AND A.LINE_NBR = R.LINE_NBR AND A.SCHED_NBR = R.SCHED_NBR AND (R.RECV_SHIP_STATUS <> 'X' OR EXISTS ( SELECT 'X' FROM PS_RECV_HDR RH WHERE RH.BUSINESS_UNIT = R.BUSINESS_UNIT AND RH.RECEIVER_ID = R.RECEIVER_ID AND RH.RECV_STATUS <> 'X')) AND EXISTS( SELECT 'X' FROM PS_PO_HDR PH , PS_PO_LINE PL WHERE PH.BUSINESS_UNIT = A.BUSINESS_UNIT_PO AND PH.PO_ID = A.PO_ID AND PL.BUSINESS_UNIT = A.BUSINESS_UNIT_PO AND PL.PO_ID = A.PO_ID AND PL.LINE_NBR = A.LINE_NBR AND (PH.PO_STATUS <> 'X' OR PL.CANCEL_STATUS <> 'X')) GROUP BY A.BUSINESS_UNIT_PO, A.PO_ID, A.LINE_NBR, A.SCHED_NBR, A.BUSINESS_UNIT_REQ, A.REQ_ID, A.REQ_LINE_NBR, A.REQ_SCHED_NBR, A.REMAIN_RCPT_QTY, A.QTY_PO |
| # | 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 | BUSINESS_UNIT_REQ | Character(5) | VARCHAR2(5) NOT NULL | Requesting Business Unit |
| 6 | REQ_ID | Character(10) | VARCHAR2(10) NOT NULL | Requisition ID |
| 7 | REQ_LINE_NBR | Number(5,0) | INTEGER NOT NULL | Requisition Line Number |
| 8 | REQ_SCHED_NBR | Number(3,0) | SMALLINT NOT NULL | Requisition Schedule Number |
| 9 | REMAIN_RCPT_QTY | Number(16,4) | DECIMAL(15,4) NOT NULL | Remaining Receipt Qty |
| 10 | QTY_PO | Number(16,4) | DECIMAL(15,4) NOT NULL | Purchase Order Quantity |
| 11 | QTY_SH_ACCPT | Number(16,4) | DECIMAL(15,4) NOT NULL | Qty Accepted in Receipt UOM |
| 12 | QTY_SH_RTN | Number(16,4) | DECIMAL(15,4) NOT NULL | Qty Returned in Receipt UOM |