USP_DATAFORMTEMPLATE_VIEW_MERCHANT_BY_ADDRESS

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

Parameters

Parameter	Parameter Type	Mode	Description
@ID	nvarchar(12)	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_ADDRESS (
          @ID nvarchar(12),
          @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 @ZIPCODE nvarchar(5);
          if len(@ID) > 5
            set @ZIPCODE = substring(@ID, 1, 5);
          else
            set @ZIPCODE = @ID;

          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.ZIPCODE = @ZIPCODE and L.ISACTIVE=1;

          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;