RO_ORD_MBULN_VW

(SQL View)
 Index Back

Order Metrics for Single BU

This view is used by Interactive Reports to help BAM query for Order Metrics Order Line for Single BU

SELECT DISTINCT L.PROBINST , A.CAPTURE_ID , A.LINE_NBR , B.CAPTURE_TYPE_CD , M.BAM_DATE , B.BUSINESS_UNIT , B.SOURCE_CD , B.CURRENCY_CD , %NumToChar(B.STATUS_CODE) , C.SETID , D.SETID , E.SETID , A.LIST_PRICE , A.PRICE , A.QTY_ORDERED , A.PARENT_LINE_NBR 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 , PS_RO_BAM_RUN_CTL K , PS_RO_BAM_OM_INST L , PS_RB_BAM_MONTHS M WHERE L.OPRID=K.OPRID AND L.RUN_CNTL_ID=K.RUN_CNTL_ID AND 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.BULK_ORDER_FLAG <> 'Y' 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 AND %DateOut(B.CAPTURE_DATE) >= %DateOut(K.START_DATE) AND %DateOut(B.CAPTURE_DATE) <= %DateOut(K.END_DATE) AND %DateOut(B.CAPTURE_DATE)<= %DateOut(M.TO_DATE) AND %DateOut(B.CAPTURE_DATE) >= %DateOut(M.FROM_DATE) AND A.BUSINESS_UNIT = K.BUSINESS_UNIT AND B.BUSINESS_UNIT = K.BUSINESS_UNIT

# 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 LINE_NBR Number(5,0) INTEGER NOT NULL This field represents the integer Order Line Number.
4 CAPTURE_TYPE_CD Character(4) VARCHAR2(4) NOT NULL Capture Type
QUO=Quote
SO=Order
5 RO_MONTHS Character(10) VARCHAR2(10) NOT NULL View on Months Calendar for ACE Order Metrics Reports
6 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit
7 SOURCE_CD Character(6) VARCHAR2(6) NOT NULL Source Code
8 CURRENCY_CD Character(3) VARCHAR2(3) NOT NULL This field stores the Currency Code value.
9 STATUS_CD_STR Character(5) VARCHAR2(5) NOT NULL Status Code
10 SETID_STATUS Character(5) VARCHAR2(5) NOT NULL MOC SETID Field
11 SETID_SOURCE Character(5) VARCHAR2(5) NOT NULL MOC SETID Field
12 SETID_CAPTURE_TYPE Character(5) VARCHAR2(5) NOT NULL MOC SETID Field
13 LIST_PRICE Signed Number(17,4) DECIMAL(15,4) NOT NULL List Price
14 PRICE Signed Number(16,4) DECIMAL(14,4) NOT NULL This is price per unit for calculating the service charge.
15 QTY_ORDERED Signed Number(16,4) DECIMAL(14,4) NOT NULL Order Qty
16 PARENT_LINE_NBR Number(5,0) INTEGER NOT NULL Parent Line #