1 PACKAGE BODY IGS_AD_TSTUH_CALL_PKG AS
2 /* $Header: IGSADA6B.pls 115.8 2002/11/28 21:48:03 nsidana ship $ */
3
4 PROCEDURE call_user_hook( p_person_id IN NUMBER,
5 p_session_id OUT NOCOPY NUMBER
6 ) IS
7
8 l_object_name user_objects.object_name%TYPE DEFAULT 'IGS_AD_TST_UH_PKG';
9 INVALID_USER_HOOK EXCEPTION;
10
11 CURSOR chk_uh_stat_cur IS
12 SELECT
13 status
14 FROM
15 user_objects
16 WHERE
17 object_name = 'IGS_AD_TST_UH_PKG' AND
18 object_type = 'PACKAGE BODY';
19
20 chk_uh_stat_rec chk_uh_stat_cur%ROWTYPE;
21
22 CURSOR session_id_cur IS
23 SELECT
24 igs_ad_tstuh_s.NEXTVAL session_id
25 FROM
26 sys.dual;
27
28 session_id_rec session_id_cur%ROWTYPE;
29
30 BEGIN
31 SAVEPOINT beforeuk;
32 --
33 -- Check the status of the user hook package procedure
34 --
35 OPEN chk_uh_stat_cur;
36 FETCH chk_uh_stat_cur INTO chk_uh_stat_rec;
37 CLOSE chk_uh_stat_cur;
38
39 --
40 -- If the status is INVALID then raise appropriate message
41 -- If the status is VALID then call the user hook procedure after generating
42 -- the session ID
43 --
44 IF chk_uh_stat_rec.status = 'INVALID' THEN
45 p_session_id := NULL;
46 RAISE INVALID_USER_HOOK;
47 ELSE
48 --
49 -- Set the savepoint and generate the session id
50 --
51 OPEN session_id_cur;
52 FETCH session_id_cur INTO session_id_rec;
53 CLOSE session_id_cur;
54 p_session_id := session_id_rec.session_id;
55 igs_ad_tst_uh_pkg.convert_test_scr_uk(
56 p_person_id => p_person_id,
57 p_session_id => p_session_id);
58 END IF;
59 EXCEPTION
60 WHEN INVALID_USER_HOOK THEN
61 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UH_INVALID');
62 FND_MESSAGE.SET_TOKEN('NAME',l_object_name);
63 igs_ge_msg_stack.add;
64 APP_EXCEPTION.RAISE_EXCEPTION;
65 WHEN OTHERS THEN
66 p_session_id := NULL;
67 ROLLBACK TO beforeuk;
68 FND_MESSAGE.SET_NAME('IGS','IGS_AD_UH_UNHAND_EXCEPTION');
69 FND_MESSAGE.SET_TOKEN('NAME','igs_ad_tstuh_call_pkg.call_user_hook');
70 igs_ge_msg_stack.add;
71 APP_EXCEPTION.RAISE_EXCEPTION;
72 END call_user_hook;
73 END IGS_AD_TSTUH_CALL_PKG;