新品建档语法2.txt

用友系统二次开发 存货管理 客户管理 订货审核 退货审核 铺货计划 调货计划

//删除零售服务器中用友系统不存在的资料
DELETE FROM OPENDATASOURCE ('SQLOLEDB', 'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.item
WHERE fchrItemCode IN (SELECT LS_Item.fchrItemCode AS Expr2
FROM OPENDATASOURCE ('SQLOLEDB', 'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.item
       LS_Item FULL OUTER JOIN
      Inventory ON 
      LS_Item.fchrItemCode COLLATE Chinese_PRC_CI_AS = Inventory.cInvCode
WHERE (Inventory.cInvCode IS NULL))


//建档零售服务器中不存在的用友系统货品资料
SELECT Inventory.cInvName, Inventory.cInvAddCode, LS_ItemType.fchrItemTypeID, 
      LS_ItemType.fchrAccountID, Inventory.cBarCode, Inventory.bFree1, Inventory.bFree2, 
      Inventory.dLastDate, Inventory.cInvCode, LS_Unit.fchrUnitID, LS_Unit.fchrUnitName, 
      Inventory.fRetailPrice, Inventory.iInvSPrice, LS_ItemType.fchrItemType, 
      Inventory.cInvStd
FROM OPENDATASOURCE ('SQLOLEDB', 'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.item
       LS_Item RIGHT OUTER JOIN
      OPENDATASOURCE ('SQLOLEDB','SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.Unit
       LS_Unit INNER JOIN
      OPENDATASOURCE ('SQLOLEDB', 'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.itemtype
       LS_ItemType INNER JOIN
      Inventory ON 
      LS_ItemType.fchrItemType COLLATE Chinese_PRC_CI_AS = Inventory.cInvCCode ON 
      LS_Unit.fchrUnitCode COLLATE Chinese_PRC_CI_AS = Inventory.cShopUnit ON 
      LS_Item.fchrItemCode COLLATE Chinese_PRC_CI_AS = Inventory.cInvCode
WHERE (LS_Item.fchrItemCode IS NULL)