USP_ADDPLANNEDGIFTREVENUE

            CREATE procedure dbo.USP_ADDPLANNEDGIFTREVENUE(
                @ID uniqueidentifier, 
                @AMOUNT money,
                @DATE datetime,
                @RECEIPTAMOUNT money,
                @CHANGEAGENTID uniqueidentifier,
                @ANONYMOUS bit,
                @NEWREVENUEID uniqueidentifier = null,
                @GENERATEGLDISTRIBUTIONS bit,
                @PDACCOUNTSYSTEMID uniqueidentifier = null,
                @TRANSACTIONCURRENCYID uniqueidentifier = null,
                @BASECURRENCYID uniqueidentifier = null,
                @BASEEXCHANGERATEID uniqueidentifier = null,
                @DONOTRECEIPT bit = null
            )
            as begin
                set nocount on;

                declare @CONSTITUENTID uniqueidentifier;
                declare @GIFTAMOUNT money;
                declare @RECOGNITIONAMOUNT money;
                declare @SPLITS xml;

                if @PDACCOUNTSYSTEMID is null
                    set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'                        

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

                if @DONOTRECEIPT is null
                    set @DONOTRECEIPT = 1

                declare @CURRENTDATE datetime;    
                set @CURRENTDATE = getdate();          

                --JamesWill WI197067 2012-03-08 Ensure that the date used does not have a timestamp

                if not @DATE is null
                    set @DATE = dbo.UFN_DATE_GETEARLIESTTIME(@DATE);

                select 
                  @CONSTITUENTID = CONSTITUENTID,
                  @GIFTAMOUNT = TRANSACTIONGIFTAMOUNT,
                  @RECOGNITIONAMOUNT = RECOGNITIONAMOUNT
                from dbo.PLANNEDGIFT
                where ID = @ID;

                --Get current designations

                set @SPLITS = dbo.UFN_PLANNEDGIFT_DESIGNATION_2_TOITEMLISTXML(@ID);

                --Splits with the same designation need to be combined

                set @SPLITS = (
                    select sum(TRANSACTIONAMOUNT) as AMOUNT, DESIGNATIONID, TRANSACTIONCURRENCYID
                    from dbo.UFN_PLANNEDGIFT_DESIGNATION_2_FROMITEMLISTXML(@SPLITS)
                    group by DESIGNATIONID, TRANSACTIONCURRENCYID
                    for xml raw('ITEM'),type,elements,root('DESIGNATION'),binary base64
                )

                --Need to prorate splits if using amount other than gift amount

                set @SPLITS = dbo.UFN_PLANNEDGIFTGETSPLITS_XML(@GIFTAMOUNT, @AMOUNT, @SPLITS); 

                declare @COUNT int;

                begin try
                    exec dbo.USP_PLANNEDGIFT_VALIDATESPLITS @SPLITS, @AMOUNT;

                    select 
                        @COUNT = count(ID) 
                    from dbo.PLANNEDGIFTREVENUE PGR
                    where PGR.ID = @ID;

                    if @COUNT > 0 
                        raiserror('A planned gift can only be linked to one revenue record.', 13, 1);

                    declare @REVENUEID uniqueidentifier;

                    if @NEWREVENUEID is null
                        set @REVENUEID=NewID();
                    else
                        set @REVENUEID = @NEWREVENUEID;

                    --Multicurrency - AdamBu 5/19/10 - Retrieve and calculate the necessary multicurrency values.    

                    declare @BASEAMOUNT money;                    
                    declare @ORGANIZATIONCURRENCYID uniqueidentifier;
                    declare @ORGANIZATIONAMOUNT money;
                    declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

                    exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @DATE, @BASECURRENCYID, @BASEEXCHANGERATEID output, @TRANSACTIONCURRENCYID output, @BASEAMOUNT output, @ORGANIZATIONCURRENCYID output, @ORGANIZATIONAMOUNT output, @ORGANIZATIONEXCHANGERATEID output, 1;

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

                    -- ****

                    if @GENERATEGLDISTRIBUTIONS = 1
                      set @GENERATEGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
                    -- ****


                    -- Determine Receipt Type Preference

                    declare @RECEIPTTYPECODE bit;
                    set @RECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID, null)

                    if @GENERATEGLDISTRIBUTIONS = 0 
                        insert into dbo.REVENUE (ID, CONSTITUENTID, DATE, DONOTPOST, POSTDATE, DONOTRECEIPT, AMOUNT, TRANSACTIONTYPECODE, RECEIPTAMOUNT, GIVENANONYMOUSLY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID,RECEIPTTYPECODE)
                            values (@REVENUEID, @CONSTITUENTID, @DATE, 1, null, @DONOTRECEIPT, @BASEAMOUNT, 4, @RECEIPTAMOUNT, @ANONYMOUS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID,@RECEIPTTYPECODE);
                    else
                        insert into dbo.REVENUE (ID, CONSTITUENTID, DATE, DONOTPOST, POSTDATE, DONOTRECEIPT, AMOUNT, TRANSACTIONTYPECODE, RECEIPTAMOUNT, GIVENANONYMOUSLY, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID,RECEIPTTYPECODE)
                            values (@REVENUEID, @CONSTITUENTID, @DATE, 0, @DATE, @DONOTRECEIPT, @BASEAMOUNT, 4, @RECEIPTAMOUNT, @ANONYMOUS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID,@RECEIPTTYPECODE);

                    declare @PAYMENTMETHODID uniqueidentifier;
                    set @PAYMENTMETHODID = newid();
                    insert into dbo.REVENUEPAYMENTMETHOD (ID, REVENUEID, PAYMENTMETHODCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values (@PAYMENTMETHODID, @REVENUEID, 9, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                    insert into dbo.PLANNEDGIFTREVENUE (ID, REVENUEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values (@ID, @REVENUEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                    declare @FREQUENCYCODE tinyint;
                    declare @NUMBEROFINSTALLMENTS int;
                    declare @INSTALLMENTSEQUENCE int;

                    set @FREQUENCYCODE = 5; --Single Installment

                    set @NUMBEROFINSTALLMENTS = 1;
                    set @INSTALLMENTSEQUENCE = 1;

                    insert into dbo.REVENUESCHEDULE (ID, STARTDATE, FREQUENCYCODE, NUMBEROFINSTALLMENTS, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                        values (@REVENUEID, @DATE, @FREQUENCYCODE, @NUMBEROFINSTALLMENTS, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);

                    insert into dbo.INSTALLMENT (ID, REVENUEID, AMOUNT, DATE, SEQUENCE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED,BASECURRENCYID,ORGANIZATIONAMOUNT,ORGANIZATIONEXCHANGERATEID,TRANSACTIONAMOUNT,TRANSACTIONCURRENCYID,BASEEXCHANGERATEID)
                        values (newid(), @REVENUEID, @BASEAMOUNT, @DATE, @INSTALLMENTSEQUENCE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,@BASECURRENCYID,@ORGANIZATIONAMOUNT,@ORGANIZATIONEXCHANGERATEID,@AMOUNT,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID);

                    --Multicurrency - AdamBu 5/19/10 - Process the splits xml to calculate the base and organization amounts and place them in proper nodes.

                    set @SPLITS = dbo.UFN_PLANNEDGIFTDESIGNATION_CONVERTAMOUNTSINXML(@SPLITS,@BASECURRENCYID,@ORGANIZATIONEXCHANGERATEID,@TRANSACTIONCURRENCYID,@BASEEXCHANGERATEID)
                    exec dbo.USP_PLANNEDGIFT_GETSPLITS_ADDFROMXML_2 @REVENUEID, @SPLITS, @CHANGEAGENTID, @CURRENTDATE;

                    -- create recognitions

                    exec dbo.USP_REVENUE_CREATERECOGNITIONS_WITHAMOUNT @REVENUEID, @RECOGNITIONAMOUNT, @CHANGEAGENTID, @CURRENTDATE;

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

                    if dbo.UFN_PLEDGE_INSTALLMENTSPLITSBALANCE(@ID) = 0
                        raiserror('INSTALLMENTSPLITSBALANCE', 13, 2);

                    -- Link revenue to Account System

                    exec dbo.USP_PDACCOUNTSYSTEM_LINKTOREVENUE @REVENUEID, @PDACCOUNTSYSTEMID, @CURRENTDATE, @CHANGEAGENTID;

                    --Save the GL distributions

                    if @GENERATEGLDISTRIBUTIONS = 1
                      exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE;

                    -- Copy campaigns from planned gift

                    insert into dbo.REVENUESPLITCAMPAIGN (REVENUESPLITID, CAMPAIGNID, CAMPAIGNSUBPRIORITYID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select distinct
                        REVENUESPLIT.ID,
                        PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNID,
                        PLANNEDGIFTDESIGNATIONCAMPAIGN.CAMPAIGNSUBPRIORITYID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CURRENTDATE,
                        @CURRENTDATE
                    from dbo.PLANNEDGIFTDESIGNATION
                    inner join dbo.PLANNEDGIFTDESIGNATIONCAMPAIGN on PLANNEDGIFTDESIGNATION.ID = PLANNEDGIFTDESIGNATIONCAMPAIGN.PLANNEDGIFTDESIGNATIONID
                    inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = @REVENUEID and REVENUESPLIT.DESIGNATIONID = PLANNEDGIFTDESIGNATION.DESIGNATIONID
                    where PLANNEDGIFTDESIGNATION.PLANNEDGIFTID = @ID

                    exec dbo.USP_PLANNEDGIFTREVENUE_DEFAULTSOLICITORS @ID, @REVENUEID, @CHANGEAGENTID, @CURRENTDATE
                end try

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