[Home] [Help]
PACKAGE BODY: APPS.CSM_SR_EVENT_PKG
Source
1 PACKAGE BODY CSM_SR_EVENT_PKG AS
2 /* $Header: csmesrb.pls 120.12 2008/05/01 09:26:38 saradhak ship $ */
3
4 -- MODIFICATION HISTORY
5 -- Person Date Comments
6 -- --------- ------ ------------------------------------------
7 -- Melvin P 05/02/02 Base creation
8 -- Enter procedure, function bodies as shown below
9 /*** Globals ***/
10 g_incidents_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_INCIDENTS_ALL_ACC';
11 g_incidents_table_name CONSTANT VARCHAR2(30) := 'CS_INCIDENTS_ALL';
12 g_incidents_seq_name CONSTANT VARCHAR2(30) := 'CSM_INCIDENTS_ALL_ACC_S' ;
13 g_incidents_pk1_name CONSTANT VARCHAR2(30) := 'INCIDENT_ID';
14 g_incidents_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
15 CSM_ACC_PKG.t_publication_item_list('CSM_INCIDENTS_ALL');
16
17 g_table_name1 CONSTANT VARCHAR2(30) := 'CSM_CSI_ITEM_ATTR';
18 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_CSI_ITEM_ATTR_ACC';
19 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_CSI_ITEM_ATTR_ACC_S';
20 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
21 CSM_ACC_PKG.t_publication_item_list('CSM_CSI_ITEM_ATTR');
22 g_pk1_name1 CONSTANT VARCHAR2(30) := 'ATTRIBUTE_VALUE_ID';
23 g_pub_item CONSTANT VARCHAR2(30) := 'CSM_CSI_ITEM_ATTR';
24
25 incident_not_found exception;
26
27 /**
28 ** Conc program called every midnight to purge task assignments depending on the
29 ** history profile of the user
30 **/
31 --12.1XB6
32 PROCEDURE PURGE_INCIDENTS_CONC (p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
33 IS
34 PRAGMA AUTONOMOUS_TRANSACTION;
35 l_sqlerrno VARCHAR2(20);
36 l_sqlerrmsg VARCHAR2(4000);
37 l_error_msg VARCHAR2(4000);
38 l_return_status VARCHAR2(2000);
39 l_incident_id cs_incidents_all_b.incident_id%TYPE;
40 l_dummy number;
41 l_last_run_date date;
42
43
44 CURSOR l_purge_incidents_csr
45 IS
46 SELECT acc.incident_id,
47 acc.user_id,
48 acc.counter
49 FROM csm_incidents_all_acc acc,
50 cs_incidents_all_b inc,
51 cs_incident_statuses_b ists
52 WHERE inc.incident_id = acc.incident_id
53 AND decode(CSM_UTIL_PKG.get_group_owner(inc.owner_group_id),
54 -1,
55 CSM_UTIL_PKG.get_owner(inc.created_by),
56 CSM_UTIL_PKG.get_group_owner(inc.owner_group_id)) = acc.user_id
57 AND (inc.creation_date < (SYSDATE - csm_profile_pkg.get_task_history_days(acc.user_id)))
58 AND inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
59 AND ists.CLOSE_FLAG = 'Y';
60
61
62 CURSOR l_upd_last_run_date_csr
63 IS
64 SELECT 1
65 FROM jtm_con_request_data
66 WHERE product_code = 'CSM'
67 AND package_name = 'CSM_SR_EVENT_PKG'
68 AND procedure_name = 'PURGE_INCIDENTS_CONC'
69 FOR UPDATE OF last_run_date NOWAIT;
70
71 TYPE l_incident_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
72 l_incident_tbl l_incident_tbl_type;
73 TYPE l_userid_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
74 l_userid_tbl l_userid_tbl_type;
75 TYPE l_cnt_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
76 l_cnt_tbl l_cnt_tbl_type;
77
78
79 BEGIN
80 l_last_run_date := SYSDATE;
81
82 OPEN l_purge_incidents_csr;
83 LOOP
84 IF l_incident_tbl.COUNT > 0 THEN
85 l_incident_tbl.DELETE;
86 END IF;
87 IF l_userid_tbl.COUNT > 0 THEN
88 l_userid_tbl.DELETE;
89 END IF;
90
91 FETCH l_purge_incidents_csr BULK COLLECT INTO l_incident_tbl,l_userid_tbl,l_cnt_tbl LIMIT 50;
92 EXIT WHEN l_incident_tbl.COUNT = 0;
93
94 CSM_UTIL_PKG.LOG(TO_CHAR(l_incident_tbl.COUNT) || ' records sent for purge', 'CSM_SR_EVENT_PKG.PURGE_INCIDENTS_CONC',FND_LOG.LEVEL_EVENT);
95
96 FOR I IN 1..l_incident_tbl.count
97 LOOP
98 l_incident_id:=l_incident_tbl(I);
99 FOR j IN 1..l_cnt_tbl.COUNT
100 LOOP
101 csm_sr_event_pkg.sr_del_init(p_incident_id=>l_incident_id);
102 END LOOP;
103 END LOOP;
104 -- commit after every 50 records
105 COMMIT;
106 END LOOP;
107
108 CLOSE l_purge_incidents_csr;
109
110 -- update last_run_date
111 OPEN l_upd_last_run_date_csr;
112 FETCH l_upd_last_run_date_csr INTO l_dummy;
113 IF l_upd_last_run_date_csr%FOUND THEN
114 UPDATE jtm_con_request_data
115 SET last_run_date = l_last_run_date
116 WHERE CURRENT OF l_upd_last_run_date_csr;
117 END IF;
118 CLOSE l_upd_last_run_date_csr;
119
120 COMMIT;
121
122 p_status := 'SUCCESS';
123 p_message := 'CSM_SR_EVENT_PKG.PURGE_INCIDENTS_CONC Executed successfully';
124
125 EXCEPTION
126 --log the error
127 WHEN OTHERS THEN
128 l_sqlerrno := to_char(SQLCODE);
129 l_sqlerrmsg := substr(SQLERRM, 1,2000);
130 ROLLBACK;
131 l_error_msg := ' Exception in Purge_incidents_conc for incident_id:'
132 || to_char(l_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
133 p_status := 'ERROR';
134 p_message := 'Error in CSM_SR_EVENT_PKG.PURGE_INCIDENTS_CONC: ' || l_error_msg;
135 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.PURGE_INCIDENTS_CONC',FND_LOG.LEVEL_EVENT);
136 -- x_return_status := FND_API.G_RET_STS_ERROR ;
137 -- RAISE;
138 END PURGE_INCIDENTS_CONC;
139
140 FUNCTION IS_SR_TASK ( p_task_id IN NUMBER)
141 RETURN BOOLEAN
142 IS
143 l_sqlerrno VARCHAR2(20);
144 l_sqlerrmsg VARCHAR2(4000);
145 l_error_msg VARCHAR2(4000);
146 l_source_object_type_code jtf_tasks_b.source_object_type_code%TYPE;
147
148 CURSOR l_jtf_tasks_csr(p_task_id jtf_tasks_b.task_id%TYPE)
149 IS
150 SELECT tsk.source_object_type_code
151 FROM jtf_tasks_b tsk, jtf_task_types_b ttype
152 WHERE tsk.task_id = p_task_id
153 AND tsk.task_type_id = ttype.task_type_id
154 AND (ttype.RULE = 'DISPATCH' OR ttype.private_flag = 'Y')
155 AND tsk.SCHEDULED_START_DATE IS NOT NULL
156 AND tsk.SCHEDULED_END_DATE IS NOT NULL
157 ;
158
159 BEGIN
160 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.IS_SR_TASK for task_id: ' || p_task_id,
161 'CSM_SR_EVENT_PKG.IS_SR_TASK',FND_LOG.LEVEL_PROCEDURE);
162
163 l_source_object_type_code := NULL;
164
165 OPEN l_jtf_tasks_csr(p_task_id);
166 FETCH l_jtf_tasks_csr INTO l_source_object_type_code;
167 IF l_jtf_tasks_csr%NOTFOUND THEN
168 CLOSE l_jtf_tasks_csr;
169 CSM_UTIL_PKG.LOG('Not a mobile Task Type for task_id: ' || p_task_id,
170 'CSM_SR_EVENT_PKG.IS_SR_TASK',FND_LOG.LEVEL_EXCEPTION);
171 RETURN FALSE;
172 END IF;
173 CLOSE l_jtf_tasks_csr;
174
175 IF (l_source_object_type_code IN ('SR', 'TASK') OR l_source_object_type_code IS NULL) THEN
176 CSM_UTIL_PKG.LOG('Leaving CSM_SR_EVENT_PKG.IS_SR_TASK for task_id: ' || p_task_id,
177 'CSM_SR_EVENT_PKG.IS_SR_TASK', FND_LOG.LEVEL_PROCEDURE);
178 RETURN TRUE;
179 ELSE
180 CSM_UTIL_PKG.LOG('Not a mobile Task Type for task_id: ' || p_task_id,
181 'CSM_SR_EVENT_PKG.IS_SR_TASK',FND_LOG.LEVEL_EXCEPTION);
182 RETURN FALSE;
183 END IF;
184
185 EXCEPTION
186 WHEN OTHERS THEN
187 l_sqlerrno := to_char(SQLCODE);
188 l_sqlerrmsg := substr(SQLERRM, 1,2000);
189 l_error_msg := ' Exception in IS_SR_TASK for task_id:' || to_char(p_task_id)
190 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
191 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.IS_SR_TASK',FND_LOG.LEVEL_EXCEPTION);
192 RETURN FALSE;
193 END IS_SR_TASK;
194
195 FUNCTION IS_TASK_STATUS_DOWNLOADABLE(p_task_id IN NUMBER)
196 RETURN BOOLEAN
197 IS
198 l_sqlerrno VARCHAR2(20);
199 l_sqlerrmsg VARCHAR2(4000);
200 l_error_msg VARCHAR2(4000);
201 l_dummy NUMBER;
202
203 CURSOR l_jtf_task_status_csr(p_task_id jtf_tasks_b.task_id%TYPE)
204 IS
205 SELECT 1
206 FROM jtf_tasks_b jt,
207 jtf_task_statuses_b jts
208 WHERE jt.task_id = p_task_id
209 AND jt.task_status_id = jts.task_status_id
210 AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL
211 OR (jts.assigned_flag = 'Y'
212 OR jts.closed_flag = 'Y'
213 OR jts.completed_flag = 'Y'
214 ));
215
216 BEGIN
217 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.IS_TASK_STATUS_DOWNLOADABLE for task_id: ' || p_task_id,
218 'CSM_SR_EVENT_PKG.IS_TASK_STATUS_DOWNLOADABLE',FND_LOG.LEVEL_PROCEDURE);
219
220 OPEN l_jtf_task_status_csr(p_task_id);
221 FETCH l_jtf_task_status_csr INTO l_dummy;
222 IF l_jtf_task_status_csr%NOTFOUND THEN
223 CLOSE l_jtf_task_status_csr;
224 CSM_UTIL_PKG.LOG('Task Status not downloadable for task_id: ' || p_task_id,
225 'CSM_SR_EVENT_PKG.IS_TASK_STATUS_DOWNLOADABLE',FND_LOG.LEVEL_EXCEPTION);
226 RETURN FALSE;
227 END IF;
228 CLOSE l_jtf_task_status_csr;
229
230 CSM_UTIL_PKG.LOG('Leaving CSM_SR_EVENT_PKG.IS_TASK_STATUS_DOWNLOADABLE for task_id: ' || p_task_id,
231 'CSM_SR_EVENT_PKG.IS_TASK_STATUS_DOWNLOADABLE',FND_LOG.LEVEL_PROCEDURE);
232
233 RETURN TRUE;
234 EXCEPTION
235 WHEN OTHERS THEN
236 l_sqlerrno := to_char(SQLCODE);
237 l_sqlerrmsg := substr(SQLERRM, 1,2000);
238 l_error_msg := ' Exception in IS_TASK_STATUS_DOWNLOADABLE for task_id:' || to_char(p_task_id)
239 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
240 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.IS_TASK_STATUS_DOWNLOADABLE',FND_LOG.LEVEL_EXCEPTION);
241 RETURN FALSE;
242 END IS_TASK_STATUS_DOWNLOADABLE;
243
244 FUNCTION IS_ASSGN_STATUS_DOWNLOADABLE(p_task_assignment_id IN NUMBER)
245 RETURN BOOLEAN
246 IS
247 l_sqlerrno VARCHAR2(20);
248 l_sqlerrmsg VARCHAR2(4000);
249 l_error_msg VARCHAR2(4000);
250 l_dummy NUMBER;
251
252 CURSOR l_jtf_task_assg_status_csr(p_task_assg_id NUMBER)
253 IS
254 SELECT 1
255 FROM jtf_task_assignments jta,
256 jtf_tasks_b jt,
257 jtf_task_statuses_b jts
258 WHERE jta.task_assignment_id = p_task_assg_id
259 AND jt.task_id = jta.task_id
260 AND jta.assignment_status_id = jts.task_status_id
261 AND (jt.source_object_type_code = 'TASK' OR jt.source_object_type_code IS NULL
262 OR (jts.assigned_flag = 'Y'
263 OR jts.closed_flag = 'Y'
264 OR jts.completed_flag = 'Y'
265 ));
266
267 BEGIN
268 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.IS_ASSGN_STATUS_DOWNLOADABLE for task_assignment_id: ' || p_task_assignment_id,
269 'CSM_SR_EVENT_PKG.IS_ASSGN_STATUS_DOWNLOADABLE',FND_LOG.LEVEL_PROCEDURE);
270
271 OPEN l_jtf_task_assg_status_csr(p_task_assignment_id);
272 FETCH l_jtf_task_assg_status_csr INTO l_dummy;
273 IF l_jtf_task_assg_status_csr%NOTFOUND THEN
274 CLOSE l_jtf_task_assg_status_csr;
275 CSM_UTIL_PKG.LOG('Task Assignment Status not downloadable for task_assignment_id: ' || p_task_assignment_id,
276 'CSM_SR_EVENT_PKG.IS_ASSGN_STATUS_DOWNLOADABLE',FND_LOG.LEVEL_EXCEPTION);
277 RETURN FALSE;
278 END IF;
279 CLOSE l_jtf_task_assg_status_csr;
280
281 CSM_UTIL_PKG.LOG('Leaving CSM_SR_EVENT_PKG.IS_ASSGN_STATUS_DOWNLOADABLE for task_assignment_id: ' || p_task_assignment_id,
282 'CSM_SR_EVENT_PKG.IS_ASSGN_STATUS_DOWNLOADABLE',FND_LOG.LEVEL_PROCEDURE);
283
284 RETURN TRUE;
285 EXCEPTION
286 WHEN OTHERS THEN
287 l_sqlerrno := to_char(SQLCODE);
288 l_sqlerrmsg := substr(SQLERRM, 1,2000);
289 l_error_msg := ' Exception in IS_ASSGN_STATUS_DOWNLOADABLE for task_assignment_id:' || to_char(p_task_assignment_id)
290 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
291 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.IS_ASSGN_STATUS_DOWNLOADABLE',FND_LOG.LEVEL_EXCEPTION);
292 RETURN FALSE;
293 END IS_ASSGN_STATUS_DOWNLOADABLE;
294
295 PROCEDURE SPAWN_SR_CONTACTS_INS(p_incident_id IN NUMBER, p_sr_contact_point_id IN NUMBER,
296 p_user_id IN NUMBER, p_flowtype IN VARCHAR2)
297 IS
298 l_sqlerrno VARCHAR2(20);
299 l_sqlerrmsg VARCHAR2(4000);
300 l_error_msg VARCHAR2(4000);
301 l_return_status VARCHAR2(2000);
302 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
303 l_party_id hz_parties.party_id%TYPE;
304
305 CURSOR l_srcontpts_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE,
306 p_sr_contact_point_id NUMBER)
307 IS
308 SELECT sr_contact_point_id,
309 contact_point_id,
310 contact_type,
311 party_id
312 FROM cs_hz_sr_contact_points
313 WHERE incident_id = p_incident_id
314 AND sr_contact_point_id = NVL(p_sr_contact_point_id, sr_contact_point_id);
315
316 CURSOR l_emp_resource_csr (p_party_id hz_parties.party_id%TYPE)
317 IS
318 SELECT jtrs.resource_id
319 FROM jtf_rs_resource_extns jtrs
320 WHERE jtrs.source_id = p_party_id
321 AND jtrs.CATEGORY = 'EMPLOYEE'
322 AND SYSDATE BETWEEN jtrs.start_date_active AND nvl(jtrs.end_date_active, SYSDATE)
323 ;
324
325 BEGIN
326 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.SPAWN_SR_CONTACTS_INS for incident_id: ' || p_incident_id,
327 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACTS_INS',FND_LOG.LEVEL_PROCEDURE);
328
329 FOR r_srcontpts_rec IN l_srcontpts_csr(p_incident_id, p_sr_contact_point_id) LOOP
330 IF r_srcontpts_rec.contact_type = 'EMPLOYEE' THEN
331 OPEN l_emp_resource_csr(r_srcontpts_rec.party_id);
332 FETCH l_emp_resource_csr INTO l_resource_id;
333 CLOSE l_emp_resource_csr;
334
335 -- insert resource into acc table
336 IF l_resource_id IS NOT NULL THEN
337 csm_resource_extns_event_pkg.resource_extns_acc_i(p_resource_id=>l_resource_id,
338 p_user_id=>p_user_id);
339 END IF;
340 END IF; --Bug 6880063
341 -- insert party record
342 csm_party_event_pkg.party_acc_i(p_party_id=> r_srcontpts_rec.party_id,
343 p_user_id=> p_user_id,
344 p_flowtype=> p_flowtype,
345 p_error_msg=> l_error_msg,
346 x_return_status=> l_return_status);
347
348 -- insert sr_contact_point into acc table
349 csm_sr_contact_event_pkg.sr_cntact_mdirty_i(p_sr_contact_point_id=>r_srcontpts_rec.sr_contact_point_id,
350 p_user_id=>p_user_id);
351 END LOOP;
352
353 CSM_UTIL_PKG.LOG('Leaving CSM_SR_EVENT_PKG.SPAWN_SR_CONTACTS_INS for task_assignment_id: ' || p_incident_id,
354 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACTS_INS',FND_LOG.LEVEL_PROCEDURE);
355 EXCEPTION
356 WHEN OTHERS THEN
357 l_sqlerrno := to_char(SQLCODE);
358 l_sqlerrmsg := substr(SQLERRM, 1,2000);
359 l_error_msg := ' Exception in SPAWN_SR_CONTACTS_INS for incident_id:' || to_char(p_incident_id)
360 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
361 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACTS_INS',FND_LOG.LEVEL_EXCEPTION);
362 RAISE;
363 END SPAWN_SR_CONTACTS_INS;
364
365 PROCEDURE SR_ITEM_INS_INIT(p_incident_id IN NUMBER, p_instance_id IN NUMBER, p_party_site_id IN NUMBER,
366 p_party_id IN NUMBER, p_location_id IN NUMBER, p_organization_id IN NUMBER,
367 p_user_id IN NUMBER, p_flow_type IN VARCHAR2)
368 IS
369 l_sqlerrno VARCHAR2(20);
370 l_sqlerrmsg VARCHAR2(4000);
371 l_error_msg VARCHAR2(4000);
372 l_return_status VARCHAR2(2000);
373
374 BEGIN
375 CSM_UTIL_PKG.LOG('Entering SR_ITEM_INS_INIT for incident_id: ' || p_incident_id ||
376 ' and instance_id: ' || p_instance_id,'CSM_SR_EVENT_PKG.SR_ITEM_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
377
378 -- initialize count of IB items at location to 0;
379 csm_sr_event_pkg.g_ib_count := 0;
380
381 -- item instances fork
382 csm_sr_event_pkg.spawn_item_instances_ins(p_instance_id=>p_instance_id,p_organization_id=>p_organization_id,
383 p_user_id=>p_user_id);
384
385 -- get IB at location
386 csm_item_instance_event_pkg.get_ib_at_location(p_instance_id=>p_instance_id,p_party_site_id=>p_party_site_id,
387 p_party_id=>p_party_id,p_location_id=>p_location_id,
388 p_user_id=>p_user_id, p_flow_type=>p_flow_type);
389
390 -- spawn counters INS
391 csm_item_instance_event_pkg.spawn_counters_ins(p_instance_id=>p_instance_id, p_user_id=>p_user_id);
392
393 -- get IB notes
394 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'CP',
395 p_sourceobjectid=>p_instance_id,
396 p_userid=>p_user_id,
397 p_error_msg=>l_error_msg,
398 x_return_status=>l_return_status);
399
400 CSM_UTIL_PKG.LOG('Leaving SR_ITEM_INS_INIT for incident_id: ' || p_incident_id ||
401 ' and instance_id: ' || p_instance_id,'CSM_SR_EVENT_PKG.SR_ITEM_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
402 EXCEPTION
403 WHEN OTHERS THEN
404 l_sqlerrno := to_char(SQLCODE);
405 l_sqlerrmsg := substr(SQLERRM, 1,2000);
406 l_error_msg := ' Exception in SR_ITEM_INS_INIT for incident_id: ' || p_incident_id || ' and instance_id:'
407 || to_char(p_instance_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
408 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_ITEM_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
409 RAISE;
410 END SR_ITEM_INS_INIT;
411
412 PROCEDURE SPAWN_ITEM_INSTANCES_INS (p_instance_id IN NUMBER, p_organization_id IN NUMBER, p_user_id IN NUMBER)
413 IS
414 l_sqlerrno VARCHAR2(20);
415 l_sqlerrmsg VARCHAR2(4000);
416 l_error_msg VARCHAR2(4000);
417 l_return_status VARCHAR2(2000);
418 l_transaction_date DATE;
419 l_null_relationship_id csi_ii_relationships.relationship_id%TYPE;
420 l_organization_id NUMBER;
421
422 -- get all the child intances of the parent and the parent instance
423 -- also check these instances do not exist for the user
424 CURSOR l_instance_children_csr (p_instance_id csi_item_instances.instance_id%TYPE,
425 p_transaction_date DATE, p_user_id fnd_user.user_id%TYPE)
426 IS
427 SELECT cir.relationship_id AS relationship_id ,
428 cir.subject_id AS instance_id ,
429 cii.inventory_item_id
430 FROM (SELECT * FROM CSI_II_RELATIONSHIPS CIRo
431 START WITH CIRo.OBJECT_ID = p_instance_id
432 AND CIRo.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
433 AND p_transaction_date BETWEEN NVL(CIRo.active_start_date, p_transaction_date)
434 AND NVL(CIRo.active_end_date, p_transaction_date)
435 CONNECT BY CIRo.OBJECT_ID = PRIOR CIRo.SUBJECT_ID
436 ) CIR,
437 CSI_ITEM_INSTANCES CII
438 WHERE CII.INSTANCE_ID = CIR.SUBJECT_ID
439 AND p_transaction_date BETWEEN NVL ( CII.ACTIVE_START_DATE , p_transaction_date )
440 AND NVL ( CII.ACTIVE_END_DATE , p_transaction_date)
441 UNION
442 -- select the current instance
443 SELECT l_null_relationship_id AS relationship_id,
444 cii.instance_id AS instance_id,
445 cii.inventory_item_id
446 FROM csi_item_instances cii
447 WHERE cii.instance_id = p_instance_id
448 AND p_transaction_date BETWEEN NVL(cii.active_start_date, p_transaction_date)
449 AND NVL(cii.active_end_date, p_transaction_date)
450 UNION
451 SELECT cir.relationship_id AS relationship_id,
452 cir.object_id AS instance_id,
453 cii.inventory_item_id
454 FROM CSI_II_RELATIONSHIPS cir,
455 csi_item_instances cii
456 WHERE cir.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
457 AND cir.SUBJECT_ID = p_instance_id
458 AND cii.instance_id = cir.object_id
459 AND p_transaction_date BETWEEN NVL(cir.active_start_date, p_transaction_date)
460 AND NVL(cir.active_end_date, p_transaction_date)
461 AND p_transaction_date BETWEEN NVL(cii.active_start_date, p_transaction_date)
462 AND NVL(cii.active_end_date, p_transaction_date)
463 ;
464
465 -- get additional IB attributes if existing
466 CURSOR l_csi_iea_values_ins_csr(p_instance_id IN NUMBER)
467 IS
468 SELECT attrval.attribute_value_id
469 FROM csi_iea_values attrval,
470 csi_i_extended_attribs attr
471 WHERE attrval.instance_id = p_instance_id
472 AND attrval.attribute_id = attr.attribute_id
473 AND SYSDATE BETWEEN NVL(attrval.active_start_date, SYSDATE) AND NVL(attrval.active_end_date, SYSDATE)
474 AND SYSDATE BETWEEN NVL(attr.active_start_date, SYSDATE) AND NVL(attr.active_end_date, SYSDATE)
475 ;
476
477 BEGIN
478 CSM_UTIL_PKG.LOG('Entering SPAWN_ITEM_INSTANCES_INS for instance_id: ' || p_instance_id,
479 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_INS',FND_LOG.LEVEL_PROCEDURE);
480
481 l_transaction_date := SYSDATE;
482 l_null_relationship_id := TO_NUMBER(NULL);
483
484 FOR l_instance_children_rec IN l_instance_children_csr(p_instance_id, l_transaction_date, p_user_id) LOOP
485
486 IF l_instance_children_rec.relationship_id IS NOT NULL THEN
487 csm_item_instance_event_pkg.ii_relationships_acc_i(l_instance_children_rec.relationship_id, p_user_id, l_error_msg, l_return_status);
488 END IF;
489
490 --insert item instance
491 csm_item_instance_event_pkg.item_instances_acc_processor(l_instance_children_rec.instance_id,
492 p_user_id,
493 NULL, -- p_flowtype
494 l_error_msg,
495 l_return_status);
496
497 -- insert mtl_system_items bug 3949282
498 csm_mtl_system_items_event_pkg.MTL_SYSTEM_ITEMS_ACC_I(l_instance_children_rec.inventory_item_id,
499 p_organization_id,
500 p_user_id,
501 l_error_msg,
502 l_return_status);
503
504 END LOOP;
505
506 -- process inserts of additional attributes
507 FOR r_csi_iea_values_ins_rec IN l_csi_iea_values_ins_csr(p_instance_id) LOOP
508 CSM_ACC_PKG.Insert_Acc
509 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
510 ,P_ACC_TABLE_NAME => g_acc_table_name1
511 ,P_SEQ_NAME => g_acc_sequence_name1
512 ,P_PK1_NAME => g_pk1_name1
513 ,P_PK1_NUM_VALUE => r_csi_iea_values_ins_rec.attribute_value_id
514 ,P_USER_ID => p_user_id
515 );
516 END LOOP;
517
518 CSM_UTIL_PKG.LOG('Leaving SPAWN_ITEM_INSTANCES_INS for instance_id: ' || p_instance_id,
519 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_INS',FND_LOG.LEVEL_PROCEDURE);
520 EXCEPTION
521 WHEN OTHERS THEN
522 l_sqlerrno := to_char(SQLCODE);
523 l_sqlerrmsg := substr(SQLERRM, 1,2000);
524 l_error_msg := l_error_msg || '- Exception in SPAWN_ITEM_INSTANCES_INS for instance_id:'
525 || to_char(p_instance_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
526 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_INS',FND_LOG.LEVEL_EXCEPTION);
527 RAISE;
528 END SPAWN_ITEM_INSTANCES_INS;
529
530 PROCEDURE INCIDENTS_ACC_I(p_incident_id IN NUMBER, p_user_id IN NUMBER)
531 IS
532 l_sqlerrno VARCHAR2(20);
533 l_sqlerrmsg VARCHAR2(4000);
534 l_error_msg VARCHAR2(4000);
535 l_return_status VARCHAR2(2000);
536
537 BEGIN
538 CSM_UTIL_PKG.LOG('Entering INCIDENTS_ACC_I for incident_id: ' || p_incident_id,
539 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
540
541 CSM_ACC_PKG.Insert_Acc
542 ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
543 ,P_ACC_TABLE_NAME => g_incidents_acc_table_name
544 ,P_SEQ_NAME => g_incidents_seq_name
545 ,P_PK1_NAME => g_incidents_pk1_name
546 ,P_PK1_NUM_VALUE => p_incident_id
547 ,P_USER_ID => p_user_id
548 );
549
550 CSM_UTIL_PKG.LOG('Leaving INCIDENTS_ACC_I for incident_id: ' || p_incident_id,
551 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
552 EXCEPTION
553 WHEN OTHERS THEN
554 l_sqlerrno := to_char(SQLCODE);
555 l_sqlerrmsg := substr(SQLERRM, 1,2000);
556 l_error_msg := ' Exception in INCIDENTS_ACC_I for incident_id:'
557 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
558 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
559 RAISE;
560 END INCIDENTS_ACC_I;
561
562 PROCEDURE SPAWN_SR_CONTACT_DEL(p_incident_id IN NUMBER, p_sr_contact_point_id IN NUMBER,
563 p_user_id IN NUMBER, p_flowtype IN VARCHAR2)
564 IS
565 l_sqlerrno VARCHAR2(20);
566 l_sqlerrmsg VARCHAR2(4000);
567 l_error_msg VARCHAR2(4000);
568 l_return_status VARCHAR2(2000);
569 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
570 l_party_id hz_parties.party_id%TYPE;
571
572 CURSOR l_srcontpts_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE,
573 p_sr_contact_point_id NUMBER)
574 IS
575 SELECT sr_contact_point_id,
576 contact_point_id,
577 contact_type,
578 party_id
579 FROM cs_hz_sr_contact_points
580 WHERE incident_id = p_incident_id
581 AND sr_contact_point_id = NVL(p_sr_contact_point_id, sr_contact_point_id);
582
583 CURSOR l_emp_resource_csr (p_party_id hz_parties.party_id%TYPE)
584 IS
585 SELECT jtrs.resource_id
586 FROM jtf_rs_resource_extns jtrs
587 WHERE jtrs.source_id = p_party_id
588 AND jtrs.CATEGORY = 'EMPLOYEE'
589 AND SYSDATE BETWEEN jtrs.start_date_active AND nvl(jtrs.end_date_active, SYSDATE)
590 ;
591
592 BEGIN
593 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL for incident_id: ' || p_incident_id,
594 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL',FND_LOG.LEVEL_PROCEDURE);
595
596 FOR r_srcontpts_rec IN l_srcontpts_csr(p_incident_id, p_sr_contact_point_id) LOOP
597 IF r_srcontpts_rec.contact_type = 'EMPLOYEE' THEN
598 OPEN l_emp_resource_csr(r_srcontpts_rec.party_id);
599 FETCH l_emp_resource_csr INTO l_resource_id;
600 CLOSE l_emp_resource_csr;
601
602 -- delete resource from acc table
603 IF l_resource_id IS NOT NULL THEN
604 csm_resource_extns_event_pkg.resource_extns_acc_d(p_resource_id=>l_resource_id,
605 p_user_id=>p_user_id);
606 END IF;
607 END IF; --Bug 6880063
608 -- delete party record
609 csm_party_event_pkg.party_acc_d(p_party_id=> r_srcontpts_rec.party_id,
610 p_user_id=> p_user_id,
611 p_flowtype=> p_flowtype,
612 p_error_msg=> l_error_msg,
613 x_return_status=> l_return_status);
614
615
616 -- delete sr_contact_point from acc table
617 csm_sr_contact_event_pkg.sr_cntact_mdirty_d(p_sr_contact_point_id=>r_srcontpts_rec.sr_contact_point_id,
618 p_user_id=>p_user_id);
619 END LOOP;
620
621 CSM_UTIL_PKG.LOG('Leaving CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL for incident_id: ' || p_incident_id,
622 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL',FND_LOG.LEVEL_PROCEDURE);
623 EXCEPTION
624 WHEN OTHERS THEN
625 l_sqlerrno := to_char(SQLCODE);
626 l_sqlerrmsg := substr(SQLERRM, 1,2000);
627 l_error_msg := ' Exception in SPAWN_SR_CONTACT_DEL for incident_id:' || to_char(p_incident_id)
628 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
629 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL',FND_LOG.LEVEL_EXCEPTION);
630 RAISE;
631 END SPAWN_SR_CONTACT_DEL;
632
633 PROCEDURE SR_ITEM_DEL_INIT(p_incident_id IN NUMBER, p_instance_id IN NUMBER, p_party_site_id IN NUMBER,
634 p_party_id IN NUMBER, p_location_id IN NUMBER, p_organization_id IN NUMBER,
635 p_user_id IN NUMBER, p_flow_type IN VARCHAR2)
636 IS
637 l_sqlerrno VARCHAR2(20);
638 l_sqlerrmsg VARCHAR2(4000);
639 l_error_msg VARCHAR2(4000);
640 l_return_status VARCHAR2(2000);
641
642 BEGIN
643 CSM_UTIL_PKG.LOG('Entering SR_ITEM_DEL_INIT for incident_id: ' || p_incident_id ||
644 ' and instance_id: ' || p_instance_id,'CSM_SR_EVENT_PKG.SR_ITEM_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
645
646 -- spawn item instances del
647 csm_sr_event_pkg.spawn_item_instances_del(p_instance_id=>p_instance_id,p_organization_id=>p_organization_id,
648 p_user_id=>p_user_id);
649
650 -- delete IB at location (logic not correct)
651 -- csm_item_instance_event_pkg.delete_ib_at_location(p_incident_id => p_incident_id, p_instance_id=>p_instance_id,p_party_site_id=>p_party_site_id,
652 -- p_party_id=>p_party_id,p_location_id=>p_location_id,
653 -- p_user_id=>p_user_id, p_flow_type=>p_flow_type);
654
655 -- spawn counters DEL
656 csm_item_instance_event_pkg.spawn_counters_del(p_instance_id=>p_instance_id, p_user_id=>p_user_id);
657
658 -- delete IB notes
659 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'CP',
660 p_sourceobjectid=>p_instance_id,
661 p_userid=>p_user_id,
662 p_error_msg=>l_error_msg,
663 x_return_status=>l_return_status);
664
665 CSM_UTIL_PKG.LOG('Leaving SR_ITEM_DEL_INIT for incident_id: ' || p_incident_id ||
666 ' and instance_id: ' || p_instance_id,'CSM_SR_EVENT_PKG.SR_ITEM_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
667 EXCEPTION
668 WHEN OTHERS THEN
669 l_sqlerrno := to_char(SQLCODE);
670 l_sqlerrmsg := substr(SQLERRM, 1,2000);
671 l_error_msg := ' Exception in SR_ITEM_DEL_INIT for incident_id: ' || p_incident_id || ' and instance_id:'
672 || to_char(p_instance_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
673 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_ITEM_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
674 RAISE;
675 END SR_ITEM_DEL_INIT;
676
677 PROCEDURE SPAWN_ITEM_INSTANCES_DEL (p_instance_id IN NUMBER, p_organization_id IN NUMBER,
678 p_user_id IN NUMBER)
679 IS
680 l_sqlerrno VARCHAR2(20);
681 l_sqlerrmsg VARCHAR2(4000);
682 l_error_msg VARCHAR2(4000);
683 l_return_status VARCHAR2(2000);
684 l_transaction_date DATE;
685 l_null_relationship_id csi_ii_relationships.relationship_id%TYPE;
686 l_organization_id NUMBER;
687
688 -- get all the child instances of the parent instance as well as the parent
689 CURSOR l_parent_child_instance_csr (p_instance_id csi_item_instances.instance_id%type,
690 p_transaction_date date)
691 IS
692 SELECT cir.relationship_id AS relationship_id ,
693 cir.subject_id AS instance_id ,
694 cii.inventory_item_id
695 FROM (SELECT * FROM CSI_II_RELATIONSHIPS CIRo
696 START WITH CIRo.OBJECT_ID = p_instance_id
697 AND CIRo.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
698 AND p_transaction_date BETWEEN NVL(CIRo.active_start_date, p_transaction_date)
699 AND NVL(CIRo.active_end_date, p_transaction_date)
700 CONNECT BY CIRo.OBJECT_ID = PRIOR CIRo.SUBJECT_ID
701 ) CIR,
702 CSI_ITEM_INSTANCES CII
703 WHERE CII.INSTANCE_ID = CIR.SUBJECT_ID
704 AND p_transaction_date BETWEEN NVL ( CII.ACTIVE_START_DATE , p_transaction_date )
705 AND NVL ( CII.ACTIVE_END_DATE , p_transaction_date)
706 UNION
707 SELECT l_null_relationship_id AS relationship_id,
708 p_instance_id AS instance_id,
709 cii.inventory_item_id
710 FROM csi_item_instances cii
711 WHERE cii.instance_id = p_instance_id
712 AND p_transaction_date BETWEEN NVL(cii.active_start_date, p_transaction_date)
713 AND NVL(cii.active_end_date, p_transaction_date)
714 -- get the parent instance
715 UNION
716 SELECT cir.relationship_id as relationship_id,
717 cir.object_id AS instance_id,
718 cii.inventory_item_id
719 FROM CSI_II_RELATIONSHIPS cir,
720 csi_item_instances cii
721 WHERE cir.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
722 AND cir.SUBJECT_ID = p_instance_id
723 AND cii.instance_id = cir.object_id
724 AND p_transaction_date BETWEEN NVL(cir.active_start_date, p_transaction_date)
725 AND NVL(cir.active_end_date, p_transaction_date)
726 AND p_transaction_date BETWEEN NVL(cii.active_start_date, p_transaction_date)
727 AND NVL(cii.active_end_date, p_transaction_date)
728 ;
729
730 -- get additional IB attributes if existing to delete
731 CURSOR l_csi_iea_values_del_csr(p_instance_id IN NUMBER)
732 IS
733 SELECT attrval.attribute_value_id
734 FROM csi_iea_values attrval,
735 csi_i_extended_attribs attr
736 WHERE attrval.instance_id = p_instance_id
737 AND attrval.attribute_id = attr.attribute_id
738 AND SYSDATE BETWEEN NVL(attrval.active_start_date, SYSDATE) AND NVL(attrval.active_end_date, SYSDATE)
739 AND SYSDATE BETWEEN NVL(attr.active_start_date, SYSDATE) AND NVL(attr.active_end_date, SYSDATE)
740 ;
741
742 BEGIN
743 CSM_UTIL_PKG.LOG('Entering SPAWN_ITEM_INSTANCES_DEL for instance_id: ' || p_instance_id,
744 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_DEL',FND_LOG.LEVEL_PROCEDURE);
745
746 l_transaction_date := SYSDATE;
747 l_null_relationship_id := TO_NUMBER(NULL);
748
749 -- get parent and all child instances
750 FOR l_parent_child_instance_rec IN l_parent_child_instance_csr(p_instance_id, l_transaction_date) LOOP
751
752 IF l_parent_child_instance_rec.relationship_id IS NOT NULL THEN
753 csm_item_instance_event_pkg.ii_relationships_acc_d(l_parent_child_instance_rec.relationship_id, p_user_id, l_error_msg, l_return_status);
754 END IF;
755
756 --delete item instance
757 csm_item_instance_event_pkg.ITEM_INSTANCES_ACC_D(p_instance_id=>l_parent_child_instance_rec.instance_id,
758 p_user_id=>p_user_id,
759 p_error_msg=>l_error_msg,
760 x_return_status=>l_return_status);
761
762 -- delete mtl_system_items bug 3949282
763 csm_mtl_system_items_event_pkg.MTL_SYSTEM_ITEMS_ACC_D(l_parent_child_instance_rec.inventory_item_id,
764 p_organization_id,
765 p_user_id,
766 l_error_msg,
767 l_return_status);
768 END LOOP;
769
770 -- process deletes of additional attributes
771 FOR r_csi_iea_values_del_rec IN l_csi_iea_values_del_csr(p_instance_id) LOOP
772 CSM_ACC_PKG.Delete_acc
773 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
774 ,P_ACC_TABLE_NAME => g_acc_table_name1
775 ,P_PK1_NAME => g_pk1_name1
776 ,P_PK1_NUM_VALUE => r_csi_iea_values_del_rec.attribute_value_id
777 ,P_USER_ID => p_user_id
778 );
779 END LOOP;
780
781 CSM_UTIL_PKG.LOG('Leaving SPAWN_ITEM_INSTANCES_DEL for instance_id: ' || p_instance_id,
782 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_DEL',FND_LOG.LEVEL_PROCEDURE);
783 EXCEPTION
784 WHEN OTHERS THEN
785 l_sqlerrno := to_char(SQLCODE);
786 l_sqlerrmsg := substr(SQLERRM, 1,2000);
787 l_error_msg := l_error_msg || '- Exception in SPAWN_ITEM_INSTANCES_DEL for instance_id:'
788 || to_char(p_instance_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
789 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_DEL',FND_LOG.LEVEL_EXCEPTION);
790 RAISE;
791 END SPAWN_ITEM_INSTANCES_DEL;
792
793 PROCEDURE INCIDENTS_ACC_D(p_incident_id IN NUMBER, p_user_id IN NUMBER)
794 IS
795 l_sqlerrno VARCHAR2(20);
796 l_sqlerrmsg VARCHAR2(4000);
797 l_error_msg VARCHAR2(4000);
798 l_return_status VARCHAR2(2000);
799
800 BEGIN
801 CSM_UTIL_PKG.LOG('Entering INCIDENTS_ACC_D for incident_id: ' || p_incident_id,
802 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
803
804 CSM_ACC_PKG.Delete_Acc
805 ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
806 ,P_ACC_TABLE_NAME => g_incidents_acc_table_name
807 ,P_PK1_NAME => g_incidents_pk1_name
808 ,P_PK1_NUM_VALUE => p_incident_id
809 ,P_USER_ID => p_user_id
810 );
811
812 CSM_UTIL_PKG.LOG('Leaving INCIDENTS_ACC_D for incident_id: ' || p_incident_id,
813 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
814 EXCEPTION
815 WHEN OTHERS THEN
816 l_sqlerrno := to_char(SQLCODE);
817 l_sqlerrmsg := substr(SQLERRM, 1,2000);
818 l_error_msg := ' Exception in INCIDENTS_ACC_D for incident_id:'
819 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
820 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
821 RAISE;
822 END INCIDENTS_ACC_D;
823
824 --12.1XB6
825 FUNCTION IS_SR_DOWNLOADED_TO_OWNER(p_task_id NUMBER) RETURN BOOLEAN
826 IS
827 CURSOR c_sr_owner_group (b_task_id NUMBER) IS
828 SELECT inc.owner_group_id,
829 inc.created_by
830 FROM CS_INCIDENTS_ALL_B inc,
831 JTF_TASKS_B tsk,
832 JTF_TASK_TYPES_B ttype
833 WHERE tsk.TASK_ID=b_task_id
834 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
835 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
836 AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
837 AND ttype.RULE='DISPATCH';
838
839 l_owner_group_id number;
840 l_created_by number ;
841 BEGIN
842 OPEN c_sr_owner_group(p_task_id);
843 FETCH c_sr_owner_group INTO l_owner_group_id,l_created_by;
844 CLOSE c_sr_owner_group;
845
846 RETURN CSM_UTIL_PKG.is_mfs_group(l_owner_group_id) OR CSM_UTIL_PKG.is_palm_user(l_created_by);
847
848 END;
849
850 --12.1
851 PROCEDURE SR_INS_INIT(p_incident_id IN NUMBER)
852 IS
853 l_sqlerrno VARCHAR2(20);
854 l_sqlerrmsg VARCHAR2(4000);
855 l_error_msg VARCHAR2(4000);
856 l_return_status VARCHAR2(2000);
857 l_organization_id NUMBER;
858 l_owner_id NUMBER;
859 l_owner_resource_id NUMBER;
860
861 --Change for Asset
862 CURSOR l_sr_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE)
863 IS
864 SELECT csi.incident_id,
865 csi.customer_id,
866 csi.install_site_id,
867 csi.customer_product_id,
868 csi.inventory_item_id,
869 csi.inv_organization_id,
870 csi.contract_service_id,
871 csi.created_by,
872 csi.incident_location_id,
873 csi.customer_id party_id,
874 decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
875 'HZ_PARTY_SITE',
876 (select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
877 'HZ_LOCATION',
878 (select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
879 ) location_id ,
880 nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
881 csi.owner_group_id
882 FROM cs_incidents_all_b csi
883 WHERE csi.incident_id = p_incident_id
884 AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION');
885 --not required as counter is important
886 /*AND NOT EXISTS
887 (SELECT 1
888 FROM csm_incidents_all_acc acc
889 WHERE acc.incident_id = csi.incident_id
890 AND acc.user_id = CSM_UTIL_PKG.get_group_owner(csi.owner_group_id));*/
891
892
893 CURSOR l_addr_id_csr (p_incident_id IN NUMBER)
894 IS
895 SELECT NVL(LOCATION_ID,ADDRESS_ID)--R12Assest
896 FROM JTF_TASKS_B
897 WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
898 AND SOURCE_OBJECT_ID = p_incident_id;
899
900 l_sr_rec l_sr_csr%ROWTYPE;
901
902 CURSOR c_tasks(b_incident_id NUMBER)
903 IS
904 SELECT TASK_ID
905 FROM JTF_TASKS_B tsk,
906 JTF_TASK_TYPES_B ttype
907 WHERE tsk.SOURCE_OBJECT_TYPE_CODE='SR'
908 AND tsk.SOURCE_OBJECT_ID=b_incident_id
909 AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
910 AND ttype.RULE='DISPATCH';
911
912 CURSOR c_task_assignments(b_task_id NUMBER)
913 IS
914 SELECT TASK_ASSIGNMENT_ID,resource_id
915 FROM JTF_TASK_ASSIGNMENTS
916 WHERE TASK_ID=b_task_id;
917
918 CURSOR c_resource_id(b_user_id NUMBER) IS
919 SELECT RESOURCE_ID
920 FROM JTF_RS_RESOURCE_EXTNS
921 WHERE USER_ID=b_user_id;
922
923 --assignee bug
924 CURSOR c_user_id(b_resource_id NUMBER) IS
925 SELECT USER_ID
926 FROM JTF_RS_RESOURCE_EXTNS
927 WHERE RESOURCE_ID=b_resource_id;
928
929 l_assignee_user_id NUMBER;
930
931 BEGIN
932 CSM_UTIL_PKG.LOG('Entering SR_INS_INIT for incident_id: ' || p_incident_id ,'CSM_SR_EVENT_PKG.SR_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
933
934 OPEN l_sr_csr(p_incident_id);
935 FETCH l_sr_csr INTO l_sr_rec;
936 IF l_sr_csr%NOTFOUND THEN
937 CLOSE l_sr_csr;
938 RETURN;
939 END IF;
940 CLOSE l_sr_csr;
941 --12.1XB6
942 IF ( NOT CSM_UTIL_PKG.is_mfs_group(l_sr_rec.owner_group_id)) THEN
943 IF ( NOT CSM_UTIL_PKG.is_palm_user(l_sr_rec.created_by)) THEN
944 CSM_UTIL_PKG.LOG('Leaving SR_INS_INIT because the Owner group of SR is not a mobile resource group
945 and also this SR , incident_id: ' || p_incident_id ||' is not created by a mobile user'
946 ,'CSM_SR_EVENT_PKG.SR_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
947 RETURN;
948 ELSE
949 l_owner_id := CSM_UTIL_PKG.get_owner(l_sr_rec.created_by);
950 END IF;
951 ELSE
952 l_owner_id := CSM_UTIL_PKG.get_group_owner(l_sr_rec.owner_group_id);
953 END IF;
954
955
956
957 -- not necessary as all DISPATCH Sr's need an install site id
958 IF l_sr_rec.INCIDENT_LOCATION_ID IS NULL THEN
959 OPEN l_addr_id_csr(p_incident_id);
960 FETCH l_addr_id_csr INTO l_sr_rec.INCIDENT_LOCATION_ID;
961 CLOSE l_addr_id_csr;
962 END IF;
963
964
965 -- get Service Inv Validation org
966 l_organization_id := csm_profile_pkg.get_organization_id(l_owner_id);
967
968 --get SR notes
969 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'SR',
970 p_sourceobjectid=>p_incident_id,
971 p_userid=>l_owner_id,
972 p_error_msg=>l_error_msg,
973 x_return_status=>l_return_status);
974
975 -- get SR contacts
976 csm_sr_event_pkg.spawn_sr_contacts_ins(p_incident_id=>p_incident_id,
977 p_user_id=>l_owner_id,
978 p_flowtype=>NULL);
979 IF l_sr_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
980 --insert location for the sr
981 CSM_HZ_LOCATIONS_EVENT_PKG.insert_location(p_location_id => l_sr_rec.incident_location_id,
982 p_user_id => l_owner_id);
983 ELSE
984 -- get party site
985 IF l_sr_rec.incident_location_id IS NOT NULL THEN
986 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_sr_rec.incident_location_id,
987 p_user_id => l_owner_id,
988 p_flowtype => NULL,
989 p_error_msg => l_error_msg,
990 x_return_status => l_return_status);
991 END IF;
992 END IF;
993 -- spawn SR customer ins
994 IF l_sr_rec.customer_id IS NOT NULL THEN
995 csm_party_event_pkg.party_acc_i(p_party_id => l_sr_rec.customer_id,
996 p_user_id => l_owner_id,
997 p_flowtype => NULL,
998 p_error_msg => l_error_msg,
999 x_return_status => l_return_status);
1000 --insert Accounts for the above party-R12
1001 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS
1002 (p_party_id=>l_sr_rec.customer_id
1003 ,p_user_id =>l_owner_id);
1004
1005 END IF;
1006
1007 IF l_sr_rec.customer_product_id IS NOT NULL THEN
1008 -- spawn SR item instance insert
1009 csm_sr_event_pkg.sr_item_ins_init(p_incident_id=>l_sr_rec.incident_id,
1010 p_instance_id=>l_sr_rec.customer_product_id,
1011 p_party_site_id=>l_sr_rec.incident_location_id,
1012 p_party_id=>l_sr_rec.party_id,
1013 p_location_id=>l_sr_rec.location_id,
1014 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1015 p_user_id=>l_owner_id,
1016 p_flow_type=>NULL);
1017
1018 ELSIF l_sr_rec.customer_product_id IS NULL OR l_sr_rec.customer_product_id = 0 THEN
1019 IF l_sr_rec.inventory_item_id IS NOT NULL THEN
1020 csm_mtl_system_items_event_pkg.mtl_system_items_acc_i
1021 (p_inventory_item_id=>l_sr_rec.inventory_item_id,
1022 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1023 p_user_id=>l_owner_id,
1024 p_error_msg=>l_error_msg,
1025 x_return_status=>l_return_status);
1026 END IF;
1027 END IF;
1028
1029 -- insert into incidents acc
1030 csm_sr_event_pkg.incidents_acc_i(p_incident_id=>l_sr_rec.incident_id,
1031 p_user_id=>l_owner_id);
1032
1033 -- get SR contracts
1034 csm_contract_event_pkg.sr_contract_acc_i(p_incident_id=>l_sr_rec.incident_id,
1035 p_user_id=>l_owner_id);
1036
1037 --get contract notes
1038 IF l_sr_rec.contract_service_id IS NOT NULL THEN
1039 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'OKS_COV_NOTE',
1040 p_sourceobjectid=>l_sr_rec.contract_service_id,
1041 p_userid=>l_owner_id,
1042 p_error_msg=>l_error_msg,
1043 x_return_status=>l_return_status);
1044 END IF;
1045
1046 --DOWNLOAD TASK/TASK_ASSIGNMENTS TO SR OWNER
1047 FOR task_rec IN c_tasks(p_incident_id)
1048 LOOP
1049 --dwnld notes to owner if required
1050 /* csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'TASK',
1051 p_sourceobjectid=>task_rec.task_id,
1052 p_userid=>l_owner_id,
1053 p_error_msg=>l_error_msg,
1054 x_return_status=>l_return_status);*/
1055
1056 csm_task_event_pkg.acc_insert(p_task_id=>task_rec.task_id,p_user_id=>l_owner_id);
1057
1058 for assign_rec in c_task_assignments(task_rec.task_id)
1059 loop
1060 csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
1061
1062 -- to download other grp's resource if required
1063 OPEN c_resource_id(l_owner_id);
1064 FETCH c_resource_id INTO l_owner_resource_id;
1065 CLOSE c_resource_id;
1066 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,assign_rec.resource_id) THEN
1067 --assignee bug
1068 OPEN c_user_id(assign_rec.resource_id);
1069 FETCH c_user_id INTO l_assignee_user_id;
1070 CLOSE c_user_id;
1071 IF l_assignee_user_id IS NOT NULL THEN
1072 CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_owner_id);
1073 END IF;
1074 END IF;
1075 --to decide if LOBS shd be downloaded for task assignment and also service history
1076 end loop;
1077
1078 END LOOP;
1079
1080 CSM_UTIL_PKG.LOG('Leaving SR_INS_INIT for incident_id: ' || p_incident_id ,'CSM_SR_EVENT_PKG.SR_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
1081 EXCEPTION
1082 WHEN OTHERS THEN
1083 l_sqlerrno := to_char(SQLCODE);
1084 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1085 l_error_msg := ' Exception in SR_INS_INIT for incident_id:'
1086 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1087 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
1088 RAISE;
1089 END SR_INS_INIT;
1090
1091 --12.1
1092 PROCEDURE SR_DEL_INIT(p_incident_id IN NUMBER,p_user_id IN NUMBER)
1093 IS
1094 l_sqlerrno VARCHAR2(20);
1095 l_sqlerrmsg VARCHAR2(4000);
1096 l_error_msg VARCHAR2(4000);
1097 l_return_status VARCHAR2(2000);
1098 l_organization_id NUMBER;
1099 l_owner_id NUMBER;
1100 l_owner_resource_id NUMBER;
1101
1102 --12.1XB6
1103 CURSOR l_sr_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE,b_user_id NUMBER)
1104 IS
1105 SELECT csi.incident_id,
1106 csi.customer_id,
1107 csi.install_site_id,
1108 csi.customer_product_id,
1109 csi.inventory_item_id,
1110 csi.inv_organization_id,
1111 csi.contract_service_id,
1112 csi.created_by,
1113 csi.incident_location_id,
1114 csi.customer_id party_id,
1115 decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
1116 'HZ_PARTY_SITE',
1117 (select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
1118 'HZ_LOCATION',
1119 (select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
1120 ) location_id ,
1121 nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
1122 csi.owner_group_id
1123 FROM cs_incidents_all_b csi
1124 WHERE csi.incident_id = p_incident_id
1125 AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION')
1126 AND EXISTS
1127 (SELECT 1
1128 FROM csm_incidents_all_acc acc
1129 WHERE acc.incident_id = csi.incident_id
1130 AND acc.user_id = NVL(b_user_id,decode(CSM_UTIL_PKG.get_group_owner(csi.owner_group_id),-1,csi.created_by,CSM_UTIL_PKG.get_group_owner(csi.owner_group_id)) ));
1131
1132 l_sr_rec l_sr_csr%ROWTYPE;
1133
1134 CURSOR c_tasks(b_incident_id NUMBER)
1135 IS
1136 SELECT tsk.TASK_ID
1137 FROM JTF_TASKS_B tsk,
1138 JTF_TASK_TYPES_B ttype
1139 WHERE tsk.SOURCE_OBJECT_TYPE_CODE='SR'
1140 AND tsk.SOURCE_OBJECT_ID=b_incident_id
1141 AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
1142 AND ttype.RULE='DISPATCH';
1143
1144 CURSOR c_task_assignments(b_task_id NUMBER)
1145 IS
1146 SELECT TASK_ASSIGNMENT_ID,resource_id
1147 FROM JTF_TASK_ASSIGNMENTS
1148 WHERE TASK_ID=b_task_id;
1149
1150 CURSOR c_resource_id(b_user_id NUMBER) IS
1151 SELECT RESOURCE_ID
1152 FROM JTF_RS_RESOURCE_EXTNS
1153 WHERE USER_ID=b_user_id;
1154
1155 --assignee bug
1156 CURSOR c_user_id(b_resource_id NUMBER) IS
1157 SELECT USER_ID
1158 FROM JTF_RS_RESOURCE_EXTNS
1159 WHERE RESOURCE_ID=b_resource_id;
1160
1161 l_assignee_user_id NUMBER;
1162
1163
1164 BEGIN
1165 CSM_UTIL_PKG.LOG('Entering SR_DEL_INIT for incident_id: ' || p_incident_id ,'CSM_SR_EVENT_PKG.SR_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
1166
1167 OPEN l_sr_csr(p_incident_id,p_user_id);
1168 FETCH l_sr_csr INTO l_sr_rec;
1169 IF l_sr_csr%NOTFOUND THEN
1170 CLOSE l_sr_csr;
1171 RETURN;
1172 END IF;
1173 CLOSE l_sr_csr;
1174
1175 --12.1XB6
1176 IF p_user_id IS NULL THEN
1177 IF ( NOT CSM_UTIL_PKG.is_mfs_group(l_sr_rec.owner_group_id)) THEN
1178 IF ( NOT CSM_UTIL_PKG.is_palm_user(l_sr_rec.created_by)) THEN
1179 CSM_UTIL_PKG.LOG('Leaving SR_DEL_INIT since the Owner group of SR is not a mobile resource group
1180 and also this SR , incident_id: ' || p_incident_id ||' is not created by a mobile user'
1181 ,'CSM_SR_EVENT_PKG.SR_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
1182 RETURN;
1183 ELSE
1184 l_owner_id := CSM_UTIL_PKG.get_owner(l_sr_rec.created_by);
1185 END IF;
1186 ELSE
1187 l_owner_id := CSM_UTIL_PKG.get_group_owner(l_sr_rec.owner_group_id);
1188 END IF;
1189 ELSE
1190 l_owner_id:=p_user_id;
1191 END IF;
1192 -- get Service Inv Validation org
1193 l_organization_id := csm_profile_pkg.get_organization_id(l_owner_id);
1194
1195 -- delete from incidents acc
1196 csm_sr_event_pkg.incidents_acc_d(p_incident_id=>l_sr_rec.incident_id,
1197 p_user_id=>l_owner_id);
1198
1199 -- delete SR notes
1200 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'SR',
1201 p_sourceobjectid=>l_sr_rec.incident_id,
1202 p_userid=>l_owner_id,
1203 p_error_msg=>l_error_msg,
1204 x_return_status=>l_return_status);
1205
1206 -- delete SR contacts
1207 csm_sr_event_pkg.spawn_sr_contact_del(p_incident_id=>l_sr_rec.incident_id,
1208 p_user_id=>l_owner_id,
1209 p_flowtype=>NULL);
1210 IF l_sr_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
1211 --insert location for the sr
1212 csm_hz_locations_event_pkg.delete_location(p_location_id => l_sr_rec.incident_location_id,
1213 p_user_id => l_owner_id);
1214 ELSE
1215 -- spawn party site del
1216 IF l_sr_rec.incident_location_id IS NOT NULL THEN
1217 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => l_sr_rec.incident_location_id,
1218 p_user_id => l_owner_id,
1219 p_flowtype => NULL,
1220 p_error_msg => l_error_msg,
1221 x_return_status => l_return_status);
1222 END IF;
1223 END IF;
1224 -- spawn SR customer del
1225 IF l_sr_rec.customer_id IS NOT NULL THEN
1226 csm_party_event_pkg.party_acc_d(p_party_id => l_sr_rec.customer_id,
1227 p_user_id => l_owner_id,
1228 p_flowtype => NULL,
1229 p_error_msg => l_error_msg,
1230 x_return_status => l_return_status);
1231 --Delete Accounts for the above party-R12
1232 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL
1233 (p_party_id=>l_sr_rec.customer_id
1234 ,p_user_id =>l_owner_id);
1235
1236 END IF;
1237
1238 -- delete SR contract
1239 IF l_sr_rec.contract_service_id IS NOT NULL THEN
1240 csm_contract_event_pkg.sr_contract_acc_d(p_incident_id=>l_sr_rec.inventory_item_id,
1241 p_user_id=>l_owner_id);
1242 END IF;
1243
1244 -- delete contract notes
1245 IF l_sr_rec.contract_service_id IS NOT NULL THEN
1246 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'OKS_COV_NOTE',
1247 p_sourceobjectid=>l_sr_rec.contract_service_id,
1248 p_userid=>l_owner_id,
1249 p_error_msg=>l_error_msg,
1250 x_return_status=>l_return_status);
1251 END IF;
1252
1253 IF l_sr_rec.customer_product_id IS NOT NULL THEN
1254 -- spawn SR item instance delete
1255 csm_sr_event_pkg.sr_item_del_init(p_incident_id=>l_sr_rec.incident_id,
1256 p_instance_id=>l_sr_rec.customer_product_id,
1257 p_party_site_id=>l_sr_rec.incident_location_id,
1258 p_party_id=>l_sr_rec.party_id,
1259 p_location_id=>l_sr_rec.location_id,
1260 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1261 p_user_id=>l_owner_id,
1262 p_flow_type=>NULL);
1263
1264 ELSIF l_sr_rec.customer_product_id IS NULL OR l_sr_rec.customer_product_id = 0 THEN
1265 IF l_sr_rec.inventory_item_id IS NOT NULL THEN
1266 csm_mtl_system_items_event_pkg.mtl_system_items_acc_d
1267 (p_inventory_item_id=>l_sr_rec.inventory_item_id,
1268 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1269 p_user_id=>l_owner_id,
1270 p_error_msg=>l_error_msg,
1271 x_return_status=>l_return_status);
1272 END IF;
1273 END IF;
1274
1275 --DELETE TASK/TASK_ASSIGNMENTS DOWNLOADED TO SR OWNER
1276 FOR task_rec IN c_tasks(p_incident_id)
1277 LOOP
1278 -- delete task notes if inserted
1279 /* csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'TASK',
1280 p_sourceobjectid=>task_rec.task_id,
1281 p_userid=>l_owner_id,
1282 p_error_msg=>l_error_msg,
1283 x_return_status=>l_return_status);*/
1284
1285 csm_task_event_pkg.acc_delete(p_task_id=>task_rec.task_id,p_user_id=>l_owner_id);
1286
1287 for assign_rec in c_task_assignments(task_rec.task_id)
1288 loop
1289
1290 csm_task_assignment_event_pkg.acc_delete(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
1291
1292 /*Other grp's member Resource to be deleted from acc if assigned to him*/
1293 OPEN c_resource_id(l_owner_id);
1294 FETCH c_resource_id INTO l_owner_resource_id;
1295 CLOSE c_resource_id;
1296 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,assign_rec.resource_id) THEN
1297 --assignee bug
1298 OPEN c_user_id(assign_rec.resource_id);
1299 FETCH c_user_id INTO l_assignee_user_id;
1300 CLOSE c_user_id;
1301 IF l_assignee_user_id IS NOT NULL THEN
1302 CSM_USER_EVENT_PKG.DELETE_ACC(l_assignee_user_id,l_owner_id);
1303 END IF;
1304 END IF;
1305 end loop;
1306
1307 END LOOP;
1308
1309 CSM_UTIL_PKG.LOG('Leaving SR_DEL_INIT for incident_id: ' || p_incident_id ,'CSM_SR_EVENT_PKG.SR_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
1310 EXCEPTION
1311 WHEN OTHERS THEN
1312 l_sqlerrno := to_char(SQLCODE);
1313 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1314 l_error_msg := ' Exception in SR_DEL_INIT for incident_id:'
1315 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1316 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
1317 RAISE;
1318 END SR_DEL_INIT;
1319
1320 --12.1
1321 PROCEDURE SR_UPD_INIT(p_incident_id IN NUMBER, p_is_install_site_updated IN VARCHAR2,
1322 p_old_install_site_id IN NUMBER,
1323 p_is_incident_location_updated IN VARCHAR2,
1324 p_old_incident_location_id IN NUMBER, p_is_sr_customer_updated IN VARCHAR2,
1325 p_old_sr_customer_id IN NUMBER, p_is_sr_instance_updated IN VARCHAR2,
1326 p_old_instance_id IN NUMBER, p_is_inventory_item_updated IN VARCHAR2,
1327 p_old_inventory_item_id IN NUMBER, p_old_organization_id IN NUMBER,
1328 p_old_party_id IN NUMBER, p_old_location_id IN NUMBER,
1329 p_is_contr_service_id_updated IN VARCHAR2, p_old_contr_service_id IN NUMBER)
1330 IS
1331 l_sqlerrno VARCHAR2(20);
1332 l_sqlerrmsg VARCHAR2(4000);
1333 l_error_msg VARCHAR2(4000);
1334 l_return_status VARCHAR2(2000);
1335 l_organization_id NUMBER;
1336
1337 CURSOR l_sr_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE)
1338 IS
1339 SELECT csi.incident_id,
1340 csi.customer_id,
1341 csi.install_site_id,
1342 csi.customer_product_id,
1343 csi.inventory_item_id,
1344 csi.inv_organization_id,
1345 csi.contract_service_id,
1346 csi.created_by,
1347 csi.incident_location_id,
1348 csi.customer_id party_id,
1349 decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
1350 'HZ_PARTY_SITE',
1351 (select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
1352 'HZ_LOCATION',
1353 (select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
1354 ) location_id ,
1355 nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
1356 csi.owner_group_id
1357 FROM cs_incidents_all_b csi
1358 WHERE csi.incident_id = p_incident_id
1359 AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION');
1360
1361
1362 CURSOR l_addr_id_csr (p_incident_id IN NUMBER)
1363 IS
1364 SELECT NVL(LOCATION_ID,ADDRESS_ID)--R12Assest
1365 FROM JTF_TASKS_B
1366 WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
1367 AND SOURCE_OBJECT_ID = p_incident_id;
1368
1369 l_sr_rec l_sr_csr%ROWTYPE;
1370
1371 CURSOR l_csm_task_assg_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE)
1372 IS
1373 SELECT acc.access_id, acc.user_id
1374 FROM csm_incidents_all_acc acc
1375 WHERE acc.incident_id = p_incident_id;
1376
1377
1378 BEGIN
1379 CSM_UTIL_PKG.LOG('Entering SR_UPD_INIT for incident_id: ' || p_incident_id ,'CSM_SR_EVENT_PKG.SR_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
1380
1381 OPEN l_sr_csr(p_incident_id);
1382 FETCH l_sr_csr INTO l_sr_rec;
1383 IF l_sr_csr%NOTFOUND THEN
1384 CLOSE l_sr_csr;
1385 RETURN;
1386 END IF;
1387 CLOSE l_sr_csr;
1388
1389 -- not necessary as all DISPATCH Sr's need an install site id
1390 IF l_sr_rec.incident_location_id IS NULL THEN
1391 OPEN l_addr_id_csr(p_incident_id);
1392 FETCH l_addr_id_csr INTO l_sr_rec.incident_location_id;
1393 CLOSE l_addr_id_csr;
1394 END IF;
1395
1396 -- get all users having access for this SR
1397 FOR r_csm_task_assg_rec IN l_csm_task_assg_csr(p_incident_id) LOOP
1398 l_organization_id := csm_profile_pkg.get_organization_id(r_csm_task_assg_rec.user_id);
1399
1400 IF p_is_incident_location_updated = 'Y' THEN
1401 IF l_sr_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
1402 --Delete old location
1403 IF p_old_incident_location_id IS NOT NULL THEN
1404
1405 csm_hz_locations_event_pkg.delete_location(p_location_id => l_sr_rec.incident_location_id,
1406 p_user_id => r_csm_task_assg_rec.user_id);
1407 END IF;
1408
1409 IF l_sr_rec.incident_location_id IS NOT NULL THEN
1410 -- insert new location
1411 csm_hz_locations_event_pkg.insert_location(p_location_id => l_sr_rec.incident_location_id,
1412 p_user_id => r_csm_task_assg_rec.user_id);
1413 END IF;
1414
1415 ELSE
1416
1417 IF p_old_incident_location_id IS NOT NULL THEN
1418 -- spawn party site del process
1419 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => p_old_incident_location_id,
1420 p_user_id => r_csm_task_assg_rec.user_id,
1421 p_flowtype => NULL,
1422 p_error_msg => l_error_msg,
1423 x_return_status => l_return_status);
1424 END IF;
1425
1426 IF l_sr_rec.incident_location_id IS NOT NULL THEN
1427 -- spawn party site ins process
1428 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_sr_rec.incident_location_id,
1429 p_user_id => r_csm_task_assg_rec.user_id,
1430 p_flowtype => NULL,
1431 p_error_msg => l_error_msg,
1432 x_return_status => l_return_status);
1433 END IF;
1434 END IF;
1435 END IF;
1436
1437 IF p_is_sr_customer_updated = 'Y' THEN
1438 IF p_old_sr_customer_id IS NOT NULL THEN
1439 -- spawn party del process
1440 csm_party_event_pkg.party_acc_d(p_party_id=>p_old_sr_customer_id,
1441 p_user_id=>r_csm_task_assg_rec.user_id,
1442 p_flowtype=>NULL,
1443 p_error_msg=>l_error_msg,
1444 x_return_status=>l_return_status);
1445 END IF;
1446
1447 IF l_sr_rec.customer_id IS NOT NULL THEN
1448 -- spawn party site ins process
1449 csm_party_event_pkg.party_acc_i(p_party_id=>l_sr_rec.customer_id,
1450 p_user_id=>r_csm_task_assg_rec.user_id,
1451 p_flowtype=>NULL,
1452 p_error_msg=>l_error_msg,
1453 x_return_status=>l_return_status);
1454 END IF;
1455 END IF;
1456
1457 IF p_is_sr_instance_updated = 'Y' THEN
1458 IF p_old_instance_id IS NOT NULL THEN
1459 -- spawn SR item instance delete
1460 csm_sr_event_pkg.sr_item_del_init(p_incident_id=>l_sr_rec.incident_id,
1461 p_instance_id=>p_old_instance_id,
1462 p_party_site_id=>p_old_incident_location_id,
1463 p_party_id=>p_old_party_id,
1464 p_location_id=>p_old_location_id,
1465 p_organization_id=>NVL(p_old_organization_id, l_organization_id),
1466 p_user_id=>r_csm_task_assg_rec.user_id,
1467 p_flow_type=>NULL);
1468 END IF;
1469
1470 IF l_sr_rec.customer_product_id IS NOT NULL THEN
1471 -- spawn SR item instance insert
1472 csm_sr_event_pkg.sr_item_ins_init(p_incident_id=>l_sr_rec.incident_id,
1473 p_instance_id=>l_sr_rec.customer_product_id,
1474 p_party_site_id=>l_sr_rec.incident_location_id,
1475 p_party_id=>l_sr_rec.party_id,
1476 p_location_id=>l_sr_rec.location_id,
1477 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1478 p_user_id=>r_csm_task_assg_rec.user_id,
1479 p_flow_type=>NULL);
1480 END IF;
1481 END IF;
1482
1483 IF p_is_inventory_item_updated = 'Y' THEN
1484 IF p_old_inventory_item_id IS NOT NULL THEN
1485 csm_mtl_system_items_event_pkg.mtl_system_items_acc_d
1486 (p_inventory_item_id=>p_old_inventory_item_id,
1487 p_organization_id=>NVL(p_old_organization_id, l_organization_id),
1488 p_user_id=>r_csm_task_assg_rec.user_id,
1489 p_error_msg=>l_error_msg,
1490 x_return_status=>l_return_status);
1491 END IF;
1492
1493 IF l_sr_rec.inventory_item_id IS NOT NULL THEN
1494 csm_mtl_system_items_event_pkg.mtl_system_items_acc_i
1495 (p_inventory_item_id=>l_sr_rec.inventory_item_id,
1496 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1497 p_user_id=>r_csm_task_assg_rec.user_id,
1498 p_error_msg=>l_error_msg,
1499 x_return_status=>l_return_status);
1500 END IF;
1501 END IF;
1502
1503 IF p_is_contr_service_id_updated = 'Y' THEN
1504 csm_contract_event_pkg.sr_contract_acc_u(p_incident_id=>l_sr_rec.incident_id,
1505 p_old_contract_service_id=>p_old_contr_service_id,
1506 p_contract_service_id=>l_sr_rec.contract_service_id,
1507 p_user_id=>r_csm_task_assg_rec.user_id);
1508 END IF;
1509
1510 -- incidents make dirty for update
1511 CSM_ACC_PKG.Update_Acc
1512 ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
1513 ,P_ACC_TABLE_NAME => g_incidents_acc_table_name
1514 ,P_USER_ID => r_csm_task_assg_rec.user_id
1515 ,P_ACCESS_ID => r_csm_task_assg_rec.access_id
1516 );
1517
1518 END LOOP;
1519
1520 CSM_UTIL_PKG.LOG('Leaving SR_UPD_INIT for incident_id: ' || p_incident_id ,'CSM_SR_EVENT_PKG.SR_UPD_INIT',FND_LOG.LEVEL_PROCEDURE);
1521 EXCEPTION
1522 WHEN OTHERS THEN
1523 l_sqlerrno := to_char(SQLCODE);
1524 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1525 l_error_msg := ' Exception in SR_UPD_INIT for incident_id:'
1526 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1527 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_UPD_INIT',FND_LOG.LEVEL_EXCEPTION);
1528 RAISE;
1529 END SR_UPD_INIT;
1530
1531 --Function to find whether the SR associated with Task assignment id is open before purging.
1532 FUNCTION IS_SR_OPEN ( p_task_id IN NUMBER)
1533 RETURN BOOLEAN
1534 IS
1535 l_sqlerrno VARCHAR2(20);
1536 l_sqlerrmsg VARCHAR2(4000);
1537 l_error_msg VARCHAR2(4000);
1538 l_incident_id cs_incidents_all_b.incident_id%TYPE;
1539
1540 CURSOR l_is_sr_open_csr(c_task_id jtf_tasks_b.task_id%TYPE)
1541 IS
1542 SELECT inc.incident_id
1543 FROM cs_incidents_all_b inc,
1544 cs_incident_statuses_b ists,
1545 jtf_tasks_b tsk
1546 WHERE inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
1547 AND ists.CLOSE_FLAG = 'Y'
1548 AND tsk.task_id = c_task_id
1549 AND tsk.source_object_id = inc.incident_id;
1550
1551 BEGIN
1552 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.IS_SR_OPEN for task_id: ' || p_task_id,
1553 'CSM_SR_EVENT_PKG.IS_SR_OPEN',FND_LOG.LEVEL_PROCEDURE);
1554
1555 l_incident_id := NULL;
1556
1557 OPEN l_is_sr_open_csr(p_task_id);
1558 FETCH l_is_sr_open_csr INTO l_incident_id;
1559 IF l_is_sr_open_csr%NOTFOUND THEN
1560
1561 CLOSE l_is_sr_open_csr;
1562 CSM_UTIL_PKG.LOG('The SR is open for task_id : ' || p_task_id,
1563 'CSM_SR_EVENT_PKG.IS_SR_OPEN',FND_LOG.LEVEL_EXCEPTION);
1564 RETURN TRUE;
1565 END IF;
1566 CLOSE l_is_sr_open_csr;
1567
1568 CSM_UTIL_PKG.LOG('The SR is Closed for task_id : ' || p_task_id,
1569 'CSM_SR_EVENT_PKG.IS_SR_OPEN',FND_LOG.LEVEL_EXCEPTION);
1570
1571 RETURN FALSE;
1572
1573 EXCEPTION
1574 WHEN OTHERS THEN
1575 l_sqlerrno := to_char(SQLCODE);
1576 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1577 l_error_msg := ' Exception in IS_SR_OPEN for task_id:' || to_char(p_task_id)
1578 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1579 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.IS_SR_OPEN',FND_LOG.LEVEL_EXCEPTION);
1580 RAISE;
1581 END IS_SR_OPEN;
1582
1583
1584 END CSM_SR_EVENT_PKG;