1 PACKAGE BODY pa_exception_engine_pkg AS
2 /* $Header: PAPEXENB.pls 120.5 2006/02/16 01:27:56 vgottimu noship $ */
3
4 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
5
6 -- Procedure PAPFEXCP
7 -- Purpose This procedure will be called from Concurrent Program to call
8 -- logic to generate exception transaction, KPA Scoring or
9 -- Notification based on the input parameters.
10
11 PROCEDURE PAPFEXCP ( x_errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
12 x_retcode OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
13 p_project_ou IN NUMBER DEFAULT NULL,
14 p_project_org IN NUMBER DEFAULT NULL,
15 p_project_type IN VARCHAR2 DEFAULT NULL,
16 p_project_manager IN NUMBER DEFAULT NULL,
17 p_project_from IN NUMBER DEFAULT NULL,
18 p_project_to IN NUMBER DEFAULT NULL,
19 p_generate_exceptions IN VARCHAR2 DEFAULT 'N',
20 p_generate_scoring IN VARCHAR2 DEFAULT 'N',
21 p_generate_notification IN VARCHAR2 DEFAULT 'N',
22 p_purge IN VARCHAR2 DEFAULT 'N',
23 p_daysold IN NUMBER DEFAULT NULL,
24 p_bz_event_code IN VARCHAR2 DEFAULT 'N',
25 p_perf_txn_set_id IN VARCHAR2 DEFAULT 'N') IS
26
27 l_project_list PA_PLSQL_DATATYPES.IdTabTyp;
28 l_bz_event_code pa_perf_bz_measures.bz_event_code%TYPE;
29 l_perf_txn_set_id pa_perf_transactions.perf_txn_id%TYPE;
30 l_proj NUMBER;
31 l_no_params_err_msg VARCHAR2(2000);
32
33 /* This cursor is for perf_txn_set_id is not null
34 Get the project_id from interface table based on business event and request_id */
35 CURSOR get_project_id(l_perf_txn_set_id IN VARCHAR2, l_proj_from IN NUMBER,
36 l_proj_to IN NUMBER, l_proj IN NUMBER) IS
37 SELECT DISTINCT object_id
38 FROM pa_perf_bz_object ppbo, pa_projects_all ppa
39 WHERE ppbo.object_type = 'PA_PROJECTS'
40 AND ppbo.perf_txn_set_id = l_perf_txn_set_id
41 AND ppa.segment1 between nvl((select segment1 from pa_projects_all where project_id = l_proj_from),' ') and
42 nvl((select segment1 from pa_projects_all where project_id = l_proj_to),ppa.segment1)
43 AND ppa.project_id = ppbo.object_id
44 ORDER BY object_id;
45
46 /* This cursor is for p_project_manager not null */
47 CURSOR get_project_id_p (l_project_manager IN NUMBER,
48 l_project_ou IN NUMBER,
49 l_project_org IN NUMBER,
50 l_proj_type VARCHAR2,
51 l_proj_from IN NUMBER,
52 l_proj_to IN NUMBER,
53 l_proj IN NUMBER) IS
54 SELECT DISTINCT ppa.project_id
55 FROM pa_projects_all ppa,
56 pa_project_parties ppp,
57 pa_project_types_all ppt,
58 pa_project_statuses pps -- Added for Bug 4338924
59 WHERE ppa.project_id = ppp.project_id
60 AND ppp.object_type = 'PA_PROJECTS'
61 AND ppa.project_status_code = pps.project_status_code -- Added for Bug 4338924
62 AND pps.status_type = 'PROJECT' -- Added for Bug 4338924
63 AND pps.project_system_status_code NOT IN ('CLOSED', 'PURGED') -- Added for Bug 4338924
64 AND ppa.segment1 BETWEEN NVL((select segment1 from pa_projects_all where project_id = l_proj_from),' ') AND
65 NVL((select segment1 from pa_projects_all where project_id = l_proj_to), ppa.segment1)
66 AND ppa.project_id = ppp.project_id
67 AND ppp.resource_source_id = nvl(l_project_manager,ppp.resource_source_id)
68 AND ppa.carrying_out_organization_id = nvl(l_project_ou, ppa.carrying_out_organization_id)
69 AND ppa.org_id = nvl(l_project_org, ppa.org_id)
70 AND ppa.project_type = nvl(l_proj_type, ppt.project_type)
71 AND ppt.org_id = ppa.org_id
72 AND ppp.project_role_id = 1 -- Added for Bug 4338924
73 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(ppp.start_date_active, SYSDATE)) AND TRUNC(NVL(ppp.end_date_active, SYSDATE)) -- Added for Bug 4338924
74 ORDER BY ppa.project_id;
75
76 /* This cursor is for p_project_manager null */
77 CURSOR get_project_id_pp (l_project_ou IN NUMBER,
78 l_project_org IN NUMBER,
79 l_proj_type IN VARCHAR2,
80 l_proj_from IN NUMBER,
81 l_proj_to IN NUMBER,
82 l_proj IN NUMBER) IS
83 SELECT distinct ppa.project_id
84 FROM pa_projects_all ppa,
85 pa_project_types_all ppt,
86 pa_project_statuses pps -- Added for Bug 4338924
87 WHERE ppa.carrying_out_organization_id = nvl(l_project_ou, ppa.carrying_out_organization_id)
88 AND ppa.org_id = nvl(l_project_org, ppa.org_id)
89 AND ppa.project_status_code = pps.project_status_code -- Added for Bug 4338924
90 AND pps.status_type = 'PROJECT' -- Added for Bug 4338924
91 AND pps.project_system_status_code NOT IN ('CLOSED', 'PURGED') -- Added for Bug 4338924
92 AND ppa.segment1 between nvl((select segment1 from pa_projects_all where project_id = l_proj_from),' ') and
93 nvl((select segment1 from pa_projects_all where project_id = l_proj_to), ppa.segment1)
94 AND ppa.project_type = nvl(l_proj_type, ppt.project_type)
95 and ppt.org_id = ppa.org_id
96 ORDER BY ppa.project_id;
97
98 l_errbuf VARCHAR2(500);
99 l_retcode VARCHAR2(100);
100
101 BEGIN
102
103 x_retcode := '1';
104 x_errbuf := null;
105
106 pa_debug.init_err_stack('PAPFEXCP');
107 pa_debug.set_process('PLSQL','LOG','Y');
108 pa_debug.G_err_stage := 'Entering PAPFEXCP ()';
109 IF P_PA_DEBUG_MODE = 'Y' THEN
110 pa_debug.write_file('LOG', pa_debug.g_err_stage);
111 END IF;
112 pa_debug.g_err_stage := ' Current system time is '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
113 IF P_PA_DEBUG_MODE = 'Y' THEN
114 pa_debug.write_file('LOG', pa_debug.g_err_stage);
115 END IF;
116
117 /* Write the Parameters passed in */
118 IF P_PA_DEBUG_MODE = 'Y' THEN
119 PA_DEBUG.write_file('LOG', 'Parameters passed ');
120 PA_DEBUG.write_file('LOG', 'Project OU => '||p_project_ou);
121 PA_DEBUG.write_file('LOG', 'Project Org => '||p_project_org);
122 PA_DEBUG.write_file('LOG', 'Project Type => '||p_project_type);
123 PA_DEBUG.write_file('LOG', 'Project Manager => '||p_project_manager);
124 PA_DEBUG.write_file('LOG', 'Project From => '||p_project_from);
125 PA_DEBUG.write_file('LOG', 'Project To => '||p_project_to);
126 PA_DEBUG.write_file('LOG', 'Exception Flag => '||p_generate_exceptions);
127 PA_DEBUG.write_file('LOG', 'KPA Scoring Flag => '||p_generate_scoring);
128 PA_DEBUG.write_file('LOG', 'Notification Flag => '||p_generate_notification);
129 PA_DEBUG.write_file('LOG', 'Purge Flag => '||p_purge);
130 END IF;
131
132 /* Check the parameters. If none of the parameters are passed in then do not
133 proceed on processing else continue */
134 IF ( p_project_ou is NULL AND
135 p_project_org is NULL AND
136 p_project_type is NULL AND
137 p_project_manager is NULL AND
138 p_project_from is NULL AND
139 p_project_to is NULL ) THEN
140
141 IF P_PA_DEBUG_MODE = 'Y' THEN -- Added for Bug 4324724
142 PA_DEBUG.write_file('LOG', 'No parameters passed in () Exception Engine will not continue');
143 END IF;
144
145 RAISE FND_API.G_EXC_ERROR; -- For Bug 4324724
146
147 ELSE
148 /*** Begin business logic to get the list of Projects ***/
149
150 IF p_perf_txn_set_id <> 'N' THEN
151 OPEN get_project_id (p_perf_txn_set_id,p_project_from, p_project_to, l_proj);
152 FETCH get_project_id bulk collect INTO l_project_list;
153 CLOSE get_project_id;
154
155 SELECT bz_ent_code
156 INTO l_bz_event_code
157 FROM pa_perf_bz_object
158 WHERE perf_txn_set_id = p_perf_txn_set_id
159 and rownum = 1;
160
161 ELSE
162 IF p_project_manager is NOT NULL THEN
163
164 OPEN get_project_id_p (p_project_manager, p_project_ou,p_project_org, p_project_type,
165 p_project_from, p_project_to, l_proj);
166 FETCH get_project_id_p bulk collect INTO l_project_list;
167 CLOSE get_project_id_p;
168
169 ELSE
170
171 OPEN get_project_id_pp (p_project_ou, p_project_org, p_project_type,p_project_from, p_project_to, l_proj);
172 FETCH get_project_id_pp bulk collect INTO l_project_list;
173 CLOSE get_project_id_pp;
174 END IF;
175
176 l_bz_event_code := 'N';
177
178 END IF;
179
180 -- Do not proceed if there are no projects selected
181 IF P_PA_DEBUG_MODE = 'Y' THEN
182 pa_debug.write_file('LOG', 'Project count selected : '||l_project_list.COUNT);
183 END IF;
184
185 IF l_project_list.COUNT <> 0 THEN
186 /*** End business logic to get the list of Projects ***/
187 IF ( NVL(p_generate_exceptions,'N') = 'Y' ) THEN
188
189 -- CALL EXCEPTION GENERATION LOGIC --
190 PA_EXCEPTION_ENGINE_PKG.generate_exception(
191 p_project_list => l_project_list,
192 p_business_event_code => l_bz_event_code,
193 x_errbuf => l_errbuf,
194 x_retcode => l_retcode
195 );
196 IF l_retcode = '0' THEN
197 IF P_PA_DEBUG_MODE = 'Y' THEN
198 PA_DEBUG.g_err_stage := 'After calling Generate Exceptions API . . returns error: '||l_errbuf;
199 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
200 RETURN;
201 END IF;
202 END IF;
203 END IF;
204
205 IF (NVL(p_generate_scoring,'N') = 'Y' ) THEN
206
207 -- CALL KPA SCORING LOGIC --
208 PA_EXCEPTION_ENGINE_PKG.get_kpa_score(
209 p_project_list => l_project_list,
210 x_errbuf => l_errbuf,
211 x_retcode => l_retcode
212 );
213
214 IF l_retcode = '0' THEN
215 IF P_PA_DEBUG_MODE = 'Y' THEN
216 PA_DEBUG.g_err_stage := 'After calling Generate KPA Scoring API . . returns error: '||l_errbuf;
217 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
218 RETURN;
219 END IF;
220 END IF;
221 END IF;
222
223 IF ( NVL(p_generate_notification,'N') = 'Y' ) THEN
224
225 -- CALL GENERATE NOTIFICATION LOGIC --
226 PA_EXCEPTION_ENGINE_PKG.generate_notification(
227 p_project_list => l_project_list,
228 x_errbuf => l_errbuf,
229 x_retcode => l_retcode
230 );
231
232 IF l_retcode = '0' THEN
233 IF P_PA_DEBUG_MODE = 'Y' THEN
234 PA_DEBUG.g_err_stage := 'After calling Generate Notification Logic API . . returns error: '||l_errbuf;
235 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
236 END IF;
237 END IF;
238
239 END IF;
240
241 IF ( NVL(p_purge,'N') = 'Y' ) THEN
242 IF p_daysold is NULL THEN
243 pa_debug.write_file('LOG', 'Days Old not passed in () Purge Logic API will not continue');
244 ELSE
245
246 -- CALL PURGE TRANSACTION LOGIC --
247 PA_EXCEPTION_ENGINE_PKG.purge_transaction(
248 p_project_list => l_project_list,
249 p_days_old => p_daysold,
250 x_errbuf => l_errbuf,
251 x_retcode => l_retcode
252 );
253
254 IF l_retcode = '0' THEN
255 IF P_PA_DEBUG_MODE = 'Y' THEN
256 PA_DEBUG.g_err_stage := 'After calling Purge Transaction Logic API . . returns error: '||l_errbuf;
257 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
258 END IF;
259 END IF;
260 END IF;
261 END IF;
262 ELSE
263 pa_debug.write_file('LOG', 'No valid project to be processed.');
264 END IF; --end if for Project count 0
265
266 END IF; --end if for No parameters passed in
267
268 x_retcode := '0';
269
270 pa_debug.G_err_stage := 'Leaving PAPFEXCP () with success';
271 IF P_PA_DEBUG_MODE = 'Y' THEN
272 pa_debug.write_file('LOG', pa_debug.g_err_stage);
273 END IF;
274 pa_debug.reset_err_stack;
275
276
277 EXCEPTION
278
279 WHEN NO_DATA_FOUND THEN
280
281 pa_debug.write_file('LOG', 'PAPFEXCP () exception: No data found');
282 pa_debug.write_file('LOG', pa_debug.g_err_stack);
283 pa_debug.write_file('LOG', pa_debug.g_err_stage);
284
285 WHEN FND_API.G_EXC_ERROR THEN -- Added for Bug 4324724
286
287 FND_MESSAGE.SET_NAME ('PA', 'PA_EXCP_NO_PARAMS_PASSED'); -- Set the translatable message name.
288 l_no_params_err_msg := FND_MESSAGE.GET; -- Get the error message.
289 x_errbuf := x_errbuf || 'errbuf: ' || l_no_params_err_msg; -- Set it in errbuf.
290 PA_DEBUG.write_file('LOG', l_no_params_err_msg); -- Write the error message to the log file.
291 RAISE; -- Pass on the exception to the calling API.
292
293 WHEN OTHERS THEN
294
295 x_errbuf := x_errbuf||'errbuf: '||sqlerrm;
296 pa_debug.write_file('LOG', 'PAPFEXCP () exception: Others');
297 pa_debug.write_file('LOG', pa_debug.g_err_stack);
298 pa_debug.write_file('LOG', pa_debug.g_err_stage);
299
300 END PAPFEXCP;
301
302 -- Procedure generate_exception
303 -- Purpose This procedure will be called by concurrent program.
304 -- Once running, it will generate the performance transactions
305
306 PROCEDURE generate_exception(p_project_list IN PA_PLSQL_DATATYPES.IdTabTyp,
307 p_business_event_code IN VARCHAR2,
308 x_errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
309 x_retcode OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
310
311 l_measure_id pa_perf_rules.measure_id%TYPE;
312 l_rule_id pa_perf_object_rules.rule_id%TYPE;
313 l_object_type pa_perf_object_rules.object_type%TYPE;
314 l_period_type pa_perf_rules.period_type%TYPE;
315 l_currency_type pa_perf_rules.currency_type%TYPE;
316 l_period_name pa_perf_transactions.period_name%TYPE;
317
318 l_object_list PA_PLSQL_DATATYPES.IdTabTyp;
319 l_rule_list PA_PLSQL_DATATYPES.IdTabTyp;
320 l_measure_list PA_PLSQL_DATATYPES.IdTabTyp;
321 l_bz_code_list PA_PLSQL_DATATYPES.Char30TabTyp;
322 l_program_name VARCHAR2(100);
323
324 l_object_rule_id pa_perf_object_rules.object_rule_id%TYPE;
325 l_kpa_code pa_perf_rules.kpa_code%TYPE;
326 l_precision pa_perf_rules.precision%TYPE;
327 l_measure_format pa_perf_rules.measure_format%TYPE;
328 l_rule_type pa_perf_rules.rule_type%TYPE;
329 l_thres_from pa_perf_thresholds.from_value%TYPE;
330 l_thres_to pa_perf_thresholds.to_value%TYPE;
331 l_threshold_id pa_perf_thresholds.threshold_id%TYPE;
332 l_indicator_code pa_perf_thresholds.indicator_code%TYPE;
333 l_weighting pa_perf_thresholds.weighting%TYPE;
334 l_exception_flag pa_perf_thresholds.exception_flag%TYPE;
335
336 l_measure_value NUMBER;
337
338
339
340 l_bz_ent_code pa_perf_bz_measures.bz_event_code%TYPE;
341 l_count NUMBER;
342
343 l_cursor INTEGER;
344 l_rows INTEGER;
345 l_stmt VARCHAR2(2000);
346
347 l_return_status VARCHAR2(30) := 'S';
348 l_msg_count NUMBER := NULL;
349 l_msg_data VARCHAR2(4000) := 'SUCCESS';
350
351 l_errbuf VARCHAR2(500);
352 l_retcode VARCHAR2(100);
353
354 /* Get all the project_id with rule_id and store it in PLSQL table */
355 CURSOR get_proj_rule_id (l_proj_id IN NUMBER) IS
356 SELECT distinct(ppor.object_id)
357 FROM pa_perf_object_rules ppor, pa_perf_rules ppr, pa_lookups pl -- Bug 4275320: Added pa_lookups
358 WHERE ppor.object_id = l_proj_id
359 AND ppor.rule_id is not null
360 AND ppor.object_type = 'PA_PROJECTS'
361 AND ppor.rule_id = ppr.rule_id
362 AND ppr.rule_type = 'PERF_RULE'
363 AND pl.lookup_code (+) = ppr.kpa_code -- For Bug 4275320
364 AND pl.lookup_type (+) = 'PA_PERF_KEY_AREAS' --Bug 4958325. Added look up type outer join, See the Bug for more details.
365 AND trunc(sysdate) between trunc(nvl(pl.start_date_active,sysdate)) and trunc(nvl(pl.end_date_active,sysdate)); -- For Bug 4275320
366
367 /* Get all the rule_id associated with the project_id */
368 CURSOR get_rule_id (l_proj_id IN NUMBER) IS
369 SELECT ppor.rule_id
370 FROM pa_perf_object_rules ppor, pa_perf_rules ppr, pa_lookups pl -- Bug 4275320: Added pa_lookups
371 WHERE ppor.object_id = l_proj_id
372 AND ppor.object_type = 'PA_PROJECTS'
373 AND ppor.rule_id = ppr.rule_id
374 AND ppr.rule_type = 'PERF_RULE'
375 AND pl.lookup_code (+) = ppr.kpa_code -- For Bug 4275320
376 AND pl.lookup_type (+) = 'PA_PERF_KEY_AREAS' --Bug 4958325. Added look up type outer join, See the Bug for more details.
377 AND trunc(sysdate) between trunc(nvl(pl.start_date_active,sysdate)) and trunc(nvl(pl.end_date_active,sysdate)); -- For Bug 4275320
378
379 /* Get the measure_id for a given project_id and rule_id */
380 CURSOR get_measures (l_proj_id IN NUMBER, l_rule_id IN NUMBER) IS
381 SELECT ppr.measure_id, ppr.period_type,
382 ppr.currency_type, ppor.object_type
383 FROM pa_perf_object_rules ppor,
384 pa_perf_rules ppr
385 WHERE ppor.object_type = 'PA_PROJECTS'
386 AND ppor.object_id = l_proj_id
387 AND ppor.rule_id = l_rule_id
388 AND ppr.rule_id = ppor.rule_id
389 AND ppr.rule_type = 'PERF_RULE';
390
391 /* Get the rule id from global temporary table for a given object_id */
392 CURSOR get_rule_id_tmp (l_proj_id IN NUMBER,
393 l_measure_id IN NUMBER) IS
394 SELECT rule_id
395 FROM pa_perf_temp_obj_measure
396 WHERE object_id = l_proj_id
397 AND object_type = 'PA_PROJECTS'
398 AND measure_id = l_measure_id;
399
400 /* Get the measure id from global temporary table for a given object_id */
401 CURSOR get_measure_id (l_object_id IN NUMBER) IS
402 SELECT DISTINCT measure_id
403 FROM pa_perf_temp_obj_measure
404 WHERE object_type = 'PA_PROJECTS'
405 AND object_id = l_object_id;
406
407 /* Get the object id from global temporary table */
408 CURSOR get_object_id IS
409 SELECT DISTINCT object_id
410 FROM pa_perf_temp_obj_measure
411 WHERE object_type = 'PA_PROJECTS'
412 ;
413
414 /* Get the list of Business Event code from temporary table */
415 CURSOR bz_ent_code IS
416 SELECT distinct bz_ent_code
417 FROM pa_perf_temp_obj_measure
418 WHERE object_type = 'PA_PROJECTS'
419 AND bz_ent_code is not null;
420
421 BEGIN
422 x_retcode := '0';
423
424 pa_debug.G_err_stage := 'Entering GENERATE_EXCEPTION ()';
425 IF P_PA_DEBUG_MODE = 'Y' THEN
426 pa_debug.write_file('LOG', pa_debug.g_err_stage);
427 END IF;
428 pa_debug.g_err_stage := ' Current system time is '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
429 IF P_PA_DEBUG_MODE = 'Y' THEN
430 pa_debug.write_file('LOG', pa_debug.g_err_stage);
431 END IF;
432
433 -- Remove the data in temp table
434 EXECUTE IMMEDIATE ('delete from pa_perf_temp_obj_measure') ;
435
436
437 /*** Begin business logic to generate the Performance Transaction ***/
438
439 -- Step 1: Populate the temp table and prepare to get the measure value
440 IF P_PA_DEBUG_MODE = 'Y' THEN
441 PA_DEBUG.write_file('LOG', 'Number of Projects to be processed is : '||p_project_list.COUNT);
442 END IF;
443 FOR i IN p_project_list.FIRST .. p_project_list.LAST LOOP
444 -- Step 1: Populate PA_PERF_TEMP_OBJ_MEASURE (temp table)
445 -- get all the rules attached to the project
446
447 OPEN get_rule_id (p_project_list(i));
448 FETCH get_rule_id bulk collect INTO l_rule_list;
449 CLOSE get_rule_id;
450
451 IF l_rule_list.COUNT <> 0 THEN
452
453 IF P_PA_DEBUG_MODE = 'Y' THEN
454 PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_TEMP_OBJ_MEASURE temp table for Project: '||p_project_list(i));
455 PA_DEBUG.write_file('LOG', 'Number of Rule IDs : '||l_rule_list.COUNT);
456 END IF;
457
458 FOR j IN l_rule_list.FIRST .. l_rule_list.LAST LOOP
459
460 -- Process only those projects with rule id
461
462 OPEN get_measures (p_project_list(i), l_rule_list(j));
463 FETCH get_measures INTO l_measure_id, l_period_type,
464 l_currency_type, l_object_type;
465 CLOSE get_measures;
466
467 IF l_measure_id is NULL THEN
468 PA_DEBUG.write_file('LOG', 'Rule ID '||l_rule_list(j)||' has no associated measure id');
469 ELSE
470 --- If bz_ent_code is not passed in, get one bz_ent_code
471 --- from pa_perf_bz_measures for the given measure id
472 IF (p_business_event_code = 'N') THEN
473 SELECT count(*) INTO l_count FROM dual where EXISTS
474 (SELECT bz_event_code
475 FROM pa_perf_bz_measures
476 WHERE measure_id = l_measure_id);
477 IF l_count = 0 THEN
478 --NULL;
479 l_bz_ent_code := null;
480 ELSE
481 SELECT bz_event_code
482 INTO l_bz_ent_code
483 FROM pa_perf_bz_measures
484 WHERE measure_id = l_measure_id
485 AND rownum = 1;
486 END IF;
487
488 INSERT INTO pa_perf_temp_obj_measure
489 ( object_type
490 ,object_id
491 ,measure_id
492 ,measure_value
493 ,rule_id
494 ,calendar_type
495 ,currency_type
496 ,period_name
497 ,bz_ent_code
498 )
499 VALUES
500 ('PA_PROJECTS'
501 ,p_project_list(i)
502 ,l_measure_id
503 ,null
504 ,l_rule_list(j)
505 ,l_period_type
506 ,l_currency_type
507 ,null
508 ,l_bz_ent_code
509 );
510 ELSE
511
512 l_bz_ent_code := p_business_event_code;
513
514 INSERT INTO pa_perf_temp_obj_measure
515 ( object_type
516 ,object_id
517 ,measure_id
518 ,measure_value
519 ,rule_id
520 ,calendar_type
521 ,currency_type
522 ,period_name
523 ,bz_ent_code
524 )
525 VALUES
526 ('PA_PROJECTS'
527 ,p_project_list(i)
528 ,l_measure_id
529 ,null
530 ,l_rule_list(j)
531 ,l_period_type
532 ,l_currency_type
533 ,null
534 ,l_bz_ent_code
535 );
536 END IF;
537
538 -- End of Step 1
539 END IF; -- end if for l_measure id null
540 END LOOP; -- end loop for l_rule_list
541 ELSE
542 --NULL;
543 --Added for bug# 3918182
544 IF P_PA_DEBUG_MODE = 'Y' THEN
545 PA_DEBUG.write_file('LOG', 'No performance rules are associated to the project ');
546 END IF;
547 -- Since there are no performance rules associated to the project there should not be
548 -- any current transaction record for the project. So marking all the transaction for the
549 -- project as History
550 UPDATE pa_perf_transactions
551 SET current_flag = 'N'
552 WHERE perf_txn_obj_type = 'PA_PROJECTS'
553 AND perf_txn_obj_id = p_project_list(i)
554 AND current_flag = 'Y';
555
556 END IF; --end of l_rule_list.COUNT
557 END LOOP; --end loop for l_project_list
558
559 commit; --Save the work after inserting all the projects into temp table
560
561 IF P_PA_DEBUG_MODE = 'Y' THEN
562 PA_DEBUG.write_file('LOG', 'End of Step 1 .. Inserting into PA_PERF_TEMP_OBJ_MEASURE temp table');
563 END IF;
564
565 --- Step 2: Call Business Event API to get the value of measure_id
566 --- for a given project_id. This API will do the update on the temp
567 --- table to set the measure value in bulk.
568
569 --- get the API for a given business event--
570 OPEN bz_ent_code;
571 FETCH bz_ent_code bulk collect INTO l_bz_code_list;
572 CLOSE bz_ent_code;
573
574 IF l_bz_code_list.COUNT <> 0 THEN
575
576 FOR i IN l_bz_code_list.FIRST .. l_bz_code_list.LAST LOOP
577 SELECT attribute1
578 INTO l_program_name
579 FROM pa_lookups
580 WHERE lookup_type = 'PA_PERF_BZ_EVENTS'
581 AND lookup_code = l_bz_code_list(i)
582 AND enabled_flag = 'Y';
583
584 IF ( l_program_name IS NOT NULL ) THEN
585 -- Construct the dynamic SQL that will call the API from PA_LOOKUPS
586 -- for PA_PERF_BZ_EVENTS lookup_type --
587 -- l_program_name is the name of the extension API.
588 -- For instance 'SUMMARIZATION.EXCEPTION'
589
590 IF P_PA_DEBUG_MODE = 'Y' THEN
591 PA_DEBUG.g_err_stage := 'Executing Dynamic SQL to call API that will set the measure value.';
592 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
593 END IF;
594
595 BEGIN
596
597 l_return_status := 'S';
598
599 -- Get cursor handle
600 l_cursor := dbms_sql.open_cursor;
601
602 -- Associate a sql statement with the cursor.
603
604 l_stmt := 'BEGIN '||l_program_name ||
605 '(p_commit_flag => :Y,'||
606 ' x_msg_count => :msg_count,'||
607 ' x_msg_data => :msg_data,'||
608 ' x_return_status => :return_status);'||
609 ' END;';
610
611 -- parse the sql statemnt to check for any syntax or symantic errors
612
613 dbms_sql.parse(l_cursor,l_stmt,dbms_sql.native);
614
615 -- before executing the sql statement bind the variables
616
617 dbms_sql.bind_variable(l_cursor,':Y',
618 'Y');
619 dbms_sql.bind_variable(l_cursor,':msg_count',
620 l_msg_count);
621 dbms_sql.bind_variable(l_cursor,':msg_data',
622 l_msg_data);
623 dbms_sql.bind_variable(l_cursor,':return_status',
624 l_return_status);
625 -- execute the statement
626 l_rows := dbms_sql.execute(l_cursor);
627
628 -- retrieve the values for the output variables
629 dbms_sql.variable_value(l_cursor, ':msg_count', l_msg_count);
630 dbms_sql.variable_value(l_cursor, ':msg_data', l_msg_data);
631 dbms_sql.variable_value(l_cursor, ':return_status', l_return_status);
632
633 IF ( l_return_status <> 'S') THEN
634 IF P_PA_DEBUG_MODE = 'Y' THEN
635 PA_DEBUG.g_err_stage := 'After executing Dynamic SQL () with error';
636 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
637 END IF;
638 ELSE
639 IF P_PA_DEBUG_MODE = 'Y' THEN
640 PA_DEBUG.g_err_stage := 'After executing Dynamic SQL () with success';
641 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
642 END IF;
643 dbms_sql.close_cursor(l_cursor);
644 END IF;
645
646 EXCEPTION
647 WHEN others THEN
648 dbms_sql.close_cursor(l_cursor);
649 pa_debug.write_file('LOG', 'Error executing Dynamic SQL () exception: Others '||sqlerrm);
650 END;
651 IF P_PA_DEBUG_MODE = 'Y' THEN
652 PA_DEBUG.write_file('LOG', 'End of Step 2 . . Called Business Event API to get the measure value');
653 END IF;
654 -- till here for dynamic SQL
655 END IF;
656
657 END LOOP; --end loop for l_bz_code_list
658 ELSE
659 IF P_PA_DEBUG_MODE = 'Y' THEN -- Added for Bug 4324824
660 PA_DEBUG.write_file('LOG', 'No Business Event code selected from PA_PERF_TEMP_OBJ_MEASURE temp table.');
661 END IF;
662 END IF;
663 -- End of Step 2
664
665 -- Step 3: Generate the Performance Transaction --
666
667 OPEN get_object_id ;
668 FETCH get_object_id bulk collect INTO l_object_list;
669 CLOSE get_object_id;
670
671 IF P_PA_DEBUG_MODE = 'Y' THEN
672 PA_DEBUG.write_file('LOG', 'Number of Object IDs to be inserted into transaction table : '||l_object_list.COUNT);
673 END IF;
674 IF l_object_list.COUNT <> 0 THEN
675 FOR i IN l_object_list.FIRST .. l_object_list.LAST LOOP
676
677 IF P_PA_DEBUG_MODE = 'Y' THEN
678 PA_DEBUG.write_file('LOG', 'Project ID : '||l_object_list(i));
679 END IF;
680 OPEN get_measure_id (l_object_list(i));
681 FETCH get_measure_id bulk collect INTO l_measure_list;
682 CLOSE get_measure_id;
683 /* Marking the transaction to be not current */
684 -- IF business event is not passed in, mark to be not current all
685 -- the transaction for a given project
686 IF p_business_event_code = 'N' THEN
687 UPDATE pa_perf_transactions
688 SET current_flag = 'N'
689 -- WHERE project_id = l_object_list(i) --Modified for Bug 3639490
690 WHERE perf_txn_obj_type = 'PA_PROJECTS'
691 AND perf_txn_obj_id = l_object_list(i)
692 AND current_flag = 'Y';
693
694 -- ELSE if business event is passed in, mark to be not current only the transactions with
695 -- measure_id associated with the business event
696 ELSE
697 --Check if there are records to be updated
698 SELECT count(*) INTO l_count FROM dual where EXISTS
699 (SELECT project_id
700 FROM pa_perf_transactions
701 --WHERE project_id = l_object_list(i) --Modified for Bug3639490
702 WHERE perf_txn_obj_type = 'PA_PROJECTS'
703 AND perf_txn_obj_id = l_object_list(i)
704 AND measure_id in (SELECT measure_id
705 FROM pa_perf_bz_measures
706 WHERE bz_event_code = p_business_event_code)
707 AND current_flag = 'Y');
708 IF l_count = 0 THEN
709 null;
710 ELSE
711 UPDATE pa_perf_transactions
712 SET current_flag = 'N'
713 --WHERE project_id = l_object_list(i) --Modified for bug 3639490
714 WHERE perf_txn_obj_type = 'PA_PROJECTS'
715 AND perf_txn_obj_id = l_object_list(i)
716 AND measure_id in (SELECT measure_id
717 FROM pa_perf_bz_measures
718 WHERE bz_event_code = p_business_event_code)
719 AND current_flag = 'Y';
720 END IF;
721
722 END IF;
723 /* till here for marking the transaction to be not current */
724
725 IF P_PA_DEBUG_MODE = 'Y' THEN
726 PA_DEBUG.write_file('LOG', 'Number of Measure ID : '||l_measure_list.COUNT);
727 END IF;
728 FOR j IN l_measure_list.FIRST .. l_measure_list.LAST LOOP
729 -- Get all the rule_id associated with the given measure_id
730 IF P_PA_DEBUG_MODE = 'Y' THEN
731 PA_DEBUG.write_file('LOG', 'Measure ID : '||l_measure_list(j));
732 END IF;
733 OPEN get_rule_id_tmp (l_object_list(i), l_measure_list(j));
734 FETCH get_rule_id_tmp bulk collect INTO l_rule_list;
735 CLOSE get_rule_id_tmp;
736
737 FOR k IN l_rule_list.FIRST .. l_rule_list.LAST LOOP
738
739 IF P_PA_DEBUG_MODE = 'Y' THEN
740 PA_DEBUG.write_file('LOG', 'Rule ID : '||l_rule_list(k));
741 END IF;
742 -- Get the calendar_type associated with the rule_id and measure_id
743 SELECT calendar_type
744 INTO l_period_type
745 FROM pa_perf_temp_obj_measure
746 WHERE object_id = l_object_list(i)
747 AND object_type = 'PA_PROJECTS'
748 AND measure_id = l_measure_list(j)
749 AND rule_id = l_rule_list(k);
750
751 IF P_PA_DEBUG_MODE = 'Y' THEN
752 PA_DEBUG.write_file('LOG', 'Period Type : '||l_period_type);
753 END IF;
754
755 -- Get the measure_value, period_type, period_name and rule_id
756 -- for a given project_id and measure_id
757
758 SELECT nvl(pptom.measure_value, null), nvl(pptom.period_name,null),
759 pptom.rule_id, pptom.object_type, ppor.object_rule_id,
760 ppr.kpa_code, ppr.precision, ppr.currency_type, ppr.measure_format,
761 ppr.rule_type
762 INTO l_measure_value, l_period_name,
763 l_rule_id, l_object_type, l_object_rule_id,
764 l_kpa_code, l_precision, l_currency_type, l_measure_format,
765 l_rule_type
766 FROM pa_perf_rules ppr,
767 pa_perf_object_rules ppor,
768 pa_perf_temp_obj_measure pptom
769 WHERE pptom.object_id = l_object_list(i)
770 AND pptom.object_id = ppor.object_id
771 AND ppor.object_type = 'PA_PROJECTS'
772 AND pptom.measure_id = l_measure_list(j)
773 AND pptom.object_type = 'PA_PROJECTS'
774 AND ppor.rule_id = l_rule_list(k)
775 AND ppor.rule_id = ppr.rule_id
776 AND ppor.rule_id = pptom.rule_id
777 AND rownum = 1;
778
779 IF P_PA_DEBUG_MODE = 'Y' THEN
780 PA_DEBUG.write_file('LOG', 'After selecting values from PA_PERF_TEMP_OBJ_MEASURE temp table' );
781 END IF;
782
783 -- If measure value is null for a given measure_id and there is associated API
784 -- then call the API to get the measure value
785 IF l_measure_value is NOT NULL THEN
786 NULL;
787 ELSE
788 SELECT pl_sql_api
789 INTO l_program_name
790 FROM pji_mt_measures_v
791 WHERE measure_id = l_measure_list(j)
792 AND rownum = 1 ;
793
794 IF ( l_program_name IS NOT NULL ) THEN
795 -- Construct the dynamic SQL that will call the API from PJI_MT_MEASURES_V
796 -- l_program_name is the name of the extension API.
797 -- For instance 'SUMMARIZATION.EXCEPTION'
798 IF P_PA_DEBUG_MODE = 'Y' THEN
799 PA_DEBUG.g_err_stage := 'Executing Dynamic SQL to call Measure Value API '||l_program_name;
800 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
801 END IF;
802 BEGIN
803
804 l_return_status := 'S';
805
806 -- Get cursor handle
807 l_cursor := dbms_sql.open_cursor;
808
809 -- Associate a sql statement with the cursor.
810
811 l_stmt := 'BEGIN '||l_program_name ||
812 '( p_object_type => :object_type,'||
813 ' p_object_id => :object_list,'||
814 ' p_measure_id => :measure_list,'||
815 ' p_period_type => :period_type,'||
816 ' x_measure_value => :measure_value,'||
817 ' x_period_name => :period_name,'||
818 ' x_return_status => :return_status,'||
819 ' x_msg_count => :msg_count,'||
820 ' x_msg_data => :msg_data);'||
821 ' END;';
822
823 -- parse the sql statemnt to check for any syntax or symantic errors
824
825 dbms_sql.parse(l_cursor,l_stmt,dbms_sql.native);
826
827 -- before executing the sql statement bind the variables
828
829 dbms_sql.bind_variable(l_cursor,':object_type',
830 'PA_PROJECTS');
831 dbms_sql.bind_variable(l_cursor,':object_list',
832 l_object_list(i));
833 dbms_sql.bind_variable(l_cursor,':measure_list',
834 l_measure_list(j));
835 dbms_sql.bind_variable(l_cursor,':period_type',
836 l_period_type);
837 dbms_sql.bind_variable(l_cursor,':measure_value',
838 l_measure_value);
839 dbms_sql.bind_variable(l_cursor,':period_name',
840 l_period_name);
841 dbms_sql.bind_variable(l_cursor,':return_status',
842 l_return_status);
843 dbms_sql.bind_variable(l_cursor,':msg_count',
844 l_msg_count);
845 dbms_sql.bind_variable(l_cursor,':msg_data',
846 l_msg_data);
847
848 -- execute the statement
849 l_rows := dbms_sql.execute(l_cursor);
850
851 -- retrieve the values for the output variables
852 dbms_sql.variable_value(l_cursor, ':measure_value', l_measure_value);
853 dbms_sql.variable_value(l_cursor, ':period_name', l_period_name);
854 dbms_sql.variable_value(l_cursor, ':return_status', l_return_status);
855 dbms_sql.variable_value(l_cursor, ':msg_count', l_msg_count);
856 dbms_sql.variable_value(l_cursor, ':msg_data', l_msg_data);
857
858 -- Check if the call to Get Measure Value API is successful
859 IF ( l_return_status <> 'S' ) THEN
860 IF P_PA_DEBUG_MODE = 'Y' THEN
861 PA_DEBUG.g_err_stage := 'After executing Dynamic SQL () with error';
862 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
863 END IF;
864 ELSE
865 IF P_PA_DEBUG_MODE = 'Y' THEN
866 PA_DEBUG.g_err_stage := 'After executing Dynamic SQL () with success';
867 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
868 END IF;
869
870 IF P_PA_DEBUG_MODE = 'Y' THEN
871 pa_debug.write_file('LOG','Period Name : '||l_period_name);
872 END IF;
873
874 IF l_measure_value is NOT NULL THEN
875 -- Update the measure value on pa_perf_temp_obj_measure table
876 UPDATE pa_perf_temp_obj_measure
877 SET measure_value = l_measure_value,
878 period_name = l_period_name
879 WHERE object_id = l_object_list(i)
880 AND measure_id = l_measure_list(j)
881 AND rule_id = l_rule_list(k)
882 AND object_type = 'PA_PROJECTS';
883 IF P_PA_DEBUG_MODE = 'Y' THEN
884 pa_debug.write_file('LOG','Updating PA_PERF_TEMP_OBJ_MEASURE () with success');
885 END IF;
886 END IF;
887 dbms_sql.close_cursor(l_cursor);
888 END IF;
889
890 EXCEPTION
891 WHEN others THEN
892 dbms_sql.close_cursor(l_cursor);
893 pa_debug.write_file('LOG','Error executing Dynamic SQL () exception: Others '||sqlerrm);
894 END;
895 -- till here for dynamic SQL --
896 ELSE
897 PA_DEBUG.write_file('LOG', 'No Measure Value API for measure id '||l_measure_list(j));
898 END IF; --end if for program name is not null
899 END IF; --end if for measure values is not null
900
901 SELECT measure_value
902 INTO l_measure_value
903 FROM pa_perf_temp_obj_measure
904 WHERE object_id = l_object_list(i)
905 AND measure_id = l_measure_list(j)
906 AND object_type = 'PA_PROJECTS'
907 AND rule_id = l_rule_list(k)
908 AND ROWNUM = 1;
909
910 IF P_PA_DEBUG_MODE = 'Y' THEN
911 pa_debug.write_file('LOG','Get Threshold for Measure Value : '||l_measure_value);
912 END IF;
913 -- Check whether the measure value is between the threshold from
914 -- and threshold to. If l_retcode = 1 then there is a match . . hence
915 -- inserting the record into pa_perf_transactions table ELSE do nothing
916 PA_EXCEPTION_ENGINE_PKG.GET_THRESHOLD(
917 l_rule_list(k)
918 ,l_rule_type
919 ,l_measure_value
920 ,l_threshold_id
921 ,l_indicator_code
922 ,l_exception_flag
923 ,l_weighting
924 ,l_thres_from
925 ,l_thres_to
926 ,l_errbuf
927 ,l_retcode);
928
929 IF l_retcode = '0' THEN
930 pa_debug.write_file('LOG','No matching threshold');
931 ELSE
932 -- Insert new record into PA_PERF_TRANSACTIONS table
933 IF P_PA_DEBUG_MODE = 'Y' THEN
934 pa_debug.write_file('LOG','Inserting record into PA_PERF_TRANSACTIONS');
935 END IF;
936 INSERT INTO pa_perf_transactions
937 ( perf_txn_id
938 ,perf_txn_obj_type
939 ,perf_txn_obj_id
940 ,object_rule_id
941 ,related_obj_type
942 ,related_obj_id
943 ,rule_id
944 ,project_id
945 ,kpa_code
946 ,measure_id
947 ,measure_value
948 ,period_name
949 ,indicator_code
950 ,threshold_from
951 ,threshold_to
952 ,weighting
953 ,precision
954 ,period_type
955 ,currency_type
956 ,measure_format
957 ,program_id
958 ,date_checked
959 ,exception_flag
960 ,current_flag
961 ,included_in_scoring
962 ,record_version_number
963 ,creation_date
964 ,created_by
965 ,last_update_date
966 ,last_updated_by
967 ,last_update_login
968 )
969 VALUES ( pa_perf_transactions_s1.nextval
970 ,'PA_PROJECTS'
971 ,l_object_list(i)
972 ,l_object_rule_id
973 ,null
974 ,null
975 ,l_rule_list(k)
976 ,l_object_list(i)
977 ,l_kpa_code
978 ,l_measure_list(j)
979 ,l_measure_value
980 ,l_period_name
981 ,l_indicator_code
982 ,l_thres_from
983 ,l_thres_to
984 ,l_weighting
985 ,l_precision
986 ,l_period_type
987 ,l_currency_type
988 ,l_measure_format
989 ,fnd_global.CONC_REQUEST_ID
990 ,sysdate
991 ,l_exception_flag
992 ,'Y'
993 ,'N'
994 ,1
995 ,sysdate
996 ,fnd_global.user_id
997 ,sysdate
998 ,fnd_global.user_id
999 ,fnd_global.login_id
1000 );
1001 END IF; --end if of l_retcode = 0
1002 END LOOP; --end loop for l_rule_list from temp table
1003 END LOOP; --end loop for l_measure_list
1004
1005 COMMIT; --Do the commit after each project
1006
1007 END LOOP; --end loop for l_object_list
1008 END IF; --end if for l_object_list.count is 0
1009
1010 IF P_PA_DEBUG_MODE = 'Y' THEN
1011 PA_DEBUG.write_file('LOG', 'End of Step 3 . . Inserting into PA_PERF_TRANSACTIONS table');
1012 END IF;
1013 -- End of Step 3 --
1014
1015 -- Remove the data in temp table
1016 -- EXECUTE IMMEDIATE ('delete from pa_perf_temp_obj_measure') ;
1017 IF P_PA_DEBUG_MODE = 'Y' THEN
1018 pa_debug.write_file('LOG','Deleting records from PA_PERF_TEMP_OBJ_MEASURE () with success');
1019 END IF;
1020
1021 -- Clean the Interface Table PA_PER_BZ_OBJECT --
1022
1023 IF p_business_event_code = 'N' THEN
1024 IF P_PA_DEBUG_MODE = 'Y' THEN -- Added for Bug 4324824
1025 pa_debug.write_file('LOG','No records will be deleted from PA_PERF_BZ_OBJECT since Business Event code is not passed in.');
1026 END IF;
1027 ELSE
1028 DELETE from pa_perf_bz_object
1029 WHERE bz_ent_code = p_business_event_code;
1030 IF P_PA_DEBUG_MODE = 'Y' THEN
1031 pa_debug.write_file('LOG','Deleting records from PA_PERF_BZ_OBJECT () with success');
1032 END IF;
1033 END IF;
1034
1035
1036 x_retcode := '1';
1037
1038 pa_debug.G_err_stage := 'Leaving GENERATE_EXCEPTION () with success';
1039 IF P_PA_DEBUG_MODE = 'Y' THEN
1040 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1041 END IF;
1042 pa_debug.reset_err_stack;
1043
1044 commit;
1045
1046
1047 EXCEPTION
1048
1049 WHEN NO_DATA_FOUND THEN
1050
1051 pa_debug.write_file('LOG', 'GENERATE_EXCEPTION () exception: No data found');
1052 pa_debug.write_file('LOG', pa_debug.g_err_stack);
1053 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1054
1055 WHEN OTHERS THEN
1056
1057 x_errbuf := x_errbuf||'errbuf: '||sqlerrm;
1058 pa_debug.write_file('LOG', 'GENERATE_EXCEPTION () exception: Others '||x_errbuf);
1059 pa_debug.write_file('LOG', pa_debug.g_err_stack);
1060 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1061
1062 END generate_exception;
1063
1064 -- Procedure generate_notification
1065 -- Purpose This procedure will be called by concurrent program.
1066 -- Once running, it will generate the workflow notification for each.
1067
1068 PROCEDURE generate_notification(
1069 p_project_list IN PA_PLSQL_DATATYPES.IdTabTyp,
1070 x_errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1071 x_retcode OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1072
1073 CURSOR get_page( c_object_id NUMBER)
1074 IS
1075 SELECT object_page_layout_id
1076 FROM pa_progress_report_setup_v
1077 WHERE object_id = c_object_id
1078 AND object_type = 'PA_PROJECTS'
1079 AND page_type_code='PPR'
1080 AND generation_method='AUTOMATIC';
1081
1082 l_project_list PA_PLSQL_DATATYPES.IdTabTyp;
1083 l_msg_count NUMBER := 0;
1084 l_data VARCHAR2(2000);
1085 l_msg_data VARCHAR2(2000);
1086 l_msg_index_out NUMBER;
1087 l_item_key VARCHAR2(2000);
1088 l_return_status VARCHAR2(1);
1089 l_number NUMBER;
1090
1091
1092 BEGIN
1093
1094 x_retcode := '0';
1095
1096 l_project_list := p_project_list;
1097
1098 pa_debug.G_err_stage := 'Entering GENERATE_NOTIFICATION ()';
1099 IF P_PA_DEBUG_MODE = 'Y' THEN
1100 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1101 END If;
1102 pa_debug.g_err_stage := ' Current system time is '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
1103 IF P_PA_DEBUG_MODE = 'Y' THEN
1104 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1105 END IF;
1106
1107
1108 /*** Begin Notification logic ***/
1109
1110 FOR i IN l_project_list.FIRST .. l_project_list.LAST LOOP
1111 -- Call workflow API to generate Workflow Notification for the given project.
1112 /* Check if any Page Layout is aatched to this project for Exception Reporting. If it is attached then
1113 Workflow will be started to send e-mail notification to all the member in the access list otherwise
1114 error message is logged in the concurrent request log and continue with next project.*/
1115 OPEN get_page( l_project_list(i));
1116 FETCH get_page INTO l_number;
1117 IF get_page%FOUND THEN
1118 PA_PERF_NOTIFICATION_PKG.START_PERF_NOTIFICATION_WF(
1119 p_item_type => 'PAEXNOWF'
1120 ,p_process_name => 'PERFORMANCE_NOTIFICATION_PROCE'
1121 ,p_project_id => l_project_list(i)
1122 ,x_item_key => l_item_key
1123 ,x_return_status => l_return_status
1124 ,x_msg_count => l_msg_count
1125 ,x_msg_data => l_msg_data);
1126
1127 IF (l_return_status <> 'S') THEN
1128 pa_debug.g_err_stage:= 'Error calling START_PERF_NOTIFICATION_WF';
1129 IF P_PA_DEBUG_MODE = 'Y' THEN
1130 pa_debug.write_file('LOG',pa_debug.g_err_stage);
1131 pa_debug.write_file('LOG','l_msg_data : '||l_msg_data);
1132 END IF;
1133
1134 PA_INTERFACE_UTILS_PUB.get_messages
1135 (p_encoded => FND_API.G_TRUE
1136 ,p_msg_index => 1
1137 ,p_msg_count => l_msg_count
1138 ,p_msg_data => l_msg_data
1139 ,p_data => l_data
1140 ,p_msg_index_out => l_msg_index_out);
1141
1142 IF P_PA_DEBUG_MODE = 'Y' THEN
1143 pa_debug.write_file('LOG','l_data : '||l_data);
1144 END IF;
1145
1146 END IF;
1147 ELSE
1148 pa_debug.write_file('LOG', ' There is no report type associated to the project( Project Id: '||l_project_list(i)||') to send the performance status notification ');
1149 END IF;
1150 CLOSE get_page;
1151 END LOOP;
1152
1153 x_retcode := '1';
1154
1155 pa_debug.G_err_stage := 'Leaving GENERATE_NOTIFICATION () with success';
1156 IF P_PA_DEBUG_MODE = 'Y' THEN
1157 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1158 END IF;
1159 pa_debug.reset_err_stack;
1160
1161 commit;
1162
1163
1164 EXCEPTION
1165
1166 WHEN NO_DATA_FOUND THEN
1167
1168 pa_debug.write_file('LOG', 'GENERATE_NOTIFICATION () exception: No data found');
1169 pa_debug.write_file('LOG', pa_debug.g_err_stack);
1170 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1171
1172 WHEN OTHERS THEN
1173
1174 x_errbuf := x_errbuf||'errbuf: '||sqlerrm;
1175 pa_debug.write_file('LOG', 'GENERATE_NOTIFICATION () exception: Others');
1176 pa_debug.write_file('LOG', pa_debug.g_err_stack);
1177 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1178
1179 END generate_notification;
1180
1181 -- Procedure purge_transaction
1182 -- Purpose This procedure will call logic to cleanup data in the
1183 -- PA_PERF_TRANSACTIONS table.
1184
1185 PROCEDURE purge_transaction( p_project_list IN PA_PLSQL_DATATYPES.IdTabTyp,
1186 p_days_old IN NUMBER,
1187 x_errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1188 x_retcode OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1189
1190 BEGIN
1191
1192 x_retcode := '0';
1193
1194
1195 pa_debug.init_err_stack('PURGE_TRANSACTION ');
1196 pa_debug.set_process('PLSQL','LOG','Y');
1197 pa_debug.G_err_stage := 'Entering PURGE_TRANSACTION ()';
1198 IF P_PA_DEBUG_MODE = 'Y' THEN
1199 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1200 END IF;
1201 pa_debug.g_err_stage := ' Current system time is '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
1202 IF P_PA_DEBUG_MODE = 'Y' THEN
1203 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1204 END IF;
1205
1206 FOR i IN p_project_list.FIRST .. p_project_list.LAST LOOP
1207
1208 IF P_PA_DEBUG_MODE = 'Y' THEN
1209 pa_debug.write_file('LOG', 'Purging Project : ' || p_project_list(i));
1210 END IF;
1211
1212 /*** clean up the comments transaction table based on project_id and purge_date ***/
1213 DELETE from pa_perf_comments
1214 WHERE perf_txn_id in (SELECT perf_txn_id
1215 FROM pa_perf_transactions
1216 WHERE perf_txn_obj_type = 'PA_PROJECTS'
1217 AND perf_txn_obj_id = p_project_list(i)
1218 AND trunc(creation_date) < trunc(sysdate - p_days_old)
1219 AND current_flag = 'N');
1220
1221 /*** clean up the the KPA Scoring Summary ***/
1222 DELETE from pa_perf_kpa_trans
1223 WHERE perf_txn_id in (SELECT perf_txn_id
1224 FROM pa_perf_transactions
1225 WHERE perf_txn_obj_type = 'PA_PROJECTS'
1226 AND perf_txn_obj_id = p_project_list(i)
1227 AND trunc(creation_date) < trunc(sysdate - p_days_old)
1228 AND current_flag = 'N');
1229
1230 DELETE from pa_perf_kpa_summary_det
1231 WHERE object_type = 'PA_PROJECTS'
1232 AND object_id = p_project_list(i)
1233 AND trunc(creation_date) < trunc(sysdate - p_days_old)
1234 AND kpa_summary_id in (SELECT kpa_summary_id
1235 FROM pa_perf_kpa_summary
1236 WHERE object_type = 'PA_PROJECTS'
1237 AND object_id = p_project_list(i)
1238 AND trunc(creation_date) < trunc(sysdate - p_days_old)
1239 AND current_flag = 'N');
1240
1241 DELETE from pa_perf_kpa_summary
1242 WHERE object_type = 'PA_PROJECTS'
1243 AND object_id = p_project_list(i)
1244 AND trunc(creation_date) < trunc(sysdate - p_days_old)
1245 AND current_flag = 'N';
1246
1247 /*** clean up the transaction table based on project_id and purge_date ***/
1248 DELETE from pa_perf_transactions
1249 WHERE perf_txn_obj_type = 'PA_PROJECTS'
1250 AND perf_txn_obj_id = p_project_list(i)
1251 AND trunc(creation_date) < trunc(sysdate - p_days_old)
1252 AND current_flag = 'N';
1253
1254 END LOOP;
1255
1256 x_retcode := '1';
1257
1258 pa_debug.G_err_stage := 'Leaving PURGE_TRANSACTION () with success';
1259 IF P_PA_DEBUG_MODE = 'Y' THEN
1260 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1261 END IF;
1262 pa_debug.reset_err_stack;
1263
1264 commit;
1265
1266
1267 EXCEPTION
1268
1269 WHEN NO_DATA_FOUND THEN
1270
1271 pa_debug.write_file('LOG', 'PURGE_TRANSACTION () exception: No data found');
1272 pa_debug.write_file('LOG', pa_debug.g_err_stack);
1273 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1274
1275 WHEN OTHERS THEN
1276
1277 x_errbuf := x_errbuf||'errbuf: '||sqlerrm;
1278 pa_debug.write_file('LOG', 'PURGE_TRANSACTION () exception: Others');
1279 pa_debug.write_file('LOG', pa_debug.g_err_stack);
1280 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1281
1282 END purge_transaction;
1283
1284 -- Procedure get_threshold
1285 -- Purpose This procedure will return information from
1286 -- PA_PERF_THRESHOLDS table.
1287
1288 PROCEDURE get_threshold (
1289 p_rule_id IN NUMBER,
1290 p_rule_type IN VARCHAR2,
1291 p_cur_value IN NUMBER,
1292 x_threshold_id out NOCOPY NUMBER, --File.Sql.39 bug 4440895
1293 x_indicator_code OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1294 x_exception_flag OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1295 x_weighting OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1296 x_from_value OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1297 x_to_value OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
1298 x_errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1299 x_retcode OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1300
1301 l_count number;
1302
1303 BEGIN
1304
1305 x_retcode := '0';
1306
1307 pa_debug.G_err_stage := 'Entering GET_THRESHOLD ()';
1308 IF P_PA_DEBUG_MODE = 'Y' THEN
1309 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1310 END IF;
1311 pa_debug.g_err_stage := ' Current system time is '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
1312 IF P_PA_DEBUG_MODE = 'Y' THEN
1313 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1314 END IF;
1315
1316 SELECT COUNT(*) INTO l_count FROM dual WHERE EXISTS
1317 (
1318 SELECT pt.threshold_Id, pt.indicator_code, pt.exception_flag,
1319 pt.weighting, pt.from_value, pt.to_value
1320 FROM pa_perf_thresholds pt, pa_perf_rules pr
1321 WHERE pr.rule_id = NVL(p_rule_id, -99)
1322 AND pr.rule_id = pt.thres_obj_id
1323 AND pt.rule_type = p_rule_type
1324 AND pr.rule_type = pt.rule_type
1325 AND NVL(round(p_cur_value, DECODE(pr.precision,0.1,1,0.01,2,0.001,3,0)),
1326 -99999999999) between pt.from_value and pt.to_value
1327 );
1328 IF l_count = 0 THEN
1329 x_retcode := 0;
1330 x_exception_flag := 'N';
1331 ELSE
1332 SELECT pt.threshold_Id, pt.indicator_code, pt.exception_flag,
1333 pt.weighting, pt.from_value, pt.to_value
1334 INTO x_threshold_id, x_indicator_code, x_exception_flag,
1335 x_weighting, x_from_value, x_to_value
1336 FROM pa_perf_thresholds pt, pa_perf_rules pr
1337 WHERE pr.rule_id = NVL(p_rule_id, -99)
1338 AND pr.rule_id = pt.thres_obj_id
1339 AND pt.rule_type = p_rule_type
1340 AND pr.rule_type = pt.rule_type
1341 AND NVL(round(p_cur_value, DECODE(pr.precision,0.1,1,0.01,2,0.001,3,0)),
1342 -99999999999) between pt.from_value and pt.to_value
1343 AND rownum = 1;
1344 END IF;
1345
1346 x_retcode := '1';
1347
1348 pa_debug.G_err_stage := 'Leaving GET_THRESHOLD () with success';
1349 IF P_PA_DEBUG_MODE = 'Y' THEN
1350 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1351 END IF;
1352 pa_debug.reset_err_stack;
1353
1354
1355
1356 EXCEPTION
1357
1358 WHEN NO_DATA_FOUND THEN
1359
1360 pa_debug.write_file('LOG', 'GET_THRESHOLD () exception: No data found');
1361 pa_debug.write_file('LOG', pa_debug.g_err_stack);
1362 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1363
1364 WHEN OTHERS THEN
1365
1366 x_errbuf := x_errbuf||'errbuf: '||sqlerrm;
1367 pa_debug.write_file('LOG', 'GET_THRESHOLD () exception: Others');
1368 pa_debug.write_file('LOG', pa_debug.g_err_stack);
1369 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1370
1371 END get_threshold;
1372
1373 -- Procedure get_kpa_score
1374 -- Purpose This procedure will be called by concurrent program.
1375 -- Once running, it will generate the Project KPA Summary.
1376 PROCEDURE get_kpa_score(p_project_list IN PA_PLSQL_DATATYPES.IdTabTyp,
1377 x_errbuf OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
1378 x_retcode OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
1379
1380
1381 l_summary_table pa_exception_engine_pkg.summary_table;
1382 l_summary_seq NUMBER;
1383 l_summary_det_seq NUMBER;
1384 l_kpas PA_PLSQL_DATATYPES.Char30TabTyp ;
1385 l_score NUMBER;
1386 l_count NUMBER;
1387 l_kpa_rule_id NUMBER;
1388 l_weighting NUMBER;
1389 l_indicator_code VARCHAR2(30);
1390 l_excp_flag VARCHAR2(1);
1391 l_thres_from NUMBER;
1392 l_thres_to NUMBER;
1393 l_status VARCHAR2(30);
1394 l_threshold_id NUMBER;
1395 l_rule_id NUMBER;
1396
1397 l_counter NUMBER;
1398
1399 l_score_list pa_plsql_datatypes.NumTabTyp ;
1400 l_count_list PA_PLSQL_DATATYPES.NumTabTyp ;
1401
1402 CURSOR get_kpas IS
1403 SELECT lookup_code
1404 FROM pa_lookups
1405 WHERE lookup_type = 'PA_PERF_KEY_AREAS'
1406 AND lookup_code <> 'ALL'
1407 ORDER BY To_number(predefined_flag) ASC ;
1408
1409 CURSOR get_score_rule (l_proj_id IN NUMBER, l_kpa_code IN VARCHAR2)
1410 IS
1411 select
1412 ppor.rule_id
1413 from
1414 pa_perf_rules ppr, pa_perf_object_rules ppor
1415 where
1416 ppor.object_type = 'PA_PROJECTS'
1417 AND ppor.object_id = l_proj_id
1418 and ppr.kpa_code = l_kpa_code
1419 AND ppor.rule_id = ppr.rule_id
1420 AND ppr.rule_type = 'SCORE_RULE'
1421 AND ppr.score_method = 'SUM'
1422 AND Trunc(Sysdate) BETWEEN ppr.start_date_active
1423 AND Nvl(ppr.end_date_active, Trunc(Sysdate +1));
1424
1425
1426 CURSOR get_kpa_score_rule (l_proj_id IN number, l_kpa_code IN varchar2)
1427 IS
1428 select Nvl(sum(ppem.weighting), 0),
1429 MIN (ppor.rule_id), COUNT(ppem.perf_txn_id)
1430 from pa_perf_transactions ppem ,
1431 pa_perf_rules ppr, pa_perf_object_rules ppor
1432 where ppem.current_flag = 'Y'
1433 and ppem.perf_txn_obj_type = 'PA_PROJECTS'
1434 and ppem.perf_txn_obj_id = l_proj_id
1435 AND ppor.object_type = 'PA_PROJECTS'
1436 AND ppor.object_id = l_proj_id
1437 AND ppor.rule_id = ppr.rule_id
1438 AND ppr.rule_type = 'SCORE_RULE'
1439 AND ppr.score_method = 'SUM'
1440 AND ppr.kpa_code = ppem.kpa_code
1441 AND ppem.kpa_code = l_kpa_code
1442 AND Nvl(ppem.exception_flag, 'Y') = 'Y'
1443 AND Trunc(Sysdate) BETWEEN ppr.start_date_active
1444 AND Nvl(ppr.end_date_active, Trunc(Sysdate +1))
1445 group by ppem.kpa_code;
1446
1447
1448 CURSOR get_kpa_score_breakdown (l_proj_id IN number, l_kpa_code IN VARCHAR2, l_ind IN varchar2)
1449 IS
1450 select
1451 COUNT(ppem.perf_txn_id),
1452 Nvl(sum(ppem.weighting), 0)
1453 from pa_perf_transactions ppem ,
1454 pa_perf_rules ppr, pa_perf_object_rules ppor
1455 where ppem.current_flag = 'Y'
1456 and ppem.perf_txn_obj_type = 'PA_PROJECTS'
1457 and ppem.perf_txn_obj_id = l_proj_id
1458 AND ppor.object_type = 'PA_PROJECTS'
1459 AND ppor.object_id = l_proj_id
1460 AND ppor.rule_id = ppr.rule_id
1461 AND ppr.rule_type = 'SCORE_RULE'
1462 AND ppr.score_method = 'SUM'
1463 AND ppr.kpa_code = ppem.kpa_code
1464 AND ppem.kpa_code = l_kpa_code
1465 AND Nvl(ppem.exception_flag, 'Y') = 'Y'
1466 AND Trunc(Sysdate) BETWEEN ppr.start_date_active
1467 AND Nvl(ppr.end_date_active, Trunc(Sysdate +1))
1468 AND ppem.indicator_code = l_ind
1469 group by ppem.kpa_code;
1470
1471
1472 CURSOR get_indicator
1473 IS
1474 SELECT lookup_code
1475 FROM pa_lookups
1476 WHERE lookup_type = 'PA_PERF_INDICATORS'
1477 ORDER BY predefined_flag ASC;
1478
1479 l_errbuf VARCHAR2(500);
1480 l_retcode VARCHAR2(100);
1481 l_excep_flag VARCHAR2(1);
1482
1483 BEGIN
1484
1485 x_retcode := '0';
1486
1487 pa_debug.G_err_stage := 'Entering PAPFSCRE ()';
1488 IF P_PA_DEBUG_MODE = 'Y' THEN
1489 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1490 END IF;
1491 pa_debug.g_err_stage := ' Current system time is '||to_char(sysdate, 'DD-MON-YYYY HH24:MI:SS');
1492 IF P_PA_DEBUG_MODE = 'Y' THEN
1493 pa_debug.write_file('LOG', pa_debug.g_err_stage);
1494 END IF;
1495
1496
1497 /*** Begin business logic to generate the KPA Summary Info ***/
1498
1499 IF P_PA_DEBUG_MODE = 'Y' THEN
1500 PA_DEBUG.write_file('LOG', 'Begin business logic to generate the KPA Summary Info ');
1501 PA_DEBUG.write_file('LOG', 'Get all KPA codes');
1502 END IF;
1503
1504 -- get all kpa codes
1505 OPEN get_kpas;
1506 FETCH get_kpas bulk collect INTO l_kpas;
1507 CLOSE get_kpas;
1508
1509 -- mass update the current_flag in PA_PERF_KPA_SUMMARY table
1510
1511 IF P_PA_DEBUG_MODE = 'Y' THEN
1512 PA_DEBUG.write_file('LOG', 'Mass update the KPA Summary table');
1513 END IF;
1514
1515 FORALL i IN p_project_list.first..p_project_list.last
1516 UPDATE pa_perf_kpa_summary
1517 SET current_flag = 'N'
1518 WHERE object_type = 'PA_PROJECTS'
1519 AND object_id = p_project_list(i);
1520
1521 /* write the Scoring Logic Here */
1522
1523 IF P_PA_DEBUG_MODE = 'Y' THEN
1524 PA_DEBUG.write_file('LOG', 'Process scoring logic for each project');
1525 END IF;
1526
1527
1528 FOR i IN p_project_list.first..p_project_list.last LOOP
1529
1530 IF P_PA_DEBUG_MODE = 'Y' THEN
1531 PA_DEBUG.write_file('LOG', 'Project ID : '||p_project_list(i));
1532 END IF;
1533
1534 SELECT pa_perf_kpa_summary_s1.nextval
1535 INTO l_summary_seq
1536 FROM DUAL;
1537
1538
1539 l_summary_table.DELETE();
1540
1541
1542 --- for each KPA
1543 FOR j IN l_kpas.first..l_kpas.last LOOP
1544
1545 --- get the overall KPA Score
1546 OPEN get_kpa_score_rule(p_project_list(i), l_kpas(j));
1547 FETCH get_kpa_score_rule INTO l_score, l_kpa_rule_id, l_count;
1548
1549
1550 if (get_kpa_score_rule%notfound) then
1551 l_score := null;
1552 l_kpa_rule_id := null;
1553 l_count := null;
1554 end if;
1555 CLOSE get_kpa_score_rule;
1556
1557 IF (l_kpa_rule_id IS NULL) THEN
1558 --- there is no exception transactions for this KPA Code
1559 --- , we still need to create a
1560 --- record in PA_PERF_KPA_SUMMARY later on
1561
1562
1563 l_summary_table(j).kpa_code := l_kpas(j);
1564 l_summary_table(j).indicator_code := null;
1565 l_summary_table(j).score := 0;
1566 l_summary_table(j).thres_from := NULL;
1567 l_summary_table(j).thres_to := NULL;
1568
1569 --- only for the kpa which the project has a scoring rule
1570 -- associated
1571 --- we need to save the summary detatil information.
1572
1573
1574 OPEN get_score_rule(p_project_list(i), l_kpas(j));
1575 FETCH get_score_rule INTO l_rule_id;
1576 IF (get_score_rule%notfound) THEN
1577 l_rule_id := NULL;
1578 END IF;
1579
1580 CLOSE get_score_rule;
1581
1582 IF (l_rule_id IS NOT NULL) THEN
1583
1584 SELECT pa_perf_kpa_summary_det_s1.NEXTVAL
1585 INTO l_summary_det_seq
1586 FROM dual;
1587
1588 IF P_PA_DEBUG_MODE = 'Y' THEN
1589 PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_KPA_SUMMARY_DET');
1590 PA_DEBUG.write_file('LOG', 'KPA code : '||l_kpas(j));
1591 PA_DEBUG.write_file('LOG', 'Rule ID : '||l_rule_id);
1592 END IF;
1593
1594 INSERT INTO pa_perf_kpa_summary_det
1595 (
1596 kpa_summary_det_id,
1597 kpa_summary_id,
1598 object_type,
1599 object_id,
1600 kpa_code,
1601 indicator_code,
1602 COUNT,
1603 score,
1604 rule_id,
1605 ind1_count,
1606 ind1_score,
1607 ind2_count,
1608 ind2_score,
1609 ind3_count,
1610 ind3_score,
1611 ind4_count,
1612 ind4_score,
1613 ind5_count,
1614 ind5_score,
1615 creation_date,
1616 created_by ,
1617 last_update_date,
1618 last_updated_by ,
1619 last_update_login
1620 )
1621 VALUES
1622 (
1623 l_summary_det_seq,
1624 l_summary_seq,
1625 'PA_PROJECTS',
1626 p_project_list(i),
1627 l_kpas(j),
1628 null,
1629 0,
1630 0,
1631 l_rule_id,
1632 0,
1633 0,
1634 0,
1635 0,
1636 0,
1637 0,
1638 0,
1639 0,
1640 0,
1641 0,
1642 Sysdate,
1643 fnd_global.user_id,
1644 Sysdate,
1645 fnd_global.user_id,
1646 fnd_global.login_id
1647 );
1648 END IF;
1649
1650
1651 ELSE
1652 -- if we find some transactions for the given KPA
1653 -- ge the KPA Scroing Rule Threshold
1654
1655 l_retcode := 0;
1656 -- call api TO get the threshold
1657 pa_exception_engine_pkg.get_threshold (
1658 l_kpa_rule_id,
1659 'SCORE_RULE',
1660 l_score,
1661 l_threshold_id,
1662 l_indicator_code ,
1663 l_excep_flag,
1664 l_weighting,
1665 l_thres_from,
1666 l_thres_to,
1667 l_errbuf,
1668 l_retcode);
1669
1670 IF (l_retcode = 1 ) THEN
1671 --- if we find a matching threshold
1672 --- save the result into PA_PERF_KPA_SUMMARY_DET table
1673
1674 l_score_list.DELETE;
1675 l_count_list.DELETE;
1676
1677 l_counter := 1;
1678 FOR ind_code IN get_indicator LOOP
1679 OPEN get_kpa_score_breakdown(p_project_list(i), l_kpas(j), ind_code.lookup_code);
1680 FETCH get_kpa_score_breakdown INTO l_count_list(l_counter), l_score_list(l_counter);
1681 IF (get_kpa_score_breakdown%NOTfound) THEN
1682 l_count_list(l_counter) := 0;
1683 l_score_list(l_counter) := 0;
1684 END IF;
1685 CLOSE get_kpa_score_breakdown;
1686
1687 l_counter := l_counter+1;
1688
1689 END LOOP;
1690
1691
1692 SELECT pa_perf_kpa_summary_det_s1.NEXTVAL
1693 INTO l_summary_det_seq
1694 FROM dual;
1695
1696 IF P_PA_DEBUG_MODE = 'Y' THEN
1697 PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_KPA_SUMMARY_DET');
1698 PA_DEBUG.write_file('LOG', 'KPA code : '||l_kpas(j));
1699 PA_DEBUG.write_file('LOG', 'Rule ID : '||l_kpa_rule_id);
1700 END IF;
1701
1702 INSERT INTO pa_perf_kpa_summary_det
1703 (
1704 kpa_summary_det_id,
1705 kpa_summary_id,
1706 object_type,
1707 object_id,
1708 kpa_code,
1709 indicator_code,
1710 COUNT,
1711 score,
1712 rule_id,
1713 ind1_count,
1714 ind1_score,
1715 ind2_count,
1716 ind2_score,
1717 ind3_count,
1718 ind3_score,
1719 ind4_count,
1720 ind4_score,
1721 ind5_count,
1722 ind5_score,
1723 creation_date,
1724 created_by ,
1725 last_update_date,
1726 last_updated_by ,
1727 last_update_login
1728 )
1729 VALUES
1730 (
1731 l_summary_det_seq,
1732 l_summary_seq,
1733 'PA_PROJECTS',
1734 p_project_list(i),
1735 l_kpas(j),
1736 l_indicator_code,
1737 l_count,
1738 l_score,
1739 l_kpa_rule_id,
1740 l_count_list(1),
1741 l_score_list(1),
1742 l_count_list(2),
1743 l_score_list(2),
1744 l_count_list(3),
1745 l_score_list(3),
1746 l_count_list(4),
1747 l_score_list(4),
1748 l_count_list(5),
1749 l_score_list(5),
1750 Sysdate,
1751 fnd_global.user_id,
1752 Sysdate,
1753 fnd_global.user_id,
1754 fnd_global.login_id
1755
1756 );
1757
1758 --- save the result to PA_PERF_KPA_TRANS table
1759 IF P_PA_DEBUG_MODE = 'Y' THEN
1760 PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_KPA_TRANS');
1761 END IF;
1762
1763 INSERT INTO pa_perf_kpa_trans
1764 (kpa_summary_det_id,
1765 perf_txn_id,
1766 creation_date,
1767 created_by ,
1768 last_update_date,
1769 last_updated_by ,
1770 last_update_login)
1771 SELECT l_summary_det_seq,perf_txn_id, Sysdate,
1772 fnd_global.user_id,
1773 Sysdate,
1774 fnd_global.user_id,
1775 fnd_global.login_id
1776 FROM pa_perf_transactions
1777 WHERE perf_txn_obj_type = 'PA_PROJECTS'
1778 AND perf_txn_obj_id = p_project_list(i)
1779 AND kpa_code = l_kpas(j)
1780 AND current_flag = 'Y'
1781 AND Nvl(exception_flag, 'Y') = 'Y'
1782 ;
1783
1784 --- update the transaction to be as included in the last scoring
1785
1786 UPDATE pa_perf_transactions
1787 SET included_in_scoring = 'Y'
1788 WHERE perf_txn_obj_type = 'PA_PROJECTS'
1789 AND perf_txn_obj_id = p_project_list(i)
1790 AND kpa_code = l_kpas(j)
1791 AND current_flag = 'Y'
1792 AND Nvl(exception_flag, 'Y') = 'Y'
1793 ;
1794
1795 --- save the KPA summary info
1796
1797 l_summary_table(j).kpa_code := l_kpas(j);
1798 l_summary_table(j).indicator_code := l_indicator_code;
1799 l_summary_table(j).score := l_score;
1800 l_summary_table(j).thres_from := l_thres_from;
1801 l_summary_table(j).thres_to := l_thres_to;
1802
1803 ELSE
1804 --- if we do not find a matching threshold
1805 --- we still need to save to the details table.
1806
1807 l_score_list.DELETE;
1808 l_count_list.DELETE;
1809
1810 l_counter := 1;
1811 FOR ind_code IN get_indicator LOOP
1812 OPEN get_kpa_score_breakdown(p_project_list(i), l_kpas(j), ind_code.lookup_code);
1813 FETCH get_kpa_score_breakdown INTO l_count_list(l_counter), l_score_list(l_counter);
1814 IF (get_kpa_score_breakdown%NOTfound) THEN
1815 l_count_list(l_counter) := 0;
1816 l_score_list(l_counter) := 0;
1817 END IF;
1818 CLOSE get_kpa_score_breakdown;
1819
1820 l_counter := l_counter+1;
1821
1822 END LOOP;
1823
1824
1825 SELECT pa_perf_kpa_summary_det_s1.NEXTVAL
1826 INTO l_summary_det_seq
1827 FROM dual;
1828
1829 IF P_PA_DEBUG_MODE = 'Y' THEN
1830 PA_DEBUG.write_file('LOG', 'Inserting into PA_PERF_KPA_SUMMARY_DET');
1831 PA_DEBUG.write_file('LOG', 'KPA code : '||l_kpas(j));
1832 PA_DEBUG.write_file('LOG', 'Rule ID : '||l_kpa_rule_id);
1833 END IF;
1834
1835 INSERT INTO pa_perf_kpa_summary_det
1836 (
1837 kpa_summary_det_id,
1838 kpa_summary_id,
1839 object_type,
1840 object_id,
1841 kpa_code,
1842 indicator_code,
1843 COUNT,
1844 score,
1845 rule_id,
1846 ind1_count,
1847 ind1_score,
1848 ind2_count,
1849 ind2_score,
1850 ind3_count,
1851 ind3_score,
1852 ind4_count,
1853 ind4_score,
1854 ind5_count,
1855 ind5_score,
1856 creation_date,
1857 created_by ,
1858 last_update_date,
1859 last_updated_by ,
1860 last_update_login
1861 )
1862 VALUES
1863 (
1864 l_summary_det_seq,
1865 l_summary_seq,
1866 'PA_PROJECTS',
1867 p_project_list(i),
1868 l_kpas(j),
1869 null,
1870 l_count,
1871 l_score,
1872 l_kpa_rule_id,
1873 l_count_list(1),
1874 l_score_list(1),
1875 l_count_list(2),
1876 l_score_list(2),
1877 l_count_list(3),
1878 l_score_list(3),
1879 l_count_list(4),
1880 l_score_list(4),
1881 l_count_list(5),
1882 l_score_list(5),
1883 Sysdate,
1884 fnd_global.user_id,
1885 Sysdate,
1886 fnd_global.user_id,
1887 fnd_global.login_id
1888 );
1889
1890 --- save the result to PA_PERF_KPA_TRANS table
1891 INSERT INTO pa_perf_kpa_trans
1892 (kpa_summary_det_id,
1893 perf_txn_id,
1894 creation_date,
1895 created_by ,
1896 last_update_date,
1897 last_updated_by ,
1898 last_update_login)
1899 SELECT l_summary_det_seq,perf_txn_id, Sysdate,
1900 fnd_global.user_id,
1901 Sysdate,
1902 fnd_global.user_id,
1903 fnd_global.login_id
1904 FROM pa_perf_transactions
1905 WHERE perf_txn_obj_type = 'PA_PROJECTS'
1906 AND perf_txn_obj_id = p_project_list(i)
1907 AND kpa_code = l_kpas(j)
1908 AND current_flag = 'Y'
1909 AND Nvl(exception_flag, 'Y') = 'Y'
1910 ;
1911
1912 --- update the transaction to be as included in the last scoring
1913
1914 UPDATE pa_perf_transactions
1915 SET included_in_scoring = 'Y'
1916 WHERE perf_txn_obj_type = 'PA_PROJECTS'
1917 AND perf_txn_obj_id = p_project_list(i)
1918 AND kpa_code = l_kpas(j)
1919 AND current_flag = 'Y'
1920 AND Nvl(exception_flag, 'Y') = 'Y'
1921 ;
1922
1923 --- save the KPA summary info
1924
1925 l_summary_table(j).kpa_code := l_kpas(j);
1926 l_summary_table(j).indicator_code := NULL;
1927 l_summary_table(j).score := l_score;
1928 l_summary_table(j).thres_from := NULL;
1929 l_summary_table(j).thres_to := null;
1930
1931 END IF;
1932
1933
1934 END IF;
1935
1936
1937
1938
1939 END LOOP;
1940
1941 --- calculate the overall score
1942 l_status := pa_perf_status_client_extn.get_performance_status(
1943 'PA_PROJECTS',
1944 p_project_list(i),
1945 l_summary_table
1946 );
1947
1948 -- create the record in PA_PERF_KPA_SUMMARY
1949
1950 INSERT INTO pa_perf_kpa_summary
1951 (
1952 kpa_summary_id,
1953 object_type,
1954 object_id,
1955 date_checked,
1956 current_flag,
1957 perf_status_code,
1958 kpa1_code,
1959 kpa1_indicator,
1960 kpa1_score,
1961 kpa1_thres_from,
1962 kpa1_thres_to,
1963 kpa2_code,
1964 kpa2_indicator,
1965 kpa2_score,
1966 kpa2_thres_from,
1967 kpa2_thres_to,
1968 kpa3_code,
1969 kpa3_indicator,
1970 kpa3_score,
1971 kpa3_thres_from,
1972 kpa3_thres_to,
1973 kpa4_code,
1974 kpa4_indicator,
1975 kpa4_score,
1976 kpa4_thres_from,
1977 kpa4_thres_to,
1978 kpa5_code,
1979 kpa5_indicator,
1980 kpa5_score,
1981 kpa5_thres_from,
1982 kpa5_thres_to,
1983 creation_date,
1984 created_by ,
1985 last_update_date,
1986 last_updated_by ,
1987 last_update_login
1988 )
1989 VALUES
1990 (
1991 l_summary_seq,
1992 'PA_PROJECTS',
1993 p_project_list(i),
1994 Sysdate,
1995 'Y',
1996 l_status,
1997 l_summary_table(1).kpa_code,
1998 l_summary_table(1).indicator_code,
1999 l_summary_table(1).score,
2000 l_summary_table(1).thres_from,
2001 l_summary_table(1).thres_to,
2002 l_summary_table(2).kpa_code,
2003 l_summary_table(2).indicator_code,
2004 l_summary_table(2).score,
2005 l_summary_table(2).thres_from,
2006 l_summary_table(2).thres_to,
2007
2008 l_summary_table(3).kpa_code,
2009 l_summary_table(3).indicator_code,
2010 l_summary_table(3).score,
2011 l_summary_table(3).thres_from,
2012 l_summary_table(3).thres_to,
2013
2014 l_summary_table(4).kpa_code,
2015 l_summary_table(4).indicator_code,
2016 l_summary_table(4).score,
2017 l_summary_table(4).thres_from,
2018 l_summary_table(4).thres_to,
2019
2020 l_summary_table(5).kpa_code,
2021 l_summary_table(5).indicator_code,
2022 l_summary_table(5).score,
2023 l_summary_table(5).thres_from,
2024 l_summary_table(5).thres_to,
2025 Sysdate,
2026 fnd_global.user_id,
2027 Sysdate,
2028 fnd_global.user_id,
2029 fnd_global.login_id
2030
2031 );
2032
2033 --- update the included in scoring flag for all other transactions
2034 UPDATE pa_perf_transactions
2035 SET included_in_scoring = 'N'
2036 WHERE perf_txn_obj_type = 'PA_PROJECTS'
2037 AND perf_txn_obj_id = p_project_list(i)
2038 AND Nvl(exception_flag, 'Y') = 'Y'
2039 AND included_in_scoring = 'Y'
2040 AND perf_txn_id NOT IN
2041 (
2042 select ppkt.perf_txn_id
2043 from pa_perf_kpa_summary ppks, pa_perf_kpa_summary_det ppkd,
2044 pa_perf_kpa_trans ppkt
2045 where ppks.object_type = 'PA_PROJECTS' and
2046 ppks.object_id = p_project_list(i)
2047 and ppks.current_flag = 'Y'
2048 and ppks.kpa_summary_id = ppkd.kpa_summary_id
2049 and ppkd.kpa_summary_det_id = ppkt.kpa_summary_det_id
2050 )
2051 ;
2052
2053 commit;
2054
2055 END LOOP;
2056
2057
2058 IF P_PA_DEBUG_MODE = 'Y' THEN
2059 PA_DEBUG.write_file('LOG', 'Sucessfully processed scoring logic for each project');
2060 END IF;
2061
2062 x_retcode := '1';
2063
2064 pa_debug.G_err_stage := 'Leaving PAPFSCRE () with success';
2065 IF P_PA_DEBUG_MODE = 'Y' THEN
2066 pa_debug.write_file('LOG', pa_debug.g_err_stage);
2067 END IF;
2068 pa_debug.reset_err_stack;
2069
2070 commit;
2071
2072 EXCEPTION
2073
2074 WHEN NO_DATA_FOUND THEN
2075
2076 pa_debug.write_file('LOG', 'PAPFSCRE () exception: No data found');
2077 pa_debug.write_file('LOG', pa_debug.g_err_stack);
2078 pa_debug.write_file('LOG', pa_debug.g_err_stage);
2079
2080 WHEN OTHERS THEN
2081 x_errbuf := x_errbuf||'errbuf: '||sqlerrm;
2082 pa_debug.write_file('LOG', 'PAPFSCRE () exception: Others');
2083 pa_debug.write_file('LOG', pa_debug.g_err_stack);
2084 pa_debug.write_file('LOG', pa_debug.g_err_stage);
2085
2086 END get_kpa_score;
2087
2088 END pa_exception_engine_pkg;
[Home] [Help]