UFN_RECEIVABLEPAYMENTCREDITAPPLICATIONS
Gets a list of all the receivable payment applications for a given payment
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @RECEIVABLEPAYMENTCREDITLINEITEMID | uniqueidentifier | IN | |
| @RECEIVABLEPAYMENTCREDITOWNERID | uniqueidentifier | IN | |
| @INCLUDEPOSSIBLEAPPLICATIONS | bit | IN |
Definition
Copy
CREATE function dbo.UFN_RECEIVABLEPAYMENTCREDITAPPLICATIONS
(
@RECEIVABLEPAYMENTCREDITLINEITEMID uniqueidentifier = null,
@RECEIVABLEPAYMENTCREDITOWNERID uniqueidentifier = null,
@INCLUDEPOSSIBLEAPPLICATIONS bit = 1
)
returns @APPLICATIONS table
(
ID uniqueidentifier,
SOURCELINEITEMID uniqueidentifier,
TARGETLINEITEMID uniqueidentifier NOT NULL,
TARGETID uniqueidentifier NOT NULL,
TARGETLINEITEMDESCRIPTION nvarchar(100),
DUEDATE datetime,
APPLICATIONDATE datetime,
DATE datetime NOT NULL,
NAME nvarchar(100) NOT NULL,
BILLINGITEMNAME nvarchar(100) NOT NULL,
DESCRIPTION nvarchar(100) NOT NULL,
TOTALAMOUNT money NOT NULL,
TOTALAMOUNTREMAINING money NOT NULL,
AMOUNT money NOT NULL,
DUEON nvarchar(40),
POSTDATE date NULL,
POSTSTATUSCODE tinyint
)
with execute as caller
as
begin
-- Check the basic rule that only one of the two IDs can be passed in
if ((not @RECEIVABLEPAYMENTCREDITLINEITEMID is null) and (not @RECEIVABLEPAYMENTCREDITOWNERID is null))
return;
-- Check the basic rule that one of the two IDs must be passed in
if ((@RECEIVABLEPAYMENTCREDITLINEITEMID is null) and (@RECEIVABLEPAYMENTCREDITOWNERID is null))
return;
-- Generate a table with all the existing applications
if (not @RECEIVABLEPAYMENTCREDITLINEITEMID is null)
begin
insert into @APPLICATIONS
(ID,
SOURCELINEITEMID,
TARGETLINEITEMID,
TARGETID,
TARGETLINEITEMDESCRIPTION,
DUEDATE,
APPLICATIONDATE,
DATE,
NAME,
BILLINGITEMNAME,
DESCRIPTION,
TOTALAMOUNT,
TOTALAMOUNTREMAINING,
AMOUNT,
DUEON,
POSTDATE,
POSTSTATUSCODE)
select FINANCIALTRANSACTIONLINEITEM_APPLICATION.ID,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID,
FINANCIALTRANSACTION_CHARGE.ID,
FINANCIALTRANSACTIONLINEITEM_CHARGE.DESCRIPTION,
CHARGE.DUEDATE,
case when (FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE > FINANCIALTRANSACTION_CHARGE.DATE) then
FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE
else
FINANCIALTRANSACTION_CHARGE.DATE
end,
FINANCIALTRANSACTION_CHARGE.DATE,
CONSTITUENT_CHARGEOWNER.NAME,
BILLINGITEM.NAME,
FINANCIALTRANSACTIONLINEITEM_CHARGE.DESCRIPTION,
FINANCIALTRANSACTIONLINEITEM_CHARGE.TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM_CHARGE.TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT,
convert(nvarchar(10), CHARGE.DUEDATE, 101),
FINANCIALTRANSACTIONLINEITEM_APPLICATION.POSTDATE,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.POSTSTATUSCODE
from dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT
inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT
on FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.ID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT.FINANCIALTRANSACTIONID
inner join FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
on FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_CHARGE
on FINANCIALTRANSACTIONLINEITEM_CHARGE.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID
inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_CHARGE
on FINANCIALTRANSACTIONLINEITEM_CHARGE.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION_CHARGE.ID
inner join dbo.CHARGE
on CHARGE.ID =FINANCIALTRANSACTION_CHARGE.ID
inner join dbo.CONSTITUENT as CONSTITUENT_CHARGEOWNER
on FINANCIALTRANSACTION_CHARGE.CONSTITUENTID = CONSTITUENT_CHARGEOWNER.ID
inner join dbo.CHARGELINEITEM
on CHARGELINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_CHARGE.ID
inner join dbo.BILLINGITEM
on BILLINGITEM.ID = CHARGELINEITEM.BILLINGITEMID
where (FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT.ID = @RECEIVABLEPAYMENTCREDITLINEITEMID) and
(FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null);
end
-- Are we including possible applications
if ((@INCLUDEPOSSIBLEAPPLICATIONS = 1) or (not @RECEIVABLEPAYMENTCREDITOWNERID is null))
begin
-- Get the payment owner
if (@RECEIVABLEPAYMENTCREDITOWNERID is null)
begin
select @RECEIVABLEPAYMENTCREDITOWNERID = FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where FINANCIALTRANSACTIONLINEITEM.ID = @RECEIVABLEPAYMENTCREDITLINEITEMID;
end
-- Find all the possible charge line items for this payment that do not already have applications
-- and that this payment owner is responsible for.
insert into @APPLICATIONS
(ID,
SOURCELINEITEMID,
TARGETLINEITEMID,
TARGETID,
TARGETLINEITEMDESCRIPTION,
DUEDATE,
DATE,
NAME,
BILLINGITEMNAME,
DESCRIPTION,
TOTALAMOUNT,
TOTALAMOUNTREMAINING,
AMOUNT,
DUEON,
POSTDATE,
POSTSTATUSCODE)
select null,
@RECEIVABLEPAYMENTCREDITLINEITEMID,
FINANCIALTRANSACTIONLINEITEM_CHARGE.ID,
FINANCIALTRANSACTION_CHARGE.ID,
FINANCIALTRANSACTIONLINEITEM_CHARGE.DESCRIPTION,
CHARGE.DUEDATE,
FINANCIALTRANSACTION_CHARGE.DATE,
CONSTITUENT_CHARGEOWNER.NAME,
BILLINGITEM.NAME,
FINANCIALTRANSACTIONLINEITEM_CHARGE.DESCRIPTION,
FINANCIALTRANSACTIONLINEITEM_CHARGE.TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM_CHARGE.TRANSACTIONAMOUNT,
0,
convert(nvarchar(10), CHARGE.DUEDATE, 101),
null,
1
from dbo.CHARGE
inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_CHARGE
on CHARGE.ID = FINANCIALTRANSACTION_CHARGE.ID
inner join dbo.CONSTITUENT as CONSTITUENT_CHARGEOWNER
on FINANCIALTRANSACTION_CHARGE.CONSTITUENTID = CONSTITUENT_CHARGEOWNER.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_CHARGE
on FINANCIALTRANSACTION_CHARGE.ID = FINANCIALTRANSACTIONLINEITEM_CHARGE.FINANCIALTRANSACTIONID
inner join dbo.CHARGELINEITEM
on CHARGELINEITEM.ID = FINANCIALTRANSACTIONLINEITEM_CHARGE.ID
inner join dbo.BILLINGITEM
on BILLINGITEM.ID = CHARGELINEITEM.BILLINGITEMID
inner join dbo.V_QUERY_CHARGELINEITEMRESPONSIBILITY
on (V_QUERY_CHARGELINEITEMRESPONSIBILITY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM_CHARGE.ID) and
(V_QUERY_CHARGELINEITEMRESPONSIBILITY.CONSTITUENTID = @RECEIVABLEPAYMENTCREDITOWNERID)
left outer join @APPLICATIONS as APPLICATIONS
on APPLICATIONS.TARGETLINEITEMID = FINANCIALTRANSACTIONLINEITEM_CHARGE.ID
where (APPLICATIONS.ID is null);
end
-- Now subtract the already made payments
update @APPLICATIONS
set TOTALAMOUNTREMAINING = TOTALAMOUNTREMAINING - isnull((select sum(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT)
from dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
where (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null) and
(FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID = APPLICATIONS.TARGETLINEITEMID)
group by FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID), 0)
from @APPLICATIONS as APPLICATIONS;
-- Update due date to only show days late if there is an overdue balance
update @APPLICATIONS
set DUEON = case when ((DATEDIFF(day, DUEDATE, GETDATE()) = 1) and (TOTALAMOUNTREMAINING > 0)) then
DUEON + ' - 1 day late'
when ((DATEDIFF(day, DUEDATE, GETDATE()) > 0) and (TOTALAMOUNTREMAINING > 0)) then
DUEON + ' - ' + convert(nvarchar(10), DATEDIFF(day, DUEDATE, GETDATE())) + ' days late'
else
DUEON
end
-- Now remove any possible charges where the amount remaining is zero
delete from @APPLICATIONS
where (ID is null) and (TOTALAMOUNTREMAINING = 0);
return;
end