[Home] [Help]
PACKAGE BODY: APPS.CSI_SYSTEMS_PUB
Source
1 PACKAGE BODY CSI_SYSTEMS_PUB AS
2 /* $Header: csipsysb.pls 120.4 2011/02/04 20:05:16 lakmohan ship $ */
3 -- Start of Comments
4 -- Package name : CSI_SYSTEMS_PUB
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- END of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSI_SYSTEMS_PUB';
12 G_FILE_NAME CONSTANT VARCHAR2(12) := 'csipsysb.pls';
13
14
15
16 PROCEDURE get_systems
17 (
18 p_api_version IN NUMBER,
19 p_commit IN VARCHAR2,
20 p_init_msg_list IN VARCHAR2,
21 p_validation_level IN NUMBER,
22 p_system_query_rec IN csi_datastructures_pub.system_query_rec,
23 p_time_stamp IN DATE,
24 p_active_systems_only IN VARCHAR2,
25 x_systems_tbl OUT NOCOPY csi_datastructures_pub.systems_tbl,
26 x_return_status OUT NOCOPY VARCHAR2,
27 x_msg_count OUT NOCOPY NUMBER,
28 x_msg_data OUT NOCOPY VARCHAR2
29 )
30 is
31 l_api_name CONSTANT VARCHAR2(30) := 'get_systems';
32 l_api_version CONSTANT NUMBER := 1.0;
33 l_return_status_full VARCHAR2(1);
34 l_access_flag VARCHAR2(1);
35 i NUMBER := 1;
36 l_flag VARCHAR2(1) :='N';
37 l_debug_level NUMBER;
38
39 BEGIN
40 -- standard start of api savepoint
41 --SAVEPOINT get_systems_pub;
42
43 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
44
45 csi_utility_grp.check_ib_active;
46
47
48 -- standard call to check for call compatibility.
49 IF NOT fnd_api.compatible_api_call ( l_api_version,
50 p_api_version,
51 l_api_name,
52 g_pkg_name)
53 THEN
54 RAISE fnd_api.g_exc_unexpected_error;
55 END IF;
56
57
58 -- initialize message list if p_init_msg_list is set to true.
59 IF fnd_api.to_boolean( p_init_msg_list )
60 THEN
61 fnd_msg_pub.initialize;
62 END IF;
63
64
65
66 -- initialize api return status to success
67 x_return_status := fnd_api.g_ret_sts_success;
68
69 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
70 -- if debug_level = 1 then dump the procedure name
71 IF (l_debug_level > 0) THEN
72 CSI_gen_utility_pvt.put_line( 'get_systems');
73 END IF;
74
75 -- IF the debug level = 2 THEN dump all the parameters values.
76 IF (l_debug_level > 1) THEN
77 CSI_gen_utility_pvt.put_line(
78 p_api_version ||'-'||
79 p_commit ||'-'||
80 p_init_msg_list ||'-'||
81 p_validation_level ||'-'||
82 p_time_stamp );
83
84 -- dump the system query records
85 csi_gen_utility_pvt.dump_sys_query_rec(p_system_query_rec);
86 END IF;
87
88 /***** srramakr commented for bug # 3304439
89 -- check for the profile option and enable trace
90 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
91 -- end enable trace
92 ****/
93
94 --
95 -- api body
96 --
97 -- debug message
98
99 csi_systems_pvt.get_systems(
100 p_api_version => p_api_version,
101 p_commit => fnd_api.g_false,
102 p_init_msg_list => p_init_msg_list,
103 p_validation_level => p_validation_level,
104 p_system_query_rec => p_system_query_rec,
105 p_time_stamp => p_time_stamp,
106 p_active_systems_only => p_active_systems_only,
107 x_systems_tbl => x_systems_tbl,
108 x_return_status => x_return_status,
109 x_msg_count => x_msg_count,
110 x_msg_data => x_msg_data
111 );
112
113
114
115
116 -- check return status from the above procedure call
117 IF x_return_status = fnd_api.g_ret_sts_error THEN
118 RAISE fnd_api.g_exc_error;
119 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
120 RAISE fnd_api.g_exc_unexpected_error;
121 END IF;
122
123 --
124 -- end of api body
125 --
126 /***** srramakr commented for bug # 3304439
127 IF (l_flag = 'Y') THEN
128 dbms_session.set_sql_trace(false);
129 END IF;
130 ****/
131
132 -- standard call to get message count and if count is 1, get message info.
133 fnd_msg_pub.count_and_get
134 ( p_count => x_msg_count,
135 p_data => x_msg_data
136 );
137
138 EXCEPTION
139 WHEN fnd_api.g_exc_error THEN
140 -- ROLLBACK TO get_systems_pub;
141 x_return_status := fnd_api.g_ret_sts_error ;
142 fnd_msg_pub.count_and_get
143 (p_count => x_msg_count ,
144 p_data => x_msg_data
145 );
146
147 WHEN fnd_api.g_exc_unexpected_error THEN
148 -- ROLLBACK TO get_systems_pub;
149 x_return_status := fnd_api.g_ret_sts_unexp_error ;
150 fnd_msg_pub.count_and_get
151 (p_count => x_msg_count ,
152 p_data => x_msg_data
153 );
154
155 WHEN OTHERS THEN
156 -- ROLLBACK TO get_systems_pub;
157 x_return_status := fnd_api.g_ret_sts_unexp_error ;
158 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
159 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
160 END IF;
161 fnd_msg_pub.count_and_get
162 (p_count => x_msg_count ,
163 p_data => x_msg_data
164 );
165 END get_systems;
166
167
168 PROCEDURE create_system(
169 p_api_version IN NUMBER,
170 p_commit IN VARCHAR2,
171 p_init_msg_list IN VARCHAR2,
172 p_validation_level IN NUMBER,
173 p_system_rec IN csi_datastructures_pub.system_rec,
174 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
175 x_system_id OUT NOCOPY NUMBER,
176 x_return_status OUT NOCOPY VARCHAR2,
177 x_msg_count OUT NOCOPY NUMBER,
178 x_msg_data OUT NOCOPY VARCHAR2
179 )
180
181 is
182 l_api_name CONSTANT VARCHAR2(30) := 'create_system';
183 l_api_version CONSTANT NUMBER := 1.0;
184 l_return_status_full VARCHAR2(1);
185 l_access_flag VARCHAR2(1);
186 l_line_count NUMBER;
187 l_flag VARCHAR2(1) :='N';
188 l_debug_level NUMBER;
189
190
191 BEGIN
192 -- standard start of api savepoint
193 SAVEPOINT create_system_pub;
194
195 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
196
197 csi_utility_grp.check_ib_active;
198
199
200 -- standard call to check for call compatibility.
201 IF NOT fnd_api.compatible_api_call ( l_api_version,
202 p_api_version,
203 l_api_name,
204 g_pkg_name)
205 THEN
206 RAISE fnd_api.g_exc_unexpected_error;
207 END IF;
208
209
210 -- initialize message list if p_init_msg_list is set to true.
211 IF fnd_api.to_boolean( p_init_msg_list )
212 THEN
213 fnd_msg_pub.initialize;
214 END IF;
215
216
217
218 -- initialize api return status to success
219 x_return_status := fnd_api.g_ret_sts_success;
220
221 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
222 -- if debug_level = 1 then dump the procedure name
223 IF (l_debug_level > 0) THEN
224 CSI_gen_utility_pvt.put_line( 'create_system');
225 END IF;
226
227 -- if the debug level = 2 then dump all the parameters values.
228 IF (l_debug_level > 1) THEN
229
230
231 CSI_gen_utility_pvt.put_line(
232 p_api_version ||'-'||
233 p_commit ||'-'||
234 p_init_msg_list ||'-'||
235 p_validation_level );
236
237 -- dump the systems query records
238 csi_gen_utility_pvt.dump_sys_rec(p_system_rec);
239 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
240
241 END IF;
242
243 /***** srramakr commented for bug # 3304439
244 -- check for the profile option and enable trace
245 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
246 -- END enable trace
247 ****/
248
249 -- calling private package: create_system
250 -- hint: primary key needs to be returned
251
252
253 csi_systems_pvt.create_system(
254 p_api_version =>p_api_version,
255 p_commit =>fnd_api.g_false,
256 p_init_msg_list =>p_init_msg_list,
257 p_validation_level =>p_validation_level,
258 p_system_rec =>p_system_rec,
259 p_txn_rec =>p_txn_rec,
260 x_system_id =>x_system_id,
261 x_return_status =>x_return_status,
262 x_msg_count =>x_msg_count,
263 x_msg_data =>x_msg_data
264 );
265
266
267
268 -- check return status from the above procedure call
269 IF x_return_status = fnd_api.g_ret_sts_error THEN
270 RAISE fnd_api.g_exc_error;
271 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
272 RAISE fnd_api.g_exc_unexpected_error;
273 END IF;
274
275 --
276 -- end of api body.
277 --
278
279 -- standard check for p_commit
280 IF fnd_api.to_boolean( p_commit )
281 THEN
282 COMMIT WORK;
283 END IF;
284
285 /***** srramakr commented for bug # 3304439
286 IF (l_flag = 'Y') THEN
287 dbms_session.set_sql_trace(false);
288 END IF;
289 ****/
290
291 -- standard call to get message count and if count is 1, get message info.
292 fnd_msg_pub.count_and_get
293 ( p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296
297 EXCEPTION
298 WHEN fnd_api.g_exc_error THEN
299 ROLLBACK TO create_system_pub;
300 x_return_status := fnd_api.g_ret_sts_error ;
301 fnd_msg_pub.count_and_get
302 (p_count => x_msg_count ,
303 p_data => x_msg_data
304 );
305
306 WHEN fnd_api.g_exc_unexpected_error THEN
307 ROLLBACK TO create_system_pub;
308 x_return_status := fnd_api.g_ret_sts_unexp_error ;
309 fnd_msg_pub.count_and_get
310 (p_count => x_msg_count ,
311 p_data => x_msg_data
312 );
313
314 WHEN OTHERS THEN
315 ROLLBACK TO create_system_pub;
316 x_return_status := fnd_api.g_ret_sts_unexp_error ;
317 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
318 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
319 END IF;
320 fnd_msg_pub.count_and_get
321 (p_count => x_msg_count ,
322 p_data => x_msg_data
323 );
324
325 END create_system;
326
327
328 PROCEDURE update_system(
329 p_api_version IN NUMBER,
330 p_commit IN VARCHAR2,
331 p_init_msg_list IN VARCHAR2,
332 p_validation_level IN NUMBER,
333 p_system_rec IN csi_datastructures_pub.system_rec,
334 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
335 x_return_status OUT NOCOPY VARCHAR2,
336 x_msg_count OUT NOCOPY NUMBER,
337 x_msg_data OUT NOCOPY VARCHAR2
338 )
339 is
340 l_api_name CONSTANT VARCHAR2(30) := 'update_system';
341 l_api_version CONSTANT NUMBER := 1.0;
342 -- local variables
343 l_rowid rowid;
344 l_flag VARCHAR2(1) :='N';
345 l_debug_level NUMBER;
346
347 l_parent_system_id NUMBER;
348 l_child_system_rec csi_datastructures_pub.system_rec;
349
350 -- Bug 6675862
351 CURSOR CHILD_SYSTEM_CUR (p_parent_system_id NUMBER) IS
352 SELECT SYSTEM_ID child_system_id
353 FROM CSI_SYSTEMS_B
354 WHERE PARENT_SYSTEM_ID = p_parent_system_id; -- Bug 10631752
355
356 -- Bug 6675862
357 CURSOR CHILD_SYSTEMS_DETAILS_CUR (p_child_sys_id NUMBER) IS
358 SELECT SYSTEM_ID,
359 SYSTEM_TYPE_CODE,
360 SYSTEM_NUMBER,
361 PARENT_SYSTEM_ID,
362 START_DATE_ACTIVE,
363 END_DATE_ACTIVE,
364 COTERMINATE_DAY_MONTH,
365 AUTOCREATED_FROM_SYSTEM_ID,
366 CONFIG_SYSTEM_TYPE,
367 CONTEXT,
368 ATTRIBUTE1,
369 ATTRIBUTE2,
370 ATTRIBUTE3,
371 ATTRIBUTE4,
372 ATTRIBUTE5,
373 ATTRIBUTE6,
374 ATTRIBUTE7,
375 ATTRIBUTE8,
376 ATTRIBUTE9,
377 ATTRIBUTE10,
378 ATTRIBUTE11,
379 ATTRIBUTE12,
380 ATTRIBUTE13,
381 ATTRIBUTE14,
382 ATTRIBUTE15,
383 OBJECT_VERSION_NUMBER,
384 OPERATING_UNIT_ID
385 FROM CSI_SYSTEMS_B
386 WHERE SYSTEM_ID = p_child_sys_id;
387
388 -- Bug 6675862
389 CURSOR SYS_TL_CSR (p_child_sys_id NUMBER) IS
390 SELECT NAME,
391 DESCRIPTION
392 FROM CSI_SYSTEMS_TL
393 WHERE SYSTEM_ID = p_child_sys_id;
394
395 l_child_sys_details_rec CHILD_SYSTEMS_DETAILS_CUR%ROWTYPE;
396 l_child_sys_tl_rec SYS_TL_CSR%ROWTYPE;
397
398 BEGIN
399 -- standard start of api savepoint
400 SAVEPOINT update_system_pub;
401
402 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
403
404 csi_utility_grp.check_ib_active;
405
406
407 -- standard call to check for call compatibility.
408 IF NOT fnd_api.compatible_api_call ( l_api_version,
409 p_api_version,
410 l_api_name,
411 g_pkg_name)
412 THEN
413 RAISE fnd_api.g_exc_unexpected_error;
414 END IF;
415
416
417 -- initialize message list if p_init_msg_list is set to true.
418 IF fnd_api.to_boolean( p_init_msg_list )
419 THEN
420 fnd_msg_pub.initialize;
421 END IF;
422
423
424 -- initialize api return status to success
425 x_return_status := fnd_api.g_ret_sts_success;
426
427 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
428 -- if debug_level = 1 then dump the procedure name
429 IF (l_debug_level > 0) THEN
430 CSI_gen_utility_pvt.put_line( 'update_system');
431 END IF;
432
433 -- if the debug level = 2 then dump all the parameters values.
434 IF (l_debug_level > 1) THEN
435
436
437 CSI_gen_utility_pvt.put_line(
438 p_api_version ||'-'||
439 p_commit ||'-'||
440 p_init_msg_list ||'-'||
441 p_validation_level );
442
443 -- dump the systems query records
444 csi_gen_utility_pvt.dump_sys_rec(p_system_rec);
445 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
446
447 END IF;
448
449 /***** srramakr commented for bug # 3304439
450 -- check for the profile option and enable trace
451 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
452 -- END enable trace
453 ****/
454
455 csi_systems_pvt.update_system(
456 p_api_version => p_api_version,
457 p_commit => fnd_api.g_false,
458 p_init_msg_list => p_init_msg_list,
459 p_validation_level => p_validation_level,
460 p_system_rec => p_system_rec,
461 p_txn_rec => p_txn_rec,
462 x_return_status => x_return_status,
463 x_msg_count => x_msg_count,
464 x_msg_data => x_msg_data
465 );
466
467
468
469
470 -- check return status from the above procedure call
471 IF x_return_status = fnd_api.g_ret_sts_error THEN
472 RAISE fnd_api.g_exc_error;
473 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
474 RAISE fnd_api.g_exc_unexpected_error;
475 END IF;
476
477 -- Bug 6675862
478 -- Updating Child systems if Cascasde ownership is selected
479
480 -- condition updated for bug 8604665
481 --IF NVL(p_system_rec.CASCADE_CUST_TO_INS_FLAG,'N') = 'Y' THEN
482 IF NVL(p_system_rec.CASCADE_CUST_TO_INS_FLAG,'N') = 'Y'
483 OR NVL(p_system_rec.install_to_site_change_flag,'N') = 'Y'
484 OR NVL(p_system_rec.bill_to_site_change_flag,'N') = 'Y'
485 OR NVL(p_system_rec.ship_to_site_change_flag,'N') = 'Y'
486 OR NVL(p_system_rec.tech_cont_change_flag,'N') = 'Y'
487 OR NVL(p_system_rec.bill_to_cont_change_flag,'N') = 'Y'
488 OR NVL(p_system_rec.ship_to_cont_change_flag,'N') = 'Y'
489 OR NVL(p_system_rec.serv_admin_cont_change_flag,'N') = 'Y'
490 THEN
491
492 -- Retrive list of all child systems for the updated system
493 l_parent_system_id := p_system_rec.system_id;
494
495 FOR child_system_rec in CHILD_SYSTEM_CUR(l_parent_system_id)
496 LOOP
497 BEGIN
498
499 -- Call Update system private with child system id
500 -- l_child_system_rec := p_system_rec;
501 -- Construct the child system cursor
502 OPEN CHILD_SYSTEMS_DETAILS_CUR (child_system_rec.child_system_id);
503 FETCH CHILD_SYSTEMS_DETAILS_CUR INTO l_child_sys_details_rec;
504 CLOSE CHILD_SYSTEMS_DETAILS_CUR;
505
506 OPEN SYS_TL_CSR (child_system_rec.child_system_id);
507 FETCH SYS_TL_CSR INTO l_child_sys_tl_rec;
508 CLOSE SYS_TL_CSR;
509
510 l_child_system_rec.SYSTEM_ID := child_system_rec.child_system_id ;
511 --commented for bug 8604665
512 --l_child_system_rec.CUSTOMER_ID := p_system_rec.CUSTOMER_ID ;
513 l_child_system_rec.SYSTEM_TYPE_CODE := l_child_sys_details_rec.SYSTEM_TYPE_CODE ;
514 l_child_system_rec.SYSTEM_NUMBER := l_child_sys_details_rec.SYSTEM_NUMBER ;
515 l_child_system_rec.PARENT_SYSTEM_ID := l_child_sys_details_rec.PARENT_SYSTEM_ID ;
516 l_child_system_rec.COTERMINATE_DAY_MONTH := l_child_sys_details_rec.COTERMINATE_DAY_MONTH ;
517 l_child_system_rec.AUTOCREATED_FROM_SYSTEM_ID := l_child_sys_details_rec.AUTOCREATED_FROM_SYSTEM_ID ;
518 l_child_system_rec.CONFIG_SYSTEM_TYPE := l_child_sys_details_rec.CONFIG_SYSTEM_TYPE ;
519 l_child_system_rec.START_DATE_ACTIVE := l_child_sys_details_rec.START_DATE_ACTIVE ;
520 l_child_system_rec.END_DATE_ACTIVE := l_child_sys_details_rec.END_DATE_ACTIVE ;
521 l_child_system_rec.CONTEXT := l_child_sys_details_rec.CONTEXT ;
522 l_child_system_rec.ATTRIBUTE1 := l_child_sys_details_rec.ATTRIBUTE1 ;
523 l_child_system_rec.ATTRIBUTE2 := l_child_sys_details_rec.ATTRIBUTE2 ;
524 l_child_system_rec.ATTRIBUTE3 := l_child_sys_details_rec.ATTRIBUTE3 ;
525 l_child_system_rec.ATTRIBUTE4 := l_child_sys_details_rec.ATTRIBUTE4 ;
526 l_child_system_rec.ATTRIBUTE5 := l_child_sys_details_rec.ATTRIBUTE5 ;
527 l_child_system_rec.ATTRIBUTE6 := l_child_sys_details_rec.ATTRIBUTE6 ;
528 l_child_system_rec.ATTRIBUTE7 := l_child_sys_details_rec.ATTRIBUTE7 ;
529 l_child_system_rec.ATTRIBUTE8 := l_child_sys_details_rec.ATTRIBUTE8 ;
530 l_child_system_rec.ATTRIBUTE9 := l_child_sys_details_rec.ATTRIBUTE9 ;
531 l_child_system_rec.ATTRIBUTE10 := l_child_sys_details_rec.ATTRIBUTE10 ;
532 l_child_system_rec.ATTRIBUTE11 := l_child_sys_details_rec.ATTRIBUTE11 ;
533 l_child_system_rec.ATTRIBUTE12 := l_child_sys_details_rec.ATTRIBUTE12 ;
534 l_child_system_rec.ATTRIBUTE13 := l_child_sys_details_rec.ATTRIBUTE13 ;
535 l_child_system_rec.ATTRIBUTE14 := l_child_sys_details_rec.ATTRIBUTE14 ;
536 l_child_system_rec.ATTRIBUTE15 := l_child_sys_details_rec.ATTRIBUTE15 ;
537 l_child_system_rec.OBJECT_VERSION_NUMBER := l_child_sys_details_rec.OBJECT_VERSION_NUMBER ;
538 l_child_system_rec.NAME := l_child_sys_tl_rec.NAME ;
539 l_child_system_rec.DESCRIPTION := l_child_sys_tl_rec.DESCRIPTION ;
540 --
541 l_child_system_rec.OPERATING_UNIT_ID := l_child_sys_details_rec.OPERATING_UNIT_ID ;
542 l_child_system_rec.REQUEST_ID := p_system_rec.REQUEST_ID ;
543 l_child_system_rec.PROGRAM_APPLICATION_ID := p_system_rec.PROGRAM_APPLICATION_ID ;
544 l_child_system_rec.PROGRAM_ID := p_system_rec.PROGRAM_ID ;
545 l_child_system_rec.PROGRAM_UPDATE_DATE := p_system_rec.PROGRAM_UPDATE_DATE ;
546 --
547
548 --bug 8604665 start
549 -- Cascading changes to the child systems
550 IF NVL(p_system_rec.CASCADE_CUST_TO_INS_FLAG,'N') = 'Y' THEN
551 l_child_system_rec.CUSTOMER_ID := p_system_rec.CUSTOMER_ID;
552 ELSE
553 l_child_system_rec.CUSTOMER_ID := FND_API.G_MISS_NUM;
554 END IF;
555
556 IF NVL(p_system_rec.ship_to_site_change_flag,'N') = 'Y' THEN
557 l_child_system_rec.SHIP_TO_SITE_USE_ID := p_system_rec.SHIP_TO_SITE_USE_ID;
558 else
559 l_child_system_rec.SHIP_TO_SITE_USE_ID := FND_API.G_MISS_NUM;
560 END IF;
561
562 IF NVL(p_system_rec.bill_to_site_change_flag,'N') = 'Y' THEN
563 l_child_system_rec.BILL_TO_SITE_USE_ID := p_system_rec.BILL_TO_SITE_USE_ID;
564 else
565 l_child_system_rec.BILL_TO_SITE_USE_ID := FND_API.G_MISS_NUM;
566 END IF;
567
568 IF NVL(p_system_rec.install_to_site_change_flag,'N') = 'Y' then
569 l_child_system_rec.INSTALL_SITE_USE_ID := p_system_rec.INSTALL_SITE_USE_ID;
570 else
571 l_child_system_rec.INSTALL_SITE_USE_ID := FND_API.G_MISS_NUM;
572 end if;
573
574 IF NVL(p_system_rec.tech_cont_change_flag,'N') = 'Y' THEN
575 l_child_system_rec.TECHNICAL_CONTACT_ID := p_system_rec.TECHNICAL_CONTACT_ID;
576
577 ELSE
578 l_child_system_rec.TECHNICAL_CONTACT_ID := FND_API.G_MISS_NUM;
579 END IF;
580
581 IF NVL(p_system_rec.bill_to_cont_change_flag,'N') = 'Y' THEN
582
583 l_child_system_rec.BILL_TO_CONTACT_ID := p_system_rec.BILL_TO_CONTACT_ID;
584 ELSE
585 l_child_system_rec.BILL_TO_CONTACT_ID := FND_API.G_MISS_NUM;
586 END IF;
587
588
589 IF NVL(p_system_rec.ship_to_cont_change_flag,'N') = 'Y' THEN
590 l_child_system_rec.SHIP_TO_CONTACT_ID := p_system_rec.SHIP_TO_CONTACT_ID;
591 ELSE
592 l_child_system_rec.SHIP_TO_CONTACT_ID := FND_API.G_MISS_NUM;
593 END IF;
594
595
596 IF NVL(p_system_rec.serv_admin_cont_change_flag,'N') = 'Y' THEN
597 l_child_system_rec.SERVICE_ADMIN_CONTACT_ID := p_system_rec.SERVICE_ADMIN_CONTACT_ID;
598 ELSE
599 l_child_system_rec.SERVICE_ADMIN_CONTACT_ID := FND_API.G_MISS_NUM;
600 END IF;
601
602 --bug 8604665 end
603
604 -- The Cascade Ownership flag is set so other location details from the
605 -- parent system wont be cascaded
606
607 --commented for bug 8604665
608 /*
609 l_child_system_rec.SHIP_TO_CONTACT_ID := FND_API.G_MISS_NUM;
610 l_child_system_rec.BILL_TO_CONTACT_ID := FND_API.G_MISS_NUM;
611 l_child_system_rec.TECHNICAL_CONTACT_ID := FND_API.G_MISS_NUM;
612 l_child_system_rec.SERVICE_ADMIN_CONTACT_ID := FND_API.G_MISS_NUM;
613 l_child_system_rec.SHIP_TO_SITE_USE_ID := FND_API.G_MISS_NUM;
614 l_child_system_rec.BILL_TO_SITE_USE_ID := FND_API.G_MISS_NUM;
615 l_child_system_rec.INSTALL_SITE_USE_ID := FND_API.G_MISS_NUM;
616 */
617
618 l_child_system_rec.TECH_CONT_CHANGE_FLAG := FND_API.G_MISS_CHAR;
619 l_child_system_rec.BILL_TO_CONT_CHANGE_FLAG := FND_API.G_MISS_CHAR;
620 l_child_system_rec.SHIP_TO_CONT_CHANGE_FLAG := FND_API.G_MISS_CHAR;
621 l_child_system_rec.SERV_ADMIN_CONT_CHANGE_FLAG := FND_API.G_MISS_CHAR;
622 l_child_system_rec.BILL_TO_SITE_CHANGE_FLAG := FND_API.G_MISS_CHAR;
623 l_child_system_rec.SHIP_TO_SITE_CHANGE_FLAG := FND_API.G_MISS_CHAR;
624 l_child_system_rec.INSTALL_TO_SITE_CHANGE_FLAG := FND_API.G_MISS_CHAR;
625 l_child_system_rec.CASCADE_CUST_TO_INS_FLAG := FND_API.G_MISS_CHAR;
626
627 csi_gen_utility_pvt.put_line('Updating Child System ID - ' || l_child_system_rec.system_id);
628 csi_gen_utility_pvt.put_line('Corresponding Parent System ID - ' || l_child_system_rec.parent_system_id);
629
630 csi_systems_pvt.update_system(
631 p_api_version => p_api_version,
632 p_commit => fnd_api.g_false,
633 p_init_msg_list => p_init_msg_list,
634 p_validation_level => p_validation_level,
635 p_system_rec => l_child_system_rec,
636 p_txn_rec => p_txn_rec,
637 x_return_status => x_return_status,
638 x_msg_count => x_msg_count,
639 x_msg_data => x_msg_data
640 );
641
642 EXCEPTION
643 WHEN OTHERS THEN
644 csi_gen_utility_pvt.put_line( 'Into Others Exception in Cascade to Child Systems');
645 csi_gen_utility_pvt.put_line( 'SQLCODE - ' || SQLCODE);
646 csi_gen_utility_pvt.put_line( 'SQLERRM - ' || substr(SQLERRM, 1, 200));
647 END;
648 END LOOP; -- child_system_rec in CHILD_SYSTEM_CUR(p_system_rec.system_id)
649
650 END IF; -- NVL(p_system_rec.CASCADE_CUST_TO_INS_FLAG,'N') = 'Y'
651 -- End Bug 6675862
652
653 --
654 -- end of api body
655 --
656
657 -- standard check for p_commit
658 IF fnd_api.to_boolean( p_commit )
659 THEN
660 COMMIT WORK;
661 END IF;
662
663 /***** srramakr commented for bug # 3304439
664 IF (l_flag = 'Y') THEN
665 dbms_session.set_sql_trace(false);
666 END IF;
667 ****/
668
669 -- standard call to get message count and if count is 1, get message info.
670 fnd_msg_pub.count_and_get
671 ( p_count => x_msg_count,
672 p_data => x_msg_data
673 );
674
675 EXCEPTION
676 WHEN fnd_api.g_exc_error THEN
677 ROLLBACK TO update_system_pub;
678 x_return_status := fnd_api.g_ret_sts_error ;
679 fnd_msg_pub.count_and_get
680 (p_count => x_msg_count ,
681 p_data => x_msg_data
682 );
683
684 WHEN fnd_api.g_exc_unexpected_error THEN
685 ROLLBACK TO update_system_pub;
686 x_return_status := fnd_api.g_ret_sts_unexp_error ;
687 fnd_msg_pub.count_and_get
688 (p_count => x_msg_count ,
689 p_data => x_msg_data
690 );
691
692 WHEN OTHERS THEN
693 ROLLBACK TO update_system_pub;
694 x_return_status := fnd_api.g_ret_sts_unexp_error ;
695 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
696 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
697 END IF;
698 fnd_msg_pub.count_and_get
699 (p_count => x_msg_count ,
700 p_data => x_msg_data
701 );
702 END update_system;
703
704 PROCEDURE expire_system
705 (
706 p_api_version IN NUMBER,
707 p_commit IN VARCHAR2,
708 p_init_msg_list IN VARCHAR2,
709 p_validation_level IN NUMBER,
710 p_system_rec IN csi_datastructures_pub.system_rec,
711 p_txn_rec IN OUT NOCOPY csi_datastructures_pub.transaction_rec,
712 x_instance_id_lst OUT NOCOPY csi_datastructures_pub.id_tbl,
713 x_return_status OUT NOCOPY VARCHAR2,
714 x_msg_count OUT NOCOPY NUMBER,
715 x_msg_data OUT NOCOPY VARCHAR2
716 )
717 is
718 l_api_name CONSTANT VARCHAR2(30) := 'expire_system';
719 l_api_version CONSTANT NUMBER := 1.0;
720 -- local variables
721 l_rowid rowid;
722 l_flag VARCHAR2(1) :='N';
723 l_debug_level NUMBER;
724
725
726 BEGIN
727 -- standard start of api savepoint
728 SAVEPOINT expire_system_pub;
729
730 -- Check for freeze_flag in csi_install_parameters is set to 'Y'
731
732 csi_utility_grp.check_ib_active;
733
734
735 -- standard call to check for call compatibility.
736 IF NOT fnd_api.compatible_api_call ( l_api_version,
737 p_api_version,
738 l_api_name,
739 g_pkg_name)
740 THEN
741 RAISE fnd_api.g_exc_unexpected_error;
742 END IF;
743
744
745 -- initialize message list if p_init_msg_list is set to true.
746 IF fnd_api.to_boolean( p_init_msg_list )
747 THEN
748 fnd_msg_pub.initialize;
749 END IF;
750
751
752 -- initialize api return status to success
753 x_return_status := fnd_api.g_ret_sts_success;
754
755 l_debug_level:=fnd_profile.value('CSI_DEBUG_LEVEL');
756 -- if debug_level = 1 then dump the procedure name
757 IF (l_debug_level > 0) THEN
758 CSI_gen_utility_pvt.put_line( 'update_system');
759 END IF;
760
761 -- if the debug level = 2 then dump all the parameters values.
762 IF (l_debug_level > 1) THEN
763
764
765 CSI_gen_utility_pvt.put_line(
766 p_api_version ||'-'||
767 p_commit ||'-'||
768 p_init_msg_list ||'-'||
769 p_validation_level
770 );
771
772 -- dump the systems query records
773 csi_gen_utility_pvt.dump_sys_rec(p_system_rec);
774 csi_gen_utility_pvt.dump_txn_rec(p_txn_rec);
775
776
777 END IF;
778
779 /***** srramakr commented for bug # 3304439
780 -- check for the profile option and enable trace
781 l_flag:=csi_gen_utility_pvt.enable_trace(l_trace_flag => l_flag);
782 -- end enable trace
783 ****/
784
785 csi_systems_pvt.expire_system(
786 p_api_version => p_api_version,
787 p_commit => fnd_api.g_false,
788 p_init_msg_list => p_init_msg_list,
789 p_validation_level => p_validation_level,
790 p_system_rec => p_system_rec,
791 p_txn_rec => p_txn_rec,
792 x_instance_id_lst => x_instance_id_lst,
793 x_return_status => x_return_status,
794 x_msg_count => x_msg_count,
795 x_msg_data => x_msg_data
796 );
797
798
799
800
801 -- check return status from the above procedure call
802 IF x_return_status = fnd_api.g_ret_sts_error THEN
803 RAISE fnd_api.g_exc_error;
804 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
805 RAISE fnd_api.g_exc_unexpected_error;
806 END IF;
807
808
809
810 --
811 -- end of api body
812 --
813
814 -- standard check for p_commit
815 IF fnd_api.to_boolean( p_commit )
816 THEN
817 COMMIT WORK;
818 END IF;
819
820 /***** srramakr commented for bug # 3304439
821 IF (l_flag = 'Y') THEN
822 dbms_session.set_sql_trace(false);
823 END IF;
824 ****/
825
826 -- standard call to get message count and if count is 1, get message info.
827 fnd_msg_pub.count_and_get
828 ( p_count => x_msg_count,
829 p_data => x_msg_data
830 );
831
832 EXCEPTION
833 WHEN fnd_api.g_exc_error THEN
834 ROLLBACK TO expire_system_pub;
835 x_return_status := fnd_api.g_ret_sts_error ;
836 fnd_msg_pub.count_and_get
837 (p_count => x_msg_count ,
838 p_data => x_msg_data
839 );
840
841 WHEN fnd_api.g_exc_unexpected_error THEN
842 ROLLBACK TO expire_system_pub;
843 x_return_status := fnd_api.g_ret_sts_unexp_error ;
844 fnd_msg_pub.count_and_get
845 (p_count => x_msg_count ,
846 p_data => x_msg_data
847 );
848
849 WHEN OTHERS THEN
850 ROLLBACK TO expire_system_pub;
851 x_return_status := fnd_api.g_ret_sts_unexp_error ;
852 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
853 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
854 END IF;
855 fnd_msg_pub.count_and_get
856 (p_count => x_msg_count ,
857 p_data => x_msg_data
858 );
859
860 END expire_system;
861
862
863
864
865 END csi_systems_pub;