USP_DATAFORMTEMPLATE_ADJUSTEDIT_REVENUEPAYMENT_3

The save procedure used by the edit dataform template "Revenue Payment Adjust Form 3".

Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CURRENTAPPUSERID	uniqueidentifier	IN	Input parameter indicating the ID of the current user.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@DATE	datetime	IN	Date
@PAYMENTMETHODCODE	tinyint	IN	Payment method
@OTHERPAYMENTMETHODCODEID	uniqueidentifier	IN	Other method
@CHECKDATE	UDT_FUZZYDATE	IN	Check date
@CHECKNUMBER	nvarchar(20)	IN	Check number
@REFERENCEDATE	UDT_FUZZYDATE	IN	Reference date
@REFERENCENUMBER	nvarchar(20)	IN	Reference number
@CARDHOLDERNAME	nvarchar(255)	IN	Cardholder name
@CREDITCARDNUMBER	nvarchar(4)	IN	Card number
@CREDITTYPECODEID	uniqueidentifier	IN	Card type
@AUTHORIZATIONCODE	nvarchar(20)	IN	Authorization code
@EXPIRESON	UDT_FUZZYDATE	IN	Expires on
@ISSUER	nvarchar(100)	IN	Issuer
@NUMBEROFUNITS	decimal(20, 3)	IN	Number of units
@SYMBOL	nvarchar(25)	IN	Symbol
@MEDIANPRICE	decimal(19, 4)	IN	Median price
@GIFTINKINDSUBTYPECODEID	uniqueidentifier	IN	Subtype
@PROPERTYSUBTYPECODEID	uniqueidentifier	IN	Subtype
@CONSTITUENTACCOUNTID	uniqueidentifier	IN	Account
@GIVENANONYMOUSLY	bit	IN	Payment is anonymous
@DONOTRECEIPT	bit	IN	Do not receipt
@ADJUSTMENTDATE	datetime	IN	Adjustment date
@ADJUSTMENTPOSTDATE	datetime	IN	Adjustment post date
@ADJUSTMENTREASON	nvarchar(300)	IN	Adjustment reason
@REFERENCE	nvarchar(255)	IN	Reference

Definition

Copy



                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADJUSTEDIT_REVENUEPAYMENT_3
                    (
                        @ID uniqueidentifier,
                        @CURRENTAPPUSERID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier,
                        @DATE datetime,
                        @PAYMENTMETHODCODE tinyint,
                        @OTHERPAYMENTMETHODCODEID uniqueidentifier,
                        @CHECKDATE dbo.UDT_FUZZYDATE,
                        @CHECKNUMBER nvarchar(20),
                        @REFERENCEDATE dbo.UDT_FUZZYDATE,
                        @REFERENCENUMBER nvarchar(20),
                        @CARDHOLDERNAME nvarchar(255),
                        @CREDITCARDNUMBER nvarchar(4),
                        @CREDITTYPECODEID uniqueidentifier,
                        @AUTHORIZATIONCODE nvarchar(20),
                        @EXPIRESON dbo.UDT_FUZZYDATE,
                        @ISSUER nvarchar(100),
                        @NUMBEROFUNITS decimal(20,3),
                        @SYMBOL nvarchar(25),
                        @MEDIANPRICE decimal(19,4),
                        @GIFTINKINDSUBTYPECODEID uniqueidentifier,
                        @PROPERTYSUBTYPECODEID uniqueidentifier,
                        @CONSTITUENTACCOUNTID uniqueidentifier,
                        @GIVENANONYMOUSLY bit,
                        @DONOTRECEIPT bit,
                        @ADJUSTMENTDATE datetime,
                        @ADJUSTMENTPOSTDATE datetime,
                        @ADJUSTMENTREASON nvarchar(300),
                        @REFERENCE nvarchar(255)
                    )
                    as
                    set nocount on;

                    declare @CHANGEDATE datetime;
                    declare @ADJUSTMENTID uniqueidentifier;
                    declare @STOCKSALEADJUSTMENTIDS uniqueidentifier;
                    declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;

                    declare @ADJUST bit;
                    declare @CLEARGLDISTRIBUTION bit;

                    declare @PROPERTYDETAILCOUNT int;
                    declare @STOCKSALECOUNT int;

                    set @ADJUST = 0
                    set @PROPERTYDETAILCOUNT = 0;
                    set @STOCKSALECOUNT = 0;

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

                    begin try

                        set @CHANGEDATE = getdate();

                        if @CHANGEAGENTID is null  
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        /* Check if paymethod changed */
                        --if (select COUNT(REVENUE.ID) from dbo.REVENUE where REVENUE.TRANSACTIONID = @ID 

                        --    and PAYMENTMETHODCODE = @PAYMENTMETHODCODE) = 0

                        if (select count(REVENUEPAYMENTMETHOD.ID) from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @ID and PAYMENTMETHODCODE = @PAYMENTMETHODCODE) = 0 
                        begin
                            set @ADJUST = 1;
                            set @CLEARGLDISTRIBUTION = 1;
                        end

                        /* Already adjusted */
                        if @ADJUST = 0
                            if (select count(ADJUSTMENT.ID)
                                    from dbo.ADJUSTMENT
                                    inner join dbo.REVENUE
                                        on REVENUE.ID = ADJUSTMENT.REVENUEID
                                    where REVENUE.ID = @ID and ADJUSTMENT.POSTSTATUSCODE = 1) > 0
                                set @ADJUST = 1;

                        /* If there was a change to GL related data log an adjustment for each revenue in the transaction */
                        if @ADJUST = 1
                        begin
                            -- update the payment information for each revenue record in the transaction

                            exec dbo.USP_SAVE_ADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;

                            select @STOCKSALECOUNT = count(STOCKSALE.ID)
                            from dbo.STOCKSALE 
                            where STOCKSALE.STOCKDETAILID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0;

                            select @PROPERTYDETAILCOUNT = count(PROPERTYDETAIL.ID)
                            from dbo.PROPERTYDETAIL 
                            where PROPERTYDETAIL.ID = @REVENUEPAYMENTMETHODID and SALEPOSTSTATUSCODE = 0;

                            /* If sold stock has been posted, log stock detail adjustment */
                            if (@PAYMENTMETHODCODE = 4) and (@STOCKSALECOUNT > 0) 
                            begin
                                exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON, @STOCKSALEADJUSTMENTIDS output
                            end

                            /* If sold property has been posted, log property detail adjustment */
                            else if (@PAYMENTMETHODCODE = 5) and (@PROPERTYDETAILCOUNT > 0) 
                            begin
                                exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @REVENUEPAYMENTMETHODID, @PROPERTYDETAILADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;                        
                            end

                        end


                        /* Save payment information */
                        exec dbo.USP_REVENUEPAYMENT_EDIT_2 @ID, @CURRENTAPPUSERID, @CHANGEAGENTID, @DATE, 
                            @PAYMENTMETHODCODE, @CHECKDATE, @CHECKNUMBER, @REFERENCEDATE, @REFERENCENUMBER, 
                            @CARDHOLDERNAME, @CREDITCARDNUMBER, @CREDITTYPECODEID, @AUTHORIZATIONCODE, @EXPIRESON, 
                            @ISSUER, @NUMBEROFUNITS, @SYMBOL, @MEDIANPRICE, @GIFTINKINDSUBTYPECODEID,
                            @PROPERTYSUBTYPECODEID, @CONSTITUENTACCOUNTID, @GIVENANONYMOUSLY, @DONOTRECEIPT, @CHANGEDATE, 
                            @OTHERPAYMENTMETHODCODEID, @REFERENCE;

                        -- if the payment method has changed, clear any user-defined gl distributions for this revenue record

                        if @CLEARGLDISTRIBUTION = 1
                        begin
                            --Clear GL

                            --Cache CONTEXT INFO

                            declare @contextCache varbinary(128);
                            set @contextCache = CONTEXT_INFO();

                            if not @CHANGEAGENTID is null
                                set CONTEXT_INFO @CHANGEAGENTID;

                            delete from dbo.REVENUEGLDISTRIBUTION where REVENUEGLDISTRIBUTION.REVENUEID = @ID and OUTDATED = 0;
                            delete from dbo.STOCKSALEGLDISTRIBUTION where STOCKSALEGLDISTRIBUTION.REVENUEID = @ID and OUTDATED = 0;
                            delete from dbo.PROPERTYDETAILGLDISTRIBUTION where PROPERTYDETAILGLDISTRIBUTION.REVENUEID = @ID and OUTDATED = 0;

                            --Restore CONTEXT_INFO

                            if not @contextCache is null
                                set CONTEXT_INFO @contextCache;

                            -- Add new GL distributions

                            exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

                            -- Add new stock detail GL distributions

                            exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;

                            -- Add new property detail GL distributions

                            exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @ID, @CHANGEAGENTID, @CHANGEDATE;
                        end

                        /* add adjustment history information */
                        if @ADJUST = 1
                        begin
                            /*call USP_ADJUSTMENTHISTORY_*_SAVEHISTORY after the revenue tables are updated */
                            if @ADJUSTMENTID is not null
                                exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @ID, @CHANGEAGENTID, null, @ADJUSTMENTID;

                            if @STOCKSALEADJUSTMENTIDS is not null
                                exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @STOCKSALEADJUSTMENTIDS;

                            if @PROPERTYDETAILADJUSTMENTID is not null
                                exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @REVENUEPAYMENTMETHODID, @CHANGEAGENTID, null, @PROPERTYDETAILADJUSTMENTID;

                        end


                    end try

                    begin catch
                        exec dbo.USP_RAISE_ERROR;
                    end catch
                    return 0;