1 PACKAGE BODY cs_sr_delete_util AS
2 /* $Header: csvsrdlb.pls 120.10.12020000.2 2012/07/05 05:39:15 gasankar ship $ */
3
4 --------------------------------------------------------------------------------
5 -- Package level definitions
6 --------------------------------------------------------------------------------
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CS_SR_DELETE_UTIL';
8
9 TYPE t_number_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
10 TYPE t_date_tbl IS TABLE OF DATE INDEX BY BINARY_INTEGER;
11 TYPE t_string_tbl IS TABLE OF VARCHAR2(3) INDEX BY BINARY_INTEGER;
12 TYPE t_long_string_tbl IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
13
14 PROCEDURE Delete_Contacts
15 (
16 p_api_version_number IN NUMBER := 1.0
17 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18 , p_commit IN VARCHAR2 := FND_API.G_FALSE
19 , p_object_type IN VARCHAR2
20 , p_processing_set_id IN NUMBER
21 , x_return_status OUT NOCOPY VARCHAR2
22 , x_msg_count OUT NOCOPY NUMBER
23 , x_msg_data OUT NOCOPY VARCHAR2
24 );
25
26 PROCEDURE Delete_Audit_Records
27 (
28 p_api_version_number IN NUMBER := 1.0
29 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
30 , p_commit IN VARCHAR2 := FND_API.G_FALSE
31 , p_object_type IN VARCHAR2
32 , p_processing_set_id IN NUMBER
33 , x_return_status OUT NOCOPY VARCHAR2
34 , x_msg_count OUT NOCOPY NUMBER
35 , x_msg_data OUT NOCOPY VARCHAR2
36 );
37
38 PROCEDURE Delete_Sr_Attributes
39 (
40 p_api_version_number IN NUMBER := 1.0
41 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
42 , p_commit IN VARCHAR2 := FND_API.G_FALSE
43 , p_object_type IN VARCHAR2
44 , p_processing_set_id IN NUMBER
45 , x_return_status OUT NOCOPY VARCHAR2
46 , x_msg_count OUT NOCOPY NUMBER
47 , x_msg_data OUT NOCOPY VARCHAR2
48 );
49
50 PROCEDURE Create_Purgeaudit_Records
51 (
52 p_api_version_number IN NUMBER := 1.0
53 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
54 , p_commit IN VARCHAR2 := FND_API.G_FALSE
55 , p_purge_set_id IN NUMBER
56 , p_incident_id_tbl IN t_number_tbl
57 , p_incident_number_tbl IN t_long_string_tbl
58 , p_incident_type_id_tbl IN t_number_tbl
59 , p_customer_id_tbl IN t_number_tbl
60 , p_inv_organization_id_tbl IN t_number_tbl
61 , p_inventory_item_id_tbl IN t_number_tbl
62 , p_customer_product_id_tbl IN t_number_tbl
63 , p_inc_creation_date_tbl IN t_date_tbl
64 , p_inc_last_update_date_tbl IN t_date_tbl
65 , p_incident_id_tl_tbl IN t_number_tbl
66 , p_language_tbl IN t_string_tbl
67 , p_source_lang_tbl IN t_string_tbl
68 , p_summary_tbl IN t_long_string_tbl
69 , x_return_status OUT NOCOPY VARCHAR2
70 , x_msg_count OUT NOCOPY NUMBER
71 , x_msg_data OUT NOCOPY VARCHAR2
72 );
73
74 PROCEDURE Check_User_Termination;
75
76 --------------------------------------------------------------------------------
77
78
79
80 --------------------------------------------------------------------------------
81 -- Procedure Name : DELETE_SR_VALIDATIONS
82 --
83 -- Parameters (other than standard ones)
84 -- IN
85 -- p_object_type : Type of object for which this
86 -- procedure is being called. (Here it
87 -- will be 'SR')
88 -- p_processing_set_id : Id that helps the API in identifying
89 -- the set of SRs for which the child
90 -- objects have to be deleted.
91 -- p_purge_source_with_open_task : Indicates whether the SRs containing
92 -- OPEN non field service tasks should
93 -- be purged or not
94 -- Description
95 -- This API physically removes the SRs and all its child objects after
96 -- performing validations wherever required. This is a wrapper which
97 -- delegates the work to another helper API named
98 -- cs_sr_delete_util.delete_servicerequest
99 --
100 -- HISTORY
101 --
102 ----------------+------------+--------------------------------------------------
103 -- DATE | UPDATED BY | Change Description
104 ----------------+------------+--------------------------------------------------
105 -- 2-Aug_2005 | varnaray | Created
106 -- | |
107 ----------------+------------+--------------------------------------------------
108 /*#
109 * This API physically removes the SRs and all its child objects after
110 * performing validations wherever required. This is a wrapper which delegates
111 * the work to another helper API named cs_sr_delete_util.delete_servicerequest
112 * @param p_object_type Type of object for which this procedure is being called.
113 * (Here it will be 'SR')
114 * @param p_processing_set_id Id that helps the API in identifying the set of
115 * SRs for which the child objects have to be deleted.
116 * @param p_purge_source_with_open_task Indicates whether the SRs containing
117 * OPEN non field service tasks should be purged or not
118 * @rep:scope internal
119 * @rep:product CS
120 * @rep:displayname Service Request Delete Validations
121 */
122 PROCEDURE Delete_Sr_Validations
123 (
124 p_api_version_number IN NUMBER := 1.0
125 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
126 , p_commit IN VARCHAR2 := FND_API.G_FALSE
127 , p_object_type IN VARCHAR2
128 , p_processing_set_id IN NUMBER
129 , p_purge_source_with_open_task IN VARCHAR2
130 , x_return_status OUT NOCOPY VARCHAR2
131 , x_msg_count OUT NOCOPY NUMBER
132 , x_msg_data OUT NOCOPY VARCHAR2
133 )
134 IS
135 --------------------------------------------------------------------------------
136
137 L_API_VERSION CONSTANT NUMBER := 1.0;
138 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_SR_VALIDATIONS';
139 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
140 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
141
142 x_msg_index_out NUMBER;
143
144 BEGIN
145 x_return_status := FND_API.G_RET_STS_SUCCESS;
146
147 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
148 THEN
149 fnd_log.string
150 (
151 fnd_log.level_procedure
152 , L_LOG_MODULE || 'start'
153 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
154 );
155 fnd_log.string
156 (
157 fnd_log.level_procedure
158 , L_LOG_MODULE || 'param 1'
159 , 'p_api_version_number:' || p_api_version_number
160 );
161 fnd_log.string
162 (
163 fnd_log.level_procedure
164 , L_LOG_MODULE || 'param 2'
165 , 'p_init_msg_list:' || p_init_msg_list
166 );
167 fnd_log.string
168 (
169 fnd_log.level_procedure
170 , L_LOG_MODULE || 'param 3'
171 , 'p_commit:' || p_commit
172 );
173 fnd_log.string
174 (
175 fnd_log.level_procedure
176 , L_LOG_MODULE || 'param 4'
177 , 'p_purge_source_with_open_task:' || p_purge_source_with_open_task
178 );
179 fnd_log.string
180 (
181 fnd_log.level_procedure
182 , L_LOG_MODULE || 'param 5'
183 , 'p_object_type:' || p_object_type
184 );
185 fnd_log.string
186 (
187 fnd_log.level_procedure
188 , L_LOG_MODULE || 'param 6'
189 , 'p_processing_set_id:' || p_processing_set_id
190 );
191 END IF ;
192
193 IF NOT FND_API.Compatible_API_Call
194 (
195 L_API_VERSION
196 , p_api_version_number
197 , L_API_NAME
198 , G_PKG_NAME
199 )
200 THEN
201 FND_MSG_PUB.Count_And_Get
202 (
203 p_count => x_msg_count
204 , p_data => x_msg_data
205 );
206 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
207 END IF ;
208
209 IF FND_API.to_Boolean(p_init_msg_list)
210 THEN
211 FND_MSG_PUB.initialize;
212 END IF ;
213
214 ------------------------------------------------------------------------------
215 -- Parameter Validations:
216 ------------------------------------------------------------------------------
217
218 IF NVL(p_object_type, 'X') <> 'SR'
219 THEN
220 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
221 THEN
222 fnd_log.string
223 (
224 fnd_log.level_unexpected
225 , L_LOG_MODULE || 'object_type_invalid'
226 , 'p_object_type has to be SR.'
227 );
228 END IF ;
229
230 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
231 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
232 FND_MESSAGE.Set_Token('PARAM', 'p_object_type');
233 FND_MESSAGE.Set_Token('CURRVAL', p_object_type);
234 FND_MSG_PUB.ADD;
235
236 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
237 END IF;
238
239 ---
240
241 IF p_processing_set_id IS NULL
242 THEN
243 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
244 THEN
245 fnd_log.string
246 (
247 fnd_log.level_unexpected
248 , L_LOG_MODULE || 'proc_set_id_invalid'
249 , 'p_processing_set_id should not be NULL.'
250 );
251 END IF ;
252
253 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
254 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
255 FND_MESSAGE.Set_Token('PARAM', 'p_processing_set_id');
256 FND_MESSAGE.Set_Token('CURRVAL', NVL(to_char(p_processing_set_id),'NULL'));
257 FND_MSG_PUB.ADD;
258
259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
260 END IF;
261
262 ---
263
264 IF p_purge_source_with_open_task IS NULL
265 OR NVL(p_purge_source_with_open_task, 'X') NOT IN ('Y', 'N')
266 THEN
267 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
268 THEN
269 fnd_log.string
270 (
271 fnd_log.level_unexpected
272 , L_LOG_MODULE || 'p_purge_source_with_open_task_invalid'
273 , 'p_purge_source_with_open_task value is invalid.'
274 );
275 END IF ;
276
277 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
278 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
279 FND_MESSAGE.Set_Token('PARAM', 'p_purge_source_with_open_task');
280 FND_MESSAGE.Set_Token
281 (
282 'CURRVAL'
283 , NVL
284 (
285 p_purge_source_with_open_task
286 , 'NULL'
287 )
288 );
289 FND_MSG_PUB.ADD;
290
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END IF;
293
294 ------------------------------------------------------------------------------
295 -- Actual Logic starts below:
296 ------------------------------------------------------------------------------
297
298 Check_User_Termination;
299
300 ---
301
302 IF x_return_status = FND_API.G_RET_STS_SUCCESS
303 THEN
304 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
305 THEN
306 fnd_log.string
307 (
308 fnd_log.level_statement
309 , L_LOG_MODULE || 'field_service_valid_start'
310 , 'calling the Field Service validation API '
311 || 'csf_maintain_grp.validate_fieldserviceobjects'
312 );
313 END IF ;
314
315 -- The following procedure call checks if the field service tasks linked to
316 -- an SR are deletable. The result of this validation is reflected in the
317 -- purge_status column of the global temp table.
318
319 CSF_MAINTAIN_GRP.Validate_FieldServiceObjects
320 (
321 p_api_version => 1.0
322 , p_init_msg_list => FND_API.G_FALSE
323 , p_commit => FND_API.G_FALSE
324 , p_processing_set_id => p_processing_set_id
325 , p_object_type => p_object_type
326 , x_return_status => x_return_status
327 , x_msg_count => x_msg_count
328 , x_msg_data => x_msg_data
329 );
330
331 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
332 THEN
333 fnd_log.string
334 (
335 fnd_log.level_statement
336 , L_LOG_MODULE || 'field_service_valid_end'
337 , 'returned from Field Service validation API with status '
338 || x_return_status
339 );
340 END IF;
341 END IF;
342
343 ---
344
345 Check_User_Termination;
346
347 ---
348
349 IF x_return_status = FND_API.G_RET_STS_SUCCESS
350 THEN
351 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
352 THEN
353 fnd_log.string
354 (
355 fnd_log.level_statement
356 , L_LOG_MODULE || 'charges_valid_start'
357 , 'calling the Charges validation API '
358 || 'cs_charge_details_pvt.purge_chg_validations'
359 );
360 END IF ;
361
362 -- The following procedure call checks if the charge lines linked to
363 -- an SR are deletable. The result of this validation is reflected in the
364 -- purge_status column of the global temp table.
365
366 CS_CHARGE_DETAILS_PVT.Purge_Chg_Validations
367 (
368 p_api_version_number => 1.0
369 , p_init_msg_list => FND_API.G_FALSE
370 , p_commit => FND_API.G_FALSE
371 , p_processing_set_id => p_processing_set_id
372 , p_object_type => p_object_type
373 , x_return_status => x_return_status
374 , x_msg_count => x_msg_count
375 , x_msg_data => x_msg_data
376 );
377
378 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
379 THEN
380 fnd_log.string
381 (
382 fnd_log.level_statement
383 , L_LOG_MODULE || 'charges_valid_end'
384 , 'returned from Charges validation API with status ' || x_return_status
385 );
386 END IF ;
387 END IF;
388
389 ---
390
391 Check_User_Termination;
392
393 ---
394
395 IF x_return_status = FND_API.G_RET_STS_SUCCESS
396 THEN
397 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
398 THEN
399 fnd_log.string
400 (
401 fnd_log.level_statement
402 , L_LOG_MODULE || 'task_valid_start'
403 , 'calling the Tasks validation API cac_task_purge_pub.validate_tasks'
404 );
405 END IF ;
406
407 -- The following procedure call checks if the non-field service Tasks linked
408 -- to an SR are deletable. The result of this validation is reflected in the
409 -- purge_status column of the global temp table.
410
411 CAC_TASK_PURGE_PUB.Validate_Tasks
412 (
413 p_api_version => 1.0
414 , p_init_msg_list => FND_API.G_FALSE
415 , p_commit => FND_API.G_FALSE
416 , p_processing_set_id => p_processing_set_id
417 , p_object_type => p_object_type
418 , p_purge_source_with_open_task => p_purge_source_with_open_task
419 , x_return_status => x_return_status
420 , x_msg_count => x_msg_count
421 , x_msg_data => x_msg_data
422 );
423
424 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
425 THEN
426 fnd_log.string
427 (
428 fnd_log.level_statement
429 , L_LOG_MODULE || 'task_valid_end'
430 , 'returned from Tasks validation API with status ' || x_return_status
431 );
432 END IF ;
433 END IF;
434
435 ---
436
437 Check_User_Termination;
438
439 ---
440
441 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
442 THEN
443 fnd_log.string
444 (
445 fnd_log.level_procedure
446 , L_LOG_MODULE || 'end'
447 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
448 || x_return_status
449 );
450 END IF ;
451
452 EXCEPTION
453
454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
456
457 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
458 THEN
459 fnd_log.string
460 (
461 fnd_log.level_unexpected
462 , L_LOG_MODULE || 'unexpected_error'
463 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
464 );
465
466 x_msg_count := FND_MSG_PUB.Count_Msg;
467
468 IF x_msg_count > 0
469 THEN
470 FOR
471 i IN 1..x_msg_count
472 LOOP
473 FND_MSG_PUB.Get
474 (
475 p_msg_index => i
476 , p_encoded => 'F'
477 , p_data => x_msg_data
478 , p_msg_index_out => x_msg_index_out
479 );
480 fnd_log.string
481 (
482 fnd_log.level_unexpected
483 , L_LOG_MODULE || 'unexpected_error'
484 , 'Error encountered is : ' || x_msg_data || ' [Index:'
485 || x_msg_index_out || ']'
486 );
487 END LOOP;
488 END IF ;
489 END IF ;
490
491 WHEN OTHERS THEN
492 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
493 FND_MESSAGE.Set_Name('CS', 'CS_SR_DEL_VAL_FAIL');
494 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
495 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
496 FND_MSG_PUB.ADD;
497
498 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
499 THEN
500 fnd_log.string
501 (
502 fnd_log.level_unexpected
503 , L_LOG_MODULE || 'when_others'
504 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
505 );
506 fnd_log.string
507 (
508 fnd_log.level_unexpected
509 , L_LOG_MODULE || 'when_others'
510 , SQLERRM
511 );
512 END IF ;
513 END Delete_Sr_Validations;
514
515 --------------------------------------------------------------------------------
516 -- Procedure Name : DELETE_SERVICEREQUEST
517 --
518 -- Parameters (other than standard ones)
519 -- IN
520 -- p_object_type : Type of object for which this procedure
521 -- is being called. (Here it will be 'SR')
522 -- p_processing_set_id : Id that helps the API in identifying the
523 -- set of SRs for which the child objects
524 -- have to be deleted.
525 -- p_purge_set_id : Id that helps identify a set of SRs that
526 -- were purged in a single batch. This can
527 -- be passed as NULL if the SR Delete API
528 -- is called separately. In that case, the
529 -- purge_set_id will be generated in this
530 -- procedure.
531 -- p_audit_required : Indicates if audit information has to be
532 -- generated after purging the service
533 -- requests
534 -- Description
535 -- This API physically removes the SRs and all its child objects after
536 -- performing validations wherever required. This procedure calls the
537 -- delete APIs for deleting child objects and directly deletes the rows
538 -- in the tables cs_incidents_all_b and tl.
539 -- This procedure also updates the staging table with the errors generated
540 -- while performing validations on SRs with all child objects so that
541 -- a log of these errors can be generated at the end of the purge process.
542 --
543 -- HISTORY
544 --
545 ----------------+------------+--------------------------------------------------
546 -- DATE | UPDATED BY | Change Description
547 ----------------+------------+--------------------------------------------------
548 -- 2-Aug_2005 | varnaray | Created
549 -- | |
550 ----------------+------------+--------------------------------------------------
551 /*#
552 * This API physically removes the SRs and all its child objects after
553 * performing validations wherever required. This procedure calls the delete
554 * APIs for deleting child objects and directly deletes the rows in the tables
555 * cs_incidents_all_b and tl. This procedure also updates the staging table
556 * with the errors generated while performing validations on SRs with all child
557 * objects so that a log of these errors can be generated at the end of the
558 * purge process.
559 * @param p_object_type Type of object for which this procedure is being
560 * called. (Here it will be 'SR')
561 * @param p_processing_set_id Id that helps the API in identifying the set of
562 * SRs for which the child objects have to be deleted.
563 * @param p_purge_set_id Id that helps identify a set of SRs that were purged
564 * in a single batch. This can be passed as NULL if the SR Delete API is called
565 * separately. In that case, the purge_set_id will be generated in this
566 * procedure.
567 * @param p_audit_required Indicates if audit information has to be generated
568 * after purging the service requests
569 * @rep:scope internal
570 * @rep:product CS
571 * @rep:displayname Delete Service Request Helper Procedure
572 */
573 PROCEDURE Delete_ServiceRequest
574 (
575 p_api_version_number IN NUMBER := 1.0
576 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
577 , p_commit IN VARCHAR2 := FND_API.G_FALSE
578 , p_purge_set_id IN NUMBER
579 , p_processing_set_id IN NUMBER
580 , p_object_type IN VARCHAR2
581 , p_audit_required IN VARCHAR2
582 , x_return_status OUT NOCOPY VARCHAR2
583 , x_msg_count OUT NOCOPY NUMBER
584 , x_msg_data OUT NOCOPY VARCHAR2
585 )
586 IS
587 --------------------------------------------------------------------------------
588 L_API_VERSION CONSTANT NUMBER := 1.0;
589 L_API_NAME CONSTANT VARCHAR2 (30) := 'DELETE_SERVICEREQUEST';
590 L_API_NAME_FULL CONSTANT VARCHAR2 (61) := G_PKG_NAME || '.' || L_API_NAME;
591 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
592
593 x_msg_index_out NUMBER;
594
595 l_purge_set_id NUMBER := p_purge_set_id;
596 l_row_count NUMBER;
597
598 l_incident_id_tbl t_number_tbl;
599 l_incident_number_tbl t_long_string_tbl;
600 l_incident_type_id_tbl t_number_tbl;
601 l_customer_id_tbl t_number_tbl;
602 l_inv_organization_id_tbl t_number_tbl;
603 l_inventory_item_id_tbl t_number_tbl;
604 l_customer_product_id_tbl t_number_tbl;
605 l_inc_creation_date_tbl t_date_tbl;
606 l_inc_last_update_date_tbl t_date_tbl;
607 l_incident_id_status_tbl t_number_tbl;
608 l_purge_error_message_tbl t_long_string_tbl;
609 l_incident_id_tl_tbl t_number_tbl;
610 l_language_tbl t_string_tbl;
611 l_source_lang_tbl t_string_tbl;
612 l_summary_tbl t_long_string_tbl;
613
614 BEGIN
615 x_return_status := FND_API.G_RET_STS_SUCCESS;
616
617 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
618 THEN
619 fnd_log.string
620 (
621 fnd_log.level_procedure
622 , L_LOG_MODULE || 'start'
623 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
624 );
625 fnd_log.string
626 (
627 fnd_log.level_procedure
628 , L_LOG_MODULE || 'param 1'
629 , 'p_api_version_number:' || p_api_version_number
630 );
631 fnd_log.string
632 (
633 fnd_log.level_procedure
634 , L_LOG_MODULE || 'param 2'
635 , 'p_init_msg_list:' || p_init_msg_list
636 );
637 fnd_log.string
638 (
639 fnd_log.level_procedure
640 , L_LOG_MODULE || 'param 3'
641 , 'p_commit:' || p_commit
642 );
643 fnd_log.string
644 (
645 fnd_log.level_procedure
646 , L_LOG_MODULE || 'param 4'
647 , 'p_object_type:' || p_object_type
648 );
649 fnd_log.string
650 (
651 fnd_log.level_procedure
652 , L_LOG_MODULE || 'param 5'
653 , 'p_processing_set_id:' || p_processing_set_id
654 );
655 fnd_log.string
656 (
657 fnd_log.level_procedure
658 , L_LOG_MODULE || 'param 6'
659 , 'p_purge_set_id:' || p_purge_set_id
660 );
661 fnd_log.string
662 (
663 fnd_log.level_procedure
664 , L_LOG_MODULE || 'param 7'
665 , 'p_audit_required:' || p_audit_required
666 );
667 END IF ;
668
669 IF NOT FND_API.Compatible_API_Call
670 (
671 L_API_VERSION
672 , p_api_version_number
673 , L_API_NAME
674 , G_PKG_NAME
675 )
676 THEN
677 FND_MSG_PUB.Count_And_Get
678 (
679 p_count => x_msg_count
680 , p_data => x_msg_data
681 );
682 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
683 END IF ;
684
685 IF
686 FND_API.to_Boolean(p_init_msg_list)
687 THEN
688 FND_MSG_PUB.initialize;
689 END IF ;
690
691 ------------------------------------------------------------------------------
692 -- Parameter Validations:
693 ------------------------------------------------------------------------------
694
695 IF p_audit_required IS NULL
696 OR NVL(p_audit_required, 'X') NOT IN ('Y', 'N')
697 THEN
698 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
699 THEN
700 fnd_log.string
701 (
702 fnd_log.level_unexpected
703 , L_LOG_MODULE || 'audit_required_invalid'
704 , 'p_audit_required has to be Y/N.'
705 );
706 END IF ;
707
708 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
709 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
710 FND_MESSAGE.Set_Token('PARAM', 'p_audit_required');
711 FND_MESSAGE.Set_Token('CURRVAL', p_audit_required);
712 FND_MSG_PUB.ADD;
713
714 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
715 END IF;
716
717 ---
718
719 IF NVL(p_object_type, 'X') <> 'SR'
720 THEN
721 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
722 THEN
723 fnd_log.string
724 (
725 fnd_log.level_unexpected
726 , L_LOG_MODULE || 'object_type_invalid'
727 , 'p_object_type has to be SR.'
728 );
729 END IF ;
730
731 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
732 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
733 FND_MESSAGE.Set_Token('PARAM', 'p_object_type');
734 FND_MESSAGE.Set_Token('CURRVAL', p_object_type);
735 FND_MSG_PUB.ADD;
736
737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
738 END IF;
739
740 ---
741
742 IF p_processing_set_id IS NULL
743 THEN
744 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
745 THEN
746 fnd_log.string
747 (
748 fnd_log.level_unexpected
749 , L_LOG_MODULE || 'proc_set_id_invalid'
750 , 'Processing Set Id should not be NULL.'
751 );
752 END IF ;
753
754 FND_MESSAGE.Set_Name('CS', 'CS_SR_PARAM_VALUE_ERROR');
755 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
756 FND_MESSAGE.Set_Token('PARAM', 'p_processing_set_id');
757 FND_MESSAGE.Set_Token('CURRVAL', NVL(to_char(p_processing_set_id),'NULL'));
758 FND_MSG_PUB.ADD;
759
760 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
761 END IF;
762
763 ------------------------------------------------------------------------------
764 -- Actual Logic starts below:
765 ------------------------------------------------------------------------------
766
767 -- If the purge_set_id is null it means that this code is
768 -- not called from the purge concurrent program but from
769 -- some other consumer of the delete API. Hence generating
770 -- a new purge_set_id before deleting the SR and child data.
771
772 IF l_purge_set_id IS NULL
773 THEN
774 SELECT
775 cs_incidents_purge_set_s.NEXTVAL
776 INTO
777 l_purge_set_id
778 FROM
779 dual
780 WHERE
781 ROWNUM = 1;
782
783 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
784 THEN
785 fnd_log.string
786 (
787 fnd_log.level_statement
788 , L_LOG_MODULE || 'gen_purge_set_id'
789 , 'Generated a new purge_set_id ' || l_purge_set_id
790 );
791 END IF ;
792 END IF;
793
794 ---
795
796 Check_User_Termination;
797
798 ---
799
800 IF x_return_status = FND_API.G_RET_STS_SUCCESS
801 THEN
802 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
803 THEN
804 fnd_log.string
805 (
806 fnd_log.level_statement
807 , L_LOG_MODULE || 'field_service_purge_start'
808 , 'calling the Field Service purge API '
809 || 'csf_maintain_grp.purge_fieldserviceobjects'
810 );
811 END IF ;
812
813 -- This procedure deletes all the field service tasks that are related to
814 -- SRs that are available in the global temp table with purge status NULL.
815
816 CSF_MAINTAIN_GRP.Purge_FieldServiceObjects
817 (
818 p_api_version => 1.0
819 , p_init_msg_list => FND_API.G_FALSE
820 , p_commit => FND_API.G_FALSE
821 , p_processing_set_id => p_processing_set_id
822 , p_object_type => p_object_type
823 , x_return_status => x_return_status
824 , x_msg_count => x_msg_count
825 , x_msg_data => x_msg_data
826 );
827
828 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
829 THEN
830 fnd_log.string
831 (
832 fnd_log.level_statement
833 , L_LOG_MODULE || 'field_service_purge_end'
834 , 'returned from Field Service purge API with status '
835 || x_return_status
836 );
837 END IF ;
838 END IF;
839
840 ---
841
842 Check_User_Termination;
843
844 ---
845
846 IF x_return_status = FND_API.G_RET_STS_SUCCESS
847 THEN
848 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
849 THEN
850 fnd_log.string
851 (
852 fnd_log.level_statement
853 , L_LOG_MODULE || 'charges_purge_start'
854 , 'calling the Charges purge API '
855 || 'cs_charge_details_pvt.purge_charges'
856 );
857 END IF ;
858
859 -- This procedure deletes all the charge lines that are related to
860 -- SRs that are available in the global temp table with purge status NULL.
861
862 CS_CHARGE_DETAILS_PVT.Purge_Charges
863 (
864 p_api_version_number => 1.0
865 , p_init_msg_list => FND_API.G_FALSE
866 , p_commit => FND_API.G_FALSE
867 , p_processing_set_id => p_processing_set_id
868 , p_object_type => p_object_type
869 , x_return_status => x_return_status
870 , x_msg_count => x_msg_count
871 , x_msg_data => x_msg_data
872 );
873
874 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
875 THEN
876 fnd_log.string
877 (
878 fnd_log.level_statement
879 , L_LOG_MODULE || 'charges_purge_end'
880 , 'returned from Charges purge API with status '
881 || x_return_status
882 );
883 END IF ;
884 END IF;
885
886 ---
887
888 Check_User_Termination;
889
890 ---
891
892
893 --Added for 12.1 Service Costing
894
895 IF x_return_status = FND_API.G_RET_STS_SUCCESS
896 THEN
897 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
898 THEN
899 fnd_log.string
900 (
901 fnd_log.level_statement
902 , L_LOG_MODULE || 'cost_purge_start'
903 , 'calling the Cost purge API '
904 || 'cs_cost_details_pvt.purge_cost'
905 );
906 END IF ;
907
908
909
910
911 -- This procedure deletes all the charge lines that are related to
912 -- SRs that are available in the global temp table with purge status NULL.
913
914 CS_COST_DETAILS_PVT.Purge_Cost
915 (
916 p_api_version_number => 1.0
917 , p_init_msg_list => FND_API.G_FALSE
918 , p_commit => FND_API.G_FALSE
919 , p_processing_set_id => p_processing_set_id
920 , p_object_type => p_object_type
921 , x_return_status => x_return_status
922 , x_msg_count => x_msg_count
923 , x_msg_data => x_msg_data
924 );
925
926
927 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
928 THEN
929 fnd_log.string
930 (
931 fnd_log.level_statement
932 , L_LOG_MODULE || 'cost_purge_end'
933 , 'returned from cost purge API with status '
934 || x_return_status
935 );
936 END IF ;
937 END IF;
938
939
940
941
942
943
944 ---
945
946 Check_User_Termination;
947
948 ---
949
950
951 IF x_return_status = FND_API.G_RET_STS_SUCCESS
952 THEN
953 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
954 THEN
955 fnd_log.string
956 (
957 fnd_log.level_statement
958 , L_LOG_MODULE || 'messages_purge_start'
959 , 'calling the Messages purge API cs_messages_pkg.delete_message'
960 );
961 END IF ;
962
963 -- This procedure deletes all the messages that are related to
964 -- SRs that are available in the global temp table with purge status NULL.
965
966 CS_MESSAGES_PKG.Delete_Message
967 (
968 p_api_version_number => 1.0
969 , p_init_msg_list => FND_API.G_FALSE
970 , p_commit => FND_API.G_FALSE
971 , p_processing_set_id => p_processing_set_id
972 , p_object_type => p_object_type
973 , x_return_status => x_return_status
974 , x_msg_count => x_msg_count
975 , x_msg_data => x_msg_data
976 );
977
978 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
979 THEN
980 fnd_log.string
981 (
982 fnd_log.level_statement
983 , L_LOG_MODULE || 'messages_purge_end'
984 , 'returned from Messages purge API with status '
985 || x_return_status
986 );
987 END IF ;
988 END IF;
989
990 ---
991
992 Check_User_Termination;
993
994 ---
995
996 IF x_return_status = FND_API.G_RET_STS_SUCCESS
997 THEN
998 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
999 THEN
1000 fnd_log.string
1001 (
1002 fnd_log.level_statement
1003 , L_LOG_MODULE || 'contacts_purge_start'
1004 , 'calling the Contacts purge API '
1005 || 'cs_sr_delete_util.delete_contacts'
1006 );
1007 END IF ;
1008
1009 -- This procedure deletes all the contacts related to SRs present in the
1010 -- global temp table with purge status NULL, and also the contact audit
1011 -- information, party role extended attributes and party role extended
1012 -- attributes audit information related to the contacts.
1013
1014 CS_SR_DELETE_UTIL.Delete_Contacts
1015 (
1016 p_api_version_number => 1.0
1017 , p_init_msg_list => FND_API.G_FALSE
1018 , p_commit => FND_API.G_FALSE
1019 , p_processing_set_id => p_processing_set_id
1020 , p_object_type => p_object_type
1021 , x_return_status => x_return_status
1022 , x_msg_count => x_msg_count
1023 , x_msg_data => x_msg_data
1024 );
1025
1026 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1027 THEN
1028 fnd_log.string
1029 (
1030 fnd_log.level_statement
1031 , L_LOG_MODULE || 'contacts_purge_end'
1032 , 'returned from Contacts purge API with status '
1033 || x_return_status
1034 );
1035 END IF ;
1036 END IF;
1037
1038 ---
1039
1040 Check_User_Termination;
1041
1042 ---
1043
1044 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1045 THEN
1046 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1047 THEN
1048 fnd_log.string
1049 (
1050 fnd_log.level_statement
1051 , L_LOG_MODULE || 'sr_attr_purge_start'
1052 , 'calling the SR Extended Attribs purge API '
1053 || 'cs_sr_delete_util.delete_sr_attributes'
1054 );
1055 END IF ;
1056
1057 -- This procedure deletes all the extended attributes related to SRs
1058 -- present in the global temp table with purge status NULL and also the
1059 -- audit information related to the extended attributes.
1060
1061 CS_SR_DELETE_UTIL.Delete_Sr_Attributes
1062 (
1063 p_api_version_number => 1.0
1064 , p_init_msg_list => FND_API.G_FALSE
1065 , p_commit => FND_API.G_FALSE
1066 , p_processing_set_id => p_processing_set_id
1067 , p_object_type => p_object_type
1068 , x_return_status => x_return_status
1069 , x_msg_count => x_msg_count
1070 , x_msg_data => x_msg_data
1071 );
1072
1073 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1074 THEN
1075 fnd_log.string
1076 (
1077 fnd_log.level_statement
1078 , L_LOG_MODULE || 'sr_attr_purge_end'
1079 , 'returned from SR Extended Attribs purge API with status '
1080 || x_return_status
1081 );
1082 END IF ;
1083 END IF;
1084
1085 ---
1086
1087 Check_User_Termination;
1088
1089 ---
1090
1091 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1092 THEN
1093 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1094 THEN
1095 fnd_log.string
1096 (
1097 fnd_log.level_statement
1098 , L_LOG_MODULE || 'sr_audit_purge_start'
1099 , 'calling the SR Audit purge API '
1100 || 'cs_sr_delete_util.delete_audit_records'
1101 );
1102 END IF ;
1103
1104 -- This procedure deletes all the audit information related to SRs
1105 -- present in the global temp table with purge status NULL
1106
1107 CS_SR_DELETE_UTIL.Delete_Audit_Records
1108 (
1109 p_api_version_number => 1.0
1110 , p_init_msg_list => FND_API.G_FALSE
1111 , p_commit => FND_API.G_FALSE
1112 , p_processing_set_id => p_processing_set_id
1113 , p_object_type => p_object_type
1114 , x_return_status => x_return_status
1115 , x_msg_count => x_msg_count
1116 , x_msg_data => x_msg_data
1117 );
1118
1119 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1120 THEN
1121 fnd_log.string
1122 (
1123 fnd_log.level_statement
1124 , L_LOG_MODULE || 'sr_audit_purge_end'
1125 , 'returned from SR Audit purge API with status '
1126 || x_return_status
1127 );
1128 END IF ;
1129 END IF;
1130
1131 ---
1132
1133 Check_User_Termination;
1134
1135 ---
1136
1137 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1138 THEN
1139 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1140 THEN
1141 fnd_log.string
1142 (
1143 fnd_log.level_statement
1144 , L_LOG_MODULE || 'sr_link_purge_start'
1145 , 'calling the SR Links purge API '
1146 || 'cs_incidentlinks_pvt.delete_incidentlink'
1147 );
1148 END IF ;
1149
1150 -- This procedure deletes all the SR links related to SRs present in the
1151 -- global temp table with purge status NULL
1152
1153 CS_INCIDENTLINKS_PVT.Delete_IncidentLink
1154 (
1155 p_api_version_number => 1.0
1156 , p_init_msg_list => FND_API.G_FALSE
1157 , p_commit => FND_API.G_FALSE
1158 , p_processing_set_id => p_processing_set_id
1159 , p_object_type => p_object_type
1160 , x_return_status => x_return_status
1161 , x_msg_count => x_msg_count
1162 , x_msg_data => x_msg_data
1163 );
1164
1165 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1166 THEN
1167 fnd_log.string
1168 (
1169 fnd_log.level_statement
1170 , L_LOG_MODULE || 'sr_link_purge_end'
1171 , 'returned from SR Links purge API with status '
1172 || x_return_status
1173 );
1174 END IF ;
1175 END IF;
1176
1177 ---
1178
1179 Check_User_Termination;
1180
1181 ---
1182
1183 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1184 THEN
1185 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1186 THEN
1187 fnd_log.string
1188 (
1189 fnd_log.level_statement
1190 , L_LOG_MODULE || 'km_link_purge_start'
1191 , 'calling the KM Links purge API '
1192 || 'cs_knowledge_grp.purge_knowledge_links'
1193 );
1194 END IF ;
1195
1196 -- This procedure deletes all the KM solution links related to SRs
1197 -- present in the global temp table with purge status NULL
1198
1199 CS_KNOWLEDGE_GRP.Purge_Knowledge_Links
1200 (
1201 p_api_version => 1.0
1202 , p_init_msg_list => FND_API.G_FALSE
1203 , p_commit => FND_API.G_FALSE
1204 , p_processing_set_id => p_processing_set_id
1205 , p_object_type => p_object_type
1206 , x_return_status => x_return_status
1207 , x_msg_count => x_msg_count
1208 , x_msg_data => x_msg_data
1209 );
1210
1211 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1212 THEN
1213 fnd_log.string
1214 (
1215 fnd_log.level_statement
1216 , L_LOG_MODULE || 'km_link_purge_end'
1217 , 'returned from KM Links purge API with status '
1218 || x_return_status
1219 );
1220 END IF ;
1221 END IF;
1222
1223 ---
1224
1225 Check_User_Termination;
1226
1227 ---
1228
1229 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1230 THEN
1231 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1232 THEN
1233 fnd_log.string
1234 (
1235 fnd_log.level_statement
1236 , L_LOG_MODULE || 'notes_purge_start'
1237 , 'calling the Notes purge API cac_note_purge_pub.purge_notes'
1238 );
1239 END IF ;
1240
1241 -- This procedure deletes all the notes related to SRs present in the
1242 -- global temp table with purge status NULL
1243
1244 CAC_NOTE_PURGE_PUB.Purge_Notes
1245 (
1246 p_api_version => 1.0
1247 , p_init_msg_list => FND_API.G_FALSE
1248 , p_commit => FND_API.G_FALSE
1249 , p_processing_set_id => p_processing_set_id
1250 , p_object_type => p_object_type
1251 , x_return_status => x_return_status
1252 , x_msg_count => x_msg_count
1253 , x_msg_data => x_msg_data
1254 );
1255
1256 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1257 THEN
1258 fnd_log.string
1259 (
1260 fnd_log.level_statement
1261 , L_LOG_MODULE || 'notes_purge_end'
1262 , 'returned from Notes purge API with status ' || x_return_status
1263 );
1264 END IF ;
1265 END IF;
1266
1267 ---
1268
1269 Check_User_Termination;
1270
1271 ---
1272
1273 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1274 THEN
1275 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1276 THEN
1277 fnd_log.string
1278 (
1279 fnd_log.level_statement
1280 , L_LOG_MODULE || 'task_purge_start'
1281 , 'calling the Tasks purge API cac_task_purge_pub.purge_task'
1282 );
1283 END IF ;
1284
1285 -- This procedure deletes all the non-field service tasks related
1286 -- to SRs present in the global temp table with purge status NULL
1287
1288 CAC_TASK_PURGE_PUB.Purge_Tasks
1289 (
1290 p_api_version => 1.0
1291 , p_init_msg_list => FND_API.G_FALSE
1292 , p_commit => FND_API.G_FALSE
1293 , p_processing_set_id => p_processing_set_id
1294 , p_object_type => p_object_type
1295 , x_return_status => x_return_status
1296 , x_msg_count => x_msg_count
1297 , x_msg_data => x_msg_data
1298 );
1299
1300 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1301 THEN
1302 fnd_log.string
1303 (
1304 fnd_log.level_statement
1305 , L_LOG_MODULE || 'task_purge_end'
1306 , 'returned from Tasks purge API with status ' || x_return_status
1307 );
1308 END IF ;
1309 END IF;
1310
1311 ---
1312
1313 Check_User_Termination;
1314
1315 ---
1316
1317 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1318 THEN
1319 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1320 THEN
1321 fnd_log.string
1322 (
1323 fnd_log.level_statement
1324 , L_LOG_MODULE || 'work_item_purge_start'
1325 , 'calling the UWQ Work Items purge API ieu_wr_pub.purge_wr_item'
1326 );
1327 END IF ;
1328
1329 -- This procedure deletes all the UWQ work items related
1330 -- to SRs present in the global temp table with purge status NULL
1331
1332 IEU_WR_PUB.Purge_Wr_Item
1333 (
1334 p_api_version_number => 1.0
1335 , p_init_msg_list => FND_API.G_FALSE
1336 , p_commit => FND_API.G_FALSE
1337 , p_processing_set_id => p_processing_set_id
1338 , p_object_type => p_object_type
1339 , x_return_status => x_return_status
1340 , x_msg_count => x_msg_count
1341 , x_msg_data => x_msg_data
1342 );
1343
1344 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1345 THEN
1346 fnd_log.string
1347 (
1348 fnd_log.level_statement
1349 , L_LOG_MODULE || 'work_item_purge_end'
1350 , 'returned from UWQ Work Items purge API with status '
1351 || x_return_status
1352 );
1353 END IF ;
1354 END IF;
1355
1356 ---
1357
1358 Check_User_Termination;
1359
1360 ---
1361
1362 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1363 THEN
1364 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1365 THEN
1366 fnd_log.string
1367 (
1368 fnd_log.level_statement
1369 , L_LOG_MODULE || 'interaction_purge_start'
1370 , 'calling the Interactions purge API '
1371 || 'jtf_ih_purge.p_delete_interactions'
1372 );
1373 END IF ;
1374
1375 -- This procedure deletes all the interactions and activities related
1376 -- to SRs present in the global temp table with purge status NULL
1377
1378 JTF_IH_PURGE.P_Delete_Interactions
1379 (
1380 p_api_version => 1.0
1381 , p_init_msg_list => FND_API.G_FALSE
1382 , p_commit => FND_API.G_FALSE
1383 , p_processing_set_id => p_processing_set_id
1384 , p_object_type => p_object_type
1385 , x_return_status => x_return_status
1386 , x_msg_count => x_msg_count
1387 , x_msg_data => x_msg_data
1388 );
1389
1390 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1391 THEN
1392 fnd_log.string
1393 (
1394 fnd_log.level_statement
1395 , L_LOG_MODULE || 'interaction_purge_end'
1396 , 'returned from Interactions purge API with status '
1397 || x_return_status
1398 );
1399 END IF ;
1400 END IF;
1401
1402 ---
1403
1404 Check_User_Termination;
1405
1406 ---
1407
1408 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1409 THEN
1410 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1411 THEN
1412 fnd_log.string
1413 (
1414 fnd_log.level_statement
1415 , L_LOG_MODULE || 'get_srid_for_attach_del_start'
1416 , 'Collecting all the Incident_ids into a pl/sql table to '
1417 || 'delete attachments, one at a time'
1418 );
1419 END IF ;
1420
1421 -- The following query collects all the incident ids for which attachments
1422 -- need to be deleted, into a pl/sql table since, in R12, FND does not have
1423 -- an API that can delete attachments in bulk.
1424
1425 SELECT
1426 object_id
1427 BULK COLLECT INTO
1428 l_incident_id_tbl
1429 FROM
1430 jtf_object_purge_param_tmp
1431 WHERE
1432 object_type = 'SR'
1433 AND p_processing_set_id = processing_set_id
1434 AND NVL(purge_status, 'S') = 'S';
1435
1436 l_row_count := SQL%ROWCOUNT;
1437
1438 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1439 THEN
1440 fnd_log.string
1441 (
1442 fnd_log.level_statement
1443 , L_LOG_MODULE || 'get_srid_for_attach_del_end'
1444 , 'After collecting all the Incident_ids into a pl/sql table '
1445 || l_row_count || ' rows'
1446 );
1447 END IF ;
1448 END IF;
1449
1450 ---
1451
1452 Check_User_Termination;
1453
1454 ---
1455
1456 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1457 THEN
1458 IF l_incident_id_tbl.COUNT > 0
1459 THEN
1460 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1461 THEN
1462 fnd_log.string
1463 (
1464 fnd_log.level_statement
1465 , L_LOG_MODULE || 'attachment_purge_start'
1466 , 'calling the Attachments purge API '
1467 || 'fnd_attached_documents2_pkg.delete_attachments'
1468 );
1469 END IF ;
1470
1471 -- This procedure deletes all the attachments related
1472 -- to SRs present in the global temp table with purge status NULL
1473 -- NOTE: THIS PROCEDURE DELETES ATTACHMENTS ONE AT A TIME.
1474
1475 FOR j in l_incident_id_tbl.FIRST..l_incident_id_tbl.LAST
1476 LOOP
1477 FND_ATTACHED_DOCUMENTS2_PKG.Delete_Attachments
1478 (
1479 x_entity_name => 'CS_INCIDENTS'
1480 , x_pk1_value => l_incident_id_tbl(j)
1481 , x_pk2_value => null
1482 , x_pk3_value => null
1483 , x_pk4_value => null
1484 , x_pk5_value => null
1485 , x_delete_document_flag => 'Y'
1486 , x_automatically_added_flag => null
1487 );
1488 END LOOP;
1489
1490 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1491 THEN
1492 fnd_log.string
1493 (
1494 fnd_log.level_statement
1495 , L_LOG_MODULE || 'attachment_purge_end'
1496 , 'returned from Attachments purge API with status '
1497 || x_return_status
1498 );
1499 END IF ;
1500 ELSIF l_incident_id_tbl.COUNT <= 0
1501 THEN
1502 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1503 THEN
1504 fnd_log.string
1505 (
1506 fnd_log.level_statement
1507 , L_LOG_MODULE || 'attachment_purge_err'
1508 , 'while calling Attachments purge API l_incident_id_tbl has '
1509 || l_incident_id_tbl.COUNT || ' rows'
1510 );
1511 END IF ;
1512 END IF;
1513 END IF;
1514
1515 ---
1516
1517 Check_User_Termination;
1518
1519 ---
1520
1521 IF p_audit_required = 'Y'
1522 AND x_return_status = FND_API.G_RET_STS_SUCCESS
1523
1524 -- If audit information is required as per profile option
1525 -- CS_SR_PURGE_AUDIT_REQUIRED, then proceed further
1526
1527 THEN
1528 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1529 THEN
1530 fnd_log.string
1531 (
1532 fnd_log.level_statement
1533 , L_LOG_MODULE || 'get_srbinfo_for_audit_start'
1534 , 'Collecting information that has to be written to the '
1535 || 'CS_INCIDENTS_PURGE_AUDIT_B table'
1536 );
1537 END IF ;
1538
1539 -- Collect all the information from the cs_incidents_all_b
1540 -- table that needs to be entered in the purge audit table
1541
1542 SELECT
1543 incident_number
1544 , incident_type_id
1545 , customer_id
1546 , inv_organization_id
1547 , inventory_item_id
1548 , customer_product_id
1549 , creation_date
1550 , last_update_date
1551 BULK COLLECT INTO
1552 l_incident_number_tbl
1553 , l_incident_type_id_tbl
1554 , l_customer_id_tbl
1555 , l_inv_organization_id_tbl
1556 , l_inventory_item_id_tbl
1557 , l_customer_product_id_tbl
1558 , l_inc_creation_date_tbl
1559 , l_inc_last_update_date_tbl
1560 FROM
1561 cs_incidents_all_b c
1562 , jtf_object_purge_param_tmp j
1563 WHERE
1564 j.object_type = 'SR'
1565 AND j.object_id = c.incident_id
1566 AND NVL
1567 (
1568 j.purge_status
1569 , 'S'
1570 ) = 'S'
1571 AND j.processing_set_id = p_processing_set_id;
1572
1573 l_row_count := SQL%ROWCOUNT;
1574
1575 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1576 THEN
1577 fnd_log.string
1578 (
1579 fnd_log.level_statement
1580 , L_LOG_MODULE || 'get_srbinfo_for_audit_end'
1581 , 'After collecting information that has to be written to the '
1582 || 'CS_INCIDENTS_PURGE_AUDIT_B table ' || l_row_count || ' rows'
1583 );
1584 END IF ;
1585
1586 ---
1587
1588 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1589 THEN
1590 fnd_log.string
1591 (
1592 fnd_log.level_statement
1593 , L_LOG_MODULE || 'get_srtlinfo_for_audit_start'
1594 , 'Collecting information that has to be written to the '
1595 || 'CS_INCIDENTS_PURGE_AUDIT_TL table'
1596 );
1597 END IF ;
1598
1599 -- Collect all the information from the cs_incidents_all_tl
1600 -- table that needs to be entered in the purge audit table
1601
1602 SELECT
1603 incident_id
1604 , language
1605 , source_lang
1606 , summary
1607 BULK COLLECT INTO
1608 l_incident_id_tl_tbl
1609 , l_language_tbl
1610 , l_source_lang_tbl
1611 , l_summary_tbl
1612 FROM
1613 cs_incidents_all_tl c
1614 , jtf_object_purge_param_tmp j
1615 WHERE
1616 j.object_type = 'SR'
1617 AND j.object_id = c.incident_id
1618 AND NVL
1619 (
1620 j.purge_status
1621 , 'S'
1622 ) = 'S'
1623 AND j.processing_set_id = p_processing_set_id;
1624
1625 l_row_count := SQL%ROWCOUNT;
1626
1627 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1628 THEN
1629 fnd_log.string
1630 (
1631 fnd_log.level_statement
1632 , L_LOG_MODULE || 'get_srtlinfo_for_audit_end'
1633 , 'After collecting information that has to be written to the '
1634 || 'CS_INCIDENTS_PURGE_AUDIT_TL table ' || l_row_count || ' rows'
1635 );
1636 END IF ;
1637 ELSIF p_audit_required <> 'Y'
1638 THEN
1639 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1640 THEN
1641 fnd_log.string
1642 (
1643 fnd_log.level_statement
1644 , L_LOG_MODULE || 'purge_audit_not_reqd'
1645 , 'Not collecting audit information since p_audit_required is '
1646 || p_audit_required || '. Would be done if Y.'
1647 );
1648 END IF ;
1649 END IF;
1650
1651 ---
1652
1653 Check_User_Termination;
1654
1655 ---
1656
1657 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1658 THEN
1659 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1660 THEN
1661 fnd_log.string
1662 (
1663 fnd_log.level_statement
1664 , L_LOG_MODULE || 'get_data_for_staging_start'
1665 , 'Collecting information that has to be written to the '
1666 || 'CS_INCIDENTS_PURGE_STAGING table'
1667 );
1668 END IF ;
1669
1670 -- Collect all the information from the global temp table
1671 -- table that needs to be entered in the staging table
1672 -- so that log can be generated at the end of the purge
1673 -- process from the purge concurrent program
1674
1675 SELECT
1676 object_id
1677 , purge_error_message
1678 BULK COLLECT INTO
1679 l_incident_id_status_tbl
1680 , l_purge_error_message_tbl
1681 FROM
1682 jtf_object_purge_param_tmp j
1683 WHERE
1684 j.object_type = 'SR'
1685 AND NVL
1686 (
1687 j.purge_status
1688 , 'S'
1689 ) = 'E'
1690 AND j.processing_set_id = p_processing_set_id;
1691
1692 l_row_count := SQL%ROWCOUNT;
1693
1694 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1695 THEN
1696 fnd_log.string
1697 (
1698 fnd_log.level_statement
1699 , L_LOG_MODULE || 'get_data_for_staging_end'
1700 , 'After collecting information that has to be written to the '
1701 || 'CS_INCIDENTS_PURGE_STAGING table ' || l_row_count || ' rows'
1702 );
1703 END IF ;
1704 END IF;
1705
1706 ---
1707
1708 Check_User_Termination;
1709
1710 ---
1711
1712 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1713 THEN
1714 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1715 THEN
1716 fnd_log.string
1717 (
1718 fnd_log.level_statement
1719 , L_LOG_MODULE || 'del_srtl_start'
1720 , 'Deleting rows from table CS_INCIDENTS_ALL_TL'
1721 );
1722 END IF ;
1723
1724 -- Delete all the translatable attributes of the
1725 -- service request from the table
1726 -- Bug 10090362: Changed the hint in the sub query
1727
1728 DELETE /*+ index(t) */ cs_incidents_all_tl t
1729 WHERE
1730 incident_id IN
1731 (
1732 SELECT /*+ cardinality(jtf_object_purge_param_tmp, 10) */
1733 object_id
1734 FROM
1735 jtf_object_purge_param_tmp
1736 WHERE
1737 object_type = 'SR'
1738 AND p_processing_set_id = processing_set_id
1739 AND NVL
1740 (
1741 purge_status
1742 , 'S'
1743 ) = 'S'
1744 );
1745
1746 l_row_count := SQL%ROWCOUNT;
1747
1748 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1749 THEN
1750 fnd_log.string
1751 (
1752 fnd_log.level_statement
1753 , L_LOG_MODULE || 'del_srtl_end'
1754 , 'After deleting rows from table CS_INCIDENTS_ALL_TL ' || l_row_count
1755 || ' rows'
1756 );
1757 END IF ;
1758 END IF;
1759
1760 ---
1761
1762 Check_User_Termination;
1763
1764 ---
1765
1766 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1767 THEN
1768 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1769 THEN
1770 fnd_log.string
1771 (
1772 fnd_log.level_statement
1773 , L_LOG_MODULE || 'del_srb_start'
1774 , 'Deleting rows from table CS_INCIDENTS_ALL_B'
1775 );
1776 END IF ;
1777
1778 -- Delete all the attributes of the
1779 -- service request from the table
1780 -- Bug 10090362: Changed the hint in the sub query
1781
1782 DELETE /*+ index(b) */ cs_incidents_all_b b
1783 WHERE
1784 incident_id IN
1785 (
1786 SELECT /*+ cardinality(jtf_object_purge_param_tmp, 10) */
1787 object_id
1788 FROM
1789 jtf_object_purge_param_tmp
1790 WHERE
1791 object_type = 'SR'
1792 AND p_processing_set_id = processing_set_id
1793 AND NVL
1794 (
1795 purge_status
1796 , 'S'
1797 ) = 'S'
1798 );
1799
1800 l_row_count := SQL%ROWCOUNT;
1801
1802 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1803 THEN
1804 fnd_log.string
1805 (
1806 fnd_log.level_statement
1807 , L_LOG_MODULE || 'del_srb_end'
1808 , 'After deleting rows from table CS_INCIDENTS_ALL_B ' || l_row_count
1809 || ' rows'
1810 );
1811 END IF ;
1812 END IF;
1813
1814 ---
1815
1816 Check_User_Termination;
1817
1818 ---
1819
1820 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1821 THEN
1822 IF l_incident_id_status_tbl.COUNT > 0
1823
1824 -- If there are any SRs with purge_status E indicating
1825 -- error while validation, the status of these SRs is
1826 -- updated back to the Staging Table to facilitate
1827 -- generation of concurrent request output file.
1828
1829 THEN
1830 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1831 THEN
1832 fnd_log.string
1833 (
1834 fnd_log.level_statement
1835 , L_LOG_MODULE || 'write_staging_start'
1836 , 'Updating purge status into table CS_INCIDENTS_PURGE_STAGING'
1837 );
1838 END IF ;
1839
1840 -- Updating the staging table to indicate the SRs that failed during purge
1841 -- due to validations against child objects.
1842
1843 FORALL j IN l_incident_id_status_tbl.FIRST..l_incident_id_status_tbl.LAST
1844 UPDATE cs_incidents_purge_staging
1845 SET
1846 purge_status = 'E'
1847 , purge_error_message = l_purge_error_message_tbl(j)
1848 WHERE
1849 incident_id = l_incident_id_status_tbl(j);
1850
1851 l_row_count := SQL%ROWCOUNT;
1852
1853 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1854 THEN
1855 fnd_log.string
1856 (
1857 fnd_log.level_statement
1858 , L_LOG_MODULE || 'write_staging_end'
1859 , 'After updating purge status into table CS_INCIDENTS_PURGE_STAGING '
1860 || l_row_count || ' rows'
1861 );
1862 END IF ;
1863 ELSIF l_incident_id_status_tbl.COUNT <= 0
1864 THEN
1865 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1866 THEN
1867 fnd_log.string
1868 (
1869 fnd_log.level_statement
1870 , L_LOG_MODULE || 'write_staging_norows'
1871 , 'While updating purge status into table CS_INCIDENTS_PURGE_STAGING '
1872 || 'l_incident_id_status_tbl had ' || l_incident_id_status_tbl.COUNT
1873 || ' rows'
1874 );
1875 END IF ;
1876 END IF;
1877 END IF;
1878
1879 ---
1880
1881 Check_User_Termination;
1882
1883 ---
1884
1885 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1886 THEN
1887 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1888 THEN
1889 fnd_log.string
1890 (
1891 fnd_log.level_statement
1892 , L_LOG_MODULE || 'write_staging_success_start'
1893 , 'Updating rows processed successfully with purge status = S'
1894 );
1895 END IF ;
1896
1897 -- Setting all the rows in the staging table
1898 -- belonging to this batch which were processed
1899 -- successfully to purge_status 'S' to be able
1900 -- to differentiate these rows from the ones
1901 -- that were not yet processed.
1902
1903 UPDATE cs_incidents_purge_staging
1904 SET
1905 purge_status = 'S'
1906 WHERE
1907 incident_id IN
1908 (
1909 SELECT
1910 object_id
1911 FROM
1912 jtf_object_purge_param_tmp j
1913 WHERE
1914 j.object_type = 'SR'
1915 AND NVL
1916 (
1917 j.purge_status
1918 , 'S'
1919 ) = 'S'
1920 AND j.processing_set_id = p_processing_set_id
1921 );
1922 l_row_count := SQL%ROWCOUNT;
1923
1924 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1925 THEN
1926 fnd_log.string
1927 (
1928 fnd_log.level_statement
1929 , L_LOG_MODULE || 'write_staging_success_end'
1930 , 'After updating rows processed successfully with '
1931 || 'purge status = S ' || l_row_count
1932 );
1933 END IF ;
1934 END IF;
1935
1936 ---
1937
1938 Check_User_Termination;
1939
1940 ---
1941
1942 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1943 AND p_audit_required = 'Y'
1944
1945 -- If audit information is required as per profile option
1946 -- CS_SR_PURGE_AUDIT_REQUIRED, then proceed further
1947
1948 THEN
1949 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1950 THEN
1951 fnd_log.string
1952 (
1953 fnd_log.level_statement
1954 , L_LOG_MODULE || 'purge_audit_create_start'
1955 , 'Calling the procedure CREATE_PURGEAUDIT_RECORDS to create purge '
1956 || 'audit information'
1957 );
1958 END IF ;
1959
1960 -- This procedure takes as input all the data that needs to be captured
1961 -- in the purge audit tables and staging table. Data of all SRs that were
1962 -- successfully purged are entered in the audit tables. For the SRs for
1963 -- which purge failed, the staging table is updated with the purge error
1964 -- message and purge status.
1965
1966 Create_Purgeaudit_Records
1967 (
1968 p_api_version_number => 1.0
1969 , p_init_msg_list => FND_API.G_FALSE
1970 , p_commit => FND_API.G_FALSE
1971 , p_purge_set_id => l_purge_set_id
1972 , p_incident_id_tbl => l_incident_id_tbl
1973 , p_incident_number_tbl => l_incident_number_tbl
1974 , p_incident_type_id_tbl => l_incident_type_id_tbl
1975 , p_customer_id_tbl => l_customer_id_tbl
1976 , p_inv_organization_id_tbl => l_inv_organization_id_tbl
1977 , p_inventory_item_id_tbl => l_inventory_item_id_tbl
1978 , p_customer_product_id_tbl => l_customer_product_id_tbl
1979 , p_inc_creation_date_tbl => l_inc_creation_date_tbl
1980 , p_inc_last_update_date_tbl => l_inc_last_update_date_tbl
1981 , p_incident_id_tl_tbl => l_incident_id_tl_tbl
1982 , p_language_tbl => l_language_tbl
1983 , p_source_lang_tbl => l_source_lang_tbl
1984 , p_summary_tbl => l_summary_tbl
1985 , x_return_status => x_return_status
1986 , x_msg_count => x_msg_count
1987 , x_msg_data => x_msg_data
1988 );
1989
1990 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1991 THEN
1992 fnd_log.string
1993 (
1994 fnd_log.level_statement
1995 , L_LOG_MODULE || 'purge_audit_create_end'
1996 , 'After calling the procedure CREATE_PURGEAUDIT_RECORDS. '
1997 || 'Returned with status ' || x_return_status
1998 );
1999 END IF ;
2000 END IF;
2001
2002 ---
2003
2004 Check_User_Termination;
2005
2006 ---
2007
2008 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2009 THEN
2010 fnd_log.string
2011 (
2012 fnd_log.level_procedure
2013 , L_LOG_MODULE || 'end'
2014 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
2015 || x_return_status
2016 );
2017 END IF ;
2018
2019 EXCEPTION
2020
2021 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2023
2024 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2025 THEN
2026 fnd_log.string
2027 (
2028 fnd_log.level_unexpected
2029 , L_LOG_MODULE || 'unexpected_error'
2030 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
2031 );
2032
2033 x_msg_count := FND_MSG_PUB.Count_Msg;
2034
2035 IF x_msg_count > 0
2036 THEN
2037 FOR
2038 i IN 1..x_msg_count
2039 LOOP
2040 FND_MSG_PUB.Get
2041 (
2042 p_msg_index => i
2043 , p_encoded => 'F'
2044 , p_data => x_msg_data
2045 , p_msg_index_out => x_msg_index_out
2046 );
2047 fnd_log.string
2048 (
2049 fnd_log.level_unexpected
2050 , L_LOG_MODULE || 'unexpected_error'
2051 , 'Error encountered is : ' || x_msg_data || ' [Index:'
2052 || x_msg_index_out || ']'
2053 );
2054 END LOOP;
2055 END IF ;
2056 END IF ;
2057
2058 WHEN OTHERS THEN
2059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2060 FND_MESSAGE.Set_Name('CS', 'CS_SR_DEL_FAIL');
2061 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2062 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
2063 FND_MSG_PUB.ADD;
2064
2065 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2066 THEN
2067 fnd_log.string
2068 (
2069 fnd_log.level_unexpected
2070 , L_LOG_MODULE || 'when_others'
2071 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
2072 );
2073 fnd_log.string
2074 (
2075 fnd_log.level_unexpected
2076 , L_LOG_MODULE || 'when_others'
2077 , SQLERRM
2078 );
2079 END IF ;
2080
2081 END Delete_ServiceRequest;
2082
2083 --------------------------------------------------------------------------------
2084 -- Procedure Name : DELETE_CONTACTS
2085 --
2086 -- Parameters (other than standard ones)
2087 -- IN
2088 -- p_object_type : Type of object for which this procedure is
2089 -- being called. (Here it will be 'SR')
2090 -- p_processing_set_id : Id that helps the API in identifying the
2091 -- set of SRs for which the child objects have
2092 -- to be deleted.
2093 --
2094 -- Description
2095 -- This procedure delets all the contacts, related to an SR present in the
2096 -- global temp table with purge status NULL along with contact audit
2097 -- information, party role extended attributes and party role extended
2098 -- attribute audit information
2099 -- HISTORY
2100 --
2101 ----------------+------------+--------------------------------------------------
2102 -- DATE | UPDATED BY | Change Description
2103 ----------------+------------+--------------------------------------------------
2104 -- 2-Aug_2005 | varnaray | Created
2105 -- | |
2106 ----------------+------------+--------------------------------------------------
2107 /*#
2108 * This procedure delets all the contacts, related to an SR present in the
2109 * global temp table with purge status NULL along with contact audit
2110 * information, party role extended attributes and party role extended
2111 * attribute audit information
2112 * @param p_object_type Type of object for which this procedure is being called.
2113 * (Here it will be 'SR')
2114 * @param p_processing_set_id Id that helps the API in identifying the set of
2115 * SRs for which the child objects have to be deleted.
2116 * @rep:scope internal
2117 * @rep:product CS
2118 * @rep:displayname Delete Contacts
2119 */
2120 PROCEDURE Delete_Contacts
2121 (
2122 p_api_version_number IN NUMBER := 1.0
2123 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2124 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2125 , p_object_type IN VARCHAR2
2126 , p_processing_set_id IN NUMBER
2127 , x_return_status OUT NOCOPY VARCHAR2
2128 , x_msg_count OUT NOCOPY NUMBER
2129 , x_msg_data OUT NOCOPY VARCHAR2
2130 )
2131 IS
2132 --------------------------------------------------------------------------------
2133 L_API_VERSION CONSTANT NUMBER := 1.0;
2134 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_CONTACTS';
2135 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
2136 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
2137
2138 l_row_count NUMBER := 0;
2139
2140 BEGIN
2141 x_return_status := FND_API.G_RET_STS_SUCCESS;
2142
2143 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2144 THEN
2145 fnd_log.string
2146 (
2147 fnd_log.level_procedure
2148 , L_LOG_MODULE || 'start'
2149 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2150 );
2151 fnd_log.string
2152 (
2153 fnd_log.level_procedure
2154 , L_LOG_MODULE || 'param 1'
2155 , 'p_api_version_number:' || p_api_version_number
2156 );
2157 fnd_log.string
2158 (
2159 fnd_log.level_procedure
2160 , L_LOG_MODULE || 'param 2'
2161 , 'p_init_msg_list:' || p_init_msg_list
2162 );
2163 fnd_log.string
2164 (
2165 fnd_log.level_procedure
2166 , L_LOG_MODULE || 'param 3'
2167 , 'p_commit:' || p_commit
2168 );
2169 fnd_log.string
2170 (
2171 fnd_log.level_procedure
2172 , L_LOG_MODULE || 'param 4'
2173 , 'p_object_type:' || p_object_type
2174 );
2175 fnd_log.string
2176 (
2177 fnd_log.level_procedure
2178 , L_LOG_MODULE || 'param 5'
2179 , 'p_processing_set_id:' || p_processing_set_id
2180 );
2181 END IF ;
2182
2183 IF NOT FND_API.Compatible_API_Call
2184 (
2185 L_API_VERSION
2186 , p_api_version_number
2187 , L_API_NAME
2188 , G_PKG_NAME
2189 )
2190 THEN
2191 FND_MSG_PUB.Count_And_Get
2192 (
2193 p_count => x_msg_count
2194 , p_data => x_msg_data
2195 );
2196 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2197 END IF ;
2198
2199 IF
2200 FND_API.to_Boolean(p_init_msg_list)
2201 THEN
2202 FND_MSG_PUB.initialize;
2203 END IF ;
2204
2205 ------------------------------------------------------------------------------
2206 -- Actual Logic starts below:
2207 ------------------------------------------------------------------------------
2208
2209 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2210 THEN
2211 fnd_log.string
2212 (
2213 fnd_log.level_statement
2214 , L_LOG_MODULE || 'del_cont_ext_aud_start'
2215 , 'Deleting data from table CS_SR_CONTACTS_EXT_AUDIT'
2216 );
2217 END IF ;
2218
2219 -- The following statement deletes the audit information captured for
2220 -- the translatable Party role extended attributes related to SRs in the
2221 -- global temp table with purge status NULL.
2222
2223 DELETE /*+ index(a) */ cs_sr_contacts_ext_audit a
2224 WHERE
2225 incident_id IN
2226 (
2227 SELECT /*+ unnest no_semijoin cardinality(10) */
2228 object_id
2229 FROM
2230 jtf_object_purge_param_tmp
2231 WHERE
2232 object_type = 'SR'
2233 AND p_processing_set_id = processing_set_id
2234 AND NVL
2235 (
2236 purge_status
2237 , 'S'
2238 ) = 'S'
2239 );
2240
2241 l_row_count := SQL%ROWCOUNT;
2242
2243 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2244 THEN
2245 fnd_log.string
2246 (
2247 fnd_log.level_statement
2248 , L_LOG_MODULE || 'del_cont_ext_aud_end'
2249 , 'After deleting data from table CS_SR_CONTACTS_EXT_AUDIT '
2250 || l_row_count || ' rows'
2251 );
2252 END IF ;
2253
2254 ---
2255
2256 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2257 THEN
2258 fnd_log.string
2259 (
2260 fnd_log.level_statement
2261 , L_LOG_MODULE || 'del_cont_ext_start'
2262 , 'Deleting data from table CS_SR_CONTACTS_EXT_TL'
2263 );
2264 END IF ;
2265
2266 -- This statement deletes all the translatable extended attributes attached
2267 -- to contacts that are linked to an SR that is available in the global
2268 -- temp table with purge status NULL.
2269
2270 DELETE /*+ index(e) */ cs_sr_contacts_ext_tl e
2271 WHERE incident_id IN
2272 (
2273 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2274 object_id
2275 FROM
2276 jtf_object_purge_param_tmp
2277 WHERE
2278 object_type = 'SR'
2279 AND p_processing_set_id = processing_set_id
2280 AND NVL(purge_status, 'S') = 'S'
2281 );
2282
2283 l_row_count := SQL%ROWCOUNT;
2284
2285 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2286 THEN
2287 fnd_log.string
2288 (
2289 fnd_log.level_statement
2290 , L_LOG_MODULE || 'del_cont_ext_end'
2291 , 'After deleting data from table CS_SR_CONTACTS_EXT_TL '
2292 || l_row_count || ' rows'
2293 );
2294 END IF ;
2295
2296 ---
2297
2298 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2299 THEN
2300 fnd_log.string
2301 (
2302 fnd_log.level_statement
2303 , L_LOG_MODULE || 'del_cont_ext_start'
2304 , 'Deleting data from table CS_SR_CONTACTS_EXT'
2305 );
2306 END IF ;
2307
2308 -- This statement deletes all the translatable extended attributes attached
2309 -- to contacts that are linked to an SR that is available in the global
2310 -- temp table with purge status NULL.
2311
2312 DELETE /*+ index(e) */ cs_sr_contacts_ext e
2313 WHERE incident_id IN
2314 (
2315 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2316 object_id
2317 FROM
2318 jtf_object_purge_param_tmp
2319 WHERE
2320 object_type = 'SR'
2321 AND p_processing_set_id = processing_set_id
2322 AND NVL(purge_status, 'S') = 'S'
2323 );
2324
2325 l_row_count := SQL%ROWCOUNT;
2326
2327 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2328 THEN
2329 fnd_log.string
2330 (
2331 fnd_log.level_statement
2332 , L_LOG_MODULE || 'del_cont_ext_end'
2333 , 'After deleting data from table CS_SR_CONTACTS_EXT '
2334 || l_row_count || ' rows'
2335 );
2336 END IF ;
2337
2338 ---
2339
2340 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2341 THEN
2342 fnd_log.string
2343 (
2344 fnd_log.level_statement
2345 , L_LOG_MODULE || 'del_cont_point_aud_start'
2346 , 'Deleting data from table CS_HZ_SR_CONTACT_PNTS_AUDIT'
2347 );
2348 END IF ;
2349
2350 -- This statement deletes all the audit info attached to contacts
2351 -- that are linked to an SR that is available in the global temp table
2352 -- with purge status NULL.
2353
2354 DELETE /*+ index(a) */ cs_hz_sr_contact_pnts_audit a
2355 WHERE incident_id IN
2356 (
2357 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2358 object_id
2359 FROM
2360 jtf_object_purge_param_tmp
2361 WHERE
2362 object_type = 'SR'
2363 AND p_processing_set_id = processing_set_id
2364 AND NVL(purge_status, 'S') = 'S'
2365 );
2366
2367 l_row_count := SQL%ROWCOUNT;
2368
2369 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2370 THEN
2371 fnd_log.string
2372 (
2373 fnd_log.level_statement
2374 , L_LOG_MODULE || 'del_cont_point_aud_end'
2375 , 'After deleting data from table CS_HZ_SR_CONTACT_PNTS_AUDIT '
2376 || l_row_count || ' rows'
2377 );
2378 END IF ;
2379
2380 ---
2381
2382 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2383 THEN
2384 fnd_log.string
2385 (
2386 fnd_log.level_statement
2387 , L_LOG_MODULE || 'del_cont_point_start'
2388 , 'Deleting data from table CS_HZ_SR_CONTACT_POINTS'
2389 );
2390 END IF ;
2391
2392 -- This statement deletes all the contacts that are linked to an
2393 -- SR that is available in the global temp table with purge status NULL.
2394 -- Bug 10090362: Changed the hint in the sub query
2395
2396 DELETE /*+ index(c) */ cs_hz_sr_contact_points c
2397 WHERE incident_id IN
2398 (
2399 SELECT /*+ cardinality(jtf_object_purge_param_tmp, 10) */
2400 object_id
2401 FROM
2402 jtf_object_purge_param_tmp
2403 WHERE
2404 object_type = 'SR'
2405 AND p_processing_set_id = processing_set_id
2406 AND NVL(purge_status, 'S') = 'S'
2407 );
2408 l_row_count := SQL%ROWCOUNT;
2409
2410 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2411 THEN
2412 fnd_log.string
2413 (
2414 fnd_log.level_statement
2415 , L_LOG_MODULE || 'del_cont_point_end'
2416 , 'After deleting data from table CS_HZ_SR_CONTACT_POINTS '
2417 || l_row_count || ' rows'
2418 );
2419 END IF ;
2420
2421 ---
2422
2423 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2424 THEN
2425 fnd_log.string
2426 (
2427 fnd_log.level_procedure
2428 , L_LOG_MODULE || 'end'
2429 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
2430 || x_return_status
2431 );
2432 END IF ;
2433
2434 EXCEPTION
2435
2436 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2438
2439 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2440 THEN
2441 fnd_log.string
2442 (
2443 fnd_log.level_unexpected
2444 , L_LOG_MODULE || 'unexpected_error'
2445 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
2446 );
2447 END IF ;
2448
2449 WHEN OTHERS THEN
2450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2451 FND_MESSAGE.Set_Name('CS', 'CS_SR_CONT_DEL_FAIL');
2452 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2453 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
2454 FND_MSG_PUB.ADD;
2455
2456 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2457 THEN
2458 fnd_log.string
2459 (
2460 fnd_log.level_unexpected
2461 , L_LOG_MODULE || 'when_others'
2462 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
2463 );
2464 fnd_log.string
2465 (
2466 fnd_log.level_unexpected
2467 , L_LOG_MODULE || 'when_others'
2468 , SQLERRM
2469 );
2470 END IF ;
2471
2472 END Delete_Contacts;
2473
2474 --------------------------------------------------------------------------------
2475 -- Procedure Name : DELETE_AUDIT_RECORDS
2476 --
2477 -- Parameters (other than standard ones)
2478 -- IN
2479 -- p_object_type : Type of object for which this procedure is
2480 -- being called. (Here it will be 'SR')
2481 -- p_processing_set_id : Id that helps the API in identifying the
2482 -- set of SRs for which the child objects have
2483 -- to be deleted.
2484 --
2485 -- Description
2486 -- This procedure deletes all the audit information related to SRs that are
2487 -- present in the global temp table with purge status NULL.
2488 --
2489 -- HISTORY
2490 --
2491 ----------------+------------+--------------------------------------------------
2492 -- DATE | UPDATED BY | Change Description
2493 ----------------+------------+--------------------------------------------------
2494 -- 2-Aug_2005 | varnaray | Created
2495 -- | |
2496 ----------------+------------+--------------------------------------------------
2497 /*#
2498 * This procedure deletes all the audit information related to SRs that are
2499 * present in the global temp table with purge status NULL.
2500 * @param p_object_type Type of object for which this procedure is being called.
2501 * (Here it will be 'SR')
2502 * @param p_processing_set_id Id that helps the API in identifying the set of
2503 * SRs for which the child objects have to be deleted.
2504 * @rep:scope internal
2505 * @rep:product CS
2506 * @rep:displayname Delete Audit Requests
2507 */
2508 PROCEDURE Delete_Audit_Records
2509 (
2510 p_api_version_number IN NUMBER := 1.0
2511 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2512 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2513 , p_object_type IN VARCHAR2
2514 , p_processing_set_id IN NUMBER
2515 , x_return_status OUT NOCOPY VARCHAR2
2516 , x_msg_count OUT NOCOPY NUMBER
2517 , x_msg_data OUT NOCOPY VARCHAR2
2518 )
2519 IS
2520 --------------------------------------------------------------------------------
2521 L_API_VERSION CONSTANT NUMBER := 1.0;
2522 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_AUDIT_RECORDS';
2523 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
2524 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
2525
2526 l_row_count NUMBER;
2527
2528 BEGIN
2529 x_return_status := FND_API.G_RET_STS_SUCCESS;
2530
2531 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2532 THEN
2533 fnd_log.string
2534 (
2535 fnd_log.level_procedure
2536 , L_LOG_MODULE || 'start'
2537 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2538 );
2539 fnd_log.string
2540 (
2541 fnd_log.level_procedure
2542 , L_LOG_MODULE || 'param 1'
2543 , 'p_api_version_number:' || p_api_version_number
2544 );
2545 fnd_log.string
2546 (
2547 fnd_log.level_procedure
2548 , L_LOG_MODULE || 'param 2'
2549 , 'p_init_msg_list:' || p_init_msg_list
2550 );
2551 fnd_log.string
2552 (
2553 fnd_log.level_procedure
2554 , L_LOG_MODULE || 'param 3'
2555 , 'p_commit:' || p_commit
2556 );
2557 fnd_log.string
2558 (
2559 fnd_log.level_procedure
2560 , L_LOG_MODULE || 'param 4'
2561 , 'p_object_type:' || p_object_type
2562 );
2563 fnd_log.string
2564 (
2565 fnd_log.level_procedure
2566 , L_LOG_MODULE || 'param 5'
2567 , 'p_processing_set_id:' || p_processing_set_id
2568 );
2569 END IF ;
2570
2571 IF NOT FND_API.Compatible_API_Call
2572 (
2573 L_API_VERSION
2574 , p_api_version_number
2575 , L_API_NAME
2576 , G_PKG_NAME
2577 )
2578 THEN
2579 FND_MSG_PUB.Count_And_Get
2580 (
2581 p_count => x_msg_count
2582 , p_data => x_msg_data
2583 );
2584 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2585 END IF ;
2586
2587 IF
2588 FND_API.to_Boolean(p_init_msg_list)
2589 THEN
2590 FND_MSG_PUB.initialize;
2591 END IF ;
2592
2593 ------------------------------------------------------------------------------
2594 -- Actual Logic starts below:
2595 ------------------------------------------------------------------------------
2596
2597 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2598 THEN
2599 fnd_log.string
2600 (
2601 fnd_log.level_statement
2602 , L_LOG_MODULE || 'del_sraudit_tl_start'
2603 , 'Deleting data from table CS_INCIDENTS_AUDIT_TL'
2604 );
2605 END IF ;
2606
2607 -- Deleting rows from the audit TL table for the
2608 -- SRs in the global temp table with purge status NULL
2609 -- Bug 10090362: Changed the hint in the sub query
2610
2611 DELETE /*+ index(t) */ cs_incidents_audit_tl t
2612 WHERE incident_id IN
2613 (
2614 SELECT /*+ cardinality(JTF_OBJECT_PURGE_PARAM_TMP, 10) */
2615 object_id
2616 FROM
2617 jtf_object_purge_param_tmp
2618 WHERE
2619 object_type = 'SR'
2620 AND p_processing_set_id = processing_set_id
2621 AND NVL(purge_status, 'S') = 'S'
2622 );
2623
2624 l_row_count := SQL%ROWCOUNT;
2625
2626 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2627 THEN
2628 fnd_log.string
2629 (
2630 fnd_log.level_statement
2631 , L_LOG_MODULE || 'del_sraudit_tl_end'
2632 , 'After deleting data from table CS_INCIDENTS_AUDIT_TL '
2633 || l_row_count || ' rows'
2634 );
2635 END IF ;
2636
2637 ---
2638
2639 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2640 THEN
2641 fnd_log.string
2642 (
2643 fnd_log.level_statement
2644 , L_LOG_MODULE || 'del_sraudit_b_start'
2645 , 'Deleting data from table CS_INCIDENTS_AUDIT_B'
2646 );
2647 END IF ;
2648
2649 -- Deleting rows from the audit table for the
2650 -- SRs in the global temp table with purge status NULL
2651
2652 DELETE /*+ index(b) */ cs_incidents_audit_b b
2653 WHERE incident_id IN
2654 (
2655 SELECT /*+ unnest no_semijoin cardinality(10) */
2656 object_id
2657 FROM
2658 jtf_object_purge_param_tmp
2659 WHERE
2660 object_type = 'SR'
2661 AND p_processing_set_id = processing_set_id
2662 AND NVL(purge_status, 'S') = 'S'
2663 );
2664
2665 l_row_count := SQL%ROWCOUNT;
2666
2667 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2668 THEN
2669 fnd_log.string
2670 (
2671 fnd_log.level_statement
2672 , L_LOG_MODULE || 'del_sraudit_b_end'
2673 , 'After deleting data from table CS_INCIDENTS_AUDIT_B '
2674 || l_row_count || ' rows'
2675 );
2676 END IF ;
2677
2678 ---
2679
2680 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2681 THEN
2682 fnd_log.string
2683 (
2684 fnd_log.level_procedure
2685 , L_LOG_MODULE || 'end'
2686 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
2687 || x_return_status
2688 );
2689 END IF ;
2690
2691 EXCEPTION
2692
2693 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2694 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2695
2696 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2697 THEN
2698 fnd_log.string
2699 (
2700 fnd_log.level_unexpected
2701 , L_LOG_MODULE || 'unexpected_error'
2702 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
2703 );
2704 END IF ;
2705
2706 WHEN OTHERS THEN
2707 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2708 FND_MESSAGE.Set_Name('CS', 'CS_SR_AUDIT_DEL_FAIL');
2709 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2710 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
2711 FND_MSG_PUB.ADD;
2712
2713 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2714 THEN
2715 fnd_log.string
2716 (
2717 fnd_log.level_unexpected
2718 , L_LOG_MODULE || 'when_others'
2719 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
2720 );
2721 fnd_log.string
2722 (
2723 fnd_log.level_unexpected
2724 , L_LOG_MODULE || 'when_others'
2725 , SQLERRM
2726 );
2727 END IF ;
2728 END Delete_Audit_Records;
2729
2730 --------------------------------------------------------------------------------
2731 -- Procedure Name : DELETE_SR_ATTRIBUTES
2732 --
2733 -- Parameters (other than standard ones)
2734 -- IN
2735 -- p_object_type : Type of object for which this procedure is
2736 -- being called. (Here it will be 'SR')
2737 -- p_processing_set_id : Id that helps the API in identifying the
2738 -- set of SRs for which the child objects have
2739 -- to be deleted.
2740 --
2741 -- Description
2742 -- This procedure deletes all the extended attributes and CIC attributes
2743 -- associated with the SRs present in the global temp table with purge
2744 -- status NULL along with the audit information captured for the extended
2745 -- attributes.
2746 -- HISTORY
2747 --
2748 ----------------+------------+--------------------------------------------------
2749 -- DATE | UPDATED BY | Change Description
2750 ----------------+------------+--------------------------------------------------
2751 -- 2-Aug_2005 | varnaray | Created
2752 -- | |
2753 ----------------+------------+--------------------------------------------------
2754 /*#
2755 * This procedure deletes all the extended attributes and CIC attributes
2756 * associated with the SRs present in the global temp table with purge status
2757 * NULL along with the audit information captured for the extended attributes.
2758 * @param p_object_type Type of object for which this procedure is being called.
2759 * (Here it will be 'SR')
2760 * @param p_processing_set_id Id that helps the API in identifying the set of
2761 * SRs for which the child objects have to be deleted.
2762 * @rep:scope internal
2763 * @rep:product CS
2764 * @rep:displayname Delete Service Request Attributes
2765 */
2766 PROCEDURE Delete_Sr_Attributes
2767 (
2768 p_api_version_number IN NUMBER
2769 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2770 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2771 , p_object_type IN VARCHAR2
2772 , p_processing_set_id IN NUMBER
2773 , x_return_status OUT NOCOPY VARCHAR2
2774 , x_msg_count OUT NOCOPY NUMBER
2775 , x_msg_data OUT NOCOPY VARCHAR2
2776 )
2777 IS
2778 --------------------------------------------------------------------------------
2779 L_API_VERSION CONSTANT NUMBER := 1.0;
2780 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_SR_ATTRIBUTES';
2781 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
2782 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
2783
2784 l_row_count NUMBER;
2785
2786 BEGIN
2787 x_return_status := FND_API.G_RET_STS_SUCCESS;
2788
2789 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2790 THEN
2791 fnd_log.string
2792 (
2793 fnd_log.level_procedure
2794 , L_LOG_MODULE || 'start'
2795 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2796 );
2797 fnd_log.string
2798 (
2799 fnd_log.level_procedure
2800 , L_LOG_MODULE || 'param 1'
2801 , 'p_api_version_number:' || p_api_version_number
2802 );
2803 fnd_log.string
2804 (
2805 fnd_log.level_procedure
2806 , L_LOG_MODULE || 'param 2'
2807 , 'p_init_msg_list:' || p_init_msg_list
2808 );
2809 fnd_log.string
2810 (
2811 fnd_log.level_procedure
2812 , L_LOG_MODULE || 'param 3'
2813 , 'p_commit:' || p_commit
2814 );
2815 fnd_log.string
2816 (
2817 fnd_log.level_procedure
2818 , L_LOG_MODULE || 'param 4'
2819 , 'p_object_type:' || p_object_type
2820 );
2821 fnd_log.string
2822 (
2823 fnd_log.level_procedure
2824 , L_LOG_MODULE || 'param 5'
2825 , 'p_processing_set_id:' || p_processing_set_id
2826 );
2827 END IF ;
2828
2829 IF NOT FND_API.Compatible_API_Call
2830 (
2831 L_API_VERSION
2832 , p_api_version_number
2833 , L_API_NAME
2834 , G_PKG_NAME
2835 )
2836 THEN
2837 FND_MSG_PUB.Count_And_Get
2838 (
2839 p_count => x_msg_count
2840 , p_data => x_msg_data
2841 );
2842 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2843 END IF ;
2844
2845 IF
2846 FND_API.to_Boolean(p_init_msg_list)
2847 THEN
2848 FND_MSG_PUB.initialize;
2849 END IF ;
2850
2851 ------------------------------------------------------------------------------
2852 -- Actual Logic starts below:
2853 ------------------------------------------------------------------------------
2854
2855 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2856 THEN
2857 fnd_log.string
2858 (
2859 fnd_log.level_statement
2860 , L_LOG_MODULE || 'cug_attr_tl_del_start'
2861 , 'Deleting data from table CUG_INCIDNT_ATTR_VALS_TL'
2862 );
2863 END IF ;
2864
2865 -- The following statement deletes all the translatable CIC attributes that
2866 -- are linked to the SRs in the global temp table with purge_status NULL
2867
2868 DELETE /*+ index(t) */ cug_incidnt_attr_vals_tl t
2869 WHERE incidnt_attr_val_id IN
2870 (
2871 SELECT /*+ cardinality(jtf_object_purge_param_tmp, 10) */
2872 c.incidnt_attr_val_id
2873 FROM
2874 jtf_object_purge_param_tmp j
2875 , cug_incidnt_attr_vals_b c
2876 WHERE
2877 j.object_type = 'SR'
2878 AND p_processing_set_id = j.processing_set_id
2879 AND NVL(j.purge_status, 'S') = 'S'
2880 AND c.incident_id = j.object_id
2881 );
2882
2883 l_row_count := SQL%ROWCOUNT;
2884
2885 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2886 THEN
2887 fnd_log.string
2888 (
2889 fnd_log.level_statement
2890 , L_LOG_MODULE || 'cug_attr_tl_del_end'
2891 , 'After deleting data from table CUG_INCIDNT_ATTR_VALS_TL '
2892 || l_row_count || ' rows'
2893 );
2894 END IF ;
2895
2896 ---
2897
2898 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2899 THEN
2900 fnd_log.string
2901 (
2902 fnd_log.level_statement
2903 , L_LOG_MODULE || 'cug_attr_b_del_start'
2904 , 'Deleting data from table CUG_INCIDNT_ATTR_VALS_B'
2905 );
2906 END IF ;
2907
2908 -- The following statement deletes all the CIC attributes that are
2909 -- linked to the SRs in the global temp table with purge_status NULL
2910
2911 DELETE /*+ index(b) */ cug_incidnt_attr_vals_b b
2912 WHERE incident_id IN
2913 (
2914 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2915 object_id
2916 FROM
2917 jtf_object_purge_param_tmp
2918 WHERE
2919 object_type = 'SR'
2920 AND p_processing_set_id = processing_set_id
2921 AND NVL(purge_status, 'S') = 'S'
2922 );
2923
2924 l_row_count := SQL%ROWCOUNT;
2925
2926 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2927 THEN
2928 fnd_log.string
2929 (
2930 fnd_log.level_statement
2931 , L_LOG_MODULE || 'cug_attr_b_del_end'
2932 , 'After deleting data from table CUG_INCIDNT_ATTR_VALS_B '
2933 || l_row_count || ' rows'
2934 );
2935 END IF ;
2936
2937 ---
2938
2939 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2940 THEN
2941 fnd_log.string
2942 (
2943 fnd_log.level_statement
2944 , L_LOG_MODULE || 'sr_ext_attr_aud_del_start'
2945 , 'Deleting data from table CS_INCIDENTS_EXT_AUDIT'
2946 );
2947 END IF ;
2948
2949 -- The following statement deletes all the audit information captured for
2950 -- the translatable extended attributes linked to SRs in the global temp
2951 -- table with purge_status NULL
2952
2953 DELETE /*+ index(a) */ cs_incidents_ext_audit a
2954 WHERE audit_extension_id IN
2955 (
2956 SELECT /*+ unnest no_semijoin leading(j) use_nl(c) cardinality(10) */
2957 audit_extension_id
2958 FROM
2959 jtf_object_purge_param_tmp j
2960 , cs_incidents_ext_audit c
2961 WHERE
2962 j.object_type = 'SR'
2963 AND p_processing_set_id = j.processing_set_id
2964 AND NVL(j.purge_status, 'S') = 'S'
2965 AND c.incident_id = j.object_id
2966 );
2967
2968 l_row_count := SQL%ROWCOUNT;
2969
2970 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2971 THEN
2972 fnd_log.string
2973 (
2974 fnd_log.level_statement
2975 , L_LOG_MODULE || 'sr_ext_attr_aud_del_end'
2976 , 'After deleting data from table CS_INCIDENTS_EXT_AUDIT '
2977 || l_row_count || ' rows'
2978 );
2979 END IF ;
2980
2981 ---
2982
2983 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2984 THEN
2985 fnd_log.string
2986 (
2987 fnd_log.level_statement
2988 , L_LOG_MODULE || 'sr_ext_attr_del_start'
2989 , 'Deleting data from table CS_INCIDENTS_EXT_TL'
2990 );
2991 END IF ;
2992
2993 -- The following statement deletes all the translatable extended attributes
2994 -- that are linked to the SRs present in the global temp table with purge
2995 -- status NULL
2996
2997 DELETE /*+ index(e) */ cs_incidents_ext_tl e
2998 WHERE incident_id IN
2999 (
3000 SELECT /*+ no_unnest no_semijoin cardinality(10) */
3001 object_id
3002 FROM
3003 jtf_object_purge_param_tmp
3004 WHERE
3005 object_type = 'SR'
3006 AND p_processing_set_id = processing_set_id
3007 AND NVL(purge_status, 'S') = 'S'
3008 );
3009
3010 l_row_count := SQL%ROWCOUNT;
3011
3012 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3013 THEN
3014 fnd_log.string
3015 (
3016 fnd_log.level_statement
3017 , L_LOG_MODULE || 'sr_ext_attr_del_end'
3018 , 'After deleting data from table CS_INCIDENTS_EXT_TL '
3019 || l_row_count || ' rows'
3020 );
3021 END IF ;
3022
3023 ---
3024
3025 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3026 THEN
3027 fnd_log.string
3028 (
3029 fnd_log.level_statement
3030 , L_LOG_MODULE || 'sr_ext_attr_del_start'
3031 , 'Deleting data from table CS_INCIDENTS_EXT'
3032 );
3033 END IF ;
3034
3035 -- The following statement deletes all the translatable extended attributes
3036 -- that are linked to the SRs present in the global temp table with purge
3037 -- status NULL
3038
3039 DELETE /*+ index(e) */ cs_incidents_ext e
3040 WHERE incident_id IN
3041 (
3042 SELECT /*+ no_unnest no_semijoin cardinality(10) */
3043 object_id
3044 FROM
3045 jtf_object_purge_param_tmp
3046 WHERE
3047 object_type = 'SR'
3048 AND p_processing_set_id = processing_set_id
3049 AND NVL(purge_status, 'S') = 'S'
3050 );
3051
3052 l_row_count := SQL%ROWCOUNT;
3053
3054 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3055 THEN
3056 fnd_log.string
3057 (
3058 fnd_log.level_statement
3059 , L_LOG_MODULE || 'sr_ext_attr_del_end'
3060 , 'After deleting data from table CS_INCIDENTS_EXT '
3061 || l_row_count || ' rows'
3062 );
3063 END IF ;
3064
3065 ---
3066
3067 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3068 THEN
3069 fnd_log.string
3070 (
3071 fnd_log.level_procedure
3072 , L_LOG_MODULE || 'end'
3073 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
3074 || x_return_status
3075 );
3076 END IF ;
3077
3078 EXCEPTION
3079
3080 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3082
3083 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3084 THEN
3085 fnd_log.string
3086 (
3087 fnd_log.level_unexpected
3088 , L_LOG_MODULE || 'unexpected_error'
3089 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
3090 );
3091 END IF ;
3092
3093 WHEN OTHERS THEN
3094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3095 FND_MESSAGE.Set_Name('CS', 'CS_SR_ATTR_VAL_DEL_FAIL');
3096 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
3097 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
3098 FND_MSG_PUB.ADD;
3099
3100 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3101 THEN
3102 fnd_log.string
3103 (
3104 fnd_log.level_unexpected
3105 , L_LOG_MODULE || 'when_others'
3106 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
3107 );
3108 fnd_log.string
3109 (
3110 fnd_log.level_unexpected
3111 , L_LOG_MODULE || 'when_others'
3112 , SQLERRM
3113 );
3114 END IF ;
3115 END Delete_Sr_Attributes;
3116
3117 --------------------------------------------------------------------------------
3118 -- Procedure Name : CREATE_PURGEAUDIT_RECORDS
3119 --
3120 -- Parameters (other than standard ones)
3121 -- IN
3122 -- p_purge_set_id : Id that helps the API in identifying the
3123 -- set of SRs for which the child objects
3124 -- have to be deleted.
3125 -- p_incident_id_tbl : pl/sql table containing all the SR ids
3126 -- that have been purged successfully
3127 -- p_incident_number_tbl : pl/sql table containing all the SR
3128 -- numbers that have been purged
3129 -- successfully
3130 -- p_incident_type_id_tbl : pl/sql table containing type ids of all
3131 -- the SRs that have been purged
3132 -- successfully
3133 -- p_customer_id_tbl : pl/sql table containing customer ids of
3134 -- all the SRs that have been purged
3135 -- successfully
3136 -- p_inv_organization_id_tbl : pl/sql table containing org ids of all
3137 -- the SRs that have been purged
3138 -- successfully
3139 -- p_inventory_item_id_tbl : pl/sql table containing item ids of all
3140 -- the SRs that have been purged
3141 -- successfully
3142 -- p_customer_product_id_tbl : pl/sql table containing instance ids of
3143 -- all the SRs that have been purged
3144 -- successfully
3145 -- p_inc_creation_date_tbl : pl/sql table containing creation dates
3146 -- of all the SRs that have been purged
3147 -- successfully
3148 -- p_inc_last_update_date_tbl : pl/sql table containing last update
3149 -- dates ids of all the SRs that have been
3150 -- purged successfully
3151 -- p_incident_id_tl_tbl : pl/sql table containing SRs ids (as
3152 -- in TL table) of all the SRs that have
3153 -- been purged successfully
3154 -- p_language_tbl : pl/sql table containing language of all
3155 -- the SRs that have been purged
3156 -- successfully
3157 -- p_source_lang_tbl : pl/sql table containing source lang of
3158 -- all the SRs that have been purged
3159 -- successfully
3160 -- p_summary_tbl : pl/sql table containing summary of all
3161 -- the SRs that have been purged
3162 -- successfully
3163 --
3164 -- Description
3165 -- This procedure creates rows in the purge audit table to preserve the
3166 -- basic information related to the SRs that were purged using the SR
3167 -- purge concurrent program. It is called from the DeleteServiceRequest
3168 -- procedure with pl/sql tables containing all the data that need to
3169 -- be preserved.
3170 --
3171 -- HISTORY
3172 --
3173 ----------------+------------+--------------------------------------------------
3174 -- DATE | UPDATED BY | Change Description
3175 ----------------+------------+--------------------------------------------------
3176 -- 2-Aug_2005 | varnaray | Created
3177 -- | |
3178 ----------------+------------+--------------------------------------------------
3179 /*#
3180 * This procedure creates rows in the purge audit table to preserve the basic
3181 * information related to the SRs that were purged using the SR purge concurrent
3182 * program. It is called from the DeleteServiceRequest procedure with pl/sql
3183 * tables containing all the data that need to be preserved.
3184 * @param p_purge_set_id Id that helps the API in identifying the set of SRs
3185 * for which the child objects have to be deleted.
3186 * @param p_incident_id_tbl pl/sql table containing all the SR ids that have
3187 * been purged successfully
3188 * @param p_incident_number_tbl pl/sql table containing all the SR numbers that
3189 * have been purged successfully
3190 * @param p_incident_type_id_tbl pl/sql table containing type ids of all the
3191 * SRs that have been purged successfully
3192 * @param p_customer_id_tbl pl/sql table containing customer ids of all the
3193 * SRs that have been purged successfully
3194 * @param p_inv_organization_id_tbl pl/sql table containing org ids of all the
3195 * SRs that have been purged successfully
3196 * @param p_inventory_item_id_tbl pl/sql table containing item ids of all the
3197 * SRs that have been purged successfully
3198 * @param p_customer_product_id_tbl pl/sql table containing instance ids of
3199 * all the SRs that have been purged successfully
3200 * @param p_inc_creation_date_tbl pl/sql table containing creation dates of
3201 * all the SRs that have been purged successfully
3202 * @param p_inc_last_update_date_tbl pl/sql table containing last update
3203 * dates ids of all the SRs that have been purged successfully
3204 * @param p_incident_id_tl_tbl pl/sql table containing SRs ids (as in TL
3205 * table) of all the SRs that have been purged successfully
3206 * @param p_language_tbl pl/sql table containing language of all the SRs
3207 * that have been purged successfully
3208 * @param p_source_lang_tbl pl/sql table containing source lang of all the SRs
3209 * that have been purged successfully
3210 * @param p_summary_tbl pl/sql table containing summary of all the SRs
3211 * that have been purged successfully
3212 * @rep:scope internal
3213 * @rep:product CS
3214 * @rep:displayname Create Purge Audit Records
3215 */
3216 PROCEDURE Create_Purgeaudit_Records
3217 (
3218 p_api_version_number IN NUMBER := 1.0
3219 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3220 , p_commit IN VARCHAR2 := FND_API.G_FALSE
3221 , p_purge_set_id IN NUMBER
3222 , p_incident_id_tbl IN t_number_tbl
3223 , p_incident_number_tbl IN t_long_string_tbl
3224 , p_incident_type_id_tbl IN t_number_tbl
3225 , p_customer_id_tbl IN t_number_tbl
3226 , p_inv_organization_id_tbl IN t_number_tbl
3227 , p_inventory_item_id_tbl IN t_number_tbl
3228 , p_customer_product_id_tbl IN t_number_tbl
3229 , p_inc_creation_date_tbl IN t_date_tbl
3230 , p_inc_last_update_date_tbl IN t_date_tbl
3231 , p_incident_id_tl_tbl IN t_number_tbl
3232 , p_language_tbl IN t_string_tbl
3233 , p_source_lang_tbl IN t_string_tbl
3234 , p_summary_tbl IN t_long_string_tbl
3235 , x_return_status OUT NOCOPY VARCHAR2
3236 , x_msg_count OUT NOCOPY NUMBER
3237 , x_msg_data OUT NOCOPY VARCHAR2
3238 )
3239 IS
3240 --------------------------------------------------------------------------------
3241 L_API_VERSION CONSTANT NUMBER := 1.0;
3242 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_PURGEAUDIT_RECORDS';
3243 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
3244 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
3245
3246 l_user_id NUMBER;
3247 l_login_id NUMBER;
3248
3249 l_row_count NUMBER;
3250
3251 BEGIN
3252 x_return_status := FND_API.G_RET_STS_SUCCESS;
3253
3254 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3255 THEN
3256 fnd_log.string
3257 (
3258 fnd_log.level_procedure
3259 , L_LOG_MODULE || 'start'
3260 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
3261 );
3262 fnd_log.string
3263 (
3264 fnd_log.level_procedure
3265 , L_LOG_MODULE || 'param 1'
3266 , 'p_api_version_number:' || p_api_version_number
3267 );
3268 fnd_log.string
3269 (
3270 fnd_log.level_procedure
3271 , L_LOG_MODULE || 'param 2'
3272 , 'p_init_msg_list:' || p_init_msg_list
3273 );
3274 fnd_log.string
3275 (
3276 fnd_log.level_procedure
3277 , L_LOG_MODULE || 'param 3'
3278 , 'p_commit:' || p_commit
3279 );
3280 fnd_log.string
3281 (
3282 fnd_log.level_procedure
3283 , L_LOG_MODULE || 'param 4'
3284 , 'p_purge_set_id' || p_purge_set_id
3285 );
3286 END IF ;
3287
3288 l_user_id := fnd_global.user_id;
3289 l_login_id := fnd_global.login_id;
3290
3291 IF NOT FND_API.Compatible_API_Call
3292 (
3293 L_API_VERSION
3294 , p_api_version_number
3295 , L_API_NAME
3296 , G_PKG_NAME
3297 )
3298 THEN
3299 FND_MSG_PUB.Count_And_Get
3300 (
3301 p_count => x_msg_count
3302 , p_data => x_msg_data
3303 );
3304 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3305 END IF ;
3306
3307 IF FND_API.to_Boolean(p_init_msg_list)
3308 THEN
3309 FND_MSG_PUB.initialize;
3310 END IF ;
3311
3312 ------------------------------------------------------------------------------
3313 -- Actual Logic starts below:
3314 ------------------------------------------------------------------------------
3315
3316 IF p_incident_id_tbl.COUNT > 0
3317 THEN
3318 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3319 THEN
3320 fnd_log.string
3321 (
3322 fnd_log.level_statement
3323 , L_LOG_MODULE || 'ins_purge_aud_b_start'
3324 , 'Inserting data into table CS_INCIDENTS_PURGE_AUDIT_B'
3325 );
3326 END IF ;
3327
3328 -- Inserting information relating to purged SRs into the audit base table
3329
3330 FORALL j IN p_incident_id_tbl.FIRST..p_incident_id_tbl.LAST
3331 INSERT INTO cs_incidents_purge_audit_b
3332 (
3333 purge_id
3334 , incident_id
3335 , incident_number
3336 , incident_type_id
3337 , customer_id
3338 , inv_organization_id
3339 , inventory_item_id
3340 , customer_product_id
3341 , inc_creation_date
3342 , inc_last_update_date
3343 , purged_date
3344 , purged_by
3345 , creation_date
3346 , created_by
3347 , last_update_date
3348 , last_updated_by
3349 , last_update_login
3350 )
3351 VALUES
3352 (
3353 p_purge_set_id
3354 , p_incident_id_tbl(j)
3355 , p_incident_number_tbl(j)
3356 , p_incident_type_id_tbl(j)
3357 , p_customer_id_tbl(j)
3358 , p_inv_organization_id_tbl(j)
3359 , p_inventory_item_id_tbl(j)
3360 , p_customer_product_id_tbl(j)
3361 , p_inc_creation_date_tbl(j)
3362 , p_inc_last_update_date_tbl(j)
3363 , SYSDATE
3364 , l_user_id
3365 , SYSDATE
3366 , l_user_id
3367 , SYSDATE
3368 , l_user_id
3369 , l_login_id
3370 );
3371
3372 l_row_count := SQL%ROWCOUNT;
3373
3374 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3375 THEN
3376 fnd_log.string
3377 (
3378 fnd_log.level_statement
3379 , L_LOG_MODULE || 'ins_purge_aud_b_end'
3380 , 'After inserting data into table CS_INCIDENTS_PURGE_AUDIT_B '
3381 || l_row_count || ' rows'
3382 );
3383 END IF ;
3384 ELSIF p_incident_id_tbl.COUNT <= 0
3385 THEN
3386 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3387 THEN
3388 fnd_log.string
3389 (
3390 fnd_log.level_statement
3391 , L_LOG_MODULE || 'ins_purge_aud_b_err'
3392 , 'While inserting data into table CS_INCIDENTS_PURGE_AUDIT_B '
3393 || 'p_incident_id_tbl had ' || p_incident_id_tbl.COUNT || ' rows'
3394 );
3395 END IF ;
3396 END IF;
3397
3398 IF p_incident_id_tl_tbl.COUNT > 0
3399 THEN
3400 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3401 THEN
3402 fnd_log.string
3403 (
3404 fnd_log.level_statement
3405 , L_LOG_MODULE || 'ins_purge_aud_tl_start'
3406 , 'Inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL'
3407 );
3408 END IF ;
3409
3410 -- Inserting translatable information relating to
3411 -- purged SRs into the audit base table
3412
3413 FORALL j IN p_incident_id_tl_tbl.FIRST..p_incident_id_tl_tbl.LAST
3414 INSERT INTO cs_incidents_purge_audit_tl
3415 (
3416 purge_id
3417 , incident_id
3418 , language
3419 , source_lang
3420 , summary
3421 , creation_date
3422 , created_by
3423 , last_update_date
3424 , last_updated_by
3425 , last_update_login
3426 )
3427 VALUES
3428 (
3429 p_purge_set_id
3430 , p_incident_id_tl_tbl(j)
3431 , p_language_tbl(j)
3432 , p_source_lang_tbl(j)
3433 , p_summary_tbl(j)
3434 , SYSDATE
3435 , l_user_id
3436 , SYSDATE
3437 , l_user_id
3438 , l_login_id
3439 );
3440
3441 l_row_count := SQL%ROWCOUNT;
3442
3443 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3444 THEN
3445 fnd_log.string
3446 (
3447 fnd_log.level_statement
3448 , L_LOG_MODULE || 'ins_purge_aud_tl_end'
3449 , 'After inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL '
3450 || l_row_count || ' rows'
3451 );
3452 END IF ;
3453 ELSIF p_incident_id_tl_tbl.COUNT <= 0
3454 THEN
3455 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3456 THEN
3457 fnd_log.string
3458 (
3459 fnd_log.level_statement
3460 , L_LOG_MODULE || 'ins_purge_aud_tl_err'
3461 , 'While inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL '
3462 || 'p_incident_id_tl_tbl had ' || p_incident_id_tl_tbl.COUNT || ' rows'
3463 );
3464 END IF ;
3465 END IF;
3466
3467 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3468 THEN
3469 fnd_log.string
3470 (
3471 fnd_log.level_procedure
3472 , L_LOG_MODULE || 'end'
3473 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
3474 || x_return_status
3475 );
3476 END IF ;
3477
3478 EXCEPTION
3479
3480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3482
3483 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3484 THEN
3485 fnd_log.string
3486 (
3487 fnd_log.level_unexpected
3488 , L_LOG_MODULE || 'unexpected_error'
3489 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
3490 );
3491 END IF ;
3492
3493 WHEN OTHERS THEN
3494 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3495 FND_MESSAGE.Set_Name('CS', 'CS_SR_PRG_CRT_FAIL');
3496 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
3497 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
3498 FND_MSG_PUB.ADD;
3499
3500 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3501 THEN
3502 fnd_log.string
3503 (
3504 fnd_log.level_unexpected
3505 , L_LOG_MODULE || 'when_others'
3506 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
3507 );
3508 fnd_log.string
3509 (
3510 fnd_log.level_unexpected
3511 , L_LOG_MODULE || 'when_others'
3512 , SQLERRM
3513 );
3514 END IF ;
3515 END Create_Purgeaudit_Records;
3516 --------------------------------------------------------------------------------
3517 -- Procedure Name : CHECK_USER_TERMINATION
3518 --
3519 -- Parameters (other than standard ones)
3520 -- NONE.
3521 --
3522 -- Description
3523 -- This procedure is called before performing any step during the purge
3524 -- process so that if the user requests a termination of the purge process
3525 -- for some reason, the process should end gracefully.
3526 --
3527 -- HISTORY
3528 --
3529 ----------------+------------+--------------------------------------------------
3530 -- DATE | UPDATED BY | Change Description
3531 ----------------+------------+--------------------------------------------------
3532 -- 2-Aug_2005 | varnaray | Created
3533 -- | |
3534 ----------------+------------+--------------------------------------------------
3535 /*#
3536 * This procedure is called before performing any step during the purge process
3537 * so that if the user requests a termination of the purge process for some
3538 * reason, the process should end gracefully.
3539 */
3540 PROCEDURE Check_User_Termination
3541 IS
3542 --------------------------------------------------------------------------------
3543 L_API_VERSION CONSTANT NUMBER := 1.0;
3544 L_API_NAME CONSTANT VARCHAR2(30) := 'CHECK_USER_TERMINATION';
3545 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
3546 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
3547
3548 l_request_id NUMBER;
3549 l_phase VARCHAR2(100);
3550 l_status VARCHAR2(100);
3551 l_dev_phase VARCHAR2(100);
3552 l_dev_status VARCHAR2(100);
3553 l_message VARCHAR2(500);
3554
3555 BEGIN
3556 l_request_id := fnd_global.conc_request_id;
3557
3558 IF l_request_id <> -1
3559
3560 -- The check for user termination is only
3561 -- required if the SR Delete Helper is called
3562 -- from a concurrent program. If l_request_id
3563 -- is -1, it means that the procedure is not
3564 -- called from a concurrent program. Hence the
3565 -- check for user termination may not be required.
3566
3567 THEN
3568 IF fnd_concurrent.get_request_status
3569 (
3570 request_id => l_request_id
3571 , phase => l_phase
3572 , status => l_status
3573 , dev_phase => l_dev_phase
3574 , dev_status => l_dev_status
3575 , message => l_message
3576 )
3577 THEN
3578 IF l_dev_status = 'TERMINATING'
3579 AND l_dev_phase = 'RUNNING'
3580 OR l_dev_status = 'TERMINATED'
3581 AND l_dev_phase = 'COMPLETE'
3582
3583 -- If the user terminates the concurrent request
3584 -- raise an exception and add a message to the stack
3585
3586 THEN
3587 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3588 THEN
3589 fnd_log.string
3590 (
3591 fnd_log.level_statement
3592 , L_LOG_MODULE || 'conc_req_user_stop'
3593 , 'This concurrent request is in status ' || l_dev_status
3594 || ' and phase ' || l_dev_phase
3595 );
3596 END IF ;
3597
3598 FND_MESSAGE.Set_Name('CS', 'CS_SR_USER_STOPPED');
3599 FND_MSG_PUB.ADD;
3600
3601 -- Setting the request_data to 'T'
3602 -- indicating that the request has
3603 -- been terminated by the user
3604
3605 fnd_conc_global.set_req_globals
3606 (
3607 request_data => 'T'
3608 );
3609
3610 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3611 END IF;
3612 ELSE
3613 -- if fnd_concurrent.get_request_status failed...
3614
3615 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3616 THEN
3617 fnd_log.string
3618 (
3619 fnd_log.level_unexpected
3620 , L_LOG_MODULE || 'conc_req_status_fail'
3621 , 'Failed while getting the status of this request'
3622 );
3623 END IF ;
3624 END IF;
3625 END IF;
3626 END Check_User_Termination;
3627 --------------------------------------------------------------------------------
3628 END cs_sr_delete_util;
[Home] [Help]