USP_DATAFORMTEMPLATE_PRELOAD_ADD_PLEDGEWRITEOFF2
The load procedure used by the edit dataform template "Pledge Write-off Add Form 2"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CONSTITUENTNAME | nvarchar(700) | INOUT | |
| @PLEDGEDATE | datetime | INOUT | Pledged on |
| @PLEDGEBALANCE | money | INOUT | Pledge balance |
| @INSTALLMENTS | xml | INOUT | |
| @TRANSACTIONTYPECODE | tinyint | INOUT | Transaction type code |
| @PLEDGEAMOUNTMINUSWRITEOFFS | money | INOUT | Pledge amount minus write-offs |
| @RECOGNITIONCREDITS | xml | INOUT | Recognition credits |
| @REVENUESPLITS | xml | INOUT | Revenue splits |
| @ORIGINALPLEDGEAMOUNT | money | INOUT | Original pledge amount |
| @WRITEOFFTOTALAMOUNT | money | INOUT | Amount |
| @TRANSACTIONCURRENCYID | uniqueidentifier | INOUT | Transaction currency |
| @BASECURRENCYID | uniqueidentifier | INOUT | Base currency |
| @EXCHANGERATE | decimal(20, 8) | INOUT | Exchange rate |
| @ALLOWGLDISTRIBUTIONS | bit | INOUT | Allow GL distributions |
| @POSTSTATUSCODE | tinyint | INOUT | GL post status |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_PRELOAD_ADD_PLEDGEWRITEOFF2
(
@REVENUEID uniqueidentifier,
@CONSTITUENTNAME nvarchar(700) = null output,
@PLEDGEDATE datetime = null output,
@PLEDGEBALANCE money = null output,
@INSTALLMENTS xml = null output,
@TRANSACTIONTYPECODE tinyint = null output,
@PLEDGEAMOUNTMINUSWRITEOFFS money = null output,
@RECOGNITIONCREDITS xml = null output,
@REVENUESPLITS xml = null output,
@ORIGINALPLEDGEAMOUNT money = null output,
@WRITEOFFTOTALAMOUNT money = null output,
@TRANSACTIONCURRENCYID uniqueidentifier = null output,
@BASECURRENCYID uniqueidentifier = null output,
@EXCHANGERATE decimal(20,8) = null output,
@ALLOWGLDISTRIBUTIONS bit = null output,
@POSTSTATUSCODE tinyint = null output
)
as
begin
set nocount on;
declare @REVENUETRANSACTIONTYPECODE tinyint;
select
@ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(FINANCIALTRANSACTION.PDACCOUNTSYSTEMID), -- Check GL business rule for this account system and set to 'Do not post' if needed.
@CONSTITUENTNAME = NF.NAME,
@PLEDGEDATE = FINANCIALTRANSACTION.DATE,
@PLEDGEBALANCE = dbo.UFN_PLEDGE_GETBALANCE(FINANCIALTRANSACTION.ID),
@REVENUETRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
@TRANSACTIONTYPECODE = FINANCIALTRANSACTION.TYPECODE,
@PLEDGEAMOUNTMINUSWRITEOFFS =
(FINANCIALTRANSACTION.TRANSACTIONAMOUNT - coalesce
(
(
select
sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
from dbo.INSTALLMENTSPLITWRITEOFF
inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
where INSTALLMENTSPLIT.PLEDGEID = @REVENUEID
),
0
)
),
@ORIGINALPLEDGEAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
@WRITEOFFTOTALAMOUNT =
case
when FINANCIALTRANSACTION.TYPECODE = 7 then FINANCIALTRANSACTION.BASEAMOUNT
else 0
end,
@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
@BASECURRENCYID = CURRENCYSET.BASECURRENCYID,
@EXCHANGERATE =
case
when FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCYSET.BASECURRENCYID
then 1
else
coalesce(CURRENCYEXCHANGERATE.RATE, 0)
end
,@POSTSTATUSCODE = case when (case FINANCIALTRANSACTION.POSTSTATUSCODE when 3 then 1 else 0 end) = 1 then 2 else 1 end
from
dbo.FINANCIALTRANSACTION
inner join
dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join
dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join
dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
cross apply dbo.UFN_CONSTITUENT_DISPLAYNAME(FINANCIALTRANSACTION.CONSTITUENTID) NF
left join
dbo.CURRENCYEXCHANGERATE on FINANCIALTRANSACTION.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where
FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null;
if @REVENUETRANSACTIONTYPECODE = 7
set @INSTALLMENTS =
(
select
ID,
DATE,
TRANSACTIONAMOUNT as AMOUNT,
BALANCE,
WRITEOFFAMOUNT,
SEQUENCE,
TRANSACTIONCURRENCYID
from dbo.UFN_AUCTIONDONATION_GETWRITEOFFINSTALLMENTS_2(@REVENUEID) WRITEOFFINSTALLMENT
order by SEQUENCE
for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
);
else
set @INSTALLMENTS =
(
select
ID,
DATE,
TRANSACTIONAMOUNT as AMOUNT,
BALANCE,
WRITEOFFAMOUNT,
SEQUENCE,
TRANSACTIONCURRENCYID
from dbo.UFN_INSTALLMENT_GETWRITEOFFINSTALLMENTS_2(@REVENUEID) WRITEOFFINSTALLMENT
order by SEQUENCE
for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
);
set @RECOGNITIONCREDITS = (
select
REVENUERECOGNITION.ID,
FINANCIALTRANSACTIONLINEITEM.ID as REVENUESPLITID,
ISNULL(dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID), 'None (Earned Income)') as DESIGNATIONNAME,
CONSTITUENT.NAME as CONSTITUENTNAME,
REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONCREDITTYPE,
REVENUERECOGNITION.EFFECTIVEDATE,
REVENUERECOGNITION.AMOUNT as ORIGINALAMOUNT,
REVENUERECOGNITION.AMOUNT as ADJUSTEDAMOUNT,
REVENUERECOGNITION.BASECURRENCYID
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.REVENUERECOGNITION on FINANCIALTRANSACTIONLINEITEM.ID = REVENUERECOGNITION.REVENUESPLITID
inner join dbo.CONSTITUENT on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENT.ID
left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
where
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
for xml raw('ITEM'),type,elements,root('RECOGNITIONCREDITS'),BINARY BASE64
)
set @REVENUESPLITS = (
select
FINANCIALTRANSACTIONLINEITEM.ID,
FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT [AMOUNT],
FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
where
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
for xml raw('ITEM'),type,elements,root('REVENUESPLITS'),BINARY BASE64
)
return 0;
end