====== pears.NetOwnerContactList ======
<WRAP center round info>
Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
</WRAP>

===== Original SQL =====
<code sql>
create procedure "pears"."NetOwnerContactList"( in "pWebUserID" char(20),in "pCompanyID" char(20) ) 
result( "Value" char(20),"Descrip" char(100) ) 
begin
  if not exists(select * from "company" where "companyid" = "pCompanyID") then
    select '0','No Contacts Available';
    return
  end if;
  select "e"."employmentid",
    "p"."name"
    from "employment" as "e"
      key join "person" as "p"
    where "e"."temp" = 0
    and "e"."leavedate" is null
    and "p"."status" in( 'L','C' ) 
    and "e"."companyid" = "pCompanyID"
    order by "p"."keyname" asc
end /* DOC
2016-08-22 PC Provide a list of contacts for creating a contact event for a Company
2018-09-14 JW Performance improvement for pulse
2018-09-28 IW-686 Changed order by from surname to keyname

Inputs:
pCompanyID - company id to create list

Outputs:
Value - EmploymentID
Descrip - Person Name

Test:
Check with empty CompanyID
check with incorrect CompanyID
Check with Correct CompanyID
*/
/* TEST
call NetTestSetup('');
delete from contactevent where contacteventid like 'TEST%';
insert into contactclass (classcode,classdescrip) on existing update values ('~~','Test Class');
update person set status='L' where personid='TEST';
select * from "NetOwnerContactEventsSet"('test.owner','TEST1','n','~~','test','test','test',dateformat(now(*),'dd/mm/yyyy'),'TestDescription','TestNote',dateformat(dateadd(hh,1,now(*)),'dd/mm/yyyy'),'TestOutcome',1);
expect pResult=0:~Success;
select * from NetOwnerContactList('TEST.OWNER',null);
expect value=0;
select * from NetOwnerContactList('TEST.OWNER','rr');
expect value=0;
select top 1 * from NetOwnerContactList('TEST.OWNER','TEST') where value='test';
expect Value=TEST,Descrip=test;
expect EOF;
update person set status='P' where personid='TEST';
delete from contactevent where contacteventid like 'TEST%';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerContactList" IS 
{create PROCEDURE pears."NetOwnerContactList"(in pWebUserID char(20), in pCompanyID char(20))
RESULT(Value char(20),Descrip char(100) )
BEGIN
  if not exists (select * from company where companyid=pCompanyID) then
    select '0','No Contacts Available';
    return;
  end if;
  select
    e.employmentid,
    p.name
  from
    employment e
    key join person p
  where
    e.temp =0
    and e.leavedate is null
    and p.status in ('L','C')
    and e.companyid=pCompanyID
  order by
    p.keyname asc
END

/* DOC
2016-08-22 PC Provide a list of contacts for creating a contact event for a Company
2018-09-14 JW Performance improvement for pulse
2018-09-28 IW-686 Changed order by from surname to keyname

Inputs:
    pCompanyID - company id to create list

Outputs:
    Value - EmploymentID
    Descrip - Person Name

Test:
    Check with empty CompanyID
    check with incorrect CompanyID
    Check with Correct CompanyID
*/

/* TEST
call NetTestSetup('');
delete from contactevent where contacteventid like 'TEST%';
insert into contactclass (classcode,classdescrip) on existing update values ('~~','Test Class');
update person set status='L' where personid='TEST';
select * from "NetOwnerContactEventsSet"('test.owner','TEST1','n','~~','test','test','test',dateformat(now(*),'dd/mm/yyyy'),'TestDescription','TestNote',dateformat(dateadd(hh,1,now(*)),'dd/mm/yyyy'),'TestOutcome',1);
expect pResult=0:~Success;
select * from NetOwnerContactList('TEST.OWNER',null);
expect value=0;
select * from NetOwnerContactList('TEST.OWNER','rr');
expect value=0;
select top 1 * from NetOwnerContactList('TEST.OWNER','TEST') where value='test';
expect Value=TEST,Descrip=test;
expect EOF;
update person set status='P' where personid='TEST';
delete from contactevent where contacteventid like 'TEST%';
*/
}
</code>