[Home] [Help]
PACKAGE BODY: APPS.QA_AK_MAPPING_API
Source
1 PACKAGE BODY qa_ak_mapping_api AS
2 /* $Header: qltakmpb.plb 120.1 2005/06/10 17:27:54 appldev $ */
3
4
5 TYPE ParentArray IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6
7 g_sysdate DATE;
8
9
10 FUNCTION osp_self_service_plan (p_plan_id IN NUMBER)
11 RETURN BOOLEAN IS
12
13 result BOOLEAN;
14 dummy NUMBER;
15
16 CURSOR c IS
17 SELECT 1
18 FROM qa_plan_transactions
19 WHERE plan_id = p_plan_id
20 AND transaction_number = qa_ss_const.ss_outside_processing_txn
21 AND enabled_flag = 1;
22
23 BEGIN
24
25 -- This subroutine determines if a plan has been associated
26 -- and enabled for osp self service transactions or not.
27 -- If it is then it returns true else it returns false.
28
29 OPEN c;
30 FETCH c INTO dummy;
31 result := c%FOUND;
32 CLOSE c;
33 RETURN result;
34
35 END osp_self_service_plan;
36
37
38 FUNCTION shipment_self_service_plan (p_plan_id IN NUMBER)
39 RETURN BOOLEAN IS
40
41 result BOOLEAN;
42 dummy NUMBER;
43
44 CURSOR c IS
45 SELECT 1
46 FROM qa_plan_transactions
47 WHERE plan_id = p_plan_id
48 AND transaction_number = qa_ss_const.ss_shipments_txn
49 AND enabled_flag = 1;
50
51 BEGIN
52
53 -- This subroutine determines if a plan has been associated
54 -- and enabled for shipment self service transactions or not.
55 -- If it is then it returns true else it returns false.
56
57 OPEN c;
58 FETCH c INTO dummy;
59 result := c%FOUND;
60 CLOSE c;
61 RETURN result;
62
63 END shipment_self_service_plan;
64
65
66 FUNCTION customer_portal_plan (p_plan_id IN NUMBER)
67 RETURN BOOLEAN IS
68
69 BEGIN
70
71 -- This subroutine determines if a plan should be mapped
72 -- for customer portal (OM). This can be determined
73 -- by checking if sales order is a part of the plan
74 -- and enabled.
75
76 IF (qa_plan_element_api.element_in_plan(p_plan_id,
77 qa_ss_const.sales_order)) THEN
78 RETURN TRUE;
79 ELSE
80 RETURN FALSE;
81 END IF;
82
83 END customer_portal_plan;
84
85
86 FUNCTION eam_work_order_plan (p_plan_id IN NUMBER)
87 RETURN BOOLEAN IS
88
89 CURSOR c IS
90 SELECT 1
91 FROM qa_plan_transactions
92 WHERE plan_id = p_plan_id
93 AND transaction_number = 31
94 AND enabled_flag = 1;
95
96 result BOOLEAN;
97 dummy NUMBER;
98
99 BEGIN
100
101 -- This subroutine determines if a plan has been associated
102 -- and enabled for EAM work order or not. If it is then it returns true
103 -- else it returns false.
104
105 OPEN c;
106 FETCH c INTO dummy;
107 result := c%FOUND;
108 CLOSE c;
109
110 RETURN result;
111
112 END eam_work_order_plan;
113
114
115
116 FUNCTION eam_asset_plan (p_plan_id IN NUMBER)
117 RETURN BOOLEAN IS
118
119 CURSOR c IS
120 SELECT 1
121 FROM qa_plan_transactions
122 WHERE plan_id = p_plan_id
123 AND transaction_number = 32
124 AND enabled_flag = 1;
125
126 result BOOLEAN;
127 dummy NUMBER;
128
129 BEGIN
130
131 -- This subroutine determines if a plan has been associated
132 -- and enabled for EAM asset query or not. If it is then it returns true
133 -- else it returns false.
134
135 OPEN c;
136 FETCH c INTO dummy;
137 result := c%FOUND;
138 CLOSE c;
139
140 RETURN result;
141
142 END eam_asset_plan;
143
144
145
146 FUNCTION eam_op_comp_plan (p_plan_id IN NUMBER)
147 RETURN BOOLEAN IS
148
149 CURSOR c IS
150 SELECT 1
151 FROM qa_plan_transactions
152 WHERE plan_id = p_plan_id
153 AND transaction_number = 33
154 AND enabled_flag = 1;
155
156 result BOOLEAN;
157 dummy NUMBER;
158
159 BEGIN
160
161 -- This subroutine determines if a plan has been associated
162 -- and enabled for EAM op comp or not. If it is then it returns true
163 -- else it returns false.
164
165 OPEN c;
166 FETCH c INTO dummy;
167 result := c%FOUND;
168 CLOSE c;
169
170 RETURN result;
171
172 END eam_op_comp_plan;
173
174
175
176 -- Parent-Child
177 FUNCTION parent_child_plan (p_plan_id IN NUMBER)
178 RETURN BOOLEAN IS
179
180 CURSOR c IS
181 SELECT 1
182 FROM qa_pc_plan_relationship
183 WHERE parent_plan_id = p_plan_id
184 OR child_plan_id = p_plan_id;
185
186 result BOOLEAN;
187 dummy NUMBER;
188
189 BEGIN
190
191 -- This subroutine determines if a plan should be mapped
192 -- for parent child VQR. This can be determined
193 -- by checking if plan is a parent or child
194 --
195 OPEN c;
196 FETCH c INTO dummy;
197 result := c%FOUND;
198 CLOSE c;
199
200 RETURN result;
201
202
203 END parent_child_plan;
204
205
206 FUNCTION construct_ak_code (p_appendix IN VARCHAR2, p_id IN VARCHAR2)
207 RETURN VARCHAR2 IS
208
209 BEGIN
210
211 -- The function is the standard way to compute attribute and
212 -- region codes.
213
214 RETURN (p_appendix ||p_id);
215
216 END construct_ak_code;
217
218
219 FUNCTION retrieve_id (p_code IN VARCHAR2)
220 RETURN NUMBER IS
221
222 pos NUMBER;
223 id VARCHAR2(100);
224
225 BEGIN
226
227 -- The function is the standard way to retrive id given the code
228
229 IF (INSTR(p_code, g_element_appendix) <> 0) THEN
230 pos := length(g_element_appendix)+1;
231
232 ELSIF (INSTR(p_code, g_osp_vqr_appendix) <> 0) THEN
233 pos := length(g_osp_vqr_appendix)+1;
234 ELSE
235 pos := length(g_txn_osp_appendix)+1;
236 END IF;
237
238 id := substr(p_code, pos, length(p_code));
239
240 RETURN to_number(id);
241
242 END retrieve_id;
243
244
245 FUNCTION get_label (p_plan_id IN NUMBER, p_element_id IN NUMBER)
246 RETURN VARCHAR2 IS
247
248 CURSOR c IS
249 SELECT prompt
250 FROM qa_plan_chars
251 WHERE plan_id = p_plan_id
252 AND char_id = p_element_id;
253
254 label qa_plan_chars.prompt%TYPE;
255
256 BEGIN
257
258 -- This functions retrieves the plan char prompt. This is used
259 -- to populate the label field in the AK tables.
260
261 OPEN c;
262 FETCH c INTO label;
263 CLOSE c;
264
265 RETURN label;
266
267 END get_label;
268
269
270 FUNCTION get_plan_char_sequence (p_plan_id IN NUMBER, p_element_id IN NUMBER)
271 RETURN NUMBER IS
272
273 CURSOR c IS
274 SELECT prompt_sequence
275 FROM qa_plan_chars
276 WHERE plan_id = p_plan_id
277 AND char_id = p_element_id;
278
279 sequence qa_plan_chars.prompt_sequence%TYPE;
280
281 BEGIN
282
283 -- This functions retrieves the plan char display sequence.
284
285 OPEN c;
286 FETCH c INTO sequence;
287 CLOSE c;
288
289 RETURN sequence;
290
291 END get_plan_char_sequence;
292
293
294 FUNCTION get_special_label (p_appendix IN VARCHAR2)
295 RETURN VARCHAR2 IS
296
297 label VARCHAR2(30);
298
299 BEGIN
300
301 -- For some hardocded columns such as "Created By", "Colleciton"
302 -- and "Last Update Date" we need to retrieve the right label
303 -- keeping translation in mind.
304
305 IF (p_appendix = g_qa_created_by_attribute) THEN
306 label := fnd_message.get_string('QA','QA_SS_CREATED_BY');
307 ELSIF (p_appendix = g_collection_id_attribute) THEN
308 label := fnd_message.get_string('QA','QA_SS_COLLECTION');
309 ELSIF (p_appendix = g_last_update_date_attribute) THEN
310 label := fnd_message.get_string('QA','QA_SS_ENTRY_DATE');
311 ELSE
312 label := '';
313 END IF;
314
315 RETURN label;
316
317 END get_special_label;
318
319
320 FUNCTION get_vo_attribute_name (p_char_id IN NUMBER, p_plan_id IN NUMBER)
321 RETURN VARCHAR2 IS
322
323 column_name VARCHAR2(100);
324
325 BEGIN
326
327 -- This function computes the vo attribute name for
328 -- soft coded elements. Also, it replaces CHARACTER
329 -- Character.
330
331 column_name := qa_core_pkg.get_result_column_name (p_char_id, p_plan_id);
332 column_name := replace(column_name, 'CHARACTER', 'Character');
333 column_name := replace(column_name, 'COMMENT', 'Comment');
334 column_name := replace(column_name, 'SEQUENCE', 'Sequence');
335
336 RETURN column_name;
337
338 END get_vo_attribute_name;
339
340
341 FUNCTION get_hardcoded_vo_attr_name (p_code IN VARCHAR2)
342 RETURN VARCHAR2 IS
343
344 column_name VARCHAR2(100);
345
346 BEGIN
347
348 -- This function retrieves the result column name for
349 -- hard coded elements.
350
351 IF (INSTR(p_code, g_org_id_attribute) <> 0) THEN
352 column_name := 'ORGANIZATION_ID';
353 ELSIF (INSTR(p_code, g_org_code_attribute) <> 0) THEN
354 column_name := 'ORGANIZATION_CODE';
355 ELSIF (INSTR(p_code, g_plan_id_attribute) <> 0) THEN
356 column_name := 'PLAN_ID';
357 ELSIF (INSTR(p_code, g_plan_name_attribute) <> 0) THEN
358 column_name := 'PLAN_NAME';
359 ELSIF (INSTR(p_code, g_process_status_attribute) <> 0) THEN
360 column_name := 'PROCESS_STATUS';
361 ELSIF (INSTR(p_code, g_source_code_attribute) <> 0) THEN
362 column_name := 'SOURCE_CODE';
363 ELSIF (INSTR(p_code, g_source_line_id_attribute) <> 0) THEN
364 column_name := 'SOURCE_LINE_ID';
365 ELSIF (INSTR(p_code, g_po_agent_id_attribute) <> 0) THEN
366 column_name := 'PO_AGENT_ID';
367 ELSIF (INSTR(p_code, g_qa_created_by_attribute) <> 0) THEN
368 column_name := 'QA_CREATED_BY_NAME';
369 ELSIF (INSTR(p_code, g_collection_id_attribute) <> 0) THEN
370 column_name := 'COLLECTION_ID';
371 ELSIF (INSTR(p_code, g_last_update_date_attribute) <> 0) THEN
372 column_name := 'LAST_UPDATE_DATE';
373 END IF;
374
375 RETURN column_name;
376
377 END get_hardcoded_vo_attr_name;
378
379
380 FUNCTION convert_data_type (p_data_type IN NUMBER)
381 RETURN VARCHAR2 IS
382
383 BEGIN
384
385 -- In Quality the data type is indicated by a number. whereas,
386 -- in ak it a is string that describes what the data type is.
387 -- This routine was written to convert the data_type according
388 -- to AK.
389
390 IF p_data_type = 1 THEN
391 return 'VARCHAR2';
392 ELSIF p_data_type = 2 THEN
393 return 'NUMBER';
394 ELSE
395 return 'DATE';
396 END IF;
397
398 END convert_data_type;
399
400
401 FUNCTION convert_flag (p_flag IN NUMBER)
402 RETURN VARCHAR2 IS
403
404 BEGIN
405
406 -- In Quality all the flags are numeric, meaning a value of 1 or 2
407 -- is used to indicate if the flag is on or off. In AK however,
408 -- it is a character that describes if the flag is on or off.
409 -- This routine was written to convert the Quality flags to AK.
410
411 IF p_flag = 1 THEN
412 return 'Y';
413 ELSIF p_flag = 2 THEN
414 return 'N';
415 END IF;
416
417 END convert_flag;
418
419
420 FUNCTION compute_item_style (p_appendix IN VARCHAR2, p_element_id IN NUMBER)
421 RETURN VARCHAR2 IS
422
423 l_txn_number NUMBER;
424
425 BEGIN
426
427 -- For Quality's self service application we need to know what
428 -- item style to render the UI. If the element is a context
429 -- element then the item style must be HIDDEN, but for the rest
430 -- of them it should be text input. This distiction is made here.
431
432
433 IF (p_appendix = g_txn_osp_appendix) THEN
434 l_txn_number := qa_ss_const.ss_outside_processing_txn;
435
436 ELSIF (p_appendix = g_txn_ship_appendix) THEN
437 l_txn_number := qa_ss_const.ss_shipments_txn;
438
439 ELSIF (p_appendix = g_txn_work_appendix) THEN
440 l_txn_number := 31;
441
442 ELSIF (p_appendix = g_txn_asset_appendix) THEN
443 l_txn_number := 32;
444
445 ELSIF (p_appendix = g_txn_op_appendix) THEN
446 l_txn_number := 33;
447
448 END IF;
449
450 IF context_element(p_element_id, l_txn_number) THEN
451 return 'HIDDEN';
452 ELSE
453 return 'TEXT_INPUT';
454 END IF;
455
456 END compute_item_style;
457
458
459 FUNCTION query_criteria (p_appendix IN VARCHAR2, p_element_id IN NUMBER)
460 RETURN BOOLEAN IS
461
462 CURSOR c (p_txn_number NUMBER) IS
463 SELECT 'TRUE'
464 FROM qa_txn_collection_triggers
465 WHERE transaction_number = p_txn_number
466 AND search_flag = 1
467 AND collection_trigger_id = p_element_id;
468
469 l_txn_number NUMBER;
470 dummy VARCHAR2(10);
471 result BOOLEAN;
472
473 BEGIN
474
475 -- This procecure determines if a colleciton element is a part of the
476 -- VQR where clause. If it is then it is a query criteria for vqr.
477 -- In case of OM nothing is a query query criteria, hence always
478 -- return FALSE
479
480 IF (instr(p_appendix, g_om_vqr_appendix) <> 0) THEN
481 RETURN FALSE;
482 END IF;
483
484 -- parent-child
485 -- In case of parentchild no hidden fields, hence always
486 -- return FALSE
487 IF ((instr(p_appendix, g_pc_vqr_appendix) <> 0) OR
488 (instr(p_appendix, g_pc_vqr_sin_appendix) <> 0 )) THEN
489 RETURN FALSE;
490 END IF;
491
492 IF ( instr(p_appendix, g_osp_vqr_appendix) <> 0 ) THEN
493 -- appendix passed has 'QAVQROSP' in it
494 l_txn_number := qa_ss_const.ss_outside_processing_txn;
495
496 ELSIF ( instr(p_appendix, g_ship_vqr_appendix) <> 0 ) THEN
497 -- appendix passed has 'QAVQRSHP' in it
498 l_txn_number := qa_ss_const.ss_shipments_txn;
499
500 ELSIF ( instr(p_appendix, g_work_vqr_appendix) <> 0 ) THEN
501 -- appendix passed has 'QAVQRWORK' in it
502 l_txn_number := 31;
503
504 ELSIF ( instr(p_appendix, g_asset_vqr_appendix) <> 0 ) THEN
505 -- appendix passed has 'QAVQRASSET' in it
506 l_txn_number := 32;
507
508 ELSIF ( instr(p_appendix, g_op_vqr_appendix) <> 0 ) THEN
509 -- appendix passed has 'QAVQROP' in it
510 l_txn_number := 33;
511 END IF;
512
513 OPEN c(l_txn_number);
514 FETCH c into dummy;
515 result := c%FOUND;
516 CLOSE c;
517 RETURN result;
518
519 END query_criteria;
520
521
522 FUNCTION get_display_sequence (p_region_code IN VARCHAR2,
523 p_region_application_id IN NUMBER)
524 RETURN NUMBER IS
525
526 max_display_sequence NUMBER DEFAULT 0;
527
528 CURSOR c IS
529 SELECT MAX(display_sequence)
530 FROM ak_region_items
531 WHERE region_code = p_region_code
532 AND region_application_id = p_region_application_id;
533
534 BEGIN
535
536 -- display_sequence is a not null and unique field in ak_region_items.
537 -- When adding region items dynamically there is a need to know the next
538 -- available display sequence. This function computes that.
539
540 OPEN c;
541 FETCH c INTO max_display_sequence;
542 CLOSE c;
543
544 IF (max_display_sequence IS NOT NULL) THEN
545 RETURN max_display_sequence + 10;
546 ELSE
547 RETURN 10;
548 END IF;
549
550 END get_display_sequence;
551
552
553 FUNCTION get_text_display_sequence (p_region_code IN VARCHAR2,
554 p_region_application_id IN NUMBER)
555 RETURN NUMBER IS
556
557 max_display_sequence NUMBER DEFAULT 0;
558
559 CURSOR c IS
560 SELECT MAX(display_sequence)
561 FROM ak_region_items
562 WHERE region_code = p_region_code
563 AND region_application_id = p_region_application_id
564 AND item_style <> 'HIDDEN';
565
566 BEGIN
567
568 -- display_sequence is a not null and unique field in ak_region_items.
569 -- When adding region items dynamically there is a need to know the next
570 -- available display sequence. However, in case of special region items
571 -- there is a little complexity. There is a ATG bug that we have to work
572 -- around, which mandates that all hidden elements are addded at the end.
573 -- Since the special elements for vqr can be 'TEXT', we must add these
574 -- before the hidden items.
575
576 OPEN c;
577 FETCH c INTO max_display_sequence;
578 CLOSE c;
579
580 IF (max_display_sequence IS NOT NULL) THEN
581 RETURN max_display_sequence + 10;
582 ELSE
583 RETURN 10;
584 END IF;
585
586 END get_text_display_sequence;
587
588
589 PROCEDURE get_dependencies (p_char_id IN NUMBER, x_parents OUT NOCOPY ParentArray) IS
590
591 BEGIN
592
593 -- This is needed for populating correct lov relatiovs.
594 -- Given a element id, this function computes the
595 -- ancestors for it and accordingly populates a
596 -- OUT table structure.
597
598 x_parents.delete();
599
600 IF p_char_id = qa_ss_const.item THEN
601 x_parents(1) := qa_ss_const.production_line;
602
603 ELSIF p_char_id = qa_ss_const.to_op_seq_num THEN
604 x_parents(1) := qa_ss_const.job_name;
605 x_parents(2) := qa_ss_const.production_line;
606
607 ELSIF p_char_id = qa_ss_const.from_op_seq_num THEN
608 x_parents(1) := qa_ss_const.job_name;
609 x_parents(2) := qa_ss_const.production_line;
610
611 ELSIF p_char_id = qa_ss_const.to_intraoperation_step THEN
612 x_parents(1) := qa_ss_const.to_op_seq_num;
613
614 ELSIF p_char_id = qa_ss_const.from_intraoperation_step THEN
615 x_parents(1) := qa_ss_const.from_op_seq_num;
616
617 ELSIF p_char_id = qa_ss_const.uom THEN
618
619 x_parents(1) := qa_ss_const.item;
620 x_parents(2) := qa_ss_const.production_line;
621
622 ELSIF p_char_id = qa_ss_const.revision THEN
623 x_parents(1) := qa_ss_const.item;
624 x_parents(2) := qa_ss_const.production_line;
625
626 ELSIF p_char_id = qa_ss_const.subinventory THEN
627 x_parents(1) := qa_ss_const.item;
628 x_parents(2) := qa_ss_const.production_line;
629
630 ELSIF p_char_id = qa_ss_const.locator THEN
631 x_parents(1) := qa_ss_const.subinventory;
632 x_parents(2) := qa_ss_const.item;
633 x_parents(3) := qa_ss_const.production_line;
634
635 ELSIF p_char_id = qa_ss_const.lot_number THEN
636 x_parents(1) := qa_ss_const.item;
637 x_parents(2) := qa_ss_const.production_line;
638
639 ELSIF p_char_id = qa_ss_const.serial_number THEN
640 x_parents(1) := qa_ss_const.lot_number;
641 x_parents(2) := qa_ss_const.item;
642 x_parents(3) := qa_ss_const.production_line;
643
644 ELSIF p_char_id = qa_ss_const.comp_uom THEN
645 x_parents(1) := qa_ss_const.comp_item;
646
647 ELSIF p_char_id = qa_ss_const.comp_revision THEN
648 x_parents(1) := qa_ss_const.comp_item;
649
650 ELSIF p_char_id = qa_ss_const.po_line_num THEN
651 x_parents(1) := qa_ss_const.po_number;
652
653 ELSIF p_char_id = qa_ss_const.po_shipment_num THEN
654 x_parents(1) := qa_ss_const.po_line_num;
655 x_parents(2) := qa_ss_const.po_number;
656
657 ELSIF p_char_id = qa_ss_const.po_release_num THEN
658 x_parents(1) := qa_ss_const.po_number;
659
660 ELSIF p_char_id = qa_ss_const.order_line THEN
661 x_parents(1) := qa_ss_const.sales_order;
662
663 ELSIF p_char_id = qa_ss_const.task_number THEN
664 x_parents(1) := qa_ss_const.project_number;
665
666 --dgupta: Start R12 EAM Integration. Bug 4345492
667 ELSIF p_char_id = qa_ss_const.asset_instance_number THEN
668 x_parents(1) := qa_ss_const.asset_group;
669 x_parents(2) := qa_ss_const.asset_number;
670
671 ELSIF p_char_id = qa_ss_const.asset_number THEN
672 x_parents(1) := qa_ss_const.asset_group;
673 x_parents(2) := qa_ss_const.asset_instance_number;
674 --dgupta: End R12 EAM Integration. Bug 4345492
675
676 END IF;
677
678 END get_dependencies;
679
680
681 FUNCTION action_target_element (p_plan_id IN NUMBER, p_char_id IN NUMBER)
682 RETURN BOOLEAN IS
683
684 CURSOR c IS
685 SELECT 1
686 FROM DUAL
687 WHERE p_char_id
688 IN
689 (SELECT assigned_char_id
690 FROM qa_plan_char_actions
691 WHERE plan_char_action_trigger_id
692 IN
693 (SELECT plan_char_action_trigger_id
694 FROM qa_plan_char_action_triggers
695 WHERE plan_id = p_plan_id));
696
697 dummy NUMBER;
698 result BOOLEAN;
699
700 BEGIN
701
702 -- if the element is a potenital target for assigned a value
703 -- action then return true.
704
705 OPEN c;
706 FETCH c INTO dummy;
707 result := c%FOUND;
708 CLOSE c;
709
710 RETURN result;
711
712 END action_target_element;
713
714
715 FUNCTION get_eqr_header_region_code (p_code VARCHAR2)
716 RETURN VARCHAR2 IS
717
718 l_header_region_code VARCHAR2(30);
719
720 BEGIN
721
722 -- A value of zero is false 1 is true for INSTR comparison
723
724 IF (instr(p_code, g_txn_work_appendix) <> 0)
725 OR (instr(p_code, g_txn_asset_appendix) <> 0)
726 OR (instr(p_code, g_txn_op_appendix) <> 0)
727 THEN
728 l_header_region_code := g_eam_eqr_hdr_region;
729 ELSE
730 l_header_region_code := g_eqr_top_region;
731 END IF;
732
733 RETURN l_header_region_code;
734
735 END get_eqr_header_region_code;
736
737
738 FUNCTION get_vqr_header_region_code (p_code VARCHAR2)
739 RETURN VARCHAR2 IS
740
741 l_header_region_code VARCHAR2(30);
742
743 BEGIN
744
745 -- A value of zero is false 1 is true for INSTR comparison
746
747 --parent-child
748 IF ((instr(p_code, g_pc_vqr_appendix) <> 0)
749 OR (instr(p_code, g_pc_vqr_sin_appendix) <> 0)) THEN
750 l_header_region_code := g_pc_vqr_hdr_region;
751 ELSIF (instr(p_code, g_work_vqr_appendix) <> 0)
752 OR (instr(p_code, g_op_vqr_appendix) <> 0) THEN
753 l_header_region_code := g_eam_vqr_work_hdr_region;
754 ELSIF (instr(p_code, g_asset_vqr_appendix) <> 0) THEN
755 l_header_region_code := g_eam_vqr_asset_hdr_region;
756 ELSE
757 l_header_region_code := g_vqr_top_region;
758 END IF;
759
760 RETURN l_header_region_code;
761
762 END get_vqr_header_region_code;
763
764
765 FUNCTION get_region_style (p_code VARCHAR2)
766 RETURN VARCHAR2 IS
767
768 l_region_style VARCHAR2(30);
769
770 BEGIN
771
772 -- A value of zero is false 1 is true for INSTR comparison
773
774 IF (instr(p_code, g_txn_work_appendix) <> 0)
775 OR (instr(p_code, g_txn_asset_appendix) <> 0)
776 OR (instr(p_code, g_txn_op_appendix) <> 0)
777 THEN
778 l_region_style := 'DEFAULT_SINGLE_COLUMN';
779 ELSIF (instr(p_code, g_pc_vqr_sin_appendix) <> 0) --parent-child
780 THEN
781 l_region_style := 'DEFAULT_DOUBLE_COLUMN';--ui improvement
782 ELSE
783 l_region_style := 'TABLE';
784 END IF;
785
786 RETURN l_region_style;
787
788 END get_region_style;
789
790
791 FUNCTION attribute_exists ( p_attribute_application_id IN NUMBER,
792 p_attribute_code IN VARCHAR2)
793 RETURN BOOLEAN IS
794
795 l_row_exists VARCHAR2(4);
796
797 CURSOR c IS
798 SELECT 'TRUE'
799 FROM ak_attributes
800 WHERE attribute_application_id = p_attribute_application_id
801 AND attribute_code = p_attribute_code;
802
803 BEGIN
804
805 -- This function determines if an attribute exists.
806
807 OPEN c;
808 FETCH c INTO l_row_exists;
809 CLOSE c;
810
811 IF (l_row_exists = 'TRUE') THEN
812 RETURN TRUE;
813 ELSE
814 RETURN FALSE;
815 END IF;
816
817 END attribute_exists;
818
819
820 PROCEDURE delete_attribute_for_plan (
821 p_attribute_code IN VARCHAR2,
822 p_attribute_application_id IN NUMBER) IS
823
824 BEGIN
825
826 -- Deletes an attribute (corresponds to a collection plan)
827 -- if the combination already exists.
828
829 IF attribute_exists(p_attribute_application_id, p_attribute_code) THEN
830 -- dbms_output.put_line('Deleting Element : ' || p_attribute_code);
831 ak_attributes_pkg.delete_row(p_attribute_application_id,
832 p_attribute_code);
833 END IF;
834
835 END delete_attribute_for_plan;
836
837
838 PROCEDURE delete_element_mapping (
839 p_char_id IN NUMBER,
840 p_attribute_application_id IN NUMBER) IS
841
842 l_attribute_code VARCHAR2(30);
843
844 BEGIN
845
846 -- Deletes an attribute (corresponds to a collection element)
847 -- If it exists.
848
849 l_attribute_code := construct_ak_code(g_element_appendix, p_char_id);
850
851 IF attribute_exists(p_attribute_application_id, l_attribute_code) THEN
852
853 -- dbms_output.put_line('Deleting Element : ' || l_attribute_code);
854 ak_attributes_pkg.delete_row(p_attribute_application_id,
855 l_attribute_code);
856 END IF;
857
858 END delete_element_mapping;
859
860
861 PROCEDURE delete_region (
862 p_region_application_id IN NUMBER,
863 p_region_code IN VARCHAR2) IS
864
865 l_row_exists VARCHAR2(6);
866 dummy VARCHAR2(6);
867
868 CURSOR c IS
869 SELECT 'TRUE'
870 FROM ak_regions
871 WHERE region_application_id = p_region_application_id
872 AND region_code = p_region_code;
873
874 BEGIN
875
876 -- Deletes a region (corresponds to a collection plan)
877 -- If it exists.
878
879 OPEN c;
880 FETCH c INTO l_row_exists;
881
882 CLOSE c;
883
884 IF (l_row_exists = 'TRUE') THEN
885 -- dbms_output.put_line('Deleting Region : ' || p_region_code);
886 ak_regions_pkg.delete_row(p_region_application_id,
887 p_region_code);
888 END IF;
889
890 END delete_region;
891
892
893 PROCEDURE delete_a_lov_relation (
894 p_region_application_id IN NUMBER,
895 p_region_code IN VARCHAR2,
896 p_attribute_application_id IN NUMBER,
897 p_attribute_code IN VARCHAR2,
898 p_lov_region_appl_id IN NUMBER,
899 p_lov_region_code IN VARCHAR2,
900 p_lov_attribute_appl_id IN NUMBER,
901 p_lov_attribute_code IN VARCHAR2,
902 p_base_attribute_appl_id IN NUMBER,
903 p_base_attribute_code IN VARCHAR2,
904 p_direction_flag IN VARCHAR2) IS
905
906 l_row_exists VARCHAR2(4);
907 err_num NUMBER;
908 err_msg VARCHAR2(100);
909
910 CURSOR c IS
911 SELECT 'TRUE'
912 FROM ak_region_lov_relations
913 WHERE region_application_id = p_region_application_id
914 AND region_code = p_region_code
915 AND attribute_application_id = p_attribute_application_id
916 AND attribute_code = p_attribute_code
917 AND lov_region_appl_id = p_lov_region_appl_id
918 AND lov_region_code = p_lov_region_code
919 AND lov_attribute_appl_id = p_lov_attribute_appl_id
920 AND lov_attribute_code = p_lov_attribute_code
921 AND base_attribute_appl_id = p_base_attribute_appl_id
922 AND base_attribute_code = p_base_attribute_code
923 AND direction_flag = p_direction_flag ;
924
925 BEGIN
926
927 -- Deletes an individual row from lov relations table.
928 -- Here the region item corresponds to a collection plan element.
929
930 OPEN c;
931 FETCH c INTO l_row_exists;
932 CLOSE c;
933
934 IF (l_row_exists = 'TRUE') THEN
935 -- dbms_output.put_line('Deleting LOV Rel : ' || p_region_code|| ' ' ||
936 -- p_base_attribute_code);
937
938 AK_LOV_RELATIONS_PKG.DELETE_ROW (
939 X_REGION_APPLICATION_ID => p_region_application_id,
940 X_REGION_CODE => p_region_code,
941 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
942 X_ATTRIBUTE_CODE => p_attribute_code,
943 X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
944 X_LOV_REGION_CODE => p_lov_region_code,
945 X_LOV_ATTRIBUTE_APPL_ID => p_lov_attribute_appl_id,
946 X_LOV_ATTRIBUTE_CODE => p_lov_attribute_code,
947 X_BASE_ATTRIBUTE_APPL_ID => p_base_attribute_appl_id,
948 X_BASE_ATTRIBUTE_CODE => p_base_attribute_code,
949 X_DIRECTION_FLAG => p_direction_flag);
950
951 END IF;
952
953
954 EXCEPTION
955
956 WHEN OTHERS THEN
957 err_num := SQLCODE;
958 err_msg := SUBSTR(SQLERRM, 1, 100);
959 -- dbms_output.put_line(err_msg);
960
961 END delete_a_lov_relation;
962
963
964 PROCEDURE delete_lov_relations (
965 p_char_id IN NUMBER,
966 p_region_application_id IN NUMBER,
967 p_region_code IN VARCHAR2,
968 p_attribute_application_id IN NUMBER,
969 p_attribute_code IN VARCHAR2,
970 p_lov_region_appl_id IN NUMBER,
971 p_lov_region_code IN VARCHAR2) IS
972
973 l_lov_attribute_code VARCHAR2(30);
974 l_base_attribute_code VARCHAR2(30);
975 l_parents ParentArray;
976
977 BEGIN
978
979 -- Deletes an lov relations for a region item.
980 -- Here the region item corresponds to a collection plan element.
981
982 delete_a_lov_relation(
983 p_region_application_id => p_region_application_id,
984 p_region_code => p_region_code,
985 p_attribute_application_id => p_attribute_application_id,
986 p_attribute_code => p_attribute_code,
987 p_lov_region_appl_id => p_lov_region_appl_id,
988 p_lov_region_code => p_lov_region_code,
989 p_lov_attribute_appl_id => g_application_id,
990 p_lov_attribute_code => g_lov_attribute_code,
991 p_base_attribute_appl_id => p_attribute_application_id,
992 p_base_attribute_code => p_attribute_code,
993 p_direction_flag => 'CRITERIA');
994
995 delete_a_lov_relation(
996 p_region_application_id => p_region_application_id,
997 p_region_code => p_region_code,
998 p_attribute_application_id => p_attribute_application_id,
999 p_attribute_code => p_attribute_code,
1000 p_lov_region_appl_id => p_lov_region_appl_id,
1001 p_lov_region_code => p_lov_region_code,
1002 p_lov_attribute_appl_id => g_application_id,
1003 p_lov_attribute_code => g_lov_attribute_code,
1004 p_base_attribute_appl_id => p_attribute_application_id,
1005 p_base_attribute_code => p_attribute_code,
1006 p_direction_flag => 'RESULT');
1007
1008 delete_a_lov_relation(
1009 p_region_application_id => p_region_application_id,
1010 p_region_code => p_region_code,
1011 p_attribute_application_id => p_attribute_application_id,
1012 p_attribute_code => p_attribute_code,
1013 p_lov_region_appl_id => p_lov_region_appl_id,
1014 p_lov_region_code => p_lov_region_code,
1015 p_lov_attribute_appl_id => g_application_id,
1016 p_lov_attribute_code => g_lov_attribute_org_id,
1017 p_base_attribute_appl_id => p_attribute_application_id,
1018 p_base_attribute_code => g_org_id_attribute,
1019 p_direction_flag => 'PASSIVE_CRITERIA');
1020
1021 delete_a_lov_relation(
1022 p_region_application_id => p_region_application_id,
1023 p_region_code => p_region_code,
1024 p_attribute_application_id => p_attribute_application_id,
1025 p_attribute_code => p_attribute_code,
1026 p_lov_region_appl_id => p_lov_region_appl_id,
1027 p_lov_region_code => p_lov_region_code,
1028 p_lov_attribute_appl_id => g_application_id,
1029 p_lov_attribute_code => g_lov_attribute_plan_id,
1030 p_base_attribute_appl_id => p_attribute_application_id,
1031 p_base_attribute_code => g_plan_id_attribute,
1032 p_direction_flag => 'PASSIVE_CRITERIA');
1033
1034 get_dependencies(p_char_id, l_parents);
1035
1036 FOR i IN 1..l_parents.COUNT LOOP
1037
1038 l_lov_attribute_code := g_lov_attribute_dependency || to_char(i);
1039 l_base_attribute_code := construct_ak_code(g_element_appendix,
1040 l_parents(i));
1041
1042 -- dbms_output.put_line('Deleting Lov Rel : ' ||
1043 -- l_lov_attribute_Code || ' ' || l_base_attribute_code);
1044
1045 delete_a_lov_relation(
1046 p_region_application_id => p_region_application_id,
1047 p_region_code => p_region_code,
1048 p_attribute_application_id => p_attribute_application_id,
1049 p_attribute_code => p_attribute_code,
1050 p_lov_region_appl_id => p_lov_region_appl_id,
1051 p_lov_region_code => p_lov_region_code,
1052 p_lov_attribute_appl_id => g_application_id,
1053 p_lov_attribute_code => l_lov_attribute_code,
1054 p_base_attribute_appl_id => p_attribute_application_id,
1055 p_base_attribute_code => l_base_attribute_code,
1056 p_direction_flag => 'PASSIVE_CRITERIA');
1057
1058 END LOOP;
1059
1060
1061 END delete_lov_relations;
1062
1063
1064 PROCEDURE delete_region_item (
1065 p_region_application_id IN NUMBER,
1066 p_region_code IN VARCHAR2,
1067 p_attribute_application_id IN NUMBER,
1068 p_attribute_code IN VARCHAR2) IS
1069
1070 l_row_exists VARCHAR2(4);
1071 l_char_id NUMBER;
1072
1073 CURSOR c IS
1074 SELECT 'TRUE'
1075 FROM ak_region_items
1076 WHERE region_application_id = p_region_application_id
1077 AND region_code = p_region_code
1078 AND attribute_application_id = p_attribute_application_id
1079 AND attribute_code = p_attribute_code;
1080
1081 BEGIN
1082
1083 -- Deletes a region item if the combination already exists.
1084 -- Here the region item corresponds to a collection plan element.
1085
1086 OPEN c;
1087 FETCH c INTO l_row_exists;
1088 CLOSE c;
1089
1090
1091 IF (l_row_exists = 'TRUE') THEN
1092
1093 -- dbms_output.put_line('Deleting Item : ' || p_region_code|| ' ' ||
1094 -- p_attribute_code);
1095
1096 ak_region_items_pkg.delete_row(
1097 p_region_application_id,
1098 p_region_code,
1099 p_attribute_application_id,
1100 p_attribute_code);
1101
1102 -- IF (INSTR(p_attribute_code, g_special_appendix) = 0) THEN
1103
1104 IF (INSTR(p_attribute_code, g_element_appendix) = 1) THEN
1105
1106 l_char_id := retrieve_id(p_attribute_code);
1107
1108 delete_lov_relations(
1109 P_CHAR_ID => l_char_id,
1110 P_REGION_APPLICATION_ID => g_application_id,
1111 P_REGION_CODE => p_region_code,
1112 P_ATTRIBUTE_APPLICATION_ID => g_application_id,
1113 P_ATTRIBUTE_CODE => p_attribute_code,
1114 P_LOV_REGION_APPL_ID => g_application_id,
1115 P_LOV_REGION_CODE => g_lov_region);
1116
1117 END IF;
1118
1119 END IF;
1120
1121 END delete_region_item;
1122
1123
1124 PROCEDURE get_element_values (p_char_id IN NUMBER,
1125 l_label_length OUT NOCOPY NUMBER,
1126 l_data_type OUT NOCOPY VARCHAR2,
1127 l_item_style OUT NOCOPY VARCHAR2,
1128 l_name OUT NOCOPY VARCHAR2,
1129 l_label OUT NOCOPY VARCHAR2,
1130 l_default_value OUT NOCOPY VARCHAR2,
1131 l_created_by OUT NOCOPY NUMBER,
1132 l_last_updated_by OUT NOCOPY NUMBER,
1133 l_last_update_login OUT NOCOPY NUMBER) IS
1134
1135 l_qa_chars_row qa_chars%ROWTYPE;
1136
1137 CURSOR c IS
1138 SELECT * from qa_chars
1139 WHERE char_id = p_char_id;
1140
1141 BEGIN
1142
1143 -- When mapping an element you need to know certain attributes
1144 -- of a collection element to be able to put in the correct
1145 -- information in ak tables. This function retrieves those
1146 -- relevant information.
1147
1148 OPEN c;
1149 FETCH c INTO l_qa_chars_row;
1150 CLOSE c;
1151
1152 l_data_type := convert_data_type(l_qa_chars_row.datatype);
1153 l_label_length := length(l_qa_chars_row.prompt);
1154 l_name := l_qa_chars_row.name;
1155 l_label := l_qa_chars_row.prompt;
1156 l_item_style := 'TEXT_INPUT';
1157 l_default_value := l_qa_chars_row.default_value;
1158 l_created_by := l_qa_chars_row.created_by;
1159 l_last_updated_by := l_qa_chars_row.last_updated_by;
1160 l_last_update_login := nvl(l_qa_chars_row.last_update_login,
1161 l_last_updated_by);
1162
1163 END get_element_values;
1164
1165
1166 PROCEDURE get_plan_values (p_plan_id IN NUMBER,
1167 l_label_length OUT NOCOPY NUMBER,
1168 l_data_type OUT NOCOPY VARCHAR2,
1169 l_item_style OUT NOCOPY VARCHAR2,
1170 l_name OUT NOCOPY VARCHAR2,
1171 l_label OUT NOCOPY VARCHAR2,
1172 l_created_by OUT NOCOPY NUMBER,
1173 l_last_updated_by OUT NOCOPY NUMBER,
1174 l_last_update_login OUT NOCOPY NUMBER) IS
1175
1176 l_qa_plans_row qa_plans%ROWTYPE;
1177
1178 CURSOR c IS
1179 SELECT * from qa_plans
1180 WHERE plan_id = p_plan_id;
1181
1182 BEGIN
1183
1184 -- For every collection plan we create not only a region but also
1185 -- ak attribute. When creating this ak_attribute there is a need
1186 -- to know certain attributes of this plan so that correct information
1187 -- canbe put in the ak tables. This function retrieves those
1188 -- relevant information.
1189
1190 OPEN c;
1191 FETCH c INTO l_qa_plans_row;
1192 CLOSE c;
1193
1194 l_data_type := 'VARCHAR2';
1195 l_label_length := length(l_qa_plans_row.name);
1196 l_name := l_qa_plans_row.name;
1197 l_label := l_qa_plans_row.name;
1198 l_item_style := 'NESTED_REGION';
1199 l_created_by := l_qa_plans_row.created_by;
1200 l_last_updated_by := l_qa_plans_row.last_updated_by;
1201 l_last_update_login := l_qa_plans_row.last_update_login;
1202
1203 END get_plan_values;
1204
1205
1206 PROCEDURE map_element (
1207 p_char_id IN NUMBER,
1208 p_attribute_application_id IN NUMBER,
1209 p_appendix IN VARCHAR2) IS
1210
1211 l_attribute_code VARCHAR2(30);
1212 l_row_id VARCHAR2(30);
1213 l_label_length NUMBER;
1214 l_data_type VARCHAR2(30);
1215 l_item_style VARCHAR2(30);
1216 l_name VARCHAR2(30);
1217 l_label VARCHAR2(30);
1218 l_varchar2_default VARCHAR2(150) DEFAULT null;
1219 l_default_value VARCHAR2(150);
1220 l_number_default NUMBER DEFAULT null;
1221 l_date_default DATE DEFAULT null;
1222 l_created_by NUMBER;
1223 l_last_updated_by NUMBER;
1224 l_last_update_login NUMBER;
1225
1226 BEGIN
1227
1228 -- This procedure does whatever is necessary to map a collection
1229 -- element to ak entity called attribute.
1230
1231 l_attribute_code := construct_ak_code(p_appendix, p_char_id);
1232
1233 delete_element_mapping (p_char_id, p_attribute_application_id);
1234
1235 get_element_values(p_char_id, l_label_length, l_data_type,
1236 l_item_style, l_name, l_label, l_default_value, l_created_by,
1237 l_last_updated_by, l_last_update_login);
1238
1239 IF (l_data_type = 'VARCHAR2') THEN
1240 l_varchar2_default := l_default_value;
1241 ELSIF (l_data_type = 'NUMBER') THEN
1242 l_number_default := to_number(l_default_value);
1243 ELSE
1244 l_date_default := qltdate.canon_to_date(l_default_value);
1245 END IF;
1246
1247 ak_attributes_pkg.insert_row (
1248 X_ROWID => l_row_id,
1249 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1250 X_ATTRIBUTE_CODE => l_attribute_code,
1251 X_ATTRIBUTE_LABEL_LENGTH => l_label_length,
1252 X_ATTRIBUTE_VALUE_LENGTH => 150,
1253 X_BOLD => 'N',
1254 X_ITALIC => 'N',
1255 X_UPPER_CASE_FLAG => 'N',
1256 X_VERTICAL_ALIGNMENT => 'TOP',
1257 X_HORIZONTAL_ALIGNMENT => 'LEFT' ,
1258 X_DEFAULT_VALUE_VARCHAR2 => l_varchar2_default,
1259 X_DEFAULT_VALUE_NUMBER => l_number_default,
1260 X_DEFAULT_VALUE_DATE => l_date_default,
1261 X_LOV_REGION_CODE => null,
1262 X_LOV_REGION_APPLICATION_ID => null,
1263 X_DATA_TYPE => l_data_type,
1264 X_DISPLAY_HEIGHT => 1,
1265 X_ITEM_STYLE => l_item_style,
1266 X_CSS_CLASS_NAME => null,
1267 X_CSS_LABEL_CLASS_NAME => null,
1268 X_POPLIST_VIEWOBJECT => null,
1269 X_POPLIST_DISPLAY_ATTRIBUTE => null,
1270 X_POPLIST_VALUE_ATTRIBUTE => null,
1271 X_ATTRIBUTE_CATEGORY => null,
1272 X_ATTRIBUTE1 => null,
1273 X_ATTRIBUTE2 => null,
1274 X_ATTRIBUTE3 => null,
1275 X_ATTRIBUTE4 => null,
1276 X_ATTRIBUTE5 => null,
1277 X_ATTRIBUTE6 => null,
1278 X_ATTRIBUTE7 => null,
1279 X_ATTRIBUTE8 => null,
1280 X_ATTRIBUTE9 => null,
1281 X_ATTRIBUTE10 => null,
1282 X_ATTRIBUTE11 => null,
1283 X_ATTRIBUTE12 => null,
1284 X_ATTRIBUTE13 => null,
1285 X_ATTRIBUTE14 => null,
1286 X_ATTRIBUTE15 => null,
1287 X_NAME => l_name,
1288 X_ATTRIBUTE_LABEL_LONG => l_label,
1289 X_DESCRIPTION => 'Quality Attribute',
1290 X_CREATION_DATE => g_sysdate,
1291 X_CREATED_BY => l_created_by,
1292 X_LAST_UPDATE_DATE => g_sysdate,
1293 X_LAST_UPDATED_BY => l_last_updated_by,
1294 X_LAST_UPDATE_LOGIN => l_last_update_login);
1295
1296
1297 -- dbms_output.put_line('Adding Element : ' || l_attribute_code);
1298
1299 END map_element;
1300
1301
1302 PROCEDURE add_attribute_for_plan (
1303 p_plan_id IN NUMBER,
1304 p_attribute_application_id IN NUMBER,
1305 p_appendix IN VARCHAR2) IS
1306
1307 l_attribute_code VARCHAR2(30);
1308 l_row_id VARCHAR2(30);
1309 l_label_length NUMBER;
1310 l_data_type VARCHAR2(30);
1311 l_item_style VARCHAR2(30);
1312 l_name VARCHAR2(30);
1313 l_label VARCHAR2(30);
1314 l_varchar2_default VARCHAR2(150) DEFAULT null;
1315 l_default_value VARCHAR2(150);
1316 l_number_default NUMBER DEFAULT null;
1317 l_date_default DATE DEFAULT null;
1318 l_created_by NUMBER;
1319 l_last_updated_by NUMBER;
1320 l_last_update_login NUMBER;
1321
1322 BEGIN
1323
1324 -- For every collection plan we create not only a region but also
1325 -- ak attribute to be able to refer to it from regions higher
1326 -- in hierarchy.
1327
1328 l_attribute_code := construct_ak_code(p_appendix, p_plan_id);
1329
1330 delete_attribute_for_plan (l_attribute_code, p_attribute_application_id);
1331
1332 get_plan_values(p_plan_id, l_label_length, l_data_type,
1333 l_item_style, l_name, l_label, l_created_by, l_last_updated_by,
1334 l_last_update_login);
1335
1336 IF (l_data_type = 'VARCHAR2') THEN
1337 l_varchar2_default := l_default_value;
1338 ELSIF (l_data_type = 'NUMBER') THEN
1339 l_number_default := to_number(l_default_value);
1340 ELSE
1341 l_date_default := qltdate.canon_to_date(l_default_value);
1342 END IF;
1343
1344 ak_attributes_pkg.insert_row (
1345 X_ROWID => l_row_id,
1346 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1347 X_ATTRIBUTE_CODE => l_attribute_code,
1348 X_ATTRIBUTE_LABEL_LENGTH => l_label_length,
1349 X_ATTRIBUTE_VALUE_LENGTH => 150,
1350 X_BOLD => 'N',
1351 X_ITALIC => 'N',
1352 X_UPPER_CASE_FLAG => 'N',
1353 X_VERTICAL_ALIGNMENT => 'TOP',
1354 X_HORIZONTAL_ALIGNMENT => 'LEFT' ,
1355 X_DEFAULT_VALUE_VARCHAR2 => l_varchar2_default,
1356 X_DEFAULT_VALUE_NUMBER => l_number_default,
1357 X_DEFAULT_VALUE_DATE => l_date_default,
1358 X_LOV_REGION_CODE => null,
1359 X_LOV_REGION_APPLICATION_ID => null,
1360 X_DATA_TYPE => l_data_type,
1361 X_DISPLAY_HEIGHT => 1,
1362 X_ITEM_STYLE => l_item_style,
1363 X_CSS_CLASS_NAME => null,
1364 X_CSS_LABEL_CLASS_NAME => null,
1365 X_POPLIST_VIEWOBJECT => null,
1366 X_POPLIST_DISPLAY_ATTRIBUTE => null,
1367 X_POPLIST_VALUE_ATTRIBUTE => null,
1368 X_ATTRIBUTE_CATEGORY => null,
1369 X_ATTRIBUTE1 => null,
1370 X_ATTRIBUTE2 => null,
1371 X_ATTRIBUTE3 => null,
1372 X_ATTRIBUTE4 => null,
1373 X_ATTRIBUTE5 => null,
1374 X_ATTRIBUTE6 => null,
1375 X_ATTRIBUTE7 => null,
1376 X_ATTRIBUTE8 => null,
1377 X_ATTRIBUTE9 => null,
1378 X_ATTRIBUTE10 => null,
1379 X_ATTRIBUTE11 => null,
1380 X_ATTRIBUTE12 => null,
1381 X_ATTRIBUTE13 => null,
1382 X_ATTRIBUTE14 => null,
1383 X_ATTRIBUTE15 => null,
1384 X_NAME => l_name,
1385 X_ATTRIBUTE_LABEL_LONG => l_label,
1386 X_DESCRIPTION => 'Quality Attribute',
1387 X_CREATION_DATE => g_sysdate,
1388 X_CREATED_BY => l_created_by,
1389 X_LAST_UPDATE_DATE => g_sysdate,
1390 X_LAST_UPDATED_BY => l_last_updated_by,
1391 X_LAST_UPDATE_LOGIN => l_last_update_login);
1392
1393 -- dbms_output.put_line('Adding Element : ' || l_attribute_code);
1394
1395 END add_attribute_for_plan;
1396
1397
1398 PROCEDURE add_ak_region (
1399 p_plan_id IN NUMBER,
1400 p_region_application_id IN NUMBER,
1401 p_appendix IN VARCHAR2) IS
1402
1403 l_region_code VARCHAR2(30);
1404 l_region_style VARCHAR2(30);
1405 l_row_id VARCHAR2(30);
1406 l_qa_plans_row qa_plans%ROWTYPE;
1407
1408 CURSOR c IS
1409 SELECT * from qa_plans
1410 WHERE plan_id = p_plan_id;
1411
1412 BEGIN
1413
1414 -- This procedure creates an entry in ak regions table for a collection
1415 -- plan in Quality.
1416
1417 OPEN c;
1418 FETCH c INTO l_qa_plans_row;
1419 CLOSE c;
1420
1421 l_region_code := construct_ak_code(p_appendix, p_plan_id);
1422 delete_region (p_region_application_id, l_region_code);
1423
1424 l_region_style := get_region_style(l_region_code);
1425
1426 AK_REGIONS_PKG.insert_row (
1427 X_ROWID => l_row_id,
1428 X_REGION_APPLICATION_ID => p_region_application_id,
1429 X_REGION_CODE => l_region_code,
1430 X_DATABASE_OBJECT_NAME => 'ICX_PROMPTS',
1431 X_REGION_STYLE => l_region_style,
1432 X_NUM_COLUMNS => null,
1433 X_ICX_CUSTOM_CALL => null ,
1434 X_NAME => l_qa_plans_row.name,
1435 X_DESCRIPTION => l_qa_plans_row.description,
1436 X_REGION_DEFAULTING_API_PKG => null,
1437 X_REGION_DEFAULTING_API_PROC => null,
1438 X_REGION_VALIDATION_API_PKG => null,
1439 X_REGION_VALIDATION_API_PROC => null,
1440 X_APPL_MODULE_OBJECT_TYPE => null,
1441 X_NUM_ROWS_DISPLAY => null,
1442 X_REGION_OBJECT_TYPE => null,
1443 X_IMAGE_FILE_NAME => null,
1444 X_ISFORM_FLAG => 'N',
1445 X_HELP_TARGET => null,
1446 X_STYLE_SHEET_FILENAME => null,
1447 X_VERSION => null,
1448 X_APPLICATIONMODULE_USAGE_NAME => null,
1449 X_ADD_INDEXED_CHILDREN => 'Y',
1450 X_STATEFUL_FLAG => null, --5.5
1451 X_FUNCTION_NAME => null, --5.5
1452 X_CHILDREN_VIEW_USAGE_NAME => null, --5.5
1453 X_SEARCH_PANEL => null, --5.5
1454 X_ADVANCED_SEARCH_PANEL => null, --5.5
1455 X_CUSTOMIZE_PANEL => null, --5.5
1456 X_DEFAULT_SEARCH_PANEL => null, --5.5
1457 X_RESULTS_BASED_SEARCH => null, --5.5
1458 X_DISPLAY_GRAPH_TABLE => null, --5.5
1459 X_CREATION_DATE => g_sysdate,
1460 X_CREATED_BY => l_qa_plans_row.created_by,
1461 X_LAST_UPDATE_DATE => g_sysdate,
1462 X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
1463 X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login,
1464 X_ATTRIBUTE_CATEGORY => null,
1465 X_ATTRIBUTE1 => null,
1466 X_ATTRIBUTE2 => null,
1467 X_ATTRIBUTE3 => null,
1468 X_ATTRIBUTE4 => null,
1469 X_ATTRIBUTE5 => null,
1470 X_ATTRIBUTE6 => null,
1471 X_ATTRIBUTE7 => null,
1472 X_ATTRIBUTE8 => null,
1473 X_ATTRIBUTE9 => null,
1474 X_ATTRIBUTE10 => null,
1475 X_ATTRIBUTE11 => null,
1476 X_ATTRIBUTE12 => null,
1477 X_ATTRIBUTE13 => null,
1478 X_ATTRIBUTE14 => null,
1479 X_ATTRIBUTE15 => null);
1480
1481 -- dbms_output.put_line('Adding Region : ' || l_region_code);
1482
1483 END add_ak_region;
1484
1485
1486 PROCEDURE add_lov_relations (
1487 p_plan_id IN NUMBER,
1488 p_region_code IN VARCHAR2,
1489 p_char_id IN NUMBER,
1490 p_attribute_code IN VARCHAR2,
1491 p_region_application_id IN NUMBER,
1492 p_attribute_application_id IN NUMBER,
1493 p_lov_region_appl_id IN NUMBER,
1494 p_lov_region_code IN VARCHAR2) IS
1495
1496
1497 err_num NUMBER;
1498 err_msg VARCHAR2(100);
1499
1500 l_row_id VARCHAR2(30);
1501 l_region_code VARCHAR2(30);
1502 l_attribute_code VARCHAR2(30);
1503 l_lov_attribute_code VARCHAR2(30);
1504 l_base_attribute_code VARCHAR2(30);
1505 l_parents ParentArray;
1506 l_qa_plans_row qa_plans%ROWTYPE;
1507
1508 CURSOR c IS
1509 SELECT * from qa_plans
1510 WHERE plan_id = p_plan_id;
1511
1512 BEGIN
1513
1514 -- This function adds lov relations for a region item.
1515 -- Here the region item corresponds to a collection plan element.
1516
1517 OPEN c;
1518 FETCH c INTO l_qa_plans_row;
1519 CLOSE c;
1520
1521 AK_LOV_RELATIONS_PKG.INSERT_ROW (
1522 X_ROWID => l_row_id,
1523 X_REGION_APPLICATION_ID => p_region_application_id,
1524 X_REGION_CODE => p_region_code,
1525 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1526 X_ATTRIBUTE_CODE => p_attribute_code,
1527 X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
1528 X_LOV_REGION_CODE => g_lov_region,
1529 X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
1530 X_LOV_ATTRIBUTE_CODE => g_lov_attribute_code,
1531 X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
1532 X_BASE_ATTRIBUTE_CODE => p_attribute_code,
1533 X_DIRECTION_FLAG => 'CRITERIA',
1534 X_REQUIRED_FLAG => 'N',
1535 X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
1536 X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
1537 X_CREATION_DATE => l_qa_plans_row.creation_date,
1538 X_CREATED_BY => l_qa_plans_row.created_by,
1539 X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
1540
1541
1542 AK_LOV_RELATIONS_PKG.INSERT_ROW (
1543 X_ROWID => l_row_id,
1544 X_REGION_APPLICATION_ID => p_region_application_id,
1545 X_REGION_CODE => p_region_code,
1546 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1547 X_ATTRIBUTE_CODE => p_attribute_code,
1548 X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
1549 X_LOV_REGION_CODE => g_lov_region,
1550 X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
1551 X_LOV_ATTRIBUTE_CODE => g_lov_attribute_code,
1552 X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
1553 X_BASE_ATTRIBUTE_CODE => p_attribute_code,
1554 X_DIRECTION_FLAG => 'RESULT',
1555 X_REQUIRED_FLAG => 'N',
1556 X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
1557 X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
1558 X_CREATION_DATE => l_qa_plans_row.creation_date,
1559 X_CREATED_BY => l_qa_plans_row.created_by,
1560 X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
1561
1562
1563 AK_LOV_RELATIONS_PKG.INSERT_ROW (
1564 X_ROWID => l_row_id,
1565 X_REGION_APPLICATION_ID => p_region_application_id,
1566 X_REGION_CODE => p_region_code,
1567 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1568 X_ATTRIBUTE_CODE => p_attribute_code,
1569 X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
1570 X_LOV_REGION_CODE => p_lov_region_code,
1571 X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
1572 X_LOV_ATTRIBUTE_CODE => g_lov_attribute_org_id,
1573 X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
1574 X_BASE_ATTRIBUTE_CODE => g_org_id_attribute,
1575 X_DIRECTION_FLAG => 'PASSIVE_CRITERIA',
1576 X_REQUIRED_FLAG => 'N',
1577 X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
1578 X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
1579 X_CREATION_DATE => l_qa_plans_row.creation_date,
1580 X_CREATED_BY => l_qa_plans_row.created_by,
1581 X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
1582
1583
1584 AK_LOV_RELATIONS_PKG.INSERT_ROW (
1585 X_ROWID => l_row_id,
1586 X_REGION_APPLICATION_ID => p_region_application_id,
1587 X_REGION_CODE => p_region_code,
1588 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1589 X_ATTRIBUTE_CODE => p_attribute_code,
1590 X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
1591 X_LOV_REGION_CODE => p_lov_region_code,
1592 X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
1593 X_LOV_ATTRIBUTE_CODE => g_lov_attribute_plan_id,
1594 X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
1595 X_BASE_ATTRIBUTE_CODE => g_plan_id_attribute,
1596 X_DIRECTION_FLAG => 'PASSIVE_CRITERIA',
1597 X_REQUIRED_FLAG => 'N',
1598 X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
1599 X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
1600 X_CREATION_DATE => l_qa_plans_row.creation_date,
1601 X_CREATED_BY => l_qa_plans_row.created_by,
1602 X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
1603
1604 get_dependencies(p_char_id, l_parents);
1605
1606 FOR i IN 1..l_parents.COUNT LOOP
1607
1608 l_lov_attribute_code := g_lov_attribute_dependency || to_char(i);
1609 l_base_attribute_code := construct_ak_code(g_element_appendix,
1610 l_parents(i));
1611
1612 AK_LOV_RELATIONS_PKG.INSERT_ROW (
1613 X_ROWID => l_row_id,
1614 X_REGION_APPLICATION_ID => p_region_application_id,
1615 X_REGION_CODE => p_region_code,
1616 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1617 X_ATTRIBUTE_CODE => p_attribute_code,
1618 X_LOV_REGION_APPL_ID => p_lov_region_appl_id,
1619 X_LOV_REGION_CODE => p_lov_region_code,
1620 X_LOV_ATTRIBUTE_APPL_ID => g_application_id,
1621 X_LOV_ATTRIBUTE_CODE => l_lov_attribute_code,
1622 X_BASE_ATTRIBUTE_APPL_ID => p_attribute_application_id,
1623 X_BASE_ATTRIBUTE_CODE => l_base_attribute_code,
1624 X_DIRECTION_FLAG => 'PASSIVE_CRITERIA',
1625 X_REQUIRED_FLAG => 'N',
1626 X_LAST_UPDATE_DATE => l_qa_plans_row.last_update_date,
1627 X_LAST_UPDATED_BY => l_qa_plans_row.last_updated_by,
1628 X_CREATION_DATE => l_qa_plans_row.creation_date,
1629 X_CREATED_BY => l_qa_plans_row.created_by,
1630 X_LAST_UPDATE_LOGIN => l_qa_plans_row.last_update_login);
1631
1632 END LOOP;
1633
1634 -- dbms_output.put_line('Adding LOV rel : ');
1635
1636 EXCEPTION
1637
1638 WHEN OTHERS THEN
1639 err_num := SQLCODE;
1640 err_msg := SUBSTR(SQLERRM, 1, 100);
1641 -- dbms_output.put_line(err_msg);
1642
1643 END add_lov_relations;
1644
1645
1646 PROCEDURE add_region_item_for_eqr (
1647 p_char_id IN NUMBER,
1648 p_attribute_application_id IN NUMBER,
1649 p_plan_id IN NUMBER,
1650 p_region_application_id IN NUMBER,
1651 p_appendix IN VARCHAR2) IS
1652
1653 l_row_id VARCHAR2(30);
1654 l_region_code VARCHAR2(30);
1655 l_attribute_code VARCHAR2(30);
1656 l_lov_region_code VARCHAR2(30) DEFAULT NULL;
1657 l_char_name VARCHAR2(30);
1658 l_vo_attribute_name VARCHAR2(30);
1659 l_display_flag VARCHAR2(1);
1660 l_required_flag VARCHAR2(1);
1661 l_item_style VARCHAR2(30);
1662 l_row_exists VARCHAR2(4);
1663 l_data_type VARCHAR2(30);
1664 l_varchar2_default VARCHAR2(150) DEFAULT null;
1665 l_number_default NUMBER DEFAULT null;
1666 l_date_default DATE DEFAULT null;
1667 l_qa_plan_chars_row qa_plan_chars%ROWTYPE;
1668 l_qa_chars_row qa_chars%ROWTYPE;
1669 l_display_sequence NUMBER;
1670
1671 err_num NUMBER;
1672 err_msg VARCHAR2(100);
1673
1674 CURSOR c1 IS
1675 SELECT *
1676 FROM qa_plan_chars
1677 WHERE plan_id = p_plan_id
1678 AND char_id = p_char_id;
1679
1680 CURSOR c2 IS
1681 SELECT *
1682 FROM qa_chars
1683 WHERE char_id = p_char_id;
1684
1685 BEGIN
1686
1687 -- This procedure adds a region item to the plan's eqr region.
1688
1689 OPEN c1;
1690 FETCH c1 INTO l_qa_plan_chars_row;
1691 CLOSE c1;
1692
1693 OPEN c2;
1694 FETCH c2 INTO l_qa_chars_row;
1695 CLOSE c2;
1696
1697 l_region_code := construct_ak_code(p_appendix, p_plan_id);
1698 l_attribute_code := construct_ak_code(g_element_appendix, p_char_id);
1699
1700 -- As a part of our upgrade strategy, we want to find out
1701 -- if the attribute exists in ak_attributes if it does not,
1702 -- we should create it.
1703
1704 IF NOT attribute_exists(p_attribute_application_id, l_attribute_code) THEN
1705
1706 -- dbms_output.put_line('Recognized region item as not existing');
1707 qa_ak_mapping_api.map_element(
1708 p_char_id => p_char_id,
1709 p_attribute_application_id => g_application_id,
1710 p_appendix => qa_ak_mapping_api.g_element_appendix);
1711
1712 END IF;
1713
1714 l_vo_attribute_name := get_vo_attribute_name(p_char_id, p_plan_id);
1715
1716 l_display_flag := convert_flag(l_qa_plan_chars_row.displayed_flag);
1717
1718 l_item_style := compute_item_style(p_appendix, p_char_id);
1719
1720 l_required_flag := convert_flag(l_qa_plan_chars_row.mandatory_flag);
1721
1722 IF l_item_style = 'TEXT_INPUT' AND
1723 (qa_plan_element_api.values_exist(p_plan_id, p_char_id)
1724 OR qa_plan_element_api.sql_validation_exists(p_char_id)
1725 OR qa_chars_api.has_hardcoded_lov(p_char_id)) THEN
1726
1727 -- dbms_output.put_line('Has Lov : ' || l_region_code || ' '
1728 -- || l_attribute_code);
1729
1730 l_lov_region_code := g_lov_region;
1731
1732 END IF;
1733
1734 l_data_type := convert_data_type(l_qa_chars_row.datatype);
1735
1736 IF (l_data_type = 'VARCHAR2') THEN
1737 l_varchar2_default := l_qa_plan_chars_row.default_value;
1738 ELSIF (l_data_type = 'NUMBER') THEN
1739 l_number_default := to_number(l_qa_plan_chars_row.default_value);
1740 ELSE
1741 l_date_default := qltdate.any_to_date(l_qa_plan_chars_row.default_value);
1742 END IF;
1743
1744 -- work around needed for a ATG bug
1745 -- hidden items should be at the very end of all region items in a region.
1746
1747 IF (l_item_style = 'HIDDEN') THEN
1748 l_display_sequence := l_qa_plan_chars_row.prompt_sequence +
1749 g_hidden_element_increment;
1750 ELSE
1751 l_display_sequence :=l_qa_plan_chars_row.prompt_sequence;
1752 END IF;
1753
1754 AK_REGION_ITEMS_PKG.insert_row (
1755 X_ROWID => l_row_id,
1756 X_REGION_APPLICATION_ID => p_region_application_id,
1757 X_REGION_CODE => l_region_code,
1758 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1759 X_ATTRIBUTE_CODE => l_attribute_code,
1760 X_DISPLAY_SEQUENCE => l_display_sequence,
1761 X_NODE_DISPLAY_FLAG => l_display_flag,
1762 X_NODE_QUERY_FLAG => 'N',
1763 X_ATTRIBUTE_LABEL_LENGTH => length(l_qa_chars_row.prompt),
1764 X_BOLD => 'N',
1765 X_ITALIC => 'N',
1766 X_VERTICAL_ALIGNMENT => 'TOP',
1767 X_HORIZONTAL_ALIGNMENT => 'LEFT',
1768 X_ITEM_STYLE => l_item_style,
1769 X_OBJECT_ATTRIBUTE_FLAG => 'N',
1770 X_ATTRIBUTE_LABEL_LONG => l_qa_chars_row.prompt,
1771 X_DESCRIPTION => l_qa_chars_row.name,
1772 X_SECURITY_CODE => null,
1773 X_UPDATE_FLAG => 'Y',
1774 X_REQUIRED_FLAG => l_required_flag,
1775 X_DISPLAY_VALUE_LENGTH => l_qa_chars_row.display_length,
1776 X_LOV_REGION_APPLICATION_ID => g_application_id,
1777 X_LOV_REGION_CODE => l_lov_region_code,
1778 X_LOV_FOREIGN_KEY_NAME => null,
1779 X_LOV_ATTRIBUTE_APPLICATION_ID => null,
1780 X_LOV_ATTRIBUTE_CODE => null,
1781 X_LOV_DEFAULT_FLAG => null,
1782 X_REGION_DEFAULTING_API_PKG => null,
1783 X_REGION_DEFAULTING_API_PROC => null,
1784 X_REGION_VALIDATION_API_PKG => null,
1785 X_REGION_VALIDATION_API_PROC => null,
1786 X_ORDER_SEQUENCE => null,
1787 X_ORDER_DIRECTION => null,
1788 X_DEFAULT_VALUE_VARCHAR2 => l_varchar2_default,
1789 X_DEFAULT_VALUE_NUMBER => l_number_default,
1790 X_DEFAULT_VALUE_DATE => l_date_default,
1791 X_ITEM_NAME => l_attribute_code,
1792 X_DISPLAY_HEIGHT => 1,
1793 X_SUBMIT => 'N',
1794 X_ENCRYPT => 'N',
1795 X_VIEW_USAGE_NAME => g_eqr_view_usage_name,
1796 X_VIEW_ATTRIBUTE_NAME => l_vo_attribute_name,
1797 X_CSS_CLASS_NAME => null,
1798 X_CSS_LABEL_CLASS_NAME => null,
1799 X_URL => null,
1800 X_POPLIST_VIEWOBJECT => null,
1801 X_POPLIST_DISPLAY_ATTRIBUTE => null,
1802 X_POPLIST_VALUE_ATTRIBUTE => null,
1803 X_IMAGE_FILE_NAME => null,
1804 X_NESTED_REGION_CODE => null,
1805 X_NESTED_REGION_APPL_ID => null,
1806 X_MENU_NAME => null,
1807 X_FLEXFIELD_NAME => null,
1808 X_FLEXFIELD_APPLICATION_ID => null,
1809 X_TABULAR_FUNCTION_CODE => null,
1810 X_TIP_TYPE => null,
1811 X_TIP_MESSAGE_NAME => null,
1812 X_TIP_MESSAGE_APPLICATION_ID => null,
1813 X_ENTITY_ID => null,
1814 X_FLEX_SEGMENT_LIST => null,
1815 X_ANCHOR => null,
1816 X_POPLIST_VIEW_USAGE_NAME => null,
1817 X_USER_CUSTOMIZABLE => null, --5.5
1818 X_SORTBY_VIEW_ATTRIBUTE_NAME => null, --5.5
1819 X_CREATION_DATE => g_sysdate,
1820 X_CREATED_BY => l_qa_plan_chars_row.created_by,
1821 X_LAST_UPDATE_DATE => g_sysdate,
1822 X_LAST_UPDATED_BY => l_qa_plan_chars_row.last_updated_by,
1823 X_LAST_UPDATE_LOGIN => l_qa_plan_chars_row.last_updated_by,
1824 X_ATTRIBUTE_CATEGORY => null,
1825 X_ATTRIBUTE1 => null,
1826 X_ATTRIBUTE2 => null,
1827 X_ATTRIBUTE3 => null,
1828 X_ATTRIBUTE4 => null,
1829 X_ATTRIBUTE5 => null,
1830 X_ATTRIBUTE6 => null,
1831 X_ATTRIBUTE7 => null,
1832 X_ATTRIBUTE8 => null,
1833 X_ATTRIBUTE9 => null,
1834 X_ATTRIBUTE10 => null,
1835 X_ATTRIBUTE11 => null,
1836 X_ATTRIBUTE12 => null,
1837 X_ATTRIBUTE13 => null,
1838 X_ATTRIBUTE14 => null,
1839 X_ATTRIBUTE15 => null);
1840
1841 -- At this point, if the element has lovs then we must determine
1842 -- what are its dependency and populate ak_region_lov_relations
1843 -- with this information.
1844
1845 IF (l_lov_region_code IS NOT NULL) THEN
1846
1847 add_lov_relations(
1848 P_PLAN_ID => p_plan_id,
1849 P_CHAR_ID => p_char_id,
1850 P_REGION_APPLICATION_ID => g_application_id,
1851 P_REGION_CODE => l_region_code,
1852 P_ATTRIBUTE_APPLICATION_ID => g_application_id,
1853 P_ATTRIBUTE_CODE => l_attribute_code,
1854 P_LOV_REGION_APPL_ID => g_application_id,
1855 P_LOV_REGION_CODE => g_lov_region);
1856
1857 END IF;
1858
1859 -- dbms_output.put_line('Adding Item : ' || l_region_code || ' ' ||
1860 -- l_attribute_code);
1861
1862 EXCEPTION
1863
1864 WHEN OTHERS THEN
1865 err_num := SQLCODE;
1866 err_msg := SUBSTR(SQLERRM, 1, 100);
1867 -- dbms_output.put_line(err_msg);
1868
1869 END add_region_item_for_eqr;
1870
1871
1872 PROCEDURE add_region_item_for_vqr (
1873 p_attribute_code IN VARCHAR2,
1874 p_attribute_application_id IN NUMBER,
1875 p_plan_id IN VARCHAR2,
1876 p_region_application_id IN NUMBER,
1877 p_appendix IN VARCHAR2) IS
1878
1879 l_row_id VARCHAR2(30);
1880 l_element_id NUMBER;
1881 l_region_code VARCHAR2(30);
1882 l_nested_region_code VARCHAR2(30) DEFAULT null;
1883 l_item_style VARCHAR2(30) DEFAULT 'TEXT';
1884 l_display_sequence NUMBER;
1885 l_display_flag VARCHAR2(1) DEFAULT 'Y';
1886 l_update_flag VARCHAR2(1) DEFAULT 'Y';
1887 l_query_flag VARCHAR2(1) DEFAULT 'N';
1888 l_view_attribute_name VARCHAR2(30) DEFAULT NULL;
1889 l_view_usage_name VARCHAR2(30) DEFAULT NULL;
1890 l_label_long VARCHAR2(30) DEFAULT NULL;
1891
1892 err_num NUMBER;
1893 err_msg VARCHAR2(100);
1894
1895 BEGIN
1896
1897 -- This procedure adds a region item to the plan's vqr region.
1898
1899 l_region_code := construct_ak_code(p_appendix, p_plan_id);
1900 l_view_usage_name := g_vqr_view_usage_name;
1901 -- parent-child for header single row region only
1902 IF (instr(p_appendix, g_pc_vqr_sin_appendix) <> 0)
1903 THEN
1904 l_view_usage_name := 'ParentResultVO';
1905 END IF;
1906
1907 l_nested_region_code := p_attribute_code;
1908 l_update_flag := 'N';
1909 l_element_id := retrieve_id(p_attribute_code);
1910
1911 -- As a part of our upgrade strategy, we want to find out
1912 -- if the attribute exists in ak_attributes if it does not,
1913 -- we should create it.
1914
1915 IF NOT attribute_exists(p_attribute_application_id, p_attribute_code) THEN
1916
1917 -- dbms_output.put_line('Recognized region item as not existing');
1918 qa_ak_mapping_api.map_element(
1919 p_char_id => l_element_id,
1920 p_attribute_application_id => g_application_id,
1921 p_appendix => qa_ak_mapping_api.g_element_appendix);
1922
1923 END IF;
1924
1925 l_view_attribute_name := qa_core_pkg.get_result_column_name (
1926 l_element_id, p_plan_id);
1927
1928 l_label_long := get_label(p_plan_id, l_element_id);
1929 l_display_sequence := get_plan_char_sequence(p_plan_id, l_element_id);
1930
1931 -- item_style is HIDDEN and if this element is a context element
1932 -- and is a query criteria (search flag is 1) for vqr
1933
1934 IF (query_criteria (l_region_code, l_element_id)) THEN
1935 l_item_style := 'HIDDEN';
1936 l_display_sequence := l_display_sequence +
1937 g_hidden_element_increment;
1938 END IF;
1939
1940 IF (instr(l_region_code, g_work_vqr_appendix) <> 0)
1941 OR (instr(l_region_code, g_asset_vqr_appendix) <> 0)
1942 OR (instr(l_region_code, g_op_vqr_appendix) <> 0) THEN
1943 l_query_flag := 'Y';
1944 END IF;
1945
1946
1947 AK_REGION_ITEMS_PKG.insert_row (
1948 X_ROWID => l_row_id,
1949 X_REGION_APPLICATION_ID => p_region_application_id,
1950 X_REGION_CODE => l_region_code,
1951 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
1952 X_ATTRIBUTE_CODE => p_attribute_code,
1953 X_DISPLAY_SEQUENCE => l_display_sequence,
1954 X_NODE_DISPLAY_FLAG => l_display_flag,
1955 X_NODE_QUERY_FLAG => l_query_flag,
1956 X_ATTRIBUTE_LABEL_LENGTH => 13,
1957 X_BOLD => 'N',
1958 X_ITALIC => 'N',
1959 X_VERTICAL_ALIGNMENT => 'TOP',
1960 X_HORIZONTAL_ALIGNMENT => 'LEFT',
1961 X_ITEM_STYLE => l_item_style,
1962 X_OBJECT_ATTRIBUTE_FLAG => 'N',
1963 X_ATTRIBUTE_LABEL_LONG => l_label_long,
1964 X_DESCRIPTION => 'Quality Column',
1965 X_SECURITY_CODE => null,
1966 X_UPDATE_FLAG => l_update_flag,
1967 X_REQUIRED_FLAG => 'N',
1968 X_DISPLAY_VALUE_LENGTH => 0,
1969 X_LOV_REGION_APPLICATION_ID => null,
1970 X_LOV_REGION_CODE => null,
1971 X_LOV_FOREIGN_KEY_NAME => null,
1972 X_LOV_ATTRIBUTE_APPLICATION_ID => null,
1973 X_LOV_ATTRIBUTE_CODE => null,
1974 X_LOV_DEFAULT_FLAG => null,
1975 X_REGION_DEFAULTING_API_PKG => null,
1976 X_REGION_DEFAULTING_API_PROC => null,
1977 X_REGION_VALIDATION_API_PKG => null,
1978 X_REGION_VALIDATION_API_PROC => null,
1979 X_ORDER_SEQUENCE => null,
1980 X_ORDER_DIRECTION => null,
1981 X_DEFAULT_VALUE_VARCHAR2 => null,
1982 X_DEFAULT_VALUE_NUMBER => null,
1983 X_DEFAULT_VALUE_DATE => null,
1984 X_ITEM_NAME => p_attribute_code,
1985 X_DISPLAY_HEIGHT => 1,
1986 X_SUBMIT => 'N',
1987 X_ENCRYPT => 'N',
1988 X_VIEW_USAGE_NAME => l_view_usage_name,
1989 X_VIEW_ATTRIBUTE_NAME => l_view_attribute_name,
1990 X_CSS_CLASS_NAME => null,
1991 X_CSS_LABEL_CLASS_NAME => null,
1992 X_URL => null,
1993 X_POPLIST_VIEWOBJECT => null,
1994 X_POPLIST_DISPLAY_ATTRIBUTE => null,
1995 X_POPLIST_VALUE_ATTRIBUTE => null,
1996 X_IMAGE_FILE_NAME => null,
1997 X_NESTED_REGION_CODE => l_nested_region_code,
1998 X_NESTED_REGION_APPL_ID => p_region_application_id,
1999 X_MENU_NAME => null,
2000 X_FLEXFIELD_NAME => null,
2001 X_FLEXFIELD_APPLICATION_ID => null,
2002 X_TABULAR_FUNCTION_CODE => null,
2003 X_TIP_TYPE => null,
2004 X_TIP_MESSAGE_NAME => null,
2005 X_TIP_MESSAGE_APPLICATION_ID => null,
2006 X_ENTITY_ID => null,
2007 X_FLEX_SEGMENT_LIST => null,
2008 X_ANCHOR => null,
2009 X_POPLIST_VIEW_USAGE_NAME => null,
2010 X_USER_CUSTOMIZABLE => null, --5.5
2011 X_SORTBY_VIEW_ATTRIBUTE_NAME => null, --5.5
2012 X_CREATION_DATE => g_sysdate,
2013 X_CREATED_BY => 1,
2014 X_LAST_UPDATE_DATE => g_sysdate,
2015 X_LAST_UPDATED_BY => 1,
2016 X_LAST_UPDATE_LOGIN => 1,
2017 X_ATTRIBUTE_CATEGORY => null,
2018 X_ATTRIBUTE1 => null,
2019 X_ATTRIBUTE2 => null,
2020 X_ATTRIBUTE3 => null,
2021 X_ATTRIBUTE4 => null,
2022 X_ATTRIBUTE5 => null,
2023 X_ATTRIBUTE6 => null,
2024 X_ATTRIBUTE7 => null,
2025 X_ATTRIBUTE8 => null,
2026 X_ATTRIBUTE9 => null,
2027 X_ATTRIBUTE10 => null,
2028 X_ATTRIBUTE11 => null,
2029 X_ATTRIBUTE12 => null,
2030 X_ATTRIBUTE13 => null,
2031 X_ATTRIBUTE14 => null,
2032 X_ATTRIBUTE15 => null);
2033
2034 -- dbms_output.put_line('Adding Item (S) : ' || l_region_code || ' ' ||
2035 -- p_attribute_code);
2036
2037 EXCEPTION
2038
2039 WHEN OTHERS THEN
2040 err_num := SQLCODE;
2041 err_msg := SUBSTR(SQLERRM, 1, 100);
2042 -- dbms_output.put_line(err_msg);
2043
2044 END add_region_item_for_vqr;
2045
2046
2047 PROCEDURE add_region_to_header (
2048 p_attribute_code IN VARCHAR2,
2049 p_attribute_application_id IN NUMBER,
2050 p_plan_id IN VARCHAR2,
2051 p_region_application_id IN NUMBER,
2052 p_appendix IN VARCHAR2) IS
2053
2054 l_row_id VARCHAR2(30);
2055 l_element_id NUMBER;
2056 l_region_code VARCHAR2(30);
2057 l_nested_region_code VARCHAR2(30) DEFAULT null;
2058 l_item_style VARCHAR2(30) DEFAULT 'NESTED_REGION';
2059 l_display_sequence NUMBER;
2060 l_display_flag VARCHAR2(1) DEFAULT 'Y';
2061 l_update_flag VARCHAR2(1) DEFAULT 'Y';
2062 l_view_attribute_name VARCHAR2(30) DEFAULT NULL;
2063 l_view_usage_name VARCHAR2(30) DEFAULT NULL;
2064 l_label_long VARCHAR2(50) DEFAULT NULL;
2065
2066 err_num NUMBER;
2067 err_msg VARCHAR2(100);
2068
2069 BEGIN
2070
2071 -- This function adds a region to a parent region.
2072 -- In our case, we want add the plan region to the top level region
2073 -- (e.g. QAPL<plan id> to EQR TOP or
2074 -- QAVQROSP<plan_id> or QAVQRSHP<plan id> to the VQR TOP
2075
2076 -- we have now added code to include mapping for eam.
2077
2078 -- l_region_code := construct_ak_code(p_appendix, p_plan_id);
2079 l_nested_region_code := p_attribute_code;
2080 l_view_usage_name := null;
2081
2082 IF ( instr(p_attribute_code, g_vqr_appendix) <> 1) THEN
2083
2084 -- l_region_code := g_eqr_top_region;
2085 l_region_code := get_eqr_header_region_code(p_attribute_code);
2086 l_display_sequence := get_display_sequence(l_region_code,
2087 p_region_application_id);
2088 l_label_long := 'Plan Name: ' || qa_plans_api.plan_name(p_plan_id);
2089
2090 ELSE
2091 -- we come here if we are adding plan region to the vqr top
2092
2093 -- this has to be enhanced to branch on attribute code as well
2094
2095 -- l_region_code := g_vqr_top_region;
2096 l_region_code := get_vqr_header_region_code(p_attribute_code);
2097 l_display_sequence := get_display_sequence(l_region_code,
2098 p_region_application_id);
2099
2100 END IF;
2101
2102 AK_REGION_ITEMS_PKG.insert_row (
2103 X_ROWID => l_row_id,
2104 X_REGION_APPLICATION_ID => p_region_application_id,
2105 X_REGION_CODE => l_region_code,
2106 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
2107 X_ATTRIBUTE_CODE => p_attribute_code,
2108 X_DISPLAY_SEQUENCE => l_display_sequence,
2109 X_NODE_DISPLAY_FLAG => l_display_flag,
2110 X_NODE_QUERY_FLAG => 'N',
2111 X_ATTRIBUTE_LABEL_LENGTH => 0,
2112 X_BOLD => 'N',
2113 X_ITALIC => 'N',
2114 X_VERTICAL_ALIGNMENT => 'TOP',
2115 X_HORIZONTAL_ALIGNMENT => 'LEFT',
2116 X_ITEM_STYLE => l_item_style,
2117 X_OBJECT_ATTRIBUTE_FLAG => 'N',
2118 X_ATTRIBUTE_LABEL_LONG => l_label_long,
2119 X_DESCRIPTION => 'Quality Column',
2120 X_SECURITY_CODE => null,
2121 X_UPDATE_FLAG => l_update_flag,
2122 X_REQUIRED_FLAG => 'N',
2123 X_DISPLAY_VALUE_LENGTH => 0,
2124 X_LOV_REGION_APPLICATION_ID => null,
2125 X_LOV_REGION_CODE => null,
2126 X_LOV_FOREIGN_KEY_NAME => null,
2127 X_LOV_ATTRIBUTE_APPLICATION_ID => null,
2128 X_LOV_ATTRIBUTE_CODE => null,
2129 X_LOV_DEFAULT_FLAG => null,
2130 X_REGION_DEFAULTING_API_PKG => null,
2131 X_REGION_DEFAULTING_API_PROC => null,
2132 X_REGION_VALIDATION_API_PKG => null,
2133 X_REGION_VALIDATION_API_PROC => null,
2134 X_ORDER_SEQUENCE => null,
2135 X_ORDER_DIRECTION => null,
2136 X_DEFAULT_VALUE_VARCHAR2 => null,
2137 X_DEFAULT_VALUE_NUMBER => null,
2138 X_DEFAULT_VALUE_DATE => null,
2139 X_ITEM_NAME => p_attribute_code,
2140 X_DISPLAY_HEIGHT => 1,
2141 X_SUBMIT => 'N',
2142 X_ENCRYPT => 'N',
2143 X_VIEW_USAGE_NAME => l_view_usage_name,
2144 X_VIEW_ATTRIBUTE_NAME => l_view_attribute_name,
2145 X_CSS_CLASS_NAME => null,
2146 X_CSS_LABEL_CLASS_NAME => null,
2147 X_URL => null,
2148 X_POPLIST_VIEWOBJECT => null,
2149 X_POPLIST_DISPLAY_ATTRIBUTE => null,
2150 X_POPLIST_VALUE_ATTRIBUTE => null,
2151 X_IMAGE_FILE_NAME => null,
2152 X_NESTED_REGION_CODE => l_nested_region_code,
2153 X_NESTED_REGION_APPL_ID => p_region_application_id,
2154 X_MENU_NAME => null,
2155 X_FLEXFIELD_NAME => null,
2156 X_FLEXFIELD_APPLICATION_ID => null,
2157 X_TABULAR_FUNCTION_CODE => null,
2158 X_TIP_TYPE => null,
2159 X_TIP_MESSAGE_NAME => null,
2160 X_TIP_MESSAGE_APPLICATION_ID => null,
2161 X_ENTITY_ID => null,
2162 X_FLEX_SEGMENT_LIST => null,
2163 X_ANCHOR => null,
2164 X_POPLIST_VIEW_USAGE_NAME => null,
2165 X_USER_CUSTOMIZABLE => null, --5.5
2166 X_SORTBY_VIEW_ATTRIBUTE_NAME => null, --5.5
2167 X_CREATION_DATE => g_sysdate,
2168 X_CREATED_BY => 1,
2169 X_LAST_UPDATE_DATE => g_sysdate,
2170 X_LAST_UPDATED_BY => 1,
2171 X_LAST_UPDATE_LOGIN => 1,
2172 X_ATTRIBUTE_CATEGORY => null,
2173 X_ATTRIBUTE1 => null,
2174 X_ATTRIBUTE2 => null,
2175 X_ATTRIBUTE3 => null,
2176 X_ATTRIBUTE4 => null,
2177 X_ATTRIBUTE5 => null,
2178 X_ATTRIBUTE6 => null,
2179 X_ATTRIBUTE7 => null,
2180 X_ATTRIBUTE8 => null,
2181 X_ATTRIBUTE9 => null,
2182 X_ATTRIBUTE10 => null,
2183 X_ATTRIBUTE11 => null,
2184 X_ATTRIBUTE12 => null,
2185 X_ATTRIBUTE13 => null,
2186 X_ATTRIBUTE14 => null,
2187 X_ATTRIBUTE15 => null);
2188
2189 -- dbms_output.put_line('Adding Item (H) : ' || l_region_code || ' ' ||
2190 -- p_attribute_code);
2191
2192 EXCEPTION
2193
2194 WHEN OTHERS THEN
2195 err_num := SQLCODE;
2196 err_msg := SUBSTR(SQLERRM, 1, 100);
2197 -- dbms_output.put_line(err_msg);
2198
2199 END add_region_to_header;
2200
2201
2202 PROCEDURE add_special_region_item (
2203 p_attribute_code IN VARCHAR2,
2204 p_attribute_application_id IN NUMBER,
2205 p_plan_id IN VARCHAR2,
2206 p_region_application_id IN NUMBER,
2207 p_appendix IN VARCHAR2) IS
2208
2209 l_row_id VARCHAR2(30);
2210 l_element_id NUMBER;
2211 l_region_code VARCHAR2(30);
2212 l_nested_region_code VARCHAR2(30) DEFAULT null;
2213 l_item_style VARCHAR2(30) DEFAULT 'HIDDEN';
2214 l_display_sequence NUMBER;
2215 l_display_flag VARCHAR2(1) DEFAULT 'Y';
2216 l_update_flag VARCHAR2(1) DEFAULT 'Y';
2217 l_view_attribute_name VARCHAR2(30) DEFAULT NULL;
2218 l_view_usage_name VARCHAR2(30) DEFAULT NULL;
2219 l_label_long VARCHAR2(30) DEFAULT NULL;
2220 l_entity_id VARCHAR2(30) DEFAULT NULL;
2221 l_url VARCHAR2(240) DEFAULT NULL;
2222 l_image_file_name VARCHAR2(240) DEFAULT NULL;
2223 l_description VARCHAR2(240) DEFAULT NULL;
2224 l_query_flag VARCHAR2(1) DEFAULT 'N';
2225
2226 err_num NUMBER;
2227 err_msg VARCHAR2(100);
2228
2229 BEGIN
2230
2231
2232 -- This function adds special region items to the region.
2233 -- 1. To add special elements for eqr (e.g. org_id, plan_id_ etc)
2234 -- 2. To add special elements for vqr (e.g. org_id, plan_id_ etc)
2235
2236 l_region_code := construct_ak_code(p_appendix, p_plan_id);
2237
2238 IF ( instr(p_appendix, g_vqr_appendix) = 1) THEN
2239
2240 -- Adding special elements for vqr
2241 l_view_usage_name := g_vqr_view_usage_name;
2242 l_item_style := 'TEXT';
2243 l_display_sequence := get_text_display_sequence(l_region_code,
2244 p_region_application_id);
2245 l_query_flag := 'Y';
2246
2247 ELSE
2248
2249 -- Adding special elements for eqr
2250 l_view_usage_name := g_eqr_view_usage_name;
2251 l_display_sequence := get_display_sequence(l_region_code,
2252 p_region_application_id);
2253
2254 END IF;
2255
2256 l_view_attribute_name := get_hardcoded_vo_attr_name(
2257 p_attribute_code);
2258
2259 l_label_long := get_special_label(p_attribute_code);
2260
2261 -- added for attachments
2262 IF (p_attribute_code = g_single_row_attachment) OR
2263 (p_attribute_code = g_multi_row_attachment) THEN
2264 l_entity_id := 'QA_RESULTS';
2265
2266 l_view_attribute_name := '';
2267 l_label_long := 'Attachment';
2268 l_description := 'View Attachment';
2269 l_query_flag := 'N';
2270
2271 IF (instr(l_region_code, g_txn_work_appendix) <> 0)
2272 OR (instr(l_region_code, g_txn_asset_appendix) <> 0)
2273 OR (instr(l_region_code, g_txn_op_appendix) <> 0)
2274 AND (p_attribute_code = g_single_row_attachment) THEN
2275 l_item_style := 'ATTACHMENT_LINK';
2276 ELSE
2277 l_item_style := 'ATTACHMENT_IMAGE';
2278 END IF;
2279
2280 IF ( instr(p_appendix, g_vqr_appendix) = 1) THEN
2281 l_update_flag := 'N';
2282 END IF;
2283 END IF;
2284
2285 IF (p_attribute_code = g_update_attribute) THEN
2286
2287 -- dbms_output.put_line('Display Sequence: ' || l_display_sequence);
2288
2289 -- need to get translated
2290 l_query_flag := 'N';
2291 l_label_long := 'Update';
2292 l_view_attribute_name := '';
2293 l_item_style := 'IMAGE';
2294 l_image_file_name := 'updateicon_enabled.gif';
2295 l_url := '/OA_HTML/OA.jsp?akRegionCode=QA_DDE_EQR_PAGE' || '&' ||
2296 'akRegionApplicationId=250' || '&' ||
2297 'PlanId={@PLAN_ID}' || '&' ||
2298 'Occurrence={@OCCURRENCE}' || '&' ||
2299 'UCollectionId={@COLLECTION_ID}' || '&' ||
2300 'retainAM=Y' || '&' || 'addBreadCrumb=Y';
2301
2302 END IF;
2303
2304 -- parent-child
2305 IF (p_attribute_code = g_child_url_attribute) THEN
2306
2307 -- dbms_output.put_line('Display Sequence: ' || l_display_sequence);
2308
2309 -- need to get translated
2310 l_query_flag := 'N';
2311 l_label_long := 'Child Plans';--earlier called Children
2312 l_view_attribute_name := '';
2313 l_item_style := 'IMAGE';
2314 l_image_file_name := 'allocationbr_pagetitle.gif';--image changed!
2315 l_url := '/OA_HTML/OA.jsp?akRegionCode=QA_PC_RES_SUMMARY_PAGE'
2316 || '&' ||
2317 'akRegionApplicationId=250' || '&' ||
2318 'ParentPlanId={@PLAN_ID}' || '&' ||
2319 'ParentOccurrence={@OCCURRENCE}' || '&' ||
2320 'ParentCollectionId={@COLLECTION_ID}' || '&' ||
2321 'retainAM=Y' || '&' || 'addBreadCrumb=Y' ;
2322 --breadcrumb added for bug 2331941
2323 END IF;
2324
2325 -- parent-child results inquiry ui improvement
2326 IF (p_attribute_code = g_vqr_all_elements_url) THEN
2327
2328 -- dbms_output.put_line('Display Sequence: ' || l_display_sequence);
2329
2330 -- need to get translated
2331 l_query_flag := 'N';
2332 l_label_long := 'More Details';--parent-child ui improvement
2333 l_view_attribute_name := '';
2334 l_item_style := 'IMAGE';
2335 l_image_file_name := 'detailsicon_enabled.gif';
2336 l_url := '/OA_HTML/OA.jsp?akRegionCode=QA_PC_RES_VQR_DETAIL_PAGE'
2337 || '&' ||
2338 'akRegionApplicationId=250' || '&' ||
2339 'ParentPlanId={@PLAN_ID}' || '&' ||
2340 'ParentOccurrence={@OCCURRENCE}' || '&' ||
2341 'ParentCollectionId={@COLLECTION_ID}' || '&' ||
2342 'PlanName={@NAME}' || '&' ||
2343 'VqrParam=DETAILS' || '&' ||
2344 'retainAM=Y' || '&' || 'addBreadCrumb=Y';
2345 --breadcrumb added for bug 2331941
2346 END IF;
2347
2348 -- dbms_output.put_line('Adding Item (S) : ' || l_region_code || ' ' ||
2349 -- p_attribute_code);
2350
2351 AK_REGION_ITEMS_PKG.insert_row (
2352 X_ROWID => l_row_id,
2353 X_REGION_APPLICATION_ID => p_region_application_id,
2354 X_REGION_CODE => l_region_code,
2355 X_ATTRIBUTE_APPLICATION_ID => p_attribute_application_id,
2356 X_ATTRIBUTE_CODE => p_attribute_code,
2357 X_DISPLAY_SEQUENCE => l_display_sequence,
2358 X_NODE_DISPLAY_FLAG => l_display_flag,
2359 X_NODE_QUERY_FLAG => l_query_flag,
2360 X_ATTRIBUTE_LABEL_LENGTH => 13,
2361 X_BOLD => 'N',
2362 X_ITALIC => 'N',
2363 X_VERTICAL_ALIGNMENT => 'TOP',
2364 X_HORIZONTAL_ALIGNMENT => 'LEFT',
2365 X_ITEM_STYLE => l_item_style,
2366 X_OBJECT_ATTRIBUTE_FLAG => 'N',
2367 X_ATTRIBUTE_LABEL_LONG => l_label_long,
2368 X_DESCRIPTION => l_description,
2369 X_SECURITY_CODE => NULL,
2370 X_UPDATE_FLAG => l_update_flag,
2371 X_REQUIRED_FLAG => 'N',
2372 X_DISPLAY_VALUE_LENGTH => 0,
2373 X_LOV_REGION_APPLICATION_ID => null,
2374 X_LOV_REGION_CODE => null,
2375 X_LOV_FOREIGN_KEY_NAME => null,
2376 X_LOV_ATTRIBUTE_APPLICATION_ID => null,
2377 X_LOV_ATTRIBUTE_CODE => null,
2378 X_LOV_DEFAULT_FLAG => null,
2379 X_REGION_DEFAULTING_API_PKG => null,
2380 X_REGION_DEFAULTING_API_PROC => null,
2381 X_REGION_VALIDATION_API_PKG => null,
2382 X_REGION_VALIDATION_API_PROC => null,
2383 X_ORDER_SEQUENCE => null,
2384 X_ORDER_DIRECTION => null,
2385 X_DEFAULT_VALUE_VARCHAR2 => null,
2386 X_DEFAULT_VALUE_NUMBER => null,
2387 X_DEFAULT_VALUE_DATE => null,
2388 X_ITEM_NAME => p_attribute_code,
2389 X_DISPLAY_HEIGHT => 1,
2390 X_SUBMIT => 'N',
2391 X_ENCRYPT => 'N',
2392 X_VIEW_USAGE_NAME => l_view_usage_name,
2393 X_VIEW_ATTRIBUTE_NAME => l_view_attribute_name,
2394 X_CSS_CLASS_NAME => null,
2395 X_CSS_LABEL_CLASS_NAME => null,
2396 X_URL => l_url,
2397 X_POPLIST_VIEWOBJECT => null,
2398 X_POPLIST_DISPLAY_ATTRIBUTE => null,
2399 X_POPLIST_VALUE_ATTRIBUTE => null,
2400 X_IMAGE_FILE_NAME => l_image_file_name,
2401 X_NESTED_REGION_CODE => l_nested_region_code,
2402 X_NESTED_REGION_APPL_ID => p_region_application_id,
2403 X_MENU_NAME => null,
2404 X_FLEXFIELD_NAME => null,
2405 X_FLEXFIELD_APPLICATION_ID => null,
2406 X_TABULAR_FUNCTION_CODE => null,
2407 X_TIP_TYPE => null,
2408 X_TIP_MESSAGE_NAME => null,
2409 X_TIP_MESSAGE_APPLICATION_ID => null,
2410 X_ENTITY_ID => l_entity_id,
2411 X_FLEX_SEGMENT_LIST => null,
2412 X_ANCHOR => null,
2413 X_POPLIST_VIEW_USAGE_NAME => null,
2414 X_USER_CUSTOMIZABLE => null, --5.5
2415 X_SORTBY_VIEW_ATTRIBUTE_NAME => null, --5.5
2416 X_CREATION_DATE => g_sysdate,
2417 X_CREATED_BY => 1,
2418 X_LAST_UPDATE_DATE => g_sysdate,
2419 X_LAST_UPDATED_BY => 1,
2420 X_LAST_UPDATE_LOGIN => 1,
2421 X_ATTRIBUTE_CATEGORY => null,
2422 X_ATTRIBUTE1 => null,
2423 X_ATTRIBUTE2 => null,
2424 X_ATTRIBUTE3 => null,
2425 X_ATTRIBUTE4 => null,
2426 X_ATTRIBUTE5 => null,
2427 X_ATTRIBUTE6 => null,
2428 X_ATTRIBUTE7 => null,
2429 X_ATTRIBUTE8 => null,
2430 X_ATTRIBUTE9 => null,
2431 X_ATTRIBUTE10 => null,
2432 X_ATTRIBUTE11 => null,
2433 X_ATTRIBUTE12 => null,
2434 X_ATTRIBUTE13 => null,
2435 X_ATTRIBUTE14 => null,
2436 X_ATTRIBUTE15 => null);
2437
2438 -- dbms_output.put_line('Added Item (S) : ' || l_region_code || ' ' ||
2439 -- p_attribute_code);
2440
2441 EXCEPTION
2442
2443 WHEN OTHERS THEN
2444 err_num := SQLCODE;
2445 err_msg := SUBSTR(SQLERRM, 1, 100);
2446 -- dbms_output.put_line(err_msg);
2447
2448 END add_special_region_item;
2449
2450
2451 PROCEDURE map_plan_for_eqr (
2452 x_plan_id IN VARCHAR2,
2453 x_region_application_id IN NUMBER,
2454 x_attribute_application_id IN NUMBER,
2455 x_appendix IN VARCHAR2) IS
2456
2457 l_element_id NUMBER;
2458 l_region_code VARCHAR2(30);
2459
2460 err_num NUMBER;
2461 err_msg VARCHAR2(100);
2462
2463 CURSOR c IS
2464 SELECT char_id
2465 FROM qa_plan_chars
2466 WHERE plan_id = x_plan_id
2467 AND enabled_flag = 1;
2468
2469 BEGIN
2470
2471 -- To map a plan for eqr (specific to a txn) we need to do the following:
2472 --
2473 -- 1. create a nested region ak attribute
2474 -- 2. create a region for the current plan
2475 -- 4. add the region as the region item of EQR Header region
2476 -- 5. add the plan elements as the region items of this region
2477 -- 6. add the special attributes (org id and plan id)
2478
2479 add_attribute_for_plan(
2480 p_plan_id => x_plan_id,
2481 p_attribute_application_id => x_attribute_application_id,
2482 p_appendix => x_appendix);
2483
2484 add_ak_region(
2485 p_plan_id => x_plan_id,
2486 p_region_application_id => x_region_application_id,
2487 p_appendix => x_appendix);
2488
2489 l_region_code := construct_ak_code(x_appendix, x_plan_id);
2490
2491 add_region_to_header (
2492 p_attribute_code => l_region_code,
2493 p_attribute_application_id => g_application_id,
2494 p_plan_id => x_plan_id,
2495 p_region_application_id => g_application_id,
2496 p_appendix => x_appendix);
2497
2498 OPEN c;
2499 LOOP
2500 FETCH c INTO l_element_id;
2501 EXIT WHEN c%NOTFOUND;
2502
2503 -- we have taken a decision to not to display an element
2504 -- for data entry if the element is a potenital target
2505 -- for a assigned a value action.
2506
2507 IF (NOT action_target_element(x_plan_id, l_element_id)) THEN
2508
2509 qa_ak_mapping_api.add_region_item_for_eqr (
2510 p_char_id => l_element_id,
2511 p_attribute_application_id => x_attribute_application_id,
2512 p_plan_id => x_plan_id,
2513 p_region_application_id => x_region_application_id,
2514 p_appendix => x_appendix);
2515
2516 END IF;
2517
2518 END LOOP;
2519 CLOSE c;
2520
2521 add_special_region_item (
2522 p_attribute_code => g_org_id_attribute,
2523 p_attribute_application_id => g_application_id,
2524 p_plan_id => x_plan_id,
2525 p_region_application_id => g_application_id,
2526 p_appendix => x_appendix);
2527
2528 add_special_region_item (
2529 p_attribute_code => g_org_code_attribute,
2530 p_attribute_application_id => g_application_id,
2531 p_plan_id => x_plan_id,
2532 p_region_application_id => g_application_id,
2533 p_appendix => x_appendix);
2534
2535 add_special_region_item (
2536 p_attribute_code => g_plan_id_attribute,
2537 p_attribute_application_id => g_application_id,
2538 p_plan_id => x_plan_id,
2539 p_region_application_id => g_application_id,
2540 p_appendix => x_appendix);
2541
2542 add_special_region_item (
2543 p_attribute_code => g_plan_name_attribute,
2544 p_attribute_application_id => g_application_id,
2545 p_plan_id => x_plan_id,
2546 p_region_application_id => g_application_id,
2547 p_appendix => x_appendix);
2548
2549 add_special_region_item (
2550 p_attribute_code => g_process_status_attribute,
2551 p_attribute_application_id => g_application_id,
2552 p_plan_id => x_plan_id,
2553 p_region_application_id => g_application_id,
2554 p_appendix => x_appendix);
2555
2556 add_special_region_item (
2557 p_attribute_code => g_source_code_attribute,
2558 p_attribute_application_id => g_application_id,
2559 p_plan_id => x_plan_id,
2560 p_region_application_id => g_application_id,
2561 p_appendix => x_appendix);
2562
2563 add_special_region_item (
2564 p_attribute_code => g_source_line_id_attribute,
2565 p_attribute_application_id => g_application_id,
2566 p_plan_id => x_plan_id,
2567 p_region_application_id => g_application_id,
2568 p_appendix => x_appendix);
2569
2570 add_special_region_item (
2571 p_attribute_code => g_po_agent_id_attribute,
2572 p_attribute_application_id => g_application_id,
2573 p_plan_id => x_plan_id,
2574 p_region_application_id => g_application_id,
2575 p_appendix => x_appendix);
2576
2577 -- added for attachments
2578
2579 IF (instr(l_region_code, g_txn_work_appendix) <> 0)
2580 OR (instr(l_region_code, g_txn_asset_appendix) <> 0)
2581 OR (instr(l_region_code, g_txn_op_appendix) <> 0) THEN
2582
2583 -- code branch for eam eqr, so make it single
2584 add_special_region_item (
2585 p_attribute_code => g_single_row_attachment,
2586 p_attribute_application_id => 601,
2587 p_plan_id => x_plan_id,
2588 p_region_application_id => g_application_id,
2589 p_appendix => x_appendix);
2590
2591 ELSE
2592 add_special_region_item (
2593 p_attribute_code => g_multi_row_attachment,
2594 p_attribute_application_id => 601,
2595 p_plan_id => x_plan_id,
2596 p_region_application_id => g_application_id,
2597 p_appendix => x_appendix);
2598
2599 END IF;
2600
2601 -- dbms_output.put_line('-------------------------------------------');
2602
2603 EXCEPTION
2604
2605 WHEN OTHERS THEN
2606 err_num := SQLCODE;
2607 err_msg := SUBSTR(SQLERRM, 1, 100);
2608 -- dbms_output.put_line(err_msg);
2609
2610 END map_plan_for_eqr;
2611
2612
2613 PROCEDURE map_plan_for_vqr (
2614 x_plan_id IN VARCHAR2,
2615 x_region_application_id IN NUMBER,
2616 x_attribute_application_id IN NUMBER,
2617 x_appendix IN VARCHAR2) IS
2618
2619 l_element_id NUMBER;
2620 l_region_code VARCHAR2(30);
2621 l_attribute_code VARCHAR2(30);
2622
2623 err_num NUMBER;
2624 err_msg VARCHAR2(100);
2625 elmt_counter NUMBER;--parent-child results inquiry
2626
2627 CURSOR c IS
2628 SELECT char_id
2629 FROM qa_plan_chars
2630 WHERE plan_id = x_plan_id
2631 AND enabled_flag = 1
2632 ORDER BY PROMPT_SEQUENCE;
2633
2634 BEGIN
2635
2636 -- To map a plan for vqr we need to do the follwing:
2637 --
2638 -- 1. create a nested region ak attribute
2639 -- 2. create a region for the current plan
2640 -- 4. add the region as the region item of VQR Header region
2641 -- 5. add the plan elements to current plan region
2642 -- 6. add special region items to the region
2643
2644 --dbms_output.put_line('in here');
2645 add_attribute_for_plan(
2646 p_plan_id => x_plan_id,
2647 p_attribute_application_id => x_attribute_application_id,
2648 p_appendix => x_appendix);
2649
2650 add_ak_region(
2651 p_plan_id => x_plan_id,
2652 p_region_application_id => x_region_application_id,
2653 p_appendix => x_appendix);
2654
2655 l_region_code := construct_ak_code(x_appendix, x_plan_id);
2656
2657 add_region_to_header (
2658 p_attribute_code => l_region_code,
2659 p_attribute_application_id => g_application_id,
2660 p_plan_id => x_plan_id,
2661 p_region_application_id => g_application_id,
2662 p_appendix => x_appendix);
2663
2664 elmt_counter := 0; --parent-child results inquiry
2665 --initialize counter
2666 OPEN c;
2667 LOOP
2668 FETCH c INTO l_element_id;
2669 EXIT WHEN c%NOTFOUND;
2670
2671 elmt_counter := elmt_counter + 1; --parent-child
2672 --dbms_output.put_line('counter' || elmt_counter);
2673 l_attribute_code := construct_ak_code(g_element_appendix,l_element_id);
2674
2675 -- For Eam transactions if it is an action target then this element
2676 -- should not show up in VQR region.
2677
2678 IF (instr(l_region_code, g_work_vqr_appendix) <> 0)
2679 OR (instr(l_region_code, g_asset_vqr_appendix) <> 0)
2680 OR (instr(l_region_code, g_op_vqr_appendix) <> 0) THEN
2681
2682 IF (NOT action_target_element(x_plan_id, l_element_id)) THEN
2683 qa_ak_mapping_api.add_region_item_for_vqr (
2684 p_attribute_code => l_attribute_code,
2685 p_attribute_application_id => x_attribute_application_id,
2686 p_plan_id => x_plan_id,
2687 p_region_application_id => x_region_application_id,
2688 p_appendix => x_appendix);
2689 END IF;
2690
2691 ELSE
2692 IF (instr(l_region_code, g_pc_vqr_appendix) <> 0 and
2693 elmt_counter > 4) THEN
2694 --if this is a parent child multi-row vqr screen
2695 EXIT; --exit the loop. Dont show more elements
2696 END IF; --parent-child
2697
2698 qa_ak_mapping_api.add_region_item_for_vqr (
2699 p_attribute_code => l_attribute_code,
2700 p_attribute_application_id => x_attribute_application_id,
2701 p_plan_id => x_plan_id,
2702 p_region_application_id => x_region_application_id,
2703 p_appendix => x_appendix);
2704 END IF;
2705
2706 END LOOP;
2707 CLOSE c;
2708
2709 IF (instr(l_region_code, g_pc_vqr_sin_appendix) = 0) THEN
2710 -- means if this is "not" single row parent vqr region
2711 add_special_region_item (
2712 p_attribute_code => g_qa_created_by_attribute,
2713 p_attribute_application_id => g_application_id,
2714 p_plan_id => x_plan_id,
2715 p_region_application_id => g_application_id,
2716 p_appendix => x_appendix);
2717
2718 add_special_region_item (
2719 p_attribute_code => g_collection_id_attribute,
2720 p_attribute_application_id => g_application_id,
2721 p_plan_id => x_plan_id,
2722 p_region_application_id => g_application_id,
2723 p_appendix => x_appendix);
2724
2725 add_special_region_item (
2726 p_attribute_code => g_last_update_date_attribute,
2727 p_attribute_application_id => g_application_id,
2728 p_plan_id => x_plan_id,
2729 p_region_application_id => g_application_id,
2730 p_appendix => x_appendix);
2731
2732
2733 -- added for attachments
2734 add_special_region_item (
2735 p_attribute_code => g_multi_row_attachment,
2736 p_attribute_application_id => 601,
2737 p_plan_id => x_plan_id,
2738 p_region_application_id => g_application_id,
2739 p_appendix => x_appendix);
2740
2741 -- added for update capability
2742
2743 IF (instr(l_region_code, g_work_vqr_appendix) <> 0)
2744 OR (instr(l_region_code, g_asset_vqr_appendix) <> 0)
2745 OR (instr(l_region_code, g_op_vqr_appendix) <> 0) THEN
2746
2747 add_special_region_item (
2748 p_attribute_code => g_update_attribute,
2749 p_attribute_application_id => g_application_id,
2750 p_plan_id => x_plan_id,
2751 p_region_application_id => g_application_id,
2752 p_appendix => x_appendix);
2753
2754 END IF;
2755
2756 -- parent-child
2757 IF (instr(l_region_code, g_pc_vqr_appendix) <> 0) THEN
2758 --if this is a parent child results inquiry multi-row vqr screen
2759 add_special_region_item (
2760 p_attribute_code => g_child_url_attribute,
2761 p_attribute_application_id => g_application_id,
2762 p_plan_id => x_plan_id,
2763 p_region_application_id => g_application_id,
2764 p_appendix => x_appendix);
2765
2766 --below introduced for ui improvement
2767 --link to click and see all coll.elements for a vqr row
2768 -- (More Details Link)
2769 --one 'nice' feedback here is that make this call only if the
2770 --total no of collection elements is greater than 4
2771 --since this link is not needed otherwise
2772 --this additional check can be coded here...
2773 --
2774 add_special_region_item (
2775 p_attribute_code => g_vqr_all_elements_url,
2776 p_attribute_application_id => g_application_id,
2777 p_plan_id => x_plan_id,
2778 p_region_application_id => g_application_id,
2779 p_appendix => x_appendix);
2780 END IF;
2781 END IF; --end "outer if" stmt: "not" single row parent vqr region
2782 EXCEPTION
2783
2784 WHEN OTHERS THEN
2785 err_num := SQLCODE;
2786 err_msg := SUBSTR(SQLERRM, 1, 100);
2787 -- dbms_output.put_line(err_msg);
2788
2789 END map_plan_for_vqr;
2790
2791
2792 PROCEDURE map_plan(
2793 p_plan_id IN NUMBER,
2794 p_region_application_id IN NUMBER,
2795 p_attribute_application_id IN NUMBER) IS
2796
2797 associated BOOLEAN DEFAULT FALSE;
2798 asset_mapped BOOLEAN DEFAULT FALSE;
2799
2800 BEGIN
2801
2802 -- This procedure does whatever is necessary to map a collection
2803 -- plan to ak tables.
2804 --
2805 -- At a very high level this is what we need to do:
2806 --
2807 -- 1. Delete the plan mapping if it existed (in case of update).
2808 -- 2. Check if this plan is associated with OSP transaction.
2809 -- If it is then map this plan for osp transaction.
2810 -- 3. Check if this plan is associated with SHIPMENT Transaction,
2811 -- If it is then map this plan for shipment transaction.
2812 -- 4. Check if this plan is associated with CUSTOMER PORTAL Transaction,
2813 -- If it is then map this plan for transaction but only the VQR part.
2814
2815 delete_plan_mapping(p_plan_id, p_region_application_id,
2816 p_attribute_application_id);
2817
2818
2819 -- To avoid hitting the database multiple times for sysdate.
2820 g_sysdate := SYSDATE;
2821 --dbms_output.put_line('entered');
2822 IF osp_self_service_plan(p_plan_id) THEN
2823
2824 -- dbms_output.put_line('mapping for osp');
2825 map_plan_for_eqr(p_plan_id, p_region_application_id,
2826 p_attribute_application_id, g_txn_osp_appendix);
2827 map_plan_for_vqr (p_plan_id, p_region_application_id,
2828 p_attribute_application_id, g_osp_vqr_appendix);
2829
2830 --for project3 parent-child
2831 --find out all descendants and call map plan for eqr and vqr
2832 --for all these descendants
2833 END IF;
2834
2835 IF shipment_self_service_plan(p_plan_id) THEN
2836
2837 -- dbms_output.put_line('mapping for shipment');
2838 map_plan_for_eqr(p_plan_id, p_region_application_id,
2839 p_attribute_application_id, g_txn_ship_appendix);
2840
2841 map_plan_for_vqr (p_plan_id, p_region_application_id,
2842 p_attribute_application_id, g_ship_vqr_appendix);
2843
2844 --for project3 parent-child
2845 --find out all descendants and call map plan for eqr and vqr
2846 --for all these descendants
2847 END IF;
2848
2849 IF customer_portal_plan(p_plan_id) THEN
2850
2851 -- dbms_output.put_line('mapping for OM');
2852 map_plan_for_vqr (p_plan_id, p_region_application_id,
2853 p_attribute_application_id, g_om_vqr_appendix);
2854 END IF;
2855
2856 IF eam_asset_plan(p_plan_id) THEN
2857
2858 if not asset_mapped then
2859 map_plan_for_eqr(p_plan_id, p_region_application_id,
2860 p_attribute_application_id, g_txn_asset_appendix);
2861 map_plan_for_vqr (p_plan_id, p_region_application_id,
2862 p_attribute_application_id, g_asset_vqr_appendix);
2863 asset_mapped := TRUE;
2864 end if;
2865
2866 END IF;
2867
2868 IF eam_work_order_plan(p_plan_id) THEN
2869
2870 -- dbms_output.put_line('mapping for EAM');
2871 map_plan_for_eqr(p_plan_id, p_region_application_id,
2872 p_attribute_application_id, g_txn_work_appendix);
2873 map_plan_for_vqr (p_plan_id, p_region_application_id,
2874 p_attribute_application_id, g_work_vqr_appendix);
2875
2876 if not asset_mapped then
2877 map_plan_for_eqr(p_plan_id, p_region_application_id,
2878 p_attribute_application_id, g_txn_asset_appendix);
2879 map_plan_for_vqr (p_plan_id, p_region_application_id,
2880 p_attribute_application_id, g_asset_vqr_appendix);
2881 asset_mapped := TRUE;
2882 end if;
2883
2884 END IF;
2885
2886 IF eam_op_comp_plan(p_plan_id) THEN
2887
2888 -- dbms_output.put_line('mapping for EAM');
2889 map_plan_for_eqr(p_plan_id, p_region_application_id,
2890 p_attribute_application_id, g_txn_op_appendix);
2891 map_plan_for_vqr (p_plan_id, p_region_application_id,
2892 p_attribute_application_id, g_op_vqr_appendix);
2893
2894 if not asset_mapped then
2895 map_plan_for_eqr(p_plan_id, p_region_application_id,
2896 p_attribute_application_id, g_txn_asset_appendix);
2897 map_plan_for_vqr (p_plan_id, p_region_application_id,
2898 p_attribute_application_id, g_asset_vqr_appendix);
2899 asset_mapped := TRUE;
2900 end if;
2901
2902 END IF;
2903
2904 -- Parent-Child
2905 IF parent_child_plan(p_plan_id) THEN
2906 map_plan_for_vqr (p_plan_id, p_region_application_id,
2907 p_attribute_application_id, g_pc_vqr_appendix);
2908 map_plan_for_vqr (p_plan_id, p_region_application_id,
2909 p_attribute_application_id, g_pc_vqr_sin_appendix);
2910
2911 --below info when we do project3 parent-child
2912 --this is called from qa_ss_parent_child_pkg
2913 --in that call check for isp and make appropriate map calls
2914 END IF;
2915
2916 END map_plan;
2917
2918
2919 PROCEDURE delete_plan_mapping_for_txn (
2920 p_plan_id IN VARCHAR2,
2921 p_region_application_id IN NUMBER,
2922 p_attribute_application_id IN NUMBER,
2923 p_appendix IN VARCHAR2) IS
2924
2925 l_element_id NUMBER;
2926 l_attribute_code VARCHAR2(30);
2927 l_region_code VARCHAR2(30);
2928 l_top_region VARCHAR2(30);
2929
2930 CURSOR c (x_region_code VARCHAR2) IS
2931 SELECT attribute_code
2932 FROM ak_region_items
2933 WHERE region_code = x_region_code
2934 AND region_application_id = g_application_id;
2935
2936 BEGIN
2937
2938 -- To delete a plan for txn we need to do the follwing:
2939 --
2940 -- 1. delete the region as the region item of EQR/VQR TOP
2941 -- 2. delete all the region items for this region
2942 -- 3. delete the region
2943 -- 4. delete the nested region ak attribute
2944
2945 l_region_code := construct_ak_code(p_appendix, p_plan_id);
2946
2947 IF ( instr(p_appendix, g_vqr_appendix) = 1) THEN
2948 -- possibly change this for eam vqr
2949 l_top_region := get_vqr_header_region_code(l_region_code);
2950 ELSE
2951 l_top_region := get_eqr_header_region_code(l_region_code);
2952 END IF;
2953
2954 delete_region_item (
2955 p_region_application_id,
2956 l_top_region,
2957 p_attribute_application_id,
2958 l_region_code);
2959
2960 -- dbms_output.put_line('Deleting From Region: ' || l_region_code);
2961 OPEN c (l_region_code);
2962 LOOP
2963 FETCH c INTO l_attribute_code;
2964 EXIT WHEN c%NOTFOUND;
2965
2966 IF (l_attribute_code = g_single_row_attachment)
2967 OR (l_attribute_code = g_multi_row_attachment) THEN
2968 delete_region_item (
2969 p_region_application_id,
2970 l_region_code,
2971 601,
2972 l_attribute_code);
2973 ELSE
2974 delete_region_item (
2975 p_region_application_id,
2976 l_region_code,
2977 p_attribute_application_id,
2978 l_attribute_code);
2979 END IF;
2980 END LOOP;
2981 CLOSE c;
2982
2983 delete_region (p_region_application_id, l_region_code);
2984 delete_attribute_for_plan (l_region_code, p_attribute_application_id);
2985
2986 -- dbms_output.put_line('-------------------------------------------');
2987
2988 END delete_plan_mapping_for_txn;
2989
2990
2991 PROCEDURE delete_plan_mapping (
2992 p_plan_id IN NUMBER,
2993 p_region_application_id IN NUMBER,
2994 p_attribute_application_id IN NUMBER) IS
2995
2996 BEGIN
2997
2998 -- This procedure deletes the mapping of a collection
2999 -- plan from ak tables. This does it by deleting all the
3000 -- individual mapping per transaction at a time.
3001
3002 -- Even for the same txn the call needs to be made multiple times
3003 -- for example for ship txn, call made once for EQR and once for VQR
3004 -- with appendix g_txn_ship_appendix and g_ship_vqr_appendix
3005
3006 -- parent-child
3007 -- note the use of g_pc_vqr_appendix means this is for PC vqr
3008 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3009 p_attribute_application_id, g_pc_vqr_appendix);
3010
3011 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3012 p_attribute_application_id, g_pc_vqr_sin_appendix);
3013
3014 -- end parent-child ... old code follows below
3015
3016 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3017 p_attribute_application_id, g_txn_osp_appendix);
3018
3019 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3020 p_attribute_application_id, g_txn_ship_appendix);
3021
3022 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3023 p_attribute_application_id, g_ship_vqr_appendix);
3024
3025 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3026 p_attribute_application_id, g_osp_vqr_appendix);
3027
3028 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3029 p_attribute_application_id, g_om_vqr_appendix);
3030
3031 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3032 p_attribute_application_id, g_txn_work_appendix);
3033
3034 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3035 p_attribute_application_id, g_work_vqr_appendix);
3036
3037 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3038 p_attribute_application_id, g_txn_asset_appendix);
3039
3040 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3041 p_attribute_application_id, g_asset_vqr_appendix);
3042
3043 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3044 p_attribute_application_id, g_txn_op_appendix);
3045
3046 delete_plan_mapping_for_txn(p_plan_id, p_region_application_id,
3047 p_attribute_application_id, g_op_vqr_appendix);
3048
3049 END delete_plan_mapping;
3050
3051
3052 FUNCTION context_element (element_id IN NUMBER, txn_number IN NUMBER)
3053 RETURN BOOLEAN IS
3054
3055 result BOOLEAN;
3056 dummy NUMBER;
3057
3058 CURSOR c IS
3059 SELECT 1
3060 FROM qa_txn_collection_triggers qtct
3061 WHERE qtct.transaction_number = txn_number
3062 AND qtct.collection_trigger_id = element_id;
3063
3064 BEGIN
3065
3066 -- This function determines if collection element is a context element
3067 -- given a transaction number.
3068
3069 OPEN c;
3070 FETCH c INTO dummy;
3071 result := c%FOUND;
3072 CLOSE c;
3073
3074 RETURN result;
3075
3076 END context_element;
3077
3078
3079 END qa_ak_mapping_api;
3080