USP_DATAFORMTEMPLATE_PRELOAD_AUCTIONDONATIONWRITEOFF
The load procedure used by the edit dataform template "Auction Donation Write-Off Add Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | Input parameter indicating the context ID for the record being added. |
| @CONSTITUENTNAME | nvarchar(255) | INOUT | |
| @PLEDGEDATE | datetime | INOUT | Donated on |
| @PLEDGEBALANCE | money | INOUT | Value |
| @INSTALLMENTS | xml | INOUT | |
| @TRANSACTIONTYPECODE | tinyint | INOUT | Transaction type code |
| @PLEDGEAMOUNTMINUSWRITEOFFS | money | INOUT | Value minus write-offs |
| @RECOGNITIONCREDITS | xml | INOUT | Recognition credits |
| @REVENUESPLITS | xml | INOUT | Revenue splits |
| @ORIGINALPLEDGEAMOUNT | decimal(18, 0) | INOUT | Original value |
| @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 | |
| @POSTDATE | datetime | INOUT |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_PRELOAD_AUCTIONDONATIONWRITEOFF
(
@REVENUEID uniqueidentifier,
@CONSTITUENTNAME nvarchar(255) = 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 decimal = 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,
@POSTDATE datetime = null output
)
as
set nocount on;
-- Check GL business rule for this account system and set to 'Do not post' if needed.
-- ****
declare @PDACCOUNTSYSTEMID uniqueidentifier;
select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEMID
from dbo.PDACCOUNTSYSTEMFORREVENUE
where ID = @REVENUEID;
set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
if @ALLOWGLDISTRIBUTIONS = 0
begin
set @POSTSTATUSCODE = 2 -- Do not post; get gl setting for default; with bug 194282 later check to get the auction post status
end
-- ****
select
@CONSTITUENTNAME = CONSTITUENT.NAME,
@PLEDGEDATE = REVENUE.DATE,
@PLEDGEBALANCE = dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID),
@INSTALLMENTS =
(
select
ID,
DATE,
TRANSACTIONAMOUNT as AMOUNT,
BALANCE,
WRITEOFFAMOUNT,
SEQUENCE,
TRANSACTIONCURRENCYID
from dbo.UFN_AUCTIONDONATION_GETWRITEOFFINSTALLMENTS_2(REVENUE.ID) WRITEOFFINSTALLMENT
for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
),
@TRANSACTIONTYPECODE = REVENUE.TRANSACTIONTYPECODE,
@PLEDGEAMOUNTMINUSWRITEOFFS = (REVENUE.TRANSACTIONAMOUNT - coalesce((select sum(WRITEOFFSPLIT.TRANSACTIONAMOUNT)
from dbo.WRITEOFFSPLIT
inner join dbo.WRITEOFF on WRITEOFF.ID = WRITEOFFSPLIT.WRITEOFFID
where WRITEOFF.REVENUEID = @REVENUEID), 0)),
@ORIGINALPLEDGEAMOUNT = REVENUE.TRANSACTIONAMOUNT,
@WRITEOFFTOTALAMOUNT = REVENUE.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = REVENUE.BASECURRENCYID,
@EXCHANGERATE =
case
when REVENUE.TRANSACTIONCURRENCYID = REVENUE.BASECURRENCYID
then 1
else
coalesce(CURRENCYEXCHANGERATE.RATE, 0)
end,
@POSTSTATUSCODE =
case
when REVENUE.DONOTPOST = 1
then 2
else
1
end
from
dbo.REVENUE
inner join
dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
left join
dbo.CURRENCYEXCHANGERATE on REVENUE.BASEEXCHANGERATEID = CURRENCYEXCHANGERATE.ID
where
REVENUE.ID = @REVENUEID and REVENUE.TRANSACTIONTYPECODE = 7;
set @RECOGNITIONCREDITS = (
select
REVENUERECOGNITION.ID,
REVENUESPLIT.ID as REVENUESPLITID,
dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT.DESIGNATIONID) as DESIGNATIONNAME,
CONSTITUENT.NAME as CONSTITUENTNAME,
REVENUERECOGNITIONTYPECODE.DESCRIPTION as RECOGNITIONCREDITTYPE,
REVENUERECOGNITION.EFFECTIVEDATE,
REVENUERECOGNITION.AMOUNT as ORIGINALAMOUNT,
REVENUERECOGNITION.AMOUNT as ADJUSTEDAMOUNT,
REVENUERECOGNITION.BASECURRENCYID
from dbo.REVENUE
inner join dbo.REVENUESPLIT on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUERECOGNITION on REVENUESPLIT.ID = REVENUERECOGNITION.REVENUESPLITID
inner join dbo.CONSTITUENT on REVENUERECOGNITION.CONSTITUENTID = CONSTITUENT.ID
left join dbo.REVENUERECOGNITIONTYPECODE on REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID = REVENUERECOGNITIONTYPECODE.ID
where
REVENUE.ID = @REVENUEID
for xml raw('ITEM'),type,elements,root('RECOGNITIONCREDITS'),BINARY BASE64
)
set @REVENUESPLITS = (
select
REVENUESPLIT.ID,
REVENUESPLIT.TRANSACTIONAMOUNT as [AMOUNT],
REVENUESPLIT.TRANSACTIONCURRENCYID
from dbo.REVENUESPLIT
where
REVENUESPLIT.REVENUEID = @REVENUEID
for xml raw('ITEM'),type,elements,root('REVENUESPLITS'),BINARY BASE64
)
return 0;