[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_JOB_ROLES_PVT
Source
1 PACKAGE BODY jtf_rs_job_roles_pvt AS
2 /* $Header: jtfrsvnb.pls 120.0 2005/05/11 08:23:09 appldev ship $ */
3
4 /*****************************************************************************************
5 This private package body defines the procedures for managing resource job roles,
6 like create and delete resource job roles.
7 Its main procedures are as following:
8 Create Resource Job Roles
9 Delete Resource Job Roles
10 These procedures does the business validations and then Calls the corresponding
11 table handlers to do actual inserts and deletes into tables.
12 ******************************************************************************************/
13
14 /* Package variables. */
15
16 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_JOB_ROLES_PVT';
17
18
19 /* Procedure to create the resource job roles
20 based on input values passed by calling routines. */
21
22 PROCEDURE create_resource_job_roles
23 (P_API_VERSION IN NUMBER,
24 P_INIT_MSG_LIST IN VARCHAR2,
25 P_COMMIT IN VARCHAR2,
26 P_JOB_ID IN JTF_RS_JOB_ROLES.JOB_ID%TYPE,
27 P_ROLE_ID IN JTF_RS_JOB_ROLES.ROLE_ID%TYPE,
28 P_ATTRIBUTE1 IN JTF_RS_JOB_ROLES.ATTRIBUTE1%TYPE,
29 P_ATTRIBUTE2 IN JTF_RS_JOB_ROLES.ATTRIBUTE2%TYPE,
30 P_ATTRIBUTE3 IN JTF_RS_JOB_ROLES.ATTRIBUTE3%TYPE,
31 P_ATTRIBUTE4 IN JTF_RS_JOB_ROLES.ATTRIBUTE4%TYPE,
32 P_ATTRIBUTE5 IN JTF_RS_JOB_ROLES.ATTRIBUTE5%TYPE,
33 P_ATTRIBUTE6 IN JTF_RS_JOB_ROLES.ATTRIBUTE6%TYPE,
34 P_ATTRIBUTE7 IN JTF_RS_JOB_ROLES.ATTRIBUTE7%TYPE,
35 P_ATTRIBUTE8 IN JTF_RS_JOB_ROLES.ATTRIBUTE8%TYPE,
36 P_ATTRIBUTE9 IN JTF_RS_JOB_ROLES.ATTRIBUTE9%TYPE,
37 P_ATTRIBUTE10 IN JTF_RS_JOB_ROLES.ATTRIBUTE10%TYPE,
38 P_ATTRIBUTE11 IN JTF_RS_JOB_ROLES.ATTRIBUTE11%TYPE,
39 P_ATTRIBUTE12 IN JTF_RS_JOB_ROLES.ATTRIBUTE12%TYPE,
40 P_ATTRIBUTE13 IN JTF_RS_JOB_ROLES.ATTRIBUTE13%TYPE,
41 P_ATTRIBUTE14 IN JTF_RS_JOB_ROLES.ATTRIBUTE14%TYPE,
42 P_ATTRIBUTE15 IN JTF_RS_JOB_ROLES.ATTRIBUTE15%TYPE,
43 P_ATTRIBUTE_CATEGORY IN JTF_RS_JOB_ROLES.ATTRIBUTE_CATEGORY%TYPE,
44 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
45 X_MSG_COUNT OUT NOCOPY NUMBER,
46 X_MSG_DATA OUT NOCOPY VARCHAR2,
47 X_JOB_ROLE_ID OUT NOCOPY JTF_RS_JOB_ROLES.JOB_ROLE_ID%TYPE
48 ) IS
49
50 l_api_version CONSTANT NUMBER := 1.0;
51 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_JOB_ROLES';
52 l_rowid ROWID;
53 l_job_id jtf_rs_job_roles.job_id%TYPE := p_job_id;
54 l_role_id jtf_rs_job_roles.role_id%type := p_role_id;
55 l_job_role_id jtf_rs_job_roles.job_role_id%TYPE;
56 l_check_char VARCHAR2(1);
57 l_check_count NUMBER;
58
59
60 CURSOR c_jtf_rs_job_roles( l_rowid IN ROWID ) IS
61 SELECT 'Y'
62 FROM jtf_rs_job_roles
63 WHERE ROWID = l_rowid;
64
65
66 BEGIN
67
68
69 SAVEPOINT create_resource_job_role_pvt;
70
71 x_return_status := fnd_api.g_ret_sts_success;
72
73
74 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
75
76 RAISE fnd_api.g_exc_unexpected_error;
77
78 END IF;
79
80
81 IF fnd_api.to_boolean(p_init_msg_list) THEN
82
83 fnd_msg_pub.initialize;
84
85 END IF;
86
87
88 /* Validate that the job is not already existing with the role. */
89
90 l_check_count := 0;
91
92 SELECT count(*)
93 INTO l_check_count
94 FROM jtf_rs_job_roles
95 WHERE job_id = l_job_id
96 AND role_id = l_role_id;
97
98 IF l_check_count > 0 THEN
99
100 x_return_status := fnd_api.g_ret_sts_error;
101
102 fnd_message.set_name('JTF', 'JTF_RS_JOB_EXISTS');
103 fnd_msg_pub.add;
104
105 RAISE fnd_api.g_exc_unexpected_error;
106
107 END IF;
108
109
110 /* Get the next value of the Job_Role_id from the sequence. */
111
112 SELECT jtf_rs_job_roles_s.nextval
113 INTO l_job_role_id
114 FROM dual;
115
116
117 /* Insert the row into the table by calling the table handler. */
118
119 jtf_rs_job_roles_pkg.insert_row(
120 x_rowid => l_rowid,
121 x_job_role_id => l_job_role_id,
122 x_job_id => l_job_id,
123 x_role_id => l_role_id,
124 x_attribute1 => p_attribute1,
125 x_attribute2 => p_attribute2,
126 x_attribute3 => p_attribute3,
127 x_attribute4 => p_attribute4,
128 x_attribute5 => p_attribute5,
129 x_attribute6 => p_attribute6,
130 x_attribute7 => p_attribute7,
131 x_attribute8 => p_attribute8,
132 x_attribute9 => p_attribute9,
133 x_attribute10 => p_attribute10,
134 x_attribute11 => p_attribute11,
135 x_attribute12 => p_attribute12,
136 x_attribute13 => p_attribute13,
137 x_attribute14 => p_attribute14,
138 x_attribute15 => p_attribute15,
139 x_attribute_category => p_attribute_category,
140 x_creation_date => SYSDATE,
141 x_created_by => jtf_resource_utl.created_by,
142 x_last_update_date => SYSDATE,
143 x_last_updated_by => jtf_resource_utl.updated_by,
144 x_last_update_login => jtf_resource_utl.login_id
145 );
146
147
148 OPEN c_jtf_rs_job_roles(l_rowid);
149
150 FETCH c_jtf_rs_job_roles INTO l_check_char;
151
152
153 IF c_jtf_rs_job_roles%NOTFOUND THEN
154
155 x_return_status := fnd_api.g_ret_sts_unexp_error;
156
157 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
158 fnd_msg_pub.add;
159
160 IF c_jtf_rs_job_roles%ISOPEN THEN
161
162 CLOSE c_jtf_rs_job_roles;
163
164 END IF;
165
166 RAISE fnd_api.g_exc_unexpected_error;
167
168 ELSE
169
170 x_job_role_id := l_job_role_id;
171
172 END IF;
173
174
175 /* Close the cursor */
176
177 IF c_jtf_rs_job_roles%ISOPEN THEN
178
179 CLOSE c_jtf_rs_job_roles;
180
181 END IF;
182
183
184 IF fnd_api.to_boolean(p_commit) THEN
185
186 COMMIT WORK;
187
188 END IF;
189
190 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
191
192
193 EXCEPTION
194
195
196 WHEN fnd_api.g_exc_unexpected_error THEN
197
198 ROLLBACK TO create_resource_job_role_pvt;
199
200 x_return_status := fnd_api.g_ret_sts_unexp_error;
201
202 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
203
204
205 WHEN OTHERS THEN
206
207 ROLLBACK TO create_resource_job_role_pvt;
208
209 x_return_status := fnd_api.g_ret_sts_unexp_error;
210
211 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
212
213
214 END create_resource_job_roles;
215
216
217
218 /* Procedure to delete the resource job roles. */
219
220 PROCEDURE delete_resource_job_roles
221 (P_API_VERSION IN NUMBER,
222 P_INIT_MSG_LIST IN VARCHAR2,
223 P_COMMIT IN VARCHAR2,
224 P_JOB_ROLE_ID IN JTF_RS_JOB_ROLES.JOB_ROLE_ID%TYPE,
225 P_OBJECT_VERSION_NUM IN JTF_RS_JOB_ROLES.OBJECT_VERSION_NUMBER%TYPE,
226 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
227 X_MSG_COUNT OUT NOCOPY NUMBER,
228 X_MSG_DATA OUT NOCOPY VARCHAR2
229 )
230
231 IS
232
233 l_api_version CONSTANT NUMBER := 1.0;
234 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_JOB_ROLES';
235 l_job_role_id jtf_rs_job_roles.job_role_id%TYPE := p_job_role_id;
236 l_check_char VARCHAR2(1);
237
238
239 BEGIN
240
241
242 SAVEPOINT delete_resource_job_role_pvt;
243
244 x_return_status := fnd_api.g_ret_sts_success;
245
246
247 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
248
249 RAISE fnd_api.g_exc_unexpected_error;
250
251 END IF;
252
253
254 IF fnd_api.to_boolean(p_init_msg_list) THEN
255
256 fnd_msg_pub.initialize;
257
258 END IF;
259
260
261
262 /* Call the lock row procedure to ensure that the object version number
263 is still valid. */
264
265 BEGIN
266
267 jtf_rs_job_roles_pkg.lock_row(
268 x_job_role_id => l_job_role_id,
269 x_object_version_number => p_object_version_num
270 );
271
272 EXCEPTION
273
274 WHEN OTHERS THEN
275
276 x_return_status := fnd_api.g_ret_sts_unexp_error;
277
278 fnd_message.set_name('JTF', 'JTF_RS_ROW_LOCK_ERROR');
279 fnd_msg_pub.add;
280
281 RAISE fnd_api.g_exc_unexpected_error;
282
283 END;
284
285
286 /* Call the private procedure for logical delete */
287
288 BEGIN
289
290 /* Delete the row into the table by calling the table handler. */
291
292 jtf_rs_job_roles_pkg.delete_row(
293 x_job_role_id => l_job_role_id
294 );
295
296 EXCEPTION
297
298 WHEN NO_DATA_FOUND THEN
299
300 x_return_status := fnd_api.g_ret_sts_unexp_error;
301
302 fnd_message.set_name('JTF', 'JTF_RS_TABLE_HANDLER_ERROR');
303 fnd_msg_pub.add;
304
305 RAISE fnd_api.g_exc_unexpected_error;
306
307 END;
308
309
310 IF fnd_api.to_boolean(p_commit) THEN
311
312 COMMIT WORK;
313
314 END IF;
315
316 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
317
318
319 EXCEPTION
320
321
322 WHEN fnd_api.g_exc_unexpected_error THEN
323
324 ROLLBACK TO delete_resource_job_role_pvt;
325
326 x_return_status := fnd_api.g_ret_sts_unexp_error;
327
328 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
329
330
331 WHEN OTHERS THEN
332
333 ROLLBACK TO delete_resource_job_role_pvt;
334
335 x_return_status := fnd_api.g_ret_sts_unexp_error;
336
337 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
338
339
340 END delete_resource_job_roles;
341
342
343 END jtf_rs_job_roles_pvt;