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