====== pears.NetClientShiftDetailsTemplates ======
<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"."NetClientShiftDetailsTemplates"( in "pWebUserID" char(20),in "pVacancyID" char(20) default null ) 
result( "LineID" char(20),"TimeFrom" char(10),"TimeTo" char(10),"BreakMinutes" integer,"Moveable" integer,"Position" char(50) ) 
begin
  // IQXWeb
  select "p"."TempShiftTemplateID" as "LineID",
    cast("dateformat"("p"."timefrom",'hh:mm') as char) as "TimeFrom",
    cast("dateformat"("p"."timeto",'hh:mm') as char) as "TimeTo",
    "p"."breakMinutes" as "BreakMinutes",
    "p"."moveable" as "Moveable",
    "p"."Description" as "Position"
    from "TempShiftTemplate" as "p"
      key join "TempShiftTemplateAllowed"
      key join "Vacancy" as "v"
      key join "Employment"
      key join "Company" as "c"
    where "v"."vacancyid" = "pvacancyid"
    and "c"."CompanyID" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
    and "v"."status" = 'C'
    order by 6 asc,2 asc
end /* DOC
2016-10-18 PC shift template for dropdown on Current Requirements page
IW-69


Tests:
valid pwebuserid and pVacancyID but invalid state
valid pwebuserid and pVacancyID and state

*/
/* TEST
call NetTestSetup('');
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update vacancy set status='P' where vacancyid='test';
select * from NetClientShiftDetailsTemplates('test.client','TEST');
expect EOF;
update vacancy set status='C' where vacancyid='test';
insert into TempShiftTemplateAllowed (TempShiftTemplateID,VacancyID) on existing update values ('test','test');
select * from NetClientShiftDetailsTemplates('test.client','TEST');
expect LineID=TEST,TimeFrom=08:00,TimeTo=16:00,BreakMinutes=30,Moveable=1,Position=test;
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
call NetTestSetup('');
*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetClientShiftDetailsTemplates" IS 
{create PROCEDURE pears."NetClientShiftDetailsTemplates"(in pWebUserID char(20),in pVacancyID char(20) default null)
RESULT(LineID char(20),TimeFrom char(10),TimeTo char(10),BreakMinutes integer,Moveable integer,Position char(50))
BEGIN
// IQXWeb
  select
    p."TempShiftTemplateID" as LineID,
    cast(dateformat(p.timefrom,'hh:mm') as char) as TimeFrom,
    cast(dateformat(p.timeto,'hh:mm') as char) as TimeTo,
    p.breakMinutes as BreakMinutes,
    p.moveable as Moveable,
    "p"."Description" as "Position"    
from 
    "TempShiftTemplate" as "p" 
    key join "TempShiftTemplateAllowed" 
    key join "Vacancy" as "v" 
    key join "Employment" 
    key join "Company" as c
  where 
    "v"."vacancyid" = "pvacancyid"
    and c."CompanyID" = any(select "companyid" from "employment" key join "iqxnetuserlink" where "iqxnetuserid" = "pWebUserID")
    and "v"."status" = 'C'  
  order by 6 asc,2 asc
END

/* DOC
2016-10-18 PC shift template for dropdown on Current Requirements page
IW-69

   
Tests:
    valid pwebuserid and pVacancyID but invalid state
    valid pwebuserid and pVacancyID and state

*/

/* TEST
call NetTestSetup('');
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.client';
update vacancy set status='P' where vacancyid='test';
select * from NetClientShiftDetailsTemplates('test.client','TEST');
expect EOF;
update vacancy set status='C' where vacancyid='test';
insert into TempShiftTemplateAllowed (TempShiftTemplateID,VacancyID) on existing update values ('test','test');
select * from NetClientShiftDetailsTemplates('test.client','TEST');
expect LineID=TEST,TimeFrom=08:00,TimeTo=16:00,BreakMinutes=30,Moveable=1,Position=test;
update vacancy set temp=0,status=null,tempdeskid=null,expiry=null where vacancyid='test';
call NetTestSetup('');
*/
}
</code>