pears.NetOwnerRegistrations [iqx]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== pears.NetOwnerRegistrations ======
<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"."NetOwnerRegistrations"( in "pWebUserID" char(20),in "pCandidateID" char(20) default null ) 
result( "DepartmentID" char(10),"PersonID" char(20),"DepartmentName" char(100),"TempJobs" smallint,"PermJobs" smallint,"Details" char(100),"SortOrder" char(20) ) 
// IQXWeb
begin
  select distinct
    "department"."departmentid",
    "search"."personid",
    "department"."name" as "DeptName",
    "isnull"("search"."temp",0) as "TempJobs",
    "isnull"("search"."permanent",0) as "PermJobs",
    "isnull"("string"((if "TempJobs" <> 0 then 'Temp jobs' endif),(if "TempJobs"*"Permjobs" <> 0 then ', ' endif),(if "PermJobs" <> 0 then 'Perm jobs' endif)),'Not Registered') as "details",
    "department"."sortorder" as "SO"
    from "department"
      key join "search"
    where "search"."personid" = "pCandidateID"
    and "department"."searchable" = 1
    and "department"."publishtoweb" = 1
    and("department"."divisionid" is null or "department"."divisionid" = any(select "p"."divisionid" from "person" as "p" where "p"."personid" = "pCandidateID"))
    and("department"."divisionid" is null or "department"."divisionid" = any(select "DivisionID" from "DashboardStaffDivisions"()))
    order by "SO" asc,
    "DeptName" asc
end /* DOC
2018-10-05 GJ Created Proc
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRegistrations" IS 
{create PROCEDURE pears."NetOwnerRegistrations"( in pWebUserID char(20), in pCandidateID char(20) default null ) 
result( DepartmentID char(10),PersonID char(20),DepartmentName char(100),TempJobs smallint,PermJobs smallint,Details char(100),SortOrder char(20)) 
// IQXWeb
begin
select distinct
    department.departmentid,
    [search].personid,
    department.name as DeptName,
    isnull([search].[temp],0) as TempJobs,
    isnull([search].permanent,0) as PermJobs,
    isnull(string((if TempJobs <> 0 then 'Temp jobs' endif ),(if TempJobs * Permjobs <> 0 then ', ' endif ),(if PermJobs <> 0 then 'Perm jobs' endif )),'Not Registered') as details,
    department.sortorder as SO
  from 
    department 
    key join [search] 
  where 
    [search].personid = pCandidateID 
    and department.searchable = 1 
    and department.publishtoweb = 1
    and (department.divisionid is null or department.divisionid in (select p.divisionid from person p where p.personid=pCandidateID)) 
    and (department.divisionid is null or department.divisionid in (select DivisionID from DashboardStaffDivisions()))
  order by 
    SO asc,
    DeptName asc
end
/* DOC
2018-10-05 GJ Created Proc
*/
}
</code>