[Home] [Help]
PACKAGE BODY: APPS.POA_CM_EVAL_SCORES_ICX
Source
1 PACKAGE BODY POA_cm_eval_scores_icx AS
2 /* $Header: POACMSCB.pls 115.15 2003/04/29 21:47:10 rvickrey ship $ */
3
4 PROCEDURE Reload_Header(p_header t_header_record, error_msg VARCHAR2)
5 IS
6 BEGIN
7 poa_cm_evaluation_icx.header_page( p_header.custom_measure_code,
8 p_header.custom_measure,
9 p_header.period_type,
10 p_header.user_period_type,
11 p_header.period_name,
12 p_header.supplier_id,
13 p_header.supplier,
14 p_header.supplier_site_id,
15 p_header.supplier_site,
16 p_header.category_id,
17 p_header.commodity,
18 p_header.item_id,
19 p_header.item,
20 p_header.comments,
21 p_header.evaluated_by_id,
22 p_header.evaluated_by,
23 p_header.org_id,
24 p_header.oper_unit_id,
25 p_header.operating_unit,
26 p_header.submit_type,
27 p_header.evaluation_id,
28 error_msg);
29 END;
30
31 PROCEDURE Get_Criteria_Info( p_category_id IN NUMBER,
32 p_oper_unit_id IN NUMBER,
33 p_table IN OUT NOCOPY t_criteria_table)
34 IS
35
36 precedence VARCHAR2(80) := FND_PROFILE.value('CUSTOM_MEASURE_PRECEDENCE');
37
38 l_criteria_table t_criteria_table;
39 l_index number := 0;
40 l_progress varchar2(240) := '';
41
42 type t_Criteria_Cursor is ref cursor return t_criteria_record;
43
44 c_Criteria t_Criteria_Cursor;
45
46 BEGIN
47
48 l_progress := '001';
49
50 open c_Criteria for
51 select criteria_code, weight, min_score, max_score
52 from poa_cm_distributions
53 where category_id = nvl(p_category_id, -1)
54 and organization_id = nvl(p_oper_unit_id, -1)
55 order by criteria_code;
56
57 l_progress := '002';
58
59 loop
60
61 fetch c_Criteria into l_criteria_table(l_index);
62
63 exit when c_Criteria%NOTFOUND;
64
65 l_index := l_index + 1;
66
67 end loop;
68
69 l_progress := '003';
70
71 close c_Criteria;
72
73 if (l_index = 0) then
74
75 if precedence = 'COMMODITY-OU' then
76
77 l_progress := '004';
78
79 open c_Criteria for
80 select criteria_code, weight, min_score, max_score
81 from poa_cm_distributions
82 where category_id = nvl(p_category_id, -1)
83 and organization_id is null
84 order by criteria_code;
85
86 elsif precedence = 'OU-COMMODITY' then
87
88 l_progress := '005';
89
90 open c_Criteria for
91 select criteria_code, weight, min_score, max_score
92 from poa_cm_distributions
93 where organization_id = nvl(p_oper_unit_id, -1)
94 and category_id is null
95 order by criteria_code;
96
97 end if;
98
99 l_progress := '006';
100
101 loop
102
103 fetch c_Criteria into l_criteria_table(l_index);
104
105 exit when c_Criteria%NOTFOUND;
106
107 l_index := l_index + 1;
108
109 end loop;
110
111 l_progress := '007';
112
113 close c_Criteria;
114
115 if (l_index = 0) then
116
117 if precedence = 'COMMODITY-OU' then
118
119 l_progress := '008';
120
121 open c_Criteria for
122 select criteria_code, weight, min_score, max_score
123 from poa_cm_distributions
124 where organization_id = nvl(p_oper_unit_id, -1)
125 and category_id is null
126 order by criteria_code;
127
128 elsif precedence = 'OU-COMMODITY' then
129
130 l_progress := '009';
131
132 open c_Criteria for
133 select criteria_code, weight, min_score, max_score
134 from poa_cm_distributions
135 where category_id = nvl(p_category_id, -1)
136 and organization_id is null
137 order by criteria_code;
138
139 end if;
140
141 l_progress := '010';
142
143 loop
144
145 fetch c_Criteria into l_criteria_table(l_index);
146
147 exit when c_Criteria%NOTFOUND;
148
149 l_index := l_index + 1;
150
151 end loop;
152
153 l_progress := '011';
154
155 close c_Criteria;
156
157 if (l_index = 0) then
158
159 open c_Criteria for
160 select criteria_code, weight, min_score, max_score
161 from poa_cm_distributions
162 where category_id is null
163 and organization_id is null
164 order by criteria_code;
165
166 l_progress := '012';
167
168 loop
169
170 fetch c_Criteria into l_criteria_table(l_index);
171
172 exit when c_Criteria%NOTFOUND;
173
174 l_index := l_index + 1;
175
176 end loop;
177
178 l_progress := '013';
179
180 close c_Criteria;
181
182 end if;
183
184 end if;
185
186 end if;
187
188 l_progress := '014';
189
190 p_table := l_criteria_table;
191
192 EXCEPTION
193 when others then
194 null;
195 END;
196
197 PROCEDURE PrintCSS IS
198 BEGIN
199
200 htp.p('<STYLE TYPE="text/css">');
201 htp.p('<!--');
202 htp.p('font.pagetitle');
203 htp.p(' {font-family: Arial, sans-serif;');
204 htp.p(' font-style: italic;');
205 htp.p(' font-weight: bold;');
206 htp.p(' color: black;');
207 htp.p(' font-size: 14pt;}');
208 htp.p('font.itemtitle');
209 htp.p(' {font-family: Arial, sans-serif;');
210 htp.p(' font-weight: bold;');
211 htp.p(' color: white;');
212 htp.p(' font-size: 14pt;}');
213 htp.p('font.bartitle');
214 htp.p(' {font-family: Arial, sans-serif;');
215 htp.p(' color: black;');
216 htp.p(' font-weight: bold;');
217 htp.p(' font-size: 8pt;}');
218 htp.p('font.containertitle');
219 htp.p(' {font-family: Arial, sans-serif;');
220 htp.p(' font-style: italic;');
221 htp.p(' font-weight: bold;');
222 htp.p(' color: #cccccc;');
223 htp.p(' font-size: 14pt;}');
224 htp.p('font.graytab');
225 htp.p(' {font-family: Arial, sans-serif;');
226 htp.p(' color: black;');
227 htp.p(' text-decoration: none;');
228 htp.p(' font-size: 10pt;}');
229 htp.p('font.purpletab');
230 htp.p(' {font-family: Arial, sans-serif;');
231 htp.p(' color: white;');
232 htp.p(' text-decoration: none;');
233 htp.p(' font-size: 10pt;}');
234 htp.p('font.promptblack');
235 htp.p(' {font-family: Arial, sans-serif;');
236 htp.p(' color: black;');
237 htp.p(' text-decoration: none;');
238 htp.p(' font-size: 10pt;}');
239 htp.p('font.helptext');
240 htp.p(' {font-family: Arial, sans-serif;');
241 htp.p(' font-style: italic;');
242 htp.p(' color: black;');
243 htp.p(' font-size: 10pt;}');
244 htp.p('font.helptitle ');
245 htp.p(' {font-family: Arial, sans-serif;');
246 htp.p(' font-style: italic;');
247 htp.p(' font-weight: bold;');
248 htp.p(' color: black;');
249 htp.p(' font-size: 14pt;}');
250 htp.p('font.promptwhite');
251 htp.p(' {font-family: sans-serif;');
252 htp.p(' font-face: arial;');
253 htp.p(' color: white;');
254 htp.p(' font-size: 10pt;}');
255 htp.p('font.datablack');
256 htp.p(' {font-family: Arial, sans-serif;');
257 htp.p(' font-weight: bold;');
258 htp.p(' color: black;');
259 htp.p(' font-size: 10pt;}');
260 htp.p('font.fielddata ');
261 htp.p(' {font-family: Arial, sans-serif;');
262 htp.p(' font-weight: bold;');
263 htp.p(' color: black;');
264 htp.p(' font-size: 10pt;}');
265 htp.p('font.tablefielddata ');
266 htp.p(' {font-family: "arial narrow", sans-serif;');
267 htp.p(' font-weight: bold;');
268 htp.p(' color: black;');
269 htp.p(' font-size: 10pt;}');
270 htp.p('font.tabledata ');
271 htp.p(' {font-family: Arial, sans-serif;');
272 htp.p(' color: black;');
273 htp.p(' font-size: 10pt;}');
274 htp.p('font.button');
275 htp.p(' {font-family: Arial, sans-serif;');
276 htp.p(' color: black;');
277 htp.p(' text-decoration: none;');
278 htp.p(' font-size: 10pt;}');
279 htp.p('font.link');
280 htp.p(' {font-family: Arial, sans-serif;');
281 htp.p(' color: blue;');
282 htp.p(' text-decoration: underline;');
283 htp.p(' font-size: 10pt;}');
284 htp.p('font.linkbold');
285 htp.p(' {font-family: Arial, sans-serif;');
286 htp.p(' font-weight: bold;');
287 htp.p(' color: blue;');
288 htp.p(' text-decoration: underline;');
289 htp.p(' font-size: 10pt;}');
290 htp.p('font.dropdownmenu');
291 htp.p(' {font-family: Arial, sans-serif;');
292 htp.p(' color: #003366;');
293 htp.p(' font-style: italic;');
294 htp.p(' font-size: 16pt;}');
295 htp.p('-->');
296 htp.p('</STYLE>');
297
298 END PrintCSS;
299
300 PROCEDURE PrintBottomButtons(p_language IN VARCHAR2, which_one IN NUMBER) IS
301 BEGIN
302
303
304 htp.p('
305 <table width=100% border=0 cellspacing=0 cellpadding=15>
306 <tr>
307 <td>
308 <table width=100% border=0 cellspacing=0 cellpadding=0>
309 <tr>
310 <td width=604> </td>
311 <td rowspan=2 valign=bottom width=12><img src=/OA_MEDIA/bisslghr.gif width=12 height=14></td>
312 </tr>
313 <tr>
314 <td bgcolor=#CCCC99 height=1><img src=/OA_MEDIA/bisspace.gif width=1 height=1></td>
315 </tr>
316 <tr>
317 <td height=5><img src=http:/OA_MEDIA/bisspace.gif width=1 height=1></td>
318 </tr>
319
320 <tr>
321 <td align="right"> <span class="OraALinkText"><span class="OraALinkText"> ');
322
323 if (which_one = 1) then
324 htp.p('
325 <A href=OracleNavigate.Responsibility onMouseOver="window.status=''Cancel'';return true"><img src=/OA_MEDIA/poacancl.gif border="0"></a>
326 <A href="javascript:window.history.back()" onMouseOver="window.status=''Back'';return true"><img src=/OA_MEDIA/poaback.gif border="0"></a>
327 <A href="javascript:submitDoc(''Done'')" onMouseOver="window.status=''Save'';return true"><img src=/OA_MEDIA/poasave.gif border="0"></a> ');
328 end if;
329
330 if (which_one = 3) then
331 htp.p('
332 <A href=OracleNavigate.Responsibility onMouseOver="window.status=''Cancel'';return true"><img src=/OA_MEDIA/poacancl.gif border="0"></a>
333 <A href="javascript:window.history.back()" onMouseOver="window.status=''Back'';return true"><img src=/OA_MEDIA/poaback.gif border="0"></a> ');
334 end if;
335
336 if (which_one = 2) then
337 htp.p('
338 <A href="javascript:window.history.back()" onMouseOver="window.status=''Back'';return true"><img src=/OA_MEDIA/poaback.gif border="0"></a>
339 <a href="javascript:document.POA_CM_EVAL_SCORES_R.submit()" onMouseOver="window.status=''Modify'';return true"><img src=/OA_MEDIA/poamodfy.gif border="0"></a> ');
340 end if;
341
342 htp.p('
343 </span></span></td>
344 </tr>
345 </table>
346 </td>
347 </tr>
348 </table>
349 ');
350
351 END PrintBottomButtons;
352
353 PROCEDURE redirect_page(poa_cm_custom_measure_code IN VARCHAR2 DEFAULT NULL,
354 poa_cm_custom_measure IN VARCHAR2 DEFAULT NULL,
355 poa_cm_period_type IN VARCHAR2 DEFAULT NULL,
356 poa_cm_user_period_type IN VARCHAR2 DEFAULT NULL,
357 poa_cm_period_name IN VARCHAR2 DEFAULT NULL,
358 poa_cm_supplier_id IN VARCHAR2 DEFAULT NULL,
359 poa_cm_supplier IN VARCHAR2 DEFAULT NULL,
360 poa_cm_supplier_site_id IN VARCHAR2 DEFAULT NULL,
361 poa_cm_supplier_site IN VARCHAR2 DEFAULT NULL,
362 poa_cm_category_id IN VARCHAR2 DEFAULT NULL,
363 poa_cm_commodity IN VARCHAR2 DEFAULT NULL,
364 poa_cm_item_id IN VARCHAR2 DEFAULT NULL,
365 poa_cm_item IN VARCHAR2 DEFAULT NULL,
366 poa_cm_comments IN VARCHAR2 DEFAULT NULL,
367 poa_cm_evaluated_by_id IN VARCHAR2 DEFAULT NULL,
368 poa_cm_evaluated_by IN VARCHAR2 DEFAULT NULL,
369 poa_cm_org_id IN VARCHAR2 DEFAULT NULL,
370 poa_cm_oper_unit_id IN VARCHAR2 DEFAULT NULL,
371 poa_cm_operating_unit IN VARCHAR2 DEFAULT NULL,
372 POA_CM_SUBMIT_TYPE IN VARCHAR2 DEFAULT NULL,
373 POA_CM_EVALUATION_ID IN VARCHAR2 DEFAULT NULL
374 ) IS
375
376 x_category_set_id NUMBER;
377 x_structure_id NUMBER;
378 x_organization_id NUMBER;
379
380 l_language VARCHAR2(5);
381 l_script_name VARCHAR2(240);
382
383 l_period_type VARCHAR2(15) := poa_cm_period_type;
384 l_period_name VARCHAR2(15) := poa_cm_period_name;
385 l_supplier_id NUMBER := to_number(poa_cm_supplier_id);
386 l_supplier_site_id NUMBER := to_number(poa_cm_supplier_site_id);
387 l_org_id NUMBER := to_number(poa_cm_org_id);
388 l_category_id NUMBER := to_number(poa_cm_category_id);
389 l_item_id NUMBER := to_number(poa_cm_item_id);
390 l_evaluated_by_id NUMBER := to_number(poa_cm_evaluated_by_id);
391 l_oper_unit_id NUMBER := to_number(poa_cm_oper_unit_id);
392 l_evaluation_id NUMBER := to_number(poa_cm_evaluation_id);
393
394 l_user_period_type VARCHAR2(15) := poa_cm_user_period_type;
395 l_supplier_name PO_VENDORS.VENDOR_NAME%TYPE := poa_cm_supplier;
396 l_site_name VARCHAR2(15) := poa_cm_supplier_site;
397 l_operating_unit hr_all_organization_units.NAME%TYPE := poa_cm_operating_unit;
398 l_commodity VARCHAR2(81) := poa_cm_commodity;
399 l_item VARCHAR2(40) := poa_cm_item;
400 l_evaluated_by VARCHAR2(240):= poa_cm_evaluated_by;
401 l_submit_type VARCHAR2(10) := poa_cm_submit_type;
402
403 temp_var VARCHAR2(1) := null;
404
405 l_progress VARCHAR2(240);
406 l_header t_header_record;
407
408 BEGIN
409
410
411 l_header.custom_measure_code := poa_cm_custom_measure_code;
412 l_header.custom_measure := poa_cm_custom_measure;
413 l_header.period_type := poa_cm_period_type;
414 l_header.user_period_type := poa_cm_user_period_type;
415 l_header.period_name := poa_cm_period_name;
416 l_header.supplier_id := poa_cm_supplier_id;
417 l_header.supplier := poa_cm_supplier;
418 l_header.supplier_site_id := poa_cm_supplier_site_id ;
419 l_header.supplier_site := poa_cm_supplier_site;
420 l_header.category_id := poa_cm_category_id;
421 l_header.commodity := poa_cm_commodity;
422 l_header.item_id := poa_cm_item_id;
423 l_header.item := poa_cm_item;
424 l_header.comments := poa_cm_comments;
425 l_header.evaluated_by_id := poa_cm_evaluated_by_id;
426 l_header.evaluated_by := poa_cm_evaluated_by;
427 l_header.org_id := poa_cm_org_id;
428 l_header.oper_unit_id := poa_cm_oper_unit_id;
429 l_header.operating_unit := poa_cm_operating_unit;
430 l_header.submit_type := poa_cm_submit_type;
431 l_header.evaluation_id := poa_cm_evaluation_id;
432
433
434 -- Set multi-org context
435
436 l_progress := '001';
437
438 fnd_client_info.set_org_context(poa_cm_org_id);
439
440 -- Get Period Type
441
442 begin
443 if l_period_type is null then
444
445 SELECT period_type
446 INTO l_period_type
447 FROM gl_period_types
448 WHERE USER_PERIOD_TYPE = l_user_period_type;
449
450 if l_period_type is null then
451 reload_header(l_header, 'Invalid Period Type');
452 return;
453 end if;
454
455 end if;
456
457 exception
458 when others then
459 reload_header(l_header, 'Invalid Period Type');
460 return;
461 end;
462
463 --Check period name
464
465 begin
466
467 SELECT count(1)
468 INTO temp_var
469 FROM gl_periods
470 WHERE PERIOD_NAME = l_period_name
471 AND PERIOD_TYPE = l_period_type;
472
473 if temp_var = 0 then
474 reload_header(l_header, 'Invalid Period Name');
475 return;
476 end if;
477
478 exception
479 when others then
480 reload_header(l_header, 'Invalid Period Name');
481 return;
482 end;
483
484 -- Get supplier id
485
486 l_progress := '00';
487
488 begin
489
490 if l_supplier_id is null then
491
492 SELECT min(vendor_id)
493 INTO l_supplier_id
494 FROM PO_VENDORS
495 WHERE VENDOR_NAME = l_supplier_name;
496
497 if l_supplier_id is null then
498 reload_header(l_header, 'Invalid Supplier');
499 return;
500 end if;
501
502 end if;
503
504 exception
505 when others then
506 reload_header(l_header, 'Invalid Supplier');
507 return;
508 end;
509
510 -- Get supplier site id
511
512 begin
513 if (l_supplier_site_id is null) AND (l_site_name is not null) then
514
515 SELECT min(vendor_site_id)
516 INTO l_supplier_site_id
517 FROM PO_VENDOR_SITES
518 WHERE VENDOR_SITE_CODE = l_site_name
519 AND VENDOR_ID = nvl(l_supplier_id,-1);
520
521 if l_supplier_site_id is null then
522 reload_header(l_header, 'Invalid Supplier Site');
523 return;
524 end if;
525
526 end if;
527
528 exception
529 when others then
530 reload_header(l_header, 'Invalid Supplier Site');
531 return;
532 end;
533
534 -- Get oper_unit_id
535
536 begin
537 if (l_oper_unit_id is null) and (l_operating_unit is not null) then
538
539 SELECT min(organization_id)
540 INTO l_oper_unit_id
541 FROM HR_OPERATING_UNITS
542 WHERE name = l_operating_unit AND EXISTS (
543 SELECT '1' FROM PO_VENDOR_SITES_ALL WHERE
544 vendor_site_id = nvl(l_supplier_site_id, vendor_site_id));
545
546 if l_oper_unit_id is null then
547 reload_header(l_header, 'Invalid Operating Unit');
548 return;
549 end if;
550
551 end if;
552
553 exception
554 when others then
555 reload_header(l_header, 'Invalid Operating Unit');
556 return;
557 end;
558
559 -- Get Category id
560
561 po_core_s.get_item_category_structure(x_category_set_id, x_structure_id);
562
563 begin
564
565 if (l_category_id is null) and (l_commodity is not null) then
566
567 SELECT min(category_id)
568 INTO l_category_id
569 FROM mtl_categories_kfv
570 WHERE CONCATENATED_SEGMENTS = l_commodity
571 AND STRUCTURE_ID = x_structure_id;
572
573 if l_category_id is null then
574 reload_header(l_header, 'Invalid Category');
575 return;
576 end if;
577
578 end if;
579
580 exception
581 when others then
582 reload_header(l_header, 'Invalid Category');
583 return;
584 end;
585
586 begin
587
588 -- Get organization_id
589
590 SELECT inventory_organization_id
591 INTO x_organization_id
592 FROM financials_system_parameters;
593
594 -- Get item id
595
596 if (l_item_id is null) and (l_item is not null) then
597 SELECT min(msi.inventory_item_id)
598 INTO l_item_id
599 FROM mtl_system_items_kfv msi,
600 mtl_item_categories mic
601 WHERE msi.CONCATENATED_SEGMENTS = l_item
602 AND msi.organization_id = x_organization_id
603 AND msi.inventory_item_id = mic.inventory_item_id
604 AND msi.organization_id = mic.organization_id
605 AND mic.category_set_id = x_category_set_id
606 AND nvl(l_category_id, mic.category_id) = mic.category_id;
607
608 if l_item_id is null then
609 reload_header(l_header, 'Invalid Tom');
610 return;
611 end if;
612
613 end if;
614
615 -- Get Category_id from item if category_id is null;
616
617 if (l_category_id is null) and (l_item_id is not null) then
618
619 SELECT CATEGORY_ID
620 INTO l_category_id
621 FROM mtl_item_categories
622 WHERE organization_id = x_organization_id
623 AND category_set_id = x_category_set_id
624 AND INVENTORY_ITEM_ID = l_item_id;
625
626 end if;
627
628 exception
629 when others then
630 reload_header(l_header, SQLERRM || ',' || x_organization_id || ',' ||
631 x_category_set_id || ',' || l_item_id);
632 return;
633 end;
634
635 -- Get evaluator id
636
637 begin
638
639 if (l_evaluated_by_id is null) and (l_evaluated_by is not null) then
640
641 SELECT orig_system_id
642 INTO l_evaluated_by_id
643 FROM WF_USERS
644 WHERE NAME = l_evaluated_by;
645
646 if l_evaluated_by_id is null then
647 reload_header(l_header, 'Invalid Evaluator');
648 return;
649 end if;
650 end if;
651
652 exception
653 when others then
654 reload_header(l_header, 'Invalid Evaluator');
655 return;
656 end;
657
658
659
660 if (POA_CM_SUBMIT_TYPE = 'Next') then
661 poa_cm_eval_scores_icx.score_entry_page(
662 poa_cm_custom_measure_code ,
663 poa_cm_custom_measure ,
664 l_period_type ,
665 poa_cm_user_period_type ,
666 poa_cm_period_name ,
667 l_supplier_id ,
668 poa_cm_supplier ,
669 l_supplier_site_id ,
670 poa_cm_supplier_site ,
671 l_category_id ,
672 poa_cm_commodity ,
673 l_item_id ,
674 poa_cm_item ,
675 poa_cm_comments ,
676 l_evaluated_by_id ,
677 poa_cm_evaluated_by ,
678 poa_cm_org_id ,
679 l_oper_unit_id ,
680 poa_cm_operating_unit ,
681 'Update' ,
682 POA_CM_EVALUATION_ID );
683 end if;
684
685 if (POA_CM_SUBMIT_TYPE = 'Update') then
686 poa_cm_eval_scores_icx.query_evals(
687 poa_cm_custom_measure_code ,
688 poa_cm_custom_measure ,
689 l_period_type ,
690 poa_cm_user_period_type ,
691 poa_cm_period_name ,
692 l_supplier_id ,
693 poa_cm_supplier ,
694 l_supplier_site_id ,
695 poa_cm_supplier_site ,
696 l_category_id ,
697 poa_cm_commodity ,
698 l_item_id ,
699 poa_cm_item ,
700 poa_cm_comments ,
701 l_evaluated_by_id ,
702 poa_cm_evaluated_by ,
703 poa_cm_org_id ,
704 l_oper_unit_id ,
705 poa_cm_operating_unit ,
706 poa_cm_submit_type ,
707 poa_cm_evaluation_id );
708 end if;
709
710 if (POA_CM_SUBMIT_TYPE is null) then
711 poa_cm_evaluation_icx.header_page();
712 end if;
713
714 END redirect_page;
715
716 PROCEDURE score_entry_page(poa_cm_custom_measure_code IN VARCHAR2 DEFAULT NULL,
717 poa_cm_custom_measure IN VARCHAR2 DEFAULT NULL,
718 poa_cm_period_type IN VARCHAR2 DEFAULT NULL,
719 poa_cm_user_period_type IN VARCHAR2 DEFAULT NULL,
720 poa_cm_period_name IN VARCHAR2 DEFAULT NULL,
721 poa_cm_supplier_id IN VARCHAR2 DEFAULT NULL,
722 poa_cm_supplier IN VARCHAR2 DEFAULT NULL,
723 poa_cm_supplier_site_id IN VARCHAR2 DEFAULT NULL,
724 poa_cm_supplier_site IN VARCHAR2 DEFAULT NULL,
725 poa_cm_category_id IN VARCHAR2 DEFAULT NULL,
726 poa_cm_commodity IN VARCHAR2 DEFAULT NULL,
727 poa_cm_item_id IN VARCHAR2 DEFAULT NULL,
728 poa_cm_item IN VARCHAR2 DEFAULT NULL,
729 poa_cm_comments IN VARCHAR2 DEFAULT NULL,
730 poa_cm_evaluated_by_id IN VARCHAR2 DEFAULT NULL,
731 poa_cm_evaluated_by IN VARCHAR2 DEFAULT NULL,
732 poa_cm_org_id IN VARCHAR2 DEFAULT NULL,
733 poa_cm_oper_unit_id IN VARCHAR2 DEFAULT NULL,
734 poa_cm_operating_unit IN VARCHAR2 DEFAULT NULL,
735 poa_cm_submit_type IN VARCHAR2 DEFAULT NULL,
736 poa_cm_evaluation_id IN VARCHAR2 DEFAULT NULL
737 ) IS
738
739 l_language VARCHAR2(5);
740 l_script_name VARCHAR2(240);
741
742 l_period_type VARCHAR2(15) := poa_cm_period_type;
743 l_period_name VARCHAR2(15) := poa_cm_period_name;
744 l_supplier_id NUMBER := to_number(poa_cm_supplier_id);
745 l_supplier_site_id NUMBER := to_number(poa_cm_supplier_site_id);
746 l_org_id NUMBER := to_number(poa_cm_org_id);
747 l_category_id NUMBER := to_number(poa_cm_category_id);
748 l_item_id NUMBER := to_number(poa_cm_item_id);
749 l_evaluated_by_id NUMBER := to_number(poa_cm_evaluated_by_id);
750 l_oper_unit_id NUMBER := to_number(poa_cm_oper_unit_id);
751 l_evaluation_id NUMBER := to_number(poa_cm_evaluation_id);
752
753 l_user_period_type VARCHAR2(15) := poa_cm_user_period_type;
754 l_supplier_name PO_VENDORS.VENDOR_NAME%TYPE := poa_cm_supplier;
755 l_site_name VARCHAR2(15) := poa_cm_supplier_site;
756 l_operating_unit hr_all_organization_units.NAME%TYPE := poa_cm_operating_unit;
757 l_commodity VARCHAR2(81) := poa_cm_commodity;
758 l_item VARCHAR2(40) := poa_cm_item;
759 l_evaluated_by VARCHAR2(240):= poa_cm_evaluated_by;
760 l_submit_type VARCHAR2(10) := poa_cm_submit_type;
761
762 l_criteria_table t_criteria_table;
763 v_Criteria VARCHAR2(80);
764
765 l_scores_table t_scores_table;
766 l_index NUMBER := 0;
767
768 type t_Scores_Cursor is ref cursor return t_scores_record;
769 c_eval_scores t_Scores_Cursor;
770
771 type t_Criteria_Cursor is ref cursor return t_criteria_record;
772 c_Criteria t_Criteria_Cursor;
773
774 BEGIN
775
776 IF (l_evaluation_id is null) THEN
777
778 Get_Criteria_Info( l_category_id,
779 l_oper_unit_id,
780 l_criteria_table);
781
782 ELSE
783
784 SELECT category_id, oper_unit_id
785 INTO l_category_id, l_oper_unit_id
786 FROM poa_cm_evaluation
787 WHERE evaluation_id = l_evaluation_id;
788
789 open c_Criteria for
790 select criteria_code, weight, min_score, max_score
791 from poa_cm_eval_scores
792 where evaluation_id = l_evaluation_id;
793
794 loop
795 fetch c_Criteria into l_criteria_table(l_index);
796 exit when c_Criteria%NOTFOUND;
797 l_index := l_index + 1;
798 end loop;
799
800 FOR v_counter IN 1..l_criteria_table.count LOOP
801
802 OPEN c_eval_scores FOR
803 SELECT score, comments
804 FROM poa_cm_eval_scores
805 WHERE evaluation_id = l_evaluation_id
806 AND criteria_code = l_criteria_table(v_counter-1).criteria_code;
807
808 FETCH c_eval_scores INTO l_scores_table(v_counter-1).score, l_scores_table(v_counter-1).comments;
809
810 CLOSE c_eval_scores;
811 END LOOP;
812
813 END IF;
814
815
816 IF NOT icx_sec.validatesession THEN
817 RETURN;
818 END IF;
819
820 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
821 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
822
823 htp.htmlOpen;
824 htp.title('Survey Response Scores');
825
826 js.scriptOpen;
827
828 htp.p('
829 function weighted_score(linenum)
830 {
831 var f = document.POA_CM_EVAL_SCORES_R;
832
833 if (isNaN(f.SCORE[linenum].value))
834 {
835 alert(''Please enter a valid score.'');
836 f.SCORE[linenum].focus();
837 return;
838 }
839
840 if (f.SCORE[linenum].value * 1 > f.MAX_SCORE[linenum].value * 1)
841 {
842 alert(''Score is out of range.'');
843 f.SCORE[linenum].focus();
844 return;
845 }
846
847 if (f.SCORE[linenum].value * 1 < f.MIN_SCORE[linenum].value * 1)
848 {
849 alert(''Score is out of range.'');
850 f.SCORE[linenum].focus();
851 return;
852 }
853
854 if (f.WEIGHT[linenum].value * 1 == -1)
855 {
856 f.WEIGHTED_SCORE[linenum].value = f.SCORE[linenum].value;
857 }
858 else
859 {
860 f.WEIGHTED_SCORE[linenum].value = roundDecimal(((f.SCORE[linenum].value - f.MIN_SCORE[linenum].value) *
861 f.WEIGHT[linenum].value /
862 (f.MAX_SCORE[linenum].value - f.MIN_SCORE[linenum].value)), 2);
863 }
864
865 var temp = 0;
866
867 for (var i = 0; i < f.WEIGHTED_SCORE.length; i++)
868 temp = temp + f.WEIGHTED_SCORE[i].value * 1;
869
870 f.TOTAL_SCORE.value = roundDecimal(temp, 2);
871 }
872
873 function updateScores()
874 {
875
876 var f = document.POA_CM_EVAL_SCORES_R;
877
878 for (var i = 0; i < (f.WEIGHTED_SCORE.length-1); i++)
879 weighted_score(i);
880 }
881
882 function roundDecimal(expr, digits)
883 {
884 var str= "" + Math.round(eval(expr) * Math.pow(10, digits));
885 while (str.length <= digits)
886 {
887 str= "0"+str;
888 }
889 var decpoint= str.length- digits;
890 return str.substring(0, decpoint) + "." + str.substring(decpoint, str.length);
891 }
892
893
894 function submitDoc(submit_type)
895 {
896 var f = document.POA_CM_EVAL_SCORES_R;
897
898 if (submit_type == "Refresh")
899 f.POA_CM_SUBMIT_TYPE.value = "Refresh";
900 else if (submit_type == "Done")
901 f.POA_CM_SUBMIT_TYPE.value = "Done";
902
903 for (var i = 0; i < f.SCORE.length; i++)
904 {
905 if (isNaN(f.SCORE[i].value))
906 {
907 return;
908 }
909
910 if (f.SCORE[i].value * 1 > f.MAX_SCORE[i].value * 1)
911 {
912 return;
913 }
914
915 if (f.SCORE[i].value * 1 < f.MIN_SCORE[i].value * 1)
916 {
917 return;
918 }
919 }
920
921 document.POA_CM_EVAL_SCORES_R.submit();
922 }
923
924 function cancelAction()
925 {
926 var f = document.POA_CM_EVAL_SCORES_R;
927
928 for (var i = 0; i < f.SCORE.length; i++)
929 {
930 f.SCORE[i].value = "";
931 f.WEIGHTED_SCORE[i].value = "0.00";
932 }
933
934 f.TOTAL_SCORE.value = "0.00";
935
936 }
937
938 ');
939
940 js.scriptClose;
941
942 htp.headOpen;
943 icx_util.copyright;
944
945 PrintCSS;
946
947 htp.p('<LINK REL="stylesheet" HREF="/OA_HTML/bismarli.css">');
948
949 htp.headClose;
950
951 htp.bodyOpen;
952
953
954 htp.p('<BODY bgColor="#ffffff" link="#663300" vlink="#996633" alink="#FF6600" text="#000000" onLoad="javascript:updateScores()">');
955
956
957 htp.p('
958
959 <table border=0 cellspacing=0 cellpadding=0 width=100%>
960 <tr><td rowspan=2 valign=bottom width=371>
961 <table border=0 cellspacing=0 cellpadding=0 width=100%>
962 <tr align=left><td height=30><img src=/OA_MEDIA/bisorcl.gif border=no height=23 width=141></a></td>
963 <tr align=left> <td valign=bottom><img src=/OA_MEDIA/POABRAND.gif border=no></a></td></td></tr>
964 </table>
965 </td><td colspan=2 rowspan=2 valign=bottom align=right>
966 <table border=0 cellpadding=0 align=right cellspacing=4>
967 <tr valign=bottom>
968 <td width=60 align=center><a href=Oraclemypage.home onMouseOver="window.status=''Return to Portal''; return true">
969 <img alt=Return to Portal src=/OA_MEDIA/bisrtrnp.gif width=32 border=0 height=32></a></td>
970 <td width=60 align=center><a href=OracleNavigate.Responsibility onMouseOver="window.status=''Menu''; return true">
971 <img alt=Menu src=/OA_MEDIA/bisnmenu.gif width=32 border=0
972 height=32></a></td>
973 <td width=60 align=center valign=bottom><a href="javascript:help_window()", onMouseOver="window.status=''Help''; return true">
974 <img alt=Help src=/OA_MEDIA/bisnhelp.gif width=32 border=0
975 height=32></a></td>
976 </tr>
977 <tr align=center valign=top>
978 <td width=60><a href=Oraclemypage.home onMouseOver="window.status=''Return to Portal''; return true">
979 <span class="OraGlobalButtonText">Return to Portal</span></a></td>
980 <td width=60><a href=OracleNavigate.Responsibility onMouseOver="window.status=''Menu''; return true">
981 <span class="OraGlobalButtonText">Menu</span></a></td>
982 <td width=60><a href="javascript:help_window()", onMouseOver="window.status=''Help''; return true">
983 <span class="OraGlobalButtonText">Help</span></a></td>
984 </tr></table>
985 </td>
986 </tr></table>
987 </table>
988
989 <table Border=0 cellpadding=0 cellspacing=0 width=100%>
990 <tbody>
991 <tr><td bgcolor=#ffffff colspan=3 height=1><img height=1 src=/OA_MEDIA/bisspace.gif width=1></td>
992 </tr>
993 <tr>
994 <td bgcolor=#31659c colspan=2 height=21><img border=0 height=21 src=/OA_MEDIA/bisspace.gif width=1></td>
995 <td bgcolor=#31659c height=21><font face="Arial, Helvetica, sans-serif" size="4" color="#ffffff"> </font></td>
996 <td background=/OA_MEDIA/bisrhshd.gif height=21 width=5><img border=0 height=1
997 src=/OA_MEDIA/bisspace.gif width=1></td>
998 </tr>
999 <tr>
1000 <td bgcolor=#31659c height=16 width=9><img border=0 height=1 src=/OA_MEDIA/bisspace.gif width=9></td>
1001 <td bgcolor=#31659c height=16 width=5><img border=0 height=1 src=/OA_MEDIA/bisspace.gif width=5></td>
1002 <td background=/OA_MEDIA/bisbot.gif width=1000><img align=top height=16
1003 src=/OA_MEDIA/bistopar.gif width=26></td>
1004 <td align=left valign=top width=5><img height=8 src=/OA_MEDIA/bisrend.gif width=8></td>
1005 </tr>
1006 <tr>
1007 <td align=left background=/OA_MEDIA/bisbot.gif height=8 valign=top width=9><img height=8
1008 src=/OA_MEDIA/bislend.gif width=10></td>
1009 <td background=/OA_MEDIA/bisbot.gif height=8 width=5><img border=0 height=1
1010 src=/OA_MEDIA/bisspace.gif width=1></td>
1011 <td align=left valign=top width=1000><img height=8 src=/OA_MEDIA/bisarchc.gif width=9></td>
1012 <td width=5></td>
1013 </tr>
1014 </tbody>
1015 </table>
1016
1017 <table width=100% border=0 cellspacing=0 cellpadding=15>
1018 <tr><td><table width=100% border=0 cellspacing=0 cellpadding=0>
1019 <tr><td class="OraHeader"><font face="Arial, Helvetica, sans-serif" size="5" color="#336699">Survey Response Scores</font></td></tr>
1020 <tr bgcolor="#CCCC99"><td height=1><img src=/OA_MEDIA/bisspace.gif width=1 height=1></td></tr>
1021 <tr><td><font face="Arial, Helvetica, sans-serif" size="2">Enter the desired scores and then save.</font></td></tr>
1022 </table>
1023 </td></tr>
1024 </table>
1025
1026 ');
1027
1028
1029
1030
1031 htp.p('<table width=100% bgcolor=#FFFFFF cellpadding=10 cellspacing=0 border=0>');
1032 htp.p('<tr><td>');
1033 htp.p('<table ALIGN=center cellpadding=2 cellspacing=1 border=2>');
1034
1035 -- Heading
1036
1037 ak_query_pkg.exec_query(p_parent_region_appl_id=>201,
1038 p_parent_region_code=>'POA_CM_EVAL_SCORES_R',
1039 p_responsibility_id=>icx_sec.getID(icx_sec.PV_RESPONSIBILITY_ID),
1040 p_user_id=>icx_sec.getID(icx_sec.PV_WEB_USER_ID),
1041 p_return_parents=>'F',
1042 p_return_children=>'F');
1043
1044 htp.p('<TR>');
1045
1046 FOR v_counter IN 1..7 LOOP
1047
1048 htp.p('<TH bgcolor=#31659C align=' || ak_query_pkg.g_items_table(v_counter).horizontal_alignment ||
1049 ' valign=bottom><font class=promptwhite>' ||
1050 ak_query_pkg.g_items_table(v_counter).attribute_label_long || '</font></TH>');
1051
1052 END LOOP;
1053
1054 htp.p('</TR>');
1055
1056 htp.p('<FORM NAME="POA_CM_EVAL_SCORES_R" ACTION="' ||l_script_name|| '/POA_CM_ENTER_SCORES_ICX.redirect_page" METHOD="POST">');
1057
1058
1059 FOR v_counter IN 1..l_criteria_table.count LOOP
1060
1061 -- Get Displayed field
1062
1063 SELECT displayed_field
1064 INTO v_Criteria
1065 FROM po_lookup_codes
1066 WHERE lookup_type = 'SATISFACTION CRITERIA'
1067 AND lookup_code = l_criteria_table(v_counter - 1).criteria_code;
1068
1069 -- Criteria
1070
1071 htp.p('<TD ALIGN=left><font class=tabledata><INPUT NAME="CRITERIA_CODE" TYPE="HIDDEN" VALUE="' || l_criteria_table(v_counter - 1).criteria_code || '">');
1072 htp.p(v_Criteria || '</font></TD>');
1073
1074 -- Score
1075
1076 if (l_evaluation_id is null) then
1077 htp.p('<TD><font class=datablack><INPUT NAME="SCORE" TYPE="TEXT" onChange="javascript:weighted_score('|| to_char(v_counter - 1) ||')" VALUE="" SIZE=4 MAXLENGTH=25></font> </TD>');
1078 else
1079 htp.p('<TD><font class=datablack><INPUT NAME="SCORE" TYPE="TEXT" onChange="javascript:weighted_score('|| to_char(v_counter - 1) ||')" VALUE="' || l_scores_table(v_counter-1).score || '" SIZE=4 MAXLENGTH=25></font> </TD>');
1080 end if;
1081
1082 -- Weight
1083
1084 if l_criteria_table(v_counter - 1).weight is null then
1085 l_criteria_table(v_counter - 1).weight := -1;
1086 end if;
1087
1088 htp.p('<TD ALIGN=right><font class=tabledata><INPUT NAME="WEIGHT" TYPE="HIDDEN" VALUE=' || to_char(l_criteria_table(v_counter - 1).weight) || ' SIZE=4 MAXLENGTH=25></font>');
1089
1090 if l_criteria_table(v_counter - 1).weight = -1 then
1091 htp.p('<font class=tabledata>None</font></TD>');
1092 else
1093 htp.p('<font class=tabledata>' || to_char(l_criteria_table(v_counter - 1).weight, '9990D00') || '</font></TD>');
1094 end if;
1095
1096 -- Weighted Score
1097
1098 htp.p('<TD ALIGN=center><font class=datablack><INPUT NAME="WEIGHTED_SCORE" TYPE="TEXT" onfocus="javascript:this.blur()" VALUE="0.00" SIZE=4 MAXLENGTH=25></font></TD>');
1099
1100 -- Min Score
1101
1102 if l_criteria_table(v_counter - 1).min_score is null then
1103 l_criteria_table(v_counter - 1).min_score := 0;
1104 end if;
1105
1106 htp.p('<TD ALIGN=right><font class=tabledata><INPUT NAME="MIN_SCORE" TYPE="HIDDEN" VALUE=' || to_char(l_criteria_table(v_counter - 1).min_score) || ' SIZE=4 MAXLENGTH=25></font>');
1107
1108
1109 htp.p('<font class=tabledata>' || to_char(l_criteria_table(v_counter - 1).min_score, '9990D00') || '</font></TD>');
1110
1111 -- Max Score
1112
1113 if l_criteria_table(v_counter - 1).max_score is null then
1114 l_criteria_table(v_counter - 1).max_score := 100;
1115 end if;
1116
1117 htp.p('<TD ALIGN=right><font class=tabledata><INPUT NAME="MAX_SCORE" TYPE="HIDDEN" VALUE=' || to_char(l_criteria_table(v_counter - 1).max_score) || ' SIZE=4 MAXLENGTH=25></font>');
1118
1119 htp.p('<font class=tabledata>' || to_char(l_criteria_table(v_counter - 1).max_score, '9990D00') || '</font></TD>');
1120
1121 -- Comments
1122
1123 if (l_evaluation_id is null) then
1124
1125 htp.p('<TD ALIGN=LEFT><font class=datablack><INPUT NAME="COMMENTS" TYPE="TEXT" VALUE="" SIZE=15 MAXLENGTH=240></font> </TD>');
1126
1127 else
1128
1129 htp.p('<TD ALIGN=LEFT><font class=datablack><INPUT NAME="COMMENTS" TYPE="TEXT" VALUE="' || l_scores_table(v_counter-1).comments || '" SIZE=15 MAXLENGTH=240></font> </TD>');
1130
1131 end if;
1132
1133 htp.p('</TR>');
1134
1135 END LOOP;
1136
1137 htp.p('<TR>');
1138
1139 htp.p('<INPUT NAME="CRITERIA_CODE" TYPE="HIDDEN" VALUE="">');
1140
1141 htp.p('<INPUT NAME="SCORE" TYPE="HIDDEN" VALUE="">');
1142
1143 htp.p('<INPUT NAME="WEIGHT" TYPE="HIDDEN" VALUE="">');
1144
1145 htp.p('<INPUT NAME="WEIGHTED_SCORE" TYPE="HIDDEN" VALUE="">');
1146
1147 htp.p('<INPUT NAME="MIN_SCORE" TYPE="HIDDEN" VALUE="">');
1148
1149 htp.p('<INPUT NAME="MAX_SCORE" TYPE="HIDDEN" VALUE="">');
1150
1151 htp.p('<INPUT NAME="COMMENTS" TYPE="HIDDEN" VALUE="">');
1152
1153
1154 htp.p('<TD COLSPAN=3 ALIGN=RIGHT><font class=datablack>' || ak_query_pkg.g_items_table(8).attribute_label_long || '</font></TD>');
1155
1156 htp.p('<TD COLSPAN=1 ALIGN=CENTER BGCOLOR=''#31659C''><font class=datablack><INPUT NAME="TOTAL_SCORE" TYPE="TEXT" onfocus="javascript:this.blur()" VALUE="0.00" SIZE=4 MAXLENGTH=25></font></TD>');
1157
1158 htp.p('<TD COLSPAN=3><FONT COLOR=#CCCCCC> </FONT>');
1159
1160 htp.p('<INPUT NAME="poa_cm_custom_measure_code" TYPE="HIDDEN" VALUE="' || poa_cm_custom_measure_code || '">');
1161 htp.p('<INPUT NAME="poa_cm_custom_measure" TYPE="HIDDEN" VALUE="' || poa_cm_custom_measure || '">');
1162 htp.p('<INPUT NAME="poa_cm_period_type" TYPE="HIDDEN" VALUE="' || l_period_type || '">');
1163 htp.p('<INPUT NAME="poa_cm_user_period_type" TYPE="HIDDEN" VALUE="' || poa_cm_user_period_type || '">');
1164 htp.p('<INPUT NAME="poa_cm_period_name" TYPE="HIDDEN" VALUE="' || poa_cm_period_name || '">');
1165 htp.p('<INPUT NAME="poa_cm_supplier_id" TYPE="HIDDEN" VALUE="' || to_char(l_supplier_id) || '">');
1166 htp.p('<INPUT NAME="poa_cm_supplier" TYPE="HIDDEN" VALUE="' || l_supplier_name || '">');
1167 htp.p('<INPUT NAME="poa_cm_supplier_site_id" TYPE="HIDDEN" VALUE="' || to_char(l_supplier_site_id) || '">');
1168 htp.p('<INPUT NAME="poa_cm_supplier_site" TYPE="HIDDEN" VALUE="' || l_site_name || '">');
1169 htp.p('<INPUT NAME="poa_cm_category_id" TYPE="HIDDEN" VALUE="' || to_char(l_category_id) || '">');
1170 htp.p('<INPUT NAME="poa_cm_commodity" TYPE="HIDDEN" VALUE="' || l_commodity || '">');
1171 htp.p('<INPUT NAME="poa_cm_item_id" TYPE="HIDDEN" VALUE="' || to_char(l_item_id) || '">');
1172 htp.p('<INPUT NAME="poa_cm_item" TYPE="HIDDEN" VALUE="' || l_item || '">');
1173 htp.p('<INPUT NAME="poa_cm_comments" TYPE="HIDDEN" VALUE="' || poa_cm_comments || '">');
1174 htp.p('<INPUT NAME="poa_cm_evaluated_by_id" TYPE="HIDDEN" VALUE="' || to_char(l_evaluated_by_id) || '">');
1175 htp.p('<INPUT NAME="poa_cm_evaluated_by" TYPE="HIDDEN" VALUE="' || l_evaluated_by || '">');
1176 htp.p('<INPUT NAME="poa_cm_org_id" TYPE="HIDDEN" VALUE="' || to_char(l_org_id) || '">');
1177 htp.p('<INPUT NAME="poa_cm_oper_unit_id" TYPE="HIDDEN" VALUE="' || to_char(l_oper_unit_id) || '">');
1178 htp.p('<INPUT NAME="poa_cm_operating_unit" TYPE="HIDDEN" VALUE="' || l_operating_unit || '">');
1179 htp.p('<INPUT NAME="POA_CM_SUBMIT_TYPE" TYPE="HIDDEN" VALUE="' || l_submit_type || '">');
1180 htp.p('<INPUT NAME="POA_CM_EVALUATION_ID" TYPE="HIDDEN" VALUE="' || l_evaluation_id || '">');
1181
1182 htp.p('</TD></TR>');
1183
1184 htp.p('</table>');
1185 htp.p('</td></tr></table>');
1186
1187 PrintBottomButtons(l_language, 1);
1188
1189 htp.bodyClose;
1190 htp.htmlClose;
1191
1192 EXCEPTION
1193 when others then
1194 null;
1195
1196 END score_entry_page;
1197
1198 PROCEDURE query_evals( poa_cm_custom_measure_code IN VARCHAR2 DEFAULT NULL,
1199 poa_cm_custom_measure IN VARCHAR2 DEFAULT NULL,
1200 poa_cm_period_type IN VARCHAR2 DEFAULT NULL,
1201 poa_cm_user_period_type IN VARCHAR2 DEFAULT NULL,
1202 poa_cm_period_name IN VARCHAR2 DEFAULT NULL,
1203 poa_cm_supplier_id IN VARCHAR2 DEFAULT NULL,
1204 poa_cm_supplier IN VARCHAR2 DEFAULT NULL,
1205 poa_cm_supplier_site_id IN VARCHAR2 DEFAULT NULL,
1206 poa_cm_supplier_site IN VARCHAR2 DEFAULT NULL,
1207 poa_cm_category_id IN VARCHAR2 DEFAULT NULL,
1208 poa_cm_commodity IN VARCHAR2 DEFAULT NULL,
1209 poa_cm_item_id IN VARCHAR2 DEFAULT NULL,
1210 poa_cm_item IN VARCHAR2 DEFAULT NULL,
1211 poa_cm_comments IN VARCHAR2 DEFAULT NULL,
1212 poa_cm_evaluated_by_id IN VARCHAR2 DEFAULT NULL,
1213 poa_cm_evaluated_by IN VARCHAR2 DEFAULT NULL,
1214 poa_cm_org_id IN VARCHAR2 DEFAULT NULL,
1215 poa_cm_oper_unit_id IN VARCHAR2 DEFAULT NULL,
1216 poa_cm_operating_unit IN VARCHAR2 DEFAULT NULL,
1217 POA_CM_SUBMIT_TYPE IN VARCHAR2 DEFAULT NULL,
1218 POA_CM_EVALUATION_ID IN VARCHAR2 DEFAULT NULL
1219 ) IS
1220
1221 l_language VARCHAR2(5);
1222 l_script_name VARCHAR2(240);
1223
1224 l_period_type VARCHAR2(15) := poa_cm_period_type;
1225 l_period_name VARCHAR2(15) := poa_cm_period_name;
1226 l_supplier_id NUMBER := to_number(poa_cm_supplier_id);
1227 l_supplier_site_id NUMBER := to_number(poa_cm_supplier_site_id);
1228 l_org_id NUMBER := to_number(poa_cm_org_id);
1229 l_category_id NUMBER := to_number(poa_cm_category_id);
1230 l_item_id NUMBER := to_number(poa_cm_item_id);
1231 l_evaluated_by_id NUMBER := to_number(poa_cm_evaluated_by_id);
1232 l_oper_unit_id NUMBER := to_number(poa_cm_oper_unit_id);
1233 l_evaluation_id NUMBER := to_number(poa_cm_evaluation_id);
1234
1235 l_user_period_type VARCHAR2(15) := poa_cm_user_period_type;
1236 l_supplier_name PO_VENDORS.VENDOR_NAME%TYPE := poa_cm_supplier;
1237 l_site_name VARCHAR2(15) := poa_cm_supplier_site;
1238 l_operating_unit hr_all_organization_units.NAME%TYPE := poa_cm_operating_unit;
1239 l_commodity VARCHAR2(81) := poa_cm_commodity;
1240 l_item VARCHAR2(40) := poa_cm_item;
1241 l_evaluated_by VARCHAR2(240):= poa_cm_evaluated_by;
1242 l_submit_type VARCHAR2(10) := poa_cm_submit_type;
1243
1244 v_eval_id NUMBER;
1245 v_supplier_site VARCHAR2(15);
1246 v_oper_unit hr_all_organization_units.NAME%TYPE;
1247 v_commodity VARCHAR2(81);
1248 v_item VARCHAR2(40);
1249 v_evaluator VARCHAR2(100);
1250 v_creation_date DATE;
1251 v_last_update_date DATE;
1252
1253 v_counter NUMBER := 0;
1254
1255 type t_Eval_Cursor is ref cursor return t_eval_record;
1256 c_evaluation t_Eval_Cursor;
1257
1258 BEGIN
1259
1260 IF NOT icx_sec.validatesession THEN
1261 RETURN;
1262 END IF;
1263
1264 l_language := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1265 l_script_name := owa_util.get_cgi_env('SCRIPT_NAME');
1266
1267 htp.htmlOpen;
1268 htp.title('Survey Response Scores');
1269
1270 js.scriptOpen;
1271
1272 js.scriptClose;
1273
1274 htp.headOpen;
1275
1276 icx_util.copyright;
1277
1278 PrintCSS;
1279
1280 htp.p('<LINK REL="stylesheet" HREF="/OA_HTML/bismarli.css">');
1281
1282 htp.headClose;
1283
1284 htp.bodyOpen;
1285
1286 htp.p('<BODY bgColor="#ffffff" link="#663300" vlink="#996633" alink="#FF6600" text="#000000">');
1287
1288 htp.p('
1289
1290
1291 <table border=0 cellspacing=0 cellpadding=0 width=100%>
1292 <tr><td rowspan=2 valign=bottom width=371>
1293 <table border=0 cellspacing=0 cellpadding=0 width=100%>
1294 <tr align=left><td height=30><img src=/OA_MEDIA/bisorcl.gif border=no height=23 width=141></a></td>
1295 <tr align=left> <td valign=bottom><img src=/OA_MEDIA/POABRAND.gif border=no></a></td></td></tr>
1296 </table>
1297 </td><td colspan=2 rowspan=2 valign=bottom align=right>
1298 <table border=0 cellpadding=0 align=right cellspacing=4>
1299 <tr valign=bottom>
1300 <td width=60 align=center><a href=Oraclemypage.home onMouseOver="window.status=''Return to Portal''; return true">
1301 <img alt=Return to Portal src=/OA_MEDIA/bisrtrnp.gif width=32 border=0 height=32></a></td>
1302 <td width=60 align=center><a href=OracleNavigate.Responsibility onMouseOver="window.status=''Menu''; return true">
1303 <img alt=Menu src=/OA_MEDIA/bisnmenu.gif width=32 border=0
1304 height=32></a></td>
1305 <td width=60 align=center valign=bottom><a href="javascript:help_window()", onMouseOver="window.status=''Help''; return true">
1306 <img alt=Help src=/OA_MEDIA/bisnhelp.gif width=32 border=0
1307 height=32></a></td>
1308 </tr>
1309 <tr align=center valign=top>
1310 <td width=60><a href=Oraclemypage.home onMouseOver="window.status=''Return to Portal''; return true">
1311 <span class="OraGlobalButtonText">Return to Portal</span></a></td>
1312 <td width=60><a href=OracleNavigate.Responsibility onMouseOver="window.status=''Menu''; return true">
1313 <span class="OraGlobalButtonText">Menu</span></a></td>
1314 <td width=60><a href="javascript:help_window()", onMouseOver="window.status=''Help''; return true">
1315 <span class="OraGlobalButtonText">Help</span></a></td>
1316 </tr></table>
1317 </td>
1318 </tr></table>
1319 </table>
1320
1321 <table Border=0 cellpadding=0 cellspacing=0 width=100%>
1322 <tbody>
1323 <tr><td bgcolor=#ffffff colspan=3 height=1><img height=1 src=/OA_MEDIA/bisspace.gif width=1></td>
1324 </tr>
1325 <tr>
1326 <td bgcolor=#31659c colspan=2 height=21><img border=0 height=21 src=/OA_MEDIA/bisspace.gif width=1></td>
1327 <td bgcolor=#31659c height=21><font face="Arial, Helvetica, sans-serif" size="4" color="#ffffff"> </font></td>
1328 <td background=/OA_MEDIA/bisrhshd.gif height=21 width=5><img border=0 height=1
1329 src=/OA_MEDIA/bisspace.gif width=1></td>
1330 </tr>
1331 <tr>
1332 <td bgcolor=#31659c height=16 width=9><img border=0 height=1 src=/OA_MEDIA/bisspace.gif width=9></td>
1333 <td bgcolor=#31659c height=16 width=5><img border=0 height=1 src=/OA_MEDIA/bisspace.gif width=5></td>
1334 <td background=/OA_MEDIA/bisbot.gif width=1000><img align=top height=16
1335 src=/OA_MEDIA/bistopar.gif width=26></td>
1336 <td align=left valign=top width=5><img height=8 src=/OA_MEDIA/bisrend.gif width=8></td>
1337 </tr>
1338 <tr>
1339 <td align=left background=/OA_MEDIA/bisbot.gif height=8 valign=top width=9><img height=8
1340 src=/OA_MEDIA/bislend.gif width=10></td>
1341 <td background=/OA_MEDIA/bisbot.gif height=8 width=5><img border=0 height=1
1342 src=/OA_MEDIA/bisspace.gif width=1></td>
1343 <td align=left valign=top width=1000><img height=8 src=/OA_MEDIA/bisarchc.gif width=9></td>
1344 <td width=5></td>
1345 </tr>
1346 </tbody>
1347 </table>
1348
1349 <table width=100% border=0 cellspacing=0 cellpadding=15>
1350 <tr><td><table width=100% border=0 cellspacing=0 cellpadding=0>
1351 <tr><td class="OraHeader"><font face="Arial, Helvetica, sans-serif" size="5" color="#336699">Survey Response Query Results</font></td></tr>
1352 <tr bgcolor="#CCCC99"><td height=1><img src=/OA_MEDIA/bisspace.gif width=1 height=1></td></tr>
1353 <tr><td><font face="Arial, Helvetica, sans-serif" size="2">Select the survey response that you would like to modify.</font></td></tr>
1354 </table>
1355 </td></tr>
1356 </table>
1357
1358
1359 ');
1360
1361 htp.p('
1362 <table width=100% bgcolor=#FFFFFF cellpadding=15 cellspacing=0 border=0>
1363
1364 <TR><td>
1365 <table width=100% cellpadding=0 cellspacing=0 border=0>
1366 <TR> <td><b>Period Type: ' || l_period_type || '</b></td></tr>
1367 <TR> <td><b>Period Name: ' || l_period_name || '</b></td></tr>
1368 <TR> <td><b>Supplier: ' || l_supplier_name || '</b></td></tr>
1369 </TR>
1370 </table>
1371
1372 <tr><td>
1373 <table ALIGN=center cellpadding=2 cellspacing=1 border=2>
1374 <TH bgcolor=#31659C align=left valign=bottom width=5%><font class=promptwhite> </font></TH>
1375 <TH bgcolor=#31659C align=left valign=bottom width=15%><font class=promptwhite>Operating Unit</font></TH>
1376 <TH bgcolor=#31659C align=left valign=bottom width=15%><font class=promptwhite>Commodity</font></TH>
1377 <TH bgcolor=#31659C align=left valign=bottom width=15%><font class=promptwhite>Item</font></TH>
1378 <TH bgcolor=#31659C align=left valign=bottom width=15%><font class=promptwhite>Supplier Site</font></TH>
1379 <TH bgcolor=#31659C align=left valign=bottom width=15%><font class=promptwhite>Evaluator</font></TH>
1380 <TH bgcolor=#31659C align=left valign=bottom width=10%><font class=promptwhite>Creation Date</font></TH>
1381 <TH bgcolor=#31659C align=left valign=bottom width=10%><font class=promptwhite>Last Update Date</font></TH>
1382 </TR>
1383 <FORM NAME="POA_CM_EVAL_SCORES_R" ACTION="' ||l_script_name|| '/POA_CM_EVAL_SCORES_ICX.score_entry_page" METHOD="POST">
1384
1385 ');
1386
1387 htp.p('<INPUT NAME="poa_cm_custom_measure_code" TYPE="HIDDEN" VALUE="' || poa_cm_custom_measure_code || '">');
1388 htp.p('<INPUT NAME="poa_cm_custom_measure" TYPE="HIDDEN" VALUE="' || poa_cm_custom_measure || '">');
1389 htp.p('<INPUT NAME="poa_cm_period_type" TYPE="HIDDEN" VALUE="' || l_period_type || '">');
1390 htp.p('<INPUT NAME="poa_cm_user_period_type" TYPE="HIDDEN" VALUE="' || poa_cm_user_period_type || '">');
1391 htp.p('<INPUT NAME="poa_cm_period_name" TYPE="HIDDEN" VALUE="' || poa_cm_period_name || '">');
1392 htp.p('<INPUT NAME="poa_cm_supplier_id" TYPE="HIDDEN" VALUE="' || to_char(l_supplier_id) || '">');
1393 htp.p('<INPUT NAME="poa_cm_supplier" TYPE="HIDDEN" VALUE="' || l_supplier_name || '">');
1394 htp.p('<INPUT NAME="poa_cm_supplier_site_id" TYPE="HIDDEN" VALUE="' || to_char(l_supplier_site_id) || '">');
1395 htp.p('<INPUT NAME="poa_cm_supplier_site" TYPE="HIDDEN" VALUE="' || l_site_name || '">');
1396 htp.p('<INPUT NAME="poa_cm_category_id" TYPE="HIDDEN" VALUE="' || to_char(l_category_id) || '">');
1397 htp.p('<INPUT NAME="poa_cm_commodity" TYPE="HIDDEN" VALUE="' || l_commodity || '">');
1398 htp.p('<INPUT NAME="poa_cm_item_id" TYPE="HIDDEN" VALUE="' || to_char(l_item_id) || '">');
1399 htp.p('<INPUT NAME="poa_cm_item" TYPE="HIDDEN" VALUE="' || l_item || '">');
1400 htp.p('<INPUT NAME="poa_cm_comments" TYPE="HIDDEN" VALUE="' || poa_cm_comments || '">');
1401 htp.p('<INPUT NAME="poa_cm_evaluated_by_id" TYPE="HIDDEN" VALUE="' || to_char(l_evaluated_by_id) || '">');
1402 htp.p('<INPUT NAME="poa_cm_evaluated_by" TYPE="HIDDEN" VALUE="' || l_evaluated_by || '">');
1403 htp.p('<INPUT NAME="poa_cm_org_id" TYPE="HIDDEN" VALUE="' || to_char(l_org_id) || '">');
1404 htp.p('<INPUT NAME="poa_cm_oper_unit_id" TYPE="HIDDEN" VALUE="' || to_char(l_oper_unit_id) || '">');
1405 htp.p('<INPUT NAME="poa_cm_operating_unit" TYPE="HIDDEN" VALUE="' || l_operating_unit || '">');
1406 htp.p('<INPUT NAME="POA_CM_SUBMIT_TYPE" TYPE="HIDDEN" VALUE="Refresh">');
1407
1408 if (l_oper_unit_id is not null) and (l_category_id is null) then
1409 OPEN c_evaluation FOR
1410 SELECT pce.evaluation_id,
1411 pvs.vendor_site_code,
1412 hr.name,
1413 cat.concatenated_segments,
1414 item.concatenated_segments,
1415 fu.user_name,
1416 pce.creation_date,
1417 pce.last_update_date
1418 FROM poa_cm_evaluation pce,
1419 hr_operating_units hr,
1420 fnd_user fu,
1421 mtl_categories_kfv cat,
1422 mtl_system_items_kfv item,
1423 po_vendor_sites pvs
1424 WHERE pce.supplier_id = l_supplier_id
1425 AND pce.period_type = l_period_type
1426 AND pce.period_name = l_period_name
1427 AND hr.organization_id (+)= pce.oper_unit_id
1428 AND fu.user_id (+)= pce.evaluated_by
1429 AND cat.category_id (+)= pce.category_id
1430 AND item.inventory_item_id (+)= pce.item_id
1431 AND item.organization_id (+)= pce.org_id
1432 AND pvs.vendor_site_id (+)= pce.supplier_site_id
1433 AND pce.oper_unit_id = l_oper_unit_id
1434 ORDER BY pce.evaluation_id;
1435
1436 elsif (l_oper_unit_id is null) and (l_category_id is not null) then
1437 OPEN c_evaluation FOR
1438 SELECT pce.evaluation_id,
1439 pvs.vendor_site_code,
1440 hr.name,
1441 cat.concatenated_segments,
1442 item.concatenated_segments,
1443 fu.user_name,
1444 pce.creation_date,
1445 pce.last_update_date
1446 FROM poa_cm_evaluation pce,
1447 hr_operating_units hr,
1448 fnd_user fu,
1449 mtl_categories_kfv cat,
1450 mtl_system_items_kfv item,
1451 po_vendor_sites pvs
1452 WHERE pce.supplier_id = l_supplier_id
1453 AND pce.period_type = l_period_type
1454 AND pce.period_name = l_period_name
1455 AND hr.organization_id (+)= pce.oper_unit_id
1456 AND fu.user_id (+)= pce.evaluated_by
1457 AND cat.category_id (+)= pce.category_id
1458 AND item.inventory_item_id (+)= pce.item_id
1459 AND item.organization_id (+)= pce.org_id
1460 AND pvs.vendor_site_id (+)= pce.supplier_site_id
1461 AND pce.category_id = l_category_id
1462 ORDER BY pce.evaluation_id;
1463
1464 elsif (l_oper_unit_id is not null) and (l_category_id is not null) then
1465 OPEN c_evaluation FOR
1466 SELECT pce.evaluation_id,
1467 pvs.vendor_site_code,
1468 hr.name,
1469 cat.concatenated_segments,
1470 item.concatenated_segments,
1471 fu.user_name,
1472 pce.creation_date,
1473 pce.last_update_date
1474 FROM poa_cm_evaluation pce,
1475 hr_operating_units hr,
1476 fnd_user fu,
1477 mtl_categories_kfv cat,
1478 mtl_system_items_kfv item,
1479 po_vendor_sites pvs
1480 WHERE pce.supplier_id = l_supplier_id
1481 AND pce.period_type = l_period_type
1482 AND pce.period_name = l_period_name
1483 AND hr.organization_id (+)= pce.oper_unit_id
1484 AND fu.user_id (+)= pce.evaluated_by
1485 AND cat.category_id (+)= pce.category_id
1486 AND item.inventory_item_id (+)= pce.item_id
1487 AND item.organization_id (+)= pce.org_id
1488 AND pvs.vendor_site_id (+)= pce.supplier_site_id
1489 AND pce.oper_unit_id = l_oper_unit_id
1490 AND pce.category_id = l_category_id
1491 ORDER BY pce.evaluation_id;
1492
1493 else
1494 OPEN c_evaluation FOR
1495 SELECT pce.evaluation_id,
1496 pvs.vendor_site_code,
1497 hr.name,
1498 cat.concatenated_segments,
1499 item.concatenated_segments,
1500 fu.user_name,
1501 pce.creation_date,
1502 pce.last_update_date
1503 FROM poa_cm_evaluation pce,
1504 hr_operating_units hr,
1505 fnd_user fu,
1506 mtl_categories_kfv cat,
1507 mtl_system_items_kfv item,
1508 po_vendor_sites pvs
1509 WHERE pce.supplier_id = l_supplier_id
1510 AND pce.period_type = l_period_type
1511 AND pce.period_name = l_period_name
1512 AND hr.organization_id (+)= pce.oper_unit_id
1513 AND fu.user_id (+)= pce.evaluated_by
1514 AND cat.category_id (+)= pce.category_id
1515 AND item.inventory_item_id (+)= pce.item_id
1516 AND item.organization_id (+)= pce.org_id
1517 AND pvs.vendor_site_id (+)= pce.supplier_site_id
1518 ORDER BY pce.evaluation_id;
1519 end if;
1520
1521 LOOP
1522
1523 FETCH c_evaluation into v_eval_id, v_supplier_site, v_oper_unit, v_commodity, v_item, v_evaluator, v_creation_date, v_last_update_date;
1524 EXIT WHEN c_evaluation%NOTFOUND;
1525
1526 v_counter := v_counter + 1;
1527
1528 if ((v_counter mod 2) = 0) THEN
1529 htp.p('<TR BGCOLOR=''#ffffff'' >');
1530 else
1531 htp.p('<TR BGCOLOR=''#ffffff'' >');
1532 end if;
1533
1534 if (v_counter = 1) THEN
1535
1536 htp.p('<TD ALIGN=CENTER BGCOLOR=''#FFFFFF''><font class=tabledata> <input type="RADIO" name="POA_CM_EVALUATION_ID" value=' || v_eval_id || ' checked></font></td>');
1537 else
1538 htp.p('<TD ALIGN=CENTER BGCOLOR=''#FFFFFF''><font class=tabledata> <input type="RADIO" name="POA_CM_EVALUATION_ID" value=' || v_eval_id || '></font></td>');
1539 end if;
1540
1541 htp.p('
1542 <TD ALIGN=left><font class=tabledata>' || v_oper_unit || ' </font></td>
1543 <TD ALIGN=left><font class=tabledata>' || v_commodity || ' </font></td>
1544 <TD ALIGN=left><font class=tabledata>' || v_item || '</font> </td>
1545 <TD ALIGN=left><font class=tabledata>' || v_supplier_site || ' </font></td>
1546 <TD ALIGN=left><font class=tabledata>' || v_evaluator || ' </font></td>
1547 <TD ALIGN=left><font class=tabledata>' || v_creation_date || ' </font></td>
1548 <TD ALIGN=left><font class=tabledata>' || v_last_update_date || ' </font></td>
1549 </tr>
1550 ');
1551
1552 END LOOP;
1553 CLOSE c_evaluation;
1554
1555 htp.p('</table></td></tr>');
1556
1557 if (v_counter = 0) then
1558 htp.p('<tr><td align=center><b>Your query returned no records.</b></td></tr>');
1559 end if;
1560
1561 htp.p('</table>');
1562
1563 if (v_counter = 0) then
1564 PrintBottomButtons(l_language, 3);
1565 else
1566 PrintBottomButtons(l_language, 2);
1567 end if;
1568
1569 htp.bodyClose;
1570 htp.htmlClose;
1571
1572 END query_evals;
1573
1574 END poa_cm_eval_scores_icx;