[Home] [Help]
PACKAGE BODY: APPS.IBE_MSITE_INFORMATION_MGR_PVT
Source
1 PACKAGE BODY Ibe_Msite_Information_Mgr_Pvt AS
2 /* $Header: IBEVMIMB.pls 120.0 2005/05/30 03:21:16 appldev noship $ */
3
4 -- HISTORY
5 -- 12/13/02 SCHAK Modified for NOCOPY (Bug # 2691704) Changes.
6 -- *********************************************************************************
7
8 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_MSITE_INFORMATION_MGR_PVT';
9 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVMIMB.pls';
10
11 --
12 --
13 --
14 PROCEDURE Change_Msite_Info
15 (
16 p_api_version IN NUMBER,
17 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
18 p_commit IN VARCHAR2 := FND_API.G_FALSE,
19 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
20 p_operation_flags IN JTF_VARCHAR2_TABLE_100,
21 p_msite_information_ids IN JTF_NUMBER_TABLE,
22 p_object_version_numbers IN JTF_NUMBER_TABLE,
23 p_msite_ids IN JTF_NUMBER_TABLE,
24 p_msite_information_contexts IN JTF_VARCHAR2_TABLE_100,
25 p_msite_informations1 IN JTF_VARCHAR2_TABLE_300,
26 p_msite_informations2 IN JTF_VARCHAR2_TABLE_300,
27 p_msite_informations3 IN JTF_VARCHAR2_TABLE_300,
28 p_msite_informations4 IN JTF_VARCHAR2_TABLE_300,
29 p_msite_informations5 IN JTF_VARCHAR2_TABLE_300,
30 p_msite_informations6 IN JTF_VARCHAR2_TABLE_300,
31 p_msite_informations7 IN JTF_VARCHAR2_TABLE_300,
32 p_msite_informations8 IN JTF_VARCHAR2_TABLE_300,
33 p_msite_informations9 IN JTF_VARCHAR2_TABLE_300,
34 p_msite_informations10 IN JTF_VARCHAR2_TABLE_300,
35 p_msite_informations11 IN JTF_VARCHAR2_TABLE_300,
36 p_msite_informations12 IN JTF_VARCHAR2_TABLE_300,
37 p_msite_informations13 IN JTF_VARCHAR2_TABLE_300,
38 p_msite_informations14 IN JTF_VARCHAR2_TABLE_300,
39 p_msite_informations15 IN JTF_VARCHAR2_TABLE_300,
40 p_msite_informations16 IN JTF_VARCHAR2_TABLE_300,
41 p_msite_informations17 IN JTF_VARCHAR2_TABLE_300,
42 p_msite_informations18 IN JTF_VARCHAR2_TABLE_300,
43 p_msite_informations19 IN JTF_VARCHAR2_TABLE_300,
44 p_msite_informations20 IN JTF_VARCHAR2_TABLE_300,
45 p_attribute_categorys IN JTF_VARCHAR2_TABLE_100,
46 p_attributes1 IN JTF_VARCHAR2_TABLE_300,
47 p_attributes2 IN JTF_VARCHAR2_TABLE_300,
48 p_attributes3 IN JTF_VARCHAR2_TABLE_300,
49 p_attributes4 IN JTF_VARCHAR2_TABLE_300,
50 p_attributes5 IN JTF_VARCHAR2_TABLE_300,
51 p_attributes6 IN JTF_VARCHAR2_TABLE_300,
52 p_attributes7 IN JTF_VARCHAR2_TABLE_300,
53 p_attributes8 IN JTF_VARCHAR2_TABLE_300,
54 p_attributes9 IN JTF_VARCHAR2_TABLE_300,
55 p_attributes10 IN JTF_VARCHAR2_TABLE_300,
56 p_attributes11 IN JTF_VARCHAR2_TABLE_300,
57 p_attributes12 IN JTF_VARCHAR2_TABLE_300,
58 p_attributes13 IN JTF_VARCHAR2_TABLE_300,
59 p_attributes14 IN JTF_VARCHAR2_TABLE_300,
60 p_attributes15 IN JTF_VARCHAR2_TABLE_300,
61 x_msite_information_ids OUT NOCOPY JTF_NUMBER_TABLE,
62 x_msite_info_return_statuses OUT NOCOPY JTF_VARCHAR2_TABLE_100,
63 x_return_status OUT NOCOPY VARCHAR2,
64 x_msg_count OUT NOCOPY NUMBER,
65 x_msg_data OUT NOCOPY VARCHAR2
66 )
67 IS
68 l_api_name CONSTANT VARCHAR2(30) :=
69 'Change_Msite_Info';
70 l_api_version CONSTANT NUMBER := 1.0;
71 l_tmp_id NUMBER;
72
73 BEGIN
74
75 -- Standard Start of API savepoint
76 SAVEPOINT Change_Msite_Info_Pvt;
77
78 -- Standard call to check for call compatibility.
79 IF NOT FND_API.Compatible_API_Call(l_api_version,
80 p_api_version,
81 l_api_name,
82 G_PKG_NAME)
83 THEN
84 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
85 END IF;
86
87 -- Initialize message list if p_init_msg_list is set to TRUE.
88 IF FND_API.to_Boolean(p_init_msg_list) THEN
89 FND_MSG_PUB.initialize;
90 END IF;
91
92 -- Initialize API return status to success
93 x_return_status := FND_API.G_RET_STS_SUCCESS;
94
95 x_msite_info_return_statuses := JTF_VARCHAR2_TABLE_100();
96 x_msite_information_ids := JTF_NUMBER_TABLE();
97
98 FOR i IN 1..p_operation_flags.COUNT LOOP
99
100 x_msite_info_return_statuses.EXTEND();
101 x_msite_information_ids.EXTEND();
102
103 x_msite_info_return_statuses(i) := FND_API.G_RET_STS_SUCCESS;
104
105 IF (p_operation_flags(i) = 'C') THEN
106
107 Ibe_Msite_Information_Pvt.Create_Msite_Information
108 (
109 p_api_version => p_api_version,
110 p_init_msg_list => FND_API.G_FALSE,
111 p_commit => FND_API.G_FALSE,
112 p_validation_level => p_validation_level,
113 p_msite_id => p_msite_ids(i),
114 p_msite_information_context => p_msite_information_contexts(i),
115 p_msite_information1 => p_msite_informations1(i),
116 p_msite_information2 => p_msite_informations2(i),
117 p_msite_information3 => p_msite_informations3(i),
118 p_msite_information4 => p_msite_informations4(i),
119 p_msite_information5 => p_msite_informations5(i),
120 p_msite_information6 => p_msite_informations6(i),
121 p_msite_information7 => p_msite_informations7(i),
122 p_msite_information8 => p_msite_informations8(i),
123 p_msite_information9 => p_msite_informations9(i),
124 p_msite_information10 => p_msite_informations10(i),
125 p_msite_information11 => p_msite_informations11(i),
126 p_msite_information12 => p_msite_informations12(i),
127 p_msite_information13 => p_msite_informations13(i),
128 p_msite_information14 => p_msite_informations14(i),
129 p_msite_information15 => p_msite_informations15(i),
130 p_msite_information16 => p_msite_informations16(i),
131 p_msite_information17 => p_msite_informations17(i),
132 p_msite_information18 => p_msite_informations18(i),
133 p_msite_information19 => p_msite_informations19(i),
134 p_msite_information20 => p_msite_informations20(i),
135 p_attribute_category => p_attribute_categorys(i),
136 p_attribute1 => p_attributes1(i),
137 p_attribute2 => p_attributes2(i),
138 p_attribute3 => p_attributes3(i),
139 p_attribute4 => p_attributes4(i),
140 p_attribute5 => p_attributes5(i),
141 p_attribute6 => p_attributes6(i),
142 p_attribute7 => p_attributes7(i),
143 p_attribute8 => p_attributes8(i),
144 p_attribute9 => p_attributes9(i),
145 p_attribute10 => p_attributes10(i),
146 p_attribute11 => p_attributes11(i),
147 p_attribute12 => p_attributes12(i),
148 p_attribute13 => p_attributes13(i),
149 p_attribute14 => p_attributes14(i),
150 p_attribute15 => p_attributes15(i),
151 x_msite_information_id => x_msite_information_ids(i),
152 x_return_status => x_msite_info_return_statuses(i),
153 x_msg_count => x_msg_count,
154 x_msg_data => x_msg_data
155 );
156
157 IF (x_msite_info_return_statuses(i) = FND_API.G_RET_STS_UNEXP_ERROR) THEN
158 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CREATE_MSITE_INFO_FL');
159 FND_MSG_PUB.Add;
160 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
161 ELSIF (x_msite_info_return_statuses(i) = FND_API.G_RET_STS_ERROR) THEN
162 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CREATE_MSITE_INFO_FL');
163 FND_MSG_PUB.Add;
164 RAISE FND_API.G_EXC_ERROR;
165 END IF;
166
167 ELSIF (p_operation_flags(i) = 'U') THEN
168
169 Ibe_Msite_Information_Pvt.Update_Msite_Information
170 (
171 p_api_version => p_api_version,
172 p_init_msg_list => FND_API.G_FALSE,
173 p_commit => FND_API.G_FALSE,
174 p_validation_level => p_validation_level,
175 p_msite_information_id => p_msite_information_ids(i),
176 p_object_version_number => p_object_version_numbers(i),
177 p_msite_information1 => p_msite_informations1(i),
178 p_msite_information2 => p_msite_informations2(i),
179 p_msite_information3 => p_msite_informations3(i),
180 p_msite_information4 => p_msite_informations4(i),
181 p_msite_information5 => p_msite_informations5(i),
182 p_msite_information6 => p_msite_informations6(i),
183 p_msite_information7 => p_msite_informations7(i),
184 p_msite_information8 => p_msite_informations8(i),
185 p_msite_information9 => p_msite_informations9(i),
186 p_msite_information10 => p_msite_informations10(i),
187 p_msite_information11 => p_msite_informations11(i),
188 p_msite_information12 => p_msite_informations12(i),
189 p_msite_information13 => p_msite_informations13(i),
190 p_msite_information14 => p_msite_informations14(i),
191 p_msite_information15 => p_msite_informations15(i),
192 p_msite_information16 => p_msite_informations16(i),
193 p_msite_information17 => p_msite_informations17(i),
194 p_msite_information18 => p_msite_informations18(i),
195 p_msite_information19 => p_msite_informations19(i),
196 p_msite_information20 => p_msite_informations20(i),
197 p_attribute_category => p_attribute_categorys(i),
198 p_attribute1 => p_attributes1(i),
199 p_attribute2 => p_attributes2(i),
200 p_attribute3 => p_attributes3(i),
201 p_attribute4 => p_attributes4(i),
202 p_attribute5 => p_attributes5(i),
203 p_attribute6 => p_attributes6(i),
204 p_attribute7 => p_attributes7(i),
205 p_attribute8 => p_attributes8(i),
206 p_attribute9 => p_attributes9(i),
207 p_attribute10 => p_attributes10(i),
208 p_attribute11 => p_attributes11(i),
209 p_attribute12 => p_attributes12(i),
210 p_attribute13 => p_attributes13(i),
211 p_attribute14 => p_attributes14(i),
212 p_attribute15 => p_attributes15(i),
213 x_return_status => x_msite_info_return_statuses(i),
214 x_msg_count => x_msg_count,
215 x_msg_data => x_msg_data
216 );
217
218 IF (x_msite_info_return_statuses(i) = FND_API.G_RET_STS_UNEXP_ERROR) THEN
219 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_INFO_FL');
220 FND_MSG_PUB.Add;
221 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
222 ELSIF (x_msite_info_return_statuses(i) = FND_API.G_RET_STS_ERROR) THEN
223 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_UPDATE_MSITE_INFO_FL');
224 FND_MSG_PUB.Add;
225 RAISE FND_API.G_EXC_ERROR;
226 END IF;
227
228 x_msite_information_ids(i) := p_msite_information_ids(i);
229
230 ELSIF (p_operation_flags(i) = 'D') THEN
231
232 Ibe_Msite_Information_Pvt.Delete_Msite_Information
233 (
234 p_api_version => p_api_version,
235 p_init_msg_list => FND_API.G_FALSE,
236 p_commit => FND_API.G_FALSE,
237 p_validation_level => p_validation_level,
238 p_msite_information_id => p_msite_information_ids(i),
239 x_return_status => x_return_status,
240 x_msg_count => x_msg_count,
241 x_msg_data => x_msg_data
242 );
243
244 IF (x_msite_info_return_statuses(i) = FND_API.G_RET_STS_UNEXP_ERROR) THEN
245 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_INFO_FL');
246 FND_MSG_PUB.Add;
247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
248 ELSIF (x_msite_info_return_statuses(i) = FND_API.G_RET_STS_ERROR) THEN
249 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DELETE_MSITE_INFO_FL');
250 FND_MSG_PUB.Add;
251 RAISE FND_API.G_EXC_ERROR;
252 END IF;
253
254 x_msite_information_ids(i) := p_msite_information_ids(i);
255
256 ELSIF (p_operation_flags(i) = 'N') THEN
257 -- do nothing
258 x_msite_information_ids(i) := p_msite_information_ids(i);
259 ELSE
260 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_INVALID_OP_FLAG');
261 FND_MESSAGE.Set_Token('FLAG', p_operation_flags(i));
262 FND_MSG_PUB.Add;
263 RAISE FND_API.G_EXC_ERROR;
264 END IF;
265
266 END LOOP; -- end for i
267
268 --
269 -- End of main API body.
270
271 -- Standard check of p_commit.
272 IF (FND_API.To_Boolean(p_commit)) THEN
273 COMMIT WORK;
274 END IF;
275
276 -- Standard call to get message count and if count is 1, get message info.
277 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
278 p_data => x_msg_data,
279 p_encoded => 'F');
280
281 EXCEPTION
282
283 WHEN FND_API.G_EXC_ERROR THEN
284 ROLLBACK TO Change_Msite_Info_Pvt;
285 x_return_status := FND_API.G_RET_STS_ERROR;
286 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
287 p_data => x_msg_data,
288 p_encoded => 'F');
289
290 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
291 ROLLBACK TO Change_Msite_Info_Pvt;
292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
293 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
294 p_data => x_msg_data,
295 p_encoded => 'F');
296
297 WHEN OTHERS THEN
298 ROLLBACK TO Change_Msite_Info_Pvt;
299 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
300 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
301 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
302 FND_MESSAGE.Set_Token('REASON', SQLERRM);
303 FND_MSG_PUB.Add;
304 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
305
306 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
307 THEN
308 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
309 END IF;
310
311 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
312 p_data => x_msg_data,
313 p_encoded => 'F');
314
315 END Change_Msite_Info;
316
317 --
318 -- Return data (association + minisite data) belonging to
319 -- the p_msite_id for the given p_msite_information_context
320 --
321 PROCEDURE Load_MsiteInformation
322 (
323 p_api_version IN NUMBER,
324 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
325 p_commit IN VARCHAR2 := FND_API.G_FALSE,
326 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
327 p_msite_id IN NUMBER,
328 p_msite_information_context IN VARCHAR2,
329 x_msite_csr OUT NOCOPY MSITE_CSR,
330 x_msite_information_csr OUT NOCOPY MSITE_INFO_CSR,
331 x_return_status OUT NOCOPY VARCHAR2,
332 x_msg_count OUT NOCOPY NUMBER,
333 x_msg_data OUT NOCOPY VARCHAR2
334 )
335 IS
336 l_api_name CONSTANT VARCHAR2(30) :=
337 'Load_MsiteInformation';
338 l_api_version CONSTANT NUMBER := 1.0;
339 BEGIN
340
341 -- Initialize message list if p_init_msg_list is set to TRUE.
342 IF FND_API.to_Boolean(p_init_msg_list) THEN
343 FND_MSG_PUB.initialize;
344 END IF;
345
346 -- Initialize API return status to success
347 x_return_status := FND_API.G_RET_STS_SUCCESS;
348
349 -- Get the mini-site data
350 OPEN x_msite_csr FOR SELECT msite_id, msite_name
351 FROM ibe_msites_vl
352 WHERE msite_id = p_msite_id and site_type = 'I';
353
354 -- Get the msite-party access data and party data
355 OPEN x_msite_information_csr FOR SELECT msite_information_id,
356 object_version_number, msite_id, msite_information_context,
357 msite_information1, msite_information2, msite_information3,
358 msite_information4, msite_information5, msite_information6,
359 msite_information7, msite_information8, msite_information9,
360 msite_information10, msite_information11, msite_information12,
361 msite_information13, msite_information14, msite_information15,
362 msite_information16, msite_information17, msite_information18,
363 msite_information19, msite_information20, attribute_category, attribute1,
364 attribute2, attribute3, attribute4, attribute5, attribute6, attribute7,
365 attribute8, attribute9, attribute10, attribute11, attribute12,
366 attribute13, attribute14, attribute15
367 FROM ibe_msite_information
368 WHERE msite_id = p_msite_id
369 AND msite_information_context = p_msite_information_context;
370
371 EXCEPTION
372
373 WHEN FND_API.G_EXC_ERROR THEN
374 x_return_status := FND_API.G_RET_STS_ERROR;
375 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
376 p_data => x_msg_data,
377 p_encoded => 'F');
378
379 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
380 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
381 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
382 p_data => x_msg_data,
383 p_encoded => 'F');
384
385 WHEN OTHERS THEN
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387
388 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
389 THEN
390 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
391 END IF;
392
393 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
394 p_data => x_msg_data,
395 p_encoded => 'F');
396
397 END Load_MsiteInformation;
398
399 PROCEDURE duplicate_msite_info(
400 p_api_version IN NUMBER,
401 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
402 p_commit IN VARCHAR2 := FND_API.G_FALSE,
403 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
404 p_source_msite_id IN NUMBER,
405 p_target_msite_id IN NUMBER,
406 x_return_status OUT NOCOPY VARCHAR2,
407 x_msg_count OUT NOCOPY NUMBER,
408 x_msg_data OUT NOCOPY VARCHAR2)
409 IS
410 l_api_version NUMBER := 1.0;
411 l_api_name VARCHAR2(50) := 'duplicate_msite_info';
412 l_return_status VARCHAR2(1);
413 l_msg_count NUMBER;
414 l_msg_data VARCHAR2(4000);
415 l_debug VARCHAR2(1);
416 l_i NUMBER;
417 -- For minisite information, such as payment type, credit card
418 -- and shipping method
419 l_operation_flags JTF_VARCHAR2_TABLE_100;
420 l_msite_information_ids JTF_NUMBER_TABLE;
421 l_object_version_numbers JTF_NUMBER_TABLE;
422 l_msite_ids JTF_NUMBER_TABLE;
423 x_msite_information_ids JTF_NUMBER_TABLE;
424 x_msite_info_return_statuses JTF_VARCHAR2_TABLE_100;
425 l_msite_information_context VARCHAR2(40);
426 l_msite_information1 VARCHAR2(150);
427 l_msite_information2 VARCHAR2(150);
428 l_msite_information3 VARCHAR2(150);
429 l_msite_information4 VARCHAR2(150);
430 l_msite_information5 VARCHAR2(150);
431 l_msite_information6 VARCHAR2(150);
432 l_msite_information7 VARCHAR2(150);
433 l_msite_information8 VARCHAR2(150);
434 l_msite_information9 VARCHAR2(150);
435 l_msite_information10 VARCHAR2(150);
436 l_msite_information11 VARCHAR2(150);
437 l_msite_information12 VARCHAR2(150);
438 l_msite_information13 VARCHAR2(150);
439 l_msite_information14 VARCHAR2(150);
440 l_msite_information15 VARCHAR2(150);
441 l_msite_information16 VARCHAR2(150);
442 l_msite_information17 VARCHAR2(150);
443 l_msite_information18 VARCHAR2(150);
444 l_msite_information19 VARCHAR2(150);
445 l_msite_information20 VARCHAR2(150);
446 l_attribute_category VARCHAR2(30);
447 l_attribute1 VARCHAR2(150);
448 l_attribute2 VARCHAR2(150);
449 l_attribute3 VARCHAR2(150);
450 l_attribute4 VARCHAR2(150);
451 l_attribute5 VARCHAR2(150);
452 l_attribute6 VARCHAR2(150);
453 l_attribute7 VARCHAR2(150);
454 l_attribute8 VARCHAR2(150);
455 l_attribute9 VARCHAR2(150);
456 l_attribute10 VARCHAR2(150);
457 l_attribute11 VARCHAR2(150);
458 l_attribute12 VARCHAR2(150);
459 l_attribute13 VARCHAR2(150);
460 l_attribute14 VARCHAR2(150);
461 l_attribute15 VARCHAR2(150);
462 l_msite_information_contexts JTF_VARCHAR2_TABLE_100;
463 l_msite_informations1 JTF_VARCHAR2_TABLE_300;
464 l_msite_informations2 JTF_VARCHAR2_TABLE_300;
465 l_msite_informations3 JTF_VARCHAR2_TABLE_300;
466 l_msite_informations4 JTF_VARCHAR2_TABLE_300;
467 l_msite_informations5 JTF_VARCHAR2_TABLE_300;
468 l_msite_informations6 JTF_VARCHAR2_TABLE_300;
469 l_msite_informations7 JTF_VARCHAR2_TABLE_300;
470 l_msite_informations8 JTF_VARCHAR2_TABLE_300;
471 l_msite_informations9 JTF_VARCHAR2_TABLE_300;
472 l_msite_informations10 JTF_VARCHAR2_TABLE_300;
473 l_msite_informations11 JTF_VARCHAR2_TABLE_300;
474 l_msite_informations12 JTF_VARCHAR2_TABLE_300;
475 l_msite_informations13 JTF_VARCHAR2_TABLE_300;
476 l_msite_informations14 JTF_VARCHAR2_TABLE_300;
477 l_msite_informations15 JTF_VARCHAR2_TABLE_300;
478 l_msite_informations16 JTF_VARCHAR2_TABLE_300;
479 l_msite_informations17 JTF_VARCHAR2_TABLE_300;
480 l_msite_informations18 JTF_VARCHAR2_TABLE_300;
481 l_msite_informations19 JTF_VARCHAR2_TABLE_300;
482 l_msite_informations20 JTF_VARCHAR2_TABLE_300;
483 l_attribute_categorys JTF_VARCHAR2_TABLE_100;
484 l_attributes1 JTF_VARCHAR2_TABLE_300;
485 l_attributes2 JTF_VARCHAR2_TABLE_300;
486 l_attributes3 JTF_VARCHAR2_TABLE_300;
487 l_attributes4 JTF_VARCHAR2_TABLE_300;
488 l_attributes5 JTF_VARCHAR2_TABLE_300;
489 l_attributes6 JTF_VARCHAR2_TABLE_300;
490 l_attributes7 JTF_VARCHAR2_TABLE_300;
491 l_attributes8 JTF_VARCHAR2_TABLE_300;
492 l_attributes9 JTF_VARCHAR2_TABLE_300;
493 l_attributes10 JTF_VARCHAR2_TABLE_300;
494 l_attributes11 JTF_VARCHAR2_TABLE_300;
495 l_attributes12 JTF_VARCHAR2_TABLE_300;
496 l_attributes13 JTF_VARCHAR2_TABLE_300;
497 l_attributes14 JTF_VARCHAR2_TABLE_300;
498 l_attributes15 JTF_VARCHAR2_TABLE_300;
499
500 CURSOR c_get_msite_info_csr(c_msite_id NUMBER) IS
501 SELECT MSITE_INFORMATION_CONTEXT, MSITE_INFORMATION1, MSITE_INFORMATION2,
502 MSITE_INFORMATION3, MSITE_INFORMATION4, MSITE_INFORMATION5,
503 MSITE_INFORMATION6, MSITE_INFORMATION7, MSITE_INFORMATION8,
504 MSITE_INFORMATION9, MSITE_INFORMATION10, MSITE_INFORMATION11,
505 MSITE_INFORMATION12, MSITE_INFORMATION13, MSITE_INFORMATION14,
506 MSITE_INFORMATION15, MSITE_INFORMATION16, MSITE_INFORMATION17,
507 MSITE_INFORMATION18, MSITE_INFORMATION19, MSITE_INFORMATION20,
508 ATTRIBUTE_CATEGORY, ATTRIBUTE1, ATTRIBUTE2,
509 ATTRIBUTE3, ATTRIBUTE4, ATTRIBUTE5, ATTRIBUTE6, ATTRIBUTE7,
510 ATTRIBUTE8, ATTRIBUTE9, ATTRIBUTE10, ATTRIBUTE11, ATTRIBUTE12,
511 ATTRIBUTE13, ATTRIBUTE14, ATTRIBUTE15
512 FROM IBE_MSITE_INFORMATION
513 WHERE MSITE_ID = c_msite_id;
514 BEGIN
515 SAVEPOINT DUPLICATE_MSITE_INFO_SAVE;
516 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
517 IF (l_debug = 'Y') THEN
518 IBE_UTIL.debug('duplicate_msite Starts +');
519 IBE_UTIL.debug('p_api_version = '||p_api_version);
520 IBE_UTIL.debug('p_init_msg_list = '||p_init_msg_list);
521 IBE_UTIL.debug('p_commit = '||p_commit);
522 IBE_UTIL.debug('p_source_msite_id = '||p_source_msite_id);
523 IBE_UTIL.debug('p_target_msite_id = '||p_target_msite_id);
524 END IF;
525 IF NOT FND_API.Compatible_API_Call(l_api_version,
526 p_api_version,
527 l_api_name,
528 G_PKG_NAME) THEN
529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
530 END IF;
531 IF FND_API.to_Boolean(p_init_msg_list) THEN
532 FND_MSG_PUB.initialize;
533 END IF;
534 -- Initialize API return status to SUCCESS
535 x_return_status := FND_API.G_RET_STS_SUCCESS;
536
537 -- For Payment Types, Shipping Methods, and Credit Card
538 l_operation_flags := JTF_VARCHAR2_TABLE_100();
539 l_msite_ids := JTF_NUMBER_TABLE();
540 l_msite_information_ids := JTF_NUMBER_TABLE();
541 l_object_version_numbers := JTF_NUMBER_TABLE();
542 l_msite_information_contexts := JTF_VARCHAR2_TABLE_100();
543 l_msite_informations1 := JTF_VARCHAR2_TABLE_300();
544 l_msite_informations2 := JTF_VARCHAR2_TABLE_300();
545 l_msite_informations3 := JTF_VARCHAR2_TABLE_300();
546 l_msite_informations4 := JTF_VARCHAR2_TABLE_300();
547 l_msite_informations5 := JTF_VARCHAR2_TABLE_300();
548 l_msite_informations6 := JTF_VARCHAR2_TABLE_300();
549 l_msite_informations7 := JTF_VARCHAR2_TABLE_300();
550 l_msite_informations8 := JTF_VARCHAR2_TABLE_300();
551 l_msite_informations9 := JTF_VARCHAR2_TABLE_300();
552 l_msite_informations10 := JTF_VARCHAR2_TABLE_300();
553 l_msite_informations11 := JTF_VARCHAR2_TABLE_300();
554 l_msite_informations12 := JTF_VARCHAR2_TABLE_300();
555 l_msite_informations13 := JTF_VARCHAR2_TABLE_300();
556 l_msite_informations14 := JTF_VARCHAR2_TABLE_300();
557 l_msite_informations15 := JTF_VARCHAR2_TABLE_300();
558 l_msite_informations16 := JTF_VARCHAR2_TABLE_300();
559 l_msite_informations17 := JTF_VARCHAR2_TABLE_300();
560 l_msite_informations18 := JTF_VARCHAR2_TABLE_300();
561 l_msite_informations19 := JTF_VARCHAR2_TABLE_300();
562 l_msite_informations20 := JTF_VARCHAR2_TABLE_300();
563 l_attribute_categorys := JTF_VARCHAR2_TABLE_100();
564 l_attributes1 := JTF_VARCHAR2_TABLE_300();
565 l_attributes2 := JTF_VARCHAR2_TABLE_300();
566 l_attributes3 := JTF_VARCHAR2_TABLE_300();
567 l_attributes4 := JTF_VARCHAR2_TABLE_300();
568 l_attributes5 := JTF_VARCHAR2_TABLE_300();
569 l_attributes6 := JTF_VARCHAR2_TABLE_300();
570 l_attributes7 := JTF_VARCHAR2_TABLE_300();
571 l_attributes8 := JTF_VARCHAR2_TABLE_300();
572 l_attributes9 := JTF_VARCHAR2_TABLE_300();
573 l_attributes10 := JTF_VARCHAR2_TABLE_300();
574 l_attributes11 := JTF_VARCHAR2_TABLE_300();
575 l_attributes12 := JTF_VARCHAR2_TABLE_300();
576 l_attributes13 := JTF_VARCHAR2_TABLE_300();
577 l_attributes14 := JTF_VARCHAR2_TABLE_300();
578 l_attributes15 := JTF_VARCHAR2_TABLE_300();
579 l_i := 1;
580 OPEN c_get_msite_info_csr(p_source_msite_id);
581 LOOP
582 FETCH c_get_msite_info_csr INTO l_msite_information_context,
583 l_msite_information1, l_msite_information2, l_msite_information3,
584 l_msite_information4, l_msite_information5, l_msite_information6,
585 l_msite_information7, l_msite_information8, l_msite_information9,
586 l_msite_information10, l_msite_information11, l_msite_information12,
587 l_msite_information13, l_msite_information14, l_msite_information15,
588 l_msite_information16, l_msite_information17, l_msite_information18,
589 l_msite_information19, l_msite_information20, l_attribute_category,
590 l_attribute1, l_attribute2, l_attribute3, l_attribute4, l_attribute5,
591 l_attribute6, l_attribute7, l_attribute8, l_attribute9, l_attribute10,
592 l_attribute11, l_attribute12, l_attribute13, l_attribute14, l_attribute15;
593 EXIT WHEN c_get_msite_info_csr%NOTFOUND;
594 l_msite_ids.EXTEND;
595 l_operation_flags.EXTEND;
596 l_msite_information_ids.EXTEND;
597 l_object_version_numbers.EXTEND;
598 l_msite_information_contexts.EXTEND;
599 l_msite_informations1.EXTEND;
600 l_msite_informations2.EXTEND;
601 l_msite_informations3.EXTEND;
602 l_msite_informations4.EXTEND;
603 l_msite_informations5.EXTEND;
604 l_msite_informations6.EXTEND;
605 l_msite_informations7.EXTEND;
606 l_msite_informations8.EXTEND;
607 l_msite_informations9.EXTEND;
608 l_msite_informations10.EXTEND;
609 l_msite_informations11.EXTEND;
610 l_msite_informations12.EXTEND;
611 l_msite_informations13.EXTEND;
612 l_msite_informations14.EXTEND;
613 l_msite_informations15.EXTEND;
614 l_msite_informations16.EXTEND;
615 l_msite_informations17.EXTEND;
616 l_msite_informations18.EXTEND;
617 l_msite_informations19.EXTEND;
618 l_msite_informations20.EXTEND;
619 l_attribute_categorys.EXTEND;
620 l_attributes1.EXTEND;
621 l_attributes2.EXTEND;
622 l_attributes3.EXTEND;
623 l_attributes4.EXTEND;
624 l_attributes5.EXTEND;
625 l_attributes6.EXTEND;
626 l_attributes7.EXTEND;
627 l_attributes8.EXTEND;
628 l_attributes9.EXTEND;
629 l_attributes10.EXTEND;
630 l_attributes11.EXTEND;
631 l_attributes12.EXTEND;
632 l_attributes13.EXTEND;
633 l_attributes14.EXTEND;
634 l_attributes15.EXTEND;
635 l_operation_flags(l_i) := 'C';
636 l_msite_ids(l_i) := p_target_msite_id;
637 l_msite_information_ids(l_i) := NULL;
638 l_object_version_numbers(l_i) := NULL;
639 l_msite_information_contexts(l_i) := l_msite_information_context;
640 l_msite_informations1(l_i) := l_msite_information1;
641 l_msite_informations2(l_i) := l_msite_information2;
642 l_msite_informations3(l_i) := l_msite_information3;
643 l_msite_informations4(l_i) := l_msite_information4;
644 l_msite_informations5(l_i) := l_msite_information5;
645 l_msite_informations6(l_i) := l_msite_information6;
646 l_msite_informations7(l_i) := l_msite_information7;
647 l_msite_informations8(l_i) := l_msite_information8;
648 l_msite_informations9(l_i) := l_msite_information9;
649 l_msite_informations10(l_i) := l_msite_information10;
650 l_msite_informations11(l_i) := l_msite_information11;
651 l_msite_informations12(l_i) := l_msite_information12;
652 l_msite_informations13(l_i) := l_msite_information13;
653 l_msite_informations14(l_i) := l_msite_information14;
654 l_msite_informations15(l_i) := l_msite_information15;
655 l_msite_informations16(l_i) := l_msite_information16;
656 l_msite_informations17(l_i) := l_msite_information17;
657 l_msite_informations18(l_i) := l_msite_information18;
658 l_msite_informations19(l_i) := l_msite_information19;
659 l_msite_informations20(l_i) := l_msite_information20;
660 l_attribute_categorys(l_i) := l_attribute_category;
661 l_attributes1(l_i) := l_attribute1;
662 l_attributes2(l_i) := l_attribute2;
663 l_attributes3(l_i) := l_attribute3;
664 l_attributes4(l_i) := l_attribute4;
665 l_attributes5(l_i) := l_attribute5;
666 l_attributes6(l_i) := l_attribute6;
667 l_attributes7(l_i) := l_attribute7;
668 l_attributes8(l_i) := l_attribute8;
669 l_attributes9(l_i) := l_attribute9;
670 l_attributes10(l_i) := l_attribute10;
671 l_attributes11(l_i) := l_attribute11;
672 l_attributes12(l_i) := l_attribute12;
673 l_attributes13(l_i) := l_attribute13;
674 l_attributes14(l_i) := l_attribute14;
675 l_attributes15(l_i) := l_attribute15;
676 l_i := l_i + 1;
677 END LOOP;
678 CLOSE c_get_msite_info_csr;
679 IF (l_i > 1) THEN
680 IBE_MSITE_INFORMATION_MGR_PVT.Change_Msite_Info (
681 p_api_version => 1.0,
682 p_init_msg_list => FND_API.G_FALSE,
683 p_commit => p_commit,
684 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
685 p_operation_flags => l_operation_flags,
686 p_msite_information_ids => l_msite_information_ids,
687 p_object_version_numbers => l_object_version_numbers,
688 p_msite_ids => l_msite_ids,
689 p_msite_information_contexts => l_msite_information_contexts,
690 p_msite_informations1 => l_msite_informations1,
691 p_msite_informations2 => l_msite_informations2,
692 p_msite_informations3 => l_msite_informations3,
693 p_msite_informations4 => l_msite_informations4,
694 p_msite_informations5 => l_msite_informations5,
695 p_msite_informations6 => l_msite_informations6,
696 p_msite_informations7 => l_msite_informations7,
697 p_msite_informations8 => l_msite_informations8,
698 p_msite_informations9 => l_msite_informations9,
699 p_msite_informations10 => l_msite_informations10,
700 p_msite_informations11 => l_msite_informations11,
701 p_msite_informations12 => l_msite_informations12,
702 p_msite_informations13 => l_msite_informations13,
703 p_msite_informations14 => l_msite_informations14,
704 p_msite_informations15 => l_msite_informations15,
705 p_msite_informations16 => l_msite_informations16,
706 p_msite_informations17 => l_msite_informations17,
707 p_msite_informations18 => l_msite_informations18,
708 p_msite_informations19 => l_msite_informations19,
709 p_msite_informations20 => l_msite_informations20,
710 p_attribute_categorys => l_attribute_categorys,
711 p_attributes1 => l_attributes1,
712 p_attributes2 => l_attributes2,
713 p_attributes3 => l_attributes3,
714 p_attributes4 => l_attributes4,
715 p_attributes5 => l_attributes5,
716 p_attributes6 => l_attributes6,
717 p_attributes7 => l_attributes7,
718 p_attributes8 => l_attributes8,
719 p_attributes9 => l_attributes9,
720 p_attributes10 => l_attributes10,
721 p_attributes11 => l_attributes11,
722 p_attributes12 => l_attributes12,
723 p_attributes13 => l_attributes13,
724 p_attributes14 => l_attributes14,
725 p_attributes15 => l_attributes15,
726 x_msite_information_ids => x_msite_information_ids,
727 x_msite_info_return_statuses => x_msite_info_return_statuses,
728 x_return_status => l_return_status,
729 x_msg_count => l_msg_count,
730 x_msg_data => l_msg_data
731 );
732 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
733 IF (l_debug = 'Y') THEN
734 IBE_UTIL.debug('Error in IBE_MSITE_INFORMATION_MGR_PVT.'||
735 'Change_Msite_Info');
736 for i in 1..l_msg_count loop
737 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
738 IBE_UTIL.debug(l_msg_data);
739 end loop;
740 END IF;
741 RAISE FND_API.G_EXC_ERROR;
742 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
743 IF (l_debug = 'Y') THEN
744 IBE_UTIL.debug('Error in IBE_MSITE_INFORMATION_MGR_PVT.'||
745 'Change_Msite_Info');
746 for i in 1..l_msg_count loop
747 l_msg_data := FND_MSG_PUB.get(i,FND_API.G_FALSE);
748 IBE_UTIL.debug(l_msg_data);
749 end loop;
750 END IF;
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
752 END IF;
753 END IF;
754 IF (l_debug = 'Y') THEN
755 IBE_UTIL.debug('Before committing the work:'||p_commit);
756 END IF;
757 IF (FND_API.To_Boolean(p_commit)) THEN
758 COMMIT WORK;
759 END IF;
760 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
761 p_data => x_msg_data,
762 p_encoded => 'F');
763 IF (l_debug = 'Y') THEN
764 IBE_UTIL.debug('duplicate_msite Ends +');
765 END IF;
766 EXCEPTION
767 WHEN FND_API.G_EXC_ERROR THEN
768 ROLLBACK TO DUPLICATE_MSITE_INFO_SAVE;
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
771 p_data => x_msg_data,
772 p_encoded => 'F');
773 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
774 ROLLBACK TO DUPLICATE_MSITE_INFO_SAVE;
775 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
776 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
777 p_data => x_msg_data,
778 p_encoded => 'F');
779 WHEN OTHERS THEN
780 ROLLBACK TO DUPLICATE_MSITE_INFO_SAVE;
781 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
782 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
783 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
784 FND_MESSAGE.Set_Token('REASON', SQLERRM);
785 FND_MSG_PUB.Add;
786 IF (l_debug = 'Y') THEN
787 IBE_UTIL.debug('SQLCODE:'||SQLCODE);
788 IBE_UTIL.debug('SQLERRM:'||SQLERRM);
789 END IF;
790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
791 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
792 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
793 END IF;
794 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
795 p_data => x_msg_data,
796 p_encoded => 'F');
797 END duplicate_msite_info;
798
799 END Ibe_Msite_Information_Mgr_Pvt;