[Home] [Help]
PACKAGE BODY: APPS.PN_EXP_TO_CAD_ITF
Source
1 PACKAGE BODY PN_EXP_TO_CAD_ITF AS
2 -- $Header: PNTXPCDB.pls 120.5 2006/08/31 10:41:23 sraaj ship $
3
4 -------------------------------------------------------------------
5 -- for loading Locations Info into the Interface Table ( FOR CAFM )
6 -- ( Run AS a Conc Process )
7 -- ( PN_LOCATIONS --> PN_LOCATIONS_ITF )
8 -- 30-AUG-06 Shabda o As part of bug # 5449595, we now pas canonical dates
9 -- This method expects non canonical too, so we change it back to non canonical.
10 -------------------------------------------------------------------
11 PROCEDURE exp_to_cad_itf (
12 errbuf OUT NOCOPY VARCHAR2 ,
13 retcode OUT NOCOPY VARCHAR2 ,
14 locn_or_spc_flag VARCHAR2 ,
15 p_batch_name VARCHAR2 ,
16 p_locn_type VARCHAR2 ,
17 p_locn_code_from VARCHAR2 ,
18 p_locn_code_to VARCHAR2 ,
19 p_last_update_from VARCHAR2 ,
20 p_last_update_to VARCHAR2 ,
21 p_as_of_date VARCHAR2 DEFAULT NULL
22 )
23
24 IS
25 l_as_of_date DATE := pnp_util_func.get_as_of_date(fnd_date.canonical_to_date(p_as_of_date));
26 BEGIN
27
28 IF (locn_or_spc_flag = 'LOCATION') THEN
29
30 BEGIN
31 exp_loc_to_cad_itf (
32 p_batch_name => p_batch_name ,
33 p_locn_type => p_locn_type ,
34 p_locn_code_from => p_locn_code_from ,
35 p_locn_code_to => p_locn_code_to ,
36 p_last_update_from => p_last_update_from ,
37 p_last_update_to => p_last_update_to ,
38 p_as_of_date => l_as_of_date
39 );
40
41 EXCEPTION
42 WHEN OTHERS THEN
43 fnd_message.set_name ('PN', 'PN_EXP_LOC_TO_CAD_ITF');
44 RAISE;
45
46 END;
47
48 ELSIF (locn_or_spc_flag = 'SPACE') THEN
49
50 BEGIN
51 exp_spc_to_cad_itf (
52 p_batch_name ,
53 p_locn_type ,
54 p_locn_code_from ,
55 p_locn_code_to ,
56 p_last_update_from ,
57 p_last_update_to ,
58 p_as_of_date
59 );
60
61 EXCEPTION
62 WHEN OTHERS THEN
63 fnd_message.set_name ('PN', 'PN_EXP_SPC_TO_CAD_ITF');
64 RAISE;
65
66 END;
67
68 -- This should logically not be reached.
69 ELSE
70 APP_EXCEPTION.RAISE_EXCEPTION;
71
72 END IF;
73
74 END exp_to_cad_itf;
75
76
77 -----------------------------------------------------------------------------
78 -- FOR loading Locations Info into the Interface Table ( FOR CAFM )
79 -- ( Run AS a Conc Process )
80 -- ( PN_LOCATIONS --> PN_LOCATIONS_ITF )
81 -- 15-JUL-05 hareesha o Bug 4284035 - Replaced pn_addresses with _ALL table.
82 -- 29-SEP-05 Hareesha o ATG mandated changes for SQL literals using dbms_sql.
83 -----------------------------------------------------------------------------
84 PROCEDURE exp_loc_to_cad_itf (
85 p_batch_name IN VARCHAR2 ,
86 p_locn_type IN VARCHAR2 ,
87 p_locn_code_from IN VARCHAR2 ,
88 p_locn_code_to IN VARCHAR2 ,
89 p_last_update_from IN VARCHAR2 ,
90 p_last_update_to IN VARCHAR2 ,
91 p_as_of_date IN VARCHAR2
92 )
93
94
95 IS
96
97 l_last_update_from DATE := fnd_date.canonical_to_date(NVL(p_last_update_from,'0001/01/01:00:00:00'));
98 l_last_update_to DATE := fnd_date.canonical_to_date(NVL(p_last_update_to,'4712/12/31:00:00:00'));
99
100 /* this is for a probable future use...
101 l_as_of_date DATE := NVL(p_as_of_date,
102 fnd_date.canonical_to_date('4712/12/31:00:00:00'));
103 */
104 exists_in_itf NUMBER;
105 insert_update VARCHAR2(6);
106 FAIL_ON_UPDATE EXCEPTION;
107 INVALID_PARAMETER EXCEPTION;
108
109 v_where_clause VARCHAR2(5000) := ' ';
110 v_dummy INTEGER;
111
112 v_counter NUMBER := 0;
113 v_fail NUMBER := 0;
114 v_success NUMBER := 0;
115
116 v_location_id PN_LOCATIONS.LOCATION_ID%TYPE;
117 v_location_code PN_LOCATIONS.LOCATION_CODE%TYPE;
118 v_location_type_lookup_code PN_LOCATIONS.LOCATION_TYPE_LOOKUP_CODE%TYPE;
119 v_space_type_lookup_code PN_LOCATIONS.SPACE_TYPE_LOOKUP_CODE%TYPE;
120 v_last_update_date PN_LOCATIONS.LAST_UPDATE_DATE%TYPE;
121 v_creation_date PN_LOCATIONS.CREATION_DATE%TYPE;
122 v_parent_location_id PN_LOCATIONS.PARENT_LOCATION_ID%TYPE;
123 v_lease_or_owned PN_LOCATIONS.LEASE_OR_OWNED%TYPE;
124 v_building PN_LOCATIONS.BUILDING%TYPE;
125 v_floor PN_LOCATIONS.FLOOR%TYPE;
126 v_office PN_LOCATIONS.OFFICE%TYPE;
127 v_address_line1 PN_ADDRESSES.ADDRESS_LINE1%TYPE;
128 v_address_line2 PN_ADDRESSES.ADDRESS_LINE2%TYPE;
129 v_address_line3 PN_ADDRESSES.ADDRESS_LINE3%TYPE;
130 v_address_line4 PN_ADDRESSES.ADDRESS_LINE4%TYPE;
131 v_county PN_ADDRESSES.COUNTY%TYPE;
132 v_city PN_ADDRESSES.CITY%TYPE;
133 v_state PN_ADDRESSES.STATE%TYPE;
134 v_province PN_ADDRESSES.PROVINCE%TYPE;
135 v_zip_code PN_ADDRESSES.ZIP_CODE%TYPE;
136 v_country PN_ADDRESSES.COUNTRY%TYPE;
137 v_address_style PN_ADDRESSES.ADDRESS_STYLE%TYPE;
138 v_max_capacity PN_LOCATIONS.MAX_CAPACITY%TYPE;
139 v_rentable_area PN_LOCATIONS.RENTABLE_AREA%TYPE;
140 v_optimum_capacity PN_LOCATIONS.OPTIMUM_CAPACITY%TYPE;
141 v_usable_area PN_LOCATIONS.USABLE_AREA%TYPE;
142 v_allocate_cost_center_code PN_LOCATIONS.ALLOCATE_COST_CENTER_CODE%TYPE;
143 v_uom_code PN_LOCATIONS.UOM_CODE%TYPE;
144 v_last_update_login PN_LOCATIONS.LAST_UPDATE_LOGIN%TYPE;
145 v_last_updated_by PN_LOCATIONS.LAST_UPDATED_BY%TYPE;
146 v_created_by PN_LOCATIONS.CREATED_BY%TYPE;
147 v_attribute_category VARCHAR2(30);
148 v_attribute1 VARCHAR2 ( 150 );
149 v_attribute2 VARCHAR2 ( 150 );
150 v_attribute3 VARCHAR2 ( 150 );
151 v_attribute4 VARCHAR2 ( 150 );
152 v_attribute5 VARCHAR2 ( 150 );
153 v_attribute6 VARCHAR2 ( 150 );
154 v_attribute7 VARCHAR2 ( 150 );
155 v_attribute8 VARCHAR2 ( 150 );
156 v_attribute9 VARCHAR2 ( 150 );
157 v_attribute10 VARCHAR2 ( 150 );
158 v_attribute11 VARCHAR2 ( 150 );
159 v_attribute12 VARCHAR2 ( 150 );
160 v_attribute13 VARCHAR2 ( 150 );
161 v_attribute14 VARCHAR2 ( 150 );
162 v_attribute15 VARCHAR2 ( 150 );
163 v_addr_attribute_category VARCHAR2 ( 30 );
164 v_addr_attribute1 VARCHAR2 ( 150 );
165 v_addr_attribute2 VARCHAR2 ( 150 );
166 v_addr_attribute3 VARCHAR2 ( 150 );
167 v_addr_attribute4 VARCHAR2 ( 150 );
168 v_addr_attribute5 VARCHAR2 ( 150 );
169 v_addr_attribute6 VARCHAR2 ( 150 );
170 v_addr_attribute7 VARCHAR2 ( 150 );
171 v_addr_attribute8 VARCHAR2 ( 150 );
172 v_addr_attribute9 VARCHAR2 ( 150 );
173 v_addr_attribute10 VARCHAR2 ( 150 );
174 v_addr_attribute11 VARCHAR2 ( 150 );
175 v_addr_attribute12 VARCHAR2 ( 150 );
176 v_addr_attribute13 VARCHAR2 ( 150 );
177 v_addr_attribute14 VARCHAR2 ( 150 );
178 v_addr_attribute15 VARCHAR2 ( 150 );
179 v_source PN_LOCATIONS.SOURCE%TYPE;
180 v_gross_area PN_LOCATIONS.GROSS_AREA%TYPE;
181 v_assignable_area PN_LOCATIONS.ASSIGNABLE_AREA%TYPE;
182 v_class PN_LOCATIONS.CLASS%TYPE;
183 v_status_type PN_LOCATIONS.STATUS_TYPE%TYPE;
184 v_suite PN_LOCATIONS.SUITE%TYPE;
185 v_common_area PN_LOCATIONS.COMMON_AREA%TYPE;
186 v_common_area_flag PN_LOCATIONS.COMMON_AREA_FLAG%TYPE;
187 v_function_type_lookup_code PN_LOCATIONS.FUNCTION_TYPE_LOOKUP_CODE%TYPE; --BUG#2198182
188 v_active_start_date PN_LOCATIONS.ACTIVE_START_DATE%TYPE;
189 v_active_end_date PN_LOCATIONS.ACTIVE_END_DATE%TYPE;
190 l_cursor INTEGER;
191 l_statement VARCHAR2(10000);
192 l_locn_type VARCHAR2(30);
193 l_locn_code_from VARCHAR2(90);
194 l_locn_code_to VARCHAR2(90);
195 l_rows INTEGER;
196 l_count INTEGER;
197 v_standard_type_lookup_code PN_LOCATIONS.STANDARD_TYPE_LOOKUP_CODE%TYPE; --BUG#5359173
198
199 BEGIN
200
201 fnd_message.set_name ('PN','PN_HRSYNC_LOC_TYPE');
202 fnd_message.set_token ('TYPE',p_locn_type);
203 pnp_debug_pkg.put_log_msg(fnd_message.get);
204
205 l_cursor := dbms_sql.open_cursor;
206 l_statement :=
207 'SELECT
208 LOCATION_ID,
209 LOCATION_CODE,
210 LOCATION_TYPE_LOOKUP_CODE,
211 SPACE_TYPE_LOOKUP_CODE,
212 PARENT_LOCATION_ID,
213 LEASE_OR_OWNED,
214 BUILDING,
215 FLOOR,
216 OFFICE,
217 ADDRESS_LINE1,
218 ADDRESS_LINE2,
219 ADDRESS_LINE3,
220 ADDRESS_LINE4,
221 COUNTY,
222 CITY,
223 STATE,
224 PROVINCE,
225 ZIP_CODE,
226 COUNTRY,
227 ADDRESS_STYLE,
228 MAX_CAPACITY,
229 OPTIMUM_CAPACITY,
230 RENTABLE_AREA,
231 USABLE_AREA,
232 ALLOCATE_COST_CENTER_CODE,
233 UOM_CODE,
234 L.ATTRIBUTE_CATEGORY,
235 L.ATTRIBUTE1,
236 L.ATTRIBUTE2,
237 L.ATTRIBUTE3,
238 L.ATTRIBUTE4,
239 L.ATTRIBUTE5,
240 L.ATTRIBUTE6,
241 L.ATTRIBUTE7,
242 L.ATTRIBUTE8,
243 L.ATTRIBUTE9,
244 L.ATTRIBUTE10,
245 L.ATTRIBUTE11,
246 L.ATTRIBUTE12,
247 L.ATTRIBUTE13,
248 L.ATTRIBUTE14,
249 L.ATTRIBUTE15,
250 A.ATTRIBUTE_CATEGORY,
251 A.ATTRIBUTE1,
252 A.ATTRIBUTE2,
253 A.ATTRIBUTE3,
254 A.ATTRIBUTE4,
255 A.ATTRIBUTE5,
256 A.ATTRIBUTE6,
257 A.ATTRIBUTE7,
258 A.ATTRIBUTE8,
259 A.ATTRIBUTE9,
260 A.ATTRIBUTE10,
261 A.ATTRIBUTE11,
262 A.ATTRIBUTE12,
263 A.ATTRIBUTE13,
264 A.ATTRIBUTE14,
265 A.ATTRIBUTE15,
266 L.SOURCE,
267 L.GROSS_AREA,
268 L.ASSIGNABLE_AREA,
269 L.CLASS,
270 L.STATUS_TYPE,
271 L.SUITE,
272 L.COMMON_AREA,
273 L.COMMON_AREA_FLAG,
274 L.FUNCTION_TYPE_LOOKUP_CODE, ---BUG#2198182
275 L.ACTIVE_START_DATE,
276 L.ACTIVE_END_DATE,
277 L.STANDARD_TYPE_LOOKUP_CODE ---BUG#5359173
278 FROM
279 PN_LOCATIONS L,
280 PN_ADDRESSES_ALL A
281 WHERE
282 L.ADDRESS_ID = A.ADDRESS_ID (+)
283 AND L.LAST_UPDATE_DATE >= TRUNC(:date_from )
287
284 AND L.LAST_UPDATE_DATE <= TRUNC(:date_to) ';
285
286 -- Append the AND clause
288 IF (p_locn_type <> 'ALL') THEN
289
290 l_locn_type := p_locn_type;
291 l_statement :=
292 l_statement || ' AND location_type_lookup_code = :l_locn_type';
293
294 END IF;
295
296 -- Append the other AND clauses, as needed.
297
298 IF p_locn_code_from IS NOT NULL THEN
299
300 l_locn_code_from := p_locn_code_from;
301 l_statement :=
302 l_statement || ' AND location_code >= :l_locn_code_from';
303
304 END IF;
305
306 IF p_locn_code_to IS NOT NULL THEN
307
308 l_locn_code_to := p_locn_code_to;
309 l_statement :=
310 l_statement || ' AND location_code <= :l_locn_code_to';
311
312 END IF;
313
314 l_statement := l_statement || ' order by L.LOCATION_CODE' ;
315
316 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
317
318 dbms_sql.bind_variable
319 (l_cursor,'date_from',l_last_update_from );
320
321 dbms_sql.bind_variable
322 (l_cursor,'date_to',l_last_update_to );
323
324 IF (p_locn_type <> 'ALL') THEN
325 dbms_sql.bind_variable
326 (l_cursor, 'l_locn_type', l_locn_type );
327 END IF;
328
329 IF p_locn_code_from IS NOT NULL THEN
330 dbms_sql.bind_variable
331 (l_cursor, 'l_locn_code_from', l_locn_code_from );
332 END IF;
333
334 IF p_locn_code_to IS NOT NULL THEN
335 dbms_sql.bind_variable
336 (l_cursor, 'l_locn_code_to', l_locn_code_to );
337 END IF;
338
339 dbms_sql.define_column (l_cursor, 1,v_location_id);
340 dbms_sql.define_column (l_cursor, 2,v_location_code,90);
341 dbms_sql.define_column (l_cursor, 3,v_location_type_lookup_code,30);
342 dbms_sql.define_column (l_cursor, 4,v_space_type_lookup_code,30);
343 dbms_sql.define_column (l_cursor, 5,v_parent_location_id);
344 dbms_sql.define_column (l_cursor, 6,v_lease_or_owned,30);
345 dbms_sql.define_column (l_cursor, 7,v_building,30);
346 dbms_sql.define_column (l_cursor, 8,v_floor,20);
347 dbms_sql.define_column (l_cursor, 9,v_office,20);
348 dbms_sql.define_column (l_cursor, 10,v_address_line1,240);
349 dbms_sql.define_column (l_cursor, 11,v_address_line2,240);
350 dbms_sql.define_column (l_cursor, 12,v_address_line3,240);
351 dbms_sql.define_column (l_cursor, 13,v_address_line4,240);
352 dbms_sql.define_column (l_cursor, 14,v_county,60);
353 dbms_sql.define_column (l_cursor, 15,v_city,60);
354 dbms_sql.define_column (l_cursor, 16,v_state,60);
355 dbms_sql.define_column (l_cursor, 17,v_province,60);
356 dbms_sql.define_column (l_cursor, 18,v_zip_code,60);
357 dbms_sql.define_column (l_cursor, 19,v_country,60);
358 dbms_sql.define_column (l_cursor, 20,v_address_style,30);
359 dbms_sql.define_column (l_cursor, 21,v_max_capacity);
360 dbms_sql.define_column (l_cursor, 22,v_optimum_capacity);
361 dbms_sql.define_column (l_cursor, 23,v_rentable_area);
362 dbms_sql.define_column (l_cursor, 24,v_usable_area);
363 dbms_sql.define_column (l_cursor, 25,v_allocate_cost_center_code,30);
364 dbms_sql.define_column (l_cursor, 26,v_uom_code,3);
365 dbms_sql.define_column (l_cursor, 27,v_attribute_category,30);
366 dbms_sql.define_column (l_cursor, 28,v_attribute1,150);
367 dbms_sql.define_column (l_cursor, 29,v_attribute2,150);
368 dbms_sql.define_column (l_cursor, 30,v_attribute3,150);
369 dbms_sql.define_column (l_cursor, 31,v_attribute4,150);
370 dbms_sql.define_column (l_cursor, 32,v_attribute5,150);
371 dbms_sql.define_column (l_cursor, 33,v_attribute6,150);
372 dbms_sql.define_column (l_cursor, 34,v_attribute7,150);
373 dbms_sql.define_column (l_cursor, 35,v_attribute8,150);
374 dbms_sql.define_column (l_cursor, 36,v_attribute9,150);
375 dbms_sql.define_column (l_cursor, 37,v_attribute10,150);
376 dbms_sql.define_column (l_cursor, 38,v_attribute11,150);
377 dbms_sql.define_column (l_cursor, 39,v_attribute12,150);
378 dbms_sql.define_column (l_cursor, 40,v_attribute13,150);
379 dbms_sql.define_column (l_cursor, 41,v_attribute14,150);
380 dbms_sql.define_column (l_cursor, 42,v_attribute15,150);
381 dbms_sql.define_column (l_cursor, 43,v_addr_attribute_category,30);
382 dbms_sql.define_column (l_cursor, 44,v_addr_attribute1,150);
383 dbms_sql.define_column (l_cursor, 45,v_addr_attribute2,150);
384 dbms_sql.define_column (l_cursor, 46,v_addr_attribute3,150);
385 dbms_sql.define_column (l_cursor, 47,v_addr_attribute4,150);
386 dbms_sql.define_column (l_cursor, 48,v_addr_attribute5,150);
387 dbms_sql.define_column (l_cursor, 49,v_addr_attribute6,150);
388 dbms_sql.define_column (l_cursor, 50,v_addr_attribute7,150);
389 dbms_sql.define_column (l_cursor, 51,v_addr_attribute8,150);
390 dbms_sql.define_column (l_cursor, 52,v_addr_attribute9,150);
391 dbms_sql.define_column (l_cursor, 53,v_addr_attribute10,150);
392 dbms_sql.define_column (l_cursor, 54,v_addr_attribute11,150);
393 dbms_sql.define_column (l_cursor, 55,v_addr_attribute12,150);
394 dbms_sql.define_column (l_cursor, 56,v_addr_attribute13,150);
395 dbms_sql.define_column (l_cursor, 57,v_addr_attribute14,150);
396 dbms_sql.define_column (l_cursor, 58,v_addr_attribute15,150);
400 dbms_sql.define_column (l_cursor, 62,v_class,30);
397 dbms_sql.define_column (l_cursor, 59,v_source,80);
398 dbms_sql.define_column (l_cursor, 60,v_gross_area);
399 dbms_sql.define_column (l_cursor, 61,v_assignable_area);
401 dbms_sql.define_column (l_cursor, 63,v_status_type,30);
402 dbms_sql.define_column (l_cursor, 64,v_suite,30);
403 dbms_sql.define_column (l_cursor, 65,v_common_area);
404 dbms_sql.define_column (l_cursor, 66,v_common_area_flag,1);
405 dbms_sql.define_column (l_cursor, 67,v_function_type_lookup_code,30);
406 dbms_sql.define_column (l_cursor, 68,v_active_start_date);
407 dbms_sql.define_column (l_cursor, 69,v_active_end_date);
408 dbms_sql.define_column (l_cursor, 70,v_standard_type_lookup_code,30); ---BUG#5359173
409
410 l_rows := dbms_sql.execute(l_cursor);
411
412 LOOP
413
414 l_count := dbms_sql.fetch_rows( l_cursor );
415
416 EXIT WHEN l_count <> 1;
417
418 dbms_sql.column_value (l_cursor, 1,v_location_id);
419 dbms_sql.column_value (l_cursor, 2,v_location_code);
420 dbms_sql.column_value (l_cursor, 3,v_location_type_lookup_code);
421 dbms_sql.column_value (l_cursor, 4,v_space_type_lookup_code);
422 dbms_sql.column_value (l_cursor, 5,v_parent_location_id);
423 dbms_sql.column_value (l_cursor, 6,v_lease_or_owned);
424 dbms_sql.column_value (l_cursor, 7,v_building);
425 dbms_sql.column_value (l_cursor, 8,v_floor);
426 dbms_sql.column_value (l_cursor, 9,v_office);
427 dbms_sql.column_value (l_cursor, 10,v_address_line1);
428 dbms_sql.column_value (l_cursor, 11,v_address_line2);
429 dbms_sql.column_value (l_cursor, 12,v_address_line3);
430 dbms_sql.column_value (l_cursor, 13,v_address_line4);
431 dbms_sql.column_value (l_cursor, 14,v_county);
432 dbms_sql.column_value (l_cursor, 15,v_city);
433 dbms_sql.column_value (l_cursor, 16,v_state);
434 dbms_sql.column_value (l_cursor, 17,v_province);
435 dbms_sql.column_value (l_cursor, 18,v_zip_code);
436 dbms_sql.column_value (l_cursor, 19,v_country);
437 dbms_sql.column_value (l_cursor, 20,v_address_style);
438 dbms_sql.column_value (l_cursor, 21,v_max_capacity);
439 dbms_sql.column_value (l_cursor, 22,v_optimum_capacity);
440 dbms_sql.column_value (l_cursor, 23,v_rentable_area);
441 dbms_sql.column_value (l_cursor, 24,v_usable_area);
442 dbms_sql.column_value (l_cursor, 25,v_allocate_cost_center_code);
443 dbms_sql.column_value (l_cursor, 26,v_uom_code);
444 dbms_sql.column_value (l_cursor, 27,v_attribute_category);
445 dbms_sql.column_value (l_cursor, 28,v_attribute1);
446 dbms_sql.column_value (l_cursor, 29,v_attribute2);
447 dbms_sql.column_value (l_cursor, 30,v_attribute3);
448 dbms_sql.column_value (l_cursor, 31,v_attribute4);
449 dbms_sql.column_value (l_cursor, 32,v_attribute5);
450 dbms_sql.column_value (l_cursor, 33,v_attribute6);
451 dbms_sql.column_value (l_cursor, 34,v_attribute7);
452 dbms_sql.column_value (l_cursor, 35,v_attribute8);
453 dbms_sql.column_value (l_cursor, 36,v_attribute9);
454 dbms_sql.column_value (l_cursor, 37,v_attribute10);
455 dbms_sql.column_value (l_cursor, 38,v_attribute11);
456 dbms_sql.column_value (l_cursor, 39,v_attribute12);
457 dbms_sql.column_value (l_cursor, 40,v_attribute13);
458 dbms_sql.column_value (l_cursor, 41,v_attribute14);
459 dbms_sql.column_value (l_cursor, 42,v_attribute15);
460 dbms_sql.column_value (l_cursor, 43,v_addr_attribute_category);
461 dbms_sql.column_value (l_cursor, 44,v_addr_attribute1);
462 dbms_sql.column_value (l_cursor, 45,v_addr_attribute2);
463 dbms_sql.column_value (l_cursor, 46,v_addr_attribute3);
464 dbms_sql.column_value (l_cursor, 47,v_addr_attribute4);
465 dbms_sql.column_value (l_cursor, 48,v_addr_attribute5);
466 dbms_sql.column_value (l_cursor, 49,v_addr_attribute6);
467 dbms_sql.column_value (l_cursor, 50,v_addr_attribute7);
468 dbms_sql.column_value (l_cursor, 51,v_addr_attribute8);
469 dbms_sql.column_value (l_cursor, 52,v_addr_attribute9);
470 dbms_sql.column_value (l_cursor, 53,v_addr_attribute10);
471 dbms_sql.column_value (l_cursor, 54,v_addr_attribute11);
472 dbms_sql.column_value (l_cursor, 55,v_addr_attribute12);
473 dbms_sql.column_value (l_cursor, 56,v_addr_attribute13);
474 dbms_sql.column_value (l_cursor, 57,v_addr_attribute14);
475 dbms_sql.column_value (l_cursor, 58,v_addr_attribute15);
476 dbms_sql.column_value (l_cursor, 59,v_source);
477 dbms_sql.column_value (l_cursor, 60,v_gross_area);
478 dbms_sql.column_value (l_cursor, 61,v_assignable_area);
479 dbms_sql.column_value (l_cursor, 62,v_class);
480 dbms_sql.column_value (l_cursor, 63,v_status_type);
481 dbms_sql.column_value (l_cursor, 64,v_suite);
482 dbms_sql.column_value (l_cursor, 65,v_common_area);
483 dbms_sql.column_value (l_cursor, 66,v_common_area_flag);
484 dbms_sql.column_value (l_cursor, 67,v_function_type_lookup_code);
485 dbms_sql.column_value (l_cursor, 68,v_active_start_date);
486 dbms_sql.column_value (l_cursor, 69,v_active_end_date);
487 dbms_sql.column_value (l_cursor, 70,v_standard_type_lookup_code); ---BUG#5359173
488 -- Check if data exists in ITF table already.
489
493 SELECT 1
490 exists_in_itf := 0;
491
492 BEGIN
494 INTO exists_in_itf
495 FROM PN_LOCATIONS_ITF
496 WHERE location_id = v_location_id
497 AND active_start_date = v_active_start_date
498 AND active_END_date = v_active_end_date;
499 EXCEPTION
500 WHEN NO_DATA_FOUND THEN
501 NULL;
502 WHEN OTHERS THEN
503 EXIT;
504 END;
505
506 /*
507 IF (v_Source IS NOT NULL) THEN
508 exists_in_itf := 0;
509 END IF;
510 */
511
512 -- Insert if data does not exist in ITF table, else Update
513
514 IF (exists_in_itf = 0) THEN
515
516
517 -- Insert the fetched data into PN_LOCATIONS_ITF table.
518
519 INSERT INTO PN_LOCATIONS_ITF (
520 BATCH_NAME,
521 ENTRY_TYPE,
522 LOCATION_ID,
523 LOCATION_CODE,
524 LOCATION_TYPE_LOOKUP_CODE,
525 SPACE_TYPE_LOOKUP_CODE,
526 LAST_UPDATE_DATE,
527 CREATION_DATE,
528 PARENT_LOCATION_ID,
529 LEASE_OR_OWNED,
530 BUILDING,
531 FLOOR,
532 OFFICE,
533 ADDRESS_LINE1,
534 ADDRESS_LINE2,
535 ADDRESS_LINE3,
536 ADDRESS_LINE4,
537 COUNTY,
538 CITY,
539 STATE,
540 PROVINCE,
541 ZIP_CODE,
542 COUNTRY,
543 ADDRESS_STYLE,
544 MAX_CAPACITY,
545 OPTIMUM_CAPACITY,
546 RENTABLE_AREA,
547 USABLE_AREA,
548 ALLOCATE_COST_CENTER_CODE,
549 UOM_CODE,
550 LAST_UPDATE_LOGIN,
551 LAST_UPDATED_BY,
552 CREATED_BY,
553 ATTRIBUTE_CATEGORY,
554 ATTRIBUTE1,
555 ATTRIBUTE2,
556 ATTRIBUTE3,
557 ATTRIBUTE4,
558 ATTRIBUTE5,
559 ATTRIBUTE6,
560 ATTRIBUTE7,
561 ATTRIBUTE8,
562 ATTRIBUTE9,
563 ATTRIBUTE10,
564 ATTRIBUTE11,
565 ATTRIBUTE12,
566 ATTRIBUTE13,
567 ATTRIBUTE14,
568 ATTRIBUTE15,
569 ADDR_ATTRIBUTE_CATEGORY,
570 ADDR_ATTRIBUTE1,
571 ADDR_ATTRIBUTE2,
572 ADDR_ATTRIBUTE3,
573 ADDR_ATTRIBUTE4,
574 ADDR_ATTRIBUTE5,
575 ADDR_ATTRIBUTE6,
576 ADDR_ATTRIBUTE7,
577 ADDR_ATTRIBUTE8,
578 ADDR_ATTRIBUTE9,
579 ADDR_ATTRIBUTE10,
580 ADDR_ATTRIBUTE11,
581 ADDR_ATTRIBUTE12,
582 ADDR_ATTRIBUTE13,
583 ADDR_ATTRIBUTE14,
584 ADDR_ATTRIBUTE15,
585 SOURCE,
586 GROSS_AREA,
587 ASSIGNABLE_AREA,
588 CLASS,
589 STATUS_TYPE,
590 SUITE,
591 COMMON_AREA,
592 COMMON_AREA_FLAG,
593 FUNCTION_TYPE_LOOKUP_CODE,
594 STANDARD_TYPE_LOOKUP_CODE, ---BUG#5359173
595 ACTIVE_START_DATE,
596 ACTIVE_END_DATE
597 )
598 VALUES (
599 p_BATCH_NAME,
600 decode(v_SOURCE, NULL, 'A', 'U'),
601 v_LOCATION_ID,
602 v_LOCATION_CODE,
603 v_LOCATION_TYPE_LOOKUP_CODE,
604 v_SPACE_TYPE_LOOKUP_CODE,
605 SYSDATE,
606 SYSDATE,
607 v_PARENT_LOCATION_ID,
608 v_LEASE_OR_OWNED,
609 v_BUILDING,
610 v_FLOOR,
611 v_OFFICE,
612 v_ADDRESS_LINE1,
613 v_ADDRESS_LINE2,
614 v_ADDRESS_LINE3,
615 v_ADDRESS_LINE4,
616 v_COUNTY,
617 v_CITY,
618 v_STATE,
619 v_PROVINCE,
620 v_ZIP_CODE,
621 v_COUNTRY,
622 v_ADDRESS_STYLE,
623 to_NUMBER(v_MAX_CAPACITY),
624 to_NUMBER(v_OPTIMUM_CAPACITY),
625 to_NUMBER(v_RENTABLE_AREA),
626 to_NUMBER(v_USABLE_AREA),
627 v_ALLOCATE_COST_CENTER_CODE,
628 v_UOM_CODE,
629 fnd_profile.value('CONC_LOGIN_ID'),
630 fnd_profile.value('USER_ID'),
631 fnd_profile.value('USER_ID'),
632 v_ATTRIBUTE_CATEGORY,
633 v_ATTRIBUTE1,
634 v_ATTRIBUTE2,
635 v_ATTRIBUTE3,
636 v_ATTRIBUTE4,
637 v_ATTRIBUTE5,
638 v_ATTRIBUTE6,
639 v_ATTRIBUTE7,
640 v_ATTRIBUTE8,
641 v_ATTRIBUTE9,
642 v_ATTRIBUTE10,
643 v_ATTRIBUTE11,
644 v_ATTRIBUTE12,
645 v_ATTRIBUTE13,
646 v_ATTRIBUTE14,
647 v_ATTRIBUTE15,
648 v_ADDR_ATTRIBUTE_CATEGORY,
649 v_ADDR_ATTRIBUTE1,
650 v_ADDR_ATTRIBUTE2,
651 v_ADDR_ATTRIBUTE3,
652 v_ADDR_ATTRIBUTE4,
653 v_ADDR_ATTRIBUTE5,
654 v_ADDR_ATTRIBUTE6,
655 v_ADDR_ATTRIBUTE7,
656 v_ADDR_ATTRIBUTE8,
657 v_ADDR_ATTRIBUTE9,
658 v_ADDR_ATTRIBUTE10,
662 v_ADDR_ATTRIBUTE14,
659 v_ADDR_ATTRIBUTE11,
660 v_ADDR_ATTRIBUTE12,
661 v_ADDR_ATTRIBUTE13,
663 v_ADDR_ATTRIBUTE15,
664 NVL(v_source,'PN'),
665 v_GROSS_AREA,
666 v_ASSIGNABLE_AREA,
667 v_CLASS,
668 v_STATUS_TYPE,
669 v_SUITE,
670 v_COMMON_AREA,
671 v_COMMON_AREA_FLAG,
672 v_function_type_lookup_code,
673 v_standard_type_lookup_code, ---BUG#5359173
674 v_active_start_date,
675 v_active_end_date
676 );
677
678 Insert_Update := 'Insert';
679
680 END IF;
681
682
683 -- Update data IN ITF table
684
685 IF (exists_in_itf = 1) THEN
686
687 UPDATE PN_LOCATIONS_ITF SET
688 BATCH_NAME = p_batch_name,
689 ENTRY_TYPE = 'U',
690 LOCATION_ID = v_location_id,
691 LOCATION_CODE = v_LOCATION_CODE,
692 LOCATION_TYPE_LOOKUP_CODE = v_location_type_lookup_code,
693 SPACE_TYPE_LOOKUP_CODE = v_location_type_lookup_code,
694 LAST_UPDATE_DATE = SYSDATE,
695 CREATION_DATE = SYSDATE,
696 PARENT_LOCATION_ID = v_parent_location_id,
697 LEASE_OR_OWNED = v_lease_or_owned,
698 BUILDING = v_building,
699 FLOOR = v_floor,
700 OFFICE = v_office,
701 ADDRESS_LINE1 = v_address_line1,
702 ADDRESS_LINE2 = v_address_line2,
703 ADDRESS_LINE3 = v_address_line3,
704 ADDRESS_LINE4 = v_address_line4,
705 COUNTY = v_county,
706 CITY = v_city,
707 STATE = v_state,
708 PROVINCE = v_province,
709 ZIP_CODE = v_zip_code,
710 COUNTRY = v_country,
711 ADDRESS_STYLE = v_address_style,
712 MAX_CAPACITY = v_max_capacity,
713 OPTIMUM_CAPACITY = v_optimum_capacity,
714 RENTABLE_AREA = v_rentable_area,
715 USABLE_AREA = v_usable_area,
716 ALLOCATE_COST_CENTER_CODE = v_allocate_cost_center_code,
717 UOM_CODE = v_uom_code,
718 LAST_UPDATE_LOGIN = fnd_profile.value('CONC_LOGIN_ID'),
719 LAST_UPDATED_BY = fnd_profile.value('USER_ID'),
720 CREATED_BY = fnd_profile.value('USER_ID'),
721 ATTRIBUTE_CATEGORY = v_attribute_category,
722 ATTRIBUTE1 = v_attribute1,
723 ATTRIBUTE2 = v_attribute2,
724 ATTRIBUTE3 = v_attribute3,
725 ATTRIBUTE4 = v_attribute4,
726 ATTRIBUTE5 = v_attribute5,
727 ATTRIBUTE6 = v_attribute6,
728 ATTRIBUTE7 = v_attribute7,
729 ATTRIBUTE8 = v_attribute8,
730 ATTRIBUTE9 = v_attribute9,
731 ATTRIBUTE10 = v_attribute10,
732 ATTRIBUTE11 = v_attribute11,
733 ATTRIBUTE12 = v_attribute12,
734 ATTRIBUTE13 = v_attribute13,
735 ATTRIBUTE14 = v_attribute14,
736 ATTRIBUTE15 = v_attribute15,
737 ADDR_ATTRIBUTE_CATEGORY = v_addr_attribute_category,
738 ADDR_ATTRIBUTE1 = v_addr_attribute1,
739 ADDR_ATTRIBUTE2 = v_addr_attribute2,
740 ADDR_ATTRIBUTE3 = v_addr_attribute3,
741 ADDR_ATTRIBUTE4 = v_addr_attribute4,
742 ADDR_ATTRIBUTE5 = v_addr_attribute5,
743 ADDR_ATTRIBUTE6 = v_addr_attribute6,
744 ADDR_ATTRIBUTE7 = v_addr_attribute7,
745 ADDR_ATTRIBUTE8 = v_addr_attribute8,
746 ADDR_ATTRIBUTE9 = v_addr_attribute9,
747 ADDR_ATTRIBUTE10 = v_addr_attribute10,
748 ADDR_ATTRIBUTE11 = v_addr_attribute11,
749 ADDR_ATTRIBUTE12 = v_addr_attribute12,
750 ADDR_ATTRIBUTE13 = v_addr_attribute13,
751 ADDR_ATTRIBUTE14 = v_addr_attribute14,
752 ADDR_ATTRIBUTE15 = v_addr_attribute15,
753 -- SOURCE = NVL(v_Source, 'PN'),
754 GROSS_AREA = v_gross_area,
755 ASSIGNABLE_AREA = v_assignable_area,
756 CLASS = v_class,
757 STATUS_TYPE = v_status_type,
758 SUITE = v_suite,
759 COMMON_AREA = v_common_area,
760 COMMON_AREA_FLAG = v_common_area_flag,
761 FUNCTION_TYPE_LOOKUP_CODE = v_function_type_lookup_code, --BUG#2198182
762 STANDARD_TYPE_LOOKUP_CODE = v_standard_type_lookup_code ---BUG#5359173
763 WHERE LOCATION_ID = v_location_id
764 AND active_start_date = v_active_start_date
765 AND active_end_date = v_active_end_date ;
766
767 IF (SQL%NOTFOUND) THEN
768 RAISE FAIL_ON_UPDATE;
769 END IF;
770
771 Insert_Update := 'Update';
772
773 END IF;
774
775
776 v_Counter := v_Counter + 1;
777
778
779 -------------------------------------------
780 -- For Conc Log/output Files
781 -------------------------------------------
782
783 IF v_counter = 1 THEN
784
785 fnd_message.set_name ('PN','PN_BATCH_NAME');
786 fnd_message.set_token ('NAME',p_batch_name);
787 pnp_debug_pkg.put_log_msg(fnd_message.get);
788
789 fnd_message.set_name ('PN','PN_HRSYNC_LOC_TYPE');
790 fnd_message.set_token ('TYPE',p_locn_type);
791 pnp_debug_pkg.put_log_msg(fnd_message.get);
792
793
794 END IF;
795
796
797 PNP_DEBUG_PKG.log (
798 'Record NUMBER: ' || v_Counter ||
802 );
799 ', ' || Insert_Update ||
800 ', Location Code: ' || v_Location_Code ||
801 ', Location Id: ' || v_Location_Id
803
804 fnd_message.set_name ('PN','PN_XPEAM_LOC');
805 fnd_message.set_token ('LOC_CODE',v_Location_Code);
806 pnp_debug_pkg.put_log_msg(fnd_message.get);
807
808 END LOOP;
809
810 IF dbms_sql.is_open (l_cursor) THEN
811 dbms_sql.close_cursor (l_cursor);
812 END IF;
813 -- Commit our work.
814
815 COMMIT;
816
817 -- log the summary
818 v_success := v_counter - v_fail;
819 PNP_DEBUG_PKG.put_log_msg('
820 ===============================================================================');
821 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_TOTAL');
822 fnd_message.set_token('NUM', v_Counter);
823 PNP_DEBUG_PKG.put_log_msg(fnd_message.get);
824
825 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_SUCCESS');
826 fnd_message.set_token('NUM', v_success);
827 PNP_DEBUG_PKG.put_log_msg(fnd_message.get);
828
829 fnd_message.set_name('PN', 'PN_CAFM_LOCATION_FAILURE');
830 fnd_message.set_token('NUM', v_fail);
831 PNP_DEBUG_PKG.put_log_msg(fnd_message.get);
832 PNP_DEBUG_PKG.put_log_msg('
833 ===============================================================================');
834 EXCEPTION
835 WHEN OTHERS THEN
836 RAISE;
837
838
839
840 END exp_loc_to_cad_itf;
841
842
843 -----------------------------------------------------------------------------
844 -- For loading Space Allocations Info into the Interface Table ( FOR CAFM )
845 -- ( Run AS a Conc Process )
846 -- ( PN_SPACE_ALLOCATIONS --> PN_EMP_SPACE_ASSIGN_ITF )
847 -- 15-JUL-05 hareesha o Bug 4284035 - Replaced PN_LOCATIONS with _ALL table.
848 -- 04-OCT-05 Hareesha o ATG mandated changes for SQL literals using dbms_sql
849 -- 28-AUG-06 Shabda o Bug 5449595 - Added changes to accomodate
850 -- project_id and task_id
851 -- 29-AUG-06 SHABDA o Bug 5449595 - Removed long log messages. Removed unused
852 -- commented code
853 -----------------------------------------------------------------------------
854
855 PROCEDURE exp_spc_to_cad_itf (
856 p_batch_name IN VARCHAR2 ,
857 p_locn_type IN VARCHAR2 ,
858 p_locn_code_from IN VARCHAR2 ,
859 p_locn_code_to IN VARCHAR2 ,
860 p_last_update_from IN VARCHAR2 ,
861 p_last_update_to IN VARCHAR2 ,
862 p_as_of_date IN VARCHAR2
863 )
864
865 IS
866
867 l_last_update_from DATE := NVL(fnd_date.canonical_to_date(p_last_update_from),
868 fnd_date.canonical_to_date('0001/01/01:00:00:00'));
869 l_last_update_to DATE := NVL(fnd_date.canonical_to_date(p_last_update_to),
870 fnd_date.canonical_to_date('4712/12/31:00:00:00'));
871 l_as_of_date DATE := fnd_date.canonical_to_date(p_as_of_date);
872
873 exists_in_itf NUMBER;
874 Insert_Update VARCHAR2(6);
875 FAIL_ON_UPDATE EXCEPTION;
876
877 v_cursorid INTEGER;
878 v_where_clause VARCHAR2(5000);
879 v_Dummy INTEGER;
880
881 v_Counter NUMBER := 0;
882
883 v_person_id NUMBER;
884 v_LOCATION_ID NUMBER;
885 v_EMP_SPACE_ASSIGN_ID NUMBER;
886 v_COST_CENTER_CODE VARCHAR2(30);
887 v_ALLOCATED_AREA NUMBER;
888 v_LAST_UPDATE_DATE DATE;
889 v_LAST_UPDATE_LOGIN NUMBER;
890 v_CREATED_BY NUMBER;
891 v_CREATION_DATE DATE;
892 v_LAST_UPDATED_BY NUMBER;
893 v_ATTRIBUTE_CATEGORY VARCHAR2(30);
894 v_ATTRIBUTE1 VARCHAR2(150);
895 v_ATTRIBUTE2 VARCHAR2(150);
896 v_ATTRIBUTE3 VARCHAR2(150);
897 v_ATTRIBUTE4 VARCHAR2(150);
898 v_ATTRIBUTE5 VARCHAR2(150);
899 v_ATTRIBUTE6 VARCHAR2(150);
900 v_ATTRIBUTE7 VARCHAR2(150);
901 v_ATTRIBUTE8 VARCHAR2(150);
902 v_ATTRIBUTE9 VARCHAR2(150);
903 v_ATTRIBUTE10 VARCHAR2(150);
904 v_ATTRIBUTE11 VARCHAR2(150);
905 v_ATTRIBUTE12 VARCHAR2(150);
906 v_ATTRIBUTE13 VARCHAR2(150);
907 v_ATTRIBUTE14 VARCHAR2(150);
908 v_ATTRIBUTE15 VARCHAR2(150);
909 v_LOCATION_TYPE_LOOKUP_CODE VARCHAR2(30);
910 v_LOCATION_CODE VARCHAR2(90);
911 v_SOURCE VARCHAR2(80);
912 v_EMP_ASSIGN_START_DATE DATE;
913 v_EMP_ASSIGN_END_DATE DATE;
914 v_UTILIZED_AREA NUMBER;
915 vl_date DATE := NULL;
919 l_locn_type VARCHAR2(30);
916 l_cursor INTEGER;
917 l_statement VARCHAR2(10000);
918 l_rows INTEGER;
920 l_locn_code_from VARCHAR2(90);
921 l_locn_code_to VARCHAR2(90);
922 l_count INTEGER;
923 v_PROJECT_ID NUMBER;
924 v_TASK_ID NUMBER;
925
926 BEGIN
927 pnp_debug_pkg.put_log_msg('inside exp_spc_to_cad_itf');
928 vl_date := NVL(vl_date, fnd_date.canonical_to_date('4712/12/31:00:00:00'));
929
930 fnd_message.set_name ('PN','PN_HRSYNC_LOC_TYPE');
931 fnd_message.set_token ('TYPE',p_locn_type);
932 pnp_debug_pkg.put_log_msg(fnd_message.get);
933
934
935 l_cursor := dbms_sql.open_cursor;
936
937 l_statement :=
938 'SELECT
939 SP.EMP_SPACE_ASSIGN_ID,
940 SP.LOCATION_ID,
941 SP.PERSON_ID,
942 SP.COST_CENTER_CODE,
943 SP.ALLOCATED_AREA,
944 SP.ATTRIBUTE_CATEGORY,
945 SP.ATTRIBUTE1,
946 SP.ATTRIBUTE2,
947 SP.ATTRIBUTE3,
948 SP.ATTRIBUTE4,
949 SP.ATTRIBUTE5,
950 SP.ATTRIBUTE6,
951 SP.ATTRIBUTE7,
952 SP.ATTRIBUTE8,
953 SP.ATTRIBUTE9,
954 SP.ATTRIBUTE10,
955 SP.ATTRIBUTE11,
956 SP.ATTRIBUTE12,
957 SP.ATTRIBUTE13,
958 SP.ATTRIBUTE14,
959 SP.ATTRIBUTE15,
960 LO.LOCATION_TYPE_LOOKUP_CODE,
961 LO.LOCATION_CODE,
962 SP.SOURCE,
963 SP.EMP_ASSIGN_START_DATE,
964 SP.EMP_ASSIGN_END_DATE,
965 SP.UTILIZED_AREA,
966 SP.PROJECT_ID,
967 SP.TASK_ID
968 FROM
969 PN_LOCATIONS LO,
970 PN_SPACE_ASSIGN_EMP_ALL SP
971 WHERE
972 LO.location_id = SP.location_id
973 AND TRUNC(:as_of_date) between
974 SP.EMP_ASSIGN_START_DATE
975 AND NVL(SP.EMP_ASSIGN_END_DATE,TRUNC(:v_date))
976 AND SP.LAST_UPDATE_DATE >= TRUNC(:date_from )
977 AND SP.LAST_UPDATE_DATE <= TRUNC(:date_to)';
978
979
980 -- append the and clauses, as needed.
981
982 IF p_locn_type <> 'ALL' THEN
983
984 l_locn_type := p_locn_type;
985 l_statement :=
986 l_statement || ' AND LO.location_type_lookup_code = :l_locn_type ';
987
988 END IF;
989
990 IF p_locn_code_from IS NOT NULL THEN
991
992 l_locn_code_from := p_locn_code_from ;
993 l_statement :=
994 l_statement ||' AND LO.location_code >= :l_locn_code_from ';
995
996 END IF;
997
998 IF p_locn_code_to IS NOT NULL THEN
999
1000 l_locn_code_to := p_locn_code_to ;
1001 l_statement :=
1002 l_statement ||' AND LO.location_code <= :l_locn_code_to ';
1003
1004 END IF;
1005
1006
1007 dbms_sql.parse(l_cursor, l_statement, dbms_sql.native);
1008
1009 pnp_debug_pkg.log(' after parse ');
1010
1011 dbms_sql.bind_variable
1012 (l_cursor,'as_of_date',l_as_of_date );
1013
1014 dbms_sql.bind_variable
1015 (l_cursor,'v_date',vl_date );
1016
1017 dbms_sql.bind_variable
1018 (l_cursor,'date_from',l_last_update_from );
1019
1020 dbms_sql.bind_variable
1021 (l_cursor,'date_to',l_last_update_to );
1022
1023 IF(p_locn_type <> 'ALL') THEN
1024 dbms_sql.bind_variable
1025 (l_cursor, 'l_locn_type', l_locn_type );
1026 END IF;
1027
1028 IF p_locn_code_FROM IS NOT NULL THEN
1029 dbms_sql.bind_variable
1030 (l_cursor, 'l_locn_code_from', l_locn_code_from );
1031 END IF;
1032
1033 IF p_locn_code_to IS NOT NULL THEN
1034 dbms_sql.bind_variable
1035 (l_cursor, 'l_locn_code_to', l_locn_code_to );
1036 END IF;
1037
1038 dbms_sql.define_column (l_cursor, 1,v_EMP_SPACE_ASSIGN_ID);
1039 dbms_sql.define_column (l_cursor, 2,v_LOCATION_ID);
1040 dbms_sql.define_column (l_cursor, 3,v_person_id);
1041 dbms_sql.define_column (l_cursor, 4,v_COST_CENTER_CODE,30);
1042 dbms_sql.define_column (l_cursor, 5,v_ALLOCATED_AREA);
1043 dbms_sql.define_column (l_cursor, 6,v_ATTRIBUTE_CATEGORY,30);
1044 dbms_sql.define_column (l_cursor, 7,v_ATTRIBUTE1,150);
1045 dbms_sql.define_column (l_cursor, 8,v_ATTRIBUTE2,150);
1046 dbms_sql.define_column (l_cursor, 9,v_ATTRIBUTE3,150);
1047 dbms_sql.define_column (l_cursor, 10,v_ATTRIBUTE4,150);
1048 dbms_sql.define_column (l_cursor, 11,v_ATTRIBUTE5,150);
1049 dbms_sql.define_column (l_cursor, 12,v_ATTRIBUTE6,150);
1053 dbms_sql.define_column (l_cursor, 16,v_ATTRIBUTE10,150);
1050 dbms_sql.define_column (l_cursor, 13,v_ATTRIBUTE7,150);
1051 dbms_sql.define_column (l_cursor, 14,v_ATTRIBUTE8,150);
1052 dbms_sql.define_column (l_cursor, 15,v_ATTRIBUTE9,150);
1054 dbms_sql.define_column (l_cursor, 17,v_ATTRIBUTE11,150);
1055 dbms_sql.define_column (l_cursor, 18,v_ATTRIBUTE12,150);
1056 dbms_sql.define_column (l_cursor, 19,v_ATTRIBUTE13,150);
1057 dbms_sql.define_column (l_cursor, 20,v_ATTRIBUTE14,150);
1058 dbms_sql.define_column (l_cursor, 21,v_ATTRIBUTE15,150);
1059 dbms_sql.define_column (l_cursor, 22,v_LOCATION_TYPE_LOOKUP_CODE,30);
1060 dbms_sql.define_column (l_cursor, 23,v_LOCATION_CODE,90);
1061 dbms_sql.define_column (l_cursor, 24,v_SOURCE,80);
1062 dbms_sql.define_column (l_cursor, 25,v_EMP_ASSIGN_START_DATE);
1063 dbms_sql.define_column (l_cursor, 26,v_EMP_ASSIGN_END_DATE);
1064 dbms_sql.define_column (l_cursor, 27,v_UTILIZED_AREA);
1065 dbms_sql.define_column (l_cursor, 28,v_PROJECT_ID);
1066 dbms_sql.define_column (l_cursor, 29,v_TASK_ID);
1067
1068 l_rows := dbms_sql.execute(l_cursor);
1069
1070 LOOP
1071
1072 l_count := dbms_sql.fetch_rows( l_cursor );
1073
1074 pnp_debug_pkg.log(' after fetch_rows');
1075 EXIT WHEN l_count <>1;
1076
1077 dbms_sql.column_value (l_cursor, 1,v_EMP_SPACE_ASSIGN_ID);
1078 dbms_sql.column_value (l_cursor, 2,v_LOCATION_ID);
1079 dbms_sql.column_value (l_cursor, 3,v_person_id);
1080 dbms_sql.column_value (l_cursor, 4,v_COST_CENTER_CODE);
1081 dbms_sql.column_value (l_cursor, 5,v_ALLOCATED_AREA);
1082 dbms_sql.column_value (l_cursor, 6,v_ATTRIBUTE_CATEGORY);
1083 dbms_sql.column_value (l_cursor, 7,v_ATTRIBUTE1);
1084 dbms_sql.column_value (l_cursor, 8,v_ATTRIBUTE2);
1085 dbms_sql.column_value (l_cursor, 9,v_ATTRIBUTE3);
1086 dbms_sql.column_value (l_cursor, 10,v_ATTRIBUTE4);
1087 dbms_sql.column_value (l_cursor, 11,v_ATTRIBUTE5);
1088 dbms_sql.column_value (l_cursor, 12,v_ATTRIBUTE6);
1089 dbms_sql.column_value (l_cursor, 13,v_ATTRIBUTE7);
1090 dbms_sql.column_value (l_cursor, 14,v_ATTRIBUTE8);
1091 dbms_sql.column_value (l_cursor, 15,v_ATTRIBUTE9);
1092 dbms_sql.column_value (l_cursor, 16,v_ATTRIBUTE10);
1093 dbms_sql.column_value (l_cursor, 17,v_ATTRIBUTE11);
1094 dbms_sql.column_value (l_cursor, 18,v_ATTRIBUTE12);
1095 dbms_sql.column_value (l_cursor, 19,v_ATTRIBUTE13);
1096 dbms_sql.column_value (l_cursor, 20,v_ATTRIBUTE14);
1097 dbms_sql.column_value (l_cursor, 21,v_ATTRIBUTE15);
1098 dbms_sql.column_value (l_cursor, 22,v_LOCATION_TYPE_LOOKUP_CODE);
1099 dbms_sql.column_value (l_cursor, 23,v_LOCATION_CODE);
1100 dbms_sql.column_value (l_cursor, 24,v_SOURCE);
1101 dbms_sql.column_value (l_cursor, 25,v_EMP_ASSIGN_START_DATE);
1102 dbms_sql.column_value (l_cursor, 26,v_EMP_ASSIGN_END_DATE);
1103 dbms_sql.column_value (l_cursor, 27,v_UTILIZED_AREA);
1104 dbms_sql.column_value (l_cursor, 28,v_PROJECT_ID);
1105 dbms_sql.column_value (l_cursor, 29,v_TASK_ID);
1106
1107 -- Check if data exists in ITF table already.
1108
1109 exists_in_itf := 0;
1110
1111 BEGIN
1112 SELECT 1
1113 INTO exists_in_itf
1114 FROM PN_EMP_SPACE_ASSIGN_ITF
1115 WHERE emp_space_assign_id = V_EMP_SPACE_ASSIGN_ID;
1116 EXCEPTION
1117 WHEN NO_DATA_FOUND THEN
1118 NULL;
1119 WHEN OTHERS THEN
1120 EXIT;
1121 END;
1122
1123
1124 -- Insert if data does not exist in ITF table, else Update
1125
1126 IF (exists_in_itf = 0) THEN
1127
1128 -- Insert the fetched data INTO PN_EMP_SPACE_ASSIGN_ITF table.
1129
1130 pnp_debug_pkg.log(' bfore insert ');
1131
1132 INSERT INTO PN_EMP_SPACE_ASSIGN_ITF (
1133 BATCH_NAME,
1137 employee_id,
1134 ENTRY_TYPE,
1135 EMP_SPACE_ASSIGN_ID,
1136 LOCATION_ID,
1138 COST_CENTER_CODE,
1139 ALLOCATED_AREA,
1140 LAST_UPDATE_DATE,
1141 LAST_UPDATE_LOGIN,
1142 CREATED_BY,
1143 CREATION_DATE,
1144 LAST_UPDATED_BY,
1145 ATTRIBUTE_CATEGORY,
1146 ATTRIBUTE1,
1147 ATTRIBUTE2,
1148 ATTRIBUTE3,
1149 ATTRIBUTE4,
1150 ATTRIBUTE5,
1151 ATTRIBUTE6,
1152 ATTRIBUTE7,
1153 ATTRIBUTE8,
1154 ATTRIBUTE9,
1155 ATTRIBUTE10,
1156 ATTRIBUTE11,
1157 ATTRIBUTE12,
1158 ATTRIBUTE13,
1159 ATTRIBUTE14,
1160 ATTRIBUTE15,
1161 SOURCE,
1162 EMP_ASSIGN_START_DATE,
1163 EMP_ASSIGN_END_DATE,
1164 UTILIZED_AREA,
1165 PROJECT_ID,
1166 TASK_ID
1167 )
1168 VALUES (
1169 p_BATCH_NAME,
1170 decode(v_SOURCE, NULL, 'A', 'U'),
1171 v_EMP_SPACE_ASSIGN_ID,
1172 v_LOCATION_ID,
1173 v_person_id,
1174 v_COST_CENTER_CODE,
1175 v_ALLOCATED_AREA,
1176 SYSDATE,
1177 fnd_profile.value('CONC_LOGIN_ID'),
1178 fnd_profile.value('USER_ID'),
1179 SYSDATE,
1180 fnd_profile.value('USER_ID'),
1181 v_ATTRIBUTE_CATEGORY,
1182 v_ATTRIBUTE1,
1183 v_ATTRIBUTE2,
1184 v_ATTRIBUTE3,
1185 v_ATTRIBUTE4,
1186 v_ATTRIBUTE5,
1187 v_ATTRIBUTE6,
1188 v_ATTRIBUTE7,
1189 v_ATTRIBUTE8,
1190 v_ATTRIBUTE9,
1191 v_ATTRIBUTE10,
1192 v_ATTRIBUTE11,
1193 v_ATTRIBUTE12,
1194 v_ATTRIBUTE13,
1195 v_ATTRIBUTE14,
1196 v_ATTRIBUTE15,
1197 NVL(v_source,'PN'),
1198 v_EMP_ASSIGN_START_DATE,
1199 v_EMP_ASSIGN_END_DATE,
1200 v_UTILIZED_AREA,
1201 v_PROJECT_ID,
1202 v_TASK_ID
1203 );
1204
1205 Insert_Update := 'Insert';
1206
1207 PNP_DEBUG_PKG.log('Inserted Row ' || v_Counter);
1208
1209 END IF;
1210
1211
1212 -- Update data in ITF table
1213
1214 IF (exists_in_itf = 1) THEN
1215
1216 UPDATE PN_EMP_SPACE_ASSIGN_ITF SET
1217 BATCH_NAME = p_batch_name,
1218 ENTRY_TYPE = 'U',
1219 EMP_SPACE_ASSIGN_ID = v_emp_space_assign_id,
1220 LOCATION_ID = v_location_id,
1221 employee_id = v_person_id,
1222 COST_CENTER_CODE = v_cost_center_code,
1223 ALLOCATED_AREA = v_allocated_area,
1224 LAST_UPDATE_DATE = SYSDATE,
1225 LAST_UPDATE_LOGIN = fnd_profile.value('CONC_LOGIN_ID'),
1226 CREATED_BY = fnd_profile.value('USER_ID'),
1227 CREATION_DATE = SYSDATE,
1228 LAST_UPDATED_BY = fnd_profile.value('USER_ID'),
1229 ATTRIBUTE_CATEGORY = v_attribute_category,
1230 ATTRIBUTE1 = v_attribute1,
1231 ATTRIBUTE2 = v_attribute2,
1232 ATTRIBUTE3 = v_attribute3,
1233 ATTRIBUTE4 = v_attribute4,
1234 ATTRIBUTE5 = v_attribute5,
1235 ATTRIBUTE6 = v_attribute6,
1236 ATTRIBUTE7 = v_attribute7,
1237 ATTRIBUTE8 = v_attribute8,
1238 ATTRIBUTE9 = v_attribute9,
1239 ATTRIBUTE10 = v_attribute10,
1240 ATTRIBUTE11 = v_attribute11,
1241 ATTRIBUTE12 = v_attribute12,
1242 ATTRIBUTE13 = v_attribute13,
1243 ATTRIBUTE14 = v_attribute14,
1244 ATTRIBUTE15 = v_attribute15,
1245 EMP_ASSIGN_START_DATE = v_EMP_ASSIGN_START_DATE,
1246 EMP_ASSIGN_END_DATE = v_EMP_ASSIGN_END_DATE,
1247 UTILIZED_AREA = v_UTILIZED_AREA,
1248 PROJECT_ID = v_PROJECT_ID,
1249 TASK_ID=v_TASK_ID
1250 WHERE EMP_SPACE_ASSIGN_ID = v_emp_space_assign_id;
1251
1252 IF (SQL%NOTFOUND) THEN
1253 RAISE FAIL_ON_UPDATE;
1254 END IF;
1255
1256 Insert_Update := 'Update';
1257
1258 PNP_DEBUG_PKG.log('Updated Row ' || v_Counter);
1259
1260 END IF;
1261
1262
1263
1264 -------------------------------------------
1265 -- For Conc Log/OutPut Files
1266 -------------------------------------------
1267
1268 IF v_Counter = 1 THEN
1269
1270 fnd_message.set_name ('PN','PN_BATCH_NAME');
1271 fnd_message.set_token ('NAME',p_batch_name);
1272 pnp_debug_pkg.put_log_msg(fnd_message.get);
1273
1274 fnd_message.set_name ('PN','PN_HRSYNC_LOC_TYPE');
1275 fnd_message.set_token ('TYPE',p_locn_type);
1276 pnp_debug_pkg.put_log_msg(fnd_message.get);
1277
1278 END IF;
1279
1280
1281 PNP_DEBUG_PKG.log (
1282 'Record Number: ' || v_Counter ||
1283 ', ' || Insert_Update ||
1284 ', Emp Space Assign Id: '|| v_emp_space_assign_id ||
1285 ', Location Id: ' || v_Location_Id ||
1286 ', Location Code: ' || v_Location_Code
1287 );
1288
1289 fnd_message.set_name ('PN','PN_XPEAM_LOC');
1290 fnd_message.set_token ('LOC_CODE',v_Location_Code);
1291 pnp_debug_pkg.put_log_msg(fnd_message.get);
1292
1293 END LOOP;
1294
1295 IF dbms_sql.is_open (l_cursor) THEN
1296 dbms_sql.close_cursor (l_cursor);
1297 END IF;
1298 -- Commit our work.
1299 commit;
1300
1301 EXCEPTION
1302
1303 WHEN OTHERS THEN
1304
1305 RAISE;
1306
1307
1308 END exp_spc_to_cad_itf;
1312 -- End of Package
1309
1310
1311 -------------------------------
1313 -------------------------------
1314 END PN_EXP_TO_CAD_ITF;