[Home] [Help]
PACKAGE BODY: APPS.FND_IMP_DEPENDENCY_PKG
Source
1 Package Body FND_IMP_DEPENDENCY_PKG AS
2 /* $Header: afimpdepb.pls 120.4 2010/07/12 21:47:56 tshort ship $ */
3
4 -- global variables --
5 G_PKG_NAME CONSTANT VARCHAR2(30):='FND_IMP_DEPENDENCY';
6 G_FILE_NAME CONSTANT VARCHAR2(16):='afimpdepb.pls';
7
8 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
9 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
10
11 PROCEDURE INSERT_DEP_OBJECT(
12 p_api_version IN NUMBER,
13 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
14 p_commit IN VARCHAR2 := FND_API.G_FALSE,
15
16 p_object_id OUT NOCOPY fnd_imp_depobjects.object_id%TYPE,
17 p_snapshot_id IN fnd_imp_depobjects.snapshot_id%TYPE,
18 p_object_name IN fnd_imp_depobjects.object_name%TYPE,
19 p_object_type IN fnd_imp_depobjects.object_type%TYPE,
20 p_app_short_name IN fnd_imp_depobjects.app_short_name%TYPE,
21 p_file_directory IN fnd_imp_depobjects.file_directory%TYPE,
22 p_filename IN fnd_imp_depobjects.filename%TYPE,
23 p_file_type IN fnd_imp_depobjects.file_type%TYPE,
24 p_rcs_id IN fnd_imp_depobjects.rcs_id%TYPE,
25 p_ochksum IN fnd_imp_depobjects.ochksum%TYPE,
26 p_fchksum IN fnd_imp_depobjects.fchksum%TYPE,
27 p_attrib0 IN fnd_imp_depobjects.attrib0%TYPE,
28 p_attrib1 IN fnd_imp_depobjects.attrib1%TYPE,
29 p_attrib2 IN fnd_imp_depobjects.attrib2%TYPE,
30 p_attrib3 IN fnd_imp_depobjects.attrib3%TYPE,
31 p_attrib4 IN fnd_imp_depobjects.attrib4%TYPE,
32 p_attrib5 IN fnd_imp_depobjects.attrib5%TYPE,
33 p_attrib6 IN fnd_imp_depobjects.attrib6%TYPE,
34 p_attrib7 IN fnd_imp_depobjects.attrib7%TYPE,
35 p_attrib8 IN fnd_imp_depobjects.attrib8%TYPE,
36 p_attrib9 IN fnd_imp_depobjects.attrib9%TYPE,
37
38 p_object_version_number OUT NOCOPY fnd_imp_depobjects.object_version_number%TYPE,
39
40 x_return_status OUT NOCOPY VARCHAR2,
41 x_msg_count OUT NOCOPY NUMBER,
42 x_msg_data OUT NOCOPY VARCHAR2
43 ) AS
44 -- local variables --
45 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_DEP_OBJECT';
46 l_api_version NUMBER := p_api_version;
47
48 CURSOR sequence_cursor IS
49 SELECT fnd_imp_depobjects_s.NEXTVAL from dual;
50
51 BEGIN
52 -- Standard Start of API savepoint
53 SAVEPOINT INSERT_DEP_OBJECT;
54
55 -- Standard call to check for call compatibility.
56 IF NOT FND_API.Compatible_API_Call (
57 l_api_version,
58 p_api_version,
59 l_api_name,
60 G_PKG_NAME)
61 THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65 -- Initialize message list if p_init_msg_list is set to TRUE.
66 IF FND_API.To_Boolean( p_init_msg_list ) THEN
67 FND_MSG_PUB.initialize;
68 END IF;
69
70 -- Initialize API return status to success
71 x_return_status := FND_API.G_RET_STS_SUCCESS;
72
73 -- real logic --
74 ----------------
75
76
77 select object_id into p_object_id
78 from fnd_imp_depobjects
79 where snapshot_id = p_snapshot_id
80 and object_name = p_object_name
81 and object_type = p_object_type;
82
83 -----------------------
84 -- end of real logic --
85
86 -- Standard check of p_commit.
87 IF (FND_API.To_Boolean(p_commit)) THEN
88 COMMIT WORK;
89 END IF;
90
91 -- Standard call to get message count and if count is 1, get message info.
92 FND_MSG_PUB.Count_And_Get(
93 p_count => x_msg_count,
94 p_data => x_msg_data );
95
96 EXCEPTION
97 WHEN FND_API.G_EXC_ERROR THEN
98 ROLLBACK TO INSERT_DEP_OBJECT;
99 x_return_status := FND_API.G_RET_STS_ERROR ;
100
101 OPEN sequence_cursor;
102 FETCH sequence_cursor INTO p_object_id;
103 CLOSE sequence_cursor;
104 p_object_version_number := 1;
105 INSERT INTO fnd_imp_depobjects (object_id,
106 object_version_number,
107 created_by,
108 creation_date,
109 last_updated_by,
110 last_update_date,
111 last_update_login,
112 -- security_group_id,
113 snapshot_id,
114 object_name,
115 object_type,
116 app_short_name,
117 file_directory,
118 filename,
119 file_type,
120 rcs_id,
121 ochksum,
122 fchksum,
123 attrib0,
124 attrib1,
125 attrib2,
126 attrib3,
127 attrib4,
128 attrib5,
129 attrib6,
130 attrib7,
131 attrib8,
132 attrib9
133 )
134 VALUES (p_object_id,
135 p_object_version_number,
136 G_USER_ID,
137 SYSDATE,
138 G_USER_ID,
139 SYSDATE,
140 G_LOGIN_ID,
141 -- NULL,
142 p_snapshot_id,
143 p_object_name,
144 p_object_type,
145 p_app_short_name,
146 p_file_directory,
147 p_filename,
148 p_file_type,
149 p_rcs_id,
150 p_ochksum,
151 p_fchksum,
152 p_attrib0,
153 p_attrib1,
154 p_attrib2,
155 p_attrib3,
156 p_attrib4,
157 p_attrib5,
158 p_attrib6,
159 p_attrib7,
160 p_attrib8,
161 p_attrib9);
162
163 FND_MSG_PUB.Count_And_Get(
164 p_count => x_msg_count,
165 p_data => x_msg_data );
166
167 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
168 ROLLBACK TO INSERT_DEP_OBJECT;
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
170
171 OPEN sequence_cursor;
172 FETCH sequence_cursor INTO p_object_id;
173 CLOSE sequence_cursor;
174 p_object_version_number := 1;
175 INSERT INTO fnd_imp_depobjects (object_id,
176 object_version_number,
177 created_by,
178 creation_date,
179 last_updated_by,
180 last_update_date,
181 last_update_login,
182 -- security_group_id,
183 snapshot_id,
184 object_name,
185 object_type,
186 app_short_name,
187 file_directory,
188 filename,
189 file_type,
190 rcs_id,
191 ochksum,
192 fchksum,
193 attrib0,
194 attrib1,
195 attrib2,
196 attrib3,
197 attrib4,
198 attrib5,
199 attrib6,
200 attrib7,
201 attrib8,
202 attrib9
203 )
204 VALUES (p_object_id,
205 p_object_version_number,
206 G_USER_ID,
207 SYSDATE,
208 G_USER_ID,
209 SYSDATE,
210 G_LOGIN_ID,
211 -- NULL,
212 p_snapshot_id,
213 p_object_name,
214 p_object_type,
215 p_app_short_name,
216 p_file_directory,
217 p_filename,
218 p_file_type,
219 p_rcs_id,
220 p_ochksum,
221 p_fchksum,
222 p_attrib0,
223 p_attrib1,
224 p_attrib2,
225 p_attrib3,
226 p_attrib4,
227 p_attrib5,
228 p_attrib6,
229 p_attrib7,
230 p_attrib8,
231 p_attrib9);
232
233 FND_MSG_PUB.Count_And_Get(
234 p_count => x_msg_count,
235 p_data => x_msg_data );
236
237 WHEN OTHERS THEN
238 ROLLBACK TO INSERT_DEP_OBJECT;
239 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
240
241 OPEN sequence_cursor;
242 FETCH sequence_cursor INTO p_object_id;
243 CLOSE sequence_cursor;
244 p_object_version_number := 1;
245 INSERT INTO fnd_imp_depobjects (object_id,
246 object_version_number,
247 created_by,
248 creation_date,
249 last_updated_by,
250 last_update_date,
251 last_update_login,
252 -- security_group_id,
253 snapshot_id,
254 object_name,
255 object_type,
256 app_short_name,
257 file_directory,
258 filename,
259 file_type,
260 rcs_id,
261 ochksum,
262 fchksum,
263 attrib0,
264 attrib1,
265 attrib2,
266 attrib3,
267 attrib4,
268 attrib5,
269 attrib6,
270 attrib7,
271 attrib8,
272 attrib9
273 )
274 VALUES (p_object_id,
275 p_object_version_number,
276 G_USER_ID,
277 SYSDATE,
278 G_USER_ID,
279 SYSDATE,
280 G_LOGIN_ID,
281 -- NULL,
282 p_snapshot_id,
283 p_object_name,
284 p_object_type,
285 p_app_short_name,
286 p_file_directory,
287 p_filename,
288 p_file_type,
289 p_rcs_id,
290 p_ochksum,
291 p_fchksum,
292 p_attrib0,
293 p_attrib1,
294 p_attrib2,
295 p_attrib3,
296 p_attrib4,
297 p_attrib5,
298 p_attrib6,
299 p_attrib7,
300 p_attrib8,
301 p_attrib9);
302
303 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
304 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
305 END IF;
306
307 FND_MSG_PUB.Count_And_Get(
308 p_count => x_msg_count,
309 p_data => x_msg_data );
310
311 END INSERT_DEP_OBJECT;
312
313 PROCEDURE INSERT_DEP_RELATION(
314 p_api_version IN NUMBER,
315 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
316 p_commit IN VARCHAR2 := FND_API.G_FALSE,
317
318 p_parent_object_id OUT NOCOPY fnd_imp_depobjects.object_id%TYPE,
319 p_child_object_id OUT NOCOPY fnd_imp_depobjects.object_id%TYPE,
320
321 p_snapshot_id IN fnd_imp_depobjects.snapshot_id%TYPE,
322 p_dependency_type IN fnd_imp_deprelations.dependency_type%TYPE,
323
324 p_parent_object_name IN fnd_imp_depobjects.object_name%TYPE,
325 p_parent_object_type IN fnd_imp_depobjects.object_type%TYPE,
326 p_parent_app_short_name IN fnd_imp_depobjects.app_short_name%TYPE,
327 p_parent_file_directory IN fnd_imp_depobjects.file_directory%TYPE,
328 p_parent_filename IN fnd_imp_depobjects.filename%TYPE,
329 p_parent_file_type IN fnd_imp_depobjects.file_type%TYPE,
330 p_parent_rcs_id IN fnd_imp_depobjects.rcs_id%TYPE,
331 p_parent_ochksum IN fnd_imp_depobjects.ochksum%TYPE,
332 p_parent_fchksum IN fnd_imp_depobjects.fchksum%TYPE,
333 p_parent_attrib0 IN fnd_imp_depobjects.attrib0%TYPE,
334 p_parent_attrib1 IN fnd_imp_depobjects.attrib1%TYPE,
335 p_parent_attrib2 IN fnd_imp_depobjects.attrib2%TYPE,
336 p_parent_attrib3 IN fnd_imp_depobjects.attrib3%TYPE,
337 p_parent_attrib4 IN fnd_imp_depobjects.attrib4%TYPE,
338 p_parent_attrib5 IN fnd_imp_depobjects.attrib5%TYPE,
339 p_parent_attrib6 IN fnd_imp_depobjects.attrib6%TYPE,
340 p_parent_attrib7 IN fnd_imp_depobjects.attrib7%TYPE,
341 p_parent_attrib8 IN fnd_imp_depobjects.attrib8%TYPE,
342 p_parent_attrib9 IN fnd_imp_depobjects.attrib9%TYPE,
343
344 p_child_object_name IN fnd_imp_depobjects.object_name%TYPE,
345 p_child_object_type IN fnd_imp_depobjects.object_type%TYPE,
346 p_child_app_short_name IN fnd_imp_depobjects.app_short_name%TYPE,
347 p_child_file_directory IN fnd_imp_depobjects.file_directory%TYPE,
348 p_child_filename IN fnd_imp_depobjects.filename%TYPE,
349 p_child_file_type IN fnd_imp_depobjects.file_type%TYPE,
350 p_child_rcs_id IN fnd_imp_depobjects.rcs_id%TYPE,
351 p_child_ochksum IN fnd_imp_depobjects.ochksum%TYPE,
352 p_child_fchksum IN fnd_imp_depobjects.fchksum%TYPE,
353 p_child_attrib0 IN fnd_imp_depobjects.attrib0%TYPE,
354 p_child_attrib1 IN fnd_imp_depobjects.attrib1%TYPE,
355 p_child_attrib2 IN fnd_imp_depobjects.attrib2%TYPE,
356 p_child_attrib3 IN fnd_imp_depobjects.attrib3%TYPE,
357 p_child_attrib4 IN fnd_imp_depobjects.attrib4%TYPE,
358 p_child_attrib5 IN fnd_imp_depobjects.attrib5%TYPE,
359 p_child_attrib6 IN fnd_imp_depobjects.attrib6%TYPE,
360 p_child_attrib7 IN fnd_imp_depobjects.attrib7%TYPE,
361 p_child_attrib8 IN fnd_imp_depobjects.attrib8%TYPE,
362 p_child_attrib9 IN fnd_imp_depobjects.attrib9%TYPE,
363
364 p_object_version_number OUT NOCOPY fnd_imp_deprelations.object_version_number%TYPE,
365
366 x_return_status OUT NOCOPY VARCHAR2,
367 x_msg_count OUT NOCOPY NUMBER,
368 x_msg_data OUT NOCOPY VARCHAR2
369 ) AS
370 -- local variables --
371 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_DEP_RELATION';
372 l_api_version NUMBER := p_api_version;
373 l_parent_object_id NUMBER;
374 l_child_object_id NUMBER;
375 l_object_version_number NUMBER;
376 l_return_status VARCHAR2(2000);
377
378 BEGIN
379 -- Standard Start of API savepoint
380 SAVEPOINT INSERT_DEP_RELATION;
381
382 -- Standard call to check for call compatibility.
383 IF NOT FND_API.Compatible_API_Call (
384 l_api_version,
385 p_api_version,
386 l_api_name,
387 G_PKG_NAME)
388 THEN
389 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
390 END IF;
391
392 -- Initialize message list if p_init_msg_list is set to TRUE.
393 IF FND_API.To_Boolean( p_init_msg_list ) THEN
394 FND_MSG_PUB.initialize;
395 END IF;
396
397 -- Initialize API return status to success
398 x_return_status := FND_API.G_RET_STS_SUCCESS;
399
400 -- real logic --
401 ----------------
402
403 CLEAN_TABLES(p_snapshot_id);
404
405 INSERT_DEP_OBJECT( 1.0,
406 FND_API.G_TRUE,
407 FND_API.G_FALSE,
408 l_child_object_id,
409 p_snapshot_id,
410 p_child_object_name,
411 p_child_object_type,
412 p_child_app_short_name,
413 p_child_file_directory,
414 p_child_filename,
415 p_child_file_type,
416 p_child_rcs_id,
417 p_child_ochksum,
418 p_child_fchksum,
419 p_child_attrib0,
420 p_child_attrib1,
421 p_child_attrib2,
422 p_child_attrib3,
423 p_child_attrib4,
424 p_child_attrib5,
425 p_child_attrib6,
426 p_child_attrib7,
427 p_child_attrib8,
428 p_child_attrib9,
429 l_object_version_number,
430 l_return_status,
431 x_msg_count,
432 x_msg_data);
433
434 p_child_object_id := l_child_object_id;
435
436 INSERT_DEP_OBJECT( 1.0,
437 FND_API.G_TRUE,
438 FND_API.G_FALSE,
439 l_parent_object_id,
440 p_snapshot_id,
441 p_parent_object_name,
442 p_parent_object_type,
443 p_parent_app_short_name,
444 p_parent_file_directory,
445 p_parent_filename,
446 p_parent_file_type,
447 p_parent_rcs_id,
448 p_parent_ochksum,
449 p_parent_fchksum,
450 p_parent_attrib0,
451 p_parent_attrib1,
452 p_parent_attrib2,
453 p_parent_attrib3,
454 p_parent_attrib4,
455 p_parent_attrib5,
456 p_parent_attrib6,
457 p_parent_attrib7,
458 p_parent_attrib8,
459 p_parent_attrib9,
460 l_object_version_number,
461 l_return_status,
462 x_msg_count,
463 x_msg_data);
464
465 p_parent_object_id := l_parent_object_id;
466
467 p_object_version_number := 1;
468
469 INSERT INTO fnd_imp_deprelations ( object_version_number,
470 created_by,
471 creation_date,
472 last_updated_by,
473 last_update_date,
474 last_update_login,
475 -- security_group_id,
476 snapshot_id,
477 parent_object_id,
478 parent_object_name,
479 parent_app_short_name,
480 parent_object_type,
481 child_object_id,
482 child_object_name,
483 child_app_short_name,
484 child_object_type,
485 dependency_type,
486 graph_dependency)
487 VALUES ( p_object_version_number,
488 G_USER_ID,
489 SYSDATE,
490 G_USER_ID,
491 SYSDATE,
492 G_LOGIN_ID,
493 -- NULL,
494 p_snapshot_id,
495 p_parent_object_id,
496 p_parent_object_name,
497 p_parent_app_short_name,
498 p_parent_object_type,
499 p_child_object_id,
500 p_child_object_name,
501 p_child_app_short_name,
502 p_child_object_type,
503 p_dependency_type,
504 0);
505
506
507 -----------------------
508 -- end of real logic --
509
510 -- Standard check of p_commit.
511 IF (FND_API.To_Boolean(p_commit)) THEN
512 COMMIT WORK;
513 END IF;
514
515 -- Standard call to get message count and if count is 1, get message info.
516 FND_MSG_PUB.Count_And_Get(
517 p_count => x_msg_count,
518 p_data => x_msg_data );
519
520 EXCEPTION
521 WHEN FND_API.G_EXC_ERROR THEN
522 ROLLBACK TO INSERT_DEP_RELATION;
523 x_return_status := FND_API.G_RET_STS_ERROR ;
524
525 FND_MSG_PUB.Count_And_Get(
526 p_count => x_msg_count,
527 p_data => x_msg_data );
528
529 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
530 ROLLBACK TO INSERT_DEP_RELATION;
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
532
533 FND_MSG_PUB.Count_And_Get(
534 p_count => x_msg_count,
535 p_data => x_msg_data );
536
537 WHEN OTHERS THEN
538 ROLLBACK TO INSERT_DEP_RELATION;
539 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
540
541 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
542 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
543 END IF;
544
545 FND_MSG_PUB.Count_And_Get(
546 p_count => x_msg_count,
547 p_data => x_msg_data );
548
549 END INSERT_DEP_RELATION;
550
551 --9368381 - need to clean dependency tables after snapshot refresh as some data could be obsolete
552 PROCEDURE CLEAN_TABLES(snapshot_id__ IN NUMBER) IS
553 snapshot_date date;
554 BEGIN
555 select max(greatest(snapshot_creation_date, snapshot_update_date, creation_date, last_update_date))
556 into snapshot_date
557 from ad_snapshots
558 where snapshot_id = snapshot_id__;
559 DELETE FND_IMP_DEPRELATIONS
560 WHERE CREATION_DATE < snapshot_date;
561 DELETE FND_IMP_DEPOBJECTS
562 WHERE CREATION_DATE < snapshot_date;
563 END CLEAN_TABLES;
564
565
566 END FND_IMP_DEPENDENCY_PKG;