[Home] [Help]
PACKAGE BODY: APPS.CSM_PARTY_SITE_EVENT_PKG
Source
1 PACKAGE BODY CSM_PARTY_SITE_EVENT_PKG AS
2 /* $Header: csmeptsb.pls 120.2 2011/01/10 10:37:02 saradhak ship $ */
3
4 g_table_name1 CONSTANT VARCHAR2(30) := 'HZ_PARTY_SITES';
5 g_acc_table_name1 CONSTANT VARCHAR2(30) := 'CSM_PARTY_SITES_ACC';
6 g_acc_sequence_name1 CONSTANT VARCHAR2(30) := 'CSM_PARTY_SITES_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_PARTY_SITES');
9 g_pk1_name1 CONSTANT VARCHAR2(30) := 'PARTY_SITE_ID';
10 g_pk2_name1 CONSTANT VARCHAR2(30) := 'PARTY_ID';
11
12 procedure insert_party_sites_acc (p_party_site_id hz_party_sites.party_site_id%type,
13 p_party_id hz_parties.party_id%type,
14 p_user_id fnd_user.user_id%type)
15 is
16 l_sysdate date;
17 begin
18 l_sysdate := sysdate;
19
20 insert into csm_party_sites_acc (party_site_id,
21 party_id,
22 user_id,
23 created_by,
24 creation_date,
25 last_updated_by,
26 last_update_date,
27 last_update_login
28 )
29 values (p_party_site_id,
30 p_party_id,
31 p_user_id,
32 fnd_global.user_id,
33 l_sysdate,
34 fnd_global.user_id,
35 l_sysdate,
36 fnd_global.login_id
37 );
38
39 exception
40 when others then
41 raise;
42
43 end insert_party_sites_acc;
44
45 PROCEDURE PARTY_SITES_ACC_I (p_party_site_id IN NUMBER,
46 p_user_id IN NUMBER,
47 p_flowtype IN VARCHAR2,
48 p_error_msg OUT NOCOPY VARCHAR2,
49 x_return_status IN OUT NOCOPY VARCHAR2
50 )
51 IS
52 l_party_id hz_parties.party_id%type;
53 l_err_msg VARCHAR2(4000);
54 l_ret_status VARCHAR2(4000);
55
56 cursor l_party_sites_csr(p_party_site_id hz_party_sites.party_site_id%type)
57 is
58 select party_id
59 from hz_party_sites
60 where party_site_id = p_party_site_id;
61
62 BEGIN
63 x_return_status := FND_API.G_RET_STS_SUCCESS;
64 p_error_msg := 'Entering CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_I' || ' for party_site_id ' || to_char(p_party_site_id);
65
66 OPEN l_party_sites_csr(p_party_site_id);
67 FETCH l_party_sites_csr into l_party_id;
68 CLOSE l_party_sites_csr;
69
70 CSM_ACC_PKG.Insert_Acc
71 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
72 ,P_ACC_TABLE_NAME => g_acc_table_name1
73 ,P_SEQ_NAME => g_acc_sequence_name1
74 ,P_PK1_NAME => g_pk1_name1
75 ,P_PK1_NUM_VALUE => p_party_site_id
76 ,P_PK2_NAME => g_pk2_name1
77 ,P_PK2_NUM_VALUE => l_party_id
78 ,P_USER_ID => p_user_id
79 );
80
81 --get the party info
82 csm_party_event_pkg.PARTY_ACC_I(p_party_id => l_party_id,
83 p_user_id => p_user_id,
84 p_flowtype => p_flowtype,
85 p_error_msg => l_err_msg,
86 x_return_status => l_ret_status);
87
88 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
89 csm_util_pkg.LOG(l_err_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_I', FND_LOG.LEVEL_ERROR);
90 END IF;
91
92 p_error_msg := 'Leaving CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_I' || ' for party_site_id ' || to_char(p_party_site_id);
93 EXCEPTION
94 WHEN others THEN
95 x_return_status := FND_API.G_RET_STS_ERROR;
96 p_error_msg := ' FAILED PARTY_SITES_ACC_I:' || to_char(p_party_site_id);
97 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_I', FND_LOG.LEVEL_EXCEPTION);
98 RAISE;
99 END PARTY_SITES_ACC_I;
100
101 PROCEDURE PARTY_SITES_ACC_D (p_party_site_id IN NUMBER,
102 p_user_id IN NUMBER,
103 p_flowtype IN VARCHAR2,
104 p_error_msg OUT NOCOPY VARCHAR2,
105 x_return_status IN OUT NOCOPY VARCHAR2
106 )
107 IS
108 l_party_id hz_parties.party_id%type;
109 l_err_msg VARCHAR2(4000);
110 l_ret_status VARCHAR2(4000);
111
112 cursor l_party_sites_csr(p_party_site_id hz_party_sites.party_site_id%type, c_user_id NUMBER)
113 is
114 select party_id
115 FROM hz_party_sites ps
116 WHERE party_site_id = p_party_site_id
117 AND NOT EXISTS
118 ( SELECT 1
119 FROM CSM_INCIDENTS_ALL_ACC acc,
120 CS_INCIDENTS_ALL_B B
121 Where B.Incident_Id = Acc.Incident_Id
122 AND B.INCIDENT_LOCATION_TYPE ='HZ_PARTY_SITE'
123 AND ACC.USER_ID = C_USER_ID
124 AND ps.party_site_id = b.incident_location_id);
125
126
127 BEGIN
128 x_return_status := FND_API.G_RET_STS_SUCCESS;
129 p_error_msg := 'Entering CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_D' || ' for party_site_id ' || to_char(p_party_site_id);
130
131 OPEN l_party_sites_csr(p_party_site_id, p_user_id);
132 FETCH l_party_sites_csr into l_party_id;
133 CLOSE l_party_sites_csr;
134
135 IF l_party_id IS NOT NULL THEN
136 CSM_ACC_PKG.Delete_Acc
137 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
138 ,P_ACC_TABLE_NAME => g_acc_table_name1
139 ,P_PK1_NAME => g_pk1_name1
140 ,P_PK1_NUM_VALUE => p_party_site_id
141 ,P_PK2_NAME => g_pk2_name1
142 ,P_PK2_NUM_VALUE => l_party_id
143 ,P_USER_ID => p_user_id
144 );
145
146 --get the party info
147 csm_party_event_pkg.PARTY_ACC_D(p_party_id => l_party_id,
148 p_user_id => p_user_id,
149 p_flowtype => p_flowtype,
150 p_error_msg => l_err_msg,
151 x_return_status => l_ret_status);
152
153 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
154 csm_util_pkg.LOG(l_err_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_D', FND_LOG.LEVEL_ERROR);
155 END IF;
156 END IF;
157 p_error_msg := 'Leaving CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_D' || ' for party_site_id ' || to_char(p_party_site_id);
158
159 EXCEPTION
160 WHEN others THEN
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 p_error_msg := ' FAILED PARTY_SITES_ACC_D:' || to_char(p_party_site_id);
163 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_D', FND_LOG.LEVEL_EXCEPTION);
164 RAISE;
165 END PARTY_SITES_ACC_D;
166
167 PROCEDURE PARTY_SITES_ACC_U (p_party_site_id IN NUMBER,
168 p_user_id IN NUMBER,
169 p_error_msg OUT NOCOPY VARCHAR2,
170 x_return_status IN OUT NOCOPY VARCHAR2
171 )
172 IS
173 l_sqlerrno VARCHAR2(20);
174 l_sqlerrmsg VARCHAR2(4000);
175 l_error_msg VARCHAR2(4000);
176 l_return_status VARCHAR2(2000);
177
178 CURSOR l_party_site_acc_csr (p_party_site_id hz_party_sites.party_site_id%TYPE)
179 IS
180 SELECT acc.access_id, acc.user_id
181 FROM csm_party_sites_acc acc
182 WHERE party_site_id = p_party_site_id;
183
184 BEGIN
185 CSM_UTIL_PKG.LOG('Entering PARTY_SITES_ACC_U for party_site_id: ' || p_party_site_id,
186 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_U',FND_LOG.LEVEL_PROCEDURE);
187
188 FOR r_party_site_acc_rec IN l_party_site_acc_csr(p_party_site_id) LOOP
189 -- Call Update
190 CSM_ACC_PKG.Update_Acc
191 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
192 ,P_ACC_TABLE_NAME => g_acc_table_name1
193 ,P_USER_ID => r_party_site_acc_rec.user_id
194 ,P_ACCESS_ID => r_party_site_acc_rec.access_id
195 );
196 END LOOP;
197
198 CSM_UTIL_PKG.LOG('Leaving PARTY_SITES_ACC_U for party_site_id: ' || p_party_site_id,
199 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_U',FND_LOG.LEVEL_PROCEDURE);
200 EXCEPTION
201 WHEN OTHERS THEN
202 l_sqlerrno := to_char(SQLCODE);
203 l_sqlerrmsg := substr(SQLERRM, 1,2000);
204 l_error_msg := ' Exception in PARTY_SITES_ACC_U for party_site_id:'
205 || to_char(p_party_site_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
206 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_U',FND_LOG.LEVEL_EXCEPTION);
207 RAISE;
208 END PARTY_SITES_ACC_U;
209
210 FUNCTION LOCATION_UPD_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
211 RETURN VARCHAR2
212 IS
213 l_sqlerrno VARCHAR2(20);
214 l_sqlerrmsg VARCHAR2(4000);
215 l_error_msg VARCHAR2(4000);
216 l_return_status VARCHAR2(2000);
217
218 l_location_id hz_party_sites.location_id%TYPE;
219
220 CURSOR l_party_site_csr (p_location_id hz_party_sites.location_id%TYPE)
221 IS
222 SELECT acc.access_id,
223 acc.user_id
224 FROM hz_party_sites ps,
225 csm_party_sites_acc acc
226 WHERE ps.location_id = p_location_id
227 AND acc.party_site_id = ps.party_site_id;
228
229 BEGIN
230 CSM_UTIL_PKG.LOG('Entering LOCATION_UPD_EVENT_SUB',
231 'CSM_PARTY_SITE_EVENT_PKG.LOCATION_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
232
233 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
234 RETURN 'SUCCESS';
235 END IF;
236
237 l_location_id := p_event.GetValueForParameter('LOCATION_ID');
238
239 FOR r_party_site_rec IN l_party_site_csr(l_location_id) LOOP
240 -- Call Update
241 CSM_ACC_PKG.Update_Acc
242 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
243 ,P_ACC_TABLE_NAME => g_acc_table_name1
244 ,P_USER_ID => r_party_site_rec.user_id
245 ,P_ACCESS_ID => r_party_site_rec.access_id
246 );
247 END LOOP;
248
249 CSM_UTIL_PKG.LOG('Leaving LOCATION_UPD_EVENT_SUB for location_id: ' || TO_CHAR(l_location_id),
250 'CSM_PARTY_SITE_EVENT_PKG.LOCATION_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
251
252 RETURN 'SUCCESS';
253 EXCEPTION
254 WHEN OTHERS THEN
255 l_sqlerrno := to_char(SQLCODE);
256 l_sqlerrmsg := substr(SQLERRM, 1,2000);
257 l_error_msg := ' Exception in LOCATION_UPD_EVENT_SUB for location_id:' || to_char(l_location_id)
258 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
259 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.LOCATION_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
260 RETURN 'ERROR';
261 END LOCATION_UPD_WF_EVENT_SUB;
262
263 FUNCTION PARTY_SITE_UPD_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
264 RETURN VARCHAR2
265 IS
266 l_sqlerrno VARCHAR2(20);
267 l_sqlerrmsg VARCHAR2(4000);
268 l_error_msg VARCHAR2(4000);
269 l_return_status VARCHAR2(2000);
270
271 l_party_site_id hz_party_sites.party_site_id%TYPE;
272
273 CURSOR l_party_site_csr (p_party_site_id hz_party_sites.party_site_id%TYPE)
274 IS
275 SELECT access_id, user_id
276 FROM csm_party_sites_acc
277 WHERE party_site_id = p_party_site_id;
278
279 BEGIN
280 CSM_UTIL_PKG.LOG('Entering PARTY_SITE_UPD_WF_EVENT_SUB',
281 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITE_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
282
283 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
284 RETURN 'SUCCESS';
285 END IF;
286
287 l_party_site_id := p_event.GetValueForParameter('PARTY_SITE_ID');
288
289 -- get users who have access to this PARTY_SITE_ID
290 FOR r_party_site_rec IN l_party_site_csr(l_party_site_id) LOOP
291 -- Call Update
292 CSM_ACC_PKG.Update_Acc
293 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
294 ,P_ACC_TABLE_NAME => g_acc_table_name1
295 ,P_USER_ID => r_party_site_rec.user_id
296 ,P_ACCESS_ID => r_party_site_rec.access_id
297 );
298 END LOOP;
299
300 CSM_UTIL_PKG.LOG('Leaving PARTY_SITE_UPD_WF_EVENT_SUB for party_site_id: ' || TO_CHAR(l_party_site_id),
301 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITE_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
302
303 RETURN 'SUCCESS';
304 EXCEPTION
305 WHEN OTHERS THEN
306 l_sqlerrno := to_char(SQLCODE);
307 l_sqlerrmsg := substr(SQLERRM, 1,2000);
308 l_error_msg := ' Exception in PARTY_SITE_UPD_WF_EVENT_SUB for party_site_id:' || to_char(l_party_site_id)
309 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
310 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITE_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
311 RETURN 'ERROR';
312 END PARTY_SITE_UPD_WF_EVENT_SUB;
313
314 END CSM_PARTY_SITE_EVENT_PKG;