pears.InsertCISFromTemplate

CREATE PROCEDURE "pears"."InsertCISFromTemplate"( 
  /* Application Maintained Function / Procedure - DO NOT EDIT*/
  IN @PersonID CHAR(20) ) 
BEGIN
  DECLARE @CertificateNumber CHAR(13);
  DECLARE @StartDate DATE;
  DECLARE @EndDate DATE;
  DECLARE @CISType CHAR(10);
  DECLARE @ActingFor CHAR(26);
  DECLARE @AuthUser CHAR(16);
  -- Get Details of CIS Attached Template
  SELECT "CertificateNumber","StartDate","EndDate","CISType","ActingFor","AuthUser" INTO @CertificateNumber,
    @StartDate,@EndDate,@CISType,@ActingFor,@AuthUser FROM "CISTemplate" KEY JOIN "Pay_Employee" WHERE "Pay_Employee"."PersonID" = @PersonID;
  IF @CISType IS NOT NULL THEN
    -- Delete any conflicting CIS Cards which have NOT been transferred
    DELETE FROM "CISCard"
      WHERE "PersonID" = @PersonID AND("EndDate" IS NULL OR "EndDate" >= @StartDate) AND "transferbatch" IS NULL;
    -- Mark as Deleted any conflicting CIS Cards which have been transferred
    UPDATE "CISCard" SET "Deleted" = 1,"WhenDeleted" = CURRENT TIMESTAMP,"DeletedBy" = "UserStaffID"
      WHERE "PersonID" = @PersonID AND "Deleted" = 0 AND("EndDate" IS NULL OR "EndDate" >= @StartDate) AND "transferbatch" IS NOT NULL;
    -- Insert New Card
    INSERT INTO "CISCard"( "CISCardID","PersonID","CertificateNumber","StartDate",
      "EndDate","CISType","ActingFor","AuthUser","WhenEntered","CreatedBy" ) VALUES
      ( "uniquekey"(@PersonID),@PersonID,@CertificateNumber,@StartDate,
      @EndDate,@CISType,@ActingFor,@AuthUser,CURRENT TIMESTAMP,"UserStaffID" ) 
  END IF
END
GO
 
COMMENT TO PRESERVE FORMAT ON PROCEDURE "pears"."InsertCISFromTemplate" IS 
{CREATE PROCEDURE InsertCISFromTemplate 
 
/* Application Maintained Function / Procedure - DO NOT EDIT*/
 
(IN @PersonID CHAR(20))
BEGIN
  DECLARE @CertificateNumber CHAR(13);
  DECLARE @StartDate DATE;
  DECLARE @EndDate DATE;
  DECLARE @CISType CHAR(10);
  DECLARE @ActingFor CHAR(26);
  DECLARE @AuthUser CHAR(16);
  -- Get Details of CIS Attached Template
  SELECT CertificateNumber,StartDate,EndDate,CISType,ActingFor,AuthUser INTO @CertificateNumber,
    @StartDate,@EndDate,@CISType,@ActingFor,@AuthUser FROM CISTemplate KEY JOIN Pay_Employee WHERE Pay_Employee.PersonID = @PersonID;
  IF @CISType IS NOT NULL THEN
    -- Delete any conflicting CIS Cards which have NOT been transferred
    DELETE FROM CISCard WHERE
      PersonID = @PersonID AND(EndDate IS NULL OR EndDate >= @StartDate) AND transferbatch IS NULL;
    -- Mark as Deleted any conflicting CIS Cards which have been transferred
    UPDATE CISCard SET Deleted = 1,WhenDeleted = CURRENT TIMESTAMP,DeletedBy = UserStaffID WHERE
      PersonID = @PersonID AND Deleted = 0 AND(EndDate IS NULL OR EndDate >= @StartDate) AND transferbatch IS NOT NULL;
    -- Insert New Card
    INSERT INTO CISCard(CISCardID,PersonID,CertificateNumber,StartDate,
      EndDate,CISType,ActingFor,AuthUser,WhenEntered,CreatedBy) VALUES(
      uniquekey(@PersonID),@PersonID,@CertificateNumber,@StartDate,
      @EndDate,@CISType,@ActingFor,@AuthUser,CURRENT TIMESTAMP,UserStaffID)
  END IF
END
}