UFN_RECEIVABLEPAYMENTAPPLICATIONS
Gets a list of all the receivable payment applications for a given payment
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @RECEIVABLEPAYMENTLINEITEMID | uniqueidentifier | IN | |
| @RECEIVABLEPAYMENTOWNERID | uniqueidentifier | IN | |
| @INCLUDEPOSSIBLEAPPLICATIONS | bit | IN |
Definition
Copy
CREATE function dbo.UFN_RECEIVABLEPAYMENTAPPLICATIONS
(
@RECEIVABLEPAYMENTLINEITEMID uniqueidentifier = null,
@RECEIVABLEPAYMENTOWNERID uniqueidentifier = null,
@INCLUDEPOSSIBLEAPPLICATIONS bit = 1
)
returns @APPLICATIONS table
(
ID uniqueidentifier,
SOURCELINEITEMID uniqueidentifier,
APPLIEDTOLINEITEMID uniqueidentifier NOT NULL,
APPLIEDTOID uniqueidentifier NOT NULL,
APPLIEDTOLINEITEMDESCRIPTION nvarchar(100),
CHARGEDUEDATE datetime,
APPLICATIONDATE datetime,
DATE datetime NOT NULL,
NAME nvarchar(100) NOT NULL,
BILLINGITEMNAME nvarchar(100) NOT NULL,
CHARGEAMOUNT money NOT NULL,
CHARGEAMOUNTREMAINING money NOT NULL,
AMOUNT money NOT NULL
)
with execute as caller
as
begin
-- Check the basic rule that only one of the two IDs can be passed in
if ((not @RECEIVABLEPAYMENTLINEITEMID is null) and (not @RECEIVABLEPAYMENTOWNERID is null))
return;
-- Check the basic rule that one of the two IDs must be passed in
if ((@RECEIVABLEPAYMENTLINEITEMID is null) and (@RECEIVABLEPAYMENTOWNERID is null))
return;
-- Generate a table with all the existing applications
if (not @RECEIVABLEPAYMENTLINEITEMID is null)
begin
insert into @APPLICATIONS
(ID,
SOURCELINEITEMID,
APPLIEDTOLINEITEMID,
APPLIEDTOID,
APPLIEDTOLINEITEMDESCRIPTION,
CHARGEDUEDATE,
APPLICATIONDATE,
DATE,
NAME,
BILLINGITEMNAME,
CHARGEAMOUNT,
CHARGEAMOUNTREMAINING,
AMOUNT)
select FINANCIALTRANSACTIONLINEITEMAPPLICATION.ID,
FINANCIALTRANSACTIONLINEITEMAPPLICATION.SOURCELINEITEMID,
FINANCIALTRANSACTIONLINEITEMAPPLICATION.APPLIEDTOLINEITEMID,
FINANCIALTRANSACTION_CHARGE.ID,
FINANCIALTRANSACTIONLINEITEM_CHARGE.DESCRIPTION,
CHARGE.DUEDATE,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.DATEADDED,
FINANCIALTRANSACTION_CHARGE.DATE,
CONSTITUENT_CHARGEOWNER.NAME,
BILLINGITEM.NAME,
FINANCIALTRANSACTIONLINEITEM_CHARGE.AMOUNT,
FINANCIALTRANSACTIONLINEITEM_CHARGE.AMOUNT,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.AMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT
inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_RECEIVABLEPAYMENT
on FINANCIALTRANSACTION_RECEIVABLEPAYMENT.ID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT.FINANCIALTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEMAPPLICATION
on FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT.ID = FINANCIALTRANSACTIONLINEITEMAPPLICATION.SOURCELINEITEMID
inner join FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
on FINANCIALTRANSACTIONLINEITEMAPPLICATION.ID = FINANCIALTRANSACTIONLINEITEM_APPLICATION.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_CHARGE
on FINANCIALTRANSACTIONLINEITEM_CHARGE.ID = FINANCIALTRANSACTIONLINEITEMAPPLICATION.APPLIEDTOLINEITEMID
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_RECEIVABLEPAYMENT.ID = @RECEIVABLEPAYMENTLINEITEMID) and
(FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETED = 0);
end
-- Are we including possible applications
if ((@INCLUDEPOSSIBLEAPPLICATIONS = 1) or (not @RECEIVABLEPAYMENTOWNERID is null))
begin
-- Get the payment owner
if (@RECEIVABLEPAYMENTOWNERID is null)
begin
select @RECEIVABLEPAYMENTOWNERID = FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where FINANCIALTRANSACTIONLINEITEM.ID = @RECEIVABLEPAYMENTLINEITEMID;
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,
APPLIEDTOLINEITEMID,
APPLIEDTOID,
APPLIEDTOLINEITEMDESCRIPTION,
CHARGEDUEDATE,
DATE,
NAME,
BILLINGITEMNAME,
CHARGEAMOUNT,
CHARGEAMOUNTREMAINING,
AMOUNT)
select null,
@RECEIVABLEPAYMENTLINEITEMID,
FINANCIALTRANSACTIONLINEITEM_CHARGE.ID,
FINANCIALTRANSACTION_CHARGE.ID,
FINANCIALTRANSACTIONLINEITEM_CHARGE.DESCRIPTION,
CHARGE.DUEDATE,
FINANCIALTRANSACTION_CHARGE.DATE,
CONSTITUENT_CHARGEOWNER.NAME,
BILLINGITEM.NAME,
FINANCIALTRANSACTIONLINEITEM_CHARGE.AMOUNT,
FINANCIALTRANSACTIONLINEITEM_CHARGE.AMOUNT,
0
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 = @RECEIVABLEPAYMENTOWNERID)
left outer join @APPLICATIONS as APPLICATIONS
on APPLICATIONS.APPLIEDTOLINEITEMID = FINANCIALTRANSACTIONLINEITEM_CHARGE.ID
where (APPLICATIONS.ID is null);
end
-- Now subtract the already made payments
update @APPLICATIONS
set CHARGEAMOUNTREMAINING = CHARGEAMOUNTREMAINING - (select sum(FINANCIALTRANSACTIONLINEITEM_APPLICATION.AMOUNT)
from dbo.FINANCIALTRANSACTIONLINEITEMAPPLICATION
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
on FINANCIALTRANSACTIONLINEITEM_APPLICATION.ID = FINANCIALTRANSACTIONLINEITEMAPPLICATION.ID
where (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETED = 0) and
(FINANCIALTRANSACTIONLINEITEMAPPLICATION.APPLIEDTOLINEITEMID = APPLICATIONS.APPLIEDTOLINEITEMID)
group by FINANCIALTRANSACTIONLINEITEMAPPLICATION.APPLIEDTOLINEITEMID)
from @APPLICATIONS as APPLICATIONS;
return;
end