DBA Data[Home] [Help]

PACKAGE BODY: APPS.QA_SSQR_JRAD_PKG

Source


1 PACKAGE BODY qa_ssqr_jrad_pkg AS
2 /* $Header: qajrmpb.pls 120.37.12020000.2 2012/07/03 13:23:42 ntungare ship $ */
3 
4 TYPE ParentArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
5 -- 12.1 Device Integration Project
6 -- Added Suffix parameter to this method.
7 -- bhsankar Wed Oct 24 04:45:16 PDT 2007
8 FUNCTION construct_code (p_prefix  IN VARCHAR2, p_id IN VARCHAR2, p_suffix IN VARCHAR2 DEFAULT NULL)
9     RETURN VARCHAR2 IS
10 
11 BEGIN
12 
13    -- The function is the standard way to compute attribute and
14    -- region codes.
15 
16    -- 12.1 Device Integration Project
17    -- Appending suffix as well.
18    -- bhsankar Wed Oct 24 04:45:16 PDT 2007
19    RETURN (p_prefix ||p_id ||p_suffix);
20 
21 END construct_code;
22 
23 -- 12.1 Inline Project
24 -- adding debug API's for better debugging
25 -- saugupta
26 procedure set_debug_mode(p_mode IN VARCHAR2) IS
27 begin
28         IF (p_mode = 'QA_LOCAL') THEN
29                 g_debug_mode := p_mode;
30         END IF;
31 end set_debug_mode;
32 
33 procedure log_local_error( p_module_name IN VARCHAR2, p_error_message IN VARCHAR2, p_comments IN VARCHAR2 DEFAULT NULL)
34 IS
35         pragma autonomous_transaction;
36         x_logid number;
37         cursor id
38         IS
39          SELECT qa_skiplot_log_id_s.nextval
40          FROM dual;
41 BEGIN
42         open id;
43         fetch id into x_logid;
44         close id;
45         INSERT
46         INTO    qa_skiplot_log
47                 (
48                         LOG_ID,
49                         MODULE_NAME,
50                         ERROR_MESSAGE,
51                         COMMENTS,
52                         LAST_UPDATE_DATE,
53                         LAST_UPDATED_BY,
54                         CREATION_DATE,
55                         CREATED_BY,
56                         LAST_UPDATE_LOGIN
57                 )
58                 VALUES
59                 (
60                         x_logid,
61                         p_module_name,
62                         p_error_message,
63                         p_comments,
64                         sysdate,
65                         fnd_global.user_id,
66                         sysdate,
67                         fnd_global.user_id,
68                         fnd_global.login_id
69                 )
70                 ;
71         commit;
72 end log_local_error;
73 
74 procedure log_error(p_api_name IN varchar2, p_error_message IN varchar2 )
75 IS
76 begin
77 
78       IF ( g_debug_mode = 'QA_LOCAL' ) THEN
79           log_local_error(p_api_name, p_error_message);
80       ELSE
81           IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
82                 FND_LOG.string ( FND_LOG.LEVEL_STATEMENT,
83                                  p_api_name,
84                                  p_error_message );
85           ELSIF (FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
86                 FND_LOG.string ( FND_LOG.LEVEL_PROCEDURE,
87                                  p_api_name,
88                                  p_error_message );
89          END IF;
90       END IF;  -- g_debug_mode
91 end log_error;
92 
93 
94 
95 -- 12.1 Inline Project END
96 
97 FUNCTION get_prompt (p_plan_id IN NUMBER,
98     p_char_id IN NUMBER) RETURN VARCHAR2 IS
99 
100     l_prompt qa_plan_chars.prompt%TYPE;
101     l_uom_code qa_plan_chars.uom_code%TYPE;
102 BEGIN
103    -- The function is the standard way to compute prompt
104    -- taking uom_code into account
105     l_prompt := qa_plan_element_api.get_prompt(p_plan_id, p_char_id);
106     l_uom_code := qa_plan_element_api.get_uom_code(p_plan_id, p_char_id);
107 
108     IF (l_uom_code is not null) THEN
109       -- 12.1 QWB Usability Improvements
110       -- Encode the HTML special characters
111       -- ntungare
112       --
113       RETURN DBMS_XMLGEN.convert(l_prompt || ' (' || l_uom_code || ')');
114     ELSE
115       RETURN DBMS_XMLGEN.convert(l_prompt);
116     END IF;
117 END get_prompt;
118 
119 
120 FUNCTION get_special_item_label (p_prefix IN VARCHAR2)
121     RETURN VARCHAR2 IS
122 
123     label VARCHAR2(30);
124 
125 BEGIN
126 
127     -- For some hardocded columns such as "Created By", "Colleciton"
128     -- and "Last Update Date" we need to retrieve the right label
129     -- keeping translation in mind.
130 
131     IF (p_prefix = g_qa_created_by_attribute) THEN
132         label := fnd_message.get_string('QA','QA_SS_CREATED_BY');
133     ELSIF (p_prefix = g_collection_id_attribute) THEN
134         label := fnd_message.get_string('QA','QA_SS_COLLECTION');
135     ELSIF (p_prefix = g_last_update_date_attribute) THEN
136         label := fnd_message.get_string('QA','QA_SS_ENTRY_DATE');
137     ELSIF (p_prefix = g_multi_row_attachment) THEN
138         label := fnd_message.get_string('QA','QA_SS_JRAD_ATTACHMENT');
139     ELSE
140         label := null;
141     END IF;
142 
143     -- 12.1 QWB Usability Improvements
144     -- Encode the HTML special characters
145     -- ntungare
146     --
147     RETURN DBMS_XMLGEN.convert(label);
148 
149 END get_special_item_label;
150 
151 
152 
153 FUNCTION get_vo_attribute_name (p_plan_id IN NUMBER, p_char_id IN NUMBER)
154     RETURN VARCHAR2 IS
155 
156     column_name  VARCHAR2(100);
157 
158 BEGIN
159 
160     -- For hardcoded elements, it returns developer name,
161     -- For others, it returns results column name in qa plan chars.
162 
163     column_name := qa_core_pkg.get_result_column_name (p_char_id, p_plan_id);
164     column_name := replace(column_name, 'CHARACTER', 'Character');
165     column_name := replace(column_name, 'COMMENT', 'Comment');
166     column_name := replace(column_name, 'SEQUENCE', 'Sequence');
167 
168     RETURN column_name;
169 
170 END get_vo_attribute_name;
171 
172 
173 
174 FUNCTION get_hardcoded_vo_attr_name (p_code IN VARCHAR2)
175     RETURN VARCHAR2 IS
176 
177     column_name  VARCHAR2(100);
178 
179 BEGIN
180 
181    -- This function retrieves the result column name for
182    -- hard coded elements.
183 
184    IF (INSTR(p_code, g_org_id_attribute) <> 0) THEN
185        column_name := 'ORGANIZATION_ID';
186    ELSIF (INSTR(p_code, g_plan_id_attribute) <> 0) THEN
187        column_name := 'PLAN_ID';
188    ELSIF (INSTR(p_code, g_qa_created_by_attribute) <> 0) THEN
189        column_name := 'QA_CREATED_BY_NAME';
190    ELSIF (INSTR(p_code, g_collection_id_attribute) <> 0) THEN
191        column_name := 'COLLECTION_ID';
192    ELSIF (INSTR(p_code, g_last_update_date_attribute) <> 0) THEN
193        column_name := 'LAST_UPDATE_DATE';
194    ELSIF (INSTR(p_code, g_multi_row_attachment) <> 0) THEN
195        column_name := '';
196    END IF;
197 
198    RETURN column_name;
199 
200 END get_hardcoded_vo_attr_name;
201 
202 
203 FUNCTION convert_data_type (p_data_type IN NUMBER)
204     RETURN VARCHAR2 IS
205 
206 BEGIN
207 
208     -- In Quality the data type is indicated by a number. whereas,
209     -- in ak it a is string that describes what the data type is.
210     -- This routine was written to convert the data_type according
211     -- to AK.
212 
213     IF p_data_type in (g_char_datatype,g_comments_datatype,g_seq_datatype) THEN
214         return 'VARCHAR2';
215     ELSIF p_data_type = g_num_datatype THEN
216         return 'NUMBER';
217     ELSIF p_data_type = g_date_datatype THEN
218         return 'DATE';
219     -- bug 3236302. rkaza. 11/04/2003. Timezone support.
220     ELSIF p_data_type = g_datetime_datatype THEN
221         return 'DATETIME';
222     ELSE --catch all
223         return 'VARCHAR2';
224     END IF;
225 
226 END convert_data_type;
227 
228 
229 FUNCTION convert_yesno_flag (p_flag IN NUMBER)
230     RETURN VARCHAR2 IS
231 
232 BEGIN
233 
234     -- In Quality all the flags are numeric, meaning a value of 1 or 2
235     -- is used to indicate if the flag is on or off.  In AK however,
236     -- it is a character that describes if the flag is on or off.
237     -- This routine was written to convert the Quality flags to AK.
238 
239     IF p_flag = 1 THEN
240         return 'yes';
241     ELSE
242         return 'no';
243     END IF;
244 
245 END convert_yesno_flag;
246 
247 
248 FUNCTION convert_boolean_flag (p_flag IN NUMBER)
249     RETURN VARCHAR2 IS
250 
251 BEGIN
252 
253     -- In Quality all the flags are numeric, meaning a value of 1 or 2
254     -- is used to indicate if the flag is on or off.  In AK however,
255     -- it is a character that describes if the flag is on or off.
256     -- This routine was written to convert the Quality flags to AK.
257 
258     IF p_flag = 1 THEN
259         return 'true';
260     ELSE
261         return 'false';
262     END IF;
263 
264 END convert_boolean_flag;
265 
266 
267 
268 FUNCTION compute_item_style (p_plan_id IN NUMBER, p_element_id IN NUMBER)
269                                 RETURN VARCHAR2 IS
270 
271 BEGIN
272 
273     IF qa_plan_element_api.qpc_read_only_flag(p_plan_id, p_element_id) = 1
274                 or qa_chars_api.datatype(p_element_id) = g_seq_datatype THEN
275         return 'messageStyledText';
276     ELSIF qa_plan_element_api.qpc_poplist_flag(p_plan_id,
277                 p_element_id) = 1 then
278         return 'messageChoice';
279     ELSIF (qa_plan_element_api.values_exist(p_plan_id, p_element_id)
280                 OR qa_plan_element_api.sql_validation_exists(p_element_id)
281                 OR qa_chars_api.has_hardcoded_lov(p_element_id)) THEN
282         return 'messageLovInput';
283     ELSE
284         return 'messageTextInput';
285     END IF;
286 
287 END compute_item_style;
288 
289 
290  -- Bug 4506400. OA Framework Integration. UT bug fix.
291  -- Set maximum length property for items.
292  -- srhariha. Mon Aug 29 04:55:57 PDT 2005.
293 
294  --
295  -- Get maximum length for region item.
296  -- Data is fetched from FND_COLUMNS table.
297  -- Important : For hardcoded elements following should be same as
298  --             developer name.
299  --             - VO ATTRIBUTE NAME
300  --             - COLUMN NAME IN QA_RESULTS_INTERFACE
301  --
302  -- Returns -1 for error condition.
303  --
304 
305 FUNCTION get_max_length(p_column_name IN VARCHAR2) RETURN NUMBER IS
306 
307 cursor c1 is
308  select fc.width
309  from fnd_columns fc, fnd_tables ft
310  where fc.table_id = ft.table_id
311  and ft.table_name = 'QA_RESULTS_INTERFACE'
312  and ft.application_id = 250
313  and fc.user_column_name = p_column_name
314  and fc.application_id = 250; -- to use index
315 
316  l_width number;
317 BEGIN
318 
319  open c1 ;
320  fetch c1 into l_width;
321  close c1;
322 
323  if(l_width is null) then
324    l_width := -1 ;
325  end if;
326 
327  return l_width;
328 
329 END get_max_length;
330 
331 
332 
333 
334 FUNCTION create_jrad_region_item(
335     p_item_style IN VARCHAR2) RETURN JDR_DOCBUILDER.ELEMENT IS
336 
337 BEGIN
338 
339     RETURN JDR_DOCBUILDER.createElement(jdr_docbuilder.OA_NS, p_item_style);
340 
341 END create_jrad_region_item;
342 
343 
344 
345 PROCEDURE add_child_to_parent(
346     parent_element IN JDR_DOCBUILDER.ELEMENT,
347     child_element IN JDR_DOCBUILDER.ELEMENT,
348     p_tag_name IN VARCHAR2) IS
349 
350 BEGIN
351     -- p_tag_name can be 'contents', 'detail'
352     JDR_DOCBUILDER.addChild(parent_element, JDR_DOCBUILDER.UI_NS, p_tag_name,
353                                 child_element);
354 
355 END add_child_to_parent;
356 
357 
358 
359 
360 PROCEDURE get_lov_dependencies (p_char_id IN NUMBER,
361                                 x_parents OUT NOCOPY ParentArray) IS
362 
363 BEGIN
364 
365     -- This is needed for populating correct lov relations.
366     -- Given a element id, this function computes the
367     -- ancestors for it and accordingly populates a
368     -- OUT table structure.
369 
370     x_parents.delete();
371 
372     IF p_char_id = qa_ss_const.item THEN
373         x_parents(1) := qa_ss_const.production_line;
374 
375     ELSIF p_char_id = qa_ss_const.to_op_seq_num THEN
376         x_parents(1) := qa_ss_const.job_name;
377         x_parents(2) := qa_ss_const.production_line;
378 
379     ELSIF p_char_id = qa_ss_const.from_op_seq_num THEN
380         x_parents(1) := qa_ss_const.job_name;
381         x_parents(2) := qa_ss_const.production_line;
382 
383     ELSIF p_char_id = qa_ss_const.to_intraoperation_step THEN
384         x_parents(1) := qa_ss_const.to_op_seq_num;
385 
386     ELSIF p_char_id = qa_ss_const.from_intraoperation_step THEN
387         x_parents(1) := qa_ss_const.from_op_seq_num;
388 
389     ELSIF p_char_id = qa_ss_const.uom THEN
390 
391         x_parents(1) := qa_ss_const.item;
392         x_parents(2) := qa_ss_const.production_line;
393 
394     ELSIF p_char_id = qa_ss_const.revision THEN
395         x_parents(1) := qa_ss_const.item;
396         x_parents(2) := qa_ss_const.production_line;
397 
398     ELSIF p_char_id = qa_ss_const.subinventory THEN
399         x_parents(1) := qa_ss_const.item;
400         x_parents(2) := qa_ss_const.production_line;
401 
402     ELSIF p_char_id = qa_ss_const.locator THEN
403         x_parents(1) := qa_ss_const.subinventory;
404         x_parents(2) := qa_ss_const.item;
405         x_parents(3) := qa_ss_const.production_line;
406 
407     -- anagarwa Thu Aug 12 15:49:51 PDT 2004
408     -- bug 3830258 incorrect LOVs in QWB
409     -- synced up the lot number lov with forms
410     ELSIF p_char_id = qa_ss_const.lot_number THEN
411         x_parents(1) := qa_ss_const.item;
412         --x_parents(2) := qa_ss_const.production_line;
413 
414     -- anagarwa Thu Aug 12 15:49:51 PDT 2004
415     -- bug 3830258 incorrect LOVs in QWB
416     -- synced up the serial number lov with forms
417     ELSIF p_char_id = qa_ss_const.serial_number THEN
418         x_parents(1) := qa_ss_const.lot_number;
419         x_parents(2) := qa_ss_const.item;
420         --x_parents(3) := qa_ss_const.production_line;
421         x_parents(3) := qa_ss_const.revision;
422 
423     ELSIF p_char_id = qa_ss_const.comp_uom THEN
424         x_parents(1) := qa_ss_const.comp_item;
425 
426     ELSIF p_char_id = qa_ss_const.comp_revision THEN
427         x_parents(1) := qa_ss_const.comp_item;
428 
429     ELSIF p_char_id = qa_ss_const.po_line_num THEN
430         x_parents(1) := qa_ss_const.po_number;
431 
432     ELSIF p_char_id = qa_ss_const.po_shipment_num THEN
433         x_parents(1) := qa_ss_const.po_line_num;
434         x_parents(2) := qa_ss_const.po_number;
435         -- Bug 9817478. Added PO Release Num as parent
436         -- for PO Shipment(for Blanket POs). skolluku.
437         x_parents(3) := qa_ss_const.po_release_num;
438 
439     ELSIF p_char_id = qa_ss_const.po_release_num THEN
440         x_parents(1) := qa_ss_const.po_number;
441         -- Bug 9817478. Added po_line as a parent.
442         -- skolluku.
443         x_parents(2) := qa_ss_const.po_line_num;
444 
445     ELSIF p_char_id = qa_ss_const.order_line THEN
446         x_parents(1) := qa_ss_const.sales_order;
447 
448     ELSIF p_char_id = qa_ss_const.task_number THEN
449         x_parents(1) := qa_ss_const.project_number;
450 
451     --dgupta: Start R12 EAM Integration. Bug 4345492
452     ELSIF p_char_id = qa_ss_const.asset_instance_number THEN
453         x_parents(1) := qa_ss_const.asset_group;
454         -- bug 13841504
455         -- The asset instance number lov would no longer depend on
456         -- the Asset serial number
457         --x_parents(2) := qa_ss_const.asset_number;
458 
459     ELSIF p_char_id = qa_ss_const.asset_number THEN
460         x_parents(1) := qa_ss_const.asset_group;
461         x_parents(2) := qa_ss_const.asset_instance_number;
462 
463     -- rkaza. 12/02/2003. bug 3215372.
464     -- Both asset group and asset number were being assigned to x_parents(1)
465     ELSIF p_char_id = qa_ss_const.asset_activity THEN
466         x_parents(1) := qa_ss_const.asset_group;
467         x_parents(2) := qa_ss_const.asset_number;
468         x_parents(3) := qa_ss_const.asset_instance_number;
469 
470     ELSIF p_char_id = qa_ss_const.followup_activity THEN
471         x_parents(1) := qa_ss_const.asset_group;
472         x_parents(2) := qa_ss_const.asset_number;
473         x_parents(3) := qa_ss_const.asset_instance_number;
474     --dgupta: End R12 EAM Integration. Bug 4345492
475 
476     -- rkaza. 12/02/2003. bug 3215404.
477     -- Added dependency relation for maintenance op seq with maintenance
478     -- work order.
479     ELSIF p_char_id = qa_ss_const.maintenance_op_seq THEN
480         x_parents(1) := qa_ss_const.work_order;
481 
482     -- rkaza. 12/02/2003. bug 3280307.
483     -- Added dependency relation for component item with item
484     ELSIF p_char_id = qa_ss_const.comp_item THEN
485         x_parents(1) := qa_ss_const.item;
486 
487     -- anagarwa Thu Aug 12 15:49:51 PDT 2004
488     -- bug 3830258 incorrect LOVs in QWB
489     -- synced up the component lot number and component serial number
490     -- lov with forms
491     ELSIF p_char_id = qa_ss_const.comp_lot_number THEN
492         x_parents(1) := qa_ss_const.comp_item;
493 
494     ELSIF p_char_id = qa_ss_const.comp_serial_number THEN
495         x_parents(1) := qa_ss_const.comp_lot_number;
496         x_parents(2) := qa_ss_const.comp_item;
497         x_parents(3) := qa_ss_const.comp_revision;
498 
499     -- R12 OPM Deviations. Bug 4345503 Start
500     ELSIF p_char_id = qa_ss_const.process_batchstep_num THEN
501         x_parents(1) := qa_ss_const.process_batch_num;
502 
503     ELSIF p_char_id = qa_ss_const.process_operation THEN
504         x_parents(1) := qa_ss_const.process_batch_num;
505         x_parents(2) := qa_ss_const.process_batchstep_num;
506 
507     ELSIF p_char_id = qa_ss_const.process_activity THEN
508         x_parents(1) := qa_ss_const.process_batch_num;
509         x_parents(2) := qa_ss_const.process_batchstep_num;
510 
511     ELSIF p_char_id = qa_ss_const.process_resource THEN
512         x_parents(1) := qa_ss_const.process_batch_num;
513         x_parents(2) := qa_ss_const.process_batchstep_num;
514         x_parents(3) := qa_ss_const.process_activity;
515 
516     ELSIF p_char_id = qa_ss_const.process_parameter THEN
517         x_parents(1) := qa_ss_const.process_resource;
518     -- R12 OPM Deviations. Bug 4345503 End
519 
520     --
521     -- Bug 6161802
522     -- Added dependency relation for  rma line number with rma number
523     -- skolluku Thu Mon Jul 16 22:08:16 PDT 2007
524     --
525     ELSIF p_char_id = qa_ss_const.rma_line_num THEN
526         x_parents(1) := qa_ss_const.rma_number;
527 
528     --
529     -- Bug 7716875.pdube Mon Apr 13 03:25:19 PDT 2009
530     -- Added dependency relation for SO line number with SO number
531     --
532     ELSIF p_char_id = qa_ss_const.order_line THEN
533          x_parents(1) := qa_ss_const.sales_order;
534 
535     -- Bug 7588754.pdube Wed Apr 15 07:37:25 PDT 2009
536     -- Added dependency on item and srl number
537     ELSIF p_char_id = qa_ss_const.serial_status THEN
538          x_parents(1) := qa_ss_const.item;
539          x_parents(2) := qa_ss_const.serial_number;
540     --
541     -- Bug 9032151
542     -- Added dependency relation for  item instance with item
543     -- skolluku
544     --
545     ELSIF p_char_id = qa_ss_const.item_instance THEN
546          x_parents(1) := qa_ss_const.item;
547     --
548     -- Bug 9359442
549     -- Added dependency relation for  item instance serial with item
550     -- skolluku
551     --
552     ELSIF p_char_id = qa_ss_const.item_instance_serial THEN
553         x_parents(1) := qa_ss_const.item;
554 
555     --
556     -- Bug 13638890
557     -- Added dependency relation for to_subinventory with item
558     -- added dependency relation for to_locator with subinventory and item
559     -- hmakam
560     --
561     ELSIF p_char_id = qa_ss_const.to_subinventory THEN
562         x_parents(1) := qa_ss_const.item;
563         x_parents(2) := qa_ss_const.production_line;
564 
565     ELSIF p_char_id = qa_ss_const.to_locator THEN
566         x_parents(1) := qa_ss_const.to_subinventory;
567         x_parents(2) := qa_ss_const.item;
568         x_parents(3) := qa_ss_const.production_line;
569     END IF;
570 
571 END get_lov_dependencies;
572 
573 
574 
575 FUNCTION get_region_prompt (p_region_type VARCHAR2)
576     RETURN VARCHAR2 IS
577 
578     -- Bug 6998253
579     -- this can also be an UI label and not just collection
580     -- element prompt, so increasing the length
581     l_prompt VARCHAR2(250);
582     l_message VARCHAR2(30);
583     l_api_name VARCHAR2(100) := 'GET_REGION_PROMPT';
584 
585 BEGIN
586 
587     log_error(g_pkg_name || l_api_name, 'Function BEGIN');
588     -- Bug 4506769. OA Framework Integation project. UT bug fix.
589     -- Getting prompts from FND_NEW_MESSAGES.
590     -- srhariha. Fri Aug 26 00:16:30 PDT 2005.
591 
592     If p_region_type = 'TOP' then
593         l_prompt := null;
594     elsif p_region_type = 'DATA' then
595         l_message := 'QA_SS_RN_PROMPT_DATA';
596     elsif p_region_type = 'COMMENTS' then
597         l_message := 'QA_SS_RN_PROMPT_COMMENTS';
598     elsif p_region_type = 'ATTACHMENTS' then
599         l_message := 'QA_SS_JRAD_ATTACHMENT';
600     -- 12.1 Device Integration Project
601     -- Get prompt for the device region
602     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
603     elsif p_region_type = 'DEVICE' then
604         l_message := 'QA_SS_RN_PROMPT_DEVICE';
605     end if;
606 
607     l_prompt := fnd_message.get_string('QA',l_message);
608 
609     log_error(g_pkg_name || l_api_name, 'Function END Returning l_prompt as ' || l_prompt);
610 
611     -- 12.1 Device Integration
612     -- Encode to HTML special Characters.
613     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
614     RETURN DBMS_XMLGEN.convert(l_prompt);
615 
616 END get_region_prompt;
617 
618 
619 
620 /*
621   anagarwa Thu Dec  4 11:30:36 PST 2003
622   Bug 3297976
623   Based upon UI cheat sheet mandatory fixes, we use labeledFieldLayout
624   for details regions. This has been done to reduce whitespace in the
625   hidden region of eqr table row.
626 
627 */
628 
629 FUNCTION create_jrad_region (
630     p_region_code IN VARCHAR2,
631     p_region_style IN VARCHAR2,
632     p_prompt IN VARCHAR2,
633     p_columns IN VARCHAR2,
634     p_mode in VARCHAR2 default null) RETURN JDR_DOCBUILDER.ELEMENT IS
635 
636     l_element JDR_DOCBUILDER.ELEMENT := NULL;
637     l_addText VARCHAR2(1000);
638 
639     l_api_name VARCHAR2(100) := 'CREATE_JRAD_REGION';
640 
641 BEGIN
642     -- region style choices:
643     -- stackLayout, defaultDoubleColumn, defaultSingleColumn, table
644     log_error(g_pkg_name || l_api_name, 'Function BEGIN');
645 
646     l_element := JDR_DOCBUILDER.createElement(JDR_DOCBUILDER.OA_NS, p_region_style);
647 
648     -- 12.1 Inline Region Project
649     -- added advancedTable below
650     -- saurabh
651 
652     -- 12.1 Device Integration Project
653     -- Added header to the in clause
654     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
655     if p_region_style in ('defaultDoubleColumn', 'defaultSingleColumn', 'table',  'header') then
656         jdr_docbuilder.setAttribute(l_element, 'text', p_prompt);
657     elsif p_region_style <> 'labeledFieldLayout' then
658         jdr_docbuilder.setAttribute(l_element, 'prompt', p_prompt);
659     end if;
660 
661     if ( p_region_style =  'advancedTable')  then
662         jdr_docbuilder.setAttribute(l_element, 'text', '');
663     end if;
664 
665     jdr_docbuilder.setAttribute(l_element, 'regionName', p_prompt);
666     jdr_docbuilder.setAttribute(l_element, 'id', p_region_code);
667 
668     -- 12.1 Inline Region Project
669     -- added advancedTable below
670     -- saurabh
671     if (p_region_style = 'table' OR p_region_style = 'advancedTable')  then
672       jdr_docbuilder.setAttribute(l_element, 'detailViewAttr', 'HideShowStatus');
673       jdr_docbuilder.setAttribute(l_element, 'unvalidated', 'True');
674       -- anagarwa Mon Nov 17 15:34:29 PST 2003
675       -- bug 3251538
676       -- we need to add addtional text for all tables.
677       if nvl(p_mode, '@') <> g_vqr_multiple_layout then
678           l_addText := fnd_message.get_string('QA','QA_SSQR_E_MULT_TEXT');
679       else
680           l_addText := fnd_message.get_string('QA','QA_SSQR_V_MULT_TEXT');
681       end if;
682 
683       -- 12.1 QWB Usability Improvements
684       -- Encode the HTML special characters
685       -- ntungare
686       jdr_docbuilder.setAttribute(l_element, 'shortDesc', DBMS_XMLGEN.convert(l_addText));
687     end if;
688 
689     if p_region_style = 'labeledFieldLayout' then
690       jdr_docbuilder.setAttribute(l_element, 'columns', p_columns);
691     end if;
692 
693     -- 12.1 Device Integration Project
694     -- Setting width to 100% for tablelayout style
695     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
696     if p_region_style = 'tableLayout' then
697       jdr_docbuilder.setAttribute(l_element, 'width', '100%');
698     end if;
699 
700     log_error(g_pkg_name || l_api_name, 'Function END returns ');
701 
702     RETURN l_element;
703 
704 END create_jrad_region;
705 
706     --
707     -- MOAC Project. 4637896
708     -- New procedure to create base attribute code.
709     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
710     --
711 
712 FUNCTION cons_base_attribute_code (
713       p_element_prefix           IN VARCHAR2,
714       p_id                       IN VARCHAR2)
715         RETURN VARCHAR2  IS
716 
717 BEGIN
718     --
719     -- bug 5383667
720     -- Added check for Party Name
721     -- ntungare
722     --
723     IF(p_id = qa_ss_const.po_number OR
724        p_id = qa_ss_const.party_name) THEN
725        return qa_chars_api.hardcoded_column(p_id);
726     END IF;
727 
728     return construct_code(p_element_prefix,p_id);
729 
730 END cons_base_attribute_code;
731 
732 --
733 -- bug 6884645
734 -- New procedure to create an array of collection
735 -- elements that would be displayed in the Header
736 -- region of a Multirow block
737 --
738 PROCEDURE multirow_hdrelements_array(
739     p_plan_id      IN NUMBER,
740     elements_array OUT NOCOPY ParentArray) IS
741 
742 
743 BEGIN
744     --
745     -- Selecting the first 5 elements in the collection
746     -- plan ordered on the basis of the mandatory flag
747     -- and the prompt sequence as they would be displayed
748     -- in the header region of a multirow block.
749     --
750     SELECT char_id BULK COLLECT INTO elements_array
751       FROM (SELECT char_id
752              FROM QA_PLAN_CHARS
753             WHERE plan_id = p_plan_id
754               AND enabled_flag = 1
755             ORDER BY mandatory_flag, prompt_sequence)
756      WHERE rownum <=5;
757 END multirow_hdrelements_array;
758 
759 --
760 -- bug 6884645
761 -- Added a new parameter the procesing mode
762 -- ntungare
763 --
764 PROCEDURE add_lov_relations (
765     p_plan_id                   IN NUMBER,
766     p_char_id                   IN NUMBER,
767     p_attribute_code            IN VARCHAR2,
768     p_input_elem                IN jdr_docbuilder.Element,
769     p_mode                      IN VARCHAR2 DEFAULT NULL) IS
770 
771 
772     l_row_id                    VARCHAR2(30);
773     l_region_code               VARCHAR2(30);
774     l_attribute_code            VARCHAR2(30);
775     l_lov_attribute_code        VARCHAR2(30);
776     l_base_attribute_code       VARCHAR2(30);
777     l_parents                   ParentArray;
778 
779     --
780     -- bug 6884645
781     -- added a new array for the multirow header elements
782     -- ntungare
783     --
784     l_multirow_headers          ParentArray;
785 
786     lovMap  jdr_docbuilder.ELEMENT;
787 
788     -- bug 6884645
789     -- variable to check if the parent attribute
790     -- has been processed
791     -- ntungare
792     --
793     parent_element_processed BOOLEAN := FALSE;
794 BEGIN
795 
796     -- This function adds lov relations for a region item.
797     -- Here the region item corresponds to a collection plan element.
798 
799    --Criteria
800    lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
801    jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', p_attribute_code);
802    jdr_docbuilder.setAttribute(lovMap, 'lovItem', g_lov_attribute_code);
803    jdr_docbuilder.setAttribute(lovMap, 'requiredForLOV', 'true');
804    jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
805                                         lovMap);
806    --Result
807    lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
808    jdr_docbuilder.setAttribute(lovMap, 'resultTo', p_attribute_code);
809    jdr_docbuilder.setAttribute(lovMap, 'lovItem', g_lov_attribute_code);
810    jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
811                                         lovMap);
812   --Org Id
813    lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
814    jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', g_org_id_attribute);
815    jdr_docbuilder.setAttribute(lovMap, 'lovItem', g_lov_attribute_org_id);
816    jdr_docbuilder.setAttribute(lovMap, 'programmaticQuery', 'true');
817    jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
818                                         lovMap);
819   --Plan Id
820    lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
821    jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', g_plan_id_attribute);
822    jdr_docbuilder.setAttribute(lovMap, 'lovItem', g_lov_attribute_plan_id);
823    jdr_docbuilder.setAttribute(lovMap, 'programmaticQuery', 'true');
824    jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
825                                         lovMap);
826 
827 
828     get_lov_dependencies(p_char_id, l_parents);
829 
830     FOR i IN 1..l_parents.COUNT LOOP
831 
832         -- anagarwa
833         -- Bug 2751198
834         -- Add dependency to LOV only if the element exists in the plan
835         -- This is achieved by adding the following IF statement
836 
837         -- rkaza. 10/22/2003. 3280307. shold not use exists_qa_plan_chars
838         -- array might not have been initialized. use element_in_plan
839       -- IF qa_plan_element_api.exists_qa_plan_chars(p_plan_id, l_parents(i)) THEN
840       IF qa_plan_element_api.element_in_plan(p_plan_id, l_parents(i)) THEN
841           l_lov_attribute_code := g_lov_attribute_dependency || to_char(i);
842           --
843           -- MOAC Project. 4637896
844           -- Call new procedure to construct base code
845           -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
846           --
847           -- bug 6884645
848           -- The multirow prefix should be used if the base element would be
849           -- displayed in the details region for a multirow block as
850           -- otherwise its value cannot be read
851           -- ntungare Sat Mar 22 08:39:05 PDT 2008
852           --
853           IF (p_mode = g_eqr_advtable_layout OR p_mode = g_eqr_multiple_layout) THEN
854              -- Call the procedure to populate an array containing the
855              -- collection elements appearing in the header region of
856              -- a multirow block
857              --
858              multirow_hdrelements_array(p_plan_id, l_multirow_headers);
859 
860              -- loop through the header elements array and check if the parent element
861              -- is a part of it. if yes then the CHARID prefix is to be used else
862              -- the DTLCHARID prefix is to be used since the parent element is in the
863              -- detail region
864              -- ntungare
865              --
866              FOR cntr in 1..l_multirow_headers.COUNT
867                 loop
868                    -- parent element in header region
869                    IF (l_parents(i) = l_multirow_headers(cntr)) THEN
870                       l_base_attribute_code := cons_base_attribute_code(g_element_prefix, l_parents(i));
871 
872                       -- Set the parent element processed flag as TRUE
873                       parent_element_processed := TRUE;
874                       EXIT;
875                    END If;
876                 end loop;
877 
878              -- If the parent element processed flag is FALSE then it means
879              -- that the parent element is not in header
880              IF (parent_element_processed = FALSE) THEN
881                  l_base_attribute_code := cons_base_attribute_code(g_dtl_element_prefix, l_parents(i));
882              END If;
883 
884           -- Single row region processing
885           ELSE
886              l_base_attribute_code := cons_base_attribute_code(g_element_prefix, l_parents(i));
887           END IF;
888 
889           lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
890           jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', l_base_attribute_code);
891           jdr_docbuilder.setAttribute(lovMap, 'lovItem', l_lov_attribute_code);
892           jdr_docbuilder.setAttribute(lovMap, 'programmaticQuery', 'true');
893           jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS,
894                                         'lovMappings',lovMap);
895       END IF;
896 
897       --
898       -- bug 6884645
899       -- resetting the flag for the next parent element
900       --
901       parent_element_processed := FALSE;
902     END LOOP;
903 
904 
905 END add_lov_relations;
906 
907     --
908     -- MOAC Project. 4637896
909     -- New procedure to create id item.
910     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
911     --
912 
913 
914 FUNCTION create_id_item_for_eqr (
915     p_plan_id                  IN NUMBER,
916     p_char_id                  IN NUMBER,
917     p_mode IN VARCHAR2 DEFAULT NULL)
918         RETURN jdr_docbuilder.ELEMENT  IS
919 
920     l_vo_attribute_name         VARCHAR2(30)  DEFAULT NULL;
921     l_id_elem jdr_docbuilder.ELEMENT := NULL;
922 
923 BEGIN
924 
925     l_vo_attribute_name := qa_chars_api.hardcoded_column(p_char_id);
926     l_id_elem := create_jrad_region_item('formValue');
927 
928     -- set properties
929     jdr_docbuilder.setAttribute(l_id_elem, 'id', l_vo_attribute_name);
930     --if( p_mode <> g_eqr_advtable_layout OR p_mode is NULL) then
931         jdr_docbuilder.setAttribute(l_id_elem, 'viewName', g_vo_name);
932     --end if;
933     jdr_docbuilder.setAttribute(l_id_elem, 'viewAttr', l_vo_attribute_name);
934     jdr_docbuilder.setAttribute(l_id_elem, 'dataType', 'NUMBER');
935 
936     return l_id_elem;
937 
938 END create_id_item_for_eqr;
939 
940 
941 
942     --
943     -- MOAC Project. 4637896
944     -- Checks whether its a normalized lov item.
945     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
946     --
947 FUNCTION is_normalized_lov  (
948          p_plan_id     IN NUMBER,
949          p_char_id     IN NUMBER) RETURN VARCHAR2 IS
950 
951 BEGIN
952     -- currently we are enabling normalized logic
953     -- only for  PO NUMBER
954     --
955     -- bug 5383667
956     -- added the party name to the list of normalized vals
957     -- ntungare
958     --
959     if((p_char_id = qa_ss_const.po_number) OR
960        (p_char_id = qa_ss_const.party_name))then
961       return 'T';
962     end if;
963 
964     return 'F';
965 END is_normalized_lov;
966 
967     --
968     -- MOAC Project. 4637896
969     -- Gets external LOV region name
970     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
971     --
972 FUNCTION get_lov_region_name  (
973          p_plan_id     IN NUMBER,
974          p_char_id     IN NUMBER) RETURN VARCHAR2 IS
975 
976 BEGIN
977     -- currently we are enabling normalized logic
978     -- only for  PO NUMBER. So we are hard coding
979     -- lov region name. In future, this proc must
980     -- be generalized.
981     if(p_char_id = qa_ss_const.po_number) then
982       return 'PONumberLovRN';
983     --
984     -- bug 5383667
985     -- getting the region LOV region name for
986     -- Party Name. We are currently using a separate
987     -- region for the Part name, however we should
988     -- later use a common region for all the normalized
989     -- id elements
990     --
991     elsif (p_char_id = qa_ss_const.party_name) then
992       return 'PartyLovRN';
993     end if;
994 
995     return 'QaLovRN';
996 
997 END get_lov_region_name;
998 
999 
1000     --
1001     -- MOAC Project. 4637896
1002     -- New method to process normalized lov item.
1003     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1004     --
1005 
1006 PROCEDURE process_normalized_lov (
1007     p_plan_id                   IN NUMBER,
1008     p_char_id                   IN NUMBER,
1009     p_attribute_code            IN VARCHAR2,
1010     p_char_item                IN jdr_docbuilder.Element) IS
1011 
1012    lovMap  jdr_docbuilder.ELEMENT;
1013    l_lov_region  VARCHAR2(100);
1014 
1015 BEGIN
1016    l_lov_region := g_jrad_lov_dir_path ||  get_lov_region_name(p_plan_id,p_char_id);
1017    jdr_docbuilder.setAttribute(p_char_item,
1018                                'externalListOfValues',
1019                                l_lov_region);
1020     --Criteria
1021    lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
1022    jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', p_attribute_code);
1023    jdr_docbuilder.setAttribute(lovMap, 'resultTo', p_attribute_code);
1024 
1025    IF(p_char_id = qa_ss_const.po_number) THEN
1026       jdr_docbuilder.setAttribute(lovMap, 'lovItem', 'Segment1');
1027       jdr_docbuilder.setAttribute(lovMap, 'requiredForLOV', 'true');
1028       jdr_docbuilder.addChild(p_char_item, jdr_docbuilder.JRAD_NS, 'lovMappings',
1029                               lovMap);
1030 
1031       -- po_header_id
1032       lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
1033       jdr_docbuilder.setAttribute(lovMap, 'lovItem', 'PoHeaderId');
1034       jdr_docbuilder.setAttribute(lovMap, 'resultTo', qa_chars_api.hardcoded_column(p_char_id));
1035       jdr_docbuilder.addChild(p_char_item, jdr_docbuilder.JRAD_NS, 'lovMappings',
1036                               lovMap);
1037    --
1038    -- bug 5383667
1039    -- Processing for the Party Name element
1040    -- ntungare
1041    --
1042    ELSIF(p_char_id = qa_ss_const.party_name) THEN
1043       jdr_docbuilder.setAttribute(lovMap, 'lovItem', 'PartyName');
1044       jdr_docbuilder.setAttribute(lovMap, 'requiredForLOV', 'true');
1045       jdr_docbuilder.addChild(p_char_item, jdr_docbuilder.JRAD_NS, 'lovMappings',
1046                               lovMap);
1047 
1048       -- PartyId
1049       lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
1050       jdr_docbuilder.setAttribute(lovMap, 'lovItem', 'PartyId');
1051       jdr_docbuilder.setAttribute(lovMap, 'resultTo', qa_chars_api.hardcoded_column(p_char_id));
1052       jdr_docbuilder.addChild(p_char_item, jdr_docbuilder.JRAD_NS, 'lovMappings',
1053                               lovMap);
1054 
1055    END IF; -- PO Number
1056 
1057 END process_normalized_lov;
1058 
1059     --
1060     -- MOAC Project. 4637896
1061     -- New method to process regular lov item.
1062     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1063     --
1064 -- bug 6884645
1065 -- added a new parameter the processing mode
1066 -- ntungare
1067 --
1068 PROCEDURE process_regular_lov (
1069     p_plan_id                   IN NUMBER,
1070     p_char_id                   IN NUMBER,
1071     p_attribute_code            IN VARCHAR2,
1072     p_char_item                 IN jdr_docbuilder.Element,
1073     p_mode                      IN VARCHAR2 DEFAULT NULL) IS
1074 
1075 BEGIN
1076 
1077     jdr_docbuilder.setAttribute(p_char_item, 'externalListOfValues',
1078                                                 g_jrad_lov_path);
1079 
1080     add_lov_relations(p_plan_id, p_char_id, p_attribute_code, p_char_item, p_mode);
1081 
1082 END process_regular_lov;
1083 
1084 --
1085 -- 12.1 QB Usability Improvements
1086 -- new fuction to check if Online actions
1087 -- have been defined on a collection element
1088 -- ntungare Tue Aug 28 04:34:33 PDT 2007
1089 --
1090 FUNCTION is_online_action_defined(p_plan_id IN NUMBER,
1091                                   p_char_id IN NUMBER)
1092    RETURN NUMBER IS
1093 
1094     -- Cursor to check if Online actions
1095     -- have been defined on a collection
1096     -- element
1097     Cursor online_actions_cur is
1098        (select 1 from
1099           qa_plan_char_action_triggers pcat,
1100           qa_plan_char_actions pca
1101         where pcat.plan_id = p_plan_id
1102           and pcat.plan_char_action_trigger_id = pca.plan_char_action_trigger_id
1103           and action_id in (1, 2, 24)
1104           and pcat.char_id = p_char_id) ;
1105 
1106      cur_val  NUMBER;
1107 BEGIN
1108     open online_actions_cur ;
1109     fetch online_actions_cur into cur_val;
1110     close online_actions_cur;
1111 
1112     RETURN cur_val;
1113 END is_online_action_defined;
1114 
1115 
1116     --
1117     -- MOAC Project. 4637896
1118     -- New method to process lov item.
1119     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1120     --
1121 --
1122 -- bug 6884645
1123 -- passing the mode of processing
1124 -- ntungare Sat Mar 22 08:39:05 PDT 2008
1125 --
1126 PROCEDURE process_messageLovInput (
1127     p_plan_id                   IN NUMBER,
1128     p_char_id                   IN NUMBER,
1129     p_attribute_code            IN VARCHAR2,
1130     p_char_item                 IN jdr_docbuilder.Element,
1131     p_displayed_flag            IN NUMBER,
1132     p_read_only_flag            IN NUMBER,
1133     p_mode                      IN VARCHAR2 DEFAULT NULL) IS
1134 
1135     -- 12.1 QWB Usability Improvements
1136     -- Flag to cehck if Online actions have been defined
1137     -- on the collection element
1138     -- ntungare
1139     --
1140     online_act_flg NUMBER := 0;
1141 
1142 BEGIN
1143    -- in the future, this may be changed to be more generic
1144    -- so that all hardcoded LOVs will go through this
1145    -- process_normalized_lov procedure.  Currently handle
1146    -- PO Number only for the immediate MOAC requirement.
1147     IF is_normalized_lov(p_plan_id,p_char_id) = 'T' THEN
1148          process_normalized_lov(
1149              p_plan_id,
1150              p_char_id,
1151              p_attribute_code,
1152              p_char_item);
1153     ELSE
1154          --
1155          -- bug 6884645
1156          -- passing the processing mode
1157          -- ntungare
1158          --
1159          process_regular_lov(
1160              p_plan_id,
1161              p_char_id,
1162              p_attribute_code,
1163              p_char_item,
1164              p_mode);
1165     END IF;
1166 
1167     -- Common code
1168 
1169     IF (p_displayed_flag <> 1 OR p_read_only_flag = 1) THEN
1170           jdr_docbuilder.setAttribute(p_char_item, 'unvalidated', 'true');
1171     ELSE
1172           jdr_docbuilder.setAttribute(p_char_item, 'unvalidated', 'false');
1173     END IF;
1174 
1175     --
1176     -- 12.1 QWB Usability improvements
1177     -- The user:attribute1 needs to be set which would help in
1178     -- identifying if the LOV event is for a Quality LOV collection
1179     -- element or not. We need to do PPR processing for LOV elements
1180     -- to set the dependent elements and to fire online actions
1181     -- ntungare Sun Oct 14 03:08:47 PDT 2007
1182     --
1183     jdr_docbuilder.setAttribute(p_char_item, 'user:attribute1', 'qapprelement');
1184 
1185     --
1186     -- 12.1 QWB Usability improvements
1187     -- If the LOV has online actions defined,
1188     -- then it should be maked for actions processing
1189     -- So setting the user:attribute2
1190     -- ntungare Sun Oct 14 03:08:47 PDT 2007
1191     --
1192     online_act_flg := is_online_action_defined(p_plan_id => p_plan_id,
1193                                                p_char_id => p_char_id);
1194 
1195     If  (online_act_flg = 1) THEN
1196        jdr_docbuilder.setAttribute(p_char_item, 'user:attribute2', 'qaactionelement');
1197     End If;
1198 
1199 END process_messageLovInput;
1200 
1201 FUNCTION create_region_item_for_eqr (
1202     p_plan_id IN NUMBER,
1203     p_char_id IN NUMBER,
1204     p_element_prefix IN VARCHAR2 DEFAULT g_element_prefix,
1205     p_mode IN VARCHAR2 DEFAULT NULL) RETURN JDR_DOCBUILDER.ELEMENT  IS
1206 
1207     l_attribute_code            VARCHAR2(30);
1208     l_item_style                VARCHAR2(30);
1209     l_vo_attribute_name         VARCHAR2(30);
1210     l_pop_vo_name               VARCHAR2(30);
1211 
1212     c_displayed_flag            VARCHAR2(10);
1213     c_datatype                  VARCHAR2(30);
1214     c_mandatory_flag            VARCHAR2(5);
1215     c_read_only_flag            VARCHAR2(5);
1216 
1217     l_displayed_flag            qa_plan_chars.displayed_flag%TYPE;
1218     l_read_only_flag            qa_plan_chars.read_only_flag%TYPE;
1219     l_datatype                  qa_chars.datatype%TYPE;
1220     l_data_entry_hint           qa_chars.data_entry_hint%TYPE := null;
1221     l_mandatory_flag            qa_plan_chars.mandatory_flag%TYPE;
1222     -- Bug 5926317
1223     -- Changing the length of the local variable l_prompt
1224     -- to a higher value - 100 and commenting out the existing code
1225     -- skolluku Mon Apr  9 04:59:34 PDT 2007
1226     --l_prompt                  qa_plan_chars.prompt%TYPE;
1227     l_prompt                    VARCHAR2(100);
1228 
1229     -- Bug 4506400. New variable.
1230     -- srhariha. Mon Aug 29 05:07:41 PDT 2005.
1231     l_maximum_length  NUMBER;
1232 
1233 
1234     l_char_item jdr_docbuilder.ELEMENT;
1235 
1236     l_api_name constant varchar2(50) := 'CREATE_REGION_ITEM_FOR_EQR';
1237 
1238     -- 12.1 QWB Usability Improvements
1239     -- ntungare Tue Aug 28 04:20:24 PDT 2007
1240     -- PPRTEST
1241     ppr_event  jdr_docbuilder.ELEMENT;
1242 
1243     online_act_flg PLS_INTEGER := 0;
1244 
1245     l_show_required_flag       qa_plan_chars.mandatory_flag%TYPE;
1246     c_show_required_flag       VARCHAR2(5);
1247 BEGIN
1248     l_displayed_flag := qa_plan_element_api.qpc_displayed_flag(p_plan_id, p_char_id);
1249 
1250     -- 12.1 QWB Usability improvements
1251     -- The fields are to be made mandatory only in case of a single
1252     -- row region and not in case of a Multirow region. This is because
1253     -- in case of a multirow region, the mandatory flag against the
1254     -- elements would conflict with the Client side validation in case
1255     -- of inline Txn int. Only the sortable header would be marked as
1256     -- mandatory so that the * mark is displayed against the mandatory
1257     -- fields.
1258     IF (p_mode = g_eqr_advtable_layout OR
1259         p_mode = g_eqr_multiple_layout) THEN
1260         l_mandatory_flag := 2; -- Non mandatory
1261 
1262         -- Although the mandatory check would not be enforced on the
1263         -- collection elements in a Multirow block, yet the show required
1264         -- property would ensure that the mandatory elements are displayed
1265         -- with the mandatory sign. This is needed only for details block
1266         IF (p_element_prefix = g_dtl_element_prefix) THEN
1267            l_show_required_flag := qa_plan_element_api.qpc_mandatory_flag(p_plan_id, p_char_id);
1268            c_show_required_flag := convert_boolean_flag(l_show_required_flag);
1269         END IF;
1270     ELSE
1271         l_mandatory_flag := qa_plan_element_api.qpc_mandatory_flag(p_plan_id, p_char_id);
1272     END IF;
1273 
1274     l_prompt := get_prompt(p_plan_id, p_char_id);
1275     l_datatype := qa_chars_api.datatype(p_char_id);
1276     l_data_entry_hint := qa_chars_api.data_entry_hint(p_char_id);
1277     l_read_only_flag := qa_plan_element_api.qpc_read_only_flag(p_plan_id, p_char_id);
1278 
1279     l_attribute_code := construct_code(p_element_prefix, p_char_id);
1280     l_item_style := compute_item_style(p_plan_id, p_char_id);
1281     l_vo_attribute_name := get_vo_attribute_name(p_plan_id, p_char_id);
1282 
1283     c_displayed_flag  := convert_boolean_flag(l_displayed_flag);
1284     c_mandatory_flag := convert_yesno_flag(l_mandatory_flag);
1285     c_datatype := convert_data_type(l_datatype);
1286 
1287 
1288     if l_datatype = g_seq_datatype then
1289         c_read_only_flag  := 'true';
1290     else
1291         c_read_only_flag  := convert_boolean_flag(l_read_only_flag);
1292     end if;
1293 
1294     l_char_item := create_jrad_region_item(l_item_style);
1295 
1296     jdr_docbuilder.setAttribute(l_char_item, 'id', l_attribute_code);
1297     jdr_docbuilder.setAttribute(l_char_item, 'rendered', c_displayed_flag);
1298     jdr_docbuilder.setAttribute(l_char_item, 'readOnly', c_read_only_flag);
1299     -- if read only elmeent then set style class
1300     if c_read_only_flag = 'true' then
1301        jdr_docbuilder.setAttribute(l_char_item, 'styleClass', g_ora_data_text);
1302     end if;
1303     jdr_docbuilder.setAttribute(l_char_item, 'prompt', l_prompt);
1304     jdr_docbuilder.setAttribute(l_char_item, 'shortDesc', l_prompt);
1305     jdr_docbuilder.setAttribute(l_char_item, 'required', c_mandatory_flag);
1306 
1307     -- 12.1 QWB Usability
1308     -- If the element is in a Multirow details block then
1309     -- although the madatory check would not be enfored yet, the
1310     -- element must atleast be marked as mandatory.
1311     IF ((p_mode = g_eqr_advtable_layout OR p_mode = g_eqr_multiple_layout) AND
1312         (p_element_prefix = g_dtl_element_prefix))THEN
1313        jdr_docbuilder.setAttribute(l_char_item, 'showRequired', c_show_required_flag);
1314     END IF;
1315 
1316     jdr_docbuilder.setAttribute(l_char_item, 'dataType', c_datatype);
1317     -- Advanced Table does not require view name for each item, write code here and other places
1318     -- jdr_docbuilder.setAttribute(l_char_item, 'viewName', g_vo_name);
1319     --if( p_mode <> g_eqr_advtable_layout  OR p_mode is NULL) then
1320         jdr_docbuilder.setAttribute(l_char_item, 'viewName', g_vo_name);
1321     --end if;
1322     jdr_docbuilder.setAttribute(l_char_item, 'viewAttr', l_vo_attribute_name);
1323 
1324     -- Bug 4506400. OA Framework Integration. UT Bug fix.
1325     -- Set maxLength property.
1326     -- srhariha. Mon Aug 29 05:07:41 PDT 2005.
1327     IF (l_item_style IN ('messageTextInput','messageLovInput')) THEN
1328        l_maximum_length := get_max_length(l_vo_attribute_name);
1329        IF (l_maximum_length is not null AND l_maximum_length <> -1) THEN
1330             jdr_docbuilder.setAttribute(l_char_item, 'maximumLength', l_maximum_length);
1331        END IF;
1332     END IF;
1333 
1334     -- At this point, if the element has lovs then we must determine
1335     -- what are its dependency and populate lov_relations
1336     -- with this information.
1337 
1338     --
1339     -- MOAC Project. 4637896
1340     -- Call new method to process lov item.
1341     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1342     --
1343     -- bug 6884645
1344     -- passing the procesing mode
1345     -- ntungare
1346     --
1347     IF (l_item_style = 'messageLovInput' ) THEN
1348         process_messageLovInput(p_plan_id,p_char_id,l_attribute_code,
1349                                   l_char_item,l_displayed_flag,l_read_only_flag, p_mode);
1350     END IF;
1351 
1352     -- Set a few pop list specific properties for poplist chars
1353 
1354     IF (l_item_style = 'messageChoice' ) THEN
1355        -- anagarwa Mon Oct 20 12:47:47 PDT 2003
1356        -- Bug 3202281 . Poplist vo should have plan name .
1357        -- This code has run time dependency on QaRenderCO.java changes
1358         --l_pop_vo_name := construct_code(g_pop_vo_prefix, p_char_id) || 'VO';
1359         l_pop_vo_name := construct_code(g_pop_vo_prefix, p_char_id) || 'PID' || p_plan_id ||  'VO';
1360         jdr_docbuilder.setAttribute(l_char_item, 'pickListViewName', l_pop_vo_name);
1361         jdr_docbuilder.setAttribute(l_char_item, 'pickListDispAttr',
1362                                         g_pop_display_column);
1363         jdr_docbuilder.setAttribute(l_char_item, 'pickListValAttr', g_pop_value_column);
1364     END IF;
1365 
1366     -- Set the text width and height properties for chars of type long comments
1367 
1368     IF (l_datatype = g_comments_datatype) THEN
1369         jdr_docbuilder.setAttribute(l_char_item, 'columns', g_comments_width);
1370         jdr_docbuilder.setAttribute(l_char_item, 'rows', g_comments_height);
1371         -- Bug 4506400. Max length set above. So commenting it out.
1372         -- srhariha. Mon Aug 29 05:07:41 PDT 2005.
1373 
1374         -- jdr_docbuilder.setAttribute(l_char_item, 'maximumLength', g_comments_max_len);
1375     END IF;
1376 
1377     -- set data entry hint
1378     IF (l_data_entry_hint is not null) THEN
1379         jdr_docbuilder.setAttribute(l_char_item, 'tipType', g_tip_type);
1380         --jdr_docbuilder.setAttribute(l_char_item, 'tipMessageName', g_tip_message_name);
1381         --jdr_docbuilder.setAttribute(l_char_item, 'tipMessageAppShortName', g_app_short_name);
1382           jdr_docbuilder.setAttribute(l_char_item, 'longTipRegion', g_long_tip_region);
1383     END IF;
1384 
1385     --
1386     -- 12.1 QWB Usability Improvements
1387     -- Enabling the MessageTextInput and MessageChoice
1388     -- type of collection elements for PPR processing
1389     -- ntungare Tue Aug 28 04:20:24 PDT 2007
1390     --
1391     IF (l_item_style = 'messageTextInput' OR
1392         l_item_style =  'messageChoice') THEN
1393        -- Disabling the serverside validation to avoid the
1394        -- entire row being validated
1395        --
1396        jdr_docbuilder.setAttribute(l_char_item, 'serverUnvalidated', 'true');
1397 
1398        -- Adding the firePartialAction tag
1399        --
1400        ppr_event := JDR_DOCBUILDER.createElement(p_namespace => JDR_DOCBUILDER.UI_NS,
1401                                                  p_tagName   => 'firePartialAction');
1402 
1403        -- Setting the event name for PPR as qappract_CHARIDXX where XX
1404        -- represents the collection elements CharId
1405        --
1406        jdr_docbuilder.setAttribute(ppr_event, 'event', 'qappract_'||l_attribute_code);
1407 
1408        -- Setting the user:attribute1 to indicate the PPR processing is to be
1409        -- done for the element
1410        --
1411        jdr_docbuilder.setAttribute(l_char_item, 'user:attribute1', 'qapprelement');
1412 
1413        -- Disabling the client side validation
1414        --
1415        jdr_docbuilder.setAttribute(ppr_event, 'unvalidated', 'true');
1416 
1417        jdr_docbuilder.addchild (p_parent          => l_char_item,
1418                                 p_groupingNS      => JDR_DOCBUILDER.UI_NS,
1419                                 p_groupingTagName => 'primaryClientAction',
1420                                 p_child           => ppr_event);
1421 
1422 
1423        -- Checking if the Online actions have been
1424        -- defined on the collection element
1425        --
1426        online_act_flg := is_online_action_defined(p_plan_id => p_plan_id,
1427                                                   p_char_id => p_char_id);
1428 
1429        If (online_act_flg =1) then
1430            -- Setting the user:attribute2 since online actions
1431            -- have been defined.
1432            --
1433            jdr_docbuilder.setAttribute(l_char_item, 'user:attribute2', 'qaactionelement');
1434            online_act_flg := 0;
1435        end if; --(online_act_flg =1)
1436     end if; -- (l_item_style = 'messageTextInput' or l_item_style =  'messageChoice')
1437     -- End of changes for PPR
1438 
1439 
1440 
1441     RETURN l_char_item;
1442 
1443 END create_region_item_for_eqr;
1444 
1445 
1446 
1447 FUNCTION create_region_item_for_vqr (
1448     p_plan_id IN NUMBER,
1449     p_char_id IN NUMBER,
1450     p_element_prefix IN VARCHAR2 DEFAULT g_element_prefix,
1451     p_mode IN VARCHAR2 DEFAULT NULL )
1452       RETURN JDR_DOCBUILDER.ELEMENT  IS
1453 
1454     l_attribute_code            VARCHAR2(30);
1455     l_item_style                VARCHAR2(30) DEFAULT 'messageStyledText';
1456     l_vo_attribute_name         VARCHAR2(30);
1457     l_datatype                  qa_chars.datatype%TYPE;
1458     -- Bug 5926317
1459     -- Changing the length of the local variable l_prompt
1460     -- to a higher value - 100 and commenting out the existing code
1461     -- skolluku Mon Apr  9 04:59:34 PDT 2007
1462     --l_prompt                  qa_plan_chars.prompt%TYPE;
1463     l_prompt                    VARCHAR2(100);
1464 
1465     -- Bug 4509114. OA Framework Integration project. UT bug fix.
1466     -- "Displayed Flag" not honoured in VQR. Added the following
1467     -- variables.
1468     -- srhariha. Thu Aug  4 21:04:49 PDT 2005.
1469     l_displayed_flag            qa_plan_chars.displayed_flag%TYPE;
1470     c_displayed_flag            VARCHAR2(10);
1471 
1472 
1473     l_char_item jdr_docbuilder.ELEMENT;
1474 
1475 BEGIN
1476     l_prompt := get_prompt(p_plan_id, p_char_id);
1477     l_attribute_code := construct_code(p_element_prefix, p_char_id);
1478     l_datatype := qa_chars_api.datatype(p_char_id);
1479 
1480     l_char_item := create_jrad_region_item(l_item_style);
1481     -- Bug 4509114. OA Framework Integration project. UT bug fix.
1482     -- "Displayed Flag" not honoured in VQR. Get the displayed flag.
1483     -- srhariha. Thu Aug  4 21:04:49 PDT 2005.
1484     jdr_docbuilder.setAttribute(l_char_item, 'id', l_attribute_code);
1485     --jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'true');
1486     jdr_docbuilder.setAttribute(l_char_item, 'prompt', l_prompt);
1487     jdr_docbuilder.setAttribute(l_char_item, 'shortDesc', l_prompt);
1488 
1489 
1490     -- bug 3236302. rkaza. timezone support. 11/04/2003
1491     -- Added datatype to vqr region items
1492     jdr_docbuilder.setAttribute(l_char_item, 'dataType', convert_data_type(l_datatype));
1493 
1494     -- Bug 4509114. OA Framework Integration project. UT bug fix.
1495     -- "Displayed Flag" not honoured in VQR. Set rendered property
1496     -- based on displayed flag.
1497     -- srhariha. Thu Aug  4 21:04:49 PDT 2005.
1498 
1499     -- 12.1 Usability project
1500     -- rendered attribute and VO attribute logic is different for export page
1501     -- abgangul
1502     if nvl(p_mode, '@') <> g_vqr_multiple_layout then
1503         l_displayed_flag := qa_plan_element_api.qpc_displayed_flag(p_plan_id, p_char_id);
1504         c_displayed_flag  := convert_boolean_flag(l_displayed_flag);
1505         jdr_docbuilder.setAttribute(l_char_item, 'rendered', c_displayed_flag);
1506 
1507         l_vo_attribute_name := get_vo_attribute_name(p_plan_id, p_char_id);
1508         jdr_docbuilder.setAttribute(l_char_item, 'viewName', g_vo_name);
1509         jdr_docbuilder.setAttribute(l_char_item, 'viewAttr', l_vo_attribute_name);
1510         jdr_docbuilder.setAttribute(l_char_item, 'styleClass', g_ora_data_text);
1511 
1512     end if;
1513 
1514     IF (l_datatype = g_comments_datatype) THEN
1515         -- fix me
1516         jdr_docbuilder.setAttribute(l_char_item, 'columns', g_comments_width);
1517         jdr_docbuilder.setAttribute(l_char_item, 'rows', g_comments_height);
1518     END IF;
1519 
1520     -- for vqr set all text to style OraDataText
1521 
1522     RETURN l_char_item;
1523 
1524 END create_region_item_for_vqr;
1525 
1526 
1527 
1528 FUNCTION create_special_region_item (
1529     p_attribute_code           IN VARCHAR2,
1530     p_item_style               IN VARCHAR2,
1531     p_element_prefix           IN VARCHAR2 DEFAULT g_element_prefix,
1532     p_mode                     IN VARCHAR2 DEFAULT NULL)
1533     RETURN jdr_docbuilder.ELEMENT  IS
1534 
1535     l_vo_attribute_name         VARCHAR2(30)  DEFAULT NULL;
1536     l_label_long                VARCHAR2(30)  DEFAULT NULL;
1537     l_special_elem jdr_docbuilder.ELEMENT := NULL;
1538     l_data_type                 VARCHAR2(30);
1539     l_attribute_code            VARCHAR2(30) := p_attribute_code;
1540 
1541     -- for attachments
1542     l_entity_id                 VARCHAR2(30)  DEFAULT NULL;
1543     l_entityMap jdr_docbuilder.ELEMENT := NULL;
1544 
1545 BEGIN
1546 
1547     -- In EQR we add org_id, org_code, plan_id, plan_code, po_agent_id as special items.
1548     -- In VQR, we add created_by, collection_id, last_update_date as special items
1549     -- VQR special items are displayed as message styled text.
1550     -- EQR special items are not displayed.
1551 
1552     l_label_long := get_special_item_label(p_attribute_code);
1553     l_special_elem := create_jrad_region_item(p_item_style);
1554 
1555     -- set properties
1556     -- 12.1 Usability project
1557     -- VO and VO attribute name is different for export page
1558     -- abgangul
1559     if nvl(p_mode, '@') <> g_vqr_multiple_layout then
1560         l_vo_attribute_name := get_hardcoded_vo_attr_name(p_attribute_code);
1561         jdr_docbuilder.setAttribute(l_special_elem, 'viewName', g_vo_name);
1562         jdr_docbuilder.setAttribute(l_special_elem, 'viewAttr', l_vo_attribute_name);
1563         jdr_docbuilder.setAttribute(l_special_elem, 'styleClass', g_ora_data_text);
1564     else
1565         l_attribute_code := p_element_prefix || l_attribute_code;
1566     end if;
1567 
1568     jdr_docbuilder.setAttribute(l_special_elem, 'id', l_attribute_code);
1569     jdr_docbuilder.setAttribute(l_special_elem, 'prompt', l_label_long);
1570 
1571     --
1572     -- Bug 5336860.  Per Coding Standard contextual information needs
1573     -- this statement to set the font.
1574     -- bso Thu Jun 15 17:30:55 PDT 2006
1575     --
1576 
1577     -- bug 3236302. rkaza. timezone support. 11/04/2003
1578     -- Added datatype datetime to last_update_date
1579     IF (p_attribute_code = g_last_update_date_attribute) THEN
1580         -- Assign a datatype of DATETIME to last_update_date.
1581         l_data_type := convert_data_type(g_datetime_datatype);
1582         jdr_docbuilder.setAttribute(l_special_elem, 'dataType', l_data_type);
1583     END IF;
1584 
1585     IF (p_attribute_code = g_collection_id_attribute) THEN
1586         l_data_type := convert_data_type(g_num_datatype);
1587         jdr_docbuilder.setAttribute(l_special_elem, 'dataType', l_data_type);
1588     END IF;
1589 
1590     if p_attribute_code = g_multi_row_attachment then
1591         l_entity_id := g_attachment_entity;
1592         jdr_docbuilder.setAttribute(l_special_elem, 'shortDesc', l_label_long);
1593 
1594         l_entityMap := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS, 'entityMap');
1595         jdr_docbuilder.setAttribute(l_entityMap, 'entityId', l_entity_id);
1596         jdr_docbuilder.addChild(l_special_elem, jdr_docbuilder.OA_NS,
1597                                 'entityMappings', l_entityMap);
1598     end if;
1599 
1600     return l_special_elem;
1601 
1602 END create_special_region_item;
1603 
1604 
1605 -- 12.1 Inline Region Project START
1606 -- new method to add columns in advanced table of inline region
1607 -- based on the plan and added elements
1608 FUNCTION create_item_for_advtable(p_plan_id IN NUMBER,
1609                                   p_char_id IN NUMBER,
1610                                   p_element_prefix IN VARCHAR2 DEFAULT g_element_prefix)
1611     RETURN jdr_docbuilder.element IS
1612 
1613 l_api_name          CONSTANT VARCHAR2(50) := 'CREATE_ITEM_FOR_ADVTABLE';
1614 l_err_num NUMBER;
1615 l_err_msg VARCHAR2(100);
1616 
1617 l_sort_hdr  jdr_docbuilder.element := NULL;
1618 l_col_hdr   jdr_docbuilder.element := NULL;
1619 l_char_item jdr_docbuilder.element := NULL;
1620 
1621 l_attr_code            VARCHAR2(30);
1622 l_col_code             VARCHAR2(30);
1623 l_hrd_code             VARCHAR2(30);
1624 l_prompt               VARCHAR2(30);
1625 l_mode                 VARCHAR2(15) := g_eqr_advtable_layout;
1626 
1627 l_mandatory_flag       qa_plan_chars.mandatory_flag%TYPE;
1628 c_mandatory_flag       VARCHAR2(5);
1629 
1630 l_displayed_flag            qa_plan_chars.displayed_flag%TYPE;
1631 c_displayed_flag            VARCHAR2(10);
1632 BEGIN
1633   log_error(g_pkg_name || l_api_name, 'BEGIN');
1634   l_attr_code := construct_code(p_element_prefix, p_char_id);
1635   l_col_code := 'column' || l_attr_code;
1636   -- create column for the table
1637   -- this is an element from the plan
1638   l_col_hdr := jdr_docbuilder.createElement(jdr_docbuilder.oa_ns,   'column');
1639   jdr_docbuilder.setattribute(l_col_hdr,   'id', l_col_code);
1640 
1641   -- Setting the rendered property based on the display flag value
1642   l_displayed_flag := qa_plan_element_api.qpc_displayed_flag(p_plan_id, p_char_id);
1643   c_displayed_flag  := convert_boolean_flag(l_displayed_flag);
1644 
1645   jdr_docbuilder.setAttribute(l_col_hdr, 'rendered', c_displayed_flag);
1646 
1647   -- add actual element
1648   log_error(g_pkg_name || l_api_name, 'Creating Element ' || to_char(p_char_id));
1649   l_char_item := create_region_item_for_eqr(p_plan_id, p_char_id, p_element_prefix, l_mode);
1650   log_error(g_pkg_name || l_api_name, 'Adding element to column header');
1651   add_child_to_parent(l_col_hdr,   l_char_item,   'contents');
1652   log_error(g_pkg_name || l_api_name, 'END');
1653 
1654   l_hrd_code := 'colHdr' || l_attr_code;
1655   -- add column header
1656   l_sort_hdr := jdr_docbuilder.createElement(jdr_docbuilder.oa_ns,   'sortableHeader');
1657   jdr_docbuilder.setAttribute(l_sort_hdr,   'id', l_hrd_code);
1658   l_prompt := get_prompt(p_plan_id,   p_char_id);
1659   jdr_docbuilder.setAttribute(l_sort_hdr,   'prompt',   l_prompt);
1660 
1661    -- adding the mandatory sign
1662   l_mandatory_flag := qa_plan_element_api.qpc_mandatory_flag(p_plan_id, p_char_id);
1663   c_mandatory_flag := convert_yesno_flag(l_mandatory_flag);
1664   jdr_docbuilder.setAttribute(l_sort_hdr, 'required', c_mandatory_flag);
1665 
1666   add_child_to_parent(l_col_hdr,   l_sort_hdr,   'columnHeader');
1667 
1668 
1669   return l_col_hdr;
1670 
1671 END create_item_for_advtable;
1672 
1673 -- 12.1 Inline Region Project END
1674 
1675 
1676 PROCEDURE delete_old_top_document(
1677     p_full_path IN VARCHAR2) IS
1678 
1679 BEGIN
1680     -- delete the document if it exists.
1681 
1682     If jdr_docbuilder.documentExists(p_full_path) then
1683         jdr_docbuilder.deleteDocument(p_full_path);
1684     end if;
1685 
1686 END delete_old_top_document;
1687 
1688 
1689 
1690 FUNCTION create_top_document(
1691     p_full_path IN VARCHAR2) RETURN JDR_DOCBUILDER.DOCUMENT IS
1692 
1693 BEGIN
1694 
1695     RETURN JDR_DOCBUILDER.createDocument(p_full_path, 'en-US');
1696 
1697 END create_top_document;
1698 
1699 
1700 FUNCTION create_and_set_top_element(
1701     p_top_doc IN JDR_DOCBUILDER.DOCUMENT,
1702     p_top_region_code IN VARCHAR2,
1703     p_layout IN VARCHAR2) RETURN JDR_DOCBUILDER.ELEMENT IS
1704 
1705     l_api_name constant varchar2(50) := 'CREATE_AND_SET_TOP_ELEMENT';
1706     l_top_region JDR_DOCBUILDER.ELEMENT := NULL;
1707     l_prompt VARCHAR2(30);
1708 BEGIN
1709     -- Creates the top region and sets it as the top level element in the document
1710     -- p_layout can be stackLayout or table layout for top regions
1711 
1712     -- Bug 4506769. OA Framework Integration. UT bug fix.
1713     -- Personalization text incorrect.
1714     -- srhariha. Fri Aug 26 05:13:34 PDT 2005.
1715 
1716     if (p_layout = 'table') then
1717         l_prompt := get_region_prompt('DATA');
1718     -- 12.1 Inline region project
1719     -- saugupta
1720     elsif ( p_layout = 'advancedTable' ) then
1721       l_prompt := get_region_prompt('DATA');
1722     end if;
1723 
1724     l_top_region := create_jrad_region (p_top_region_code, p_layout,l_prompt, null);
1725     JDR_DOCBUILDER.setTopLevelElement(p_top_doc, l_top_region);
1726 
1727     -- 12.1 Inline region project
1728     -- saugupta
1729     if ( p_layout = 'advancedTable' ) then
1730       -- found code bug, regionName appearing twice in the final XML
1731       -- saugupta
1732       -- JDR_DOCBUILDER.setAttribute(l_top_region, 'regionName', l_prompt);
1733       JDR_DOCBUILDER.setAttribute(l_top_region, 'viewName', g_vo_name);
1734       -- JDR_DOCBUILDER.setAttribute(l_top_region, 'detailViewAttr', 'HideShowStatus');
1735     end if;
1736 
1737     RETURN l_top_region;
1738 
1739 END create_and_set_top_element;
1740 
1741     --
1742     -- MOAC Project. 4637896
1743     -- Rewrote the code for MOAC.
1744     -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1745     --
1746 
1747 
1748 -- Returns no of plan chars added to the region
1749 
1750 FUNCTION add_plan_chars_to_region(
1751     p_plan_id IN NUMBER,
1752     p_content_region IN JDR_DOCBUILDER.ELEMENT,
1753     p_mode IN VARCHAR2,
1754     p_char_type IN VARCHAR2,
1755     p_parent_region_prefix IN VARCHAR2 DEFAULT NULL) RETURN NUMBER IS
1756 
1757     char_count NUMBER := 0;
1758     l_char_item JDR_DOCBUILDER.ELEMENT := NULL;
1759     -- MOAC
1760     l_id_item JDR_DOCBUILDER.ELEMENT := NULL;
1761 
1762     l_element_prefix VARCHAR2(15) := g_element_prefix;
1763 
1764     l_api_name          CONSTANT VARCHAR2(50) := 'ADD_PLAN_CHARS_TO_REGION';
1765 
1766     --
1767     -- Bug 9381413
1768     -- New boolean variables which will be set to true if the formValue
1769     -- for PO Number or Party Name needs to be added. These bool variables
1770     -- will be used to add the formValue clause at the end of the region rather
1771     -- than immediately after the respective elements, as it causes the sequence
1772     -- of other elements to be displayed incorrectly.
1773     -- skolluku
1774     --
1775     l_po_num_exists    BOOLEAN := false;
1776     l_party_exists     BOOLEAN := false;
1777 
1778     --
1779     -- Bug 9954346
1780     -- Order by Display flag too, so that non displayed elements
1781     -- will be added in the end.
1782     -- skolluku
1783     --
1784     CURSOR c IS
1785         SELECT   qpc.char_id
1786         FROM     qa_plan_chars qpc,
1787                  qa_chars qc
1788         WHERE    qc.char_id = qpc.char_id
1789         AND      qpc.enabled_flag = 1
1790         AND      qpc.plan_id = p_plan_id
1791         AND      ((p_char_type = 'NonComments' AND qc.datatype <> g_comments_datatype) OR
1792                   (p_char_type = 'Comments' AND qc.datatype = g_comments_datatype))
1793         ORDER BY qpc.displayed_flag, QPC.prompt_sequence;
1794 
1795 
1796 
1797 BEGIN
1798     IF p_parent_region_prefix = g_eqr_mult_dtl_prefix THEN
1799       l_element_prefix := g_dtl_element_prefix;
1800     END IF;
1801 
1802 
1803     -- p_mode can be EQR, VQR. Attribute properties are different for the modes
1804     -- p_char_type can be NonComments and Comments.
1805     -- 'NonComments' is passed in when processing data region.
1806     -- 'Comments' is passed in when processing comments region.
1807 
1808     FOR r in c LOOP
1809         IF p_mode = g_vqr_single_layout THEN
1810             l_char_item := create_region_item_for_vqr(p_plan_id,r.char_id,
1811                                                       l_element_prefix);
1812             add_child_to_parent(p_content_region, l_char_item, 'contents');
1813         -- 12.1 Inline Region Project
1814         -- saugupta
1815         ELSIF p_mode = g_eqr_advtable_layout THEN
1816             log_error(g_pkg_name || l_api_name,
1817                        'Creating adv table item for: ' || to_char(r.char_id));
1818             l_char_item := create_item_for_advtable(p_plan_id, r.char_id, l_element_prefix);
1819             add_child_to_parent(p_content_region, l_char_item, 'contents');
1820         ELSE
1821             l_char_item := create_region_item_for_eqr(p_plan_id, r.char_id,
1822                                                       l_element_prefix, p_mode);
1823             add_child_to_parent(p_content_region, l_char_item, 'contents');
1824              -- Bug 4691416. MOAC project. UT bug fix.
1825              -- Dont add id field to multi detail region.
1826              -- srhariha. Thu Oct 20 22:18:41 PDT 2005.
1827 
1828             -- For MOAC : add normalized column.
1829             --
1830             -- bug 5383667
1831             -- Added the conditon for Party name
1832             -- ntungare
1833             --
1834             -- Requires code addition for Adanced Table
1835             -- todo saugupta
1836             IF (r.char_id = qa_ss_const.po_number OR
1837                 r.char_id = qa_ss_const.party_name) AND
1838                      (p_parent_region_prefix <> g_eqr_mult_dtl_prefix OR
1839                       p_parent_region_prefix IS NULL) THEN
1840               -- Bug 9381413
1841               -- Added IF-ESLIF ladder to set the bool variables appropriately and
1842               -- moved the existing code outside the loop.
1843               -- skolluku
1844               IF (r.char_id = qa_ss_const.po_number) THEN
1845                   l_po_num_exists := true;
1846               ELSIF (r.char_id = qa_ss_const.party_name) THEN
1847                   l_party_exists := true;
1848               END IF;
1849               -- l_id_item := create_id_item_for_eqr(p_plan_id,r.char_id);
1850               -- add_child_to_parent(p_content_region, l_id_item, 'contents');
1851             END IF;
1852         END IF;
1853         char_count := char_count + 1;
1854     END LOOP;
1855 
1856     -- Bug  9381413
1857     -- Add the form values for PO Number and Party Name, if required.
1858     -- skolluku
1859     IF (l_po_num_exists) THEN
1860        l_id_item := create_id_item_for_eqr(p_plan_id,qa_ss_const.po_number);
1861        add_child_to_parent(p_content_region, l_id_item, 'contents');
1862     END IF;
1863 
1864     IF (l_party_exists) THEN
1865        l_id_item := create_id_item_for_eqr(p_plan_id,qa_ss_const.party_name);
1866        add_child_to_parent(p_content_region, l_id_item, 'contents');
1867     END IF;
1868 
1869     RETURN char_count;
1870 
1871 END add_plan_chars_to_region;
1872 
1873 
1874 
1875 PROCEDURE add_special_chars_to_region(
1876     p_plan_id IN NUMBER,
1877     p_content_region IN JDR_DOCBUILDER.ELEMENT,
1878     p_mode IN VARCHAR2,
1879     p_parent_region_prefix IN VARCHAR2 DEFAULT NULL) IS
1880 
1881     l_char_item JDR_DOCBUILDER.ELEMENT := NULL;
1882     l_item_style VARCHAR2(30) := null;
1883     l_element_prefix VARCHAR2(15) := g_element_prefix;
1884 BEGIN
1885 
1886     IF p_parent_region_prefix = g_eqr_mult_dtl_prefix THEN
1887       l_element_prefix := g_dtl_element_prefix;
1888     END IF;
1889 
1890     -- p_mode can be EQR or VQR.
1891     -- if EQR
1892     -- add special chars like org_id, org_code, plan_id, plan_name, po_agent_id
1893     -- I am not adding process_status, source_code, source_line_id. They were used
1894     -- in older versions when self service results were processed as in import.
1895     -- if VQR add created by, collection id, last update date.
1896 
1897     If p_mode = g_eqr_single_layout then
1898 
1899         l_item_style := 'formValue';
1900 
1901         l_char_item := create_special_region_item (
1902             p_attribute_code           => g_org_id_attribute,
1903             p_item_style               => l_item_style,
1904             p_element_prefix           => l_element_prefix);
1905         add_child_to_parent(p_content_region, l_char_item, 'contents');
1906 
1907         l_char_item := create_special_region_item (
1908             p_attribute_code           => g_plan_id_attribute,
1909             p_item_style               => l_item_style,
1910             p_element_prefix           => l_element_prefix);
1911         add_child_to_parent(p_content_region, l_char_item, 'contents');
1912 
1913     elsif p_mode = g_eqr_multiple_layout then
1914 
1915         l_item_style := 'formValue';
1916 
1917         l_char_item := create_special_region_item (
1918             p_attribute_code           => g_org_id_attribute,
1919             p_item_style               => l_item_style,
1920             p_element_prefix           => l_element_prefix);
1921         add_child_to_parent(p_content_region, l_char_item, 'contents');
1922 
1923         l_char_item := create_special_region_item (
1924             p_attribute_code           => g_plan_id_attribute,
1925             p_item_style               => l_item_style,
1926             p_element_prefix           => l_element_prefix);
1927         add_child_to_parent(p_content_region, l_char_item, 'contents');
1928 
1929         l_item_style := 'attachmentImage';
1930 
1931         l_char_item := create_special_region_item (
1932             p_attribute_code           => g_multi_row_attachment,
1933             p_item_style               => l_item_style,
1934             p_element_prefix           => l_element_prefix);
1935         add_child_to_parent(p_content_region, l_char_item, 'contents');
1936 
1937     elsif p_mode = g_vqr_single_layout then
1938 
1939         l_item_style := 'messageStyledText';
1940 
1941         l_char_item := create_special_region_item (
1942             p_attribute_code           => g_qa_created_by_attribute,
1943             p_item_style               => l_item_style,
1944             p_element_prefix           => l_element_prefix);
1945         add_child_to_parent(p_content_region, l_char_item, 'contents');
1946 
1947         l_char_item := create_special_region_item (
1948             p_attribute_code           => g_collection_id_attribute,
1949             p_item_style               => l_item_style,
1950             p_element_prefix           => l_element_prefix);
1951         add_child_to_parent(p_content_region, l_char_item, 'contents');
1952 
1953         l_char_item := create_special_region_item (
1954             p_attribute_code           => g_last_update_date_attribute,
1955             p_item_style               => l_item_style,
1956             p_element_prefix           => l_element_prefix);
1957         add_child_to_parent(p_content_region, l_char_item, 'contents');
1958 
1959     end if;
1960 
1961 END add_special_chars_to_region;
1962 
1963 
1964 
1965 FUNCTION create_data_region(
1966     p_plan_id IN NUMBER,
1967     p_data_region_code IN VARCHAR2,
1968     p_mode IN VARCHAR2,
1969     p_parent_region_prefix IN VARCHAR2 DEFAULT NULL) RETURN JDR_DOCBUILDER.ELEMENT IS
1970 
1971     l_data_region JDR_DOCBUILDER.ELEMENT := NULL;
1972     l_prompt VARCHAR2(30) := null;
1973     l_char_count NUMBER := null;
1974 
1975 BEGIN
1976     -- create data region
1977     -- loop thru enabled plan elements that are not of type 'comment'
1978     -- create element region items and add to data region
1979     -- add special chars to the data region
1980     -- return data region
1981     -- p_mode can be EQR or VQR.
1982 
1983 
1984     if p_mode = g_eqr_multiple_layout then
1985         l_prompt := null;
1986     else
1987         l_prompt := get_region_prompt('DATA');
1988     end if;
1989 
1990 
1991 /*
1992     l_data_region := create_jrad_region(p_data_region_code, 'defaultDoubleColumn',
1993                         l_prompt);
1994 */
1995     if p_parent_region_prefix = g_eqr_mult_dtl_prefix then
1996       l_data_region := create_jrad_region(p_data_region_code, 'labeledFieldLayout',
1997                         l_prompt, '2');
1998     else
1999       l_data_region := create_jrad_region(p_data_region_code, 'defaultDoubleColumn',
2000                        l_prompt, '-1');
2001     end if;
2002 
2003      l_char_count := add_plan_chars_to_region(p_plan_id, l_data_region,
2004                                              p_mode, 'NonComments',
2005                                              p_parent_region_prefix);
2006      IF p_parent_region_prefix is null or
2007          p_parent_region_prefix <> g_eqr_mult_dtl_prefix THEN
2008       add_special_chars_to_region(p_plan_id, l_data_region, p_mode,
2009                                 p_parent_region_prefix);
2010     END IF;
2011 
2012     RETURN l_data_region;
2013 
2014 END create_data_region;
2015 
2016 
2017 
2018 FUNCTION create_comments_region(
2019     p_plan_id IN NUMBER,
2020     p_comments_region_code IN VARCHAR2,
2021     p_mode IN VARCHAR2,
2022     p_parent_region_prefix IN VARCHAR2 DEFAULT NULL)
2023     RETURN JDR_DOCBUILDER.ELEMENT IS
2024 
2025     l_comments_region JDR_DOCBUILDER.ELEMENT := NULL;
2026     l_prompt VARCHAR2(30) := null;
2027     l_char_count NUMBER := null;
2028 
2029 BEGIN
2030     -- create comments region
2031     -- loop thru enabled plan elements that are of type 'comment'
2032     -- create element region items and add to comments region
2033     -- return comments region
2034     -- p_mode can be EQR or VQR.
2035 
2036     if p_mode = g_eqr_multiple_layout then
2037         l_prompt := null;
2038     else
2039         l_prompt := get_region_prompt('COMMENTS');
2040     end if;
2041 
2042     if p_parent_region_prefix = g_eqr_mult_dtl_prefix then
2043        l_comments_region := create_jrad_region(p_comments_region_code,
2044                                 'labeledFieldLayout', l_prompt, '1');
2045     else
2046        l_comments_region := create_jrad_region(p_comments_region_code,
2047                                 'defaultSingleColumn', l_prompt, '-1');
2048     end if;
2049 
2050     l_char_count := add_plan_chars_to_region(p_plan_id,
2051                                 l_comments_region, p_mode, 'Comments',
2052                                  p_parent_region_prefix);
2053 
2054     if l_char_count = 0 then
2055         l_comments_region.id := null;
2056     end if;
2057 
2058     RETURN l_comments_region;
2059 
2060 END create_comments_region;
2061 
2062 
2063 FUNCTION create_attachments_region(p_plan_id IN NUMBER,
2064                                    p_mode  IN VARCHAR2)
2065     RETURN JDR_DOCBUILDER.ELEMENT IS
2066 
2067     l_comments_region JDR_DOCBUILDER.ELEMENT := NULL;
2068     l_prompt VARCHAR2(30) := null;
2069     l_char_count NUMBER := null;
2070 
2071     l_row_id                    VARCHAR2(30);
2072     l_element_id                NUMBER;
2073     l_region_code               VARCHAR2(30);
2074     --l_nested_region_code      VARCHAR2(30)  DEFAULT null;
2075     l_item_style                VARCHAR2(30)  DEFAULT 'formValue';
2076     --l_display_sequence                NUMBER;
2077     --l_display_flag            VARCHAR2(1)   DEFAULT 'Y';
2078     --l_update_flag             VARCHAR2(1)   DEFAULT 'Y';
2079     l_view_attribute_name       VARCHAR2(30)  DEFAULT NULL;
2080     l_view_usage_name           VARCHAR2(30)  DEFAULT NULL;
2081     l_label_long                VARCHAR2(30)  DEFAULT NULL;
2082     l_entity_id                 VARCHAR2(30)  DEFAULT NULL;
2083     l_url                       VARCHAR2(240) DEFAULT NULL;
2084     l_image_file_name           VARCHAR2(240) DEFAULT NULL;
2085     l_description               VARCHAR2(240) DEFAULT NULL;
2086     --l_query_flag              VARCHAR2(1)   DEFAULT 'N';
2087 
2088     special_elem jdr_docbuilder.ELEMENT := NULL;
2089     l_entityMap jdr_docbuilder.ELEMENT := NULL;
2090 
2091     err_num                     NUMBER;
2092     err_msg                     VARCHAR2(100);
2093 l_attachments_region JDR_DOCBUILDER.ELEMENT := NULL;
2094 
2095 
2096 BEGIN
2097     -- create comments region
2098     -- loop thru enabled plan elements that are of type 'comment'
2099     -- create element region items and add to comments region
2100     -- return comments region
2101     -- p_mode can be EQR or VQR.
2102 
2103     l_prompt := get_region_prompt('ATTACHMENTS');
2104 
2105     l_attachments_region := create_jrad_region('QA_SSQR_E_ATTACHMENTS',
2106                                 'defaultSingleColumn', l_prompt, '-1');
2107     -- added for attachments
2108     l_entity_id := 'QA_RESULTS';
2109 
2110     l_view_attribute_name := '';
2111     l_label_long := fnd_message.get_string('QA', 'QA_SS_JRAD_ATTACHMENT');
2112     l_description := l_label_long;
2113 
2114     --l_item_style := 'attachmentImage';
2115     l_item_style := 'attachmentLink';
2116     --special handling for attachments
2117     l_entityMap := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS,
2118                                         'entityMap');
2119     jdr_docbuilder.setAttribute(l_entityMap, 'entityId', l_entity_id);
2120     --
2121     -- Bug 8671769
2122     -- Adding 'id' for the entity map so that the personalization works perfectly.
2123     -- skolluku
2124     --
2125     jdr_docbuilder.setAttribute(l_entityMap, 'id', 'qaEntityMap1');
2126 
2127     -- Bug 6718507
2128     -- in VQR we do not want the user to insert or update Attachements
2129     -- ntungare Wed Jan 23 04:20:57 PST 2008
2130     --
2131     IF (p_mode = g_vqr_single_layout) THEN
2132        jdr_docbuilder.setAttribute(l_entityMap, 'insertAllowed', 'false' );
2133        jdr_docbuilder.setAttribute(l_entityMap, 'updateAllowed', 'false' );
2134        jdr_docbuilder.setAttribute(l_entityMap, 'deleteAllowed', 'false' );
2135     END IF;
2136 
2137     special_elem := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS,
2138                                                   l_item_style);
2139     jdr_docbuilder.setAttribute(special_elem, 'id', 'AK_ATTACHMENT_LINK' );
2140     jdr_docbuilder.setAttribute(special_elem, 'viewName', 'QualityResultsVO' );
2141 
2142     -- in VQR we do not want to user to insert or update Attachements
2143     IF (p_mode = g_vqr_single_layout) THEN
2144        jdr_docbuilder.setAttribute(special_elem, 'insertAllowed', 'false' );
2145        jdr_docbuilder.setAttribute(special_elem, 'updateAllowed', 'false' );
2146        jdr_docbuilder.setAttribute(special_elem, 'deleteAllowed', 'false' );
2147 
2148 
2149     END IF;
2150 
2151 
2152     jdr_docbuilder.addChild(special_elem, jdr_docbuilder.OA_NS,
2153                                 'entityMappings', l_entityMap);
2154 
2155 JDR_DOCBUILDER.addChild(l_attachments_region,  JDR_DOCBUILDER.UI_NS,
2156                                 'contents', special_elem);
2157 
2158 /*
2159     IF ( instr(p_prefix, g_vqr_prefix) = 1) THEN
2160        --l_update_flag := 'N';
2161        null;
2162     END IF;
2163 */
2164 
2165     RETURN l_attachments_region;
2166 
2167 END create_attachments_region;
2168 
2169 --12.1 Inline Project Start
2170 
2171 FUNCTION create_detail_region(
2172     p_plan_id IN NUMBER,
2173     p_dtl_region_code IN VARCHAR2,
2174     p_mode IN VARCHAR2) RETURN JDR_DOCBUILDER.ELEMENT IS
2175 
2176     l_data_region_code VARCHAR2(35);
2177     l_comments_region_code VARCHAR2(35);
2178 
2179     l_dtl_region JDR_DOCBUILDER.ELEMENT := NULL;
2180     l_data_region JDR_DOCBUILDER.ELEMENT := NULL;
2181     l_comments_region JDR_DOCBUILDER.ELEMENT := NULL;
2182 
2183     l_prompt VARCHAR2(30) := null;
2184     l_char_count NUMBER := null;
2185 
2186 BEGIN
2187     -- create data region
2188     -- loop thru enabled plan elements that are not of type 'comment'
2189     -- create element region items and add to data region
2190     -- add special chars to the data region
2191     -- return data region
2192     -- p_mode can be EQR or VQR.
2193 
2194     l_prompt := null;
2195     l_data_region_code := construct_code(g_eqr_data_prefix, p_plan_id);
2196     l_comments_region_code := construct_code(g_eqr_comments_prefix, p_plan_id);
2197 
2198     l_dtl_region := create_jrad_region(p_dtl_region_code, 'stackLayout',
2199                         l_prompt, '-1');
2200 
2201     -- create data and comments regions and add them as children to the detail region
2202 
2203     l_data_region := create_data_region(p_plan_id, l_data_region_code, p_mode, g_eqr_mult_dtl_prefix);
2204     add_child_to_parent(l_dtl_region, l_data_region, 'contents');
2205 
2206     l_comments_region := create_comments_region(p_plan_id, l_comments_region_code,
2207                                                 p_mode, g_eqr_mult_dtl_prefix);
2208     if l_comments_region.id is not null then
2209         add_child_to_parent(l_dtl_region, l_comments_region, 'contents');
2210     end if;
2211 
2212     RETURN l_dtl_region;
2213 
2214 END create_detail_region;
2215 
2216 -- create table action region containing buttons like duplicate and delete
2217 FUNCTION create_table_action_region RETURN jdr_docbuilder.element IS
2218 
2219 l_row_layout jdr_docbuilder.element := NULL;
2220 l_text       jdr_docbuilder.element := NULL;
2221 l_dup_btn    jdr_docbuilder.element := NULL;
2222 l_del_btn    jdr_docbuilder.element := NULL;
2223 
2224 l_attr_code            VARCHAR2(30);
2225 
2226 l_api_name          CONSTANT VARCHAR2(50) := 'CREATE_TABLE_ACTION_REGION';
2227 l_label                  VARCHAR2(30);
2228 l_prompt                 VARCHAR2(50);
2229 l_del_attr_set      CONSTANT VARCHAR2(50) := '/oracle/apps/fnd/attributesets/Buttons/Delete';
2230 l_dup_attr_set      CONSTANT VARCHAR2(50) := '/oracle/apps/fnd/attributesets/Buttons/Duplicate';
2231 
2232 
2233 BEGIN
2234   -- create tableAction region items
2235   log_error(g_pkg_name || l_api_name, 'Function BEGIN');
2236   -- create rowLayout
2237   l_row_layout := create_jrad_region_item('rowLayout');
2238   jdr_docbuilder.setattribute(l_row_layout,   'id', 'rowLayoutRN'); -- set proper non static id
2239 
2240   -- create display text item
2241   l_text := create_jrad_region_item('messageStyledText');
2242   jdr_docbuilder.setattribute(l_text,   'id',   'displayText');
2243   l_label := fnd_message.get_string('QA','QA_TABLE_SELECT_MSG');
2244   jdr_docbuilder.setattribute(l_text,   'prompt', l_label);
2245 
2246   --create button
2247   l_dup_btn := create_jrad_region_item('submitButton');
2248   jdr_docbuilder.setattribute(l_dup_btn,   'id',   'dupBtn');
2249 
2250   -- do not set prompt with '&' instead use attribute sets
2251   -- l_prompt :=  fnd_message.get_string('QA','QA_QWB_DUPLICATE_PROMPT');
2252   -- jdr_docbuilder.setattribute(l_dup_btn,   'prompt', DBMS_XMLGEN.convert(l_prompt));
2253   jdr_docbuilder.setattribute(l_dup_btn,   'use', l_dup_attr_set);
2254 
2255   jdr_docbuilder.setattribute(l_dup_btn,  'unvalidated', 'true');
2256 
2257   --create button
2258   l_del_btn := create_jrad_region_item('submitButton');
2259   jdr_docbuilder.setattribute(l_del_btn,   'id',   'delBtn');
2260 
2261   -- do not set prompt with '&' instead use attribute sets
2262   -- l_prompt :=  fnd_message.get_string('QA','QA_QWB_DELETE_PROMPT');
2263   --  jdr_docbuilder.setattribute(l_del_btn,   'prompt',   DBMS_XMLGEN.convert(l_prompt) );
2264   jdr_docbuilder.setattribute(l_del_btn,   'use', l_del_attr_set);
2265 
2266   jdr_docbuilder.setattribute(l_del_btn,  'unvalidated', 'true');
2267   -- Bug 6856743 - Set server side validation to false.
2268   jdr_docbuilder.setattribute(l_del_btn,  'serverUnvalidated', 'True');
2269 
2270   -- add items to region
2271   add_child_to_parent(l_row_layout,   l_text,   'contents');
2272   add_child_to_parent(l_row_layout,   l_dup_btn,   'contents');
2273   add_child_to_parent(l_row_layout,   l_del_btn,   'contents');
2274 
2275   -- rowLayout should be added as a child to table or advanced table
2276   log_error(g_pkg_name || l_api_name, 'Function END');
2277   RETURN l_row_layout;
2278 
2279 END create_table_action_region;
2280 
2281 -- create table single selection region with radio button
2282 FUNCTION create_table_selection RETURN jdr_docbuilder.element IS
2283 
2284 l_sing_select jdr_docbuilder.element := NULL;
2285 l_api_name          CONSTANT VARCHAR2(50) := 'CREATE_TABLE_SELECTION';
2286 
2287 -- 12.1 Device Integration Project
2288 -- bhsankar Tue Aug 28 04:20:24 PDT 2007
2289 -- For PPR Event
2290 ppr_event  jdr_docbuilder.ELEMENT;
2291 
2292 BEGIN
2293   -- create tableSelection
2294   log_error(g_pkg_name || l_api_name, 'Function BEGIN');
2295   -- create singleSelection
2296   l_sing_select := create_jrad_region_item('singleSelection');
2297   jdr_docbuilder.setattribute(l_sing_select,   'id', 'ResultSelection');
2298   jdr_docbuilder.setattribute(l_sing_select,   'text', ''); -- get it from message
2299   jdr_docbuilder.setattribute(l_sing_select,   'viewAttr', 'SelectFlag');
2300   jdr_docbuilder.setAttribute(l_sing_select, 'unvalidated', 'true');
2301   jdr_docbuilder.setAttribute(l_sing_select, 'serverUnvalidated', 'true');
2302 
2303   -- 12.1 Device Integration Project
2304   -- bhsankar Tue Aug 28 04:20:24 PDT 2007
2305   -- For PPR Event
2306   ppr_event := JDR_DOCBUILDER.createElement(p_namespace => JDR_DOCBUILDER.UI_NS,
2307                                             p_tagName   => 'firePartialAction');
2308   jdr_docbuilder.setAttribute(ppr_event, 'event', 'qaselect');
2309   jdr_docbuilder.setAttribute(ppr_event, 'unvalidated', 'true');
2310   jdr_docbuilder.addchild(p_parent          => l_sing_select,
2311                           p_groupingNS      => JDR_DOCBUILDER.UI_NS,
2312                           p_groupingTagName => 'primaryClientAction',
2313                           p_child           => ppr_event);
2314   -- Device Integration Project End.
2315 
2316   -- add items to region
2317   -- add it in plan mapping procedure
2318   -- add_child_to_parent(l_sing_select,   l_text,   'tableSelection');
2319 
2320   -- rowLayout should be added as a child to table or advanced table
2321   log_error(g_pkg_name || l_api_name, 'Function END');
2322   RETURN l_sing_select;
2323 
2324 END create_table_selection;
2325 
2326 -- create table single selection region with radio button
2327 FUNCTION create_table_footer RETURN jdr_docbuilder.element IS
2328 
2329 l_add_btn jdr_docbuilder.element := NULL;
2330 l_footer  jdr_docbuilder.element := NULL;
2331 l_api_name          CONSTANT VARCHAR2(50) := 'CREATE_TABLE_FOOTER';
2332 l_prompt                 VARCHAR2(50);
2333 
2334 BEGIN
2335   -- create tableSelection
2336   log_error(g_pkg_name || l_api_name, 'Function BEGIN');
2337   -- create Button
2338   l_add_btn := create_jrad_region_item('addTableRow');
2339   jdr_docbuilder.setattribute(l_add_btn,   'id', 'addResultRow');
2340   jdr_docbuilder.setattribute(l_add_btn,   'autoInsertion', 'false');
2341 
2342   --  l_prompt :=  fnd_message.get_string('QA','QA_QWB_ADD_PROMPT');
2343   --  jdr_docbuilder.setattribute(l_add_btn, 'prompt',  DBMS_XMLGEN.convert(l_prompt));
2344 
2345   l_footer := create_jrad_region_item('tableFooter');
2346   jdr_docbuilder.setattribute(l_footer,   'id', 'resultFooter');
2347 
2348   -- add button to footer
2349   add_child_to_parent(l_footer,   l_add_btn,   'contents');
2350 
2351   -- rowLayout should be added as a child to table or advanced table
2352   log_error(g_pkg_name || l_api_name, 'Function END');
2353   RETURN l_footer;
2354 
2355 END create_table_footer;
2356 
2357 -- add table action to table or advanced table
2358 PROCEDURE add_table_action(p_prnt_elm IN jdr_docbuilder.element ) IS
2359   l_tbl_act jdr_docbuilder.element := NULL;
2360 BEGIN
2361   -- add table actions to table or advanced table
2362   l_tbl_act     := create_table_action_region;
2363   add_child_to_parent(p_prnt_elm,   l_tbl_act,   'tableActions');
2364 END add_table_action;
2365 
2366 
2367 -- add table single selection to table or advanced table
2368 PROCEDURE add_table_selection(p_prnt_elm IN jdr_docbuilder.element )IS
2369   l_tbl_s_sel  jdr_docbuilder.element := NULL;
2370 BEGIN
2371   -- add ingle selection  to table or advanced table
2372   l_tbl_s_sel   := create_table_selection;
2373   add_child_to_parent(p_prnt_elm,   l_tbl_s_sel,   'tableSelection');
2374 END add_table_selection;
2375 
2376 
2377 -- add footer to advanced table
2378 PROCEDURE add_table_footer(p_prnt_elm IN jdr_docbuilder.element) IS
2379 l_tbl_footer jdr_docbuilder.element := NULL;
2380 BEGIN
2381   -- add footer to table or advanced table
2382   l_tbl_footer  := create_table_footer;
2383   add_child_to_parent(p_prnt_elm,  l_tbl_footer,   'footer');
2384 END add_table_footer;
2385 
2386 --12.1 Inline Project End
2387 
2388 --
2389 -- 12.1 Device Integration Project
2390 -- Functions to create the device region
2391 -- bhsankar Wed Oct 24 04:45:16 PDT 2007
2392 --
2393 
2394 PROCEDURE add_device_checkbox_to_parent(
2395     p_plan_id IN NUMBER,
2396     p_parent_region JDR_DOCBUILDER.ELEMENT) IS
2397 
2398     l_api_name          CONSTANT VARCHAR2(50) := 'ADD_DEVICE_CHECKBOX_TO_PARENT';
2399     l_messageCompLayout JDR_DOCBUILDER.ELEMENT := NULL;
2400     l_char_item JDR_DOCBUILDER.ELEMENT := NULL;
2401     l_prompt VARCHAR2(100);
2402     l_element_prefix VARCHAR2(15) := g_element_prefix;
2403     l_element_suffix VARCHAR2(15) := g_device_element_suffix;
2404     l_attribute_code VARCHAR2(30);
2405 
2406     CURSOR c IS
2407         SELECT   qpc.char_id
2408         FROM     qa_plan_chars qpc,
2409                  qa_chars qc,
2410                  qa_device_info qdi
2411         WHERE    qc.char_id = qpc.char_id
2412         AND      nvl(qpc.enabled_flag, 2) = 1
2413         AND      nvl(qpc.device_flag, 2) = 1
2414         AND      nvl(qpc.displayed_flag, 2) = 1
2415         AND      qdi.device_id = qpc.device_id
2416         AND      qdi.enabled_flag = 1
2417         AND      qpc.plan_id = p_plan_id
2418         ORDER BY qpc.prompt_sequence;
2419 
2420 BEGIN
2421    log_error(g_pkg_name || l_api_name, 'Procedure BEGIN');
2422    l_messageCompLayout := JDR_DOCBUILDER.createElement(JDR_DOCBUILDER.OA_NS, 'messageComponentLayout');
2423    jdr_docbuilder.setAttribute(l_messageCompLayout, 'id', 'ChkBoxRN');
2424    jdr_docbuilder.setAttribute(l_messageCompLayout, 'rows', '1');
2425    jdr_docbuilder.setAttribute(l_messageCompLayout, 'columns', '4');
2426    log_error(g_pkg_name || l_api_name, 'message componenet layput created');
2427 
2428    FOR r in c LOOP
2429       l_char_item := create_jrad_region_item('messageCheckBox');
2430       l_prompt := get_prompt(p_plan_id, r.char_id);
2431       l_attribute_code := construct_code(l_element_prefix, r.char_id, l_element_suffix);
2432 
2433       jdr_docbuilder.setAttribute(l_char_item, 'id', l_attribute_code);
2434       jdr_docbuilder.setAttribute(l_char_item, 'prompt', l_prompt);
2435       jdr_docbuilder.setAttribute(l_char_item, 'unvalidated', 'true');
2436       jdr_docbuilder.setAttribute(l_char_item, 'serverUnvalidated', 'true');
2437       jdr_docbuilder.setAttribute(l_char_item, 'checked', 'true');
2438       add_child_to_parent(l_messageCompLayout, l_char_item,'contents');
2439       log_error(g_pkg_name || l_api_name, 'checkbox created for element: ' || l_prompt);
2440    END LOOP;
2441 
2442    add_child_to_parent(p_parent_region, l_messageCompLayout,'contents');
2443    log_error(g_pkg_name || l_api_name, 'Procedure END');
2444 END add_device_checkbox_to_parent;
2445 
2446 FUNCTION create_device_button_region
2447             RETURN JDR_DOCBUILDER.ELEMENT IS
2448     l_api_name          CONSTANT VARCHAR2(50) := 'CREATE_DEVICE_BUTTON_REGION';
2449     l_button_rowlayout_region JDR_DOCBUILDER.ELEMENT := NULL;
2450     l_button_cellformat_region JDR_DOCBUILDER.ELEMENT := NULL;
2451     l_submit_button JDR_DOCBUILDER.ELEMENT := NULL;
2452     l_prompt VARCHAR2(30);
2453 
2454     --PPRTEST
2455     ppr_event  jdr_docbuilder.ELEMENT;
2456 BEGIN
2457     log_error(g_pkg_name || l_api_name, 'Function BEGIN');
2458     l_button_rowlayout_region := JDR_DOCBUILDER.createElement(JDR_DOCBUILDER.OA_NS, 'rowLayout');
2459     jdr_docbuilder.setAttribute(l_button_rowlayout_region, 'id', 'ReadButtonRowLayout');
2460     jdr_docbuilder.setAttribute(l_button_rowlayout_region, 'width', '100%');
2461     jdr_docbuilder.setAttribute(l_button_rowlayout_region, 'hAlign', 'end');
2462     jdr_docbuilder.setAttribute(l_button_rowlayout_region, 'vAlign', 'top');
2463 
2464     l_button_cellformat_region := JDR_DOCBUILDER.createElement(JDR_DOCBUILDER.OA_NS, 'cellFormat');
2465     jdr_docbuilder.setAttribute(l_button_cellformat_region, 'id', 'ReadButtonCellLayout');
2466     jdr_docbuilder.setAttribute(l_button_cellformat_region, 'width', '100%');
2467     jdr_docbuilder.setAttribute(l_button_cellformat_region, 'hAlign', 'end');
2468     jdr_docbuilder.setAttribute(l_button_cellformat_region, 'vAlign', 'top');
2469 
2470     l_submit_button := create_jrad_region_item('submitButton');
2471     l_prompt := DBMS_XMLGEN.convert(fnd_message.get_string('QA','QA_QWB_READ_DEV_BUTTON_LABEL'));
2472     jdr_docbuilder.setAttribute(l_submit_button, 'id', 'ReadDeviceButton');
2473     jdr_docbuilder.setAttribute(l_submit_button, 'prompt', l_prompt);
2474     jdr_docbuilder.setAttribute(l_submit_button, 'unvalidated', 'true');
2475     -- bug 6737113
2476     -- Added accessKey for Read Device as O.
2477     -- bhsankar Tue Jan 22 04:12:09 PST 2008
2478     jdr_docbuilder.setAttribute(l_submit_button, 'accessKey', '0');
2479     jdr_docbuilder.setAttribute(l_submit_button, 'serverUnvalidated', 'true');
2480 
2481     --For PPR
2482     ppr_event := JDR_DOCBUILDER.createElement(p_namespace => JDR_DOCBUILDER.UI_NS,
2483                                               p_tagName   => 'firePartialAction');
2484     jdr_docbuilder.setAttribute(ppr_event, 'event', 'qadevice');
2485     jdr_docbuilder.setAttribute(ppr_event, 'unvalidated', 'true');
2486 
2487     jdr_docbuilder.addchild(p_parent          => l_submit_button,
2488                             p_groupingNS      => JDR_DOCBUILDER.UI_NS,
2489                             p_groupingTagName => 'primaryClientAction',
2490                             p_child           => ppr_event);
2491 
2492     add_child_to_parent(l_button_cellformat_region,l_submit_button,'contents');
2493     add_child_to_parent(l_button_rowlayout_region,l_button_cellformat_region,'contents');
2494     log_error(g_pkg_name || l_api_name, 'Function END');
2495     return l_button_rowlayout_region;
2496 
2497 END create_device_button_region;
2498 
2499 FUNCTION create_device_checkbox_region(
2500     p_plan_id IN NUMBER) RETURN JDR_DOCBUILDER.ELEMENT IS
2501 
2502     l_rowlayout_region JDR_DOCBUILDER.ELEMENT := NULL;
2503     l_cellformat_region JDR_DOCBUILDER.ELEMENT := NULL;
2504     l_api_name          CONSTANT VARCHAR2(50) := 'CREATE_DEVICE_CHECKBOX_REGION';
2505 BEGIN
2506     log_error(g_pkg_name || l_api_name, 'Function BEGIN');
2507     l_rowlayout_region := JDR_DOCBUILDER.createElement(JDR_DOCBUILDER.OA_NS, 'rowLayout');
2508     jdr_docbuilder.setAttribute(l_rowlayout_region, 'id', 'ChkBoxRowLayout');
2509     jdr_docbuilder.setAttribute(l_rowlayout_region, 'width', '100%');
2510 
2511     l_cellformat_region := JDR_DOCBUILDER.createElement(JDR_DOCBUILDER.OA_NS, 'cellFormat');
2512     jdr_docbuilder.setAttribute(l_cellformat_region, 'id', 'ChkBoxCellFormat');
2513     jdr_docbuilder.setAttribute(l_cellformat_region, 'width', '100%');
2514 
2515     add_device_checkbox_to_parent(p_plan_id, l_cellformat_region);
2516     add_child_to_parent(l_rowlayout_region,l_cellformat_region,'contents');
2517     log_error(g_pkg_name || l_api_name, 'Function END');
2518     return l_rowlayout_region;
2519 
2520 END create_device_checkbox_region;
2521 
2522 FUNCTION create_device_region(
2523     p_plan_id IN NUMBER,
2524     p_device_region_code IN VARCHAR2 DEFAULT g_eqr_device_prefix,
2525     p_parent_region_prefix IN VARCHAR2 DEFAULT NULL) RETURN JDR_DOCBUILDER.ELEMENT IS
2526     l_api_name          CONSTANT VARCHAR2(50) := 'CREATE_DEVICE_REGION';
2527     l_device_region_hdr JDR_DOCBUILDER.ELEMENT := NULL;
2528     l_table_layout_region JDR_DOCBUILDER.ELEMENT := NULL;
2529     l_button_region JDR_DOCBUILDER.ELEMENT := NULL;
2530     l_checkbox_region JDR_DOCBUILDER.ELEMENT := NULL;
2531 
2532     l_prompt VARCHAR2(100) := null;
2533 
2534 
2535 BEGIN
2536     log_error(g_pkg_name || l_api_name, 'Function BEGIN');
2537     l_prompt := get_region_prompt('DEVICE');
2538     log_error(g_pkg_name || l_api_name, 'Device prompt: ' || l_prompt );
2539     l_device_region_hdr := create_jrad_region(p_device_region_code, 'header', l_prompt, '-1');
2540     log_error(g_pkg_name || l_api_name,'p_device_region_code :' || p_device_region_code );
2541     l_table_layout_region := create_jrad_region('DeviceTabLayout', 'tableLayout', '', '-1');
2542 
2543     log_error(g_pkg_name || l_api_name, 'Device table layout created');
2544 
2545     l_button_region := create_device_button_region();
2546     add_child_to_parent(l_table_layout_region,l_button_region,'contents');
2547 
2548     log_error(g_pkg_name || l_api_name, 'Device read button created');
2549 
2550     l_checkbox_region := create_device_checkbox_region(p_plan_id);
2551     add_child_to_parent(l_table_layout_region,l_checkbox_region,'contents');
2552 
2553     log_error(g_pkg_name || l_api_name, 'Device checkbox region created');
2554 
2555     add_child_to_parent(l_device_region_hdr,l_table_layout_region,'contents');
2556     log_error(g_pkg_name || l_api_name, 'Function END');
2557     RETURN l_device_region_hdr;
2558 
2559 END create_device_region;
2560 
2561 -- 12.1 Device Integration Project End.
2562 
2563 PROCEDURE map_plan_eqr_single(
2564     p_plan_id IN NUMBER,
2565     p_special_segment VARCHAR2) IS
2566 
2567     l_top_region_code  VARCHAR2(35);
2568     l_data_region_code VARCHAR2(35);
2569     l_comments_region_code VARCHAR2(35);
2570 
2571     l_mode      VARCHAR2(15) := g_eqr_single_layout;
2572     l_saved PLS_INTEGER;
2573     l_err_num      NUMBER;
2574     l_err_msg      VARCHAR2(100);
2575 
2576     l_top_doc JDR_DOCBUILDER.DOCUMENT := NULL;
2577     l_top_region JDR_DOCBUILDER.ELEMENT := NULL;
2578     l_data_region JDR_DOCBUILDER.ELEMENT := NULL;
2579     l_comments_region JDR_DOCBUILDER.ELEMENT := NULL;
2580     l_attachments_region JDR_DOCBUILDER.ELEMENT := NULL;
2581 
2582     --
2583     -- 12.1 Device Integration Project
2584     -- Functions to create the device region
2585     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
2586     --
2587     l_device_region_code VARCHAR2(35) := NULL;
2588     l_device_region JDR_DOCBUILDER.ELEMENT := NULL;
2589     l_api_name          CONSTANT VARCHAR2(50) := 'map_plan_eqr_single';
2590 
2591 BEGIN
2592     -- this version of map_plan_eqr_single takes in prefix of top region, data region and comment region
2593     -- The top region contains the data, comment regions. Attachment region is a
2594     -- static region taken care of at run time. Not adding attachments here.
2595     -- A document is created with the top region as its top level element
2596     -- The top region is a stack layout for a single row layout.
2597     -- Deleting the top document should delete the existing regions for the plan.
2598 
2599     log_error(g_pkg_name || l_api_name, 'Function BEGIN');
2600 
2601     l_top_region_code := construct_code(g_eqr_single_prefix || p_special_segment, p_plan_id);
2602     l_data_region_code := construct_code(g_eqr_data_prefix || p_special_segment, p_plan_id);
2603     l_device_region_code := construct_code(g_eqr_device_prefix || p_special_segment, p_plan_id);
2604     l_comments_region_code := construct_code(g_eqr_comments_prefix || p_special_segment, p_plan_id);
2605 
2606     delete_old_top_document(g_jrad_region_path || l_top_region_code);
2607 
2608     l_top_doc := create_top_document(g_jrad_region_path || l_top_region_code);
2609     l_top_region := create_and_set_top_element(l_top_doc, l_top_region_code,
2610                         'stackLayout');
2611     --
2612     -- 12.1 Device Integration Project
2613     -- Functions to create the device region
2614     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
2615     --
2616     -- Creating the device region if MES is uptaken
2617 
2618     log_error(g_pkg_name || l_api_name, ' Creating device region');
2619 
2620     IF FND_PROFILE.VALUE('WIP_MES_OPS_FLAG') = 1 THEN
2621        l_device_region := create_device_region(p_plan_id, l_device_region_code);
2622        IF l_device_region.id is not null THEN
2623           add_child_to_parent(l_top_region, l_device_region, 'contents');
2624        END IF;
2625     END IF;
2626 
2627     log_error(g_pkg_name || l_api_name, ' Device region created');
2628 
2629     -- 12.1 Device Integration Project End.
2630 
2631     -- create data and comments regions and add them as children to the top region
2632 
2633     l_data_region := create_data_region(p_plan_id, l_data_region_code, l_mode);
2634     add_child_to_parent(l_top_region, l_data_region, 'contents');
2635 
2636     l_comments_region := create_comments_region(p_plan_id, l_comments_region_code,
2637                                                 l_mode);
2638     if l_comments_region.id is not null then
2639         add_child_to_parent(l_top_region, l_comments_region, 'contents');
2640     end if;
2641 
2642     l_attachments_region := create_attachments_region(p_plan_id, '');
2643     IF l_attachments_region.id is not null THEN
2644         add_child_to_parent(l_top_region, l_attachments_region, 'contents');
2645     END IF;
2646 
2647     -- save the document
2648     l_saved := JDR_DOCBUILDER.SAVE;
2649 
2650     log_error(g_pkg_name || l_api_name, ' Function END');
2651 
2652 EXCEPTION
2653 
2654     WHEN OTHERS THEN
2655         l_err_num := SQLCODE;
2656         l_err_msg := SUBSTR(SQLERRM, 1, 100);
2657         log_error(g_pkg_name || l_api_name, 'EXCEPTION : ' || l_err_msg);
2658 
2659 END map_plan_eqr_single;
2660 
2661 PROCEDURE map_plan_eqr_single(
2662     p_plan_id IN NUMBER) IS
2663 
2664 BEGIN
2665     -- this is a wrapper to map_plan_eqr_single (p_plan_id, <special segment string>)
2666     -- in this case, special segment is null
2667     map_plan_eqr_single(p_plan_id, NULL);
2668 
2669 END map_plan_eqr_single;
2670 
2671 
2672 PROCEDURE map_plan_vqr_single(
2673     p_plan_id IN NUMBER,
2674     p_special_segment VARCHAR2) IS
2675 
2676     l_top_region_code  VARCHAR2(35);
2677     l_data_region_code VARCHAR2(35);
2678     l_comments_region_code VARCHAR2(35);
2679 
2680     l_mode      VARCHAR2(15) := g_vqr_single_layout;
2681     l_saved PLS_INTEGER;
2682     l_err_num      NUMBER;
2683     l_err_msg      VARCHAR2(100);
2684 
2685     l_top_doc JDR_DOCBUILDER.DOCUMENT := NULL;
2686     l_top_region JDR_DOCBUILDER.ELEMENT := NULL;
2687     l_data_region JDR_DOCBUILDER.ELEMENT := NULL;
2688     l_comments_region JDR_DOCBUILDER.ELEMENT := NULL;
2689     l_attachments_region JDR_DOCBUILDER.ELEMENT := NULL;
2690 
2691 BEGIN
2692     -- Similar to map_plan_eqr_single logic
2693 
2694     -- The top region contains the data, comment regions. Attachment region is a
2695     -- static region taken care of at run time. Not adding attachments here.
2696     -- A document is created with the top region as its top level element
2697     -- The top region is a stack layout for a single row layout.
2698     -- Deleting the top document should delete the existing regions for the plan.
2699 
2700     l_top_region_code := construct_code(g_vqr_single_prefix || p_special_segment, p_plan_id);
2701     l_data_region_code := construct_code(g_vqr_data_prefix || p_special_segment, p_plan_id);
2702     l_comments_region_code := construct_code(g_vqr_comments_prefix || p_special_segment, p_plan_id);
2703 
2704     delete_old_top_document(g_jrad_region_path || l_top_region_code);
2705 
2706     l_top_doc := create_top_document(g_jrad_region_path || l_top_region_code);
2707     l_top_region := create_and_set_top_element(l_top_doc, l_top_region_code,
2708                         'stackLayout');
2709 
2710     -- create data and comments regions and add them as children to the top region
2711 
2712     l_data_region := create_data_region(p_plan_id, l_data_region_code, l_mode);
2713     add_child_to_parent(l_top_region, l_data_region, 'contents');
2714 
2715     l_comments_region := create_comments_region(p_plan_id, l_comments_region_code,
2716                                                 l_mode);
2717     if l_comments_region.id is not null then
2718         add_child_to_parent(l_top_region, l_comments_region, 'contents');
2719     end if;
2720 
2721     l_attachments_region := create_attachments_region(p_plan_id, l_mode);
2722     IF l_attachments_region.id is not null THEN
2723         add_child_to_parent(l_top_region, l_attachments_region, 'contents');
2724     END IF;
2725 
2726     -- save the document
2727     l_saved := JDR_DOCBUILDER.SAVE;
2728 
2729 EXCEPTION
2730 
2731     WHEN OTHERS THEN
2732         l_err_num := SQLCODE;
2733         l_err_msg := SUBSTR(SQLERRM, 1, 100);
2734         -- dbms_output.put_line(err_msg);
2735 
2736 END map_plan_vqr_single;
2737 
2738 PROCEDURE map_plan_vqr_single(
2739     p_plan_id IN NUMBER) IS
2740 
2741 BEGIN
2742     -- this is a wrapper to map_plan_vqr_single (p_plan_id, <special segment string>)
2743     -- in this case, special segment is null
2744     map_plan_vqr_single(p_plan_id, NULL);
2745 
2746 END map_plan_vqr_single;
2747 
2748 
2749 
2750 
2751 -- 12.1 Usability project
2752 -- added for export page
2753 -- abgangul
2754 FUNCTION get_export_vo_attribute_name(p_plan_id NUMBER , p_char_id NUMBER)
2755 RETURN VARCHAR2 IS
2756 
2757 l_vo_attr_name VARCHAR2(50);
2758 BEGIN
2759     select upper(translate(qc.name,' ''*{}','_____'))
2760     into   l_vo_attr_name
2761     from   qa_chars qc,
2762            qa_plan_chars qpc
2763     where  qc.char_id = qpc.char_id
2764     and    qpc.plan_id = p_plan_id
2765     and    qpc.char_id = p_char_id;
2766 
2767     return l_vo_attr_name;
2768 END get_export_vo_attribute_name;
2769 
2770 
2771 
2772 FUNCTION get_hc_export_vo_attr_name (p_attribute_name VARCHAR2)
2773 RETURN VARCHAR2 IS
2774 
2775 BEGIN
2776     if p_attribute_name = g_qa_created_by_attribute then
2777         return 'CREATED_BY';
2778     elsif p_attribute_name = g_collection_id_attribute then
2779         return 'COLLECTION_ID';
2780     elsif p_attribute_name = g_last_update_date_attribute then
2781         return 'LAST_UPDATE_DATE';
2782     else
2783         return null;
2784     end if;
2785 
2786 END get_hc_export_vo_attr_name;
2787 
2788 
2789 FUNCTION create_item_for_mult_vqr( p_plan_id        IN  NUMBER,
2790                                    p_char_id        IN  NUMBER,
2791                                    x_char_item      OUT NOCOPY JDR_DOCBUILDER.ELEMENT,
2792                                    x_char_dtl_item  OUT NOCOPY JDR_DOCBUILDER.ELEMENT)
2793 RETURN VARCHAR2 IS
2794 
2795 l_vo_attribute_name     VARCHAR2(50);
2796 l_attr_code             VARCHAR2(50);
2797 l_col_code              VARCHAR2(50);
2798 l_hrd_code              VARCHAR2(50);
2799 l_prompt                VARCHAR2(50);
2800 c_displayed_flag        VARCHAR2(50);
2801 c_datatype              VARCHAR2(30);
2802 
2803 l_displayed_flag        NUMBER;
2804 
2805 l_col_hdr               JDR_DOCBUILDER.ELEMENT;
2806 l_char_item             JDR_DOCBUILDER.ELEMENT;
2807 l_char_dtl_item         JDR_DOCBUILDER.ELEMENT;
2808 l_sort_hdr              JDR_DOCBUILDER.ELEMENT;
2809 
2810 l_datatype              qa_chars.datatype%TYPE;
2811 
2812 
2813 BEGIN
2814         l_attr_code := construct_code(g_element_prefix, p_char_id);
2815         l_col_code := 'column' || l_attr_code;
2816         l_col_hdr := jdr_docbuilder.createElement(jdr_docbuilder.oa_ns,   'column');
2817         jdr_docbuilder.setattribute(l_col_hdr,   'id', l_col_code);
2818 
2819 
2820         l_char_item := create_region_item_for_vqr(p_plan_id, p_char_id,
2821                                                              g_element_prefix, g_vqr_multiple_layout);
2822         l_char_dtl_item := create_region_item_for_vqr(p_plan_id, p_char_id,
2823                                                           g_dtl_element_prefix, g_vqr_multiple_layout);
2824 
2825         l_vo_attribute_name := get_export_vo_attribute_name(p_plan_id, p_char_id);
2826         jdr_docbuilder.setAttribute(l_char_item, 'viewName', g_export_vo_name);
2827         jdr_docbuilder.setAttribute(l_char_item, 'viewAttr', l_vo_attribute_name);
2828         jdr_docbuilder.setAttribute(l_char_dtl_item, 'viewName', g_export_vo_name);
2829         jdr_docbuilder.setAttribute(l_char_dtl_item, 'viewAttr', l_vo_attribute_name);
2830 
2831         jdr_docbuilder.setAttribute(l_char_item, 'sortable', 'true');
2832         jdr_docbuilder.setAttribute(l_char_dtl_item, 'sortable', 'true');
2833         jdr_docbuilder.setAttribute(l_col_hdr, 'sortable' , 'true');
2834 
2835 
2836         add_child_to_parent(l_col_hdr,   l_char_item,   'contents');
2837 
2838         l_hrd_code := 'colHdr' || l_attr_code;
2839         l_sort_hdr := jdr_docbuilder.createElement(jdr_docbuilder.oa_ns,   'sortableHeader');
2840         jdr_docbuilder.setAttribute(l_sort_hdr,   'id', l_hrd_code);
2841         l_prompt := get_prompt(p_plan_id,   p_char_id);
2842         jdr_docbuilder.setAttribute(l_sort_hdr,   'prompt',   l_prompt);
2843         jdr_docbuilder.setAttribute(l_sort_hdr, 'sortable' , 'yes');
2844         jdr_docbuilder.setAttribute(l_sort_hdr, 'sortState' , 'yes');
2845         add_child_to_parent(l_col_hdr,   l_sort_hdr,   'columnHeader');
2846 
2847         l_datatype := qa_chars_api.datatype(p_char_id);
2848         c_datatype := convert_data_type(l_datatype);
2849         jdr_docbuilder.setAttribute(l_char_dtl_item, 'styleClass', 'OraTableDetail');
2850 
2851 --        jdr_docbuilder.setAttribute(l_char_item, 'dataType', c_datatype);
2852 --        jdr_docbuilder.setAttribute(l_char_dtl_item, 'dataType', c_datatype);
2853 
2854         x_char_item := l_col_hdr;
2855         x_char_dtl_item := l_char_dtl_item;
2856 
2857         l_displayed_flag := qa_plan_element_api.qpc_displayed_flag(p_plan_id, p_char_id);
2858         c_displayed_flag :=  convert_boolean_flag(l_displayed_flag);
2859 
2860         if c_displayed_flag = 'true' then
2861             jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'true');
2862 --            jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'true');
2863         end if;
2864 
2865         return c_displayed_flag;
2866 
2867 
2868 END create_item_for_mult_vqr;
2869 
2870 PROCEDURE create_special_item_mult_vqr( p_attribute_code           IN VARCHAR2,
2871                                    x_char_item      OUT NOCOPY JDR_DOCBUILDER.ELEMENT,
2872                                    x_char_dtl_item  OUT NOCOPY JDR_DOCBUILDER.ELEMENT)
2873 IS
2874 
2875 l_vo_attribute_name     VARCHAR2(50);
2876 l_attr_code             VARCHAR2(50);
2877 l_col_code              VARCHAR2(50);
2878 l_hrd_code              VARCHAR2(50);
2879 l_prompt                VARCHAR2(50);
2880 
2881 l_col_hdr               JDR_DOCBUILDER.ELEMENT;
2882 l_char_item             JDR_DOCBUILDER.ELEMENT;
2883 l_char_dtl_item         JDR_DOCBUILDER.ELEMENT;
2884 l_sort_hdr              JDR_DOCBUILDER.ELEMENT;
2885 
2886 BEGIN
2887 
2888     l_attr_code := construct_code(g_element_prefix, p_attribute_code);
2889     l_col_code := 'column' || l_attr_code;
2890     l_col_hdr := jdr_docbuilder.createElement(jdr_docbuilder.oa_ns,   'column');
2891     jdr_docbuilder.setattribute(l_col_hdr,   'id', l_col_code);
2892 
2893     l_char_item := create_special_region_item (p_attribute_code, 'messageStyledText',
2894                                                                 g_element_prefix, g_vqr_multiple_layout);
2895     l_char_dtl_item := create_special_region_item (p_attribute_code, 'messageStyledText',
2896                                                                 g_dtl_element_prefix, g_vqr_multiple_layout);
2897 
2898     jdr_docbuilder.setAttribute(l_char_item, 'sortable', 'true');
2899     jdr_docbuilder.setAttribute(l_char_dtl_item, 'sortable', 'true');
2900     jdr_docbuilder.setAttribute(l_col_hdr, 'sortable' , 'true');
2901 
2902     add_child_to_parent(l_col_hdr, l_char_item, 'contents');
2903 
2904     l_hrd_code := 'colHdr' || l_attr_code;
2905     l_sort_hdr := jdr_docbuilder.createElement(jdr_docbuilder.oa_ns,   'sortableHeader');
2906     jdr_docbuilder.setAttribute(l_sort_hdr,   'id', l_hrd_code);
2907     l_prompt := get_special_item_label(p_attribute_code);
2908     jdr_docbuilder.setAttribute(l_sort_hdr,   'prompt',   l_prompt);
2909     jdr_docbuilder.setAttribute(l_sort_hdr, 'sortable' , 'yes');
2910     jdr_docbuilder.setAttribute(l_sort_hdr, 'sortState' , 'yes');
2911     add_child_to_parent(l_col_hdr,   l_sort_hdr,   'columnHeader');
2912 
2913 
2914     jdr_docbuilder.setAttribute(l_char_dtl_item, 'styleClass', 'OraTableDetail');
2915 
2916     jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'true');
2917 --    jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'true');
2918 
2919 
2920     l_vo_attribute_name := get_hc_export_vo_attr_name(p_attribute_code);
2921     jdr_docbuilder.setAttribute(l_char_item, 'viewName', g_export_vo_name);
2922     jdr_docbuilder.setAttribute(l_char_item, 'viewAttr', l_vo_attribute_name);
2923     jdr_docbuilder.setAttribute(l_char_dtl_item, 'viewName', g_export_vo_name);
2924     jdr_docbuilder.setAttribute(l_char_dtl_item, 'viewAttr', l_vo_attribute_name);
2925 
2926 
2927 
2928     x_char_item := l_col_hdr;
2929     x_char_dtl_item := l_char_dtl_item;
2930 
2931 
2932 END create_special_item_mult_vqr;
2933 
2934 PROCEDURE map_plan_vqr_multiple(
2935     p_plan_id IN NUMBER) IS
2936 
2937     l_top_region_code           VARCHAR2(35);
2938     l_dtl_region_code           VARCHAR2(35);
2939     l_data_region_code          VARCHAR2(35);
2940     l_comments_region_code      VARCHAR2(35);
2941     l_vo_attribute_name         VARCHAR2(35);
2942     l_prompt                    VARCHAR2(35);
2943     l_mode                      VARCHAR2(15) := g_vqr_multiple_layout;
2944     l_saved                     PLS_INTEGER;
2945     l_err_num                   NUMBER;
2946     l_err_msg                   VARCHAR2(100);
2947     l_counter                   NUMBER  := 0;
2948     l_comments_exist            VARCHAR2(1) := 'N';
2949     l_displayed_flag            NUMBER;
2950     c_displayed_flag            VARCHAR2(10);
2951 
2952 
2953     l_top_doc           JDR_DOCBUILDER.DOCUMENT := NULL;
2954     l_top_region        JDR_DOCBUILDER.ELEMENT  := NULL;
2955     l_data_region       JDR_DOCBUILDER.ELEMENT  := NULL;
2956     l_dtl_region        JDR_DOCBUILDER.ELEMENT  := NULL;
2957     l_comments_region   JDR_DOCBUILDER.ELEMENT  := NULL;
2958     l_char_item         JDR_DOCBUILDER.ELEMENT  := NULL;
2959     l_char_dtl_item     JDR_DOCBUILDER.ELEMENT  := NULL;
2960 
2961 
2962     CURSOR plan_chars (p_char_type VARCHAR2)IS
2963         SELECT   qpc.char_id
2964         FROM     qa_plan_chars qpc,
2965                  qa_chars qc
2966         WHERE    qc.char_id = qpc.char_id
2967         AND      qpc.enabled_flag = 1
2968         AND      qpc.plan_id = p_plan_id
2969         AND      ((p_char_type = 'NonComments' AND qc.datatype <> g_comments_datatype) OR
2970                   (p_char_type = 'Comments' AND qc.datatype = g_comments_datatype))
2971         ORDER BY QPC.mandatory_flag, QPC.prompt_sequence;
2972 
2973 
2974 BEGIN
2975     -- The top region contains the data, comment regions.
2976     -- A document is created with the top region as its top level element
2977     -- The top region is a stack layout for a single row layout.
2978     -- Deleting the top document should delete the existing regions for the plan.
2979 
2980     l_top_region_code := construct_code(g_vqr_multiple_prefix, p_plan_id);
2981 
2982     delete_old_top_document(g_jrad_region_path || l_top_region_code);
2983 
2984     l_top_doc := create_top_document(g_jrad_region_path || l_top_region_code);
2985 
2986     l_prompt := get_region_prompt('DATA');
2987     l_top_region := create_jrad_region (l_top_region_code, 'advancedTable', l_prompt, null, g_vqr_multiple_layout);
2988     jdr_docbuilder.setAttribute(l_top_region, 'width', '100%');
2989     JDR_DOCBUILDER.setTopLevelElement(l_top_doc, l_top_region);
2990     JDR_DOCBUILDER.setAttribute(l_top_region, 'viewName', g_export_vo_name);
2991 
2992     -- create the detail container region
2993     l_dtl_region_code := construct_code(g_vqr_mult_dtl_prefix, p_plan_id);
2994     l_dtl_region := create_jrad_region(l_dtl_region_code, 'stackLayout', null, '-1');
2995 
2996     l_data_region_code := construct_code(g_vqr_data_prefix, p_plan_id);
2997     l_data_region := create_jrad_region(l_data_region_code, 'labeledFieldLayout', null, '2');
2998     add_child_to_parent(l_dtl_region, l_data_region, 'contents');
2999 
3000 
3001     -- Now add the plan items to main and detail regions.
3002     -- First the mandatory non-comment items, ordered by prompt sequence, followed by non-mandatory
3003     -- non-comment items ordered by prompt sequence. Only 5 items come in main region, everything
3004     -- else goes to detail region. Comment items are always in detail region and follw the non-comment
3005     -- items ordered by prompt sequence. Thereafter we will have the special attributes created_By,
3006     -- collection_id and last_updated_by. These will usually be in detail region, but will come in
3007     -- main region if the main region still has less than 5 items.
3008 
3009     l_counter := 0;
3010     for rec in plan_chars ('NonComments')
3011     loop
3012         c_displayed_flag := create_item_for_mult_vqr(p_plan_id, rec.char_id, l_char_item, l_char_dtl_item);
3013         add_child_to_parent(l_top_region, l_char_item, 'contents');
3014         add_child_to_parent(l_data_region, l_char_dtl_item, 'contents');
3015 
3016         if c_displayed_flag = 'false' then
3017             jdr_docbuilder.setAttribute(l_char_item, 'rendered', c_displayed_flag);
3018             jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', c_displayed_flag);
3019         elsif c_displayed_flag = 'true' then
3020             if l_counter < 5 then
3021                 jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'true');
3022                 jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', 'false');
3023 --                jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'true');
3024 --                jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'false');
3025                 l_counter := l_counter + 1;
3026             else
3027                 jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'false');
3028                 jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', 'true');
3029 --                jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'false');
3030 --                jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'true');
3031             end if;
3032         end if;
3033 
3034     end loop;
3035 
3036 
3037     -- Now add the special region items
3038     create_special_item_mult_vqr(g_qa_created_by_attribute, l_char_item, l_char_dtl_item);
3039     add_child_to_parent(l_top_region, l_char_item, 'contents');
3040     add_child_to_parent(l_data_region, l_char_dtl_item, 'contents');
3041     if l_counter < 5 then
3042         jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'true');
3043         jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', 'false');
3044 --        jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'true');
3045 --        jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'false');
3046         l_counter := l_counter + 1;
3047     else
3048         jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'false');
3049         jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', 'true');
3050 --        jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'false');
3051 --        jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'true');
3052     end if;
3053 
3054 
3055 
3056 
3057     create_special_item_mult_vqr(g_collection_id_attribute, l_char_item, l_char_dtl_item);
3058     add_child_to_parent(l_top_region, l_char_item, 'contents');
3059     add_child_to_parent(l_data_region, l_char_dtl_item, 'contents');
3060     if l_counter < 5 then
3061         jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'true');
3062         jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', 'false');
3063 --        jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'true');
3064 --        jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'false');
3065         l_counter := l_counter + 1;
3066     else
3067         jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'false');
3068         jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', 'true');
3069 --        jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'false');
3070 --        jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'true');
3071     end if;
3072 
3073 
3074 
3075     create_special_item_mult_vqr(g_last_update_date_attribute, l_char_item, l_char_dtl_item);
3076     add_child_to_parent(l_top_region, l_char_item, 'contents');
3077     add_child_to_parent(l_data_region, l_char_dtl_item, 'contents');
3078     if l_counter < 5 then
3079         jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'true');
3080         jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', 'false');
3081 --        jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'true');
3082 --        jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'false');
3083         l_counter := l_counter + 1;
3084     else
3085         jdr_docbuilder.setAttribute(l_char_item, 'rendered', 'false');
3086         jdr_docbuilder.setAttribute(l_char_dtl_item, 'rendered', 'true');
3087 --        jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'false');
3088 --        jdr_docbuilder.setAttribute(l_char_dtl_item, 'queryable', 'true');
3089     end if;
3090 
3091 
3092 
3093     -- add the comment items, if any
3094     for rec in plan_chars ('Comments')
3095     loop
3096         -- create the comments region
3097         If l_comments_exist = 'N' then
3098             l_comments_region_code := construct_code(g_vqr_comments_prefix, p_plan_id);
3099             l_comments_region := create_jrad_region(l_comments_region_code, 'labeledFieldLayout', null, '1');
3100             l_comments_exist := 'Y';
3101         end if;
3102         -- add to comment items to the region
3103         c_displayed_flag := create_item_for_mult_vqr(p_plan_id, rec.char_id, l_char_item, l_char_dtl_item);
3104         add_child_to_parent(l_comments_region, l_char_dtl_item, 'contents');
3105         jdr_docbuilder.setAttribute(l_char_item, 'rendered', c_displayed_flag);
3106         jdr_docbuilder.setAttribute(l_char_item, 'styleClass', 'OraTableDetail');
3107 
3108 --        if c_displayed_flag = 'true' then
3109 --            jdr_docbuilder.setAttribute(l_char_item, 'queryable', 'true');
3110 --        end if;
3111 
3112     end loop;
3113 
3114     -- Now add comments region to detail region
3115     if l_comments_exist = 'Y' then
3116         add_child_to_parent(l_dtl_region, l_comments_region, 'contents');
3117     end if;
3118 
3119     -- now add the detail region to the main region
3120     add_child_to_parent(l_top_region, l_dtl_region, 'detail');
3121 
3122     -- save the document
3123     l_saved := JDR_DOCBUILDER.SAVE;
3124 
3125 EXCEPTION
3126 
3127     WHEN OTHERS THEN
3128         l_err_num := SQLCODE;
3129         l_err_msg := SUBSTR(SQLERRM, 1, 100);
3130         -- dbms_output.put_line(err_msg);
3131 
3132 END map_plan_vqr_multiple;
3133 
3134 -- End: 12.1 Usability project changes for export
3135 -- abgangul
3136 
3137 
3138 PROCEDURE map_plan_eqr_multiple(
3139     p_plan_id IN NUMBER,
3140     p_special_segment VARCHAR2) IS
3141 
3142     l_table_region_code  VARCHAR2(35);
3143     l_dtl_region_code VARCHAR2(35);
3144 
3145     l_mode      VARCHAR2(15) := g_eqr_multiple_layout;
3146     l_saved PLS_INTEGER;
3147     l_err_num      NUMBER;
3148     l_err_msg      VARCHAR2(100);
3149     l_char_count NUMBER := NULL;
3150 
3151     l_top_doc JDR_DOCBUILDER.DOCUMENT := NULL;
3152     l_table_region JDR_DOCBUILDER.ELEMENT := NULL;
3153     l_dtl_region JDR_DOCBUILDER.ELEMENT := NULL;
3154 
3155     --
3156     -- 12.1 Device Integration Project
3157     -- Variables for adding device region
3158     -- to the multi row layout.
3159     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
3160     --
3161     l_stk_region    JDR_DOCBUILDER.ELEMENT := NULL;
3162     l_device_region JDR_DOCBUILDER.ELEMENT := NULL;
3163     l_stk_region_code varchar2(35) := NULL;
3164     l_device_region_code varchar2(35) := NULL;
3165     l_prompt VARCHAR2(30);
3166 BEGIN
3167     -- The top region is a table
3168     -- It contains all the region items and the hide/show detailed region.
3169     -- Here I add all the plan chars to the table as well as the detailed region
3170     -- with a different ID. Comments are added only to detailed region.
3171     -- Adding multi row attachment link too to the table.
3172     -- At run time, CO decides what to render horizontally in the row and what
3173     -- to render in the detailed region depending on the number of elements in the
3174     -- plan and whether an element is a context element or not.
3175 
3176     -- A document is created with the table region as its top level element
3177 
3178 
3179     l_stk_region_code := construct_code(g_eqr_multiple_prefix || p_special_segment, p_plan_id);
3180     l_dtl_region_code := construct_code(g_eqr_mult_dtl_prefix || p_special_segment, p_plan_id);
3181     l_table_region_code := construct_code(g_eqr_mult_data_prefix || p_special_segment, p_plan_id);
3182     l_device_region_code := construct_code(g_eqr_device_prefix || p_special_segment, p_plan_id);
3183     delete_old_top_document(g_jrad_region_path || l_stk_region_code);
3184 
3185     l_top_doc := create_top_document(g_jrad_region_path || l_stk_region_code);
3186     --
3187     -- 12.1 Device Integration Project
3188     -- The top document needs to be a stack layout to
3189     -- stack the device region instead of table layout.
3190     -- Hence, commenting.
3191     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
3192     --
3193     -- l_table_region := create_and_set_top_element(l_top_doc, l_table_region_code,
3194     --                  'table');
3195 
3196     -- Setting the top document as a stacklayout.
3197     l_stk_region := create_and_set_top_element(l_top_doc, l_stk_region_code,
3198                         'stackLayout');
3199 
3200     -- Creating the device region if MES is uptaken
3201 
3202     IF FND_PROFILE.VALUE('WIP_MES_OPS_FLAG') = 1 THEN
3203        l_device_region := create_device_region(p_plan_id, l_device_region_code);
3204 
3205        IF l_device_region.id is not null THEN
3206           add_child_to_parent(l_stk_region, l_device_region, 'contents');
3207        END IF;
3208     END IF;
3209 
3210     -- Adding the table region (data region)
3211     -- to the stack region (top document) already created.
3212     l_prompt := get_region_prompt('DATA');
3213 
3214     l_table_region := create_jrad_region (l_table_region_code, 'table', l_prompt, null);
3215     add_child_to_parent(l_stk_region, l_table_region, 'contents');
3216 
3217     -- 12.1 Device Integration Project End.
3218 
3219     -- Add non comment plan chars and special items as children to the table region
3220     l_char_count := add_plan_chars_to_region(p_plan_id, l_table_region,
3221                                                 l_mode, 'NonComments');
3222     add_special_chars_to_region(p_plan_id, l_table_region, l_mode);
3223 
3224     -- Create detail region and add it to the table region.
3225     l_dtl_region := create_detail_region(p_plan_id, l_dtl_region_code,
3226                                                 g_eqr_multiple_layout);
3227     add_child_to_parent(l_table_region, l_dtl_region, 'detail');
3228 
3229     -- 12.1 Inline Project
3230     -- saugupta
3231     add_table_selection(l_table_region);
3232     add_table_action(l_table_region);
3233 
3234     -- save the document
3235     l_saved := JDR_DOCBUILDER.SAVE;
3236 
3237 EXCEPTION
3238 
3239     WHEN OTHERS THEN
3240         l_err_num := SQLCODE;
3241         l_err_msg := SUBSTR(SQLERRM, 1, 100);
3242         -- dbms_output.put_line(err_msg);
3243 
3244 END map_plan_eqr_multiple;
3245 
3246 PROCEDURE map_plan_eqr_multiple(
3247     p_plan_id IN NUMBER) IS
3248 
3249 BEGIN
3250     -- this is a wrapper to map_plan_eqr_multiple (p_plan_id, <special segment string>)
3251     -- in this case, special segment is null
3252     map_plan_eqr_multiple(p_plan_id, NULL);
3253 
3254 END map_plan_eqr_multiple;
3255 
3256 -- 12.1 Inline Region in MES Transactions
3257 -- Project code start
3258 
3259 -- create advanced table of inline region
3260 -- this should be called from the CP
3261 PROCEDURE map_plan_adv_table_eqr(p_plan_id IN NUMBER,   p_special_segment VARCHAR2) IS
3262 
3263 l_table_region_code VARCHAR2(35);
3264 l_dtl_region_code VARCHAR2(35);
3265 
3266 l_mode VARCHAR2(15) := g_eqr_advtable_layout;
3267 -- change mode for inline region
3268 l_saved pls_integer;
3269 l_err_num NUMBER;
3270 l_err_msg VARCHAR2(100);
3271 l_char_count NUMBER := NULL;
3272 
3273 l_top_doc      jdr_docbuilder.DOCUMENT := NULL;
3274 l_table_region jdr_docbuilder.element  := NULL;
3275 l_dtl_region   jdr_docbuilder.element  := NULL;
3276 
3277 l_tbl_act      jdr_docbuilder.element  := NULL;
3278 l_tbl_s_sel    jdr_docbuilder.element  := NULL;
3279 l_tbl_footer   jdr_docbuilder.element  := NULL;
3280 l_api_name constant varchar2(50) := 'MAP_PLAN_ADV_TABLE_EQR';
3281 
3282 --
3283 -- 12.1 Device Integration Project
3284 -- Variables for adding device region
3285 -- to the multi row layout.
3286 -- bhsankar Wed Oct 24 04:45:16 PDT 2007
3287 --
3288 l_stk_region    JDR_DOCBUILDER.ELEMENT := NULL;
3289 l_device_region JDR_DOCBUILDER.ELEMENT := NULL;
3290 l_stk_region_code varchar2(35) := NULL;
3291 l_device_region_code varchar2(35) := NULL;
3292 l_prompt VARCHAR2(30);
3293 
3294 
3295 BEGIN
3296   log_error(g_pkg_name || l_api_name, 'Function BEGIN');
3297     l_stk_region_code := construct_code(g_eqr_multiple_prefix || p_special_segment, p_plan_id);
3298     l_dtl_region_code := construct_code(g_eqr_mult_dtl_prefix || p_special_segment, p_plan_id);
3299     l_table_region_code := construct_code(g_eqr_mult_data_prefix || p_special_segment, p_plan_id);
3300     l_device_region_code := construct_code(g_eqr_device_prefix || p_special_segment, p_plan_id);
3301     delete_old_top_document(g_jrad_region_path || l_stk_region_code);
3302 
3303     l_top_doc := create_top_document(g_jrad_region_path || l_stk_region_code);
3304     --
3305     -- 12.1 Device Integration Project
3306     -- The top document needs to be a stack layout to
3307     -- stack the device region instead of table layout.
3308     -- Hence, commenting.
3309     -- bhsankar Wed Oct 24 04:45:16 PDT 2007
3310     --
3311     -- l_table_region := create_and_set_top_element(l_top_doc, l_table_region_code,
3312     --                  'table');
3313 
3314     -- Setting the top document as a stacklayout.
3315     l_stk_region := create_and_set_top_element(l_top_doc, l_stk_region_code,
3316                         'stackLayout');
3317 
3318     -- Creating the device region if MES is uptaken
3319 
3320     IF FND_PROFILE.VALUE('WIP_MES_OPS_FLAG') = 1 THEN
3321        l_device_region := create_device_region(p_plan_id, l_device_region_code);
3322 
3323        IF l_device_region.id is not null THEN
3324           add_child_to_parent(l_stk_region, l_device_region, 'contents');
3325        END IF;
3326     END IF;
3327 
3328     -- Adding the table region (data region)
3329     -- to the stack region (top document) already created.
3330     l_prompt := get_region_prompt('DATA');
3331 
3332     l_table_region := create_jrad_region (l_table_region_code, 'advancedTable', l_prompt, null);
3333     JDR_DOCBUILDER.setAttribute(l_table_region, 'viewName', g_vo_name);
3334     add_child_to_parent(l_stk_region, l_table_region, 'contents');
3335 
3336     -- 12.1 Device Integration Project End.
3337 
3338 --  l_table_region_code := construct_code(g_eqr_adv_table_prefix || p_special_segment,   p_plan_id);
3339 --  l_dtl_region_code := construct_code(g_eqr_mult_dtl_prefix || p_special_segment,   p_plan_id);
3340   log_error(g_pkg_name || l_api_name, 'Region Info: ' || l_table_region_code);
3341   -- delete the existing document
3342 --  delete_old_top_document(g_jrad_region_path || l_table_region_code);
3343 
3344 --  l_top_doc := create_top_document(g_jrad_region_path || l_table_region_code);
3345   log_error(g_pkg_name || l_api_name, 'JRAD Doc created');
3346   -- either change the method or call a diff method for creating the advanced region
3347   -- l_table_region := create_and_set_top_element(l_top_doc,   l_table_region_code,   'advancedTable');
3348   log_error(g_pkg_name || l_api_name,'Advanced Table Created ');
3349   -- Add non comment plan chars and special items as children to the table region
3350   -- below we neeed to add special colums to advanced table to work
3351   l_char_count := add_plan_chars_to_region(p_plan_id,   l_table_region,   l_mode,   'NonComments');
3352   log_error(g_pkg_name || l_api_name,'Elements added to Advanced Table: ' || to_char(l_char_count));
3353   -- Add attachments and special items like org etc
3354  add_special_chars_to_region(p_plan_id,   l_table_region,  g_eqr_multiple_layout);
3355 
3356   -- Create detail region and add it to the table region.
3357   --  TODO -> hide show and detail region
3358    l_dtl_region := create_detail_region(p_plan_id,   l_dtl_region_code,   g_eqr_multiple_layout);
3359    add_child_to_parent(l_table_region,   l_dtl_region,   'detail');
3360 
3361   add_table_selection(l_table_region);
3362   add_table_action(l_table_region);
3363   add_table_footer(l_table_region);
3364 
3365   -- save the document
3366   l_saved := jdr_docbuilder.save;
3367   log_error(g_pkg_name || l_api_name, 'Saved Document. Function END');
3368 
3369 EXCEPTION
3370 
3371 WHEN others THEN
3372   l_err_num := SQLCODE;
3373   l_err_msg := SUBSTR(sqlerrm,   1,   100);
3374   -- dbms_output.put_line(err_msg);
3375 
3376 END map_plan_adv_table_eqr;
3377 
3378 -- End 12.1 Inline Region in MES Txn
3379 
3380 PROCEDURE map_plan_special(
3381     p_plan_id IN NUMBER) IS
3382 
3383    PRAGMA AUTONOMOUS_TRANSACTION;
3384 
3385    CURSOR plan IS
3386         SELECT template_plan_id
3387         FROM qa_plans
3388         WHERE plan_id = p_plan_id;
3389 
3390    CURSOR valueLookups(c_char_id NUMBER) IS
3391         SELECT short_code, ORA_HASH(short_code) hash_code
3392         FROM qa_plan_char_value_lookups
3393         WHERE plan_id = p_plan_id
3394         AND char_id = c_char_id;
3395 
3396    l_template_plan_id NUMBER;
3397    l_short_code qa_plan_char_value_lookups.short_code%TYPE;
3398    l_hash_code NUMBER;
3399 BEGIN
3400    OPEN plan;
3401    FETCH plan INTO l_template_plan_id;
3402    CLOSE plan;
3403 
3404    -- special logic if template_plan_id matches Nonconformance Master
3405    -- or Corrective Action Request plan
3406    -- map one plan per each Nonconformance Source (or Request type)
3407    -- the region name will embed the Nonconformance Source (or Request type)
3408    --
3409    -- this is a workaround to overcome the inability to personalize
3410    -- region with double default column style at user level
3411    -- admin will create one personalization per each Nonconformance Source (or Request type)
3412    --
3413    -- still map one with normal naming scheme
3414    IF l_template_plan_id in (18, 35) THEN -- Nonconformance master
3415       OPEN valueLookups(qa_ss_const.nonconformance_source);
3416       LOOP
3417          FETCH valueLookups INTO l_short_code, l_hash_code;
3418          EXIT WHEN valueLookups%NOTFOUND;
3419          -- map_plan_eqr_single(p_plan_id, g_ncm || l_short_code || '_');
3420          -- map_plan_vqr_single(p_plan_id, g_ncm || l_short_code || '_');
3421          -- map_plan_eqr_multiple(p_plan_id, g_ncm || l_short_code || '_');
3422          map_plan_eqr_single(p_plan_id, g_ncm || l_hash_code || '_');
3423          map_plan_vqr_single(p_plan_id, g_ncm || l_hash_code || '_');
3424          map_plan_eqr_multiple(p_plan_id, g_ncm || l_hash_code || '_');
3425 
3426       END LOOP;
3427       CLOSE valueLookups;
3428    ELSIF l_template_plan_id = 65 THEN -- Corrective Action Request
3429       OPEN valueLookups(2147483607); -- should be qa_ss_const.request_type
3430       LOOP
3431          FETCH valueLookups INTO l_short_code, l_hash_code;
3432          EXIT WHEN valueLookups%NOTFOUND;
3433          -- map_plan_eqr_single(p_plan_id, g_car || l_short_code || '_');
3434          -- map_plan_vqr_single(p_plan_id, g_car || l_short_code || '_');
3435          -- map_plan_eqr_multiple(p_plan_id, g_car || l_short_code || '_');
3436          map_plan_eqr_single(p_plan_id, g_car || l_hash_code || '_');
3437          map_plan_vqr_single(p_plan_id, g_car || l_hash_code || '_');
3438          map_plan_eqr_multiple(p_plan_id, g_car || l_hash_code || '_');
3439 
3440       END LOOP;
3441       CLOSE valueLookups;
3442    END IF;
3443 
3444    COMMIT; --commit the autonomous txn.
3445 
3446 END map_plan_special;
3447 
3448 PROCEDURE map_plan(
3449     p_plan_id IN NUMBER) IS
3450 
3451 -- jrad_doc_version was used to check for a jrad plan region in map on demand.
3452 -- During AK -> Jrad migration, since it would take a lot of installation time to
3453 -- create jrad regions for all the existing plans, mapping on demand was used.
3454 -- jrad_doc_version is also used as a work around for fwk's bug 2837618.
3455 -- Whenever we modify a plan, we needed to create a jrad region with a new name.
3456 -- This is achieved by appending jrad_doc_version to the prefix.
3457 
3458 -- Here we assume the bug is fixed (11i10). But we still a boolean to check the
3459 -- existence for map on demand. It seems we can directly qiery fwk tables to find
3460 -- out whether a region exists. Needs further investigation.
3461 
3462 -- For now, p_jrad_doc_version is null.
3463 l_err_num NUMBER;
3464 l_err_msg VARCHAR2(100);
3465 
3466    PRAGMA AUTONOMOUS_TRANSACTION;
3467 
3468 BEGIN
3469    set_debug_mode('FND');
3470    -- special logic if template_plan_id matches Nonconformance Master
3471    -- or Corrective Action Request plan
3472 
3473    map_plan_special(p_plan_id);
3474 
3475    -- map for all plan using normal naming scheme
3476    map_plan_eqr_single(p_plan_id);
3477    map_plan_vqr_single(p_plan_id);
3478    -- 12.1 QWB Usability Improvements
3479    -- map_plan_eqr_multiple(p_plan_id);
3480 
3481 
3482    -- 12.1 Inline Region Project
3483    -- saugupta
3484    -- log_error(g_pkg_name || 'Map_plan', 'Start Advanced table');
3485    map_plan_adv_table_eqr(p_plan_id, NULL);
3486 
3487    -- 12.1 Usability Project
3488    -- Added for export page
3489    -- abgangul
3490    map_plan_vqr_multiple(p_plan_id);
3491 
3492    COMMIT; --commit the autonomous txn.
3493 
3494 END map_plan;
3495 
3496 
3497 
3498 PROCEDURE map_on_demand (p_plan_id IN NUMBER) IS
3499 
3500     PRAGMA AUTONOMOUS_TRANSACTION;
3501 
3502     l_eqr_single_doc  VARCHAR2(100);
3503     l_vqr_single_doc VARCHAR2(100);
3504     l_eqr_multiple_doc VARCHAR2(100);
3505     l_vqr_multiple_doc VARCHAR2(100);
3506 
3507     l_jrad_upgrade_ver NUMBER;
3508     l_seed_ver         NUMBER;
3509 
3510 BEGIN
3511 
3512     --
3513     -- Tracking Bug 4697145
3514     -- MOAC Upgrade feature to perform on demand mapping
3515     -- if a special JRad LOV is changed.
3516     -- Added the two SELECTs.
3517     -- bso Sun Nov  6 17:07:45 PST 2005
3518     --
3519 
3520     SELECT jrad_upgrade_ver
3521     INTO   l_jrad_upgrade_ver
3522     FROM   qa_plans
3523     WHERE  plan_id = p_plan_id
3524     FOR UPDATE;
3525 
3526     SELECT jrad_upgrade_ver
3527     INTO   l_seed_ver
3528     FROM   qa_plans
3529     WHERE  plan_id = qa_ss_const.JRAD_UPGRADE_PLAN;
3530 
3531     fnd_msg_pub.initialize;
3532 
3533     l_eqr_single_doc := g_jrad_region_path || construct_code(
3534         g_eqr_single_prefix, p_plan_id);
3535     l_vqr_single_doc := g_jrad_region_path || construct_code(
3536         g_vqr_single_prefix, p_plan_id);
3537     l_eqr_multiple_doc := g_jrad_region_path || construct_code(
3538         g_eqr_multiple_prefix, p_plan_id);
3539 
3540 
3541     -- 12.1 Usability project
3542     -- Added for export page.
3543     l_vqr_multiple_doc := g_jrad_region_path || construct_code(
3544         g_vqr_multiple_prefix, p_plan_id);
3545 
3546 
3547     --
3548     -- Tracking Bug 4697145
3549     -- MOAC Upgrade feature to perform on demand mapping
3550     -- if a special JRad LOV is changed.
3551     -- Added the two OR conditions.
3552     -- bso Sun Nov  6 17:07:45 PST 2005
3553     --
3554     -- Reformatted the logical conditions to a form that I
3555     -- believe is easier to understand.
3556     -- bso
3557     --
3558 
3559     IF l_jrad_upgrade_ver IS NULL OR
3560         l_jrad_upgrade_ver < l_seed_ver OR
3561         NOT jdr_docbuilder.documentExists(l_eqr_single_doc) OR
3562             NOT jdr_docbuilder.documentExists(l_vqr_single_doc) OR
3563             NOT jdr_docbuilder.documentExists(l_vqr_multiple_doc) OR
3564             NOT jdr_docbuilder.documentExists(l_eqr_multiple_doc) THEN
3565         -- map plan. documents do not exist,
3566         -- or of lower version than wanted.
3567         map_plan(p_plan_id);
3568 
3569         -- Tracking Bug 4697145
3570         -- Map iSP/eAM etc plans also.
3571         qa_jrad_pkg.map_plan(p_plan_id);
3572 
3573 
3574         -- Tracking Bug 4697145
3575         -- Now indicate the upgrade is completed.
3576         jrad_upgraded(p_plan_id);
3577     END IF;
3578 
3579     --
3580     -- Bug 5182097
3581     -- Make a call to refetch the qpc cache in the validation
3582     -- API otherwise some subtle Setup Collection Plan changes
3583     -- such as turning mandatory flag on/off will not be
3584     -- immediately reflected in QWB.
3585     -- bso Mon May  1 17:43:03 PDT 2006
3586     --
3587     qa_plan_element_api.refetch_qa_plan_chars(p_plan_id);
3588 
3589     COMMIT;
3590 
3591 END map_on_demand;
3592 
3593 
3594 
3595 --
3596 -- Bug 4697145
3597 -- MOAC upgrade needs to delete JRad region.  But this procedure
3598 -- is generic to be used by other projects.
3599 -- Used by qajrad.sql
3600 --
3601 PROCEDURE delete_plan_jrad_region(p_plan_id IN NUMBER) IS
3602 
3603 BEGIN
3604     delete_old_top_document(g_jrad_region_path ||
3605         construct_code(g_eqr_single_prefix, p_plan_id));
3606     delete_old_top_document(g_jrad_region_path ||
3607         construct_code(g_eqr_multiple_prefix, p_plan_id));
3608 END delete_plan_jrad_region;
3609 
3610 
3611 --
3612 -- Tracking Bug 4697145
3613 -- MOAC Upgrade feature to indicate this plan has
3614 -- been regenerated and on demand mapping can skip.
3615 -- bso Sun Nov  6 16:52:53 PST 2005
3616 --
3617 PROCEDURE jrad_upgraded(p_plan_id IN NUMBER) IS
3618 
3619 BEGIN
3620     UPDATE qa_plans
3621     SET    jrad_upgrade_ver =
3622           (SELECT nvl(jrad_upgrade_ver, 1)
3623            FROM   qa_plans
3624            WHERE  plan_id = qa_ss_const.JRAD_UPGRADE_PLAN)
3625     WHERE  plan_id = p_plan_id;
3626 END jrad_upgraded;
3627 
3628 
3629 END qa_ssqr_jrad_pkg;