[Home] [Help]
PACKAGE BODY: APPS.CSI_T_TXN_SYSTEMS_PVT
Source
1 PACKAGE BODY csi_t_txn_systems_pvt AS
2 /* $Header: csivtsyb.pls 120.1 2006/02/09 14:54:24 shegde noship $ */
3 -- start of comments
4 -- package name : csi_t_txn_systems_pvt
5 -- purpose :
6 -- history :
7 -- note :
8 -- END of comments
9
10
11 g_pkg_name CONSTANT VARCHAR2(30) := 'csi_t_txn_systems_pvt';
12 g_file_name CONSTANT VARCHAR2(12) := 'csivtsyb.pls';
13
14 PROCEDURE dmsg (
15 p_msg IN VARCHAR2
16 );
17 PROCEDURE validate_txn_systems(
18 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
19 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
20 p_validation_mode IN VARCHAR2 ,
21 p_txn_system_rec IN csi_t_datastructures_grp.txn_system_rec,
22 x_return_status OUT NOCOPY VARCHAR2 ,
23 x_msg_count OUT NOCOPY NUMBER ,
24 x_msg_data OUT NOCOPY VARCHAR2
25 );
26
27 /* ----------------------------------------------------------------------------------------------- */
28 /* This Procedure(during creation/updation)is used to check for Unique system_name for a Customer */
29 /* and System_Number.IF found then raise an error else success */
30 /* ----------------------------------------------------------------------------------------------- */
31 PROCEDURE Check_Unique( p_txn_system_id IN NUMBER ,
32 p_system_name IN VARCHAR2,
33 p_Customer_ID IN NUMBER ,
34 p_System_number IN VARCHAR2,
35 p_txn_line_id IN NUMBER ,
36 p_validation_level IN NUMBER ,
37 x_return_status OUT NOCOPY VARCHAR2,
38 x_msg_count OUT NOCOPY NUMBER ,
39 x_msg_data OUT NOCOPY VARCHAR2) IS
40 /* split out and commented the 2 cursors for perf Bug 4907908
41 CURSOR dup_cur IS
42 SELECT 'x'
43 FROM csi_t_txn_systems txn, csi_systems_vl sys
44 WHERE txn.system_name = p_system_name
45 AND txn.customer_id = p_Customer_ID
46 AND (txn.system_number IS NULL OR
47 txn.system_number = p_System_number)
48 OR sys.name = p_system_name
49 AND sys.customer_id = p_Customer_ID
50 AND (sys.system_number IS NULL OR
51 sys.system_number = p_System_number);
52
53 CURSOR dup_cur1 IS
54 SELECT 'x'
55 FROM csi_t_txn_systems txn, csi_systems_vl sys
56 WHERE txn.system_name = p_system_name
57 AND txn.customer_id = p_Customer_ID
58 AND (txn.system_number IS NULL OR
59 txn.system_number = p_System_number)
60 AND txn.transaction_line_id =p_txn_line_id
61 OR sys.name = p_system_name
62 AND sys.customer_id = p_Customer_ID
63 AND (sys.system_number IS NULL OR
64 sys.system_number = p_System_number);
65 */
66 l_dummy VARCHAR2(1);
67 BEGIN
68 x_return_status := fnd_api.g_ret_sts_success;
69
70 Begin
71 SELECT 'Y'
72 INTO l_dummy
73 FROM csi_systems_vl sys
74 WHERE sys.name = p_system_name
75 AND sys.customer_id = p_Customer_ID
76 AND (sys.system_number IS NULL OR
77 sys.system_number = p_System_number);
78 Exception
79 when no_data_found then
80 null; -- valid system
81 when others then
82 FND_MESSAGE.SET_NAME('CSI', 'CSI_TXN_SYSTEM_DUP_NAME');
83 FND_MSG_PUB.add;
84 x_return_status := fnd_api.g_ret_sts_error;
85 End;
86
87 /* commented for perf Bug 4907908
88 l_dummy:=NULL;
89 OPEN dup_cur;
90 FETCH dup_cur INTO l_dummy;
91 IF (dup_cur%FOUND) THEN
92 FND_MESSAGE.SET_NAME('CSI', 'CSI_TXN_SYSTEM_DUP_NAME');
93 fnd_msg_pub.add;
94 x_return_status := fnd_api.g_ret_sts_error;
95 END IF;
96
97 CLOSE dup_cur;
98 ELSE
99 l_dummy:=NULL;
100 OPEN dup_cur1;
101 FETCH dup_cur1 INTO l_dummy;
102 IF (dup_cur1%FOUND) THEN
103 FND_MESSAGE.SET_NAME('CSI', 'CSI_TXN_SYSTEM_DUP_NAME');
104 fnd_msg_pub.add;
105 x_return_status := fnd_api.g_ret_sts_error;
106 END IF;
107
108 CLOSE dup_cur1;
109 */
110 Begin
111 IF p_validation_level = fnd_api.g_valid_level_full THEN
112 SELECT 'Y'
113 INTO l_dummy
114 FROM csi_t_txn_systems txn
115 WHERE txn.system_name = p_system_name
116 AND txn.customer_id = p_Customer_ID
117 AND (txn.system_number IS NULL OR
118 txn.system_number = p_System_number);
119 ELSE -- validation level <> full
120 SELECT 'Y'
121 INTO l_dummy
122 FROM csi_t_txn_systems txn
123 WHERE txn.system_name = p_system_name
124 AND txn.customer_id = p_Customer_ID
125 AND txn.transaction_line_id =p_txn_line_id
126 AND (txn.system_number IS NULL OR
127 txn.system_number = p_System_number);
128 END IF;
129
130 Exception
131 when no_data_found then
132 null; -- valid system
133 when others then
134 FND_MESSAGE.SET_NAME('CSI', 'CSI_TXN_SYSTEM_DUP_NAME');
135 FND_MSG_PUB.add;
136 x_return_status := fnd_api.g_ret_sts_error;
137 End;
138
139 EXCEPTION
140 WHEN OTHERS THEN
141 NULL;
142 END Check_Unique;
143
144 -- hint: primary key needs to be returned.
145 PROCEDURE create_txn_system(
146 p_api_version IN NUMBER,
147 p_commit IN VARCHAR2 := fnd_api.g_false,
148 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
149 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
150 p_txn_system_rec IN csi_t_datastructures_grp.txn_system_rec,
151 x_txn_system_id OUT NOCOPY NUMBER,
152 x_return_status OUT NOCOPY VARCHAR2,
153 x_msg_count OUT NOCOPY NUMBER,
154 x_msg_data OUT NOCOPY VARCHAR2
155 )
156
157 IS
158 l_api_name CONSTANT VARCHAR2(30) := 'create_txn_system';
159 l_api_version_number CONSTANT NUMBER := 1.0;
160 l_txn_system_id NUMBER;
161 l_system_history_id NUMBER :=fnd_api.g_miss_num;
162 l_debug_level NUMBER;
163 l_system_rec csi_datastructures_pub.system_rec;
164 l_start_date DATE;
165 BEGIN
166 -- standard start of api savepoint
167 SAVEPOINT create_txn_system_pvt;
168
169 -- standard call to check for call compatibility.
170 IF NOT fnd_api.compatible_api_call ( l_api_version_number,
171 p_api_version,
172 l_api_name,
173 g_pkg_name)
174 THEN
175 RAISE fnd_api.g_exc_unexpected_error;
176 END IF;
177
178
179 -- initialize message list IF p_init_msg_list IS set to true.
180 IF fnd_api.to_boolean( p_init_msg_list )
181 THEN
182 fnd_msg_pub.initialize;
183 END IF;
184
185
186 -- debug message
187
188
189 -- initialize api return status to success
190 x_return_status := fnd_api.g_ret_sts_success;
191
192 --debug messages
193 csi_t_gen_utility_pvt.dump_api_info(
194 p_pkg_name => g_pkg_name,
195 p_api_name => l_api_name);
196
197 IF csi_t_gen_utility_pvt.g_debug_level > 1 then
198
199
200 csi_t_gen_utility_pvt.add(p_api_version ||'-'||
201 p_commit ||'-'||
202 p_init_msg_list ||'-'||
203 p_validation_level);
204
205 csi_t_gen_utility_pvt.dump_txn_systems_rec(
206 p_txn_systems_rec => p_txn_system_rec);
207
208 END IF;
209
210 validate_txn_systems( p_init_msg_list => p_init_msg_list
211 ,p_validation_level => p_validation_level
212 ,p_validation_mode => 'CREATE'
213 ,p_txn_system_rec => p_txn_system_rec
214 ,x_return_status => x_return_status
215 ,x_msg_count => x_msg_count
216 ,x_msg_data => x_msg_data
217 );
218
219 -- invoke validation procedures
220 l_system_rec.customer_id := p_txn_system_rec.customer_id;
221 l_system_rec.system_type_code := p_txn_system_rec.system_type_code;
222 l_system_rec.ship_to_contact_id := p_txn_system_rec.ship_to_contact_id;
223 l_system_rec.bill_to_contact_id := p_txn_system_rec.bill_to_contact_id;
224 l_system_rec.technical_contact_id := p_txn_system_rec.technical_contact_id;
225 l_system_rec.service_admin_contact_id := p_txn_system_rec.service_admin_contact_id;
226 l_system_rec.ship_to_site_use_id := p_txn_system_rec.ship_to_site_use_id;
227 l_system_rec.bill_to_site_use_id := p_txn_system_rec.bill_to_site_use_id;
228 l_system_rec.install_site_use_id := p_txn_system_rec.install_site_use_id;
229 l_system_rec.name := p_txn_system_rec.system_name;
230
231 csi_systems_pvt.validate_systems(
232 p_init_msg_list => p_init_msg_list,
233 p_validation_level => p_validation_level,
234 p_validation_mode => 'CREATE',
235 p_system_rec => l_system_rec,
236 x_return_status => x_return_status,
237 x_msg_count => x_msg_count,
238 x_msg_data => x_msg_data);
239
240
241 IF x_return_status<>fnd_api.g_ret_sts_success THEN
242 RAISE fnd_api.g_exc_error;
243 END IF;
244
245 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
246
247
248 -- check for unique system name
249 Check_Unique(
250 p_txn_system_id => NULL
251 ,p_system_name => p_txn_system_rec.system_name
252 ,p_Customer_ID => p_txn_system_rec.customer_id
253 ,p_System_number => p_txn_system_rec.system_number
254 ,p_txn_line_id => p_txn_system_rec.transaction_line_id
255 ,p_validation_level => p_validation_level
256 ,x_return_status => x_return_status
257 ,x_msg_count => x_msg_count
258 ,x_msg_data => x_msg_data);
259
260 --dbms_output.put_line('Value of x_return_status='||x_return_status);
261 END IF;
262
263 IF ( (p_txn_system_rec.start_date_active = fnd_api.g_miss_date)
264 OR (p_txn_system_rec.start_date_active IS NULL) )
265 THEN
266 l_start_date := SYSDATE;
267 ELSE l_start_date := p_txn_system_rec.start_date_active;
268 END IF;
269
270 IF x_return_status = fnd_api.g_ret_sts_success THEN
271
272 csi_t_txn_systems_pkg.insert_row(
273 px_transaction_system_id => x_txn_system_id,
274 p_transaction_line_id => p_txn_system_rec.transaction_line_id,
275 p_system_name => p_txn_system_rec.system_name,
276 p_description => p_txn_system_rec.description,
277 p_system_type_code => p_txn_system_rec.system_type_code,
278 p_system_number => p_txn_system_rec.system_number,
279 p_customer_id => p_txn_system_rec.customer_id,
280 p_bill_to_contact_id => p_txn_system_rec.bill_to_contact_id,
281 p_ship_to_contact_id => p_txn_system_rec.ship_to_contact_id,
282 p_technical_contact_id => p_txn_system_rec.technical_contact_id,
283 p_service_admin_contact_id => p_txn_system_rec.service_admin_contact_id,
284 p_ship_to_site_use_id => p_txn_system_rec.ship_to_site_use_id,
285 p_bill_to_site_use_id => p_txn_system_rec.bill_to_site_use_id,
286 p_install_site_use_id => p_txn_system_rec.install_site_use_id,
287 p_coterminate_day_month => p_txn_system_rec.coterminate_day_month,
288 p_config_system_type => p_txn_system_rec.config_system_type,
289 p_start_date_active => l_start_date,
290 p_end_date_active => p_txn_system_rec.end_date_active,
291 p_context => p_txn_system_rec.context,
292 p_attribute1 => p_txn_system_rec.attribute1,
293 p_attribute2 => p_txn_system_rec.attribute2,
294 p_attribute3 => p_txn_system_rec.attribute3,
295 p_attribute4 => p_txn_system_rec.attribute4,
296 p_attribute5 => p_txn_system_rec.attribute5,
297 p_attribute6 => p_txn_system_rec.attribute6,
298 p_attribute7 => p_txn_system_rec.attribute7,
299 p_attribute8 => p_txn_system_rec.attribute8,
300 p_attribute9 => p_txn_system_rec.attribute9,
301 p_attribute10 => p_txn_system_rec.attribute10,
302 p_attribute11 => p_txn_system_rec.attribute11,
303 p_attribute12 => p_txn_system_rec.attribute12,
304 p_attribute13 => p_txn_system_rec.attribute13,
305 p_attribute14 => p_txn_system_rec.attribute14,
306 p_attribute15 => p_txn_system_rec.attribute15,
307 p_created_by => fnd_global.user_id,
308 p_creation_date => SYSDATE,
309 p_last_updated_by => fnd_global.user_id,
310 p_last_update_date => SYSDATE,
311 p_last_update_login => fnd_global.conc_login_id,
312 p_object_version_number => 1
313 );
314 l_txn_system_id := x_txn_system_id;
315
316
317 END IF;
318
319 IF x_return_status <> fnd_api.g_ret_sts_success THEN
320 RAISE fnd_api.g_exc_error;
321 END IF;
322
323 --
324 -- END of api body
325 --
326
327 -- standard check FOR p_commit
328 IF fnd_api.to_boolean( p_commit )
329 THEN
330 COMMIT WORK;
331 END IF;
332
333
334
335
336 -- standard call to get message count AND IF count IS 1, get message info.
337 fnd_msg_pub.count_and_get
338 ( p_count => x_msg_count,
339 p_data => x_msg_data
340 );
341
342 EXCEPTION
343 WHEN fnd_api.g_exc_error THEN
344 ROLLBACK TO create_txn_system_pvt;
345 x_return_status := fnd_api.g_ret_sts_error ;
346 fnd_msg_pub.count_and_get
347 (p_count => x_msg_count ,
348 p_data => x_msg_data
349 );
350
351 WHEN fnd_api.g_exc_unexpected_error THEN
352 ROLLBACK TO create_txn_system_pvt;
353 x_return_status := fnd_api.g_ret_sts_unexp_error ;
354 fnd_msg_pub.count_and_get
355 (p_count => x_msg_count ,
356 p_data => x_msg_data
357 );
358
359 WHEN OTHERS THEN
360 ROLLBACK TO create_txn_system_pvt;
361 x_return_status := fnd_api.g_ret_sts_unexp_error ;
362 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
363 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
364 END IF;
365 fnd_msg_pub.count_and_get
366 (p_count => x_msg_count ,
367 p_data => x_msg_data
368 );
369
370 END create_txn_system;
371
372
373
374 PROCEDURE update_txn_system(
375 p_api_version IN NUMBER,
376 p_commit IN VARCHAR2 := fnd_api.g_false,
377 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
378 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
379 p_txn_system_rec IN csi_t_datastructures_grp.txn_system_rec,
380 x_return_status OUT NOCOPY VARCHAR2,
381 x_msg_count OUT NOCOPY NUMBER,
382 x_msg_data OUT NOCOPY VARCHAR2
383 )
384 IS
385 CURSOR txn_sys_csr (sys_id NUMBER) IS
386 SELECT customer_id
387 ,object_version_number
388 ,start_date_active
389 ,end_date_active
390 FROM csi_t_txn_systems
391 WHERE transaction_system_id=sys_id
392 FOR UPDATE NOWAIT;
393
394 l_api_name CONSTANT VARCHAR2(30) := 'update_txn_system';
395 l_sys_csr txn_sys_csr%ROWTYPE;
396 l_api_version_number CONSTANT NUMBER := 1.0;
397 l_rowid rowid;
398 l_object_version_number NUMBER;
399 l_count NUMBER;
400 l_full_dump NUMBER;
401 l_debug_level NUMBER;
402 l_customer_id NUMBER;
403 l_system_rec csi_datastructures_pub.system_rec;
404
405
406 BEGIN
407 SAVEPOINT update_txn_system_pvt;
408
409 IF NOT fnd_api.compatible_api_call ( l_api_version_number,
410 p_api_version,
411 l_api_name,
412 g_pkg_name)
413 THEN
414 RAISE fnd_api.g_exc_unexpected_error;
415 END IF;
416
417 IF fnd_api.to_boolean( p_init_msg_list )
418 THEN
419 fnd_msg_pub.initialize;
420 END IF;
421
422 x_return_status := fnd_api.g_ret_sts_success;
423
424 --debug messages
425 csi_t_gen_utility_pvt.dump_api_info(
426 p_pkg_name => g_pkg_name,
427 p_api_name => l_api_name);
428
429 IF csi_t_gen_utility_pvt.g_debug_level > 1 then
430
431 csi_t_gen_utility_pvt.add(p_api_version ||'-'||
432 p_commit ||'-'||
433 p_init_msg_list ||'-'||
434 p_validation_level);
435
436 csi_t_gen_utility_pvt.dump_txn_systems_rec(
437 p_txn_systems_rec => p_txn_system_rec);
438
439 END IF;
440
441
442
443 OPEN txn_sys_csr (p_txn_system_rec.transaction_system_id);
444 FETCH txn_sys_csr INTO l_sys_csr;
445 IF ( (l_sys_csr.object_version_number<>p_txn_system_rec.object_version_number)
446 AND (p_txn_system_rec.object_version_number <> fnd_api.g_miss_num) ) THEN
447 fnd_message.set_name('CSI', 'CSI_RECORD_CHANGED');
448 fnd_msg_pub.add;
449 RAISE fnd_api.g_exc_error;
450 END IF;
451 CLOSE txn_sys_csr;
452
453 -- validation for Active Start Date
454 IF p_txn_system_rec.start_date_active <> fnd_api.g_miss_date THEN
455 IF p_txn_system_rec.start_date_active <> l_sys_csr.start_date_active THEN
456 fnd_message.set_name('CSI', 'CSI_UPD_NOT_ALLOWED');
457 fnd_message.set_token('start_date_active',p_txn_system_rec.start_date_active);
458 fnd_msg_pub.add;
459 RAISE fnd_api.g_exc_error;
460 END IF;
461 END IF;
462
463 -- validating the effective active end date
464 IF l_sys_csr.end_date_active <= SYSDATE THEN
465 IF (p_txn_system_rec.end_date_active = fnd_api.g_miss_date) OR
466 (p_txn_system_rec.end_date_active <= SYSDATE) THEN
467 fnd_message.set_name('CSI', 'CSI_CANT_UPDATE_EXPIRED_SYS');
468 fnd_message.set_token('start_date_active',p_txn_system_rec.end_date_active);
469 fnd_msg_pub.add;
470 RAISE fnd_api.g_exc_error;
471 END IF;
472 END IF;
473
474
475
476
477 validate_txn_systems( p_init_msg_list => p_init_msg_list
478 ,p_validation_level => p_validation_level
479 ,p_validation_mode => 'UPDATE'
480 ,p_txn_system_rec => p_txn_system_rec
481 ,x_return_status => x_return_status
482 ,x_msg_count => x_msg_count
483 ,x_msg_data => x_msg_data
484 );
485
486 l_system_rec.customer_id := p_txn_system_rec.customer_id;
487 l_system_rec.system_type_code := p_txn_system_rec.system_type_code;
488 l_system_rec.ship_to_contact_id := p_txn_system_rec.ship_to_contact_id;
489 l_system_rec.bill_to_contact_id := p_txn_system_rec.bill_to_contact_id;
490 l_system_rec.technical_contact_id := p_txn_system_rec.technical_contact_id;
491 l_system_rec.service_admin_contact_id := p_txn_system_rec.service_admin_contact_id;
492 l_system_rec.ship_to_site_use_id := p_txn_system_rec.ship_to_site_use_id;
493 l_system_rec.bill_to_site_use_id := p_txn_system_rec.bill_to_site_use_id;
494 l_system_rec.install_site_use_id := p_txn_system_rec.install_site_use_id;
495 l_system_rec.name := p_txn_system_rec.system_name;
496
497 csi_systems_pvt.validate_systems(
498 p_init_msg_list => p_init_msg_list,
499 p_validation_level => p_validation_level,
500 p_validation_mode => 'UPDATE',
501 p_system_rec => l_system_rec,
502 x_return_status => x_return_status,
503 x_msg_count => x_msg_count,
504 x_msg_data => x_msg_data);
505
506 IF x_return_status<>fnd_api.g_ret_sts_success THEN
507 RAISE fnd_api.g_exc_error;
508 END IF;
509
510 IF ( (p_txn_system_rec.customer_id IS NOT NULL) AND (p_txn_system_rec.customer_id<>fnd_api.g_miss_num) ) THEN
511 l_customer_id:=p_txn_system_rec.customer_id;
512 ELSE
513 l_customer_id:=l_sys_csr.customer_id;
514 END IF;
515
516
517 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
518 -- check for unique system name
519 Check_Unique(
520 p_txn_system_id => p_txn_system_rec.transaction_system_id
521 ,p_system_name => p_txn_system_rec.system_name
522 ,p_Customer_ID => l_customer_id
523 ,p_System_number => p_txn_system_rec.system_number
524 ,p_txn_line_id => p_txn_system_rec.transaction_line_id
525 ,p_validation_level => p_validation_level
526 ,x_return_status => x_return_status
527 ,x_msg_count => x_msg_count
528 ,x_msg_data => x_msg_data);
529
530 --dbms_output.put_line('Value of x_return_status='||x_return_status);
531 END IF;
532
533 IF x_return_status = fnd_api.g_ret_sts_success THEN
534
535
536 csi_t_txn_systems_pkg.update_row(
537 p_transaction_system_id => p_txn_system_rec.transaction_system_id,
538 p_transaction_line_id => p_txn_system_rec.transaction_line_id,
539 p_system_name => p_txn_system_rec.system_name,
540 p_description => p_txn_system_rec.description,
541 p_system_type_code => p_txn_system_rec.system_type_code,
542 p_system_number => p_txn_system_rec.system_number,
543 p_customer_id => p_txn_system_rec.customer_id,
544 p_bill_to_contact_id => p_txn_system_rec.bill_to_contact_id,
545 p_ship_to_contact_id => p_txn_system_rec.ship_to_contact_id,
546 p_technical_contact_id => p_txn_system_rec.technical_contact_id,
547 p_service_admin_contact_id => p_txn_system_rec.service_admin_contact_id,
548 p_ship_to_site_use_id => p_txn_system_rec.ship_to_site_use_id,
549 p_bill_to_site_use_id => p_txn_system_rec.bill_to_site_use_id,
550 p_install_site_use_id => p_txn_system_rec.install_site_use_id,
551 p_coterminate_day_month => p_txn_system_rec.coterminate_day_month,
552 p_config_system_type => p_txn_system_rec.config_system_type,
553 p_start_date_active => p_txn_system_rec.start_date_active,
554 p_end_date_active => p_txn_system_rec.end_date_active,
555 p_context => p_txn_system_rec.context,
556 p_attribute1 => p_txn_system_rec.attribute1,
557 p_attribute2 => p_txn_system_rec.attribute2,
558 p_attribute3 => p_txn_system_rec.attribute3,
559 p_attribute4 => p_txn_system_rec.attribute4,
560 p_attribute5 => p_txn_system_rec.attribute5,
561 p_attribute6 => p_txn_system_rec.attribute6,
562 p_attribute7 => p_txn_system_rec.attribute7,
563 p_attribute8 => p_txn_system_rec.attribute8,
564 p_attribute9 => p_txn_system_rec.attribute9,
565 p_attribute10 => p_txn_system_rec.attribute10,
566 p_attribute11 => p_txn_system_rec.attribute11,
567 p_attribute12 => p_txn_system_rec.attribute12,
568 p_attribute13 => p_txn_system_rec.attribute13,
569 p_attribute14 => p_txn_system_rec.attribute14,
570 p_attribute15 => p_txn_system_rec.attribute15,
571 p_created_by => fnd_global.user_id,
572 p_creation_date => SYSDATE,
573 p_last_updated_by => fnd_global.user_id,
574 p_last_update_date => SYSDATE,
575 p_last_update_login => fnd_global.conc_login_id,
576 p_object_version_number => p_txn_system_rec.object_version_number
577 );
578
579
580 END IF;
581
582 IF x_return_status <> fnd_api.g_ret_sts_success THEN
583 RAISE fnd_api.g_exc_error;
584 END IF;
585 IF fnd_api.to_boolean( p_commit )
586 THEN
587 COMMIT WORK;
588 END IF;
589
590 fnd_msg_pub.count_and_get
591 ( p_count => x_msg_count,
592 p_data => x_msg_data
593 );
594
595 EXCEPTION
596 WHEN fnd_api.g_exc_error THEN
597 ROLLBACK TO update_txn_system_pvt;
598 x_return_status := fnd_api.g_ret_sts_error ;
599 fnd_msg_pub.count_and_get
600 (p_count => x_msg_count ,
601 p_data => x_msg_data
602 );
603
604 WHEN fnd_api.g_exc_unexpected_error THEN
605 ROLLBACK TO update_txn_system_pvt;
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 );
611
612 WHEN OTHERS THEN
613 ROLLBACK TO update_txn_system_pvt;
614 x_return_status := fnd_api.g_ret_sts_unexp_error ;
615 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
616 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
617 END IF;
618 fnd_msg_pub.count_and_get
619 (p_count => x_msg_count ,
620 p_data => x_msg_data
621 );
622
623 END update_txn_system;
624
625 PROCEDURE delete_txn_system
626 (
627 p_api_version IN NUMBER,
628 p_commit IN VARCHAR2 := fnd_api.g_false,
629 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
630 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
631 p_txn_system_id IN NUMBER,
632 x_return_status OUT NOCOPY VARCHAR2,
633 x_msg_count OUT NOCOPY NUMBER,
634 x_msg_data OUT NOCOPY VARCHAR2
635 ) IS
636 l_api_version_number CONSTANT NUMBER := 1.0;
637 l_api_name CONSTANT VARCHAR2(30) := 'delete_txn_system';
638 l_debug_level NUMBER;
639 l_dummy VARCHAR2(1);
640 l_txn_system_rec csi_t_datastructures_grp.txn_system_rec;
641
642 BEGIN
643
644 SAVEPOINT delete_txn_system_pvt;
645
646 IF NOT fnd_api.compatible_api_call ( l_api_version_number,
647 p_api_version,
648 l_api_name,
649 g_pkg_name)
650 THEN
651 RAISE fnd_api.g_exc_unexpected_error;
652 END IF;
653
654
655 IF fnd_api.to_boolean( p_init_msg_list )
656 THEN
657 fnd_msg_pub.initialize;
658 END IF;
659
660 x_return_status := fnd_api.g_ret_sts_success;
661
662 --debug messages
663 csi_t_gen_utility_pvt.dump_api_info(
664 p_pkg_name => g_pkg_name,
665 p_api_name => l_api_name);
666
667 IF csi_t_gen_utility_pvt.g_debug_level > 1 then
668
669
670 csi_t_gen_utility_pvt.add(p_api_version ||'-'||
671 p_commit ||'-'||
672 p_init_msg_list ||'-'||
673 p_validation_level ||'-'||
674 p_txn_system_id);
675
676 END IF;
677 l_txn_system_rec.transaction_system_id:=p_txn_system_id;
678 validate_txn_systems( p_init_msg_list => p_init_msg_list
679 ,p_validation_level => p_validation_level
680 ,p_validation_mode => 'DELETE'
681 ,p_txn_system_rec => l_txn_system_rec
682 ,x_return_status => x_return_status
683 ,x_msg_count => x_msg_count
684 ,x_msg_data => x_msg_data
685 );
686
687
688 IF x_return_status<>fnd_api.g_ret_sts_success THEN
689 RAISE fnd_api.g_exc_error;
690 END IF;
691
692
693 csi_t_txn_systems_pkg.Delete_Row(p_txn_system_id );
694
695
696 IF x_return_status <> fnd_api.g_ret_sts_success THEN
697 RAISE fnd_api.g_exc_error;
698 END IF;
699 IF fnd_api.to_boolean( p_commit )
700 THEN
701 COMMIT WORK;
702 END IF;
703
704 fnd_msg_pub.count_and_get
705 ( p_count => x_msg_count,
706 p_data => x_msg_data
707 );
708
709 EXCEPTION
710 WHEN fnd_api.g_exc_error THEN
711 ROLLBACK TO delete_txn_system_pvt;
712 x_return_status := fnd_api.g_ret_sts_error ;
713 fnd_msg_pub.count_and_get
714 (p_count => x_msg_count ,
715 p_data => x_msg_data
716 );
717
718 WHEN fnd_api.g_exc_unexpected_error THEN
719 ROLLBACK TO delete_txn_system_pvt;
720 x_return_status := fnd_api.g_ret_sts_unexp_error ;
721 fnd_msg_pub.count_and_get
722 (p_count => x_msg_count ,
723 p_data => x_msg_data
724 );
725
726 WHEN OTHERS THEN
727 ROLLBACK TO delete_txn_system_pvt;
728 x_return_status := fnd_api.g_ret_sts_unexp_error ;
729 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
730 fnd_msg_pub.add_exc_msg(g_pkg_name ,l_api_name);
731 END IF;
732 fnd_msg_pub.count_and_get
733 (p_count => x_msg_count ,
734 p_data => x_msg_data
735 );
736 END delete_txn_system;
737
738
739 PROCEDURE validate_txn_system_id (
740 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
741 p_validation_mode IN VARCHAR2,
742 p_txn_system_id IN NUMBER,
743 x_return_status OUT NOCOPY VARCHAR2,
744 x_msg_count OUT NOCOPY NUMBER,
745 x_msg_data OUT NOCOPY VARCHAR2
746 )
747 IS
748 l_dummy VARCHAR2(1);
749 BEGIN
750
751 IF fnd_api.to_boolean( p_init_msg_list )
752 THEN
753 fnd_msg_pub.initialize;
754 END IF;
755 x_return_status := fnd_api.g_ret_sts_success;
756
757 IF p_validation_mode='CREATE' THEN
758 IF ( (p_txn_system_id IS NOT NULL) AND (p_txn_system_id<>fnd_api.g_miss_num) ) THEN
759 BEGIN
760 SELECT 'x'
761 INTO l_dummy
762 FROM csi_t_txn_systems
763 WHERE transaction_system_id=p_txn_system_id;
764 EXCEPTION
765 WHEN no_data_found THEN
766 fnd_message.set_name('CSI', 'CSI_INVALID_TXN_SYSTEM_ID');
767 fnd_message.set_token('transaction_system_id',p_txn_system_id);
768 fnd_msg_pub.add;
769 x_return_status := fnd_api.g_ret_sts_error;
770 END;
771 END IF;
772
773 ELSIF p_validation_mode='UPDATE' OR p_validation_mode='DELETE' THEN
774 IF ( (p_txn_system_id IS NOT NULL) AND (p_txn_system_id<>fnd_api.g_miss_num) ) THEN
775 BEGIN
776 SELECT 'x'
777 INTO l_dummy
778 FROM csi_t_txn_systems
779 WHERE transaction_system_id=p_txn_system_id;
780 EXCEPTION
781 WHEN no_data_found THEN
782 fnd_message.set_name('CSI', 'CSI_INVALID_TXN_SYSTEM_ID');
783 fnd_message.set_token('transaction_system_id',p_txn_system_id);
784 fnd_msg_pub.add;
785 x_return_status := fnd_api.g_ret_sts_error;
786 END;
787 ELSE
788 fnd_message.set_name('CSI', 'CSI_NO_TXN_SYSTEM_ID');
789 fnd_msg_pub.add;
790 x_return_status := fnd_api.g_ret_sts_error;
791 END IF;
792
793 END IF;
794
795 fnd_msg_pub.count_and_get
796 ( p_count => x_msg_count,
797 p_data => x_msg_data
798 );
799
800 END validate_txn_system_id;
801
802 PROCEDURE validate_object_version_num (
803 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
804 p_validation_mode IN VARCHAR2,
805 p_object_version_number IN NUMBER,
806 x_return_status OUT NOCOPY VARCHAR2,
807 x_msg_count OUT NOCOPY NUMBER,
808 x_msg_data OUT NOCOPY VARCHAR2
809 )
810 IS
811 l_dummy VARCHAR2(1);
812 BEGIN
813 IF fnd_api.to_boolean( p_init_msg_list )
814 THEN
815 fnd_msg_pub.initialize;
816 END IF;
817 x_return_status := fnd_api.g_ret_sts_success;
818
819 IF(p_validation_mode = 'UPDATE') THEN
820 IF ( (p_object_version_number IS NULL) OR (p_object_version_number = fnd_api.g_miss_num) ) THEN
821 fnd_message.set_name('CSI', 'CSI_MISSING_OBJ_VER_NUM');
822 fnd_msg_pub.add;
823 x_return_status := fnd_api.g_ret_sts_error;
824 END IF;
825 END IF;
826
827 fnd_msg_pub.count_and_get
828 ( p_count => x_msg_count,
829 p_data => x_msg_data
830 );
831
832 END validate_object_version_num;
833
834 PROCEDURE validate_start_date (
835 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
836 p_validation_mode IN VARCHAR2,
837 p_start_date IN DATE ,
838 p_end_date IN DATE ,
839 x_return_status OUT NOCOPY VARCHAR2,
840 x_msg_count OUT NOCOPY NUMBER ,
841 x_msg_data OUT NOCOPY VARCHAR2
842 )
843 IS
844 l_dummy VARCHAR2(1);
845 l_start_date DATE;
846 l_end_date DATE;
847 BEGIN
848 IF fnd_api.to_boolean( p_init_msg_list )
849 THEN
850 fnd_msg_pub.initialize;
851 END IF;
852 x_return_status := fnd_api.g_ret_sts_success;
853 IF p_validation_mode='CREATE' THEN
854 IF ((p_start_date = FND_API.G_MISS_DATE) OR (p_start_date IS NULL)) THEN
855 l_start_date := SYSDATE;
856 ELSE l_start_date := p_start_date;
857 END IF;
858
859 IF (p_end_date = FND_API.G_MISS_DATE) THEN
860 l_end_date := NULL;
861 ELSE l_end_date := p_end_date;
862 END IF;
863
864 IF (l_end_date IS NOT NULL) THEN
865 IF (l_start_date > l_end_date) THEN
866 fnd_message.set_name('CSI','CSI_API_INVALID_START_DATE');
867 fnd_message.set_token('START_DATE_ACTIVE',l_start_date);
868 fnd_msg_pub.Add;
869 x_return_status := fnd_api.g_ret_sts_error;
870 END IF;
871 END IF;
872 END IF;
873 fnd_msg_pub.count_and_get
874 ( p_count => x_msg_count,
875 p_data => x_msg_data
876 );
877
878 END validate_start_date;
879
880 PROCEDURE validate_end_date (
881 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
882 p_validation_mode IN VARCHAR2,
883 p_start_date IN DATE ,
884 p_end_date IN DATE ,
885 x_return_status OUT NOCOPY VARCHAR2,
886 x_msg_count OUT NOCOPY NUMBER ,
887 x_msg_data OUT NOCOPY VARCHAR2
888 )
889 IS
890 l_dummy VARCHAR2(1);
891 l_start_date DATE;
892 l_end_date DATE;
893 BEGIN
894 IF fnd_api.to_boolean( p_init_msg_list )
895 THEN
896 fnd_msg_pub.initialize;
897 END IF;
898 x_return_status := fnd_api.g_ret_sts_success;
899 IF ( (p_validation_mode='CREATE') OR (p_validation_mode='UPDATE') ) THEN
900 IF (p_end_date = FND_API.G_MISS_DATE) THEN
901 l_end_date := NULL;
902 ELSE l_end_date := p_end_date;
903 END IF;
904
905 IF (l_end_date IS NOT NULL) THEN
906 IF l_end_date < SYSDATE THEN
907 fnd_message.set_name('CSI','CSI_API_INVALID_END_DATE');
908 fnd_message.set_token('END_DATE_ACTIVE',l_end_date);
909 fnd_msg_pub.Add;
910 x_return_status := fnd_api.g_ret_sts_error;
911 END IF;
912 END IF;
913 END IF;
914 fnd_msg_pub.count_and_get
915 ( p_count => x_msg_count,
916 p_data => x_msg_data
917 );
918
919 END validate_end_date;
920
921
922
923
924 PROCEDURE dmsg (
925 p_msg IN VARCHAR2
926 ) IS
927 BEGIN
928 --dbms_output.put_line( p_msg );
929 null;
930 END;
931
932
933
934
935 PROCEDURE validate_txn_systems(
936 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
937 p_validation_level IN NUMBER := fnd_api.g_valid_level_full,
938 p_validation_mode IN VARCHAR2,
939 p_txn_system_rec IN csi_t_datastructures_grp.txn_system_rec,
940 x_return_status OUT NOCOPY VARCHAR2,
941 x_msg_count OUT NOCOPY NUMBER,
942 x_msg_data OUT NOCOPY VARCHAR2
943 )
944 IS
945 l_api_name CONSTANT VARCHAR2(30) := 'validate_txn_systems';
946 BEGIN
947
948 x_return_status := fnd_api.g_ret_sts_success;
949
950 IF (p_validation_level >= fnd_api.g_valid_level_full) THEN
951 validate_txn_system_id(
952 p_init_msg_list => fnd_api.g_false,
953 p_validation_mode => p_validation_mode,
954 p_txn_system_id => p_txn_system_rec.transaction_system_id,
955 x_return_status => x_return_status,
956 x_msg_count => x_msg_count,
957 x_msg_data => x_msg_data);
958 IF x_return_status <> fnd_api.g_ret_sts_success THEN
959 RAISE fnd_api.g_exc_error;
960 END IF;
961
962 END IF;
963
964 IF (p_validation_level >= fnd_api.g_valid_level_full) THEN
965 validate_object_version_num(
966 p_init_msg_list => fnd_api.g_false,
967 p_validation_mode => p_validation_mode,
968 p_object_version_number => p_txn_system_rec.object_version_number,
969 x_return_status => x_return_status,
970 x_msg_count => x_msg_count,
971 x_msg_data => x_msg_data);
972 IF x_return_status <> fnd_api.g_ret_sts_success THEN
973 RAISE fnd_api.g_exc_error;
974 END IF;
975
976 END IF;
977
978 IF (p_validation_level >= fnd_api.g_valid_level_full) THEN
979 validate_start_date(
980 p_init_msg_list => fnd_api.g_false,
981 p_validation_mode => p_validation_mode,
982 p_start_date => p_txn_system_rec.start_date_active,
983 p_end_date => p_txn_system_rec.end_date_active,
984 x_return_status => x_return_status,
985 x_msg_count => x_msg_count,
986 x_msg_data => x_msg_data);
987 IF x_return_status <> fnd_api.g_ret_sts_success THEN
988 RAISE fnd_api.g_exc_error;
989 END IF;
990
991 END IF;
992
993 IF (p_validation_level >= fnd_api.g_valid_level_full) THEN
994 validate_end_date(
995 p_init_msg_list => fnd_api.g_false,
996 p_validation_mode => p_validation_mode,
997 p_start_date => p_txn_system_rec.start_date_active,
998 p_end_date => p_txn_system_rec.end_date_active,
999 x_return_status => x_return_status,
1000 x_msg_count => x_msg_count,
1001 x_msg_data => x_msg_data);
1002 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1003 RAISE fnd_api.g_exc_error;
1004 END IF;
1005
1006 END IF;
1007 END validate_txn_systems;
1008
1009 END csi_t_txn_systems_pvt;