create or replace function pandcreport (first_name varchar, surname varchar, 
provider_name varchar, plantype varchar, plangroup varchar)
returns pandctype 
as' 
select tblemployee.employee_first_name,tblemployee.employee_surname,tblnewbusiness.newbusiness_date_issued,
tblclient.client_first_name,tblclient.client_middle_names,tblclient.client_surname,tblplantypes.plantype_group,
tblplangroups.plan_group,tblproviders.provider_company, tblnewbusiness.newbusiness_policy_number, 
tblnewbusiness_sum_assured,tblnewbusiness.newbusiness_benefit, tblnewbusiness.newbusiness_premium, 
tblnewbusiness.brokerage,tblnewbusiness.newbusiness_comments 
from 
(tblproviders right join tblnewbusiness on (tblproviders.provider_ref=tblnewbusiness.provider_ref)) and 
(tblemployee inner join tbladvisersplit on (tblemployee.employee_ref=tbladvisersplit.adviser_ref)) and
(tblnewbusiness inner join tblturnover on (tblnewbusiness.newbusiness_ref=tblturnover.newbusiness_ref) )and
(tblemployee inner join tbladvisersplit on (tblemployee.employee_ref=tbladvisersplit.adviser_ref))
where 
((tblnewbusiness.newbusiness_renewal=0) and (tblclientlist.clientlist_primary=-1) and 
(tblclient_list.clientlist_primary=-1) and (tblcancelled.reasoncancelled_ref is null) and (tblturnover.newbusiness_ref is null)
 and ((tblnewbusiness.newbusiness_delete_=0) OR (tblnewbusiness.renewal=0)) and (tblclientlist.clientlist_primary=-1) 
and (tblnewbusiness.newbusiness.newbusiness_date_issued is null) and (tblnewbusiness.newbusiness_delete_=0)) 
order by tblemployee.employee_first_name,tblemployee.employee_surname,tblclient.client_first_name, 
tblclient.client_middle_names,tblclient.client_surname;
'language sql;