pears.NetAgencyCompanyStatusSelect

CREATE PROCEDURE "pears"."NetAgencyCompanyStatusSelect"( IN "pWebUserID" CHAR(20) ) 
RESULT( "Value" CHAR(5),"Name" CHAR(25) ) 
BEGIN
  IF(SELECT "count"() FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID") = 0 THEN
    SELECT '',''
  ELSE
    SELECT "companystatusid","name" FROM "companystatus" ORDER BY "sortorder" ASC
  END IF
END /* DOC
2016-10-04 PC
Ref:IW4 
provide filter dropdown for Company status select box
2018-04-20 PC revise tests
 
tests:
valid pWebUserID
add test status with sort order 0
*/
/* TEST
call NetTestSetup('');
delete from Companystatus where Companystatusid='~';
insert into Companystatus (companystatusid,name,help,sortorder) values ('~','TestState','testhelp',0);
select value,name from NetAgencyCompanyStatusSelect('test.owner');
expect ~,TestState;
delete from Companystatus where Companystatusid='~';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCompanyStatusSelect" IS 
{CREATE PROCEDURE pears."NetAgencyCompanyStatusSelect"(IN pWebUserID CHAR(20))
RESULT(VALUE CHAR(5),Name CHAR(25))
BEGIN
    IF (SELECT COUNT(*) FROM iqxnetuser WHERE iqxnetuserid=pWebUserID) = 0 THEN
        SELECT '','';
    ELSE
        SELECT companystatusid,name FROM companystatus ORDER BY sortorder
    END IF;
END
 
/* DOC
2016-10-04 PC
Ref:IW4 
provide filter dropdown for Company status select box
2018-04-20 PC revise tests
 
tests:
    valid pWebUserID
    add test status with sort order 0
*/
 
/* TEST
call NetTestSetup('');
delete from Companystatus where Companystatusid='~';
insert into Companystatus (companystatusid,name,help,sortorder) values ('~','TestState','testhelp',0);
select value,name from NetAgencyCompanyStatusSelect('test.owner');
expect ~,TestState;
delete from Companystatus where Companystatusid='~';
*/
}