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