1 PACKAGE BODY PA_RBS_ASGMT_PVT AS
2 /* $Header: PARASGVB.pls 120.1.12010000.2 2008/09/18 04:49:38 rballamu ship $*/
3
4 -- Standard who
5 g_last_updated_by NUMBER(15) := FND_GLOBAL.USER_ID;
6 g_last_update_date DATE := SYSDATE;
7 g_creation_date DATE := SYSDATE;
8 g_created_by NUMBER(15) := FND_GLOBAL.USER_ID;
9 -- g_last_update_login NUMBER(15) := FND_GLOBAL.LOG_ID;
10
11 /**********************************************************
12 * Function : Check_Primary_rep_flag
13 * Parameter: p_project_id,p_rbs_header_id
14 * Return : Varchar2
15 * Desc : The purpose of this Function is to determine if
16 * The Value of the Primary reporting RBS flag can be set to
17 * 'Y' or not. It checks to see if any other RBS asso.
18 * to the project have the flag set to 'Y' already.
19 * If yes then we shouldn't allow the user to create/Update
20 * the value for the flag to 'Y'.
21 ******************************************************************/
22 FUNCTION Check_Primary_rep_flag
23 (p_project_id IN NUMBER,
24 p_rbs_header_id IN NUMBER)
25 RETURN VARCHAR2
26 IS
27 l_primary_rep_exists Varchar2(1) := 'N';
28 BEGIN
29 BEGIN
30 SELECT 'Y'
31 INTO l_primary_rep_exists
32 FROM dual
33 WHERE EXISTS
34 (SELECT rbs_prj_assignment_id
35 FROM pa_rbs_prj_assignments
36 WHERE project_id = p_project_id
37 AND assignment_status = 'ACTIVE'
38 AND primary_reporting_rbs_flag = 'Y'
39 AND rbs_header_id <> p_rbs_header_id);
40 EXCEPTION
41 WHEN NO_DATA_FOUND THEN
42 l_primary_rep_exists := 'N';
43 WHEN OTHERS THEN
44 l_primary_rep_exists := 'Y';
45 END;
46
47 RETURN l_primary_rep_exists;
48 END Check_Primary_rep_flag;
49
50 /**************************************************************
51 * Procedure : Create_RBS_Assignment
52 * Description : The purpose of this procedure is to associate
53 * an RBS to a project for any of the 4 uasges:-
54 * Reporting, Financial Plan, Workplan and
55 * Program Reporting.
56 * Reporting is the Default Usage type for all the
57 * associations.
58 * This Package would take care of all the validations
59 * necessary and then call the PA_RBS_ASGMT_Pkg to
60 * do the insertion.
61 *Called From : PA_RBS_ASGMT_PUB.Create_RBS_Assignment
62 ****************************************************************/
63 PROCEDURE Create_RBS_Assignment(
64 p_rbs_header_id IN NUMBER,
65 p_rbs_version_id IN NUMBER DEFAULT NULL,
66 p_project_id IN NUMBER,
67 p_wp_usage_flag IN VARCHAR2 DEFAULT NULL,
68 p_fp_usage_flag IN VARCHAR2 DEFAULT NULL,
69 p_prog_rep_usage_flag IN VARCHAR2 DEFAULT NULL,
70 p_primary_rep_flag IN VARCHAR2 DEFAULT 'N',
71 x_return_status OUT NOCOPY VARCHAR2 ,
72 x_msg_count OUT NOCOPY NUMBER ,
73 x_error_msg_data OUT NOCOPY VARCHAR2)
74 IS
75 --Declaration of Local Variables
76 l_count Number;
77 l_fp_assoc_id Number(15);
78 l_rbs_version_id Number(15);
79 l_exists_association Varchar2(1);
80 l_rbs_header_id Number;
81 l_return_status Varchar2(30);
82 l_rbs_prj_assignment_id Number(15);
83 l_record_version_number Number;
84 l_primary_assignment Varchar2(1);
85 l_wp_flag Varchar2(1);
86 l_fp_flag Varchar2(1);
87 l_prog_flag Varchar2(1);
88 l_msg_code Number;
89 l_sys_program_flag Varchar2(1);
90 BEGIN
91 x_msg_count := 0;
92 x_return_status := FND_API.G_RET_STS_SUCCESS;
93
94 -- Check to see if the flag parameters have been defaulted or passed
95 -- in as Y or N.
96 l_prog_flag := nvl(p_prog_rep_usage_flag, 'N');
97 l_wp_flag := nvl(p_wp_usage_flag, 'N');
98 l_fp_flag := nvl(p_fp_usage_flag, 'N');
99
100 /******************************************
101 * Check if the Header ID passed is a valid in
102 * the system. This is done by checking for the
103 * header ID in the pa_rbs_headers_b table.
104 ********************************************/
105 BEGIN
106 SELECT rbs_header_id
107 INTO l_rbs_header_id
108 FROM pa_rbs_headers_b
109 WHERE rbs_header_id = p_rbs_header_id;
110 EXCEPTION
111 WHEN NO_DATA_FOUND THEN
112 x_return_status := FND_API.G_RET_STS_ERROR;
113 x_msg_count := x_msg_count + 1;
114 --Need to get a message for this.
115 x_error_msg_data := 'PA_INVALID_HEADER_ID';
116 PA_UTILS.Add_Message ('PA', x_error_msg_data);
117 Return;
118 WHEN OTHERS THEN
119 x_return_status := FND_API.G_RET_STS_ERROR;
120 x_msg_count := x_msg_count + 1;
121 x_error_msg_data := 'PA_INVALID_HEADER_ID';
122 PA_UTILS.Add_Message ('PA', x_error_msg_data);
123 Return;
124 END;
125
126 /************************************************
127 * Check if a Value has been passed for the Version
128 * ID parameter. If a value has been passed, then
129 * Use that else call the API x and get the Version ID.
130 * **************************************************/
131 -- IF p_rbs_version_id IS NULL THEN -- for bug 7376494
132 l_rbs_version_id :=
133 PA_RBS_UTILS.get_max_rbs_frozen_version(p_rbs_header_id);
134 -- ELSE -- bug 7376494
135 /********************************************
136 * Check if the version ID passed corresponds to the
137 * header_id passed.
138 **************************************************/
139 IF l_rbs_version_id IS NULL THEN -- bug 7376494
140 BEGIN
141
142 IF p_rbs_version_id IS NOT NULL THEN
143 SELECT rbs_version_id
144 INTO l_rbs_version_id
145 FROM pa_rbs_versions_b
146 WHERE rbs_version_id = p_rbs_version_id
147 AND rbs_header_id = p_rbs_header_id
148 AND status_code = 'FROZEN';
149 ELSE
150 RAISE NO_DATA_FOUND;
151 END IF;
152
153 EXCEPTION
154 WHEN NO_DATA_FOUND THEN
155 x_return_status := FND_API.G_RET_STS_ERROR;
156 x_msg_count := x_msg_count + 1;
157 --Need to get a message for this.
158 x_error_msg_data := 'PA_VER_NOT_CORR_HEADER';
159 PA_UTILS.Add_Message ('PA', x_error_msg_data);
160 RETURN;
161 WHEN OTHERS THEN
162 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
163 x_msg_count := x_msg_count + 1;
164 RETURN;
165 END;
166 END IF;
167
168
169 /************************************************
170 * First check if the program reporting usage flag is set to 'Y'.
171 * If so then check for the sys_program_flag of that project.
172 * Raise error if it is 'N' coz it cannot be used for reporting
173 * since its not a program.
174 *************************************************/
175
176 SELECT sys_program_flag
177 INTO l_sys_program_flag
178 FROM pa_projects_all
179 WHERE project_id = p_project_id;
180
181 IF p_prog_rep_usage_flag = 'Y' AND l_sys_program_flag = 'N' THEN
182 x_return_status := FND_API.G_RET_STS_ERROR;
183 x_msg_count := x_msg_count + 1;
184 --Need to get a message for this.
185 x_error_msg_data := 'PA_RBS_NOT_A_PROGRAM';
186 PA_UTILS.Add_Message ('PA', x_error_msg_data);
187 RETURN;
188 END IF;
189
190
191 /***********************************************
192 * First check the primary reporting flag that is passed.
193 * If the value is passed as 'Y' then check if for the RBS
194 * header ID passed, any of the assignments have a value
195 * of 'Y'. If yes then throw an error message and Return.
196 ****************************************************/
197 IF p_primary_rep_flag = 'Y' THEN
198 IF Check_Primary_rep_flag(p_project_id,p_rbs_header_id) = 'Y' THEN
199 x_return_status := FND_API.G_RET_STS_ERROR;
200 x_msg_count := x_msg_count + 1;
201 --Need to get a message for this.
202 x_error_msg_data := 'PA_EXISTS_PRIM_REP';
203 PA_UTILS.Add_Message ('PA', x_error_msg_data);
204 RETURN;
205 END IF;
206 END IF;
207
208 -- Bug 3712581 -- If the WP usage is Y, check whether another RBS
209 -- association exists with WP usage as Y. If it does, then make it N.
210
211 IF l_wp_flag = 'Y' THEN
212
213 UPDATE pa_rbs_prj_assignments
214 SET wp_usage_flag = 'N'
215 WHERE rbs_header_id <> p_rbs_header_id
216 AND rbs_version_id <> l_rbs_version_id
217 AND project_id = p_project_id
218 AND wp_usage_flag = 'Y'
219 AND assignment_status = 'ACTIVE';
220
221 END IF;
222
223 -- Bug 3712581 -- If the FP usage is Y, check whether another RBS
224 -- association exists with FP usage as Y which is not used by any plan
225 -- type or version. If it does, then make it N.
226
227 -- hr_utility.trace_on(NULL, 'RMFP');
228 -- hr_utility.trace('start *********');
229 -- hr_utility.trace('l_rbs_version_id IS : ' || l_rbs_version_id);
230 -- hr_utility.trace('p_project_id IS : ' || p_project_id);
231 IF l_fp_flag = 'Y' THEN
232
233 BEGIN
234 SELECT rpa.rbs_prj_assignment_id
235 INTO l_fp_assoc_id
236 FROM pa_rbs_prj_assignments rpa
237 WHERE rpa.project_id = p_project_id
238 AND rpa.fp_usage_flag = 'Y'
239 AND rpa.assignment_status = 'ACTIVE'
240 -- AND rpa.rbs_version_id <> l_rbs_version_id
241 AND rpa.rbs_version_id NOT IN (
242 SELECT pfo.rbs_version_id
243 FROM pa_proj_fp_options pfo
244 WHERE pfo.project_id = rpa.project_id
245 AND ((pfo.fin_plan_type_id <> (
246 SELECT pt.fin_plan_type_id
247 FROM pa_fin_plan_types_b pt
248 WHERE use_for_workplan_flag = 'Y'))
249 OR
250 (pfo.fin_plan_type_id IS NULL)));
251
252 -- hr_utility.trace('l_fp_assoc_id IS : ' || l_fp_assoc_id);
253 UPDATE pa_rbs_prj_assignments
254 SET fp_usage_flag = 'N'
255 WHERE rbs_prj_assignment_id = l_fp_assoc_id;
256
257 -- hr_utility.trace('done upd');
258 EXCEPTION WHEN NO_DATA_FOUND THEN
259 l_fp_assoc_id := null;
260 END;
261
262 END IF;
263
264 /***********************************************
265 * Check for existance of the RBS association for
266 * the project. ie if the RBS passed already exists
267 * for the project_id passed. Then we just need to
268 * do an Update. Only if it does not exist do an
269 * Insert.
270 ************************************************/
271 BEGIN
272 SELECT 'Y'
273 INTO l_exists_association
274 FROM dual
275 WHERE EXISTS
276 (SELECT rbs_prj_assignment_id
277 FROM pa_rbs_prj_assignments
278 WHERE rbs_header_id = p_rbs_header_id
279 AND rbs_version_id = l_rbs_version_id
280 AND project_id = p_project_id
281 AND assignment_status = 'ACTIVE');
282 EXCEPTION
283 WHEN NO_DATA_FOUND THEN
284 l_exists_association := 'N';
285 WHEN OTHERS THEN
286 l_exists_association := 'Y';
287 END;
288
289 IF l_exists_association = 'Y' THEN
290 /******************************************
291 * If record already exists then derive
292 * the corr rbs_prj_assignment_id and the
293 * record_version_number and then pass those
294 * to the Update_Row Procedure.
295 ********************************************/
296 BEGIN
297 SELECT rbs_prj_assignment_id, record_version_number,
298 nvl(p_wp_usage_flag, wp_usage_flag),
299 nvl(p_fp_usage_flag, fp_usage_flag),
300 nvl(p_prog_rep_usage_flag, prog_rep_usage_flag)
301 INTO l_rbs_prj_assignment_id, l_record_version_number,
302 l_wp_flag, l_fp_flag, l_prog_flag
303 FROM pa_rbs_prj_assignments
304 WHERE project_id = p_project_id
305 AND rbs_header_id = p_rbs_header_id
306 AND rbs_version_id = l_rbs_version_id; -- changed 7376494 to pass on value of max frozen RBS version id
307 EXCEPTION
308 WHEN OTHERS THEN
309 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
310 x_msg_count := x_msg_count + 1;
311 RETURN;
312 END;
313
314 /************************************************
315 * Call to PA_RBS_ASGMT_PKG.Update_Row procedure, which would
316 * take care of Updation of the pa_rbs_prj_assignments
317 * table.
318 *****************************************************/
319 PA_RBS_ASGMT_PKG.Update_Row(
320 p_rbs_prj_assignment_id => l_rbs_prj_assignment_id ,
324 p_primary_rep_flag => p_primary_rep_flag,
321 p_wp_usage_flag => l_wp_flag,
322 p_fp_usage_flag => l_fp_flag,
323 p_prog_rep_usage_flag => l_prog_flag,
325 p_record_version_number => l_record_version_number,
326 x_return_status => l_return_status );
327
328 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
329 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
330 x_msg_count := x_msg_count + 1;
331 RETURN;
332 END IF;
333 ELSE
334 /**************************************
335 * Get the rbs Assignment ID from the sequence
336 * PA_RBS_PRJ_ASSIGNMENTS_S
337 ******************************************/
338 SELECT PA_RBS_PRJ_ASSIGNMENTS_S.NEXTVAL
339 INTO l_rbs_prj_assignment_id
340 FROM DUAL;
341 /************************************************
342 * Call to PA_RBS_ASGMT_PKG.Insert_Row procedure, which would
343 * take care of Insertion into the pa_rbs_prj_assignments
344 * table.
345 *****************************************************/
346 BEGIN
347 SELECT count(*)
348 INTO l_count
349 FROM pa_rbs_prj_assignments
350 WHERE project_id = p_project_id
351 AND assignment_status = 'ACTIVE' ;
352 END;
353 IF l_count = 0 THEN
354 l_primary_assignment := 'Y';
355 ELSE
356 l_primary_assignment := p_primary_rep_flag;
357 END IF;
358
359 PA_RBS_ASGMT_PKG.Insert_Row(
360 p_rbs_assignment_id => l_rbs_prj_assignment_id,
361 p_rbs_header_id => p_rbs_header_id,
362 p_rbs_version_id => l_rbs_version_id,
363 p_project_id => p_project_id,
364 p_wp_usage_flag => l_wp_flag,
365 p_fp_usage_flag => l_fp_flag,
366 p_prog_rep_usage_flag => l_prog_flag,
367 p_primary_rep_flag => l_primary_assignment,
368 x_return_status => l_return_status);
369
370 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
371 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
372 x_msg_count := x_msg_count + 1;
373 RETURN;
374 END IF;
375 BEGIN
376 PJI_FM_XBS_ACCUM_MAINT.RBS_PUSH
377 (P_NEW_RBS_VERSION_ID => l_rbs_version_id,
378 P_PROJECT_ID => p_project_id,
379 X_RETURN_STATUS => l_return_status,
380 x_msg_code => l_msg_code);
381 END;
382 /****************************************************
383 * Updating the Value of x_rbs_prj_assignment_id and
384 * x_record_version_number after insertion.??
385 ********************************************************/
386 END IF;
387
388 -- After Update or Insert, if the program reporting flag was Y, log event
389 IF p_prog_rep_usage_flag = 'Y' THEN
390 PJI_FM_XBS_ACCUM_MAINT.RBS_PUSH
391 (P_NEW_RBS_VERSION_ID => l_rbs_version_id,
392 P_PROJECT_ID => p_project_id,
393 P_PROGRAM_FLAG => p_prog_rep_usage_flag,
394 X_RETURN_STATUS => l_return_status,
395 X_MSG_CODE => l_msg_code);
396 END IF;
397
398 /* Add check to ensure that if only one association exists, then
399 * the primary reporting flag is set to 'Y' */
400 BEGIN
401 SELECT count(*)
402 INTO l_count
403 FROM pa_rbs_prj_assignments
404 WHERE project_id = p_project_id
405 AND assignment_status = 'ACTIVE'
406 AND primary_reporting_rbs_flag = 'Y';
407 END;
408
409 IF l_count = 0 THEN
410 UPDATE pa_rbs_prj_assignments
411 SET primary_reporting_rbs_flag = 'Y'
412 WHERE project_id = p_project_id
413 AND assignment_status = 'ACTIVE'
414 AND rownum = 1;
415 END IF;
416
417 END Create_RBS_Assignment;
418 /***************************/
419 /**************************************************************
420 * Procedure : Update_RBS_Assignment
421 * Description : The purpose of this procedure is to update an associate
422 * of an RBS to a project for any of the 4 uasges:-
423 * Reporting, Financial Plan, Workplan and
424 * Program Reporting.
425 * Reporting is the Default Usage type for all the
426 * associations.
427 * This Package would take care of all the validations
428 * necessary and then call the PA_RBS_ASGMT_Pkg.Update_Row to
429 * do the Updation.
430 *Called From : PA_RBS_ASGMT_PUB.Update_RBS_Assignment
431 ****************************************************************/
432 PROCEDURE Update_RBS_Assignment(
433 p_rbs_prj_assignment_id IN NUMBER,
434 p_wp_usage_flag IN VARCHAR2 DEFAULT 'N',
435 p_fp_usage_flag IN VARCHAR2 DEFAULT 'N',
436 p_prog_rep_usage_flag IN VARCHAR2 DEFAULT 'N',
437 p_primary_rep_flag IN VARCHAR2 DEFAULT 'N',
438 p_record_version_number IN Number,
439 p_set_as_primary IN Varchar2 DEFAULT 'N',
440 x_return_status OUT NOCOPY VARCHAR2 ,
444 l_return_status Varchar2(30);
441 x_msg_count OUT NOCOPY NUMBER,
442 x_error_msg_data OUT NOCOPY VARCHAR2 )
443 IS
445 l_project_id Number;
446 l_rbs_header_id Number;
447 l_rbs_version_id Number;
448 l_msg_code Varchar2(30);
449 BEGIN
450 x_msg_count := 0;
451 x_return_status := FND_API.G_RET_STS_SUCCESS;
452 /*************************************************
453 * Derive the Project_id and RBS_header_id based on
454 * the rbs_prj_assignment_id from the
455 * pa_rbs_prj_assignments table. These values will then
456 * be passed to the Check_Primary_rep_flag function
457 **************************************************/
458 BEGIN
459 SELECT project_id,rbs_header_id,rbs_version_id
460 INTO l_project_id,l_rbs_header_id,l_rbs_version_id
461 FROM pa_rbs_prj_assignments
462 WHERE rbs_prj_assignment_id = p_rbs_prj_assignment_id;
463 EXCEPTION
464 WHEN OTHERS THEN
465 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
466 x_msg_count := x_msg_count + 1;
467 RETURN;
468 END;
469 IF p_set_as_primary = 'Y' THEN
470 BEGIN
471 UPDATE pa_rbs_prj_assignments
472 SET primary_reporting_rbs_flag = 'N'
473 WHERE project_id = l_project_id
474 AND primary_reporting_rbs_flag = 'Y'
475 AND assignment_status = 'ACTIVE';
476 END;
477 END IF;
478 /***********************************************
479 * First check the primary reporting flag that is passed.
480 * If the value is passed as 'Y' then check if for the RBS
481 * header ID passed, any of the assignments have a value
482 * of 'Y'. If yes then throw an error message and Return.
483 ****************************************************/
484 IF p_primary_rep_flag = 'Y' THEN
485 IF Check_Primary_rep_flag(l_project_id,l_rbs_header_id) = 'Y' THEN
486 x_return_status := FND_API.G_RET_STS_ERROR;
487 x_msg_count := x_msg_count + 1;
488 --Need to get a message for this.
489 x_error_msg_data := 'PA_EXISTS_PRIM_REP';
490 PA_UTILS.Add_Message ('PA', x_error_msg_data);
491 RETURN;
492 END IF;
493 END IF;
494
495 /******************************************************
496 * Call to the Pa_rbs_Asgmt_pkg.Update_Row Procedure
497 * Which would update the values in the table
498 * pa_rbs_prj_asignemnts with the values passed.
499 *****************************************************/
500 Pa_Rbs_Asgmt_Pkg.Update_Row(
501 p_rbs_prj_assignment_id => p_rbs_prj_assignment_id ,
502 p_wp_usage_flag => p_wp_usage_flag,
503 p_fp_usage_flag => p_fp_usage_flag,
504 p_prog_rep_usage_flag => p_prog_rep_usage_flag,
505 p_primary_rep_flag => p_primary_rep_flag,
506 p_record_version_number => p_record_version_number,
507 x_return_status => l_return_status );
508
509 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
511 x_msg_count := x_msg_count + 1;
512 RETURN;
513 END IF;
514 IF p_prog_rep_usage_flag = 'Y' THEN
515 PJI_FM_XBS_ACCUM_MAINT.RBS_PUSH
516 (P_NEW_RBS_VERSION_ID => l_rbs_version_id,
517 P_PROJECT_ID => l_project_id,
518 P_PROGRAM_FLAG => p_prog_rep_usage_flag,
519 X_RETURN_STATUS => l_return_status,
520 x_msg_code => l_msg_code);
521 END IF;
522 END Update_RBS_Assignment;
523
524 /**************************************************************
525 * Procedure : Delete_RBS_Assignment
526 * Description : The purpose of this procedure is to Delete an associate
527 * of an RBS to a project for any of the 4 uasges:-
528 * This Package would take care of all the validations
529 * necessary and then call the PA_RBS_ASGMT_Pkg.Delete_Row to
530 * do the Remove operation.
531 * We cannot Remove any RBS that is being used for
532 * Workplan or Financial Plan.
533 * Called From : PA_RBS_ASGMT_PUB.Delete_RBS_Assignment
534 ****************************************************************/
535 PROCEDURE Delete_RBS_Assignment(
536 p_rbs_prj_assignment_id IN NUMBER,
537 x_return_status OUT NOCOPY VARCHAR2,
538 x_msg_count OUT NOCOPY NUMBER,
539 x_error_msg_data OUT NOCOPY VARCHAR2 )
540 IS
541 l_wp_usage_flag Varchar2(1);
542 l_fp_usage_flag Varchar2(1);
543 l_prog_rep_usage_flag Varchar2(1);
544 l_project_id Number;
545 l_rbs_version_id Number;
546 l_chk_prog Number;
547 BEGIN
548 /********************************************
549 * This select is used to retrieve the wp_usage_flag,
550 * fp_usage_flag and prog_rep_usage_flag
551 * for the rbs_rpj_assignment_id passed, from the
552 * pa_rbs_prj_assignments table.
553 * We will then use these values to determine if Removal
554 * of record is possible or not.
555 * **********************************************/
556 BEGIN
557 SELECT WP_USAGE_FLAG, FP_USAGE_FLAG,
558 PROG_REP_USAGE_FLAG,project_id,rbs_version_id
559 INTO l_wp_usage_flag, l_fp_usage_flag,
563 EXCEPTION
560 l_prog_rep_usage_flag,l_project_id,l_rbs_version_id
561 FROM pa_rbs_prj_assignments
562 WHERE RBS_PRJ_ASSIGNMENT_ID = p_rbs_prj_assignment_id;
564 WHEN OTHERS THEN
565 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
566 x_msg_count := x_msg_count + 1;
567 RETURN;
568 END;
569 /**************************************************
570 * If the RBS is being used for Workplan or Financial
571 * plan or Program reportinmg then we cannot remove the
572 * record.
573 * So we are only allowing removal of records for 'Reporting'.
574 * **************************************************/
575 IF (l_wp_usage_flag = 'Y' OR l_fp_usage_flag = 'Y')
576 THEN
577 x_return_status := FND_API.G_RET_STS_ERROR;
578 x_msg_count := x_msg_count + 1;
579 --Need to get a message for this.
580 --If RBS used for WP FP it cannot be deleted.
581 x_error_msg_data := 'PA_RBS_USED_WP_FP_PR';
582 PA_UTILS.Add_Message ('PA', x_error_msg_data);
583 RETURN;
584 END IF;
585 l_chk_prog := PJI_UTILS.CHECK_PROGRAM_RBS(
586 p_project_id => l_project_id,
587 p_rbs_version_id => l_rbs_version_id);
588 IF l_chk_prog = -1 THEN
589 x_return_status := FND_API.G_RET_STS_ERROR;
590 x_msg_count := x_msg_count + 1;
591 --Need to get a message for this.
592 --If RBS used for Prog Rep it cannot be deleted.
593 x_error_msg_data := 'PA_RBS_USED_PROG_REP';
594 PA_UTILS.Add_Message ('PA', x_error_msg_data);
595 RETURN;
596 END IF;
597
598
599 PA_RBS_ASGMT_PKG.Delete_Row(
600 p_rbs_prj_assignment_id => p_rbs_prj_assignment_id,
601 x_return_status => x_return_status);
602
603 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
605 x_msg_count := x_msg_count + 1;
606 RETURN;
607 END IF;
608
609 -- Log an event for summarization purposes.
610 PJI_FM_XBS_ACCUM_MAINT.RBS_DELETE (
611 p_rbs_version_id => l_rbs_version_id
612 , p_project_id => l_project_id
613 , x_return_status => x_return_status
614 , x_msg_code => x_error_msg_data);
615
616 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
617 x_msg_count := x_msg_count + 1;
618 RETURN;
619 END IF;
620
621 END Delete_RBS_Assignment;
622 /**************************************************
623 * Procedure : Associate_Rbs_To_Program
624 * Description : This API is used to associate an
625 * RBS to the list of project_ID's
626 * passed in as a table.
627 * We are going to set the program reporting
628 * flag = 'Y' and the reporting flag = 'Y'
629 * Rest of the flag's = 'N'
630 **************************************************/
631 PROCEDURE Associate_Rbs_To_Program(
632 p_rbs_header_id IN NUMBER,
633 p_rbs_version_id IN NUMBER DEFAULT NULL,
634 p_project_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
635 x_return_status OUT NOCOPY VARCHAR2)
636 IS
637 l_rbs_version_id Number;
638 l_exception Exception;
639 l_exists_association Varchar2(1);
640 l_rbs_prj_assignment_id Number;
641 l_record_version_number Number;
642 l_return_status Varchar2(30);
643 BEGIN
644 x_return_status := Fnd_Api.G_Ret_Sts_Success;
645 /***********************************************************
646 * This check is done to determine that the mandatory values
647 * ie p_rbs_header_id is passed in and there is atleast 1
648 * project ID passed in.
649 **********************************************************/
650 IF (p_project_id_tbl.count = 0) OR (p_rbs_header_id IS NULL) THEN
651 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
652 RETURN;
653 END IF;
654 /*******************************************************
655 * If no value is passed for the p_rbs_version_id parameter
656 * then derive it, with a call to function
657 * PA_RBS_UTILS.get_max_rbs_frozen_version passing the
658 * rbs_header_id value.
659 ******************************************************/
660 IF p_rbs_version_id IS NULL THEN
661 l_rbs_version_id :=
662 PA_RBS_UTILS.get_max_rbs_frozen_version(p_rbs_header_id);
663 ELSE
664 /*******************************************************
665 * Do a check to determine that the version ID passed in
666 * corr to the header ID passed in.
667 *******************************************************/
668 BEGIN
669 SELECT rbs_version_id
670 INTO l_rbs_version_id
671 FROM pa_rbs_versions_b
672 WHERE rbs_version_id = p_rbs_version_id
673 AND rbs_header_id = p_rbs_header_id
674 AND status_code = 'FROZEN';
675 EXCEPTION
676 WHEN OTHERS THEN
677 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
678 RETURN;
679 END;
680 END IF;
681 /******************************************************
682 * Delete all the associations in the pa_rbs_prj_assignments
683 * table which corr to the rbs_header, version and project ID
684 * passed in, Which are Obsolete.
688 WHERE rbs_header_id = p_rbs_header_id
685 ******************************************************/
686 /*FORALL i IN p_project_id_tbl.first .. p_project_id_tbl.last
687 DELETE FROM pa_rbs_prj_assignments
689 AND rbs_version_id = l_rbs_version_id
690 AND project_id = p_project_id_tbl(i)
691 AND assignment_status = 'OBSOLETE';
692 */
693
694 FOR i IN p_project_id_tbl.first .. p_project_id_tbl.last
695 LOOP
696 /***********************************************
697 * Check for existance of the RBS association for
698 * the project. ie if the RBS passed already exists
699 * for the project_id passed. Then we just need to
700 * do an Update. Only if it does not exist do an
701 * Insert.
702 ************************************************/
703 BEGIN
704 SELECT 'Y'
705 INTO l_exists_association
706 FROM dual
707 WHERE EXISTS
708 (SELECT rbs_prj_assignment_id
709 FROM pa_rbs_prj_assignments
710 WHERE rbs_header_id = p_rbs_header_id
711 AND rbs_version_id = l_rbs_version_id
712 AND project_id = p_project_id_tbl(i)
713 AND assignment_status = 'ACTIVE');
714 EXCEPTION
715 WHEN NO_DATA_FOUND THEN
716 l_exists_association := 'N';
717 WHEN OTHERS THEN
718 l_exists_association := 'Y';
719 END;
720
721 IF l_exists_association = 'Y' THEN
722 /******************************************
723 * If record already exists then derive
724 * the corr rbs_prj_assignment_id and the
725 * record_version_number and then pass those
726 * to the Update_Row Procedure.
727 ********************************************/
728 BEGIN
729 SELECT rbs_prj_assignment_id, record_version_number
730 INTO l_rbs_prj_assignment_id, l_record_version_number
731 FROM pa_rbs_prj_assignments
732 WHERE project_id = p_project_id_tbl(i)
733 AND rbs_header_id = p_rbs_header_id
734 AND rbs_version_id = l_rbs_version_id;
735 EXCEPTION
736 WHEN OTHERS THEN
737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
738 RETURN;
739 END;
740
741 /************************************************
742 * Call to PA_RBS_ASGMT_PKG.Update_Row procedure, which would
743 * take care of Updation of the pa_rbs_prj_assignments
744 * table.
745 * We only need to set the value for the
746 * reporting_usage flag = 'Y' and the prog_rep_usage_flag
747 * = 'Y'
748 *****************************************************/
749 BEGIN
750 UPDATE pa_rbs_prj_assignments
751 SET reporting_usage_flag = 'Y',
752 prog_rep_usage_flag = 'Y',
753 last_update_date = sysdate,
754 record_version_number = record_version_number + 1
755 WHERE Rbs_prj_assignment_id = l_rbs_prj_assignment_id
756 AND assignment_status = 'ACTIVE'
757 AND prog_rep_usage_flag = 'N'
758 AND NVL(record_version_number, 0) =
759 NVL(l_record_version_number, 0);
760 EXCEPTION
761 WHEN OTHERS THEN
762 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
763 RETURN;
764 END;
765 ELSE
766 /**************************************
767 * Get the rbs Assignment ID from the sequence
768 * PA_RBS_PRJ_ASSIGNMENTS_S
769 ******************************************/
770 SELECT PA_RBS_PRJ_ASSIGNMENTS_S.NEXTVAL
771 INTO l_rbs_prj_assignment_id
772 FROM DUAL;
773
774 PA_RBS_ASGMT_PKG.Insert_Row(
775 p_rbs_assignment_id => l_rbs_prj_assignment_id,
776 p_rbs_header_id => p_rbs_header_id,
777 p_rbs_version_id => l_rbs_version_id,
778 p_project_id => p_project_id_tbl(i),
779 p_wp_usage_flag => 'N',
780 p_fp_usage_flag => 'N',
781 p_prog_rep_usage_flag => 'Y',
782 p_primary_rep_flag => 'N',
783 x_return_status => l_return_status );
784
785 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
787 RETURN;
788 END IF;
789
790 END IF;
791
792 END LOOP;
793 END Associate_Rbs_To_Program;
794
795 /*****************************************************
796 * Procedure : Assign_New_Version
797 * Description : This API is used to assign the
798 * new version number passed to all
799 * the Projects passed in as a table.
800 * Update the pa_rbs_prj_assignments
801 * table.
802 ****************************************************/
803 PROCEDURE Assign_New_Version(
804 p_rbs_new_version_id IN Number,
805 p_project_id_tbl IN SYSTEM.PA_NUM_TBL_TYPE,
806 x_return_status OUT NOCOPY Varchar2)
807 IS
808 l_rbs_header_id Number;
809 BEGIN
810 X_Return_Status := Fnd_Api.G_Ret_Sts_Success;
811 BEGIN
812 SELECT rbs_header_id
813 INTO l_rbs_header_id
814 FROM pa_rbs_versions_b
815 WHERE rbs_version_id = p_rbs_new_version_id;
816 EXCEPTION
817 WHEN OTHERS THEN
818 X_Return_Status := Fnd_Api.G_Ret_Sts_UNEXP_ERROR;
819 Return;
820 END;
821
822 FORALL i IN p_project_id_tbl.FIRST.. p_project_id_tbl.LAST
823 UPDATE pa_rbs_prj_assignments
824 SET rbs_version_id = p_rbs_new_version_id
825 WHERE project_id = p_project_id_tbl(i)
826 AND rbs_header_id = l_rbs_header_id
827 AND assignment_status = 'ACTIVE' ;
828 EXCEPTION
829 WHEN OTHERS THEN
830 X_Return_Status := Fnd_Api.G_Ret_Sts_UNEXP_ERROR;
831 Return;
832 END Assign_New_Version;
833
834 /*****************************************************
835 * Procedure : Copy_Project_Assignment
836 * Description : This API is used to copy the
837 * RBS project assignments from the
838 * source project to the destination
839 * project.
840 ****************************************************/
841 PROCEDURE Copy_Project_Assignment(
842 p_rbs_src_project_id IN NUMBER,
843 p_rbs_dest_project_id IN NUMBER,
844 x_return_status OUT NOCOPY Varchar2)
845 IS
846 BEGIN
847 x_return_status := Fnd_Api.G_Ret_Sts_SUCCESS;
848 IF p_rbs_src_project_id IS NULL OR p_rbs_dest_project_id IS NULL THEN
849 X_Return_Status := Fnd_Api.G_Ret_Sts_UNEXP_ERROR;
850 Return;
851 END IF;
852
853 BEGIN
854 INSERT INTO pa_rbs_prj_assignments
855 (RBS_PRJ_ASSIGNMENT_ID,
856 PROJECT_ID,
857 RBS_VERSION_ID,
858 RBS_HEADER_ID,
859 REPORTING_USAGE_FLAG,
860 WP_USAGE_FLAG,
861 FP_USAGE_FLAG,
862 PROG_REP_USAGE_FLAG,
863 PRIMARY_REPORTING_RBS_FLAG,
864 ASSIGNMENT_STATUS,
865 LAST_UPDATE_DATE,
866 LAST_UPDATED_BY,
867 CREATION_DATE,
868 CREATED_BY,
869 LAST_UPDATE_LOGIN,
870 RECORD_VERSION_NUMBER)
871 SELECT
872 PA_RBS_PRJ_ASSIGNMENTS_S.NEXTVAL,
873 p_rbs_dest_project_id,
874 a.RBS_VERSION_ID,
875 a.RBS_HEADER_ID,
876 a.REPORTING_USAGE_FLAG,
877 a.WP_USAGE_FLAG,
878 a.FP_USAGE_FLAG,
879 a.PROG_REP_USAGE_FLAG,
880 a.PRIMARY_REPORTING_RBS_FLAG,
881 a.ASSIGNMENT_STATUS,
882 SYSDATE,
883 FND_GLOBAL.USER_ID,
884 SYSDATE,
885 FND_GLOBAL.USER_ID,
886 FND_GLOBAL.LOGIN_ID,
887 1
888 FROM pa_rbs_prj_assignments a
889 WHERE a.project_id = p_rbs_src_project_id
890 and (a.RBS_VERSION_ID,a.RBS_HEADER_ID)
891 NOT IN (select rbs_version_id,rbs_header_id
892 from pa_rbs_prj_assignments
893 where project_id = p_rbs_dest_project_id);
894 EXCEPTION
895 WHEN OTHERS THEN
896 X_Return_Status := Fnd_Api.G_Ret_Sts_UNEXP_ERROR;
897 Return;
898 END;
899
900 EXCEPTION
901 WHEN OTHERS THEN
902 X_Return_Status := Fnd_Api.G_Ret_Sts_UNEXP_ERROR;
903 Return;
904 END Copy_Project_Assignment;
905
906 END PA_RBS_ASGMT_PVT;
[Home] [Help]