[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;