pears.NetOwnerRequirementConsultantList [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.NetOwnerRequirementConsultantList ======
<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"."NetOwnerRequirementConsultantList"( in "pWebUserID" char(20) ) 
result( "ConsultantID" char(20),"ConsultantName" char(100),"ThisIsMe" integer,"SortCol" char(50) ) 
// IQXWeb
begin
  select top 10000
    "staff"."staffid",
    "string"("staff"."userid",'  (',"staff"."name",')') as "ConsultantName",
    (if "staff"."staffid" = "UserStaffID" then 1 else 0 endif) as "ThisIsMe",
    "staff"."userid" as "SortCol"
    from "staff"
    where "isnull"("staff"."defunct",0) = 0
    and "staff"."divisionid" = any(select "divisionid" from "dashboardstaffdivisions"())
    order by "SortCol" asc
end /* DOC
2017-12-22 PC OP-13 provide a list of consultants who are in the divisions which pWebUserID consultant has access to. 
2018-02-08 PC modify to include division name and ignore not in use ########### modify for agency ##########
2018-02-15 PC OP-29 modify to include ThisIsMe to allow consultant to be pre-selected
2018-05-02 GJ IW-476 Sort order changed to keyname and full name replaces the division name in brackets
2018-05-07 PC modify method of selecting division list
2018-12-20 MHS fixed bug (non unique field), cleaned up, added minimal test
2019-06-21 GJ Increased column length due to issues with clients having it longer than 20
2020-02-17 AW ordered by keyname now works added"top 10000"

Inputs
pWebUserID

Outputs
ConsultantID
ConsultantName
*/
/* TEST
call NetTestSetup('');
select * from NetOwnerRequirementConsultantList('TEST.OWNER');
expect ROW;
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementConsultantList" IS 
{create PROCEDURE pears."NetOwnerRequirementConsultantList"(in pWebUserID char(20) )
RESULT(ConsultantID char(20), ConsultantName char(100),ThisIsMe integer, SortCol char(50))

// IQXWeb
BEGIN
select top 10000
  staff.staffid,
  string(staff.userid,'  (',staff.name,')') as ConsultantName,
  (if staff.staffid=UserStaffID then 1 else 0 endif) as ThisIsMe,
  staff.userid as SortCol 
from 
  staff 
where
  isnull(staff.defunct,0)=0 
  and staff.divisionid in (select divisionid from dashboardstaffdivisions())
order by 
  SortCol asc
END

/* DOC
2017-12-22 PC OP-13 provide a list of consultants who are in the divisions which pWebUserID consultant has access to. 
2018-02-08 PC modify to include division name and ignore not in use ########### modify for agency ##########
2018-02-15 PC OP-29 modify to include ThisIsMe to allow consultant to be pre-selected
2018-05-02 GJ IW-476 Sort order changed to keyname and full name replaces the division name in brackets
2018-05-07 PC modify method of selecting division list
2018-12-20 MHS fixed bug (non unique field), cleaned up, added minimal test
2019-06-21 GJ Increased column length due to issues with clients having it longer than 20
2020-02-17 AW ordered by keyname now works added"top 10000"

Inputs
    pWebUserID

Outputs
    ConsultantID
    ConsultantName
*/
/* TEST
call NetTestSetup('');
select * from NetOwnerRequirementConsultantList('TEST.OWNER');
expect ROW;
*/
}
</code>