1XWf1cqy

1USE [Bridge_Test]
2GO
3/****** Object:  StoredProcedure [dbo].[sp_i_u_people]    Script Date: 01/18/2018 10:41:04 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8
9
10
11-- =============================================
12-- Author:		<Author,,Name>
13-- Create date: <Create Date,,>
14-- Description:	<Description,,>
15-- =============================================
16/*
17Test Code
18EXEC [sp_i_u_people]
19DELETE FROM dbo.LogErrors WHERE Error_Procedure = ''
20SELECT * FROM dbo.LogErrors ORDER BY Time_Stamp DESC;
21SELECT [pkLogErrorID],[Time_Stamp],[Error_Number],[Error_Message],[Error_Severity],[Error_State],[Error_Procedure],[Error_Line],[UserName],[HostName] FROM dbo.LogErrors ORDER BY Time_Stamp DESC;
22SELECT * FROM Bridge_test.dbo.PEOPLECHANGES WHERE PROCESSED IS NULL AND PEOPLE_ID LIKE '0%' --
23DELETE FROM Bridge_test.dbo.PEOPLECHANGES WHERE PROCESSED IS NULL AND PEOPLE_ID LIKE '2%'
24*/
25ALTER PROCEDURE [dbo].[sp_i_u_people]
26AS
27BEGIN TRY
28	
29	DECLARE @LogErrors TABLE
30	(
31	[pkLogErrorID] [int] IDENTITY(1,1) NOT NULL,
32	[Error_Number] [int] NOT NULL,
33	[Error_Message] [varchar](4000) NULL,
34	[Error_Severity] [smallint] NOT NULL,
35	[Error_State] [smallint] NOT NULL,
36	[Error_Procedure] [varchar](200) NOT NULL,
37	[Error_Line] [int] NOT NULL,
38	[UserName] [varchar](128) NOT NULL,
39	[HostName] [varchar](128) NOT NULL,
40	[Time_Stamp] [datetime] NOT NULL);
41
42
43/*	
44Solution: 
45	1. Using Loop to check each of record in PEOPLECHANGES
46	2. Run the logic to synchornize data to PowerCampus
47	3. Move record from PEOPLECHANGES to PEOPLECHANGES_HIS to reduce number of loop
48	4. PEOPLECHANGES_HIS will store data to trace and check if need
49*/
50--	Start Check Comparison People Information
51
52	IF OBJECT_ID('tempdb..#TMP_PEOPLE_ACTION') IS NOT NULL
53	DROP TABLE #TMP_PEOPLE_ACTION;
54
55	CREATE TABLE #TMP_PEOPLE_ACTION 
56	(
57		sequence_no int,
58		PEOPLE_ID VARCHAR(200),
59		ACCOUNT_NO NVARCHAR(200),
60		RESPOND_CODE VARCHAR(20),
61		ACT CHAR(1) --I: INSERT, U: UPDATE, E: ERROR
62	);
63
64	
65
66	IF OBJECT_ID('tempdb..#tmp_bridge_people_change') IS NOT NULL
67	DROP TABLE #tmp_bridge_people_change;
68	SELECT t.* INTO #tmp_bridge_people_change FROM PEOPLECHANGES t WHERE t.Processed IS NULL;
69
70	IF OBJECT_ID('tempdb..#tmp_powercampus_people') IS NOT NULL
71	DROP TABLE #tmp_powercampus_people;
72	SELECT * INTO #tmp_powercampus_people FROM powercampustest.dbo.PEOPLE WHERE CAST(PEOPLE_ID AS INT) < 2000000;
73	
74	DECLARE @COUNT_PEOPLE_CHANGE INT = 0;
75	SELECT @COUNT_PEOPLE_CHANGE = COUNT(1) FROM #tmp_bridge_people_change;
76	PRINT @COUNT_PEOPLE_CHANGE
77	--SELECT * FROM #tmp_bridge_people_change
78	WHILE @COUNT_PEOPLE_CHANGE > 0
79	BEGIN
80	BEGIN TRY
81	BEGIN TRANSACTION txn_people
82	DECLARE @PEOPLE_ID VARCHAR(200),
83			@PC_PEOPLE_ID VARCHAR(200),
84			@PC_ACCOUNT_NO NVARCHAR(200),
85			@TABLE_NAME VARCHAR(200),
86			@sequence_no VARCHAR(200);
87
88	SET @PEOPLE_ID = NULL;
89	SELECT TOP 1 @sequence_no = CAST(sequence_no AS varchar(200)), @PC_PEOPLE_ID = PEOPLE_ID, @PC_ACCOUNT_NO = ACCOUNTNO FROM #tmp_bridge_people_change;
90	PRINT @sequence_no;
91	
92--	Start Check Comparison People Information
93	SELECT @PEOPLE_ID = pw.PEOPLE_ID FROM #tmp_bridge_people_change br
94	--SELECT * FROM #tmp_bridge_people_change br
95	INNER JOIN powercampustest.dbo.PEOPLE pw ON 
96	ISNULL(br.FIRST_NAME,'xxxxx') = ISNULL(pw.FIRST_NAME,'xxxxx') 
97	AND ISNULL(br.MIDDLE_NAME,'xxxxx') = ISNULL(pw.MIDDLE_NAME,'xxxxx') 
98	AND ISNULL(br.LAST_NAME,'xxxxx') = ISNULL(pw.LAST_NAME,'xxxxx')
99	--AND ISNULL(br.TAX_ID,'xxxxx') = ISNULL(pw.TAX_ID,'xxxxx') 
100	AND ISNULL(br.GOVERNMENT_ID,'xxxxx') = ISNULL(pw.GOVERNMENT_ID,'xxxxx') 
101	AND ISNULL(br.BIRTH_DATE,getdate()) = ISNULL(pw.BIRTH_DATE,getdate())
102	WHERE CAST(pw.PEOPLE_ID AS INT) < 2000000 AND br.sequence_no = @sequence_no;
103	--PRINT 'PEOPLE_ID' + @PEOPLE_ID;
104
105	IF @PEOPLE_ID IS NULL
106	BEGIN
107		DECLARE @CNT_99_SSN INT = 0,
108				@CNT_99_NAM INT = 0,
109				@CNT_99_DOB INT = 0
110		--PRINT 'CNT_99_SSN' + @CNT_99_SSN
111		--PRINT 'CNT_99_NAM' + @CNT_99_NAM
112		--PRINT 'CNT_99_DOB' + @CNT_99_DOB
113		--Check NAME matched, SSN not matched
114		SELECT @CNT_99_SSN = COUNT(1) FROM #tmp_bridge_people_change br
115		--SELECT pw.TAX_ID, br.TAX_ID FROM #tmp_bridge_people_change br
116		INNER JOIN powercampustest.dbo.PEOPLE pw ON 
117		ISNULL(br.FIRST_NAME,'xxxxx') = ISNULL(pw.FIRST_NAME,'xxxxx') 
118		AND ISNULL(br.MIDDLE_NAME,'xxxxx') = ISNULL(pw.MIDDLE_NAME,'xxxxx') 
119		AND ISNULL(br.LAST_NAME,'xxxxx') = ISNULL(pw.LAST_NAME,'xxxxx')
120		--AND ISNULL(br.TAX_ID,'xxxxx') <> ISNULL(pw.TAX_ID,'xxxxx') 
121		AND ISNULL(br.GOVERNMENT_ID,'xxxxx') <> ISNULL(pw.GOVERNMENT_ID,'xxxxx') 
122		WHERE CAST(pw.PEOPLE_ID AS INT) < 2000000 AND br.sequence_no = @sequence_no;
123
124		--Check SSN matched, Name not matched
125		SELECT @CNT_99_NAM = COUNT(1) FROM #tmp_bridge_people_change br
126		INNER JOIN powercampustest.dbo.PEOPLE pw ON 
127		(
128		ISNULL(br.FIRST_NAME,'xxxxx') <> ISNULL(pw.FIRST_NAME,'xxxxx') --changed
129		OR ISNULL(br.MIDDLE_NAME,'xxxxx') <> ISNULL(pw.MIDDLE_NAME,'xxxxx') --changed
130		OR ISNULL(br.LAST_NAME,'xxxxx') <> ISNULL(pw.LAST_NAME,'xxxxx') --changed
131		)
132		--AND ISNULL(br.TAX_ID,'xxxxx') = ISNULL(pw.TAX_ID,'xxxxx')
133		AND ISNULL(br.GOVERNMENT_ID,'xxxxx') = ISNULL(pw.GOVERNMENT_ID,'xxxxx')
134		WHERE CAST(pw.PEOPLE_ID AS INT) < 2000000 AND br.sequence_no = @sequence_no;
135
136		--Check Name matched, SSN Matched, DOB not matched
137		SELECT @CNT_99_DOB = COUNT(1) FROM #tmp_bridge_people_change br
138		INNER JOIN powercampustest.dbo.PEOPLE pw ON 
139		ISNULL(br.FIRST_NAME,'xxxxx') = ISNULL(pw.FIRST_NAME,'xxxxx') 
140		AND ISNULL(br.MIDDLE_NAME,'xxxxx') = ISNULL(pw.MIDDLE_NAME,'xxxxx') 
141		AND ISNULL(br.LAST_NAME,'xxxxx') = ISNULL(pw.LAST_NAME,'xxxxx')
142		--AND ISNULL(br.TAX_ID,'xxxxx') = ISNULL(pw.TAX_ID,'xxxxx') 
143		AND ISNULL(br.GOVERNMENT_ID,'xxxxx') = ISNULL(pw.GOVERNMENT_ID,'xxxxx') 
144		AND ISNULL(br.BIRTH_DATE,getdate()) <> ISNULL(pw.BIRTH_DATE,getdate())
145		WHERE CAST(pw.PEOPLE_ID AS INT) < 2000000 AND br.sequence_no = @sequence_no;
146
147		IF @CNT_99_SSN > 0 
148		BEGIN
149			PRINT 'SSN ERROR ' + @PEOPLE_ID
150			INSERT INTO #TMP_PEOPLE_ACTION (sequence_no, PEOPLE_ID, ACCOUNT_NO, RESPOND_CODE, ACT)
151			VALUES (@sequence_no, @PEOPLE_ID, @PC_ACCOUNT_NO, '99-SSN', 'E')
152		END
153		ELSE IF @CNT_99_NAM > 0
154		BEGIN
155			PRINT 'NAME ERROR ' + @PEOPLE_ID
156			INSERT INTO #TMP_PEOPLE_ACTION (sequence_no, PEOPLE_ID, ACCOUNT_NO, RESPOND_CODE, ACT)
157			VALUES (@sequence_no, @PEOPLE_ID, @PC_ACCOUNT_NO, '99-NAM', 'E')
158		END
159		ELSE IF @CNT_99_DOB > 0
160		BEGIN
161			PRINT 'DOB ERROR ' + @PEOPLE_ID
162			INSERT INTO #TMP_PEOPLE_ACTION (sequence_no, PEOPLE_ID, ACCOUNT_NO, RESPOND_CODE, ACT)
163			VALUES (@sequence_no, @PEOPLE_ID, @PC_ACCOUNT_NO, '99-DOB', 'E')
164		END
165		ELSE 
166		BEGIN
167			/*Generate new PEOPLE_ID*/
168			PRINT 'NEW PEOPLE ID'
169			SELECT @PEOPLE_ID = right('000000000'+ rtrim(CAST(( CAST(coalesce(MAX(CAST(PEOPLE_ID as INT)), 0) as int) + 1) as VARCHAR(50))), 9) FROM powercampustest.dbo.People
170			WHERE CAST(PEOPLE_ID AS INT) < 2000000;		
171			
172/*			
173			SELECT right('000000000'+ rtrim(CAST(( CAST(coalesce(MAX(CAST(PEOPLE_ID as INT)), 0) as int) + 1) as VARCHAR(50))), 9) FROM powercampustest.dbo.People
174			WHERE CAST(PEOPLE_ID AS INT) < 2000000;	
175(No column name)
176000095050		found next number					
177*/
178			
179			UPDATE Bridge_Test.dbo.PEOPLECHANGES SET PEOPLE_ID = @PEOPLE_ID, PEOPLE_CODE_ID = PEOPLE_CODE + @PEOPLE_ID
180			WHERE sequence_no = @sequence_no
181			INSERT INTO #TMP_PEOPLE_ACTION (sequence_no, PEOPLE_ID, ACCOUNT_NO, RESPOND_CODE, ACT)
182			VALUES (@sequence_no, @PEOPLE_ID, @PC_ACCOUNT_NO, NULL, 'I')
183		END
184	END
185	ELSE 
186	/*Found existing People*/
187	BEGIN
188		INSERT INTO #TMP_PEOPLE_ACTION (sequence_no, PEOPLE_ID, ACCOUNT_NO, RESPOND_CODE, ACT)
189		VALUES (@sequence_no, @PEOPLE_ID, @PC_ACCOUNT_NO, NULL, 'U')
190
191		UPDATE Bridge_Test.dbo.PEOPLECHANGES SET PEOPLE_ID = @PEOPLE_ID, PEOPLE_CODE_ID = PEOPLE_CODE + @PEOPLE_ID
192		WHERE sequence_no = @sequence_no
193	END
194	
195--	End Check Comparison People Information
196--	SELECT * FROM #TMP_PEOPLE_ACTION
197--	IF FALSE RAISE MESSEAGE TO UPDATE GM STATUS
198	IF (SELECT COUNT(1) FROM #TMP_PEOPLE_ACTION WHERE ACT = 'E') > 0 
199		BEGIN TRY
200			UPDATE c
201				SET c.KEY1 = tmp.RESPOND_CODE
202				FROM #TMP_PEOPLE_ACTION tmp
203				JOIN GoldMine_test.dbo.CONTACT1 c	ON  tmp.ACCOUNT_NO = c.ACCOUNTNO
204				WHERE tmp.ACT = 'E';
205			UPDATE Bridge_Test.dbo.PEOPLECHANGES SET PEOPLE_ID = '0', PEOPLE_CODE_ID = '0', Processed = 'Y'
206		WHERE sequence_no = @sequence_no
207		END TRY
208		BEGIN CATCH
209			UPDATE c
210			SET c.KEY1 = '99-OVR'
211			FROM #TMP_PEOPLE_ACTION tmp
212			JOIN GoldMine_test.dbo.CONTACT1 c	ON  tmp.ACCOUNT_NO = c.ACCOUNTNO
213			WHERE tmp.ACT = 'E';
214			INSERT INTO @LogErrors
215			(
216				[Error_Number],
217				[Error_Message],
218				[Error_Severity],
219				[Error_State],
220				[Error_Procedure],
221				[Error_Line],
222				[UserName],
223				[HostName],
224				[Time_Stamp]
225			)
226				SELECT ISNULL(ERROR_NUMBER(),0),
227					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
228					ISNULL(ERROR_SEVERITY(),0),	
229					ISNULL(ERROR_STATE(),1),	
230					ISNULL(ERROR_PROCEDURE(),''),			 
231					ISNULL(ERROR_LINE(), 0), 
232					SUSER_SNAME(), 
233					HOST_NAME(), 
234					GETDATE();
235		END CATCH
236	
237--	IF TRUE NEW RECORD THEN INSERT INTO PowerCampus
238	IF (SELECT COUNT(1) FROM #TMP_PEOPLE_ACTION WHERE ACT = 'I') > 0 
239	BEGIN
240		--PEOPLE - Revised
241		SET @TABLE_NAME = 'PEOPLE'
242		BEGIN TRY
243		INSERT INTO powercampustest.dbo.People ---should have birth date , SELECT TOP 1 * FROM powercampustest.dbo.PEOPLE SELECT * FROM Bridge_test.dbo.PEOPLECHANGES
244		  (PEOPLE_CODE,
245		   PEOPLE_ID,
246		   PEOPLE_CODE_ID,
247		   GOVERNMENT_ID, ---added 1/18/2018
248		   FIRST_NAME,
249		   MIDDLE_NAME,
250		   LAST_NAME,
251		   BIRTH_DATE,  ---added 1/17/2018
252		   CREATE_DATE,
253		   CREATE_TIME,
254		   PREFERRED_ADD,
255		   DECEASED_FLAG,
256		   CREATE_OPID,
257		   CREATE_TERMINAL,
258		   REVISION_DATE,
259		   REVISION_TIME,
260		   REVISION_OPID,
261		   REVISION_TERMINAL,
262		   RELEASE_INFO, ---added
263		   ABT_JOIN,
264		   TAX_ID) 
265		  SELECT TOP 1 
266			     pc.PEOPLE_CODE,
267				 pc.PEOPLE_ID,
268				 pc.PEOPLE_CODE_ID,
269				 pc.GOVERNMENT_ID, ---added 1/18/2018
270				 pc.FIRST_NAME,
271				 pc.MIDDLE_NAME,
272				 pc.LAST_NAME,
273				 pc.BIRTH_DATE,  ---added 1/17/2018
274				 pc.CREATE_DATE,
275				 pc.CREATE_TIME,
276				 'PERM',
277				 pc.DECEASED_FLAG,
278				 pc.CREATE_OPID,
279				 pc.CREATE_TERMINAL,
280				 pc.REVISION_DATE,
281				 pc.REVISION_TIME,
282				 pc.REVISION_OPID,
283				 pc.REVISION_TERMINAL,
284				 pc.RELEASE_INFO, ---added
285				 pc.ABT_JOIN,
286				 pc.TAX_ID
287		  FROM Bridge_test.dbo.PEOPLECHANGES pc
288		  WHERE (pc.Processed is Null) 
289		  AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
290		END TRY
291		BEGIN CATCH
292		INSERT INTO @LogErrors
293			(
294				[Error_Number],
295				[Error_Message],
296				[Error_Severity],
297				[Error_State],
298				[Error_Procedure],
299				[Error_Line],
300				[UserName],
301				[HostName],
302				[Time_Stamp]
303			)
304				SELECT ISNULL(ERROR_NUMBER(),0),
305					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
306					ISNULL(ERROR_SEVERITY(),0),	
307					ISNULL(ERROR_STATE(),1),	
308					ISNULL(ERROR_PROCEDURE(),''),			 
309					ISNULL(ERROR_LINE(), 0), 
310					SUSER_SNAME(), 
311					HOST_NAME(), 
312					GETDATE();
313		END CATCH
314
315		--PersonPhone - Revised
316		SET @TABLE_NAME = 'PersonPhone'
317		BEGIN TRY
318		INSERT INTO powercampustest.dbo.PersonPhone 
319					([PersonId] 
320					,[PhoneType] 
321					,[PhoneNumber] 
322					,[CountryId]
323					,[Create_Date]
324					,[Create_Time]
325					,[Create_Opid]
326					,[Create_Terminal]
327					,[Revision_Date]
328					,[Revision_Time]
329					,[Revision_Opid]
330					,[Revision_Terminal])
331				(
332				SELECT TOP 1
333				p.PersonId,
334				'HOME',
335				COALESCE(REPLACE(REPLACE(REPLACE(REPLACE(pc.Phone1,'(',''),' ',''),'-',''),')',''), N' '),
336				ISNULL(CC.CODE_VALUE_KEY, ''), 
337				COALESCE(pc.CREATE_Date, GetDate()),
338				COALESCE(pc.CREATE_TIME, GetDate()),
339				COALESCE(pc.Create_Opid, N' '),
340				pc.CREATE_TERMINAL,
341				COALESCE(pc.Revision_Date, GetDate()),
342				COALESCE(pc.Revision_TIME, GetDate()),
343				COALESCE(pc.Revision_Opid, N' '),
344				pc.REVISION_TERMINAL
345				from Bridge_test.dbo.PEOPLECHANGES pc 
346				INNER JOIN [powercampustest].[dbo].[PEOPLE] p ON pc.PEOPLE_ID = p.PEOPLE_ID
347				LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]  /*Lookup Country*/
348				WHERE 1=1 
349				AND EXISTS 
350				(
351					SELECT 1 FROM #TMP_PEOPLE_ACTION tmp 
352					WHERE tmp.ACT = 'I' 
353					AND pc.sequence_no = tmp.sequence_no
354				)
355				AND pc.Processed IS NULL
356
357				UNION
358				 
359				SELECT TOP 1
360				p.PersonId,
361				'Office',
362				COALESCE(REPLACE(REPLACE(REPLACE(REPLACE(pc.Phone2,'(',''),' ',''),'-',''),')',''), N' '),
363				ISNULL(CC.CODE_VALUE_KEY, ''),
364				COALESCE(pc.CREATE_Date, GetDate()),
365				COALESCE(pc.CREATE_TIME, GetDate()),
366				COALESCE(pc.Create_Opid, N' '),
367				pc.CREATE_TERMINAL,
368				COALESCE(pc.Revision_Date, GetDate()),
369				COALESCE(pc.Revision_TIME, GetDate()),
370				COALESCE(pc.Revision_Opid, N' '),
371				pc.REVISION_TERMINAL
372				from Bridge_test.dbo.PEOPLECHANGES pc 
373				LEFT JOIN powercampustest.dbo.PEOPLE p ON pc.PEOPLE_ID = p.PEOPLE_ID
374				LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]  /*Lookup Country*/
375				WHERE 1=1 
376				AND EXISTS 
377				(
378					SELECT 1 FROM #TMP_PEOPLE_ACTION tmp 
379					WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no
380				)
381				AND pc.Processed IS NULL
382
383				UNION 
384
385				SELECT TOP 1
386				p.PersonId,
387				'Cell',
388				COALESCE(REPLACE(REPLACE(REPLACE(REPLACE(pc.Phone3,'(',''),' ',''),'-',''),')',''), N' '),
389				ISNULL(CC.CODE_VALUE_KEY, ''),
390				COALESCE(pc.CREATE_Date, GetDate()),
391				COALESCE(pc.CREATE_TIME, GetDate()),
392				COALESCE(pc.Create_Opid, N' '),
393				pc.CREATE_TERMINAL,
394				COALESCE(pc.Revision_Date, GetDate()),
395				COALESCE(pc.Revision_TIME, GetDate()),
396				COALESCE(pc.Revision_Opid, N' '),
397				pc.REVISION_TERMINAL
398				from Bridge_test.dbo.PEOPLECHANGES pc 
399				LEFT JOIN powercampustest.dbo.PEOPLE p ON pc.PEOPLE_ID = p.PEOPLE_ID
400				LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]  /*Lookup Country*/
401				WHERE 1=1
402				AND EXISTS 
403				(
404					SELECT 1 FROM #TMP_PEOPLE_ACTION tmp 
405					WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no
406				)
407				AND pc.Processed IS NULL
408				);
409		END TRY
410		BEGIN CATCH
411		INSERT INTO @LogErrors
412			(
413				[Error_Number],
414				[Error_Message],
415				[Error_Severity],
416				[Error_State],
417				[Error_Procedure],
418				[Error_Line],
419				[UserName],
420				[HostName],
421				[Time_Stamp]
422			)
423				SELECT ISNULL(ERROR_NUMBER(),0),
424					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
425					ISNULL(ERROR_SEVERITY(),0),	
426					ISNULL(ERROR_STATE(),1),	
427					ISNULL(ERROR_PROCEDURE(),''),			 
428					ISNULL(ERROR_LINE(), 0), 
429					SUSER_SNAME(), 
430					HOST_NAME(), 
431					GETDATE();
432		END CATCH
433
434		--PersonEthnicity
435		SET @TABLE_NAME = 'PersonEthnicity'
436		BEGIN TRY
437		INSERT INTO powercampustest.dbo.PersonEthnicity 
438		  (IpedsEthnicityId,
439		   IpedsFederalCategoryId,
440		   CREATE_DATE,
441		   CREATE_TIME,
442		   CREATE_OPID,
443		   CREATE_TERMINAL,
444		   REVISION_DATE,
445		   REVISION_TIME,
446		   REVISION_OPID,
447		   REVISION_TERMINAL,
448		   PersonId
449		   ) 
450		  SELECT TOP 1--t.Ubrthcnty AS IpedsEthnicityId,
451				 --t.Uethnicbg AS IpedsFederalCategoryId,
452				 0 AS IpedsEthnicityId,
453				 0 AS IpedsFederalCategoryId,
454				 t.CREATE_DATE,
455				 t.CREATE_TIME,
456				 t.CREATE_OPID,
457				 t.CREATE_TERMINAL,
458				 t.REVISION_DATE,
459				 t.REVISION_TIME,
460				 t.REVISION_OPID,
461				 t.REVISION_TERMINAL,
462				 p.PersonId
463		  FROM Bridge_test.dbo.PEOPLECHANGES t
464		  --LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[LONG_DESC] like '%' + t.[Ubrthcnty] + '%' /*Lookup Country*/
465		  --LEFT JOIN [powercampustest].[dbo].[DEMOGRAPHICS] DG ON DG.ETHNICITY
466
467		  LEFT JOIN powercampustest.dbo.PEOPLE p ON t.PEOPLE_ID = p.PEOPLE_ID
468		  WHERE (Processed is Null) 
469		  AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND t.sequence_no = tmp.sequence_no);
470		END TRY
471		BEGIN CATCH
472		INSERT INTO @LogErrors
473			(
474				[Error_Number],
475				[Error_Message],
476				[Error_Severity],
477				[Error_State],
478				[Error_Procedure],
479				[Error_Line],
480				[UserName],
481				[HostName],
482				[Time_Stamp]
483			)
484				SELECT ISNULL(ERROR_NUMBER(),0),
485					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
486					ISNULL(ERROR_SEVERITY(),0),	
487					ISNULL(ERROR_STATE(),1),	
488					ISNULL(ERROR_PROCEDURE(),''),			 
489					ISNULL(ERROR_LINE(), 0), 
490					SUSER_SNAME(), 
491					HOST_NAME(), 
492					GETDATE();
493		END CATCH
494
495		--Telecomunications - Revised
496		SET @TABLE_NAME = 'Telecomunications'
497		BEGIN TRY
498		INSERT INTO [powercampustest].[dbo].[TELECOMMUNICATIONS]
499						   ([PEOPLE_ORG_CODE_ID]
500						   ,[COM_TYPE]
501						   ,[COM_STRING]
502						   ,[PRIMARY_FLAG]
503						   ,[PRIVATE_FLAG]
504						   ,[STATUS]
505						   ,[ADDRESS_TYPE]
506						   ,[COMMENTS]
507						   ,[CREATE_DATE]
508						   ,[CREATE_TIME]
509						   ,[CREATE_OPID]
510						   ,[CREATE_TERMINAL]
511						   ,[REVISION_DATE]
512						   ,[REVISION_TIME]
513						   ,[REVISION_OPID]
514						   ,[REVISION_TERMINAL])
515					 SELECT TOP 1
516							t.PEOPLE_CODE_ID, --need PEOPLE_ORG_CODE_ID
517							/*SELECT DISTINCT [COM_TYPE] FROM powercampustest.DBO.
518							TELECOMMUNICATIONS
519							COM_TYPE
520							ALTPHN
521							ALTEML
522							HISTE
523							*/			
524							'ALTEML', ---COM_TYPE need code for email 
525							COALESCE(t.PERSONAL_EMAIL, N' '), ---Insert null error, COM_STRING personal email from contact support
526							'N', --PRIMARY_FLAG
527							'N', --PRIVATE_FLAG
528							'A', --STATUS
529							'Home', --address type, should be Home not HOME									
530							COALESCE(N' ', N' '), --comments
531							COALESCE(t.CREATE_DATE, GETDATE()), --date format, 2006-07-26 00:00:00.000
532							COALESCE(t.CREATE_TIME, GetDate()), --time format, 1900-01-01 08:39:02.000
533							COALESCE(t.CREATE_OPID, N' '), 
534							COALESCE(t.CREATE_TERMINAL, N' '), 
535							--COALESCE(REVISION_DATE, (SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)) x)), --needs only date
536							COALESCE(t.REVISION_DATE, GETDATE()), --date format, 2006-07-26 00:00:00.000
537							COALESCE(t.REVISION_TIME, GetDate()), --time format, 1900-01-01 08:39:02.000
538							COALESCE(t.REVISION_OPID, N' '),  
539							COALESCE(t.REVISION_TERMINAL, N' ') 
540							FROM Bridge_test.dbo.peoplechanges t
541							WHERE (t.Processed is Null)
542	    AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND t.sequence_no = tmp.sequence_no);
543		END TRY
544		BEGIN CATCH
545		INSERT INTO @LogErrors
546			(
547				[Error_Number],
548				[Error_Message],
549				[Error_Severity],
550				[Error_State],
551				[Error_Procedure],
552				[Error_Line],
553				[UserName],
554				[HostName],
555				[Time_Stamp]
556			)
557				SELECT ISNULL(ERROR_NUMBER(),0),
558					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
559					ISNULL(ERROR_SEVERITY(),0),	
560					ISNULL(ERROR_STATE(),1),	
561					ISNULL(ERROR_PROCEDURE(),''),			 
562					ISNULL(ERROR_LINE(), 0), 
563					SUSER_SNAME(), 
564					HOST_NAME(), 
565					GETDATE();
566		END CATCH
567
568		--Address - Revised
569		SET @TABLE_NAME = 'ADDRESS'
570		BEGIN TRY
571		INSERT INTO [powercampustest].[dbo].ADDRESS
572			(PEOPLE_ORG_CODE,
573			PEOPLE_ORG_ID,
574			PEOPLE_ORG_CODE_ID,
575			ADDRESS_TYPE,
576			ADDRESS_LINE_1,
577			ADDRESS_LINE_2,
578			ADDRESS_LINE_3,
579			CITY,
580			STATE,
581			ZIP_CODE,
582			COUNTRY,
583			COUNTY,
584			DAY_PHONE,
585			EVENING_PHONE,
586			MAIN_FAX,
587			ALTERNATE_FAX,
588			NO_MAIL,
589			NO_CALL_DAY_PHONE,
590			NO_CALL_EVE_PHONE,
591			NOT_VALID_BEG_MM,
592			NOT_VALID_BEG_YY,
593			NOT_VALID_END_MM,
594			NOT_VALID_END_YY,
595			ALTERNATE_ADD_TYPE,
596			CREATE_DATE,
597			CREATE_TIME,
598			CREATE_OPID,
599			CREATE_TERMINAL,
600			REVISION_DATE,
601			REVISION_TIME,
602			REVISION_OPID,
603			REVISION_TERMINAL,
604			ABT_JOIN,
605			CITY_PREFIX,
606			CITY_SUFFIX,
607			EMAIL_ADDRESS,
608			ADDRESS_LINE_4,
609			HOUSE_NUMBER)
610		SELECT TOP 1
611			PC.PEOPLE_CODE, 
612			PC.PEOPLE_ID, 
613			PC.PEOPLE_CODE_ID,
614			'PERM', 			
615			PC.ADDRESS1,
616			PC.ADDRESS2,
617			PC.ADDRESS3,
618			PC.CITY,
619			PC.STATE,
620			PC.ZIP,
621			ISNULL(CC.CODE_VALUE_KEY, ''), 
622			ISNULL(CT.CODE_VALUE, ''), 
623			PC.PHONE1,
624			PC.PHONE2,
625			NULL,
626			NULL,
627			' ',
628			NULL,
629			NULL,
630			NULL,
631			NULL,
632			NULL,
633			NULL,
634			NULL,
635			COALESCE(PC.CREATE_Date, GetDate()),
636			COALESCE(PC.CREATE_TIME, GetDate()),
637			COALESCE(PC.Create_Opid, N' '),
638			PC.CREATE_TERMINAL,
639			COALESCE(PC.Revision_Date, GetDate()),
640			COALESCE(PC.Revision_TIME, GetDate()),
641			COALESCE(PC.Revision_Opid, N' '),
642			PC.REVISION_TERMINAL,
643			'*',
644			NULL,
645			NULL,
646			PC.UEMAILSCT,
647			NULL,
648			NULL
649		FROM         [Bridge_test].[dbo].[PEOPLECHANGES] PC
650		LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]  /*Lookup Country*/
651		LEFT JOIN [powercampustest].[dbo].[CODE_COUNTY] CT ON CT.[LONG_DESC] =  PC.UCOUNTY  /*Lookup County*/
652		WHERE        (Processed is Null)
653		AND EXISTS 
654		(
655			SELECT 1 FROM #TMP_PEOPLE_ACTION tmp 
656			WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no
657		);
658        END TRY
659		BEGIN CATCH
660		INSERT INTO @LogErrors
661			(
662				[Error_Number],
663				[Error_Message],
664				[Error_Severity],
665				[Error_State],
666				[Error_Procedure],
667				[Error_Line],
668				[UserName],
669				[HostName],
670				[Time_Stamp]
671			)
672				SELECT ISNULL(ERROR_NUMBER(),0),
673					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
674					ISNULL(ERROR_SEVERITY(),0),	
675					ISNULL(ERROR_STATE(),1),	
676					ISNULL(ERROR_PROCEDURE(),''),			 
677					ISNULL(ERROR_LINE(), 0), 
678					SUSER_SNAME(), 
679					HOST_NAME(), 
680					GETDATE();
681		END CATCH
682
683		--STOPLIST - Revised
684		
685		SET @TABLE_NAME = 'STOPLIST'
686		WAITFOR DELAY '00:00:02';
687		BEGIN TRY
688		
689		DECLARE @CNT_STOPLIST INT = 0
690		SELECT @CNT_STOPLIST = COUNT(1) FROM powercampustest.dbo.[STOPLIST] WHERE PEOPLE_ID IN 
691		(SELECT PC.PEOPLE_ID  FROM  Bridge_test.dbo.PeopleChanges PC
692		WHERE (PC.Processed is Null)
693		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' 
694		AND pc.sequence_no = tmp.sequence_no))
695		
696		DELETE FROM powercampustest.dbo.[STOPLIST] WHERE PEOPLE_ID = '000095052'
697		
698		IF @CNT_STOPLIST > 0 
699		BEGIN
700			DELETE FROM powercampustest.dbo.[STOPLIST] WHERE PEOPLE_ID IN 
701			(SELECT PC.PEOPLE_ID  FROM  Bridge_test.dbo.PeopleChanges PC
702			WHERE (PC.Processed is Null)
703			AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' 
704			AND pc.sequence_no = tmp.sequence_no))
705		END
706		
707		INSERT INTO powercampustest.dbo.[STOPLIST] 
708		(
709			PEOPLE_CODE,
710			PEOPLE_ID,
711			PEOPLE_CODE_ID,
712			STOP_REASON,
713			STOP_DATE,
714			CLEARED,
715			CLEARED_DATE,
716			COMMENTS,
717			CREATE_DATE,
718			CREATE_TIME,
719			CREATE_OPID,
720			CREATE_TERMINAL,
721			REVISION_DATE,
722			REVISION_TIME,
723			REVISION_OPID,
724			REVISION_TERMINAL,
725			ABT_JOIN
726		)
727		SELECT TOP 1
728			PC.PEOPLE_CODE,
729			PC.PEOPLE_ID,
730			PC.PEOPLE_CODE_ID,
731			'RGSA' ---check stop reason
732			,GetDate(),'Y',GetDate(),
733			'SP',
734			COALESCE(PC.CREATE_Date, GetDate()),
735			COALESCE(PC.CREATE_TIME, GetDate()),
736			COALESCE(PC.Create_Opid, N' '),
737			PC.CREATE_TERMINAL,
738			COALESCE(PC.Revision_Date, GetDate()),
739			COALESCE(PC.Revision_TIME, GetDate()),
740			COALESCE(PC.Revision_Opid, N' '),
741			PC.REVISION_TERMINAL,
742			'*'
743		FROM  Bridge_test.dbo.PeopleChanges PC
744		WHERE (PC.Processed is Null)
745		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
746        END TRY
747		BEGIN CATCH
748		INSERT INTO @LogErrors
749			(
750				[Error_Number],
751				[Error_Message],
752				[Error_Severity],
753				[Error_State],
754				[Error_Procedure],
755				[Error_Line],
756				[UserName],
757				[HostName],
758				[Time_Stamp]
759			)
760				SELECT ISNULL(ERROR_NUMBER(),0),
761					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
762					ISNULL(ERROR_SEVERITY(),0),	
763					ISNULL(ERROR_STATE(),1),	
764					ISNULL(ERROR_PROCEDURE(),''),			 
765					ISNULL(ERROR_LINE(), 0), 
766					SUSER_SNAME(), 
767					HOST_NAME(), 
768					GETDATE();
769		END CATCH
770		
771		--ADDRESSHIERARCHYUNIQUE - Revised
772		SET @TABLE_NAME = 'ADDRESSHIERARCHYUNIQUE'
773		BEGIN TRY
774		INSERT INTO powercampustest.dbo.ADDRESSHIERARCHYUNIQUE
775				(PEOPLE_ORG_CODE_ID
776				,ADDRESS_TYPE
777				,RANK
778				,CREATE_DATE
779				,CREATE_TIME
780				,CREATE_OPID
781				,CREATE_TERMINAL
782				,REVISION_DATE
783				,REVISION_TIME
784				,REVISION_OPID
785				,REVISION_TERMINAL)
786           
787		SELECT  TOP 1
788				PC.PEOPLE_CODE_ID, --ahs O prepended to people id O000000003
789				'PERM',
790				1, 
791				COALESCE(PC.CREATE_Date, GetDate()),
792				COALESCE(PC.CREATE_TIME, GetDate()),
793				COALESCE(PC.Create_Opid, N' '),
794				PC.CREATE_TERMINAL,
795				COALESCE(PC.Revision_Date, GetDate()),
796				COALESCE(PC.Revision_TIME, GetDate()),
797				COALESCE(PC.Revision_Opid, N' '),
798				PC.REVISION_TERMINAL
799		FROM Bridge_test.dbo.PeopleChanges PC
800		WHERE (Processed is Null)
801		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
802        END TRY
803		BEGIN CATCH
804		INSERT INTO @LogErrors
805			(
806				[Error_Number],
807				[Error_Message],
808				[Error_Severity],
809				[Error_State],
810				[Error_Procedure],
811				[Error_Line],
812				[UserName],
813				[HostName],
814				[Time_Stamp]
815			)
816				SELECT ISNULL(ERROR_NUMBER(),0),
817					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
818					ISNULL(ERROR_SEVERITY(),0),	
819					ISNULL(ERROR_STATE(),1),	
820					ISNULL(ERROR_PROCEDURE(),''),			 
821					ISNULL(ERROR_LINE(), 0), 
822					SUSER_SNAME(), 
823					HOST_NAME(), 
824					GETDATE();
825		END CATCH
826		
827		--AddressSchedule - Revised
828		SET @TABLE_NAME = 'AddressSchedule'
829		BEGIN TRY
830		INSERT INTO powercampustest.dbo.ADDRESSSCHEDULE (
831			zip_code, 
832			address_line_1, 
833			address_line_2, 
834			address_line_3, 
835			city, 
836			state, 
837			county, 
838			alternate_fax, 
839			email_address, 
840			main_fax, 
841			address_type, 
842			--SEQUENCE_NO,  --is identity, it should increment, Explicit value must be specified for identity column in table 'ADDRESSSCHEDULE' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
843			CREATE_OPID, 
844			CREATE_TERMINAL, 
845			REVISION_OPID, 
846			REVISION_TERMINAL, 
847			PEOPLE_ORG_CODE, 
848			PEOPLE_ORG_ID, 
849			PEOPLE_ORG_CODE_ID, 
850			NO_MAIL, 
851			STATUS, 
852			RECURRING, 
853			APPROVED,
854			CREATE_DATE,  
855			CREATE_TIME,
856			REVISION_DATE,
857			REVISION_TIME)
858		SELECT TOP 1
859			PC.ZIP, 
860			PC.ADDRESS1, 
861			PC.ADDRESS2, 
862			PC.ADDRESS3, 
863			PC.CITY, 
864			PC.STATE, 
865			ISNULL(CT.CODE_VALUE, ''), /*Lookup County*/
866			PC.FAX, 
867			PC.UEMAILSCT, 
868			PC.FAX, 
869			--COALESCE(UADDRTYPE, N' '), 
870			'PERM', 		
871			--COALESCE(SEQUENCE_NO, 1), --sequence_no, is identity, it should increment
872			COALESCE(PC.CREATE_OPID, N' '), 
873			COALESCE(PC.CREATE_TERMINAL, N' '), 
874			COALESCE(PC.REVISION_OPID, N' '), 
875			COALESCE(PC.REVISION_TERMINAL, N' '),
876			COALESCE(PC.PEOPLE_CODE, N' '), 
877			COALESCE(PC.PEOPLE_ID, N' '), 
878			COALESCE(PC.PEOPLE_CODE_ID, N' '), 
879			COALESCE(N' ', N' '), 
880			COALESCE(N' ', N' '), 
881			COALESCE(N' ', N' '), 
882			COALESCE(N' ', N' '),
883			COALESCE(PC.CREATE_DATE, GetDate()),  
884			COALESCE(PC.CREATE_TIME, GetDate()),  
885			COALESCE(PC.REVISION_DATE, GetDate()), 
886			COALESCE(PC.REVISION_TIME, GetDate())  
887		from Bridge_test.dbo.peoplechanges PC
888		LEFT JOIN [powercampustest].[dbo].[CODE_COUNTY] CT ON CT.[LONG_DESC] =  PC.UCOUNTY  /*Lookup County*/
889		WHERE        (PC.Processed is Null)
890		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
891        END TRY
892		BEGIN CATCH
893		INSERT INTO @LogErrors
894			(
895				[Error_Number],
896				[Error_Message],
897				[Error_Severity],
898				[Error_State],
899				[Error_Procedure],
900				[Error_Line],
901				[UserName],
902				[HostName],
903				[Time_Stamp]
904			)
905				SELECT ISNULL(ERROR_NUMBER(),0),
906					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
907					ISNULL(ERROR_SEVERITY(),0),	
908					ISNULL(ERROR_STATE(),1),	
909					ISNULL(ERROR_PROCEDURE(),''),			 
910					ISNULL(ERROR_LINE(), 0), 
911					SUSER_SNAME(), 
912					HOST_NAME(), 
913					GETDATE();
914		END CATCH
915
916		--Mailing - Revised
917		SET @TABLE_NAME = 'Mailing111'
918		BEGIN TRY
919		
920		DECLARE @CNT_MAILING INT = 0
921		SELECT @CNT_MAILING = COUNT(1) FROM powercampustest.dbo.MAILING WHERE PEOPLE_ORG_ID IN 
922		(SELECT PC.PEOPLE_ID  FROM  Bridge_test.dbo.PeopleChanges PC
923		WHERE (PC.Processed is Null)
924		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' 
925		AND pc.sequence_no = tmp.sequence_no))
926		
927		IF @CNT_MAILING > 0 
928		BEGIN
929			DELETE FROM powercampustest.dbo.MAILING WHERE PEOPLE_ORG_ID IN 
930			(SELECT PC.PEOPLE_ID  FROM  Bridge_test.dbo.PeopleChanges PC
931			WHERE (PC.Processed is Null)
932			AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' 
933			AND pc.sequence_no = tmp.sequence_no))
934		END
935		
936		INSERT INTO powercampustest.dbo.MAILING
937		(
938			PEOPLE_ORG_CODE,
939			PEOPLE_ORG_ID,
940			PEOPLE_ORG_CODE_ID,
941			MAILING_TYPE,
942			ADDRESS_TYPE,
943			CREATE_DATE,CREATE_TIME,
944			CREATE_OPID,
945			CREATE_TERMINAL,
946			REVISION_DATE,
947			REVISION_TIME,
948			REVISION_OPID,
949			REVISION_TERMINAL,
950			ABT_JOIN,
951			RECIPIENT_CODE,
952			RECIPIENT_ID,
953			ADD_TITLE_LINE_1,
954			ADD_TITLE_LINE_2,
955			PREFERRED_ADDRESS
956		)
957		SELECT TOP 1
958			PC.PEOPLE_CODE,
959			PC.PEOPLE_ID,
960			PC.PEOPLE_CODE_ID,
961			COALESCE(PC.UADDRTYPE, N' '),
962			PC.UADDRTYPE,
963			COALESCE(PC.CREATE_Date, GetDate()),
964			COALESCE(PC.CREATE_TIME, GetDate()),
965			COALESCE(PC.Create_Opid, N' '),
966			PC.CREATE_TERMINAL,
967			COALESCE(PC.Revision_Date, GetDate()),
968			COALESCE(PC.Revision_TIME, GetDate()),
969			COALESCE(PC.Revision_Opid, N' '),
970			PC.REVISION_TERMINAL,
971			'*',
972			'P',
973			PC.PEOPLE_ID, ---check RECIPIENT_ID
974			'S',
975			' ',
976			'PERM'
977		FROM  Bridge_test.dbo.PeopleChanges PC
978		WHERE (PC.Processed is Null)	
979		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
980        END TRY
981		BEGIN CATCH
982		INSERT INTO @LogErrors
983			(
984				[Error_Number],
985				[Error_Message],
986				[Error_Severity],
987				[Error_State],
988				[Error_Procedure],
989				[Error_Line],
990				[UserName],
991				[HostName],
992				[Time_Stamp]
993			)
994				SELECT ISNULL(ERROR_NUMBER(),0),
995					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE123: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
996					ISNULL(ERROR_SEVERITY(),0),	
997					ISNULL(ERROR_STATE(),1),	
998					ISNULL(ERROR_PROCEDURE(),''),			 
999					ISNULL(ERROR_LINE(), 0), 
1000					SUSER_SNAME(), 
1001					HOST_NAME(), 
1002					GETDATE();
1003		END CATCH
1004
1005		--DEMOGRAPHICS - Revised - Will move before PersonEthnicity
1006		SET @TABLE_NAME = 'DEMOGRAPHICS'
1007		BEGIN TRY
1008		INSERT INTO powercampustest.dbo.DEMOGRAPHICS 
1009		(
1010			PEOPLE_CODE, 
1011			PEOPLE_ID, 
1012			PEOPLE_CODE_ID, 
1013			CREATE_DATE, 
1014			CREATE_TIME, 
1015			CREATE_OPID, 
1016			CREATE_TERMINAL, 
1017			REVISION_DATE, 
1018			REVISION_TIME, 
1019			REVISION_OPID, 
1020			REVISION_TERMINAL, 
1021			ABT_JOIN, 
1022			LAST_ACTIVITY, 
1023			CURRENT_ACTIVITY,
1024			academic_year, 
1025			academic_term, 
1026			academic_session,
1027			legal_residence, 
1028			gender, 
1029			marital_status, 
1030			religion, 
1031			veteran,
1032			citizenship,
1033			visa
1034		)
1035		SELECT TOP 1
1036			COALESCE(PC.PEOPLE_CODE, N' '), 
1037			COALESCE(PC.PEOPLE_ID, N' '), 
1038			PC.PEOPLE_CODE_ID, 
1039			--COALESCE(CREATE_DATE, (SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)) x), --needs only date
1040			COALESCE(PC.CREATE_DATE, GETDATE()), --date format, 2006-07-26 00:00:00.000
1041			COALESCE(PC.CREATE_TIME, GetDate()), --time format, 1900-01-01 08:39:02.000
1042			COALESCE(PC.CREATE_OPID, N' '), 
1043			COALESCE(PC.CREATE_TERMINAL, N' '), 
1044			--COALESCE(REVISION_DATE, (SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)) x)), --needs only date
1045			COALESCE(PC.REVISION_DATE, GETDATE()), --date format, 2006-07-26 00:00:00.000
1046			COALESCE(PC.REVISION_TIME, GetDate()), --time format, 1900-01-01 08:39:02.000
1047			COALESCE(PC.REVISION_OPID, N' '), 
1048			COALESCE(PC.REVISION_TERMINAL, N' '), 
1049			COALESCE(PC.ABT_JOIN, N' '), 
1050			COALESCE(N' ', N' '), 
1051			COALESCE(N' ', N' '), 
1052			--COALESCE(LEFT(PC.KEY2,CHARINDEX(' ',PC.KEY2)-1), N' '), --academic_year
1053			COALESCE(N' ', N' '), --academic_year
1054			--COALESCE(RIGHT(PC.KEY2,LEN(PC.KEY2)-CHARINDEX(' ',PC.KEY2)), N' '), --academic_term
1055			COALESCE(N' ', N' '), --academic_term
1056			--COALESCE(PC.KEY5, N' '), --ACADEMIC_SESSION
1057			COALESCE(N' ', N' '), --academic_session
1058			--UBRTHCNTY, 
1059			ISNULL(CC.CODE_VALUE_KEY, ''),
1060			--COALESCE(UGENDER, N' '), ---SELECT UGENDER FROM Bridge_test.dbo.peoplechanges UGENDER M,
1061			case PC.UGENDER --nvarchar(1)
1062			when 'M' then 'M'
1063			when 'F' then 'F'
1064			when 'U' then ' '  ---dont' find unknown
1065			else ' ' --null
1066			end as Gender, --PC
1067			/*
1068			SELECT DISTINCT Gender FROM powercampustest.dbo.DEMOGRAPHICS 
1069			Gender
1070			F
1071			M
1072			U
1073					
1074			GM Field		PC Field	
1075			M	=	Male
1076			F	=	Female
1077			U	=	Unknown
1078			*/
1079			--UMARSTAT, ---SELECT UMARSTAT FROM Bridge_test.dbo.peoplechanges, UMARSTAT S, 
1080			case PC.UMARSTAT		--nvarchar(4) map is wrong
1081			when 'D' then 'DVRC'
1082			when 'M' then 'MARR'
1083			when 'O' then 'UNKN'
1084			when 'S' then 'SNGL'
1085			when 'U' then 'UNKN'
1086			when 'W' then 'WDWD'
1087			when 'X' then 'S'
1088			else ' ' --null
1089			end as marital_status, --PC
1090			/*
1091			GM Field		PC Field	
1092			D	=	Divorced
1093			M	=	Married
1094			O	=	Other
1095			S	=	Single
1096			U	=	Unknown
1097			W	=	Widowed
1098			X	=	Seperated
1099			*/
1100			--UDENOMINAT, ---SELECT UDENOMINAT FROM Bridge_test.dbo.peoplechanges
1101			(SELECT TOP 1 ISNULL(CC.CODE_VALUE_KEY, '') FROM Bridge_test.dbo.peoplechanges pc 
1102						LEFT JOIN [powercampustest].[dbo].[CODE_RELIGION] CC ON CC.[CODE_VALUE] = PC.[UDENOMINAT]
1103						WHERE pc.Processed IS NULL 
1104						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
1105			/*case PC.UDENOMINAT				--nvarchar(4) map is wrong
1106			when 'ADV' then 'SDA'	
1107			when 'AOG' then 'ASSM'	
1108			when 'APO' then 'APO'
1109			when 'BAP' then 'BAPT'
1110			when 'BIB' then 'BIB'	
1111			when 'CC' then 'CHOC'
1112			when 'CHAR' then 'CHAR'
1113			when 'CHCH' then 'CHRS'
1114			when 'COG' then 'CHOG'
1115			when 'COV' then 'COV'
1116			when 'EPIS' then 'EPIS'
1117			when 'EVAN' then 'EVAN'	
1118			when 'FOUR' then 'FSQR'	
1119			when 'FULL' then 'FULL'	
1120			when 'GREG' then 'CONG'	
1121			when 'HOLI' then 'HOLI'	
1122			when 'IND' then 'IND'	
1123			when 'ISL' then 'ISLA'
1124			when 'LUT' then 'LUTH'	
1125			when 'MEN' then 'MENN'	
1126			when 'MET' then 'METH'	
1127			when 'MISS' then 'MISS'	
1128			when 'NA' then 'NONR'	
1129			when 'NAZ' then 'NAZ'	
1130			when 'NON' then 'NOND'	
1131			when 'OTH' then 'OTHR'
1132			when 'PENT' then 'PENT'	
1133			when 'PRES' then 'PRES'	
1134			when 'RCAT' then 'RCA'	
1135			when 'REF' then 'REFM'	
1136			when 'SBAP' then 'SBAP'	
1137			when 'WES' then 'WES' --didn't find wesleyan need to check
1138			--when 'JW' then 'Jehovah's Witness'	--need to escape the '
1139			when 'JEW' then 'JEW'
1140			when 'LDS' then 'LDS'	
1141			when 'ORTH' then 'ORX'
1142			else ' ' --null
1143			end as religion,*/ --PC
1144			/*
1145			GM Field		PC Field	
1146			ADV	=	7th Day Adventist	
1147			AOG	=	 Assembly of God	
1148			APO	=	Apostolic	
1149			BAP	=	Baptist	
1150			BIB	=	Bible Church	
1151			CC	=	Church of Christ	
1152			CHAR	=	Charasmatic	
1153			CHCH	=	Christian	
1154			COG	=	Church of God	
1155			COV	=	Covenant	
1156			EPIS	=	Episcopal	
1157			EVAN	=	Evangelical	
1158			FOUR	=	Foursquare	
1159			FULL	=	Full Gospel	
1160			GREG	=	Congregational	
1161			HOLI	=	Holiness	
1162			IND	=	Independent	
1163			ISL	=	Islam	
1164			LUT	=	Lutheran	
1165			MEN	=	Mennonite	
1166			MET	=	Methodist	
1167			MISS	=	Missionary	
1168			NA	=	No Religion	
1169			NAZ	=	Nazarene	
1170			NON	=	Non-Denominational	
1171			OTH	=	Other	
1172			PENT	=	Pentecostal	
1173			PRES	=	Presbyterian	
1174			RCAT	=	Roman Catholic	
1175			REF	=	Reformed	
1176			SBAP	=	Southern Baptist	
1177			WES	=	Wesleyan	
1178			JW	=	Jehovah's Witness	
1179			JEW	=	Jewish	
1180			LDS	=	Latter Day Saints	
1181			ORTH	=	Orthodox	
1182			Uveteran		demographics_veteran	
1183			*/
1184			--UVETERAN  ---SELECT UVETERAN FROM Bridge_test.dbo.peoplechanges, UVETERAN ---NULL
1185				
1186			case PC.UVETERAN		
1187			when 'VETA' then 'VETA'
1188			when 'VETT' then 'VETT'
1189			when 'VET' then 'VET'
1190			when 'VETB' then 'VETB'
1191			else ' ' --null
1192			end as veteran, --PC
1193			/*
1194			GM Field		PC Field
1195			VETA	=	Vet Active Milit TA	
1196			VETT	=	Vet Transfer Benefit	
1197			VET	=	Veteran	
1198			VETB	=	Veteran with Benfits	
1199			*/
1200			(SELECT TOP 1 ISNULL(CC.CODE_VALUE_KEY, '') FROM Bridge_test.dbo.peoplechanges pc  ---looks up code value, UCNTRYCITZ United States, didn't find, SELECT * FROM Bridge_test.dbo.PEOPLECHANGES ORDER BY sequence_no DESC
1201	--MEDIUM_DESC	LONG_DESC
1202	--United States	United States of America
1203			
1204						--LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ] ---country is null
1205						LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ] ---MEDIUM_DESC matches
1206						WHERE pc.Processed IS NULL 
1207						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
1208			--,UVISATYPE ---SELECT UVISATYPE FROM Bridge_test.dbo.peoplechanges, UVISATYPE ---NULL
1209			case PC.UVISATYPE
1210			when 'B-1' then 'Green Card'
1211			when 'PR' then 'Permanent Resident'
1212			when 'F-1' then 'Student Visa'
1213			else ' ' --null
1214			end as VISA --PC
1215			/*
1216			GM Field		PC Field
1217			B-1	=	Green Card
1218			PR	=	Permanent Resident
1219			F-1	=	Student Visa
1220			*/
1221			FROM Bridge_test.dbo.peoplechanges pc
1222			LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]  /*Lookup Country*/
1223			WHERE        (PC.Processed is Null)
1224				AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
1225        END TRY
1226		BEGIN CATCH
1227		INSERT INTO @LogErrors
1228			(
1229				[Error_Number],
1230				[Error_Message],
1231				[Error_Severity],
1232				[Error_State],
1233				[Error_Procedure],
1234				[Error_Line],
1235				[UserName],
1236				[HostName],
1237				[Time_Stamp]
1238			)
1239				SELECT ISNULL(ERROR_NUMBER(),0),
1240					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
1241					ISNULL(ERROR_SEVERITY(),0),	
1242					ISNULL(ERROR_STATE(),1),	
1243					ISNULL(ERROR_PROCEDURE(),''),			 
1244					ISNULL(ERROR_LINE(), 0), 
1245					SUSER_SNAME(), 
1246					HOST_NAME(), 
1247					GETDATE();
1248		END CATCH
1249
1250
1251		--PEOPLETYPE - Revised
1252		SET @TABLE_NAME = 'PEOPLETYPE'
1253		BEGIN TRY
1254		INSERT INTO powercampustest.dbo.PEOPLETYPE
1255		(
1256			PEOPLE_CODE,
1257			PEOPLE_ID,
1258			PEOPLE_CODE_ID,
1259			PEOPLE_TYPE,
1260			CREATE_DATE,
1261			CREATE_TIME,
1262			CREATE_OPID,
1263			CREATE_TERMINAL,
1264			REVISION_DATE,
1265			REVISION_TIME,
1266			REVISION_OPID,
1267			REVISION_TERMINAL,
1268			ABT_JOIN
1269		)
1270		SELECT TOP 1
1271			PC.PEOPLE_CODE,
1272			PC.PEOPLE_ID,
1273			PC.PEOPLE_CODE_ID,
1274			'APP', ---check PEOPLE_TYPE
1275			COALESCE(PC.CREATE_Date, GetDate()),
1276			COALESCE(PC.CREATE_TIME, GetDate()),
1277			COALESCE(PC.Create_Opid, N' '),
1278			PC.CREATE_TERMINAL,
1279			COALESCE(PC.Revision_Date, GetDate()),
1280			COALESCE(PC.Revision_TIME, GetDate()),
1281			COALESCE(PC.Revision_Opid, N' '),
1282			PC.REVISION_TERMINAL,
1283			'*'
1284		FROM  Bridge_test.dbo.PeopleChanges PC
1285		WHERE (PC.Processed is Null)
1286		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
1287        END TRY
1288		BEGIN CATCH
1289		INSERT INTO @LogErrors
1290			(
1291				[Error_Number],
1292				[Error_Message],
1293				[Error_Severity],
1294				[Error_State],
1295				[Error_Procedure],
1296				[Error_Line],
1297				[UserName],
1298				[HostName],
1299				[Time_Stamp]
1300			)
1301				SELECT ISNULL(ERROR_NUMBER(),0),
1302					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
1303					ISNULL(ERROR_SEVERITY(),0),	
1304					ISNULL(ERROR_STATE(),1),	
1305					ISNULL(ERROR_PROCEDURE(),''),			 
1306					ISNULL(ERROR_LINE(), 0), 
1307					SUSER_SNAME(), 
1308					HOST_NAME(), 
1309					GETDATE();
1310		END CATCH
1311
1312		--RESIDENCY -- Revised
1313		SET @TABLE_NAME = 'RESIDENCY'
1314		BEGIN TRY
1315		DECLARE @CNT_RESIDENCY INT = 0
1316		SELECT @CNT_RESIDENCY = COUNT(1) FROM powercampustest.dbo.RESIDENCY WHERE PEOPLE_ID IN 
1317		(SELECT PC.PEOPLE_ID  FROM  Bridge_test.dbo.PeopleChanges PC
1318		WHERE (PC.Processed is Null)
1319		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' 
1320		AND pc.sequence_no = tmp.sequence_no))
1321		
1322		--DELETE FROM powercampustest.dbo.[STOPLIST] WHERE PEOPLE_ID = '000095052'
1323		
1324		IF @CNT_RESIDENCY > 0 
1325		BEGIN
1326			DELETE FROM powercampustest.dbo.RESIDENCY WHERE PEOPLE_ID IN 
1327			(SELECT PC.PEOPLE_ID  FROM  Bridge_test.dbo.PeopleChanges PC
1328			WHERE (PC.Processed is Null)
1329			AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' 
1330			AND pc.sequence_no = tmp.sequence_no))
1331		END
1332		
1333		INSERT INTO powercampustest.dbo.RESIDENCY
1334		(
1335			PEOPLE_CODE,
1336			PEOPLE_ID,
1337			PEOPLE_CODE_ID,
1338			ACADEMIC_YEAR,
1339			ACADEMIC_TERM,
1340			ACADEMIC_SESSION,
1341			RESIDENT_COMMUTER,
1342			FOOD_PLAN,
1343			DORM_PLAN,
1344			DORM_CAMPUS,
1345			DORM_BUILDING,
1346			DORM_ROOM,
1347			DORM_KEY,
1348			LOCKER,
1349			MAIL_SLOT,
1350			CREATE_DATE,
1351			CREATE_TIME,
1352			CREATE_OPID,
1353			CREATE_TERMINAL,
1354			REVISION_DATE,
1355			REVISION_TIME,
1356			REVISION_OPID,
1357			REVISION_TERMINAL,
1358			ABT_JOIN,
1359			LAST_ACTIVITY,
1360			CURRENT_ACTIVITY
1361		)
1362		SELECT TOP 1
1363		PC.PEOPLE_CODE,
1364		PC.PEOPLE_ID,
1365		PC.PEOPLE_CODE_ID,
1366		COALESCE(LEFT(PC.KEY2,CHARINDEX(' ',PC.KEY2)-1), N' '), --academic_year
1367		COALESCE(RIGHT(PC.KEY2,LEN(PC.KEY2)-CHARINDEX(' ',PC.KEY2)), N' '), --academic_term
1368		COALESCE(PC.KEY5, N' '), --ACADEMIC_SESSION
1369		COALESCE(N' ', N' '),
1370		COALESCE(N' ', N' '),
1371		COALESCE(N' ', N' '),
1372		COALESCE(N' ', N' '),
1373		COALESCE(N' ', N' '),
1374		COALESCE(N' ', N' '),
1375		COALESCE(N' ', N' '),
1376		COALESCE(N' ', N' '),
1377		COALESCE(N' ', N' '),
1378		COALESCE(PC.CREATE_Date, GetDate()),
1379		COALESCE(PC.CREATE_TIME, GetDate()),
1380		COALESCE(PC.Create_Opid, N' '),
1381		PC.CREATE_TERMINAL,
1382		COALESCE(PC.Revision_Date, GetDate()),
1383		COALESCE(PC.Revision_TIME, GetDate()),
1384		COALESCE(PC.Revision_Opid, N' '),
1385		PC.REVISION_TERMINAL,
1386		'*',
1387		'Y',
1388		'N'
1389		FROM Bridge_test.dbo.PeopleChanges PC
1390		WHERE (PC.Processed is Null)
1391		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
1392        END TRY
1393		BEGIN CATCH
1394		INSERT INTO @LogErrors
1395			(
1396				[Error_Number],
1397				[Error_Message],
1398				[Error_Severity],
1399				[Error_State],
1400				[Error_Procedure],
1401				[Error_Line],
1402				[UserName],
1403				[HostName],
1404				[Time_Stamp]
1405			)
1406				SELECT ISNULL(ERROR_NUMBER(),0),
1407					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
1408					ISNULL(ERROR_SEVERITY(),0),	
1409					ISNULL(ERROR_STATE(),1),	
1410					ISNULL(ERROR_PROCEDURE(),''),			 
1411					ISNULL(ERROR_LINE(), 0), 
1412					SUSER_SNAME(), 
1413					HOST_NAME(), 
1414					GETDATE();
1415		END CATCH
1416
1417		--ACADEMIC - Revised
1418		/*
1419		SET @TABLE_NAME = 'ACADEMIC'
1420		BEGIN TRY 
1421		INSERT INTO powercampustest.dbo.ACADEMIC 
1422		(
1423			PEOPLE_CODE
1424			,PEOPLE_ID
1425			,PEOPLE_CODE_ID
1426			,academic_year
1427			, academic_term
1428			, academic_session
1429			/*
1430			AGS	=	AGS	
1431			TRD	=	Traditional	
1432			Key3		academic_program	
1433			*/
1434			, program
1435			/*
1436			FF-AGS, FF-TRD, TR-AGS, CH-TRD, CO-TRD, RE-TRD, TR-TRD, WDVW	=	Undergraduate
1437			CROSS	=	CROSS
1438			GR-AGS	=	Graduate
1439			ND-AGS, ND-TRD	=	Non Degree Seeking
1440			*/
1441			, degree
1442			/*
1443			AAS	=	Associate of Ap Sci
1444			AA	=	Associate of Arts
1445			BA	=	BA
1446			BS	=	BS
1447			BSN	=	BSN
1448			CON	=	Concurrent
1449			HS	=	High School Diploma
1450			2ND	=	Major 2
1451			MAS	=	Masters
1452			MIN	=	Minor
1453			NON	=	Non Degree Seeking
1454			RND	=	RN Diploma
1455			RLV	=	RN License Verifictn
1456			UND	=	Undeclared
1457			*/
1458			, curriculum
1459			/*
1460			ACM	=	Accounting
1461			BSBA, M B A, MBAF, MBAH, MBAM, MBAN	=	AGS  Business Admin
1462			AASCM	=	AGS Christian Mnstry
1463			AACS	=	AGS Christian Std
1464			CROSS	=	AGS CROSS
1465			M & L	=	AGS Min & Leadshp
1466			B&TS	=	Biblical & Theol Std
1467			BICH	=	Biochemistry
1468			BIO	=	Biology
1469			CHEM	=	Chemistry
1470			COM	=	Communication Arts
1471			CJ	=	Criminal Justice
1472			AEC, EDEC	=	Early Child Educ
1473			EDES	=	Elementary Education
1474			ENCM	=	Eng Cmpsition MINOR
1475			ENA	=	English
1476			ECLM	=	English CmpLit MINOR
1477			ENLM	=	English Literature
1478			EXCS	=	Ex Sc Clinical Fit
1479			EXNS	=	Ex Sc Nonclin Fit
1480			EXPS	=	Ex Sc Pre Therapy
1481			FIEC	=	Financial Economics
1482			STEP	=	Gen Studies AGS
1483			GEAA	=	Gen Studies Trad
1484			GSCI	=	General Science
1485			VOM, GLOB	=	Global Studies
1486			GLSO	=	GLST Ordination
1487			GD&MD	=	GraphDsgnMkgDynmcs
1488			HSCH	=	High School
1489			HIST	=	History
1490			HPS	=	History Political Sc
1491			HRM	=	Hmn Rsrc Mgmnt MINOR
1492			INM	=	Intercul Std MINOR
1493			INS	=	Interdispl Business
1494			MRK	=	Marketing
1495			MED	=	MASTERS Education
1496			MATH	=	Mathematics
1497			MD&S	=	Media Design & Strat
1498			POM	=	MINOR Pol Science
1499			YMM	=	MINOR Youth Ministry
1500			MLDR	=	Mngmnt and Ldrship
1501			PMWM	=	MNR PMWorship Arts
1502			SMNP	=	MNR Soc for NonPsyc
1503			SMP	=	MNR SOC for Psyc Mjr
1504			MUA	=	Music
1505			EDMS	=	Music Education Voca
1506			NOM	=	Nnprft Oprtns MINOR
1507			NBSN	=	Nursing BSN TRAD
1508			NURS	=	Nursing RNBSN AGS
1509			OME	=	Org Mgmt & Ethics
1510			PMBS	=	Pastoral Min Bib Stu
1511			PM&T	=	Pastoral Min BibThSt
1512			PMWA	=	Pastoral Min WA
1513			PMY	=	Pastoral Min Youth
1514			PMIN	=	Pastoral Ministry
1515			HPED	=	Physical Ed K12
1516			PNURS	=	Pre Nursing
1517			PACM	=	Pstrl Counslng MINOR
1518			PSY	=	Psychology
1519			PSYC	=	Psychology Studies
1520			PSYM	=	Psychology AGS MINOR
1521			PSY1	=	Psychology Track One
1522			PSY2	=	Psychology Track Two
1523			ESEA	=	Secondary Ed Eng
1524			ESMS	=	Secondary Ed Math
1525			ESB	=	Secondary Ed Sci
1526			ESSS	=	Secondary Ed Soc St
1527			SSPL	=	Social Stu Pre Law
1528			SSH	=	Social Studies Hist
1529			SPS	=	Sports Ministry
1530			TAAS	=	Technical Programs
1531			BUS	=	Trad Business
1532			UNDC	=	Undeclared
1533			WOA	=	Worship Arts
1534			*/
1535			, college
1536			/*
1537			AGS	=	AGS
1538			TRD	=	Traditional
1539			*/
1540			, department
1541			/*
1542			BUS	=	Business
1543			EDU	=	Education
1544			HUM	=	Humanities
1545			NUR	=	Nursing
1546			R&P	=	Religion and Philo
1547			S&M	=	Science and Math
1548			S&B	=	Social and Behav Sc
1549			*/
1550			, class_level
1551			/*
1552			FRS	=	Freshman
1553			GRD	=	Graduate
1554			HIS	=	High School
1555			JNR	=	Junior
1556			NON	=	Non Degree
1557			CER	=	Certificate
1558			CON	=	Concurrent
1559			SNR	=	Senior
1560			SPH	=	Sophomore
1561			*/
1562			, full_part
1563			/*
1564			F	=	Full Time
1565			1/2	=	Half Time
1566			<1/2	=	Less than Half Time
1567			3/4	=	Three Quarter Time
1568			*/
1569			, nontrad_program
1570			---many missing, need to complete
1571			,[POPULATION]
1572			,[REGISTER_LIMIT]
1573			,[CREDITS]
1574			,[PREREG_VALIDATE]
1575			,[REG_VALIDATE]
1576			,[GRADUATED]
1577			,[ORG_CODE_ID]
1578			,[ACADEMIC_FLAG]
1579			,[PRIMARY_FLAG]
1580			,[APPLICATION_FLAG]
1581			,[ACA_PLAN_SETUP]
1582			,[STATUS]
1583			,[TRANSCRIPT_SEQ]
1584			,[LAST_ACTIVITY]
1585			,[CURRENT_ACTIVITY]
1586			,[INQUIRY_FLAG]
1587			,[PROTECT_COUNSELOR]
1588			,[ENROLLMENT_STATUS_DATE]
1589			,[CREATE_DATE]
1590			,[CREATE_TIME]
1591			,[CREATE_OPID]
1592			,[CREATE_TERMINAL]
1593			,[REVISION_DATE]
1594			,[REVISION_TIME]
1595			,[REVISION_OPID]
1596			,[REVISION_TERMINAL]
1597			,[ABT_JOIN]
1598			,[ADVISOR]
1599			,[COUNSELOR]
1600           
1601		) --nvarchar 4
1602		select TOP 1
1603		PC.PEOPLE_CODE
1604		,PC.PEOPLE_ID
1605		,PC.PEOPLE_CODE_ID
1606		,LEFT(PC.KEY2,CHARINDEX(' ',PC.KEY2)-1)
1607		,RIGHT(PC.KEY2,LEN(PC.KEY2)-CHARINDEX(' ',PC.KEY2))
1608		/*
1609		AGS	= AGS
1610		TRD	= Traditional
1611		*/
1612		, CASE PC.KEY5 
1613			WHEN 'AGS' THEN 'AGS' 
1614			WHEN 'TRD' THEN 'Traditional'
1615		END academic_session
1616
1617		/*
1618		FF-AGS, FF-TRD, TR-AGS, CH-TRD, CO-TRD, RE-TRD, TR-TRD, WDVW	= Undergraduate
1619		CROSS	= CROSS
1620		GR-AGS	= Graduate
1621		ND-AGS, ND-TRD	= Non Degree Seeking
1622		*/
1623		, CASE
1624			WHEN PC.KEY3 IN ('FF-AGS', 'FF-TRD', 'TR-AGS', 'CH-TRD', 'CO-TRD', 'RE-TRD', 'TR-TRD', 'WDVW') THEN 'Undergraduate'
1625			WHEN PC.KEY3 = 'CROSS' THEN 'CROSS'
1626			WHEN PC.KEY3 = 'GR-AGS' THEN 'Graduate'
1627			WHEN PC.KEY3 IN ('ND-AGS', 'ND-TRD') THEN 'Non Degree Seeking'
1628		END program
1629		/*
1630		AAS	Associate of Ap Sci
1631		AA	Associate of Arts
1632		BA	BA
1633		BS	BS
1634		BSN	BSN
1635		CON	Concurrent
1636		HS	High School Diploma
1637		2ND	Major 2
1638		MAS	Masters
1639		MIN	Minor
1640		NON	Non Degree Seeking
1641		RND	RN Diploma
1642		RLV	RN License Verifictn
1643		UND	Undeclared
1644		*/
1645		, CASE
1646			WHEN PC.UCURRICULM = 'AAS' THEN	'Associate of Ap Sci'
1647			WHEN PC.UCURRICULM = 'AA' THEN	'Associate of Arts'
1648			WHEN PC.UCURRICULM = 'BA' THEN	'BA'
1649			WHEN PC.UCURRICULM = 'BS' THEN	'BS'
1650			WHEN PC.UCURRICULM = 'BSN' THEN	'BSN'
1651			WHEN PC.UCURRICULM = 'CON' THEN	'Concurrent'
1652			WHEN PC.UCURRICULM = 'HS' THEN	'High School Diploma'
1653			WHEN PC.UCURRICULM = '2ND' THEN	'Major 2'
1654			WHEN PC.UCURRICULM = 'MAS' THEN	'Masters'
1655			WHEN PC.UCURRICULM = 'MIN' THEN	'Minor'
1656			WHEN PC.UCURRICULM = 'NON' THEN	'Non Degree Seeking'
1657			WHEN PC.UCURRICULM = 'RND' THEN	'RN Diploma'
1658			WHEN PC.UCURRICULM = 'RLV' THEN	'RN License Verifictn'
1659			WHEN PC.UCURRICULM = 'UND' THEN	'Undeclared'
1660		END degree
1661		/*
1662		ACM	Accounting
1663		BSBA, M B A, MBAF, MBAH, MBAM, MBAN	AGS  Business Admin
1664		AASCM	AGS Christian Mnstry
1665		AACS	AGS Christian Std
1666		CROSS	AGS CROSS
1667		M & L	AGS Min & Leadshp
1668		B&TS	Biblical & Theol Std
1669		BICH	Biochemistry
1670		BIO	Biology
1671		CHEM	Chemistry
1672		COM	Communication Arts
1673		CJ	Criminal Justice
1674		AEC, EDEC	Early Child Educ
1675		EDES	Elementary Education
1676		ENCM	Eng Cmpsition MINOR
1677		ENA	English
1678		ECLM	English CmpLit MINOR
1679		ENLM	English Literature
1680		EXCS	Ex Sc Clinical Fit
1681		EXNS	Ex Sc Nonclin Fit
1682		EXPS	Ex Sc Pre Therapy
1683		FIEC	Financial Economics
1684		STEP	Gen Studies AGS
1685		GEAA	Gen Studies Trad
1686		GSCI	General Science
1687		VOM, GLOB	Global Studies
1688		GLSO	GLST Ordination
1689		GD&MD	GraphDsgnMkgDynmcs
1690		HSCH	High School
1691		HIST	History
1692		HPS	History Political Sc
1693		HRM	Hmn Rsrc Mgmnt MINOR
1694		INM	Intercul Std MINOR
1695		INS	Interdispl Business
1696		MRK	Marketing
1697		MED	MASTERS Education
1698		MATH	Mathematics
1699		MD&S	Media Design & Strat
1700		POM	MINOR Pol Science
1701		YMM	MINOR Youth Ministry
1702		MLDR	Mngmnt and Ldrship
1703		PMWM	MNR PMWorship Arts
1704		SMNP	MNR Soc for NonPsyc
1705		SMP	MNR SOC for Psyc Mjr
1706		MUA	Music
1707		EDMS	Music Education Voca
1708		NOM	Nnprft Oprtns MINOR
1709		NBSN	Nursing BSN TRAD
1710		NURS	Nursing RNBSN AGS
1711		OME	Org Mgmt & Ethics
1712		PMBS	Pastoral Min Bib Stu
1713		PM&T	Pastoral Min BibThSt
1714		PMWA	Pastoral Min WA
1715		PMY	Pastoral Min Youth
1716		PMIN	Pastoral Ministry
1717		HPED	Physical Ed K12
1718		PNURS	Pre Nursing
1719		PACM	Pstrl Counslng MINOR
1720		PSY	Psychology
1721		PSYC	Psychology Studies
1722		PSYM	Psychology AGS MINOR
1723		PSY1	Psychology Track One
1724		PSY2	Psychology Track Two
1725		ESEA	Secondary Ed Eng
1726		ESMS	Secondary Ed Math
1727		ESB	Secondary Ed Sci
1728		ESSS	Secondary Ed Soc St
1729		SSPL	Social Stu Pre Law
1730		SSH	Social Studies Hist
1731		SPS	Sports Ministry
1732		TAAS	Technical Programs
1733		BUS	Trad Business
1734		UNDC	Undeclared
1735		WOA	Worship Arts
1736		*/
1737		, 
1738		(SELECT TOP 1 ISNULL(CC.CODE_VALUE_KEY, '') FROM Bridge_test.dbo.peoplechanges pc 
1739						LEFT JOIN [powercampustest].[dbo].[CODE_CURRICULUM] CC ON CC.[CODE_VALUE] = PC.[UMAJOR]
1740						WHERE pc.Processed IS NULL 
1741						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
1742		/*CASE
1743			WHEN PC.UMAJOR = 'ACM' THEN 'Accounting'
1744			WHEN PC.UMAJOR IN ('BSBA','M B A', 'MBAF', 'MBAH', 'MBAM', 'MBAN', 'AGS') THEN 'Business Admin'
1745			WHEN PC.UMAJOR = 'AASCM' THEN 'AGS Christian Mnstry'
1746			WHEN PC.UMAJOR = 'AACS' THEN 'AGS Christian Std'
1747			WHEN PC.UMAJOR = 'CROSS' THEN 'AGS CROSS'
1748			WHEN PC.UMAJOR = 'M & L' THEN 'AGS Min & Leadshp'
1749			WHEN PC.UMAJOR = 'B&TS' THEN 'Biblical & Theol Std'
1750			WHEN PC.UMAJOR = 'BICH' THEN 'Biochemistry'
1751			WHEN PC.UMAJOR = 'BIO' THEN 'Biology'
1752			WHEN PC.UMAJOR = 'CHEM' THEN 'Chemistry'
1753			WHEN PC.UMAJOR = 'COM' THEN 'Communication Arts'
1754			WHEN PC.UMAJOR = 'CJ' THEN 'Criminal Justice'
1755			WHEN PC.UMAJOR IN ('AEC', 'EDEC') THEN 'Early Child Educ'
1756			WHEN PC.UMAJOR = 'EDES' THEN 'Elementary Education'
1757			WHEN PC.UMAJOR = 'ENCM' THEN 'Eng Cmpsition MINOR'
1758			WHEN PC.UMAJOR = 'ENA' THEN 'English'
1759			WHEN PC.UMAJOR = 'ECLM' THEN 'English CmpLit MINOR'
1760			WHEN PC.UMAJOR = 'ENLM' THEN 'English Literature'
1761			WHEN PC.UMAJOR = 'EXCS' THEN 'Ex Sc Clinical Fit'
1762			WHEN PC.UMAJOR = 'EXNS' THEN 'Ex Sc Nonclin Fit'
1763			WHEN PC.UMAJOR = 'EXPS' THEN 'Ex Sc Pre Therapy'
1764			WHEN PC.UMAJOR = 'FIEC' THEN 'Financial Economics'
1765			WHEN PC.UMAJOR = 'STEP' THEN 'Gen Studies AGS'
1766			WHEN PC.UMAJOR = 'GEAA' THEN 'Gen Studies Trad'
1767			WHEN PC.UMAJOR = 'GSCI' THEN 'General Science'
1768			WHEN PC.UMAJOR IN ('VOM', 'GLOB') THEN 'Global Studies'
1769			WHEN PC.UMAJOR = 'GLSO' THEN 'GLST Ordination'
1770			WHEN PC.UMAJOR = 'GD&MD' THEN 'GraphDsgnMkgDynmcs'
1771			WHEN PC.UMAJOR = 'HSCH' THEN 'High School'
1772			WHEN PC.UMAJOR = 'HIST' THEN 'History'
1773			WHEN PC.UMAJOR = 'HPS' THEN 'History Political Sc'
1774			WHEN PC.UMAJOR = 'HRM' THEN 'Hmn Rsrc Mgmnt MINOR'
1775			WHEN PC.UMAJOR = 'INM' THEN 'Intercul Std MINOR'
1776			WHEN PC.UMAJOR = 'INS' THEN 'Interdispl Business'
1777			WHEN PC.UMAJOR = 'MRK' THEN 'Marketing'
1778			WHEN PC.UMAJOR = 'MED' THEN 'MASTERS Education'
1779			WHEN PC.UMAJOR = 'MATH' THEN 'Mathematics'
1780			WHEN PC.UMAJOR = 'MD&S' THEN 'Media Design & Strat'
1781			WHEN PC.UMAJOR = 'POM' THEN 'MINOR Pol Science'
1782			WHEN PC.UMAJOR = 'YMM' THEN 'MINOR Youth Ministry'
1783			WHEN PC.UMAJOR = 'MLDR' THEN 'Mngmnt and Ldrship'
1784			WHEN PC.UMAJOR = 'PMWM' THEN 'MNR PMWorship Arts'
1785			WHEN PC.UMAJOR = 'SMNP' THEN 'MNR Soc for NonPsyc'
1786			WHEN PC.UMAJOR = 'SMP' THEN 'MNR SOC for Psyc Mjr'
1787			WHEN PC.UMAJOR = 'MUA' THEN 'Music'
1788			WHEN PC.UMAJOR = 'EDMS' THEN 'Music Education Voca'
1789			WHEN PC.UMAJOR = 'NOM' THEN 'Nnprft Oprtns MINOR'
1790			WHEN PC.UMAJOR = 'NBSN' THEN 'Nursing BSN TRAD'
1791			WHEN PC.UMAJOR = 'NURS' THEN 'Nursing RNBSN AGS'
1792			WHEN PC.UMAJOR = 'OME' THEN 'Org Mgmt & Ethics'
1793			WHEN PC.UMAJOR = 'PMBS' THEN 'Pastoral Min Bib Stu'
1794			WHEN PC.UMAJOR = 'PM&T' THEN 'Pastoral Min BibThSt'
1795			WHEN PC.UMAJOR = 'PMWA' THEN 'Pastoral Min WA'
1796			WHEN PC.UMAJOR = 'PMY' THEN 'Pastoral Min Youth'
1797			WHEN PC.UMAJOR = 'PMIN' THEN 'Pastoral Ministry'
1798			WHEN PC.UMAJOR = 'HPED' THEN 'Physical Ed K12'
1799			WHEN PC.UMAJOR = 'PNURS' THEN 'Pre Nursing'
1800			WHEN PC.UMAJOR = 'PACM' THEN 'Pstrl Counslng MINOR'
1801			WHEN PC.UMAJOR = 'PSY' THEN 'Psychology'
1802			WHEN PC.UMAJOR = 'PSYC' THEN 'Psychology Studies'
1803			WHEN PC.UMAJOR = 'PSYM' THEN 'Psychology AGS MINOR'
1804			WHEN PC.UMAJOR = 'PSY1' THEN 'Psychology Track One'
1805			WHEN PC.UMAJOR = 'PSY2' THEN 'Psychology Track Two'
1806			WHEN PC.UMAJOR = 'ESEA' THEN 'Secondary Ed Eng'
1807			WHEN PC.UMAJOR = 'ESMS' THEN 'Secondary Ed Math'
1808			WHEN PC.UMAJOR = 'ESB' THEN 'Secondary Ed Sci'
1809			WHEN PC.UMAJOR = 'ESSS' THEN 'Secondary Ed Soc St'
1810			WHEN PC.UMAJOR = 'SSPL' THEN 'Social Stu Pre Law'
1811			WHEN PC.UMAJOR = 'SSH' THEN 'Social Studies Hist'
1812			WHEN PC.UMAJOR = 'SPS' THEN 'Sports Ministry'
1813			WHEN PC.UMAJOR = 'TAAS' THEN 'Technical Programs'
1814			WHEN PC.UMAJOR = 'BUS' THEN 'Trad Business'
1815			WHEN PC.UMAJOR = 'UNDC' THEN 'Undeclared'
1816			WHEN PC.UMAJOR = 'WOA' THEN 	'Worship Arts'
1817		END curriculum, */
1818		CASE PC.KEY5 
1819			WHEN 'AGS' THEN 'AGS' 
1820			WHEN 'TRD' THEN 'Traditional'
1821		END college
1822		/*
1823		BUS	Business
1824		EDU	Education
1825		HUM	Humanities
1826		NUR	Nursing
1827		R&P	Religion and Philo
1828		S&M	Science and Math
1829		S&B	Social and Behav Sc
1830		*/
1831		,
1832		CASE 
1833			WHEN PC.DEPARTMENT ='BUS' THEN 'Business'
1834			WHEN PC.DEPARTMENT ='EDU' THEN 'Education'
1835			WHEN PC.DEPARTMENT ='HUM' THEN 'Humanities'
1836			WHEN PC.DEPARTMENT ='NUR' THEN 'Nursing'
1837			WHEN PC.DEPARTMENT ='R&P' THEN 'Religion and Philo'
1838			WHEN PC.DEPARTMENT ='S&M' THEN 'Science and Math'
1839			WHEN PC.DEPARTMENT ='S&B' THEN 'Social and Behav Sc'
1840		END department
1841		/*
1842		FRS	Freshman
1843		GRD	Graduate
1844		HIS	High School
1845		JNR	Junior
1846		NON	Non Degree
1847		CER	Certificate
1848		CON	Concurrent
1849		SNR	Senior
1850		SPH	Sophomore
1851		*/
1852		,CASE
1853			WHEN PC.UCOLLCMPLT = 'FRS' THEN 'Freshman'
1854			WHEN PC.UCOLLCMPLT = 'GRD' THEN 'Graduate'
1855			WHEN PC.UCOLLCMPLT = 'HIS' THEN 'High School'
1856			WHEN PC.UCOLLCMPLT = 'JNR' THEN 'Junior'
1857			WHEN PC.UCOLLCMPLT = 'NON' THEN 'Non Degree'
1858			WHEN PC.UCOLLCMPLT = 'CER' THEN 'Certificate'
1859			WHEN PC.UCOLLCMPLT = 'CON' THEN 'Concurrent'
1860			WHEN PC.UCOLLCMPLT = 'SNR' THEN 'Senior'
1861			WHEN PC.UCOLLCMPLT = 'SPH' THEN 'Sophomore'
1862		END class_level
1863
1864		/*
1865		F	Full Time
1866		1/2	Half Time
1867		<1/2	Less than Half Time
1868		3/4	Three Quarter Time
1869		*/
1870		,CASE 
1871			WHEN PC.Ufullpart = 'F' THEN 'Full Time'
1872			WHEN PC.Ufullpart = '1/2' THEN 'Half Time'
1873			WHEN PC.Ufullpart = '<1/2' THEN 'Less than Half Time'
1874			WHEN PC.Ufullpart = '3/4' THEN 'Three Quarter Time'
1875		END full_part
1876		,COALESCE(PC.Ucohort, N' ') 
1877		, COALESCE( N' ', N' ') 
1878		, COALESCE( 1, 1)  --REGISTER_LIMIT
1879		, COALESCE( 1, 1) 
1880		, COALESCE( N' ', N' ') 
1881		, COALESCE( N' ', N' ') 
1882		, COALESCE( N' ', N' ') 
1883		, COALESCE( N' ', N' ') 
1884		, COALESCE( N' ', N' ') 
1885		, COALESCE( N' ', N' ') 
1886		, COALESCE( 1, 1) 
1887		, COALESCE( 1, 1)
1888		, COALESCE(  1, 1)  --CREDITS
1889		, COALESCE( N' ', N' ') 
1890		, COALESCE( N' ', N' ') 
1891		, COALESCE( N' ', N' ') 
1892		, COALESCE( N' ', N' ') 
1893		, COALESCE( N' ', N' ') 
1894		, COALESCE( N' ', N' ') 
1895		, COALESCE(PC.CREATE_DATE, GETDATE()) --date format, 2006-07-26 00:00:00.000
1896		, COALESCE(PC.CREATE_TIME, GetDate()) --time format, 1900-01-01 08:39:02.000
1897		, COALESCE(PC.CREATE_OPID, N' ') 
1898		, COALESCE(PC.CREATE_TERMINAL, N' ') 
1899		--COALESCE(REVISION_DATE, (SELECT DATEADD(dd, DATEDIFF(dd, 0, GETDATE()),0)) x)), --needs only date
1900		, COALESCE(PC.REVISION_DATE, GETDATE()) --date format, 2006-07-26 00:00:00.000
1901		, COALESCE(PC.REVISION_TIME, GetDate()) --time format, 1900-01-01 08:39:02.000
1902		, COALESCE(PC.REVISION_OPID, N' ') 
1903		, COALESCE(PC.REVISION_TERMINAL, N' ') 
1904		, COALESCE(PC.ABT_JOIN, N' ')
1905		/*
1906		Dan Wimberly	000-00-0024
1907		Curtis Williams	000-00-7954
1908		Kyle White	000-00-0039
1909		Mark Weeter	000-00-0038
1910		Wendel Weaver	000-00-0046
1911		Angela Watson	000-08-0511
1912		William Vieux	000-06-1761
1913		Evert Van Kuiken	000-06-4804
1914		Brian Turner	000-00-0028
1915		Rosimar Truitt	000-07-7556
1916		Travis Sutton	000-07-5416
1917		Jonathan Stewart	000-07-1101
1918		Barbara Schmidt	000-05-3016
1919		Keith Scanlon	000-04-1404
1920		Lisa Riggs	000-02-8801
1921		Cynthia Riffe	000-00-0323
1922		Gail Richardson	000-00-0117
1923		Brenda Raleigh	000-00-0076
1924		Douglas Quick	000-00-0011
1925		Matthew Parker	000-08-0513
1926		Mark Molder	000-04-8209
1927		Joshua McNall	000-05-0157
1928		Kenneth LeVan	000-08-2148
1929		Patricia Leggett	000-05-2244
1930		Rebecca Le	000-00-7956
1931		Kirk Kelley	000-07-0505
1932		Dariusz Jarmola	000-00-0027
1933		Kirk Jackson	000-07-9570
1934		Stephen Hughes	000-00-0009
1935		Sheryl Hill	000-07-6649
1936		Beverly Hartter	000-05-4748
1937		Kerri Hampton	000-05-7578
1938		J. Michael Fullingim	000-00-0015
1939		Nancy Eckerd	000-07-6210
1940		Charissa Dunn	000-00-0008
1941		Amanda Dickinson	000-00-5447
1942		Richard Daake	000-00-0012
1943		David Cochran	000-00-0026
1944		Sheldon Buxton	000-07-6612
1945		Robert Bonner	000-00-0048
1946		Jason Beschta	000-08-0512
1947		Abbie Bailey	000-00-0155
1948		Danielle Appeldorn	000-05-5325
1949		Brett Andrews	000-00-0019
1950		Amanda Alonzo	000-05-2841
1951		*/
1952		, CASE UPPER(COALESCE(PC.Uadvismntr, N' '))
1953			WHEN UPPER('Dan Wimberly') THEN 'P000000024'
1954			WHEN UPPER('Curtis Williams') THEN 'P000007954'
1955			WHEN UPPER('Kyle White') THEN 'P000000039'
1956			WHEN UPPER('Mark Weeter') THEN 'P000000038'
1957			WHEN UPPER('Wendel Weaver') THEN 'P000000046'
1958			WHEN UPPER('Angela Watson') THEN 'P000080511'
1959			WHEN UPPER('William Vieux') THEN 'P000061761'
1960			WHEN UPPER('Evert Van Kuiken') THEN 'P000064804'
1961			WHEN UPPER('Brian Turner') THEN 'P000000028'
1962			WHEN UPPER('Rosimar Truitt') THEN 'P000077556'
1963			WHEN UPPER('Travis Sutton') THEN 'P000075416'
1964			WHEN UPPER('Jonathan Stewart') THEN 'P000071101'
1965			WHEN UPPER('Barbara Schmidt') THEN 'P000053016'
1966			WHEN UPPER('Keith Scanlon') THEN 'P000041404'
1967			WHEN UPPER('Lisa Riggs') THEN 'P000028801'
1968			WHEN UPPER('Cynthia Riffe') THEN 'P000000323'
1969			WHEN UPPER('Gail Richardson') THEN 'P000000117'
1970			WHEN UPPER('Brenda Raleigh') THEN 'P000000076'
1971			WHEN UPPER('Douglas Quick') THEN 'P000000011'
1972			WHEN UPPER('Matthew Parker') THEN 'P000080513'
1973			WHEN UPPER('Mark Molder') THEN 'P000048209'
1974			WHEN UPPER('Joshua McNall') THEN 'P000050157'
1975			WHEN UPPER('Kenneth LeVan') THEN 'P000082148'
1976			WHEN UPPER('Patricia Leggett') THEN 'P000052244'
1977			WHEN UPPER('Rebecca Le') THEN 'P000007956'
1978			WHEN UPPER('Kirk Kelley') THEN 'P000070505'
1979			WHEN UPPER('Dariusz Jarmola') THEN 'P000000027'
1980			WHEN UPPER('Kirk Jackson') THEN 'P000079570'
1981			WHEN UPPER('Stephen Hughes') THEN 'P000000009'
1982			WHEN UPPER('Sheryl Hill') THEN 'P000076649'
1983			WHEN UPPER('Beverly Hartter') THEN 'P000054748'
1984			WHEN UPPER('Kerri Hampton') THEN 'P000057578'
1985			WHEN UPPER('J. Michael Fullingim') THEN 'P000000015'
1986			WHEN UPPER('Nancy Eckerd') THEN 'P000076210'
1987			WHEN UPPER('Charissa Dunn') THEN 'P000000008'
1988			WHEN UPPER('Amanda Dickinson') THEN 'P000005447'
1989			WHEN UPPER('Richard Daake') THEN 'P000000012'
1990			WHEN UPPER('David Cochran') THEN 'P000000026'
1991			WHEN UPPER('Sheldon Buxton') THEN 'P000076612'
1992			WHEN UPPER('Robert Bonner') THEN 'P000000048'
1993			WHEN UPPER('Jason Beschta') THEN 'P000080512'
1994			WHEN UPPER('Abbie Bailey') THEN 'P000000155'
1995			WHEN UPPER('Danielle Appeldorn') THEN 'P000055325'
1996			WHEN UPPER('Brett Andrews') THEN 'P000000019'
1997			WHEN UPPER('Amanda Alonzo') THEN 'P000052841'
1998			END
1999		/*
2000		JENNIFERW	000-00-7691
2001		DANNYV	000-07-1345
2002		TJT	000-00-7936
2003		COURTNEYS	000-01-2724
2004		CAMIES	000-08-4158
2005		EDWINR	000-03-9820
2006		SAMP	000-00-7515
2007		JAMESP	000-00-7590
2008		NATALIEN	000-00-5638
2009		MARKM	000-04-8209
2010		JOHNM	000-07-3408
2011		JANETL	000-04-9016
2012		SARAHK	000-00-6432
2013		NICOLEJ	000-07-3408
2014		MIKEH	000-08-4210
2015		PATTIG	000-07-1311
2016		BRITTANYD	000-00-7978
2017		TRENTOND	000-00-2874
2018		JEREMYD	000-07-0564
2019		*/
2020		, CASE UPPER(COALESCE(PC.Uadmcouncd, N' ')) 
2021			WHEN 'JENNIFERW' THEN 'P000007691'
2022			WHEN 'DANNYV' THEN 'P000071345'
2023			WHEN 'TJT' THEN 'P000007936'
2024			WHEN 'COURTNEYS' THEN 'P000012724'
2025			WHEN 'CAMIES' THEN 'P000084158'
2026			WHEN 'EDWINR' THEN 'P000039820'
2027			WHEN 'SAMP' THEN 'P000007515'
2028			WHEN 'JAMESP' THEN 'P000007590'
2029			WHEN 'NATALIEN' THEN 'P000005638'
2030			WHEN 'MARKM' THEN 'P000048209'
2031			WHEN 'JOHNM' THEN 'P000073408'
2032			WHEN 'JANETL' THEN 'P000049016'
2033			WHEN 'SARAHK' THEN 'P000006432'
2034			WHEN 'NICOLEJ' THEN 'P000073408'
2035			WHEN 'MIKEH' THEN 'P000084210'
2036			WHEN 'PATTIG' THEN 'P000071311'
2037			WHEN 'BRITTANYD' THEN 'P000007978'
2038			WHEN 'TRENTOND' THEN 'P000002874'
2039			WHEN 'JEREMYD' THEN 'P000070564 '
2040		END 
2041		FROM   Bridge_test.dbo.PeopleChanges PC
2042		WHERE  (PC.Processed is Null)
2043		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no);
2044        END TRY
2045		BEGIN CATCH
2046		INSERT INTO @LogErrors
2047			(
2048				[Error_Number],
2049				[Error_Message],
2050				[Error_Severity],
2051				[Error_State],
2052				[Error_Procedure],
2053				[Error_Line],
2054				[UserName],
2055				[HostName],
2056				[Time_Stamp]
2057			)
2058				SELECT ISNULL(ERROR_NUMBER(),0),
2059					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2060					ISNULL(ERROR_SEVERITY(),0),	
2061					ISNULL(ERROR_STATE(),1),	
2062					ISNULL(ERROR_PROCEDURE(),''),			 
2063					ISNULL(ERROR_LINE(), 0), 
2064					SUSER_SNAME(), 
2065					HOST_NAME(), 
2066					GETDATE();
2067		END CATCH
2068		*/
2069		--Send to KEY4, U_KEY4 the new PEOPLE_ID
2070		SET @TABLE_NAME = 'CONTACT1 - Step Send new People to GoldMine (KEY4, U_UKEY4) - '
2071		BEGIN TRY 
2072			UPDATE GoldMine_test.dbo.CONTACT1
2073			SET KEY4 = @PEOPLE_ID, U_KEY4 = @PEOPLE_ID
2074			WHERE ACCOUNTNO = @PC_ACCOUNT_NO
2075		END TRY
2076		BEGIN CATCH
2077		INSERT INTO @LogErrors
2078			(
2079				[Error_Number],
2080				[Error_Message],
2081				[Error_Severity],
2082				[Error_State],
2083				[Error_Procedure],
2084				[Error_Line],
2085				[UserName],
2086				[HostName],
2087				[Time_Stamp]
2088			)
2089				SELECT ISNULL(ERROR_NUMBER(),0),
2090					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2091					ISNULL(ERROR_SEVERITY(),0),	
2092					ISNULL(ERROR_STATE(),1),	
2093					ISNULL(ERROR_PROCEDURE(),''),			 
2094					ISNULL(ERROR_LINE(), 0), 
2095					SUSER_SNAME(), 
2096					HOST_NAME(), 
2097					GETDATE();
2098		END CATCH
2099	END
2100	;
2101
2102--	IF TRUE EXISTS RECORD THEN UPDATE PowerCampus
2103	IF (SELECT COUNT(1) FROM #TMP_PEOPLE_ACTION WHERE ACT = 'U') > 0 
2104	BEGIN TRY
2105		--PEOPLE - Revised - OK
2106		SET @TABLE_NAME = 'PEOPLE'
2107		BEGIN TRY
2108		UPDATE c
2109			SET	   c.PEOPLE_CODE = pc.PEOPLE_CODE,
2110				   --c.PEOPLE_CODE_ID = pc.PEOPLE_CODE_ID,
2111				   c.FIRST_NAME = pc.FIRST_NAME,
2112				   c.MIDDLE_NAME = pc.MIDDLE_NAME,
2113				   c.LAST_NAME = pc.LAST_NAME,
2114				   c.BIRTH_DATE = pc.BIRTH_DATE,   ---added 1/18/2018
2115				   c.CREATE_DATE = pc.CREATE_DATE,
2116				   c.CREATE_TIME = pc.CREATE_TIME,
2117				   c.PREFERRED_ADD = pc.PREFERRED_ADD,
2118				   c.DECEASED_FLAG = pc.DECEASED_FLAG,
2119				   c.CREATE_OPID = pc.CREATE_OPID,
2120				   c.CREATE_TERMINAL = pc.CREATE_TERMINAL,
2121				   c.REVISION_DATE = pc.REVISION_DATE,
2122				   c.REVISION_TIME = pc.REVISION_TIME,
2123				   c.REVISION_OPID = pc.REVISION_OPID,
2124				   c.REVISION_TERMINAL = pc.REVISION_TERMINAL,
2125				   c.ABT_JOIN = pc.ABT_JOIN
2126		FROM powercampustest.dbo.PEOPLE c
2127		INNER JOIN #TMP_PEOPLE_ACTION tmp ON c.PEOPLE_ID = tmp.PEOPLE_ID AND tmp.ACT = 'U'
2128		INNER JOIN Bridge_Test.dbo.PEOPLECHANGES pc ON pc.sequence_no = tmp.sequence_no;
2129		END TRY
2130		BEGIN CATCH
2131		INSERT INTO @LogErrors
2132			(
2133				[Error_Number],
2134				[Error_Message],
2135				[Error_Severity],
2136				[Error_State],
2137				[Error_Procedure],
2138				[Error_Line],
2139				[UserName],
2140				[HostName],
2141				[Time_Stamp]
2142			)
2143				SELECT ISNULL(ERROR_NUMBER(),0),
2144					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2145					ISNULL(ERROR_SEVERITY(),0),	
2146					ISNULL(ERROR_STATE(),1),	
2147					ISNULL(ERROR_PROCEDURE(),''),			 
2148					ISNULL(ERROR_LINE(), 0), 
2149					SUSER_SNAME(), 
2150					HOST_NAME(), 
2151					GETDATE();
2152		END CATCH
2153
2154		--PersonPhone - Revised - OK
2155		SET @TABLE_NAME = 'PersonPhone'
2156		BEGIN TRY
2157		IF OBJECT_ID('tempdb..#TMP_PERSON_PHONE') IS NOT NULL
2158		DROP TABLE #TMP_PERSON_PHONE;
2159		SELECT * INTO #TMP_PERSON_PHONE FROM 
2160		(
2161				SELECT TOP 1
2162				p.PersonId PersonId,
2163				'HOME' PhoneType,
2164				COALESCE(REPLACE(REPLACE(REPLACE(REPLACE(pc.Phone1,'(',''),' ',''),'-',''),')',''), N' ') PhoneNumber,
2165				ISNULL(CC.CODE_VALUE_KEY, '') CountryId,
2166				COALESCE(pc.CREATE_Date, GetDate()) Create_Date,
2167				COALESCE(pc.CREATE_TIME, GetDate()) Create_Time,
2168				COALESCE(pc.Create_Opid, N' ') Create_Opid,
2169				pc.CREATE_TERMINAL,
2170				COALESCE(pc.Revision_Date, GetDate()) Revision_Date,
2171				COALESCE(pc.Revision_TIME, GetDate()) Revision_Time,
2172				COALESCE(pc.Revision_Opid, N' ') Revision_Opid,
2173				pc.REVISION_TERMINAL
2174				from Bridge_test.dbo.PEOPLECHANGES pc 
2175				LEFT JOIN [powercampustest].[dbo].[PEOPLE] p ON pc.PEOPLE_ID = p.PEOPLE_ID
2176				LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]  /*Lookup Country*/
2177				WHERE 1=1 
2178				AND EXISTS 
2179				(
2180					SELECT 1 FROM #TMP_PEOPLE_ACTION tmp 
2181					WHERE tmp.ACT = 'I' 
2182					AND pc.sequence_no = tmp.sequence_no
2183				)
2184				AND pc.Processed IS NULL
2185
2186				UNION
2187				 
2188				SELECT TOP 1
2189				p.PersonId PersonId,
2190				'Office' PhoneType,
2191				COALESCE(REPLACE(REPLACE(REPLACE(REPLACE(pc.Phone2,'(',''),' ',''),'-',''),')',''), N' ') PhoneNumber,
2192				ISNULL(CC.CODE_VALUE_KEY, '') CountryId,
2193				COALESCE(pc.CREATE_Date, GetDate()) Create_Date,
2194				COALESCE(pc.CREATE_TIME, GetDate()) Create_Time,
2195				COALESCE(pc.Create_Opid, N' ') Create_Opid,
2196				pc.CREATE_TERMINAL,
2197				COALESCE(pc.Revision_Date, GetDate()) Revision_Date,
2198				COALESCE(pc.Revision_TIME, GetDate()) Revision_Time,
2199				COALESCE(pc.Revision_Opid, N' ') Revision_Opid,
2200				pc.REVISION_TERMINAL
2201				from Bridge_test.dbo.PEOPLECHANGES pc 
2202				LEFT JOIN powercampustest.dbo.PEOPLE p ON pc.PEOPLE_ID = p.PEOPLE_ID
2203				LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]  /*Lookup Country*/
2204				WHERE 1=1 
2205				AND EXISTS 
2206				(
2207					SELECT 1 FROM #TMP_PEOPLE_ACTION tmp 
2208					WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no
2209				)
2210				AND pc.Processed IS NULL
2211
2212				UNION 
2213
2214				SELECT TOP 1
2215				p.PersonId PersonId,
2216				'Cell' PhoneType,
2217				COALESCE(REPLACE(REPLACE(REPLACE(REPLACE(pc.Phone3,'(',''),' ',''),'-',''),')',''), N' ') PhoneNumber,
2218				ISNULL(CC.CODE_VALUE_KEY, '') CountryId,
2219				COALESCE(pc.CREATE_Date, GetDate()) Create_Date,
2220				COALESCE(pc.CREATE_TIME, GetDate()) Create_Time,
2221				COALESCE(pc.Create_Opid, N' ') Create_Opid,
2222				pc.CREATE_TERMINAL,
2223				COALESCE(pc.Revision_Date, GetDate()) Revision_Date,
2224				COALESCE(pc.Revision_TIME, GetDate()) Revision_Time,
2225				COALESCE(pc.Revision_Opid, N' ') Revision_Opid,
2226				pc.REVISION_TERMINAL
2227				from Bridge_test.dbo.PEOPLECHANGES pc 
2228				LEFT JOIN powercampustest.dbo.PEOPLE p ON pc.PEOPLE_ID = p.PEOPLE_ID
2229				LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]  /*Lookup Country*/
2230				WHERE 1=1
2231				AND EXISTS 
2232				(
2233					SELECT 1 FROM #TMP_PEOPLE_ACTION tmp 
2234					WHERE tmp.ACT = 'I' AND pc.sequence_no = tmp.sequence_no
2235				)
2236				AND pc.Processed IS NULL
2237			) u;
2238
2239		UPDATE m 
2240		SET
2241		   --PersonId = pc.PersonId,
2242		   --PhoneType = pc.PhoneType,
2243		   --PhoneNumber = pc.PhoneNumber,
2244		   --CountryId = pc.CountryId,
2245		   Create_Date = pc.Create_Date,
2246		   Create_Time = pc.Create_Time,
2247		   Create_Opid = pc.Create_Opid,
2248		   Create_Terminal = pc.Create_Terminal,
2249		   Revision_Date = pc.Revision_Date,
2250		   Revision_Time = pc.Revision_Time,
2251		   Revision_Opid = pc.Revision_Opid,
2252		   Revision_Terminal = pc.Revision_Terminal
2253		  FROM #TMP_PERSON_PHONE pc
2254		  INNER JOIN powercampustest.dbo.PersonPhone m  
2255		   ON m.PersonId = pc.PersonId AND m.PhoneType = pc.PhoneType AND m.PhoneNumber = pc.PhoneNumber AND m.CountryId = pc.CountryId
2256		END TRY
2257		BEGIN CATCH
2258		INSERT INTO @LogErrors
2259			(
2260				[Error_Number],
2261				[Error_Message],
2262				[Error_Severity],
2263				[Error_State],
2264				[Error_Procedure],
2265				[Error_Line],
2266				[UserName],
2267				[HostName],
2268				[Time_Stamp]
2269			)
2270				SELECT ISNULL(ERROR_NUMBER(),0),
2271					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2272					ISNULL(ERROR_SEVERITY(),0),	
2273					ISNULL(ERROR_STATE(),1),	
2274					ISNULL(ERROR_PROCEDURE(),''),			 
2275					ISNULL(ERROR_LINE(), 0), 
2276					SUSER_SNAME(), 
2277					HOST_NAME(), 
2278					GETDATE();
2279		END CATCH
2280
2281		--PersonEthnicity - Revised - OK
2282		SET @TABLE_NAME = 'PersonEthnicity'
2283		BEGIN TRY
2284		UPDATE m  
2285		SET
2286		   IpedsEthnicityId = t.Ubrthcnty,
2287		   IpedsFederalCategoryId = t.Uethnicbg,
2288		   CREATE_DATE = t.CREATE_DATE,
2289		   CREATE_TIME = t.CREATE_TIME,
2290		   CREATE_OPID = t.CREATE_OPID,
2291		   CREATE_TERMINAL = t.CREATE_TERMINAL,
2292		   REVISION_DATE = t.REVISION_DATE,
2293		   REVISION_TIME = t.REVISION_TIME,
2294		   REVISION_OPID = t.REVISION_OPID,
2295		   REVISION_TERMINAL = t.REVISION_TERMINAL
2296		  FROM Bridge_test.dbo.PEOPLECHANGES t
2297		  INNER JOIN powercampustest.dbo.PersonEthnicity m ON m.PersonId = t.PersonId
2298		  LEFT JOIN powercampustest.dbo.PEOPLE p ON t.PEOPLE_ID = p.PEOPLE_ID
2299		  WHERE (Processed is Null) 
2300		  AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND t.sequence_no = tmp.sequence_no);
2301		END TRY
2302		BEGIN CATCH
2303		INSERT INTO @LogErrors
2304			(
2305				[Error_Number],
2306				[Error_Message],
2307				[Error_Severity],
2308				[Error_State],
2309				[Error_Procedure],
2310				[Error_Line],
2311				[UserName],
2312				[HostName],
2313				[Time_Stamp]
2314			)
2315				SELECT ISNULL(ERROR_NUMBER(),0),
2316					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2317					ISNULL(ERROR_SEVERITY(),0),	
2318					ISNULL(ERROR_STATE(),1),	
2319					ISNULL(ERROR_PROCEDURE(),''),			 
2320					ISNULL(ERROR_LINE(), 0), 
2321					SUSER_SNAME(), 
2322					HOST_NAME(), 
2323					GETDATE();
2324		END CATCH
2325
2326		--Telecomunications - Revised - OK
2327		SET @TABLE_NAME = 'Telecomunications'
2328		BEGIN TRY
2329		UPDATE tel
2330		SET 
2331			--[COM_TYPE] = 'ALTEML',
2332			--[COM_STRING] = t.PERSONAL_EMAIL, --COM_STRING PERSONAL_EMAIL, --should be PERSONAL_EMAIL
2333			[PRIMARY_FLAG] = 'N', --PRIMARY_FLAG
2334			[PRIVATE_FLAG] = 'N', --PRIVATE_FLAG
2335			[STATUS] = 'A', --STATUS
2336			[ADDRESS_TYPE] = 'HOME', --address type
2337			[COMMENTS] = COALESCE(N' ', N' '),
2338			[CREATE_DATE] = COALESCE(t.CREATE_DATE, GETDATE()), --date format, 2006-07-26 00:00:00.000
2339			[CREATE_TIME] = COALESCE(t.CREATE_TIME, GetDate()), --time format, 1900-01-01 08:39:02.000
2340			[CREATE_OPID] = COALESCE(t.CREATE_OPID, N' '), 
2341			[CREATE_TERMINAL] = COALESCE(t.CREATE_TERMINAL, N' '),
2342			[REVISION_DATE] = COALESCE(t.REVISION_DATE, GETDATE()), --date format, 2006-07-26 00:00:00.000
2343			[REVISION_TIME] = COALESCE(t.REVISION_TIME, GetDate()), --time format, 1900-01-01 08:39:02.000
2344			[REVISION_OPID] = COALESCE(t.REVISION_OPID, N' '),
2345			[REVISION_TERMINAL] = COALESCE(t.REVISION_TERMINAL, N' ')
2346		FROM Bridge_test.dbo.peoplechanges t
2347		INNER JOIN powercampustest.dbo.TELECOMMUNICATIONS tel ON t.PEOPLE_CODE_ID = tel.PEOPLE_ORG_CODE_ID
2348		AND tel.COM_TYPE = 'ALTEML'
2349		AND	tel.COM_STRING = t.PERSONAL_EMAIL
2350		WHERE        (t.Processed is Null) 
2351	    AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND t.sequence_no = tmp.sequence_no);
2352		END TRY
2353		BEGIN CATCH
2354		INSERT INTO @LogErrors
2355			(
2356				[Error_Number],
2357				[Error_Message],
2358				[Error_Severity],
2359				[Error_State],
2360				[Error_Procedure],
2361				[Error_Line],
2362				[UserName],
2363				[HostName],
2364				[Time_Stamp]
2365			)
2366				SELECT ISNULL(ERROR_NUMBER(),0),
2367					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2368					ISNULL(ERROR_SEVERITY(),0),	
2369					ISNULL(ERROR_STATE(),1),	
2370					ISNULL(ERROR_PROCEDURE(),''),			 
2371					ISNULL(ERROR_LINE(), 0), 
2372					SUSER_SNAME(), 
2373					HOST_NAME(), 
2374					GETDATE();
2375		END CATCH
2376
2377		--Address - Revised - OK
2378		SET @TABLE_NAME = 'ADDRESS'
2379		BEGIN TRY
2380        UPDATE m
2381		SET 
2382		   PEOPLE_ORG_CODE = PC.PEOPLE_CODE,
2383		   --PEOPLE_ORG_ID = PC.PEOPLE_ID,
2384		   --PEOPLE_ORG_CODE_ID = PC.PEOPLE_CODE_ID,
2385		   --ADDRESS_TYPE = 'PERM',
2386		   ADDRESS_LINE_1 = PC.ADDRESS1,
2387		   ADDRESS_LINE_2 = PC.ADDRESS2,
2388		   ADDRESS_LINE_3 = PC.ADDRESS3,
2389		   CITY = PC.CITY,
2390		   STATE = PC.STATE,
2391		   ZIP_CODE = PC.ZIP,
2392		   COUNTRY = (SELECT TOP 1 ISNULL(CC.CODE_VALUE_KEY, '') FROM Bridge_test.dbo.peoplechanges pc 
2393						LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]
2394						WHERE pc.Processed IS NULL 
2395						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
2396		   COUNTY = (SELECT TOP 1 ISNULL(CT.CODE_VALUE, '') FROM Bridge_test.dbo.peoplechanges pc 
2397						LEFT JOIN [powercampustest].[dbo].[CODE_COUNTY] CT ON CT.[LONG_DESC] = PC.[UCOUNTY]
2398						WHERE pc.Processed IS NULL 
2399						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
2400		   DAY_PHONE = PC.PHONE1,
2401		   EVENING_PHONE = PC.PHONE2,
2402		   MAIN_FAX = NULL,
2403		   ALTERNATE_FAX = NULL,
2404		   NO_MAIL = ' ',
2405		   NO_CALL_DAY_PHONE = NULL,
2406		   NO_CALL_EVE_PHONE = NULL,
2407		   NOT_VALID_BEG_MM = NULL,
2408		   NOT_VALID_BEG_YY = NULL,
2409		   NOT_VALID_END_MM = NULL,
2410		   NOT_VALID_END_YY = NULL,
2411		   ALTERNATE_ADD_TYPE = NULL,
2412		   CREATE_DATE = COALESCE(PC.CREATE_Date, GetDate()),
2413		   CREATE_TIME =  COALESCE(PC.CREATE_TIME, GetDate()),
2414		   CREATE_OPID = COALESCE(PC.Create_Opid, N' '),
2415		   CREATE_TERMINAL = PC.CREATE_TERMINAL,
2416		   REVISION_DATE = COALESCE(PC.Revision_Date, GetDate()),
2417		   REVISION_TIME = COALESCE(PC.Revision_TIME, GetDate()),
2418		   REVISION_OPID = COALESCE(PC.Revision_Opid, N' '),
2419		   REVISION_TERMINAL = PC.REVISION_TERMINAL,
2420		   ABT_JOIN = '*',
2421		   CITY_PREFIX = NULL,
2422		   CITY_SUFFIX = NULL,
2423		   EMAIL_ADDRESS = PC.UEMAILSCT,
2424		   ADDRESS_LINE_4 = NULL,
2425		   HOUSE_NUMBER = NULL
2426		FROM Bridge_test.dbo.PEOPLECHANGES PC
2427		INNER JOIN powercampustest.dbo.ADDRESS m ON PC.PEOPLE_ID = m.PEOPLE_ORG_ID
2428		AND m.ADDRESS_TYPE = 'PERM'
2429		WHERE 
2430		(Processed is Null)
2431		AND EXISTS (SELECT 1
2432					FROM #TMP_PEOPLE_ACTION tmp
2433				   WHERE tmp.ACT = 'U'
2434					 AND pc.sequence_no = tmp.sequence_no);
2435		END TRY
2436		BEGIN CATCH
2437		INSERT INTO @LogErrors
2438			(
2439				[Error_Number],
2440				[Error_Message],
2441				[Error_Severity],
2442				[Error_State],
2443				[Error_Procedure],
2444				[Error_Line],
2445				[UserName],
2446				[HostName],
2447				[Time_Stamp]
2448			)
2449				SELECT ISNULL(ERROR_NUMBER(),0),
2450					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2451					ISNULL(ERROR_SEVERITY(),0),	
2452					ISNULL(ERROR_STATE(),1),	
2453					ISNULL(ERROR_PROCEDURE(),''),			 
2454					ISNULL(ERROR_LINE(), 0), 
2455					SUSER_SNAME(), 
2456					HOST_NAME(), 
2457					GETDATE();
2458		END CATCH
2459
2460		--ADDRESSHIERARCHYUNIQUE - Revised - OK
2461		SET @TABLE_NAME = 'ADDRESSHIERARCHYUNIQUE'
2462		BEGIN TRY
2463		UPDATE m
2464		SET					
2465			ADDRESS_TYPE = 'PERM',
2466			RANK = 1,
2467			CREATE_DATE = COALESCE(PC.CREATE_Date, GetDate()),
2468			CREATE_TIME = COALESCE(PC.CREATE_TIME, GetDate()),
2469			CREATE_OPID = COALESCE(PC.Create_Opid, N' '),
2470			CREATE_TERMINAL = PC.CREATE_TERMINAL,
2471			REVISION_DATE = COALESCE(PC.Revision_Date, GetDate()),
2472			REVISION_TIME = COALESCE(PC.Revision_TIME, GetDate()),
2473			REVISION_OPID = COALESCE(PC.Revision_Opid, N' '),
2474			REVISION_TERMINAL = PC.REVISION_TERMINAL
2475		FROM         Bridge_test.dbo.PeopleChanges PC
2476		INNER JOIN  powercampustest.dbo.ADDRESSHIERARCHYUNIQUE m ON m.PEOPLE_ORG_CODE_ID = PC.PEOPLE_CODE_ID
2477		WHERE        (Processed is Null)
2478		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no);
2479        END TRY
2480		BEGIN CATCH
2481		INSERT INTO @LogErrors
2482			(
2483				[Error_Number],
2484				[Error_Message],
2485				[Error_Severity],
2486				[Error_State],
2487				[Error_Procedure],
2488				[Error_Line],
2489				[UserName],
2490				[HostName],
2491				[Time_Stamp]
2492			)
2493				SELECT ISNULL(ERROR_NUMBER(),0),
2494					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2495					ISNULL(ERROR_SEVERITY(),0),	
2496					ISNULL(ERROR_STATE(),1),	
2497					ISNULL(ERROR_PROCEDURE(),''),			 
2498					ISNULL(ERROR_LINE(), 0), 
2499					SUSER_SNAME(), 
2500					HOST_NAME(), 
2501					GETDATE();
2502		END CATCH
2503
2504		--AddressSchedule - Revised - OK
2505		SET @TABLE_NAME = 'AddressSchedule'
2506		BEGIN TRY
2507		UPDATE m 
2508		SET
2509			zip_code = pc.ZIP, 
2510			address_line_1 =  pc.ADDRESS1, 
2511			address_line_2 = PC.ADDRESS2, 
2512			address_line_3 = PC.ADDRESS3, 
2513			city = PC.CITY, 
2514			state = PC.STATE, 
2515			county = (SELECT TOP 1 ISNULL(CC.CODE_VALUE_KEY, '') FROM Bridge_test.dbo.peoplechanges pc 
2516						LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]
2517						WHERE pc.Processed IS NULL 
2518						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
2519			alternate_fax = PC.FAX, 
2520			email_address = PC.UEMAILSCT,
2521			main_fax = PC.FAX,
2522			address_type = 'PERM',
2523			--SEQUENCE_NO,  --is identity, it should increment, Explicit value must be specified for identity column in table 'ADDRESSSCHEDULE' either when IDENTITY_INSERT is set to ON or when a replication user is inserting into a NOT FOR REPLICATION identity column.
2524			CREATE_OPID = COALESCE(PC.CREATE_OPID, N' '), 
2525			CREATE_TERMINAL = COALESCE(PC.CREATE_TERMINAL, N' '),  
2526			REVISION_OPID = COALESCE(PC.REVISION_OPID, N' '), 
2527			REVISION_TERMINAL = COALESCE(PC.REVISION_TERMINAL, N' '),
2528			PEOPLE_ORG_CODE = COALESCE(PC.PEOPLE_CODE, N' '),
2529			--PEOPLE_ORG_ID = COALESCE(PC.PEOPLE_ID, N' '),
2530			PEOPLE_ORG_CODE_ID = COALESCE(PC.PEOPLE_CODE_ID, N' '),
2531			NO_MAIL = COALESCE(N' ', N' '), 
2532			STATUS = COALESCE(N' ', N' '), 
2533			RECURRING = COALESCE(N' ', N' '), 
2534			APPROVED = COALESCE(N' ', N' '), 
2535			CREATE_DATE = COALESCE(PC.CREATE_DATE, GetDate()),
2536			CREATE_TIME = COALESCE(PC.CREATE_TIME, GetDate()),
2537			REVISION_DATE = COALESCE(PC.REVISION_DATE, GetDate()), 
2538			REVISION_TIME = COALESCE(PC.REVISION_TIME, GetDate())  
2539		FROM Bridge_test.dbo.peoplechanges pc
2540		INNER JOIN powercampustest.dbo.ADDRESSSCHEDULE m ON pc.PEOPLE_ID = PEOPLE_ORG_ID
2541		WHERE        (pc.Processed is Null)
2542		AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no);
2543        END TRY
2544		BEGIN CATCH
2545		INSERT INTO @LogErrors
2546			(
2547				[Error_Number],
2548				[Error_Message],
2549				[Error_Severity],
2550				[Error_State],
2551				[Error_Procedure],
2552				[Error_Line],
2553				[UserName],
2554				[HostName],
2555				[Time_Stamp]
2556			)
2557				SELECT ISNULL(ERROR_NUMBER(),0),
2558					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2559					ISNULL(ERROR_SEVERITY(),0),	
2560					ISNULL(ERROR_STATE(),1),	
2561					ISNULL(ERROR_PROCEDURE(),''),			 
2562					ISNULL(ERROR_LINE(), 0), 
2563					SUSER_SNAME(), 
2564					HOST_NAME(), 
2565					GETDATE();
2566		END CATCH
2567
2568		--Mailing - Revised - OK
2569		SET @TABLE_NAME = 'UPDATE - Mailing'
2570		BEGIN TRY
2571		UPDATE m
2572		SET
2573		   PEOPLE_ORG_CODE = PC.PEOPLE_CODE,
2574		   --PEOPLE_ORG_ID,
2575		   --PEOPLE_ORG_CODE_ID = PC.PEOPLE_CODE_ID,
2576		   ADDRESS_TYPE = PC.UADDRTYPE,
2577		   CREATE_DATE = COALESCE(PC.CREATE_Date, GetDate()),
2578		   CREATE_TIME = COALESCE(PC.CREATE_TIME, GetDate()),
2579		   CREATE_OPID = COALESCE(PC.Create_Opid, N' '),
2580		   CREATE_TERMINAL = PC.CREATE_TERMINAL,
2581		   REVISION_DATE = COALESCE(PC.Revision_Date, GetDate()),
2582		   REVISION_TIME = COALESCE(PC.Revision_TIME, GetDate()),
2583		   REVISION_OPID = COALESCE(PC.Revision_Opid, N' '),
2584		   REVISION_TERMINAL = PC.REVISION_TERMINAL,
2585		   ABT_JOIN = '*',
2586		   --RECIPIENT_CODE = 'P',
2587		   --RECIPIENT_ID = PC.PEOPLE_ID, ---check RECIPIENT_ID
2588		   ADD_TITLE_LINE_1 = ' ',
2589		   ADD_TITLE_LINE_2 = ' ',
2590		   PREFERRED_ADDRESS = 'PERM'
2591		 
2592		FROM Bridge_test.dbo.PeopleChanges PC
2593		INNER JOIN powercampustest.dbo.MAILING m 
2594		ON PC.PEOPLE_ID = m.PEOPLE_ORG_ID AND m.MAILING_TYPE = COALESCE(PC.UADDRTYPE, N' ') 
2595		AND m.RECIPIENT_CODE = 'P'
2596		AND m.RECIPIENT_ID = PC.PEOPLE_ID
2597		
2598		WHERE (PC.Processed is Null)
2599			 AND EXISTS (SELECT 1
2600					FROM #TMP_PEOPLE_ACTION tmp
2601				   WHERE tmp.ACT = 'U'
2602					 AND pc.sequence_no = tmp.sequence_no);
2603		END TRY
2604		BEGIN CATCH
2605		INSERT INTO @LogErrors
2606			(
2607				[Error_Number],
2608				[Error_Message],
2609				[Error_Severity],
2610				[Error_State],
2611				[Error_Procedure],
2612				[Error_Line],
2613				[UserName],
2614				[HostName],
2615				[Time_Stamp]
2616			)
2617				SELECT ISNULL(ERROR_NUMBER(),0),
2618					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2619					ISNULL(ERROR_SEVERITY(),0),	
2620					ISNULL(ERROR_STATE(),1),	
2621					ISNULL(ERROR_PROCEDURE(),''),			 
2622					ISNULL(ERROR_LINE(), 0), 
2623					SUSER_SNAME(), 
2624					HOST_NAME(), 
2625					GETDATE();
2626		END CATCH
2627
2628		--DEMOGRAPHICS - Revised - OK
2629		SET @TABLE_NAME = 'DEMOGRAPHICS'
2630		BEGIN TRY	
2631		UPDATE m 
2632		SET
2633			PEOPLE_CODE =  COALESCE(PC.PEOPLE_CODE, N' '), 
2634			--PEOPLE_ID =  COALESCE(PEOPLE_ID, N' '), 
2635			--PEOPLE_CODE_ID =  PC.PEOPLE_CODE_ID, 
2636			CREATE_DATE =  COALESCE(PC.CREATE_DATE, GETDATE()), --date format, 2006-07-26 00:00:00.000
2637			CREATE_TIME =  COALESCE(PC.CREATE_TIME, GetDate()), --time format, 1900-01-01 08:39:02.000
2638			CREATE_OPID =  COALESCE(PC.CREATE_OPID, N' '), 
2639			CREATE_TERMINAL =  COALESCE(PC.CREATE_TERMINAL, N' '), 
2640			REVISION_DATE =  COALESCE(PC.REVISION_DATE, GETDATE()), --date format, 2006-07-26 00:00:00.000
2641			REVISION_TIME =  COALESCE(PC.REVISION_TIME, GetDate()), --time format, 1900-01-01 08:39:02.000
2642			REVISION_OPID =  COALESCE(PC.REVISION_OPID, N' '), 
2643			REVISION_TERMINAL =  COALESCE(PC.REVISION_TERMINAL, N' '), 
2644			ABT_JOIN =  COALESCE(PC.ABT_JOIN, N' '), 
2645			LAST_ACTIVITY =  COALESCE(N' ', N' '), 
2646			CURRENT_ACTIVITY = COALESCE(N' ', N' '),
2647			--academic_year = COALESCE(N' ', N' '), --COALESCE(LEFT(PC.KEY2,CHARINDEX(' ',PC.KEY2)-1), N' '), --academic_year
2648			--academic_term = COALESCE(N' ', N' '), --COALESCE(RIGHT(PC.KEY2,LEN(PC.KEY2)-CHARINDEX(' ',PC.KEY2)), N' '), --academic_term
2649			--academic_session = COALESCE(N' ', N' '),--COALESCE(PC.KEY5, N' '), --ACADEMIC_SESSION
2650			legal_residence = (SELECT TOP 1 ISNULL(CC.CODE_VALUE_KEY, '') FROM Bridge_test.dbo.peoplechanges pc 
2651						LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ]
2652						WHERE pc.Processed IS NULL 
2653						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
2654			gender =  case PC.UGENDER --nvarchar(1)
2655					when 'M' then 'M'
2656					when 'F' then 'F'
2657					when 'U' then ' '  ---dont' find unknown
2658				  else ' ' --null
2659				  end, --PC
2660			marital_status =  case PC.UMARSTAT		--nvarchar(4) map is wrong
2661					when 'D' then 'DVRC'
2662					when 'M' then 'MARR'
2663					when 'O' then 'UNKN'
2664					when 'S' then 'SNGL'
2665					when 'U' then 'UNKN'
2666					when 'W' then 'WDWD'
2667					when 'X' then 'S'
2668				  else ' ' --null
2669				  end,
2670			religion =  (SELECT TOP 1 ISNULL(CC.CODE_VALUE_KEY, '') FROM Bridge_test.dbo.peoplechanges pc 
2671						LEFT JOIN [powercampustest].[dbo].[CODE_RELIGION] CC ON CC.[CODE_VALUE] = PC.[UDENOMINAT]
2672						WHERE pc.Processed IS NULL 
2673						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
2674			/*case PC.UDENOMINAT				--nvarchar(4)---UDENOMINAT AOG, should be AOG,then ASSM, might need to trim
2675					when 'ADV' then 'SDA'	
2676					when 'AOG' then 'ASSM'	
2677					when 'APO' then 'APO'
2678					when 'BAP' then 'BAPT'
2679					when 'BIB' then 'BIB'	
2680					when 'CC' then 'CHOC'
2681					when 'CHAR' then 'CHAR'
2682					when 'CHCH' then 'CHRS'
2683					when 'COG' then 'CHOG'
2684					when 'COV' then 'COV'
2685					when 'EPIS' then 'EPIS'
2686					when 'EVAN' then 'EVAN'	
2687					when 'FOUR' then 'FSQR'	
2688					when 'FULL' then 'FULL'	
2689					when 'GREG' then 'CONG'	
2690					when 'HOLI' then 'HOLI'	
2691					when 'IND' then 'IND'	
2692					when 'ISL' then 'ISLA'
2693					when 'LUT' then 'LUTH'	
2694					when 'MEN' then 'MENN'	
2695					when 'MET' then 'METH'	
2696					when 'MISS' then 'MISS'	
2697					when 'NA' then 'NONR'	
2698					when 'NAZ' then 'NAZ'	
2699					when 'NON' then 'NOND'	
2700					when 'OTH' then 'OTHR'
2701					when 'PENT' then 'PENT'	
2702					when 'PRES' then 'PRES'	
2703					when 'RCAT' then 'RCA'	
2704					when 'REF' then 'REFM'	
2705					when 'SBAP' then 'SBAP'	
2706					when 'WES' then 'WES' --didn't find wesleyan need to check
2707					--when 'JW' then 'Jehovah's Witness'	--need to escape the '
2708					when 'JEW' then 'JEW'
2709					when 'LDS' then 'LDS'	
2710					when 'ORTH' then 'ORX'
2711				  else ' ' --null
2712				  end, --PC*/
2713			veteran = case PC.UVETERAN		
2714					when 'VETA' then 'VETA'
2715					when 'VETT' then 'VETT'
2716					when 'VET' then 'VET'
2717					when 'VETB' then 'VETB'
2718				  else ' ' --null
2719				  end, --PC
2720			citizenship = (SELECT TOP 1 ISNULL(CC.CODE_VALUE_KEY, '') FROM Bridge_test.dbo.peoplechanges pc  ---looks up code value, UCNTRYCITZ United States, didn't find, SELECT * FROM Bridge_test.dbo.PEOPLECHANGES ORDER BY sequence_no DESC
2721	--MEDIUM_DESC	LONG_DESC
2722	--United States	United States of America
2723			
2724						--LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ] ---country is null
2725						LEFT JOIN [powercampustest].[dbo].[CODE_COUNTRY] CC ON CC.[MEDIUM_DESC] = PC.[UCNTRYCITZ] ---MEDIUM_DESC matches
2726						WHERE pc.Processed IS NULL 
2727						AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no)),
2728			visa = case PC.UVISATYPE
2729					when 'B-1' then 'Green Card'
2730					when 'PR' then 'Permanent Resident'
2731					when 'F-1' then 'Student Visa'
2732				  else ' ' --null
2733				  end --PC
2734			FROM Bridge_test.dbo.peoplechanges pc
2735			INNER JOIN powercampustest.dbo.DEMOGRAPHICS m ON m.PEOPLE_ID = pc.PEOPLE_ID
2736			AND m.academic_year = COALESCE(N' ', N' ') --COALESCE(LEFT(PC.KEY2,CHARINDEX(' ',PC.KEY2)-1), N' '), --academic_year
2737			AND m.academic_term = COALESCE(N' ', N' ') --COALESCE(RIGHT(PC.KEY2,LEN(PC.KEY2)-CHARINDEX(' ',PC.KEY2)), N' '), --academic_term
2738			AND m.academic_session = COALESCE(N' ', N' ')--COALESCE(PC.KEY5, N' '), --ACADEMIC_SESSION
2739			WHERE        (PC.Processed is Null)
2740			AND EXISTS (SELECT 1 FROM #TMP_PEOPLE_ACTION tmp WHERE tmp.ACT = 'U' AND pc.sequence_no = tmp.sequence_no);
2741        END TRY
2742		BEGIN CATCH
2743		INSERT INTO @LogErrors
2744			(
2745				[Error_Number],
2746				[Error_Message],
2747				[Error_Severity],
2748				[Error_State],
2749				[Error_Procedure],
2750				[Error_Line],
2751				[UserName],
2752				[HostName],
2753				[Time_Stamp]
2754			)
2755				SELECT ISNULL(ERROR_NUMBER(),0),
2756					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2757					ISNULL(ERROR_SEVERITY(),0),	
2758					ISNULL(ERROR_STATE(),1),	
2759					ISNULL(ERROR_PROCEDURE(),''),			 
2760					ISNULL(ERROR_LINE(), 0), 
2761					SUSER_SNAME(), 
2762					HOST_NAME(), 
2763					GETDATE();
2764		END CATCH
2765
2766		--STOPLIST -Revised
2767		SET @TABLE_NAME = 'UPDATE - STOPLIST'
2768		BEGIN TRY
2769		UPDATE m 
2770		SET
2771		   PEOPLE_CODE = PC.PEOPLE_CODE,
2772		   --PEOPLE_ID = 
2773		   --PEOPLE_CODE_ID = PC.PEOPLE_CODE_ID,
2774		   --STOP_REASON = 'RGSA', ---check stop reason
2775		   --STOP_DATE = GetDate(),
2776		   
2777		   CLEARED = 'Y',
2778		   CLEARED_DATE = GetDate(),
2779		   COMMENTS = NULL,
2780		   CREATE_DATE = COALESCE(PC.CREATE_Date, GetDate()),
2781		   CREATE_TIME = COALESCE(PC.CREATE_TIME, GetDate()),
2782		   CREATE_OPID = COALESCE(PC.Create_Opid, N' '),
2783		   CREATE_TERMINAL = PC.CREATE_TERMINAL,
2784		   REVISION_DATE = COALESCE(PC.Revision_Date, GetDate()),
2785		   REVISION_TIME = COALESCE(PC.Revision_TIME, GetDate()),
2786		   REVISION_OPID = COALESCE(PC.Revision_Opid, N' '),
2787		   REVISION_TERMINAL = PC.REVISION_TERMINAL,
2788		   ABT_JOIN = '*'	 
2789		FROM Bridge_test.dbo.PeopleChanges PC
2790		INNER JOIN powercampustest.dbo.[STOPLIST] m ON m.PEOPLE_CODE_ID = PC.PEOPLE_CODE_ID
2791		   AND m.STOP_REASON = 'RGSA' ---check stop reason
2792		WHERE (Processed is Null)
2793			AND EXISTS (SELECT 1
2794				FROM #TMP_PEOPLE_ACTION tmp
2795				WHERE tmp.ACT = 'U'
2796					AND pc.sequence_no = tmp.sequence_no);
2797		END TRY
2798		BEGIN CATCH
2799		INSERT INTO @LogErrors
2800			(
2801				[Error_Number],
2802				[Error_Message],
2803				[Error_Severity],
2804				[Error_State],
2805				[Error_Procedure],
2806				[Error_Line],
2807				[UserName],
2808				[HostName],
2809				[Time_Stamp]
2810			)
2811				SELECT ISNULL(ERROR_NUMBER(),0),
2812					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2813					ISNULL(ERROR_SEVERITY(),0),	
2814					ISNULL(ERROR_STATE(),1),	
2815					ISNULL(ERROR_PROCEDURE(),''),			 
2816					ISNULL(ERROR_LINE(), 0), 
2817					SUSER_SNAME(), 
2818					HOST_NAME(), 
2819					GETDATE();
2820		END CATCH
2821
2822		--PEOPLETYPE - Revised - OK
2823		SET @TABLE_NAME = 'PEOPLETYPE'
2824		BEGIN TRY
2825		UPDATE m
2826		SET
2827		   PEOPLE_CODE = PC.PEOPLE_CODE,
2828		   --PEOPLE_CODE_ID = PC.PEOPLE_CODE_ID,
2829		   --PEOPLE_TYPE = 'APP', ---check PEOPLE_TYPE
2830		   CREATE_DATE = COALESCE(PC.CREATE_Date, GetDate()),
2831		   CREATE_TIME = COALESCE(PC.CREATE_TIME, GetDate()),
2832		   CREATE_OPID = COALESCE(PC.Create_Opid, N' '),
2833		   CREATE_TERMINAL = PC.CREATE_TERMINAL,
2834		   REVISION_DATE = COALESCE(PC.Revision_Date, GetDate()),
2835		   REVISION_TIME = COALESCE(PC.Revision_TIME, GetDate()),
2836		   REVISION_OPID = COALESCE(PC.Revision_Opid, N' '),
2837		   REVISION_TERMINAL = PC.REVISION_TERMINAL,
2838		   ABT_JOIN = '*'
2839		FROM Bridge_test.dbo.PeopleChanges PC
2840		INNER JOIN powercampustest.dbo.PEOPLETYPE m ON m.PEOPLE_ID = PC.PEOPLE_ID AND m.PEOPLE_TYPE = 'APP'
2841		WHERE (Processed is Null)
2842			 AND EXISTS (SELECT 1
2843					FROM #TMP_PEOPLE_ACTION tmp
2844				   WHERE tmp.ACT = 'U'
2845					 AND pc.sequence_no = tmp.sequence_no);
2846		END TRY
2847		BEGIN CATCH
2848		INSERT INTO @LogErrors
2849			(
2850				[Error_Number],
2851				[Error_Message],
2852				[Error_Severity],
2853				[Error_State],
2854				[Error_Procedure],
2855				[Error_Line],
2856				[UserName],
2857				[HostName],
2858				[Time_Stamp]
2859			)
2860				SELECT ISNULL(ERROR_NUMBER(),0),
2861					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2862					ISNULL(ERROR_SEVERITY(),0),	
2863					ISNULL(ERROR_STATE(),1),	
2864					ISNULL(ERROR_PROCEDURE(),''),			 
2865					ISNULL(ERROR_LINE(), 0), 
2866					SUSER_SNAME(), 
2867					HOST_NAME(), 
2868					GETDATE();
2869		END CATCH
2870
2871		--RESIDENCY -Revised - OK
2872		SET @TABLE_NAME = 'UPDATE  - RESIDENCY'
2873		BEGIN TRY
2874		UPDATE m 
2875		SET
2876		   PEOPLE_CODE = PC.PEOPLE_CODE,
2877		   --PEOPLE_ID = 
2878		   --PEOPLE_CODE_ID = PC.PEOPLE_CODE_ID,
2879		   --ACADEMIC_YEAR = COALESCE(LEFT(PC.KEY2, CHARINDEX(' ', PC.KEY2) - 1), N' '), --academic_year
2880		   --ACADEMIC_TERM = COALESCE(RIGHT(PC.KEY2, LEN(PC.KEY2) - CHARINDEX(' ', PC.KEY2)), N' '), --academic_term
2881		   --ACADEMIC_SESSION = COALESCE(PC.KEY5, N' '), --ACADEMIC_SESSION
2882		   RESIDENT_COMMUTER = COALESCE(N' ', N' '),
2883		   FOOD_PLAN = COALESCE(N' ', N' '),
2884		   DORM_PLAN = COALESCE(N' ', N' '),
2885		   DORM_CAMPUS = COALESCE(N' ', N' '),
2886		   DORM_BUILDING = COALESCE(N' ', N' '),
2887		   DORM_ROOM = COALESCE(N' ', N' '),
2888		   DORM_KEY = COALESCE(N' ', N' '),
2889		   LOCKER = COALESCE(N' ', N' '),
2890		   MAIL_SLOT = COALESCE(N' ', N' '),
2891		   CREATE_DATE = COALESCE(PC.CREATE_Date, GetDate()),
2892		   CREATE_TIME = COALESCE(PC.CREATE_TIME, GetDate()),
2893		   CREATE_OPID = COALESCE(PC.Create_Opid, N' '),
2894		   CREATE_TERMINAL = PC.CREATE_TERMINAL,
2895		   REVISION_DATE = COALESCE(PC.Revision_Date, GetDate()),
2896		   REVISION_TIME = COALESCE(PC.Revision_TIME, GetDate()),
2897		   REVISION_OPID = COALESCE(PC.Revision_Opid, N' '),
2898		   REVISION_TERMINAL = PC.REVISION_TERMINAL,
2899		   ABT_JOIN = '*',
2900		   LAST_ACTIVITY = 'Y',
2901		   CURRENT_ACTIVITY= 'N'
2902		FROM Bridge_test.dbo.PeopleChanges PC
2903		INNER JOIN powercampustest.dbo.RESIDENCY m ON 
2904		m.PEOPLE_CODE_ID = PC.PEOPLE_CODE_ID
2905		 AND   m.ACADEMIC_YEAR = COALESCE(N' ', N' ') --COALESCE(LEFT(PC.KEY2, CHARINDEX(' ', PC.KEY2) - 1), N' ') --academic_year
2906		 AND   m.ACADEMIC_TERM = COALESCE(N' ', N' ') --COALESCE(RIGHT(PC.KEY2, LEN(PC.KEY2) - CHARINDEX(' ', PC.KEY2)), N' ') --academic_term
2907		   AND ACADEMIC_SESSION = COALESCE(N' ', N' ') --COALESCE(PC.KEY5, N' ') --ACADEMIC_SESSION
2908		   WHERE (Processed is Null)
2909			 AND EXISTS (SELECT 1
2910					FROM #TMP_PEOPLE_ACTION tmp
2911				   WHERE tmp.ACT = 'U'
2912					 AND pc.sequence_no = tmp.sequence_no);
2913		END TRY
2914		BEGIN CATCH
2915		INSERT INTO @LogErrors
2916			(
2917				[Error_Number],
2918				[Error_Message],
2919				[Error_Severity],
2920				[Error_State],
2921				[Error_Procedure],
2922				[Error_Line],
2923				[UserName],
2924				[HostName],
2925				[Time_Stamp]
2926			)
2927				SELECT ISNULL(ERROR_NUMBER(),0),
2928					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
2929					ISNULL(ERROR_SEVERITY(),0),	
2930					ISNULL(ERROR_STATE(),1),	
2931					ISNULL(ERROR_PROCEDURE(),''),			 
2932					ISNULL(ERROR_LINE(), 0), 
2933					SUSER_SNAME(), 
2934					HOST_NAME(), 
2935					GETDATE();
2936		END CATCH
2937
2938		--ACADEMIC - Revised
2939		/*
2940		SET @TABLE_NAME = 'ACADEMIC'
2941		BEGIN TRY
2942		UPDATE m 
2943		SET
2944		   PEOPLE_CODE = PC.PEOPLE_CODE,
2945		   --PEOPLE_ID = PC.PEOPLE_ID,
2946		   PEOPLE_CODE_ID = PC.PEOPLE_CODE_ID,
2947		   academic_year = LEFT(PC.KEY2, CHARINDEX(' ', PC.KEY2) - 1),
2948		   academic_term = RIGHT(PC.KEY2, LEN(PC.KEY2) - CHARINDEX(' ', PC.KEY2)),
2949		   academic_session = PC.KEY5,
2950		   program = PC.Key3,
2951		   degree = PC.UCURRICULM,
2952		   curriculum = PC.UMAJOR,
2953		   college = PC.KEY5,
2954		   department = PC.DEPARTMENT,
2955		   class_level = PC.UCOLLCMPLT,
2956		   full_part = PC.Ufullpart,
2957		   nontrad_program = COALESCE(PC.Ucohort, N' '),
2958		   POPULATION = COALESCE(N' ', N' '),
2959		   REGISTER_LIMIT = COALESCE(1, 1),
2960		   CREDITS =  COALESCE(1, 1),
2961		   PREREG_VALIDATE = COALESCE(N' ', N' '),
2962		   REG_VALIDATE = COALESCE(N' ', N' '),
2963		   GRADUATED = COALESCE(N' ', N' '),
2964		   ORG_CODE_ID = COALESCE(N' ', N' '),
2965		   ACADEMIC_FLAG = COALESCE(N' ', N' '),
2966		   PRIMARY_FLAG = COALESCE(N' ', N' '),
2967		   APPLICATION_FLAG = COALESCE(1, 1),
2968		   ACA_PLAN_SETUP = COALESCE(1, 1),
2969		   STATUS = COALESCE(1, 1),
2970		   TRANSCRIPT_SEQ = COALESCE(N' ', N' '),
2971		   LAST_ACTIVITY = COALESCE(N' ', N' '),
2972		   CURRENT_ACTIVITY = COALESCE(N' ', N' '),
2973		   INQUIRY_FLAG = COALESCE(N' ', N' '),
2974		   PROTECT_COUNSELOR = COALESCE(N' ', N' '),
2975		   ENROLLMENT_STATUS_DATE = COALESCE(N' ', N' '),
2976		   CREATE_DATE = COALESCE(PC.CREATE_DATE, GETDATE()),
2977		   CREATE_TIME = COALESCE(PC.CREATE_TIME, GetDate()),
2978		   CREATE_OPID = COALESCE(PC.CREATE_OPID, N' '),
2979		   CREATE_TERMINAL = COALESCE(PC.CREATE_TERMINAL, N' '),
2980		   REVISION_DATE = COALESCE(PC.REVISION_DATE, GETDATE()),
2981		   REVISION_TIME = COALESCE(PC.REVISION_TIME, GetDate()),
2982		   REVISION_OPID = COALESCE(PC.REVISION_OPID, N' '),
2983		   REVISION_TERMINAL = COALESCE(PC.REVISION_TERMINAL, N' '),
2984		   ABT_JOIN = COALESCE(PC.ABT_JOIN, N' '),
2985		   ADVISOR = CASE UPPER(COALESCE(PC.Uadvismntr, N' '))
2986						WHEN UPPER('Dan Wimberly') THEN 'P000000024'
2987						WHEN UPPER('Curtis Williams') THEN 'P000007954'
2988						WHEN UPPER('Kyle White') THEN 'P000000039'
2989						WHEN UPPER('Mark Weeter') THEN 'P000000038'
2990						WHEN UPPER('Wendel Weaver') THEN 'P000000046'
2991						WHEN UPPER('Angela Watson') THEN 'P000080511'
2992						WHEN UPPER('William Vieux') THEN 'P000061761'
2993						WHEN UPPER('Evert Van Kuiken') THEN 'P000064804'
2994						WHEN UPPER('Brian Turner') THEN 'P000000028'
2995						WHEN UPPER('Rosimar Truitt') THEN 'P000077556'
2996						WHEN UPPER('Travis Sutton') THEN 'P000075416'
2997						WHEN UPPER('Jonathan Stewart') THEN 'P000071101'
2998						WHEN UPPER('Barbara Schmidt') THEN 'P000053016'
2999						WHEN UPPER('Keith Scanlon') THEN 'P000041404'
3000						WHEN UPPER('Lisa Riggs') THEN 'P000028801'
3001						WHEN UPPER('Cynthia Riffe') THEN 'P000000323'
3002						WHEN UPPER('Gail Richardson') THEN 'P000000117'
3003						WHEN UPPER('Brenda Raleigh') THEN 'P000000076'
3004						WHEN UPPER('Douglas Quick') THEN 'P000000011'
3005						WHEN UPPER('Matthew Parker') THEN 'P000080513'
3006						WHEN UPPER('Mark Molder') THEN 'P000048209'
3007						WHEN UPPER('Joshua McNall') THEN 'P000050157'
3008						WHEN UPPER('Kenneth LeVan') THEN 'P000082148'
3009						WHEN UPPER('Patricia Leggett') THEN 'P000052244'
3010						WHEN UPPER('Rebecca Le') THEN 'P000007956'
3011						WHEN UPPER('Kirk Kelley') THEN 'P000070505'
3012						WHEN UPPER('Dariusz Jarmola') THEN 'P000000027'
3013						WHEN UPPER('Kirk Jackson') THEN 'P000079570'
3014						WHEN UPPER('Stephen Hughes') THEN 'P000000009'
3015						WHEN UPPER('Sheryl Hill') THEN 'P000076649'
3016						WHEN UPPER('Beverly Hartter') THEN 'P000054748'
3017						WHEN UPPER('Kerri Hampton') THEN 'P000057578'
3018						WHEN UPPER('J. Michael Fullingim') THEN 'P000000015'
3019						WHEN UPPER('Nancy Eckerd') THEN 'P000076210'
3020						WHEN UPPER('Charissa Dunn') THEN 'P000000008'
3021						WHEN UPPER('Amanda Dickinson') THEN 'P000005447'
3022						WHEN UPPER('Richard Daake') THEN 'P000000012'
3023						WHEN UPPER('David Cochran') THEN 'P000000026'
3024						WHEN UPPER('Sheldon Buxton') THEN 'P000076612'
3025						WHEN UPPER('Robert Bonner') THEN 'P000000048'
3026						WHEN UPPER('Jason Beschta') THEN 'P000080512'
3027						WHEN UPPER('Abbie Bailey') THEN 'P000000155'
3028						WHEN UPPER('Danielle Appeldorn') THEN 'P000055325'
3029						WHEN UPPER('Brett Andrews') THEN 'P000000019'
3030						WHEN UPPER('Amanda Alonzo') THEN 'P000052841'
3031						END,
3032		   COUNSELOR = CASE UPPER(COALESCE(PC.Uadmcouncd, N' ')) 
3033							WHEN 'JENNIFERW' THEN 'P000007691'
3034							WHEN 'DANNYV' THEN 'P000071345'
3035							WHEN 'TJT' THEN 'P000007936'
3036							WHEN 'COURTNEYS' THEN 'P000012724'
3037							WHEN 'CAMIES' THEN 'P000084158'
3038							WHEN 'EDWINR' THEN 'P000039820'
3039							WHEN 'SAMP' THEN 'P000007515'
3040							WHEN 'JAMESP' THEN 'P000007590'
3041							WHEN 'NATALIEN' THEN 'P000005638'
3042							WHEN 'MARKM' THEN 'P000048209'
3043							WHEN 'JOHNM' THEN 'P000073408'
3044							WHEN 'JANETL' THEN 'P000049016'
3045							WHEN 'SARAHK' THEN 'P000006432'
3046							WHEN 'NICOLEJ' THEN 'P000073408'
3047							WHEN 'MIKEH' THEN 'P000084210'
3048							WHEN 'PATTIG' THEN 'P000071311'
3049							WHEN 'BRITTANYD' THEN 'P000007978'
3050							WHEN 'TRENTOND' THEN 'P000002874'
3051							WHEN 'JEREMYD' THEN 'P000070564 '
3052						END 
3053		FROM Bridge_test.dbo.PeopleChanges PC
3054		INNER JOIN powercampustest.dbo.ACADEMIC m ON m.PEOPLE_ID = PC.PEOPLE_ID
3055		   WHERE (Processed is Null)
3056			 AND EXISTS (SELECT 1
3057					FROM #TMP_PEOPLE_ACTION tmp
3058				   WHERE tmp.ACT = 'U'
3059					 AND pc.sequence_no = tmp.sequence_no);
3060		END TRY
3061		BEGIN CATCH
3062		INSERT INTO @LogErrors
3063			(
3064				[Error_Number],
3065				[Error_Message],
3066				[Error_Severity],
3067				[Error_State],
3068				[Error_Procedure],
3069				[Error_Line],
3070				[UserName],
3071				[HostName],
3072				[Time_Stamp]
3073			)
3074				SELECT ISNULL(ERROR_NUMBER(),0),
3075					'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
3076					ISNULL(ERROR_SEVERITY(),0),	
3077					ISNULL(ERROR_STATE(),1),	
3078					ISNULL(ERROR_PROCEDURE(),''),			 
3079					ISNULL(ERROR_LINE(), 0), 
3080					SUSER_SNAME(), 
3081					HOST_NAME(), 
3082					GETDATE();
3083		END CATCH
3084		*/
3085	END TRY
3086	BEGIN CATCH
3087		UPDATE c
3088		SET c.U_KEY4 = '99-OVR'
3089		FROM #TMP_PEOPLE_ACTION tmp
3090		JOIN GoldMine_test.dbo.CONTACT1 c	ON  tmp.PEOPLE_ID = c.KEY4
3091		WHERE tmp.ACT = 'U';
3092		INSERT INTO @LogErrors
3093		(
3094			[Error_Number],
3095			[Error_Message],
3096			[Error_Severity],
3097			[Error_State],
3098			[Error_Procedure],
3099			[Error_Line],
3100			[UserName],
3101			[HostName],
3102			[Time_Stamp]
3103		)
3104			SELECT ISNULL(ERROR_NUMBER(),0),
3105				'SEQUENCE NUMBER: ' + CAST(@sequence_no AS VARCHAR(200)) + ' - ' + 'TABLE: ' + @TABLE_NAME + ' - ' + ISNULL(ERROR_MESSAGE(),'NULL Message'),
3106				ISNULL(ERROR_SEVERITY(),0),	
3107				ISNULL(ERROR_STATE(),1),	
3108				ISNULL(ERROR_PROCEDURE(),''),			 
3109				ISNULL(ERROR_LINE(), 0), 
3110				SUSER_SNAME(), 
3111				HOST_NAME(), 
3112				GETDATE();
3113	END CATCH;
3114	
3115--  End Action after Comparison
3116
3117--	Check logs to raise rollback if need
3118	IF(SELECT COUNT(1) FROM @LogErrors) > 0
3119	RAISERROR ('Error raised in TRY block.', -- Message text.  
3120               16, -- Severity.  
3121               1 -- State.  
3122               );
3123
3124	/*Update Record Proccessed*/
3125	UPDATE pc
3126	SET pc.Processed = 'Y'
3127	FROM #TMP_PEOPLE_ACTION tmp
3128	INNER JOIN Bridge_Test.dbo.PEOPLECHANGES pc ON  pc.sequence_no = tmp.sequence_no;
3129
3130	COMMIT TRANSACTION txn_people
3131
3132	
3133	
3134	END TRY
3135	BEGIN CATCH
3136		ROLLBACK TRANSACTION txn_people;
3137		PRINT 'Rollback ' + @PEOPLE_ID;
3138	END CATCH
3139
3140--	Continue loop with other People ID
3141	DELETE FROM #tmp_bridge_people_change WHERE sequence_no = @sequence_no;
3142	--DELETE FROM #tmp_bridge_people_change WHERE sequence_no = @sequence_no;
3143	SET @COUNT_PEOPLE_CHANGE = @COUNT_PEOPLE_CHANGE - 1;
3144			
3145	INSERT INTO LogErrors
3146		(
3147			[Error_Number],
3148			[Error_Message],
3149			[Error_Severity],
3150			[Error_State],
3151			[Error_Procedure],
3152			[Error_Line],
3153			[UserName],
3154			[HostName],
3155			[Time_Stamp]
3156		)
3157	SELECT		[Error_Number],
3158				[Error_Message],
3159				[Error_Severity],
3160				[Error_State],
3161				[Error_Procedure],
3162				[Error_Line],
3163				[UserName],
3164				[HostName],
3165				[Time_Stamp]
3166	FROM @LogErrors;
3167	END
3168
3169	--16/ requirement for app_decision, app_status
3170	/*Update Status of GM, no need check Create Date and Time, just synchronize data if data was not updated*/
3171
3172	
3173	UPDATE c
3174	SET c.KEY1 = a.APP_STATUS 
3175	FROM powercampustest.dbo.ACADEMIC a 
3176	JOIN GoldMine_test.dbo.CONTACT1 c	ON a.PEOPLE_ID = c.KEY4
3177	WHERE a.APP_DECISION = 'ACT' --need codes, might be ACPT
3178	AND a.APP_STATUS = '5x-xxx' --need codes --Final GM_PC Map shows 5x-XXX, 6x-XXX, need to find the code for confirmed
3179	--and powercampustest.dbo.people CREATE_TIME, CREATE_DATE in last 15 minutes
3180	AND a.APP_STATUS <> c.KEY1;
3181	
3182
3183	/*Rebuild Index*/
3184	BEGIN
3185		ALTER INDEX ALL ON powercampustest.dbo.ADDRESS REORGANIZE
3186		ALTER INDEX ALL ON powercampustest.dbo.[STOPLIST] REORGANIZE
3187		ALTER INDEX ALL ON powercampustest.dbo.PEOPLE REORGANIZE
3188	END
3189	
3190END TRY
3191BEGIN CATCH
3192	ROLLBACK;
3193	INSERT INTO LogErrors
3194			(
3195				[Error_Number],
3196				[Error_Message],
3197				[Error_Severity],
3198				[Error_State],
3199				[Error_Procedure],
3200				[Error_Line],
3201				[UserName],
3202				[HostName],
3203				[Time_Stamp]
3204			)
3205				SELECT ISNULL(ERROR_NUMBER(),0),
3206					ISNULL(ERROR_MESSAGE(),'NULL Message'),
3207					ISNULL(ERROR_SEVERITY(),0),	
3208					ISNULL(ERROR_STATE(),1),	
3209					ISNULL(ERROR_PROCEDURE(),''),			 
3210					ISNULL(ERROR_LINE(), 0), 
3211					SUSER_SNAME(), 
3212					HOST_NAME(), 
3213					GETDATE();
3214END CATCH
3215
3216/*
3217SELECT * FROM LogErrors
3218*/