USP_CREDITCARD_SAVE

            CREATE procedure dbo.USP_CREDITCARD_SAVE
            (
                @ID uniqueidentifier output,
                @CREDITCARDTOKEN uniqueidentifier,
                @CARDHOLDERNAME nvarchar(255),
                @CREDITCARDPARTIALNUMBER nvarchar(4),
                @CREDITTYPECODEID uniqueidentifier,
                @EXPIRESON dbo.UDT_FUZZYDATE,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null,
                @VALIDFROM dbo.UDT_FUZZYDATE = '00000000',
                @ISSUENUMBER nvarchar(3) = '',
                @PREVIOUSCREDITCARDID uniqueidentifier = null,
                @OWNINGRECORDID uniqueidentifier = null,
                @ISTRANSIENT bit = 0
            )
            as
                set nocount on

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

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate()

                -- Only save the data if the card number is set.  If that's set, validation on the server

                -- should have already enforced that CARDHOLDERNAME and EXPIRESON are set.

                if (@CREDITCARDPARTIALNUMBER is not null and rtrim(ltrim(@CREDITCARDPARTIALNUMBER)) <> '') or (len(@CARDHOLDERNAME)>0) or (@EXPIRESON <> '00000000') or (@CREDITTYPECODEID is not null)
                begin
                    -- Check if the token already exists.  If it does, that record can be used.

                    if @CREDITCARDTOKEN is not null
                        select top 1 @ID = ID from dbo.CREDITCARD where CREDITCARDTOKEN = @CREDITCARDTOKEN
                    else
                    begin
                        -- If the token is null, see if another credit card record has the same data and use that row.

                        -- We need to check all fields here (including CREDITTYPECODEID) so we don't update seemingly

                        -- matching cards with different types (i.e. expiration is the same, or other fields are blank)

                        select top 1 @ID = ID from dbo.CREDITCARD
                        where
                            CARDHOLDERNAME = @CARDHOLDERNAME and
                            CREDITCARDPARTIALNUMBER = @CREDITCARDPARTIALNUMBER and
                            EXPIRESON = @EXPIRESON and
                            VALIDFROM = coalesce(@VALIDFROM, '00000000') and
                            ISSUENUMBER = coalesce(@ISSUENUMBER, '') and
                            coalesce(CREDITTYPECODEID, '00000000-0000-0000-0000-000000000000') = coalesce(@CREDITTYPECODEID, '00000000-0000-0000-0000-000000000000')

                        -- If the record already had an existing credit card id, only reuse it if no other 

                        -- records are referencing it

                        if @ID is null and @PREVIOUSCREDITCARDID is not null
                        begin
                            declare @ISIDINUSEUPDATECHECK bit
                            set @ISIDINUSEUPDATECHECK = 0
                            exec dbo.USP_CREDITCARDID_ISINUSE @PREVIOUSCREDITCARDID, @OWNINGRECORDID, @ISIDINUSEUPDATECHECK output

                            if @ISIDINUSEUPDATECHECK = 0
                                set @ID = @PREVIOUSCREDITCARDID
                        end
                    end


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

                        insert into dbo.CREDITCARD 
                        (
                            [ID], 
                            [CREDITCARDTOKEN], 
                            [CARDHOLDERNAME], 
                            [CREDITTYPECODEID], 
                            [CREDITCARDPARTIALNUMBER], 
                            [EXPIRESON],
                            [VALIDFROM],
                            [ISSUENUMBER],
                            [ISTRANSIENT],
                            [ADDEDBYID], [CHANGEDBYID], [DATEADDED], [DATECHANGED]
                        )
                        values 
                        (
                            @ID, 
                            @CREDITCARDTOKEN, 
                            @CARDHOLDERNAME, 
                            @CREDITTYPECODEID, 
                            @CREDITCARDPARTIALNUMBER, 
                            @EXPIRESON,
                            @VALIDFROM,
                            @ISSUENUMBER,
                            coalesce(@ISTRANSIENT, 0),
                            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                        )
                    end
                    else
                    begin
                        -- The Is Transient flag doesn't change so it isn't included in the update statement


                        -- **NOTE** Update the where clause if you add a new column to be updated since

                        -- we check to make sure a column has changed before doing the update.

                        update dbo.CREDITCARD set
                            CARDHOLDERNAME = @CARDHOLDERNAME,
                            CREDITCARDPARTIALNUMBER = @CREDITCARDPARTIALNUMBER,
                            CREDITTYPECODEID = @CREDITTYPECODEID,
                            EXPIRESON = @EXPIRESON,
                            VALIDFROM = @VALIDFROM,
                            ISSUENUMBER = @ISSUENUMBER,
                            CHANGEDBYID = @CHANGEAGENTID,
                            DATECHANGED = @CURRENTDATE
                        where 
                            ID = @ID and
                            -- Make sure the row has changed before doing the update so we don't create an unnecessary exclusive lock

                            (
                                CARDHOLDERNAME <> @CARDHOLDERNAME or
                                CREDITCARDPARTIALNUMBER <> @CREDITCARDPARTIALNUMBER or
                                coalesce(CREDITTYPECODEID, '00000000-0000-0000-0000-000000000000') <> coalesce(@CREDITTYPECODEID, '00000000-0000-0000-0000-000000000000') or
                                EXPIRESON <> @EXPIRESON or
                                VALIDFROM <> @VALIDFROM
                            )
                    end
                end
                else
                    set @ID = null