====== pears.NetOwnerStaffCompanySalesStatus ======
<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"."NetOwnerStaffCompanySalesStatus"( in "pWebUserID" char(20),in @StaffID char(20) default null ) 
result( "CompanyID" char(20),"CompanyName" char(60),"CompanyKeyName" char(60),"EmploymentID" char(20),"PersonName" char(30),"PersonKeyName" char(60),"CompanySalesStatusID" char(1),"CompanySalesStatusName" char(40) ) 
begin
  declare "pStaffID" char(20);
  if(@StaffID is null) then
    set "pStaffID" = (select "StaffID" from "iqxnetuser" where "iqxnetuser"."iqxnetuserid" = "pWebUserID")
  else
    set "pStaffID" = @StaffID
  end if;
  select "c"."CompanyID",
    "c"."Name",
    "c"."KeyName",
    "e"."EmploymentID",
    "p"."Name",
    "p"."KeyName",
    "s"."CompanySalesStatusID",
    "s"."Name"
    from "Person" as "p" key join "Employment" as "e" key join "Company" as "c" key join "CompanySalesStatus" as "s"
    where "e"."OwnerStaffID" = "pStaffID"
    and("c"."DivisionID" is null or "c"."DivisionID" = any(select "DivisionID" from "DivisionsAllowed"))
    order by "c"."KeyName" asc,
    "p"."KeyName" asc
end /* DOC
2020-06-01 GJ IW-1188 Created/Modified proc from Justin so it works on the web
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerStaffCompanySalesStatus" IS 
{create PROCEDURE pears."NetOwnerStaffCompanySalesStatus"(in pWebUserID char(20), in @StaffID char(20) default null)
RESULT ( CompanyID char(20) ,CompanyName char(60) ,CompanyKeyName char(60) ,EmploymentID char(20) ,PersonName char(30) ,PersonKeyName char(60) ,CompanySalesStatusID char(1) ,CompanySalesStatusName char(40) )
BEGIN
    DECLARE pStaffID char(20);
    IF(@StaffID IS NULL) THEN
        SET pStaffID = (SELECT StaffID FROM iqxnetuser WHERE iqxnetuser.iqxnetuserid = pWebUserID);
    ELSE
        SET pStaffID = @StaffID;
    END IF;

    SELECT
        c.CompanyID,
        c.[Name],
        c.KeyName,
        e.EmploymentID,
        p.[Name],
        p.KeyName,
        s.CompanySalesStatusID,
        s.[Name]
    FROM
        Person p KEY JOIN Employment e KEY JOIN Company c KEY JOIN CompanySalesStatus s 
    WHERE
        e.OwnerStaffID = pStaffID
       AND (c.DivisionID IS NULL OR c.DivisionID IN (SELECT DivisionID FROM DivisionsAllowed))
    ORDER BY
        c.KeyName,
        p.KeyName
END

/* DOC
2020-06-01 GJ IW-1188 Created/Modified proc from Justin so it works on the web
*/
}
</code>