====== pears.NetAgencyCurrentRequirementConfirmInterest ======
<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"."NetAgencyCurrentRequirementConfirmInterest"( in "pWebUserID" char(20),in "pLineID" char(22),in "pTransactionType" char(1),in "pNote" char(255),in "pDeclinedReason" char(20) default null ) 
result( "retval" char(250) ) 
begin
  // IQXWeb
  declare "CEKey" char(20);
  declare "dStaffID" char(20);
  declare "dEmploymentID" char(20);
  declare "ConfirmContactClass" char(2);
  declare "DeclineContactClass" char(2);
  declare "CommentClass" char(2);
  declare "SecondaryAgencyID" char(20);
  declare "LineID" char(20);
  declare "ShiftRef" char(20);
  declare "dWhoID" char(20);
  declare "DeclinedReason" char(255);
  set "CEKey" = "uniquekey"("pLineID");
  if "isnull"("pTransactionType",'') not in( 'C','D','N' ) then
    select '99:~Invalid transaction type'
  end if;
  if "left"("pLineID",1) = 'V' then
    set "LineID" = "right"("pLineID",20);
    select "refcode" into "ShiftRef" from "vacancy" where "vacancyID" = "LineID"
  end if;
  if "left"("pLineID",1) = 'P' then
    set "LineID" = (select "vacancyid" from "tempshiftplan" where "tempshiftplanid" = "right"("pLineID",20));
    select "ShiftSerialNumber" into "ShiftRef" from "tempshiftplan" where "tempshiftplanid" = "right"("pLineID",20)
  end if;
  select top 1 "employment"."employmentid" into "dEmploymentID" from "iqxnetuserlink" key join "employment" where "iqxnetuserid" = "pWebUserID" order by "isnull"("employment"."leavedate",current date) desc;
  select "e"."personid" into "dWhoID" from "employment" as "e" where "e"."employmentid" = "dEmploymentID";
  set "dStaffID" = (select "staffid" from "vacancy" where "vacancyid" = "LineID");
  set "ConfirmContactClass" = (select top 1 "classcode" from "contactclass" where "classdescrip" = 'Confirm Interest' order by "sortorder" asc);
  set "DeclineContactClass" = (select top 1 "classcode" from "contactclass" where "classdescrip" = 'Decline Interest' order by "sortorder" asc);
  set "CommentClass" = (select top 1 "classcode" from "contactclass" where "classdescrip" = 'Client Comment' order by "sortorder" asc);
  set "SecondaryAgencyID" = (select "CompanyID" from "Employment" where "EmploymentID" = "dEmploymentID");
  -- Confirm
  if "pTransactionType" = 'C' then
    insert into "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes","whoentered" ) values
      ( "CEKey","LineID","dStaffID","dEmploymentID",current date,current time,'Confirm Interest:'+"ShiftRef","ConfirmContactClass","pNote","dWhoID" ) ;
    select '0:~success#Confirm Interest at '+"dateformat"(current timestamp,'dd/mm/yyyy hh:mm')
  end if;
  -- Decline
  if "pTransactionType" = 'D' then
    -- Mark Shift or Vacancy Cascade record as declined
    if "left"("pLineID",1) = 'P' then
      set "DeclinedReason" = (select top 1 "reason" from "rejectionreason" where "rejectreasonid" = "pDeclinedReason" order by "sortorder" asc);
      update "CascadedShift" set "WhenDeclined" = current timestamp,"DeclinedNote" = "pNote","Details" = "DeclinedReason" where "TempShiftPlanID" = "LineID" and "SecondaryAgencyID" = "SecondaryAgencyID"
    end if;
    if "left"("pLineID",1) = 'V' then
      update "CascadedVacancy" set "WhenDeclined" = current timestamp,"Details" = "pNote" where "VacancyID" = "LineID" and "SecondaryAgencyID" = "SecondaryAgencyID"
    end if;
    insert into "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes","whoentered" ) values
      ( "CEKey","LineID","dStaffID","dEmploymentID",current date,current time,'Decline Interest:'+"ShiftRef"+' '+"DeclinedReason","DeclineContactClass","pNote","dWhoID" ) ;
    select '0:~success#Decline Interest at '+"dateformat"(current timestamp,'dd/mm/yyyy hh:mm')
  end if;
  -- Note (comment)
  if "pTransactionType" = 'N' then
    insert into "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes","whoentered" ) values
      ( "CEKey","LineID","dStaffID","dEmploymentID",current date,current time,'Add comment:'+"ShiftRef","CommentClass","pNote","dWhoID" ) ;
    select '0:~success#Comment made at '+"dateformat"(current timestamp,'dd/mm/yyyy hh:mm')+': '+"pNote"
  end if
end /* DOC
2016-10-06 PC set decline for cascaded shift / vacancy, and create confirm / decline / Note entry into the contact event table
2019-03-05 PC IW-873 add top 1 and order by on subqueries

This routine requires three contact event types which must contain the following text:
Confirm Interest
Decline Interest
Client Comment
*/
/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
select * from NetAgencyCurrentRequirementConfirmInterest('test.agency','VTESTTESTTESTTESTTEST','C','TestNote');
expect retval=0:~success#Confirm*;
select top 1 staffid,vacancyid,employmentid,classcode,notes from contactevent order by whenentered desc,contacteventid desc;
expect staffid=test,vacancyid=TESTTESTTESTTESTTEST,employmentid=TEST,classcode=CI,notes=TestNote;
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';

*/
go

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetAgencyCurrentRequirementConfirmInterest" IS 
{create PROCEDURE pears."NetAgencyCurrentRequirementConfirmInterest"( in "pWebUserID" char(20),in "pLineID" char(22),in "pTransactionType" char(1), in pNote char(255),in pDeclinedReason char(20) default null)
result( "retval" char(250) ) 
BEGIN
// IQXWeb
    declare CEKey char(20);
    declare dStaffID char(20);
    declare dEmploymentID char(20);
    declare ConfirmContactClass char(2);
    declare DeclineContactClass char(2);
    declare CommentClass char(2);
    declare SecondaryAgencyID char(20);
    declare LineID char(20);
    declare ShiftRef char(20);
    declare dWhoID char(20);
    declare DeclinedReason char(255);
    set CEKey = "uniquekey"("pLineID");
    if isnull(pTransactionType,'') not in ('C','D','N') then
        select '99:~Invalid transaction type';
    end if;
    if left(pLineID,1)='V' then 
        set LineID = right(pLineID,20);
        select "refcode" into ShiftRef from "vacancy" where "vacancyID" = "LineID"; 
    end if;
    if left(pLineID,1)='P' then 
        set LineID = (select vacancyid from tempshiftplan where tempshiftplanid=right(pLineID,20));
        select "ShiftSerialNumber" into ShiftRef from tempshiftplan where tempshiftplanid=right(pLineID,20); 
    end if;
    select top 1 employment."employmentid" into dEmploymentID from "iqxnetuserlink" key join employment where "iqxnetuserid" = "pWebUserID" order by isnull(employment.leavedate,current date) desc;
    select e.personid into dWhoID from employment e where e.employmentid = dEmploymentID;
    set dStaffID = (select "staffid" from "vacancy" where "vacancyid" = "LineID");
    set ConfirmContactClass = (select top 1 classcode from contactclass where classdescrip='Confirm Interest' order by sortorder);
    set DeclineContactClass = (select top 1 classcode from contactclass where classdescrip='Decline Interest' order by sortorder);
    set CommentClass = (select top 1 classcode from contactclass where classdescrip='Client Comment' order by sortorder);
    set SecondaryAgencyID = (select "CompanyID" from "Employment" where "EmploymentID" = dEmploymentID);
  -- Confirm
  if "pTransactionType" = 'C' then
    insert into "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes",whoentered ) values
      (CEKey,"LineID",dStaffID,dEmploymentID,current date,current time,'Confirm Interest:'+ShiftRef,ConfirmContactClass,"pNote",dWhoID ) ;
    select '0:~success#Confirm Interest at '+"dateformat"(current timestamp,'dd/mm/yyyy hh:mm')
  end if;
  -- Decline
  if "pTransactionType" = 'D' then
    -- Mark Shift or Vacancy Cascade record as declined
    if left(pLineID,1)='P' then
      set DeclinedReason = (select top 1 reason from rejectionreason where rejectreasonid = pDeclinedReason order by sortorder);
      update "CascadedShift" set "WhenDeclined" = current timestamp,"DeclinedNote" = "pNote",Details=DeclinedReason where "TempShiftPlanID" = "LineID" and "SecondaryAgencyID" = SecondaryAgencyID
    end if;
    if left(pLineID,1)='V' then
      update "CascadedVacancy" set "WhenDeclined" = current timestamp,Details=pNote where "VacancyID" = "LineID" and "SecondaryAgencyID" = SecondaryAgencyID
    end if;
    insert into "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes",whoentered ) values
      ( "CEKey","LineID",dStaffID,dEmploymentID,current date,current time,'Decline Interest:'+ShiftRef+' '+DeclinedReason,DeclineContactClass,"pNote",dWhoID ) ;
    select '0:~success#Decline Interest at '+"dateformat"(current timestamp,'dd/mm/yyyy hh:mm')
  end if;
 -- Note (comment)
  if "pTransactionType" = 'N' then
      insert into "contactevent"( "contacteventid","vacancyid","staffid","employmentid","contactdate","contacttime","description","classcode","notes",whoentered ) values
        ( "CEKey","LineID",dStaffID,dEmploymentID,current date,current time,'Add comment:'+ShiftRef,CommentClass,"pNote",dWhoID ) ;
    select '0:~success#Comment made at '+"dateformat"(current timestamp,'dd/mm/yyyy hh:mm')+': '+"pNote"
  end if
END

/* DOC
2016-10-06 PC set decline for cascaded shift / vacancy, and create confirm / decline / Note entry into the contact event table
2019-03-05 PC IW-873 add top 1 and order by on subqueries

This routine requires three contact event types which must contain the following text:
    Confirm Interest
    Decline Interest
    Client Comment
*/

/* TEST
call NetTestSetup('');
update iqxnetuserlink set employmentid=null where IQXNetUserLinkID in ('test.candidate','test.client','test.owner');
update iqxnetuserlink set employmentid='TEST' where IQXNetUserLinkID='test.agency';
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';
insert into vacancy (vacancyid,departmentid,employmentid,status,position,staffid) values ('TESTTESTTESTTESTTEST','~~','TEST','[','test2','test');
select * from NetAgencyCurrentRequirementConfirmInterest('test.agency','VTESTTESTTESTTESTTEST','C','TestNote');
expect retval=0:~success#Confirm*;
select top 1 staffid,vacancyid,employmentid,classcode,notes from contactevent order by whenentered desc,contacteventid desc;
expect staffid=test,vacancyid=TESTTESTTESTTESTTEST,employmentid=TEST,classcode=CI,notes=TestNote;
delete from contactevent where vacancyid='TESTTESTTESTTESTTEST';
delete from vacancy where vacancyid='TESTTESTTESTTESTTEST';

*/
}
</code>