OCD_LINE_VW(SQL View) |
Index Back |
---|---|
View of a LineThis 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 |