[Home] [Help]
PACKAGE BODY: APPS.IBE_MSITE_PRTY_ACCSS_MGR_PVT
Source
1 PACKAGE BODY Ibe_Msite_Prty_Accss_Mgr_Pvt AS
2 /* $Header: IBEVMPMB.pls 120.0 2005/05/30 02:55:43 appldev noship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- Ibe_Msite_Prty_Accss_Mgr_Pvt
10 --
11 -- PURPOSE
12 --
13 --
14 -- NOTES
15 --
16 -- HISTORY
17 -- 01/24/01 VPALAIYA Created
18 -- 12/13/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
19 -- 01/10/03 JQU Delete procedure Get_Party_Id_List for bug 2699536
20 -- 05/05/03 JQU Delete procedure Get_Party_Info_For_Lookup for performance bug 2935856
21 -- ********************************************************************************
22
23 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_MSITE_PRTY_ACCSS_MGR_PVT';
24 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVMPMB.pls';
25
26 --
27 -- Associate (p_party_ids) with p_msite_id.
28 -- x_is_any_duplicate_status will be FND_API.G_RET_STS_SUCCESS, if there is
29 -- no duplicate and will be FND_API.G_RET_STS_ERROR when there is at least 1
30 -- duplicate association attempted
31 --
32 PROCEDURE Associate_Parties_To_MSite
33 (
34 p_api_version IN NUMBER,
35 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
36 p_commit IN VARCHAR2 := FND_API.G_FALSE,
37 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
38 p_msite_id IN NUMBER,
39 p_party_ids IN JTF_NUMBER_TABLE,
40 p_start_date_actives IN JTF_DATE_TABLE,
41 p_end_date_actives IN JTF_DATE_TABLE,
42 x_msite_prty_accss_ids OUT NOCOPY JTF_NUMBER_TABLE,
43 x_duplicate_association_status OUT NOCOPY JTF_VARCHAR2_TABLE_100,
44 x_is_any_duplicate_status OUT NOCOPY VARCHAR2,
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2
48 )
49 IS
50 l_api_name CONSTANT VARCHAR2(30) :=
51 'Associate_Parties_To_MSite';
52 l_api_version CONSTANT NUMBER := 1.0;
53 l_tmp_id NUMBER;
54
55 CURSOR c1(l_c_msite_id IN NUMBER, l_c_party_id IN NUMBER)
56 IS SELECT msite_prty_accss_id FROM ibe_msite_prty_accss
57 WHERE msite_id = l_c_msite_id
58 AND party_id = l_c_party_id;
59
60 BEGIN
61
62 -- Standard Start of API savepoint
63 SAVEPOINT Associate_Parties_To_Msite_Pvt;
64
65 -- Standard call to check for call compatibility.
66 IF NOT FND_API.Compatible_API_Call(l_api_version,
67 p_api_version,
68 l_api_name,
69 G_PKG_NAME)
70 THEN
71 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
72 END IF;
73
74 -- Initialize message list if p_init_msg_list is set to TRUE.
75 IF FND_API.to_Boolean(p_init_msg_list) THEN
76 FND_MSG_PUB.initialize;
77 END IF;
78
79 -- Initialize API return status to success
80 x_return_status := FND_API.G_RET_STS_SUCCESS;
81
82 -- Check if the association already exists. Populate the
83 -- x_duplicate_association_status with the appropriate information
84 x_duplicate_association_status := JTF_VARCHAR2_TABLE_100();
85 x_msite_prty_accss_ids := JTF_NUMBER_TABLE();
86 x_is_any_duplicate_status := FND_API.G_RET_STS_SUCCESS;
87
88 FOR i IN 1..p_party_ids.COUNT LOOP
89
90 x_duplicate_association_status.EXTEND();
91 x_msite_prty_accss_ids.EXTEND();
92
93 OPEN c1(p_msite_id, p_party_ids(i));
94 FETCH c1 INTO l_tmp_id;
95 IF(c1%FOUND) THEN
96 CLOSE c1;
97 -- duplicate exists
98 x_duplicate_association_status(i) := FND_API.G_RET_STS_ERROR;
99 x_is_any_duplicate_status := FND_API.G_RET_STS_ERROR;
100 x_msite_prty_accss_ids(i) := l_tmp_id;
101 ELSE
102 CLOSE c1;
103 -- no duplicate exists, create new entry
104 x_duplicate_association_status(i) := FND_API.G_RET_STS_SUCCESS;
105
106 Ibe_Msite_Prty_Accss_Pvt.Create_Msite_Prty_Accss
107 (
108 p_api_version => p_api_version,
109 p_init_msg_list => FND_API.G_FALSE,
110 p_commit => FND_API.G_FALSE,
111 p_validation_level => p_validation_level,
112 p_msite_id => p_msite_id,
113 p_party_id => p_party_ids(i),
114 p_start_date_active => p_start_date_actives(i),
115 p_end_date_active => p_end_date_actives(i),
116 x_msite_prty_accss_id => x_msite_prty_accss_ids(i),
117 x_return_status => x_return_status,
118 x_msg_count => x_msg_count,
119 x_msg_data => x_msg_data
120 );
121
122 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
123 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CREATE_MSITE_PRTY_FL');
124 FND_MSG_PUB.Add;
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
127 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CREATE_MSITE_PRTY_FL');
128 FND_MSG_PUB.Add;
129 RAISE FND_API.G_EXC_ERROR;
130 END IF;
131
132 END IF;
133
134 END LOOP; -- end for i
135
136 --
137 -- End of main API body.
138
139 -- Standard check of p_commit.
140 IF (FND_API.To_Boolean(p_commit)) THEN
141 COMMIT WORK;
142 END IF;
143
144 -- Standard call to get message count and if count is 1, get message info.
145 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
146 p_data => x_msg_data,
147 p_encoded => 'F');
148
149 EXCEPTION
150
151 WHEN FND_API.G_EXC_ERROR THEN
152 ROLLBACK TO Associate_Parties_To_Msite_Pvt;
153 x_return_status := FND_API.G_RET_STS_ERROR;
154 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
155 p_data => x_msg_data,
156 p_encoded => 'F');
157
158 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
159 ROLLBACK TO Associate_Parties_To_Msite_Pvt;
160 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
161 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
162 p_data => x_msg_data,
163 p_encoded => 'F');
164
165 WHEN OTHERS THEN
166 ROLLBACK TO Associate_Parties_To_Msite_Pvt;
167 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
168 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
169 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
170 FND_MESSAGE.Set_Token('REASON', SQLERRM);
171 FND_MSG_PUB.Add;
172 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
173
174 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
175 THEN
176 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
177 END IF;
178
179 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
180 p_data => x_msg_data,
181 p_encoded => 'F');
182
183 END Associate_Parties_To_MSite;
184
185 --
186 -- to update and delete multiple entries.
187 --
188 PROCEDURE Update_Delete_Msite_Prty
189 (
190 p_api_version IN NUMBER,
191 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
192 p_commit IN VARCHAR2 := FND_API.G_FALSE,
193 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
194 p_msite_prty_accss_ids IN JTF_NUMBER_TABLE,
195 p_object_version_numbers IN JTF_NUMBER_TABLE,
196 p_msite_ids IN JTF_NUMBER_TABLE,
197 p_party_ids IN JTF_NUMBER_TABLE,
198 p_start_date_actives IN JTF_DATE_TABLE,
199 p_end_date_actives IN JTF_DATE_TABLE,
200 p_delete_flags IN JTF_VARCHAR2_TABLE_100,
201 p_msite_id IN NUMBER,
202 p_party_access_code IN VARCHAR2,
203 x_return_status OUT NOCOPY VARCHAR2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2
206 )
207 IS
208 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delete_Msite_Prty';
209 l_api_version CONSTANT NUMBER := 1.0;
210
211 BEGIN
212
213 -- Standard Start of API savepoint
214 SAVEPOINT Update_Delete_Msite_Prty_Pvt;
215
216 -- Standard call to check for call compatibility.
217 IF NOT FND_API.Compatible_API_Call(l_api_version,
218 p_api_version,
219 l_api_name,
220 G_PKG_NAME)
221 THEN
222 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
223 END IF;
224
225 -- Initialize message list if p_init_msg_list is set to TRUE.
226 IF FND_API.to_Boolean(p_init_msg_list) THEN
227 FND_MSG_PUB.initialize;
228 END IF;
229
230 -- Initialize API return status to success
231 x_return_status := FND_API.G_RET_STS_SUCCESS;
232
233 -- API body
234 -- CALL FLOW :
235 -- 1.
236
237 FOR i IN 1..p_msite_prty_accss_ids.COUNT LOOP
238
239 IF (p_delete_flags(i) = 'Y') THEN
240
241 Ibe_Msite_Prty_Accss_Pvt.Delete_Msite_Prty_Accss
242 (
243 p_api_version => p_api_version,
244 p_init_msg_list => FND_API.G_FALSE,
245 p_commit => FND_API.G_FALSE,
246 p_validation_level => p_validation_level,
247 p_msite_prty_accss_id => p_msite_prty_accss_ids(i),
248 p_msite_id => FND_API.G_MISS_NUM,
249 p_party_id => FND_API.G_MISS_NUM,
250 x_return_status => x_return_status,
251 x_msg_count => x_msg_count,
252 x_msg_data => x_msg_data
253 );
254
255 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
256 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_PRTY_FL');
257 FND_MSG_PUB.Add;
258 RAISE FND_API.G_EXC_ERROR;
259 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
260 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_PRTY_FL');
261 FND_MSG_PUB.Add;
262 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
263 END IF;
264
265 ELSE
266
267 Ibe_Msite_Prty_Accss_Pvt.Update_Msite_Prty_Accss
268 (
269 p_api_version => p_api_version,
270 p_init_msg_list => FND_API.G_FALSE,
271 p_commit => FND_API.G_FALSE,
272 p_validation_level => p_validation_level,
273 p_msite_prty_accss_id => p_msite_prty_accss_ids(i),
274 p_object_version_number => p_object_version_numbers(i),
275 p_msite_id => FND_API.G_MISS_NUM,
276 p_party_id => FND_API.G_MISS_NUM,
277 p_start_date_active => p_start_date_actives(i),
278 p_end_date_active => p_end_date_actives(i),
279 x_return_status => x_return_status,
280 x_msg_count => x_msg_count,
281 x_msg_data => x_msg_data
282 );
283
284 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
285 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_PRTY_FL');
286 FND_MSG_PUB.Add;
287 RAISE FND_API.G_EXC_ERROR;
288 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
289 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_PRTY_FL');
290 FND_MSG_PUB.Add;
291 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
292 END IF;
293
294 END IF;
295
296 END LOOP; -- end for i
297
298 --
299 -- Update ibe_msites_b's party_access_code flag
300 --
301 -- Check for validity of party access code
302 IF ((p_party_access_code IS NULL) OR
303 (p_party_access_code = FND_API.G_MISS_CHAR))
304 THEN
305 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVLD_PRTY_ACSS_CODE');
306 FND_MSG_PUB.Add;
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309
310 BEGIN
311 UPDATE ibe_msites_b
312 SET party_access_code = p_party_access_code
313 WHERE msite_id = p_msite_id;
314 EXCEPTION
315 WHEN OTHERS THEN
316 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
317 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
318 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
319 FND_MESSAGE.Set_Token('REASON', SQLERRM);
320 FND_MSG_PUB.Add;
321
322 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PRTY_ACCSS_CODE_FAIL');
323 FND_MESSAGE.Set_Token('PARTY_ACCESS_CODE', p_party_access_code);
324 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
325 FND_MSG_PUB.Add;
326 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
327 END;
328
329 -- End of main API body.
330
331 -- Standard check of p_commit.
332 IF (FND_API.To_Boolean(p_commit)) THEN
333 COMMIT WORK;
334 END IF;
335
336 -- Standard call to get message count and if count is 1, get message info.
337 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
338 p_data => x_msg_data,
339 p_encoded => 'F');
340
341 EXCEPTION
342
343 WHEN FND_API.G_EXC_ERROR THEN
344 ROLLBACK TO Update_Delete_Msite_Prty_Pvt;
345 x_return_status := FND_API.G_RET_STS_ERROR;
346 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
347 p_data => x_msg_data,
348 p_encoded => 'F');
349
350 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
351 ROLLBACK TO Update_Delete_Msite_Prty_Pvt;
352 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
353 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
354 p_data => x_msg_data,
355 p_encoded => 'F');
356
357 WHEN OTHERS THEN
358 ROLLBACK TO Update_Delete_Msite_Prty_Pvt;
359 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
360
361 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
362 THEN
363 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
364 END IF;
365
366 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
367 p_data => x_msg_data,
368 p_encoded => 'F');
369
370 END Update_Delete_Msite_Prty;
371
372 --
373 -- Return data (association + minisite data + party data) belonging to
374 -- the p_msite_id
375 --
376 PROCEDURE Load_MsiteParties_For_Msite
377 (
378 p_api_version IN NUMBER,
379 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
380 p_commit IN VARCHAR2 := FND_API.G_FALSE,
381 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
382 p_msite_id IN NUMBER,
383 x_party_access_code_csr OUT NOCOPY PARTY_ACCESS_CODE_CSR,
384 x_msite_csr OUT NOCOPY MSITE_CSR,
385 x_msite_prty_accss_csr OUT NOCOPY MSITE_PRTY_ACCSS_CSR,
386 x_cust_account_csr OUT NOCOPY CUST_ACCOUNT_CSR,
387 x_return_status OUT NOCOPY VARCHAR2,
388 x_msg_count OUT NOCOPY NUMBER,
389 x_msg_data OUT NOCOPY VARCHAR2
390 )
391 IS
392 l_api_name CONSTANT VARCHAR2(30) :=
393 'Load_MsiteParties_For_Msite';
394 l_api_version CONSTANT NUMBER := 1.0;
395 BEGIN
396
397 -- Initialize message list if p_init_msg_list is set to TRUE.
398 IF FND_API.to_Boolean(p_init_msg_list) THEN
399 FND_MSG_PUB.initialize;
400 END IF;
401
402 -- Initialize API return status to success
403 x_return_status := FND_API.G_RET_STS_SUCCESS;
404
405 -- Get the party access code data
406 OPEN x_party_access_code_csr FOR SELECT lookup_code, meaning
407 FROM fnd_lookups
408 WHERE lookup_type = 'IBE_PARTY_ACCESS_CODE'
409 ORDER BY lookup_code;
410
411 -- Get the mini-site data
412 OPEN x_msite_csr FOR SELECT msite_id, msite_name, party_access_code
413 FROM ibe_msites_vl
414 WHERE msite_id = p_msite_id and site_type = 'I';
415
416 -- Get the msite-party access data and party data
417 OPEN x_msite_prty_accss_csr FOR SELECT MP.msite_prty_accss_id,
418 MP.object_version_number, MP.msite_id, MP.party_id,
419 P.party_name, P.party_type, L.meaning, MP.start_date_active,
420 MP.end_date_active
421 FROM ibe_msite_prty_accss MP, hz_parties P, ar_lookups L
422 WHERE MP.msite_id = p_msite_id
423 AND MP.party_id = P.party_id
424 AND P.party_type = 'ORGANIZATION'
425 AND L.lookup_type = 'PARTY_TYPE'
426 AND P.party_type = L.lookup_code;
427
428 -- Get the party account data
429 OPEN x_cust_account_csr FOR SELECT party_id, account_number
430 FROM hz_cust_accounts_all
431 WHERE status = 'A' and party_id IN
432 (SELECT party_id FROM ibe_msite_prty_accss
433 WHERE msite_id = p_msite_id)
434 ORDER BY party_id, account_number;
435
436 EXCEPTION
437
438 WHEN FND_API.G_EXC_ERROR THEN
439 x_return_status := FND_API.G_RET_STS_ERROR;
440 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
441 p_data => x_msg_data,
442 p_encoded => 'F');
443
444 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
445 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
446 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
447 p_data => x_msg_data,
448 p_encoded => 'F');
449
450 WHEN OTHERS THEN
451 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
452
453 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
454 THEN
455 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
456 END IF;
457
458 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
459 p_data => x_msg_data,
460 p_encoded => 'F');
461
462 END Load_MsiteParties_For_Msite;
463
464 END Ibe_Msite_Prty_Accss_Mgr_Pvt;