RO_ORD_METLN_VW

(SQL View)
 Index Back

Order Metrics Order Line

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

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)

# 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 #