OCD_LINE_VW

(SQL View)
 Index Back

View of a Line

This is the order management dashboard view of a line.

SELECT A.CAPTURE_ID , A.UID20 , A.UID20F , A.LINE_NBR , A.QTY_ORDERED , R1.SETID , A.PRODUCT_ID , R2.SETID , A.STATUS_CODE , CASE WHEN A.PARENT_LINE_NBR=0 THEN A.PPRC_RECUR_FREQ ELSE ' ' END , CASE WHEN A.PARENT_LINE_NBR=0 THEN A.RECUR_PRICE ELSE 0 END , CASE WHEN A.PARENT_LINE_NBR=0 THEN A.PRICE ELSE 0 END , A.CURRENCY_CD , A.START_DT , A.END_DT , hdrvw.BUSINESS_UNIT , hdrvw.BO_ID_SOLD_TO_CUST , hdrvw.BO_ID_SOLD_TO_CNT , hdrvw.REGION_ID , hdrvw.SETID_CAPTURE_TYPE , hdrvw.CAPTURE_TYPE_CD , hdrvw.SETID_SRC_CD , hdrvw.SOURCE_CD , hdrvw.SETID_STATUS , hdrvw.STATUS_CODE , hdrvw.CURRENCY_DATE , destvw.SETID_CARRIER , destvw.CARRIER , destvw.REQ_ARRIVAL_DATE , destvw.REQ_SHIP_DATE , destvw.SCHED_ARRV_DATE , destvw.SCHED_SHIP_DATE FROM PS_RO_LINE A , PS_SET_CNTRL_REC R1 , PS_SET_CNTRL_REC R2 , PS_OCD_HEADER_VW hdrvw , PS_OCD_DEST_VW destvw WHERE R1.SETCNTRLVALUE = A.BUSINESS_UNIT AND R1.RECNAME='PROD_ITEM' AND R2.SETCNTRLVALUE = A.BUSINESS_UNIT AND R2.RECNAME='RO_DEFN_LNSTAT' AND hdrvw.CAPTURE_ID = A.CAPTURE_ID AND A.CAPTURE_ID = destvw.CAPTURE_ID AND A.UID20F = destvw.UID20

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 CAPTURE_ID Character(20) VARCHAR2(20) NOT NULL Field use to store the capture ID in order capture.
2 UID20 Character(20) VARCHAR2(20) NOT NULL This field is for UID of 20 characters long.
3 UID20F_DEST Character(20) VARCHAR2(20) NOT NULL Foreign key into the destination record
4 LINE_NBR Number(5,0) INTEGER NOT NULL This field represents the integer Order Line Number.
5 QTY_ORDERED Signed Number(16,4) DECIMAL(14,4) NOT NULL Order Qty
6 SETID_PRODUCT Character(5) VARCHAR2(5) NOT NULL Product SetID
7 PRODUCT_ID Character(18) VARCHAR2(18) NOT NULL Product ID

Prompt Table: PROD_ITEM

8 SETID_STATUS Character(5) VARCHAR2(5) NOT NULL MOC SETID Field
9 STATUS_CODE Number(5,0) INTEGER NOT NULL Status Code

Default Value: 1000

Prompt Table: RO_DEFN_LNSTAT

10 PPRC_RECUR_FREQ Character(2) VARCHAR2(2) NOT NULL Field used to store the recurring frequency.
00=Daily
05=Weekly
10=Monthly
15=Quarterly
20=Annually
11 RECUR_PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL Recurring Price
12 PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL This is price per unit for calculating the service charge.
13 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.
14 START_DT Date(10) DATE Start date field to store start date value.
15 END_DT Date(10) DATE This field stores the end date values.
16 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
17 BO_ID_SOLD_TO_CUST Number(31,0) DECIMAL(31) NOT NULL BO ID for the sold to customer
18 BO_ID_SOLD_TO_CNT Number(31,0) DECIMAL(31) NOT NULL BO ID for the sold to contact.
19 REGION_ID Character(15) VARCHAR2(15) NOT NULL This field stores the region id value.
20 SETID_CAPTURE_TYPE Character(5) VARCHAR2(5) NOT NULL MOC SETID Field
21 CAPTURE_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Capture Type
QUO=Quote
SO=Order
22 SETID_SRC_CD Character(5) VARCHAR2(5) NOT NULL Setid Source Code
23 SOURCE_CD Character(6) VARCHAR2(6) NOT NULL Source Code
24 SETID_HDSTAT Character(5) VARCHAR2(5) NOT NULL This field is for setid for header status
25 STATUS_CD_HDR Number(5,0) INTEGER NOT NULL This field contains the header status for work order statuses in FMS.
26 CURRENCY_DATE Date(10) DATE Order capture date used for orders and quotes
27 SETID_CARRIER Character(5) VARCHAR2(5) NOT NULL Carrier Setid
28 CARRIER_ID Character(10) VARCHAR2(10) NOT NULL Carrier ID
29 REQ_ARRIVAL_DATE Date(10) DATE Requested Arrival Date
30 REQ_SHIP_DATE Date(10) DATE Requested Ship Date
31 SCHED_ARRV_DATE Date(10) DATE Sch Arr Dt/Tm
32 SCHED_SHIP_DATE Date(10) DATE Sch Shp Dt/Tm