Banned
Регистрация: 09.07.2002
Адрес: Parndorf, AT
|
Удалял InventTrans там, где InventSum была "Closed". Проблематично в том, что InventSum не сообщает, когда именно данный серийный номер был "закрыт" и сопоставлен. В тех же случаях, когда серийные номера или batches не используются, а есть только склады, ситуация еще сложнее, и удаляли только те InventTrans, где ItemId "устарели". При удалении очень рекомендую отключить индексы.
Вот для вдохновения:
X++: delete InventDim
where exists (select 1 from INVENTSERIAL
where INVENTSERIAL.DATAAREAID = 'XXX' AND INVENTDIM.INVENTSERIALID = INVENTSERIAL.INVENTSERIALID) AND
not exists (select 1 from inventTrans where InventTrans.InventDimId = InventDim.InventDimId AND InventTrans.DataAreaId = 'XXX')
AND INVENTDIM.INVENTSERIALID <> '' AND INVENTDIM.DATAAREAID = 'XXX' X++: insert into INVENTSETTLEMENT
(QTYSETTLED, COSTAMOUNTSETTLED, SETTLETRANSID, COSTAMOUNTADJUSTMENT, RECVERSION, RECID,
TRANSRECID, INVENTTRANSID, ITEMID, TRANSDATE, VOUCHER, BALANCESHEETACCOUNT, OPERATIONSACCOUNT, CANCELLED, SETTLEMODEL,
DIMENSION, DIMENSION2_, DIMENSION3_, DIMENSION4_, DIMENSION5_, DIMENSION6_, DIMENSION7_,
BALANCESHEETPOSTING, OPERATIONSPOSTING, ITEMGROUPID, POSTED, SETTLETYPE, DATAAREAID)
select
SUM(is1.QtySettled), SUM(is1.CostAmountSettled), is1.INVENTTRANSID, SUM(is1.COSTAMOUNTADJUSTMENT), 777, max(is1.RecId),
is1.TRANSRECID, is1.INVENTTRANSID, is1.ItemId, is1.TransDate, is1.VOUCHER,
is1.BalanceSheetAccount, is1.OPERATIONSACCOUNT, is1.Cancelled, is1.SettleModel,
is1.Dimension, is1.Dimension2_, is1.Dimension3_, is1.Dimension4_, is1.Dimension5_, is1.Dimension6_, is1.Dimension7_,
is1.BalanceSheetPosting, is1.OperationsPosting, is1.ItemGroupId, is1.Posted, 4, is1.DataAreaId
from iSettlement as is1
where exists (select 1 from INVENTTRANS
where INVENTTRANS.DATAAREAID = is1.DATAAREAID AND INVENTTRANS.RECID = is1.TRANSRECID AND INVENTTRANS.VALUEOPEN = 0)
group by
is1.TRANSRECID, is1.INVENTTRANSID, is1.ItemId, is1.TransDate, is1.VOUCHER,
is1.BalanceSheetAccount, is1.OPERATIONSACCOUNT, is1.Cancelled, is1.SettleModel,
is1.Dimension, is1.Dimension2_, is1.Dimension3_, is1.Dimension4_, is1.Dimension5_, is1.Dimension6_, is1.Dimension7_,
is1.BalanceSheetPosting, is1.OperationsPosting, is1.ItemGroupId, is1.Posted, is1.SettleType, is1.DataAreaId
GO
delete iSettlement
where exists
(select 1 from INVENTTRANS
where INVENTTRANS.DATAAREAID = iSettlement.DATAAREAID AND INVENTTRANS.RECID = iSettlement.TRANSRECID AND INVENTTRANS.VALUEOPEN = 0)
AND iSettlement.RecVersion <> 777 X++: IF @TurnOffIndexes = 1
BEGIN
ALTER INDEX I_155VoucherIdx ON INVENTJOURNALTRANS DISABLE
ALTER INDEX I_155ToTransIdIdx ON INVENTJOURNALTRANS DISABLE
ALTER INDEX I_155TransIDIdx ON INVENTJOURNALTRANS DISABLE
ALTER INDEX I_155ExciseTariffCodes_INIdx ON INVENTJOURNALTRANS DISABLE
ALTER INDEX I_155RecID ON INVENTJOURNALTRANS DISABLE
ALTER INDEX I_174ClosedItemDimIdx ON INVENTSUM DISABLE
ALTER INDEX I_174DimIdIdx ON INVENTSUM DISABLE
ALTER INDEX I_174ClosedQtyIdx ON INVENTSUM DISABLE
ALTER INDEX I_174ReservationIdx ON INVENTSUM DISABLE
ALTER INDEX I_174ClosedAvailPhysIdx ON INVENTSUM DISABLE
ALTER INDEX I_174ItemCloseAvailIdx ON INVENTSUM DISABLE
ALTER INDEX I_174RecID ON INVENTSUM DISABLE
ALTER INDEX I_698PalletIdIdx ON INVENTDIM DISABLE
ALTER INDEX I_698LocationIdIdx ON INVENTDIM DISABLE
ALTER INDEX I_698BatchIdIdx ON INVENTDIM DISABLE
ALTER INDEX I_698DimIdx ON INVENTDIM DISABLE
ALTER INDEX I_698SiteIdx ON INVENTDIM DISABLE
ALTER INDEX I_698InventProfileIdx_RU ON INVENTDIM DISABLE
ALTER INDEX I_698InventOwnerIdx_RU ON INVENTDIM DISABLE
ALTER INDEX I_698GTDIdIdx_RU ON INVENTDIM DISABLE
ALTER INDEX I_177StatusItemIdx ON INVENTTRANS DISABLE
ALTER INDEX I_177DimIdIdx ON INVENTTRANS DISABLE
ALTER INDEX I_177OpenItemIdx ON INVENTTRANS DISABLE
ALTER INDEX I_177GoodsInRouteSalesIdx_RU ON INVENTTRANS DISABLE
ALTER INDEX I_177GroupRefIdx_RU ON INVENTTRANS DISABLE
ALTER INDEX I_177OpenSecCurItemIdx_RU ON INVENTTRANS DISABLE
ALTER INDEX I_177GoodsInRouteTransitIdx_RU ON INVENTTRANS DISABLE
ALTER INDEX I_177GoodsInRouteDeliveryIdx_RU ON INVENTTRANS DISABLE
ALTER INDEX I_177RecId ON INVENTTRANS DISABLE
ALTER INDEX I_2938InventTransIdIdx ON INVENTTRANSORIGIN DISABLE
ALTER INDEX I_2938ItemIdx ON INVENTTRANSORIGIN DISABLE
--ALTER INDEX I_553ItemTypeDateIdx ON INVENTTRANSPOSTING DISABLE
ALTER INDEX I_553InventTransOriginIdx ON INVENTTRANSPOSTING DISABLE
ALTER INDEX I_173DateVoucherIdx ON INVENTSETTLEMENT DISABLE
ALTER INDEX I_173ItemDateIdx ON INVENTSETTLEMENT DISABLE
ALTER INDEX I_173ItemVoucherDateIdx ON INVENTSETTLEMENT DISABLE
ALTER INDEX I_752BatchIdx ON INVENTBATCH DISABLE
ALTER INDEX I_752ItemVendBatchIdx ON INVENTBATCH DISABLE
ALTER INDEX I_1204SerialIdx ON INVENTSERIAL DISABLE
ALTER INDEX I_1204RFIDTagIdx ON INVENTSERIAL DISABLE
END X++: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventSum, InventDim ' + @ItemId
delete INVENTSUM output deleted.INVENTDIMID into @DeletedIds where INVENTSUM.ITEMID = @ItemId and INVENTSUM.DATAAREAID = @DataAreaID
delete INVENTDIM from INVENTDIM join @DeletedIds IDs on INVENTDIM.INVENTDIMID = IDs.ID
where INVENTDIM.INVENTSERIALID <> '' and INVENTDIM.DATAAREAID = @DataAreaID --and INVENTDIM.INVENTBATCHID <> ''
delete @DeletedIds
PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventTrans ' + @ItemId
delete INVENTTRANS
output deleted.RECID into @DeletedRecIds
where INVENTTRANS.ITEMID = @ItemId and INVENTTRANS.DATAAREAID = @DataAreaID
PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventTransOrigin ' + @ItemId
delete INVENTTRANSORIGIN
output deleted.RECID into @DeletedOriginIds
--join @DeletedRecIds IDs on INVENTTRANSORIGIN.RECID = IDs.OriginRecId
where INVENTTRANSORIGIN.DATAAREAID = @DataAreaID and INVENTTRANSORIGIN.ITEMID = @ItemId
delete INVENTTRANSORIGINPURCHLINE from INVENTTRANSORIGINPURCHLINE join @DeletedOriginIds IDs on INVENTTRANSORIGINPURCHLINE.INVENTTRANSORIGIN = IDs.OriginRecId
delete INVENTTRANSORIGINSALESLINE from INVENTTRANSORIGINSALESLINE join @DeletedOriginIds IDs on INVENTTRANSORIGINSALESLINE.INVENTTRANSORIGIN = IDs.OriginRecId
delete INVENTTRANSORIGINJOURNALTRANS from INVENTTRANSORIGINJOURNALTRANS join @DeletedOriginIds IDs on INVENTTRANSORIGINJOURNALTRANS.INVENTTRANSORIGIN = IDs.OriginRecId
delete INVENTTRANSORIGINJOURNALTRANSRECEIPT from INVENTTRANSORIGINJOURNALTRANSRECEIPT join @DeletedOriginIds IDs on INVENTTRANSORIGINJOURNALTRANSRECEIPT.INVENTTRANSORIGIN = IDs.OriginRecId
delete INVENTTRANSORIGINTRANSFER from INVENTTRANSORIGINTRANSFER join @DeletedOriginIds IDs on INVENTTRANSORIGINTRANSFER.ISSUEINVENTTRANSORIGIN = IDs.OriginRecId
delete INVENTTRANSORIGINTRANSFER from INVENTTRANSORIGINTRANSFER join @DeletedOriginIds IDs on INVENTTRANSORIGINTRANSFER.RECEIPTINVENTTRANSORIGIN = IDs.OriginRecId
-- the most expensive query
PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventTransPosting ' + @ItemId
delete INVENTTRANSPOSTING from INVENTTRANSPOSTING
--join @DeletedOriginIds IDs on INVENTTRANSPOSTING.INVENTTRANSORIGIN = IDs.OriginRecId
where INVENTTRANSPOSTING.DATAAREAID = @DataAreaID and INVENTTRANSPOSTING.ITEMID = @ItemId
PRINT convert(nvarchar, CURRENT_TIMESTAMP) + ' Deleting InventTransSettlement ' + @ItemId
-- the itemID alone is just too slow
delete INVENTSETTLEMENT from INVENTSETTLEMENT
join @DeletedRecIds IDs on INVENTSETTLEMENT.TRANSRECID = IDs.RecID
where INVENTSETTLEMENT.DATAAREAID = @DataAreaID and INVENTSETTLEMENT.ITEMID = @ItemId
|