[Home] [Help]
PACKAGE BODY: APPS.CSF_MAINTAIN_GRP
Source
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
1 PACKAGE BODY CSF_MAINTAIN_GRP as
2 /* $Header: csfpurgb.pls 120.14 2011/12/15 18:55:42 hhaugeru ship $ */
3 --
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;
124 END IF;
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 ');
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
158 tmp.processing_set_id = P_PROCESSING_SET_ID
155 nvl(tmp.purge_status, 'S') <> 'E' and
156 jtftk.task_type_id = jttp.task_type_id and
157 jttp.rule = 'DISPATCH' and
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 CURSOR c_fetch_sched_req_id IS
210 (( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
211 CSACTL.SCHED_REQUEST_ID FROM CSF_R_SCHED_REQUESTS
212 CSACTL , CSF_R_REQUEST_TASKS CSACC , JTF_TASKS_B TSKT ,
213 JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
214 NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
215 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
216 CSACC.TASK_ID AND CSACC.SCHED_REQUEST_ID = CSACTL.SCHED_REQUEST_ID )
217 UNION ALL(SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
218 CSACTL.PARENT_REQUEST_ID FROM CSF_R_SCHED_REQUESTS
219 CSACTL , CSF_R_REQUEST_TASKS CSACC , JTF_TASKS_B TSKT ,
220 JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
221 NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
222 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
223 CSACC.TASK_ID AND CSACC.SCHED_REQUEST_ID = CSACTL.SCHED_REQUEST_ID));
224
225 l_tab_sched_req_id JTF_NUMBER_TABLE := JTF_NUMBER_TABLE();
226
227
228 BEGIN
232
229
230
231 SAVEPOINT Purge_FieldServiceObjects;
233 -- Standard call to check for call compatibility.
234 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
235 p_api_version,
236 l_api_name,
237 G_PKG_NAME)
238 THEN
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241
242
243 -- Initialize message list
244 FND_MSG_PUB.initialize;
245
246 IF (nvl(P_OBJECT_TYPE, 'SR') = 'SR') then
247
248 -- Initialize API return status to SUCCESS
249 x_return_status := FND_API.G_RET_STS_SUCCESS;
250
251 /* Delete Logic
252 1 Fetch the incident ids associated to the processing set id (i/p parameter) which are marked for purge
253 1.1 If no incident ids (for the given processing set id) require purging, Return Success
254 2 Fetch Field Service tasks belonging to the incident ids which are purge candidates
255 3 Fetch debrief headers that belong to the fetched tasks
256 4 Fetch debrief lines that belong to the fetched debrief headers
257 5 Fetch debrief notes that belong to the fetched debrief headers
258 6 Fetch parts requirement headers that belong to the fetched tasks
259 7 Fetch parts requirement lines that belong to the fetched parts requirement headers
260 8 Fetch parts requirement line details that belong to the fetched parts requirement headers
261 9 Fetch required skills that are associated to the fetched tasks
262 10 Fetch Schedule Requests
263 11 Fetch Resource Results
264 12 Fetch Messages and Message Tokens
265 13 Delete contents fetched from step 3 - step 12
266 14 Return Success if delete operation is successful
267 15 Return Failure if delete operation fails
268 */
269
270
271 /* Step 1 - Delete relevant debrief notes and note contexts */
272 DELETE /*+ index(jnc) */ FROM JTF_NOTE_CONTEXTS JNC
273 WHERE
274 JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
275 JTFNL.JTF_NOTE_ID FROM JTF_NOTE_CONTEXTS JTFNL ,
276 JTF_NOTES_B JTFNB , CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA ,
277 JTF_TASKS_B TSKT , JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
278 TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND
279 TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID AND TMP.OBJECT_TYPE =
280 TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID = TSKA.TASK_ID AND
281 TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID AND
282 DBFH.DEBRIEF_HEADER_ID = JTFNB.SOURCE_OBJECT_ID AND
283 JTFNB.SOURCE_OBJECT_CODE = 'SD' AND JTFNB.JTF_NOTE_ID = JTFNL.JTF_NOTE_ID );
284
285
286
287 DELETE /*+ index(jnt)*/ FROM JTF_NOTES_TL JNT
288 WHERE
289 JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
290 JTFNB.JTF_NOTE_ID FROM JTF_NOTES_B JTFNB ,
291 CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
292 JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
293 NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
294 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
295 TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID AND
296 DBFH.DEBRIEF_HEADER_ID = JTFNB.SOURCE_OBJECT_ID AND
297 JTFNB.SOURCE_OBJECT_CODE = 'SD' );
298
299
300 DELETE /*+ index(jnb)*/ FROM JTF_NOTES_B JNB
301 WHERE
302 JTF_NOTE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
306 NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
303 JTFNB.JTF_NOTE_ID FROM JTF_NOTES_B JTFNB ,
304 CSF_DEBRIEF_HEADERS DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
305 JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
307 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
308 TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID AND
309 DBFH.DEBRIEF_HEADER_ID = JTFNB.SOURCE_OBJECT_ID AND
310 JTFNB.SOURCE_OBJECT_CODE = 'SD' );
311
312 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
313 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task notes and note related objs ');
314 END IF;
315
316 /* Step 2 - Delete relevant debrief lines and debrief line related objects:
317 1) CSF_DEBRIEF_LINES
318 */
319
320 DELETE /*+ index (cdl)*/
321 FROM CSF_DEBRIEF_LINES CDL
322 WHERE DEBRIEF_LINE_ID IN
323 ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10)*/
324 DBFL.DEBRIEF_LINE_ID
325 FROM CSF_DEBRIEF_LINES DBFL ,
329 JTF_OBJECT_PURGE_PARAM_TMP TMP
326 CSF_DEBRIEF_HEADERS DBFH ,
327 JTF_TASK_ASSIGNMENTS TSKA ,
328 JTF_TASKS_B TSKT ,
330 WHERE TMP.PROCESSING_SET_ID = p_processing_set_id
331 AND NVL ( TMP.PURGE_STATUS , 'S' ) <> 'E'
332 AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
333 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE
334 AND TSKT.TASK_ID = TSKA.TASK_ID
335 AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID
336 AND DBFH.DEBRIEF_HEADER_ID = DBFL.DEBRIEF_HEADER_ID );
337
338 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
339 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task debriefed line details ');
340 END IF;
341
342
343 /* Step 3 - Delete relevant debrief headers*/
344 DELETE /*+ index(cdh) */ FROM CSF_DEBRIEF_HEADERS CDH
345 WHERE
346 DEBRIEF_HEADER_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
347 DBFH.DEBRIEF_HEADER_ID FROM CSF_DEBRIEF_HEADERS
348 DBFH , JTF_TASK_ASSIGNMENTS TSKA , JTF_TASKS_B TSKT ,
349 JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
350 NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
351 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
352 TSKA.TASK_ID AND TSKA.TASK_ASSIGNMENT_ID = DBFH.TASK_ASSIGNMENT_ID );
353
354 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
355 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task debrief header details ');
356 END IF;
357
358 /* Step 4 - Delete relevant requirement line details */
359 DELETE /*+ index(crld) */ FROM CSP_REQ_LINE_DETAILS crld
360 WHERE
361 req_line_detail_id in
362 (
363 SELECT /*+ cardinality(TMP, 10) */
364 csprdt.req_line_detail_id
365 FROM
366 CSP_REQ_LINE_DETAILS csprdt
367 , CSP_REQUIREMENT_LINES csprl
368 , CSP_REQUIREMENT_HEADERS csprh
369 , JTF_TASKS_B tskt
370 , JTF_OBJECT_PURGE_PARAM_TMP tmp
371 WHERE
372 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
373 nvl(tmp.purge_status, 'S') <> 'E' and
374 tmp.object_id = tskt.source_object_id and
375 tmp.object_type = tskt.source_object_type_code and
376 tskt.task_id = csprh.task_id and
377 csprh.requirement_header_id = csprl.requirement_header_id and
378 csprl.requirement_line_id = csprdt.requirement_line_id
379 );
380
381
382 /* Step 5 - Delete relevant requirement lines */
383 DELETE /*+ index(crl) */ FROM CSP_REQUIREMENT_LINES crl
384 WHERE
385 requirement_line_id in
386 (
387 SELECT /*+ cardinality(TMP, 10) */
388 csprl.requirement_line_id
389 FROM
390 CSP_REQUIREMENT_LINES csprl
391 , CSP_REQUIREMENT_HEADERS csprh
392 , JTF_TASKS_B tskt
393 , JTF_OBJECT_PURGE_PARAM_TMP tmp
394 WHERE
395 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
396 nvl(tmp.purge_status, 'S') <> 'E' and
397 tmp.object_id = tskt.source_object_id and
398 tmp.object_type = tskt.source_object_type_code and
399 tskt.task_id = csprh.task_id and
400 csprh.requirement_header_id = csprl.requirement_header_id
401 );
402
403
404
405 /* Step 6 - Delete relevant requirement headers */
406 DELETE /*+ index(crh) */ FROM CSP_REQUIREMENT_HEADERS crh
407 WHERE
408 requirement_header_id in
409 (
410 SELECT /*+ cardinality(TMP, 10) */
411 csprh.requirement_header_id
412 FROM
413 CSP_REQUIREMENT_HEADERS csprh
414 , JTF_TASKS_B tskt
415 , JTF_OBJECT_PURGE_PARAM_TMP tmp
416 WHERE
417 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
418 nvl(tmp.purge_status, 'S') <> 'E' and
419 tmp.object_id = tskt.source_object_id and
420 tmp.object_type = tskt.source_object_type_code and
421 tskt.task_id = csprh.task_id
422 );
423
424
425 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
426 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task requirement headers and lines ');
427 END IF;
428
429 /* Step 7 - Delete relevant required skills */
430 DELETE /*+ index(crsb) */ FROM CSF_REQUIRED_SKILLS_B CRSB
431 WHERE
432 REQUIRED_SKILL_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
433 CSFSK.REQUIRED_SKILL_ID FROM CSF_REQUIRED_SKILLS_B
434 CSFSK , JTF_TASKS_B TSKT , JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
435 TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND
436 TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID AND TMP.OBJECT_TYPE =
437 TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID = CSFSK.HAS_SKILL_ID AND
438 CSFSK.HAS_SKILL_TYPE = 'TASK' );
439
440 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
441 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task required skills ');
442 END IF;
443
444 /* Step 8 - Delete Access Hours */
445 DELETE /*+ index(caht) */ FROM CSF_ACCESS_HOURS_TL caht
446 WHERE
447 access_hour_id in
448 (
449 SELECT /*+ LEADING(TMP) no_unnest no_semijoin cardinality(TMP,
450 10) */
451 csacc.access_hour_id
452 FROM
453 CSF_ACCESS_HOURS_B csacc
454 , JTF_TASKS_B tskt
455 , JTF_OBJECT_PURGE_PARAM_TMP tmp
459 tmp.object_id = tskt.source_object_id and
456 WHERE
457 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
458 nvl(tmp.purge_status, 'S') <> 'E' and
460 tmp.object_type = tskt.source_object_type_code and
461 tskt.task_id = csacc.task_id
462 );
463
464 DELETE /*+ index(cahb) */ FROM CSF_ACCESS_HOURS_B cahb
465 WHERE
466 task_id in
467 (
468 SELECT /*+ cardinality(TMP, 10) */
469 csacc.task_id
470 FROM
471 CSF_ACCESS_HOURS_B csacc
472 , JTF_TASKS_B tskt
473 , JTF_OBJECT_PURGE_PARAM_TMP tmp
474 WHERE
475 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
476 nvl(tmp.purge_status, 'S') <> 'E' and
477 tmp.object_id = tskt.source_object_id and
478 tmp.object_type = tskt.source_object_type_code and
479 tskt.task_id = csacc.task_id
480 );
481
482
483 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
484 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task access hours details ');
485 END IF;
486
487
488 /* Step 9 - Delete Plan Options */
489 DELETE /*+ index(crpo) */ FROM CSF_R_PLAN_OPTIONS CRPO
490 WHERE
491 PLAN_OPTION_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
492 CSACTL.PLAN_OPTION_ID FROM CSF_R_PLAN_OPTIONS
493 CSACTL , CSF_R_PLAN_OPTION_TASKS CSACC , JTF_TASKS_B TSKT ,
494 JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
495 NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
496 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
497 CSACC.TASK_ID AND CSACC.PLAN_OPTION_ID = CSACTL.PLAN_OPTION_ID );
498
499
500 DELETE /*+ index(crpot) */ FROM CSF_R_PLAN_OPTION_TASKS CRPOT
501 WHERE
502 TASK_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
503 CSACC.TASK_ID FROM CSF_R_PLAN_OPTION_TASKS CSACC , JTF_TASKS_B TSKT ,
504 JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
505 NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
506 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
507 CSACC.TASK_ID );
508
509
510 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
511 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task plan options ');
512 END IF;
513
514 /* Step 10 - Delete Resource Results */
515 DELETE /*+ index(crso) */ FROM CSF_R_SPARES_OPTIONS CRSO
516 WHERE
517 RESOURCE_RESULT_ID IN (SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
518 CSACTL.RESOURCE_RESULT_ID FROM CSF_R_SPARES_OPTIONS
519 CSACTL , CSF_R_RESOURCE_RESULTS CSRR ,CSF_R_REQUEST_TASKS
520 CSACC, JTF_TASKS_B TSKT ,JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
521 TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND
522 NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID
523 AND TMP.OBJECT_TYPE = TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID =
524 CSACC.TASK_ID AND CSACC.REQUEST_TASK_ID=CSRR.REQUEST_TASK_ID
525 AND CSRR.RESOURCE_RESULT_ID =CSACTL.RESOURCE_RESULT_ID);
526
527
528 DELETE /*+ index(crrr) */ FROM CSF_R_RESOURCE_RESULTS CRRR
529 WHERE
530 REQUEST_TASK_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
531 CSACC.REQUEST_TASK_ID FROM CSF_R_RESOURCE_RESULTS CSRR ,CSF_R_REQUEST_TASKS
532 CSACC , JTF_TASKS_B TSKT , JTF_OBJECT_PURGE_PARAM_TMP TMP WHERE
533 TMP.PROCESSING_SET_ID = P_PROCESSING_SET_ID AND NVL(TMP.PURGE_STATUS, 'S') <> 'E' AND
534 TMP.OBJECT_ID = TSKT.SOURCE_OBJECT_ID AND TMP.OBJECT_TYPE =
535 TSKT.SOURCE_OBJECT_TYPE_CODE AND TSKT.TASK_ID = CSACC.TASK_ID AND
536 CSACC.REQUEST_TASK_ID=CSRR.REQUEST_TASK_ID);
537
538
539 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
540 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task resource results ');
541 END IF;
542
543 --Fetching all the available schedule request id including parent.
544 OPEN c_fetch_sched_req_id;
545 FETCH c_fetch_sched_req_id BULK COLLECT INTO l_tab_sched_req_id;
546 IF (c_fetch_sched_req_id%ISOPEN) THEN
547 CLOSE c_fetch_sched_req_id;
548 END IF;
549
550 /* Step 11 - Delete Messages and Message Tokens */
551 --FORALL i in l_tab_sched_req_id.first..l_tab_sched_req_id.last
552 DELETE /*+ index(crmt) */ FROM CSF_R_MESSAGE_TOKENS CRMT
553 WHERE
554 MESSAGE_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
555 CSACTL.MESSAGE_ID FROM CSF_R_MESSAGE_TOKENS CSACTL ,
556 CSF_R_MESSAGES CSRM, CSF_R_REQUEST_TASKS CSACC , CSF_R_SCHED_REQUESTS CSCHR
557 WHERE CSCHR.SCHED_REQUEST_ID IN (SELECT T.COLUMN_VALUE FROM TABLE(CAST(L_TAB_SCHED_REQ_ID AS JTF_NUMBER_TABLE)) T) AND
561 CSRM.MESSAGE_ID = CSACTL.MESSAGE_ID );
558 --WHERE CSCHR.SCHED_REQUEST_ID = l_tab_sched_req_id(i) AND
559 CSCHR.SCHED_REQUEST_ID = CSACC.SCHED_REQUEST_ID AND
560 CSACC.REQUEST_TASK_ID=CSRM.REQUEST_TASK_ID AND
562
563 --FORALL i in l_tab_sched_req_id.first..l_tab_sched_req_id.last
564 DELETE /*+ index(crm) */ FROM CSF_R_MESSAGES CRM
565 WHERE
566 REQUEST_TASK_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
567 CSACC.REQUEST_TASK_ID FROM CSF_R_MESSAGES CSRM ,
568 CSF_R_REQUEST_TASKS CSACC , CSF_R_SCHED_REQUESTS CSCHR
569 WHERE CSCHR.SCHED_REQUEST_ID IN (SELECT T.COLUMN_VALUE FROM TABLE(CAST(L_TAB_SCHED_REQ_ID AS JTF_NUMBER_TABLE)) T) AND
570 --WHERE CSCHR.SCHED_REQUEST_ID = l_tab_sched_req_id(i) AND
571 CSCHR.SCHED_REQUEST_ID = CSACC.SCHED_REQUEST_ID AND
572 CSACC.REQUEST_TASK_ID=CSRM.REQUEST_TASK_ID);
573
574 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
575 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task messages and message tokens ');
576 END IF;
577
578 /* Step 12 - Delete Request Task */
579 --FORALL i in l_tab_sched_req_id.first..l_tab_sched_req_id.last
580 DELETE /*+ index(crrt) */ FROM CSF_R_REQUEST_TASKS CRRT
581 WHERE
582 REQUEST_TASK_ID IN ( SELECT /*+ LEADING(TMP) cardinality(TMP, 10) */
583 CSACC.REQUEST_TASK_ID FROM CSF_R_REQUEST_TASKS
584 CSACC , CSF_R_SCHED_REQUESTS CSCHR
585 WHERE CSCHR.SCHED_REQUEST_ID IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(l_tab_sched_req_id AS JTF_NUMBER_TABLE)) t) AND
586 --WHERE CSCHR.SCHED_REQUEST_ID = l_tab_sched_req_id(i) AND
587 CSCHR.SCHED_REQUEST_ID = CSACC.SCHED_REQUEST_ID);
588
589
590 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
591 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task scheduled requests tasks');
592 END IF;
593
594 /* Step 13 - Delete Schedule Requests */
598 SCHED_REQUEST_ID IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(l_tab_sched_req_id AS JTF_NUMBER_TABLE)) t);
595 --FORALL i in l_tab_sched_req_id.first..l_tab_sched_req_id.last
596 DELETE /*+ index(crsr) */ FROM CSF_R_SCHED_REQUESTS CRSR
597 WHERE
599 --SCHED_REQUEST_ID = l_tab_sched_req_id(i);
600
601 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
602 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS task scheduled requests ');
603 END IF;
604
605 delete cac_sr_object_capacity
606 where object_capacity_id in
607 (select cac.object_capacity_id
608 from
609 cac_sr_object_capacity cac
610 , JTF_TASKS_B tskt
611 , JTF_OBJECT_PURGE_PARAM_TMP tmp
612 , jtf_Task_assignments jtt
613 , jtf_task_statuses_b jtfts
614 WHERE
615 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
616 nvl(tmp.purge_status, 'S') <> 'E' and
617 tmp.object_id = tskt.source_object_id and
618 tmp.object_type = tskt.source_object_type_code and
619 tskt.task_id = jtt.TASK_ID and
620 jtt.object_capacity_id = cac.object_capacity_id and
621 jtt.assignment_status_id = jtfts.task_status_id and
622 (nvl(jtfts.closed_flag,'N') = 'Y' or
623 nvl(jtfts.completed_flag,'N') = 'Y' or
624 nvl(jtfts.cancelled_flag,'N') = 'Y') and
625 cac.end_date_time < sysdate and
626 not exists (select 1 from
627 jtf_Task_assignments jtts, jtf_task_statuses_b jtsts
628 where
629 jtts.object_capacity_id = cac.object_capacity_id and
630 jtts.task_id <> jtt.task_id and
631 jtt.assignment_status_id = jtfts.task_status_id and
632 (nvl(jtfts.closed_flag,'N') <> 'Y' and
633 nvl(jtfts.completed_flag,'N') <> 'Y' and
634 nvl(jtfts.cancelled_flag,'N') <> 'Y'))
635
636 );
637
638
639
640 update cac_sr_object_capacity cac set cac.available_hours =
641 ( SELECT (cac.END_DATE_TIME - cac.START_DATE_TIME) -
642 SUM(ta.booking_end_date - ta.booking_start_date) -
643 SUM(NVL(csf_util_pvt.convert_to_minutes(
644 ta.sched_travel_duration
645 , ta.sched_travel_duration_uom
646 ), 0)) /(24*60)
647 FROM jtf_task_assignments ta
648 WHERE ta.object_capacity_id = cac.object_capacity_id
649 )
650 where cac.start_date_time > sysdate and
651 cac.object_capacity_id in
652 (select cac.object_capacity_id
653 from
654 cac_sr_object_capacity cac
655 , JTF_TASKS_B tskt
656 , JTF_OBJECT_PURGE_PARAM_TMP tmp
660 tmp.PROCESSING_SET_ID = P_PROCESSING_SET_ID and
657 , jtf_Task_assignments jtt
658 , jtf_task_statuses_b jtfts
659 WHERE
661 nvl(tmp.purge_status, 'S') <> 'E' and
662 tmp.object_id = tskt.source_object_id and
663 tmp.object_type = tskt.source_object_type_code and
664 tskt.task_id = jtt.TASK_ID and
665 jtt.object_capacity_id = cac.object_capacity_id and
666 jtt.assignment_status_id = jtfts.task_status_id and
667 (nvl(jtfts.closed_flag,'N') = 'Y' or
668 nvl(jtfts.completed_flag,'N') = 'Y' or
669 nvl(jtfts.cancelled_flag,'N') = 'Y'));
670
671 if((FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)) THEN
672 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE, 'CSF', 'In CSF_MAINTAIN_GRP.Purge_FieldServiceObjects() deleted FS capacities ');
673 END IF;
674
675
676 -- Standard check of p_commit
677 -- IF fnd_api.to_boolean(p_commit) THEN
678 -- COMMIT WORK;
679 -- END IF;
680
681 END IF;
682
683
684 EXCEPTION
685 WHEN fnd_api.g_exc_error THEN
686 ROLLBACK TO Purge_FieldServiceObjects;
687 x_return_status := fnd_api.g_ret_sts_error;
688 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
689
690 WHEN fnd_api.g_exc_unexpected_error THEN
691 ROLLBACK TO Purge_FieldServiceObjects;
692 x_return_status := fnd_api.g_ret_sts_unexp_error;
693 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
694
695 WHEN OTHERS THEN
696 x_return_status := fnd_api.g_ret_sts_unexp_error;
697 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
698 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
699 END IF;
700 ROLLBACK TO Purge_FieldServiceObjects;
701
702
703 END;
704
705 -- Enter further code below as specified in the Package spec.
706 End CSF_MAINTAIN_GRP;
707