pears.NetAgencyContact [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetAgencyContact ======
<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"."NetAgencyContact"( in "pWebUserID" char(20) ) 
result( "employmentid" char(20),"directphone" char(250),"mobile" char(250),"email" char(250),"directfax" char(250),"cascademethod" char(1),"cascadeaddress" char(250) ) 
//IQXWeb
begin
  select top 1
    "employment"."employmentid",
    "getphone"('CP','Direct Telephone',"employment"."employmentid"),
    "getphone"('CP','Mobile',"employment"."employmentid"),
    "netgetemail"('CP',"employment"."employmentid"),
    "getphone"('CP','Direct Fax',"employment"."employmentid"),
    "cascadedeliveryaddress"."deliverymethod",
    "cascadedeliveryaddress"."address"
    from "employment" key join "iqxnetuserlink"
      ,"employment" left outer join "cascadedeliveryaddress" on "employment"."companyid" = "cascadedeliveryaddress"."companyid"
    where "iqxnetuserlink"."iqxnetuserid" = "pWebUserID"
    order by "isnull"("employment"."leavedate","today"()) asc
end /* DOC
2016-10-10 PC add Doc and test sections
2018-04-20 PC revise tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries, and test routine

*/
/* TEST
call NetTestSetup('');
delete from cascadedeliveryaddress where companyid='test';
update iqxnetuserlink set employmentid='TEST' where iqxnetuserlinkid='test.candidate';
select isnull(cascadeaddress,'!') as A,* from NetAgencyContact('TEST.CANDIDATE');
expect employmentid=TEST,email=test70@test.com,A=!;
insert into cascadedeliveryaddress values ('TEST','E','test@testaddress.com');
select isnull(cascadeaddress,'!') as B,* from NetAgencyContact('TEST.CANDIDATE');
expect employmentid=TEST,email=test70@test.com,B=test@testaddress.com;
delete from cascadedeliveryaddress where companyid='test';
update iqxnetuserlink set employmentid=null where iqxnetuserlinkid='test.candidate';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyContact" IS 
{create PROCEDURE pears."NetAgencyContact"(in pWebUserID char(20))
result(employmentid char(20),directphone char(250),mobile char(250),email char(250),directfax char(250),cascademethod char(1),cascadeaddress char(250))
//IQXWeb
begin
    select top 1 
        employment.employmentid,
        getphone('CP','Direct Telephone',employment.employmentid),
        getphone('CP','Mobile',employment.employmentid),
        netgetemail('CP',employment.employmentid),
        getphone('CP','Direct Fax',employment.employmentid),
        cascadedeliveryaddress.deliverymethod,
        cascadedeliveryaddress.address
    from 
        employment key join iqxnetuserlink,
        employment left outer join cascadedeliveryaddress on employment.companyid = cascadedeliveryaddress.companyid
    where 
        iqxnetuserlink.iqxnetuserid = pWebUserID
    order BY 
        isnull(employment.leavedate,today(*))
end

/* DOC
2016-10-10 PC add Doc and test sections
2018-04-20 PC revise tests
2019-03-04 PC IW-873 add top 1 and order by on subqueries, and test routine

*/

/* TEST
call NetTestSetup('');
delete from cascadedeliveryaddress where companyid='test';
update iqxnetuserlink set employmentid='TEST' where iqxnetuserlinkid='test.candidate';
select isnull(cascadeaddress,'!') as A,* from NetAgencyContact('TEST.CANDIDATE');
expect employmentid=TEST,email=test70@test.com,A=!;
insert into cascadedeliveryaddress values ('TEST','E','test@testaddress.com');
select isnull(cascadeaddress,'!') as B,* from NetAgencyContact('TEST.CANDIDATE');
expect employmentid=TEST,email=test70@test.com,B=test@testaddress.com;
delete from cascadedeliveryaddress where companyid='test';
update iqxnetuserlink set employmentid=null where iqxnetuserlinkid='test.candidate';
*/
}
</code>