[Home] [Help]
PACKAGE BODY: APPS.CSM_NOTES_EVENT_PKG
Source
1 PACKAGE BODY CSM_NOTES_EVENT_PKG AS
2 /* $Header: csmenotb.pls 120.3 2006/09/19 10:58:38 saradhak noship $ */
3
4 -- To modify this template, edit file PKGBODY.TXT in TEMPLATE
5 -- directory of SQL Navigator
6 --
7 -- Purpose: Briefly explain the functionality of the package body
8 --
9 -- MODIFICATION HISTORY
10 -- Person Date Comments
11 -- --------- ------ ------------------------------------------
12 -- Enter procedure, function bodies as shown below
13
14 l_markdirty_failed EXCEPTION;
15
16 g_notes_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_ACC';
17 g_notes_table_name CONSTANT VARCHAR2(30) := 'JTF_NOTES_B';
18 g_notes_seq_name CONSTANT VARCHAR2(30) := 'CSM_NOTES_ACC_S';
19 g_notes_pk1_name CONSTANT VARCHAR2(30) := 'JTF_NOTE_ID';
20 g_notes_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
21 CSM_ACC_PKG.t_publication_item_list('CSF_M_NOTES');
22
23 g_omappings_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_OBJECT_MAPPINGS_ACC';
24 g_omappings_table_name CONSTANT VARCHAR2(30) := 'JTF_OBJECT_MAPPINGS';
25 g_omappings_seq_name CONSTANT VARCHAR2(30) := 'CSM_OBJECT_MAPPINGS_ACC_S';
26 g_omappings_pk1_name CONSTANT VARCHAR2(30) := 'MAPPING_ID';
27 g_omappings_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
28 CSM_ACC_PKG.t_publication_item_list('CSF_M_OBJECT_MAPPINGS');
29
30 PROCEDURE INSERT_CSM_NOTES_ACC (p_jtf_note_id jtf_notes_b.jtf_note_id%TYPE,
31 p_user_id fnd_user.user_id%TYPE)
32 IS
33
34 BEGIN
35
36 CSM_ACC_PKG.Insert_Acc
37 ( P_PUBLICATION_ITEM_NAMES => g_notes_pubi_name
38 ,P_ACC_TABLE_NAME => g_notes_acc_table_name
39 ,P_SEQ_NAME => g_notes_seq_name
40 ,P_PK1_NAME => g_notes_pk1_name
41 ,P_PK1_NUM_VALUE => p_jtf_note_id
42 ,P_USER_ID => p_user_id
43 );
44
45
46 EXCEPTION WHEN OTHERS THEN
47 CSM_UTIL_PKG.LOG( sqlerrm|| ' for PK ' || to_char(p_jtf_note_id),
48 'CSM_NOTES_EVENT_PKG.INSERT_CSM_NOTES_ACC',FND_LOG.LEVEL_EXCEPTION);
49 RAISE;
50
51 END;-- end INSERT_CSM_NOTES_ACC;
52
53 PROCEDURE INSERT_CSM_OBJECT_MAPPINGS_ACC (p_access_id IN NUMBER,
54 p_mapping_id jtf_object_mappings.mapping_id%TYPE
55 )
56 IS
57 l_sysdate date;
58 BEGIN
59 l_sysdate := SYSDATE;
60
61 INSERT INTO csm_object_mappings_acc (access_id,
62 mapping_id,
63 created_by,
64 creation_date,
65 last_updated_by,
66 last_update_date,
67 last_update_login
68 )
69 VALUES (p_access_id,
70 p_mapping_id,
71 fnd_global.user_id,
72 l_sysdate,
73 fnd_global.user_id,
74 l_sysdate,
75 fnd_global.login_id
76 );
77
78 EXCEPTION
79 WHEN others THEN
80 RAISE;
81
82 END;-- end INSERT_CSM_OBJECT_MAPPINGS_ACC;
83
84 -- Bug 5532961
85 PROCEDURE NOTES_MAKE_DIRTY_I_FOREACHUSER(p_jtf_note_id IN NUMBER,
86 p_error_msg OUT NOCOPY VARCHAR2,
87 x_return_status IN OUT NOCOPY VARCHAR2)
88 IS
89 --Variable Declarations
90 l_jtf_note_id jtf_notes_b.jtf_note_id%TYPE;
91 l_user_found boolean;
92 l_source_object_code jtf_notes_b.source_object_code%TYPE;
93 l_userlist asg_download.user_list;
94 l_countlist asg_download.user_list;
95 l_source_object_id jtf_notes_b.source_object_id%TYPE;
96
97
98 --Cursor Declarations
99 CURSOR c_csm_notes_csr (c_jtf_note_id jtf_notes_b.jtf_note_id%TYPE)
100 IS
101 SELECT source_object_code,
102 source_object_id
103 FROM jtf_notes_b jtn
104 WHERE jtn.jtf_note_id = c_jtf_note_id;
105
106 CURSOR c_sr_notes(c_source_object_id jtf_notes_b.source_object_id%TYPE)
107 IS
108 SELECT acc.user_id,acc.counter
109 FROM csm_incidents_all_acc acc
110 WHERE acc.incident_id = c_source_object_id;
111
112 CURSOR c_task_notes(c_source_object_id jtf_notes_b.source_object_id%TYPE)
113 IS
114 SELECT acc.user_id,acc.counter
115 FROM csm_tasks_acc acc
116 WHERE acc.task_id = c_source_object_id;
117
118 CURSOR c_ib_notes(c_source_object_id jtf_notes_b.source_object_id%TYPE)
119 IS
120 SELECT acc.user_id,acc.counter
121 FROM CSM_ITEM_INSTANCES_ACC acc
122 WHERE acc.INSTANCE_ID = c_source_object_id;
123
124 CURSOR c_cst_notes(c_source_object_id jtf_notes_b.source_object_id%TYPE)
125 IS
126 SELECT acc.user_id,acc.counter
127 FROM CSM_PARTIES_ACC acc
128 WHERE acc.PARTY_ID = c_source_object_id;
129
130 CURSOR c_contract_notes(c_source_object_id jtf_notes_b.source_object_id%TYPE)
131 IS
132 SELECT DISTINCT acc.user_id,acc.counter --distinct not removed as there is no primary key with contract service id
133 FROM csm_contr_headers_acc acc
134 WHERE acc.contract_service_id = c_source_object_id;
135
136 CURSOR c_dbheader_notes(c_source_object_id jtf_notes_b.source_object_id%TYPE)
137 IS
138 SELECT acc.user_id,acc.counter
139 FROM csm_debrief_headers_acc acc
140 WHERE acc.debrief_header_id = c_source_object_id;
141
142
143 BEGIN
144 x_return_status := FND_API.G_RET_STS_SUCCESS;
145 CSM_UTIL_PKG.LOG('Entering CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_FOREACHUSER ',
146 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_FOREACHUSER',FND_LOG.LEVEL_PROCEDURE);
147
148 l_user_found := false;
149 l_jtf_note_id := p_jtf_note_id;
150 --bug 5104453
151 OPEN c_csm_notes_csr(p_jtf_note_id);
152 FETCH c_csm_notes_csr INTO l_source_object_code,l_source_object_id;
153 CLOSE c_csm_notes_csr;
154
155 IF l_source_object_code='SR' THEN
156 OPEN c_sr_notes(l_source_object_id);
157 FETCH c_sr_notes BULK COLLECT INTO l_userlist,l_countlist;
158 CLOSE c_sr_notes;
159
160 ELSIF l_source_object_code='TASK' THEN
161 OPEN c_task_notes(l_source_object_id);
162 FETCH c_task_notes BULK COLLECT INTO l_userlist,l_countlist;
163 CLOSE c_task_notes;
164
165 ELSIF l_source_object_code='CP' THEN
166 OPEN c_ib_notes(l_source_object_id);
167 FETCH c_ib_notes BULK COLLECT INTO l_userlist,l_countlist;
168 CLOSE c_ib_notes;
169
170 ELSIF l_source_object_code='PARTY' THEN
171 OPEN c_cst_notes(l_source_object_id);
172 FETCH c_cst_notes BULK COLLECT INTO l_userlist,l_countlist;
173 CLOSE c_cst_notes;
174
175 ELSIF l_source_object_code='OKS_COV_NOTE' THEN
176 OPEN c_contract_notes(l_source_object_id);
177 FETCH c_contract_notes BULK COLLECT INTO l_userlist,l_countlist;
178 CLOSE c_contract_notes;
179
180 ELSIF l_source_object_code='SD' THEN
181 OPEN c_dbheader_notes(l_source_object_id);
182 FETCH c_dbheader_notes BULK COLLECT INTO l_userlist,l_countlist;
183 CLOSE c_dbheader_notes;
184
185 END IF;
186 -- insert for all the affected users
187 FOR i IN 1..l_userlist.COUNT
188 LOOP
189 l_user_found := true;
190 -- insert into csm_notes_acc table
191 insert_csm_notes_acc (l_jtf_note_id, l_userlist(i));
192 --Bug 5532961
193 update csm_notes_acc set counter=l_countlist(i) where jtf_note_id=l_jtf_note_id and user_id=l_userlist(i);
194 END LOOP;
195
196 IF l_userlist.COUNT >0 THEN
197 l_userlist.DELETE;
198 END IF;
199 --bug 5104453
200
201
202 IF l_user_found THEN
203 p_error_msg := ' CMPLT JtfNoteId:' || to_char(l_jtf_note_id);
204 ELSE
205 p_error_msg := ' No User for JtfNoteId: ' || to_char(l_jtf_note_id) ;
206 END IF;
207
208 CSM_UTIL_PKG.LOG('Leaving CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_FOREACHUSER ',
209 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_FOREACHUSER',FND_LOG.LEVEL_PROCEDURE);
210
211 EXCEPTION
212 WHEN others THEN
213 x_return_status := FND_API.G_RET_STS_ERROR;
214 p_error_msg := ' FAILED NOTES_MAKE_DIRTY_I_FOREACHUSER:' || to_char(p_jtf_note_id);
215 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_FOREACHUSER',FND_LOG.LEVEL_EXCEPTION);
216 END NOTES_MAKE_DIRTY_I_FOREACHUSER;
217
218 PROCEDURE NOTES_MAKE_DIRTY_U_FOREACHUSER(p_jtf_note_id IN NUMBER,
219 p_error_msg OUT NOCOPY VARCHAR2,
220 x_return_status IN OUT NOCOPY VARCHAR2)
221 IS
222 --variable declarations
223 l_jtf_note_id jtf_notes_b.jtf_note_id%TYPE;
224 l_user_found boolean;
225
226 --cursor declarations
227 CURSOR l_csm_notes_foreachuser_csr (p_jtf_note_id jtf_notes_b.jtf_note_id%TYPE) IS
228 SELECT acc.user_id,
229 acc.access_id
230 FROM csm_notes_acc acc
231 WHERE acc.jtf_note_id = p_jtf_note_id;
232
233 BEGIN
234 x_return_status := FND_API.G_RET_STS_SUCCESS;
235 CSM_UTIL_PKG.LOG('Entering CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_U_FOREACHUSER ',
236 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_U_FOREACHUSER',FND_LOG.LEVEL_PROCEDURE);
237
238 l_user_found := false;
239 l_jtf_note_id := p_jtf_note_id;
240
241 -- update for all the affected users
242 for l_csm_notes_foreachuser_rec in l_csm_notes_foreachuser_csr(l_jtf_note_id) loop
243 CSM_ACC_PKG.Update_Acc
244 ( P_PUBLICATION_ITEM_NAMES => g_notes_pubi_name
245 ,P_ACC_TABLE_NAME => g_notes_acc_table_name
246 ,P_USER_ID => l_csm_notes_foreachuser_rec.user_id
247 ,p_ACCESS_ID => l_csm_notes_foreachuser_rec.access_id
248 );
249 l_user_found := TRUE;
250 end loop;
251
252 if l_user_found then
253 p_error_msg := ' COMP Note_id:' || to_char(l_jtf_note_id);
254 else
255 p_error_msg := ' No Users for JtfNoteId : ' || to_char(l_jtf_note_id);
256 end if;
257
258 CSM_UTIL_PKG.LOG('Leaving CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_U_FOREACHUSER ',
259 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_U_FOREACHUSER',FND_LOG.LEVEL_PROCEDURE);
260
261 EXCEPTION
262 WHEN others THEN
263 x_return_status := FND_API.G_RET_STS_ERROR;
264 p_error_msg := ' FAILED NOTES_MAKE_DIRTY_U_FOREACHUSER:' || to_char(p_jtf_note_id);
265 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_U_FOREACHUSER',FND_LOG.LEVEL_EXCEPTION);
266
267 END NOTES_MAKE_DIRTY_U_FOREACHUSER;
268
269 PROCEDURE NOTES_MAKE_DIRTY_I_GRP(p_sourceobjectcode IN VARCHAR2,
270 p_sourceobjectid IN NUMBER,
271 p_userid IN NUMBER,
272 p_error_msg OUT NOCOPY VARCHAR2,
273 x_return_status IN OUT NOCOPY VARCHAR2)
274 IS
275 CURSOR l_notes_by_task_csr (p_sourceobjectcode VARCHAR2,
276 p_sourceobjectid NUMBER) IS
277 SELECT jtf_note_id
278 FROM jtf_notes_b
279 WHERE source_object_code = p_sourceobjectcode
280 AND source_object_id = p_sourceobjectid;
281
282 BEGIN
283 x_return_status := FND_API.G_RET_STS_SUCCESS;
284 CSM_UTIL_PKG.LOG('Entering CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_GRP ',
285 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_GRP',FND_LOG.LEVEL_PROCEDURE);
286
287 FOR l_notes_by_task_rec in l_notes_by_task_csr(p_sourceobjectcode,
288 p_sourceobjectid) LOOP
289
290 -- insert into csm_notes_acc table
291 insert_csm_notes_acc (l_notes_by_task_rec.jtf_note_id, p_userid);
292 END LOOP;
293
294 CSM_UTIL_PKG.LOG('Leaving CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_GRP ',
295 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_GRP',FND_LOG.LEVEL_PROCEDURE);
296
297 EXCEPTION
298 WHEN others THEN
299 x_return_status := FND_API.G_RET_STS_ERROR;
300 p_error_msg := ' FAILED NOTES_MAKE_DIRTY_I_GRP for ' || p_sourceobjectcode || ':' || to_char(p_sourceobjectid);
301 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_GRP',FND_LOG.LEVEL_EXCEPTION);
302
303 END NOTES_MAKE_DIRTY_I_GRP;
304
305 PROCEDURE NOTES_MAKE_DIRTY_D_GRP(p_sourceobjectcode IN VARCHAR2,
306 p_sourceobjectid IN NUMBER,
307 p_userid IN NUMBER,
308 p_error_msg OUT NOCOPY VARCHAR2,
309 x_return_status IN OUT NOCOPY VARCHAR2)
310 IS
311 CURSOR l_notes_by_task_csr (p_source_object_code VARCHAR2,
312 p_source_object_id NUMBER,
313 p_user_id NUMBER) IS
314 SELECT acc.jtf_note_id, acc.user_id
315 FROM jtf_notes_b notes, csm_notes_acc acc
316 WHERE notes.source_object_code = p_source_object_code
317 AND notes.source_object_id = p_source_object_id
318 AND notes.jtf_note_id = acc.jtf_note_id
319 AND acc.user_id = p_user_id;
320
321 BEGIN
322 x_return_status := FND_API.G_RET_STS_SUCCESS;
323 CSM_UTIL_PKG.LOG('Entering CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_D_GRP ',
324 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_D_GRP',FND_LOG.LEVEL_PROCEDURE);
325
326 FOR l_notes_by_task_rec IN l_notes_by_task_csr(p_sourceobjectcode,
327 p_sourceobjectid,
328 p_userid) LOOP
329
330 CSM_ACC_PKG.Delete_Acc
331 ( P_PUBLICATION_ITEM_NAMES => g_notes_pubi_name
332 ,P_ACC_TABLE_NAME => g_notes_acc_table_name
333 ,P_PK1_NAME => g_notes_pk1_name
334 ,P_PK1_NUM_VALUE => l_notes_by_task_rec.jtf_note_id
335 ,P_USER_ID => l_notes_by_task_rec.user_id
336 );
337
338 END LOOP;
339
340 CSM_UTIL_PKG.LOG('Leaving CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_D_GRP ',
341 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_D_GRP',FND_LOG.LEVEL_PROCEDURE);
342
343 EXCEPTION
344 WHEN others THEN
345 x_return_status := FND_API.G_RET_STS_ERROR;
346 p_error_msg := ' FAILED NOTES_MAKE_DIRTY_D_GRP for ' || p_sourceobjectcode || ':' || to_char(p_sourceobjectid);
347 CSM_UTIL_PKG.LOG(p_error_msg, 'CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_D_GRP',FND_LOG.LEVEL_EXCEPTION);
348 END NOTES_MAKE_DIRTY_D_GRP;
349
350 PROCEDURE OBJECT_MAPPINGS_ACC_PROCESSOR
351 IS
352 l_sqlerrno VARCHAR2(20);
353 l_sqlerrmsg VARCHAR2(4000);
354 l_error_msg VARCHAR2(4000);
355 l_return_status VARCHAR2(2000);
356 l_markdirty BOOLEAN;
357 l_omfs_palm_user_list asg_download.user_list;
358 l_null_user_list asg_download.user_list;
359 l_single_access_id_list asg_download.access_list;
360 l_null_access_list asg_download.access_list;
361
362 CURSOR l_mappings_csr
363 IS
364 SELECT CSM_OBJECT_MAPPINGS_ACC_S.NEXTVAL access_id, jom.mapping_id
365 FROM jtf_object_mappings jom
366 WHERE jom.source_object_code IN ('PARTY', 'TASK', 'SR', 'CP','OKS_COV_NOTE','SD')
367 AND NVL(end_date, SYSDATE) >= SYSDATE
368 AND NOT EXISTS
369 (SELECT 1
370 FROM csm_object_mappings_acc acc
371 WHERE acc.mapping_id = jom.mapping_id);
372
373 BEGIN
374 --get mfs users
375 l_omfs_palm_user_list := l_null_user_list;
376 l_omfs_palm_user_list := csm_util_pkg.get_all_omfs_palm_user_list;
377
378 -- insert into csm_object_mappings_acc
379 FOR r_mappings_rec IN l_mappings_csr LOOP
380
381 --nullify the access list
382 l_single_access_id_list := l_null_access_list;
383 FOR i in 1 .. l_omfs_palm_user_list.COUNT LOOP
384 l_single_access_id_list(i) := r_mappings_rec.access_id;
385 END LOOP;
386
387 INSERT_CSM_OBJECT_MAPPINGS_ACC(r_mappings_rec.access_id, r_mappings_rec.mapping_id);
388
389 --mark dirty the SDQ for all users
390 IF l_single_access_id_list.count > 0 THEN
391 l_markdirty := CSM_UTIL_PKG.MakeDirtyForUser('CSF_M_OBJECT_MAPPINGS',
392 l_single_access_id_list, l_omfs_palm_user_list,
393 ASG_DOWNLOAD.INS, sysdate);
394 END IF;
395 END LOOP;
396
397 EXCEPTION
398 WHEN OTHERS THEN
399 l_sqlerrno := to_char(SQLCODE);
400 l_sqlerrmsg := substr(SQLERRM, 1,2000);
401 l_error_msg := ' Exception in OBJECT_MAPPINGS_ACC_PROCESSOR ' || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
402 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_NOTES_EVENT_PKG.OBJECT_MAPPINGS_ACC_PROCESSOR',FND_LOG.LEVEL_EXCEPTION);
403 RAISE;
404 END OBJECT_MAPPINGS_ACC_PROCESSOR;
405
406 END CSM_NOTES_EVENT_PKG;