USP_DATAFORMTEMPLATE_VIEW_MERCHANT_BY_CITY

The load procedure used by the view dataform template "Local City Merchant Account Lookup Data View Data Form"

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(258)	IN	The input ID parameter used to load the fields defined on the form.
@DATALOADED	bit	INOUT	Output parameter indicating whether or not data was actually loaded.
@MERCHANTACCOUNT	uniqueidentifier	INOUT	MERCHANTACCOUNT
@SUPPORTEDCARD	xml	INOUT	SUPPORTEDCARD
@DESIGNATIONID	uniqueidentifier	INOUT	DESIGNATIONID
@LOCALCORPID	uniqueidentifier	INOUT	LOCALCORPID
@LOCALCORPNAME	nvarchar(510)	INOUT	LOCALCORPNAME

Definition

Copy



        CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MERCHANT_BY_CITY (
          @ID nvarchar(258),
          @DATALOADED bit = 0 output,
          @MERCHANTACCOUNT uniqueidentifier = null output,
          @SUPPORTEDCARD xml = null output,
          @DESIGNATIONID uniqueidentifier = null output,
          @LOCALCORPID uniqueidentifier = null output,
          @LOCALCORPNAME nvarchar(510) = null output
        ) 
        as
            set nocount on;

          set @DATALOADED = 0;

          declare @CITY nvarchar(255) = '';
          declare @STATEABBREV nvarchar(2) = '';
          declare @DELIMITERPOS int;
          declare @TERRITORY nvarchar(5) = null
          declare @DIVISION nvarchar(5) = null

          declare @RECCOUNT integer = 0

          set @ID = ltrim(rtrim(@ID));
          set @DELIMITERPOS = CHARINDEX('@', @ID);

          if @DELIMITERPOS > 0
          begin
            set @CITY = substring(@ID, 1, @DELIMITERPOS - 1);
            set @STATEABBREV = substring(@ID, @DELIMITERPOS + 1, 2);
          end

          select @RECCOUNT = count(ID) from dbo.LOCALCORP L where L.STATEABBREV = @STATEABBREV and L.CITY = @CITY and L.ISACTIVE = 1;

          if @RECCOUNT > 1
            begin
              select top 1 @TERRITORY = L.TERRITORYABBREV, 
                @DIVISION = L.DIVISIONABBREV 
              from dbo.LOCALCORP L(nolock) 
              where L.STATEABBREV = @STATEABBREV and L.CITY = @CITY and L.ISACTIVE = 1 and L.ZIPCODE > '' and L.LOCALCORP > '';

              select  top 1 @DATALOADED = 1,
                @MERCHANTACCOUNT = M.BBPSID, 
                @SUPPORTEDCARD = M.XMLTransactionInfo,
                @DESIGNATIONID = B.DESIGNATIONID,
                @LOCALCORPID = L.ID,
                @LOCALCORPNAME = ISNULL(L.LOCALCORP,'')
              from dbo.LOCALCORP L(nolock)
              inner join dbo.BBNCDESIGNATIONIDMAP B(nolock) on (B.DESIGNATIONID = L.DESIGNATIONID)
              inner join dbo.ClientMerchantAccounts M(nolock) on (M.BBPSID = L.BBPSMERCHANTACCTID)
              where L.TERRITORYABBREV = @TERRITORY and L.DIVISIONABBREV = @DIVISION and L.ISACTIVE = 1 and L.ZIPCODE = '' and L.LOCALCORP = '';  
            end
          else
            begin
              select  top 1 @DATALOADED = 1,
                @MERCHANTACCOUNT = M.BBPSID, 
                @SUPPORTEDCARD = M.XMLTransactionInfo,
                @DESIGNATIONID = B.DESIGNATIONID,
                @LOCALCORPID = L.ID,
                @LOCALCORPNAME = ISNULL( L.LOCALCORP, '')
              from dbo.LOCALCORP L(nolock)
              inner join dbo.BBNCDESIGNATIONIDMAP B(nolock) on (B.DESIGNATIONID = L.DESIGNATIONID)
              inner join dbo.ClientMerchantAccounts M(nolock) on (M.BBPSID = L.BBPSMERCHANTACCTID)
              where L.CITY = @CITY and L.STATEABBREV = @STATEABBREV and L.ISACTIVE = 1;            
            end

          if @DATALOADED = 0
          begin
            select  top 1 @DATALOADED = 1,
              @MERCHANTACCOUNT = M.BBPSID, 
              @SUPPORTEDCARD = M.XMLTransactionInfo,
              @DESIGNATIONID = B.DESIGNATIONID,
              @LOCALCORPID = L.ID,
              @LOCALCORPNAME = ISNULL( L.LOCALCORP, '')
            from dbo.LOCALCORP L(nolock)
            inner join dbo.BBNCDESIGNATIONIDMAP B(nolock) on (B.DESIGNATIONID = L.DESIGNATIONID)
            inner join dbo.ClientMerchantAccounts M(nolock) on (M.BBPSID = L.BBPSMERCHANTACCTID)
            where L.TERRITORYABBREV = '' 
              and L.DIVISIONABBREV = ''
              and L.LOCALCORP = ''
              and L.MERCHANTACCT > ''
              and L.ISACTIVE = 1;
          end

          return 0;