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