[Home] [Help]
PACKAGE BODY: APPS.CN_TABLE_MAPS_PVT
Source
1 PACKAGE BODY CN_TABLE_MAPS_PVT AS
2 /* $Header: cnvtmapb.pls 120.8 2006/01/11 23:53:52 apink noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_TABLE_MAPS_PVT';
5 G_LAST_UPDATE_DATE DATE := SYSDATE;
6 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
7 G_CREATION_DATE DATE := SYSDATE;
8 G_CREATED_BY NUMBER := fnd_global.user_id;
9 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
10
11 -----------------------------------------------------------------------------+
12 -- Procedure : Create_Map
13 -----------------------------------------------------------------------------+
14 PROCEDURE Create_Map (
15 p_api_version IN NUMBER,
16 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
17 p_commit IN VARCHAR2 := FND_API.G_FALSE,
18 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
19 x_return_status OUT NOCOPY VARCHAR2,
20 x_msg_count OUT NOCOPY NUMBER,
21 x_msg_data OUT NOCOPY VARCHAR2,
22 p_source_name IN VARCHAR2 ,
23 p_table_map_rec IN OUT NOCOPY table_map_rec_type,
24 x_event_id_out OUT NOCOPY NUMBER -- Modified For R12
25 ) IS
26 l_api_name CONSTANT VARCHAR2(30) := 'Create_Map';
27 l_api_version CONSTANT NUMBER := 1.0;
28 l_rowid ROWID;
29 l_event_id cn_events.event_id%TYPE;
30 l_application_repository_id cn_events.application_repository_id%TYPE;
31 l_parent_module_id cn_modules.parent_module_id%TYPE;
32 l_user_id NUMBER := nvl(fnd_profile.value('USER_ID'),-1);
33 l_column_map_id cn_column_maps.column_map_id%TYPE;
34 l_object_id cn_objects.object_id%TYPE;
35 l_table_map_object_id cn_table_map_objects.object_id%TYPE;
36 l_org_append VARCHAR2(100);
37 l_count NUMBER;
38
39 -- Variable For ORG_ID Value - MOAC Change
40 l_org_id NUMBER;
41
42 -- Variable for Notes
43 l_note_msg VARCHAR2(4000);
44 l_object_name VARCHAR2(4000);
45 x_note_id NUMBER;
46
47 CURSOR get_object_name(x_object_id NUMBER, x_org_id NUMBER) IS
48 SELECT name
49 FROM CN_OBJECTS
50 WHERE object_id = x_object_id
51 AND org_id = x_org_id;
52
53
54 BEGIN
55 -- Standard Start of API savepoint
56 SAVEPOINT Create_Map;
57 -- Standard call to check for call compatibility.
58 IF NOT FND_API.Compatible_API_Call (l_api_version,
59 p_api_version,
60 l_api_name,
61 G_PKG_NAME)
62 THEN
63 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
64 END IF;
65
66 -- Standard Check For Create Operations to validate the
67 -- ORG_ID by calling the common validation utility.
68
69
70 l_org_id := MO_GLOBAL.get_valid_org(p_table_map_rec.org_id);
71
72 cn_collection_gen.set_org_id(l_org_id);
73 l_org_append := cn_collection_gen.get_org_append;
74
75
76 IF l_org_id is NULL THEN
77 x_return_status := FND_API.G_RET_STS_ERROR;
78 RAISE FND_API.G_EXC_ERROR;
79 END IF;
80
81 -- Initialize message list if p_init_msg_list is set to TRUE.
82 IF FND_API.to_Boolean(p_init_msg_list) THEN
83 FND_MSG_PUB.initialize;
84 END IF;
85 -- Initialize API return status to success
86 x_return_status := FND_API.G_RET_STS_SUCCESS;
87 ------------------+
88 -- API body
89 ------------------+
90 --+
91 --+ Remove spaces in Mapping_Type
92 --+
93 p_table_map_rec.mapping_type := TRANSLATE(p_table_map_rec.mapping_type,' ','_');
94
95 p_table_map_rec.creation_date := SYSDATE;
96 p_table_map_rec.last_update_date := SYSDATE;
97
98 -- Check if the mapping type exists.
99 SELECT count(1)
100 INTO l_count
101 FROM cn_table_maps
102 WHERE mapping_type = p_table_map_rec.mapping_type
103 AND org_id = l_org_id;
104
105 IF l_count >= 1 THEN
106 -- Ensure that a mapping type must be unique.
107 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
108 THEN
109 fnd_message.set_name('CN', 'CL_UNIQUE_MAPPING_TYPE');
110 fnd_msg_pub.add;
111 END IF;
112 RAISE FND_API.G_EXC_ERROR;
113 END IF;
114
115 --+
116 --+ Get Repository_Id, Parent_Module_Id and Event_Id
117 --+
118 SELECT repository_id, module_id
119 INTO l_application_repository_id, l_parent_module_id
120 FROM cn_modules
121 WHERE module_type = 'COL'
122 AND org_id = l_org_id;
123
124 SELECT cn_events_s.NEXTVAL
125 INTO l_event_id
126 FROM dual;
127
128 --+
129 --+ Sending Event Id Back, As Required By TableMapsEO. If Update
130 --+ Happens Right After Create
131 --+ Modified For R12
132 --+
133 x_event_id_out := l_event_id;
134 --+
135 --+ Create Event
136 --+ Added ORG_ID MOAC Change
137 --+
138
139 cn_events_all_pkg.insert_row (
140 x_rowid => l_rowid,
141 x_event_id => l_event_id,
142 x_application_repository_id => l_application_repository_id,
143 x_description => NULL,
144 x_name => p_source_name,
145 x_creation_date => p_table_map_rec.creation_date,
146 x_created_by => p_table_map_rec.created_by,
147 x_last_update_date => p_table_map_rec.last_update_date,
148 x_last_updated_by => p_table_map_rec.last_updated_by,
149 x_last_update_login => p_table_map_rec.last_update_login,
150 x_org_id => p_table_map_rec.org_id);
151
152
153
154 --+
155 --+ Get Module_Id
156 --+
157 SELECT cn_modules_s.NEXTVAL
158 INTO p_table_map_rec.module_id
159 FROM dual;
160 --+
161 --+ Create Module using Source Name and Event Id
162 --+ Added ORG_ID MOAC Change
163 --+
164 cn_modules_pkg.insert_row (
165 x_rowid => l_rowid,
166 x_module_id => p_table_map_rec.module_id,
167 x_module_type => p_table_map_rec.mapping_type,
168 x_repository_id => l_application_repository_id,
169 x_description => NULL,
170 x_parent_module_id => l_parent_module_id,
171 x_source_repository_id => NULL,
172 x_module_status => 'UNSYNC',
173 x_event_id => l_event_id,
174 x_last_modification => SYSDATE,
175 x_last_synchronization => NULL,
176 x_output_filename => NULL,
177 x_collect_flag => 'YES',
178 x_name => p_source_name,
179 x_creation_date => p_table_map_rec.creation_date,
180 x_created_by => p_table_map_rec.created_by,
181 x_last_update_date => p_table_map_rec.last_update_date,
182 x_last_updated_by => p_table_map_rec.last_updated_by,
183 x_last_update_login => p_table_map_rec.last_update_login,
184 x_org_id => p_table_map_rec.org_id);
185
186 --+
187 --+ cn_table_maps.source_table_id is a legacy column, but is NOT NULL so we
188 --+ must fill it. However, we must make sure that it is the table to which
189 --+ the source_tbl_pkcol_id belongs...
190 --+
191 --+ SELECT table_id
192 --+ INTO p_table_map_rec.source_table_id
193 --+ FROM cn_obj_columns_v
194 --+ WHERE column_id = p_table_map_rec.source_tbl_pkcol_id;
195
196 SELECT table_id
197 INTO p_table_map_rec.source_table_id
198 FROM CN_OBJECTS
199 WHERE object_type = 'COL'
200 AND object_id = p_table_map_rec.source_tbl_pkcol_id
201 AND org_id = p_table_map_rec.org_id;
202
203 --+
204 --+ Select Destination Table Id For CN_COMM_LINES_API
205 --+ As It Is Required Column In CN_TABLE_MAPS_ALL
206 --+
207
208 SELECT object_id
209 INTO p_table_map_rec.destination_table_id
210 FROM cn_objects
211 WHERE name = 'CN_COMM_LINES_API'
212 AND org_id = p_table_map_rec.org_id;
213
214 --+ Create Table Map that points to Module
215 --+
216 IF p_table_map_rec.delete_flag IS NULL THEN
217 p_table_map_rec.delete_flag := 'N';
218 END IF;
219 cn_table_maps_pkg.insert_row(
220 x_rowid => l_rowid,
221 x_table_map_id => p_table_map_rec.table_map_id, -- autocreated if left null
222 x_mapping_type => p_table_map_rec.mapping_type,
223 x_module_id => p_table_map_rec.module_id,
224 x_source_table_id => p_table_map_rec.source_table_id,
225 x_source_tbl_pkcol_id => p_table_map_rec.source_tbl_pkcol_id,
226 x_destination_table_id => p_table_map_rec.destination_table_id,
227 x_source_hdr_tbl_pkcol_id => p_table_map_rec.source_hdr_tbl_pkcol_id,
228 x_source_tbl_hdr_fkcol_id => p_table_map_rec.source_tbl_hdr_fkcol_id,
229 x_notify_where => p_table_map_rec.notify_where,
230 x_collect_where => p_table_map_rec.collect_where,
231 x_delete_flag => p_table_map_rec.delete_flag,
232 x_creation_date => p_table_map_rec.creation_date,
233 x_created_by => p_table_map_rec.created_by,
234 x_org_id => p_table_map_rec.org_id);
235
236 --+
237 --+ Create mandatory column-mapping mapping rows
238 --+
239
240 FOR rec IN
241 (SELECT *
242 FROM cn_column_maps
243 WHERE table_map_id = -999
244 AND org_id = l_org_id) -- MOAC Need To Verify
245 LOOP
246 l_column_map_id := NULL; -- set inside the procedure
247 cn_column_maps_pkg.insert_row (
248 x_rowid => l_rowid,
249 x_column_map_id => l_column_map_id,
250 x_destination_column_id => rec.destination_column_id,
251 x_table_map_id => p_table_map_rec.table_map_id,
252 x_expression => rec.expression,
253 x_editable => rec.editable,
254 x_modified => rec.modified,
255 x_update_clause => rec.update_clause,
256 x_calc_ext_table_id => rec.calc_ext_table_id,
257 --x_creation_date => p_table_map_rec.creation_date,
258 x_creation_date => SYSDATE,
259 x_created_by => p_table_map_rec.created_by,
260 x_org_id => p_table_map_rec.org_id);
261 END LOOP;
262 --+
263 --+ Create Collection Package Spec and Body objects
264 --+
265
266 Create_Table_Map_Object (
267 p_api_version => 1.0,
268 x_return_status => x_return_status,
269 x_msg_count => x_msg_count,
270 x_msg_data => x_msg_data,
271 p_table_map_id => p_table_map_rec.table_map_id,
272 p_object_name => 'cn_collect_'||LOWER(p_table_map_rec.mapping_type)||l_org_append,
273 p_tm_object_type => 'PKS',
274 p_creation_date => p_table_map_rec.creation_date,
275 p_created_by => p_table_map_rec.created_by,
276 x_table_map_object_id => l_table_map_object_id,
277 x_object_id => l_object_id,
278 x_org_id => p_table_map_rec.org_id);
279
280 Create_Table_Map_Object (
281 p_api_version => 1.0,
282 x_return_status => x_return_status,
283 x_msg_count => x_msg_count,
284 x_msg_data => x_msg_data,
285 p_table_map_id => p_table_map_rec.table_map_id,
286 p_object_name => 'cn_collect_'||LOWER(p_table_map_rec.mapping_type)||l_org_append,
287 p_tm_object_type => 'PKB',
288 p_creation_date => p_table_map_rec.creation_date,
289 p_created_by => p_table_map_rec.created_by,
290 x_table_map_object_id => l_table_map_object_id,
291 x_object_id => l_object_id,
292 x_org_id => p_table_map_rec.org_id);
293
294 ------------------+
295 -- End of API body.
296 ------------------+
297 -- Standard check of p_commit.
298 --IF FND_API.To_Boolean( p_commit ) THEN
299 -- COMMIT WORK;
300 --END IF;
301 -- Standard call to get message count and if count is 1, get message info.
302 FND_MSG_PUB.Count_And_Get
303 (p_count => x_msg_count ,
304 p_data => x_msg_data ,
305 p_encoded => FND_API.G_FALSE);
306
307 -- Creating notes for the new Transaction Source. Code written here because the
308 -- sequence is generated in the Pl/SQL call
309 FND_MESSAGE.SET_NAME('CN', 'CN_TRANS_SRC_ATTR_INSERT_NOTE');
310 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Transaction Source');
311 FND_MESSAGE.SET_TOKEN('VALUE',p_source_name);
312 l_note_msg := FND_MESSAGE.GET;
313
314 jtf_notes_pub.create_note
315 ( p_api_version => 1.0,
316 x_return_status => x_return_status,
317 x_msg_count => x_msg_count,
318 x_msg_data => x_msg_data,
319 p_source_object_id => p_table_map_rec.table_map_id,
320 p_source_object_code => 'CN_TABLE_MAPS',
321 p_notes => l_note_msg,
322 p_notes_detail => l_note_msg,
323 p_note_type => 'CN_SYSGEN',
324 x_jtf_note_id => x_note_id
325 );
326
327 -- Adding notes for Transaction Source Type
328 FND_MESSAGE.SET_NAME('CN', 'CN_TRANS_SRC_ATTR_INSERT_NOTE');
329 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Transaction Source Type');
330 FND_MESSAGE.SET_TOKEN('VALUE',p_table_map_rec.mapping_type);
331 l_note_msg := FND_MESSAGE.GET;
332
333 jtf_notes_pub.create_note
334 ( p_api_version => 1.0,
335 x_return_status => x_return_status,
336 x_msg_count => x_msg_count,
337 x_msg_data => x_msg_data,
338 p_source_object_id => p_table_map_rec.table_map_id,
339 p_source_object_code => 'CN_TABLE_MAPS',
340 p_notes => l_note_msg,
341 p_notes_detail => l_note_msg,
342 p_note_type => 'CN_SYSGEN',
343 x_jtf_note_id => x_note_id
344 );
345
346 -- Creating notes for Source Table entry
347 OPEN get_object_name(p_table_map_rec.source_table_id, p_table_map_rec.org_id);
348 FETCH get_object_name INTO l_object_name;
349 CLOSE get_object_name;
350
351 FND_MESSAGE.SET_NAME('CN', 'CN_TRANS_SRC_ATTR_INSERT_NOTE');
352 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Line Table');
353 FND_MESSAGE.SET_TOKEN('VALUE',l_object_name);
354 l_note_msg := FND_MESSAGE.GET;
355
356 jtf_notes_pub.create_note
357 ( p_api_version => 1.0,
358 x_return_status => x_return_status,
359 x_msg_count => x_msg_count,
360 x_msg_data => x_msg_data,
361 p_source_object_id => p_table_map_rec.table_map_id,
362 p_source_object_code => 'CN_TABLE_MAPS',
363 p_notes => l_note_msg,
364 p_notes_detail => l_note_msg,
365 p_note_type => 'CN_SYSGEN',
366 x_jtf_note_id => x_note_id
367 );
368
369 -- Creating notes for Source Table Key Column entry
370 OPEN get_object_name(p_table_map_rec.source_tbl_pkcol_id, p_table_map_rec.org_id);
371 FETCH get_object_name INTO l_object_name;
372 CLOSE get_object_name;
373
374 FND_MESSAGE.SET_NAME('CN', 'CN_TRANS_SRC_ATTR_INSERT_NOTE');
375 FND_MESSAGE.SET_TOKEN('ATTRIBUTE', 'Line Table Key Column');
376 FND_MESSAGE.SET_TOKEN('VALUE',l_object_name);
377 l_note_msg := FND_MESSAGE.GET;
378
379 jtf_notes_pub.create_note
380 ( p_api_version => 1.0,
381 x_return_status => x_return_status,
382 x_msg_count => x_msg_count,
383 x_msg_data => x_msg_data,
384 p_source_object_id => p_table_map_rec.table_map_id,
385 p_source_object_code => 'CN_TABLE_MAPS',
386 p_notes => l_note_msg,
387 p_notes_detail => l_note_msg,
388 p_note_type => 'CN_SYSGEN',
389 x_jtf_note_id => x_note_id
390 );
391
392 EXCEPTION
393 WHEN FND_API.G_EXC_ERROR THEN
394 ROLLBACK TO Create_Map;
395 x_return_status := FND_API.G_RET_STS_ERROR ;
396 FND_MSG_PUB.Count_And_Get
397 (p_count => x_msg_count,
398 p_data => x_msg_data,
399 p_encoded => FND_API.G_FALSE);
400 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
401 ROLLBACK TO Create_Map;
402 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
403 FND_MSG_PUB.Count_And_Get
404 (p_count => x_msg_count,
405 p_data => x_msg_data,
406 p_encoded => FND_API.G_FALSE);
407 WHEN OTHERS THEN
408 ROLLBACK TO Create_Map;
409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
410 IF FND_MSG_PUB.Check_Msg_Level
411 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
412 FND_MSG_PUB.Add_Exc_Msg
413 (G_PKG_NAME,
414 l_api_name );
415 END IF;
416 FND_MSG_PUB.Count_And_Get
417 (p_count => x_msg_count,
418 p_data => x_msg_data,
419 p_encoded => FND_API.G_FALSE);
420 END Create_Map;
421
422
423 -----------------------------------------------------------------------------+
424 -- Procedure : Delete_Map
425 -- HITHANKI Modified For R12
426 -----------------------------------------------------------------------------+
427 PROCEDURE Delete_Map
428 ( p_api_version IN NUMBER,
429 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
430 p_commit IN VARCHAR2 := FND_API.G_FALSE,
431 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_msg_count OUT NOCOPY NUMBER,
434 x_msg_data OUT NOCOPY VARCHAR2,
435 p_table_map_id IN NUMBER,
436 p_org_id IN NUMBER -- Added For R12 MOAC
437 ) IS
438 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Map';
439 l_api_version CONSTANT NUMBER := 1.0;
440 l_rowid ROWID;
441 l_event_id cn_events.event_id%TYPE;
442 l_pkg_spec_id cn_objects.object_id%TYPE;
443 l_pkg_body_id cn_objects.object_id%TYPE;
444
445 CURSOR c_table_map IS
446 SELECT * FROM cn_table_maps_v
447 WHERE table_map_id = p_table_map_id
448 AND org_id = p_org_id; -- Added For MOAC
449 -- Need To Change This View Definition
450 -- And Then Add This Clause
451 --AND org_id = p_org_id; -- Added For MOAC
452
453 l_table_map_rec c_table_map%ROWTYPE;
454 l_org_append VARCHAR2(100) ;
455
456 CURSOR l_pks_csr(p_mapping_type VARCHAR2,p_org_append VARCHAR2) IS
457 SELECT object_id
458 FROM cn_objects
459 WHERE object_type = 'PKS'
460 AND name = 'cn_collect_'||LOWER(p_mapping_type)||p_org_append
461 AND org_id = p_org_id; -- Added For MOAC
462
463 CURSOR l_pkb_csr(p_mapping_type VARCHAR2,p_org_append VARCHAR2) IS
464 SELECT object_id
465 FROM cn_objects
466 WHERE object_type = 'PKB'
467 AND name = 'cn_collect_'||LOWER(p_mapping_type)||p_org_append
468 AND org_id = p_org_id; -- Added For MOAC
469
470 BEGIN
471 -- Standard Start of API savepoint
472 SAVEPOINT Delete_Map;
473 -- Standard call to check for call compatibility.
474 IF NOT FND_API.Compatible_API_Call (l_api_version,
475 p_api_version,
476 l_api_name,
477 G_PKG_NAME)
478 THEN
479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
480 END IF;
481 -- Initialize message list if p_init_msg_list is set to TRUE.
482 IF FND_API.to_Boolean( p_init_msg_list ) THEN
483 FND_MSG_PUB.initialize;
484 END IF;
485 -- Initialize API return status to success
486 x_return_status := FND_API.G_RET_STS_SUCCESS;
487
488
489 --Changed by Ashley as part of MOACing exercise
490 cn_collection_gen.set_org_id(p_org_id);
491 l_org_append := cn_collection_gen.get_org_append;
492
493 -------------------+
494 -- API body
495 -------------------+
496 --+
497 --+ Cannot delete seeded table map
498 --+
499 IF p_table_map_id < 0 THEN
500 RAISE FND_API.G_EXC_ERROR;
501 END IF;
502 --+
503 --+ Get information about the map to be deleted
504 --+
505 OPEN c_table_map;
506 FETCH c_table_map INTO l_table_map_rec;
507 CLOSE c_table_map;
508 --+
509 --+ Delete Event
510 --+
511 cn_events_all_pkg.delete_row (x_event_id => l_table_map_rec.event_id,
512 x_org_id => p_org_id); -- Added For R12 MOAC
513 --+
514 --+ Delete Module
515 --+
516 cn_modules_pkg.delete_row (x_module_id => l_table_map_rec.module_id,
517 x_org_id => p_org_id); -- Added For R12 MOAC
518 --+
519 --+ Delete Table Map
520 --+
521 cn_table_maps_pkg.delete_row(x_table_map_id => p_table_map_id,
522 x_org_id => p_org_id); -- Added For R12 MOAC
523 --+
524 --+ Delete column-mapping mapping rows
525 --+
526 DELETE FROM cn_column_maps
527 WHERE table_map_id = p_table_map_id
528 AND org_id = p_org_id; -- Added For R12 MOAC
529 --+
530 --+ Delete Collection Package Spec and Body objects
531 --+
532
533 -- SELECT object_id
534 -- INTO l_pkg_spec_id
535 -- FROM cn_objects
536 -- WHERE object_type = 'PKS'
537 -- AND name = 'cn_collect_'||LOWER(l_table_map_rec.mapping_type)||l_org_append;
538 -- SELECT object_id
539 -- INTO l_pkg_body_id
540 -- FROM cn_objects
541 -- WHERE object_type = 'PKB'
542 -- AND name = 'cn_collect_'||LOWER(l_table_map_rec.mapping_type)||l_org_append;
543
544 OPEN l_pks_csr(l_table_map_rec.mapping_type, l_org_append);
545 FETCH l_pks_csr INTO l_pkg_spec_id;
546 CLOSE l_pks_csr;
547
548 OPEN l_pkb_csr(l_table_map_rec.mapping_type, l_org_append);
549 FETCH l_pkb_csr INTO l_pkg_body_id;
550 CLOSE l_pkb_csr;
551
552 DELETE FROM cn_source WHERE object_id IN (l_pkg_spec_id, l_pkg_body_id);
553 DELETE FROM cn_objects WHERE object_id IN (l_pkg_spec_id, l_pkg_body_id);
554 --+
555 -- Delete Table Map Objects
556 --+
557 FOR rec IN
558 (SELECT table_map_object_id,
559 UPPER(tm_object_type) tm_object_type,
560 object_id
561 FROM cn_table_map_objects
562 WHERE table_map_id = p_table_map_id
563 AND org_id = p_org_id) -- Added For R12 MOAC
564 LOOP
565 IF rec.tm_object_type IN ('PARAM','FILTER','PKS','PKB') THEN
566 --+
567 -- Object belongs exclusively to Collections and can be deleted
568 --+
569 delete_table_map_Object (
570 p_api_version => 1.0,
571 x_return_status => x_return_status,
572 x_msg_count => x_msg_count,
573 x_msg_data => x_msg_data,
574 p_table_map_object_id => rec.table_map_object_id,
575 x_org_id => p_org_id); -- Added For R12 MOAC
576 ELSE
577 --+
578 -- Collections only created the reference to the object, so just
579 -- delete that.
580 --+
581 cn_table_map_objects_pkg.delete_row(rec.table_map_object_id,p_org_id);
582 END IF;
583 END LOOP;
584 -------------------+
585 -- End of API body.
586 -------------------+
587 -- Standard check of p_commit.
588 --IF FND_API.To_Boolean( p_commit ) THEN
589 -- COMMIT WORK;
590 --END IF;
591 -- Standard call to get message count and if count is 1, get message info.
592 FND_MSG_PUB.Count_And_Get
593 (p_count => x_msg_count ,
594 p_data => x_msg_data ,
595 p_encoded => FND_API.G_FALSE);
596 EXCEPTION
597 WHEN FND_API.G_EXC_ERROR THEN
598 ROLLBACK TO Delete_Map;
599 x_return_status := FND_API.G_RET_STS_ERROR ;
600 FND_MSG_PUB.Count_And_Get
601 (p_count => x_msg_count,
602 p_data => x_msg_data,
603 p_encoded => FND_API.G_FALSE);
604 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
605 ROLLBACK TO Delete_Map;
606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
607 FND_MSG_PUB.Count_And_Get
608 (p_count => x_msg_count,
609 p_data => x_msg_data,
610 p_encoded => FND_API.G_FALSE);
611 WHEN OTHERS THEN
612 ROLLBACK TO Delete_Map;
613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
614 IF FND_MSG_PUB.Check_Msg_Level
615 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
616 FND_MSG_PUB.Add_Exc_Msg
617 (G_PKG_NAME,
618 l_api_name );
619 END IF;
620 FND_MSG_PUB.Count_And_Get
621 (p_count => x_msg_count,
622 p_data => x_msg_data,
623 p_encoded => FND_API.G_FALSE);
624 END Delete_Map;
625
626
627 -----------------------------------------------------------------------------+
628 -- Procedure : Update_Table_Map_Objects
629 -----------------------------------------------------------------------------+
630
631 PROCEDURE Update_Table_Map_Objects
632 (
633 p_api_version IN NUMBER,
634 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
635 p_commit IN VARCHAR2 := FND_API.G_FALSE,
636 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
637 x_return_status OUT NOCOPY VARCHAR2,
638 x_msg_count OUT NOCOPY NUMBER,
639 x_msg_data OUT NOCOPY VARCHAR2,
640 p_table_map_id IN NUMBER,
641 p_delete_flag IN VARCHAR2,
642 p_object_name IN VARCHAR2,
643 p_object_id IN NUMBER,
644 p_object_value IN VARCHAR2,
645 p_object_version_number IN OUT NOCOPY NUMBER,
646 x_org_id IN NUMBER) -- Added For R12 MOAC
647 IS
648
649 l_api_name CONSTANT VARCHAR2(30) := 'Update_Table_Map_Objects';
650 l_api_version CONSTANT NUMBER := 1.0;
651 l_ovn_obj_number cn_objects.object_version_number%TYPE;
652 l_ovn_tbl_number cn_table_maps.object_version_number%TYPE;
653
654 l_org_id NUMBER; -- Added For R12 MOAC
655
656 CURSOR l_ovn_obj IS
657 SELECT object_version_number
658 FROM cn_objects
659 WHERE object_id = p_object_id
660 AND org_id = x_org_id; -- Added For R12 MOAC
661
662 CURSOR l_ovn_tbl IS
663 SELECT object_version_number
664 FROM cn_table_maps
665 WHERE table_map_id = p_table_map_id
666 AND org_id = x_org_id; -- Added For R12 MOAC
667
668 BEGIN
669 -- Standard Start of API savepoint
670 SAVEPOINT update_trx_source_sv;
671 -- Standard call to check for call compatibility.
672 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
673 p_api_version ,
674 l_api_name,
675 G_PKG_NAME ) THEN
676 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
677 END IF;
678
679 -- Initialize message list if p_init_msg_list is set to TRUE.
680 IF FND_API.to_Boolean( p_init_msg_list ) THEN
681 FND_MSG_PUB.initialize;
682 END IF;
683
684 -- Initialize API return status to success
685 x_return_status := FND_API.G_RET_STS_SUCCESS;
686 -- API Body Begin
687
688 -- check if the object version number is the same
689 OPEN l_ovn_obj;
690 FETCH l_ovn_obj INTO l_ovn_obj_number;
691 CLOSE l_ovn_obj;
692
693 OPEN l_ovn_tbl;
694 FETCH l_ovn_tbl INTO l_ovn_tbl_number;
695 CLOSE l_ovn_tbl;
696
697 IF (l_ovn_obj_number <> p_object_version_number)
698 THEN
699
700 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
701 THEN
702 fnd_message.set_name('CN', 'CL_INVALID_OVN');
703 fnd_msg_pub.add;
704 END IF;
705
706 RAISE FND_API.G_EXC_ERROR;
707
708 END IF;
709
710 l_org_id := x_org_id;
711
712 IF p_delete_flag = 'Y'
713 THEN
714 UPDATE cn_table_maps
715 SET delete_flag = p_delete_flag,
716 object_version_number = l_ovn_tbl_number + 1
717 WHERE table_map_id = p_table_map_id
718 AND org_id = l_org_id;
719 END IF;
720
721 UPDATE CN_OBJECTS
722 SET NAME = p_object_name,
723 OBJECT_VALUE = p_object_value
724 WHERE OBJECT_ID = p_object_id
725 AND org_id = l_org_id;
726
727 FND_MSG_PUB.Count_And_Get
728 (p_count => x_msg_count ,
729 p_data => x_msg_data ,
730 p_encoded => FND_API.G_FALSE );
731 EXCEPTION
732 WHEN FND_API.G_EXC_ERROR THEN
733 ROLLBACK TO update_trx_source_sv;
734 x_return_status := FND_API.G_RET_STS_ERROR ;
735 FND_MSG_PUB.Count_And_Get(
736 p_count => x_msg_count ,
737 p_data => x_msg_data ,
738 p_encoded => FND_API.G_FALSE);
739 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
740 ROLLBACK TO update_trx_source_sv;
741 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
742 FND_MSG_PUB.Count_And_Get(
743 p_count => x_msg_count ,
744 p_data => x_msg_data ,
745 p_encoded => FND_API.G_FALSE);
746 WHEN OTHERS THEN
747 ROLLBACK TO update_trx_source_sv;
748 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
749 IF FND_MSG_PUB.Check_Msg_Level(
750 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
751 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
752 END IF;
753 FND_MSG_PUB.Count_And_Get(
754 p_count => x_msg_count ,
755 p_data => x_msg_data ,
756 p_encoded => FND_API.G_FALSE);
757
758 END Update_Table_Map_Objects;
759
760 -----------------------------------------------------------------------------+
761 -- Procedure : Update_Map
762 -----------------------------------------------------------------------------+
763 PROCEDURE Update_Map
764 (
765 p_api_version IN NUMBER,
766 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
767 p_commit IN VARCHAR2 := FND_API.G_FALSE,
768 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
769 x_return_status OUT NOCOPY VARCHAR2,
770 x_msg_count OUT NOCOPY NUMBER,
771 x_msg_data OUT NOCOPY VARCHAR2,
772 p_table_map_id IN NUMBER,
773 p_mapping_type IN VARCHAR2,
774 p_module_id IN NUMBER,
775 p_source_table_id IN NUMBER,
776 p_source_tbl_pkcol_id IN NUMBER,
777 p_destination_table_id IN NUMBER,
778 p_source_hdr_tbl_pkcol_id IN NUMBER,
779 p_source_tbl_hdr_fkcol_id IN NUMBER,
780 p_notify_where IN VARCHAR2,
781 p_collect_where IN VARCHAR2,
782 p_delete_flag IN VARCHAR2,
783 p_event_id IN NUMBER,
784 p_event_name IN VARCHAR2,
785 p_object_version_number IN OUT NOCOPY NUMBER,
786 x_org_id IN NUMBER) -- Added For R12 MOAC
787 IS
788
789 l_api_name CONSTANT VARCHAR2(30) := 'update_trx_source';
790 l_api_version CONSTANT NUMBER := 1.0;
791 l_object_version_number cn_table_maps.object_version_number%TYPE;
792 l_evn_object_version_number cn_events_all_b.object_version_number%TYPE := 1;
793 --l_mod_object_version_number cn_modules_all_b.object_version_number%TYPE;
794 l_org_id NUMBER; -- Added For R12 MOAC
795
796 CURSOR l_ovn_csr IS
797 SELECT object_version_number
798 FROM cn_table_maps
799 WHERE table_map_id = p_table_map_id
800 AND org_id = x_org_id; -- Added For R12 MOAC
801
802
803 BEGIN
804 -- Standard Start of API savepoint
805 SAVEPOINT update_trx_source_sv;
806 -- Standard call to check for call compatibility.
807 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
808 p_api_version ,
809 l_api_name,
810 G_PKG_NAME ) THEN
811 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
812 END IF;
813 -- Initialize message list if p_init_msg_list is set to TRUE.
814 IF FND_API.to_Boolean( p_init_msg_list ) THEN
815 FND_MSG_PUB.initialize;
816 END IF;
817 -- Initialize API return status to success
818 x_return_status := FND_API.G_RET_STS_SUCCESS;
819 -- API Body Begin
820
821 -- check if the object version number is the same
822 OPEN l_ovn_csr;
823 FETCH l_ovn_csr INTO l_object_version_number;
824 CLOSE l_ovn_csr;
825
826
827
828 if (l_object_version_number <> p_object_version_number) THEN
829
830 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error)
831 THEN
832 fnd_message.set_name('CN', 'CL_INVALID_OVN');
833 fnd_msg_pub.add;
834 END IF;
835 RAISE FND_API.G_EXC_ERROR;
836
837 end if;
838
839 l_org_id := x_org_id;
840
841
842
843 cn_table_maps_pkg.update_row(x_table_map_id=> p_table_map_id,
844 x_mapping_type => p_mapping_type,
845 x_module_id => p_module_id,
846 x_source_table_id => p_source_table_id,
847 x_source_tbl_pkcol_id => p_source_tbl_pkcol_id,
848 x_destination_table_id => p_destination_table_id,
849 x_source_hdr_tbl_pkcol_id => p_source_hdr_tbl_pkcol_id,
850 x_source_tbl_hdr_fkcol_id => p_source_tbl_hdr_fkcol_id,
851 x_notify_where => p_notify_where,
852 x_collect_where => p_collect_where,
853 x_delete_flag => p_delete_flag,
854 x_last_update_date => G_LAST_UPDATE_DATE,
855 x_last_updated_by => G_LAST_UPDATED_BY,
856 x_last_update_login => g_last_update_login,
857 x_object_version_number => p_object_version_number,
858 x_org_id => l_org_id); -- Added For R12 MOAC
859
860 IF p_event_id IS NOT NULL THEN
861 cn_events_all_pkg.update_row
862 (x_event_id => p_event_id,
863 x_application_repository_id => 100,
864 x_description => NULL,
865 x_name => p_event_name,
866 x_last_update_date => G_LAST_UPDATE_DATE,
867 x_last_updated_by => G_LAST_UPDATED_BY,
868 x_last_update_login => G_LAST_UPDATE_LOGIN,
869 x_org_id => l_org_id,
870 p_object_version_number => l_evn_object_version_number); -- Added For R12 MOAC
871 END IF;
872
873
874 p_object_version_number := l_object_version_number + 1;
875
876 -- End of API body.
877 -- Standard check of p_commit.
878 --IF FND_API.To_Boolean( p_commit ) THEN
879 -- COMMIT WORK;
880 --END IF;
881 -- Standard call to get message count and if count is 1, get message info.
882 FND_MSG_PUB.Count_And_Get
883 (p_count => x_msg_count ,
884 p_data => x_msg_data ,
885 p_encoded => FND_API.G_FALSE );
886 EXCEPTION
887 WHEN FND_API.G_EXC_ERROR THEN
888 ROLLBACK TO update_trx_source_sv;
889 x_return_status := FND_API.G_RET_STS_ERROR ;
890 FND_MSG_PUB.Count_And_Get(
891 p_count => x_msg_count ,
892 p_data => x_msg_data ,
893 p_encoded => FND_API.G_FALSE);
894 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
895 ROLLBACK TO update_trx_source_sv;
896 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
897 FND_MSG_PUB.Count_And_Get(
898 p_count => x_msg_count ,
899 p_data => x_msg_data ,
900 p_encoded => FND_API.G_FALSE);
901 WHEN OTHERS THEN
902 ROLLBACK TO update_trx_source_sv;
903 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
904 IF FND_MSG_PUB.Check_Msg_Level(
905 FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
906 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,l_api_name );
907 END IF;
908 FND_MSG_PUB.Count_And_Get(
909 p_count => x_msg_count ,
910 p_data => x_msg_data ,
911 p_encoded => FND_API.G_FALSE);
912 END Update_Map;
913
914
915
916 ------------------------------------------------------------------------------+
917 -- Procedure : Create_Table_Map_Object
918 ------------------------------------------------------------------------------+
919 -- WARNING: only use this procedure to create a table map object that
920 -- does not yet exist in CN_OBJECTS. If you are creating a
921 -- table map object which references an existing object (for
922 -- example an Extra Collection Table) then just use the
923 -- cn_table_map_objects_pkg.insert_row procedure.
924 PROCEDURE Create_Table_Map_Object (
925 p_api_version IN NUMBER,
926 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
927 p_commit IN VARCHAR2 := FND_API.G_FALSE,
928 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
929 x_return_status OUT NOCOPY VARCHAR2,
930 x_msg_count OUT NOCOPY NUMBER,
931 x_msg_data OUT NOCOPY VARCHAR2,
932 p_table_map_id IN NUMBER,
933 p_object_name IN VARCHAR2,
934 p_object_value IN VARCHAR2 := NULL,
935 p_tm_object_type IN VARCHAR2,
936 p_creation_date IN DATE,
937 p_created_by IN NUMBER,
938 x_table_map_object_id OUT NOCOPY NUMBER,
939 x_object_id OUT NOCOPY NUMBER,
940 x_org_id IN NUMBER) IS
941
942 l_api_name CONSTANT VARCHAR2(30) := 'Create_Table_Map_Object';
943 l_api_version CONSTANT NUMBER := 1.0;
944 l_rowid ROWID;
945 l_application_repository_id cn_events.application_repository_id%TYPE;
946 l_org_id NUMBER;
947 BEGIN
948 -- Standard Start of API savepoint
949 SAVEPOINT Create_Table_Map_Object;
950 -- Standard call to check for call compatibility.
951 IF NOT FND_API.Compatible_API_Call (l_api_version,
952 p_api_version,
953 l_api_name,
954 G_PKG_NAME)
955 THEN
956 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
957 END IF;
958 -- Initialize message list if p_init_msg_list is set to TRUE.
959 IF FND_API.to_Boolean( p_init_msg_list ) THEN
960 FND_MSG_PUB.initialize;
961 END IF;
962 -- Initialize API return status to success
963 x_return_status := FND_API.G_RET_STS_SUCCESS;
964
965 l_org_id := x_org_id;
966
967 -------------------+
968 -- API body
969 -------------------+
970 SELECT repository_id
971 INTO l_application_repository_id
972 FROM cn_modules_all_b
973 WHERE module_type = 'COL' -- name = 'Collection';
974 AND org_id = x_org_id; -- MOAC Change Need To Verify
975 --+
976
977 SELECT cn_objects_s.NEXTVAL
978 INTO x_object_id
979 FROM dual;
980 --+
981 -- Create the object in CN_OBJECTS
982 --+
983
984 cn_objects_pkg.insert_row(
985 x_rowid => l_rowid,
986 x_object_id => x_object_id,
987 x_dependency_map_complete => 'N',
988 x_name => p_object_name,
989 x_object_value => p_object_value,
990 x_description => 'Custom Data Source Collection Object',
991 x_object_type => UPPER(p_tm_object_type),
992 x_repository_id => l_application_repository_id,
993 x_next_synchronization_date => NULL,
994 x_synchronization_frequency => NULL,
995 x_object_status => 'A',
996 X_org_id => l_org_id);
997 --+
998 -- Create the reference to the object in CN_TABLE_MAP_OBJECTS
999 --+
1000
1001 cn_table_map_objects_pkg.insert_row(
1002 x_rowid => l_rowid,
1003 x_table_map_object_id => x_table_map_object_id, --set inside procedure
1004 x_tm_object_type => UPPER(p_tm_object_type),
1005 x_table_map_id => p_table_map_id,
1006 x_object_id => x_object_id,
1007 x_creation_date => p_creation_date,
1008 x_created_by => p_created_by,
1009 X_org_id => l_org_id);
1010 -------------------+
1011 -- End of API body.
1012 -------------------+
1013 -- Standard check of p_commit.
1014 --IF FND_API.To_Boolean( p_commit ) THEN
1015 -- COMMIT WORK;
1016 --END IF;
1017 -- Standard call to get message count and if count is 1, get message info.
1018 FND_MSG_PUB.Count_And_Get
1019 (p_count => x_msg_count ,
1020 p_data => x_msg_data ,
1021 p_encoded => FND_API.G_FALSE);
1022 EXCEPTION
1023 WHEN FND_API.G_EXC_ERROR THEN
1024 ROLLBACK TO Create_Table_Map_Object;
1025 x_return_status := FND_API.G_RET_STS_ERROR ;
1026 FND_MSG_PUB.Count_And_Get
1027 (p_count => x_msg_count,
1028 p_data => x_msg_data,
1029 p_encoded => FND_API.G_FALSE);
1030 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1031 ROLLBACK TO Create_Table_Map_Object;
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1033 FND_MSG_PUB.Count_And_Get
1034 (p_count => x_msg_count,
1035 p_data => x_msg_data,
1036 p_encoded => FND_API.G_FALSE);
1037 WHEN OTHERS THEN
1038 ROLLBACK TO Create_Table_Map_Object;
1039 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1040 IF FND_MSG_PUB.Check_Msg_Level
1041 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1042 FND_MSG_PUB.Add_Exc_Msg
1043 (G_PKG_NAME,
1044 l_api_name );
1045 END IF;
1046 FND_MSG_PUB.Count_And_Get
1047 (p_count => x_msg_count,
1048 p_data => x_msg_data,
1049 p_encoded => FND_API.G_FALSE);
1050 END Create_Table_Map_Object;
1051
1052
1053
1054 ------------------------------------------------------------------------------+
1055 -- Procedure : Delete_Table_Map_Object
1056 ------------------------------------------------------------------------------+
1057 -- WARNING: Use this procedure for deleting objects like Notification
1058 -- Query Parameters. If you only want to delete the CN_TABLE_MAP_OBJECTS
1059 -- references to an object (for example an Extra Collection Table)
1060 -- then just use the cn_table_map_objects_pkg.delete_row procedure.
1061 PROCEDURE Delete_Table_Map_Object (
1062 p_api_version IN NUMBER,
1063 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1064 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1065 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1066 x_return_status OUT NOCOPY VARCHAR2,
1067 x_msg_count OUT NOCOPY NUMBER,
1068 x_msg_data OUT NOCOPY VARCHAR2,
1069 p_table_map_object_id IN NUMBER,
1070 x_org_id IN NUMBER) IS -- Added For R12 MOAC
1071
1072 l_api_name CONSTANT VARCHAR2(30) := 'Create_Map';
1073 l_api_version CONSTANT NUMBER := 1.0;
1074 l_object_id NUMBER;
1075
1076 CURSOR del_tbmp IS
1077 SELECT object_id
1078 FROM cn_table_map_objects
1079 WHERE table_map_object_id = p_table_map_object_id
1080 AND org_id = x_org_id;
1081
1082 BEGIN
1083 -- Standard Start of API savepoint
1084 SAVEPOINT Delete_Table_Map_Object;
1085 -- Standard call to check for call compatibility.
1086 IF NOT FND_API.Compatible_API_Call (l_api_version,
1087 p_api_version,
1088 l_api_name,
1089 G_PKG_NAME)
1090 THEN
1091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1092 END IF;
1093 -- Initialize message list if p_init_msg_list is set to TRUE.
1094 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1095 FND_MSG_PUB.initialize;
1096 END IF;
1097 -- Initialize API return status to success
1098 x_return_status := FND_API.G_RET_STS_SUCCESS;
1099 -------------------+
1100 -- API body
1101 -------------------+
1102 --+
1103 -- Delete the object in CN_OBJECTS
1104 --+
1105 FOR del IN del_tbmp
1106 LOOP
1107 DELETE FROM cn_objects WHERE object_id = del.object_id;
1108 END LOOP;
1109 --+
1110 -- Delete the reference to the object in CN_TABLE_MAP_OBJECTS
1111 --+
1112 cn_table_map_objects_pkg.delete_row(
1113 x_table_map_object_id => p_table_map_object_id,
1114 x_org_id => x_org_id); -- Added For R12 MOAC
1115 -------------------+
1116 -- End of API body.
1117 -------------------+
1118 -- Standard check of p_commit.
1119 --IF FND_API.To_Boolean( p_commit ) THEN
1120 -- COMMIT WORK;
1121 --END IF;
1122 -- Standard call to get message count and if count is 1, get message info.
1123 FND_MSG_PUB.Count_And_Get
1124 (p_count => x_msg_count ,
1125 p_data => x_msg_data ,
1126 p_encoded => FND_API.G_FALSE);
1127 EXCEPTION
1128 WHEN FND_API.G_EXC_ERROR THEN
1129 ROLLBACK TO Delete_Table_Map_Object;
1130 x_return_status := FND_API.G_RET_STS_ERROR ;
1131 FND_MSG_PUB.Count_And_Get
1132 (p_count => x_msg_count,
1133 p_data => x_msg_data,
1134 p_encoded => FND_API.G_FALSE);
1135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1136 ROLLBACK TO Delete_Table_Map_Object;
1137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1138 FND_MSG_PUB.Count_And_Get
1139 (p_count => x_msg_count,
1140 p_data => x_msg_data,
1141 p_encoded => FND_API.G_FALSE);
1142 WHEN OTHERS THEN
1143 ROLLBACK TO Delete_Table_Map_Object;
1144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1145 IF FND_MSG_PUB.Check_Msg_Level
1146 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1147 FND_MSG_PUB.Add_Exc_Msg
1148 (G_PKG_NAME,
1149 l_api_name );
1150 END IF;
1151 FND_MSG_PUB.Count_And_Get
1152 (p_count => x_msg_count,
1153 p_data => x_msg_data,
1154 p_encoded => FND_API.G_FALSE);
1155 END Delete_Table_Map_Object;
1156
1157 ------------------------------------------------------------------------------+
1158 -- Procedure : Get_SQL_Clauses
1159 ------------------------------------------------------------------------------+
1160 PROCEDURE Get_SQL_Clauses
1161 ( p_api_version IN NUMBER,
1162 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1163 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1164 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1165 x_return_status OUT NOCOPY VARCHAR2,
1166 x_msg_count OUT NOCOPY NUMBER,
1167 x_msg_data OUT NOCOPY VARCHAR2,
1168 p_table_map_id IN NUMBER ,
1169 x_notify_from OUT NOCOPY VARCHAR2,
1170 x_notify_where OUT NOCOPY VARCHAR2,
1171 x_collect_from OUT NOCOPY VARCHAR2,
1172 x_collect_where OUT NOCOPY VARCHAR2,
1173 p_org_id IN NUMBER
1174 ) IS
1175 l_api_name CONSTANT VARCHAR2(30) := 'GET_SQL_CLAUSES';
1176 l_api_version CONSTANT NUMBER := 1.0;
1177 -- Cursor to get all necessary info about header and line tables
1178 CURSOR c1 IS
1179 SELECT
1180 tmv.table_map_id,
1181 LOWER(tmv.source_table_name) line_tab_name,
1182 LOWER(NVL(tmv.source_table_alias,tmv.source_table_name)) line_tab_alias,
1183 LOWER(tmv.linepk_name) line_pk_col,
1184 LOWER(tmv.linefk_name) line_fk_col,
1185 LOWER(tmv.header_table_name) hdr_tab_name,
1186 LOWER(NVL(tmv.header_table_alias,tmv.header_table_name)) hdr_tab_alias,
1187 LOWER(tmv.hdrpk_name) hdr_pk_col
1188 FROM
1189 cn_table_maps_v tmv
1190 WHERE
1191 tmv.table_map_id = p_table_map_id
1192 AND tmv.org_id = p_org_id;
1193 l_c1_rec c1%ROWTYPE;
1194
1195 BEGIN
1196 -- Standard Start of API savepoint
1197 SAVEPOINT Get_SQL_Clauses;
1198 -- Standard call to check for call compatibility.
1199 IF NOT FND_API.Compatible_API_Call (l_api_version,
1200 p_api_version,
1201 l_api_name,
1202 G_PKG_NAME)
1203 THEN
1204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1205 END IF;
1206 -- Initialize message list if p_init_msg_list is set to TRUE.
1207 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1208 FND_MSG_PUB.initialize;
1209 END IF;
1210 -- Initialize API return status to success
1211 x_return_status := FND_API.G_RET_STS_SUCCESS;
1212 -------------------+
1213 -- API body
1214 -------------------+
1215 --+
1216 -- Get the information from CN_TABLE_MAPS about the Line table and how
1217 -- to join it to the Header table (if there is one)
1218 --+
1219 OPEN c1;
1220 FETCH c1 into l_c1_rec;
1221 CLOSE c1;
1222 --+
1223 -- FIRST we derive the NOTIFY FROM and WHERE clauses
1224 --+
1225 --+
1226 -- By default, the Notify_From clause only contains the Line table
1227 -- and the Notify_Where clause is empty.
1228 --+
1229 x_notify_from := l_c1_rec.line_tab_name||' '||l_c1_rec.line_tab_alias;
1230 x_notify_where := '1 = 1';
1231 --+
1232 -- If a header table is specified
1233 --+
1234 IF l_c1_rec.hdr_pk_col IS NOT NULL THEN
1235 --+
1236 -- Add the Header Table to the Notify_From table list and add
1237 -- the join to it into the Notify_Where clause
1238 --+
1239 x_notify_from := x_notify_from||','||fnd_global.local_chr(10)||l_c1_rec.hdr_tab_name||
1240 ' '||l_c1_rec.hdr_tab_alias;
1241 x_notify_where := l_c1_rec.hdr_tab_alias||'.'||l_c1_rec.hdr_pk_col||' = '||
1242 l_c1_rec.line_tab_alias||'.'||l_c1_rec.line_fk_col;
1243 END IF;
1244 --+
1245 -- NOW we derive the COLLECT FROM and WHERE clauses
1246 --+
1247 -- The Collect_From table list starts out the same as the list from Notify_Where
1248 --+
1249 x_collect_from := x_notify_from||',';
1250 FOR rec IN
1251 --+
1252 -- Add to the Collect_From list any extra collection tables that have been specified
1253 --+
1254 (SELECT LOWER(obj.name||' '||NVL(obj.alias,obj.name)) name
1255 FROM cn_table_map_objects tmobj,
1256 cn_objects obj
1257 WHERE tmobj.table_map_id = l_c1_rec.table_map_id
1258 AND tmobj.tm_object_type = 'COLLTAB'
1259 AND obj.object_id = tmobj.object_id
1260 AND obj.org_id = p_org_id AND obj.org_id = tmobj.org_id)
1261 LOOP
1262 x_collect_from := x_collect_from||fnd_global.local_chr(10)||rec.name||',';
1263 END LOOP;
1264 --+
1265 -- Terminate the Collect_From list with the CN_NOT_TRX table
1266 --+
1267 x_collect_from := x_collect_from||fnd_global.local_chr(10)||'cn_not_trx cnt';
1268 --+
1269 -- The Collect_Where clause at a minimun joins the primary_key of the Line
1270 -- table to cnt.Source_Trx_Line_Id
1271 --+
1272 x_collect_where := l_c1_rec.line_tab_alias||'.'||l_c1_rec.line_pk_col||
1273 ' = cnt.source_trx_line_id';
1274 --+
1275 -- If there is a header table then the Collect_Where is extended to join
1276 -- the primary_key of the Header table to cnt.Source_Trx_Id
1277 --+
1278 IF l_c1_rec.hdr_pk_col IS NOT NULL THEN
1279 x_collect_where := x_collect_where||fnd_global.local_chr(10)||'AND '||
1280 l_c1_rec.hdr_tab_alias||'.'||l_c1_rec.hdr_pk_col||' = cnt.source_trx_id';
1281 END IF;
1282 -------------------+
1283 -- End of API body.
1284 -------------------+
1285 -- Standard check of p_commit.
1286 IF FND_API.To_Boolean( p_commit ) THEN
1287 COMMIT WORK;
1288 END IF;
1289 -- Standard call to get message count and if count is 1, get message info.
1290 FND_MSG_PUB.Count_And_Get
1291 (p_count => x_msg_count ,
1292 p_data => x_msg_data ,
1293 p_encoded => FND_API.G_FALSE);
1294 EXCEPTION
1295 WHEN FND_API.G_EXC_ERROR THEN
1296 ROLLBACK TO Get_SQL_Clauses;
1297 x_return_status := FND_API.G_RET_STS_ERROR ;
1298 FND_MSG_PUB.Count_And_Get
1299 (p_count => x_msg_count,
1300 p_data => x_msg_data,
1301 p_encoded => FND_API.G_FALSE);
1302 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1303 ROLLBACK TO Get_SQL_Clauses;
1304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1305 FND_MSG_PUB.Count_And_Get
1306 (p_count => x_msg_count,
1307 p_data => x_msg_data,
1308 p_encoded => FND_API.G_FALSE);
1309 WHEN OTHERS THEN
1310 ROLLBACK TO Get_SQL_Clauses;
1311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1312 IF FND_MSG_PUB.Check_Msg_Level
1313 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1314 FND_MSG_PUB.Add_Exc_Msg
1315 (G_PKG_NAME,
1316 l_api_name );
1317 END IF;
1318 FND_MSG_PUB.Count_And_Get
1319 (p_count => x_msg_count,
1320 p_data => x_msg_data,
1321 p_encoded => FND_API.G_FALSE);
1322 END Get_SQL_Clauses;
1323
1324 END cn_table_maps_pvt;
1325