[Home] [Help]
PACKAGE BODY: APPS.XLA_ENTITY_ID_MAPPINGS_F_PKG
Source
1 PACKAGE BODY xla_entity_id_mappings_f_pkg AS
2 /* $Header: xlatheim.pkb 120.2 2005/04/28 18:45:48 masada ship $ */
3 /*======================================================================+
4 | Copyright (c) 2001-2002 Oracle Corporation |
5 | Redwood Shores, CA, USA |
6 | All rights reserved. |
7 +=======================================================================+
8 | PACKAGE NAME |
9 | xla_entity_id_mappings_f_pkg |
10 | |
11 | DESCRIPTION |
12 | Forms PL/SQL Wrapper for xla_entity_id_mappings |
13 | |
14 | HISTORY |
15 | Generated from XLAUTB. |
16 | |
17 +======================================================================*/
18
19 --=============================================================================
20 -- *********** Local Trace Routine **********
21 --=============================================================================
22 C_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
23 C_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
24 C_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
25 C_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
26 C_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
27 C_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
28
29 C_LEVEL_LOG_DISABLED CONSTANT NUMBER := 99;
30 C_DEFAULT_MODULE CONSTANT VARCHAR2(240) := 'xla.plsql.xla_entity_id_mappings_f_pkg';
31
32 g_log_level NUMBER;
33 g_log_enabled BOOLEAN;
34
35 PROCEDURE trace
36 (p_msg IN VARCHAR2
37 ,p_module IN VARCHAR2
38 ,p_level IN NUMBER) IS
39 BEGIN
40 ----------------------------------------------------------------------------
41 -- Following is for FND log.
42 ----------------------------------------------------------------------------
43 IF (p_msg IS NULL AND p_level >= g_log_level) THEN
44 fnd_log.message(p_level, p_module);
45 ELSIF p_level >= g_log_level THEN
46 fnd_log.string(p_level, p_module, p_msg);
47 END IF;
48
49 EXCEPTION
50 WHEN xla_exceptions_pkg.application_exception THEN
51 RAISE;
52
53 WHEN OTHERS THEN
54 xla_exceptions_pkg.raise_message
55 (p_location => 'xla_entity_id_mappings_f_pkg.trace');
56 END trace;
57
58
59 /*======================================================================+
60 | |
61 | Procedure insert_row |
62 | |
63 +======================================================================*/
64 PROCEDURE insert_row
65 (x_rowid IN OUT NOCOPY VARCHAR2
66 ,x_application_id IN NUMBER
67 ,x_entity_code IN VARCHAR2
68 ,x_transaction_id_col_name_1 IN VARCHAR2
69 ,x_transaction_id_col_name_2 IN VARCHAR2
70 ,x_transaction_id_col_name_3 IN VARCHAR2
71 ,x_transaction_id_col_name_4 IN VARCHAR2
72 ,x_source_id_col_name_1 IN VARCHAR2
73 ,x_source_id_col_name_2 IN VARCHAR2
74 ,x_source_id_col_name_3 IN VARCHAR2
75 ,x_source_id_col_name_4 IN VARCHAR2
76 ,x_creation_date IN DATE
77 ,x_created_by IN NUMBER
78 ,x_last_update_date IN DATE
79 ,x_last_updated_by IN NUMBER
80 ,x_last_update_login IN NUMBER)
81
82 IS
83
84 CURSOR c IS
85 SELECT rowid
86 FROM xla_entity_id_mappings
87 WHERE application_id = x_application_id
88 AND entity_code = x_entity_code
89 ;
90
91 l_log_module VARCHAR2(240);
92 BEGIN
93
94 IF g_log_enabled THEN
95 l_log_module := C_DEFAULT_MODULE||'.insert_row';
96 END IF;
97
98 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
99 trace(p_msg => 'BEGIN of procedure insert_row',
100 p_module => l_log_module,
101 p_level => C_LEVEL_PROCEDURE);
102 END IF;
103
104
105 INSERT INTO xla_entity_id_mappings
106 (creation_date
107 ,created_by
108 ,application_id
109 ,entity_code
110 ,transaction_id_col_name_1
111 ,transaction_id_col_name_2
112 ,transaction_id_col_name_3
113 ,transaction_id_col_name_4
114 ,source_id_col_name_1
115 ,source_id_col_name_2
116 ,source_id_col_name_3
117 ,source_id_col_name_4
118 ,last_update_date
119 ,last_updated_by
120 ,last_update_login)
121 VALUES
122 (x_creation_date
123 ,x_created_by
124 ,x_application_id
125 ,x_entity_code
126 ,x_transaction_id_col_name_1
127 ,x_transaction_id_col_name_2
128 ,x_transaction_id_col_name_3
129 ,x_transaction_id_col_name_4
130 ,x_source_id_col_name_1
131 ,x_source_id_col_name_2
132 ,x_source_id_col_name_3
133 ,x_source_id_col_name_4
134 ,x_last_update_date
135 ,x_last_updated_by
136 ,x_last_update_login)
137 ;
138
139 OPEN c;
140 FETCH c INTO x_rowid;
141
142 IF (c%NOTFOUND) THEN
143 CLOSE c;
144 RAISE NO_DATA_FOUND;
145 END IF;
146 CLOSE c;
147
148 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
149 trace(p_msg => 'END of procedure insert_row',
150 p_module => l_log_module,
151 p_level => C_LEVEL_PROCEDURE);
152 END IF;
153
154 END insert_row;
155
156 /*======================================================================+
157 | |
158 | Procedure lock_row |
159 | |
160 +======================================================================*/
161 PROCEDURE lock_row
162 (x_application_id IN NUMBER
163 ,x_entity_code IN VARCHAR2
164 ,x_transaction_id_col_name_1 IN VARCHAR2
165 ,x_transaction_id_col_name_2 IN VARCHAR2
166 ,x_transaction_id_col_name_3 IN VARCHAR2
167 ,x_transaction_id_col_name_4 IN VARCHAR2
168 ,x_source_id_col_name_1 IN VARCHAR2
169 ,x_source_id_col_name_2 IN VARCHAR2
170 ,x_source_id_col_name_3 IN VARCHAR2
171 ,x_source_id_col_name_4 IN VARCHAR2)
172
173 IS
174
175 CURSOR c IS
176 SELECT application_id
177 ,entity_code
178 ,transaction_id_col_name_1
179 ,transaction_id_col_name_2
180 ,transaction_id_col_name_3
181 ,transaction_id_col_name_4
182 ,source_id_col_name_1
183 ,source_id_col_name_2
184 ,source_id_col_name_3
185 ,source_id_col_name_4
186 FROM xla_entity_id_mappings
187 WHERE application_id = x_application_id
188 AND entity_code = x_entity_code
189 FOR UPDATE OF application_id NOWAIT;
190
191 recinfo c%ROWTYPE;
192
193 l_log_module VARCHAR2(240);
194 BEGIN
195
196 IF g_log_enabled THEN
197 l_log_module := C_DEFAULT_MODULE||'.lock_row';
198 END IF;
199
200 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
201 trace(p_msg => 'BEGIN of procedure lock_row',
202 p_module => l_log_module,
203 p_level => C_LEVEL_PROCEDURE);
204 END IF;
205
206 OPEN c;
207 FETCH c INTO recinfo;
208
209 IF (c%NOTFOUND) THEN
210 CLOSE c;
211 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
212 app_exception.raise_exception;
213 END IF;
214 CLOSE c;
215
216 IF ( ((recinfo.transaction_id_col_name_1 = x_transaction_id_col_name_1) OR
217 (recinfo.transaction_id_col_name_1 IS NULL AND x_transaction_id_col_name_1 IS NULL))
218 AND ((recinfo.transaction_id_col_name_2 = x_transaction_id_col_name_2) OR
219 (recinfo.transaction_id_col_name_2 IS NULL AND x_transaction_id_col_name_2 IS NULL))
220 AND ((recinfo.transaction_id_col_name_3 = x_transaction_id_col_name_3) OR
221 (recinfo.transaction_id_col_name_3 IS NULL AND x_transaction_id_col_name_3 IS NULL))
222 AND ((recinfo.transaction_id_col_name_4 = x_transaction_id_col_name_4) OR
223 (recinfo.transaction_id_col_name_4 IS NULL AND x_transaction_id_col_name_4 IS NULL))
224 AND ((recinfo.source_id_col_name_1 = x_source_id_col_name_1) OR
225 (recinfo.source_id_col_name_1 IS NULL AND x_source_id_col_name_1 IS NULL))
226 AND ((recinfo.source_id_col_name_2 = x_source_id_col_name_2) OR
227 (recinfo.source_id_col_name_2 IS NULL AND x_source_id_col_name_2 IS NULL))
228 AND ((recinfo.source_id_col_name_3 = x_source_id_col_name_3) OR
229 (recinfo.source_id_col_name_3 IS NULL AND x_source_id_col_name_3 IS NULL))
230 AND ((recinfo.source_id_col_name_4 = x_source_id_col_name_4) OR
231 (recinfo.source_id_col_name_4 IS NULL AND x_source_id_col_name_4 IS NULL))
232 ) THEN
233 NULL;
234 ELSE
235 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
236 app_exception.raise_exception;
237 END IF;
238
239 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
240 trace(p_msg => 'END of procedure lock_row',
241 p_module => l_log_module,
242 p_level => C_LEVEL_PROCEDURE);
243 END IF;
244
245 RETURN;
246
247 END lock_row;
248
249 /*======================================================================+
250 | |
251 | Procedure update_row |
252 | |
253 +======================================================================*/
254 PROCEDURE update_row
255 (x_application_id IN NUMBER
256 ,x_entity_code IN VARCHAR2
257 ,x_transaction_id_col_name_1 IN VARCHAR2
258 ,x_transaction_id_col_name_2 IN VARCHAR2
259 ,x_transaction_id_col_name_3 IN VARCHAR2
260 ,x_transaction_id_col_name_4 IN VARCHAR2
261 ,x_source_id_col_name_1 IN VARCHAR2
262 ,x_source_id_col_name_2 IN VARCHAR2
263 ,x_source_id_col_name_3 IN VARCHAR2
264 ,x_source_id_col_name_4 IN VARCHAR2
265 ,x_last_update_date IN DATE
266 ,x_last_updated_by IN NUMBER
267 ,x_last_update_login IN NUMBER)
268
269 IS
270 l_log_module VARCHAR2(240);
271 BEGIN
272
273 IF g_log_enabled THEN
274 l_log_module := C_DEFAULT_MODULE||'.update_row';
275 END IF;
276
277 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
278 trace(p_msg => 'BEGIN of procedure update_row',
279 p_module => l_log_module,
280 p_level => C_LEVEL_PROCEDURE);
281 END IF;
282
283 UPDATE xla_entity_id_mappings
284 SET
285 last_update_date = x_last_update_date
289 ,transaction_id_col_name_4 = x_transaction_id_col_name_4
286 ,transaction_id_col_name_1 = x_transaction_id_col_name_1
287 ,transaction_id_col_name_2 = x_transaction_id_col_name_2
288 ,transaction_id_col_name_3 = x_transaction_id_col_name_3
290 ,source_id_col_name_1 = x_source_id_col_name_1
291 ,source_id_col_name_2 = x_source_id_col_name_2
292 ,source_id_col_name_3 = x_source_id_col_name_3
293 ,source_id_col_name_4 = x_source_id_col_name_4
294 ,last_updated_by = x_last_updated_by
295 ,last_update_login = x_last_update_login
296 WHERE application_id = X_application_id
297 AND entity_code = X_entity_code;
298
299 IF (SQL%NOTFOUND) THEN
300 RAISE NO_DATA_FOUND;
301 END IF;
302
303 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
304 trace(p_msg => 'END of procedure update_row',
305 p_module => l_log_module,
306 p_level => C_LEVEL_PROCEDURE);
307 END IF;
308
309 END update_row;
310
311 /*======================================================================+
312 | |
313 | Procedure delete_row |
314 | |
315 +======================================================================*/
316 PROCEDURE delete_row
317 (x_application_id IN NUMBER
318 ,x_entity_code IN VARCHAR2)
319 IS
320 l_log_module VARCHAR2(240);
321 BEGIN
322
323 IF g_log_enabled THEN
324 l_log_module := C_DEFAULT_MODULE||'.delete_row';
325 END IF;
326
327 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
328 trace(p_msg => 'BEGIN of procedure delete_row',
329 p_module => l_log_module,
330 p_level => C_LEVEL_PROCEDURE);
331 END IF;
332
333 DELETE FROM xla_entity_id_mappings
334 WHERE application_id = x_application_id
335 AND entity_code = x_entity_code;
336
337
338 IF (SQL%NOTFOUND) THEN
339 RAISE NO_DATA_FOUND;
340 END IF;
341
342 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
343 trace(p_msg => 'END of procedure delete_row',
344 p_module => l_log_module,
345 p_level => C_LEVEL_PROCEDURE);
346 END IF;
347
348 END delete_row;
349
350 --=============================================================================
351 --
352 -- Name: load_row
353 -- Description: To be used by FNDLOAD to upload a row to the table
354 --
355 --=============================================================================
356 PROCEDURE load_row
357 (p_application_short_name IN VARCHAR2
358 ,p_entity_code IN VARCHAR2
359 ,p_transaction_id_col_name_1 IN VARCHAR2
360 ,p_transaction_id_col_name_2 IN VARCHAR2
361 ,p_transaction_id_col_name_3 IN VARCHAR2
362 ,p_transaction_id_col_name_4 IN VARCHAR2
363 ,p_source_id_col_name_1 IN VARCHAR2
364 ,p_source_id_col_name_2 IN VARCHAR2
365 ,p_source_id_col_name_3 IN VARCHAR2
366 ,p_source_id_col_name_4 IN VARCHAR2
367 ,p_owner IN VARCHAR2
368 ,p_last_update_date IN VARCHAR2)
369 IS
370 CURSOR c_app_id IS
371 SELECT application_id
372 FROM fnd_application
373 WHERE application_short_name = p_application_short_name;
374
375 l_application_id INTEGER;
376 l_rowid ROWID;
377 l_exist VARCHAR2(1);
378 f_luby NUMBER; -- entity owner in file
379 f_ludate DATE; -- entity update date in file
380 db_luby NUMBER; -- entity owner in db
381 db_ludate DATE; -- entity update date in db
382 l_log_module VARCHAR2(240);
383 BEGIN
384
385 IF g_log_enabled THEN
386 l_log_module := C_DEFAULT_MODULE||'.load_row';
387 END IF;
388
389 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
390 trace(p_msg => 'BEGIN of procedure load_row',
391 p_module => l_log_module,
392 p_level => C_LEVEL_PROCEDURE);
393 END IF;
394
395 -- Translate owner to file_last_updated_by
396 f_luby := fnd_load_util.owner_id(p_owner);
397
398 -- Translate char last_update_date to date
399 f_ludate := nvl(to_date(p_last_update_date, 'YYYY/MM/DD'), sysdate);
400
401 OPEN c_app_id;
402 FETCH c_app_id INTO l_application_id;
403 CLOSE c_app_id;
404
405 BEGIN
406
407 SELECT last_updated_by, last_update_date
408 INTO db_luby, db_ludate
409 FROM xla_entity_id_mappings
410 WHERE application_id = l_application_id
411 AND entity_code = p_entity_code;
412
413 IF (fnd_load_util.upload_test(f_luby, f_ludate, db_luby, db_ludate, NULL)) THEN
414 xla_entity_id_mappings_f_pkg.update_row
415 (x_application_id => l_application_id
416 ,x_entity_code => p_entity_code
417 ,x_transaction_id_col_name_1 => p_transaction_id_col_name_1
418 ,x_transaction_id_col_name_2 => p_transaction_id_col_name_2
419 ,x_transaction_id_col_name_3 => p_transaction_id_col_name_3
420 ,x_transaction_id_col_name_4 => p_transaction_id_col_name_4
421 ,x_source_id_col_name_1 => p_source_id_col_name_1
422 ,x_source_id_col_name_2 => p_source_id_col_name_2
426 ,x_last_updated_by => f_luby
423 ,x_source_id_col_name_3 => p_source_id_col_name_3
424 ,x_source_id_col_name_4 => p_source_id_col_name_4
425 ,x_last_update_date => f_ludate
427 ,x_last_update_login => 0);
428
429 END IF;
430
431 EXCEPTION
432 WHEN NO_DATA_FOUND THEN
433 xla_entity_id_mappings_f_pkg.insert_row
434 (x_rowid => l_rowid
435 ,x_application_id => l_application_id
436 ,x_entity_code => p_entity_code
437 ,x_transaction_id_col_name_1 => p_transaction_id_col_name_1
438 ,x_transaction_id_col_name_2 => p_transaction_id_col_name_2
439 ,x_transaction_id_col_name_3 => p_transaction_id_col_name_3
440 ,x_transaction_id_col_name_4 => p_transaction_id_col_name_4
441 ,x_source_id_col_name_1 => p_source_id_col_name_1
442 ,x_source_id_col_name_2 => p_source_id_col_name_2
443 ,x_source_id_col_name_3 => p_source_id_col_name_3
444 ,x_source_id_col_name_4 => p_source_id_col_name_4
445 ,x_creation_date => f_ludate
446 ,x_created_by => f_luby
447 ,x_last_update_date => f_ludate
448 ,x_last_updated_by => f_luby
449 ,x_last_update_login => 0);
450
451 END;
452
453 IF (C_LEVEL_PROCEDURE >= g_log_level) THEN
454 trace(p_msg => 'END of procedure load_row',
455 p_module => l_log_module,
456 p_level => C_LEVEL_PROCEDURE);
457 END IF;
458
459 EXCEPTION
460 WHEN NO_DATA_FOUND THEN
461 null;
462 WHEN OTHERS THEN
463 xla_exceptions_pkg.raise_message
464 (p_location => 'xla_entity_id_mappings_f_pkg.load_row');
465
466 END load_row;
467
468
469 --=============================================================================
470 --
471 -- Following code is executed when the package body is referenced for the first
472 -- time
473 --
474 --=============================================================================
475 BEGIN
476 g_log_level := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
477 g_log_enabled := fnd_log.test
478 (log_level => g_log_level
479 ,module => C_DEFAULT_MODULE);
480
481 IF NOT g_log_enabled THEN
482 g_log_level := C_LEVEL_LOG_DISABLED;
483 END IF;
484
485
486 end xla_entity_id_mappings_f_pkg;