1 PACKAGE BODY qa_jrad_pkg AS
2 /* $Header: qajradb.pls 120.8.12000000.2 2007/10/17 13:15:15 bhsankar ship $ */
3
4
5 TYPE ParentArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6
7 g_sysdate DATE;
8
9 -- Bug 3769260. shkalyan 29 July 2004.
10 -- Since this cursor was used by many functions, declared it as global
11 -- so that, parse time could be reduced.
12 -- Modified the following functions to use this cursor
13 -- FUNCTION osp_self_service_plan
14 -- FUNCTION shipment_self_service_plan
15 -- FUNCTION eam_work_order_plan
16 -- FUNCTION eam_asset_plan
17 -- FUNCTION eam_op_comp_plan
18
19 CURSOR qptxn(c_plan_id NUMBER, c_txn_no NUMBER) IS
20 SELECT 1
21 FROM qa_plan_transactions
22 WHERE plan_id = c_plan_id
23 AND transaction_number = c_txn_no
24 AND enabled_flag = 1;
25
26
27
28 FUNCTION osp_self_service_plan (p_plan_id IN NUMBER)
29 RETURN BOOLEAN IS
30
31 result BOOLEAN;
32 dummy NUMBER;
33
34 BEGIN
35
36 -- This subroutine determines if a plan has been associated
37 -- and enabled for osp self service transactions or not.
38 -- If it is then it returns true else it returns false.
39
40 OPEN qptxn(p_plan_id,qa_ss_const.ss_outside_processing_txn);
41 FETCH qptxn INTO dummy;
42 result := qptxn%FOUND;
43 CLOSE qptxn;
44 RETURN result;
45
46 END osp_self_service_plan;
47
48
49 FUNCTION shipment_self_service_plan (p_plan_id IN NUMBER)
50 RETURN BOOLEAN IS
51
52 result BOOLEAN;
53 dummy NUMBER;
54
55 BEGIN
56
57 -- This subroutine determines if a plan has been associated
58 -- and enabled for shipment self service transactions or not.
59 -- If it is then it returns true else it returns false.
60
61 OPEN qptxn(p_plan_id,qa_ss_const.ss_shipments_txn);
62 FETCH qptxn INTO dummy;
63 result := qptxn%FOUND;
64 CLOSE qptxn;
65 RETURN result;
66
67 END shipment_self_service_plan;
68
69
70 FUNCTION customer_portal_plan (p_plan_id IN NUMBER)
71 RETURN BOOLEAN IS
72
73 BEGIN
74
75 -- This subroutine determines if a plan should be mapped
76 -- for customer portal (OM). This can be determined
77 -- by checking if sales order is a part of the plan
78 -- and enabled.
79
80 IF (qa_plan_element_api.element_in_plan(p_plan_id,
81 qa_ss_const.sales_order)) THEN
82 RETURN TRUE;
83 ELSE
84 RETURN FALSE;
85 END IF;
86
87 END customer_portal_plan;
88
89
90 FUNCTION eam_work_order_plan (p_plan_id IN NUMBER)
91 RETURN BOOLEAN IS
92
93 result BOOLEAN;
94 dummy NUMBER;
95
96 BEGIN
97
98 -- This subroutine determines if a plan has been associated
99 -- and enabled for EAM work order or not. If it is then it returns true
100 -- else it returns false.
101
102 OPEN qptxn(p_plan_id,qa_ss_const.eam_work_order_txn);
103 FETCH qptxn INTO dummy;
104 result := qptxn%FOUND;
105 CLOSE qptxn;
106
107 RETURN result;
108
109 END eam_work_order_plan;
110
111
112 FUNCTION eam_asset_plan (p_plan_id IN NUMBER)
113 RETURN BOOLEAN IS
114
115 result BOOLEAN;
116 dummy NUMBER;
117
118 BEGIN
119
120 -- This subroutine determines if a plan has been associated
121 -- and enabled for EAM asset query or not. If it is then it returns true
122 -- else it returns false.
123
124 OPEN qptxn(p_plan_id,qa_ss_const.eam_asset_txn);
125 FETCH qptxn INTO dummy;
126 result := qptxn%FOUND;
127 CLOSE qptxn;
128
129 RETURN result;
130
131 END eam_asset_plan;
132
133
134
135 FUNCTION eam_op_comp_plan (p_plan_id IN NUMBER)
136 RETURN BOOLEAN IS
137
138 result BOOLEAN;
139 dummy NUMBER;
140
141 BEGIN
142
143 -- This subroutine determines if a plan has been associated
144 -- and enabled for EAM op comp or not. If it is then it returns true
145 -- else it returns false.
146
147 OPEN qptxn(p_plan_id,qa_ss_const.eam_operation_txn);
148 FETCH qptxn INTO dummy;
149 result := qptxn%FOUND;
150 CLOSE qptxn;
151
152 RETURN result;
153
154 END eam_op_comp_plan;
155
156 --dgupta: Start R12 EAM Integration. Bug 4345492
157 FUNCTION eam_checkin_plan (p_plan_id IN NUMBER)
158 RETURN BOOLEAN IS
159
160 result BOOLEAN;
161 dummy NUMBER;
162
163 BEGIN
164 OPEN qptxn(p_plan_id,qa_ss_const.eam_checkin_txn);
165 FETCH qptxn INTO dummy;
166 result := qptxn%FOUND;
167 CLOSE qptxn;
168
169 RETURN result;
170 END eam_checkin_plan;
171
172 FUNCTION eam_checkout_plan (p_plan_id IN NUMBER)
173 RETURN BOOLEAN IS
174
175 result BOOLEAN;
176 dummy NUMBER;
177
178 BEGIN
179 OPEN qptxn(p_plan_id,qa_ss_const.eam_checkout_txn);
180 FETCH qptxn INTO dummy;
181 result := qptxn%FOUND;
182 CLOSE qptxn;
183
184 RETURN result;
185 END eam_checkout_plan;
186 --dgupta: End R12 EAM Integration. Bug 4345492
187
188 -- Parent-Child
189 FUNCTION parent_child_plan (p_plan_id IN NUMBER)
190 RETURN BOOLEAN IS
191
192 CURSOR c IS
193 SELECT 1
194 FROM qa_pc_plan_relationship
195 WHERE parent_plan_id = p_plan_id
196 OR child_plan_id = p_plan_id;
197
198 result BOOLEAN;
199 dummy NUMBER;
200
201 BEGIN
202
203 -- This subroutine determines if a plan should be mapped
204 -- for parent child VQR. This can be determined
205 -- by checking if plan is a parent or child
206 --
207 OPEN c;
208 FETCH c INTO dummy;
209 result := c%FOUND;
210 CLOSE c;
211
212 RETURN result;
213
214
215 END parent_child_plan;
216
217
218 FUNCTION construct_jrad_code (p_prefix IN VARCHAR2, p_id IN VARCHAR2,
219 p_jrad_doc_ver IN NUMBER DEFAULT NULL)
220 RETURN VARCHAR2 IS
221
222 BEGIN
223
224 -- The function is the standard way to compute attribute and
225 -- region codes.
226 if (p_jrad_doc_ver IS NULL) then
227 RETURN (p_prefix ||p_id);
228 else
229 RETURN (p_prefix ||p_id || '_V' || p_jrad_doc_ver);
230 end if;
231
232 END construct_jrad_code;
233
234
235 FUNCTION retrieve_id (p_code IN VARCHAR2)
236 RETURN NUMBER IS
237
238 pos NUMBER;
239 id VARCHAR2(100);
240
241 BEGIN
242
243 -- The function is the standard way to retrive id given the code
244
245 IF (INSTR(p_code, g_element_prefix) <> 0) THEN
246 pos := length(g_element_prefix)+1;
247
248 ELSIF (INSTR(p_code, g_osp_vqr_prefix) <> 0) THEN
249 pos := length(g_osp_vqr_prefix)+1;
250 ELSE
251 pos := length(g_txn_osp_prefix)+1;
252 END IF;
253
254 id := substr(p_code, pos, length(p_code));
255
256 RETURN to_number(id);
257
258 END retrieve_id;
259
260 -- Bug 3769260. shkalyan 29 July 2004.
261 -- Removed the get_label function which was getting prompt from
262 -- qa_plan_chars. Using the qa_plan_element_api function to utilize cache
263
264 /*
265 FUNCTION get_label (p_plan_id IN NUMBER, p_element_id IN NUMBER)
266 RETURN VARCHAR2 IS
267
268 CURSOR c IS
269 SELECT prompt
270 FROM qa_plan_chars
271 WHERE plan_id = p_plan_id
272 AND char_id = p_element_id;
273
274 label qa_plan_chars.prompt%TYPE;
275
276 BEGIN
277
278 -- This functions retrieves the plan char prompt. This is used
279 -- to populate the label field in the AK tables.
280
281 OPEN c;
282 FETCH c INTO label;
283 CLOSE c;
284
285 RETURN label;
286
287 END get_label;
288 */
289
290 FUNCTION get_special_label (p_prefix IN VARCHAR2)
291 RETURN VARCHAR2 IS
292
293 label VARCHAR2(30);
294
295 BEGIN
296
297 -- For some hardocded columns such as "Created By", "Colleciton"
298 -- and "Last Update Date" we need to retrieve the right label
299 -- keeping translation in mind.
300
301 IF (p_prefix = g_qa_created_by_attribute) THEN
302 label := fnd_message.get_string('QA','QA_SS_CREATED_BY');
303 ELSIF (p_prefix = g_collection_id_attribute) THEN
304 label := fnd_message.get_string('QA','QA_SS_COLLECTION');
305 ELSIF (p_prefix = g_last_update_date_attribute) THEN
306 label := fnd_message.get_string('QA','QA_SS_ENTRY_DATE');
307 ELSE
308 label := '';
309 END IF;
310
311 RETURN label;
312
313 END get_special_label;
314
315
316 FUNCTION get_vo_attribute_name (p_char_id IN NUMBER, p_plan_id IN NUMBER)
317 RETURN VARCHAR2 IS
318
319 column_name VARCHAR2(100);
320
321 BEGIN
322
323 -- This function computes the vo attribute name for
324 -- soft coded elements. Also, it replaces CHARACTER
325 -- Character.
326
327 column_name := qa_core_pkg.get_result_column_name (p_char_id, p_plan_id);
328 column_name := replace(column_name, 'CHARACTER', 'Character');
329
330 RETURN column_name;
331
332 END get_vo_attribute_name;
333
334
335 FUNCTION get_hardcoded_vo_attr_name (p_code IN VARCHAR2)
336 RETURN VARCHAR2 IS
337
338 column_name VARCHAR2(100);
339
340 BEGIN
341
342 -- This function retrieves the result column name for
343 -- hard coded elements.
344
345 IF (INSTR(p_code, g_org_id_attribute) <> 0) THEN
346 column_name := 'ORGANIZATION_ID';
347 ELSIF (INSTR(p_code, g_org_code_attribute) <> 0) THEN
348 column_name := 'ORGANIZATION_CODE';
349 ELSIF (INSTR(p_code, g_plan_id_attribute) <> 0) THEN
350 column_name := 'PLAN_ID';
351 ELSIF (INSTR(p_code, g_plan_name_attribute) <> 0) THEN
352 column_name := 'PLAN_NAME';
353 ELSIF (INSTR(p_code, g_process_status_attribute) <> 0) THEN
354 column_name := 'PROCESS_STATUS';
355 ELSIF (INSTR(p_code, g_source_code_attribute) <> 0) THEN
356 column_name := 'SOURCE_CODE';
357 ELSIF (INSTR(p_code, g_source_line_id_attribute) <> 0) THEN
358 column_name := 'SOURCE_LINE_ID';
359 ELSIF (INSTR(p_code, g_po_agent_id_attribute) <> 0) THEN
360 column_name := 'PO_AGENT_ID';
361 ELSIF (INSTR(p_code, g_qa_created_by_attribute) <> 0) THEN
362 column_name := 'QA_CREATED_BY_NAME';
363 ELSIF (INSTR(p_code, g_collection_id_attribute) <> 0) THEN
364 column_name := 'COLLECTION_ID';
365 ELSIF (INSTR(p_code, g_last_update_date_attribute) <> 0) THEN
366 column_name := 'LAST_UPDATE_DATE';
367 END IF;
368
369 RETURN column_name;
370
371 END get_hardcoded_vo_attr_name;
372
373
374 FUNCTION convert_data_type (p_data_type IN NUMBER)
375 RETURN VARCHAR2 IS
376
377 BEGIN
378
379 -- In Quality the data type is indicated by a number. whereas,
380 -- in ak it a is string that describes what the data type is.
381 -- This routine was written to convert the data_type according
382 -- to AK.
383
384 IF p_data_type in (1,4,5) THEN
385 return 'VARCHAR2';
386 ELSIF p_data_type = 2 THEN
387 return 'NUMBER';
388 ELSIF p_data_type = 3 THEN
389 return 'DATE';
390 -- bug 3178307. rkaza. 10/06/2003. Timezone support.
391 ELSIF p_data_type = 6 THEN
392 return 'DATETIME';
393 ELSE --catch all
394 return 'VARCHAR2';
395 END IF;
396
397 END convert_data_type;
398
399
400 FUNCTION convert_yesno_flag (p_flag IN NUMBER)
401 RETURN VARCHAR2 IS
402
403 BEGIN
404
405 -- In Quality all the flags are numeric, meaning a value of 1 or 2
406 -- is used to indicate if the flag is on or off. In AK however,
407 -- it is a character that describes if the flag is on or off.
408 -- This routine was written to convert the Quality flags to AK.
409
410 IF p_flag = 1 THEN
411 return 'yes';
412 ELSIF p_flag = 2 THEN
413 return 'no';
414 END IF;
415
416 END convert_yesno_flag;
417
418
419 FUNCTION convert_boolean_flag (p_flag IN NUMBER)
420 RETURN VARCHAR2 IS
421
422 BEGIN
423
424 -- In Quality all the flags are numeric, meaning a value of 1 or 2
425 -- is used to indicate if the flag is on or off. In AK however,
426 -- it is a character that describes if the flag is on or off.
427 -- This routine was written to convert the Quality flags to AK.
428
429 -- rkaza. bug 3329507. Added null value to be interpreted as not read only.
430 IF p_flag IS NULL THEN
431 return 'false';
432 ELSIF p_flag = 1 THEN
433 return 'true';
434 ELSIF p_flag = 2 THEN
435 return 'false';
436 END IF;
437
438 END convert_boolean_flag;
439
440 FUNCTION compute_item_style (p_prefix IN VARCHAR2, p_element_id IN NUMBER,
441 p_plan_id IN NUMBER)
442 RETURN VARCHAR2 IS
443
444 l_txn_number NUMBER;
445
446 BEGIN
447
448 -- For Quality's self service application we need to know what
449 -- item style to render the UI. If the element is a context
450 -- element then the item style must be HIDDEN, but for the rest
451 -- of them it should be text input. This distiction is made here.
452
453
454 IF (p_prefix = g_txn_osp_prefix) THEN
455 l_txn_number := qa_ss_const.ss_outside_processing_txn;
456
457 ELSIF (p_prefix = g_txn_ship_prefix) THEN
458 l_txn_number := qa_ss_const.ss_shipments_txn;
459
460 ELSIF (p_prefix = g_txn_work_prefix) THEN
461 l_txn_number := qa_ss_const.eam_work_order_txn;
462
463 ELSIF (p_prefix = g_txn_asset_prefix) THEN
464 l_txn_number := qa_ss_const.eam_asset_txn;
465
466 ELSIF (p_prefix = g_txn_op_prefix) THEN
467 l_txn_number := qa_ss_const.eam_operation_txn;
468
469 --dgupta: Start R12 EAM Integration. Bug 4345492
470 ELSIF (p_prefix = g_checkin_eqr_prefix) THEN
471 l_txn_number := qa_ss_const.eam_checkin_txn;
472
473 ELSIF (p_prefix = g_checkout_eqr_prefix) THEN
474 l_txn_number := qa_ss_const.eam_checkout_txn;
475 --dgupta: End R12 EAM Integration. Bug 4345492
476
477
478 END IF;
479
480 IF context_element(p_element_id, l_txn_number) THEN
481 return 'formValue';
482 ELSIF
483 (qa_plan_element_api.values_exist(p_plan_id, p_element_id)
484 OR qa_plan_element_api.sql_validation_exists(p_element_id)
485 OR qa_chars_api.has_hardcoded_lov(p_element_id)) THEN
486 return 'messageLovInput';
487 ELSE
488
489 return 'messageTextInput';
490 END IF;
491
492 END compute_item_style;
493
494
495 FUNCTION query_criteria (p_prefix IN VARCHAR2, p_element_id IN NUMBER)
496 RETURN BOOLEAN IS
497
498 -- anagarwa Fri Mar 12 12:12:18 PST 2004
499 -- bug 1795119 Ordered Quantity appears in context and detail region
500 -- for VQR
501 -- The cursor is modified by removing search_flag condition so that all
502 -- context elements are hidden
503 -- enabled_flag added as a condition to ensure that no disabled elements
504 -- get added to detail region.
505 CURSOR c (p_txn_number NUMBER) IS
506 SELECT 'TRUE'
507 FROM qa_txn_collection_triggers
508 WHERE transaction_number = p_txn_number
509 --AND search_flag = 1
510 AND enabled_flag = 1
511 AND collection_trigger_id = p_element_id;
512
513 l_txn_number NUMBER;
514 dummy VARCHAR2(10);
515 result BOOLEAN;
516
517 BEGIN
518
519 -- This procecure determines if a colleciton element is a part of the
520 -- VQR where clause. If it is then it is a query criteria for vqr.
521 -- In case of OM nothing is a query query criteria, hence always
522 -- return FALSE
523
524 IF (instr(p_prefix, g_om_vqr_prefix) <> 0) THEN
525 RETURN FALSE;
526 END IF;
527
528 -- parent-child
529 -- In case of parentchild no hidden fields, hence always
530 -- return FALSE
531 IF ((instr(p_prefix, g_pc_vqr_prefix) <> 0) OR
532 (instr(p_prefix, g_pc_vqr_sin_prefix) <> 0 )) THEN
533 RETURN FALSE;
534 END IF;
535
536 IF ( instr(p_prefix, g_osp_vqr_prefix) <> 0 ) THEN
537 -- prefix passed has 'QAVQROSP' in it
538 l_txn_number := qa_ss_const.ss_outside_processing_txn;
539
540 ELSIF ( instr(p_prefix, g_ship_vqr_prefix) <> 0 ) THEN
541 -- prefix passed has 'QAVQRSHP' in it
542 l_txn_number := qa_ss_const.ss_shipments_txn;
543
544 ELSIF ( instr(p_prefix, g_work_vqr_prefix) <> 0 ) THEN
545 -- prefix passed has 'QAVQRWORK' in it
546 l_txn_number := qa_ss_const.eam_work_order_txn;
547
548 ELSIF ( instr(p_prefix, g_asset_vqr_prefix) <> 0 ) THEN
549 -- prefix passed has 'QAVQRASSET' in it
550 l_txn_number := qa_ss_const.eam_asset_txn;
551
552 ELSIF ( instr(p_prefix, g_op_vqr_prefix) <> 0 ) THEN
553 -- prefix passed has 'QAVQROP' in it
554 l_txn_number := qa_ss_const.eam_operation_txn;
555
556 --dgupta: Start R12 EAM Integration. Bug 4345492
557 ELSIF ( instr(p_prefix, g_checkin_vqr_prefix) <> 0 ) THEN
558 -- prefix passed has 'QAVQRCHECKIN' in it
559 l_txn_number := qa_ss_const.eam_checkin_txn;
560
561 ELSIF ( instr(p_prefix, g_checkout_vqr_prefix) <> 0 ) THEN
562 -- prefix passed has 'QAVQRCHECKOUT' in it
563 l_txn_number := qa_ss_const.eam_checkout_txn;
564 --dgupta: End R12 EAM Integration. Bug 4345492
565
566 END IF;
567
568 OPEN c(l_txn_number);
569 FETCH c into dummy;
570 result := c%FOUND;
571 CLOSE c;
572 RETURN result;
573
574 END query_criteria;
575
576
577
578 PROCEDURE get_dependencies (p_char_id IN NUMBER, x_parents OUT NOCOPY ParentArray) IS
579
580 BEGIN
581
582 -- This is needed for populating correct lov relatiovs.
583 -- Given a element id, this function computes the
584 -- ancestors for it and accordingly populates a
585 -- OUT table structure.
586
587 x_parents.delete();
588
589 IF p_char_id = qa_ss_const.item THEN
590 x_parents(1) := qa_ss_const.production_line;
591
592 ELSIF p_char_id = qa_ss_const.to_op_seq_num THEN
593 x_parents(1) := qa_ss_const.job_name;
594 x_parents(2) := qa_ss_const.production_line;
595
596 ELSIF p_char_id = qa_ss_const.from_op_seq_num THEN
597 x_parents(1) := qa_ss_const.job_name;
598 x_parents(2) := qa_ss_const.production_line;
599
600 ELSIF p_char_id = qa_ss_const.to_intraoperation_step THEN
601 x_parents(1) := qa_ss_const.to_op_seq_num;
602
603 ELSIF p_char_id = qa_ss_const.from_intraoperation_step THEN
604 x_parents(1) := qa_ss_const.from_op_seq_num;
605
606 ELSIF p_char_id = qa_ss_const.uom THEN
607
608 x_parents(1) := qa_ss_const.item;
609 x_parents(2) := qa_ss_const.production_line;
610
611 ELSIF p_char_id = qa_ss_const.revision THEN
612 x_parents(1) := qa_ss_const.item;
613 x_parents(2) := qa_ss_const.production_line;
614
615 ELSIF p_char_id = qa_ss_const.subinventory THEN
616 x_parents(1) := qa_ss_const.item;
617 x_parents(2) := qa_ss_const.production_line;
618
619 ELSIF p_char_id = qa_ss_const.locator THEN
620 x_parents(1) := qa_ss_const.subinventory;
621 x_parents(2) := qa_ss_const.item;
622 x_parents(3) := qa_ss_const.production_line;
623
624 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
625 -- bug 3830258 incorrect LOVs in QWB
626 -- synced up the lot number lov with forms
627 ELSIF p_char_id = qa_ss_const.lot_number THEN
628 x_parents(1) := qa_ss_const.item;
629 --x_parents(2) := qa_ss_const.production_line;
630
631 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
632 -- bug 3830258 incorrect LOVs in QWB
633 -- synced up the serial number lov with forms
634 ELSIF p_char_id = qa_ss_const.serial_number THEN
635 x_parents(1) := qa_ss_const.lot_number;
636 x_parents(2) := qa_ss_const.item;
637 --x_parents(3) := qa_ss_const.production_line;
638 x_parents(3) := qa_ss_const.revision;
639
640 ELSIF p_char_id = qa_ss_const.comp_uom THEN
641 x_parents(1) := qa_ss_const.comp_item;
642
643 ELSIF p_char_id = qa_ss_const.comp_revision THEN
644 x_parents(1) := qa_ss_const.comp_item;
645
646 ELSIF p_char_id = qa_ss_const.po_line_num THEN
647 x_parents(1) := qa_ss_const.po_number;
648
649 ELSIF p_char_id = qa_ss_const.po_shipment_num THEN
650 x_parents(1) := qa_ss_const.po_line_num;
651 x_parents(2) := qa_ss_const.po_number;
652
653 ELSIF p_char_id = qa_ss_const.po_release_num THEN
654 x_parents(1) := qa_ss_const.po_number;
655
656 ELSIF p_char_id = qa_ss_const.order_line THEN
657 x_parents(1) := qa_ss_const.sales_order;
658
659 ELSIF p_char_id = qa_ss_const.task_number THEN
660 x_parents(1) := qa_ss_const.project_number;
661
662 --dgupta: Start R12 EAM Integration. Bug 4345492
663 ELSIF p_char_id = qa_ss_const.asset_instance_number THEN
664 x_parents(1) := qa_ss_const.asset_group;
665 x_parents(2) := qa_ss_const.asset_number;
666
667 ELSIF p_char_id = qa_ss_const.asset_number THEN
668 x_parents(1) := qa_ss_const.asset_group;
669 x_parents(2) := qa_ss_const.asset_instance_number;
670
671 -- rkaza. 10/22/2003. 3209804. As part of EAM rebuild project
672 -- added the following dependencies
673 ELSIF p_char_id = qa_ss_const.asset_activity THEN
674 x_parents(1) := qa_ss_const.asset_group;
675 x_parents(2) := qa_ss_const.asset_number;
676 x_parents(3) := qa_ss_const.asset_instance_number;
677
678 ELSIF p_char_id = qa_ss_const.followup_activity THEN
679 x_parents(1) := qa_ss_const.asset_group;
680 x_parents(2) := qa_ss_const.asset_number;
681 x_parents(3) := qa_ss_const.asset_instance_number;
682 --dgupta: End R12 EAM Integration. Bug 4345492
683
684 ELSIF p_char_id = qa_ss_const.maintenance_op_seq THEN
685 x_parents(1) := qa_ss_const.work_order;
686
687 -- rkaza. 12/02/2003. bug 3280307.
688 -- Added dependency relation for component item with item
689 ELSIF p_char_id = qa_ss_const.comp_item THEN
690 x_parents(1) := qa_ss_const.item;
691
692 -- anagarwa Thu Aug 12 15:49:51 PDT 2004
693 -- bug 3830258 incorrect LOVs in QWB
694 -- synced up the component lot number and component serial number
695 -- lov with forms
696 ELSIF p_char_id = qa_ss_const.comp_lot_number THEN
697 x_parents(1) := qa_ss_const.comp_item;
698
699 ELSIF p_char_id = qa_ss_const.comp_serial_number THEN
700 x_parents(1) := qa_ss_const.comp_lot_number;
701 x_parents(2) := qa_ss_const.comp_item;
702 x_parents(3) := qa_ss_const.comp_revision;
703
704 -- R12 OPM Deviations. Bug 4345503 Start
705 ELSIF p_char_id = qa_ss_const.process_batchstep_num THEN
706 x_parents(1) := qa_ss_const.process_batch_num;
707
708 ELSIF p_char_id = qa_ss_const.process_operation THEN
709 x_parents(1) := qa_ss_const.process_batch_num;
710 x_parents(2) := qa_ss_const.process_batchstep_num;
711
712 ELSIF p_char_id = qa_ss_const.process_activity THEN
713 x_parents(1) := qa_ss_const.process_batch_num;
714 x_parents(2) := qa_ss_const.process_batchstep_num;
715
716 ELSIF p_char_id = qa_ss_const.process_resource THEN
717 x_parents(1) := qa_ss_const.process_batch_num;
718 x_parents(2) := qa_ss_const.process_batchstep_num;
719 x_parents(2) := qa_ss_const.process_activity;
720
721 ELSIF p_char_id = qa_ss_const.process_parameter THEN
722 x_parents(1) := qa_ss_const.process_resource;
723
724 -- R12 OPM Deviations. Bug 4345503 End
725 END IF;
726
727 END get_dependencies;
728
729
730 FUNCTION action_target_element (p_plan_id IN NUMBER, p_char_id IN NUMBER)
731 RETURN BOOLEAN IS
732
733 -- Bug 3111310
734 -- Changed the Cursor Query for SQL performance fix
735 -- saugupta Mon Sep 8 06:00:06 PDT 2003
736
737 CURSOR c IS
738 SELECT 1
739 FROM qa_plan_char_actions
740 WHERE assigned_char_id = p_char_id
741 AND plan_char_action_trigger_id
742 IN (SELECT plan_char_action_trigger_id
743 FROM qa_plan_char_action_triggers
744 WHERE plan_id = p_plan_id);
745
746 dummy NUMBER;
747 result BOOLEAN;
748
749 BEGIN
750
751 -- if the element is a potenital target for assigned a value
752 -- action then return true.
753
754 OPEN c;
755 FETCH c INTO dummy;
756 result := c%FOUND;
757 CLOSE c;
758
759 RETURN result;
760
761 END action_target_element;
762
763
764
765
766 FUNCTION get_region_style (p_code VARCHAR2)
767 RETURN VARCHAR2 IS
768
769 l_region_style VARCHAR2(30);
770
771 BEGIN
772
773 -- A value of zero is false 1 is true for INSTR comparison
774
775 IF (instr(p_code, g_txn_work_prefix) <> 0)
776 OR (instr(p_code, g_txn_asset_prefix) <> 0)
777 OR (instr(p_code, g_txn_op_prefix) <> 0)
778 --dgupta: Start R12 EAM Integration. Bug 4345492
779 OR (instr(p_code, g_checkin_eqr_prefix) <> 0)
780 OR (instr(p_code, g_checkout_eqr_prefix) <> 0)
781 --dgupta: End R12 EAM Integration. Bug 4345492
782
783 THEN
784 -- rkaza. BLAF. 01/28/2004. Changed to double col from single col
785 l_region_style := 'defaultDoubleColumn';
786 ELSIF (instr(p_code, g_pc_vqr_sin_prefix) <> 0) --parent-child
787 THEN
788 --ilawler - bug #3462025 - Mon Feb 23 17:42:47 2004
789 --use the new labeledFieldLayout instead
790 l_region_style := 'labeledFieldLayout';
791 ELSE
792 l_region_style := 'table';
793 END IF;
794
795 RETURN l_region_style;
796
797 END get_region_style;
798
799
800 FUNCTION create_jrad_region (
801 p_region_code IN VARCHAR2,
802 p_plan_id IN NUMBER) RETURN JDR_DOCBUILDER.ELEMENT IS
803
804 l_region_style VARCHAR2(30);
805 l_row_id VARCHAR2(30);
806 l_plan_name qa_plans.name%TYPE;
807 topLevel JDR_DOCBUILDER.ELEMENT := NULL;
808
809 l_prompt VARCHAR2(30);
810
811 CURSOR c IS
812 SELECT NAME from qa_plans
813 WHERE plan_id = p_plan_id;
814
815 BEGIN
816
817 -- This procedure creates an entry in ak regions table for a collection
818 -- plan in Quality.
819
820 OPEN c;
821 FETCH c INTO l_plan_name;
822 CLOSE c;
823
824
825 l_region_style := get_region_style(p_region_code);
826 topLevel := JDR_DOCBUILDER.createElement(JDR_DOCBUILDER.OA_NS, l_region_style);
827
828 -- rkaza. 02/26/2004. bug 3461988.
829 -- Prompt of the EQR region should be Data for EAM EQR page.
830 -- EAM vqr table regions should have a width 100%
831
832 IF (instr(p_region_code, g_txn_work_prefix) <> 0)
833 OR (instr(p_region_code, g_txn_asset_prefix) <> 0)
834 --dgupta: Start R12 EAM Integration. Bug 4345492
835 OR (instr(p_region_code, g_checkin_eqr_prefix) <> 0)
836 OR (instr(p_region_code, g_checkout_eqr_prefix) <> 0)
837 --dgupta: End R12 EAM Integration. Bug 4345492
838 OR (instr(p_region_code, g_txn_op_prefix) <> 0) then
839 l_prompt := fnd_message.get_string('QA', 'QA_SS_RN_PROMPT_DATA');
840 jdr_docbuilder.setAttribute(topLevel, 'text', l_prompt);
841 END IF;
842
843 IF (instr(p_region_code, g_work_vqr_prefix) <> 0)
844 OR (instr(p_region_code, g_asset_vqr_prefix) <> 0)
845 --dgupta: Start R12 EAM Integration. Bug 4345492
846 OR (instr(p_region_code, g_checkin_vqr_prefix) <> 0)
847 OR (instr(p_region_code, g_checkout_vqr_prefix) <> 0)
848 --dgupta: End R12 EAM Integration. Bug 4345492
849 OR (instr(p_region_code, g_op_vqr_prefix) <> 0) then
850 jdr_docbuilder.setAttribute(topLevel, 'width', '100%');
851 END IF;
852
853 jdr_docbuilder.setAttribute(topLevel, 'prompt', l_plan_name);
854 jdr_docbuilder.setAttribute(topLevel, 'regionName', l_plan_name);
855 jdr_docbuilder.setAttribute(topLevel, 'id', p_region_code);
856
857 IF (instr(p_region_code, g_pc_vqr_sin_prefix) <> 0) THEN
858 jdr_docbuilder.setAttribute(topLevel, 'headerDisabled', 'true');
859 ELSIF (instr(p_region_code, g_pc_vqr_prefix) <> 0) THEN
860 --ilawler - bug #3462025 - Mon Feb 23 17:19:07 2004
861 --Set width of table to be 90%
862 jdr_docbuilder.setAttribute(topLevel, 'width', '90%');
863 END IF;
864
865 -- Tracking Bug : 3209719
866 -- Added for OAC complicance
867 -- ilawler Tue Oct 21 15:24:26 2003
868 IF l_region_style = 'table' THEN
869 jdr_docbuilder.setAttribute(topLevel, 'shortDesc', fnd_message.get_string('QA','QA_OAC_RESULTS_TABLE'));
870 ELSIF l_region_style = 'labeledFieldLayout' THEN
871 --ilawler - bug #3462025 - Mon Feb 23 17:42:47 2004
872 --when using the labeledFieldLayout, suggest 2 columns
873 jdr_docbuilder.setAttribute(topLevel, 'columns', '2');
874 END IF;
875
876 RETURN topLevel;
877
878 -- dbms_output.put_line('Adding Region : ' || l_region_code);
879
880 END create_jrad_region;
881
882
883 --
884 -- MOAC Project. 4637896
885 -- New procedure to create base attribute code.
886 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
887 --
888
889 FUNCTION cons_base_attribute_code (
890 p_element_prefix IN VARCHAR2,
891 p_id IN VARCHAR2)
892 RETURN VARCHAR2 IS
893
894 BEGIN
895 IF(p_id = qa_ss_const.po_number) THEN
896 return qa_chars_api.hardcoded_column(p_id);
897 END IF;
898
899 return construct_jrad_code(p_element_prefix,p_id);
900
901 END cons_base_attribute_code;
902
903
904 PROCEDURE add_lov_relations (
905 p_plan_id IN NUMBER,
906 p_char_id IN NUMBER,
907 p_attribute_code IN VARCHAR2,
908 p_input_elem IN jdr_docbuilder.Element) IS
909
910
911 err_num NUMBER;
912 err_msg VARCHAR2(100);
913
914 l_row_id VARCHAR2(30);
915 l_region_code VARCHAR2(30);
916 l_attribute_code VARCHAR2(30);
917 l_lov_attribute_code VARCHAR2(30);
918 l_base_attribute_code VARCHAR2(30);
919 l_parents ParentArray;
920
921 lovMap jdr_docbuilder.ELEMENT;
922
923
924 BEGIN
925
926 -- This function adds lov relations for a region item.
927 -- Here the region item corresponds to a collection plan element.
928
929 --Criteria
930 lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
931 jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', p_attribute_code);
932 jdr_docbuilder.setAttribute(lovMap, 'lovItem', g_lov_attribute_code);
933 jdr_docbuilder.setAttribute(lovMap, 'requiredForLOV', 'true');
934 jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
935 lovMap);
936 --Result
937 lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
938 jdr_docbuilder.setAttribute(lovMap, 'resultTo', p_attribute_code);
939 jdr_docbuilder.setAttribute(lovMap, 'lovItem', g_lov_attribute_code);
940 jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
941 lovMap);
942 --Org Id
943 lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
944 jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', g_org_id_attribute);
945 jdr_docbuilder.setAttribute(lovMap, 'lovItem', g_lov_attribute_org_id);
946 jdr_docbuilder.setAttribute(lovMap, 'programmaticQuery', 'true');
947 jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
948 lovMap);
949 --Plan Id
950 lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
951 jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', g_plan_id_attribute);
952 jdr_docbuilder.setAttribute(lovMap, 'lovItem', g_lov_attribute_plan_id);
953 jdr_docbuilder.setAttribute(lovMap, 'programmaticQuery', 'true');
954 jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
955 lovMap);
956
957
958 get_dependencies(p_char_id, l_parents);
959
960 FOR i IN 1..l_parents.COUNT LOOP
961
962 -- anagarwa
963 -- Bug 2751198
964 -- Add dependency to LOV only if the element exists in the plan
965 -- This is achieved by adding the following IF statement
966 -- IF qa_plan_element_api.exists_qa_plan_chars(p_plan_id,
967 -- l_parents(i)) THEN
968
969 -- rkaza. 10/22/2003. 3209804. shold not use exists_qa_plan_chars
970 -- array might not have been initialized. use element_in_plan
971
972 IF qa_plan_element_api.element_in_plan(p_plan_id,
973 l_parents(i)) THEN
974
975 l_lov_attribute_code := g_lov_attribute_dependency || to_char(i);
976 --
977 -- MOAC Project. 4637896
978 -- Call new procedure to construct base code
979 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
980 --
981
982 l_base_attribute_code := cons_base_attribute_code(g_element_prefix, l_parents(i));
983
984 lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS,
985 'lovMap');
986 jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom',
987 l_base_attribute_code);
988 jdr_docbuilder.setAttribute(lovMap, 'lovItem', l_lov_attribute_code);
989 jdr_docbuilder.setAttribute(lovMap, 'programmaticQuery', 'true');
990 jdr_docbuilder.addChild(p_input_elem, jdr_docbuilder.JRAD_NS, 'lovMappings',
991 lovMap);
992 END IF;
993
994 END LOOP;
995
996 -- dbms_output.put_line('Adding LOV rel : ');
997
998 EXCEPTION
999
1000 WHEN OTHERS THEN
1001 err_num := SQLCODE;
1002 err_msg := SUBSTR(SQLERRM, 1, 100);
1003 -- dbms_output.put_line(err_msg);
1004
1005 END add_lov_relations;
1006
1007 --
1008 -- MOAC Project. 4637896
1009 -- New procedure to create id item.
1010 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1011 --
1012
1013
1014 FUNCTION create_id_item_for_eqr (
1015 p_plan_id IN NUMBER,
1016 p_char_id IN NUMBER)
1017 RETURN jdr_docbuilder.ELEMENT IS
1018
1019 l_vo_attribute_name VARCHAR2(30) DEFAULT NULL;
1020 l_id_elem jdr_docbuilder.ELEMENT := NULL;
1021
1022 BEGIN
1023
1024 l_vo_attribute_name := qa_chars_api.hardcoded_column(p_char_id);
1025 l_id_elem := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS, 'formValue');
1026
1027 -- set properties
1028 jdr_docbuilder.setAttribute(l_id_elem, 'id', l_vo_attribute_name);
1029 jdr_docbuilder.setAttribute(l_id_elem, 'viewName', g_eqr_view_usage_name);
1030 jdr_docbuilder.setAttribute(l_id_elem, 'viewAttr', l_vo_attribute_name);
1031 jdr_docbuilder.setAttribute(l_id_elem, 'dataType', 'NUMBER');
1032
1033 return l_id_elem;
1034
1035 END create_id_item_for_eqr;
1036
1037
1038
1039 --
1040 -- MOAC Project. 4637896
1041 -- Checks whether its a normalized lov item.
1042 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1043 --
1044 FUNCTION is_normalized_lov (
1045 p_plan_id IN NUMBER,
1046 p_char_id IN NUMBER) RETURN VARCHAR2 IS
1047
1048 BEGIN
1049 -- currently we are enabling normalized logic
1050 -- only for PO NUMBER
1051 if(p_char_id = qa_ss_const.po_number) then
1052 return 'T';
1053 end if;
1054
1055 return 'F';
1056 END is_normalized_lov;
1057
1058 --
1059 -- MOAC Project. 4637896
1060 -- Gets external LOV region name
1061 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1062 --
1063 FUNCTION get_lov_region_name (
1064 p_plan_id IN NUMBER,
1065 p_char_id IN NUMBER) RETURN VARCHAR2 IS
1066
1067 BEGIN
1068 -- currently we are enabling normalized logic
1069 -- only for PO NUMBER. So we are hard coding
1070 -- lov region name. In future, this proc must
1071 -- be generalized.
1072 if(p_char_id = qa_ss_const.po_number) then
1073 return 'PONumberLovRN';
1074 end if;
1075
1076 return 'QaLovRN';
1077
1078 END get_lov_region_name;
1079
1080
1081 --
1082 -- MOAC Project. 4637896
1083 -- New method to process normalized lov item.
1084 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1085 --
1086
1087 PROCEDURE process_normalized_lov (
1088 p_plan_id IN NUMBER,
1089 p_char_id IN NUMBER,
1090 p_attribute_code IN VARCHAR2,
1091 p_char_item IN jdr_docbuilder.Element) IS
1092
1093 lovMap jdr_docbuilder.ELEMENT;
1094 l_lov_region VARCHAR2(100);
1095
1096 BEGIN
1097
1098 IF(p_char_id = qa_ss_const.po_number) THEN
1099 l_lov_region := qa_ssqr_jrad_pkg.g_jrad_lov_dir_path || get_lov_region_name(p_plan_id,p_char_id);
1100 jdr_docbuilder.setAttribute(p_char_item,
1101 'externalListOfValues',
1102 l_lov_region);
1103 --Criteria
1104 lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
1105 jdr_docbuilder.setAttribute(lovMap, 'criteriaFrom', p_attribute_code);
1106 jdr_docbuilder.setAttribute(lovMap, 'resultTo', p_attribute_code);
1107 jdr_docbuilder.setAttribute(lovMap, 'lovItem', 'Segment1');
1108 jdr_docbuilder.setAttribute(lovMap, 'requiredForLOV', 'true');
1109 jdr_docbuilder.addChild(p_char_item, jdr_docbuilder.JRAD_NS, 'lovMappings',
1110 lovMap);
1111 -- po_header_id
1112 lovMap := jdr_docbuilder.createElement(jdr_docbuilder.JRAD_NS, 'lovMap');
1113 jdr_docbuilder.setAttribute(lovMap, 'lovItem', 'PoHeaderId');
1114 jdr_docbuilder.setAttribute(lovMap, 'resultTo', qa_chars_api.hardcoded_column(p_char_id));
1115 jdr_docbuilder.addChild(p_char_item, jdr_docbuilder.JRAD_NS, 'lovMappings',
1116 lovMap);
1117
1118 END IF; -- PO Number
1119
1120 END process_normalized_lov;
1121
1122 --
1123 -- MOAC Project. 4637896
1124 -- New method to process regular lov item.
1125 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1126 --
1127
1128 PROCEDURE process_regular_lov (
1129 p_plan_id IN NUMBER,
1130 p_char_id IN NUMBER,
1131 p_attribute_code IN VARCHAR2,
1132 p_char_item IN jdr_docbuilder.Element) IS
1133
1134 BEGIN
1135
1136 jdr_docbuilder.setAttribute(p_char_item, 'externalListOfValues',
1137 g_jrad_lov_path);
1138
1139 add_lov_relations(p_plan_id, p_char_id, p_attribute_code, p_char_item);
1140
1141 END process_regular_lov;
1142
1143 --
1144 -- MOAC Project. 4637896
1145 -- New method to process lov item.
1146 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1147 --
1148
1149 PROCEDURE process_messageLovInput (
1150 p_plan_id IN NUMBER,
1151 p_char_id IN NUMBER,
1152 p_attribute_code IN VARCHAR2,
1153 p_char_item IN jdr_docbuilder.Element) IS
1154
1155
1156
1157 BEGIN
1158 -- in the future, this may be changed to be more generic
1159 -- so that all hardcoded LOVs will go through this
1160 -- process_normalized_lov procedure. Currently handle
1161 -- PO Number only for the immediate MOAC requirement.
1162 IF is_normalized_lov(p_plan_id,p_char_id) = 'T' THEN
1163 process_normalized_lov(
1164 p_plan_id,
1165 p_char_id,
1166 p_attribute_code,
1167 p_char_item);
1168 ELSE
1169 process_regular_lov(
1170 p_plan_id,
1171 p_char_id,
1172 p_attribute_code,
1173 p_char_item);
1174 END IF;
1175
1176 END process_messageLovInput;
1177
1178
1179 -- Bug 5455658. SHKALYAN 20-Sep-2006
1180 -- Added this function to get the prompt and concatenate the UOM
1181 FUNCTION get_prompt (p_plan_id IN NUMBER,
1182 p_char_id IN NUMBER) RETURN VARCHAR2 IS
1183
1184 l_prompt qa_plan_chars.prompt%TYPE;
1185 l_uom_code qa_plan_chars.uom_code%TYPE;
1186 BEGIN
1187 -- The function is the standard way to compute prompt
1188 -- taking uom_code into account
1189 l_prompt := qa_plan_element_api.get_prompt(p_plan_id, p_char_id);
1190 l_uom_code := qa_plan_element_api.get_uom_code(p_plan_id, p_char_id);
1191
1192 IF (l_uom_code is not null) THEN
1193 RETURN l_prompt || ' (' || l_uom_code || ')';
1194 ELSE
1195 RETURN l_prompt;
1196 END IF;
1197 END get_prompt;
1198
1199
1200 FUNCTION create_region_item_for_eqr (
1201 p_char_id IN NUMBER,
1202 p_plan_id IN NUMBER,
1203 p_prefix IN VARCHAR2) RETURN JDR_DOCBUILDER.ELEMENT IS
1204
1205 -- Bug 3769260. shkalyan 29 July 2004.
1206 -- Removed these attributes since the cursor to get from qa_plan_chars
1207 -- and qa_chars are removed.
1208 /*
1209 c_displayed_flag qa_plan_chars.displayed_flag%TYPE;
1210 c_mandatory_flag qa_plan_chars.mandatory_flag%TYPE;
1211 c_prompt qa_plan_chars.prompt%TYPE;
1212 c_char_name qa_chars.name%TYPE;
1213 c_datatype qa_chars.datatype%TYPE;
1214 c_readonly_flag NUMBER;
1215 */
1216
1217 l_attribute_code VARCHAR2(30);
1218 l_vo_attribute_name VARCHAR2(30);
1219 l_display_flag VARCHAR2(10);
1220 l_required_flag VARCHAR2(10);
1221 l_item_style VARCHAR2(30);
1222 l_data_type VARCHAR2(30);
1223 l_display_sequence NUMBER;
1224 -- Bug 6491622
1225 -- Changing the length of the local variable l_prompt
1226 -- to a higher value - 100 and commenting out the existing code
1227 -- bhsankar Tue Oct 16 23:21:38 PDT 2007
1228 -- l_prompt qa_plan_chars.prompt%TYPE;
1229 l_prompt VARCHAR2(100);
1230
1231 child1 jdr_docbuilder.ELEMENT;
1232
1233 -- Tracking Bug : 3104827
1234 -- Added for Read Only Flag for Collection Plan Enhancement
1235 -- saugupta Thu Aug 28 08:58:53 PDT 2003
1236 l_readonly_flag VARCHAR2(10);
1237
1238 -- Bug 3769260. shkalyan 29 July 2004.
1239 -- Removed these cursors so that the cached arrays of qa_chars_api
1240 -- and qa_plan_element_api are used instead.
1241 /*
1242 CURSOR c1 IS
1243 SELECT displayed_flag, mandatory_flag, prompt, read_only_flag
1244 FROM qa_plan_chars
1245 WHERE plan_id = p_plan_id
1246 AND char_id = p_char_id;
1247
1248 CURSOR c2 IS
1249 SELECT name, datatype
1250 FROM qa_chars
1251 WHERE char_id = p_char_id;
1252 */
1253
1254 err_num NUMBER;
1255 err_msg VARCHAR2(100);
1256
1257 BEGIN
1258
1259 -- Bug 3769260. shkalyan 29 July 2004.
1260 -- Removed these cursors so that the cached arrays of qa_chars_api
1261 -- and qa_plan_element_api are used instead.
1262 /*
1263 OPEN c1;
1264 FETCH c1 INTO c_displayed_flag, c_mandatory_flag, c_prompt, c_readonly_flag;
1265 CLOSE c1;
1266
1267 OPEN c2;
1268 FETCH c2 INTO c_char_name, c_datatype;
1269 CLOSE c2;
1270 */
1271
1272 l_attribute_code := construct_jrad_code(g_element_prefix, p_char_id);
1273 -- This procedure adds a region item to the plan's eqr region.
1274
1275 l_attribute_code := construct_jrad_code(g_element_prefix, p_char_id);
1276
1277 l_vo_attribute_name := get_vo_attribute_name(p_char_id, p_plan_id);
1278
1279 l_display_flag := convert_boolean_flag( qa_plan_element_api.qpc_displayed_flag( p_plan_id, p_char_id ) );
1280
1281 l_required_flag := convert_yesno_flag( qa_plan_element_api.qpc_mandatory_flag( p_plan_id, p_char_id ) );
1282
1283 l_data_type := convert_data_type( qa_chars_api.datatype( p_char_id ) );
1284
1285 l_item_style := compute_item_style(p_prefix, p_char_id, p_plan_id);
1286
1287 -- Tracking Bug : 3104827
1288 -- Added for Read Only Flag Collection Plan Enhancement
1289 -- saugupta Thu Aug 28 08:59:59 PDT 2003
1290
1291 l_readonly_flag := convert_boolean_flag( qa_plan_element_api.qpc_read_only_flag( p_plan_id, p_char_id ) );
1292
1293 -- Bug 5455658. SHKALYAN 20-Sep-2006
1294 -- modified to invoke a local function which calls
1295 -- qa_plan_element_api.get_prompt instead of
1296 -- qa_chars_api.prompt because the latter would always return the prompt
1297 -- defined at the element level only and this is not desired.
1298 -- l_prompt := qa_chars_api.prompt( p_char_id );
1299 l_prompt := get_prompt(p_plan_id, p_char_id);
1300
1301 child1 := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS, l_item_style);
1302 jdr_docbuilder.setAttribute(child1, 'id', l_attribute_code);
1303 jdr_docbuilder.setAttribute(child1, 'rendered', l_display_flag);
1304 jdr_docbuilder.setAttribute(child1, 'prompt', l_prompt);
1305 jdr_docbuilder.setAttribute(child1, 'shortDesc', l_prompt);
1306 jdr_docbuilder.setAttribute(child1, 'required', l_required_flag);
1307 jdr_docbuilder.setAttribute(child1, 'dataType', l_data_type);
1308 jdr_docbuilder.setAttribute(child1, 'viewName', g_eqr_view_usage_name);
1309 jdr_docbuilder.setAttribute(child1, 'viewAttr', l_vo_attribute_name);
1310
1311 -- Tracking Bug : 3104827
1312 -- Added for Read Only Flag Collection Plan Enhancement
1313 -- saugupta Thu Aug 28 08:59:59 PDT 2003
1314
1315 jdr_docbuilder.setAttribute(child1, 'readOnly', l_readonly_flag);
1316
1317 -- At this point, if the element has lovs then we must determine
1318 -- what are its dependency and populate lov_relations
1319 -- with this information.
1320
1321 -- MOAC Project. 4637896
1322 -- Call new method to process lov item.
1323 -- srhariha. Tue Oct 11 04:22:16 PDT 2005.
1324 --
1325 IF (l_item_style = 'messageLovInput' ) THEN
1326 process_messageLovInput(p_plan_id,
1327 p_char_id,
1328 l_attribute_code,
1329 child1);
1330 END IF;
1331
1332
1333 RETURN child1;
1334
1335 -- dbms_output.put_line('Adding Item : ' || l_region_code || ' ' ||
1336 -- l_attribute_code);
1337
1338 EXCEPTION
1339
1340 WHEN OTHERS THEN
1341 err_num := SQLCODE;
1342 err_msg := SUBSTR(SQLERRM, 1, 100);
1343 -- dbms_output.put_line(err_msg);
1344
1345 END create_region_item_for_eqr;
1346
1347
1348 FUNCTION create_region_item_for_vqr (
1349 p_attribute_code IN VARCHAR2,
1350 p_plan_id IN VARCHAR2,
1351 p_prefix IN VARCHAR2,
1352 p_char_id IN NUMBER)
1353 RETURN JDR_DOCBUILDER.ELEMENT IS
1354
1355 l_row_id VARCHAR2(30);
1356 l_element_id NUMBER;
1357 l_region_code VARCHAR2(30);
1358 l_nested_region_code VARCHAR2(30) DEFAULT null;
1359 l_item_style VARCHAR2(30) DEFAULT 'messageStyledText';
1360 l_display_sequence NUMBER;
1361 l_display_flag VARCHAR2(1) DEFAULT 'Y';
1362 l_update_flag VARCHAR2(1) DEFAULT 'Y';
1363 l_query_flag VARCHAR2(30) DEFAULT NULL;
1364 l_view_attribute_name VARCHAR2(30) DEFAULT NULL;
1365 l_view_usage_name VARCHAR2(30) DEFAULT NULL;
1366 -- Bug 6491622
1367 -- Changing the length of the local variable l_label_long
1368 -- to a higher value - 100 and commenting out the existing code
1369 -- bhsankar Tue Oct 16 23:21:38 PDT 2007
1370 -- l_label_long VARCHAR2(30) DEFAULT NULL;
1371 l_label_long VARCHAR2(100) DEFAULT NULL;
1372 l_data_type VARCHAR2(30);
1373
1374 child1 jdr_docbuilder.ELEMENT;
1375
1376 err_num NUMBER;
1377 err_msg VARCHAR2(100);
1378
1379 BEGIN
1380
1381 -- This procedure adds a region item to the plan's vqr region.
1382
1383 -- bug 3178307. rkaza. timezone support. 10/06/2003
1384 -- Added datatype to vqr region items
1385
1386 l_region_code := construct_jrad_code(p_prefix, p_plan_id);
1387 l_view_usage_name := g_vqr_view_usage_name;
1388 -- parent-child for header single row region only
1389 IF (instr(p_prefix, g_pc_vqr_sin_prefix) <> 0)
1390 THEN
1391 l_view_usage_name := 'ParentResultVO';
1392 END IF;
1393
1394
1395 l_element_id := p_char_id;
1396
1397 l_view_attribute_name := qa_core_pkg.get_result_column_name (
1398 l_element_id, p_plan_id);
1399
1400 l_data_type := convert_data_type(qa_chars_api.datatype(p_char_id));
1401
1402 -- Bug 3769260. shkalyan 29 July 2004.
1403 -- Removed the get_label function which was getting prompt from
1404 -- qa_plan_chars. Using the qa_plan_element_api function to utilize cache
1405
1406 -- Bug 5455658. SHKALYAN 20-Sep-2006
1407 -- modified to invoke a local function which calls
1408 -- qa_plan_element_api.get_prompt and concatenates the UOM
1409 l_label_long := get_prompt(p_plan_id, l_element_id);
1410
1411 -- item_style is HIDDEN and if this element is a context element
1412 -- and is a query criteria (search flag is 1) for vqr
1413
1414 IF (query_criteria (l_region_code, l_element_id)) THEN
1415 l_item_style := 'formValue';
1416 END IF;
1417
1418 IF (instr(l_region_code, g_work_vqr_prefix) <> 0)
1419 OR (instr(l_region_code, g_asset_vqr_prefix) <> 0)
1420 --dgupta: Start R12 EAM Integration. Bug 4345492
1421 OR (instr(l_region_code, g_checkin_vqr_prefix) <> 0)
1422 OR (instr(l_region_code, g_checkout_vqr_prefix) <> 0)
1423 --dgupta: End R12 EAM Integration. Bug 4345492
1424 OR (instr(l_region_code, g_op_vqr_prefix) <> 0) THEN
1425 l_query_flag := 'true';
1426 END IF;
1427
1428 child1 := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS, l_item_style);
1429 jdr_docbuilder.setAttribute(child1, 'id', p_attribute_code);
1430 jdr_docbuilder.setAttribute(child1, 'rendered', 'true');
1431 jdr_docbuilder.setAttribute(child1, 'prompt', l_label_long);
1432 jdr_docbuilder.setAttribute(child1, 'dataType', l_data_type);
1433 jdr_docbuilder.setAttribute(child1, 'shortDesc', l_label_long);
1434 jdr_docbuilder.setAttribute(child1, 'viewName', l_view_usage_name);
1435 jdr_docbuilder.setAttribute(child1, 'viewAttr', l_view_attribute_name);
1436
1437 --ilawler - bug #3462025 - Mon Feb 23 17:48:09 2004
1438 --set the right CSS Class for data elements
1439 IF (instr(l_region_code, g_pc_vqr_sin_prefix) <> 0) THEN
1440 jdr_docbuilder.setAttribute(child1, 'styleClass', 'OraDataText');
1441 END IF;
1442
1443 IF l_query_flag IS NOT NULL THEN
1444 --dbms_output.put_line('found a queryable');
1445 jdr_docbuilder.setAttribute(child1, 'queryable', l_query_flag);
1446 END IF;
1447 RETURN child1;
1448
1449 -- dbms_output.put_line('Adding Item (S) : ' || l_region_code || ' ' ||
1450 -- p_attribute_code);
1451
1452 EXCEPTION
1453
1454 WHEN OTHERS THEN
1455 err_num := SQLCODE;
1456 err_msg := SUBSTR(SQLERRM, 1, 100);
1457 -- dbms_output.put_line(err_msg);
1458
1459 END create_region_item_for_vqr;
1460
1461
1462
1463 FUNCTION add_special_region_item (
1464 p_attribute_code IN VARCHAR2,
1465 p_plan_id IN VARCHAR2,
1466 p_prefix IN VARCHAR2,
1467 p_region_code IN VARCHAR2)
1468 RETURN jdr_docbuilder.ELEMENT IS
1469
1470 l_row_id VARCHAR2(30);
1471 l_element_id NUMBER;
1472 l_region_code VARCHAR2(30);
1473 --l_nested_region_code VARCHAR2(30) DEFAULT null;
1474 l_item_style VARCHAR2(30) DEFAULT 'formValue';
1475 --l_display_sequence NUMBER;
1476 --l_display_flag VARCHAR2(1) DEFAULT 'Y';
1477 l_restrict_attachment VARCHAR2(1) DEFAULT NULL;
1478 l_view_attribute_name VARCHAR2(30) DEFAULT NULL;
1479 l_view_usage_name VARCHAR2(30) DEFAULT NULL;
1480 l_label_long VARCHAR2(30) DEFAULT NULL;
1481 l_entity_id VARCHAR2(30) DEFAULT NULL;
1482 l_url VARCHAR2(240) DEFAULT NULL;
1483 l_image_file_name VARCHAR2(240) DEFAULT NULL;
1484 l_description VARCHAR2(240) DEFAULT NULL;
1485 --l_query_flag VARCHAR2(1) DEFAULT 'N';
1486 l_data_type VARCHAR2(30);
1487
1488 special_elem jdr_docbuilder.ELEMENT := NULL;
1489 l_entityMap jdr_docbuilder.ELEMENT := NULL;
1490
1491 err_num NUMBER;
1492 err_msg VARCHAR2(100);
1493
1494 BEGIN
1495
1496
1497 -- This function adds special region items to the region.
1498 -- 1. To add special elements for eqr (e.g. org_id, plan_id_ etc)
1499 -- 2. To add special elements for vqr (e.g. org_id, plan_id_ etc)
1500
1501 -- bug 3178307. rkaza. timezone support. 10/06/2003
1502 -- Added datatype datetime to last_update_date
1503
1504 l_region_code := p_region_code;
1505
1506 IF ( instr(p_prefix, g_vqr_prefix) = 1) THEN
1507
1508 -- Adding special elements for vqr
1509 l_view_usage_name := g_vqr_view_usage_name;
1510 l_item_style := 'messageStyledText';
1511 ELSE
1512
1513 -- Adding special elements for eqr
1514 l_view_usage_name := g_eqr_view_usage_name;
1515
1516 END IF;
1517
1518 l_view_attribute_name := get_hardcoded_vo_attr_name(
1519 p_attribute_code);
1520
1521 l_label_long := get_special_label(p_attribute_code);
1522
1523 -- added for attachments
1524 IF (p_attribute_code = g_single_row_attachment) OR
1525 (p_attribute_code = g_multi_row_attachment) THEN
1526 l_entity_id := 'QA_RESULTS';
1527
1528 l_view_attribute_name := '';
1529 --Tue Apr 29 15:03:47 2003, ilawler: static text replaced with a message
1530 l_label_long := fnd_message.get_string('QA', 'QA_SS_JRAD_ATTACHMENT');
1531 l_description := l_label_long;
1532
1533 IF (instr(l_region_code, g_txn_work_prefix) <> 0)
1534 OR (instr(l_region_code, g_txn_asset_prefix) <> 0)
1535 --dgupta: Start R12 EAM Integration. Bug 4345492
1536 OR (instr(l_region_code, g_checkin_eqr_prefix) <> 0)
1537 OR (instr(l_region_code, g_checkout_eqr_prefix) <> 0)
1538 --dgupta: End R12 EAM Integration. Bug 4345492
1539 OR (instr(l_region_code, g_txn_op_prefix) <> 0)
1540 AND (p_attribute_code = g_single_row_attachment) THEN
1541 l_item_style := 'attachmentLink';
1542 ELSE
1543 l_item_style := 'attachmentImage';
1544 END IF;
1545
1546 --ilawler - bug #3436725 - Thu Feb 12 14:39:38 2004
1547 --In VQR, set the attachment to be non-updateable/deletable/insertable
1548 IF (instr(p_prefix, g_vqr_prefix) = 1) THEN
1549 l_restrict_attachment := 'Y';
1550 END IF;
1551 END IF;
1552
1553 IF (p_attribute_code = g_update_attribute) THEN
1554
1555 -- dbms_output.put_line('Display Sequence: ' || l_display_sequence);
1556
1557 --l_query_flag := 'N';
1558 --Tue Apr 29 15:03:47 2003, ilawler: static text replaced with a message
1559 l_label_long := fnd_message.get_string('QA', 'QA_SS_JRAD_UPDATE');
1560 l_view_attribute_name := '';
1561 l_item_style := 'image';
1562 l_image_file_name := 'updateicon_enabled.gif';
1563
1564 -- rkaza. bug 3461988. EAM BLAF changes.
1565 -- Plan name has to be displayed in the page title
1566 -- Adding plan name as a parameter.
1567 l_url := '/OA_HTML/OA.jsp?akRegionCode=QA_DDE_EQR_PAGE' || '&' ||
1568 'akRegionApplicationId=250' || '&' ||
1569 'PlanId={@PLAN_ID}' || '&' ||
1570 'PlanName={@NAME}' || '&' ||
1571 'Occurrence={@OCCURRENCE}' || '&' ||
1572 'UCollectionId={@COLLECTION_ID}' || '&' ||
1573 'retainAM=Y' || '&' || 'addBreadCrumb=Y';
1574
1575 END IF;
1576
1577 -- parent-child
1578 IF (p_attribute_code = g_child_url_attribute) THEN
1579
1580 -- dbms_output.put_line('Display Sequence: ' || l_display_sequence);
1581
1582
1583 --l_query_flag := 'N';
1584 --Tue Apr 29 15:03:47 2003, ilawler: static text replaced with a message
1585 l_label_long := fnd_message.get_string('QA', 'QA_SS_JRAD_CHILD_PLANS');
1586 l_view_attribute_name := '';
1587 l_item_style := 'image';
1588 l_image_file_name := 'allocationbr_pagetitle.gif';--image changed!
1589 l_url := '/OA_HTML/OA.jsp?akRegionCode=QA_PC_RES_SUMMARY_PAGE'
1590 || '&' ||
1591 'akRegionApplicationId=250' || '&' ||
1592 'ParentPlanId={@PLAN_ID}' || '&' ||
1593 'ParentOccurrence={@OCCURRENCE}' || '&' ||
1594 'ParentCollectionId={@COLLECTION_ID}' || '&' ||
1595 'ParentPlanName={@NAME}' || '&' ||
1596 'retainAM=Y' || '&' || 'addBreadCrumb=Y' ;
1597 --breadcrumb added for bug 2331941
1598 END IF;
1599
1600 -- parent-child results inquiry ui improvement
1601 IF (p_attribute_code = g_vqr_all_elements_url) THEN
1602
1603 -- dbms_output.put_line('Display Sequence: ' || l_display_sequence);
1604
1605
1606 --l_query_flag := 'N';
1607 --Tue Apr 29 15:03:47 2003, ilawler: static text replaced with a message
1608 l_label_long := fnd_message.get_string('QA', 'QA_SS_JRAD_MORE_DETAILS');
1609 l_view_attribute_name := '';
1610 l_item_style := 'image';
1611 l_image_file_name := 'detailsicon_enabled.gif';
1612 l_url := '/OA_HTML/OA.jsp?akRegionCode=QA_PC_RES_VQR_DETAIL_PAGE'
1613 || '&' ||
1614 'akRegionApplicationId=250' || '&' ||
1615 'ParentPlanId={@PLAN_ID}' || '&' ||
1616 'ParentOccurrence={@OCCURRENCE}' || '&' ||
1617 'ParentCollectionId={@COLLECTION_ID}' || '&' ||
1618 'PlanName={@NAME}' || '&' ||
1619 'VqrParam=DETAILS' || '&' ||
1620 'retainAM=Y' || '&' || 'addBreadCrumb=Y';
1621 --breadcrumb added for bug 2331941
1622 END IF;
1623
1624 IF (p_attribute_code = g_last_update_date_attribute) THEN
1625 -- Assign a datatype of DATETIME to last_update_date.
1626 l_data_type := convert_data_type(qa_ss_const.datetime_datatype);
1627 END IF;
1628
1629 special_elem := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS,
1630 l_item_style);
1631 jdr_docbuilder.setAttribute(special_elem, 'id', p_attribute_code);
1632
1633 if (l_view_usage_name is not null) then
1634 jdr_docbuilder.setAttribute(special_elem, 'viewName',
1635 l_view_usage_name);
1636 end if;
1637 if (l_view_attribute_name is not null) then
1638 jdr_docbuilder.setAttribute(special_elem, 'viewAttr',
1639 l_view_attribute_name);
1640 end if;
1641 if (l_label_long is not null) then
1642 jdr_docbuilder.setAttribute(special_elem, 'prompt',
1643 l_label_long);
1644 end if;
1645 if (l_description is not null) then
1646 jdr_docbuilder.setAttribute(special_elem, 'shortDesc',
1647 l_description);
1648 end if;
1649 if (l_image_file_name is not null) then
1650 jdr_docbuilder.setAttribute(special_elem, 'source',
1651 l_image_file_name);
1652 end if;
1653 if (l_url is not null) then
1654 jdr_docbuilder.setAttribute(special_elem, 'destination',
1655 replace(l_url, '&', '&'));
1656 end if;
1657 if (l_data_type is not null) then
1658 jdr_docbuilder.setAttribute(special_elem, 'dataType', l_data_type);
1659 end if;
1660 if (l_entity_id is not null) then
1661 --special handling for attachments
1662 l_entityMap := jdr_docbuilder.createElement(jdr_docbuilder.OA_NS,
1663 'entityMap');
1664 jdr_docbuilder.setAttribute(l_entityMap, 'entityId', l_entity_id);
1665
1666 --ilawler - bug #3436725 - Thu Feb 12 14:39:38 2004
1667 --In VQR, set the attachment to be non-updateable/deletable/insertable
1668 IF (l_restrict_attachment = 'Y') THEN
1669 jdr_docbuilder.setAttribute(l_entityMap, 'insertAllowed', 'false');
1670 jdr_docbuilder.setAttribute(l_entityMap, 'updateAllowed', 'false');
1671 jdr_docbuilder.setAttribute(l_entityMap, 'deleteAllowed', 'false');
1672 END IF;
1673
1674 jdr_docbuilder.addChild(special_elem, jdr_docbuilder.OA_NS,
1675 'entityMappings', l_entityMap);
1676 end if;--entity id not null
1677
1678 return special_elem;
1679
1680 EXCEPTION
1681
1682 WHEN OTHERS THEN
1683 err_num := SQLCODE;
1684 err_msg := SUBSTR(SQLERRM, 1, 100);
1685 -- dbms_output.put_line(err_msg);
1686
1687 END add_special_region_item;
1688
1689
1690 PROCEDURE map_plan_for_eqr (
1691 p_plan_id IN VARCHAR2,
1692 p_prefix IN VARCHAR2,
1693 p_jrad_doc_ver IN NUMBER) IS
1694 --p_jrad_doc_ver cannot be null for now
1695 --in future, pass null explicitly if necessary
1696
1697 l_element_id NUMBER;
1698 l_region_code VARCHAR2(30);
1699 l_old_doc_name VARCHAR2(255);
1700 l_saved PLS_INTEGER;
1701
1702 err_num NUMBER;
1703 err_msg VARCHAR2(100);
1704
1705 qa_jrad_doc JDR_DOCBUILDER.DOCUMENT := NULL;
1706 topLevel JDR_DOCBUILDER.ELEMENT := NULL;
1707 child1 JDR_DOCBUILDER.ELEMENT := NULL;
1708 -- MOAC
1709 l_id_item JDR_DOCBUILDER.ELEMENT := NULL;
1710
1711 CURSOR c IS
1712 SELECT char_id
1713 FROM qa_plan_chars
1714 WHERE plan_id = p_plan_id
1715 AND enabled_flag = 1;
1716
1717 BEGIN
1718
1719 --qa_ian_pkg.write_log('Entered EQR, p_prefix: "'||p_prefix||'", p_jrad_doc_ver: '||p_jrad_doc_ver);
1720
1721 l_region_code := construct_jrad_code(p_prefix, p_plan_id, p_jrad_doc_ver);
1722
1723 --dbms_output.put_line('Entered EQR, path: "'||g_jrad_region_path||l_region_code||'", p_jrad_doc_ver: '||p_jrad_doc_ver);
1724 qa_jrad_doc := JDR_DOCBUILDER.createDocument(g_jrad_region_path || l_region_code, 'en-US');
1725
1726 topLevel := create_jrad_region (l_region_code, p_plan_id);
1727 JDR_DOCBUILDER.setTopLevelElement(qa_jrad_doc, topLevel);
1728
1729 OPEN c;
1730 LOOP
1731 FETCH c INTO l_element_id;
1732 EXIT WHEN c%NOTFOUND;
1733
1734 -- we have taken a decision to not to display an element
1735 -- for data entry if the element is a potenital target
1736 -- for a assigned a value action.
1737
1738 IF (NOT action_target_element(p_plan_id, l_element_id)) THEN
1739
1740 child1 := create_region_item_for_eqr(
1741 l_element_id, p_plan_id, p_prefix);
1742 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1743 'contents', child1);
1744 --contents is the grouping name
1745
1746 -- For MOAC : add normalized column.
1747 IF l_element_id = qa_ss_const.po_number THEN
1748 l_id_item := create_id_item_for_eqr(p_plan_id,l_element_id);
1749 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,'contents',l_id_item);
1750 END IF;
1751
1752
1753 END IF;
1754
1755 END LOOP;
1756 CLOSE c;
1757
1758 child1 := add_special_region_item (
1759 p_attribute_code => g_org_id_attribute,
1760 p_plan_id => p_plan_id,
1761 p_prefix => p_prefix,
1762 p_region_code => l_region_code);
1763
1764 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1765 'contents', child1);
1766
1767 child1 := add_special_region_item (
1768 p_attribute_code => g_org_code_attribute,
1769 p_plan_id => p_plan_id,
1770 p_prefix => p_prefix,
1771 p_region_code => l_region_code);
1772 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1773 'contents', child1);
1774
1775 child1 := add_special_region_item (
1776 p_attribute_code => g_plan_id_attribute,
1777 p_plan_id => p_plan_id,
1778 p_prefix => p_prefix,
1779 p_region_code => l_region_code);
1780 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1781 'contents', child1);
1782
1783 child1 := add_special_region_item (
1784 p_attribute_code => g_plan_name_attribute,
1785 p_plan_id => p_plan_id,
1786 p_prefix => p_prefix,
1787 p_region_code => l_region_code);
1788 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1789 'contents', child1);
1790
1791 child1 := add_special_region_item (
1792 p_attribute_code => g_process_status_attribute,
1793 p_plan_id => p_plan_id,
1794 p_prefix => p_prefix,
1795 p_region_code => l_region_code);
1796 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1797 'contents', child1);
1798
1799 child1 := add_special_region_item (
1800 p_attribute_code => g_source_code_attribute,
1801 p_plan_id => p_plan_id,
1802 p_prefix => p_prefix,
1803 p_region_code => l_region_code);
1804 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1805 'contents', child1);
1806
1807 child1 := add_special_region_item (
1808 p_attribute_code => g_source_line_id_attribute,
1809 p_plan_id => p_plan_id,
1810 p_prefix => p_prefix,
1811 p_region_code => l_region_code);
1812 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1813 'contents', child1);
1814
1815 child1 := add_special_region_item (
1816 p_attribute_code => g_po_agent_id_attribute,
1817 p_plan_id => p_plan_id,
1818 p_prefix => p_prefix,
1819 p_region_code => l_region_code);
1820 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1821 'contents', child1);
1822 -- added for attachments
1823
1824 IF (instr(l_region_code, g_txn_work_prefix) <> 0)
1825 OR (instr(l_region_code, g_txn_asset_prefix) <> 0)
1826 --dgupta: Start R12 EAM Integration. Bug 4345492
1827 OR (instr(l_region_code, g_checkin_eqr_prefix) <> 0)
1828 OR (instr(l_region_code, g_checkout_eqr_prefix) <> 0)
1829 --dgupta: End R12 EAM Integration. Bug 4345492
1830 OR (instr(l_region_code, g_txn_op_prefix) <> 0) THEN
1831
1832 -- code branch for eam eqr, so make it single
1833 child1 := add_special_region_item (
1834 p_attribute_code => g_single_row_attachment,
1835 p_plan_id => p_plan_id,
1836 p_prefix => p_prefix,
1837 p_region_code => l_region_code);
1838 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1839 'contents', child1);
1840 ELSE
1841 child1 := add_special_region_item (
1842 p_attribute_code => g_multi_row_attachment,
1843 p_plan_id => p_plan_id,
1844 p_prefix => p_prefix,
1845 p_region_code => l_region_code);
1846 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1847 'contents', child1);
1848 END IF;
1849
1850 l_saved := JDR_DOCBUILDER.SAVE;
1851
1852 --if saved went ok and a previous, lower version document exists
1853 --then try to delete the previous document
1854 l_old_doc_name := g_jrad_region_path || construct_jrad_code(p_prefix, p_plan_id, p_jrad_doc_ver-1);
1855 IF (l_saved = jdr_docbuilder.SUCCESS) AND
1856 (p_jrad_doc_ver > 1) AND
1857 (jdr_docbuilder.documentExists(l_old_doc_name)) THEN
1858 jdr_docbuilder.deleteDocument(l_old_doc_name);
1859 END IF;
1860
1861 --qa_ian_pkg.write_log('EQR Dumping Document(l_saved): "'||g_jrad_region_path || l_region_code||'"('||l_saved||')');
1862 --qa_ian_pkg.write_log(jdr_mds_internal.exportsingledocument(jdr_mds_internal.GETDOCUMENTID(g_jrad_region_path || l_region_code, 'DOCUMENT'), true));
1863 --qa_ian_pkg.write_log('EQR Done Dumping Document: "'||g_jrad_region_path || l_region_code||'"');
1864
1865 -- dbms_output.put_line('-------------------------------------------');
1866
1867 EXCEPTION
1868
1869 WHEN OTHERS THEN
1870 err_num := SQLCODE;
1871 err_msg := SUBSTR(SQLERRM, 1, 100);
1872 -- dbms_output.put_line(err_msg);
1873
1874 END map_plan_for_eqr;
1875
1876
1877 PROCEDURE map_plan_for_vqr (
1878 p_plan_id IN VARCHAR2,
1879 p_prefix IN VARCHAR2,
1880 p_jrad_doc_ver IN NUMBER) IS
1881 --p_jrad_doc_ver cannot be null for now
1882 --in future, pass null explicitly if necessary
1883
1884 l_element_id NUMBER;
1885 l_region_code VARCHAR2(30);
1886 l_attribute_code VARCHAR2(30);
1887 l_old_doc_name VARCHAR2(255);
1888 l_saved PLS_INTEGER;
1889
1890 err_num NUMBER;
1891 err_msg VARCHAR2(100);
1892 elmt_counter NUMBER;--parent-child results inquiry
1893
1894 qa_jrad_doc JDR_DOCBUILDER.DOCUMENT := NULL;
1895 topLevel JDR_DOCBUILDER.ELEMENT := NULL;
1896 child1 JDR_DOCBUILDER.ELEMENT := NULL;
1897
1898 CURSOR c IS
1899 SELECT char_id
1900 FROM qa_plan_chars
1901 WHERE plan_id = p_plan_id
1902 AND enabled_flag = 1
1903 ORDER BY PROMPT_SEQUENCE;
1904
1905 BEGIN
1906
1907 --qa_ian_pkg.write_log('Entered VQR, p_prefix: "'||p_prefix||'", p_jrad_doc_ver: '||p_jrad_doc_ver);
1908 l_region_code := construct_jrad_code(p_prefix, p_plan_id, p_jrad_doc_ver);
1909
1910 --dbms_output.put_line('Entered VQR, path: "'||g_jrad_region_path||l_region_code||'", p_jrad_doc_ver: '||p_jrad_doc_ver);
1911
1912 qa_jrad_doc := JDR_DOCBUILDER.createDocument(g_jrad_region_path || l_region_code, 'en-US');
1913
1914 topLevel := create_jrad_region (l_region_code, p_plan_id);
1915 JDR_DOCBUILDER.setTopLevelElement(qa_jrad_doc, topLevel);
1916
1917 elmt_counter := 0; --parent-child results inquiry
1918 --initialize counter
1919 OPEN c;
1920 LOOP
1921 FETCH c INTO l_element_id;
1922 EXIT WHEN c%NOTFOUND;
1923
1924 elmt_counter := elmt_counter + 1; --parent-child
1925 --dbms_output.put_line('counter' || elmt_counter);
1926 l_attribute_code := construct_jrad_code(g_element_prefix,l_element_id);
1927
1928 -- For Eam transactions if it is an action target then this element
1929 -- should not show up in VQR region.
1930 IF (instr(l_region_code, g_work_vqr_prefix) <> 0)
1931 OR (instr(l_region_code, g_asset_vqr_prefix) <> 0)
1932 --dgupta: Start R12 EAM Integration. Bug 4345492
1933 OR (instr(l_region_code, g_checkin_vqr_prefix) <> 0)
1934 OR (instr(l_region_code, g_checkout_vqr_prefix) <> 0)
1935 --dgupta: End R12 EAM Integration. Bug 4345492
1936 OR (instr(l_region_code, g_op_vqr_prefix) <> 0) THEN
1937 IF (NOT action_target_element(p_plan_id, l_element_id)) THEN
1938 child1 := create_region_item_for_vqr (
1939 p_attribute_code => l_attribute_code,
1940 p_plan_id => p_plan_id,
1941 p_prefix => p_prefix,
1942 p_char_id => l_element_id);
1943 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1944 'contents', child1);
1945 --contents is the grouping name
1946 END IF;
1947
1948 ELSE
1949 IF (instr(l_region_code, g_pc_vqr_prefix) <> 0 and
1950 elmt_counter > 4) THEN
1951 --if this is a parent child multi-row vqr screen
1952 EXIT; --exit the loop. Dont show more elements
1953 END IF; --parent-child
1954
1955 child1 := create_region_item_for_vqr (
1956 p_attribute_code => l_attribute_code,
1957 p_plan_id => p_plan_id,
1958 p_prefix => p_prefix,
1959 p_char_id => l_element_id);
1960 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1961 'contents', child1);
1962 --contents is the grouping name
1963 END IF;
1964
1965 END LOOP;
1966 CLOSE c;
1967
1968 IF (instr(l_region_code, g_pc_vqr_sin_prefix) = 0) THEN
1969 -- means if this is "not" single row parent vqr region
1970 child1 := add_special_region_item (p_attribute_code => g_qa_created_by_attribute,
1971 p_plan_id => p_plan_id,
1972 p_prefix => p_prefix,
1973 p_region_code => l_region_code);
1974 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1975 'contents', child1);
1976
1977 child1 := add_special_region_item (p_attribute_code => g_collection_id_attribute,
1978 p_plan_id => p_plan_id,
1979 p_prefix => p_prefix,
1980 p_region_code => l_region_code);
1981 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1982 'contents', child1);
1983
1984 child1 := add_special_region_item (p_attribute_code => g_last_update_date_attribute,
1985 p_plan_id => p_plan_id,
1986 p_prefix => p_prefix,
1987 p_region_code => l_region_code);
1988 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1989 'contents', child1);
1990
1991 -- added for attachments
1992 child1 := add_special_region_item (p_attribute_code => g_multi_row_attachment,
1993 p_plan_id => p_plan_id,
1994 p_prefix => p_prefix,
1995 p_region_code => l_region_code);
1996 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
1997 'contents', child1);
1998
1999 -- added for update capability
2000 IF (instr(l_region_code, g_work_vqr_prefix) <> 0)
2001 OR (instr(l_region_code, g_asset_vqr_prefix) <> 0)
2002 --dgupta: Start R12 EAM Integration. Bug 4345492
2003 OR (instr(l_region_code, g_checkin_vqr_prefix) <> 0)
2004 OR (instr(l_region_code, g_checkout_vqr_prefix) <> 0)
2005 --dgupta: End R12 EAM Integration. Bug 4345492
2006 OR (instr(l_region_code, g_op_vqr_prefix) <> 0) THEN
2007 child1 := add_special_region_item (p_attribute_code => g_update_attribute,
2008 p_plan_id => p_plan_id,
2009 p_prefix => p_prefix,
2010 p_region_code => l_region_code);
2011 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
2012 'contents', child1);
2013 END IF;
2014
2015 -- parent-child
2016 IF (instr(l_region_code, g_pc_vqr_prefix) <> 0) THEN
2017 --if this is a parent child results inquiry multi-row vqr screen
2018 child1 := add_special_region_item (p_attribute_code => g_child_url_attribute,
2019 p_plan_id => p_plan_id,
2020 p_prefix => p_prefix,
2021 p_region_code => l_region_code);
2022 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
2023 'contents', child1);
2024
2025 --below introduced for ui improvement
2026 --link to click and see all coll.elements for a vqr row
2027 -- (More Details Link)
2028 --one 'nice' feedback here is that make this call only if the
2029 --total no of collection elements is greater than 4
2030 --since this link is not needed otherwise
2031 --this additional check can be coded here...
2032 --
2033 child1 := add_special_region_item (p_attribute_code => g_vqr_all_elements_url,
2034 p_plan_id => p_plan_id,
2035 p_prefix => p_prefix,
2036 p_region_code => l_region_code);
2037 JDR_DOCBUILDER.addChild(topLevel, JDR_DOCBUILDER.UI_NS,
2038 'contents', child1);
2039 END IF;
2040 END IF; --end "outer if" stmt: "not" single row parent vqr region
2041
2042 l_saved := JDR_DOCBUILDER.SAVE;
2043
2044 --if saved went ok and a previous, lower version document exists
2045 --then try to delete the previous document
2046 l_old_doc_name := g_jrad_region_path || construct_jrad_code(p_prefix, p_plan_id, p_jrad_doc_ver-1);
2047 IF (l_saved = jdr_docbuilder.SUCCESS) AND
2048 (p_jrad_doc_ver > 1) AND
2049 (jdr_docbuilder.documentExists(l_old_doc_name)) THEN
2050 jdr_docbuilder.deleteDocument(l_old_doc_name);
2051 END IF;
2052
2053 --qa_ian_pkg.write_log('VQR Dumping Document(l_saved): "'||g_jrad_region_path || l_region_code||'"('||l_saved||')');
2054 --qa_ian_pkg.write_log(jdr_mds_internal.exportsingledocument(jdr_mds_internal.GETDOCUMENTID(g_jrad_region_path || l_region_code, 'DOCUMENT'), true));
2055 --qa_ian_pkg.write_log('VQR Done Dumping Document: "'||g_jrad_region_path || l_region_code||'"');
2056
2057 EXCEPTION
2058
2059 WHEN OTHERS THEN
2060 err_num := SQLCODE;
2061 err_msg := SUBSTR(SQLERRM, 1, 100);
2062 --qa_ian_pkg.write_log('Encountered Error: SQLCODE('||SQLCODE||'), SQLERRM('||SQLERRM||')');
2063
2064 -- dbms_output.put_line(err_msg);
2065
2066 END map_plan_for_vqr;
2067
2068
2069 PROCEDURE map_plan(
2070 p_plan_id IN NUMBER,
2071 p_jrad_doc_ver IN NUMBER) IS --default null defined in spec
2072
2073 -- if p_jrad_doc_ver is passed in, then dont increment it
2074 -- use that as the version of the new document to be created
2075
2076 -- Tracking Bug 4697145
2077 -- MOAC Upgrade feature. This is not required.
2078 -- PRAGMA AUTONOMOUS_TRANSACTION;
2079
2080 associated BOOLEAN DEFAULT FALSE;
2081 asset_mapped BOOLEAN DEFAULT FALSE;
2082 l_jrad_doc_ver NUMBER := NULL;
2083
2084 --
2085 -- Tracking Bug 4697145
2086 -- MOAC Upgrade feature.
2087 -- Removed the FOR UPDATE clause.
2088 -- bso Sun Nov 6 16:52:53 PST 2005
2089 --
2090 CURSOR jrad_doc_ver_cur IS
2091 SELECT jrad_doc_ver
2092 FROM QA_PLANS
2093 WHERE PLAN_ID = p_plan_id;
2094
2095 BEGIN
2096
2097 -- This procedure maps a collection plan to ak tables.
2098 --
2099
2100 --qa_ian_pkg.init_log('jrad_t1');
2101 -- To avoid hitting the database multiple times for sysdate.
2102 g_sysdate := SYSDATE;
2103 --dbms_output.put_line('entered');
2104
2105 IF (p_jrad_doc_ver IS NULL)THEN
2106
2107 --qa_ian_pkg.write_log('p_jrad_doc_ver was NULL');
2108 OPEN jrad_doc_ver_cur;
2109 FETCH jrad_doc_ver_cur INTO l_jrad_doc_ver;
2110 CLOSE jrad_doc_ver_cur;
2111
2112 IF l_jrad_doc_ver IS NULL THEN
2113 l_jrad_doc_ver := 1;
2114 ELSE
2115 l_jrad_doc_ver := l_jrad_doc_ver + 1; --increment
2116 END IF;
2117 ELSE
2118 --dont open the cursor since record has already been locked
2119 --by calling procedure, and increment has happened
2120 l_jrad_doc_ver := p_jrad_doc_ver;
2121 END IF;
2122
2123 IF osp_self_service_plan(p_plan_id) THEN
2124
2125 -- dbms_output.put_line('mapping for osp');
2126 map_plan_for_eqr(p_plan_id, g_txn_osp_prefix ,l_jrad_doc_ver);
2127 map_plan_for_vqr (p_plan_id, g_osp_vqr_prefix ,l_jrad_doc_ver);
2128
2129 --for project3 parent-child
2130 --find out all descendants and call map plan for eqr and vqr
2131 --for all these descendants
2132 END IF;
2133
2134 IF shipment_self_service_plan(p_plan_id) THEN
2135
2136 -- dbms_output.put_line('mapping for shipment');
2137 map_plan_for_eqr(p_plan_id, g_txn_ship_prefix ,l_jrad_doc_ver);
2138
2139 map_plan_for_vqr (p_plan_id, g_ship_vqr_prefix ,l_jrad_doc_ver);
2140
2141 --for project3 parent-child
2142 --find out all descendants and call map plan for eqr and vqr
2143 --for all these descendants
2144 END IF;
2145
2146 IF customer_portal_plan(p_plan_id) THEN
2147
2148 -- dbms_output.put_line('mapping for OM');
2149 map_plan_for_vqr (p_plan_id, g_om_vqr_prefix ,l_jrad_doc_ver);
2150 END IF;
2151
2152 IF eam_asset_plan(p_plan_id) THEN
2153
2154 if not asset_mapped then
2155 map_plan_for_eqr(p_plan_id, g_txn_asset_prefix ,l_jrad_doc_ver);
2156 map_plan_for_vqr (p_plan_id, g_asset_vqr_prefix ,l_jrad_doc_ver);
2157 asset_mapped := TRUE;
2158 end if;
2159
2160 END IF;
2161
2162 --dgupta: Start R12 EAM Integration. Bug 4345492
2163 IF eam_checkin_plan(p_plan_id) THEN
2164 map_plan_for_eqr(p_plan_id, g_checkin_eqr_prefix ,l_jrad_doc_ver);
2165 map_plan_for_vqr (p_plan_id, g_checkin_vqr_prefix ,l_jrad_doc_ver);
2166
2167 if not asset_mapped then
2168 map_plan_for_eqr(p_plan_id, g_txn_asset_prefix ,l_jrad_doc_ver);
2169 map_plan_for_vqr (p_plan_id, g_asset_vqr_prefix ,l_jrad_doc_ver);
2170 asset_mapped := TRUE;
2171 end if;
2172
2173 END IF;
2174
2175
2176 IF eam_checkout_plan(p_plan_id) THEN
2177 map_plan_for_eqr(p_plan_id, g_checkout_eqr_prefix ,l_jrad_doc_ver);
2178 map_plan_for_vqr (p_plan_id, g_checkout_vqr_prefix ,l_jrad_doc_ver);
2179
2180 if not asset_mapped then
2181 map_plan_for_eqr(p_plan_id, g_txn_asset_prefix ,l_jrad_doc_ver);
2182 map_plan_for_vqr (p_plan_id, g_asset_vqr_prefix ,l_jrad_doc_ver);
2183 asset_mapped := TRUE;
2184 end if;
2185
2186 END IF;
2187 --dgupta: End R12 EAM Integration. Bug 4345492
2188
2189 IF eam_work_order_plan(p_plan_id) THEN
2190
2191 -- dbms_output.put_line('mapping for EAM');
2192 map_plan_for_eqr(p_plan_id, g_txn_work_prefix ,l_jrad_doc_ver);
2193 map_plan_for_vqr (p_plan_id, g_work_vqr_prefix ,l_jrad_doc_ver);
2194
2195 if not asset_mapped then
2196 map_plan_for_eqr(p_plan_id, g_txn_asset_prefix ,l_jrad_doc_ver);
2197 map_plan_for_vqr (p_plan_id, g_asset_vqr_prefix ,l_jrad_doc_ver);
2198 asset_mapped := TRUE;
2199 end if;
2200
2201 END IF;
2202
2203 IF eam_op_comp_plan(p_plan_id) THEN
2204
2205 -- dbms_output.put_line('mapping for EAM');
2206 map_plan_for_eqr(p_plan_id, g_txn_op_prefix ,l_jrad_doc_ver);
2207 map_plan_for_vqr (p_plan_id, g_op_vqr_prefix ,l_jrad_doc_ver);
2208
2209 if not asset_mapped then
2210 map_plan_for_eqr(p_plan_id, g_txn_asset_prefix ,l_jrad_doc_ver);
2211 map_plan_for_vqr (p_plan_id, g_asset_vqr_prefix ,l_jrad_doc_ver);
2212 asset_mapped := TRUE;
2213 end if;
2214
2215 END IF;
2216
2217 -- Parent-Child
2218 IF parent_child_plan(p_plan_id) THEN
2219 map_plan_for_vqr (p_plan_id, g_pc_vqr_prefix ,l_jrad_doc_ver);
2220 map_plan_for_vqr (p_plan_id, g_pc_vqr_sin_prefix ,l_jrad_doc_ver);
2221
2222 --below info when we do project3 parent-child
2223 --this is called from qa_ss_parent_child_pkg
2224 --in that call check for isp and make appropriate map calls
2225 END IF;
2226
2227 IF (p_jrad_doc_ver IS NULL) THEN
2228 UPDATE QA_PLANS
2229 SET JRAD_DOC_VER = l_jrad_doc_ver
2230 WHERE PLAN_ID = p_plan_id;
2231 ELSE
2232 NULL; --dont update here...calling procedure will update
2233 --since calling procedure has a lock
2234 END IF;
2235 --qa_ian_pkg.close_log;
2236
2237 -- Tracking Bug 4697145
2238 -- MOAC Upgrade feature. This is not required.
2239 -- COMMIT; --commit the autonomous txn, so all locks are released
2240
2241 END map_plan;
2242
2243 PROCEDURE map_on_demand (p_plan_id IN NUMBER,
2244 x_jrad_doc_ver OUT NOCOPY NUMBER)
2245
2246 IS
2247 PRAGMA AUTONOMOUS_TRANSACTION;
2248
2249 l_jrad_doc_ver NUMBER;
2250 l_jrad_upgrade_ver NUMBER;
2251 l_seed_ver NUMBER;
2252
2253 --
2254 -- Tracking Bug 4697145
2255 -- MOAC Upgrade feature.
2256 -- Rewritten cursors in inline SQL for convenience.
2257 -- bso Sun Nov 6 17:07:45 PST 2005
2258 --
2259
2260 BEGIN
2261 --Bug 2946779
2262 --reset the message table so that stale errors aren't
2263 --thrown by the checkErrors on this procedure
2264 --ilawler Thu May 8 11:19:27 2003
2265 fnd_msg_pub.initialize;
2266
2267 --
2268 -- Tracking Bug 4697145
2269 -- MOAC Upgrade feature.
2270 -- bso Sun Nov 6 17:34:31 PST 2005
2271 --
2272 SELECT jrad_upgrade_ver
2273 INTO l_jrad_upgrade_ver
2274 FROM qa_plans
2275 WHERE plan_id = p_plan_id
2276 FOR UPDATE;
2277
2278 SELECT jrad_upgrade_ver
2279 INTO l_seed_ver
2280 FROM qa_plans
2281 WHERE plan_id = qa_ss_const.JRAD_UPGRADE_PLAN;
2282
2283 IF l_jrad_upgrade_ver IS NULL OR
2284 l_jrad_upgrade_ver < l_seed_ver THEN
2285 qa_ssqr_jrad_pkg.map_plan(p_plan_id);
2286 qa_ssqr_jrad_pkg.jrad_upgraded(p_plan_id);
2287 map_plan(p_plan_id);
2288 END IF;
2289
2290 --
2291 -- Since map_plan may be called above and may change
2292 -- jrad_doc_ver number, now re-check Jrad Doc Ver
2293 --
2294 SELECT jrad_doc_ver
2295 INTO l_jrad_doc_ver
2296 FROM qa_plans
2297 WHERE plan_id = p_plan_id;
2298
2299 IF l_jrad_doc_ver IS NULL THEN
2300 map_plan(p_plan_id);
2301 END IF;
2302
2303 x_jrad_doc_ver := nvl(l_jrad_doc_ver, 1); --set out variable
2304
2305 COMMIT;
2306 END map_on_demand;
2307
2308
2309 FUNCTION context_element (element_id IN NUMBER, txn_number IN NUMBER)
2310 RETURN BOOLEAN IS
2311
2312 result BOOLEAN;
2313 dummy NUMBER;
2314
2315 CURSOR c IS
2316 SELECT 1
2317 FROM qa_txn_collection_triggers qtct
2318 WHERE qtct.transaction_number = txn_number
2319 AND qtct.collection_trigger_id = element_id;
2320
2321 BEGIN
2322
2323 -- This function determines if collection element is a context element
2324 -- given a transaction number.
2325
2326 OPEN c;
2327 FETCH c INTO dummy;
2328 result := c%FOUND;
2329 CLOSE c;
2330
2331 RETURN result;
2332
2333 END context_element;
2334
2335
2336 END qa_jrad_pkg;
2337
[Home] [Help]