Create PROCEDURE spdCommissionReceipt
(

    @TransactionInternalNo INT

) 
AS 
SET NOCOUNT ON
begin

  DECLARE @ReturnValue INT

  IF @TransactionInternalNo IS NULL begin
    RETURN (-100)
  end

  BEGIN TRANSACTION

  -- Step 1: Commission Detail
  EXEC @ReturnValue = spdbCommissionDetail @TransactionInternalNo
  IF @ReturnValue <> 0 GOTO ERROR_HANDLER

  -- Step 2: Missing Policy
  EXEC @ReturnValue = spdbCommissionMissingPolicy @TransactionInternalNo
  IF @ReturnValue <> 0 GOTO ERROR_HANDLER

  -- Step 3: Recovery
  EXEC @ReturnValue = spdbCommissionRecovery @TransactionInternalNo
  IF @ReturnValue <> 0 GOTO ERROR_HANDLER

  -- Step 4: Deduction
  EXEC @ReturnValue = spdbCommissionDeduction @TransactionInternalNo
  IF @ReturnValue <> 0 GOTO ERROR_HANDLER

  -- Step 5: Delete Commission (Parent)
  DELETE FROM Commission
  WHERE CommissionInternalNo = @TransactionInternalNo

  IF @@ROWCOUNT <> 1 GOTO ERROR_HANDLER

  -- Step 6: Delete Transaction
  DELETE FROM InsuranceTransaction
  WHERE TransactionInternalNo = @TransactionInternalNo

  IF @@ROWCOUNT <> 1 GOTO ERROR_HANDLER

  COMMIT TRANSACTION
  RETURN (0)

  ERROR_HANDLER:
    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION

    RETURN (-1)
end
