USP_DATAFORMTEMPLATE_EDITLOAD_PAYMENTADJUST3

Loads payment information for a posted payment.

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
@CONSTITUENTNAME	nvarchar(255)	INOUT	Constituent
@DATE	datetime	INOUT	Date
@AMOUNT	money	INOUT	Amount
@APPLICATIONCODE	tinyint	INOUT	Application
@RECEIPTAMOUNT	money	INOUT	Receipt amount
@REVENUESTREAMS	xml	INOUT	Revenue streams
@SOURCECODE	nvarchar(50)	INOUT	Source code
@APPEALID	uniqueidentifier	INOUT	Appeal
@BENEFITS	xml	INOUT	Benefits
@BENEFITSWAIVED	bit	INOUT	Benefits waived
@GIVENANONYMOUSLY	bit	INOUT	Payment is anonymous
@MAILINGID	uniqueidentifier	INOUT	Mailing
@CHANNELCODEID	uniqueidentifier	INOUT	Inbound channel
@DONOTRECEIPT	bit	INOUT	Do not receipt
@DONOTACKNOWLEDGE	bit	INOUT	Do not acknowledge
@REFERENCE	nvarchar(255)	INOUT	Reference
@POSTSTATUSCODE	tinyint	INOUT	Post status
@POSTDATE	datetime	INOUT	Post date
@CANHAVEUNAPPLIEDMG	bit	INOUT
@ADJUSTMENTDATE	datetime	INOUT	Adjustment date
@ADJUSTMENTPOSTDATE	datetime	INOUT	Adjustment post date
@ADJUSTMENTPOSTSTATUSCODE	tinyint	INOUT	Adjustment post status
@ADJUSTMENTREASON	nvarchar(300)	INOUT	Adjustment details
@PENDINGADJUSTMENTCODE	tinyint	INOUT
@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.
@PAYMENTMETHODCODE	tinyint	INOUT	Payment method
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	INOUT	Other method
@CHECKDATE	UDT_FUZZYDATE	INOUT	Check date
@CHECKNUMBER	nvarchar(20)	INOUT	Check number
@REFERENCEDATE	UDT_FUZZYDATE	INOUT	Reference date
@REFERENCENUMBER	nvarchar(20)	INOUT	Reference number
@CARDHOLDERNAME	nvarchar(255)	INOUT	Name on card
@CREDITCARDNUMBER	nvarchar(4)	INOUT	Card number
@CREDITTYPECODEID	uniqueidentifier	INOUT	Card type
@AUTHORIZATIONCODE	nvarchar(20)	INOUT	Authorization code
@EXPIRESON	UDT_FUZZYDATE	INOUT	Expires on
@ISSUER	nvarchar(100)	INOUT	Issuer
@NUMBEROFUNITS	decimal(20, 3)	INOUT	Number of units
@SYMBOL	nvarchar(25)	INOUT	Symbol
@MEDIANPRICE	decimal(19, 4)	INOUT	Median price
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	INOUT	Subtype
@PROPERTYSUBTYPECODEID	uniqueidentifier	INOUT	Subtype
@CONSTITUENTACCOUNTID	uniqueidentifier	INOUT	Account
@DIRECTDEBITRESULTCODE	nvarchar(10)	INOUT	Result code
@LOWPRICE	decimal(19, 4)	INOUT	Low price
@HIGHPRICE	decimal(19, 4)	INOUT	High price
@HASPOSTEDSOLDSTOCK	bit	INOUT	Has sold stock
@HASSOLDSTOCK	bit	INOUT	Has sold stock
@HASSOLDPROPERTY	bit	INOUT	Has sold property
@ADJUSTMENTREASONCODEID	uniqueidentifier	INOUT	Adjustment reason
@GIFTINKINDITEMNAME	nvarchar(100)	INOUT	Item name
@GIFTINKINDDISPOSITIONCODE	tinyint	INOUT	Disposition
@GIFTINKINDNUMBEROFUNITS	int	INOUT	Number of units
@GIFTINKINDFAIRMARKETVALUE	money	INOUT	Fair market value per unit
@GIFTINKINDUSEAPPRAISALTHRESHOLD	bit	INOUT
@GIFTINKINDAPPRAISALTHRESHOLD	money	INOUT
@GIFTINKINDGIFTAPPRAISED	bit	INOUT
@DEPOSITID	uniqueidentifier	INOUT
@DIRECTDEBITISREJECTED	bit	INOUT	Rejected
@ADJUSTMENTID	uniqueidentifier	INOUT	Adjustment ID
@DISABLEADJUSTMENTPOSTSTATUS	bit	INOUT	Disable adjustment post status
@PERCENTAGEBENEFITS	xml	INOUT	Benefits
@HASCREDITCARDBEENCHARGED	bit	INOUT
@BASECURRENCYID	uniqueidentifier	INOUT
@TRANSACTIONCURRENCYID	uniqueidentifier	INOUT
@BASEEXCHANGERATEID	uniqueidentifier	INOUT
@EXCHANGERATE	decimal(20, 8)	INOUT
@HADSPOTRATE	bit	INOUT
@BASEDECIMALDIGITS	tinyint	INOUT
@BASEROUNDINGTYPECODE	tinyint	INOUT
@TRANSACTIONCURRENCYDESCRIPTION	nvarchar(110)	INOUT
@HASMATCHINGGIFTCLAIM	bit	INOUT
@ADJUSTMATCHINGGIFTCLAIMS	tinyint	INOUT
@UPDATEGIFTFEEOPTION	tinyint	INOUT
@UPDATETRIBUTEOPTION	tinyint	INOUT
@HASTRIBUTE	bit	INOUT
@VALIDATETRIBUTES	bit	INOUT
@ALLOWGLDISTRIBUTIONS	bit	INOUT
@HASSOLDGIFTINKIND	bit	INOUT
@HASPOSTEDSOLDGIFTINKIND	bit	INOUT
@PDACCOUNTSYSTEMID	uniqueidentifier	INOUT
@REQUIREDEPOSIT	bit	INOUT
@SEPAMANDATEID	uniqueidentifier	INOUT
@FINDERNUMBER	bigint	INOUT

Definition

Copy



            CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDITLOAD_PAYMENTADJUST3
            (
                @ID uniqueidentifier,
                @DATALOADED bit = 0 output,
                @CONSTITUENTID uniqueidentifier = null output,    
                @CONSTITUENTNAME nvarchar(255) = null output,
                @DATE datetime = null output,
                @AMOUNT money = null output,
                @APPLICATIONCODE tinyint = null output,
                @RECEIPTAMOUNT money = null output,
                @REVENUESTREAMS xml = null output,
                @SOURCECODE nvarchar(50) = null output,
                @APPEALID uniqueidentifier = null output,
                @BENEFITS xml = null output,
                @BENEFITSWAIVED bit = null output,
                @GIVENANONYMOUSLY bit = null output,
                @MAILINGID uniqueidentifier = null output,
                @CHANNELCODEID uniqueidentifier = null output,
                @DONOTRECEIPT bit = null output,
                @DONOTACKNOWLEDGE bit = null output,
                @REFERENCE nvarchar(255) = null output,
                @POSTSTATUSCODE tinyint = null output,
                @POSTDATE datetime = null output,
                @CANHAVEUNAPPLIEDMG bit = null output,
                @ADJUSTMENTDATE datetime = null output,
                @ADJUSTMENTPOSTDATE datetime = null output,
                @ADJUSTMENTPOSTSTATUSCODE tinyint = null output,
                @ADJUSTMENTREASON nvarchar(300) = null output,
                @PENDINGADJUSTMENTCODE tinyint = null output,
                @TSLONG bigint = 0 output,
                @PAYMENTMETHODCODE tinyint = null output,
                @OTHERPAYMENTMETHODCODEID uniqueidentifier = null output,
                @CHECKDATE dbo.UDT_FUZZYDATE = null output,
                @CHECKNUMBER nvarchar(20) = null output,
                @REFERENCEDATE dbo.UDT_FUZZYDATE = null output,
                @REFERENCENUMBER nvarchar(20) = null output,                    
                @CARDHOLDERNAME nvarchar(255) = null output,
                @CREDITCARDNUMBER nvarchar(4) = null output,
                @CREDITTYPECODEID uniqueidentifier = null output,
                @AUTHORIZATIONCODE nvarchar(20) = null output,
                @EXPIRESON dbo.UDT_FUZZYDATE = null output,
                @ISSUER nvarchar(100) = null output,
                @NUMBEROFUNITS decimal(20,3) = null output,
                @SYMBOL nvarchar(25) = null output,
                @MEDIANPRICE decimal(19,4) = null output,
                @GIFTINKINDSUBTYPECODEID uniqueidentifier = null output,
                @PROPERTYSUBTYPECODEID uniqueidentifier = null output,                    
                @CONSTITUENTACCOUNTID uniqueidentifier = null output,
                @DIRECTDEBITRESULTCODE nvarchar(10) = null output,
                @LOWPRICE decimal(19,4) = null output,
                @HIGHPRICE decimal(19,4) = null output,
                @HASPOSTEDSOLDSTOCK bit = null output,
                @HASSOLDSTOCK bit = null output,
                @HASSOLDPROPERTY bit = null output,
                @ADJUSTMENTREASONCODEID uniqueidentifier = null output,
                @GIFTINKINDITEMNAME nvarchar(100) = null output,
                @GIFTINKINDDISPOSITIONCODE tinyint = null output,
                @GIFTINKINDNUMBEROFUNITS int = null output,
                @GIFTINKINDFAIRMARKETVALUE money = null output,
                @GIFTINKINDUSEAPPRAISALTHRESHOLD bit = null output,
                @GIFTINKINDAPPRAISALTHRESHOLD money = null output,
                @GIFTINKINDGIFTAPPRAISED bit = null output,
                @DEPOSITID uniqueidentifier = null output,
                @DIRECTDEBITISREJECTED bit = null output,
                @ADJUSTMENTID uniqueidentifier = null output,
                @DISABLEADJUSTMENTPOSTSTATUS bit = null output,
                @PERCENTAGEBENEFITS xml = null output,
                @HASCREDITCARDBEENCHARGED bit = null output,
                @BASECURRENCYID uniqueidentifier = null output,
                @TRANSACTIONCURRENCYID uniqueidentifier = null output,
                @BASEEXCHANGERATEID uniqueidentifier = null output,
                @EXCHANGERATE decimal(20,8) = null output,
                @HADSPOTRATE bit = null output,
                @BASEDECIMALDIGITS tinyint = null output,
                @BASEROUNDINGTYPECODE tinyint = null output,
                @TRANSACTIONCURRENCYDESCRIPTION nvarchar(110) = null output,
                @HASMATCHINGGIFTCLAIM bit = null output,
                @ADJUSTMATCHINGGIFTCLAIMS tinyint = null output,
                @UPDATEGIFTFEEOPTION tinyint = null output,
                @UPDATETRIBUTEOPTION tinyint = null output,
                @HASTRIBUTE bit = null output,
                @VALIDATETRIBUTES bit = null output,
                @ALLOWGLDISTRIBUTIONS bit = null output,
                @HASSOLDGIFTINKIND bit = null output,
                @HASPOSTEDSOLDGIFTINKIND bit = null output,
                @PDACCOUNTSYSTEMID uniqueidentifier = null output,
                @REQUIREDEPOSIT bit = null output,
                @SEPAMANDATEID uniqueidentifier = null output,
                @FINDERNUMBER bigint = null output
            )
            as
            set nocount on;

            select  
                @ADJUSTMENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate()),
                @ADJUSTMENTPOSTSTATUSCODE = 0;

            select top 1
                @ADJUSTMENTDATE = ADJUSTMENT.DATE,
                @ADJUSTMENTPOSTDATE = ADJUSTMENT.POSTDATE,
                @ADJUSTMENTPOSTSTATUSCODE = ADJUSTMENT.POSTSTATUSCODE,
                @ADJUSTMENTREASON = ADJUSTMENT.REASON,
                @ADJUSTMENTREASONCODEID = ADJUSTMENT.REASONCODEID,
                @ADJUSTMENTID = ADJUSTMENT.ID,
                @DISABLEADJUSTMENTPOSTSTATUS = case when (ADJUSTMENT.POSTSTATUSCODE <> 0 and ADJUSTMENT.ADJUSTMENTCODE <> 0) then 1 else 0 end
            from dbo.ADJUSTMENT
            where ADJUSTMENT.REVENUEID = @ID and ADJUSTMENT.POSTSTATUSCODE = 1
            order by dateadded desc;

    if @ADJUSTMENTREASONCODEID is null
    begin
              select
                  @ADJUSTMENTDATE = DATE,
                  @ADJUSTMENTPOSTDATE = POSTDATE,
                  @ADJUSTMENTPOSTSTATUSCODE = 1,
                  @ADJUSTMENTREASON = REASON,
                  @ADJUSTMENTREASONCODEID = REASONCODEID,
                  @ADJUSTMENTID = ID
              from dbo.BENEFITADJUSTMENT
              where REVENUEID = @ID and POSTSTATUSCODE = 1
    end

            set @VALIDATETRIBUTES = 1;

            exec dbo.USP_PAYMENT_LOAD 
                @ID, @DATALOADED output, @CONSTITUENTID output,    @CONSTITUENTNAME output,
                @DATE output, @AMOUNT output, @APPLICATIONCODE output, @RECEIPTAMOUNT output, 
                @REVENUESTREAMS output, @SOURCECODE output, @POSTSTATUSCODE output, @POSTDATE output,
                @APPEALID output, @BENEFITS output, @BENEFITSWAIVED output, 
                @GIVENANONYMOUSLY output, @MAILINGID output, @CHANNELCODEID output, 
                @DONOTRECEIPT output, @REFERENCE output, @TSLONG output, @DONOTACKNOWLEDGE output,
                @FINDERNUMBER output, @PERCENTAGEBENEFITS output, @BASECURRENCYID output, @TRANSACTIONCURRENCYID output,
                @BASEEXCHANGERATEID output, @EXCHANGERATE output, @HADSPOTRATE output,
                @BASEDECIMALDIGITS output, @BASEROUNDINGTYPECODE output, @TRANSACTIONCURRENCYDESCRIPTION output;


            exec dbo.USP_REVENUE_GETPAYMENTDETAILS
                @REVENUEID = @ID,
                @PAYMENTMETHODCODE = @PAYMENTMETHODCODE output,
                @CHECKDATE = @CHECKDATE output,
                @CHECKNUMBER = @CHECKNUMBER output,
                @REFERENCEDATE = @REFERENCEDATE output,
                @REFERENCENUMBER = @REFERENCENUMBER output,                    
                @CARDHOLDERNAME = @CARDHOLDERNAME output,
                @CREDITCARDNUMBER = @CREDITCARDNUMBER output,
                @CREDITTYPECODEID = @CREDITTYPECODEID output,
                @AUTHORIZATIONCODE = @AUTHORIZATIONCODE output,
                @EXPIRESON = @EXPIRESON output,
                @ISSUER = @ISSUER output,
                @NUMBEROFUNITS = @NUMBEROFUNITS output,
                @SYMBOL = @SYMBOL output,
                @MEDIANPRICE = @MEDIANPRICE output,
                @GIFTINKINDSUBTYPECODEID = @GIFTINKINDSUBTYPECODEID output,
                @PROPERTYSUBTYPECODEID = @PROPERTYSUBTYPECODEID output,                    
                @CONSTITUENTACCOUNTID = @CONSTITUENTACCOUNTID output,
                @OTHERPAYMENTMETHODCODEID = @OTHERPAYMENTMETHODCODEID output,
                @DIRECTDEBITRESULTCODE = @DIRECTDEBITRESULTCODE output,
                @LOWPRICE = @LOWPRICE output,
                @HIGHPRICE = @HIGHPRICE output,
                @GIFTINKINDITEMNAME = @GIFTINKINDITEMNAME output,
                @GIFTINKINDDISPOSITIONCODE = @GIFTINKINDDISPOSITIONCODE output,
                @GIFTINKINDNUMBEROFUNITS = @GIFTINKINDNUMBEROFUNITS output,
                @GIFTINKINDFAIRMARKETVALUE = @GIFTINKINDFAIRMARKETVALUE output,
                @DIRECTDEBITISREJECTED = @DIRECTDEBITISREJECTED output,
                @HASCREDITCARDBEENCHARGED = @HASCREDITCARDBEENCHARGED output,
                @SEPAMANDATEID = @SEPAMANDATEID output;

            select @HASMATCHINGGIFTCLAIM = 1
            from dbo.REVENUEMATCHINGGIFT
                left join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUEMATCHINGGIFT.ID
            where REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = @ID
                and REVENUEMATCHINGGIFT.MATCHINGGIFTCONDITIONID is not null
                and INSTALLMENTSPLITPAYMENT.ID is null;

            select @HASTRIBUTE = count(1)
            from dbo.REVENUETRIBUTE
            where REVENUETRIBUTE.REVENUEID = @ID;
            set @UPDATETRIBUTEOPTION = 0;

            set @ADJUSTMATCHINGGIFTCLAIMS = 3;
            set @UPDATEGIFTFEEOPTION = 0;

            select @CANHAVEUNAPPLIEDMG = ISORGANIZATION
            from dbo.CONSTITUENT
            where ID = @CONSTITUENTID;

            declare @REVENUEPAYMENTMETHODID uniqueidentifier
            select @REVENUEPAYMENTMETHODID = ID
            from dbo.REVENUEPAYMENTMETHOD
            where REVENUEID = @ID

            set @PENDINGADJUSTMENTCODE = 0;

            if @PENDINGADJUSTMENTCODE = 0
                begin
                    if ((select count(STOCKSALEADJUSTMENT.ID) from dbo.STOCKSALEADJUSTMENT
                            inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
                            where STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID and STOCKSALEADJUSTMENT.POSTSTATUSCODE <> 0) > 0)
                        set @PENDINGADJUSTMENTCODE = 1;    
                end
            if @PENDINGADJUSTMENTCODE = 0
                begin
                    if ((select count(GIFTINKINDSALEADJUSTMENT.ID) from dbo.GIFTINKINDSALEADJUSTMENT
                            inner join dbo.GIFTINKINDSALE on GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID = GIFTINKINDSALE.ID
                            where GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID and GIFTINKINDSALEADJUSTMENT.POSTSTATUSCODE <> 0) > 0)
                        set @PENDINGADJUSTMENTCODE = 1;    
                end
            if @PENDINGADJUSTMENTCODE = 0
                begin
                    if ((select count(PROPERTYDETAILADJUSTMENT.ID) from dbo.PROPERTYDETAILADJUSTMENT 
                            where PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = @REVENUEPAYMENTMETHODID and PROPERTYDETAILADJUSTMENT.POSTSTATUSCODE <> 0) > 0)
                        set @PENDINGADJUSTMENTCODE = 1;    
                end

            set @HASPOSTEDSOLDSTOCK = 0
            if exists (select 1 from dbo.STOCKSALE where STOCKDETAILID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0)
                set @HASPOSTEDSOLDSTOCK = 1;

            set @HASSOLDSTOCK = 0;
            if @PAYMENTMETHODCODE = 4
            begin
                if exists (    select 1 from dbo.STOCKSALE 
                            inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                            where REVENUEPAYMENTMETHOD.REVENUEID = @ID)
                    set @HASSOLDSTOCK = 1;
            end

            set @HASPOSTEDSOLDGIFTINKIND = 0
            if exists (select 1 from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0)
                set @HASPOSTEDSOLDGIFTINKIND = 1;

            set @HASSOLDGIFTINKIND = 0;
            if @PAYMENTMETHODCODE = 6
            begin
                if exists (    select 1 from dbo.GIFTINKINDSALE 
                            inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
                            where REVENUEPAYMENTMETHOD.REVENUEID = @ID)
                    set @HASSOLDGIFTINKIND = 1;
            end

            set @HASSOLDPROPERTY = 0
            if @PAYMENTMETHODCODE = 5
            begin
                if exists ( select 1 from dbo.PROPERTYDETAIL 
                            inner join dbo.REVENUEPAYMENTMETHOD on PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                            where ISSOLD = 1 and REVENUEPAYMENTMETHOD.REVENUEID = @ID)
                    set @HASSOLDPROPERTY = 1
            end

            select top 1
                @GIFTINKINDUSEAPPRAISALTHRESHOLD = USEAPPRAISALTHRESHOLD,
                @GIFTINKINDAPPRAISALTHRESHOLD = APPRAISALTHRESHOLD
            from
                dbo.GIFTINKINDAPPRAISALSETTINGS

            if exists (    select top(1) 1 
                        from dbo.GIFTINKINDAPPRAISAL inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDPAYMENTMETHODDETAILID = REVENUEPAYMENTMETHOD.ID
                        where REVENUEPAYMENTMETHOD.REVENUEID = @ID)
                set @GIFTINKINDGIFTAPPRAISED = 1
            else
                set @GIFTINKINDGIFTAPPRAISED = 0

            select @DEPOSITID = DEPOSITID
            from dbo.BANKACCOUNTDEPOSITPAYMENT
            where ID = @ID;

    if @ADJUSTMENTPOSTDATE is null
    begin
    if not @DEPOSITID is null
      select @ADJUSTMENTPOSTDATE = POSTDATE 
      from dbo.BANKACCOUNTDEPOSITPAYMENT 
      inner join dbo.BANKACCOUNTDEPOSIT on BANKACCOUNTDEPOSITPAYMENT.DEPOSITID = BANKACCOUNTDEPOSIT.ID
      inner join dbo.BANKACCOUNTTRANSACTION on BANKACCOUNTDEPOSIT.ID = BANKACCOUNTTRANSACTION.ID
      where BANKACCOUNTDEPOSITPAYMENT.ID = @ID;
    else
      select @ADJUSTMENTPOSTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
    end

  -- Check GL business rule for this account system and set to 'Do not post' if needed.

  select @PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID, @REQUIREDEPOSIT = PDACCOUNTSYSTEM.REQUIREDPOSIT 
    from dbo.FINANCIALTRANSACTION 
    inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
    where FINANCIALTRANSACTION.ID = @ID;

  set @ALLOWGLDISTRIBUTIONS = dbo.UFN_PDACCOUNTSYSTEM_ALLOWGLDISTRIBUTIONS(@PDACCOUNTSYSTEMID);