[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.12020000.2 2013/04/09 11:00:02 saradhak ship $ */
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 FROM jtf_task_assignments jta,
304 csp_requirement_headers hdr,
305 csp_requirement_lines line,
306 asg_user au
307 WHERE jta.task_assignment_id = p_task_assg_id
308 AND hdr.task_id = jta.task_id
309 AND line.requirement_header_id = hdr.requirement_header_id
310 AND au.resource_id=jta.resource_id
311 AND au.user_id=p_user_id
312 AND NOT EXISTS
313 (SELECT 1
314 FROM csm_req_lines_acc acc
315 WHERE acc.requirement_line_id = line.requirement_line_id
316 AND acc.user_id = au.user_id
317 );
318
319 BEGIN
320 CSM_UTIL_PKG.LOG('Entering SPAWN_REQUIREMENTS_INS for task_assignment_id: ' || p_task_assignment_id,
321 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENTS_INS',FND_LOG.LEVEL_PROCEDURE);
322
323 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
324 FOR r_req_lines_rec IN l_req_lines_csr(p_task_assignment_id, p_user_id) LOOP
325 -- insert requirement lines
326 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_i(p_requirement_line_id=>r_req_lines_rec.requirement_line_id,
327 p_user_id=>p_user_id);
328 FOR rec IN (SELECT req_line_detail_id from csp_req_line_details WHERE requirement_line_id=r_req_lines_rec.requirement_line_id)
329 LOOP
330 CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD(rec.req_line_detail_id,'I');
331 END LOOP;
332 END LOOP;
333 END IF;
334
335 CSM_UTIL_PKG.LOG('Leaving SPAWN_REQUIREMENTS_INS for task_assignment_id: ' || p_task_assignment_id,
336 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENTS_INS',FND_LOG.LEVEL_PROCEDURE);
337 EXCEPTION
338 WHEN OTHERS THEN
339 l_sqlerrno := to_char(SQLCODE);
340 l_sqlerrmsg := substr(SQLERRM, 1,2000);
341 l_error_msg := ' Exception in SPAWN_REQUIREMENTS_INS for task_assignment_id:'
342 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
343 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENTS_INS',FND_LOG.LEVEL_EXCEPTION);
344 RAISE;
345 END SPAWN_REQUIREMENT_LINES_INS;
346
347 PROCEDURE TASK_ASSIGNMENTS_ACC_PROCESSOR(p_task_assignment_id IN NUMBER,
348 p_incident_id IN NUMBER,
349 p_task_id IN NUMBER,
350 p_source_object_type_code IN VARCHAR2,
351 p_flow_type IN VARCHAR2,
352 p_user_id IN NUMBER)
353 IS
354 l_sqlerrno VARCHAR2(20);
355 l_sqlerrmsg VARCHAR2(4000);
356 l_error_msg VARCHAR2(4000);
357 l_return_status VARCHAR2(2000);
358 l_dummy NUMBER;
359
360 CURSOR l_task_access_csr(p_task_id IN NUMBER, p_user_id IN NUMBER)
361 IS
362 SELECT 1
363 FROM csm_tasks_acc
364 WHERE user_id = p_user_id
365 AND task_id = p_task_id;
366
367 BEGIN
368 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENTS_ACC_PROCESSOR for task_assignment_id: ' || p_task_assignment_id,
369 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
370
371 IF p_source_object_type_code = 'SR' THEN
372 csm_sr_event_pkg.incidents_acc_i(p_incident_id=>p_incident_id,p_user_id=>p_user_id);
373
374 IF (NOT CSM_UTIL_PKG.IS_HTML5_USER(p_user_id) AND (p_flow_type IS NULL OR p_flow_type <> 'HISTORY')) THEN
375 csm_contract_event_pkg.sr_contract_acc_i(p_incident_id=>p_incident_id,p_user_id=>p_user_id);
376 END IF ;
377 END IF;
378
379 -- check if user already has access to the task. if already present then do not re-insert
380 -- since a task can be assigned to a user only once..multiple assignments are not supported
381
382 OPEN l_task_access_csr(p_task_id=>p_task_id, p_user_id=>p_user_id);
383 FETCH l_task_access_csr INTO l_dummy;
384 IF l_task_access_csr%NOTFOUND THEN
385 -- get notes only if it not history
386 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
387 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'TASK',
388 p_sourceobjectid=>p_task_id,
389 p_userid=>p_user_id,
390 p_error_msg=>l_error_msg,
391 x_return_status=>l_return_status);
392 END IF;
393
394 csm_task_event_pkg.acc_insert(p_task_id=>p_task_id, p_user_id=>p_user_id);
395 END IF;
396 CLOSE l_task_access_csr;
397
398 csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>p_task_assignment_id,
399 p_user_id=>p_user_id);
400
401 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENTS_ACC_PROCESSOR for task_assignment_id: ' || p_task_assignment_id,
402 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_PROCESSOR',FND_LOG.LEVEL_PROCEDURE);
403 EXCEPTION
404 WHEN OTHERS THEN
405 l_sqlerrno := to_char(SQLCODE);
406 l_sqlerrmsg := substr(SQLERRM, 1,2000);
407 l_error_msg := ' Exception in TASK_ASSIGNMENTS_ACC_PROCESSOR for task_assignment_id:'
408 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
409 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_PROCESSOR',FND_LOG.LEVEL_EXCEPTION);
410 RAISE;
411 END TASK_ASSIGNMENTS_ACC_PROCESSOR;
412
413 PROCEDURE ACC_INSERT(p_task_assignment_id IN NUMBER, p_user_id IN NUMBER)
414 IS
415 l_sqlerrno VARCHAR2(20);
416 l_sqlerrmsg VARCHAR2(4000);
417 l_error_msg VARCHAR2(4000);
418 l_return_status VARCHAR2(2000);
419
420 BEGIN
421 CSM_UTIL_PKG.LOG('Entering ACC_INSERT for task_assignment_id: ' || p_task_assignment_id,
422 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_PROCEDURE);
423
424 CSM_ACC_PKG.Insert_Acc
425 ( P_PUBLICATION_ITEM_NAMES => g_pubi_name
426 ,P_ACC_TABLE_NAME => g_acc_table_name
427 ,P_SEQ_NAME => g_acc_seq_name
428 ,P_PK1_NAME => g_pk1_name
429 ,P_PK1_NUM_VALUE => p_task_assignment_id
430 ,P_USER_ID => p_user_id
431 );
432
433 CSM_UTIL_PKG.LOG('Leaving ACC_INSERT for task_assignment_id: ' || p_task_assignment_id,
434 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_PROCEDURE);
435 EXCEPTION
436 WHEN OTHERS THEN
437 l_sqlerrno := to_char(SQLCODE);
438 l_sqlerrmsg := substr(SQLERRM, 1,2000);
439 l_error_msg := ' Exception in ACC_INSERT for task_assignment_id:'
440 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
441 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_INSERT',FND_LOG.LEVEL_EXCEPTION);
442 RAISE;
443 END ACC_INSERT;
444
445 --Bug 4938130
446 PROCEDURE LOBS_MDIRTY_I(p_task_assignment_id IN NUMBER, p_resource_id IN NUMBER)
447 IS
448 BEGIN
449 CSM_LOBS_EVENT_PKG.INSERT_ACC_RECORD(p_task_assignment_id, p_resource_id);
450 END LOBS_MDIRTY_I;
451
452 PROCEDURE TASK_ASSIGNMENT_INITIALIZER (p_task_assignment_id IN NUMBER,
453 p_error_msg OUT NOCOPY VARCHAR2,
454 x_return_status IN OUT NOCOPY VARCHAR2)
455 IS
456 l_sqlerrno VARCHAR2(20);
457 l_sqlerrmsg VARCHAR2(4000);
458 l_error_msg VARCHAR2(4000);
459 l_return_status VARCHAR2(2000);
460 l_organization_id NUMBER;
461 l_is_synchronous_history VARCHAR2(1);
462
463 --Bug 5220635
464 CURSOR l_TaskAssgDetails_csr (p_taskassgid number) IS
465 SELECT au.user_id,
466 jta.resource_id,
467 jta.task_id,
468 csi.incident_id,
469 csi.customer_id,
470 hz_ps.party_site_id,
471 hz_ps.party_id,
472 csi.inventory_item_id,
473 csi.inv_organization_id,
474 csi.contract_service_id,
475 csi.customer_product_id,
476 hz_ps.location_id,
477 jt.source_object_type_code,
478 csi.incident_location_type,
479 jt.address_id,
480 csi.org_id
481 FROM JTF_TASK_ASSIGNMENTS jta,
482 asg_user au,
483 asg_user_pub_resps aupr,
484 jtf_tasks_b jt,
485 cs_incidents_all_b csi,
486 hz_party_sites hz_ps
487 WHERE jta.task_assignment_id = p_taskassgid
488 AND jta.assignee_role = 'ASSIGNEE'
489 AND au.resource_id = jta.resource_id
490 AND au.user_name = aupr.user_name
491 AND aupr.pub_name = 'SERVICEP'
492 AND jt.task_id = jta.task_id
493 AND jt.source_object_type_code = 'SR'
494 AND jt.source_object_id = csi.incident_id
495 AND hz_ps.party_site_id = NVL(csi.incident_location_id, jt.ADDRESS_ID)
496 AND NVL(csi.incident_location_type,'HZ_PARTY_SITE')='HZ_PARTY_SITE'
497 UNION
498 SELECT au.user_id,
499 jta.resource_id,
500 jta.task_id,
501 csi.incident_id,
502 csi.customer_id,
503 NULL,
504 csi.customer_id,
505 csi.inventory_item_id,
506 csi.inv_organization_id,
507 csi.contract_service_id,
508 csi.customer_product_id,
509 lc.location_id,
510 jt.source_object_type_code,
511 csi.incident_location_type,
512 jt.address_id,
513 csi.org_id
514 FROM JTF_TASK_ASSIGNMENTS jta,
515 asg_user au,
516 asg_user_pub_resps aupr,
517 jtf_tasks_b jt,
518 cs_incidents_all_b csi,
519 hz_locations lc
520 WHERE jta.task_assignment_id = p_taskassgid
521 AND jta.assignee_role = 'ASSIGNEE'
522 AND au.resource_id = jta.resource_id
523 AND au.user_name = aupr.user_name
524 AND aupr.pub_name = 'SERVICEP'
525 AND jt.task_id = jta.task_id
526 AND jt.source_object_type_code = 'SR'
527 AND jt.source_object_id = csi.incident_id
528 AND lc.location_id = NVL(jt.LOCATION_ID,csi.incident_location_id)
529 AND csi.incident_location_type='HZ_LOCATION'
530 UNION
531 SELECT au.user_id,
532 jta.resource_id,
533 jta.task_id,
534 to_number(NULL),
535 to_number(NULL),
536 to_number(NULL),
537 to_number(NULL),
538 to_number(NULL),
539 to_number(NULL),
540 to_number(NULL),
541 to_number(NULL),
542 to_number(NULL),
543 jt.source_object_type_code,
544 to_char(NULL),
545 jt.address_id,
546 to_number(NULL)
547 FROM JTF_TASK_ASSIGNMENTS jta,
548 asg_user au,
549 asg_user_pub_resps aupr,
550 jtf_tasks_b jt
551 WHERE jta.task_assignment_id = p_taskassgid
552 AND jta.assignee_role = 'ASSIGNEE'
553 AND au.resource_id = jta.resource_id
554 AND au.user_name = aupr.user_name
555 AND aupr.pub_name = 'SERVICEP'
556 AND jt.task_id = jta.task_id
557 AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL);
558
559 l_TaskAssgDetails_rec l_TaskAssgDetails_csr%ROWTYPE;
560 l_TaskAssgDetails_null_rec l_TaskAssgDetails_csr%ROWTYPE;
561 l_htm5_user boolean:=false;
562
563 BEGIN
564 x_return_status := FND_API.G_RET_STS_SUCCESS;
565 CSM_UTIL_PKG.LOG('Entering CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER for task_assignment_id: ' || p_task_assignment_id,
566 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
567
568 l_TaskAssgDetails_rec := l_TaskAssgDetails_null_rec;
569
570 OPEN l_TaskAssgDetails_csr(p_task_assignment_id);
571 FETCH l_TaskAssgDetails_csr INTO l_taskassgdetails_rec;
572 IF l_taskassgdetails_csr%NOTFOUND THEN
573 CLOSE l_taskassgdetails_csr;
574 CSM_UTIL_PKG.LOG('Not a mobile task_assignment_id: ' || p_task_assignment_id,
575 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
576 RETURN;
577 END IF;
578 CLOSE l_taskassgdetails_csr;
579
580 l_htm5_user :=CSM_UTIL_PKG.IS_HTML5_USER(l_taskassgdetails_rec.user_id);
581
582 IF l_htm5_user AND l_TaskAssgDetails_rec.source_object_type_code <>'SR' THEN
583 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENT_INITIALIZER as this Task of HTML5 user is not having src incident','CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
584 RETURN;
585 END IF;
586
587 -- check if its a SR Task
588 IF NOT csm_sr_event_pkg.is_sr_task(l_taskassgdetails_rec.task_id) THEN
589 RETURN;
590 END IF;
591
592 -- check if task status is downloadable
593 IF NOT csm_sr_event_pkg.is_task_status_downloadable(l_taskassgdetails_rec.task_id) THEN
594 RETURN;
595 END IF;
596
597 -- check if task assignment status is downloadable
598 IF NOT csm_sr_event_pkg.is_assgn_status_downloadable(p_task_assignment_id) THEN
599 RETURN;
600 END IF;
601
602 -- get Service Inv Validation org
603 l_organization_id := csm_profile_pkg.get_organization_id(l_taskassgdetails_rec.user_id);
604
605 --get task notes moved to task_assignments_acc processor
606
607
608 IF NOT l_htm5_user THEN
609 --get SR notes
610 IF l_taskassgdetails_rec.incident_id IS NOT NULL THEN
611 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'SR',
612 p_sourceobjectid=>l_taskassgdetails_rec.incident_id,
613 p_userid=>l_taskassgdetails_rec.user_id,
614 p_error_msg=>l_error_msg,
615 x_return_status=>l_return_status);
616 END IF;
617
618 --get contract notes
619 IF l_taskassgdetails_rec.contract_service_id IS NOT NULL THEN
620 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'OKS_COV_NOTE',
621 p_sourceobjectid=>l_taskassgdetails_rec.contract_service_id,
622 p_userid=>l_taskassgdetails_rec.user_id,
623 p_error_msg=>l_error_msg,
624 x_return_status=>l_return_status);
625 END IF;
626 END IF;
627
628 -- get SR contacts
629 IF l_taskassgdetails_rec.incident_id IS NOT NULL THEN
630 csm_sr_event_pkg.spawn_sr_contacts_ins(p_incident_id=>l_taskassgdetails_rec.incident_id,
631 p_user_id=>l_taskassgdetails_rec.user_id,
632 p_flowtype=>NULL);
633 END IF;
634
635 IF l_taskassgdetails_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
636 --insert location for the task
637 CSM_HZ_LOCATIONS_EVENT_PKG.insert_location(p_location_id => l_taskassgdetails_rec.location_id,
638 p_user_id => l_taskassgdetails_rec.user_id);
639 ELSE
640 -- spawn party site ins
641 IF l_taskassgdetails_rec.party_site_id IS NOT NULL THEN
642 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_taskassgdetails_rec.party_site_id,
643 p_user_id => l_taskassgdetails_rec.user_id,
644 p_flowtype => NULL,
645 p_error_msg => l_error_msg,
646 x_return_status => l_return_status);
647
648 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_INS
649 (p_party_site_id=>l_taskassgdetails_rec.party_site_id,
650 p_org_id =>l_taskassgdetails_rec.org_id ,p_user_id =>l_taskassgdetails_rec.user_id);
651 END IF;
652 END IF;
653
654 -- spawn SR customer ins
655 IF l_taskassgdetails_rec.customer_id IS NOT NULL THEN
656 csm_party_event_pkg.party_acc_i(p_party_id => l_taskassgdetails_rec.customer_id,
657 p_user_id => l_taskassgdetails_rec.user_id,
658 p_flowtype => NULL,
659 p_error_msg => l_error_msg,
660 x_return_status => l_return_status);
661
662 --insert Accounts for the above party-R12
663 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS
664 (p_party_id=> l_taskassgdetails_rec.customer_id
665 ,p_user_id => l_taskassgdetails_rec.user_id);
666
667 END IF;
668
669 IF l_taskassgdetails_rec.customer_product_id IS NOT NULL THEN
670 -- spawn SR item instance insert
671 csm_sr_event_pkg.sr_item_ins_init(p_incident_id=>l_taskassgdetails_rec.incident_id,
672 p_instance_id=>l_taskassgdetails_rec.customer_product_id,
673 p_party_site_id=>l_taskassgdetails_rec.party_site_id,
674 p_party_id=>l_taskassgdetails_rec.party_id,
675 p_location_id=>l_taskassgdetails_rec.location_id,
676 p_organization_id=>NVL(l_taskassgdetails_rec.inv_organization_id, l_organization_id),
677 p_user_id=>l_taskassgdetails_rec.user_id,
678 p_flow_type=>NULL);
679
680 ELSIF l_taskassgdetails_rec.customer_product_id IS NULL OR l_taskassgdetails_rec.customer_product_id = 0 THEN
681 IF l_taskassgdetails_rec.inventory_item_id IS NOT NULL THEN
682 csm_mtl_system_items_event_pkg.mtl_system_items_acc_i
683 (p_inventory_item_id=>l_taskassgdetails_rec.inventory_item_id,
684 p_organization_id=>NVL(l_taskassgdetails_rec.inv_organization_id, l_organization_id),
685 p_user_id=>l_taskassgdetails_rec.user_id,
686 p_error_msg=>l_error_msg,
687 x_return_status=>l_return_status);
688 END IF;
689 END IF;
690
691 -- spawn debrief line ins
692 csm_task_assignment_event_pkg.spawn_debrief_line_ins(p_task_assignment_id=>p_task_assignment_id,
693 p_user_id=>l_taskassgdetails_rec.user_id,
694 p_flow_type=>NULL);
695
696 -- spawn debrief header ins
697 csm_task_assignment_event_pkg.spawn_debrief_header_ins(p_task_assignment_id=>p_task_assignment_id,
698 p_user_id=>l_taskassgdetails_rec.user_id,
699 p_flow_type=>NULL);
700
701 -- spawn requirement lines ins
702 csm_task_assignment_event_pkg.spawn_requirement_lines_ins(p_task_assignment_id=>p_task_assignment_id,
703 p_user_id=>l_taskassgdetails_rec.user_id,
704 p_flow_type=>NULL);
705
706 -- spawn requirement headers ins
707 csm_task_assignment_event_pkg.spawn_requirement_header_ins(p_task_assignment_id=>p_task_assignment_id,
708 p_user_id=>l_taskassgdetails_rec.user_id,
709 p_flow_type=>NULL);
710
711 -- task_assignments_acc processor
712 csm_task_assignment_event_pkg.task_assignments_acc_processor
713 (p_task_assignment_id=>p_task_assignment_id,
714 p_incident_id=>l_taskassgdetails_rec.incident_id,
715 p_task_id=>l_taskassgdetails_rec.task_id,
716 p_source_object_type_code=>l_taskassgdetails_rec.source_object_type_code,
717 p_flow_type=>NULL,
718 p_user_id=>l_taskassgdetails_rec.user_id);
719
720
721 -- get synchronous history
722 IF l_taskassgdetails_rec.incident_id IS NOT NULL AND NOT l_htm5_user THEN
723 l_is_synchronous_history := fnd_profile.value('CSM_SYNCHRONOUS_HISTORY');
724
725 IF l_is_synchronous_history = 'Y' THEN
726 csm_service_history_event_pkg.calculate_history(l_incident_id=>l_taskassgdetails_rec.incident_id,
727 l_user_id=>l_taskassgdetails_rec.user_id);
728 END IF;
729 END IF;
730
731
732 IF NOT l_htm5_user THEN
733 -- lobs mdirty I
734 csm_task_assignment_event_pkg.lobs_mdirty_i(p_task_assignment_id=>p_task_assignment_id,
735 p_resource_id=>l_taskassgdetails_rec.resource_id);
736 END IF;
737
738
739 --html5 functionality
740 IF l_taskassgdetails_rec.ADDRESS_ID IS NOT NULL AND l_htm5_user THEN
741 CSM_UTIL_PKG.LOG('Inserting task address for htm user','CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
742 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_taskassgdetails_rec.ADDRESS_ID,
743 p_user_id => l_taskassgdetails_rec.user_id,
744 p_flowtype => NULL,
745 p_error_msg => l_error_msg,
746 x_return_status => l_return_status);
747
748 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_INS
749 (p_party_site_id=>l_taskassgdetails_rec.ADDRESS_ID,
750 p_org_id =>l_taskassgdetails_rec.org_id ,p_user_id =>l_taskassgdetails_rec.user_id);
751 END IF;
752
753 CSM_UTIL_PKG.LOG('Leaving CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER for task_assignment_id: ' || p_task_assignment_id,
754 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_PROCEDURE);
755 EXCEPTION
756 WHEN OTHERS THEN
757 l_sqlerrno := to_char(SQLCODE);
758 l_sqlerrmsg := substr(SQLERRM, 1,2000);
759 x_return_status := FND_API.G_RET_STS_ERROR;
760 p_error_msg := ' Exception in TASK_ASSIGNMENT_INITIALIZER for task_assignment_id:' || to_char(p_task_assignment_id)
761 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
762 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_INITIALIZER',FND_LOG.LEVEL_EXCEPTION);
763 RAISE;
764 END TASK_ASSIGNMENT_INITIALIZER;
765
766 PROCEDURE TASK_ASSIGNMENT_HIST_INIT(p_task_assignment_id IN NUMBER,
767 p_parent_incident_id IN NUMBER,
768 p_user_id IN NUMBER,
769 p_error_msg OUT NOCOPY VARCHAR2,
770 x_return_status IN OUT NOCOPY VARCHAR2)
771 IS
772 l_sqlerrno VARCHAR2(20);
773 l_sqlerrmsg VARCHAR2(4000);
774 l_error_msg VARCHAR2(4000);
775 l_return_status VARCHAR2(2000);
776 l_organization_id NUMBER;
777 l_flowtype VARCHAR2(20);
778
779 --Bug 5220635
780 CURSOR l_TaskAssgDetails_csr (p_taskassgid number) IS
781 SELECT jta.resource_id,
782 jta.task_id,
783 csi.incident_id,
784 csi.customer_id,
785 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
786 hz_ps.party_site_id,
787 hz_ps.party_id,
788 csi.inventory_item_id,
789 csi.inv_organization_id,
790 csi.contract_service_id,
791 csi.customer_product_id,
792 hz_ps.location_id,
793 jt.source_object_type_code,
794 csi.incident_location_type
795 FROM JTF_TASK_ASSIGNMENTS jta,
796 jtf_tasks_b jt,
797 cs_incidents_all_b csi,
798 hz_party_sites hz_ps
799 WHERE jta.task_assignment_id = p_taskassgid
800 AND jt.task_id = jta.task_id
801 AND jt.source_object_type_code = 'SR'
802 AND jt.source_object_id = csi.incident_id
803 AND hz_ps.party_site_id = NVL(csi.incident_location_id, jt.ADDRESS_ID) -- csi.install_site_use_id
804 AND NVL(csi.incident_location_type,'HZ_PARTY_SITE')='HZ_PARTY_SITE'
805 UNION
806 SELECT jta.resource_id,
807 jta.task_id,
808 csi.incident_id,
809 csi.customer_id,
810 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
811 NULL,
812 csi.customer_id,
813 csi.inventory_item_id,
814 csi.inv_organization_id,
815 csi.contract_service_id,
816 csi.customer_product_id,
817 lc.location_id,
818 jt.source_object_type_code,
819 csi.incident_location_type
820 FROM JTF_TASK_ASSIGNMENTS jta,
821 jtf_tasks_b jt,
822 cs_incidents_all_b csi,
823 hz_locations lc
824 WHERE jta.task_assignment_id = p_taskassgid
825 AND jt.task_id = jta.task_id
826 AND jt.source_object_type_code = 'SR'
827 AND jt.source_object_id = csi.incident_id
828 AND lc.location_id = NVL(jt.LOCATION_ID,csi.incident_location_id) -- csi.install_site_use_id;
829 AND csi.incident_location_type='HZ_LOCATION';
830
831 l_TaskAssgDetails_rec l_TaskAssgDetails_csr%ROWTYPE;
832 l_TaskAssgDetails_null_rec l_TaskAssgDetails_csr%ROWTYPE;
833
834 BEGIN
835 x_return_status := FND_API.G_RET_STS_SUCCESS;
836 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENT_HIST_INIT for task_assignment_id: ' || p_task_assignment_id
837 || ' and parent_incident_id: ' || p_parent_incident_id,
838 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_PROCEDURE);
839
840 l_TaskAssgDetails_rec := l_TaskAssgDetails_null_rec;
841 l_flowtype := 'HISTORY';
842
843 OPEN l_TaskAssgDetails_csr(p_task_assignment_id);
844 FETCH l_TaskAssgDetails_csr INTO l_taskassgdetails_rec;
845 IF l_taskassgdetails_csr%NOTFOUND THEN
846 CLOSE l_taskassgdetails_csr;
847 CSM_UTIL_PKG.LOG('No date found for history task_assignment_id: ' || p_task_assignment_id,
848 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_EXCEPTION);
849 RETURN;
850 END IF;
851 CLOSE l_taskassgdetails_csr;
852
853 -- insert into service history acc
854 csm_service_history_event_pkg.service_history_acc_i(p_parent_incident_id=>p_parent_incident_id,
855 p_incident_id=>l_taskassgdetails_rec.incident_id,
856 p_user_id=>p_user_id);
857
858 -- get SR contacts
859 csm_sr_event_pkg.spawn_sr_contacts_ins(p_incident_id=>l_taskassgdetails_rec.incident_id,
860 p_user_id=>p_user_id,
861 p_flowtype=>l_flowtype);
862 IF l_taskassgdetails_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
863 --insert location for the sr
864 CSM_HZ_LOCATIONS_EVENT_PKG.insert_location(p_location_id => l_taskassgdetails_rec.location_id,
865 p_user_id => p_user_id);
866 ELSE
867 -- spawn party site ins
868 IF l_taskassgdetails_rec.party_site_id IS NOT NULL THEN
869 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_taskassgdetails_rec.party_site_id,
870 p_user_id => p_user_id,
871 p_flowtype => l_flowtype,
872 p_error_msg => l_error_msg,
873 x_return_status => l_return_status);
874 END IF;
875 END IF;
876
877 -- spawn SR customer ins
878 IF l_taskassgdetails_rec.customer_id IS NOT NULL THEN
879 csm_party_event_pkg.party_acc_i(p_party_id => l_taskassgdetails_rec.customer_id,
880 p_user_id => p_user_id,
881 p_flowtype => l_flowtype,
882 p_error_msg => l_error_msg,
883 x_return_status => l_return_status);
884
885 --insert Accounts for the above party-R12
886 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS
887 (p_party_id=> l_taskassgdetails_rec.customer_id
888 ,p_user_id => p_user_id);
889
890 END IF;
891
892 -- get customer product
893 IF l_taskassgdetails_rec.customer_product_id IS NOT NULL THEN
894 csm_item_instance_event_pkg.item_instances_acc_processor(p_instance_id=>l_taskassgdetails_rec.customer_product_id,
895 p_user_id=>p_user_id,
896 p_flowtype=>l_flowtype,
897 p_error_msg=>l_error_msg,
898 x_return_status=>l_return_status);
899 END IF;
900
901 -- spawn debrief line ins
902 csm_task_assignment_event_pkg.spawn_debrief_line_ins(p_task_assignment_id=>p_task_assignment_id,
903 p_user_id=>p_user_id,
904 p_flow_type=>l_flowtype);
905
906 -- spawn debrief header ins
907 csm_task_assignment_event_pkg.spawn_debrief_header_ins(p_task_assignment_id=>p_task_assignment_id,
908 p_user_id=>p_user_id,
909 p_flow_type=>l_flowtype);
910
911 -- task_assignments_acc processor
912 csm_task_assignment_event_pkg.task_assignments_acc_processor
913 (p_task_assignment_id=>p_task_assignment_id,
914 p_incident_id=>l_taskassgdetails_rec.incident_id,
915 p_task_id=>l_taskassgdetails_rec.task_id,
916 p_source_object_type_code=>l_taskassgdetails_rec.source_object_type_code,
917 p_flow_type=>l_flowtype,
918 p_user_id=>p_user_id);
919
920 -- resource extns acc
921 csm_resource_extns_event_pkg.resource_extns_acc_i(p_resource_id=>l_taskassgdetails_rec.resource_id,
922 p_user_id=>p_user_id);
923
924 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENT_HIST_INIT for task_assignment_id: ' || p_task_assignment_id
925 || ' and parent_incident_id: ' || p_parent_incident_id,
926 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_PROCEDURE);
927 EXCEPTION
928 WHEN OTHERS THEN
929 l_sqlerrno := to_char(SQLCODE);
930 l_sqlerrmsg := substr(SQLERRM, 1,2000);
931 x_return_status := FND_API.G_RET_STS_ERROR;
932 p_error_msg := ' Exception in TASK_ASSIGNMENT_HIST_INIT for task_assignment_id:' || p_task_assignment_id
933 || ' and parent_incident_id: ' || p_parent_incident_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
934 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_EXCEPTION);
935 RAISE;
936 END TASK_ASSIGNMENT_HIST_INIT;
937
938 PROCEDURE TASK_ASSIGNMENT_PURGE_INIT (p_task_assignment_id IN NUMBER,
939 p_error_msg OUT NOCOPY VARCHAR2,
940 x_return_status IN OUT NOCOPY VARCHAR2)
941 IS
942 l_sqlerrno VARCHAR2(20);
943 l_sqlerrmsg VARCHAR2(4000);
944 l_error_msg VARCHAR2(4000);
945 l_return_status VARCHAR2(2000);
946 l_organization_id NUMBER;
947
948 CURSOR l_task_assg_purge_csr(p_task_assignment_id jtf_task_assignments.task_assignment_id%TYPE)
949 IS
950 --Bug 5220635
951 SELECT /*+ INDEX (acc CSM_TASK_ASSIGNMENTS_ACC_U1)*/ au.user_id,
952 au.resource_id,
953 jt.task_id,
954 csi.incident_id,
955 csi.customer_id,
956 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
957 hz_ps.party_id,
958 csi.inventory_item_id,
959 csi.inv_organization_id,
960 csi.contract_service_id,
961 csi.customer_product_id,
962 hz_ps.location_id,
963 jt.source_object_type_code,
964 csi.incident_location_type,
965 jt.ADDRESS_ID,
966 csi.org_id
967 FROM csm_task_assignments_acc acc,
968 jtf_task_assignments jta,
969 asg_user au,
970 asg_user_pub_resps aupr,
971 jtf_tasks_b jt,
972 cs_incidents_all_b csi,
973 hz_party_sites hz_ps
974 WHERE acc.task_assignment_id = p_task_assignment_id
975 AND acc.task_assignment_id = jta.task_assignment_id
976 AND acc.user_id = au.user_id
977 AND au.user_name = aupr.user_name
978 AND aupr.pub_name = 'SERVICEP'
979 AND jta.task_id = jt.task_id
980 AND jt.source_object_type_code = 'SR'
981 AND jt.source_object_id = csi.INCIDENT_ID
982 AND hz_ps.party_site_id = NVL(csi.incident_location_id, jt.ADDRESS_ID)
983 AND NVL(csi.incident_location_type,'HZ_PARTY_SITE')='HZ_PARTY_SITE'
984 UNION
985 SELECT /*+ INDEX (acc CSM_TASK_ASSIGNMENTS_ACC_U1)*/ au.user_id,
986 au.resource_id,
987 jt.task_id,
988 csi.incident_id,
989 csi.customer_id,
990 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
991 csi.customer_id,
992 csi.inventory_item_id,
993 csi.inv_organization_id,
994 csi.contract_service_id,
995 csi.customer_product_id,
996 lc.location_id,
997 jt.source_object_type_code,
998 csi.incident_location_type,
999 jt.ADDRESS_ID,
1000 csi.org_id
1001 FROM csm_task_assignments_acc acc,
1002 jtf_task_assignments jta,
1003 asg_user au,
1004 asg_user_pub_resps aupr,
1005 jtf_tasks_b jt,
1006 cs_incidents_all_b csi,
1007 hz_locations lc
1008 WHERE acc.task_assignment_id = p_task_assignment_id
1009 AND acc.task_assignment_id = jta.task_assignment_id
1010 AND acc.user_id = au.user_id
1011 AND au.user_name = aupr.user_name
1012 AND aupr.pub_name = 'SERVICEP'
1013 AND jta.task_id = jt.task_id
1014 AND jt.source_object_type_code = 'SR'
1015 AND jt.source_object_id = csi.INCIDENT_ID
1016 AND lc.location_id = NVL(jt.LOCATION_ID,csi.incident_location_id)
1017 AND csi.incident_location_type='HZ_LOCATION'
1018 UNION
1019 SELECT /*+ INDEX (acc CSM_TASK_ASSIGNMENTS_ACC_U1)*/ au.user_id,
1020 au.resource_id,
1021 jt.task_id,
1022 TO_NUMBER(NULL),
1023 TO_NUMBER(NULL),
1024 TO_NUMBER(NULL),
1025 TO_NUMBER(NULL),
1026 TO_NUMBER(NULL),
1027 TO_NUMBER(NULL),
1028 TO_NUMBER(NULL),
1029 TO_NUMBER(NULL),
1030 TO_NUMBER(NULL),
1031 jt.source_object_type_code,
1032 TO_CHAR(NULL),
1033 jt.ADDRESS_ID,
1034 TO_NUMBER(NULL)
1035 FROM csm_task_assignments_acc acc,
1036 JTF_TASK_ASSIGNMENTS jta,
1037 asg_user au,
1038 asg_user_pub_resps aupr,
1039 jtf_tasks_b jt
1040 WHERE acc.task_assignment_id = p_task_assignment_id
1041 AND acc.task_assignment_id = jta.task_assignment_id
1042 AND acc.user_id = au.user_id
1043 AND au.user_name = aupr.user_name
1044 AND aupr.pub_name = 'SERVICEP'
1045 AND jt.task_id = jta.task_id
1046 AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL);
1047
1048 l_task_assg_purge_rec l_task_assg_purge_csr%ROWTYPE;
1049 l_task_assg_purge_null_rec l_task_assg_purge_csr%ROWTYPE;
1050 l_htm5_user boolean:=false;
1051
1052 BEGIN
1053 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENT_PURGE_INIT for task_assignment_id: ' || p_task_assignment_id,
1054 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_PURGE_INIT',FND_LOG.LEVEL_PROCEDURE);
1055 l_task_assg_purge_rec := l_task_assg_purge_null_rec;
1056
1057 OPEN l_task_assg_purge_csr(p_task_assignment_id);
1058 FETCH l_task_assg_purge_csr INTO l_task_assg_purge_rec;
1059 IF l_task_assg_purge_csr%NOTFOUND THEN
1060 CLOSE l_task_assg_purge_csr;
1061 CSM_UTIL_PKG.LOG('Not a mobile task_assignment_id: ' || p_task_assignment_id,
1062 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_PURGE_INIT',FND_LOG.LEVEL_PROCEDURE);
1063 RETURN;
1064 END IF;
1065 CLOSE l_task_assg_purge_csr;
1066 -- get Service Inv Validation org
1067 l_organization_id := csm_profile_pkg.get_organization_id(l_task_assg_purge_rec.user_id);
1068
1069 l_htm5_user:=csm_util_pkg.is_html5_user(l_task_assg_purge_rec.user_id);
1070
1071 --html5 functionality
1072 IF l_task_assg_purge_rec.ADDRESS_ID IS NOT NULL AND l_htm5_user THEN
1073 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => l_task_assg_purge_rec.ADDRESS_ID,
1074 p_user_id => l_task_assg_purge_rec.user_id,
1075 p_flowtype => NULL,
1076 p_error_msg => l_error_msg,
1077 x_return_status => l_return_status);
1078 END IF;
1079
1080
1081 -- delete task notes
1082 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'TASK',
1083 p_sourceobjectid=>l_task_assg_purge_rec.task_id,
1084 p_userid=>l_task_assg_purge_rec.user_id,
1085 p_error_msg=>l_error_msg,
1086 x_return_status=>l_return_status);
1087
1088
1089 IF NOT l_htm5_user THEN
1090 -- lobs mdirty D
1091 csm_task_assignment_event_pkg.lobs_mdirty_D(p_task_assignment_id=>p_task_assignment_id,
1092 p_resource_id=>l_task_assg_purge_rec.resource_id);
1093
1094
1095
1096
1097 -- delete SR notes
1098 IF l_task_assg_purge_rec.incident_id IS NOT NULL THEN
1099 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'SR',
1100 p_sourceobjectid=>l_task_assg_purge_rec.incident_id,
1101 p_userid=>l_task_assg_purge_rec.user_id,
1102 p_error_msg=>l_error_msg,
1103 x_return_status=>l_return_status);
1104 END IF;
1105
1106 -- delete contract notes
1107 IF l_task_assg_purge_rec.contract_service_id IS NOT NULL THEN
1108 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'OKS_COV_NOTE',
1109 p_sourceobjectid=>l_task_assg_purge_rec.contract_service_id,
1110 p_userid=>l_task_assg_purge_rec.user_id,
1111 p_error_msg=>l_error_msg,
1112 x_return_status=>l_return_status);
1113 END IF;
1114
1115 END IF; --htm user
1116
1117 -- delete SR contacts
1118 IF l_task_assg_purge_rec.incident_id IS NOT NULL THEN
1119 csm_sr_event_pkg.spawn_sr_contact_del(p_incident_id=>l_task_assg_purge_rec.incident_id,
1120 p_user_id=>l_task_assg_purge_rec.user_id,
1121 p_flowtype=>NULL);
1122 END IF;
1123
1124 IF l_task_assg_purge_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
1125 --delete location for the sr
1126 CSM_HZ_LOCATIONS_EVENT_PKG.delete_location(p_location_id => l_task_assg_purge_rec.location_id,
1127 p_user_id => l_task_assg_purge_rec.user_id);
1128 ELSE
1129 -- spawn party site del
1130 --Bug 5220635
1131 IF l_task_assg_purge_rec.incident_location_id IS NOT NULL THEN
1132 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => l_task_assg_purge_rec.incident_location_id,
1133 p_user_id => l_task_assg_purge_rec.user_id,
1134 p_flowtype => NULL,
1135 p_error_msg => l_error_msg,
1136 x_return_status => l_return_status);
1137 END IF;
1138 END IF;
1139
1140
1141 -- spawn SR customer del
1142 IF l_task_assg_purge_rec.customer_id IS NOT NULL THEN
1143 csm_party_event_pkg.party_acc_d(p_party_id => l_task_assg_purge_rec.customer_id,
1144 p_user_id => l_task_assg_purge_rec.user_id,
1145 p_flowtype => NULL,
1146 p_error_msg => l_error_msg,
1147 x_return_status => l_return_status);
1148
1149 --Delete Accounts for the above party-R12
1150 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL
1151 (p_party_id=>l_task_assg_purge_rec.customer_id
1152 ,p_user_id =>l_task_assg_purge_rec.user_id);
1153
1154 END IF;
1155
1156 IF l_task_assg_purge_rec.customer_product_id IS NOT NULL THEN
1157 -- spawn SR item instance delete
1158 --Bug 5220635
1159 csm_sr_event_pkg.sr_item_del_init(p_incident_id=>l_task_assg_purge_rec.incident_id,
1160 p_instance_id=>l_task_assg_purge_rec.customer_product_id,
1161 p_party_site_id=>l_task_assg_purge_rec.incident_location_id,
1162 p_party_id=>l_task_assg_purge_rec.party_id,
1163 p_location_id=>l_task_assg_purge_rec.location_id,
1164 p_organization_id=>NVL(l_task_assg_purge_rec.inv_organization_id, l_organization_id),
1165 p_user_id=>l_task_assg_purge_rec.user_id,
1166 p_flow_type=>NULL);
1167
1168 ELSIF l_task_assg_purge_rec.customer_product_id IS NULL OR l_task_assg_purge_rec.customer_product_id = 0 THEN
1169 IF l_task_assg_purge_rec.inventory_item_id IS NOT NULL THEN
1170 csm_mtl_system_items_event_pkg.mtl_system_items_acc_d
1171 (p_inventory_item_id=>l_task_assg_purge_rec.inventory_item_id,
1172 p_organization_id=>NVL(l_task_assg_purge_rec.inv_organization_id, l_organization_id),
1173 p_user_id=>l_task_assg_purge_rec.user_id,
1174 p_error_msg=>l_error_msg,
1175 x_return_status=>l_return_status);
1176 END IF;
1177 END IF;
1178
1179 -- spawn debrief line del
1180 csm_task_assignment_event_pkg.spawn_debrief_line_del(p_task_assignment_id=>p_task_assignment_id,
1181 p_user_id=>l_task_assg_purge_rec.user_id,
1182 p_flow_type=>NULL);
1183
1184 -- spawn debrief header del
1185 csm_task_assignment_event_pkg.spawn_debrief_header_del(p_task_assignment_id=>p_task_assignment_id,
1186 p_user_id=>l_task_assg_purge_rec.user_id,
1187 p_flow_type=>NULL);
1188
1189 -- spawn requirement line del
1190 csm_task_assignment_event_pkg.spawn_requirement_lines_del(p_task_assignment_id=>p_task_assignment_id,
1191 p_user_id=>l_task_assg_purge_rec.user_id,
1192 p_flow_type=>NULL);
1193
1194 -- spawn requirement header del
1195 csm_task_assignment_event_pkg.spawn_requirement_header_del(p_task_assignment_id=>p_task_assignment_id,
1196 p_user_id=>l_task_assg_purge_rec.user_id,
1197 p_flow_type=>NULL);
1198
1199 -- delete SR history
1200 IF l_task_assg_purge_rec.incident_id IS NOT NULL AND NOT l_htm5_user THEN
1201 csm_service_history_event_pkg.delete_history(p_task_assignment_id=>p_task_assignment_id,
1202 p_incident_id=>l_task_assg_purge_rec.incident_id,
1203 p_user_id=>l_task_assg_purge_rec.user_id);
1204 END IF;
1205
1206 -- task_assignments_acc delete
1207 csm_task_assignment_event_pkg.task_assignments_acc_d
1208 (p_task_assignment_id=>p_task_assignment_id,
1209 p_incident_id=>l_task_assg_purge_rec.incident_id,
1210 p_task_id=>l_task_assg_purge_rec.task_id,
1211 p_source_object_type_code=>l_task_assg_purge_rec.source_object_type_code,
1212 p_flow_type=>NULL,
1213 p_user_id=>l_task_assg_purge_rec.user_id);
1214
1215 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENT_PURGE_INIT for task_assignment_id: ' || p_task_assignment_id,
1216 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_PURGE_INIT',FND_LOG.LEVEL_PROCEDURE);
1217 EXCEPTION
1218 WHEN OTHERS THEN
1219 l_sqlerrno := to_char(SQLCODE);
1220 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1221 l_error_msg := ' Exception in TASK_ASSIGNMENT_PURGE_INIT for task_assignment_id:' || to_char(p_task_assignment_id)
1222 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1223 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_PURGE_INIT',FND_LOG.LEVEL_EXCEPTION);
1224 RAISE;
1225 END TASK_ASSIGNMENT_PURGE_INIT;
1226
1227 --Bug 4938130
1228 PROCEDURE LOBS_MDIRTY_D(p_task_assignment_id IN NUMBER, p_resource_id IN NUMBER)
1229 IS
1230 BEGIN
1231 CSM_LOBS_EVENT_PKG.DELETE_ACC_RECORD(p_task_assignment_id, p_resource_id);
1232 END LOBS_MDIRTY_D;
1233
1234 PROCEDURE SPAWN_DEBRIEF_HEADER_DEL (p_task_assignment_id IN NUMBER,
1235 p_user_id IN NUMBER,
1236 p_flow_type IN VARCHAR2)
1237 IS
1238 l_sqlerrno VARCHAR2(20);
1239 l_sqlerrmsg VARCHAR2(4000);
1240 l_error_msg VARCHAR2(4000);
1241 l_return_status VARCHAR2(2000);
1242
1243 -- cursor to get all debrief header id
1244 CURSOR l_debrief_header_csr(p_task_assg_id jtf_task_assignments.task_assignment_id%TYPE,
1245 p_user_id fnd_user.user_id%TYPE)
1246 IS
1247 SELECT hdr.debrief_header_id
1248 FROM csm_debrief_headers_acc acc,
1249 csf_debrief_headers hdr
1250 WHERE hdr.task_assignment_id = p_task_assg_id
1251 AND acc.debrief_header_id = hdr.debrief_header_id
1252 AND acc.user_id = p_user_id
1253 ;
1254
1255 BEGIN
1256 CSM_UTIL_PKG.LOG('Entering SPAWN_DEBRIEF_HEADER_DEL for task_assignment_id: ' || p_task_assignment_id,
1257 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_DEL',FND_LOG.LEVEL_PROCEDURE);
1258
1259 FOR r_debrief_header_rec IN l_debrief_header_csr(p_task_assignment_id, p_user_id) LOOP
1260 -- delete debrief headers
1261 csm_debrief_header_event_pkg.debrief_header_del_init(p_debrief_header_id=>r_debrief_header_rec.debrief_header_id,
1262 p_user_id=>p_user_id,
1263 p_flow_type=>p_flow_type);
1264 END LOOP;
1265
1266 CSM_UTIL_PKG.LOG('Leaving SPAWN_DEBRIEF_HEADER_DEL for task_assignment_id: ' || p_task_assignment_id,
1267 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_DEL',FND_LOG.LEVEL_PROCEDURE);
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270 l_sqlerrno := to_char(SQLCODE);
1271 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1272 l_error_msg := ' Exception in SPAWN_DEBRIEF_HEADER_DEL for task_assignment_id:'
1273 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1274 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_HEADER_DEL',FND_LOG.LEVEL_EXCEPTION);
1275 RAISE;
1276 END SPAWN_DEBRIEF_HEADER_DEL;
1277
1278 PROCEDURE SPAWN_DEBRIEF_LINE_DEL (p_task_assignment_id IN NUMBER,
1279 p_user_id IN NUMBER,
1280 p_flow_type IN VARCHAR2)
1281 IS
1282 l_sqlerrno VARCHAR2(20);
1283 l_sqlerrmsg VARCHAR2(4000);
1284 l_error_msg VARCHAR2(4000);
1285 l_return_status VARCHAR2(2000);
1286
1287 -- cursor to get all debrief line id
1288 CURSOR l_debrief_lines_csr(p_task_assg_id jtf_task_assignments.task_assignment_id%TYPE,
1289 p_user_id fnd_user.user_id%TYPE)
1290 IS
1291 SELECT dl.debrief_line_id,
1292 dl.debrief_header_id
1293 FROM csm_debrief_lines_acc acc,
1294 csF_debrief_headers hdr,
1295 csf_debrief_lines dl
1296 WHERE hdr.task_assignment_id = p_task_assg_id
1297 AND hdr.DEBRIEF_HEADER_ID = dl.DEBRIEF_HEADER_ID
1298 AND acc.debrief_line_id = dl.debrief_line_id
1299 AND acc.user_id = p_user_id
1300 ;
1301
1302 BEGIN
1303 CSM_UTIL_PKG.LOG('Entering SPAWN_DEBRIEF_LINE_DEL for task_assignment_id: ' || p_task_assignment_id,
1304 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_DEL',FND_LOG.LEVEL_PROCEDURE);
1305
1306 FOR r_debrief_line_rec IN l_debrief_lines_csr(p_task_assignment_id, p_user_id) LOOP
1307 -- delete debrief lines
1308 csm_debrief_event_pkg.debrief_line_del_init(p_debrief_line_id=>r_debrief_line_rec.debrief_line_id,
1309 p_user_id=>p_user_id,
1310 p_flow_type=>p_flow_type);
1311
1312 END LOOP;
1313
1314 CSM_UTIL_PKG.LOG('Leaving SPAWN_DEBRIEF_LINE_DEL for task_assignment_id: ' || p_task_assignment_id,
1315 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_DEL',FND_LOG.LEVEL_PROCEDURE);
1316 EXCEPTION
1317 WHEN OTHERS THEN
1318 l_sqlerrno := to_char(SQLCODE);
1319 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1320 l_error_msg := ' Exception in SPAWN_DEBRIEF_LINE_DEL for task_assignment_id:'
1321 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1322 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_DEBRIEF_LINE_DEL',FND_LOG.LEVEL_EXCEPTION);
1323 RAISE;
1324 END SPAWN_DEBRIEF_LINE_DEL;
1325
1326 PROCEDURE SPAWN_REQUIREMENT_HEADER_DEL(p_task_assignment_id IN NUMBER,
1327 p_user_id IN NUMBER,
1328 p_flow_type IN VARCHAR2)
1329 IS
1330 l_sqlerrno VARCHAR2(20);
1331 l_sqlerrmsg VARCHAR2(4000);
1332 l_error_msg VARCHAR2(4000);
1333 l_return_status VARCHAR2(2000);
1334
1335 -- cursor to get all requirement headers for the task_id
1336 CURSOR l_req_headers_csr(p_task_assg_id IN jtf_task_assignments.task_assignment_id%TYPE,
1337 p_user_id IN NUMBER)
1338 IS
1339 SELECT hdr.requirement_header_id,
1340 jta.resource_id
1341 FROM jtf_task_assignments jta,
1342 csp_requirement_headers hdr
1343 WHERE jta.task_assignment_id = p_task_assg_id
1344 AND hdr.task_id = jta.task_id
1345 AND EXISTS
1346 (SELECT 1
1347 FROM csm_req_headers_acc acc
1348 WHERE acc.requirement_header_id = hdr.requirement_header_id
1349 AND acc.user_id = p_user_id
1350 );
1351
1352 BEGIN
1353 CSM_UTIL_PKG.LOG('Entering SPAWN_REQUIREMENT_HEADER_DEL for task_assignment_id: ' || p_task_assignment_id,
1354 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_DEL',FND_LOG.LEVEL_PROCEDURE);
1355
1356 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
1357 FOR r_req_headers_rec IN l_req_headers_csr(p_task_assignment_id, p_user_id) LOOP
1358 -- delete requirement headers
1359 csm_csp_req_headers_event_pkg.csp_req_headers_mdirty_d(p_requirement_header_id=>r_req_headers_rec.requirement_header_id,
1360 p_user_id=>p_user_id);
1361 END LOOP;
1362 END IF;
1363
1364 CSM_UTIL_PKG.LOG('Leaving SPAWN_REQUIREMENT_HEADER_DEL for task_assignment_id: ' || p_task_assignment_id,
1365 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_DEL',FND_LOG.LEVEL_PROCEDURE);
1366 EXCEPTION
1367 WHEN OTHERS THEN
1368 l_sqlerrno := to_char(SQLCODE);
1369 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1370 l_error_msg := ' Exception in SPAWN_REQUIREMENT_HEADER_DEL for task_assignment_id:'
1371 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1372 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_HEADER_DEL',FND_LOG.LEVEL_EXCEPTION);
1373 RAISE;
1374 END SPAWN_REQUIREMENT_HEADER_DEL;
1375
1376 PROCEDURE SPAWN_REQUIREMENT_LINES_DEL(p_task_assignment_id IN NUMBER,
1377 p_user_id IN NUMBER,
1378 p_flow_type IN VARCHAR2)
1379 IS
1380 l_sqlerrno VARCHAR2(20);
1381 l_sqlerrmsg VARCHAR2(4000);
1382 l_error_msg VARCHAR2(4000);
1383 l_return_status VARCHAR2(2000);
1384
1385 -- cursor to get all requirement lines for the task_id
1386
1387 CURSOR l_req_lines_csr(p_task_assg_id IN jtf_task_assignments.task_assignment_id%TYPE,
1388 p_user_id IN NUMBER)
1389 IS
1390 SELECT line.requirement_line_id,
1391 line.requirement_header_id,
1392 acc.user_id
1393 FROM jtf_task_assignments jta,
1394 csp_requirement_headers hdr,
1395 csp_requirement_lines line,
1396 csm_req_lines_acc acc
1397 WHERE jta.task_assignment_id = p_task_assg_id
1398 AND hdr.task_id = jta.task_id
1399 AND line.requirement_header_id = hdr.requirement_header_id
1400 AND acc.requirement_line_id = line.requirement_line_id
1401 AND acc.user_id=p_user_id;
1402
1403 BEGIN
1404 CSM_UTIL_PKG.LOG('Entering SPAWN_REQUIREMENT_LINES_DEL for task_assignment_id: ' || p_task_assignment_id,
1405 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_LINES_DEL',FND_LOG.LEVEL_PROCEDURE);
1406
1407 IF p_flow_type IS NULL OR p_flow_type <> 'HISTORY' THEN
1408 FOR r_req_lines_rec IN l_req_lines_csr(p_task_assignment_id, p_user_id) LOOP
1409 -- delete requirement lines
1410 csm_csp_req_lines_event_pkg.csp_req_lines_mdirty_d(p_requirement_line_id=>r_req_lines_rec.requirement_line_id,
1411 p_user_id=>p_user_id);
1412
1413 FOR rec IN (SELECT req_line_detail_id from csp_req_line_details WHERE requirement_line_id=r_req_lines_rec.requirement_line_id)
1414 LOOP
1415 CSM_CSP_REQ_LINES_EVENT_PKG.REQ_LINE_DTL_IUD(rec.req_line_detail_id,'D');
1416 END LOOP;
1417 END LOOP;
1418 END IF;
1419
1420 CSM_UTIL_PKG.LOG('Leaving SPAWN_REQUIREMENT_LINES_DEL for task_assignment_id: ' || p_task_assignment_id,
1421 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_LINES_DEL',FND_LOG.LEVEL_PROCEDURE);
1422 EXCEPTION
1423 WHEN OTHERS THEN
1424 l_sqlerrno := to_char(SQLCODE);
1425 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1426 l_error_msg := ' Exception in SPAWN_REQUIREMENT_LINES_DEL for task_assignment_id:'
1427 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1428 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.SPAWN_REQUIREMENT_LINES_DEL',FND_LOG.LEVEL_EXCEPTION);
1429 RAISE;
1430 END SPAWN_REQUIREMENT_LINES_DEL;
1431
1432 PROCEDURE TASK_ASSIGNMENTS_ACC_D(p_task_assignment_id IN NUMBER,
1433 p_incident_id IN NUMBER,
1434 p_task_id IN NUMBER,
1435 p_source_object_type_code IN VARCHAR2,
1436 p_flow_type IN VARCHAR2,
1437 p_user_id IN NUMBER)
1438 IS
1439 l_sqlerrno VARCHAR2(20);
1440 l_sqlerrmsg VARCHAR2(4000);
1441 l_error_msg VARCHAR2(4000);
1442 l_return_status VARCHAR2(2000);
1443
1444 BEGIN
1445 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENTS_ACC_D for task_assignment_id: ' || p_task_assignment_id,
1446 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1447
1448 IF p_source_object_type_code = 'SR' THEN
1449 csm_sr_event_pkg.incidents_acc_d(p_incident_id=>p_incident_id,p_user_id=>p_user_id);
1450
1451 IF (NOT CSM_UTIL_PKG.IS_HTML5_USER(p_user_id) AND (p_flow_type IS NULL OR p_flow_type <> 'HISTORY')) THEN
1452 csm_contract_event_pkg.sr_contract_acc_d(p_incident_id=>p_incident_id,p_user_id=>p_user_id);
1453 END IF ;
1454 END IF;
1455
1456 -- delete tasks
1457 csm_task_event_pkg.acc_delete(p_user_id=>p_user_id, p_task_id=>p_task_id);
1458
1459 -- delete task assignments
1460 csm_task_assignment_event_pkg.acc_delete(p_task_assignment_id=>p_task_assignment_id,
1461 p_user_id=>p_user_id);
1462
1463 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENTS_ACC_D for task_assignment_id: ' || p_task_assignment_id,
1464 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
1465 EXCEPTION
1466 WHEN OTHERS THEN
1467 l_sqlerrno := to_char(SQLCODE);
1468 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1469 l_error_msg := ' Exception in TASK_ASSIGNMENTS_ACC_D for task_assignment_id:'
1470 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1471 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENTS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
1472 RAISE;
1473 END TASK_ASSIGNMENTS_ACC_D;
1474
1475 PROCEDURE ACC_DELETE(p_task_assignment_id IN NUMBER, p_user_id IN NUMBER)
1476 IS
1477 l_sqlerrno VARCHAR2(20);
1478 l_sqlerrmsg VARCHAR2(4000);
1479 l_error_msg VARCHAR2(4000);
1480 l_return_status VARCHAR2(2000);
1481
1482 BEGIN
1483 CSM_UTIL_PKG.LOG('Entering ACC_DELETE for task_assignment_id: ' || p_task_assignment_id,
1484 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_PROCEDURE);
1485
1486 CSM_ACC_PKG.Delete_Acc
1487 ( P_PUBLICATION_ITEM_NAMES => g_pubi_name
1488 ,P_ACC_TABLE_NAME => g_acc_table_name
1489 ,P_PK1_NAME => g_pk1_name
1490 ,P_PK1_NUM_VALUE => p_task_assignment_id
1491 ,P_USER_ID => p_user_id
1492 );
1493
1494 CSM_UTIL_PKG.LOG('Leaving ACC_DELETE for task_assignment_id: ' || p_task_assignment_id,
1495 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_PROCEDURE);
1496 EXCEPTION
1497 WHEN OTHERS THEN
1498 l_sqlerrno := to_char(SQLCODE);
1499 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1500 l_error_msg := ' Exception in ACC_DELETE for task_assignment_id:'
1501 || to_char(p_task_assignment_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1502 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.ACC_DELETE',FND_LOG.LEVEL_EXCEPTION);
1503 RAISE;
1504 END ACC_DELETE;
1505
1506 PROCEDURE TASK_ASSIGNMENT_HIST_DEL_INIT(p_task_assignment_id IN NUMBER,
1507 p_parent_incident_id IN NUMBER,
1508 p_user_id IN NUMBER,
1509 p_error_msg OUT NOCOPY VARCHAR2,
1510 x_return_status IN OUT NOCOPY VARCHAR2)
1511 IS
1512 l_sqlerrno VARCHAR2(20);
1513 l_sqlerrmsg VARCHAR2(4000);
1514 l_error_msg VARCHAR2(4000);
1515 l_return_status VARCHAR2(2000);
1516 l_organization_id NUMBER;
1517 l_flowtype VARCHAR2(20);
1518
1519 CURSOR l_TaskAssgHistPurge_csr (p_task_assignment_id jtf_task_assignments.task_assignment_id%TYPE)
1520 IS
1521 --Bug 5220635
1522 SELECT jta.resource_id,
1523 jta.task_id,
1524 csi.incident_id,
1525 csi.customer_id,
1526 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
1527 hz_ps.party_site_id,
1528 hz_ps.party_id,
1529 csi.inventory_item_id,
1530 csi.inv_organization_id,
1531 csi.contract_service_id,
1532 csi.customer_product_id,
1533 hz_ps.location_id,
1534 jt.source_object_type_code,
1535 csi.incident_location_type
1536 FROM JTF_TASK_ASSIGNMENTS jta,
1537 jtf_tasks_b jt,
1538 cs_incidents_all_b csi,
1539 hz_party_sites hz_ps
1540 WHERE jta.task_assignment_id = p_task_assignment_id
1541 AND jt.task_id = jta.task_id
1542 AND jt.source_object_type_code = 'SR'
1543 AND jt.source_object_id = csi.incident_id
1544 AND hz_ps.party_site_id = NVL(csi.incident_location_id, jt.ADDRESS_ID) -- csi.install_site_use_id
1545 AND NVL(csi.incident_location_type,'HZ_PARTY_SITE')='HZ_PARTY_SITE'
1546 UNION
1547 SELECT jta.resource_id,
1548 jta.task_id,
1549 csi.incident_id,
1550 csi.customer_id,
1551 NVL(csi.incident_location_id, jt.ADDRESS_ID) AS incident_location_id,
1552 NULL,
1553 csi.customer_id,
1554 csi.inventory_item_id,
1555 csi.inv_organization_id,
1556 csi.contract_service_id,
1557 csi.customer_product_id,
1558 lc.location_id,
1559 jt.source_object_type_code,
1560 csi.incident_location_type
1561 FROM JTF_TASK_ASSIGNMENTS jta,
1562 jtf_tasks_b jt,
1563 cs_incidents_all_b csi,
1564 hz_locations lc
1565 WHERE jta.task_assignment_id = p_task_assignment_id
1566 AND jt.task_id = jta.task_id
1567 AND jt.source_object_type_code = 'SR'
1568 AND jt.source_object_id = csi.incident_id
1569 AND lc.location_id = NVL(jt.LOCATION_ID,csi.incident_location_id ) -- csi.install_site_use_id;
1570 AND csi.incident_location_type='HZ_LOCATION'
1571 ;
1572
1573 l_TaskAssgHistPurge_rec l_TaskAssgHistPurge_csr%ROWTYPE;
1574 l_TaskAssgHistPurge_null_rec l_TaskAssgHistPurge_csr%ROWTYPE;
1575
1576 BEGIN
1577 x_return_status := FND_API.G_RET_STS_SUCCESS;
1578 CSM_UTIL_PKG.LOG('Entering TASK_ASSIGNMENT_HIST_DEL_INIT for task_assignment_id: ' || p_task_assignment_id
1579 || ' and parent_incident_id: ' || p_parent_incident_id,
1580 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
1581
1582 l_TaskAssgHistPurge_rec := l_TaskAssgHistPurge_null_rec;
1583 l_flowtype := 'HISTORY';
1584
1585 OPEN l_TaskAssgHistPurge_csr(p_task_assignment_id);
1586 FETCH l_TaskAssgHistPurge_csr INTO l_TaskAssgHistPurge_rec;
1587 IF l_TaskAssgHistPurge_csr%NOTFOUND THEN
1588 CLOSE l_TaskAssgHistPurge_csr;
1589 CSM_UTIL_PKG.LOG('No date found for history task_assignment_id: ' || p_task_assignment_id,
1590 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_INIT',FND_LOG.LEVEL_EXCEPTION);
1591 RETURN;
1592 END IF;
1593 CLOSE l_TaskAssgHistPurge_csr;
1594
1595 -- delete from service history acc
1596 csm_service_history_event_pkg.service_history_acc_d(p_parent_incident_id=>p_parent_incident_id,
1597 p_incident_id=>l_TaskAssgHistPurge_rec.incident_id,
1598 p_user_id=>p_user_id);
1599
1600 -- delete SR contacts
1601 csm_sr_event_pkg.spawn_sr_contact_del(p_incident_id=>l_TaskAssgHistPurge_rec.incident_id,
1602 p_user_id=>p_user_id,
1603 p_flowtype=>l_flowtype);
1604 IF l_TaskAssgHistPurge_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
1605 --insert location for the sr
1606 csm_hz_locations_event_pkg.delete_location(p_location_id => l_TaskAssgHistPurge_rec.location_id,
1607 p_user_id => p_user_id);
1608 ELSE
1609
1610 -- spawn party site del
1611 --Bug 5220635
1612 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => l_TaskAssgHistPurge_rec.incident_location_id,
1613 p_user_id => p_user_id,
1614 p_flowtype => l_flowtype,
1615 p_error_msg => l_error_msg,
1616 x_return_status => l_return_status);
1617 END IF;
1618 -- spawn SR customer del
1619 csm_party_event_pkg.party_acc_d(p_party_id => l_TaskAssgHistPurge_rec.customer_id,
1620 p_user_id => p_user_id,
1621 p_flowtype => l_flowtype,
1622 p_error_msg => l_error_msg,
1623 x_return_status => l_return_status);
1624
1625 --Delete Accounts for the above party-R12
1626 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL
1627 (p_party_id=> l_TaskAssgHistPurge_rec.customer_id
1628 ,p_user_id => p_user_id);
1629
1630 -- delete customer product
1631 IF l_TaskAssgHistPurge_rec.customer_product_id IS NOT NULL THEN
1632 csm_item_instance_event_pkg.item_instances_acc_d(p_instance_id=>l_TaskAssgHistPurge_rec.customer_product_id,
1633 p_user_id=>p_user_id,
1634 p_error_msg=>l_error_msg,
1635 x_return_status=>l_return_status);
1636 END IF;
1637
1638 -- spawn debrief line del
1639 csm_task_assignment_event_pkg.spawn_debrief_line_del(p_task_assignment_id=>p_task_assignment_id,
1640 p_user_id=>p_user_id,
1641 p_flow_type=>l_flowtype);
1642
1643 -- spawn debrief header del
1644 csm_task_assignment_event_pkg.spawn_debrief_header_del(p_task_assignment_id=>p_task_assignment_id,
1645 p_user_id=>p_user_id,
1646 p_flow_type=>l_flowtype);
1647
1648 -- resource extns acc del
1649 csm_resource_extns_event_pkg.resource_extns_acc_d(p_resource_id=>l_TaskAssgHistPurge_rec.resource_id,
1650 p_user_id=>p_user_id);
1651
1652 -- task_assignments_acc delete
1653 csm_task_assignment_event_pkg.task_assignments_acc_d
1654 (p_task_assignment_id=>p_task_assignment_id,
1655 p_incident_id=>l_TaskAssgHistPurge_rec.incident_id,
1656 p_task_id=>l_TaskAssgHistPurge_rec.task_id,
1657 p_source_object_type_code=>l_TaskAssgHistPurge_rec.source_object_type_code,
1658 p_flow_type=>l_flowtype,
1659 p_user_id=>p_user_id);
1660
1661 CSM_UTIL_PKG.LOG('Leaving TASK_ASSIGNMENT_HIST_DEL_INIT for task_assignment_id: ' || p_task_assignment_id
1662 || ' and parent_incident_id: ' || p_parent_incident_id,
1663 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
1664 EXCEPTION
1665 WHEN OTHERS THEN
1666 l_sqlerrno := to_char(SQLCODE);
1667 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1668 x_return_status := FND_API.G_RET_STS_ERROR;
1669 p_error_msg := ' Exception in TASK_ASSIGNMENT_HIST_DEL_INIT for task_assignment_id:' || p_task_assignment_id
1670 || ' and parent_incident_id: ' || p_parent_incident_id || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1671 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_TASK_ASSIGNMENT_EVENT_PKG.TASK_ASSIGNMENT_HIST_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
1672 RAISE;
1673 END TASK_ASSIGNMENT_HIST_DEL_INIT;
1674
1675 END CSM_TASK_ASSIGNMENT_EVENT_PKG;