pears.NetCandidateShiftTemplates [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.NetCandidateShiftTemplates ======
<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"."NetCandidateShiftTemplates"( in "pWebUserID" char(20),in "CategoryFilter" char(1) default null ) 
result( "EventID" char(20),"EventName" char(100),"EventFrom" char(20),"EventTo" char(20),"Grp" char(1),"SortOrder" char(10) ) 
begin
  declare "DivID" char(20);
  declare "DefaultFrom" char(10) = '00:00';
  declare "DefaultTo" char(10) = '23:59';
  declare "CatFilter" char(1);
  set "DivID" = (select first "divisionid" from "person" as "p" key join "iqxnetuserlink" as "i" where "i"."iqxnetuserid" = "pWebUserID");
  select top 1 "dateformat"("p"."defaultshiftfrom",'hh:nn'),"dateformat"("defaultshiftto",'hh:nn') into "DefaultFrom","DefaultTo" from "params" as "p";
  select "AvailTemplateID" as "EventID",
    "string"("Name",' ',"dateformat"("isnull"("TimeFrom","DefaultFrom"),'hh:nn'),' - ',"dateformat"("isnull"("TimeTo","DefaultTo"),'hh:nn')) as "EventName",
    "dateformat"("isnull"("TimeFrom","DefaultFrom"),'hh:nn') as "EventFrom",
    "dateformat"("isnull"("TimeTo","DefaultTo"),'hh:nn') as "EventTo",
    'A' as "Grp",
    "dateformat"("EventFrom",'hhnn') as "SOrder"
    from "AvailabilityTemplate" as "a"
    where("a"."divisionid" = "DivID" or "isnull"("a"."divisionid",'') = '')
    and "PublishToWeb" = '1'
    and "Grp" = "isnull"("CategoryFilter",'A') union
  select "TempShiftUnavailableReasonID" as "EventID",
    "Name" as "EventName",
    "DefaultFrom" as "EventFrom",
    "DefaultTo" as "EventTo",
    'U' as "Grp",
    "Sortorder" as "SOrder"
    from "TempShiftUnavailableReason"
    where "isnull"("PublishToWeb",'0') = '1'
    and "Grp" = "isnull"("CategoryFilter",'U')
    order by "Grp" asc,"SOrder" asc
end
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateShiftTemplates" IS 
{create PROCEDURE pears."NetCandidateShiftTemplates"(in pWebUserID char(20), in CategoryFilter char(1) default null)
RESULT(EventID char(20),EventName char(100),EventFrom char(20),EventTo char(20),Grp char(1), SortOrder char(10))
BEGIN
	declare DivID char(20);
    declare DefaultFrom char(10) default '00:00';
    declare DefaultTo char(10) default '23:59';
    declare CatFilter char(1);

    set DivID = (select first divisionid from person as p key join iqxnetuserlink as i where i.iqxnetuserid = pWebUserID);
    select top 1 dateformat(p.defaultshiftfrom,'hh:nn'),dateformat(defaultshiftto,'hh:nn') into DefaultFrom,DefaultTo from params p;

    select
        AvailTemplateID as EventID, 
        string(Name,' ',dateformat(isnull(TimeFrom,DefaultFrom),'hh:nn'),' - ',dateformat(isnull(TimeTo,DefaultTo),'hh:nn')) as EventName,
        dateformat(isnull(TimeFrom,DefaultFrom),'hh:nn') as EventFrom,
        dateformat(isnull(TimeTo,DefaultTo),'hh:nn') as EventTo,
        'A' as Grp,
        dateformat(EventFrom,'hhnn') as SOrder
    from 
        AvailabilityTemplate as a
    where
        (a.divisionid = DivID or isnull(a.divisionid,'') = '') 
        and PublishToWeb = '1'
        and Grp = isnull(CategoryFilter,'A')
    union
    select 
        TempShiftUnavailableReasonID as EventID,
        Name as EventName,
        DefaultFrom as EventFrom,
        DefaultTo as EventTo,
        'U' as Grp,
        Sortorder as SOrder    
    from
        TempShiftUnavailableReason
    where 
        isnull(PublishToWeb,'0') = '1'
        and Grp = isnull(CategoryFilter,'U')
    order by
        Grp Asc,SOrder asc

END
}
</code>