1 PACKAGE BODY qa_validation_api AS
2 /* $Header: qltvalb.plb 120.40.12020000.3 2013/02/11 14:14:03 ntungare ship $ */
3
4 g_dependency_matrix DependencyArray;
5
6 --
7 -- Mapping between an internal validation error message code
8 -- and the AOL message name.
9 --
10 g_message_map dbms_sql.varchar2s;
11
12 g_restrict_subinv_code NUMBER;
13 g_restrict_locators_code NUMBER;
14 g_location_control_code NUMBER;
15 g_revision_qty_cntrl_code NUMBER;
16
17 g_comp_restrict_subinv_code NUMBER;
18 g_comp_restrict_locators_code NUMBER;
19 g_comp_location_control_code NUMBER;
20 g_comp_revision_qty_cntrl_code NUMBER;
21
22 -- Added the following for NCM Hardcode Elements.
23 -- suramasw Thu Oct 31 10:48:59 PST 2002.
24 -- Bug 2449067.
25
26 g_bill_restrict_subinv_code NUMBER;
27 g_bill_restrict_locators_code NUMBER;
28 g_bill_location_control_code NUMBER;
29 g_bill_revision_qty_cntrl_code NUMBER;
30
31 g_rout_restrict_subinv_code NUMBER;
32 g_rout_restrict_locators_code NUMBER;
33 g_rout_location_control_code NUMBER;
34 g_rout_revision_qty_cntrl_code NUMBER;
35
36 -- End of inclusions for NCM Hardcode Elements.
37
38
39 g_comp_item_id NUMBER;
40 g_item_id NUMBER;
41 g_org_id NUMBER;
42 g_transaction_number NUMBER;
43 g_transaction_id NUMBER;
44 g_lot_number VARCHAR2(150);
45 g_line_id NUMBER DEFAULT null;
46 g_wip_entity_id NUMBER;
47 g_po_header_id NUMBER;
48 --
49 -- bug 9652549 CLM changes
50 --
51 g_po_line_number VARCHAR2(240);
52 g_subinventory VARCHAR2(15);
53 -- Bug 9773026
54 -- Global variable to store RMA Header Id.
55 -- skolluku
56 g_rma_header_id NUMBER;
57
58 --
59 -- bug 11659488
60 -- Global variable to store sales_order_id
61 g_sales_order_id NUMBER;
62 --
63 -- See Bug 2588213
64 -- To support the element Maintenance Op Seq Number
65 -- to be used along with Maintenance Workorder
66 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
67 -- Added the following global variable
68 --
69 g_work_order_id NUMBER;
70
71 -- Added the following for NCM Hardcode Elements.
72 -- suramasw Thu Oct 31 10:48:59 PST 2002.
73 -- Bug 2449067.
74
75 g_bill_reference_id NUMBER;
76 g_routing_reference_id NUMBER;
77 g_to_subinventory VARCHAR2(15);
78
79 --
80 -- Bug 6126260
81 -- Needed for validating the dependent
82 -- elements of componenet subinventory
83 -- like comp_locator.
84 -- bhsankar Mon Jul 16 05:51:51 PDT 2007
85 --
86 g_comp_subinventory VARCHAR2(15);
87
88 --
89 -- Bug 2672396. Added g_project_id because task is a dependent element.
90 -- We will need to keep a cache copy of project_id when it is validated
91 -- to be used to validate task_number later.
92 -- bso Mon Nov 25 17:29:56 PST 2002
93 --
94 g_project_id NUMBER;
95
96 -- End of inclusions for NCM Hardcode Elements.
97
98 -- R12 OPM Deviations. Bug 4345503 Start
99 g_process_batch_id NUMBER;
100 g_process_batchstep_id NUMBER;
101 g_process_activity_id NUMBER;
102 g_process_resource_id NUMBER;
103 -- R12 OPM Deviations. Bug 4345503 End
104
105 --
106 -- Bug 4635316.
107 -- Global variable to store the application user_id.
108 -- ntungare Wed Sep 28 05:59:14 PDT 2005.
109 --
110 g_user_id number;
111
112
113
114 -- Bug 4558205. Lot/Serial Validation.
115 g_revision VARCHAR2(10);
116 -- End 4558205.
117
118
119 -- Bug 4558205. Lot/Serial Validation.
120 -- Init procedure for global variables.
121 -- srhariha. Tue Sep 27 05:07:58 PDT 2005.
122
123 PROCEDURE init_globals IS
124
125 BEGIN
126 g_lot_number := NULL;
127 g_revision := NULL;
128 END init_globals;
129
130 -- End 4558205.
131
132 PROCEDURE populate_dependency_matrix IS
133
134 BEGIN
135
136 g_dependency_matrix(1).element_id := qa_ss_const.item;
137 g_dependency_matrix(1).parent := qa_ss_const.production_line;
138
139 g_dependency_matrix(2).element_id := qa_ss_const.to_op_seq_num;
140 g_dependency_matrix(2).parent := qa_ss_const.job_name;
141
142 g_dependency_matrix(3).element_id := qa_ss_const.to_op_seq_num;
143 g_dependency_matrix(3).parent := qa_ss_const.production_line;
144
145 g_dependency_matrix(4).element_id := qa_ss_const.from_op_seq_num;
146 g_dependency_matrix(4).parent := qa_ss_const.job_name;
147
148 g_dependency_matrix(5).element_id := qa_ss_const.from_op_seq_num;
149 g_dependency_matrix(5).parent := qa_ss_const.production_line;
150
151 g_dependency_matrix(6).element_id := qa_ss_const.to_intraoperation_step;
152 g_dependency_matrix(6).parent := qa_ss_const.to_op_seq_num;
153
154 g_dependency_matrix(7).element_id := qa_ss_const.from_intraoperation_step;
155 g_dependency_matrix(7).parent := qa_ss_const.from_op_seq_num;
156
157 g_dependency_matrix(8).element_id := qa_ss_const.uom;
158 g_dependency_matrix(8).parent := qa_ss_const.item;
159
160 g_dependency_matrix(9).element_id := qa_ss_const.revision;
161 g_dependency_matrix(9).parent := qa_ss_const.item;
162
163 g_dependency_matrix(10).element_id := qa_ss_const.subinventory;
164 g_dependency_matrix(10).parent := qa_ss_const.item;
165
166 g_dependency_matrix(11).element_id := qa_ss_const.locator;
167 g_dependency_matrix(11).parent := qa_ss_const.subinventory;
168
169 g_dependency_matrix(12).element_id := qa_ss_const.lot_number;
170 g_dependency_matrix(12).parent := qa_ss_const.item;
171
172 g_dependency_matrix(13).element_id := qa_ss_const.serial_number;
173 g_dependency_matrix(13).parent := qa_ss_const.item;
174
175 g_dependency_matrix(14).element_id := qa_ss_const.comp_uom;
176 g_dependency_matrix(14).parent := qa_ss_const.comp_item;
177
178 g_dependency_matrix(15).element_id := qa_ss_const.comp_revision;
179 g_dependency_matrix(15).parent := qa_ss_const.comp_item;
180
181 g_dependency_matrix(16).element_id := qa_ss_const.po_line_num;
182 g_dependency_matrix(16).parent := qa_ss_const.po_number;
183
184 g_dependency_matrix(17).element_id := qa_ss_const.po_shipment_num;
185 g_dependency_matrix(17).parent := qa_ss_const.po_line_num;
186
187 g_dependency_matrix(18).element_id := qa_ss_const.po_release_num;
188 g_dependency_matrix(18).parent := qa_ss_const.po_number;
189
190 g_dependency_matrix(19).element_id := qa_ss_const.order_line;
191 g_dependency_matrix(19).parent := qa_ss_const.sales_order;
192
193 g_dependency_matrix(20).element_id := qa_ss_const.task_number;
194 g_dependency_matrix(20).parent := qa_ss_const.project_number;
195
196 g_dependency_matrix(21).element_id := qa_ss_const.serial_number;
197 g_dependency_matrix(21).parent := qa_ss_const.lot_number;
198
199 g_dependency_matrix(22).element_id := qa_ss_const.contract_line_number;
200 g_dependency_matrix(22).parent := qa_ss_const.contract_number;
201
202 g_dependency_matrix(23).element_id := qa_ss_const.deliverable_number;
203 g_dependency_matrix(23).parent := qa_ss_const.contract_line_number;
204
205 g_dependency_matrix(24).element_id := qa_ss_const.asset_number;
206 g_dependency_matrix(24).parent := qa_ss_const.asset_group;
207
208 --
209 -- See Bug 2588213
210 -- To support the element Maintenance Op Seq Number
211 -- to be used along with Maintenance Workorder
212 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
213 --
214 g_dependency_matrix(25).element_id := qa_ss_const.maintenance_op_seq;
215 g_dependency_matrix(25).parent := qa_ss_const.work_order;
216
217 -- Start of inclusions for NCM Hardcode Elements.
218 -- suramasw Thu Oct 31 10:48:59 PST 2002.
219 -- Bug 2449067.
220
221 g_dependency_matrix(26).element_id := qa_ss_const.to_subinventory;
222 g_dependency_matrix(26).parent := qa_ss_const.item;
223
224 g_dependency_matrix(27).element_id := qa_ss_const.to_locator;
225 g_dependency_matrix(27).parent := qa_ss_const.to_subinventory;
226
227 -- R12 OPM Deviations. Bug 4345503 Start
228 g_dependency_matrix(28).element_id := qa_ss_const.process_batchstep_num;
229 g_dependency_matrix(28).parent := qa_ss_const.process_batch_num;
230
231 g_dependency_matrix(29).element_id := qa_ss_const.process_operation;
232 g_dependency_matrix(29).parent := qa_ss_const.process_batch_num;
233
234 g_dependency_matrix(30).element_id := qa_ss_const.process_activity;
235 g_dependency_matrix(30).parent := qa_ss_const.process_batch_num;
236
237 g_dependency_matrix(31).element_id := qa_ss_const.process_resource;
238 g_dependency_matrix(31).parent := qa_ss_const.process_batch_num;
239
240 g_dependency_matrix(32).element_id := qa_ss_const.process_parameter;
241 g_dependency_matrix(32).parent := qa_ss_const.process_batch_num;
242 -- R12 OPM Deviations. Bug 4345503 End
243
244 --dgupta: Start R12 EAM Integration. Bug 4345492
245 --Ensure that sequence number (within brackets) is unique when merged
246 g_dependency_matrix(33).element_id := qa_ss_const.asset_instance_number;
247 g_dependency_matrix(33).parent := qa_ss_const.asset_group;
248 --dgupta: End R12 EAM Integration. Bug 4345492
249
250 END populate_dependency_matrix;
251
252
253 FUNCTION flag_is_set (all_flags IN VARCHAR2, flag_in_question IN VARCHAR2)
254 RETURN BOOLEAN IS
255
256 pos NUMBER;
257
258 BEGIN
259
260 IF instr(all_flags, flag_in_question, 1, 1) > 0 THEN
261 RETURN TRUE;
262 ELSE
263 RETURN FALSE;
264 END IF;
265
266 END flag_is_set;
267
268
269 FUNCTION parent (element_id IN NUMBER)
270 RETURN BOOLEAN IS
271
272 BEGIN
273
274 -- Earlier we have populated an array with the elements
275 -- that have least one element dependent on them
276 --
277 -- This PARENT function should simply look through
278 -- this list to and return if that exists
279
280
281 FOR i IN 1..g_dependency_matrix.count LOOP
282
283 IF g_dependency_matrix(i).parent = element_id THEN
284 RETURN TRUE;
285 END IF;
286
287 END LOOP;
288
289 RETURN FALSE;
290
291 END parent;
292
293
294 -- Bug 3397484 ksoh Tue Jan 27 13:54:38 PST 2004
295 -- order ordered_array by prompt_sequence, need to pass plan_id in
296 FUNCTION populate_elements (row_elements IN ElementsArray, p_plan_id IN NUMBER)
297 RETURN ElementInfoArray IS
298
299 i NUMBER;
300 indx NUMBER;
301 ordered_array ElementInfoArray;
302
303 CURSOR c IS
304 select char_id
305 from qa_plan_chars
306 where plan_id = p_plan_id
307 and enabled_flag = 1
308 order by prompt_sequence;
309
310 BEGIN
311
312 -- This function takes the row_elements array that is passed
313 -- by the user of validate_row and copies the ids and validation
314 -- flags to the ordered_array. The ordered_array has continuous
315 -- indexing (e.g. 1,2,3,4..) unlike row_elements which is sparsed
316 -- (e.g. 4, 10, 120)
317
318 -- i := row_elements.FIRST;
319
320 -- ordered_array is being processed from index 1
321
322 -- ordered_array(ordered_array.count).id := 0;
323
324 -- WHILE (i <> row_elements.LAST) LOOP
325 OPEN c;
326 LOOP
327 FETCH c INTO i;
328 EXIT WHEN c%NOTFOUND;
329 indx := ordered_array.count;
330 -- bug 3419514 ksoh Wed Feb 4 12:19:04 PST 2004
331 -- must skip if row_elements(i) does not exist.
332 -- It would not exist for the following case:
333 -- say Quantity is in the history plan
334 -- but not in relationship with the parent plan.
335 -- From qapcb, when we call post_result_with_no_validation,
336 -- we pass only elements in pc history relationship.
337 -- And thus row_elements(i).validation_flag would fail for Quantity
338 -- old check does not cover this case
339 -- IF (i <= row_elements.LAST) THEN
340 IF row_elements.EXISTS(i) THEN
341 ordered_array(indx).id := i;
342 ordered_array(indx).validation_flag := nvl(row_elements(i).validation_flag, 'invalid');
343 END IF;
344 -- i := row_elements.NEXT(i);
345
346 END LOOP;
347
348 -- indx := ordered_array.count;
349 -- ordered_array(indx).id := i;
350 -- ordered_array(indx).validation_flag := nvl(row_elements(i).validation_flag, 'invalid');
351
352 RETURN ordered_array;
353
354 END populate_elements;
355
356
357 FUNCTION direct_child(x IN NUMBER, y IN NUMBER) RETURN BOOLEAN IS
358 --
359 -- Determine if x is a direct child of y. Implemented as simple
360 -- linear lookup.
361 --
362 BEGIN
363
364 FOR i IN 1..g_dependency_matrix.count LOOP
365 IF x = g_dependency_matrix(i).element_id AND
366 y = g_dependency_matrix(i).parent THEN
367 RETURN TRUE;
368 END IF;
369 END LOOP;
370 RETURN FALSE;
371
372 END direct_child;
373
374
375 FUNCTION descendant(x IN NUMBER, y IN NUMBER) RETURN BOOLEAN IS
376 --
377 -- Determine if x is a descendant of y. This is the logic:
378 -- x is a descendant of y either
379 -- 1. x is a direct child or
380 -- 2. x is the child of some z and z is a descendant of y.
381 --
382 BEGIN
383
384 IF direct_child(x, y) THEN
385 RETURN TRUE;
386 END IF;
387
388 FOR i IN 1..g_dependency_matrix.count LOOP
389 IF x = g_dependency_matrix(i).element_id AND
390 descendant(g_dependency_matrix(i).parent, y) THEN
391 RETURN TRUE;
392 END IF;
393 END LOOP;
394
395 RETURN FALSE;
396
397 END descendant;
398
399
400 PROCEDURE pushdown ( position IN NUMBER, element_id IN NUMBER, assorted_array
401 IN OUT NOCOPY ElementInfoArray) IS
402
403 BEGIN
404
405 FOR i IN REVERSE position..assorted_array.count-1 LOOP
406 assorted_array(i+1).id := assorted_array(i).id;
407 END LOOP;
408
409 assorted_array(position).id := element_id;
410
411 END pushdown;
412
413
414 PROCEDURE determine_navigation_order (ordered_array IN OUT
415 NOCOPY ElementInfoArray) IS
416
417 KEY NUMBER;
418 ancestor BOOLEAN;
419
420 assorted_array ElementInfoArray;
421
422 BEGIN
423
424 ancestor := false;
425
426 FOR j IN 0..ordered_array.count-1 LOOP
427
428 IF parent(ordered_array(j).id) THEN
429
430 -- If parent, then check if any of the children is present
431 -- in the already assorted list
432
433 FOR t IN 0..assorted_array.count-1 LOOP
434
435 IF descendant (assorted_array(t).id, ordered_array(j).id) THEN
436 ancestor := TRUE;
437 pushdown(t, ordered_array(j).id, assorted_array);
438 exit;
439 END IF;
440 END LOOP;
441 END IF;
442
443 IF NOT ancestor THEN
444 assorted_array(assorted_array.count).id := ordered_array(j).id;
445 END IF;
446
447 ancestor := FALSE;
448
449 END LOOP;
450
451 FOR q IN 0..assorted_array.count-1 LOOP
452 ordered_array(q).id := assorted_array(q).id;
453 -- dbms_output.put_line(ordered_array(q).id);
454 END LOOP;
455
456 END determine_navigation_order;
457
458
459 PROCEDURE append_errors (element_id IN NUMBER, element_error_list IN
460 ErrorArray, row_error_list IN OUT NOCOPY ErrorArray) IS
461
462 ind NUMBER;
463 i NUMBER;
464
465 BEGIN
466
467 ind := row_error_list.count + 1;
468
469 --
470 -- Bug 5331420
471 -- Corrected small issue with this routine before the above
472 -- bug fix can continue. The indexes for element_error_list
473 -- is not linear, so we should use WHILE loop instead of FOR.
474 -- bso Thu Jun 15 16:22:54 PDT 2006
475 --
476 -- FOR i IN 1..element_error_list.count LOOP
477 --
478
479 i := element_error_list.FIRST;
480 WHILE i IS NOT NULL LOOP
481
482 IF (element_error_list(i).error_code <> ok) THEN
483
484 row_error_list(ind).element_id := element_id;
485 row_error_list(ind).error_code :=
486 element_error_list(i).error_code;
487
488 ind := ind + 1;
489
490 END IF;
491 i := element_error_list.NEXT(i);
492
493 END LOOP;
494
495 END append_errors;
496
497
498 FUNCTION no_errors (error_array IN ErrorArray)
499 RETURN BOOLEAN IS
500
501 BEGIN
502
503
504 FOR i IN 1..error_array.count LOOP
505 IF error_array(i).error_code <> ok THEN
506 RETURN FALSE;
507 END IF;
508 END LOOP;
509
510 RETURN TRUE;
511
512 END no_errors;
513
514
515 FUNCTION validate_enabled (plan_id IN NUMBER, element_id IN NUMBER)
516 RETURN NUMBER IS
517
518 flag NUMBER;
519
520 BEGIN
521
522 flag := qa_plan_element_api.get_enabled_flag(plan_id, element_id);
523
524 IF (flag = 1) THEN
525 RETURN ok;
526 ELSE
527 RETURN not_enabled_error;
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 RETURN no_data_found_error;
533
534 END validate_enabled;
535
536
537 FUNCTION validate_mandatory_revision (plan_id IN NUMBER,
538 element_id IN NUMBER,
539 value IN NUMBER)
540 RETURN NUMBER IS
541
542 revision_flag NUMBER;
543
544 BEGIN
545
546 IF (element_id = qa_ss_const.revision) THEN
547 revision_flag := g_revision_qty_cntrl_code;
548
549 ELSIF (element_id = qa_ss_const.comp_revision) THEN
550 revision_flag := g_comp_revision_qty_cntrl_code;
551 END IF;
552
553 IF ( revision_flag = 1) and (value is not NULL) THEN
554 return not_revision_controlled_error;
555 END IF;
556
557 IF ( revision_flag = 2) and (value is NULL) THEN
558 return mandatory_revision_error;
559 END IF;
560
561 RETURN ok;
562
563 EXCEPTION
564 WHEN OTHERS THEN
565 RETURN unknown_error;
566
567 END validate_mandatory_revision;
568
569 -- anagarwa Mon Feb 24 17:08:57 PST 2003
570 -- Bug 2808693
571 -- Overloaded method validate_mandatory_revision.
572 -- Here value is of type VARCHAR2 to support data being entered from selfservice
573
574 FUNCTION validate_mandatory_revision (plan_id IN NUMBER,
575 element_id IN NUMBER,
576 value IN VARCHAR2)
577 RETURN NUMBER IS
578
579 revision_flag NUMBER;
580
581 BEGIN
582
583 IF (element_id = qa_ss_const.revision) THEN
584 revision_flag := g_revision_qty_cntrl_code;
585
586 ELSIF (element_id = qa_ss_const.comp_revision) THEN
587 revision_flag := g_comp_revision_qty_cntrl_code;
588 END IF;
589
590 IF ( revision_flag = 1) and (value is not NULL) THEN
591 return not_revision_controlled_error;
592 END IF;
593
594 IF ( revision_flag = 2) and (value is NULL) THEN
595 return mandatory_revision_error;
596 END IF;
597
598 RETURN ok;
599
600 EXCEPTION
601 WHEN OTHERS THEN
602 RETURN unknown_error;
603
604 END validate_mandatory_revision;
605
606
607 FUNCTION validate_mandatory_locator (plan_id IN NUMBER,
608 element_id IN NUMBER,
609 value IN NUMBER)
610 RETURN NUMBER IS
611
612 BEGIN
613
614 --
615 -- delay mandatory validation until we validate
616 -- the locator keyflex
617 --
618
619 RETURN ok;
620
621 END validate_mandatory_locator;
622
623
624 -- The Function validate_mandatory_locator has been overloaded.
625 -- The overloading is required because History record insert procedure
626 -- is called by Parent child call, locator values is passed as locator
627 -- name (like 6.6.6..)and not as locator_id.
628 -- Bug 2700230.suramasw Mon Dec 23 03:06:30 PST 2002.
629
630 FUNCTION validate_mandatory_locator (plan_id IN NUMBER,
631 element_id IN NUMBER,
632 value IN VARCHAR2)
633 RETURN NUMBER IS
634
635 BEGIN
636
637 --
638 -- delay mandatory validation until we validate
639 -- the locator keyflex
640 --
641
642 RETURN ok;
643
644 END validate_mandatory_locator;
645
646
647 FUNCTION validate_mandatory (row_record IN RowRecord, element_id IN NUMBER,
648 value IN VARCHAR2)
649 RETURN NUMBER IS
650
651 element_name VARCHAR2(240);
652 m_flag NUMBER;
653
654 BEGIN
655
656 IF (element_id = qa_ss_const.revision) or
657 (element_id = qa_ss_const.comp_revision) THEN
658
659 RETURN validate_mandatory_revision(row_record.plan_id, element_id,
660 value);
661
662 END IF;
663
664 -- Modified the code below to enable History Records to be created
665 -- when History Relationship is present.
666 -- Bug 2700230.suramasw Mon Dec 23 03:06:30 PST 2002.
667
668 /*
669 IF (element_id = qa_ss_const.locator) or
670 (element_name = qa_ss_const.comp_locator) THEN
671 */
672
673 IF (element_id IN( qa_ss_const.locator,qa_ss_const.to_locator,
674 qa_ss_const.comp_locator)) THEN
675
676 RETURN validate_mandatory_locator(row_record.plan_id, element_id,
677 value);
678 END IF;
679
680
681 m_flag := qa_plan_element_api.get_mandatory_flag(row_record.plan_id,
682 element_id);
683
684 IF ( (m_flag = 2) or (value IS NOT NULL) ) THEN
685 RETURN ok;
686 END IF;
687
688 RETURN mandatory_error;
689
690 EXCEPTION
691 WHEN OTHERS THEN
692 RETURN no_data_found_error;
693
694 END validate_mandatory;
695
696
697 FUNCTION validate_kf_item (row_elements IN ElementsArray,
698 row_record IN RowRecord, element_id IN NUMBER, value IN VARCHAR2,
699 result_holder IN OUT NOCOPY ResultRecord)
700 RETURN NUMBER IS
701
702 id NUMBER;
703 error_code NUMBER;
704 x_where_clause VARCHAR2(500);
705
706 CURSOR c2 (i_id NUMBER, o_id NUMBER) IS
707 select restrict_subinventories_code, restrict_locators_code,
708 location_control_code, revision_qty_control_code
709 from mtl_system_items
710 where inventory_item_id = i_id
711 and organization_id = o_id;
712
713 BEGIN
714 IF (value is NULL) THEN
715 RETURN ok;
716 END IF;
717
718 -- Bug 3593287
719 --
720 -- Item validation needs to be revised. The original
721 -- implementation was not correct. The subsequent bug
722 -- fix 3506231 is also not correct, introducing new bug
723 -- 3593287. The proper design is this
724 --
725 -- if Production Line is in plan and is not null then
726 -- we should restrict item further by wip production line
727 -- or flow production line
728 --
729 -- This is an exact rephrase from QLTRES mimicing the Forms
730 -- validation logic. Nowhere do we involve sales order line.
731 --
732 -- In addition, we want to make absolutely certain that
733 -- Bug 3506231 does not recur. To prevent that always
734 -- check row_elements.exists(i) before accessing
735 -- row_elements(i).value
736 --
737 -- bso Mon Apr 26 18:15:10 PDT 2004
738 --
739 -- original comment now obsolete:
740 -- if production line is in the plan then
741 -- if so line number is in the plan then
742 -- if the vlaue for so line number is NULL then
743 -- x_where_clause := NULL;
744 --
745
746 IF (qa_plan_element_api.element_in_plan(row_record.plan_id,
747 qa_ss_const.production_line) AND
748 row_elements.exists(qa_ss_const.production_line) AND
749 row_elements(qa_ss_const.production_line).value IS NOT NULL) THEN
750
751 x_where_clause :=
752 'inventory_item_id IN ' ||
753 '((SELECT primary_item_id ' ||
754 'FROM wip_repetitive_items_v ' ||
755 'WHERE organization_id = ' || g_org_id || ' AND ' ||
756 'line_code = ''' ||
757 row_elements(qa_ss_const.production_line).value ||
758 ''') ' ||
759 'UNION ALL ' ||
760 '(SELECT assembly_item_id '||
761 'FROM bom_operational_routings_v '||
762 'WHERE organization_id = ' || g_org_id || ' AND ' ||
763 'line_code = ''' ||
764 row_elements(qa_ss_const.production_line).value ||
765 '''))';
766
767 ELSE
768 x_where_clause := NULL;
769 END IF;
770
771 IF (
772 FND_FLEX_KEYVAL.validate_segs(
773 operation => 'CHECK_COMBINATION',
774 key_flex_code => 'MSTK',
775 appl_short_name => 'INV',
776 structure_number => '101',
777 concat_segments => value,
778 data_set => g_org_id,
779 where_clause => x_where_clause)) THEN
780
781 id := FND_FLEX_KEYVAL.combination_id;
782
783 IF (id = 0) THEN
784 RETURN item_keyflex_error;
785 END IF;
786
787 OPEN c2 (id, g_org_id);
788 FETCH c2 INTO g_restrict_subinv_code, g_restrict_locators_code,
789 g_location_control_code, g_revision_qty_cntrl_code;
790 CLOSE c2;
791
792 result_holder.id := id;
793 g_item_id := id;
794
795 RETURN ok;
796
797 ELSE
798 RETURN item_keyflex_error;
799
800 -- NEED FURTHER WORK: FND_FLEX_KEYVAL.error_message
801
802 END IF;
803
804 EXCEPTION
805 WHEN OTHERS THEN
806 RETURN item_keyflex_error;
807
808 END validate_kf_item;
809
810 -- Bug 5248191. Wrote a new method to validate Asset Group
811 -- as Asset Group may be in production/maintenance org.
812 -- saugupta Wed, 02 Aug 2006 01:42:09 -0700 PDT
813 FUNCTION validate_kf_asset_group (row_elements IN ElementsArray,
814 row_record IN RowRecord, element_id IN NUMBER, value IN VARCHAR2,
815 result_holder IN OUT NOCOPY ResultRecord)
816 RETURN NUMBER IS
817
818 id NUMBER;
819 error_code NUMBER;
820 x_where_clause VARCHAR2(500);
821
822 CURSOR c2 (i_value VARCHAR2, o_id NUMBER) IS
823 SELECT restrict_subinventories_code, restrict_locators_code,
824 location_control_code, revision_qty_control_code,inventory_item_id
825 FROM mtl_system_items_kfv msi, mtl_parameters mp
826 WHERE msi.concatenated_segments = i_value
827 AND msi.organization_id = mp.organization_id
828 AND mp.maint_organization_id = o_id
829 AND rownum = 1;
830 BEGIN
831
832 IF (value is NULL) THEN
833 RETURN ok;
834 END IF;
835
836 OPEN c2 (value, g_org_id);
837 FETCH c2 INTO g_restrict_subinv_code, g_restrict_locators_code,
838 g_location_control_code, g_revision_qty_cntrl_code,id;
839 IF c2%NOTFOUND THEN
840 CLOSE c2;
841 RETURN item_keyflex_error;
842 END IF;
843 CLOSE c2;
844
845 result_holder.id := id;
846 g_item_id := id;
847
848 RETURN ok;
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 RETURN item_keyflex_error;
853 END validate_kf_asset_group;
854
855 FUNCTION validate_kf_comp_item (row_elements IN ElementsArray, row_record IN
856 RowRecord, element_id IN NUMBER, value IN VARCHAR2, result_holder IN OUT
857 NOCOPY ResultRecord)
858 RETURN NUMBER IS
859
860 id NUMBER;
861 error_code NUMBER;
862 x_where_clause VARCHAR2(500);
863
864 CURSOR c2 (i_id NUMBER, o_id NUMBER) IS
865 select restrict_subinventories_code, restrict_locators_code,
866 location_control_code, revision_qty_control_code
867 from mtl_system_items
868 where inventory_item_id = i_id
869 and organization_id = o_id;
870
871 BEGIN
872
873 IF (value is NULL) THEN
874 RETURN ok;
875 END IF;
876
877 -- Bug 3506231.suramasw.
878 -- Bug 3593287
879 --
880 -- A large chunk of code is removed due to error in
881 -- initial implementation. See validate_kf_item for more
882 -- info. For Component Item, we will use a simple relaxed
883 -- validation. Since the validation API is not being used
884 -- as LOV, it will suffice to give a relaxed superset
885 -- validation instead of a very accurate one. See QLTRES
886 -- for precise comp item LOV conditions which is dependent
887 -- on either WIP or BOM depending on what elements are
888 -- present in the plan.
889 --
890 -- bso Mon Apr 26 19:56:05 PDT 2004
891 --
892 -- x_where_clause := NULL;
893
894 IF (
895 FND_FLEX_KEYVAL.validate_segs(
896 operation => 'CHECK_COMBINATION',
897 key_flex_code => 'MSTK',
898 appl_short_name => 'INV',
899 structure_number => '101',
900 concat_segments => value,
901 data_set => g_org_id,
902 where_clause => x_where_clause)) THEN
903
904 id := FND_FLEX_KEYVAL.combination_id;
905
906 IF (id = 0) THEN
907 RETURN comp_item_keyflex_error;
908 END IF;
909
910 OPEN c2 (id, g_org_id);
911 FETCH c2 INTO g_comp_restrict_subinv_code,
912 g_comp_restrict_locators_code, g_comp_location_control_code,
913 g_comp_revision_qty_cntrl_code;
914 CLOSE c2;
915
916 result_holder.id := id;
917 g_comp_item_id := id;
918
919 RETURN ok;
920
921 ELSE
922 RETURN comp_item_keyflex_error;
923
924 -- NEED FURTHER WORK: FND_FLEX_KEYVAL.error_message
925
926 END IF;
927
928 EXCEPTION
929 WHEN OTHERS THEN
930 RETURN comp_item_keyflex_error;
931
932 END validate_kf_comp_item;
933
934
935 FUNCTION validate_kf_locator (row_elements IN ElementsArray, row_record IN
936 RowRecord, element_id IN NUMBER, value IN VARCHAR2, result_holder IN OUT
937 NOCOPY ResultRecord)
938 RETURN NUMBER IS
939
940 error_code NUMBER;
941 stock_locator NUMBER;
942 negative_inv NUMBER;
943 locator_type NUMBER;
944 locator_flag NUMBER;
945 mandatory_flag NUMBER;
946 id NUMBER;
947 x_where_clause VARCHAR2(500);
948
949 CURSOR C1 (org_id NUMBER) IS
950 select stock_locator_control_code, negative_inv_receipt_code
951 from mtl_parameters
952 where organization_id = org_id;
953
954
955 -- Bug 3381173. The cursor C2 was based on the view mtl_subinventories_val_v,
956 -- which is only for storage subs. Based the cursor on mtl_secondary_inventories
957 -- and added the where clause to differentiate receiving subs.
958 -- kabalakr Tue Jan 27 02:18:59 PST 2004.
959
960 CURSOR C2 (org_id NUMBER) IS
961 select locator_type
962 FROM mtl_secondary_inventories
963 WHERE organization_id = org_id
964 AND ((((SUBINVENTORY_TYPE <> 2) OR (SUBINVENTORY_TYPE IS NULL))
965 AND nvl(disable_date, sysdate+1) > sysdate)
966 OR (SUBINVENTORY_TYPE = 2))
967 AND secondary_inventory_name = g_subinventory;
968
969 /*
970 CURSOR C2 (org_id NUMBER) IS
971 select locator_type
972 from mtl_subinventories_val_v
973 where organization_id = org_id
974 and secondary_inventory_name = g_subinventory;
975 */
976
977 BEGIN
978
979 OPEN C1(g_org_id);
980 FETCH C1 INTO stock_locator, negative_inv;
981 CLOSE C1;
982
983 OPEN C2(g_org_id);
984 FETCH C2 INTO locator_type;
985 CLOSE C2;
986
987 locator_flag := qltinvcb.control(org_control => stock_locator,
988 sub_control => locator_type,
989 item_control => g_location_control_code,
990 restrict_flag => g_restrict_locators_code,
991 neg_flag => negative_inv);
992
993 IF locator_flag = 1 THEN
994 IF value IS NULL THEN
995 RETURN ok;
996 ELSE
997 RETURN not_locator_controlled_error;
998 END IF;
999 END IF;
1000
1001 mandatory_flag := qa_plan_element_api.get_mandatory_flag(
1002 row_record.plan_id, element_id);
1003
1004 IF ( (mandatory_flag = 1) and (value IS NULL) ) THEN
1005
1006 IF NOT flag_is_set(row_elements(element_id).validation_flag,
1007 background_element)
1008 AND NOT flag_is_set(row_elements(element_id).validation_flag,
1009 valid_element) THEN
1010 RETURN mandatory_error;
1011 ELSE
1012 RETURN ok;
1013 END IF;
1014 END IF;
1015
1016 IF locator_flag = 2 THEN
1017
1018 IF g_restrict_locators_code = 1 THEN
1019 x_where_clause := '(disable_date > sysdate or disable_date is null)
1020 and subinventory_code = ' ||
1021 '''' || g_subinventory || '''' ||
1022 ' and inventory_location_id in
1023 (select secondary_locator
1024 from mtl_secondary_locators
1025 where inventory_item_id = g_item_id and
1026 organization_id = ' ||
1027 '''' || g_org_id || '''' ||
1028 ' and subinventory_code = ' ||
1029 '''' || g_subinventory || '''' || '))';
1030
1031 ELSIF g_restrict_locators_code = 2 THEN
1032 x_where_clause := '(disable_date > sysdate or disable_date is null)
1033 and subinventory_code = ' ||
1034 '''' || g_subinventory || '''';
1035
1036 ELSE
1037 x_where_clause := null;
1038
1039 END IF;
1040
1041 IF (
1042 FND_FLEX_KEYVAL.validate_segs(
1043 operation => 'CHECK_COMBINATION',
1044 appl_short_name => 'INV',
1045 key_flex_code => 'MTLL',
1046 structure_number => '101',
1047 concat_segments => value,
1048 values_or_ids => 'V',
1049 data_set => g_org_id,
1050 where_clause => x_where_clause)) THEN
1051
1052 id := FND_FLEX_KEYVAL.combination_id;
1053 END IF;
1054 END IF;
1055
1056 IF locator_flag = 3 THEN
1057 IF (
1058 FND_FLEX_KEYVAL.validate_segs(
1059 operation => 'CREATE_COMBINATION',
1060 appl_short_name => 'INV',
1061 key_flex_code => 'MTLL',
1062 structure_number => '101',
1063 concat_segments => value,
1064 values_or_ids => 'V',
1065 data_set => g_org_id,
1066 where_clause => x_where_clause)) THEN
1067
1068 id := FND_FLEX_KEYVAL.combination_id;
1069 END IF;
1070
1071 END IF;
1072
1073 IF (id = 0) THEN
1074 RETURN locator_keyflex_error;
1075 END IF;
1076
1077 result_holder.id := id;
1078
1079 RETURN ok;
1080
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 RETURN locator_keyflex_error;
1084
1085 END validate_kf_locator;
1086
1087
1088 FUNCTION validate_kf_comp_locator (row_elements IN ElementsArray, row_record
1089 IN RowRecord, element_id IN NUMBER, value IN VARCHAR2, result_holder IN
1090 OUT NOCOPY ResultRecord)
1091 RETURN NUMBER IS
1092
1093 error_code NUMBER;
1094 stock_locator NUMBER;
1095 negative_inv NUMBER;
1096 locator_type NUMBER;
1097 locator_flag NUMBER;
1098 mandatory_flag NUMBER;
1099 id NUMBER;
1100 x_where_clause VARCHAR2(500);
1101
1102 CURSOR C1 (org_id NUMBER) IS
1103 select stock_locator_control_code, negative_inv_receipt_code
1104 from mtl_parameters
1105 where organization_id = org_id;
1106
1107 --
1108 -- Bug 6126260
1109 -- In the where clause replaced g_subinventory
1110 -- with g_comp_subinventory for getting locator type
1111 -- for the component subinventory
1112 -- bhsankar Mon Jul 16 05:51:51 PDT 2007
1113 --
1114 CURSOR C2 (org_id NUMBER) IS
1115 select locator_type
1116 from mtl_subinventories_val_v
1117 where organization_id = org_id
1118 and secondary_inventory_name = g_comp_subinventory;
1119
1120 BEGIN
1121
1122 OPEN C1(g_org_id);
1123 FETCH C1 INTO stock_locator, negative_inv;
1124 CLOSE C1;
1125
1126 OPEN C2(g_org_id);
1127 FETCH C2 INTO locator_type;
1128 CLOSE C2;
1129
1130 locator_flag := qltinvcb.control(
1131 org_control => stock_locator,
1132 sub_control => locator_type,
1133 item_control => g_comp_location_control_code,
1134 restrict_flag => g_comp_restrict_locators_code,
1135 neg_flag => negative_inv);
1136
1137 IF locator_flag = 1 THEN
1138 IF value IS NULL THEN
1139 RETURN ok;
1140 ELSE
1141 RETURN not_locator_controlled_error;
1142 END IF;
1143 END IF;
1144
1145 mandatory_flag := qa_plan_element_api.get_mandatory_flag(
1146 row_record.plan_id, element_id);
1147
1148 IF ( (mandatory_flag = 1) and (value IS NULL) ) THEN
1149 RETURN mandatory_error;
1150 END IF;
1151
1152 IF locator_flag = 2 THEN
1153 --
1154 -- Bug 6126260
1155 -- In the where clause replaced g_subinventory
1156 -- with g_comp_subinventory for getting locator type
1157 -- for the component subinventory
1158 -- bhsankar Mon Jul 16 05:51:51 PDT 2007
1159 --
1160 IF g_restrict_locators_code = 1 THEN
1161 x_where_clause := '(disable_date > sysdate or disable_date is null)
1162 and subinventory_code = ' ||
1163 '''' || g_comp_subinventory || '''' ||
1164 ' and inventory_location_id in
1165 (select secondary_locator
1166 from mtl_secondary_locators
1167 where inventory_item_id = x_item_id and
1168 organization_id = ' ||
1169 '''' || g_org_id || '''' ||
1170 ' and subinventory_code = ' ||
1171 '''' || g_comp_subinventory || '''' || '))';
1172
1173 ELSIF g_restrict_locators_code = 2 THEN
1174 x_where_clause := '(disable_date > sysdate or disable_date is null)
1175 and subinventory_code = ' ||
1176 '''' || g_comp_subinventory || '''';
1177
1178 ELSE
1179 x_where_clause := null;
1180
1181 END IF;
1182
1183 IF (
1184 FND_FLEX_KEYVAL.validate_segs(
1185 operation => 'CHECK_COMBINATION',
1186 appl_short_name => 'INV',
1187 key_flex_code => 'MTLL',
1188 structure_number => '101',
1189 concat_segments => value,
1190 values_or_ids => 'V',
1191 data_set => g_org_id,
1192 where_clause => x_where_clause)) THEN
1193
1194 id := FND_FLEX_KEYVAL.combination_id;
1195 END IF;
1196 END IF;
1197
1198 IF locator_flag = 3 THEN
1199 IF (
1200 FND_FLEX_KEYVAL.validate_segs(
1201 operation => 'CREATE_COMBINATION',
1202 appl_short_name => 'INV',
1203 key_flex_code => 'MTLL',
1204 structure_number => '101',
1205 concat_segments => value,
1206 values_or_ids => 'V',
1207 data_set => g_org_id,
1208 where_clause => x_where_clause)) THEN
1209
1210 id := FND_FLEX_KEYVAL.combination_id;
1211 END IF;
1212
1213 END IF;
1214
1215 IF (id = 0) THEN
1216 RETURN comp_locator_keyflex_error;
1217 END IF;
1218
1219 result_holder.id := id;
1220
1221 RETURN ok;
1222
1223 EXCEPTION
1224 WHEN OTHERS THEN
1225 RETURN comp_locator_keyflex_error;
1226
1227 END validate_kf_comp_locator;
1228
1229 -- Start of inclusions for NCM Hardcode Elements.
1230 -- suramasw Thu Oct 31 10:48:59 PST 2002.
1231 -- Bug 2449067.
1232
1233 FUNCTION validate_kf_bill_reference (row_elements IN ElementsArray,
1234 row_record IN RowRecord, element_id IN NUMBER, value IN VARCHAR2,
1235 result_holder IN OUT NOCOPY ResultRecord)
1236 RETURN NUMBER IS
1237
1238 id NUMBER;
1239 error_code NUMBER;
1240 x_where_clause VARCHAR2(240) := NULL;
1241
1242 CURSOR c2 (i_id NUMBER, o_id NUMBER) IS
1243 select restrict_subinventories_code, restrict_locators_code,
1244 location_control_code, revision_qty_control_code
1245 from mtl_system_items
1246 where inventory_item_id = i_id
1247 and organization_id = o_id;
1248
1249 BEGIN
1250
1251 IF (value is NULL) THEN
1252 RETURN ok;
1253 END IF;
1254
1255 IF (
1256 FND_FLEX_KEYVAL.validate_segs(
1257 operation => 'CHECK_COMBINATION',
1258 key_flex_code => 'MSTK',
1259 appl_short_name => 'INV',
1260 structure_number => '101',
1261 concat_segments => value,
1262 data_set => g_org_id,
1263 where_clause => x_where_clause)) THEN
1264
1265 id := FND_FLEX_KEYVAL.combination_id;
1266
1267 IF (id = 0) THEN
1268 RETURN bill_reference_keyflex_error;
1269 END IF;
1270
1271 OPEN c2 (id, g_org_id);
1272 FETCH c2 INTO g_bill_restrict_subinv_code, g_bill_restrict_locators_code,
1273 g_bill_location_control_code, g_bill_revision_qty_cntrl_code;
1274 CLOSE c2;
1275
1276 result_holder.id := id;
1277 g_bill_reference_id := id;
1278
1279 RETURN ok;
1280
1281 ELSE
1282 RETURN bill_reference_keyflex_error;
1283
1284 -- NEED FURTHER WORK: FND_FLEX_KEYVAL.error_message
1285
1286 END IF;
1287 EXCEPTION
1288 WHEN OTHERS THEN
1289 RETURN bill_reference_keyflex_error;
1290
1291 END validate_kf_bill_reference;
1292
1293
1294
1295 FUNCTION validate_kf_routing_reference (row_elements IN ElementsArray,
1296 row_record IN RowRecord, element_id IN NUMBER, value IN VARCHAR2,
1297 result_holder IN OUT NOCOPY ResultRecord)
1298 RETURN NUMBER IS
1299
1300 id NUMBER;
1301 error_code NUMBER;
1302 x_where_clause VARCHAR2(240) := NULL;
1303
1304 CURSOR c2 (i_id NUMBER, o_id NUMBER) IS
1305 select restrict_subinventories_code, restrict_locators_code,
1306 location_control_code, revision_qty_control_code
1307 from mtl_system_items
1308 where inventory_item_id = i_id
1309 and organization_id = o_id;
1310
1311 BEGIN
1312
1313 IF (value is NULL) THEN
1314 RETURN ok;
1315 END IF;
1316
1317 IF (
1318 FND_FLEX_KEYVAL.validate_segs(
1319 operation => 'CHECK_COMBINATION',
1320 key_flex_code => 'MSTK',
1321 appl_short_name => 'INV',
1322 structure_number => '101',
1323 concat_segments => value,
1324 data_set => g_org_id,
1325 where_clause => x_where_clause)) THEN
1326
1327 id := FND_FLEX_KEYVAL.combination_id;
1328
1329 IF (id = 0) THEN
1330 RETURN rtg_reference_keyflex_error;
1331 END IF;
1332
1333 OPEN c2 (id, g_org_id);
1334 FETCH c2 INTO g_rout_restrict_subinv_code, g_rout_restrict_locators_code,
1335 g_rout_location_control_code, g_rout_revision_qty_cntrl_code;
1336 CLOSE c2;
1337
1338 result_holder.id := id;
1339 g_routing_reference_id := id;
1340
1341 RETURN ok;
1342 ELSE
1343 RETURN rtg_reference_keyflex_error;
1344
1345 -- NEED FURTHER WORK: FND_FLEX_KEYVAL.error_message
1346
1347 END IF;
1348
1349 EXCEPTION
1350 WHEN OTHERS THEN
1351 RETURN rtg_reference_keyflex_error;
1352
1353 END validate_kf_routing_reference;
1354
1355
1356 FUNCTION validate_kf_to_locator (row_elements IN ElementsArray, row_record IN
1357 RowRecord, element_id IN NUMBER, value IN VARCHAR2, result_holder IN OUT
1358 NOCOPY ResultRecord)
1359 RETURN NUMBER IS
1360
1361 error_code NUMBER;
1362 stock_locator NUMBER;
1363 negative_inv NUMBER;
1364 locator_type NUMBER;
1365 locator_flag NUMBER;
1366 mandatory_flag NUMBER;
1367 id NUMBER;
1368 x_where_clause VARCHAR2(500);
1369
1370 CURSOR C1 (org_id NUMBER) IS
1371 select stock_locator_control_code, negative_inv_receipt_code
1372 from mtl_parameters
1373 where organization_id = org_id;
1374
1375 CURSOR C2 (org_id NUMBER) IS
1376 select locator_type
1377 from mtl_subinventories_val_v
1378 where organization_id = org_id
1379 and secondary_inventory_name = g_to_subinventory;
1380
1381
1382 BEGIN
1383
1384 OPEN C1(g_org_id);
1385 FETCH C1 INTO stock_locator, negative_inv;
1386 CLOSE C1;
1387
1388 OPEN C2(g_org_id);
1389 FETCH C2 INTO locator_type;
1390 CLOSE C2;
1391
1392 locator_flag := qltinvcb.control(org_control => stock_locator,
1393 sub_control => locator_type,
1394 item_control => g_location_control_code,
1395 restrict_flag => g_restrict_locators_code,
1396 neg_flag => negative_inv);
1397
1398 IF locator_flag = 1 THEN
1399 IF value IS NULL THEN
1400 RETURN ok;
1401 ELSE
1402 RETURN not_locator_controlled_error;
1403 END IF;
1404 END IF;
1405
1406 mandatory_flag := qa_plan_element_api.get_mandatory_flag(
1407 row_record.plan_id, element_id);
1408
1409 IF ( (mandatory_flag = 1) and (value IS NULL) ) THEN
1410
1411 IF NOT flag_is_set(row_elements(element_id).validation_flag,
1412 background_element)
1413 AND NOT flag_is_set(row_elements(element_id).validation_flag,
1414 valid_element) THEN
1415 RETURN mandatory_error;
1416 ELSE
1417 RETURN ok;
1418 END IF;
1419 END IF;
1420
1421 IF locator_flag = 2 THEN
1422
1423 IF g_restrict_locators_code = 1 THEN
1424 x_where_clause := '(disable_date > sysdate or disable_date is null)
1425 and subinventory_code = ' ||
1426 '''' || g_to_subinventory || '''' ||
1427 ' and inventory_location_id in
1428 (select secondary_locator
1429 from mtl_secondary_locators
1430 where inventory_item_id = g_item_id and
1431 organization_id = ' ||
1432 '''' || g_org_id || '''' ||
1433 ' and subinventory_code = ' ||
1434 '''' || g_to_subinventory || '''' || '))';
1435
1436 ELSIF g_restrict_locators_code = 2 THEN
1437 x_where_clause := '(disable_date > sysdate or disable_date is null)
1438 and subinventory_code = ' ||
1439 '''' || g_to_subinventory || '''';
1440
1441 ELSE
1442 x_where_clause := null;
1443
1444 END IF;
1445
1446 IF (
1447 FND_FLEX_KEYVAL.validate_segs(
1448 operation => 'CHECK_COMBINATION',
1449 appl_short_name => 'INV',
1450 key_flex_code => 'MTLL',
1451 structure_number => '101',
1452 concat_segments => value,
1453 values_or_ids => 'V',
1454 data_set => g_org_id,
1455 where_clause => x_where_clause)) THEN
1456
1457 id := FND_FLEX_KEYVAL.combination_id;
1458 END IF;
1459 END IF;
1460
1461 IF locator_flag = 3 THEN
1462 IF (
1463 FND_FLEX_KEYVAL.validate_segs(
1464 operation => 'CREATE_COMBINATION',
1465 appl_short_name => 'INV',
1466 key_flex_code => 'MTLL',
1467 structure_number => '101',
1468 concat_segments => value,
1469 values_or_ids => 'V',
1470 data_set => g_org_id,
1471 where_clause => x_where_clause)) THEN
1472
1473 id := FND_FLEX_KEYVAL.combination_id;
1474 END IF;
1475
1476 END IF;
1477
1478 IF (id = 0) THEN
1479 RETURN to_locator_keyflex_error;
1480 END IF;
1481
1482 result_holder.id := id;
1483
1484 RETURN ok;
1485
1486 EXCEPTION
1487 WHEN OTHERS THEN
1488 RETURN to_locator_keyflex_error;
1489
1490 END validate_kf_to_locator;
1491
1492 -- End of inclusions for NCM Hardcode Elements.
1493
1494
1495
1496 FUNCTION validate_keyflex (row_elements IN ElementsArray,
1497 row_record IN RowRecord, element_id IN NUMBER, value IN VARCHAR2,
1498 result_holder IN OUT NOCOPY ResultRecord)
1499 RETURN NUMBER IS
1500
1501 error_code NUMBER;
1502
1503 BEGIN
1504 IF flag_is_set( row_elements(element_id).validation_flag, id_derived) THEN
1505 result_holder.id := row_elements(element_id).id;
1506
1507 -- added the following to include new hardcoded element followup activity
1508 -- saugupta
1509
1510 IF element_id IN (qa_ss_const.item, qa_ss_const.comp_item,
1511 qa_ss_const.asset_group, qa_ss_const.asset_activity, qa_ss_const.followup_activity) THEN
1512 g_item_id := row_elements(element_id).id;
1513 END IF;
1514
1515 ELSIF (element_id = qa_ss_const.item) THEN
1516 error_code := validate_kf_item(row_elements, row_record, element_id,
1517 value, result_holder);
1518
1519 ELSIF (element_id = qa_ss_const.comp_item) THEN
1520 error_code := validate_kf_comp_item(row_elements, row_record,
1521 element_id, value, result_holder);
1522
1523 ELSIF (element_id = qa_ss_const.locator) THEN
1524 error_code := validate_kf_locator(row_elements, row_record, element_id,
1525 value, result_holder);
1526
1527 ELSIF (element_id = qa_ss_const.comp_locator) THEN
1528 error_code := validate_kf_comp_locator(row_elements, row_record,
1529 element_id, value, result_holder);
1530
1531 -- Bug 5248191. Modified check for Asset Group to call new function
1532 -- saugupta Wed, 02 Aug 2006 01:44:07 -0700 PDT
1533 ELSIF (element_id = qa_ss_const.asset_group) THEN
1534 error_code := validate_kf_asset_group(row_elements, row_record,
1535 element_id, value, result_holder);
1536
1537 ELSIF (element_id = qa_ss_const.asset_activity) THEN
1538 error_code := validate_kf_item(row_elements, row_record,
1539 element_id, value, result_holder);
1540
1541 -- added the following to include new hardcoded element followup activity
1542 -- saugupta
1543
1544 ELSIF (element_id = qa_ss_const.followup_activity) THEN
1545 error_code := validate_kf_item(row_elements, row_record,
1546 element_id, value, result_holder);
1547
1548
1549 -- Added the following for NCM Hardcode Elements.
1550 -- suramasw Thu Oct 31 10:48:59 PST 2002.
1551 -- Bug 2449067.
1552
1553 ELSIF (element_id = qa_ss_const.bill_reference) THEN
1554 error_code := validate_kf_bill_reference(row_elements, row_record,
1555 element_id, value, result_holder);
1556
1557 ELSIF (element_id = qa_ss_const.routing_reference) THEN
1558 error_code := validate_kf_routing_reference(row_elements, row_record,
1559 element_id, value, result_holder);
1560
1561 ELSIF (element_id = qa_ss_const.to_locator) THEN
1562 error_code := validate_kf_to_locator(row_elements, row_record,
1563 element_id, value, result_holder);
1564
1565 -- End of inclusions for NCM Hardcode Elements.
1566
1567
1568 END IF;
1569
1570 RETURN error_code;
1571
1572 EXCEPTION
1573 WHEN OTHERS THEN
1574 RETURN keyflex_error;
1575
1576 END validate_keyflex;
1577
1578 --
1579 -- Added to the IF-ELSIF ladder for newly added collection elements
1580 -- for ASO project. New entries are appended after Party_Name
1581 -- rkunchal Thu Jul 25 01:43:48 PDT 2002
1582 --
1583
1584 FUNCTION get_normalized_id (element_id IN NUMBER, value IN VARCHAR2, x_org_id
1585 IN NUMBER)
1586 RETURN NUMBER IS
1587
1588 id NUMBER;
1589
1590 BEGIN
1591
1592 IF (element_id = qa_ss_const.department)
1593 OR (element_id = qa_ss_const.to_department) THEN
1594 id := qa_plan_element_api.get_department_id(x_org_id, value);
1595
1596 ELSIF (element_id = qa_ss_const.job_name) THEN
1597 id := qa_plan_element_api.get_job_id(x_org_id, value);
1598
1599 ELSIF (element_id = qa_ss_const.production_line) THEN
1600 id := qa_plan_element_api.get_production_line_id(x_org_id, value);
1601
1602 ELSIF (element_id = qa_ss_const.resource_code) THEN
1603 id := qa_plan_element_api.get_resource_code_id(x_org_id, value);
1604
1605 ELSIF (element_id = qa_ss_const.vendor_name) THEN
1606 id := qa_plan_element_api.get_supplier_id(value);
1607
1608 ELSIF (element_id = qa_ss_const.po_number) THEN
1609 id := qa_plan_element_api.get_po_number_id(value);
1610
1611 ELSIF (element_id = qa_ss_const.customer_name) THEN
1612 id := qa_plan_element_api.get_customer_id(value);
1613
1614 ELSIF (element_id = qa_ss_const.sales_order) THEN
1615 id := qa_plan_element_api.get_so_number_id(value);
1616
1617 -- bug 16214422
1618 g_sales_order_id := id;
1619
1620 ELSIF (element_id = qa_ss_const.order_line) THEN
1621 -- bug 16214422
1622 -- Get the SO line id based on the SO header Id as well
1623 --
1624 --id := qa_plan_element_api.get_so_line_number_id(value);
1625 id := qa_plan_element_api.get_so_line_number_id(value, g_sales_order_id);
1626
1627 ELSIF (element_id = qa_ss_const.po_release_num) THEN
1628 id := qa_plan_element_api.get_po_release_number_id(value,
1629 g_po_header_id);
1630
1631 ELSIF (element_id = qa_ss_const.project_number) THEN
1632 id := qa_plan_element_api.get_project_number_id(value);
1633 --
1634 -- Bug 2672396. Need to keep a cache of the returned project ID
1635 -- bso Mon Nov 25 17:29:56 PST 2002
1636 --
1637 g_project_id := id;
1638
1639 ELSIF (element_id = qa_ss_const.task_number) THEN
1640 --
1641 -- Bug 2672396. Added g_project_id because task is a dependent element.
1642 -- bso Mon Nov 25 17:29:56 PST 2002
1643 --
1644 id := qa_plan_element_api.get_task_number_id(value, g_project_id);
1645
1646 ELSIF (element_id = qa_ss_const.rma_number) THEN
1647 id := qa_plan_element_api.get_rma_number_id(value);
1648
1649 ELSIF (element_id = qa_ss_const.license_plate_number) THEN
1650 id := qa_plan_element_api.get_lpn_id(value);
1651
1652 -- added the following to include new hardcoded element Transfer license plate number
1653 -- saugupta
1654
1655 ELSIF (element_id = qa_ss_const.xfr_license_plate_number) THEN
1656 id := qa_plan_element_api.get_xfr_lpn_id(value);
1657
1658
1659 ELSIF (element_id = qa_ss_const.contract_number) THEN
1660 id := qa_plan_element_api.get_contract_id(value);
1661
1662 ELSIF (element_id = qa_ss_const.contract_line_number) THEN
1663 id := qa_plan_element_api.get_contract_line_id(value);
1664
1665 ELSIF (element_id = qa_ss_const.deliverable_number) THEN
1666 id := qa_plan_element_api.get_deliverable_id(value);
1667
1668 ELSIF (element_id = qa_ss_const.work_order) THEN
1669 id := qa_plan_element_api.get_work_order_id(x_org_id, value);
1670
1671 --dgupta: Start R12 EAM Integration. Bug 4345492
1672 ELSIF (element_id = qa_ss_const.asset_instance_number) THEN
1673 id := qa_plan_element_api.get_asset_instance_id(value);
1674 --dgupta: End R12 EAM Integration. Bug 4345492
1675
1676 ELSIF (element_id = qa_ss_const.party_name) THEN
1677 id := qa_plan_element_api.get_party_id(value);
1678
1679 ELSIF (element_id = qa_ss_const.item_instance) THEN
1680 id := qa_plan_element_api.get_item_instance_id(value);
1681
1682 ELSIF (element_id = qa_ss_const.service_request) THEN
1683 id := qa_plan_element_api.get_service_request_id(value);
1684
1685 ELSIF (element_id = qa_ss_const.maintenance_requirement) THEN
1686 id := qa_plan_element_api.get_maintenance_req_id(value);
1687
1688 ELSIF (element_id = qa_ss_const.rework_job) THEN
1689 id := qa_plan_element_api.get_rework_job_id(x_org_id, value);
1690
1691 ELSIF (element_id = qa_ss_const.counter_name) THEN
1692 id := qa_plan_element_api.get_counter_name_id(value);
1693
1694 -- Added the following for NCM Hardcode Elements.
1695 -- suramasw Thu Oct 31 10:48:59 PST 2002.
1696 -- Bug 2449067.
1697
1698 ELSIF (element_id = qa_ss_const.lot_status) THEN
1699 id := qa_plan_element_api.get_lot_status_id(value);
1700
1701 ELSIF (element_id = qa_ss_const.serial_status) THEN
1702 id := qa_plan_element_api.get_serial_status_id(value);
1703
1704 -- End of inclusions for NCM Hardcode Elements.
1705
1706 -- R12 OPM Deviations. Bug 4345503 Start
1707 ELSIF (element_id = qa_ss_const.process_batch_num) THEN
1708 id := qa_plan_element_api.get_process_batch_id(value, x_org_id);
1709 g_process_batch_id := id;
1710
1711 ELSIF (element_id = qa_ss_const.process_batchstep_num) THEN
1712 id := qa_plan_element_api.get_process_batchstep_id
1713 (value,g_process_batch_id);
1714 g_process_batchstep_id := id;
1715
1716 ELSIF (element_id = qa_ss_const.process_operation) THEN
1717 id := qa_plan_element_api.get_process_operation_id
1718 (value,g_process_batch_id,g_process_batchstep_id);
1719
1720 ELSIF (element_id = qa_ss_const.process_activity) THEN
1721 id := qa_plan_element_api.get_process_activity_id
1722 (value,g_process_batch_id,g_process_batchstep_id);
1723 g_process_activity_id := id;
1724
1725 ELSIF (element_id = qa_ss_const.process_resource) THEN
1726 id := qa_plan_element_api.get_process_resource_id
1727 (value,g_process_batch_id,
1728 g_process_batchstep_id, g_process_activity_id);
1729 g_process_resource_id := id;
1730 ELSIF (element_id = qa_ss_const.process_parameter) THEN
1731 id := qa_plan_element_api.get_process_parameter_id
1732 (value,g_process_resource_id);
1733
1734 -- R12 OPM Deviations. Bug 4345503 End
1735 --R12 DR Integration . Bug 4345489 start
1736 ELSIF (element_id = qa_ss_const.repair_order_number) THEN
1737 id := qa_plan_element_api.get_repair_line_id
1738 (value);
1739 ELSIF (element_id = qa_ss_const.jtf_task_number) THEN
1740 id := qa_plan_element_api.get_jtf_task_id(value);
1741 --R12 Dr Integration. Bug 4345489 end
1742 END IF;
1743
1744 RETURN id;
1745
1746 END get_normalized_id;
1747
1748
1749 FUNCTION validate_normalized (row_elements IN ElementsArray,
1750 row_record IN RowRecord, element_id IN NUMBER, value IN VARCHAR2,
1751 result_holder IN OUT NOCOPY ResultRecord)
1752 RETURN NUMBER IS
1753
1754
1755 id NUMBER;
1756 sql_statement VARCHAR2(1500);
1757
1758 BEGIN
1759
1760 --
1761 -- Bug 2617638.
1762 -- Added the second conjunct because value can be null if there
1763 -- is a valid id. We want the procedure to continue in that case
1764 -- or the result_holder will be empty.
1765 -- bso Tue Oct 8 18:42:49 PDT 2002
1766 --
1767 IF (value is NULL AND NOT
1768 flag_is_set(row_elements(element_id).validation_flag, id_derived)) THEN
1769 RETURN ok;
1770 END IF;
1771
1772
1773 IF (flag_is_set(row_elements(element_id).validation_flag, id_derived)) THEN
1774 id := row_elements(element_id).id;
1775 ELSE
1776 id := get_normalized_id (element_id, value, g_org_id);
1777 IF (id IS NULL) THEN
1778 RETURN id_not_found_error;
1779 END IF;
1780 END IF;
1781
1782 result_holder.id := id;
1783
1784 --
1785 -- See Bug 2588213
1786 -- To support the element Maintenance Op Seq Number
1787 -- to be used along with Maintenance Workorder
1788 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
1789 -- Added to the IF ladder for Maintenance Workorder
1790 --
1791 IF (element_id = qa_ss_const.job_name) THEN
1792 g_wip_entity_id := id;
1793 ELSIF (element_id = qa_ss_const.po_number) THEN
1794 g_po_header_id := id;
1795 ELSIF (element_id = qa_ss_const.production_line) THEN
1796 g_line_id := id;
1797 ELSIF (element_id = qa_ss_const.work_order) THEN
1798 g_work_order_id := id;
1799 ELSIF (element_id = qa_ss_const.rma_number) THEN
1800 -- Bug 9773026. Assign RMA Header Id to the global variable.
1801 -- skolluku
1802 g_rma_header_id := id;
1803 --
1804 -- Bug 11659488
1805 -- Cache the Sales_order_id in the Global variable so that it can be used
1806 -- to filter the SO_line_numbers
1807 --
1808 ELSIF (element_id = qa_ss_const.sales_order) THEN
1809 g_sales_order_id := id;
1810 END IF;
1811
1812
1813 RETURN ok;
1814
1815 EXCEPTION
1816 WHEN OTHERS THEN
1817 RETURN id_not_found_error;
1818
1819 END validate_normalized;
1820
1821
1822 FUNCTION valid_against_sql (element_id IN NUMBER, x_org_id IN NUMBER,
1823 x_value IN VARCHAR2)
1824 RETURN BOOLEAN IS
1825
1826 result BOOLEAN;
1827
1828 BEGIN
1829
1830 -- Transaction Date is validated only in PO Inspection Transaction
1831
1832 IF (element_id = qa_ss_const.transaction_date) THEN
1833 result := qa_plan_element_api.validate_transaction_date(
1834 g_transaction_number);
1835 --
1836 -- Bug 8690822
1837 -- Separating the condition for UOM and Comp UOM,
1838 -- as Comp Item Id would have to be used for Comp UOM.
1839 -- skolluku
1840 --
1841 ELSIF (element_id = qa_ss_const.uom) THEN
1842 --OR (element_id = qa_ss_const.comp_uom) THEN
1843 result := qa_plan_element_api.validate_uom(
1844 x_org_id, g_item_id, x_value);
1845 --
1846 -- Bug 8690822
1847 -- Pass g_comp_item_id to validate_uom, as the validation has
1848 -- to be done for Comp UOM and not UOM.
1849 -- skolluku
1850 --
1851 ELSIF (element_id = qa_ss_const.comp_uom) THEN
1852 result := qa_plan_element_api.validate_uom(
1853 x_org_id, g_comp_item_id, x_value);
1854
1855 ELSIF (element_id = qa_ss_const.revision)
1856 OR (element_id = qa_ss_const.comp_revision) THEN
1857 result := qa_plan_element_api.validate_revision(
1858 x_org_id, g_item_id, x_value);
1859
1860 ELSIF (element_id = qa_ss_const.subinventory)
1861 OR (element_id = qa_ss_const.comp_subinventory) THEN
1862 result := qa_plan_element_api.validate_subinventory(
1863 x_org_id, x_value);
1864
1865 -- Bug 4558205. OA Framewok Integration UT bug fix.
1866 -- Lot and serial numbers are not validated in server
1867 -- side for stand alone QWB.
1868 -- srhariha. Tue Sep 27 03:14:23 PDT 2005.
1869
1870 ELSIF (element_id = qa_ss_const.lot_number) THEN
1871 IF(g_transaction_number is null) OR (g_transaction_number = -1) THEN
1872 result := qa_plan_element_api.validate_lot_num(x_org_id,
1873 g_item_id ,
1874 x_value);
1875 ELSE
1876 result := qa_plan_element_api.validate_lot_number(g_transaction_number,
1877 g_transaction_id,
1878 x_value);
1879 END IF;
1880
1881 ELSIF (element_id = qa_ss_const.serial_number) THEN
1882
1883 IF(g_transaction_number is null) OR (g_transaction_number = -1) THEN
1884
1885 result := qa_plan_element_api.validate_serial_num(x_org_id,
1886 g_item_id,
1887 g_lot_number,
1888 g_revision,
1889 x_value);
1890 ELSE
1891 result := qa_plan_element_api.validate_serial_number(g_transaction_number,
1892 g_transaction_id,
1893 g_lot_number,
1894 x_value);
1895 END IF;
1896
1897 -- End. Bug 4558205.
1898
1899 ELSIF (element_id = qa_ss_const.to_op_seq_num)
1900 OR (element_id = qa_ss_const.from_op_seq_num) THEN
1901 result := qa_plan_element_api.validate_op_seq_number(
1902 x_org_id, g_line_id,
1903 g_wip_entity_id, x_value);
1904
1905 --
1906 -- See Bug 2588213
1907 -- To support the element Maintenance Op Seq Number
1908 -- to be used along with Maintenance Workorder
1909 -- rkunchal Mon Sep 23 23:46:28 PDT 2002
1910 --
1911
1912 ELSIF (element_id = qa_ss_const.maintenance_op_seq) THEN
1913 result := qa_plan_element_api.validate_maintenance_op_seq(
1914 x_org_id, g_work_order_id, x_value);
1915 --
1916 -- End of inclusions for Bug 2588213
1917 --
1918
1919
1920 ELSIF (element_id = qa_ss_const.po_line_num) THEN
1921
1922 g_po_line_number := x_value;
1923 result := qa_plan_element_api.validate_po_line_number(
1924 g_po_header_id, x_value);
1925
1926 ELSIF (element_id = qa_ss_const.po_shipment_num) THEN
1927 result := qa_plan_element_api.validate_po_shipments(
1928 g_po_line_number,
1929 g_po_header_id, x_value);
1930
1931 ELSIF (element_id = qa_ss_const.receipt_num) THEN
1932 result := qa_plan_element_api.validate_receipt_number(x_value);
1933
1934 /*
1935 ELSIF (element_id = qa_ss_const.comp_serial_number) THEN
1936 result := TRUE;
1937
1938 ELSIF (element_id = qa_ss_const.comp_lot_number) THEN
1939 result := TRUE;
1940
1941 ELSIF (element_id = qa_ss_const.quantity) THEN
1942 result := TRUE;
1943
1944 ELSIF (element_id = qa_ss_const.asset_number) THEN
1945 result := TRUE;
1946 */
1947 -- Added the following for NCM Hardcode Elements.
1948 -- suramasw Thu Oct 31 10:48:59 PST 2002.
1949 -- Bug 2449067.
1950
1951 ELSIF (element_id = qa_ss_const.to_subinventory) THEN
1952 result := qa_plan_element_api.validate_to_subinventory(
1953 x_org_id, x_value);
1954
1955 /*
1956 ELSIF (element_id = qa_ss_const.lot_status) THEN
1957 result := qa_plan_element_api.validate_lot_status(x_value);
1958
1959 ELSIF (element_id = qa_ss_const.concurrent_request_id) THEN
1960 result := TRUE;
1961
1962 ELSIF (element_id = qa_ss_const.nonconformance_code) THEN
1963 result := TRUE;
1964
1965 ELSIF (element_id = qa_ss_const.date_opened) THEN
1966 result := TRUE;
1967
1968 ELSIF (element_id = qa_ss_const.date_closed) THEN
1969 result := TRUE;
1970
1971 ELSIF (element_id = qa_ss_const.days_to_close) THEN
1972 result := TRUE;
1973 */
1974 -- the above code and the code above to_subinventory procedure was commented
1975 -- out to directly put an else clause which validates to TRUE since there is no
1976 -- special validation - suramasw Thu Oct 24 05:14:54 PDT 2002
1977
1978 ELSE
1979 result := TRUE;
1980
1981
1982 END IF;
1983
1984 RETURN result;
1985
1986 END valid_against_sql;
1987
1988
1989 FUNCTION validate_values (plan_id IN NUMBER, element_id IN NUMBER,
1990 value IN VARCHAR2)
1991 RETURN NUMBER IS
1992
1993 sql_string VARCHAR2(1500);
1994 ok_flag varchar2(240);
1995
1996 -- Bug 3111310. Used to have a SQL that select from
1997 -- dual which is inefficient
1998 -- saugupta Aug 2003
1999
2000 CURSOR c1 (p_id NUMBER, e_id NUMBER, v VARCHAR2) IS
2001 select '1'
2002 from qa_plan_char_value_lookups
2003 where plan_id = p_id
2004 and char_id = e_id
2005 and short_code = v;
2006 BEGIN
2007
2008 IF (value is NULL) THEN
2009 RETURN ok;
2010 END IF;
2011
2012 OPEN c1(plan_id, element_id, value);
2013 FETCH c1 INTO ok_flag;
2014 CLOSE c1;
2015
2016 IF ok_flag = '1' THEN
2017 RETURN ok;
2018 ELSE
2019 RETURN no_values_error;
2020 END IF;
2021
2022 EXCEPTION
2023 WHEN OTHERS THEN
2024 RETURN no_data_found_error;
2025
2026 END validate_values;
2027
2028 -- Bug 4343758. OA Framework Integration project.
2029 -- Added a new procedure. If the primitive values are validated
2030 -- then it must be copied to package globals, which will be used
2031 -- to validate other dependent elements.
2032 -- srhariha. Thu May 26 02:38:04 PDT 2005.
2033
2034 PROCEDURE copy_primitive_to_global(p_element_id IN NUMBER, p_value IN VARCHAR2) IS
2035
2036 BEGIN
2037
2038 IF (p_element_id = qa_ss_const.subinventory) THEN
2039 g_subinventory := p_value;
2040
2041 ELSIF (p_element_id = qa_ss_const.lot_number) THEN
2042 g_lot_number := p_value;
2043
2044 -- Added the following condition to enable History Records
2045 -- to be created when History Relationship is present.
2046 -- Bug 2700230.suramasw Mon Dec 23 03:06:30 PST 2002.
2047
2048 ELSIF (p_element_id = qa_ss_const.to_subinventory) THEN
2049 g_to_subinventory := p_value;
2050
2051 -- Bug 4558205. OA Framewok Integration UT bug fix.
2052 -- Lot and serial numbers are not validated in server
2053 -- side for stand alone QWB. Adding revision for serial
2054 -- number validation.
2055 -- srhariha. Tue Sep 27 03:14:23 PDT 2005.
2056
2057 ELSIF (p_element_id = qa_ss_const.revision) THEN
2058 g_revision := p_value;
2059 --
2060 -- Bug 6126260
2061 -- Added the extra condition to assign the value of the
2062 -- component subinventory to the global variable which
2063 -- would later be used to validate the comp subinventory locator.
2064 -- bhsankar Thu Jan 4 20:58:23 PST 2007
2065 --
2066 ELSIF (p_element_id = qa_ss_const.comp_subinventory) THEN
2067 g_comp_subinventory := p_value;
2068
2069 END IF;
2070
2071 END copy_primitive_to_global;
2072
2073
2074
2075 FUNCTION validate_primitive (plan_id IN NUMBER, row_elements IN ElementsArray,
2076 row_record IN RowRecord, element_id IN NUMBER, value IN VARCHAR2)
2077 RETURN NUMBER IS
2078
2079 sql_statement VARCHAR2(1500);
2080 valid_element BOOLEAN;
2081
2082 BEGIN
2083
2084 IF (value IS NULL) THEN
2085 RETURN ok;
2086 END IF;
2087
2088 -- The following code was put to reduce element_id check.The existing code
2089 -- for element_id check is commented out after the new code.
2090 -- suramasw Thu Oct 24 05:14:54 PDT 2002.
2091
2092 IF qa_plan_element_api.values_exist(plan_id, element_id) THEN
2093 RETURN validate_values(plan_id, element_id, value);
2094 END IF;
2095
2096 /*
2097 IF element_id IN (
2098 qa_ss_const.disposition,
2099 qa_ss_const.disposition_action,
2100 qa_ss_const.disposition_source,
2101 qa_ss_const.disposition_status,
2102 qa_ss_const.nonconformance_source,
2103 qa_ss_const.nonconform_severity,
2104 qa_ss_const.nonconform_priority,
2105 qa_ss_const.nonconformance_type,
2106 qa_ss_const.nonconformance_status) THEN
2107
2108 RETURN validate_values(plan_id, element_id, value);
2109 END IF;
2110 */
2111
2112 valid_element := valid_against_sql(element_id, g_org_id, value);
2113
2114 IF valid_element THEN
2115
2116 -- Bug 4343758. OA Framework Integration project.
2117 -- If the primitive values are validated then it
2118 -- must be copied to package globals, which will
2119 -- be used to validate other dependent elements.
2120 -- Existing logic wrapped into a new procedure.
2121 -- srhariha. Thu May 26 02:38:04 PDT 2005.
2122
2123 copy_primitive_to_global(p_element_id => element_id,
2124 p_value => value);
2125
2126 RETURN ok;
2127
2128 ELSE
2129 RETURN value_not_in_sql_error;
2130 END IF;
2131
2132 EXCEPTION
2133 WHEN OTHERS THEN
2134 RETURN value_not_in_sql_error;
2135
2136 END validate_primitive;
2137
2138
2139 FUNCTION validate_sql (element_id IN NUMBER, value IN VARCHAR2, x_org_id IN
2140 NUMBER, x_user_id IN NUMBER)
2141 RETURN NUMBER IS
2142
2143 sql_string VARCHAR2(1500);
2144
2145 BEGIN
2146
2147 IF (value IS NULL) THEN
2148 RETURN ok;
2149 END IF;
2150
2151 sql_string := qa_plan_element_api.get_sql_validation_string(element_id);
2152
2153 --
2154 -- Bug 9773062
2155 -- For all elements which need bind variables, add them to following block
2156 -- so that the binds can be added to their original sql validation string.
2157 -- skolluku
2158 --
2159 --
2160 -- Bug 11659488
2161 -- For So_line_number, the LOV has to be pruned by the SO_Header_id
2162 --
2163 IF (element_id IN (qa_ss_const.rma_line_num, qa_ss_const.order_line)) THEN -- add more elements if needed.
2164 sql_string := qa_chars_api.remove_order_by_from_sql(sql_string);
2165 IF (element_id = qa_ss_const.rma_line_num AND g_rma_header_id IS NOT NULL) THEN
2166 sql_string := sql_string || ' AND SH.HEADER_ID = ' || g_rma_header_id;
2167 END IF;
2168
2169 IF (element_id = qa_ss_const.order_line AND g_sales_order_id IS NOT NULL) THEN
2170 sql_string := sql_string || ' AND OEHA.HEADER_ID IN (SELECT oe.header_id FROM mtl_sales_orders mso, '
2171 || ' oe_order_headers_all oe, oe_transaction_types_tl ot '
2172 || ' WHERE mso.segment1 = oe.order_number AND oe.order_type_id = ot.transaction_type_id '
2173 || ' AND mso.segment2 = ot.name AND mso.sales_order_id = '|| g_sales_order_id || ')';
2174 END IF;
2175 END IF;
2176
2177 sql_string := qa_chars_api.format_sql_for_validation (sql_string,
2178 x_org_id, x_user_id);
2179
2180 IF qa_plan_element_api.value_in_sql (sql_string, value) THEN
2181 RETURN ok;
2182
2183 ELSE
2184 RETURN sql_validation_error;
2185
2186 END IF;
2187
2188 EXCEPTION WHEN OTHERS THEN
2189 RETURN sql_validation_error;
2190
2191 END validate_sql;
2192
2193
2194 FUNCTION validate_spec_limits (row_record IN RowRecord, element_id IN NUMBER,
2195 value IN VARCHAR2)
2196 RETURN NUMBER IS
2197
2198 lower_limit VARCHAR2(150);
2199 upper_limit VARCHAR2(150);
2200 datatype NUMBER;
2201
2202 BEGIN
2203
2204 IF (value IS NULL) THEN
2205 RETURN ok;
2206 END IF;
2207
2208 -- gets the spec limits and converts them to canonical
2209 -- BUG 3303285
2210 -- ksoh Mon Dec 29 13:33:02 PST 2003
2211 -- call overloaded get_spec_limits that takes in plan_id
2212 -- it performs uom conversion
2213 qa_plan_element_api.get_spec_limits(row_record.plan_id, row_record.spec_id,
2214 element_id,
2215 lower_limit, upper_limit);
2216
2217 IF (lower_limit IS NULL) OR (upper_limit IS NULL) THEN
2218 RETURN ok;
2219 END IF;
2220
2221 datatype := qa_plan_element_api.get_element_datatype(element_id);
2222
2223 IF qltcompb.compare(value, 6, lower_limit, null, datatype) THEN
2224 -- if (value < lower_limit) then
2225 RETURN lower_limit_error;
2226 END IF;
2227
2228 IF qltcompb.compare(value, 5, upper_limit, null, datatype) THEN
2229 -- if (value > upper_limit) then
2230 RETURN upper_limit_error;
2231 END IF;
2232
2233 RETURN ok;
2234
2235 EXCEPTION
2236 WHEN OTHERS THEN
2237 RETURN spec_error;
2238
2239 END validate_spec_limits;
2240
2241
2242 FUNCTION validate_char (value IN VARCHAR2, result_holder IN OUT NOCOPY ResultRecord)
2243 RETURN NUMBER IS
2244
2245 BEGIN
2246
2247 result_holder.canonical_value := substr(value, 1, 150);
2248
2249 RETURN ok;
2250
2251 END validate_char;
2252
2253
2254 FUNCTION validate_number (value IN VARCHAR2, length IN NUMBER,
2255 precision IN NUMBER, result_holder IN OUT NOCOPY ResultRecord)
2256 RETURN NUMBER IS
2257
2258 BEGIN
2259
2260 -- anagarwal Fri Dec 12 12:04:39 PST 2003
2261 -- Bug 3303276
2262 -- Decimal precision of numbers was not being used. The new call
2263 -- uses the passed precision to round off the number according to
2264 -- the precision specified in the plan
2265 -- We use qltdate.number_to_cannon to ensure that there are no errors
2266 -- if the number is in format 1,2345 instead of 1.2345 (a German user
2267 -- perhaps).
2268
2269 --result_holder.canonical_value := qltdate.any_to_number(value);
2270 result_holder.canonical_value := qltdate.number_to_canon(
2271 round(qltdate.any_to_number(value),
2272 nvl(precision, 240)));
2273
2274 RETURN ok;
2275
2276
2277 EXCEPTION
2278 WHEN OTHERS THEN
2279 RAISE;
2280
2281 END validate_number;
2282
2283
2284 FUNCTION validate_date (value IN VARCHAR2, result_holder IN OUT NOCOPY ResultRecord)
2285 RETURN NUMBER IS
2286
2287 BEGIN
2288 -- convert it to canonical format
2289 result_holder.canonical_value := qltdate.any_to_canon(value);
2290 RETURN ok;
2291
2292 EXCEPTION
2293 WHEN OTHERS THEN
2294 RAISE;
2295
2296 END validate_date;
2297
2298
2299 -- rkaza. bug 3220767. 10/29/2003. Follwing function not used.
2300 -- When coming from ss, we have to do the tz conversion in the middle tier.
2301 -- because server side initializations required for tz conversion to work
2302 -- on server side would not be done by ss tech stack as in forms.
2303 FUNCTION validate_datetime (value IN VARCHAR2, result_holder IN OUT NOCOPY ResultRecord)
2304 RETURN NUMBER IS
2305
2306 -- Bug 4965371
2307 -- Variable to hold the date converted
2308 -- from the User format into DD-MON-RRRR HH24:MI:SS
2309 -- format
2310 -- nutngare Sun Feb 12 04:31:28 PST 2006
2311
2312 l_value varchar2(50);
2313
2314 BEGIN
2315
2316 -- Bug 4965371
2317 -- Convert the format of the argument value which would be '2005/12/21 17:09:36' to the
2318 -- format compatible to the fnd_date.displayDT_to_date function which needs the value to
2319 -- be in the format 'DD-MON-RRRR HH24:MI:SS'. So used qltdate.any_to_user_dt to convert
2320 -- the value to the format 'DD-MON-RRRR HH24:MI:SS' and then call the Fnd_date functions.
2321 -- ntungare Sun Feb 12 04:35:39 PST 2006
2322
2323 l_value := qltdate.any_to_user_dt(value);
2324
2325 -- convert the value to server tz and then to canonical.
2326 -- Bug 4965371
2327 -- Using the converted value
2328 -- ntungare Sun Feb 12 04:35:20 PST 2006
2329
2330 result_holder.canonical_value :=
2331 Fnd_date.date_to_canonical(fnd_date.displayDT_to_date(l_value, calendar_aware=> FND_DATE.calendar_aware_alt));
2332
2333 RETURN ok;
2334
2335 EXCEPTION
2336 WHEN OTHERS THEN
2337 -- Bug 3318462. This exception is raised when date coming
2338 -- from self service and mobile, since data is in server
2339 -- timezone from history plans and it is required to distinguish
2340 -- data from Forms.
2341 -- saugupta Fri, 06 Feb 2004 00:03:57 -0800 PDT
2342 IF SQLCODE = -1861 THEN
2343 RETURN -1861;
2344 ELSE
2345 RAISE;
2346 END IF;
2347
2348 END validate_datetime;
2349
2350
2351 FUNCTION validate_datatype (plan_id IN NUMBER, element_id IN NUMBER,
2352 value IN VARCHAR2, row_elements IN OUT NOCOPY ElementsArray,
2353 result_holder IN OUT NOCOPY ResultRecord)
2354 RETURN NUMBER IS
2355
2356 data_type NUMBER;
2357 len NUMBER;
2358 precision NUMBER;
2359 error_code NUMBER;
2360 val_len NUMBER;
2361 -- Bug 3318462. New variable to get actual datatype
2362 -- of date time elements to distinguish between
2363 -- softcoded and hardcoded eements
2364 actual_datatype NUMBER;
2365
2366 BEGIN
2367 -- bug 3178307. rkaza. 10/06/2003.
2368 -- Modifed the function for Timezone Support.
2369
2370 data_type := qa_chars_api.datatype(element_id);
2371
2372 len := qa_chars_api.display_length(element_id);
2373
2374 -- Bug 3318462. History child plans showing wrong
2375 -- client timezone. It is saving date time in client timezone
2376 -- for child plans due to this, difference is again added when
2377 -- viewing child plans
2378 -- saugupta Wed, 14 Jan 2004 04:39:30 -0800 PDT
2379 actual_datatype := qa_plan_element_api.get_actual_datatype(element_id);
2380
2381 --
2382 -- See Bug 2624112
2383 -- The decimal precision for a number type collection
2384 -- element is to be configured at plan level.
2385 --
2386 -- Validation would be appropriate when decimal precision is
2387 -- taken from plan definition rather than element setup
2388 --
2389 -- Before this change
2390 -- precision := qa_chars_api.decimal_precision(element_id);
2391 -- After this change
2392 -- precision := nvl(qa_plan_element_api.decimal_precision(plan_id, element_id),
2393 -- qa_chars_api.decimal_precision(element_id));
2394 --
2395 -- rkunchal Wed Oct 16 05:32:33 PDT 2002
2396 --
2397
2398 precision := nvl(qa_plan_element_api.decimal_precision(plan_id, element_id),
2399 qa_chars_api.decimal_precision(element_id));
2400
2401 IF (data_type = qa_ss_const.date_datatype) THEN
2402 error_code := validate_date(value, result_holder);
2403
2404 ELSIF (data_type = qa_ss_const.datetime_datatype) THEN
2405 val_len := length(value);
2406 if substr(value, val_len - 1, 2) = '.0' then
2407 -- all hardcoded datetimes in self service come here.
2408 -- When coming from Self service, a '.0' is appended to the value
2409 -- because of an implicit conversion from date to string.
2410 -- So chop it off.
2411 error_code := validate_date(substr(value, 1, val_len-2), result_holder);
2412 else
2413 -- softcoded datetime elements from ss and mobile comes here
2414 -- From ss, values are already in server tz and in canonical format
2415 -- values from Mobile are also in server timezone
2416 -- saugupta Fri, 06 Feb 2004 00:14:24 -0800 PDT
2417
2418 -- Bug 3318462. Also all forms datetime elements come here
2419 -- Forms calls validate_api and data for softcoded is coming
2420 -- from DISPLAYxx. So for History Plans softcoded date is getting saved in
2421 -- client timezone. To correct it, using get_actual_datatype() to check the
2422 -- real datatype of the element. If it is character than call validate_datetime
2423 -- else its hardcoded so calling validate_date.
2424 -- saugupta Wed, 14 Jan 2004 04:40:08 -0800 PDT
2425
2426 IF (actual_datatype = qa_ss_const.character_datatype) THEN
2427 error_code := validate_datetime(value, result_holder);
2428
2429 -- Bug 3318462. From ss and mobile softcoded datetime elements are
2430 -- in server timezone. Above call will throw and exception. Returned
2431 -- the error number from validate_datetime() and handled it in below
2432 -- statement to make sure that ss and mobile calls correct function.
2433 -- saugupta Fri, 06 Feb 2004 00:18:38 -0800 PDT
2434
2435 IF (error_code = -1861 ) THEN
2436 error_code := validate_date(value, result_holder);
2437 END IF;
2438 ELSE
2439 error_code := validate_date(value, result_holder);
2440 END IF;
2441 end if;
2442
2443 ELSIF (data_type = qa_ss_const.number_datatype) THEN
2444 error_code := validate_number(value, len, precision,
2445 result_holder);
2446 --
2447 -- Bug 3402251. In order to use the round-up values in
2448 -- further assign-a-value actions, the canonical value
2449 -- needs to be copied back to the row element, readjusted
2450 -- to client format.
2451 -- bso Mon Feb 9 21:42:20 PST 2004
2452 --
2453 row_elements(element_id).value := to_char(
2454 fnd_number.canonical_to_number(result_holder.canonical_value));
2455
2456 -- Bug 2427337. Added following elsif condition for longcomment datatype
2457 -- rponnusa Tue Jun 25 06:15:48 PDT 2002
2458
2459 ELSIF (data_type = qa_ss_const.comment_datatype) THEN
2460 error_code := validate_comment(value, result_holder);
2461
2462 ELSE
2463 error_code := validate_char(value, result_holder);
2464
2465 END IF;
2466
2467 RETURN ok;
2468
2469 EXCEPTION
2470 WHEN INVALID_NUMBER OR VALUE_ERROR THEN
2471 RETURN invalid_number_error;
2472
2473 WHEN INVALID_DATE OR INVALID_DATE_FORMAT THEN
2474 RETURN invalid_date_error;
2475
2476 WHEN OTHERS THEN
2477 RETURN unknown_error;
2478
2479 END validate_datatype;
2480
2481
2482 FUNCTION evaluate_trigger (condition_record IN ConditionRecord, plan_id IN NUMBER, element_id IN
2483 NUMBER, value IN VARCHAR2, spec_id IN NUMBER)
2484 RETURN BOOLEAN IS
2485
2486 low_value VARCHAR2(150);
2487 high_value VARCHAR2(150);
2488 datatype NUMBER;
2489
2490 BEGIN
2491
2492 IF condition_record.low_value_other IS NULL THEN
2493 -- BUG 3303285
2494 -- ksoh Mon Jan 5 12:55:13 PST 2004
2495 -- replaced get_spec_limits, which does not retrieve the
2496 -- correct low high value as it always retrieve reasonable limits
2497 --
2498 -- qa_plan_element_api.get_spec_limits(spec_id, element_id,
2499 -- low_value, high_value);
2500 qa_plan_element_api.get_low_high_values(plan_id, spec_id, element_id,
2501 condition_record.low_value_lookup, condition_record.high_value_lookup,
2502 low_value, high_value);
2503
2504 ELSE
2505 low_value := condition_record.low_value_other;
2506 high_value := condition_record.high_value_other;
2507
2508 END IF;
2509
2510 datatype := qa_plan_element_api.get_element_datatype(element_id);
2511
2512 IF qltcompb.compare(value, condition_record.operator, low_value,
2513 high_value, datatype) THEN
2514 RETURN TRUE;
2515 ELSE
2516 RETURN FALSE;
2517 END IF;
2518
2519 END evaluate_trigger;
2520
2521 --
2522 -- Bug 2976810
2523 -- this function modified to a procedure to give back the value of a token
2524 -- as well as the datatype of this value.
2525 -- ilawler Tue May 27 13:34:49 2003
2526 --
2527 PROCEDURE get_token_value (p_token_name IN VARCHAR2,
2528 p_plan_char_action_id IN NUMBER,
2529 p_row_elements IN ElementsArray,
2530 x_token_value OUT NOCOPY VARCHAR2,
2531 x_token_datatype OUT NOCOPY NUMBER)
2532 IS
2533
2534 element_id NUMBER;
2535 token_value VARCHAR2(2150);
2536
2537 --move the lookup of the datatype into this cursor
2538 /* CURSOR c1 (token VARCHAR2, pca_id NUMBER) IS
2539 SELECT qpcao.char_id, qpcv.datatype
2540 FROM qa_plan_char_action_outputs qpcao, qa_plan_chars_v qpcv
2541 WHERE plan_char_action_id = pca_id AND
2542 token_name = token AND
2543 qpcao.char_id = qpcv.char_id; */
2544
2545 -- Bug 4958778. SQL Repository Fix SQL ID: 15008783
2546 CURSOR c1 (token VARCHAR2, pca_id NUMBER) IS
2547 SELECT
2548 qpcao.char_id,
2549 qpcv.datatype
2550 FROM qa_plan_char_action_outputs qpcao,
2551 qa_chars qpcv
2552 WHERE plan_char_action_id = pca_id
2553 AND token_name = token
2554 AND qpcao.char_id = qpcv.char_id;
2555
2556
2557 BEGIN
2558 -- rkaza. 05/07/2003. Bug 2946779. Converting token name to upper case
2559 -- before comparing with token variable
2560
2561 OPEN c1 (upper(p_token_name), p_plan_char_action_id);
2562 FETCH c1 INTO element_id, x_token_datatype;
2563 CLOSE c1;
2564
2565 x_token_value := p_row_elements(element_id).value;
2566
2567 END get_token_value;
2568
2569
2570 --
2571 -- Bug 2976810
2572 -- this function performs the sql text/formula associated with an
2573 -- 'Assign a value to a collection element' plan action. This function has been
2574 -- modified to use dbms_sql and bind variables to remove our dependence on
2575 -- an exemption. This function was also expanded to do some of the data formatting
2576 -- done by import(qltdactb.do_assignment).
2577 -- ilawler Tue May 27 13:34:49 2003
2578 --
2579 FUNCTION get_assigned_value (plan_char_action_id IN NUMBER,
2580 message IN VARCHAR2,
2581 row_elements IN ElementsArray)
2582 RETURN VARCHAR2
2583 IS
2584 assigned_value VARCHAR2(150);
2585
2586 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2587 -- Increased the column width of final_stmt from 500 to 2500.
2588 -- In 'Assign a value' action if we populate a comment datatype element
2589 -- directly with a value(without tokens) which is approximately 2000
2590 -- characters then this variable would not be able to hold the value.
2591 final_stmt VARCHAR2(2500);
2592
2593 len NUMBER;
2594 i NUMBER;
2595 k NUMBER := 1;
2596 ignore NUMBER;
2597 assigned_datatype NUMBER;
2598 assigned_precision NUMBER;
2599 curr_char VARCHAR2(30);
2600 token_name VARCHAR2(30);
2601 token_datatype NUMBER;
2602 bind_var_name VARCHAR2(100);
2603
2604 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2605 -- Increased the column width of token_value from 150 to 2000.
2606 -- If the value of token_value which is going to be copied to
2607 -- the target element is more than 150 characters then ORA-06502
2608 -- would be raised. To prevent that column width has been increased.
2609 token_value VARCHAR2(2000) DEFAULT NULL;
2610
2611 TYPE tokenValTab IS TABLE OF token_value%TYPE INDEX BY BINARY_INTEGER;
2612 token_vals tokenValTab;
2613
2614 assignment_type VARCHAR2(1);
2615
2616 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2617 -- Increased the column width of return_value_char from 1500 to
2618 -- 2000 for the same reason mentioned above for token_value.
2619 return_value_char VARCHAR2(2000);
2620
2621 return_value_num NUMBER;
2622 return_value_date DATE;
2623
2624 c1 NUMBER;
2625
2626 --besides fetching the assign type, get the datatype of the assign target
2627 /* CURSOR c2 (pca_id NUMBER) IS
2628 SELECT qpca.assign_type, qpcv.datatype, qpcv.decimal_precision
2629 FROM qa_plan_char_actions qpca, qa_plan_chars_v qpcv
2630 WHERE qpca.plan_char_action_id = pca_id AND
2631 qpca.assigned_char_id = qpcv.char_id; */
2632
2633 -- Bug 4958778. SQL Repository Fix SQL ID: 15008799
2634 -- There is also an existing bug in the original query,
2635 -- namely qpc.plan_id is not used as a condition.
2636 -- It is hereby fixed.
2637 -- bso Fri Feb 3 11:31:47 PST 2006
2638 CURSOR c2 (pca_id NUMBER) IS
2639 SELECT
2640 qpca.assign_type,
2641 qc.datatype,
2642 nvl(qpc.decimal_precision, qc.decimal_precision) decimal_precision
2643 FROM qa_chars qc,
2644 qa_plan_chars qpc,
2645 qa_plan_char_actions qpca,
2646 qa_plan_char_action_triggers qpcat
2647 WHERE
2648 qpca.plan_char_action_trigger_id = qpcat.plan_char_action_trigger_id
2649 AND qpca.plan_char_action_id = pca_id
2650 AND qpc.plan_id = qpcat.plan_id
2651 AND qpc.char_id = qpca.assigned_char_id
2652 AND qc.char_id = qpca.assigned_char_id;
2653
2654 BEGIN
2655
2656 i := 1;
2657 len := NVL(length (message), 0);
2658
2659 WHILE i <= len LOOP
2660 curr_char := substr(message, i, 1);
2661 IF curr_char <> '&' THEN
2662 final_stmt := final_stmt || curr_char;
2663 i := i + 1;
2664
2665 ELSE -- we're at an ampersand
2666
2667 i := i + 1; -- skip over ampersand
2668 token_name := '';
2669 curr_char := substr(message, i, 1);
2670
2671 WHILE (curr_char between '0' and '9')
2672 OR (curr_char between 'A' and 'Z')
2673 OR (curr_char between 'a' and 'z')
2674 LOOP
2675 token_name := token_name || curr_char;
2676 i := i + 1;
2677 curr_char := substr(message, i, 1);
2678 END LOOP;
2679
2680 -- at this point the token name is formed so get the value and datatype
2681 get_token_value (p_token_name => token_name,
2682 p_plan_char_action_id => plan_char_action_id,
2683 p_row_elements => row_elements,
2684 x_token_value => token_value,
2685 x_token_datatype => token_datatype);
2686
2687 --based on this datatype we add different text to our sql statement
2688 bind_var_name := ':' || k;
2689
2690 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2691 -- Included comment datatype(y_datatype=4) in the following IF loop
2692 -- Before this fix if we try to assign a value to a comment
2693 -- datatype element the action would fire but the value would not
2694 -- be copied to the target element (or) would error out in SSQR.
2695 if token_datatype in (1,4) then
2696 bind_var_name := bind_var_name;
2697 elsif token_datatype = 2 then
2698 bind_var_name:= 'nvl(qltdate.canon_to_number(' || bind_var_name || '), 0)';
2699 elsif token_datatype = 3 then
2700 bind_var_name := 'qltdate.any_to_date(' || bind_var_name || ')';
2701
2702 -- Bug 3179845. Timezone Project. rponnusa Thu Oct 30 00:47:31 PST 2003
2703 elsif token_datatype = 6 then -- datetime
2704 bind_var_name := 'qltdate.any_to_datetime(' || bind_var_name || ')';
2705 end if;
2706
2707 --add the token value to the token value array and append the bind variable
2708 --reference string to the statement
2709 token_vals(k) := token_value;
2710 final_stmt := final_stmt || bind_var_name ;
2711
2712 k := k + 1;
2713
2714 END IF;
2715
2716 END LOOP;
2717
2718 -- anagarwa Wed Jan 7 16:27:11 PST 2004
2719 -- Bug 3340004 actions are not being fired in SSQR
2720 -- The reason was that template plans have an extra new line character
2721 -- at the end of the sql for date opened. This extran new line is now
2722 -- being removed along with comma and forward slash
2723 -- IMPORTANT NOTE: DO NOT REMOVE THE NEW LINE CHARACTER IN FOLLOWING
2724 -- RTRIM EXPRESSION. IT HAS BEEN PUT TO FIX BUG 3340004
2725 final_stmt := rtrim(final_stmt, ' ;/
2726 ');
2727
2728 OPEN c2 (plan_char_action_id);
2729 FETCH c2 INTO assignment_type, assigned_datatype, assigned_precision;
2730 CLOSE c2;
2731
2732 -- if it's a formula type assign, wrap the action text in a select from dual
2733 IF assignment_type = 'F' THEN
2734 final_stmt := 'SELECT ' || final_stmt || ' FROM DUAL';
2735 END IF;
2736
2737 c1 := dbms_sql.open_cursor;
2738 dbms_sql.parse(c1, final_stmt, dbms_sql.native);
2739
2740 --go through the token_vals array and do the bindings
2741 k := token_vals.FIRST;
2742 WHILE (k IS NOT NULL) LOOP
2743 dbms_sql.bind_variable(c1, ':' || to_char(k), token_vals(k));
2744 k := token_vals.NEXT(k);
2745 END LOOP;
2746
2747 --
2748 -- Bug 4635316
2749 -- Binding the value of the Global parameter Org Id set in the function
2750 -- validate_row
2751 -- ntungare Mon Oct 10 01:14:31 PDT 2005
2752 --
2753 IF INSTR(final_stmt, QA_SS_CONST.bindvar_param_org_id, 1)<> 0 THEN
2754 dbms_sql.bind_variable(c1, QA_SS_CONST.bindvar_param_org_id, g_org_id);
2755 END IF;
2756
2757 --
2758 -- Bug 4635316
2759 -- Binding the value of the Global parameter User Id set in the function
2760 -- validate_row
2761 -- ntungare Mon Oct 10 01:14:31 PDT 2005
2762 --
2763 IF INSTR(final_stmt, QA_SS_CONST.bindvar_param_user_id, 1) <> 0 THEN
2764 dbms_sql.bind_variable(c1, QA_SS_CONST.bindvar_param_user_id, g_user_id);
2765 END IF;
2766
2767 --set up the type of the output we expect
2768
2769 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2770 -- Included comment datatype(assigned_datatype=4) in the following IF loop.
2771 -- Before this fix if we try to assign a value to a comment datatype
2772 -- element the action would fire but the value would not be copied to
2773 -- the target element in SSQR. Also increased the width of
2774 -- return_value_char from 1500 to 2000.
2775 IF assigned_datatype in (1,4) THEN
2776 dbms_sql.define_column(c1, 1, return_value_char, 2000);
2777 ELSIF assigned_datatype = 2 THEN
2778 dbms_sql.define_column(c1, 1, return_value_num);
2779 ELSIF assigned_datatype = 3 THEN
2780 -- Bug 3179845. Timezone Project. rponnusa Thu Oct 30 00:47:31 PST 2003
2781
2782 -- Assign value from Date element to Date element fail if
2783 -- col defined as char type. To fix this get the value as Date type
2784 -- dbms_sql.define_column(c1, 1, return_value_char, 1500);
2785 dbms_sql.define_column(c1, 1, return_value_date);
2786
2787 ELSIF assigned_datatype = 6 THEN -- datetime
2788 dbms_sql.define_column(c1, 1, return_value_date);
2789 END IF;
2790
2791 --execute the cursor and fetch the value into return_value_char
2792 ignore := dbms_sql.execute(c1);
2793
2794 IF dbms_sql.fetch_rows(c1)>0 THEN
2795
2796 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2797 -- Included assigned_datatype=4 also in the following loop for the
2798 -- same reason mentioned few lines above.
2799 IF assigned_datatype in (1,4) THEN
2800
2801 dbms_sql.column_value(c1, 1, return_value_char);
2802
2803 ELSIF assigned_datatype = 2 THEN
2804
2805 dbms_sql.column_value(c1, 1, return_value_num);
2806 return_value_char := to_char(round(return_value_num, nvl(assigned_precision, 0)));
2807
2808 ELSIF assigned_datatype = 3 THEN
2809 -- Bug 3179845. Timezone Project. rponnusa Thu Oct 30 00:47:31 PST 2003
2810
2811 -- dbms_sql.column_value(c1, 1, return_value_char);
2812 dbms_sql.column_value(c1, 1, return_value_date);
2813 return_value_char := qltdate.date_to_canon(return_value_date);
2814
2815 ELSIF assigned_datatype = 6 THEN -- datetime
2816
2817 dbms_sql.column_value(c1, 1, return_value_date);
2818 return_value_char := qltdate.date_to_canon_dt(return_value_date);
2819
2820 END IF;
2821 ELSE
2822 --if we didn't get anything then just give back a null
2823 return_value_char := NULL;
2824 END IF;
2825 dbms_sql.close_cursor(c1);
2826
2827 RETURN return_value_char;
2828
2829 EXCEPTION WHEN OTHERS THEN
2830 RAISE;
2831
2832 END get_assigned_value;
2833
2834 -- Bug 3397484. Added row_record to the function required to make a call to
2835 -- validate_keyflex and validate_normalized.
2836 -- saugupta Wed, 28 Jan 2004 07:59:39 -0800 PDT
2837
2838 -- 12.1 QWB Usability Improvements
2839 -- Added 2 new parameters p_ssqr_operation
2840 -- and ordered_array
2841 FUNCTION perform_immediate_actions (action_id IN NUMBER,
2842 element_id IN NUMBER,
2843 row_record IN RowRecord,
2844 message IN VARCHAR2,
2845 plan_char_action_id IN NUMBER,
2846 row_elements IN OUT NOCOPY ElementsArray,
2847 return_results_array IN OUT NOCOPY ResultRecordArray,
2848 message_array IN OUT NOCOPY MessageArray,
2849 result_holder IN OUT NOCOPY ResultRecord,
2850 ordered_array IN OUT NOCOPY ElementInfoArray,
2851 p_ssqr_operation IN NUMBER DEFAULT NULL
2852 )
2853 RETURN NUMBER IS
2854
2855 target_element NUMBER;
2856
2857 -- Bug 5150287. SHKALYAN 02-Mar-2006.
2858 -- Increased the column width of assigned_value from 150 to 2000.
2859 -- If the value of variable assigned_value which is going to store the
2860 -- value to be assigned to the target element is more than 150 characters
2861 -- then ORA-06502 would be raised. To prevent that column width has been
2862 -- increased to the maximum supported by Quality.
2863 assigned_value VARCHAR2(2000);
2864 message_index NUMBER;
2865 assigned_element VARCHAR2(30);
2866
2867 -- Bug 3397484. Added the variables to make a call to validate_keyflex and validate_normalized.
2868 -- This is required for backward assignment.
2869 -- saugupta Wed, 28 Jan 2004 08:01:37 -0800 PDT.
2870 back_result_holder ResultRecord;
2871 error_code NUMBER;
2872
2873 -- Bug 3679762. Added the following cursor to validte whether the target column
2874 -- for "assign a value" action exist/enabled in collection plan.
2875 -- srhariha.Wed Jun 16 06:54:06 PDT 2004
2876
2877 cursor validate_target(p_plan_id number,p_char_id number) is
2878 select 1
2879 from qa_plan_chars
2880 where plan_id = p_plan_id
2881 and char_id = p_char_id
2882 and enabled_flag = 1;
2883
2884 l_target_exists number;
2885
2886 --
2887 -- Bug 4635316.
2888 -- The variable altered_message would hold the formula or the sql text used in the
2889 -- assign a value action and passed to this fucntion in the variable message. If
2890 -- the sql text has reference to parameter fields like org_id and user_id then
2891 -- they are replaced with the BindVars and the resultant string is stored in this
2892 -- variable altered_message
2893 -- ntungare Sun Oct 16 20:36:17 PDT 2005
2894 --
2895 altered_message VARCHAR2(2000);
2896
2897 BEGIN
2898
2899 -- We need to take both the row_elements and return_results_array
2900 -- because we allow both forward and backward assignment in the assign
2901 -- a value action.
2902 --
2903 -- If it is forward assignment then row_elements will have the
2904 -- correct value and will be validated as the validation proceeds.
2905 --
2906 -- If it is backward assignment then we must modify the
2907 -- return_results_array to indicate the change, but this value
2908 -- will NOT be validated.
2909
2910 IF (action_id = qa_ss_const.display_message_action) THEN
2911
2912 message_index := message_array.count;
2913 message_array(message_index).element_id := element_id;
2914 message_array(message_index).action_type :=
2915 qa_ss_const.display_message_action;
2916 message_array(message_index).message := message;
2917
2918 ELSIF (action_id = qa_ss_const.reject_input_action) THEN
2919
2920 message_index := message_array.count;
2921 message_array(message_index).element_id := element_id;
2922 message_array(message_index).action_type :=
2923 qa_ss_const.reject_input_action;
2924 message_array(message_index).message := message;
2925
2926 -- This can be used in self service, and if the action
2927 -- is fired, this will be consiidered an error. Therrfore,
2928 -- we should return reject_an_entry_error.
2929
2930 RETURN reject_an_entry_error;
2931
2932 ELSIF (action_id = qa_ss_const.assign_value_action) THEN
2933
2934 target_element := qa_plan_element_api.get_target_element(
2935 plan_char_action_id);
2936
2937
2938 -- Bug 3679762. Check whether target column of assign a value action
2939 -- exist/enabled in collection plan.If it doesnot exist/enabled return
2940 -- the corresponding error code.
2941 -- srhariha. Wed Jun 16 06:54:06 PDT 2004
2942
2943 l_target_exists:= -1;
2944
2945 open validate_target(row_record.plan_id,target_element);
2946 fetch validate_target into l_target_exists;
2947
2948 if (validate_target%NOTFOUND) then
2949 close validate_target;
2950 return missing_assign_column;
2951 end if;
2952
2953 close validate_target;
2954
2955 --
2956 -- Bug 4635316.
2957 -- The variable message would hold the formula or the sql text used in the
2958 -- assign a value action. If the sql text has reference to parameter fields
2959 -- like org_id and user_id then replace those references with the Bind Vars
2960 -- The replaced message string is stored in a variable altered_message, which
2961 -- is then used in the subsequent calls.
2962 -- ntungare Sun Oct 16 20:36:17 PDT 2005
2963 --
2964 altered_message := QLTTRAFB.REPLACE_TOKEN(X_STRING => message,
2965 X_OLD_TOKEN => QA_SS_CONST.global_param_org_id ,
2966 X_NEW_TOKEN => QA_SS_CONST.bindvar_param_org_id);
2967
2968 altered_message := QLTTRAFB.REPLACE_TOKEN(X_STRING => altered_message,
2969 X_OLD_TOKEN => QA_SS_CONST.global_param_user_id,
2970 X_NEW_TOKEN => QA_SS_CONST.bindvar_param_user_id);
2971
2972 assigned_value := get_assigned_value(plan_char_action_id, altered_message,
2973 row_elements);
2974
2975 -- Bug 3397484. Check for the item type keyflex or normalized and then call appropriate
2976 -- validate functions to get the ids.
2977 -- saugupta Wed, 28 Jan 2004 08:02:59 -0800 PDT
2978 IF qa_plan_element_api.keyflex(target_element) THEN
2979 error_code := validate_keyflex(row_elements, row_record,
2980 target_element, assigned_value, back_result_holder);
2981
2982 return_results_array(target_element).id := back_result_holder.id;
2983 row_elements(target_element).id := back_result_holder.id;
2984
2985 --12.1 QWB Usability Improvements
2986 -- Setting the validation flag to action not fired
2987 -- so that the actions initiated from value not entered trigger
2988 -- on one of the collection elements are cascaded
2989 --
2990 IF (p_ssqr_operation IS NOT NULL) THEN
2991 ordered_array(target_element).validation_flag := invalid_element;
2992 END IF;
2993
2994 ELSIF qa_plan_element_api.normalized(target_element) THEN
2995 error_code := validate_normalized(row_elements, row_record,
2996 target_element, assigned_value, back_result_holder);
2997
2998 return_results_array(target_element).id := back_result_holder.id;
2999 row_elements(target_element).id := back_result_holder.id;
3000
3001 --12.1 QWB Usability Improvements
3002 -- Setting the validation flag to action not fired
3003 -- so that the actions initiated from value not entered trigger
3004 -- on one of the collection elements are cascaded
3005 --
3006 IF (p_ssqr_operation IS NOT NULL) THEN
3007 ordered_array(target_element).validation_flag := invalid_element;
3008 END IF;
3009
3010 END IF;
3011
3012 return_results_array(target_element).canonical_value := assigned_value;
3013 row_elements(target_element).value := assigned_value;
3014
3015 --12.1 QWB Usability Improvements
3016 -- Setting the validation flag to action not fired
3017 -- so that the actions initiated from value not entered trigger
3018 -- on one of the collection elements are cascaded
3019 --
3020 IF (p_ssqr_operation IS NOT NULL) THEN
3021 ordered_array(target_element).validation_flag := invalid_element;
3022 END IF;
3023
3024 --ilawler - bug #3340004 - Mon Feb 16 18:40:12 2004
3025 --this covers the case of actions that do self-assignment.
3026 --update the result_holder element also to keep validate_row's logic
3027 --from overwriting the return_results_array with a blank value.
3028 IF (element_id = target_element) THEN
3029 result_holder.canonical_value := assigned_value;
3030 END IF;
3031
3032 message_index := message_array.count;
3033 message_array(message_index).element_id := element_id;
3034 message_array(message_index).action_type := qa_ss_const.assign_value_action;
3035 assigned_element := qa_chars_api.prompt(target_element);
3036
3037 -- bug 3178307. rkaza. 10/07/2003. Timezone Support.
3038 If qa_chars_api.datatype(target_element) = qa_ss_const.datetime_datatype then
3039 assigned_value :=
3040 fnd_date.date_to_displayDT(fnd_date.canonical_to_date(assigned_value), calendar_aware=> FND_DATE.calendar_aware_alt);
3041 End if;
3042
3043 -- anagarwa Wed Jan 28 16:39:34 PST 2004
3044 -- Bug bug 3404863 Commenting the hardcoded message
3045 -- Modify the action messages to Source: Target = Value
3046 -- Also we now escape @ chars in value string.
3047
3048 --ilawler - bug #3340004 - Mon Feb 16 18:53:29 2004
3049 --According to bso, the message should always be <target> = <value>
3050 /*
3051 message_array(message_index).message := assigned_value || ' Has Been Assigned To: ' || assigned_element || ' As Per The Assign A Value Action Of ' || trigger_element;
3052 IF (action_id <> qa_ss_const.assign_value_action) THEN
3053 startText := trigger_element || ': ' ;
3054 END IF;
3055
3056 message_array(message_index).message := startText || assigned_element || ' = ' || replace(assigned_value,'@','@@');
3057 */
3058 message_array(message_index).message := assigned_element || ' = ' || replace(assigned_value,'@','@@');
3059
3060 END IF;
3061
3062 RETURN ok;
3063
3064 END perform_immediate_actions;
3065
3066 -- 12.1 QWB Usability Improvements
3067 -- Added 2 new parameters p_ssqr_operation
3068 -- and ordered_array
3069 --
3070 FUNCTION fire_immediate_actions (plan_id IN NUMBER,
3071 spec_id IN NUMBER,
3072 element_id IN NUMBER,
3073 row_elements IN OUT NOCOPY ElementsArray,
3074 return_results_array IN OUT NOCOPY ResultRecordArray,
3075 message_array IN OUT NOCOPY MessageArray,
3076 result_holder IN OUT NOCOPY ResultRecord,
3077 ordered_array IN OUT NOCOPY ElementInfoArray,
3078 p_ssqr_operation IN NUMBER DEFAULT NULL
3079 )
3080 RETURN NUMBER IS
3081
3082 sequence_limit NUMBER;
3083 sequence_number NUMBER;
3084 trigger_id NUMBER;
3085 action_id NUMBER;
3086 plan_char_action_id NUMBER;
3087 message VARCHAR2(2000);
3088 target_element NUMBER;
3089 error_code NUMBER default ok;
3090 value VARCHAR2(2000);
3091 condition_record ConditionRecord;
3092 val_len number;
3093 datatype number;
3094
3095 --
3096 -- Bug 5003885
3097 -- Added the column plan_char_action_trigger_id to the order by
3098 -- clause so that all the actions under the trigger sequences
3099 -- on the same level are picked up in order
3100 -- ntungare Sun Feb 12 06:23:11 PST 2006
3101 --
3102
3103 CURSOR rule_cursor (p_id NUMBER, c_id NUMBER) IS
3104 SELECT plan_char_action_trigger_id, trigger_sequence, operator,
3105 low_value_other, high_value_other,
3106 low_value_lookup, high_value_lookup
3107 FROM qa_plan_char_action_triggers
3108 WHERE plan_id = p_id
3109 AND char_id = c_id
3110 ORDER BY trigger_sequence, plan_char_action_trigger_id;
3111
3112 CURSOR action_cursor (rule_id NUMBER) IS
3113 SELECT plan_char_action_id, action_id, message
3114 FROM qa_plan_char_actions
3115 WHERE plan_char_action_trigger_id = rule_id;
3116
3117 -- Bug 3397484. Added to make a call to changed function perform_immediate_actions
3118 -- see below the data in RowRecord.
3119 -- saugupta Wed, 28 Jan 2004 08:04:20 -0800 PDT
3120 row_record RowRecord;
3121
3122 BEGIN
3123
3124 -- Bug 3397484. setting values of row_record for passing to perform_immediate_actions.
3125 -- saugupta Wed, 28 Jan 2004 08:05:21 -0800 PDT
3126 row_record.plan_id := plan_id;
3127 row_record.spec_id := spec_id;
3128 row_record.org_id := NULL;
3129 row_record.user_id := NULL;
3130
3131
3132 value := row_elements(element_id).value;
3133
3134 -- rkaza. bug 3248836. 11/11/2003. tz bug.
3135 datatype := qa_chars_api.datatype(element_id);
3136
3137 IF (datatype = qa_ss_const.datetime_datatype) THEN
3138 val_len := length(value);
3139 if substr(value, val_len - 1, 2) = '.0' then
3140 -- Sometimes when coming from Self service, a '.0'
3141 -- is appended to the value
3142 -- because of an implicit conversion from date to string.
3143 -- So chop it off.
3144 value := substr(value, 1, val_len-2);
3145 end if;
3146 end if;
3147
3148 sequence_limit := 9999;
3149
3150 OPEN rule_cursor (plan_id, element_id);
3151
3152 LOOP
3153
3154 FETCH rule_cursor INTO trigger_id, sequence_number,
3155 condition_record.operator,
3156 condition_record.low_value_other,
3157 condition_record.high_value_other,
3158 condition_record.low_value_lookup,
3159 condition_record.high_value_lookup;
3160
3161 EXIT WHEN rule_cursor%NOTFOUND;
3162
3163 IF sequence_number > sequence_limit THEN
3164 EXIT;
3165
3166 ELSE
3167
3168 -- BUG 3303285
3169 -- ksoh Mon Dec 29 13:33:02 PST 2003
3170 -- pass in plan_id so that
3171 -- it calls get_low_high_values and performs uom conversion
3172 IF evaluate_trigger(condition_record, plan_id, element_id, value, spec_id)
3173 THEN
3174 OPEN action_cursor (trigger_id);
3175 LOOP
3176
3177 FETCH action_cursor INTO plan_char_action_id, action_id,
3178 message;
3179
3180 EXIT WHEN action_cursor%NOTFOUND;
3181
3182 error_code := perform_immediate_actions(
3183 action_id,
3184 element_id,
3185 row_record,
3186 message,
3187 plan_char_action_id,
3188 row_elements,
3189 return_results_array,
3190 message_array,
3191 result_holder,
3192 ordered_array,
3193 p_ssqr_operation);
3194
3195 IF error_code = reject_an_entry_error THEN
3196 RETURN reject_an_entry_error;
3197 END IF;
3198
3199 END LOOP;
3200
3201 CLOSE action_cursor;
3202 sequence_limit := sequence_number;
3203 END IF;
3204
3205 END IF;
3206
3207 END LOOP;
3208
3209 CLOSE rule_cursor;
3210
3211 RETURN error_code;
3212
3213 EXCEPTION WHEN OTHERS THEN
3214 RETURN immediate_action_error;
3215
3216 END fire_immediate_actions;
3217
3218 -- Bug 4519558. OA Framework integration project. UT bug fix.
3219 -- Transaction type element was erroring out for WIP transactions.
3220 -- "Transaction Type" will be treated as one of a kind.
3221 -- srhariha.Tue Aug 2 00:43:07 PDT 2005.
3222
3223 FUNCTION validate_transaction_type(value IN VARCHAR2,
3224 p_org_id IN NUMBER,
3225 p_user_id IN NUMBER)
3226 RETURN NUMBER IS
3227
3228
3229 BEGIN
3230
3231 IF (value IS NULL) THEN
3232 RETURN ok;
3233 END IF;
3234
3235 IF qa_plan_element_api.validate_transaction_type(g_transaction_number,p_org_id,
3236 p_user_id, value) THEN
3237 RETURN ok;
3238 ELSE
3239 RETURN value_not_in_sql_error;
3240 END IF;
3241
3242 EXCEPTION
3243 WHEN OTHERS THEN
3244 RETURN value_not_in_sql_error;
3245
3246 END validate_transaction_type;
3247
3248 --
3249 -- Bug 6749653
3250 -- 12.1 QWB Usability Enhancements
3251 -- New function to peform the Reject and Input
3252 -- online validation
3253 -- nutngare
3254 --
3255 FUNCTION validate_reject_an_input(plan_id IN NUMBER,
3256 element_id IN NUMBER,
3257 value IN VARCHAR2)
3258 RETURN NUMBER AS
3259 CURSOR rule_cursor (p_id NUMBER, c_id NUMBER) IS
3260 SELECT pcat.plan_char_action_trigger_id, pcat.trigger_sequence, pcat.operator,
3261 pcat.low_value_other, pcat.high_value_other,
3262 pcat.low_value_lookup, pcat.high_value_lookup,
3263 pca.plan_char_action_id, pca.action_id, pca.message
3264 FROM qa_plan_char_action_triggers pcat, qa_plan_char_actions pca
3265 WHERE plan_id = p_id
3266 AND char_id = c_id
3267 AND pca.plan_char_action_trigger_id = pcat.plan_char_action_trigger_id
3268 AND pca.action_id = 2
3269 ORDER BY trigger_sequence, plan_char_action_trigger_id;
3270
3271 sequence_number NUMBER;
3272 trigger_id NUMBER;
3273 condition_record ConditionRecord;
3274 action_id NUMBER;
3275 plan_char_action_id NUMBER;
3276 message VARCHAR2(2000);
3277 sequence_limit NUMBER;
3278 BEGIN
3279 sequence_limit := 9999;
3280
3281 OPEN rule_cursor (plan_id, element_id);
3282
3283 LOOP
3284 FETCH rule_cursor INTO trigger_id, sequence_number,
3285 condition_record.operator,
3286 condition_record.low_value_other,
3287 condition_record.high_value_other,
3288 condition_record.low_value_lookup,
3289 condition_record.high_value_lookup,
3290 plan_char_action_id,
3291 action_id,
3292 message;
3293
3294 EXIT WHEN rule_cursor%NOTFOUND;
3295
3296 IF sequence_number > sequence_limit THEN
3297 EXIT;
3298 ELSE
3299 IF evaluate_trigger(condition_record, plan_id, element_id, value, 0)
3300 THEN
3301 RETURN reject_an_entry_error;
3302 END IF;
3303 END IF;
3304 END LOOP;
3305
3306 RETURN ok;
3307 END validate_reject_an_input;
3308
3309 --
3310 -- 12.1 QWB Usablity Improvements Project
3311 -- Modified the API to add a new parameter
3312 -- org_id. This is needed to online validations
3313 -- through PPR
3314 -- ntungare
3315 --
3316 FUNCTION validate_element (
3317 row_elements IN OUT NOCOPY ElementsArray,
3318 row_record IN RowRecord,
3319 element_id IN NUMBER,
3320 org_id IN NUMBER,
3321 result_holder IN OUT NOCOPY ResultRecord)
3322 RETURN ErrorArray IS
3323
3324 error_list ErrorArray;
3325 error_code NUMBER;
3326
3327 -- Bug 2427337 variable size increased for long comment datatype
3328 -- rponnusa Tue Jun 25 06:15:48 PDT 2002
3329 element_value VARCHAR2(2100);
3330
3331 BEGIN
3332 -- 12.1 QWB Usablitity Improvements
3333 g_org_id := org_id;
3334 element_value := row_elements(element_id).value;
3335
3336 result_holder.actual_datatype :=
3337 qa_plan_element_api.get_actual_datatype (element_id);
3338
3339 error_list(1).error_code := validate_enabled (row_record.plan_id,
3340 element_id);
3341
3342
3343 -- We should not perform any mandatory check if the row is
3344 -- coming from background transaction. For Background
3345 -- transaction null is allowed even for mandatory elements.
3346
3347 error_list(2).error_code := ok;
3348
3349 --
3350 -- Bug 2617638.
3351 -- Added the id_derived conjunct. No need to call validate
3352 -- mandatory if ID is passed. It is always NOT NULL in this
3353 -- case, and will not fail validate_mandatory.
3354 -- bso Tue Oct 8 18:44:45 PDT 2002
3355 --
3356 IF NOT flag_is_set(row_elements(element_id).validation_flag,
3357 background_element)
3358 AND NOT flag_is_set(row_elements(element_id).validation_flag,
3359 valid_element)
3360 AND NOT flag_is_set(row_elements(element_id).validation_flag,
3361 id_derived)
3362 THEN
3363
3364 error_list(2).error_code := validate_mandatory (row_record, element_id,
3365 element_value);
3366 END IF;
3367
3368 error_list(3).error_code := validate_datatype(row_record.plan_id,
3369 element_id, element_value, row_elements, result_holder);
3370
3371 IF qa_plan_element_api.keyflex(element_id) THEN
3372 error_list(4).error_code := validate_keyflex(row_elements, row_record,
3373 element_id, element_value, result_holder);
3374
3375 ELSIF qa_plan_element_api.normalized(element_id) THEN
3376 error_list(4).error_code := validate_normalized(row_elements,
3377 row_record, element_id, element_value, result_holder);
3378
3379 ELSIF qa_plan_element_api.primitive(element_id) THEN
3380 error_list(4).error_code := validate_primitive(row_record.plan_id,
3381 row_elements, row_record, element_id, element_value);
3382
3383 -- Bug 4519558. OA Framework integration project. UT bug fix.
3384 -- Transaction type element was erroring out for WIP transactions.
3385 -- "Transaction Type" will be treated as one of a kind.
3386 -- srhariha.Tue Aug 2 00:43:07 PDT 2005.
3387
3388 ELSIF element_id = qa_ss_const.transaction_type THEN
3389 error_list(4).error_code := validate_transaction_type(element_value,
3390 g_org_id,row_record.user_id);
3391
3392 ELSIF qa_plan_element_api.values_exist(row_record.plan_id, element_id) THEN
3393 error_list(4).error_code := validate_values(row_record.plan_id,
3394 element_id, element_value);
3395
3396 -- Bug 7716875.pdube Mon Apr 13 03:25:19 PDT 2009
3397 -- Added condition based on validation_flag which will
3398 -- be true for elements with sql validation for forms and qwb
3399 -- ELSIF qa_plan_element_api.sql_validation_exists(element_id) THEN
3400 -- Bug 10431717
3401 -- Added the NVL condition to ensure that if the flag is NULL then
3402 -- the data is considered as invalid and the validation is performed
3403 --
3404 ELSIF qa_plan_element_api.sql_validation_exists(element_id)
3405 AND NOT NVL(row_elements(element_id).validation_flag,'invalid') = 'valid' THEN
3406 error_list(4).error_code := validate_sql(element_id, element_value,
3407 row_record.org_id, row_record.user_id);
3408
3409 ELSE -- For
3410 error_list(4).error_code := ok;
3411 END if;
3412
3413 error_list(5).error_code := validate_spec_limits(row_record, element_id,
3414 result_holder.canonical_value);
3415
3416 --
3417 -- Bug 6749653
3418 -- 12.1 QWB Usability Improvements
3419 -- Checking for Reject an input actions
3420 -- ntungare
3421 --
3422 error_list(6).error_code := validate_reject_an_input(row_record.plan_id, element_id,
3423 result_holder.canonical_value);
3424
3425 RETURN error_list;
3426
3427 END validate_element;
3428
3429 --
3430 -- 12.1 QWB Usability Improvements
3431 -- Added a new parameter p_ssqr_operation that would determine
3432 -- if the online actions should be fired or not. This is neede
3433 -- since in QWB the online actions would be fired though PPR
3434 -- and so need not be fired again at the time of validation
3435 --
3436 FUNCTION validate_row (
3437 plan_id IN NUMBER,
3438 spec_id IN NUMBER,
3439 org_id IN NUMBER,
3440 user_id IN NUMBER,
3441 transaction_number IN NUMBER,
3442 transaction_id IN NUMBER,
3443 return_results_array OUT NOCOPY ResultRecordArray,
3444 message_array OUT NOCOPY MessageArray,
3445 row_elements IN OUT NOCOPY ElementsArray,
3446 p_ssqr_operation IN NUMBER DEFAULT NULL)
3447 RETURN ErrorArray IS
3448
3449 row_record RowRecord;
3450 result_holder ResultRecord;
3451 error_list ErrorArray;
3452 row_error_list ErrorArray;
3453 ordered_array ElementInfoArray;
3454
3455 BEGIN
3456
3457 row_record.plan_id := plan_id;
3458 row_record.spec_id := spec_id;
3459 row_record.org_id := org_id;
3460 row_record.user_id := user_id;
3461
3462 g_org_id := org_id;
3463 g_transaction_number := transaction_number;
3464 g_transaction_id := transaction_id;
3465
3466 --
3467 -- Bug 4635316.
3468 -- Store the value of user_id in the global variable g_user_id so
3469 -- that it can be used in Function fire_immediate_actions.
3470 -- ntungare Wed Sep 28 06:03:11 PDT 2005.
3471 --
3472 g_user_id := user_id;
3473
3474 -- Bug 4558205. Lot/Serial Validation.
3475 -- Call init procedure for global variables.
3476 -- srhariha. Tue Sep 27 05:07:58 PDT 2005.
3477
3478 init_globals;
3479
3480 -- End Bug 4558205.
3481
3482 -- Bug 3397484 ksoh Tue Jan 27 13:54:38 PST 2004
3483 -- pass plan_id for ordering ordered_array by prompt_sequence
3484 ordered_array := populate_elements (row_elements, plan_id);
3485 determine_navigation_order (ordered_array);
3486
3487 FOR i IN 0..row_elements.count-1 LOOP
3488
3489 IF NOT nvl(ordered_array(i).treated, FALSE) THEN
3490
3491 -- We must not validate an element if it is already valid,
3492 -- for example context elements for transactions.
3493 --
3494 -- However, if this element is a keyflex or normalized
3495 -- then we have to go through validation to determine
3496 -- the corresponding id to put in qa_results.
3497
3498 -- Bug 3381173. Validation should be performed even if the
3499 -- element is a primitive one. Hence adding an OR condn below.
3500 -- This is also necessary because of the dependent elements.
3501 -- Eg.. the subinventory (primitive) validation should be
3502 -- performed before the locator (keyflex) validation.
3503 -- kabalakr Tue Jan 27 02:18:59 PST 2004.
3504
3505 -- Bug 4343758. OA Framework Integration project.
3506 -- If the primitive values are validated then it
3507 -- must be copied to package globals, which will
3508 -- be used to validate other dependent elements.
3509 -- Also removed primitive call from below OR list.
3510 -- srhariha. Thu May 26 02:38:04 PDT 2005.
3511
3512 IF flag_is_set(ordered_array(i).validation_flag,valid_element) AND
3513 qa_plan_element_api.primitive(ordered_array(i).id) THEN
3514
3515 copy_primitive_to_global(p_element_id => ordered_array(i).id,
3516 p_value => row_elements(ordered_array(i).id).value);
3517 END IF;
3518
3519
3520 IF NOT flag_is_set(ordered_array(i).validation_flag,
3521 valid_element)
3522 OR qa_plan_element_api.keyflex(ordered_array(i).id)
3523 OR qa_plan_element_api.normalized(ordered_array(i).id) THEN
3524 -- OR qa_plan_element_api.primitive(ordered_array(i).id) THEN
3525
3526 -- 12.1 QWB Usabiltiy Improvements
3527 -- Passing the Org Id parameter
3528 error_list := validate_element( row_elements, row_record,
3529 ordered_array(i).id, org_id, result_holder);
3530 ordered_array(i).treated := TRUE;
3531
3532 --
3533 -- Bug 5331420. Some primitive elements are not firing
3534 -- actions because they do not match the complex IF
3535 -- condition. Hence, moved the following block to
3536 -- out of the IF THEN ELSE. Actions should be fired
3537 -- for all elements as long as it doesn't have the
3538 -- action_fired flag set which will be checked here.
3539 -- bso Thu Jun 15 16:19:59 PDT 2006
3540 --
3541 -- IF no_errors(error_list) THEN
3542 --
3543 -- -- if the calling application took care of immediate
3544 -- -- actions then we must not do any processing
3545 -- -- related to firing immediate actions
3546 --
3547 -- --ilawler - bug #3340004 - Mon Feb 16 18:38:25 2004
3548 -- --add result_holder as a param to allow self-assignment
3549 -- IF NOT (flag_is_set(ordered_array(i).validation_flag,
3550 -- action_fired)) THEN
3551 -- error_list(6).error_code := fire_immediate_actions (
3552 -- plan_id, spec_id, ordered_array(i).id,
3553 -- row_elements, return_results_array, message_array,
3554 -- result_holder);
3555 -- END IF;
3556 --
3557 -- END IF;
3558
3559 ELSE
3560
3561 -- If validation is not performed on elements there are couple of
3562 -- things we must compute.
3563 -- validate_datatype is called because we want to compute
3564 -- the canonical values for each element.
3565 -- Another thing is to cmopute the actual datatype
3566
3567 error_list(1).error_code := validate_datatype(row_record.plan_id,
3568 ordered_array(i).id , row_elements(ordered_array(i).id).value,
3569 row_elements, result_holder);
3570
3571 result_holder.actual_datatype := qa_plan_element_api.get_actual_datatype(
3572 ordered_array(i).id);
3573
3574 END IF;
3575
3576 --
3577 -- Bug 5331420. See above. Moved code here.
3578 --
3579 IF no_errors(error_list) THEN
3580
3581 -- if the calling application took care of immediate
3582 -- actions then we must not do any processing
3583 -- related to firing immediate actions
3584
3585 --ilawler - bug #3340004 - Mon Feb 16 18:38:25 2004
3586 --add result_holder as a param to allow self-assignment
3587 -- 12.1 QWB Usabiltity Improvements
3588 -- Online actions are to fired only in case the
3589 -- Validate_row method has not been called from the QWB
3590 -- Application.
3591 --
3592 IF (NOT (flag_is_set(ordered_array(i).validation_flag,
3593 action_fired)) AND
3594 p_ssqr_operation IS NULL) THEN
3595 error_list(6).error_code := fire_immediate_actions (
3596 plan_id, spec_id, ordered_array(i).id,
3597 row_elements, return_results_array, message_array,
3598 result_holder, ordered_array);
3599 END IF;
3600 END IF;
3601
3602 END IF;
3603
3604 append_errors( ordered_array(i).id, error_list, row_error_list);
3605 return_results_array(ordered_array(i).id).element_id :=
3606 ordered_array(i).id;
3607 return_results_array(ordered_array(i).id).id := result_holder.id;
3608 return_results_array(ordered_array(i).id).actual_datatype :=
3609 result_holder.actual_datatype;
3610 return_results_array(ordered_array(i).id).canonical_value :=
3611 result_holder.canonical_value;
3612 result_holder.id := NULL;
3613 error_list.delete;
3614
3615 END LOOP;
3616
3617 RETURN row_error_list;
3618
3619 EXCEPTION WHEN OTHERS THEN
3620 -- dbms_output.put_line('An Error Occurred, Unable To Continue');
3621 -- dbms_output.put_line(to_char(sqlcode)||': '||sqlerrm);
3622 RAISE;
3623
3624 END validate_row;
3625
3626
3627 PROCEDURE init_message_map IS
3628 --
3629 -- A mapping between our internal error message and its AOL message name.
3630 --
3631 BEGIN
3632 g_message_map(not_enabled_error) := 'QA_API_NOT_ENABLED';
3633 g_message_map(no_values_error) := 'QA_API_NO_VALUES';
3634 g_message_map(mandatory_error) := 'QA_API_MANDATORY';
3635 g_message_map(not_revision_controlled_error) := 'QA_API_REVISION_CONTROLLED';
3636 g_message_map(mandatory_revision_error) := 'QA_API_MANDATORY_REVISION';
3637 g_message_map(no_values_error) := 'QA_API_NO_VALUES';
3638 g_message_map(keyflex_error) := 'QA_API_KEYFLEX';
3639 g_message_map(id_not_found_error) := 'QA_API_ID_NOT_FOUND';
3640 g_message_map(spec_limit_error) := 'QA_API_SPEC_LIMIT';
3641 g_message_map(immediate_action_error) := 'QA_API_IMMEDIATE_ACTION';
3642 g_message_map(lower_limit_error) := 'QA_API_LOWER_LIMIT';
3643 g_message_map(upper_limit_error) := 'QA_API_UPPER_LIMIT';
3644 g_message_map(value_not_in_sql_error) := 'QA_API_VALUE_NOT_IN_SQL';
3645 g_message_map(sql_validation_error) := 'QA_API_SQL_VALIDATION';
3646 g_message_map(date_conversion_error) := 'QA_API_INVALID_DATE';
3647 g_message_map(data_type_error) := 'QA_API_DATA_TYPE';
3648 g_message_map(number_conversion_error) := 'QA_API_INVALID_NUMBER';
3649 g_message_map(no_data_found_error) := 'QA_API_NO_DATA_FOUND';
3650 g_message_map(not_locator_controlled_error) := 'QA_API_NOT_LOCATOR_CONTROLLED';
3651 g_message_map(item_keyflex_error) := 'QA_API_ITEM_KEYFLEX';
3652 g_message_map(comp_item_keyflex_error) := 'QA_API_COMP_ITEM_KEYFLEX';
3653 g_message_map(locator_keyflex_error) := 'QA_API_LOCATOR_KEYFLEX';
3654 g_message_map(comp_locator_keyflex_error) := 'QA_API_COMP_LOCATOR_KEYFLEX';
3655 g_message_map(invalid_number_error) := 'QA_API_INVALID_NUMBER';
3656 g_message_map(invalid_date_error) := 'QA_API_INVALID_DATE';
3657 g_message_map(spec_error) := 'QA_API_SPEC';
3658 g_message_map(ok) := 'QA_API_NO_ERROR';
3659 g_message_map(unknown_error) := 'QA_API_UNKNOWN';
3660 g_message_map(reject_an_entry_error) := 'QA_API_REJECT_AN_ENTRY';
3661
3662 -- Added the following messages for Bill_Reference,Routing_Reference,To_locator
3663 -- Key FlexFields. Bug 2686970.suramasw Wed Nov 27 05:12:52 PST 2002.
3664
3665 g_message_map(bill_reference_keyflex_error) := 'QA_API_BILL_REFERENCE_KEYFLEX';
3666 g_message_map(rtg_reference_keyflex_error) := 'QA_API_RTG_REFERENCE_KEYFLEX';
3667 g_message_map(to_locator_keyflex_error) := 'QA_API_TO_LOCATOR_KEYFLEX';
3668
3669 -- Bug 3679762.Initialising the message array for the "missing assign a value target
3670 -- column" error message.
3671 -- srhariha.Wed Jun 16 06:54:06 PDT 2004
3672
3673 g_message_map(missing_assign_column) := 'QA_MISSING_ASSIGN_COLUMN';
3674
3675 END init_message_map;
3676
3677
3678 FUNCTION get_error_message(error_code IN NUMBER) RETURN VARCHAR2 IS
3679 --
3680 -- Return an error message in the user's language given an error code.
3681 --
3682 BEGIN
3683 RETURN fnd_message.get_string('QA', g_message_map(error_code));
3684 END get_error_message;
3685
3686 FUNCTION validate_comment (value IN VARCHAR2, result_holder IN OUT NOCOPY ResultRecord)
3687 RETURN NUMBER IS
3688 --
3689 -- This function is for long comment datatype. Long comment can hold
3690 -- max of 2000 characters. Introduced for bug 2427337
3691 -- rponnusa Tue Jun 25 06:15:48 PDT 2002
3692
3693 BEGIN
3694 result_holder.canonical_value := substr(value, 1, 2000);
3695
3696 RETURN ok;
3697 END validate_comment;
3698
3699
3700 --
3701 -- R12 Project MOAC 4637896
3702 --
3703 -- Moved several procedures from qltssreb to this package
3704 -- to centralize all validation routines. These are
3705 --
3706 -- parse_id
3707 -- parse_value
3708 -- result_to_array
3709 -- id_to_array
3710 -- set_validation_flag
3711 -- set_validation_flag_txn
3712 --
3713 -- bso Sat Oct 1 16:15:58 PDT 2005
3714 --
3715
3716
3717 FUNCTION parse_id(p_result IN VARCHAR2, n IN INTEGER,
3718 p IN INTEGER, q IN INTEGER) RETURN NUMBER IS
3719 BEGIN
3720 RETURN to_number(substr(p_result, p, q-p));
3721 END parse_id;
3722
3723
3724 FUNCTION parse_value(p_result IN VARCHAR2, n IN INTEGER,
3725 p IN OUT NOCOPY INTEGER) RETURN VARCHAR2 IS
3726
3727 -- anagarwal Fri Nov 14 14:11:12 PST 2003
3728 -- bug 3256981
3729 -- the problem was happening due to value being initialized to have
3730 -- character1 length. For comment1 to comment5, it can be as long as
3731 -- 2000 chars. So we need to have long enough field.
3732 l_value qa_results.comment1%TYPE;
3733 c VARCHAR2(10);
3734 separator CONSTANT VARCHAR2(1) := '@';
3735
3736 BEGIN
3737 --
3738 -- Loop until a single @ is found or p_result is exhausted.
3739 --
3740 p := p + 1; -- add 1 before substr to skip '='
3741 WHILE p <= n LOOP
3742 c := substr(p_result, p, 1);
3743 p := p + 1;
3744 IF (c = separator) THEN
3745 IF substr(p_result, p, 1) <> separator THEN
3746 --
3747 -- take a peak at the next character, if not another @,
3748 -- we have reached the end. Otherwise, skip this @
3749 --
3750 RETURN l_value;
3751 ELSE
3752 p := p + 1;
3753 END IF;
3754 END IF;
3755 l_value := l_value || c;
3756 END LOOP;
3757
3758 RETURN l_value;
3759 END parse_value;
3760
3761
3762 FUNCTION result_to_array(p_result IN VARCHAR2)
3763 RETURN qa_validation_api.ElementsArray IS
3764
3765 elements qa_validation_api.ElementsArray;
3766 n INTEGER;
3767 p INTEGER; -- starting string position
3768 q INTEGER; -- ending string position
3769 x_char_id NUMBER;
3770 --anagarwal Fri Nov 14 14:11:12 PST 2003
3771 -- bug 3256981
3772 -- the problem was happening due t value being initialized to have
3773 -- character1 length. For comment1 to comment5, it can be as long as
3774 -- 2000 chars. So we need to have long enough field.
3775 x_value qa_results.comment1%TYPE;
3776
3777 BEGIN
3778 n := length(p_result);
3779 p := 1;
3780 WHILE p < n LOOP
3781 q := instr(p_result, '=', p);
3782 --
3783 -- found the first = sign. To the left, must be char_id
3784 --
3785 x_char_id := parse_id(p_result, n, p, q);
3786 -- Bug 7716875.pdube Mon Apr 13 03:25:19 PDT 2009
3787 -- Added this statement to set the validation flag back to
3788 -- valid if the element has a validation string.
3789 -- this api is called from forms and qwb where the
3790 -- value of the element will be validated at UI.
3791 --
3792 -- Bug 10431717
3793 -- Reverted this fix since we would be validating the entered data
3794 -- again since the LOV values are not validated on cancel
3795 --
3796 --IF qa_validation_api.set_validation_flag_sql_valid(x_char_id) THEN
3797 -- elements(x_char_id).validation_flag := 'valid';
3798 --END IF;
3799 --
3800 -- To the right, must be the value
3801 --
3802 x_value := parse_value(p_result, n, q);
3803 elements(x_char_id).value := x_value;
3804 p := q;
3805 END LOOP;
3806
3807 RETURN elements;
3808 END result_to_array;
3809
3810
3811 -- I am over riding this function for the ability to pass ids.
3812 -- This will resolve the validation issue for work order like elements
3813 -- names are not unique only ids are.
3814 --
3815 -- ORASHID 15-August-2001
3816 --
3817 -- R12 Project MOAC 4637896
3818 -- Renaming this function from result_to_array to
3819 -- id_to_array to fix a confusion. Uptake the
3820 -- parameter and local variable naming to Apps
3821 -- standard convention.
3822 -- bso Tue Sep 27 17:51:10 PDT 2005
3823 --
3824 FUNCTION id_to_array(
3825 p_result IN VARCHAR2,
3826 x_elements IN OUT NOCOPY qa_validation_api.ElementsArray)
3827 RETURN qa_validation_api.ElementsArray IS
3828
3829 n INTEGER;
3830 p INTEGER; -- starting string position
3831 q INTEGER; -- ending string position
3832 l_char_id NUMBER;
3833 l_value qa_results.character1%TYPE;
3834
3835 BEGIN
3836
3837 IF p_result IS NULL THEN
3838 RETURN x_elements;
3839 END IF;
3840
3841 n := length(p_result);
3842 p := 1;
3843 WHILE p < n LOOP
3844 q := instr(p_result, '=', p);
3845 --
3846 -- found the first = sign. To the left, must be char_id
3847 --
3848 l_char_id := parse_id(p_result, n, p, q);
3849 --
3850 -- To the right, must be the value
3851 --
3852 l_value := parse_value(p_result, n, q);
3853 x_elements(l_char_id).id := l_value;
3854 -- Bug 7716875.pdube Mon Apr 13 03:25:19 PDT 2009
3855 -- Added this statement to set the validation flag back to
3856 -- valid if the element has a validation string.
3857 -- this api is called from forms and qwb where the
3858 -- value of the element will be validated at UI.
3859 IF qa_validation_api.set_validation_flag_sql_valid(l_char_id) THEN
3860 x_elements(l_char_id).validation_flag := 'valid';
3861 END IF;
3862
3863 x_elements(l_char_id).validation_flag :=
3864 nvl(x_elements(l_char_id).validation_flag, 'invalid') ||
3865 qa_validation_api.id_derived;
3866 p := q;
3867 END LOOP;
3868
3869 RETURN x_elements;
3870 END id_to_array;
3871
3872
3873 PROCEDURE set_validation_flag(
3874 x_elements IN OUT NOCOPY qa_validation_api.ElementsArray) IS
3875 i INTEGER;
3876 BEGIN
3877 i := x_elements.FIRST;
3878 WHILE i <= x_elements.LAST LOOP
3879 x_elements(i).validation_flag :=
3880 x_elements(i).validation_flag ||
3881 qa_validation_api.action_fired;
3882
3883 IF i = qa_ss_const.transaction_type THEN
3884 x_elements(i).validation_flag :=
3885 x_elements(i).validation_flag ||
3886 qa_validation_api.valid_element;
3887 END IF;
3888
3889 i := x_elements.NEXT(i);
3890 END LOOP;
3891 END set_validation_flag;
3892
3893
3894 --
3895 -- The previous version of the following routine was
3896 -- incorrectly implemented. It is hereby redone.
3897 -- See version 120.4 of qltssreb.plb or before for the
3898 -- old version. Also renamed from set_validation_flag
3899 -- to set_validation_flag_txn.
3900 --
3901 -- This procedure is needed to set context elements to
3902 -- have background_element flag so that it will pass
3903 -- mandatory-ness validation even though the value is
3904 -- null. Background values will be posted later in a
3905 -- separate step, hence mandatory check is not needed.
3906 --
3907 -- Caller chould supply p_plan_id + p_transaction_number
3908 -- or simply p_plan_transaction_id which will be more
3909 -- accurate.
3910 --
3911 -- bso Sat Oct 1 15:05:35 PDT 2005
3912 --
3913
3914 PROCEDURE set_validation_flag_txn(
3915 x_elements IN OUT NOCOPY qa_validation_api.ElementsArray,
3916 p_plan_id NUMBER,
3917 p_transaction_number NUMBER,
3918 p_plan_transaction_id NUMBER) IS
3919
3920 CURSOR context IS
3921 SELECT collection_trigger_id
3922 FROM qa_txn_collection_triggers
3923 WHERE transaction_number = p_transaction_number AND
3924 enabled_flag = 1;
3925
3926 CURSOR bg1 IS
3927 SELECT 1
3928 FROM qa_plan_transactions
3929 WHERE plan_id = p_plan_id AND
3930 transaction_number = p_transaction_number AND
3931 enabled_flag = 1 AND
3932 background_collection_flag = 1;
3933
3934 CURSOR bg2 IS
3935 SELECT 1
3936 FROM qa_plan_transactions
3937 WHERE plan_transaction_id = p_plan_transaction_id AND
3938 enabled_flag = 1 AND
3939 background_collection_flag = 1;
3940
3941 i BINARY_INTEGER;
3942 l_background NUMBER;
3943
3944 BEGIN
3945 --
3946 -- Step 1. Set all context elements to be valid.
3947 --
3948 FOR c IN context LOOP
3949 IF x_elements.EXISTS(c.collection_trigger_id) THEN
3950 x_elements(c.collection_trigger_id).validation_flag :=
3951 x_elements(c.collection_trigger_id).validation_flag ||
3952 qa_validation_api.valid_element;
3953 END IF;
3954 END LOOP;
3955
3956 --
3957 -- Step 2. Set additional valid elements.
3958 --
3959 IF x_elements.EXISTS(qa_ss_const.lot_number) THEN
3960 x_elements(qa_ss_const.lot_number).validation_flag :=
3961 x_elements(qa_ss_const.lot_number).validation_flag ||
3962 qa_validation_api.valid_element;
3963 END IF;
3964
3965 IF x_elements.EXISTS(qa_ss_const.serial_number) THEN
3966 x_elements(qa_ss_const.serial_number).validation_flag :=
3967 x_elements(qa_ss_const.serial_number).validation_flag ||
3968 qa_validation_api.valid_element;
3969 END IF;
3970
3971 IF x_elements.EXISTS(qa_ss_const.transaction_type) THEN
3972 x_elements(qa_ss_const.transaction_type).validation_flag :=
3973 x_elements(qa_ss_const.transaction_type).validation_flag ||
3974 qa_validation_api.valid_element;
3975 END IF;
3976
3977 --
3978 -- Step 3. If it is a background plan, set all elements
3979 -- to have the background_element flag. Currently this
3980 -- is only an approximation because we should have used
3981 -- plan_transaction_id to identify the particular txn.
3982 -- But this was not available in the current architecture,
3983 -- so using a conservative approach to look for all
3984 -- plan_id + transaction_number combination and if any
3985 -- is background, then assume it is a background plan.
3986 -- The correct fix is that the caller must supply the
3987 -- p_plan_transaction_id param, then this function will
3988 -- work correctly.
3989 --
3990
3991 IF p_plan_transaction_id IS NULL THEN
3992 OPEN bg1;
3993 FETCH bg1 INTO l_background;
3994 CLOSE bg1;
3995 ELSE
3996 OPEN bg2;
3997 FETCH bg2 INTO l_background;
3998 CLOSE bg2;
3999 END IF;
4000
4001 IF l_background IS NOT NULL THEN
4002 i := x_elements.FIRST;
4003 WHILE i <= x_elements.LAST LOOP
4004 x_elements(i).validation_flag :=
4005 x_elements(i).validation_flag ||
4006 qa_validation_api.background_element;
4007 i := x_elements.NEXT(i);
4008 END LOOP;
4009 END IF;
4010
4011 END set_validation_flag_txn;
4012
4013 -- End R12 Project MOAC 4637896
4014
4015 -- Bug 7716875.pdube Mon Apr 13 03:25:19 PDT 2009
4016 -- This function will return true if there is already a sql validation
4017 -- attached to the element.We consider that the UI will validate the
4018 -- elements correctly and they need not to be validated again, hence
4019 -- using this value, shall set the validation_flag to valid
4020 FUNCTION set_validation_flag_sql_valid(
4021 p_char_id IN NUMBER) RETURN BOOLEAN IS
4022
4023 CURSOR sql_valid(x_char_id NUMBER) IS
4024 SELECT 1
4025 FROM qa_chars
4026 WHERE char_id = x_char_id AND
4027 sql_validation_string is not null;
4028 p_sql_validation_flag BOOLEAN;
4029 temp NUMBER := 0; -- Flag number 1 for true and 0 for false
4030
4031 BEGIN
4032 IF p_char_id is not null then
4033 OPEN sql_valid(p_char_id);
4034 FETCH sql_valid INTO temp;
4035 CLOSE sql_valid;
4036 END IF;
4037
4038 IF temp = 1 THEN
4039 RETURN TRUE;
4040 ELSE
4041 RETURN FALSE;
4042 END IF;
4043 END set_validation_flag_sql_valid;
4044
4045 -- 12.1 QWB Usability Improvements
4046 -- Procedure to De-reference the values for the HC elements
4047 -- that depended on Non Quality tables for their values
4048 PROCEDURE build_deref_string(p_plan_id IN NUMBER,
4049 p_collection_id IN NUMBER,
4050 p_occurrence IN NUMBER,
4051 p_charid_string OUT NOCOPY VARCHAR2,
4052 p_values_string OUT NOCOPY VARCHAR2)
4053 AS
4054
4055 Type hardcoded_char_tab_typ IS TABLE OF NUMBER INDEX BY binary_integer;
4056 hardcoded_char_tab hardcoded_char_tab_typ;
4057
4058 cols_str VARCHAR2(32767);
4059 char_ids_str VARCHAR2(32767);
4060
4061 plan_name VARCHAR2(100);
4062
4063 result_str VARCHAR2(32767);
4064
4065 char_name VARCHAR(100);
4066
4067 BEGIN
4068 --Get the list of HardCoded elements in the
4069 --Collection plan
4070 SELECT qpc.char_id bulk collect
4071 INTO hardcoded_char_tab
4072 FROM qa_plan_chars qpc,
4073 qa_chars qc
4074 WHERE qpc.plan_id = p_plan_id
4075 AND qpc.char_id = qc.char_id
4076 AND qc.hardcoded_column IS NOT NULL
4077 AND fk_table_name IS NOT NULL;
4078
4079 --loop through the list of HardCoded elements
4080 FOR cntr IN 1 .. hardcoded_char_tab.COUNT
4081 LOOP
4082 -- Select the column name for the Hardcoded
4083 -- element based on the element id
4084 Select upper(translate(name,' ''*{}','_____')) into char_name
4085 from qa_chars
4086 where char_id = hardcoded_char_tab(cntr);
4087
4088 --
4089 -- bug 7559568
4090 -- Added replace function to double encode the delimiter ,
4091 -- if the data contains the delimiter.
4092 -- ntungare
4093 --
4094 cols_str := cols_str || 'REPLACE(NVL(to_char('||char_name||'), ''NULL''),'','','',,'') ||'',''||';
4095 char_ids_str := char_ids_str ||
4096 qa_ak_mapping_api.get_vo_attribute_name(hardcoded_char_tab(cntr), p_plan_id)||
4097 ',';
4098 END LOOP;
4099
4100 if hardcoded_char_tab.count <> 0 then
4101 -- Get the plan name to form the Dynamic Plan View Name
4102 SELECT deref_view_name
4103 INTO plan_name
4104 FROM qa_plans
4105 WHERE plan_id = p_plan_id;
4106
4107 cols_str := RTRIM(cols_str, '||'',''||');
4108 char_ids_str := RTRIM(char_ids_str, ',');
4109
4110 begin
4111 EXECUTE IMMEDIATE 'Select ''' || char_ids_str || ''',' || cols_str || ' from ' || plan_name ||
4112 ' where collection_id = :collection_id and
4113 occurrence = :occurrence'
4114 INTO p_charid_string,
4115 p_values_string
4116 USING p_collection_id,
4117 p_occurrence;
4118 EXCEPTION when others
4119 then null;
4120 end;
4121 end if;
4122
4123 END build_deref_string;
4124
4125 -- 12.1 QWB Usability Improvements
4126 -- API to replace the Tokens in the error messages
4127 -- with the element prompts
4128 -- ntungare
4129 FUNCTION replace_message_tokens(p_error_message IN VARCHAR2)
4130 RETURN VARCHAR2 AS
4131
4132 token_count NUMBER;
4133 element_prompt VARCHAR2(200);
4134 replaced_message VARCHAR2(2000);
4135 token_postn NUMBER;
4136 BEGIN
4137 SELECT LENGTH(p_error_message) - LENGTH(REPLACE(p_error_message,'&',''))
4138 INTO token_count FROM DUAL;
4139
4140 IF token_count <> 0 THEN
4141 SELECT INSTR(p_error_message,':',1) INTO token_postn FROM DUAL;
4142
4143 SELECT SUBSTR(p_error_message,1,token_postn-1),
4144 SUBSTR(p_error_message,token_postn+1)
4145 INTO element_prompt, replaced_message
4146 FROM DUAL;
4147
4148 SELECT REPLACE(replaced_message,'&'||'CHAR_PROMPT', element_prompt)
4149 INTO replaced_message FROM DUAL;
4150 ELSE
4151 RETURN p_error_message;
4152 END IF;
4153
4154 RETURN replaced_message;
4155 END replace_message_tokens;
4156
4157 -- 12.1 QWB Usability Improvements
4158 -- Method to do the online validations
4159 -- This method would also make a call to the API
4160 -- process_dependent_elements to do the dependent
4161 -- elelemts processing
4162 --
4163 PROCEDURE perform_ssqr_validation (p_plan_id IN VARCHAR2,
4164 p_org_id IN VARCHAR2,
4165 p_spec_id IN VARCHAR2,
4166 p_user_id IN VARCHAR2 DEFAULT NULL,
4167 p_element_id IN VARCHAR2,
4168 p_input_value IN VARCHAR2,
4169 result_string IN VARCHAR2,
4170 id_string IN VARCHAR2,
4171 normalized_attr OUT NOCOPY VARCHAR2,
4172 normalized_id_val OUT NOCOPY VARCHAR2,
4173 message OUT NOCOPY VARCHAR2,
4174 dependent_elements OUT NOCOPY VARCHAR2,
4175 disable_enable_flag_list OUT NOCOPY VARCHAR2,
4176 disabled_dep_elem_vo_attr_lst OUT NOCOPY VARCHAR2)
4177 AS
4178 row_record QA_VALIDATION_API.RowRecord;
4179 result_record QA_VALIDATION_API.ResultRecord;
4180 row_elements QA_VALIDATION_API.ElementsArray;
4181 error_array QA_VALIDATION_API.ErrorArray;
4182 errors_list QA_VALIDATION_API.ErrorArray;
4183 cntr NUMBER;
4184 i NUMBER :=1 ;
4185 message_str VARCHAR2(4000);
4186
4187 l_id_string VARCHAR2(4000);
4188 -- Bug 9382356
4189 -- Modified the length to 32767 so that it can hold more than 1 Comments type elements.
4190 -- Also, added new variable l_input_value to truncate a string to 2000 characters as the
4191 -- limit in row_elements.value is 2000 characters.
4192 -- skolluku
4193 l_result_string VARCHAR2(32767);
4194 l_input_value VARCHAR2(2000);
4195
4196 dep_elements_list VARCHAR2(4000);
4197 dep_flag_list VARCHAR2(4000);
4198 dep_elements_status VARCHAR2(4000);
4199
4200 elements qa_validation_api.ElementsArray;
4201
4202 dependent_elements_arr QA_PARENT_CHILD_PKG.ChildPlanArray;
4203 dep_elements_status_arr QA_PARENT_CHILD_PKG.ChildPlanArray;
4204
4205 disabled_dep_elem_vo_attr VARCHAR2(4000);
4206 disabled_dep_elem_id_attr VARCHAR2(4000);
4207 BEGIN
4208 -- populate the record structures
4209 /*
4210 if result_string is not NULL THEN
4211 elements := qa_validation_api.result_to_array(result_string);
4212 elements := qa_validation_api.id_to_array(id_string, elements);
4213 end if;
4214 */
4215
4216 row_elements(p_element_id).id := p_element_id;
4217 /*
4218 If (elements.COUNT <> 0 AND elements(p_element_id).value IS NOT NULL) THEN
4219 row_elements(p_element_id).value := elements(p_element_id).value;
4220 ELSE
4221 row_elements(p_element_id).value := p_input_value;
4222 END If;
4223 */
4224 -- Bug 9382356. Truncate the value to 2000 and use it instead of p_input_value.
4225 l_input_value := substr(p_input_value,1,2000);
4226 --
4227 -- bug 12375005
4228 -- The input value has the @s doubly encoded. This is done
4229 -- while building the result string. While this is correct for the
4230 -- result string which again would be decoded when its converted
4231 -- into an array, it is not proper for the direct value. So replace
4232 -- the double encoded @s with a sigle one
4233 --
4234 -- row_elements(p_element_id).value := l_input_value;
4235 row_elements(p_element_id).value := replace(l_input_value, '@@','@');
4236
4237 -- Bug 9382356. Use l_input_value instead of p_input_value.
4238 l_result_string := result_string || '@' || p_element_id || '=' || l_input_value;
4239
4240 IF (qa_chars_api.datatype(p_element_id) IN (6,3) AND
4241 p_input_value is NOT NULL) THEN
4242 BEGIN
4243 row_elements(p_element_id).value := QLTDATE.any_to_canon(UPPER(p_input_value));
4244 EXCEPTION WHEN OTHERS THEN
4245 errors_list(1).error_code := invalid_date_error;
4246 errors_list(1).element_id := p_element_id;
4247
4248 QA_SS_RESULTS.get_error_messages(errors_list, p_plan_id,message_str);
4249 message := replace_message_tokens(message_str);
4250 RETURN;
4251 END;
4252 END IF;
4253
4254 row_record.plan_id := p_plan_id;
4255 row_record.org_id := p_org_id;
4256 row_record.spec_id := p_spec_id;
4257 row_record.user_id := p_user_id;
4258
4259 result_record.element_id := p_element_id;
4260
4261 -- call the validate_element api to perform
4262 -- the element validation
4263 error_array := QA_VALIDATION_API.validate_element(
4264 row_elements => row_elements,
4265 row_record => row_record,
4266 element_id => p_element_id,
4267 org_id => p_org_id,
4268 result_holder => result_record);
4269
4270 cntr := error_array.first;
4271
4272 -- build the error list array
4273 while cntr <= error_array.last
4274 loop
4275 if error_array(cntr).error_code <> QA_VALIDATION_API.OK THEN
4276 errors_list(i).element_id := p_element_id;
4277 errors_list(i).error_code := error_array(cntr).error_code;
4278 i := i+1;
4279 END If;
4280 cntr := error_array.next(cntr);
4281 end loop;
4282
4283 -- build the error message string
4284 -- bug 6980226
4285 -- If the element is a HC element then we need to clear
4286 -- the Id value and also disable the dependent elements
4287 -- This is done in the ELSE part
4288 -- ntungare
4289 --
4290 If (QA_VALIDATION_API.no_errors(error_array) = FALSE AND
4291 ((qa_plan_element_api.keyflex(p_element_id) OR
4292 qa_plan_element_api.normalized(p_element_id)) = FALSE)) THEN
4293
4294 QA_SS_RESULTS.get_error_messages(errors_list, p_plan_id, message_str);
4295 message := replace_message_tokens(message_str);
4296
4297 ELSE
4298 -- bug 6980226
4299 -- In case the element is a HC element then the error message
4300 -- needs to be captured if there is any. The dependent elements
4301 -- processing needs to be done in case of an invalid value as well
4302 -- ntungare
4303 --
4304 IF QA_VALIDATION_API.no_errors(error_array) = FALSE THEN
4305 QA_SS_RESULTS.get_error_messages(errors_list, p_plan_id, message_str);
4306 message := replace_message_tokens(message_str);
4307 END IF;
4308
4309 -- Set the normalized value for HC element
4310 IF ((qa_plan_element_api.keyflex(p_element_id) OR
4311 qa_plan_element_api.normalized(p_element_id)) = TRUE) THEN
4312 normalized_id_val := result_record.id;
4313 -- Set the normalized element VO attribute
4314 normalized_attr := qa_chars_api.hardcoded_column(p_element_id);
4315
4316 IF normalized_id_val is NOT NULL THEN
4317 l_id_string := id_string || '@'|| p_element_id || '=' || normalized_id_val;
4318 ELSE
4319 normalized_id_val := 'NULL';
4320 END IF;
4321
4322 l_id_string := LTRIM(l_id_string, '@');
4323 END IF;
4324
4325 -- Process dependent elements
4326 QA_PLAN_ELEMENT_API.process_dependent_elements(
4327 l_result_string,
4328 l_id_string,
4329 p_org_id,
4330 p_plan_id,
4331 p_element_id,
4332 dep_elements_list,
4333 dep_flag_list);
4334
4335 dependent_elements:= dep_elements_list;
4336 disable_enable_flag_list := dep_flag_list;
4337
4338 -- If any dependent element has been disabled then its
4339 -- value needs to be cleared.
4340 --
4341 IF (dependent_elements IS NOT NULL AND
4342 INSTR(disable_enable_flag_list,'D',1,1) <> 0) THEN
4343 -- If the above condition evaluates to true then it means
4344 -- that there are dependent elements that are disabled
4345
4346 -- Representing the enabled and disabled flags as 1 and 2
4347 -- respectively. This is needed since the parse_list API
4348 -- that converts the comma separated strings into arrays
4349 -- works only with number elements
4350 SELECT TRANSLATE(disable_enable_flag_list,'ED','12')
4351 INTO dep_elements_status
4352 FROM DUAL;
4353
4354 QA_PARENT_CHILD_PKG.parse_list(dependent_elements , dependent_elements_arr);
4355 QA_PARENT_CHILD_PKG.parse_list(dep_elements_status, dep_elements_status_arr);
4356
4357 -- Loop though the dependent elements
4358 FOR dep_elem_ctr in 1..dependent_elements_arr.COUNT
4359 LOOP
4360 -- Check if any of them is disabled
4361 IF (dep_elements_status_arr(dep_elem_ctr) = 2) THEN
4362
4363 disabled_dep_elem_vo_attr :=
4364 qa_ak_mapping_api.get_vo_attribute_name(dependent_elements_arr(dep_elem_ctr),
4365 p_plan_id);
4366
4367 IF (disabled_dep_elem_vo_attr IS NOT NULL) THEN
4368 disabled_dep_elem_vo_attr_lst := disabled_dep_elem_vo_attr_lst || ',' ||
4369 disabled_dep_elem_vo_attr;
4370 END IF;
4371
4372 -- Getting the HC id column
4373 disabled_dep_elem_id_attr :=
4374 qa_chars_api.hardcoded_column(dependent_elements_arr(dep_elem_ctr));
4375
4376 IF (disabled_dep_elem_id_attr IS NOT NULL AND
4377 disabled_dep_elem_id_attr <> disabled_dep_elem_vo_attr) THEN
4378 disabled_dep_elem_vo_attr_lst := disabled_dep_elem_vo_attr_lst || ',' ||
4379 disabled_dep_elem_id_attr;
4380 END IF;
4381
4382 disabled_dep_elem_id_attr := NULL;
4383 disabled_dep_elem_vo_attr := NULL;
4384 END IF;
4385 END LOOP;
4386
4387 IF (disabled_dep_elem_vo_attr_lst IS NOT NULL) THEN
4388 disabled_dep_elem_vo_attr_lst := LTRIM(disabled_dep_elem_vo_attr_lst, ',');
4389 END IF;
4390 END IF;
4391 END IF;
4392 END perform_ssqr_validation;
4393
4394
4395 -- 12.1 QWB Usability Improvements
4396 -- method to get the sql string for ResultExportVO
4397 FUNCTION get_export_vo_sql (p_plan_id in NUMBER) Return VARCHAR2 is
4398
4399 CURSOR plan_chars IS
4400 select upper(translate(qc.name,' ''*{}','_____')) name,
4401 qpc.char_id char_id,
4402 qc.datatype
4403 from qa_chars qc,
4404 qa_plan_chars qpc
4405 WHERE qc.char_id = qpc.char_id
4406 AND qpc.plan_id = p_plan_id
4407 ORDER BY qpc.prompt_sequence;
4408
4409 l_sql_stmt VARCHAR2(4000);
4410 l_view_sql VARCHAR2(32767);
4411
4412 l_view_name VARCHAR2(100) := NULL;
4413
4414 l_apps_schema VARCHAR(30) := 'APPS';
4415
4416 CURSOR get_exp_view(p_view_name IN VARCHAR2) IS
4417 SELECT view_name from all_views
4418 WHERE view_name = p_view_name
4419 AND owner = l_apps_schema;
4420
4421 --
4422 -- bug 12596623
4423 --
4424 l_dummy BOOLEAN;
4425 l_fnd CONSTANT VARCHAR2(3) := 'FND';
4426 l_status VARCHAR2(1);
4427 l_industry VARCHAR2(10);
4428 l_schema VARCHAR2(30);
4429
4430 BEGIN
4431 l_sql_stmt := 'select ''N'' as "HideShowStatus" , ';
4432 for rec in plan_chars
4433 loop
4434 l_sql_stmt := l_sql_stmt || rec.name ||', ';
4435 end loop;
4436
4437 l_sql_stmt := l_sql_stmt || 'CREATED_BY, COLLECTION_ID, LAST_UPDATE_DATE, PLAN_ID, OCCURRENCE FROM ';
4438
4439 OPEN get_exp_view('QA_'||p_plan_id||'_EXP_V');
4440 FETCH get_exp_view INTO l_view_name;
4441 CLOSE get_exp_view;
4442
4443 IF l_view_name IS NOT NULL THEN
4444 l_sql_stmt := l_sql_stmt || l_view_name ;
4445 ELSE
4446 --
4447 -- bug 12596623
4448 -- Use the exploded view SQL for export VO instead of using the plan view directly
4449 --
4450 l_view_sql := 'CREATE OR REPLACE FORCE VIEW QA_'||p_plan_id||'_EXP_V AS '||QLTVCREB.get_export_view_sql(p_plan_id);
4451
4452 l_dummy := fnd_installation.get_app_info(l_fnd, l_status, l_industry, l_schema);
4453
4454 ad_ddl.do_ddl(l_schema, 'QA', ad_ddl.create_view, l_view_sql, 'QA_'|| p_plan_id || '_EXP_V');
4455 l_sql_stmt := l_sql_stmt || 'QA_'||p_plan_id||'_EXP_V' ;
4456 END IF;
4457
4458 return l_sql_stmt;
4459
4460 END get_export_vo_sql;
4461
4462
4463
4464 -- 12.1 QWB Usability Improvements
4465 -- Procedure to fire the online actions
4466 -- on elements that have triggers defined
4467 -- for the value not entered conditition
4468 --
4469 FUNCTION processNotEnteredActions (p_plan_id IN NUMBER,
4470 p_spec_id IN NUMBER,
4471 p_ssqr_operation IN NUMBER DEFAULT NULL,
4472 p_row_elements IN OUT NOCOPY ElementsArray,
4473 p_return_results_array IN OUT NOCOPY ResultRecordArray,
4474 message_array OUT NOCOPY MessageArray)
4475 RETURN ErrorArray IS
4476 charctr NUMBER;
4477 ordered_array ElementInfoArray;
4478 ordered_array2 ElementInfoArray;
4479 error_list ErrorArray;
4480 result_holder ResultRecord;
4481
4482 row_error_list ErrorArray;
4483
4484 TYPE action_elements_tab_Typ IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
4485 action_elements_tab action_elements_tab_Typ ;
4486
4487 Cursor cur is
4488 SELECT distinct char_id
4489 FROM qa_plan_char_action_triggers qpt, qa_plan_char_actions qpa
4490 WHERE plan_id = p_plan_id AND
4491 qpt.plan_char_action_trigger_id = qpa.plan_char_action_trigger_id AND
4492 operator = 8;
4493 BEGIN
4494 ordered_array := populate_elements (p_row_elements, p_plan_id);
4495
4496 for c in cur
4497 loop
4498 action_elements_tab(c.char_id) := c.char_id;
4499 end loop;
4500
4501 For cntr in 0..ordered_array.COUNT-1
4502 Loop
4503 -- If the element is in the actions tab then it means
4504 -- the actions for it have not been fired since this tab
4505 -- is specific to elements having online actions defined on
4506 -- operator
4507 IF (action_elements_tab.EXISTS((ordered_array(cntr).id)) AND
4508 p_row_elements(ordered_array(cntr).id).id IS NULL AND
4509 p_row_elements(ordered_array(cntr).id).value IS NULL) THEN
4510
4511 ordered_array(cntr).validation_flag := invalid_element;
4512 ELSE
4513 ordered_array(cntr).validation_flag := action_fired;
4514 END If;
4515 ordered_array2(ordered_array(cntr).id) := ordered_array(cntr);
4516 End Loop;
4517
4518 charctr := p_row_elements.first;
4519
4520 while charctr <= p_row_elements.last
4521 loop
4522 result_holder.element_id := charctr;
4523
4524 -- In case the call is from the self-service application p_ssqr_operation=1
4525 -- then the results_record_arry needs to be populated. This is because in other
4526 -- cases the result array is populated by the validate_row api in qltrsiub
4527 -- which is not called for standalone ssqr operations
4528 --
4529 IF (p_ssqr_operation = 1) THEN
4530 -- Assinging the element Id
4531 p_return_results_array(ordered_array2(charctr).id).element_id :=
4532 ordered_array2(charctr).id;
4533
4534 -- Assinging the element Normalized Id
4535 p_return_results_array(ordered_array2(charctr).id).id :=
4536 p_row_elements(ordered_array2(charctr).id).id;
4537
4538 -- Assinging the Actual data Type
4539 p_return_results_array(ordered_array2(charctr).id).actual_datatype :=
4540 qa_plan_element_api.get_actual_datatype(ordered_array2(charctr).id);
4541
4542 -- Assinging the Deferenced value
4543 p_return_results_array(ordered_array2(charctr).id).canonical_value :=
4544 p_row_elements(ordered_array2(charctr).id).value;
4545
4546 END IF;
4547
4548
4549 if p_return_results_array(ordered_array2(charctr).id).id IS NOT NULL THEN
4550 -- Set the id value
4551 result_holder.id := p_return_results_array(ordered_array2(charctr).id).id;
4552 ELSE
4553 -- Set the canonical value
4554 result_holder.canonical_value := p_return_results_array(ordered_array2(charctr).id).canonical_value;
4555 END If;
4556
4557 result_holder.actual_datatype := qa_plan_element_api.get_actual_datatype(charctr);
4558
4559 IF (NOT (flag_is_set(ordered_array2(charctr).validation_flag,action_fired))) THEN
4560 error_list(6).error_code := fire_immediate_actions (
4561 p_plan_id,
4562 p_spec_id,
4563 ordered_array2(charctr).id,
4564 p_row_elements,
4565 p_return_results_array,
4566 message_array,
4567 result_holder,
4568 ordered_array2,
4569 p_ssqr_operation);
4570 END IF;
4571 append_errors( ordered_array2(charctr).id, error_list, row_error_list);
4572
4573 result_holder.id := NULL;
4574 error_list.delete;
4575
4576 charctr := p_row_elements.next(charctr);
4577 end loop;
4578 RETURN row_error_list;
4579 EXCEPTION WHEN OTHERS THEN
4580 raise;
4581 END processNotEnteredActions;
4582
4583 BEGIN
4584
4585 populate_dependency_matrix;
4586 init_message_map;
4587
4588 END qa_validation_api;
[Home] [Help]