pears.ComplianceValidforTempShift

Generated schema reference. Regenerate this page from the SQL unload; keep hand-maintained business notes in the narrative namespace.

Original SQL

CREATE FUNCTION "pears"."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" AS "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