RO_ORD_REV_VW(SQL View) |
Index Back |
|---|---|
Enterprise Order RevenueThis view is used by Interactive Reports to help BAM query for Enterprise Order Revenue |
| SELECT DISTINCT A.CAPTURE_ID , A.BUSINESS_UNIT , (A.CAPTURE_DATE) , A.CAPTURE_TYPE_CD , A.SOURCE_CD , A.STATUS_CODE , A.TOTAL_PRICE , A.CURRENCY_CD , F.DESCR50 , H.DESCR50 , L.DESCR50 , B.DESCR , G.DESCR FROM PS_RO_HEADER A , PS_RO_DEFN_HDSTAT F , PS_RO_DEFN_SOURCE H , PS_RO_TYPE L , PS_CURRENCY_CD_TBL B , PS_SP_BU_RO_NONVW G , PS_RO_LINE I WHERE F.SETID = ( SELECT C.SETID FROM PS_SET_CNTRL_REC C WHERE C.RECNAME = 'RO_DEFN_HDSTAT' AND C.SETCNTRLVALUE = A.BUSINESS_UNIT) AND H.SETID = ( SELECT D.SETID FROM PS_SET_CNTRL_REC D WHERE D.RECNAME = 'RO_DEFN_SOURCE' AND D.SETCNTRLVALUE = A.BUSINESS_UNIT) AND L.SETID = ( SELECT E.SETID FROM PS_SET_CNTRL_REC E WHERE E.RECNAME = 'RO_TYPE' AND E.SETCNTRLVALUE = A.BUSINESS_UNIT) AND A.CAPTURE_TYPE_CD IN ('SO','QUO','IQO','FPA') AND F.STATUS_CODE = A.STATUS_CODE AND H.SOURCE_CD = A.SOURCE_CD AND L.CAPTURE_TYPE_CD = A.CAPTURE_TYPE_CD AND B.CURRENCY_CD = A.CURRENCY_CD AND B.EFFDT = ( SELECT MAX(B_ED.EFFDT) FROM PS_CURRENCY_CD_TBL B_ED WHERE B.CURRENCY_CD = B_ED.CURRENCY_CD) AND G.BUSINESS_UNIT = A.BUSINESS_UNIT AND A.CAPTURE_ID = I.CAPTURE_ID AND A.BULK_ORDER_FLAG <> 'Y' |
| # | 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 | BUSINESS_UNIT | Character(5) | VARCHAR2(5) NOT NULL | Business Unit |
| 3 | CAPTURE_DATE | Date(10) | DATE | Order capture date used for orders and quotes |
| 4 | CAPTURE_TYPE_CD | Character(4) | VARCHAR2(4) NOT NULL |
Capture Type
QUO=Quote SO=Order |
| 5 | SOURCE_CD | Character(6) | VARCHAR2(6) NOT NULL | Source Code |
| 6 | STATUS_CODE | Number(5,0) | INTEGER NOT NULL | Status Code |
| 7 | TOTAL_PRICE | Signed Number(30,4) | DECIMAL(28,4) NOT NULL | Total Price |
| 8 | CURRENCY_CD | Character(3) | VARCHAR2(3) NOT NULL | This field stores the Currency Code value. |
| 9 | DESCR50 | Character(50) | VARCHAR2(50) NOT NULL | Description of length 50 |
| 10 | DESCR50_1 | Character(50) | VARCHAR2(50) NOT NULL | Field for Descriptions of 50 Characters |
| 11 | DESCR50_2 | Character(50) | VARCHAR2(50) NOT NULL | Description field used in various pages |
| 12 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
| 13 | DESCR1 | Character(30) | VARCHAR2(30) NOT NULL | Descr |