USP_REVENUEBATCH_REVENUEAPPLICATIONS_ADDFROMXML

      CREATE procedure dbo.USP_REVENUEBATCH_REVENUEAPPLICATIONS_ADDFROMXML
      (
          @BATCHREVENUEID uniqueidentifier,
          @REVENUESTREAMS xml,
          @CHANGEAGENTID uniqueidentifier,
          @CHANGEDATE datetime,
          @CURRENTAPPUSERID uniqueidentifier = null
      )
      as
          set nocount on;

            -- The @REVENUESTREAMS field is a collection with REVENUESTREAM elements.

          -- A REVENUESTREAM element may have a PLEDGES collection with PLEDGE elements.

          -- A PLEDGE element may have SPLITS, BENEFITS, and INSTALLMENTS collections with similarly named elements.

          --

          -- The approach taken is to shred a collection into a table variable which contains its children as XML columns, creating a new primary key.

          -- Then, repeat the process for the children elements inserting a foreign key into their tables using the newly generated parent primary key.

          -- Repeat the process until we hit the bottom of the object tree.

          -- After everything has been shredded, insert the rows from our table variables into the corresponding batch tables.

          --

          -- More specifically:

          -- 1. Shred XML into table variables using FROMXML functions:

          --     +-----------------------------------------------------------------------+--------------------------------------+----------------------

          --     | Xml to Table Function Name                                            | Node Name                            | Table Variable Name

          --     |-----------------------------------------------------------------------+--------------------------------------+----------------------

          --   a | UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN_FROMITEMLISTXML          | RevenueStreams/Item                  | @REVENUEAPPLICATIONS

          --   b | UFN_REVENUEBATCH_GETPAYMENTPLEDGE_FROMITEMLISTXML                     | ../../Pledges/Item                   | @PLEDGES

          --   c | UFN_REVENUEBATCH_GETSPLITSFORPAYMENTPLEDGE_FROMITEMLISTXML            | ../../../../Splits/Item              | @SPLITS

          --   d | UFN_REVENUEBATCH_GETINSTALLMENTSFORPAYMENTPLEDGE_FROMITEMLISTXML      | ../../../../Installments/Item        | @INSTALLMENTS

          --   e | UFN_REVENUEBATCH_GETBENEFITSFORPAYMENTPLEDGE_FROMITEMLISTXML          | ../../../../Benefits/Item            | @BENEFITS

          --   f | UFN_REVENUEBATCH_GETPERCENTAGEBENEFITSFORPAYMENTPLEDGE_FROMITEMLISTXML| ../../../../PercentageBenefits/Item  | @PERCENTAGEBENEFITS

          --

          -- 2. Insert table variables into batch tables:

          --     +----------------------+--------------------------------------------

          --     | Table Variable Name  | Batch Table Name

          --     |----------------------+--------------------------------------------

          --   a | @PLEDGES             | BATCHREVENUEAPPLICATIONPLEDGE

          --   b | @REVENUEAPPLICATIONS | BATCHREVENUEAPPLICATION

          --   c | @SPLITS              | BATCHREVENUEAPPLICATIONPLEDGESPLITS

          --   d | @INSTALLMENTS        | BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENTS

          --   e | @BENEFITS            | BATCHREVENUEAPPLICATIONPLEDGEBENEFITS

          --   f | @PERCENTAGEBENEFITS  | BATCHREVENUEAPPLICATIONPLEDGEPERCENTAGEBENEFITS


          --A REVENUESTREAM may also have a MEMBERSHIPS collection with MEMBERSHIP elements.

          --A MEMBERSHIP element will have MEMBERS and MEMBERSHIPCARDS collections


          if @CHANGEAGENTID is null
              exec USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

          if @CHANGEDATE is null 
              set @CHANGEDATE = getdate();

          begin 

              -- 1(a) REVENUEAPPLICATION XML shredding

              declare @REVENUEAPPLICATIONS table
              (
                  ID uniqueidentifier,
                  APPLICATIONID uniqueidentifier,
                  CONSTITUENTID uniqueidentifier,
                  CONSTITUENTNAME nvarchar(300),
                  APPLIED money,
                  BALANCE money,
                  AMOUNTDUE money,
                  DATEDUE datetime,
                  TYPECODE tinyint,
                  RECEIPTAMOUNT money,
                  DONOTRECEIPT bit,
                  [DESCRIPTION] nvarchar(300),
                  WASGENERATED bit,
                  PLEDGES xml,
                  MEMBERSHIPS xml,
                  OVERPAYMENTAPPLICATIONTYPECODE tinyint,
                  USEDAPPLICATIONCOMMITMENTLOOKUPID bit,
                  SPONSORSHIPOPPORTUNITY nvarchar(300),
          REVENUESPLITID uniqueidentifier
              );

              --Insert normal rows from batch/import

              insert into @REVENUEAPPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, [DESCRIPTION], WASGENERATED, PLEDGES, MEMBERSHIPS, OVERPAYMENTAPPLICATIONTYPECODE, USEDAPPLICATIONCOMMITMENTLOOKUPID,SPONSORSHIPOPPORTUNITY, REVENUESPLITID)
              select case when ID = '00000000-0000-0000-0000-000000000000' then newid() else coalesce(ID, newid()) end ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, [DESCRIPTION], WASGENERATED, PLEDGES, MEMBERSHIPS, OVERPAYMENTAPPLICATIONTYPECODE, 0, SPONSORSHIPOPPORTUNITY, REVENUESPLITID
              from dbo.UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN_FROMITEMLISTXML(@REVENUESTREAMS)
              where APPLICATIONCOMMITMENTID is null;

              --Insert rows that are using the APPLICATIONCOMMITMENTID as a LookupID to find the appropriate commitment

              insert into @REVENUEAPPLICATIONS(ID, APPLICATIONID, CONSTITUENTID, CONSTITUENTNAME, APPLIED, BALANCE, AMOUNTDUE, DATEDUE, TYPECODE, RECEIPTAMOUNT, DONOTRECEIPT, [DESCRIPTION], WASGENERATED, PLEDGES, MEMBERSHIPS, OVERPAYMENTAPPLICATIONTYPECODE, USEDAPPLICATIONCOMMITMENTLOOKUPID,SPONSORSHIPOPPORTUNITY, REVENUESPLITID)
              select newid(), APPS.APPLICATIONCOMMITMENTID, APPS.CONSTITUENTID, APPS.CONSTITUENTNAME, APPS.APPLIED, APPS.BALANCE, APPS.AMOUNTDUE, APPS.DATEDUE,
                case
                    when dbo.UFN_REVENUE_GETBATCHAPPLICATIONTYPECODE(APPS.APPLICATIONCOMMITMENTID) = 0 then 0
                    when dbo.UFN_REVENUE_GETBATCHAPPLICATIONTYPECODE(APPS.APPLICATIONCOMMITMENTID) = 1 then 6
                    when dbo.UFN_REVENUE_GETBATCHAPPLICATIONTYPECODE(APPS.APPLICATIONCOMMITMENTID) = 5 then 5
                    else null
                end,
                APPS.RECEIPTAMOUNT, APPS.DONOTRECEIPT, APPS.[DESCRIPTION], APPS.WASGENERATED, APPS.PLEDGES, 
                case
                    when dbo.UFN_REVENUE_GETBATCHAPPLICATIONTYPECODE(APPS.APPLICATIONCOMMITMENTID) = 5 then dbo.UFN_REVENUEBATCH_GETMEMBERSHIP_TOITEMLISTXML(APPS.APPLICATIONCOMMITMENTID)
                    else APPS.MEMBERSHIPS
                end,
                case
                    when REVENUE.TRANSACTIONTYPECODE = 1 and dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(dbo.UFN_PLEDGE_GETNEXTINSTALLMENT(REVENUE.ID, null)) < APPS.APPLIED and APPS.OVERPAYMENTAPPLICATIONTYPECODE is not null then APPS.OVERPAYMENTAPPLICATIONTYPECODE
                    when REVENUE.TRANSACTIONTYPECODE = 1 and dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(dbo.UFN_PLEDGE_GETNEXTINSTALLMENT(REVENUE.ID, null)) < APPS.APPLIED and APPS.OVERPAYMENTAPPLICATIONTYPECODE is null then dbo.UFN_PLEDGE_GETDEFAULTOVERPAYMENTAPPLICATIONCODE()
                    else null                     
                end,
                1,
                SPONSORSHIPOPPORTUNITY,
        REVENUESPLITID
              from dbo.UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN_FROMITEMLISTXML(@REVENUESTREAMS) APPS
              left join dbo.REVENUE on APPS.APPLICATIONCOMMITMENTID = REVENUE.ID
              where APPLICATIONCOMMITMENTID is not null;

              -- 1(b) PLEDGE XML shredding

              declare @PLEDGES table
                (
                    ID uniqueidentifier,
                    APPLICATIONID uniqueidentifier,
                    CONSTITUENTID uniqueidentifier,
                    DATE datetime,
                    AMOUNT money,
                    POSTDATE datetime,
                    POSTSTATUSCODE tinyint,
                    SENDPLEDGEREMINDER bit,
                    FREQUENCYCODE tinyint,
                    NUMBEROFINSTALLMENTS int,
                    STARTDATE datetime,
                    SPLITS xml,
                    INSTALLMENTS xml,
                    FINDERNUMBER bigint,
                    SOURCECODE nvarchar(50),
                    APPEALID uniqueidentifier,
                    BENEFITS xml,
                    BENEFITSWAIVED bit,
                    GIVENANONYMOUSLY bit,
                    MAILINGID uniqueidentifier,
                    CHANNELCODEID uniqueidentifier,
                    DONOTACKNOWLEDGE bit,
                    PLEDGESUBTYPEID uniqueidentifier,
                    SINGLEDESIGNATIONID uniqueidentifier,
                    REFERENCE nvarchar(255),
                    GLREVENUECATEGORYMAPPINGID uniqueidentifier,
                    OPPORTUNITYID uniqueidentifier,
                    PERCENTAGEBENEFITS xml
                );
              insert into @PLEDGES(ID, APPLICATIONID, CONSTITUENTID, DATE, AMOUNT, POSTDATE, POSTSTATUSCODE, SENDPLEDGEREMINDER, FREQUENCYCODE, NUMBEROFINSTALLMENTS, STARTDATE, SPLITS, INSTALLMENTS, FINDERNUMBER, SOURCECODE, APPEALID, BENEFITS, BENEFITSWAIVED, GIVENANONYMOUSLY, MAILINGID, CHANNELCODEID, DONOTACKNOWLEDGE, PLEDGESUBTYPEID, REFERENCE, GLREVENUECATEGORYMAPPINGID, OPPORTUNITYID, PERCENTAGEBENEFITS)
              select newid(), RA.ID, P.CONSTITUENTID, P.DATE, P.AMOUNT, P.POSTDATE, P.POSTSTATUSCODE, P.SENDPLEDGEREMINDER, P.FREQUENCYCODE, P.NUMBEROFINSTALLMENTS, P.STARTDATE, P.SPLITS, P.INSTALLMENTS, coalesce(P.FINDERNUMBER, 0), P.SOURCECODE, P.APPEALID, P.BENEFITS, P.BENEFITSWAIVED, P.GIVENANONYMOUSLY, P.MAILINGID, P.CHANNELCODEID, P.DONOTACKNOWLEDGE, P.PLEDGESUBTYPEID, P.REFERENCE, P.GLREVENUECATEGORYMAPPINGID, P.OPPORTUNITYID, P.PERCENTAGEBENEFITS
              from @REVENUEAPPLICATIONS RA
              cross apply UFN_REVENUEBATCH_GETPAYMENTPLEDGE_FROMITEMLISTXML(RA.PLEDGES) P;

             declare @EMPTYGUID uniqueidentifier;
             set @EMPTYGUID = '00000000-0000-0000-0000-000000000000';

              -- 1(c) SPLIT XML shredding

              declare @PLEDGESPLITS table
              (
                  ID uniqueidentifier,
                  PLEDGEID uniqueidentifier,
                  DESIGNATIONID uniqueidentifier,
                  AMOUNT money,
            DECLINESGIFTAID bit
              );
              insert into @PLEDGESPLITS (ID, PLEDGEID, DESIGNATIONID, AMOUNT, DECLINESGIFTAID)
              select newid(), P.ID, S.DESIGNATIONID, S.AMOUNT, COALESCE(S.DECLINESGIFTAID, 0)
              from @PLEDGES P
              cross apply UFN_REVENUEBATCH_GETSPLITSFORPAYMENTPLEDGE_FROMITEMLISTXML(P.SPLITS) S;

              -- 1(d) INSTALLMENT XML shredding

              declare @PLEDGEINSTALLMENTS table
              (
                  ID uniqueidentifier,
                  PLEDGEID uniqueidentifier,
                  AMOUNT money,
                  RECEIPTAMOUNT money,
                  DATE datetime,
                  SEQUENCE int
              );
              insert into @PLEDGEINSTALLMENTS(ID, PLEDGEID, AMOUNT, RECEIPTAMOUNT, DATE, SEQUENCE)
              select newid(), P.ID, I.AMOUNT, I.RECEIPTAMOUNT, I.DATE, I.SEQUENCE
              from @PLEDGES P
              cross apply UFN_REVENUEBATCH_GETINSTALLMENTSFORPAYMENTPLEDGE_FROMITEMLISTXML(P.INSTALLMENTS) I;

              -- 1(e) BENEFIT XML shredding

              declare @PLEDGEBENEFITS table
              (
                  ID uniqueidentifier,
                  PLEDGEID uniqueidentifier,
                  BENEFITID uniqueidentifier,
                  QUANTITY smallint,
                  UNITVALUE money,
                  DETAILS nvarchar(255),
                  SEQUENCE int
              );
              insert into @PLEDGEBENEFITS(ID, PLEDGEID, BENEFITID, QUANTITY, UNITVALUE, DETAILS, SEQUENCE)
              select newid(), P.ID, B.BENEFITID, B.QUANTITY, B.UNITVALUE, B.DETAILS, B.SEQUENCE
              from @PLEDGES P
              cross apply UFN_REVENUEBATCH_GETBENEFITSFORPAYMENTPLEDGE_FROMITEMLISTXML(P.BENEFITS) B;

              -- 1(f) PERCENTAGEBENEFIT XML shredding

              declare @PLEDGEPERCENTAGEBENEFITS table
              (
                  ID uniqueidentifier,
                  PLEDGEID uniqueidentifier,
                  BENEFITID uniqueidentifier,
                  PERCENTAPPLICABLEAMOUNT money,
                  VALUEPERCENT decimal,
                  DETAILS nvarchar(255),
                  SEQUENCE int
              );
              insert into @PLEDGEPERCENTAGEBENEFITS(ID, PLEDGEID, BENEFITID, PERCENTAPPLICABLEAMOUNT, VALUEPERCENT, DETAILS, SEQUENCE)
              select newid(), P.ID, B.BENEFITID, B.PERCENTAPPLICABLEAMOUNT, B.VALUEPERCENT, B.DETAILS, B.SEQUENCE
              from @PLEDGES P
              cross apply UFN_REVENUEBATCH_GETPERCENTAGEBENEFITSFORPAYMENTPLEDGE_FROMITEMLISTXML(P.PERCENTAGEBENEFITS) B;

              -- 2(a) PLEDGE variable to batch table insertion

              insert into dbo.BATCHREVENUEAPPLICATIONPLEDGE
              (
                ID, CONSTITUENTID, GIVENANONYMOUSLY, FINDERNUMBER,
                DATE, AMOUNT, SOURCECODE, CHANNELCODEID, APPEALID, MAILINGID, PLEDGESUBTYPEID,
                BENEFITSWAIVED, FREQUENCYCODE, NUMBEROFINSTALLMENTS, STARTDATE, POSTDATE, 
                POSTSTATUSCODE, SENDPLEDGEREMINDER, DONOTACKNOWLEDGE,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, REFERENCE, GLREVENUECATEGORYMAPPINGID,
                OPPORTUNITYID
              )
              select
                ID, CONSTITUENTID, GIVENANONYMOUSLY, FINDERNUMBER,
                DATE, AMOUNT, SOURCECODE, CHANNELCODEID, APPEALID, MAILINGID, PLEDGESUBTYPEID,
                BENEFITSWAIVED, FREQUENCYCODE, NUMBEROFINSTALLMENTS, STARTDATE, POSTDATE,
                POSTSTATUSCODE, SENDPLEDGEREMINDER, DONOTACKNOWLEDGE,
                @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, REFERENCE, GLREVENUECATEGORYMAPPINGID, OPPORTUNITYID
              from @PLEDGES;         

                declare @MEMBERSHIPS table
                (
                    ID uniqueidentifier,
                    MEMBERSHIPID uniqueidentifier,
                    APPLICATIONID uniqueidentifier,
                    MEMBERSHIPPROGRAMID uniqueidentifier,
                    MEMBERSHIPLEVELID uniqueidentifier,
                    MEMBERSHIPLEVELTERMID uniqueidentifier,
                    MEMBERSHIPLEVELTYPECODEID uniqueidentifier,
                    NUMBEROFCHILDREN tinyint,
                    COMMENTS nvarchar(1000),
                    ISGIFT bit,
                    SENDRENEWALCODE tinyint,
                    EXPIRATIONDATE datetime,
                    GIVENBYID uniqueidentifier,
                    MEMBERS xml
                )
                insert into @MEMBERSHIPS(ID, APPLICATIONID, MEMBERSHIPID, MEMBERSHIPPROGRAMID,MEMBERSHIPLEVELID,MEMBERSHIPLEVELTERMID,MEMBERSHIPLEVELTYPECODEID,NUMBEROFCHILDREN,COMMENTS,ISGIFT,SENDRENEWALCODE,EXPIRATIONDATE,GIVENBYID, MEMBERS)
                select newid(), RA.ID, RA.APPLICATIONID, MEMBERSHIPPROGRAMID, MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID, MEMBERSHIPLEVELTYPECODEID, NUMBEROFCHILDREN, COMMENTS, ISGIFT, SENDRENEWALCODE, EXPIRATIONDATE, GIVENBYID, MEMBERS 
                from @REVENUEAPPLICATIONS RA
                cross apply dbo.UFN_REVENUEBATCH_GETMEMBERSHIP_FROMITEMLISTXML(RA.MEMBERSHIPS)


                insert into dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP
                (
                    ID,MEMBERSHIPID, MEMBERSHIPPROGRAMID,MEMBERSHIPLEVELID,MEMBERSHIPLEVELTERMID,
                    MEMBERSHIPLEVELTYPECODEID,NUMBEROFCHILDREN,COMMENTS,ISGIFT,SENDRENEWALCODE,
                    EXPIRATIONDATE,GIVENBYID,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select ID, MEMBERSHIPID, MEMBERSHIPPROGRAMID, MEMBERSHIPLEVELID, MEMBERSHIPLEVELTERMID,
                        MEMBERSHIPLEVELTYPECODEID, NUMBEROFCHILDREN, COMMENTS, ISGIFT, SENDRENEWALCODE, 
                        EXPIRATIONDATE,    GIVENBYID,
                        @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
                from @MEMBERSHIPS;

                -- Handle the membership member collection

                declare @MEMBERSHIPMEMBER table
                (
                    ID uniqueidentifier,
                    BATCHREVENUEAPPLICATIONMEMBERSHIPID uniqueidentifier,
                    CONSTITUENTID uniqueidentifier,
                    ISPRIMARY bit,
                    MEMBERSHIPCARDS xml
                );

                insert into @MEMBERSHIPMEMBER (ID, BATCHREVENUEAPPLICATIONMEMBERSHIPID, CONSTITUENTID, ISPRIMARY, MEMBERSHIPCARDS)
                select
                    newid(),
                    MEMBERSHIPS.ID,
                    MEMBERS.CONSTITUENTID,
                    MEMBERS.ISPRIMARY,
                    MEMBERSHIPCARDS
                from @MEMBERSHIPS MEMBERSHIPS
                cross apply dbo.UFN_REVENUEBATCH_GETMEMBERSHIPMEMBERSWITHCHILDREN_FROMITEMLISTXML(MEMBERSHIPS.MEMBERS) MEMBERS

                insert into dbo.BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBER
                (
                    ID, 
                    BATCHREVENUEAPPLICATIONMEMBERSHIPID,
                    CONSTITUENTID,
                    ISPRIMARY,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    ID,
                    BATCHREVENUEAPPLICATIONMEMBERSHIPID,
                    CONSTITUENTID,
                    ISPRIMARY,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE
                from @MEMBERSHIPMEMBER

                -- Handle the membership member membership card collection

                insert into dbo.BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBERMEMBERSHIPCARD
                (
                    BATCHREVENUEAPPLICATIONMEMBERSHIPMEMBERID,
                    NAMEONCARD,
                    EXPIRATIONDATE,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED,
                    DATECHANGED
                )
                select
                    MEMBERSHIPMEMBER.ID,
                    MEMBERSHIPCARD.NAMEONCARD,
                    MEMBERSHIPCARD.EXPIRATIONDATE,
                    @CHANGEAGENTID,
                    @CHANGEAGENTID,
                    @CHANGEDATE,
                    @CHANGEDATE
                from @MEMBERSHIPMEMBER MEMBERSHIPMEMBER
                cross apply dbo.UFN_REVENUEBATCH_GETMEMBERSHIPMEMBERMEMBERSHIPCARDS_FROMITEMLISTXML(MEMBERSHIPMEMBER.MEMBERSHIPCARDS) MEMBERSHIPCARD

            -- 2(b) REVENUEAPPLICATION variable to batch table insertion 

              insert into dbo.BATCHREVENUEAPPLICATION
              (
                ID, BATCHREVENUEID, APPLICATIONTYPECODE, REVENUEID, REGISTRANTID, BATCHREVENUEAPPLICATIONPLEDGEID, BATCHREVENUEAPPLICATIONMEMBERSHIPID, BATCHREVENUEREGISTRANTID,
                APPLIED, WASGENERATED, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, OVERPAYMENTAPPLICATIONTYPECODE, REVENUESPLITID
              )
              select
                  ra.ID, 
                  @BATCHREVENUEID,
                  case
                      when ra.TYPECODE = 3 and ra.APPLICATIONID = @EMPTYGUID then 7
                      when ra.TYPECODE = 10 and ra.APPLICATIONID = @EMPTYGUID then 13
                      when ra.TYPECODE = 6 then 1
                      when ra.TYPECODE = 5 then 5
                      when ra.TYPECODE <> 6 and ra.PLEDGES is null then 0
                      when ra.TYPECODE <> 6 and not ra.PLEDGES is null then 2
                  end,
                  case 
                    when (ra.TYPECODE = 100) then null -- opportunities pass the opportunityid as the application id

                    when (ra.TYPECODE <> 6 and ra.TYPECODE <> 5 and ra.PLEDGES is null and ra.APPLICATIONID <> @EMPTYGUID) then ra.APPLICATIONID
                    else null 
                  end,
                  case when ra.TYPECODE = 6 and exists(select top(1) ID from dbo.REGISTRANT where REGISTRANT.ID = ra.APPLICATIONID) then ra.APPLICATIONID else null end,
                  case when ra.TYPECODE <> 6 and ra.TYPECODE <> 5 and not ra.PLEDGES is null then (select p.ID from @PLEDGES p where p.APPLICATIONID = ra.ID) else null end,
                  case when ra.TYPECODE = 5 and not ra.MEMBERSHIPS is null then (select m.ID from @MEMBERSHIPS m where m.APPLICATIONID = ra.ID) else null end,
                  case when ra.TYPECODE = 6 and exists (select top(1) ID from dbo.BATCHREVENUEREGISTRANT where BATCHREVENUEREGISTRANT.ID = ra.APPLICATIONID and BATCHREVENUEREGISTRANT.ID not in (select ID from dbo.REGISTRANT where ID = ra.APPLICATIONID)) then ra.APPLICATIONID else null end,
                  ra.APPLIED,
                  coalesce(ra.WASGENERATED, 0),
                  @CHANGEAGENTID,
                  @CHANGEAGENTID,
                  @CHANGEDATE,
                  @CHANGEDATE,
                  case
                    when ra.OVERPAYMENTAPPLICATIONTYPECODE is null then 255
                    else ra.OVERPAYMENTAPPLICATIONTYPECODE
                  end,
           ra.REVENUESPLITID
              from @REVENUEAPPLICATIONS ra;

              -- 2(c) SPLIT variable to batch table insertion

              insert into dbo.BATCHREVENUEAPPLICATIONPLEDGESPLIT(ID, BATCHREVENUEAPPLICATIONPLEDGEID, DESIGNATIONID, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, DECLINESGIFTAID)
              select ID, PLEDGEID, DESIGNATIONID, AMOUNT, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE, DECLINESGIFTAID
              from @PLEDGESPLITS;

              -- 2(d) INSTALLMENT variable to batch table insertion

              insert into dbo.BATCHREVENUEAPPLICATIONPLEDGEINSTALLMENT(ID, BATCHREVENUEAPPLICATIONPLEDGEID, AMOUNT, RECEIPTAMOUNT, DATE, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              select ID, PLEDGEID, AMOUNT, RECEIPTAMOUNT, DATE, SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
              from @PLEDGEINSTALLMENTS;

              -- 2(e) BENEFIT variable to batch table insertion

              insert into dbo.BATCHREVENUEAPPLICATIONPLEDGEBENEFIT(ID, BATCHREVENUEAPPLICATIONPLEDGEID, BENEFITID, QUANTITY, UNITVALUE, DETAILS, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              select ID, PLEDGEID, BENEFITID, QUANTITY, UNITVALUE, DETAILS, SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
              from @PLEDGEBENEFITS;

              -- 2(f) PERCENTAGEBENEFITS variable to batch table insertion

              insert into dbo.BATCHREVENUEAPPLICATIONPLEDGEPERCENTAGEBENEFIT(ID, BATCHREVENUEAPPLICATIONPLEDGEID, BENEFITID, PERCENTAPPLICABLEAMOUNT, VALUEPERCENT, DETAILS, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
              select ID, PLEDGEID, BENEFITID, PERCENTAPPLICABLEAMOUNT, VALUEPERCENT, DETAILS, SEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE
              from @PLEDGEPERCENTAGEBENEFITS;

              -- That's all folks.            

              end
          return 0;