RO_ORD_MBUHR_VW(SQL View) |
Index Back |
|---|---|
Order Metric Header for BUInput view for Order Capture Metric Header ACE report for specific Business Unit |
| SELECT DISTINCT K.PROBINST , A.CAPTURE_ID , M.BAM_DATE , B.CAPTURE_TYPE_CD , B.BUSINESS_UNIT , B.SOURCE_CD , B.CURRENCY_CD , B.STATUS_CODE , C.SETID , D.SETID , E.SETID , B.TOTAL_PRICE FROM PS_RO_LINE A , PS_RO_HEADER B , PS_RO_DEFN_HDSTAT C , PS_RO_DEFN_SOURCE D , PS_RO_TYPE E , PS_RO_BAM_RUN_CTL I , PS_RO_BAM_OM_INST K , PS_RB_BAM_MONTHS M WHERE I.OPRID=K.OPRID AND I.RUN_CNTL_ID=K.RUN_CNTL_ID AND A.CAPTURE_ID = B.CAPTURE_ID AND B.CAPTURE_TYPE_CD IN ('SO','QUO') AND B.BULK_ORDER_FLAG <> 'Y' AND %DateOut(B.CAPTURE_DATE) >= %DateOut(I.START_DATE) AND %DateOut(B.CAPTURE_DATE) <= %DateOut(I.END_DATE) AND %DateOut(B.CAPTURE_DATE)<= %DateOut(M.TO_DATE) AND %DateOut(B.CAPTURE_DATE) >= %DateOut(M.FROM_DATE) 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 B.BUSINESS_UNIT = I.BUSINESS_UNIT AND A.BUSINESS_UNIT = I.BUSINESS_UNIT AND C.STATUS_CODE = B.STATUS_CODE AND D.SOURCE_CD = B.SOURCE_CD AND E.CAPTURE_TYPE_CD = B.CAPTURE_TYPE_CD |
| # | 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 | RO_MONTHS |
Character(10) | VARCHAR2(10) NOT NULL | View on Months Calendar for ACE Order Metrics Reports |
| 4 | CAPTURE_TYPE_CD |
Character(4) | VARCHAR2(4) NOT NULL |
Capture Type
QUO=Quote SO=Order Prompt Table: RO_TYPE |
| 5 | BUSINESS_UNIT |
Character(5) | VARCHAR2(5) NOT NULL |
Business Unit
Prompt Table: SP_BU_RO_NONVW |
| 6 | SOURCE_CD |
Character(6) | VARCHAR2(6) NOT NULL |
Source Code
Prompt Table: RO_DEFN_SOURCE |
| 7 | CURRENCY_CD |
Character(3) | VARCHAR2(3) NOT NULL |
This field stores the Currency Code value.
Prompt Table: CURRENCY_CD_TBL |
| 8 | STATUS_CODE |
Number(5,0) | INTEGER NOT NULL |
Status Code
Prompt Table: RO_DEFN_HDSTAT |
| 9 | SETID_STATUS |
Character(5) | VARCHAR2(5) NOT NULL | MOC SETID Field |
| 10 | SETID_SOURCE |
Character(5) | VARCHAR2(5) NOT NULL | MOC SETID Field |
| 11 | SETID_CAPTURE_TYPE |
Character(5) | VARCHAR2(5) NOT NULL | MOC SETID Field |
| 12 | TOTAL_PRICE | Signed Number(30,4) | DECIMAL(28,4) NOT NULL | Total Price |