1 PACKAGE BODY QA_PARENT_CHILD_PKG as
2 /* $Header: qapcb.pls 120.26.12010000.2 2008/09/26 13:43:11 pdube ship $ */
3
4 -- Bug 4343758
5 -- R12 OAF Txn Integration Project
6 -- Standard Global variable
7 -- shkalyan 05/07/2005.
8 g_pkg_name CONSTANT VARCHAR2(30) := 'QA_PARENT_CHILD_PKG';
9
10 --
11 -- Through out this package all the functions will return 'T' or 'F'
12 -- instead of 'TRUE' or 'FALSE'. The reason is, we may call this
13 -- serverside functions from Java or by other platforms.
14 --
15
16 FUNCTION aggregate_functions(p_sql_string IN VARCHAR2,
17 p_occurrence IN NUMBER,
18 p_child_plan_id IN NUMBER,
19 x_value OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
20 l_value NUMBER;
21
22 BEGIN
23 BEGIN
24 EXECUTE IMMEDIATE p_sql_string INTO l_value USING p_occurrence,p_child_plan_id;
25
26 -- Bug 2716973
27 -- Even though fix here is not required, but for consistency adding NVL function here.
28 -- rponnusa Sun Jan 12 23:59:07 PST 2003
29
30 x_value := NVL(l_value,0);
31 RETURN 'T';
32 EXCEPTION
33 WHEN OTHERS THEN
34 RETURN 'F';
35 END;
36
37 END aggregate_functions;
38
39 --
40 -- bug 5682448
41 -- added the Txn_header_id parameter
42 -- ntungare Wed Feb 21 07:28:43 PST 2007
43 --
44 FUNCTION aggregate_functions(p_sql_string IN VARCHAR2,
45 p_occurrence IN NUMBER,
46 p_child_plan_id IN NUMBER,
47 p_txn_header_id IN NUMBER,
48 x_value OUT NOCOPY NUMBER) RETURN VARCHAR2 IS
49 l_value NUMBER;
50
51 BEGIN
52 BEGIN
53 EXECUTE IMMEDIATE p_sql_string INTO l_value
54 USING p_occurrence,p_child_plan_id,p_txn_header_id;
55
56 -- Bug 2716973
57 -- Even though fix here is not required, but for consistency adding NVL function here.
58 -- rponnusa Sun Jan 12 23:59:07 PST 2003
59 x_value := NVL(l_value,0);
60 RETURN 'T';
61 EXCEPTION
62 WHEN OTHERS THEN
63 RETURN 'F';
64 END;
65
66 END aggregate_functions;
67
68
69 FUNCTION commit_allowed(p_plan_id NUMBER, p_collection_id NUMBER,
70 p_occurrence NUMBER ,p_child_plan_ids VARCHAR2) RETURN VARCHAR2 IS
71 l_incomplete_plan_ids VARCHAR2(10000);
72 BEGIN
73
74 -- Bug 5161719. SHKALYAN 13-Apr-2006
75 -- Modified to call the new overloaded commit_allowed to avoid
76 -- code duplication
77 RETURN commit_allowed
78 (
79 p_plan_id => p_plan_id,
80 p_collection_id => p_collection_id,
81 p_occurrence => p_occurrence,
82 p_child_plan_ids => p_child_plan_ids,
83 x_incomplete_plan_ids => l_incomplete_plan_ids
84 );
85 END commit_allowed;
86
87 -- Bug 5161719. SHKALYAN 13-Apr-2006
88 -- Created this overloaded commit_allowed function to pass back to the
89 -- caller a list of incomplete child plan ids in x_incomplete_plan_ids
90 -- This is because in OAF Txn integration project the message is expected
91 -- to have the incomplete child plan information.
92 -- Rest of the logic was moved from the old get_plan_name
93 -- to avoid code duplication.
94 FUNCTION commit_allowed(
95 p_plan_id NUMBER,
96 p_collection_id NUMBER,
97 p_occurrence NUMBER,
98 p_child_plan_ids VARCHAR2,
99 x_incomplete_plan_ids OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
100
101 ---
102 --- Simple function which returns True when results are collected for
103 --- immediate child plans else returns False.
104 ---
105 --- Important thing to remember here is that collection_id for parent
106 --- child plan are same in case of EQR. That is the reason I have included
107 --- one more where condtion in the cursor c2. See the following statement
108 --- 'AND child_collection_id = p_collection_id '
109 ---
110 --- The parameter 'p_child_plan_ids' will contain all the immediate child plan ids
111 --- separated by comma operator.
112
113
114 l_child_id_array ChildPlanArray;
115 l_child_plan_id NUMBER;
116 l_total_length NUMBER;
117 l_result VARCHAR2(1);
118 separator CONSTANT VARCHAR2(1) := ',';
119
120 -- Bug 2300962. Removed child_collection_id in the where clause
121 -- To check, child is entered or not child plan id is enough.
122
123 CURSOR c2(c_child_plan_id NUMBER) IS
124 SELECT 'T' FROM qa_pc_results_relationship
125 WHERE parent_plan_id = p_plan_id
126 AND parent_collection_id = p_collection_id
127 AND parent_occurrence = p_occurrence
128 AND child_plan_id = c_child_plan_id
129 AND rownum =1;
130
131 BEGIN
132 l_result := 'F';
133 l_total_length := LENGTH(p_child_plan_ids);
134
135 -- We need check for all the child_plan_ids one by one to see whether records are
136 -- entered for the child plan or not.
137
138 -- anagarwa Mon Apr 15 15:51:58 PDT 2002
139 -- Bug 2320896 was being caused due to error in logic.
140 -- This code is being replaced to avoid character to number conversion
141
142
143 parse_list(p_child_plan_ids, l_child_id_array);
144
145 FOR i IN 1..l_child_id_array.COUNT LOOP
146 l_child_plan_id := l_child_id_array(i);
147 OPEN c2(l_child_plan_id);
148 FETCH c2 INTO l_result;
149 IF (c2%NOTFOUND) THEN
150 l_result := 'F';
151
152 -- Bug 5161719. SHKALYAN 13-Apr-2006
153 -- In addition to setting result as false form the list of
154 -- incomplete plan ids
155 x_incomplete_plan_ids := x_incomplete_plan_ids || separator || l_child_plan_id;
156 CLOSE c2;
157 EXIT;
158 END IF;
159 CLOSE c2;
160
161 END LOOP;
162
163 -- Bug 5161719. SHKALYAN 13-Apr-2006
164 -- Remove the leading comma
165 IF ( x_incomplete_plan_ids IS NOT NULL ) THEN
166 x_incomplete_plan_ids := SUBSTR( x_incomplete_plan_ids, LENGTH(separator) + 1 );
167 END IF;
168
169 RETURN l_result;
170
171 END commit_allowed;
172
173
174 PROCEDURE enable_and_fire_actions(p_collection_id NUMBER) IS
175
176 ---
177 --- This procedure commits all the records corresponding to one single session with status = 2.
178 --- This is required since all the child records will be saved with qa_results.status =1
179 --- When the child record is enabled, status code will be changed to 2. The status of
180 --- child record is changed to 2 only when Parent record gets committed
181 ---
182
183 BEGIN
184 qa_results_api.enable_and_fire_action(p_collection_id);
185 END enable_and_fire_actions;
186
187 FUNCTION get_descendants(
188 p_plan_id NUMBER,
189 p_collection_id NUMBER,
190 p_occurrence NUMBER,
191 x_plan_ids OUT NOCOPY dbms_sql.number_table,
192 x_collection_ids OUT NOCOPY dbms_sql.number_table,
193 x_occurrences OUT NOCOPY dbms_sql.number_table) RETURN VARCHAR2 IS
194
195 ---
196 --- Given a parent record (plan/collection/occurrence), this procedure finds all the child and
197 --- grandchildren records (therefore, descendants) of the record. These are returned in the
198 --- three output PL/SQL tables. The parent record itself is not included in the output.
199 --- The query technical is called hierarchical subquery. The final where clause makes
200 --- sure the child record is actually enabled in the qa_results table.
201 ---
202
203 BEGIN
204
205 SELECT child_plan_id, child_collection_id, child_occurrence
206 BULK COLLECT INTO
207 x_plan_ids, x_collection_ids, x_occurrences
208 FROM qa_pc_results_relationship r
209 WHERE EXISTS (
210 SELECT 1
211 FROM qa_results qr
212 WHERE qr.plan_id = r.child_plan_id AND
213 qr.collection_id = r.child_collection_id AND
214 qr.occurrence = r.child_occurrence AND
215 (qr.status IS NULL or qr.status=2) )
216 START WITH parent_plan_id = p_plan_id AND
217 parent_collection_id = p_collection_id AND
218 parent_occurrence = p_occurrence
219 CONNECT BY PRIOR child_occurrence = parent_occurrence;
220
221 IF (SQL%FOUND) THEN
222 RETURN 'T';
223 ELSE
224 RETURN 'F';
225 END IF;
226
227 END get_descendants;
228
229 ---------------------------------------------------------------------------
230 FUNCTION evaluate_child_lov_criteria( p_plan_id IN NUMBER,
231 p_criteria_values IN VARCHAR2,
232 x_child_plan_ids OUT NOCOPY VARCHAR2)
233 RETURN VARCHAR2 IS
234 ---
235 --- This function finds all the matching child plan for the current plan.
236 --- First converts the values passed through p_criteria_values
237 --- into array. For each child plan we checking for the criteria values
238 --- by calling another function 'criteria_matched'.
239 --- We will concatenate all the child id into string with separator as ','
240 --- Return true if any matching child availabe with the concatenated
241 --- child plan_id's otherwise return false.
242 ---
243
244 -- Bug 2448888. when all child plans have effective from and to date range is
245 -- outside the sysdate then, FRM-41084:- Error getting Group Cell raised when
246 -- child button is hit. This is similar to bug Bug 2355817.
247 -- Make a join to qa_plans in cursor C and fetch only effective child plans.
248 -- rponnusa Tue Jul 9 00:25:19 PDT 2002
249
250 CURSOR c IS SELECT qpr.plan_relationship_id,qpr.child_plan_id
251 FROM qa_plans qp,
252 qa_pc_plan_relationship qpr
253 WHERE qpr.parent_plan_id = p_plan_id
254 AND qpr.child_plan_id = qp.plan_id
255 AND qpr.plan_relationship_type = 1
256 AND qpr.data_entry_mode in (1,2,3)
257 AND ((qp.effective_to IS NULL AND TRUNC(SYSDATE) >= qp.effective_from)
258 OR (qp.effective_from IS NULL AND TRUNC(SYSDATE) <= qp.effective_to)
259 OR (qp.effective_from IS NOT NULL AND qp.effective_to IS NOT NULL
260 AND TRUNC(SYSDATE) BETWEEN qp.effective_from AND qp.effective_to)
261 OR (qp.effective_from IS NULL AND qp.effective_to IS NULL ));
262
263 current_child_plan_id NUMBER;
264 p_plan_relationship_id NUMBER;
265 ret_value VARCHAR2(1);
266 childexist BOOLEAN;
267 elements qa_txn_grp.ElementsArray;
268 BEGIN
269 ret_value := 'F';
270 childexist := FALSE;
271 elements := qa_txn_grp.result_to_array(p_criteria_values);
272 OPEN c;
273 LOOP
274 FETCH c INTO p_plan_relationship_id,current_child_plan_id;
275 IF (c%NOTFOUND) THEN
276 EXIT;
277 END IF;
278
279 IF( criteria_matched(p_plan_relationship_id,elements) = 'T') THEN
280
281 IF( childexist) THEN
282 x_child_plan_ids := x_child_plan_ids ||','||current_child_plan_id;
283 ELSE
284 x_child_plan_ids :=current_child_plan_id;
285 childexist := TRUE;
286 END IF;
287 END IF;
288 END LOOP;
289
290 IF (c%ROWCOUNT = 0) THEN
291 ret_value := 'F';
292 ELSIF (x_child_plan_ids IS NULL) THEN
293 ret_value := 'F';
294 ELSE
295 ret_value := 'T';
296 END IF;
297 CLOSE c;
298
299 RETURN ret_value;
300 END evaluate_child_lov_criteria;
301
302 /* following function added to be able to view history records in VQR.
303 The name is eval_updateview_lov_criteria and NOT evaluate_updateview_lov_criteria
304 because there's a character limit for length of function name in package.
305 */
306
307 FUNCTION eval_updateview_lov_criteria( p_plan_id IN NUMBER,
308 p_criteria_values IN VARCHAR2,
309 x_child_plan_ids OUT NOCOPY VARCHAR2)
310 RETURN VARCHAR2 IS
311 ---
312 --- This function finds all the matching child plan for the current plan.
313 --- First converts the values passed through p_criteria_values
314 --- into array. For each child plan we checking for the criteria values
315 --- by calling another function 'criteria_matched'.
316 --- We will concatenate all the child id into string with separator as ','
317 --- Return true if any matching child availabe with the concatenated
318 --- child plan_id's otherwise return false.
319 ---
320
321 -- Bug 2448888. when all child plans have effective from and to date range is
322 -- outside the sysdate then, FRM-41084:- Error getting Group Cell raised when
323 -- child button is hit. This is similar to bug Bug 2355817.
324 -- Make a join to qa_plans in cursor C and fetch only effective child plans.
325 -- rponnusa Tue Jul 9 00:25:19 PDT 2002
326
327 CURSOR c IS SELECT qpr.plan_relationship_id,qpr.child_plan_id
328 FROM qa_plans qp,
329 qa_pc_plan_relationship qpr
330 WHERE qpr.parent_plan_id = p_plan_id
331 AND qpr.child_plan_id = qp.plan_id
332 AND qpr.plan_relationship_type = 1
333 AND qpr.data_entry_mode in (1,2,3,4)
334 AND ((qp.effective_to IS NULL AND TRUNC(SYSDATE) >= qp.effective_from)
335 OR (qp.effective_from IS NULL AND TRUNC(SYSDATE) <= qp.effective_to)
336 OR (qp.effective_from IS NOT NULL AND qp.effective_to IS NOT NULL
337 AND TRUNC(SYSDATE) BETWEEN qp.effective_from AND qp.effective_to)
338 OR (qp.effective_from IS NULL AND qp.effective_to IS NULL ));
339
340 current_child_plan_id NUMBER;
341 p_plan_relationship_id NUMBER;
342 ret_value VARCHAR2(1);
343 childexist BOOLEAN;
344 elements qa_txn_grp.ElementsArray;
345 BEGIN
346 ret_value := 'F';
347 childexist := FALSE;
348 elements := qa_txn_grp.result_to_array(p_criteria_values);
349 OPEN c;
350 LOOP
351 FETCH c INTO p_plan_relationship_id,current_child_plan_id;
352 IF (c%NOTFOUND) THEN
353 EXIT;
354 END IF;
355
356 IF( criteria_matched(p_plan_relationship_id,elements) = 'T') THEN
357
358 IF( childexist) THEN
359 x_child_plan_ids := x_child_plan_ids ||','||current_child_plan_id;
360 ELSE
361 x_child_plan_ids :=current_child_plan_id;
362 childexist := TRUE;
363 END IF;
364 END IF;
365 END LOOP;
366
367 IF (c%ROWCOUNT = 0) THEN
368 ret_value := 'F';
369 ELSIF (x_child_plan_ids IS NULL) THEN
370 ret_value := 'F';
371 ELSE
372 ret_value := 'T';
373 END IF;
374 CLOSE c;
375
376 RETURN ret_value;
377
378 END eval_updateview_lov_criteria;
379
380 -----------------------------------------------------------------------------------------------------
381 FUNCTION criteria_matched(p_plan_relationship_id IN NUMBER,
382 p_criteria_array qa_txn_grp.ElementsArray)
383 RETURN VARCHAR2 IS
384
385 ---
386 --- This function first finds out all the criteria for the parent-child
387 --- relationship through plan_relationship_id in qa_pc_criteria table.
388 --- If no criteria found then return true
389 --- else finds out all the char_id and its associated values
390 --- for the plan_relationship_id.
391
392 --- Check for each char_id (ie,. element ) to see value available
393 --- in the parent form. We are checking this condition in the
394 --- element array (which contains all the parent-form char_id and
395 --- its associated value). If, not able to find the char_id in the
396 --- element array then return false.
397
398 --- If there is matching char_id in element array then compare
399 --- the value in the element array with the low_value, high_value.
400 --- If everything is ok then return true, false otherwise.
401 ---
402
403 l_char_id NUMBER ;
404 l_operator NUMBER ;
405 l_low_value VARCHAR2(150);
406 l_high_value VARCHAR2(150);
407 l_ret_value VARCHAR2(1);
408 l_datatype NUMBER;
409
410 CURSOR c IS SELECT qpc.char_id,qpc.operator,qpc.low_value,qpc.high_value,qc.datatype
411 FROM qa_pc_criteria qpc ,qa_chars qc
412 WHERE qpc.plan_relationship_id = p_plan_relationship_id
413 AND qpc.char_id = qc.char_id;
414 BEGIN
415
416 l_ret_value := 'F';
417 OPEN c;
418
419 -- To launch a single child plan there may be more than one criteria defined.
420 -- Hence going into the loop to match all criteria. In case of more than one
421 -- criteria, all the criteria should match in order to return TRUE
422
423 LOOP
424 FETCH c INTO l_char_id, l_operator, l_low_value, l_high_value, l_datatype;
425 IF (c%NOTFOUND) THEN
426 EXIT;
427 END IF;
428
429 -- There are records for the plan_relationship_id, our next job is to
430 -- check the value entered in the parent plan for the element, matches
431 -- with the criteria for the same element in qa_pc_criteria
432
433 IF (p_criteria_array.EXISTS(l_char_id)) THEN
434
435 IF( QLTCOMPB.compare(p_criteria_array(l_char_id).value,
436 l_operator,l_low_value,
437 l_high_value,l_datatype)) THEN
438
439 l_ret_value := 'T';
440 ELSE
441 -- For example if 3 criteria defined and matching condition
442 -- fails in the first criteria itself then we need not check for
443 -- other criterias, we can simply exit from the loop and
444 -- return FALSE
445
446 l_ret_value := 'F';
447 EXIT;
448 END IF;
449 ELSE
450 -- This is a worst case. There is no value found in the element array
451 -- for the char_id
452 l_ret_value := 'F';
453 END IF;
454 END LOOP;
455
456 IF (c%ROWCOUNT = 0) THEN
457 -- No criteria defined for the plan, so simply return TRUE
458 l_ret_value := 'T';
459 END IF;
460 CLOSE c;
461
462 RETURN l_ret_value;
463 END criteria_matched;
464
465 ------------------------------------------------------------------------------------------
466 FUNCTION evaluate_criteria(p_plan_id IN NUMBER,
467 p_criteria_values IN VARCHAR2,
468 p_relationship_type IN NUMBER,
469 p_data_entry_mode IN NUMBER,
470 x_child_plan_ids OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
471 --
472 -- This function finds out the matching child plan for the given data_entry_mode and
473 -- relationship_type. In case of matching child found returns TRUE with the child
474 -- plan_id in a comma separated string through x_child_plan_ids.
475 -- In case of no match simply returns FALSE
476 --
477
478 CURSOR c IS SELECT plan_relationship_id,child_plan_id
479 FROM qa_pc_plan_relationship
480 WHERE parent_plan_id = p_plan_id
481 AND plan_relationship_type = p_relationship_type
482 AND data_entry_mode = p_data_entry_mode;
483
484 -- Added the cursor below to check the effectivity of the child plan.
485 -- The Cursor will fetch the child_plan only if it falls in the effective
486 -- data range. Bug 2355817. kabalakr 06 MAY 2002.
487
488 CURSOR p(l_child_plan_id NUMBER) IS
489 SELECT plan_id
490 FROM qa_plans
491 WHERE plan_id = l_child_plan_id
492 AND ((effective_to IS NULL AND TRUNC(SYSDATE) >= effective_from)
493 OR (effective_from IS NULL AND TRUNC(SYSDATE) <= effective_to)
494 OR (effective_from IS NOT NULL AND effective_to IS NOT NULL
495 AND TRUNC(SYSDATE) BETWEEN effective_from AND effective_to)
496 OR (effective_from IS NULL AND effective_to IS NULL ));
497
498
499 current_child_plan_id NUMBER ;
500 p_plan_relationship_id NUMBER ;
501 ret_value VARCHAR2(1);
502 childexist BOOLEAN;
503 separator CONSTANT VARCHAR2(1) := ',';
504
505 elements qa_txn_grp.ElementsArray;
506
507 -- Bug 2355817. kabalakr
508 l_child_pl_id NUMBER;
509
510 BEGIN
511 ret_value := 'F';
512 childexist := FALSE;
513 elements := qa_txn_grp.result_to_array(p_criteria_values);
514 OPEN c;
515 -- Get all the child plan id for the parent plan to find out any matching
516 -- childplan is exist or not.
517 LOOP
518
519 FETCH c INTO p_plan_relationship_id,current_child_plan_id;
520 IF (c%NOTFOUND) THEN
521 EXIT;
522 END IF;
523
524 -- Open cursor p for the current child_plan_id. Call criteria_matched
525 -- only if the cursor fetches the plan. Bug 2355817.
526 -- kabalakr 06 MAY 2002.
527
528 OPEN p(current_child_plan_id);
529 FETCH p INTO l_child_pl_id;
530
531 IF (p%FOUND) THEN
532
533 IF( criteria_matched(p_plan_relationship_id,elements) = 'T') THEN
534
535 -- The following 'if' condition is required because we should not return the
536 -- child plan ids with last character as the separator ( comma here)
537
538 IF( childexist) THEN
539 x_child_plan_ids := x_child_plan_ids || separator ||current_child_plan_id;
540 ELSE
541 x_child_plan_ids :=current_child_plan_id;
542 childexist := TRUE;
543 END IF;
544
545 END IF;
546 END IF;
547
548 CLOSE p;
549
550 END LOOP;
551
552 IF (c%ROWCOUNT = 0) THEN
553 -- This scanario can happen if the parent plan dont have child plan associated
554 -- with it.
555 ret_value := 'F';
556
557 ELSIF (x_child_plan_ids IS NULL) THEN
558 -- This can happen when parent plan have child but the matching criteria to launch the
559 -- child fails
560 ret_value := 'F';
561 ELSE
562 ret_value := 'T';
563 END IF;
564 CLOSE c;
565
566 RETURN ret_value;
567
568 END evaluate_criteria;
569
570 PROCEDURE parse_list(x_result IN VARCHAR2,
571 x_array OUT NOCOPY ChildPlanArray) IS
572
573 -- For longcomments enhancement, Bug 2234299
574 -- changed 'value' type from qa_results.character1%TYPE to varchar2(2000)
575 -- rponnusa Thu Mar 14 21:27:04 PST 2002
576
577 value VARCHAR2(2000);
578 c VARCHAR2(10);
579 separator CONSTANT VARCHAR2(1) := ',';
580 arr_index INTEGER;
581 p INTEGER;
582 n INTEGER;
583
584 BEGIN
585 --
586 -- Loop until a single ',' is found or x_result is exhausted.
587 --
588 arr_index := 1;
589 p := 1;
590 n := length(x_result);
591 WHILE p <= n LOOP
592 c := substr(x_result, p, 1);
593 p := p + 1;
594 IF (c = separator) THEN
595 x_array(arr_index) := value;
596 arr_index := arr_index + 1;
597 value := '';
598 ELSE
599 value := value || c;
600 END IF;
601
602 END LOOP;
603 x_array(arr_index) := value;
604 END parse_list;
605
606 --
607 -- Removed DEFAULT clause for GSCC compliance
608 -- Before removal
609 -- p_txn_header_id IN NUMBER DEFAULT NULL
610 -- After removal
611 -- p_txn_header_id IN NUMBER
612 -- rkunchal
613 --
614
615 PROCEDURE insert_automatic_records(p_plan_id IN NUMBER,
616 p_collection_id IN NUMBER,
617 p_occurrence IN NUMBER,
618 p_child_plan_ids IN VARCHAR2,
619 p_relationship_type IN NUMBER,
620 p_data_entry_mode IN NUMBER,
621 p_criteria_values IN VARCHAR2,
622 p_org_id IN NUMBER,
623 p_spec_id IN NUMBER,
624 x_status OUT NOCOPY VARCHAR2,
625 p_txn_header_id IN NUMBER) IS
626
627 parent_values_array qa_txn_grp.ElementsArray;
628 l_child_id_array ChildPlanArray;
629 l_sysdate DATE;
630 l_length INTEGER;
631 l_row_count INTEGER;
632 l_count INTEGER;
633 l_child_char_id INTEGER;
634 l_parent_char_id INTEGER;
635 l_p INTEGER;
636 l_return_int INTEGER;
637 l_occurrence NUMBER;
638 l_child_plan_id NUMBER;
639 l_child_element_values VARCHAR2(32000);
640 l_messages VARCHAR2(32000);
641 l_rowid VARCHAR2(1000);
642
643
644 CURSOR row_num_cur(c_child_plan_id NUMBER) IS
645 SELECT auto_row_count
646 FROM qa_pc_plan_relationship
647 WHERE parent_plan_id = p_plan_id
648 AND child_plan_id = c_child_plan_id;
649
650
651 -- anagarwa Mon Dec 16 16:55:09 PST 2002
652 -- Bug 2701777
653 -- if parent or child elements are disabled and the parent child relationship
654 -- still exists for them then insert API qa_mqa_results.post_result raises
655 -- returns an error and prevents the history as well as automatic results
656 -- from being saved. It causes a ON-INSERT trigger being raised on forms
657 -- and even the parent results cannot be saved.
658 -- To fix the problem, qa_pc_result_column_v is being modified to have parent
659 -- and child char's enabled flags which are checked to be 1 before the values
660 -- are copied.
661
662 CURSOR char_id_cur(c_child_plan_id NUMBER) IS
663 SELECT parent_char_id, child_char_id
664 FROM qa_pc_result_columns_v
665 WHERE parent_plan_id = p_plan_id
666 AND child_plan_id = c_child_plan_id
667 AND parent_enabled_flag = 1
668 AND child_enabled_flag = 1;
669
670 -- Bug 3678910. In Automatic data collection, sequence generation should be
671 -- enabled for Sequence type elements. The below cursor will fetch all the
672 -- sequence element char_ids which is not a target for Copy Element relation.
673 -- If there exist any copy relation with sequence element as target, the value
674 -- will be copied from the parent plan. Sequence will not get generated in that
675 -- case. kabalakr.
676
677 -- Bug 4958734. SQL Repository Fix SQL ID: 15007931
678 CURSOR child_seq_char_ids(c_child_plan_id NUMBER) IS
679 SELECT qc.char_id
680 FROM qa_plan_chars qpc, qa_chars qc
681 WHERE qpc.plan_id = c_child_plan_id
682 AND qpc.char_id = qc.char_id
683 AND qpc.enabled_flag = 1
684 AND qc.datatype = 5
685 MINUS
686 SELECT child_char_id
687 FROM qa_pc_result_columns_v
688 WHERE parent_plan_id = p_plan_id
689 AND child_plan_id = c_child_plan_id
690 AND parent_enabled_flag = 1
691 AND child_enabled_flag = 1;
692 /*
693 SELECT qc.char_id
694 FROM qa_plan_chars qpc,
695 qa_chars qc
696 WHERE qpc.plan_id = c_child_plan_id
697 AND qpc.char_id = qc.char_id
698 AND qpc.enabled_flag = 1
699 AND qc.datatype = 5
700 AND qc.char_id NOT IN
701 (SELECT child_char_id
702 FROM qa_pc_result_columns_v
703 WHERE parent_plan_id = p_plan_id
704 AND child_plan_id = c_child_plan_id
705 AND parent_enabled_flag = 1
706 AND child_enabled_flag = 1);
707 */
708
709 l_seq_default_str VARCHAR(30);
710
711 --
712 -- Bug 5383667
713 -- String to hold the Id values
714 -- ntungare
715 --
716 l_char_id_val VARCHAR2(2000);
717 l_id_str VARCHAR2(2000);
718
719 --
720 -- bug 6086385
721 -- New variable to catch the status returned
722 -- by the insert_history_auto_rec_QWB proc
723 -- called for the subsequent child records
724 -- ntungare Thu Jul 5 06:50:27 PDT 2007
725 --
726 auto_hist_proc_stat varchar2(2000);
727
728 --
729 -- bug 6086385
730 -- New variable to read the occurrence of
731 -- the Child plan record enetred
732 -- ntungare Thu Jul 5 06:50:27 PDT 2007
733 --
734 l_child_occurrence NUMBER;
735
736 BEGIN
737 l_length := length(p_child_plan_ids);
738 l_count := 1;
739 l_sysdate := sysdate;
740
741 -- flatten the p_criteria_values string into an array
742 parent_values_array := qa_txn_grp.result_to_array(p_criteria_values);
743
744 --parse p_child_plan_ids to get child plan ids in an array
745 IF p_child_plan_ids IS NOT NULL THEN
746 l_p := 0;
747 parse_list(p_child_plan_ids, l_child_id_array);
748 END IF;
749 --for each child plan insert automatic rows as follows
750 FOR i IN 1..l_child_id_array.COUNT LOOP
751
752 l_child_plan_id := l_child_id_array(i);
753
754 --if p_relationship_type=1 and p_data_entry_mode=4, it means we are
755 --entering rows for History plan. The number of rows to be entered
756 -- in this case is always 1. ELSE get the number of rows to be entered.
757 IF(p_relationship_type=1 AND p_data_entry_mode=4) THEN
758 l_row_count := 1;
759 ELSE
760 OPEN row_num_cur(l_child_id_array(i));
761 fetch row_num_cur into l_row_count;
762 CLOSE row_num_cur;
763 END IF;
764 --for row_count
765 WHILE l_count <= l_row_count LOOP
766 --OPEN cursor of parent_char_id and child_char_id from
767 -- QA_PC_RESULTS_COLUMN_V for p_plan_id and current child plan id
768 --for each cursor row
769 l_child_element_values := '';
770 FOR char_id_record IN char_id_cur(l_child_id_array(i)) LOOP
771 l_parent_char_id := char_id_record.parent_char_id;
772 l_child_char_id := char_id_record.child_char_id;
773
774 -- Bug 2403395
775 -- Added 'replace' command to doubly encode ''@' character
776 -- if the l_parent_char_id.value contains '@' character.
777 -- rponnusa Wed Jun 5 00:49:14 PDT 2002
778 l_child_element_values := l_child_element_values || '@' ||
779 l_child_char_id || '=' ||
780 replace(parent_values_array(l_parent_char_id).value,'@','@@');
781
782 --
783 -- Bug 5383667
784 -- Constructing the Id str
785 -- The id string has to be built of the format
786 -- charid=value@charid=value.
787 -- ntungare
788 --
789 l_char_id_val := qa_plan_element_api.get_id_val
790 (l_child_char_id,
791 p_plan_id,
792 p_collection_id,
793 p_occurrence);
794
795 If l_char_id_val IS NOT NULL THEN
796 l_id_str := l_id_str || '@' || l_child_char_id || '='|| l_char_id_val;
797 End If;
798
799 END LOOP; -- for all columns to be copied
800
801 -- Bug 3678910. Now, check whether we are inserting records for
802 -- data entry mode - Automatic. If yes, we should make sure to generate
803 -- sequence numbers (assign the string 'Automatic') for sequence type
804 -- elements that are not copy targets. kabalakr.
805
806 IF(p_relationship_type=1 AND p_data_entry_mode=2) THEN
807
808 fnd_message.set_name('QA','QA_SEQ_DEFAULT');
809 l_seq_default_str := fnd_message.get;
810
811 FOR seq_char_id_record IN child_seq_char_ids(l_child_id_array(i))
812 LOOP
813 l_child_element_values := l_child_element_values || '@' ||
814 seq_char_id_record.char_id || '=' ||
815 l_seq_default_str;
816 END LOOP;
817
818 END IF; -- If Automatic. End of bug 3678910.
819
820 --
821 -- Bug 5383667
822 -- Removing the extra @ appended at the start
823 -- ntungare
824 --
825 If l_id_str IS NOT NULL THEN
826 l_id_str := SUBSTR(l_id_str, 2);
827 END If;
828
829 IF (l_child_element_values IS NOT NULL) THEN
830 l_child_element_values := substr(l_child_element_values,2);
831 l_p :=1;
832
833 --
834 -- bug 5682448
835 -- modified the call to the proc to send the commit
836 -- flag as no (0)
837 -- ntungare Wed Feb 21 07:31:00 PST 2007
838 --
839
840 -- Bug 2290747.Added parameter p_txn_header_id to enable
841 -- history plan record when parent plan gets updated
842 -- rponnusa Mon Apr 1 22:25:49 PST 2002
843
844 -- anagarwa Thu Dec 19 15:43:27 PST 2002
845 -- Bug 2701777
846 -- post_result_with_no_validation inserts records into
847 -- qa_results without any validations. This prevents any
848 -- errors if user changes element values in parent plan
849 -- but not the History plan
850
851 --
852 -- bug 5383667
853 -- Passing the Id string as well
854 -- ntungare
855 --
856 l_return_int:= qa_mqa_results.post_result_with_no_validation(
857 l_occurrence,
858 p_org_id,
859 l_child_plan_id, p_spec_id,
860 p_collection_id,
861 l_child_element_values,
862 l_id_str, '', l_p, 0, l_messages,
863 p_txn_header_id);
864
865 --ilawler - bug #2648137 - Fri Mar 19 09:50:07 2004
866 --added post_result return check
867 IF l_return_int = -1 THEN
868 x_status := 'F';
869 RETURN;
870 END IF;
871
872 -- anagarwa Fri Aug 30 13:07:05 PDT 2002
873 -- Bug 2517932
874 -- following added to copy attachments to History records.
875
876 IF p_data_entry_mode = 4 THEN
877 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
878 X_from_entity_name => 'QA_RESULTS',
879 X_from_pk1_value => to_char(p_occurrence),
880 X_from_pk2_value => to_char(p_collection_id),
881 X_from_pk3_value => to_char(p_plan_id),
882 X_to_entity_name => 'QA_RESULTS',
883 X_to_pk1_value => to_char(l_occurrence),
884 X_to_pk2_value => to_char(p_collection_id),
885 X_to_pk3_value => to_char(l_child_plan_id));
886 END IF;
887
888 -- insert the relationships
889 -- Gapless Sequence Proj. rponnusa Wed Jul 30 04:52:45 PDT 2003
890 -- passing child_txn_header_id
891 QA_PC_RESULTS_REL_PKG.Insert_Row(
892 X_Rowid => l_rowid,
893 X_Parent_Plan_Id => p_plan_id,
894 X_Parent_Collection_Id => p_collection_id,
895 X_Parent_Occurrence => p_occurrence,
896 X_Child_Plan_Id => l_child_plan_id,
897 X_Child_Collection_Id => p_collection_id,
898 X_Child_Occurrence => l_occurrence,
899 X_Enabled_Flag => 1,
900 X_Last_Update_Date => l_sysdate,
901 X_Last_Updated_By => fnd_global.user_id,
902 X_Creation_Date => l_sysdate,
903 X_Created_By => fnd_global.user_id,
904 X_Last_Update_Login => fnd_global.user_id,
905 X_Child_Txn_Header_Id => p_txn_header_id);
906
907 END IF;
908 l_count := l_count + 1;
909
910 --
911 -- Bug 5383667
912 -- Resetting the Id string for the next row being
913 -- processed
914 -- ntungare
915 l_id_str := NULL;
916
917 --
918 -- bug 6086385
919 -- Getting the Occurrence of the Child record that has been
920 -- inserted
921 -- nutngare Thu Jul 5 05:21:16 PDT 2007
922 --
923 SELECT MAX(occurrence)
924 into l_child_occurrence
925 FROM qa_results
926 WHERE plan_id = l_child_plan_id and
927 collection_id = p_collection_id and
928 organization_id = p_org_id and
929 txn_header_id = p_txn_header_id;
930
931 --
932 -- bug 6086385
933 -- Processing the Subsequent Automatic Child plans
934 -- Using the insert_history_auto_rec_QWB instead of
935 -- insert_history_auto_rec to make sure that the Txn_header_id
936 -- is not incremented
937 -- ntungare Thu Jul 5 05:15:29 PDT 2007
938 --
939 insert_history_auto_rec_QWB(p_plan_id => l_child_plan_id,
940 p_collection_id => p_collection_id,
941 p_occurrence => l_child_occurrence,
942 p_organization_id => p_org_id,
943 p_txn_header_id => p_txn_header_id,
944 p_relationship_type => 1,
945 p_data_entry_mode => 2 ,
946 x_status => auto_hist_proc_stat);
947
948 --
949 -- bug 6086385
950 -- Processing the History Child plans
951 -- Using the insert_history_auto_rec_QWB instead of
952 -- insert_history_auto_rec to make sure that the Txn_header_id
953 -- is not incremented
954 -- ntungare Thu Jul 5 05:15:29 PDT 2007
955 --
956 insert_history_auto_rec_QWB(p_plan_id => l_child_plan_id,
957 p_collection_id => p_collection_id,
958 p_occurrence => l_child_occurrence,
959 p_organization_id => p_org_id,
960 p_txn_header_id => p_txn_header_id,
961 p_relationship_type => 1,
962 p_data_entry_mode => 4 ,
963 x_status => auto_hist_proc_stat);
964
965 END LOOP; --for number of rows
966
967 /* Bug 3223081 : Added the following statement to reset the l_count to 1 after all the rows are inserted for one child plan
968 l_count :=1;
969 - akbhatia
970 */
971 l_count :=1;
972 -- i := i + 1;
973 END LOOP; --outer for loop for child plans
974 x_status := 'T';
975 END;
976
977
978
979
980 FUNCTION descendants_exist(p_plan_id NUMBER,
981 p_collection_id NUMBER,
982 p_occurrence NUMBER)
983 RETURN VARCHAR2 IS
984 ---
985 --- This function takes in plan_id, collection_id and occurrence and returns a 'T'
986 --- if it finds any child record for this record. Otherwise it returns 'F'.
987 ---
988
989 --l_exists INTEGER;
990 l_exists VARCHAR2(1);
991
992 CURSOR descendant_cur IS
993 SELECT 'T'
994 FROM qa_pc_results_relationship
995 WHERE parent_occurrence = p_occurrence
996 AND rownum = 1;
997
998
999 BEGIN
1000 l_exists := 'F';
1001 OPEN descendant_cur;
1002 FETCH descendant_cur INTO l_exists;
1003 IF (descendant_cur%NOTFOUND) THEN
1004 l_exists := 'F';
1005 END IF;
1006
1007 CLOSE descendant_cur;
1008 RETURN l_exists;
1009
1010 END;
1011
1012 FUNCTION get_disabled_descendants(p_plan_id NUMBER,
1013 p_collection_id NUMBER,
1014 p_occurrence NUMBER,
1015 --p_enabled NUMBER,
1016 x_plan_ids OUT NOCOPY dbms_sql.number_table,
1017 x_collection_ids OUT NOCOPY dbms_sql.number_table,
1018 x_occurrences OUT NOCOPY dbms_sql.number_table)
1019 RETURN VARCHAR2 IS
1020 ---
1021 --- This function is similar to get_descendants above with one difference
1022 --- it looks for all disabled records .
1023 ---
1024
1025 BEGIN
1026
1027 BEGIN
1028 SELECT child_plan_id, child_collection_id, child_occurrence
1029 BULK COLLECT INTO
1030 x_plan_ids, x_collection_ids, x_occurrences
1031 FROM qa_pc_results_relationship r
1032 WHERE EXISTS (
1033 SELECT 1
1034 FROM qa_results qr
1035 WHERE qr.plan_id = r.child_plan_id AND
1036 qr.collection_id = r.child_collection_id AND
1037 qr.occurrence = r.child_occurrence AND
1038 qr.status = 1 )
1039 START WITH parent_plan_id = p_plan_id AND
1040 parent_collection_id = p_collection_id AND
1041 parent_occurrence = p_occurrence
1042 CONNECT BY PRIOR child_occurrence = parent_occurrence;
1043 EXCEPTION
1044 WHEN NO_DATA_FOUND THEN
1045 RETURN 'F';
1046 END;
1047
1048 IF SQL%FOUND THEN
1049 RETURN 'T';
1050 ELSE
1051 RETURN 'F';
1052 END IF;
1053
1054 END;
1055
1056 PROCEDURE delete_child_rows(p_plan_ids IN dbms_sql.number_table,
1057 p_collection_ids IN dbms_sql.number_table,
1058 p_occurrences IN dbms_sql.number_table,
1059 p_parent_plan_id NUMBER ,
1060 p_parent_collection_id NUMBER ,
1061 p_parent_occurrence NUMBER ,
1062 p_enabled_flag VARCHAR2)
1063
1064 IS
1065 ---
1066 --- The following procedure takes in plan_id, collection id and occurrece and
1067 --- deletes these rows from QA_RESULTS. It also deletes entry for these rows
1068 --- from relationships tables.
1069 ---
1070 --- p_enabled_flag holds 'T' => delete only enabled child records
1071 --- 'F' => delete only disabled child records
1072
1073 i INTEGER ;
1074
1075 BEGIN
1076
1077 i := 0;
1078
1079 -- Gapless Sequence Proj. rponnusa Wed Jul 30 04:52:45 PDT 2003
1080 -- Call the sequence api to capture audit information for the child/grand
1081 -- child record records. Audit info. for toplevel parent is not
1082 -- collected here.
1083
1084 -- capture audit only when enabled child records (status NULL or 2)
1085 -- are deleted
1086 IF p_enabled_flag = 'T' THEN
1087
1088 QA_SEQUENCE_API.audit_sequence_values(
1089 p_plan_ids,
1090 p_collection_ids,
1091 p_occurrences,
1092 p_parent_plan_id,
1093 p_parent_collection_id,
1094 p_parent_occurrence);
1095 END IF;
1096
1097 FORALL i IN p_occurrences.FIRST .. p_occurrences.LAST
1098 DELETE from QA_RESULTS
1099 WHERE plan_id = p_plan_ids(i)
1100 AND collection_id = p_collection_ids(i)
1101 AND occurrence = p_occurrences(i);
1102
1103 FORALL i IN p_occurrences.FIRST .. p_occurrences.LAST
1104 DELETE from QA_PC_RESULTS_RELATIONSHIP
1105 WHERE child_occurrence = p_occurrences(i);
1106 END delete_child_rows;
1107
1108
1109 PROCEDURE enable_fire_for_txn_hdr_id(p_txn_header_id IN NUMBER) IS
1110 flag BOOLEAN ;
1111
1112 BEGIN
1113
1114 IF p_txn_header_id is not null THEN
1115 UPDATE qa_results
1116 SET status = 2
1117 WHERE txn_header_id = p_txn_header_id;
1118
1119 flag := QLTDACTB.DO_ACTIONS(p_txn_header_id, 1, NULL, NULL,
1120 FALSE , FALSE, 'DEFERRED' , 'TXN_HEADER_ID');
1121 END IF;
1122 END;
1123
1124 --
1125 -- bug 5682448
1126 -- New proc to enable the records and fire
1127 -- the actions for all those enabled records
1128 -- ntungare Wed Feb 21 07:34:11 PST 2007
1129 --
1130 PROCEDURE enable_fire_for_coll_id(p_txn_header_id IN NUMBER) IS
1131 flag BOOLEAN ;
1132
1133 Type num_tab_typ is table of number index by binary_integer;
1134
1135 plan_id_tab num_tab_typ;
1136 collection_id_tab num_tab_typ;
1137 occurrence_tab num_tab_typ;
1138
1139 BEGIN
1140 IF p_txn_header_id is not null THEN
1141
1142 -- Updating the rows in the QA_RESULTS which are currently
1143 -- invalid
1144 --
1145 UPDATE qa_results
1146 SET status = 2
1147 WHERE txn_header_id = p_txn_header_id
1148 and status =1
1149 RETURNING plan_id, collection_id, occurrence
1150 BULK COLLECT INTO plan_id_tab, collection_id_tab, occurrence_tab;
1151
1152 -- Looping through all the updated records and firing
1153 -- actions for them
1154 --
1155 For Cntr in 1..plan_id_tab.COUNT
1156 LOOP
1157 -- Calling the do_actions for the plan_id, collection_id,
1158 -- Occurrence combination
1159 --
1160 flag := QLTDACTB.DO_ACTIONS
1161 (X_TXN_HEADER_ID => collection_id_tab(cntr),
1162 X_CONCURRENT => 1,
1163 X_PO_TXN_PROCESSOR_MODE => NULL,
1164 X_GROUP_ID => NULL,
1165 X_BACKGROUND => FALSE ,
1166 X_DEBUG => FALSE,
1167 X_ACTION_TYPE => 'DEFERRED' ,
1168 X_PASSED_ID_NAME => 'COLLECTION_ID',
1169 P_OCCURRENCE => occurrence_tab(cntr),
1170 P_PLAN_ID => plan_id_tab(cntr));
1171 END LOOP;
1172 END IF; --p_txn_header_id is not null
1173 END enable_fire_for_coll_id;
1174
1175
1176 -- Bug 4270911. CU2 SQL Literal fix. TD #18
1177 -- Uses FND_DSQL package for the case of unknown number of binds.
1178 -- srhariha. Fri Apr 15 06:40:15 PDT 2005.
1179
1180 FUNCTION find_parent(p_child_plan_id IN NUMBER,
1181 p_child_collection_id IN NUMBER,
1182 p_child_occurrence IN NUMBER,
1183 x_parent_plan_id OUT NOCOPY NUMBER,
1184 x_parent_collection_id OUT NOCOPY NUMBER,
1185 x_parent_occurrence OUT NOCOPY NUMBER)
1186 RETURN VARCHAR2 IS
1187
1188 --
1189 -- This function intelligently finding out parent plan record when child plan record information
1190 -- is passed. First find out parent_plan_id from qa_pc_plan_relationship. Then findout all
1191 -- element ids with which parent and child plans are related. Take only those elements which have
1192 -- link_flag = 1 in qa_pc_element_relationship table.
1193
1194 -- Find the values of the elements from qa_results for the child plan. Then find the first record
1195 -- for the parent plan which has all the elements (only those related in the qa_pc_element_relation)
1196 -- same value for those of child plan. Return the parent record information.
1197
1198 l_plan_relationship_id NUMBER;
1199 l_parent_plan_id NUMBER;
1200 l_temp_var NUMBER;
1201 l_res_col VARCHAR2(150); -- stores result column name in qa_results
1202 l_res_value VARCHAR2(150); -- stores result column value in qa_results
1203
1204 query_clause VARCHAR2(32000):= NULL;
1205 select_clause VARCHAR2(80) := NULL;
1206 from_clause CONSTANT VARCHAR2(80) := ' FROM QA_RESULTS ';
1207 where_clause VARCHAR2(5000) := NULL;
1208 parent_where_clause VARCHAR2(5000):= NULL;
1209
1210
1211 Type resCurTyp IS REF CURSOR; --define weak REF CURSOR type
1212 res_cur resCurTyp; --define cursor variable
1213
1214 CURSOR plan_cursor(p_child_plan_id NUMBER) IS
1215 SELECT plan_relationship_id,parent_plan_id
1216 FROM qa_pc_plan_relationship
1217 WHERE child_plan_id = p_child_plan_id
1218 AND rownum = 1;
1219
1220 -- Bug 2357067. Modified the element_cursor so that all parent,child columns
1221 -- can be fetched once.
1222
1223 CURSOR element_cursor(p_relationship_id NUMBER) IS
1224 select pe.parent_char_id,
1225 qpc1.result_column_name parent_database_column,
1226 pe.child_char_id,
1227 qpc2.result_column_name child_database_column
1228 from
1229 qa_pc_plan_relationship pr,
1230 qa_pc_element_relationship pe,
1231 qa_plan_chars qpc1,
1232 qa_plan_chars qpc2
1233 where
1234 pr.plan_relationship_id = pe.plan_relationship_id and
1235 pr.parent_plan_id = qpc1.plan_id and
1236 pe.parent_char_id = qpc1.char_id and
1237 pr.child_plan_id = qpc2.plan_id and
1238 pe.child_char_id = qpc2.char_id and
1239 pe.plan_relationship_id = p_relationship_id and
1240 pe.element_relationship_type = 1 and
1241 pe.link_flag = 1;
1242
1243 -- Bug 4270911. CU2 SQL Literal fix.
1244 -- New cursor handler.
1245 cursor_handle NUMBER;
1246 no_of_rows NUMBER;
1247 BEGIN
1248 l_temp_var := -99;
1249 -- Bug 4270911. CU2 SQL Literal fix.
1250 -- Use bind variables.
1251 -- srhariha. Fri Apr 15 06:22:04 PDT 2005.
1252
1253 where_clause := ' WHERE plan_id = :p_child_plan_id' ||
1254 ' AND collection_id = :p_child_collection_id' ||
1255 ' AND occurrence = :p_child_occurrence';
1256
1257 -- get the parent_plan_id for the child plan
1258 OPEN plan_cursor(p_child_plan_id);
1259 FETCH plan_cursor INTO l_plan_relationship_id,l_parent_plan_id;
1260 IF (plan_cursor%NOTFOUND) THEN
1261 CLOSE plan_cursor;
1262 RETURN 'F';
1263 END IF;
1264
1265 CLOSE plan_cursor;
1266
1267 -- Bug 4270911. CU2 SQL Literal fix.
1268 -- Use fnd_dsql package.
1269 -- srhariha. Fri Apr 15 06:22:04 PDT 2005.
1270
1271 select_clause := ' SELECT 1, plan_id, collection_id, occurrence ';
1272 fnd_dsql.init;
1273 fnd_dsql.add_text(select_clause || from_clause || ' ');
1274 fnd_dsql.add_text(' WHERE plan_id =');
1275 fnd_dsql.add_bind(l_parent_plan_id);
1276 fnd_dsql.add_text(' ');
1277
1278 -- parent_where_clause := ' WHERE plan_id = ' || parent_plan_id ;
1279
1280 -- get all the child plan elements which has relationship
1281 -- and link_flag = 1. This flag is specifically used for flow workstation integration.
1282
1283 FOR ele_rec IN element_cursor(l_plan_relationship_id) LOOP
1284
1285 select_clause := ' SELECT ' || ele_rec.child_database_column;
1286 query_clause := select_clause || from_clause || where_clause;
1287
1288 -- Bug 4270911. CU2 SQL Literal fix.
1289 -- Use bind variables.
1290 -- srhariha. Fri Apr 15 06:22:04 PDT 2005.
1291
1292
1293 OPEN res_cur FOR query_clause USING p_child_plan_id, p_child_collection_id, p_child_occurrence;
1294 FETCH res_cur INTO l_res_value;
1295 CLOSE res_cur;
1296
1297 -- If the copy element in child record is null, then build the query accordingly
1298
1299 IF l_res_value IS NULL THEN
1300 --parent_where_clause := parent_where_clause || ' AND ' ||
1301 -- ele_rec.parent_database_column || ' IS NULL';
1302 fnd_dsql.add_text(' AND ' || ele_rec.parent_database_column || ' IS NULL ');
1303 ELSE
1304 /* rkaza 06/04/2002. Bug 2302554. Enclosing l_res_value with single quotes. */
1305 -- parent_where_clause := parent_where_clause || ' AND ' ||
1306 -- ele_rec.parent_database_column || ' = ' || '''' || qa_core_pkg.dequote(l_res_value) || '''';
1307
1308 fnd_dsql.add_text(' AND ' || ele_rec.parent_database_column || ' = ');
1309 fnd_dsql.add_bind(l_res_value);
1310 fnd_dsql.add_text(' ');
1311 END IF;
1312
1313 END LOOP;
1314
1315 --Necessary to say rownum=1 to avoid multiple rows
1316 -- parent_where_clause := parent_where_clause || ' AND ROWNUM = 1 ';
1317 fnd_dsql.add_text(' AND ROWNUM = 1 ');
1318
1319 -- query_clause := select_clause || from_clause || parent_where_clause;
1320
1321 -- OPEN res_cur FOR query_clause USING l_parent_plan_id;
1322 -- FETCH res_cur INTO l_temp_var, x_parent_plan_id,
1323 -- x_parent_collection_id, x_parent_occurrence;
1324 -- CLOSE res_cur;
1325
1326
1327 cursor_handle := dbms_sql.open_cursor;
1328 fnd_dsql.set_cursor(cursor_handle);
1329
1330 query_clause := fnd_dsql.get_text;
1331 dbms_sql.parse(cursor_handle,query_clause,dbms_sql.NATIVE);
1332 fnd_dsql.do_binds;
1333
1334 dbms_sql.define_column(cursor_handle,1,l_temp_var);
1335 dbms_sql.define_column(cursor_handle,2,x_parent_plan_id);
1336 dbms_sql.define_column(cursor_handle,3,x_parent_collection_id);
1337 dbms_sql.define_column(cursor_handle,4,x_parent_occurrence);
1338
1339 no_of_rows := dbms_sql.execute(cursor_handle);
1340
1341 no_of_rows := dbms_sql.fetch_rows(cursor_handle);
1342
1343 l_temp_var := 0;
1344 IF (no_of_rows > 0) THEN
1345 dbms_sql.column_value(cursor_handle,1,l_temp_var);
1346 dbms_sql.column_value(cursor_handle,2,x_parent_plan_id);
1347 dbms_sql.column_value(cursor_handle,3,x_parent_collection_id);
1348 dbms_sql.column_value(cursor_handle,4,x_parent_occurrence);
1349
1350
1351 END IF;
1352
1353 dbms_sql.close_cursor(cursor_handle);
1354
1355 IF (l_temp_var = 1) THEN
1356 RETURN 'T';
1357 ELSE
1358 RETURN 'F';
1359 END IF;
1360
1361 END find_parent;
1362
1363 -- 12. QWB Usability Improvements
1364 -- added 2 new prameters to return a comma separated list
1365 -- of Parent plan elements for which the aggregation is done
1366 -- and the list of the aggregated values
1367 --
1368 --
1369 -- bug 7046071
1370 -- Added the parameter p_ssqr_operation parameter to check if the
1371 -- call is done from the OAF application or from Forms
1372 -- In case of the OAF application, the COMMIT that is
1373 -- executed in the aggregate_parent must not be called
1374 -- ntungare
1375 --
1376 PROCEDURE relate(p_parent_plan_id IN NUMBER,
1377 p_parent_collection_id IN NUMBER,
1378 p_parent_occurrence IN NUMBER,
1379 p_child_plan_id IN NUMBER,
1380 p_child_collection_id IN NUMBER,
1381 p_child_occurrence IN NUMBER,
1382 p_child_txn_header_id IN NUMBER,
1383 x_agg_elements OUT NOCOPY VARCHAR2,
1384 x_agg_val OUT NOCOPY VARCHAR2,
1385 p_ssqr_operation IN NUMBER DEFAULT NULL) IS
1386
1387 l_date DATE;
1388 l_user_id NUMBER;
1389 l_login_id NUMBER;
1390 l_rowid VARCHAR2(18) := null;
1391
1392 l_ret_value VARCHAR2(1);
1393 -- Gapless Sequence Proj. rponnusa Wed Jul 30 04:52:45 PDT 2003
1394 -- Added following cursor.
1395
1396 l_child_txn_header_id NUMBER;
1397
1398 CURSOR c IS
1399 SELECT txn_header_id FROM qa_results
1400 WHERE plan_id = p_child_plan_id AND
1401 collection_id = p_child_collection_id AND
1402 occurrence = p_child_occurrence;
1403
1404 -- 12.1 QWB Usability Improvements
1405 --
1406 agg_elements VARCHAR2(4000);
1407 agg_val VARCHAR2(4000);
1408 BEGIN
1409
1410 --anagarwa Fri Jun 11 15:08:03 PDT 2004
1411 -- bug 3678910
1412 -- If parent or child key is invalid then no need to create a relationship
1413 IF p_parent_occurrence < 0 OR p_parent_collection_id < 0 OR
1414 p_parent_plan_id < 0 OR p_child_plan_id < 0 OR
1415 p_child_collection_id < 0 OR p_child_occurrence < 0 THEN
1416
1417 RETURN;
1418 END IF;
1419
1420 l_user_id := fnd_global.user_id;
1421 l_login_id := fnd_global.login_id;
1422 l_date := sysdate;
1423
1424 -- Gapless Sequence Proj. rponnusa Wed Jul 30 04:52:45 PDT 2003
1425 -- Findout txn header ID for the child record
1426
1427 IF p_child_txn_header_id IS NULL THEN
1428 OPEN c;
1429 FETCH c INTO l_child_txn_header_id;
1430 CLOSE c;
1431 ELSE
1432 l_child_txn_header_id := p_child_txn_header_id;
1433 END IF;
1434
1435 -- Gapless Sequence Proj passing child_txn_header_id
1436 QA_PC_RESULTS_REL_PKG.Insert_Row(
1437 X_Rowid => l_rowid,
1438 X_Parent_Plan_Id => p_parent_plan_id,
1439 X_Parent_Collection_Id => p_parent_collection_id,
1440 X_Parent_Occurrence => p_parent_occurrence,
1441 X_Child_Plan_Id => p_child_plan_id,
1442 X_Child_Collection_Id => p_child_collection_id,
1443 X_Child_Occurrence => p_child_occurrence,
1444 X_Enabled_Flag => 1,
1445 X_Last_Update_Date => l_date,
1446 X_Last_Updated_By => l_user_id,
1447 X_Creation_Date => l_date,
1448 X_Created_By => l_user_id,
1449 X_Last_Update_Login => l_login_id,
1450 X_Child_Txn_Header_Id => l_child_txn_header_id);
1451
1452 -- Bug 2302554
1453 -- once the parent and child are related, parent record
1454 -- should be updated with child element values(if any aggregate
1455 -- relationship defined) and child record should be updated
1456 -- with parent plan values (if copy relation defined with
1457 -- link_flag = 2)
1458 -- 12.1 QWB Usabiltity improvements
1459 -- added 2 new parameters to get the parent element which
1460 -- is to have the aggregated value and to get the aggregated
1461 -- value
1462 --
1463 --
1464 -- bug 7046071
1465 -- Passing the parameter p_ssqr_operation parameter to check if the
1466 -- call is done from the OAF application or from Forms
1467 -- In case of the OAF application, the COMMIT that is
1468 -- executed in the aggregate_parent must not be called
1469 -- ntungare
1470 --
1471 l_ret_value := QA_PARENT_CHILD_PKG.update_parent(p_parent_plan_id ,
1472 p_parent_collection_id ,
1473 p_parent_occurrence,
1474 p_child_plan_id,
1475 p_child_collection_id ,
1476 p_child_occurrence,
1477 agg_elements,
1478 agg_val,
1479 p_ssqr_operation);
1480
1481 -- 12.1 QWB Usability Improvements
1482 --
1483 x_agg_elements := agg_elements;
1484 x_agg_val := agg_val;
1485
1486 l_ret_value:= QA_PARENT_CHILD_PKG.update_child(p_parent_plan_id ,
1487 p_parent_collection_id ,
1488 p_parent_occurrence,
1489 p_child_plan_id,
1490 p_child_collection_id ,
1491 p_child_occurrence );
1492
1493 END relate;
1494
1495 FUNCTION get_plan_name(p_plan_ids IN VARCHAR2 , x_plan_name OUT NOCOPY VARCHAR2) return VARCHAR2 IS
1496
1497 -- This functions returns the name of the plan when plan_id is passed.
1498 -- This can take the plan_id in the comma separated string like '501,502,503'
1499 -- and returns all the plan_name in the comma separated string.
1500
1501 -- This function is useful when we need to display the message to user about the
1502 -- childplan name or parent plan name.
1503
1504 l_child_id_array ChildPlanArray;
1505 l_total_length NUMBER;
1506 l_plan_name VARCHAR2(10000) := NULL;
1507 l_name VARCHAR2(30);
1508 l_plan_id NUMBER;
1509 l_str_from NUMBER := 1;
1510 l_str_to NUMBER;
1511 l_separator CONSTANT VARCHAR2(1) := ',';
1512
1513 CURSOR plan_cursor(c_plan_id NUMBER) IS
1514 SELECT name
1515 FROM qa_plans
1516 WHERE plan_id = c_plan_id;
1517
1518 BEGIN
1519 l_total_length := LENGTH(p_plan_ids);
1520
1521 -- We need check for all the child_plan_ids one by one or parent_plan_id to
1522 -- to get plan name
1523
1524 -- anagarwa Mon Apr 15 15:51:58 PDT 2002
1525 -- Bug 2320896 was being caused due to error in logic.
1526 -- This code is being replaced to avoid character to number conversion
1527
1528 /*
1529
1530 LOOP
1531 l_str_to := instr(p_plan_ids,l_separator,l_str_from);
1532 IF (l_str_to = 0) THEN
1533 -- we are here if only one plan id is passed or we are in the
1534 -- last child plan id
1535
1536 l_plan_id := to_number(substr(p_plan_ids, l_str_from, l_total_length));
1537 ELSE
1538 l_plan_id := to_number(substr(p_plan_ids, l_str_from, l_str_to -1 ));
1539
1540 -- Adding +1 with the l_str_to to make l_str_from variable pointing to first
1541 -- character after the comma separator
1542
1543 l_str_from := l_str_to +1;
1544 END IF;
1545
1546 OPEN plan_cursor;
1547 FETCH plan_cursor INTO l_name;
1548 IF (plan_cursor%NOTFOUND) THEN
1549 CLOSE plan_cursor;
1550 RETURN 'F';
1551 END IF;
1552
1553 IF l_plan_name IS NULL THEN
1554 l_plan_name := l_name;
1555 ELSE
1556 l_plan_name := l_plan_name || l_separator || l_name;
1557 END IF;
1558 CLOSE plan_cursor;
1559
1560 IF (l_str_to = 0 ) THEN
1561 -- We parsed all the child plan ids.
1562 EXIT;
1563 END IF;
1564 END LOOP;
1565 */
1566
1567 parse_list(p_plan_ids, l_child_id_array);
1568
1569 FOR i IN 1..l_child_id_array.COUNT LOOP
1570 l_plan_id := l_child_id_array(i);
1571 OPEN plan_cursor(l_plan_id);
1572 FETCH plan_cursor INTO l_name;
1573 IF (plan_cursor%NOTFOUND) THEN
1574 CLOSE plan_cursor;
1575 RETURN 'F';
1576 END IF;
1577
1578 IF l_plan_name IS NULL THEN
1579 l_plan_name := l_name;
1580 ELSE
1581 l_plan_name := l_plan_name || l_separator || l_name;
1582 END IF;
1583 CLOSE plan_cursor;
1584
1585 END LOOP;
1586 x_plan_name := l_plan_name;
1587
1588 RETURN 'T';
1589 END get_plan_name;
1590
1591 FUNCTION should_parent_spec_be_copied(p_parent_plan_id NUMBER, p_child_plan_id NUMBER)
1592 RETURN VARCHAR2 IS
1593
1594 -- This function returns true if parent plan specification_id can be copied to child
1595 -- else return false.
1596
1597 l_default_parent NUMBER := -99;
1598
1599 CURSOR default_cursor IS
1600 SELECT default_parent_spec
1601 FROM qa_pc_plan_relationship
1602 WHERE parent_plan_id = p_parent_plan_id
1603 AND child_plan_id = p_child_plan_id;
1604
1605
1606 BEGIN
1607
1608 -- As of now just return true. To implement this function we should have a new field
1609 -- default_parent_spec column in table qa_pc_plan_relationship.
1610 -- Hence i am commenting out the actual implementation of this function.
1611
1612 -- RETURN 'T';
1613
1614 OPEN default_cursor;
1615 FETCH default_cursor INTO l_default_parent;
1616 IF (default_cursor%NOTFOUND) THEN
1617 l_default_parent := -99;
1618 END IF;
1619 CLOSE default_cursor;
1620 IF l_default_parent = 1 THEN
1621 RETURN 'T';
1622 ELSE
1623 RETURN 'F';
1624 END IF;
1625
1626 END should_parent_spec_be_copied;
1627
1628 FUNCTION is_parent_child_plan(p_plan_id NUMBER) RETURN VARCHAR2 IS
1629 -- this functions return 'T' if the plan is parent-child relationship
1630 -- plan. ie pc relationship is defined for this plan.
1631
1632 l_is_parent_plan VARCHAR2(1);
1633
1634 CURSOR plan_cursor(p_plan_id NUMBER) IS
1635 SELECT 'T'
1636 FROM qa_pc_plan_relationship
1637 WHERE parent_plan_id = p_plan_id
1638 OR child_plan_id = p_plan_id
1639 AND rownum = 1;
1640 BEGIN
1641 l_is_parent_plan := 'F';
1642 OPEN plan_cursor(p_plan_id);
1643 FETCH plan_cursor INTO l_is_parent_plan;
1644 IF( plan_cursor%NOTFOUND) THEN
1645 l_is_parent_plan := 'F';
1646 END IF;
1647 CLOSE plan_cursor;
1648 RETURN l_is_parent_plan;
1649 END is_parent_child_plan;
1650
1651 -- Bug 4343758
1652 -- R12 OAF Txn Integration Project
1653 -- Added p_commit parameter to the existing update_parent function
1654 -- and renamed it as aggregate_parent since we do not want
1655 -- the explicit commit for OAF Txn Delete Flows
1656 -- shkalyan 05/13/2005.
1657 --
1658 -- 12.1 QWB Usability Improvements
1659 -- added 2 new parameters to get the list of the
1660 -- aggregated elements and the aggregated values.
1661 --
1662 FUNCTION aggregate_parent(p_parent_plan_id IN NUMBER,
1663 p_parent_collection_id IN NUMBER,
1664 p_parent_occurrence IN NUMBER,
1665 p_child_plan_id IN NUMBER,
1666 p_child_collection_id IN NUMBER,
1667 p_child_occurrence IN NUMBER,
1668 p_commit IN VARCHAR2,
1669 x_agg_elements OUT NOCOPY VARCHAR2,
1670 x_agg_val OUT NOCOPY VARCHAR2)
1671 RETURN VARCHAR2 IS
1672
1673 l_sql_string VARCHAR2(32000);
1674 l_update_parent_sql VARCHAR2(32000);
1675 l_value NUMBER;
1676
1677 --
1678 -- Bug 6450756
1679 -- Declaration of variables needed for
1680 -- locking the row that needs to be
1681 -- updated with the aggregating values.
1682 -- bhsankar Sun Sep 30 23:38:58 PDT 2007
1683 --
1684 l_parent_db_col VARCHAR2(30);
1685 l_select_sql VARCHAR2(32000);
1686
1687 -- anagarwa Mon Dec 16 16:55:09 PST 2002
1688 -- Bug 2701777
1689 -- added parent_enabled_flag and child_enabled_flag to where clause
1690 -- to limit working on onlly those elements that are enabled.
1691 CURSOR element_cursor IS
1692 SELECT parent_database_column,
1693 child_database_column,
1694 element_relationship_type,
1695 parent_char_id
1696 FROM qa_pc_result_columns_v
1697 WHERE parent_plan_id = p_parent_plan_id
1698 AND child_plan_id = p_child_plan_id
1699 AND element_relationship_type in (2,3,4,5,6,7,8)
1700 AND parent_enabled_flag = 1
1701 AND child_enabled_flag = 1;
1702
1703 --
1704 -- Bug 6450756
1705 -- User Defined exception for handling row locks
1706 -- in scenarios U->V->U or U->V->E
1707 -- where aggegating into top most parent will
1708 -- result in a lock.
1709 -- bhsankar Sun Sep 30 23:38:58 PDT 2007
1710 --
1711 ROW_LOCK_FAILED EXCEPTION;
1712 PRAGMA EXCEPTION_INIT(ROW_LOCK_FAILED, -54);
1713
1714
1715 BEGIN
1716
1717 FOR cur_rec IN element_cursor LOOP
1718
1719 -- build the required sql string
1720
1721 l_sql_string := 'FROM qa_results qr, qa_pc_results_relationship pc'
1722 || ' WHERE qr.plan_id=pc.child_plan_id'
1723 || ' AND qr.collection_id=pc.child_collection_id'
1724 || ' AND qr.occurrence=pc.child_occurrence'
1725 || ' AND pc.parent_occurrence= :p_parent_occurrence'
1726 || ' AND pc.child_plan_id= :p_child_plan_id'
1727 --
1728 -- bug 5682448
1729 -- Added the extra condititon to aggregate only the
1730 -- enabled records in stauts 2 or NULL
1731 -- ntungare Wed Feb 21 07:38:04 PST 2007
1732 --
1733 || ' AND (qr.status = 2 OR qr.status IS NULL)';
1734
1735 -- Bug 2427337. Fix here is not related this bug. To use aggregate functions
1736 -- on a element which is stored in character col in qa_results table, we need
1737 -- to use to_number function, or else, unwanted value will be returned.
1738 -- rponnusa Tue Jun 25 06:15:48 PDT 2002
1739
1740 IF (cur_rec.element_relationship_type = 2 ) THEN -- sum
1741 l_sql_string := 'SELECT SUM(to_number(qr.'||cur_rec.child_database_column||')) ' || l_sql_string;
1742 ELSIF (cur_rec.element_relationship_type = 3 ) THEN -- average or Mean
1743 l_sql_string := 'SELECT AVG(to_number(qr.'||cur_rec.child_database_column||')) ' || l_sql_string;
1744 ELSIF (cur_rec.element_relationship_type = 4 ) THEN -- std. deviation
1745 l_sql_string := 'SELECT STDDEV(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
1746
1747 ELSIF (cur_rec.element_relationship_type = 5 ) THEN -- min
1748 l_sql_string := 'SELECT MIN(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
1749 ELSIF (cur_rec.element_relationship_type = 6 ) THEN -- max
1750 l_sql_string := 'SELECT MAX(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
1751 ELSIF (cur_rec.element_relationship_type = 7 ) THEN -- variance
1752 l_sql_string := 'SELECT VARIANCE(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
1753 ELSIF (cur_rec.element_relationship_type = 8 ) THEN -- count
1754 -- anagarwa Tue Feb 18 11:13:20 PST 2003
1755 -- Bug 2789847
1756 -- Count may be done on non numeric elements like Sequence Numbers and
1757 -- even Nonconformance Status, Source etc.
1758 -- A to_number will cause an exception in such a case and is hence
1759 -- removed from sql statement.
1760 l_sql_string := 'SELECT COUNT(qr.'|| cur_rec.child_database_column||') ' || l_sql_string;
1761 END IF;
1762 -- find out the aggregate value for the element in child plan.
1763 BEGIN
1764 EXECUTE IMMEDIATE l_sql_string INTO l_value
1765 USING p_parent_occurrence,p_child_plan_id;
1766 EXCEPTION
1767 WHEN OTHERS THEN raise;
1768
1769 END;
1770
1771 -- Bug 2716973
1772 -- When the child aggregate relationship element value is updated to parent record,
1773 -- Post-Forms-Commit Trigger error raised if child element contain null value.
1774 -- rponnusa Sun Jan 12 23:59:07 PST 2003
1775
1776 l_value := NVL(l_value,0);
1777
1778 -- See 2624112
1779 -- The maximum allowed precision is now expanded to 12.
1780 -- Rounding to 12...
1781 -- rkunchal Thu Oct 17 22:51:45 PDT 2002
1782
1783 -- rounding off to 6 digits is required since, for a number field, the maximum allowd
1784 -- decimal places is 6.
1785
1786 -- l_value := round(l_value,6);
1787 l_value := round(l_value,12);
1788 --
1789 -- Bug 6450756
1790 -- Lock the row in the parent so that the
1791 -- values can be aggregated from the child
1792 -- If the row is not getting locked, then
1793 -- it might be because of the following flow
1794 -- U->V->U or U->V->E. Catch the exception and
1795 -- dont take any action since the aggregations
1796 -- would anyway fire at the parent level.
1797 -- bhsankar Sun Sep 30 23:38:58 PDT 2007
1798 --
1799 l_select_sql := 'SELECT '
1800 || cur_rec.parent_database_column
1801 || ' FROM qa_results WHERE plan_id = :p_parent_plan_id'
1802 || ' AND collection_id= :p_parent_collection_id'
1803 || ' AND occurrence= :p_parent_occurrence FOR UPDATE NOWAIT';
1804
1805 BEGIN
1806 EXECUTE IMMEDIATE l_select_sql INTO l_parent_db_col
1807 USING p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
1808
1809 -- now we need to update the parent record. Build the sql here.
1810
1811 l_update_parent_sql := 'UPDATE qa_results SET '
1812 || cur_rec.parent_database_column || ' = :l_value'
1813 || ' WHERE plan_id= :p_parent_plan_id'
1814 || ' AND collection_id= :p_parent_collection_id'
1815 || ' AND occurrence= :p_parent_occurrence';
1816 BEGIN
1817 EXECUTE IMMEDIATE l_update_parent_sql
1818 USING l_value,p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
1819
1820 -- 12.1 QWB Usability improvements
1821 -- Building a list of the Aggregated parent plan elements
1822 --
1823 x_agg_elements := x_agg_elements ||','||
1824 qa_ak_mapping_api.get_vo_attribute_name(cur_rec.parent_char_id, p_parent_plan_id);
1825 -- 12.1 QWB Usability improvements
1826 -- Building a list of the Aggregated values
1827 --
1828 x_agg_val := x_agg_val ||','|| l_value;
1829
1830 EXCEPTION
1831 WHEN OTHERS THEN raise;
1832 END;
1833
1834 EXCEPTION
1835 WHEN ROW_LOCK_FAILED THEN NULL;
1836 WHEN OTHERS THEN RAISE;
1837 END;
1838
1839 END LOOP;
1840 -- we are returning true when the parent record is updated or
1841 -- there is no aggregate relationship defined for the parent,child plans.
1842
1843 -- Bug 4343758
1844 -- R12 OAF Txn Integration Project
1845 -- Added check based on p_commit parameter since we do not want to commit
1846 -- by default if invoked from OAF Pages.
1847 -- shkalyan 05/13/2005.
1848 IF ( p_commit = 'T' ) THEN
1849 -- Bug 2300962. Needs explicit commit, if called from post-database-commit trigger
1850 COMMIT;
1851 END IF;
1852
1853 RETURN 'T';
1854
1855 END aggregate_parent;
1856
1857 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
1858 p_parent_collection_id IN NUMBER,
1859 p_parent_occurrence IN NUMBER,
1860 p_child_plan_id IN NUMBER,
1861 p_child_collection_id IN NUMBER,
1862 p_child_occurrence IN NUMBER)
1863 RETURN VARCHAR2 IS
1864
1865 -- 12.1 QWB Usability Improvements
1866 agg_elements VARCHAR2(4000);
1867 agg_val VARCHAR2(4000);
1868 BEGIN
1869 -- 12.1 QWB Usability Improvements
1870 return update_parent(p_parent_plan_id,
1871 p_parent_collection_id,
1872 p_parent_occurrence,
1873 p_child_plan_id,
1874 p_child_collection_id,
1875 p_child_occurrence,
1876 agg_elements,
1877 agg_val);
1878 END;
1879
1880 -- 12.1 QWB Usability Improvements
1881 -- Overloaded method that has 2 additional parameters
1882 -- that return a list of Aggregated elements and
1883 -- their values
1884 --
1885 -- bug 7046071
1886 -- Passing the parameter p_ssqr_operation parameter to check if the
1887 -- call is done from the OAF application or from Forms
1888 -- In case of the OAF application, the COMMIT that is
1889 -- executed in the aggregate_parent must not be called
1890 -- ntungare
1891 --
1892 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
1893 p_parent_collection_id IN NUMBER,
1894 p_parent_occurrence IN NUMBER,
1895 p_child_plan_id IN NUMBER,
1896 p_child_collection_id IN NUMBER,
1897 p_child_occurrence IN NUMBER,
1898 x_agg_elements OUT NOCOPY VARCHAR2,
1899 x_agg_val OUT NOCOPY VARCHAR2,
1900 p_ssqr_operation IN NUMBER DEFAULT NULL)
1901 RETURN VARCHAR2 IS
1902 l_return_status VARCHAR2(1);
1903
1904 agg_elements VARCHAR2(4000);
1905 agg_val VARCHAR2(4000);
1906
1907 -- bug 7046071
1908 l_commit VARCHAR2(1) := 'T';
1909 BEGIN
1910 -- Bug 4343758
1911 -- R12 OAF Txn Integration Project
1912 -- Moved the entire code to aggregate_parent function because
1913 -- this function was committing by default. Since we do not want
1914 -- the explicit commit for OAF Txn Delete Flows we have introduced this
1915 -- new procedure which accepts the commit flag as input.
1916
1917 --
1918 -- bug 7046071
1919 -- If this processing is initiated from the OAF application
1920 -- either standalone or through OAF txn then the commit must
1921 -- not be done since this would taken care by the framework
1922 -- so setting the commit flag as 'F'
1923 -- ntungare
1924 --
1925 IF (p_ssqr_operation IN (1,2)) THEN
1926 l_commit := 'F';
1927 END IF;
1928
1929 -- 12.1 QWB Usabitlity Improvements.
1930 -- Passing parameters for the aggregated elements
1931 --
1932 -- bug 7046071
1933 -- Passing the derived value for the commit flag
1934 -- ntungare
1935 --
1936 l_return_status :=
1937 aggregate_parent
1938 (
1939 p_parent_plan_id => p_parent_plan_id,
1940 p_parent_collection_id => p_parent_collection_id,
1941 p_parent_occurrence => p_parent_occurrence,
1942 p_child_plan_id => p_child_plan_id,
1943 p_child_collection_id => p_child_collection_id,
1944 p_child_occurrence => p_child_occurrence,
1945 p_commit => l_commit,
1946 x_agg_elements => agg_elements,
1947 x_agg_val => agg_val
1948 );
1949
1950 x_agg_elements := agg_elements;
1951 x_agg_val := agg_val;
1952
1953 -- Bug 4343758. OA Framework Integration project.
1954 -- Function should return the status back to caller.
1955 -- srhariha. Tue May 24 22:56:13 PDT 2005.
1956 RETURN l_return_status;
1957 END update_parent;
1958
1959 --
1960 -- bug 6266439
1961 -- New procedure to peform the date conversions while
1962 -- selecting and updating the data in the QA_RESULTS
1963 -- table, while peforming a Child record update.
1964 -- ntungare Thu Aug 2 03:32:32 PDT 2007
1965 --
1966 PROCEDURE DATE_SELECT_UPDATE(p_parent_result_column IN VARCHAR2,
1967 p_child_result_column IN VARCHAR2,
1968 p_parent_plan_id IN NUMBER,
1969 p_child_plan_id IN NUMBER,
1970 p_var IN NUMBER,
1971 p_select_column OUT NOCOPY VARCHAR2,
1972 p_update_column OUT NOCOPY VARCHAR2)
1973 IS
1974
1975 -- Cursor to check if the resultcolumn is
1976 -- of the DateTime type and whether its a Hardcoded
1977 -- element
1978 Cursor cur (p_plan_id in NUMBER, p_res_col in VARCHAR2) is
1979 Select 1, qc.hardcoded_column
1980 from qa_plan_chars qpc, qa_chars qc
1981 where qpc.plan_id = p_plan_id
1982 and qpc.char_id = qc.char_id
1983 and qpc.result_column_name = p_res_col
1984 and qc.datatype = 6;
1985
1986 data_found PLS_INTEGER := 0;
1987 hardcoded_column QA_CHARS.HARDCODED_COLUMN%TYPE := NULL;
1988
1989 parent_is_date BOOLEAN := FALSE;
1990 child_is_date BOOLEAN := FALSE;
1991 parent_hardcoded_column BOOLEAN := FALSE;
1992 child_hardcoded_column BOOLEAN := FALSE;
1993 BEGIN
1994 -- Checking if the Parent element is a date element
1995 Open cur (p_parent_plan_id, p_parent_result_column);
1996 Fetch cur into data_found, hardcoded_column;
1997 Close cur;
1998
1999 If data_found =1 Then
2000 parent_is_date := TRUE;
2001 data_found :=0;
2002
2003 -- Checking if the Parent element is a HC date
2004 If hardcoded_column IS NOT NULL THEN
2005 parent_hardcoded_column := TRUE;
2006 hardcoded_column := NULL;
2007 END If;
2008 End If;
2009
2010 -- This processing is to be performed only if the
2011 -- elements are dates. If the parent element is not a date
2012 -- then the child element too won't be of the date type since
2013 -- relationship with any other datatype cannot be established
2014 -- in which case, the processing can be terminated.
2015 --
2016 If parent_is_date <> TRUE THEN
2017 RETURN;
2018 ELSE
2019 -- Checking if the Child element is a date element
2020 Open cur (p_child_plan_id, p_child_result_column);
2021 fetch cur into data_found, hardcoded_column;
2022 Close cur;
2023
2024 If data_found =1 Then
2025 child_is_date := TRUE;
2026 data_found :=0;
2027 -- Checking if the Child element is a HC date
2028 If hardcoded_column IS NOT NULL THEN
2029 child_hardcoded_column := TRUE;
2030 hardcoded_column := NULL;
2031 END If;
2032 End If;
2033
2034
2035 If (parent_is_date) AND (parent_hardcoded_column) THEN
2036 --HC-> HC
2037 If (child_is_date) AND (child_hardcoded_column) THEN
2038 p_select_column := 'to_char('||p_parent_result_column||',''DD-MON-YYYY HH24:MI:SS'') ';
2039 p_update_column := 'to_date(:'||to_char(p_var)||',''DD-MON-YYYY HH24:MI:SS'') ';
2040
2041 --HC-> SC
2042 ELSIF (child_is_date) AND (child_hardcoded_column = FALSE) THEN
2043 p_select_column := 'QLTDATE.date_to_canon_dt('||p_parent_result_column||') ';
2044 p_update_column := ':'||to_char(p_var);
2045 END IF;
2046 ELSIF (parent_is_date) AND (parent_hardcoded_column = FALSE) THEN
2047 --SC-> HC
2048 If (child_is_date) AND (child_hardcoded_column) THEN
2049 p_select_column := p_parent_result_column;
2050 p_update_column := 'qltdate.canon_to_date(:'||to_char(p_var)||') ';
2051
2052 --SC-> SC
2053 ELSIF (child_is_date) AND (child_hardcoded_column = FALSE) THEN
2054 p_select_column := p_parent_result_column;
2055 p_update_column := ':'||to_char(p_var);
2056 END If;
2057 END If;
2058 END If;
2059 END DATE_SELECT_UPDATE;
2060
2061 -- 5114865
2062 -- Function to perform the Updation of the Child
2063 -- Plan Columns those which have been identified
2064 -- as having a Copy Relationship with the corresponding
2065 -- Parent Plans
2066 -- This section of code was earlier a part of
2067 -- update_child Function and was extraced so that
2068 -- it can be used in common by function
2069 -- update_sequence_Child
2070 -- nutngare Wed Mar 8 09:00:46 PST 2006
2071 --
2072 FUNCTION perform_child_update(p_parentchild_element_tab IN QA_PARENT_CHILD_PKG.g_parentchild_elementtab_type,
2073 p_parent_plan_id IN NUMBER,
2074 p_parent_collection_id IN NUMBER,
2075 p_parent_occurrence IN NUMBER,
2076 p_child_plan_id IN NUMBER,
2077 p_child_collection_id IN NUMBER,
2078 p_child_occurrence IN NUMBER)
2079 RETURN VARCHAR2 IS
2080
2081 l_sql_string VARCHAR2(32000) := NULL;
2082 l_update_clause VARCHAR2(32000) := NULL;
2083 -- bug 6266477
2084 -- Increased the width to 32000 from 2000 for l_value
2085 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2086 l_value VARCHAR2(32000);
2087 l_append BOOLEAN := FALSE;
2088 l_comma CONSTANT VARCHAR2(3) := ' , ';
2089
2090
2091 c1 NUMBER;
2092 ignore NUMBER;
2093 l_var NUMBER := 1;
2094 -- bug 6266477
2095 -- Commented the bindTab array since the elementsarray would
2096 -- be used to build the array out of the string of values.
2097 -- l_bind_var would be declared as an object of ElementsArray
2098 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2099 --
2100 -- TYPE bindTab IS TABLE OF l_value%TYPE INDEX BY BINARY_INTEGER;
2101 -- l_bind_var bindTab;
2102 l_bind_var qa_txn_grp.ElementsArray;
2103
2104 --
2105 -- bug 6266439
2106 -- New variable to hold the name of the column
2107 -- to be selected from the QA_RESULTS table
2108 -- ntungare Thu Aug 2 03:40:42 PDT 2007
2109 --
2110 select_column varchar2(2000);
2111
2112 -- New variable to hold the bind variable
2113 -- to be updated in the QA_RESULTS table
2114 update_column varchar2(2000);
2115
2116 BEGIN
2117 For element_cntr in 1..p_parentchild_element_tab.count
2118 LOOP
2119 --
2120 -- Bug 6266477
2121 -- Moved the if block below for better framing.
2122 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2123 --
2124 -- IF(l_append) THEN
2125 -- l_update_clause := l_update_clause || l_comma;
2126 -- END IF;
2127
2128 --
2129 -- bug 6266439
2130 -- If the result column names are not of the sequenceXX or CommentsXX
2131 -- type, then they can be of the HC or SC date Type.
2132 -- So make a call to the new proc to get the appropriate Select
2133 -- and update columns
2134 -- ntungare Thu Aug 2 03:42:18 PDT 2007
2135 --
2136 If ((Substr(UPPER(p_parentchild_element_tab(element_cntr).parent_database_column),1,8) <> 'SEQUENCE') AND
2137 (Substr(UPPER(p_parentchild_element_tab(element_cntr).parent_database_column),1,7) <> 'COMMENT')) THEN
2138
2139 DATE_SELECT_UPDATE(p_parent_result_column => UPPER(p_parentchild_element_tab(element_cntr).parent_database_column),
2140 p_child_result_column => UPPER(p_parentchild_element_tab(element_cntr).child_database_column),
2141 p_parent_plan_id => p_parent_plan_id,
2142 p_child_plan_id => p_child_plan_id,
2143 p_var => l_var,
2144 p_select_column => select_column,
2145 p_update_column => update_column);
2146
2147 End If;
2148 -- bug 6266477
2149 -- Moved if block below for easier framing of
2150 -- the select clause.
2151 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2152 --
2153 IF(l_append) THEN
2154 l_update_clause := l_update_clause || l_comma;
2155 --
2156 -- bug 6266477
2157 -- Added the following to execute the query
2158 -- to fetch all the parent result column values
2159 -- in a single hit to qa_results table
2160 -- The string is built as 1=<result_column_value1>@2=result_column_value2>
2161 -- so that the result_to_array can be reused to collect into an array.
2162 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2163 --
2164 l_sql_string := l_sql_string || ' || ''@';
2165 l_sql_string := l_sql_string || element_cntr || '='' || '
2166 || 'replace(' || NVL(select_column, p_parentchild_element_tab(element_cntr).parent_database_column) || ', ''@'', ''@@'')';
2167 ELSE
2168 l_sql_string := l_sql_string || '''' || element_cntr || '='' || '
2169 || 'replace(' || NVL(select_column, p_parentchild_element_tab(element_cntr).parent_database_column) || ', ''@'', ''@@'')';
2170 END IF;
2171
2172 --
2173 -- bug 6266439
2174 -- Making use of the select columns string
2175 -- ntungare Thu Aug 2 03:42:18 PDT 2007
2176 --
2177 /*
2178 l_sql_string := 'SELECT ' || p_parentchild_element_tab(element_cntr).parent_database_column
2179 || ' FROM qa_results '
2180 || ' WHERE plan_id= :p_parent_plan_id'
2181 || ' AND collection_id= :p_parent_collection_id'
2182 || ' AND occurrence= :p_parent_occurrence';
2183 */
2184 --
2185 -- bug 626477
2186 -- Commented out the execution of the built string,
2187 -- since this query needs to be executed
2188 -- only once to better performance.
2189 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2190 --
2191 /*
2192 l_sql_string := 'SELECT ' || NVL(select_column, p_parentchild_element_tab(element_cntr).parent_database_column)
2193 || ' FROM qa_results '
2194 || ' WHERE plan_id= :p_parent_plan_id'
2195 || ' AND collection_id= :p_parent_collection_id'
2196 || ' AND occurrence= :p_parent_occurrence';
2197
2198 BEGIN
2199 EXECUTE IMMEDIATE l_sql_string INTO l_value
2200 USING p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
2201 EXCEPTION
2202 WHEN OTHERS THEN RETURN 'F';
2203 END;
2204 */
2205 -- anagarwa Fri May 24 11:09:43 PDT 2002
2206 -- bug 2388986. Though not directly related to this bug, it was found
2207 -- during the analysis/review. If there's a single quote in value then
2208 -- this whole thing will fail. adding dequote prevents such catastrophic
2209 -- scenarios.
2210
2211 -- Bug 2976810. Instead of the literal value concatenation and execution using
2212 -- EXECUTE IMMEDIATE, we'll pack these values into an array, bind them and
2213 -- and execute using DBMS_SQL.execute. kabalakr
2214
2215 -- l_update_clause := l_update_clause || cur_rec.child_database_column ||
2216 -- ' = ' || ''''||qa_core_pkg.dequote(l_value) ||'''';
2217
2218 --
2219 -- bug 6266439
2220 -- Making use of the update columns string
2221 -- ntungare Thu Aug 2 03:45:10 PDT 2007
2222 --
2223 /*
2224 l_update_clause := l_update_clause ||
2225 p_parentchild_element_tab(element_cntr).child_database_column ||
2226 ' = :'||to_char(l_var);
2227 */
2228 l_update_clause := l_update_clause ||
2229 p_parentchild_element_tab(element_cntr).child_database_column ||
2230 ' = '||NVL(update_column, ':'||to_char(l_var));
2231 --
2232 -- bug 6266477
2233 -- Commented the below assignment
2234 -- since it will happen after statement execution
2235 -- outside the loop.
2236 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2237 --
2238 -- l_bind_var(l_var) := l_value;
2239 l_var := l_var + 1;
2240
2241 l_append := TRUE;
2242 END LOOP;
2243
2244 IF( l_update_clause IS NULL) THEN
2245 -- this will happen only if the element_cursor does not fetch any records.
2246 RETURN 'T';
2247 END IF;
2248
2249 --
2250 -- bug 6266477
2251 -- Execute the select statement here to hit the table
2252 -- QA_RESULTS only once to improve performance and get
2253 -- the values into anl_bind_var array.
2254 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2255 --
2256 l_sql_string := 'Select ' || l_sql_string
2257 || ' FROM qa_results '
2258 || ' WHERE plan_id= :p_parent_plan_id'
2259 || ' AND collection_id= :p_parent_collection_id'
2260 || ' AND occurrence= :p_parent_occurrence';
2261 BEGIN
2262 EXECUTE IMMEDIATE l_sql_string INTO l_value
2263 USING p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
2264 EXCEPTION
2265 WHEN OTHERS THEN RETURN 'F';
2266 END;
2267
2268 l_bind_var := qa_txn_grp.result_to_array(l_value);
2269
2270 l_update_clause := 'UPDATE qa_results SET ' || l_update_clause
2271 || ' WHERE plan_id= :p_child_plan_id'
2272 || ' AND collection_id= :p_child_collection_id'
2273 || ' AND occurrence= :p_child_occurrence';
2274
2275 BEGIN
2276
2277 c1 := dbms_sql.open_cursor;
2278 dbms_sql.parse(c1, l_update_clause, dbms_sql.native);
2279
2280 l_var := l_bind_var.FIRST;
2281
2282 WHILE (l_var IS NOT NULL) LOOP
2283 --
2284 -- bug 6266477
2285 -- Replaced bind statement since, l_bind_val is
2286 -- an object of qa_txn_grp.ElementsArray which
2287 -- will have 2 fields and we are interested
2288 -- only in the value field.
2289 -- skolluku Sun Oct 14 03:26:31 PDT 2007
2290 --
2291 -- dbms_sql.bind_variable(c1, ':' || to_char(l_var), l_bind_var(l_var));
2292 dbms_sql.bind_variable(c1, ':' || to_char(l_var), l_bind_var(l_var).value);
2293 l_var := l_bind_var.NEXT(l_var);
2294 END LOOP;
2295
2296 dbms_sql.bind_variable(c1, ':p_child_plan_id', p_child_plan_id);
2297 dbms_sql.bind_variable(c1, ':p_child_collection_id', p_child_collection_id);
2298 dbms_sql.bind_variable(c1, ':p_child_occurrence', p_child_occurrence);
2299
2300 ignore := dbms_sql.execute(c1);
2301
2302 --bug# 5510747 shkalyan. Added close cursor
2303 dbms_sql.close_cursor(c1);
2304
2305 EXCEPTION
2306 WHEN OTHERS THEN
2307 --
2308 -- Bug 4675642.
2309 -- The cursor c1 was not being closed in case of error during processing the records. Doing
2310 -- that now.
2311 -- ntungare Sun Oct 16 21:38:29 PDT 2005
2312 --
2313 IF dbms_sql.is_open(c1)
2314 THEN
2315 dbms_sql.close_cursor(c1);
2316 END IF;
2317 RETURN 'F';
2318 END;
2319 Return 'T';
2320 END perform_child_update;
2321
2322 FUNCTION update_child(p_parent_plan_id IN NUMBER,
2323 p_parent_collection_id IN NUMBER,
2324 p_parent_occurrence IN NUMBER,
2325 p_child_plan_id IN NUMBER,
2326 p_child_collection_id IN NUMBER,
2327 p_child_occurrence IN NUMBER)
2328 RETURN VARCHAR2 IS
2329
2330 -- the following cursor contains sql text used for the view
2331 -- qa_pc_result_columns_v. I added link_flag in where clause.
2332
2333 -- anagarwa Mon Dec 16 16:55:09 PST 2002
2334 -- Bug 2701777
2335 -- added parent_enabled_flag and child_enabled_flag to where clause
2336 -- to limit working on onlly those elements that are enabled.
2337
2338 CURSOR element_cursor IS
2339 SELECT qprc.parent_database_column,
2340 qprc.child_database_column
2341 FROM
2342 qa_pc_result_columns_v qprc
2343 WHERE
2344 qprc.parent_plan_id = p_parent_plan_id and
2345 qprc.child_plan_id = p_child_plan_id and
2346 qprc.element_relationship_type = 1 and
2347 parent_enabled_flag = 1 and
2348 child_enabled_flag = 1;
2349
2350 -- suramasw.Bug 3561911.
2351
2352 -- The following cursor was added to generate sequence numbers
2353 -- for sequence elements in child plan which donot have copy
2354 -- relation from the parent plan in date entry mode 'Automatic'.
2355
2356 -- The cursor does the following(starting from the inner join)
2357 -- 1.get the child plan collection elements char_id's which have
2358 -- copy relation with the parent plan and when the date entry
2359 -- mode is 'Automatic'.
2360 -- 2.get the child plan collection elements char_ids which are
2361 -- of datatype sequence and which donot belong to the set of
2362 -- values fetched in step 1 mentioned above.
2363 -- 3.get the result_column_name(SEQUENCE1, SEQUENCE2, ......)
2364 -- for the values fetched in step 2 mentioned above.
2365
2366 /* Bug 3678910. Commenting out the changes done for bug 3561911.
2367 Please see the bug for more info. kabalakr.
2368
2369 CURSOR seq_cursor is
2370 SELECT qpc.char_id,
2371 qpc.result_column_name
2372 FROM qa_chars qc,
2373 qa_plan_chars qpc
2374 WHERE qpc.plan_id = p_child_plan_id
2375 AND qpc.char_id NOT IN
2376 (SELECT child_char_id
2377 FROM qa_pc_element_relationship qper,
2378 qa_pc_plan_relationship qppr
2379 WHERE qper.plan_relationship_id = qppr.plan_relationship_id
2380 AND qppr.parent_plan_id = p_parent_plan_id
2381 AND qppr.child_plan_id = p_child_plan_id
2382 AND qppr.data_entry_mode = 2)
2383 AND qpc.char_id = qc.char_id
2384 AND qc.datatype =5;
2385
2386 l_seq VARCHAR2(2000);
2387
2388 */
2389
2390
2391 -- 5114865
2392 -- Collection to hold the PC relationship elements
2393 l_element_cursor_tab QA_PARENT_CHILD_PKG.g_parentchild_elementtab_type;
2394
2395 -- Counter for the PC elements
2396 l_element_cntr PLS_INTEGER := 1;
2397
2398 l_ret_val VARCHAR2(10);
2399
2400 BEGIN
2401 -- Bug 5114865
2402 -- Collecting the child plan elements which are
2403 -- to be updated into a collection that would be
2404 -- passed to perform_child_update to processing
2405 -- ntungare Wed Mar 8 09:04:26 PST 2006
2406 --
2407 FOR cur_rec IN element_cursor LOOP
2408 l_element_cursor_tab(l_element_cntr).parent_database_column:= cur_rec.parent_database_column;
2409 l_element_cursor_tab(l_element_cntr).child_database_column := cur_rec.child_database_column;
2410 l_element_cntr := l_element_cntr + 1;
2411 END LOOP;
2412
2413 -- Bug 5114865
2414 -- Calling the proedure to perform the update
2415 -- ntungare Wed Mar 8 09:04:26 PST 2006
2416 --
2417 l_ret_val := perform_child_update
2418 (p_parentchild_element_tab => l_element_cursor_tab,
2419 p_parent_plan_id => p_parent_plan_id ,
2420 p_parent_collection_id => p_parent_collection_id,
2421 p_parent_occurrence => p_parent_occurrence,
2422 p_child_plan_id => p_child_plan_id ,
2423 p_child_collection_id => p_child_collection_id,
2424 p_child_occurrence => p_child_occurrence);
2425
2426 If l_ret_val = 'F'
2427 THEN RETURN 'F';
2428 END IF;
2429
2430 RETURN 'T';
2431 END update_child;
2432
2433 -- bug 5114865
2434 -- New Function to get a list of all the elements
2435 -- in the Child Plan that get the value from a seq
2436 -- type element in the Parent plan or from a char
2437 -- element having a sequence Ancestor
2438 -- ntungare Wed Mar 22 01:13:53 PST 2006
2439 --
2440 FUNCTION get_seq_rel_elements (p_parent_plan_id IN NUMBER,
2441 p_child_plan_id IN NUMBER,
2442 p_topmostRel_flag IN BOOLEAN,
2443 p_parent_elements_tab IN QA_PARENT_CHILD_PKG.g_parentchild_elementtab_type,
2444 p_elements_tab OUT NOCOPY QA_PARENT_CHILD_PKG.g_parentchild_elementtab_type)
2445
2446 RETURN BOOLEAN AS
2447
2448 l_parent_datatype NUMBER;
2449 l_childdbcolname VARCHAR2(1000);
2450 l_prev_parentdbcolname VARCHAR2(1000);
2451
2452 -- Cursor to fetch the Char elements in the child plan
2453 -- having sequence ancestor but not a direct Seq-char
2454 -- relation
2455 Cursor element_cur(p_parentdbcol VARCHAR2) IS
2456 SELECT qprc.parent_database_column parent_database_column,
2457 qprc.child_database_column child_database_column
2458 FROM
2459 qa_pc_result_columns_v qprc
2460 WHERE
2461 qprc.parent_plan_id = p_parent_plan_id and
2462 qprc.child_plan_id = p_child_plan_id and
2463 qprc.element_relationship_type = 1 and
2464 parent_enabled_flag = 1 and
2465 child_dataType <> 5 and
2466 child_enabled_flag = 1 and
2467 parent_database_column = p_parentdbcol ;
2468
2469 elements_tab_count PLS_INTEGER;
2470 BEGIN
2471 -- Fetching the Sequence to Char Relation Elements
2472 SELECT qprc.parent_database_column parent_database_column,
2473 qprc.child_database_column child_database_column
2474 BULK COLLECT INTO p_elements_tab
2475 FROM
2476 qa_pc_result_columns_v qprc
2477 WHERE
2478 qprc.parent_plan_id = p_parent_plan_id and
2479 qprc.child_plan_id = p_child_plan_id and
2480 qprc.element_relationship_type = 1 and
2481 parent_enabled_flag = 1 and
2482 child_dataType <> 5 and
2483 child_enabled_flag = 1 and
2484 parent_dataType = 5 ;
2485
2486 -- A topmost plan-child combination will only have
2487 -- Seq-Char copy relation. However, further down the
2488 -- hierarchy the copy relationship can be between
2489 -- two char elements, the one on the parent having a
2490 -- Sequence Ancestor. The following section of code
2491 -- fetches these relations
2492 -- ntungare
2493 If p_topmostRel_flag = FALSE THEN
2494 elements_tab_count := NVL(p_elements_tab.LAST,0);
2495
2496 -- Looping through the elements that have been copied onto the
2497 -- Current parent plan, when it was processed as a child, and
2498 -- Checking if any of these have to be futher copied down on to
2499 -- the Current Child plan.
2500 FOR cntr in 1..p_parent_elements_tab.COUNT
2501 LOOP
2502 -- Fetching the values from the Cursor defined above
2503 -- passing the Parent and Child Plan Id and the Parent
2504 -- DB columns and setting the Child DB column where they need
2505 -- to be copied .
2506 FOR elem in element_cur(p_parent_elements_tab(cntr).child_database_column)
2507 LOOP
2508 elements_tab_count := elements_tab_count +1;
2509 p_elements_tab(elements_tab_count).parent_database_column := elem.parent_database_column;
2510 p_elements_tab(elements_tab_count).child_database_column := elem.child_database_column;
2511 END LOOP;
2512 END LOOP;
2513 END IF;
2514
2515 -- Checking if any P-C relations exist
2516 -- If they do then the function return
2517 -- TRUE after wich furhter processing can
2518 -- be done.
2519 IF p_elements_tab.COUNT <> 0 THEN
2520 RETURN TRUE;
2521 ELSE RETURN FALSE;
2522 END IF;
2523 END get_seq_rel_elements;
2524
2525 -- Bug 5114865
2526 -- New Procedure to get a list of the elements that
2527 -- have been copied onto the plan Id passed when it
2528 -- is processed as a child plan.
2529 -- The elements those have been copied at every level
2530 -- are stored in the Collection nested in p_parentchild_Tab
2531 -- ntungare Wed Mar 22 01:14:24 PST 2006
2532 --
2533 PROCEDURE get_parent_elementscopied(p_parentchild_Tab IN QA_PARENT_CHILD_PKG.ParentChildTabTyp,
2534 p_current_plan_id IN NUMBER,
2535 p_parent_elements_tab OUT NOCOPY QA_PARENT_CHILD_PKG.g_parentchild_elementtab_type)
2536 AS
2537 BEGIN
2538 -- Looping through all the P-C relations
2539 -- Till we reach a level where the Plan Id passed
2540 -- has been a child plan
2541 For cntr in 1..p_parentchild_Tab.COUNT
2542 LOOP
2543 If p_current_plan_id = p_parentchild_Tab(cntr).child_plan_id
2544 THEN
2545 -- If the level if found then the elements
2546 -- those have been copied in it when it was a child
2547 -- are returned in a collection.
2548 -- These are the only elements whose values may or
2549 -- maynot have to be propagated further to the
2550 -- subsequent Children, if such a relationship exists
2551 p_parent_elements_tab := p_parentchild_Tab(cntr).parentelement_tab;
2552 EXIT;
2553 END IF;
2554 END LOOP;
2555 END get_parent_elementscopied;
2556
2557
2558 -- Bug 5114865
2559 -- Function to copy the values from the Parent to the Child plans
2560 -- only when the source element in the Parent plan is of the
2561 -- Sequence Type and that in the child is of the Char Type
2562 -- ntungare Wed Mar 22 01:15:26 PST 2006
2563 --
2564 FUNCTION update_sequence_child(p_ParentChild_Tab IN QA_PARENT_CHILD_PKG.ParentChildTabTyp)
2565 RETURN VARCHAR2 IS
2566
2567 Type Num_tab_Typ is table of NUMBER INDEX BY BINARY_INTEGER;
2568 parentCol_DataType_Tab Num_tab_Typ;
2569 parentwithSeq_flag BOOLEAN;
2570
2571 l_ParentChild_Tab QA_PARENT_CHILD_PKG.ParentChildTabTyp;
2572 l_elements_toprocess_tab QA_PARENT_CHILD_PKG.g_parentchild_elementtab_type;
2573 l_parent_elements_tab QA_PARENT_CHILD_PKG.g_parentchild_elementtab_type;
2574
2575 l_ret_val VARCHAR2(10);
2576
2577 l_element_cntr PLS_INTEGER := 1;
2578
2579 l_topmostRel_flag BOOLEAN;
2580 l_topmost_plan_id NUMBER;
2581
2582 BEGIN
2583 l_ParentChild_Tab := p_ParentChild_Tab;
2584 parentwithSeq_flag := FALSE;
2585 l_topmostRel_flag := TRUE;
2586
2587 --Getting the Topmost Plan Id
2588 l_topmost_plan_id := l_ParentChild_Tab(1).parent_plan_id;
2589
2590 -- Looping through the P-C plan combinations to be processed
2591 For cntr in 1..l_ParentChild_Tab.COUNT
2592 LOOP
2593 -- If the current Parent Plan Id is the same as the
2594 -- Topmost plan id then setting the flag accordingly
2595 -- This flag would determine if we need to look at what
2596 -- elements have been copied to the Current Parent plan
2597 -- when it was processed as a child, or not, as it would
2598 -- have no meaning for the Topmost level Plan.
2599 If l_ParentChild_Tab(cntr).parent_plan_id = l_topmost_plan_id
2600 THEN l_topmostRel_flag := TRUE;
2601 ELSE l_topmostRel_flag := FALSE;
2602 END IF;
2603
2604 -- Getting the elements copied to the current Parent Plan
2605 -- during its processing as a child Plan.
2606 -- For the Topmost level plan, this would be of no meaning
2607 IF l_topmostRel_flag = FALSE THEN
2608 get_parent_elementscopied(p_ParentChild_Tab => l_ParentChild_Tab,
2609 p_current_plan_id => l_ParentChild_Tab(cntr).parent_plan_id,
2610 p_parent_elements_tab => l_parent_elements_tab);
2611 END IF;
2612
2613 -- Getting a list of the the elements in the child plan
2614 -- that either get the value from a sequence element in
2615 -- the parent plan or a Char element which is turn may
2616 -- have received the data from a sequence element
2617 --
2618 parentwithSeq_flag := get_seq_rel_elements
2619 (p_parent_plan_id => l_ParentChild_Tab(cntr).parent_plan_id,
2620 p_child_plan_id => l_ParentChild_Tab(cntr).child_plan_id,
2621 p_topmostRel_flag => l_topmostRel_flag,
2622 p_parent_elements_tab => l_parent_elements_tab,
2623 p_elements_tab => l_elements_toprocess_tab);
2624
2625 -- Processing the list of elements obtained from above
2626 -- and captured in l_pc_elementstoprocess_tab
2627 -- The parentwithSeq_flag would be TRUE only if any of
2628 -- the elements of the Child Plan have copy relations
2629 -- with Seq Type elements or with Char elements with Seq
2630 -- Type ancestors, in the parent plan
2631 --
2632 IF parentwithSeq_flag THEN
2633 l_ret_val := perform_child_update
2634 (p_parentchild_element_tab => l_elements_toprocess_tab,
2635 p_parent_plan_id => l_ParentChild_Tab(cntr).parent_plan_id,
2636 p_parent_collection_id => l_ParentChild_Tab(cntr).parent_collection_id,
2637 p_parent_occurrence => l_ParentChild_Tab(cntr).parent_occurrence,
2638 p_child_plan_id => l_ParentChild_Tab(cntr).child_plan_id,
2639 p_child_collection_id => l_ParentChild_Tab(cntr).child_collection_id,
2640 p_child_occurrence => l_ParentChild_Tab(cntr).child_occurrence);
2641
2642 If l_ret_val = 'F'
2643 THEN RETURN 'F';
2644 END IF;
2645
2646 -- Resetting the flag value
2647 parentwithSeq_flag := FALSE;
2648
2649 -- Copying the list of the elements copied to the
2650 -- Child plan into the nested collection in l_ParentChild_Tab
2651 -- as this would be looked up when the Current Child plan is
2652 -- processed as a Parent Plan
2653 --
2654 l_ParentChild_Tab(cntr).parentelement_tab:= l_elements_toprocess_tab;
2655
2656 -- Emptying the elements collection
2657 l_elements_toprocess_tab.DELETE;
2658 END IF;
2659 END LOOP;
2660 RETURN 'T';
2661 END update_sequence_child;
2662
2663 PROCEDURE get_criteria_values(p_parent_plan_id IN NUMBER,
2664 p_parent_collection_id IN NUMBER,
2665 p_parent_occurrence IN NUMBER,
2666 p_organization_id IN NUMBER,
2667 x_criteria_values OUT NOCOPY VARCHAR2) IS
2668
2669
2670 CURSOR parent_cur IS
2671 /*
2672 anagarwa Tue Jul 16 18:36:52 PDT 2002
2673 Bug 2465920 reports that when hardcoded elements have to be copied to
2674 history or automatic plans in collection imports, it (histor/automatic
2675 functionality) fails.
2676 By selecting form_field instead of database_column we can fix it.
2677 However, item, comp_item, locator and comp_locator don't exist in
2678 QA_RESULTS_V. So we add special handling for these later.
2679 */
2680 --SELECT char_id,database_column
2681 SELECT char_id, replace(form_field, 'DISPLAY' , 'CHARACTER') database_column,
2682 datatype
2683 FROM qa_pc_plan_columns_v
2684 WHERE plan_id = p_parent_plan_id;
2685
2686 --
2687 -- bug 6266477
2688 -- Increased the width of the variables
2689 -- l_res_value, select_clause to 32000
2690 -- skolluku Mon Oct 15 02:57:40 PDT 2007
2691 --
2692 l_res_value VARCHAR2(32000);
2693 l_string VARCHAR2(32000);
2694 l_append BOOLEAN;
2695
2696 select_clause VARCHAR2(32000);
2697 from_clause CONSTANT VARCHAR2(80) := ' FROM QA_RESULTS_V ';
2698 where_clause VARCHAR2(5000);
2699 query_clause VARCHAR2(32000);
2700 -- anagarwa Tue Jul 16 18:36:52 PDT 2002
2701 -- Bug 2465920: new variable to handle item, comp_item, locator and
2702 -- comp_locator
2703 column_name VARCHAR2(150);
2704
2705
2706 -- Bug 3776542. Performance issue due to use of literals in the SQL to fetch
2707 -- criteria value from QA_RESULTS_V. Earlier we were using reference cursor to
2708 -- fetch the value with a SQL that had literals. After fix, we are using EXECUTE_IMMEDIATE
2709 -- with SQL containing bind variables. This ref cursor is needed no more, hence commenting
2710 -- it out.Thu Jul 29 02:02:03 PDT 2004.
2711 -- srhariha.
2712
2713 -- Type resCurTyp IS REF CURSOR; --define weak REF CURSOR type
2714 -- res_cur resCurTyp; --define cursor variable
2715
2716 BEGIN
2717
2718 -- Bug 3776542. Performance issue due to use of literals in the SQL. Modified the
2719 -- string to include bind variables.
2720 -- srhariha.Thu Jul 29 02:02:03 PDT 2004.
2721 l_append := FALSE;
2722 where_clause := ' WHERE plan_id = ' || ':p_parent_plan_id' ||
2723 ' AND collection_id = ' ||':p_parent_collection_id' ||
2724 ' AND occurrence = ' || ':p_parent_occurrence';
2725
2726 -- finding out the values for each element of parent record
2727 FOR parent_rec IN parent_cur LOOP
2728 -- anagarwa Tue Jul 16 18:36:52 PDT 2002
2729 -- Bug 2465920: item, comp_item, locator and comp_locator don't exist in
2730 -- QA_REULTS_V so we select id's instead.
2731 column_name := parent_rec.database_column;
2732 IF column_name = 'ITEM' THEN
2733 column_name := 'ITEM_ID';
2734
2735 ELSIF column_name = 'COMP_ITEM' THEN
2736 column_name := 'COMP_ITEM_ID';
2737
2738 ELSIF column_name = 'LOCATOR' THEN
2739 column_name := 'LOCATOR_ID';
2740
2741 ELSIF column_name = 'COMP_LOCATOR' THEN
2742 column_name := 'COMP_LOCATOR_ID';
2743
2744 -- Bug 2694385. Added bill_reference,routing_reference,to_locator since
2745 -- these elements will not present in qa_results_v.
2746 -- rponnusa Wed Dec 18 05:38:40 PST 2002
2747
2748 ELSIF column_name = 'BILL_REFERENCE' THEN
2749 column_name := 'BILL_REFERENCE_ID';
2750
2751 ELSIF column_name = 'ROUTING_REFERENCE' THEN
2752 column_name := 'ROUTING_REFERENCE_ID';
2753
2754 ELSIF column_name = 'TO_LOCATOR' THEN
2755 column_name := 'TO_LOCATOR_ID';
2756
2757 -- Bug 3424886 ksoh Mon Feb 9 13:39:41 PST 2004
2758 -- need to convert hardcoded dates to canonical string
2759 ELSIF (substr(column_name, 1, 9) <> 'CHARACTER') AND
2760 (parent_rec.datatype = qa_ss_const.datetime_datatype) THEN
2761 column_name := 'FND_DATE.DATE_TO_CANONICAL(' || column_name || ')';
2762 END IF;
2763 --
2764 -- bug 6266477
2765 -- Commenting the below code since the handling is done
2766 -- differently to avoid multiple hits to QA_RESULTS_V
2767 -- skolluku Mon Oct 15 02:57:40 PDT 2007
2768 --
2769 /*select_clause := 'SELECT ' || column_name;
2770 query_clause := select_clause || from_clause || where_clause;
2771
2772 -- Bug 3776542. Performance issue due to use of literals in the SQL to fetch
2773 -- criteria value from QA_RESULTS_V. Earlier we were using reference cursor to
2774 -- fetch the value with a SQL that had literals. After fix, we are using EXECUTE_IMMEDIATE
2775 -- with SQL containing bind variables. This ref cursor is needed no more, hence commenting
2776 -- it out.
2777 -- srhariha.Thu Jul 29 02:02:03 PDT 2004
2778
2779 --OPEN res_cur FOR query_clause ;
2780 --FETCH res_cur INTO l_res_value;
2781 --CLOSE res_cur;
2782 EXECUTE IMMEDIATE query_clause
2783 INTO l_res_value
2784 USING p_parent_plan_id,
2785 p_parent_collection_id,
2786 p_parent_occurrence;
2787
2788 IF (l_append) THEN
2789 l_string := l_string || '@';
2790 END IF;
2791 */
2792
2793
2794 -- Bug 2694385. Commented existing IF condition and added following code with
2795 -- bill_reference,routing_reference,to_locator
2796 -- rponnusa Wed Dec 18 05:38:40 PST 2002
2797
2798 /*
2799 IF ((parent_rec.char_id = 10) or parent_rec.char_id = 60) then
2800 l_res_value := qa_flex_util.item(p_organization_id , l_res_value);
2801 ELSIF ((parent_rec.char_id = 15) or (parent_rec.char_id = 65)) then
2802 l_res_value := qa_flex_util.locator(p_organization_id, l_res_value);
2803 END IF;
2804 */
2805 -- Bug 6266477
2806 -- Commented below code.
2807 -- skollluku Mon Oct 15 02:57:40 PDT 2007
2808 /*IF parent_rec.char_id IN (qa_ss_const.item, qa_ss_const.comp_item,
2809 qa_ss_const.routing_reference, qa_ss_const.bill_reference) THEN
2810 l_res_value := qa_flex_util.item(p_organization_id , l_res_value);
2811
2812 ELSIF parent_rec.char_id IN (qa_ss_const.locator, qa_ss_const.comp_locator,
2813 qa_ss_const.to_locator) THEN
2814 l_res_value := qa_flex_util.locator(p_organization_id, l_res_value);
2815 END IF;
2816
2817 -- Bug 2403395
2818 -- If the l_res_value contains '@' character then doubly encode it.
2819 -- rponnusa Wed Jun 5 00:49:14 PDT 2002
2820 l_res_value := replace(l_res_value,'@','@@');
2821
2822 l_string := l_string || parent_rec.char_id || '=' || l_res_value;
2823 */
2824 --
2825 -- bug 6266477
2826 -- Added the below code to enhance performance
2827 -- by hitting the view QA_RESULTS_V just once
2828 -- skolluku Mon Oct 15 02:57:40 PDT 2007
2829 --
2830 IF parent_rec.char_id IN (qa_ss_const.item, qa_ss_const.comp_item,
2831 qa_ss_const.routing_reference, qa_ss_const.bill_reference) THEN
2832 column_name := 'qa_flex_util.item(' || p_organization_id || ', ' || column_name || ')';
2833
2834 ELSIF parent_rec.char_id IN (qa_ss_const.locator, qa_ss_const.comp_locator,
2835 qa_ss_const.to_locator) THEN
2836 column_name := 'qa_flex_util.locator(' || p_organization_id || ', ' || column_name || ')';
2837 END IF;
2838
2839 column_name := 'replace(' || column_name || ', ''@'', ''@@'')';
2840 if (l_append) then
2841 l_string := l_string || ' || ''@';
2842 l_string := l_string || parent_rec.char_id || '='' || ' || column_name;
2843 else
2844 l_string := l_string || '''' || parent_rec.char_id || '='' || ' || column_name;
2845 end if;
2846
2847 l_append := TRUE;
2848
2849 END LOOP;
2850 --
2851 -- bug 6266477
2852 -- Executing the statement outside the loop
2853 -- to improve performance.
2854 -- skolluku Mon Oct 15 02:57:40 PDT 2007
2855 --
2856 select_clause := 'SELECT ' || l_string;
2857 query_clause := select_clause || from_clause || where_clause;
2858 EXECUTE IMMEDIATE query_clause
2859 INTO l_res_value
2860 USING p_parent_plan_id,
2861 p_parent_collection_id,
2862 p_parent_occurrence;
2863 --
2864 -- bug 6266477
2865 -- Modified since l_res_value needs be
2866 -- assigned to x_criteria_values
2867 -- skolluku Mon Oct 15 02:57:40 PDT 2007
2868 --
2869 -- x_criteria_values := l_string;
2870 x_criteria_values := l_res_value;
2871
2872 END get_criteria_values;
2873
2874
2875
2876 PROCEDURE insert_history_auto_rec(p_parent_plan_id IN NUMBER,
2877 p_txn_header_id IN NUMBER,
2878 p_relationship_type IN NUMBER,
2879 p_data_entry_mode IN NUMBER) IS
2880
2881 CURSOR plan_cur IS
2882 SELECT 1
2883 FROM qa_pc_plan_relationship
2884 WHERE parent_plan_id = p_parent_plan_id
2885 AND plan_relationship_type = p_relationship_type
2886 AND data_entry_mode = p_data_entry_mode;
2887
2888 CURSOR res_cur IS
2889 SELECT collection_id,occurrence,organization_id
2890 FROM qa_results
2891 WHERE plan_id = p_parent_plan_id
2892 AND txn_header_id = p_txn_header_id;
2893
2894
2895 l_dummy NUMBER := -99;
2896 l_spec_id NUMBER;
2897 x_status VARCHAR2(1);
2898 l_status VARCHAR2(1);
2899
2900 l_criteria_values VARCHAR2(32000);
2901 l_child_plan_ids VARCHAR2(10000);
2902
2903 -- variables declared for bug 2302539
2904 l_child_txn_header_id NUMBER;
2905 l_fire_action BOOLEAN := FALSE;
2906
2907 BEGIN
2908 IF(QA_PARENT_CHILD_PKG.is_parent_child_plan(p_parent_plan_id ) = 'F') THEN
2909 -- don't do anything
2910 RETURN;
2911 END IF;
2912 OPEN plan_cur;
2913 FETCH plan_cur INTO l_dummy;
2914 CLOSE plan_cur;
2915
2916 IF(l_dummy <> 1) THEN
2917 -- no history or automatic child plans
2918 RETURN;
2919 END IF;
2920
2921 -- Bug 2302539
2922 -- Parent and child records txn_header_id should be different in order to fire
2923 -- actions for the child plans, since action firing for the parent record
2924 -- was taken care in collection import code. We just needs to fire actions
2925 -- for the child records.
2926 -- rponnusa Tue May 28 01:52:47 PDT 2002
2927 FOR c1 in (SELECT mtl_material_transactions_s.nextval txn_header_id FROM DUAL) LOOP
2928
2929 l_child_txn_header_id := c1.txn_header_id;
2930 EXIT;
2931 END LOOP;
2932
2933 FOR import_rec IN res_cur LOOP
2934 get_criteria_values(p_parent_plan_id,
2935 import_rec.collection_id,
2936 import_rec.occurrence,
2937 import_rec.organization_id,
2938 l_criteria_values);
2939 l_status := evaluate_criteria(p_parent_plan_id,
2940 l_criteria_values,
2941 p_relationship_type,
2942 p_data_entry_mode,
2943 l_child_plan_ids);
2944
2945 IF(l_status = 'T') THEN
2946
2947 insert_automatic_records(p_parent_plan_id,
2948 import_rec.collection_id,
2949 import_rec.occurrence,
2950 l_child_plan_ids,
2951 p_relationship_type,
2952 p_data_entry_mode,
2953 l_criteria_values,
2954 import_rec.organization_id,
2955 l_spec_id,
2956 x_status,
2957 l_child_txn_header_id);
2958 l_fire_action := TRUE;
2959 END IF;
2960
2961
2962 END LOOP;
2963
2964 -- Bug 2302539
2965 -- enable and fire actions only if atleast one history/automatic record is inserted.
2966 -- Passing child_txn_header_id to fire actions for the child plans.
2967 -- rponnusa Tue May 28 01:52:47 PDT 2002
2968
2969 IF l_fire_action THEN
2970 enable_fire_for_txn_hdr_id(l_child_txn_header_id);
2971 END IF;
2972
2973 END insert_history_auto_rec;
2974
2975 FUNCTION is_parent_saved(p_plan_id IN NUMBER,
2976 p_collection_id IN NUMBER,
2977 p_occurrence IN NUMBER)
2978 RETURN VARCHAR2 IS
2979
2980 -- Return true if the given parent record is saved in enable status
2981
2982 CURSOR c IS
2983 SELECT 1
2984 FROM qa_results
2985 WHERE plan_id = p_plan_id
2986 AND collection_id = p_collection_id
2987 AND occurrence = p_occurrence
2988 AND status = 2;
2989
2990 l_status NUMBER := -99;
2991 BEGIN
2992 OPEN c;
2993 FETCH c INTO l_status;
2994 CLOSE c;
2995 IF (l_status = 1) THEN
2996 RETURN 'T';
2997 ELSE
2998 RETURN 'F';
2999 END IF;
3000
3001 END is_parent_saved;
3002
3003 FUNCTION update_all_children(p_parent_plan_id IN NUMBER,
3004 p_parent_collection_id IN NUMBER,
3005 p_parent_occurrence IN NUMBER)
3006 RETURN VARCHAR2 IS
3007
3008 l_return_value VARCHAR2(1);
3009 l_dummy VARCHAR2(1);
3010
3011 -- anagarwa Fri May 24 09:57:43 PDT 2002
3012 -- bug 2388986
3013 -- the cursor was incorrect because it was updating all children including
3014 -- history records. this is incorrect as instead of just inserting a new
3015 -- record for history, all previour records are updated with new data.
3016 -- This in turn causes the audit trail to be lost thereby defeating the
3017 -- whole purpose of having history plans!
3018 -- I've added a new join with qa_pc_plan_relationship to ensure this does
3019 -- NOT happen for history plans.
3020 -- IT IS EXTREMELY IMPORTANT TO ENSURE THAT A SINGLE PAIR OF PARENT CHILD
3021 -- PLANS FORM A SINGLE RELATIONSHIP. IF NOT THAT THIS JOIN WILL FAIL !
3022 CURSOR children_cur IS
3023 select qprr.child_plan_id,
3024 qprr.child_collection_id,
3025 qprr.child_occurrence
3026 from qa_pc_results_relationship qprr,
3027 qa_pc_plan_relationship qpr
3028 where qprr.parent_occurrence = p_parent_occurrence
3029 and qprr.parent_plan_id = p_parent_plan_id
3030 and qprr.parent_collection_id = p_parent_collection_id
3031 and qpr.parent_plan_id = qprr.parent_plan_id
3032 and qpr.child_plan_id = qprr.child_plan_id
3033 and qpr.data_entry_mode <> 4;
3034
3035 BEGIN
3036 l_return_value := 'T';
3037 l_dummy := 'T';
3038
3039 FOR children_rec IN children_cur
3040 LOOP
3041 l_return_value :=
3042 update_child ( p_parent_plan_id,
3043 p_parent_collection_id,
3044 p_parent_occurrence,
3045 children_rec.child_plan_id,
3046 children_rec.child_collection_id,
3047 children_rec.child_occurrence);
3048
3049 --check if the fetched child has any children
3050 IF (descendants_exist(children_rec.child_plan_id,
3051 children_rec.child_collection_id,
3052 children_rec.child_occurrence) = 'T')
3053 THEN
3054 --Recursive call
3055 l_dummy :=
3056 update_all_children(children_rec.child_plan_id,
3057 children_rec.child_collection_id,
3058 children_rec.child_occurrence);
3059 END IF;
3060 END LOOP;
3061
3062 RETURN l_return_value;
3063
3064 END update_all_children;
3065
3066
3067 FUNCTION applicable_child_plans_eqr( p_plan_id IN NUMBER ,
3068 p_criteria_values IN VARCHAR2)
3069 RETURN VARCHAR2 IS
3070
3071 ret_flag VARCHAR2(10);
3072 child_plan_list VARCHAR2(1000);
3073
3074 BEGIN
3075
3076 ret_flag := evaluate_child_lov_criteria (p_plan_id, p_criteria_values,
3077 child_plan_list);
3078
3079 RETURN child_plan_list;
3080
3081
3082 END applicable_child_plans_eqr;
3083
3084 FUNCTION applicable_child_plans(p_plan_id IN NUMBER,
3085 p_criteria_values IN VARCHAR2)
3086 RETURN VARCHAR2
3087 IS
3088 --similar to evaluate_child_lov_criteria except no data_entry_mode
3089 -- restriction
3090 CURSOR c IS
3091 SELECT qpr.plan_relationship_id,
3092 qpr.child_plan_id,
3093 qpr.data_entry_mode
3094 FROM qa_plans qp,
3095 qa_pc_plan_relationship qpr
3096 WHERE qpr.parent_plan_id = p_plan_id
3097 AND qpr.child_plan_id = qp.plan_id
3098 AND qpr.plan_relationship_type = 1
3099 AND ((qp.effective_to IS NULL AND TRUNC(SYSDATE) >= qp.effective_from)
3100 OR (qp.effective_from IS NULL AND TRUNC(SYSDATE) <= qp.effective_to)
3101 OR (qp.effective_from IS NOT NULL AND qp.effective_to IS NOT NULL
3102 AND TRUNC(SYSDATE) BETWEEN qp.effective_from AND qp.effective_to)
3103 OR (qp.effective_from IS NULL AND qp.effective_to IS NULL));
3104 l_separator CONSTANT VARCHAR2(1) := '@';
3105 l_subseparator CONSTANT VARCHAR2(1) := '=';
3106 l_child_plan_id NUMBER;
3107 l_data_entry_mode NUMBER;
3108 l_plan_relationship_id NUMBER;
3109 l_childexist BOOLEAN;
3110 l_return_string VARCHAR2(4000);
3111 l_elements qa_txn_grp.ElementsArray;
3112 BEGIN
3113 l_childexist := FALSE;
3114
3115 l_elements := qa_txn_grp.result_to_array(p_criteria_values);
3116 OPEN c;
3117 LOOP
3118 FETCH c INTO l_plan_relationship_id, l_child_plan_id, l_data_entry_mode;
3119 IF (c%NOTFOUND) THEN
3120 EXIT;
3121 END IF;
3122
3123 IF (qa_parent_child_pkg.criteria_matched(l_plan_relationship_id,
3124 l_elements) = 'T') THEN
3125 IF (l_childexist) THEN
3126 l_return_string := l_return_string || l_separator
3127 || l_child_plan_id || l_subseparator
3128 || l_data_entry_mode;
3129 ELSE
3130 l_return_string := l_child_plan_id || l_subseparator
3131 || l_data_entry_mode;
3132 l_childexist := TRUE;
3133 END IF;
3134 END IF;
3135 END LOOP;
3136
3137 CLOSE c;
3138 RETURN l_return_string;
3139 END;
3140
3141
3142
3143 --anagarwa
3144 -- Bug 3195431
3145 -- only copy elements are context elements. So
3146 -- element_relationship type added to the cursor
3147 FUNCTION is_context_element( p_plan_id IN NUMBER ,
3148 p_char_id IN NUMBER,
3149 p_parent_plan_id IN NUMBER,
3150 p_txn_or_child_flag IN NUMBER)
3151 RETURN VARCHAR2 IS
3152
3153 CURSOR c IS SELECT 1
3154 FROM qa_pc_result_columns_v
3155 WHERE child_plan_id = p_plan_id and
3156 child_char_id = p_char_id and
3157 parent_plan_id = p_parent_plan_id and
3158 ELEMENT_RELATIONSHIP_TYPE = 1;
3159
3160
3161 l_context VARCHAR2(1);
3162 ret_val NUMBER;
3163
3164
3165 BEGIN
3166 l_context := 'N';
3167
3168 OPEN c;
3169 FETCH c INTO ret_val;
3170 IF(c%NOTFOUND) THEN
3171 l_context := 'N';
3172 ELSIF ret_val = 1 THEN
3173 l_context := 'Y';
3174 END IF;
3175 CLOSE c;
3176
3177 RETURN l_context;
3178
3179 END is_context_element;
3180
3181
3182
3183 FUNCTION get_parent_vo_attribute_name(p_child_char_id IN NUMBER,
3184 p_plan_id IN NUMBER)
3185 RETURN VARCHAR2 IS
3186
3187 CURSOR c IS SELECT parent_char_id
3188 FROM qa_pc_result_columns_v
3189 WHERE parent_plan_id = p_plan_id and
3190 child_char_id = p_child_char_id and
3191 element_relationship_type = 1;
3192
3193 l_parent_char_id NUMBER;
3194
3195
3196 BEGIN
3197
3198 OPEN c;
3199 FETCH c INTO l_parent_char_id;
3200 IF(c%NOTFOUND) THEN
3201 CLOSE c;
3202 RETURN NULL;
3203 END IF;
3204 CLOSE c;
3205
3206 RETURN qa_ak_mapping_api.get_vo_attribute_name(l_parent_char_id,
3207 p_plan_id);
3208
3209 END get_parent_vo_attribute_name;
3210
3211 FUNCTION get_layout_mode (p_parent_plan_id IN NUMBER,
3212 p_child_plan_id IN NUMBER)
3213 RETURN NUMBER IS
3214 CURSOR c is
3215 SELECT layout_mode
3216 FROM qa_pc_plan_relationship
3217 WHERE parent_plan_id = p_parent_plan_id
3218 AND child_plan_id = p_child_plan_id;
3219
3220 l_layout_mode NUMBER := 0;
3221
3222 BEGIN
3223
3224 OPEN c;
3225 FETCH c INTO l_layout_mode;
3226 IF(c%NOTFOUND) THEN
3227 CLOSE c;
3228 RETURN -1;
3229 END IF;
3230 CLOSE c;
3231 RETURN l_layout_mode;
3232
3233 END get_layout_mode;
3234
3235 FUNCTION ssqr_post_actions(p_txn_hdr_id IN NUMBER,
3236 p_plan_id IN NUMBER,
3237 p_transaction_number IN NUMBER,
3238 x_sequence_string OUT NOCOPY VARCHAR2)
3239 RETURN VARCHAR2 IS
3240
3241 x_status VARCHAR2(10) ;
3242
3243 BEGIN
3244
3245 --initialize the sequence string to empty value
3246 x_sequence_string := '';
3247 x_status := '';
3248
3249 QA_SEQUENCE_API.generate_seq_for_DDE(p_txn_hdr_id, p_plan_id,
3250 x_status, x_sequence_string);
3251
3252
3253 -- generate sequences
3254
3255 -- call enable and fire actions
3256
3257 IF p_transaction_number > 0 THEN
3258 -- do nothing in case of transaction.
3259 RETURN x_status;
3260 ELSE
3261 enable_fire_for_txn_hdr_id(p_txn_hdr_id);
3262 RETURN x_status;
3263 END IF;
3264
3265 END;
3266 FUNCTION count_updated(p_plan_id IN NUMBER,
3267 p_txn_header_id IN NUMBER) RETURN NUMBER IS
3268 ---
3269 --- Bug 3095436: Self Service Quality project
3270 --- Simple function to count the number of rows updated in a plan
3271 --- with a particular txn_header_id
3272 --- Used by the Plan Search VO
3273 ---
3274 cnt NUMBER;
3275
3276 cursor c is
3277 select count(plan_id)
3278 from qa_results
3279 where plan_id = p_plan_id
3280 and txn_header_id = p_txn_header_id;
3281
3282 BEGIN
3283 open c;
3284 fetch c into cnt;
3285 if (c%notfound) then
3286 return 0;
3287 else
3288 return cnt;
3289 end if;
3290 close c;
3291
3292 END count_updated;
3293
3294 FUNCTION get_vud_allowed ( p_plan_id IN NUMBER)
3295 RETURN VARCHAR2 IS
3296 ---
3297 --- Bug 3095436: Self Service Quality project
3298 --- Simple function to tell if the current user has privilege to
3299 --- view, update or delete results in a particular plan
3300 --- Used by the Plan Search VO
3301 ---
3302
3303 BEGIN
3304
3305 IF (qa_web_txn_api.allowed_for_plan('QA_RESULTS_VIEW', p_plan_id) = 'T') or
3306 (qa_web_txn_api.allowed_for_plan('QA_RESULTS_DELETE', p_plan_id) = 'T') or
3307 (qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', p_plan_id) = 'T') THEN
3308 RETURN 'T';
3309 ELSE
3310 RETURN 'F';
3311 END IF;
3312
3313 END get_vud_allowed;
3314
3315 --12.1 QWB Usaibilty Improvements
3316 -- Overloaded this function so as to cause minimum
3317 -- impact to the existing code
3318 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
3319 p_parent_collection_id IN NUMBER,
3320 p_parent_occurrence IN NUMBER,
3321 p_child_plan_id IN NUMBER,
3322 p_child_collection_id IN NUMBER,
3323 p_child_occurrence IN NUMBER,
3324 p_child_txn_hdr_id IN NUMBER)
3325 RETURN VARCHAR2 IS
3326 agg_elements VARCHAR2(4000);
3327 agg_val VARCHAR2(4000);
3328 BEGIN
3329 return update_parent(
3330 p_parent_plan_id,
3331 p_parent_collection_id,
3332 p_parent_occurrence,
3333 p_child_plan_id,
3334 p_child_collection_id,
3335 p_child_occurrence,
3336 p_child_txn_hdr_id,
3337 agg_elements,
3338 agg_val);
3339 END update_parent;
3340
3341 -- anagarwa Fri Jan 23 12:10:04 PST 2004
3342 -- Bug 3384986 Actions for CAR master not fired when child is updated
3343 -- This is a copy of update_parent above with one extra param, txn_header_id
3344 -- In SSQR when we update the child record, then we call this to update parent
3345 -- and since now we would ike to fire background actions of parent, we update the
3346 -- txn_header_id too.
3347 -- NOTE: I did not modify the existing update_parent but duplicated the code
3348 -- because changing the parameters of existing procedure/function is strongly
3349 -- discouraged per Safe Spec Guide located at
3350 -- http://www-apps.us.oracle.com/%7Epwallack/SafeSpecs.htm
3351
3352 -- Bug 4343758
3353 -- R12 OAF Txn Integration Project
3354 -- Added p_commit parameter since we do not want to commit by default
3355 -- If invoked from OAF Pages.
3356 -- shkalyan 05/13/2005.
3357 FUNCTION update_parent(p_parent_plan_id IN NUMBER,
3358 p_parent_collection_id IN NUMBER,
3359 p_parent_occurrence IN NUMBER,
3360 p_child_plan_id IN NUMBER,
3361 p_child_collection_id IN NUMBER,
3362 p_child_occurrence IN NUMBER,
3363 p_child_txn_hdr_id IN NUMBER,
3364 x_agg_elements OUT NOCOPY VARCHAR2,
3365 x_agg_val OUT NOCOPY VARCHAR2)
3366 RETURN VARCHAR2 IS
3367
3368 l_sql_string VARCHAR2(32000);
3369 l_update_parent_sql VARCHAR2(32000);
3370 l_value NUMBER;
3371
3372 -- anagarwa Mon Dec 16 16:55:09 PST 2002
3373 -- Bug 2701777
3374 -- added parent_enabled_flag and child_enabled_flag to where clause
3375 -- to limit working on onlly those elements that are enabled.
3376 CURSOR element_cursor IS
3377 SELECT parent_database_column,
3378 child_database_column,
3379 element_relationship_type,
3380 parent_char_id
3381 FROM qa_pc_result_columns_v
3382 WHERE parent_plan_id = p_parent_plan_id
3383 AND child_plan_id = p_child_plan_id
3384 AND element_relationship_type in (2,3,4,5,6,7,8)
3385 AND parent_enabled_flag = 1
3386 AND child_enabled_flag = 1;
3387
3388
3389 BEGIN
3390
3391 FOR cur_rec IN element_cursor LOOP
3392
3393 -- build the required sql string
3394
3395 l_sql_string := 'FROM qa_results qr, qa_pc_results_relationship pc'
3396 || ' WHERE qr.plan_id=pc.child_plan_id'
3397 || ' AND qr.collection_id=pc.child_collection_id'
3398 || ' AND qr.occurrence=pc.child_occurrence'
3399 || ' AND pc.parent_occurrence= :p_parent_occurrence'
3400 || ' AND pc.child_plan_id= :p_child_plan_id'
3401 --
3402 -- bug 5682448
3403 -- Added the extra condititon to aggregate only the
3404 -- enabled records in stauts 2 or NULL
3405 -- ntungare Wed Feb 21 07:36:09 PST 2007
3406 --
3407 || ' AND (qr.status = 2 OR qr.status IS NULL)';
3408
3409 -- Bug 2427337. Fix here is not related this bug. To use aggregate functions
3410 -- on a element which is stored in character col in qa_results table, we need
3411 -- to use to_number function, or else, unwanted value will be returned.
3412 -- rponnusa Tue Jun 25 06:15:48 PDT 2002
3413
3414 IF (cur_rec.element_relationship_type = 2 ) THEN -- sum
3415 l_sql_string := 'SELECT SUM(to_number(qr.'||cur_rec.child_database_column||')) ' || l_sql_string;
3416 ELSIF (cur_rec.element_relationship_type = 3 ) THEN -- average or Mean
3417 l_sql_string := 'SELECT AVG(to_number(qr.'||cur_rec.child_database_column||')) ' || l_sql_string;
3418 ELSIF (cur_rec.element_relationship_type = 4 ) THEN -- std. deviation
3419 l_sql_string := 'SELECT STDDEV(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
3420
3421 ELSIF (cur_rec.element_relationship_type = 5 ) THEN -- min
3422 l_sql_string := 'SELECT MIN(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
3423 ELSIF (cur_rec.element_relationship_type = 6 ) THEN -- max
3424 l_sql_string := 'SELECT MAX(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
3425 ELSIF (cur_rec.element_relationship_type = 7 ) THEN -- variance
3426 l_sql_string := 'SELECT VARIANCE(to_number(qr.'|| cur_rec.child_database_column||')) ' || l_sql_string;
3427 ELSIF (cur_rec.element_relationship_type = 8 ) THEN -- count
3428 -- anagarwa Tue Feb 18 11:13:20 PST 2003
3429 -- Bug 2789847
3430 -- Count may be done on non numeric elements like Sequence Numbers and
3431 -- even Nonconformance Status, Source etc.
3432 -- A to_number will cause an exception in such a case and is hence
3433 -- removed from sql statement.
3434 l_sql_string := 'SELECT COUNT(qr.'|| cur_rec.child_database_column||') ' || l_sql_string;
3435 END IF;
3436 -- find out the aggregate value for the element in child plan.
3437 BEGIN
3438 EXECUTE IMMEDIATE l_sql_string INTO l_value
3439 USING p_parent_occurrence,p_child_plan_id;
3440 EXCEPTION
3441 WHEN OTHERS THEN raise;
3442
3443 END;
3444
3445 -- Bug 2716973
3446 -- When the child aggregate relationship element value is updated to parent record,
3447 -- Post-Forms-Commit Trigger error raised if child element contain null value.
3448 -- rponnusa Sun Jan 12 23:59:07 PST 2003
3449
3450 l_value := NVL(l_value,0);
3451
3452 -- See 2624112
3453 -- The maximum allowed precision is now expanded to 12.
3454 -- Rounding to 12...
3455 -- rkunchal Thu Oct 17 22:51:45 PDT 2002
3456
3457 -- rounding off to 6 digits is required since, for a number field, the maximum allowd
3458 -- decimal places is 6.
3459
3460 -- l_value := round(l_value,6);
3461 l_value := round(l_value,12);
3462
3463 -- now we need to update the parent record. Build the sql here.
3464
3465 -- Bug 4270911. CU2 SQL Literal fix.TD #19
3466 -- Use bind variable for child txn hdr id.
3467 -- srhariha. Fri Apr 15 05:55:04 PDT 2005.
3468
3469 l_update_parent_sql := 'UPDATE qa_results SET '
3470 || cur_rec.parent_database_column || ' = :l_value'
3471 || ' ,txn_header_id = :p_child_txn_hdr_id'
3472 || ' WHERE plan_id= :p_parent_plan_id'
3473 || ' AND collection_id= :p_parent_collection_id'
3474 || ' AND occurrence= :p_parent_occurrence';
3475 BEGIN
3476 EXECUTE IMMEDIATE l_update_parent_sql
3477 USING l_value,p_child_txn_hdr_id,p_parent_plan_id,p_parent_collection_id,p_parent_occurrence;
3478
3479 -- 12.1 QWB Usability improvements
3480 -- Building a list of the Aggregated parent plan elements
3481 --
3482 x_agg_elements := x_agg_elements ||','||
3483 qa_ak_mapping_api.get_vo_attribute_name(cur_rec.parent_char_id, p_parent_plan_id);
3484 -- 12.1 QWB Usability improvements
3485 -- Building a list of the Aggregated values
3486 --
3487 x_agg_val := x_agg_val ||','|| l_value;
3488 EXCEPTION
3489 WHEN OTHERS THEN raise;
3490 END;
3491
3492
3493 END LOOP;
3494 -- we are returning true when the parent record is updated or
3495 -- there is no aggregate relationship defined for the parent,child plans.
3496
3497 -- Bug 2300962. Needs explicit commit, if called from post-database-commit trigger
3498 -- bug 5306909
3499 -- Commenting this COMMIT as this Update_parent function
3500 -- with the Txn_header_id is called from QWB
3501 -- wherein the Commits are appropriately handled
3502 -- ntungare
3503 --
3504 -- COMMIT;
3505
3506 RETURN 'T';
3507
3508 END update_parent;
3509
3510 -- Bug 3536025. Adding a new procedure insert_history_auto_rec_QWB,which will be
3511 -- called from qltssreb.pls (Quality Workbench). This procedure is very much
3512 -- similar to insert_history_auto_rec ,except this procedure doesnot changes
3513 -- the child plan's txn_header_id and doesnot fire actions for child plans.
3514 -- srhariha. Wed May 26 22:31:28 PDT 2004
3515
3516 -- Bug 3681815.
3517 -- Removing the old procedure insert_history_auto_rec_QWB with the new code
3518 -- saugupta Tue, 15 Jun 2004 05:23:07 -0700 PDT
3519 --This procedure is similar to the insert_history_auto_rec but is
3520 --simplified for the needs of the SSQR post/update results processing.
3521 --The primary differences are that this procedure is limited to a single
3522 --parent row, the children have the same txn_header_id as the parent, and
3523 --the results are not enabled/don't have actions fired. This
3524 --enabling/action firing is deferred to the ssqr_post_actions() method.
3525 --Instead, this procedure checks for children using the criteria and then
3526 --passes off to insert_automatic_records to actually create the child
3527 --result rows and relationship rows.
3528 --ilawler Thu Jun 10 17:24:08 2004
3529
3530 PROCEDURE insert_history_auto_rec_QWB(p_plan_id IN NUMBER,
3531 p_collection_id IN NUMBER,
3532 p_occurrence IN NUMBER,
3533 p_organization_id IN NUMBER,
3534 p_txn_header_id IN NUMBER,
3535 p_relationship_type IN NUMBER,
3536 p_data_entry_mode IN NUMBER,
3537 x_status OUT NOCOPY VARCHAR2) IS
3538
3539 CURSOR child_check_cur(c_plan_id NUMBER) IS
3540 SELECT 'T'
3541 FROM qa_pc_plan_relationship
3542 WHERE parent_plan_id = c_plan_id
3543 AND plan_relationship_type = p_relationship_type
3544 AND data_entry_mode = p_data_entry_mode;
3545
3546 l_status VARCHAR2(1);
3547 l_criteria_values VARCHAR2(32000);
3548 l_child_plan_ids VARCHAR2(10000);
3549
3550 BEGIN
3551 --sanity check, make sure this plan has relevant children
3552 BEGIN
3553 OPEN child_check_cur(p_plan_id);
3554 FETCH child_check_cur INTO l_status;
3555 CLOSE child_check_cur;
3556 EXCEPTION
3557 WHEN OTHERS THEN
3558 l_status := 'F';
3559 END;
3560
3561 IF (l_status <> 'T' OR l_status is NULL) THEN
3562 -- no child plans with type and entry mode provided
3563 RETURN;
3564 END IF;
3565
3566 --check plan's values against child plans' criteria to get a list of
3567 --applicable children in l_child_plan_ids
3568 get_criteria_values(p_parent_plan_id => p_plan_id,
3569 p_parent_collection_id => p_collection_id,
3570 p_parent_occurrence => p_occurrence,
3571 p_organization_id => p_organization_id,
3572 x_criteria_values => l_criteria_values);
3573
3574 l_status := evaluate_criteria(p_plan_id => p_plan_id,
3575 p_criteria_values => l_criteria_values,
3576 p_relationship_type => p_relationship_type,
3577 p_data_entry_mode => p_data_entry_mode,
3578 x_child_plan_ids => l_child_plan_ids);
3579
3580 IF (l_status = 'T') THEN
3581
3582 --when evaluate_criteria returns T, we have children that need to be
3583 --created so call insert_automatic_records to do the grunt child row
3584 --creation.
3585 insert_automatic_records(p_plan_id => p_plan_id,
3586 p_collection_id => p_collection_id,
3587 p_occurrence => p_occurrence,
3588 p_child_plan_ids => l_child_plan_ids,
3589 p_relationship_type => p_relationship_type,
3590 p_data_entry_mode => p_data_entry_mode,
3591 p_criteria_values => l_criteria_values,
3592 p_org_id => p_organization_id,
3593 p_spec_id => null,
3594 x_status => l_status,
3595 p_txn_header_id => p_txn_header_id);
3596
3597 --make sure the insert_automatic succeeded
3598 IF (l_status <> 'T') THEN
3599 x_status := l_status;
3600 RETURN;
3601 END IF;
3602 END IF;
3603
3604 --don't worry about firing actions, this is handled in
3605 --ssqr_post_actions
3606
3607 x_status := 'T';
3608 RETURN;
3609
3610 END insert_history_auto_rec_QWB;
3611
3612 -- The following procedure was added to remove the entry
3613 -- from QA_PC_RESULTS_RELATIONSHIP table when the user
3614 -- deletes the record from the child plan and saves the
3615 -- child plan. This procedure is called from procedure
3616 -- key_delete_dependent_rows in QLTRES.pld.
3617 -- Bug 3646166.suramasw.
3618
3619 PROCEDURE DELETE_RELATIONSHIP_ROW(p_child_plan_id IN NUMBER,
3620 p_child_occurrence IN NUMBER) IS
3621
3622 BEGIN
3623
3624 DELETE FROM qa_pc_results_relationship
3625 WHERE child_plan_id = p_child_plan_id
3626 AND child_occurrence = p_child_occurrence;
3627
3628 END;
3629
3630 -- Bug 4343758
3631 -- R12 OAF Txn Integration Project
3632 -- Function to delete a Result Row and and it's parent child relationship
3633 -- shkalyan 05/13/2005.
3634 FUNCTION delete_row(
3635 p_plan_id IN NUMBER,
3636 p_collection_id IN NUMBER,
3637 p_occurrence IN NUMBER,
3638 p_enabled IN NUMBER := NULL) RETURN VARCHAR2
3639 IS
3640
3641 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_ROW';
3642 l_parent_plan_id NUMBER;
3643 l_parent_collection_id NUMBER;
3644 l_parent_occurrence NUMBER;
3645
3646 l_return_status VARCHAR2(1);
3647
3648 -- Bug 4343758. OA Framework Integration project.
3649 -- Cursor to fetch relationship details.
3650 -- srhariha. Tue May 24 22:56:13 PDT 2005.
3651
3652 CURSOR c1 IS
3653 SELECT parent_plan_id,
3654 parent_collection_id,
3655 parent_occurrence
3656 FROM QA_PC_RESULTS_RELATIONSHIP
3657 WHERE child_plan_id = p_plan_id
3658 AND child_collection_id = p_collection_id
3659 AND child_occurrence = p_occurrence;
3660
3661 agg_elements VARCHAR2(4000);
3662 agg_val VARCHAR2(4000);
3663
3664 BEGIN
3665
3666 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
3667 FND_LOG.string
3668 (
3669 FND_LOG.level_procedure,
3670 g_pkg_name || '.' || l_api_name,
3671 'ENTERING PROCEDURE: P_PLAN_ID: ' || p_plan_id || ' P_COLLECTION_ID: ' || p_collection_id || ' P_OCCURRENCE: ' || p_occurrence || ' P_ENABLED: ' || p_enabled
3672 );
3673 END IF;
3674
3675 DELETE QA_RESULTS
3676 WHERE occurrence = p_occurrence
3677 AND plan_id = p_plan_id
3678 AND collection_id = p_collection_id;
3679
3680 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
3681 FND_LOG.string
3682 (
3683 FND_LOG.level_statement,
3684 g_pkg_name || '.' || l_api_name,
3685 'DELETED ROW IN QA RESULTS. GETTING PARENT'
3686 );
3687 END IF;
3688
3689 -- Bug 4343758. Oa Framework Integration project.
3690 -- Use cursor to fetch relationship details.
3691 -- srhariha. Tue May 24 22:56:13 PDT 2005.
3692 l_parent_plan_id := null;
3693
3694 OPEN C1;
3695 FETCH C1 INTO l_parent_plan_id,l_parent_collection_id,l_parent_occurrence;
3696 CLOSE C1;
3697
3698 IF ( l_parent_plan_id IS NOT NULL ) THEN
3699 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
3700 FND_LOG.string
3701 (
3702 FND_LOG.level_statement,
3703 g_pkg_name || '.' || l_api_name,
3704 'BEFORE DELETING RELATIONSHIP ROW FOR CHILD'
3705 );
3706 END IF;
3707
3708 delete_relationship_row
3709 (
3710 p_child_plan_id => p_plan_id,
3711 p_child_occurrence => p_occurrence
3712 );
3713
3714 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
3715 FND_LOG.string
3716 (
3717 FND_LOG.level_statement,
3718 g_pkg_name || '.' || l_api_name,
3719 'BEFORE UPDATING PARENT FOR AGGREGATION PLAN_ID: ' || l_parent_plan_id || ' COLLECTION_ID: ' || l_parent_collection_id || ' OCCURRENCE: ' || l_parent_occurrence || ' FOR AGGREGATION '
3720 );
3721 END IF;
3722
3723 -- 12.1 QWB Usability Improvements
3724 -- Added 2 new parameters to get a list of Aggregated elements
3725 -- and their values
3726 --
3727 l_return_status :=
3728 aggregate_parent
3729 (
3730 p_parent_plan_id => l_parent_plan_id,
3731 p_parent_collection_id => l_parent_collection_id,
3732 p_parent_occurrence => l_parent_occurrence,
3733 p_child_plan_id => p_plan_id,
3734 p_child_collection_id => TO_NUMBER( NULL ),
3735 p_child_occurrence => TO_NUMBER( NULL ),
3736 p_commit => 'F',
3737 x_agg_elements => agg_elements,
3738 x_agg_val => agg_val
3739 );
3740 END IF;
3741
3742 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
3743 FND_LOG.string
3744 (
3745 FND_LOG.level_procedure,
3746 g_pkg_name || '.' || l_api_name,
3747 'EXITING PROCEDURE: SUCCESS'
3748 );
3749 END IF;
3750
3751 RETURN 'T';
3752
3753 END delete_row;
3754
3755
3756 -- Bug 4345779. Audits Copy UI project.
3757 -- Code Review feedback incorporation. CR Ref 4.9.5, 4.9.6 and 4.9.7
3758 -- Modularization. Parent child API's must be defined in parent pkg.
3759 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
3760
3761 --
3762 -- Parent-Child collections API. Operaters on collection of records.
3763 --
3764
3765
3766 -- Bug 4345779. Audits Copy UI project.
3767 -- Code Review feedback incorporation. CR Ref 4.10.1
3768 -- Using static SQL.
3769 -- srhariha. Thu Sep 29 00:09:40 PDT 2005.
3770
3771 PROCEDURE create_relationship_for_coll
3772 ( p_parent_plan_id NUMBER,
3773 p_parent_collection_id NUMBER,
3774 p_parent_occurrence NUMBER,
3775 p_child_plan_id NUMBER,
3776 p_child_collection_id NUMBER,
3777 p_org_id NUMBER) IS
3778
3779 l_sql_string VARCHAR2(1000);
3780
3781 -- Bug 4345779. Audits Copy UI project.
3782 -- Code Review feedback incorporation. CR Ref 4.9.1
3783 -- l_api_name must be declared as constant.
3784 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
3785
3786 l_api_name CONSTANT VARCHAR2(40) := 'CREATE_RELATIONSHIP ()';
3787
3788 BEGIN
3789 INSERT INTO QA_PC_RESULTS_RELATIONSHIP (PARENT_PLAN_ID,
3790 PARENT_COLLECTION_ID,
3791 PARENT_OCCURRENCE,
3792 CHILD_PLAN_ID,
3793 CHILD_COLLECTION_ID,
3794 CHILD_OCCURRENCE,
3795 ENABLED_FLAG,
3796 LAST_UPDATE_DATE,
3797 LAST_UPDATED_BY,
3798 CREATION_DATE,
3799 CREATED_BY,
3800 LAST_UPDATE_LOGIN,
3801 CHILD_TXN_HEADER_ID)
3802 SELECT p_parent_plan_id,
3803 p_parent_collection_id,
3804 p_parent_occurrence,
3805 QR.PLAN_ID,
3806 QR.COLLECTION_ID,
3807 QR.OCCURRENCE,
3808 2,
3809 SYSDATE,
3810 FND_GLOBAL.USER_ID,
3811 SYSDATE,
3812 FND_GLOBAL.USER_ID,
3813 FND_GLOBAL.USER_ID,
3814 QR.TXN_HEADER_ID
3815 FROM QA_RESULTS QR
3816 WHERE QR.PLAN_ID = p_child_plan_id
3817 AND QR.COLLECTION_ID = p_child_collection_id
3818 AND QR.ORGANIZATION_ID = p_org_id;
3819
3820
3821 EXCEPTION
3822
3823 WHEN OTHERS THEN
3824 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
3825 FND_MSG_PUB.Add_Exc_Msg
3826 (
3827 p_pkg_name => g_pkg_name,
3828 p_procedure_name => l_api_name,
3829 p_error_text => SUBSTR(SQLERRM,1,240)
3830 );
3831 END IF;
3832
3833 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
3834 FND_LOG.string
3835 (
3836 FND_LOG.level_procedure,
3837 g_pkg_name || '.' || l_api_name,
3838 'EXITING PROCEDURE: ERROR'
3839 );
3840 END IF;
3841
3842 RAISE;
3843
3844 END create_relationship_for_coll;
3845
3846
3847 PROCEDURE get_copy_result_cols(p_parent_plan_id NUMBER,
3848 p_child_plan_id NUMBER,
3849 x_parent_rc_str OUT NOCOPY VARCHAR2,
3850 x_child_rc_str OUT NOCOPY VARCHAR2 ) IS
3851
3852 CURSOR C IS
3853 SELECT qprc.parent_database_column,
3854 qprc.child_database_column
3855 FROM qa_pc_result_columns_v qprc
3856 WHERE qprc.parent_plan_id = p_parent_plan_id and
3857 qprc.child_plan_id = p_child_plan_id and
3858 qprc.element_relationship_type = 1 and
3859 parent_enabled_flag = 1 and
3860 child_enabled_flag = 1;
3861
3862 p_rc DBMS_SQL.VARCHAR2_TABLE;
3863 c_rc DBMS_SQL.VARCHAR2_TABLE;
3864
3865
3866 BEGIN
3867
3868 OPEN C;
3869 FETCH C BULK COLLECT INTO p_rc,c_rc;
3870 CLOSE C;
3871
3872 if(p_rc is null OR c_rc is null) then
3873 return;
3874 end if;
3875
3876
3877 FOR i IN p_rc.FIRST .. p_rc.LAST LOOP
3878 x_parent_rc_str := x_parent_rc_str || p_rc(i);
3879 x_child_rc_str := x_child_rc_str || c_rc(i);
3880
3881 IF (i <> p_rc.LAST) THEN
3882 x_parent_rc_str := x_parent_rc_str || ', ';
3883 x_child_rc_str := x_child_rc_str || ', ';
3884 END IF;
3885
3886
3887 END LOOP;
3888
3889
3890
3891 END get_copy_result_cols;
3892
3893 PROCEDURE copy_from_parent_for_coll
3894 (p_parent_plan_id NUMBER,
3895 p_parent_collection_id NUMBER,
3896 p_parent_occurrence NUMBER,
3897 p_child_plan_id NUMBER,
3898 p_child_collection_id NUMBER,
3899 p_org_id NUMBER) IS
3900
3901 l_sql_string VARCHAR2(32000);
3902 l_src_string VARCHAR2(32000);
3903 l_dest_string VARCHAR2(32000);
3904 -- Bug 4345779. Audits Copy UI project.
3905 -- Code Review feedback incorporation. CR Ref 4.9.1
3906 -- l_api_name must be declared as constant.
3907 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
3908
3909 l_api_name CONSTANT VARCHAR2(40) := 'COPY_FROM_PARENT_FOR_COLL';
3910 BEGIN
3911
3912 -- get parent and child result column names
3913 get_copy_result_cols(p_parent_plan_id => p_parent_plan_id,
3914 p_child_plan_id => p_child_plan_id,
3915 x_parent_rc_str => l_src_string,
3916 x_child_rc_str => l_dest_string);
3917
3918 l_sql_string := ' UPDATE QA_RESULTS ' ||
3919 ' SET ( ' || l_dest_string || ' ) = ' ||
3920 ' ( SELECT ' || l_src_string || ' ' ||
3921 ' FROM QA_RESULTS QR1 ' ||
3922 ' WHERE QR1.PLAN_ID = :1 ' ||
3923 ' AND QR1.COLLECTION_ID = :2' ||
3924 ' AND QR1.OCCURRENCE = :3) ' ||
3925 ' WHERE PLAN_ID = :4 ' ||
3926 ' AND COLLECTION_ID = :5 ';
3927
3928 EXECUTE IMMEDIATE l_sql_string USING p_parent_plan_id,
3929 p_parent_collection_id,
3930 p_parent_occurrence,
3931 p_child_plan_id,
3932 p_child_collection_id;
3933
3934 EXCEPTION
3935
3936 WHEN OTHERS THEN
3937 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
3938 FND_MSG_PUB.Add_Exc_Msg
3939 (
3940 p_pkg_name => g_pkg_name,
3941 p_procedure_name => l_api_name,
3942 p_error_text => SUBSTR(SQLERRM,1,240)
3943 );
3944 END IF;
3945
3946 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
3947 FND_LOG.string
3948 (
3949 FND_LOG.level_procedure,
3950 g_pkg_name || '.' || l_api_name,
3951 'EXITING PROCEDURE: ERROR'
3952 );
3953 END IF;
3954
3955 RAISE;
3956 END copy_from_parent_for_coll;
3957
3958 -- Bug 4345779. Audits Copy UI project.
3959 -- Code Review feedback incorporation. CR Ref 4.10.2
3960 -- Rewriting the logic based on new interesting algo
3961 -- suggested by Bryan.
3962 -- srhariha. Thu Sep 29 00:09:40 PDT 2005.
3963
3964 PROCEDURE create_history_for_coll (
3965 p_plan_id NUMBER,
3966 p_collection_id NUMBER,
3967 p_org_id NUMBER,
3968 p_txn_header_id NUMBER) IS
3969
3970 l_sql_string VARCHAR2(32000);
3971
3972
3973 CURSOR c(x_plan_id NUMBER) IS
3974 SELECT child_plan_id
3975 FROM qa_pc_plan_relationship
3976 WHERE parent_plan_id = x_plan_id
3977 AND data_entry_mode = 4;
3978
3979 l_src_string VARCHAR2(32000);
3980 l_dest_string VARCHAR2(32000);
3981 -- Bug 4345779. Audits Copy UI project.
3982 -- Code Review feedback incorporation. CR Ref 4.9.1
3983 -- l_api_name must be declared as constant.
3984 -- srhariha. Tue Jul 12 02:12:17 PDT 2005.
3985
3986 l_api_name CONSTANT VARCHAR2(40) := 'CREATE_HISTORY';
3987
3988 BEGIN
3989
3990 -- get history plan id
3991
3992 FOR hst_rec IN c(p_plan_id) LOOP
3993
3994 INSERT INTO QA_PC_RESULTS_RELATIONSHIP (
3995 PARENT_PLAN_ID,
3996 PARENT_COLLECTION_ID,
3997 PARENT_OCCURRENCE,
3998 CHILD_PLAN_ID ,
3999 CHILD_COLLECTION_ID,
4000 CHILD_OCCURRENCE,
4001 ENABLED_FLAG,
4002 LAST_UPDATE_DATE,
4003 LAST_UPDATED_BY,
4004 CREATION_DATE,
4005 CREATED_BY ,
4006 LAST_UPDATE_LOGIN,
4007 CHILD_TXN_HEADER_ID)
4008 SELECT QR.PLAN_ID,
4009 QR.COLLECTION_ID,
4010 QR.OCCURRENCE,
4011 hst_rec.child_plan_id,
4012 p_collection_id,
4013 QA_OCCURRENCE_S.NEXTVAL,
4014 2,
4015 SYSDATE,
4016 FND_GLOBAL.USER_ID,
4017 SYSDATE,
4018 FND_GLOBAL.USER_ID,
4019 FND_GLOBAL.USER_ID,
4020 p_txn_header_id
4021 FROM QA_RESULTS QR
4022 WHERE QR.PLAN_ID = p_plan_id
4023 AND QR.COLLECTION_ID = p_collection_id
4024 AND QR.ORGANIZATION_ID = p_org_id;
4025
4026
4027
4028 -- get parent and child result column names
4029 get_copy_result_cols(p_parent_plan_id => p_plan_id,
4030 p_child_plan_id => hst_rec.child_plan_id,
4031 x_parent_rc_str => l_src_string,
4032 x_child_rc_str => l_dest_string);
4033
4034
4035 l_sql_string := ' INSERT INTO qa_results ( collection_id, ' ||
4036 ' occurrence, ' ||
4037 ' last_update_date, ' ||
4038 ' qa_last_update_date, '||
4039 ' last_updated_by, ' ||
4040 ' qa_last_updated_by, ' ||
4041 ' creation_date, ' ||
4042 ' qa_creation_date, ' ||
4043 ' created_by, ' ||
4044 ' last_update_login, ' ||
4045 ' qa_created_by, ' ||
4046 ' status, ' ||
4047 ' transaction_number, ' ||
4048 ' organization_id, ' ||
4049 ' plan_id, ' ||
4050 ' txn_header_id, ' ||
4051 l_dest_string || ')' ||
4052 ' SELECT QPRR.CHILD_COLLECTION_ID, ' ||
4053 ' QPRR.CHILD_OCCURRENCE, ' ||
4054 ' sysdate, ' ||
4055 ' sysdate, ' ||
4056 ' fnd_global.user_id, ' ||
4057 ' fnd_global.user_id, ' ||
4058 ' sysdate, ' ||
4059 ' sysdate, ' ||
4060 ' fnd_global.user_id, ' ||
4061 ' fnd_global.user_id, ' ||
4062 ' fnd_global.user_id, ' ||
4063 ' 2, ' ||
4064 ' -1, ' ||
4065 ' QR.ORGANIZATION_ID, ' ||
4066 ' QPRR.CHILD_PLAN_ID, ' ||
4067 ' QPRR.CHILD_TXN_HEADER_ID, ' ||
4068 l_src_string || ' ' ||
4069 ' FROM QA_RESULTS QR, QA_PC_RESULTS_RELATIONSHIP QPRR ' ||
4070 ' WHERE QPRR.CHILD_PLAN_ID = :1 ' ||
4071 ' AND QPRR.CHILD_COLLECTION_ID = :2 ' ||
4072 ' AND QPRR.PARENT_PLAN_ID = :3 ' ||
4073 ' AND QPRR.PARENT_COLLECTION_ID = :4 ' ||
4074 ' AND QPRR.PARENT_OCCURRENCE = QR.OCCURRENCE ';
4075
4076 EXECUTE IMMEDIATE l_sql_string USING hst_rec.child_plan_id,
4077 p_collection_id,
4078 p_plan_id,
4079 p_collection_id;
4080
4081
4082
4083 END LOOP; -- hst_rec
4084
4085 EXCEPTION
4086
4087 WHEN OTHERS THEN
4088 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
4089 FND_MSG_PUB.Add_Exc_Msg
4090 (
4091 p_pkg_name => g_pkg_name,
4092 p_procedure_name => l_api_name,
4093 p_error_text => SUBSTR(SQLERRM,1,240)
4094 );
4095 END IF;
4096
4097 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
4098 FND_LOG.string
4099 (
4100 FND_LOG.level_procedure,
4101 g_pkg_name || '.' || l_api_name,
4102 'EXITING PROCEDURE: ERROR'
4103 );
4104 END IF;
4105
4106 RAISE;
4107
4108
4109 END create_history_for_coll;
4110
4111
4112 -- Bug 4502450. R12 Esig Status support in Multirow UQR
4113 -- saugupta Wed, 24 Aug 2005 08:40:56 -0700 PDT
4114
4115 -- Function returns only all ancestors i.e parent and grandparent plan rows
4116 -- for a given child plan and does not retrun child itself.
4117 FUNCTION get_ancestors( p_child_plan_id IN NUMBER,
4118 p_child_occurrence IN NUMBER,
4119 p_child_collection_id IN NUMBER,
4120 x_parent_plan_ids OUT NOCOPY dbms_sql.number_table,
4121 x_parent_collection_ids OUT NOCOPY dbms_sql.number_table,
4122 x_parent_occurrences OUT NOCOPY dbms_sql.number_table)
4123 RETURN VARCHAR2
4124 IS
4125
4126 BEGIN
4127 -- check for NULL values
4128 IF( p_child_plan_id IS NULL OR
4129 p_child_occurrence IS NULL OR
4130 p_child_collection_id IS NULL) THEN
4131 -- return False
4132 RETURN 'F';
4133 END IF;
4134
4135 -- Given a child occurrence this query finds all the parents
4136 -- and grandparent records, therefore ancestors, of the child record.
4137 -- These are returned in the three output PL/SQL tables.
4138 -- The child record itself is not included in the output.
4139 SELECT parent_plan_id, parent_collection_id, parent_occurrence
4140 BULK COLLECT INTO x_parent_plan_ids, x_parent_collection_ids, x_parent_occurrences
4141 FROM qa_pc_results_relationship
4142 START WITH child_plan_id = p_child_plan_id
4143 AND child_occurrence = p_child_occurrence
4144 AND child_collection_id = p_child_collection_id
4145 CONNECT BY PRIOR parent_occurrence = child_occurrence;
4146
4147 IF (SQL%FOUND) THEN
4148 RETURN 'T';
4149 ELSE
4150 RETURN 'F';
4151 END IF;
4152
4153
4154 END get_ancestors;
4155
4156 --
4157 -- Bug 5435657
4158 -- New procedure to update the Aggregate values on
4159 -- all the ancestors of the plan_id passed, in case
4160 -- such a P-C relationship exists
4161 -- ntungare Wed Aug 2 20:53:40 PDT 2006
4162 --
4163 PROCEDURE update_all_ancestors(p_parent_plan_id IN NUMBER,
4164 p_parent_collection_id IN NUMBER,
4165 p_parent_occurrence IN NUMBER) IS
4166
4167 l_parent_plan_id_tab DBMS_SQL.NUMBER_TABLE;
4168 l_parent_collection_id_tab DBMS_SQL.NUMBER_TABLE;
4169 l_parent_occurrence_tab DBMS_SQL.NUMBER_TABLE;
4170
4171 l_current_child_planid NUMBER;
4172 l_current_child_collid NUMBER;
4173 l_current_child_occrid NUMBER;
4174 l_current_parent_planid NUMBER;
4175 l_current_parent_collid NUMBER;
4176 l_current_parent_occrid NUMBER;
4177
4178 BEGIN
4179 -- Calling the function get_ancestors to get a
4180 -- List of the Ancestors if they exist
4181 IF ( QA_PARENT_CHILD_PKG.get_ancestors(
4182 p_parent_plan_id,
4183 p_parent_occurrence,
4184 p_parent_collection_id,
4185 l_parent_plan_id_tab,
4186 l_parent_collection_id_tab,
4187 l_parent_occurrence_tab) = 'T') THEN
4188
4189 l_current_child_planid := p_parent_plan_id;
4190 l_current_child_collid := p_parent_collection_id;
4191 l_current_child_occrid := p_parent_occurrence;
4192
4193 -- Ancestors exist for the plan_id passed so
4194 -- Need to check if an aggregate P-C relationship
4195 -- exists and do the aggregation
4196 -- Looping through all the ancestors
4197 For ancestors_cntr in 1..l_parent_plan_id_tab.COUNT
4198 LOOP
4199 l_current_parent_planid := l_parent_plan_id_tab(ancestors_cntr);
4200 l_current_parent_collid := l_parent_collection_id_tab(ancestors_cntr);
4201 l_current_parent_occrid := l_parent_occurrence_tab(ancestors_cntr);
4202
4203 -- Calling the procedure to check for aggregate relationships
4204 -- and do the agrregation
4205 IF(QA_PARENT_CHILD_PKG.update_parent
4206 (l_current_parent_planid,
4207 l_current_parent_collid,
4208 l_current_parent_occrid,
4209 l_current_child_planid,
4210 l_current_child_collid,
4211 l_current_child_occrid)='T')
4212 THEN
4213 NULL;
4214 END IF;
4215
4216 -- Assigning the Current Parrent plan Id, Collection Id
4217 -- and the occurrences as the Child plan Plan id,
4218 -- Collcetion Id and occurrences, for the next round of
4219 -- processing of the ancestors collection
4220 --
4221 l_current_child_planid := l_current_parent_planid;
4222 l_current_child_collid := l_current_parent_collid;
4223 l_current_child_occrid := l_current_parent_occrid;
4224 END LOOP; -- End of Ancestors Loop
4225
4226 END If; -- End of If ancestors Found
4227 END update_all_ancestors;
4228
4229 --
4230 -- bug 6134920
4231 -- Added a new procedure to delete all the status
4232 -- 1 invalid child records, generated during an
4233 -- incomplete txn
4234 -- ntungare Tue Jul 10 23:08:22 PDT 2007
4235 --
4236 PROCEDURE delete_invalid_children(p_txn_header_id IN NUMBER) IS
4237 PRAGMA AUTONOMOUS_TRANSACTION;
4238
4239 TYPE child_plan_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_plan_id%TYPE
4240 INDEX BY BINARY_INTEGER;
4241 TYPE child_collection_id_tab_typ IS TABLE OF qa_pc_results_relationship.child_collection_id%TYPE
4242 INDEX BY BINARY_INTEGER;
4243 TYPE child_occurrence_tab_typ IS TABLE OF qa_pc_results_relationship.child_occurrence%TYPE
4244 INDEX BY BINARY_INTEGER;
4245
4246 child_plan_id_tab child_plan_id_tab_typ;
4247 child_collection_id_tab child_collection_id_tab_typ;
4248 child_occurrence_tab child_occurrence_tab_typ;
4249
4250 BEGIN
4251 DELETE FROM qa_results
4252 WHERE txn_header_id = p_txn_header_id
4253 AND status = 1
4254 RETURNING plan_id, collection_id, occurrence
4255 BULK COLLECT INTO child_plan_id_tab,
4256 child_collection_id_tab,
4257 child_occurrence_tab;
4258
4259 FORALL cntr in 1..child_plan_id_tab.COUNT
4260 DELETE from qa_pc_results_relationship
4261 WHERE child_txn_header_id = p_txn_header_id
4262 AND child_plan_id = child_plan_id_tab(cntr)
4263 AND child_collection_id = child_collection_id_tab(cntr)
4264 AND child_occurrence = child_occurrence_tab(cntr);
4265
4266 COMMIT;
4267 END delete_invalid_children;
4268
4269 -- 12.1 QWB Usability Improvements
4270 -- New method to check if a Parent Plan record
4271 -- has any applicable child plan into which data can be
4272 -- entered.
4273 --
4274 FUNCTION has_enterable_child(p_plan_id in number,
4275 p_collection_id in number,
4276 p_occurrence in number)
4277 RETURN varchar2 as
4278 TYPE plan_det IS RECORD (char_id varchar2(200),
4279 res_col_name varchar2(200)) ;
4280
4281 TYPE res_col_tab_typ IS TABLE OF plan_det INDEX BY binary_integer;
4282 res_col_tab res_col_tab_typ;
4283 str varchar2(32767);
4284 result_string varchar2(32767);
4285
4286 plans qa_txn_grp.ElementsArray;
4287
4288 cntr NUMBER;
4289 BEGIN
4290 -- Getting the list of the result_column_names from the
4291 -- qa_plan_chars table
4292 SELECT char_id, result_column_name
4293 BULK COLLECT INTO res_col_tab
4294 FROM qa_plan_chars
4295 WHERE plan_id = p_plan_id;
4296
4297 -- building the select query
4298 FOR cntr in 1..res_col_tab.count
4299 LOOP
4300 str := res_col_tab(cntr).char_id ||
4301 '=''||REPLACE('||res_col_tab(cntr).res_col_name||
4302 ',''@'',''@@'')||''@'||str;
4303 END LOOP;
4304 str := rtrim(str, '||''@');
4305
4306 -- Use the columns list built above to query
4307 -- qa_results table, to build the result_string
4308 EXECUTE IMMEDIATE
4309 'Select '''||str||
4310 ' from qa_results where plan_id = :plan_id and
4311 collection_id = :collection_id and
4312 occurrence = :occurrence'
4313 INTO result_string USING p_plan_id,
4314 p_collection_id ,
4315 p_occurrence;
4316
4317 -- Pass the result string to the applicable_child_plans
4318 -- to get a list of applicable Child plans for the entered data
4319 -- and convert the list of plans returned as a string
4320 -- into an array
4321 plans := qa_txn_grp.result_to_array(
4322 qa_parent_child_pkg.applicable_child_plans(p_plan_id,
4323 result_string));
4324
4325 cntr := plans.first;
4326
4327 -- looping through the child plans list to check
4328 -- if there is any non History child plan
4329 WHILE cntr <= plans.LAST
4330 LOOP
4331 IF plans(cntr).VALUE <>4
4332 THEN RETURN 'CHILD_Y';
4333 END IF;
4334 cntr := plans.next(cntr);
4335 END LOOP;
4336 RETURN 'CHILD_N';
4337 END has_enterable_child;
4338
4339 -- 12.1 QWB Usability Improvements
4340 -- New method to check if there aare any updatable child records
4341 --
4342 FUNCTION child_exists_for_update(p_plan_id IN NUMBER,
4343 p_collection_id IN NUMBER,
4344 p_occurrence IN NUMBER)
4345 RETURN VARCHAR2 AS
4346 --
4347 -- removed the Immediate plans check
4348 -- ntungare
4349 --
4350 CURSOR cur is
4351 select 'UPDATE_CHILD_Y'
4352 from qa_pc_results_relationship qpc,
4353 qa_results qr,
4354 qa_pc_plan_relationship qpr
4355 where qpc.parent_plan_id = p_plan_id and
4356 qpc.parent_collection_id = p_collection_id and
4357 qpc.parent_occurrence = p_occurrence and
4358 qpc.child_plan_id = qr.plan_id and
4359 qpc.child_collection_id = qr.collection_id and
4360 qpc.child_occurrence = qr.occurrence and
4361 (qr.status = 2 or qr.status is NULL) and
4362 qpr.parent_plan_id = p_plan_id and
4363 qpr.child_plan_id = qpc.child_plan_id and
4364 qpr.data_entry_mode <> 4 and
4365 qa_web_txn_api.allowed_for_plan('QA_RESULTS_UPDATE', qpc.child_plan_id) = 'T';
4366 --rownum =1;
4367
4368 has_child VARCHAR2(100) :='UPDATE_CHILD_N';
4369 BEGIN
4370 /*
4371 This procedure has a bit of a complexity in the form that if the
4372 Criteria defined for the P-C relationship is changed later, then
4373 the child data that has already been collected would be no longer
4374 be applicable, in which case the child records though present in
4375 the relationship table should be ignored. For this we need to
4376 make a call to the "has_enterable" procedure to check for the
4377 applicable children, which would be a severe overhead. A better
4378 way is to prevent the user from changing the criteria if Child
4379 records that match the criteria have already been collected.
4380 */
4381 OPEN cur;
4382 FETCH cur INTO has_child;
4383 CLOSE cur;
4384
4385 RETURN has_child;
4386 END child_exists_for_update;
4387
4388 -- 12.1 QWB usability Improvements
4389 -- New method to get a count of child records
4390 -- present for any parent plan record
4391 --
4392 FUNCTION getChildCount(p_plan_id IN NUMBER,
4393 p_collection_id IN NUMBER,
4394 p_occurrence IN NUMBER)
4395 RETURN NUMBER AS
4396
4397 childCount NUMBER := 0;
4398 BEGIN
4399 SELECT count(*) INTO childCount
4400 FROM qa_pc_results_relationship qpc,
4401 qa_results qr
4402 WHERE qpc.parent_plan_id = p_plan_id and
4403 qpc.parent_collection_id = p_collection_id and
4404 qpc.parent_occurrence = p_occurrence and
4405 qpc.child_plan_id = qr.plan_id and
4406 qpc.child_collection_id = qr.collection_id and
4407 qpc.child_occurrence = qr.occurrence and
4408 (qr.status = 2 or qr.status is NULL);
4409
4410 RETURN childCount;
4411 end getChildCount;
4412
4413 -- 12.1 Quality Inline Transaction INtegration
4414 -- New method to identify whether a plan has
4415 -- child plans associated with it or not
4416 --
4417 FUNCTION has_child(p_plan_id IN NUMBER)
4418 RETURN INTEGER AS
4419
4420 childCount NUMBER;
4421 BEGIN
4422 SELECT count(*) INTO childCount
4423 FROM qa_pc_plan_relationship
4424 WHERE parent_plan_id=p_plan_id;
4425 IF childCount > 0 THEN
4426 RETURN 1;
4427 ELSE
4428 RETURN 2;
4429 END IF;
4430 END has_child;
4431
4432 -- 12.1 QWB Usability Improvements project
4433 -- Function to update all the History
4434 -- Child records corresponding to a parent record
4435 FUNCTION update_hist_children(p_parent_plan_id IN NUMBER,
4436 p_parent_collection_id IN NUMBER,
4437 p_parent_occurrence IN NUMBER)
4438 RETURN VARCHAR2 IS
4439
4440 l_return_value VARCHAR2(1);
4441 l_dummy VARCHAR2(1);
4442
4443 CURSOR children_cur IS
4444 select qprr.child_plan_id,
4445 qprr.child_collection_id,
4446 qprr.child_occurrence
4447 from qa_pc_results_relationship qprr,
4448 qa_pc_plan_relationship qpr
4449 where qprr.parent_occurrence = p_parent_occurrence
4450 and qprr.parent_plan_id = p_parent_plan_id
4451 and qprr.parent_collection_id = p_parent_collection_id
4452 and qpr.parent_plan_id = qprr.parent_plan_id
4453 and qpr.child_plan_id = qprr.child_plan_id
4454 and qpr.data_entry_mode = 4;
4455
4456 BEGIN
4457 l_return_value := 'T';
4458 l_dummy := 'T';
4459
4460 FOR children_rec IN children_cur
4461 LOOP
4462 l_return_value :=
4463 update_child ( p_parent_plan_id,
4464 p_parent_collection_id,
4465 p_parent_occurrence,
4466 children_rec.child_plan_id,
4467 children_rec.child_collection_id,
4468 children_rec.child_occurrence);
4469 END LOOP;
4470
4471 RETURN l_return_value;
4472 END update_hist_children;
4473
4474 -- Bug 7436465.FP for Bug 7035041.pdube Fri Sep 26 03:46:20 PDT 2008
4475 -- Inroduced this procedure to check if any child record exists for parent record.
4476 FUNCTION IF_CHILD_RECORD_EXISTS( p_plan_id IN NUMBER,
4477 p_collection_id IN NUMBER,
4478 p_occurrence IN NUMBER) RETURN result_column_name_tab_typ IS
4479 result_column_name_tab result_column_name_tab_typ;
4480 BEGIN
4481 SELECT REPLACE(DECODE(QC.HARDCODED_COLUMN, NULL ,QAPC.RESULT_COLUMN_NAME,QC.DEVELOPER_NAME),
4482 'CHARACTER','DISPLAY') FORM_FIELD
4483 BULK COLLECT INTO result_column_name_tab
4484 FROM qa_pc_plan_relationship qppr,
4485 qa_pc_criteria qpc,
4486 qa_results qr,
4487 qa_plan_chars qapc,
4488 qa_chars qc
4489 WHERE qpc.plan_relationship_id = qppr.plan_relationship_id
4490 AND qapc.char_id = qpc.char_id
4491 AND qapc.char_id = qc.char_id
4492 AND qr.occurrence = p_occurrence
4493 AND qr.collection_id = p_collection_id
4494 AND qr.plan_id = p_plan_id
4495 AND qr.plan_id = qapc.plan_id
4496 AND qppr.parent_plan_id = qr.plan_id
4497 AND EXISTS
4498 (SELECT 1 FROM
4499 qa_pc_results_relationship qprr
4500 WHERE qppr.child_plan_id = qprr.child_plan_id
4501 AND qppr.parent_plan_id = qprr.parent_plan_id
4502 AND qppr.child_plan_id = qprr.child_plan_id
4503 AND qprr.parent_plan_id = qr.plan_id
4504 AND qprr.parent_collection_id = p_collection_id
4505 AND qprr.parent_occurrence = p_occurrence
4506 AND qprr.parent_plan_id = p_plan_id
4507 AND ROWNUM = 1);
4508
4509 RETURN result_column_name_tab;
4510 EXCEPTION
4511 WHEN OTHERS THEN
4512 RAISE;
4513 RETURN result_column_name_tab;
4514 END IF_CHILD_RECORD_EXISTS;
4515
4516 END QA_PARENT_CHILD_PKG;
[Home] [Help]