pears.ComplianceValidforPlacement

CREATE FUNCTION "pears"."ComplianceValidforPlacement"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN "pPersonID" CHAR(20),IN "pVacancyID" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "pComplianceDomainID" CHAR(20);
  DECLARE "pClientCode" CHAR(12);
  DECLARE "pDate" DATE;
  DECLARE "pDeptID" CHAR(2);
  SELECT "v"."ComplianceDomainID","v"."StartDate","v"."DepartmentID","c"."TempComplianceCode" INTO "pComplianceDomainID","pDate","pDeptID","pClientCode" FROM "vacancy" AS "v" KEY JOIN "employment" JOIN "company" AS "c" WHERE "v"."vacancyid" = "pVacancyID";
  IF "pDate" IS NULL OR "pDate" < CURRENT DATE THEN
    SET "pDate" = CURRENT DATE
  END IF;
  IF "pComplianceDomainID" IS NULL THEN
    SELECT FIRST "ComplianceDomainID" INTO "pComplianceDomainID" FROM "ComplianceDomain" WHERE "DepartmentID" = "pDeptID" AND "Name" LIKE 'Default%'
      AND("ClientTempComplianceCode" IS NULL OR "ClientTempComplianceCode" = "pClientCode")
      ORDER BY "ClientTempComplianceCode" DESC,"Name" ASC
  END IF;
  IF "pComplianceDomainID" IS NOT NULL THEN
    RETURN "ComplianceValid"("pPersonID","pComplianceDomainID","pDate","pClientCode")
  END IF
END