[Home] [Help]
PACKAGE BODY: APPS.AZW_REPORT
Source
1 PACKAGE BODY AZW_REPORT AS
2 /* $Header: AZWREPTB.pls 115.64 2000/03/08 11:22:40 pkm ship $ */
3 /* Global Variables **********************************************************/
4 g_current_mode VARCHAR2(255); -- Wizard Mode
5 g_mode_label fnd_lookups.meaning%TYPE; -- Wizard Mode label
6 g_blank VARCHAR2(8) := ' '; -- for padding in the hierarchy
7 g_indent INTEGER := 3; -- for indentation in hierarchy
8 g_web_agent VARCHAR2(255) := ''; -- URL for the host server
9 /* labels */
10 g_planning VARCHAR2(2000) := NULL; -- Planning Reports
11 g_monitor VARCHAR2(2000) := NULL; -- Status Reports
12 g_related VARCHAR2(2000) := NULL; -- Related Reports
13 g_ipr VARCHAR2(2000) := NULL; -- Implementation Process Report (IPR)
14 g_cpr VARCHAR2(2000) := NULL; -- Context Process Report (CPR)
15 g_ppr VARCHAR2(2000) := NULL; -- Product Process Report (PPR)
16 g_isr VARCHAR2(2000) := NULL; -- Implementation Status Report (ISR)
17 g_upr VARCHAR2(2000) := NULL; -- User Performance Report (UPR)
18 g_ok VARCHAR2(2000) := NULL; -- OK
19 g_ok_hlp VARCHAR2(2000) := NULL; -- OK Button Status Help
20 g_cancel VARCHAR2(2000) := NULL; -- Cancel
21 g_cancel_hlp VARCHAR2(2000) := NULL; -- Cancel Button Status Help
22 g_all VARCHAR2(2000) := NULL; -- All
23 g_phase VARCHAR2(2000) := NULL; -- Phase
24 g_proc_status VARCHAR2(2000) := NULL; -- Process Status
25 g_status VARCHAR2(2000) := NULL; -- Status
26 g_installed VARCHAR2(2000) := NULL; -- Product Installed
27 g_summary VARCHAR2(2000) := NULL; -- Report Summary
28 g_process_group VARCHAR2(2000) := NULL; -- Process Group
29
30 g_num_procs VARCHAR2(2000) := NULL; -- # of Processes
31 g_num_active_procs VARCHAR2(2000) := NULL; -- # of Active Processes
32 g_num_completed_procs VARCHAR2(2000) := NULL; -- # of Complete Processes
33 g_num_notstarted_procs VARCHAR2(2000) := NULL; -- # of Not Started Processes
34
35 g_num_tasks VARCHAR2(2000) := NULL; -- # of tasks worked on
36
37 g_details VARCHAR2(2000) := NULL; -- Report Details
38 g_selected VARCHAR2(2000) := NULL; -- Product Selected
39 g_hierarchy VARCHAR2(2000) := NULL; -- Process Hierarchy
40 g_ctxt_type VARCHAR2(2000) := NULL; -- Context Type
41 g_ctxt_name VARCHAR2(2000) := NULL; -- Context Name
42 g_user VARCHAR2(2000) := NULL; -- User
43 g_duration VARCHAR2(2000) := NULL; -- Duration
44 g_start VARCHAR2(2000) := NULL; -- Start Date
45 g_from VARCHAR2(2000) := NULL; -- From
46 g_to VARCHAR2(2000) := NULL; -- To
47 g_end VARCHAR2(2000) := NULL; -- End Date
48 g_days VARCHAR2(2000) := NULL; -- Days
49 g_dateformat_msg VARCHAR2(2000) := NULL; -- used by user_param
50 g_timeformat VARCHAR2(2000) := NULL; -- HH:MM AM/PM
51 g_atmost fnd_lookups.meaning%TYPE := NULL; -- At most
52 g_atleast fnd_lookups.meaning%TYPE := NULL; -- At least
53 g_description VARCHAR2(2000) := NULL; -- Process Description
54 g_comments VARCHAR2(2000) := NULL; -- Process Comments
55 /* messages */
56 g_welcome_msg VARCHAR2(2000) := NULL; -- welcome message in start_page
57 g_ipr_msg VARCHAR2(2000) := NULL; -- instructions for IPR
58 g_cpr_msg VARCHAR2(2000) := NULL; -- instructions for CPR
59 g_ppr_msg VARCHAR2(2000) := NULL; -- instructions for PPR
60 g_isr_msg VARCHAR2(2000) := NULL; -- instructions for ISR
61 g_upr_msg VARCHAR2(2000) := NULL; -- instructions for UPR
62 g_help_target VARCHAR2(2000) := NULL; -- Help Target
63 g_ipr_desc VARCHAR2(2000) := NULL; -- Short Description for IPR
64 g_cpr_desc VARCHAR2(2000) := NULL; -- Short Description for CPR
65 g_ppr_desc VARCHAR2(2000) := NULL; -- Short Description for PPR
66 g_isr_desc VARCHAR2(2000) := NULL; -- Short Description for ISR
67 g_upr_desc VARCHAR2(2000) := NULL; -- Short Description for UPR
68 g_param_hdr VARCHAR2(2000) := NULL; -- Report Parameters header
69 g_param_note VARCHAR2(2000) := NULL; -- Report Parameters note
70 g_mn_menu VARCHAR2(2000) := NULL; -- Main Icon Menu Balloon Help
71 g_exit VARCHAR2(2000) := NULL; -- Exit Icon Balloon Help
72 g_help VARCHAR2(2000) := NULL; -- Help Icon Balloon Help
73 g_as_of VARCHAR2(2000) := NULL; -- As of
74
75 g_no_prod_inst VARCHAR2(2000) := NULL; -- no products installed
76 g_no_prod_sel VARCHAR2(2000) := NULL; -- no products selected
77 g_back_top VARCHAR2(2000) := NULL; -- Back to top
78
79
80 g_report_legend VARCHAR2(2000) := NULL; -- Report Legend
81 g_group_legend VARCHAR2(2000) := NULL; -- Process Group legend desc
82 g_subgrp_legend VARCHAR2(2000) := NULL; -- Process Sub-Group legend desc
83 g_process_legend VARCHAR2(2000) := NULL; -- Process legend desc
84 g_task_legend VARCHAR2(2000) := NULL; -- Task legend desc
85
86 g_task_details VARCHAR2(2000) := NULL; -- Task Step Details
87 g_step_name VARCHAR2(2000) := NULL; -- Step Name
88 g_step_response VARCHAR2(2000) := NULL; -- Response
89
90 g_task_params VARCHAR2(2000) := NULL; -- Task Parameters
91
92 g_step_details VARCHAR2(2000) := NULL; -- Step Details
93 g_process VARCHAR2(2000) := NULL; -- Process
94 g_steps VARCHAR2(2000) := NULL; -- Steps
95 g_step_msg VARCHAR2(2000) := NULL; -- Message
96
97 g_step_all_procs VARCHAR2(2000) := NULL; -- Step details for all processes
98
99 g_active_by VARCHAR2(2000) := NULL; -- Active By
100
101
102
103
104 /* Java Script Messages */
105 -- Select a product before OK
106 g_js_slct_prd VARCHAR2(2000) := NULL;
107 -- Select the Time Elapsed option
108 g_js_slct_time_elapsed VARCHAR2(2000) := NULL;
109 g_image_path CONSTANT VARCHAR2(20) := '/OA_MEDIA';
110
111 --
112 -- Used by display_process_steps to display page header only once
113 -- if a subprocess exists.
114 --
115 g_FirstTime BOOLEAN := TRUE;
116 /* Type Definitions **********************************************************/
117
118 TYPE id_tbl_t IS TABLE OF NUMBER(15) INDEX BY BINARY_INTEGER;
119
120 TYPE PlanProcessGroupRec IS RECORD(
121 phase AZ_PLANNING_REPORTS.PHASE%TYPE,
122 display_name AZ_PLANNING_REPORTS.DISPLAY_NAME%TYPE,
123 processes_count PLS_INTEGER,
124 node_id AZ_PLANNING_REPORTS.NODE_ID%TYPE
125 );
126
127 TYPE StatusProcessGroupRec IS RECORD(
128 node_id AZ_MONITOR_REPORTS.NODE_ID%TYPE,
129 display_name AZ_MONITOR_REPORTS.DISPLAY_NAME%TYPE,
130 processes_count INTEGER,
131 active_procs_count INTEGER,
132 complete_procs_count INTEGER,
133 not_started_procs_count INTEGER
134 );
135 -- used by report summary
136 TYPE PlanProcessGroups IS TABLE OF PlanProcessGroupRec
137 INDEX BY BINARY_INTEGER;
138 TYPE StatusProcessGroups IS TABLE OF StatusProcessGroupRec
139 INDEX BY BINARY_INTEGER;
140
141 TYPE HierarchyLevels IS TABLE OF VARCHAR2(240)
142 INDEX BY BINARY_INTEGER;
143
144 g_upper_process_names HierarchyLevels;
145 g_curr_process_level PLS_INTEGER;
146 g_inst_count PLS_INTEGER := 0;
147 g_instance_ids id_tbl_t;
148 g_prev_act_name wf_process_activities.activity_name%TYPE := NULL;
149
150 /* Private Procedure Declarations ********************************************/
151
152 PROCEDURE get_context_processes(p_context IN VARCHAR2);
153 PROCEDURE get_implementation_processes(p_phase IN NUMBER);
154 PROCEDURE get_monitor_group_parent(p_group_id IN VARCHAR2,
155 p_user IN VARCHAR2);
156 PROCEDURE get_planning_group_parent(p_group_id IN VARCHAR2,
157 p_phase IN NUMBER);
158 PROCEDURE get_product_processes(p_application_id IN NUMBER);
159 PROCEDURE get_report_title_desc(p_rpt_code IN VARCHAR2,
160 p_rpt_title IN OUT VARCHAR2,
161 p_rpt_desc IN OUT VARCHAR2);
162 PROCEDURE get_status_groups(p_status IN VARCHAR2);
163 PROCEDURE get_status_processes(p_status IN VARCHAR2);
164 PROCEDURE get_status_tasks(p_status IN VARCHAR2);
165 PROCEDURE get_user_trees_by_atleast(p_user IN VARCHAR2,
166 p_status IN VARCHAR2,
167 p_duration IN NUMBER);
168 PROCEDURE get_user_trees_by_atmost(p_user IN VARCHAR2,
169 p_status IN VARCHAR2,
170 p_duration IN NUMBER);
171 PROCEDURE get_user_trees_by_period(p_user IN VARCHAR2,
172 p_status IN VARCHAR2,
173 p_startdate IN DATE,
174 p_enddate IN DATE);
175 PROCEDURE get_translated_labels;
176 PROCEDURE get_web_agent;
177 PROCEDURE get_process_type_name (p_node_id IN VARCHAR2,
178 p_item_type OUT VARCHAR2,
179 p_process_name OUT VARCHAR2);
180 PROCEDURE get_task_type_key (p_node_id IN VARCHAR2,
181 p_item_type OUT VARCHAR2,
182 p_item_key OUT VARCHAR2);
183 PROCEDURE print_html_style;
184 PROCEDURE print_imp_start_page;
185 PROCEDURE print_upgrade_start_page;
186 PROCEDURE print_context_subheader(p_context IN VARCHAR2);
187 PROCEDURE print_product_subheader(p_ids IN id_tbl_t);
188 PROCEDURE print_ipr_report_parameters (p_phase IN VARCHAR2);
189 PROCEDURE print_ipr_installed_products (p_phase IN VARCHAR2);
190 PROCEDURE print_planning_reports_summary (p_phase IN VARCHAR2);
191 PROCEDURE populate_process_groups_array (p_phase IN NUMBER,
192 process_groups IN OUT NOCOPY PlanProcessGroups);
193 PROCEDURE print_status_subheader (p_status IN VARCHAR2);
194 PROCEDURE print_isr_installed_products;
195 PROCEDURE print_isr_report_summary (p_status IN VARCHAR2);
196 PROCEDURE populate_isr_process_groups (
197 process_groups IN OUT NOCOPY StatusProcessGroups);
198 PROCEDURE print_user_subheader(p_user IN VARCHAR2,
199 p_status IN VARCHAR2,
200 p_time_or_period IN VARCHAR2,
201 p_operator IN VARCHAR2,
202 p_days IN VARCHAR2,
203 p_start IN VARCHAR2,
204 p_end IN VARCHAR2);
205 PROCEDURE print_user_report_summary;
206 PROCEDURE print_param_page_header(p_title IN VARCHAR2,
207 p_msg IN VARCHAR2,
208 p_mode_label IN VARCHAR2 DEFAULT NULL);
209 PROCEDURE print_param_page_footer;
210 PROCEDURE print_selected_prods_table (p_ids IN id_tbl_t);
211 PROCEDURE print_footer_separator_line;
212 PROCEDURE print_ok_cancel_buttons(p_ok_action IN VARCHAR2);
213
214 PROCEDURE print_report_header(p_title IN VARCHAR2,
215 p_type IN BOOLEAN,
216 p_param_page IN VARCHAR2);
217 PROCEDURE print_related_reports(p_rpt1 IN VARCHAR2,
218 p_rpt2 IN VARCHAR2 DEFAULT NULL);
219 PROCEDURE print_welcome_header(p_title IN VARCHAR2);
220 PROCEDURE print_pp_jscripts;
221 PROCEDURE print_up_jscripts;
222 PROCEDURE print_back_to_top (p_col_span IN NUMBER);
223 PROCEDURE raise_error_msg (
224 ErrCode IN NUMBER,
225 ErrMsg IN VARCHAR2,
226 ProcedureName IN VARCHAR2,
227 Statement IN VARCHAR2);
228
229 PROCEDURE print_time_stamp (v_string VARCHAR2);
230 PROCEDURE print_legend_link;
231 PROCEDURE print_legend (p_status BOOLEAN DEFAULT FALSE);
232 PROCEDURE print_step_details_header (p_display_msg IN VARCHAR2 DEFAULT NULL);
233 PROCEDURE print_activity(
234 p_selected_products IN VARCHAR2,
235 p_instance_id IN NUMBER,
236 p_display_msg IN VARCHAR2);
237 PROCEDURE print_js_open_url (blnStep IN BOOLEAN DEFAULT FALSE);
238 PROCEDURE print_task_steps(
239 p_item_type IN VARCHAR2,
240 p_item_key IN VARCHAR2,
241 p_process_name IN VARCHAR2,
242 p_process_level IN NUMBER);
243
244 /* Function Definitions ******************************************************/
245
246 /*
247 **
248 ** CHECK_ACTIVITY_PRODUCTS
249 ** =======================
250 **
251 ** Private Function.
252 ** It checks if any of the selected products is in
253 ** the defined list of installed products for the
254 ** current activity.
255 ** It returns Y or N to match
256 **
257 */
258 FUNCTION check_activity_products (
259 p_selected_products IN VARCHAR2,
260 p_instance_id IN NUMBER) RETURN VARCHAR2 IS
261
262 v_app_id NUMBER;
263 v_cnt BINARY_INTEGER;
264 v_ids id_tbl_t;
265 v_inst_prods wf_activity_attr_values.TEXT_VALUE%TYPE;
266
267 BEGIN
268
269 v_cnt := 1;
270 v_app_id := azw_proc.parse_application_ids(p_selected_products, v_cnt);
271 WHILE (v_app_id > -1) LOOP
272 v_ids(v_cnt) := v_app_id;
273 v_cnt := v_cnt + 1;
274 v_app_id := azw_proc.parse_application_ids(p_selected_products, v_cnt);
275 END LOOP;
276
277 SELECT TEXT_VALUE INTO v_inst_prods
278 FROM wf_activity_attr_values
279 WHERE NAME = 'AZW_IA_WFPROD'
280 AND PROCESS_ACTIVITY_ID = p_instance_id
281 AND TEXT_VALUE <> 'AZW_IA_WFPROD';
282
283 FOR v_cnt IN 1..v_ids.COUNT LOOP
284 IF (INSTR(v_inst_prods, TO_CHAR(v_ids(v_cnt))) > 0) THEN
285 return('Y');
286 END IF;
287 END LOOP;
288 return('N');
289 EXCEPTION
290 WHEN NO_DATA_FOUND THEN
291 RETURN ('N');
292 WHEN application_exception THEN
293 RAISE;
294 WHEN OTHERS THEN
295 raise_error_msg (SQLCODE, SQLERRM,
296 'CHECK_ACTIVITY_PRODUCTS', '');
297 END check_activity_products;
298
299 /*------------------------------------------------------------------------
300 * GET_APPLICATION_NAME
301 *
302 * Private function.
303 * Given an application id, return the application name.
304 *-----------------------------------------------------------------------*/
305 FUNCTION get_application_name(p_app_id IN NUMBER) RETURN VARCHAR2 IS
306 v_name fnd_application_vl.application_name%TYPE := TO_CHAR(p_app_id);
307
308 BEGIN
309 SELECT application_name
310 INTO v_name
311 FROM fnd_application_vl
312 WHERE application_id = p_app_id;
313
314 RETURN v_name;
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317 RETURN v_name;
318 WHEN application_exception THEN
319 RAISE;
320 WHEN OTHERS THEN
321 raise_error_msg (SQLCODE, SQLERRM,
322 'GET_APPLICATION_NAME', '');
323 END get_application_name;
324
325 /*------------------------------------------------------------------------
326 * GET_APPLICATION_SHORT_NAME
327 *
328 * Private function.
329 * Given an application id, return the application short name.
330 *-----------------------------------------------------------------------*/
331 FUNCTION get_application_short_name(p_app_id IN NUMBER) RETURN VARCHAR2 IS
332 v_name fnd_application_vl.application_short_name%TYPE := TO_CHAR(p_app_id);
333
334 BEGIN
335 SELECT application_short_name
336 INTO v_name
337 FROM fnd_application_vl
338 WHERE application_id = p_app_id;
339
340 RETURN v_name;
341 EXCEPTION
342 WHEN NO_DATA_FOUND THEN
343 RETURN v_name;
344 WHEN application_exception THEN
345 RAISE;
346 WHEN OTHERS THEN
347 raise_error_msg (SQLCODE, SQLERRM,
348 'GET_APPLICATION_SHORT_NAME', '');
349 END get_application_short_name;
350
351 /*------------------------------------------------------------------------
352 * GET_REPORT_PROCEDURE
353 *
354 * Private function.
355 * Given a report code, return the name of the package and the procedure
356 * that generates the parameter entry page for the report.
357 *-----------------------------------------------------------------------*/
358 FUNCTION get_report_procedure(p_rpt_code IN VARCHAR2) RETURN VARCHAR2 IS
359 v_value VARCHAR2(240) := p_rpt_code;
360
361 BEGIN
362 IF (p_rpt_code LIKE '%IPR') THEN
363 v_value := 'azw_report.implementation_param_page';
364 ELSIF (p_rpt_code LIKE '%CPR') THEN
365 v_value := 'azw_report.context_param_page';
366 ELSIF (p_rpt_code LIKE '%PPR') THEN
367 v_value := 'azw_report.product_param_page';
368 ELSIF (p_rpt_code LIKE '%ISR') THEN
369 v_value := 'azw_report.status_param_page';
370 ELSIF (p_rpt_code LIKE '%IPRR') THEN
371 v_value := 'azw_report.implementation_report';
372 ELSIF (p_rpt_code LIKE '%CPRR') THEN
373 v_value := 'azw_report.context_report';
374 ELSIF (p_rpt_code LIKE '%PPRR') THEN
375 v_value := 'azw_report.product_report';
376 ELSIF (p_rpt_code LIKE '%ISRR') THEN
377 v_value := 'azw_report.status_report';
378 ELSIF (p_rpt_code LIKE '%UPRR') THEN
379 v_value := 'azw_report.user_report';
380 ELSE -- (p_rpt_code LIKE '%UPR') THEN
381 v_value := 'azw_report.user_param_page';
382 END IF;
383 RETURN v_value;
384 EXCEPTION
385 WHEN application_exception THEN
386 RAISE;
387 WHEN OTHERS THEN
388 raise_error_msg (SQLCODE, SQLERRM,
389 'GET_REPORT_PROCEDURE', '');
390 END get_report_procedure;
391
392 /*------------------------------------------------------------------------
393 * GET_TRANSLATION
394 *
395 * Private function.
396 * Given a lookup type and a lookup code, returns its translated meaning.
397 *-----------------------------------------------------------------------*/
398 FUNCTION get_translation(p_type IN VARCHAR2, p_code IN VARCHAR2)
399 RETURN VARCHAR2 IS
400 v_meaning fnd_lookups.meaning%TYPE := p_code;
401
402 BEGIN
403 SELECT meaning
404 INTO v_meaning
405 FROM fnd_lookups
406 WHERE lookup_type = p_type
407 AND lookup_code = p_code;
408
409 RETURN v_meaning;
410 EXCEPTION
411 WHEN NO_DATA_FOUND THEN
412 RETURN v_meaning;
413 WHEN application_exception THEN
414 RAISE;
415 WHEN OTHERS THEN
416 raise_error_msg (SQLCODE, SQLERRM,
417 'GET_TRANSLATION', '');
418 END get_translation;
419
420 /*------------------------------------------------------------------------
421 * LPAD_NBSP
422 *
423 * Private function.
424 * Given a hierarchy node and its level, returns a string left-padded with
425 * ' ' based on the level.
426 *-----------------------------------------------------------------------*/
427 FUNCTION lpad_nbsp(p_level IN NUMBER) RETURN VARCHAR2 IS
428 v_node VARCHAR2(2000);
429 v_cnt NUMBER;
430 BEGIN
431 FOR v_cnt IN 1..(p_level-1) LOOP
432 v_node := ' '|| v_node;
433 END LOOP;
434 RETURN v_node;
435 EXCEPTION
436 WHEN application_exception THEN
437 RAISE;
438 WHEN OTHERS THEN
439 raise_error_msg (SQLCODE, SQLERRM,
440 'LPAD_NBSP', '');
441 END lpad_nbsp;
442
443 /*
444 **
445 ** GET_PARENT_STRUCTURE
446 ** ====================
447 **
448 ** Returns all the parents specified in the array till
449 ** the specified level, in a directory structure.
450 ** For example it will return
451 ** "\Common Applications\System Administration\" for the
452 ** Process "Printers"
453 ** All the upper level parent names must have already been
454 ** populated in the passed array.
455 **
456 */
457 FUNCTION get_parent_structure (
458 p_upper_group_names IN HierarchyLevels,
459 p_level IN NUMBER,
460 p_seperator IN VARCHAR2 DEFAULT '\') RETURN VARCHAR2 IS
461 i PLS_INTEGER;
462 v_output VARCHAR2(4000);
463 BEGIN
464 FOR i IN 1..p_level LOOP
465 IF (i = p_level) THEN
466 v_output := v_output || p_upper_group_names(i);
467 ELSE
468 v_output := v_output || p_upper_group_names(i) || p_seperator;
469 END IF;
470 END LOOP;
471 return (v_output);
472 EXCEPTION
473 WHEN application_exception THEN
474 RAISE;
475 WHEN OTHERS THEN
476 raise_error_msg (SQLCODE, SQLERRM,
477 'GET_PARENT_STRUCTURE', '');
478 END get_parent_structure;
479
480 /*
481 **
482 ** IS_ACT_NOTFOUND
483 ** ===============
484 **
485 ** It checks if the specified instance ID has already been inserted
486 ** in the g_instance_ids PL/SQL table. Not to process an activity twice.
487 ** Called from display_process_steps, print_activity.
488 **
489 */
490
491 FUNCTION is_act_notfound(p_instance_id IN NUMBER) return boolean IS
492 i PLS_INTEGER;
493 BEGIN
494
495 IF g_inst_count > 0 THEN
496 FOR i IN 1..g_inst_count LOOP
497 IF (g_instance_ids(i) = p_instance_id) THEN
498 return FALSE;
499 END IF;
500 END LOOP;
501 END IF;
502 return TRUE;
503
504 EXCEPTION
505 WHEN application_exception THEN
506 RAISE;
507 WHEN OTHERS THEN
508 raise_error_msg (SQLCODE, SQLERRM,
509 'IS_ACT_NOTFOUND', '');
510
511 END is_act_notfound;
512
513 /*
514 **
515 ** URL_ENCODE
516 ** ==========
517 **
518 ** Private Function.
519 ** It replaces all the spaces in the URL to '+' sign.
520 **
521 */
522 FUNCTION url_encode (p_url IN VARCHAR2) RETURN VARCHAR2 IS
523 v_url VARCHAR2(4000):= p_url;
524
525 BEGIN
526 v_url := REPLACE(v_url, ':', '%3A');
527 v_url := REPLACE(v_url, '&', '%26');
528 v_url := REPLACE(v_url, ' ', '+');
529 return(v_url);
530 EXCEPTION
531 WHEN application_exception THEN
532 RAISE;
533 WHEN OTHERS THEN
534 raise_error_msg (SQLCODE, SQLERRM,
535 'URL_ENCODE', '');
536 END url_encode;
537
538 /*
539 **
540 ** FORMAT_STEP_BODY
541 ** ================
542 **
543 ** Private Function.
544 ** When a formated text is displayed in HTML format the text is shown as a
545 ** big blob of text. This function is created to retain the format and not
546 ** to lose the basic text format like carriage returns, line feeds, tabs, ect.
547 ** It also replaces the '<' and '>' to their HTML representation.
548 **
549 */
550
551 FUNCTION format_step_body(p_body IN VARCHAR2) RETURN VARCHAR2 IS
552
553 v_body VARCHAR2(4000):= p_body;
554
555 BEGIN
556
557 v_body := REPLACE(v_body, '>', '>');
558 v_body := REPLACE(v_body, '<', '<');
559 v_body := REPLACE(v_body, FND_GLOBAL.LOCAL_CHR(10), '<BR>');
560 v_body := REPLACE(v_body, FND_GLOBAL.LOCAL_CHR(13), NULL);
561
562 return(v_body);
563 EXCEPTION
564 WHEN application_exception THEN
565 RAISE;
566 WHEN OTHERS THEN
567 raise_error_msg (SQLCODE, SQLERRM,
568 'FORMAT_STEP_BODY', '');
569 END format_step_body;
570
571 /* Procedure Definitions *****************************************************/
572
573 /*
574 **
575 ** ADD_INSTANCE_TO_ARRAY
576 ** =====================
577 **
578 ** Adds a new element at the end of the g_instance_ids.
579 ** which is used to keep track if the current instance has
580 ** been processed or not. Not to tarce an activity twice.
581 **
582 */
583
584 PROCEDURE add_instance_to_array(p_instance_id IN NUMBER) IS
585
586 BEGIN
587
588 g_instance_ids(g_inst_count + 1) := p_instance_id;
589 g_inst_count := g_inst_count + 1;
590
591 EXCEPTION
592 WHEN application_exception THEN
593 RAISE;
594 WHEN OTHERS THEN
595 raise_error_msg (SQLCODE, SQLERRM,
596 'ADD_INSTANCE_TO_ARRAY', '');
597
598 END add_instance_to_array;
599
600 /*------------------------------------------------------------------------
601 * CONTEXT_PARAM_PAGE
602 *
603 * Public procedure.
604 * Generates parameter entry page in HTML for the context process report.
605 * Performs the following steps:
606 * 1. Get the URL for host server and all display strings if the URL is ''.
607 * 2. Print the title and the instruction as the header.
608 * 3. Display all the choices of context types in a drop down list, making
609 * BG as the default option.
610 * 4. Print the OK and Cancel buttons as the footer. OK buttons calls the
611 * context report ans passes the user selected context type; Cancel
612 * button returns to the starting welcome page.
613 *-----------------------------------------------------------------------*/
614 PROCEDURE context_param_page IS
615
616 CURSOR context_types_cursor IS
617 SELECT lookup_code,
618 meaning,
619 DECODE(lookup_code, 'NONE', 1, 'BG', 2, 'SOB', 3, 'OU', 4, 5)
620 display_order
621 FROM fnd_lookups
622 WHERE lookup_type = 'AZ_CONTEXT_TYPE'
623 ORDER BY display_order;
624
625 BEGIN
626 g_help_target := get_report_procedure('AZW_RPT_CPR');
627 IF (g_web_agent IS NULL) THEN
628 get_web_agent;
629 get_translated_labels;
630 END IF;
631
632 print_param_page_header(g_cpr, g_cpr_msg, NULL);
633
634 htp.p('<table align="center" border="0" cellpadding="0" ' ||
635 'cellspacing="2" width="96%">');
636 htp.p('<tr><td colspan=4><br></td>');
637 htp.p('<form name="Form1" method="post" ' ||
638 'action="azw_report.context_report"></tr>');
639 htp.p('<tr><td align=right width=50%><font class=normal>' ||
640 g_ctxt_type || '</font></td>');
641 htp.p('<td align=left colspan=3><select name="p_context" size=1>');
642 -- construct pop-up list of context types BG, IO, OU, SOB and NONE
643 -- if v_context.lookup_code like '%BG%' make it default
644
645 FOR one_context IN context_types_cursor LOOP
646 IF (one_context.lookup_code LIKE '%BG%') THEN
647 htp.p('<option selected value="'||one_context.lookup_code||'"> '||
648 one_context.meaning || '</option>');
649 ELSE
650 htp.p('<option value="'||one_context.lookup_code||'"> '||
651 one_context.meaning || '</option>');
652 END IF;
653 END LOOP;
654 htp.p('</select></td></tr>');
655
656 -- print OK and Cancel buttons
657 print_param_page_footer;
658
659 EXCEPTION
660 WHEN application_exception THEN
661 RAISE;
662 WHEN OTHERS THEN
663 raise_error_msg (SQLCODE, SQLERRM,
664 'CONTEXT_PARAM_PAGE', '');
665 END context_param_page;
666
667 /*------------------------------------------------------------------------
668 * CONTEXT_REPORT
669 *
670 * Public procedure. Invoked by the OK button in context_param_page.
671 * Generates the context process report in HTML. It performs the following
672 * steps:
673 * 1. Get the URL for host server and all display strings if the URL is ''.
674 * 2. Print report header and subheader.
675 * 4. Print HTML Table opening tag and header.
676 * 5. Get all processes of the specified context into the intermediate
677 * table.
678 * 6. Retrieve the trees in the intermediate table. For each node, print
679 * Table Row and Table Data.
680 * 7. Print Table closing tag.
681 * 8. Print links to related reports.
682 *-----------------------------------------------------------------------*/
683 PROCEDURE context_report(p_context IN VARCHAR2) IS
684 v_count NUMBER;
685 CURSOR hierarchies_cursor IS
686 SELECT LPAD(g_blank, g_indent*(LEVEL-1))||display_name hierarchy,
687 node_type,
688 context_type_name,
689 description,
690 parent_node_id,
691 node_id,
692 LEVEL
693 FROM az_planning_reports
694 START WITH parent_node_id IS NULL
695 CONNECT BY PRIOR node_id = parent_node_id
696 AND PRIOR phase = phase;
697
698 BEGIN
699 print_time_stamp('Start the report');
700 -- dbms_output.put_line('context_report: '||p_context);
701 g_help_target := get_report_procedure('AZW_RPT_CPRR');
702 IF (g_web_agent IS NULL) THEN
703 get_web_agent;
704 get_translated_labels;
705 END IF;
706
707 print_report_header(g_cpr, TRUE, 'azw_report.context_param_page');
708
709 print_context_subheader(p_context);
710
711 get_context_processes(p_context);
712
713 --
714 -- Display the report summary
715 --
716 print_planning_reports_summary(-1);
717
718 print_legend_link;
719
720 --
721 -- Display the Report Details
722 --
723 htp.p('<TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
724 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="3">' ||
725 '<FONT CLASS="tableHeader">'|| g_details ||'</FONT></TD></TR>');
726 htp.p('<TR>');
727 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
728 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_hierarchy ||'</FONT></TH>');
729 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
730 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_ctxt_type ||'</FONT></TH>');
731 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
732 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_description ||'</FONT></TH>');
733
734 v_count := 0;
735 FOR one_node IN hierarchies_cursor LOOP
736 htp.tableRowOpen;
737 IF (one_node.parent_node_id IS NULL) THEN
738 IF (v_count > 0) THEN
739 print_back_to_top(2);
740 END IF;
741 htp.p('<TD ALIGN="LEFT" COLSPAN="3" BGCOLOR="#666666" ' ||
742 'NOWRAP><i><FONT COLOR="#FFFFFF"><A NAME="PH-1_'||
743 one_node.node_id || '">' ||
744 one_node.hierarchy || '</A></FONT></i></TD>');
745 ELSIF (one_node.node_type = 'G') THEN
746 htp.tableData('<A NAME="'|| v_count ||'"><i>'||
747 lpad_nbsp(one_node.level) || one_node.hierarchy ||
748 '</A></i>', 'LEFT', '', 'NOWRAP');
749 htp.tableData('<i>'|| one_node.context_type_name || '</i>');
750 v_count := v_count + 1;
751 ELSE
752 htp.tableData('<b>'|| lpad_nbsp(one_node.level) || one_node.hierarchy ||
753 '</b>', 'LEFT', '', 'NOWRAP');
754 htp.tableData(one_node.context_type_name);
755 htp.tableData(one_node.description);
756 END IF;
757 htp.tableRowClose;
758 END LOOP;
759 print_back_to_top(2);
760 -- print Table closing tag
761 htp.tableClose;
762 -- print report legend
763 print_legend;
764
765 --
766 -- Print related report links
767 --
768 print_related_reports('AZW_RPT_IPR', 'AZW_RPT_PPR');
769 print_time_stamp('End the report');
770
771 -- COMMIT;
772
773 EXCEPTION
774 WHEN NO_DATA_FOUND THEN
775 -- print Table closing tag
776 htp.tableClose;
777 print_related_reports('AZW_RPT_IPR', 'AZW_RPT_PPR');
778 WHEN application_exception THEN
779 RAISE;
780 WHEN OTHERS THEN
781 raise_error_msg (SQLCODE, SQLERRM,
782 'CONTEXT_REPORT', '');
783 END context_report;
784
785 /*------------------------------------------------------------------------
786 * GET_CONTEXT_PROCESSES
787 *
788 * Private procedure. Called by context_report.
789 * Populate context process hierarchies in the intermediate table.
790 * It performs the following steps:
791 * 1. Get all distinct processes of the given context type from
792 * az_processes_all_v into the intermediate table.
793 * 2. Find all distinct parent ids for the processes found in Step 1.
794 * 3. For each parent id in Step 2, get all distinct hierarchy ancestors
795 * in az_groups_v into the intermediate table.
796 *-----------------------------------------------------------------------*/
797 PROCEDURE get_context_processes(p_context IN VARCHAR2) IS
798 CURSOR processes_cursor IS
799 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
800 ||'.'||apv.process_name node_id,
801 apv.display_name,
802 apv.context_type_name,
803 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
804 parent_node_id,
805 apv.description
806 FROM az_processes_all_v apv,
807 az_groups ag
808 WHERE (apv.context_type = p_context OR apv.context_type = 'NONE')
809 AND apv.process_type = 'IMP'
810 AND apv.parent_id = ag.group_id
811 AND ag.process_type = apv.process_type;
812
813 CURSOR parents_cursor IS
814 SELECT DISTINCT apv.parent_id
815 FROM az_processes_all_v apv
816 WHERE apv.context_type = p_context OR apv.context_type = 'NONE';
817
818 v_locator NUMBER;
819 BEGIN
820 v_locator := 1;
821 FOR one_proc IN processes_cursor LOOP
822 INSERT INTO az_planning_reports
823 (NODE_ID, PHASE, NODE_TYPE, DISPLAY_NAME,
824 CONTEXT_TYPE_NAME,PARENT_NODE_ID, DESCRIPTION)
825 VALUES
826 (one_proc.node_id, -1, 'P', one_proc.display_name,
827 one_proc.context_type_name, one_proc.parent_node_id, one_proc.description);
828 END LOOP;
829
830 v_locator := 2;
831 -- kick off recursive search for parents
832 FOR one_process IN parents_cursor LOOP
833 get_planning_group_parent(one_process.parent_id, -1);
834 END LOOP;
835
836 EXCEPTION
837 WHEN application_exception THEN
838 RAISE;
839 WHEN OTHERS THEN
840 raise_error_msg (SQLCODE, SQLERRM,
841 'GET_CONTEXT_PROCESSES', 'v_locator := ' || v_locator);
842 END get_context_processes;
843
844 /*------------------------------------------------------------------------
845 * GET_IMPLEMENTATION_PROCESSES
846 *
847 * Private procedure. Called by implementation_report.
848 * Populate implementation process hierarchies in the intermediate table.
849 * It performs the following steps:
850 * 1. Get all distinct processes of the given phase from az_processes_all_v
851 * and az_flow_phases_v into the intermediate table.
852 * 2. Find all distinct parent ids for the processes found in Step 1.
853 * 3. For each parent id in Step 2, get all distinct hierarchy ancestors
854 * in az_groups_v into the intermediate table.
855 *-----------------------------------------------------------------------*/
856 PROCEDURE get_implementation_processes(p_phase IN NUMBER)
857 IS
858 CURSOR processes_cursor IS
859 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||
860 apv.item_type ||'.'||apv.process_name node_id,
861 apv.display_name,
862 TO_CHAR(agv.display_order, '0000')||'.'|| apv.parent_id parent_node_id,
863 apv.description
864 FROM az_processes_all_v apv,
865 az_groups agv,
866 az_flow_phases_v afpv
867 WHERE afpv.phase = p_phase
868 AND afpv.item_type = apv.item_type
869 AND afpv.process_name = apv.process_name
870 AND apv.parent_id = agv.group_id
871 AND apv.process_type = 'IMP'
872 AND agv.process_type = apv.process_type;
873
874 CURSOR parents_cursor IS
875 SELECT DISTINCT apv.parent_id
876 FROM az_processes apv,
877 az_flow_phases_v afpv
878 WHERE apv.item_type = afpv.item_type
879 AND apv.process_name = afpv.process_name
880 AND apv.process_type = 'IMP'
881 AND afpv.phase = p_phase;
882
883 v_locator NUMBER;
884 BEGIN
885 v_locator := 1;
886 FOR one_process IN processes_cursor LOOP
887 INSERT INTO az_planning_reports
888 (NODE_ID, PHASE, NODE_TYPE, DISPLAY_NAME,
889 CONTEXT_TYPE_NAME,PARENT_NODE_ID, DESCRIPTION)
890 VALUES
891 (one_process.node_id, p_phase, 'P', one_process.display_name, '',
892 one_process.parent_node_id, one_process.description);
893 END LOOP;
894 v_locator := 2;
895 -- kick off the recursive search for parents
896 FOR one_process IN parents_cursor LOOP
897 get_planning_group_parent(one_process.parent_id, p_phase);
898 END LOOP;
899
900 EXCEPTION
901 WHEN application_exception THEN
902 RAISE;
903 WHEN OTHERS THEN
904 raise_error_msg (SQLCODE, SQLERRM,
905 'GET_IMPLEMENTATION_PROCESSES', 'v_locator := ' || v_locator);
906 END get_implementation_processes;
907
908 /*------------------------------------------------------------------------
909 * GET_MONITOR_GROUP_PARENT
910 *
911 * Private procedure. Called by reports that use az_monitor_reports as the
912 * intermediate data storage (get_user_trees_by_period,
913 * get_user_trees_by_atleast, get_user_trees_by_atmost, get_status_groups).
914 * Given a group id, recursively get all its group parents into the
915 * intermediate table if the parent does not exist in the table.
916 *-----------------------------------------------------------------------*/
917 PROCEDURE get_monitor_group_parent(p_group_id IN VARCHAR2,
918 p_user IN VARCHAR2)
919 IS
920 CURSOR group_parent_cursor IS
921 SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id,
922 agv.display_name,
923 TO_CHAR(ag.display_order, '0000')||'.'|| agv.hierarchy_parent_id parent_node_id,
924 agv.status,
925 agv.hierarchy_parent_id h_parent_id
926 FROM az_groups_v agv,
927 az_groups ag
928 WHERE agv.group_id = p_group_id
929 AND agv.process_type = g_current_mode
930 AND ag.process_type = g_current_mode
931 AND agv.hierarchy_parent_id = ag.group_id
932 UNION
933 SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id,
934 agv.display_name,
935 NULL parent_node_id,
936 agv.status,
937 NULL h_parent_id
938 FROM az_groups_v agv
939 WHERE agv.group_id = p_group_id
940 AND agv.process_type = g_current_mode
941 AND agv.hierarchy_parent_id IS NULL;
942
943 v_exist_cnt NUMBER := -1;
944 v_group group_parent_cursor%ROWTYPE;
945 v_status fnd_lookups.meaning%TYPE;
946 v_locator NUMBER;
947 BEGIN
948 v_locator := 1;
949 OPEN group_parent_cursor;
950 FETCH group_parent_cursor INTO v_group;
951
952 v_locator := 2;
953 SELECT COUNT(*)
954 INTO v_exist_cnt
955 FROM az_monitor_reports amr
956 WHERE amr.node_id = v_group.node_id
957 AND amr.assigned_user = p_user;
958
959 IF (v_exist_cnt = 0) THEN
960 v_status := get_translation('AZ_PROCESS_STATUS', v_group.status);
961 v_locator := 3;
962 INSERT INTO az_monitor_reports
963 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
964 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
965 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
966 VALUES
967 (v_group.node_id, p_user, 'G', v_group.display_name, '',
968 '', v_group.parent_node_id, v_status, '', '', '', NULL);
969 v_locator := 4;
970 CLOSE group_parent_cursor;
971 IF (v_group.h_parent_id IS NOT NULL) THEN
972 get_monitor_group_parent(v_group.h_parent_id, p_user);
973 END IF;
974 ELSE -- the node is already in the table
975 CLOSE group_parent_cursor;
976 END IF;
977
978 EXCEPTION
979 WHEN application_exception THEN
980 RAISE;
981 WHEN OTHERS THEN
982 raise_error_msg (SQLCODE, SQLERRM,
983 'GET_MONITOR_GROUP_PARENT', 'v_locator := ' || v_locator);
984 END get_monitor_group_parent;
985
986 /*------------------------------------------------------------------------
987 * GET_PLANNING_GROUP_PARENT
988 *
989 * Private procedure. Called by reports that use az_planning_reports as the
990 * intermediate data storage (get_implementation_processes,
991 * get_context_processes, get_product_processes).
992 * Given a group id, recursively get all its group parents into the
993 * intermediate table if the parent does not exist in the table.
994 *-----------------------------------------------------------------------*/
995 PROCEDURE get_planning_group_parent(p_group_id IN VARCHAR2,
996 p_phase IN NUMBER)
997 IS
998 CURSOR group_parent_cursor IS
999 SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id,
1000 agv.display_name,
1001 DECODE(agv.hierarchy_parent_id, '', '',
1002 TO_CHAR(ag.display_order, '0000')||'.'||
1003 agv.hierarchy_parent_id) parent_node_id,
1004 agv.hierarchy_parent_id h_parent_id
1005 FROM az_groups_v agv,
1006 az_groups ag
1007 WHERE agv.group_id = p_group_id
1008 AND agv.process_type = g_current_mode
1009 AND ag.process_type = g_current_mode
1010 AND agv.hierarchy_parent_id = ag.group_id
1011 UNION
1012 SELECT TO_CHAR(agv.display_order, '0000')||'.'||agv.group_id node_id,
1013 agv.display_name,
1014 NULL,
1015 NULL
1016 FROM az_groups_v agv
1017 WHERE agv.group_id = p_group_id
1018 AND agv.process_type = g_current_mode
1019 AND agv.hierarchy_parent_id IS NULL;
1020
1021 v_exist_cnt NUMBER := -1;
1022 v_group group_parent_cursor%ROWTYPE;
1023 v_locator NUMBER;
1024 BEGIN
1025 v_locator := 1;
1026 OPEN group_parent_cursor;
1027 FETCH group_parent_cursor INTO v_group;
1028
1029 v_locator := 2;
1030 SELECT COUNT(*)
1031 INTO v_exist_cnt
1032 FROM az_planning_reports apr
1033 WHERE apr.node_id = v_group.node_id
1034 AND apr.phase = p_phase;
1035
1036 IF (v_exist_cnt = 0) THEN
1037 v_locator := 3;
1038 INSERT INTO az_planning_reports
1039 (NODE_ID, PHASE, NODE_TYPE, DISPLAY_NAME,
1040 CONTEXT_TYPE_NAME,PARENT_NODE_ID, DESCRIPTION)
1041 VALUES
1042 (v_group.node_id, p_phase, 'G', v_group.display_name, '',
1043 v_group.parent_node_id, NULL);
1044 v_locator := 4;
1045 CLOSE group_parent_cursor;
1046 IF (v_group.h_parent_id IS NOT NULL) THEN
1047 get_planning_group_parent(v_group.h_parent_id, p_phase);
1048 v_locator := 5;
1049 END IF;
1050 ELSE -- the node is already in the table
1051 CLOSE group_parent_cursor;
1052 END IF;
1053
1054 EXCEPTION
1055 WHEN application_exception THEN
1056 RAISE;
1057 WHEN OTHERS THEN
1058 raise_error_msg (SQLCODE, SQLERRM,
1059 'GET_PLANNING_GROUP_PARENT', 'v_locator := ' || v_locator);
1060 END get_planning_group_parent;
1061
1062 /*------------------------------------------------------------------------
1063 * GET_PRODUCT_PROCESSES
1064 *
1065 * Private procedure. Called by product_report.
1066 * Populate product process hierarchies in the intermediate table.
1067 * Performs the following steps:
1068 * 1. Get all distinct processes for the given application id from
1069 * az_product_flows into the intermediate table if the process does
1070 * not exist in the table.
1071 * 2. Find all distinct parent ids for the processes found in Step 1.
1072 * 3. For each parent id in Step 2, get all distinct hierarchy ancestors
1073 * in az_groups_v into the intermediate table if the parent does not
1074 * exist in the table.
1075 *-----------------------------------------------------------------------*/
1076 PROCEDURE get_product_processes(p_application_id IN NUMBER)
1077 IS
1078 CURSOR processes_cursor IS
1079 SELECT DISTINCT TO_CHAR(apf.display_order, '0000')||'.'||apf.item_type
1080 ||'.'||apf.process_name node_id,
1081 wav.display_name display_name,
1082 TO_CHAR(ag.display_order, '0000')||'.'||apf.parent_id
1083 parent_node_id,
1084 meaning context_type_name,
1085 wav.description
1086 FROM az_product_flows apf,
1087 az_groups ag,
1088 wf_activities_vl wav,
1089 fnd_lookups fnd
1090 WHERE apf.application_id = p_application_id
1091 AND apf.process_type = 'IMP'
1092 AND wav.end_date IS NULL
1093 AND wav.item_type like 'AZ%'
1094 AND wav.name like 'AZ%'
1095 AND apf.item_type = wav.item_type
1096 AND apf.process_name = wav.name
1097 AND ag.process_type = apf.process_type
1098 AND apf.parent_id = ag.group_id
1099 AND fnd.lookup_type = 'AZ_CONTEXT_TYPE'
1100 AND fnd.lookup_code = apf.context_type;
1101
1102 CURSOR parents_cursor IS
1103 SELECT DISTINCT apf.parent_id
1104 FROM az_product_flows apf
1105 WHERE apf.application_id = p_application_id;
1106
1107 v_exist_cnt NUMBER;
1108 v_locator NUMBER;
1109
1110 BEGIN
1111 v_locator := 1;
1112 FOR one_process IN processes_cursor LOOP
1113 -- check if the process is already in the table
1114 v_exist_cnt := -1;
1115 v_locator := 2;
1116 SELECT COUNT(*)
1117 INTO v_exist_cnt
1118 FROM az_planning_reports apr
1119 WHERE apr.node_id = one_process.node_id
1120 AND apr.phase = -1;
1121 v_locator := 3;
1122 IF (v_exist_cnt = 0) THEN
1123 INSERT INTO az_planning_reports
1124 (NODE_ID, PHASE, NODE_TYPE, DISPLAY_NAME,
1125 CONTEXT_TYPE_NAME,PARENT_NODE_ID, DESCRIPTION)
1126 VALUES
1127 (one_process.node_id, -1, 'P', one_process.display_name,
1128 one_process.context_type_name, one_process.parent_node_id, one_process.description);
1129 END IF;
1130 END LOOP;
1131
1132 -- kick off recursive search for parents
1133 v_locator := 4;
1134 FOR one_process IN parents_cursor LOOP
1135 get_planning_group_parent(one_process.parent_id, -1);
1136 END LOOP;
1137 EXCEPTION
1138 WHEN application_exception THEN
1139 RAISE;
1140 WHEN OTHERS THEN
1141 raise_error_msg (SQLCODE, SQLERRM,
1142 'GET_PRODUCT_PROCESSES', 'v_locator := ' || v_locator);
1143 END get_product_processes;
1144
1145 /*------------------------------------------------------------------------
1146 * GET_REPORT_TITLE_DESC
1147 *
1148 * Private function.
1149 * Given a report code, returns the translated title and short description
1150 * for the report.
1151 *-----------------------------------------------------------------------*/
1152 PROCEDURE get_report_title_desc(p_rpt_code IN VARCHAR2,
1153 p_rpt_title IN OUT VARCHAR2,
1154 p_rpt_desc IN OUT VARCHAR2) IS
1155 BEGIN
1156 IF (p_rpt_code LIKE '%IPR') THEN
1157 p_rpt_title := g_ipr;
1158 p_rpt_desc := g_ipr_desc;
1159 ELSIF (p_rpt_code LIKE '%CPR') THEN
1160 p_rpt_title := g_cpr;
1161 p_rpt_desc := g_cpr_desc;
1162 ELSIF (p_rpt_code LIKE '%PPR') THEN
1163 p_rpt_title := g_ppr;
1164 p_rpt_desc := g_ppr_desc;
1165 ELSIF (p_rpt_code LIKE '%ISR') THEN
1166 p_rpt_title := g_isr;
1167 p_rpt_desc := g_isr_desc;
1168 ELSE -- IF (p_rpt_code LIKE '%UPR') THEN
1169 p_rpt_title := g_upr;
1170 p_rpt_desc := g_upr_desc;
1171 END IF;
1172 EXCEPTION
1173 WHEN application_exception THEN
1174 RAISE;
1175 WHEN OTHERS THEN
1176 raise_error_msg (SQLCODE, SQLERRM,
1177 'GET_REPORT_TITLE_DESC', '');
1178 END get_report_title_desc;
1179
1180 /*------------------------------------------------------------------------
1181 * GET_STATUS_GROUPS
1182 *
1183 * Private procedure. Called by status_report.
1184 * Populate groups with processes with a particular status into the
1185 * intermediate table. Performs the following steps:
1186 * 1. If all statuses is chosen, get all group parents in az_groups_v into
1187 * the intermediate table.
1188 * 2. Otherwise, find all distinct group parents that has a process with
1189 * the status. For each parent found, recursively get all its ancestors
1190 * into the table if the parent does not exist in the table.
1191 *-----------------------------------------------------------------------*/
1192 PROCEDURE get_status_groups(p_status IN VARCHAR2) IS
1193
1194 /* distinct parents for one status (A, C, N) */
1195
1196 CURSOR status_parents_cursor IS
1197 SELECT DISTINCT apv.parent_id
1198 FROM az_processes apv
1199 WHERE apv.status_code = p_status
1200 AND apv.process_type = g_current_mode;
1201
1202 /* distinct parents for 'I' status */
1203 CURSOR incomplete_parents_cursor IS
1204 SELECT DISTINCT apv.parent_id
1205 FROM az_processes apv
1206 WHERE (apv.status_code = 'N'
1207 OR apv.status_code = 'A')
1208 AND apv.process_type = g_current_mode;
1209
1210 /* distinct parents for all statuses */
1211
1212 CURSOR all_parents_cursor IS
1213 SELECT DISTINCT apv.parent_id
1214 FROM az_processes apv
1215 WHERE apv.process_type = g_current_mode;
1216
1217 v_status fnd_lookups.meaning%TYPE;
1218 v_locator PLS_INTEGER := 0;
1219 BEGIN
1220 -- dbms_output.put_line('get_status_groups: '||p_status);
1221 IF (p_status IS NULL) THEN /* get parents for all processes */
1222 v_locator := 1;
1223 FOR one_proc IN all_parents_cursor LOOP
1224 get_monitor_group_parent(one_proc.parent_id, ' ');
1225 END LOOP
1226 COMMIT;
1227 /* get parents for I status */
1228 ELSIF (p_status = 'I') THEN
1229 v_locator := 2;
1230 FOR one_proc IN incomplete_parents_cursor LOOP
1231 get_monitor_group_parent(one_proc.parent_id, ' ');
1232 END LOOP;
1233 ELSE /* get parents for processes of A, C, or N status */
1234 v_locator := 3;
1235 FOR one_proc IN status_parents_cursor LOOP
1236 get_monitor_group_parent(one_proc.parent_id, ' ');
1237 END LOOP;
1238 END IF;
1239
1240 EXCEPTION
1241 WHEN application_exception THEN
1242 RAISE;
1243 WHEN OTHERS THEN
1244 raise_error_msg (SQLCODE, SQLERRM,
1245 'GET_STATUS_GROUPS', 'v_locator := ' || v_locator);
1246 END get_status_groups;
1247
1248 /*------------------------------------------------------------------------
1249 * GET_STATUS_PROCESSES
1250 *
1251 * Private procedure. Called by status_report.
1252 * Populate processes with a particular status in the intermediate table.
1253 * Performs the following steps:
1254 * 1. If all statuses is chosen, get all processes from az_processes_all_v
1255 * into the intermediate table.
1256 * 2. Otherwise, get processes of the specified status form
1257 * az_processes_all_v into the intermediate table.
1258 *-----------------------------------------------------------------------*/
1259 PROCEDURE get_status_processes(p_status IN VARCHAR2)
1260 IS
1261 /* processes for A, C, or N status */
1262 CURSOR status_processes_cursor IS
1263 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
1264 ||'.'||apv.process_name||'.'||apv.context_id node_id,
1265 apv.display_name,
1266 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
1267 parent_node_id,
1268 apv.status,
1269 apv.context_type_name,
1270 apv.context_name,
1271 apv.comments
1272 FROM az_processes_all_v apv,
1273 az_groups ag
1274 WHERE apv.status = p_status
1275 AND apv.process_type = g_current_mode
1276 AND ag.process_type = g_current_mode
1277 AND apv.parent_id = ag.group_id;
1278
1279 /* processes for I status */
1280 CURSOR incomplete_processes_cursor IS
1281 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
1282 ||'.'||apv.process_name||'.'||apv.context_id node_id,
1283 apv.display_name,
1284 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
1285 parent_node_id,
1286 apv.status,
1287 apv.context_type_name,
1288 apv.context_name,
1289 apv.comments
1290 FROM az_processes_all_v apv,
1291 az_groups ag
1292 WHERE (apv.status = 'N' OR apv.status = 'A')
1293 AND apv.process_type = g_current_mode
1294 AND ag.process_type = g_current_mode
1295 AND apv.parent_id = ag.group_id;
1296
1297 /* processes for all statuses */
1298 CURSOR all_processes_cursor IS
1299 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
1300 ||'.'||apv.process_name||'.'||apv.context_id node_id,
1301 apv.display_name,
1302 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
1303 parent_node_id,
1304 apv.status,
1305 apv.context_type_name,
1306 apv.context_name,
1307 apv.comments
1308 FROM az_processes_all_v apv,
1309 az_groups ag
1310 WHERE apv.process_type = g_current_mode
1311 AND ag.process_type = g_current_mode
1312 AND apv.parent_id = ag.group_id;
1313
1314 v_status fnd_lookups.meaning%TYPE;
1315 v_locator PLS_INTEGER := 0;
1316 BEGIN
1317 -- dbms_output.put_line('get_status_processes:'||p_status);
1318
1319 IF (p_status IS NULL) THEN /* get all processes */
1320 v_locator := 1;
1321 FOR one_proc IN all_processes_cursor LOOP
1322 v_status := get_translation('AZ_PROCESS_STATUS', one_proc.status);
1323 INSERT INTO az_monitor_reports
1324 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1325 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1326 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1327 VALUES
1328 (one_proc.node_id, ' ', 'P', one_proc.display_name,
1329 one_proc.context_type_name, one_proc.context_name,
1330 one_proc.parent_node_id, v_status, '', '', '',one_proc.comments);
1331 END LOOP;
1332 /* get processes for I status */
1333 ELSIF (p_status = 'I') THEN
1334 v_locator := 2;
1335 FOR one_proc IN incomplete_processes_cursor LOOP
1336 v_status := get_translation('AZ_PROCESS_STATUS', one_proc.status);
1337 INSERT INTO az_monitor_reports
1338 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1339 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1340 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1341 VALUES
1342 (one_proc.node_id, ' ', 'P', one_proc.display_name,
1343 one_proc.context_type_name, one_proc.context_name,
1344 one_proc.parent_node_id, v_status, '', '', '', one_proc.comments);
1345
1346 END LOOP;
1347 ELSE /* get processes of A, C or N status */
1348 v_locator := 3;
1349 v_status := get_translation('AZ_PROCESS_STATUS', p_status);
1350 FOR one_proc IN status_processes_cursor LOOP
1351 INSERT INTO az_monitor_reports
1352 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1353 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1354 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1355 VALUES
1356 (one_proc.node_id, ' ', 'P', one_proc.display_name,
1357 one_proc.context_type_name, one_proc.context_name,
1358 one_proc.parent_node_id, v_status, '', '', '', one_proc.comments);
1359 END LOOP;
1360 END IF;
1361
1362 EXCEPTION
1363 WHEN application_exception THEN
1364 RAISE;
1365 WHEN OTHERS THEN
1366 raise_error_msg (SQLCODE, SQLERRM,
1367 'GET_STATUS_PROCESSES', 'v_locator := ' || v_locator);
1368 END get_status_processes;
1369
1370 /*------------------------------------------------------------------------
1371 * GET_STATUS_TASKS
1372 *
1373 * Private procedure. Called by status_report.
1374 * Populate tasks belong to processes with a particular status into the
1375 * intermediate table. Performs the following steps:
1376 * 1. If all statuses is chosen, get all tasks in az_tasks_v into the
1377 * intermediate table.
1378 * 2. Otherwise, get all tasks that belong to processes with the specified
1379 * status into the intermediate table.
1380 *-----------------------------------------------------------------------*/
1381 PROCEDURE get_status_tasks(p_status IN VARCHAR2) IS
1382
1383 /* tasks of processes with one status (A, C, N) */
1384
1385 CURSOR status_tasks_cursor IS
1386 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
1387 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
1388 apv.context_type_name,
1389 atv.context_name,
1390 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
1391 apv.process_name||'.'||apv.context_id parent_node_id,
1392 atv.status,
1393 atv.assigned_user,
1394 atv.begin_date,
1395 atv.end_date,
1396 atv.duration
1397 FROM az_tasks_v atv,
1398 az_processes_all_v apv
1399 WHERE apv.status = p_status
1400 AND atv.item_type = apv.item_type
1401 AND atv.root_activity = apv.process_name
1402 AND apv.process_type = g_current_mode
1403 AND atv.context_id = apv.context_id;
1404
1405 /* tasks of processes with I status */
1406
1407 CURSOR incomplete_tasks_cursor IS
1408 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
1409 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
1410 apv.context_type_name,
1411 atv.context_name,
1412 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
1413 apv.process_name||'.'||apv.context_id parent_node_id,
1414 atv.status,
1415 atv.assigned_user,
1416 atv.begin_date,
1417 atv.end_date,
1418 atv.duration
1419 FROM az_tasks_v atv,
1420 az_processes_all_v apv
1421 WHERE (apv.status = 'N'
1422 OR apv.status = 'A')
1423 AND atv.item_type = apv.item_type
1424 AND atv.root_activity = apv.process_name
1425 AND apv.process_type = g_current_mode
1426 AND atv.context_id = apv.context_id;
1427
1428 /* tasks for processes of all statuses */
1429
1430 CURSOR all_tasks_cursor IS
1431 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
1432 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
1433 apv.context_type_name,
1434 atv.context_name,
1435 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
1436 apv.process_name||'.'||apv.context_id parent_node_id,
1437 atv.status,
1438 atv.assigned_user,
1439 atv.begin_date,
1440 atv.end_date,
1441 atv.duration
1442 FROM az_tasks_v atv,
1443 az_processes_all_v apv
1444 WHERE atv.item_type = apv.item_type
1445 AND atv.root_activity = apv.process_name
1446 AND apv.process_type = g_current_mode
1447 AND atv.context_id = apv.context_id;
1448
1449 v_status fnd_lookups.meaning%TYPE;
1450 v_locator PLS_INTEGER := 0;
1451 BEGIN
1452 -- dbms_output.put_line('get_status_tasks: '||p_status);
1453
1454 IF (p_status = 'N') THEN /* Not Started processes do not have tasks */
1455 RETURN;
1456 ELSIF (p_status IS NULL) THEN /* get tasks for all processes */
1457 v_locator := 1;
1458 FOR one_task IN all_tasks_cursor LOOP
1459 v_status := get_translation('AZ_PROCESS_STATUS', one_task.status);
1460 INSERT INTO az_monitor_reports
1461 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1462 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1463 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1464 VALUES
1465 (one_task.node_id, one_task.assigned_user, 'T', '',
1466 one_task.context_type_name, one_task.context_name,
1467 one_task.parent_node_id, v_status, one_task.begin_date,
1468 one_task.end_date, one_task.duration, NULL);
1469 END LOOP;
1470 /* get tasks for processes of I status */
1471 ELSIF (p_status = 'I') THEN
1472 v_locator := 2;
1473 FOR one_task IN incomplete_tasks_cursor LOOP
1474 v_status := get_translation('AZ_PROCESS_STATUS', one_task.status);
1475 INSERT INTO az_monitor_reports
1476 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1477 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1478 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1479 VALUES
1480 (one_task.node_id, one_task.assigned_user, 'T', '',
1481 one_task.context_type_name, one_task.context_name,
1482 one_task.parent_node_id, v_status, one_task.begin_date,
1483 one_task.end_date, one_task.duration,NULL);
1484 END LOOP;
1485 ELSE /* get tasks for processes of A or C status */
1486 v_locator := 3;
1487 FOR one_task IN status_tasks_cursor LOOP
1488 v_status := get_translation('AZ_PROCESS_STATUS', one_task.status);
1489 INSERT INTO az_monitor_reports
1490 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1491 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1492 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1493 VALUES
1494 (one_task.node_id, one_task.assigned_user, 'T', '',
1495 one_task.context_type_name, one_task.context_name,
1496 one_task.parent_node_id, v_status, one_task.begin_date,
1497 one_task.end_date, one_task.duration,NULL);
1498 END LOOP;
1499 END IF;
1500
1501 EXCEPTION
1502 WHEN application_exception THEN
1503 RAISE;
1504 WHEN OTHERS THEN
1505 raise_error_msg (SQLCODE, SQLERRM,
1506 'GET_STATUS_TASKS', 'v_locator := ' || v_locator);
1507 END get_status_tasks;
1508
1509 /*------------------------------------------------------------------------
1510 * GET_TRANSLATED_LABELS
1511 *
1512 * Private procedure.
1513 * Retrieve all the translatable texts used in the reports.
1514 *-----------------------------------------------------------------------*/
1515 PROCEDURE get_translated_labels IS
1516 v_locator PLS_INTEGER := 0;
1517 BEGIN
1518 g_current_mode := fnd_profile.value('AZ_CURRENT_MODE');
1519 g_planning := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PR');
1520 g_monitor := FND_MESSAGE.get_string('AZ', 'AZW_RPT_SR');
1521 g_related := FND_MESSAGE.get_string('AZ', 'AZW_RPT_RELATED');
1522 g_ipr := FND_MESSAGE.get_string('AZ', 'AZW_RPT_IPR');
1523 g_cpr := FND_MESSAGE.get_string('AZ', 'AZW_RPT_CPR');
1524 g_ppr := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PPR');
1525 g_isr := FND_MESSAGE.get_string('AZ', 'AZW_RPT_ISR');
1526 g_upr := FND_MESSAGE.get_string('AZ', 'AZW_RPT_UPR');
1527 g_as_of := FND_MESSAGE.get_string('AZ', 'AZW_RPT_AS_OF');
1528 g_ok := FND_MESSAGE.get_string('AZ', 'AZW_RPT_OK');
1529 g_cancel := FND_MESSAGE.get_string('AZ', 'AZW_RPT_CANCEL');
1530 g_phase := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PHASE');
1531 g_proc_status := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PS');
1532 g_status := FND_MESSAGE.get_string('AZ', 'AZW_RPT_STATUS');
1533 g_installed := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PRDINSTL');
1534 g_summary := FND_MESSAGE.get_string('AZ', 'AZW_RPT_SUMMARY');
1535 g_process_group := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PROC_GROUPS');
1536 g_num_procs := FND_MESSAGE.get_string('AZ', 'AZW_RPT_NUM_PROCESSES');
1537 g_num_active_procs := FND_MESSAGE.get_string('AZ', 'AZW_RPT_NUM_ACTIVE_PROCS');
1538 g_num_completed_procs := FND_MESSAGE.get_string('AZ', 'AZW_RPT_NUM_COMPLETED_PROCS');
1539 g_num_notstarted_procs := FND_MESSAGE.get_string('AZ', 'AZW_RPT_NUM_NOT_STARTED_PROCS');
1540 g_num_tasks := FND_MESSAGE.get_string('AZ', 'AZW_RPT_NUM_TASKS');
1541
1542 g_details := FND_MESSAGE.get_string('AZ', 'AZW_RPT_DETAILS');
1543 g_selected := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PRDSLCT');
1544 g_back_top := FND_MESSAGE.get_string('AZ', 'AZW_RPT_BACK_TOP');
1545 g_hierarchy := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PH');
1546 g_ctxt_type := FND_MESSAGE.get_string('AZ', 'AZW_RPT_CTXTYPE');
1547 g_ctxt_name := FND_MESSAGE.get_string('AZ', 'AZW_RPT_CTXTNAME');
1548 g_user := FND_MESSAGE.get_string('AZ', 'AZW_RPT_USER');
1549 g_duration := FND_MESSAGE.get_string('AZ', 'AZW_RPT_XTIME');
1550 g_start := FND_MESSAGE.get_string('AZ', 'AZW_RPT_STARTDATE');
1551 g_from := FND_MESSAGE.get_string('AZ', 'AZW_RPT_FROM');
1552 g_to := FND_MESSAGE.get_string('AZ', 'AZW_RPT_TO');
1553 g_active_by := FND_MESSAGE.get_string('AZ', 'AZW_RPT_ACTIVE_BY');
1554 g_end := FND_MESSAGE.get_string('AZ', 'AZW_RPT_ENDDATE');
1555 g_days := FND_MESSAGE.get_string('AZ', 'AZW_RPT_DAYS');
1556 g_all := FND_MESSAGE.get_string('AZ', 'AZW_RPT_ALL');
1557 g_description := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PRO_DESC');
1558 g_comments := FND_MESSAGE.get_string('AZ', 'AZW_RPT_COMMTS');
1559
1560 g_atmost := get_translation('AZ_REPORT_DURATION_RANGE', '<=');
1561 g_atleast := get_translation('AZ_REPORT_DURATION_RANGE', '>=');
1562
1563 g_welcome_msg := FND_MESSAGE.get_string('AZ', 'AZW_RPT_WELCOME');
1564 g_ipr_msg := FND_MESSAGE.get_string('AZ', 'AZW_RPT_IPR_INTRO');
1565 g_cpr_msg := FND_MESSAGE.get_string('AZ', 'AZW_RPT_CPR_INTRO');
1566 g_ppr_msg := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PPR_INTRO');
1567 g_isr_msg := FND_MESSAGE.get_string('AZ', 'AZW_RPT_ISR_INTRO');
1568 g_upr_msg := FND_MESSAGE.get_string('AZ', 'AZW_RPT_UPR_INTRO');
1569
1570 g_param_hdr := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PARAM');
1571 g_param_note := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PARAM_NOTE');
1572 g_mn_menu := FND_MESSAGE.get_string('AZ', 'AZW_RPT_MAIN_MENU');
1573 g_exit := FND_MESSAGE.get_string('AZ', 'AZW_RPT_EXIT');
1574 g_help := FND_MESSAGE.get_string('AZ', 'AZW_RPT_HLP');
1575 g_ipr_desc := FND_MESSAGE.get_string('AZ', 'AZW_RPT_IPR_DESC');
1576 g_cpr_desc := FND_MESSAGE.get_string('AZ', 'AZW_RPT_CPR_DESC');
1577 g_ppr_desc := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PPR_DESC');
1578 g_isr_desc := FND_MESSAGE.get_string('AZ', 'AZW_RPT_ISR_DESC');
1579 g_upr_desc := FND_MESSAGE.get_string('AZ', 'AZW_RPT_UPR_DESC');
1580 g_ok_hlp := FND_MESSAGE.get_string('AZ', 'AZW_RPT_OK_HLP');
1581 g_cancel_hlp := FND_MESSAGE.get_string('AZ', 'AZW_RPT_CANCEL_HLP');
1582
1583 g_js_slct_prd := FND_MESSAGE.get_string('AZ',
1584 'AZW_RPT_JS_SLCT_PRD_MSG');
1585 g_js_slct_time_elapsed := FND_MESSAGE.get_string('AZ',
1586 'AZW_RPT_JS_TIME_ELAPSED_MSG');
1587
1588 v_locator := 3;
1589 SELECT meaning
1590 INTO g_mode_label
1591 FROM fnd_lookups
1592 WHERE lookup_type = 'AZ_PROCESS_TYPE'
1593 AND lookup_code = g_current_mode;
1594
1595 g_no_prod_inst := FND_MESSAGE.get_string('AZ', 'AZW_RPT_NO_PROD_INSTALLED');
1596 g_no_prod_sel := FND_MESSAGE.get_string('AZ', 'AZW_RPT_NO_PROD_SELECTED');
1597
1598 g_report_legend := FND_MESSAGE.get_string('AZ', 'AZW_RPT_LEGEND');
1599 g_group_legend := FND_MESSAGE.get_string('AZ', 'AZW_RPT_GROUP_LEGEND');
1600 g_subgrp_legend := FND_MESSAGE.get_string('AZ', 'AZW_RPT_SUBGRP_LEGEND');
1601 g_process_legend := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PROCESS_LEGEND');
1602 g_task_legend := FND_MESSAGE.get_string('AZ', 'AZW_RPT_TASK_LEGEND');
1603
1604 g_task_details := FND_MESSAGE.get_string('AZ', 'AZW_RPT_TSK_DETAIL');
1605 g_step_name := FND_MESSAGE.get_string('AZ', 'AZW_RPT_STEP_NAME');
1606 g_step_response := FND_MESSAGE.get_string('AZ', 'AZW_RPT_RESPONSE');
1607
1608 g_task_params := FND_MESSAGE.get_string('AZ', 'AZW_RPT_TSK_PRMTRS');
1609
1610 g_step_details := FND_MESSAGE.get_string('AZ', 'AZW_RPT_STP_DETAIL');
1611 g_process := FND_MESSAGE.get_string('AZ', 'AZW_RPT_PROCESS');
1612 g_steps := FND_MESSAGE.get_string('AZ', 'AZW_RPT_STEPS');
1613 g_step_msg := FND_MESSAGE.get_string('AZ', 'AZW_RPT_STP_MSG');
1614
1615 g_step_all_procs := FND_MESSAGE.get_string('AZ', 'AZW_RPT_STP_ALL_PROCS');
1616
1617 g_dateformat_msg := FND_MESSAGE.get_string('AZ', 'AZW_RPT_DATEFORMAT_MSG');
1618
1619 EXCEPTION
1620 WHEN application_exception THEN
1621 RAISE;
1622 WHEN OTHERS THEN
1623 raise_error_msg (SQLCODE, SQLERRM,
1624 'GET_TRANSLATED_LABELS', 'v_locator := ' || v_locator);
1625 END get_translated_labels;
1626
1627 /*------------------------------------------------------------------------
1628 * GET_USER_TREES_BY_ATLEAST
1629 *
1630 * Private procedure. Called by user_report.
1631 * Populate the hierarchies based on the selected criteria into the
1632 * intermediate table. Performs the following steps:
1633 * 1. If no status is chosen, get tasks, processes, and groups for both
1634 * Active and Completed statuses based on the 'at least' duration search
1635 * criterion into the intermediate table.
1636 * 2. Otherwise, get tasks, processes, and groups for the particular status
1637 * based on the 'at least' duration search criterion into
1638 * the intermediate table.
1639 *-----------------------------------------------------------------------*/
1640 PROCEDURE get_user_trees_by_atleast(p_user IN VARCHAR2,
1641 p_status IN VARCHAR2,
1642 p_duration IN NUMBER)
1643 IS
1644
1645 /* cursors for at least case with one status (A or C) */
1646
1647 CURSOR atleast_tasks_cursor IS
1648 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
1649 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
1650 apv.context_type_name,
1651 atv.context_name,
1652 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
1653 apv.process_name||'.'||apv.context_id parent_node_id,
1654 atv.status,
1655 atv.assigned_user,
1656 atv.begin_date,
1657 atv.end_date,
1658 atv.duration
1659 FROM az_tasks_v atv,
1660 az_processes_all_v apv
1661 WHERE atv.assigned_user = p_user
1662 AND atv.status = p_status
1663 AND atv.duration >= p_duration
1664 AND atv.item_type = apv.item_type
1665 AND atv.root_activity = apv.process_name
1666 AND atv.context_id = apv.context_id
1667 AND apv.process_type = g_current_mode;
1668
1669 CURSOR atleast_processes_cursor IS
1670 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
1671 ||'.'||apv.process_name||'.'||apv.context_id node_id,
1672 apv.display_name,
1673 apv.context_type_name,
1674 apv.context_name,
1675 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
1676 parent_node_id,
1677 apv.status,
1678 apv.comments
1679 FROM az_processes_all_v apv,
1680 az_groups ag
1681 WHERE apv.parent_id = ag.group_id
1682 AND apv.process_type = g_current_mode
1683 AND ag.process_type = g_current_mode
1684 AND EXISTS(
1685 SELECT 1
1686 FROM az_tasks_v atv
1687 WHERE apv.context_id = atv.context_id
1688 AND apv.item_type = atv.item_type
1689 AND apv.process_name = atv.root_activity
1690 AND atv.assigned_user = p_user
1691 AND atv.status = p_status
1692 AND atv.duration >= p_duration);
1693
1694 CURSOR atleast_parents_cursor IS
1695 SELECT DISTINCT apv.parent_id
1696 FROM az_processes_all_v apv
1697 WHERE EXISTS(
1698 SELECT 1
1699 FROM az_tasks_v atv
1700 WHERE apv.item_type = atv.item_type
1701 AND apv.process_name = atv.root_activity
1702 AND apv.context_id = atv.context_id
1703 AND atv.status = p_status
1704 AND atv.assigned_user = p_user
1705 AND atv.duration >= p_duration
1706 AND apv.process_type = g_current_mode);
1707
1708 /* cursors for at least case with both statuses (A and C) */
1709
1710 CURSOR atleast_both_tasks_cursor IS
1711 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
1712 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
1713 apv.context_type_name,
1714 atv.context_name,
1715 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
1716 apv.process_name||'.'||apv.context_id parent_node_id,
1717 atv.status,
1718 atv.assigned_user,
1719 atv.begin_date,
1720 atv.end_date,
1721 atv.duration
1722 FROM az_tasks_v atv,
1723 az_processes_all_v apv
1724 WHERE atv.assigned_user = p_user
1725 AND atv.duration >= p_duration
1726 AND atv.item_type = apv.item_type
1727 AND atv.root_activity = apv.process_name
1728 AND atv.context_id = apv.context_id
1729 AND apv.process_type = g_current_mode;
1730
1731 CURSOR atleast_both_processes_cursor IS
1732 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
1733 ||'.'||apv.process_name||'.'||apv.context_id node_id,
1734 apv.display_name,
1735 apv.context_type_name,
1736 apv.context_name,
1737 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
1738 parent_node_id,
1739 apv.status,
1740 apv.comments
1741 FROM az_processes_all_v apv,
1742 az_groups ag
1743 WHERE apv.parent_id = ag.group_id
1744 AND apv.process_type = g_current_mode
1745 AND ag.process_type = g_current_mode
1746 AND EXISTS(
1747 SELECT 1
1748 FROM az_tasks_v atv
1749 WHERE apv.item_type = atv.item_type
1750 AND apv.process_name = atv.root_activity
1751 AND apv.context_id = atv.context_id
1752 AND atv.assigned_user = p_user
1753 AND atv.duration >= p_duration);
1754
1755 CURSOR atleast_both_parents_cursor IS
1756 SELECT DISTINCT apv.parent_id
1757 FROM az_processes_all_v apv
1758 WHERE EXISTS(
1759 SELECT 1
1760 FROM az_tasks_v atv
1761 WHERE apv.item_type = atv.item_type
1762 AND apv.process_name = atv.root_activity
1763 AND apv.context_id = atv.context_id
1764 AND atv.assigned_user = p_user
1765 AND atv.duration >= p_duration
1766 AND apv.process_type = g_current_mode);
1767
1768 v_status fnd_lookups.meaning%TYPE;
1769 v_locator PLS_INTEGER := 0;
1770
1771 BEGIN
1772 IF (p_status IS NOT NULL) THEN /* with one status, A or C */
1773 -- get tasks
1774 v_locator := 1;
1775 FOR one_task IN atleast_tasks_cursor LOOP
1776 v_status := get_translation('AZ_PROCESS_STATUS', one_task.status);
1777 INSERT INTO az_monitor_reports
1778 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1779 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1780 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1781 VALUES
1782 (one_task.node_id, p_user, 'T', '',
1783 one_task.context_type_name, one_task.context_name,
1784 one_task.parent_node_id, v_status, one_task.begin_date,
1785 one_task.end_date, one_task.duration, NULL);
1786 END LOOP;
1787 -- get processes
1788 v_status := get_translation('AZ_PROCESS_STATUS', p_status);
1789 v_locator := 2;
1790 FOR one_proc IN atleast_processes_cursor LOOP
1791 INSERT INTO az_monitor_reports
1792 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1793 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1794 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1795 VALUES
1796 (one_proc.node_id, p_user, 'P', one_proc.display_name,
1797 one_proc.context_type_name, one_proc.context_name,
1798 one_proc.parent_node_id, v_status, '', '', '', one_proc.comments);
1799 END LOOP;
1800 -- start recursive search for group parents
1801 v_locator := 3;
1802 FOR one_proc IN atleast_parents_cursor LOOP
1803 get_monitor_group_parent(one_proc.parent_id, p_user);
1804 END LOOP;
1805
1806 ELSE /* p_status is null, for both A and C statuses */
1807 v_locator := 4;
1808 -- get tasks
1809 FOR one_task IN atleast_both_tasks_cursor LOOP
1810 v_status:=get_translation('AZ_PROCESS_STATUS', one_task.status);
1811 INSERT INTO az_monitor_reports
1812 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1813 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1814 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1815 VALUES
1816 (one_task.node_id, p_user, 'T', '',
1817 one_task.context_type_name, one_task.context_name,
1818 one_task.parent_node_id, v_status, one_task.begin_date,
1819 one_task.end_date, one_task.duration,NULL);
1820 END LOOP;
1821 v_locator := 5;
1822 -- get processes
1823 FOR one_proc IN atleast_both_processes_cursor LOOP
1824 v_status := get_translation('AZ_PROCESS_STATUS', one_proc.status);
1825 INSERT INTO az_monitor_reports
1826 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
1827 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
1828 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
1829 VALUES
1830 (one_proc.node_id, p_user, 'P', one_proc.display_name,
1831 one_proc.context_type_name, one_proc.context_name,
1832 one_proc.parent_node_id, v_status, '', '', '', one_proc.comments);
1833 END LOOP;
1834 v_locator := 6;
1835 -- start recursive search for group parents
1836 FOR one_proc IN atleast_both_parents_cursor LOOP
1837 get_monitor_group_parent(one_proc.parent_id, p_user);
1838 END LOOP;
1839 END IF;
1840
1841 EXCEPTION
1842 WHEN application_exception THEN
1843 RAISE;
1844 WHEN OTHERS THEN
1845 raise_error_msg (SQLCODE, SQLERRM,
1846 'GET_USER_TREES_BY_ATLEAST', 'v_locator := ' || v_locator);
1847 END get_user_trees_by_atleast;
1848
1849 /*------------------------------------------------------------------------
1850 * GET_USER_TREES_BY_ATMOST
1851 *
1852 * Private procedure. Called by user_report.
1853 * Populate the hierarchies based on the selected criteria into the
1854 * intermediate table. Performs the following steps:
1855 * 1. If no status is chosen, get tasks, processes, and groups for both
1856 * Active and Completed statuses based on the 'at most' duration search
1857 * criterion into the intermediate table.
1858 * 2. Otherwise, get tasks, processes, and groups for the particular status
1859 * based on the 'at most' duration search criterion into
1860 * the intermediate table.
1861 *-----------------------------------------------------------------------*/
1862 PROCEDURE get_user_trees_by_atmost(p_user IN VARCHAR2,
1863 p_status IN VARCHAR2,
1864 p_duration IN NUMBER)
1865 IS
1866
1867 /* cursors for at most case with one status (A or C) */
1868
1869 CURSOR atmost_tasks_cursor IS
1870 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
1871 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
1872 apv.context_type_name,
1873 atv.context_name,
1874 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
1875 apv.process_name||'.'||apv.context_id parent_node_id,
1876 atv.status,
1877 atv.assigned_user,
1878 atv.begin_date,
1879 atv.end_date,
1880 atv.duration
1881 FROM az_tasks_v atv,
1882 az_processes_all_v apv
1883 WHERE atv.assigned_user = p_user
1884 AND atv.status = p_status
1885 AND atv.duration <= p_duration
1886 AND atv.item_type = apv.item_type
1887 AND atv.root_activity = apv.process_name
1888 AND atv.context_id = apv.context_id
1889 AND apv.process_type = g_current_mode;
1890
1891 CURSOR atmost_processes_cursor IS
1892 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
1893 ||'.'||apv.process_name||'.'||apv.context_id node_id,
1894 apv.display_name,
1895 apv.context_type_name,
1896 apv.context_name,
1897 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
1898 parent_node_id,
1899 apv.status,
1900 apv.comments
1901 FROM az_processes_all_v apv,
1902 az_groups ag
1903 WHERE apv.parent_id = ag.group_id
1904 AND apv.process_type = g_current_mode
1905 AND ag.process_type = apv.process_type
1906 AND EXISTS(
1907 SELECT 1
1908 FROM az_tasks_v atv
1909 WHERE apv.item_type = atv.item_type
1910 AND apv.process_name = atv.root_activity
1911 AND apv.context_id = atv.context_id
1912 AND atv.assigned_user = p_user
1913 AND atv.status = p_status
1914 AND atv.duration <= p_duration);
1915
1916 CURSOR atmost_parents_cursor IS
1917 SELECT DISTINCT apv.parent_id
1918 FROM az_processes_all_v apv
1919 WHERE EXISTS(
1920 SELECT 1
1921 FROM az_tasks_v atv
1922 WHERE apv.item_type = atv.item_type
1923 AND apv.process_name = atv.root_activity
1924 AND apv.context_id = atv.context_id
1925 AND atv.status = p_status
1926 AND atv.assigned_user = p_user
1927 AND atv.duration <= p_duration
1928 AND apv.process_type = g_current_mode);
1929
1930 /* cursors for at most case with both statuses (A and C) */
1931
1932 CURSOR atmost_both_tasks_cursor IS
1933 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
1934 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
1935 apv.context_type_name,
1936 atv.context_name,
1937 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
1938 apv.process_name||'.'||apv.context_id parent_node_id,
1939 atv.status,
1940 atv.assigned_user,
1941 atv.begin_date,
1942 atv.end_date,
1943 atv.duration
1944 FROM az_tasks_v atv,
1945 az_processes_all_v apv
1946 WHERE atv.assigned_user = p_user
1947 AND atv.duration <= p_duration
1948 AND atv.item_type = apv.item_type
1949 AND atv.root_activity = apv.process_name
1950 AND atv.context_id = apv.context_id
1951 AND apv.process_type = g_current_mode;
1952
1953 CURSOR atmost_both_processes_cursor IS
1954 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
1955 ||'.'||apv.process_name||'.'||apv.context_id node_id,
1956 apv.display_name,
1957 apv.context_type_name,
1958 apv.context_name,
1959 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
1960 parent_node_id,
1961 apv.status,
1962 apv.comments
1963 FROM az_processes_all_v apv,
1964 az_groups ag
1965 WHERE apv.parent_id = ag.group_id
1966 AND apv.process_type = g_current_mode
1967 AND ag.process_type = apv.process_type
1968 AND EXISTS(
1969 SELECT 1
1970 FROM az_tasks_v atv
1971 WHERE apv.item_type = atv.item_type
1972 AND apv.process_name = atv.root_activity
1973 AND apv.context_id = atv.context_id
1974 AND atv.assigned_user = p_user
1975 AND atv.duration <= p_duration);
1976
1977 CURSOR atmost_both_parents_cursor IS
1978 SELECT DISTINCT apv.parent_id
1979 FROM az_processes_all_v apv
1980 WHERE EXISTS(
1981 SELECT 1
1982 FROM az_tasks_v atv
1983 WHERE apv.item_type = atv.item_type
1984 AND apv.process_name = atv.root_activity
1985 AND apv.context_id = atv.context_id
1986 AND atv.assigned_user = p_user
1987 AND atv.duration <= p_duration
1988 AND apv.process_type = g_current_mode);
1989
1990 v_status fnd_lookups.meaning%TYPE;
1991 v_locator PLS_INTEGER := 0;
1992 BEGIN
1993
1994 IF (p_status IS NOT NULL) THEN /* with one status, A or C */
1995 -- get tasks
1996 v_locator := 1;
1997 FOR one_task IN atmost_tasks_cursor LOOP
1998 v_status := get_translation('AZ_PROCESS_STATUS', one_task.status);
1999 INSERT INTO az_monitor_reports
2000 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
2001 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
2002 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
2003 VALUES
2004 (one_task.node_id, p_user, 'T', '',
2005 one_task.context_type_name, one_task.context_name,
2006 one_task.parent_node_id, v_status, one_task.begin_date,
2007 one_task.end_date, one_task.duration,NULL);
2008 END LOOP;
2009 v_locator := 2;
2010 -- get processes
2011 v_status := get_translation('AZ_PROCESS_STATUS', p_status);
2012 FOR one_proc IN atmost_processes_cursor LOOP
2013 INSERT INTO az_monitor_reports
2014 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
2015 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
2016 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
2017 VALUES
2018 (one_proc.node_id, p_user, 'P', one_proc.display_name,
2019 one_proc.context_type_name, one_proc.context_name,
2020 one_proc.parent_node_id, v_status, '', '', '', one_proc.comments);
2021 END LOOP;
2022 v_locator := 3;
2023 -- start recursive search for group parents
2024 FOR one_proc IN atmost_parents_cursor LOOP
2025 get_monitor_group_parent(one_proc.parent_id, p_user);
2026 END LOOP;
2027
2028 ELSE /* p_status is null, for both A and C statuses */
2029 v_locator := 4;
2030 -- get tasks
2031 FOR one_task IN atmost_both_tasks_cursor LOOP
2032 v_status := get_translation('AZ_PROCESS_STATUS', one_task.status);
2033 INSERT INTO az_monitor_reports
2034 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
2035 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
2036 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
2037 VALUES
2038 (one_task.node_id, p_user, 'T', '',
2039 one_task.context_type_name, one_task.context_name,
2040 one_task.parent_node_id, v_status, one_task.begin_date,
2041 one_task.end_date, one_task.duration,NULL);
2042 END LOOP;
2043 v_locator := 5;
2044 -- get processes
2045 FOR one_proc IN atmost_both_processes_cursor LOOP
2046 v_status := get_translation('AZ_PROCESS_STATUS', one_proc.status);
2047 INSERT INTO az_monitor_reports
2048 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
2049 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
2050 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
2051 VALUES
2052 (one_proc.node_id, p_user, 'P', one_proc.display_name,
2053 one_proc.context_type_name, one_proc.context_name,
2054 one_proc.parent_node_id, v_status, '', '', '',one_proc.comments);
2055 END LOOP;
2056 v_locator := 6;
2057 -- start recursive search for group parents
2058 FOR one_proc IN atmost_both_parents_cursor LOOP
2059 get_monitor_group_parent(one_proc.parent_id, p_user);
2060 END LOOP;
2061 END IF;
2062
2063 EXCEPTION
2064 WHEN application_exception THEN
2065 RAISE;
2066 WHEN OTHERS THEN
2067 raise_error_msg (SQLCODE, SQLERRM,
2068 'GET_USER_TREES_BY_ATMOST', 'v_locator := ' || v_locator);
2069 END get_user_trees_by_atmost;
2070
2071 /*------------------------------------------------------------------------
2072 * GET_USER_TREES_BY_PERIOD
2073 *
2074 * Private procedure. Called by user_report.
2075 * Populate the hierarchies based on the selected criteria into the
2076 * intermediate table. Performs the following steps:
2077 * 1. If no status is chosen, get tasks, processes, and groups for both
2078 * Active and Completed statuses based on the start and end period into
2079 * the intermediate table.
2080 * 2. Otherwise, get tasks, processes, and groups for the particular status
2081 * based on the start and end period into the intermediate table.
2082 *-----------------------------------------------------------------------*/
2083 PROCEDURE get_user_trees_by_period(p_user IN VARCHAR2,
2084 p_status IN VARCHAR2,
2085 p_startdate IN DATE,
2086 p_enddate IN DATE)
2087 IS
2088
2089 /* cursors for period case with one status (A or C) */
2090
2091 CURSOR period_tasks_cursor IS
2092 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
2093 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
2094 apv.context_type_name,
2095 atv.context_name,
2096 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
2097 apv.process_name||'.'||apv.context_id parent_node_id,
2098 atv.status,
2099 atv.begin_date,
2100 atv.end_date,
2101 atv.duration
2102 FROM az_tasks_v atv,
2103 az_processes_all_v apv
2104 WHERE atv.assigned_user = p_user
2105 AND atv.status = p_status
2106 AND ((atv.begin_date >= p_startdate AND atv.begin_date <= p_enddate)
2107 OR (atv.end_date >= p_startdate AND atv.end_date <= p_enddate)
2108 OR (atv.begin_date <= p_startdate AND
2109 (atv.end_date >= p_enddate OR atv.end_date IS NULL)))
2110 AND atv.item_type = apv.item_type
2111 AND atv.root_activity = apv.process_name
2112 AND atv.context_id = apv.context_id
2113 AND apv.process_type = g_current_mode;
2114
2115 CURSOR period_processes_cursor IS
2116 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
2117 ||'.'||apv.process_name||'.'||apv.context_id node_id,
2118 apv.display_name,
2119 apv.context_type_name,
2120 apv.context_name,
2121 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
2122 parent_node_id,
2123 apv.status,
2124 apv.comments
2125 FROM az_processes_all_v apv,
2126 az_groups ag
2127 WHERE apv.parent_id = ag.group_id
2128 AND apv.process_type = g_current_mode
2129 AND ag.process_type = apv.process_type
2130 AND EXISTS(
2131 SELECT 1
2132 FROM az_tasks_v atv
2133 WHERE apv.item_type = atv.item_type
2134 AND apv.process_name = atv.root_activity
2135 AND apv.context_id = atv.context_id
2136 AND atv.assigned_user = p_user
2137 AND atv.status = p_status
2138 AND ((atv.begin_date >= p_startdate AND atv.begin_date <= p_enddate)
2139 OR (atv.end_date >= p_startdate AND atv.end_date <= p_enddate)
2140 OR (atv.begin_date <= p_startdate AND
2141 (atv.end_date >= p_enddate OR atv.end_date IS NULL))));
2142
2143 CURSOR period_parents_cursor IS
2144 SELECT DISTINCT apv.parent_id
2145 FROM az_processes_all_v apv
2146 WHERE EXISTS(
2147 SELECT 1
2148 FROM az_tasks_v atv
2149 WHERE apv.item_type = atv.item_type
2150 AND apv.process_name = atv.root_activity
2151 AND apv.context_id = atv.context_id
2152 AND atv.status = p_status
2153 AND atv.assigned_user = p_user
2154 AND ((atv.begin_date >= p_startdate AND atv.begin_date <= p_enddate)
2155 OR (atv.end_date >= p_startdate AND atv.end_date <= p_enddate)
2156 OR (atv.begin_date <= p_startdate AND
2157 (atv.end_date >= p_enddate OR atv.end_date IS NULL)))
2158 AND apv.process_type = g_current_mode);
2159
2160 /* cursors for period case with both statuses (A and C) */
2161
2162 CURSOR period_both_tasks_cursor IS
2163 SELECT atv.item_type||'.'||atv.root_activity||'.'||atv.context_id||'.'||
2164 TO_CHAR(TO_NUMBER(atv.item_key), '00000') node_id,
2165 apv.context_type_name,
2166 atv.context_name,
2167 TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type||'.'||
2168 apv.process_name||'.'||apv.context_id parent_node_id,
2169 atv.status,
2170 atv.begin_date,
2171 atv.end_date,
2172 atv.duration
2173 FROM az_tasks_v atv,
2174 az_processes_all_v apv
2175 WHERE atv.assigned_user = p_user
2176 AND ((atv.begin_date >= p_startdate AND atv.begin_date <= p_enddate)
2177 OR (atv.end_date >= p_startdate AND atv.end_date <= p_enddate)
2178 OR (atv.begin_date <= p_startdate AND
2179 (atv.end_date >= p_enddate OR atv.end_date IS NULL)))
2180 AND atv.item_type = apv.item_type
2181 AND atv.root_activity = apv.process_name
2182 AND atv.context_id = apv.context_id
2183 AND apv.process_type = g_current_mode;
2184
2185 CURSOR period_both_processes_cursor IS
2186 SELECT DISTINCT TO_CHAR(apv.display_order, '0000')||'.'||apv.item_type
2187 ||'.'||apv.process_name||'.'||apv.context_id node_id,
2188 apv.display_name,
2189 apv.context_type_name,
2190 apv.context_name,
2191 TO_CHAR(ag.display_order, '0000')||'.'||apv.parent_id
2192 parent_node_id,
2193 apv.status,
2194 apv.comments
2195 FROM az_processes_all_v apv,
2196 az_groups ag
2197 WHERE apv.parent_id = ag.group_id
2198 AND apv.process_type = g_current_mode
2199 AND ag.process_type = apv.process_type
2200 AND EXISTS(
2201 SELECT 1
2202 FROM az_tasks_v atv
2203 WHERE apv.item_type = atv.item_type
2204 AND apv.process_name = atv.root_activity
2205 AND apv.context_id = atv.context_id
2206 AND atv.assigned_user = p_user
2207 AND ((atv.begin_date >= p_startdate AND atv.begin_date <= p_enddate)
2208 OR (atv.end_date >= p_startdate AND atv.end_date <= p_enddate)
2209 OR (atv.begin_date <= p_startdate AND
2210 (atv.end_date >= p_enddate OR atv.end_date IS NULL))));
2211
2212 CURSOR period_both_parents_cursor IS
2213 SELECT DISTINCT apv.parent_id
2214 FROM az_processes_all_v apv
2215 WHERE EXISTS(
2216 SELECT 1
2217 FROM az_tasks_v atv
2218 WHERE apv.item_type = atv.item_type
2219 AND apv.process_name = atv.root_activity
2220 AND apv.context_id = atv.context_id
2221 AND atv.assigned_user = p_user
2222 AND ((atv.begin_date >= p_startdate AND atv.begin_date <= p_enddate)
2223 OR (atv.end_date >= p_startdate AND atv.end_date <= p_enddate)
2224 OR (atv.begin_date <= p_startdate AND
2225 (atv.end_date >= p_enddate OR atv.end_date IS NULL)))
2226 AND apv.process_type = g_current_mode);
2227
2228 v_status fnd_lookups.meaning%TYPE;
2229 v_locator PLS_INTEGER := 0;
2230 BEGIN
2231 /*
2232 dbms_output.enable(1000000);
2233 dbms_output.put_line('p_startdate=[' || p_startdate || ']');
2234 dbms_output.put_line('p_enddate=[' || p_enddate || ']');
2235 */
2236 IF (p_status IS NOT NULL) THEN /* for one status */
2237 v_locator := 1;
2238 -- get tasks
2239 FOR one_task IN period_tasks_cursor LOOP
2240 v_status := get_translation('AZ_PROCESS_STATUS', one_task.status);
2241 INSERT INTO az_monitor_reports
2242 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
2243 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
2244 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
2245 VALUES
2246 (one_task.node_id, p_user, 'T', '',
2247 one_task.context_type_name, one_task.context_name,
2248 one_task.parent_node_id, v_status, one_task.begin_date,
2249 one_task.end_date, one_task.duration,NULL);
2250 END LOOP;
2251 v_locator := 2;
2252 -- get processes
2253 v_status := get_translation('AZ_PROCESS_STATUS', p_status);
2254 FOR one_proc IN period_processes_cursor LOOP
2255 INSERT INTO az_monitor_reports
2256 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
2257 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
2258 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
2259 VALUES
2260 (one_proc.node_id, p_user, 'P', one_proc.display_name,
2261 one_proc.context_type_name, one_proc.context_name,
2262 one_proc.parent_node_id, v_status, '', '', '', one_proc.comments);
2263 END LOOP;
2264 v_locator := 3;
2265 -- start recursive search for group parents
2266 FOR one_proc IN period_parents_cursor LOOP
2267 get_monitor_group_parent(one_proc.parent_id, p_user);
2268 END LOOP;
2269
2270 ELSE /* p_status is null, for both statuses */
2271 v_locator := 4;
2272 -- get tasks
2273 FOR one_task IN period_both_tasks_cursor LOOP
2274 v_status := get_translation('AZ_PROCESS_STATUS', one_task.status);
2275 INSERT INTO az_monitor_reports
2276 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
2277 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
2278 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
2279 VALUES
2280 (one_task.node_id, p_user, 'T', '',
2281 one_task.context_type_name, one_task.context_name,
2282 one_task.parent_node_id, v_status, one_task.begin_date,
2283 one_task.end_date, one_task.duration, NULL);
2284 END LOOP;
2285 v_locator := 5;
2286 -- get processes
2287 FOR one_proc IN period_both_processes_cursor LOOP
2288 v_status := get_translation('AZ_PROCESS_STATUS', one_proc.status);
2289 INSERT INTO az_monitor_reports
2290 (NODE_ID, ASSIGNED_USER, NODE_TYPE, DISPLAY_NAME,
2291 CONTEXT_TYPE_NAME, CONTEXT_NAME, PARENT_NODE_ID,
2292 STATUS_CODE_NAME, START_DATE, END_DATE, DURATION, COMMENTS)
2293 VALUES
2294 (one_proc.node_id, p_user, 'P', one_proc.display_name,
2295 one_proc.context_type_name, one_proc.context_name,
2296 one_proc.parent_node_id, v_status, '', '', '', one_proc.comments);
2297 END LOOP;
2298 v_locator := 6;
2299 -- start recursive search for group parents
2300 FOR one_proc IN period_both_parents_cursor LOOP
2301 get_monitor_group_parent(one_proc.parent_id, p_user);
2302 END LOOP;
2303 END IF;
2304
2305 EXCEPTION
2306 WHEN application_exception THEN
2307 RAISE;
2308 WHEN OTHERS THEN
2309 raise_error_msg (SQLCODE, SQLERRM,
2310 'GET_USER_TREES_BY_PERIOD', 'v_locator := ' || v_locator);
2311 END get_user_trees_by_period;
2312
2313 /*------------------------------------------------------------------------
2314 * GET_WEB_AGENT
2315 *
2316 * Private procedure.
2317 * Retrieve the value for fnd profile option 'WEB_APPS_AGENT', the string
2318 * represents the URL for the host server.
2319 *-----------------------------------------------------------------------*/
2320 PROCEDURE get_web_agent IS
2321 v_slash NUMBER;
2322 v_len NUMBER;
2323
2324 BEGIN
2325 FND_PROFILE.get('APPS_WEB_AGENT', g_web_agent);
2326 --
2327 -- checking the slash at the end
2328 --
2329 v_len := LENGTH(g_web_agent);
2330 v_slash := INSTR(g_web_agent, '/', -1, 1);
2331 IF (v_slash <> v_len) THEN
2332 g_web_agent := g_web_agent || '/';
2333 END IF;
2334
2335 EXCEPTION
2336 WHEN application_exception THEN
2337 RAISE;
2338 WHEN OTHERS THEN
2339 raise_error_msg (SQLCODE, SQLERRM,
2340 'GET_WEB_AGENT', '');
2341 END get_web_agent;
2342
2343 /*
2344 **
2345 ** GET_PROCESS_TYPE_KEY
2346 ** ====================
2347 **
2348 ** The node id specified in the az_planning_reports temp
2349 ** table is composed of multiple fields. two of them are
2350 ** the item_type and process name fields.
2351 ** this procedure extracts and returns these two fields
2352 ** from the input node_id.
2353 **
2354 */
2355 PROCEDURE get_process_type_name (p_node_id IN VARCHAR2,
2356 p_item_type OUT VARCHAR2,
2357 p_process_name OUT VARCHAR2) IS
2358
2359 start_pos PLS_INTEGER;
2360 end_pos PLS_INTEGER;
2361
2362 BEGIN
2363
2364 start_pos := INSTR(p_node_id, '.', 1) + 1;
2365 end_pos := INSTR(p_node_id, '.', start_pos);
2366 p_item_type := TRIM(SUBSTR(p_node_id, start_pos, (end_pos - start_pos)));
2367 p_process_name := TRIM(SUBSTR(p_node_id, (INSTR(p_node_id, '.', -1) + 1)));
2368
2369 EXCEPTION
2370 WHEN application_exception THEN
2371 RAISE;
2372 WHEN OTHERS THEN
2373 raise_error_msg (SQLCODE, SQLERRM,
2374 'GET_PROCESS_TYPE_NAME', '');
2375 END get_process_type_name;
2376
2377 /*------------------------------------------------------------------------
2378 * IMPLEMENTATION_PARAM_PAGE
2379 *
2380 * Public procedure.
2381 * Generates parameter entry page in HTML for the implememtation process
2382 * report. Performs the following steps:
2383 * 1. Get the URL for host server and all display strings if the URL is
2384 * null.
2385 * 2. Print the title and the instruction as the header.
2386 * 3. Display all the valid phases in a drop down list, making blank as
2387 * the default, meaning for all phases.
2388 * 4. Print the OK and Cancel buttons as the footer. OK button calls the
2389 * implementation report and passes the user selected phase; Cancel
2390 * button calls the starting welcome page.
2391 *-----------------------------------------------------------------------*/
2392 PROCEDURE implementation_param_page IS
2393
2394 CURSOR valid_phases_cursor IS
2395 SELECT DISTINCT phase
2396 FROM az_product_phases_v
2397 ORDER BY phase;
2398
2399 BEGIN
2400 IF (g_web_agent IS NULL) THEN
2401 get_web_agent;
2402 get_translated_labels;
2403 END IF;
2404
2405 g_help_target := get_report_procedure ('AZW_RPT_IPR');
2406
2407 print_param_page_header (g_ipr, g_ipr_msg, NULL);
2408
2409 htp.p('<table align="center" border="0" cellpadding="0" ' ||
2410 'cellspacing="2" width="96%">');
2411 htp.p('<tr><td colspan=4><br></td>');
2412 htp.p('<form name="Form1" method="post" ' ||
2413 'action="azw_report.implementation_report"></tr>');
2414 htp.p('<tr><td align=right width="50%"><font class=normal>' ||
2415 g_phase || '</font></td>');
2416 --
2417 -- create the pop-up list of phase values
2418 --
2419 htp.p('<td align=left colspan=3><select name="p_phase" size=1>');
2420 -- by default the blank choice means selecting all phases
2421 htp.p('<option value="">'|| g_all ||'</option>');
2422
2423 FOR one_phase IN valid_phases_cursor LOOP
2424 htp.p('<option value"'|| one_phase.phase ||'">'|| one_phase.phase ||
2425 '</option>');
2426 END LOOP;
2427 htp.p('</select></td></tr>');
2428
2429 print_param_page_footer;
2430
2431 EXCEPTION
2432 WHEN application_exception THEN
2433 RAISE;
2434 WHEN OTHERS THEN
2435 raise_error_msg (SQLCODE, SQLERRM,
2436 'IMPLEMENTATION_PARAM_PAGE', '');
2437 END implementation_param_page;
2438
2439 /*------------------------------------------------------------------------
2440 * IMPLEMENTATION_REPORT
2441 *
2442 * Public procedure. Invoked by the OK button in implementation_param_page.
2443 * Generates the implementation process report in HTML. It performs the
2444 * following steps:
2445 * 1. Get the URL for host server and all display strings if the URL is ''.
2446 * 2. Print report header and subheader.
2447 * 4. Print Table opening tag and header based on selected phase.
2448 * 4. If the parameter is null, get all valid phases, and
2449 * a. for each phase, get the processes into the intermediate table.
2450 * b. for each phase, retrieve the trees from the intermediate table.
2451 * for each row retrieved, print the Table Row and Table Data.
2452 * 5. If the parameter is not null, get the processes for the specified
2453 * phase into the intermediate table, and retrieve the trees from the
2454 * intermediate table. For each row retrieved, print the Table Row and
2455 * Table Data.
2456 * 6. Print HTML Table closing tag.
2457 * 7. Print links to related reports.
2458 *-----------------------------------------------------------------------*/
2459 PROCEDURE implementation_report(p_phase IN VARCHAR2) IS
2460 v_phase NUMBER;
2461
2462 CURSOR valid_phases_cursor IS
2463 SELECT DISTINCT phase
2464 FROM az_product_phases_v
2465 ORDER BY phase;
2466
2467 CURSOR hierarchies_cursor(x_phase NUMBER) IS
2468 SELECT phase,
2469 LPAD(g_blank, g_indent*(LEVEL-1))||display_name hierarchy,
2470 node_type,
2471 description,
2472 node_id,
2473 parent_node_id,
2474 LEVEL
2475 FROM az_planning_reports
2476 WHERE phase = x_phase
2477 START WITH parent_node_id IS NULL
2478 CONNECT BY PRIOR node_id = parent_node_id
2479 AND PRIOR phase = phase;
2480
2481 v_count PLS_INTEGER;
2482 blnBackToTop BOOLEAN;
2483 v_locator PLS_INTEGER := 0;
2484 BEGIN
2485 print_time_stamp('Start the report');
2486 g_help_target := get_report_procedure('AZW_RPT_IPRR');
2487 IF (g_web_agent IS NULL) THEN
2488 get_web_agent;
2489 print_time_stamp('After getting the APP_WEB_AGENT');
2490 get_translated_labels;
2491 END IF;
2492
2493 print_time_stamp('Start Insert into temp table');
2494 IF (p_phase IS NULL) THEN
2495 FOR one IN valid_phases_cursor LOOP
2496 get_implementation_processes(one.phase);
2497 END LOOP;
2498 ELSE
2499 v_phase := TO_NUMBER(p_phase);
2500 get_implementation_processes(v_phase);
2501 END IF;
2502 print_time_stamp('End Insert into temp table');
2503
2504 --
2505 -- Display the report header, date, installed products and it's summary.
2506 --
2507 print_report_header (g_ipr, TRUE, 'azw_report.implementation_param_page');
2508 print_time_stamp('After Displaying table header');
2509 print_ipr_report_parameters(p_phase);
2510
2511 print_ipr_installed_products (p_phase);
2512 print_time_stamp('Start displaying summary');
2513 print_planning_reports_summary (p_phase);
2514 print_time_stamp('After displaying summary');
2515
2516 print_legend_link;
2517
2518 --
2519 -- Display Report Details
2520 --
2521 htp.p('<TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
2522 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="2">' ||
2523 '<FONT CLASS="tableHeader">'|| g_details ||'</FONT></TD></TR>');
2524 htp.p('<TR>');
2525 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
2526 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_hierarchy ||'</FONT></TH>');
2527 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
2528 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_description ||'</FONT></TH>');
2529
2530 IF (p_phase IS NULL) THEN -- for all phases
2531 -- print hierarchy for each phase
2532 v_count := 0;
2533 blnBackToTop := FALSE;
2534 FOR one IN valid_phases_cursor LOOP
2535 IF (v_count > 0) THEN
2536 blnBackToTop := TRUE;
2537 print_back_to_top(2);
2538 END IF;
2539 htp.p('<TR><TH ALIGN="CENTER" BGCOLOR="#336699"' ||
2540 ' COLSPAN="2"><FONT CLASS="tableSubHeader">'|| g_phase
2541 || ' ' || one.phase || '</FONT></TH></TR>');
2542 v_locator := 1;
2543 FOR one_node IN hierarchies_cursor(one.phase) LOOP
2544 IF (one_node.parent_node_id IS NULL) THEN
2545 IF (v_count > 0 AND NOT blnBackToTop) THEN
2546 print_back_to_top(2);
2547 ELSE
2548 blnBackToTop := FALSE;
2549 END IF;
2550 htp.p('<TR><TD ALIGN="LEFT" COLSPAN="2" BGCOLOR="#666666" ' ||
2551 'NOWRAP><i><FONT COLOR="#FFFFFF"><A NAME="PH'||
2552 one_node.phase || '_' ||
2553 one_node.node_id || '">' ||
2554 one_node.hierarchy || '</A></FONT></i></TD>');
2555 ELSIF (one_node.node_type = 'G' ) THEN
2556 -- htp.tableData(one_node.phase, 'RIGHT');
2557 htp.tableRowOpen;
2558 htp.tableData('<i>'|| lpad_nbsp(one_node.level) || one_node.hierarchy
2559 ||'</i>', 'LEFT', '', 'NOWRAP');
2560 ELSE
2561 -- htp.tableData(one_node.phase, 'RIGHT');
2562 htp.tableRowOpen;
2563 htp.tableData('<b>'|| lpad_nbsp(one_node.level) || one_node.hierarchy
2564 ||'</b>', 'LEFT', '', 'NOWRAP');
2565 htp.tableData(one_node.description);
2566 END IF;
2567 htp.tableRowClose;
2568 v_count := v_count + 1;
2569 END LOOP;
2570 END LOOP;
2571 ELSE -- for one phase
2572 htp.p('<TR><TH ALIGN="CENTER" BGCOLOR="#336699"' ||
2573 ' COLSPAN="2"><FONT CLASS="tableSubHeader">'|| g_phase
2574 || ' ' || v_phase || '</FONT></TH></TR>');
2575 v_count := 0;
2576 -- print hierarchy for the phase
2577 v_locator := 2;
2578 FOR one_node IN hierarchies_cursor(v_phase) LOOP
2579 htp.tableRowOpen;
2580 IF (one_node.parent_node_id IS NULL) THEN
2581 IF (v_count > 0) THEN
2582 print_back_to_top(2);
2583 END IF;
2584 htp.p('<TD ALIGN="LEFT" COLSPAN="2" BGCOLOR="#666666" '||
2585 'NOWRAP><i><FONT COLOR="#FFFFFF"><A NAME="PH'||
2586 one_node.phase || '_' ||
2587 one_node.node_id || '">' ||
2588 one_node.hierarchy ||'</A></FONT></i></TD>');
2589 ELSIF (one_node.node_type = 'G') THEN
2590 htp.tableData('<i>'||lpad_nbsp(one_node.level) || one_node.hierarchy
2591 || '</i>', 'LEFT', '', 'NOWRAP');
2592 ELSE
2593 htp.tableData('<b>'||lpad_nbsp(one_node.level) || one_node.hierarchy
2594 || '</b>', 'LEFT', '', 'NOWRAP');
2595 htp.tableData(one_node.description, 'LEFT', '', 'NOWRAP');
2596 END IF;
2597 htp.tableRowClose;
2598 v_count := v_count +1;
2599 END LOOP;
2600 END IF;
2601 print_back_to_top(2);
2602 htp.tableClose;
2603 print_time_stamp('End displaying report details');
2604 print_legend;
2605 --
2606 -- print related report links
2607 --
2608 print_related_reports('AZW_RPT_CPR', 'AZW_RPT_PPR');
2609
2610 COMMIT;
2611
2612 EXCEPTION
2613 WHEN NO_DATA_FOUND THEN
2614 -- print Table closing tag
2615 htp.tableClose;
2616 print_related_reports('AZW_RPT_CPR', 'AZW_RPT_PPR');
2617 WHEN application_exception THEN
2618 RAISE;
2619 WHEN OTHERS THEN
2620 raise_error_msg (SQLCODE, SQLERRM,
2621 'IMPLEMENTATION_REPORT', 'v_locator := ' || v_locator);
2622 END implementation_report;
2623
2624 /*
2625 **
2626 ** PRINT_BACK_TO_TOP
2627 ** =================
2628 **
2629 ** Private Procedure.
2630 ** This procedure prints the back to top row in the report body,
2631 ** which is a link to the top of the report summary.
2632 **
2633 */
2634 PROCEDURE print_back_to_top (p_col_span IN NUMBER) IS
2635
2636 BEGIN
2637
2638 htp.p('<TR><TD ALIGN="LEFT" COLSPAN="'|| p_col_span ||'"><A HREF="#TOP">' ||
2639 '<FONT SIZE="-1">' || g_back_top || '</FONT></A></TD></TR>');
2640
2641 EXCEPTION
2642 WHEN application_exception THEN
2643 RAISE;
2644 WHEN OTHERS THEN
2645 raise_error_msg (SQLCODE, SQLERRM,
2646 'PRINT_BACK_TO_TOP', '');
2647 END print_back_to_top;
2648
2649 /*
2650 **
2651 ** PRINT_HTML_STYLE
2652 ** ================
2653 **
2654 ** Private procedure.
2655 ** Print out the HTML style sheet used by the BIS 11i UI standard.
2656 ** The HTML style is a set of predefined font formats used through
2657 ** out the reports. This procedure must be called by all the
2658 ** display procedures, to be able to use the predefined fonts.
2659 **
2660 */
2661 PROCEDURE print_html_style IS
2662
2663 BEGIN
2664 htp.p('<STYLE type="text/css">');
2665 htp.p('<!--');
2666
2667 htp.p('font.button {font-family: arial, sans-serif; ' ||
2668 'color: black; text-decoration: none; font-size: 10pt}');
2669 htp.p('font.disable {font-family: arial, sans-serif; ' ||
2670 'color: #666666; text-decoration: none; font-size: 10pt}');
2671 htp.p('font.tableHeader {font-family: arial, sans-serif; ' ||
2672 'font-weight: bold; color: white; ' ||
2673 'text-decoration: none; font-size: 10pt}');
2674 htp.p('font.tableSubHeader {font-family: times new roman; ' ||
2675 'font-weight: bold; color: white; ' ||
2676 'text-decoration: none; font-size: 10pt}');
2677 htp.p('font.normal {font-family: arial, sans-serif; ' ||
2678 'color: black; font-size: 10pt}');
2679 htp.p('font.normalLink {font-family: arial, sans-serif; font-size: 10pt}');
2680 htp.p('font.normalBold {color: black; font-family: arial, ' ||
2681 'sans-serif; font-size: 10pt; font-weight: bold}');
2682 htp.p('font.banner {font-family: arial, sans-serif; font-size: 16pt; ' ||
2683 'font-weight: bold; color: white; text-decoration: none}');
2684 htp.p('font.subtitle {font-family: arial, sans-serif; font-size: 14pt; ' ||
2685 'color: black; text-decoration: none}');
2686 htp.p('font.curOption {font-family: arial, sans-serif; font-size: 10pt; ' ||
2687 'color: #666666; text-decoration: none}');
2688 htp.p('--></STYLE>');
2689
2690 EXCEPTION
2691 WHEN application_exception THEN
2692 RAISE;
2693 WHEN OTHERS THEN
2694 raise_error_msg (SQLCODE, SQLERRM,
2695 'PRINT_HTML_STYLE', '');
2696 END print_html_style;
2697
2698 /*------------------------------------------------------------------------
2699 * PRINT_CONTEXT_SUBHEADER
2700 *
2701 * Private procedure. Called by context_report.
2702 * Print the context type chosen for the context report.
2703 *-----------------------------------------------------------------------*/
2704 PROCEDURE print_context_subheader(p_context IN VARCHAR2) IS
2705
2706 BEGIN
2707 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="96%">');
2708 htp.p('<TR><TD WIDTH="50%" ALIGN="LEFT">');
2709 htp.p('<TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
2710 htp.p('<TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
2711 htp.p('<TR><TD ALIGN="RIGHT">'|| g_ctxt_type || '</TD><TD> </TD><TD ALIGN="LEFT"><B>' ||
2712 get_translation('AZ_CONTEXT_TYPE', p_context) ||'</B></TD></TR>');
2713 htp.p('</TABLE>');
2714 htp.p('</TD><TD WIDTH="50%" ALIGN="RIGHT">');
2715 htp.p('<TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
2716 htp.p('<TR><TD ALIGN="RIGHT">' || g_as_of || '</TD><TD> </TD><TD ALIGN="LEFT"><B>'||
2717 FND_DATE.date_to_displayDT(SYSDATE) || '</B></TD></TR>');
2718 htp.p('<TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
2719 htp.p('</TABLE>');
2720 htp.p('</TD>');
2721 htp.p('</TR>');
2722 htp.p('</TABLE>');
2723 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="98%">');
2724 htp.p('<TR><TD BGCOLOR="#CCCCCC"><IMG SRC="' || g_image_path ||'/FNDDBPXC.gif"></TD></TR>');
2725 htp.p('</TABLE>');
2726 htp.p(' <BR>');
2727
2728 EXCEPTION
2729 WHEN application_exception THEN
2730 RAISE;
2731 WHEN OTHERS THEN
2732 raise_error_msg (SQLCODE, SQLERRM,
2733 'PRINT_CONTEXT_SUBHEADER', '');
2734 END print_context_subheader;
2735
2736 /*------------------------------------------------------------------------
2737 * PRINT_IMP_START_PAGE
2738 *
2739 * Private procedure. Called by start page.
2740 * Print start page for Fresh Implementation.
2741 *-----------------------------------------------------------------------*/
2742 PROCEDURE print_imp_start_page IS
2743 v_link VARCHAR2(240);
2744 BEGIN
2745
2746 htp.nl;
2747 htp.nl;
2748 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=50%>');
2749
2750 htp.p('<tr><td><table border=0 cellspacing=0 cellpadding=0 width=80%>');
2751
2752 -- display hyper-linked reports in group 1
2753 htp.tableRowOpen;
2754 htp.p('<td bgcolor="#336699" width=1%><br></td>');
2755 htp.p('<td bgcolor="#336699" width=90% align="left" NOWRAP>'||
2756 '<font class="tableheader">'|| g_planning ||'</font></td>');
2757
2758 htp.p('</tr></table></td></tr>');
2759
2760 htp.p('<tr><td><font size=-2><BR></font></td></tr>');
2761 htp.p('<tr><td><table border=0 cellspacing=0 cellpadding=0 width=80%>');
2762
2763 v_link := get_report_procedure('AZW_RPT_IPR');
2764 htp.p('<tr><td align="left" valign="middle" NOWRAP>'||
2765 '<image src=/OA_MEDIA/FNDWATHS.gif height=18 width=18 '||'alt="'||
2766 g_ipr_desc ||'"><font face="Arial" size=2><b><a href="'||
2767 g_web_agent || v_link||'" OnMouseOver="window.status=' ||
2768 '''' || g_ipr_desc || '''' || ';return true;">'|| g_ipr ||'</a></b></td></tr>');
2769
2770 v_link := get_report_procedure('AZW_RPT_PPR');
2771 htp.p('<tr><td align="left" valign="middle" NOWRAP>'||
2772 '<image src=/OA_MEDIA/FNDWATHS.gif height=18 width=18 alt="'||
2773 g_ppr_desc || '"><font face="Arial" size=2><b><a href="'||
2774 g_web_agent|| v_link||'" OnMouseOver="window.status=' ||
2775 '''' || g_ppr_desc || '''' || ';return true;">'
2776 || g_ppr||'</a></b></td></tr>');
2777
2778 v_link := get_report_procedure('AZW_RPT_CPR');
2779 htp.p('<tr><td align="left" valign="middle" NOWRAP>'||
2780 '<image src=/OA_MEDIA/FNDWATHS.gif height=18 width=18 alt="'||
2781 g_cpr_desc || '"><font face="Arial" size=2><b><a href="'||
2782 g_web_agent|| v_link||'" OnMouseOver="window.status=' ||
2783 '''' || g_cpr_desc || '''' || ';return true;">'
2784 || g_cpr||'</a></b></td></tr>');
2785 htp.p('</td></tr></table></table><small><br></small>');
2786 htp.p('</td><td valign=TOP height=9></td>');
2787
2788 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=50%>');
2789 htp.p('<tr><td><table border=0 cellspacing=0 cellpadding=0 width=80%>');
2790
2791 -- display hyper-linked reports in group 2
2792 htp.tableRowOpen;
2793 htp.p('<td bgcolor="#336699" width=1%><br></td>');
2794 htp.p('<td bgcolor="#336699" width=90% align="left" NOWRAP>'||
2795 '<font class="tableheader">'|| g_monitor ||'</font></td>');
2796 htp.p('</tr></table></td></tr>');
2797
2798 htp.p('<tr><td><font size=-2><BR></font></td></tr>');
2799 htp.p('<tr><td><table border=0 cellspacing=0 cellpadding=0 width=80%>');
2800
2801 v_link := get_report_procedure('AZW_RPT_ISR');
2802 htp.p('<tr><td align="left" valign="middle" NOWRAP>'||
2803 '<image src=/OA_MEDIA/FNDWATHS.gif height=18 width=18 alt="'||
2804 g_isr_desc || '"><font face="Arial" size=2><b><a href="'||
2805 g_web_agent|| v_link||'" OnMouseOver="window.status=' ||
2806 '''' || g_isr_desc || '''' || ';return true;">'
2807 ||g_isr||' (' || g_mode_label || ')'||'</a></b></td></tr>');
2808
2809 v_link := get_report_procedure('AZW_RPT_UPR');
2810 htp.p('<tr><td align="left" valign="middle" NOWRAP>'||
2811 '<image src=/OA_MEDIA/FNDWATHS.gif height=18 width=18 alt="'||
2812 g_upr_desc || '"><font face="Arial" size=2><b><a href="'||
2813 g_web_agent|| v_link||'" OnMouseOver="window.status=' ||
2814 '''' || g_upr_desc || '''' || ';return true;">'
2815 ||g_upr||' (' || g_mode_label || ')'||'</a></b></td></tr>');
2816
2817 htp.p('</td>');
2818 htp.p('</td></tr></table><small><br></small>');
2819 htp.p('</td>');
2820
2821 htp.tableClose;
2822
2823 EXCEPTION
2824 WHEN application_exception THEN
2825 RAISE;
2826 WHEN OTHERS THEN
2827 raise_error_msg (SQLCODE, SQLERRM,
2828 'PRINT_IMP_START_PAGE', '');
2829 END print_imp_start_page;
2830 /*------------------------------------------------------------------------
2831 * PRINT_UPGRADE_START_PAGE
2832 *
2833 * Private procedure. Called by start page.
2834 * Print start page for Upgrade Implementation.
2835 *-----------------------------------------------------------------------*/
2836 PROCEDURE print_upgrade_start_page IS
2837 v_link VARCHAR2(240);
2838 BEGIN
2839
2840 htp.nl;
2841 htp.nl;
2842 htp.p('<table border=0 cellspacing=0 cellpadding=0 width=50%>');
2843
2844 htp.p('<tr><td><table border=0 cellspacing=0 cellpadding=0 width=80%>');
2845
2846 -- display hyper-linked reports in group 2
2847
2848 htp.tableRowOpen;
2849 htp.p('<td bgcolor="#336699" width=1%><br></td>');
2850 htp.p('<td bgcolor="#336699" width=90% align="left" NOWRAP>'||
2851 '<font class="tableheader">'|| g_monitor ||'</font></td>');
2852 htp.p('</tr></table></td></tr>');
2853
2854 htp.p('<tr><td><font size=-2><BR></font></td></tr>');
2855 htp.p('<tr><td><table border=0 cellspacing=0 cellpadding=0 width=80%>');
2856
2857 v_link := get_report_procedure('AZW_RPT_ISR');
2858 htp.p('<tr><td align="left" valign="middle" NOWRAP>'||
2859 '<image src=/OA_MEDIA/FNDWATHS.gif height=18 width=18 alt="'||
2860 g_isr_desc ||'"><font face="Arial" size=2><b><a href="'||
2861 g_web_agent|| v_link||'" OnMouseOver="window.status=' ||
2862 '''' || g_isr_desc || '''' || ';return true;">'
2863 ||g_isr||' (' || g_mode_label || ')'||'</a></b></td></tr>');
2864
2865 v_link := get_report_procedure('AZW_RPT_UPR');
2866 htp.p('<tr><td align="left" valign="middle" NOWRAP>'||
2867 '<image src=/OA_MEDIA/FNDWATHS.gif height=18 width=18 alt="'||
2868 g_upr_desc ||'"><font face="Arial" size=2><b><a href="'||
2869 g_web_agent|| v_link||'" OnMouseOver="window.status=' ||
2870 '''' || g_upr_desc || '''' || ';return true;">'
2871 ||g_upr||' (' || g_mode_label || ')'||'</a></b></td></tr>');
2872
2873 htp.p('</td>');
2874 htp.p('</td></tr></table><small><br></small>');
2875 htp.p('</td>');
2876
2877 htp.tableClose;
2878 EXCEPTION
2879 WHEN application_exception THEN
2880 RAISE;
2881 WHEN OTHERS THEN
2882 raise_error_msg (SQLCODE, SQLERRM,
2883 'PRINT_UPGRADE_START_PAGE', '');
2884 END print_upgrade_start_page;
2885
2886 /*
2887 **
2888 ** PRINT_IPR_SREPORT_PARAMETERS
2889 ** ============================
2890 **
2891 ** Private Procedure.
2892 ** It displays the date and time stamp at the upper right corner of
2893 ** the report and the selected phase parameter at the left.
2894 **
2895 */
2896 PROCEDURE print_ipr_report_parameters (p_phase IN VARCHAR2) IS
2897
2898 BEGIN
2899 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="96%">');
2900 htp.p('<TR>');
2901 htp.p(' <TD WIDTH="50%" ALIGN="LEFT">');
2902 htp.p(' <TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
2903 htp.p(' <TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
2904 htp.p(' <TR><TD ALIGN="RIGHT">'|| g_phase || '</TD><TD> </TD><TD ALIGN="LEFT"><B>');
2905 IF (p_phase IS NOT NULL) THEN
2906 htp.p(p_phase);
2907 ELSE
2908 htp.p(g_all);
2909 END IF;
2910 htp.p('</B></TD></TR>');
2911 htp.p(' </TABLE>');
2912 htp.p(' </TD><TD WIDTH="50%" ALIGN="RIGHT">');
2913 htp.p(' <TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
2914 htp.p(' <TR><TD ALIGN="RIGHT">' || g_as_of || '</TD><TD> </TD><TD ALIGN="LEFT"><B>'||
2915 FND_DATE.date_to_displayDT(SYSDATE) || '</B></TD></TR>');
2916 htp.p(' <TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
2917 htp.p(' </TABLE>');
2918 htp.p(' </TD>');
2919 htp.p('</TR>');
2920 htp.p('</TABLE>');
2921 htp.p('');
2922 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="98%">');
2923 htp.p('<TR><TD BGCOLOR="#CCCCCC"><IMG SRC="'|| g_image_path ||'/FNDDBPXC.gif"></TD></TR>');
2924 htp.p('</TABLE>');
2925 htp.p(' <BR>');
2926
2927 EXCEPTION
2928 WHEN application_exception THEN
2929 RAISE;
2930 WHEN OTHERS THEN
2931 raise_error_msg (SQLCODE, SQLERRM,
2932 'PRINT_IPR_SREPORT_PARAMETERS', '');
2933 END print_ipr_report_parameters;
2934
2935 /*
2936 **
2937 ** PRINT_IPR_INSTALLED_PRODUCTS
2938 ** ============================
2939 **
2940 ** Private Procedure.
2941 ** It displays the installed products table for the implementaion report.
2942 ** It is called from implementation_report.
2943 */
2944 PROCEDURE print_ipr_installed_products (p_phase IN VARCHAR2) IS
2945
2946 CURSOR all_phases IS
2947 SELECT DISTINCT appv.phase,
2948 appv.application_short_name short_name,
2949 appv.application_name name
2950 FROM az_product_phases_v appv
2951 ORDER BY 1, 3;
2952
2953 CURSOR per_phase(x_phase NUMBER) IS
2954 SELECT DISTINCT appv.phase,
2955 appv.application_short_name short_name,
2956 appv.application_name name
2957 FROM az_product_phases_v appv
2958 WHERE appv.phase = x_phase
2959 ORDER BY 1, 3;
2960
2961 product_rec all_phases%ROWTYPE;
2962 v_prev_phase az_product_phases_v.phase%TYPE;
2963 v_count PLS_INTEGER;
2964 v_locator PLS_INTEGER := 0;
2965 BEGIN
2966 -- Print the Table header
2967 htp.p('<TABLE ALIGN="center" BORDER="1" CELLPADDING="1" CELLSPACING="1">');
2968 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="2">' ||
2969 '<FONT CLASS="tableHeader">'|| g_installed ||'</FONT></TD></TR>');
2970
2971 -- open the correspondig cursor according to the p_phase argument.
2972 IF (p_phase IS NULL) THEN
2973 v_locator := 1;
2974 OPEN all_phases;
2975 ELSE
2976 v_locator := 2;
2977 OPEN per_phase(p_phase);
2978 END IF;
2979
2980 v_prev_phase := 0;
2981 v_count := 0;
2982 LOOP
2983 IF (p_phase IS NULL) THEN
2984 v_locator := 3;
2985 FETCH all_phases INTO product_rec;
2986 EXIT WHEN all_phases%NOTFOUND;
2987 ELSE
2988 v_locator := 4;
2989 FETCH per_phase INTO product_rec;
2990 EXIT WHEN per_phase%NOTFOUND;
2991 END IF;
2992 -- If there is a new phase record
2993 IF (v_count = 0 OR v_prev_phase <> product_rec.phase) THEN
2994 IF (v_count > 0 AND MOD(v_count, 2) = 1) THEN
2995 -- display an empty table data and close
2996 -- both table data and table row
2997 htp.p('<TD> </TD></TR>');
2998 v_count := v_count + 1;
2999 END IF;
3000 -- Display the phase number header.
3001 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="2">' ||
3002 '<FONT CLASS="tableSubHeader"> '|| g_phase || ' ' ||
3003 product_rec.phase ||'</FONT></TD></TR>');
3004 -- Display the product name in the first column
3005 htp.p('<TR><TD>'|| product_rec.name ||'</TD>');
3006 ELSE
3007 IF (MOD(v_count, 2) = 0) THEN -- if v_count is even then new record for the same phase
3008 -- Display the product name in the first column
3009 htp.p('<TR><TD>'|| product_rec.name ||'</TD>');
3010 ELSE
3011 -- Display the product name in the Second column
3012 htp.p('<TD>'|| product_rec.name ||'</TD></TR>');
3013 END IF;
3014 END IF;
3015 v_count := v_count + 1;
3016 v_prev_phase := product_rec.phase;
3017 END LOOP;
3018 IF (p_phase IS NULL) THEN
3019 v_locator := 5;
3020 CLOSE all_phases;
3021 ELSE
3022 v_locator := 6;
3023 CLOSE per_phase;
3024 END IF;
3025 IF (v_count > 0 AND MOD(v_count, 2) = 1) THEN
3026 htp.p('<TD> </TD></TR>');
3027 END IF;
3028 -- If there are no products installed
3029 IF (v_count = 0) THEN
3030 htp.p('<TR><TD COLSPAN="2"><i>' || g_no_prod_inst || '</i></TD></TR>');
3031 END IF;
3032 htp.p('</TABLE><BR>');
3033 return;
3034
3035 EXCEPTION
3036 WHEN application_exception THEN
3037 RAISE;
3038 WHEN OTHERS THEN
3039 raise_error_msg (SQLCODE, SQLERRM,
3040 'PRINT_IPR_INSTALLED_PRODUCTS', 'v_locator := ' || v_locator);
3041 END print_ipr_installed_products;
3042
3043 /*
3044 **
3045 ** PRINT_PLANNING_REPORTS_SUMMARY
3046 ** ==============================
3047 **
3048 ** Private Procedure.
3049 ** It displays the report summary which contains the total number
3050 ** of installed processes for each process group.
3051 **
3052 */
3053 PROCEDURE print_planning_reports_summary (p_phase IN VARCHAR2) IS
3054
3055 process_groups PlanProcessGroups;
3056
3057 v_prev_phase az_product_phases_v.phase%TYPE;
3058 v_count PLS_INTEGER;
3059 v_display_data VARCHAR2(500);
3060 v_locator PLS_INTEGER := 0;
3061 BEGIN
3062 -- Print the Table header
3063
3064 htp.p('<TABLE ALIGN="center" BORDER="1" CELLPADDING="1" CELLSPACING="1">');
3065 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="4">' ||
3066 '<FONT CLASS="tableHeader"><A NAME="TOP">' ||
3067 g_summary ||'</A></FONT></TD></TR>');
3068 htp.p('<TR>');
3069 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3070 g_process_group ||'</FONT></TD>');
3071 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3072 g_num_procs ||'</FONT></TD>');
3073 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3074 g_process_group ||'</FONT></TD>');
3075 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3076 g_num_procs ||'</FONT></TD>');
3077 htp.p('</TR>');
3078
3079 --
3080 -- Select the report summary data and populate the array with it.
3081 --
3082 v_locator := 1;
3083 populate_process_groups_array (p_phase, process_groups);
3084
3085 v_prev_phase := 0;
3086 v_count := 0;
3087 v_locator := 2;
3088 FOR i IN 1..process_groups.COUNT LOOP
3089 v_display_data := '<A HREF="#PH'||
3090 process_groups(i).phase || '_' ||
3091 process_groups(i).node_id || '">'||
3092 process_groups(i).display_name || '</A>';
3093 -- If there is a new phase record
3094 IF (v_count = 0 OR v_prev_phase <> process_groups(i).phase) THEN
3095 IF (v_count > 0 AND MOD(v_count, 2) = 1) THEN
3096 -- display an empty table data and close
3097 -- both table data and table row
3098 htp.p('<TD> </TD><TD> </TD></TR>');
3099 v_count := v_count + 1;
3100 END IF;
3101 IF (process_groups(i).phase > 0) THEN
3102 -- Display the phase number header.
3103 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="4">' ||
3104 '<FONT CLASS="tableSubHeader">' || g_phase || ' ' ||
3105 process_groups(i).phase || '</FONT></TD></TR>');
3106 END IF;
3107 -- Display the product name in the first column
3108 htp.p('<TR><TD>'|| v_display_data ||'</TD>' ||
3109 '<TD ALIGN="CENTER">'|| process_groups(i).processes_count || '</TD>');
3110 ELSE
3111 -- if v_count is even then new record for the same phase
3112 IF (MOD(v_count, 2) = 0) THEN
3113 -- Display the product name in the first column
3114 htp.p('<TR><TD>'|| v_display_data ||'</TD><TD ALIGN="CENTER">'
3115 || process_groups(i).processes_count ||'</TD>');
3116 ELSE
3117 -- Display the product name in the Second column
3118 htp.p('<TD>'|| v_display_data || '</TD><TD ALIGN="CENTER">'||
3119 process_groups(i).processes_count ||'</TD></TR>');
3120 END IF;
3121 END IF;
3122 v_count := v_count + 1;
3123 v_prev_phase := process_groups(i).phase;
3124 END LOOP;
3125 -- if v_count is odd then we need to close with an empty table data
3126 IF (MOD(v_count, 2) = 1) THEN
3127 htp.p('<TD> </TD><TD> </TD></TR>');
3128 END IF;
3129 htp.p('</TABLE><BR>');
3130 return;
3131
3132 EXCEPTION
3133 WHEN application_exception THEN
3134 RAISE;
3135 WHEN OTHERS THEN
3136 raise_error_msg (SQLCODE, SQLERRM,
3137 'PRINT_PLANNING_REPORTS_SUMMARY', 'v_locator := ' || v_locator);
3138 END print_planning_reports_summary;
3139
3140 /*
3141 **
3142 ** POPULATE_PROCESS_GROUPS_ARRAY
3143 ** =============================
3144 **
3145 ** Private procedure.
3146 ** This procedure is responsible for selecting the available Process Groups,
3147 ** counts the number of processes available for each and populates the
3148 ** passed array with the data to be used by the calling function
3149 ** (print_planning_reports_summary).
3150 **
3151 */
3152 PROCEDURE populate_process_groups_array (
3153 p_phase IN NUMBER,
3154 process_groups IN OUT NOCOPY PlanProcessGroups) IS
3155
3156 CURSOR all_phases IS
3157 SELECT phase,
3158 display_name,
3159 node_type,
3160 parent_node_id,
3161 node_id
3162 FROM az_planning_reports
3163 START WITH parent_node_id IS NULL
3164 CONNECT BY PRIOR node_id = parent_node_id
3165 AND PRIOR phase = phase;
3166
3167 CURSOR per_phase(x_phase NUMBER) IS
3168 SELECT phase,
3169 display_name,
3170 node_type,
3171 parent_node_id,
3172 node_id
3173 FROM az_planning_reports
3174 WHERE phase = x_phase
3175 START WITH parent_node_id IS NULL
3176 CONNECT BY PRIOR node_id = parent_node_id
3177 AND PRIOR phase = phase;
3178
3179 product_rec all_phases%ROWTYPE;
3180 v_count PLS_INTEGER;
3181 v_proc_count PLS_INTEGER;
3182 v_locator PLS_INTEGER := 0;
3183 BEGIN
3184 --
3185 -- Select all the upper limit parents and poulate the array.
3186 --
3187 -- open the corresponding cursor according to the p_phase argument.
3188
3189 v_count := 1;
3190 v_proc_count := 0;
3191 IF (p_phase IS NULL) THEN
3192 v_locator := 1;
3193 OPEN all_phases;
3194 ELSE
3195 v_locator := 2;
3196 OPEN per_phase(p_phase);
3197 END IF;
3198 LOOP
3199 IF (p_phase IS NULL) THEN
3200 v_locator := 3;
3201 FETCH all_phases INTO product_rec;
3202 EXIT WHEN all_phases%NOTFOUND;
3203 ELSE
3204 v_locator := 4;
3205 FETCH per_phase INTO product_rec;
3206 EXIT WHEN per_phase%NOTFOUND;
3207 END IF;
3208
3209 IF (product_rec.parent_node_id IS NULL) THEN -- upper parent group
3210 process_groups(v_count).phase := product_rec.phase;
3211 process_groups(v_count).display_name := product_rec.display_name;
3212 process_groups(v_count).node_id := product_rec.node_id;
3213 process_groups(v_count).processes_count := 0;
3214 IF (v_count > 1) THEN
3215 process_groups(v_count-1).processes_count := v_proc_count;
3216 END IF;
3217 v_proc_count := 0; -- reset it for the next process group.
3218 v_count := v_count + 1;
3219 ELSIF (product_rec.node_type = 'P') THEN
3220 v_proc_count := v_proc_count + 1;
3221 END IF;
3222 END LOOP;
3223 --
3224 -- Check if the last record is a process
3225 --
3226 v_locator := 5;
3227 IF (product_rec.node_type = 'P') THEN
3228 process_groups(v_count-1).processes_count := v_proc_count;
3229 END IF;
3230
3231 IF (p_phase IS NULL) THEN
3232 v_locator := 6;
3233 CLOSE all_phases;
3234 ELSE
3235 v_locator := 7;
3236 CLOSE per_phase;
3237 END IF;
3238 return;
3239
3240 EXCEPTION
3241 WHEN application_exception THEN
3242 RAISE;
3243 WHEN OTHERS THEN
3244 raise_error_msg (SQLCODE, SQLERRM,
3245 'POPULATE_PROCESS_GROUPS_ARRAY', 'v_locator := ' || v_locator);
3246 END populate_process_groups_array;
3247
3248 /*------------------------------------------------------------------------
3249 * PRINT_PARAM_PAGE_HEADER
3250 *
3251 * Private procedure.
3252 * Given page title and text, generates the header in HTML for the parameter
3253 * entry page.
3254 *-----------------------------------------------------------------------*/
3255 PROCEDURE print_param_page_header(p_title IN VARCHAR2,
3256 p_msg IN VARCHAR2,
3257 p_mode_label IN VARCHAR2 DEFAULT NULL) IS
3258
3259 BEGIN
3260 -- dbms_output.put_line('print_param_page_header: '||p_title);
3261
3262 IF (p_mode_label IS NOT NULL) THEN
3263 print_report_header (p_title || ' (' || p_mode_label || ')', FALSE, NULL);
3264 ELSE
3265 print_report_header (p_title, FALSE, NULL);
3266 END IF;
3267
3268
3269 htp.p('<table align=center border="0" cellpadding="0" cellspacing="0" ' ||
3270 'width="96%">');
3271 htp.p('<tr><td><br></td></tr>');
3272 htp.p('<tr><td align=center><font class=subtitle>'||
3273 g_param_hdr ||'</font></td></tr>');
3274 htp.p('<tr><td><br></td></tr>');
3275 htp.p('<tr><td align=left><font class=normal>'||
3276 g_param_note ||'</font></td></tr>');
3277 htp.p('<tr><td><br></td></tr>');
3278 htp.p('</table>');
3279
3280 EXCEPTION
3281 WHEN application_exception THEN
3282 RAISE;
3283 WHEN OTHERS THEN
3284 raise_error_msg (SQLCODE, SQLERRM,
3285 'PRINT_PARAM_PAGE_HEADER', '');
3286 END print_param_page_header;
3287
3288 /*------------------------------------------------------------------------
3289 * PRINT_PARAM_PAGE_FOOTER
3290 *
3291 * Private procedure. Called by xxxx_param_page.
3292 * prints the OK and Cancel buttons in HTML for the parameter entry page.
3293 * OK always links to the report; Cancel always returns to the start page.
3294 *-----------------------------------------------------------------------*/
3295 PROCEDURE print_param_page_footer IS
3296
3297 BEGIN
3298 -- Print the horizontal seperator
3299 print_footer_separator_line();
3300 -- Print the ok and cancel buttons
3301 print_ok_cancel_buttons('javascript:void(document.Form1.submit())');
3302
3303 htp.p('</form>');
3304 htp.centerClose;
3305 htp.bodyClose;
3306 htp.htmlClose;
3307
3308 EXCEPTION
3309 WHEN application_exception THEN
3310 RAISE;
3311 WHEN OTHERS THEN
3312 raise_error_msg (SQLCODE, SQLERRM,
3313 'PRINT_PARAM_PAGE_FOOTER', '');
3314 END print_param_page_footer;
3315
3316 /*----------------------------------------------------------------------------
3317 * PRINT_FOOTER_SEPERATOR_LINE
3318 *
3319 * Private procedure. Called by print_param_page_footer and product_param_page.
3320 * Displays the thin horizontal line seperating the main param page form its
3321 * footer.
3322 *--------------------------------------------------------------------------*/
3323 PROCEDURE print_footer_separator_line IS
3324
3325 BEGIN
3326 htp.p('<tr><td colspan=4><br></td></tr>');
3327 htp.p('<tr><td colspan=4><br></td></tr>');
3328 -- light grey single pixel separator line between parameters and buttons
3329 htp.p('<tr><td bgcolor=#CCCCCC colspan=4>' ||
3330 '<img src="'|| g_image_path ||'/FNDDBPXC.gif"></td></tr>');
3331 htp.p('<tr><td colspan=4><br></td></tr>');
3332 htp.p('</tr></table>');
3333
3334 EXCEPTION
3335 WHEN application_exception THEN
3336 RAISE;
3337 WHEN OTHERS THEN
3338 raise_error_msg (SQLCODE, SQLERRM,
3339 'PRINT_FOOTER_SEPERATOR_LINE', '');
3340 END print_footer_separator_line;
3341
3342 /*----------------------------------------------------------------------------
3343 * PRINT_OK_CANCEL_BUTTONS
3344 *
3345 * Private procedure. Called by print_param_page_footer and product_param_page.
3346 *
3347 *---------------------------------------------------------------------------*/
3348 PROCEDURE print_ok_cancel_buttons(p_ok_action IN VARCHAR2) IS
3349
3350 BEGIN
3351
3352 -- Table for the OK and Cancel buttons
3353 htp.p('<table align=center border=0 cellpadding=0 cellspacing=2 width=96%>');
3354 htp.p('<tr><td align=right width=370>');
3355 htp.p('<table align=right border=0 cellpadding=0 cellspacing=0>');
3356 htp.p('<tr>');
3357 -- OK button
3358 htp.p('<td align=right rowspan=5><img src="'|| g_image_path ||'/FNDBRNDL.gif"></td>');
3359 htp.p('<td bgcolor=#333333><img src="'|| g_image_path ||'/FNDDBPX3.gif"></td>');
3360 htp.p('<td align=left rowspan=5><img src="'|| g_image_path ||'/FNDBSQRR.gif"></td>');
3361 htp.p('<td rowspan=5 width=3></td>');
3362 htp.p('</tr>');
3363 htp.p('<tr><td bgcolor=#FFFFFF>' ||
3364 '<img src="'|| g_image_path ||'/FNDDBPXW.gif"></td></tr>');
3365 htp.p('<tr><td bgcolor=#CCCCCC height=20 nowrap><a href="'||
3366 p_ok_action ||'" OnMouseOver="window.status=' ||
3367 '''' || g_ok_hlp || '''' || ';return true;" ' ||
3368 '><font class=button>'|| g_ok ||'</font></a></td></tr>');
3369 htp.p('<tr><td bgcolor=#666666>' ||
3370 '<img src="'|| g_image_path ||'/FNDDBPX6.gif"></td></tr>');
3371 htp.p('<tr><td bgcolor=#333333>' ||
3372 '<img src="'|| g_image_path ||'/FNDDBPX3.gif"></td></tr>');
3373 htp.p('</table></td>');
3374 htp.p('<td align=left width=400>');
3375 htp.p('<table align=left border=0 cellpadding=0 cellspacing=0>');
3376 htp.p('<tr>');
3377 -- Cancel button
3378 htp.p('<td align=right rowspan=5>' ||
3379 '<img src="'|| g_image_path ||'/FNDBSQRL.gif"></td>');
3380 htp.p('<td bgcolor=#333333><img src="'|| g_image_path ||'/FNDDBPX3.gif"></td>');
3381 htp.p('<td align=left rowspan=5>' ||
3382 '<img src="'|| g_image_path ||'/FNDBRNDR.gif"></td></tr>');
3383 htp.p('<tr><td bgcolor=#FFFFFF>' ||
3384 '<img src="'|| g_image_path ||'/FNDDBPXW.gif"></td></tr>');
3385 htp.p('<tr><td bgcolor=#CCCCCC height=20 nowrap>' ||
3386 '<a href="javascript:history.back();" ' ||
3387 'onMouseOver="window.status='||''''|| g_cancel_hlp ||''''||
3388 ';return true"><font class=button>'|| g_cancel ||'</font></a></td></tr>');
3389 htp.p('<tr><td bgcolor=#666666>' ||
3390 '<img src="'|| g_image_path ||'/FNDDBPX6.gif"></td></tr>');
3391 htp.p('<tr><td bgcolor=#333333>' ||
3392 '<img src="'|| g_image_path ||'/FNDDBPX3.gif"></td></tr>');
3393 htp.p('</table></td></tr></table>');
3394 htp.p('<br><br><br><br><br><br>');
3395
3396 EXCEPTION
3397 WHEN application_exception THEN
3398 RAISE;
3399 WHEN OTHERS THEN
3400 raise_error_msg (SQLCODE, SQLERRM,
3401 'PRINT_OK_CANCEL_BUTTONS', '');
3402 END print_ok_cancel_buttons;
3403
3404 /*------------------------------------------------------------------------
3405 * PRINT_PP_JSCRIPTS
3406 *
3407 * Private procedure. Called by product_param_page.
3408 * the print_pp_jscripts prints the JavaScript functions that are used in
3409 * the product_parameter_page in order to maintain a comma-delimited list
3410 * of products that have been selected by the user.
3411 *-----------------------------------------------------------------------*/
3412 PROCEDURE print_pp_jscripts IS
3413
3414 BEGIN
3415 htp.p('<SCRIPT LANGUAGE=Javascript>');
3416
3417 htp.p('function determine_checked_boxes()');
3418 htp.p('{');
3419 htp.p(' var loop_count = document.forms[0].p_product_count.value');
3420 htp.p(' document.forms[0].p_product_list.value = "" ');
3421 -- htp.p(' alert("number of installed products = " + loop_count)');
3422 htp.p(' ');
3423 htp.p(' for(var i=0; i<loop_count; i++)');
3424 htp.p(' {');
3425 htp.p(' if(document.forms[0].elements[i].checked)');
3426 htp.p(' {');
3427 -- htp.p(' alert("checked box " + i + " = " + ' ||
3428 -- 'document.forms[0].elements[i].value)');
3429 htp.p(' if( document.forms[0].p_product_list.value == "" )');
3430 htp.p(' {');
3431 htp.p(' document.forms[0].p_product_list.value =');
3432 htp.p(' document.forms[0].elements[i].value');
3433 htp.p(' }');
3434 htp.p(' else');
3435 htp.p(' {');
3436 htp.p(' document.forms[0].p_product_list.value += ","');
3437 htp.p(' document.forms[0].p_product_list.value += ');
3438 htp.p(' document.forms[0].elements[i].value');
3439 htp.p(' }');
3440 htp.p(' }');
3441 htp.p(' }');
3442 htp.p(' return true');
3443 htp.p('');
3444 htp.p('} // determine_checked_boxes');
3445
3446 htp.p('function set_final_product_list(field)');
3447 htp.p('{');
3448
3449 htp.p(' var tmp = determine_checked_boxes() ');
3450 htp.p(' document.forms[1].p_product_list.value = ' ||
3451 'document.forms[0].p_product_list.value');
3452 -- htp.p(' msg = "final product list = " + ' ||
3453 -- 'document.forms[1].p_product_list.value');
3454 -- htp.p(' alert(msg) ');
3455 htp.p(' return true');
3456 htp.p('');
3457 htp.p('} // set_final_product_list');
3458 htp.p('');
3459 htp.p('function SubmitCurrentForm(varProductList)');
3460 htp.p('{');
3461 htp.p(' set_final_product_list(varProductList);');
3462 htp.p(' if (document.forms[1].p_product_list.value == "")');
3463 htp.p(' alert("'|| g_js_slct_prd ||'");');
3464 htp.p(' else');
3465 htp.p(' document.Form1.submit();');
3466 htp.p('}');
3467 htp.p('</SCRIPT>');
3468 htp.br;
3469 EXCEPTION
3470 WHEN application_exception THEN
3471 RAISE;
3472 WHEN OTHERS THEN
3473 raise_error_msg (SQLCODE, SQLERRM,
3474 'PRINT_PP_JSCRIPTS', '');
3475 END print_pp_jscripts;
3476
3477
3478 /*
3479 **
3480 ** PRINT_PRODUCT_SUBHEADER
3481 ** =======================
3482 **
3483 ** Private Procedure.
3484 ** Display the selected products table.
3485 ** Called from product_report procedure.
3486 **
3487 */
3488 PROCEDURE print_product_subheader (p_ids IN id_tbl_t) IS
3489
3490 BEGIN
3491
3492 --
3493 -- Print The Date header
3494 --
3495 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="96%">');
3496 htp.p('<TR>');
3497 htp.p(' <TD WIDTH="50%" ALIGN="LEFT">');
3498 htp.p(' <TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
3499 htp.p(' <TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
3500 htp.p(' </TABLE>');
3501 htp.p(' </TD><TD WIDTH="50%" ALIGN="RIGHT">');
3502 htp.p(' <TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
3503 htp.p(' <TR><TD ALIGN="RIGHT">' || g_as_of || '</TD><TD> </TD><TD ALIGN="LEFT"><B>'||
3504 FND_DATE.date_to_displayDT(SYSDATE) || '</B></TD></TR>');
3505 htp.p(' </TABLE>');
3506 htp.p(' </TD>');
3507 htp.p('</TR>');
3508 htp.p('</TABLE>');
3509
3510 --
3511 -- Print the Selected Products Table header
3512 --
3513 print_selected_prods_table(p_ids);
3514
3515 return;
3516
3517 EXCEPTION
3518 WHEN application_exception THEN
3519 RAISE;
3520 WHEN OTHERS THEN
3521 raise_error_msg (SQLCODE, SQLERRM,
3522 'PRINT_PRODUCT_SUBHEADER', '');
3523 END print_product_subheader;
3524
3525 /*
3526 **
3527 ** print_selected_prods_table
3528 ** ==========================
3529 **
3530 ** Private Procedure.
3531 ** Display the selected products table.
3532 ** Called from print_product_subheader and show_all_steps procedures.
3533 **
3534 */
3535 PROCEDURE print_selected_prods_table (p_ids IN id_tbl_t) IS
3536
3537 v_text VARCHAR2(240);
3538 v_count PLS_INTEGER;
3539
3540 BEGIN
3541
3542 htp.p('<TABLE ALIGN="center" BORDER="1" CELLPADDING="1" CELLSPACING="1">');
3543 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="2">' ||
3544 '<FONT CLASS="tableHeader">'|| g_selected ||'</FONT></TD></TR>');
3545
3546 v_count := 0;
3547 FOR v_index IN 1..p_ids.COUNT LOOP
3548 v_text := get_application_name(p_ids(v_index));
3549 IF (MOD(v_count, 2) = 0) THEN
3550 htp.p('<TR><TD WIDTH="50%">'|| v_text ||'</TD>');
3551 ELSE
3552 htp.p('<TD WIDTH="50%">'|| v_text ||'</TD></TR>');
3553 END IF;
3554 v_count := v_count + 1;
3555 END LOOP;
3556 -- if the last record was odd then we need to add a new empty cell
3557 IF (v_count > 0 AND MOD(v_count, 2) = 1) THEN
3558 htp.p('<TD> </TD></TR>');
3559 END IF;
3560 -- If there are no products selected
3561 IF (v_count = 0) THEN
3562 htp.p('<TR><TD COLSPAN="2"><i>' || g_no_prod_sel ||'</i></TD></TR>');
3563 END IF;
3564 htp.p('</TABLE><BR>');
3565
3566 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="98%">');
3567 htp.p('<TR><TD BGCOLOR="#CCCCCC"><IMG SRC="'|| g_image_path ||'/FNDDBPXC.gif"></TD></TR>');
3568 htp.p('</TABLE>');
3569 htp.p(' <BR>');
3570 EXCEPTION
3571 WHEN application_exception THEN
3572 RAISE;
3573 WHEN OTHERS THEN
3574 raise_error_msg (SQLCODE, SQLERRM,
3575 'PRINT_SELECTED_PRODS_TABLE', '');
3576 END print_selected_prods_table;
3577
3578 /*------------------------------------------------------------------------
3579 * PRINT_RELATED_REPORTS
3580 *
3581 * Private procedure. Called by xxxx_report.
3582 * Given the related report codes, generate the links to the reports.
3583 *-----------------------------------------------------------------------*/
3584 PROCEDURE print_related_reports(p_rpt1 IN VARCHAR2, p_rpt2 IN VARCHAR2) IS
3585 v_text VARCHAR2(240);
3586 v_desc VARCHAR2(2000);
3587 BEGIN
3588
3589 -- Get the report title and short desc
3590 get_report_title_desc(p_rpt1, v_text, v_desc);
3591
3592 htp.hr('ALL');
3593 htp.p('<table border=0 cellpadding=1 cellspacing=1 width="60%">');
3594 htp.tableRowOpen;
3595 htp.p('<td colspan="2" align="left"><font class="normalbold">' ||
3596 '<A NAME="RELATED_REPORTS">'|| g_related ||'</A></font></td>');
3597 htp.tableRowClose;
3598 htp.tableRowOpen;
3599 htp.p('<td align="left" width="50%"><a href="' || g_web_agent || get_report_procedure(p_rpt1) ||
3600 '" onMouseOver="window.status=' || '''' || v_desc || '''' || ';return true">' ||
3601 '<img src="'|| g_image_path ||'/azrelat.gif" border=0 '
3602 ||' alt="'|| v_desc ||'"> '|| v_text ||'</a></td>');
3603
3604 IF (p_rpt2 IS NOT NULL) THEN
3605 -- Get the report title and short desc
3606 get_report_title_desc(p_rpt2, v_text, v_desc);
3607
3608 htp.p('<td align="left" width="50%"><a href="'|| g_web_agent || get_report_procedure(p_rpt2) ||
3609 '" onMouseOver="window.status=' || '''' || v_desc || '''' || ';return true">' ||
3610 '<img src="'|| g_image_path ||'/azrelat.gif" border=0 '||
3611 ' alt="'|| v_desc ||'"> '|| v_text || '</a></td>');
3612 END IF;
3613
3614 htp.tableRowClose;
3615 htp.tableClose;
3616 htp.bodyClose;
3617 htp.htmlClose;
3618 EXCEPTION
3619 WHEN application_exception THEN
3620 RAISE;
3621 WHEN OTHERS THEN
3622 raise_error_msg (SQLCODE, SQLERRM,
3623 'PRINT_RELATED_REPORTS', '');
3624 END print_related_reports;
3625
3626 /*------------------------------------------------------------------------
3627 * PRINT_REPORT_HEADER
3628 *
3629 * Private procedure. Called by xxxx_report.
3630 * Given a report title, prints a report header comformant with BIS standard.
3631 *-----------------------------------------------------------------------*/
3632 PROCEDURE print_report_header(p_title IN VARCHAR2, p_type IN BOOLEAN, p_param_page IN VARCHAR2) IS
3633 v_js_string VARCHAR2(2000);
3634 report_help_url VARCHAR2(240);
3635
3636 BEGIN
3637
3638 report_help_url := fnd_help.get_url('AZ', g_help_target);
3639
3640 htp.htmlOpen;
3641 htp.headOpen;
3642
3643 htp.title(p_title);
3644 print_html_style;
3645 htp.headClose;
3646 htp.p('<BODY BGCOLOR="#FFFFFF" LINK="#0000A0" VLINK="#0000A0">');
3647
3648 v_js_string:='<SCRIPT LANGUAGE=Javascript> function help_window() '||
3649 '{help_win=window.open('||''''|| report_help_url
3650 ||''''||','||''''||'help_win'||''''||','
3651 ||''''||'resizable=yes,scrollbars=yes,toolbar=yes,width=550,'
3652 ||'height=250'||''''||')}';
3653
3654 htp.p(v_js_string);
3655 htp.p('function exit_window() { self.close(); }');
3656 htp.p('');
3657 htp.p('');
3658 htp.p('</SCRIPT>');
3659
3660 htp.tableOpen('align=center bgcolor=#336699 border=0 cellpadding=0 ' ||
3661 'cellspacing=0 valign=center width=100%');
3662 htp.tableRowOpen;
3663 htp.p('<td width=1%><br></td>');
3664 htp.p('<td><font class="banner"> '|| p_title ||'</font></td>');
3665
3666 IF p_type THEN
3667
3668 -- Related Reports Button
3669 htp.p('<td width=2%><br></td>');
3670 htp.p('<td width=4%><a href="#RELATED_REPORTS" ' ||
3671 'onMouseOver="window.status=' || '''' ||
3672 g_related || '''' || ';return true">' ||
3673 '<img src="'|| g_image_path ||'/azrelat.gif" border="0" alt="'||
3674 g_related ||'"></a></td>');
3675
3676 -- Parameters Page Button
3677 htp.p('<td width=2%><br></td>');
3678 htp.p('<td width=4%><a href="' || p_param_page || '" ' ||
3679 'onMouseOver="window.status=' || '''' ||
3680 g_param_hdr || '''' || ';return true">' ||
3681 '<img src="'|| g_image_path ||'/azparam.gif" border="0" alt="'||
3682 g_param_hdr ||'"></a></td>');
3683 END IF;
3684 -- Main Menu Button
3685 htp.p('<td width=2%><br></td>');
3686 htp.p('<td width=4%><a href="azw_report.start_page" ' ||
3687 'onMouseOver="window.status=' || '''' ||
3688 g_mn_menu || '''' || ';return true">' ||
3689 '<img src="'|| g_image_path ||'/azmenu.gif" border="0" alt="'||
3690 g_mn_menu ||'"></a></td>');
3691 -- Exit Button
3692 htp.p('<td width=2%><br></td>');
3693 htp.p('<td width=4%><a href="javascript:exit_window()" ' ||
3694 'onMouseOver="window.status=' || '''' ||
3695 g_exit || '''' || ';return true">' ||
3696 '<img src="/OA_MEDIA/FNDEXIT.gif" border="0" alt="'||
3697 g_exit ||'"></a></td>');
3698 -- Help Button
3699 htp.p('<td width=2%><br></td>');
3700 htp.p('<td width=4%><a href="javascript:help_window()" ' ||
3701 ' onMouseOver="window.status=' || '''' ||
3702 g_help || '''' || ';return true">' ||
3703 '<img src="'|| g_image_path ||'/azhelp.gif" border="0" alt="'||
3704 g_help ||'"></a></td>');
3705 -- Oracle Applications Logo
3706 htp.p('<td width=2%><br></td>');
3707 htp.p('<td width=12%><img src="'|| g_image_path ||'/azapplo.gif"></td>');
3708 htp.p('<td width=1%><br></td></tr>');
3709 htp.p('</table>');
3710 EXCEPTION
3711 WHEN application_exception THEN
3712 RAISE;
3713 WHEN OTHERS THEN
3714 raise_error_msg (SQLCODE, SQLERRM,
3715 'PRINT_REPORT_HEADER', '');
3716 END print_report_header;
3717
3718 /*------------------------------------------------------------------------
3719 * PRINT_STATUS_SUBHEADER
3720 *
3721 * Private procedure. Called by status_report.
3722 * Print the status chosen and the product short names for the installed
3723 * products.
3724 *-----------------------------------------------------------------------*/
3725 PROCEDURE print_status_subheader(p_status IN VARCHAR2) IS
3726
3727 v_text VARCHAR2(240);
3728 v_product_cnt INTEGER := 0;
3729 v_line_cnt INTEGER := 1;
3730
3731 BEGIN
3732
3733 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="96%">');
3734 htp.p('<TR>');
3735 htp.p(' <TD WIDTH="50%" ALIGN="LEFT">');
3736 htp.p(' <TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
3737 htp.p(' <TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
3738 htp.p(' <TR><TD ALIGN="RIGHT">'|| g_proc_status || '</TD><TD> </TD><TD ALIGN="LEFT"><B>');
3739 IF (p_status IS NOT NULL) THEN
3740 htp.p(get_translation('AZ_PROCESS_STATUS', p_status));
3741 ELSE
3742 htp.p(g_all);
3743 END IF;
3744 htp.p('</B></TD></TR>');
3745 htp.p(' </TABLE>');
3746 htp.p(' </TD><TD WIDTH="50%" ALIGN="RIGHT">');
3747 htp.p(' <TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
3748 htp.p(' <TR><TD ALIGN="RIGHT">' || g_as_of || '</TD><TD> </TD><TD ALIGN="LEFT"><B>'||
3749 FND_DATE.date_to_displayDT(SYSDATE) || '</B></TD></TR>');
3750 htp.p(' <TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
3751 htp.p(' </TABLE>');
3752 htp.p(' </TD>');
3753 htp.p('</TR>');
3754 htp.p('</TABLE>');
3755 htp.p('');
3756 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="98%">');
3757 htp.p('<TR><TD BGCOLOR="#CCCCCC"><IMG SRC="'|| g_image_path ||'/FNDDBPXC.gif"></TD></TR>');
3758 htp.p('</TABLE>');
3759 htp.p(' <BR>');
3760 --
3761 -- Display Installed products table
3762 --
3763 print_isr_installed_products;
3764
3765 EXCEPTION
3766 WHEN application_exception THEN
3767 RAISE;
3768 WHEN OTHERS THEN
3769 raise_error_msg (SQLCODE, SQLERRM,
3770 'PRINT_STATUS_SUBHEADER', '');
3771 END print_status_subheader;
3772
3773 /*
3774 **
3775 ** PRINT_STATUS_INSTALLED_PRODS
3776 ** ============================
3777 **
3778 ** Private Procedure.
3779 ** It displays the installed products table for the implementation
3780 ** status report. It is called from print_status_subheader.
3781 **
3782 */
3783 PROCEDURE print_isr_installed_products IS
3784
3785 CURSOR installed_products IS
3786 SELECT distinct application_short_name short_name,
3787 application_name name
3788 FROM az_product_phases_v appv
3789 ORDER BY 1;
3790
3791 product_rec installed_products%ROWTYPE;
3792 v_count PLS_INTEGER;
3793
3794 BEGIN
3795 -- Print the Table header
3796 htp.p('<TABLE ALIGN="center" BORDER="1" CELLPADDING="1" CELLSPACING="1">');
3797 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="2">' ||
3798 '<FONT CLASS="tableHeader">'|| g_installed ||'</FONT></TD></TR>');
3799
3800 v_count := 0;
3801 FOR product_rec IN installed_products LOOP
3802 -- if v_count is even then new record for the same phase
3803 IF (MOD(v_count, 2) = 0) THEN
3804 -- Display the product name in the first column
3805 htp.p('<TR><TD>'|| product_rec.name ||'</TD>');
3806 ELSE
3807 -- Display the product name in the Second column
3808 htp.p('<TD>'|| product_rec.name ||'</TD></TR>');
3809 END IF;
3810 v_count := v_count + 1;
3811 END LOOP;
3812 IF (v_count > 0 AND MOD(v_count, 2) = 1) THEN
3813 htp.p('<TD> </TD></TR>');
3814 END IF;
3815 -- If there are no products installed
3816 IF (v_count = 0) THEN
3817 htp.p('<TR><TD COLSPAN="2"><i>' || g_no_prod_inst ||'</i></TD></TR>');
3818 END IF;
3819 htp.p('</TABLE><BR>');
3820
3821 EXCEPTION
3822 WHEN application_exception THEN
3823 RAISE;
3824 WHEN OTHERS THEN
3825 raise_error_msg (SQLCODE, SQLERRM,
3826 'PRINT_STATUS_INSTALLED_PRODS', '');
3827 END print_isr_installed_products;
3828
3829 /*
3830 **
3831 ** PRINT_ISR_REPORT_SUMMARY
3832 ** ========================
3833 **
3834 ** Private Procedure.
3835 ** It displays the report summary for the implementation status report.
3836 ** The report summary displays the total number of processes, number of
3837 ** active, completed and not started processes for each process group.
3838 ** It is called from status_report procedure.
3839 **
3840 */
3841 PROCEDURE print_isr_report_summary (p_status IN VARCHAR2) IS
3842
3843 process_groups StatusProcessGroups;
3844
3845 v_display_data VARCHAR2(500);
3846 v_colspan INTEGER;
3847 v_locator PLS_INTEGER := 0;
3848 BEGIN
3849 -- Print the Table header
3850 v_locator := 1;
3851 htp.p('<TABLE ALIGN="center" BORDER="1" CELLPADDING="1" CELLSPACING="1">');
3852
3853 IF (p_status IS NULL) THEN
3854 v_colspan := 5;
3855 ELSIF (p_status = 'I') THEN
3856 v_colspan := 4;
3857 ELSE
3858 v_colspan := 2;
3859 END IF;
3860
3861 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="'|| v_colspan ||'">' ||
3862 '<FONT CLASS="tableHeader"><A NAME="TOP">' ||
3863 g_summary ||'</A></FONT></TD></TR>');
3864 htp.p('<TR>');
3865 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3866 g_process_group ||'</FONT></TD>');
3867 IF (p_status IS NULL OR p_status = 'I') THEN
3868 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3869 g_num_procs ||'</FONT></TD>');
3870 END IF;
3871 IF (p_status IS NULL OR p_status = 'C') THEN
3872 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3873 g_num_completed_procs ||'</FONT></TD>');
3874 END IF;
3875 IF (p_status IS NULL OR p_status = 'A' OR p_status = 'I') THEN
3876 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3877 g_num_active_procs ||'</FONT></TD>');
3878 END IF;
3879 IF (p_status IS NULL OR p_status = 'N' OR p_status = 'I') THEN
3880 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699"><FONT CLASS="tableSubHeader">'||
3881 g_num_notstarted_procs ||'</FONT></TD>');
3882 END IF;
3883 htp.p('</TR>');
3884
3885 --
3886 -- Select the report summary data and populate the array with it.
3887 --
3888 populate_isr_process_groups (process_groups);
3889 v_locator := 2;
3890 FOR i IN 1..process_groups.COUNT LOOP
3891 v_display_data := '<A HREF="#'||
3892 process_groups(i).node_id || '">'||
3893 process_groups(i).display_name || '</A>';
3894 htp.p('<TR><TD>'|| v_display_data ||'</TD>');
3895 IF (p_status IS NULL OR p_status = 'I') THEN
3896 htp.p('<TD ALIGN="CENTER">'
3897 || process_groups(i).processes_count ||'</TD>');
3898 END IF;
3899 IF (p_status IS NULL OR p_status = 'C') THEN
3900 htp.p('<TD ALIGN="CENTER">'
3901 || process_groups(i).complete_procs_count ||'</TD>');
3902 END IF;
3903 IF (p_status IS NULL OR p_status = 'A' OR p_status = 'I') THEN
3904 htp.p('<TD ALIGN="CENTER">'
3905 || process_groups(i).active_procs_count ||'</TD>');
3906 END IF;
3907 IF (p_status IS NULL OR p_status = 'N' OR p_status = 'I') THEN
3908 htp.p('<TD ALIGN="CENTER">'
3909 || process_groups(i).not_started_procs_count ||'</TD>');
3910 END IF;
3911 htp.p('</TR>');
3912 END LOOP;
3913 htp.p('</TABLE><BR>');
3914 return;
3915
3916 EXCEPTION
3917 WHEN application_exception THEN
3918 RAISE;
3919 WHEN OTHERS THEN
3920 raise_error_msg (SQLCODE, SQLERRM,
3921 'PRINT_ISR_REPORT_SUMMARY', 'v_locator := ' || v_locator);
3922 END print_isr_report_summary;
3923
3924 /*
3925 **
3926 ** POPULATE_ISR_PROCESS_GROUPS
3927 ** ===========================
3928 **
3929 ** Private procedure.
3930 ** This procedure is responsible for selecting the available Process Groups,
3931 ** counts the number of processes, active, completed and not started processes
3932 ** for each and populates the passed array with the data to be used by the
3933 ** calling procedure (print_isr_report_summary).
3934 **
3935 */
3936 PROCEDURE populate_isr_process_groups (
3937 process_groups IN OUT NOCOPY StatusProcessGroups) IS
3938
3939 CURSOR products_cursor IS
3940 SELECT node_id,
3941 display_name,
3942 node_type,
3943 parent_node_id,
3944 status_code_name
3945 FROM az_monitor_reports
3946 START WITH parent_node_id IS NULL
3947 CONNECT BY PRIOR node_id = parent_node_id;
3948
3949 product_rec products_cursor%ROWTYPE;
3950 v_index INTEGER;
3951 v_total_procs INTEGER;
3952 v_active_procs INTEGER;
3953 v_complete_procs INTEGER;
3954 v_not_started_procs INTEGER;
3955 bln_IsProcess BOOLEAN;
3956
3957 v_ActiveStatus VARCHAR2(80);
3958 v_CompleteStatus VARCHAR2(80);
3959 v_NotStartedStatus VARCHAR2(80);
3960 v_locator PLS_INTEGER := 0;
3961 BEGIN
3962 --
3963 -- Initialize all counters.
3964 --
3965 v_index := 1;
3966 v_total_procs := 0;
3967 v_active_procs := 0;
3968 v_complete_procs := 0;
3969 v_not_started_procs := 0;
3970 v_locator := 1;
3971 --
3972 -- Loop through each retrived record.
3973 --
3974 FOR product_rec IN products_cursor LOOP
3975 IF (product_rec.parent_node_id IS NULL) THEN -- upper parent group
3976 v_locator := 2;
3977 process_groups(v_index).display_name := product_rec.display_name;
3978 process_groups(v_index).node_id := product_rec.node_id;
3979 process_groups(v_index).processes_count := 0;
3980 process_groups(v_index).active_procs_count := 0;
3981 process_groups(v_index).complete_procs_count := 0;
3982 process_groups(v_index).not_started_procs_count := 0;
3983 IF (v_index > 1) THEN
3984 process_groups(v_index-1).processes_count := v_total_procs;
3985 process_groups(v_index-1).active_procs_count := v_active_procs;
3986 process_groups(v_index-1).complete_procs_count := v_complete_procs;
3987 process_groups(v_index-1).not_started_procs_count := v_not_started_procs;
3988 END IF;
3989 --
3990 -- Reset all counters for the next process group.
3991 --
3992 v_total_procs := 0;
3993 v_active_procs := 0;
3994 v_complete_procs := 0;
3995 v_not_started_procs := 0;
3996
3997 v_index := v_index + 1;
3998 bln_IsProcess := FALSE;
3999 ELSIF (product_rec.node_type = 'P') THEN
4000 v_locator := 3;
4001 v_ActiveStatus := get_translation('AZ_PROCESS_STATUS', 'A');
4002 v_CompleteStatus := get_translation('AZ_PROCESS_STATUS', 'C');
4003 v_NotStartedStatus := get_translation('AZ_PROCESS_STATUS', 'N');
4004
4005 v_total_procs := v_total_procs + 1;
4006 IF (product_rec.status_code_name = v_ActiveStatus) THEN
4007 v_active_procs := v_active_procs + 1;
4008 ELSIF (product_rec.status_code_name = v_CompleteStatus) THEN
4009 v_complete_procs := v_complete_procs + 1;
4010 ELSIF (product_rec.status_code_name = v_NotStartedStatus) THEN
4011 v_not_started_procs := v_not_started_procs + 1;
4012 END IF;
4013 bln_IsProcess := TRUE;
4014 END IF;
4015 END LOOP;
4016 --
4017 -- Check if the last record is a process
4018 --
4019 IF (bln_IsProcess) THEN
4020 v_locator := 4;
4021 process_groups(v_index-1).processes_count := v_total_procs;
4022 process_groups(v_index-1).active_procs_count := v_active_procs;
4023 process_groups(v_index-1).complete_procs_count := v_complete_procs;
4024 process_groups(v_index-1).not_started_procs_count := v_not_started_procs;
4025 END IF;
4026
4027 EXCEPTION
4028 WHEN application_exception THEN
4029 RAISE;
4030 WHEN OTHERS THEN
4031 raise_error_msg (SQLCODE, SQLERRM,
4032 'POPULATE_ISR_PROCESS_GROUPS', 'v_locator := ' || v_locator);
4033 END populate_isr_process_groups;
4034
4035 /*------------------------------------------------------------------------
4036 * PRINT_USER_SUBHEADER
4037 *
4038 * Private procedure. Called by user_report.
4039 * Print the selected search criteria, such as user, process status, duration
4040 * or period, for the user performance report.
4041 *-----------------------------------------------------------------------*/
4042 PROCEDURE print_user_subheader(p_user IN VARCHAR2,
4043 p_status IN VARCHAR2,
4044 p_time_or_period IN VARCHAR2,
4045 p_operator IN VARCHAR2,
4046 p_days IN VARCHAR2,
4047 p_start IN VARCHAR2,
4048 p_end IN VARCHAR2) IS
4049
4050 p_display_name wf_roles.display_name%TYPE;
4051 v_locator PLS_INTEGER := 0;
4052 BEGIN
4053
4054 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="96%">');
4055 htp.p('<TR>');
4056 htp.p(' <TD WIDTH="50%" ALIGN="LEFT">');
4057 htp.p(' <TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
4058 htp.p(' <TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
4059 htp.p(' <TR><TD ALIGN="RIGHT">' || g_user || '</TD><TD> </TD><TD ALIGN="LEFT"><B>');
4060 v_locator := 1;
4061 IF (p_user IS NOT NULL) THEN
4062 SELECT display_name INTO p_display_name
4063 FROM wf_roles
4064 WHERE name = p_user;
4065 htp.p(p_user || ' (' || p_display_name || ')');
4066 ELSE
4067 htp.p(g_all);
4068 END IF;
4069 v_locator := 2;
4070 htp.p('</B></TD></TR>');
4071 htp.p(' <TR><TD ALIGN="RIGHT">'|| g_status ||'</TD><TD> </TD><TD ALIGN="LEFT"><B>');
4072 IF (p_status IS NOT NULL) THEN
4073 htp.p(get_translation('AZ_PROCESS_STATUS', p_status));
4074 ELSE
4075 htp.p(g_all);
4076 END IF;
4077 v_locator := 3;
4078 htp.p('</B></TD></TR>');
4079 htp.p(' </TABLE>');
4080 htp.p(' </TD><TD WIDTH="50%" ALIGN="RIGHT">');
4081 htp.p(' <TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
4082 htp.p(' <TR><TD ALIGN="RIGHT">' || g_as_of ||
4083 '</TD><TD> </TD><TD ALIGN="LEFT"><B>'||
4084 FND_DATE.date_to_displayDT(SYSDATE) || '</B></TD></TR>');
4085 IF (p_time_or_period = 'T') THEN -- Time Elapsed
4086 htp.p('<TR><TD ALIGN="RIGHT">' || g_duration ||
4087 '</TD><TD> </TD><TD ALIGN="LEFT"><B>');
4088 IF (p_operator = '<=') THEN
4089 htp.p(g_atmost);
4090 ELSE
4091 htp.p(g_atleast);
4092 END IF;
4093 htp.p(' '|| p_days ||' '|| g_days);
4094 htp.p('</B></TD></TR>');
4095 ELSE -- Date Range
4096 htp.p('<TR><TD ALIGN="RIGHT">' || g_active_by
4097 || '</TD><TD> </TD><TD ALIGN="LEFT"><B>' ||
4098 p_start || ' - ' || p_end || '</B></TD></TR>');
4099 END IF;
4100 v_locator := 4;
4101 htp.p('<TR><TD ALIGN="RIGHT"> </TD><TD ALIGN="LEFT"><B> </B></TD></TR>');
4102 htp.p(' </TABLE>');
4103 htp.p(' </TD>');
4104 htp.p('</TR>');
4105 htp.p('</TABLE>');
4106 htp.p('');
4107 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="0" CELLSPACING="0" WIDTH="98%">');
4108 htp.p('<TR><TD BGCOLOR="#CCCCCC"><IMG SRC="'|| g_image_path ||'/FNDDBPXC.gif"></TD></TR>');
4109 htp.p('</TABLE>');
4110 htp.p(' <BR>');
4111
4112 EXCEPTION
4113 WHEN application_exception THEN
4114 RAISE;
4115 WHEN OTHERS THEN
4116 raise_error_msg (SQLCODE, SQLERRM,
4117 'PRINT_USER_SUBHEADER', 'v_locator := ' || v_locator);
4118 END print_user_subheader;
4119
4120 /*------------------------------------------------------------------------
4121 * PRINT_UP_JSCRIPTS
4122 *
4123 * Private procedure. Called by user_param_page.
4124 * the print_up_jscripts prints the JavaScript functions that are used in
4125 * the user_parameter_page in order to do the following:
4126 * a) ensure that one radio button has been clicked
4127 * b) validate the 'days' field
4128 * c) set the appropriate fields to BLANK if a radio button is de-selected
4129 *-----------------------------------------------------------------------*/
4130 PROCEDURE print_up_jscripts IS
4131
4132 BEGIN
4133 htp.p('<SCRIPT LANGUAGE=Javascript>');
4134
4135 htp.p('function set_fields_to_null()');
4136 htp.p('{');
4137 htp.p(' if (document.Form1.p_time_or_period[0].checked)');
4138 htp.p(' {');
4139 htp.p(' document.Form1.p_start.value = "";');
4140 htp.p(' document.Form1.p_end.value = "";');
4141 htp.p(' }');
4142 htp.p(' else if (document.Form1.p_time_or_period[1].checked)');
4143 htp.p(' {');
4144 htp.p(' document.Form1.p_days.value = "0";');
4145 htp.p(' }');
4146 htp.p('}');
4147 htp.p('');
4148 htp.p('function CheckRelativeRadioButton(varChangedField)');
4149 htp.p('{');
4150 htp.p(' if (varChangedField == "p_days" ||
4151 varChangedField == "p_operator")');
4152 htp.p(' {');
4153 htp.p(' document.Form1.p_time_or_period[0].checked = true;');
4154 htp.p(' document.Form1.p_start.value = "";');
4155 htp.p(' document.Form1.p_end.value = "";');
4156 htp.p('');
4157 htp.p(' }');
4158 htp.p(' else if (varChangedField == "p_start" ||
4159 varChangedField == "p_end")');
4160 htp.p(' {');
4161 htp.p(' document.Form1.p_time_or_period[1].checked = true;');
4162 htp.p(' document.Form1.p_days.value = "0";');
4163 htp.p(' }');
4164 htp.p('}');
4165 htp.p('');
4166 htp.p('function DisplayAlert(Ctrl, strAlert)');
4167 htp.p('{');
4168 htp.p(' alert (strAlert);');
4169 htp.p(' Ctrl.focus();');
4170 htp.p(' return;');
4171 htp.p('}');
4172 htp.p('');
4173 htp.p('function is_numeric(input_field)');
4174 htp.p('{');
4175 htp.p(' input_string = "" + input_field;');
4176 htp.p('');
4177 htp.p(' for(var i = 0; i< input_string.length; i++)');
4178 htp.p(' {');
4179 htp.p(' var one_char = input_string.charAt(i);');
4180 htp.p('');
4181 htp.p(' if( one_char < "0" || one_char > "9")');
4182 htp.p(' {');
4183 htp.p(' return false;');
4184 htp.p(' }');
4185 htp.p(' }');
4186 htp.p(' return true;');
4187 htp.p('}');
4188 htp.p('');
4189 htp.p('function SubmitCurrentForm(CurForm)');
4190 htp.p('{');
4191 htp.p(' var value_string = CurForm.p_days.value;');
4192 htp.p('');
4193 htp.p(' if (!is_numeric(value_string))');
4194 htp.p(' {');
4195 htp.p(' DisplayAlert(CurForm.p_days, "'||
4196 g_js_slct_time_elapsed ||'");');
4197 htp.p(' return false;');
4198 htp.p(' }');
4199 htp.p(' else');
4200 htp.p(' CurForm.submit();');
4201 htp.p('}');
4202 htp.p('</SCRIPT>');
4203
4204 EXCEPTION
4205 WHEN application_exception THEN
4206 RAISE;
4207 WHEN OTHERS THEN
4208 raise_error_msg (SQLCODE, SQLERRM,
4209 'PRINT_UP_JSCRIPTS', '');
4210 END print_up_jscripts;
4211
4212 /*------------------------------------------------------------------------
4213 * PRINT_WELCOME_HEADER
4214 *
4215 * Private procedure. Called by start_page.
4216 * The print_welcome_header routine displays the FNDEXIT icon, the FNDHELP
4217 * icon, and the date (without a sub-header).
4218 * Note that the print_report_header routine will display an information
4219 * icon instead of the FNDEXIT icon.
4220 *-----------------------------------------------------------------------*/
4221 PROCEDURE print_welcome_header(p_title IN VARCHAR2) IS
4222 v_js_string VARCHAR2(32767);
4223 report_help_url VARCHAR2(2000);
4224
4225 BEGIN
4226
4227 IF (g_web_agent IS NULL) THEN
4228 get_web_agent;
4229 get_translated_labels;
4230 END IF;
4231
4232 htp.htmlOpen;
4233 htp.headOpen;
4234 htp.title(g_welcome_msg);
4235 print_html_style; -- print the 11i HTML style sheet.
4236 htp.headClose;
4237
4238 htp.p('<body bgcolor="#FFFFFF" LINK="#0000A0" VLINK="#0000A0">');
4239
4240 report_help_url := fnd_help.get_url('AZ', 'azw_report.start_page');
4241
4242 v_js_string := '<SCRIPT LANGUAGE=Javascript> function help_window() '||
4243 '{help_win=window.open('||''''|| report_help_url
4244 ||''''||','||''''||'help_win'
4245 ||''''||','||''''||'resizable=yes,scrollbars=yes,'||
4246 'toolbar=yes, width=550, height=250'||''''||')}';
4247
4248 htp.p(v_js_string);
4249
4250 htp.p('function exit_window() { self.close(); }');
4251 htp.p('');
4252 htp.p('');
4253 htp.p('</SCRIPT>');
4254 htp.tableOpen('align=center bgcolor=#336699 border=0 cellpadding=0 ' ||
4255 'cellspacing=0 valign=center width=100%');
4256 htp.tableRowOpen;
4257 htp.p('<td width=1%><br></td>');
4258 htp.p('<td><font class=banner> '|| g_welcome_msg ||'</font></td>');
4259 htp.p('<td width=2%><br></td>');
4260 htp.p('<td width=4%><a href="javascript:exit_window()" ' ||
4261 'onMouseOver="window.status=' || '''' ||
4262 g_exit || '''' || ';return true">' ||
4263 '<img src="/OA_MEDIA/FNDEXIT.gif" border="0" alt="'||
4264 g_exit ||'"></a></td>');
4265 htp.p('<td width=2%><br></td>');
4266 htp.p('<td width=4%><a href="javascript:help_window()" ' ||
4267 ' onMouseOver="window.status=' || '''' ||
4268 g_help || '''' || ';return true">' ||
4269 '<img src="'|| g_image_path ||'/azhelp.gif" border="0" alt="'||
4270 g_help ||'"></a></td>');
4271 htp.p('<td width=2%><br></td>');
4272 htp.p('<td width=12%><img src="'|| g_image_path ||'/azapplo.gif"></td>');
4273 htp.p('<td width=1%><br></td></tr>');
4274 htp.p('</table>');
4275 htp.p(FND_DATE.date_to_displaydate(SYSDATE));
4276 EXCEPTION
4277 WHEN application_exception THEN
4278 RAISE;
4279 WHEN OTHERS THEN
4280 raise_error_msg (SQLCODE, SQLERRM,
4281 'PRINT_WELCOME_HEADER', '');
4282 END print_welcome_header;
4283
4284 /*------------------------------------------------------------------------
4285 * PRODUCT_PARAM_PAGE
4286 *
4287 * Public procedure.
4288 * Generates parameter entry page in HTML for the product process report.
4289 * Performs the following steps:
4290 * 1. Get the URL for host server and all display strings if the URL is
4291 * null.
4292 * 2. Print the title and the instruction as the header.
4293 * 3. Display all products covered by Wizard in a two-column checkbox
4294 * layout with the application ids as the internal codes.
4295 * 4. Print the OK and Cancel buttons as the footer. OK button calls the
4296 * implementation report and passes the user selected product ids;
4297 * Cancel button calls the starting welcome page.
4298 *-----------------------------------------------------------------------*/
4299 PROCEDURE product_param_page IS
4300
4301 CURSOR product_list_cursor IS
4302 SELECT ag.dependency_parent_id,
4303 ag.application_id,
4304 fav.application_name
4305 FROM az_groups ag,
4306 fnd_application_vl fav
4307 WHERE ag.application_id IS NOT NULL
4308 AND ag.application_id = fav.application_id
4309 AND ag.process_type = g_current_mode
4310 ORDER BY ag.dependency_parent_id,
4311 ag.display_order;
4312
4313 v_product product_list_cursor%ROWTYPE;
4314 v_group az_groups.dependency_parent_id%TYPE;
4315 v_column INTEGER := 0;
4316 v_product_cnt INTEGER := 0;
4317 v_locator PLS_INTEGER := 0;
4318 BEGIN
4319 g_help_target := get_report_procedure('AZW_RPT_PPR');
4320 IF (g_web_agent IS NULL) THEN
4321 get_web_agent;
4322 get_translated_labels;
4323 END IF;
4324
4325 /*
4326 * The first form contains a hidden field labelled 'p_product_list', and
4327 * its value is a comma-delimited list of installed product codes. Each
4328 * time that a checkbox is clicked, the value of p_product_list is
4329 * updated. Note that the first form does not contain any SUBMIT or
4330 * Cancel buttons.
4331 *
4332 * The second form contains the report parameter field 'p_product_list'
4333 * and a SUBMIT button and a Cancel button. When users click on the
4334 * SUBMIT button in the second form, report parameter field
4335 * 'p_product_list' in the second form is assigned the value of the
4336 * hidden field 'p_product_list' in the first form.
4337 *
4338 * Due to a possible bug in HTML forms, an extra parameter is used in
4339 * the product parameter page in order to force the HTML page to display
4340 * the 'Cancel' button. Since the extra parameter in the HTML page is
4341 * sent to the product_report procedure, a second parameter is required
4342 * (called p_artificial) as a workaround.
4343 */
4344
4345 print_param_page_header(g_ppr, g_ppr_msg, NULL);
4346
4347 print_pp_jscripts;
4348
4349 htp.centerOpen;
4350 v_locator := 1;
4351 OPEN product_list_cursor;
4352 FETCH product_list_cursor INTO v_product;
4353 v_group := v_product.dependency_parent_id;
4354 v_column := 0;
4355
4356 /*
4357 * Open the first form and generate a table consisting of a two-column
4358 * list of radio buttons, each of which displays an installed product.
4359 *
4360 * The internal value of each radio button equals the product code, and
4361 * when users click on the 'SUBMIT' button, the JavaScript function
4362 * 'determine_checked_boxes' is invoked. This JavaScript constructs a
4363 * string that is a comma-delimited list of product codes that is based
4364 * on the checkboxes that have been selected. This string is assigned
4365 * as the value of a hidden field 'p_product_list'. Note that all the
4366 * checkboxes and the hidden field are contained within form #0.
4367 *
4368 * Next, the hidden field 'p_product_list' in form #0 is assigned to a
4369 * hidden field named 'p_product_list' in form #1. At this point, the
4370 * appropriate PL/SQL procedure is invoked in order to display the
4371 * report data.
4372 *
4373 * NOTES
4374 * -----
4375 * Users can return to the product parameter page in two distinct ways.
4376 *
4377 * 1) a) Users can click on 'Cancel' (which returns users to
4378 * the previous page), or specify a new URL
4379 * b) navigate back to the product parameter page
4380 *
4381 * 2) Users can do something similar to the following:
4382 * a) click the 'OK' button on the product parameter page
4383 * b) click the 'Context Process Report' button that is located
4384 * at the bottom of the product report page
4385 * c) select a Context type and click the 'OK' button
4386 * d) click the 'Product Process Report' button that is located
4387 * at the bottom of the context report page
4388 *
4389 * In scenario #1, the product parameter page will display only the
4390 * checkboxes, if any, that were checked by the users.
4391 *
4392 * In scenario #2, NONE of the checkboxes will be selected in the
4393 * product parameter page.
4394 */
4395
4396 htp.p('<form>');
4397 htp.p('<table border=0 cellpadding=1 cellspacing=1>');
4398 v_locator := 2;
4399 WHILE product_list_cursor%FOUND LOOP
4400 v_product_cnt := v_product_cnt + 1;
4401 IF (v_group <> v_product.dependency_parent_id) THEN
4402 -- print a line break bewteen groups
4403 htp.p('</tr><tr><td colspan=3 height=10></td></tr>');
4404
4405 v_group := v_product.dependency_parent_id;
4406 v_column := 0;
4407 END IF;
4408
4409 IF (v_column = 0) THEN
4410 -- print check box with v_product.application_name and
4411 -- v_product.application_id in the first column
4412 htp.tableRowOpen;
4413
4414 htp.p('<td align="left">'||
4415 '<input type=checkbox name='||'p_product_list'||v_product_cnt||
4416 ' value='||v_product.application_id||
4417 ' UNCHECKED> '||v_product.application_name||' </td>');
4418
4419 v_column := v_column + 1;
4420 htp.tableData(lpad_nbsp(3)); -- tab one column
4421
4422 ELSE
4423 -- print check box with v_product.application_name and
4424 -- v_product.application_id in the third column
4425
4426 htp.p('<td align="left">'||'<input type=checkbox name='||
4427 'p_product_list'||v_product_cnt||' value='||
4428 v_product.application_id||
4429 ' UNCHECKED> '||v_product.application_name||' </td>');
4430
4431 v_column := 0;
4432
4433 END IF;
4434 FETCH product_list_cursor INTO v_product;
4435 END LOOP;
4436 htp.tableRowClose;
4437 v_locator := 4;
4438 CLOSE product_list_cursor;
4439 htp.tableClose;
4440
4441 -- add the hidden field 'p_product_list' which will be used in order
4442 -- to maintain the comma-delimited list of selected product codes
4443
4444 htp.p('<input type="hidden" name="p_product_list" value="">');
4445
4446 -- hidden field for the number of installed products
4447 htp.p('<input type="hidden" name="p_product_count" value="'||
4448 v_product_cnt||'">');
4449 htp.p('</form>');
4450
4451 -- the second form contains the report parameter field 'p_product_list'
4452 htp.p('<form name="Form1" method="post" action="azw_report.product_report">');
4453
4454 -- Print the horizontal seperator
4455 htp.p('<table align=center border=0 cellpadding=0 cellspacing=2 width=96%>');
4456 --
4457 -- Print Footer line seperator.
4458 --
4459 print_footer_separator_line();
4460 --
4461 -- Print the ok and cancel buttons
4462 --
4463 v_locator := 5;
4464 print_ok_cancel_buttons('javascript:void(' ||
4465 'SubmitCurrentForm(document.Form1.p_product_list))');
4466 htp.p('<input type="hidden" name="p_product_list" value="">');
4467 htp.p('</form>');
4468 htp.centerClose;
4469 htp.bodyClose;
4470 htp.htmlClose;
4471
4472 EXCEPTION
4473 WHEN application_exception THEN
4474 RAISE;
4475 WHEN OTHERS THEN
4476 raise_error_msg (SQLCODE, SQLERRM,
4477 'PRODUCT_PARAM_PAGE', 'v_locator := ' || v_locator);
4478 END product_param_page;
4479
4480 /*------------------------------------------------------------------------
4481 * PRODUCT_REPORT
4482 *
4483 * Public procedure. Invoked by the OK button in product_param_page.
4484 * Generates the product process report in HTML. It performs the following
4485 * steps:
4486 * 1. Get the URL for host server and all display strings if the URL is ''.
4487 * 2. Get the string of concatenated product ids into an array of numbers.
4488 * 3. Print report header and subheader.
4489 * 5. Print Table opening tag and header.
4490 * 6. For each product id in the array, get the processes for the product
4491 * into the intermediate table.
4492 * 7. Get the trees from the intermediate table. For each node, print
4493 * the data.
4494 * 8. Print Table closing tag.
4495 * 9. Print links to related reports.
4496 *
4497 * Notes:
4498 * Second argument is a work around. Refer to the spec for detail.
4499 *-----------------------------------------------------------------------*/
4500 PROCEDURE product_report(p_product_list IN VARCHAR2,
4501 p_artificial IN VARCHAR2) IS
4502 v_app_id NUMBER;
4503 v_cnt BINARY_INTEGER;
4504 v_ids id_tbl_t;
4505 v_count PLS_INTEGER;
4506
4507 CURSOR hierarchies_cursor IS
4508 SELECT LPAD(g_blank, g_indent*(LEVEL-1))||display_name hierarchy,
4509 node_type,
4510 description,
4511 parent_node_id,
4512 node_id,
4513 context_type_name,
4514 LEVEL
4515 FROM az_planning_reports
4516 START WITH parent_node_id IS NULL
4517 CONNECT BY PRIOR node_id = parent_node_id
4518 AND PRIOR phase = phase;
4519
4520 v_locator PLS_INTEGER := 0;
4521 v_upper_group_names HierarchyLevels;
4522 v_item_type wf_process_activities.PROCESS_ITEM_TYPE%TYPE;
4523 v_process_name wf_process_activities.PROCESS_NAME%TYPE;
4524 v_process_groups VARCHAR2(4000);
4525
4526 BEGIN
4527 print_time_stamp('Start report');
4528 g_help_target := get_report_procedure('AZW_RPT_PPRR');
4529 IF (g_web_agent IS NULL) THEN
4530 get_web_agent;
4531 get_translated_labels;
4532 END IF;
4533
4534 print_js_open_url(TRUE);
4535
4536 print_time_stamp('End Translating messages and getting APP_WEB_AGENT');
4537 v_locator := 1;
4538 --
4539 -- get the product list string into the id table
4540 --
4541 v_cnt := 1;
4542 v_app_id := azw_proc.parse_application_ids(p_product_list, v_cnt);
4543 WHILE (v_app_id > -1) LOOP
4544 v_ids(v_cnt) := v_app_id;
4545 v_cnt := v_cnt + 1;
4546 v_app_id := azw_proc.parse_application_ids(p_product_list, v_cnt);
4547 END LOOP;
4548 v_locator := 2;
4549 print_report_header (g_ppr, TRUE, 'azw_report.product_param_page');
4550
4551 print_product_subheader (v_ids);
4552
4553 v_locator := 3;
4554 print_time_stamp('Start insert into temp table');
4555 FOR v_index IN 1..v_ids.COUNT LOOP
4556 --dbms_output.put_line('app_id: '||v_ids(v_index));
4557 get_product_processes(v_ids(v_index));
4558 END LOOP;
4559 print_time_stamp('End insert into temp table');
4560 v_locator := 4;
4561 --
4562 -- Print the Product Report Summary
4563 --
4564 print_planning_reports_summary (-1);
4565 print_time_stamp('End display report summary');
4566
4567 print_legend_link;
4568
4569 --
4570 -- Add a hyperlink to the Step Details report that
4571 -- shows all the steps for all the processes available
4572 -- to the selected products
4573 --
4574 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="1" CELLSPACING="1" WIDTH="100%">');
4575 /*htp.p('<TR><TD ALIGN="CENTER"><A HREF=javascript:void(OpenURL("' ||
4576 'azw_report.show_all_steps?p_selected_products='||
4577 p_product_list || '")) onMouseOver=window.status="' ||
4578 g_step_all_procs || '";return true><FONT SIZE="-1">' ||
4579 g_step_all_procs || '</FONT></A></TD></TR>');*/
4580 htp.p('<TR><TD ALIGN="CENTER"><A HREF=javascript:void(OpenURL("' ||
4581 'azw_report.show_all_steps?p_selected_products='||
4582 p_product_list || '"))><FONT SIZE="-1">' ||
4583 g_step_all_procs || '</FONT></A></TD></TR>');
4584 htp.p('</TABLE>');
4585 htp.p('<BR>');
4586
4587 --
4588 -- Display Report Details
4589 --
4590 htp.p('<TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
4591 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="2">' ||
4592 '<FONT CLASS="tableHeader">'|| g_details ||'</FONT></TD></TR>');
4593 htp.p('<TR>');
4594 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4595 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_hierarchy ||'</FONT></TH>');
4596 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4597 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_description ||'</FONT></TH>');
4598
4599 -- print hierarchies nodes
4600 v_count := 0;
4601 v_locator := 5;
4602 FOR one_node IN hierarchies_cursor LOOP
4603 --
4604 -- Keep track of all the upper groups to be able to display
4605 -- them in a flat directory structure like
4606 -- "\Common Applications\System Administration\"
4607 --
4608 v_upper_group_names(one_node.level) := TRIM(one_node.hierarchy);
4609
4610 IF (one_node.parent_node_id IS NULL) THEN
4611 IF (v_count > 0) THEN
4612 print_back_to_top(2);
4613 END IF;
4614 htp.p('<TR><TD ALIGN="LEFT" COLSPAN="2" BGCOLOR="#666666" NOWRAP>' ||
4615 '<i><FONT COLOR="#FFFFFF"><A NAME="PH-1_'||
4616 one_node.node_id || '">' ||
4617 one_node.hierarchy || '</A></FONT></i></TD>');
4618 ELSIF (one_node.node_type = 'G') THEN
4619 htp.tableRowOpen;
4620 htp.tableData('<i>'||lpad_nbsp(one_node.level)|| one_node.hierarchy ||
4621 '</i>', 'LEFT', '', 'NOWRAP');
4622 ELSE
4623 get_process_type_name (one_node.node_id, v_item_type, v_process_name);
4624 v_process_groups := get_parent_structure(v_upper_group_names, one_node.level - 1, '\\');
4625 htp.tableRowOpen;
4626 htp.tableData(lpad_nbsp(one_node.level) || '<A HREF=javascript:void(OpenURL("'||
4627 'azw_report.display_process_steps?p_selected_products=' || p_product_list ||
4628 '&p_item_type=' || v_item_type ||
4629 '&p_process_name=' || v_process_name ||
4630 '&p_context_type_name=' || url_encode(one_node.context_type_name) ||
4631 '&p_process_groups=' || url_encode(v_process_groups) ||
4632 '&p_new_call=YES&p_external_call=YES' || '"))><b>'
4633 || TRIM(one_node.hierarchy) ||
4634 '</b></A>', 'LEFT', '', 'NOWRAP');
4635 htp.tableData(one_node.description, 'LEFT', '', 'NOWRAP');
4636 END IF;
4637 htp.tableRowClose;
4638 v_count := v_count + 1;
4639 END LOOP;
4640 v_locator := 6;
4641 print_back_to_top(2);
4642 -- print Table closing tag
4643 htp.tableClose;
4644 -- print report legend
4645 print_legend;
4646 -- print related report links
4647 print_related_reports('AZW_RPT_IPR', 'AZW_RPT_CPR');
4648 print_time_stamp('End display report details');
4649 COMMIT;
4650
4651 EXCEPTION
4652 WHEN NO_DATA_FOUND THEN
4653 -- print Table closing tag
4654 htp.tableClose;
4655 print_related_reports('AZW_RPT_IPR', 'AZW_RPT_CPR');
4656 WHEN application_exception THEN
4657 RAISE;
4658 WHEN OTHERS THEN
4659 raise_error_msg (SQLCODE, SQLERRM,
4660 'PRODUCT_REPORT', 'v_locator := ' || v_locator);
4661 END product_report;
4662
4663 /*------------------------------------------------------------------------
4664 * START_PAGE
4665 *
4666 * Public procedure.
4667 * Define 5 hyperlinks for the following reports:
4668 * Implementation Process Report (IPR) (group 1)
4669 * Context Process Report (CPR) (group 1)
4670 * Product Process Report (PPR) (group 1)
4671 * Implementation Status Report (ISR) (group 2)
4672 * User Performance Report (UPR) (group 2)
4673 *-----------------------------------------------------------------------*/
4674 PROCEDURE start_page IS
4675 v_link VARCHAR2(240);
4676 BEGIN
4677 IF (g_web_agent IS NULL) THEN
4678 get_web_agent;
4679 get_translated_labels;
4680 END IF;
4681
4682 -- display the header containing the welcome message and two icons
4683 print_welcome_header(g_welcome_msg);
4684
4685 -- branch out depending on the current Wizard Mode
4686 IF (g_current_mode = 'IMP') THEN
4687 print_imp_start_page;
4688 ELSE
4689 print_upgrade_start_page;
4690 END IF;
4691 htp.bodyClose;
4692 htp.htmlClose;
4693
4694 EXCEPTION
4695 WHEN application_exception THEN
4696 RAISE;
4697 WHEN OTHERS THEN
4698 raise_error_msg (SQLCODE, SQLERRM,
4699 'START_PAGE', '');
4700 END start_page;
4701
4702 /*------------------------------------------------------------------------
4703 * STATUS_PARAM_PAGE
4704 *
4705 * Public procedure.
4706 * Generates parameter entry page in HTML for implmentation status report.
4707 * Performs the following steps:
4708 * 1. Get the URL for host server and all display strings if the URL is
4709 * null.
4710 * 2. Print the title and the instruction as the header.
4711 * 3. Display all valid statuses in a drop down list with the lookup_codes
4712 * as the internal code, making the blank as the default option meaning
4713 * all statuses.
4714 * 4. Print the OK and Cancel buttons as the footer. OK button calls the
4715 * status report and passes the user selected status(es); Cancel button
4716 * calls the starting welcome page.
4717 *-----------------------------------------------------------------------*/
4718 PROCEDURE status_param_page IS
4719
4720 CURSOR statuses_cursor IS
4721 SELECT lookup_code, meaning
4722 FROM fnd_lookups
4723 WHERE lookup_type = 'AZ_PROCESS_STATUS'
4724 ORDER BY lookup_code;
4725
4726 BEGIN
4727 g_help_target := get_report_procedure('AZW_RPT_ISR');
4728 IF (g_web_agent IS NULL) THEN
4729 get_web_agent;
4730 get_translated_labels;
4731 END IF;
4732
4733 print_param_page_header(g_isr, g_isr_msg, g_mode_label);
4734
4735 htp.p('<table align="center" border="0" cellpadding="0" ' ||
4736 'cellspacing="2" width="96%">');
4737 htp.p('<tr><td colspan=4><br></td>');
4738 htp.p('<form name="Form1" method="post" ' ||
4739 'action="azw_report.status_report"></tr>');
4740
4741 -- create the pop-up list of process status
4742 htp.p('<tr><td align=right WIDTH="50%"><font class=normal>' ||
4743 g_proc_status || '</font></td>');
4744 htp.p('<td align=left colspan=3><select name="p_status" size=1>');
4745
4746 -- make the blank as the default and first option meaning for all statuses
4747 htp.p('<option value="">'|| g_all ||'</option>');
4748
4749 FOR one_status IN statuses_cursor LOOP
4750 IF (one_status.lookup_code <> 'ALL') THEN
4751 htp.p('<option value="'||one_status.lookup_code||'"> '||
4752 one_status.meaning||'</option>');
4753 END IF;
4754 END LOOP;
4755 htp.p('</select></td></tr>');
4756
4757 print_param_page_footer;
4758
4759 EXCEPTION
4760 WHEN application_exception THEN
4761 RAISE;
4762 WHEN OTHERS THEN
4763 raise_error_msg (SQLCODE, SQLERRM,
4764 'STATUS_PARAM_PAGE', '');
4765 END status_param_page;
4766
4767 /*------------------------------------------------------------------------
4768 * STATUS_REPORT
4769 *
4770 * Public procedure. Invoked by the OK button in status_param_page.
4771 * Generates the implementation status report in HTML. It performs the
4772 * following steps:
4773 * 1. Get the URL for host server and all display strings if the URL is ''.
4774 * 2. Print report header and subheader.
4775 * 4. Print Table Header based on the selected status.
4776 * 5. Get tasks, processes, groups for the particular status into the
4777 * intermediate table.
4778 * 6. Get the trees from the intermediate table. For each node, print the
4779 * Table Row and Table Data based on the selected status.
4780 * 7. Print Table closing tag.
4781 * 8. Print links to related reports.
4782 *-----------------------------------------------------------------------*/
4783 PROCEDURE status_report(p_status IN VARCHAR2) IS
4784
4785 CURSOR hierarchies_cursor IS
4786 SELECT LPAD(g_blank, g_indent*(LEVEL-1))||display_name hierarchy,
4787 node_type,
4788 context_type_name,
4789 context_name,
4790 status_code_name,
4791 assigned_user,
4792 FND_DATE.date_to_displaydate(start_date) start_date,
4793 FND_DATE.date_to_displaydate(end_date) end_date,
4794 DECODE(duration, '', '', duration||' '||g_days) time_elapsed,
4795 LEVEL,
4796 parent_node_id,
4797 node_id,
4798 COMMENTS
4799 FROM az_monitor_reports
4800 START WITH parent_node_id IS NULL
4801 CONNECT BY PRIOR node_id = parent_node_id;
4802
4803 v_colspan PLS_INTEGER;
4804 v_count PLS_INTEGER;
4805 v_locator PLS_INTEGER := 0;
4806 v_href VARCHAR2(4000);
4807 v_upper_group_names HierarchyLevels;
4808
4809 v_ctx_type_name az_monitor_reports.context_type_name%TYPE;
4810 v_process_groups VARCHAR2(4000);
4811 v_item_type AZ_TASKS_V.ITEM_TYPE%TYPE;
4812 v_item_key AZ_TASKS_V.ITEM_KEY%TYPE;
4813
4814 BEGIN
4815 print_time_stamp('Start report');
4816 g_help_target := get_report_procedure('AZW_RPT_ISRR');
4817 IF (g_web_agent IS NULL) THEN
4818 get_web_agent;
4819 get_translated_labels;
4820 END IF;
4821 v_locator := 1;
4822 print_report_header(g_isr || ' (' || g_mode_label || ')',
4823 TRUE, 'azw_report.status_param_page');
4824
4825 print_js_open_url;
4826
4827 print_status_subheader (p_status);
4828
4829
4830 print_time_stamp('Start Temp Table Inserts/Queries.');
4831 get_status_groups(p_status);
4832 get_status_processes(p_status);
4833 get_status_tasks(p_status);
4834 print_time_stamp('End Temp Table Inserts/Queries.');
4835
4836 --
4837 -- Display the Report Summary
4838 --
4839 print_isr_report_summary (p_status);
4840
4841 print_legend_link;
4842
4843 --
4844 -- print report details and header based on p_status
4845 --
4846 htp.p('<TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
4847 IF (p_status IS NULL OR p_status='A' OR p_status='C' OR p_status='I') THEN
4848 v_colspan := 8;
4849 ELSE
4850 v_colspan := 4;
4851 END IF;
4852 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="'|| v_colspan ||'">' ||
4853 '<FONT CLASS="tableHeader">'|| g_details ||'</FONT></TD></TR>');
4854
4855 htp.p('<TR>');
4856 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4857 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_hierarchy ||'</FONT></TH>');
4858 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4859 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_ctxt_type ||'</FONT></TH>');
4860 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4861 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_ctxt_name ||'</FONT></TH>');
4862 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4863 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_status ||'</FONT></TH>');
4864 IF (p_status IS NULL OR p_status='A' OR p_status='C' OR p_status='I') THEN
4865 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4866 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_user ||'</FONT></TH>');
4867 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4868 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_start ||'</FONT></TH>');
4869 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4870 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_end ||'</FONT></TH>');
4871 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
4872 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_duration ||'</FONT></TH>');
4873 END IF;
4874 htp.tableRowClose;
4875 v_count := 0;
4876 v_locator := 2;
4877 FOR one_node IN hierarchies_cursor LOOP
4878 --
4879 -- Keep track of all the upper groups to be able to display
4880 -- them in a flat directory structure like
4881 -- "\Common Applications\System Administration\"
4882 --
4883 v_upper_group_names(one_node.level) := TRIM(one_node.hierarchy);
4884 -- print hierarchy, context type, context name and status
4885 htp.tableRowOpen;
4886 IF (one_node.parent_node_id IS NULL) THEN
4887 IF (v_count > 0) THEN
4888 print_back_to_top(v_colspan);
4889 END IF;
4890 htp.p('<TR><TD ALIGN="LEFT" COLSPAN="'|| v_colspan
4891 ||'" BGCOLOR="#666666" ' ||
4892 'NOWRAP><i><FONT COLOR="#FFFFFF"><A NAME="' ||
4893 one_node.node_id || '">' ||
4894 one_node.hierarchy || '</A></FONT></i></TD>');
4895 ELSIF (one_node.node_type = 'G') THEN
4896 htp.tableData('<i>'||lpad_nbsp(one_node.level)|| one_node.hierarchy ||
4897 '</i>', 'LEFT', '', 'NOWRAP');
4898 htp.tableData('<i>'||one_node.context_type_name||'</i>');
4899 htp.tableData('<i>'||one_node.context_name||'</i>');
4900 htp.tableData('<i>'||one_node.status_code_name||'</i>', 'LEFT', '', 'NOWRAP');
4901 IF (p_status IS NULL OR p_status='A' OR p_status='C' OR p_status='I')
4902 THEN
4903 -- print assigned user, start/end dates, and duration
4904 htp.tableData('<i>'||one_node.assigned_user||'</i>');
4905 htp.tableData('<i>'||one_node.start_date||'</i>');
4906 htp.tableData('<i>'||one_node.end_date||'</i>');
4907 htp.tableData('<i>'||one_node.time_elapsed||'</i>', 'RIGHT');
4908 END IF;
4909 ELSIF (one_node.node_type = 'P') THEN
4910 -- To be used by the task HREF
4911 v_process_groups := get_parent_structure(v_upper_group_names, one_node.level, '\\');
4912 v_ctx_type_name := one_node.context_type_name;
4913
4914 htp.tableData('<b>'||lpad_nbsp(one_node.level)|| one_node.hierarchy ||
4915 '</b>', 'LEFT', '', 'NOWRAP');
4916 htp.tableData(one_node.context_type_name||'</b>');
4917 htp.tableData(one_node.context_name||'</b>');
4918 htp.tableData(one_node.status_code_name||'</b>', 'LEFT', '', 'NOWRAP');
4919 IF (p_status IS NULL OR p_status='A' OR p_status='C' OR p_status='I')
4920 THEN
4921 -- print assigned user, start/end dates, and duration
4922 htp.tableData('<b>'||one_node.assigned_user||'</b>');
4923 htp.tableData('<b>'||one_node.start_date||'</b>');
4924 htp.tableData('<b>'||one_node.end_date||'</b>');
4925 htp.tableData('<b>'||one_node.time_elapsed||'</b>', 'RIGHT');
4926 END IF;
4927 IF (one_node.comments IS NOT NULL) THEN
4928 htp.tableRowClose;
4929 htp.tableRowOpen;
4930 htp.tableData('<b>' || lpad_nbsp(one_node.level)||'('||
4931 g_comments ||':'||one_node.comments
4932 ||')</b>', 'LEFT', '', 'NOWRAP');
4933 END IF;
4934 ELSE /* node_type = 'T' */
4935 get_task_type_key(one_node.node_id, v_item_type, v_item_key);
4936 v_href := '<A HREF=javascript:void(OpenURL("' ||
4937 'azw_report.task_details?' ||
4938 'p_process_groups=' || url_encode(v_process_groups) ||
4939 '&p_ctx_type=' || url_encode(v_ctx_type_name) ||
4940 '&p_status=' || url_encode(one_node.status_code_name) ||
4941 '&p_start=' || url_encode(one_node.start_date) ||
4942 '&p_end=' || url_encode(one_node.end_date)||
4943 '&p_time_elapsed=' || url_encode(one_node.time_elapsed) ||
4944 '&p_item_type=' || v_item_type ||
4945 '&p_item_key=' || v_item_key || '")) onMouseOver=window.status='
4946 || g_task_details || '";return true>';
4947 htp.p('<td bgcolor=#DDDDDD> </td>');
4948 htp.p('<td bgcolor=#DDDDDD> </td>');
4949 htp.p('<td bgcolor=#DDDDDD> </td>');
4950 htp.p('<TD ALIGN="LEFT" BGCOLOR="#DDDDDD" NOWRAP>'|| v_href || one_node.status_code_name || '</A></TD>');
4951
4952 IF (p_status IS NULL OR p_status='A' OR p_status='C' OR p_status='I') THEN
4953 -- print assigned user, start/end dates, and duration
4954 htp.tableData(one_node.assigned_user, '', '', '', '', '',
4955 'bgcolor=#DDDDDD');
4956 htp.tableData(one_node.start_date, '', '', '', '', '',
4957 'bgcolor=#DDDDDD');
4958 htp.tableData(one_node.end_date||' ', '', '', '', '', '',
4959 'bgcolor=#DDDDDD');
4960 htp.tableData(one_node.time_elapsed, 'RIGHT', '', '', '', '',
4961 'bgcolor=#DDDDDD');
4962 END IF;
4963 END IF;
4964
4965 -- print Table row closing tag
4966 htp.tableRowClose;
4967 v_count := v_count + 1;
4968 END LOOP;
4969 v_locator := 3;
4970 print_back_to_top(v_colspan);
4971 -- print Table closing tag
4972 htp.tableClose;
4973 -- print report legend
4974 print_legend (TRUE);
4975
4976 -- print related report links
4977 print_related_reports('AZW_RPT_UPR');
4978 print_time_stamp('End report');
4979
4980 COMMIT;
4981
4982 EXCEPTION
4983 WHEN NO_DATA_FOUND THEN
4984 -- print Table closing tag
4985 htp.tableClose;
4986 print_related_reports('AZW_RPT_UPR');
4987 WHEN application_exception THEN
4988 RAISE;
4989 WHEN OTHERS THEN
4990 raise_error_msg (SQLCODE, SQLERRM,
4991 'STATUS_REPORT', 'v_locator := ' || v_locator);
4992 END status_report;
4993
4994 /*------------------------------------------------------------------------
4995 * USER_PARAM_PAGE
4996 *
4997 * Public procedure.
4998 * Generates parameter entry page in HTML for user performance report.
4999 * Performs the following steps:
5000 * 1. Get the URL for host server and all display strings if the URL is
5001 * null.
5002 * 2. Print the title and the instruction as the header.
5003 * 3. Display the selection boxes for users, process statuses, duration
5004 * and start and end periods.
5005 * 4. Print the OK and Cancel buttons as the footer. OK button calls the
5006 * user report and passes the selected parameters; Cancel button calls
5007 * the starting welcome page.
5008 *-----------------------------------------------------------------------*/
5009 PROCEDURE user_param_page IS
5010
5011 CURSOR valid_users_cursor IS
5012 SELECT DISTINCT wf.text_value name,
5013 wfr.display_name
5014 FROM wf_item_attribute_values wf, wf_roles wfr
5015 WHERE wf.item_type LIKE 'AZ%'
5016 AND wf.text_value = wfr.name
5017 AND wf.name = 'AZW_IA_ROLE'
5018 ORDER BY 1;
5019
5020 CURSOR statuses_cursor IS
5021 SELECT lookup_code, meaning
5022 FROM fnd_lookups
5023 WHERE lookup_type = 'AZ_PROCESS_STATUS'
5024 AND lookup_code IN ('A', 'C')
5025 ORDER BY lookup_code;
5026
5027 CURSOR duration_ranges_cursor IS
5028 SELECT lookup_code, meaning
5029 FROM fnd_lookups
5030 WHERE lookup_type = 'AZ_REPORT_DURATION_RANGE'
5031 ORDER BY lookup_code;
5032 v_locator PLS_INTEGER := 0;
5033
5034 BEGIN
5035
5036 /*
5037 * The User Performance Parameter Page consists of one table
5038 * with three pop-up lists (user, status, and execution time),
5039 * a numeric 'days' field, a date-from field, and a date-to field:
5040 *
5041 *
5042 * ----------
5043 * User | |
5044 * ----------
5045 * ----------
5046 * Process Status | |
5047 * ----------
5048 * ---------- -----
5049 * o Execution Time | | | | Days
5050 * ---------- -----
5051 * -------------- ------------
5052 * o Start Date | | - | |
5053 * -------------- ------------
5054 *
5055 *
5056 * Fields that are on a "row" with a radio button can only be
5057 * assigned a value when their radio button has been selected,
5058 * otherwise the input field will be set to blank. Due to the
5059 * extremely large number of date formats, the validation for
5060 * the start date and the end date is performed on the server
5061 * rather than the client.
5062 */
5063
5064 /*
5065 * Java functions appear at the beginning of the body. These
5066 * functions keep track of the currently clicked radio button,
5067 * determine when to set fields to blank, and perform numeric
5068 * validation of the 'p_days' field.
5069 *
5070 * The 'execution' radio button is the default value. Each
5071 * time users click on a radio button, the affected fields
5072 * are set to blank. For example, when users click on the
5073 * 'execution' radio button, the start and end dates are set
5074 * to blanks.
5075 *
5076 * NOTES
5077 * 1) the Java function 'set_fields_to_null' determines which
5078 * radio button has been clicked
5079 *
5080 * 2) the default radio button does not have an actual value;
5081 * hence, it is hard-coded to zero because it precedes the
5082 * 'period_from' radio button
5083 *
5084 * 3) due to point 2), if the default radio button is changed
5085 * (i.e., to the other radio button or a new radio button),
5086 * then its value must be altered in the Java function, and
5087 * its value is one less than the order of its appearance
5088 * in the HTML page when the HTML page is traversed from
5089 * left-to-right and from top-to-bottom.
5090 */
5091 g_help_target := get_report_procedure('AZW_CPT_UPR');
5092 IF (g_web_agent IS NULL) THEN
5093 get_web_agent;
5094 get_translated_labels;
5095 END IF;
5096 print_param_page_header(g_upr, g_upr_msg, g_mode_label);
5097 print_up_jscripts;
5098
5099 htp.p('<form name="Form1" method="post" action="azw_report.user_report">');
5100 htp.p('<table align=center border=0 cellpadding=0 cellspacing=2 width=96%>');
5101 htp.p('<tr><td colspan=2><br></td></tr>');
5102
5103 --
5104 -- Display the list of users (extracted from the database).
5105 --
5106 htp.p('<tr><td align=right width=50%><font class=normal>'||
5107 g_user ||'</font></td>');
5108 htp.p('<td align=left width=50%><select name="p_user" size=1>');
5109 htp.p('<option value="">'||g_all||'</option>');
5110 v_locator := 1;
5111 FOR one_user IN valid_users_cursor LOOP
5112 -- print each valid user as an option
5113 htp.p('<option value="'||one_user.name||'">'|| one_user.name
5114 || ' (' || one_user.display_name || ')' ||
5115 '</option>');
5116 END LOOP;
5117 htp.p('</select></font><td></tr>');
5118
5119 --
5120 -- Display the list of status values.
5121 --
5122 htp.p('<tr><td align=right width=50%><font class=normal>'||
5123 g_status ||'</font></td>');
5124 htp.p('<td align=left width=50%><select name="p_status" size=1>');
5125 htp.p('<option value="">'||g_all||'</option>');
5126 v_locator := 2;
5127 FOR one_status IN statuses_cursor LOOP
5128 -- print each status as an option
5129 htp.p('<option value="'||one_status.lookup_code||'">'||
5130 one_status.meaning||'</option>');
5131 END LOOP;
5132 htp.p('</select></td></tr>');
5133
5134 -- Note that the default value for the radio button is "T",
5135 -- which represents the date range.
5136
5137 --
5138 -- Display Both Radio buttons in the first <TD> with a
5139 -- rowspan of 2 (to align the radio buttons)
5140 --
5141 htp.p('<tr><td align=right WIDTH="50%" ROWSPAN=2>');
5142 htp.p('<table border=0>');
5143 htp.p('<TR>');
5144 htp.p('<TD><input type=radio name=p_time_or_period value="T" ' ||
5145 'CHECKED align=right onClick="set_fields_to_null()"></td>');
5146 htp.p('<td align=right><font class=normal>'||
5147 g_duration ||'</font></td>');
5148 htp.p('</TR>');
5149 htp.p('<TR><TD><BR></TD></TR>');
5150 htp.p('<TR>');
5151 htp.p('<TD><input type=radio name=p_time_or_period value="P" ' ||
5152 ' align=right onClick="set_fields_to_null()"></td>');
5153 htp.p('<td align=right><font class=normal>'||
5154 g_active_by ||'</font></td>');
5155 htp.p('</TR>');
5156 htp.p('</TABLE>');
5157 htp.p('</td>');
5158 --
5159 -- Display the rest of the Time Elapsed row.
5160 --
5161 htp.p('<td align=left>');
5162 htp.p('<select name="p_operator" size=1 ' ||
5163 'onChange="CheckRelativeRadioButton(' ||
5164 '''' ||'p_operator' || '''' || ')">');
5165 v_locator := 3;
5166 FOR one_range IN duration_ranges_cursor LOOP
5167 -- htp.p one_range as an option, with '>=' as the default value
5168 IF (one_range.lookup_code = '>=') THEN
5169 htp.p('<option selected value="'||one_range.lookup_code||'">'||
5170 one_range.meaning||'</option>');
5171 ELSE
5172 htp.p('<option value="'||one_range.lookup_code||'">'||' '||
5173 one_range.meaning||'</option>');
5174 END IF;
5175 END LOOP;
5176
5177 htp.p('</select> ');
5178 htp.p('<input type=text value ="0" name="p_days" ' ||
5179 'onChange="CheckRelativeRadioButton(' ||
5180 '''' ||'p_days' || '''' || ')" ' ||
5181 'size=3><font class="normal">'|| g_days ||'</font>');
5182 htp.p('</td></tr>');
5183
5184 --
5185 -- Display the Start Date "From" - "To" row.
5186 --
5187 htp.p('<tr>');
5188 htp.p('<td align=left valign="bottom">');
5189 htp.p('<TABLE BORDER="0">');
5190 htp.p('<TR><TD ALIGN="LEFT">' || g_from || '</TD><TD> </TD>');
5191 htp.p('<TD ALIGN="LEFT">' || g_to || '</TD></TR>');
5192 htp.p('<TR><TD ALIGN="LEFT">');
5193 htp.p('<input type=text name="p_start" size=10 ' ||
5194 'onChange="CheckRelativeRadioButton(' ||
5195 '''' ||'p_start' || '''' || ')">');
5196 htp.p('</TD><TD>-</TD><TD ALIGN="LEFT">');
5197 htp.p('<input type=text name="p_end" size=10 ' ||
5198 'onChange="CheckRelativeRadioButton(' ||
5199 '''' ||'p_end' || '''' || ')">');
5200 htp.p('</td></tr></TABLE></TD></TR>');
5201 htp.p('<tr><td width=50%></td><td align="left" width="50%"><font class=normal>' ||
5202 g_dateformat_msg || ' ' || FND_DATE.user_mask ||'</FONT></TD></TR>');
5203
5204 -- Print the horizontal seperator
5205 print_footer_separator_line();
5206 -- Print the ok and cancel buttons
5207 print_ok_cancel_buttons('javascript:' ||
5208 'void(SubmitCurrentForm(document.Form1))');
5209
5210 htp.p('</form>');
5211 htp.centerClose;
5212 htp.bodyClose;
5213 htp.htmlClose;
5214 EXCEPTION
5215 WHEN application_exception THEN
5216 RAISE;
5217 WHEN OTHERS THEN
5218 raise_error_msg (SQLCODE, SQLERRM,
5219 'USER_PARAM_PAGE', 'v_locator := ' || v_locator);
5220 END user_param_page;
5221
5222 /*------------------------------------------------------------------------
5223 * USER_REPORT
5224 *
5225 * Public procedure. Invoked by the OK button in user_param_page.
5226 * Generates the user performance report in HTML. It performs the following
5227 * steps:
5228 * 1. Get the URL for host server and all display strings if the URL is ''.
5229 * 2. Print report header and subheader.
5230 * 4. Print Table opening tag and header based on selected user.
5231 * 4. If one particular user is chosen, get user trees by other search
5232 * criteria into the intermediate table based on duration ('at most' or
5233 * 'at least') or period. Retrieve the trees from the intermediate
5234 * table. For each row retrieved, print Table Row and Table Data.
5235 * 5. If all users is chosen, for each user, get user trees by other search
5236 * criteria into the intermediate table based on duration ('at most' or
5237 * 'at least') or period. For each user, retrieve the trees from the
5238 * intermediate table, For each row retrieved, print Table Row and
5239 * Table Data.
5240 * 6. Print Table closing tag.
5241 * 7. Print links to related reports.
5242 *-----------------------------------------------------------------------*/
5243 PROCEDURE user_report (p_user IN VARCHAR2,
5244 p_status IN VARCHAR2,
5245 p_time_or_period IN VARCHAR2,
5246 p_operator IN VARCHAR2,
5247 p_days IN VARCHAR2,
5248 p_start IN VARCHAR2,
5249 p_end IN VARCHAR2) IS
5250
5251 v_start DATE;
5252 v_end DATE;
5253
5254 CURSOR valid_users_cursor IS
5255 SELECT DISTINCT wf.text_value user_name,
5256 wfr.display_name
5257 FROM wf_item_attribute_values wf, wf_roles wfr
5258 WHERE wf.item_type LIKE 'AZ%'
5259 AND wf.text_value = wfr.name
5260 AND wf.name = 'AZW_IA_ROLE'
5261 ORDER BY 1;
5262
5263 CURSOR hierarchies_cursor(x_user VARCHAR2) IS
5264 SELECT assigned_user,
5265 LPAD(g_blank, g_indent*(LEVEL-1))||display_name hierarchy,
5266 node_type,
5267 context_type_name,
5268 context_name,
5269 status_code_name,
5270 FND_DATE.date_to_displaydate(start_date) start_date,
5271 FND_DATE.date_to_displaydate(end_date) end_date,
5272 DECODE(duration, '', '', duration||' '||g_days) time_elapsed,
5273 parent_node_id,
5274 LEVEL,
5275 node_id,
5276 comments
5277 FROM az_monitor_reports
5278 WHERE assigned_user = x_user
5279 START WITH parent_node_id IS NULL
5280 CONNECT BY PRIOR node_id = parent_node_id
5281 AND PRIOR assigned_user = assigned_user
5282 AND assigned_user = x_user;
5283
5284 v_count INTEGER;
5285 i PLS_INTEGER;
5286 v_locator PLS_INTEGER := 0;
5287 v_item_type AZ_TASKS_V.ITEM_TYPE%TYPE;
5288 v_item_key AZ_TASKS_V.ITEM_KEY%TYPE;
5289 v_href VARCHAR2(4000);
5290 v_upper_group_names HierarchyLevels;
5291
5292 v_ctx_type_name az_monitor_reports.context_type_name%TYPE;
5293 v_process_groups VARCHAR2(4000);
5294
5295 BEGIN
5296
5297 print_time_stamp('Start report');
5298 g_help_target := get_report_procedure('AZW_RPT_UPRR');
5299 IF (g_web_agent IS NULL) THEN
5300 get_web_agent;
5301 get_translated_labels;
5302 END IF;
5303 print_report_header(g_upr || ' (' || g_mode_label || ')', TRUE, 'azw_report.user_param_page');
5304
5305 print_js_open_url;
5306
5307 IF (p_time_or_period = 'P') THEN
5308 IF (p_start IS NULL) THEN
5309 v_start := TO_DATE('01/01/1950', 'DD/MM/YYYY');
5310 ELSE
5311 v_start := FND_DATE.displaydate_to_date(p_start);
5312 END IF;
5313 IF (p_end IS NULL) THEN
5314 v_end := SYSDATE + 1;
5315 ELSE
5316 v_end := FND_DATE.displaydate_to_date(p_end);
5317 END IF;
5318 END IF;
5319
5320 print_user_subheader(p_user, p_status, p_time_or_period, p_operator,
5321 p_days, FND_DATE.date_to_displaydate(v_start),
5322 FND_DATE.date_to_displaydate(v_end));
5323 v_locator := 1;
5324 print_time_stamp('Start of Temp Table Inserts');
5325 IF (p_user IS NOT NULL) THEN
5326 IF (p_time_or_period = 'T') THEN
5327 IF (p_operator = '<=') THEN
5328 v_locator := 2;
5329 get_user_trees_by_atmost(p_user, p_status, TO_NUMBER(p_days));
5330 ELSE
5331 v_locator := 3;
5332 get_user_trees_by_atleast(p_user, p_status, TO_NUMBER(p_days));
5333 END IF;
5334 ELSE
5335 v_locator := 4;
5336 get_user_trees_by_period(p_user, p_status, v_start, v_end) ;
5337 END IF;
5338 print_time_stamp('End of Temp Table Inserts');
5339 print_legend_link;
5340
5341 -- print hierarchy for the user
5342 htp.p('<TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
5343 htp.tableRowOpen;
5344 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5345 '<FONT CLASS="tableSubHeader">'|| g_hierarchy ||'</FONT></TD>');
5346 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5347 '<FONT CLASS="tableSubHeader">'|| g_ctxt_type ||'</FONT></TD>');
5348 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5349 '<FONT CLASS="tableSubHeader">'|| g_ctxt_name ||'</FONT></TD>');
5350 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5351 '<FONT CLASS="tableSubHeader">'|| g_status ||'</FONT></TD>');
5352 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5353 '<FONT CLASS="tableSubHeader">'|| g_start ||'</FONT></TD>');
5354 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5355 '<FONT CLASS="tableSubHeader">'|| g_end ||'</FONT></TD>');
5356 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5357 '<FONT CLASS="tableSubHeader">'|| g_duration ||'</FONT></TD>');
5358 htp.tableRowClose;
5359 v_locator := 5;
5360 FOR one_node IN hierarchies_cursor(p_user) LOOP
5361 --
5362 -- Keep track of all the upper groups to be able to display
5363 -- them in a flat directory structure like
5364 -- "\Common Applications\System Administration\"
5365 --
5366 v_upper_group_names(one_node.level) := TRIM(one_node.hierarchy);
5367 htp.tableRowOpen;
5368 IF (one_node.parent_node_id IS NULL) THEN
5369 htp.p('<TR><TD ALIGN="LEFT" COLSPAN="7" BGCOLOR="#666666" ' ||
5370 'NOWRAP><i><FONT COLOR="#FFFFFF">' ||
5371 one_node.hierarchy || '</FONT></i></TD>');
5372 ELSIF (one_node.node_type = 'G') THEN
5373 htp.tableData('<i>'||lpad_nbsp(one_node.level)|| one_node.hierarchy ||
5374 '</i>', 'LEFT', '', 'NOWRAP');
5375 htp.tableData('<i>'||one_node.context_type_name||'</i>');
5376 htp.tableData('<i>'||one_node.context_name||'</i>');
5377 htp.tableData('<i>'||one_node.status_code_name||'</i>', 'LEFT', '','NOWRAP');
5378 htp.tableData('<i>'||one_node.start_date||'</i>');
5379 htp.tableData('<i>'||one_node.end_date||'</i>');
5380 htp.tableData('<i>'||one_node.time_elapsed||'</i>', 'RIGHT');
5381
5382 ELSIF (one_node.node_type = 'P') THEN
5383
5384 -- To be used by the task HREF
5385 v_process_groups := get_parent_structure(v_upper_group_names, one_node.level, '\\');
5386 v_ctx_type_name := one_node.context_type_name;
5387
5388 htp.tableData('<b>'||lpad_nbsp(one_node.level)|| one_node.hierarchy ||
5389 '</b>', 'LEFT', '', 'NOWRAP');
5390 htp.tableData(one_node.context_type_name);
5391 htp.tableData(one_node.context_name);
5392 htp.tableData(one_node.status_code_name, 'LEFT', '','NOWRAP');
5393 htp.tableData(one_node.start_date);
5394 htp.tableData(one_node.end_date);
5395 htp.tableData(one_node.time_elapsed, 'RIGHT');
5396 IF (one_node.comments IS NOT NULL) THEN
5397 htp.tableRowClose;
5398 htp.tableRowOpen;
5399 htp.tableData('<b>' || lpad_nbsp(one_node.level)||'('|| g_comments ||':'||
5400 one_node.comments ||')</b>', 'LEFT', '', 'NOWRAP');
5401 END IF;
5402 ELSE
5403 get_task_type_key(one_node.node_id, v_item_type, v_item_key);
5404 v_href := '<A HREF=javascript:void(OpenURL("azw_report.task_details?' ||
5405 'p_process_groups=' || url_encode(v_process_groups) ||
5406 '&p_ctx_type=' || url_encode(v_ctx_type_name) ||
5407 '&p_status=' || url_encode(one_node.status_code_name) ||
5408 '&p_start=' || url_encode(one_node.start_date) ||
5409 '&p_end=' || url_encode(one_node.end_date)||
5410 '&p_time_elapsed=' || url_encode(one_node.time_elapsed) ||
5411 '&p_item_type=' || v_item_type ||
5412 '&p_item_key=' || v_item_key || '")) onMouseOver=window.status="'
5413 || g_task_details || '";return true>';
5414 htp.p('<td bgcolor=#DDDDDD> </td>');
5415 htp.p('<td bgcolor=#DDDDDD> </td>');
5416 htp.p('<td bgcolor=#DDDDDD> </td>');
5417 htp.p('<TD ALIGN="LEFT" BGCOLOR="#DDDDDD" NOWRAP>'|| v_href || one_node.status_code_name || '</A></TD>');
5418 htp.p('<TD ALIGN="LEFT" BGCOLOR="#DDDDDD" NOWRAP>' || one_node.start_date || '</TD>');
5419 htp.p('<TD ALIGN="LEFT" BGCOLOR="#DDDDDD" NOWRAP>' || one_node.end_date || ' </TD>');
5420 htp.p('<TD ALIGN="RIGHT" BGCOLOR="#DDDDDD" NOWRAP>' || one_node.time_elapsed || '</TD>');
5421 END IF;
5422 htp.tableRowClose;
5423 /*
5424 dbms_output.put_line('Name=' || one_node.hierarchy);
5425 dbms_output.put_line('Type=' || one_node.node_type);
5426 dbms_output.put_line('start=' || one_node.start_date);
5427 dbms_output.put_line('end=' || one_node.end_date);
5428 */
5429 END LOOP;
5430 htp.tableClose;
5431 ELSE
5432 print_time_stamp('Start of Temp Table Inserts');
5433 IF (p_time_or_period = 'T') THEN
5434 IF (p_operator = '<=') THEN
5435 FOR one_user IN valid_users_cursor LOOP
5436 get_user_trees_by_atmost(one_user.user_name, p_status,
5437 TO_NUMBER(p_days));
5438 END LOOP;
5439 ELSE
5440 FOR one_user IN valid_users_cursor LOOP
5441 get_user_trees_by_atleast(one_user.user_name, p_status,
5442 TO_NUMBER(p_days));
5443 END LOOP;
5444 END IF;
5445 ELSE
5446 FOR one_user IN valid_users_cursor LOOP
5447 get_user_trees_by_period(one_user.user_name, p_status, v_start,v_end);
5448 END LOOP;
5449 END IF;
5450 print_time_stamp('End of Temp Table Inserts');
5451
5452 print_user_report_summary;
5453
5454 print_legend_link;
5455
5456 htp.p('<table border=0 cellpadding=1 cellspacing=1>');
5457 -- print hierarchy for each user
5458 htp.tableRowOpen;
5459 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5460 '<FONT CLASS="tableSubHeader">'|| g_hierarchy ||'</FONT></TD>');
5461 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5462 '<FONT CLASS="tableSubHeader">'|| g_ctxt_type ||'</FONT></TD>');
5463 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5464 '<FONT CLASS="tableSubHeader">'|| g_ctxt_name ||'</FONT></TD>');
5465 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5466 '<FONT CLASS="tableSubHeader">'|| g_status ||'</FONT></TD>');
5467 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5468 '<FONT CLASS="tableSubHeader">'|| g_start ||'</FONT></TD>');
5469 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5470 '<FONT CLASS="tableSubHeader">'|| g_end ||'</FONT></TD>');
5471 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5472 '<FONT CLASS="tableSubHeader">'|| g_duration ||'</FONT></TD>');
5473 htp.tableRowClose;
5474
5475 i := 0;
5476 v_locator := 6;
5477 FOR one IN valid_users_cursor LOOP
5478 v_locator := 7;
5479 SELECT count(*)
5480 INTO v_count
5481 FROM az_monitor_reports
5482 WHERE assigned_user = one.user_name;
5483 v_locator := 8;
5484 IF (v_count <> 0) THEN
5485 IF (i > 0) THEN
5486 print_back_to_top(7);
5487 END IF;
5488 htp.p('<TR><TD ALIGN="LEFT" COLSPAN="7" BGCOLOR="#336699">' ||
5489 '<FONT CLASS="tableSubHeader"><A NAME="'|| one.user_name ||'">'||
5490 g_user || ': ' || one.user_name || ' (' || one.display_name || ')'
5491 || '</A></FONT></TD></TR>');
5492 FOR one_node IN hierarchies_cursor(one.user_name) LOOP
5493 --
5494 -- Keep track of all the upper groups to be able to display
5495 -- them in a flat directory structure like
5496 -- "\Common Applications\System Administration\"
5497 --
5498 v_upper_group_names(one_node.level) := TRIM(one_node.hierarchy);
5499 htp.tableRowOpen;
5500 IF (one_node.parent_node_id IS NULL) THEN
5501 htp.p('<TR><TD ALIGN="LEFT" COLSPAN="7" BGCOLOR="#666666" ' ||
5502 'NOWRAP><i><FONT COLOR="#FFFFFF">' ||
5503 one_node.hierarchy || '</FONT></i></TD>');
5504 ELSIF (one_node.node_type = 'G') THEN
5505 htp.tableData('<i>'||lpad_nbsp(one_node.level) || one_node.hierarchy
5506 ||'</i>', 'LEFT', '', 'NOWRAP');
5507 htp.tableData('<i>'||one_node.context_type_name||'</i>');
5508 htp.tableData('<i>'||one_node.context_name||'</i>');
5509 htp.tableData('<i>'||one_node.status_code_name||'</i>');
5510 htp.tableData('<i>'||one_node.start_date||'</i>');
5511 htp.tableData('<i>'||one_node.end_date||'</i>');
5512 htp.tableData('<i>'||one_node.time_elapsed||'</i>', 'RIGHT');
5513 ELSIF (one_node.node_type = 'P') THEN
5514 -- To be used by the task HREF
5515 v_process_groups := get_parent_structure(
5516 v_upper_group_names,
5517 one_node.level, '\\');
5518 v_ctx_type_name := one_node.context_type_name;
5519 htp.tableData('<b>'||lpad_nbsp(one_node.level) ||
5520 one_node.hierarchy
5521 ||'</b>', 'LEFT', '', 'NOWRAP');
5522 htp.tableData(one_node.context_type_name);
5523 htp.tableData(one_node.context_name);
5524 htp.tableData(one_node.status_code_name);
5525 htp.tableData(one_node.start_date);
5526 htp.tableData(one_node.end_date);
5527 htp.tableData(one_node.time_elapsed, 'RIGHT');
5528 IF (one_node.comments IS NOT NULL) THEN
5529 htp.tableRowClose;
5530 htp.tableRowOpen;
5531 htp.tableData('<b>' || lpad_nbsp(one_node.level)|| '('|| g_comments ||':'||
5532 one_node.comments || ')</b>', 'LEFT', '', 'NOWRAP');
5533 END IF;
5534 ELSE
5535 get_task_type_key(one_node.node_id, v_item_type, v_item_key);
5536 v_href := '<A HREF=javascript:void(OpenURL("' ||
5537 'azw_report.task_details?' ||
5538 'p_process_groups=' || url_encode(v_process_groups) ||
5539 '&p_ctx_type=' || url_encode(v_ctx_type_name) ||
5540 '&p_status=' || url_encode(one_node.status_code_name) ||
5541 '&p_start=' || url_encode(one_node.start_date) ||
5542 '&p_end=' || url_encode(one_node.end_date)||
5543 '&p_time_elapsed=' || url_encode(one_node.time_elapsed) ||
5544 '&p_item_type=' || v_item_type ||
5545 '&p_item_key=' || v_item_key || '")) onMouseOver=window.status="'
5546 || g_task_details || '";return true>';
5547 htp.p('<td bgcolor=#DDDDDD> </td>');
5548 htp.p('<td bgcolor=#DDDDDD> </td>');
5549 htp.p('<td bgcolor=#DDDDDD> </td>');
5550 htp.p('<TD ALIGN="LEFT" BGCOLOR="#DDDDDD" NOWRAP>'|| v_href || one_node.status_code_name || '</A></TD>');
5551 htp.p('<TD BGCOLOR="#DDDDDD" NOWRAP>' || one_node.start_date || '</A></TD>');
5552 htp.p('<TD BGCOLOR="#DDDDDD" NOWRAP>' || one_node.end_date || '</A> </TD>');
5553 htp.p('<TD ALIGN="RIGHT" BGCOLOR="#DDDDDD" NOWRAP>' || one_node.time_elapsed || '</A></TD>');
5554 END IF;
5555 htp.tableRowClose;
5556 END LOOP;
5557 i := i + 1;
5558 END IF;
5559 END LOOP;
5560 print_back_to_top(7);
5561 htp.tableClose;
5562 END IF;
5563
5564 -- print Table closing tag
5565 -- htp.tableClose;
5566
5567 -- print report legend
5568 print_legend (TRUE);
5569
5570 -- print related report links
5571 print_related_reports('AZW_RPT_ISR');
5572 print_time_stamp('End of Report');
5573 COMMIT;
5574
5575 EXCEPTION
5576 WHEN NO_DATA_FOUND THEN
5577 -- print Table closing tag
5578 htp.tableClose;
5579 print_related_reports('AZW_RPT_ISR');
5580 WHEN application_exception THEN
5581 RAISE;
5582 WHEN OTHERS THEN
5583 raise_error_msg (SQLCODE, SQLERRM,
5584 'USER_REPORT', 'v_locator := ' || v_locator);
5585 END user_report;
5586
5587 /*
5588 **
5589 ** PRIINT_USER_REPORT_SUMMARY
5590 ** ==========================
5591 **
5592 ** Private Procedure.
5593 ** It displays the report summary which contains the total number
5594 ** of tasks for each user specified in the temp table az_monitor_reports.
5595 **
5596 */
5597 PROCEDURE print_user_report_summary IS
5598
5599 CURSOR user_task_count IS
5600 SELECT ASSIGNED_USER, COUNT(NODE_TYPE) NUM_TASKS
5601 FROM AZ_MONITOR_REPORTS
5602 WHERE NODE_TYPE='T'
5603 GROUP BY ASSIGNED_USER;
5604
5605 BEGIN
5606 -- Print the Table header
5607 htp.p('<TABLE ALIGN="center" BORDER="1" CELLPADDING="1" CELLSPACING="1">');
5608 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="2">' ||
5609 '<FONT CLASS="tableHeader"><A NAME="TOP">' || g_summary
5610 ||'</A></FONT></TD></TR>');
5611 htp.p('<TR>');
5612 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699" ><FONT CLASS="tableSubHeader">'||
5613 g_user ||'</FONT></TD>');
5614 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699" ><FONT CLASS="tableSubHeader">'||
5615 g_num_tasks ||'</FONT></TD>');
5616 htp.p('</TR>');
5617
5618
5619 FOR current_rec IN user_task_count LOOP
5620 htp.p('<TR><TD ALIGN="LEFT"><A HREF="#'|| current_rec.assigned_user ||'">'||
5621 current_rec.assigned_user ||'</A></TD>');
5622 htp.p('<TD ALIGN="CENTER">'|| current_rec.num_tasks ||'</TD></TR>');
5623 END LOOP;
5624 htp.p('</TABLE><BR>');
5625 return;
5626
5627 EXCEPTION
5628 WHEN application_exception THEN
5629 RAISE;
5630 WHEN OTHERS THEN
5631 raise_error_msg (SQLCODE, SQLERRM,
5632 'PRIINT_USER_REPORT_SUMMARY', '');
5633 END print_user_report_summary;
5634
5635 /*
5636 ** RAISE_ERROR_MSG
5637 ** ===============
5638 **
5639 ** It generates a generic error message for all the reports.
5640 ** It is called from all exception handling blocks.
5641 **
5642 */
5643 PROCEDURE raise_error_msg (
5644 ErrCode IN NUMBER,
5645 ErrMsg IN VARCHAR2,
5646 ProcedureName IN VARCHAR2,
5647 Statement IN VARCHAR2) IS
5648
5649 v_message VARCHAR2(2048);
5650 BEGIN
5651 v_message := SUBSTR(fnd_message.get_string('AZ',
5652 'AZW_PLSQL_EXCEPTION'), 1, 2048);
5653
5654 v_message := REPLACE(v_message, '&AZW_ERROR_CODE', ErrCode);
5655 v_message := REPLACE(v_message, '&AZW_ERROR_MESG', ErrMsg);
5656 v_message := REPLACE(v_message, '&AZW_ERROR_PROC',
5657 'AZW_REPORT.' || ProcedureName);
5658 IF (Statement IS NOT NULL) THEN
5659 v_message := REPLACE(v_message, '&AZW_ERROR_STMT', Statement);
5660 ELSE
5661 v_message := REPLACE(v_message, '&AZW_ERROR_STMT', 'v_locator := 0');
5662 END IF;
5663 --
5664 -- ErrMsg must not be more than 2048 bytes
5665 --
5666 raise_application_error(-20001, v_message);
5667
5668 END raise_error_msg;
5669
5670 /*
5671 **
5672 ** PRINT_TIME_STAMP
5673 ** ================
5674 **
5675 ** It displays the system time with the associated message
5676 ** as an HTML comment, it would be visible only in the HTML
5677 ** source.
5678 ** It is used for performance issues and to know how long
5679 ** a certain process is taking by calling this function
5680 ** before and after the process.
5681 **
5682 */
5683 PROCEDURE print_time_stamp (v_string VARCHAR2) IS
5684
5685 BEGIN
5686 -- htp.p('<!-- '|| v_string ||' =['|| TO_CHAR(SYSDATE, 'HH24:MI:SS') ||'] -->');
5687 NULL;
5688 END print_time_stamp;
5689
5690 /*
5691 **
5692 ** PRINT_LEGEND_LINK
5693 ** =================
5694 **
5695 ** It displays the hyperlink to the report legend table.
5696 ** at the bottom of the report.
5697 **
5698 */
5699 PROCEDURE print_legend_link IS
5700
5701 BEGIN
5702 htp.p('<TABLE ALIGN="CENTER" BORDER="0" CELLPADDING="1" CELLSPACING="1" WIDTH="100%">');
5703 htp.p('<TR><TD ALIGN="CENTER"><A HREF="#LEGEND"><FONT SIZE="-1">' ||
5704 g_report_legend || '</FONT></A></TD></TR>');
5705 htp.p('</TABLE>');
5706 htp.p('<BR>');
5707 END print_legend_link;
5708
5709 /*
5710 **
5711 ** PRINT_LEGEND
5712 ** ============
5713 **
5714 ** It displays the report legend table.
5715 **
5716 */
5717 PROCEDURE print_legend (p_status BOOLEAN DEFAULT FALSE) IS
5718
5719 BEGIN
5720 htp.p('<BR>');
5721 htp.p('<TABLE ALIGN="CENTER" BORDER="1" CELLPADDING="1" CELLSPACING="1">');
5722 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5723 '<A NAME="LEGEND"><FONT CLASS="tableHeader">' || g_report_legend ||'</A></FONT></TD></TR>');
5724 htp.p('<TR><TD BGCOLOR="#666666"><i><FONT COLOR="#FFFFFF">'|| g_group_legend ||'</FONT></i></TD></TR>');
5725 htp.p('<TR><TD><i>'|| g_subgrp_legend ||'</i></TD></TR>');
5726 htp.p('<TR><TD><b>'|| g_process_legend ||'</b></TD></TR>');
5727 IF (p_status) THEN
5728 htp.p('<TR><TD BGCOLOR="#DDDDDD">'|| g_task_legend ||'</TD></TR>');
5729 END IF;
5730 htp.tableClose;
5731 END print_legend;
5732
5733 /*
5734 **
5735 ** TASK_DETAILS
5736 ** ============
5737 **
5738 ** It displays the step details for the specified task.
5739 ** The status reports should have a hyper link for each
5740 ** task that calls this procedure.
5741 **
5742 */
5743 PROCEDURE task_details (
5744 p_process_groups IN VARCHAR2,
5745 p_ctx_type IN VARCHAR2,
5746 p_status IN VARCHAR2,
5747 p_start IN VARCHAR2,
5748 p_end IN VARCHAR2,
5749 p_time_elapsed IN VARCHAR2,
5750 p_item_type IN VARCHAR2,
5751 p_item_key IN VARCHAR2) IS
5752
5753 v_process_name wf_items.root_activity%TYPE;
5754
5755 BEGIN
5756 g_help_target := get_report_procedure('AZW_RPT_ISRR');
5757 IF (g_web_agent IS NULL) THEN
5758 get_web_agent;
5759 get_translated_labels;
5760 END IF;
5761 htp.p('<HTML>');
5762 htp.title(g_task_details);
5763 print_html_style;
5764 htp.p('<BODY>');
5765
5766 -- Print the Table header
5767 htp.p('<TABLE ALIGN="center" BORDER="0" CELLPADDING="1" CELLSPACING="1">');
5768 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="6">' ||
5769 '<FONT CLASS="tableHeader">' || g_task_params ||'</FONT></TD></TR>');
5770 htp.tableRowOpen;
5771 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5772 '<FONT CLASS="tableSubHeader">'|| g_hierarchy ||'</FONT></TD>');
5773 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5774 '<FONT CLASS="tableSubHeader">'|| g_ctxt_type ||'</FONT></TD>');
5775 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5776 '<FONT CLASS="tableSubHeader">'|| g_status ||'</FONT></TD>');
5777 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5778 '<FONT CLASS="tableSubHeader">'|| g_start ||'</FONT></TD>');
5779 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5780 '<FONT CLASS="tableSubHeader">'|| g_end ||'</FONT></TD>');
5781 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699">' ||
5782 '<FONT CLASS="tableSubHeader">'|| g_duration ||'</FONT></TD>');
5783 htp.tableRowClose;
5784 htp.tableRowOpen;
5785
5786 --
5787 --
5788 --
5789 g_curr_process_level := 1;
5790 g_upper_process_names(1) := SUBSTR(p_process_groups, INSTR(p_process_groups, '\', -1) + 1);
5791 htp.p('<TD>' || p_process_groups ||'</TD>');
5792 htp.p('<TD>' || p_ctx_type ||'</TD>');
5793 htp.p('<TD>' || p_status ||'</TD>');
5794 htp.p('<TD>' || p_start ||'</TD>');
5795 htp.p('<TD>' || p_end ||'</TD>');
5796 htp.p('<TD>' || p_time_elapsed ||'</TD>');
5797 htp.tableRowClose;
5798 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="6">' ||
5799 '<FONT CLASS="tableHeader">' || g_task_details ||'</FONT></TD></TR>');
5800 htp.p('<TR>');
5801 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699" ><FONT CLASS="tableSubHeader">'||
5802 g_hierarchy ||'</FONT></TD>');
5803 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699" ><FONT CLASS="tableSubHeader">'||
5804 g_step_name ||'</FONT></TD>');
5805 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699" ><FONT CLASS="tableSubHeader">'||
5806 g_step_response ||'</FONT></TD>');
5807 htp.p('<TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="3"><FONT CLASS="tableSubHeader">'||
5808 g_comments ||'</FONT></TD>');
5809 htp.p('</TR>');
5810
5811 --
5812 -- Get the main process name
5813 --
5814 SELECT root_activity INTO v_process_name
5815 FROM wf_items
5816 WHERE item_type = p_item_type
5817 AND item_key = p_item_key;
5818
5819 --
5820 -- Update the global array of the upper process names
5821 --
5822 g_curr_process_level := 1;
5823 g_upper_process_names(1) := SUBSTR(p_process_groups, INSTR(p_process_groups, '\', -1) + 1);
5824 htp.p('<TR BGCOLOR="#666666"><TD><FONT COLOR="#FFFFFF">' ||
5825 g_upper_process_names(g_curr_process_level)
5826 || '</FONT></TD><TD COLSPAN="5"> </TD></TR>');
5827
5828 --
5829 -- Call the print task steps procedure by level equal to 1
5830 -- which mean call it by the main process name.
5831 --
5832 print_task_steps(p_item_type, p_item_key, v_process_name, 1);
5833
5834 htp.p('</TABLE>');
5835 htp.p('</BODY></HTML>');
5836 EXCEPTION
5837 WHEN application_exception THEN
5838 RAISE;
5839 WHEN OTHERS THEN
5840 raise_error_msg (SQLCODE, SQLERRM,
5841 'TASK_DETAILS', '');
5842 END task_details;
5843
5844 /*
5845 **
5846 ** PRINT_TASK_STEPS
5847 ** ================
5848 **
5849 ** Private procedure.
5850 ** It prints the task steps that the user went through for
5851 ** the specified process name. This function is recuerssive.
5852 ** It prints all the notices and functions except start
5853 ** and end. When a sub-process is encountered it calls itself
5854 ** and so on. This function will keep track of the process names
5855 ** that it passed though in a temp array to display the process
5856 ** hierarchy when a sub-process is encountered. To show the
5857 ** subprocess the same way it is shown in the Product Planning
5858 ** report.
5859 **
5860 */
5861
5862 PROCEDURE print_task_steps(
5863 p_item_type IN VARCHAR2,
5864 p_item_key IN VARCHAR2,
5865 p_process_name IN VARCHAR2,
5866 p_process_level IN NUMBER) IS
5867
5868 CURSOR process_steps IS
5869 SELECT wiasv.activity_type_code type
5870 ,wiasv.activity_name name
5871 ,wiasv.activity_display_name display_name
5872 ,wiasv.activity_result_display_name result_name
5873 ,wiasv.activity_begin_date begin_date
5874 ,wiasv.activity_end_date end_date
5875 ,wiasv.notification_id
5876 FROM wf_item_activity_statuses_v wiasv,
5877 wf_process_activities wpa
5878 WHERE wiasv.item_type = p_item_type
5879 AND wiasv.item_key = p_item_key
5880 AND wiasv.item_type = wpa.process_item_type
5881 AND wpa.process_name = p_process_name
5882 AND wpa.instance_id = wiasv.activity_id
5883 ORDER BY 5, 6;
5884 v_user_comments wf_notifications.user_comment%TYPE := NULL;
5885 BEGIN
5886
5887 --
5888 -- Start looping through all the steps for the current specified process
5889 --
5890 FOR step IN process_steps LOOP
5891 --
5892 -- Get the user comment if there is a notification
5893 --
5894 IF step.notification_id IS NOT NULL THEN
5895 SELECT user_comment INTO v_user_comments
5896 FROM wf_notifications
5897 WHERE notification_id = step.notification_id;
5898 END IF;
5899
5900 IF step.type = 'PROCESS' THEN
5901 --
5902 -- fill the global array with the process names
5903 -- to use it in displaying the process hierarchy
5904 -- for all sub-processes.
5905 --
5906 g_curr_process_level := p_process_level + 1;
5907 g_upper_process_names(g_curr_process_level) := step.display_name;
5908 htp.p('<TR BGCOLOR="#DDDDDD"><TD>' ||
5909 get_parent_structure(g_upper_process_names, g_curr_process_level)
5910 || '</TD><TD COLSPAN="5"> </TD></TR>');
5911 print_task_steps(p_item_type, p_item_key, step.name, g_curr_process_level);
5912 ELSIF step.type = 'FUNCTION' AND step.name = 'START' THEN
5913 NULL;
5914 ELSIF step.type = 'FUNCTION' AND step.name = 'END' THEN
5915 IF p_process_level > 1 THEN
5916 htp.p('<TR><TD> </TD><TD COLSPAN=5 BGCOLOR="#DDDDDD"> <TD></TR>');
5917 END IF;
5918 g_curr_process_level := g_curr_process_level - 1;
5919 ELSE
5920 htp.p('<TR><TD> </TD>');
5921 htp.p('<TD>'||step.display_name ||'</TD>');
5922 htp.p('<TD>'||step.result_name ||'</TD>');
5923 htp.p('<TD>'||v_user_comments ||'</TD></TR>');
5924 END IF;
5925 END LOOP;
5926
5927 EXCEPTION
5928 WHEN application_exception THEN
5929 RAISE;
5930 WHEN OTHERS THEN
5931 raise_error_msg (SQLCODE, SQLERRM, 'PRINT_TASK_STEPS', '');
5932 END print_task_steps;
5933
5934 /*
5935 **
5936 ** GET_TASK_TYPE_KEY
5937 ** =================
5938 **
5939 ** The node id specified in the az_monitor_reports temp
5940 ** table is composed of multiple fields. two of them are
5941 ** the item_type and item_key fields.
5942 ** this procedure extracts and returns these two fields
5943 ** from the input node_id.
5944 ** It is called from the status and user report, to be
5945 ** able to build the URL that display the step details
5946 ** for each task.
5947 **
5948 */
5949 PROCEDURE get_task_type_key (p_node_id IN VARCHAR2,
5950 p_item_type OUT VARCHAR2,
5951 p_item_key OUT VARCHAR2) IS
5952 BEGIN
5953
5954 p_item_type := TRIM(SUBSTR(p_node_id, 1, (INSTR(p_node_id, '.', 1) - 1)));
5955 p_item_key := TRIM(SUBSTR(p_node_id, (INSTR(p_node_id, '.', -1) + 1)));
5956 --
5957 -- Remove leading zeros
5958 --
5959 WHILE (INSTR(p_item_key, '0', 1) = 1) LOOP
5960 p_item_key := SUBSTR(p_item_key, 2);
5961 END LOOP;
5962 EXCEPTION
5963 WHEN application_exception THEN
5964 RAISE;
5965 WHEN OTHERS THEN
5966 raise_error_msg (SQLCODE, SQLERRM,
5967 'GET_TASK_TYPE_KEY', '');
5968 END get_task_type_key;
5969
5970 /*
5971 **
5972 ** SHOW_ALL_STEPS
5973 ** ==============
5974 **
5975 ** It displays the step details for all the processes
5976 ** available for the specified products.
5977 ** It will open a new window showing the steps that he
5978 ** user needs to go through in order to implement the
5979 ** selected products.
5980 **
5981 */
5982 PROCEDURE show_all_steps(p_selected_products IN VARCHAR2) IS
5983
5984
5985 v_current_id az_groups.application_id%TYPE;
5986 i INTEGER;
5987
5988 CURSOR hierarchies_cursor IS
5989 SELECT display_name hierarchy,
5990 node_type,
5991 description,
5992 parent_node_id,
5993 node_id,
5994 context_type_name,
5995 LEVEL
5996 FROM az_planning_reports
5997 START WITH parent_node_id IS NULL
5998 CONNECT BY PRIOR node_id = parent_node_id
5999 AND PRIOR phase = phase;
6000
6001 v_item_type wf_process_activities.PROCESS_ITEM_TYPE%TYPE;
6002 v_process_name wf_process_activities.PROCESS_NAME%TYPE;
6003
6004 v_process_groups VARCHAR2(4000);
6005 v_last_printed VARCHAR2(4000);
6006 v_upper_group_names HierarchyLevels;
6007 v_app_id NUMBER;
6008 v_cnt BINARY_INTEGER;
6009 v_ids id_tbl_t;
6010 v_count PLS_INTEGER;
6011 v_locator PLS_INTEGER := 0;
6012
6013 BEGIN
6014
6015 IF (g_web_agent IS NULL) THEN
6016 get_web_agent;
6017 get_translated_labels;
6018 END IF;
6019 v_locator := 1;
6020
6021 htp.htmlOpen;
6022 htp.headOpen;
6023 htp.title(g_step_details);
6024 htp.headClose;
6025
6026
6027 --
6028 -- get the product list string into the id table
6029 --
6030 v_cnt := 1;
6031 v_locator := 2;
6032 v_app_id := azw_proc.parse_application_ids(p_selected_products, v_cnt);
6033 WHILE (v_app_id > -1) LOOP
6034 v_ids(v_cnt) := v_app_id;
6035 v_cnt := v_cnt + 1;
6036 v_app_id := azw_proc.parse_application_ids(p_selected_products, v_cnt);
6037 END LOOP;
6038
6039 print_html_style;
6040
6041 print_selected_prods_table(v_ids);
6042
6043 print_step_details_header;
6044
6045 v_locator := 3;
6046 FOR i IN 1..v_ids.COUNT LOOP
6047 get_product_processes(v_ids(i));
6048 END LOOP;
6049
6050
6051 v_locator := 4;
6052 FOR one_node IN hierarchies_cursor LOOP
6053 --
6054 -- Keep track of all the upper groups to be able to display
6055 -- them in a flat directory structure like
6056 -- "\Common Applications\System Administration\"
6057 --
6058 v_upper_group_names(one_node.level) := TRIM(one_node.hierarchy);
6059
6060 IF (one_node.node_type = 'P') THEN
6061 v_locator := 5;
6062 v_process_groups := get_parent_structure(v_upper_group_names, one_node.level - 1);
6063 IF v_process_groups = v_last_printed THEN
6064 v_process_groups := ' ';
6065 ELSE
6066 v_last_printed := v_process_groups;
6067 END IF;
6068 get_process_type_name (one_node.node_id, v_item_type, v_process_name);
6069 v_locator := 6;
6070 display_process_steps(p_selected_products,
6071 v_item_type,
6072 v_process_name,
6073 one_node.context_type_name,
6074 v_process_groups,
6075 'YES');
6076 v_locator := 8;
6077 END IF;
6078 END LOOP;
6079 v_locator := 7;
6080 htp.p('</TABLE>');
6081 htp.p('</BODY>');
6082 htp.htmlClose;
6083 COMMIT;
6084
6085 EXCEPTION
6086 WHEN application_exception THEN
6087 RAISE;
6088 WHEN OTHERS THEN
6089 raise_error_msg (SQLCODE, SQLERRM,
6090 'SHOW_ALL_STEPS', 'v_locator := ' || v_locator);
6091 END show_all_steps;
6092
6093 /*
6094 **
6095 ** PRINT_STEP_DETAILS_HEADER
6096 ** =========================
6097 **
6098 ** This procedure displays the report header an table header
6099 ** for the step details reports.
6100 ** Called from show_all_steps and from show_process_steps.
6101 **
6102 */
6103 PROCEDURE print_step_details_header (p_display_msg IN VARCHAR2 DEFAULT NULL) IS
6104 v_colspan PLS_INTEGER;
6105
6106 BEGIN
6107 IF (p_display_msg = 'YES') THEN
6108 htp.htmlOpen;
6109 htp.headOpen;
6110 htp.title(g_step_details);
6111 htp.headClose;
6112 v_colspan := 4;
6113 ELSE
6114 v_colspan := 3;
6115 END IF;
6116
6117 print_html_style;
6118 htp.p('<body bgcolor=#FFFFFF>');
6119 htp.p('<TABLE BORDER="0" CELLPADDING="1" CELLSPACING="1">');
6120 htp.p('<TR><TD ALIGN="CENTER" BGCOLOR="#336699" COLSPAN="'|| v_colspan ||'">' ||
6121 '<FONT CLASS="tableHeader">' || g_step_details ||'</FONT></TD></TR>');
6122 htp.p('<TR><TH ALIGN="LEFT" BGCOLOR="#336699" '||
6123 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_hierarchy ||'</FONT></TH>');
6124 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
6125 'NOWRAP><FONT CLASS="tableSubHeader">' || g_process || '</FONT></TH>');
6126 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
6127 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_steps ||'</FONT></TH>');
6128 IF (p_display_msg = 'YES') THEN
6129 htp.p('<TH ALIGN="LEFT" BGCOLOR="#336699" '||
6130 'NOWRAP><FONT CLASS="tableSubHeader">'|| g_step_msg ||'</FONT></TH>');
6131 END IF;
6132 htp.p('</TR>');
6133 EXCEPTION
6134 WHEN application_exception THEN
6135 RAISE;
6136 WHEN OTHERS THEN
6137 raise_error_msg (SQLCODE, SQLERRM,
6138 'PRINT_STEP_DETAILS_HEADER', '');
6139 END print_step_details_header;
6140
6141 /*
6142 **
6143 ** DISPLAY_PROCESS_STEPS
6144 ** =====================
6145 **
6146 ** Public Procedure.
6147 ** It displays all the available steps for the specified
6148 ** process. It displays the activities in the same order
6149 ** of the work flow activities if the selected products
6150 ** are installed.
6151 ** It can be called as an an external procedure that
6152 ** displays the available steps for the specified process.
6153 ** It can be called from show_all_steps.
6154 ** And it can be called to display a subprocess, from
6155 ** print_activity.
6156 **
6157 */
6158 PROCEDURE display_process_steps (
6159 p_selected_products IN VARCHAR2,
6160 p_item_type IN VARCHAR2,
6161 p_process_name IN VARCHAR2,
6162 p_context_type_name IN VARCHAR2,
6163 p_process_groups IN VARCHAR2,
6164 p_new_call IN VARCHAR2 DEFAULT NULL,
6165 p_external_call IN VARCHAR2 DEFAULT NULL) IS
6166
6167 l_dname wf_activities_vl.display_name%TYPE;
6168 proc_version NUMBER;
6169
6170 CURSOR start_act_cur IS
6171 select instance_id, activity_name, start_end
6172 FROM wf_process_activities wpa
6173 where wpa.process_item_type = p_item_type
6174 and wpa.process_name = p_process_name
6175 and wpa.start_end = 'START'
6176 and wpa.process_version = proc_version;
6177
6178 v_locator PLS_INTEGER := 0;
6179
6180 BEGIN
6181
6182 IF (p_external_call = 'YES') AND (g_FirstTime) THEN
6183 IF (g_web_agent IS NULL) THEN
6184 get_web_agent;
6185 get_translated_labels;
6186 END IF;
6187 v_locator := 1;
6188 print_step_details_header(p_external_call);
6189 g_FirstTime := FALSE;
6190 END IF;
6191
6192 v_locator := 2;
6193
6194 select display_name, version
6195 into l_dname, proc_version
6196 from wf_activities_vl wav
6197 where wav.item_type = p_item_type
6198 and wav.name = p_process_name
6199 and wav.end_date IS NULL;
6200
6201 v_locator := 3;
6202 IF (p_new_call = 'YES') THEN
6203 g_curr_process_level := 1;
6204 g_upper_process_names(1) := l_dname;
6205 v_locator := 4;
6206 --
6207 -- Delete the records found in the
6208 -- g_instance_ids table array.
6209 --
6210 g_instance_ids.DELETE;
6211 g_inst_count := 0;
6212 ELSE
6213 g_curr_process_level := g_curr_process_level + 1;
6214 g_upper_process_names(g_curr_process_level) := l_dname;
6215 END IF;
6216
6217 IF (g_curr_process_level > 1) THEN
6218 htp.p('<TR><TD> </TD><TD BGCOLOR="#DDDDDD">'||
6219 get_parent_structure(g_upper_process_names, g_curr_process_level) ||
6220 '</TD><TD BGCOLOR="#DDDDDD"> </TD>');
6221 g_prev_act_name := NULL;
6222 ELSE
6223 htp.p('<TR><TD BGCOLOR="#666666"><FONT COLOR="#FFFFFF">' || p_process_groups ||
6224 '</FONT></TD><TD BGCOLOR="#666666"><FONT COLOR="#FFFFFF">'||
6225 get_parent_structure(g_upper_process_names, g_curr_process_level));
6226 IF (p_context_type_name IS NOT NULL) THEN
6227 htp.p('(' || p_context_type_name || ')');
6228 END IF;
6229 htp.p('</FONT></TD><TD BGCOLOR="#666666"> </TD>');
6230 END IF;
6231
6232 IF (p_external_call = 'YES') THEN
6233 IF (g_curr_process_level > 1) THEN
6234 htp.p('<TD BGCOLOR="#DDDDDD"> </TD>');
6235 ELSE
6236 htp.p('<TD BGCOLOR="#666666"> </TD>');
6237 END IF;
6238 END IF;
6239 htp.p('</TR>');
6240
6241 --
6242 -- Display each link.
6243 --
6244 v_locator := 6;
6245 FOR each_start IN start_act_cur LOOP
6246 IF (is_act_notfound(each_start.instance_id)) THEN
6247 add_instance_to_array(each_start.instance_id);
6248 print_activity (p_selected_products, each_start.instance_id, p_external_call);
6249 END IF;
6250 END LOOP;
6251
6252 IF (p_external_call = 'YES') AND (p_new_call = 'YES') THEN
6253 htp.p('</TABLE></BODY></HTML>');
6254 END IF;
6255
6256 EXCEPTION
6257 WHEN application_exception THEN
6258 RAISE;
6259 WHEN OTHERS THEN
6260 raise_error_msg (SQLCODE, SQLERRM,
6261 'DISPLAY_PROCESS_STEPS', 'v_locator := ' || v_locator);
6262 END display_process_steps;
6263
6264 /*
6265 **
6266 ** PRINT_ACTIVITY
6267 ** ==============
6268 **
6269 ** Private procedure.
6270 ** It prints the activity if it is a notice according to the
6271 ** response order. It calls display_process if a subprocess is
6272 ** encountered. It is a recursive function that displays all the
6273 ** next activities in the path according to the response code.
6274 ** If it is a function it decides which path to take by calling
6275 ** check_activity_products function.
6276 **
6277 */
6278 PROCEDURE print_activity(
6279 p_selected_products IN VARCHAR2,
6280 p_instance_id IN NUMBER,
6281 p_display_msg IN VARCHAR2) IS
6282
6283 l_act_type wf_activities.type%TYPE;
6284 l_mesg wf_activities_vl.message%TYPE;
6285 l_dname wf_activities_vl.display_name%TYPE;
6286
6287 l_body wf_messages_vl.body%TYPE;
6288
6289 act_type wf_process_activities.activity_item_type%TYPE;
6290 act_name wf_process_activities.activity_name%TYPE;
6291 v_result VARCHAR2(200);
6292
6293 CURSOR act_cur IS
6294 SELECT wat.to_process_activity instance_id, result_code
6295 from wf_activity_transitions wat
6296 where wat.from_process_activity = p_instance_id
6297 order By result_code;
6298
6299 BEGIN
6300
6301 select activity_item_type, activity_name
6302 into act_type, act_name
6303 from wf_process_activities wpa
6304 where wpa.instance_id = p_instance_id;
6305
6306 select type, message, display_name
6307 into l_act_type, l_mesg, l_dname
6308 from wf_activities_vl wav
6309 where wav.item_type = act_type
6310 and wav.name = act_name
6311 and wav.end_date IS NULL;
6312
6313 IF l_act_type = 'NOTICE' THEN
6314
6315 -- To show an empty line at the end of a sub-process
6316 -- if it is followed by a NOTICE
6317 IF (g_prev_act_name = 'END') THEN
6318 IF (p_display_msg = 'YES') THEN
6319 htp.p('<TR><TD COLSPAN="2"> </TD><TD BGCOLOR="#DDDDDD" COLSPAN="2"> </TD></TR>');
6320 ELSE
6321 htp.p('<TR><TD COLSPAN="2"> </TD><TD BGCOLOR="#DDDDDD"> </TD></TR>');
6322 END IF;
6323 g_prev_act_name := NULL;
6324 END IF;
6325
6326 IF (p_display_msg = 'YES') THEN
6327 select body into l_body
6328 from wf_messages_vl wmv
6329 where wmv.type = act_type
6330 and wmv.name = l_mesg;
6331 htp.p('<TR><TD> </TD><TD> </TD><TD VALIGN="TOP">'||
6332 l_dname ||'</TD><TD>'|| format_step_body(l_body) ||'</TD></TR>');
6333 htp.p('<TR><TD COLSPAN="4"> </TD></TR>');
6334 ELSE
6335 htp.p('<TR><TD> </TD><TD> </TD><TD>'|| l_dname ||'</TD></TR>');
6336 END IF;
6337 ELSIF l_act_type = 'PROCESS' THEN
6338 display_process_steps(p_selected_products,
6339 act_type,
6340 act_name,
6341 ' ',
6342 ' ',
6343 'NO',
6344 p_display_msg);
6345 g_curr_process_level := g_curr_process_level - 1;
6346 ELSIF (l_act_type = 'FUNCTION' AND (act_name <> 'START' AND act_name <> 'END')) THEN
6347 --
6348 -- Check if any of the selected products
6349 -- exist in the activity installed products.
6350 --
6351 v_result := check_activity_products(p_selected_products, p_instance_id);
6352 ELSIF (l_act_type = 'FUNCTION' AND act_name = 'END') THEN
6353 g_prev_act_name := 'END';
6354 ELSE
6355 g_prev_act_name := NULL;
6356 END IF;
6357
6358 --
6359 -- For each process/activity linked to the specified
6360 -- activity.
6361 --
6362 FOR each_act IN act_cur LOOP
6363 IF (is_act_notfound(each_act.instance_id)) THEN
6364 IF (l_act_type <> 'FUNCTION') OR
6365 (l_act_type = 'FUNCTION' AND (act_name = 'START' OR act_name = 'END')) OR
6366 ((l_act_type = 'FUNCTION') AND (v_result = each_act.result_code)) THEN
6367 add_instance_to_array(each_act.instance_id);
6368 print_activity(p_selected_products, each_act.instance_id, p_display_msg);
6369 END IF;
6370 END IF;
6371 END LOOP;
6372
6373 EXCEPTION
6374 WHEN application_exception THEN
6375 RAISE;
6376 WHEN OTHERS THEN
6377 raise_error_msg (SQLCODE, SQLERRM,
6378 'PRINT_ACTIVITY', '');
6379 END print_activity;
6380
6381 /*
6382 **
6383 ** PRINT_JS_OPEN_URL
6384 ** =================
6385 **
6386 ** This procedure displays the Java Script required to open
6387 ** a URL in a new window and set focus to it.
6388 **
6389 */
6390 PROCEDURE print_js_open_url (blnStep IN BOOLEAN DEFAULT FALSE) IS
6391
6392 BEGIN
6393 htp.p('<SCRIPT LANGUAGE=Javascript>');
6394 htp.p('function OpenURL(vURL)');
6395 htp.p('{');
6396 IF (blnStep) THEN
6397 htp.p(' var NewWindow = window.open(vURL, "STEPS", ');
6398 ELSE
6399 htp.p(' var NewWindow = window.open(vURL, "TASKS", ');
6400 END IF;
6401
6402 htp.p('"scrollbars=yes,menubar=yes,toolbar=no,status=yes,location=no,resizable=yes");');
6403
6404 htp.p(' NewWindow.focus();');
6405 htp.p('}');
6406 htp.p('</SCRIPT>');
6407 EXCEPTION
6408 WHEN application_exception THEN
6409 RAISE;
6410 WHEN OTHERS THEN
6411 raise_error_msg (SQLCODE, SQLERRM,
6412 'PRINT_JS_OPEN_URL', '');
6413 END print_js_open_url;
6414
6415 END AZW_REPORT;