====== pears.NetOwnerRequirementsTempdeskList ======
<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"."NetOwnerRequirementsTempdeskList"( in "pWebUserID" char(20),in "pType" char(1) ) 
result( "tempdeskid" char(20),"tempdeskname" char(100),"selected" char(10) ) 
begin
  declare "CurrentDesk" char(20);
  set "CurrentDesk" = (select top 1 "s"."tempdeskid" from "staff" as "s" key join "iqxnetuser" where "iqxnetuserid" = "pWebUserID" order by "s"."staffid" asc);
  select "d"."tempdeskid",
    "d"."name" as "tempdeskname",
    (if "tempdeskid" = "CurrentDesk" then 'Selected' else null endif) as "Selected"
    from "tempdesk" as "d"
    where(("d"."desktype" in( 'M','W' ) and "pType" = 'C')
    or("d"."desktype" = 'S' and "pType" = 'S'))
    and "d"."divisionid" = any(select "divisionid" from "dashboardstaffdivisions"())
end /* DOC
2018-11-27 PC IW-758 provide list of tempdesks associated with a consultant

*/
/* TEST
update tempdesk set desktype='S' where tempdeskid  in ('test','test2');
update staff set tempdeskid='test' where staffid='test';
insert into deptmaintenance (departmentid,staffid) on existing update defaults off values ('~~','TEST');
select tempdeskid,tempdeskname,isnull(selected,'!') as A from NetOwnerRequirementsTempdeskList('test.owner','S' ) where tempdeskid in ('test','test2') order by tempdeskid;
expect tempdeskid=TEST,tempdeskname=test,A=Selected;
expect tempdeskid=TEST2,tempdeskname=test2,A=!;
expect EOF;
update tempdesk set desktype='M' where tempdeskid in ('test','test2');
update staff set tempdeskid='test2' where staffid='test';
select tempdeskid,tempdeskname,isnull(selected,'!') as A from NetOwnerRequirementsTempdeskList('test.owner','C' ) where tempdeskid in ('test','test2') order by tempdeskid;
expect tempdeskid=TEST,tempdeskname=test,A=!;
expect tempdeskid=TEST2,tempdeskname=test2,A=Selected;
update tempdesk set desktype='S' where tempdeskid  in ('test','test2');
update staff set tempdeskid='test2' where staffid='test';
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementsTempdeskList" IS 
{create PROCEDURE pears."NetOwnerRequirementsTempdeskList"(in pWebUserID char(20),in pType char(1) )
RESULT(tempdeskid char(20),tempdeskname char(100),selected char(10))
BEGIN
    declare CurrentDesk char(20);
    set CurrentDesk=(select top 1 s.tempdeskid from staff s key join iqxnetuser where iqxnetuserid=pWebUserID order by s.staffid);

	select
        d.tempdeskid,
        d.name as tempdeskname,
        (if tempdeskid=CurrentDesk then 'Selected' else null endif) as Selected
    from 
        tempdesk d 
    where 
        ((d.desktype in ('M','W') and pType='C')
        or (d.desktype='S' and pType='S'))
        and d.divisionid in (select divisionid from dashboardstaffdivisions());
END

/* DOC
2018-11-27 PC IW-758 provide list of tempdesks associated with a consultant

*/

/* TEST
update tempdesk set desktype='S' where tempdeskid  in ('test','test2');
update staff set tempdeskid='test' where staffid='test';
insert into deptmaintenance (departmentid,staffid) on existing update defaults off values ('~~','TEST');
select tempdeskid,tempdeskname,isnull(selected,'!') as A from NetOwnerRequirementsTempdeskList('test.owner','S' ) where tempdeskid in ('test','test2') order by tempdeskid;
expect tempdeskid=TEST,tempdeskname=test,A=Selected;
expect tempdeskid=TEST2,tempdeskname=test2,A=!;
expect EOF;
update tempdesk set desktype='M' where tempdeskid in ('test','test2');
update staff set tempdeskid='test2' where staffid='test';
select tempdeskid,tempdeskname,isnull(selected,'!') as A from NetOwnerRequirementsTempdeskList('test.owner','C' ) where tempdeskid in ('test','test2') order by tempdeskid;
expect tempdeskid=TEST,tempdeskname=test,A=!;
expect tempdeskid=TEST2,tempdeskname=test2,A=Selected;
update tempdesk set desktype='S' where tempdeskid  in ('test','test2');
update staff set tempdeskid='test2' where staffid='test';
*/
}
</code>