RF_IPRD_SRH_VW2(SQL View) |
Index Back |
---|---|
Installed Product Search ViewInstalled Product Search Record View used in configurable search. |
SELECT INST.SETID , INST.INST_PROD_ID , MLPB.RB_HLEV_ID , INST.INST_PROD_TYPE , BON.BO_NAME_DISPLAY , BON.BO_NAME_AC , INST.PRODUCT_GROUP , INST.PRODUCT_ID , PROD.DESCR , LINKS.ALIAS , INST.INV_ITEM_ID , INST.SERIAL_ID , INST.ASSETTAG , BONC.BO_NAME_DISPLAY , BONC.BO_NAME_AC , INST.CAPTURE_ID , INST.EXTERNAL_ID , INST.SITE_ID , SITE.BO_NAME , INST.BO_ID_CUST , INST.ROLE_TYPE_ID_CUST , INST.BO_ID_CONTACT , INST.ROLE_TYPE_ID_CNTCT , INST.BO_ID_PTNR_CMP , INST.BO_ID_PTNR_CNT , INST.ROLETYPE_PTNR_CMP , INST.ROLETYPE_PTNR_CNT , INST.SYNCID , INST.SYNCDTTM , ACCTS.RBTACCTID , ACCOUNT.RBTACCTNO , INST.RBTNUMBER FROM ( SELECT A.SETID AS SETID , A.PROD_REL_TYPE AS PROD_REL_TYPE , A.INST_PROD_ID AS INST_PROD_ID , RELS.OUTGOING_ALIAS AS ALIAS FROM PS_RF_INST_LINK A , PS_RB_RELATIONS RELS WHERE A.PROD_REL_TYPE = RELS.PROD_REL_TYPE UNION SELECT B.SETID AS SETID , B.PROD_REL_TYPE AS PROD_REL_TYPE , B.REL_INST_PROD_ID AS INST_PROD_ID , RELS_1.INCOMING_ALIAS AS ALIAS FROM PS_RF_INST_LINK B , PS_RB_RELATIONS RELS_1 WHERE B.PROD_REL_TYPE = RELS_1.PROD_REL_TYPE UNION SELECT C.SETID AS SETID , ' ' AS PROD_REL_TYPE , C.INST_PROD_ID AS INST_PROD_ID , ' ' AS ALIAS FROM PS_RF_INST_PROD C) LINKS , ( SELECT C.SETID AS SETID , C.INST_PROD_ID AS INST_PROD_ID , C.RBTACCTID AS RBTACCTID FROM PS_RF_INST_PROD C WHERE C.RBTACCTID <> ' ' UNION SELECT D.SETID AS SETID , D.INST_PROD_ID AS INST_PROD_ID , D.RBTACCTID AS RBTACCTID FROM PS_RF_INST_RBT D UNION SELECT E.SETID AS SETID , E.INST_PROD_ID AS INST_PROD_ID , ' ' AS RBTACCTID FROM PS_RF_INST_PROD E) ACCTS , PS_RF_INST_PROD INST , PS_BO_NAME BON , PS_BO_NAME BONC , PS_RF_SITE_P_NM_VW SITE , PS_PROD_ITEM PROD , PS_PROD_MLPB_ITEM MLPB , PS_RBT_ACCOUNT ACCOUNT WHERE LINKS.SETID = INST.SETID AND LINKS.INST_PROD_ID = INST.INST_PROD_ID AND ACCTS.SETID = INST.SETID AND ACCTS.INST_PROD_ID = INST.INST_PROD_ID AND INST.BO_ID_CUST = BON.BO_ID (+) AND BON.PRIMARY_IND(+) = 'Y' AND INST.BO_ID_CONTACT = BONC.BO_ID (+) AND BONC.PRIMARY_IND (+) = 'Y' AND INST.SETID = SITE.SETCNTRLVALUE (+) AND INST.SITE_ID = SITE.SITE_ID(+) AND PROD.SETID = ( SELECT r.setid FROM ps_set_cntrl_rec r WHERE r.setcntrlvalue = INST.setid AND r.recname = 'PROD_ITEM') AND PROD.PRODUCT_ID = INST.PRODUCT_ID AND PROD.SETID = INST.SETID AND MLPB.SETID = PROD.SETID AND MLPB.PRODUCT_ID = PROD.PRODUCT_ID AND INST.INST_PROD_TYPE IN ('002', '003', '004', '005') AND ACCTS.RBTACCTID = ACCOUNT.RBTACCTID (+) AND ACCTS.SETID = ACCOUNT.SETID (+) |
# | PeopleSoft Field Name | PeopleSoft Field Type | Database Column Type | Description |
---|---|---|---|---|
1 | SETID | Character(5) | VARCHAR2(5) NOT NULL |
This field is used to store the value of SetID on various setup tables.
Default Value: OPR_DEF_TBL_RB.SETID Prompt Table: SP_SETID_NONVW |
2 | INST_PROD_ID | Character(20) | VARCHAR2(20) NOT NULL | This field is used to represent the installed product ID (key field) |
3 | RB_HLEV_ID | Character(20) | VARCHAR2(20) NOT NULL | Field used for storing multilevel structure level id information |
4 | INST_PROD_TYPE | Character(3) | VARCHAR2(3) NOT NULL |
This field is used by the Installed Product component to determine the type of product that is being registered for the Customer, Site, Department or Employee. There are four allowable types: 1) Product, which designates a product assigned to an external customer; 2) Service 3) Asset, which designates a product assigned to an internal employee or department; and 4)Agreement, which designates an Installed Agreement Product (used in Order Capture integration with Agreements).
001=Asset 002=Product 003=Service 004=Agreement 005=Commitment |
5 | BO_NAME_DISPLAY_1 | Character(80) | VARCHAR2(80) NOT NULL | This field represents the Display Name for a business object |
6 | BO_NAME_AC | Character(50) | VARCHAR2(50) NOT NULL | Name Description of Business Object |
7 | PRODUCT_GROUP | Character(10) | VARCHAR2(10) NOT NULL | PRoduct Group |
8 | PRODUCT_ID | Character(18) | VARCHAR2(18) NOT NULL |
Product ID
Prompt Table: RF_IPRD_PRD_VW |
9 | DESCR | Character(30) | VARCHAR2(30) NOT NULL | Description |
10 | LINK_DEFN_NAME | Character(30) | VARCHAR2(30) NOT NULL | Short Description. Used on may CallCenter set-up tables |
11 | INV_ITEM_ID | Character(18) | VARCHAR2(18) NOT NULL |
Item ID
Prompt Table: RF_IPRD_ITM_VW |
12 | SERIAL_ID | Character(20) | VARCHAR2(20) NOT NULL |
This field is used to represent the Serial ID of an item or installed product
Prompt Table: RF_IPRD_SN_VW |
13 | ASSETTAG | Character(40) | VARCHAR2(40) NOT NULL |
Asset Tag
Prompt Table: RF_IPRD_AT_VW |
14 | BO_NAME_DISPLAY_2 | Character(80) | VARCHAR2(80) NOT NULL | Full name of the Business Object |
15 | BO_NAME_1 | Character(50) | VARCHAR2(50) NOT NULL | BO Name 1 |
16 | CAPTURE_ID | Character(20) | VARCHAR2(20) NOT NULL | Field use to store the capture ID in order capture. |
17 | EXTERNAL_ID | Character(20) | VARCHAR2(20) NOT NULL | External ID |
18 | SITE_ID | Character(15) | VARCHAR2(15) NOT NULL | Uniquely identifies an organizational entity that purchases goods or services from the enterprise. Represents the sold-to customer which is the customer organization that places orders. The CUST_ID may or may not be the same as the identifiers for the bill-to and ship-to locations. |
19 | BO_NAME_3 | Character(50) | VARCHAR2(50) NOT NULL |
BO Name 3
Prompt Table:
RF_IPRD_SIT2_VW
|
20 | BO_ID_CUST | Number(31,0) | DECIMAL(31) NOT NULL | This field stores the Business Object ID for the customer. |
21 | ROLE_TYPE_ID_CUST | Number(12,0) | DECIMAL(12) NOT NULL | Stores ROLE_TYPE_ID for the customer |
22 | BO_ID_CONTACT | Number(31,0) | DECIMAL(31) NOT NULL | This field indicates the Business Object ID for the contact. |
23 | ROLE_TYPE_ID_CNTCT | Number(12,0) | DECIMAL(12) NOT NULL | Stores ROLE_TYPE_ID for the contact |
24 | BO_ID_PTNR_CMP | Signed Number(32,0) | DECIMAL(31) NOT NULL | Partner Company Business Object ID. |
25 | BO_ID_PTNR_CNT | Signed Number(32,0) | DECIMAL(31) NOT NULL | Partner Contact Business Object ID. |
26 | ROLETYPE_PTNR_CMP | Number(12,0) | DECIMAL(12) NOT NULL | Role Type Partner Company |
27 | ROLETYPE_PTNR_CNT | Number(12,0) | DECIMAL(12) NOT NULL | Role Type Partner Contact |
28 | SYNCID | Number(10,0) | DECIMAL(10) NOT NULL | The Synchronization ID field stores a value generated by the sync processor. The value is used to identify the type of object that the sync processor is about to handle. |
29 | SYNCDTTM | DateTime(26) | TIMESTAMP | Synchronization Last Update Date Time |
30 | RBTACCTID | Character(15) | VARCHAR2(15) NOT NULL | Unique service account indentifier |
31 | RBTACCTNO | Character(20) | VARCHAR2(20) NOT NULL | Account No |
32 | RBTNUMBER | Character(15) | VARCHAR2(15) NOT NULL | Number For Telco |