pears.NetOwnerShortlistedJobs

CREATE PROCEDURE "pears"."NetOwnerShortlistedJobs"( IN "pWebUserID" CHAR(20) ) 
/* RESULT( column_name column_type, ... ) */
BEGIN
  DECLARE "pStaffID" CHAR(20);
  SET "pStaffID" = (SELECT "staffid" FROM "iqxnetuser" WHERE "iqxnetuserid" = "pWebUserID");
  SELECT "person"."personid","person"."name","vacancy"."position","actiondate","status"."name" AS "status"
    FROM "progress"
      LEFT OUTER JOIN "vacancy"
      ON "progress"."vacancyID" = "vacancy"."vacancyid"
      LEFT OUTER JOIN "person"
      ON "progress"."personid" = "person"."personid"
      FULL OUTER JOIN "status"
      ON "progress"."status" = "status"."status"
    WHERE "progress"."status" = 'H'
    AND "type" = 'R'
    AND "vacancy"."staffid" = "pStaffID"
END /* DOC
2016-12-01 PC test and doc
added check on pWebUserID
 
Inputs:
pWebUserID
 
Outputs:
undefined
 
Tests:
invalid pWebUserID
valid pWebUserID
 
*/
/* 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 vacancy set staffid='TEST' where vacancyid='TEST';
insert into progress (progressid,vacancyid,status,staffid,personid) on existing update values('TEST','TEST','H','TEST','TEST');
select * from NetOwnerShortlistedJobs(null);
expect EOF;
select * from NetOwnerShortlistedJobs('test.owner');
expect personid=TEST,name=test,position=test,status=Interested;
update vacancy set staffid=null where vacancyid='TEST';
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerShortlistedJobs" IS 
{CREATE PROCEDURE pears."NetOwnerShortlistedJobs"( IN "pWebUserID" CHAR(20) )
/* RESULT( column_name column_type, ... ) */
BEGIN
    DECLARE pStaffID CHAR(20);
    SET pStaffID=(SELECT staffid FROM iqxnetuser WHERE iqxnetuserid=pWebUserID);
 
    SELECT person.personid, person.name, vacancy.position, actiondate, STATUS.name AS STATUS
    FROM progress 
    LEFT JOIN vacancy
    ON progress.vacancyID=vacancy.vacancyid
    LEFT JOIN person
    ON progress.personid=person.personid
    FULL OUTER JOIN STATUS
    ON progress.status=STATUS.status
    WHERE 
    progress.status = 'H'
    AND TYPE='R'
    AND vacancy.staffid=pStaffID;
END
 
/* DOC
2016-12-01 PC test and doc
    added check on pWebUserID
 
Inputs:
    pWebUserID
 
Outputs:
    undefined
 
Tests:
    invalid pWebUserID
    valid pWebUserID
 
*/
 
/* 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 vacancy set staffid='TEST' where vacancyid='TEST';
insert into progress (progressid,vacancyid,status,staffid,personid) on existing update values('TEST','TEST','H','TEST','TEST');
select * from NetOwnerShortlistedJobs(null);
expect EOF;
select * from NetOwnerShortlistedJobs('test.owner');
expect personid=TEST,name=test,position=test,status=Interested;
update vacancy set staffid=null where vacancyid='TEST';
*/
}