RO_ORD_REVEN_VW

(SQL View)
 Index Back

Enterprise Order Revenue

Input 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 A.BULK_ORDER_FLAG <> 'Y' 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 D.OPRID = K.OPRID AND D.RUN_CNTL_ID = K.RUN_CNTL_ID 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 PROBINST Character(20) VARCHAR2(20) NOT NULL Optimization Problem Instance
2 CAPTURE_ID Character(20) VARCHAR2(20) NOT NULL Field use to store the capture ID in order capture.
3 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

Prompt Table: SP_BU_RO_NONVW

4 SETID_STATUS Character(5) VARCHAR2(5) NOT NULL MOC SETID Field
5 SETID_SOURCE Character(5) VARCHAR2(5) NOT NULL MOC SETID Field
6 SETID_CAPTURE_TYPE Character(5) VARCHAR2(5) NOT NULL MOC SETID Field
7 EFFDT Date(10) DATE Effective Date

Default Value: %date

8 STATUS_CD_STR Character(5) VARCHAR2(5) NOT NULL Status Code
9 SOURCE_CD Character(6) VARCHAR2(6) NOT NULL Source Code
10 CAPTURE_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Capture Type
QUO=Quote
SO=Order
11 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.
12 BAM_DATE 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