====== pears.NetOwnerTreeSource ======
<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"."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
}
</code>