[Home] [Help]
PACKAGE BODY: APPS.CSM_SR_EVENT_PKG
Source
1 PACKAGE BODY CSM_SR_EVENT_PKG AS
2 /* $Header: csmesrb.pls 120.12.12020000.2 2013/04/09 10:59:41 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
379 -- initialize count of IB items at location to 0;
380 csm_sr_event_pkg.g_ib_count := 0;
381
382 -- item instances fork
383 csm_sr_event_pkg.spawn_item_instances_ins(p_instance_id=>p_instance_id,p_organization_id=>p_organization_id,
384 p_user_id=>p_user_id);
385
386 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(p_user_id ) THEN
387 -- get IB at location
388 csm_item_instance_event_pkg.get_ib_at_location(p_instance_id=>p_instance_id,p_party_site_id=>p_party_site_id,
389 p_party_id=>p_party_id,p_location_id=>p_location_id,
390 p_user_id=>p_user_id, p_flow_type=>p_flow_type);
391
392 -- spawn counters INS
393 csm_item_instance_event_pkg.spawn_counters_ins(p_instance_id=>p_instance_id, p_user_id=>p_user_id);
394
395 -- get IB notes
396 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'CP',
397 p_sourceobjectid=>p_instance_id,
398 p_userid=>p_user_id,
399 p_error_msg=>l_error_msg,
400 x_return_status=>l_return_status);
401
402 END IF;
403
404 CSM_UTIL_PKG.LOG('Leaving SR_ITEM_INS_INIT for incident_id: ' || p_incident_id ||
405 ' and instance_id: ' || p_instance_id,'CSM_SR_EVENT_PKG.SR_ITEM_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
406 EXCEPTION
407 WHEN OTHERS THEN
408 l_sqlerrno := to_char(SQLCODE);
409 l_sqlerrmsg := substr(SQLERRM, 1,2000);
410 l_error_msg := ' Exception in SR_ITEM_INS_INIT for incident_id: ' || p_incident_id || ' and instance_id:'
411 || to_char(p_instance_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
412 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_ITEM_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
413 RAISE;
414 END SR_ITEM_INS_INIT;
415
416 PROCEDURE SPAWN_ITEM_INSTANCES_INS (p_instance_id IN NUMBER, p_organization_id IN NUMBER, p_user_id IN NUMBER)
417 IS
418 l_sqlerrno VARCHAR2(20);
419 l_sqlerrmsg VARCHAR2(4000);
420 l_error_msg VARCHAR2(4000);
421 l_return_status VARCHAR2(2000);
422 l_transaction_date DATE;
423 l_null_relationship_id csi_ii_relationships.relationship_id%TYPE;
424 l_organization_id NUMBER;
425
426 -- get all the child intances of the parent and the parent instance
427 -- also check these instances do not exist for the user
428 CURSOR l_instance_children_csr (p_transaction_date DATE,b_htm5 VARCHAR2)
429 IS
430 SELECT cir.relationship_id AS relationship_id ,
431 cir.subject_id AS instance_id ,
432 cii.inventory_item_id
433 FROM (SELECT * FROM CSI_II_RELATIONSHIPS CIRo
434 WHERE CIRo.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
435 AND p_transaction_date BETWEEN NVL(CIRo.active_start_date, p_transaction_date) AND NVL(CIRo.active_end_date, p_transaction_date)
436 AND (b_htm5='N' OR LEVEL <=2) --for htm5 functionality
437 START WITH CIRo.OBJECT_ID = p_instance_id
438 CONNECT BY NOCYCLE CIRo.OBJECT_ID = PRIOR CIRo.SUBJECT_ID
439 ) CIR,
440 CSI_ITEM_INSTANCES CII
441 WHERE CII.INSTANCE_ID = CIR.SUBJECT_ID
442 AND p_transaction_date BETWEEN NVL ( CII.ACTIVE_START_DATE , p_transaction_date )
443 AND NVL ( CII.ACTIVE_END_DATE , p_transaction_date)
444 UNION
445 -- select the current instance
446 SELECT l_null_relationship_id AS relationship_id,
447 cii.instance_id AS instance_id,
448 cii.inventory_item_id
449 FROM csi_item_instances cii
450 WHERE cii.instance_id = p_instance_id
451 AND p_transaction_date BETWEEN NVL(cii.active_start_date, p_transaction_date)
452 AND NVL(cii.active_end_date, p_transaction_date)
453 UNION
454 SELECT cir.relationship_id AS relationship_id,
455 cir.object_id AS instance_id,
456 cii.inventory_item_id
457 FROM CSI_II_RELATIONSHIPS cir,
458 csi_item_instances cii
459 WHERE cir.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
460 AND cir.SUBJECT_ID = p_instance_id
461 AND cii.instance_id = cir.object_id
462 AND p_transaction_date BETWEEN NVL(cir.active_start_date, p_transaction_date)
463 AND NVL(cir.active_end_date, p_transaction_date)
464 AND p_transaction_date BETWEEN NVL(cii.active_start_date, p_transaction_date)
465 AND NVL(cii.active_end_date, p_transaction_date)
466 ;
467
468 -- get additional IB attributes if existing
469 CURSOR l_csi_iea_values_ins_csr(p_instance_id IN NUMBER)
470 IS
471 SELECT attrval.attribute_value_id
472 FROM csi_iea_values attrval,
473 csi_i_extended_attribs attr
474 WHERE attrval.instance_id = p_instance_id
475 AND attrval.attribute_id = attr.attribute_id
476 AND SYSDATE BETWEEN NVL(attrval.active_start_date, SYSDATE) AND NVL(attrval.active_end_date, SYSDATE)
477 AND SYSDATE BETWEEN NVL(attr.active_start_date, SYSDATE) AND NVL(attr.active_end_date, SYSDATE)
478 ;
479
480 l_htm5_user BOOLEAN:=CSM_UTIL_PKG.IS_HTML5_USER(p_user_id );
481
482 BEGIN
483 CSM_UTIL_PKG.LOG('Entering SPAWN_ITEM_INSTANCES_INS for instance_id: ' || p_instance_id,
484 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_INS',FND_LOG.LEVEL_PROCEDURE);
485
486 l_transaction_date := SYSDATE;
487 l_null_relationship_id := TO_NUMBER(NULL);
488
489 FOR l_instance_children_rec IN l_instance_children_csr(l_transaction_date,csm_util_pkg.bool2yn(l_htm5_user)) LOOP
490
491 IF l_instance_children_rec.relationship_id IS NOT NULL THEN
492 csm_item_instance_event_pkg.ii_relationships_acc_i(l_instance_children_rec.relationship_id, p_user_id, l_error_msg, l_return_status);
493 END IF;
494
495 --insert item instance
496 csm_item_instance_event_pkg.item_instances_acc_processor(l_instance_children_rec.instance_id,
497 p_user_id,
498 NULL, -- p_flowtype
499 l_error_msg,
500 l_return_status);
501
502 -- insert mtl_system_items bug 3949282
503 csm_mtl_system_items_event_pkg.MTL_SYSTEM_ITEMS_ACC_I(l_instance_children_rec.inventory_item_id,
504 p_organization_id,
505 p_user_id,
506 l_error_msg,
507 l_return_status);
508
509 END LOOP;
510
511
512 IF NOT l_htm5_user THEN
513 -- process inserts of additional attributes
514 FOR r_csi_iea_values_ins_rec IN l_csi_iea_values_ins_csr(p_instance_id) LOOP
515 CSM_ACC_PKG.Insert_Acc
516 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
517 ,P_ACC_TABLE_NAME => g_acc_table_name1
518 ,P_SEQ_NAME => g_acc_sequence_name1
519 ,P_PK1_NAME => g_pk1_name1
520 ,P_PK1_NUM_VALUE => r_csi_iea_values_ins_rec.attribute_value_id
521 ,P_USER_ID => p_user_id
522 );
523 END LOOP;
524 END IF;
525
526 CSM_UTIL_PKG.LOG('Leaving SPAWN_ITEM_INSTANCES_INS for instance_id: ' || p_instance_id,
527 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_INS',FND_LOG.LEVEL_PROCEDURE);
528 EXCEPTION
529 WHEN OTHERS THEN
530 l_sqlerrno := to_char(SQLCODE);
531 l_sqlerrmsg := substr(SQLERRM, 1,2000);
532 l_error_msg := l_error_msg || '- Exception in SPAWN_ITEM_INSTANCES_INS for instance_id:'
533 || to_char(p_instance_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
534 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_INS',FND_LOG.LEVEL_EXCEPTION);
535 RAISE;
536 END SPAWN_ITEM_INSTANCES_INS;
537
538 PROCEDURE INCIDENTS_ACC_I(p_incident_id IN NUMBER, p_user_id IN NUMBER)
539 IS
540 l_sqlerrno VARCHAR2(20);
541 l_sqlerrmsg VARCHAR2(4000);
542 l_error_msg VARCHAR2(4000);
543 l_return_status VARCHAR2(2000);
544
545 BEGIN
546 CSM_UTIL_PKG.LOG('Entering INCIDENTS_ACC_I for incident_id: ' || p_incident_id,
547 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
548
549 CSM_ACC_PKG.Insert_Acc
550 ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
551 ,P_ACC_TABLE_NAME => g_incidents_acc_table_name
552 ,P_SEQ_NAME => g_incidents_seq_name
553 ,P_PK1_NAME => g_incidents_pk1_name
554 ,P_PK1_NUM_VALUE => p_incident_id
555 ,P_USER_ID => p_user_id
556 );
557
558 CSM_UTIL_PKG.LOG('Leaving INCIDENTS_ACC_I for incident_id: ' || p_incident_id,
559 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_I',FND_LOG.LEVEL_PROCEDURE);
560 EXCEPTION
561 WHEN OTHERS THEN
562 l_sqlerrno := to_char(SQLCODE);
563 l_sqlerrmsg := substr(SQLERRM, 1,2000);
564 l_error_msg := ' Exception in INCIDENTS_ACC_I for incident_id:'
565 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
566 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_I',FND_LOG.LEVEL_EXCEPTION);
567 RAISE;
568 END INCIDENTS_ACC_I;
569
570 PROCEDURE SPAWN_SR_CONTACT_DEL(p_incident_id IN NUMBER, p_sr_contact_point_id IN NUMBER,
571 p_user_id IN NUMBER, p_flowtype IN VARCHAR2)
572 IS
573 l_sqlerrno VARCHAR2(20);
574 l_sqlerrmsg VARCHAR2(4000);
575 l_error_msg VARCHAR2(4000);
576 l_return_status VARCHAR2(2000);
577 l_resource_id jtf_rs_resource_extns.resource_id%TYPE;
578 l_party_id hz_parties.party_id%TYPE;
579
580 CURSOR l_srcontpts_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE,
581 p_sr_contact_point_id NUMBER)
582 IS
583 SELECT sr_contact_point_id,
584 contact_point_id,
585 contact_type,
586 party_id
587 FROM cs_hz_sr_contact_points
588 WHERE incident_id = p_incident_id
589 AND sr_contact_point_id = NVL(p_sr_contact_point_id, sr_contact_point_id);
590
591 CURSOR l_emp_resource_csr (p_party_id hz_parties.party_id%TYPE)
592 IS
593 SELECT jtrs.resource_id
594 FROM jtf_rs_resource_extns jtrs
595 WHERE jtrs.source_id = p_party_id
596 AND jtrs.CATEGORY = 'EMPLOYEE'
597 AND SYSDATE BETWEEN jtrs.start_date_active AND nvl(jtrs.end_date_active, SYSDATE)
598 ;
599
600 BEGIN
601 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL for incident_id: ' || p_incident_id,
602 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL',FND_LOG.LEVEL_PROCEDURE);
603
604 FOR r_srcontpts_rec IN l_srcontpts_csr(p_incident_id, p_sr_contact_point_id) LOOP
605 IF r_srcontpts_rec.contact_type = 'EMPLOYEE' THEN
606 OPEN l_emp_resource_csr(r_srcontpts_rec.party_id);
607 FETCH l_emp_resource_csr INTO l_resource_id;
608 CLOSE l_emp_resource_csr;
609
610 -- delete resource from acc table
611 IF l_resource_id IS NOT NULL THEN
612 csm_resource_extns_event_pkg.resource_extns_acc_d(p_resource_id=>l_resource_id,
613 p_user_id=>p_user_id);
614 END IF;
615 END IF; --Bug 6880063
616 -- delete party record
617 csm_party_event_pkg.party_acc_d(p_party_id=> r_srcontpts_rec.party_id,
618 p_user_id=> p_user_id,
619 p_flowtype=> p_flowtype,
620 p_error_msg=> l_error_msg,
621 x_return_status=> l_return_status);
622
623
624 -- delete sr_contact_point from acc table
625 csm_sr_contact_event_pkg.sr_cntact_mdirty_d(p_sr_contact_point_id=>r_srcontpts_rec.sr_contact_point_id,
626 p_user_id=>p_user_id);
627 END LOOP;
628
629 CSM_UTIL_PKG.LOG('Leaving CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL for incident_id: ' || p_incident_id,
630 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL',FND_LOG.LEVEL_PROCEDURE);
631 EXCEPTION
632 WHEN OTHERS THEN
633 l_sqlerrno := to_char(SQLCODE);
634 l_sqlerrmsg := substr(SQLERRM, 1,2000);
635 l_error_msg := ' Exception in SPAWN_SR_CONTACT_DEL for incident_id:' || to_char(p_incident_id)
636 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
637 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SPAWN_SR_CONTACT_DEL',FND_LOG.LEVEL_EXCEPTION);
638 RAISE;
639 END SPAWN_SR_CONTACT_DEL;
640
641 PROCEDURE SR_ITEM_DEL_INIT(p_incident_id IN NUMBER, p_instance_id IN NUMBER, p_party_site_id IN NUMBER,
642 p_party_id IN NUMBER, p_location_id IN NUMBER, p_organization_id IN NUMBER,
643 p_user_id IN NUMBER, p_flow_type IN VARCHAR2)
644 IS
645 l_sqlerrno VARCHAR2(20);
646 l_sqlerrmsg VARCHAR2(4000);
647 l_error_msg VARCHAR2(4000);
648 l_return_status VARCHAR2(2000);
649
650 BEGIN
651 CSM_UTIL_PKG.LOG('Entering SR_ITEM_DEL_INIT for incident_id: ' || p_incident_id ||
652 ' and instance_id: ' || p_instance_id,'CSM_SR_EVENT_PKG.SR_ITEM_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
653
654 -- spawn item instances del
655 csm_sr_event_pkg.spawn_item_instances_del(p_instance_id=>p_instance_id,p_organization_id=>p_organization_id,
656 p_user_id=>p_user_id);
657
658 IF NOT CSM_UTIL_PKG.IS_HTML5_USER(p_user_id ) THEN
659
660 -- delete IB at location (logic not correct)
661 -- 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,
662 -- p_party_id=>p_party_id,p_location_id=>p_location_id,
663 -- p_user_id=>p_user_id, p_flow_type=>p_flow_type);
664
665 -- spawn counters DEL
666 csm_item_instance_event_pkg.spawn_counters_del(p_instance_id=>p_instance_id, p_user_id=>p_user_id);
667
668 -- delete IB notes
669 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'CP',
670 p_sourceobjectid=>p_instance_id,
671 p_userid=>p_user_id,
672 p_error_msg=>l_error_msg,
673 x_return_status=>l_return_status);
674
675 END IF;
676 CSM_UTIL_PKG.LOG('Leaving SR_ITEM_DEL_INIT for incident_id: ' || p_incident_id ||
677 ' and instance_id: ' || p_instance_id,'CSM_SR_EVENT_PKG.SR_ITEM_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
678 EXCEPTION
679 WHEN OTHERS THEN
680 l_sqlerrno := to_char(SQLCODE);
681 l_sqlerrmsg := substr(SQLERRM, 1,2000);
682 l_error_msg := ' Exception in SR_ITEM_DEL_INIT for incident_id: ' || p_incident_id || ' and instance_id:'
683 || to_char(p_instance_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
684 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_ITEM_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
685 RAISE;
686 END SR_ITEM_DEL_INIT;
687
688 PROCEDURE SPAWN_ITEM_INSTANCES_DEL (p_instance_id IN NUMBER, p_organization_id IN NUMBER,p_user_id IN NUMBER)
689 IS
690 l_sqlerrno VARCHAR2(20);
691 l_sqlerrmsg VARCHAR2(4000);
692 l_error_msg VARCHAR2(4000);
693 l_return_status VARCHAR2(2000);
694 l_transaction_date DATE;
695 l_null_relationship_id csi_ii_relationships.relationship_id%TYPE;
696 l_organization_id NUMBER;
697
698 -- get all the child instances of the parent instance as well as the parent
699 CURSOR l_parent_child_instance_csr (p_transaction_date date,b_htm5 VARCHAR2)
700 IS
701 SELECT cir.relationship_id AS relationship_id ,
702 cir.subject_id AS instance_id ,
703 cii.inventory_item_id
704 FROM (SELECT * FROM CSI_II_RELATIONSHIPS CIRo
705 WHERE CIRo.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
706 AND (b_htm5='N' OR LEVEL <=2) --for htm5 functionality SR1(A-B , B-C) & SR2(B-C,C-D) are downloaded. if no level for htm5, then SR1 will remove C-D as well
707 START WITH CIRo.OBJECT_ID = p_instance_id
708 CONNECT BY NOCYCLE CIRo.OBJECT_ID = PRIOR CIRo.SUBJECT_ID
709 ) CIR,
710 CSI_ITEM_INSTANCES CII
711 WHERE CII.INSTANCE_ID = CIR.SUBJECT_ID
712 AND EXISTS(SELECT 1 FROM csm_ii_relationships_acc acc
713 where acc.relationship_id = cir.relationship_id
714 and acc.user_id=p_user_id)
715 UNION
716 SELECT l_null_relationship_id AS relationship_id,
717 p_instance_id AS instance_id,
718 cii.inventory_item_id
719 FROM csi_item_instances cii
720 WHERE cii.instance_id = p_instance_id
721 AND EXISTS(SELECT 1 FROM csm_item_instances_acc acc
722 where acc.instance_id =p_instance_id
723 and acc.user_id=p_user_id)
724 -- get the parent instance
725 UNION
726 SELECT cir.relationship_id as relationship_id,
727 cir.object_id AS instance_id,
728 cii.inventory_item_id
729 FROM CSI_II_RELATIONSHIPS cir,
730 csi_item_instances cii
731 WHERE cir.RELATIONSHIP_TYPE_CODE = 'COMPONENT-OF'
732 AND cir.SUBJECT_ID = p_instance_id
733 AND cii.instance_id = cir.object_id
734 AND EXISTS(SELECT 1 FROM csm_ii_relationships_acc acc
735 where acc.relationship_id = cir.relationship_id
736 and acc.user_id=p_user_id);
737
738 -- get additional IB attributes if existing to delete
739 CURSOR l_csi_iea_values_del_csr(p_instance_id IN NUMBER)
740 IS
741 SELECT attrval.attribute_value_id
742 FROM csi_iea_values attrval,
743 csi_i_extended_attribs attr
744 WHERE attrval.instance_id = p_instance_id
745 AND attrval.attribute_id = attr.attribute_id
746 AND EXISTS(SELECT 1 FROM CSM_CSI_ITEM_ATTR_ACC acc
747 WHERE acc.attribute_value_id = attrval.attribute_value_id
748 AND acc.USER_ID=p_user_id);
749
750 l_htm5_user BOOLEAN:= CSM_UTIL_PKG.IS_HTML5_USER(p_user_id );
751 BEGIN
752 CSM_UTIL_PKG.LOG('Entering SPAWN_ITEM_INSTANCES_DEL for instance_id: ' || p_instance_id,
753 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_DEL',FND_LOG.LEVEL_PROCEDURE);
754
755 l_transaction_date := SYSDATE;
756 l_null_relationship_id := TO_NUMBER(NULL);
757
758 -- get parent and all child instances
759 FOR l_parent_child_instance_rec IN l_parent_child_instance_csr(l_transaction_date,csm_util_pkg.bool2yn(l_htm5_user)) LOOP
760
761 IF l_parent_child_instance_rec.relationship_id IS NOT NULL THEN
762 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);
763 END IF;
764
765 --delete item instance
766 csm_item_instance_event_pkg.ITEM_INSTANCES_ACC_D(p_instance_id=>l_parent_child_instance_rec.instance_id,
767 p_user_id=>p_user_id,
768 p_error_msg=>l_error_msg,
769 x_return_status=>l_return_status);
770
771 -- delete mtl_system_items bug 3949282
772 csm_mtl_system_items_event_pkg.MTL_SYSTEM_ITEMS_ACC_D(l_parent_child_instance_rec.inventory_item_id,
773 p_organization_id,
774 p_user_id,
775 l_error_msg,
776 l_return_status);
777 END LOOP;
778
779 IF NOT l_htm5_user THEN
780 -- process deletes of additional attributes
781 FOR r_csi_iea_values_del_rec IN l_csi_iea_values_del_csr(p_instance_id) LOOP
782 CSM_ACC_PKG.Delete_acc
783 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
784 ,P_ACC_TABLE_NAME => g_acc_table_name1
785 ,P_PK1_NAME => g_pk1_name1
786 ,P_PK1_NUM_VALUE => r_csi_iea_values_del_rec.attribute_value_id
787 ,P_USER_ID => p_user_id
788 );
789 END LOOP;
790 END IF;
791
792 CSM_UTIL_PKG.LOG('Leaving SPAWN_ITEM_INSTANCES_DEL for instance_id: ' || p_instance_id,
793 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_DEL',FND_LOG.LEVEL_PROCEDURE);
794 EXCEPTION
795 WHEN OTHERS THEN
796 l_sqlerrno := to_char(SQLCODE);
797 l_sqlerrmsg := substr(SQLERRM, 1,2000);
798 l_error_msg := l_error_msg || '- Exception in SPAWN_ITEM_INSTANCES_DEL for instance_id:'
799 || to_char(p_instance_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
800 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SPAWN_ITEM_INSTANCES_DEL',FND_LOG.LEVEL_EXCEPTION);
801 RAISE;
802 END SPAWN_ITEM_INSTANCES_DEL;
803
804 PROCEDURE INCIDENTS_ACC_D(p_incident_id IN NUMBER, p_user_id IN NUMBER)
805 IS
806 l_sqlerrno VARCHAR2(20);
807 l_sqlerrmsg VARCHAR2(4000);
808 l_error_msg VARCHAR2(4000);
809 l_return_status VARCHAR2(2000);
810
811 BEGIN
812 CSM_UTIL_PKG.LOG('Entering INCIDENTS_ACC_D for incident_id: ' || p_incident_id,
813 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
814
815 CSM_ACC_PKG.Delete_Acc
816 ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
817 ,P_ACC_TABLE_NAME => g_incidents_acc_table_name
818 ,P_PK1_NAME => g_incidents_pk1_name
819 ,P_PK1_NUM_VALUE => p_incident_id
820 ,P_USER_ID => p_user_id
821 );
822
823 CSM_UTIL_PKG.LOG('Leaving INCIDENTS_ACC_D for incident_id: ' || p_incident_id,
824 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_D',FND_LOG.LEVEL_PROCEDURE);
825 EXCEPTION
826 WHEN OTHERS THEN
827 l_sqlerrno := to_char(SQLCODE);
828 l_sqlerrmsg := substr(SQLERRM, 1,2000);
829 l_error_msg := ' Exception in INCIDENTS_ACC_D for incident_id:'
830 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
831 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.INCIDENTS_ACC_D',FND_LOG.LEVEL_EXCEPTION);
832 RAISE;
833 END INCIDENTS_ACC_D;
834
835 --12.1XB6
836 FUNCTION IS_SR_DOWNLOADED_TO_OWNER(p_task_id NUMBER) RETURN BOOLEAN
837 IS
838 CURSOR c_sr_owner_group (b_task_id NUMBER) IS
839 SELECT inc.owner_group_id,
840 inc.created_by
841 FROM CS_INCIDENTS_ALL_B inc,
842 JTF_TASKS_B tsk,
843 JTF_TASK_TYPES_B ttype
844 WHERE tsk.TASK_ID=b_task_id
845 AND tsk.SOURCE_OBJECT_TYPE_CODE='SR'
846 AND tsk.SOURCE_OBJECT_ID=inc.INCIDENT_ID
847 AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
848 AND ttype.RULE='DISPATCH';
849
850 l_owner_group_id number;
851 l_created_by number ;
852 BEGIN
853 OPEN c_sr_owner_group(p_task_id);
854 FETCH c_sr_owner_group INTO l_owner_group_id,l_created_by;
855 CLOSE c_sr_owner_group;
856
857 IF CSM_UTIL_PKG.IS_HTML5_USER(CSM_UTIL_PKG.get_group_owner(l_owner_group_id)) AND CSM_UTIL_PKG.IS_HTML5_USER(l_created_by) THEN
858 RETURN FALSE;
859 END IF;
860
861 RETURN CSM_UTIL_PKG.is_mfs_group(l_owner_group_id) OR CSM_UTIL_PKG.is_palm_user(l_created_by);
862
863 END;
864
865 --12.1
866 PROCEDURE SR_INS_INIT(p_incident_id IN NUMBER)
867 IS
868 l_sqlerrno VARCHAR2(20);
869 l_sqlerrmsg VARCHAR2(4000);
870 l_error_msg VARCHAR2(4000);
871 l_return_status VARCHAR2(2000);
872 l_organization_id NUMBER;
873 l_owner_id NUMBER;
874 l_owner_resource_id NUMBER;
875
876 --Change for Asset
877 CURSOR l_sr_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE)
878 IS
879 SELECT csi.incident_id,
880 csi.customer_id,
881 csi.install_site_id,
882 csi.customer_product_id,
883 csi.inventory_item_id,
884 csi.inv_organization_id,
885 csi.contract_service_id,
886 csi.created_by,
887 csi.incident_location_id,
888 csi.customer_id party_id,
889 decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
890 'HZ_PARTY_SITE',
891 (select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
892 'HZ_LOCATION',
893 (select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
894 ) location_id ,
895 nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
896 csi.owner_group_id,
897 csi.org_id
898 FROM cs_incidents_all_b csi
899 WHERE csi.incident_id = p_incident_id
900 AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION');
901 --not required as counter is important
902 /*AND NOT EXISTS
903 (SELECT 1
904 FROM csm_incidents_all_acc acc
905 WHERE acc.incident_id = csi.incident_id
906 AND acc.user_id = CSM_UTIL_PKG.get_group_owner(csi.owner_group_id));*/
907
908
909 CURSOR l_addr_id_csr (p_incident_id IN NUMBER)
910 IS
911 SELECT NVL(LOCATION_ID,ADDRESS_ID)--R12Assest
912 FROM JTF_TASKS_B
913 WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
914 AND SOURCE_OBJECT_ID = p_incident_id;
915
916 l_sr_rec l_sr_csr%ROWTYPE;
917
918 CURSOR c_tasks(b_incident_id NUMBER)
919 IS
920 SELECT TASK_ID
921 FROM JTF_TASKS_B tsk,
922 JTF_TASK_TYPES_B ttype
923 WHERE tsk.SOURCE_OBJECT_TYPE_CODE='SR'
924 AND tsk.SOURCE_OBJECT_ID=b_incident_id
925 AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
926 AND ttype.RULE='DISPATCH';
927
928 CURSOR c_task_assignments(b_task_id NUMBER)
929 IS
930 SELECT TASK_ASSIGNMENT_ID,resource_id
931 FROM JTF_TASK_ASSIGNMENTS
932 WHERE TASK_ID=b_task_id;
933
934 CURSOR c_resource_id(b_user_id NUMBER) IS
935 SELECT RESOURCE_ID
936 FROM JTF_RS_RESOURCE_EXTNS
937 WHERE USER_ID=b_user_id;
938
939 --assignee bug
940 CURSOR c_user_id(b_resource_id NUMBER) IS
941 SELECT USER_ID
942 FROM JTF_RS_RESOURCE_EXTNS
943 WHERE RESOURCE_ID=b_resource_id;
944
945 l_assignee_user_id NUMBER;
946
947 BEGIN
948 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);
949
950 OPEN l_sr_csr(p_incident_id);
951 FETCH l_sr_csr INTO l_sr_rec;
952 IF l_sr_csr%NOTFOUND THEN
953 CLOSE l_sr_csr;
954 RETURN;
955 END IF;
956 CLOSE l_sr_csr;
957 --12.1XB6
958 IF ( NOT CSM_UTIL_PKG.is_mfs_group(l_sr_rec.owner_group_id)) THEN
959 IF ( NOT CSM_UTIL_PKG.is_palm_user(l_sr_rec.created_by)) THEN
960 CSM_UTIL_PKG.LOG('Leaving SR_INS_INIT because the Owner group of SR is not a mobile resource group
961 and also this SR , incident_id: ' || p_incident_id ||' is not created by a mobile user'
962 ,'CSM_SR_EVENT_PKG.SR_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
963 RETURN;
964 ELSE
965 l_owner_id := CSM_UTIL_PKG.get_owner(l_sr_rec.created_by);
966 END IF;
967 ELSE
968 l_owner_id := CSM_UTIL_PKG.get_group_owner(l_sr_rec.owner_group_id);
969 END IF;
970
971 IF CSM_UTIL_PKG.IS_HTML5_USER(l_owner_id) THEN --no download for owners/created by in htm5
972 CSM_UTIL_PKG.LOG('Leaving SR_INS_INIT as this SR is owned/created by HTML5 user','CSM_SR_EVENT_PKG.SR_INS_INIT',FND_LOG.LEVEL_PROCEDURE);
973 RETURN;
974 END IF;
975
976 -- not necessary as all DISPATCH Sr's need an install site id
977 IF l_sr_rec.INCIDENT_LOCATION_ID IS NULL THEN
978 OPEN l_addr_id_csr(p_incident_id);
979 FETCH l_addr_id_csr INTO l_sr_rec.INCIDENT_LOCATION_ID;
980 CLOSE l_addr_id_csr;
981 END IF;
982
983
984 -- get Service Inv Validation org
985 l_organization_id := csm_profile_pkg.get_organization_id(l_owner_id);
986
987 --get SR notes
988 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'SR',
989 p_sourceobjectid=>p_incident_id,
990 p_userid=>l_owner_id,
991 p_error_msg=>l_error_msg,
992 x_return_status=>l_return_status);
993
994 -- get SR contacts
995 csm_sr_event_pkg.spawn_sr_contacts_ins(p_incident_id=>p_incident_id,
996 p_user_id=>l_owner_id,
997 p_flowtype=>NULL);
998 IF l_sr_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
999 --insert location for the sr
1000 CSM_HZ_LOCATIONS_EVENT_PKG.insert_location(p_location_id => l_sr_rec.incident_location_id,
1001 p_user_id => l_owner_id);
1002 ELSE
1003 -- get party site
1004 IF l_sr_rec.incident_location_id IS NOT NULL THEN
1005 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_sr_rec.incident_location_id,
1006 p_user_id => l_owner_id,
1007 p_flowtype => NULL,
1008 p_error_msg => l_error_msg,
1009 x_return_status => l_return_status);
1010
1011 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCT_SITES_INS
1012 (p_party_site_id=> l_sr_rec.incident_location_id,
1013 p_org_id =>l_sr_rec.org_id ,p_user_id =>l_owner_id);
1014 END IF;
1015 END IF;
1016 -- spawn SR customer ins
1017 IF l_sr_rec.customer_id IS NOT NULL THEN
1018 csm_party_event_pkg.party_acc_i(p_party_id => l_sr_rec.customer_id,
1019 p_user_id => l_owner_id,
1020 p_flowtype => NULL,
1021 p_error_msg => l_error_msg,
1022 x_return_status => l_return_status);
1023 --insert Accounts for the above party-R12
1024 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_INS
1025 (p_party_id=>l_sr_rec.customer_id
1026 ,p_user_id =>l_owner_id);
1027
1028 END IF;
1029
1030 IF l_sr_rec.customer_product_id IS NOT NULL THEN
1031 -- spawn SR item instance insert
1032 csm_sr_event_pkg.sr_item_ins_init(p_incident_id=>l_sr_rec.incident_id,
1033 p_instance_id=>l_sr_rec.customer_product_id,
1034 p_party_site_id=>l_sr_rec.incident_location_id,
1035 p_party_id=>l_sr_rec.party_id,
1036 p_location_id=>l_sr_rec.location_id,
1037 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1038 p_user_id=>l_owner_id,
1039 p_flow_type=>NULL);
1040
1041 ELSIF l_sr_rec.customer_product_id IS NULL OR l_sr_rec.customer_product_id = 0 THEN
1042 IF l_sr_rec.inventory_item_id IS NOT NULL THEN
1043 csm_mtl_system_items_event_pkg.mtl_system_items_acc_i
1044 (p_inventory_item_id=>l_sr_rec.inventory_item_id,
1045 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1046 p_user_id=>l_owner_id,
1047 p_error_msg=>l_error_msg,
1048 x_return_status=>l_return_status);
1049 END IF;
1050 END IF;
1051
1052 -- insert into incidents acc
1053 csm_sr_event_pkg.incidents_acc_i(p_incident_id=>l_sr_rec.incident_id,
1054 p_user_id=>l_owner_id);
1055
1056 -- get SR contracts
1057 csm_contract_event_pkg.sr_contract_acc_i(p_incident_id=>l_sr_rec.incident_id,
1058 p_user_id=>l_owner_id);
1059
1060 --get contract notes
1061 IF l_sr_rec.contract_service_id IS NOT NULL THEN
1062 csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'OKS_COV_NOTE',
1063 p_sourceobjectid=>l_sr_rec.contract_service_id,
1064 p_userid=>l_owner_id,
1065 p_error_msg=>l_error_msg,
1066 x_return_status=>l_return_status);
1067 END IF;
1068
1069 --DOWNLOAD TASK/TASK_ASSIGNMENTS TO SR OWNER
1070 FOR task_rec IN c_tasks(p_incident_id)
1071 LOOP
1072 --dwnld notes to owner if required
1073 /* csm_notes_event_pkg.notes_make_dirty_i_grp(p_sourceobjectcode=>'TASK',
1074 p_sourceobjectid=>task_rec.task_id,
1075 p_userid=>l_owner_id,
1076 p_error_msg=>l_error_msg,
1077 x_return_status=>l_return_status);*/
1078
1079 csm_task_event_pkg.acc_insert(p_task_id=>task_rec.task_id,p_user_id=>l_owner_id);
1080
1081 for assign_rec in c_task_assignments(task_rec.task_id)
1082 loop
1083 csm_task_assignment_event_pkg.acc_insert(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
1084
1085 -- to download other grp's resource if required
1086 OPEN c_resource_id(l_owner_id);
1087 FETCH c_resource_id INTO l_owner_resource_id;
1088 CLOSE c_resource_id;
1089 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,assign_rec.resource_id) THEN
1090 --assignee bug
1091 OPEN c_user_id(assign_rec.resource_id);
1092 FETCH c_user_id INTO l_assignee_user_id;
1093 CLOSE c_user_id;
1094 IF l_assignee_user_id IS NOT NULL THEN
1095 CSM_USER_EVENT_PKG.INSERT_ACC(l_assignee_user_id,l_owner_id);
1096 END IF;
1097 END IF;
1098 --to decide if LOBS shd be downloaded for task assignment and also service history
1099 end loop;
1100
1101 END LOOP;
1102
1103 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);
1104 EXCEPTION
1105 WHEN OTHERS THEN
1106 l_sqlerrno := to_char(SQLCODE);
1107 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1108 l_error_msg := ' Exception in SR_INS_INIT for incident_id:'
1109 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1110 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_INS_INIT',FND_LOG.LEVEL_EXCEPTION);
1111 RAISE;
1112 END SR_INS_INIT;
1113
1114 --12.1
1115 PROCEDURE SR_DEL_INIT(p_incident_id IN NUMBER,p_user_id IN NUMBER)
1116 IS
1117 l_sqlerrno VARCHAR2(20);
1118 l_sqlerrmsg VARCHAR2(4000);
1119 l_error_msg VARCHAR2(4000);
1120 l_return_status VARCHAR2(2000);
1121 l_organization_id NUMBER;
1122 l_owner_id NUMBER;
1123 l_owner_resource_id NUMBER;
1124
1125 --12.1XB6
1126 CURSOR l_sr_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE,b_user_id NUMBER)
1127 IS
1128 SELECT csi.incident_id,
1129 csi.customer_id,
1130 csi.install_site_id,
1131 csi.customer_product_id,
1132 csi.inventory_item_id,
1133 csi.inv_organization_id,
1134 csi.contract_service_id,
1135 csi.created_by,
1136 csi.incident_location_id,
1137 csi.customer_id party_id,
1138 decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
1139 'HZ_PARTY_SITE',
1140 (select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
1141 'HZ_LOCATION',
1142 (select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
1143 ) location_id ,
1144 nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
1145 csi.owner_group_id
1146 FROM cs_incidents_all_b csi
1147 WHERE csi.incident_id = p_incident_id
1148 AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION')
1149 AND EXISTS
1150 (SELECT 1
1151 FROM csm_incidents_all_acc acc
1152 WHERE acc.incident_id = csi.incident_id
1153 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)) ));
1154
1155 l_sr_rec l_sr_csr%ROWTYPE;
1156
1157 CURSOR c_tasks(b_incident_id NUMBER)
1158 IS
1159 SELECT tsk.TASK_ID
1160 FROM JTF_TASKS_B tsk,
1161 JTF_TASK_TYPES_B ttype
1162 WHERE tsk.SOURCE_OBJECT_TYPE_CODE='SR'
1163 AND tsk.SOURCE_OBJECT_ID=b_incident_id
1164 AND ttype.TASK_TYPE_ID = tsk.TASK_TYPE_ID
1165 AND ttype.RULE='DISPATCH';
1166
1167 CURSOR c_task_assignments(b_task_id NUMBER)
1168 IS
1169 SELECT TASK_ASSIGNMENT_ID,resource_id
1170 FROM JTF_TASK_ASSIGNMENTS
1171 WHERE TASK_ID=b_task_id;
1172
1173 CURSOR c_resource_id(b_user_id NUMBER) IS
1174 SELECT RESOURCE_ID
1175 FROM JTF_RS_RESOURCE_EXTNS
1176 WHERE USER_ID=b_user_id;
1177
1178 --assignee bug
1179 CURSOR c_user_id(b_resource_id NUMBER) IS
1180 SELECT USER_ID
1181 FROM JTF_RS_RESOURCE_EXTNS
1182 WHERE RESOURCE_ID=b_resource_id;
1183
1184 l_assignee_user_id NUMBER;
1185
1186 BEGIN
1187 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);
1188
1189 OPEN l_sr_csr(p_incident_id,p_user_id);
1190 FETCH l_sr_csr INTO l_sr_rec;
1191 IF l_sr_csr%NOTFOUND THEN
1192 CLOSE l_sr_csr;
1193 RETURN;
1194 END IF;
1195 CLOSE l_sr_csr;
1196
1197 --12.1XB6
1198 IF p_user_id IS NULL THEN
1199 IF ( NOT CSM_UTIL_PKG.is_mfs_group(l_sr_rec.owner_group_id)) THEN
1200 IF ( NOT CSM_UTIL_PKG.is_palm_user(l_sr_rec.created_by)) THEN
1201 CSM_UTIL_PKG.LOG('Leaving SR_DEL_INIT since the Owner group of SR is not a mobile resource group
1202 and also this SR , incident_id: ' || p_incident_id ||' is not created by a mobile user'
1203 ,'CSM_SR_EVENT_PKG.SR_DEL_INIT',FND_LOG.LEVEL_PROCEDURE);
1204 RETURN;
1205 ELSE
1206 l_owner_id := CSM_UTIL_PKG.get_owner(l_sr_rec.created_by);
1207 END IF;
1208 ELSE
1209 l_owner_id := CSM_UTIL_PKG.get_group_owner(l_sr_rec.owner_group_id);
1210 END IF;
1211 ELSE
1212 l_owner_id:=p_user_id;
1213 END IF;
1214 -- get Service Inv Validation org
1215 l_organization_id := csm_profile_pkg.get_organization_id(l_owner_id);
1216
1217 -- delete from incidents acc
1218 csm_sr_event_pkg.incidents_acc_d(p_incident_id=>l_sr_rec.incident_id,
1219 p_user_id=>l_owner_id);
1220
1221 -- delete SR notes
1222 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'SR',
1223 p_sourceobjectid=>l_sr_rec.incident_id,
1224 p_userid=>l_owner_id,
1225 p_error_msg=>l_error_msg,
1226 x_return_status=>l_return_status);
1227
1228 -- delete SR contacts
1229 csm_sr_event_pkg.spawn_sr_contact_del(p_incident_id=>l_sr_rec.incident_id,
1230 p_user_id=>l_owner_id,
1231 p_flowtype=>NULL);
1232 IF l_sr_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
1233 --insert location for the sr
1234 csm_hz_locations_event_pkg.delete_location(p_location_id => l_sr_rec.incident_location_id,
1235 p_user_id => l_owner_id);
1236 ELSE
1237 -- spawn party site del
1238 IF l_sr_rec.incident_location_id IS NOT NULL THEN
1239 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => l_sr_rec.incident_location_id,
1240 p_user_id => l_owner_id,
1241 p_flowtype => NULL,
1242 p_error_msg => l_error_msg,
1243 x_return_status => l_return_status);
1244 END IF;
1245 END IF;
1246 -- spawn SR customer del
1247 IF l_sr_rec.customer_id IS NOT NULL THEN
1248 csm_party_event_pkg.party_acc_d(p_party_id => l_sr_rec.customer_id,
1249 p_user_id => l_owner_id,
1250 p_flowtype => NULL,
1251 p_error_msg => l_error_msg,
1252 x_return_status => l_return_status);
1253 --Delete Accounts for the above party-R12
1254 CSM_CUSTOMER_ACCOUNT_EVENT_PKG.CUST_ACCOUNTS_DEL
1255 (p_party_id=>l_sr_rec.customer_id
1256 ,p_user_id =>l_owner_id);
1257
1258 END IF;
1259
1260 -- delete SR contract
1261 IF l_sr_rec.contract_service_id IS NOT NULL THEN
1262 csm_contract_event_pkg.sr_contract_acc_d(p_incident_id=>l_sr_rec.inventory_item_id,
1263 p_user_id=>l_owner_id);
1264 END IF;
1265
1266 -- delete contract notes
1267 IF l_sr_rec.contract_service_id IS NOT NULL THEN
1268 csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'OKS_COV_NOTE',
1269 p_sourceobjectid=>l_sr_rec.contract_service_id,
1270 p_userid=>l_owner_id,
1271 p_error_msg=>l_error_msg,
1272 x_return_status=>l_return_status);
1273 END IF;
1274
1275 IF l_sr_rec.customer_product_id IS NOT NULL THEN
1276 -- spawn SR item instance delete
1277 csm_sr_event_pkg.sr_item_del_init(p_incident_id=>l_sr_rec.incident_id,
1278 p_instance_id=>l_sr_rec.customer_product_id,
1279 p_party_site_id=>l_sr_rec.incident_location_id,
1280 p_party_id=>l_sr_rec.party_id,
1281 p_location_id=>l_sr_rec.location_id,
1282 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1283 p_user_id=>l_owner_id,
1284 p_flow_type=>NULL);
1285
1286 ELSIF l_sr_rec.customer_product_id IS NULL OR l_sr_rec.customer_product_id = 0 THEN
1287 IF l_sr_rec.inventory_item_id IS NOT NULL THEN
1288 csm_mtl_system_items_event_pkg.mtl_system_items_acc_d
1289 (p_inventory_item_id=>l_sr_rec.inventory_item_id,
1290 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1291 p_user_id=>l_owner_id,
1292 p_error_msg=>l_error_msg,
1293 x_return_status=>l_return_status);
1294 END IF;
1295 END IF;
1296
1297 --DELETE TASK/TASK_ASSIGNMENTS DOWNLOADED TO SR OWNER
1298 FOR task_rec IN c_tasks(p_incident_id)
1299 LOOP
1300 -- delete task notes if inserted
1301 /* csm_notes_event_pkg.notes_make_dirty_d_grp(p_sourceobjectcode=>'TASK',
1302 p_sourceobjectid=>task_rec.task_id,
1303 p_userid=>l_owner_id,
1304 p_error_msg=>l_error_msg,
1305 x_return_status=>l_return_status);*/
1306
1307 csm_task_event_pkg.acc_delete(p_task_id=>task_rec.task_id,p_user_id=>l_owner_id);
1308
1309 for assign_rec in c_task_assignments(task_rec.task_id)
1310 loop
1311
1312 csm_task_assignment_event_pkg.acc_delete(p_task_assignment_id=>assign_rec.task_assignment_id,p_user_id=>l_owner_id);
1313
1314 /*Other grp's member Resource to be deleted from acc if assigned to him*/
1315 OPEN c_resource_id(l_owner_id);
1316 FETCH c_resource_id INTO l_owner_resource_id;
1317 CLOSE c_resource_id;
1318 IF NOT CSM_UTIL_PKG.from_same_group(l_owner_resource_id,assign_rec.resource_id) THEN
1319 --assignee bug
1320 OPEN c_user_id(assign_rec.resource_id);
1321 FETCH c_user_id INTO l_assignee_user_id;
1322 CLOSE c_user_id;
1323 IF l_assignee_user_id IS NOT NULL THEN
1324 CSM_USER_EVENT_PKG.DELETE_ACC(l_assignee_user_id,l_owner_id);
1325 END IF;
1326 END IF;
1327 end loop;
1328
1329 END LOOP;
1330
1331 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);
1332 EXCEPTION
1333 WHEN OTHERS THEN
1334 l_sqlerrno := to_char(SQLCODE);
1335 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1336 l_error_msg := ' Exception in SR_DEL_INIT for incident_id:'
1337 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1338 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_DEL_INIT',FND_LOG.LEVEL_EXCEPTION);
1339 RAISE;
1340 END SR_DEL_INIT;
1341
1342 --12.1
1343 PROCEDURE SR_UPD_INIT(p_incident_id IN NUMBER, p_is_install_site_updated IN VARCHAR2,
1344 p_old_install_site_id IN NUMBER,
1345 p_is_incident_location_updated IN VARCHAR2,
1346 p_old_incident_location_id IN NUMBER, p_is_sr_customer_updated IN VARCHAR2,
1347 p_old_sr_customer_id IN NUMBER, p_is_sr_instance_updated IN VARCHAR2,
1348 p_old_instance_id IN NUMBER, p_is_inventory_item_updated IN VARCHAR2,
1349 p_old_inventory_item_id IN NUMBER, p_old_organization_id IN NUMBER,
1350 p_old_party_id IN NUMBER, p_old_location_id IN NUMBER,
1351 p_is_contr_service_id_updated IN VARCHAR2, p_old_contr_service_id IN NUMBER)
1352 IS
1353 l_sqlerrno VARCHAR2(20);
1354 l_sqlerrmsg VARCHAR2(4000);
1355 l_error_msg VARCHAR2(4000);
1356 l_return_status VARCHAR2(2000);
1357 l_organization_id NUMBER;
1358
1359 CURSOR l_sr_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE)
1360 IS
1361 SELECT csi.incident_id,
1362 csi.customer_id,
1363 csi.install_site_id,
1364 csi.customer_product_id,
1365 csi.inventory_item_id,
1366 csi.inv_organization_id,
1367 csi.contract_service_id,
1368 csi.created_by,
1369 csi.incident_location_id,
1370 csi.customer_id party_id,
1371 decode(nvl(csi.incident_location_type,'HZ_PARTY_SITE'),
1372 'HZ_PARTY_SITE',
1373 (select location_id from hz_party_sites where party_site_id = NVL(csi.incident_location_id, csi.install_site_id)),
1374 'HZ_LOCATION',
1375 (select location_id from hz_locations where location_id = NVL(csi.incident_location_id, csi.install_site_id))
1376 ) location_id ,
1377 nvl(csi.incident_location_type,'HZ_PARTY_SITE') incident_location_type,
1378 csi.owner_group_id
1379 FROM cs_incidents_all_b csi
1380 WHERE csi.incident_id = p_incident_id
1381 AND nvl(csi.incident_location_type,'HZ_PARTY_SITE') IN ('HZ_PARTY_SITE','HZ_LOCATION');
1382
1383
1384 CURSOR l_addr_id_csr (p_incident_id IN NUMBER)
1385 IS
1386 SELECT NVL(LOCATION_ID,ADDRESS_ID)--R12Assest
1387 FROM JTF_TASKS_B
1388 WHERE SOURCE_OBJECT_TYPE_CODE = 'SR'
1389 AND SOURCE_OBJECT_ID = p_incident_id;
1390
1391 l_sr_rec l_sr_csr%ROWTYPE;
1392
1393 CURSOR l_csm_task_assg_csr (p_incident_id cs_incidents_all_b.incident_id%TYPE)
1394 IS
1395 SELECT acc.access_id, acc.user_id
1396 FROM csm_incidents_all_acc acc
1397 WHERE acc.incident_id = p_incident_id;
1398
1399
1400 BEGIN
1401 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);
1402
1403 OPEN l_sr_csr(p_incident_id);
1404 FETCH l_sr_csr INTO l_sr_rec;
1405 IF l_sr_csr%NOTFOUND THEN
1406 CLOSE l_sr_csr;
1407 RETURN;
1408 END IF;
1409 CLOSE l_sr_csr;
1410
1411 -- not necessary as all DISPATCH Sr's need an install site id
1412 IF l_sr_rec.incident_location_id IS NULL THEN
1413 OPEN l_addr_id_csr(p_incident_id);
1414 FETCH l_addr_id_csr INTO l_sr_rec.incident_location_id;
1415 CLOSE l_addr_id_csr;
1416 END IF;
1417
1418 -- get all users having access for this SR
1419 FOR r_csm_task_assg_rec IN l_csm_task_assg_csr(p_incident_id) LOOP
1420 l_organization_id := csm_profile_pkg.get_organization_id(r_csm_task_assg_rec.user_id);
1421
1422 IF p_is_incident_location_updated = 'Y' THEN
1423 IF l_sr_rec.incident_location_type = 'HZ_LOCATION' THEN --R12 Assest
1424 --Delete old location
1425 IF p_old_incident_location_id IS NOT NULL THEN
1426
1427 csm_hz_locations_event_pkg.delete_location(p_location_id => l_sr_rec.incident_location_id,
1428 p_user_id => r_csm_task_assg_rec.user_id);
1429 END IF;
1430
1431 IF l_sr_rec.incident_location_id IS NOT NULL THEN
1432 -- insert new location
1433 csm_hz_locations_event_pkg.insert_location(p_location_id => l_sr_rec.incident_location_id,
1434 p_user_id => r_csm_task_assg_rec.user_id);
1435 END IF;
1436
1437 ELSE
1438
1439 IF p_old_incident_location_id IS NOT NULL THEN
1440 -- spawn party site del process
1441 csm_party_site_event_pkg.party_sites_acc_d(p_party_site_id => p_old_incident_location_id,
1442 p_user_id => r_csm_task_assg_rec.user_id,
1443 p_flowtype => NULL,
1444 p_error_msg => l_error_msg,
1445 x_return_status => l_return_status);
1446 END IF;
1447
1448 IF l_sr_rec.incident_location_id IS NOT NULL THEN
1449 -- spawn party site ins process
1450 csm_party_site_event_pkg.party_sites_acc_i(p_party_site_id => l_sr_rec.incident_location_id,
1451 p_user_id => r_csm_task_assg_rec.user_id,
1452 p_flowtype => NULL,
1453 p_error_msg => l_error_msg,
1454 x_return_status => l_return_status);
1455 END IF;
1456 END IF;
1457 END IF;
1458
1459 IF p_is_sr_customer_updated = 'Y' THEN
1460 IF p_old_sr_customer_id IS NOT NULL THEN
1461 -- spawn party del process
1462 csm_party_event_pkg.party_acc_d(p_party_id=>p_old_sr_customer_id,
1463 p_user_id=>r_csm_task_assg_rec.user_id,
1464 p_flowtype=>NULL,
1465 p_error_msg=>l_error_msg,
1466 x_return_status=>l_return_status);
1467 END IF;
1468
1469 IF l_sr_rec.customer_id IS NOT NULL THEN
1470 -- spawn party site ins process
1471 csm_party_event_pkg.party_acc_i(p_party_id=>l_sr_rec.customer_id,
1472 p_user_id=>r_csm_task_assg_rec.user_id,
1473 p_flowtype=>NULL,
1474 p_error_msg=>l_error_msg,
1475 x_return_status=>l_return_status);
1476 END IF;
1477 END IF;
1478
1479 IF p_is_sr_instance_updated = 'Y' THEN
1480 IF p_old_instance_id IS NOT NULL THEN
1481 -- spawn SR item instance delete
1482 csm_sr_event_pkg.sr_item_del_init(p_incident_id=>l_sr_rec.incident_id,
1483 p_instance_id=>p_old_instance_id,
1484 p_party_site_id=>p_old_incident_location_id,
1485 p_party_id=>p_old_party_id,
1486 p_location_id=>p_old_location_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_flow_type=>NULL);
1490 END IF;
1491
1492 IF l_sr_rec.customer_product_id IS NOT NULL THEN
1493 -- spawn SR item instance insert
1494 csm_sr_event_pkg.sr_item_ins_init(p_incident_id=>l_sr_rec.incident_id,
1495 p_instance_id=>l_sr_rec.customer_product_id,
1496 p_party_site_id=>l_sr_rec.incident_location_id,
1497 p_party_id=>l_sr_rec.party_id,
1498 p_location_id=>l_sr_rec.location_id,
1499 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1500 p_user_id=>r_csm_task_assg_rec.user_id,
1501 p_flow_type=>NULL);
1502 END IF;
1503 END IF;
1504
1505 IF p_is_inventory_item_updated = 'Y' THEN
1506 IF p_old_inventory_item_id IS NOT NULL THEN
1507 csm_mtl_system_items_event_pkg.mtl_system_items_acc_d
1508 (p_inventory_item_id=>p_old_inventory_item_id,
1509 p_organization_id=>NVL(p_old_organization_id, l_organization_id),
1510 p_user_id=>r_csm_task_assg_rec.user_id,
1511 p_error_msg=>l_error_msg,
1512 x_return_status=>l_return_status);
1513 END IF;
1514
1515 IF l_sr_rec.inventory_item_id IS NOT NULL THEN
1516 csm_mtl_system_items_event_pkg.mtl_system_items_acc_i
1517 (p_inventory_item_id=>l_sr_rec.inventory_item_id,
1518 p_organization_id=>NVL(l_sr_rec.inv_organization_id, l_organization_id),
1519 p_user_id=>r_csm_task_assg_rec.user_id,
1520 p_error_msg=>l_error_msg,
1521 x_return_status=>l_return_status);
1522 END IF;
1523 END IF;
1524
1525 IF p_is_contr_service_id_updated = 'Y' THEN
1526 csm_contract_event_pkg.sr_contract_acc_u(p_incident_id=>l_sr_rec.incident_id,
1527 p_old_contract_service_id=>p_old_contr_service_id,
1528 p_contract_service_id=>l_sr_rec.contract_service_id,
1529 p_user_id=>r_csm_task_assg_rec.user_id);
1530 END IF;
1531
1532 -- incidents make dirty for update
1533 CSM_ACC_PKG.Update_Acc
1534 ( P_PUBLICATION_ITEM_NAMES => g_incidents_pubi_name
1535 ,P_ACC_TABLE_NAME => g_incidents_acc_table_name
1536 ,P_USER_ID => r_csm_task_assg_rec.user_id
1537 ,P_ACCESS_ID => r_csm_task_assg_rec.access_id
1538 );
1539
1540 END LOOP;
1541
1542 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);
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 l_sqlerrno := to_char(SQLCODE);
1546 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1547 l_error_msg := ' Exception in SR_UPD_INIT for incident_id:'
1548 || to_char(p_incident_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1549 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.SR_UPD_INIT',FND_LOG.LEVEL_EXCEPTION);
1550 RAISE;
1551 END SR_UPD_INIT;
1552
1553 --Function to find whether the SR associated with Task assignment id is open before purging.
1554 FUNCTION IS_SR_OPEN ( p_task_id IN NUMBER)
1555 RETURN BOOLEAN
1556 IS
1557 l_sqlerrno VARCHAR2(20);
1558 l_sqlerrmsg VARCHAR2(4000);
1559 l_error_msg VARCHAR2(4000);
1560 l_incident_id cs_incidents_all_b.incident_id%TYPE;
1561
1562 CURSOR l_is_sr_open_csr(c_task_id jtf_tasks_b.task_id%TYPE)
1563 IS
1564 SELECT inc.incident_id
1565 FROM cs_incidents_all_b inc,
1566 cs_incident_statuses_b ists,
1567 jtf_tasks_b tsk
1568 WHERE inc.INCIDENT_STATUS_ID = ists.INCIDENT_STATUS_ID
1569 AND ists.CLOSE_FLAG = 'Y'
1570 AND tsk.task_id = c_task_id
1571 AND tsk.source_object_id = inc.incident_id;
1572
1573 BEGIN
1574 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.IS_SR_OPEN for task_id: ' || p_task_id,
1575 'CSM_SR_EVENT_PKG.IS_SR_OPEN',FND_LOG.LEVEL_PROCEDURE);
1576
1577 l_incident_id := NULL;
1578
1579 OPEN l_is_sr_open_csr(p_task_id);
1580 FETCH l_is_sr_open_csr INTO l_incident_id;
1581 IF l_is_sr_open_csr%NOTFOUND THEN
1582
1583 CLOSE l_is_sr_open_csr;
1584 CSM_UTIL_PKG.LOG('The SR is open for task_id : ' || p_task_id,
1585 'CSM_SR_EVENT_PKG.IS_SR_OPEN',FND_LOG.LEVEL_EXCEPTION);
1586 RETURN TRUE;
1587 END IF;
1588 CLOSE l_is_sr_open_csr;
1589
1590 CSM_UTIL_PKG.LOG('The SR is Closed for task_id : ' || p_task_id,
1591 'CSM_SR_EVENT_PKG.IS_SR_OPEN',FND_LOG.LEVEL_EXCEPTION);
1592
1593 RETURN FALSE;
1594
1595 EXCEPTION
1596 WHEN OTHERS THEN
1597 l_sqlerrno := to_char(SQLCODE);
1598 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1599 l_error_msg := ' Exception in IS_SR_OPEN for task_id:' || to_char(p_task_id)
1600 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1601 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.IS_SR_OPEN',FND_LOG.LEVEL_EXCEPTION);
1602 RAISE;
1603 END IS_SR_OPEN;
1604
1605
1606 --Function to find whether the SR associated with Task assignment id is open before purging.
1607 PROCEDURE GET_PROFORMA_INVOICE (
1608 itemtype IN VARCHAR2
1609 ,itemkey IN VARCHAR2
1610 ,actid IN NUMBER
1611 ,funcmode IN VARCHAR2
1612 ,RESULT IN OUT NOCOPY VARCHAR2)
1613 IS
1614 --PRAGMA AUTONOMOUS_TRANSACTION;
1615
1616 CURSOR c_check_results(c_USER_ID NUMBER,c_QUERY_ID NUMBER,c_INSTANCE_ID NUMBER)
1617 IS
1618 SELECT ACCESS_ID
1619 FROM CSM_QUERY_RESULTS_ACC
1620 WHERE QUERY_ID = c_QUERY_ID
1621 AND USER_ID = c_USER_ID
1622 AND INSTANCE_ID = c_INSTANCE_ID;
1623
1624 CURSOR c_get_sr_id (c_incident_number VARCHAR2)
1625 IS
1626 SELECT INCIDENT_ID FROM CS_INCIDENTS_ALL_B
1627 WHERE INCIDENT_NUMBER =c_incident_number;
1628
1629 CURSOR c_get_invoice(c_incident_id NUMBER)
1630 IS
1631 SELECT inv.INCIDENT_ID,
1632 inv.LINE_NUMBER,
1633 inv.BUSINESS_PROCESS_ID,
1634 inv.TXN_BILLING_TYPE_ID,
1635 inv.INVENTORY_ITEM_ID,
1636 inv.SERIAL_NUMBER,
1637 inv.QUANTITY_REQUIRED,
1638 inv.UNIT_OF_MEASURE_CODE,
1639 inv.SELLING_PRICE,
1640 inv.AFTER_WARRANTY_COST,
1641 inv.CHARGE_LINE_TYPE,
1642 inv.BILL_TO_PARTY_ID
1643 FROM CS_ESTIMATE_DETAILS inv
1644 WHERE INV.ORIGINAL_SOURCE_CODE = 'SR'
1645 AND inv.INCIDENT_ID = c_incident_id ;
1646
1647 l_xml CLOB;
1648 l_xml_blob BLOB;
1649 qrycontext DBMS_XMLGEN.ctxHandle;
1650 l_dest_offset NUMBER := 1;
1651 l_Src_offset NUMBER := 1;
1652 l_language NUMBER := 0;
1653 l_warning NUMBER := 0;
1654 l_access_id NUMBER;
1655 l_mark_dirty BOOLEAN;
1656 l_rs_access_id NUMBER;
1657 l_SQL_TEXT VARCHAR2(4000);
1658 g_pub_item_qres VARCHAR2(30) := 'CSM_QUERY_RESULTS';
1659 l_sqlerrno VARCHAR2(200);
1660 l_sqlerrmsg VARCHAR2(4000);
1661 l_error_msg VARCHAR2(4000);
1662 l_error_status VARCHAR2(200);
1663 l_INCIDENT_ID NUMBER;
1664 l_USER_ID NUMBER;
1665 l_QUERY_ID NUMBER;
1666 l_INSTANCE_ID NUMBER;
1667 l_INCIDENT_NUM VARCHAR2(64);
1668
1669 BEGIN
1670 CSM_UTIL_PKG.LOG('Entering CSM_SR_EVENT_PKG.GET_PROFORMA_INVOICE ',
1671 'CSM_SR_EVENT_PKG.GET_PROFORMA_INVOICE',FND_LOG.LEVEL_PROCEDURE);
1672
1673 IF (funcmode <> 'RUN')
1674 THEN
1675 RETURN;
1676 END IF;
1677
1678 l_USER_ID := wf_engine.getitemattrnumber(itemtype => itemtype, itemkey => itemkey, aname => 'USER_ID');
1679 l_QUERY_ID := wf_engine.getitemattrnumber(itemtype => itemtype, itemkey => itemkey, aname => 'QUERY_ID');
1680 l_INSTANCE_ID := wf_engine.getitemattrnumber(itemtype => itemtype, itemkey => itemkey, aname => 'INSTANCE_ID');
1681 l_INCIDENT_NUM := wf_engine.getitemattrtext(itemtype => itemtype, itemkey => itemkey, aname => 'SR_NUMBER');
1682
1683 OPEN c_get_sr_id (l_INCIDENT_NUM);
1684 FETCH c_get_sr_id INTO l_INCIDENT_ID;
1685 CLOSE c_get_sr_id;
1686
1687 IF l_INCIDENT_ID IS NULL THEN
1688 CSM_UTIL_PKG.LOG('The Given SR number is Invalid : ' || l_INCIDENT_NUM,
1689 'CSM_SR_EVENT_PKG.GET_PROFORMA_INVOICE',FND_LOG.LEVEL_PROCEDURE);
1690 RETURN;
1691 END IF;
1692
1693
1694 l_SQL_TEXT := 'SELECT inv.INCIDENT_ID, inv.LINE_NUMBER, inv.BUSINESS_PROCESS_ID,
1695 inv.TXN_BILLING_TYPE_ID, inv.INVENTORY_ITEM_ID, inv.SERIAL_NUMBER,
1696 inv.QUANTITY_REQUIRED, inv.UNIT_OF_MEASURE_CODE, inv.SELLING_PRICE,
1697 inv.AFTER_WARRANTY_COST, inv.CHARGE_LINE_TYPE, inv.BILL_TO_PARTY_ID
1698 FROM CS_ESTIMATE_DETAILS inv WHERE INV.ORIGINAL_SOURCE_CODE = ''SR''
1699 AND inv.INCIDENT_ID = ' || l_INCIDENT_ID ;
1700 --Execute the SQL query
1701 qrycontext := DBMS_XMLGEN.newcontext(l_SQL_TEXT) ;
1702
1703 l_xml := DBMS_XMLGEN.getxml (qrycontext);
1704
1705 IF DBMS_LOB.GETLENGTH(l_xml) > 0 THEN
1706 --Convert the XML output into BLOB and store it in the DB
1707 dbms_lob.createtemporary(l_xml_blob,TRUE);
1708 DBMS_LOB.convertToBlob(l_xml_blob,l_xml,DBMS_LOB.LOBMAXSIZE,
1709 l_dest_offset,l_src_offset,DBMS_LOB.default_csid,l_language,l_warning);
1710
1711 CSM_QUERY_PKG.INSERT_RESULT
1712 ( p_USER_ID => l_USER_ID,
1713 p_QUERY_ID => l_QUERY_ID,
1714 p_INSTANCE_ID => l_INSTANCE_ID,
1715 p_QUERY_RESULT => l_xml_blob,
1716 p_commit => fnd_api.G_FALSE,
1717 x_return_status => l_error_status,
1718 x_error_message => l_error_msg
1719 );
1720 END IF;
1721
1722 IF l_error_status = FND_API.G_RET_STS_SUCCESS THEN
1723 COMMIT;
1724 RESULT := 'COMPLETE:Y';
1725 CSM_UTIL_PKG.LOG('Proforma Invoice Generated sucessfully for Incident Number : ' || l_INCIDENT_NUM,
1726 'CSM_SR_EVENT_PKG.GET_PROFORMA_INVOICE',FND_LOG.LEVEL_EXCEPTION);
1727
1728 ELSE
1729 CSM_UTIL_PKG.LOG('ERROR IN CSM_SR_EVENT_PKG.GET_PROFORMA_INVOICE' || l_error_msg, 'CSM_SR_EVENT_PKG.GET_PROFORMA_INVOICE',FND_LOG.LEVEL_EXCEPTION);
1730 ROLLBACK;
1731 RESULT := 'COMPLETE:N';
1732 END IF;
1733
1734 EXCEPTION
1735 WHEN OTHERS THEN
1736 l_sqlerrno := to_char(SQLCODE);
1737 l_sqlerrmsg := substr(SQLERRM, 1,2000);
1738 l_error_msg := ' Exception in GET_PROFORMA_INVOICE for Incident Number:' || to_char(l_INCIDENT_NUM)
1739 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
1740 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_EVENT_PKG.GET_PROFORMA_INVOICE',FND_LOG.LEVEL_EXCEPTION);
1741 ROLLBACK;
1742 RESULT := 'COMPLETE:N';
1743 END GET_PROFORMA_INVOICE;
1744
1745 END CSM_SR_EVENT_PKG;