1 PACKAGE BODY QA_RESULT_GRP AS
2 /* $Header: qltgresb.plb 120.8.12000000.2 2007/02/20 21:43:03 shkalyan ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='QA_RESULT_GRP';
5
6 -- R12 ERES Support in Service Family. Bug 4345768 Start
7 -- Global variable to hold the status of the eRecord for Txn Acknowledgement.
8 g_erec_success CONSTANT VARCHAR2(30) := 'SUCCESS';
9 g_erec_error CONSTANT VARCHAR2(30) := 'ERROR';
10 g_ackn_by CONSTANT VARCHAR2(30) := 'QUALITY TXN INTEGRATION';
11 -- R12 ERES Support in Service Family. Bug 4345768 End
12
13 PROCEDURE Purge
14 ( p_api_version IN NUMBER ,
15 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
16 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
17 p_validation_level IN NUMBER :=
18 FND_API.G_VALID_LEVEL_FULL ,
19 p_collection_id IN NUMBER ,
20 p_return_status OUT NOCOPY VARCHAR2 ,
21 p_msg_count OUT NOCOPY NUMBER ,
22 p_msg_data OUT NOCOPY VARCHAR2
23 )
24 IS
25 l_api_name CONSTANT VARCHAR2(30) := 'Purge';
26 l_api_version CONSTANT NUMBER := 1.0;
27
28 -- R12 ERES Support in Service Family. Bug 4345768
29 l_result_count NUMBER;
30 BEGIN
31
32 -- Standard Start of API savepoint
33 SAVEPOINT Purge_GRP;
34 -- Standard call to check for call compatibility.
35 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
36 p_api_version ,
37 l_api_name ,
38 G_PKG_NAME )
39 THEN
40 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
41 END IF;
42 -- Initialize message list if p_init_msg_list is set to TRUE.
43 IF FND_API.to_Boolean( p_init_msg_list ) THEN
44 FND_MSG_PUB.initialize;
45 END IF;
46 -- Initialize API return status to success
47 p_return_status := FND_API.G_RET_STS_SUCCESS;
48
49 IF p_collection_id is not null THEN
50
51 DELETE qa_results
52 WHERE collection_id = p_collection_id;
53
54 -- R12 ERES Support in Service Family. Bug 4345768
55 l_result_count := SQL%ROWCOUNT;
56 END IF;
57
58 -- R12 ERES Support in Service Family. Bug 4345768 Start
59 -- Purge the Result Relationship
60 IF ( l_result_count > 0 ) THEN
61 DELETE qa_pc_results_relationship
62 WHERE parent_collection_id = p_collection_id
63 OR child_collection_id = p_collection_id;
64
65 -- Bug 5502106. Action Logs must also be purged.
66 DELETE qa_action_log
67 WHERE collection_id = p_collection_id;
68 END IF;
69 -- R12 ERES Support in Service Family. Bug 4345768 End
70
71 -- Standard check of p_commit.
72 IF FND_API.To_Boolean( p_commit ) THEN
73 COMMIT WORK;
74 END IF;
75 -- Standard call to get message count and if count is 1, get message info.
76 FND_MSG_PUB.Count_And_Get
77 ( p_count => p_msg_count ,
78 p_data => p_msg_data
79 );
80
81 EXCEPTION
82 WHEN FND_API.G_EXC_ERROR THEN
83 ROLLBACK TO Purge_GRP;
84 p_return_status := FND_API.G_RET_STS_ERROR ;
85 FND_MSG_PUB.Count_And_Get
86 ( p_count => p_msg_count ,
87 p_data => p_msg_data
88 );
89 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
90 ROLLBACK TO Purge_GRP;
91 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
92 FND_MSG_PUB.Count_And_Get
93 ( p_count => p_msg_count ,
94 p_data => p_msg_data
95 );
96 WHEN OTHERS THEN
97 ROLLBACK TO Purge_GRP;
98 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
99 IF FND_MSG_PUB.Check_Msg_Level
100 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
101 THEN
102 FND_MSG_PUB.Add_Exc_Msg
103 ( G_PKG_NAME ,
104 l_api_name
105 );
106 END IF;
107 FND_MSG_PUB.Count_And_Get
108 ( p_count => p_msg_count ,
109 p_data => p_msg_data
110 );
111 END Purge;
112
113 --
114 -- Added new parameter p_incident_id for Service Request Enhancements Project
115 -- Default value is null for backward compatibility
116 -- rkunchal Tue Sep 3 10:20:12 PDT 2002
117 --
118
119 PROCEDURE Enable
120 ( p_api_version IN NUMBER ,
121 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
122 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
123 p_validation_level IN NUMBER :=
124 FND_API.G_VALID_LEVEL_FULL ,
125 p_collection_id IN NUMBER ,
126 p_return_status OUT NOCOPY VARCHAR2 ,
127 p_msg_count OUT NOCOPY NUMBER ,
128 p_msg_data OUT NOCOPY VARCHAR2 ,
129 p_incident_id IN NUMBER
130 )
131 IS
132 l_api_name CONSTANT VARCHAR2(30) := 'Enable';
133 l_api_version CONSTANT NUMBER := 1.0;
134
135
136 -- Gapless Sequence Proj. rponnusa Wed Jul 30 04:52:45 PDT 2003
137 l_return_status VARCHAR2(3);
138
139 --
140 -- bug 5642050
141 -- added a new variable to get the count of
142 -- rows updated
143 -- ntungare Sun Nov 5 22:28:57 PST 2006
144 --
145 no_of_rows_updated NUMBER;
146
147 BEGIN
148
149 -- Standard Start of API savepoint
150 SAVEPOINT Enable_GRP;
151 -- Standard call to check for call compatibility.
152 IF NOT FND_API.Compatible_API_Call ( l_api_version ,
153 p_api_version ,
154 l_api_name ,
155 G_PKG_NAME )
156 THEN
157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
158 END IF;
159 -- Initialize message list if p_init_msg_list is set to TRUE.
160 IF FND_API.to_Boolean( p_init_msg_list ) THEN
161 FND_MSG_PUB.initialize;
162 END IF;
163 -- Initialize API return status to success
164 p_return_status := FND_API.G_RET_STS_SUCCESS;
165
166 IF p_collection_id is not null THEN
167
168 --
169 -- Modified the following UPDATE for Service Request Enhancements Project
170 -- The incident_id should also be updated during enabling the results
171 -- rkunchal Tue Sep 3 10:20:12 PDT 2002
172 --
173 -- Bug 4473407.
174 -- Update the status of the records to 2(Enabled Status) only if it
175 -- is not already 2.
176 -- ntungare Thu Sep 8 07:24:20 PDT 2005
177 --
178 UPDATE qa_results
179 SET status = 2,
180 cs_incident_id = nvl(p_incident_id, cs_incident_id)
181 WHERE p_collection_id = collection_id
182 AND status <> 2;
183
184 --
185 -- bug 5642050
186 -- getting a count of rows updated
187 -- ntungare Sun Nov 5 22:28:57 PST 2006
188 --
189 no_of_rows_updated := SQL%ROWCOUNT;
190
191 -- Gapless Sequence Proj. rponnusa Wed Jul 30 04:52:45 PDT 2003
192 -- Generate the sequence element value for all the records (including
193 -- child,grand child plans
194 -- call seq. api only if eres is not enabled.
195 IF FND_PROFILE.VALUE('EDR_ERES_ENABLED') <> 'Y' THEN
196
197 QA_SEQUENCE_API.Generate_Seq_for_Txn(
198 p_collection_id,
199 l_return_status);
200
201 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
202 -- in case of failure raise error.
203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
204 END IF;
205 END IF;
206
207 -- launch quality actions
208 -- only actions that are performed in commit cycle are to be
209 -- launched here
210 --
211 -- Bug 4473407.
212 -- If the previous update statement had updated qa_results then
213 -- SQL%ROWCOUNT would be > 0 and only in that case we call do_actions
214 -- to fire the action. Before this fix qltdactb.do_actions was called
215 -- without any condition and because of this whenever the procedure
216 -- Enable is called the actions are also fired unnecessarily which is
217 -- not correct. The actions should fire only once for the enabled
218 -- records - even if the parent transaction calls QA_RESULT_GRP.ENABLE
219 -- more than once, the updation of qa_results and action firing will
220 -- happen only the first time and nothing would happen during the
221 -- consecutive calls.
222 -- ntungare Thu Sep 8 07:37:32 PDT 2005
223 --
224 --
225 -- bug 5642050
226 -- using the variable to check the updated
227 -- rows count
228 -- ntungare Sun Nov 5 22:28:57 PST 2006
229 --
230 -- IF SQL%ROWCOUNT > 0 THEN
231 IF no_of_rows_updated > 0 THEN
232 IF (QLTDACTB.DO_ACTIONS(p_collection_id,
233 1,
234 NULL,
235 NULL,
236 FALSE ,
237 FALSE,
238 'DEFERRED' ,
239 'COLLECTION_ID'
240 )= FALSE ) then
241 p_msg_count := -1 ;
242 END IF ;
243 END IF ;
244
245 END IF ;
246
247
248 -- Standard check of p_commit.
249 IF FND_API.To_Boolean( p_commit ) THEN
250 COMMIT WORK;
251 END IF;
252
253 -- Standard call to get message count and if count is 1, get message info.
254 FND_MSG_PUB.Count_And_Get
255 ( p_count => p_msg_count ,
256 p_data => p_msg_data
257 );
258
259 EXCEPTION
260 WHEN FND_API.G_EXC_ERROR THEN
261 ROLLBACK TO Enable_GRP;
262 p_return_status := FND_API.G_RET_STS_ERROR ;
263 FND_MSG_PUB.Count_And_Get
264 ( p_count => p_msg_count ,
265 p_data => p_msg_data
266 );
267
268 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
269 ROLLBACK TO Enable_GRP;
270 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
271 FND_MSG_PUB.Count_And_Get
272 ( p_count => p_msg_count ,
273 p_data => p_msg_data
274 );
275 WHEN OTHERS THEN
276 -- ROLLBACK TO Enable_GRP;
277 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
278 IF FND_MSG_PUB.Check_Msg_Level
279 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
280 THEN
281 FND_MSG_PUB.Add_Exc_Msg
282 ( G_PKG_NAME ,
283 l_api_name
284 );
285 END IF;
286 FND_MSG_PUB.Count_And_Get
287 ( p_count => p_msg_count ,
288 p_data => p_msg_data
289 );
290 END Enable;
291
292 /********************************************************************
293
294 Purpose: this procedure has been written specifically for WIP
295 completion transaction. IN WIP COmpletion online , transaction are processed
296 in batch and not row by row. Since collection id is different for each
297 completion txn , our existing Enable API can not be called to
298 enable Quality results for a batch of completion txns.
299 Following procedure accepts a material txn header id for a
300 batch of txn and all the quality data for that batch of
301 transaction are enabled.
302
303 Date: 08/21/98
304 Last Updated: 08/21/98
305 Called From: WIP COMPLETION TXN COMMIT LOGIC
306 Parameter: X_txn_header_id ( identifier for a group of material txns )
307
308 ***********************************************************************/
309 PROCEDURE Enable_QA_Results ( X_Txn_Header_ID Number,
310 P_MSG_COUNT IN OUT NOCOPY NUMBER ) IS
311
312 Cursor C_Collection_id is
313 Select qa_collection_id
314 from mtl_material_transactions_temp
315 where Transaction_header_id = x_txn_header_id
316 and qa_collection_id is not null;
317
318 x_collection_id Number ;
319 x_return_status varchar2(1);
320 x_msg_count Number ;
321 x_msg_data varchar2(2000) ;
322 BEGIN
323 Open c_collection_id ;
324 LOOP
325 fetch c_collection_id into x_collection_id ;
326 exit when c_collection_id%notfound ;
327
328 -- enable quality results
329 ENABLE(
330 p_api_version => 1.0,
331 p_init_msg_list => 'F',
332 p_commit => 'F',
333 p_validation_level => 0,
334 p_collection_id => x_collection_id,
335 p_return_status => x_return_status,
336 p_msg_count => x_msg_count,
337 p_msg_data => x_msg_data);
338
339 -- set the message count to indiacare that actions failed
340 IF ( x_msg_count = -1) THEN
341 p_msg_count := -1 ;
342 END IF ;
343
344 END LOOP ;
345 CLOSE C_COLLECTION_ID ;
346
347 Exception When Others Then
348 return ;
349 END Enable_QA_Results ;
350
351 -- Start R12 EAM Integration. Bug 4345492
352 PROCEDURE enable_and_fire_action (
353 p_api_version IN NUMBER,
354 p_init_msg_list IN VARCHAR2 := NULL,
355 p_commit IN VARCHAR2 := NULL,
356 p_validation_level IN NUMBER := NULL,
357 p_collection_id IN NUMBER,
358 x_return_status OUT NOCOPY VARCHAR2,
359 x_msg_count OUT NOCOPY NUMBER,
360 x_msg_data OUT NOCOPY VARCHAR2) IS
361
362 l_api_name CONSTANT VARCHAR2(30) := 'enable_and_fire_action';
363 l_api_version CONSTANT NUMBER := 1.0;
364 l_error_found BOOLEAN;
365 actions_request_id NUMBER;
366
367 BEGIN
368 -- Standard Start of API savepoint
369 SAVEPOINT enable_and_fire_action_grp;
373 NVL( p_api_version, 1.0 ),
370
371 -- Standard call to check for call compatibility.
372 IF NOT FND_API.Compatible_API_Call (l_api_version,
374 l_api_name,
375 G_PKG_NAME ) THEN
376 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
377 END IF;
378
379 -- Initialize message list if p_init_msg_list is set to TRUE.
380 IF FND_API.to_Boolean( NVL( p_init_msg_list, FND_API.G_FALSE ) ) THEN
381 FND_MSG_PUB.initialize;
382 END IF;
383
384 -- Initialize API return status to error
385 x_return_status := FND_API.G_RET_STS_ERROR;
386
387 qa_results_api.enable(p_collection_id);
388 actions_request_id := fnd_request.submit_request('QA', 'QLTACTWB', NULL,
389 NULL, FALSE, to_char(-p_collection_id));
390
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392
393 -- Standard check of p_commit.
394 IF FND_API.To_Boolean( NVL(p_commit, FND_API.G_FALSE ) ) THEN
395 COMMIT;
396 END IF;
397
398 EXCEPTION
399
400 WHEN FND_API.G_EXC_ERROR THEN
401 ROLLBACK TO enable_and_fire_action_grp;
402 x_return_status := FND_API.G_RET_STS_ERROR ;
403 FND_MSG_PUB.Count_And_Get
404 (p_count => x_msg_count,
405 p_data => x_msg_data
406 );
407
408 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
409 ROLLBACK TO enable_and_fire_action_grp;
410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
411 FND_MSG_PUB.Count_And_Get
412 (p_count => x_msg_count,
413 p_data => x_msg_data
414 );
415
416 WHEN OTHERS THEN
417 ROLLBACK TO enable_and_fire_action_grp;
418 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
419 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
420 FND_MSG_PUB.Add_Exc_Msg
421 (G_PKG_NAME,
422 l_api_name
423 );
424 END IF;
425 FND_MSG_PUB.Count_And_Get
426 (p_count => x_msg_count,
427 p_data => x_msg_data
428 );
429
430 END enable_and_fire_action;
431 -- End R12 EAM Integration. Bug 4345492
432
433 -- R12 ERES Support in Service Family. Bug 4345768 Start
434
435 -- API to retrieve the Quality Results E-Records captured as
436 -- part of a Transaction session ( collection )
437 -- The E-Record IDs are returned as part of the x_qa_erecord_tbl
438 -- output parameter.
439 PROCEDURE get_qa_results_erecords
440 (
441 p_api_version IN NUMBER,
442 p_init_msg_list IN VARCHAR2 := NULL ,
443 p_commit IN VARCHAR2 := NULL ,
444 p_validation_level IN NUMBER := NULL ,
445 p_collection_id IN NUMBER ,
446 x_qa_erecord_tbl OUT NOCOPY qa_erecord_tbl_type ,
447 x_return_status OUT NOCOPY VARCHAR2 ,
448 x_msg_count OUT NOCOPY NUMBER ,
449 x_msg_data OUT NOCOPY VARCHAR2
450 )
451 IS
452 l_api_name CONSTANT VARCHAR2(30) := 'get_qa_results_erecords';
453 l_api_version CONSTANT NUMBER := 1.0;
454
455 ctr NUMBER := 1;
456 l_prev_plan_id NUMBER := -1;
457 l_event_key EDR_PSIG_DOCUMENTS.event_key%TYPE;
458
459 -- Get all occurrences for a collection
460 -- Bug 5508639. SHKALYAN 13-Sep-2006.
461 -- Process only Results which are in disabled status.
462 -- Added status <> 2 check for filtering out already enabled records.
463 -- Also added order by on txn_header_id to get the most recent txn first
464 CURSOR get_occurrences( c_collection_id NUMBER )
465 IS
466 SELECT plan_id,
467 txn_header_id,
468 occurrence
469 FROM QA_RESULTS
470 WHERE collection_id = c_collection_id
471 AND status <> 2
472 ORDER BY txn_header_id DESC, plan_id;
473
474 -- Bug 5508639. SHKALYAN 13-Sep-2006.
475 l_txn_header_id NUMBER := NULL;
476 l_event_processed BOOLEAN := FALSE;
477
478 -- Get all the Quality ERES Events based on an Event Key
479 -- Bug 5508639. SHKALYAN 13-Sep-2006.
480 -- Modified cursor to get the most recent E-Record
481 -- Also added a status check to retreive only valid E-Records
482
483 -- Bug 5729384. SHKALYAN 20-Feb-2007.
484 -- Removed the status check
485 -- AND psig_status = 'COMPLETE'
486 -- in the cursor so that ALL the records are
487 -- processed. This is because acknowledgment should be sent for all
488 -- E-records including those with REJECTED and PENDING status.
489 CURSOR get_erecords( c_event_key VARCHAR2 )
490 IS
491 SELECT event_name,
492 document_id
493 FROM EDR_PSIG_DOCUMENTS
494 WHERE event_key = c_event_key
495 AND event_name IN
496 ( 'oracle.apps.qa.ncm.create',
497 'oracle.apps.qa.ncm.update',
498 'oracle.apps.qa.ncm.master.approve',
499 'oracle.apps.qa.ncm.detail.approve',
500 'oracle.apps.qa.disp.create',
501 'oracle.apps.qa.disp.update',
505 'oracle.apps.qa.car.update',
502 'oracle.apps.qa.disp.header.approve',
503 'oracle.apps.qa.disp.detail.approve',
504 'oracle.apps.qa.car.create',
506 'oracle.apps.qa.car.approve',
507 'oracle.apps.qa.car.review.approve',
508 'oracle.apps.qa.car.impl.approve',
509 'oracle.apps.qa.result.create',
510 'oracle.apps.qa.result.update' )
511 ORDER BY creation_date DESC;
512
513 BEGIN
514
515 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
516 FND_LOG.string
517 (
518 FND_LOG.level_procedure,
519 g_pkg_name || '.' || l_api_name,
520 'Entering Procedure for collection: ' || p_collection_id
521 );
522 END IF;
523
524 -- Standard call to check for call compatibility.
525 IF NOT FND_API.Compatible_API_Call
526 (
527 l_api_version,
528 NVL( p_api_version, 1.0 ),
529 l_api_name,
530 g_pkg_name
531 ) THEN
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
533 END IF;
534
535 -- Initialize message list if p_init_msg_list is set to TRUE.
536 IF FND_API.to_Boolean( NVL( p_init_msg_list, FND_API.G_FALSE ) ) THEN
537 FND_MSG_PUB.initialize;
538 END IF;
539
540 -- Initialize API return status to success
541 x_return_status := FND_API.G_RET_STS_SUCCESS;
542
543 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
544 FND_LOG.string
545 (
546 FND_LOG.level_statement,
547 g_pkg_name || '.' || l_api_name,
548 'Before getting occurrences for the collection'
549 );
550 END IF;
551
552 FOR occ_cur IN get_occurrences( p_collection_id ) LOOP
553
554 -- Bug 5508639. SHKALYAN 13-Sep-2006.
555 -- Process only the most recent txn_header_id.
556 IF ( NVL(l_txn_header_id, occ_cur.txn_header_id) <> occ_cur.txn_header_id ) THEN
557 EXIT;
558 END IF;
559
560 l_txn_header_id := occ_cur.txn_header_id;
561
562 IF ( occ_cur.plan_id <> l_prev_plan_id ) THEN
563
564 l_event_key := occ_cur.plan_id || '-' || p_collection_id || '-';
565 l_prev_plan_id := occ_cur.plan_id;
566
567 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
568 FND_LOG.string
569 (
570 FND_LOG.level_statement,
571 g_pkg_name || '.' || l_api_name,
572 'Checking if event exists for the collection. Event Key: ' || l_event_key
573 );
574 END IF;
575
576 FOR erec_cur IN get_erecords( l_event_key ) LOOP
577
578 -- Ensure that only 1 event is processed
579 IF ( l_event_processed ) THEN
580 EXIT;
581 ELSE
582 l_event_processed := TRUE;
583 END IF;
584
585 x_qa_erecord_tbl(ctr).event_name := erec_cur.event_name;
586 x_qa_erecord_tbl(ctr).event_key := l_event_key;
587 x_qa_erecord_tbl(ctr).erec_id := erec_cur.document_id;
588
589 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
590 FND_LOG.string
591 (
592 FND_LOG.level_statement,
593 g_pkg_name || '.' || l_api_name,
594 'Found Event for the collection. Adding record: ' || ctr || ' E-Record: ' || x_qa_erecord_tbl(ctr).erec_id
595 );
596 END IF;
597
598 ctr := ctr + 1;
599 END LOOP;
600 l_event_processed := FALSE;
601 END IF;
602
603 l_event_key := occ_cur.plan_id || '-' || p_collection_id || '-' || occ_cur.occurrence;
604
605 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
606 FND_LOG.string
607 (
608 FND_LOG.level_statement,
609 g_pkg_name || '.' || l_api_name,
610 'Checking if event exists for the occurrence. Event Key: ' || l_event_key
611 );
612 END IF;
613
614 FOR erec_cur IN get_erecords( l_event_key ) LOOP
615
616 x_qa_erecord_tbl(ctr).event_name := erec_cur.event_name;
617 x_qa_erecord_tbl(ctr).event_key := l_event_key;
618 x_qa_erecord_tbl(ctr).erec_id := erec_cur.document_id;
619
620 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
621 FND_LOG.string
622 (
623 FND_LOG.level_statement,
624 g_pkg_name || '.' || l_api_name,
625 'Found Event for the collection. Adding record: ' || ctr || ' E-Record: ' || x_qa_erecord_tbl(ctr).erec_id
626 );
627 END IF;
628 ctr := ctr + 1;
629 END LOOP;
630
631 END LOOP;
632
633 -- Standard call to get message count and if count is 1, get message info.
637 p_data => x_msg_data
634 FND_MSG_PUB.Count_And_Get
635 (
636 p_count => x_msg_count,
638 );
639
640 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
641 FND_LOG.string
642 (
643 FND_LOG.level_procedure,
644 g_pkg_name || '.' || l_api_name,
645 'Exiting Procedure: Success'
646 );
647 END IF;
648
649 EXCEPTION
650
651 WHEN FND_API.G_EXC_ERROR THEN
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 FND_MSG_PUB.Count_And_Get
654 (
655 p_count => x_msg_count,
656 p_data => x_msg_data
657 );
658
659 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
660 FND_LOG.string
661 (
662 FND_LOG.level_procedure,
663 g_pkg_name || '.' || l_api_name,
664 'Exiting Procedure: Error'
665 );
666 END IF;
667
668 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
669 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
670 FND_MSG_PUB.Count_And_Get
671 (
672 p_count => x_msg_count,
673 p_data => x_msg_data
674 );
675
676 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
677 FND_LOG.string
678 (
679 FND_LOG.level_procedure,
680 g_pkg_name || '.' || l_api_name,
681 'Exiting Procedure: Error'
682 );
683 END IF;
684
685 WHEN OTHERS THEN
686 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
687 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
688 FND_MSG_PUB.Add_Exc_Msg
689 (
690 p_pkg_name => g_pkg_name,
691 p_procedure_name => l_api_name,
692 p_error_text => SUBSTR(SQLERRM,1,240)
693 );
694 END IF;
695
696 FND_MSG_PUB.Count_And_Get
697 (
698 p_count => x_msg_count,
699 p_data => x_msg_data
700 );
701
702 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
703 FND_LOG.string
704 (
705 FND_LOG.level_procedure,
706 g_pkg_name || '.' || l_api_name,
707 'Exiting Procedure: Error'
708 );
709 END IF;
710
711 END get_qa_results_erecords;
712
713 -- Bug 5508639. SHKALYAN 13-Sep-2006.
714 -- new API to delete the old invalid results For a collection_id
715 -- This is needed because the Per Collection E-Records show
716 -- up old invalid results for a given collection_id for SR Txn
717 PROCEDURE purge_invalid_results
718 (
719 p_collection_id IN NUMBER
720 ) IS
721 PRAGMA autonomous_transaction;
722
723 l_occurrences dbms_sql.number_table;
724 l_plans dbms_sql.number_table;
725
726 CURSOR C IS
727 SELECT occurrence, plan_id
728 FROM qa_results
729 WHERE collection_id = p_collection_id
730 AND status <> 2;
731
732 BEGIN
733
734 OPEN C;
735 FETCH C BULK COLLECT INTO l_occurrences, l_plans;
736 CLOSE C;
737
738 -- Delete all old invaild records for collection_id
739 FORALL i IN l_occurrences.FIRST..l_occurrences.LAST
740 DELETE QA_RESULTS
741 WHERE plan_id = l_plans(i)
742 AND occurrence = l_occurrences(i);
743
744 -- Delete Child relationships
745 FORALL i in l_occurrences.FIRST..l_occurrences.LAST
746 DELETE QA_PC_RESULTS_RELATIONSHIP
747 WHERE parent_occurrence = l_occurrences(i);
748
749 -- Delete Parent relationships
750 FORALL i in l_occurrences.FIRST..l_occurrences.LAST
751 DELETE QA_PC_RESULTS_RELATIONSHIP
752 WHERE child_occurrence = l_occurrences(i);
753
754 -- Autonomous commit
755 COMMIT;
756
757 END purge_invalid_results;
758
759 -- API to enable the Quality Results captured as part of the
760 -- Transaction session ( collection ). This API will call the existing
761 -- enable API to enable the results and fire background quality actions
762 -- in addition to invoking EDR API to stamp the acknowledgement status
763 -- of the Quality E-Records captured as part of the Txn as SUCCESS
764 PROCEDURE enable_results_erecords
765 (
766 p_api_version IN NUMBER ,
767 p_init_msg_list IN VARCHAR2 := NULL ,
768 p_commit IN VARCHAR2 := NULL ,
769 p_validation_level IN NUMBER := NULL ,
770 p_collection_id IN NUMBER ,
771 p_incident_id IN NUMBER := NULL,
772 x_return_status OUT NOCOPY VARCHAR2 ,
773 x_msg_count OUT NOCOPY NUMBER ,
774 x_msg_data OUT NOCOPY VARCHAR2
775 )
776 IS
777 l_api_name CONSTANT VARCHAR2(30) := 'enable_results_erecords';
778 l_api_version CONSTANT NUMBER := 1.0;
779 l_commit BOOLEAN;
780
781 l_erec_tbl qa_erecord_tbl_type;
782 l_return_status VARCHAR2(1);
783 l_msg_count NUMBER;
784 l_msg_data VARCHAR2(5000);
785
786 -- Bug 5508639. SHKALYAN 13-Sep-2006.
787 -- Get the most recent txn_header_id for a collection
788 -- processing will be done only for records with this txn_header_id
789 -- Also Process only Results which are in disabled status.
790 CURSOR get_txn_header_id( c_collection_id NUMBER )
791 IS
792 SELECT MAX( txn_header_id )
793 FROM QA_RESULTS
794 WHERE collection_id = c_collection_id
795 AND status <> 2;
796
797 -- Bug 5508639. SHKALYAN 13-Sep-2006.
798 l_txn_header_id NUMBER;
799
800 -- Bug 5656202. SHKALYAN 11-NOV-2006.
801 -- Arrays for holding the return values of qa results
802 -- enabled in the current session
803 Type num_tab_typ is table of number index by binary_integer;
804 plan_id_tab num_tab_typ;
805 collection_id_tab num_tab_typ;
806 occurrence_tab num_tab_typ;
807
808 BEGIN
809 l_commit := FND_API.To_Boolean( NVL(p_commit, FND_API.G_FALSE) );
810
811 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
812 FND_LOG.string
813 (
814 FND_LOG.level_procedure,
815 g_pkg_name || '.' || l_api_name,
816 'Entering Procedure for collection: ' || p_collection_id
817 );
818 END IF;
819
820 -- Standard Start of API savepoint
821 SAVEPOINT enable_results_erecords_GRP;
822
823 -- Standard call to check for call compatibility.
824 IF NOT FND_API.Compatible_API_Call
825 (
826 l_api_version,
827 NVL( p_api_version, 1.0 ),
828 l_api_name,
829 g_pkg_name
830 ) THEN
831 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
832 END IF;
833
834 -- Initialize message list if p_init_msg_list is set to TRUE.
835 IF FND_API.to_Boolean( NVL( p_init_msg_list, FND_API.G_FALSE ) ) THEN
836 FND_MSG_PUB.initialize;
837 END IF;
838
839 -- Initialize API return status to success
840 x_return_status := FND_API.G_RET_STS_SUCCESS;
841
842 IF ( FND_PROFILE.value('EDR_ERES_ENABLED') = 'Y' ) THEN
843
844 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
845 FND_LOG.string
846 (
847 FND_LOG.level_statement,
848 g_pkg_name || '.' || l_api_name,
849 'Before Getting QA Result ERecords'
850 );
851 END IF;
852
853 -- Get all the QA Results E-Records for the given collection
854 get_qa_results_erecords
855 (
856 p_api_version => 1.0,
857 p_init_msg_list => FND_API.G_FALSE,
858 p_commit => FND_API.G_FALSE,
859 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
860 p_collection_id => p_collection_id,
861 x_qa_erecord_tbl => l_erec_tbl,
862 x_return_status => l_return_status,
863 x_msg_count => l_msg_count,
864 x_msg_data => l_msg_data
865 );
866
867 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
868 FND_LOG.string
869 (
870 FND_LOG.level_statement,
871 g_pkg_name || '.' || l_api_name,
872 'Obtained ' || l_erec_tbl.COUNT || ' number of QA Result ERecords'
873 );
874 END IF;
875
876 -- Send Transaction Acknowledgement for E-Records obtained
877 IF ( l_erec_tbl.COUNT > 0 ) THEN
878 FOR i IN l_erec_tbl.FIRST..l_erec_tbl.LAST LOOP
879
880 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
881 FND_LOG.string
882 (
883 FND_LOG.level_statement,
884 g_pkg_name || '.' || l_api_name,
885 'Before Sending Txn acknowledgement for ERecord: ' || l_erec_tbl(i).erec_id || ' with event_name: ' || l_erec_tbl(i).event_name || ' and event_key: ' || l_erec_tbl(i).event_key
886 );
887 END IF;
888
889 EDR_TRANS_ACKN_PUB.send_ackn
890 (
891 p_api_version => 1.0,
892 x_return_status => l_return_status,
893 x_msg_count => l_msg_count,
894 x_msg_data => l_msg_data,
898 p_trans_status => g_erec_success,
895 p_event_name => l_erec_tbl(i).event_name,
896 p_event_key => l_erec_tbl(i).event_key,
897 p_erecord_id => l_erec_tbl(i).erec_id,
899 p_ackn_by => g_ackn_by,
900 p_ackn_note => '',
901 p_autonomous_commit => FND_API.G_FALSE
902 );
903
904 END LOOP;
905 END IF;
906
907 END IF;
908
909 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
910 FND_LOG.string
911 (
912 FND_LOG.level_statement,
913 g_pkg_name || '.' || l_api_name,
914 'Before Enabling QA Results and firing Actions'
915 );
916 END IF;
917
918 -- Bug 5508639. SHKALYAN 13-Sep-2006.
919 -- We were calling the enable API here. Modified to directly
920 -- include the logic for updating qa_results and calling do_actions
921 -- This is because we want to process based on txn_header_id
922 -- instead of collection_id and the old enable api cannot be used.
923
924 -- Get the most recent txn_header_id
925 OPEN get_txn_header_id( p_collection_id );
926 FETCH get_txn_header_id INTO l_txn_header_id;
927 CLOSE get_txn_header_id;
928
929 -- Enable the results for the most recent txn_header_id
930
931 -- Bug 5656202. SHKALYAN 11-NOV-2006.
932 -- For Background results posted during the transaction, the
933 -- txn_header_id will be NULL. Added OR condition to bring in
934 -- background results. Also, since subsequent action processing
935 -- cannot be based on just txn_header_id, collecting all the
936 -- occurrences updated in this session, so that, actions can be fired
937 -- for only these records
938 UPDATE qa_results
939 SET status = 2,
940 cs_incident_id = nvl(p_incident_id, cs_incident_id)
941 WHERE p_collection_id = collection_id
942 AND (txn_header_id = l_txn_header_id OR txn_header_id IS NULL)
943 AND status = 1
944 RETURNING plan_id, collection_id, occurrence
945 bulk collect into plan_id_tab, collection_id_tab, occurrence_tab;
946
947 IF SQL%ROWCOUNT > 0 THEN
948
949 -- Generate the sequence element value for all the records (including
950 -- child,grand child plans
951 -- call seq. api only if eres is not enabled.
952 IF FND_PROFILE.VALUE('EDR_ERES_ENABLED') <> 'Y' THEN
953
954 QA_SEQUENCE_API.Generate_Seq_for_Txn(
955 p_collection_id,
956 l_return_status);
957
958 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
959 -- in case of failure raise error.
960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
961 END IF;
962 END IF;
963
964 -- launch quality actions for the most recent txn_header_id
965 -- only actions that are performed in commit cycle are to be
966 -- launched here. action processing is based on txn_header_id
967 -- because txns such as service request is re-using the same
968 -- collection_id throughout the lifecycle of the SR. In this
969 -- case only un-processed records should be processed.
970 --
971
972 -- Bug 5656202. SHKALYAN 11-NOV-2006.
973 -- Looping through all the records updated in this session
974 -- and firing actions for them
975 FOR Cntr in 1..plan_id_tab.count LOOP
976
977 -- Bug 5656202. SHKALYAN 11-NOV-2006.
978 -- Calling the do_actions for the plan_id, collection_id,
979 -- Occurrence combination instead of txn_header_id
980
981 IF (QLTDACTB.do_actions( x_txn_header_id => collection_id_tab(cntr),
982 x_concurrent => 1,
983 x_po_txn_processor_mode => NULL,
984 x_group_id => NULL,
985 x_background => TRUE,
986 x_debug => FALSE,
987 x_action_type => 'DEFERRED',
988 x_passed_id_name => 'COLLECTION_ID',
989 p_occurrence => occurrence_tab(cntr),
990 p_plan_id => plan_id_tab(cntr),
991 x_argument => NULL
992 ) = FALSE ) THEN
993 raise FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF ;
995 END LOOP;
996 END IF;
997 -- Bug 5508639. SHKALYAN 13-Sep-2006.
998 -- End code changes.
999
1000 -- Commit (if requested)
1001 IF ( l_commit ) THEN
1002 COMMIT WORK;
1003 END IF;
1004
1005 -- Standard call to get message count and if count is 1, get message info.
1006 FND_MSG_PUB.count_and_get
1007 (
1008 p_count => x_msg_count,
1009 p_data => x_msg_data
1010 );
1011
1012 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1013 FND_LOG.string
1014 (
1015 FND_LOG.level_procedure,
1016 g_pkg_name || '.' || l_api_name,
1020
1017 'Exiting Procedure: Success'
1018 );
1019 END IF;
1021 EXCEPTION
1022
1023 WHEN FND_API.G_EXC_ERROR THEN
1024 ROLLBACK TO enable_results_erecords_GRP;
1025 x_return_status := FND_API.G_RET_STS_ERROR;
1026 FND_MSG_PUB.Count_And_Get
1027 (
1028 p_count => x_msg_count,
1029 p_data => x_msg_data
1030 );
1031
1032 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1033 FND_LOG.string
1034 (
1035 FND_LOG.level_procedure,
1036 g_pkg_name || '.' || l_api_name,
1037 'Exiting Procedure: Error'
1038 );
1039 END IF;
1040
1041 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1042 ROLLBACK TO enable_results_erecords_GRP;
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1044 FND_MSG_PUB.Count_And_Get
1045 (
1046 p_count => x_msg_count,
1047 p_data => x_msg_data
1048 );
1049
1050 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1051 FND_LOG.string
1052 (
1053 FND_LOG.level_procedure,
1054 g_pkg_name || '.' || l_api_name,
1055 'Exiting Procedure: Error'
1056 );
1057 END IF;
1058
1059 WHEN OTHERS THEN
1060 ROLLBACK TO enable_results_erecords_GRP;
1061 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1062 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
1063 FND_MSG_PUB.Add_Exc_Msg
1064 (
1065 p_pkg_name => g_pkg_name,
1066 p_procedure_name => l_api_name,
1067 p_error_text => SUBSTR(SQLERRM,1,240)
1068 );
1069 END IF;
1070
1071 FND_MSG_PUB.Count_And_Get
1072 (
1073 p_count => x_msg_count,
1074 p_data => x_msg_data
1075 );
1076
1077 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1078 FND_LOG.string
1079 (
1080 FND_LOG.level_procedure,
1081 g_pkg_name || '.' || l_api_name,
1082 'Exiting Procedure: Error'
1083 );
1084 END IF;
1085
1086 END enable_results_erecords;
1087
1088 -- API to purge the Quality Results captured as part of the
1089 -- Transaction session ( collection ). This API will call the existing
1090 -- purge API to delete the results in addition to invoking EDR API to
1091 -- stamp the acknowledgement status of the Quality E-Records captured
1092 -- as part of the Txn as SUCCESS
1093 PROCEDURE purge_results_erecords
1094 (
1095 p_api_version IN NUMBER ,
1096 p_init_msg_list IN VARCHAR2 := NULL ,
1097 p_commit IN VARCHAR2 := NULL ,
1098 p_validation_level IN NUMBER := NULL ,
1099 p_collection_id IN NUMBER ,
1100 x_return_status OUT NOCOPY VARCHAR2 ,
1101 x_msg_count OUT NOCOPY NUMBER ,
1102 x_msg_data OUT NOCOPY VARCHAR2
1103 )
1104 IS
1105 l_api_name CONSTANT VARCHAR2(30) := 'purge_results_erecords';
1106 l_api_version CONSTANT NUMBER := 1.0;
1107 l_commit BOOLEAN;
1108
1109 l_erec_tbl qa_erecord_tbl_type;
1110 l_return_status VARCHAR2(1);
1111 l_msg_count NUMBER;
1112 l_msg_data VARCHAR2(5000);
1113
1114 BEGIN
1115 l_commit := FND_API.To_Boolean( NVL(p_commit, FND_API.G_FALSE) );
1116
1117 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1118 FND_LOG.string
1119 (
1120 FND_LOG.level_procedure,
1121 g_pkg_name || '.' || l_api_name,
1122 'Entering Procedure for collection: ' || p_collection_id
1123 );
1124 END IF;
1125
1126 -- Standard Start of API savepoint
1127 SAVEPOINT purge_results_erecords_GRP;
1128
1129 -- Standard call to check for call compatibility.
1130 IF NOT FND_API.Compatible_API_Call
1131 (
1132 l_api_version,
1133 NVL( p_api_version, 1.0 ),
1134 l_api_name,
1135 g_pkg_name
1136 ) THEN
1137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1138 END IF;
1139
1140 -- Initialize message list if p_init_msg_list is set to TRUE.
1141 IF FND_API.to_Boolean( NVL( p_init_msg_list, FND_API.G_FALSE ) ) THEN
1142 FND_MSG_PUB.initialize;
1143 END IF;
1144
1145 -- Initialize API return status to success
1146 x_return_status := FND_API.G_RET_STS_SUCCESS;
1147
1148 IF ( FND_PROFILE.value('EDR_ERES_ENABLED') = 'Y' ) THEN
1149
1150 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1151 FND_LOG.string
1152 (
1153 FND_LOG.level_statement,
1154 g_pkg_name || '.' || l_api_name,
1155 'Before Getting QA Result ERecords'
1156 );
1157 END IF;
1158
1159 -- Get all the QA Results E-Records for the given collection
1160 get_qa_results_erecords
1161 (
1162 p_api_version => 1.0,
1163 p_init_msg_list => FND_API.G_FALSE,
1167 x_qa_erecord_tbl => l_erec_tbl,
1164 p_commit => FND_API.G_FALSE,
1165 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1166 p_collection_id => p_collection_id,
1168 x_return_status => l_return_status,
1169 x_msg_count => l_msg_count,
1170 x_msg_data => l_msg_data
1171 );
1172
1173 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1174 FND_LOG.string
1175 (
1176 FND_LOG.level_statement,
1177 g_pkg_name || '.' || l_api_name,
1178 'Obtained ' || l_erec_tbl.COUNT || ' number of QA Result ERecords'
1179 );
1180 END IF;
1181
1182 -- Send Transaction Acknowledgement for E-Records obtained
1183 IF ( l_erec_tbl.COUNT > 0 ) THEN
1184 FOR i IN l_erec_tbl.FIRST..l_erec_tbl.LAST LOOP
1185
1186 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1187 FND_LOG.string
1188 (
1189 FND_LOG.level_statement,
1190 g_pkg_name || '.' || l_api_name,
1191 'Before Sending Txn acknowledgement for ERecord: ' || l_erec_tbl(i).erec_id || ' with event_name: ' || l_erec_tbl(i).event_name || ' and event_key: ' || l_erec_tbl(i).event_key
1192 );
1193 END IF;
1194
1195 EDR_TRANS_ACKN_PUB.send_ackn
1196 (
1197 p_api_version => 1.0,
1198 x_return_status => l_return_status,
1199 x_msg_count => l_msg_count,
1200 x_msg_data => l_msg_data,
1201 p_event_name => l_erec_tbl(i).event_name,
1202 p_event_key => l_erec_tbl(i).event_key,
1203 p_erecord_id => l_erec_tbl(i).erec_id,
1204 p_trans_status => g_erec_error,
1205 p_ackn_by => g_ackn_by,
1206 p_ackn_note => '',
1207 p_autonomous_commit => FND_API.G_FALSE
1208 );
1209
1210 END LOOP;
1211 END IF;
1212
1213 END IF;
1214
1215 IF ( FND_LOG.level_statement >= FND_LOG.g_current_runtime_level ) THEN
1216 FND_LOG.string
1217 (
1218 FND_LOG.level_statement,
1219 g_pkg_name || '.' || l_api_name,
1220 'Before Purging QA Results'
1221 );
1222 END IF;
1223
1224 -- Purge the Quality Results for the Collection
1225 purge
1226 (
1227 p_api_version => 1.0,
1228 p_init_msg_list => FND_API.G_FALSE,
1229 p_commit => FND_API.G_FALSE,
1230 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1231 p_collection_id => p_collection_id,
1232 p_return_status => l_return_status,
1233 p_msg_count => l_msg_count,
1234 p_msg_data => l_msg_data
1235 );
1236
1237 -- Commit (if requested)
1238 IF ( l_commit ) THEN
1239 COMMIT WORK;
1240 END IF;
1241
1242 -- Standard call to get message count and if count is 1, get message info.
1243 FND_MSG_PUB.Count_And_Get
1244 (
1245 p_count => x_msg_count,
1246 p_data => x_msg_data
1247 );
1248
1249 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1250 FND_LOG.string
1251 (
1252 FND_LOG.level_procedure,
1253 g_pkg_name || '.' || l_api_name,
1254 'Exiting Procedure: Success'
1255 );
1256 END IF;
1257
1258 EXCEPTION
1259
1260 WHEN FND_API.G_EXC_ERROR THEN
1261 ROLLBACK TO purge_results_erecords_GRP;
1262 x_return_status := FND_API.G_RET_STS_ERROR;
1263 FND_MSG_PUB.Count_And_Get
1264 (
1265 p_count => x_msg_count,
1266 p_data => x_msg_data
1267 );
1268
1269 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1270 FND_LOG.string
1271 (
1272 FND_LOG.level_procedure,
1273 g_pkg_name || '.' || l_api_name,
1274 'Exiting Procedure: Error'
1275 );
1276 END IF;
1277
1278 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1279 ROLLBACK TO purge_results_erecords_GRP;
1280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1281 FND_MSG_PUB.Count_And_Get
1282 (
1283 p_count => x_msg_count,
1284 p_data => x_msg_data
1285 );
1286
1287 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1288 FND_LOG.string
1289 (
1290 FND_LOG.level_procedure,
1291 g_pkg_name || '.' || l_api_name,
1292 'Exiting Procedure: Error'
1293 );
1294 END IF;
1295
1296 WHEN OTHERS THEN
1297 ROLLBACK TO purge_results_erecords_GRP;
1298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1299 IF ( FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) ) THEN
1300 FND_MSG_PUB.Add_Exc_Msg
1301 (
1302 p_pkg_name => g_pkg_name,
1303 p_procedure_name => l_api_name,
1304 p_error_text => SUBSTR(SQLERRM,1,240)
1305 );
1306 END IF;
1307
1308 FND_MSG_PUB.Count_And_Get
1309 (
1310 p_count => x_msg_count,
1311 p_data => x_msg_data
1312 );
1313
1314 IF ( FND_LOG.level_procedure >= FND_LOG.g_current_runtime_level ) THEN
1315 FND_LOG.string
1316 (
1317 FND_LOG.level_procedure,
1318 g_pkg_name || '.' || l_api_name,
1319 'Exiting Procedure: Error'
1320 );
1321 END IF;
1322
1323 END purge_results_erecords;
1324 -- R12 ERES Support in Service Family. Bug 4345768 End
1325
1326 END qa_result_grp;
[Home] [Help]