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