[Home] [Help]
PACKAGE BODY: APPS.CSM_TASK_ASSIGNMENT_EVENT_PKG
Source
1 PACKAGE BODY CSM_TASK_ASSIGNMENT_EVENT_PKG AS
2 /* $Header: csmetab.pls 120.8 2006/09/15 13:00:29 trajasek noship $ */
3
4 /*** Globals ***/
5 g_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_TASK_ASSIGNMENTS_ACC';
6 g_table_name CONSTANT VARCHAR2(30) := 'JTF_TASK_ASSIGNMENTS';
7 g_acc_seq_name CONSTANT VARCHAR2(30) := 'CSM_TASK_ASSIGNMENTS_ACC_S' ;
8 g_pk1_name CONSTANT VARCHAR2(30) := 'TASK_ASSIGNMENT_ID';
9 g_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
10 CSM_ACC_PKG.t_publication_item_list('CSM_TASK_ASSIGNMENTS');
11
12 /**
13 ** Conc program called every midnight to purge task assignments depending on the
14 ** history profile of the user
15 **/
16 PROCEDURE PURGE_TASK_ASSIGNMENTS_CONC(p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
17 IS
18 PRAGMA AUTONOMOUS_TRANSACTION;
19 l_task_assignment_id jtf_task_assignments.task_assignment_id%TYPE;
20 l_task_id jtf_task_assignments.task_id%TYPE;
21 l_resource_id jtf_task_assignments.resource_id%TYPE;
22 l_user_id number;
23 l_dummy number;
24 l_last_run_date date;
25
26 l_sqlerrno VARCHAR2(20);
27 l_sqlerrmsg VARCHAR2(4000);
28 l_error_msg VARCHAR2(4000);
29 l_return_status VARCHAR2(2000);
30
31 CURSOR l_purge_task_assignments_csr
32 IS
33 SELECT /*+ INDEX (acc CSM_TASK_ASSIGNMENTS_ACC_U1)*/
34 acc.task_assignment_id,
35 jt.task_id,
36 jt.source_object_type_code
37 FROM csm_task_assignments_acc acc,
38 jtf_task_assignments jta,
39 jtf_tasks_b jt,
40 jtf_task_statuses_b jts,
41 jtf_task_statuses_b jts_jta
42 WHERE acc.task_assignment_id = jta.task_assignment_id
43 AND jt.task_id = jta.task_id
44 AND (jt.scheduled_start_date
45 < (SYSDATE - csm_profile_pkg.get_task_history_days(acc.user_id)))
46 AND jts.task_status_id = jt.task_status_id
47 AND jts_jta.task_status_id = jta.assignment_status_id
48 AND (jts.cancelled_flag = 'Y' OR jts.closed_flag = 'Y'
49 OR jts.completed_flag = 'Y' OR jts.rejected_flag = 'Y'
50 OR jts_jta.cancelled_flag = 'Y' OR jts_jta.closed_flag = 'Y'
51 OR jts_jta.completed_flag = 'Y' OR jts_jta.rejected_flag = 'Y')
52 AND NOT EXISTS (SELECT 'x'
53 FROM csm_service_history_acc hist,
54 cs_incidents_all_b cia,
55 cs_incident_statuses_b ists
56 WHERE hist.user_id = acc.user_id
57 AND hist.history_incident_id = jt.source_object_id
58 AND jt.source_object_type_code = 'SR'
59 AND hist.incident_id = cia.incident_id
60 AND cia.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
61 AND NVL(ists.CLOSE_FLAG, 'N') <> 'Y');
62
63 TYPE l_purge_task_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
64 TYPE l_task_src_type_tbl_type IS TABLE OF jtf_tasks_b.source_object_type_code%TYPE INDEX BY BINARY_INTEGER;
65
66 l_purge_task_assignment_tbl l_purge_task_tbl_type;
67 l_task_src_type_tbl l_task_src_type_tbl_type;
68 l_purge_task_tbl l_purge_task_tbl_type;
69
70 CURSOR l_upd_last_run_date_csr
71 IS
72 SELECT 1
73 FROM jtm_con_request_data
74 WHERE product_code = 'CSM'
75 AND package_name = 'CSM_TASK_ASSIGNMENT_EVENT_PKG'
76 AND procedure_name = 'PURGE_TASK_ASSIGNMENTS_CONC'
77 FOR UPDATE OF last_run_date NOWAIT
78 ;
79
80 BEGIN
81 l_last_run_date := SYSDATE;
82
83 OPEN l_purge_task_assignments_csr;
84 LOOP
85 IF l_purge_task_tbl.COUNT > 0 THEN
86 l_purge_task_tbl.DELETE;
87 END IF;
88
89 IF l_task_src_type_tbl.COUNT > 0 THEN
90 l_task_src_type_tbl.DELETE;
91 END IF;
92
93 FETCH l_purge_task_assignments_csr BULK COLLECT INTO l_purge_task_assignment_tbl,l_purge_task_tbl,l_task_src_type_tbl LIMIT 10;
94 EXIT WHEN l_purge_task_assignment_tbl.COUNT = 0;
95
96 IF l_purge_task_assignment_tbl.COUNT > 0 THEN
97 CSM_UTIL_PKG.LOG(TO_CHAR(l_purge_task_assignment_tbl.COUNT) || ' records sent for purge', 'CSM_TASK_ASSIGNMENT_EVENT_PKG.PURGE_TASK_ASSIGNMENTS_CONC',FND_LOG.LEVEL_EVENT);
98 FOR i IN l_purge_task_assignment_tbl.FIRST..l_purge_task_assignment_tbl.LAST LOOP
99
100 l_task_assignment_id := l_purge_task_assignment_tbl(i);
101 l_task_id := l_purge_task_tbl(i);
102 --Delete SR tasks only if the corresponding SR is closed
103 IF l_task_src_type_tbl(i) ='SR' AND CSM_SR_EVENT_PKG.IS_SR_OPEN(l_task_id) = FALSE THEN
104
105 csm_task_assignment_event_pkg.TASK_ASSIGNMENT_PURGE_INIT(p_task_assignment_id=>l_task_assignment_id,
106 p_error_msg=>l_error_msg,
107 x_return_status=>l_return_status);
108 ELSIF l_task_src_type_tbl(i) ='TASK' THEN
109 csm_task_assignment_event_pkg.TASK_ASSIGNMENT_PURGE_INIT(p_task_assignment_id=>l_task_assignment_id,
110 p_error_msg=>l_error_msg,
111 x_return_status=>l_return_status);
112
113 END IF;
114 END LOOP;
115 END IF;
116 -- commit after every 10 records
117 COMMIT;
118 END LOOP;
119 CLOSE l_purge_task_assignments_csr;
120 -- update last_run_date
121 OPEN l_upd_last_run_date_csr;
122 FETCH l_upd_last_run_date_csr INTO l_dummy;
123 IF l_upd_last_run_date_csr%FOUND THEN
124 UPDATE jtm_con_request_data
125 SET last_run_date = l_last_run_date
126 WHERE CURRENT OF l_upd_last_run_date_csr;
127 END IF;
128 CLOSE l_upd_last_run_date_csr;
129 COMMIT;
130
131 p_status := 'SUCCESS';
132 p_message := 'CSM_TASK_ASSIGNMENT_EVENT_PKG.PURGE_TASK_ASSIGNMENTS_CONC Executed successfully';
133
134 EXCEPTION
135 WHEN OTHERS THEN
136 l_sqlerrno := to_char(SQLCODE);
137 l_sqlerrmsg := substr(SQLERRM, 1,2000);
138 ROLLBACK;
139 l_error_msg := ' Exception in PURGE_TASK_ASSIGNMENTS_CONC for task_assignment_id:' || to_char(l_task_assignment_id)
140 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
141 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.PURGE_TASK_ASSIGNMENTS_CONC',FND_LOG.LEVEL_EVENT);
142 p_status := 'ERROR';
143 p_message := 'Error in CSM_TASK_ASSIGNMENT_EVENT_PKG.PURGE_TASK_ASSIGNMENTS_CONC: ' || l_error_msg;
144 -- x_return_status := FND_API.G_RET_STS_ERROR ;
145 -- RAISE;
146 END PURGE_TASK_ASSIGNMENTS_CONC;
147
148 PROCEDURE SPAWN_DEBRIEF_HEADER_INS (p_task_assignment_id IN NUMBER,
149 p_user_id IN NUMBER,
150 p_flow_type IN VARCHAR2)
151 IS
152 l_sqlerrno VARCHAR2(20);
153 l_sqlerrmsg VARCHAR2(4000);
154 l_error_msg VARCHAR2(4000);
155 l_return_status VARCHAR2(2000);
156
157 -- cursor to get all debrief header id
158 CURSOR l_debrief_header_csr(p_task_assg_id jtf_task_assignments.task_assignment_id%TYPE)
159 IS
160 SELECT dh.debrief_header_id
161 FROM csf_debrief_headers dh
162 WHERE dh.task_assignment_id =p_task_assg_id
163 AND NOT EXISTS
164 (SELECT 1
165 FROM csm_debrief_headers_acc acc
166 WHERE acc.debrief_header_id = dh.debrief_header_id
167 AND acc.user_id = p_user_id);
168
169 BEGIN
170 CSM_UTIL_PKG.LOG('Entering SPAWN_DEBRIEF_HEADER_INS for task_assignment_id: ' || p_task_assignment_id,
171 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_INS',FND_LOG.LEVEL_PROCEDURE);
172
173 FOR r_debrief_header_rec IN l_debrief_header_csr(p_task_assignment_id) LOOP
174 -- insert debrief headers
175 csm_debrief_header_event_pkg.debrief_header_ins_init(p_debrief_header_id=>r_debrief_header_rec.debrief_header_id,
176 p_h_user_id=>p_user_id,
177 p_flow_type=>p_flow_type);
178 END LOOP;
179
180 CSM_UTIL_PKG.LOG('Leaving SPAWN_DEBRIEF_HEADER_INS for task_assignment_id: ' || p_task_assignment_id,
181 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_INS',FND_LOG.LEVEL_PROCEDURE);
182 EXCEPTION
183 WHEN OTHERS THEN
184 l_sqlerrno := to_char(SQLCODE);
185 l_sqlerrmsg := substr(SQLERRM, 1,2000);
186 l_error_msg := ' Exception in SPAWN_DEBRIEF_HEADER_INS for task_assignment_id:'
187 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
188 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_INS',FND_LOG.LEVEL_EXCEPTION);
189 RAISE;
190 END SPAWN_DEBRIEF_HEADER_INS;
191
192 PROCEDURE SPAWN_DEBRIEF_LINE_INS (p_task_assignment_id IN NUMBER,
193 p_user_id IN NUMBER,
194 p_flow_type IN VARCHAR2)
195 IS
196 l_sqlerrno VARCHAR2(20);
197 l_sqlerrmsg VARCHAR2(4000);
198 l_error_msg VARCHAR2(4000);
199 l_return_status VARCHAR2(2000);
200
201 -- cursor to get all debrief line id
202 CURSOR l_debrief_lines_csr(p_task_assg_id jtf_task_assignments.task_assignment_id%TYPE)
203 IS
204 SELECT dl.debrief_line_id,
205 dh.debrief_header_id
206 FROM csf_debrief_lines dl,
207 csf_debrief_headers dh
208 WHERE dh.task_assignment_id =p_task_assg_id
209 AND dl.debrief_header_id = dh.debrief_header_id
210 AND NOT EXISTS
211 (SELECT 1
212 FROM csm_debrief_lines_acc acc
213 WHERE acc.debrief_line_id = dl.debrief_line_id
214 AND acc.user_id = p_user_id);
215
216 BEGIN
217 CSM_UTIL_PKG.LOG('Entering SPAWN_DEBRIEF_LINE_INS for task_assignment_id: ' || p_task_assignment_id,
218 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_LINE_INS',FND_LOG.LEVEL_PROCEDURE);
219
220 FOR r_debrief_line_rec IN l_debrief_lines_csr(p_task_assignment_id) LOOP
221 -- insert debrief lines
222 csm_debrief_event_pkg.debrief_line_ins_init(p_debrief_line_id=>r_debrief_line_rec.debrief_line_id,
223 p_h_user_id=>p_user_id,
224 p_flow_type=>p_flow_type);
225 END LOOP;
226
227 CSM_UTIL_PKG.LOG('Leaving SPAWN_DEBRIEF_LINE_INS for task_assignment_id: ' || p_task_assignment_id,
228 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_LINE_INS',FND_LOG.LEVEL_PROCEDURE);
229 EXCEPTION
230 WHEN OTHERS THEN
231 l_sqlerrno := to_char(SQLCODE);
232 l_sqlerrmsg := substr(SQLERRM, 1,2000);
233 l_error_msg := ' Exception in SPAWN_DEBRIEF_LINE_INS for task_assignment_id:'
234 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
235 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_LINE_INS',FND_LOG.LEVEL_EXCEPTION);
236 RAISE;
237 END SPAWN_DEBRIEF_LINE_INS;
238
239 PROCEDURE SPAWN_REQUIREMENT_HEADER_INS(p_task_assignment_id IN NUMBER,
240 p_user_id IN NUMBER,
241 p_flow_type IN VARCHAR2)
242 IS
243 l_sqlerrno VARCHAR2(20);
244 l_sqlerrmsg VARCHAR2(4000);
245 l_error_msg VARCHAR2(4000);
246 l_return_status VARCHAR2(2000);
247
248 -- cursor to get all requirement headers for the task_id
249 CURSOR l_req_headers_csr(p_task_assg_id IN jtf_task_assignments.task_assignment_id%TYPE,
250 p_user_id IN NUMBER)
251 IS
252 SELECT hdr.requirement_header_id,
253 jta.resource_id
254 FROM jtf_task_assignments jta,
255 csp_requirement_headers hdr
256 WHERE jta.task_assignment_id = p_task_assg_id
257 AND hdr.task_id = jta.task_id
258 AND NOT EXISTS
259 (SELECT 1
260 FROM csm_req_headers_acc acc
261 WHERE acc.requirement_header_id = hdr.requirement_header_id
262 AND acc.user_id = p_user_id
263 );
264
265 BEGIN
266 CSM_UTIL_PKG.LOG('Entering SPAWN_REQUIREMENT_HEADER_INS for task_assignment_id: ' || p_task_assignment_id,
267 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_INS',FND_LOG.LEVEL_PROCEDURE);
268
269 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
270 FOR r_req_headers_rec IN l_req_headers_csr(p_task_assignment_id, p_user_id) LOOP
271 -- insert requirement headers
272 csm_csp_req_headers_event_pkg.csp_req_headers_mdirty_i(p_requirement_header_id=>r_req_headers_rec.requirement_header_id,
273 p_user_id=>p_user_id);
274 END LOOP;
275 END IF;
276
277 CSM_UTIL_PKG.LOG('Leaving SPAWN_REQUIREMENT_HEADER_INS for task_assignment_id: ' || p_task_assignment_id,
278 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_INS',FND_LOG.LEVEL_PROCEDURE);
279 EXCEPTION
280 WHEN OTHERS THEN
281 l_sqlerrno := to_char(SQLCODE);
282 l_sqlerrmsg := substr(SQLERRM, 1,2000);
283 l_error_msg := ' Exception in SPAWN_REQUIREMENT_HEADER_INS for task_assignment_id:'
284 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
285 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_INS',FND_LOG.LEVEL_EXCEPTION);
286 RAISE;
287 END SPAWN_REQUIREMENT_HEADER_INS;
288
289 PROCEDURE SPAWN_REQUIREMENT_LINES_INS(p_task_assignment_id IN NUMBER,
290 p_user_id IN NUMBER,
291 p_flow_type IN VARCHAR2)
292 IS
293 l_sqlerrno VARCHAR2(20);
294 l_sqlerrmsg VARCHAR2(4000);
295 l_error_msg VARCHAR2(4000);
296 l_return_status VARCHAR2(2000);
297
298 -- cursor to get all requirement lines for the task_id
299 CURSOR l_req_lines_csr(p_task_assg_id IN jtf_task_assignments.task_assignment_id%TYPE,
300 p_user_id IN NUMBER)
301 IS
302 SELECT line.requirement_line_id,
303 line.requirement_header_id,
304 jta.resource_id
305 FROM jtf_task_assignments jta,
306 csp_requirement_headers hdr,
307 csp_requirement_lines line
308 WHERE jta.task_assignment_id = p_task_assg_id
309 AND hdr.task_id = jta.task_id
310 AND line.requirement_header_id = hdr.requirement_header_id
311 AND NOT EXISTS
312 (SELECT 1
313 FROM csm_req_lines_acc acc
314 WHERE acc.requirement_line_id = line.requirement_line_id
315 AND acc.user_id = p_user_id
316 );
317
318 BEGIN
319 CSM_UTIL_PKG.LOG('Entering SPAWN_REQUIREMENTS_INS for task_assignment_id: ' || p_task_assignment_id,
320 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENTS_INS',FND_LOG.LEVEL_PROCEDURE);
321
322 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
323 FOR r_req_lines_rec IN l_req_lines_csr(p_task_assignment_id, p_user_id) LOOP
324 -- insert requirement lines
325 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_i(p_requirement_line_id=>r_req_lines_rec.requirement_line_id,
326 p_user_id=>p_user_id);
327 END LOOP;
328 END IF;
329
330 CSM_UTIL_PKG.LOG('Leaving SPAWN_REQUIREMENTS_INS for task_assignment_id: ' || p_task_assignment_id,
331 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENTS_INS',FND_LOG.LEVEL_PROCEDURE);
332 EXCEPTION
333 WHEN OTHERS THEN
334 l_sqlerrno := to_char(SQLCODE);
335 l_sqlerrmsg := substr(SQLERRM, 1,2000);
336 l_error_msg := ' Exception in SPAWN_REQUIREMENTS_INS for task_assignment_id:'
337 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
338 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENTS_INS',FND_LOG.LEVEL_EXCEPTION);
339 RAISE;
340 END SPAWN_REQUIREMENT_LINES_INS;
341
342 PROCEDURE TASK_ASSIGNMENTS_ACC_PROCESSOR(p_task_assignment_id IN NUMBER,
343 p_incident_id IN NUMBER,
344 p_task_id IN NUMBER,
345 p_source_object_type_code IN VARCHAR2,
346 p_flow_type IN VARCHAR2,
347 p_user_id IN NUMBER)
348 IS
349 l_sqlerrno VARCHAR2(20);
350 l_sqlerrmsg VARCHAR2(4000);
351 l_error_msg VARCHAR2(4000);
352 l_return_status VARCHAR2(2000);
353 l_dummy NUMBER;
354
355 CURSOR l_task_access_csr(p_task_id IN NUMBER, p_user_id IN NUMBER)
356 IS
357 SELECT 1
358 FROM csm_tasks_acc
359 WHERE user_id = p_user_id
360 AND task_id = p_task_id;
361
362 BEGIN
363 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENTS_ACC_PROCESSOR for task_assignment_id: ' || p_task_assignment_id,
364 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
365
366 IF p_source_object_type_code = 'SR' THEN
367 csm_sr_event_pkg.incidents_acc_i(p_incident_id=>p_incident_id,
368 p_user_id=>p_user_id);
369
370 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
371 csm_contract_event_pkg.sr_contract_acc_i(p_incident_id=>p_incident_id,
372 p_user_id=>p_user_id);
373 END IF ;
374 END IF;
375
376 -- check if user already has access to the task. if already present then do not re-insert
377 -- since a task can be assigned to a user only once..multiple assignments are not supported
378
379 OPEN l_task_access_csr(p_task_id=>p_task_id, p_user_id=>p_user_id);
380 FETCH l_task_access_csr INTO l_dummy;
381 IF l_task_access_csr%NOTFOUND THEN
382 -- get notes only if it not history
383 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
384 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'TASK',
385 p_sourceobjectid=>p_task_id,
386 p_userid=>p_user_id,
387 p_error_msg=>l_error_msg,
388 x_return_status=>l_return_status);
389 END IF;
390
391 csm_task_event_pkg.acc_insert(p_task_id=>p_task_id, p_user_id=>p_user_id);
392 END IF;
393 CLOSE l_task_access_csr;
394
395 csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>p_task_assignment_id,
396 p_user_id=>p_user_id);
397
398 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENTS_ACC_PROCESSOR for task_assignment_id: ' || p_task_assignment_id,
399 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
400 EXCEPTION
401 WHEN OTHERS THEN
402 l_sqlerrno := to_char(SQLCODE);
403 l_sqlerrmsg := substr(SQLERRM, 1,2000);
404 l_error_msg := ' Exception in TASK_ASSIGNMENTS_ACC_PROCESSOR for task_assignment_id:'
405 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
406 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_PROCESSOR',FND_LOG.LEVEL_EXCEPTION);
407 RAISE;
408 END TASK_ASSIGNMENTS_ACC_PROCESSOR;
409
410 PROCEDURE ACC_INSERT(p_task_assignment_id IN NUMBER, p_user_id IN NUMBER)
411 IS
412 l_sqlerrno VARCHAR2(20);
413 l_sqlerrmsg VARCHAR2(4000);
414 l_error_msg VARCHAR2(4000);
415 l_return_status VARCHAR2(2000);
416
417 BEGIN
418 CSM_UTIL_PKG.LOG('Entering ACC_INSERT for task_assignment_id: ' || p_task_assignment_id,
419 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_PROCEDURE);
420
421 CSM_ACC_PKG.Insert_Acc
422 ( P_PUBLICATION_ITEM_NAMES => g_pubi_name
423 ,P_ACC_TABLE_NAME => g_acc_table_name
424 ,P_SEQ_NAME => g_acc_seq_name
425 ,P_PK1_NAME => g_pk1_name
426 ,P_PK1_NUM_VALUE => p_task_assignment_id
427 ,P_USER_ID => p_user_id
428 );
429
430 CSM_UTIL_PKG.LOG('Leaving ACC_INSERT for task_assignment_id: ' || p_task_assignment_id,
431 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_PROCEDURE);
432 EXCEPTION
433 WHEN OTHERS THEN
434 l_sqlerrno := to_char(SQLCODE);
435 l_sqlerrmsg := substr(SQLERRM, 1,2000);
436 l_error_msg := ' Exception in ACC_INSERT for task_assignment_id:'
437 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
438 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_EXCEPTION);
439 RAISE;
440 END ACC_INSERT;
441
442 --Bug 4938130
443 PROCEDURE LOBS_MDIRTY_I(p_task_assignment_id IN NUMBER, p_resource_id IN NUMBER)
444 IS
445 BEGIN
446 CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD(p_task_assignment_id, p_resource_id);
447 END LOBS_MDIRTY_I;
448
449 PROCEDURE TASK_ASSIGNMENT_INITIALIZER (p_task_assignment_id IN NUMBER,
450 p_error_msg OUT NOCOPY VARCHAR2,
451 x_return_status IN OUT NOCOPY VARCHAR2)
452 IS
453 l_sqlerrno VARCHAR2(20);
454 l_sqlerrmsg VARCHAR2(4000);
455 l_error_msg VARCHAR2(4000);
456 l_return_status VARCHAR2(2000);
457 l_organization_id NUMBER;
458 l_is_synchronous_history VARCHAR2(1);
459
460 --Bug 5220635
461 CURSOR l_TaskAssgDetails_csr (p_taskassgid number) IS
462 SELECT au.user_id,
463 jta.resource_id,
464 jta.task_id,
465 csi.incident_id,
466 csi.customer_id,
467 hz_ps.party_site_id,
468 hz_ps.party_id,
469 csi.inventory_item_id,
470 csi.inv_organization_id,
471 csi.contract_service_id,
472 csi.customer_product_id,
473 hz_ps.location_id,
474 jt.source_object_type_code,
475 csi.incident_location_type
476 FROM JTF_TASK_ASSIGNMENTS jta,
477 asg_user au,
478 asg_user_pub_resps aupr,
479 jtf_tasks_b jt,
480 cs_incidents_all_b csi,
481 hz_party_sites hz_ps
482 WHERE jta.task_assignment_id = p_taskassgid
483 AND jta.assignee_role = 'ASSIGNEE'
484 AND au.resource_id = jta.resource_id
485 AND au.user_name = aupr.user_name
486 AND aupr.pub_name = 'SERVICEP'
487 AND jt.task_id = jta.task_id
488 AND jt.source_object_type_code = 'SR'
489 AND jt.source_object_id = csi.incident_id
490 AND hz_ps.party_site_id = NVL(csi.incident_location_id, jt.ADDRESS_ID)
491 AND NVL(csi.incident_location_type,'HZ_PARTY_SITE')='HZ_PARTY_SITE'
492 UNION
493 SELECT au.user_id,
494 jta.resource_id,
495 jta.task_id,
496 csi.incident_id,
497 csi.customer_id,
498 NULL,
499 csi.customer_id,
500 csi.inventory_item_id,
501 csi.inv_organization_id,
502 csi.contract_service_id,
503 csi.customer_product_id,
504 lc.location_id,
505 jt.source_object_type_code,
506 csi.incident_location_type
507 FROM JTF_TASK_ASSIGNMENTS jta,
508 asg_user au,
509 asg_user_pub_resps aupr,
510 jtf_tasks_b jt,
511 cs_incidents_all_b csi,
512 hz_locations lc
513 WHERE jta.task_assignment_id = p_taskassgid
514 AND jta.assignee_role = 'ASSIGNEE'
515 AND au.resource_id = jta.resource_id
516 AND au.user_name = aupr.user_name
517 AND aupr.pub_name = 'SERVICEP'
518 AND jt.task_id = jta.task_id
519 AND jt.source_object_type_code = 'SR'
520 AND jt.source_object_id = csi.incident_id
521 AND lc.location_id = NVL(jt.LOCATION_ID,csi.incident_location_id)
522 AND csi.incident_location_type='HZ_LOCATION'
523 UNION
524 SELECT au.user_id,
525 jta.resource_id,
526 jta.task_id,
527 to_number(NULL),
528 to_number(NULL),
529 to_number(NULL),
530 to_number(NULL),
531 to_number(NULL),
532 to_number(NULL),
533 to_number(NULL),
534 to_number(NULL),
535 to_number(NULL),
536 jt.source_object_type_code,
537 to_char(NULL)
538 FROM JTF_TASK_ASSIGNMENTS jta,
539 asg_user au,
540 asg_user_pub_resps aupr,
541 jtf_tasks_b jt
542 WHERE jta.task_assignment_id = p_taskassgid
543 AND jta.assignee_role = 'ASSIGNEE'
544 AND au.resource_id = jta.resource_id
545 AND au.user_name = aupr.user_name
546 AND aupr.pub_name = 'SERVICEP'
547 AND jt.task_id = jta.task_id
548 AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL);
549
550 l_TaskAssgDetails_rec l_TaskAssgDetails_csr%ROWTYPE;
551 l_TaskAssgDetails_null_rec l_TaskAssgDetails_csr%ROWTYPE;
552
553 BEGIN
554 x_return_status := FND_API.G_RET_STS_SUCCESS;
555 CSM_UTIL_PKG.LOG('Entering CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER for task_assignment_id: ' || p_task_assignment_id,
556 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
557
558 l_TaskAssgDetails_rec := l_TaskAssgDetails_null_rec;
559
560 OPEN l_TaskAssgDetails_csr(p_task_assignment_id);
561 FETCH l_TaskAssgDetails_csr INTO l_taskassgdetails_rec;
562 IF l_taskassgdetails_csr%NOTFOUND THEN
563 CLOSE l_taskassgdetails_csr;
564 CSM_UTIL_PKG.LOG('Not a mobile task_assignment_id: ' || p_task_assignment_id,
565 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
566 RETURN;
567 END IF;
568 CLOSE l_taskassgdetails_csr;
569
570 -- check if its a SR Task
571 IF NOT csm_sr_event_pkg.is_sr_task(l_taskassgdetails_rec.task_id) THEN
572 RETURN;
573 END IF;
574
575 -- check if task status is downloadable
576 IF NOT csm_sr_event_pkg.is_task_status_downloadable(l_taskassgdetails_rec.task_id) THEN
577 RETURN;
578 END IF;
579
580 -- check if task assignment status is downloadable
581 IF NOT csm_sr_event_pkg.is_assgn_status_downloadable(p_task_assignment_id) THEN
582 RETURN;
583 END IF;
584
585 -- get Service Inv Validation org
586 l_organization_id := csm_profile_pkg.get_organization_id(l_taskassgdetails_rec.user_id);
587
588 --get task notes moved to task_assignments_acc processor
589
590
591 --get SR notes
592 IF l_taskassgdetails_rec.incident_id IS NOT NULL THEN
593 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'SR',
594 p_sourceobjectid=>l_taskassgdetails_rec.incident_id,
595 p_userid=>l_taskassgdetails_rec.user_id,
596 p_error_msg=>l_error_msg,
597 x_return_status=>l_return_status);
598 END IF;
599
600 --get contract notes
601 IF l_taskassgdetails_rec.contract_service_id IS NOT NULL THEN
602 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'OKS_COV_NOTE',
603 p_sourceobjectid=>l_taskassgdetails_rec.contract_service_id,
604 p_userid=>l_taskassgdetails_rec.user_id,
605 p_error_msg=>l_error_msg,
606 x_return_status=>l_return_status);
607 END IF;
608
609 -- get SR contacts
610 IF l_taskassgdetails_rec.incident_id IS NOT NULL THEN
611 csm_sr_event_pkg.spawn_sr_contacts_ins(p_incident_id=>l_taskassgdetails_rec.incident_id,
612 p_user_id=>l_taskassgdetails_rec.user_id,
613 p_flowtype=>NULL);
614 END IF;
615
616 IF l_taskassgdetails_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
617 --insert location for the task
618 CSM_HZ_LOCATIONS_EVENT_PKG.insert_location(p_location_id => l_taskassgdetails_rec.location_id,
619 p_user_id => l_taskassgdetails_rec.user_id);
620 ELSE
621 -- spawn party site ins
622 IF l_taskassgdetails_rec.party_site_id IS NOT NULL THEN
623 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_taskassgdetails_rec.party_site_id,
624 p_user_id => l_taskassgdetails_rec.user_id,
625 p_flowtype => NULL,
626 p_error_msg => l_error_msg,
627 x_return_status => l_return_status);
628 END IF;
629 END IF;
630
631 -- spawn SR customer ins
632 IF l_taskassgdetails_rec.customer_id IS NOT NULL THEN
633 csm_party_event_pkg.party_acc_i(p_party_id => l_taskassgdetails_rec.customer_id,
634 p_user_id => l_taskassgdetails_rec.user_id,
635 p_flowtype => NULL,
636 p_error_msg => l_error_msg,
637 x_return_status => l_return_status);
638
639 --insert Accounts for the above party-R12
640 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS
641 (p_party_id=> l_taskassgdetails_rec.customer_id
642 ,p_user_id => l_taskassgdetails_rec.user_id);
643
644 END IF;
645
646 IF l_taskassgdetails_rec.customer_product_id IS NOT NULL THEN
647 -- spawn SR item instance insert
648 csm_sr_event_pkg.sr_item_ins_init(p_incident_id=>l_taskassgdetails_rec.incident_id,
649 p_instance_id=>l_taskassgdetails_rec.customer_product_id,
650 p_party_site_id=>l_taskassgdetails_rec.party_site_id,
651 p_party_id=>l_taskassgdetails_rec.party_id,
652 p_location_id=>l_taskassgdetails_rec.location_id,
653 p_organization_id=>NVL(l_taskassgdetails_rec.inv_organization_id, l_organization_id),
654 p_user_id=>l_taskassgdetails_rec.user_id,
655 p_flow_type=>NULL);
656
657 ELSIF l_taskassgdetails_rec.customer_product_id IS NULL OR l_taskassgdetails_rec.customer_product_id = 0 THEN
658 IF l_taskassgdetails_rec.inventory_item_id IS NOT NULL THEN
659 csm_mtl_system_items_event_pkg.mtl_system_items_acc_i
660 (p_inventory_item_id=>l_taskassgdetails_rec.inventory_item_id,
661 p_organization_id=>NVL(l_taskassgdetails_rec.inv_organization_id, l_organization_id),
662 p_user_id=>l_taskassgdetails_rec.user_id,
663 p_error_msg=>l_error_msg,
664 x_return_status=>l_return_status);
665 END IF;
666 END IF;
667
668 -- spawn debrief line ins
669 csm_task_assignment_event_pkg.spawn_debrief_line_ins(p_task_assignment_id=>p_task_assignment_id,
670 p_user_id=>l_taskassgdetails_rec.user_id,
671 p_flow_type=>NULL);
672
673 -- spawn debrief header ins
674 csm_task_assignment_event_pkg.spawn_debrief_header_ins(p_task_assignment_id=>p_task_assignment_id,
675 p_user_id=>l_taskassgdetails_rec.user_id,
676 p_flow_type=>NULL);
677
678 -- spawn requirement lines ins
679 csm_task_assignment_event_pkg.spawn_requirement_lines_ins(p_task_assignment_id=>p_task_assignment_id,
680 p_user_id=>l_taskassgdetails_rec.user_id,
681 p_flow_type=>NULL);
682
683 -- spawn requirement headers ins
684 csm_task_assignment_event_pkg.spawn_requirement_header_ins(p_task_assignment_id=>p_task_assignment_id,
685 p_user_id=>l_taskassgdetails_rec.user_id,
686 p_flow_type=>NULL);
687
688 -- task_assignments_acc processor
689 csm_task_assignment_event_pkg.task_assignments_acc_processor
690 (p_task_assignment_id=>p_task_assignment_id,
691 p_incident_id=>l_taskassgdetails_rec.incident_id,
692 p_task_id=>l_taskassgdetails_rec.task_id,
693 p_source_object_type_code=>l_taskassgdetails_rec.source_object_type_code,
694 p_flow_type=>NULL,
695 p_user_id=>l_taskassgdetails_rec.user_id);
696
697 -- get synchronous history
698 IF l_taskassgdetails_rec.incident_id IS NOT NULL THEN
699 l_is_synchronous_history := fnd_profile.value('CSM_SYNCHRONOUS_HISTORY');
700
701 IF l_is_synchronous_history = 'Y' THEN
702 csm_service_history_event_pkg.calculate_history(l_incident_id=>l_taskassgdetails_rec.incident_id,
703 l_user_id=>l_taskassgdetails_rec.user_id);
704 END IF;
705 END IF;
706
707 -- lobs mdirty I
708 csm_task_assignment_event_pkg.lobs_mdirty_i(p_task_assignment_id=>p_task_assignment_id,
709 p_resource_id=>l_taskassgdetails_rec.resource_id);
710
711 CSM_UTIL_PKG.LOG('Leaving CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER for task_assignment_id: ' || p_task_assignment_id,
712 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
713 EXCEPTION
714 WHEN OTHERS THEN
715 l_sqlerrno := to_char(SQLCODE);
716 l_sqlerrmsg := substr(SQLERRM, 1,2000);
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 p_error_msg := ' Exception in TASK_ASSIGNMENT_INITIALIZER for task_assignment_id:' || to_char(p_task_assignment_id)
719 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
720 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_EXCEPTION);
721 RAISE;
722 END TASK_ASSIGNMENT_INITIALIZER;
723
724 PROCEDURE TASK_ASSIGNMENT_HIST_INIT(p_task_assignment_id IN NUMBER,
725 p_parent_incident_id IN NUMBER,
726 p_user_id IN NUMBER,
727 p_error_msg OUT NOCOPY VARCHAR2,
728 x_return_status IN OUT NOCOPY VARCHAR2)
729 IS
730 l_sqlerrno VARCHAR2(20);
731 l_sqlerrmsg VARCHAR2(4000);
732 l_error_msg VARCHAR2(4000);
733 l_return_status VARCHAR2(2000);
734 l_organization_id NUMBER;
735 l_flowtype VARCHAR2(20);
736
737 --Bug 5220635
738 CURSOR l_TaskAssgDetails_csr (p_taskassgid number) IS
739 SELECT jta.resource_id,
740 jta.task_id,
741 csi.incident_id,
742 csi.customer_id,
743 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
744 hz_ps.party_site_id,
745 hz_ps.party_id,
746 csi.inventory_item_id,
747 csi.inv_organization_id,
748 csi.contract_service_id,
749 csi.customer_product_id,
750 hz_ps.location_id,
751 jt.source_object_type_code,
752 csi.incident_location_type
753 FROM JTF_TASK_ASSIGNMENTS jta,
754 jtf_tasks_b jt,
755 cs_incidents_all_b csi,
756 hz_party_sites hz_ps
757 WHERE jta.task_assignment_id = p_taskassgid
758 AND jt.task_id = jta.task_id
759 AND jt.source_object_type_code = 'SR'
760 AND jt.source_object_id = csi.incident_id
761 AND hz_ps.party_site_id = NVL(csi.incident_location_id, jt.ADDRESS_ID) -- csi.install_site_use_id
762 AND NVL(csi.incident_location_type,'HZ_PARTY_SITE')='HZ_PARTY_SITE'
763 UNION
764 SELECT jta.resource_id,
765 jta.task_id,
766 csi.incident_id,
767 csi.customer_id,
768 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
769 NULL,
770 csi.customer_id,
771 csi.inventory_item_id,
772 csi.inv_organization_id,
773 csi.contract_service_id,
774 csi.customer_product_id,
775 lc.location_id,
776 jt.source_object_type_code,
777 csi.incident_location_type
778 FROM JTF_TASK_ASSIGNMENTS jta,
779 jtf_tasks_b jt,
780 cs_incidents_all_b csi,
781 hz_locations lc
782 WHERE jta.task_assignment_id = p_taskassgid
783 AND jt.task_id = jta.task_id
784 AND jt.source_object_type_code = 'SR'
785 AND jt.source_object_id = csi.incident_id
786 AND lc.location_id = NVL(jt.LOCATION_ID,csi.incident_location_id) -- csi.install_site_use_id;
787 AND csi.incident_location_type='HZ_LOCATION';
788
789 l_TaskAssgDetails_rec l_TaskAssgDetails_csr%ROWTYPE;
790 l_TaskAssgDetails_null_rec l_TaskAssgDetails_csr%ROWTYPE;
791
792 BEGIN
793 x_return_status := FND_API.G_RET_STS_SUCCESS;
794 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENT_HIST_INIT for task_assignment_id: ' || p_task_assignment_id
795 || ' and parent_incident_id: ' || p_parent_incident_id,
796 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_PROCEDURE);
797
798 l_TaskAssgDetails_rec := l_TaskAssgDetails_null_rec;
799 l_flowtype := 'HISTORY';
800
801 OPEN l_TaskAssgDetails_csr(p_task_assignment_id);
802 FETCH l_TaskAssgDetails_csr INTO l_taskassgdetails_rec;
803 IF l_taskassgdetails_csr%NOTFOUND THEN
804 CLOSE l_taskassgdetails_csr;
805 CSM_UTIL_PKG.LOG('No date found for history task_assignment_id: ' || p_task_assignment_id,
806 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_EXCEPTION);
807 RETURN;
808 END IF;
809 CLOSE l_taskassgdetails_csr;
810
811 -- insert into service history acc
812 csm_service_history_event_pkg.service_history_acc_i(p_parent_incident_id=>p_parent_incident_id,
813 p_incident_id=>l_taskassgdetails_rec.incident_id,
814 p_user_id=>p_user_id);
815
816 -- get SR contacts
817 csm_sr_event_pkg.spawn_sr_contacts_ins(p_incident_id=>l_taskassgdetails_rec.incident_id,
818 p_user_id=>p_user_id,
819 p_flowtype=>l_flowtype);
820 IF l_taskassgdetails_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
821 --insert location for the sr
822 CSM_HZ_LOCATIONS_EVENT_PKG.insert_location(p_location_id => l_taskassgdetails_rec.location_id,
823 p_user_id => p_user_id);
824 ELSE
825 -- spawn party site ins
826 IF l_taskassgdetails_rec.party_site_id IS NOT NULL THEN
827 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_taskassgdetails_rec.party_site_id,
828 p_user_id => p_user_id,
829 p_flowtype => l_flowtype,
830 p_error_msg => l_error_msg,
831 x_return_status => l_return_status);
832 END IF;
833 END IF;
834
835 -- spawn SR customer ins
836 IF l_taskassgdetails_rec.customer_id IS NOT NULL THEN
837 csm_party_event_pkg.party_acc_i(p_party_id => l_taskassgdetails_rec.customer_id,
838 p_user_id => p_user_id,
839 p_flowtype => l_flowtype,
840 p_error_msg => l_error_msg,
841 x_return_status => l_return_status);
842
843 --insert Accounts for the above party-R12
844 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS
845 (p_party_id=> l_taskassgdetails_rec.customer_id
846 ,p_user_id => p_user_id);
847
848 END IF;
849
850 -- get customer product
851 IF l_taskassgdetails_rec.customer_product_id IS NOT NULL THEN
852 csm_item_instance_event_pkg.item_instances_acc_processor(p_instance_id=>l_taskassgdetails_rec.customer_product_id,
853 p_user_id=>p_user_id,
854 p_flowtype=>l_flowtype,
855 p_error_msg=>l_error_msg,
856 x_return_status=>l_return_status);
857 END IF;
858
859 -- spawn debrief line ins
860 csm_task_assignment_event_pkg.spawn_debrief_line_ins(p_task_assignment_id=>p_task_assignment_id,
861 p_user_id=>p_user_id,
862 p_flow_type=>l_flowtype);
863
864 -- spawn debrief header ins
865 csm_task_assignment_event_pkg.spawn_debrief_header_ins(p_task_assignment_id=>p_task_assignment_id,
866 p_user_id=>p_user_id,
867 p_flow_type=>l_flowtype);
868
869 -- task_assignments_acc processor
870 csm_task_assignment_event_pkg.task_assignments_acc_processor
871 (p_task_assignment_id=>p_task_assignment_id,
872 p_incident_id=>l_taskassgdetails_rec.incident_id,
873 p_task_id=>l_taskassgdetails_rec.task_id,
874 p_source_object_type_code=>l_taskassgdetails_rec.source_object_type_code,
875 p_flow_type=>l_flowtype,
876 p_user_id=>p_user_id);
877
878 -- resource extns acc
879 csm_resource_extns_event_pkg.resource_extns_acc_i(p_resource_id=>l_taskassgdetails_rec.resource_id,
880 p_user_id=>p_user_id);
881
882 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENT_HIST_INIT for task_assignment_id: ' || p_task_assignment_id
883 || ' and parent_incident_id: ' || p_parent_incident_id,
884 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_PROCEDURE);
885 EXCEPTION
886 WHEN OTHERS THEN
887 l_sqlerrno := to_char(SQLCODE);
888 l_sqlerrmsg := substr(SQLERRM, 1,2000);
889 x_return_status := FND_API.G_RET_STS_ERROR;
890 p_error_msg := ' Exception in TASK_ASSIGNMENT_HIST_INIT for task_assignment_id:' || p_task_assignment_id
891 || ' and parent_incident_id: ' || p_parent_incident_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
892 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_EXCEPTION);
893 RAISE;
894 END TASK_ASSIGNMENT_HIST_INIT;
895
896 PROCEDURE TASK_ASSIGNMENT_PURGE_INIT (p_task_assignment_id IN NUMBER,
897 p_error_msg OUT NOCOPY VARCHAR2,
898 x_return_status IN OUT NOCOPY VARCHAR2)
899 IS
900 l_sqlerrno VARCHAR2(20);
901 l_sqlerrmsg VARCHAR2(4000);
902 l_error_msg VARCHAR2(4000);
903 l_return_status VARCHAR2(2000);
904 l_organization_id NUMBER;
905
906 CURSOR l_task_assg_purge_csr(p_task_assignment_id jtf_task_assignments.task_assignment_id%TYPE)
907 IS
908 --Bug 5220635
909 SELECT /*+ INDEX (acc CSM_TASK_ASSIGNMENTS_ACC_U1)*/ au.user_id,
910 au.resource_id,
911 jt.task_id,
912 csi.incident_id,
913 csi.customer_id,
914 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
915 hz_ps.party_id,
916 csi.inventory_item_id,
917 csi.inv_organization_id,
918 csi.contract_service_id,
919 csi.customer_product_id,
920 hz_ps.location_id,
921 jt.source_object_type_code,
922 csi.incident_location_type
923 FROM csm_task_assignments_acc acc,
924 jtf_task_assignments jta,
925 asg_user au,
926 asg_user_pub_resps aupr,
927 jtf_tasks_b jt,
928 cs_incidents_all_b csi,
929 hz_party_sites hz_ps
930 WHERE acc.task_assignment_id = p_task_assignment_id
931 AND acc.task_assignment_id = jta.task_assignment_id
932 AND acc.user_id = au.user_id
933 AND au.user_name = aupr.user_name
934 AND aupr.pub_name = 'SERVICEP'
935 AND jta.task_id = jt.task_id
936 AND jt.source_object_type_code = 'SR'
937 AND jt.source_object_id = csi.INCIDENT_ID
938 AND hz_ps.party_site_id = NVL(csi.incident_location_id, jt.ADDRESS_ID)
939 AND NVL(csi.incident_location_type,'HZ_PARTY_SITE')='HZ_PARTY_SITE'
940 UNION
941 SELECT /*+ INDEX (acc CSM_TASK_ASSIGNMENTS_ACC_U1)*/ au.user_id,
942 au.resource_id,
943 jt.task_id,
944 csi.incident_id,
945 csi.customer_id,
946 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
947 csi.customer_id,
948 csi.inventory_item_id,
949 csi.inv_organization_id,
950 csi.contract_service_id,
951 csi.customer_product_id,
952 lc.location_id,
953 jt.source_object_type_code,
954 csi.incident_location_type
955 FROM csm_task_assignments_acc acc,
956 jtf_task_assignments jta,
957 asg_user au,
958 asg_user_pub_resps aupr,
959 jtf_tasks_b jt,
960 cs_incidents_all_b csi,
961 hz_locations lc
962 WHERE acc.task_assignment_id = p_task_assignment_id
963 AND acc.task_assignment_id = jta.task_assignment_id
964 AND acc.user_id = au.user_id
965 AND au.user_name = aupr.user_name
966 AND aupr.pub_name = 'SERVICEP'
967 AND jta.task_id = jt.task_id
968 AND jt.source_object_type_code = 'SR'
969 AND jt.source_object_id = csi.INCIDENT_ID
970 AND lc.location_id = NVL(jt.LOCATION_ID,csi.incident_location_id)
971 AND csi.incident_location_type='HZ_LOCATION'
972 UNION
973 SELECT /*+ INDEX (acc CSM_TASK_ASSIGNMENTS_ACC_U1)*/ au.user_id,
974 au.resource_id,
975 jt.task_id,
976 TO_NUMBER(NULL),
977 TO_NUMBER(NULL),
978 TO_NUMBER(NULL),
979 TO_NUMBER(NULL),
980 TO_NUMBER(NULL),
981 TO_NUMBER(NULL),
982 TO_NUMBER(NULL),
983 TO_NUMBER(NULL),
984 TO_NUMBER(NULL),
985 jt.source_object_type_code,
986 TO_CHAR(NULL)
987 FROM csm_task_assignments_acc acc,
988 JTF_TASK_ASSIGNMENTS jta,
989 asg_user au,
990 asg_user_pub_resps aupr,
991 jtf_tasks_b jt
992 WHERE acc.task_assignment_id = p_task_assignment_id
993 AND acc.task_assignment_id = jta.task_assignment_id
994 AND acc.user_id = au.user_id
995 AND au.user_name = aupr.user_name
996 AND aupr.pub_name = 'SERVICEP'
997 AND jt.task_id = jta.task_id
998 AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL);
999
1000 l_task_assg_purge_rec l_task_assg_purge_csr%ROWTYPE;
1001 l_task_assg_purge_null_rec l_task_assg_purge_csr%ROWTYPE;
1002
1003 BEGIN
1004 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENT_PURGE_INIT for task_assignment_id: ' || p_task_assignment_id,
1005 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_PURGE_INIT',FND_LOG.LEVEL_PROCEDURE);
1006 l_task_assg_purge_rec := l_task_assg_purge_null_rec;
1007
1008 OPEN l_task_assg_purge_csr(p_task_assignment_id);
1009 FETCH l_task_assg_purge_csr INTO l_task_assg_purge_rec;
1010 IF l_task_assg_purge_csr%NOTFOUND THEN
1011 CLOSE l_task_assg_purge_csr;
1012 CSM_UTIL_PKG.LOG('Not a mobile task_assignment_id: ' || p_task_assignment_id,
1013 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_PURGE_INIT',FND_LOG.LEVEL_PROCEDURE);
1014 RETURN;
1015 END IF;
1016 CLOSE l_task_assg_purge_csr;
1017 -- get Service Inv Validation org
1018 l_organization_id := csm_profile_pkg.get_organization_id(l_task_assg_purge_rec.user_id);
1019
1020 -- lobs mdirty D
1021 csm_task_assignment_event_pkg.lobs_mdirty_D(p_task_assignment_id=>p_task_assignment_id,
1022 p_resource_id=>l_task_assg_purge_rec.resource_id);
1023
1024 -- delete task notes
1025 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'TASK',
1026 p_sourceobjectid=>l_task_assg_purge_rec.task_id,
1027 p_userid=>l_task_assg_purge_rec.user_id,
1028 p_error_msg=>l_error_msg,
1029 x_return_status=>l_return_status);
1030
1031 -- delete SR notes
1032 IF l_task_assg_purge_rec.incident_id IS NOT NULL THEN
1033 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'SR',
1034 p_sourceobjectid=>l_task_assg_purge_rec.incident_id,
1035 p_userid=>l_task_assg_purge_rec.user_id,
1036 p_error_msg=>l_error_msg,
1037 x_return_status=>l_return_status);
1038 END IF;
1039
1040 -- delete contract notes
1041 IF l_task_assg_purge_rec.contract_service_id IS NOT NULL THEN
1042 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'OKS_COV_NOTE',
1043 p_sourceobjectid=>l_task_assg_purge_rec.contract_service_id,
1044 p_userid=>l_task_assg_purge_rec.user_id,
1045 p_error_msg=>l_error_msg,
1046 x_return_status=>l_return_status);
1047 END IF;
1048
1049 -- delete SR contacts
1050 IF l_task_assg_purge_rec.incident_id IS NOT NULL THEN
1051 csm_sr_event_pkg.spawn_sr_contact_del(p_incident_id=>l_task_assg_purge_rec.incident_id,
1052 p_user_id=>l_task_assg_purge_rec.user_id,
1053 p_flowtype=>NULL);
1054 END IF;
1055
1056 IF l_task_assg_purge_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
1057 --delete location for the sr
1058 CSM_HZ_LOCATIONS_EVENT_PKG.delete_location(p_location_id => l_task_assg_purge_rec.location_id,
1059 p_user_id => l_task_assg_purge_rec.user_id);
1060 ELSE
1061 -- spawn party site del
1062 --Bug 5220635
1063 IF l_task_assg_purge_rec.incident_location_id IS NOT NULL THEN
1064 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => l_task_assg_purge_rec.incident_location_id,
1065 p_user_id => l_task_assg_purge_rec.user_id,
1066 p_flowtype => NULL,
1067 p_error_msg => l_error_msg,
1068 x_return_status => l_return_status);
1069 END IF;
1070 END IF;
1071
1072
1073 -- spawn SR customer del
1074 IF l_task_assg_purge_rec.customer_id IS NOT NULL THEN
1075 csm_party_event_pkg.party_acc_d(p_party_id => l_task_assg_purge_rec.customer_id,
1076 p_user_id => l_task_assg_purge_rec.user_id,
1077 p_flowtype => NULL,
1078 p_error_msg => l_error_msg,
1079 x_return_status => l_return_status);
1080
1081 --Delete Accounts for the above party-R12
1082 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL
1083 (p_party_id=>l_task_assg_purge_rec.customer_id
1084 ,p_user_id =>l_task_assg_purge_rec.user_id);
1085
1086 END IF;
1087
1088 IF l_task_assg_purge_rec.customer_product_id IS NOT NULL THEN
1089 -- spawn SR item instance delete
1090 --Bug 5220635
1091 csm_sr_event_pkg.sr_item_del_init(p_incident_id=>l_task_assg_purge_rec.incident_id,
1092 p_instance_id=>l_task_assg_purge_rec.customer_product_id,
1093 p_party_site_id=>l_task_assg_purge_rec.incident_location_id,
1094 p_party_id=>l_task_assg_purge_rec.party_id,
1095 p_location_id=>l_task_assg_purge_rec.location_id,
1096 p_organization_id=>NVL(l_task_assg_purge_rec.inv_organization_id, l_organization_id),
1097 p_user_id=>l_task_assg_purge_rec.user_id,
1098 p_flow_type=>NULL);
1099
1100 ELSIF l_task_assg_purge_rec.customer_product_id IS NULL OR l_task_assg_purge_rec.customer_product_id = 0 THEN
1101 IF l_task_assg_purge_rec.inventory_item_id IS NOT NULL THEN
1102 csm_mtl_system_items_event_pkg.mtl_system_items_acc_d
1103 (p_inventory_item_id=>l_task_assg_purge_rec.inventory_item_id,
1104 p_organization_id=>NVL(l_task_assg_purge_rec.inv_organization_id, l_organization_id),
1105 p_user_id=>l_task_assg_purge_rec.user_id,
1106 p_error_msg=>l_error_msg,
1107 x_return_status=>l_return_status);
1108 END IF;
1109 END IF;
1110
1111 -- spawn debrief line del
1112 csm_task_assignment_event_pkg.spawn_debrief_line_del(p_task_assignment_id=>p_task_assignment_id,
1113 p_user_id=>l_task_assg_purge_rec.user_id,
1114 p_flow_type=>NULL);
1115
1116 -- spawn debrief header del
1117 csm_task_assignment_event_pkg.spawn_debrief_header_del(p_task_assignment_id=>p_task_assignment_id,
1118 p_user_id=>l_task_assg_purge_rec.user_id,
1119 p_flow_type=>NULL);
1120
1121 -- spawn requirement line del
1122 csm_task_assignment_event_pkg.spawn_requirement_lines_del(p_task_assignment_id=>p_task_assignment_id,
1123 p_user_id=>l_task_assg_purge_rec.user_id,
1124 p_flow_type=>NULL);
1125
1126 -- spawn requirement header del
1127 csm_task_assignment_event_pkg.spawn_requirement_header_del(p_task_assignment_id=>p_task_assignment_id,
1128 p_user_id=>l_task_assg_purge_rec.user_id,
1129 p_flow_type=>NULL);
1130
1131 -- delete SR history
1132 IF l_task_assg_purge_rec.incident_id IS NOT NULL THEN
1133 csm_service_history_event_pkg.delete_history(p_task_assignment_id=>p_task_assignment_id,
1134 p_incident_id=>l_task_assg_purge_rec.incident_id,
1135 p_user_id=>l_task_assg_purge_rec.user_id);
1136 END IF;
1137
1138 -- task_assignments_acc delete
1139 csm_task_assignment_event_pkg.task_assignments_acc_d
1140 (p_task_assignment_id=>p_task_assignment_id,
1141 p_incident_id=>l_task_assg_purge_rec.incident_id,
1142 p_task_id=>l_task_assg_purge_rec.task_id,
1143 p_source_object_type_code=>l_task_assg_purge_rec.source_object_type_code,
1144 p_flow_type=>NULL,
1145 p_user_id=>l_task_assg_purge_rec.user_id);
1146
1147 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENT_PURGE_INIT for task_assignment_id: ' || p_task_assignment_id,
1148 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_PURGE_INIT',FND_LOG.LEVEL_PROCEDURE);
1149 EXCEPTION
1150 WHEN OTHERS THEN
1151 l_sqlerrno := to_char(SQLCODE);
1152 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1153 l_error_msg := ' Exception in TASK_ASSIGNMENT_PURGE_INIT for task_assignment_id:' || to_char(p_task_assignment_id)
1154 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1155 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_PURGE_INIT',FND_LOG.LEVEL_EXCEPTION);
1156 RAISE;
1157 END TASK_ASSIGNMENT_PURGE_INIT;
1158
1159 --Bug 4938130
1160 PROCEDURE LOBS_MDIRTY_D(p_task_assignment_id IN NUMBER, p_resource_id IN NUMBER)
1161 IS
1162 BEGIN
1163 CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD(p_task_assignment_id, p_resource_id);
1164 END LOBS_MDIRTY_D;
1165
1166 PROCEDURE SPAWN_DEBRIEF_HEADER_DEL (p_task_assignment_id IN NUMBER,
1167 p_user_id IN NUMBER,
1168 p_flow_type IN VARCHAR2)
1169 IS
1170 l_sqlerrno VARCHAR2(20);
1171 l_sqlerrmsg VARCHAR2(4000);
1172 l_error_msg VARCHAR2(4000);
1173 l_return_status VARCHAR2(2000);
1174
1175 -- cursor to get all debrief header id
1176 CURSOR l_debrief_header_csr(p_task_assg_id jtf_task_assignments.task_assignment_id%TYPE,
1177 p_user_id fnd_user.user_id%TYPE)
1178 IS
1179 SELECT hdr.debrief_header_id
1180 FROM csm_debrief_headers_acc acc,
1181 csf_debrief_headers hdr
1182 WHERE hdr.task_assignment_id = p_task_assg_id
1183 AND acc.debrief_header_id = hdr.debrief_header_id
1184 AND acc.user_id = p_user_id
1185 ;
1186
1187 BEGIN
1188 CSM_UTIL_PKG.LOG('Entering SPAWN_DEBRIEF_HEADER_DEL for task_assignment_id: ' || p_task_assignment_id,
1189 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_DEL',FND_LOG.LEVEL_PROCEDURE);
1190
1191 FOR r_debrief_header_rec IN l_debrief_header_csr(p_task_assignment_id, p_user_id) LOOP
1192 -- delete debrief headers
1193 csm_debrief_header_event_pkg.debrief_header_del_init(p_debrief_header_id=>r_debrief_header_rec.debrief_header_id,
1194 p_user_id=>p_user_id,
1195 p_flow_type=>p_flow_type);
1196 END LOOP;
1197
1198 CSM_UTIL_PKG.LOG('Leaving SPAWN_DEBRIEF_HEADER_DEL for task_assignment_id: ' || p_task_assignment_id,
1199 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_DEL',FND_LOG.LEVEL_PROCEDURE);
1200 EXCEPTION
1201 WHEN OTHERS THEN
1202 l_sqlerrno := to_char(SQLCODE);
1203 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1204 l_error_msg := ' Exception in SPAWN_DEBRIEF_HEADER_DEL for task_assignment_id:'
1205 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1206 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_DEL',FND_LOG.LEVEL_EXCEPTION);
1207 RAISE;
1208 END SPAWN_DEBRIEF_HEADER_DEL;
1209
1210 PROCEDURE SPAWN_DEBRIEF_LINE_DEL (p_task_assignment_id IN NUMBER,
1211 p_user_id IN NUMBER,
1212 p_flow_type IN VARCHAR2)
1213 IS
1214 l_sqlerrno VARCHAR2(20);
1215 l_sqlerrmsg VARCHAR2(4000);
1216 l_error_msg VARCHAR2(4000);
1217 l_return_status VARCHAR2(2000);
1218
1219 -- cursor to get all debrief line id
1220 CURSOR l_debrief_lines_csr(p_task_assg_id jtf_task_assignments.task_assignment_id%TYPE,
1221 p_user_id fnd_user.user_id%TYPE)
1222 IS
1223 SELECT dl.debrief_line_id,
1224 dl.debrief_header_id
1225 FROM csm_debrief_lines_acc acc,
1226 csF_debrief_headers hdr,
1227 csf_debrief_lines dl
1228 WHERE hdr.task_assignment_id = p_task_assg_id
1229 AND hdr.DEBRIEF_HEADER_ID = dl.DEBRIEF_HEADER_ID
1230 AND acc.debrief_line_id = dl.debrief_line_id
1231 AND acc.user_id = p_user_id
1232 ;
1233
1234 BEGIN
1235 CSM_UTIL_PKG.LOG('Entering SPAWN_DEBRIEF_LINE_DEL for task_assignment_id: ' || p_task_assignment_id,
1236 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_DEL',FND_LOG.LEVEL_PROCEDURE);
1237
1238 FOR r_debrief_line_rec IN l_debrief_lines_csr(p_task_assignment_id, p_user_id) LOOP
1239 -- delete debrief lines
1240 csm_debrief_event_pkg.debrief_line_del_init(p_debrief_line_id=>r_debrief_line_rec.debrief_line_id,
1241 p_user_id=>p_user_id,
1242 p_flow_type=>p_flow_type);
1243
1244 END LOOP;
1245
1246 CSM_UTIL_PKG.LOG('Leaving SPAWN_DEBRIEF_LINE_DEL for task_assignment_id: ' || p_task_assignment_id,
1247 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_DEL',FND_LOG.LEVEL_PROCEDURE);
1248 EXCEPTION
1249 WHEN OTHERS THEN
1250 l_sqlerrno := to_char(SQLCODE);
1251 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1252 l_error_msg := ' Exception in SPAWN_DEBRIEF_LINE_DEL for task_assignment_id:'
1253 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1254 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_LINE_DEL',FND_LOG.LEVEL_EXCEPTION);
1255 RAISE;
1256 END SPAWN_DEBRIEF_LINE_DEL;
1257
1258 PROCEDURE SPAWN_REQUIREMENT_HEADER_DEL(p_task_assignment_id IN NUMBER,
1259 p_user_id IN NUMBER,
1260 p_flow_type IN VARCHAR2)
1261 IS
1262 l_sqlerrno VARCHAR2(20);
1263 l_sqlerrmsg VARCHAR2(4000);
1264 l_error_msg VARCHAR2(4000);
1265 l_return_status VARCHAR2(2000);
1266
1267 -- cursor to get all requirement headers for the task_id
1268 CURSOR l_req_headers_csr(p_task_assg_id IN jtf_task_assignments.task_assignment_id%TYPE,
1269 p_user_id IN NUMBER)
1270 IS
1271 SELECT hdr.requirement_header_id,
1272 jta.resource_id
1273 FROM jtf_task_assignments jta,
1274 csp_requirement_headers hdr
1275 WHERE jta.task_assignment_id = p_task_assg_id
1276 AND hdr.task_id = jta.task_id
1277 AND EXISTS
1278 (SELECT 1
1279 FROM csm_req_headers_acc acc
1280 WHERE acc.requirement_header_id = hdr.requirement_header_id
1281 AND acc.user_id = p_user_id
1282 );
1283
1284 BEGIN
1285 CSM_UTIL_PKG.LOG('Entering SPAWN_REQUIREMENT_HEADER_DEL for task_assignment_id: ' || p_task_assignment_id,
1286 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_DEL',FND_LOG.LEVEL_PROCEDURE);
1287
1288 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
1289 FOR r_req_headers_rec IN l_req_headers_csr(p_task_assignment_id, p_user_id) LOOP
1290 -- delete requirement headers
1291 csm_csp_req_headers_event_pkg.csp_req_headers_mdirty_d(p_requirement_header_id=>r_req_headers_rec.requirement_header_id,
1292 p_user_id=>p_user_id);
1293 END LOOP;
1294 END IF;
1295
1296 CSM_UTIL_PKG.LOG('Leaving SPAWN_REQUIREMENT_HEADER_DEL for task_assignment_id: ' || p_task_assignment_id,
1297 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_DEL',FND_LOG.LEVEL_PROCEDURE);
1298 EXCEPTION
1299 WHEN OTHERS THEN
1300 l_sqlerrno := to_char(SQLCODE);
1301 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1302 l_error_msg := ' Exception in SPAWN_REQUIREMENT_HEADER_DEL for task_assignment_id:'
1303 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1304 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_DEL',FND_LOG.LEVEL_EXCEPTION);
1305 RAISE;
1306 END SPAWN_REQUIREMENT_HEADER_DEL;
1307
1308 PROCEDURE SPAWN_REQUIREMENT_LINES_DEL(p_task_assignment_id IN NUMBER,
1309 p_user_id IN NUMBER,
1310 p_flow_type IN VARCHAR2)
1311 IS
1312 l_sqlerrno VARCHAR2(20);
1313 l_sqlerrmsg VARCHAR2(4000);
1314 l_error_msg VARCHAR2(4000);
1315 l_return_status VARCHAR2(2000);
1316
1317 -- cursor to get all requirement lines for the task_id
1318
1319 CURSOR l_req_lines_csr(p_task_assg_id IN jtf_task_assignments.task_assignment_id%TYPE,
1320 p_user_id IN NUMBER)
1321 IS
1322 SELECT line.requirement_line_id,
1323 line.requirement_header_id,
1324 acc.user_id
1325 FROM jtf_task_assignments jta,
1326 csp_requirement_headers hdr,
1327 csp_requirement_lines line,
1328 csm_req_lines_acc acc
1329 WHERE jta.task_assignment_id = p_task_assg_id
1330 AND hdr.task_id = jta.task_id
1331 AND line.requirement_header_id = hdr.requirement_header_id
1332 AND acc.requirement_line_id = line.requirement_line_id;
1333
1334 BEGIN
1335 CSM_UTIL_PKG.LOG('Entering SPAWN_REQUIREMENT_LINES_DEL for task_assignment_id: ' || p_task_assignment_id,
1336 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_LINES_DEL',FND_LOG.LEVEL_PROCEDURE);
1337
1338 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
1339 FOR r_req_lines_rec IN l_req_lines_csr(p_task_assignment_id, p_user_id) LOOP
1340 -- delete requirement lines
1341 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_d(p_requirement_line_id=>r_req_lines_rec.requirement_line_id,
1342 p_user_id=>p_user_id);
1343 END LOOP;
1344 END IF;
1345
1346 CSM_UTIL_PKG.LOG('Leaving SPAWN_REQUIREMENT_LINES_DEL for task_assignment_id: ' || p_task_assignment_id,
1347 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_LINES_DEL',FND_LOG.LEVEL_PROCEDURE);
1348 EXCEPTION
1349 WHEN OTHERS THEN
1350 l_sqlerrno := to_char(SQLCODE);
1351 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1352 l_error_msg := ' Exception in SPAWN_REQUIREMENT_LINES_DEL for task_assignment_id:'
1353 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1354 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_LINES_DEL',FND_LOG.LEVEL_EXCEPTION);
1355 RAISE;
1356 END SPAWN_REQUIREMENT_LINES_DEL;
1357
1358 PROCEDURE TASK_ASSIGNMENTS_ACC_D(p_task_assignment_id IN NUMBER,
1359 p_incident_id IN NUMBER,
1360 p_task_id IN NUMBER,
1361 p_source_object_type_code IN VARCHAR2,
1362 p_flow_type IN VARCHAR2,
1363 p_user_id IN NUMBER)
1364 IS
1365 l_sqlerrno VARCHAR2(20);
1366 l_sqlerrmsg VARCHAR2(4000);
1367 l_error_msg VARCHAR2(4000);
1368 l_return_status VARCHAR2(2000);
1369
1370 BEGIN
1371 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENTS_ACC_D for task_assignment_id: ' || p_task_assignment_id,
1372 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1373
1374 IF p_source_object_type_code = 'SR' THEN
1375 csm_sr_event_pkg.incidents_acc_d(p_incident_id=>p_incident_id,
1376 p_user_id=>p_user_id);
1377
1378 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
1379 csm_contract_event_pkg.sr_contract_acc_d(p_incident_id=>p_incident_id,
1380 p_user_id=>p_user_id);
1381 END IF ;
1382 END IF;
1383
1384 -- delete tasks
1385 csm_task_event_pkg.acc_delete(p_user_id=>p_user_id, p_task_id=>p_task_id);
1386
1387 -- delete task assignments
1388 csm_task_assignment_event_pkg.acc_delete(p_task_assignment_id=>p_task_assignment_id,
1389 p_user_id=>p_user_id);
1390
1391 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENTS_ACC_D for task_assignment_id: ' || p_task_assignment_id,
1392 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1393 EXCEPTION
1394 WHEN OTHERS THEN
1395 l_sqlerrno := to_char(SQLCODE);
1396 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1397 l_error_msg := ' Exception in TASK_ASSIGNMENTS_ACC_D for task_assignment_id:'
1398 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1399 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
1400 RAISE;
1401 END TASK_ASSIGNMENTS_ACC_D;
1402
1403 PROCEDURE ACC_DELETE(p_task_assignment_id IN NUMBER, p_user_id IN NUMBER)
1404 IS
1405 l_sqlerrno VARCHAR2(20);
1406 l_sqlerrmsg VARCHAR2(4000);
1407 l_error_msg VARCHAR2(4000);
1408 l_return_status VARCHAR2(2000);
1409
1410 BEGIN
1411 CSM_UTIL_PKG.LOG('Entering ACC_DELETE for task_assignment_id: ' || p_task_assignment_id,
1412 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_PROCEDURE);
1413
1414 CSM_ACC_PKG.Delete_Acc
1415 ( P_PUBLICATION_ITEM_NAMES => g_pubi_name
1416 ,P_ACC_TABLE_NAME => g_acc_table_name
1417 ,P_PK1_NAME => g_pk1_name
1418 ,P_PK1_NUM_VALUE => p_task_assignment_id
1419 ,P_USER_ID => p_user_id
1420 );
1421
1422 CSM_UTIL_PKG.LOG('Leaving ACC_DELETE for task_assignment_id: ' || p_task_assignment_id,
1423 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_PROCEDURE);
1424 EXCEPTION
1425 WHEN OTHERS THEN
1426 l_sqlerrno := to_char(SQLCODE);
1427 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1428 l_error_msg := ' Exception in ACC_DELETE for task_assignment_id:'
1429 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1430 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_EXCEPTION);
1431 RAISE;
1432 END ACC_DELETE;
1433
1434 PROCEDURE TASK_ASSIGNMENT_HIST_DEL_INIT(p_task_assignment_id IN NUMBER,
1435 p_parent_incident_id IN NUMBER,
1436 p_user_id IN NUMBER,
1437 p_error_msg OUT NOCOPY VARCHAR2,
1438 x_return_status IN OUT NOCOPY VARCHAR2)
1439 IS
1440 l_sqlerrno VARCHAR2(20);
1441 l_sqlerrmsg VARCHAR2(4000);
1442 l_error_msg VARCHAR2(4000);
1443 l_return_status VARCHAR2(2000);
1444 l_organization_id NUMBER;
1445 l_flowtype VARCHAR2(20);
1446
1447 CURSOR l_TaskAssgHistPurge_csr (p_task_assignment_id jtf_task_assignments.task_assignment_id%TYPE)
1448 IS
1449 --Bug 5220635
1450 SELECT jta.resource_id,
1451 jta.task_id,
1452 csi.incident_id,
1453 csi.customer_id,
1454 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
1455 hz_ps.party_site_id,
1456 hz_ps.party_id,
1457 csi.inventory_item_id,
1458 csi.inv_organization_id,
1459 csi.contract_service_id,
1460 csi.customer_product_id,
1461 hz_ps.location_id,
1462 jt.source_object_type_code,
1463 csi.incident_location_type
1464 FROM JTF_TASK_ASSIGNMENTS jta,
1465 jtf_tasks_b jt,
1466 cs_incidents_all_b csi,
1467 hz_party_sites hz_ps
1468 WHERE jta.task_assignment_id = p_task_assignment_id
1469 AND jt.task_id = jta.task_id
1470 AND jt.source_object_type_code = 'SR'
1471 AND jt.source_object_id = csi.incident_id
1472 AND hz_ps.party_site_id = NVL(csi.incident_location_id, jt.ADDRESS_ID) -- csi.install_site_use_id
1473 AND NVL(csi.incident_location_type,'HZ_PARTY_SITE')='HZ_PARTY_SITE'
1474 UNION
1475 SELECT jta.resource_id,
1476 jta.task_id,
1477 csi.incident_id,
1478 csi.customer_id,
1479 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
1480 NULL,
1481 csi.customer_id,
1482 csi.inventory_item_id,
1483 csi.inv_organization_id,
1484 csi.contract_service_id,
1485 csi.customer_product_id,
1486 lc.location_id,
1487 jt.source_object_type_code,
1488 csi.incident_location_type
1489 FROM JTF_TASK_ASSIGNMENTS jta,
1490 jtf_tasks_b jt,
1491 cs_incidents_all_b csi,
1492 hz_locations lc
1493 WHERE jta.task_assignment_id = p_task_assignment_id
1494 AND jt.task_id = jta.task_id
1495 AND jt.source_object_type_code = 'SR'
1496 AND jt.source_object_id = csi.incident_id
1497 AND lc.location_id = NVL(jt.LOCATION_ID,csi.incident_location_id ) -- csi.install_site_use_id;
1498 AND csi.incident_location_type='HZ_LOCATION'
1499 ;
1500
1501 l_TaskAssgHistPurge_rec l_TaskAssgHistPurge_csr%ROWTYPE;
1502 l_TaskAssgHistPurge_null_rec l_TaskAssgHistPurge_csr%ROWTYPE;
1503
1504 BEGIN
1505 x_return_status := FND_API.G_RET_STS_SUCCESS;
1506 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENT_HIST_DEL_INIT for task_assignment_id: ' || p_task_assignment_id
1507 || ' and parent_incident_id: ' || p_parent_incident_id,
1508 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
1509
1510 l_TaskAssgHistPurge_rec := l_TaskAssgHistPurge_null_rec;
1511 l_flowtype := 'HISTORY';
1512
1513 OPEN l_TaskAssgHistPurge_csr(p_task_assignment_id);
1514 FETCH l_TaskAssgHistPurge_csr INTO l_TaskAssgHistPurge_rec;
1515 IF l_TaskAssgHistPurge_csr%NOTFOUND THEN
1516 CLOSE l_TaskAssgHistPurge_csr;
1517 CSM_UTIL_PKG.LOG('No date found for history task_assignment_id: ' || p_task_assignment_id,
1518 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_EXCEPTION);
1519 RETURN;
1520 END IF;
1521 CLOSE l_TaskAssgHistPurge_csr;
1522
1523 -- delete from service history acc
1524 csm_service_history_event_pkg.service_history_acc_d(p_parent_incident_id=>p_parent_incident_id,
1525 p_incident_id=>l_TaskAssgHistPurge_rec.incident_id,
1526 p_user_id=>p_user_id);
1527
1528 -- delete SR contacts
1529 csm_sr_event_pkg.spawn_sr_contact_del(p_incident_id=>l_TaskAssgHistPurge_rec.incident_id,
1530 p_user_id=>p_user_id,
1531 p_flowtype=>l_flowtype);
1532 IF l_TaskAssgHistPurge_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
1533 --insert location for the sr
1534 csm_hz_locations_event_pkg.delete_location(p_location_id => l_TaskAssgHistPurge_rec.location_id,
1535 p_user_id => p_user_id);
1536 ELSE
1537
1538 -- spawn party site del
1539 --Bug 5220635
1540 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => l_TaskAssgHistPurge_rec.incident_location_id,
1541 p_user_id => p_user_id,
1542 p_flowtype => l_flowtype,
1543 p_error_msg => l_error_msg,
1544 x_return_status => l_return_status);
1545 END IF;
1546 -- spawn SR customer del
1547 csm_party_event_pkg.party_acc_d(p_party_id => l_TaskAssgHistPurge_rec.customer_id,
1548 p_user_id => p_user_id,
1549 p_flowtype => l_flowtype,
1550 p_error_msg => l_error_msg,
1551 x_return_status => l_return_status);
1552
1553 --Delete Accounts for the above party-R12
1554 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL
1555 (p_party_id=> l_TaskAssgHistPurge_rec.customer_id
1556 ,p_user_id => p_user_id);
1557
1558 -- delete customer product
1559 IF l_TaskAssgHistPurge_rec.customer_product_id IS NOT NULL THEN
1560 csm_item_instance_event_pkg.item_instances_acc_d(p_instance_id=>l_TaskAssgHistPurge_rec.customer_product_id,
1561 p_user_id=>p_user_id,
1562 p_error_msg=>l_error_msg,
1563 x_return_status=>l_return_status);
1564 END IF;
1565
1566 -- spawn debrief line del
1567 csm_task_assignment_event_pkg.spawn_debrief_line_del(p_task_assignment_id=>p_task_assignment_id,
1568 p_user_id=>p_user_id,
1569 p_flow_type=>l_flowtype);
1570
1571 -- spawn debrief header del
1572 csm_task_assignment_event_pkg.spawn_debrief_header_del(p_task_assignment_id=>p_task_assignment_id,
1573 p_user_id=>p_user_id,
1574 p_flow_type=>l_flowtype);
1575
1576 -- resource extns acc del
1577 csm_resource_extns_event_pkg.resource_extns_acc_d(p_resource_id=>l_TaskAssgHistPurge_rec.resource_id,
1578 p_user_id=>p_user_id);
1579
1580 -- task_assignments_acc delete
1581 csm_task_assignment_event_pkg.task_assignments_acc_d
1582 (p_task_assignment_id=>p_task_assignment_id,
1583 p_incident_id=>l_TaskAssgHistPurge_rec.incident_id,
1584 p_task_id=>l_TaskAssgHistPurge_rec.task_id,
1585 p_source_object_type_code=>l_TaskAssgHistPurge_rec.source_object_type_code,
1586 p_flow_type=>l_flowtype,
1587 p_user_id=>p_user_id);
1588
1589 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENT_HIST_DEL_INIT for task_assignment_id: ' || p_task_assignment_id
1590 || ' and parent_incident_id: ' || p_parent_incident_id,
1591 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
1592 EXCEPTION
1593 WHEN OTHERS THEN
1594 l_sqlerrno := to_char(SQLCODE);
1595 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1596 x_return_status := FND_API.G_RET_STS_ERROR;
1597 p_error_msg := ' Exception in TASK_ASSIGNMENT_HIST_DEL_INIT for task_assignment_id:' || p_task_assignment_id
1598 || ' and parent_incident_id: ' || p_parent_incident_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1599 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
1600 RAISE;
1601 END TASK_ASSIGNMENT_HIST_DEL_INIT;
1602
1603 END CSM_TASK_ASSIGNMENT_EVENT_PKG;