USP_REMOVECARESTRICTEDPAYMENTS_DIRECTDEBITPROCESS

            CREATE procedure dbo.USP_REMOVECARESTRICTEDPAYMENTS_DIRECTDEBITPROCESS
            (
            @BATCHID uniqueidentifier,
            @BATCHTYPE tinyint,
            @EXCEPTIONTABLENAME nvarchar(max)
            )
            as
            begin
            declare @SQL nvarchar(max)='',@ERRORMESSAGE nvarchar(max)='';

            Create table #RESTRICTEDPAYMENTS
            (
                Id int identity(1,1),
                PAYMENTID uniqueidentifier,
                RECORDTYPE nvarchar(200),
                BATCHREVENUEID uniqueidentifier,
                REASON nvarchar(max)
            )

            begin transaction;
            begin try
                if(@BATCHTYPE=1)
                    begin
                        insert into #RESTRICTEDPAYMENTS(BATCHREVENUEID,PAYMENTID,RECORDTYPE,REASON)
                        select FTLI.FINANCIALTRANSACTIONID,BATCHMEMBERSHIPDUES.ID,'MEMBERSHIPBATCH','California state law prohibits this transaction'
                        from dbo.BATCHMEMBERSHIPDUES 
            inner join dbo.CONSTITUENT on CONSTITUENT.ID = BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID 
                        left join dbo.ADDRESS on ADDRESS.CONSTITUENTID= BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID and ADDRESS.ISPRIMARY = 1
                        left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
                        left join dbo.STATE on ADDRESS.STATEID=STATE.ID
            left join MEMBERSHIPTRANSACTION MT on BATCHMEMBERSHIPDUES.EXISTINGMEMBERSHIPID=MT.MEMBERSHIPID
                        left join FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.ID = MT.REVENUESPLITID
                        where BATCHMEMBERSHIPDUES.BATCHID=@BATCHID
                        and ((isnull(COUNTRY.ISO3166,'')='US' and isnull(STATE.ABBREVIATION,'')='CA')
                        or (isnull(COUNTRY.ISO3166,'')='US' and isnull(STATE.ABBREVIATION,'')='' and CAST(LEFT(ADDRESS.POSTCODE,3) as int)  between 900 and 961)
                        or (isnull(COUNTRY.ISO3166,'')='' and isnull(STATE.ABBREVIATION,'')='' and CAST(LEFT(ADDRESS.POSTCODE,3) as int)  between 900 and 961)
                        or (isnull(COUNTRY.ISO3166,'')='' and isnull(STATE.ABBREVIATION,'')='' and isnull(ADDRESS.POSTCODE,'')='')
                        or (isnull(COUNTRY.ISO3166,'')='US' and isnull(STATE.ABBREVIATION,'')='' and isnull(ADDRESS.POSTCODE,'')=''));

                        insert into dbo.BATCHMEMBERSHIPDUESRESTRICTED(BATCHMEMBERSHIPDUESID,TOTALAMOUNT,[DATE],TRANSACTIONCURRENCYID,BILLTOCONSTITUENTID)
                        select BATCHMEMBERSHIPDUES.ID,BATCHMEMBERSHIPDUES.TOTALAMOUNT,BATCHMEMBERSHIPDUES.DATE,BATCHMEMBERSHIPDUES.TRANSACTIONCURRENCYID,BATCHMEMBERSHIPDUES.BILLTOCONSTITUENTID
                        from dbo.BATCHMEMBERSHIPDUES inner join #RESTRICTEDPAYMENTS on BATCHMEMBERSHIPDUES.ID=#RESTRICTEDPAYMENTS.PAYMENTID;

                        if(@@rowcount>0)
                        delete BATCHMEMBERSHIPDUES from 
                        dbo.BATCHMEMBERSHIPDUES inner join #RESTRICTEDPAYMENTS on BATCHMEMBERSHIPDUES.ID=#RESTRICTEDPAYMENTS.PAYMENTID
                        where BATCHID=@BATCHID ;

            update REVENUESCHEDULE
                        set REVENUESCHEDULE.ISPENDING = 0
                        from dbo.REVENUESCHEDULE inner join #RESTRICTEDPAYMENTS on REVENUESCHEDULE.id=#RESTRICTEDPAYMENTS.BATCHREVENUEID;
                    end
                else
                    begin
                        insert into #RESTRICTEDPAYMENTS(BATCHREVENUEID,PAYMENTID,RECORDTYPE,REASON)
                        select BATCHREVENUE.ID,case when BATCHREVENUE.REVENUEID  IS NULL then BATCHREVENUE.PAYINGPENDINGREVENUEID else BATCHREVENUE.REVENUEID end As PAYMENTID,
                        'FINANCIALTRANSACTION','California state law prohibits this transaction'
                        from dbo.BATCHREVENUE
            inner join dbo.CONSTITUENT on CONSTITUENT.ID = BATCHREVENUE.CONSTITUENTID 
                        left join dbo.ADDRESS on ADDRESS.CONSTITUENTID= BATCHREVENUE.CONSTITUENTID and ADDRESS.ISPRIMARY = 1
                        left join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID
                        left join dbo.STATE on ADDRESS.STATEID=STATE.ID
                        where BATCHID=@BATCHID
                        and ((isnull(COUNTRY.ISO3166,'')='US' and isnull(STATE.ABBREVIATION,'')='CA')
                        or (isnull(COUNTRY.ISO3166,'')='US' and isnull(STATE.ABBREVIATION,'')='' and CAST(LEFT(ADDRESS.POSTCODE,3) as int)  between 900 and 961)
                        or (isnull(COUNTRY.ISO3166,'')='' and isnull(STATE.ABBREVIATION,'')='' and CAST(LEFT(ADDRESS.POSTCODE,3) as int)  between 900 and 961)
                        or (isnull(COUNTRY.ISO3166,'')='' and isnull(STATE.ABBREVIATION,'')='' and isnull(ADDRESS.POSTCODE,'')='')
                        or (isnull(COUNTRY.ISO3166,'')='US' and isnull(STATE.ABBREVIATION,'')='' and isnull(ADDRESS.POSTCODE,'')=''));

                        if(@@rowcount>0)
                        delete BATCHREVENUE from dbo.BATCHREVENUE inner join #RESTRICTEDPAYMENTS on BATCHREVENUE.ID=#RESTRICTEDPAYMENTS.BATCHREVENUEID
                        where BATCHID=@BATCHID; 

                        update REVENUESCHEDULE
                        set REVENUESCHEDULE.ISPENDING = 0
                        from dbo.REVENUESCHEDULE inner join #RESTRICTEDPAYMENTS on REVENUESCHEDULE.id=#RESTRICTEDPAYMENTS.PAYMENTID;

      end    

                set @SQL='Insert into dbo.'+@EXCEPTIONTABLENAME+'(RECORDTYPE,RECORDID,REASON)  select RECORDTYPE, PAYMENTID, REASON from dbo.#RESTRICTEDPAYMENTS'
                exec sp_executesql @SQL

                commit transaction;
                end try 
                begin catch 
                    set @ERRORMESSAGE=ERROR_MESSAGE()
                    rollback transaction;
                    raiserror('%s', 13, 1, @ERRORMESSAGE);
                end catch 

            drop table if exists #RESTRICTEDPAYMENTS
            end