USP_DATAFORMTEMPLATE_ADJUST_STOCKSALE

The save procedure used by the edit dataform template "Stock Sale By Transaction Edit Form".
Parameters

Parameter	Parameter Type	Mode	Description
@ID	uniqueidentifier	IN	The input ID parameter indicating the ID of the record being edited.
@CHANGEAGENTID	uniqueidentifier	IN	Input parameter indicating the ID of the change agent invoking the procedure.
@SALEDATE	date	IN	Date of sale
@SALEAMOUNT	money	IN	Sale amount
@FEE	money	IN	Fees
@NUMBEROFUNITS	decimal(20, 3)	IN	Units sold
@LOWPRICE	decimal(19, 4)	IN	Low price
@MEDIANPRICE	decimal(19, 4)	IN	Median price
@HIGHPRICE	decimal(19, 4)	IN	High price
@ADJUSTMENTPOSTDATE	datetime	IN	Adjustment post date
@ADJUSTMENTDATE	datetime	IN	Adjustment date
@ADJUSTMENTREASON	nvarchar(100)	IN	Adjustment reason
Definition

Copy


                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADJUST_STOCKSALE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier,
                        @SALEDATE date,
                        @SALEAMOUNT money,
                        @FEE money,
                        @NUMBEROFUNITS decimal(20,3),
                        @LOWPRICE decimal(19,4),
                        @MEDIANPRICE decimal(19,4),
                        @HIGHPRICE decimal(19,4),
                        @ADJUSTMENTPOSTDATE datetime,
                        @ADJUSTMENTDATE datetime,
                        @ADJUSTMENTREASON nvarchar(100)
                    )
                    as
                        set nocount on

                        declare @ORIGINALFEE money, @ORIGINALSALEAMOUNT money, @ORIGINALNUMBEROFUNITS decimal(20,3)
                        declare @SALEPOSTSTATUSCODE tinyint, @STOCKDETAILID uniqueidentifier, @REVENUEID uniqueidentifier
                        select
                            @ORIGINALFEE = STOCKSALE.FEE,
                            @ORIGINALSALEAMOUNT = STOCKSALE.SALEAMOUNT,
                            @SALEPOSTSTATUSCODE = STOCKSALE.SALEPOSTSTATUSCODE,
                            @STOCKDETAILID = STOCKSALE.STOCKDETAILID,
                            @ORIGINALNUMBEROFUNITS = STOCKSALE.NUMBEROFUNITS,
                            @REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
                        from dbo.STOCKSALE
                        inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                        where STOCKSALE.ID = @ID

                        declare @NEEDSADJUSTMENT bit
                        set @NEEDSADJUSTMENT = 0
                        if coalesce(@ORIGINALFEE, 0) <> coalesce(@FEE, 0) or
                            coalesce(@ORIGINALSALEAMOUNT, 0) <> coalesce(@SALEAMOUNT, 0) or
                            coalesce(@ORIGINALNUMBEROFUNITS, 0) <> coalesce(@NUMBEROFUNITS, 0)
                        begin
                            set @NEEDSADJUSTMENT = 1
                        end

                        -- Verify the transaction has already been posted            

                        if @SALEPOSTSTATUSCODE <> 0 -- Posted

                        begin
                            raiserror('STOCKSALEMUSTBEPOSTED', 13, 1)
                            return 1                        
                        end

                        -- Already adjusted

                        if @NEEDSADJUSTMENT = 0
                            if exists (    select 1 from dbo.STOCKSALEADJUSTMENT
                                        where STOCKSALEID = @ID and POSTSTATUSCODE = 1)
                                set @NEEDSADJUSTMENT = 1

                        -- Make sure the adjustment date fields are set if it will be adjusted

                        if @NEEDSADJUSTMENT = 1 and @ADJUSTMENTPOSTDATE is null
                        begin
                            raiserror('ADJUSTMENTPOSTDATEREQUIRED', 13, 1)
                            return 1
                        end

                        if @NEEDSADJUSTMENT = 1 and @ADJUSTMENTDATE is null
                        begin
                            raiserror('ADJUSTMENTDATEREQUIRED', 13, 1)
                            return 1
                        end

                        if @LOWPRICE < 0
                        begin
                            raiserror('BBERR_LOWPRICEPERSHARENEGATIVE', 13, 1)
                            return 1
                        end

                        if @MEDIANPRICE < 0
                        begin
                            raiserror('BBERR_MEDIANPRICEPERSHARENEGATIVE', 13, 1)
                            return 1
                        end

                        if @HIGHPRICE < 0
                        begin
                            raiserror('BBERR_HIGHPRICEPERSHARENEGATIVE', 13, 1)
                            return 1
                        end

                        declare @ADJUSTMENTID uniqueidentifier

                        if @CHANGEAGENTID is null
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENTID @CHANGEAGENTID output

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        begin try
                            if @NEEDSADJUSTMENT = 1
                                exec dbo.USP_SAVE_STOCKSALEADJUSTMENT @ID, @ADJUSTMENTID output, @CHANGEAGENTID, @CURRENTDATE, @ADJUSTMENTDATE, @ADJUSTMENTPOSTDATE, @ADJUSTMENTREASON

                            update dbo.STOCKSALE set
                                SALEDATE = @SALEDATE,
                                SALEAMOUNT = @SALEAMOUNT,
                                FEE = @FEE,
                                NUMBEROFUNITS = @NUMBEROFUNITS,
                                LOWPRICE = @LOWPRICE,
                                MEDIANPRICE = @MEDIANPRICE,
                                HIGHPRICE = @HIGHPRICE,
                                DATECHANGED = @CURRENTDATE,
                                CHANGEDBYID = @CHANGEAGENTID
                            where
                                ID = @ID

                            if @NEEDSADJUSTMENT = 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.STOCKSALEGLDISTRIBUTION where STOCKSALEID = @ID and OUTDATED = 0

                                exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CURRENTDATE, @ID

                                --Restore CONTEXT_INFO

                                if not @contextCache is null
                                    set CONTEXT_INFO @contextCache;

                                if @ADJUSTMENTID is not null
                                    exec dbo.USP_ADJUSTMENTHISTORY_STOCKSALE_SAVEHISTORY @ID, @CHANGEAGENTID, null, @ADJUSTMENTID
                            end
                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                        return 0