====== pears.NetOwnerRequirementShortListPlaced ======
<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"."NetOwnerRequirementShortListPlaced"( in "pWebUserID" char(20),in "pRowID" char(20),in "IsShift" char(1) ) 
result( "PersonName" char(60),"PersonID" char(20),"PersonStatus" char(50) ) 
// IQXWeb
begin
  declare "VacID" char(20);
  if "IsShift" = 0 then
    set "VacID" = "pRowID"
  end if;
  if "IsShift" = 1 then
    set "VacID" = (select "VacancyID" from "TempShiftPlan" where "tempshiftplanid" = "pRowID")
  end if;
  select distinct
    "pe"."name",
    "pe"."personid",
    "s"."name" as "PersonStatus"
    from "placement" as "p"
      key join "employment" as "e"
      key join "person" as "pe"
      left outer join "status" as "s" on "pe"."status" = "s"."status" and "s"."type" = 'P'
    where "p"."vacancyid" = "VacID"
    order by "pe"."name" asc
end /* DOC
2016-11-30 PC add doc and test
2017-11-01 PC RC2 modify to refer to placement table directly, include state and make available for all consultants. Also check count and return 'no data' if no records
2018-09-02 PC IW-685 simplify status column

Inputs:
pWebUserID
pRowID
IsShift

Outputs:
PersonName
PersonID
PersonStatus

Tests:
invalid pWebUserID
valid pWebUserID, invalid Rowid
valid pWebUserID, Rowid invalid IsShift
valid pWebUserID, Rowid, IsShift=0
valid pWebUserID, Rowid, IsShift=1
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.owner';
update iqxnetuser set staffid=null where staffid='TEST';
update iqxnetuser set staffid='TEST' where iqxnetuserid='TEST.OWNER';
insert into progress (progressid,vacancyid,personid,status,staffid) on existing skip values ('TEST','TEST','TEST','#','TEST');

update vacancy set staffid='TEST' where vacancyid='TEST';
select * from NetOwnerRequirementShortListPlaced(null,null,null);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner',null,0);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner','zz',0);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',0);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
update person set status='X' where personid='test';
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',1);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
update person set status=null where personid='test';
insert into tempshiftprogress (tempshiftprogressid,tempshiftplanid,personid,status,staffid) on existing skip values ('TEST','TEST','TEST','#','TEST');
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',1);
expect PersonName=test,PersonID=TEST,PersonStatus=*;

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerRequirementShortListPlaced" IS 
{create PROCEDURE pears."NetOwnerRequirementShortListPlaced"(in pWebUserID char(20),in pRowID char(20),in IsShift char(1))
RESULT(PersonName char(60),PersonID char(20),PersonStatus char(50))
// IQXWeb
BEGIN
  declare VacID char(20);
  if IsShift=0  then
    set VacID=pRowID;	
  end if;
  if IsShift=1  then
    set "VacID" = (select VacancyID from "TempShiftPlan" where "tempshiftplanid" = pRowID);
  end if;
  select distinct
    pe.name,
    pe.personid, 
    s.name as PersonStatus
from 
    placement p
    key join employment e
    key join person pe
    left join status s on pe.status=s.status and s.type='P'
where
    p.vacancyid=VacID
order BY 
    pe.name asc 
END

/* DOC
2016-11-30 PC add doc and test
2017-11-01 PC RC2 modify to refer to placement table directly, include state and make available for all consultants. Also check count and return 'no data' if no records
2018-09-02 PC IW-685 simplify status column

Inputs:
    pWebUserID
    pRowID
    IsShift

Outputs:
    PersonName
    PersonID
    PersonStatus

Tests:
    invalid pWebUserID
    valid pWebUserID, invalid Rowid
    valid pWebUserID, Rowid invalid IsShift
    valid pWebUserID, Rowid, IsShift=0
    valid pWebUserID, Rowid, IsShift=1
*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.client','test.agency','test.candidate','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.owner';
update iqxnetuser set staffid=null where staffid='TEST';
update iqxnetuser set staffid='TEST' where iqxnetuserid='TEST.OWNER';
insert into progress (progressid,vacancyid,personid,status,staffid) on existing skip values ('TEST','TEST','TEST','#','TEST');

update vacancy set staffid='TEST' where vacancyid='TEST';
select * from NetOwnerRequirementShortListPlaced(null,null,null);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner',null,0);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner','zz',0);
expect EOF;
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',0);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
update person set status='X' where personid='test';
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',1);
expect PersonName=test,PersonID=TEST,PersonStatus=*;
update person set status=null where personid='test';
insert into tempshiftprogress (tempshiftprogressid,tempshiftplanid,personid,status,staffid) on existing skip values ('TEST','TEST','TEST','#','TEST');
select * from NetOwnerRequirementShortListPlaced('test.owner','TEST',1);
expect PersonName=test,PersonID=TEST,PersonStatus=*;

*/
}
</code>