USP_VIEWBYDISBURSEMENT_DATALIST

CREATE procedure dbo.USP_VIEWBYDISBURSEMENT_DATALIST
(
    @FROMDATE datetime,
    @TODATE datetime,
    @VENDORID nvarchar(50) = null,
  @PROCESSORTRANSACTIONSETTLEMENTIMPORTID uniqueidentifier = null,
  @CURRENTAPPUSERID uniqueidentifier = null
)
as
begin

    declare @MERCHANTACCOUNTID as uniqueidentifier = null;
    declare @CHARGEBACKID as uniqueidentifier = null;

  declare @DETAILS 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
  )

  insert into @DETAILS
  (
    ID,
        TRANSACTIONID,
        PROCESSORTRANSACTIONSETTLEMENTIMPORTID,
        DISBURSEMENTDATE,
        TRANSACTIONDATE,
        CARDHOLDERNAME,
        AMOUNT,
        FEE,
        NETAMOUNT,
        CURRENCYID,
        STATUSCODE,
        VENDORID,
        DATABASETRANSACTIONID,
        CARDTYPE,
        UNMATCHEDDISBURSEMENTID,
    DISBURSEMENTID,
    MERCHANTACCOUNTID
  )
  select
    ID,
        TRANSACTIONID,
        PROCESSORTRANSACTIONSETTLEMENTIMPORTID,
        DISBURSEMENTDATE,
        TRANSACTIONDATE,
        CARDHOLDERNAME,
        AMOUNT,
        FEE,
        NETAMOUNT,
        CURRENCYID,
        STATUSCODE,
        VENDORID,
        DATABASETRANSACTIONID,
        CARDTYPE,
        UNMATCHEDDISBURSEMENTID,
    DISBURSEMENTID,
    MERCHANTACCOUNTID
  from
    dbo.UFN_DATALIST_DISBURSEMENTDETAILS(@FROMDATE, @TODATE, @VENDORID, @PROCESSORTRANSACTIONSETTLEMENTIMPORTID, @CURRENTAPPUSERID);

    -- Parent rows

    select
        PROCESSORTRANSACTIONSETTLEMENTIMPORTID as ID,
        null as TRANSACTIONID,
        null as PROCESSORTRANSACTIONSETTLEMENTIMPORTID,
        DISBURSEMENTDATE,
        null as TRANSACTIONDATE,
        null as CARDHOLDERNAME,
        sum(AMOUNT) as AMOUNT,
        sum(FEE) as FEE,
        sum(NETAMOUNT) as NETAMOUNT,
        CURRENCYID as CURRENCYID,
        -1 as STATUSCODE,
        VENDORID,
        null as DATABASETRANSACTIONID,
        cast(1 as int) as ISPARENT,
        DISBURSEMENTID,
        cast(PROCESSORTRANSACTIONSETTLEMENTIMPORTID as nvarchar(36)) + cast(CURRENCYID as nvarchar(36)) as GROUPID,
        cast(PROCESSORTRANSACTIONSETTLEMENTIMPORTID as nvarchar(36)) as GROUPPARENTID,
        @CHARGEBACKID as CHARGEBACKID,
        MERCHANTACCOUNTID,
        0 as CHALLENGEABLE,
        '' as CASENUMBER,
        null as CARDTYPE,
        null as UNMATCHEDDISBURSEMENTID
    from
        @DETAILS
    group by
        PROCESSORTRANSACTIONSETTLEMENTIMPORTID, VENDORID, CURRENCYID, DISBURSEMENTID, DISBURSEMENTDATE, MERCHANTACCOUNTID

    union all

    -- Child rows

    select
        ID,
        TRANSACTIONID,
        PROCESSORTRANSACTIONSETTLEMENTIMPORTID,
        DISBURSEMENTDATE,
        TRANSACTIONDATE,
        CARDHOLDERNAME,
        AMOUNT,
        FEE,
        NETAMOUNT,
        CURRENCYID,
        STATUSCODE,
        VENDORID,
        DATABASETRANSACTIONID,
        cast(0 as int) as ISPARENT,
        null as DISBURSEMENTID,
        cast(ID as nvarchar(36)) as GROUPID,
        cast(PROCESSORTRANSACTIONSETTLEMENTIMPORTID as nvarchar(36)) + cast(CURRENCYID as nvarchar(36)) as GROUPPARENTID,
        @CHARGEBACKID as CHARGEBACKID,
        MERCHANTACCOUNTID,
        0 as CHALLENGEABLE,
        '' as CASENUMBER,
        CARDTYPE,
        UNMATCHEDDISBURSEMENTID
    from
        @DETAILS

end