pears.placementelementvalidV2

CREATE FUNCTION "pears"."placementelementvalidV2"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "bvalid" SMALLINT,IN "splacid" CHAR(20),IN "spersid" CHAR(20),IN "svacid" CHAR(20),IN "selementname" CHAR(20),IN "ismanager" SMALLINT,IN "isaccounts" SMALLINT ) 
RETURNS SMALLINT
BEGIN
  DECLARE "rv" SMALLINT;
  DECLARE "l" long VARCHAR;
  DECLARE "s" long VARCHAR;
  DECLARE "i" INTEGER;
  DECLARE "dt" CHAR(1);
  DECLARE "subname" CHAR(30);
  DECLARE "subchar" CHAR(1);
  IF "isaccounts" IS NULL THEN
    SET "isaccounts" = 0
  END IF;
  IF "isaccounts" <> 0 THEN
    SET "isaccounts" = 1
  END IF;
  IF "ismanager" IS NULL THEN
    SET "ismanager" = 0
  END IF;
  IF "ismanager" <> 0 THEN
    SET "ismanager" = 1
  END IF;
  SET "l" = NULL;
  IF "bvalid" = 1 THEN
    SELECT "validationcheck" INTO "l" FROM "placementelementtype" WHERE "name" = "selementname"
  ELSE
    SELECT "warningcheck" INTO "l" FROM "placementelementtype" WHERE "name" = "selementname"
  END IF;
  IF "l" IS NULL THEN
    RETURN(1)
  END IF;
  "subloop": loop
    SET "i" = "patindex"('%{%}%',"l");
    IF "i" = 0 THEN
      leave "subloop"
    END IF;
    SET "subname" = '';
    "charloop": loop
      SET "subchar" = "substr"("l","i",1);
      IF "length"("l") = 1 THEN
        SET "l" = '' // IN this CASE stuffing crashes the server
      ELSE
        SET "l" = "stuff"("l","i",1,'')
      END IF;
      IF "subchar" = '}' OR "i" > "length"("l") THEN // The latter IS precautionary
        leave "charloop"
      ELSEIF "subchar" <> '{' THEN
        SET "subname" = "subname" || "subchar"
      END IF
    END loop "charloop";
    IF "subname" = 'ACCOUNTS' THEN
      SET "s" = '(' || "str"("isaccounts") || '=1)';
      SET "dt" = 'B'
    ELSEIF "subname" = 'MANAGER' THEN
      SET "s" = '(' || "str"("ismanager") || '=1)';
      SET "dt" = 'B'
    ELSE
      SET "s" = "placementelementvalueV2"("splacid","spersid","svacid","subname");
      IF "subname" = 'PLACEMENTID' OR "subname" = 'PERSONID' OR "subname" = 'VACANCYID' THEN
        SET "dt" = 'S'
      ELSE
        SET "dt" = NULL;
        SELECT "datatype" INTO "dt" FROM "placementelementtype" WHERE "name" = "subname"
      END IF END IF;
    IF "s" IS NULL THEN
      SET "s" = 'null'
    ELSE
      SET "s" = "replace"("s",'''','''''');
      IF "dt" = 'S' THEN
        SET "s" = '''' || "s" || ''''
      ELSE
        IF "dt" <> 'B' THEN
          SET "s" = 'cast(' || "s" || ' as real)'
        END IF
      END IF END IF;
    SET "l" = "insertstr"("i"-1,"l","s")
  END loop "subloop";
  IF "l" IS NULL OR "length"("l") = 0 THEN
    RETURN(1) // Assume valid
  END IF;
  SET "rv" = NULL;
  SET "l" = 'set rv=(select (if (' || "l" || ') then 1 else 0 endif) as bexpr)';
  EXECUTE IMMEDIATE "l";
  RETURN("rv")
END