pears.NetOwnerTreeSource

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.
Original SQL

CREATE PROCEDURE "pears"."NetOwnerTreeSource"( IN "pWebUserID" CHAR(20),IN "pParentID" CHAR(23) ) 
RESULT( "ObjectID" CHAR(23),"ObjectName" CHAR(100),"IsLeaf" tinyint ) 
BEGIN
  DECLARE "ParentType" CHAR(2);
  DECLARE "ParentID" CHAR(20);
  DECLARE "DivID" CHAR(20);
  DECLARE "persid" CHAR(20);
  SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  SET "ParentType" = "substring"("pParentID",1,2);
  SET "ParentID" = "substring"("pParentID",4,20);
  IF "DivID" IS NULL THEN
    RETURN
  END IF;
  IF("ParentType" = '0') THEN
    SELECT 'AC_New' AS "ObjectID",'**New Account' AS "ObjectName",1 AS "IsLeaf" FROM "dummy" UNION
    SELECT 'AC_'+"clientcode" AS "ObjectID","name" AS "ObjectName",0 AS "IsLeaf"
      FROM "company"
      WHERE "isnull"("status",'') <> '.' AND "divisionid" = "DivID" AND "trim"("isnull"("ClientCode",'')) <> '' AND "InvoiceAddress" = 1
  ELSE IF("ParentType" = 'AC') THEN
      SELECT 'CO_'+"companyid" AS "ObjectID","string"("name",', ',"addr1") AS "ObjectName",
        IF EXISTS(SELECT * FROM "employment"
          WHERE "employment"."companyid" = "substring"("ObjectID",4)) THEN
          0
        ELSE 1
        endif AS "IsLeaf"
        FROM "company"
        WHERE "isnull"("status",'') <> '.' AND "divisionid" = "DivID" AND "ClientCode" = "ParentID" //AND isnull(InvoiceAddress,0)=0 
        ORDER BY "keyname" ASC
    ELSE IF("ParentType" = 'CO') THEN
        SELECT 'CX_'+"ParentID" AS "ObjectID",'**All Vacancies' AS "ObjectName",0 AS "IsLeaf" FROM "person" UNION
        SELECT 'EM_'+"employment"."employmentid" AS "ObjectID","person"."name" AS "ObjectName",
          IF EXISTS(SELECT * FROM "vacancy" KEY JOIN "employment" KEY JOIN "company"
            WHERE "employment"."employmentid" = "substring"("ObjectID",4) AND "isnull"("employment"."temp",0) = 0 AND("employment"."leavedate" IS NULL OR "employment"."leavedate" > CURRENT DATE)
            AND("vacancy"."finishdate" IS NULL OR "vacancy"."finishdate" > CURRENT DATE)) THEN
            0
          ELSE 1
          endif AS "IsLeaf"
          FROM "employment" KEY JOIN("person","company")
          WHERE "company"."divisionid" = "DivID" AND "employment"."companyid" = "ParentID" AND "isnull"("employment"."temp",0) = 0 AND("employment"."leavedate" IS NULL OR "employment"."leavedate" > CURRENT DATE)
      ELSE IF("ParentType" = 'CX') THEN -- used by Timesheet page to gather all vacancies for all employees
          SELECT 'VA_'+"vacancy"."vacancyid" AS "ObjectID","string"("trim"("vacancy"."position"),' (',"trim"("person"."surname"),')') AS "ObjectName",0 AS "IsLeaf"
            FROM "vacancy" KEY JOIN "employment" KEY JOIN("company","person")
            WHERE "company"."divisionid" = "DivID" AND "vacancy"."employmentid" = any(SELECT "employment"."employmentid"
              FROM "employment" KEY JOIN("person","company")
              WHERE "company"."divisionid" = "DivID" AND "employment"."companyid" = "ParentID" AND "isnull"("employment"."temp",0) = 0 AND("employment"."leavedate" IS NULL OR "employment"."leavedate" > CURRENT DATE))
            AND("vacancy"."finishdate" IS NULL OR "vacancy"."finishdate" > CURRENT DATE)
            ORDER BY "vacancy"."position" ASC
        ELSE IF("ParentType" = 'EM') THEN
            SELECT 'VA_'+"vacancy"."vacancyid" AS "ObjectID","vacancy"."position" AS "ObjectName",
              IF EXISTS(SELECT * FROM "TempJobRate" AS "r" KEY JOIN "TempPayBand" AS "b"
                WHERE "r"."vacancyid" = "substring"("ObjectID",4) AND("r"."enddate" IS NULL OR "r"."enddate" > CURRENT DATE)) THEN
                0
              ELSE 1
              endif AS "IsLeaf"
              FROM "vacancy" KEY JOIN "employment" KEY JOIN "company"
              WHERE "company"."divisionid" = "DivID" AND "vacancy"."employmentid" = "ParentID" AND("vacancy"."finishdate" IS NULL OR "vacancy"."finishdate" > CURRENT DATE)
              ORDER BY "vacancy"."position" ASC
          ELSE IF("ParentType" = 'VA') THEN
              SELECT 'XX_'+"r"."TempJobRateID" AS "ObjectID","string"("b"."Description",' ',"trim"("str"("r"."payrate",12,2)),'/',"trim"("str"("r"."chargerate",12,2))) AS "ObjectName",1 AS "IsLeaf"
                FROM "TempJobRate" AS "r" KEY JOIN "TempPayBand" AS "b"
                WHERE "r"."vacancyid" = "ParentID" AND("r"."enddate" IS NULL OR "r"."enddate" > CURRENT DATE)
                ORDER BY "b"."sortorder" ASC
            END IF
          END IF
        END IF
      END IF
    END IF
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerTreeSource" IS 
{CREATE PROCEDURE pears."NetOwnerTreeSource"( IN "pWebUserID" CHAR(20),IN "pParentID" CHAR(23) ) 
RESULT( "ObjectID" CHAR(23),"ObjectName" CHAR(100),"IsLeaf" tinyint ) 
BEGIN
  DECLARE "ParentType" CHAR(2);
  DECLARE "ParentID" CHAR(20);
  DECLARE "DivID" CHAR(20);
  DECLARE "persid" CHAR(20);
  SET "DivID" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  SET "ParentType" = "substring"("pParentID",1,2);
  SET "ParentID" = "substring"("pParentID",4,20);
  IF "DivID" IS NULL THEN
    RETURN
  END IF;
  IF("ParentType" = '0') THEN
    SELECT 'AC_New' AS "ObjectID",'**New Account' AS "ObjectName",1 AS "IsLeaf" FROM "dummy" UNION
    SELECT 'AC_'+"clientcode" AS "ObjectID","name" AS "ObjectName",0 AS "IsLeaf"
      FROM "company"
      WHERE "isnull"("status",'') <> '.' AND "divisionid" = "DivID" AND "trim"("isnull"("ClientCode",'')) <> '' AND "InvoiceAddress" = 1
  ELSE IF("ParentType" = 'AC') THEN
      SELECT 'CO_'+"companyid" AS "ObjectID","string"("name",', ',"addr1") AS "ObjectName",
        IF EXISTS(SELECT * FROM "employment"
          WHERE "employment"."companyid" = "substring"("ObjectID",4)) THEN
          0
        ELSE 1
        endif AS "IsLeaf"
        FROM "company"
        WHERE "isnull"("status",'') <> '.' AND "divisionid" = "DivID" AND "ClientCode" = "ParentID" //AND isnull(InvoiceAddress,0)=0 
        ORDER BY "keyname" ASC
    ELSE IF("ParentType" = 'CO') THEN
        SELECT 'CX_'+"ParentID" AS "ObjectID",'**All Vacancies' AS "ObjectName",0 AS "IsLeaf" FROM "person" UNION
        SELECT 'EM_'+"employment"."employmentid" AS "ObjectID","person"."name" AS "ObjectName",
          IF EXISTS(SELECT * FROM "vacancy" KEY JOIN "employment" KEY JOIN "company"
            WHERE "employment"."employmentid" = "substring"("ObjectID",4) AND "isnull"("employment"."temp",0) = 0 AND("employment"."leavedate" IS NULL OR "employment"."leavedate" > CURRENT DATE)
            AND("vacancy"."finishdate" IS NULL OR "vacancy"."finishdate" > CURRENT DATE)) THEN
            0
          ELSE 1
          endif AS "IsLeaf"
          FROM "employment" KEY JOIN("person","company")
          WHERE "company"."divisionid" = "DivID" AND "employment"."companyid" = "ParentID" AND "isnull"("employment"."temp",0) = 0 AND("employment"."leavedate" IS NULL OR "employment"."leavedate" > CURRENT DATE)
      ELSE IF("ParentType" = 'CX') THEN -- used by Timesheet page to gather all vacancies for all employees
          SELECT 'VA_'+"vacancy"."vacancyid" AS "ObjectID","string"("trim"("vacancy"."position"),' (',"trim"("person"."surname"),')') AS "ObjectName",0 AS "IsLeaf"
            FROM "vacancy" KEY JOIN "employment" KEY JOIN("company","person")
            WHERE "company"."divisionid" = "DivID" AND "vacancy"."employmentid" = any(SELECT "employment"."employmentid"
              FROM "employment" KEY JOIN("person","company")
              WHERE "company"."divisionid" = "DivID" AND "employment"."companyid" = "ParentID" AND "isnull"("employment"."temp",0) = 0 AND("employment"."leavedate" IS NULL OR "employment"."leavedate" > CURRENT DATE))
            AND("vacancy"."finishdate" IS NULL OR "vacancy"."finishdate" > CURRENT DATE)
            ORDER BY "vacancy"."position" ASC
        ELSE IF("ParentType" = 'EM') THEN
            SELECT 'VA_'+"vacancy"."vacancyid" AS "ObjectID","vacancy"."position" AS "ObjectName",
              IF EXISTS(SELECT * FROM "TempJobRate" AS "r" KEY JOIN "TempPayBand" AS "b"
                WHERE "r"."vacancyid" = "substring"("ObjectID",4) AND("r"."enddate" IS NULL OR "r"."enddate" > CURRENT DATE)) THEN
                0
              ELSE 1
              endif AS "IsLeaf"
              FROM "vacancy" KEY JOIN "employment" KEY JOIN "company"
              WHERE "company"."divisionid" = "DivID" AND "vacancy"."employmentid" = "ParentID" AND("vacancy"."finishdate" IS NULL OR "vacancy"."finishdate" > CURRENT DATE)
              ORDER BY "vacancy"."position" ASC
          ELSE IF("ParentType" = 'VA') THEN
              SELECT 'XX_'+"r"."TempJobRateID" AS "ObjectID","string"("b"."Description",' ',"trim"("str"("r"."payrate",12,2)),'/',"trim"("str"("r"."chargerate",12,2))) AS "ObjectName",1 AS "IsLeaf"
                FROM "TempJobRate" AS "r" KEY JOIN "TempPayBand" AS "b"
                WHERE "r"."vacancyid" = "ParentID" AND("r"."enddate" IS NULL OR "r"."enddate" > CURRENT DATE)
                ORDER BY "b"."sortorder" ASC
            END IF
          END IF
        END IF
      END IF
    END IF
  END IF
END
}