USP_RECEIVABLEPAYMENT_ADD

Adds the given receivable payment information to the system.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	INOUT
@CHANGEAGENTID	uniqueidentifier	IN
@CONSTITUENTID	uniqueidentifier	IN
@AMOUNT	money	IN
@PAYMENTDATE	datetime	IN
@POSTSTATUSCODE	tinyint	IN
@POSTDATE	datetime	IN
@PAYMENTMETHODCODE	tinyint	IN
@CONSTITUENTACCOUNTID	uniqueidentifier	IN
@CHECKDATE	UDT_FUZZYDATE	IN
@CHECKNUMBER	nvarchar(20)	IN
@REFERENCEDATE	UDT_FUZZYDATE	IN
@REFERENCENUMBER	nvarchar(20)	IN
@CARDHOLDERNAME	nvarchar(255)	IN
@CREDITCARDPARTIALNUMBER	nvarchar(4)	IN
@CREDITTYPECODEID	uniqueidentifier	IN
@EXPIRESON	UDT_FUZZYDATE	IN
@AUTHORIZATIONCODE	nvarchar(20)	IN
@DIRECTDEBITRESULTCODE	nvarchar(10)	IN
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	IN
@RECEIVABLEPAYMENTID	nvarchar(60)	IN
@STUDENTID	uniqueidentifier	IN
@APPLICATIONS	xml	IN
Definition

Copy


            CREATE procedure dbo.USP_RECEIVABLEPAYMENT_ADD
            (
                @ID uniqueidentifier = null output,
                @CHANGEAGENTID uniqueidentifier,
                @CONSTITUENTID uniqueidentifier,
                @AMOUNT money = 0,
                @PAYMENTDATE datetime = null,
                @POSTSTATUSCODE tinyint = 1,
                @POSTDATE datetime = null,
                @PAYMENTMETHODCODE tinyint = 1,
                @CONSTITUENTACCOUNTID uniqueidentifier = null,
                @CHECKDATE dbo.UDT_FUZZYDATE = '00000000',
                @CHECKNUMBER nvarchar(20) = '',
                @REFERENCEDATE dbo.UDT_FUZZYDATE = '00000000',
                @REFERENCENUMBER nvarchar(20) = '',
                @CARDHOLDERNAME nvarchar(255) = '',
                @CREDITCARDPARTIALNUMBER nvarchar(4) = '',
                @CREDITTYPECODEID uniqueidentifier = null,
                @EXPIRESON dbo.UDT_FUZZYDATE = '00000000',
                @AUTHORIZATIONCODE nvarchar(20) = '',
                @DIRECTDEBITRESULTCODE nvarchar(10) = '',
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null,
                @RECEIVABLEPAYMENTID nvarchar(60) = null,
                @STUDENTID uniqueidentifier = null,
                @APPLICATIONS xml = null
            )
            as
            begin
                set nocount on;

                -- Setup the basic information

                if (@CHANGEAGENTID is null)
                begin
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
                end

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

                -- Make sure we have a student

                if (@STUDENTID is null)
                begin
                    raiserror('ERR_RECEIVABLEPAYMENT_STUDENTREQUIRED', 13, 1);
                end

                -- Make sure we have a constituent

                if (@CONSTITUENTID is null)
                begin
                    raiserror('ERR_RECEIVABLEPAYMENT_CONSTITUENTREQUIRED', 13, 1);
                end

                -- Make sure we have an ID

                if (@ID is null)
                begin
                    set @ID = newid();
                end

                -- Setup the next receivable payment ID

                if ((@RECEIVABLEPAYMENTID is null) or (len(@RECEIVABLEPAYMENTID) = 0))
                begin
                    set @RECEIVABLEPAYMENTID = dbo.UFN_FINANCIALTRANSACTION_GETNEXTFINANCIALTRANSACTIONID(105);
                end

                -- Insert the financial transaction

                exec dbo.USP_FINANCIALTRANSACTION_ADD @ID=@ID, @CHANGEAGENTID=@CHANGEAGENTID, @CONSTITUENTID=@STUDENTID, @TYPECODE=105, @AMOUNT=@AMOUNT, @DATE=@PAYMENTDATE, @POSTDATE=@POSTDATE, @POSTSTATUSCODE=@POSTSTATUSCODE, @USERDEFINEDID=@RECEIVABLEPAYMENTID;

                -- Insert into the receivable payment

                insert into dbo.RECEIVABLEPAYMENT
                    (ID, PAYMENTMETHODCODE, REFERENCEDATE, REFERENCENUMBER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, CONSTITUENTID)
                values
                    (@ID, @PAYMENTMETHODCODE, @REFERENCEDATE, @REFERENCENUMBER, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE, @CONSTITUENTID);

                -- Based on the payment method save to a different field

                if (@PAYMENTMETHODCODE = 1) -- Check

                begin
                    -- Insert the extra check information

                    insert into dbo.RECEIVABLEPAYMENTCHECK
                        (ID, CHECKNUMBER, CHECKDATE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values
                        (@ID, @CHECKNUMBER, @CHECKDATE, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);
                end
                else if (@PAYMENTMETHODCODE = 2) -- Credit card

                begin
                    -- Insert the extra credit card information

                    insert into dbo.RECEIVABLEPAYMENTCREDITCARD
                        (ID, CARDHOLDERNAME, CREDITTYPECODEID, CREDITCARDPARTIALNUMBER, EXPIRESON, AUTHORIZATIONCODE, TRANSACTIONID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values
                        (@ID, @CARDHOLDERNAME, @CREDITTYPECODEID, @CREDITCARDPARTIALNUMBER, @EXPIRESON, @AUTHORIZATIONCODE, null, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);                    
                end
                else if (@PAYMENTMETHODCODE = 3) -- Direct debit

                begin
                    -- Insert the extra direct debit information

                    insert into dbo.RECEIVABLEPAYMENTDIRECTDEBIT
                        (ID, DIRECTDEBITRESULTCODE, CONSTITUENTACCOUNTID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values
                        (@ID, @DIRECTDEBITRESULTCODE, @CONSTITUENTACCOUNTID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);                    
                end
                else if (@PAYMENTMETHODCODE = 10) -- Other

                begin
                    -- Insert the extra direct debit information

                    insert into dbo.RECEIVABLEPAYMENTOTHERMETHOD
                        (ID, OTHERPAYMENTMETHODCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    values
                        (@ID, @OTHERPAYMENTMETHODCODEID, @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE);                    
                end

                declare @RECEIVABLEPAYMENTLINEITEMID uniqueidentifier;
                select top 1 @RECEIVABLEPAYMENTLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                from FINANCIALTRANSACTION
                    inner join FINANCIALTRANSACTIONLINEITEM
                        on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                where (FINANCIALTRANSACTION.ID = @ID) and
                        (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) and
                        (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0);

                set @APPLICATIONS = 
                    (select ID uniqueidentifier,
                            @RECEIVABLEPAYMENTLINEITEMID as SOURCELINEITEMID,
                            TARGETLINEITEMID,
                            AMOUNT,
                            POSTDATE,
                            POSTSTATUSCODE
                    from dbo.UFN_RECEIVABLEPAYMENTCREDITAPPLICATIONS_FROMITEMLISTXML(@APPLICATIONS)
                    for xml raw('ITEM'),type,elements,root('APPLICATIONS'),BINARY BASE64);

                exec dbo.USP_FINANCIALTRANSACTION_LINEITEMAPPLICATIONS_EDIT @RECEIVABLEPAYMENTLINEITEMID, null, @CHANGEAGENTID, @APPLICATIONS

            end