USP_DATAFORMTEMPLATE_EDIT_REVENUESPLITDETAILSADJUST_4

CREATE procedure [dbo].[USP_DATAFORMTEMPLATE_EDIT_REVENUESPLITDETAILSADJUST_4]
(
  @ID uniqueidentifier,
  @CHANGEAGENTID uniqueidentifier,
  @DESIGNATIONID uniqueidentifier,
  @CATEGORYCODEID uniqueidentifier,
  @OTHERTYPECODEID uniqueidentifier,
  @OPPORTUNITYID uniqueidentifier,
  @ADJUSTMENTPOSTDATE datetime,
  @ADJUSTMENTDATE datetime,
  @ADJUSTMENTREASON nvarchar(300),
  @DECLINESGIFTAID bit,
  @ADJUSTMENTREASONCODEID uniqueidentifier,
  @ISGIFTAIDSPONSORSHIP bit
)
as
  set nocount on

  if @CHANGEAGENTID is null
    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

  set @ADJUSTMENTREASON = coalesce(@ADJUSTMENTREASON, '')

  declare @CURRENTDATE datetime
  set @CURRENTDATE = getdate()

  -- Validate that the all the values passed in apply for the application code

  declare @REVENUEID uniqueidentifier, @APPLICATIONCODE tinyint
  select
    @REVENUEID = REVENUESPLIT.FINANCIALTRANSACTIONID,
    @APPLICATIONCODE = REVENUESPLIT_EXT.APPLICATIONCODE
  from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
  inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
  where REVENUESPLIT.ID = @ID
    and REVENUESPLIT.TYPECODE <> 1
    and REVENUESPLIT.DELETEDON is null

  -- Check GL business rule for this account system and set to 'Do not post' if needed.

  declare @POSTSTATUSCODE tinyint;
  declare @PDACCOUNTSYSTEMID uniqueidentifier;
  set @POSTSTATUSCODE = 1;
  select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID from dbo.PDACCOUNTSYSTEMFORREVENUE where ID = @REVENUEID;
  if dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID) = 0
  begin
    set @POSTSTATUSCODE = 2  -- Do not post

    set @ADJUSTMENTPOSTDATE = null
  end            

  if @DESIGNATIONID is null and 
    (@APPLICATIONCODE = 0 or -- Donation

    @APPLICATIONCODE = 4) -- Other

  begin
    raiserror('DESIGNATIONREQUIRED', 13, 1)
    return 1
  end

  if @OTHERTYPECODEID is not null and @APPLICATIONCODE <> 4
  begin
    raiserror('OTHERTYPECODENOTVALID', 13, 1)
    return 1                        
  end

  if @OTHERTYPECODEID is null and @APPLICATIONCODE = 4
  begin
    raiserror('OTHERTYPECODEREQUIRED', 13, 1)
    return 1                        
  end

  if @OPPORTUNITYID is not null and @APPLICATIONCODE not in (0,2,6,8)
  begin
    raiserror('OPPORTUNITYCODENOTVALID', 13, 1)
    return 1                        
  end

  -- Verify the transaction has already been posted

  if not exists (select 1 from dbo.REVENUEPOSTED where ID = @REVENUEID)
  begin
    raiserror('TRANSACTIONMUSTBEPOSTED', 13, 1)
    return 1
  end

  -- Verify the designation is unique for this revenue

  if (@APPLICATIONCODE = 0 or @APPLICATIONCODE = 4) and exists (
    select 1 
    from dbo.REVENUESPLIT 
    where REVENUEID = @REVENUEID 
    and DESIGNATIONID = @DESIGNATIONID
    and APPLICATIONCODE = @APPLICATIONCODE
    and ID <> @ID
  )
  begin
    raiserror('REVENUESPLITDESIGNATIONNOTUNIQUE', 13, 1)
    return 1
  end

  --check to see if designation changed, if so re-receipt, re-acknowledge and adjust (if posted)

  declare @CURRENTDESIGNATIONID uniqueidentifier, @CURRENTCATEGORYID uniqueidentifier
  select @CURRENTDESIGNATIONID = DESIGNATIONID from dbo.REVENUESPLIT_EXT where ID = @ID
  select @CURRENTCATEGORYID = GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY where ID = @ID

  declare @DESIGNATIONCHANGED bit, @CATEGORYCHANGED bit, @ALREADYADJUSTED bit
  if (@CURRENTDESIGNATIONID is null and @DESIGNATIONID is null) or @CURRENTDESIGNATIONID = @DESIGNATIONID
    set @DESIGNATIONCHANGED = 0
  else
    set @DESIGNATIONCHANGED = 1

  if (@CURRENTCATEGORYID is null and @CATEGORYCODEID is null) or @CURRENTCATEGORYID = @CATEGORYCODEID
    set @CATEGORYCHANGED = 0
  else
    set @CATEGORYCHANGED = 1

  if exists (select 1 from dbo.ADJUSTMENT where REVENUEID = @REVENUEID and POSTSTATUSCODE = 1)
    set @ALREADYADJUSTED = 1
  else
    set @ALREADYADJUSTED = 0

  declare @ADJUST bit
  if (@DESIGNATIONCHANGED = 1 or @CATEGORYCHANGED = 1 or @ALREADYADJUSTED = 1) and 
    exists (select 1 from dbo.REVENUEPOSTED where REVENUEPOSTED.ID = @REVENUEID)
  begin
    set @ADJUST = 1

    -- Verify the adjustment dates are set

    if @ADJUSTMENTPOSTDATE is null and @POSTSTATUSCODE <> 2
    begin
      raiserror('ADJUSTMENTPOSTDATEREQUIRED', 13, 1)
      return 1
    end

    if @ADJUSTMENTDATE is null
    begin
      raiserror('ADJUSTMENTDATEREQUIRED', 13, 1)
      return 1
    end
  end
  else
    set @ADJUST = 0

  begin try
    declare @ADJUSTMENTID uniqueidentifier;
    declare @STOCKSALEADJUSTMENTIDS xml;
    declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;
    declare @PROPERTYDETAILCOUNT int;
    declare @PAYMENTMETHODCODE int;
    declare @REVENUEPAYMENTMETHODID uniqueidentifier;
    declare @OLDGIFTAIDQUALIFICATIONSTATUS nvarchar(30);
    declare @GIFTINKINDSALEADJUSTMENTIDS xml;

    set @OLDGIFTAIDQUALIFICATIONSTATUS = dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(@ID);

    set @PROPERTYDETAILCOUNT = 0;

    if @ADJUST = 1
    begin
      if @ADJUSTMENTREASONCODEID is null
        raiserror('BBERR_ADJUSTMENTREASONCODEIDREQUIRED', 13, 1)

      select 
        @PAYMENTMETHODCODE = PAYMENTMETHODCODE,
        @REVENUEPAYMENTMETHODID = ID
      from 
        dbo.REVENUEPAYMENTMETHOD 
      where 
        REVENUEID = @REVENUEID;

      -- Save the revenue adjustment

      exec dbo.USP_SAVE_ADJUSTMENT @REVENUEID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, 
        @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID, @POSTSTATUSCODE

      declare @DEPOSITID uniqueidentifier;
      declare @DEPOSITPOSTDATE date;

      select 
        @DEPOSITID = BADP.DEPOSITID
        ,@DEPOSITPOSTDATE = DEPOSIT.POSTDATE
      from
        dbo.BANKACCOUNTDEPOSITPAYMENT BADP
        inner join dbo.FINANCIALTRANSACTION DEPOSIT on DEPOSIT.ID = BADP.ID
      where BADP.ID = @REVENUEID

      select @PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
      from dbo.PROPERTYDETAIL 
      where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0;


      /* If sold stock has been posted, log stock detail adjustment */
      if @PAYMENTMETHODCODE = 4 and exists(select ID from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID)
      begin
        exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, 
          @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @STOCKSALEADJUSTMENTIDS output, @ADJUSTMENTREASONCODEID, @POSTSTATUSCODE;
      end

      /* If sold property has been posted, log property detail adjustment */
      else if (@PAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0) 
      begin
        exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @PROPERTYDETAILADJUSTMENTID output, 
          @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @ADJUSTMENTREASONCODEID, @POSTSTATUSCODE;
      end

      /* If sold gift-in-kind has been posted, log gift-in-kind detail adjustment */
      if @PAYMENTMETHODCODE = 6 and exists(select ID from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID)
      begin
        exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, 
          @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @GIFTINKINDSALEADJUSTMENTIDS output, @ADJUSTMENTREASONCODEID, @POSTSTATUSCODE;
      end

      declare @GIFTFEEADJUSTMENTID uniqueidentifier;
      if exists(select 1 from dbo.REVENUESPLITGIFTFEE GF inner join dbo.FINANCIALTRANSACTIONLINEITEM LI on LI.ID = GF.ID where LI.FINANCIALTRANSACTIONID = @REVENUEID)
      begin
        exec dbo.USP_SAVE_GIFTFEEADJUSTMENT @REVENUEID, @GIFTFEEADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, 
          @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, default, @ADJUSTMENTREASONCODEID;
      end

      declare @AUCTIONPURCHASEADJUSTMENTID uniqueidentifier;
      /*Auction purchase -- log adjustment*/

            if exists(select 1 from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID and REVENUESPLIT_EXT.TYPECODE = 12 and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1 and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null)
      begin
                exec dbo.USP_SAVE_AUCTIONPURCHASEADJUSTMENT @REVENUEID = @REVENUEID, @ADJUSTMENTID = @AUCTIONPURCHASEADJUSTMENTID output, @CHANGEAGENTID = @CHANGEAGENTID,
                    @CHANGEDATE = @CURRENTDATE, @DATE = @ADJUSTMENTDATE, @POSTDATE = @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON = @ADJUSTMENTREASON, 
                    @ADJUSTMENTREASONCODEID = @ADJUSTMENTREASONCODEID

        update dbo.FINANCIALTRANSACTIONLINEITEM set
          DELETEDON = null
        where FINANCIALTRANSACTIONID = @REVENUEID 
          and FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @AUCTIONPURCHASEADJUSTMENTID
          and TYPECODE != 1
      end
    end

    if @DESIGNATIONCHANGED = 1
    begin
      exec dbo.USP_REVENUE_UPDATERERECEIPTS @REVENUEID, @CHANGEAGENTID, @CURRENTDATE

      exec dbo.USP_REVENUELETTER_SETREACKNOWLEDGEMENTS @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;
    end


    update dbo.REVENUESPLIT_EXT set
      DESIGNATIONID = @DESIGNATIONID,
      CHANGEDBYID = @CHANGEAGENTID,
      DATECHANGED = @CURRENTDATE
    where
      ID = @ID

    if @DESIGNATIONCHANGED = 1
    begin
      -- update campaigns

      if @APPLICATIONCODE = 0  -- Donation

      begin
        exec dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS @REVENUEID = @REVENUEID, @OPPORTUNITYID = @OPPORTUNITYID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CURRENTDATE
      end
      else if @APPLICATIONCODE = 4  -- Other

      begin
        exec dbo.USP_REVENUE_CLEARANDADDCAMPAIGNS @REVENUEID = @REVENUEID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CURRENTDATE
      end
    end

    if @CATEGORYCODEID is null
      exec dbo.USP_REVENUECATEGORY_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
    else
    begin
      update dbo.REVENUECATEGORY set
        GLREVENUECATEGORYMAPPINGID = @CATEGORYCODEID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @ID

      if @@ROWCOUNT = 0
        insert into dbo.REVENUECATEGORY (ID, GLREVENUECATEGORYMAPPINGID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (@ID, @CATEGORYCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
    end

    if @OTHERTYPECODEID is null
      exec dbo.USP_REVENUESPLITOTHER_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
    else
    begin
      update dbo.REVENUESPLITOTHER set
        OTHERTYPECODEID = @OTHERTYPECODEID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @ID

      if @@ROWCOUNT = 0
        insert into dbo.REVENUESPLITOTHER (ID, OTHERTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (@ID, @OTHERTYPECODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
    end

    if @OPPORTUNITYID is null
      exec dbo.USP_REVENUEOPPORTUNITY_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID
    else
    begin
      exec dbo.USP_REVENUESPLIT_PULLSOLICITORSFROMOPPORTUNITY @ID, @OPPORTUNITYID, @CHANGEAGENTID, @CURRENTDATE

      update dbo.REVENUEOPPORTUNITY set
        OPPORTUNITYID = @OPPORTUNITYID,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
      where
        ID = @ID

      if @@ROWCOUNT = 0
        insert into dbo.REVENUEOPPORTUNITY (ID, OPPORTUNITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
        values (@ID, @OPPORTUNITYID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE)
    end

    exec dbo.USP_REVENUESPLIT_UPDATEDECLINESGIFTAID @REVENUEID = @REVENUEID,
      @APPLICATIONCODE = @APPLICATIONCODE,
      @REVENUESPLITID = @ID,
      @DESIGNATIONID = @DESIGNATIONID,
      @DECLINESGIFTAID = @DECLINESGIFTAID,
      @CHANGEAGENTID = @CHANGEAGENTID,
      @CURRENTDATE = @CURRENTDATE,
      @ISGIFTAIDSPONSORSHIP = @ISGIFTAIDSPONSORSHIP

    -- Clear the user-defined gl distributions if the designation or revenue category has changed

    -- Do this regardless of whether gift is posted or not

    if @DESIGNATIONCHANGED = 1 or @CATEGORYCHANGED = 1
    begin
      declare @contextCache varbinary(128);

      --cache current context information

      set @contextCache = CONTEXT_INFO();

      -- Clear GL

      delete from dbo.REVENUEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;
      delete from dbo.STOCKSALEGLDISTRIBUTION where STOCKSALEGLDISTRIBUTION.REVENUEID = @REVENUEID and OUTDATED = 0;
      delete from dbo.PROPERTYDETAILGLDISTRIBUTION where PROPERTYDETAILGLDISTRIBUTION.REVENUEID = @REVENUEID and OUTDATED = 0;
      delete from dbo.GIFTINKINDSALEGLDISTRIBUTION where GIFTINKINDSALEGLDISTRIBUTION.REVENUEID = @REVENUEID and OUTDATED = 0;
      delete from dbo.AUCTIONPURCHASEGLDISTRIBUTION where REVENUEPURCHASEID =  @REVENUEID and OUTDATED = 0;
      delete from dbo.GIFTFEEGLDISTRIBUTION where REVENUEID = @REVENUEID and OUTDATED = 0;

      set CONTEXT_INFO @CHANGEAGENTID;

      --reset CONTEXT_INFO to previous value

      if not @contextCache is null
        set CONTEXT_INFO @contextCache;    

      -- Add new GL distributions

      declare @DONOTPOST bit
      select @DONOTPOST = case POSTSTATUSCODE when 3 then 1 else 0 end from dbo.FINANCIALTRANSACTION where ID = @REVENUEID
      if @POSTSTATUSCODE = 2
        set @DONOTPOST = 1;

      if @DONOTPOST = 0
      begin    
        -- Add new GL distributions

        exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

        -- Add new stock detail GL distributions

        exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

        -- Add new property detail GL distributions

        exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

        -- Add new gift-in-kind detail GL distributions

        exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

        -- Add new gift fee GL distributions

        exec dbo.USP_SAVE_GIFTFEEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

        -- Add new auction purchase GL distributions

                if (@AUCTIONPURCHASEADJUSTMENTID is not null)
                    exec dbo.USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION @REVENUEID = @REVENUEID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CURRENTDATE
      end

      --Replace gift aid GL distributions

      if (@OLDGIFTAIDQUALIFICATIONSTATUS = dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(@ID))
        and (exists(select 1 from dbo.GIFTAIDGLDISTRIBUTION where REVENUESPLITGIFTAIDID = @ID))
      begin
        exec dbo.USP_REVENUESPLITGIFTAID_UPDATEGLDISTRIBUTIONS @ID, @CHANGEAGENTID, @CURRENTDATE, @DONOTPOST;
      end
    end

    /* add adjustment history information */
    if @ADJUST = 1
    begin
      if @ADJUSTMENTID is not null
        exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @REVENUEID, @CHANGEAGENTID, null, @ADJUSTMENTID;

      if @STOCKSALEADJUSTMENTIDS is not null
        exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @STOCKSALEADJUSTMENTIDS;

      if @PROPERTYDETAILADJUSTMENTID is not null
        exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;

      if @GIFTINKINDSALEADJUSTMENTIDS is not null
        exec dbo.USP_ADJUSTMENTHISTORY_GIFTINKIND_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @GIFTINKINDSALEADJUSTMENTIDS;
    end

    if @DEPOSITID is not null
    begin
      if @DEPOSITPOSTDATE = isnull(@ADJUSTMENTPOSTDATE, @DEPOSITPOSTDATE)
      begin
        if (@DESIGNATIONCHANGED = 1 or @CATEGORYCHANGED = 1)
          exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @REVENUEID, @DEPOSITID, @CHANGEAGENTID, @CURRENTDATE;
      end
      else
      begin
        update dbo.BANKACCOUNTDEPOSITPAYMENT set DEPOSITID = null where ID = @REVENUEID;
      end
    end

  end try
  begin catch
    exec dbo.USP_RAISE_ERROR;
    return 1;
  end catch

  return 0;