pears.NetOwnerRequirementConsultantList

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;
*/
}