1 PACKAGE BODY qa_results_api AS
2 /* $Header: qltrsiub.plb 120.7.12010000.1 2008/07/25 09:22:15 appldev ship $ */
3
4
5 PROCEDURE enable_and_fire_action (p_collection_id IN NUMBER ) IS
6
7 BEGIN
8
9 UPDATE qa_results
10 SET status=2
11 WHERE collection_id = p_collection_id;
12
13 commit_qa_results(p_collection_id);
14
15 END enable_and_fire_action;
16
17
18 PROCEDURE commit_qa_results(p_collection_id IN NUMBER ) IS
19
20 actions_request_id NUMBER;
21
22 BEGIN
23
24 COMMIT;
25
26 --
27 -- Bug 1580498. The concurrent program qltactwb always passed
28 -- TXN_HEADER_ID as argument to do_actions. This is not correct
29 -- in the api situation. In order to make minimal impact to
30 -- testing and release, I am now passing a negative collection ID
31 -- to qltactwb to indicate COLLECTION_ID instead of TXN_HEADER_ID
32 -- be passed to the do_actions function.
33 -- bso Thu Jan 11 19:22:51 PST 2001
34 --
35
36 actions_request_id := fnd_request.submit_request('QA', 'QLTACTWB', NULL,
37 NULL, FALSE, to_char(-p_collection_id));
38
39 COMMIT;
40
41 END commit_qa_results;
42
43 --
44 -- 12.1 QWB Usability Improvements
45 -- added a new parameter, p_ssqr_opperation to ensure
46 -- that the validation is not done again at the time of
47 -- inserting rows through QWB
48 --
49 FUNCTION insert_row( p_plan_id IN NUMBER,
50 p_spec_id IN NUMBER DEFAULT NULL,
51 p_org_id IN NUMBER,
52 p_transaction_number IN NUMBER DEFAULT NULL,
53 p_transaction_id IN NUMBER DEFAULT 0,
54 p_collection_id IN OUT NOCOPY NUMBER,
55 p_who_last_updated_by IN NUMBER := fnd_global.user_id,
56 p_who_created_by IN NUMBER := fnd_global.user_id,
57 p_who_last_update_login IN NUMBER := fnd_global.user_id,
58 p_enabled_flag IN NUMBER,
59 p_commit_flag IN BOOLEAN DEFAULT FALSE,
60 p_error_found OUT NOCOPY BOOLEAN,
61 p_occurrence IN OUT NOCOPY NUMBER,
62 p_do_action_return OUT NOCOPY BOOLEAN,
63 p_message_array OUT NOCOPY qa_validation_api.MessageArray,
64 p_row_elements IN OUT NOCOPY qa_validation_api.ElementsArray,
65 p_txn_header_id IN NUMBER DEFAULT NULL,
66 p_ssqr_operation IN NUMBER DEFAULT NULL,
67 p_last_update_date IN DATE DEFAULT SYSDATE)
68 RETURN qa_validation_api.ErrorArray IS
69
70
71
72 return_results_array qa_validation_api.ResultRecordArray;
73 master_error_list qa_validation_api.ErrorArray;
74
75 insert_string varchar2(6000) := null;
76 value_string varchar2(30000) := null;
77 sql_string varchar2(32000) := null;
78 column_name varchar2(240) := null;
79 column_value varchar2(2000);
80
81 i NUMBER;
82 j NUMBER;
83 x_collection_id NUMBER;
84 x_occurrence NUMBER;
85 x_txn_header_id NUMBER;
86 actions_request_id NUMBER;
87 x_sysdate DATE;
88
89 c INTEGER; /* cursor handler */
90 insert_dbms_sql_feedback INTEGER;
91
92 -- 12.1 QWB Usability Improvements
93 charctr NUMBER;
94
95 --
96 -- bug 6933282
97 -- Variable to hold the decimal precision
98 -- defined for a numeric element.
99 -- ntungare
100 --
101 l_precision NUMBER;
102 BEGIN
103 x_collection_id := p_collection_id;
104
105 IF x_collection_id IS NULL THEN
106 SELECT QA_COLLECTION_ID_S.NEXTVAL INTO x_collection_id FROM DUAL;
107 p_collection_id := x_collection_id;
108 END IF;
109
110 -- Ankur: Following logic makes sure that if OCCURRENCE is an IN parameter
111 -- then we put this value else we select next sequence value.
112 x_occurrence := p_occurrence;
113
114 IF x_occurrence IS NULL THEN
115 SELECT QA_OCCURRENCE_S.NEXTVAL INTO x_occurrence FROM DUAL;
116 p_occurrence := x_occurrence;
117 END IF;
118
119 -- Bug 2290747. In Parent-Child scanario History plan record is
120 -- inserted with status 1. When parent plan gets saved, history
121 -- record gets enabled ie status = 2. In UQR mode, enabling is
122 -- done using txn_header_id. For this parent and history record
123 -- should have the same txn_header_id. In order to achieve this
124 -- txn_header_id is passed from the form.
125 -- rponnusa Mon Apr 1 22:25:49 PST 2002
126
127 IF p_txn_header_id IS NULL THEN
128 SELECT sysdate,
129 MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
130 INTO x_sysdate,
131 x_txn_header_id
132 FROM DUAL;
133 ELSE
134 SELECT sysdate INTO x_sysdate FROM DUAL;
135 x_txn_header_id := p_txn_header_id;
136 END IF;
137
138 -- 12.1 QWB Usability Improvements
139 -- If the value of the variable p_ssqr_operation is 1 it means
140 -- that the data insert is happening from a standalone QWB application, in
141 -- which case the validations is not needed since its done online however
142 -- in case of an OAF Txn Integration the valiadtion needs to be done to
143 -- derive the Id values for the context elements
144 --
145 If (p_ssqr_operation IS NULL OR
146 p_ssqr_operation = 2) THEN
147 master_error_list := qa_validation_api.validate_row(
148 p_plan_id,
149 p_spec_id,
150 p_org_id,
151 p_who_created_by,
152 p_transaction_number,
153 p_transaction_id,
154 return_results_array,
155 p_message_array,
156 p_row_elements,
157 p_ssqr_operation);
158 END IF;
159 -- In case the call is from the self-service application
160 -- then the results_record_arry needs to be populated
161 --
162 IF (p_ssqr_operation = 1 OR
163 p_ssqr_operation = 2) THEN
164 -- For an ssqr operation the validations are not needed. Also
165 -- all the online actions except those corresponding to the
166 -- value not entered trigger conditiion would not be fired.
167 -- hence making a call to the api processNotEnteredActions that
168 -- would process those action conditions
169 --
170 master_error_list := qa_validation_api.processNotEnteredActions(
171 p_plan_id => p_plan_id,
172 p_spec_id => p_spec_id,
173 p_ssqr_operation => p_ssqr_operation,
174 p_row_elements => p_row_elements,
175 p_return_results_array => return_results_array,
176 message_array => p_message_array);
177
178 END If;
179 IF qa_validation_api.no_errors(master_error_list) THEN
180
181 p_error_found := FALSE;
182
183 -- construct INSERT sql part
184 -- e.g insert statement trying to build INSERT INTO
185 -- qa_results (collection_id, occurrence) VALUES (111, 222);
186
187 insert_string := 'INSERT INTO qa_results (
188 collection_id,
189 occurrence,
190 last_update_date,
191 qa_last_update_date,
192 last_updated_by,
193 qa_last_updated_by,
194 creation_date,
195 qa_creation_date,
196 created_by,
197 last_update_login,
198 qa_created_by,
199 status,
200 transaction_number,
201 organization_id,
202 plan_id,
203 spec_id,
204 transaction_id,
205 txn_header_id ';
206
207
208 -- construct VALUES sql part
209 -- anagarwa: a bind variable current_date is introduced
210 -- to store time in MM-DD-YYYY HH24:MI:SS format.
211 -- This is a fix for bug #1691501 and 1708891(duplicate)
212 -- If we don't use bind variable but use x_sysdate or sysdate
213 -- the date is inserted as MM-DD-YYYY 00:00:00.
214 -- For example on 6th Apr, 2001 the date inserted will be
215 -- 06-06-2001 00:00:00
216 --
217 -- Bug 5912439.
218 -- Performance issue due to the use of literals in the SQL below.
219 -- Modified the SQL string to include bind variables. This is used
220 -- to insert data in QA_RESULTS.
221 -- skolluku Mon Apr 23 23:39:10 PDT 2007.
222 --
223
224 -- construct VALUES sql part
225 /*value_string:=' VALUES ('||x_collection_id||', '||
226 x_occurrence||', '||
227 ':current_date'||', '||
228 ':current_date'||', '||
229 ''''||p_who_last_updated_by||''''|| ', '||
230 ''''||p_who_last_updated_by||''''||', '||
231 ':current_date'||', '||
232 ':current_date'||', '||
233 ''''||p_who_created_by||''''||', '||
234 ''''|| to_char(p_who_last_update_login)||''''||', '||
235 ''''||p_who_created_by||''''||', '||
236 p_enabled_flag || ', '||
237 nvl(to_char(p_transaction_number),'NULL')||', '||
238 to_char(p_org_id)||', '||
239 to_char(p_plan_id)||', '||
240 nvl(to_char(p_spec_id),'NULL')||', '||
241 nvl(to_char(p_transaction_id),'NULL')||', '||
242 x_txn_header_id;*/
243
244 value_string:=' VALUES (:x_collection_id, :x_occurrence, :current_date, '
245 ||':current_date, :p_who_last_updated_by, '
246 ||':p_who_last_updated_by, :current_date, :current_date, '
247 ||':p_who_created_by, :p_who_last_update_login, '
248 ||':p_who_created_by, :p_enabled_flag, :p_transaction_number, '
249 ||':p_org_id, :p_plan_id, :p_spec_id, '
250 ||':p_transaction_id, :x_txn_header_id';
251
252
253
254 i := return_results_array.first;
255 j := 0;
256
257 WHILE (i <= return_results_array.last) LOOP
258
259 j := j+1;
260
261 --
262 -- Bug 3402251. This small inefficiency was found during
263 -- this bug fix, return_results_array(i).element_id is
264 -- always the same as i. In some rare assign-a-value case
265 -- element_id was not populated. So it is more correct to
266 -- use i as the second parameter here.
267 -- bso Mon Feb 9 21:42:46 PST 2004
268 --
269 column_name := qa_plan_element_api.get_result_column_name(
270 p_plan_id, i);
271
272 -- anagarwa Fri Dec 20 18:49:19 PST 2002
273 -- Bug 2701777
274 -- Though not directly related , the following issue was
275 -- found while fixing this bug
276 -- killing redundant code to avoid confusion
277
278 /*
279
280 IF return_results_array(i).id IS NOT NULL THEN
281 column_value := to_char(return_results_array(i).id);
282 ELSE
283 column_value := return_results_array(i).canonical_value;
284 END IF;
285
286 IF return_results_array(i).actual_datatype = qa_ss_const.date_datatype THEN
287
288 column_value := qltdate.canon_to_user(column_value);
289
290 ELSE
291
292 -- other datatypes are character and number datatype
293 -- need to put quotes for character data type
294
295 column_value := '''' || column_value || '''';
296
297 END IF;
298
299 */
300 insert_string := insert_string||','|| column_name;
301
302 -- anagarwa Fri Dec 20 18:49:19 PST 2002
303 -- Bug 2701777
304 -- Though not directly related , the following issue was
305 -- found while fixing this bug
306 -- replaced value string to get correct date format
307 -- value_string := value_string ||','||':X'||to_char(j)||' ';
308
309 IF return_results_array(i).actual_datatype in
310 (qa_ss_const.date_datatype, qa_ss_const.datetime_datatype) THEN
311 value_string := value_string ||', fnd_date.canonical_to_date(:X' || j || ') ';
312 ELSE
313 value_string := value_string ||','||':X'||to_char(j)||' ';
314 END IF;
315
316 i := return_results_array.next(i);
317
318 END LOOP;
319
320 -- concatenate
321 sql_string := insert_string||') '||value_string||') ';
322
323 c := DBMS_SQL.OPEN_CURSOR;
324
325 --dbms_output.put_line('DBMS PARSE');
326 DBMS_SQL.PARSE(c, sql_string, DBMS_SQL.NATIVE);
327
328
329 i := return_results_array.first;
330 j := 0;
331
332 WHILE (i <= return_results_array.last) LOOP
333
334 j := j+1;
335
336 IF return_results_array(i).id IS NOT NULL THEN
337 column_value := to_char(return_results_array(i).id);
338 ELSE
339 column_value := return_results_array(i).canonical_value;
340 END IF;
341
342 -- anagarwa Fri Dec 20 18:49:19 PST 2002
343 -- Bug 2701777
344 -- Though not directly related , the following issue was
345 -- found while fixing this bug
346 -- canonical values have already been derived. The following code is
347 -- redundant and incorrect
348 -- killing incorrect code
349 /*
350
351 IF return_results_array(i).actual_datatype = qa_ss_const.date_datatype THEN
352 column_value := qltdate.canon_to_user(column_value);
353
354 END IF;
355
356 */
357 -- Bug 5335509. SHKALYAN 15-Jun-2006
358 -- Need to insert the value 'Automatic' for Sequences while
359 -- posting results. Calling Sequence API function
360 -- so as to get the translated value for 'Automatic'
361 IF return_results_array(i).actual_datatype = qa_ss_const.sequence_datatype THEN
362 column_value := QA_SEQUENCE_API.get_sequence_default_value;
363 END IF;
364 --
365 -- bug 6933282
366 -- Round off the number element to the decimal precision defined
367 -- either on the plan level or on the element level before saving
368 -- ntungare
369 --
370 IF qa_plan_element_api.get_element_datatype(return_results_array(i).element_id)
371 = qa_ss_const.number_datatype THEN
372 l_precision := nvl(qa_plan_element_api.decimal_precision(p_plan_id,
373 return_results_array(i).element_id),
374 qa_chars_api.decimal_precision(return_results_array(i).element_id));
375 column_value := round(qltdate.any_to_number(column_value),nvl(l_precision, 240));
376 END IF;
377
378 DBMS_SQL.BIND_VARIABLE(c, ':X'||to_char(j), column_value);
379 i := return_results_array.next(i);
380
381 END LOOP;
382
383 --dbms_output.put_line('DBMS EXECUTE');
384 --
385 -- Bug 5912439
386 -- Performance issue due to the use of literals in the SQL .
387 -- Binding the variables with corresponding values.
388 -- skolluku Mon Apr 23 23:39:10 PDT 2007.
389 --
390 DBMS_SQL.BIND_VARIABLE(c, ':x_collection_id', x_collection_id);
391 DBMS_SQL.BIND_VARIABLE(c, ':x_occurrence', x_occurrence);
392 DBMS_SQL.BIND_VARIABLE(c, ':p_who_last_updated_by', p_who_last_updated_by);
393 DBMS_SQL.BIND_VARIABLE(c, ':p_who_created_by', p_who_created_by);
394 DBMS_SQL.BIND_VARIABLE(c, ':p_who_last_update_login', p_who_last_update_login);
395 DBMS_SQL.BIND_VARIABLE(c, ':p_enabled_flag', p_enabled_flag);
396 DBMS_SQL.BIND_VARIABLE(c, ':p_transaction_number', p_transaction_number);
397 DBMS_SQL.BIND_VARIABLE(c, ':p_org_id', p_org_id);
398 DBMS_SQL.BIND_VARIABLE(c, ':p_plan_id', p_plan_id);
399 DBMS_SQL.BIND_VARIABLE(c, ':p_spec_id', p_spec_id);
400 DBMS_SQL.BIND_VARIABLE(c, ':p_transaction_id', p_transaction_id);
401 DBMS_SQL.BIND_VARIABLE(c, ':x_txn_header_id', x_txn_header_id);
402
403 DBMS_SQL.BIND_VARIABLE(c, ':current_date', p_last_update_date);
404
405 insert_dbms_sql_feedback := DBMS_SQL.EXECUTE(c);
406
407 --dbms_output.put_line('DBMS CLOSE');
408 DBMS_SQL.CLOSE_CURSOR(c);
409
410
411 IF (p_commit_flag = TRUE) THEN
412 COMMIT;
413
414 IF p_enabled_flag=2 or p_enabled_flag= NULL THEN
415
416 -- rkaza. bug 3183284. 12/11/2003.
417 -- Modified x_colelction_id to x_txn_header_id
418 -- Only EAM Asset Query and deferred completion should pass
419 -- commit flag as true.
420 actions_request_id := fnd_request.submit_request('QA', 'QLTACTWB', NULL,
421 NULL, FALSE, x_txn_header_id);
422
423 p_do_action_return := TRUE;
424 ELSE
425 p_do_action_return := FALSE;
426
427 END IF;
428
429
430 END IF;
431
432 ELSE
433
434 p_error_found := TRUE;
435
436 END IF;
437
438 RETURN master_error_list;
439
440
441 EXCEPTION when others then
442 raise;
443
444
445 END insert_row;
446
447 --
448 -- 12.1 QWB Usability Improvements
449 -- added a new parameter, p_ssqr_opperation to ensure
450 -- that the validation is not done again at the time of
451 -- updating rows through QWB
452 --
453 FUNCTION update_row( p_plan_id IN NUMBER,
454 p_spec_id IN NUMBER,
455 p_org_id IN NUMBER,
456 p_transaction_number IN NUMBER DEFAULT NULL,
457 p_transaction_id IN NUMBER DEFAULT NULL,
458 p_collection_id IN NUMBER,
459 p_who_last_updated_by IN NUMBER := fnd_global.user_id,
460 p_who_created_by IN NUMBER := fnd_global.user_id,
461 p_who_last_update_login IN NUMBER := fnd_global.user_id,
462 p_enabled_flag IN NUMBER,
463 p_commit_flag IN BOOLEAN DEFAULT FALSE,
464 p_error_found OUT NOCOPY BOOLEAN,
465 p_occurrence IN NUMBER,
466 p_do_action_return OUT NOCOPY BOOLEAN,
467 p_message_array OUT NOCOPY qa_validation_api.MessageArray,
468 p_row_elements IN OUT NOCOPY qa_validation_api.ElementsArray,
469 p_txn_header_id IN NUMBER DEFAULT NULL,
470 p_ssqr_operation IN NUMBER DEFAULT NULL,
471 p_last_update_date IN DATE DEFAULT SYSDATE)
472 RETURN qa_validation_api.ErrorArray IS
473
474
475
476 return_results_array qa_validation_api.ResultRecordArray;
477 master_error_list qa_validation_api.ErrorArray;
478
479 update_string varchar2(25) := null;
480 where_clause varchar2(500) := null;
481 value_string varchar2(30000) := null;
482 insert_qruh varchar2(1000) := null;
483 value_qruh varchar2(1000) := null;
484 sql_qruh varchar2(3000) := null;
485
486 sql_string varchar2(32000) := null;
487 column_name varchar2(240) := null;
488 update_column_value varchar2(2000);
489
490 i NUMBER;
491 j NUMBER;
492 k NUMBER;
493 x_collection_id NUMBER;
494 x_txn_header_id NUMBER;
495 x_update_id NUMBER;
496 actions_request_id NUMBER;
497 x_sysdate date;
498
499 c1 INTEGER; /* cursor handler */
500 update_dbms_sql_feedback INTEGER;
501
502 -- 12.1 QWB Usability improvements
503 charctr NUMBER;
504
505 --
506 -- bug 6933282
507 -- Variable to hold the decimal precision
508 -- defined for a numeric element.
509 -- ntungare
510 --
511 l_precision NUMBER;
512 BEGIN
513
514 x_collection_id := p_collection_id;
515
516 -- x_sysdate := sysdate;
517 x_sysdate := p_last_update_date;
518
519 -- anagarwa Sep 30 2003
520 -- SSQR project relies upon txn_header_id to enable and fire actions
521 -- following code looks for txn_header_id and generates a new one if
522 -- not found.
523
524 IF p_txn_header_id IS NULL THEN
525 SELECT MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
526 INTO x_txn_header_id
527 FROM DUAL;
528 ELSE
529 x_txn_header_id := p_txn_header_id;
530 END IF;
531
532 -- 12.1 QWB Usability Improvements
533 -- Call the validateRow Method only if the parameter
534 -- p_ssqr_operation is NULL which means that the call
535 -- of not from the self service application
536 --
537 If (p_ssqr_operation IS NULL) THEN
538 master_error_list := qa_validation_api.validate_row(p_plan_id,
539 p_spec_id, p_org_id, p_who_created_by, p_transaction_number,
540 p_transaction_id, return_results_array, p_message_array,
541 p_row_elements);
542 -- In case the call is from the self-service application
543 -- then the results_record_arry needs to be populated
544 --
545 ELSIF (p_ssqr_operation = 1) THEN
546 --perf
547 charctr := p_row_elements.first;
548 while charctr <= p_row_elements.last
549 loop
550 -- Set the element Id
551 return_results_array(charctr).element_id := charctr;
552
553 -- Check if the id value for the element is present
554 if p_row_elements(charctr).id IS NOT NULL THEN
555 -- Set the id value
556 return_results_array(charctr).id := p_row_elements(charctr).id;
557 ELSE
558 -- Set the canonical value
559 return_results_array(charctr).canonical_value := p_row_elements(charctr).value;
560
561 --
562 -- bug 6933282
563 -- Round off the number element to the decimal precision defined
564 -- either on the plan level or on the element level before saving
565 -- ntungare
566 --
567 IF qa_plan_element_api.get_element_datatype(charctr)
568 = qa_ss_const.number_datatype THEN
569 l_precision := nvl(qa_plan_element_api.decimal_precision(p_plan_id,
570 charctr),
571 qa_chars_api.decimal_precision(charctr));
572 return_results_array(charctr).canonical_value :=
573 round(qltdate.any_to_number(return_results_array(charctr).canonical_value),
574 nvl(l_precision, 240));
575 END IF;
576 END If;
577
578 -- Set the actual data type
579 return_results_array(charctr).actual_datatype := qa_plan_element_api.get_actual_datatype(charctr);
580
581 charctr := p_row_elements.next(charctr);
582 end loop;
583 END If;
584
585 IF qa_validation_api.no_errors(master_error_list) THEN
586
587 p_error_found := FALSE;
588
589
590 update_string := 'UPDATE QA_RESULTS ';
591
592 -- Bug 3776542. Performance issue due to the use of literals in the SQL below.
593 -- Modified the SQL string to include bind variables.This where_clause is used
594 -- to update/insert data in QA_RESULTS and QA_RESULTS_UPDATE_HISTORY.
595 -- srhariha. Thu Jul 29 00:27:59 PDT 2004.
596 where_clause := 'WHERE collection_id = '||':x_collection_id'||
597 ' AND '||'occurrence = '||':p_occurrence'||
598 ' AND '||'plan_id = '||':p_plan_id';
599
600
601 -- anagarwa Sep 30 2003
602 -- SSQR project relies upon txn_header_id to enable and fire actions
603
604 -- anagarwa Mon Mar 8 12:18:47 PST 2004
605 -- Bug 3489530 Last_update_date was missing from this sql
606 -- also made qa_last_update_date a bind variable so that the
607 -- time portion is stored properly
608 -- Finally rest of the sql string is also modified to use bind
609 -- variables to follow standards as currently enforced
610 value_string := 'SET qa_last_update_date = '|| ':current_date'||', '||
611 ' last_update_date = '|| ':current_date'||', '||
612 ' last_updated_by = '|| ':updated_by'||', '||
613 ' qa_last_updated_by = '|| ':updated_by'||', '||
614 ' last_update_login = '|| ':update_login'||', '||
615 ' txn_header_id = '|| ':txn_hdr_id';
616
617 insert_qruh := ' INSERT INTO qa_results_update_history (creation_date,
618 last_update_date,
619 created_by,
620 last_update_login,
621 last_updated_by,
622 occurrence,
623 update_id,
624 old_value,
625 char_id) ';
626
627 -- Bug 3776542. Performance issue due to the use of literals in the SQL below.
628 -- Modified the string to include bind variables.
629 -- srhariha. Thu Jul 29 00:27:59 PDT 2004.
630
631 value_qruh := '(SELECT '||':creation_date'||','||
632 ':last_upd_date'||','||
633 ':created_by'|| ' ,'||
634 ':update_login'|| ' ,'||
635 ':updated_by'|| ' ,'||
636 ':p_occurrence'||' ,';
637
638
639
640
641 i := return_results_array.first;
642 k := 0;
643
644 WHILE (i <= return_results_array.last) LOOP
645
646 k := k +1;
647
648 -- See comments above for Bug 3402251
649 column_name := qa_plan_element_api.get_result_column_name(
650 p_plan_id, i);
651
652 -- bug 3178307. rkaza. 10/06/2003. Timezone support.
653 IF return_results_array(i).actual_datatype in
654 (qa_ss_const.date_datatype, qa_ss_const.datetime_datatype) THEN
655 value_string := value_string || ', ' || column_name || ' = ' || 'fnd_date.canonical_to_date(:X' || k || ') ';
656 ELSE
657 value_string := value_string || ', ' || column_name || ' = ' || ':X' || to_char(k) || ' ';
658 END IF;
659
660 SELECT QA_RESULTS_UPDATE_HISTORY_S.NEXTVAL INTO x_update_id FROM DUAL;
661
662 -- Bug 3776542. Performance issue due to the use of literals in the SQL below.
663 -- Modified the string to include bind variables.
664 -- srhariha. Thu Jul 29 00:27:59 PDT 2004.
665
666 sql_qruh := insert_qruh|| value_qruh||':x_update_id'||', '||column_name||','||
667 ':element_id'||' FROM QA_RESULTS '||where_clause ||' )';
668
669 -- Bug 3776542. Performance issue due to the use of literals in the SQL .
670 -- Binding the variables with corresponding values.
671 -- srhariha. Thu Jul 29 00:27:59 PDT 2004.
672
673 EXECUTE IMMEDIATE sql_qruh
674 USING x_sysdate,
675 x_sysdate,
676 p_who_last_updated_by,
677 p_who_last_updated_by,
678 p_who_last_updated_by,
679 p_occurrence,
680 x_update_id,
681 return_results_array(i).element_id,
682 x_collection_id,
683 p_occurrence,
684 p_plan_id;
685
686 i := return_results_array.next(i);
687
688
689 END LOOP;
690
691 -- concatenate
692
693 sql_string := update_string||' '||value_string||' '||where_clause;
694
695 c1 := DBMS_SQL.OPEN_CURSOR;
696
697 --dbms_output.put_line('DBMS PARSE');
698 DBMS_SQL.PARSE(c1, sql_string, DBMS_SQL.NATIVE);
699
700 j := return_results_array.first;
701 k := 0;
702
703 WHILE (j <= return_results_array.last) LOOP
704
705 k := k +1;
706
707 column_name := qa_plan_element_api.get_result_column_name(
708 p_plan_id, return_results_array(j).element_id);
709
710
711 IF return_results_array(j).id IS NOT NULL THEN
712 update_column_value := to_char(return_results_array(j).id);
713 ELSE
714 update_column_value := return_results_array(j).canonical_value;
715 END IF;
716
717 -- dbms_output.put_line('bind varaible ->'||':X'||to_char(k));
718
719 DBMS_SQL.BIND_VARIABLE(c1, ':X'||to_char(k), update_column_value);
720 -- anagarwa Mon Mar 8 12:18:47 PST 2004
721 -- Bug 3489530 last_update_Date is added as bind variable so
722 -- we need to bind value here.
723 -- also updated_by and update login were made bind variables to
724 -- comply with coding standards
725 DBMS_SQL.BIND_VARIABLE(c1, ':current_date', x_sysdate);
726 DBMS_SQL.BIND_VARIABLE(c1, ':updated_by', p_who_last_updated_by);
727 DBMS_SQL.BIND_VARIABLE(c1, ':update_login', p_who_last_updated_by);
728 DBMS_SQL.BIND_VARIABLE(c1, ':txn_hdr_id', x_txn_header_id);
729
730 -- Bug 3776542. Performance issue due to the use of literals in the SQL .
731 -- Binding the variables with corresponding values.
732 -- srhariha. Thu Jul 29 00:27:59 PDT 2004.
733
734 DBMS_SQL.BIND_VARIABLE(c1, ':x_collection_id', x_collection_id);
735 DBMS_SQL.BIND_VARIABLE(c1, ':p_occurrence', p_occurrence);
736 DBMS_SQL.BIND_VARIABLE(c1, ':p_plan_id', p_plan_id);
737
738
739
740 j := return_results_array.next(j);
741
742
743 END LOOP;
744
745 update_dbms_sql_feedback := DBMS_SQL.EXECUTE(c1);
746
747 DBMS_SQL.CLOSE_CURSOR(c1);
748
749
750 IF (p_commit_flag = TRUE) THEN
751 COMMIT;
752
753 IF p_enabled_flag=2 or p_enabled_flag= NULL THEN
754
755 actions_request_id := fnd_request.submit_request('QA', 'QLTACTWB', NULL,
756 NULL, FALSE, x_collection_id);
757
758 p_do_action_return := TRUE;
759 ELSE
760 p_do_action_return := FALSE;
761
762 END IF;
763
764
765 END IF;
766
767
768 ELSE
769
770 p_error_found := TRUE;
771
772 END IF;
773
774 RETURN master_error_list;
775
776
777 EXCEPTION when others then
778 raise;
779
780
781
782 END update_row;
783
784 /* akbhatia - Bug 3345279 : Added the following procedure enable. */
785
786 PROCEDURE enable (p_collection_id IN NUMBER ) IS
787
788 BEGIN
789
790 UPDATE qa_results
791 SET status=2
792 WHERE collection_id = p_collection_id;
793
794 EXCEPTION when others then
795 raise;
796
797 END enable;
798
799 END qa_results_api;
800
[Home] [Help]