Create procedure spdPremiumDeposit(
 @PremiumDepositInternalNo int = null,
 @PremiumDepositDetailInternalNo int = Null)
 as
 Set nocount on
 begin
  Declare @ReturnValue Int

  Begin Transaction                              

-- Delete CommissionDetail
  if exists(Select * From CommissionDetail
/*          Join PremiumDue On CommissionDetail.PremiumDueInternalNo = PremiumDue.PremiumDueInternalNo */
            Join PremiumDue On CommissionDetail.DueDate = PremiumDue.DueDate
                 And CommissionDetail.PolicyPropInternalNo = PremiumDue.PolicyPropInternalNo
            Join PremiumDeposit On PremiumDue.PremiumDepositInternalNo = PremiumDeposit.PremiumDepositInternalNo  and PremiumDue.PremiumDepositDetailInternalNo = PremiumDeposit.PremiumDepositDetailInternalNo
            Where PremiumDeposit.PremiumDepositInternalNo = @PremiumDepositInternalNo And PremiumDeposit.PremiumDepositDetailInternalNo = @PremiumDepositDetailInternalNo) begin

    Delete  CommissionDetail From CommissionDetail
/*          Join PremiumDue On CommissionDetail.PremiumDueInternalNo = PremiumDue.PremiumDueInternalNo */
            Join PremiumDue On CommissionDetail.DueDate = PremiumDue.DueDate
                 And CommissionDetail.PolicyPropInternalNo = PremiumDue.PolicyPropInternalNo
            Join PremiumDeposit On PremiumDue.PremiumDepositInternalNo = PremiumDeposit.PremiumDepositInternalNo  and PremiumDue.PremiumDepositDetailInternalNo = PremiumDeposit.PremiumDepositDetailInternalNo
            Where PremiumDeposit.PremiumDepositInternalNo = @PremiumDepositInternalNo And PremiumDeposit.PremiumDepositDetailInternalNo = @PremiumDepositDetailInternalNo

    if @@RowCount < 0 begin
      if @@TranCount = 1 begin
        Rollback Tran
      end
      Return(-1)
    end
  end


-- Delete CommissionRecovery
  if exists(Select * From CommissionRecovery
/*            Join PremiumDue On CommissionRecovery.PremiumDueInternalNo = PremiumDue.PremiumDueInternalNo */
            Join PremiumDue On CommissionRecovery.DueDate = PremiumDue.DueDate
                 And CommissionRecovery.PolicyPropInternalNo = PremiumDue.PolicyPropInternalNo
            Join PremiumDeposit On PremiumDue.PremiumDepositInternalNo = PremiumDeposit.PremiumDepositInternalNo  and PremiumDue.PremiumDepositDetailInternalNo = PremiumDeposit.PremiumDepositDetailInternalNo
            Where PremiumDeposit.PremiumDepositInternalNo = @PremiumDepositInternalNo And PremiumDeposit.PremiumDepositDetailInternalNo = @PremiumDepositDetailInternalNo) begin


    Delete  CommissionRecovery From CommissionRecovery
/*            Join PremiumDue On CommissionRecovery.PremiumDueInternalNo = PremiumDue.PremiumDueInternalNo */
            Join PremiumDue On CommissionRecovery.DueDate = PremiumDue.DueDate
                 And CommissionRecovery.PolicyPropInternalNo = PremiumDue.PolicyPropInternalNo
            Join PremiumDeposit On PremiumDue.PremiumDepositInternalNo = PremiumDeposit.PremiumDepositInternalNo  and PremiumDue.PremiumDepositDetailInternalNo = PremiumDeposit.PremiumDepositDetailInternalNo
            Where PremiumDeposit.PremiumDepositInternalNo = @PremiumDepositInternalNo And PremiumDeposit.PremiumDepositDetailInternalNo = @PremiumDepositDetailInternalNo


    if @@RowCount < 0 begin
      if @@TranCount = 1 begin
        Rollback Tran
      end
      Return(-1)
    end
  end

-- Delete CommissionDeduction
  if exists (Select CommissionDetail.CommissionDetailInternalNo from CommissionDeduction
             Left Join CommissionDetail On CommissionDeduction.CommissionInternalNo = CommissionDetail.CommissionInternalNo
             Where CommissionDetail.CommissionDetailInternalNo is Null) begin

    Delete CommissionDeduction From CommissionDeduction
             Left Join CommissionDetail On CommissionDeduction.CommissionInternalNo = CommissionDetail.CommissionInternalNo
             Where CommissionDetail.CommissionDetailInternalNo is Null

    if @@RowCount < 0 begin
      if @@TranCount = 1 begin
        Rollback Tran
      end
      Return(-1)
    end
  end

-- Delete Commission
  if exists (Select CommissionDetail.CommissionDetailInternalNo from Commission
             Left Join CommissionDetail On Commission.CommissionInternalNo = CommissionDetail.CommissionInternalNo
             Left Join CommissionMissingPolicy On Commission.CommissionInternalNo = CommissionMissingPolicy.CommissionInternalNo
             Where CommissionDetail.CommissionDetailInternalNo is Null 
               and CommissionMissingPolicy.MissingPolicyInternalNo is null ) begin

    Delete Commission From Commission
             Left Join CommissionDetail On Commission.CommissionInternalNo = CommissionDetail.CommissionInternalNo
             Where CommissionDetail.CommissionDetailInternalNo is Null
 
    if @@RowCount < 0 begin
      if @@TranCount = 1 begin
        Rollback Tran
      end
      Return(-1)
    end
  end


-- Update PremiumDue
  if exists(Select * from PremiumDue 
            where PremiumDepositInternalNo = @PremiumDepositInternalNo
                  And
                  PremiumDepositDetailInternalNo =@PremiumDepositDetailInternalNo) begin

    Execute @ReturnValue = spdPremiumDepositPremiumDue
      @PremiumDepositInternalNo = @PremiumDepositInternalNo,
      @PremiumDepositDetailInternalNo = @PremiumDepositDetailInternalNo

    if @ReturnValue < 0 begin
      if @@TranCount = 1 begin
        Rollback Tran
      end
      Return(-1)
    end
  end

--Delete PolicyProposalUnit
  Delete PolicyProposalUnit 
   Where PremiumDepositInternalNo = @PremiumDepositInternalNo
     and PremiumDepositDetailInternalNo = @PremiumDepositDetailInternalNo

--Delete Premium Deposit
  if exists(Select * from PremiumDeposit Where PremiumDepositInternalNo = @PremiumDepositInternalNo
                         and PremiumDepositDetailInternalNo = @PremiumDepositDetailInternalNo) begin
    if Not exists(Select * from PremiumDue 
            where PremiumDepositInternalNo = @PremiumDepositInternalNo
                  And
                  PremiumDepositDetailInternalNo =@PremiumDepositDetailInternalNo) begin
      Delete PremiumDeposit
      Where PremiumDepositInternalNo = @PremiumDepositInternalNo
        and PremiumDepositDetailInternalNo =@PremiumDepositDetailInternalNo 
      if @@rowCount <> 1 begin
        if @@TranCount = 1 begin
          rollback tran
        end
        return -1
      end
    end
  end

  if not exists(Select * from PremiumDeposit where PremiumDepositInternalNo = @PremiumDepositInternalNo) 
   and exists(Select * from InsuranceTransaction where TransactionInternalNo = @PremiumDepositInternalNo) begin
    Execute @ReturnValue = spdInsuranceTransaction @TransactionInternalNo = @PremiumDepositInternalNo     
    if @ReturnValue < 0 begin
      if @@TranCount = 1 begin
        Rollback Tran
      end
      Return(-1)
    end
  end
  Commit Transaction
end


