[Home] [Help]
PACKAGE BODY: APPS.CSM_PARTY_EVENT_PKG
Source
1 PACKAGE BODY CSM_PARTY_EVENT_PKG AS
2 /* $Header: csmeptyb.pls 120.2 2011/01/10 10:37:24 saradhak ship $ */
3
4 g_table_name1 CONSTANT VARCHAR2(30) := 'HZ_PARTIES';
5 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_PARTIES_ACC';
6 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_PARTIES_ACC_S';
7 g_publication_item_name1 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
8 CSM_ACC_PKG.t_publication_item_list('CSF_M_PARTIES');
9 g_pk1_name1 CONSTANT VARCHAR2(30) := 'PARTY_ID';
10
11 g_pub_item CONSTANT varchar(30) := 'CSF_M_PARTIES';
12
13 g_table_name2 CONSTANT VARCHAR2(30) := 'HZ_PARTY_SITES';
14 g_acc_table_name2 CONSTANT VARCHAR2(30) := 'CSM_PARTY_SITES_ACC';
15 g_acc_sequence_name2 CONSTANT VARCHAR2(30) := 'CSM_PARTY_SITES_ACC_S';
16 g_publication_item_name2 CONSTANT CSM_ACC_PKG.t_publication_item_list :=
17 CSM_ACC_PKG.t_publication_item_list('CSF_M_PARTY_SITES');
18 g_pk1_name2 CONSTANT VARCHAR2(30) := 'PARTY_SITE_ID';
19 g_pk2_name2 CONSTANT VARCHAR2(30) := 'PARTY_ID';
20
21 PROCEDURE INSERT_PARTIES_ACC (p_party_id hz_parties.party_id%TYPE,
22 p_user_id fnd_user.user_id%TYPE,
23 x_access_id OUT NOCOPY number)
24 IS
25 l_sysdate date;
26 BEGIN
27 l_sysdate := SYSDATE;
28
29 INSERT INTO csm_parties_acc (party_id,
30 user_id,
31 created_by,
32 creation_date,
33 last_updated_by,
34 last_update_date,
35 last_update_login,
36 access_id,
37 counter
38 )
39 VALUES (p_party_id,
40 p_user_id,
41 fnd_global.user_id,
42 l_sysdate,
43 fnd_global.user_id,
44 l_sysdate,
45 fnd_global.login_id,
46 csm_parties_acc_s.nextval,
47 1
48 )
49 RETURNING access_id INTO x_access_id;
50
51 EXCEPTION
52 WHEN others THEN
53 RAISE;
54
55 END INSERT_PARTIES_ACC;
56
57 PROCEDURE PARTY_ACC_I (p_party_id IN NUMBER,
58 p_user_id IN NUMBER,
59 p_flowtype IN VARCHAR2,
60 p_error_msg OUT NOCOPY VARCHAR2,
61 x_return_status IN OUT NOCOPY VARCHAR2
62 )
63 IS
64 l_err_msg VARCHAR2(4000);
65 l_ret_status VARCHAR2(4000);
66
67 BEGIN
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69 p_error_msg := 'Entering CSM_PARTY_EVENT_PKG.PARTY_ACC_I' || ' for party_id ' || to_char(p_party_id);
70
71 CSM_ACC_PKG.Insert_Acc
72 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
73 ,P_ACC_TABLE_NAME => g_acc_table_name1
74 ,P_SEQ_NAME => g_acc_sequence_name1
75 ,P_PK1_NAME => g_pk1_name1
76 ,P_PK1_NUM_VALUE => p_party_id
77 ,P_USER_ID => p_user_id
78 );
79
80 -- if not history, get notes for customer
81 IF NOT csm_util_pkg.is_flow_history(p_flowtype) THEN
82 CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_I_GRP(p_sourceobjectcode => 'PARTY',
83 p_sourceobjectid => p_party_id,
84 p_userid => p_user_id,
85 p_error_msg => l_err_msg,
86 x_return_status => l_ret_status
87 );
88
89 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
90 csm_util_pkg.LOG(l_err_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_I', FND_LOG.LEVEL_ERROR);
91 END IF;
92 END IF;
93
94 p_error_msg := 'Leaving CSM_PARTY_EVENT_PKG.PARTY_ACC_I' || ' for party_id ' || to_char(p_party_id);
95 EXCEPTION
96 WHEN others THEN
97 x_return_status := FND_API.G_RET_STS_ERROR;
98 p_error_msg := ' FAILED PARTY_ACC_I:' || to_char(p_party_id);
99 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_I', FND_LOG.LEVEL_EXCEPTION);
100 RAISE;
101
102 END PARTY_ACC_I;
103
104 PROCEDURE PARTY_ACC_D (p_party_id IN NUMBER,
105 p_user_id IN NUMBER,
106 p_flowtype IN VARCHAR2,
107 p_error_msg OUT NOCOPY VARCHAR2,
108 x_return_status IN OUT NOCOPY VARCHAR2
109 )
110 IS
111 l_err_msg VARCHAR2(4000);
112 l_ret_status VARCHAR2(4000);
113 l_ref_exists NUMBER := 0 ;
114 l_count NUMBER := 0 ;
115 /** Check ref for given party and user*/
116 CURSOR l_check_party_ref(l_party_id csm_parties_acc.party_id%TYPE,
117 l_user_id csm_parties_acc.user_id%TYPE) IS
118 SELECT 1
119 FROM csm_parties_acc p
120 WHERE p.party_id = l_party_id
121 AND p.user_id = l_user_id
122 AND NOT EXISTS
123 (
124 SELECT 1
125 FROM csm_incidents_all_acc acc,
126 CS_INCIDENTS_ALL_B csi
127 WHERE acc.incident_id = csi.incident_id
128 AND acc.user_id = p.user_id
129 AND csi.customer_id = p.party_id
130 )
131 AND NOT EXISTS
132 (
133 SELECT 1
134 From CSM_PARTY_SITES_ACC ps
135 WHERE ps.USER_ID = p.USER_ID
136 AND ps.PARTY_ID= p.PARTY_ID
137 )
138 AND NOT EXISTS
139 (
140 SELECT 1
141 From CS_HZ_SR_CONTACT_POINTS cont,
142 CSM_SR_CONTACTS_ACC src
143 WHERE src.USER_ID = p.USER_ID
144 AND cont.PARTY_ID = p.PARTY_ID
145 AND src.sr_contact_point_id = cont.sr_contact_point_id
146 );
147
148 CURSOR c_get_party_count(l_party_id csm_parties_acc.party_id%TYPE,
149 l_user_id csm_parties_acc.user_id%TYPE)
150 IS
151 SELECT count(*)
152 FROM csm_parties_acc
153 WHERE party_id = l_party_id
154 AND user_id = l_user_id;
155
156 BEGIN
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158 p_error_msg := 'Entering CSM_PARTY_EVENT_PKG.PARTY_ACC_D' || ' for party_id ' || to_char(p_party_id);
159
160 OPEN c_get_party_count(p_party_id, p_user_id) ;
161 FETCH c_get_party_count INTO l_count ;
162 CLOSE c_get_party_count;
163
164 IF l_count >1 THEN
165 CSM_ACC_PKG.Delete_Acc
166 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
167 ,P_ACC_TABLE_NAME => g_acc_table_name1
168 ,P_PK1_NAME => g_pk1_name1
169 ,P_PK1_NUM_VALUE => p_party_id
170 ,P_USER_ID => p_user_id
171 );
172
173 -- delete notes if flow is not history
174 IF NOT (csm_util_pkg.is_flow_history(p_flowtype)) THEN
175 CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_D_GRP(p_sourceobjectcode => 'PARTY',
176 p_sourceobjectid => p_party_id,
177 p_userid => p_user_id,
178 p_error_msg => l_err_msg,
179 x_return_status => l_ret_status
180 );
181
182 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
183 csm_util_pkg.LOG(l_err_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_D', FND_LOG.LEVEL_ERROR);
184 END IF;
185 END IF;
186 ELSIF l_count = 1 THEN
187 OPEN l_check_party_ref(p_party_id, p_user_id) ;
188 FETCH l_check_party_REF INTO l_REF_EXISTS ;
189 IF l_check_party_REF%NOTFOUND THEN
190 l_ref_exists := 0 ;
191 END IF ;
192 CLOSE l_check_party_ref ;
193 IF l_ref_exists = 1 THEN
194 CSM_ACC_PKG.Delete_Acc
195 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
196 ,P_ACC_TABLE_NAME => g_acc_table_name1
197 ,P_PK1_NAME => g_pk1_name1
198 ,P_PK1_NUM_VALUE => p_party_id
199 ,P_USER_ID => p_user_id
200 );
201
202 -- delete notes if flow is not history
203 IF NOT (csm_util_pkg.is_flow_history(p_flowtype)) THEN
204 CSM_NOTES_EVENT_PKG.NOTES_MAKE_DIRTY_D_GRP(p_sourceobjectcode => 'PARTY',
205 p_sourceobjectid => p_party_id,
206 p_userid => p_user_id,
207 p_error_msg => l_err_msg,
208 x_return_status => l_ret_status
209 );
210
211 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
212 csm_util_pkg.LOG(l_err_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_D', FND_LOG.LEVEL_ERROR);
213 END IF;
214 END IF;
215 END IF;
216 END IF;
217 -- commented the above..as such cases should not occur
218 p_error_msg := 'Leaving CSM_PARTY_EVENT_PKG.PARTY_ACC_D' || ' for party_id ' || to_char(p_party_id);
219 EXCEPTION
220 WHEN others THEN
221 x_return_status := FND_API.G_RET_STS_ERROR;
222 p_error_msg := ' FAILED PARTY_ACC_D:' || to_char(p_party_id);
223 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ACC_D', FND_LOG.LEVEL_EXCEPTION);
224 RAISE;
225
226 END PARTY_ACC_D;
227
228 FUNCTION PARTY_ORG_UPD_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
229 RETURN VARCHAR2
230 IS
231 l_sqlerrno VARCHAR2(20);
232 l_sqlerrmsg VARCHAR2(4000);
233 l_error_msg VARCHAR2(4000);
234 l_return_status VARCHAR2(2000);
235
236 l_party_id hz_parties.party_id%TYPE;
237 l_party_site_id hz_party_sites.party_site_id%TYPE;
238
239 CURSOR l_parties_csr (p_party_id hz_parties.party_id%TYPE)
240 IS
241 SELECT access_id, user_id
242 FROM csm_parties_acc
243 WHERE party_id = p_party_id;
244
245 CURSOR l_party_sites_csr (p_party_id hz_parties.party_id%TYPE)
246 IS
247 SELECT access_id, user_id
248 FROM csm_party_sites_acc
249 WHERE party_id = p_party_id;
250
251 BEGIN
252 CSM_UTIL_PKG.LOG('Entering PARTY_ORG_UPD_WF_EVENT_SUB',
253 'CSM_PARTY_EVENT_PKG.PARTY_ORG_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
254
255 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
256 RETURN 'SUCCESS';
257 END IF;
258
259 l_party_id := p_event.GetValueForParameter('PARTY_ID');
260
261 -- get users who have access to this PARTY_ID
262 FOR r_parties_rec IN l_parties_csr(l_party_id) LOOP
263 -- Call Update
264 CSM_ACC_PKG.Update_Acc
265 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
266 ,P_ACC_TABLE_NAME => g_acc_table_name1
267 ,P_USER_ID => r_parties_rec.user_id
268 ,P_ACCESS_ID => r_parties_rec.access_id
269 );
270 END LOOP;
271
272 -- update party_sites pub item
273 FOR r_party_sites_rec IN l_party_sites_csr(l_party_id) LOOP
274 -- Call Update
275 CSM_ACC_PKG.Update_Acc
276 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name2
277 ,P_ACC_TABLE_NAME => g_acc_table_name2
278 ,P_USER_ID => r_party_sites_rec.user_id
279 ,P_ACCESS_ID => r_party_sites_rec.access_id
280 );
281 END LOOP;
282
283 CSM_UTIL_PKG.LOG('Leaving PARTY_ORG_UPD_WF_EVENT_SUB for party_id: ' || TO_CHAR(l_party_id),
284 'CSM_PARTY_EVENT_PKG.PARTY_ORG_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
285 RETURN 'SUCCESS';
286 EXCEPTION
287 WHEN OTHERS THEN
288 l_sqlerrno := to_char(SQLCODE);
289 l_sqlerrmsg := substr(SQLERRM, 1,2000);
290 l_error_msg := ' Exception in PARTY_ORG_UPD_WF_EVENT_SUB for party_id:' || to_char(l_party_id)
291 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
292 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_EVENT_PKG.PARTY_ORG_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
293 RETURN 'ERROR';
294 END PARTY_ORG_UPD_WF_EVENT_SUB;
295
296 --Bug 9128169
297 PROCEDURE PURGE_PARTY_CONC (p_status OUT NOCOPY VARCHAR2, p_message OUT NOCOPY VARCHAR2)
298 IS
299 PRAGMA AUTONOMOUS_TRANSACTION;
300 CURSOR l_party_csr
301 IS
302 SELECT /*+ INDEX (p CSM_PARTIES_ACC_U1) */ p.party_id,p.USER_ID
303 FROM CSM_PARTIES_ACC p
304 WHERE NOT EXISTS
305 (
306 SELECT /*+INDEX (csi CS_INCIDENTS_U1) */ 1
307 FROM csm_incidents_all_acc acc,
308 CS_INCIDENTS_ALL_B csi
309 WHERE acc.incident_id = csi.incident_id
310 AND acc.user_id=p.user_id
311 AND csi.customer_id=p.party_id
312 )
313 AND NOT EXISTS
314 (
315 SELECT 1
316 From CSM_PARTY_SITES_ACC ps
317 WHERE ps.USER_ID=p.USER_ID
318 AND ps.PARTY_ID=p.PARTY_ID
319 )
320 AND NOT EXISTS
321 (
322 SELECT 1
323 From CS_HZ_SR_CONTACT_POINTS cont,
324 CSM_SR_CONTACTS_ACC src
325 WHERE src.USER_ID = p.USER_ID
326 AND cont.PARTY_ID = p.PARTY_ID
327 AND src.sr_contact_point_id = cont.sr_contact_point_id
328 );
329
330 CURSOR c_get_access_ID(b_party_id NUMBER,b_user_id NUMBER) IS
331 SELECT /*+ INDEX (p CSM_PARTIES_ACC_U1) */
332 P.ACCESS_ID
333 FROM CSM_PARTIES_ACC P
334 WHERE P.PARTY_ID = b_party_id
335 AND P.USER_ID = b_user_id;
336
337 l_sqlerrno VARCHAR2(20);
338 l_sqlerrmsg VARCHAR2(4000);
339 l_error_msg VARCHAR2(4000);
340 l_return_status VARCHAR2(2000);
341 l_rc BOOLEAN;
342
343 TYPE num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
344
345 l_partyid_tbl num_tab;
346 l_userid_tbl asg_download.user_list;
347 l_accessid_tbl asg_download.access_list;
348
349 BEGIN
350
351 OPEN l_party_csr;
352 LOOP
353 IF l_userid_tbl.COUNT > 0 THEN
354 l_userid_tbl.DELETE;
355 END IF;
356 IF l_partyid_tbl.COUNT > 0 THEN
357 l_partyid_tbl.DELETE;
358 END IF;
359
360 FETCH l_party_csr BULK COLLECT INTO l_partyid_tbl,l_userid_tbl LIMIT 100;
361
362 FOR I IN 1..l_partyid_tbl.COUNT LOOP
363 OPEN c_get_access_ID(l_partyid_tbl(I),l_userid_tbl(I));
364 FETCH c_get_access_ID INTO l_accessid_tbl(I);
365 CLOSE c_get_access_ID;
366 END LOOP;
367
368 CSM_UTIL_PKG.LOG(TO_CHAR(l_accessid_tbl.COUNT) || ' records sent for purge', 'CSM_PARTY_EVENT_PKG.PURGE_PARTY_CONC',FND_LOG.LEVEL_EVENT);
369
370 --calling MARK-DIRTY
371 l_rc := asg_download.mark_dirty( 'CSF_M_PARTIES', l_accessid_tbl
372 , l_userid_tbl, 'D', sysdate,true );
373
374 -- doing PARTY delete
375 FORALL I IN 1..l_accessid_tbl.COUNT
376 DELETE FROM CSM_PARTIES_ACC WHERE ACCESS_ID=l_accessid_tbl(I);
377 -- commit after every 100 records since mark dirty takes time when more than 100
378 COMMIT;
379 EXIT WHEN l_partyid_tbl.COUNT < 100;
380 END LOOP;
381 CLOSE l_party_csr;
382
383 --update concurrent run
384 UPDATE jtm_con_request_data
385 SET last_run_date = SYSDATE
386 WHERE product_code = 'CSM'
387 AND package_name = 'CSM_PARTY_EVENT_PKG'
388 AND procedure_name = 'PURGE_PARTY_CONC';
389
390 COMMIT;
391
392 p_status := 'SUCCESS';
393 p_message := 'CSM_PARTY_EVENT_PKG.PURGE_PARTY_CONC Executed successfully';
394
395 EXCEPTION
396 --log the error
397 WHEN OTHERS THEN
398 l_sqlerrno := to_char(SQLCODE);
399 l_sqlerrmsg := substr(SQLERRM, 1,2000);
400 ROLLBACK;
401 l_error_msg := ' Exception in PURGE_PARTY_CONC :' || l_sqlerrno || ':' || l_sqlerrmsg;
402
403 p_status := 'ERROR';
404 p_message := 'Error in CSM_PARTY_EVENT_PKG.PURGE_PARTY_CONC: ' || l_error_msg;
405 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_EVENT_PKG.PURGE_PARTY_CONC',FND_LOG.LEVEL_EVENT);
406
407 END PURGE_PARTY_CONC;
408
409 END CSM_PARTY_EVENT_PKG;