accounts_sold.sql

--Search all accounts that have been sold and are not Canceled
Select UNIQUE PVRCE.VZ_ACCOUNT_ID as "Account ID", PB.CUST_ID as "Customer Id", PVBA.COMPANYID as "Company Id", 
PVBA.VZ_DE_CH_ID as "Item Id", PRIP.VZ_PRODUCT as "Product Id", PVRCE.VZ_ACCOUNT_ID || '-' || PB.CUST_ID as "Account-Customer", 
PRIP.PRODUCTS_ID, PRIP.VZ_INFO_ID, PRIPS.STATUS
from PS_RF_INST_PROD PRIP, PS_RF_INST_PROD_ST PRIPS, PS_BC PB, PS_VZ_RD_CMP_EXTN PVRCE, PS_VZ_BILL_ACCT PVBA, ps_ro_line PRL
where PRODUCTS_ID = PRODUCTS_ID
and PRIP.BO_ID_CUST = PB.BO_ID and PB.CUST_ID = PVRCE.COMPANYID
and PRIP.CAPTURE_ID = PRL.CAPTURE_ID and PRIP.PRODUCT_ID = PRL.PRODUCT_ID and PRL.VZ_ACCOUNTS_ID = PVBA.VZ_ACCOUNTS_ID
and PRIP.PRODUCT_ID in ('Security','Upgrade')
and PRIPS.STATUS <> 'CAN'
Order by PRIP.PRODUCT_ID