Create PROCEDURE spdPolicyProposal (
@PolicyPropInternalNo int)
as
set nocount on
begin
  Declare @ReturnValue Int,
          @InternalNo Int,
          @DetailInternalNo Int
        

  begin Transaction


--Deleting PolicyProposalRider
  if exists (Select PolicyProposalRider.PolicyPropInternalNo From PolicyProposalRider
      Where PolicyProposalRider.PolicyPropInternalNo = @PolicyPropInternalNo) begin

    Execute @ReturnValue = spdPolicyProposalRider @PolicyPropInternalNo
    if @ReturnValue < 0 begin
      if @@TranCount = 1 begin
        Rollback
      end
      Return -1
    end
  end


-- Deleting Maturity
    
    Declare curMat Cursor For
    Select MaturityInternalNo from Maturity
    Join InsuranceTransaction On Maturity.MaturityInternalNo = InsuranceTransaction.TransactionInternalNo
    Where PolicyPropInternalNo = @PolicyPropInternalNo
    Order By InsuranceTransaction.Date Desc

    Open curMat
    Fetch curMat into @InternalNo
    While @@Fetch_Status <> -1 begin
      Execute @ReturnValue = spdMaturity @TransactionInternalNo = @InternalNo
      if @ReturnValue < 0 begin
        if @@TranCount = 1 begin
          Rollback Tran
        end
        close curMat
        Deallocate curMat
        Return(-1)
      end
      Fetch curMat into @InternalNo
    end
    close curMat
    Deallocate curMat

--Deleting CommissionDetail
   if exists(Select * from CommissionDetail Where PolicyPropInternalNo = @PolicyPropInternalNo) begin
     Delete CommissionDetail Where PolicyPropInternalNo = @PolicyPropInternalNo
     if @@RowCount <= 0 begin
       if @@TranCount = 1 begin
         Rollback Tran
       end
       Return(-1)
     end
   end

--Deleting CommissionRecovery
   if exists(Select * from CommissionRecovery Where PolicyPropInternalNo = @PolicyPropInternalNo) begin
     Delete CommissionRecovery Where PolicyPropInternalNo = @PolicyPropInternalNo
     if @@RowCount <= 0 begin
       if @@TranCount = 1 begin
         Rollback Tran
       end
       Return(-1)
     end
   end

-- Deleting PremiumDeposit
    Declare curPD Cursor For
    Select PremiumDepositInternalNo, PremiumDepositDetailInternalNo from PremiumDeposit
    Join InsuranceTransaction On PremiumDeposit.PremiumDepositInternalNo = InsuranceTransaction.TransactionInternalNo
    Where PolicyPropInternalNo = @PolicyPropInternalNo
    Order By InsuranceTransaction.Date Desc

    Open curPD
    Fetch curPD into @InternalNo, @DetailInternalNo

    While @@Fetch_Status <> -1 begin
      Execute @ReturnValue = spdPremiumDeposit @PremiumDepositInternalNo = @InternalNo,
                                               @PremiumDepositDetailInternalNo = @DetailInternalNo
      if @ReturnValue < 0 begin
        if @@TranCount = 1 begin
          Rollback Tran
        end
        close curPD
        Deallocate curPD
        Return(-1)
      end
      Fetch curPD into @InternalNo, @DetailInternalNo
    end
    close curPD
    Deallocate curPD

-- Deleting Conversion
   
   -- delete every thing from  conversion history
   Delete From conversionHistory where PolicyPropInternalNo = @PolicyPropInternalNo


    Declare curConv Cursor For
    Select ConversionInternalNo from Conversion
    Join InsuranceTransaction On Conversion.ConversionInternalNo = InsuranceTransaction.TransactionInternalNo
    Where PolicyPropInternalNo = @PolicyPropInternalNo
    Order By InsuranceTransaction.Date Desc

    Open curConv
    Fetch curConv into @InternalNo

    While @@Fetch_Status <> -1 begin
      Execute @ReturnValue = spdConversion @ConversionInternalNo = @InternalNo
      if @ReturnValue < 0 begin
        if @@TranCount = 1 begin
          Rollback Tran
        end
        close curConv
        Deallocate curConv
        Return(-1)
      end
      Fetch curConv into @InternalNo
    end
    close curConv
    Deallocate curConv

    --  deletion of InterestDue And InterestPayment Remaining
    Declare curInterestPayment Cursor For
    Select InterestPaymentInternalNo, InterestPaymentDetailInternalNo
      from InterestPayment
      Join InsuranceTransaction On InterestPayment.InterestPaymentInternalNo = InsuranceTransaction.TransactionInternalNo
     Where InterestPayment.PolicyPropInternalNo = @PolicyPropInternalNo
    Order By InsuranceTransaction.Date Desc

    Open curInterestPayment
    Fetch curInterestPayment into @InternalNo, @DetailInternalNo

    While @@Fetch_Status <> -1 begin
      Execute @ReturnValue = spdInterestPayment @InterestPaymentInternalNo = @InternalNo, @InterestPaymentDetailInternalNo = @DetailInternalNo
      if @ReturnValue < 0 begin
        if @@TranCount = 1 begin
          Rollback Tran
        end
        close curInterestPayment
        Deallocate curInterestPayment
        Return(-1)
      end
      Fetch curInterestPayment into @InternalNo, @DetailInternalNo
    end

    close curInterestPayment
    Deallocate curInterestPayment
   
-- Deleting Loan
    Declare curLoan Cursor For
    Select LoanInternalNo from Loan
    Join InsuranceTransaction On Loan.LoanInternalNo = InsuranceTransaction.TransactionInternalNo
    Where PolicyPropInternalNo = @PolicyPropInternalNo
    Order By InsuranceTransaction.Date Desc

    Open curLoan
    Fetch curLoan into @InternalNo

    While @@Fetch_Status <> -1 begin
      Execute @ReturnValue = spdLoan @LoanInternalNo = @InternalNo
      if @ReturnValue < 0 begin
        if @@TranCount = 1 begin
          Rollback Tran
        end
        close curLoan
        Deallocate curLoan
        Return(-1)
      end
      Fetch curLoan into @InternalNo
    end
    close curLoan
    Deallocate curLoan


    -- Proposal
    if exists(Select * from Proposal Where PolicyPropInternalNo = @PolicyPropInternalNo) begin
      update Proposal
      set
        PolicyPropInternalNo = Null
      where
        PolicyPropInternalNo = @PolicyPropInternalNo
      if @@rowCount <= 0 begin
        if @@TranCount = 1 begin
          rollback tran
        end
        return -1
      end
    end

   delete from PremiumDue
   where
   PolicyPropInternalNo = @PolicyPropInternalNo

   Delete From PolicyProposalUnit Where PolicyPropInternalNo = @PolicyPropInternalNo
   Delete from PolicyProposalHolder Where PolicyPropInternalNo = @PolicyPropInternalNo

  delete from PolicyProposal
   where PolicyPropInternalNo = @PolicyPropInternalNo

  if @@rowCount <> 1 begin
    if @@TranCount = 1 begin
      rollback tran
    end
    return -1
  end

  delete from InsuranceTransaction
  where TransactionInternalNo = @PolicyPropInternalNo

  if @@rowCount <> 1 begin
    if @@TranCount = 1 begin
      rollback tran
    end
    return -1
  end

  commit Transaction
end
