USP_DATAFORMTEMPLATE_VIEW_BILLINGINDIVIDUALSUMMARYOVERVIEW
The load procedure used by the view dataform template "Billing Individual Summary Overview Data Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @CONSTITUENTID | uniqueidentifier | INOUT | CONSTITUENTID |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @BILLINGCYCLEID | uniqueidentifier | INOUT | Billing for |
| @BILLINGSTARTDATE | datetime | INOUT | BILLINGSTARTDATE |
| @BILLINGENDDATE | datetime | INOUT | BILLINGENDDATE |
| @TOTALBILLED | money | INOUT | Total billed |
| @BALANCE | money | INOUT | Balance in full |
| @LASTPAYMENTID | uniqueidentifier | INOUT | LASTPAYMENTID |
| @LASTPAYMENTAMOUNT | nvarchar(100) | INOUT | Last payment |
| @LASTPAYMENTDATE | datetime | INOUT | LASTPAYMENTDATE |
| @LASTPAYMENTPAYERID | uniqueidentifier | INOUT | LASTPAYMENTPAYERID |
| @LASTPAYMENTPAYERNAME | nvarchar(200) | INOUT | LASTPAYMENTPAYERNAME |
| @LASTPAYMENTPAYERNAMESTUDENT | nvarchar(200) | INOUT | LASTPAYMENTPAYERNAMESTUDENT |
| @LASTPAYMENTMETHODCODE | tinyint | INOUT | LASTPAYMENTMETHODCODE |
| @LASTPAYMENTMETHOD | nvarchar(50) | INOUT | LASTPAYMENTMETHOD |
| @LASTPAYMENTMETHODINFO | nvarchar(50) | INOUT | LASTPAYMENTMETHODINFO |
| @AGINGBUCKET1LABEL | nvarchar(100) | INOUT | AGINGBUCKET1LABEL |
| @AGINGBUCKET1AMOUNT | money | INOUT | AGINGBUCKET1AMOUNT |
| @AGINGBUCKET2LABEL | nvarchar(100) | INOUT | AGINGBUCKET2LABEL |
| @AGINGBUCKET2AMOUNT | money | INOUT | AGINGBUCKET2AMOUNT |
| @AGINGBUCKET3LABEL | nvarchar(100) | INOUT | AGINGBUCKET3LABEL |
| @AGINGBUCKET3AMOUNT | money | INOUT | AGINGBUCKET3AMOUNT |
| @AGINGBUCKET4LABEL | nvarchar(100) | INOUT | AGINGBUCKET4LABEL |
| @AGINGBUCKET4AMOUNT | money | INOUT | AGINGBUCKET4AMOUNT |
| @OPENCHARGESAMOUNT | money | INOUT | OPENCHARGESAMOUNT |
| @OPENCHARGES | xml | INOUT | OPENCHARGES |
| @OPENCHARGESINCLUDEPASTYEARS | bit | INOUT | OPENCHARGESINCLUDEPASTYEARS |
| @OPENCHARGESINCLUDEFUTUREPAYMENTSCREDITS | bit | INOUT | OPENCHARGESINCLUDEFUTUREPAYMENTSCREDITS |
| @PASTDUECHARGESAMOUNT | money | INOUT | PASTDUECHARGESAMOUNT |
| @PASTDUECHARGES | xml | INOUT | PASTDUECHARGES |
| @PASTDUECHARGESINCLUDEFUTUREPAYMENTSCREDITS | bit | INOUT | PASTDUECHARGESINCLUDEFUTUREPAYMENTSCREDITS |
| @UNNAPLIEDPAYMENTSCREDITSEXIST | bit | INOUT | UNNAPLIEDPAYMENTSCREDITSEXIST |
| @ACCOUNTINGMETHODCODE | int | INOUT | ACCOUNTINGMETHODCODE |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_BILLINGINDIVIDUALSUMMARYOVERVIEW
(
@ID uniqueidentifier,
@CONSTITUENTID uniqueidentifier = null output,
@DATALOADED bit = 0 output,
@BILLINGCYCLEID uniqueidentifier = null output,
@BILLINGSTARTDATE datetime = null output,
@BILLINGENDDATE datetime = null output,
@TOTALBILLED money = null output,
@BALANCE money = null output,
@LASTPAYMENTID uniqueidentifier = null output,
@LASTPAYMENTAMOUNT nvarchar(100) = null output,
@LASTPAYMENTDATE datetime = null output,
@LASTPAYMENTPAYERID uniqueidentifier = null output,
@LASTPAYMENTPAYERNAME nvarchar(200) = null output,
@LASTPAYMENTPAYERNAMESTUDENT nvarchar(200) = null output,
@LASTPAYMENTMETHODCODE tinyint = null output,
@LASTPAYMENTMETHOD nvarchar(50) = null output,
@LASTPAYMENTMETHODINFO nvarchar(50) = null output,
@AGINGBUCKET1LABEL nvarchar(100) = null output,
@AGINGBUCKET1AMOUNT money = null output,
@AGINGBUCKET2LABEL nvarchar(100) = null output,
@AGINGBUCKET2AMOUNT money = null output,
@AGINGBUCKET3LABEL nvarchar(100) = null output,
@AGINGBUCKET3AMOUNT money = null output,
@AGINGBUCKET4LABEL nvarchar(100) = null output,
@AGINGBUCKET4AMOUNT money = null output,
@OPENCHARGESAMOUNT money = null output,
@OPENCHARGES xml = null output,
@OPENCHARGESINCLUDEPASTYEARS bit = null output,
@OPENCHARGESINCLUDEFUTUREPAYMENTSCREDITS bit = null output,
@PASTDUECHARGESAMOUNT money = null output,
@PASTDUECHARGES xml = null output,
@PASTDUECHARGESINCLUDEFUTUREPAYMENTSCREDITS bit = null output,
@UNNAPLIEDPAYMENTSCREDITSEXIST bit = null output,
@ACCOUNTINGMETHODCODE int = null output
)
as
set nocount on;
set @DATALOADED = 0;
-- Get the basic information based on billing cycle ID
exec dbo.USP_BILLINGINDIVIDUALSUMMARYOVERVIEW @ID,
@BILLINGCYCLEID output,
@BILLINGSTARTDATE output,
@BILLINGENDDATE output,
@TOTALBILLED output,
@BALANCE output,
@LASTPAYMENTID output,
@LASTPAYMENTAMOUNT output,
@LASTPAYMENTDATE output,
@LASTPAYMENTPAYERID output,
@LASTPAYMENTPAYERNAME output,
@LASTPAYMENTPAYERNAMESTUDENT output,
@LASTPAYMENTMETHODCODE output,
@LASTPAYMENTMETHOD output,
@LASTPAYMENTMETHODINFO output;
set @CONSTITUENTID = @ID;
-- Get the aging bucket information
declare @AGINGBUCKETS table
(
SEQUENCE int,
LABEL nvarchar(100),
AMOUNT money
);
insert into @AGINGBUCKETS
(SEQUENCE, LABEL, AMOUNT)
select SEQUENCE, LABEL, AMOUNT
from dbo.UFN_BILLINGINDIVIDUALAGING(@ID, null);
select @AGINGBUCKET1LABEL = LABEL,
@AGINGBUCKET1AMOUNT = AMOUNT
from @AGINGBUCKETS
where SEQUENCE = 1;
select @AGINGBUCKET2LABEL = LABEL,
@AGINGBUCKET2AMOUNT = AMOUNT
from @AGINGBUCKETS
where SEQUENCE = 2;
select @AGINGBUCKET3LABEL = LABEL,
@AGINGBUCKET3AMOUNT = AMOUNT
from @AGINGBUCKETS
where SEQUENCE = 3;
select @AGINGBUCKET4LABEL = LABEL,
@AGINGBUCKET4AMOUNT = AMOUNT
from @AGINGBUCKETS
where SEQUENCE = 4;
-- Get open charge and past due charge information
select @DATALOADED = 1,
@OPENCHARGESAMOUNT = dbo.UFN_BILLINGBALANCEINDIVIDUAL(@ID, null, getdate()),
@OPENCHARGES = dbo.UFN_BILLINGINDIVIDUALOPENCHARGES_TOITEMLISTXML(@ID, null),
@PASTDUECHARGESAMOUNT = dbo.UFN_BILLINGPASTDUEBALANCEINDIVIDUAL(@ID, null, getdate()),
@PASTDUECHARGES = dbo.UFN_BILLINGINDIVIDUALPASTDUECHARGES_TOITEMLISTXML(@ID, null),
@ACCOUNTINGMETHODCODE = dbo.UFN_APPLICATIONRULES_GETACCOUNTINGMETHODCODE();
-- Setup to get the unapplied payment/credit amount
declare @UNAPPLIEDPAYMENTCREDITAMOUNT money;
set @UNAPPLIEDPAYMENTCREDITAMOUNT = dbo.UFN_BILLINGTRANSACTIONTYPETOTAL(105, @ID, null, getdate(), 1, 0, 0) + dbo.UFN_BILLINGTRANSACTIONTYPETOTAL(106, @ID, null, getdate(), 1, 0, 0);
-- Check if unapplied payments or credits exist
set @UNNAPLIEDPAYMENTSCREDITSEXIST = case when (@UNAPPLIEDPAYMENTCREDITAMOUNT > 0) then
1
else
0
end;
-- If the total of the past due charge balance plus unapplied payments is greater than the
-- total of the open charges then the open charges must include future payments.
set @PASTDUECHARGESINCLUDEFUTUREPAYMENTSCREDITS = case when ((@PASTDUECHARGESAMOUNT + @UNAPPLIEDPAYMENTCREDITAMOUNT) > (select sum(isnull(PASTDUECHARGEAMOUNTS.AMOUNTREMAINING, 0))
from (select T.c.value('(AMOUNTREMAINING)[1]','money') as AMOUNTREMAINING
from @PASTDUECHARGES.nodes('/PASTDUECHARGES/ITEM') T(c)) as PASTDUECHARGEAMOUNTS)) then
1
else
0
end;
-- If the past due charges include future payments and credits than the open charges must as well,
-- since any past due charges must also be open.
if (@PASTDUECHARGESINCLUDEFUTUREPAYMENTSCREDITS = 1)
begin
set @OPENCHARGESINCLUDEFUTUREPAYMENTSCREDITS = 1;
end
else
begin
-- If the past due charges do not contain future payments we will have to check the open charges.
-- If the total of the open charge balance plus unapplied payments is greater than the
-- total of the open charges then the open charges must include future payments.
set @OPENCHARGESINCLUDEFUTUREPAYMENTSCREDITS = case when ((@OPENCHARGESAMOUNT + @UNAPPLIEDPAYMENTCREDITAMOUNT) > (select sum(isnull(OPENCHARGEAMOUNTS.AMOUNTREMAINING, 0))
from (select T.c.value('(AMOUNTREMAINING)[1]','money') as AMOUNTREMAINING
from @OPENCHARGES.nodes('/OPENCHARGES/ITEM') T(c)) as OPENCHARGEAMOUNTS)) then
1
else
0
end;
end
-- Get the open charge min and max dates.
select @OPENCHARGESINCLUDEPASTYEARS = case when (not @BILLINGCYCLEID is null) and
(@BILLINGCYCLEID != '00000000-0000-0000-0000-000000000000') and
(@BILLINGCYCLEID != '00000000-0000-0000-0000-000000000001') and
(exists (select OPENCHARGEDATES.DATE
from (select T.c.value('(DATE)[1]','datetime') as DATE
from @OPENCHARGES.nodes('/OPENCHARGES/ITEM') T(c)) as OPENCHARGEDATES
where OPENCHARGEDATES.DATE < @BILLINGSTARTDATE
)) then
1
else
0
end;
return 0;