[Home] [Help]
PACKAGE BODY: APPS.PA_ROLE_PROFILES_PKG
Source
1 PACKAGE BODY PA_ROLE_PROFILES_PKG AS
2 -- $Header: PARPRPKB.pls 120.1 2005/08/19 16:59:10 mwasowic noship $
3 --
4 -- PROCEDURE
5 -- Insert_Row1
6 -- PURPOSE
7 -- This procedure inserts a row into the pa_role_profiles
8 -- table.
9
10 PROCEDURE Insert_Row1
11 ( p_profile_name IN VARCHAR,
12 p_description IN VARCHAR2,
13 p_effective_start_date IN DATE,
14 p_effective_end_date IN DATE DEFAULT NULL,
15 p_profile_type_code IN VARCHAR2 DEFAULT NULL,
16 p_approval_status_code IN VARCHAR2 DEFAULT NULL,
17 p_business_group_id IN NUMBER DEFAULT NULL,
18 p_organization_id IN NUMBER DEFAULT NULL,
19 p_job_id IN NUMBER DEFAULT NULL,
20 p_position_id IN NUMBER DEFAULT NULL,
21 p_resource_id IN NUMBER DEFAULT NULL,
22 x_profile_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
23 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
24
25 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
26
27 BEGIN
28 x_return_status := FND_API.G_RET_STS_SUCCESS;
29
30 INSERT INTO pa_role_profiles
31 (profile_id,
32 profile_name,
33 description,
34 effective_start_date,
35 effective_end_date,
36 profile_type_code,
37 approval_status_code,
38 business_group_id,
39 organization_id,
40 job_id,
41 position_id,
42 resource_id,
43 creation_date,
44 created_by,
45 last_update_date,
46 last_updated_by)
47 VALUES
48 (PA_ROLE_PROFILES_S.nextval,
49 p_profile_name,
50 p_description,
51 p_effective_start_date,
52 p_effective_end_date,
53 p_profile_type_code,
54 p_approval_status_code,
55 p_business_group_id,
56 p_organization_id,
57 p_job_id,
58 p_position_id,
59 p_resource_id,
60 SYSDATE,
61 FND_GLOBAL.USER_ID,
62 SYSDATE,
63 FND_GLOBAL.USER_ID)
64 RETURNING
65 profile_id INTO x_profile_id;
66
67 EXCEPTION
68 WHEN OTHERS THEN -- catch the exceptins here
69 -- Set the exception Message and the stack
70 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ROLE_PROFILES_PKG.Insert_Row1'
71 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
72 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
73 RAISE;
74 END Insert_Row1;
75
76
77 --
78 -- PROCEDURE
79 -- Insert_Row2
80 -- PURPOSE
81 -- This procedure inserts a row into the pa_role_profile_lines
82 -- table.
83
84 PROCEDURE Insert_Row2
85 ( p_profile_id IN NUMBER,
86 p_project_role_id IN NUMBER,
87 p_role_weighting IN NUMBER,
88 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
89
90 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
91
92 BEGIN
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 INSERT INTO pa_role_profile_lines
96 (profile_id,
97 project_role_id,
98 role_weighting,
99 creation_date,
100 created_by,
101 last_update_date,
102 last_updated_by)
103 VALUES
104 (p_profile_id,
105 p_project_role_id,
106 p_role_weighting,
107 SYSDATE,
108 FND_GLOBAL.USER_ID,
109 SYSDATE,
110 FND_GLOBAL.USER_ID);
111
112 EXCEPTION
113 WHEN OTHERS THEN -- catch the exceptins here
114 -- Set the exception Message and the stack
115 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ROLE_PROFILES_PKG.Insert_Row2'
116 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
117 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
118 RAISE;
119 END Insert_Row2;
120
121
122 --
123 -- PROCEDURE
124 -- Update_Row
125 -- PURPOSE
126 -- This procedure updates a row in the pa_role_profiles
127 -- table.
128
129 PROCEDURE Update_Row
130 ( p_profile_id IN NUMBER,
131 p_profile_name IN VARCHAR,
132 p_description IN VARCHAR2,
133 p_effective_start_date IN DATE,
134 p_effective_end_date IN DATE DEFAULT NULL,
135 -- p_profile_type_code IN VARCHAR2 DEFAULT NULL,
136 p_approval_status_code IN VARCHAR2 DEFAULT NULL,
137 p_business_group_id IN NUMBER DEFAULT NULL,
138 p_organization_id IN NUMBER DEFAULT NULL,
139 p_job_id IN NUMBER DEFAULT NULL,
140 p_position_id IN NUMBER DEFAULT NULL,
141 p_resource_id IN NUMBER DEFAULT NULL,
142 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
143
144 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
145
146 BEGIN
147 x_return_status := FND_API.G_RET_STS_SUCCESS;
148
149 UPDATE pa_role_profiles SET
150 profile_name = p_profile_name,
151 description = p_description,
152 effective_start_date = p_effective_start_date,
153 effective_end_date = p_effective_end_date,
154 approval_status_code = p_approval_status_code,
155 business_group_id = p_business_group_id,
156 organization_id = p_organization_id,
157 job_id = p_job_id,
158 position_id = p_position_id,
159 resource_id = p_resource_id,
160 last_update_date = SYSDATE,
161 last_updated_by = FND_GLOBAL.USER_ID
162 WHERE profile_id = p_profile_id;
163
164 EXCEPTION
165 WHEN OTHERS THEN -- catch the exceptins here
166 -- Set the exception Message and the stack
167 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_ROLE_PROFILES_PKG.Update_Row'
168 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
169 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
170 RAISE;
171 END Update_Row;
172
173
174
175 /*******************************************************************************
176 Beginning of Angie's code, need to change later
177 ******************************************************************************/
178
179 /**********************************************************************
180 * This procedure will launch workflow to add a new Resource Role Profile
181 * and Role Profile Lines to PA_ROLE_PROFILES and PA_ROLE_PROFILE_LINES
182 * after proper validation.
183 * This will be called from 'Add Resource Role Profile' page of PJR.
184 **********************************************************************/
185 /*
186 PROCEDURE Add_Res_Profiles
187 ( p_resource_id IN NUMBER,
188 p_profile_name IN VARCHAR2,
189 p_profile_type_code IN VARCHAR2,
190 p_description IN VARCHAR2 := NULL,
191 p_effective_start_date IN DATE,
192 p_effective_end_date IN DATE := NULL,
193 p_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
194 p_role_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
195 p_weighting_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
196 p_commit IN VARCHAR2 := FND_API.G_FALSE,
197 x_return_status OUT VARCHAR2,
198 x_msg_count OUT NUMBER,
199 x_msg_data OUT VARCHAR2)
200 IS
201 CURSOR is_conflict_profile_csr IS
202 SELECT 'Y'
203 FROM pa_role_profiles
204 WHERE resource_id = p_resource_id
205 AND profile_type_code = p_profile_type_code
206 AND (TRUNC(effective_start_date) BETWEEN TRUNC(p_effective_start_date)
207 AND NVL(TRUNC(p_effective_end_date), TRUNC(effective_start_date))
208 OR
209 TRUNC(p_effective_start_date) BETWEEN TRUNC(effective_start_date)
210 AND NVL(TRUNC(effective_end_date), TRUNC(effective_start_date)) )
211 AND rownum = 1;
212
213 l_profile_id NUMBER;
214 l_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
215 l_exists VARCHAR2(1);
216 l_return_status VARCHAR2(1);
217 l_msg_index_out NUMBER := 0;
218 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
219 BEGIN
220 -------------------------------------------------------------------
221 -- Initial Setup
222 -------------------------------------------------------------------
223 IF l_enable_log = 'Y' THEN
224 PA_DEBUG.init_err_stack('PA_ROLE_PROFILES_PUB.Add_Res_Profile');
225 END IF;
226 dbms_output.put_line('started');
227
228 -- Clear the global PL/SQL message table
229 FND_MSG_PUB.initialize;
230
231 -- Issue API savepoint if the transaction is to be committed
232 IF (p_commit = FND_API.G_TRUE) THEN
233 SAVEPOINT ROLE_PUB_ADD_RES_PRF;
234 END IF;
235
236 x_return_status := FND_API.G_RET_STS_SUCCESS;
237
238 -------------------------------------------------------------------
239 -- Validate Resource Profile (any conflicting profile?)
240 -------------------------------------------------------------------
241 OPEN is_conflict_profile_csr;
242 FETCH is_conflict_profile_csr INTO l_exists;
243 dbms_output.put_line('after cursor');
244
245 IF is_conflict_profile_csr%FOUND THEN
246 dbms_output.put_line('corsor found');
247 pa_utils.add_message (p_app_short_name => 'PA',
248 p_msg_name => 'PA_DATE_CONFLICT');
249 RAISE FND_API.G_EXC_ERROR;
250 END IF;
251 CLOSE is_conflict_profile_csr;
252
253 dbms_output.put_line('corsor found passed');
254
255 -------------------------------------------------------------------
256 -- Validate Resource Profile Lines (role_id/name, total weighting)
257 -------------------------------------------------------------------
258 Validate_Profile_Lines
259 ( p_role_id_tbl => p_role_id_tbl,
260 p_role_name_tbl => p_role_name_tbl,
261 p_weighting_tbl => p_weighting_tbl,
262 x_role_id_tbl => l_role_id_tbl,
263 x_return_status => l_return_status);
264
265 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
266 RAISE FND_API.G_EXC_ERROR;
267 END IF;
268 dbms_output.put_line('Validate_Profile_Lines passed');
269
270 -------------------------------------------------------------------
271 -- Start Workflow : workflow api should be called instead below
272 -------------------------------------------------------------------
273
274 -------------------------------------------------------------------
275 -- Insert Resource Profile to 'PA_ROLE_PROFILES'
276 -------------------------------------------------------------------
277 INSERT INTO pa_role_profiles
278 (profile_id,
279 profile_name,
280 profile_type_code,
281 resource_id,
282 description,
283 effective_start_date,
284 effective_end_date,
285 approval_status_code,
286 creation_date,
287 created_by,
288 last_update_date,
289 last_updated_by)
290 VALUES
291 (PA_ROLE_PROFILES_S.nextval,
292 p_profile_name,
293 p_profile_type_code,
294 p_resource_id,
295 p_description,
296 p_effective_start_date,
297 p_effective_end_date,
298 'ASGMT_APPRVL_APPROVED', -- should be changed
299 SYSDATE,
300 FND_GLOBAL.USER_ID,
301 SYSDATE,
302 FND_GLOBAL.USER_ID)
303 RETURNING
304 profile_id INTO l_profile_id;
305 dbms_output.put_line('after inserting pa_role_profiles, l_profile_id:'||l_profile_id);
306
307 -------------------------------------------------------------------
308 -- Insert Resource Profile Lines to 'PA_ROLE_PROFILE_LINES'
309 -------------------------------------------------------------------
310 FOR i IN 1..l_role_id_tbl.count LOOP
311 INSERT INTO pa_role_profile_lines
312 (profile_id,
313 project_role_id,
314 role_weighting,
315 creation_date,
316 created_by,
317 last_update_date,
318 last_updated_by)
319 VALUES
320 (l_profile_id,
321 l_role_id_tbl(i),
322 p_weighting_tbl(i),
323 SYSDATE,
324 FND_GLOBAL.USER_ID,
325 SYSDATE,
326 FND_GLOBAL.USER_ID);
327 END LOOP;
328
329 -------------------------------------------------------------------
330 -- Exceptions
331 -------------------------------------------------------------------
332 EXCEPTION
333 WHEN FND_API.G_EXC_ERROR THEN
334 x_return_status := FND_API.G_RET_STS_ERROR;
335 x_msg_count := FND_MSG_PUB.Count_Msg;
336 IF x_msg_count = 1 THEN
337 pa_interface_utils_pub.get_messages (p_encoded => FND_API.G_TRUE,
338 p_msg_index => 1,
339 p_data => x_msg_data,
340 p_msg_index_out => l_msg_index_out );
341 END IF;
342 WHEN OTHERS THEN
343 dbms_output.put_line('other error');
344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
345 x_msg_data := SQLERRM;
346 IF p_commit = FND_API.G_TRUE THEN
347 ROLLBACK TO ROLE_PUB_ADD_RES_PRF;
348 END IF;
349
350 -- Set the exception Message and the stack
351 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_ROLE_PROFILES_PUB.Add_Res_Profiles'
352 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
353 RAISE; -- This is optional depending on the needs
354
355 END Add_Res_Profiles;
356 */
357
358 /**********************************************************************
359 * This procedure will launch workflow to update the Resource Role Profile
360 * and Role Profile Lines in PA_ROLE_PROFILES and PA_ROLE_PROFILE_LINES
361 * after proper validation.
362 * This will be called from 'Update Resource Role Profile' page of PJR.
363 **********************************************************************/
364 /*
365 PROCEDURE Update_Res_Profiles
366 ( p_profile_id IN NUMBER,
367 p_profile_name IN VARCHAR2,
368 p_description IN VARCHAR2 := NULL,
369 p_effective_start_date IN DATE,
370 p_effective_end_date IN DATE := NULL,
371 p_role_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
372 p_role_name_tbl IN SYSTEM.PA_VARCHAR2_80_TBL_TYPE,
373 p_weighting_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
374 p_commit IN VARCHAR2 := FND_API.G_FALSE,
375 x_return_status OUT VARCHAR2,
376 x_msg_count OUT NUMBER,
377 x_msg_data OUT VARCHAR2)
378 IS
379 CURSOR is_conflict_profile_csr IS
380 SELECT 'Y'
381 FROM pa_role_profiles pf1,
382 pa_role_profiles pf2
383 WHERE pf2.profile_id = p_profile_id
384 AND pf1.resource_id = pf2.resource_id
385 AND pf1.profile_type_code = pf2.profile_type_code
386 AND pf1.profile_id <> p_profile_id
387 AND (TRUNC(pf1.effective_start_date) BETWEEN TRUNC(p_effective_start_date)
388 AND NVL(TRUNC(p_effective_end_date), TRUNC(pf1.effective_start_date))
389 OR
390 TRUNC(p_effective_start_date) BETWEEN TRUNC(pf1.effective_start_date)
391 AND NVL(TRUNC(pf1.effective_end_date), TRUNC(pf1.effective_start_date)))
392 AND rownum = 1;
393
394 l_role_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
395 l_exists VARCHAR2(1) := 'N';
396 l_return_status VARCHAR2(1);
397 l_msg_index_out NUMBER := 0;
398 l_enable_log varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
399 BEGIN
400 -------------------------------------------------------------------
401 -- Initial Setup
402 -------------------------------------------------------------------
403 IF l_enable_log = 'Y' THEN
404 PA_DEBUG.init_err_stack('PA_ROLE_PROFILES_PUB.Update_Res_Profile');
405 END IF;
406
407 -- Clear the global PL/SQL message table
408 FND_MSG_PUB.initialize;
409
410 -- Issue API savepoint if the transaction is to be committed
411 IF (p_commit = FND_API.G_TRUE) THEN
412 SAVEPOINT ROLE_PUB_UPD_RES_PRF;
413 END IF;
414
415 x_return_status := FND_API.G_RET_STS_SUCCESS;
416
417 -------------------------------------------------------------------
418 -- Validate Resource Profile (start_date, end_date)
419 -------------------------------------------------------------------
420 -- Check if there is any conflicting profile of this resource and profile_type
421 OPEN is_conflict_profile_csr;
422 FETCH is_conflict_profile_csr INTO l_exists;
423
424 IF is_conflict_profile_csr%FOUND THEN
425 pa_utils.add_message (p_app_short_name => 'PA',
426 p_msg_name => 'PA_DATE_CONFLICT');
427 RAISE FND_API.G_EXC_ERROR;
428 END IF;
429 CLOSE is_conflict_profile_csr;
430
431 -------------------------------------------------------------------
432 -- Validate Resource Profile Lines (role_id/name, total weighting)
433 -------------------------------------------------------------------
434 Validate_Profile_Lines
435 ( p_role_id_tbl => p_role_id_tbl,
436 p_role_name_tbl => p_role_name_tbl,
437 p_weighting_tbl => p_weighting_tbl,
438 x_role_id_tbl => l_role_id_tbl,
439 x_return_status => l_return_status);
440
441 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
442 RAISE FND_API.G_EXC_ERROR;
443 END IF;
444
445 -------------------------------------------------------------------
446 -- Start Workflow
447 -------------------------------------------------------------------
448
449 -------------------------------------------------------------------
450 -- Update Resource Profile to 'PA_ROLE_PROFILES'
451 -------------------------------------------------------------------
452 UPDATE pa_role_profiles
453 SET profile_name = p_profile_name,
454 description = p_description,
455 effective_start_date = p_effective_start_date,
456 effective_end_date = p_effective_end_date,
457 approval_status_code = 'ASGMT_APPRVL_APPROVED', -- should be changed
458 last_update_date = SYSDATE,
459 last_updated_by = FND_GLOBAL.USER_ID
460 WHERE profile_id = p_profile_id;
461
462 -------------------------------------------------------------------
463 -- Insert Resource Profile Lines to 'PA_ROLE_PROFILE_LINES'
464 -------------------------------------------------------------------
465 -- Delete all roles in the pa_role_profile_lines table before insertion.
466 DELETE FROM pa_role_profile_lines
467 WHERE profile_id = p_profile_id;
468
469 FOR i IN 1..l_role_id_tbl.count LOOP
470 INSERT INTO pa_role_profile_lines
471 (profile_id,
472 project_role_id,
473 role_weighting,
474 creation_date,
475 created_by,
476 last_update_date,
477 last_updated_by)
478 VALUES
479 (p_profile_id,
480 l_role_id_tbl(i),
481 p_weighting_tbl(i),
482 SYSDATE,
483 FND_GLOBAL.USER_ID,
484 SYSDATE,
485 FND_GLOBAL.USER_ID);
486 END LOOP;
487
488 -------------------------------------------------------------------
489 -- Exceptions
490 -------------------------------------------------------------------
491 EXCEPTION
492 WHEN FND_API.G_EXC_ERROR THEN
493 x_return_status := FND_API.G_RET_STS_ERROR;
494 x_msg_count := FND_MSG_PUB.Count_Msg;
495 IF x_msg_count = 1 THEN
496 pa_interface_utils_pub.get_messages (p_encoded => FND_API.G_TRUE,
497 p_msg_index => 1,
498 p_data => x_msg_data,
499 p_msg_index_out => l_msg_index_out );
500 END IF;
501 WHEN OTHERS THEN
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
503 x_msg_data := SQLERRM;
504 IF p_commit = FND_API.G_TRUE THEN
505 ROLLBACK TO ROLE_PUB_UPD_RES_PRF;
506 END IF;
507
508 -- Set the exception Message and the stack
509 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'PA_ROLE_PROFILES_PUB.Update_Res_Profiles'
510 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
511 RAISE; -- This is optional depending on the needs
512
513 END Update_Res_Profiles;
514
515 ***************** end of Angie's temporary saving code, need to change later*****/
516
517 END PA_ROLE_PROFILES_PKG;