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