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