USP_DATAFORMTEMPLATE_VIEW_MISCELLANEOUSPAYMENT
The load procedure used by the view dataform template "Miscellaneous Payment View 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. |
| @CONSTITUENTID | uniqueidentifier | INOUT | Constituent ID |
| @REVENUEID | uniqueidentifier | INOUT | RevenueID |
| @AMOUNT | money | INOUT | Amount |
| @DATE | datetime | INOUT | Date |
| @CONSTITUENTNAME | nvarchar(255) | INOUT | Constituent |
| @ISGIFT | bit | INOUT | Is gift |
| @ISPLEDGE | bit | INOUT | Is pledge |
| @ISRECURRINGGIFT | bit | INOUT | Is recurring gift |
| @ISMGPLEDGE | bit | INOUT | Is matching gift claim |
| @ISPLANNEDGIFT | bit | INOUT | Is planned gift |
| @ISPAYMENT | bit | INOUT | Is payment |
| @ISPOSTED | bit | INOUT | Is posted |
| @LINKEDRECORDID | uniqueidentifier | INOUT | Linked record |
| @ALLOWEDITPAYMENT | bit | INOUT | Allow Edit Payment |
| @BENEFITSWAIVED | bit | INOUT | Benefits waived |
| @CANROLLBACK | bit | INOUT | Can rollback transaction |
| @CANSKIPTRANSACTION | bit | INOUT | Can skip transaction |
| @ISPENDING | bit | INOUT | Is pending |
| @EXTRADATAISPOSTED | bit | INOUT | Extra data is posted |
| @HASWRITEOFF | bit | INOUT | Has write-offs |
| @HASADJUSTMENTS | bit | INOUT | Has Adjustments |
| @HASUNPOSTEDREVERSAL | bit | INOUT | Has unposted GL Reversal |
| @HASUNPOSTEDADJUSTMENT | bit | INOUT | Has unposted Revenue Adjustment |
| @HASUNPOSTEDEXTRADATA | bit | INOUT | Has unposted Stock, Gift-in-kind, or Property Adjustment |
| @TYPE | nvarchar(20) | INOUT | Type |
| @PLEDGEBALANCE | money | INOUT | Pledge balance |
| @PAYMENTTYPECODE | tinyint | INOUT | Payment type code |
| @ATTRIBUTEDEFINED | bit | INOUT | Attribute defined |
| @REVENUECOUNT | int | INOUT | Revenue count |
| @ALLOWMATCHINGGIFTCLAIM | bit | INOUT | Allow matching gift claim |
| @HASTRIBUTES | bit | INOUT | Has tributes |
| @CANAPPLYTRIBUTE | bit | INOUT | Can apply tribute |
| @PAYMENTINCLUDESGIFT | bit | INOUT | Payment includes gift |
| @RECEIPTSTATUSCODE | tinyint | INOUT | Receipt status code |
| @MGPLEDGEISINACTIVE | bit | INOUT | Matching gift claim is inactive |
| @ISCONSTITUENTHOUSEHOLD | bit | INOUT | Is the transaction's associated constituent a household |
| @HOUSEHOLDSCANBEDONORS | bit | INOUT | Can households be donors |
| @NEEDSRERECEIPT | bit | INOUT | Needs re-receipt |
| @RECEIPTCOUNT | int | INOUT | Receipt count |
| @PLANNEDGIFTID | uniqueidentifier | INOUT | Planned gift ID |
| @REVENUEPAYMENTMETHODID | uniqueidentifier | INOUT | Revenue payment method ID |
| @DONOTPOST | bit | INOUT | Do not post |
| @SALEPOSTSTATUSCODE | tinyint | INOUT | Sale post status code |
| @HASSOLDSTOCK | bit | INOUT | Has sold stock |
| @GIFTFEE_ENABLED | bit | INOUT | GIFTFEE_ENABLED |
| @HASSOLDPROPERTY | bit | INOUT | Has sold property |
| @ELIGIBLEFORMATCHINGGIFTCLAIM | bit | INOUT | Eligible for matching gift claims |
| @ISORDER | bit | INOUT | Is order |
| @SALESORDERID | uniqueidentifier | INOUT | Sales order ID |
| @ISORDERAPPLICATION | bit | INOUT | Is order application |
| @ISMISCELLANEOUSPAYMENT | bit | INOUT | Is miscellaneous payment |
| @REFERENCE | nvarchar(255) | INOUT | Reference |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @HASSOLDGIFTINKIND | bit | INOUT | Has sold gift-in-kind |
Definition
Copy
CREATE procedure dbo.USP_DATAFORMTEMPLATE_VIEW_MISCELLANEOUSPAYMENT
(
@ID uniqueidentifier,
@DATALOADED bit = 0 output,
@CONSTITUENTID uniqueidentifier = null output,
@REVENUEID uniqueidentifier = null output,
@AMOUNT money = null output,
@DATE datetime = null output,
@CONSTITUENTNAME nvarchar(255) = null output,
@ISGIFT bit = null output,
@ISPLEDGE bit = null output,
@ISRECURRINGGIFT bit = null output,
@ISMGPLEDGE bit = null output,
@ISPLANNEDGIFT bit = null output,
@ISPAYMENT bit = null output,
@ISPOSTED bit = null output,
@LINKEDRECORDID uniqueidentifier = null output,
@ALLOWEDITPAYMENT bit = null output,
@BENEFITSWAIVED bit = null output,
@CANROLLBACK bit = null output,
@CANSKIPTRANSACTION bit = null output,
@ISPENDING bit = null output,
@EXTRADATAISPOSTED bit = null output,
@HASWRITEOFF bit = null output,
@HASADJUSTMENTS bit = null output,
@HASUNPOSTEDREVERSAL bit = null output,
@HASUNPOSTEDADJUSTMENT bit = null output,
@HASUNPOSTEDEXTRADATA bit = null output,
@TYPE nvarchar(20) = null output,
@PLEDGEBALANCE money = null output,
@PAYMENTTYPECODE tinyint = null output,
@ATTRIBUTEDEFINED bit = null output,
@REVENUECOUNT int = null output,
@ALLOWMATCHINGGIFTCLAIM bit = null output,
@HASTRIBUTES bit = null output,
@CANAPPLYTRIBUTE bit = null output,
@PAYMENTINCLUDESGIFT bit = null output,
@RECEIPTSTATUSCODE tinyint = null output,
@MGPLEDGEISINACTIVE bit = null output,
@ISCONSTITUENTHOUSEHOLD bit = null output,
@HOUSEHOLDSCANBEDONORS bit = null output,
@NEEDSRERECEIPT bit = null output,
@RECEIPTCOUNT int = null output,
@PLANNEDGIFTID uniqueidentifier = null output,
@REVENUEPAYMENTMETHODID uniqueidentifier = null output,
@DONOTPOST bit = null output,
@SALEPOSTSTATUSCODE tinyint = null output,
@HASSOLDSTOCK bit = null output,
@GIFTFEE_ENABLED bit = null output,
@HASSOLDPROPERTY bit = null output,
@ELIGIBLEFORMATCHINGGIFTCLAIM bit = null output,
@ISORDER bit = null output,
@SALESORDERID uniqueidentifier = null output,
@ISORDERAPPLICATION bit = null output,
@ISMISCELLANEOUSPAYMENT bit = null output,
@REFERENCE nvarchar(255) = null output,
@CURRENTAPPUSERID uniqueidentifier = null,
@HASSOLDGIFTINKIND bit = null output
)
as
set nocount on;
set @DATALOADED = 0;
declare @POSTED bit;
declare @DONOTRECEIPT bit;
select
@ISPAYMENT = 0,
@ISGIFT = 0,
@ISPLEDGE = 0,
@ISRECURRINGGIFT = 0,
@ISMGPLEDGE = 0,
@ISPLANNEDGIFT = 0,
@CANROLLBACK = 0,
@CANSKIPTRANSACTION = 0,
@EXTRADATAISPOSTED = 0,
@ISPENDING = 0,
@HASWRITEOFF = 0,
@HASUNPOSTEDREVERSAL = 0,
@HASUNPOSTEDADJUSTMENT = 0,
@HASUNPOSTEDEXTRADATA = 0,
@HASTRIBUTES = 0,
@CANAPPLYTRIBUTE = 0,
@PAYMENTINCLUDESGIFT = 0,
@ATTRIBUTEDEFINED = dbo.UFN_ATTRIBUTECATEGORY_DEFINEDFORRECORDTYPE_FORAPPUSER('REVENUE',@CURRENTAPPUSERID),
@ALLOWMATCHINGGIFTCLAIM = case when exists (select top 1 ID from dbo.REVENUESPLIT where REVENUEID = @ID and APPLICATIONCODE <> 7) then 1 else 0 end,
@MGPLEDGEISINACTIVE = 0,
@GIFTFEE_ENABLED = dbo.UFN_GIFTFEE_ENABLED(),
@ISORDER = 0,
@ISORDERAPPLICATION = 0,
@ISMISCELLANEOUSPAYMENT = 0;
select top 1
@DATALOADED = 1,
@REVENUEID = REVENUE.ID,
@CONSTITUENTID = REVENUE.CONSTITUENTID,
@CONSTITUENTNAME = CONSTITUENT.NAME,
@ISPAYMENT = case when TRANSACTIONTYPECODE = 0 then 1 else 0 end,
@ISGIFT = case when TRANSACTIONTYPECODE = 0 then 1 else 0 end,
@ISPLEDGE = case when TRANSACTIONTYPECODE = 1 then 1 else 0 end,
@ISRECURRINGGIFT = case when TRANSACTIONTYPECODE = 2 then 1 else 0 end,
@ISMGPLEDGE = case when TRANSACTIONTYPECODE = 3 then 1 else 0 end,
@ISPLANNEDGIFT = case when TRANSACTIONTYPECODE = 4 then 1 else 0 end,
@ISORDER = case when TRANSACTIONTYPECODE = 5 then 1 else 0 end,
@HASUNPOSTEDADJUSTMENT = case when ADJUSTMENT.POSTSTATUSCODE = 1 then 1 else 0 end,
@ISPOSTED = case when REVENUEPOSTED.ID is null then 0 else 1 end,
@DATE = REVENUE.DATE,
@TYPE = REVENUE.TRANSACTIONTYPE,
@PLEDGEBALANCE = case when TRANSACTIONTYPECODE = 1 or TRANSACTIONTYPECODE = 3 then dbo.UFN_PLEDGE_GETBALANCE(REVENUE.ID) else 0 end,
@PAYMENTTYPECODE = REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
@DONOTRECEIPT = REVENUE.DONOTRECEIPT,
@ISCONSTITUENTHOUSEHOLD = dbo.UFN_CONSTITUENT_ISHOUSEHOLD(CONSTITUENTID),
@NEEDSRERECEIPT = NEEDSRERECEIPT,
@REVENUEPAYMENTMETHODID = REVENUEPAYMENTMETHOD.ID,
@DONOTPOST = REVENUE.DONOTPOST,
@ELIGIBLEFORMATCHINGGIFTCLAIM = REVENUE.ELIGIBLEFORMATCHINGGIFTCLAIM,
@REFERENCE = REVENUEREFERENCE.REFERENCE
from dbo.REVENUE
inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
left join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = REVENUE.ID
left join dbo.ADJUSTMENT on REVENUE.ID = ADJUSTMENT.REVENUEID and ADJUSTMENT.POSTSTATUSCODE = 1
left join dbo.REVENUEREFERENCE on REVENUE.ID = REVENUEREFERENCE.ID
where REVENUE.ID = @ID
set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();
select
@AMOUNT = sum(REVENUE.AMOUNT),
@REVENUECOUNT = count(REVENUE.ID)
from dbo.REVENUE
where REVENUE.ID = @ID
if exists(select top 1 ID from dbo.REVENUE
where REVENUE.ID = @ID and REVENUE.TRANSACTIONTYPECODE = 0)
set @ISPAYMENT = 1;
select @HASADJUSTMENTS = case when count(*) > 0 then 1 else 0 end,
@HASUNPOSTEDADJUSTMENT = case when max(POSTSTATUSCODE) > 0 then 1 else 0 end
from
(select ADJUSTMENT.ID, POSTSTATUSCODE from dbo.ADJUSTMENT inner join dbo.REVENUE on ADJUSTMENT.REVENUEID = REVENUE.ID
where REVENUE.ID = @ID
union all
select STOCKSALEADJUSTMENT.ID, POSTSTATUSCODE from dbo.STOCKSALEADJUSTMENT
inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @ID
union all
select PROPERTYDETAILADJUSTMENT.ID, POSTSTATUSCODE from dbo.PROPERTYDETAILADJUSTMENT
inner join dbo.REVENUEPAYMENTMETHOD on PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = REVENUEPAYMENTMETHOD.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @ID
union all
select GIFTINKINDSALEADJUSTMENT.ID, POSTSTATUSCODE from dbo.GIFTINKINDSALEADJUSTMENT
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID = GIFTINKINDSALE.ID
inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @ID
) as A;
-- Check if any of the stock has been sold
set @HASSOLDSTOCK = 0
if @PAYMENTTYPECODE = 4 -- Stock payment method
begin
if exists ( select STOCKDETAIL.ID from dbo.STOCKDETAIL
inner join dbo.STOCKSALE on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
where STOCKDETAIL.ID = @REVENUEPAYMENTMETHODID)
set @HASSOLDSTOCK = 1
end
-- Check if the property has sold
set @HASSOLDPROPERTY = 0
if @PAYMENTTYPECODE = 5 -- Property payment method
begin
if exists (select PROPERTYDETAIL.ID from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID and ISSOLD = 1)
set @HASSOLDPROPERTY = 1
end
-- Check if the Gift-in-Kind has been sold
set @HASSOLDGIFTINKIND = 0
if @PAYMENTTYPECODE = 6 -- Gift-in-kind payment method
begin
if exists ( select GIFTINKINDPAYMENTMETHODDETAIL.ID from dbo.GIFTINKINDPAYMENTMETHODDETAIL
inner join dbo.GIFTINKINDSALE on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
where GIFTINKINDPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID)
set @HASSOLDGIFTINKIND = 1
end
--if Revenue is of type Sold Stock or Sold Payment and is Posted, we won't allow the user to edit the payment
if @PAYMENTTYPECODE = 4 and
exists ( select ID from dbo.STOCKSALE
where STOCKDETAILID = @REVENUEPAYMENTMETHODID and STOCKSALE.SALEPOSTSTATUSCODE = 0)
begin
set @ALLOWEDITPAYMENT = 0;
set @EXTRADATAISPOSTED = 1;
if exists (select STOCKSALEADJUSTMENT.ID from dbo.STOCKSALEADJUSTMENT
inner join dbo.STOCKSALE on STOCKSALE.ID = STOCKSALEADJUSTMENT.STOCKSALEID and STOCKSALEADJUSTMENT.POSTSTATUSCODE = 1
where STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID)
set @HASUNPOSTEDEXTRADATA = 1;
end
if @PAYMENTTYPECODE = 5 and @HASSOLDPROPERTY = 1
select @SALEPOSTSTATUSCODE = SALEPOSTSTATUSCODE from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID;
if @SALEPOSTSTATUSCODE = 0
begin
set @ALLOWEDITPAYMENT = 0;
set @EXTRADATAISPOSTED = 1;
if exists (select * from dbo.PROPERTYDETAILADJUSTMENT
inner join dbo.PROPERTYDETAIL on PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = PROPERTYDETAIL.ID and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE = 1
where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID)
set @HASUNPOSTEDEXTRADATA = 1;
end
--if Revenue is of type Sold Gift-in-kind or Sold Payment and is Posted, we won't allow the user to edit the payment
if @PAYMENTTYPECODE = 6 and
exists ( select ID from dbo.GIFTINKINDSALE
where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID and GIFTINKINDSALE.SALEPOSTSTATUSCODE = 0)
begin
set @ALLOWEDITPAYMENT = 0;
set @EXTRADATAISPOSTED = 1;
if exists (select GIFTINKINDSALEADJUSTMENT.ID from dbo.GIFTINKINDSALEADJUSTMENT
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALE.ID = GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID and GIFTINKINDSALEADJUSTMENT.POSTSTATUSCODE = 1
where GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID)
set @HASUNPOSTEDEXTRADATA = 1;
end
if @ALLOWEDITPAYMENT = 1
begin
if @PAYMENTTYPECODE = 4 and @HASSOLDSTOCK = 1
if (select STOCKSALE.SALEPOSTSTATUSCODE from dbo.STOCKSALE
where STOCKDETAILID = @REVENUEPAYMENTMETHODID) = 0
begin
set @ALLOWEDITPAYMENT = 0;
set @EXTRADATAISPOSTED = 1;
if exists (select * from dbo.STOCKSALEADJUSTMENT
inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID and STOCKSALEADJUSTMENT.POSTSTATUSCODE = 1
inner join dbo.STOCKDETAIL on STOCKSALE.STOCKDETAILID = STOCKDETAIL.ID
where STOCKDETAIL.ID = @REVENUEPAYMENTMETHODID)
set @HASUNPOSTEDEXTRADATA = 1;
else
set @HASUNPOSTEDEXTRADATA = 0;
end
else
set @EXTRADATAISPOSTED = 0;
if @PAYMENTTYPECODE = 5 and @HASSOLDPROPERTY = 1
if (select SALEPOSTSTATUSCODE from dbo.PROPERTYDETAIL where ID = @REVENUEPAYMENTMETHODID) = 0
begin
set @ALLOWEDITPAYMENT = 0;
set @EXTRADATAISPOSTED = 1;
if exists (select * from dbo.PROPERTYDETAILADJUSTMENT
inner join dbo.PROPERTYDETAIL on PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = PROPERTYDETAIL.ID and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE = 1
where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID)
set @HASUNPOSTEDEXTRADATA = 1;
else
set @HASUNPOSTEDEXTRADATA = 0;
end
else
set @EXTRADATAISPOSTED = 0;
if @PAYMENTTYPECODE = 6 and @HASSOLDGIFTINKIND = 1
if (select GIFTINKINDSALE.SALEPOSTSTATUSCODE from dbo.GIFTINKINDSALE
where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID) = 0
begin
set @ALLOWEDITPAYMENT = 0;
set @EXTRADATAISPOSTED = 1;
if exists (select * from dbo.GIFTINKINDSALEADJUSTMENT
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID = GIFTINKINDSALE.ID and GIFTINKINDSALEADJUSTMENT.POSTSTATUSCODE = 1
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDPAYMENTMETHODDETAIL.ID
where GIFTINKINDPAYMENTMETHODDETAIL.ID = @REVENUEPAYMENTMETHODID)
set @HASUNPOSTEDEXTRADATA = 1;
else
set @HASUNPOSTEDEXTRADATA = 0;
end
else
set @EXTRADATAISPOSTED = 0;
end
if @ALLOWEDITPAYMENT = 1
if @POSTED = 1
set @ALLOWEDITPAYMENT = 0;
if @ISRECURRINGGIFT = 1
begin
select @CANROLLBACK =
case when REVENUESCHEDULE.STATUSCODE <> 3 and STARTDATE <= dbo.[UFN_REVENUE_GETPREVIOUSTRANSACTIONDATE_BYID_1_1](@REVENUEID, NEXTTRANSACTIONDATE)
then 1 else 0 end,
@CANSKIPTRANSACTION =
case when REVENUESCHEDULE.STATUSCODE <> 3 -- completed
then 1 else 0 end
from dbo.REVENUESCHEDULE
where REVENUESCHEDULE.ID = @REVENUEID;
select
@ISPENDING = REVENUESCHEDULE.ISPENDING,
@BENEFITSWAIVED = REVENUE.BENEFITSWAIVED
from dbo.REVENUE
inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
where REVENUE.ID = @REVENUEID;
end
select @HASADJUSTMENTS = case when count(*) > 0 then 1 else 0 end
from
(select ID from dbo.ADJUSTMENT where REVENUEID = @ID
union all
select STOCKSALEADJUSTMENT.ID from dbo.STOCKSALEADJUSTMENT
inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
where STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID
union all
select GIFTINKINDSALEADJUSTMENT.ID from dbo.GIFTINKINDSALEADJUSTMENT
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID = GIFTINKINDSALE.ID
where GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID
union all
select ID from dbo.PROPERTYDETAILADJUSTMENT where PROPERTYDETAILID = @REVENUEPAYMENTMETHODID
) as A;
--ADDTRANSACTIONID
select @HASUNPOSTEDREVERSAL = case when count(*) > 0 then 1 else 0 end from
(
select REVENUEGLDISTRIBUTION.ID
from dbo.REVENUEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on REVENUEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSALTRANSACTION on GLTRANSACTION.ID = REVERSALTRANSACTION.REVERSEDGLTRANSACTIONID
where REVENUEGLDISTRIBUTION.REVENUEID = @ID and REVERSALTRANSACTION.POSTSTATUSCODE = 1
union all
select STOCKSALEGLDISTRIBUTION.ID
from dbo.STOCKSALEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on STOCKSALEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSALTRANSACTION on GLTRANSACTION.ID = REVERSALTRANSACTION.REVERSEDGLTRANSACTIONID
where STOCKSALEGLDISTRIBUTION.REVENUEID = @ID and REVERSALTRANSACTION.POSTSTATUSCODE = 1
union all
select PROPERTYDETAILGLDISTRIBUTION.ID
from dbo.PROPERTYDETAILGLDISTRIBUTION
inner join dbo.GLTRANSACTION on PROPERTYDETAILGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSALTRANSACTION on GLTRANSACTION.ID = REVERSALTRANSACTION.REVERSEDGLTRANSACTIONID
where PROPERTYDETAILGLDISTRIBUTION.REVENUEID = @ID and REVERSALTRANSACTION.POSTSTATUSCODE = 1
union all
select WRITEOFFGLDISTRIBUTION.ID
from dbo.WRITEOFFGLDISTRIBUTION
inner join dbo.GLTRANSACTION on WRITEOFFGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSALTRANSACTION on GLTRANSACTION.ID = REVERSALTRANSACTION.REVERSEDGLTRANSACTIONID
where WRITEOFFGLDISTRIBUTION.REVENUEID = @ID and REVERSALTRANSACTION.POSTSTATUSCODE = 1
union all
select GIFTINKINDSALEGLDISTRIBUTION.ID
from dbo.GIFTINKINDSALEGLDISTRIBUTION
inner join dbo.GLTRANSACTION on GIFTINKINDSALEGLDISTRIBUTION.GLTRANSACTIONID = GLTRANSACTION.ID
inner join dbo.GLTRANSACTION REVERSALTRANSACTION on GLTRANSACTION.ID = REVERSALTRANSACTION.REVERSEDGLTRANSACTIONID
where GIFTINKINDSALEGLDISTRIBUTION.REVENUEID = @ID and REVERSALTRANSACTION.POSTSTATUSCODE = 1
) as DATA
if @ISPLEDGE = 1
begin
select @HASWRITEOFF = case when count(*) > 0 then 1 else 0 end
from DBO.REVENUE
inner join DBO.WRITEOFF on REVENUE.ID = WRITEOFF.REVENUEID
where REVENUE.ID = @ID
and WRITEOFF.POSTSTATUSCODE <> 2;
select
@ISPENDING = REVENUESCHEDULE.ISPENDING,
@BENEFITSWAIVED = REVENUE.BENEFITSWAIVED
from dbo.REVENUE
inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
where REVENUE.ID = @ID;
end
if @ISMGPLEDGE = 1
begin
select
@ISPENDING = REVENUESCHEDULE.ISPENDING,
@LINKEDRECORDID = RMG.MGSOURCEREVENUEID,
@BENEFITSWAIVED = REVENUE.BENEFITSWAIVED,
@MGPLEDGEISINACTIVE = case when [RMG].ISACTIVE = 0 then 1 else 0 end
from dbo.REVENUE
inner join dbo.REVENUEMATCHINGGIFT RMG on REVENUE.ID = RMG.ID
inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
where REVENUE.ID = @ID;
end
if exists (
select REVENUETRIBUTE.ID
from dbo.REVENUETRIBUTE
inner join dbo.REVENUE on REVENUETRIBUTE.REVENUEID = REVENUE.ID
where REVENUE.ID = @ID
)
select @HASTRIBUTES = 1;
if @ISPLEDGE = 1
begin
if (select coalesce(sum(RT.AMOUNT),0) from dbo.REVENUETRIBUTE RT where REVENUEID in (select ID from dbo.REVENUE where ID = @ID)) < @AMOUNT
select @CANAPPLYTRIBUTE = 1;
end
else if @ISPAYMENT = 1
begin
set @PAYMENTINCLUDESGIFT = 1;
if (select coalesce(sum(RT.AMOUNT),0) from dbo.REVENUETRIBUTE RT where REVENUEID = @ID) < @AMOUNT
set @CANAPPLYTRIBUTE = 1;
end
-- determine the receipt status
if @DONOTRECEIPT = 1
set @RECEIPTSTATUSCODE = 1; -- do not receipt
else
begin
declare @RECEIPTID uniqueidentifier;
declare @RECEIPTDATE datetime;
select top 1 @RECEIPTID = RR.ID, @RECEIPTDATE = RECEIPTDATE
from dbo.REVENUERECEIPT RR
where RR.REVENUEID = @REVENUEID
order by RR.RECEIPTPROCESSDATE desc;
if @RECEIPTID is null
set @RECEIPTSTATUSCODE = 3; -- not receipted
else if @RECEIPTDATE is null
set @RECEIPTSTATUSCODE = 2; -- pending
else
set @RECEIPTSTATUSCODE = 0; -- receipted
end
-- if re-receipts turned off
if (coalesce((select top 1 RERECEIPTPAYMENTS from dbo.RECEIPTPREFERENCEINFO), 1)) = 0
set @NEEDSRERECEIPT = null;
select @RECEIPTCOUNT = count(ID) from dbo.REVENUERECEIPT where REVENUEID = @REVENUEID;
if @ISPLANNEDGIFT = 1
select @PLANNEDGIFTID = PLANNEDGIFTID from dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS where REVENUEID = @REVENUEID;
if @ISORDER = 1
select @SALESORDERID = ID from dbo.SALESORDER where REVENUEID = @REVENUEID;
if @ISPAYMENT = 1
if exists(select ID from dbo.REVENUESPLIT where REVENUEID = @ID and APPLICATIONCODE = 10)
begin
set @ISORDERAPPLICATION = 1;
end
if exists(select ID from dbo.REVENUESPLIT where REVENUEID = @ID and APPLICATIONCODE = 11)
begin
set @ISMISCELLANEOUSPAYMENT = 1;
end
return 0;