新品建档语法.txt

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

	(@cInvCode 	[nvarchar](20),
	 @cInvName 	[nvarchar](60),
	 @cInvStd 	[nvarchar](60),
	 @cInvCCode 	[nvarchar](12),
	 @cVenCode 	[nvarchar](20),
   	 @cLabel	[nvarchar](20),
	 @cUnitCode	[nvarchar](35),
	 @Person 	[nvarchar](20),
	 @DefWH 	[nvarchar](20),
	 @cSouName 	[nvarchar](20),
	 @cSize 	[nvarchar](500),
	 @cColor 	[nvarchar](500),
	 @iPlanCust 	[Integer],
	 @iInvSPrice 	[float],
	 @iInvSCost 	[float],
	 @iInvLSCost 	[float],
	 @fRetailPrice	[float],
	 @dSDate 	[datetime],
	 @cInvType1 	[nvarchar](20),
	 @cInvType2 	[nvarchar](20),
	 @cInvType3 	[nvarchar](20),
	 @cInvType4 	[nvarchar](60),
	 @cInvType5	[nvarchar](60),
	 @cInvType6 	[nvarchar](60))


--商品条码的建录
INSERT INTO ColorSize (cInvCode, sColor, sSize)
SELECT  Inventory.cInvCode AS cInvCode, UserDefine.cValue AS sColor, UserDefine_1.cValue AS sSize
FROM UserDefine UserDefine_1 CROSS JOIN UserDefine CROSS JOIN Inventory
WHERE (Inventory.cInvCode = @cInvCode) 
AND (UserDefine_1.cValue IN (@cSize))
AND (UserDefine.cValue IN (@cColor)) 
ORDER BY sColor


--分配商品的条码规则
INSERT INTO RuleAssign (assignId, assignType, invId, ruleId) 
SELECT Inventory.I_id, 1 AS assignType, Inventory.cInvCode,
CASE WHEN Inventory.cInvCCode Like '20%' THEN '11' ELSE (CASE WHEN Inventory.cInvCCode = '1086' THEN '13' ELSE '12' END)END AS cruleId 
FROM RuleAssign RIGHT OUTER JOIN Inventory ON RuleAssign.invId = Inventory.cInvCode 
WHERE (RuleAssign.invId IS NULL) 
ORDER BY Inventory.cInvCode


--修正非合裤类批发商品的条码
UPDATE Inventory SET cBarCode = 
CASE WHEN LEN(cBarCode) > 10 THEN LEFT(cInvCode, 10)       ELSE(
CASE WHEN LEN(cBarCode) = 9 THEN cInvCode + '0'            ELSE(
CASE WHEN LEN(cBarCode) = 8 THEN cInvCode + '00'           ELSE( 
CASE WHEN LEN(cBarCode) = 7 THEN cInvCode + '000'          ELSE( 
CASE WHEN LEN(cBarCode) = 6 THEN cInvCode + '0000'         ELSE( 
CASE WHEN LEN(cBarCode) = 5 THEN cInvCode + '00000'        ELSE( 
CASE WHEN LEN(cBarCode) = 4 THEN cInvCode + '000000'       ELSE( 
CASE WHEN LEN(cBarCode) = 3 THEN cInvCode + '0000000'      ELSE( 
CASE WHEN LEN(cBarCode) = 2 THEN cInvCode + '00000000'     ELSE 
cInvCode END)END)END)END)END)END)END)END)END 
WHERE (cInvCCode LIKE '10%') AND (LEN(cBarCode) <> 10) AND (Inventory.cInvCCode <>'1086')

--修正连锁类商品的条码
UPDATE Inventory SET cBarCode = 
CASE WHEN LEN(cBarCode) > 8 THEN LEFT(cInvCode, 10)       ELSE(
CASE WHEN LEN(cBarCode) = 7 THEN cInvCode + '0'            ELSE(
CASE WHEN LEN(cBarCode) = 6 THEN cInvCode + '00'           ELSE( 
CASE WHEN LEN(cBarCode) = 5 THEN cInvCode + '000'          ELSE( 
CASE WHEN LEN(cBarCode) = 4 THEN cInvCode + '0000'         ELSE( 
CASE WHEN LEN(cBarCode) = 3 THEN cInvCode + '00000'        ELSE( 
CASE WHEN LEN(cBarCode) = 2 THEN cInvCode + '000000'       ELSE
cInvCode END)END)END)END)END)END)END 
WHERE (cInvCCode LIKE '20%')

--修正合裤类商品的条码
UPDATE Inventory SET cBarCode = 
CASE WHEN LEN(cBarCode) > 13 THEN LEFT(cInvCode, 10)       ELSE(
CASE WHEN LEN(cBarCode) = 12 THEN cInvCode + '0'            ELSE(
CASE WHEN LEN(cBarCode) = 11 THEN cInvCode + '00'           ELSE( 
CASE WHEN LEN(cBarCode) = 10 THEN cInvCode + '000'          ELSE( 
CASE WHEN LEN(cBarCode) =   9 THEN cInvCode + '0000'         ELSE( 
CASE WHEN LEN(cBarCode) =   8 THEN cInvCode + '00000'        ELSE( 
CASE WHEN LEN(cBarCode) =   7 THEN cInvCode + '000000'       ELSE( 
CASE WHEN LEN(cBarCode) =   6 THEN cInvCode + '0000000'      ELSE( 
CASE WHEN LEN(cBarCode) =   5 THEN cInvCode + '00000000'     ELSE 
cInvCode END)END)END)END)END)END)END)END)END 
WHERE (LEN(cBarCode) <> 13) AND (Inventory.cInvCCode ='1086')

--非合裤类商品的条码建档
INSERT INTO InventoryBarCodeSet 
(cInvCode, cBarCode, cRuleId, cAssignId, cInvName, cInvCCode, cInvCName, cFree1, cFree2) 
SELECT DISTINCT Inventory.cInvCode,  
Inventory.cBarCode + UserDefine_1.cBarCode + UserDefine.cBarCode AS nBarCode, 
RuleAssign.ruleId, RuleAssign.assignId, Inventory.cInvName, Inventory.cInvCCode,  
InventoryClass.cInvCName, ColorSize.sColor, ColorSize.sSize 
FROM Inventory INNER JOIN 
ColorSize ON Inventory.cInvCode = ColorSize.cInvCode INNER JOIN
UserDefine ON ColorSize.sColor = UserDefine.cValue INNER JOIN 
UserDefine UserDefine_1 ON ColorSize.sSize = UserDefine_1.cValue INNER JOIN 
RuleAssign ON Inventory.cInvCode = RuleAssign.invId INNER JOIN 
InventoryClass ON  
Inventory.cInvCCode = InventoryClass.cInvCCode LEFT OUTER JOIN
InventoryBarCodeSet ON Inventory.cInvCode = InventoryBarCodeSet.cInvCode AND  
ColorSize.sColor = InventoryBarCodeSet.cFree1 AND  
ColorSize.sSize = InventoryBarCodeSet.cFree2 
WHERE (NOT (Inventory.cBarCode + UserDefine.cBarCode + UserDefine_1.cBarCode IS NULL)) 
AND (Inventory.cInvCCode <>'1086')

--合裤类商品的条码建档
INSERT INTO InventoryBarCodeSet 
(cInvCode, cBarCode, cRuleId, cAssignId, cInvName, cInvCCode, cInvCName, cFree1, cFree2) 
SELECT DISTINCT Inventory.cInvCode,  
Inventory.cBarCode AS nBarCode, 
RuleAssign.ruleId, RuleAssign.assignId, Inventory.cInvName, Inventory.cInvCCode,  
InventoryClass.cInvCName, ColorSize.sColor, ColorSize.sSize 
FROM Inventory INNER JOIN 
ColorSize ON Inventory.cInvCode = ColorSize.cInvCode INNER JOIN
UserDefine ON ColorSize.sColor = UserDefine.cValue INNER JOIN 
UserDefine UserDefine_1 ON ColorSize.sSize = UserDefine_1.cValue INNER JOIN 
RuleAssign ON Inventory.cInvCode = RuleAssign.invId INNER JOIN 
InventoryClass ON  
Inventory.cInvCCode = InventoryClass.cInvCCode LEFT OUTER JOIN
InventoryBarCodeSet ON Inventory.cInvCode = InventoryBarCodeSet.cInvCode AND  
ColorSize.sColor = InventoryBarCodeSet.cFree1 AND  
ColorSize.sSize = InventoryBarCodeSet.cFree2 
WHERE (NOT (Inventory.cBarCode + UserDefine.cBarCode + UserDefine_1.cBarCode IS NULL)) 
AND (Inventory.cInvCCode ='1086')

----清除零售服务器中用友未定义的颜色尺码组合
DElETE FROM OPENDATASOURCE ('SQLOLEDB','SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.ItemAllotAnalysis
WHERE (fchrID IN (SELECT LS_Detail.fchrID
FROM OPENDATASOURCE ('SQLOLEDB','SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.Item LS_Item INNER JOIN
      OPENDATASOURCE ('SQLOLEDB','SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.ItemAllotAnalysis LS_Detail ON 
	LS_Item.fchrItemID = LS_Detail.fchrItemID FULL OUTER JOIN  _PM_V_InventoryColorSize ON 
      LS_Item.fchrItemCode = _PM_V_InventoryColorSize.cInvCode AND 
      LS_Detail.fchrValueName = _PM_V_InventoryColorSize.cFree
WHERE (_PM_V_InventoryColorSize.cInvCode IS NULL)))

--同步零售服务器中未定义用友已定义的颜色尺码组合
INSERT INTO OPENDATASOURCE ('SQLOLEDB', 'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.ItemAllotAnalysis
      (fchrAccountID, fchrItemID, fchrDefItemID, fchrValueID, fdtmLastTransfer, 
      fchrTimeStamp, fdtmLastModified, fchrValueName, fchrDefItemName, fintOrder, fbitExport)
SELECT TOP 100 PERCENT LS_Item.fchrAccountID, LS_Item.fchrItemID, 
      LS_UserDefValues.fchrDefItemID, LS_UserDefValues.fchrValueID, GETDATE() 
      AS fdtmLastTransfer, GETDATE() AS fchrTimeStamp, GETDATE() AS fdtmLastModified, 
      LS_UserDefValues.fchrValueName, LS_UserDef.fchrDefItemName, 
      LS_UserDef.fintOrder, LS_UserDefValues.fbitExport
FROM OPENDATASOURCE ('SQLOLEDB',  'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.UserDefValues  LS_UserDefValues INNER JOIN
      OPENDATASOURCE ('SQLOLEDB', 'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.UserDef LS_UserDef ON 
      LS_UserDefValues.fchrDefItemID = LS_UserDef.fchrDefItemID INNER JOIN  _PM_V_InventoryColorSize ON 
      LS_UserDefValues.fchrValueName = _PM_V_InventoryColorSize.cFree LEFT OUTER JOIN
      OPENDATASOURCE ('SQLOLEDB', 'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.Item LS_Item LEFT OUTER JOIN
      OPENDATASOURCE ('SQLOLEDB', 'SERVER=erpserver1;UID=sa;PWD=11111;DATABASE=UFSOFT_U8_Retail' ).UFSOFT_U8_Retail.dbo.ItemAllotAnalysis
       LS_Detail ON LS_Item.fchrItemID = LS_Detail.fchrItemID ON       _PM_V_InventoryColorSize.cInvCode = LS_Item.fchrItemCode COLLATE Chinese_PRC_CI_AS  AND 
      _PM_V_InventoryColorSize.cFree = LS_Detail.fchrValueName COLLATE Chinese_PRC_CI_AS
WHERE (LS_Detail.fchrValueName IS NULL) AND (NOT (LS_Item.fchrItemCode IS NULL))

--供应商价格建档
CREATE PROCEDURE [dbo].[_PM_P_VenPrice_Insert] 
	(@cInvCode 	[nvarchar](20),
	 @cVenCode 	[nvarchar](20)
)
AS

--供应商价格主记录建档
INSERT INTO Ven_Inv_Price_Rule (cVenCode, cInvCode, dEnableDate, cExch_Name, bPromotion, iSupplyType, btaxcost)
SELECT @cVenCode, cInvCode, CONVERT(nvarchar(10), Getdate(), 121) AS Sdate, '人民币' AS cExch_Name, 0 AS bPromotion, 1 AS iSupplyType, 0 AS btaxcost
FROM Inventory
WHERE cInvCode = @cInvCode


--供应商价格明细记录建档
INSERT INTO Ven_Inv_Price_RuleItem  (ID, itemId, iLowerLimit, iUnitPrice, iTaxRate, iTaxUnitPrice)
SELECT (SELECT TOP 1 ID FROM Ven_Inv_Price_Rule WHERE (cVenCode = @cVenCode) AND (cInvCode = @cInvCode) ORDER BY ID DESC) AS ID, 
	1 AS itemId, 0 AS iLowerLimit, iInvSPrice AS iUnitPrice, 0 AS iTaxRate, iInvSPrice AS iTaxUnitPrice
FROM Inventory
GO


--铺货计划建档
CREATE PROCEDURE [dbo].[_PM_P_Plan_Insert] 
	(@cInvCode 	[nvarchar](20),
	 @cInvStd 	[nvarchar](60),
	 @cPlanType 	[nvarchar](20),
	 @Person 	[nvarchar](20),
	 @cMemo	[nvarchar](255),
	 @iPlanCust 	[Integer]
)
AS

INSERT INTO a__Plan (PTCode, PSDate, PEDate, PBProd, PBName, PBCust, PBMemo, PCDate, PMaker)
SELECT @cPlanType AS PTCode, cInvCode, CONVERT(nvarchar(10), GETDATE(), 121) AS PSDate, 
      CONVERT(nvarchar(10), GETDATE() + 15, 121) AS PEDate,
     'P' + CONVERT(nvarchar(4), GETDATE(), 12)+(SELECT TOP 1 CONVERT(nvarchar(4),(CONVERT(Int, Right(PBName,3))+1)) FROM a__Plan WHERE CONVERT(nvarchar(7), PSDate, 121)=CONVERT(nvarchar(7), GETDATE(), 121) ORDER BY PBName DESC) AS PBName, 
      @iPlanCust AS PBCust, 
      @cMemo AS PBMemo,GETDATE() AS CreateDate, 
      @Person AS Marker
FROM Inventory
WHERE (cInvCode = @cInvCode)
GO

--存货价格建档
CREATE PROCEDURE [dbo].[_PM_P_InvPrice_Insert] 
	(@cInvCode 	[nvarchar](20),
	 @iInvSCost 	[float])
AS

INSERT INTO SA_InvUPrice (cInvCode, dStartDate, dEndDate, ISalePrice1, cMemo)
VALUES (@cInvCode,CONVERT(nvarchar(10), GETDATE(), 121),Null,@iInvSCost,'')

GO