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 ,
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 ,
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
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;
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
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: */
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
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
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,
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,
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,
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
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.
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:
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,
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
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:
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(
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;
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,
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: --
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
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:
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 ,
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: );
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 ,
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: );
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
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,
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,
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
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
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
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:
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,
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;
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
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;
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
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;
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:
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
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;
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;
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
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,
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
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;
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:
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 ,
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: );
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 ,
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: );
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
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,
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,
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
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
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 )
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:
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,
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:
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 );
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
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;
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:
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 ,
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: );
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 ,
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: );
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
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,
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;
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
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
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
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
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;
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:
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,
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;
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');
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;
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
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,
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;
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;
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:
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:
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
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,
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;
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;
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:
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
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,
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,
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,
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,
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,
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;
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:
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,
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,
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;
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:
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,
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,
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;
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:
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,
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,
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;
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;