====== pears.ComplianceValidforPlacement ======
<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 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
</code>