create procedure spRepostServiceTax (
@PolicyPropInternalNo int
)
as
set nocount on
begin

  Declare @PlanInternalNo int,
          @Duration int,
          @DepositDate DateTime,
          @PremiumDueInternalNo int,
          @DueDate DateTime,
          @PremiumDueServiceTax money,
          @PlanServiceTax money

  Declare tmpCursor Cursor Local STATIC For
   select PolicyProposal.PlanInternalNo, (DateDiff(dd, InsuranceTransaction.Date, PremiumDue.DueDate)/365)+1 Duraton,
          DepositInsuranceTransaction.Date DepositDate, PremiumDue.PremiumDueInternalNo, PremiumDue.DueDate,
          PremiumDue.ServiceTax
     From PremiumDue
     Join PolicyProposal on PremiumDue.PolicyPropInternalNo = PolicyProposal.PolicyPropInternalNo
     Join InsuranceTransaction on PremiumDue.PolicyPropInternalNo = InsuranceTransaction.TransactionInternalNo  
     left Join PremiumDeposit On PremiumDue.PremiumDepositInternalNo = PremiumDeposit.PremiumDepositInternalNo
     left Join InsuranceTransaction DepositInsuranceTransaction on PremiumDeposit.PolicyPropInternalNo = DepositInsuranceTransaction.TransactionInternalNo  
   Where  PremiumDue.PolicyPropInternalNo = @PolicyPropInternalNo

  Open tmpCursor
  Fetch tmpCursor into @PlanInternalNo, @Duration, @DepositDate, @PremiumDueInternalNo, @DueDate, @PremiumDueServiceTax

  While (@@FETCH_STATUS = 0) begin
    if (@DepositDate is not null) begin
      Select @PlanServiceTax = Tax 
        From PlanServiceTax
       Where PlanServiceTax.PlanInternalNo = @PlanInternalNo
         and @Duration between PlanServiceTax.FromDuration and PlanServiceTax.ToDuration
         and ((@DepositDate between PlanServiceTax.StartDate and PlanServiceTax.EndDate)
          or (@DepositDate >= PlanServiceTax.StartDate and PlanServiceTax.EndDate is null))
    end else begin
      Select @PlanServiceTax = Tax 
        From PlanServiceTax
       Where PlanServiceTax.PlanInternalNo = @PlanInternalNo
         and @Duration between PlanServiceTax.FromDuration and PlanServiceTax.ToDuration
         and PlanServiceTax.EndDate is null
    end

    if IsNull(@PlanServiceTax, 0) <> IsNull(@PremiumDueServiceTax, 0) begin
      Update PremiumDue Set ServiceTax = IsNull(@PlanServiceTax, 0) where PremiumDueInternalNo = @PremiumDueInternalNo and PolicyPropInternalNo = @PolicyPropInternalNo
    end
    Fetch tmpCursor into @PlanInternalNo, @Duration, @DepositDate, @PremiumDueInternalNo, @DueDate, @PremiumDueServiceTax
  end

  close tmpCursor
  deallocate tmpCursor
end

