ATP_S_PO_VW

(SQL View)
 Index Back

ATP Supply -- PO View

View used to fetch purchase order supply information for ATP calculation purposes. Because production orders are always included in the calculation, this view excludes subcontract POs.

SELECT d.business_unit_in , l.inv_item_id , 'PO' , 'S' , 'PO' , d.po_id , d.line_nbr , v.name1 , s.due_dt , d.qty_po , l.unit_of_measure , h.vendor_setid , h.vendor_id , h.vndr_loc , h.recv_status , d.business_unit , d.sched_nbr , d.distrib_line_num , d.qty_po_std_exp , d.peg_status FROM ps_po_line_distrib d , PS_PO_LINE_SHIP s , ps_po_line l , ps_vendor v , ps_po_hdr h WHERE h.po_status IN ('O','PA','A','D') AND h.po_type <> 'KAN' AND v.setid = h.vendor_setid AND v.vendor_id = h.vendor_id AND l.business_unit = h.business_unit AND l.po_id = h.po_id AND l.cancel_status = 'A' AND s.business_unit = l.business_unit AND s.po_id = l.po_id AND s.line_nbr = l.line_nbr AND s.cancel_status = 'A' AND s.production_id = ' ' AND d.business_unit_in <> ' ' AND d.business_unit = s.business_unit AND d.po_id = s.po_id AND d.line_nbr = s.line_nbr AND d.sched_nbr = s.sched_nbr AND d.distrib_ln_status IN ('O','P')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
2 INV_ITEM_ID Character(18) VARCHAR2(18) NOT NULL Item ID
3 ATP_SOURCE Character(6) VARCHAR2(6) NOT NULL This field is used in the ATP and Availability calculation to determine where the particular source or demand was fetched from. It will store the up-to-6 character mnemonic used in the associated view that fetched the data. For example, if ATP_S_PO_VW was used to fetch purchase order information, this field will contain "PO". If view ATP_D_PROMIS_VW was used to fetch promised demand information, this field will contain "PROMIS".
4 AVAIL_TYPE Character(1) VARCHAR2(1) NOT NULL Availability Type
D=Demand
S=Supply
5 DEMAND_SOURCE Character(2) VARCHAR2(2) NOT NULL Demand Source
IN=Material Request
OM=Sales Order
PL=Planning Requisition
PO=Purchase Order
PR=Purchasing Requisition
RT=Material Return
SF=Production Request
WM=Work Order
6 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
7 ORDER_INT_LINE_NO Number(5,0) INTEGER NOT NULL Order Line
8 SHIP_CUST_NAME1 Character(40) VARCHAR2(40) NOT NULL Ship To Name
9 SCHED_DATE Date(10) DATE Schedule Date
10 QTY_REQUESTED Signed Number(17,4) DECIMAL(15,4) NOT NULL Qty Requested
11 UNIT_OF_MEASURE Character(3) VARCHAR2(3) NOT NULL Used on an approval rule set.
MHR=Muti Hourly
PER=Percentage
SQF=Square Footage
12 VENDOR_SETID Character(5) VARCHAR2(5) NOT NULL Vendor SetID
13 VENDOR_ID Character(10) VARCHAR2(10) NOT NULL Vendor Identifier
14 VNDR_LOC Character(10) VARCHAR2(10) NOT NULL Vendor Location
15 RECV_STATUS Character(1) VARCHAR2(1) NOT NULL Receipt Status
C=Closed Receipt
H=Hold Receipt
M=Moved to Destination
N=PO Not Received
O=Open
P=PO Partially Received
R=Fully Received
X=Canceled
16 BUSINESS_UNIT_PO Character(5) VARCHAR2(5) NOT NULL PO Business Unit
17 SCHED_NBR Number(3,0) SMALLINT NOT NULL Schedule Number
18 DISTRIB_LINE_NUM Number(5,0) INTEGER NOT NULL Distribution Line
19 QTY_PO_STD_EXP Number(16,4) DECIMAL(15,4) NOT NULL Expected PO Quantity Standard
20 PEG_STATUS Character(2) VARCHAR2(2) NOT NULL 07/21/04 EGS: Created
10=Open
20=Completed
30=Canceled
40=Not Pegged