UFN_DATALIST_DISBURSEMENTDETAILS

  CREATE function dbo.UFN_DATALIST_DISBURSEMENTDETAILS
    (
      @FROMDATE datetime = null,
      @TODATE datetime = null,
      @VENDORID nvarchar(50) = null,
      @PROCESSORTRANSACTIONSETTLEMENTIMPORTID uniqueidentifier = null,
      @CURRENTAPPUSERID uniqueidentifier = null
    )
    returns @DETAIL table
    (
      ID uniqueidentifier,
      TRANSACTIONID uniqueidentifier,
      PROCESSORTRANSACTIONSETTLEMENTIMPORTID uniqueidentifier,
      DISBURSEMENTDATE datetime,
      TRANSACTIONDATE datetime,
      CARDHOLDERNAME nvarchar(255),
      AMOUNT money,
      FEE money,
      NETAMOUNT money,
      CURRENCYID uniqueidentifier,
      STATUSCODE tinyint,
      VENDORID nvarchar(50),
      DATABASETRANSACTIONID uniqueidentifier,
      CARDTYPE nvarchar(255),
      UNMATCHEDDISBURSEMENTID uniqueidentifier,
      DISBURSEMENTID uniqueidentifier,
      MERCHANTACCOUNTID uniqueidentifier
    )
    with execute as caller
    as begin

    if @VENDORID = '0'
      set @VENDORID = '';
    else if @VENDORID = ''
      set @VENDORID = null;

      declare @SITEFILTERMODE tinyint = 0,
              @SITESSELECTED nvarchar(56) = N'<?xml version="1.0" encoding="utf-16"?><SITESSELECTED />',
              @SECURITYFEATUREID uniqueidentifier = 'AC71DAA0-79C1-49EC-BD76-914098BD0F5D',
              @SECURITYFEATURETYPE tinyint = 2;

    insert into @DETAIL
    (
      ID,
      TRANSACTIONID,
      PROCESSORTRANSACTIONSETTLEMENTIMPORTID,
      DISBURSEMENTDATE,
      TRANSACTIONDATE,
      CARDHOLDERNAME,
      AMOUNT,
      FEE,
      NETAMOUNT,
      CURRENCYID,
      STATUSCODE,
      VENDORID,
      DATABASETRANSACTIONID,
      CARDTYPE,
      UNMATCHEDDISBURSEMENTID,
      DISBURSEMENTID,
      MERCHANTACCOUNTID
    )
    select
      ROW.ID,
      ROW.TRANSACTIONID,
      ROW.PROCESSORTRANSACTIONSETTLEMENTIMPORTID,
      isnull(IMPORT.DISBURSEMENTDATE, IMPORT.DATEADDED),
      ROW.TRANSACTIONDATE,
      ROW.CARDHOLDERNAME,
      ROW.AMOUNT,
      ROW.FEE,
      ROW.NETAMOUNT,
      ROW.CURRENCYID,
      ROW.STATUSCODE,
      ROW.VENDORID,
      isnull(ROW.DATABASETRANSACTIONID, ROW.ID),
      ROW.CARDTYPE,
      U.ID as UNMATCHEDDISBURSEMENTID,
      IMPORT.DISBURSEMENTID,
      ROW.MERCHANTACCOUNTID
    from
      dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORTROW ROW
      inner join dbo.PROCESSORTRANSACTIONSETTLEMENTIMPORT IMPORT on IMPORT.ID = ROW.PROCESSORTRANSACTIONSETTLEMENTIMPORTID
      left join dbo.UNSETTLEDCREDITCARDTRANSACTION U on U.PROCESSORTRANSACTIONSETTLEMENTIMPORTROWID = ROW.ID
      left join dbo.UFN_MERCHANTACCOUNT_SITESECUREDATALIST(@CURRENTAPPUSERID,@SITEFILTERMODE,@SITESSELECTED,@SECURITYFEATUREID,@SECURITYFEATURETYPE) SITES
        on SITES.MERCHANTACCOUNTID = ROW.MERCHANTACCOUNTID
    where
      (@TODATE is null or cast(isnull(IMPORT.DISBURSEMENTDATE, IMPORT.DATEADDED) as date) <= @TODATE)
      and (@FROMDATE is null or cast(isnull(IMPORT.DISBURSEMENTDATE, IMPORT.DATEADDED) as date) >= @FROMDATE)
      and (@VENDORID is null or ROW.VENDORID = @VENDORID)
      and (@PROCESSORTRANSACTIONSETTLEMENTIMPORTID is null or IMPORT.ID = @PROCESSORTRANSACTIONSETTLEMENTIMPORTID)
      and (@CURRENTAPPUSERID is null or SITES.MERCHANTACCOUNTID is null or SITES.ACCESSGRANTED = 1)

    return;

  end