USP_DATAFORMTEMPLATE_EDIT_REVENUESPLITDETAILS_2

          CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_REVENUESPLITDETAILS_2
          (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier,
            @DESIGNATIONID uniqueidentifier,
            @CATEGORYCODEID uniqueidentifier,
            @OTHERTYPECODEID uniqueidentifier,
            @OPPORTUNITYID uniqueidentifier,
            @DECLINESGIFTAID bit,
            @ISGIFTAIDSPONSORSHIP bit
          )
          as
            set nocount on

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

            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 = REVENUEID,
              @APPLICATIONCODE = APPLICATIONCODE
            from dbo.REVENUESPLIT
            where ID = @ID

            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 hasn't already been posted

            if exists (select 1 from dbo.REVENUEPOSTED where ID = @REVENUEID)
            begin
              raiserror('TRANSACTIONCANNOTBEPOSTED', 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 where ID = @ID
            select @CURRENTCATEGORYID = GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY where ID = @ID

            declare @DESIGNATIONCHANGED as bit, @CATEGORYCHANGED as 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

            begin try                
              declare @OLDGIFTAIDQUALIFICATIONSTATUS nvarchar(30);
              set @OLDGIFTAIDQUALIFICATIONSTATUS = dbo.UFN_GIFTAIDREVENUESPLIT_GETQUALIFICATIONSTATUS(@ID);

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

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


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

                  if (@APPLICATIONCODE = 0) 
                    begin
                      declare @SALESORDERITEMID uniqueidentifier = (select ID from dbo.SALESORDERITEMDONATION where REVENUESPLITID = @ID)
                      declare @DESIGNATIONNAME nvarchar(512) = (select NAME from dbo.DESIGNATION where ID = @DESIGNATIONID)

                      update dbo.SALESORDERITEMDONATION set
                        DESIGNATIONID = @DESIGNATIONID, 
                        DESIGNATIONNAME = @DESIGNATIONNAME,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      where ID = @SALESORDERITEMID 

                      update dbo.SALESORDERITEM set
                        [DESCRIPTION] = @DESIGNATIONNAME,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      where ID = @SALESORDERITEMID 

                      -- update campaigns

                      exec dbo.USP_PLEDGE_CLEARANDADDCAMPAIGNS @REVENUEID = @REVENUEID, @OPPORTUNITYID = @OPPORTUNITYID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CURRENTDATE

                      -- Bug 320593

                      --    update designation for payment associated with the order

                      update dbo.REVENUESPLIT_EXT set
                        DESIGNATIONID = @DESIGNATIONID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                      from dbo.FINANCIALTRANSACTIONLINEITEM
                      inner join dbo.REVENUESPLIT_EXT
                        on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                      where FINANCIALTRANSACTIONLINEITEM.SOURCELINEITEMID = @ID
                        and REVENUESPLIT_EXT.APPLICATIONCODE = 10;                    -- Order


                    end

                  else if @APPLICATIONCODE = 4  -- Other                    

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


                end
/* CMC
              update dbo.REVENUESPLIT_EXT set
                DESIGNATIONID = @DESIGNATIONID
              where
                ID = @ID
*/


              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)

                declare @REVENUEDATE datetime
                select @REVENUEDATE = DATE from dbo.REVENUE where ID = @REVENUEID

                exec dbo.USP_OPPORTUNITY_UPDATEACCEPTEDSTATUS @OPPORTUNITYID, @REVENUEDATE, @CHANGEAGENTID, @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 - Deleting from table instead of views

                --REVENUEGLDISTRIBUTION

                delete JE from  dbo.JOURNALENTRY JE
                inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                inner join  dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                where FTLI.POSTSTATUSCODE != 2 and JEX.OUTDATED = 0
                and JEX.TABLENAMECODE = 1 and FTLI.FINANCIALTRANSACTIONID = @REVENUEID;

                --AUCTIONPURCHASEGLDISTRIBUTION

                delete JE from  dbo.JOURNALENTRY JE
                inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                inner join  dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                where FTLI.POSTSTATUSCODE != 2 and JEX.OUTDATED = 0
                and JEX.TABLENAMECODE = 2 and JEX.REVENUEPURCHASEID = @REVENUEID;

                --GIFTFEEGLDISTRIBUTION and STOCKSALEGLDISTRIBUTION

                delete JE from dbo.JOURNALENTRY JE
                inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                inner join  dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                where FTLI.POSTSTATUSCODE != 2 and JEX.OUTDATED = 0
                and JEX.TABLENAMECODE in (8,11) and JEX.LOGICALREVENUEID = @REVENUEID;

                --Stock posted (Logic from STOCKSALEGLDISTRIBUTION delete trigger)

                if exists(select 1 from dbo.JOURNALENTRY JE
                inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                inner join dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                where FTLI.POSTSTATUSCODE = 2 and JEX.TABLENAMECODE = 11 and JEX.LOGICALREVENUEID = @REVENUEID)
                    update JOURNALENTRY_EXT set STOCKSALEID = null from dbo.JOURNALENTRY 
                        inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                        inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
                        where FTLI.POSTSTATUSCODE = 2 and JOURNALENTRY_EXT.TABLENAMECODE = 11 and JOURNALENTRY_EXT.LOGICALREVENUEID = @REVENUEID;

                --PROPERTYDETAILGLDISTRIBUTION

                delete JE from  dbo.JOURNALENTRY JE
                inner join  dbo.FINANCIALTRANSACTIONLINEITEM FTLI on JE.FINANCIALTRANSACTIONLINEITEMID = FTLI.ID
                inner join  dbo.JOURNALENTRY_EXT JEX on JE.ID = JEX.ID
                inner join  dbo.FINANCIALTRANSACTION FT on FT.ID = FTLI.FINANCIALTRANSACTIONID
                where FTLI.POSTSTATUSCODE != 2 and JEX.TABLENAMECODE = 10 and FTLI.TYPECODE != 1 and JEX.OUTDATED = 0
                and (JEX.LOGICALREVENUEID = @REVENUEID or (JEX.LOGICALREVENUEID is null and FT.PARENTID = @REVENUEID));

                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 = DONOTPOST from dbo.REVENUE where ID = @REVENUEID

                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 fee GL distributions

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

                  -- Add new auction purchase GL distributions

                  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.DELETEDON is null and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1)
                    exec dbo.USP_SAVE_AUCTIONPURCHASEGLDISTRIBUTION @REVENUEID = @REVENUEID, @CHANGEAGENTID = @CHANGEAGENTID, @CHANGEDATE = @CURRENTDATE

                  declare @DEPOSITID uniqueidentifier;
                  select 
                    @DEPOSITID = BADP.DEPOSITID
                  from dbo.BANKACCOUNTDEPOSITPAYMENT BADP
                  where BADP.ID = @REVENUEID;
                  if @DEPOSITID is not null
                    exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @REVENUEID, @DEPOSITID, @CHANGEAGENTID, @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
            end try
            begin catch
              exec dbo.USP_RAISE_ERROR;
              return 1;
            end catch

            return 0;