RF_GOPAOCHLD_VW

(SQL View)
 Index Back

Grouup Offer usrs/membrs views

View used for displaying information about associated Group Offer Users/Members on Customer 360-Degree View page

SELECT DISTINCT inst1.BO_ID_CUST AS BO_ID_CUST , inst1.ROLE_TYPE_ID_CUST AS ROLE_TYPE_ID_CUST , inst1.BO_ID_CONTACT AS BO_ID_CONTACT , inst1.ROLE_TYPE_ID_CNTCT AS ROLE_TYPE_ID_CNTCT , 0 , stat.inst_prod_status AS INST_PROD_STATUS , inst3.SETID AS SETID , inst3.INST_PROD_ID AS INST_PROD_ID , ' ' AS INST_EXT_SVC_ID , inst1.INST_PROD_ID AS INST_PROD_ID_2 , inst1.PARENT_INST_PRODID AS PARENT_INST_PRODID , prod3.DESCR AS DESCR , prod4.DESCR AS DESCR1 , name.BO_NAME_DISPLAY AS BO_NAME_DISPLAY , ' ' AS REL_INST_PROD_ID , inst3.ROW_ADDED_DTTM AS ROW_ADDED_DTTM , inst3.ROW_ADDED_OPRID AS ROW_ADDED_OPRID , inst3.ROW_LASTMANT_DTTM AS ROW_LASTMANT_DTTM , inst3.ROW_LASTMANT_OPRID AS ROW_LASTMANT_OPRID , inst3.SYNCID AS SYNCID , inst3.SYNCDTTM AS SYNCDTTM FROM ps_rf_inst_link link1 JOIN ps_rf_inst_link link2 ON link1.rel_inst_prod_id = link2.inst_prod_id AND link2.setid = link1.setid AND link1.prod_rel_type = 'SLLS' AND link1.inst_link_status = 'INS' AND link2.prod_rel_type = 'RLON' AND link2.inst_link_status = 'INS' JOIN ps_rf_inst_link link3 ON link3.setid = link2.setid AND link2.rel_inst_prod_id = link3.rel_inst_prod_id AND link3.prod_rel_type = 'SLLS' AND link3.inst_link_status = 'INS' JOIN PS_RF_INST_PROD inst1 ON inst1.setid = link1.setid AND inst1.inst_prod_id = link1.inst_prod_id JOIN PS_RF_INST_PROD inst3 ON inst3.setid = link3.setid AND inst3.inst_prod_id = link3.inst_prod_id JOIN PS_RF_INST_PROD_st stat ON stat.setid = inst1.setid AND stat.inst_prod_id = inst1.inst_prod_id JOIN ps_prod_mlpb_item mlpb1 ON mlpb1.setid = inst3.setid AND mlpb1.product_id = inst3.product_id JOIN PS_RF_INST_PROD inst4 ON inst3.TOPLVL_INSTPROD_ID = inst4.INST_PROD_ID AND inst3.SETID = inst4.SETID JOIN PS_PROD_ITEM prod3 ON inst3.setid = prod3.setid AND inst3.product_id = prod3.product_id JOIN PS_PROD_ITEM prod4 ON inst4.setid = prod4.setid AND inst4.product_id = prod4.product_id JOIN PS_BO_NAME NAME ON inst3.BO_ID_CUST = name.BO_ID UNION SELECT inst1.BO_ID_CUST AS BO_ID_CUST , inst1.ROLE_TYPE_ID_CUST AS ROLE_TYPE_ID_CUST , inst1.BO_ID_CONTACT AS BO_ID_CONTACT , inst1.ROLE_TYPE_ID_CNTCT AS ROLE_TYPE_ID_CNTCT , 0 , ' ' AS INST_PROD_STATUS , link2.SETID AS SETID , link2.INST_EXT_SVC_ID AS INST_PROD_ID , link2.INST_EXT_SVC_ID AS INST_EXT_SVC_ID , inst1.INST_PROD_ID AS INST_PROD_ID_2 , inst1.PARENT_INST_PRODID AS PARENT_INST_PRODID , prod.descr AS DESCR , ' ' , ' ' AS BO_NAME_DISPLAY , ' ' AS REL_INST_PROD_ID , ext.ROW_ADDED_DTTM AS ROW_ADDED_DTTM , ext.ROW_ADDED_OPRID AS ROW_ADDED_OPRID , ext.ROW_LASTMANT_DTTM AS ROW_LASTMANT_DTTM , ext.ROW_LASTMANT_OPRID AS ROW_LASTMANT_OPRID , ext.SYNCID AS SYNCID , ext.SYNCDTTM AS SYNCDTTM FROM ps_rf_inst_link link1 JOIN ps_rf_inst_link link2 ON link1.rel_inst_prod_id = link2.inst_prod_id AND link2.setid = link1.setid AND link1.prod_rel_type = 'SLLS' AND link1.inst_link_status = 'INS' AND link2.prod_rel_type = 'RLON' AND link2.inst_link_status = 'INS' JOIN PS_RF_INST_PROD inst1 ON inst1.setid = link1.setid AND inst1.inst_prod_id = link1.inst_prod_id JOIN PS_RF_INST_EXT ext ON ext.setid = link2.setid AND ext.inst_ext_svc_id = link2.inst_ext_svc_id JOIN PS_PROD_ITEM prod ON ext.setid = prod.setid AND ext.product_id = prod.product_id WHERE link2.INST_EXT_SVC_ID <> ' '

# 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 INST_PROD_STATUS Character(4) VARCHAR2(4) NOT NULL Status of an installed product.
7 SETID Character(5) VARCHAR2(5) NOT NULL This field is used to store the value of SetID on various setup tables.
8 INST_PROD_ID Character(20) VARCHAR2(20) NOT NULL This field is used to represent the installed product ID (key field)
9 INST_EXT_SVC_ID Character(20) VARCHAR2(20) NOT NULL This field is used to represent the installed product ID (key field)
10 INST_PROD_ID_2 Character(20) VARCHAR2(20) NOT NULL Clone of Installed product ID used in work record
11 PARENT_INST_PRODID Character(20) VARCHAR2(20) NOT NULL This field is used to store the installed product ID of the parent installed product
12 DESCR Character(30) VARCHAR2(30) NOT NULL Description
13 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
14 BO_NAME_DISPLAY Character(80) VARCHAR2(80) NOT NULL Full name of the Business Object
15 REL_INST_PROD_ID Character(20) VARCHAR2(20) NOT NULL Field used for storing releated installed product information
16 ROW_ADDED_DTTM DateTime(26) TIMESTAMP This is a datetime field for record creation.
17 ROW_ADDED_OPRID Character(30) VARCHAR2(30) NOT NULL ID of user who added the row.
18 ROW_LASTMANT_DTTM DateTime(26) TIMESTAMP The date and time that the row was last updated.
19 ROW_LASTMANT_OPRID Character(30) VARCHAR2(30) NOT NULL ID of user who last modified the row.
20 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.
21 SYNCDTTM DateTime(26) TIMESTAMP Synchronization Last Update Date Time