create procedure SetStatus 
as
set nocount on
begin

  Begin Transaction

  Declare @FirstUnpaidPremium DateTime,
          @CommencementDate DateTime,
          @DueDate DateTime,
          @PolicyPropInternalNo int,
          @Mode Char(1),
          @Term Smallint,
          @Status char(1),
          @PlanInternalNo int,
          @DeferDate DateTime,
          @BirthDate DateTime,
          @EntryAge Int,
          @MaturityType Char(2)

  Select @Status = null

  Declare curSetStatus Cursor Local For 

  Select PolicyProposal.PolicyPropInternalNo, InsuranceTransaction.Date, PolicyProposal.Term, 
          PolicyProposal.Mode, PolicyProposal.PlanInternalNo, Member.BirthDate, Maturity.MaturityType,
          Min(Premiumdue.DueDate) DueDate
     From PolicyProposal 
     Join InsuranceTransaction On PolicyProposal.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalno
     Join PremiumDue On PolicyProposal.PolicyPropInternalNo = PremiumDue.PolicyPropInternalNo
     Join Member On PolicyProposal.HolderInternalNo = Member.MemberInternalNo
     Left Join Maturity On PolicyProposal.PolicyPropInternalNo = Maturity.PolicyPropInternalNo
    where PremiumDue.PremiumAmount > PremiumDue.AdjustAmount
      and PolicyProposal.PolicyStatus in ('I', 'E')
      --and PolicyProposal.PolicyPropInternalNo in (19205,19205,19206,19207,19208,19209,19500,20378,19212,19213 )
    Group By PolicyProposal.PolicyPropInternalNo, InsuranceTransaction.Date, PolicyProposal.Term, 
          PolicyProposal.Mode, PolicyProposal.PlanInternalNo, Member.BirthDate, Maturity.MaturityType
union
   Select PolicyProposal.PolicyPropInternalNo, InsuranceTransaction.Date, PolicyProposal.Term, 
          PolicyProposal.Mode, PolicyProposal.PlanInternalNo, Member.BirthDate, Maturity.MaturityType, null
     From PolicyProposal 
     Join InsuranceTransaction On PolicyProposal.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalno
     Join Member On PolicyProposal.HolderInternalNo = Member.MemberInternalNo
     Left Join Maturity On PolicyProposal.PolicyPropInternalNo = Maturity.PolicyPropInternalNo
    Where PolicyProposal.PolicyPropInternalNo not in (Select distinct PolicyPropInternalNo from PremiumDue)
     and PolicyProposal.PolicyStatus not in ('C', 'S') -- 20/04/2024 added this line as when policy is surrendered then also status changed to 'F'
union
   Select PolicyProposal.PolicyPropInternalNo, InsuranceTransaction.Date, PolicyProposal.Term, 
          PolicyProposal.Mode, PolicyProposal.PlanInternalNo, Member.BirthDate, Maturity.MaturityType, null
     From PolicyProposal 
     Join InsuranceTransaction On PolicyProposal.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalno
     Join Member On PolicyProposal.HolderInternalNo = Member.MemberInternalNo
     Join Maturity On PolicyProposal.PolicyPropInternalNo = Maturity.PolicyPropInternalNo
     and Maturity.MaturityType in ('SU', 'DT', 'AD') 
union
   Select PolicyProposal.PolicyPropInternalNo, InsuranceTransaction.Date, PolicyProposal.Term, 
          PolicyProposal.Mode, PolicyProposal.PlanInternalNo, Member.BirthDate, Maturity.MaturityType, null
     From PolicyProposal 
     Join InsuranceTransaction On PolicyProposal.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalno
     Join Member On PolicyProposal.HolderInternalNo = Member.MemberInternalNo
     Left Join Maturity On PolicyProposal.PolicyPropInternalNo = Maturity.PolicyPropInternalNo
     Left Join (Select PolicyPropInternalNo, Min(DueDate) DueDate From PremiumDue Where 
                    PremiumAmount > AdjustAmount Group By PolicyPropInternalNo
            ) Temp1 on PolicyProposal.PolicyPropInternalno = Temp1.PolicyPropInternalNo
    Where DateAdd(mm, (PolicyProposal.PremiumPayiTerm * 12) - 
           Case PolicyProposal.Mode
             When 'P' then 12
             When 'Y' then 12
             When 'H' then 6
             When 'Q' then 3
             when 'M' then 1
             when 'E' then 1
             when 'S' then 1
           end, InsuranceTransaction.Date) <= getdate()
      and PolicyProposal.PolicyStatus in ('I', 'E')
      and Temp1.DueDate is null

  Select GetDate()

  Open curSetStatus  

 Fetch curSetStatus Into @PolicyPropInternalno, @CommencementDate, @Term, 
        @Mode, @PlanInternalNo, @BirthDate, @MaturityType, @FirstUnpaidPremium

  While @@Fetch_Status <> -1 begin
     
    if (@Mode = 'P') begin
      if (DateAdd (yy, @Term, @CommencementDate) > GetDate()) or (@Term = 0)  begin
        Select @Status = 'F'
      end else begin
        Select @Status = 'M'
      end
    end else
    if (@MaturityType = 'SU') begin
      select @Status = 'S'
    end else
    if (@MaturityType = 'DT' or @MaturityType = 'AD') begin
      select @Status ='C'
    end else begin
      if (not @FirstUnpaidPremium is null) begin
        if @PlanInternalNo in (165) begin
          if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) < 36 begin
            if (@Mode = 'Y') or (@Mode = 'H') or (@Mode = 'Q') begin
              Select @DueDate = DateAdd(mm, 1, @FirstUnpaidPremium)
              if @DueDate < DateAdd(dd, 30, @FirstUnpaidPremium) begin
                Select @DueDate = DateAdd(dd, 30, @FirstUnpaidPremium)
              end
            end
            if (@Mode = 'M') or (@Mode = 'S') or (@Mode = 'E') begin
              Select @DueDate = DateAdd(dd, 15, @FirstUnpaidPremium)
            end
          end else begin
            Select @DueDate = DateAdd(mm, 12, @FirstUnpaidPremium)
          end
        end else
        if @PlanInternalNo in (174, 179, 184, 185) begin
          if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) < 24 begin
            if (@Mode = 'Y') or (@Mode = 'H') or (@Mode = 'Q') begin
              Select @DueDate = DateAdd(mm, 1, @FirstUnpaidPremium)
              if @DueDate < DateAdd(dd, 30, @FirstUnpaidPremium) begin
                Select @DueDate = DateAdd(dd, 30, @FirstUnpaidPremium)
              end
            end
            if (@Mode = 'M') or (@Mode = 'S') or (@Mode = 'E') begin
              Select @DueDate = DateAdd(dd, 15, @FirstUnpaidPremium)
            end
          end else begin
            Select @DueDate = DateAdd(mm, 24, @FirstUnpaidPremium)
          end
        end else
        if @PlanInternalNo in (128, 91, 160) begin
          if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) < 24 begin
            if (@Mode = 'Y') or (@Mode = 'H') or (@Mode = 'Q') begin
              Select @DueDate = DateAdd(mm, 1, @FirstUnpaidPremium)
              if @DueDate < DateAdd(dd, 30, @FirstUnpaidPremium) begin
                Select @DueDate = DateAdd(dd, 30, @FirstUnpaidPremium)
              end
            end
            if (@Mode = 'M') or (@Mode = 'S') or (@Mode = 'E') begin
              Select @DueDate = DateAdd(dd, 15, @FirstUnpaidPremium)
            end
          end else begin
            Select @DueDate = DateAdd(mm, 36, @FirstUnpaidPremium)
          end
        end else
        if @PlanInternalNo in (153,164,177, 190) begin
          Select @DueDate = DateAdd(dd, 15, @FirstUnpaidPremium)
        end else
        if @PlanInternalNo in (102, 109, 113, 159) begin
          Select @EntryAge = DateDiff (yy, @BirthDate, @CommencementDate)
          if (@EntryAge >= 1) and (@EntryAGe <= 4) begin
            Select @DeferDate = DateAdd (yy, 7-@EntryAge, @CommencementDate)
          end else
          if (@EntryAge >= 5) and (@EntryAGe <= 10) begin
            Select @DeferDate = DateAdd (yy, 2, @CommencementDate)
          end else
          if (@EntryAge = 11) begin
            Select @DeferDate = DateAdd(yy, 1, @CommencementDate)
          end else begin
            Select @DeferDate = @CommencementDate
          end
             
          if @DeferDate > GetDate() begin
            if (@Mode = 'Y') or (@Mode = 'H') or (@Mode = 'Q') begin
              Select @DueDate = DateAdd(mm, 1, @FirstUnpaidPremium)
              if @DueDate < DateAdd(dd, 30, @FirstUnpaidPremium) begin
                Select @DueDate = DateAdd(dd, 30, @FirstUnpaidPremium)
              end
            end
          if (@Mode = 'M') or (@Mode = 'S') or (@Mode = 'E') begin
              Select @DueDate = DateAdd(dd, 15, @FirstUnpaidPremium)
            end
          end else begin
            if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) < 36 begin
              if (@Mode = 'Y') or (@Mode = 'H') or (@Mode = 'Q') begin
                Select @DueDate = DateAdd(mm, 1, @FirstUnpaidPremium)
                if @DueDate < DateAdd(dd, 30, @FirstUnpaidPremium) begin
                  Select @DueDate = DateAdd(dd, 30, @FirstUnpaidPremium)
                end
              end
              if (@Mode = 'M') or (@Mode = 'S') or (@Mode = 'E') begin
                Select @DueDate = DateAdd(dd, 15, @FirstUnpaidPremium)
              end
            end
            if (DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) >= 36) begin
              Select @DueDate = DateAdd(mm, 6, @FirstUnpaidPremium)
            end
          end
        end else begin
          if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) < 36 begin
            if (@Mode = 'Y') or (@Mode = 'H') or (@Mode = 'Q') begin
              Select @DueDate = DateAdd(mm, 1, @FirstUnpaidPremium)
              if @DueDate < DateAdd(dd, 30, @FirstUnpaidPremium) begin
                Select @DueDate = DateAdd(dd, 30, @FirstUnpaidPremium)
              end
            end
            if (@Mode = 'M') or (@Mode = 'S') or (@Mode = 'E') begin
              Select @DueDate = DateAdd(dd, 15, @FirstUnpaidPremium)
            end
          end else
          if (DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) >= 36) begin
            Select @DueDate = DateAdd(mm, 6, @FirstUnpaidPremium)
          end
        end 
        
        if @DueDate >= GetDate() begin
          Select @Status = 'I'
        end else begin
          if @CommencementDate < 'April 1, 1973' begin
            if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) >= 24 begin
              if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) >= 60 begin
                if (DateDiff(mm, @FirstUnpaidPremium, GetDate()) <= 12) begin
                  Select @Status = 'E'
                end else begin
                  Select @Status = 'R'
                end
              end else begin
                Select @Status = 'R'
              end
            end else begin
              Select @Status = 'L'
            end
          end else begin
            if @PlanInternalno <> 153 begin
              if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) >= 36 begin
                if DateDiff(mm, @CommencementDate, @FirstUnpaidPremium) >= 60 begin
                  if (DateDiff(mm, @FirstUnpaidPremium, GetDate()) <= 12) begin
                    Select @Status = 'E'
                  end else begin
                    Select @Status = 'R'
                  end
                end else begin
                  Select @Status = 'R'
                end
              end else begin
                Select @Status = 'L'
              end
            end else begin
              select @Status = 'L'
            end
          end
        end
      end else begin
        if (DateAdd (yy, @Term, @CommencementDate) > GetDate()) or (@Term = 0) begin
          Select @Status = 'F'
        end else begin
          Select @Status = 'M'
        end
      end
    end
    if (not @Status is null) begin
      if (@Status <> 'I') begin
        Update PolicyProposal 
           Set PolicyStatus = @Status 
         Where PolicyPropInternalNo = @PolicyPropInternalNo
      end
    end
 Fetch curSetStatus Into @PolicyPropInternalno, @CommencementDate, @Term, 
          @Mode, @PlanInternalNo, @BirthDate, @MaturityType, @FirstUnpaidPremium
  end
  close curSetStatus
  Deallocate curSetStatus
  Commit Transaction
end





