USP_DATAFORMTEMPLATE_EDITLOAD_GRANTAWARD
The load procedure used by the edit dataform template "Grant Award Edit Form"
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ID | uniqueidentifier | IN | The input ID parameter used to load the fields defined on the form. |
| @DATALOADED | bit | INOUT | Output parameter indicating whether or not data was actually loaded. |
| @TSLONG | bigint | INOUT | Output parameter indicating the TSLONG value of the record being edited. This is used to manage multi-user concurrency issues when multiple users access the same record. |
| @GRANTNAME | nvarchar(150) | INOUT | Grant program |
| @GRANTORNAME | nvarchar(154) | INOUT | Grantor |
| @DATE | datetime | INOUT | Date |
| @AMOUNT | money | INOUT | Amount |
| @POSTSTATUSCODE | tinyint | INOUT | Post status |
| @POSTDATE | datetime | INOUT | Post date |
| @SPLITS | xml | INOUT | Designations |
| @FREQUENCYCODE | tinyint | INOUT | Frequency |
| @NUMBEROFINSTALLMENTS | int | INOUT | No. installments remaining |
| @NEXTTRANSACTIONDATE | datetime | INOUT | Next installment date |
| @AMOUNTPAID | money | INOUT | Amount paid |
| @INSTALLMENTS | xml | INOUT | |
| @DONOTACKNOWLEDGE | bit | INOUT | Do not acknowledge |
| @MAINSINGLEDESIGNATIONID | uniqueidentifier | INOUT | Designation |
| @LASTPAYMENTSEQUENCE | int | INOUT | Installment sequence of last payment |
| @CATEGORYCODEID | uniqueidentifier | INOUT | Revenue category |
| @MAXSOLICITORSPLITAMOUNTS | xml | INOUT | Max solicitor split amounts |
| @ISREIMBURSABLE | bit | INOUT | Grant award is reimbursable |
| @GRANTAWARDON | tinyint | INOUT | |
| @OPPORTUNITYID | uniqueidentifier | INOUT | Opportunity |
| @PROSPECTNAME | nvarchar(154) | INOUT | Prospect |
| @CONSTITUENTID | uniqueidentifier | INOUT | |
| @BASECURRENCYID | uniqueidentifier | INOUT | Base currency |
| @TRANSACTIONCURRENCYID | uniqueidentifier | INOUT | Transaction currency |
| @BASEEXCHANGERATEID | uniqueidentifier | INOUT | Exchange rate ID |
| @EXCHANGERATE | decimal(20, 8) | INOUT | Exchange rate |
| @HADSPOTRATE | bit | INOUT | Had spot rate |
| @RATECHANGED | bit | INOUT | Rate changed |
| @BASEDECIMALDIGITS | tinyint | INOUT | Decimal digits |
| @BASEROUNDINGTYPECODE | tinyint | INOUT | Rounding type |
| @TRANSACTIONCURRENCYDESCRIPTION | nvarchar(110) | INOUT | Transaction currency description |
| @HASPOSTEDPAYMENTS | bit | INOUT | Grant award has posted payments |
| @ALLOWGLDISTRIBUTIONS | bit | INOUT | Allow GL distributions |
| @USERGRANTEDSPOTRATE | bit | INOUT | User granted spot rate |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_GRANTAWARD
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@TSLONG bigint = 0 output,
@GRANTNAME nvarchar(150) = null output,
@GRANTORNAME nvarchar(154) = null output,
@DATE datetime = null output,
@AMOUNT money = null output,
@POSTSTATUSCODE tinyint = null output,
@POSTDATE datetime = null output,
@SPLITS xml = null output,
@FREQUENCYCODE tinyint = null output,
@NUMBEROFINSTALLMENTS int = null output,
@NEXTTRANSACTIONDATE datetime = null output,
@AMOUNTPAID money = null output,
@INSTALLMENTS xml = null output,
@DONOTACKNOWLEDGE bit = null output,
@MAINSINGLEDESIGNATIONID uniqueidentifier = null output,
@LASTPAYMENTSEQUENCE int = null output,
@CATEGORYCODEID uniqueidentifier = null output,
@MAXSOLICITORSPLITAMOUNTS xml = null output,
@ISREIMBURSABLE bit = null output,
@GRANTAWARDON tinyint = null output,
@OPPORTUNITYID uniqueidentifier = null output,
@PROSPECTNAME nvarchar(154) = null output,
@CONSTITUENTID uniqueidentifier = null output,
@BASECURRENCYID uniqueidentifier = null output,
@TRANSACTIONCURRENCYID uniqueidentifier = null output,
@BASEEXCHANGERATEID uniqueidentifier = null output,
@EXCHANGERATE decimal(20,8) = null output,
@HADSPOTRATE bit = null output,
@RATECHANGED bit = null output,
@BASEDECIMALDIGITS tinyint = null output,
@BASEROUNDINGTYPECODE tinyint = null output,
@TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output,
@HASPOSTEDPAYMENTS bit = null output,
@ALLOWGLDISTRIBUTIONS bit = null output,
@USERGRANTEDSPOTRATE bit = null output
)
as
set nocount on;
set @DATALOADED = 0
set @TSLONG = 0
-- 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 = @ID;
set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);
-- ****
declare @LASTINSTALLMENT uniqueidentifier;
select
@DATALOADED = DATALOADED,
@GRANTNAME = GRANTNAME,
@GRANTORNAME = GRANTORNAME,
@DATE = DATE,
@AMOUNT = AMOUNT,
@POSTSTATUSCODE = POSTSTATUSCODE,
@POSTDATE = POSTDATE,
@FREQUENCYCODE = FREQUENCYCODE,
@NUMBEROFINSTALLMENTS = NUMBEROFINSTALLMENTS,
@NEXTTRANSACTIONDATE = NEXTTRANSACTIONDATE,
@TSLONG = TSLONG,
@DONOTACKNOWLEDGE = DONOTACKNOWLEDGE,
@CATEGORYCODEID = CATEGORYCODEID,
@MAXSOLICITORSPLITAMOUNTS = MAXSOLICITORSPLITAMOUNTS,
@ISREIMBURSABLE = ISREIMBURSABLE,
@GRANTAWARDON = GRANTAWARDON,
@OPPORTUNITYID = OPPORTUNITYID,
@PROSPECTNAME = PROSPECTNAME,
@CONSTITUENTID = CONSTITUENTID,
@BASECURRENCYID = BASECURRENCYID,
@TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID = BASEEXCHANGERATEID,
@EXCHANGERATE = EXCHANGERATE,
@HADSPOTRATE = HADSPOTRATE,
@RATECHANGED = 0,
@BASEDECIMALDIGITS = BASEDECIMALDIGITS,
@BASEROUNDINGTYPECODE = BASEROUNDINGTYPECODE,
@TRANSACTIONCURRENCYDESCRIPTION = TRANSACTIONCURRENCYDESCRIPTION
from
(
select
1 as DATALOADED,
GRANTS.TITLE as GRANTNAME,
dbo.UFN_CONSTITUENT_BUILDNAME(GRANTS.GRANTORID) as GRANTORNAME,
REVENUE.DATE as DATE,
REVENUE.TRANSACTIONAMOUNT as AMOUNT,
case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end as POSTSTATUSCODE,
REVENUE.POSTDATE as POSTDATE,
REVENUESCHEDULE.FREQUENCYCODE as FREQUENCYCODE,
REVENUESCHEDULE.NUMBEROFINSTALLMENTS as NUMBEROFINSTALLMENTS,
coalesce(REVENUESCHEDULE.NEXTTRANSACTIONDATE, REVENUESCHEDULE.STARTDATE) as NEXTTRANSACTIONDATE,
REVENUE.TSLONG as TSLONG,
DONOTACKNOWLEDGE,
(select top 1 GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID where REVENUEID = REVENUE.ID) as CATEGORYCODEID,
dbo.UFN_REVENUEDETAIL_GETMAXSOLICITORSPLITAMOUNTS_TOITEMLISTXML(@ID) as MAXSOLICITORSPLITAMOUNTS,
ISREIMBURSABLE as ISREIMBURSABLE,
1 as GRANTAWARDON, -- Grant program
null as OPPORTUNITYID,
null as PROSPECTNAME,
GRANTS.GRANTORID as CONSTITUENTID,
REVENUE.BASECURRENCYID,
REVENUE.TRANSACTIONCURRENCYID,
REVENUE.BASEEXCHANGERATEID,
CURRENCYEXCHANGERATE.RATE as EXCHANGERATE,
case
when CURRENCYEXCHANGERATE.TYPECODE = 2
then 1
else 0
end as HADSPOTRATE,
CURRENCY.DECIMALDIGITS as BASEDECIMALDIGITS,
CURRENCY.ROUNDINGTYPECODE as BASEROUNDINGTYPECODE,
dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCYDESCRIPTION
from
dbo.REVENUE
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
inner join dbo.REVENUEFUNDINGREQUEST on REVENUE.ID = REVENUEFUNDINGREQUEST.ID
inner join dbo.FUNDINGREQUEST on REVENUEFUNDINGREQUEST.FUNDINGREQUESTID = FUNDINGREQUEST.ID
inner join dbo.GRANTS on FUNDINGREQUEST.GRANTSID = GRANTS.ID
inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.BASECURRENCYID
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
where
REVENUE.ID = @ID
and REVENUE.TRANSACTIONTYPECODE = 6
union all
select
1 as DATALOADED,
null as GRANTNAME,
null as GRANTORNAME,
REVENUE.DATE as DATE,
REVENUE.TRANSACTIONAMOUNT as AMOUNT,
case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end as POSTSTATUSCODE,
REVENUE.POSTDATE as POSTDATE,
REVENUESCHEDULE.FREQUENCYCODE as FREQUENCYCODE,
REVENUESCHEDULE.NUMBEROFINSTALLMENTS as NUMBEROFINSTALLMENTS,
coalesce(REVENUESCHEDULE.NEXTTRANSACTIONDATE, REVENUESCHEDULE.STARTDATE) as NEXTTRANSACTIONDATE,
REVENUE.TSLONG as TSLONG,
DONOTACKNOWLEDGE,
(select top 1 GLREVENUECATEGORYMAPPINGID from dbo.REVENUECATEGORY inner join dbo.REVENUESPLIT on REVENUECATEGORY.ID = REVENUESPLIT.ID where REVENUEID = REVENUE.ID) as CATEGORYCODEID,
dbo.UFN_REVENUEDETAIL_GETMAXSOLICITORSPLITAMOUNTS_TOITEMLISTXML(@ID) as MAXSOLICITORSPLITAMOUNTS,
ISREIMBURSABLE as ISREIMBURSABLE,
2 as GRANTAWARDON, -- Opportunity
OPPORTUNITY.ID as OPPORTUNITYID,
dbo.UFN_CONSTITUENT_BUILDNAME(PROSPECTPLAN.PROSPECTID) as PROSPECTNAME,
PROSPECTPLAN.PROSPECTID as CONSTITUENTID,
REVENUE.BASECURRENCYID,
REVENUE.TRANSACTIONCURRENCYID,
REVENUE.BASEEXCHANGERATEID,
CURRENCYEXCHANGERATE.RATE as EXCHANGERATE,
case
when CURRENCYEXCHANGERATE.TYPECODE = 2
then 1
else 0
end as HADSPOTRATE,
CURRENCY.DECIMALDIGITS as BASEDECIMALDIGITS,
CURRENCY.ROUNDINGTYPECODE as BASEROUNDINGTYPECODE,
dbo.UFN_CURRENCY_GETDESCRIPTION(REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCYDESCRIPTION
from
dbo.REVENUE
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = REVENUE.ID
inner join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.ID = REVENUESPLIT.ID
inner join dbo.OPPORTUNITY on REVENUEOPPORTUNITY.OPPORTUNITYID = OPPORTUNITY.ID
inner join dbo.PROSPECTPLAN on OPPORTUNITY.PROSPECTPLANID = PROSPECTPLAN.ID
inner join dbo.CURRENCY on CURRENCY.ID = REVENUE.BASECURRENCYID
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID
where
REVENUE.ID = @ID
and REVENUE.TRANSACTIONTYPECODE = 6
)
as TEMPTABLE;
if @DATALOADED = 1
begin
set @AMOUNTPAID = @AMOUNT - dbo.UFN_PLEDGE_GETBALANCE(@ID);
set @LASTINSTALLMENT = dbo.UFN_INSTALLMENT_GETLASTINSTALLMENTPAID(@ID);
if @LASTINSTALLMENT is null
begin
set @LASTPAYMENTSEQUENCE = 0;
end
else
begin
set @LASTPAYMENTSEQUENCE = (select SEQUENCE from dbo.INSTALLMENT where ID = @LASTINSTALLMENT);
end
--set @INSTALLMENTS = dbo.UFN_INSTALLMENT_GETINSTALLMENTS_TOITEMLISTXML(@ID);
-- set @NUMBEROFINSTALLMENTS to the number of unpaid installments
select @NUMBEROFINSTALLMENTS = @NUMBEROFINSTALLMENTS - @LASTPAYMENTSEQUENCE
set @SPLITS = (
select
SPLITS.TRANSACTIONAMOUNT AMOUNT,
SPLITS.APPLICATIONCODE,
SPLITS.CATEGORYCODEID,
SPLITS.DESIGNATIONID,
SPLITS.ID,
SPLITS.TYPECODE,
SPLITS.TRANSACTIONCURRENCYID
from dbo.UFN_REVENUE_GETSPLITS_2(@ID) SPLITS
inner join dbo.DESIGNATION on DESIGNATION.ID = SPLITS.DESIGNATIONID
order by DESIGNATION.VANITYNAME
for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
);
set @INSTALLMENTS =(
select
INSTALLMENT.ID,
INSTALLMENT.DATE,
INSTALLMENT.TRANSACTIONAMOUNT as AMOUNT,
INSTALLMENT.BALANCE,
INSTALLMENT.APPLIED,
INSTALLMENT.SEQUENCE,
(
select
INSTALLMENTSPLIT.ID,
INSTALLMENTSPLIT.DESIGNATIONID,
INSTALLMENTSPLIT.TRANSACTIONAMOUNT as AMOUNT,
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITAMOUNTAPPLIED(INSTALLMENTSPLIT.ID) APPLIED,
INSTALLMENTSPLIT.TRANSACTIONCURRENCYID,
INSTALLMENTSPLIT.REVENUESPLITID
from dbo.INSTALLMENTSPLIT
inner join dbo.DESIGNATION on DESIGNATION.ID = INSTALLMENTSPLIT.DESIGNATIONID
where INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
order by DESIGNATION.VANITYNAME
for xml raw('ITEM'),type,elements,BINARY BASE64
) as INSTALLMENTSPLITS,
INSTALLMENT.TRANSACTIONCURRENCYID
from dbo.UFN_INSTALLMENT_GETINSTALLMENTS_2(@ID) INSTALLMENT
order by INSTALLMENT.DATE
for xml raw('ITEM'),type,elements,root('INSTALLMENTS'),BINARY BASE64
);
end
set @HASPOSTEDPAYMENTS = dbo.UFN_PLEDGEHASPOSTEDPAYMENTS(@ID)
--replace with commented code for PBI 102747
set @USERGRANTEDSPOTRATE = 1;
/*set @USERGRANTEDSPOTRATE = case
when dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or
dbo.UFN_SECURITY_APPUSER_GRANTED_SYSTEMPRIVILEGE_IN_SYSTEMROLE(@CURRENTAPPUSERID, '911f104d-ba5f-4469-b0ae-184c879aea99') = 1
then 1
else 0
end;*/
return 0;