SELECT i.SETID , i.BO_ID_CUST , ln.AGREEMENT_CODE , ln.AGR_RENEWAL_NUM , ln.AGREEMENT_LINE , i.INST_PROD_ID , i.SERIAL_ID , i.DESCR FROM %Table(RC_INST_PROD_VW) i , %Table(RF_AGREEMENT_LN) ln , %Table(SET_CNTRL_REC) sc WHERE i.INST_PROD_TYPE IN ('002', '003') AND ln.SETID = sc.SETID AND sc.SETCNTRLVALUE = i.SETID AND sc.RECNAME = 'RF_AGREEMENT_LN' AND ((i.PRODUCT_ID IN ( SELECT lnp.PRODUCT_ID FROM %Table(RF_AG_LINE_PROD) lnp , %Table(RF_AGREEMENT) ag WHERE i.BO_ID_CUST = ag.BO_ID_CUST AND lnp.SETID = ln.SETID AND lnp.AGREEMENT_CODE = ln.AGREEMENT_CODE AND lnp.AGR_RENEWAL_NUM = ln.AGR_RENEWAL_NUM AND lnp.AGREEMENT_LINE = ln.AGREEMENT_LINE AND lnp.SETID = ag.SETID AND lnp.AGREEMENT_CODE = ag.AGREEMENT_CODE AND lnp.AGR_RENEWAL_NUM = ag.AGR_RENEWAL_NUM AND lnp.PRODUCT_ID NOT IN ( SELECT i2.PRODUCT_ID FROM %Table(RF_INST_PROD) i2 , %Table(RF_UNDER_SVC) lni2 WHERE i.SETID = i2.SETID AND lni2.INST_PROD_ID = i2.INST_PROD_ID AND lni2.SETID = ln.SETID AND lni2.AGREEMENT_CODE = ln.AGREEMENT_CODE AND lni2.AGR_RENEWAL_NUM = ln.AGR_RENEWAL_NUM AND lni2.AGREEMENT_LINE = ln.AGREEMENT_LINE))) OR (i.INST_PROD_ID IN ( SELECT lni.INST_PROD_ID FROM %Table(RF_UNDER_SVC) lni WHERE lni.SETID = ln.SETID AND lni.AGREEMENT_CODE = ln.AGREEMENT_CODE AND lni.AGR_RENEWAL_NUM = ln.AGR_RENEWAL_NUM AND lni.AGREEMENT_LINE = ln.AGREEMENT_LINE))) AND i.BO_ID_CUST <> 0
|