pears.NetCandidateSaveQuestions

CREATE PROCEDURE "pears"."NetCandidateSaveQuestions"( IN "pWebUserID" CHAR(20),IN "qanswers" long VARCHAR DEFAULT NULL,IN "pExperience" long VARCHAR DEFAULT NULL,IN "pSourceID" CHAR(20) DEFAULT NULL,IN "pPersonID" CHAR(20) DEFAULT NULL ) 
RESULT( "pResult" CHAR(250) ) 
// IQXWeb
BEGIN
  DECLARE "qOriginID" CHAR(20);
  DECLARE "CombinedNote" long VARCHAR;
  SET "pPersonID" = "NetCandidateValidPersonID"("pWebUserID","pPersonID");
  IF "pPersonID" IS NULL THEN
    SELECT '97:~Unique candidate link required';
    RETURN
  END IF;
  IF "isnull"("pSourceID",'') <> '' THEN
    SET "qOriginID" = "isnull"("pSourceID",(SELECT top 1 "originid" FROM "origin" WHERE "descrip" IN( 'none','unknown' ) ORDER BY "sortorder" ASC));
    UPDATE "person" SET "originid" = "qOriginID" WHERE "personid" = "pPersonID"
  END IF;
  IF "isnull"("pExperience",'none') <> 'none' THEN
    SET "CombinedNote" = "string"('What is your experience: ',"pExperience","char"(13),"char"(13),(SELECT top 1 "notes" FROM "person" WHERE "personid" = "pPersonID"));
    UPDATE "person" SET "notes" = "CombinedNote" WHERE "personid" = "pPersonID"
  END IF;
  IF "isnull"("qanswers",'none') <> 'none' THEN
    CALL "IQXNetSaveQuestionnaire"("pPersonID","qanswers")
  END IF;
  SELECT '0:~Success'
END /* DOC
2017-11-07 GJ Created - Probably needs tweaking
2017-11-24 PC Needed tweaking :slightly_smiling_face:
2017-11-27 PC modified for Experience and Source fields
2018-02-02 PC spelling change
2018-07-19 GJ Changed to not change the sourceID when this procedure is called for the 2nd time
2018-10-22 MHS IW-456 multi-cand fix
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
Inputs:
pWebUserID
qanswers
 
Outputs:
pResult
 
*/
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."NetCandidateSaveQuestions" IS 
{CREATE PROCEDURE pears."NetCandidateSaveQuestions"( IN pWebUserID CHAR(20),IN qanswers long VARCHAR DEFAULT NULL,IN pExperience long VARCHAR DEFAULT NULL,IN pSourceID CHAR(20) DEFAULT NULL, IN pPersonID CHAR(20) DEFAULT NULL ) 
RESULT( pResult CHAR(250) ) 
// IQXWeb
BEGIN
    DECLARE qOriginID CHAR(20);
    DECLARE CombinedNote long VARCHAR;
 
    SET pPersonID=NetCandidateValidPersonID(pWebUserID,pPersonID);
    IF pPersonID IS NULL THEN
       SELECT '97:~Unique candidate link required';
       RETURN;
    END IF;
 
    IF isnull(pSourceID,'') != '' THEN
        SET qOriginID = isnull(pSourceID,(SELECT top 1 originid FROM origin WHERE descrip IN ('none','unknown') ORDER BY sortorder));
        UPDATE person SET originid=qOriginID WHERE personid=pPersonID;
    END IF;
 
    IF isnull(pExperience,'none') != 'none' THEN
        SET CombinedNote = string('What is your experience: ',pExperience,CHAR(13),CHAR(13),(SELECT top 1 notes FROM person WHERE personid=pPersonID));
        UPDATE person SET notes=CombinedNote WHERE personid=pPersonID;
    END IF;
 
 
   IF isnull(qanswers,'none') != 'none' THEN
        CALL "IQXNetSaveQuestionnaire"(pPersonID,"qanswers");
    END IF;
    SELECT '0:~Success';
END
 
/* DOC
2017-11-07 GJ Created - Probably needs tweaking
2017-11-24 PC Needed tweaking :slightly_smiling_face:
2017-11-27 PC modified for Experience and Source fields
2018-02-02 PC spelling change
2018-07-19 GJ Changed to not change the sourceID when this procedure is called for the 2nd time
2018-10-22 MHS IW-456 multi-cand fix
2019-03-05 PC IW-873 add top 1 and order by on subqueries
 
Inputs:
    pWebUserID
    qanswers
 
Outputs:
    pResult
 
*/
}