[Home] [Help]
PACKAGE BODY: APPS.JTA_SYNC_TASK_CATEGORY
Source
1 PACKAGE BODY jta_sync_task_category AS
2 /* $Header: jtavstyb.pls 115.9 2002/12/13 22:28:29 cjang ship $ */
3 /*======================================================================+
4 | Copyright (c) 2002 Oracle Corporation Redwood Shores, California, USA|
5 | All rights reserved. |
6 +=======================================================================+
7 | FILENAME |
8 | jtavstyb.pls |
9 | |
10 | DESCRIPTION |
11 | This package body is for task categories. |
12 | |
13 | NOTES |
14 | |
15 | |
16 | Date Developer Change |
17 | ------ --------------- -------------------------------------- |
18 | 12-Mar-2002 arpatel created |
19 | 16-Apr-2002 sanjeev changed create_category() |
20 | for fixing bugs |
21 | 24-Jun-2002 cjang Fix bug 2418798 : |
22 | Added truncate_category_name() |
23 | Modified create_category() |
24 | get_category_id() to truncate |
25 | category name if it's greater than 40. |
26 | 04-Oct-2002 cjang Fix bug 2540722 : |
27 | Increase the length of category name |
28 | to 240 bytes |
29 | in truncate_category_name() |
30 | and create_category() |
31 *=======================================================================*/
32
33 FUNCTION truncate_category_name(p_category_name IN VARCHAR2)
34 RETURN VARCHAR2
35 IS
36 l_category_name VARCHAR2(240); -- Fix bug 2540722
37 BEGIN
38 IF LENGTHB(p_category_name) > 240 -- Fix bug 2540722
39 THEN
40 l_category_name := SUBSTRB(p_category_name, 1, 237)||'...'; -- -- Fix bug 2540722
41 ELSE
42 l_category_name := p_category_name;
43 END IF;
44
45 RETURN l_category_name;
46 END truncate_category_name;
47
48 FUNCTION get_category_id (
49 p_category_name IN VARCHAR2,
50 p_profile_id IN NUMBER
51 ) RETURN NUMBER
52 IS
53 CURSOR c_category_id (b_category_name VARCHAR2) IS -- Fix bug 2418798
54 SELECT perz_data_id
55 FROM JTF_PERZ_DATA
56 WHERE perz_data_desc = b_category_name
57 AND profile_id = p_profile_id;
58
59 l_category_id NUMBER;
60 BEGIN
61 OPEN c_category_id (truncate_category_name(p_category_name)); -- Fix bug 2418798
62 FETCH c_category_id INTO l_category_id;
63 CLOSE c_category_id;
64
65 RETURN l_category_id;
66 END get_category_id;
67
68 PROCEDURE create_category (
69 p_category_name IN OUT NOCOPY VARCHAR2,
70 p_resource_id IN NUMBER
71 )
72 IS
73 CURSOR c_categories (b_profile_id NUMBER) IS
74 SELECT perz_data_id
75 FROM JTF_PERZ_DATA
76 WHERE profile_id = b_profile_id;
77
78 l_category_id NUMBER;
79 l_category_name VARCHAR2(240) := truncate_category_name(p_category_name); --Fix bug 2418798, Fix bug 2540722
80 l_profile_id NUMBER;
81 l_return_status VARCHAR2(30);
82 l_msg_count NUMBER;
83 l_msg_data VARCHAR2(30);
84 category_seq NUMBER;
85 l_perz_data_id NUMBER;
86 l_unfiled VARCHAR2(240); -- Fix bug 2540722
87 l_comma_location NUMBER;
88
89 BEGIN
90 SELECT jtf_task_utl.get_category_name(NULL)
91 INTO l_unfiled
92 FROM DUAL;
93
94 IF l_category_name IS NOT NULL AND
95 l_category_name <> l_unfiled
96 THEN
97 l_profile_id := get_profile_id ( p_resource_id => p_resource_id );
98 l_comma_location := instr(l_category_name, ',');
99
100 IF (l_comma_location > 0) THEN
101 l_category_name := substr(l_category_name, 1, l_comma_location-1);
102 END IF;
103
104 -- get the category_id of the category name if it exists for this profile
105 l_category_id := get_category_id (p_category_name => l_category_name,
106 p_profile_id => l_profile_id );
107
108 --Dont create duplicate categories
109 IF l_category_id IS NULL
110 THEN
111 SELECT jtf_task_category_s.nextval
112 INTO category_seq
113 FROM sys.dual;
114
115 jtf_perz_data_pub.create_perz_data
116 (p_api_version_number => 1.0,
117 p_application_id => 690,
118 p_profile_id => l_profile_id,
119 p_profile_name => NULL,
120 p_perz_data_id => l_category_id,
121 p_perz_data_name => 'JTF_TASK_CATEGORY:' || category_seq,
122 p_perz_data_type => 'JTF_TASK_CATEGORY',
123 p_perz_data_desc => l_category_name,
124 x_perz_data_id => l_perz_data_id,
125 x_return_status => l_return_status,
126 x_msg_count => l_msg_count,
127 x_msg_data => l_msg_data
128 );
129 END IF;
130 END IF;
131 END create_category;
132
133 FUNCTION get_profile_id (
134 p_resource_id IN NUMBER
135 ) RETURN NUMBER
136 IS
137 CURSOR c_profile_id IS
138 SELECT profile_id
139 FROM jtf_perz_profile
140 WHERE profile_name = p_resource_id || ':JTF_TASK';
141
142 l_profile_id NUMBER;
143 l_profile_name VARCHAR2(100);
144 l_return_status VARCHAR2(30);
145 l_msg_count NUMBER;
146 l_msg_data VARCHAR2(30);
147 BEGIN
148
149 IF p_resource_id IS NOT NULL THEN
150
151 OPEN c_profile_id;
152 FETCH c_profile_id INTO l_profile_id;
153 CLOSE c_profile_id;
154
155 IF l_profile_id IS NULL THEN
156 --create new profile
157 jtf_perz_profile_pub.Create_Profile
158 ( p_api_version_number => 1.0,
159 p_profile_id => NULL,
160 p_profile_name => p_resource_id || ':JTF_TASK',
161 x_profile_name => l_profile_name,
162 x_profile_id => l_profile_id,
163 x_return_status => l_return_status,
164 x_msg_count => l_msg_count,
165 x_msg_data => l_msg_data
166 );
167
168 --check return status
169 IF l_return_status = fnd_api.g_ret_sts_error
170 THEN
171 RAISE fnd_api.g_exc_error;
172 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error
173 THEN
174 RAISE fnd_api.g_exc_unexpected_error;
175 END IF;
176
177 END IF;
178 ELSE
179 fnd_message.set_name('JTF', 'JTA_SYNC_APPL_ERROR');
180 fnd_msg_pub.add;
181
182 fnd_message.set_name('JTF', 'JTA_SYNC_NULL_RESOURCE_ID');
183 fnd_message.set_token('PROC_NAME','JTA_SYNC_TASK_CATEGORY.GET_PROFILE_ID');
184 fnd_msg_pub.add;
185
186 raise_application_error (-20100,jta_sync_common.get_messages);
187 END IF;
188
189 RETURN l_profile_id;
190 END get_profile_id;
191
192 END jta_sync_task_category; -- Package spec