[Home] [Help]
PACKAGE BODY: APPS.CSM_SR_CONTACT_EVENT_PKG
Source
1 PACKAGE BODY CSM_SR_CONTACT_EVENT_PKG AS
2 /* $Header: csmesrcb.pls 120.1 2005/07/25 00:22:12 trajasek noship $ */
3 /*** Globals ***/
4 g_srcntacts_acc_table_name CONSTANT VARCHAR2(30) := 'CSM_SR_CONTACTS_ACC';
5 g_srcntacts_table_name CONSTANT VARCHAR2(30) := 'CS_HZ_SR_CONTACT_POINTS';
6 g_srcntacts_seq_name CONSTANT VARCHAR2(30) := 'CSM_SR_CONTACTS_ACC_S' ;
7 g_srcntacts_pk1_name CONSTANT VARCHAR2(30) := 'SR_CONTACT_POINT_ID';
8 g_srcntacts_pubi_name CONSTANT CSM_ACC_PKG.t_publication_item_list :=
9 CSM_ACC_PKG.t_publication_item_list('CSF_M_SR_CONTACTS');
10
11 l_markdirty_failed EXCEPTION;
12
13 PROCEDURE SR_CNTACT_MDIRTY_U_FOREACHUSER(p_sr_contact_point_id IN NUMBER)
14 IS
15 l_sr_contact_point_id cs_hz_sr_contact_points.sr_contact_point_id%type;
16 l_access_id NUMBER;
17 l_err_msg VARCHAR2(4000);
18 l_user_id NUMBER;
19
20 CURSOR l_user_csr (p_sr_contact_point_id cs_hz_sr_contact_points.sr_contact_point_id%type) IS
21 SELECT access_id, user_id
22 FROM csm_sr_contacts_acc
23 WHERE sr_contact_point_id = p_sr_contact_point_id;
24
25 l_user_rec l_user_csr% ROWTYPE;
26 l_sqlerrno VARCHAR2(20);
27 l_sqlerrmsg VARCHAR2(4000);
28 l_error_msg VARCHAR2(4000);
29 l_return_status VARCHAR2(2000);
30
31
32 BEGIN
33 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
34 CSM_UTIL_PKG.LOG('Entering SR_CNTACT_MDIRTY_U_FOREACHUSER for sr_contact_point_id: ' || p_sr_contact_point_id,
35 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_U_FOREACHUSER',FND_LOG.LEVEL_PROCEDURE);
36
37
38 -- l_sr_contact_point_id := p_sr_contact_point_id;
39
40 -- get users who have access to this SR_CONTACT_POINT_ID
41 FOR l_user_rec IN l_user_csr(p_sr_contact_point_id) LOOP
42
43 -- Call Update
44 CSM_ACC_PKG.Update_Acc
45 ( P_PUBLICATION_ITEM_NAMES => g_srcntacts_pubi_name
46 ,P_ACC_TABLE_NAME => g_srcntacts_acc_table_name
47 ,P_USER_ID => l_user_rec.user_id
48 ,P_ACCESS_ID => l_user_rec.access_id
49 );
50 END LOOP;
51
52 CSM_UTIL_PKG.LOG('Leaving SR_CNTACT_MDIRTY_U_FOREACHUSER for sr_contact_point_id: ' || p_sr_contact_point_id,
53 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_U_FOREACHUSER',FND_LOG.LEVEL_PROCEDURE);
54
55 EXCEPTION
56 WHEN others THEN
57 l_sqlerrno := to_char(SQLCODE);
58 l_sqlerrmsg := substr(SQLERRM, 1,2000);
59 l_error_msg := ' Exception in SR_CNTACT_MDIRTY_U_FOREACHUSER for sr_contact_point_id:' || to_char(p_sr_contact_point_id)
60 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
61 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_U_FOREACHUSER',FND_LOG.LEVEL_EXCEPTION);
62 RAISE;
63 END SR_CNTACT_MDIRTY_U_FOREACHUSER;
64
65 PROCEDURE SR_CNTACT_MDIRTY_I(p_sr_contact_point_id IN NUMBER, p_user_id IN NUMBER)
66 IS
67 l_sqlerrno VARCHAR2(20);
68 l_sqlerrmsg VARCHAR2(4000);
69 l_error_msg VARCHAR2(4000);
70 l_return_status VARCHAR2(2000);
71
72 BEGIN
73 CSM_UTIL_PKG.LOG('Entering SR_CNTACT_MDIRTY_I for sr_contact_point_id: ' || p_sr_contact_point_id,
74 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
75
76 CSM_ACC_PKG.Insert_Acc
77 ( P_PUBLICATION_ITEM_NAMES => g_srcntacts_pubi_name
78 ,P_ACC_TABLE_NAME => g_srcntacts_acc_table_name
79 ,P_SEQ_NAME => g_srcntacts_seq_name
80 ,P_PK1_NAME => g_srcntacts_pk1_name
81 ,P_PK1_NUM_VALUE => p_sr_contact_point_id
82 ,P_USER_ID => p_user_id
83 );
84
85 CSM_UTIL_PKG.LOG('Leaving SR_CNTACT_MDIRTY_I for sr_contact_point_id: ' || p_sr_contact_point_id,
86 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_I',FND_LOG.LEVEL_PROCEDURE);
87 EXCEPTION
88 WHEN OTHERS THEN
89 l_sqlerrno := to_char(SQLCODE);
90 l_sqlerrmsg := substr(SQLERRM, 1,2000);
91 l_error_msg := ' Exception in SR_CNTACT_MDIRTY_I for sr_contact_point_id:' || to_char(p_sr_contact_point_id)
92 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
93 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_I',FND_LOG.LEVEL_EXCEPTION);
94 RAISE;
95 END SR_CNTACT_MDIRTY_I;
96
97 PROCEDURE SPAWN_USERLOOP_SR_CONTACT_INS(p_sr_contact_point_id IN NUMBER)
98 IS
99 l_sqlerrno VARCHAR2(20);
100 l_sqlerrmsg VARCHAR2(4000);
101 l_error_msg VARCHAR2(4000);
102 l_return_status VARCHAR2(2000);
103
104 CURSOR l_sr_contact_usr_csr (p_sr_contact_point_id cs_hz_sr_contact_points.sr_contact_point_id%TYPE)
105 IS
106 SELECT acc.incident_id,
107 acc.user_id
108 FROM cs_hz_sr_contact_points srcp,
109 csm_incidents_all_acc acc
110 WHERE srcp.sr_contact_point_id = p_sr_contact_point_id
111 AND acc.incident_id = srcp.incident_id
112 AND NOT EXISTS
113 (SELECT 1
114 FROM csm_sr_contacts_acc cont_acc
115 WHERE cont_acc.sr_contact_point_id = srcp.sr_contact_point_id
116 AND cont_acc.user_id = acc.user_id)
117 ;
118
119 BEGIN
120 CSM_UTIL_PKG.LOG('Entering SPAWN_USERLOOP_SR_CONTACT_INS for sr_contact_point_id: ' || p_sr_contact_point_id,
121 'CSM_SR_CONTACT_EVENT_PKG.SPAWN_USERLOOP_SR_CONTACT_INS',FND_LOG.LEVEL_PROCEDURE);
122
123 FOR r_sr_contact_usr_rec IN l_sr_contact_usr_csr(p_sr_contact_point_id) LOOP
124 csm_sr_event_pkg.spawn_sr_contacts_ins(p_incident_id=>r_sr_contact_usr_rec.incident_id,
125 p_sr_contact_point_id=>p_sr_contact_point_id,
126 p_user_id=>r_sr_contact_usr_rec.user_id,
127 p_flowtype=>NULL);
128 END LOOP;
129
130 CSM_UTIL_PKG.LOG('Leaving SPAWN_USERLOOP_SR_CONTACT_INS for sr_contact_point_id: ' || p_sr_contact_point_id,
131 'CSM_SR_CONTACT_EVENT_PKG.SPAWN_USERLOOP_SR_CONTACT_INS',FND_LOG.LEVEL_PROCEDURE);
132 EXCEPTION
133 WHEN OTHERS THEN
134 l_sqlerrno := to_char(SQLCODE);
135 l_sqlerrmsg := substr(SQLERRM, 1,2000);
136 l_error_msg := ' Exception in SPAWN_USERLOOP_SR_CONTACT_INS for sr_contact_point_id:' || to_char(p_sr_contact_point_id)
137 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
138 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_CONTACT_EVENT_PKG.SPAWN_USERLOOP_SR_CONTACT_INS',FND_LOG.LEVEL_EXCEPTION);
139 RAISE;
140 END SPAWN_USERLOOP_SR_CONTACT_INS;
141
142 PROCEDURE SR_CNTACT_MDIRTY_D(p_sr_contact_point_id IN NUMBER, p_user_id IN NUMBER)
143 IS
144 l_sqlerrno VARCHAR2(20);
145 l_sqlerrmsg VARCHAR2(4000);
146 l_error_msg VARCHAR2(4000);
147 l_return_status VARCHAR2(2000);
148
149 BEGIN
150 CSM_UTIL_PKG.LOG('Entering SR_CNTACT_MDIRTY_D for sr_contact_point_id: ' || p_sr_contact_point_id,
151 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
152
153 CSM_ACC_PKG.Delete_Acc
154 ( P_PUBLICATION_ITEM_NAMES => g_srcntacts_pubi_name
155 ,P_ACC_TABLE_NAME => g_srcntacts_acc_table_name
156 ,P_PK1_NAME => g_srcntacts_pk1_name
157 ,P_PK1_NUM_VALUE => p_sr_contact_point_id
158 ,P_USER_ID => p_user_id
159 );
160
161 CSM_UTIL_PKG.LOG('Leaving SR_CNTACT_MDIRTY_D for sr_contact_point_id: ' || p_sr_contact_point_id,
162 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_D',FND_LOG.LEVEL_PROCEDURE);
163 EXCEPTION
164 WHEN OTHERS THEN
165 l_sqlerrno := to_char(SQLCODE);
166 l_sqlerrmsg := substr(SQLERRM, 1,2000);
167 l_error_msg := ' Exception in SR_CNTACT_MDIRTY_D for sr_contact_point_id:' || to_char(p_sr_contact_point_id)
168 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
169 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_CONTACT_EVENT_PKG.SR_CNTACT_MDIRTY_D',FND_LOG.LEVEL_EXCEPTION);
170 RAISE;
171 END SR_CNTACT_MDIRTY_D;
172
173 PROCEDURE SPAWN_USERLOOP_SR_CONTACT_DEL(p_sr_contact_point_id IN NUMBER)
174 IS
175 l_sqlerrno VARCHAR2(20);
176 l_sqlerrmsg VARCHAR2(4000);
177 l_error_msg VARCHAR2(4000);
178 l_return_status VARCHAR2(2000);
179
180 CURSOR l_sr_contact_usr_csr (p_sr_contact_point_id cs_hz_sr_contact_points.sr_contact_point_id%TYPE)
181 IS
182 SELECT acc.incident_id,
183 acc.user_id
184 FROM cs_hz_sr_contact_points srcp,
185 csm_incidents_all_acc acc
186 WHERE srcp.sr_contact_point_id = p_sr_contact_point_id
187 AND acc.incident_id = srcp.incident_id
188 AND EXISTS
189 (SELECT 1
190 FROM csm_sr_contacts_acc cont_acc
191 WHERE cont_acc.sr_contact_point_id = srcp.sr_contact_point_id
192 AND cont_acc.user_id = acc.user_id)
193 ;
194
195 BEGIN
196 CSM_UTIL_PKG.LOG('Entering SPAWN_USERLOOP_SR_CONTACT_DEL for sr_contact_point_id: ' || p_sr_contact_point_id,
197 'CSM_SR_CONTACT_EVENT_PKG.SPAWN_USERLOOP_SR_CONTACT_DEL',FND_LOG.LEVEL_PROCEDURE);
198
199 FOR r_sr_contact_usr_rec IN l_sr_contact_usr_csr(p_sr_contact_point_id) LOOP
200 csm_sr_event_pkg.spawn_sr_contact_del(p_incident_id=>r_sr_contact_usr_rec.incident_id,
201 p_sr_contact_point_id=>p_sr_contact_point_id,
202 p_user_id=>r_sr_contact_usr_rec.user_id,
203 p_flowtype=>NULL);
204 END LOOP;
205
206 CSM_UTIL_PKG.LOG('Leaving SPAWN_USERLOOP_SR_CONTACT_DEL for sr_contact_point_id: ' || p_sr_contact_point_id,
207 'CSM_SR_CONTACT_EVENT_PKG.SPAWN_USERLOOP_SR_CONTACT_DEL',FND_LOG.LEVEL_PROCEDURE);
208 EXCEPTION
209 WHEN OTHERS THEN
210 l_sqlerrno := to_char(SQLCODE);
211 l_sqlerrmsg := substr(SQLERRM, 1,2000);
212 l_error_msg := ' Exception in SPAWN_USERLOOP_SR_CONTACT_DEL for sr_contact_point_id:' || to_char(p_sr_contact_point_id)
213 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
214 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_CONTACT_EVENT_PKG.SPAWN_USERLOOP_SR_CONTACT_DEL',FND_LOG.LEVEL_EXCEPTION);
215 RAISE;
216 END SPAWN_USERLOOP_SR_CONTACT_DEL;
217
218 FUNCTION CONTACT_POINT_UPD_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
219 RETURN VARCHAR2
220 IS
221 l_sqlerrno VARCHAR2(20);
222 l_sqlerrmsg VARCHAR2(4000);
223 l_error_msg VARCHAR2(4000);
224 l_return_status VARCHAR2(2000);
225 l_contact_point_id hz_contact_points.contact_point_id%TYPE;
226
227 CURSOR l_user_csr (p_contact_point_id hz_contact_points.contact_point_id%TYPE)
228 IS
229 SELECT acc.user_id,
230 acc.access_id
231 FROM csm_sr_contacts_acc acc,
232 cs_hz_sr_contact_points pts
233 WHERE acc.sr_contact_point_id = pts.sr_contact_point_id
234 AND pts.contact_point_id = p_contact_point_id;
235
236 BEGIN
237 CSM_UTIL_PKG.LOG('Entering CONTACT_POINT_UPD_WF_EVENT_SUB',
238 'CSM_SR_CONTACT_EVENT_PKG.CONTACT_POINT_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
239
240 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
241 RETURN 'SUCCESS';
242 END IF;
243
244 l_contact_point_id := p_event.GetValueForParameter('CONTACT_POINT_ID');
245
246 -- get users who have access to this contact_point_id and there may be more than one record for this contact_point_id
247 FOR l_user_rec IN l_user_csr(l_contact_point_id) LOOP
248 -- Call Update
249 CSM_ACC_PKG.Update_Acc
250 ( P_PUBLICATION_ITEM_NAMES => g_srcntacts_pubi_name
251 ,P_ACC_TABLE_NAME => g_srcntacts_acc_table_name
252 ,P_USER_ID => l_user_rec.user_id
253 ,P_ACCESS_ID => l_user_rec.access_id
254 );
255 END LOOP;
256
257 CSM_UTIL_PKG.LOG('Leaving CONTACT_POINT_UPD_WF_EVENT_SUB for contact_point_id: ' || TO_CHAR(l_contact_point_id),
258 'CSM_SR_CONTACT_EVENT_PKG.CONTACT_POINT_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
259 RETURN 'SUCCESS';
260 EXCEPTION
261 WHEN OTHERS THEN
262 l_sqlerrno := to_char(SQLCODE);
263 l_sqlerrmsg := substr(SQLERRM, 1,2000);
264 l_error_msg := ' Exception in CONTACT_POINT_UPD_WF_EVENT_SUB for contact_point_id:' || to_char(l_contact_point_id)
265 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
266 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_SR_CONTACT_EVENT_PKG.CONTACT_POINT_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
267 RETURN 'ERROR';
268 END CONTACT_POINT_UPD_WF_EVENT_SUB;
269
270 END CSM_SR_CONTACT_EVENT_PKG;