[Home] [Help]
PACKAGE BODY: APPS.QA_TXN_GRP
Source
1 PACKAGE BODY qa_txn_grp AS
2 /* $Header: qagtxnb.pls 120.17.12020000.2 2012/07/03 13:22:51 ntungare ship $ */
3
4 -- Bug 4343758
5 -- R12 OAF Txn Integration Project
6 -- Standard Global variable
7 -- shkalyan 05/07/2005.
8 g_pkg_name CONSTANT VARCHAR2(30) := 'QA_TXN_GRP';
9
10 FUNCTION qa_enabled(
11 p_txn_number IN NUMBER,
12 p_org_id IN NUMBER) RETURN VARCHAR2 IS
13
14 l_txn_id NUMBER;
15 l_status VARCHAR2(1);
16 l_industry VARCHAR2(10);
17 l_schema VARCHAR2(30);
18 dummy BOOLEAN;
19
20 CURSOR txn_plans IS
21 SELECT /*+ ordered use_nl(qp) */ qpt.plan_transaction_id
22 FROM qa_plan_transactions qpt,
23 qa_plans qp
24 WHERE qpt.transaction_number = p_txn_number AND
25 qpt.plan_id = qp.plan_id AND
26 qpt.enabled_flag = 1 AND
27 qp.organization_id = p_org_id AND
28 trunc(sysdate) BETWEEN
29 nvl(trunc(qp.effective_from), trunc(sysdate)) AND
30 nvl(trunc(qp.effective_to), trunc(sysdate));
31
32 BEGIN
33 IF p_txn_number = txn_number_cache AND p_org_id = org_id_cache THEN
34 RETURN qa_enabled_cache;
35 END IF;
36
37 txn_number_cache := p_txn_number;
38 org_id_cache := p_org_id;
39
40 qa_enabled_cache := 'F';
41 dummy := fnd_installation.get_app_info('QA', l_status,
42 l_industry, l_schema);
43 IF l_status IN ('I', 'S') THEN
44 OPEN txn_plans;
45 FETCH txn_plans INTO l_txn_id;
46 IF txn_plans%FOUND THEN
47 qa_enabled_cache := 'T';
48 END IF;
49 CLOSE txn_plans;
50 END IF;
51
52 RETURN qa_enabled_cache;
53 END qa_enabled;
54
55
56
57 --------------------
58 FUNCTION commit_allowed(
59 p_txn_number IN VARCHAR2,
60 p_org_id IN NUMBER,
61 p_plan_txn_ids IN VARCHAR2,
62 p_collection_id IN NUMBER,
63 x_plan_ids OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
64
65 l_plan_list VARCHAR2(5000) DEFAULT NULL;
66 l_plan_id NUMBER;
67
68 select_stmt VARCHAR2(10000);
69
70 TYPE result_cursor IS REF CURSOR;
71 c_plans_results result_cursor;
72
73 BEGIN
74 /*
75 Bug: 2345277
76 rkaza: 05/06/2002. return true if there are no applicable plans.
77 Otherwise the sql statement
78 qpt.plan_transaction_id IN ( )
79 would error out.
80 */
81 If p_plan_txn_ids is null then
82 return 'T';
83 End if;
84 -- Bug 4270911. CU2 SQL Literal fix. TD #20
85 -- Add p_plan_txn_ds into temp table and rewrite the
86 -- query to select the id from it.
87 -- srhariha. Mon Apr 18 03:52:46 PDT 2005.
88
89 qa_performance_temp_pkg.purge_and_add_ids('QAGTXNB.COMMIT_ALLOWED', p_plan_txn_ids);
90
91
92 select_stmt :=
93 'SELECT DISTINCT qpt.plan_id' ||
94 ' FROM qa_plan_transactions qpt' ||
95 ' WHERE qpt.plan_transaction_id IN ' ||
96 '( SELECT id FROM qa_performance_temp ' ||
97 ' WHERE key=''QAGTXNB.COMMIT_ALLOWED'' ) '||
98 ' AND qpt.enabled_flag = 1' ||
99 ' AND qpt.mandatory_collection_flag = 1' ||
100 ' AND qpt.background_collection_flag = 2' ||
101 ' AND NOT EXISTS' ||
102 ' (SELECT 1' ||
103 ' FROM qa_results qr ' ||
104 ' WHERE qr.plan_id = qpt.plan_id ' ||
105 ' AND qr.collection_id = :c)';
106
107 OPEN c_plans_results FOR select_stmt USING p_collection_id;
108 LOOP
109 FETCH c_plans_results INTO l_plan_id;
110 EXIT WHEN c_plans_results%NOTFOUND;
111 l_plan_list := l_plan_list || ',' || l_plan_id;
112 END LOOP;
113 CLOSE c_plans_results;
114
115 IF l_plan_list IS NOT NULL THEN
116 x_plan_ids := substr(l_plan_list, 2);
117 RETURN 'F';
118 END IF;
119
120 RETURN 'T';
121 END commit_allowed;
122
123
124 --------------------
125 FUNCTION get_collection_id RETURN NUMBER IS
126
127 l_coll_id NUMBER;
128
129 BEGIN
130 SELECT qa_collection_id_s.nextval INTO l_coll_id FROM dual;
131 RETURN l_coll_id;
132 END get_collection_id;
133
134 ---------------------------------------------------
135
136
137 FUNCTION parse_id(x_result IN VARCHAR2, n IN INTEGER,
138 p IN INTEGER, q IN INTEGER) RETURN NUMBER IS
139 BEGIN
140 RETURN to_number(substr(x_result, p, q-p));
141 END parse_id;
142
143
144 FUNCTION parse_value(x_result IN VARCHAR2, n IN INTEGER,
145 p IN OUT NOCOPY INTEGER) RETURN VARCHAR2 IS
146
147 -- changed the variable type from qa_results.character1%TYPE to
148 -- qa_results.comment1%TYPE for LongComments Project
149 -- Bug 2234299
150 -- rponnusa Thu Mar 14 05:33:00 PST 2002
151
152 /*
153 Bug: 2369332
154 rkaza: 05/10/2002. Changing type comment to varchar2(2000) to
155 avoid dependencies on case changes for long comments.
156 */
157 -- value qa_results.comment1%TYPE := '';
158 value varchar2(2000) := '';
159 c VARCHAR2(10);
160 separator CONSTANT VARCHAR2(1) := '@';
161
162 BEGIN
163 --
164 -- Loop until a single @ is found or x_result is exhausted.
165 --
166 p := p + 1; -- add 1 before substr to skip '='
167 WHILE p <= n LOOP
168 c := substr(x_result, p, 1);
169 p := p + 1;
170 IF (c = separator) THEN
171 IF substr(x_result, p, 1) <> separator THEN
172 --
173 -- take a peak at the next character, if not another @,
174 -- we have reached the end. Otherwise, skip this @
175 --
176 RETURN value;
177 ELSE
178 p := p + 1;
179 END IF;
180 END IF;
181 value := value || c;
182 END LOOP;
183
184 RETURN value;
185 END parse_value;
186
187
188 FUNCTION result_to_array(x_result IN VARCHAR2)
189 RETURN ElementsArray IS
190
191 elements ElementsArray;
192 n INTEGER := length(x_result);
193 p INTEGER; -- starting string position
194 q INTEGER; -- ending string position
195 x_char_id NUMBER;
196
197 -- changed the variable type from qa_results.character1%TYPE to
198 -- qa_results.comment1%TYPE for LongComments Project
199 -- Bug 2234299
200 -- rponnusa Thu Mar 14 05:33:00 PST 2002
201
202 /*
203 Bug: 2369332
204 rkaza: 05/10/2002. Changing type comment to varchar2(2000) to
205 avoid dependencies on case changes for long comments.
206 */
207 -- x_value qa_results.comment1%TYPE;
208 x_value varchar2(2000);
209
210 BEGIN
211 p := 1;
212 WHILE p < n LOOP
213 q := instr(x_result, '=', p);
214 --
215 -- found the first = sign. To the left, must be char_id
216 --
217 x_char_id := parse_id(x_result, n, p, q);
218 --
219 -- To the right, must be the value
220 --
221 x_value := parse_value(x_result, n, q);
222 elements(x_char_id).value := x_value;
223 p := q;
224 END LOOP;
225
226 RETURN elements;
227 END result_to_array;
228
229 --
230 -- Bug 4995406
231 -- Added a new function to convert the
232 -- Normalized Ids passed by the EAM
233 -- transactions into the denormalized values
234 -- ntungare Wed Feb 22 06:52:41 PST 2006
235 --
236 -- Bug 5279941
237 -- Modified the function to a Proceudre
238 -- the array that was being returned has now
239 -- been defined as an IN OUT param
240 -- ntungare Wed Jun 21 02:09:36 PDT 2006
241 --
242 PROCEDURE eam_denormalize_array(x_normalized_id_array IN OUT NOCOPY ElementsArray,
243 x_Org_Id IN NUMBER)
244 IS
245 BEGIN
246 --
247 -- Bug 5279941
248 -- Denormalizing the Asset Group
249 -- ntungare
250 --
251 If x_normalized_id_array.exists(qa_ss_const.asset_group) THEN
252 x_normalized_id_array(qa_ss_const.asset_group).value := QA_FLEX_UTIL.ITEM
253 (x_org_id,
254 x_normalized_id_array(qa_ss_const.asset_group).value);
255 End If;
256
257 --
258 -- Bug 5279941
259 -- Denormalizing the Asset Activity
260 -- ntungare
261 --
262 If x_normalized_id_array.exists(qa_ss_const.asset_activity) THEN
263 x_normalized_id_array(qa_ss_const.asset_activity).value :=
264 QA_FLEX_UTIL.ITEM
265 (x_org_id,
266 x_normalized_id_array(qa_ss_const.asset_activity).value);
267 End If;
268
269 --
270 -- Bug 5279941
271 -- Denormalizing the Asset Instance Number
272 -- ntungare
273 --
274 If x_normalized_id_array.exists(qa_ss_const.asset_instance_number) THEN
275 x_normalized_id_array(qa_ss_const.asset_instance_number).value :=
276 QA_FLEX_UTIL.GET_ASSET_INSTANCE_NAME
277 (x_normalized_id_array(qa_ss_const.asset_instance_number).value);
278 End If;
279
280 END eam_denormalize_array;
281
282 FUNCTION triggers_matched(p_plan_txn_id IN NUMBER, elements ElementsArray)
283 RETURN VARCHAR2 IS
284
285 BEGIN
286
287 FOR plan_record in (
288 SELECT qpct.operator,
289 qpct.Low_Value,
290 qpct.High_Value ,
291 qc.datatype,
292 qc.char_id
293 FROM qa_plan_collection_triggers qpct,
294 qa_chars qc
295 WHERE qpct.Collection_Trigger_ID = qc.char_id and
296 qpct.plan_transaction_id = p_plan_txn_id) LOOP
297
298 IF NOT elements.EXISTS(plan_record.char_id) THEN
299 RETURN 'F';
300 END IF;
301
302 /*
303 Added NVL condition for the IF condition below to handle the
304 condition when null is returned by qltcompb.compare.Before
305 the fix if qltcompb.compare returns null then the following
306 condition is not satisfied and True is returned which is not
307 correct.Take for Eg., elements(plan_record.char_id).value is
308 Null and plan_record.Low_Value has the value 'Test' and
309 plan_record.operator is 1(equals) then qltcompb.compare will
310 return Null and because of that the following call fails. If
311 Null is retruned then it has to be considered as False.
312 Bug 3810082. suramasw.
313 */
314
315 IF NOT (NVL(qltcompb.compare(
316 elements(plan_record.char_id).value,
317 plan_record.operator,
318 plan_record.Low_Value,
319 plan_record.High_Value,
320 plan_record.datatype),FALSE)) THEN
321 RETURN 'F';
322 END IF;
323
324 END LOOP;
325
326 RETURN 'T';
327 END triggers_matched;
328
329
330 FUNCTION evaluate_triggers(
331 p_txn_number IN NUMBER,
332 p_org_id IN NUMBER,
333 p_context_values IN VARCHAR2,
334 x_plan_txn_ids OUT NOCOPY VARCHAR2) RETURN VARCHAR2 IS
335
336 elements ElementsArray;
337 plan_txn_list VARCHAR2(10000);
338
339 --
340 -- Bug 8744187
341 -- The following 3 variables would be used to fetch
342 -- the item category.
343 -- skolluku
344 --
345 p_item varchar2(100);
346 p_category varchar2(1000);
347 p_category_id varchar2(1000);
348
349 BEGIN
350 elements := result_to_array(p_context_values);
351 -- Bug 9098649.pdube
352 -- Included this if condition to avoid unnecessary no_data_found
353 -- exceptions for EAM transacitons where item is not context.
354 -- Also, setting the value only when category is null.
355 IF (elements.EXISTS(10) AND
356 elements(10).value IS NOT NULL) THEN
357 IF (NOT elements.EXISTS(11) OR
358 (elements.EXISTS(11) AND
359 elements(11).value IS NULL)) THEN
360 --
361 -- Bug 8744187
362 -- Fetch the item category using the procedure
363 -- from qa_ss_core and assign it to elements(11)
364 -- which is not populated from the parent transaction.
365 -- skolluku
366 --
367 p_item := elements(10).value;
368 qa_ss_core.get_item_category_val(
369 p_org_id => p_org_id,
370 p_item_val => p_item,
371 x_category_val => p_category,
372 x_category_id => p_category_id);
373 elements(11).value := p_category;
374 END IF;
375 END IF;
376 /*
377 Bug: 2345277
378 rkaza: 05/06/2002. Added date restriction for the applicable
379 collection plans.
380 */
381 FOR pt IN (
382 SELECT qpt.plan_transaction_id
383 FROM qa_plan_transactions qpt, qa_plans qp
384 WHERE qpt.transaction_number = p_txn_number
385 AND qpt.plan_id = qp.plan_id
386 AND qp.organization_id = p_org_id
387 AND trunc(sysdate) between
388 nvl(trunc(qp.effective_from), trunc(sysdate)) and
389 nvl(trunc(qp.effective_to), trunc(sysdate))
390 AND qpt.enabled_flag = 1) LOOP
391
392 IF triggers_matched(pt.plan_transaction_id, elements) = 'T' THEN
393 plan_txn_list := plan_txn_list || ',' || pt.plan_transaction_id;
394 END IF;
395
396 END LOOP;
397
398 IF plan_txn_list IS NOT NULL THEN
399 x_plan_txn_ids := substr(plan_txn_list, 2);
400 RETURN 'T';
401 END IF;
402
403 RETURN 'F';
404 END evaluate_triggers;
405
406 -- bug 4995406
407 -- New procedure to evaluate the Transaction
408 -- triggers on the Context elements for
409 -- EAM Transactions and Insert the results
410 -- for the applicable Plans
411 -- ntungare Wed Feb 22 06:52:59 PST 2006
412 --
413 PROCEDURE evaltriggers_InsertRes_eamtxn(
414 p_txn_number IN NUMBER,
415 p_org_id IN NUMBER,
416 p_context_values IN VARCHAR2,
417 p_plans_tab IN QA_PARENT_CHILD_PKG.ChildPlanArray,
418 p_collection_id IN NUMBER) IS
419
420 elements ElementsArray;
421 denormalized_values ElementsArray;
422 plan_txn_id NUMBER;
423
424 --
425 -- Bug 5335509
426 -- Variable to get the return status of the Seq
427 -- generation procedure
428 -- ntungare Tue Jul 4 06:20:09 PDT 2006
429 --
430 l_return_status VARCHAR2(3);
431
432 BEGIN
433 elements := result_to_array(p_context_values);
434
435 denormalized_values := elements;
436
437 --Since the Context values sent in the EAM transaction are
438 --normalized so calling the function to get the demormalized
439 --values array
440 --
441 -- Bug 5279941
442 -- Made the necessary changes as the
443 -- method eam_denormalize_array has
444 -- been modified to a procedure
445 -- ntungare
446 --
447 eam_denormalize_array(x_normalized_id_array => denormalized_values,
448 x_Org_Id => p_org_id);
449
450 plan_txn_id := p_plans_tab.FIRST;
451
452 -- Looping through the plan_txn_ids and checking
453 -- for the applicable ids
454 WHILE plan_txn_id <= p_plans_tab.LAST
455 LOOP
456 -- Checking if the Triggers Match
457 IF triggers_matched(plan_txn_id, denormalized_values) = 'T' THEN
458
459 -- If they do then the Plan is applicable, so insert the results
460 insert_results(p_plans_tab(plan_txn_id), p_org_id, p_collection_id, elements);
461 END IF;
462 plan_txn_id := p_plans_tab.NEXT(plan_txn_id);
463 END LOOP;
464
465 --
466 -- Bug 5335509
467 -- Calling the Sequence generation Api
468 -- to generate the sequences
469 -- ntungare Tue Jul 4 06:20:09 PDT 2006
470 --
471 QA_SEQUENCE_API.Generate_Seq_for_Txn
472 ( p_collection_id,
473 l_return_status);
474
475 END evaltriggers_InsertRes_eamtxn;
476
477 ------------------------------------------------------
478
479
480 FUNCTION fmt(value VARCHAR2, datatype NUMBER, column_name VARCHAR2)
481 RETURN VARCHAR2 IS
482 BEGIN
483 IF value IS NULL THEN
484 RETURN 'NULL';
485
486 ELSIF datatype = 1 THEN -- string
487 RETURN '''' || qa_core_pkg.dequote(value) || '''';
488
489 ELSIF datatype = 2 THEN -- number
490 IF column_name LIKE 'CHARACTER%' THEN -- multiradix
491 RETURN '''' || qltdate.number_user_to_canon(value) || '''';
492 ELSE -- real number
493 RETURN qltdate.number_user_to_canon(value);
494 END IF;
495
496 ELSIF datatype = 3 THEN -- date
497 IF column_name LIKE 'CHARACTER%' THEN -- flexdate
498 RETURN '''' || qltdate.date_to_canon(
499 to_date(value, fnd_date.name_in_dt_mask)) || '''';
500 ELSE -- real date
501 RETURN 'to_date(''' || value || ''', ''' ||
502 fnd_date.name_in_dt_mask || ''')';
503 END IF;
504
505 -- Bug 5335509. SHKALYAN 15-Jun-2006
506 -- Need to insert the value 'Automatic' for Sequences while
507 -- posting background results. Calling Sequence API function
508 -- so as to consistently get the translated value for 'Automatic'
509 ELSIF datatype = 5 THEN -- sequence
510 RETURN QA_SEQUENCE_API.get_sequence_default_value;
511
512 -- Bug 3179845. Timezone Project. rponnusa Fri Oct 17 10:34:50 PDT 2003
513 -- Added datetime datatype
514
515 ELSIF datatype = 6 THEN -- datetime
516 IF column_name LIKE 'CHARACTER%' THEN -- flexdate
517 RETURN '''' || qltdate.date_to_canon_dt(to_date(value,
518 fnd_date.name_in_dt_mask)) || '''';
519 ELSE -- real date
520 RETURN 'to_date (''' || value || ''', ''' ||fnd_date.name_in_dt_mask || ''')';
521 END IF;
522 END IF;
523
524 --
525 -- By coincident, the above will also work for normalized IDs
526 --
527
528 RETURN NULL;
529 END fmt;
530
531
532 PROCEDURE insert_results(
533 p_plan_id IN NUMBER,
534 p_org_id IN NUMBER,
535 p_collection_id IN NUMBER,
536 elements IN ElementsArray) IS
537
538 uid NUMBER := fnd_global.user_id;
539
540 l_insert_columns VARCHAR2(10000);
541 l_insert_values VARCHAR2(10000);
542
543 BEGIN
544 l_insert_columns :=
545 'INSERT INTO qa_results ' ||
546 ' (status, plan_id, organization_id, collection_id, occurrence,' ||
547 ' last_update_date, qa_last_update_date, ' ||
548 ' creation_date, qa_creation_date, ' ||
549 ' last_updated_by, qa_last_updated_by, ' ||
550 ' created_by, qa_created_by ';
551
552 l_insert_values :=
553 ' VALUES(1, :c1, :c2, :c3, qa_occurrence_s.nextval,' ||
554 ' sysdate, sysdate,' ||
555 ' sysdate, sysdate,' ||
556 ' :c4, :c5,' ||
557 ' :c6, :c7';
558
559 FOR c IN (
560 --
561 -- Bug 5365165
562 -- Fetching the default value
563 -- set either on the plan or the element
564 -- level
565 -- ntungare
566 --
567 SELECT qpc.char_id, qpc.result_column_name, qc.datatype,
568 NVL(qpc.default_value, qc.default_value) default_value
569 FROM qa_plan_chars qpc, qa_chars qc
570 WHERE plan_id = p_plan_id AND qpc.char_id = qc.char_id) LOOP
571
572 IF elements.EXISTS(c.char_id) THEN
573 l_insert_columns := l_insert_columns || ',' || c.result_column_name;
574 l_insert_values := l_insert_values || ',' ||
575 fmt(elements(c.char_id).value, c.datatype, c.result_column_name);
576
577 --
578 -- Bug 5335509
579 -- The sequence elements won't be passed as context
580 -- Values and hence won't be present in the "elements" array
581 -- So explicitly checking for the sequence elements
582 -- and initializing them to Automatic.
583 -- ntungare Tue Jul 4 06:20:09 PDT 2006
584 --
585 ELSIF c.datatype = qa_ss_const.sequence_datatype THEN
586 l_insert_columns := l_insert_columns || ',' || c.result_column_name;
587 l_insert_values := l_insert_values || ', '''
588 || QA_SEQUENCE_API.get_sequence_default_value
589 || '''';
590
591 --
592 -- Bug 5365165
593 -- Added the handling for default values
594 -- ntungare
595 --
596 ELSIF c.default_value IS NOT NULL THEN
597 l_insert_columns := l_insert_columns || ',' || c.result_column_name;
598 l_insert_values := l_insert_values || ', '''
599 || c.default_value
600 || '''';
601 END IF;
602
603 END LOOP;
604
605 l_insert_columns := l_insert_columns || ')';
606 l_insert_values := l_insert_values || ')';
607 EXECUTE IMMEDIATE l_insert_columns || l_insert_values
608 USING p_plan_id, p_org_id, p_collection_id, uid, uid, uid, uid;
609 EXCEPTION WHEN OTHERS THEN
610
611 if( FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL ) then
612 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,
613 'QA_TXN_GRP.INSERT_RESULTS.err', l_insert_columns || l_insert_values );
614 end if;
615
616 END insert_results;
617
618
619 PROCEDURE post_background_results(
620 p_txn_number IN NUMBER,
621 p_org_id IN NUMBER,
622 p_plan_txn_ids IN VARCHAR2,
623 p_context_values IN VARCHAR2,
624 p_collection_id IN NUMBER) IS
625
626 select_stmt VARCHAR2(10000);
627 TYPE ref_cursor IS REF CURSOR;
628 c ref_cursor;
629
630 l_plan_id NUMBER;
631 elements ElementsArray;
632
633 --
634 -- Bug 5335509
635 -- Variable to get the return status of the Seq
636 -- generation procedure
637 -- ntungare Tue Jul 4 06:20:09 PDT 2006
638 --
639 l_return_status VARCHAR2(3);
640
641 BEGIN
642 /*
643 Bug 2440015
644 suramasw: Thu Jul 18 04:26:41 PDT 2002
645 Added only the IF condition.
646 Only if the Service Request Type has applicable collection plans then the IF
647 condition is used else it is skipped */
648 IF p_plan_txn_ids is NOT NULL then
649
650 -- Bug 4270911. CU2 SQL Literal fix. TD #21
651 -- Add p_plan_txn_ds into temp table and rewrite the
652 -- query to select the id from it.
653 -- srhariha. Mon Apr 18 03:52:46 PDT 2005.
654
655 qa_performance_temp_pkg.purge_and_add_ids('QAGTXNB.POST_BACKGROUND_RESULTS', p_plan_txn_ids);
656
657 elements := result_to_array(p_context_values);
658 select_stmt :=
659 'SELECT DISTINCT qpt.plan_id' ||
660 ' FROM qa_plan_transactions qpt' ||
661 ' WHERE qpt.plan_transaction_id IN ' ||
662 '( SELECT id FROM qa_performance_temp ' ||
663 ' WHERE key=''QAGTXNB.POST_BACKGROUND_RESULTS'' ) '||
664 ' AND qpt.enabled_flag = 1' ||
665 ' AND qpt.background_collection_flag = 1' ||
666 ' AND NOT EXISTS ' ||
667 ' (SELECT 1 ' ||
668 ' FROM qa_results qr ' ||
669 ' WHERE qr.plan_id = qpt.plan_id ' ||
670 ' AND qr.collection_id = :c)';
671
672 OPEN c FOR select_stmt USING p_collection_id;
673 LOOP
674 FETCH c INTO l_plan_id;
675 EXIT WHEN c%NOTFOUND;
676 insert_results(l_plan_id, p_org_id, p_collection_id, elements);
677
678 END LOOP;
679 CLOSE c;
680
681 --
682 -- Bug 5335509
683 -- Calling the Sequence generation Api
684 -- to generate the sequences
685 -- ntungare Tue Jul 4 06:20:09 PDT 2006
686 --
687 QA_SEQUENCE_API.Generate_Seq_for_Txn
688 (p_collection_id,
689 l_return_status);
690
691 END IF;
692
693 END post_background_results;
694
695 --Bug 4995406
696 --Procedure to post the results for Background
697 --Collection Plans, for EAM Transctions
698 --This procedure is different from the
699 --Background Plan Procssing Procedure for
700 --other Transaction in the way that it does not
701 --handle the Parent Child Scenarios. It
702 --also doesn't do the actions firing
703 --as this is done by the EAM Txn
704 --ntungare Wed Feb 22 06:47:38 PST 2006
705 PROCEDURE eam_post_background_results(
706 p_txn_number IN NUMBER,
707 p_org_id IN NUMBER,
708 p_context_values IN VARCHAR2,
709 p_collection_id IN NUMBER) IS
710
711 elements ElementsArray;
712
713 -- Cursor to get a listing of the Enabled
714 -- Background Plans for which the results
715 -- Havent been collected
716
717 CURSOR c1(txn_no number, org_id number, col_id number) is
718 SELECT DISTINCT qpt.plan_id plan_id,
719 qpt.plan_transaction_id plan_txn_id
720 FROM qa_plan_transactions qpt, qa_plans qp
721 WHERE qpt.transaction_number = txn_no
722 AND qpt.plan_id = qp.plan_id
723 AND qp.organization_id = org_id
724 AND trunc(sysdate) between
725 nvl(trunc(qp.effective_from), trunc(sysdate)) and
726 nvl(trunc(qp.effective_to), trunc(sysdate))
727 AND qpt.enabled_flag = 1
728 AND qpt.background_collection_flag = 1
729 AND NOT EXISTS
730 (SELECT 1
731 FROM qa_results qr
732 WHERE qr.plan_id = qpt.plan_id
733 AND qr.collection_id = col_id);
734
735 plan_id_tab QA_PARENT_CHILD_PKG.ChildPlanArray;
736
737 BEGIN
738 -- Populating the array of the Plan_ids with the
739 -- Plan Txn Ids as the Indices
740 For curval in c1(p_txn_number, p_org_id, p_collection_id)
741 LOOP
742 plan_id_tab(curval.plan_txn_id) := curval.plan_id;
743 END LOOP;
744
745 -- Calling evaltriggers_InsertRes_eamtxn
746 -- to get the list of the applicable plan Txn Ids
747 -- and insert the data for the corresponding plans
748 --
749 -- Bug 5279941
750 -- Calling the procedure only in case there
751 -- are any background plans setup
752 -- ntungare Wed Jun 21 00:37:54 PDT 2006
753 --
754 If plan_id_tab.COUNT <> 0 Then
755 evaltriggers_InsertRes_eamtxn(p_txn_number => p_txn_number ,
756 p_org_id => p_org_id,
757 p_context_values => p_context_values,
758 p_plans_tab => plan_id_tab,
759 p_collection_id => p_collection_id);
760 End If;
761 END eam_post_background_results;
762
763 -- Bug 5161719. SHKALYAN 13-Apr-2006
764 -- Added new function to accept prefix and suffix for plan names
765 -- and construct a message string in the form of
766 -- <prefix> || <plan name> || <suffix>
767 -- rest of the logic was moved from the old get_plan_name
768 -- to avoid code duplication.
769 FUNCTION get_plan_names_message(
770 p_plan_ids IN VARCHAR2,
771 p_prefix IN VARCHAR2,
772 p_suffix IN VARCHAR2
773 ) RETURN VARCHAR2 IS
774 TYPE ref_cursor IS REF CURSOR;
775 c ref_cursor;
776 s VARCHAR2(1000);
777 l_name qa_plans.name%TYPE;
778 l_names VARCHAR2(20000) DEFAULT NULL;
779 BEGIN
780
781 -- Bug 4270911. CU2 SQL Literal fix. TD #22
782 -- Add p_plan_ids into temp table and rewrite the
783 -- query to select the id from it.
784 -- srhariha. Mon Apr 18 03:52:46 PDT 2005.
785
786 qa_performance_temp_pkg.purge_and_add_ids('QAGTXNB.GET_PLAN_NAMES', p_plan_ids);
787
788 s := 'SELECT name FROM qa_plans WHERE plan_id IN ' ||
789 '( SELECT id FROM qa_performance_temp ' ||
790 ' WHERE key=''QAGTXNB.GET_PLAN_NAMES'' ) ';
791
792 OPEN c FOR s;
793 LOOP
794 FETCH c INTO l_name;
795 EXIT WHEN c%NOTFOUND;
796
797 -- Bug 5161719. SHKALYAN 13-Apr-2006
798 -- Added these conditions to add prefix and suffix to the output message
799 -- only if non null values are passed for prefix and suffix.
800 IF ( l_names IS NOT NULL ) THEN
801 l_names := l_names || ', ';
802 END IF;
803
804 IF ( p_prefix IS NOT NULL ) THEN
805 l_names := l_names || p_prefix;
806 END IF;
807
808 l_names := l_names || l_name;
809
810 IF ( p_suffix IS NOT NULL ) THEN
811 l_names := l_names || p_suffix;
812 END IF;
813
814 END LOOP;
815 CLOSE c;
816
817 RETURN l_names;
818 END get_plan_names_message;
819
820 FUNCTION get_plan_names(p_plan_ids IN VARCHAR2) RETURN VARCHAR2 IS
821 BEGIN
822
823 -- Bug 5161719. SHKALYAN 13-Apr-2006
824 -- Modified the original get_plan_names to call get_plan_names_message
825 -- with null prefix and suffix (to avoid code duplication).
826 -- This will return the vanila plan names in a comma separated string
827 -- as before.
828 return get_plan_names_message( p_plan_ids => p_plan_ids,
829 p_prefix => null,
830 p_suffix => null);
831 END get_plan_names;
832
833
834 PROCEDURE relate_results(p_collection_id NUMBER) IS
835 parent_plan_id number;
836 parent_collection_id number;
837 parent_occurrence number;
838 child_plan_id number;
839 child_collection_id number;
840 child_occurrence number;
841 parent_rec_found varchar2(1);
842
843 cursor child_rec(col_id NUMBER) IS
844 select plan_id, occurrence
845 from qa_results
846 where collection_id = col_id;
847
848 -- 12.1 QWB Usability improvements
849 agg_elements VARCHAR2(4000);
850 agg_vals VARCHAR2(4000);
851 BEGIN
852 child_collection_id := p_collection_id;
853
854 open child_rec(child_collection_id);
855 fetch child_rec into child_plan_id, child_occurrence;
856
857 parent_rec_found :=
858 QA_PARENT_CHILD_PKG.find_parent(
859 p_child_plan_id => child_plan_id,
860 p_child_collection_id => child_collection_id,
861 p_child_occurrence => child_occurrence,
862 x_parent_plan_id => parent_plan_id,
863 x_parent_collection_id => parent_collection_id,
864 x_parent_occurrence => parent_occurrence);
865
866 If parent_rec_found = 'T' then
867 Loop
868 -- 12.1 QWB Usability improvements
869 QA_PARENT_CHILD_PKG.relate(
870 p_parent_plan_id => parent_plan_id,
871 p_parent_collection_id => parent_collection_id,
872 p_parent_occurrence => parent_occurrence,
873 p_child_plan_id => child_plan_id,
874 p_child_collection_id => child_collection_id,
875 p_child_occurrence => child_occurrence,
876 x_agg_elements => agg_elements,
877 x_agg_val => agg_vals);
878 fetch child_rec into child_plan_id, child_occurrence;
879 exit when child_rec%NOTFOUND;
880 End Loop;
881 end if;
882
883 close child_rec;
884
885 END relate_results;
886
887
888 PROCEDURE clear_customs IS
889 BEGIN
890 g_custom1 := '';
891 g_custom2 := '';
892 g_custom3 := '';
893 g_custom4 := '';
894 g_custom5 := '';
895 g_custom6 := '';
896 g_custom7 := '';
897 g_custom8 := '';
898 g_custom9 := '';
899 g_custom10 := '';
900 g_custom11 := '';
901 g_custom12 := '';
902 g_custom13 := '';
903 g_custom14 := '';
904 g_custom15 := '';
905 END clear_customs;
906
907
908 PROCEDURE put_custom1(p_value IN VARCHAR2) IS BEGIN g_custom1 := p_value; END;
909 PROCEDURE put_custom2(p_value IN VARCHAR2) IS BEGIN g_custom2 := p_value; END;
910 PROCEDURE put_custom3(p_value IN VARCHAR2) IS BEGIN g_custom3 := p_value; END;
911 PROCEDURE put_custom4(p_value IN VARCHAR2) IS BEGIN g_custom4 := p_value; END;
912 PROCEDURE put_custom5(p_value IN VARCHAR2) IS BEGIN g_custom5 := p_value; END;
913 PROCEDURE put_custom6(p_value IN VARCHAR2) IS BEGIN g_custom6 := p_value; END;
914 PROCEDURE put_custom7(p_value IN VARCHAR2) IS BEGIN g_custom7 := p_value; END;
915 PROCEDURE put_custom8(p_value IN VARCHAR2) IS BEGIN g_custom8 := p_value; END;
916 PROCEDURE put_custom9(p_value IN VARCHAR2) IS BEGIN g_custom9 := p_value; END;
917 PROCEDURE put_custom10(p_value IN VARCHAR2) IS BEGIN g_custom10 := p_value; END;
918 PROCEDURE put_custom11(p_value IN VARCHAR2) IS BEGIN g_custom11 := p_value; END;
919 PROCEDURE put_custom12(p_value IN VARCHAR2) IS BEGIN g_custom12 := p_value; END;
920 PROCEDURE put_custom13(p_value IN VARCHAR2) IS BEGIN g_custom13 := p_value; END;
921 PROCEDURE put_custom14(p_value IN VARCHAR2) IS BEGIN g_custom14 := p_value; END;
922 PROCEDURE put_custom15(p_value IN VARCHAR2) IS BEGIN g_custom15 := p_value; END;
923
924 FUNCTION get_custom1 RETURN VARCHAR2 IS BEGIN RETURN g_custom1; END;
925 FUNCTION get_custom2 RETURN VARCHAR2 IS BEGIN RETURN g_custom2; END;
926 FUNCTION get_custom3 RETURN VARCHAR2 IS BEGIN RETURN g_custom3; END;
927 FUNCTION get_custom4 RETURN VARCHAR2 IS BEGIN RETURN g_custom4; END;
928 FUNCTION get_custom5 RETURN VARCHAR2 IS BEGIN RETURN g_custom5; END;
929 FUNCTION get_custom6 RETURN VARCHAR2 IS BEGIN RETURN g_custom6; END;
930 FUNCTION get_custom7 RETURN VARCHAR2 IS BEGIN RETURN g_custom7; END;
931 FUNCTION get_custom8 RETURN VARCHAR2 IS BEGIN RETURN g_custom8; END;
932 FUNCTION get_custom9 RETURN VARCHAR2 IS BEGIN RETURN g_custom9; END;
933 FUNCTION get_custom10 RETURN VARCHAR2 IS BEGIN RETURN g_custom10; END;
934 FUNCTION get_custom11 RETURN VARCHAR2 IS BEGIN RETURN g_custom11; END;
935 FUNCTION get_custom12 RETURN VARCHAR2 IS BEGIN RETURN g_custom12; END;
936 FUNCTION get_custom13 RETURN VARCHAR2 IS BEGIN RETURN g_custom13; END;
937 FUNCTION get_custom14 RETURN VARCHAR2 IS BEGIN RETURN g_custom14; END;
938 FUNCTION get_custom15 RETURN VARCHAR2 IS BEGIN RETURN g_custom15; END;
939
940
941 -- Bug 4343758. OA Framework Integration Project.
942 -- Helper method to build result string for background plan.
943 -- srhariha. Wed May 4 03:12:40 PDT 2005.
944
945
946 FUNCTION build_result_string(elements ElementsArray, p_plan_id IN NUMBER)
947 RETURN VARCHAR2 IS
948
949 l_ret_string varchar2(32000);
950
951 --
952 -- bug 5365251
953 -- modified the Cursor to fetch the default values
954 -- ntungare
955 --
956 -- bug 5335509
957 -- modified the cursor definition
958 -- to select the datatype of the collection element
959 -- ntunagre
960 --
961 CURSOR C1 IS
962 /*
963 SELECT char_id
964 from qa_plan_chars
965 where plan_id = p_plan_id
966 and enabled_flag = 1;
967 */
968 SELECT qpc.char_id,
969 NVL(qpc.default_value, qc.default_value) default_value,
970 qc.datatype
971 from qa_plan_chars qpc, qa_chars qc
972 where qpc.plan_id = p_plan_id
973 and qpc.char_id = qc.char_id
974 and qpc.enabled_flag = 1;
975
976 BEGIN
977 l_ret_string := null;
978 for pc_rec in c1 loop
979 if elements.EXISTS(pc_rec.char_id) then
980 -- Bug 4343758. OA Framework Integration.
981 -- Code review incorporation. CR DOC Ref 4.6.1
982 -- Encode the value.
983 -- srhariha. Tue Jun 21 03:12:31 PDT 2005.
984
985 l_ret_string := l_ret_string || '@' || to_char(pc_rec.char_id) || '=' || replace(elements(pc_rec.char_id).value,'@','@@');
986
987 --
988 -- bug 5335509
989 -- checking if the element is of the seq type
990 -- in which case set the default val as
991 -- 'Automatic'
992 -- ntungare
993 --
994 elsif pc_rec.datatype = qa_ss_const.sequence_datatype THEN
995 l_ret_string := l_ret_string || '@' || to_char(pc_rec.char_id) || '=' || QA_SEQUENCE_API.get_sequence_default_value;
996
997 --
998 -- Bug 5365251
999 -- Checking if any collection element has
1000 -- a default value
1001 -- ntungare
1002 --
1003 elsif pc_rec.default_value IS NOT NULL THEN
1004 l_ret_string := l_ret_string || '@' || to_char(pc_rec.char_id) || '=' || replace(pc_rec.default_value,'@','@@');
1005 end if;
1006 end loop;
1007
1008 if (l_ret_string is not null) then
1009 return substr(l_ret_string,2);
1010 end if;
1011 return null;
1012 END build_result_string;
1013
1014
1015 -- Bug 4343758. OA Integration Project.
1016 -- Function to insert post background results
1017 -- transaction scenario. Similar to post_back_ground_result
1018 -- except it uses qa_ss_results_.ssqr_post_result.
1019 -- srhariha. Wed May 4 03:12:40 PDT 2005.
1020
1021
1022 PROCEDURE ssqr_post_background_results( p_txn_number IN NUMBER,
1023 p_org_id IN NUMBER,
1024 p_plan_txn_ids IN VARCHAR2,
1025 p_context_values IN VARCHAR2,
1026 p_collection_id IN NUMBER,
1027 p_txn_header_id IN NUMBER) IS
1028
1029 CURSOR C1 IS
1030 SELECT qa_occurrence_s.nextval
1031 FROM DUAL;
1032
1033 CURSOR C2(c_collection_id NUMBER) IS
1034 SELECT DISTINCT qpt.plan_id
1035 FROM qa_plan_transactions qpt
1036 WHERE qpt.plan_transaction_id IN
1037 ( SELECT id FROM qa_performance_temp
1038 WHERE key='QAGTXNB.SSQR_POST_BACKGROUND_RESULTS' )
1039 AND qpt.enabled_flag = 1
1040 AND qpt.background_collection_flag = 1
1041 AND NOT EXISTS (SELECT 1
1042 FROM qa_results qr
1043 WHERE qr.plan_id = qpt.plan_id
1044 AND qr.collection_id = c_collection_id);
1045
1046 l_plan_id NUMBER;
1047 elements ElementsArray;
1048 l_occurrence NUMBER;
1049 l_ret VARCHAR2(10);
1050 l_result_string VARCHAR2(32000);
1051 x_out_message VARCHAR2(32000);
1052
1053 -- 12.1 QWB Usability Improvements
1054 agg_elements VARCHAR2(4000);
1055 agg_vals VARCHAR2(4000);
1056 BEGIN
1057
1058 IF p_plan_txn_ids is NOT NULL then
1059
1060 qa_performance_temp_pkg.purge_and_add_ids('QAGTXNB.SSQR_POST_BACKGROUND_RESULTS', p_plan_txn_ids);
1061
1062 elements := result_to_array(p_context_values);
1063
1064 -- Bug 4343758. OA Framework Integration.
1065 -- Code review incorporation. CR DOC Ref 4.6.1
1066 -- Used static SQL cursor.
1067 -- srhariha. Tue Jun 21 03:12:31 PDT 2005.
1068 FOR crec IN C2(p_collection_id) LOOP
1069
1070 -- get occurrence
1071 OPEN C1;
1072 FETCH C1 into l_occurrence;
1073 CLOSE C1;
1074
1075 l_result_string := build_result_string(elements,crec.plan_id);
1076
1077 if(l_result_string is not null AND length(l_result_string) >= 0) then
1078 --
1079 -- Bug 4932622. Background results not posted in WIP move transaction.
1080 -- Pass transaction number so that validation API can set proper flags
1081 -- for context elements.
1082 -- srhariha.Wed Jan 11 20:55:18 PST 2006
1083 --
1084 -- 12.1 QWB Usability Improvements
1085 l_ret := QA_SS_RESULTS.SSQR_POST_RESULT(X_OCCURRENCE => l_occurrence,
1086 X_ORG_ID => p_org_id,
1087 X_PLAN_ID => crec.plan_id,
1088 X_SPEC_ID => null,
1089 X_COLLECTION_ID => p_collection_id,
1090 X_TXN_HEADER_ID => p_txn_header_id,
1091 X_PAR_PLAN_ID => null,
1092 X_PAR_COL_ID => null,
1093 X_PAR_OCC => null,
1094 X_RESULT => l_result_string,
1095 X_RESULT1 => null,
1096 X_RESULT2 => null,
1097 X_ENABLED => 1,
1098 X_COMMITTED => 0,
1099 X_TRANSACTION_NUMBER => p_txn_number, -- bug 4932622
1100 X_MESSAGES => x_out_message,
1101 X_AGG_ELEMENTS => agg_elements,
1102 X_AGG_VAL => agg_vals,
1103 P_BACKGROUND_FLAG => 1); --bug 11896067
1104
1105
1106 end if;
1107 END LOOP;
1108 END IF; -- p_plan_txn_ids
1109
1110 EXCEPTION
1111 WHEN OTHERS THEN
1112 raise;
1113
1114 END ssqr_post_background_results;
1115
1116 -- Bug 4343758
1117 -- R12 OAF Txn Integration Project
1118 -- shkalyan 05/07/2005.
1119
1120 PROCEDURE get_child_plans
1121 (
1122 p_plan_id IN NUMBER,
1123 p_org_id IN NUMBER,
1124 p_collection_id IN NUMBER,
1125 p_occurrence IN NUMBER,
1126 p_relationship_type IN NUMBER,
1127 p_data_entry_mode IN NUMBER,
1128 x_return_status OUT NOCOPY VARCHAR2,
1129 x_criteria_values OUT NOCOPY VARCHAR2,
1130 x_child_plan_ids OUT NOCOPY VARCHAR2
1131 )
1132 IS
1133 l_api_name CONSTANT VARCHAR2(30) := 'GET_CHILD_PLANS';
1134 BEGIN
1135 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1136 FND_LOG.string
1137 (
1138 FND_LOG.level_statement,
1139 g_pkg_name || '.' || l_api_name,
1140 'BEFORE GETTING PC CRITERIA VALUES'
1141 );
1142 END IF;
1143
1144 QA_PARENT_CHILD_PKG.get_criteria_values
1145 (
1146 p_parent_plan_id => p_plan_id,
1147 p_parent_collection_id => p_collection_id,
1148 p_parent_occurrence => p_occurrence,
1149 p_organization_id => p_org_id,
1150 x_criteria_values => x_criteria_values
1151 );
1152
1153 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1154 FND_LOG.string
1155 (
1156 FND_LOG.level_statement,
1157 g_pkg_name || '.' || l_api_name,
1158 'BEFORE EVALUATING PC CRITERIA'
1159 );
1160 END IF;
1161
1162 x_return_status :=
1163 QA_PARENT_CHILD_PKG.evaluate_criteria
1164 (
1165 p_plan_id => p_plan_id,
1166 p_criteria_values => x_criteria_values,
1167 p_relationship_type => p_relationship_type,
1168 p_data_entry_mode => p_data_entry_mode,
1169 x_child_plan_ids => x_child_plan_ids
1170 );
1171
1172 END get_child_plans;
1173
1174 -- Bug 4343758
1175 -- R12 OAF Txn Integration Project
1176 -- shkalyan 05/07/2005.
1177
1178 PROCEDURE insert_child_results
1179 (
1180 p_plan_id IN NUMBER,
1181 p_org_id IN NUMBER,
1182 p_collection_id IN NUMBER,
1183 p_occurrence IN NUMBER,
1184 p_relationship_type IN NUMBER,
1185 p_data_entry_mode IN NUMBER,
1186 p_txn_header_id IN NUMBER
1187 )
1188 IS
1189 l_api_name CONSTANT VARCHAR2(30) := 'INSERT_CHILD_RESULTS';
1190 l_criteria_values VARCHAR2(32000);
1191 l_child_plan_ids VARCHAR2(10000);
1192 l_return_status VARCHAR2(1);
1193
1194 BEGIN
1195
1196 get_child_plans
1197 (
1198 p_plan_id => p_plan_id,
1199 p_org_id => p_org_id,
1200 p_collection_id => p_collection_id,
1201 p_occurrence => p_occurrence,
1202 p_relationship_type => p_relationship_type,
1203 p_data_entry_mode => p_data_entry_mode,
1204 x_return_status => l_return_status,
1205 x_criteria_values => l_criteria_values,
1206 x_child_plan_ids => l_child_plan_ids
1207 );
1208
1209 IF( l_return_status = 'T' ) THEN
1210 QA_PARENT_CHILD_PKG.insert_automatic_records
1211 (
1212 p_plan_id => p_plan_id,
1213 p_collection_id => p_collection_id,
1214 p_occurrence => p_occurrence,
1215 p_child_plan_ids => l_child_plan_ids,
1216 p_relationship_type => p_relationship_type,
1217 p_data_entry_mode => p_data_entry_mode,
1218 p_criteria_values => l_criteria_values,
1219 p_org_id => p_org_id,
1220 p_spec_id => null,
1221 x_status => l_return_status,
1222 p_txn_header_id => p_txn_header_id
1223 );
1224 END IF;
1225
1226 END insert_child_results;
1227
1228 -- Bug 4343758
1229 -- R12 OAF Txn Integration Project
1230 -- shkalyan 05/10/2005.
1231 -- This function is used by parent Txns to check whether the Quality
1232 -- Results entered during the Transaction can be committed.
1233 FUNCTION is_commit_allowed(
1234 p_api_version IN NUMBER := NULL,
1235 p_init_msg_list IN VARCHAR2 := NULL,
1236 p_commit IN VARCHAR2 := NULL,
1237 p_validation_level IN NUMBER := NULL,
1238 p_txn_number IN NUMBER,
1239 p_org_id IN NUMBER,
1240 p_txn_header_id IN NUMBER := NULL,
1241 p_collection_id IN NUMBER,
1242 p_plan_txn_ids IN VARCHAR2 := NULL,
1243 x_plan_names OUT NOCOPY VARCHAR2) RETURN VARCHAR2
1244 IS
1245 l_api_name CONSTANT VARCHAR2(30) := 'IS_COMMIT_ALLOWED';
1246
1247 -- Bug 5161719. SHKALYAN 13-Apr-2006
1248 -- Added these variables to form the message in the required format
1249 -- Final message will be of the form
1250 -- "Quality Collection Plan: XXX" (or)
1251 -- "Quality Collection Plan: YYY ( Child of ZZZ )"
1252 l_space CONSTANT VARCHAR2(2) := ' ';
1253 l_separator CONSTANT VARCHAR2(2) := ', ';
1254 -- bug 13924421. Increased the variable size.
1255 l_prefix1 VARCHAR2(300);
1256 l_prefix2 CONSTANT VARCHAR2(2) := ': ';
1257 l_suffix1 CONSTANT VARCHAR2(3) := ' ( ';
1258 -- bug 13924421. Increased the variable size.
1259 l_suffix2 VARCHAR2(300);
1260 l_suffix3 CONSTANT VARCHAR2(3) := ' ) ';
1261
1262 l_criteria_values VARCHAR2(32000);
1263 l_plan_ids VARCHAR2(10000);
1264 l_child_plan_ids VARCHAR2(10000);
1265 l_return_status VARCHAR2(1);
1266
1267 TYPE number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1268 l_plans number_tab;
1269
1270 -- Bug 5161719. SHKALYAN 13-Apr-2006
1271 -- this array is needed for storing parent plan names for the message
1272 TYPE plan_name_tab IS TABLE OF qa_plans.name%TYPE INDEX BY BINARY_INTEGER;
1273 l_plan_names plan_name_tab;
1274
1275 l_occurrences number_tab;
1276
1277 BEGIN
1278
1279 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1280 FND_LOG.string
1281 (
1282 FND_LOG.level_procedure,
1283 g_pkg_name || '.' || l_api_name,
1284 'ENTERING PROCEDURE: p_txn_number: ' || p_txn_number || ' p_org_id: ' || p_org_id || ' p_txn_header_id: ' || p_txn_header_id || ' p_collection_id: ' || p_collection_id || ' p_plan_txn_ids: ' || p_plan_txn_ids
1285 );
1286 END IF;
1287
1288 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1289 FND_LOG.string
1290 (
1291 FND_LOG.level_statement,
1292 g_pkg_name || '.' || l_api_name,
1293 'BEFORE CHECKING MANDATORY COLLECTION'
1294 );
1295 END IF;
1296
1297 -- Bug 5161719. SHKALYAN 13-Apr-2006
1298 -- Populate the prefix and suffix text from seed.
1299 -- contains text "Quality Collection Plan: "
1300 FND_MESSAGE.set_name('QA','QA_QLTY_COLLECTION_PLAN');
1301 l_prefix1 := FND_MESSAGE.get;
1302
1303 -- contains text "Child of ";
1304 FND_MESSAGE.set_name('QA','QA_CHILD_OF');
1305 l_suffix2 := FND_MESSAGE.get;
1306
1307 -- Check if Results have been entered for all mandatory plans
1308 -- for the given Txn
1309 l_return_status :=
1310 commit_allowed
1311 (
1312 p_txn_number => p_txn_number,
1313 p_org_id => p_org_id,
1314 p_plan_txn_ids => p_plan_txn_ids,
1315 p_collection_id => p_collection_id,
1316 x_plan_ids => l_plan_ids
1317 );
1318
1319 IF ( l_return_status <> 'T' ) THEN
1320
1321 -- Return a Comma separated list of plan names which are incomplete
1322 -- Bug 5161719. SHKALYAN 13-Apr-2006
1323 -- Modified to call new function with prefix and suffix
1324 x_plan_names :=
1325 get_plan_names_message
1326 (
1327 p_plan_ids => l_plan_ids,
1328 p_prefix => l_prefix1 || l_prefix2,
1329 p_suffix => NULL
1330 );
1331
1332 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1333 FND_LOG.string
1334 (
1335 FND_LOG.level_procedure,
1336 g_pkg_name || '.' || l_api_name,
1337 'EXITING PROCEDURE: INCOMPLETE PLANS - IDS: ' || l_plan_ids || ' NAMES: ' || x_plan_names
1338 );
1339 END IF;
1340
1341 return 'F';
1342 END IF;
1343
1344 l_plan_ids := '';
1345
1346 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1347 FND_LOG.string
1348 (
1349 FND_LOG.level_statement,
1350 g_pkg_name || '.' || l_api_name,
1351 'BEFORE VALIDATING IMMEDIATE CHILD COLLECTION'
1352 );
1353 END IF;
1354
1355 -- Get all the distinct plans and occurences from QA_RESULTS
1356 -- for the given collection_id
1357 -- Bug 5161719. SHKALYAN 13-Apr-2006
1358 -- Modified cursor to include plan name for the message
1359 SELECT QR.plan_id,
1360 QP.name,
1361 QR.occurrence
1362 BULK COLLECT INTO
1363 l_plans,
1364 l_plan_names,
1365 l_occurrences
1366 FROM QA_RESULTS QR,
1367 QA_PLANS QP
1368 WHERE QP.plan_id = QR.plan_id
1369 AND QR.collection_id = p_collection_id;
1370
1371 -- Bug 4343758. OA Framework Integration project.
1372 -- Added a null check.
1373 -- srhariha. Tue May 24 23:18:48 PDT 2005.
1374
1375 IF l_plans.FIRST IS NOT NULL THEN
1376
1377 FOR i IN l_plans.FIRST .. l_plans.LAST LOOP
1378
1379 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1380 FND_LOG.string
1381 (
1382 FND_LOG.level_statement,
1383 g_pkg_name || '.' || l_api_name,
1384 'BEFORE GETTING CHILD PLANS FOR PLAN_ID: ' || l_plans(i) || ' OCCURRENCE: ' || l_occurrences(i)
1385 );
1386 END IF;
1387
1388 -- Bug 4343758. OA Framework Integration project.
1389 -- data_entry_mode for immediate is 1.
1390 -- srhariha. Tue May 24 22:53:40 PDT 2005.
1391
1392 -- Get Immediate Children for the current plan
1393 get_child_plans
1394 (
1395 p_plan_id => l_plans(i),
1396 p_org_id => p_org_id,
1397 p_collection_id => p_collection_id,
1398 p_occurrence => l_occurrences(i),
1399 p_relationship_type => 1,
1400 p_data_entry_mode => 1, -- Immediate
1401 x_return_status => l_return_status,
1402 x_criteria_values => l_criteria_values,
1403 x_child_plan_ids => l_child_plan_ids
1404 );
1405
1406 IF( l_return_status = 'T' ) THEN
1407
1408 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1409 FND_LOG.string
1410 (
1411 FND_LOG.level_statement,
1412 g_pkg_name || '.' || l_api_name,
1413 'BEFORE CHECKING IF COMMIT IS ALLOWED FOR IMMEDIATE CHILDREN: ' || l_child_plan_ids
1414 );
1415 END IF;
1416
1417 -- Check if Results have been submitted for Immediate Child Plans
1418 -- Bug 5161719. SHKALYAN 13-Apr-2006
1419 -- Modified to call new overloaded QA_PARENT_CHILD_PKG.commit_allowed
1420 -- so that incomplete child plan ids are obtained
1421 l_return_status :=
1422 QA_PARENT_CHILD_PKG.commit_allowed
1423 (
1424 p_plan_id => l_plans(i),
1425 p_collection_id => p_collection_id,
1426 p_occurrence => l_occurrences(i),
1427 p_child_plan_ids => l_child_plan_ids,
1428 x_incomplete_plan_ids => l_plan_ids
1429 );
1430
1431 IF ( l_return_status <> 'T' ) THEN
1432
1433 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1434 FND_LOG.string
1435 (
1436 FND_LOG.level_statement,
1437 g_pkg_name || '.' || l_api_name,
1438 'ALL CHILD RESULTS ARE NOT CAPTURED FOR PLAN: ' || l_plans(i)
1439 );
1440 END IF;
1441
1442 -- Bug 5161719. SHKALYAN 13-Apr-2006
1443 -- Form the message for each child plan that is not completed
1444 -- Message will be of the form:
1445 -- "Quality Collection Plan: <Child Plan Name> ( Child of <Parent Plan Name> )"
1446 x_plan_names := x_plan_names ||
1447 l_separator ||
1448 get_plan_names_message
1449 (
1450 p_plan_ids => l_plan_ids,
1451 p_prefix => l_prefix1 || l_prefix2,
1452 p_suffix => l_suffix1 ||
1453 l_suffix2 ||
1454 l_space ||
1455 l_plan_names(i) ||
1456 l_suffix3
1457 );
1458
1459 l_plan_ids := '';
1460 END IF;
1461
1462 END IF; -- l_return_status ='T'
1463 END LOOP;
1464
1465 END IF; -- l_plans.FIRST is not null
1466
1467 IF ( LENGTH( x_plan_names ) > 0 ) THEN
1468
1469 -- Bug 5161719. SHKALYAN 13-Apr-2006
1470 -- Remove the leading comma
1471 x_plan_names := SUBSTR( x_plan_names , LENGTH(l_separator) + 1 );
1472
1473 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1474 FND_LOG.string
1475 (
1476 FND_LOG.level_procedure,
1477 g_pkg_name || '.' || l_api_name,
1478 'EXITING PROCEDURE: INCOMPLETE PLANS : ' || x_plan_names
1479 );
1480 END IF;
1481
1482 return 'F';
1483 END IF;
1484
1485 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1486 FND_LOG.string
1487 (
1488 FND_LOG.level_procedure,
1489 g_pkg_name || '.' || l_api_name,
1490 'EXITING PROCEDURE: COMMIT IS ALLOWED'
1491 );
1492 END IF;
1493
1494 return 'T';
1495
1496 END is_commit_allowed;
1497
1498 -- Bug 4343758
1499 -- R12 OAF Txn Integration Project
1500 -- shkalyan 05/07/2005.
1501 -- This is an API for performing the post commit processing in
1502 -- transaction integration scenario. This API performs the following actions
1503 -- Insert Automatic and History Results.
1504 -- Post Background results for the transaction.
1505 -- Generate Sequence element values.
1506 -- Enable the Quality Results
1507 -- Fire Background actions.
1508
1509 PROCEDURE process_txn_post_commit(
1510 p_api_version IN NUMBER := NULL,
1511 p_init_msg_list IN VARCHAR2 := NULL,
1512 p_commit IN VARCHAR2 := NULL,
1513 p_validation_level IN NUMBER := NULL,
1514 p_txn_number IN NUMBER,
1515 p_org_id IN NUMBER,
1516 p_txn_header_id IN NUMBER,
1517 p_collection_id IN NUMBER,
1518 p_plan_txn_ids IN VARCHAR2 := NULL,
1519 p_context_values IN VARCHAR2,
1520 p_context_ids IN VARCHAR2 := NULL,
1521 p_generated_values IN VARCHAR2 := NULL,
1522 x_return_status OUT NOCOPY VARCHAR2,
1523 x_msg_count OUT NOCOPY NUMBER,
1524 x_msg_data OUT NOCOPY VARCHAR2)
1525 IS
1526 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_TXN_POST_COMMIT';
1527 l_api_version CONSTANT NUMBER := 1.0;
1528
1529 l_commit BOOLEAN;
1530 l_return_status VARCHAR2(1);
1531
1532 TYPE number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1533 l_plan_ids number_tab;
1534 l_occurrences number_tab;
1535 BEGIN
1536
1537 l_commit := FND_API.To_Boolean( NVL(p_commit, FND_API.G_FALSE) );
1538
1539 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1540 FND_LOG.string
1541 (
1542 FND_LOG.level_procedure,
1543 g_pkg_name || '.' || l_api_name,
1544 'ENTERING PROCEDURE: p_txn_number: ' || p_txn_number || ' p_org_id: ' || p_org_id || ' p_txn_header_id: ' || p_txn_header_id ||
1545 ' p_collection_id: ' || p_collection_id || ' p_plan_txn_ids: ' || p_plan_txn_ids || ' p_context_values: ' || p_context_values || ' p_context_ids: ' || p_context_ids
1546 );
1547 END IF;
1548
1549 -- Standard Start of API savepoint
1550 SAVEPOINT process_txn_post_commit_GRP;
1551
1552 -- Standard call to check for call compatibility.
1553 IF NOT FND_API.Compatible_API_Call
1554 (
1555 l_api_version,
1556 NVL( p_api_version, 1.0 ),
1557 l_api_name,
1558 g_pkg_name
1559 ) THEN
1560 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1561 END IF;
1562
1563 -- Initialize message list if p_init_msg_list is set to TRUE.
1564 IF FND_API.to_Boolean( NVL( p_init_msg_list, FND_API.G_FALSE ) ) THEN
1565 FND_MSG_PUB.initialize;
1566 END IF;
1567
1568 -- Initialize API return status to success
1569 x_return_status := FND_API.G_RET_STS_SUCCESS;
1570
1571 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1572 FND_LOG.string
1573 (
1574 FND_LOG.level_statement,
1575 g_pkg_name || '.' || l_api_name,
1576 'BEFORE POSTING BACKGROUND RESULTS'
1577 );
1578 END IF;
1579
1580 -- Post Background Results for the Given Context
1581 ssqr_post_background_results
1582 (
1583 p_txn_number => p_txn_number,
1584 p_org_id => p_org_id,
1585 p_plan_txn_ids => p_plan_txn_ids,
1586 p_context_values => p_context_values,
1587 p_collection_id => p_collection_id,
1588 p_txn_header_id => p_txn_header_id
1589 );
1590
1591 -- Get all the distinct plans and occurences from QA_RESULTS
1592 SELECT plan_id,
1593 occurrence
1594 BULK COLLECT INTO
1595 l_plan_ids,
1596 l_occurrences
1597 FROM QA_RESULTS
1598 WHERE collection_id = p_collection_id;
1599
1600
1601 -- Bug 4343758. OA Framework Integration project.
1602 -- Added a null check.
1603 -- srhariha. Tue May 24 23:18:48 PDT 2005.
1604
1605 IF l_plan_ids.FIRST IS NOT NULL THEN
1606
1607 FOR i IN l_plan_ids.FIRST .. l_plan_ids.LAST LOOP
1608
1609 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1610 FND_LOG.string
1611 (
1612 FND_LOG.level_statement,
1613 g_pkg_name || '.' || l_api_name,
1614 'BEFORE INSERTING AUTOMATIC RECORDS FOR PLAN_ID: ' || l_plan_ids(i) || ' OCCURRENCE: ' || l_occurrences(i)
1615 );
1616 END IF;
1617
1618 -- Insert Automatic Child Records
1619 insert_child_results
1620 (
1621 p_plan_id => l_plan_ids(i),
1622 p_org_id => p_org_id,
1623 p_collection_id => p_collection_id,
1624 p_occurrence => l_occurrences(i),
1625 p_relationship_type => 1,
1626 p_data_entry_mode => 2, -- Automatic
1627 p_txn_header_id => p_txn_header_id
1628 );
1629
1630 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1631 FND_LOG.string
1632 (
1633 FND_LOG.level_statement,
1634 g_pkg_name || '.' || l_api_name,
1635 'BEFORE INSERTING HISTORY RECORDS FOR PLAN_ID: ' || l_plan_ids(i) || ' OCCURRENCE: ' || l_occurrences(i)
1636 );
1637 END IF;
1638
1639 -- Insert History Child Records
1640 insert_child_results
1641 (
1642 p_plan_id => l_plan_ids(i),
1643 p_org_id => p_org_id,
1644 p_collection_id => p_collection_id,
1645 p_occurrence => l_occurrences(i),
1646 p_relationship_type => 1,
1647 p_data_entry_mode => 4, -- History
1648 p_txn_header_id => p_txn_header_id
1649 );
1650
1651 END LOOP;
1652 END IF; -- l_plan_ids.FIRST is not null
1653
1654 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1655 FND_LOG.string
1656 (
1657 FND_LOG.level_statement,
1658 g_pkg_name || '.' || l_api_name,
1659 'BEFORE ENABLING QA RESULTS'
1660 );
1661 END IF;
1662
1663 -- Enable the Results
1664 UPDATE qa_results
1665 SET status = 2
1666 WHERE collection_id = p_collection_id;
1667
1668 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1669 FND_LOG.string
1670 (
1671 FND_LOG.level_statement,
1672 g_pkg_name || '.' || l_api_name,
1673 'BEFORE GENERATING SEQUENCES'
1674 );
1675 END IF;
1676
1677 -- Generate Sequences
1678 QA_SEQUENCE_API.generate_seq_for_txn
1679 (
1680 p_collection_id => p_collection_id,
1681 p_return_status => l_return_status
1682 );
1683
1684 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1686 END IF;
1687
1688 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1689 FND_LOG.string
1690 (
1691 FND_LOG.level_statement,
1692 g_pkg_name || '.' || l_api_name,
1693 'BEFORE FIRING BACKGROUND ACTIONS'
1694 );
1695 END IF;
1696
1697 -- launch quality actions
1698 -- only actions that are performed in commit cycle are to be
1699 -- launched here
1700 IF ( QLTDACTB.do_actions
1701 (
1702 p_collection_id,
1703 1,
1704 NULL,
1705 NULL,
1706 FALSE ,
1707 FALSE,
1708 'DEFERRED' ,
1709 'COLLECTION_ID'
1710 ) = FALSE ) THEN
1711 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1712 END IF;
1713
1714 -- Commit (if requested)
1715 IF ( l_commit ) THEN
1716 COMMIT WORK;
1717 END IF;
1718
1719 -- Standard call to get message count and if count is 1, get message info.
1720 FND_MSG_PUB.Count_And_Get
1721 (
1722 p_count => x_msg_count,
1723 p_data => x_msg_data
1724 );
1725
1726 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1727 FND_LOG.string
1728 (
1729 FND_LOG.level_procedure,
1730 g_pkg_name || '.' || l_api_name,
1731 'EXITING PROCEDURE: SUCCESS'
1732 );
1733 END IF;
1734
1735 EXCEPTION
1736
1737 WHEN FND_API.G_EXC_ERROR THEN
1738 ROLLBACK TO process_txn_post_commit_GRP;
1739 x_return_status := FND_API.G_RET_STS_ERROR;
1740 FND_MSG_PUB.Count_And_Get
1741 (
1742 p_count => x_msg_count,
1743 p_data => x_msg_data
1744 );
1745
1746 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1747 FND_LOG.string
1748 (
1749 FND_LOG.level_procedure,
1750 g_pkg_name || '.' || l_api_name,
1751 'EXITING PROCEDURE: ERROR'
1752 );
1753 END IF;
1754
1755 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1756 ROLLBACK TO process_txn_post_commit_GRP;
1757 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1758 FND_MSG_PUB.Count_And_Get
1759 (
1760 p_count => x_msg_count,
1761 p_data => x_msg_data
1762 );
1763
1764 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1765 FND_LOG.string
1766 (
1767 FND_LOG.level_procedure,
1768 g_pkg_name || '.' || l_api_name,
1769 'EXITING PROCEDURE: ERROR'
1770 );
1771 END IF;
1772
1773 WHEN OTHERS THEN
1774 ROLLBACK TO process_txn_post_commit_GRP;
1775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1776 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
1777 FND_MSG_PUB.Add_Exc_Msg
1778 (
1779 p_pkg_name => g_pkg_name,
1780 p_procedure_name => l_api_name,
1781 p_error_text => SUBSTR(SQLERRM,1,240)
1782 );
1783 END IF;
1784
1785 FND_MSG_PUB.Count_And_Get
1786 (
1787 p_count => x_msg_count,
1788 p_data => x_msg_data
1789 );
1790
1791 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1792 FND_LOG.string
1793 (
1794 FND_LOG.level_procedure,
1795 g_pkg_name || '.' || l_api_name,
1796 'EXITING PROCEDURE: ERROR'
1797 );
1798 END IF;
1799
1800 END process_txn_post_commit;
1801
1802 -- Bug 4343758
1803 -- R12 OAF Txn Integration Project
1804 -- shkalyan 05/10/2005.
1805 -- This function is used for Purging QA Results and their associated
1806 -- Records. This API is called when the parent Transaction is Unsuccessful.
1807 FUNCTION purge_records(
1808 p_txn_number IN NUMBER,
1809 p_org_id IN NUMBER,
1810 p_txn_header_id IN NUMBER := NULL,
1811 p_collection_id IN NUMBER) RETURN NUMBER
1812 IS
1813 l_api_name CONSTANT VARCHAR2(30) := 'PURGE_RECORDS';
1814 l_result_count NUMBER;
1815 BEGIN
1816
1817 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1818 FND_LOG.string
1819 (
1820 FND_LOG.level_procedure,
1821 g_pkg_name || '.' || l_api_name,
1822 'ENTERING PROCEDURE: P_TXN_NUMBER: ' || p_txn_number || ' P_ORG_ID: ' || p_org_id || ' P_TXN_HEADER_ID: ' || p_txn_header_id || ' P_COLLECTION_ID: ' || p_collection_id
1823 );
1824 END IF;
1825
1826 DELETE qa_results
1827 WHERE collection_id = p_collection_id;
1828
1829 l_result_count := SQL%ROWCOUNT;
1830
1831 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1832 FND_LOG.string
1833 (
1834 FND_LOG.level_statement,
1835 g_pkg_name || '.' || l_api_name,
1836 'DELETED ' || l_result_count || ' ROWS FROM QA_RESULTS'
1837 );
1838 END IF;
1839
1840 IF ( l_result_count > 0 ) THEN
1841 DELETE qa_pc_results_relationship
1842 WHERE parent_collection_id = p_collection_id
1843 OR child_collection_id = p_collection_id;
1844
1845 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1846 FND_LOG.string
1847 (
1848 FND_LOG.level_statement,
1849 g_pkg_name || '.' || l_api_name,
1850 'DELETED ' || SQL%ROWCOUNT || ' ROWS FROM QA_PC_RESULTS_RELATIONSHIP'
1851 );
1852 END IF;
1853
1854 END IF;
1855
1856 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1857 FND_LOG.string
1858 (
1859 FND_LOG.level_procedure,
1860 g_pkg_name || '.' || l_api_name,
1861 'EXITING PROCEDURE: SUCCESS'
1862 );
1863 END IF;
1864
1865 RETURN l_result_count;
1866
1867 END purge_records;
1868
1869 -- Bug 4343758. OA Integration Project.
1870 -- Wrapper around evaluate_triggers.
1871 -- Returns comma separated list of transaction id
1872 -- srhariha. Wed May 4 03:12:40 PDT 2005.
1873
1874 FUNCTION ssqr_evaluate_triggers(p_txn_number IN NUMBER,
1875 p_org_id IN NUMBER,
1876 p_context_values IN VARCHAR2)
1877 RETURN VARCHAR2 IS
1878
1879 l_txn_id_list VARCHAR2(32000);
1880 l_ret VARCHAR2(3);
1881
1882 BEGIN
1883 l_ret := evaluate_triggers(p_txn_number,p_org_id,p_context_values,l_txn_id_list);
1884
1885 IF l_ret = 'T' THEN
1886 return l_txn_id_list;
1887 END IF;
1888
1889 return null;
1890
1891 END ssqr_evaluate_triggers;
1892
1893 -- This API performs the following actions before eres is fired to have
1894 -- complete data in Quality e-Record in MES.
1895 -- Insert Automatic and History Results.
1896 -- Post Background results for the transaction.
1897 -- Generate Sequence element values.
1898 -- saugupta Mon, 07 Jan 2008 02:37:52 -0800 PDT
1899
1900 PROCEDURE process_txn_for_eres(
1901 p_api_version IN NUMBER := NULL,
1902 p_init_msg_list IN VARCHAR2 := NULL,
1903 p_commit IN VARCHAR2 := NULL,
1904 p_validation_level IN NUMBER := NULL,
1905 p_txn_number IN NUMBER,
1906 p_org_id IN NUMBER,
1907 p_txn_header_id IN NUMBER,
1908 p_collection_id IN NUMBER,
1909 p_plan_txn_ids IN VARCHAR2 := NULL,
1910 p_context_values IN VARCHAR2,
1911 p_context_ids IN VARCHAR2 := NULL,
1912 p_generated_values IN VARCHAR2 := NULL,
1913 x_return_status OUT NOCOPY VARCHAR2,
1914 x_msg_count OUT NOCOPY NUMBER,
1915 x_msg_data OUT NOCOPY VARCHAR2)
1916 IS
1917 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_TXN_FOR_ERES';
1918 l_api_version CONSTANT NUMBER := 1.0;
1919
1920 l_commit BOOLEAN;
1921 l_return_status VARCHAR2(1);
1922
1923 TYPE number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1924 l_plan_ids number_tab;
1925 l_occurrences number_tab;
1926 BEGIN
1927
1928 l_commit := FND_API.To_Boolean( NVL(p_commit, FND_API.G_FALSE) );
1929
1930 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1931 FND_LOG.string
1932 (
1933 FND_LOG.level_procedure,
1934 g_pkg_name || '.' || l_api_name,
1935 'ENTERING PROCEDURE: p_txn_number: ' || p_txn_number || ' p_org_id: ' || p_org_id || ' p_txn_header_id: ' || p_txn_header_id ||
1936 ' p_collection_id: ' || p_collection_id || ' p_plan_txn_ids: ' || p_plan_txn_ids || ' p_context_values: ' || p_context_values || '
1937 p_context_ids: ' || p_context_ids
1938 );
1939 END IF;
1940
1941 -- Standard Start of API savepoint
1942 SAVEPOINT process_txn_for_eres_GRP;
1943
1944 -- Standard call to check for call compatibility.
1945 IF NOT FND_API.Compatible_API_Call
1946 (
1947 l_api_version,
1948 NVL( p_api_version, 1.0 ),
1949 l_api_name,
1950 g_pkg_name
1951 ) THEN
1952 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1953 END IF;
1954
1955 -- Initialize message list if p_init_msg_list is set to TRUE.
1956 IF FND_API.to_Boolean( NVL( p_init_msg_list, FND_API.G_FALSE ) ) THEN
1957 FND_MSG_PUB.initialize;
1958 END IF;
1959
1960 -- Initialize API return status to success
1961 x_return_status := FND_API.G_RET_STS_SUCCESS;
1962 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1963 FND_LOG.string
1964 (
1965 FND_LOG.level_statement,
1966 g_pkg_name || '.' || l_api_name,
1967 'BEFORE FIRING MES ERES EVENT'
1968 );
1969 END IF;
1970
1971 -- Post Background Results for the Given Context
1972 ssqr_post_background_results
1973 (
1974 p_txn_number => p_txn_number,
1975 p_org_id => p_org_id,
1976 p_plan_txn_ids => p_plan_txn_ids,
1977 p_context_values => p_context_values,
1978 p_collection_id => p_collection_id,
1979 p_txn_header_id => p_txn_header_id
1980 );
1981
1982 -- Get all the distinct plans and occurences from QA_RESULTS
1983 SELECT plan_id,
1984 occurrence
1985 BULK COLLECT INTO
1986 l_plan_ids,
1987 l_occurrences
1988 FROM QA_RESULTS
1989 WHERE collection_id = p_collection_id;
1990
1991 IF l_plan_ids.FIRST IS NOT NULL THEN
1992
1993 FOR i IN l_plan_ids.FIRST .. l_plan_ids.LAST LOOP
1994
1995 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1996 FND_LOG.string
1997 (
1998 FND_LOG.level_statement,
1999 g_pkg_name || '.' || l_api_name,
2000 'BEFORE INSERTING AUTOMATIC RECORDS FOR PLAN_ID: ' || l_plan_ids(i) || ' OCCURRENCE: ' || l_occurrences(i)
2001 );
2002 END IF;
2003
2004 -- Insert Automatic Child Records
2005 insert_child_results
2006 (
2007 p_plan_id => l_plan_ids(i),
2008 p_org_id => p_org_id,
2009 p_collection_id => p_collection_id,
2010 p_occurrence => l_occurrences(i),
2011 p_relationship_type => 1,
2012 p_data_entry_mode => 2, -- Automatic
2013 p_txn_header_id => p_txn_header_id
2014 );
2015
2016 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
2017 FND_LOG.string
2018 (
2019 FND_LOG.level_statement,
2020 g_pkg_name || '.' || l_api_name,
2021 'BEFORE INSERTING HISTORY RECORDS FOR PLAN_ID: ' || l_plan_ids(i) || ' OCCURRENCE: ' || l_occurrences(i)
2022 );
2023 END IF;
2024 -- Insert History Child Records
2025 insert_child_results
2026 (
2027 p_plan_id => l_plan_ids(i),
2028 p_org_id => p_org_id,
2029 p_collection_id => p_collection_id,
2030 p_occurrence => l_occurrences(i),
2031 p_relationship_type => 1,
2032 p_data_entry_mode => 4, -- History
2033 p_txn_header_id => p_txn_header_id
2034 );
2035
2036 END LOOP;
2037 END IF; -- l_plan_ids.FIRST is not null
2038
2039
2040 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
2041 FND_LOG.string
2042 (
2043 FND_LOG.level_statement,
2044 g_pkg_name || '.' || l_api_name,
2045 'BEFORE ENABLING QA RESULTS'
2046 );
2047 END IF;
2048
2049 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
2050 FND_LOG.string
2051 (
2052 FND_LOG.level_statement,
2053 g_pkg_name || '.' || l_api_name,
2054 'BEFORE GENERATING SEQUENCES'
2055 );
2056 END IF;
2057
2058 -- Generate Sequences
2059 QA_SEQUENCE_API.generate_seq_for_txn
2060 (
2061 p_collection_id => p_collection_id,
2062 p_return_status => l_return_status
2063 );
2064
2065 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2066 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2067 END IF;
2068
2069 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
2070 FND_LOG.string
2071 (
2072 FND_LOG.level_statement,
2073 g_pkg_name || '.' || l_api_name,
2074 'BEFORE FIRING BACKGROUND ACTIONS'
2075 );
2076 END IF;
2077
2078 -- Enable the Results
2079 /*
2080 UPDATE qa_results
2081 SET status = 2
2082 WHERE collection_id = p_collection_id;
2083 */
2084
2085 -- launch quality actions
2086 -- only actions that are performed in commit cycle are to be
2087 -- launched here
2088 /* IF ( QLTDACTB.do_actions
2089 (
2090 p_collection_id,
2091 1,
2092 NULL,
2093 NULL,
2094 FALSE ,
2095 FALSE,
2096 'DEFERRED' ,
2097 'COLLECTION_ID'
2098 ) = FALSE ) THEN
2099 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2100 END IF;
2101 */
2102
2103 -- Commit (if requested)
2104 IF ( l_commit ) THEN
2105 COMMIT WORK;
2106 END IF;
2107
2108 -- Standard call to get message count and if count is 1, get message info.
2109 FND_MSG_PUB.Count_And_Get
2110 (
2111 p_count => x_msg_count,
2112 p_data => x_msg_data
2113 );
2114
2115 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2116 FND_LOG.string
2117 (
2118 FND_LOG.level_procedure,
2119 g_pkg_name || '.' || l_api_name,
2120 'EXITING PROCEDURE: SUCCESS'
2121 );
2122 END IF;
2123
2124
2125 EXCEPTION
2126
2127 WHEN FND_API.G_EXC_ERROR THEN
2128 ROLLBACK TO process_txn_for_eres_GRP;
2129 x_return_status := FND_API.G_RET_STS_ERROR;
2130 FND_MSG_PUB.Count_And_Get
2131 (
2132 p_count => x_msg_count,
2133 p_data => x_msg_data
2134 );
2135
2136 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2137 FND_LOG.string
2138 (
2139 FND_LOG.level_procedure,
2140 g_pkg_name || '.' || l_api_name,
2141 'EXITING PROCEDURE: ERROR'
2142 );
2143 END IF;
2144
2145 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2146 ROLLBACK TO process_txn_for_eres_GRP;
2147 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2148 FND_MSG_PUB.Count_And_Get
2149 (
2150 p_count => x_msg_count,
2151 p_data => x_msg_data
2152 );
2153
2154 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2155 FND_LOG.string
2156 (
2157 FND_LOG.level_procedure,
2158 g_pkg_name || '.' || l_api_name,
2159 'EXITING PROCEDURE: ERROR'
2160 );
2161 END IF;
2162
2163 WHEN OTHERS THEN
2164 ROLLBACK TO process_txn_for_eres_GRP;
2165 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2166 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
2167 FND_MSG_PUB.Add_Exc_Msg
2168 (
2169 p_pkg_name => g_pkg_name,
2170 p_procedure_name => l_api_name,
2171 p_error_text => SUBSTR(SQLERRM,1,240)
2172 );
2173 END IF;
2174
2175 FND_MSG_PUB.Count_And_Get
2176 (
2177 p_count => x_msg_count,
2178 p_data => x_msg_data
2179 );
2180
2181 FND_MSG_PUB.Count_And_Get
2182 (
2183 p_count => x_msg_count,
2184 p_data => x_msg_data
2185 );
2186
2187 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2188 FND_LOG.string
2189 (
2190 FND_LOG.level_procedure,
2191 g_pkg_name || '.' || l_api_name,
2192 'EXITING PROCEDURE: ERROR'
2193 );
2194 END IF;
2195
2196 END process_txn_for_eres;
2197
2198 -- enable and fire background actions for Applicable plans in ERES flow
2199 -- saugupta Mon, 07 Jan 2008 05:47:37 -0800 PDT
2200 PROCEDURE enable_and_fire_action (
2201 p_api_version IN NUMBER := NULL,
2202 p_init_msg_list IN VARCHAR2 := NULL,
2203 p_commit IN VARCHAR2 := NULL,
2204 p_validation_level IN NUMBER := NULL,
2205 p_collection_id IN NUMBER,
2206 x_return_status OUT NOCOPY VARCHAR2,
2207 x_msg_count OUT NOCOPY NUMBER,
2208 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2209
2210 l_api_name CONSTANT VARCHAR2(30) := 'ENABLE_AND_FIRE_ACTION';
2211 l_api_version CONSTANT NUMBER := 1.0;
2212
2213 l_commit BOOLEAN;
2214 l_return_status VARCHAR2(1);
2215
2216 BEGIN
2217
2218 l_commit := FND_API.To_Boolean( NVL(p_commit, FND_API.G_FALSE) );
2219
2220 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2221 FND_LOG.string
2222 (
2223 FND_LOG.level_procedure,
2224 g_pkg_name || '.' || l_api_name,
2225 'ENTERING PROCEDURE' );
2226 END IF;
2227
2228 -- Standard Start of API savepoint
2229 SAVEPOINT enable_and_fire_action_GRP;
2230
2231 -- Standard call to check for call compatibility.
2232 IF NOT FND_API.Compatible_API_Call
2233 (
2234 l_api_version,
2235 NVL( p_api_version, 1.0 ),
2236 l_api_name,
2237 g_pkg_name
2238 ) THEN
2239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2240 END IF;
2241
2242 -- Initialize message list if p_init_msg_list is set to TRUE.
2243 IF FND_API.to_Boolean( NVL( p_init_msg_list, FND_API.G_FALSE ) ) THEN
2244 FND_MSG_PUB.initialize;
2245 END IF;
2246
2247 -- Initialize API return status to success
2248 x_return_status := FND_API.G_RET_STS_SUCCESS;
2249 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
2250 FND_LOG.string
2251 (
2252 FND_LOG.level_statement,
2253 g_pkg_name || '.' || l_api_name,
2254 'BEFORE ENABLING RESULTS'
2255 );
2256 END IF;
2257
2258 UPDATE qa_results
2259 SET status=2
2260 WHERE collection_id = p_collection_id;
2261
2262 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
2263 FND_LOG.string
2264 (
2265 FND_LOG.level_statement,
2266 g_pkg_name || '.' || l_api_name,
2267 'BEFORE FIRING BACKGROUND ACTIONS'
2268 );
2269 END IF;
2270
2271 -- launch quality actions
2272 -- only actions that are performed in commit cycle are to be
2273 -- launched here
2274 IF ( QLTDACTB.do_actions
2275 (
2276 p_collection_id,
2277 1,
2278 NULL,
2279 NULL,
2280 FALSE ,
2281 FALSE,
2282 'DEFERRED' ,
2283 'COLLECTION_ID'
2284 ) = FALSE ) THEN
2285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2286 END IF;
2287
2288 IF ( l_commit ) THEN
2289 COMMIT WORK;
2290 END IF;
2291
2292 -- Standard call to get message count and if count is 1, get message info.
2293 FND_MSG_PUB.Count_And_Get
2294 (
2295 p_count => x_msg_count,
2296 p_data => x_msg_data
2297 );
2298
2299 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2300 FND_LOG.string
2301 (
2302 FND_LOG.level_procedure,
2303 g_pkg_name || '.' || l_api_name,
2304 'EXITING PROCEDURE: SUCCESS'
2305 );
2306 END IF;
2307
2308 EXCEPTION
2309
2310 WHEN FND_API.G_EXC_ERROR THEN
2311 ROLLBACK TO enable_and_fire_action_GRP;
2312 x_return_status := FND_API.G_RET_STS_ERROR;
2313 FND_MSG_PUB.Count_And_Get
2314 (
2315 p_count => x_msg_count,
2316 p_data => x_msg_data
2317 );
2318
2319 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2320 FND_LOG.string
2321 (
2322 FND_LOG.level_procedure,
2323 g_pkg_name || '.' || l_api_name,
2324 'EXITING PROCEDURE: ERROR'
2325 );
2326 END IF;
2327
2328 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2329 ROLLBACK TO enable_and_fire_action_GRP;
2330 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2331 FND_MSG_PUB.Count_And_Get
2332 (
2333 p_count => x_msg_count,
2334 p_data => x_msg_data
2335 );
2336
2337 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2338 FND_LOG.string
2339 (
2340 FND_LOG.level_procedure,
2341 g_pkg_name || '.' || l_api_name,
2342 'EXITING PROCEDURE: ERROR'
2343 );
2344 END IF;
2345
2346 WHEN OTHERS THEN
2347 ROLLBACK TO enable_and_fire_action_GRP;
2348 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2349 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
2350 FND_MSG_PUB.Add_Exc_Msg
2351 (
2352 p_pkg_name => g_pkg_name,
2353 p_procedure_name => l_api_name,
2354 p_error_text => SUBSTR(SQLERRM,1,240)
2355 );
2356 END IF;
2357
2358 FND_MSG_PUB.Count_And_Get
2359 (
2360 p_count => x_msg_count,
2361 p_data => x_msg_data
2362 );
2363
2364 FND_MSG_PUB.Count_And_Get
2365 (
2366 p_count => x_msg_count,
2367 p_data => x_msg_data
2368 );
2369
2370 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
2371 FND_LOG.string
2372 (
2373 FND_LOG.level_procedure,
2374 g_pkg_name || '.' || l_api_name,
2375 'EXITING PROCEDURE: ERROR'
2376 );
2377 END IF;
2378
2379
2380 END enable_and_fire_action;
2381
2382
2383 END qa_txn_grp;