[Home] [Help]
PACKAGE BODY: APPS.JTF_TASK_OBJECT_MIGRATION_PUB
Source
1 PACKAGE BODY JTF_TASK_OBJECT_MIGRATION_PUB AS
2 /* $Header: jtfptkjb.pls 115.0 2003/04/08 23:42:10 cjang noship $ */
3
4 PROCEDURE update_object_name(p_object_code IN VARCHAR2
5 ,p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false
6 ,p_commit IN VARCHAR2 DEFAULT fnd_api.g_false
7 ,x_msg_count OUT NOCOPY NUMBER
8 ,x_msg_data OUT NOCOPY VARCHAR2
9 ,x_return_status OUT NOCOPY VARCHAR2)
10 IS
11 CURSOR c_object IS
12 SELECT select_id, select_name, from_table, where_clause
13 FROM jtf_objects_b
14 WHERE object_code = p_object_code;
15
16 l_id_column jtf_objects_b.select_id%TYPE;
17 l_name_column jtf_objects_b.select_name%TYPE;
18 l_from_clause jtf_objects_b.from_table%TYPE;
19 l_where_clause jtf_objects_b.where_clause%TYPE;
20
21 l_stmt VARCHAR2(1000);
22
23 BEGIN
24 SAVEPOINT update_object_name_pub;
25 x_return_status := fnd_api.g_ret_sts_success;
26
27 IF fnd_api.to_boolean (p_init_msg_list)
28 THEN
29 fnd_msg_pub.initialize;
30 END IF;
31
32 OPEN c_object;
33 FETCH c_object
34 INTO l_id_column
35 , l_name_column
36 , l_from_clause
37 , l_where_clause;
38
39 IF c_object%NOTFOUND
40 THEN
41 fnd_message.set_name ('JTF', 'JTF_TASK_OBJECT_NOT_FOUND');
42 fnd_message.set_token ('OBJECT_NAME', p_object_code);
43 fnd_msg_pub.add;
44 RAISE fnd_api.g_exc_unexpected_error;
45 END IF;
46
47 IF l_where_clause IS NOT NULL THEN
48 l_where_clause := l_where_clause || ' AND ';
49 ELSE
50 l_where_clause := l_where_clause || ' ';
51 END IF;
52
53 l_stmt := 'UPDATE jtf_tasks_b '||
54 ' SET source_object_name = (SELECT SUBSTRB('||l_name_column||',1,80)'||
55 ' FROM '||l_from_clause||
56 ' WHERE '||l_where_clause||
57 l_id_column||' = source_object_id) '||
58 ' WHERE source_object_type_code = '''||p_object_code||''''||
59 ' AND NVL(deleted_flag,''N'') = ''N''';
60 EXECUTE IMMEDIATE l_stmt;
61
62 l_stmt := 'UPDATE jtf_task_references_b '||
63 ' SET object_name = (SELECT SUBSTRB('||l_name_column||',1,80)'||
64 ' FROM '||l_from_clause||
65 ' WHERE '||l_where_clause||
66 l_id_column||' = object_id) '||
67 ' WHERE object_type_code = '''||p_object_code||'''';
68 EXECUTE IMMEDIATE l_stmt;
69
70 IF fnd_api.to_boolean (p_commit)
71 THEN
72 COMMIT WORK;
73 END IF;
74
75 EXCEPTION
76 WHEN fnd_api.g_exc_unexpected_error THEN
77 ROLLBACK TO update_object_name_pub;
78 x_return_status := fnd_api.g_ret_sts_unexp_error;
79 IF c_object%ISOPEN
80 THEN
81 CLOSE c_object;
82 END IF;
83 fnd_msg_pub.count_and_get (
84 p_count => x_msg_count
85 ,p_data => x_msg_data
86 );
87 WHEN OTHERS THEN
88 ROLLBACK TO update_object_name_pub;
89 x_return_status := fnd_api.g_ret_sts_unexp_error;
90 IF c_object%ISOPEN
91 THEN
92 CLOSE c_object;
93 END IF;
94 fnd_message.set_name ('JTF', 'JTF_TASK_UNKNOWN_ERROR');
95 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
96 fnd_msg_pub.add;
97 fnd_msg_pub.count_and_get (
98 p_count => x_msg_count
99 ,p_data => x_msg_data
100 );
101 END update_object_name;
102
103 END JTF_TASK_OBJECT_MIGRATION_PUB;