[Home] [Help]
PACKAGE BODY: APPS.JTF_RS_TEAM_MEMBERS_PUB
Source
1 PACKAGE BODY jtf_rs_team_members_pub AS
2 /* $Header: jtfrspeb.pls 120.0 2005/05/11 08:21:06 appldev ship $ */
3
4 /*****************************************************************************************
5 This package body defines the procedures for managing resource team members, like
6 create, update and delete resource team members.
7 Its main procedures are as following:
8 Create Resource Team Members
9 Update Resource Team Members
10 Delete Resource Team Members
11 This package validates the input parameters to these procedures and then
12 Calls corresponding procedures from jtf_rs_team_members_pvt
13 to do business validations and to do actual inserts, updates and deletes into tables.
14 ******************************************************************************************/
15
16 /* Package variables. */
17
18 G_PKG_NAME VARCHAR2(30) := 'JTF_RS_TEAM_MEMBERS_PUB';
19
20
21 /* Procedure to create the resource team members
22 based on input values passed by calling routines. */
23
24 PROCEDURE create_resource_team_members
25 (P_API_VERSION IN NUMBER,
26 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
27 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
28 P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
29 P_TEAM_NUMBER IN JTF_RS_TEAMS_VL.TEAM_NUMBER%TYPE,
30 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
31 P_TEAM_RESOURCE_NUMBER IN NUMBER,
32 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
33 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
34 X_MSG_COUNT OUT NOCOPY NUMBER,
35 X_MSG_DATA OUT NOCOPY VARCHAR2,
36 X_TEAM_MEMBER_ID OUT NOCOPY JTF_RS_TEAM_MEMBERS.TEAM_MEMBER_ID%TYPE
37 ) IS
38
39 l_api_version CONSTANT NUMBER := 1.0;
40 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_RESOURCE_TEAM_MEMBERS';
41 l_team_id jtf_rs_team_members.team_id%TYPE := p_team_id;
42 l_team_number jtf_rs_teams_vl.team_number%TYPE := p_team_number;
43 l_team_resource_id jtf_rs_team_members.team_resource_id%TYPE := p_team_resource_id;
44 l_team_resource_number NUMBER := p_team_resource_number;
45 l_resource_type jtf_rs_team_members.resource_type%TYPE := upper(p_resource_type);
46 l_team_member_id jtf_rs_team_members.team_member_id%TYPE;
47
48 l_team_id_out jtf_rs_team_members.team_id%TYPE;
49 l_team_resource_id_out jtf_rs_team_members.team_resource_id%TYPE;
50
51 BEGIN
52
53
54 SAVEPOINT create_resource_member_pub;
55
56 x_return_status := fnd_api.g_ret_sts_success;
57
58 -- DBMS_OUTPUT.put_line(' Started Create Resource Member Pub ');
59
60
61 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
62
63 RAISE fnd_api.g_exc_unexpected_error;
64
65 END IF;
66
67
68 IF fnd_api.to_boolean(p_init_msg_list) THEN
69
70 fnd_msg_pub.initialize;
71
72 END IF;
73
74
75 /* Validate the Resource Team. */
76
77 BEGIN
78
79 jtf_resource_utl.validate_resource_team(
80 p_team_id => l_team_id,
81 p_team_number => l_team_number,
82 x_return_status => x_return_status,
83 x_team_id => l_team_id_out
84 );
85
86 -- added for NOCOPY
87 l_team_id := l_team_id_out;
88
89
90 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
91
92 x_return_status := fnd_api.g_ret_sts_unexp_error;
93
94 RAISE fnd_api.g_exc_unexpected_error;
95
96 END IF;
97
98 END; /* End of Resource Team Validation */
99
100
101
102 /* Validate the Resource Type */
103
104 IF l_resource_type NOT IN ('GROUP', 'INDIVIDUAL') THEN
105
106 -- dbms_output.put_line('Resource Type can only be Group or Resource');
107
108 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE_TYPE');
109 fnd_msg_pub.add;
110
111 x_return_status := fnd_api.g_ret_sts_unexp_error;
112
113 RAISE fnd_api.g_exc_unexpected_error;
114
115 END IF;
116
117
118 /* Validate the Team Resource */
119
120 IF l_resource_type = 'INDIVIDUAL' then
121
122 jtf_resource_utl.validate_resource_number(
123 p_resource_id => l_team_resource_id,
124 p_resource_number => l_team_resource_number,
125 x_return_status => x_return_status,
126 x_resource_id => l_team_resource_id_out
127 );
128 -- added for NOCOPY
129 l_team_resource_id := l_team_resource_id_out;
130
131 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
132
133 x_return_status := fnd_api.g_ret_sts_unexp_error;
134
135 RAISE fnd_api.g_exc_unexpected_error;
136
137 END IF;
138
139 END IF; /* End of Resource Validation */
140
141
142 IF l_resource_type = 'GROUP' then
143
144 jtf_resource_utl.validate_resource_group(
145 p_group_id => l_team_resource_id,
146 p_group_number => l_team_resource_number,
147 x_return_status => x_return_status,
148 x_group_id => l_team_resource_id_out
149 );
150
151 -- added for NOCOPY
152 l_team_resource_id := l_team_resource_id_out;
153
154 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
155
156 x_return_status := fnd_api.g_ret_sts_unexp_error;
157
158 RAISE fnd_api.g_exc_unexpected_error;
159
160 END IF;
161
162 END IF; /* End of Resource Validation */
163
164
165 jtf_rs_team_members_pvt.create_resource_team_members
166 (P_API_VERSION => 1,
167 P_INIT_MSG_LIST => fnd_api.g_false,
168 P_COMMIT => fnd_api.g_false,
169 P_TEAM_ID => l_team_id,
170 P_TEAM_RESOURCE_ID => l_team_resource_id,
171 P_RESOURCE_TYPE => l_resource_type,
172 X_RETURN_STATUS => x_return_status,
173 X_MSG_COUNT => x_msg_count,
174 X_MSG_DATA => x_msg_data,
175 X_TEAM_MEMBER_ID => x_team_member_id
176 );
177
178
179 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
180
181 -- dbms_output.put_line('Failed status from call to private procedure');
182
183 RAISE fnd_api.g_exc_unexpected_error;
184
185 END IF;
186
187
188 IF fnd_api.to_boolean(p_commit) THEN
189
190 COMMIT WORK;
191
192 END IF;
193
194 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
195
196
197 EXCEPTION
198
199
200 WHEN fnd_api.g_exc_unexpected_error THEN
201
202 -- DBMS_OUTPUT.put_line (' ========================================== ');
203
204 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
205
206 ROLLBACK TO create_resource_member_pub;
207
208 x_return_status := fnd_api.g_ret_sts_unexp_error;
209
210 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
211
212
213 WHEN OTHERS THEN
214
215 -- DBMS_OUTPUT.put_line (' ========================================== ');
216
217 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Create Team Member Pub ============= ');
218
219 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
220
221 ROLLBACK TO create_resource_member_pub;
222
223 x_return_status := fnd_api.g_ret_sts_unexp_error;
224
225 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
226
227
228 END create_resource_team_members;
229
230
231
232
233 /* Procedure to delete the resource team members. */
234
235 PROCEDURE delete_resource_team_members
236 (P_API_VERSION IN NUMBER,
237 P_INIT_MSG_LIST IN VARCHAR2 DEFAULT FND_API.G_FALSE,
238 P_COMMIT IN VARCHAR2 DEFAULT FND_API.G_FALSE,
239 P_TEAM_ID IN JTF_RS_TEAM_MEMBERS.TEAM_ID%TYPE,
240 P_TEAM_NUMBER IN JTF_RS_TEAMS_VL.TEAM_NUMBER%TYPE,
241 P_TEAM_RESOURCE_ID IN JTF_RS_TEAM_MEMBERS.TEAM_RESOURCE_ID%TYPE,
242 P_TEAM_RESOURCE_NUMBER IN NUMBER,
243 P_RESOURCE_TYPE IN JTF_RS_TEAM_MEMBERS.RESOURCE_TYPE%TYPE,
244 P_OBJECT_VERSION_NUM IN JTF_RS_TEAM_MEMBERS.OBJECT_VERSION_NUMBER%TYPE,
245 X_RETURN_STATUS OUT NOCOPY VARCHAR2,
246 X_MSG_COUNT OUT NOCOPY NUMBER,
247 X_MSG_DATA OUT NOCOPY VARCHAR2
248 ) IS
249
250 l_api_version CONSTANT NUMBER := 1.0;
251 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_TEAM_MEMBERS';
252 l_team_id jtf_rs_team_members.team_id%TYPE := p_team_id;
253 l_team_number jtf_rs_teams_vl.team_number%TYPE := p_team_number;
254 l_team_resource_id jtf_rs_team_members.team_resource_id%TYPE := p_team_resource_id;
255 l_team_resource_number NUMBER := p_team_resource_number;
256 l_resource_type jtf_rs_team_members.resource_type%TYPE := upper(p_resource_type);
257 l_team_member_id jtf_rs_team_members.team_member_id%TYPE;
258 --added for NOCOPY
259 l_team_id_out jtf_rs_team_members.team_id%TYPE;
260 l_team_resource_id_out jtf_rs_team_members.team_resource_id%TYPE;
261
262
263 BEGIN
264
265
266 SAVEPOINT delete_resource_member_pub;
267
268 x_return_status := fnd_api.g_ret_sts_success;
269
270 -- DBMS_OUTPUT.put_line(' Started Delete Resource Member Pub ');
271
272
273 IF NOT fnd_api.compatible_api_call(l_api_version, p_api_version, l_api_name, g_pkg_name) THEN
274
275 RAISE fnd_api.g_exc_unexpected_error;
276
277 END IF;
278
279
280 IF fnd_api.to_boolean(p_init_msg_list) THEN
281
282 fnd_msg_pub.initialize;
283
284 END IF;
285
286
287
288 /* Validate the Resource Team. */
289
290 BEGIN
291
292 jtf_resource_utl.validate_resource_team(
293 p_team_id => l_team_id,
294 p_team_number => l_team_number,
295 x_return_status => x_return_status,
296 x_team_id => l_team_id_out
297 );
298 -- added for NOCOPY
299 l_team_id := l_team_id_out;
300
301 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
302
303 x_return_status := fnd_api.g_ret_sts_unexp_error;
304
305 RAISE fnd_api.g_exc_unexpected_error;
306
307 END IF;
308
309 END; /* End of Resource Team Validation */
310
311
312
313 /* Validate the Resource Type */
314
315 IF l_resource_type NOT IN ('GROUP', 'INDIVIDUAL') THEN
316
317 -- dbms_output.put_line('Resource Type can only be Group or Resource');
318
319 fnd_message.set_name('JTF', 'JTF_RS_INVALID_RESOURCE_TYPE');
320 fnd_msg_pub.add;
321
322 x_return_status := fnd_api.g_ret_sts_unexp_error;
323
324 RAISE fnd_api.g_exc_unexpected_error;
325
326 END IF;
327
328
329 /* Validate the Team Resource */
330
331 IF l_resource_type = 'INDIVIDUAL' then
332
333 jtf_resource_utl.validate_resource_number(
334 p_resource_id => l_team_resource_id,
335 p_resource_number => l_team_resource_number,
336 x_return_status => x_return_status,
337 x_resource_id => l_team_resource_id_out
338 );
339 -- added for NOCOPY
340 l_team_resource_id := l_team_resource_id_out;
341
342 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
343
344 x_return_status := fnd_api.g_ret_sts_unexp_error;
345
346 RAISE fnd_api.g_exc_unexpected_error;
347
348 END IF;
349
350 END IF; /* End of Resource Validation */
351
352
353 IF l_resource_type = 'GROUP' then
354
355 jtf_resource_utl.validate_resource_group(
356 p_group_id => l_team_resource_id,
357 p_group_number => l_team_resource_number,
358 x_return_status => x_return_status,
359 x_group_id => l_team_resource_id_out
360 );
361
362 -- added for NOCOPY
363 l_team_resource_id := l_team_resource_id_out;
364
365 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
366
367 x_return_status := fnd_api.g_ret_sts_unexp_error;
368
369 RAISE fnd_api.g_exc_unexpected_error;
370
371 END IF;
372
373 END IF; /* End of Resource Validation */
374
375
376
377 /* Call the private procedure for delete */
378
379 jtf_rs_team_members_pvt.delete_resource_team_members
380 (P_API_VERSION => 1,
381 P_INIT_MSG_LIST => fnd_api.g_false,
382 P_COMMIT => fnd_api.g_false,
383 P_TEAM_ID => l_team_id,
384 P_TEAM_RESOURCE_ID => l_team_resource_id,
385 P_RESOURCE_TYPE => l_resource_type,
386 P_OBJECT_VERSION_NUM => p_object_version_num,
387 X_RETURN_STATUS => x_return_status,
388 X_MSG_COUNT => x_msg_count,
389 X_MSG_DATA => x_msg_data
390 );
391
392
393 IF NOT (x_return_status = fnd_api.g_ret_sts_success) THEN
394
395 -- dbms_output.put_line('Failed status from call to private procedure');
396
397 RAISE fnd_api.g_exc_unexpected_error;
398
399 END IF;
400
401
402 IF fnd_api.to_boolean(p_commit) THEN
403
404 COMMIT WORK;
405
406 END IF;
407
408 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
409
410
411 EXCEPTION
412
413
414 WHEN fnd_api.g_exc_unexpected_error THEN
415
416 -- DBMS_OUTPUT.put_line (' ========================================== ');
417
418 -- DBMS_OUTPUT.put_line ('=========== Raised Unexpected Error ======= ======== ');
419
420 ROLLBACK TO delete_resource_member_pub;
421
422 x_return_status := fnd_api.g_ret_sts_unexp_error;
423
424 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
425
426
427 WHEN OTHERS THEN
428
429 -- DBMS_OUTPUT.put_line (' ========================================== ');
430
431 -- DBMS_OUTPUT.put_line (' =========== Raised Others in Delete Team Member Pub ============= ');
432
433 -- DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
434
435 ROLLBACK TO delete_resource_member_pub;
436
437 x_return_status := fnd_api.g_ret_sts_unexp_error;
438
439 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
440
441
442 END delete_resource_team_members;
443
444
445
446 END jtf_rs_team_members_pub;