SELECT COUNT (distinct(sq.policy_id))
FROM
(SELECT *
FROM lic_policiy l, 
lic_policy_item lpi, 
lic_policy_group lpg1, 
lic_policy_group lpg2,
lic_policy_pro lpp 
WHERE l.prod_cde LIKE 'PAMH0D%'
AND l.policy_allocate_nbr LIKE nvl( null , l.policy_allocate_nbr)
AND nvl(l.policy_nbr, '0' ) LIKE nvl( null , nvl(l.policy_nbr, '0' ))
AND l.status LIKE nvl( null , l.status)
AND l.policy_start_dt >= nvl( null , l.policy_start_dt)
AND l.policy_start_dt <= nvl( null , l.policy_start_dt)
AND lpi.lpi_policy_id = l.policy_id
AND lpi.lpi_policy_item_id = l.lp_map_policy_item_id
AND lpi.lpi_policy_end_dt IS NULL
AND lpg1.pg_policy_id = l.policy_id
AND lpg1.lpg_p_grp_cde = nvl( null , lpg1.lpg_p_grp_cde)
--AND lpg1.lpg_stake_holder_id LIKE nvl(:B2, 'SH' )
AND lpg1.lpg_stake_holder_id = 'SH'
AND lpg1.lpg_grp_cd LIKE nvl( null , '%' )
AND lpg1.lpg_eff_end_dt IS NULL
AND lpg2.pg_policy_id = l.policy_id
AND lpg2.lpg_p_grp_cde = nvl( null , lpg1.lpg_p_grp_cde)
AND lpg2.lpg_stake_holder_id LIKE nvl( null , 'SH' )
AND lpg2.lpg_grp_cd LIKE nvl( null , '%' )
AND lpg2.lpg_eff_end_dt IS NULL
AND lpp.lpp_policy_id = l.policy_id
AND lpp.LPP_P_VAL = 'Rule 214'
AND nvl(lpp.lpp_val_cde, lpp.lpp_val_flag) = 'Y'
AND lpp.lpp_eff_end_dt IS NULL ) sq
,lic_policy_group lpg3
WHERE lpg3.pg_policy_id = sq.policy_id
AND lpg3.lpg_stake_holder_id = 'XXXXX'
AND lpg3.lpg_grp_cd = 'CK001'
AND lpg3.lpg_eff_end_dt IS NULL