USP_DATALIST_INSTALLMENTSBYCONSTITUENT
Returns a list of all installments for a constituent
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
Definition
Copy
CREATE procedure [dbo].[USP_DATALIST_INSTALLMENTSBYCONSTITUENT]
(
@CONSTITUENTID uniqueidentifier
)
as
begin
select * from
(
select
REVENUE.ID as REVENUEID,
REVENUE.LOOKUPID as REVENUELOOKUPID,
INSTALLMENT.ID as ID,
INSTALLMENT.SEQUENCE,
LISTORDER = 0,
ACTIVITY = 'Installment ' + cast(INSTALLMENT.SEQUENCE as nvarchar(3)),
INSTALLMENT.DATE as Date,
INSTALLMENT.TRANSACTIONAMOUNT as AMOUNT,
PAIDBY = null,
PAYMETHOD = NULL,
BALANCE = coalesce(dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID),0),
TRANSACTIONID = null,
TRANSACTIONLOOKUPID = '',
TRANSACTIONAMOUNT = 0,
RECORDTYPE = 0,
dbo.UDA_BUILDLIST(DISTINCT DESIGNATION.NAME) DESIGNATIONLIST,
PARENT = null
from
dbo.INSTALLMENT
inner join
dbo.REVENUE on INSTALLMENT.REVENUEID = REVENUE.ID
inner join
dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
inner join
dbo.DESIGNATION on DESIGNATION.ID = INSTALLMENTSPLIT.DESIGNATIONID
where
REVENUE.[CONSTITUENTID] = @CONSTITUENTID
and
REVENUE.[TRANSACTIONTYPECODE] = 1
group by
REVENUE.ID,
REVENUE.LOOKUPID,
INSTALLMENT.ID,
INSTALLMENT.SEQUENCE,
INSTALLMENT.DATE,
INSTALLMENT.TRANSACTIONAMOUNT
union all
select
REVENUE.ID as REVENUEID,
REVENUE.LOOKUPID as REVENUELOOKUPID,
null as ID,
INSTALLMENT.SEQUENCE,
LISTORDER = 1,
ACTIVITY = 'Payment',
PAYMENT.DATE as Date,
sum(INSTALLMENTSPLITPAYMENT.AMOUNT) AMOUNT,
PAIDBY = CONSTITUENT.NAME,
PAYMETHOD = REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
BALANCE = null,
PAYMENT.ID as TRANSACTIONID,
PAYMENT.LOOKUPID as TRANSACTIONLOOKUPID,
PAYMENT.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT,
RECORDTYPE = 1,
'' DESIGNATIONLIST,
PARENT = INSTALLMENT.ID
from
dbo.REVENUE
inner join
dbo.INSTALLMENT on REVENUE.ID = INSTALLMENT.REVENUEID
inner join
dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
inner join
dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
inner join
dbo.REVENUESPLIT PAYMENTSPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = PAYMENTSPLIT.ID
inner join
dbo.REVENUE PAYMENT on PAYMENTSPLIT.REVENUEID = PAYMENT.ID
inner join
dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = PAYMENT.ID
inner join
dbo.CONSTITUENT on PAYMENT.CONSTITUENTID = CONSTITUENT.ID
where
REVENUE.[CONSTITUENTID] = @CONSTITUENTID
and
REVENUE.[TRANSACTIONTYPECODE] = 1
group by
REVENUE.ID,
REVENUE.LOOKUPID,
INSTALLMENT.SEQUENCE,
PAYMENT.DATE,
CONSTITUENT.NAME,
REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
PAYMENT.ID,
PAYMENT.LOOKUPID,
INSTALLMENT.ID,
PAYMENT.TRANSACTIONAMOUNT
union all
select
REVENUE.ID as REVENUEID,
REVENUE.LOOKUPID as REVENUELOOKUPID,
null as ID,
INSTALLMENT.SEQUENCE,
LISTORDER = 2,
ACTIVITY = 'Write-off',
WRITEOFF.DATE as Date,
sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT) AMOUNT,
PAIDBY = null,
PAYMETHOD = null,
BALANCE = null,
null as TRANSACTIONID,
TRANSACTIONLOOKUPID = '',
TRANSACTIONAMOUNT = 0,
RECORDTYPE = 2,
'' DESIGNATIONLIST,
PARENT = INSTALLMENT.ID
from
dbo.REVENUE
inner join
dbo.INSTALLMENT on REVENUE.ID = INSTALLMENT.REVENUEID
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
REVENUE.[CONSTITUENTID] = @CONSTITUENTID
and
REVENUE.[TRANSACTIONTYPECODE] = 1
group by
REVENUE.ID,
REVENUE.LOOKUPID,
INSTALLMENT.SEQUENCE,
WRITEOFF.DATE,
INSTALLMENT.ID
) as INSTALLMENTDATA
order by REVENUEID, SEQUENCE
end