[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.1 2005/07/25 00:18:18 trajasek noship $ */
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)
113 is
114 select party_id
115 from hz_party_sites
116 where party_site_id = p_party_site_id;
117
118 BEGIN
119 x_return_status := FND_API.G_RET_STS_SUCCESS;
120 p_error_msg := 'Entering CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_D' || ' for party_site_id ' || to_char(p_party_site_id);
121
122 OPEN l_party_sites_csr(p_party_site_id);
123 FETCH l_party_sites_csr into l_party_id;
124 CLOSE l_party_sites_csr;
125
126 CSM_ACC_PKG.Delete_Acc
127 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
128 ,P_ACC_TABLE_NAME => g_acc_table_name1
129 ,P_PK1_NAME => g_pk1_name1
130 ,P_PK1_NUM_VALUE => p_party_site_id
131 ,P_PK2_NAME => g_pk2_name1
132 ,P_PK2_NUM_VALUE => l_party_id
133 ,P_USER_ID => p_user_id
134 );
135
136 --get the party info
137 csm_party_event_pkg.PARTY_ACC_D(p_party_id => l_party_id,
138 p_user_id => p_user_id,
139 p_flowtype => p_flowtype,
140 p_error_msg => l_err_msg,
141 x_return_status => l_ret_status);
142
143 IF l_ret_status <> FND_API.G_RET_STS_SUCCESS THEN
144 csm_util_pkg.LOG(l_err_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_D', FND_LOG.LEVEL_ERROR);
145 END IF;
146
147 p_error_msg := 'Leaving CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_D' || ' for party_site_id ' || to_char(p_party_site_id);
148
149 EXCEPTION
150 WHEN others THEN
151 x_return_status := FND_API.G_RET_STS_ERROR;
152 p_error_msg := ' FAILED PARTY_SITES_ACC_D:' || to_char(p_party_site_id);
153 CSM_UTIL_PKG.LOG( p_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_D', FND_LOG.LEVEL_EXCEPTION);
154 RAISE;
155 END PARTY_SITES_ACC_D;
156
157 PROCEDURE PARTY_SITES_ACC_U (p_party_site_id IN NUMBER,
158 p_user_id IN NUMBER,
159 p_error_msg OUT NOCOPY VARCHAR2,
160 x_return_status IN OUT NOCOPY VARCHAR2
161 )
162 IS
163 l_sqlerrno VARCHAR2(20);
164 l_sqlerrmsg VARCHAR2(4000);
165 l_error_msg VARCHAR2(4000);
166 l_return_status VARCHAR2(2000);
167
168 CURSOR l_party_site_acc_csr (p_party_site_id hz_party_sites.party_site_id%TYPE)
169 IS
170 SELECT acc.access_id, acc.user_id
171 FROM csm_party_sites_acc acc
172 WHERE party_site_id = p_party_site_id;
173
174 BEGIN
175 CSM_UTIL_PKG.LOG('Entering PARTY_SITES_ACC_U for party_site_id: ' || p_party_site_id,
176 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_U',FND_LOG.LEVEL_PROCEDURE);
177
178 FOR r_party_site_acc_rec IN l_party_site_acc_csr(p_party_site_id) LOOP
179 -- Call Update
180 CSM_ACC_PKG.Update_Acc
181 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
182 ,P_ACC_TABLE_NAME => g_acc_table_name1
183 ,P_USER_ID => r_party_site_acc_rec.user_id
184 ,P_ACCESS_ID => r_party_site_acc_rec.access_id
185 );
186 END LOOP;
187
188 CSM_UTIL_PKG.LOG('Leaving PARTY_SITES_ACC_U for party_site_id: ' || p_party_site_id,
189 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_U',FND_LOG.LEVEL_PROCEDURE);
190 EXCEPTION
191 WHEN OTHERS THEN
192 l_sqlerrno := to_char(SQLCODE);
193 l_sqlerrmsg := substr(SQLERRM, 1,2000);
194 l_error_msg := ' Exception in PARTY_SITES_ACC_U for party_site_id:'
195 || to_char(p_party_site_id) || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
196 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITES_ACC_U',FND_LOG.LEVEL_EXCEPTION);
197 RAISE;
198 END PARTY_SITES_ACC_U;
199
200 FUNCTION LOCATION_UPD_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
201 RETURN VARCHAR2
202 IS
203 l_sqlerrno VARCHAR2(20);
204 l_sqlerrmsg VARCHAR2(4000);
205 l_error_msg VARCHAR2(4000);
206 l_return_status VARCHAR2(2000);
207
208 l_location_id hz_party_sites.location_id%TYPE;
209
210 CURSOR l_party_site_csr (p_location_id hz_party_sites.location_id%TYPE)
211 IS
212 SELECT acc.access_id,
213 acc.user_id
214 FROM hz_party_sites ps,
215 csm_party_sites_acc acc
216 WHERE ps.location_id = p_location_id
217 AND acc.party_site_id = ps.party_site_id;
218
219 BEGIN
220 CSM_UTIL_PKG.LOG('Entering LOCATION_UPD_EVENT_SUB',
221 'CSM_PARTY_SITE_EVENT_PKG.LOCATION_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
222
223 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
224 RETURN 'SUCCESS';
225 END IF;
226
227 l_location_id := p_event.GetValueForParameter('LOCATION_ID');
228
229 FOR r_party_site_rec IN l_party_site_csr(l_location_id) LOOP
230 -- Call Update
231 CSM_ACC_PKG.Update_Acc
232 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
233 ,P_ACC_TABLE_NAME => g_acc_table_name1
234 ,P_USER_ID => r_party_site_rec.user_id
235 ,P_ACCESS_ID => r_party_site_rec.access_id
236 );
237 END LOOP;
238
239 CSM_UTIL_PKG.LOG('Leaving LOCATION_UPD_EVENT_SUB for location_id: ' || TO_CHAR(l_location_id),
240 'CSM_PARTY_SITE_EVENT_PKG.LOCATION_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
241
242 RETURN 'SUCCESS';
243 EXCEPTION
244 WHEN OTHERS THEN
245 l_sqlerrno := to_char(SQLCODE);
246 l_sqlerrmsg := substr(SQLERRM, 1,2000);
247 l_error_msg := ' Exception in LOCATION_UPD_EVENT_SUB for location_id:' || to_char(l_location_id)
248 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
249 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.LOCATION_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
250 RETURN 'ERROR';
251 END LOCATION_UPD_WF_EVENT_SUB;
252
253 FUNCTION PARTY_SITE_UPD_WF_EVENT_SUB(p_subscription_guid IN RAW, p_event IN OUT NOCOPY WF_EVENT_T)
254 RETURN VARCHAR2
255 IS
256 l_sqlerrno VARCHAR2(20);
257 l_sqlerrmsg VARCHAR2(4000);
258 l_error_msg VARCHAR2(4000);
259 l_return_status VARCHAR2(2000);
260
261 l_party_site_id hz_party_sites.party_site_id%TYPE;
262
263 CURSOR l_party_site_csr (p_party_site_id hz_party_sites.party_site_id%TYPE)
264 IS
265 SELECT access_id, user_id
266 FROM csm_party_sites_acc
267 WHERE party_site_id = p_party_site_id;
268
269 BEGIN
270 CSM_UTIL_PKG.LOG('Entering PARTY_SITE_UPD_WF_EVENT_SUB',
271 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITE_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
272
273 IF NOT CSM_UTIL_PKG.IS_FIELD_SERVICE_PALM_ENABLED THEN
274 RETURN 'SUCCESS';
275 END IF;
276
277 l_party_site_id := p_event.GetValueForParameter('PARTY_SITE_ID');
278
279 -- get users who have access to this PARTY_SITE_ID
280 FOR r_party_site_rec IN l_party_site_csr(l_party_site_id) LOOP
281 -- Call Update
282 CSM_ACC_PKG.Update_Acc
283 ( P_PUBLICATION_ITEM_NAMES => g_publication_item_name1
284 ,P_ACC_TABLE_NAME => g_acc_table_name1
285 ,P_USER_ID => r_party_site_rec.user_id
286 ,P_ACCESS_ID => r_party_site_rec.access_id
287 );
288 END LOOP;
289
290 CSM_UTIL_PKG.LOG('Leaving PARTY_SITE_UPD_WF_EVENT_SUB for party_site_id: ' || TO_CHAR(l_party_site_id),
291 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITE_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_PROCEDURE);
292
293 RETURN 'SUCCESS';
294 EXCEPTION
295 WHEN OTHERS THEN
296 l_sqlerrno := to_char(SQLCODE);
297 l_sqlerrmsg := substr(SQLERRM, 1,2000);
298 l_error_msg := ' Exception in PARTY_SITE_UPD_WF_EVENT_SUB for party_site_id:' || to_char(l_party_site_id)
299 || ':' || l_sqlerrno || ':' || l_sqlerrmsg;
300 CSM_UTIL_PKG.LOG(l_error_msg, 'CSM_PARTY_SITE_EVENT_PKG.PARTY_SITE_UPD_WF_EVENT_SUB',FND_LOG.LEVEL_EXCEPTION);
301 RETURN 'ERROR';
302 END PARTY_SITE_UPD_WF_EVENT_SUB;
303
304 END CSM_PARTY_SITE_EVENT_PKG;