pears.ComplianceValidforTempShift

COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."ComplianceValidforTempShift" IS 
{CREATE FUNCTION ComplianceValidforTempShift 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
( IN "pPersonID" CHAR(20),IN "pTempShiftPlanID" CHAR(20) ) 
RETURNS long VARCHAR
BEGIN
  DECLARE "pComplianceDomainID" CHAR(20);
  DECLARE pClientCode CHAR(12);
  DECLARE "pDate" DATE;
  DECLARE "pDeptID" CHAR(2);
  SELECT "t"."ComplianceDomainID","p"."ShiftDate",c.TempComplianceCode INTO "pComplianceDomainID","pDate",pClientCode 
     FROM "TempShiftPlan" AS "p" KEY JOIN "TempShiftTemplate" AS "t",
          "TempShiftPlan" AS "p" KEY JOIN vacancy KEY JOIN employment KEY JOIN company c
     WHERE "p"."tempshiftplanid" = "pTempShiftPlanID";
  IF "pComplianceDomainID" IS NULL THEN
    SELECT "v"."ComplianceDomainID","v"."DepartmentID" INTO "pComplianceDomainID","pDeptID" FROM "TempShiftPlan" AS "p" KEY JOIN "vacancy" AS "v" WHERE "p"."tempshiftplanid" = "pTempShiftPlanID"
  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
}