pears.NetOwnerVacancyDetails

CREATE PROCEDURE "pears"."NetOwnerVacancyDetails"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20) ) 
RESULT( "ObjID" CHAR(20),"Position" CHAR(50),"Their_Ref" CHAR(50),"Start_Date__DT" DATE,"Finish_Date__DT" DATE,"Expiry_Date__DT" DATE,"Our_Ref" CHAR(20),"Contract_Ref" CHAR(20),"Notes" long VARCHAR ) 
// IQXWeb
BEGIN
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    RETURN
  END IF;
  IF "isnull"("pVacancyID",'') = '' THEN
    SELECT '','','',NULL,NULL,NULL,'','',''
  ELSE
    SELECT "vacancy"."vacancyid" AS "ObjID",
      "vacancy"."position" AS "Position",
      "vacancy"."TheirRef" AS "Their_Ref",
      "vacancy"."startdate" AS "Start_Date__DT",
      "vacancy"."FinishDate" AS "Finish_Date__DT",
      "vacancy"."expiry" AS "Expiry_Date__DT",
      "vacancy"."refcode" AS "Our_Ref",
      "vacancy"."ContractRef" AS "Contract_Ref",
      "vacancy"."othernotes" AS "Notes"
      FROM "vacancy" KEY JOIN "employment" KEY JOIN "company"
      WHERE "vacancyid" = "pVacancyID" AND "company"."divisionid" = "divid"
  END IF
END /* DOC
2017-06-05 PC Test and doc
 
*/
/* 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 company set divisionid='TEST' where companyid='TEST';
update staff set divisionid='TEST' where staffid='TEST';
select replace(ObjID,'','!') as A from NetOwnerVacancyDetails(null,null);
expect EOF;
select '!'+ObjID+'!' as B from NetOwnerVacancyDetails('test.owner',null);
expect B=!!;
select replace(ObjID,'','!') as C from NetOwnerVacancyDetails('test.owner','TEST');
expect C=TEST;
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetOwnerVacancyDetails" IS 
{CREATE PROCEDURE pears."NetOwnerVacancyDetails"( IN "pWebUserID" CHAR(20),IN "pVacancyID" CHAR(20) ) 
RESULT( "ObjID" CHAR(20),"Position" CHAR(50),"Their_Ref" CHAR(50),"Start_Date__DT" DATE,"Finish_Date__DT" DATE,"Expiry_Date__DT" DATE,"Our_Ref" CHAR(20),"Contract_Ref" CHAR(20),"Notes" long VARCHAR ) 
// IQXWeb
BEGIN
  DECLARE "divid" CHAR(20);
  SET "divid" = (SELECT FIRST "staff"."divisionid" FROM "iqxnetuser" KEY JOIN "staff" WHERE "iqxnetuser"."iqxnetuserid" = "pwebuserid");
  IF "divid" IS NULL THEN
    RETURN
  END IF;
  IF "isnull"("pVacancyID",'') = '' THEN
    SELECT '','','',NULL,NULL,NULL,'','',''
  ELSE
    SELECT "vacancy"."vacancyid" AS "ObjID",
      "vacancy"."position" AS "Position",
      "vacancy"."TheirRef" AS "Their_Ref",
      "vacancy"."startdate" AS "Start_Date__DT",
      "vacancy"."FinishDate" AS "Finish_Date__DT",
      "vacancy"."expiry" AS "Expiry_Date__DT",
      "vacancy"."refcode" AS "Our_Ref",
      "vacancy"."ContractRef" AS "Contract_Ref",
      "vacancy"."othernotes" AS "Notes"
      FROM "vacancy" KEY JOIN "employment" KEY JOIN "company"
      WHERE "vacancyid" = "pVacancyID" AND "company"."divisionid" = "divid"
  END IF
END
 
/* DOC
2017-06-05 PC Test and doc
 
*/
 
/* 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 company set divisionid='TEST' where companyid='TEST';
update staff set divisionid='TEST' where staffid='TEST';
select replace(ObjID,'','!') as A from NetOwnerVacancyDetails(null,null);
expect EOF;
select '!'+ObjID+'!' as B from NetOwnerVacancyDetails('test.owner',null);
expect B=!!;
select replace(ObjID,'','!') as C from NetOwnerVacancyDetails('test.owner','TEST');
expect C=TEST;
*/
}