1 PACKAGE BODY qa_ss_results AS
2 /* $Header: qltssreb.plb 120.25.12010000.1 2008/07/25 09:22:49 appldev ship $ */
3
4
5 g_message_table mesg_table;
6
7
8 PROCEDURE populate_message_table IS
9
10 BEGIN
11
12 g_message_table(qa_validation_api.not_enabled_error) :=
13 'QA_API_NOT_ENABLED';
14 g_message_table(qa_validation_api.no_values_error) := 'QA_API_NO_VALUES';
15 g_message_table(qa_validation_api.mandatory_error) := 'QA_API_MANDATORY';
16 g_message_table(qa_validation_api.not_revision_controlled_error) :=
17 'QA_API_REVISION_CONTROLLED';
18 g_message_table(qa_validation_api.mandatory_revision_error) :=
19 'QA_API_MANDATORY_REVISION';
20 g_message_table(qa_validation_api.no_values_error) := 'QA_API_NO_VALUES';
21 g_message_table(qa_validation_api.keyflex_error) := 'QA_API_KEYFLEX';
22 g_message_table(qa_validation_api.id_not_found_error) :=
23 'QA_API_ID_NOT_FOUND';
24 g_message_table(qa_validation_api.spec_limit_error) := 'QA_API_SPEC_LIMIT';
25 g_message_table(qa_validation_api.immediate_action_error) :=
26 'QA_API_IMMEDIATE_ACTION';
27 g_message_table(qa_validation_api.lower_limit_error) :=
28 'QA_API_LOWER_LIMIT';
29 g_message_table(qa_validation_api.upper_limit_error) :=
30 'QA_API_UPPER_LIMIT';
31 g_message_table(qa_validation_api.value_not_in_sql_error) :=
32 'QA_API_VALUE_NOT_IN_SQL';
33 g_message_table(qa_validation_api.sql_validation_error) :=
34 'QA_API_SQL_VALIDATION';
35 g_message_table(qa_validation_api.date_conversion_error) :=
36 'QA_API_INVALID_DATE';
37 g_message_table(qa_validation_api.data_type_error) := 'QA_API_DATA_TYPE';
38 g_message_table(qa_validation_api.number_conversion_error) :=
39 'QA_API_INVALID_NUMBER';
40 g_message_table(qa_validation_api.no_data_found_error) :=
41 'QA_API_NO_DATA_FOUND';
42 g_message_table(qa_validation_api.not_locator_controlled_error) :=
43 'QA_API_NOT_LOCATOR_CONTROLLED';
44 g_message_table(qa_validation_api.item_keyflex_error) :=
45 'QA_API_ITEM_KEYFLEX';
46 g_message_table(qa_validation_api.comp_item_keyflex_error) :=
47 'QA_API_COMP_ITEM_KEYFLEX';
48 g_message_table(qa_validation_api.comp_locator_keyflex_error) :=
49 'QA_API_COMP_LOCATOR_KEYFLEX';
50 g_message_table(qa_validation_api.invalid_number_error) :=
51 'QA_API_INVALID_NUMBER';
52 g_message_table(qa_validation_api.invalid_date_error) :=
53 'QA_API_INVALID_DATE';
54 g_message_table(qa_validation_api.spec_error) := 'QA_API_SPEC';
55 g_message_table(qa_validation_api.ok) := 'QA_API_NO_ERROR';
56 g_message_table(qa_validation_api.unknown_error) := 'QA_API_UNKNOWN';
57 g_message_table(qa_validation_api.reject_an_entry_error) :=
58 'QA_API_REJECT_AN_ENTRY';
59
60 -- Bug 3679762.Initialising the message array for the missing assign a value target
61 -- column error message.
62 -- srhariha.Wed Jun 16 06:54:06 PDT 2004
63
64 g_message_table(qa_validation_api.missing_assign_column) :=
65 'QA_MISSING_ASSIGN_COLUMN';
66
67
68 END populate_message_table;
69
70 --
71 -- Bug 5932426
72 -- New Procedure to check if the data entered in a
73 -- collection element during an update Txn has been
74 -- changed, in which case the validation is not to be
75 -- performed and so the action on that element wont
76 -- refire.
77 -- ntungare Sat Apr 14 00:51:48 PDT 2007
78 --
79 PROCEDURE update_validation_flg(elements IN OUT NOCOPY qa_validation_api.ElementsArray,
80 p_plan_id IN NUMBER,
81 p_collection_id IN NUMBER,
82 p_occurrence IN NUMBER) AS
83
84 char_id INTEGER;
85 input_val VARCHAR2(32767);
86 saved_val VARCHAR2(32767);
87 --
88 -- bug 6266477
89 -- Variable declaration
90 -- skolluku Sun Oct 14 03:26:31 PDT 2007
91 --
92 l_append BOOLEAN := FALSE;
93 l_rescol VARCHAR2(30);
94 l_rescol_val VARCHAR2(32767);
95 l_sql_string VARCHAR2(32767);
96 elements_db qa_validation_api.ElementsArray;
97
98
99 BEGIN
100 char_id:= elements.first;
101
102 -- Looping through the elements
103 --
104 --
105 -- bug 6266477
106 -- Loop through all the char_id and get their
107 -- result_column_names to build the select clause
108 -- for fetching their values from QA_RESULTS_FULL_V
109 -- skolluku Sun Oct 14 03:26:31 PDT 2007
110 --
111 WHILE char_id <= elements.last
112 LOOP
113 --input_val := elements(char_id).value;
114
115 -- Getting the result_column_name stored in QA_PLAN_CHARS,
116 -- for the collection and append it to the string.
117 --
118 l_rescol := QA_ERES_PKG.get_result_column_name(p_plan_id, char_id);
119 --
120 -- bug 7194013
121 -- Check if the derived result column name is NULL in which
122 -- case the collection element needs to be removed from the
123 -- elements array since it does not exist in the Collection
124 -- plan
125 -- ntungare
126 --
127 IF (l_rescol IS NOT NULL) THEN
128 IF(l_append) THEN
129 l_sql_string := l_sql_string || ' || ''@';
130 l_sql_string := l_sql_string || char_id || '='' || ' || 'replace(' || l_rescol || ', ''@'', ''@@'')';
131 ELSE
132 l_sql_string := l_sql_string || '''' || char_id || '='' || ' || 'replace(' || l_rescol || ', ''@'', ''@@'')';
133 END IF;
134 l_append := TRUE;
135 ELSE
136 elements.delete(char_id);
137 END IF;
138
139 char_id:= elements.next(char_id);
140 END LOOP;
141
142 -- Execute the statement and fetch the values into an array
143 -- using result_to_array.
144 l_sql_string := 'SELECT ' || l_sql_string || ' FROM QA_RESULTS_FULL_V WHERE plan_id = :2 and collection_id = :3 and occurrence = :4';
145 EXECUTE IMMEDIATE l_sql_string INTO l_rescol_val USING p_plan_id, p_collection_id, p_occurrence;
146 elements_db := qa_validation_api.result_to_array(l_rescol_val);
147
148 -- Reinitialize the char_id variable since, the comparison
149 -- needs to be done here. So, looping again...
150 char_id:= elements.first;
151 -- Looping through the elements
152 --
153 WHILE char_id <= elements.last
154 LOOP
155 input_val := elements(char_id).value;
156 --
157 -- bug 6266477
158 -- Commented the call to get_result_column_value since,
159 -- it fetches one value at a time from QA_RESULTS_FULL_V
160 -- resulting in performance issues. To avoid this the hit
161 -- to QA_RESULTS_FULL_V is done just once and collected
162 -- into the array elements_db in the earlier processing.
163 -- skolluku Sun Oct 14 03:26:31 PDT 2007
164 --
165 -- Getting the value stored in QA_RESULTS, for the collection
166 -- element being processed
167 --
168 /*
169 saved_val := QA_ERES_PKG.get_result_column_value(
170 p_plan_id => p_plan_id,
171 p_collection_id => p_collection_id,
172 p_occurrence => p_occurrence,
173 p_char_id => char_id);
174 */
175 saved_val := elements_db(char_id).value;
176 -- If the input value is the same as the
177 -- value already existing in QA_RESULTS, it means that
178 -- the element has not been updated, in which case the
179 -- actions based on it are not to be fired. So setting
180 -- the validation flag for this element accordingly
181 --
182 If NVL(input_val,'0') = NVL(saved_val,'0') THEN
183 elements(char_id).validation_flag := qa_validation_api.action_fired;
184 End If;
185
186 char_id:= elements.next(char_id);
187 END LOOP;
188 END update_validation_flg;
189
190
191 FUNCTION get_error_code(code IN NUMBER) RETURN VARCHAR2 IS
192 BEGIN
193 --
194 -- Should figure out the error message from dictionary.
195 --
196 RETURN qa_validation_api.get_error_message (code);
197 END get_error_code;
198
199
200 --
201 -- Get the error messages and append them into an @-separated
202 -- string.
203 --
204 PROCEDURE get_error_messages(
205 p_errors IN qa_validation_api.ErrorArray,
206 p_plan_id IN NUMBER,
207 x_messages OUT NOCOPY VARCHAR2) IS
208
209 separator CONSTANT VARCHAR2(1) := '@';
210 name qa_chars.prompt%TYPE;
211 code VARCHAR2(2000);
212
213 -- Bug 5307450
214 -- Cursor to get the message entered by the user for the
215 -- 'Reject the input' action in the collection plan.
216 -- ntungare Tue Mar 28 08:02:43 PST 2006.
217 --
218 CURSOR cur_mesg(p_plan_id NUMBER,p_char_id NUMBER) IS
219 SELECT message
220 FROM qa_plan_char_actions
221 WHERE plan_char_action_trigger_id IN
222 (SELECT plan_char_action_trigger_id
223 FROM qa_plan_char_action_triggers
224 WHERE plan_id = p_plan_id
225 AND char_id = p_char_id)
226 AND action_id = 2;
227 BEGIN
228 x_messages := '';
229
230 --
231 -- This bug is discovered during bug fix for 3402251.
232 -- In some rare situation, this proc can be called when
233 -- error stack is actually empty. Should return
234 -- immediately.
235 -- bso Mon Feb 9 22:06:09 PST 2004
236 --
237 IF p_errors.count = 0 then
238 RETURN;
239 END IF;
240
241 FOR i IN p_errors.FIRST .. p_errors.LAST LOOP
242 name := qa_plan_element_api.get_prompt(p_plan_id,
243 p_errors(i).element_id);
244 --
245 -- Just in case the prompt contains @
246 --
247 name := replace(name, separator, separator||separator);
248
249 -- Bug 5307450
250 -- In QWB if the action is 'Reject the input' we were displaying the
251 -- seeded error message 'QA_API_REJECT_AN_ENTRY' and not the message
252 -- added in the collection plan setup by the user. Now the following
253 -- condition would get the 'Reject the input' action message
254 -- from the collection plan and put in the variable code. If the action
255 -- is not 'Reject the input' then the existing code is used.
256 -- ntunagre Tue Mar 28 08:04:54 PST 2006.
257 --
258 If p_errors(i).error_code = qa_validation_api.reject_an_entry_error then
259 OPEN cur_mesg(p_plan_id, p_errors(i).element_id);
260 FETCH cur_mesg INTO code;
261 CLOSE cur_mesg;
262 else
263 code := get_error_code(p_errors(i).error_code);
264 End If;
265
266 x_messages := x_messages || name || ': ' || code;
267 IF i < p_errors.LAST THEN
268 x_messages := x_messages || separator;
269 END IF;
270 END LOOP;
271 END get_error_messages;
272
273
274 --
275 -- Get the action messages and append them into an @-separated
276 -- string.
277 --
278 PROCEDURE get_action_messages(
279 msg_array IN qa_validation_api.MessageArray,
280 plan_id IN NUMBER,
281 messages OUT NOCOPY VARCHAR2) IS
282
283 separator CONSTANT VARCHAR2(1) := '@';
284 name qa_chars.prompt%TYPE;
285 code VARCHAR2(2000);
286 BEGIN
287
288 messages := '';
289
290 IF msg_array.COUNT <> 0 THEN
291 FOR i IN msg_array.FIRST .. msg_array.LAST LOOP
292 --ilawler - bug #3340004 - Mon Feb 16 18:53:29 2004
293 --According to bso, the message should always be <target> = <value>
294
295 /*
296 name := qa_plan_element_api.get_prompt(plan_id, msg_array(i).element_id);
297
298 --
299 -- Just in case the prompt contains @
300 --
301 name := replace(name, separator, separator||separator);
302 messages := messages || name || ':' || msg_array(i).message;
303 */
304 messages := messages || msg_array(i).message;
305
306 IF i < msg_array.LAST THEN
307 messages := messages || separator;
308 END IF;
309 END LOOP;
310 END IF;
311
312 END get_action_messages;
313
314 --
315 -- Post a result to the database. This is a wrapper to the QA API
316 -- qa_results_api.insert_row
317 --
318 -- This function is used by
319 --
320 -- CMRO/QA Integration /ahldev/ahl/12.0/patch/115/sql/AHLVQARB.pls
321 --
322 -- MQA DDE /qadev/qa/12.0/patch/115/sql/qaresb.pls
323 --
324 -- Return 0 if OK
325 -- Return -1 if error.
326 --
327 FUNCTION nontxn_post_result(
328 x_occurrence OUT NOCOPY NUMBER,
329 x_org_id IN NUMBER,
330 x_plan_id IN NUMBER,
331 x_spec_id IN NUMBER,
332 x_collection_id IN NUMBER,
333 x_result IN VARCHAR2,
334 x_result1 IN VARCHAR2, -- R12 Project MOAC 4637896, ID passing
335 x_result2 IN VARCHAR2, -- not used yet, for future expansion
336 x_enabled IN INTEGER,
337 x_committed IN INTEGER,
338 x_messages OUT NOCOPY VARCHAR2)
339 RETURN INTEGER IS
340 elements qa_validation_api.ElementsArray;
341 error_array qa_validation_api.ErrorArray;
342 message_array qa_validation_api.MessageArray;
343 return_status VARCHAR2(1);
344 action_result VARCHAR2(1);
345 msg_count NUMBER;
346 msg_data VARCHAR2(2000);
347 y_spec_id NUMBER;
348 y_collection_id NUMBER;
349 y_committed VARCHAR2(1);
350 BEGIN
351
352 --
353 -- Bug 2617638
354 -- The original statement returns if x_result IS NULL.
355 -- Undesirable if caller passes all validated IDs in x_result1.
356 -- Added AND x_result1 IS NULL
357 -- bso Tue Oct 8 18:34:38 PDT 2002
358 --
359 IF x_result IS NULL AND x_result1 IS NULL THEN
360 RETURN -1;
361 END IF;
362
363 IF x_committed = 1 THEN
364 y_committed := fnd_api.g_true;
365 ELSE
366 y_committed := fnd_api.g_false;
367 END IF;
368
369 --
370 -- Some input can be -1, if that's the case, set to null
371 --
372 IF x_collection_id = -1 THEN
373 y_collection_id := NULL;
374 ELSE
375 y_collection_id := x_collection_id;
376 END IF;
377 IF x_spec_id = -1 THEN
378 y_spec_id := NULL;
379 ELSE
380 y_spec_id := x_spec_id;
381 END IF;
382
383 --
384 -- The flatten string is a representation that looks like this:
385 --
386 -- 10=Item@101=Defected@102=20 ...
387 --
388 -- namely, it is an @ separated list of charID=value. In case
389 -- value contains @, then it is doubly encoded.
390 --
391 -- First task is to decode this string into the row_element
392 -- array.
393 --
394 elements := qa_validation_api.result_to_array(x_result);
395 elements := qa_validation_api.id_to_array(x_result1, elements);
396 qa_validation_api.set_validation_flag(elements);
397
398 --
399 -- Bug 2617638
400 -- The follow statement is needed to process x_result1.
401 -- This has been included in function post_result but
402 -- was missed out in this procedure.
403 -- bso Tue Oct 8 18:37:00 PDT 2002
404 --
405
406 qa_results_pub.insert_row(
407 p_api_version => 1.0,
408 p_init_msg_list => fnd_api.g_true,
409 p_org_id => x_org_id,
410 p_plan_id => x_plan_id,
411 p_spec_id => y_spec_id,
412 p_transaction_number => null,
413 p_transaction_id => null,
414 p_enabled_flag => x_enabled,
415 p_commit => y_committed,
416 x_collection_id => y_collection_id,
417 x_occurrence => x_occurrence,
418 x_row_elements => elements,
419 x_msg_count => msg_count,
420 x_msg_data => msg_data,
421 x_error_array => error_array,
422 x_message_array => message_array,
423 x_return_status => return_status,
424 x_action_result => action_result);
425
426 IF qa_validation_api.no_errors(error_array) AND
427 return_status <> FND_API.G_RET_STS_ERROR AND
428 return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
429 RETURN 0;
430 ELSE
431 get_error_messages(error_array, x_plan_id, x_messages);
432 END IF;
433
434 RETURN -1;
435 END nontxn_post_result;
436
437 --
438 -- Bug 5955808
439 -- New procedure to initalize the Sequence Type
440 -- Collection elements with the default value of
441 -- Automatic, using which the Sequence generation
442 -- program will generate sequences. This is very
443 -- important in case of Background result collection
444 -- ntungare Mon Mar 26 06:20:05 PDT 2007
445 --
446 PROCEDURE initialize_seq_elements(x_plan_id IN NUMBER,
447 elements IN OUT NOCOPY qa_validation_api.ElementsArray)
448 IS
449 TYPE seq_charid_tab_typ IS TABLE OF qa_chars.char_id%TYPE INDEX BY BINARY_INTEGER;
450 seq_charid_tab seq_charid_tab_typ;
451
452 def_seq_val VARCHAR2(100);
453 BEGIN
454 SELECT qc.char_id
455 BULK COLLECT INTO seq_charid_tab
456 FROM qa_chars qc, qa_plan_chars qpc
457 WHERE qpc.plan_id = x_plan_id
458 AND qc.char_id = qpc.char_id
459 AND qc.datatype = 5;
460
461 IF seq_charid_tab.COUNT <> 0 THEN
462 def_seq_val := QA_SEQUENCE_API.get_sequence_default_value();
463 END IF;
464
465 FOR cntr IN 1..seq_charid_tab.COUNT
466 LOOP
467 If elements(seq_charid_tab(cntr)).VALUE IS NULL THEN
468 elements(seq_charid_tab(cntr)).VALUE := def_seq_val;
469 End If;
470 END LOOP;
471 END initialize_seq_elements;
472
473 --
474 -- This overloaded method is for transaction only
475 -- Post a result to the database. This is a wrapper to the QA API
476 -- qa_results_api.insert_row
477 --
478 -- This function is used by
479 --
480 -- iSP /qadev/qa/12.0/java/dde/eam/server/QaResultsVORowImpl.java
481 --
482 -- EAM /qadev/qa/12.0/java/selfservice/server/QaResultsVORowImpl.java
483 --
484 -- MQA Txn /qadev/qa/12.0/patch/115/sql/qaresb.pls
485 --
486 -- Return 0 if OK
487 -- Return -1 if error.
488 --
489 FUNCTION post_result(
490 x_occurrence IN OUT NOCOPY NUMBER,
491 x_org_id IN NUMBER,
492 x_plan_id IN NUMBER,
493 x_spec_id IN NUMBER,
494 x_collection_id IN NUMBER,
495 x_result IN VARCHAR2,
496 x_result1 IN VARCHAR2, -- R12 Project MOAC 4637896, ID passing
497 x_result2 IN VARCHAR2, -- not used yet, for future expansion
498 x_enabled IN INTEGER,
499 x_committed IN INTEGER,
500 x_transaction_number IN NUMBER,
501 x_messages OUT NOCOPY VARCHAR2)
502 RETURN INTEGER IS
503 elements qa_validation_api.ElementsArray;
504 error_array qa_validation_api.ErrorArray;
505 message_array qa_validation_api.MessageArray;
506 return_status VARCHAR2(1);
507 action_result VARCHAR2(1);
508 msg_count NUMBER;
509 msg_data VARCHAR2(2000);
510 y_spec_id NUMBER;
511 y_collection_id NUMBER;
512 y_committed VARCHAR2(1);
513 BEGIN
514
515 --
516 -- Bug 2617638
517 -- The original statement returns if x_result IS NULL.
518 -- Undesirable if caller passes all validated IDs in x_result1.
519 -- Added AND x_result1 IS NULL
520 -- bso Tue Oct 8 18:34:38 PDT 2002
521 --
522 IF x_result IS NULL AND x_result1 IS NULL THEN
523 RETURN -1;
524 END IF;
525
526 IF x_transaction_number IS NULL THEN
527 RETURN -1;
528 END IF;
529
530 IF x_committed = 1 THEN
531 y_committed := fnd_api.g_true;
532 ELSE
533 y_committed := fnd_api.g_false;
534 END IF;
535
536 --
537 -- Some input can be -1, if that's the case, set to null
538 --
539 IF x_collection_id = -1 THEN
540 y_collection_id := NULL;
541 ELSE
542 y_collection_id := x_collection_id;
543 END IF;
544 IF x_spec_id = -1 THEN
545 y_spec_id := NULL;
546 ELSE
547 y_spec_id := x_spec_id;
548 END IF;
549 --
550 -- The flatten string is a representation that looks like this:
551 --
552 -- 10=Item@101=Defected@102=20 ...
553 --
554 -- namely, it is an @ separated list of charID=value. In case
555 -- value contains @, then it is doubly encoded.
556 --
557 -- First task is to decode this string into the row_element
558 -- array.
559 --
560 elements := qa_validation_api.result_to_array(x_result);
561 elements := qa_validation_api.id_to_array(x_result1, elements);
562
563 IF (x_transaction_number IS NOT NULL) AND (x_transaction_number > 0) THEN
564 qa_validation_api.set_validation_flag_txn(
565 x_elements => elements,
566 p_plan_id => x_plan_id,
567 p_transaction_number => x_transaction_number,
568 p_plan_transaction_id => NULL);
569 END IF;
570
571 --
572 -- bug 5955808
573 -- Making a call to the proc which would initialize
574 -- the seq type collection elements with the value
575 -- automatic
576 -- Mon Mar 26 06:21:43 PDT 2007
577 --
578 initialize_seq_elements(x_plan_id, elements);
579
580 qa_results_pub.insert_row(
581 p_api_version => 1.0,
582 p_init_msg_list => fnd_api.g_true,
583 p_org_id => x_org_id,
584 p_plan_id => x_plan_id,
585 p_spec_id => y_spec_id,
586 p_transaction_number => x_transaction_number,
587 p_transaction_id => null,
588 p_enabled_flag => x_enabled,
589 p_commit => y_committed,
590 x_collection_id => y_collection_id,
591 x_occurrence => x_occurrence,
592 x_row_elements => elements,
593 x_msg_count => msg_count,
594 x_msg_data => msg_data,
595 x_error_array => error_array,
596 x_message_array => message_array,
597 x_return_status => return_status,
598 x_action_result => action_result);
599
600 IF qa_validation_api.no_errors(error_array) AND
601 return_status <> FND_API.G_RET_STS_ERROR AND
602 return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
603 get_action_messages(message_array, x_plan_id, x_messages);
604 RETURN 0;
605 ELSE
606 get_error_messages(error_array, x_plan_id, x_messages);
607 --
608 -- Remove commit; completely. The insert_row API
609 -- will take care of committing according to the
610 -- x_committed flag.
611 --
612 END IF;
613
614
615 RETURN -1;
616 END post_result;
617
618 --
619 -- This overloaded method is for ssqr
620 -- Same as post_result. In addition, it inserts pc history and automatic records.
621 --
622 -- Used only by QWB and Fwk Integration
623 -- /qadev/qa/12.0/java/ssqr/server/QualityResultsEOImpl.java
624 --
625 -- Pass -1 to x_transaction_number when using standalone.
626 --
627 -- Return 0 if OK
628 -- Return -1 if error.
629 --
630 -- 12.1 QWB Usability Improvements
631 -- Added 2 new parameters x_agg_elements and x_agg_val
632 -- to ensure that the value aggregated for any of the parent
633 -- collection elements is pushed into the JAVA layer for
634 -- assigned to the appropriate EO attributes.
635 --
636 FUNCTION ssqr_post_result(
637 x_occurrence IN OUT NOCOPY NUMBER,
638 x_org_id IN NUMBER,
639 x_plan_id IN NUMBER,
640 x_spec_id IN NUMBER,
641 x_collection_id IN NUMBER,
642 x_txn_header_id IN NUMBER,
643 x_par_plan_id IN NUMBER,
644 x_par_col_id IN NUMBER,
645 x_par_occ IN NUMBER,
646 x_result IN VARCHAR2,
647 x_result1 IN VARCHAR2, -- R12 Project MOAC 4637896, ID passing
648 x_result2 IN VARCHAR2, -- not used yet, for future expansion
649 x_enabled IN INTEGER,
650 x_committed IN INTEGER,
651 x_transaction_number IN NUMBER,
652 x_messages OUT NOCOPY VARCHAR2,
653 x_agg_elements OUT NOCOPY VARCHAR2,
654 x_agg_val OUT NOCOPY VARCHAR2,
655 p_last_update_date IN DATE DEFAULT SYSDATE)
656 RETURN INTEGER IS
657 elements qa_validation_api.ElementsArray;
658 error_array qa_validation_api.ErrorArray;
659 message_array qa_validation_api.MessageArray;
660 return_status VARCHAR2(1);
661 action_result VARCHAR2(1);
662 msg_count NUMBER;
663 msg_data VARCHAR2(2000);
664 y_spec_id NUMBER;
665 y_collection_id NUMBER;
666 y_committed VARCHAR2(1);
667
668 -- anagarwa Wed May 26 17:07:29 PDT 2004
669 -- bug 3667449
670 l_ret_value VARCHAR2(1);
671
672 -- bug 4658275
673 -- for supporting Eres functionality
674 l_esig_status BOOLEAN ;
675
676 -- 12.1 QWB Usability Improvements
677 --
678 agg_elements VARCHAR2(4000);
679 agg_vals VARCHAR2(4000);
680
681 l_ssqr_operation NUMBER;
682
683 l_dummy VARCHAR2(1);
684
685 BEGIN
686
687 --messages should be cleared everytime a Submit is clicked on client
688 fnd_msg_pub.Initialize();
689 fnd_msg_pub.reset();
690
691 --
692 -- Bug 2617638
693 -- The original statement returns if x_result IS NULL.
694 -- Undesirable if caller passes all validated IDs in x_result1.
695 -- Added AND x_result1 IS NULL
696 -- bso Tue Oct 8 18:34:38 PDT 2002
697 --
698 IF x_result IS NULL AND x_result1 IS NULL THEN
699 RETURN -1;
700 END IF;
701
702 /*
703 IF x_transaction_number IS NULL THEN
704 RETURN -1;
705 END IF;
706 */
707 IF x_committed = 1 THEN
708 y_committed := fnd_api.g_true;
709 ELSE
710 y_committed := fnd_api.g_false;
711 END IF;
712
713 --
714 -- Some input can be -1, if that's the case, set to null
715 --
716 IF x_collection_id = -1 THEN
717 y_collection_id := NULL;
718 ELSE
719 y_collection_id := x_collection_id;
720 END IF;
721 IF x_spec_id = -1 THEN
722 y_spec_id := NULL;
723 ELSE
724 y_spec_id := x_spec_id;
725 END IF;
726
727 -- Bug 4658275. eres support in QWB
728 -- check for the collection id of parent and child
729 -- if collection Id is same then we are entering
730 -- child row immediately in the same session
731 -- check is only required when parent row is
732 -- already in database in status 2
733 IF ( x_par_col_id <> x_collection_id ) THEN
734 l_esig_status := validate_esig_for_insert(p_plan_id => x_par_plan_id,
735 p_plan_collection_id => x_par_col_id,
736 p_plan_occurrence => x_par_occ );
737 IF NOT l_esig_status THEN
738 RETURN -1;
739 END IF;
740 END IF; -- x_par_col_id <> x_collection_id
741
742 --
743 -- The flatten string is a representation that looks like this:
744 --
745 -- 10=Item@101=Defected@102=20 ...
746 --
747 -- namely, it is an @ separated list of charID=value. In case
748 -- value contains @, then it is doubly encoded.
749 --
750 -- First task is to decode this string into the row_element
751 -- array.
752 --
753 elements := qa_validation_api.result_to_array(x_result);
754 elements := qa_validation_api.id_to_array(x_result1, elements);
755
756 IF (x_transaction_number IS NOT NULL) AND (x_transaction_number > 0) THEN
757 qa_validation_api.set_validation_flag_txn(
758 x_elements => elements,
759 p_plan_id => x_plan_id,
760 p_transaction_number => x_transaction_number,
761 p_plan_transaction_id => NULL);
762 END IF;
763
764 -- If the Txn number is 0 or -1 then it means that the
765 -- txn is not an OAF Txn so set the value as 1 else 2
766 -- The difference is that for the OAF Txns (stauts 2)
767 -- the validation needs to be done again to derive the
768 -- Id values for the context elements.
769 -- This is not needed in case of Standalone entry in QWB
770 --
771 If x_transaction_number IN (0, -1) then
772 l_ssqr_operation := 1;
773 Else
774 l_ssqr_operation := 2;
775 End If;
776
777 -- 12.1 QWB Usability Improvements
778 -- Passing a new parameter p_ssqr_operation to indicate
779 -- that the processing is being done for QWB application
780 -- that would ensure that the Row Validation is not done
781 -- again at the time of the data insertion
782 --
783 qa_results_pub.insert_row(
784 p_api_version => 1.0,
785 p_init_msg_list => fnd_api.g_true,
786 p_org_id => x_org_id,
787 p_plan_id => x_plan_id,
788 p_spec_id => y_spec_id,
789 p_transaction_number => x_transaction_number,
790 p_transaction_id => null,
791 p_txn_header_id => x_txn_header_id,
792 p_enabled_flag => x_enabled,
793 p_commit => y_committed,
794 x_collection_id => y_collection_id,
795 x_occurrence => x_occurrence,
796 x_row_elements => elements,
797 x_msg_count => msg_count,
798 x_msg_data => msg_data,
799 x_error_array => error_array,
800 x_message_array => message_array,
801 x_return_status => return_status,
802 x_action_result => action_result,
803 p_ssqr_operation => l_ssqr_operation,
804 p_last_update_date => p_last_update_date);
805
806 IF qa_validation_api.no_errors(error_array) AND
807 return_status <> FND_API.G_RET_STS_ERROR AND
808 return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
809 get_action_messages(message_array, x_plan_id, x_messages);
810
811 --create relationship with parent
812
813 -- Bug 4343758. OA Framework Integration project.
814 -- Shouldnt create relationship if parent plan is null.
815 -- srhariha. Wed May 18 04:34:53 PDT 2005.
816 -- 12.1 QWB Usability Improvements
817 -- Get the aggregated values for tge Parent plan elements
818 --
819
820 if(x_par_plan_id is not null) then
821 --
822 -- bug 7046071
823 -- Passing the p_ssqr_operation parameter to check if the
824 -- call is done from the OAF application or from Forms
825 -- In case of the OAF application, the COMMIT that is
826 -- executed in the aggregate_parent must not be called
827 -- ntungare
828 --
829 qa_parent_child_pkg.relate(x_par_plan_id, x_par_col_id,
830 x_par_occ, x_plan_id,
831 x_collection_id, x_occurrence,
832 x_txn_header_id,
833 agg_elements,
834 agg_vals,
835 l_ssqr_operation);
836 -- If the parent Collection id is not equal to the child collection
837 -- Id it means that the Child record is being added to an existing
838 -- parent record in which case the Aggregation processing needs to
839 -- be done
840 --
841 -- Bug 6729769
842 -- This check is no longer needed since OAF will always call
843 -- the update API for the parent when a Child record is inserted.
844 -- The History record would be inserted when the update for parent is
845 -- called. If a Hist record is already present for the parent record
846 -- for the txn then it would be updated
847 -- ntungare
848 --
849 --if (x_par_col_id <> x_collection_id) THEN
850 x_agg_elements := RTRIM(LTRIM(agg_elements,','),',');
851 x_agg_val := RTRIM(LTRIM(agg_vals,','),',');
852 -- else if both the parent and the child records are being
853 -- inserted anew then History child records need to be explicitly
854 -- updated if the aggregation has happened
855 --
856 /*
857 else
858 if (agg_elements IS NOT NULL) THEN
859 l_dummy := QA_PARENT_CHILD_PKG.update_hist_children(
860 p_parent_plan_id => x_par_plan_id,
861 p_parent_collection_id => x_par_col_id,
862 p_parent_occurrence => x_par_occ);
863 end if;
864 end if;
865 */
866 end if;
867
868 -- Bug 3536025. Calling new procedure insert_history_auto_rec_QWB
869 -- instead of insert_history_auto_rec. The new procedure is same as
870 -- the old one except it doesnot changes child plan's txn_header_id
871 -- and doesnot fire actions for child plans.
872 -- srhariha. Wed May 26 22:31:28 PDT 2004
873
874
875 /*
876 qa_parent_child_pkg.insert_history_auto_rec_QWB(x_plan_id,
877 x_txn_header_id,
878 1,
879 2);
880 qa_parent_child_pkg.insert_history_auto_rec_QWB(x_plan_id,
881 x_txn_header_id,
882 1,
883 4);
884 */
885
886 -- Bug 4343758. OA Framework Integration project.
887 -- Shouldnt create history and automatic for txn scenario.
888 -- srhariha. Wed May 18 04:34:53 PDT 2005.
889
890 -- Bug 3681815. Changing the call to proc below as the signature got
891 -- changed due to the bug.
892 -- commenting the previous calls to proc above.
893 -- saugupta Tue, 15 Jun 2004 05:51:00 -0700 PDT
894
895 -- insert automatic records
896 if (x_enabled = 2 or x_enabled is null) then
897
898 qa_parent_child_pkg.insert_history_auto_rec_QWB(p_plan_id => x_plan_id,
899 p_collection_id => y_collection_id,
900 p_occurrence => x_occurrence,
901 p_organization_id => x_org_id,
902 p_txn_header_id => x_txn_header_id,
903 p_relationship_type => 1,
904 p_data_entry_mode => 2,
905 x_status => return_status);
906 -- insert history records
907 qa_parent_child_pkg.insert_history_auto_rec_QWB(p_plan_id => x_plan_id,
908 p_collection_id => y_collection_id,
909 p_occurrence => x_occurrence,
910 p_organization_id => x_org_id,
911 p_txn_header_id => x_txn_header_id,
912 p_relationship_type => 1,
913 p_data_entry_mode => 4,
914 x_status => return_status);
915 end if;
916
917
918
919 -- Bug 4343758. OA Framework Integration project.
920 -- Shouldnt update parent if parent plan is null.
921 -- srhariha. Wed May 18 04:34:53 PDT 2005.
922
923 -- Bug 5355933. Do not call update if Insert has not happened
924 -- saugupta Wed, 26 Jul 2006 05:55:14 -0700 PDT
925 if(x_par_plan_id is not null and return_status = 'T' ) then
926 -- anagarwa Wed May 26 17:07:29 PDT 2004
927 -- bug 3667449
928 -- if parent copy source element is updated, then update the
929 -- child too.
930 l_ret_value:= QA_PARENT_CHILD_PKG.update_child(x_par_plan_id,
931 x_par_col_id,
932 x_par_occ, x_plan_id,
933 x_collection_id, x_occurrence);
934 end if;
935
936 RETURN 0;
937 ELSE
938 get_error_messages(error_array, x_plan_id, x_messages);
939 --
940 -- Remove commit; completely. The insert_row API
941 -- will take care of committing according to the
942 -- x_committed flag.
943 --
944 END IF;
945
946 RETURN -1;
947 END ssqr_post_result;
948
949
950 --
951 -- This seems to be used only by EAM transactions.
952 -- /qadev/qa/12.0/java/dde/eam/server/QaResultsVORowImpl.java
953 --
954 FUNCTION update_result(
955 x_occurrence IN NUMBER,
956 x_org_id IN NUMBER,
957 x_plan_id IN NUMBER,
958 x_spec_id IN NUMBER,
959 x_collection_id IN NUMBER,
960 x_result IN VARCHAR2,
961 x_result1 IN VARCHAR2, -- R12 Project MOAC 4637896, ID passing
962 x_result2 IN VARCHAR2, -- not used yet, for future expansion
963 x_enabled IN INTEGER,
964 x_committed IN INTEGER,
965 x_transaction_number IN NUMBER,
966 x_messages OUT NOCOPY VARCHAR2)
967 RETURN INTEGER IS
968 elements qa_validation_api.ElementsArray;
969 error_array qa_validation_api.ErrorArray;
970 message_array qa_validation_api.MessageArray;
971 return_status VARCHAR2(1);
972 action_result VARCHAR2(1);
973 msg_count NUMBER;
974 msg_data VARCHAR2(2000);
975 y_spec_id NUMBER;
976 y_collection_id NUMBER;
977 y_committed VARCHAR2(1);
978
979 -- esig status boolean variable
980 l_esig_status BOOLEAN;
981 BEGIN
982 IF x_result IS NULL AND x_result1 IS NULL THEN
983 RETURN -1;
984 END IF;
985
986 --IF x_transaction_number IS NULL THEN
987 -- RETURN -1;
988 --END IF;
989
990 IF x_committed = 1 THEN
991 y_committed := fnd_api.g_true;
992 ELSE
993 y_committed := fnd_api.g_false;
994 END IF;
995
996 --
997 -- Some input can be -1, if that's the case, set to null
998 --
999 IF x_collection_id = -1 THEN
1000 y_collection_id := NULL;
1001 ELSE
1002 y_collection_id := x_collection_id;
1003 END IF;
1004 IF x_spec_id = -1 THEN
1005 y_spec_id := NULL;
1006 ELSE
1007 y_spec_id := x_spec_id;
1008 END IF;
1009
1010
1011 --
1012 -- The flatten string is a representation that looks like this:
1013 --
1014 -- 10=Item@101=Defected@102=20 ...
1015 --
1016 -- namely, it is an @ separated list of charID=value. In case
1017 -- value contains @, then it is doubly encoded.
1018 --
1019 -- First task is to decode this string into the row_element
1020 -- array.
1021 --
1022
1023 elements := qa_validation_api.result_to_array(x_result);
1024 elements := qa_validation_api.id_to_array(x_result1, elements);
1025
1026 IF (x_transaction_number IS NOT NULL) AND (x_transaction_number > 0) THEN
1027 qa_validation_api.set_validation_flag_txn(
1028 x_elements => elements,
1029 p_plan_id => x_plan_id,
1030 p_transaction_number => x_transaction_number,
1031 p_plan_transaction_id => NULL);
1032 END IF;
1033
1034 qa_results_pub.update_row(
1035 p_api_version => 1.0,
1036 p_init_msg_list => fnd_api.g_true,
1037 p_commit => y_committed,
1038 p_plan_id => x_plan_id,
1039 p_spec_id => y_spec_id,
1040 p_org_id => x_org_id,
1041 p_transaction_number => x_transaction_number,
1042 p_transaction_id => null,
1043 p_enabled_flag => x_enabled,
1044 p_collection_id => y_collection_id,
1045 p_occurrence => x_occurrence,
1046 x_row_elements => elements,
1047 x_msg_count => msg_count,
1048 x_msg_data => msg_data,
1049 x_error_array => error_array,
1050 x_message_array => message_array,
1051 x_return_status => return_status,
1052 x_action_result => action_result);
1053
1054 IF qa_validation_api.no_errors(error_array) THEN
1055
1056 -- Bug 4057388. Display a message action was not firing in eam.
1057 -- message_array returned from qa_results_pub.update_row was
1058 -- not converted to output string x_mesasges.
1059 -- srhariha.Thu Dec 30 22:32:00 PST 2004
1060
1061 get_action_messages(message_array, x_plan_id, x_messages);
1062
1063
1064 RETURN 0;
1065 ELSIF return_status <> FND_API.G_RET_STS_ERROR AND
1066 return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
1067 get_error_messages(error_array, x_plan_id, x_messages);
1068 END IF;
1069
1070 RETURN -1;
1071 END update_result;
1072
1073
1074 PROCEDURE post_error_messages (p_errors IN qa_validation_api.ErrorArray,
1075 plan_id NUMBER) IS
1076
1077 l_message_name VARCHAR2(30);
1078 l_char_prompt VARCHAR2(100);
1079
1080 -- bug 5307450
1081 -- Cursor to get the message entered by the user for the
1082 -- 'Reject the input' action in the collection plan.
1083 -- ntungare Tue Mar 28 08:07:05 PST 2006.
1084 --
1085 CURSOR cur_mesg(p_plan_id NUMBER,p_char_id NUMBER) IS
1086 SELECT message
1087 FROM qa_plan_char_actions
1088 WHERE plan_char_action_trigger_id IN
1089 (SELECT plan_char_action_trigger_id
1090 FROM qa_plan_char_action_triggers
1091 WHERE plan_id = p_plan_id
1092 AND char_id = p_char_id)
1093 AND action_id = 2;
1094
1095 l_mesg varchar2(2000);
1096
1097 BEGIN
1098
1099
1100 --messages should be cleared everytime a Submit is clicked on client
1101 fnd_msg_pub.Initialize();
1102 fnd_msg_pub.reset();
1103
1104 FOR i IN p_errors.FIRST .. p_errors.LAST LOOP
1105 -- Bug 5307450
1106 -- In QWB if the action is 'Reject the input' we were displaying the
1107 -- seeded error message 'QA_API_REJECT_AN_ENTRY' and not the message
1108 -- added in the collection plan setup by the user. Now the following
1109 -- IF condition would get the 'Reject the input' action message from
1110 -- the collection plan and pass it to the token of a seeded meesage.
1111 -- If the action is not 'Reject the input' then the existing code is
1112 -- used.
1113 -- Also a new seeded message has been created with a token as the message
1114 -- description. This token would be replaced with the message added by
1115 -- the user in the collection plan setup for 'Reject the input' action.
1116 -- ntungare Tue Mar 28 08:10:23 PST 2006.
1117 --
1118 IF p_errors(i).error_code = qa_validation_api.reject_an_entry_error then
1119 OPEN cur_mesg(plan_id,p_errors(i).element_id);
1120 FETCH cur_mesg INTO l_mesg;
1121 CLOSE cur_mesg;
1122
1123 l_message_name := g_message_table(p_errors(i).error_code);
1124 l_char_prompt := qa_plan_element_api.get_prompt(plan_id, p_errors(i).element_id);
1125
1126 fnd_message.set_name('QA','QA_API_REJECT_INPUT');
1127 fnd_message.set_token('CHAR_PROMPT', l_char_prompt);
1128 fnd_message.set_token('REJECT_MESSAGE', l_mesg);
1129 fnd_msg_pub.add();
1130 ELSE
1131 l_message_name := g_message_table(p_errors(i).error_code);
1132 l_char_prompt := qa_plan_element_api.get_prompt(plan_id, p_errors(i).element_id);
1133
1134 fnd_message.set_name('QA', l_message_name);
1135 fnd_message.set_token('CHAR_ID', p_errors(i).element_id);
1136 fnd_message.set_token('CHAR_PROMPT', l_char_prompt);
1137 fnd_msg_pub.add();
1138 End If;
1139 END LOOP;
1140
1141 END post_error_messages;
1142
1143 --
1144 -- Used only by QWB and Fwk Integration
1145 -- /qadev/qa/12.0/java/ssqr/server/QualityResultsEOImpl.java
1146 --
1147 -- Pass -1 to p_transaction_number when using standalone.
1148 --
1149 --
1150 -- Bug 6881303
1151 -- added 2 new elements, one a comma separated list of the
1152 -- Parent collection elements that would receive aggregated
1153 -- values and the other a comma separated list of the
1154 -- aggregated values.
1155 -- ntungare Fri Mar 21 01:19:03 PDT 2008
1156 --
1157 FUNCTION ssqr_update_result(
1158 x_occurrence IN NUMBER,
1159 x_org_id IN NUMBER,
1160 x_plan_id IN NUMBER,
1161 x_spec_id IN NUMBER,
1162 x_collection_id IN NUMBER,
1163 x_txn_header_id IN NUMBER,
1164 x_par_plan_id IN NUMBER,
1165 x_par_col_id IN NUMBER,
1166 x_par_occ IN NUMBER,
1167 x_result IN VARCHAR2,
1168 x_result1 IN VARCHAR2, -- R12 Project MOAC 4637896, ID passing
1169 x_result2 IN VARCHAR2, -- not used yet, for future expansion
1170 x_enabled IN INTEGER,
1171 x_committed IN INTEGER,
1172 x_transaction_number IN NUMBER,
1173 x_messages OUT NOCOPY VARCHAR2,
1174 x_agg_elements OUT NOCOPY VARCHAR2,
1175 x_agg_val OUT NOCOPY VARCHAR2,
1176 p_last_update_date IN DATE DEFAULT SYSDATE)
1177 RETURN INTEGER IS
1178 elements qa_validation_api.ElementsArray;
1179 error_array qa_validation_api.ErrorArray;
1180 message_array qa_validation_api.MessageArray;
1181 return_status VARCHAR2(1);
1182 action_result VARCHAR2(1);
1183 msg_count NUMBER;
1184 msg_data VARCHAR2(2000);
1185 y_spec_id NUMBER;
1186 y_collection_id NUMBER;
1187 y_committed VARCHAR2(1);
1188
1189 -- anagarwa Wed May 26 17:07:29 PDT 2004
1190 -- bug 3667449
1191 l_ret_value VARCHAR2(1);
1192
1193 l_esig_status BOOLEAN;
1194
1195 --
1196 -- bug 6729769
1197 -- Record and a collection to get the details of History Child
1198 -- Plan plans for which data has been collected in a Txn
1199 -- ntungare
1200 --
1201 TYPE hist_plan_rec IS RECORD (plan_id NUMBER,
1202 collection_id NUMBER,
1203 occurrence NUMBER);
1204 TYPE hist_plan_tab_typ IS TABLE OF hist_plan_rec INDEX BY binary_integer;
1205 hist_plan_tab hist_plan_tab_typ ;
1206 BEGIN
1207 --messages should be cleared everytime a Submit is clicked on client
1208 fnd_msg_pub.Initialize();
1209 fnd_msg_pub.reset();
1210
1211 --
1212 -- Bug 2617638
1213 -- The original statement returns if x_result IS NULL.
1214 -- Undesirable if caller passes all validated IDs in x_result1.
1215 -- Added AND x_result1 IS NULL
1216 -- bso Tue Oct 8 18:34:38 PDT 2002
1217 --
1218 IF x_result IS NULL AND x_result1 IS NULL THEN
1219 RETURN -1;
1220 END IF;
1221
1222 IF x_committed = 1 THEN
1223 y_committed := fnd_api.g_true;
1224 ELSE
1225 y_committed := fnd_api.g_false;
1226 END IF;
1227
1228 --
1229 -- Some input can be -1, if that's the case, set to null
1230 --
1231 IF x_collection_id = -1 THEN
1232 y_collection_id := NULL;
1233 ELSE
1234 y_collection_id := x_collection_id;
1235 END IF;
1236 IF x_spec_id = -1 THEN
1237 y_spec_id := NULL;
1238 ELSE
1239 y_spec_id := x_spec_id;
1240 END IF;
1241
1242
1243 -- Bug 4502450. esig status support for multirow uqr
1244 -- check for validation before update
1245 -- saugupta Wed, 24 Aug 2005 08:49:45 -0700 PDT
1246 l_esig_status :=
1247 validate_esig_for_update(p_plan_id => x_plan_id,
1248 p_plan_collection_id => x_collection_id,
1249 p_plan_occurrence => x_occurrence);
1250 IF ( NOT l_esig_status ) THEN
1251 RETURN -1;
1252 END IF;
1253
1254
1255 --
1256 -- The flatten string is a representation that looks like this:
1257 --
1258 -- 10=Item@101=Defected@102=20 ...
1259 --
1260 -- namely, it is an @ separated list of charID=value. In case
1261 -- value contains @, then it is doubly encoded.
1262 --
1263 -- First task is to decode this string into the row_element
1264 -- array.
1265 --
1266 elements := qa_validation_api.result_to_array(x_result);
1267 elements := qa_validation_api.id_to_array(x_result1, elements);
1268
1269 IF (x_transaction_number IS NOT NULL) AND (x_transaction_number > 0) THEN
1270 qa_validation_api.set_validation_flag_txn(
1271 x_elements => elements,
1272 p_plan_id => x_plan_id,
1273 p_transaction_number => x_transaction_number,
1274 p_plan_transaction_id => NULL);
1275 END IF;
1276
1277 -- 12.1 QWB Usability Improvements
1278 -- Passing a new parameter p_ssqr_operation to indicate
1279 -- that the processing is being done for QWB application
1280 -- that would ensure that the Row Validation is not done
1281 -- again at the time of the data updation
1282 --
1283 qa_results_pub.update_row(
1284 p_api_version => 1.0,
1285 p_init_msg_list => fnd_api.g_true,
1286 p_commit => y_committed,
1287 p_plan_id => x_plan_id,
1288 p_spec_id => y_spec_id,
1289 p_org_id => x_org_id,
1290 p_transaction_number => x_transaction_number,
1291 p_transaction_id => null,
1292 p_txn_header_id => x_txn_header_id,
1293 p_enabled_flag => x_enabled,
1294 p_collection_id => y_collection_id,
1295 p_occurrence => x_occurrence,
1296 x_row_elements => elements,
1297 x_msg_count => msg_count,
1298 x_msg_data => msg_data,
1299 x_error_array => error_array,
1300 x_message_array => message_array,
1301 x_return_status => return_status,
1302 x_action_result => action_result,
1303 p_ssqr_operation => 1,
1304 p_last_update_date => p_last_update_date);
1305
1306 IF qa_validation_api.no_errors(error_array) THEN
1307
1308 -- anagarwa Fri Jan 23 12:10:04 PST 2004
1309 -- Bug 3384986 Actions for CAR master not fired when child is updated
1310 -- update parent and also update parent txn id so that the actions like
1311 -- sending an email can be fired.
1312 -- NOTE: In SsqrAMImpl.processEqrApply we already call
1313 -- setParentAttributes that recursively updates parent rows with the
1314 -- current txn header id. However, that is done only to set the
1315 -- completed status and it exits once it finds the completed flag for
1316 -- parent row. The following update parent causes some
1317 -- backward relationships to be fired and if there are actions associated
1318 -- with those then they should be fired too. So we set txn header id
1319 -- of the parent records so that actions get fired in
1320 -- qapcb.ssqr_post_commit
1321 IF(QA_PARENT_CHILD_PKG.update_parent(
1322 x_par_plan_id,
1323 x_par_col_id,
1324 x_par_occ,
1325 x_plan_id,
1326 x_collection_id,
1327 x_occurrence,
1328 x_txn_header_id,
1329 x_agg_elements,
1330 x_agg_val
1331 ) = 'T') THEN
1332 NULL;
1333 -- 12.1 QWB Usabiility Improvements
1334 -- Insert the History for Parent if it has been updated
1335 --
1336 -- bug 6936302
1337 -- This is not needed since OAF now calls the update
1338 -- method for the parent row whenever a child record
1339 -- is changed since R12.1 XBuid4 which automatically
1340 -- creates a History child for the parent
1341 -- ntungare
1342 --
1343 /*
1344 if ((x_enabled = 2 or x_enabled is null) AND
1345 x_agg_elements IS NOT NULL) then
1346 qa_parent_child_pkg.insert_history_auto_rec_QWB(
1347 p_plan_id => x_par_plan_id,
1348 p_collection_id => x_par_col_id,
1349 p_occurrence => x_par_occ,
1350 p_organization_id => x_org_id,
1351 p_txn_header_id => x_txn_header_id,
1352 p_relationship_type => 1,
1353 p_data_entry_mode => 4,
1354 x_status => return_status);
1355 end If;
1356 */
1357 END IF;
1358
1359 get_action_messages(message_array, x_plan_id, x_messages);
1360
1361 -- Bug 3536025. Calling new procedure insert_history_auto_rec_QWB
1362 -- instead of insert_history_auto_rec. The new procedure is same as
1363 -- the old one except it doesnot changes child plan's txn_header_id
1364 -- and doesnot fire actions for child plans.
1365 -- srhariha. Wed May 26 22:31:28 PDT 2004.
1366
1367 /*
1368 qa_parent_child_pkg.insert_history_auto_rec_QWB(x_plan_id,
1369 x_txn_header_id,
1370 1,
1371 4);
1372 */
1373
1374 -- Bug 4343758. OA Framework Integration project.
1375 -- Shouldnt create history and automatic for txn scenario.
1376 -- srhariha. Wed May 18 04:34:53 PDT 2005.
1377
1378 -- Bug 3681815. Changing the call to proc below as the signature got
1379 -- changed due to the bug.
1380 -- commenting the previous calls to proc above.
1381 -- saugupta Tue, 15 Jun 2004 05:51:00 -0700 PDT
1382
1383 -- insert history records
1384
1385 if (x_enabled = 2 or x_enabled is null) then
1386 --
1387 -- bug 6729769
1388 -- Check if History Child records already
1389 -- exist for this Txn in which case a new
1390 -- record is not to be created. The existing
1391 -- hist records would be updated.
1392 -- ntungare
1393 --
1394 SELECT b.child_plan_id,
1395 b.child_collection_id,
1396 b.child_occurrence
1397 BULK COLLECT INTO hist_plan_tab
1398 FROM qa_pc_results_relationship b, qa_pc_plan_relationship a
1399 WHERE a.parent_plan_id = x_plan_id
1400 AND a.parent_plan_id = b.parent_plan_id
1401 AND a.child_plan_id = b.child_plan_id
1402 AND a.data_entry_mode = 4
1403 AND b.parent_collection_id = y_collection_id
1404 AND b.parent_occurrence = x_occurrence
1405 AND b.child_txn_header_id = x_txn_header_id;
1406
1407 -- If the Hist plan data not collected then
1408 -- insert the history records
1409 IF (hist_plan_tab.count = 0) THEN
1410 qa_parent_child_pkg.insert_history_auto_rec_QWB(p_plan_id => x_plan_id,
1411 p_collection_id => y_collection_id,
1412 p_occurrence => x_occurrence,
1413 p_organization_id => x_org_id,
1414 p_txn_header_id => x_txn_header_id,
1415 p_relationship_type => 1,
1416 p_data_entry_mode => 4,
1417 x_status => return_status);
1418 ELSE
1419 --
1420 -- bug 6729769
1421 -- for history records that are present we need to
1422 -- update them to ensure that the aggregated values
1423 -- reflect in them
1424 -- ntungare
1425 FOR hist_plan_cntr in 1..hist_plan_tab.count
1426 LOOP
1427 l_ret_value:= QA_PARENT_CHILD_PKG.update_child(x_plan_id,
1428 y_collection_id,
1429 x_occurrence,
1430 hist_plan_tab(hist_plan_cntr).plan_id,
1431 hist_plan_tab(hist_plan_cntr).collection_id,
1432 hist_plan_tab(hist_plan_cntr).occurrence);
1433 END LOOP;
1434 END IF;
1435 end if;
1436
1437
1438
1439 -- anagarwa Wed May 26 17:07:29 PDT 2004
1440 -- bug 3667449
1441 -- if parent copy source element is updated, then update the
1442 -- child too.
1443 -- we need to check and call both the methods because of following
1444 -- scenario. A is parent of B is parent of C is parent of D.
1445 -- Element X is copied from B to C to D. If D is updated and
1446 -- X is updated in B , we want both C and D to be updated.
1447
1448 IF (x_par_occ > 0 ) THEN
1449 l_ret_value:= QA_PARENT_CHILD_PKG.update_child(x_par_plan_id,
1450 x_par_col_id,
1451 x_par_occ,
1452 x_plan_id,
1453 x_collection_id,
1454 x_occurrence);
1455 ELSE
1456 l_ret_value := QA_PARENT_CHILD_PKG.update_all_children(x_plan_id,
1457 x_collection_id, x_occurrence);
1458 END IF;
1459
1460 RETURN 0;
1461 ELSIF return_status <> FND_API.G_RET_STS_ERROR AND
1462 return_status <> FND_API.G_RET_STS_UNEXP_ERROR THEN
1463 get_error_messages(error_array, x_plan_id, x_messages);
1464 END IF;
1465
1466 RETURN -1;
1467
1468 END ssqr_update_result;
1469
1470 -- bug 5306909
1471 -- Added p_last_update_date parameter. This parameter is used
1472 -- to check whether the record which the user is trying to
1473 -- update has been updated already by some other user.
1474 -- ntungare Mon Apr 10 07:03:13 PDT 2006
1475 --
1476 FUNCTION ssqr_lock_row (
1477 p_occurrence IN NUMBER,
1478 p_plan_id IN NUMBER,
1479 p_last_update_date IN DATE,
1480 x_status OUT NOCOPY VARCHAR2)
1481 RETURN INTEGER IS
1482
1483 l_occurrence NUMBER;
1484
1485 -- Bug 5306909
1486 -- Modified the cursor definition to fetch the
1487 -- last_update_date
1488 -- ntungare Mon Apr 10 07:03:13 PDT 2006
1489 --
1490 CURSOR upd_cur IS
1491 SELECT occurrence, last_update_date
1492 FROM qa_results
1493 WHERE occurrence = p_occurrence
1494 AND plan_id = p_plan_id
1495 FOR UPDATE NOWAIT;
1496
1497 -- Bug 5306909
1498 -- New variable to get the last_update_date
1499 -- ntungare Mon Apr 10 07:03:13 PDT 2006
1500 --
1501 l_last_update_date DATE;
1502
1503 BEGIN
1504
1505 x_status := '1';
1506 OPEN upd_cur;
1507
1508 --
1509 -- Bug 5306909
1510 -- Modified the Fetch statement to fetch
1511 -- the last_update_date
1512 -- ntungare Mon Apr 10 07:15:42 PDT 2006
1513 --
1514 FETCH upd_cur INTO l_occurrence, l_last_update_date;
1515
1516 IF (upd_cur%NOTFOUND) THEN
1517 RETURN -1;
1518 END IF;
1519
1520 CLOSE upd_cur;
1521
1522 -- bug 5306909
1523 -- If the p_last_update_date is not equal to what is
1524 -- there in the database (l_last_update_date) then it
1525 -- means the record has been updated by some other user.
1526 -- So we return -1, to QualityResultsEOImpl.java which
1527 -- would display the seeded QA_SSQR_LOCK_FAILED error
1528 -- message.
1529 -- ntungare Mon Apr 10 07:06:39 PDT 2006
1530 --
1531 IF (p_last_update_date <> l_last_update_date) THEN
1532 RETURN -1;
1533 END IF;
1534
1535 RETURN 1;
1536
1537 EXCEPTION when others then
1538 IF upd_cur%ISOPEN THEN
1539 CLOSE upd_cur;
1540 RETURN -1;
1541 ELSE
1542 RETURN -1;
1543 END IF;
1544 --raise;
1545
1546 END ssqr_lock_row;
1547
1548
1549 --
1550 -- Removed procedure wb_set_valid_flag entirely because
1551 -- the new qa_validation_api.set_validation_flag_txn is a superset.
1552 --
1553
1554 --
1555 -- Used only by QWB and Fwk Integration
1556 -- /qadev/qa/12.0/java/ssqr/server/QualityResultsEOImpl.java
1557 --
1558 -- Pass -1 to p_transaction_number when using standalone.
1559 --
1560 -- 12.1 QWB Usability Improvement
1561 -- Added 2 new parameters x_charid_str and x_id_str
1562 -- TO return the list of Id values for the HC elements
1563 --
1564 FUNCTION ssqr_validate_row (
1565 p_occurrence IN OUT NOCOPY NUMBER,
1566 p_org_id IN NUMBER,
1567 p_plan_id IN NUMBER,
1568 p_spec_id IN NUMBER,
1569 p_collection_id IN NUMBER,
1570 p_result IN VARCHAR2,
1571 p_result1 IN VARCHAR2, -- R12 Project MOAC 4637896, ID passing
1572 p_result2 IN VARCHAR2, -- not used yet, for future expansion
1573 p_enabled IN INTEGER,
1574 p_committed IN INTEGER,
1575 p_transaction_number IN NUMBER,
1576 p_transaction_id IN NUMBER DEFAULT 0,
1577 x_messages OUT NOCOPY VARCHAR2,
1578 x_charid_str OUT NOCOPY VARCHAR2,
1579 x_id_str out NOCOPY VARCHAR2)
1580 RETURN INTEGER IS
1581
1582 elements qa_validation_api.ElementsArray;
1583 error_array qa_validation_api.ErrorArray;
1584 l_results_array qa_validation_api.ResultRecordArray;
1585 l_message_array qa_validation_api.MessageArray;
1586
1587 --
1588 -- Bug 5932426
1589 -- Checing if the Txn is an update Txn or an
1590 -- Insert Txn. If the txn is update, then the
1591 -- Plan_id - Collection_id - Occurrence combination
1592 -- would be present in the QA_RESULTS table.
1593 -- ntungare Sat Apr 14 00:53:40 PDT 2007
1594 --
1595 Cursor C1 is
1596 Select 1 from QA_RESULTS
1597 WHERE plan_id = p_plan_id
1598 and collection_id = p_collection_id
1599 and occurrence = p_occurrence;
1600
1601 update_txn PLS_INTEGER := 0;
1602
1603 -- 12.1 QWB Usability Improvements
1604 --
1605 id_ctr Number;
1606 charid_str VARCHAR2(4000);
1607 id_str VARCHAR2(4000);
1608
1609
1610 BEGIN
1611
1612 --clearing cache so that errors are not shown over and over again.
1613 fnd_msg_pub.Initialize();
1614 fnd_msg_pub.reset();
1615 elements := qa_validation_api.result_to_array(p_result);
1616 elements := qa_validation_api.id_to_array(p_result1, elements);
1617
1618 --
1619 -- Bug 5932426
1620 -- Updating the validation flag only if the
1621 -- Txn is an update txn
1622 -- ntungare Sat Apr 14 00:55:08 PDT 2007
1623 --
1624 Open C1;
1625 Fetch C1 into update_txn;
1626 Close C1;
1627
1628 If update_txn = 1 then
1629 update_validation_flg(elements,
1630 p_plan_id,
1631 p_collection_id,
1632 p_occurrence);
1633 update_txn := 0;
1634 End If;
1635
1636 --
1637 -- Replaced the following by set_validation_flag_txn which
1638 -- is a superset of wb_set_valid_flag.
1639 --
1640 -- Bug 4519558. OA Framework Integration project. UT bug fix.
1641 -- Set validation flag.
1642 -- srhariha. Tue Aug 2 22:38:59 PDT 2005.
1643 -- wb_set_valid_flag(p_elements => elements,
1644 -- p_transaction_number => p_transaction_number);
1645 --
1646 IF (p_transaction_number IS NOT NULL) AND (p_transaction_number > 0) THEN
1647 qa_validation_api.set_validation_flag_txn(
1648 x_elements => elements,
1649 p_plan_id => p_plan_id,
1650 p_transaction_number => p_transaction_number,
1651 p_plan_transaction_id => NULL);
1652 END IF;
1653
1654 -- 12.1 QWB Usability Improvements
1655 -- Passing the value for the P_ssqr_operation flag as
1656 -- 1 to indicate that the online actions firing is
1657 -- not be done
1658 --
1659 error_array := qa_validation_api.validate_row(p_plan_id,
1660 p_spec_id, p_org_id, fnd_global.user_id, p_transaction_number,
1661 p_transaction_id, l_results_array, l_message_array,
1662 elements, 1);
1663
1664 -- 12.1 QWB Usability Improvements
1665 -- If the IDs values are present for HC elements then
1666 -- the build a string of the Id elements
1667 id_ctr := l_results_array.first;
1668 while id_ctr <= l_results_array.last
1669 loop
1670 If l_results_array(id_ctr).id IS NOT NULL THEN
1671 -- Append the HC element column name to the char id string
1672 charid_str := charid_str ||
1673 qa_chars_api.hardcoded_column(l_results_array(id_ctr).element_id)||
1674 ',';
1675 -- Append the Id value to Normalized id string
1676 id_str := id_str || l_results_array(id_ctr).id||',';
1677 End If;
1678 id_ctr := l_results_array.next(id_ctr);
1679 end loop;
1680
1681 If charid_str IS NOT NULL THEN
1682 charid_str := LTRIM(RTRIM(charid_str,','),',');
1683 id_str := LTRIM(RTRIM(id_str,','),',');
1684 End If;
1685
1686 x_charId_str := charid_str;
1687 x_Id_str := id_str;
1688
1689 -- End of 12.1 QWB Usability Improvements
1690
1691 IF qa_validation_api.no_errors(error_array) THEN
1692 get_action_messages(l_message_array, p_plan_id, x_messages);
1693 RETURN 0;
1694 ELSE
1695 get_error_messages(error_array, p_plan_id, x_messages);
1696 post_error_messages(error_array, p_plan_id);
1697 END IF;
1698
1699 RETURN -1;
1700 END ssqr_validate_row;
1701
1702 --
1703 -- Delete a result.
1704 --
1705 PROCEDURE delete_result(
1706 x_plan_id IN NUMBER,
1707 x_collection_id IN NUMBER,
1708 x_occurrence IN NUMBER) IS
1709 BEGIN
1710 DELETE FROM qa_results
1711 WHERE plan_id = x_plan_id AND
1712 collection_id = x_collection_id AND
1713 occurrence = x_occurrence;
1714 END delete_result;
1715
1716 --
1717 -- Batch delete a set of results (supply occurrences in
1718 -- comma-separated list.)
1719 --
1720 PROCEDURE delete_results(
1721 x_plan_id IN NUMBER,
1722 x_collection_id IN NUMBER,
1723 x_occurrences IN VARCHAR2) IS
1724
1725 c_key CONSTANT VARCHAR2(30) := 'QA_SS_RESULTS.DELETE_RESULTS';
1726
1727 BEGIN
1728 --
1729 -- For SQL Bind Compliance, change dynamic SQL to
1730 -- static using qa_performance_temp table
1731 -- bso Sat Oct 1 13:03:17 PDT 2005
1732 --
1733 qa_performance_temp_pkg.add_ids(c_key, x_occurrences);
1734
1735 DELETE FROM qa_results
1736 WHERE plan_id = x_plan_id AND
1737 collection_id = x_collection_id AND
1738 occurrence IN
1739 (SELECT id
1740 FROM qa_performance_temp
1741 WHERE key = c_key);
1742
1743 qa_performance_temp_pkg.purge(c_key);
1744 END delete_results;
1745
1746 --
1747 -- Perform database commit. Do not use in transaction integration,
1748 -- otherwise we will be committing the parent's data without their
1749 -- knowing! Actions will be fired in the background.
1750 --
1751 PROCEDURE commit_results IS
1752 BEGIN
1753 commit;
1754 --
1755 -- work on action later.
1756 --
1757 END commit_results;
1758
1759 PROCEDURE wrapper_fire_action (
1760 q_collection_id IN NUMBER,
1761 q_return_status OUT NOCOPY VARCHAR2,
1762 q_msg_count OUT NOCOPY NUMBER,
1763 q_msg_data OUT NOCOPY VARCHAR2)
1764 IS
1765
1766 BEGIN
1767
1768 qa_results_pub.enable_and_fire_action (
1769 p_api_version => 1.0,
1770 p_commit => FND_API.G_TRUE,
1771 p_collection_id => q_collection_id,
1772 x_return_status => q_return_status,
1773 x_msg_count => q_msg_count,
1774 x_msg_data => q_msg_data);
1775
1776 END wrapper_fire_action;
1777
1778
1779 PROCEDURE GET_COLLECTION_ID (x_collection_id OUT NOCOPY NUMBER)
1780 IS
1781 CURSOR cid_cur IS
1782 SELECT QA_COLLECTION_ID_S.NEXTVAL FROM DUAL;
1783 BEGIN
1784 OPEN cid_cur;
1785 FETCH cid_cur INTO x_collection_id;
1786 CLOSE cid_cur;
1787
1788 EXCEPTION when others then
1789 IF cid_cur%ISOPEN THEN
1790 CLOSE cid_cur;
1791 END IF;
1792 raise;
1793 END GET_COLLECTION_ID;
1794
1795
1796 -- Bug 4502450. R12 eSig Status functionality for PENDING Status
1797 -- function checks for eSignature elements present for current
1798 -- plan as well as for all the parent plans
1799 -- if status is not pending i.e if update is allowed by any means
1800 -- returns TRUE else returns FALSE
1801 -- sets the message if eSig Status is PENDING for any row for
1802 -- the current plan relationship
1803 -- saugupta Wed, 24 Aug 2005 08:50:00 -0700 PDT
1804 --
1805 -- Reformatted indentation and cases to make it easier
1806 -- to read. Syntactically equivalent to previous revision.
1807 -- bso Sat Oct 1 13:16:08 PDT 2005
1808
1809 FUNCTION validate_esig_for_update(
1810 p_plan_id IN NUMBER,
1811 p_plan_collection_id IN NUMBER,
1812 p_plan_occurrence IN NUMBER)
1813 RETURN BOOLEAN
1814 IS
1815 l_eres_profile VARCHAR2(3);
1816 l_esig_status VARCHAR(20);
1817 l_par_esig_status VARCHAR(20);
1818 l_ancestors_exists VARCHAR(1);
1819 i NUMBER;
1820 parent_plan_ids dbms_sql.number_table;
1821 parent_collection_ids dbms_sql.number_table;
1822 parent_occurrences dbms_sql.number_table;
1823
1824 BEGIN
1825
1826 l_eres_profile := fnd_profile.value('EDR_ERES_ENABLED');
1827 -- Check if the profile is on if not return TRUE
1828 IF ( l_eres_profile IS NULL OR l_eres_profile = 'N' ) THEN
1829 RETURN TRUE;
1830 END IF;
1831
1832
1833 -- check if eSig Status is pending for current plan
1834 l_esig_status := qa_eres_util.is_esig_status_pending(
1835 p_plan_id => p_plan_id ,
1836 p_collection_id => p_plan_collection_id,
1837 p_occurrence => p_plan_occurrence);
1838
1839
1840 IF ( l_esig_status = 'T' ) THEN
1841 -- current plan has eSig status as PENDING
1842 -- fill the error array and return
1843 fnd_message.set_name('QA', 'QA_ERES_CANNOT_UPDATE_RESULT');
1844 fnd_msg_pub.add();
1845 RETURN FALSE;
1846 END IF; -- l_esig_status = T
1847 -- esig status is false for child plan
1848 -- check esig status in ancestor plans
1849 -- before check if ancestor plans exists
1850
1851 l_ancestors_exists := qa_parent_child_pkg.get_ancestors(
1852 p_child_plan_id => p_plan_id,
1853 p_child_occurrence => p_plan_occurrence,
1854 p_child_collection_id => p_plan_collection_id,
1855 x_parent_plan_ids => parent_plan_ids,
1856 x_parent_collection_ids => parent_collection_ids,
1857 x_parent_occurrences => parent_occurrences);
1858 -- if ancestors does not exists, meaning record is updateable
1859 IF ( l_ancestors_exists = 'F' ) THEN
1860 RETURN TRUE;
1861 END IF;
1862
1863 -- if not, we need to check esig Status for all ancestors
1864 i := parent_occurrences.FIRST;
1865 l_par_esig_status := 'F';
1866 WHILE i IS NOT NULL LOOP
1867 l_par_esig_status := qa_eres_util.is_esig_status_pending(
1868 p_plan_id => parent_plan_ids(i) ,
1869 p_collection_id => parent_collection_ids(i),
1870 p_occurrence => parent_occurrences(i));
1871 EXIT WHEN l_par_esig_status = 'T';
1872 i := parent_occurrences.NEXT(i);
1873 END LOOP; -- while i is not null
1874
1875 IF ( l_par_esig_status = 'F' ) THEN -- no parent has status pending
1876 RETURN TRUE;
1877 END IF;
1878 -- current plan ancestors has eSig status as PENDING
1879 -- fill the error array and return
1880 fnd_message.set_name('QA', 'QA_ERES_CANNOT_UPDATE_RESULT');
1881 fnd_msg_pub.add();
1882 return FALSE;
1883
1884 END validate_esig_for_update;
1885
1886 -- bug 4658275. eSig functionality support in QWB
1887 -- this new method checks if user can insert a new
1888 -- child row if ERES is enables
1889 -- saugupta Tue, 18 Oct 2005 02:55:19 -0700 PDT
1890 FUNCTION validate_esig_for_insert(p_plan_id IN NUMBER,
1891 p_plan_collection_id IN NUMBER,
1892 p_plan_occurrence IN NUMBER)
1893 RETURN BOOLEAN
1894 IS
1895 l_eres_profile VARCHAR2(3);
1896 l_esig_status VARCHAR(20);
1897 l_par_esig_status VARCHAR(20);
1898 l_ancestors_exists VARCHAR(1);
1899 i NUMBER;
1900 parent_plan_ids dbms_sql.number_table;
1901 parent_collection_ids dbms_sql.number_table;
1902 parent_occurrences dbms_sql.number_table;
1903
1904 BEGIN
1905
1906 l_eres_profile := FND_PROFILE.VALUE('EDR_ERES_ENABLED');
1907 -- Check if the profile is on if not return TRUE
1908 IF ( l_eres_profile IS NULL OR l_eres_profile = 'N' ) THEN
1909 return TRUE;
1910 END IF;
1911
1912 -- check if eSig Status is pending for parent plan
1913 -- in the method we are passing the parent plan id's
1914 -- as we can add a new row with eSignature status
1915 -- is pending but not when eSig Status is pending
1916 -- for the parnet plan
1917
1918 l_esig_status :=
1919 QA_ERES_UTIL.is_esig_status_pending(p_plan_id => p_plan_id ,
1920 p_collection_id => p_plan_collection_id,
1921 p_occurrence => p_plan_occurrence);
1922
1923
1924 IF ( l_esig_status = 'T' ) THEN
1925 -- current plan has eSig status as PENDING
1926 -- fill the error array and return
1927 fnd_message.set_name('QA', 'QA_ERES_CANNOT_ENTER_CHILD');
1928 fnd_msg_pub.add();
1929 return FALSE;
1930 END IF; -- l_esig_status = T
1931
1932
1933 -- esig status is false for child plan
1934 -- check esig status in ancestor plans
1935 -- before check if ancestor plans exists
1936 l_ancestors_exists :=
1937 QA_PARENT_CHILD_PKG.get_ancestors(p_child_plan_id => p_plan_id,
1938 p_child_occurrence => p_plan_occurrence,
1939 p_child_collection_id => p_plan_collection_id,
1940 x_parent_plan_ids => parent_plan_ids,
1941 x_parent_collection_ids => parent_collection_ids,
1942 x_parent_occurrences => parent_occurrences);
1943 -- if ancestors does not exists, meaning record can be inserted
1944
1945 IF ( l_ancestors_exists = 'F' ) THEN
1946 return TRUE;
1947 END IF;
1948
1949 -- if not, we need to check esig Status for all ancestors
1950 i := parent_occurrences.FIRST;
1951 l_par_esig_status := 'F';
1952 WHILE i IS NOT NULL
1953 LOOP
1954 l_par_esig_status :=
1955 QA_ERES_UTIL.is_esig_status_pending(p_plan_id => parent_plan_ids(i) ,
1956 p_collection_id => parent_collection_ids(i),
1957 p_occurrence => parent_occurrences(i));
1958 EXIT WHEN l_par_esig_status = 'T';
1959 i := parent_occurrences.NEXT(i);
1960 END LOOP; -- while i is not null
1961
1962 IF ( l_par_esig_status = 'F' ) THEN -- no parent has status pending
1963 return TRUE;
1964 END IF;
1965 -- current plan ancestors has eSig status as PENDING
1966 -- fill the error array and return
1967 fnd_message.set_name('QA', 'QA_ERES_CANNOT_ENTER_CHILD');
1968 fnd_msg_pub.add();
1969 return FALSE;
1970
1971 END validate_esig_for_insert;
1972
1973 -- R12.1 QWB Usability Improvements project
1974 -- Function to perform deletetion of rows
1975 --
1976 FUNCTION delete_row(p_plan_id IN NUMBER,
1977 p_collection_id IN NUMBER,
1978 p_occurrence IN NUMBER,
1979 p_org_id IN NUMBER,
1980 p_txn_header_id IN NUMBER,
1981 p_par_plan_id IN NUMBER DEFAULT -1,
1982 p_par_col_id IN NUMBER DEFAULT -1,
1983 p_par_occ IN NUMBER DEFAULT -1)
1984 RETURN VARCHAR2 AS
1985 delete_api_ret_val VARCHAR2(1);
1986 insert_api_ret_val VARCHAR2(1);
1987 CURSOR check_agg_rel_cur IS
1988 SELECT 1
1989 FROM qa_pc_result_columns_v
1990 WHERE parent_plan_id = p_par_plan_id
1991 AND child_plan_id = p_plan_id
1992 AND element_relationship_type in (2,3,4,5,6,7,8)
1993 AND parent_enabled_flag = 1
1994 AND child_enabled_flag = 1;
1995
1996 agg_rel_flag NUMBER;
1997 BEGIN
1998 delete_api_ret_val := QA_PARENT_CHILD_PKG.delete_row (
1999 p_plan_id => p_plan_id,
2000 p_collection_id => p_collection_id,
2001 p_occurrence => p_occurrence);
2002
2003 -- Check for successful deletion
2004 IF (delete_api_ret_val = 'T') THEN
2005 -- Check if a parent record exists for the current record
2006 -- and if an aggregate relationship exists between then in which
2007 -- case History record needs to be created for the parent
2008 --
2009 IF (p_par_plan_id <> -1 AND
2010 p_par_col_id <> -1 AND
2011 p_par_occ <> -1) THEN
2012
2013 OPEN check_agg_rel_cur;
2014 FETCH check_agg_rel_cur INTO agg_rel_flag;
2015 CLOSE check_agg_rel_cur;
2016
2017 IF (agg_rel_flag = 1) THEN --Aggregate relation exists
2018 -- Hence insert history record for the parent
2019 -- as the deletion of the child record would
2020 -- impact the aggregated value on the parent.
2021 qa_parent_child_pkg.insert_history_auto_rec_QWB(
2022 p_plan_id => p_par_plan_id,
2023 p_collection_id => p_par_col_id,
2024 p_occurrence => p_par_occ,
2025 p_organization_id => p_org_id,
2026 p_txn_header_id => p_txn_header_id,
2027 p_relationship_type => 1,
2028 p_data_entry_mode => 4,
2029 x_status => insert_api_ret_val);
2030 END IF;
2031 END IF;
2032 END IF;
2033
2034 RETURN 'T';
2035 END;
2036
2037 --anagarwa 3197700 Wed Oct 15 17:38:56 PDT 2003
2038
2039 BEGIN
2040
2041 populate_message_table;
2042 END qa_ss_results;
[Home] [Help]