UFN_RECEIVABLECREDITAPPLICATIONS
Gets a list of all the receivable credit applications for a given credit
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @RECEIVABLECREDITLINEITEMID | uniqueidentifier | IN | |
| @INCLUDEPOSSIBLEAPPLICATIONS | bit | IN |
Definition
Copy
create function dbo.UFN_RECEIVABLECREDITAPPLICATIONS
(
@RECEIVABLECREDITLINEITEMID uniqueidentifier,
@INCLUDEPOSSIBLEAPPLICATIONS bit = 1
)
returns @APPLICATIONS table
(
ID uniqueidentifier,
SOURCELINEITEMID uniqueidentifier NOT NULL,
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,
TOTALAMOUNT money NOT NULL,
AMOUNTREMAINING money NOT NULL,
AMOUNT money NOT NULL
)
with execute as caller
as
begin
-- Generate a table with all the existing applications
insert into @APPLICATIONS
(ID,
SOURCELINEITEMID,
APPLIEDTOLINEITEMID,
APPLIEDTOID,
APPLIEDTOLINEITEMDESCRIPTION,
CHARGEDUEDATE,
APPLICATIONDATE,
DATE,
NAME,
BILLINGITEMNAME,
TOTALAMOUNT,
AMOUNTREMAINING,
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_RECEIVABLECREDIT
inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_RECEIVABLECREDIT
on FINANCIALTRANSACTION_RECEIVABLECREDIT.ID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.FINANCIALTRANSACTIONID
inner join dbo.FINANCIALTRANSACTIONLINEITEMAPPLICATION
on FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.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_RECEIVABLECREDIT.ID = @RECEIVABLECREDITLINEITEMID) and
(FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETED = 0);
-- Are we including possible applications
if (@INCLUDEPOSSIBLEAPPLICATIONS = 1)
begin
-- Get the credit owner
declare @RECEIVABLECREDITOWNER uniqueidentifier;
select @RECEIVABLECREDITOWNER = FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where FINANCIALTRANSACTIONLINEITEM.ID = @RECEIVABLECREDITLINEITEMID;
-- Find all the possible charge line items for this credit that do not already have applications
-- and that this credit owner is responsible for.
insert into @APPLICATIONS
(ID,
SOURCELINEITEMID,
APPLIEDTOLINEITEMID,
APPLIEDTOID,
APPLIEDTOLINEITEMDESCRIPTION,
CHARGEDUEDATE,
DATE,
NAME,
BILLINGITEMNAME,
TOTALAMOUNT,
AMOUNTREMAINING,
AMOUNT)
select null,
@RECEIVABLECREDITLINEITEMID,
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 = @RECEIVABLECREDITOWNER)
left outer join @APPLICATIONS as APPLICATIONS
on APPLICATIONS.APPLIEDTOLINEITEMID = FINANCIALTRANSACTIONLINEITEM_CHARGE.ID
where (APPLICATIONS.ID is null);
end
-- Now subtract the already made payments and credits
update @APPLICATIONS
set AMOUNTREMAINING = AMOUNTREMAINING - FINANCIALTRANSACTIONLINEITEM_APPLICATION.AMOUNT
from @APPLICATIONS as APPLICATIONS
inner join dbo.FINANCIALTRANSACTIONLINEITEMAPPLICATION
on FINANCIALTRANSACTIONLINEITEMAPPLICATION.APPLIEDTOLINEITEMID = APPLICATIONS.APPLIEDTOLINEITEMID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_APPLICATION
on FINANCIALTRANSACTIONLINEITEM_APPLICATION.ID = FINANCIALTRANSACTIONLINEITEMAPPLICATION.ID
where (FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETED = 0);
return;
end