UFN_BILLINGINDIVIDUALTRANSACTIONDETAILS

            CREATE function dbo.UFN_BILLINGINDIVIDUALTRANSACTIONDETAILS
            (
                @CONSTITUENTID uniqueidentifier,
                @TYPECODE tinyint = 0,      -- see BillingIndividualActivity.DataList.xml for details

                @STATUSCODE tinyint = 0,    -- see BillingIndividualActivity.DataList.xml for details

                @BILLINGSTARTDATE datetime,
                @BILLINGENDDATE datetime,
                @BILLINGSTARTDUEDATE datetime,
                @BILLINGENDDUEDATE datetime
            )
            returns @TRANSACTIONS table
                (
                    ID uniqueidentifier,
                    DATE datetime,
                    DUEDATE datetime,
                    DAYSLATE integer,
                    DUEDATEDISPLAY nvarchar(50),
                    TYPE nvarchar(50),
                    TYPECODE tinyint,
                    DESCRIPTION nvarchar(200),
                    AMOUNT money,
                    AMOUNTREMAINING money,
                    PAIDBY nvarchar(100),
                    STATUSCODE tinyint,
                    STATUS nvarchar(100)
                )
            as
            begin  
                insert into @TRANSACTIONS
                select ID,
                        DATE,
                        DUEDATE,
                        DAYSLATE,
                        DUEDATEDISPLAY,
                        TYPE,
                        TYPECODE,
                        DESCRIPTION,
                        TRANSACTIONAMOUNT,
                        AMOUNTREMAINING,
                        PAIDBY,
                        STATUSCODE,
                        STATUS
                from (
                    select FINANCIALTRANSACTION.ID,
                            FINANCIALTRANSACTION.DATE,
                            CHARGE.DUEDATE,
                            case when ((FINANCIALTRANSACTION.TRANSACTIONAMOUNT > sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) and 
                                      (FINANCIALTRANSACTION.TYPECODE = 104) and 
                                      (CHARGE.DUEDATE < dbo.UFN_DATE_GETEARLIESTTIME(getdate()))) then
                                    datediff(d, CHARGE.DUEDATE, dbo.UFN_DATE_GETEARLIESTTIME(getdate()))
                                else
                                    0
                            end as DAYSLATE,
                            case when ((FINANCIALTRANSACTION.TRANSACTIONAMOUNT > sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) and 
                                      (FINANCIALTRANSACTION.TYPECODE = 104) and 
                                      (CHARGE.DUEDATE < dbo.UFN_DATE_GETEARLIESTTIME(getdate()))) then
                                    CONVERT(nvarchar(12), CHARGE.DUEDATE, 101) + ' - ' + cast(datediff(d, CHARGE.DUEDATE, dbo.UFN_DATE_GETEARLIESTTIME(getdate())) as nvarchar(12)) + ' days late'
                                else
                                    CONVERT(nvarchar(12), CHARGE.DUEDATE, 101)
                            end as DUEDATEDISPLAY,
                            FINANCIALTRANSACTION.TYPE,
                            FINANCIALTRANSACTION.TYPECODE,
                            case when (len(FINANCIALTRANSACTIONLINEITEM.DESCRIPTION) > 0) then
                                FINANCIALTRANSACTIONLINEITEM.DESCRIPTION
                            when (len(FINANCIALTRANSACTION.DESCRIPTION) > 0) then
                                FINANCIALTRANSACTION.DESCRIPTION
                            when ((not BILLINGITEM.NAME is null) and (len(BILLINGITEM.NAME) > 0)) then
                                BILLINGITEM.NAME
                            else
                                FINANCIALTRANSACTION.DESCRIPTION
                            end as DESCRIPTION,
                            FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
          FINANCIALTRANSACTION.TRANSACTIONAMOUNT - sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0)) as AMOUNTREMAINING,
                            CONSTITUENT.NAME as PAIDBY,
                            case when (((FINANCIALTRANSACTION.TRANSACTIONAMOUNT - sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) = 0) and 
                                      (FINANCIALTRANSACTION.TYPECODE = 104) and 
                                      (exists (select FINANCIALTRANSACTIONLINEITEM_INNER.ID 
                                                from dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_INNER
                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION_INNER
                                                        on FINANCIALTRANSACTIONLINEITEM_INNER.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION_INNER.TARGETLINEITEMID
                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT_INNER
                                                        on FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT_INNER.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION_INNER.SOURCELINEITEMID
                                                    inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT
                                                        on FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.ID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT_INNER.FINANCIALTRANSACTIONID
                                                where (FINANCIALTRANSACTIONLINEITEM_INNER.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID) and
                                                      (((FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE > FINANCIALTRANSACTION.DATE) and (FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE > CHARGE.DUEDATE)) or
                                                       ((FINANCIALTRANSACTION.DATE > FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE) and (FINANCIALTRANSACTION.DATE > CHARGE.DUEDATE)))))) then
                                    5
                                when ((FINANCIALTRANSACTION.TRANSACTIONAMOUNT - sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) = 0) then
                                    1
                                when ((FINANCIALTRANSACTION.TRANSACTIONAMOUNT > sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) and 
                                      (FINANCIALTRANSACTION.TYPECODE = 104) and 
                                      (CHARGE.DUEDATE < dbo.UFN_DATE_GETEARLIESTTIME(getdate()))) then
                                    4
                                when (sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0)) = 0) then
                                    3
                                when (FINANCIALTRANSACTION.TRANSACTIONAMOUNT > sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) then 
                                    2
                            end as STATUSCODE,
                            case when (((FINANCIALTRANSACTION.TRANSACTIONAMOUNT - sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) = 0) and 
                                      (FINANCIALTRANSACTION.TYPECODE = 104) and 
                                      (exists (select FINANCIALTRANSACTIONLINEITEM_INNER.ID 
                                                from dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_INNER
                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION_INNER
                                                        on FINANCIALTRANSACTIONLINEITEM_INNER.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION_INNER.TARGETLINEITEMID
                                                    inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT_INNER
                                                        on FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT_INNER.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION_INNER.SOURCELINEITEMID
                                                    inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT
                                                        on FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.ID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT_INNER.FINANCIALTRANSACTIONID
                                                where (FINANCIALTRANSACTIONLINEITEM_INNER.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID) and
                                                      (((FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE > FINANCIALTRANSACTION.DATE) and (FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE > CHARGE.DUEDATE)) or
                                                       ((FINANCIALTRANSACTION.DATE > FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE) and (FINANCIALTRANSACTION.DATE > CHARGE.DUEDATE)))))) then
                                    'Paid late'
                                when ((FINANCIALTRANSACTION.TRANSACTIONAMOUNT - sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) = 0) then
                                    'Paid/Applied'
                                when ((FINANCIALTRANSACTION.TRANSACTIONAMOUNT > sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) and 
                                      (FINANCIALTRANSACTION.TYPECODE = 104) and 
                                      (CHARGE.DUEDATE < dbo.UFN_DATE_GETEARLIESTTIME(getdate()))) then
                                    'Overdue'
                                when (sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0)) = 0) then
                                    'Open/Unpaid'
                                when (FINANCIALTRANSACTION.TRANSACTIONAMOUNT > sum(isnull(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT, 0))) then
                                    'Partially paid/applied'
                            end as STATUS
                    from dbo.FINANCIALTRANSACTION
                        inner join dbo.FINANCIALTRANSACTIONLINEITEM
                            on (FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID) and
                                (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0) and -- Standard

                                (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null)
                        left outer join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
                            on (((FINANCIALTRANSACTION.TYPECODE = 104) and
                                 (FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID)) or
                                ((FINANCIALTRANSACTION.TYPECODE in (105, 106)) and
                                 (FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID  = FINANCIALTRANSACTIONLINEITEM.ID))) and
                               (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null)
                        left outer join dbo.CHARGE
                            on (FINANCIALTRANSACTION.ID = CHARGE.ID) and
                                (FINANCIALTRANSACTION.TYPECODE = 104)
                        left outer join dbo.CHARGELINEITEM
                            on FINANCIALTRANSACTIONLINEITEM.ID = CHARGELINEITEM.ID
                        left outer join dbo.RECEIVABLECREDITLINEITEM
                            on FINANCIALTRANSACTIONLINEITEM.ID = RECEIVABLECREDITLINEITEM.ID
                        left outer join dbo.BILLINGITEM
               on (CHARGELINEITEM.BILLINGITEMID = BILLINGITEM.ID) or 
                                (RECEIVABLECREDITLINEITEM.BILLINGITEMID = BILLINGITEM.ID)
                        left outer join dbo.RECEIVABLEPAYMENT
                            on (FINANCIALTRANSACTION.ID = RECEIVABLEPAYMENT.ID) and
                                (FINANCIALTRANSACTION.TYPECODE = 105)
                        left outer join dbo.CONSTITUENT
                            on RECEIVABLEPAYMENT.CONSTITUENTID = CONSTITUENT.ID
                    where (FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID) and
                            ((isnull(@TYPECODE, 0)  = 0) or (@TYPECODE = FINANCIALTRANSACTION.TYPECODE) or ((@TYPECODE = 1) and (FINANCIALTRANSACTION.TYPECODE in (105, 106)))) and -- TYPE filter

                            ((@BILLINGSTARTDATE is null) or (@BILLINGSTARTDATE <= FINANCIALTRANSACTION.DATE)) and 
                            ((@BILLINGENDDATE is null) or (@BILLINGENDDATE >= FINANCIALTRANSACTION.DATE)) and 
                            ((@BILLINGSTARTDUEDATE is null) or ((FINANCIALTRANSACTION.TYPECODE = 104) and (@BILLINGSTARTDUEDATE <= CHARGE.DUEDATE))) and 
                            ((@BILLINGENDDUEDATE is null) or ((FINANCIALTRANSACTION.TYPECODE = 104) and (@BILLINGENDDUEDATE >= CHARGE.DUEDATE)))
                    group by FINANCIALTRANSACTION.ID,
                                FINANCIALTRANSACTION.DATE,
                                CHARGE.DUEDATE,
                                FINANCIALTRANSACTIONLINEITEM.DESCRIPTION,
                                FINANCIALTRANSACTION.DESCRIPTION,
                                BILLINGITEM.NAME,
                                FINANCIALTRANSACTION.TYPE,
                                FINANCIALTRANSACTION.TYPECODE,
                                FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
                                CONSTITUENT.NAME
                    ) as INNERSELECT
                where ((isnull(@STATUSCODE, 0) = 0) or 
                        ((@STATUSCODE != 3) and (@STATUSCODE = INNERSELECT.STATUSCODE)) or
                        ((@STATUSCODE = 3) and (INNERSELECT.STATUSCODE in (2,3,4))))
                order by DATE asc;

                return;
            end