SELECT Id, Name FROM object p WHERE p.Status='A' AND ((COALESCE(p.Branch, '')='') OR (p.`Branch`='Operative' and not exists (select 1 from `object` p2 where p2.`NameJava`=p.`NameJava` and COALESCE(p2.Branch, '')='' and p2.`Status`='A'))) AND p.GroupId=67 AND p.ApplicationId in ('C') ORDER BY p.Name

Data Dictionary for UniCharge : ProPay

Objects:

EFT ProPay Profile
ProPay Auth Retail Transaction

ProPay Provisioning Transaction
ProPay Return Batch

ProPay Return Transaction
Retail Propay Profile