//这个是脚本
CREATE PROCEDURE [dbo].[uspInsertSynchronizedData]
(
@sXMLDoc TEXT= NULL
)
AS
declare @id int,
@wNo varchar(10),
@pCode varchar(10),
@sLoc varchar(10),
@sSku varchar(10),
@junk varchar(50),
@others varchar(50),
@OnHandQty int,
@UnitFactor int,
@PickLocation varchar(10),
@filter varchar(19),
@ImportCertNumber varchar(50),
@CountryOfOrigin varchar(3),
@CommittedQty int
declare @StoreId int
declare @ProductId int
declare @InventoryId int
declare @today datetime
declare @PRODUCT_ERROR varchar(100)
declare @INVENTORYID_ERROR varchar(100)
declare @user varchar(20)
DECLARE @nDoc INT
SET NOCOUNT ON
set @PRODUCT_ERROR=' have not the corresponding ProductID in the local Store Master.'
set @INVENTORYID_ERROR=' the Inventory id has been existed please check it but it has been updated.'
set @user='InventoryService'
CREATE TABLE [#SyncErrorLog] (
[id] [int] NULL ,
[wNo] [varchar] (10) NULL ,
[pCode] [varchar] (10) NULL ,
[sLoc] [varchar] (10) NULL ,
[sSku] [varchar] (10) NULL ,
[OnHandQty] [int] NULL ,
[UnitFactor] [int] NULL ,
[PickLocation] [varchar] (10) NULL ,
[filter] [varchar] (19) NULL,
[ImportCertNumber] [varchar] (50) NULL,
[CountryOfOrigin] [varchar](3) NULL,
[ErrorDesc] [varchar](100) NULL
) ON [PRIMARY]
CREATE TABLE [#SyncData] (
[wNo] [varchar] (10) NULL ,
[pCode] [varchar] (10) NULL ,
[sLoc] [varchar] (10) NULL ,
[sSku] [varchar] (10) NULL ,
[OnHandQty] [int] NULL ,
[UnitFactor] [int] NULL ,
[PickLocation] [varchar] (10) NULL ,
[filter] [varchar] (19) NULL,
[ImportCertNumber] [varchar] (50) NULL,
[CountryOfOrigin] [varchar](3) NULL,
[id] [int] IDENTITY (1, 1) NOT NULL
) ON [PRIMARY]
EXEC sp_xml_preparedocument @nDoc OUTPUT, @sXMLDoc
insert into #SyncData(wNo,pCode,sLoc,sSku,OnHandQty,UnitFactor,PickLocation,filter,ImportCertNumber,CountryOfOrigin)
select *
from OPENXML (@nDoc,'/InventoryItems/InventoryItem',1)
with (
WarehouseNo varchar(10) ,
ProductCode varchar(10) ,
SubIntentoryLocation varchar(10) ,
StockSKU varchar(10) ,
OnHandQty int ,
UnitFactor int ,
PickLocation varchar(10) ,
filter varchar(19) ,
ImportCertNumber varchar(50) ,
CountryOfOrigin varchar(3)
) a
set @today=dbo.ufGetGMTDate(getdate())
--begin tran
declare sync_cur cursor for
select * from #SyncData
for read only
open sync_cur
fetch sync_cur into @wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@id
while @@fetch_status=0
begin
-- To see whether the StoreID in Store Master
if exists(select * from store where warehouseid=@wNo)
begin
-- get storeid
select @StoreId=StoreId from store where warehouseid=@wNo
-- To See whether the ProductId in Product Master
if exists(select * from product where ProductSKU=@pCode)
begin
-- get productid
select @ProductId=ProductId from product where ProductSKU=@pCode
-- handle product inventory data
if exists(select * from ProductInventory where StockSKU=@sSKU and SubInventoryLocation=@sLoc and IsDeleted=0)
begin
update ProductInventory
set OnHandQty=@OnHandQty ,UpdatedBy=@user,UpdatedOn=@today
where StockSKU=@sSKU and SubInventoryLocation=@sLoc and IsDeleted=0
end
else
begin
insert into ProductInventory(SubInventoryLocation,StockSKU,OnHandQty,CommittedQty,CreatedOn,CreatedBy,IsDeleted)
values(@sLoc,@sSKU,@OnHandQty,0,@today,@user,0)
end
-- get the inventory id new or old one
select @InventoryId=InventoryId
from ProductInventory
where StockSKU=@sSKU and SubInventoryLocation=@sLoc and IsDeleted=0
-- handle product inventory xref data
if not exists(select * from ProductInventoryXref where StoreId=@StoreId and ProductId=@ProductId and IsDeleted=0)
begin
insert into ProductInventoryXref(InventoryId,ProductId,StoreId,CreatedOn,CreatedBy,UnitFactor,IsDeleted)
values(@InventoryID,@ProductId,@StoreId,@today,@user,1,0)
end
-- handle error data
if not exists(select * from ProductInventoryXref where StoreId=@StoreId and ProductId=@ProductId and InventoryId=@InventoryId and IsDeleted=0)
begin
insert into #SyncErrorLog
values(@id,@wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@INVENTORYID_ERROR)
-- new added by Jerry Woo 2005-10-20 for changing sublocation
select @CommittedQty=CommittedQty
from ProductInventory p inner join ProductInventoryXref pix on p.InventoryID=pix.InventoryID
where StoreId=@StoreId and ProductId=@ProductId and pix.IsDeleted=0 and p.IsDeleted=0
update ProductInventoryXref
set InventoryID=@InventoryId
where StoreId=@StoreId and ProductId=@ProductId and IsDeleted=0
update ProductInventory
set CommittedQty=@CommittedQty
where InventoryId=@InventoryId
end
-- when unitfactor not null update it
if (@UnitFactor is not null)
begin
update ProductInventoryXref
set UnitFactor=@UnitFactor,UpdatedBy=@user,UpdatedOn=@today
where StoreId=@StoreId and ProductId=@ProductId and InventoryId=@InventoryId and IsDeleted=0
end
-- when PickLocation not null update it
if (@PickLocation is not null)
begin
update ProductInventoryXref
set PickLocation=@PickLocation,UpdatedBy=@user,UpdatedOn=@today
where StoreId=@StoreId and ProductId=@ProductId and InventoryId=@InventoryId and IsDeleted=0
end
end
else
begin
-- log error data
insert into #SyncErrorLog
values(@id,@wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@PRODUCT_ERROR)
end
end
fetch sync_cur into @wNo,@pCode,@sLoc,@sSku,@OnHandQty,@UnitFactor,@PickLocation,@filter,@ImportCertNumber,@CountryOfOrigin,@id
end
--commit tran
close sync_cur
deallocate sync_cur
select * from #SyncErrorLog
--select * from #syncData
EXEC sp_xml_removedocument @nDoc
drop table #SyncData
drop table #SyncErrorLog