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