RBT_LOST_ORD_VW (SQL View)	Index Back
Service Management Order View Service Management Order View for lost or stolen. Retrieves in process suspend or disconnect orders that have a reason code of lost or stolen.

RBT_LOST_ORD_VW

(SQL View)

Service Management Order View

Service Management Order View for lost or stolen. Retrieves in process suspend or disconnect orders that have a reason code of lost or stolen.

SELECT cust.BO_ID , cust.ROLE_TYPE_ID , %Coalesce(contact.BO_ID ,0) , %Coalesce(contact.ROLE_TYPE_ID ,0) ,9999999999999999999999999 ,ord.STATUS_CODE ,ord.BUSINESS_UNIT ,ord.CAPTURE_ID ,ordline.INST_PROD_ID ,ordline.HOLD_DENIAL_CD ,lost.INST_PROD_ID ,stat.DESCR50 ,reason.DESCR FROM PS_RO_HEADER ord LEFT OUTER JOIN PS_RO_BOID contact ON ord.CAPTURE_ID = contact.CAPTURE_ID AND ord.UID20F_SOLDC = contact.UID20 , PS_RO_BOID cust , PS_RO_LINE ordline , PS_RO_LINE_LOSTDTL lost , PS_RO_DEFN_HDSTAT stat , PS_RO_DEFN_HLD_DEN reason WHERE stat.SETID = ( SELECT X.SETID FROM PS_SET_CNTRL_REC X WHERE X.SETCNTRLVALUE = ord.BUSINESS_UNIT AND X.RECNAME = 'RO_DEFN_HDSTAT') AND stat.STATUS_CODE=ord.STATUS_CODE AND reason.SETID = ( SELECT X.SETID FROM PS_SET_CNTRL_REC X WHERE X.SETCNTRLVALUE = ord.BUSINESS_UNIT AND X.RECNAME = 'RO_DEFN_HLD_DEN') AND reason.HOLD_DENIAL_CD=ordline.HOLD_DENIAL_CD AND ord.CAPTURE_ID = cust.CAPTURE_ID AND ord.UID20F_SOLD = cust.UID20 AND ord.CAPTURE_ID = ordline.CAPTURE_ID AND ordline.CAPTURE_ID =lost.CAPTURE_ID AND ordline.UID20 = lost.UID20 AND ord.CAPTURE_TYPE_CD='SM' AND ord.STATUS_CODE IN(350,400,500,950,9000) AND ord.BULK_ORDER_FLAG <> 'Y' AND ordline.HOLD_DENIAL_CD IN('LOSTD','LOSTS','LOSTR')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description

1 BO_ID_CUST Number(31,0) DECIMAL(31) NOT NULL This field stores the Business Object ID for the customer.

2 ROLE_TYPE_ID_CUST Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the customer

3 BO_ID_CONTACT Number(31,0) DECIMAL(31) NOT NULL This field indicates the Business Object ID for the contact.

4 ROLE_TYPE_ID_CNTCT Number(12,0) DECIMAL(12) NOT NULL Stores ROLE_TYPE_ID for the contact

5 BO_ID_SITE Number(31,0) DECIMAL(31) NOT NULL Business Object ID

6 STATUS_CODE Number(5,0) INTEGER NOT NULL Status Code
Prompt Table: RO_DEFN_HDSTAT

7 BUSINESS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

8 CAPTURE_ID Character(20) VARCHAR2(20) NOT NULL Field use to store the capture ID in order capture.

9 INST_PROD_ID Character(20) VARCHAR2(20) NOT NULL This field is used to represent the installed product ID (key field)

10 HOLD_DENIAL_CD Character(6) VARCHAR2(6) NOT NULL Define the hold denial codes that can be used by order capture an enterprise user want to deny an order that was placed by a partner user and went on hold.

11 INST_PROD_ID_2 Character(20) VARCHAR2(20) NOT NULL Clone of Installed product ID used in work record

12 DESCR50 Character(50) VARCHAR2(50) NOT NULL Description of length 50

13 DESCR Character(30) VARCHAR2(30) NOT NULL Description

#	PeopleSoft Field Name	PeopleSoft Field Type	Database Column Type	Description
1	BO_ID_CUST	Number(31,0)	DECIMAL(31) NOT NULL	This field stores the Business Object ID for the customer.
2	ROLE_TYPE_ID_CUST	Number(12,0)	DECIMAL(12) NOT NULL	Stores ROLE_TYPE_ID for the customer
3	BO_ID_CONTACT	Number(31,0)	DECIMAL(31) NOT NULL	This field indicates the Business Object ID for the contact.
4	ROLE_TYPE_ID_CNTCT	Number(12,0)	DECIMAL(12) NOT NULL	Stores ROLE_TYPE_ID for the contact
5	BO_ID_SITE	Number(31,0)	DECIMAL(31) NOT NULL	Business Object ID
6	STATUS_CODE	Number(5,0)	INTEGER NOT NULL	Status Code Prompt Table: RO_DEFN_HDSTAT
7	BUSINESS_UNIT	Character(5)	VARCHAR2(5) NOT NULL	Business Unit
8	CAPTURE_ID	Character(20)	VARCHAR2(20) NOT NULL	Field use to store the capture ID in order capture.
9	INST_PROD_ID	Character(20)	VARCHAR2(20) NOT NULL	This field is used to represent the installed product ID (key field)
10	HOLD_DENIAL_CD	Character(6)	VARCHAR2(6) NOT NULL	Define the hold denial codes that can be used by order capture an enterprise user want to deny an order that was placed by a partner user and went on hold.
11	INST_PROD_ID_2	Character(20)	VARCHAR2(20) NOT NULL	Clone of Installed product ID used in work record
12	DESCR50	Character(50)	VARCHAR2(50) NOT NULL	Description of length 50
13	DESCR	Character(30)	VARCHAR2(30) NOT NULL	Description