USP_DATALIST_INSTALLMENTSBYTRANSACTION
This datalist returns a list of pledge installments by revenue.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @PAYMENTSWRITEOFFSONLY | bit | IN | Payments/Write-offs only |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_INSTALLMENTSBYTRANSACTION
(
@ID uniqueidentifier,
@PAYMENTSWRITEOFFSONLY bit = 0
)
as
begin
set nocount on;
select * from
(
select
INSTALLMENT.ID as ID,
INSTALLMENT.SEQUENCE,
LISTORDER = 0,
ACTIVITY = 'Installment ' + cast(INSTALLMENT.SEQUENCE as nvarchar(3)),
cast(INSTALLMENT.DATE as datetime) as Date,
INSTALLMENT.TRANSACTIONAMOUNT as AMOUNT,
PAIDBY = null,
PAYMETHOD = NULL,
BALANCE = coalesce(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID),0),
ISVISIBLE =
case
when @PAYMENTSWRITEOFFSONLY = 1 then 0
else 1
end,
TRANSACTIONID = null,
RECORDTYPE = 0,
dbo.UDA_BUILDLIST(DISTINCT case INSTALLMENTSPLIT.AMOUNT when 0 then null else coalesce(DESIGNATION.NAME, 'None (Earned income)') end) DESIGNATIONLIST,
PARENT = null,
INSTALLMENT.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID,
INSTALLMENT.ID as CHILDORPARENTID,
INSTALLMENT.TRANSACTIONRECEIPTAMOUNT as RECEIPTAMOUNT
from
dbo.INSTALLMENT
inner join
dbo.FINANCIALTRANSACTION on INSTALLMENT.REVENUEID = FINANCIALTRANSACTION.ID
inner join
dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join
dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
left join
dbo.DESIGNATION on DESIGNATION.ID = INSTALLMENTSPLIT.DESIGNATIONID
where
FINANCIALTRANSACTION.ID = @ID
and FINANCIALTRANSACTION.DELETEDON is null
group by
INSTALLMENT.ID,
INSTALLMENT.SEQUENCE,
INSTALLMENT.DATE,
INSTALLMENT.TRANSACTIONAMOUNT,
INSTALLMENT.TRANSACTIONCURRENCYID,
INSTALLMENT.TRANSACTIONRECEIPTAMOUNT
union all
select
null as ID,
INSTALLMENT.SEQUENCE,
LISTORDER = 1,
ACTIVITY = 'Payment',
cast(PAYMENT.DATE as datetime) as Date,
sum(INSTALLMENTSPLITPAYMENT.AMOUNT) AMOUNT, -- In installment transaction currency
PAIDBY = NF.NAME,
PAYMETHOD = REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
BALANCE = null,
ISVISIBLE = 1,
PAYMENT.ID as TRANSACTIONID,
RECORDTYPE = 1,
'' DESIGNATIONLIST,
PARENT = case when @PAYMENTSWRITEOFFSONLY = 1 then null
else INSTALLMENT.ID end,
INSTALLMENT.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID,
PAYMENT.ID as CHILDORPARENTID,
null as RECEIPTAMOUNT
from
dbo.INSTALLMENT
inner join
dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
inner join
dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
inner join
dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = PAYMENTSPLIT.ID
inner join
dbo.REVENUESPLIT_EXT on PAYMENTSPLIT.ID = REVENUESPLIT_EXT.ID
inner join
dbo.FINANCIALTRANSACTION PAYMENT on PAYMENTSPLIT.FINANCIALTRANSACTIONID = PAYMENT.ID
inner join
dbo.REVENUE_EXT on PAYMENT.ID = REVENUE_EXT.ID
inner join
dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = PAYMENT.ID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PAYMENT.CONSTITUENTID) NF
where INSTALLMENT.REVENUEID = @ID
and PAYMENT.DELETEDON is null
and PAYMENTSPLIT.DELETEDON is null
and PAYMENTSPLIT.TYPECODE <> 1
group by
INSTALLMENT.SEQUENCE,
cast(PAYMENT.DATE as datetime),
NF.NAME,
REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
PAYMENT.ID,
INSTALLMENT.ID,
INSTALLMENT.TRANSACTIONCURRENCYID,
REVENUE_EXT.RECEIPTAMOUNT
union all
select
null as ID,
INSTALLMENT.SEQUENCE,
LISTORDER = 2,
ACTIVITY = 'Write-off',
cast(WRITEOFF.DATE as datetime) as Date,
-- Needs to be updated when we address installment split writeoffs
sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT,
PAIDBY = null,
PAYMETHOD = null,
BALANCE = null,
ISVISIBLE = 1,
ID = null,
RECORDTYPE = 2,
'' DESIGNATIONLIST,
PARENT = case when @PAYMENTSWRITEOFFSONLY = 1 then null
else INSTALLMENT.ID end,
INSTALLMENT.TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID,
WRITEOFF.ID as CHILDORPARENTID,
null as RECEIPTAMOUNT
from
dbo.INSTALLMENT
inner join
dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
inner join
dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
inner join
dbo.WRITEOFF on INSTALLMENTSPLITWRITEOFF.WRITEOFFID = WRITEOFF.ID
where
INSTALLMENT.REVENUEID = @ID
group by
INSTALLMENT.SEQUENCE,
WRITEOFF.DATE,
WRITEOFF.ID,
INSTALLMENT.ID,
INSTALLMENT.TRANSACTIONCURRENCYID,
INSTALLMENT.TRANSACTIONRECEIPTAMOUNT
) as INSTALLMENTDATA
where ISVISIBLE = 1
order by SEQUENCE, LISTORDER, Date;
end