人員維護-將好友從老版本導入

— 目的將 AM8數據庫表的好友，轉入到 OIOrg表，這個sql 需要在 oiorg 表環境下運行? （先在am8數據庫運行同名）

INSERT INTO OI_ORG_CONTACT_GROUP ( S_OWNER, G_ID, S_NAME, N_MEMBER_VER )

SELECT c.S_OWNER, c.G_GROUPID, g.S_NAME, 1

FROM [AM8].[dbo].[OI_STK_CUSTOMVIEW] c

INNER JOIN [AM8].[dbo].[OI_STK_CUSTOMGROUP] g ON g.G_GROUP = c.G_GROUPID

GROUP BY c.S_OWNER, c.G_GROUPID, g.S_NAME

ORDER BY c.S_OWNER, c.G_GROUPID

INSERT INTO OI_ORG_CONTACT ( S_OWNER, S_USER_LOGIN, G_GROUP_ID )

SELECT c.S_OWNER, c.S_USERLOGIN, c.G_GROUPID

FROM [AM8].[dbo].[OI_STK_CUSTOMVIEW] c

WHERE c.S_USERLOGIN <> ”

AND (G_GROUPID IN ( Select G_GROUP From [AM8].[dbo].[OI_STK_CUSTOMGROUP] )

ORG_GROUPID = ‘{00000000-0000-0000-0000-000000000000}’ )

ORDER BY c.S_OWNER, c.G_GROUPID, c.S_USERLOGIN

— 得到人員的聯系人版本號

UPDATE OI_ORG_T008 SET N_C41 = b.N_VER

FROM ( SELECT S_OWNER, Max( N_VER ) AS N_VER

? FROM [AM8].[dbo].[OI_STK_CUSTOMVIEW]

? GROUP BY S_OWNER ) b

WHERE S_C1 = b.S_OWNER

— 人員的聯系人分組版本號，有分組就置 1

UPDATE OI_ORG_T008 SET N_C42 = 1 WHERE S_C1 IN ( Select S_OWNER From OI_ORG_CONTACT_GROUP )

— 清除 AM8 數據表中的 聯系人 數據

— TRUNCATE TABLE [AM8].[dbo].[OI_STK_CUSTOMVIEW]

— GO

— TRUNCATE TABLE [AM8].[dbo].[OI_STK_CUSTOMGROUP]

— GO