[Home] [Help]
PACKAGE BODY: APPS.CSF_MAINTAIN_GRP
Source
1 PACKAGE BODY CSF_MAINTAIN_GRP as
2 /* $Header: csfpurgb.pls 120.9 2006/05/10 10:13:38 hhaugeru noship $ */
3 --
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter package declarations as shown below
13
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'CSF_MAINTAIN_GRP';
15
16 PROCEDURE Validate_FieldServiceObjects(
17 P_API_VERSION IN NUMBER,
18 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
19 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
20 P_PROCESSING_SET_ID IN NUMBER,
21 P_OBJECT_TYPE IN VARCHAR2,
22 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2,
23 X_MSG_COUNT IN OUT NOCOPY NUMBER,
24 X_MSG_DATA IN OUT NOCOPY VARCHAR2)
25 IS
26
27 l_api_name CONSTANT VARCHAR2(30) := 'Validate_FieldServiceObjects';
28 l_api_version_number CONSTANT NUMBER := 1.0;
29
30 l_return_status VARCHAR2(1);
31 l_msg_count NUMBER;
32 l_msg_data VARCHAR2(2000);
33
34 BEGIN
35
36 SAVEPOINT Validate_FieldServiceObjects;
37
38 -- Standard call to check for call compatibility.
39 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
40 p_api_version,
41 l_api_name,
42 G_PKG_NAME)
43 THEN
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
45 END IF;
46
47
48 -- Initialize message list
49 FND_MSG_PUB.initialize;
50
51
52 -- Initialize API return status to SUCCESS
53 x_return_status := FND_API.G_RET_STS_SUCCESS;
54
55 /*
56 Validation logic
57 -----------------
58 1 Check if the incident ids associated to the processing set id (i/p parameter) has incident links to CMRO objects.
59 1.1 If this check returns true,
60 1.1.1 Mark the corresponding incident id as NOT a candidate for purge
61 2 Invoke the mobile validation API to verify if the incident ids associated to the processing set id (i/p parameter) are eligible for purge. This API will mark those incident ids which are NOT candidates for purge
62 3 Check if the Field Service tasks that belong to the (unmarked) incident ids associated to the processing set id (i/p parameter) are in Closed/Cancelled/Completed task status
63 3.1 If this check returns false,
64 3.1.1 Mark the corresponding incident id as NOT a candidate for purge
65 4 Return Success
66 */
67
68 IF (nvl(P_OBJECT_TYPE, 'SR') = 'SR') then
69
70 /* Step 1 : Mark SRs (for the given p_processing_set_id) as NOT a delete candidate
71 IF the SR is linked to a CMRO object
72 */
73 update JTF_OBJECT_PURGE_PARAM_TMP
74 set
75 purge_status = 'E'
76 , purge_error_message = 'CSF:CSF_DEBRIEF_PURGE_FAILED'
77 where
78 processing_set_id = P_PROCESSING_SET_ID and
79 object_id in
80 (
81 select
82 lnk.subject_id
83 from
84 cs_incident_links lnk
85 , JTF_OBJECT_PURGE_PARAM_TMP tmp
86 where
87 tmp.object_id = lnk.subject_id and
88 lnk.object_type = 'AHL_UMP_EFF' and
89 lnk.link_type_id = 6 and
90 nvl(tmp.purge_status, 'S') <> 'E' and
91 tmp.processing_set_id = P_PROCESSING_SET_ID
92 )
93 and nvl(purge_status, 'S') <> 'E';
94
95 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
96 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Validate_FieldServiceObjects() after first validation ');
97 END IF;
98 /* Step 2: Mark SRs (for the given p_processing_set_id) as NOT a delete candidate
99 IF the SR is linked to mobile FS tasks that CANNOT be deleted
100 */
101 /*
102 This is the place where we call the mobile field service task validation API to further
103 mark the JTF_OBJECT_PURGE_PARAM_TMP table with a status 'E' for all those SRs which have
104 mobile field service tasks that CANNOT be deleted
105 */
106 csm_sr_purge_pkg.Validate_MobileFSObjects(
107 P_API_VERSION => P_API_VERSION,
108 P_INIT_MSG_LIST => P_INIT_MSG_LIST,
109 P_COMMIT => P_COMMIT,
110 P_PROCESSING_SET_ID => P_PROCESSING_SET_ID,
111 P_OBJECT_TYPE => P_OBJECT_TYPE,
112 X_RETURN_STATUS => l_return_status,
113 X_MSG_COUNT => l_msg_count,
114 X_MSG_DATA => l_msg_data);
115
116 IF l_return_status = fnd_api.g_ret_sts_error THEN
117 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
118 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'csm_sr_purge_pkg.Validate_MobileFSObjects returned error ');
119 END IF;
120 RAISE fnd_api.g_exc_error;
121 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
122 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
123 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'csm_sr_purge_pkg.Validate_MobileFSObjects returned unexpected error ');
124 END IF;
125 RAISE fnd_api.g_exc_unexpected_error;
126 END IF;
127
128
129
130 /* Step 3: Mark SRs (for the given p_processing_set_id) as NOT a delete candidate
131 IF the SR is linked to FS tasks that are in Closed/Completed/Cancelled status
132 */
133 update JTF_OBJECT_PURGE_PARAM_TMP
134 set
135 purge_status = 'E'
136 , purge_error_message = 'CSF:CSF_DEBRIEF_PURGE_FAILED'
137 where
138 processing_set_id = P_PROCESSING_SET_ID and
139 object_id in
140 (
141 select
142 tmp.object_id
143 from
144 jtf_tasks_b jtftk
145 , jtf_task_statuses_b jtfts
146 , jtf_task_types_b jttp
147 , JTF_OBJECT_PURGE_PARAM_TMP tmp
148 where
149 tmp.object_id = jtftk.source_object_id and
150 tmp.object_type = jtftk.source_object_type_code and
151 jtftk.task_status_id = jtfts.task_status_id and
152 (nvl(jtfts.closed_flag,'N') <> 'Y' and
153 nvl(jtfts.completed_flag,'N') <> 'Y' and
154 nvl(jtfts.cancelled_flag,'N') <> 'Y') and
155 nvl(tmp.purge_status, 'S') <> 'E' and
156 jtftk.task_type_id = jttp.task_type_id and
157 jttp.rule = 'DISPATCH' and
158 tmp.processing_set_id = P_PROCESSING_SET_ID
159 )
160 and nvl(purge_status, 'S') <> 'E';
161
162 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
163 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Validate_FieldServiceObjects() after third validation ');
164 END IF;
165
166 -- Standard check of p_commit
167 -- IF fnd_api.to_boolean(p_commit) THEN
168 -- COMMIT WORK;
169 -- END IF;
170
171 END IF;
172
173
174 EXCEPTION
175 WHEN fnd_api.g_exc_error THEN
176 ROLLBACK TO Validate_FieldServiceObjects;
177 x_return_status := fnd_api.g_ret_sts_error;
178 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
179
180 WHEN fnd_api.g_exc_unexpected_error THEN
181 ROLLBACK TO Validate_FieldServiceObjects;
182 x_return_status := fnd_api.g_ret_sts_unexp_error;
183 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
184
185 WHEN OTHERS THEN
186 x_return_status := fnd_api.g_ret_sts_unexp_error;
187 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
188 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
189 END IF;
190 ROLLBACK TO Validate_FieldServiceObjects;
191 END;
192
193
194
195
196 PROCEDURE Purge_FieldServiceObjects(
197 P_API_VERSION IN NUMBER ,
198 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
199 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
200 P_PROCESSING_SET_ID IN NUMBER ,
201 P_OBJECT_TYPE IN VARCHAR2 ,
202 X_RETURN_STATUS IN OUT NOCOPY VARCHAR2,
203 X_MSG_COUNT IN OUT NOCOPY NUMBER,
204 X_MSG_DATA IN OUT NOCOPY VARCHAR2)
205 IS
206 l_api_name CONSTANT VARCHAR2(30) := 'Purge_FieldServiceObjects';
207 l_api_version_number CONSTANT NUMBER := 1.0;
208
209 BEGIN
210
211
212 SAVEPOINT Purge_FieldServiceObjects;
213
214 -- Standard call to check for call compatibility.
215 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
216 p_api_version,
217 l_api_name,
218 G_PKG_NAME)
219 THEN
220 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
221 END IF;
222
223
224 -- Initialize message list
225 FND_MSG_PUB.initialize;
226
227 IF (nvl(P_OBJECT_TYPE, 'SR') = 'SR') then
228
229 -- Initialize API return status to SUCCESS
230 x_return_status := FND_API.G_RET_STS_SUCCESS;
231
232 /* Delete Logic
233 1 Fetch the incident ids associated to the processing set id (i/p parameter) which are marked for purge
234 1.1 If no incident ids (for the given processing set id) require purging, Return Success
235 2 Fetch Field Service tasks belonging to the incident ids which are purge candidates
236 3 Fetch debrief headers that belong to the fetched tasks
237 4 Fetch debrief lines that belong to the fetched debrief headers
238 5 Fetch debrief notes that belong to the fetched debrief headers
239 6 Fetch parts requirement headers that belong to the fetched tasks
240 7 Fetch parts requirement lines that belong to the fetched parts requirement headers
241 8 Fetch parts requirement line details that belong to the fetched parts requirement headers
242 9 Fetch required skills that are associated to the fetched tasks
243 10 Fetch Schedule Requests
244 11 Fetch Resource Results
245 12 Fetch Messages and Message Tokens
246 13 Delete contents fetched from step 3 - step 12
247 14 Return Success if delete operation is successful
248 15 Return Failure if delete operation fails
249 */
250
251
252 /* Step 1 - Delete relevant debrief notes and note contexts */
253 DELETE /*+ index(jnc) */ FROM JTF_NOTE_CONTEXTS jnc
254 WHERE
255 jtf_note_id in
256 (
257 SELECT /*+ no_unnest no_semijoin cardinality(10) */
258 jtfnl.jtf_note_id
259 FROM
260 JTF_NOTE_CONTEXTS jtfnl
261 , JTF_NOTES_B jtfnb
262 , CSF_DEBRIEF_HEADERS dbfh
263 , JTF_TASK_ASSIGNMENTS tska
264 , JTF_TASKS_B tskt
265 , JTF_OBJECT_PURGE_PARAM_TMP tmp
266 WHERE
267 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
268 nvl(tmp.purge_status, 'S') <> 'E' and
269 tmp.object_id = tskt.source_object_id and
270 tmp.object_type = tskt.source_object_type_code and
271 tskt.task_id = tska.task_id and
272 tska.task_assignment_id = dbfh.task_assignment_id and
273 dbfh.debrief_header_id = jtfnb.source_object_id and
274 jtfnb.source_object_code = 'DF' and
275 jtfnb.jtf_note_id = jtfnl.jtf_note_id
276 );
277
278
279
280 DELETE /*+ index(jnt)*/ from JTF_NOTES_TL jnt
281 WHERE
282 jtf_note_id in
283 (
284 SELECT /*+ no_unnest no_semijoin cardinality(10) */
285 jtfnb.jtf_note_id
286 FROM
287 JTF_NOTES_B jtfnb
288 , CSF_DEBRIEF_HEADERS dbfh
289 , JTF_TASK_ASSIGNMENTS tska
290 , JTF_TASKS_B tskt
291 , JTF_OBJECT_PURGE_PARAM_TMP tmp
292 WHERE
293 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
294 nvl(tmp.purge_status, 'S') <> 'E' and
295 tmp.object_id = tskt.source_object_id and
296 tmp.object_type = tskt.source_object_type_code and
297 tskt.task_id = tska.task_id and
298 tska.task_assignment_id = dbfh.task_assignment_id and
299 dbfh.debrief_header_id = jtfnb.source_object_id and
300 jtfnb.source_object_code = 'DF'
301 );
302
303
304 DELETE /*+ index(jnb)*/ from JTF_NOTES_B jnb
305 WHERE
306 jtf_note_id in
307 (
308 SELECT /*+ no_unnest no_semijoin cardinality(10) */
309 jtfnb.jtf_note_id
310 FROM
311 JTF_NOTES_B jtfnb
312 , CSF_DEBRIEF_HEADERS dbfh
313 , JTF_TASK_ASSIGNMENTS tska
314 , JTF_TASKS_B tskt
315 , JTF_OBJECT_PURGE_PARAM_TMP tmp
316 WHERE
317 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
318 nvl(tmp.purge_status, 'S') <> 'E' and
319 tmp.object_id = tskt.source_object_id and
320 tmp.object_type = tskt.source_object_type_code and
321 tskt.task_id = tska.task_id and
322 tska.task_assignment_id = dbfh.task_assignment_id and
323 dbfh.debrief_header_id = jtfnb.source_object_id and
324 jtfnb.source_object_code = 'DF'
325 );
326
327 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
328 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task notes and note related objs ');
329 END IF;
330
331 /* Step 2 - Delete relevant debrief lines and debrief line related objects:
332 1) CSF_DEBRIEF_LINES
333 */
334
335 DELETE /*+ index(cdl) */ FROM CSF_DEBRIEF_LINES cdl
336 WHERE
337 debrief_line_id in
338 (
339 SELECT /*+ no_unnest no_semijoin cardinality(10) */
340 dbfl.debrief_line_id
341 FROM
342 CSF_DEBRIEF_LINES dbfl
343 , CSF_DEBRIEF_HEADERS dbfh
344 , JTF_TASK_ASSIGNMENTS tska
345 , JTF_TASKS_B tskt
346 , JTF_OBJECT_PURGE_PARAM_TMP tmp
347 WHERE
348 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
349 nvl(tmp.purge_status, 'S') <> 'E' and
350 tmp.object_id = tskt.source_object_id and
351 tmp.object_type = tskt.source_object_type_code and
352 tskt.task_id = tska.task_id and
353 tska.task_assignment_id = dbfh.task_assignment_id and
354 dbfh.debrief_header_id = dbfl.debrief_header_id
355 );
356
357 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
358 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task debriefed line details ');
359 END IF;
360
361
362 /* Step 3 - Delete relevant debrief headers*/
363 DELETE /*+ index(cdh) */ FROM CSF_DEBRIEF_HEADERS cdh
364 WHERE
365 debrief_header_id in
366 (
367 SELECT /*+ no_unnest no_semijoin cardinality(10) */
368 dbfh.debrief_header_id
369 FROM
370 CSF_DEBRIEF_HEADERS dbfh
374 WHERE
371 , JTF_TASK_ASSIGNMENTS tska
372 , JTF_TASKS_B tskt
373 , JTF_OBJECT_PURGE_PARAM_TMP tmp
375 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
376 nvl(tmp.purge_status, 'S') <> 'E' and
377 tmp.object_id = tskt.source_object_id and
378 tmp.object_type = tskt.source_object_type_code and
379 tskt.task_id = tska.task_id and
380 tska.task_assignment_id = dbfh.task_assignment_id
381 );
382
383 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
384 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task debrief header details ');
385 END IF;
386
387 /* Step 4 - Delete relevant requirement line details */
388 DELETE /*+ index(crld) */ FROM CSP_REQ_LINE_DETAILS crld
389 WHERE
390 req_line_detail_id in
391 (
392 SELECT /*+ no_unnest no_semijoin cardinality(10) */
393 csprdt.req_line_detail_id
394 FROM
395 CSP_REQ_LINE_DETAILS csprdt
396 , CSP_REQUIREMENT_LINES csprl
397 , CSP_REQUIREMENT_HEADERS csprh
398 , JTF_TASKS_B tskt
399 , JTF_OBJECT_PURGE_PARAM_TMP tmp
400 WHERE
401 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
402 nvl(tmp.purge_status, 'S') <> 'E' and
403 tmp.object_id = tskt.source_object_id and
404 tmp.object_type = tskt.source_object_type_code and
405 tskt.task_id = csprh.task_id and
406 csprh.requirement_header_id = csprl.requirement_header_id and
407 csprl.requirement_line_id = csprdt.requirement_line_id
408 );
409
410
411 /* Step 5 - Delete relevant requirement lines */
412 DELETE /*+ index(crl) */ FROM CSP_REQUIREMENT_LINES crl
413 WHERE
414 requirement_line_id in
415 (
416 SELECT /*+ no_unnest no_semijoin cardinality(10) */
417 csprl.requirement_line_id
418 FROM
419 CSP_REQUIREMENT_LINES csprl
420 , CSP_REQUIREMENT_HEADERS csprh
421 , JTF_TASKS_B tskt
422 , JTF_OBJECT_PURGE_PARAM_TMP tmp
423 WHERE
424 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
425 nvl(tmp.purge_status, 'S') <> 'E' and
426 tmp.object_id = tskt.source_object_id and
427 tmp.object_type = tskt.source_object_type_code and
428 tskt.task_id = csprh.task_id and
429 csprh.requirement_header_id = csprl.requirement_header_id
430 );
431
432
433
434 /* Step 6 - Delete relevant requirement headers */
435 DELETE /*+ index(crh) */ FROM CSP_REQUIREMENT_HEADERS crh
436 WHERE
437 requirement_header_id in
438 (
439 SELECT /*+ no_unnest no_semijoin cardinality(10) */
440 csprh.requirement_header_id
441 FROM
442 CSP_REQUIREMENT_HEADERS csprh
443 , JTF_TASKS_B tskt
444 , JTF_OBJECT_PURGE_PARAM_TMP tmp
445 WHERE
446 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
447 nvl(tmp.purge_status, 'S') <> 'E' and
448 tmp.object_id = tskt.source_object_id and
449 tmp.object_type = tskt.source_object_type_code and
450 tskt.task_id = csprh.task_id
451 );
452
453
454 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
455 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task requirement headers and lines ');
456 END IF;
457
458 /* Step 7 - Delete relevant required skills */
459 DELETE /*+ index(crsb) */ FROM CSF_REQUIRED_SKILLS_B crsb
460 WHERE
461 required_skill_id in
462 (
463 SELECT /*+ no_unnest no_semijoin cardinality(10) */
464 csfsk.required_skill_id
465 FROM
466 CSF_REQUIRED_SKILLS_B csfsk
467 , JTF_TASKS_B tskt
468 , JTF_OBJECT_PURGE_PARAM_TMP tmp
469 WHERE
470 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
471 nvl(tmp.purge_status, 'S') <> 'E' and
472 tmp.object_id = tskt.source_object_id and
473 tmp.object_type = tskt.source_object_type_code and
474 tskt.task_id = csfsk.has_skill_id and
475 csfsk.has_skill_type = 'TASK'
476 );
477
478 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
479 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task required skills ');
480 END IF;
481
482 /* Step 8 - Delete Access Hours */
483 DELETE /*+ index(cahb) */ FROM CSF_ACCESS_HOURS_B cahb
484 WHERE
485 task_id in
486 (
487 SELECT /*+ no_unnest no_semijoin cardinality(10) */
488 csacc.task_id
489 FROM
490 CSF_ACCESS_HOURS_B csacc
491 , JTF_TASKS_B tskt
492 , JTF_OBJECT_PURGE_PARAM_TMP tmp
493 WHERE
494 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
495 nvl(tmp.purge_status, 'S') <> 'E' and
496 tmp.object_id = tskt.source_object_id and
497 tmp.object_type = tskt.source_object_type_code and
498 tskt.task_id = csacc.task_id
499 );
500
501
502 DELETE /*+ index(caht) */ FROM CSF_ACCESS_HOURS_TL caht
503 WHERE
504 access_hour_id in
505 (
506 SELECT /*+ no_unnest no_semijoin cardinality(10) */
507 csacc.access_hour_id
508 FROM
509 CSF_ACCESS_HOURS_B csacc
510 , JTF_TASKS_B tskt
511 , JTF_OBJECT_PURGE_PARAM_TMP tmp
512 WHERE
513 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
514 nvl(tmp.purge_status, 'S') <> 'E' and
515 tmp.object_id = tskt.source_object_id and
516 tmp.object_type = tskt.source_object_type_code and
517 tskt.task_id = csacc.task_id
518 );
519
520 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
521 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task access hours details ');
522 END IF;
526 DELETE /*+ index(crpo) */ FROM CSF_R_PLAN_OPTIONS crpo
523
524
525 /* Step 9 - Delete Plan Options */
527 WHERE
528 plan_option_id in
529 (
530 SELECT /*+ no_unnest no_semijoin cardinality(10) */
531 csactl.plan_option_id
532 FROM
533 CSF_R_PLAN_OPTIONS csactl
534 , CSF_R_PLAN_OPTION_TASKS csacc
535 , JTF_TASKS_B tskt
536 , JTF_OBJECT_PURGE_PARAM_TMP tmp
537 WHERE
538 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
539 nvl(tmp.purge_status, 'S') <> 'E' and
540 tmp.object_id = tskt.source_object_id and
541 tmp.object_type = tskt.source_object_type_code and
542 tskt.task_id = csacc.task_id and
543 csacc.plan_option_id = csactl.plan_option_id
544 );
545
546
547 DELETE /*+ index(crpot) */ FROM CSF_R_PLAN_OPTION_TASKS crpot
548 WHERE
549 task_id in
550 (
551 SELECT /*+ no_unnest no_semijoin cardinality(10) */
552 csacc.task_id
553 FROM
554 CSF_R_PLAN_OPTION_TASKS csacc
555 , JTF_TASKS_B tskt
556 , JTF_OBJECT_PURGE_PARAM_TMP tmp
557 WHERE
558 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
559 nvl(tmp.purge_status, 'S') <> 'E' and
560 tmp.object_id = tskt.source_object_id and
561 tmp.object_type = tskt.source_object_type_code and
562 tskt.task_id = csacc.task_id
563 );
564
565
566 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
567 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task plan options ');
568 END IF;
569
570 /* Step 10 - Delete Schedule Requests */
571 DELETE /*+ index(crsr) */ FROM CSF_R_SCHED_REQUESTS crsr
572 WHERE
573 SCHED_REQUEST_ID in
574 (
575 SELECT /*+ no_unnest no_semijoin cardinality(10) */
576 csactl.SCHED_REQUEST_ID
577 FROM
578 CSF_R_SCHED_REQUESTS csactl
579 , CSF_R_REQUEST_TASKS csacc
580 , JTF_TASKS_B tskt
581 , JTF_OBJECT_PURGE_PARAM_TMP tmp
582 WHERE
583 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
584 nvl(tmp.purge_status, 'S') <> 'E' and
585 tmp.object_id = tskt.source_object_id and
586 tmp.object_type = tskt.source_object_type_code and
587 tskt.task_id = csacc.REQUEST_TASK_ID and
588 csacc.SCHED_REQUEST_ID = csactl.SCHED_REQUEST_ID
589 );
590
591
592 DELETE /*+ index(crrt) */ FROM CSF_R_REQUEST_TASKS crrt
593 WHERE
594 REQUEST_TASK_ID in
595 (
596 SELECT /*+ no_unnest no_semijoin cardinality(10) */
597 csacc.REQUEST_TASK_ID
598 FROM
599 CSF_R_REQUEST_TASKS csacc
600 , JTF_TASKS_B tskt
601 , JTF_OBJECT_PURGE_PARAM_TMP tmp
602 WHERE
603 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
604 nvl(tmp.purge_status, 'S') <> 'E' and
605 tmp.object_id = tskt.source_object_id and
606 tmp.object_type = tskt.source_object_type_code and
607 tskt.task_id = csacc.REQUEST_TASK_ID
608 );
609
610 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
611 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task scheduled requests ');
612 END IF;
613
614 /* Step 11 - Delete Resource Results */
615 DELETE /*+ index(crso) */ FROM CSF_R_SPARES_OPTIONS crso
616 WHERE
617 RESOURCE_RESULT_ID in
618 (
619 SELECT /*+ no_unnest no_semijoin cardinality(10) */
620 csactl.RESOURCE_RESULT_ID
621 FROM
622 CSF_R_SPARES_OPTIONS csactl
623 , CSF_R_RESOURCE_RESULTS csacc
624 , JTF_TASKS_B tskt
625 , JTF_OBJECT_PURGE_PARAM_TMP tmp
626 WHERE
627 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
628 nvl(tmp.purge_status, 'S') <> 'E' and
629 tmp.object_id = tskt.source_object_id and
630 tmp.object_type = tskt.source_object_type_code and
631 tskt.task_id = csacc.REQUEST_TASK_ID and
632 csacc.RESOURCE_RESULT_ID = csactl.RESOURCE_RESULT_ID
633 );
634
635
639 (
636 DELETE /*+ index(crrr) */ FROM CSF_R_RESOURCE_RESULTS crrr
637 WHERE
638 REQUEST_TASK_ID in
640 SELECT /*+ no_unnest no_semijoin cardinality(10) */
641 csacc.REQUEST_TASK_ID
642 FROM
643 CSF_R_RESOURCE_RESULTS csacc
644 , JTF_TASKS_B tskt
645 , JTF_OBJECT_PURGE_PARAM_TMP tmp
646 WHERE
647 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
648 nvl(tmp.purge_status, 'S') <> 'E' and
649 tmp.object_id = tskt.source_object_id and
650 tmp.object_type = tskt.source_object_type_code and
651 tskt.task_id = csacc.REQUEST_TASK_ID
652 );
653
654
655 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
656 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task resource results ');
657 END IF;
658
659
660 /* Step 12 - Delete Messages and Message Tokens */
661 DELETE /*+ index(crmt) */ FROM CSF_R_MESSAGE_TOKENS crmt
662 WHERE
663 MESSAGE_ID in
664 (
665 SELECT /*+ no_unnest no_semijoin cardinality(10) */
666 csactl.MESSAGE_ID
667 FROM
668 CSF_R_MESSAGE_TOKENS csactl
669 , CSF_R_MESSAGES csacc
670 , JTF_TASKS_B tskt
671 , JTF_OBJECT_PURGE_PARAM_TMP tmp
672 WHERE
673 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
674 nvl(tmp.purge_status, 'S') <> 'E' and
675 tmp.object_id = tskt.source_object_id and
676 tmp.object_type = tskt.source_object_type_code and
677 tskt.task_id = csacc.REQUEST_TASK_ID and
678 csacc.MESSAGE_ID = csactl.MESSAGE_ID
679 );
680
681
682 DELETE /*+ index(crm) */ FROM CSF_R_MESSAGES crm
683 WHERE
684 REQUEST_TASK_ID in
685 (
686 SELECT /*+ no_unnest no_semijoin cardinality(10) */
687 csacc.REQUEST_TASK_ID
688 FROM
689 CSF_R_MESSAGES csacc
690 , JTF_TASKS_B tskt
691 , JTF_OBJECT_PURGE_PARAM_TMP tmp
692 WHERE
693 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
694 nvl(tmp.purge_status, 'S') <> 'E' and
695 tmp.object_id = tskt.source_object_id and
696 tmp.object_type = tskt.source_object_type_code and
697 tskt.task_id = csacc.REQUEST_TASK_ID
698 );
699
700 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
701 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task messages and message tokens ');
702 END IF;
703
704
705
706 delete cac_sr_object_capacity
707 where object_capacity_id in
708 (select cac.object_capacity_id
709 from
710 cac_sr_object_capacity cac
711 , JTF_TASKS_B tskt
712 , JTF_OBJECT_PURGE_PARAM_TMP tmp
713 , jtf_Task_assignments jtt
717 nvl(tmp.purge_status, 'S') <> 'E' and
714 , jtf_task_statuses_b jtfts
715 WHERE
716 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
718 tmp.object_id = tskt.source_object_id and
719 tmp.object_type = tskt.source_object_type_code and
720 tskt.task_id = jtt.TASK_ID and
721 jtt.object_capacity_id = cac.object_capacity_id and
722 jtt.assignment_status_id = jtfts.task_status_id and
723 (nvl(jtfts.closed_flag,'N') = 'Y' or
724 nvl(jtfts.completed_flag,'N') = 'Y' or
725 nvl(jtfts.cancelled_flag,'N') = 'Y') and
726 cac.end_date_time < sysdate and
727 not exists (select 1 from
728 jtf_Task_assignments jtts, jtf_task_statuses_b jtsts
729 where
730 jtts.object_capacity_id = cac.object_capacity_id and
731 jtts.task_id <> jtt.task_id and
732 jtt.assignment_status_id = jtfts.task_status_id and
733 (nvl(jtfts.closed_flag,'N') <> 'Y' and
734 nvl(jtfts.completed_flag,'N') <> 'Y' and
735 nvl(jtfts.cancelled_flag,'N') <> 'Y'))
736
737 );
738
739
740
741 update cac_sr_object_capacity cac set cac.available_hours =
742 ( SELECT (cac.END_DATE_TIME - cac.START_DATE_TIME) -
743 SUM(ta.booking_end_date - ta.booking_start_date) -
744 SUM(NVL(csf_util_pvt.convert_to_minutes(
745 ta.sched_travel_duration
746 , ta.sched_travel_duration_uom
747 ), 0)) /(24*60)
748 FROM jtf_task_assignments ta
749 WHERE ta.object_capacity_id = cac.object_capacity_id
750 )
751 where cac.start_date_time > sysdate and
752 cac.object_capacity_id in
753 (select cac.object_capacity_id
754 from
755 cac_sr_object_capacity cac
756 , JTF_TASKS_B tskt
757 , JTF_OBJECT_PURGE_PARAM_TMP tmp
758 , jtf_Task_assignments jtt
759 , jtf_task_statuses_b jtfts
760 WHERE
761 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
762 nvl(tmp.purge_status, 'S') <> 'E' and
763 tmp.object_id = tskt.source_object_id and
764 tmp.object_type = tskt.source_object_type_code and
765 tskt.task_id = jtt.TASK_ID and
766 jtt.object_capacity_id = cac.object_capacity_id and
767 jtt.assignment_status_id = jtfts.task_status_id and
768 (nvl(jtfts.closed_flag,'N') = 'Y' or
769 nvl(jtfts.completed_flag,'N') = 'Y' or
770 nvl(jtfts.cancelled_flag,'N') = 'Y'));
771
772 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
773 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS capacities ');
774 END IF;
775
776
777 -- Standard check of p_commit
778 -- IF fnd_api.to_boolean(p_commit) THEN
779 -- COMMIT WORK;
780 -- END IF;
781
782 END IF;
783
784
785 EXCEPTION
786 WHEN fnd_api.g_exc_error THEN
787 ROLLBACK TO Purge_FieldServiceObjects;
788 x_return_status := fnd_api.g_ret_sts_error;
789 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
790
791 WHEN fnd_api.g_exc_unexpected_error THEN
792 ROLLBACK TO Purge_FieldServiceObjects;
793 x_return_status := fnd_api.g_ret_sts_unexp_error;
794 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
795
796 WHEN OTHERS THEN
797 x_return_status := fnd_api.g_ret_sts_unexp_error;
798 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
799 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
800 END IF;
801 ROLLBACK TO Purge_FieldServiceObjects;
802
803
804 END;
805
806 -- Enter further code below as specified in the Package spec.
807 End CSF_MAINTAIN_GRP;
808