pears.NetOwnerStaffCompanySalesStatus

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