USP_DATAFORMTEMPLATE_ADJUST_STOCKSALEGLDISTRIBUTION

The save procedure used by the edit dataform template "Stock Sale GL Distribution Adjust Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@GLDISTRIBUTION	xml	IN	Sold Stock GL distribution
@ADJUSTMENTDATE	datetime	IN	Adjusted Date
@ADJUSTMENTPOSTDATE	datetime	IN	Adjusted Post Date
@ADJUSTMENTREASON	nvarchar(300)	IN	Adjustment reason
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
Definition

Copy


                CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADJUST_STOCKSALEGLDISTRIBUTION
                    (
                        @ID uniqueidentifier,
                        @GLDISTRIBUTION xml,
                        @ADJUSTMENTDATE datetime,
                        @ADJUSTMENTPOSTDATE datetime,
                        @ADJUSTMENTREASON nvarchar(300),
                        @CHANGEAGENTID uniqueidentifier = null
                    )
                as
                    set nocount on;

                    declare @ADJUST bit;
                    declare @ADJUSTMENTEXISTS bit;
                    declare @STOCKSALEADJUSTMENTID uniqueidentifier;

                    set @ADJUST = 0;                    
                    set @ADJUSTMENTEXISTS = 0;

                    declare @CHANGEDATE datetime;
                    set @CHANGEDATE = getdate();

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

                        if (dbo.UFN_GLDISTRIBUTION_DEBITSEQUALCREDITS(@GLDISTRIBUTION) = 0)
                            raiserror('The sum of the debit accounts must equal the sum of the credit accounts.', 13, 1)

                        /* Already adjusted */
                        if exists (    select 1
                                    from dbo.STOCKSALEADJUSTMENT as ADJUSTMENT
                                    where STOCKSALEID = @ID and POSTSTATUSCODE = 1)
                        begin
                            set @ADJUST = 1;
                            set @ADJUSTMENTEXISTS = 1;
                        end

                        /* Distributions Changed */
                        if @ADJUST = 0
                            set @ADJUST = dbo.UFN_STOCKDETAIL_DISTRIBUTIONCHANGED(@ID, @GLDISTRIBUTION)

                        /* If there was a change to GL related data log an adjustment */
                        if @ADJUST = 1
                        begin
                            set @STOCKSALEADJUSTMENTID = null;
                            exec dbo.USP_SAVE_STOCKSALEADJUSTMENT @ID, @STOCKSALEADJUSTMENTID output, @CHANGEAGENTID, @CHANGEDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON;
                        end

                        declare @POSTDATE datetime;
                        select @POSTDATE = SALEPOSTDATE from dbo.STOCKSALE where ID = @ID;

                        -- If an unposted adjustment already exists, update the stock sale GL distribution rows.

                        -- Otherwise, insert new stock sale gl distribution rows using @GLDISTRIBUTION.

                        if @ADJUSTMENTEXISTS = 1 
                        begin
                            exec dbo.USP_REVENUE_GETSTOCKSALEGLDISTRIBUTION_CUSTOMUPDATEFROMXML @ID, @GLDISTRIBUTION, @POSTDATE, @CHANGEAGENTID;
                        end
                        else
                        begin
                            declare @JOURNAL nvarchar(50);
                            set @JOURNAL = 'Blackbaud Enterprise';

                            declare @REVENUEID uniqueidentifier;
                            select @REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
                            from dbo.STOCKSALE
                            inner join REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                            where STOCKSALE.ID = @ID

                            declare @DISTRIBUTIONS table(
                                GLTRANSACTIONID uniqueidentifier,
                                ACCOUNT nvarchar(100),
                                AMOUNT money,
                                PROJECT nvarchar(100),
                                REFERENCE nvarchar(255),
                                TRANSACTIONTYPECODE tinyint,
                                GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier
                            );

                            -- Get the user-defined GL distributions for the stock detail record.

                            insert into @DISTRIBUTIONS(GLTRANSACTIONID, ACCOUNT, AMOUNT, PROJECT, REFERENCE, TRANSACTIONTYPECODE, GLPAYMENTMETHODREVENUETYPEMAPPINGID)
                            select 
                                newid(),
                                STOCKDISTRIBUTIONS.ACCOUNT,
                                STOCKDISTRIBUTIONS.AMOUNT,
                                STOCKDISTRIBUTIONS.PROJECT,
                                STOCKDISTRIBUTIONS.REFERENCE, 
                                STOCKDISTRIBUTIONS.TRANSACTIONTYPECODE,
                                STOCKDISTRIBUTIONS.GLPAYMENTMETHODREVENUETYPEMAPPINGID
                            from 
                                dbo.UFN_REVENUE_GETSTOCKSALEGLDISTRIBUTION_FROMITEMLISTXML(@GLDISTRIBUTION) STOCKDISTRIBUTIONS;

                            -- Insert rows in GLTRANSACTION table

                            insert into dbo.GLTRANSACTION(ID, TRANSACTIONTYPECODE, ACCOUNT, AMOUNT, PROJECT, REFERENCE, POSTDATE, JOURNAL, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                            select
                                GLTRANSACTIONID,
                                TRANSACTIONTYPECODE,
                                ACCOUNT,
                                AMOUNT,
                                PROJECT,
                                REFERENCE,
                                @ADJUSTMENTPOSTDATE,
                                @JOURNAL,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE
                            from 
                                @DISTRIBUTIONS;

                            insert into dbo.STOCKSALEGLDISTRIBUTION
                                (ID,GLTRANSACTIONID,STOCKSALEID,REVENUEID,GLPAYMENTMETHODREVENUETYPEMAPPINGID,PROJECT,REFERENCE,AMOUNT,ACCOUNT,TRANSACTIONTYPECODE,ADDEDBYID,CHANGEDBYID,DATEADDED,DATECHANGED)
                            select
                                newid(),
                                GLTRANSACTIONID,
                                @ID,
                                @REVENUEID,
                                GLPAYMENTMETHODREVENUETYPEMAPPINGID,
                                PROJECT,
                                REFERENCE,
                                AMOUNT,
                                ACCOUNT,
                                TRANSACTIONTYPECODE,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE
                            from 
                                @DISTRIBUTIONS;
                        end

                        if @ADJUST = 1
                            exec dbo.USP_ADJUSTMENTHISTORY_STOCKSALE_SAVEHISTORY @ID, @CHANGEAGENTID, @CHANGEDATE, @STOCKSALEADJUSTMENTID;
                    end try

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

                    return 0;