====== pears.NetProvTimesheetSetTheirRef ======
<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"."NetProvTimesheetSetTheirRef"( in "pWebUserID" char(20),in "ptempprovtimesheetid" char(20),in "pTheirRef" char(100) default null ) 
result( "pResult" char(250) ) 
// IQXWeb
begin
  if not "ptempprovtimesheetid"
     = any(select "t"."tempprovtimesheetid" from "tempprovtimesheet" as "t" key join "vacancy" key join "employment" as "vacemp" key join "company" key join "employment" key join "iqxnetuserlink"
      where "iqxnetuserlink"."iqxnetuserid" = "pwebuserid") then
    select '99:~Permission denied';
    return
  end if;
  update "tempprovtimesheet" set "theirref" = "ucase"("ptheirref") where "tempprovtimesheetid" = "ptempprovtimesheetid";
  select '0:~Success'
end /* DOC
2016-12-06 PC doc and test
2018-07-26 PC include improved error handler

Tests:
invalid pWebUserID
valid pWebUserID, invalid ptempprovtimesheet
valid pWebUserID, ptempprovtimesheet, invalid pTheirRef
all valid

*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
select * from NetProvTimesheetSetTheirRef(null,null,null);
expect 99*;
select * from NetProvTimesheetSetTheirRef('test.candidate',null,null);
expect 0*;
select * from NetProvTimesheetSetTheirRef('Test.candidate','TEST',null);
expect 0*;
select isnull(theirref,'z') as A from tempprovtimesheet where tempprovtimesheetid='TEST';
expect A=z;
select * from NetProvTimesheetSetTheirRef('Test.candidate','TEST','testRef');
expect 0*;
select isnull(theirref,'z') as A from tempprovtimesheet where tempprovtimesheetid='TEST';
expect A=TESTREF;


*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetProvTimesheetSetTheirRef" IS 
{create PROCEDURE pears."NetProvTimesheetSetTheirRef"(in pWebUserID char(20),in ptempprovtimesheetid char(20),in pTheirRef char(100) default null)
result(pResult char(250))
// IQXWeb
begin
  if not ptempprovtimesheetid
     = any(select t.tempprovtimesheetid from tempprovtimesheet as t key join vacancy key join employment as vacemp key join company key join employment key join iqxnetuserlink
      where iqxnetuserlink.iqxnetuserid = pwebuserid) then
    select '99:~Permission denied';
    return
  end if;
  update tempprovtimesheet set theirref = ucase(ptheirref) where tempprovtimesheetid = ptempprovtimesheetid;
  select '0:~Success'
end

/* DOC
2016-12-06 PC doc and test
2018-07-26 PC include improved error handler

Tests:
    invalid pWebUserID
    valid pWebUserID, invalid ptempprovtimesheet
    valid pWebUserID, ptempprovtimesheet, invalid pTheirRef
    all valid

*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.candidate';
select * from NetProvTimesheetSetTheirRef(null,null,null);
expect 99*;
select * from NetProvTimesheetSetTheirRef('test.candidate',null,null);
expect 0*;
select * from NetProvTimesheetSetTheirRef('Test.candidate','TEST',null);
expect 0*;
select isnull(theirref,'z') as A from tempprovtimesheet where tempprovtimesheetid='TEST';
expect A=z;
select * from NetProvTimesheetSetTheirRef('Test.candidate','TEST','testRef');
expect 0*;
select isnull(theirref,'z') as A from tempprovtimesheet where tempprovtimesheetid='TEST';
expect A=TESTREF;


*/
}
</code>