select psf.pol_num, psf.bank_acct_num, cba_dda.bank_acct_nm
from tbank_pos_slip_files psf, tclient_policy_links cpl_dda, tclient_bank_accounts cba_dda
where cpl_dda.cli_num = cba_dda.cli_num
and cpl_dda.bank_acct_typ = cba_dda.bank_acct_typ
and cpl_dda.link_typ = 'O'
and psf.bank_acct_num = cba_dda.bank_acct_num
and psf.trade_role = 'C'
and psf.mtch_ind = 'Y'
and psf.mtch_dt between to_date('2013/11/01', 'yyyy/mm/dd') and to_date('2013/11/30', 'yyyy/mm/dd')
--and (p_terr_cd = '*' or instr(p_terr_cd, psf.pos_terr_cd ||'/') > 0)
and not exists (select 'Y'
from tclient_policy_links cpl, tclient_details cli
where cpl.cli_num = cli.cli_num
and cpl.link_typ in ('I', 'O')
and cpl.pol_num = psf.pol_num
and cli.cli_nm = cba_dda.bank_acct_nm
union all
select 'Y'
from tbeneficiary_details bft
where bft.pol_num = psf.pol_num
and bft.bnfy_nm = cba_dda.bank_acct_nm)
union
select psf.pol_num, psf.bank_acct_num, cba_dca.bank_acct_nm
from tbank_pos_slip_files psf, vclient_policy_links cpl_dca, tclient_bank_accounts cba_dca
where cpl_dca.cli_num = cba_dca.cli_num
and cpl_dca.payo_bank_acct_typ = cba_dca.bank_acct_typ
and psf.bank_acct_num = cba_dca.bank_acct_num
and psf.trade_role = 'C'
and psf.mtch_ind = 'Y'
and psf.mtch_dt between to_date('2013/11/01', 'yyyy/mm/dd') and to_date('2013/11/30', 'yyyy/mm/dd')
--and (p_terr_cd = '*' or instr(p_terr_cd, psf.pos_terr_cd ||'/') > 0)
and not exists (select 'Y'
from tclient_policy_links cpl, tclient_details cli
where cpl.cli_num = cli.cli_num
and cpl.link_typ in ('I', 'O')
and cpl.pol_num = psf.pol_num
and cli.cli_nm = cba_dca.bank_acct_nm
union all
select 'Y'
from tbeneficiary_details bft
where bft.pol_num = psf.pol_num
and bft.bnfy_nm = cba_dca.bank_acct_nm)