[Home] [Help]
PACKAGE BODY: APPS.PA_PERF_EXCP_UTILS
Source
1 PACKAGE BODY PA_PERF_EXCP_UTILS AS
2 /* $Header: PAPEUTLB.pls 120.1 2005/08/19 16:40:11 mwasowic noship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 Procedure get_kpa_color_indicator_list
7 (
8 p_object_type in varchar2
9 , p_object_id in number
10 , p_kpa_codes in SYSTEM.PA_VARCHAR2_30_TBL_TYPE
11 , x_indicators out NOCOPY SYSTEM.PA_VARCHAR2_2000_TBL_TYPE --File.Sql.39 bug 4440895
12 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
13 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
14 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
15 , x_ind_meaning out NOCOPY SYSTEM.PA_VARCHAR2_80_TBL_TYPE --Added for bug 4064923 --File.Sql.39 bug 4440895
16 ) IS
17
18 l_return_code varchar2(200);
19
20
21 l_meaning VARCHAR2(80);
22 l_icon VARCHAR2(150);
23
24
25
26 CURSOR get_indicator (l_kpa_code IN VARCHAR2)
27 IS
28 SELECT meaning, attribute1
29 FROM pa_perf_kpa_summary ppks, pa_perf_kpa_summary_det ppksd,
30 pa_lookups pl
31 WHERE
32 ppks.object_type = p_object_type
33 AND ppks.object_id = p_object_id
34 AND ppks.current_flag = 'Y'
35 AND ppks.kpa_summary_id = ppksd.kpa_summary_id
36 AND ppksd.kpa_code = l_kpa_code
37 AND pl.lookup_type = 'PA_PERF_INDICATORS'
38 AND pl.lookup_code = ppksd.indicator_code
39 ;
40
41 CURSOR
42 get_overall_indicator
43 IS
44 SELECT meaning, attribute1
45 FROM pa_perf_kpa_summary ppks,
46 pa_lookups pl
47 WHERE
48 ppks.object_type = p_object_type
49 AND ppks.object_id = p_object_id
50 AND ppks.current_flag = 'Y'
51 AND pl.lookup_type = 'PA_PERF_INDICATORS'
52 AND pl.lookup_code = ppks.perf_status_code
53 ;
54
55 BEGIN
56
57 x_return_status := FND_API.G_RET_STS_SUCCESS;
58
59 x_indicators := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
60 x_ind_meaning := SYSTEM.PA_VARCHAR2_80_TBL_TYPE(); --Added for bug 4064923
61 x_indicators.extend(p_kpa_codes.count);
62 x_ind_meaning.extend(p_kpa_codes.count); --Added for bug 4064923
63 pa_security_pvt.check_user_privilege(x_ret_code => l_return_code,
64 x_return_status => x_return_status,
65 x_msg_count => x_msg_count,
66 x_msg_data => x_msg_data,
67 p_privilege => 'PA_PERF_EXCEPTIONS',
68 p_object_name => p_object_type,
69 p_object_key => p_object_id);
70
71
72
73 IF l_return_code <> fnd_api.g_true THEN
74 RETURN;
75
76
77 ELSE
78
79 --x_indicators.DELETE;
80
81 FOR i IN p_kpa_codes.first..p_kpa_codes.last LOOP
82 IF p_kpa_codes(i) <> 'STATUS' THEN
83
84 OPEN get_indicator(p_kpa_codes(i));
85 FETCH get_indicator INTO l_meaning, l_icon;
86
87 IF (get_indicator%found) THEN
88
89 /* Commented for bug# 4169188
90 x_indicators(i) := '<a href="OA.jsp?page=/oracle/apps/pa/excp/webui/ExceptionListPG&akRegionApplicationId=275&paKPACode='
91 || p_kpa_codes(i) || '&paProjectId=' || p_object_id
92 || '"><p align="center"><img ALT="'|| l_meaning || '" src="/OA_MEDIA/'
93 || l_icon ||'" border="0" align="middle"></p></a> '; --Changed for bug# 3841535*/
94 x_indicators(i) := l_icon; --added for bug# 4169188
95 x_ind_meaning(i) := l_meaning; --Added for bug 4064923
96 ELSE
97
98 x_indicators(i) := NULL;
99 x_ind_meaning(i) := NULL; --Added for bug 4064923
100 END IF;
101
102 CLOSE get_indicator;
103
104
105 ELSE
106 --- get overall status indicator
107
108 OPEN get_overall_indicator;
109 FETCH get_overall_indicator INTO l_meaning, l_icon;
110
111 IF (get_overall_indicator%found) THEN
112
113 /* Commented for bug# 4169188
114 x_indicators(i) := '<a href="OA.jsp?akRegionCode=PA_PROJECT_HOME_LAYOUT&addBreadCrumb=RS&OAPB=PA_BRAND&akRegionApplicationId=275'
115 || '&paProjectId=' || p_object_id
116 || '"><p align="center"><img ALT="'|| l_meaning || '" src="/OA_MEDIA/'
117 || l_icon ||'" border="0" align="middle"></p></a> '; --Changed for bug# 3841535*/
118 x_indicators(i) := l_icon; --added for bug# 4169188
119
120 x_ind_meaning(i) := l_meaning; --Added for bug 4064923
121 ELSE
122
123 x_indicators(i) := NULL;
124 x_ind_meaning(i) := NULL; --Added for bug 4064923
125 END IF;
126 CLOSE get_overall_indicator;
127 END IF;
128
129 END LOOP;
130
131
132
133 END IF;
134
135 EXCEPTION
136 WHEN OTHERS THEN
137 --
138 -- Set the excetption Message and the stack
139 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PERF_EXCP_UTILS.get_kpa_name_list'
140 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
141 --
142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
143
144
145 END;
146
147
148 Procedure get_kpa_name_list
149 (
150 p_kpa_codes in SYSTEM.PA_VARCHAR2_30_TBL_TYPE
151 , x_kpa_names out NOCOPY SYSTEM.PA_VARCHAR2_240_TBL_TYPE --File.Sql.39 bug 4440895
152 , x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
153 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
154 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
155 ) IS
156
157 CURSOR get_kpa_name (l_kpa_code IN VARCHAR2)
158 IS
159 SELECT meaning
160 FROM pa_lookups pl
161 WHERE pl.lookup_code = l_kpa_code
162 AND pl.lookup_type = 'PA_PERF_KEY_AREAS'
163 AND pl.enabled_flag = 'Y' ;
164
165 CURSOR get_kpa_count IS
166 SELECT COUNT(*) FROM pa_lookups
167 WHERE lookup_type = 'PA_PERF_KEY_AREAS'
168 AND lookup_code <> 'ALL';
169
170 l_count NUMBER;
171
172
173 BEGIN
174
175 x_return_status := FND_API.G_RET_STS_SUCCESS;
176
177 x_kpa_names := SYSTEM.PA_VARCHAR2_240_TBL_TYPE();
178
179 x_kpa_names.extend(p_kpa_codes.count);
180
181 FOR i IN p_kpa_codes.first..p_kpa_codes.last loop
182 OPEN get_kpa_name(p_kpa_codes(i));
183 FETCH get_kpa_name INTO x_kpa_names(i);
184 IF get_kpa_name%notfound THEN
185 x_kpa_names(i) := NULL;
186 END IF;
187
188 CLOSE get_kpa_name;
189
190 END LOOP;
191
192
193 EXCEPTION
194 WHEN OTHERS THEN
195 --
196 -- Set the excetption Message and the stack
197 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PERF_EXCP_UTILS.get_kpa_name_list'
198 ,p_procedure_name => PA_DEBUG.G_Err_Stack );
199 --
200 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
201
202 END;
203
204
205 Function get_kpa_color_indicator
206 (
207 p_object_type in varchar2
208 ,p_object_id in number
209 , p_kpa_code in varchar2
210 ) return VARCHAR2
211 IS
212
213 l_ret VARCHAR2(1);
214 l_meaning VARCHAR2(80);
215 l_icon VARCHAR2(150);
216 x_indicator VARCHAR2(2000) := null;
217
218 CURSOR get_indicator
219 IS
220 SELECT meaning, attribute1
221 FROM pa_perf_kpa_summary ppks, pa_perf_kpa_summary_det ppksd,
222 pa_lookups pl
223 WHERE
224 ppks.object_type = p_object_type
225 AND ppks.object_id = p_object_id
226 AND ppks.current_flag = 'Y'
227 AND ppks.kpa_summary_id = ppksd.kpa_summary_id
228 AND ppksd.kpa_code = p_kpa_code
229 AND pl.lookup_type = 'PA_PERF_INDICATORS'
230 AND pl.lookup_code = ppksd.indicator_code
231 ;
232
233 BEGIN
234
235
236 l_ret := pa_security_pvt.check_user_privilege(
237 p_privilege => 'PA_PERF_EXCEPTIONS',
238 p_object_name => p_object_type,
239 p_object_key => p_object_id);
240
241
242 IF l_ret <> fnd_api.g_true THEN
243
244 RETURN NULL;
245
246 END IF;
247
248
249 OPEN get_indicator;
250 FETCH get_indicator INTO l_meaning, l_icon;
251 IF (get_indicator%found) THEN
252 x_indicator :=
253 '<a href="OA.jsp?page=/oracle/apps/pa/excp/webui/ExceptionListPG&akRegionApplicationId=275&paKPACode='
254 || p_kpa_code || '&paProjectId=' || p_object_id ||
255 '"><img ALT="'|| l_meaning || '" src="/OA_MEDIA/' || l_icon
256 ||'" border="0" align="middle"></a> ';
257 ELSE
258 x_indicator := NULL;
259
260 END IF;
261
262 CLOSE get_indicator;
263
264 RETURN x_indicator;
265
266
267 END;
268
269
270
271 Function get_measure_indicator
272 (
273 p_object_type in varchar2
274 ,p_object_id in number
275 ,p_measure_id in number
276 ,p_period_type in varchar2 DEFAULT NULL
277 ,p_period_name in VARCHAR2 DEFAULT NULL
278 ,p_raw_text_flag in VARCHAR2 DEFAULT 'Y'
279 ,x_perf_txn_id out NOCOPY NUMBER --File.Sql.39 bug 4440895
280 ,x_excp_meaning out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
281 ) return VARCHAR2
282 IS
283
284 l_meaning VARCHAR2(80);
285 l_icon VARCHAR2(150);
286 l_flag VARCHAR2(1);
287 l_tran_id NUMBER;
288 l_indicator VARCHAR2(2000) := null;
289
290 CURSOR get_indicator
291 IS
292 SELECT
293 meaning, attribute1, ppt.perf_txn_id, ppt.exception_flag
294 FROM pa_perf_transactions ppt, pa_lookups pl
295 WHERE
296 ppt.perf_txn_obj_type = p_object_type
297 AND ppt.perf_txn_obj_id = p_object_id
298 AND ppt.period_type = Nvl(p_period_type, ppt.period_type)
299 AND nvl(ppt.period_name, '-9999')
300 = Nvl(p_period_name, '-9999')
301 AND ppt.measure_id = p_measure_id
302 AND ppt.current_flag = 'Y'
303 AND ppt.indicator_code = pl.lookup_code
304 AND pl.lookup_type = 'PA_PERF_INDICATORS'
305 AND ROWNUM = 1
306 ;
307
308 l_ret VARCHAR2(1);
309
310
311 BEGIN
312
313 l_ret := pa_security_pvt.check_user_privilege(
314 p_privilege => 'PA_PERF_EXCEPTIONS',
315 p_object_name => p_object_type,
316 p_object_key => p_object_id);
317
318 IF l_ret <> fnd_api.g_true THEN
319
320 RETURN NULL;
321
322 END IF;
323
324
325
326 OPEN get_indicator;
327
328 FETCH get_indicator INTO l_meaning, l_icon, l_tran_id, l_flag;
329
330 IF get_indicator%notfound THEN
331
332 l_indicator := NULL;
333 ELSE
334 x_perf_txn_id := l_tran_id;
335 x_excp_meaning := l_meaning;
336
337 IF l_flag = 'N' THEN
338 l_indicator :=
339 '<img ALT="'|| l_meaning || '" src="/OA_MEDIA/' || l_icon
340 ||'" border="0" align="middle"> ';
341 x_perf_txn_id := NULL; -- Added for bug# 3979802
342 ELSE
343 if p_raw_text_flag = 'Y' then
344 l_indicator :=
345 '<a href="OA.jsp?page=/oracle/apps/pa/excp/webui/ExceptionDetailsPG&akRegionApplicationId=275&paPerfTransId='
346 || l_tran_id || '&paProjectId=' || p_object_id ||
347 '"><img ALT="'|| l_meaning || '" src="/OA_MEDIA/' || l_icon
348 ||'" border="0" align="middle"></a> ';
349
350 else
351 l_indicator :=
352 '<img ALT="'|| l_meaning || '" src="/OA_MEDIA/' || l_icon
353 ||'" border="0" align="middle"> ';
354
355 end if;
356
357 END IF;
358 END IF;
359
360 CLOSE get_indicator;
361
362 RETURN l_indicator;
363
364 END;
365
366
367 Function get_measure_indicator_list
368 (
369 p_object_type in varchar2
370 ,p_object_id in number
371 ,p_measure_id in SYSTEM.PA_NUM_TBL_TYPE
372 ,p_period_type in varchar2 DEFAULT NULL
373 ,p_period_name in VARCHAR2 DEFAULT NULL
374 ,p_raw_text_flag in VARCHAR2 DEFAULT 'Y'
375 ,x_perf_txn_id out NOCOPY SYSTEM.PA_NUM_TBL_TYPE --File.Sql.39 bug 4440895
376 ,x_excp_meaning out NOCOPY SYSTEM.PA_VARCHAR2_80_TBL_TYPE --File.Sql.39 bug 4440895
377 ) return SYSTEM.pa_varchar2_2000_tbl_type
378
379 IS
380
381 l_meaning VARCHAR2(80);
382 l_icon VARCHAR2(150);
383 l_tran_id NUMBER;
384 l_flag VARCHAR2(1);
385 l_meaning_tbl SYSTEM.PA_VARCHAR2_80_TBL_TYPE;
386 l_icon_tbl SYSTEM.pa_varchar2_2000_tbl_type;
387 l_perf_txn_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
388 l_flag_tbl SYSTEM.PA_VARCHAR2_1_TBL_TYPE;
389 l_meaure_id_tbl SYSTEM.PA_NUM_TBL_TYPE;
390 l_indicators SYSTEM.pa_varchar2_2000_tbl_type;
391
392 CURSOR get_indicator (l_measure_id IN NUMBER)
393 IS
394 SELECT
395 meaning, attribute1, ppt.perf_txn_id, ppt.exception_flag
396 FROM pa_perf_transactions ppt, pa_lookups pl
397 WHERE
398 ppt.perf_txn_obj_type = p_object_type
399 AND ppt.perf_txn_obj_id = p_object_id
400 AND ppt.period_TYPE = Nvl(p_period_type, ppt.period_type)
401 AND nvl(ppt.period_name, '-9999')
405 AND ppt.indicator_code = pl.lookup_code
402 = Nvl(p_period_name, '-9999')
403 AND ppt.measure_id = (l_measure_id)
404 AND ppt.current_flag = 'Y'
406 AND pl.lookup_type = 'PA_PERF_INDICATORS'
407 AND ROWNUM = 1
408 ;
409
410 CURSOR get_measure_index(l_measure_id IN NUMBER)
411 IS
412 SELECT measure_index
413 FROM PA_PERF_EXCP_MSR_TEMP
414 WHERE measure_id= l_measure_id;
415
416 l_ret VARCHAR2(1);
417 j BINARY_INTEGER;
418 BEGIN
419
420
421 l_ret := pa_security_pvt.check_user_privilege(
422 p_privilege => 'PA_PERF_EXCEPTIONS',
423 p_object_name => p_object_type,
424 p_object_key => p_object_id);
425
426 IF l_ret <> fnd_api.g_true THEN
427
428 RETURN NULL;
429
430 END IF;
431
432
433 l_indicators := SYSTEM.PA_VARCHAR2_2000_TBL_TYPE();
434
435 l_indicators.extend(p_measure_id.count);
436
437 /* Code added for bug#3894113, starts here */
438
439 x_perf_txn_id := SYSTEM.PA_NUM_TBL_TYPE();
440 x_perf_txn_id.extend(p_measure_id.count);
441
442 x_excp_meaning := SYSTEM.PA_VARCHAR2_80_TBL_TYPE();
443 x_excp_meaning.extend(p_measure_id.count);
444
445 /* Code added for bug#3894113, ends here */
446
447 /*
448 Code fix for BUG#:4053713 starts
449 For perfmance issue now using a global temporary table PA_PERF_EXCP_MSR_TEMP
450 In this table we are storing the measure ids and their corresponding index in
451 the table type parameter p_measure_id.
452 This is required for using the bulk fetch instead of the cursor get_indicator
453 */
454
455 --Purging the previous data from PA_PERF_EXCP_MSR_TEMP, if any
456 delete from PA_PERF_EXCP_MSR_TEMP;
457
458 --Inserting the measure id and thier corresponding index in p_measure_id parameter in the temporary table
459 FOR i IN p_measure_id.FIRST..p_measure_id.LAST LOOP
460 INSERT INTO PA_PERF_EXCP_MSR_TEMP(measure_index,measure_id)
461 VALUES(to_number(i),p_measure_id(i));
462 END LOOP;
463
464 -- Executing the bulk fetch query to get the indicators icon
465 SELECT meaning, attribute1, ppt.perf_txn_id, ppt.exception_flag,ppt.measure_id
466 BULK COLLECT INTO l_meaning_tbl, l_icon_tbl, l_perf_txn_id_tbl, l_flag_tbl,l_meaure_id_tbl
467 FROM pa_perf_transactions ppt, pa_lookups pl
468 WHERE ppt.perf_txn_obj_type = p_object_type
469 AND ppt.perf_txn_obj_id = p_object_id
470 AND ppt.period_TYPE = Nvl(p_period_type, ppt.period_type)
471 AND nvl(ppt.period_name, '-9999')
472 = Nvl(p_period_name, '-9999')
473 AND ppt.measure_id in (select measure_id from PA_PERF_EXCP_MSR_TEMP)
474 AND ppt.current_flag = 'Y'
475 AND ppt.indicator_code = pl.lookup_code
476 AND pl.lookup_type = 'PA_PERF_INDICATORS'
477 ;
478
479 if l_meaure_id_tbl.count > 0 then
480 FOR k IN l_meaure_id_tbl.FIRST..l_meaure_id_tbl.LAST loop
481
482 --Fetching the index of the measure_id in the parameter p_measure_id
483 --which we have stored in the PA_PERF_EXCP_MSR_TEMP.MEASURE_INDEX
484 --This is required as we need to l_indicators the icon name corresponding to the measure
485 --in l_indicators at the same index as the index of the measure id in the input parameter p_measure_id
486 open get_measure_index(l_meaure_id_tbl(k));
487 fetch get_measure_index into j;
488 close get_measure_index;
489
490 x_perf_txn_id(j) := l_perf_txn_id_tbl(k);
491 x_excp_meaning(j) := l_meaning_tbl(k);
492
493 if l_flag_tbl(k) = 'N' then
494 if p_raw_text_flag = 'Y' then
495 l_indicators(j) :='<img ALT="'|| l_meaning_tbl(k) || '" src="/OA_MEDIA/' ||l_icon_tbl(k)||'" border="0" align="middle"> ';
496 else
497 l_indicators(j) := l_icon_tbl(k); -- Added for bug# 3922850
498 end if;
499 x_perf_txn_id(j) := NULL; -- Added for bug# 3979802
500 else
501 if p_raw_text_flag = 'Y' then
502 l_indicators(j) :=
503 '<img ALT="'|| l_meaning_tbl(k) || '" src="/OA_MEDIA/' || l_icon_tbl(k)
504 ||'" border="0" align="middle">';
505 else
506 l_indicators(j) := l_icon_tbl(k); -- Added for bug# 3922850
507
508 end if;
509 end if;
510
511 end loop;
512 end if;
513
514 /* Commented the lines below for bug# 4053713
515 Since now bulk fetch is being used instead of the cursor get_indicator query*/
516 -- FOR i IN p_measure_id.first..p_measure_id.last loop
517 -- OPEN get_indicator(p_measure_id(i));
518 -- FETCH get_indicator INTO l_meaning, l_icon, l_tran_id, l_flag;
519 --
520 -- IF get_indicator%notfound THEN
521 --
522 -- l_indicators(i) := NULL;
523 -- ELSE
524 -- x_perf_txn_id(i) := l_tran_id;
525 -- x_excp_meaning(i) := l_meaning;
526
527 -- IF l_flag = 'N' THEN
528 -- -- Added the below if condition for bug 3979906
529 -- if p_raw_text_flag = 'Y' then
530 -- -- Commented for bug# 3922850
531 -- l_indicators(i) := '<img ALT="'|| l_meaning || '" src="/OA_MEDIA/' ||l_icon||'" border="0" align="middle"> ';
532 -- else
533 -- l_indicators(i) := l_icon; -- Added for bug# 3922850
534 -- end if;
535 -- x_perf_txn_id(i) := NULL; -- Added for bug# 3979802
536 -- ELSE
537 -- if p_raw_text_flag = 'Y' then
538 -- -- Commented for bug# 3922850
539 -- /*l_indicators(i) :=
543 -- ||'" border="0" align="middle"></a> ';*/
540 -- '<a href="OA.jsp?page=/oracle/apps/pa/excp/webui/ExceptionDetailsPG&akRegionApplicationId=275&paPerfTransId='
541 -- || l_tran_id || '&paProjectId=' || p_object_id ||
542 -- '"><img ALT="'|| l_meaning || '" src="/OA_MEDIA/' || l_icon
544 --
545 -- -- Added for bug# 3922850
546 -- l_indicators(i) :=
547 -- '<img ALT="'|| l_meaning || '" src="/OA_MEDIA/' || l_icon
548 -- ||'" border="0" align="middle">';
549 -- else
550 -- -- Commented for bug# 3922850
551 -- /*l_indicators(i) :=
552 -- '<img ALT="'|| l_meaning || '" src="/OA_MEDIA/' || l_icon
553 -- ||'" border="0" align="middle"> ';*/
554 --
555 -- l_indicators(i) := l_icon; -- Added for bug# 3922850
556 --
557 -- end if;
558 -- END IF;
559
560
561 -- END IF;
562
563 -- CLOSE get_indicator;
564
565
566 -- END LOOP;
567
568 /* End of code changes for BUG# 4053713 */
569
570
571 RETURN l_indicators;
572
573 END;
574
575
576 Procedure copy_object_rule_assoc
577 ( p_from_object_type in varchar2
578 ,p_from_object_id in number
579 ,p_to_object_type in varchar2
580 ,p_to_object_id in number
581 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
582 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
583 ,x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
584
585 CURSOR perf_score_rules IS
586 SELECT Object_rule_id,
587 Object_type,
588 Object_id,
589 rule_id
590 FROM pa_perf_object_rules
591 WHERE object_type = p_from_object_type
592 AND object_id = p_from_object_id;
593
594 l_object_rule_id NUMBER;
595 l_rowid ROWID;
596 BEGIN
597
598 PA_DEBUG.init_err_stack('PA_PERF_EXCP_UTILS.copy_object_rule_assoc');
599 x_return_status:=fnd_api.g_ret_sts_success;
600
601 savepoint copy_object_rule_assoc;
602
603 IF (x_return_status = fnd_api.g_ret_sts_success) THEN
604 -- Insert the perf/score rules for the new object
605
606 FOR c_rec in perf_score_rules LOOP
607
608 select PA_PERF_OBJECT_RULES_S1.nextval into l_object_rule_id from dual;
609
610 PA_PERF_OBJECT_RULES_PKG.insert_row(
611 X_ROWID => l_rowid,
612 X_OBJECT_RULE_ID => l_object_rule_id,
613 X_OBJECT_TYPE => P_TO_OBJECT_TYPE,
614 X_OBJECT_ID => P_TO_OBJECT_ID,
615 X_RULE_ID => c_rec.RULE_ID,
616 X_RECORD_VERSION_NUMBER => 1,
617 X_CREATION_DATE => sysdate,
618 X_CREATED_BY => fnd_global.user_id,
619 X_LAST_UPDATE_DATE => sysdate,
620 X_LAST_UPDATED_BY => fnd_global.user_id,
621 X_LAST_UPDATE_LOGIN => fnd_global.login_id);
622
623 END LOOP;
624
625 END IF;
626
627 PA_DEBUG.Reset_Err_Stack;
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 ROLLBACK TO copy_object_rule_assoc;
632 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PERF_EXCP_UTILS.copy_object_rule_assoc',
633 p_procedure_name => PA_DEBUG.G_Err_Stack );
634
635 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
636
637 END copy_object_rule_assoc;
638
639
640 Procedure delete_object_exceptions
641 ( p_object_type in varchar2
642 ,p_object_id in number
643 , x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
644 , x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
645 , x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
646
647 BEGIN
648
649 PA_DEBUG.init_err_stack('PA_PERF_EXCP_UTILS.delete_object_exceptions');
650 x_return_status:=fnd_api.g_ret_sts_success;
651
652 savepoint delete_object_exceptions;
653
654 delete pa_perf_object_rules
655 where object_type = p_object_type
656 and object_id = p_object_id;
657
658
659 delete pa_perf_kpa_trans
660 where perf_txn_id in (select perf_txn_id from pa_perf_transactions
661 where project_id = p_object_id)
662 and kpa_summary_det_id in (select kpa_summary_det_id from pa_perf_kpa_summary_det
663 where object_type = p_object_type
664 and object_id = p_object_id);
665
666 delete pa_perf_kpa_summary_det
667 where object_type = p_object_type
668 and object_id = p_object_id;
669
670 delete pa_perf_kpa_summary
671 where object_type = p_object_type
672 and object_id = p_object_id;
673
674 delete pa_perf_comments
675 where perf_txn_id in (select perf_txn_id from pa_perf_transactions
676 where project_id = p_object_id);
677
678 delete pa_perf_transactions
679 where project_id = p_object_id;
680
681 PA_DEBUG.Reset_Err_Stack;
682
683 EXCEPTION
684 WHEN OTHERS THEN
685 ROLLBACK TO delete_object_exceptions;
686 FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_PERF_EXCP_UTILS.copy_object_rule_assoc',
687 p_procedure_name => PA_DEBUG.G_Err_Stack );
688
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
690
691 END delete_object_exceptions;
692
693
694 Procedure start_exception_engine
695 (
696 p_project_id in NUMBER DEFAULT NULL
700 ,p_purge IN VARCHAR2 DEFAULT 'N'
697 ,p_generate_exceptions IN VARCHAR2 DEFAULT 'Y'
698 ,p_generate_scoring IN VARCHAR2 DEFAULT 'Y'
699 ,p_generate_notification IN VARCHAR2 DEFAULT 'N'
701 ,p_daysold IN NUMBER DEFAULT NULL
702 ,x_request_id OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
703 ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
704 ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
705 ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
706 )
707 IS
708
709 v_err_msg VARCHAR2(1000);
710
711 BEGIN
712
713 x_return_Status := FND_API.g_ret_sts_success;
714
715
716 x_request_id := FND_REQUEST.submit_request
717 (application => 'PA',
718 program => 'PAPFEXCP',
719 description => '',
720 start_time => '',
721 sub_request => false,
722 argument1 => NULL,
723 argument2 => NULL,
724 argument3 => NULL,
725 argument4 => NULL,
726 argument5 => p_project_id,
727 argument6 => p_project_id,
728 argument7 => p_generate_exceptions,
729 argument8 => p_generate_scoring,
730 argument9 => p_generate_notification,
731 argument10 => p_purge,
732 argument11 => p_daysold
733 );
734
735
736 IF x_request_id = 0 then
737 IF P_PA_DEBUG_MODE = 'Y' THEN
738 PA_DEBUG.g_err_stage := 'Error while submitting Request [PAPFEXCP]';
739 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
740 END IF;
741 fnd_message.retrieve(v_err_msg);
742
743 PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
744 p_msg_name => 'PA_PERF_EXCP_UTILS');
745 x_return_status := FND_API.G_RET_STS_ERROR;
746
747 PA_UTILS.ADD_MESSAGE
748 (p_app_short_name => 'PA',
749 p_msg_name => 'PA_EXCP_REQUEST_FAILURE',
750 p_token1 => 'PA_SYS_ERR',
751 p_value1 => v_err_msg
752 );
753
754 ROLLBACK;
755 RETURN;
756 ELSE
757 COMMIT;
758
759 PA_UTILS.ADD_MESSAGE
760 (p_app_short_name => 'PA',
761 p_msg_name => 'PA_EXCP_REQUEST_SUCCESS',
762 p_token1 => 'REQUEST_ID',
763 p_value1 => x_request_id
764 );
765
766 END IF;
767
768
769 EXCEPTION
770 WHEN OTHERS THEN
771 FND_MSG_PUB.add_exc_msg(
772 p_pkg_name => 'PA_PERF_EXCP_UTILS.start_exception_engine'
773 ,p_procedure_name => PA_DEBUG.G_Err_Stack);
774
775 IF P_PA_DEBUG_MODE = 'Y' THEN
776 pa_debug.write_file('start_exception_engine: ' || SQLERRM);
777 END IF;
778 pa_debug.reset_err_stack;
779 RAISE;
780
781
782
783 END ;
784
785
786 END PA_PERF_EXCP_UTILS;