USP_PLANNEDGIFTADDITION_DELETE

          CREATE procedure dbo.USP_PLANNEDGIFTADDITION_DELETE
          (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier
          )
          as begin
            if exists (select ID from dbo.PLANNEDGIFTADDITIONREVENUE where ID = @ID) 
            begin
              raiserror('BBERR_PLANNEDGIFTADDITION_ASSOCIATEDREVENUE', 16, 1);
              return 1;
            end

            declare @contextCache varbinary(128);
            set @contextCache = CONTEXT_INFO();
            set CONTEXT_INFO @CHANGEAGENTID;

            /* Delete orphaned PLANNEDGIFTRECONCILE rows
             * A row becomes orphaned when the PLANNEDGIFTRECONCILE row for this planned gift addition references
             * a revenue record that is no longer associated with this planned gift addition.
             * NOTE:
             * This deletion is handled automatically for planned gifts using a Cascade Delete when the planned gift is deleted.
             * We could not add the same mechanism for planned gift additions because it creates a circular cascade path.
             */
            delete PLANNEDGIFTRECONCILE
            from dbo.PLANNEDGIFTRECONCILE
              inner join dbo.FINANCIALTRANSACTIONLINEITEM on PLANNEDGIFTRECONCILE.REVENUEID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
              left join dbo.INSTALLMENTSPLITPAYMENT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
              left join dbo.PLANNEDGIFTADDITIONREVENUE on INSTALLMENTSPLITPAYMENT.PLEDGEID = PLANNEDGIFTADDITIONREVENUE.REVENUEID
            where
              PLANNEDGIFTRECONCILE.PLANNEDGIFTADDITIONID = @ID and
              PLANNEDGIFTADDITIONREVENUE.ID is null and
              FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;

            --reset CONTEXT_INFO to previous value

            if not @contextCache is null
              set CONTEXT_INFO @contextCache;

            exec USP_PLANNEDGIFTADDITION_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;

            return 0;
          end