5pXHNCwz

1BEGIN
2
3DECLARE
4--local variable
5	@cnt				int,
6
7--first cursor variables
8	@ffcompanycode		varchar(3),
9	@ffjobnumber		varchar(10),
10	@ffphasecode		varchar(12),
11	@ffcosttype			varchar(1),
12	@fftrantypecode		varchar(2),
13	@fftrandatetext		varchar(8),
14	@ffreference1		varchar(15),
15	@ffreference2		varchar(20),
16	@ffdetailsequence	varchar(10),
17	@ffdescription		varchar(30),
18	@ffinvoicedate		datetime,
19	@ffponumber			varchar(15),
20	@ffquantity			decimal(12,2),
21	@fftotalhours		decimal(12,2),
22	@fftranamount		decimal(12,2),
23	@ffemployeename		varchar(30),
24	@ffvendorname		varchar(30),
25	@ffunitofmeasure	varchar(3),
26
27	@communityname		varchar(100),
28	@projectmanager		varchar(100),
29	@statuscode			varchar(1),
30
31--second cursor variable
32	@brecordid			int
33
34--create temp table before declaring cursor(s) using it
35IF EXISTS (
36  SELECT NAME
37  FROM SYSOBJECTS
38  WHERE NAME= 'tmp_JCHupdates'
39  AND TYPE = 'U')
40BEGIN 
41DROP TABLE dbo.tmp_JCHupdates
42END
43
44create table tmp_JCHupdates (
45	Company_Code	varchar(3),
46	Job_Number		varchar(10),
47	Phase_Code		varchar(12),
48	Cost_Type		varchar(1),
49	Tran_Type_Code	varchar(2),
50	Tran_Date_Text	varchar(8),
51	Reference_1		varchar(15),
52	Reference_2		varchar(20),
53	Detail_Sequence varchar(10),
54	Description		varchar(30),
55	Invoice_Date	datetime,
56	Po_Number		varchar(15),
57	Quantity		decimal(12,2),
58	Total_Hours		decimal(12,2),
59	Tran_Amount		decimal(12,2),
60	Employee_Name	varchar(30),
61	Vendor_Name		varchar(30),
62	Unit_of_Measure varchar(3)
63)
64
65insert into tmp_JCHupdates  -- DEH
66select
67company_code, job_number, phase_code, cost_type,
68tran_type_code, tran_date_text, reference_1, reference_2,
69detail_sequence, description, invoice_date, po_number,
70quantity, total_hours, tran_amount, employee_name,
71vendor_name, unit_of_measure
72from forefront.dbo.jc_transaction_history_mc f
73where company_code = 'DEH'
74AND job_number LIKE '___.______'
75AND job_number not LIKE '___._ILLAB'
76AND job_number not LIKE '___.5_____'
77AND job_number not LIKE 'RUC.______'
78and job_number not like 'RUK.______'
79and len(ltrim(rtrim(job_number))) = 10  -- post conversion
80and ltrim(rtrim(job_number)) not like '___.___.__'  -- pre conversion
81and len(ltrim(rtrim(phase_code))) <= 6  -- pre conversion
82and ltrim(rtrim(job_number)) not in (select ltrim(rtrim(pf.funding_name))
83								from cognito.dbo.v_projectfunding pf, cognito.dbo.v_project p
84								where pf.project_id = p.project_id
85								and p.project_status in ( 'Complete', 'Cancelled')
86								and pf.funding_name is not null
87								)
88and not exists (
89	select b.company_code, b.job_number, b.phase_code, b.cost_type,
90	b.tran_type_code, b.tran_date_text, b.reference_1, b.reference_2,
91	b.detail_sequence, b.description, b.invoice_date, b.po_number,
92	b.quantity, b.total_hours, b.tran_amount, b.employee_name,
93	b.vendor_name, b.unit_of_measure
94	from job_cost_history b
95	where ltrim(rtrim(f.company_code)) = ltrim(rtrim(b.company_code))
96	and ltrim(rtrim(f.job_number)) = ltrim(rtrim(b.job_number))
97	and ltrim(rtrim(f.phase_code)) = ltrim(rtrim(b.phase_code))
98	and ltrim(rtrim(f.cost_type)) = ltrim(rtrim(b.cost_type))
99	and ltrim(rtrim(f.tran_type_code)) = ltrim(rtrim(b.tran_type_code))
100	and ltrim(rtrim(f.tran_date_text)) = ltrim(rtrim(b.tran_date_text))
101	and ltrim(rtrim(f.reference_1)) = ltrim(rtrim(b.reference_1))
102	and ltrim(rtrim(f.reference_2)) = ltrim(rtrim(b.reference_2))
103	and ltrim(rtrim(f.detail_sequence)) = ltrim(rtrim(b.detail_sequence))
104	and ltrim(rtrim(f.description)) = ltrim(rtrim(b.description))
105	and isnull(f.invoice_date, '1900-01-01') = isnull(b.invoice_date, '1900-01-01')
106	and ltrim(rtrim(f.po_number)) = ltrim(rtrim(b.po_number))
107	and f.quantity = b.quantity
108	and f.total_hours = b.total_hours
109	and f.tran_amount = b.tran_amount
110	and ltrim(rtrim(f.employee_name)) = ltrim(rtrim(b.employee_name))
111	and ltrim(rtrim(f.vendor_name)) = ltrim(rtrim(b.vendor_name))
112	and ltrim(rtrim(f.unit_of_measure)) = ltrim(rtrim(b.unit_of_measure))
113	)
114
115insert into tmp_JCHupdates  --RUC
116select
117company_code, job_number, phase_code, cost_type,
118tran_type_code, tran_date_text, reference_1, reference_2,
119detail_sequence, description, invoice_date, po_number,
120quantity, total_hours, tran_amount, employee_name,
121vendor_name, unit_of_measure
122from forefront.dbo.jc_transaction_history_mc f
123where company_code = 'RUC'
124and not exists (
125	select b.company_code, b.job_number, b.phase_code, b.cost_type,
126	b.tran_type_code, b.tran_date_text, b.reference_1, b.reference_2,
127	b.detail_sequence, b.description, b.invoice_date, b.po_number,
128	b.quantity, b.total_hours, b.tran_amount, b.employee_name,
129	b.vendor_name, b.unit_of_measure
130	from job_cost_history b
131	where ltrim(rtrim(f.company_code)) = ltrim(rtrim(b.company_code))
132	and ltrim(rtrim(f.job_number)) = ltrim(rtrim(b.job_number))
133	and ltrim(rtrim(f.phase_code)) = ltrim(rtrim(b.phase_code))
134	and ltrim(rtrim(f.cost_type)) = ltrim(rtrim(b.cost_type))
135	and ltrim(rtrim(f.tran_type_code)) = ltrim(rtrim(b.tran_type_code))
136	and ltrim(rtrim(f.tran_date_text)) = ltrim(rtrim(b.tran_date_text))
137	and ltrim(rtrim(f.reference_1)) = ltrim(rtrim(b.reference_1))
138	and ltrim(rtrim(f.reference_2)) = ltrim(rtrim(b.reference_2))
139	and ltrim(rtrim(f.detail_sequence)) = ltrim(rtrim(b.detail_sequence))
140	and ltrim(rtrim(f.description)) = ltrim(rtrim(b.description))
141	and isnull(f.invoice_date, '1900-01-01') = isnull(b.invoice_date, '1900-01-01')
142	and ltrim(rtrim(f.po_number)) = ltrim(rtrim(b.po_number))
143	and f.quantity = b.quantity
144	and f.total_hours = b.total_hours
145	and f.tran_amount = b.tran_amount
146	and ltrim(rtrim(f.employee_name)) = ltrim(rtrim(b.employee_name))
147	and ltrim(rtrim(f.vendor_name)) = ltrim(rtrim(b.vendor_name))
148	and ltrim(rtrim(f.unit_of_measure)) = ltrim(rtrim(b.unit_of_measure))
149	)
150
151--first cursor, record by record of anything new or changed
152
153declare job_cost_history_cursor cursor
154for
155select
156company_code, job_number, phase_code, cost_type,
157tran_type_code, tran_date_text, reference_1, reference_2,
158detail_sequence, description, invoice_date, po_number,
159quantity, total_hours, tran_amount, employee_name,
160vendor_name, unit_of_measure
161from tmp_JCHupdates
162
163open job_cost_history_cursor  
164  
165fetch next from job_cost_history_cursor  
166into @ffcompanycode, @ffjobnumber, @ffphasecode, @ffcosttype,	
167@fftrantypecode, @fftrandatetext, @ffreference1, @ffreference2,
168@ffdetailsequence, @ffdescription, @ffinvoicedate, @ffponumber,
169@ffquantity, @fftotalhours, @fftranamount, @ffemployeename,
170@ffvendorname, @ffunitofmeasure
171
172while @@fetch_status = 0  
173begin  
174
175--get communityname
176IF @ffcompanycode = 'DEH'
177BEGIN
178	select top 1 @communityname = community_name 
179	from cognito.dbo.community
180	where community_code = left(ltrim(rtrim(@ffjobnumber)), 3)
181END
182ELSE IF @ffcompanycode =  'RUC'
183BEGIN
184	select top 1 @communityname = community_name 
185	from cognito.dbo.community
186	where community_code = substring(ltrim(rtrim(@ffjobnumber)),5,3)
187END
188ELSE 
189BEGIN
190	select @communityname = 'UNKNOWN'
191END
192
193
194--get projectmanager
195select top 1 @projectmanager = contact_name
196from cognito.dbo.v_projectteam
197where project_id = (select project_id
198	from cognito.dbo.v_projectfunding
199	where funding_name = ltrim(rtrim(@ffjobnumber))
200				)
201and role_name = 'Project Manager'
202
203IF @projectmanager is null
204BEGIN
205	if @ffjobnumber like 'EHS.______' 
206	begin
207		select @projectmanager = 'EHS'
208	end
209	else if @ffjobnumber like 'RUC.______' 
210	begin
211		select @projectmanager = 'ARUC'
212	end
213	else if @ffjobnumber like 'TUS.______' 
214	begin
215		select @projectmanager = 'TUS'
216	end
217	else if @ffjobnumber like '___.5__HAF' 
218	begin
219		select @projectmanager = 'HAF'
220	end
221	else if @ffjobnumber like '___.VILLAB' 
222	begin
223		select @projectmanager = 'VILLAB'
224	end
225	else 
226	begin
227		select @projectmanager = 'UNKNOWN'
228	end
229END
230
231	
232--get statuscode
233IF @ffcompanycode = 'DEH'
234BEGIN
235	select @statuscode = ltrim(rtrim(jm.status_code))
236	from forefront.dbo.jc_job_master_mc jm
237	where ltrim(rtrim(@ffjobnumber)) = ltrim(rtrim(jm.job_number))
238	and jm.company_code = 'DEH'
239END
240ELSE IF @ffcompanycode = 'RUC'
241BEGIN
242	select @statuscode = ltrim(rtrim(jm.status_code))
243	from forefront.dbo.jc_job_master_mc jm
244	where ltrim(rtrim(@ffjobnumber)) = ltrim(rtrim(jm.job_number))
245	and jm.company_code = 'RUC'
246END
247ELSE 
248BEGIN
249	select @statuscode = 'U'
250END
251
252
253--new or update?  check pk
254select @cnt = count(record_id)
255from job_cost_history
256where ltrim(rtrim(company_code)) = ltrim(rtrim(@ffcompanycode))
257and ltrim(rtrim(job_number)) = ltrim(rtrim(@ffjobnumber))
258and ltrim(rtrim(phase_code)) = ltrim(rtrim(@ffphasecode))
259and ltrim(rtrim(cost_type)) = ltrim(rtrim(@ffcosttype))
260and ltrim(rtrim(tran_type_code)) = ltrim(rtrim(@fftrantypecode)) 
261and ltrim(rtrim(tran_date_text)) = ltrim(rtrim(@fftrandatetext)) 
262and ltrim(rtrim(reference_1)) = ltrim(rtrim(@ffreference1)) 
263and ltrim(rtrim(reference_2)) = ltrim(rtrim(@ffreference2))
264and ltrim(rtrim(detail_sequence)) = ltrim(rtrim(@ffdetailsequence))
265
266if @cnt = 0 -- then new record based on pk
267begin
268	insert into job_cost_history (
269	community_name, project_manager,
270	company_code, job_number, phase_code, cost_type,
271	tran_type_code, tran_date_text, reference_1, reference_2,
272	detail_sequence, status_code,
273	description, invoice_date, po_number,
274	quantity, total_hours, tran_amount, employee_name,
275	vendor_name, unit_of_measure, 
276	comments, created, updated, status
277	)
278	values  (
279	@communityname, @projectmanager,
280	ltrim(rtrim(@ffcompanycode)), ltrim(rtrim(@ffjobnumber)), ltrim(rtrim(@ffphasecode)), ltrim(rtrim(@ffcosttype)),	
281	ltrim(rtrim(@fftrantypecode)), ltrim(rtrim(@fftrandatetext)), ltrim(rtrim(@ffreference1)), ltrim(rtrim(@ffreference2)),
282	ltrim(rtrim(@ffdetailsequence)), @statuscode,
283	ltrim(rtrim(@ffdescription)), @ffinvoicedate, ltrim(rtrim(@ffponumber)),
284	@ffquantity, @fftotalhours, @fftranamount, ltrim(rtrim(@ffemployeename)),
285	ltrim(rtrim(@ffvendorname)), ltrim(rtrim(@ffunitofmeasure)),
286	null, getdate(), getdate(), 'New'
287	)
288end
289
290else if @cnt = 1  -- then changed data based on pk
291begin
292	update job_cost_history
293	set community_name = @communityname,
294	project_manager = @projectmanager,
295	status_code = @statuscode,
296	description = ltrim(rtrim(@ffdescription)),
297	invoice_date = @ffinvoicedate,
298	po_number = ltrim(rtrim(@ffponumber)),
299	quantity = @ffquantity,
300	total_hours = @fftotalhours,
301	tran_amount = @fftranamount,
302	employee_name = ltrim(rtrim(@ffemployeename)),
303	vendor_name = ltrim(rtrim(@ffvendorname)),
304	unit_of_measure = ltrim(rtrim(@ffunitofmeasure)),
305	updated = getdate(),
306	status = 'Update'
307	where ltrim(rtrim(company_code)) = ltrim(rtrim(@ffcompanycode))
308	and ltrim(rtrim(job_number)) = ltrim(rtrim(@ffjobnumber))
309	and ltrim(rtrim(phase_code)) = ltrim(rtrim(@ffphasecode))
310	and ltrim(rtrim(cost_type)) = ltrim(rtrim(@ffcosttype))
311	and ltrim(rtrim(tran_type_code)) = ltrim(rtrim(@fftrantypecode)) 
312	and ltrim(rtrim(tran_date_text)) = ltrim(rtrim(@fftrandatetext)) 
313	and ltrim(rtrim(reference_1)) = ltrim(rtrim(@ffreference1)) 
314	and ltrim(rtrim(reference_2)) = ltrim(rtrim(@ffreference2))
315	and ltrim(rtrim(detail_sequence)) = ltrim(rtrim(@ffdetailsequence))
316end
317
318else -- dont know what this is
319begin
320	print 'In else'
321end
322
323select @cnt = null
324
325select @ffcompanycode = null
326select @ffjobnumber = null
327select @ffphasecode = null 
328select @ffcosttype = null	
329select @fftrantypecode = null 
330select @fftrandatetext = null
331select @ffreference1 = null 
332select @ffreference2 = null
333select @ffdetailsequence = null 
334select @ffdescription = null 
335select @ffinvoicedate = null 
336select @ffponumber = null
337select @ffquantity = null
338select @fftotalhours = null 
339select @fftranamount = null 
340select @ffemployeename = null
341select @ffvendorname = null 
342select @ffunitofmeasure = null
343
344select @communityname = null
345select @projectmanager = null
346select @statuscode = null
347
348fetch next from job_cost_history_cursor  
349into @ffcompanycode, @ffjobnumber, @ffphasecode, @ffcosttype,	
350@fftrantypecode, @fftrandatetext, @ffreference1, @ffreference2,
351@ffdetailsequence, @ffdescription, @ffinvoicedate, @ffponumber,
352@ffquantity, @fftotalhours, @fftranamount, @ffemployeename,
353@ffvendorname, @ffunitofmeasure
354  
355end -- while for cursor
356  
357close job_cost_history_cursor  
358deallocate job_cost_history_cursor
359
360--second cursor, delete all BI records no longer in Spectrum
361declare delete_job_cost_history_cursor cursor
362for
363select
364b.record_id 
365from job_cost_history b
366where not exists (
367	select f.company_code, f.job_number, f.phase_code, f.cost_type,
368	f.tran_type_code, f.tran_date_text, f.reference_1, f.reference_2,
369	f.detail_sequence, f.description, f.invoice_date, f.po_number,
370	f.quantity, f.total_hours, f.tran_amount, f.employee_name,
371	f.vendor_name, f.unit_of_measure
372	from forefront.dbo.jc_transaction_history_mc f
373	where ltrim(rtrim(f.company_code)) = ltrim(rtrim(b.company_code))
374	and ltrim(rtrim(f.job_number)) = ltrim(rtrim(b.job_number))
375	and ltrim(rtrim(f.phase_code)) = ltrim(rtrim(b.phase_code))
376	and ltrim(rtrim(f.cost_type)) = ltrim(rtrim(b.cost_type))
377	and ltrim(rtrim(f.tran_type_code)) = ltrim(rtrim(b.tran_type_code))
378	and ltrim(rtrim(f.tran_date_text)) = ltrim(rtrim(b.tran_date_text))
379	and ltrim(rtrim(f.reference_1)) = ltrim(rtrim(b.reference_1))
380	and ltrim(rtrim(f.reference_2)) = ltrim(rtrim(b.reference_2))
381	and ltrim(rtrim(f.detail_sequence)) = ltrim(rtrim(b.detail_sequence))
382	and ltrim(rtrim(f.description)) = ltrim(rtrim(b.description))
383	and isnull(f.invoice_date, '1900-01-01') = isnull(b.invoice_date, '1900-01-01')
384	and ltrim(rtrim(f.po_number)) = ltrim(rtrim(b.po_number))
385	and f.quantity = b.quantity
386	and f.total_hours = b.total_hours
387	and f.tran_amount = b.tran_amount
388	and ltrim(rtrim(f.employee_name)) = ltrim(rtrim(b.employee_name))
389	and ltrim(rtrim(f.vendor_name)) = ltrim(rtrim(b.vendor_name))
390	and ltrim(rtrim(f.unit_of_measure)) = ltrim(rtrim(b.unit_of_measure))
391	)
392
393open delete_job_cost_history_cursor  
394  
395fetch next from delete_job_cost_history_cursor  
396into @brecordid
397  
398while @@fetch_status = 0  
399begin  
400
401print 'deleting record_id ' + @brecordid
402
403delete from job_cost_history
404where record_id = @brecordid
405
406fetch next from delete_job_cost_history_cursor  
407into @brecordid
408  
409end -- while for cursor 
410  
411close delete_job_cost_history_cursor  
412deallocate delete_job_cost_history_cursor
413
414END  -- proc