USP_ADJUSTMENTHISTORY_STOCKSALE_SAVEHISTORY
Store historical sold stock adjustment information for reporting purposes.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @STOCKSALEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @STOCKSALEADJUSTMENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_ADJUSTMENTHISTORY_STOCKSALE_SAVEHISTORY
(
@STOCKSALEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CHANGEDATE datetime,
@STOCKSALEADJUSTMENTID uniqueidentifier
)
as
set nocount on;
/*Before calling this procedure, be sure that you call USP_SAVE_STOCKSALEADJUSTMENT and then save changes to the STOCKSALE table*/
declare @ADJUSTMENTCOUNT int;
declare @PREVIOUSSTOCKSALEADJUSTMENTID uniqueidentifier;
declare @PREVIOUSADJUSTMENTHISTORYSTOCKID uniqueidentifier;
declare @ADJUSTMENTHISTORYSTOCKID uniqueidentifier;
declare @SALEAMOUNTCHANGED bit;
declare @BROKERFEECHANGED bit;
declare @NUMBEROFUNITSCHANGED bit;
declare @CONSTITUENTCHANGED bit;
declare @PAYMENTNUMBEROFUNITSCHANGED bit;
declare @PAYMENTMEDIANPRICECHANGED bit;
declare @BASERATECHANGED bit;
declare @ORGANIZATIONRATECHANGED bit;
declare @EDITINGADJUSTMENT bit;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
select @ADJUSTMENTCOUNT = count(ID)
from dbo.STOCKSALEADJUSTMENT
where STOCKSALEADJUSTMENT.STOCKSALEID = @STOCKSALEID;
select @ADJUSTMENTHISTORYSTOCKID = ID
from dbo.ADJUSTMENTHISTORYSTOCK
where STOCKSALEADJUSTMENTID = @STOCKSALEADJUSTMENTID
/*if this is a new adjustment, there will only be a header row (no details or distributions). Otherwise, it's an edit*/
if (select count(ID) from dbo.ADJUSTMENTHISTORYSTOCKDETAIL where ADJUSTMENTHISTORYSTOCKID = @ADJUSTMENTHISTORYSTOCKID) = 0
and (select count(ID) from dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTION where ADJUSTMENTHISTORYSTOCKID = @ADJUSTMENTHISTORYSTOCKID) = 0
set @EDITINGADJUSTMENT = 0;
else
set @EDITINGADJUSTMENT = 1;
/*delete the previous report information so we can recreate it*/
delete from ADJUSTMENTHISTORYSTOCKDETAIL where ADJUSTMENTHISTORYSTOCKID = @ADJUSTMENTHISTORYSTOCKID;
/*determine what changed by comparing the snapshot to the live data*/
if (select SALEAMOUNT from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT where ID = @ADJUSTMENTHISTORYSTOCKID)
<> (select SALEAMOUNT from dbo.STOCKSALE where ID = @STOCKSALEID)
set @SALEAMOUNTCHANGED = 1;
else
set @SALEAMOUNTCHANGED = 0;
if (select BROKERFEE from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT where ID = @ADJUSTMENTHISTORYSTOCKID)
<> (select FEE from dbo.STOCKSALE where ID = @STOCKSALEID)
set @BROKERFEECHANGED = 1;
else
set @BROKERFEECHANGED = 0;
if (select NUMBEROFUNITS from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT where ID = @ADJUSTMENTHISTORYSTOCKID)
<> (select NUMBEROFUNITS from dbo.STOCKSALE where ID = @STOCKSALEID)
set @NUMBEROFUNITSCHANGED = 1;
else
set @NUMBEROFUNITSCHANGED = 0;
if (select CONSTITUENTIDENTIFIER from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT where ID = @ADJUSTMENTHISTORYSTOCKID)
<> ( select REVENUE.CONSTITUENTID
from dbo.REVENUE
inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.STOCKSALE on REVENUEPAYMENTMETHOD.ID = STOCKSALE.STOCKDETAILID
where STOCKSALE.ID = @STOCKSALEID)
set @CONSTITUENTCHANGED = 1;
else
set @CONSTITUENTCHANGED = 0;
if (select PAYMENTNUMBEROFUNITS from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT where ID = @ADJUSTMENTHISTORYSTOCKID)
<> ( select STOCKDETAIL.NUMBEROFUNITS
from dbo.STOCKDETAIL
inner join dbo.STOCKSALE on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
where STOCKSALE.ID = @STOCKSALEID)
set @PAYMENTNUMBEROFUNITSCHANGED = 1;
else
set @PAYMENTNUMBEROFUNITSCHANGED = 0;
if (select PAYMENTMEDIANPRICE from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT where ID = @ADJUSTMENTHISTORYSTOCKID)
<> ( select STOCKDETAIL.MEDIANPRICE
from dbo.STOCKDETAIL
inner join dbo.STOCKSALE on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
where STOCKSALE.ID = @STOCKSALEID)
set @PAYMENTMEDIANPRICECHANGED = 1;
else
set @PAYMENTMEDIANPRICECHANGED = 0;
if (select BASERATE from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT where ID = @ADJUSTMENTHISTORYSTOCKID)
<> (select CURRENCYEXCHANGERATE.RATE from dbo.STOCKSALE left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = STOCKSALE.BASEEXCHANGERATEID where STOCKSALE.ID = @STOCKSALEID)
set @BASERATECHANGED = 1;
else
set @BASERATECHANGED = 0;
if (select ORGANIZATIONRATE from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT where ID = @ADJUSTMENTHISTORYSTOCKID)
<> (select CURRENCYEXCHANGERATE.RATE from dbo.STOCKSALE left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = STOCKSALE.ORGANIZATIONEXCHANGERATEID where STOCKSALE.ID = @STOCKSALEID)
set @ORGANIZATIONRATECHANGED = 1;
else
set @ORGANIZATIONRATECHANGED = 0;
/*Log the detail for this adjustment*/
if @SALEAMOUNTCHANGED = 1
insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISMONEY, ADJUSTEDFIELDPREVIOUSMONEYVALUE, ADJUSTEDFIELDADJUSTEDMONEYVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYSTOCKID,
'Sale amount',
[SNAP].SALEAMOUNT,
[REAL].SALEAMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
1,
[SNAP].SALEAMOUNT,
[REAL].SALEAMOUNT,
[SNAP].TRANSACTIONSALEAMOUNT,
[REAL].TRANSACTIONSALEAMOUNT,
[SNAP].TRANSACTIONSALEAMOUNT,
[REAL].TRANSACTIONSALEAMOUNT,
[SNAP].ORGANIZATIONSALEAMOUNT,
[REAL].ORGANIZATIONSALEAMOUNT,
[SNAP].ORGANIZATIONSALEAMOUNT,
[REAL].ORGANIZATIONSALEAMOUNT,
[REAL].BASECURRENCYID,
[REAL].TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT as [SNAP]
inner join dbo.STOCKSALE as [REAL] on [REAL].ID = @STOCKSALEID
where [SNAP].ID = @ADJUSTMENTHISTORYSTOCKID
if @BROKERFEECHANGED = 1
insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISMONEY, ADJUSTEDFIELDPREVIOUSMONEYVALUE, ADJUSTEDFIELDADJUSTEDMONEYVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYSTOCKID,
'Fees',
[SNAP].BROKERFEE,
[REAL].FEE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
1,
[SNAP].BROKERFEE,
[REAL].FEE,
[SNAP].TRANSACTIONBROKERFEE,
[REAL].TRANSACTIONFEE,
[SNAP].TRANSACTIONBROKERFEE,
[REAL].TRANSACTIONFEE,
[SNAP].ORGANIZATIONBROKERFEE,
[REAL].ORGANIZATIONFEE,
[SNAP].ORGANIZATIONBROKERFEE,
[REAL].ORGANIZATIONFEE,
[REAL].BASECURRENCYID,
[REAL].TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT as [SNAP]
inner join dbo.STOCKSALE as [REAL] on [REAL].ID = @STOCKSALEID
where [SNAP].ID = @ADJUSTMENTHISTORYSTOCKID
if @NUMBEROFUNITSCHANGED = 1
insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYSTOCKID,
'Units sold',
[SNAP].NUMBEROFUNITS,
[REAL].NUMBEROFUNITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
[SNAP].NUMBEROFUNITS,
[REAL].NUMBEROFUNITS,
[SNAP].NUMBEROFUNITS,
[REAL].NUMBEROFUNITS
from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT as [SNAP]
inner join dbo.STOCKSALE as [REAL] on [REAL].ID = @STOCKSALEID
where [SNAP].ID = @ADJUSTMENTHISTORYSTOCKID
if @CONSTITUENTCHANGED = 1
insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYSTOCKID,
'Constituent',
dbo.UFN_CONSTITUENT_BUILDNAME([SNAP].CONSTITUENTIDENTIFIER),
dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
dbo.UFN_CONSTITUENT_BUILDNAME([SNAP].CONSTITUENTIDENTIFIER),
dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID),
dbo.UFN_CONSTITUENT_BUILDNAME([SNAP].CONSTITUENTIDENTIFIER),
dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID)
from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT as [SNAP]
inner join dbo.STOCKSALE as [REAL] on [REAL].ID = @STOCKSALEID
inner join dbo.REVENUEPAYMENTMETHOD on [REAL].ID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
where [SNAP].ID = @ADJUSTMENTHISTORYSTOCKID
if @PAYMENTNUMBEROFUNITSCHANGED = 1
insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYSTOCKID,
'Payment''s number of units',
[SNAP].PAYMENTNUMBEROFUNITS,
[REAL].NUMBEROFUNITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
[SNAP].PAYMENTNUMBEROFUNITS,
[REAL].NUMBEROFUNITS,
[SNAP].PAYMENTNUMBEROFUNITS,
[REAL].NUMBEROFUNITS
from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT as [SNAP]
inner join dbo.STOCKSALE on STOCKSALE.ID = @STOCKSALEID
inner join dbo.STOCKDETAIL as [REAL] on STOCKSALE.STOCKDETAILID = [REAL].ID
where [SNAP].ID = @ADJUSTMENTHISTORYSTOCKID
if @PAYMENTMEDIANPRICECHANGED = 1
insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISMONEY, ADJUSTEDFIELDPREVIOUSMONEYVALUE, ADJUSTEDFIELDADJUSTEDMONEYVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYSTOCKID,
'Payment''s median price',
[SNAP].PAYMENTMEDIANPRICE,
[REAL].MEDIANPRICE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
1,
[SNAP].PAYMENTMEDIANPRICE,
[REAL].MEDIANPRICE,
[SNAP].TRANSACTIONPAYMENTMEDIANPRICE,
[REAL].TRANSACTIONMEDIANPRICE,
[SNAP].TRANSACTIONPAYMENTMEDIANPRICE,
[REAL].TRANSACTIONMEDIANPRICE,
[SNAP].ORGANIZATIONPAYMENTMEDIANPRICE,
[REAL].ORGANIZATIONMEDIANPRICE,
[SNAP].ORGANIZATIONPAYMENTMEDIANPRICE,
[REAL].ORGANIZATIONMEDIANPRICE,
[REAL].BASECURRENCYID,
[REAL].TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT as [SNAP]
inner join dbo.STOCKSALE on STOCKSALE.ID = @STOCKSALEID
inner join dbo.STOCKDETAIL as [REAL] on STOCKSALE.STOCKDETAILID = [REAL].ID
where [SNAP].ID = @ADJUSTMENTHISTORYSTOCKID
/*Record the GL reversal and adjustment information*/
if @EDITINGADJUSTMENT = 0 /*if this is a new adjustment, store the reversal that will take place*/
begin
insert into dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTION(ADJUSTMENTHISTORYSTOCKID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYSTOCKID,
0, /*Reversal*/
TRANSACTIONTYPECODE,
ACCOUNT,
PROJECT,
REFERENCE,
AMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT,
BASECURRENCYID,
TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTIONSNAPSHOT
where ADJUSTMENTHISTORYSTOCKID = @ADJUSTMENTHISTORYSTOCKID;
end
if @BASERATECHANGED = 1
begin
declare @OLDBASERATE decimal(20,8);
select @OLDBASERATE = CURRENCYEXCHANGERATE.RATE
from dbo.STOCKSALE
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = STOCKSALE.BASEEXCHANGERATEID
where STOCKSALE.ID = @STOCKSALEID
insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYSTOCKID,
'Base exchange rate',
ADJUSTMENTHISTORYSTOCKSNAPSHOT.BASERATE,
@OLDBASERATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
ADJUSTMENTHISTORYSTOCKSNAPSHOT.BASERATE,
@OLDBASERATE,
ADJUSTMENTHISTORYSTOCKSNAPSHOT.BASERATE,
@OLDBASERATE
from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT
where ID = @ADJUSTMENTHISTORYSTOCKID;
end
if @ORGANIZATIONRATECHANGED = 1
begin
declare @OLDORGANIZATIONRATE decimal(20,8);
select @OLDORGANIZATIONRATE = CURRENCYEXCHANGERATE.RATE
from dbo.STOCKSALE
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = STOCKSALE.ORGANIZATIONEXCHANGERATEID
where STOCKSALE.ID = @STOCKSALEID
insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYSTOCKID,
'Organization exchange rate',
ADJUSTMENTHISTORYSTOCKSNAPSHOT.ORGANIZATIONRATE,
@OLDORGANIZATIONRATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
ADJUSTMENTHISTORYSTOCKSNAPSHOT.ORGANIZATIONRATE,
@OLDORGANIZATIONRATE,
ADJUSTMENTHISTORYSTOCKSNAPSHOT.ORGANIZATIONRATE,
@OLDORGANIZATIONRATE
from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT
where ID = @ADJUSTMENTHISTORYSTOCKID;
end
if @ADJUSTMENTCOUNT > 1 /*if this is not the first adjustment, we need to copy our reversal information to be the adjustment information for the last adjustment */
begin
select top 1
@PREVIOUSSTOCKSALEADJUSTMENTID = ID
from dbo.STOCKSALEADJUSTMENT
where
STOCKSALEID = @STOCKSALEID and
ID <> @STOCKSALEADJUSTMENTID
order by TSLONG desc;
/*If there is a previous adjustment, write the same reversal as the "adjusted" portion of the previous adjustment */
if not @PREVIOUSSTOCKSALEADJUSTMENTID is null
begin
select
@PREVIOUSADJUSTMENTHISTORYSTOCKID = ID
from dbo.ADJUSTMENTHISTORYSTOCK
where STOCKSALEADJUSTMENTID = @PREVIOUSSTOCKSALEADJUSTMENTID;
if @PREVIOUSADJUSTMENTHISTORYSTOCKID is not null
begin
/*first, delete the previous adjustment record*/
delete from dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTION where ADJUSTMENTHISTORYSTOCKID = @PREVIOUSADJUSTMENTHISTORYSTOCKID and TYPECODE = 1;
/*then insert the updated ones*/
insert into dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTION(ADJUSTMENTHISTORYSTOCKID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@PREVIOUSADJUSTMENTHISTORYSTOCKID,
1, /*Adjustment*/
TRANSACTIONTYPECODE,
ACCOUNT,
PROJECT,
REFERENCE,
AMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT,
BASECURRENCYID,
TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTIONSNAPSHOT
where ADJUSTMENTHISTORYSTOCKID = @ADJUSTMENTHISTORYSTOCKID;
end
end
end