[Home] [Help]
PACKAGE BODY: APPS.PN_CC_SYNC_PKG
Source
1 PACKAGE BODY PN_CC_SYNC_PKG AS
2 -- $Header: PNCCSYNB.pls 120.5 2007/08/10 05:49:06 hrodda ship $
3
4 /*===========================================================================+
5 | PROCEDURE
6 | cc_sync_with_hr
7 |
8 | DESCRIPTION
9 | Main procedure for concurrent program 'Cost Center Synchronization
10 | with HR'
11 |
12 | SCOPE - PUBLIC
13 |
14 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
15 | pnp_util_func.get_segment_column_name
16 | pnp_util_func.get_location_code
17 | pn_space_assign_emp_pkg.update_row
18 |
19 | ARGUMENTS : IN:
20 | p_as_of_date
21 | p_locn_type
22 | p_locn_code_from
23 | p_locn_code_to
24 | p_emp_cost_center
25 | OUT:
26 | Std. concurrent program out params (errbuf, retcode)
27 |
28 |
29 | MODIFICATION HISTORY
30 |
31 | 13-NOV-03 Vikas Mehta Created
32 | 26-OCT-05 Hareesha o ATG mandated changes for SQL literals using
33 | dbms_sql.
34 | 04-APR-06 Hareesha o Bug 5119241 Modified call to
35 | PN_SPACE_ASSIGN_EMP_PKG.Update_Row to pass
36 | new CC.
37 | 30-JUN-06 Hareesha o Bug #5262982 Select org_id too in the query
38 | along with all other columns.The new record
39 | created was not being populated with org_id.
40 | 10-AUG-07 Hareesha o Bug 6168505 , this package invalid at macerich's instance,
41 | because, asg_rec is referring to baseview rowtype,
42 | instead of _all rowtype.
43 +===========================================================================*/
44
45 PROCEDURE cc_sync_with_hr (
46 errbuf OUT NOCOPY VARCHAR2,
47 retcode OUT NOCOPY VARCHAR2,
48 p_as_of_date IN VARCHAR2,
49 p_locn_type IN pn_locations.location_type_lookup_code%TYPE,
50 p_locn_code_from IN pn_locations.location_code%TYPE,
51 p_locn_code_to IN pn_locations.location_code%TYPE,
52 p_emp_cost_center IN pn_space_assign_emp.cost_center_code%TYPE
53 ) IS
54
55
56 asg_rec pn_space_assign_emp_ALL%ROWTYPE;
57 l_query VARCHAR2(2000);
58 l_where_clause VARCHAR2(2000);
59 l_where_clause_loc VARCHAR2(2000) := NULL;
60 l_count_total NUMBER := 0;
61 l_count_success NUMBER := 0;
62 l_count_failure NUMBER := 0;
63 l_commit_count NUMBER := 0;
64 l_log_context VARCHAR2(2000);
65 l_desc VARCHAR2(100) := 'pn_cc_sync_pkg.cc_sync_with_hr';
66 l_as_of_date DATE;
67 l_cost_center VARCHAR2(30) := NULL;
68 l_person_id pn_space_assign_emp.person_id%TYPE := 0 ;
69 l_column_name VARCHAR2(30) := NULL;
70 l_last_person_processed pn_space_assign_emp.person_id%TYPE := 0 ;
71 l_last_updated_by pn_space_assign_emp.last_updated_by%TYPE ;
72 l_last_update_login pn_space_assign_emp.last_update_login%TYPE ;
73 l_err_msg1 VARCHAR2(2000);
74 l_source_id VARCHAR2(30) := 'PNCCSYNC';
75 l_batch_size NUMBER := 5000 ;
76 l_emp_name per_people_f.full_name%TYPE;
77 l_location_code pn_locations.location_code%TYPE;
78 l_success_msg VARCHAR2(1000) := NULL;
79 l_failure_msg VARCHAR2(1000) := NULL;
80 i NUMBER := 0;
81 l_errbuf VARCHAR2(200) := NULL ;
82 l_message VARCHAR2(1000) := NULL ;
83 l_out_date DATE ;
84 l_cursor INTEGER;
85 l_rows INTEGER;
86 l_count INTEGER;
87 x_as_of_date DATE;
88 l_locn_code_from VARCHAR2(90);
89 l_locn_code_to VARCHAR2(90);
90 l_locn_type VARCHAR2(30);
91 l_emp_cost_center VARCHAR2(30);
92 x_last_person_processed pn_space_assign_emp.person_id%TYPE := 0 ;
93
94
95 CURSOR get_emp_name (p_employee_id IN NUMBER, p_as_of_date DATE) IS
96 SELECT
97 FULL_NAME
98 FROM
99 PER_PEOPLE_F
100 WHERE person_id = p_employee_id
101 AND TRUNC(p_as_of_date) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE
102 AND EMPLOYEE_NUMBER IS NOT NULL ;
103
104 TYPE failed_tbl_type IS TABLE OF VARCHAR2(1000)
105 INDEX BY BINARY_INTEGER;
106 failure_table failed_tbl_type;
107
108 TYPE success_tbl_type IS TABLE OF VARCHAR2(1000)
109 INDEX BY BINARY_INTEGER;
110 success_table success_tbl_type;
111
112
113 BEGIN
114
115
116 pnp_debug_pkg.debug(l_desc||' (+)');
117
118
119 --Print all input parameters
120
121 fnd_message.set_name ('PN','PN_HRSYNC_INP_PARAMS');
122 fnd_message.set_token ('DATE', to_char(fnd_date.canonical_to_date(p_as_of_date),'mm/dd/yyyy'));
123 fnd_message.set_token ('LOC_TYPE', p_locn_type);
124 fnd_message.set_token ('LOC_CODE_FROM', p_locn_code_from);
125 fnd_message.set_token ('LOC_CODE_TO', p_locn_code_to);
126 fnd_message.set_token ('COST_CENTER', p_emp_cost_center);
127 pnp_debug_pkg.put_log_msg(fnd_message.get);
128
129 l_log_context := ' Getting as of Date..';
130 l_as_of_date := trunc(fnd_date.canonical_to_date(p_as_of_date));
131
132 l_log_context := 'Initializing WHO variables..';
133 l_last_updated_by := nvl(fnd_profile.VALUE ('USER_ID'), 0);
134 l_last_update_login := nvl(fnd_profile.value('LOGIN_ID'),0);
135
136 l_log_context := ' Getting segment column ' ;
137 l_column_name := pnp_util_func.get_segment_column_name(pn_mo_cache_utils.get_current_org_id);
138
139 l_log_context := ' Defining sub-query for location ';
140
141 l_cursor := dbms_sql.open_cursor;
142 x_as_of_date := l_as_of_date;
143
144 l_where_clause_loc := ' WHERE :x_as_of_date between trunc(active_start_date) and trunc(active_end_date) ';
145
146 /* Construct sub-query for location based on input parameters */
147
148 IF p_locn_type IS NULL THEN
149
150 /* No need to traverse the hierarchy.
151 Construct where clause based on location_code range */
152
153 IF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NOT NULL THEN
154 l_locn_code_from := p_locn_code_from;
155 l_locn_code_to := p_locn_code_to;
156 l_where_clause_loc := l_where_clause_loc || ' AND location_code between :l_locn_code_from AND :l_locn_code_to ';
157
158 ELSIF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NULL THEN
159 l_locn_code_from := p_locn_code_from;
160 l_where_clause_loc := l_where_clause_loc || ' AND location_code >= :l_locn_code_from ';
161
162 ELSIF p_locn_code_from IS NULL AND p_locn_code_to IS NOT NULL THEN
163 l_locn_code_to := p_locn_code_to;
164 l_where_clause_loc := l_where_clause_loc || ' AND location_code <= :l_locn_code_to ';
165
166 END IF;
167
168 ELSE
169
170 /* Need to traverse hierarchy to identify children.
171 Apply location_code range for identifying top nodes only */
172
173 l_locn_type := p_locn_type;
174 l_where_clause_loc := l_where_clause_loc ||
175 ' START WITH location_type_lookup_code = :l_locn_type ';
176
177 IF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NOT NULL THEN
178 l_locn_code_from := p_locn_code_from;
179 l_locn_code_to := p_locn_code_to;
180 l_where_clause_loc := l_where_clause_loc || ' AND location_code between :l_locn_code_from AND :l_locn_code_to ';
181
182 ELSIF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NULL THEN
183 l_locn_code_from := p_locn_code_from;
184 l_where_clause_loc := l_where_clause_loc || ' AND location_code >= :l_locn_code_from ';
185
186 ELSIF p_locn_code_from IS NULL AND p_locn_code_to IS NOT NULL THEN
187 l_locn_code_to := p_locn_code_to;
188 l_where_clause_loc := l_where_clause_loc || ' AND location_code <= :l_locn_code_to ';
189
190 END IF;
191
192 l_where_clause_loc := l_where_clause_loc ||
193 ' AND :x_as_of_date BETWEEN trunc(active_start_date) AND trunc(active_end_date)
194 CONNECT BY PRIOR location_id = parent_location_id ';
195
196 END IF;
197
198 /* Construct query for main cursor */
199
200 l_log_context := ' Defining query for cursor ';
201
202 l_query :=
203 'SELECT
204 EMP_SPACE_ASSIGN_ID
205 ,LOCATION_ID
206 ,PERSON_ID
207 ,PROJECT_ID
208 ,TASK_ID
209 ,EMP_ASSIGN_START_DATE
210 ,EMP_ASSIGN_END_DATE
211 ,COST_CENTER_CODE
212 ,ALLOCATED_AREA_PCT
213 ,ALLOCATED_AREA
214 ,UTILIZED_AREA
215 ,EMP_SPACE_COMMENTS
216 ,LAST_UPDATE_DATE
217 ,LAST_UPDATED_BY
218 ,CREATION_DATE
219 ,CREATED_BY
220 ,LAST_UPDATE_LOGIN
221 ,ATTRIBUTE_CATEGORY
222 ,ATTRIBUTE1
223 ,ATTRIBUTE2
224 ,ATTRIBUTE3
225 ,ATTRIBUTE4
226 ,ATTRIBUTE5
227 ,ATTRIBUTE6
228 ,ATTRIBUTE7
229 ,ATTRIBUTE8
230 ,ATTRIBUTE9
231 ,ATTRIBUTE10
232 ,ATTRIBUTE11
233 ,ATTRIBUTE12
234 ,ATTRIBUTE13
235 ,ATTRIBUTE14
236 ,ATTRIBUTE15
237 ,ORG_ID
238 FROM PN_SPACE_ASSIGN_EMP ';
239
240 x_last_person_processed := l_last_person_processed ;
241 l_where_clause := ' WHERE :x_as_of_date BETWEEN trunc(emp_assign_start_date) AND
242 trunc(NVL(emp_assign_end_date, TO_DATE(''12/31/4712'', ''MM/DD/YYYY'')))
243 AND person_id >= :x_last_person_processed' ;
244
245 IF p_emp_cost_center IS NOT NULL THEN
246 l_emp_cost_center := p_emp_cost_center;
247 l_where_clause := l_where_clause ||
248 ' AND cost_center_code = :l_emp_cost_center ';
249
250 END IF;
251
252 l_where_clause := l_where_clause || ' AND location_id IN (SELECT location_id FROM pn_locations ' ;
253 l_where_clause := l_where_clause || l_where_clause_loc;
254 l_where_clause := l_where_clause|| ' )';
255 l_where_clause := l_where_clause || ' ORDER BY person_id ';
256 l_query := l_query || l_where_clause ;
257
258 /* uncomment for debugging purposes */
259 /*pnp_debug_pkg.log('l_query is : '|| l_query);*/
260
261 dbms_sql.parse(l_cursor, l_query, dbms_sql.native);
262
263 dbms_sql.bind_variable
264 (l_cursor,'x_as_of_date',x_as_of_date );
265
266 dbms_sql.bind_variable
267 (l_cursor,'x_last_person_processed',x_last_person_processed );
268
269 IF p_emp_cost_center IS NOT NULL THEN
270 dbms_sql.bind_variable
271 (l_cursor,'l_emp_cost_center',l_emp_cost_center );
272 END IF;
273
274 IF p_locn_type IS NULL THEN
275 IF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NOT NULL THEN
276 dbms_sql.bind_variable
277 (l_cursor,'l_locn_code_from',l_locn_code_from );
278 dbms_sql.bind_variable
279 (l_cursor,'l_locn_code_to',l_locn_code_to );
280 ELSIF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NULL THEN
281 dbms_sql.bind_variable
282 (l_cursor,'l_locn_code_from',l_locn_code_from );
283 ELSIF p_locn_code_from IS NULL AND p_locn_code_to IS NOT NULL THEN
284 dbms_sql.bind_variable
285 (l_cursor,'l_locn_code_to',l_locn_code_to );
286 END IF;
287 ELSE
288 dbms_sql.bind_variable
289 (l_cursor,'l_locn_type',l_locn_type );
290 dbms_sql.bind_variable
291 (l_cursor,'x_as_of_date',x_as_of_date );
292
293 IF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NOT NULL THEN
294 dbms_sql.bind_variable
295 (l_cursor,'l_locn_code_from',l_locn_code_from );
296 dbms_sql.bind_variable
297 (l_cursor,'l_locn_code_to',l_locn_code_to );
298 ELSIF p_locn_code_from IS NOT NULL AND p_locn_code_to IS NULL THEN
299 dbms_sql.bind_variable
300 (l_cursor,'l_locn_code_from',l_locn_code_from );
301 ELSIF p_locn_code_from IS NULL AND p_locn_code_to IS NOT NULL THEN
302 dbms_sql.bind_variable
303 (l_cursor,'l_locn_code_to',l_locn_code_to );
304 END IF;
305 END IF;
306
307 dbms_sql.define_column (l_cursor, 1,asg_rec.EMP_SPACE_ASSIGN_ID);
308 dbms_sql.define_column (l_cursor, 2,asg_rec.LOCATION_ID);
309 dbms_sql.define_column (l_cursor, 3,asg_rec.PERSON_ID);
310 dbms_sql.define_column (l_cursor, 4,asg_rec.PROJECT_ID);
311 dbms_sql.define_column (l_cursor, 5,asg_rec.TASK_ID);
312 dbms_sql.define_column (l_cursor, 6,asg_rec.EMP_ASSIGN_START_DATE);
313 dbms_sql.define_column (l_cursor, 7,asg_rec.EMP_ASSIGN_END_DATE);
314 dbms_sql.define_column (l_cursor, 8,asg_rec.COST_CENTER_CODE,30);
315 dbms_sql.define_column (l_cursor, 9,asg_rec.ALLOCATED_AREA_PCT);
316 dbms_sql.define_column (l_cursor, 10,asg_rec.ALLOCATED_AREA);
317 dbms_sql.define_column (l_cursor, 11,asg_rec.UTILIZED_AREA);
318 dbms_sql.define_column (l_cursor, 12,asg_rec.EMP_SPACE_COMMENTS,2000);
319 dbms_sql.define_column (l_cursor, 13,asg_rec.LAST_UPDATE_DATE);
320 dbms_sql.define_column (l_cursor, 14,asg_rec.LAST_UPDATED_BY);
321 dbms_sql.define_column (l_cursor, 15,asg_rec.CREATION_DATE);
322 dbms_sql.define_column (l_cursor, 16,asg_rec.CREATED_BY);
323 dbms_sql.define_column (l_cursor, 17,asg_rec.LAST_UPDATE_LOGIN);
324 dbms_sql.define_column (l_cursor, 18,asg_rec.ATTRIBUTE_CATEGORY,30);
325 dbms_sql.define_column (l_cursor, 19,asg_rec.ATTRIBUTE1,150);
326 dbms_sql.define_column (l_cursor, 20,asg_rec.ATTRIBUTE2,150);
327 dbms_sql.define_column (l_cursor, 21,asg_rec.ATTRIBUTE3,150);
328 dbms_sql.define_column (l_cursor, 22,asg_rec.ATTRIBUTE4,150);
329 dbms_sql.define_column (l_cursor, 23,asg_rec.ATTRIBUTE5,150);
330 dbms_sql.define_column (l_cursor, 24,asg_rec.ATTRIBUTE6,150);
331 dbms_sql.define_column (l_cursor, 25,asg_rec.ATTRIBUTE7,150);
332 dbms_sql.define_column (l_cursor, 26,asg_rec.ATTRIBUTE8,150);
333 dbms_sql.define_column (l_cursor, 27,asg_rec.ATTRIBUTE9,150);
334 dbms_sql.define_column (l_cursor, 28,asg_rec.ATTRIBUTE10,150);
335 dbms_sql.define_column (l_cursor, 29,asg_rec.ATTRIBUTE11,150);
336 dbms_sql.define_column (l_cursor, 30,asg_rec.ATTRIBUTE12,150);
337 dbms_sql.define_column (l_cursor, 31,asg_rec.ATTRIBUTE13,150);
338 dbms_sql.define_column (l_cursor, 32,asg_rec.ATTRIBUTE14,150);
339 dbms_sql.define_column (l_cursor, 33,asg_rec.ATTRIBUTE15,150);
340 dbms_sql.define_column (l_cursor, 34,asg_rec.ORG_ID);
341
342 l_rows := dbms_sql.execute(l_cursor);
343
344 LOOP
345
346 l_count := dbms_sql.fetch_rows( l_cursor );
347
348 EXIT WHEN l_count <> 1;
349
350 dbms_sql.column_value (l_cursor, 1,asg_rec.EMP_SPACE_ASSIGN_ID);
351 dbms_sql.column_value (l_cursor, 2,asg_rec.LOCATION_ID);
352 dbms_sql.column_value (l_cursor, 3,asg_rec.PERSON_ID);
353 dbms_sql.column_value (l_cursor, 4,asg_rec.PROJECT_ID);
354 dbms_sql.column_value (l_cursor, 5,asg_rec.TASK_ID);
355 dbms_sql.column_value (l_cursor, 6,asg_rec.EMP_ASSIGN_START_DATE);
356 dbms_sql.column_value (l_cursor, 7,asg_rec.EMP_ASSIGN_END_DATE);
357 dbms_sql.column_value (l_cursor, 8,asg_rec.COST_CENTER_CODE);
358 dbms_sql.column_value (l_cursor, 9,asg_rec.ALLOCATED_AREA_PCT);
359 dbms_sql.column_value (l_cursor, 10,asg_rec.ALLOCATED_AREA);
360 dbms_sql.column_value (l_cursor, 11,asg_rec.UTILIZED_AREA);
361 dbms_sql.column_value (l_cursor, 12,asg_rec.EMP_SPACE_COMMENTS);
362 dbms_sql.column_value (l_cursor, 13,asg_rec.LAST_UPDATE_DATE);
363 dbms_sql.column_value (l_cursor, 14,asg_rec.LAST_UPDATED_BY);
364 dbms_sql.column_value (l_cursor, 15,asg_rec.CREATION_DATE);
365 dbms_sql.column_value (l_cursor, 16,asg_rec.CREATED_BY);
366 dbms_sql.column_value (l_cursor, 17,asg_rec.LAST_UPDATE_LOGIN);
367 dbms_sql.column_value (l_cursor, 18,asg_rec.ATTRIBUTE_CATEGORY);
368 dbms_sql.column_value (l_cursor, 19,asg_rec.ATTRIBUTE1);
369 dbms_sql.column_value (l_cursor, 20,asg_rec.ATTRIBUTE2);
370 dbms_sql.column_value (l_cursor, 21,asg_rec.ATTRIBUTE3);
371 dbms_sql.column_value (l_cursor, 22,asg_rec.ATTRIBUTE4);
372 dbms_sql.column_value (l_cursor, 23,asg_rec.ATTRIBUTE5);
373 dbms_sql.column_value (l_cursor, 24,asg_rec.ATTRIBUTE6);
374 dbms_sql.column_value (l_cursor, 25,asg_rec.ATTRIBUTE7);
375 dbms_sql.column_value (l_cursor, 26,asg_rec.ATTRIBUTE8);
376 dbms_sql.column_value (l_cursor, 27,asg_rec.ATTRIBUTE9);
377 dbms_sql.column_value (l_cursor, 28,asg_rec.ATTRIBUTE10);
378 dbms_sql.column_value (l_cursor, 29,asg_rec.ATTRIBUTE11);
379 dbms_sql.column_value (l_cursor, 30,asg_rec.ATTRIBUTE12);
380 dbms_sql.column_value (l_cursor, 31,asg_rec.ATTRIBUTE13);
381 dbms_sql.column_value (l_cursor, 32,asg_rec.ATTRIBUTE14);
382 dbms_sql.column_value (l_cursor, 33,asg_rec.ATTRIBUTE15);
383 dbms_sql.column_value (l_cursor, 34,asg_rec.ORG_ID);
384 l_log_context := 'opening cursor ';
385
386 PN_SPACE_ASSIGN_EMP_PKG.tlempinfo := asg_rec;
387
388
389 /* Get cost center from HR */
390 IF l_person_id <> asg_rec.person_id THEN
391 pn_cc_sync_pkg.get_cc_as_of_date(asg_rec.person_id, l_column_name, fnd_date.canonical_to_date(p_as_of_date), l_emp_name, l_cost_center);
392
393 /* Get employee name if any issues with finding cost center */
394 IF l_emp_name IS NULL THEN
395 OPEN get_emp_name(asg_rec.person_id, l_as_of_date);
396 FETCH get_emp_name INTO l_emp_name;
397 CLOSE get_emp_name;
398 END IF;
399
400 l_person_id := asg_rec.person_id;
401 pnp_debug_pkg.log('Processing data for person: '|| l_person_id || ' HR Cost Center : ' || l_cost_center);
402
403 END IF;
404
405 /* Comapre cost centers and make changes if necessary */
406
407 IF l_cost_center IS NULL THEN
408
409 fnd_message.set_name ('PN', 'PN_CC_NOT_FOUND_MSG');
410 l_err_msg1 := fnd_message.get;
411 l_failure_msg := rpad(nvl(l_emp_name,' '), 50, ' ') ||
412 rpad(asg_rec.emp_assign_start_date, 15, ' ') ||
413 rpad(nvl(to_char(asg_rec.emp_assign_end_date),' '), 15, ' ') ||
414 rpad(pnp_util_func.get_location_code(asg_rec.location_id, l_as_of_date), 30, ' ') ||
415 rpad(asg_rec.cost_center_code, 30, ' ') ||
416 l_err_msg1 ;
417 failure_table(failure_table.COUNT) := l_failure_msg;
418 l_count_failure := l_count_failure + 1;
419 l_count_total := l_count_total + 1;
420
421 ELSIF asg_rec.cost_center_code <> l_cost_center THEN
422
423 l_log_context := ' inserting/updating data for assignment_id : ' || asg_rec.emp_space_assign_id ;
424 pnp_debug_pkg.log(l_log_context);
425
426
427 BEGIN
428
429 PN_SPACE_ASSIGN_EMP_PKG.tlempinfo := asg_rec;
430
431 /* Check if assignmnet start date is same as as_of_date.
432 We do not need to insert new record but only need to update cost ceneter in this case. */
433
434 IF l_as_of_date = trunc(asg_rec.emp_assign_start_date) THEN
435
436 /* Call Update_Row in CORRECT mode */
437
438 PN_SPACE_ASSIGN_EMP_PKG.Update_Row(
439 asg_rec.emp_space_assign_id,
440 asg_rec.attribute1,
441 asg_rec.attribute2,
442 asg_rec.attribute3,
443 asg_rec.attribute4,
444 asg_rec.attribute5,
445 asg_rec.attribute6,
446 asg_rec.attribute7,
447 asg_rec.attribute8,
448 asg_rec.attribute9,
449 asg_rec.attribute10,
450 asg_rec.attribute11,
451 asg_rec.attribute12,
452 asg_rec.attribute13,
453 asg_rec.attribute14,
454 asg_rec.attribute15,
455 asg_rec.location_id,
456 asg_rec.person_id,
457 asg_rec.project_id,
458 asg_rec.task_id,
459 asg_rec.emp_assign_start_date,
460 asg_rec.emp_assign_end_date,
461 l_cost_center,
462 asg_rec.allocated_area_pct,
463 asg_rec.allocated_area,
464 asg_rec.utilized_area,
465 asg_rec.emp_space_comments,
466 asg_rec.attribute_category,
467 sysdate,
468 l_last_updated_by,
469 l_last_update_login,
470 'CORRECT',
471 l_out_date,
472 l_source_id /* Use process identifier for source column */
473 ) ;
474
475
476 ELSE
477 /* Need to update current record and insert new record
478 Call Update_Row in UPDATE mode */
479
480 PN_SPACE_ASSIGN_EMP_PKG.tlempinfo.source := l_source_id || '_' || asg_rec.emp_space_assign_id ;
481
482 PN_SPACE_ASSIGN_EMP_PKG.Update_Row(
483 asg_rec.emp_space_assign_id,
484 asg_rec.attribute1,
485 asg_rec.attribute2,
486 asg_rec.attribute3,
487 asg_rec.attribute4,
488 asg_rec.attribute5,
489 asg_rec.attribute6,
490 asg_rec.attribute7,
491 asg_rec.attribute8,
492 asg_rec.attribute9,
493 asg_rec.attribute10,
494 asg_rec.attribute11,
495 asg_rec.attribute12,
496 asg_rec.attribute13,
497 asg_rec.attribute14,
498 asg_rec.attribute15,
499 asg_rec.location_id,
500 asg_rec.person_id,
501 asg_rec.project_id,
502 asg_rec.task_id,
503 l_as_of_date, /* Use As of Date as start date */
504 asg_rec.emp_assign_end_date,
505 l_cost_center, /* Use HR cost center */
506 asg_rec.allocated_area_pct,
507 asg_rec.allocated_area,
508 asg_rec.utilized_area,
509 asg_rec.emp_space_comments,
510 asg_rec.attribute_category,
511 sysdate,
512 l_last_updated_by,
513 l_last_update_login,
514 'UPDATE',
515 l_out_date,
516 l_source_id /* Use process identifier for source column */
517 ) ;
518
519 END IF;
520
521 l_log_context := ' constructing l_success_msg ...';
522
523 l_success_msg := rpad(nvl(l_emp_name, ' '), 50, ' ') ||
524 rpad(l_as_of_date, 15, ' ') ||
525 rpad(nvl(to_char(asg_rec.emp_assign_end_date),' '), 15, ' ') ||
526 rpad(pnp_util_func.get_location_code(asg_rec.location_id, l_as_of_date), 30, ' ') ||
527 rpad(asg_rec.cost_center_code, 30, ' ') ||
528 rpad(l_cost_center, 30, ' ') ;
529
530 success_table(success_table.COUNT) := l_success_msg;
531 l_count_success := l_count_success + 1;
532 l_count_total := l_count_total + 1;
533 l_commit_count := l_commit_count + 1;
534
535 IF l_commit_count > l_batch_size then
536 l_log_context := ' doing batch commit...';
537 commit;
538 l_log_context := ' done batch commit...current person_id : ' || asg_rec.person_id;
539 pnp_debug_pkg.log(l_log_context);
540 l_commit_count := 0;
541 l_last_person_processed := asg_rec.person_id;
542 l_log_context := ' Cursor closing and opening again...' ;
543 pnp_debug_pkg.log(l_log_context);
544
545 END IF;
546
547 EXCEPTION
548 WHEN OTHERS THEN
549
550 l_log_context := ' constructing l_failure_msg ...';
551 l_err_msg1 := SQLERRM || ' : ' || SQLCODE ;
552 l_failure_msg := rpad(nvl(l_emp_name, ' '), 50, ' ') ||
553 rpad(asg_rec.emp_assign_start_date, 15, ' ') ||
554 rpad(nvl(to_char(asg_rec.emp_assign_end_date),' '), 15, ' ') ||
555 rpad(pnp_util_func.get_location_code(asg_rec.location_id, l_as_of_date), 30, ' ') ||
556 rpad(asg_rec.cost_center_code, 30, ' ')
557 || l_err_msg1 ;
558 failure_table(failure_table.COUNT) := l_failure_msg;
559 l_count_failure := l_count_failure + 1;
560 l_count_total := l_count_total + 1;
561
562 END;
563
564 END IF;
565
566 END LOOP;
567
568 -- Commit last batch
569
570 l_log_context := ' commiting last batch...';
571 commit;
572 l_log_context := ' commited last batch...';
573 pnp_debug_pkg.log(l_log_context);
574
575 l_log_context := ' printing summary ...';
576
577 pnp_debug_pkg.put_log_msg('===============================================================================');
578
579
580 fnd_message.set_name ('PN','PN_CAFM_LOCATION_TOTAL');
581 fnd_message.set_token ('TOTAL', TO_CHAR(l_count_total));
582 pnp_debug_pkg.put_log_msg(fnd_message.get);
583
584 fnd_message.set_name ('PN','PN_CAFM_LOCATION_SUCCESS');
585 fnd_message.set_token ('SUCCESS', TO_CHAR(l_count_success));
586 pnp_debug_pkg.put_log_msg(fnd_message.get);
587
588 fnd_message.set_name ('PN','PN_CAFM_LOCATION_FAILURE');
589 fnd_message.set_token ('FAILURE', TO_CHAR(l_count_failure));
590 pnp_debug_pkg.put_log_msg(fnd_message.get);
591
592 pnp_debug_pkg.put_log_msg('===============================================================================');
593
594
595 -- Print failed records
596
597 IF failure_table.COUNT > 0 THEN
598 l_log_context := ' printing failure table...';
599 pnp_debug_pkg.put_log_msg(' ');
600 pnp_debug_pkg.put_log_msg(' ');
601
602 fnd_message.set_name ('PN','PN_HRSYNC_FAIL_DTLS');
603 pnp_debug_pkg.put_log_msg(fnd_message.get);
604
605 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_NAME');
606 l_message := fnd_message.get;
607 l_message := l_message||' ';
608
609 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_FROM');
610 l_message := l_message||fnd_message.get||' ';
611
612 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_TO');
613 l_message := l_message||fnd_message.get||' ';
614
615 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_LOC');
616 l_message := l_message||fnd_message.get||' ';
617
618 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_CC');
619 l_message := l_message||fnd_message.get||' ';
620
621 fnd_message.set_name ('PN','PN_ERR');
622 l_message := l_message||fnd_message.get||' ';
623
624 pnp_debug_pkg.put_log_msg(l_message);
625 pnp_debug_pkg.put_log_msg(' ');
626
627
628 pnp_debug_pkg.put_log_msg('==============================================================================================================================================================');
629
630 i := 0;
631 FOR i IN 0 .. (failure_table.COUNT - 1) LOOP
632 pnp_debug_pkg.put_log_msg(failure_table(i));
633 END LOOP;
634
635 pnp_debug_pkg.put_log_msg('==============================================================================================================================================================');
636
637
638 END IF;
639
640 -- Print succeeded records
641 l_message := NULL;
642
643 IF success_table.COUNT > 0 THEN
644 l_log_context := ' printing success table...';
645 pnp_debug_pkg.put_log_msg(' ');
646 pnp_debug_pkg.put_log_msg(' ');
647 fnd_message.set_name ('PN','PN_HRSYNC_SUC_DTLS');
648 pnp_debug_pkg.put_log_msg(fnd_message.get);
649
650 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_NAME');
651 l_message := fnd_message.get;
652 l_message := l_message||' ';
653
654 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_FROM');
655 l_message := l_message||fnd_message.get||' ';
656
657 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_TO');
658 l_message := l_message||fnd_message.get||' ';
659
660 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_LOC');
661 l_message := l_message||fnd_message.get||' ';
662
663 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_OLD_CC');
664 l_message := l_message||fnd_message.get||' ';
665
666 fnd_message.set_name ('PN','PN_HRSYNC_REC_DTLS_NEW_CC');
667 l_message := l_message||fnd_message.get||' ';
668
669 pnp_debug_pkg.put_log_msg(l_message);
670 pnp_debug_pkg.put_log_msg(' ');
671
672
673 pnp_debug_pkg.put_log_msg('==============================================================================================================================================================');
674
675
676
677 i := 0;
678 FOR i IN 0 .. (success_table.COUNT - 1) LOOP
679 pnp_debug_pkg.put_log_msg(success_table(i));
680 END LOOP;
681
682 pnp_debug_pkg.put_log_msg('==============================================================================================================================================================');
683
684
685 END IF;
686
687 pnp_debug_pkg.debug(l_desc||' (-)');
688
689 EXCEPTION
690 WHEN OTHERS THEN
691
692 pnp_debug_pkg.log(l_desc || ': Error while ' || l_log_context);
693 Errbuf := SQLERRM || ' : ' || SQLCODE ;
694 raise;
695 END cc_sync_with_hr;
696
697
698 /*============================================================================+
699 | PROCEDURE
700 | get_cc_as_of_date
701 |
702 | DESCRIPTION
703 | RETURN the cost center of an employee at HR assignment level on
704 | 'As of Date' alongwith full name of an employee
705 |
706 | SCOPE - PUBLIC
707 |
708 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED
709 | pnp_util_func.get_segment_column_name
710 |
711 | ARGUMENTS : IN:
712 | p_employee_id
713 | p_column_name
714 | p_as_of_date
715 | OUT:
716 | p_cost_center
717 | p_emp_name
718 |
719 |
720 | NOTES : Currently being used in concurrent program
721 | 'Cost Center Synchronization with HR'
722 | (called by pn_cc_sync_pkg.cc_sync_with_hr)
723 |
724 | MODIFICATION HISTORY
725 |
726 | 13-NOV-03 Vikas Mehta Created
727 | 26-OCT-05 Hareesha o ATG mandated changes for SQL literals
728 | using dbms_sql.
729 | 22-NOV-05 Hareesha o Replaced _all with secured synonyms/
730 | base views.
731 | 04-APR-06 Hareesha o Bug 5119241 Use SQL literal for l_column_name
732 | instead of bind variable since usage of
733 | bind varaible selscts
734 | the column name,not the column value
735 +=============================================================================*/
736
737
738 PROCEDURE get_cc_as_of_date (
739 p_employee_id IN NUMBER,
740 p_column_name IN VARCHAR2,
741 p_as_of_date IN DATE,
742 p_emp_name OUT NOCOPY VARCHAR2,
743 p_cost_center OUT NOCOPY VARCHAR2
744 ) IS
745
746 l_column_name VARCHAR2 (25) := NULL;
747 sql_statement VARCHAR2(2000);
748 l_code_comb_id PER_ALL_ASSIGNMENTS_F.default_code_comb_id%TYPE;
749 l_cursor INTEGER;
750 l_statement VARCHAR2(10000);
751 l_rows INTEGER;
752 l_count INTEGER;
753 x_code_comb_id PER_ALL_ASSIGNMENTS_F.default_code_comb_id%TYPE;
754
755
756 CURSOR get_default_code_comb_id (p_employee_id IN NUMBER, p_as_of_date IN DATE) IS
757 SELECT
758 A.DEFAULT_CODE_COMB_ID,
759 P.FULL_NAME
760 FROM
761 PER_PEOPLE_F P,
762 PER_ALL_ASSIGNMENTS_F A,
763 PER_PERIODS_OF_SERVICE B
764 WHERE
765 A.PERSON_ID = P.PERSON_ID
766 AND A.person_id = p_employee_id
767 AND A.PRIMARY_FLAG = 'Y'
768 AND A.ASSIGNMENT_TYPE = 'E'
769 AND A.PERIOD_OF_SERVICE_ID = B.PERIOD_OF_SERVICE_ID
770 AND TRUNC(p_as_of_date) BETWEEN P.EFFECTIVE_START_DATE AND P.EFFECTIVE_END_DATE
771 AND TRUNC(p_as_of_date) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE
772 AND (trunc(B.ACTUAL_TERMINATION_DATE)>= trunc(p_as_of_date) or B.ACTUAL_TERMINATION_DATE is null)
773 AND P.EMPLOYEE_NUMBER IS NOT NULL
774 AND A.DEFAULT_CODE_COMB_ID IS NOT NULL ;
775
776 BEGIN
777
778 IF p_column_name IS NULL THEN
779 l_column_name := pnp_util_func.get_segment_column_name(pn_mo_cache_utils.get_current_org_id);
780 ELSE
781 l_column_name := p_column_name;
782 END IF;
783
784 FOR rec IN get_default_code_comb_id(p_employee_id, p_as_of_date) LOOP
785 l_code_comb_id := rec.DEFAULT_CODE_COMB_ID;
786 p_emp_name := rec.FULL_NAME;
787 END LOOP;
788
789 IF l_column_name IS NOT NULL THEN
790
791 l_cursor := dbms_sql.open_cursor;
792
793 l_statement :=
794 ' SELECT '|| l_column_name ||
795 ' FROM gl_code_combinations
796 WHERE code_combination_id = :x_code_comb_id ';
797
798 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
799
800 /* uncomment for debugging purposes */
801 /*pnp_debug_pkg.log(' l_statement_4:'||l_statement);
802 pnp_debug_pkg.log(' l_code_comb_id:'||l_code_comb_id);
803 pnp_debug_pkg.log(' l_column_name:'||l_column_name);*/
804
805 dbms_sql.bind_variable
806 (l_cursor,'x_code_comb_id',l_code_comb_id );
807
808 dbms_sql.define_column (l_cursor, 1,p_cost_center,30);
809
810 l_rows := dbms_sql.execute(l_cursor);
811
812 LOOP
813
814 l_count := dbms_sql.fetch_rows( l_cursor );
815 EXIT WHEN l_count <> 1;
816 dbms_sql.column_value (l_cursor, 1,p_cost_center);
817
818 END LOOP;
819
820 IF dbms_sql.is_open (l_cursor) THEN
821 dbms_sql.close_cursor (l_cursor);
822 END IF;
823 END IF;
824
825
826
827 EXCEPTION
828 WHEN OTHERS THEN
829 p_cost_center := NULL;
830 END get_cc_as_of_date;
831
832 ------------------------------
833 -- End of Package
834 ------------------------------
835 END PN_CC_SYNC_PKG;