|
SELECT a.company , b.descr FROM ps_gpbr_c_parm_src a , PS_COMPANY_TBL b , ps_gpbr_comp_p_eff c WHERE a.company = b.company AND c.company = b.company AND c.gpbr_comp_status = 'E' AND a.gpbr_es_adhere = ( SELECT MAX(a2.gpbr_es_adhere) FROM ps_gpbr_c_parm_src a2 WHERE a.company = a2.company) AND b.effdt = ( SELECT MAX(b2.effdt) FROM PS_COMPANY_TBL b2 WHERE b2.company = b.company AND b2.effdt <= a.gpbr_es_adhere) AND c.effdt = ( SELECT MAX(c2.effdt) FROM ps_gpbr_comp_p_eff c2 WHERE c2.company = c.company AND c2.effdt <= %CurrentDateIn) AND ( EXISTS ( SELECT 'X' FROM ps_gpbr_comp_param cp WHERE cp.company = a.company AND cp.effdt = ( SELECT MAX(cp2.effdt) FROM ps_gpbr_comp_param cp2 WHERE cp2.company = cp.company AND cp2.effdt <= a.gpbr_es_adhere) AND cp.gpbr_event_cd = 'S-2190' AND cp.gpbr_evnt_status = 'A') OR ( EXISTS ( SELECT 'X' FROM ps_gpbr_evnt_actv act WHERE act.effdt = ( SELECT MAX(act2.effdt) FROM ps_gpbr_evnt_actv act2 WHERE act2.effdt <= a.gpbr_es_adhere) AND act.gpbr_event_cd = 'S-2190' AND act.gpbr_evnt_status = 'A' ) AND NOT EXISTS ( SELECT 'X' FROM ps_gpbr_comp_param cp WHERE cp.company = a.company AND cp.effdt = ( SELECT MAX(cp2.effdt) FROM ps_gpbr_comp_param cp2 WHERE cp2.company = cp.company AND cp2.effdt <= a.gpbr_es_adhere) AND cp.gpbr_event_cd = 'S-2190' AND cp.gpbr_evnt_status = 'I') ) )
|
COMPANY