RO_ORDMTLNBU_VW(SQL View) |
Index Back |
|---|---|
Order Metrics for Single BUThis view is used by Interactive Reports to help BAM query for Order Metrics for Single BU |
| SELECT DISTINCT A.CAPTURE_ID , A.LINE_NBR , A.LIST_PRICE , A.PRICE , A.QTY_ORDERED , A.PARENT_LINE_NBR , B.CAPTURE_TYPE_CD , (B.CAPTURE_DATE) , B.BUSINESS_UNIT , B.SOURCE_CD , B.CURRENCY_CD , B.STATUS_CODE , C.DESCR50 , D.DESCR50 , E.DESCR50 , I.DESCR , J.DESCR FROM PS_RO_LINE A , PS_RO_HEADER B , PS_RO_DEFN_HDSTAT C , PS_RO_DEFN_SOURCE D , PS_RO_TYPE E , PS_CURRENCY_CD_TBL I , PS_SP_BU_RO_NONVW J WHERE A.CAPTURE_ID = B.CAPTURE_ID AND B.CAPTURE_TYPE_CD IN ('SO','QUO') AND B.STATUS_CODE = C.STATUS_CODE AND B.SOURCE_CD = D.SOURCE_CD AND B.CAPTURE_TYPE_CD = E.CAPTURE_TYPE_CD AND C.SETID = ( SELECT F.SETID FROM PS_SET_CNTRL_REC F WHERE F.RECNAME = 'RO_DEFN_HDSTAT' AND F.SETCNTRLVALUE = B.BUSINESS_UNIT) AND D.SETID = ( SELECT H.SETID FROM PS_SET_CNTRL_REC H WHERE H.RECNAME = 'RO_DEFN_SOURCE' AND H.SETCNTRLVALUE = B.BUSINESS_UNIT) AND E.SETID = ( SELECT G.SETID FROM PS_SET_CNTRL_REC G WHERE G.RECNAME = 'RO_TYPE' AND G.SETCNTRLVALUE = B.BUSINESS_UNIT) AND I.CURRENCY_CD = A.CURRENCY_CD AND I.EFFDT = ( SELECT MAX(I_ED.EFFDT) FROM PS_CURRENCY_CD_TBL I_ED WHERE I.CURRENCY_CD = I_ED.CURRENCY_CD) AND J.BUSINESS_UNIT = A.BUSINESS_UNIT |
| # | 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 | LINE_NBR | Number(5,0) | INTEGER NOT NULL | This field represents the integer Order Line Number. |
| 3 | LIST_PRICE | Signed Number(17,4) | DECIMAL(15,4) NOT NULL | List Price |
| 4 | PRICE | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | This is price per unit for calculating the service charge. |
| 5 | QTY_ORDERED | Signed Number(16,4) | DECIMAL(14,4) NOT NULL | Order Qty |
| 6 | PARENT_LINE_NBR | Number(5,0) | INTEGER NOT NULL | Parent Line # |
| 7 | CAPTURE_TYPE_CD | Character(4) | VARCHAR2(4) NOT NULL |
Capture Type
QUO=Quote SO=Order |
| 8 | CAPTURE_DATE | Date(10) | DATE | Order capture date used for orders and quotes |
| 9 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 10 | SOURCE_CD | Character(6) | VARCHAR2(6) NOT NULL | Source Code |
| 11 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | This field stores the Currency Code value. |
| 12 | STATUS_CODE | Number(5,0) | INTEGER NOT NULL | Status Code |
| 13 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 14 | DESCR50_1 | Character(50) | VARCHAR2(50) NOT NULL | Field for Descriptions of 50 Characters |
| 15 | DESCR50_2 | Character(50) | VARCHAR2(50) NOT NULL | Description field used in various pages |
| 16 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 17 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |