1 PACKAGE BODY cs_sr_delete_util AS
2 /* $Header: csvsrdlb.pls 120.10 2008/01/24 10:51:53 nveerara 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
1727 DELETE /*+ index(t) */ cs_incidents_all_tl t
1728 WHERE
1729 incident_id IN
1730 (
1731 SELECT /*+ no_unnest no_semijoin cardinality(10) */
1732 object_id
1733 FROM
1734 jtf_object_purge_param_tmp
1735 WHERE
1736 object_type = 'SR'
1737 AND p_processing_set_id = processing_set_id
1738 AND NVL
1739 (
1740 purge_status
1741 , 'S'
1742 ) = 'S'
1743 );
1744
1745 l_row_count := SQL%ROWCOUNT;
1746
1747 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1748 THEN
1749 fnd_log.string
1750 (
1751 fnd_log.level_statement
1752 , L_LOG_MODULE || 'del_srtl_end'
1753 , 'After deleting rows from table CS_INCIDENTS_ALL_TL ' || l_row_count
1754 || ' rows'
1755 );
1756 END IF ;
1757 END IF;
1758
1759 ---
1760
1761 Check_User_Termination;
1762
1763 ---
1764
1765 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1766 THEN
1767 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1768 THEN
1769 fnd_log.string
1770 (
1771 fnd_log.level_statement
1772 , L_LOG_MODULE || 'del_srb_start'
1773 , 'Deleting rows from table CS_INCIDENTS_ALL_B'
1774 );
1775 END IF ;
1776
1777 -- Delete all the attributes of the
1778 -- service request from the table
1779
1780 DELETE /*+ index(b) */ cs_incidents_all_b b
1781 WHERE
1782 incident_id IN
1783 (
1784 SELECT /*+ no_unnest no_semijoin cardinality(10) */
1785 object_id
1786 FROM
1787 jtf_object_purge_param_tmp
1788 WHERE
1789 object_type = 'SR'
1790 AND p_processing_set_id = processing_set_id
1791 AND NVL
1792 (
1793 purge_status
1794 , 'S'
1795 ) = 'S'
1796 );
1797
1798 l_row_count := SQL%ROWCOUNT;
1799
1800 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1801 THEN
1802 fnd_log.string
1803 (
1804 fnd_log.level_statement
1805 , L_LOG_MODULE || 'del_srb_end'
1806 , 'After deleting rows from table CS_INCIDENTS_ALL_B ' || l_row_count
1807 || ' rows'
1808 );
1809 END IF ;
1810 END IF;
1811
1812 ---
1813
1814 Check_User_Termination;
1815
1816 ---
1817
1818 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1819 THEN
1820 IF l_incident_id_status_tbl.COUNT > 0
1821
1822 -- If there are any SRs with purge_status E indicating
1823 -- error while validation, the status of these SRs is
1824 -- updated back to the Staging Table to facilitate
1825 -- generation of concurrent request output file.
1826
1827 THEN
1828 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1829 THEN
1830 fnd_log.string
1831 (
1832 fnd_log.level_statement
1833 , L_LOG_MODULE || 'write_staging_start'
1834 , 'Updating purge status into table CS_INCIDENTS_PURGE_STAGING'
1835 );
1836 END IF ;
1837
1838 -- Updating the staging table to indicate the SRs that failed during purge
1839 -- due to validations against child objects.
1840
1841 FORALL j IN l_incident_id_status_tbl.FIRST..l_incident_id_status_tbl.LAST
1842 UPDATE cs_incidents_purge_staging
1843 SET
1844 purge_status = 'E'
1845 , purge_error_message = l_purge_error_message_tbl(j)
1846 WHERE
1847 incident_id = l_incident_id_status_tbl(j);
1848
1849 l_row_count := SQL%ROWCOUNT;
1850
1851 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1852 THEN
1853 fnd_log.string
1854 (
1855 fnd_log.level_statement
1856 , L_LOG_MODULE || 'write_staging_end'
1857 , 'After updating purge status into table CS_INCIDENTS_PURGE_STAGING '
1858 || l_row_count || ' rows'
1859 );
1860 END IF ;
1861 ELSIF l_incident_id_status_tbl.COUNT <= 0
1862 THEN
1863 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1864 THEN
1865 fnd_log.string
1866 (
1867 fnd_log.level_statement
1868 , L_LOG_MODULE || 'write_staging_norows'
1869 , 'While updating purge status into table CS_INCIDENTS_PURGE_STAGING '
1870 || 'l_incident_id_status_tbl had ' || l_incident_id_status_tbl.COUNT
1871 || ' rows'
1872 );
1873 END IF ;
1874 END IF;
1875 END IF;
1876
1877 ---
1878
1879 Check_User_Termination;
1880
1881 ---
1882
1883 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1884 THEN
1885 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1886 THEN
1887 fnd_log.string
1888 (
1889 fnd_log.level_statement
1890 , L_LOG_MODULE || 'write_staging_success_start'
1891 , 'Updating rows processed successfully with purge status = S'
1892 );
1893 END IF ;
1894
1895 -- Setting all the rows in the staging table
1896 -- belonging to this batch which were processed
1897 -- successfully to purge_status 'S' to be able
1898 -- to differentiate these rows from the ones
1899 -- that were not yet processed.
1900
1901 UPDATE cs_incidents_purge_staging
1902 SET
1903 purge_status = 'S'
1904 WHERE
1905 incident_id IN
1906 (
1907 SELECT
1908 object_id
1909 FROM
1910 jtf_object_purge_param_tmp j
1911 WHERE
1912 j.object_type = 'SR'
1913 AND NVL
1914 (
1915 j.purge_status
1916 , 'S'
1917 ) = 'S'
1918 AND j.processing_set_id = p_processing_set_id
1919 );
1920 l_row_count := SQL%ROWCOUNT;
1921
1922 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1923 THEN
1924 fnd_log.string
1925 (
1926 fnd_log.level_statement
1927 , L_LOG_MODULE || 'write_staging_success_end'
1928 , 'After updating rows processed successfully with '
1929 || 'purge status = S ' || l_row_count
1930 );
1931 END IF ;
1932 END IF;
1933
1934 ---
1935
1936 Check_User_Termination;
1937
1938 ---
1939
1940 IF x_return_status = FND_API.G_RET_STS_SUCCESS
1941 AND p_audit_required = 'Y'
1942
1943 -- If audit information is required as per profile option
1944 -- CS_SR_PURGE_AUDIT_REQUIRED, then proceed further
1945
1946 THEN
1947 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1948 THEN
1949 fnd_log.string
1950 (
1951 fnd_log.level_statement
1952 , L_LOG_MODULE || 'purge_audit_create_start'
1953 , 'Calling the procedure CREATE_PURGEAUDIT_RECORDS to create purge '
1954 || 'audit information'
1955 );
1956 END IF ;
1957
1958 -- This procedure takes as input all the data that needs to be captured
1959 -- in the purge audit tables and staging table. Data of all SRs that were
1960 -- successfully purged are entered in the audit tables. For the SRs for
1961 -- which purge failed, the staging table is updated with the purge error
1962 -- message and purge status.
1963
1964 Create_Purgeaudit_Records
1965 (
1966 p_api_version_number => '1.0'
1967 , p_init_msg_list => FND_API.G_FALSE
1968 , p_commit => FND_API.G_FALSE
1969 , p_purge_set_id => l_purge_set_id
1970 , p_incident_id_tbl => l_incident_id_tbl
1971 , p_incident_number_tbl => l_incident_number_tbl
1972 , p_incident_type_id_tbl => l_incident_type_id_tbl
1973 , p_customer_id_tbl => l_customer_id_tbl
1974 , p_inv_organization_id_tbl => l_inv_organization_id_tbl
1975 , p_inventory_item_id_tbl => l_inventory_item_id_tbl
1976 , p_customer_product_id_tbl => l_customer_product_id_tbl
1977 , p_inc_creation_date_tbl => l_inc_creation_date_tbl
1978 , p_inc_last_update_date_tbl => l_inc_last_update_date_tbl
1979 , p_incident_id_tl_tbl => l_incident_id_tl_tbl
1980 , p_language_tbl => l_language_tbl
1981 , p_source_lang_tbl => l_source_lang_tbl
1982 , p_summary_tbl => l_summary_tbl
1983 , x_return_status => x_return_status
1984 , x_msg_count => x_msg_count
1985 , x_msg_data => x_msg_data
1986 );
1987
1988 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
1989 THEN
1990 fnd_log.string
1991 (
1992 fnd_log.level_statement
1993 , L_LOG_MODULE || 'purge_audit_create_end'
1994 , 'After calling the procedure CREATE_PURGEAUDIT_RECORDS. '
1995 || 'Returned with status ' || x_return_status
1996 );
1997 END IF ;
1998 END IF;
1999
2000 ---
2001
2002 Check_User_Termination;
2003
2004 ---
2005
2006 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2007 THEN
2008 fnd_log.string
2009 (
2010 fnd_log.level_procedure
2011 , L_LOG_MODULE || 'end'
2012 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
2013 || x_return_status
2014 );
2015 END IF ;
2016
2017 EXCEPTION
2018
2019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2020 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2021
2022 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2023 THEN
2024 fnd_log.string
2025 (
2026 fnd_log.level_unexpected
2027 , L_LOG_MODULE || 'unexpected_error'
2028 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
2029 );
2030
2031 x_msg_count := FND_MSG_PUB.Count_Msg;
2032
2033 IF x_msg_count > 0
2034 THEN
2035 FOR
2036 i IN 1..x_msg_count
2037 LOOP
2038 FND_MSG_PUB.Get
2039 (
2040 p_msg_index => i
2041 , p_encoded => 'F'
2042 , p_data => x_msg_data
2043 , p_msg_index_out => x_msg_index_out
2044 );
2045 fnd_log.string
2046 (
2047 fnd_log.level_unexpected
2048 , L_LOG_MODULE || 'unexpected_error'
2049 , 'Error encountered is : ' || x_msg_data || ' [Index:'
2050 || x_msg_index_out || ']'
2051 );
2052 END LOOP;
2053 END IF ;
2054 END IF ;
2055
2056 WHEN OTHERS THEN
2057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2058 FND_MESSAGE.Set_Name('CS', 'CS_SR_DEL_FAIL');
2059 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2060 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
2061 FND_MSG_PUB.ADD;
2062
2063 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2064 THEN
2065 fnd_log.string
2066 (
2067 fnd_log.level_unexpected
2068 , L_LOG_MODULE || 'when_others'
2069 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
2070 );
2071 fnd_log.string
2072 (
2073 fnd_log.level_unexpected
2074 , L_LOG_MODULE || 'when_others'
2075 , SQLERRM
2076 );
2077 END IF ;
2078
2079 END Delete_ServiceRequest;
2080
2081 --------------------------------------------------------------------------------
2082 -- Procedure Name : DELETE_CONTACTS
2083 --
2084 -- Parameters (other than standard ones)
2085 -- IN
2086 -- p_object_type : Type of object for which this procedure is
2087 -- being called. (Here it will be 'SR')
2088 -- p_processing_set_id : Id that helps the API in identifying the
2089 -- set of SRs for which the child objects have
2090 -- to be deleted.
2091 --
2092 -- Description
2093 -- This procedure delets all the contacts, related to an SR present in the
2094 -- global temp table with purge status NULL along with contact audit
2095 -- information, party role extended attributes and party role extended
2096 -- attribute audit information
2097 -- HISTORY
2098 --
2099 ----------------+------------+--------------------------------------------------
2100 -- DATE | UPDATED BY | Change Description
2101 ----------------+------------+--------------------------------------------------
2102 -- 2-Aug_2005 | varnaray | Created
2103 -- | |
2104 ----------------+------------+--------------------------------------------------
2105 /*#
2106 * This procedure delets all the contacts, related to an SR present in the
2107 * global temp table with purge status NULL along with contact audit
2108 * information, party role extended attributes and party role extended
2109 * attribute audit information
2110 * @param p_object_type Type of object for which this procedure is being called.
2111 * (Here it will be 'SR')
2112 * @param p_processing_set_id Id that helps the API in identifying the set of
2113 * SRs for which the child objects have to be deleted.
2114 * @rep:scope internal
2115 * @rep:product CS
2116 * @rep:displayname Delete Contacts
2117 */
2118 PROCEDURE Delete_Contacts
2119 (
2120 p_api_version_number IN NUMBER := 1.0
2121 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2122 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2123 , p_object_type IN VARCHAR2
2124 , p_processing_set_id IN NUMBER
2125 , x_return_status OUT NOCOPY VARCHAR2
2126 , x_msg_count OUT NOCOPY NUMBER
2127 , x_msg_data OUT NOCOPY VARCHAR2
2128 )
2129 IS
2130 --------------------------------------------------------------------------------
2131 L_API_VERSION CONSTANT NUMBER := 1.0;
2132 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_CONTACTS';
2133 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
2134 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
2135
2136 l_row_count NUMBER := 0;
2137
2138 BEGIN
2139 x_return_status := FND_API.G_RET_STS_SUCCESS;
2140
2141 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2142 THEN
2143 fnd_log.string
2144 (
2145 fnd_log.level_procedure
2146 , L_LOG_MODULE || 'start'
2147 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2148 );
2149 fnd_log.string
2150 (
2151 fnd_log.level_procedure
2152 , L_LOG_MODULE || 'param 1'
2153 , 'p_api_version_number:' || p_api_version_number
2154 );
2155 fnd_log.string
2156 (
2157 fnd_log.level_procedure
2158 , L_LOG_MODULE || 'param 2'
2159 , 'p_init_msg_list:' || p_init_msg_list
2160 );
2161 fnd_log.string
2162 (
2163 fnd_log.level_procedure
2164 , L_LOG_MODULE || 'param 3'
2165 , 'p_commit:' || p_commit
2166 );
2167 fnd_log.string
2168 (
2169 fnd_log.level_procedure
2170 , L_LOG_MODULE || 'param 4'
2171 , 'p_object_type:' || p_object_type
2172 );
2173 fnd_log.string
2174 (
2175 fnd_log.level_procedure
2176 , L_LOG_MODULE || 'param 5'
2177 , 'p_processing_set_id:' || p_processing_set_id
2178 );
2179 END IF ;
2180
2181 IF NOT FND_API.Compatible_API_Call
2182 (
2183 L_API_VERSION
2184 , p_api_version_number
2185 , L_API_NAME
2186 , G_PKG_NAME
2187 )
2188 THEN
2189 FND_MSG_PUB.Count_And_Get
2190 (
2191 p_count => x_msg_count
2192 , p_data => x_msg_data
2193 );
2194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2195 END IF ;
2196
2197 IF
2198 FND_API.to_Boolean(p_init_msg_list)
2199 THEN
2200 FND_MSG_PUB.initialize;
2201 END IF ;
2202
2203 ------------------------------------------------------------------------------
2204 -- Actual Logic starts below:
2205 ------------------------------------------------------------------------------
2206
2207 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2208 THEN
2209 fnd_log.string
2210 (
2211 fnd_log.level_statement
2212 , L_LOG_MODULE || 'del_cont_ext_aud_start'
2213 , 'Deleting data from table CS_SR_CONTACTS_EXT_AUDIT'
2214 );
2215 END IF ;
2216
2217 -- The following statement deletes the audit information captured for
2218 -- the translatable Party role extended attributes related to SRs in the
2219 -- global temp table with purge status NULL.
2220
2221 DELETE /*+ index(a) */ cs_sr_contacts_ext_audit a
2222 WHERE
2223 incident_id IN
2224 (
2225 SELECT /*+ unnest no_semijoin cardinality(10) */
2226 object_id
2227 FROM
2228 jtf_object_purge_param_tmp
2229 WHERE
2230 object_type = 'SR'
2231 AND p_processing_set_id = processing_set_id
2232 AND NVL
2233 (
2234 purge_status
2235 , 'S'
2236 ) = 'S'
2237 );
2238
2239 l_row_count := SQL%ROWCOUNT;
2240
2241 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2242 THEN
2243 fnd_log.string
2244 (
2245 fnd_log.level_statement
2246 , L_LOG_MODULE || 'del_cont_ext_aud_end'
2247 , 'After deleting data from table CS_SR_CONTACTS_EXT_AUDIT '
2248 || l_row_count || ' rows'
2249 );
2250 END IF ;
2251
2252 ---
2253
2254 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2255 THEN
2256 fnd_log.string
2257 (
2258 fnd_log.level_statement
2259 , L_LOG_MODULE || 'del_cont_ext_start'
2260 , 'Deleting data from table CS_SR_CONTACTS_EXT_TL'
2261 );
2262 END IF ;
2263
2264 -- This statement deletes all the translatable extended attributes attached
2265 -- to contacts that are linked to an SR that is available in the global
2266 -- temp table with purge status NULL.
2267
2268 DELETE /*+ index(e) */ cs_sr_contacts_ext_tl e
2269 WHERE incident_id IN
2270 (
2271 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2272 object_id
2273 FROM
2274 jtf_object_purge_param_tmp
2275 WHERE
2276 object_type = 'SR'
2277 AND p_processing_set_id = processing_set_id
2278 AND NVL(purge_status, 'S') = 'S'
2279 );
2280
2281 l_row_count := SQL%ROWCOUNT;
2282
2283 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2284 THEN
2285 fnd_log.string
2286 (
2287 fnd_log.level_statement
2288 , L_LOG_MODULE || 'del_cont_ext_end'
2289 , 'After deleting data from table CS_SR_CONTACTS_EXT_TL '
2290 || l_row_count || ' rows'
2291 );
2292 END IF ;
2293
2294 ---
2295
2296 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2297 THEN
2298 fnd_log.string
2299 (
2300 fnd_log.level_statement
2301 , L_LOG_MODULE || 'del_cont_ext_start'
2302 , 'Deleting data from table CS_SR_CONTACTS_EXT'
2303 );
2304 END IF ;
2305
2306 -- This statement deletes all the translatable extended attributes attached
2307 -- to contacts that are linked to an SR that is available in the global
2308 -- temp table with purge status NULL.
2309
2310 DELETE /*+ index(e) */ cs_sr_contacts_ext e
2311 WHERE incident_id IN
2312 (
2313 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2314 object_id
2315 FROM
2316 jtf_object_purge_param_tmp
2317 WHERE
2318 object_type = 'SR'
2319 AND p_processing_set_id = processing_set_id
2320 AND NVL(purge_status, 'S') = 'S'
2321 );
2322
2323 l_row_count := SQL%ROWCOUNT;
2324
2325 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2326 THEN
2327 fnd_log.string
2328 (
2329 fnd_log.level_statement
2330 , L_LOG_MODULE || 'del_cont_ext_end'
2331 , 'After deleting data from table CS_SR_CONTACTS_EXT '
2332 || l_row_count || ' rows'
2333 );
2334 END IF ;
2335
2336 ---
2337
2338 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2339 THEN
2340 fnd_log.string
2341 (
2342 fnd_log.level_statement
2343 , L_LOG_MODULE || 'del_cont_point_aud_start'
2344 , 'Deleting data from table CS_HZ_SR_CONTACT_PNTS_AUDIT'
2345 );
2346 END IF ;
2347
2348 -- This statement deletes all the audit info attached to contacts
2349 -- that are linked to an SR that is available in the global temp table
2350 -- with purge status NULL.
2351
2352 DELETE /*+ index(a) */ cs_hz_sr_contact_pnts_audit a
2353 WHERE incident_id IN
2354 (
2355 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2356 object_id
2357 FROM
2358 jtf_object_purge_param_tmp
2359 WHERE
2360 object_type = 'SR'
2361 AND p_processing_set_id = processing_set_id
2362 AND NVL(purge_status, 'S') = 'S'
2363 );
2364
2365 l_row_count := SQL%ROWCOUNT;
2366
2367 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2368 THEN
2369 fnd_log.string
2370 (
2371 fnd_log.level_statement
2372 , L_LOG_MODULE || 'del_cont_point_aud_end'
2373 , 'After deleting data from table CS_HZ_SR_CONTACT_PNTS_AUDIT '
2374 || l_row_count || ' rows'
2375 );
2376 END IF ;
2377
2378 ---
2379
2380 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2381 THEN
2382 fnd_log.string
2383 (
2384 fnd_log.level_statement
2385 , L_LOG_MODULE || 'del_cont_point_start'
2386 , 'Deleting data from table CS_HZ_SR_CONTACT_POINTS'
2387 );
2388 END IF ;
2389
2390 -- This statement deletes all the contacts that are linked to an
2391 -- SR that is available in the global temp table with purge status NULL.
2392
2393 DELETE /*+ index(c) */ cs_hz_sr_contact_points c
2394 WHERE incident_id IN
2395 (
2396 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2397 object_id
2398 FROM
2399 jtf_object_purge_param_tmp
2400 WHERE
2401 object_type = 'SR'
2402 AND p_processing_set_id = processing_set_id
2403 AND NVL(purge_status, 'S') = 'S'
2404 );
2405 l_row_count := SQL%ROWCOUNT;
2406
2407 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2408 THEN
2409 fnd_log.string
2410 (
2411 fnd_log.level_statement
2412 , L_LOG_MODULE || 'del_cont_point_end'
2413 , 'After deleting data from table CS_HZ_SR_CONTACT_POINTS '
2414 || l_row_count || ' rows'
2415 );
2416 END IF ;
2417
2418 ---
2419
2420 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2421 THEN
2422 fnd_log.string
2423 (
2424 fnd_log.level_procedure
2425 , L_LOG_MODULE || 'end'
2426 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
2427 || x_return_status
2428 );
2429 END IF ;
2430
2431 EXCEPTION
2432
2433 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2434 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2435
2436 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2437 THEN
2438 fnd_log.string
2439 (
2440 fnd_log.level_unexpected
2441 , L_LOG_MODULE || 'unexpected_error'
2442 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
2443 );
2444 END IF ;
2445
2446 WHEN OTHERS THEN
2447 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2448 FND_MESSAGE.Set_Name('CS', 'CS_SR_CONT_DEL_FAIL');
2449 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2450 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
2451 FND_MSG_PUB.ADD;
2452
2453 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2454 THEN
2455 fnd_log.string
2456 (
2457 fnd_log.level_unexpected
2458 , L_LOG_MODULE || 'when_others'
2459 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
2460 );
2461 fnd_log.string
2462 (
2463 fnd_log.level_unexpected
2464 , L_LOG_MODULE || 'when_others'
2465 , SQLERRM
2466 );
2467 END IF ;
2468
2469 END Delete_Contacts;
2470
2471 --------------------------------------------------------------------------------
2472 -- Procedure Name : DELETE_AUDIT_RECORDS
2473 --
2474 -- Parameters (other than standard ones)
2475 -- IN
2476 -- p_object_type : Type of object for which this procedure is
2477 -- being called. (Here it will be 'SR')
2478 -- p_processing_set_id : Id that helps the API in identifying the
2479 -- set of SRs for which the child objects have
2480 -- to be deleted.
2481 --
2482 -- Description
2483 -- This procedure deletes all the audit information related to SRs that are
2484 -- present in the global temp table with purge status NULL.
2485 --
2486 -- HISTORY
2487 --
2488 ----------------+------------+--------------------------------------------------
2489 -- DATE | UPDATED BY | Change Description
2490 ----------------+------------+--------------------------------------------------
2491 -- 2-Aug_2005 | varnaray | Created
2492 -- | |
2493 ----------------+------------+--------------------------------------------------
2494 /*#
2495 * This procedure deletes all the audit information related to SRs that are
2496 * present in the global temp table with purge status NULL.
2497 * @param p_object_type Type of object for which this procedure is being called.
2498 * (Here it will be 'SR')
2499 * @param p_processing_set_id Id that helps the API in identifying the set of
2500 * SRs for which the child objects have to be deleted.
2501 * @rep:scope internal
2502 * @rep:product CS
2503 * @rep:displayname Delete Audit Requests
2504 */
2505 PROCEDURE Delete_Audit_Records
2506 (
2507 p_api_version_number IN NUMBER := 1.0
2508 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2509 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2510 , p_object_type IN VARCHAR2
2511 , p_processing_set_id IN NUMBER
2512 , x_return_status OUT NOCOPY VARCHAR2
2513 , x_msg_count OUT NOCOPY NUMBER
2514 , x_msg_data OUT NOCOPY VARCHAR2
2515 )
2516 IS
2517 --------------------------------------------------------------------------------
2518 L_API_VERSION CONSTANT NUMBER := 1.0;
2519 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_AUDIT_RECORDS';
2520 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
2521 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
2522
2523 l_row_count NUMBER;
2524
2525 BEGIN
2526 x_return_status := FND_API.G_RET_STS_SUCCESS;
2527
2528 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2529 THEN
2530 fnd_log.string
2531 (
2532 fnd_log.level_procedure
2533 , L_LOG_MODULE || 'start'
2534 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2535 );
2536 fnd_log.string
2537 (
2538 fnd_log.level_procedure
2539 , L_LOG_MODULE || 'param 1'
2540 , 'p_api_version_number:' || p_api_version_number
2541 );
2542 fnd_log.string
2543 (
2544 fnd_log.level_procedure
2545 , L_LOG_MODULE || 'param 2'
2546 , 'p_init_msg_list:' || p_init_msg_list
2547 );
2548 fnd_log.string
2549 (
2550 fnd_log.level_procedure
2551 , L_LOG_MODULE || 'param 3'
2552 , 'p_commit:' || p_commit
2553 );
2554 fnd_log.string
2555 (
2556 fnd_log.level_procedure
2557 , L_LOG_MODULE || 'param 4'
2558 , 'p_object_type:' || p_object_type
2559 );
2560 fnd_log.string
2561 (
2562 fnd_log.level_procedure
2563 , L_LOG_MODULE || 'param 5'
2564 , 'p_processing_set_id:' || p_processing_set_id
2565 );
2566 END IF ;
2567
2568 IF NOT FND_API.Compatible_API_Call
2569 (
2570 L_API_VERSION
2571 , p_api_version_number
2572 , L_API_NAME
2573 , G_PKG_NAME
2574 )
2575 THEN
2576 FND_MSG_PUB.Count_And_Get
2577 (
2578 p_count => x_msg_count
2579 , p_data => x_msg_data
2580 );
2581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2582 END IF ;
2583
2584 IF
2585 FND_API.to_Boolean(p_init_msg_list)
2586 THEN
2587 FND_MSG_PUB.initialize;
2588 END IF ;
2589
2590 ------------------------------------------------------------------------------
2591 -- Actual Logic starts below:
2592 ------------------------------------------------------------------------------
2593
2594 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2595 THEN
2596 fnd_log.string
2597 (
2598 fnd_log.level_statement
2599 , L_LOG_MODULE || 'del_sraudit_tl_start'
2600 , 'Deleting data from table CS_INCIDENTS_AUDIT_TL'
2601 );
2602 END IF ;
2603
2604 -- Deleting rows from the audit TL table for the
2605 -- SRs in the global temp table with purge status NULL
2606
2607 DELETE /*+ index(t) */ cs_incidents_audit_tl t
2608 WHERE incident_id IN
2609 (
2610 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2611 object_id
2612 FROM
2613 jtf_object_purge_param_tmp
2614 WHERE
2615 object_type = 'SR'
2616 AND p_processing_set_id = processing_set_id
2617 AND NVL(purge_status, 'S') = 'S'
2618 );
2619
2620 l_row_count := SQL%ROWCOUNT;
2621
2622 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2623 THEN
2624 fnd_log.string
2625 (
2626 fnd_log.level_statement
2627 , L_LOG_MODULE || 'del_sraudit_tl_end'
2628 , 'After deleting data from table CS_INCIDENTS_AUDIT_TL '
2629 || l_row_count || ' rows'
2630 );
2631 END IF ;
2632
2633 ---
2634
2635 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2636 THEN
2637 fnd_log.string
2638 (
2639 fnd_log.level_statement
2640 , L_LOG_MODULE || 'del_sraudit_b_start'
2641 , 'Deleting data from table CS_INCIDENTS_AUDIT_B'
2642 );
2643 END IF ;
2644
2645 -- Deleting rows from the audit table for the
2646 -- SRs in the global temp table with purge status NULL
2647
2648 DELETE /*+ index(b) */ cs_incidents_audit_b b
2649 WHERE incident_id IN
2650 (
2651 SELECT /*+ unnest no_semijoin cardinality(10) */
2652 object_id
2653 FROM
2654 jtf_object_purge_param_tmp
2655 WHERE
2656 object_type = 'SR'
2657 AND p_processing_set_id = processing_set_id
2658 AND NVL(purge_status, 'S') = 'S'
2659 );
2660
2661 l_row_count := SQL%ROWCOUNT;
2662
2663 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2664 THEN
2665 fnd_log.string
2666 (
2667 fnd_log.level_statement
2668 , L_LOG_MODULE || 'del_sraudit_b_end'
2669 , 'After deleting data from table CS_INCIDENTS_AUDIT_B '
2670 || l_row_count || ' rows'
2671 );
2672 END IF ;
2673
2674 ---
2675
2676 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2677 THEN
2678 fnd_log.string
2679 (
2680 fnd_log.level_procedure
2681 , L_LOG_MODULE || 'end'
2682 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
2683 || x_return_status
2684 );
2685 END IF ;
2686
2687 EXCEPTION
2688
2689 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2690 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2691
2692 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2693 THEN
2694 fnd_log.string
2695 (
2696 fnd_log.level_unexpected
2697 , L_LOG_MODULE || 'unexpected_error'
2698 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
2699 );
2700 END IF ;
2701
2702 WHEN OTHERS THEN
2703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2704 FND_MESSAGE.Set_Name('CS', 'CS_SR_AUDIT_DEL_FAIL');
2705 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
2706 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
2707 FND_MSG_PUB.ADD;
2708
2709 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
2710 THEN
2711 fnd_log.string
2712 (
2713 fnd_log.level_unexpected
2714 , L_LOG_MODULE || 'when_others'
2715 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
2716 );
2717 fnd_log.string
2718 (
2719 fnd_log.level_unexpected
2720 , L_LOG_MODULE || 'when_others'
2721 , SQLERRM
2722 );
2723 END IF ;
2724 END Delete_Audit_Records;
2725
2726 --------------------------------------------------------------------------------
2727 -- Procedure Name : DELETE_SR_ATTRIBUTES
2728 --
2729 -- Parameters (other than standard ones)
2730 -- IN
2731 -- p_object_type : Type of object for which this procedure is
2732 -- being called. (Here it will be 'SR')
2733 -- p_processing_set_id : Id that helps the API in identifying the
2734 -- set of SRs for which the child objects have
2735 -- to be deleted.
2736 --
2737 -- Description
2738 -- This procedure deletes all the extended attributes and CIC attributes
2739 -- associated with the SRs present in the global temp table with purge
2740 -- status NULL along with the audit information captured for the extended
2741 -- attributes.
2742 -- HISTORY
2743 --
2744 ----------------+------------+--------------------------------------------------
2745 -- DATE | UPDATED BY | Change Description
2746 ----------------+------------+--------------------------------------------------
2747 -- 2-Aug_2005 | varnaray | Created
2748 -- | |
2749 ----------------+------------+--------------------------------------------------
2750 /*#
2751 * This procedure deletes all the extended attributes and CIC attributes
2752 * associated with the SRs present in the global temp table with purge status
2753 * NULL along with the audit information captured for the extended attributes.
2754 * @param p_object_type Type of object for which this procedure is being called.
2755 * (Here it will be 'SR')
2756 * @param p_processing_set_id Id that helps the API in identifying the set of
2757 * SRs for which the child objects have to be deleted.
2758 * @rep:scope internal
2759 * @rep:product CS
2760 * @rep:displayname Delete Service Request Attributes
2761 */
2762 PROCEDURE Delete_Sr_Attributes
2763 (
2764 p_api_version_number IN NUMBER
2765 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
2766 , p_commit IN VARCHAR2 := FND_API.G_FALSE
2767 , p_object_type IN VARCHAR2
2768 , p_processing_set_id IN NUMBER
2769 , x_return_status OUT NOCOPY VARCHAR2
2770 , x_msg_count OUT NOCOPY NUMBER
2771 , x_msg_data OUT NOCOPY VARCHAR2
2772 )
2773 IS
2774 --------------------------------------------------------------------------------
2775 L_API_VERSION CONSTANT NUMBER := 1.0;
2776 L_API_NAME CONSTANT VARCHAR2(30) := 'DELETE_SR_ATTRIBUTES';
2777 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
2778 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
2779
2780 l_row_count NUMBER;
2781
2782 BEGIN
2783 x_return_status := FND_API.G_RET_STS_SUCCESS;
2784
2785 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
2786 THEN
2787 fnd_log.string
2788 (
2789 fnd_log.level_procedure
2790 , L_LOG_MODULE || 'start'
2791 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
2792 );
2793 fnd_log.string
2794 (
2795 fnd_log.level_procedure
2796 , L_LOG_MODULE || 'param 1'
2797 , 'p_api_version_number:' || p_api_version_number
2798 );
2799 fnd_log.string
2800 (
2801 fnd_log.level_procedure
2802 , L_LOG_MODULE || 'param 2'
2803 , 'p_init_msg_list:' || p_init_msg_list
2804 );
2805 fnd_log.string
2806 (
2807 fnd_log.level_procedure
2808 , L_LOG_MODULE || 'param 3'
2809 , 'p_commit:' || p_commit
2810 );
2811 fnd_log.string
2812 (
2813 fnd_log.level_procedure
2814 , L_LOG_MODULE || 'param 4'
2815 , 'p_object_type:' || p_object_type
2816 );
2817 fnd_log.string
2818 (
2819 fnd_log.level_procedure
2820 , L_LOG_MODULE || 'param 5'
2821 , 'p_processing_set_id:' || p_processing_set_id
2822 );
2823 END IF ;
2824
2825 IF NOT FND_API.Compatible_API_Call
2826 (
2827 L_API_VERSION
2828 , p_api_version_number
2829 , L_API_NAME
2830 , G_PKG_NAME
2831 )
2832 THEN
2833 FND_MSG_PUB.Count_And_Get
2834 (
2835 p_count => x_msg_count
2836 , p_data => x_msg_data
2837 );
2838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2839 END IF ;
2840
2841 IF
2842 FND_API.to_Boolean(p_init_msg_list)
2843 THEN
2844 FND_MSG_PUB.initialize;
2845 END IF ;
2846
2847 ------------------------------------------------------------------------------
2848 -- Actual Logic starts below:
2849 ------------------------------------------------------------------------------
2850
2851 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2852 THEN
2853 fnd_log.string
2854 (
2855 fnd_log.level_statement
2856 , L_LOG_MODULE || 'cug_attr_tl_del_start'
2857 , 'Deleting data from table CUG_INCIDNT_ATTR_VALS_TL'
2858 );
2859 END IF ;
2860
2861 -- The following statement deletes all the translatable CIC attributes that
2862 -- are linked to the SRs in the global temp table with purge_status NULL
2863
2864 DELETE /*+ index(t) */ cug_incidnt_attr_vals_tl t
2865 WHERE incidnt_attr_val_id IN
2866 (
2867 SELECT /*+ no_unnest no_semijoin leading(j) use_concat cardinality(10) */
2868 c.incidnt_attr_val_id
2869 FROM
2870 jtf_object_purge_param_tmp j
2871 , cug_incidnt_attr_vals_b c
2872 WHERE
2873 j.object_type = 'SR'
2874 AND p_processing_set_id = j.processing_set_id
2875 AND NVL(j.purge_status, 'S') = 'S'
2876 AND c.incident_id = j.object_id
2877 );
2878
2879 l_row_count := SQL%ROWCOUNT;
2880
2881 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2882 THEN
2883 fnd_log.string
2884 (
2885 fnd_log.level_statement
2886 , L_LOG_MODULE || 'cug_attr_tl_del_end'
2887 , 'After deleting data from table CUG_INCIDNT_ATTR_VALS_TL '
2888 || l_row_count || ' rows'
2889 );
2890 END IF ;
2891
2892 ---
2893
2894 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2895 THEN
2896 fnd_log.string
2897 (
2898 fnd_log.level_statement
2899 , L_LOG_MODULE || 'cug_attr_b_del_start'
2900 , 'Deleting data from table CUG_INCIDNT_ATTR_VALS_B'
2901 );
2902 END IF ;
2903
2904 -- The following statement deletes all the CIC attributes that are
2905 -- linked to the SRs in the global temp table with purge_status NULL
2906
2907 DELETE /*+ index(b) */ cug_incidnt_attr_vals_b b
2908 WHERE incident_id IN
2909 (
2910 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2911 object_id
2912 FROM
2913 jtf_object_purge_param_tmp
2914 WHERE
2915 object_type = 'SR'
2916 AND p_processing_set_id = processing_set_id
2917 AND NVL(purge_status, 'S') = 'S'
2918 );
2919
2920 l_row_count := SQL%ROWCOUNT;
2921
2922 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2923 THEN
2924 fnd_log.string
2925 (
2926 fnd_log.level_statement
2927 , L_LOG_MODULE || 'cug_attr_b_del_end'
2928 , 'After deleting data from table CUG_INCIDNT_ATTR_VALS_B '
2929 || l_row_count || ' rows'
2930 );
2931 END IF ;
2932
2933 ---
2934
2935 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2936 THEN
2937 fnd_log.string
2938 (
2939 fnd_log.level_statement
2940 , L_LOG_MODULE || 'sr_ext_attr_aud_del_start'
2941 , 'Deleting data from table CS_INCIDENTS_EXT_AUDIT'
2942 );
2943 END IF ;
2944
2945 -- The following statement deletes all the audit information captured for
2946 -- the translatable extended attributes linked to SRs in the global temp
2947 -- table with purge_status NULL
2948
2949 DELETE /*+ index(a) */ cs_incidents_ext_audit a
2950 WHERE audit_extension_id IN
2951 (
2952 SELECT /*+ unnest no_semijoin leading(j) use_nl(c) cardinality(10) */
2953 audit_extension_id
2954 FROM
2955 jtf_object_purge_param_tmp j
2956 , cs_incidents_ext_audit c
2957 WHERE
2958 j.object_type = 'SR'
2959 AND p_processing_set_id = j.processing_set_id
2960 AND NVL(j.purge_status, 'S') = 'S'
2961 AND c.incident_id = j.object_id
2962 );
2963
2964 l_row_count := SQL%ROWCOUNT;
2965
2966 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2967 THEN
2968 fnd_log.string
2969 (
2970 fnd_log.level_statement
2971 , L_LOG_MODULE || 'sr_ext_attr_aud_del_end'
2972 , 'After deleting data from table CS_INCIDENTS_EXT_AUDIT '
2973 || l_row_count || ' rows'
2974 );
2975 END IF ;
2976
2977 ---
2978
2979 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
2980 THEN
2981 fnd_log.string
2982 (
2983 fnd_log.level_statement
2984 , L_LOG_MODULE || 'sr_ext_attr_del_start'
2985 , 'Deleting data from table CS_INCIDENTS_EXT_TL'
2986 );
2987 END IF ;
2988
2989 -- The following statement deletes all the translatable extended attributes
2990 -- that are linked to the SRs present in the global temp table with purge
2991 -- status NULL
2992
2993 DELETE /*+ index(e) */ cs_incidents_ext_tl e
2994 WHERE incident_id IN
2995 (
2996 SELECT /*+ no_unnest no_semijoin cardinality(10) */
2997 object_id
2998 FROM
2999 jtf_object_purge_param_tmp
3000 WHERE
3001 object_type = 'SR'
3002 AND p_processing_set_id = processing_set_id
3003 AND NVL(purge_status, 'S') = 'S'
3004 );
3005
3006 l_row_count := SQL%ROWCOUNT;
3007
3008 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3009 THEN
3010 fnd_log.string
3011 (
3012 fnd_log.level_statement
3013 , L_LOG_MODULE || 'sr_ext_attr_del_end'
3014 , 'After deleting data from table CS_INCIDENTS_EXT_TL '
3015 || l_row_count || ' rows'
3016 );
3017 END IF ;
3018
3019 ---
3020
3021 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3022 THEN
3023 fnd_log.string
3024 (
3025 fnd_log.level_statement
3026 , L_LOG_MODULE || 'sr_ext_attr_del_start'
3027 , 'Deleting data from table CS_INCIDENTS_EXT'
3028 );
3029 END IF ;
3030
3031 -- The following statement deletes all the translatable extended attributes
3032 -- that are linked to the SRs present in the global temp table with purge
3033 -- status NULL
3034
3035 DELETE /*+ index(e) */ cs_incidents_ext e
3036 WHERE incident_id IN
3037 (
3038 SELECT /*+ no_unnest no_semijoin cardinality(10) */
3039 object_id
3040 FROM
3041 jtf_object_purge_param_tmp
3042 WHERE
3043 object_type = 'SR'
3044 AND p_processing_set_id = processing_set_id
3045 AND NVL(purge_status, 'S') = 'S'
3046 );
3047
3048 l_row_count := SQL%ROWCOUNT;
3049
3050 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3051 THEN
3052 fnd_log.string
3053 (
3054 fnd_log.level_statement
3055 , L_LOG_MODULE || 'sr_ext_attr_del_end'
3056 , 'After deleting data from table CS_INCIDENTS_EXT '
3057 || l_row_count || ' rows'
3058 );
3059 END IF ;
3060
3061 ---
3062
3063 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3064 THEN
3065 fnd_log.string
3066 (
3067 fnd_log.level_procedure
3068 , L_LOG_MODULE || 'end'
3069 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
3070 || x_return_status
3071 );
3072 END IF ;
3073
3074 EXCEPTION
3075
3076 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3078
3079 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3080 THEN
3081 fnd_log.string
3082 (
3083 fnd_log.level_unexpected
3084 , L_LOG_MODULE || 'unexpected_error'
3085 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
3086 );
3087 END IF ;
3088
3089 WHEN OTHERS THEN
3090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3091 FND_MESSAGE.Set_Name('CS', 'CS_SR_ATTR_VAL_DEL_FAIL');
3092 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
3093 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
3094 FND_MSG_PUB.ADD;
3095
3096 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3097 THEN
3098 fnd_log.string
3099 (
3100 fnd_log.level_unexpected
3101 , L_LOG_MODULE || 'when_others'
3102 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
3103 );
3104 fnd_log.string
3105 (
3106 fnd_log.level_unexpected
3107 , L_LOG_MODULE || 'when_others'
3108 , SQLERRM
3109 );
3110 END IF ;
3111 END Delete_Sr_Attributes;
3112
3113 --------------------------------------------------------------------------------
3114 -- Procedure Name : CREATE_PURGEAUDIT_RECORDS
3115 --
3116 -- Parameters (other than standard ones)
3117 -- IN
3118 -- p_purge_set_id : Id that helps the API in identifying the
3119 -- set of SRs for which the child objects
3120 -- have to be deleted.
3121 -- p_incident_id_tbl : pl/sql table containing all the SR ids
3122 -- that have been purged successfully
3123 -- p_incident_number_tbl : pl/sql table containing all the SR
3124 -- numbers that have been purged
3125 -- successfully
3126 -- p_incident_type_id_tbl : pl/sql table containing type ids of all
3127 -- the SRs that have been purged
3128 -- successfully
3129 -- p_customer_id_tbl : pl/sql table containing customer ids of
3130 -- all the SRs that have been purged
3131 -- successfully
3132 -- p_inv_organization_id_tbl : pl/sql table containing org ids of all
3133 -- the SRs that have been purged
3134 -- successfully
3135 -- p_inventory_item_id_tbl : pl/sql table containing item ids of all
3136 -- the SRs that have been purged
3137 -- successfully
3138 -- p_customer_product_id_tbl : pl/sql table containing instance ids of
3139 -- all the SRs that have been purged
3140 -- successfully
3141 -- p_inc_creation_date_tbl : pl/sql table containing creation dates
3142 -- of all the SRs that have been purged
3143 -- successfully
3144 -- p_inc_last_update_date_tbl : pl/sql table containing last update
3145 -- dates ids of all the SRs that have been
3146 -- purged successfully
3147 -- p_incident_id_tl_tbl : pl/sql table containing SRs ids (as
3148 -- in TL table) of all the SRs that have
3149 -- been purged successfully
3150 -- p_language_tbl : pl/sql table containing language of all
3151 -- the SRs that have been purged
3152 -- successfully
3153 -- p_source_lang_tbl : pl/sql table containing source lang of
3154 -- all the SRs that have been purged
3155 -- successfully
3156 -- p_summary_tbl : pl/sql table containing summary of all
3157 -- the SRs that have been purged
3158 -- successfully
3159 --
3160 -- Description
3161 -- This procedure creates rows in the purge audit table to preserve the
3162 -- basic information related to the SRs that were purged using the SR
3163 -- purge concurrent program. It is called from the DeleteServiceRequest
3164 -- procedure with pl/sql tables containing all the data that need to
3165 -- be preserved.
3166 --
3167 -- HISTORY
3168 --
3169 ----------------+------------+--------------------------------------------------
3170 -- DATE | UPDATED BY | Change Description
3171 ----------------+------------+--------------------------------------------------
3172 -- 2-Aug_2005 | varnaray | Created
3173 -- | |
3174 ----------------+------------+--------------------------------------------------
3175 /*#
3176 * This procedure creates rows in the purge audit table to preserve the basic
3177 * information related to the SRs that were purged using the SR purge concurrent
3178 * program. It is called from the DeleteServiceRequest procedure with pl/sql
3179 * tables containing all the data that need to be preserved.
3180 * @param p_purge_set_id Id that helps the API in identifying the set of SRs
3181 * for which the child objects have to be deleted.
3182 * @param p_incident_id_tbl pl/sql table containing all the SR ids that have
3183 * been purged successfully
3184 * @param p_incident_number_tbl pl/sql table containing all the SR numbers that
3185 * have been purged successfully
3186 * @param p_incident_type_id_tbl pl/sql table containing type ids of all the
3187 * SRs that have been purged successfully
3188 * @param p_customer_id_tbl pl/sql table containing customer ids of all the
3189 * SRs that have been purged successfully
3190 * @param p_inv_organization_id_tbl pl/sql table containing org ids of all the
3191 * SRs that have been purged successfully
3192 * @param p_inventory_item_id_tbl pl/sql table containing item ids of all the
3193 * SRs that have been purged successfully
3194 * @param p_customer_product_id_tbl pl/sql table containing instance ids of
3195 * all the SRs that have been purged successfully
3196 * @param p_inc_creation_date_tbl pl/sql table containing creation dates of
3197 * all the SRs that have been purged successfully
3198 * @param p_inc_last_update_date_tbl pl/sql table containing last update
3199 * dates ids of all the SRs that have been purged successfully
3200 * @param p_incident_id_tl_tbl pl/sql table containing SRs ids (as in TL
3201 * table) of all the SRs that have been purged successfully
3202 * @param p_language_tbl pl/sql table containing language of all the SRs
3203 * that have been purged successfully
3204 * @param p_source_lang_tbl pl/sql table containing source lang of all the SRs
3205 * that have been purged successfully
3206 * @param p_summary_tbl pl/sql table containing summary of all the SRs
3207 * that have been purged successfully
3208 * @rep:scope internal
3209 * @rep:product CS
3210 * @rep:displayname Create Purge Audit Records
3211 */
3212 PROCEDURE Create_Purgeaudit_Records
3213 (
3214 p_api_version_number IN NUMBER := 1.0
3215 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3216 , p_commit IN VARCHAR2 := FND_API.G_FALSE
3217 , p_purge_set_id IN NUMBER
3218 , p_incident_id_tbl IN t_number_tbl
3219 , p_incident_number_tbl IN t_long_string_tbl
3220 , p_incident_type_id_tbl IN t_number_tbl
3221 , p_customer_id_tbl IN t_number_tbl
3222 , p_inv_organization_id_tbl IN t_number_tbl
3223 , p_inventory_item_id_tbl IN t_number_tbl
3224 , p_customer_product_id_tbl IN t_number_tbl
3225 , p_inc_creation_date_tbl IN t_date_tbl
3226 , p_inc_last_update_date_tbl IN t_date_tbl
3227 , p_incident_id_tl_tbl IN t_number_tbl
3228 , p_language_tbl IN t_string_tbl
3229 , p_source_lang_tbl IN t_string_tbl
3230 , p_summary_tbl IN t_long_string_tbl
3231 , x_return_status OUT NOCOPY VARCHAR2
3232 , x_msg_count OUT NOCOPY NUMBER
3233 , x_msg_data OUT NOCOPY VARCHAR2
3234 )
3235 IS
3236 --------------------------------------------------------------------------------
3237 L_API_VERSION CONSTANT NUMBER := 1.0;
3238 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_PURGEAUDIT_RECORDS';
3239 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
3240 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
3241
3242 l_user_id NUMBER;
3243 l_login_id NUMBER;
3244
3245 l_row_count NUMBER;
3246
3247 BEGIN
3248 x_return_status := FND_API.G_RET_STS_SUCCESS;
3249
3250 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3251 THEN
3252 fnd_log.string
3253 (
3254 fnd_log.level_procedure
3255 , L_LOG_MODULE || 'start'
3256 , 'Inside ' || L_API_NAME_FULL || ', called with parameters below:'
3257 );
3258 fnd_log.string
3259 (
3260 fnd_log.level_procedure
3261 , L_LOG_MODULE || 'param 1'
3262 , 'p_api_version_number:' || p_api_version_number
3263 );
3264 fnd_log.string
3265 (
3266 fnd_log.level_procedure
3267 , L_LOG_MODULE || 'param 2'
3268 , 'p_init_msg_list:' || p_init_msg_list
3269 );
3270 fnd_log.string
3271 (
3272 fnd_log.level_procedure
3273 , L_LOG_MODULE || 'param 3'
3274 , 'p_commit:' || p_commit
3275 );
3276 fnd_log.string
3277 (
3278 fnd_log.level_procedure
3279 , L_LOG_MODULE || 'param 4'
3280 , 'p_purge_set_id' || p_purge_set_id
3281 );
3282 END IF ;
3283
3284 l_user_id := fnd_global.user_id;
3285 l_login_id := fnd_global.login_id;
3286
3287 IF NOT FND_API.Compatible_API_Call
3288 (
3289 L_API_VERSION
3290 , p_api_version_number
3291 , L_API_NAME
3292 , G_PKG_NAME
3293 )
3294 THEN
3295 FND_MSG_PUB.Count_And_Get
3296 (
3297 p_count => x_msg_count
3298 , p_data => x_msg_data
3299 );
3300 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3301 END IF ;
3302
3303 IF FND_API.to_Boolean(p_init_msg_list)
3304 THEN
3305 FND_MSG_PUB.initialize;
3306 END IF ;
3307
3308 ------------------------------------------------------------------------------
3309 -- Actual Logic starts below:
3310 ------------------------------------------------------------------------------
3311
3312 IF p_incident_id_tbl.COUNT > 0
3313 THEN
3314 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3315 THEN
3316 fnd_log.string
3317 (
3318 fnd_log.level_statement
3319 , L_LOG_MODULE || 'ins_purge_aud_b_start'
3320 , 'Inserting data into table CS_INCIDENTS_PURGE_AUDIT_B'
3321 );
3322 END IF ;
3323
3324 -- Inserting information relating to purged SRs into the audit base table
3325
3326 FORALL j IN p_incident_id_tbl.FIRST..p_incident_id_tbl.LAST
3327 INSERT INTO cs_incidents_purge_audit_b
3328 (
3329 purge_id
3330 , incident_id
3331 , incident_number
3332 , incident_type_id
3333 , customer_id
3334 , inv_organization_id
3335 , inventory_item_id
3336 , customer_product_id
3337 , inc_creation_date
3338 , inc_last_update_date
3339 , purged_date
3340 , purged_by
3341 , creation_date
3342 , created_by
3343 , last_update_date
3344 , last_updated_by
3345 , last_update_login
3346 )
3347 VALUES
3348 (
3349 p_purge_set_id
3350 , p_incident_id_tbl(j)
3351 , p_incident_number_tbl(j)
3352 , p_incident_type_id_tbl(j)
3353 , p_customer_id_tbl(j)
3354 , p_inv_organization_id_tbl(j)
3355 , p_inventory_item_id_tbl(j)
3356 , p_customer_product_id_tbl(j)
3357 , p_inc_creation_date_tbl(j)
3358 , p_inc_last_update_date_tbl(j)
3359 , SYSDATE
3360 , l_user_id
3361 , SYSDATE
3362 , l_user_id
3363 , SYSDATE
3364 , l_user_id
3365 , l_login_id
3366 );
3367
3368 l_row_count := SQL%ROWCOUNT;
3369
3370 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3371 THEN
3372 fnd_log.string
3373 (
3374 fnd_log.level_statement
3375 , L_LOG_MODULE || 'ins_purge_aud_b_end'
3376 , 'After inserting data into table CS_INCIDENTS_PURGE_AUDIT_B '
3377 || l_row_count || ' rows'
3378 );
3379 END IF ;
3380 ELSIF p_incident_id_tbl.COUNT <= 0
3381 THEN
3382 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3383 THEN
3384 fnd_log.string
3385 (
3386 fnd_log.level_statement
3387 , L_LOG_MODULE || 'ins_purge_aud_b_err'
3388 , 'While inserting data into table CS_INCIDENTS_PURGE_AUDIT_B '
3389 || 'p_incident_id_tbl had ' || p_incident_id_tbl.COUNT || ' rows'
3390 );
3391 END IF ;
3392 END IF;
3393
3394 IF p_incident_id_tl_tbl.COUNT > 0
3395 THEN
3396 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3397 THEN
3398 fnd_log.string
3399 (
3400 fnd_log.level_statement
3401 , L_LOG_MODULE || 'ins_purge_aud_tl_start'
3402 , 'Inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL'
3403 );
3404 END IF ;
3405
3406 -- Inserting translatable information relating to
3407 -- purged SRs into the audit base table
3408
3409 FORALL j IN p_incident_id_tl_tbl.FIRST..p_incident_id_tl_tbl.LAST
3410 INSERT INTO cs_incidents_purge_audit_tl
3411 (
3412 purge_id
3413 , incident_id
3414 , language
3415 , source_lang
3416 , summary
3417 , creation_date
3418 , created_by
3419 , last_update_date
3420 , last_updated_by
3421 , last_update_login
3422 )
3423 VALUES
3424 (
3425 p_purge_set_id
3426 , p_incident_id_tl_tbl(j)
3427 , p_language_tbl(j)
3428 , p_source_lang_tbl(j)
3429 , p_summary_tbl(j)
3430 , SYSDATE
3431 , l_user_id
3432 , SYSDATE
3433 , l_user_id
3434 , l_login_id
3435 );
3436
3437 l_row_count := SQL%ROWCOUNT;
3438
3439 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3440 THEN
3441 fnd_log.string
3442 (
3443 fnd_log.level_statement
3444 , L_LOG_MODULE || 'ins_purge_aud_tl_end'
3445 , 'After inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL '
3446 || l_row_count || ' rows'
3447 );
3448 END IF ;
3449 ELSIF p_incident_id_tl_tbl.COUNT <= 0
3450 THEN
3451 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3452 THEN
3453 fnd_log.string
3454 (
3455 fnd_log.level_statement
3456 , L_LOG_MODULE || 'ins_purge_aud_tl_err'
3457 , 'While inserting data into table CS_INCIDENTS_PURGE_AUDIT_TL '
3458 || 'p_incident_id_tl_tbl had ' || p_incident_id_tl_tbl.COUNT || ' rows'
3459 );
3460 END IF ;
3461 END IF;
3462
3463 IF fnd_log.level_procedure >= fnd_log.g_current_runtime_level
3464 THEN
3465 fnd_log.string
3466 (
3467 fnd_log.level_procedure
3468 , L_LOG_MODULE || 'end'
3469 , 'Completed work in ' || L_API_NAME_FULL || ' with return status '
3470 || x_return_status
3471 );
3472 END IF ;
3473
3474 EXCEPTION
3475
3476 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3478
3479 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3480 THEN
3481 fnd_log.string
3482 (
3483 fnd_log.level_unexpected
3484 , L_LOG_MODULE || 'unexpected_error'
3485 , 'Inside WHEN FND_API.G_EXC_UNEXPECTED_ERROR of ' || L_API_NAME_FULL
3486 );
3487 END IF ;
3488
3489 WHEN OTHERS THEN
3490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3491 FND_MESSAGE.Set_Name('CS', 'CS_SR_PRG_CRT_FAIL');
3492 FND_MESSAGE.Set_Token('API_NAME', L_API_NAME_FULL);
3493 FND_MESSAGE.Set_Token('ERROR', SQLERRM);
3494 FND_MSG_PUB.ADD;
3495
3496 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3497 THEN
3498 fnd_log.string
3499 (
3500 fnd_log.level_unexpected
3501 , L_LOG_MODULE || 'when_others'
3502 , 'Inside WHEN OTHERS of ' || L_API_NAME_FULL || '. Oracle Error was:'
3503 );
3504 fnd_log.string
3505 (
3506 fnd_log.level_unexpected
3507 , L_LOG_MODULE || 'when_others'
3508 , SQLERRM
3509 );
3510 END IF ;
3511 END Create_Purgeaudit_Records;
3512 --------------------------------------------------------------------------------
3513 -- Procedure Name : CHECK_USER_TERMINATION
3514 --
3515 -- Parameters (other than standard ones)
3516 -- NONE.
3517 --
3518 -- Description
3519 -- This procedure is called before performing any step during the purge
3520 -- process so that if the user requests a termination of the purge process
3521 -- for some reason, the process should end gracefully.
3522 --
3523 -- HISTORY
3524 --
3525 ----------------+------------+--------------------------------------------------
3526 -- DATE | UPDATED BY | Change Description
3527 ----------------+------------+--------------------------------------------------
3528 -- 2-Aug_2005 | varnaray | Created
3529 -- | |
3530 ----------------+------------+--------------------------------------------------
3531 /*#
3532 * This procedure is called before performing any step during the purge process
3533 * so that if the user requests a termination of the purge process for some
3534 * reason, the process should end gracefully.
3535 */
3536 PROCEDURE Check_User_Termination
3537 IS
3538 --------------------------------------------------------------------------------
3539 L_API_VERSION CONSTANT NUMBER := 1.0;
3540 L_API_NAME CONSTANT VARCHAR2(30) := 'CHECK_USER_TERMINATION';
3541 L_API_NAME_FULL CONSTANT VARCHAR2(61) := G_PKG_NAME || '.' || L_API_NAME;
3542 L_LOG_MODULE CONSTANT VARCHAR2(255) := 'cs.plsql.' || L_API_NAME_FULL || '.';
3543
3544 l_request_id NUMBER;
3545 l_phase VARCHAR2(100);
3546 l_status VARCHAR2(100);
3547 l_dev_phase VARCHAR2(100);
3548 l_dev_status VARCHAR2(100);
3549 l_message VARCHAR2(500);
3550
3551 BEGIN
3552 l_request_id := fnd_global.conc_request_id;
3553
3554 IF l_request_id <> -1
3555
3556 -- The check for user termination is only
3557 -- required if the SR Delete Helper is called
3558 -- from a concurrent program. If l_request_id
3559 -- is -1, it means that the procedure is not
3560 -- called from a concurrent program. Hence the
3561 -- check for user termination may not be required.
3562
3563 THEN
3564 IF fnd_concurrent.get_request_status
3565 (
3566 request_id => l_request_id
3567 , phase => l_phase
3568 , status => l_status
3569 , dev_phase => l_dev_phase
3570 , dev_status => l_dev_status
3571 , message => l_message
3572 )
3573 THEN
3574 IF l_dev_status = 'TERMINATING'
3575 AND l_dev_phase = 'RUNNING'
3576 OR l_dev_status = 'TERMINATED'
3577 AND l_dev_phase = 'COMPLETE'
3578
3579 -- If the user terminates the concurrent request
3580 -- raise an exception and add a message to the stack
3581
3582 THEN
3583 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level
3584 THEN
3585 fnd_log.string
3586 (
3587 fnd_log.level_statement
3588 , L_LOG_MODULE || 'conc_req_user_stop'
3589 , 'This concurrent request is in status ' || l_dev_status
3590 || ' and phase ' || l_dev_phase
3591 );
3592 END IF ;
3593
3594 FND_MESSAGE.Set_Name('CS', 'CS_SR_USER_STOPPED');
3595 FND_MSG_PUB.ADD;
3596
3597 -- Setting the request_data to 'T'
3598 -- indicating that the request has
3599 -- been terminated by the user
3600
3601 fnd_conc_global.set_req_globals
3602 (
3603 request_data => 'T'
3604 );
3605
3606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3607 END IF;
3608 ELSE
3609 -- if fnd_concurrent.get_request_status failed...
3610
3611 IF fnd_log.level_unexpected >= fnd_log.g_current_runtime_level
3612 THEN
3613 fnd_log.string
3614 (
3615 fnd_log.level_unexpected
3616 , L_LOG_MODULE || 'conc_req_status_fail'
3617 , 'Failed while getting the status of this request'
3618 );
3619 END IF ;
3620 END IF;
3621 END IF;
3622 END Check_User_Termination;
3623 --------------------------------------------------------------------------------
3624 END cs_sr_delete_util;
[Home] [Help]