RO_ORD_REBUS_VW(SQL View) |
Index Back |
---|---|
Enterprise Order RevenueInput view for Enterprise Order Revenue ACE report. The view is for all business units. |
SELECT DISTINCT K.PROBINST , A.CAPTURE_ID , A.BUSINESS_UNIT , F.SETID , H.SETID , L.SETID , B.EFFDT , %NumToChar(A.STATUS_CODE) , A.SOURCE_CD , A.CAPTURE_TYPE_CD , A.CURRENCY_CD , BM.BAM_DATE , A.TOTAL_PRICE , 1 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 , PS_RO_BAM_RUN_CTL D , PS_RO_BAM_RUN_INST K , PS_RB_BAM_MONTHS BM 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 A.CAPTURE_DATE >= D.START_DATE AND A.CAPTURE_DATE <= D.END_DATE 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' AND D.OPRID = K.OPRID AND D.RUN_CNTL_ID = K.RUN_CNTL_ID AND D.BUSINESS_UNIT = A.BUSINESS_UNIT AND A.CAPTURE_DATE >= BM.FROM_DATE AND A.CAPTURE_DATE <= BM.TO_DATE |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | Character(20) | VARCHAR2(20) NOT NULL | Optimization Problem Instance | |
2 | Character(20) | VARCHAR2(20) NOT NULL | Field use to store the capture ID in order capture. | |
3 | Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_RO_NONVW |
|
4 | Character(5) | VARCHAR2(5) NOT NULL | MOC SETID Field | |
5 | Character(5) | VARCHAR2(5) NOT NULL | MOC SETID Field | |
6 | Character(5) | VARCHAR2(5) NOT NULL | MOC SETID Field | |
7 | Date(10) | DATE |
Effective Date
Default Value: %date |
|
8 | Character(5) | VARCHAR2(5) NOT NULL | Status Code | |
9 | Character(6) | VARCHAR2(6) NOT NULL | Source Code | |
10 | Character(4) | VARCHAR2(4) NOT NULL |
Capture Type
QUO=Quote SO=Order |
|
11 | Character(3) | VARCHAR2(3) NOT NULL | This field stores the Currency Code value. | |
12 | Character(10) | VARCHAR2(10) NOT NULL | This is a Date field, to be used in BAM. | |
13 | TOTAL_PRICE | Signed Number(30,4) | DECIMAL(28,4) NOT NULL | Total Price |
14 | TOTAL_COUNT | Number(7,0) | INTEGER NOT NULL | Total Count |