UFN_CHARGEAPPLICATIONS
Gets a list of all the charge applications for a given charge.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CHARGELINEITEMID | uniqueidentifier | IN | |
| @CHARGEOWNERID | uniqueidentifier | IN | |
| @INCLUDEPOSSIBLEAPPLICATIONS | bit | IN |
Definition
Copy
CREATE function dbo.UFN_CHARGEAPPLICATIONS
(
@CHARGELINEITEMID uniqueidentifier = null,
@CHARGEOWNERID uniqueidentifier = null,
@INCLUDEPOSSIBLEAPPLICATIONS bit = 1
)
returns @APPLICATIONS table
(
ID uniqueidentifier,
SOURCELINEITEMID uniqueidentifier,
TARGETLINEITEMID uniqueidentifier NOT NULL,
SOURCEID uniqueidentifier NOT NULL,
DATE datetime NOT NULL,
APPLICATIONDATE datetime,
TYPE nvarchar(100) NOT NULL,
NAME nvarchar(100) NOT NULL,
DESCRIPTION nvarchar(100) NOT NULL,
TOTALAMOUNT money NOT NULL,
TOTALAMOUNTREMAINING money NOT NULL,
AMOUNT money NOT NULL,
POSTDATE date NULL,
POSTSTATUSCODE tinyint 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 @CHARGELINEITEMID is null) and (not @CHARGEOWNERID is null))
return;
-- Check the basic rule that one of the two IDs must be passed in
if ((@CHARGELINEITEMID is null) and (@CHARGEOWNERID is null))
return;
-- Generate a table with all the existing applications
if (not @CHARGELINEITEMID is null)
begin
insert into @APPLICATIONS
(ID,
SOURCELINEITEMID,
TARGETLINEITEMID,
SOURCEID,
DATE,
APPLICATIONDATE,
TYPE,
NAME,
DESCRIPTION,
TOTALAMOUNT,
TOTALAMOUNTREMAINING,
AMOUNT,
POSTDATE,
POSTSTATUSCODE)
select FINANCIALTRANSACTIONLINEITEM_APPLICATION.ID,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.TARGETLINEITEMID,
FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.ID,
FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE,
case when (FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE > FINANCIALTRANSACTION_CHARGE.DATE) then
FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.DATE
else
FINANCIALTRANSACTION_CHARGE.DATE
end,
FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.TYPE,
case FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.TYPECODE
when 105 then
CONSTITUENT_PAYER.NAME
else
''
end,
case FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.TYPECODE
when 105 then
RECEIVABLEPAYMENT.PAYMENTMETHOD
when 106 then
BILLINGITEM.NAME
end,
FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT.TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT.TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM_APPLICATION.TRANSACTIONAMOUNT,
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.CONSTITUENT as CONSTITUENT_RECEIVABLEPAYMENTCREDITOWNER
on FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.CONSTITUENTID = CONSTITUENT_RECEIVABLEPAYMENTCREDITOWNER.ID
left outer join dbo.RECEIVABLEPAYMENT
on RECEIVABLEPAYMENT.ID =FINANCIALTRANSACTION_RECEIVABLEPAYMENTCREDIT.ID
left outer join dbo.CONSTITUENT as CONSTITUENT_PAYER
on RECEIVABLEPAYMENT.CONSTITUENTID = CONSTITUENT_PAYER.ID
left outer join dbo.RECEIVABLECREDITLINEITEM
on RECEIVABLECREDITLINEITEM.ID =FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENTCREDIT.ID
left outer join dbo.BILLINGITEM
on BILLINGITEM.ID = RECEIVABLECREDITLINEITEM.BILLINGITEMID
where (FINANCIALTRANSACTIONLINEITEM_CHARGE.ID = @CHARGELINEITEMID) and
(FINANCIALTRANSACTIONLINEITEM_APPLICATION.DELETEDON is null);
end
-- Are we including possible applications
if ((@INCLUDEPOSSIBLEAPPLICATIONS = 1) or (not @CHARGEOWNERID is null))
begin
-- Get the charge owner
if (@CHARGEOWNERID is null)
begin
select @CHARGEOWNERID = FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM
on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where FINANCIALTRANSACTIONLINEITEM.ID = @CHARGELINEITEMID;
end
-- Find all the possible receivable payment line items for this charge that do not already have applications
-- and that this charge owner is the owner of.
insert into @APPLICATIONS
(ID,
SOURCELINEITEMID,
TARGETLINEITEMID,
SOURCEID,
DATE,
TYPE,
NAME,
DESCRIPTION,
TOTALAMOUNT,
TOTALAMOUNTREMAINING,
AMOUNT,
POSTDATE,
POSTSTATUSCODE)
select null,
FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT.ID,
@CHARGELINEITEMID,
FINANCIALTRANSACTION_RECEIVABLEPAYMENT.ID,
FINANCIALTRANSACTION_RECEIVABLEPAYMENT.DATE,
FINANCIALTRANSACTION_RECEIVABLEPAYMENT.TYPE,
CONSTITUENT_PAYMENTOWNER.NAME,
RECEIVABLEPAYMENT.PAYMENTMETHOD,
FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT.TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT.TRANSACTIONAMOUNT,
0,
null,
1
from dbo.RECEIVABLEPAYMENT
inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_RECEIVABLEPAYMENT
on (RECEIVABLEPAYMENT.ID = FINANCIALTRANSACTION_RECEIVABLEPAYMENT.ID) and
(FINANCIALTRANSACTION_RECEIVABLEPAYMENT.CONSTITUENTID = @CHARGEOWNERID)
inner join dbo.CONSTITUENT as CONSTITUENT_PAYMENTOWNER
on RECEIVABLEPAYMENT.CONSTITUENTID = CONSTITUENT_PAYMENTOWNER.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT
on (FINANCIALTRANSACTION_RECEIVABLEPAYMENT.ID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT.FINANCIALTRANSACTIONID) and
(FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT.TYPECODE = 0)
left outer join @APPLICATIONS as APPLICATIONS
on APPLICATIONS.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLEPAYMENT.ID
where (APPLICATIONS.ID is null);
-- Find all the possible receivable credit line items for this charge that do not already have applications
-- and that this charge owner is the owner of.
insert into @APPLICATIONS
(ID,
SOURCELINEITEMID,
TARGETLINEITEMID,
SOURCEID,
DATE,
TYPE,
NAME,
DESCRIPTION,
TOTALAMOUNT,
TOTALAMOUNTREMAINING,
AMOUNT,
POSTDATE,
POSTSTATUSCODE)
select null,
FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.ID,
@CHARGELINEITEMID,
FINANCIALTRANSACTION_RECEIVABLECREDIT.ID,
FINANCIALTRANSACTION_RECEIVABLECREDIT.DATE,
FINANCIALTRANSACTION_RECEIVABLECREDIT.TYPE,
'',
BILLINGITEM.NAME,
FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.TRANSACTIONAMOUNT,
0,
null,
1
from dbo.RECEIVABLECREDIT
inner join dbo.FINANCIALTRANSACTION as FINANCIALTRANSACTION_RECEIVABLECREDIT
on (RECEIVABLECREDIT.ID = FINANCIALTRANSACTION_RECEIVABLECREDIT.ID) and
(FINANCIALTRANSACTION_RECEIVABLECREDIT.CONSTITUENTID = @CHARGEOWNERID)
inner join dbo.FINANCIALTRANSACTIONLINEITEM as FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT
on (FINANCIALTRANSACTION_RECEIVABLECREDIT.ID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.FINANCIALTRANSACTIONID) and
(FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.TYPECODE = 0)
inner join dbo.RECEIVABLECREDITLINEITEM
on RECEIVABLECREDITLINEITEM.ID =FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.ID
inner join dbo.BILLINGITEM
on BILLINGITEM.ID = RECEIVABLECREDITLINEITEM.BILLINGITEMID
left outer join @APPLICATIONS as APPLICATIONS
on APPLICATIONS.SOURCELINEITEMID = FINANCIALTRANSACTIONLINEITEM_RECEIVABLECREDIT.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.SOURCELINEITEMID = APPLICATIONS.SOURCELINEITEMID)
group by FINANCIALTRANSACTIONLINEITEM_APPLICATION.SOURCELINEITEMID), 0)
from @APPLICATIONS as APPLICATIONS;
-- Now remove any possible payments where the amount left to be applied is zero
delete from @APPLICATIONS
where (ID is null) and (TOTALAMOUNTREMAINING = 0);
return;
end