[Home] [Help]
PACKAGE BODY: APPS.IBE_MSITE_INFORMATION_PVT
Source
1 PACKAGE BODY Ibe_Msite_Information_Pvt AS
2 /* $Header: IBEVMINB.pls 120.1 2005/08/10 07:03:32 appldev ship $ */
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_PVT';
9 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVMINB.pls';
10
11 --
12 --
13 --
14 PROCEDURE Validate_Msite_Id_Exists
15 (
16 p_msite_id IN NUMBER,
17 x_return_status OUT NOCOPY VARCHAR2,
18 x_msg_count OUT NOCOPY NUMBER,
19 x_msg_data OUT NOCOPY VARCHAR2
20 )
21 IS
22 l_api_name CONSTANT VARCHAR2(30) :=
23 'Validate_Msite_Id_Exists';
24 l_api_version CONSTANT NUMBER := 1.0;
25 l_tmp_id NUMBER;
26
27 CURSOR c1(l_c_msite_id IN NUMBER)
28 IS SELECT msite_id FROM ibe_msites_b
29 WHERE msite_id = l_c_msite_id
30 AND master_msite_flag = 'N' and site_type = 'I';
31
32 BEGIN
33
34 -- Initialize status to SUCCESS
35 x_return_status := FND_API.G_RET_STS_SUCCESS;
36
37 -- Check if msite_id exists in ibe_msites_b
38 OPEN c1(p_msite_id);
39 FETCH c1 INTO l_tmp_id;
40 IF (c1%NOTFOUND) THEN
41 CLOSE c1;
42 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_NOT_FOUND');
43 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
44 FND_MSG_PUB.Add;
45 RAISE FND_API.G_EXC_ERROR;
46 END IF;
47 CLOSE c1;
48
49 EXCEPTION
50
51 WHEN FND_API.G_EXC_ERROR THEN
52 x_return_status := FND_API.G_RET_STS_ERROR;
53 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
54 p_data => x_msg_data,
55 p_encoded => 'F');
56
57 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
58 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
59 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
60 p_data => x_msg_data,
61 p_encoded => 'F');
62
63 WHEN OTHERS THEN
64 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
65 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
66 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
67 FND_MESSAGE.Set_Token('REASON', SQLERRM);
68 FND_MSG_PUB.Add;
69
70 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
71
72 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
73 THEN
74 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
75 END IF;
76
77 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
78 p_data => x_msg_data,
79 p_encoded => 'F');
80
81 END Validate_Msite_Id_Exists;
82
83 --
84 --
85 --
86 PROCEDURE Validate_Payment_Method_Code
87 (
88 p_payment_method_code IN VARCHAR2,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2
92 )
93 IS
94 l_api_name CONSTANT VARCHAR2(30) :=
95 'Validate_Payment_Method_Code';
96 l_api_version CONSTANT NUMBER := 1.0;
97 l_tmp_id NUMBER;
98
99 CURSOR c1(l_c_payment_method_code IN VARCHAR2)
100 IS SELECT 1 FROM fnd_lookups
101 WHERE lookup_type = 'IBE_PAYMENT_TYPE'
102 AND lookup_code = l_c_payment_method_code
103 AND enabled_flag = 'Y'
104 AND (sysdate BETWEEN NVL(start_date_active, sysdate)
105 AND NVL(end_date_active, sysdate));
106
107 BEGIN
108
109 -- Initialize status to SUCCESS
110 x_return_status := FND_API.G_RET_STS_SUCCESS;
111
112 -- Check if p_payment_method_code exists in fnd_lookups
113 OPEN c1(p_payment_method_code);
114 FETCH c1 INTO l_tmp_id;
115 IF (c1%NOTFOUND) THEN
116 CLOSE c1;
117 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PM_CODE_NOT_EXISTS');
118 FND_MESSAGE.Set_Token('PAYMENT_METHOD_CODE', p_payment_method_code);
119 FND_MSG_PUB.Add;
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122 CLOSE c1;
123
124 EXCEPTION
125
126 WHEN FND_API.G_EXC_ERROR THEN
127 x_return_status := FND_API.G_RET_STS_ERROR;
128 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
129 p_data => x_msg_data,
130 p_encoded => 'F');
131
132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
133 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
134 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
135 p_data => x_msg_data,
136 p_encoded => 'F');
137
138 WHEN OTHERS THEN
139 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
140 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
141 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
142 FND_MESSAGE.Set_Token('REASON', SQLERRM);
143 FND_MSG_PUB.Add;
144
145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
146
147 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
148 THEN
149 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
150 END IF;
151
152 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
153 p_data => x_msg_data,
154 p_encoded => 'F');
155
156 END Validate_Payment_Method_Code;
157
158 --
159 --
160 --
161 PROCEDURE Validate_Credit_Card_Code
162 (
163 p_credit_card_code IN VARCHAR2,
164 x_return_status OUT NOCOPY VARCHAR2,
165 x_msg_count OUT NOCOPY NUMBER,
166 x_msg_data OUT NOCOPY VARCHAR2
167 )
168 IS
169 l_api_name CONSTANT VARCHAR2(30) :=
170 'Validate_Credit_Card_Code';
171 l_api_version CONSTANT NUMBER := 1.0;
172 l_tmp_id NUMBER;
173
174 CURSOR c1(l_c_credit_card_code IN VARCHAR2)
175 IS SELECT 1 FROM iby_creditcard_issuers_v
176 WHERE card_issuer_code = l_c_credit_card_code;
177
178 BEGIN
179
180 -- Initialize status to SUCCESS
181 x_return_status := FND_API.G_RET_STS_SUCCESS;
182 -- Check if p_credit_card_code exists in oe_lookups
183 OPEN c1(p_credit_card_code);
184 FETCH c1 INTO l_tmp_id;
185 IF (c1%NOTFOUND) THEN
186 CLOSE c1;
187 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CC_CODE_NOT_EXISTS');
188 FND_MESSAGE.Set_Token('CREDIT_CARD_CODE', p_credit_card_code);
189 FND_MSG_PUB.Add;
190 RAISE FND_API.G_EXC_ERROR;
191 END IF;
192 CLOSE c1;
193
194 EXCEPTION
195
196 WHEN FND_API.G_EXC_ERROR THEN
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
199 p_data => x_msg_data,
200 p_encoded => 'F');
201
202 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
203 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
204 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
205 p_data => x_msg_data,
206 p_encoded => 'F');
207
208 WHEN OTHERS THEN
209 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
210 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
211 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
212 FND_MESSAGE.Set_Token('REASON', SQLERRM);
213 FND_MSG_PUB.Add;
214
215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
216
217 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
218 THEN
219 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
220 END IF;
221
222 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
223 p_data => x_msg_data,
224 p_encoded => 'F');
225
226 END Validate_Credit_Card_Code;
227
228 --
229 --
230 --
231 PROCEDURE Validate_Shipment_Method_Code
232 (
233 p_shipment_method_code IN VARCHAR2,
234 x_return_status OUT NOCOPY VARCHAR2,
235 x_msg_count OUT NOCOPY NUMBER,
236 x_msg_data OUT NOCOPY VARCHAR2
237 )
238 IS
239 l_api_name CONSTANT VARCHAR2(30) :=
240 'Validate_Shipment_Method_Code';
241 l_api_version CONSTANT NUMBER := 1.0;
242 l_tmp_id NUMBER;
243
244 CURSOR c1(l_c_shipment_method_code IN VARCHAR2)
245 IS SELECT 1 FROM oe_ship_methods_v
246 WHERE lookup_type = 'SHIP_METHOD'
247 AND lookup_code = l_c_shipment_method_code
248 AND enabled_flag = 'Y'
249 AND (sysdate BETWEEN NVL(start_date_active, sysdate)
250 AND NVL(end_date_active, sysdate));
251
252 BEGIN
253
254 -- Initialize status to SUCCESS
255 x_return_status := FND_API.G_RET_STS_SUCCESS;
256
257 -- Check if p_shipment_method_code exists in oe_ship_methods_v
258 OPEN c1(p_shipment_method_code);
259 FETCH c1 INTO l_tmp_id;
260 IF (c1%NOTFOUND) THEN
261 CLOSE c1;
262 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_SM_CODE_NOT_EXISTS');
263 FND_MESSAGE.Set_Token('SHIPMENT_METHOD_CODE', p_shipment_method_code);
264 FND_MSG_PUB.Add;
265 RAISE FND_API.G_EXC_ERROR;
266 END IF;
267 CLOSE c1;
268
269 EXCEPTION
270
271 WHEN FND_API.G_EXC_ERROR THEN
272 x_return_status := FND_API.G_RET_STS_ERROR;
273 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
274 p_data => x_msg_data,
275 p_encoded => 'F');
276
277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
279 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
280 p_data => x_msg_data,
281 p_encoded => 'F');
282
283 WHEN OTHERS THEN
284 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
285 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
286 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
287 FND_MESSAGE.Set_Token('REASON', SQLERRM);
288 FND_MSG_PUB.Add;
289
290 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
291
292 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
293 THEN
294 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
295 END IF;
296
297 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
298 p_data => x_msg_data,
299 p_encoded => 'F');
300
301 END Validate_Shipment_Method_Code;
302
303 --*****************************************************************************
304 -- PROCEDURE Check_Duplicate_Entry()
305 --*****************************************************************************
306
307 --
308 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if there is duplicate entry
309 -- x_return_status = FND_API.G_RET_STS_ERROR, if there is no duplicate entry
310 --
311 PROCEDURE Check_Duplicate_Entry
312 (
313 p_msite_id IN NUMBER,
314 p_msite_information_context IN VARCHAR2,
315 p_msite_information1 IN VARCHAR2,
316 p_msite_information2 IN VARCHAR2,
317 p_msite_information3 IN VARCHAR2,
318 p_msite_information4 IN VARCHAR2,
319 p_msite_information5 IN VARCHAR2,
320 p_msite_information6 IN VARCHAR2,
321 p_msite_information7 IN VARCHAR2,
322 p_msite_information8 IN VARCHAR2,
323 p_msite_information9 IN VARCHAR2,
324 p_msite_information10 IN VARCHAR2,
325 p_msite_information11 IN VARCHAR2,
326 p_msite_information12 IN VARCHAR2,
327 p_msite_information13 IN VARCHAR2,
328 p_msite_information14 IN VARCHAR2,
329 p_msite_information15 IN VARCHAR2,
330 p_msite_information16 IN VARCHAR2,
331 p_msite_information17 IN VARCHAR2,
332 p_msite_information18 IN VARCHAR2,
333 p_msite_information19 IN VARCHAR2,
334 p_msite_information20 IN VARCHAR2,
335 x_return_status OUT NOCOPY VARCHAR2,
336 x_msg_count OUT NOCOPY NUMBER,
337 x_msg_data OUT NOCOPY VARCHAR2
338 )
339 IS
340 l_api_name CONSTANT VARCHAR2(30) := 'Check_Duplicate_Entry';
341 l_api_version CONSTANT NUMBER := 1.0;
342 l_tmp_id NUMBER;
343
344 CURSOR c1(l_c_msite_id IN NUMBER, l_c_msite_information1 IN VARCHAR2)
345 IS SELECT 1 FROM ibe_msite_information
346 WHERE msite_information_context = 'PMT_MTHD'
347 AND msite_information1 = l_c_msite_information1
348 AND msite_id = l_c_msite_id;
349
350 CURSOR c2(l_c_msite_id IN NUMBER, l_c_msite_information1 IN VARCHAR2)
351 IS SELECT 1 FROM ibe_msite_information
352 WHERE msite_information_context = 'CC_TYPE'
353 AND msite_information1 = l_c_msite_information1
354 AND msite_id = l_c_msite_id;
355
356 CURSOR c3(l_c_msite_id IN NUMBER, l_c_msite_information1 IN VARCHAR2)
357 IS SELECT 1 FROM ibe_msite_information
358 WHERE msite_information_context = 'SHPMT_MTHD'
359 AND msite_information1 = l_c_msite_information1
360 AND msite_id = l_c_msite_id;
361
362 BEGIN
363
364 -- Initialize API return status to error, i.e, its not duplicate
365 x_return_status := FND_API.G_RET_STS_ERROR;
366
367 -- developer flexfield
368 IF (p_msite_information_context = 'PMT_MTHD') THEN
369
370 -- payment method
371 OPEN c1(p_msite_id, p_msite_information1);
372 FETCH c1 INTO l_tmp_id;
373 IF (c1%FOUND) THEN -- found duplicate
374 CLOSE c1;
375 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFORMATION_DUP');
376 FND_MESSAGE.Set_Token('VALUE', p_msite_information1);
377 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
378 FND_MESSAGE.Set_Token('CONTEXT', p_msite_information_context);
379 FND_MSG_PUB.Add;
380 RAISE FND_API.G_EXC_ERROR;
381 END IF;
382 CLOSE c1;
383
384 ELSIF (p_msite_information_context = 'CC_TYPE') THEN
385
386 -- credit card type
387 OPEN c2(p_msite_id, p_msite_information1);
388 FETCH c2 INTO l_tmp_id;
389 IF (c2%FOUND) THEN -- found duplicate
390 CLOSE c2;
391 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFORMATION_DUP');
392 FND_MESSAGE.Set_Token('VALUE', p_msite_information1);
393 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
394 FND_MESSAGE.Set_Token('CONTEXT', p_msite_information_context);
395 FND_MSG_PUB.Add;
396 RAISE FND_API.G_EXC_ERROR;
397 END IF;
398 CLOSE c2;
399
400 ELSIF (p_msite_information_context = 'SHPMT_MTHD') THEN
401
402 -- shipment method
403 OPEN c3(p_msite_id, p_msite_information1);
404 FETCH c3 INTO l_tmp_id;
405 IF (c3%FOUND) THEN -- found duplicate
406 CLOSE c3;
407 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFORMATION_DUP');
408 FND_MESSAGE.Set_Token('VALUE', p_msite_information1);
409 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
410 FND_MESSAGE.Set_Token('CONTEXT', p_msite_information_context);
411 FND_MSG_PUB.Add;
412 RAISE FND_API.G_EXC_ERROR;
413 END IF;
414 CLOSE c3;
415
416 END IF;
417
418 EXCEPTION
419
420 WHEN FND_API.G_EXC_ERROR THEN
421 x_return_status := FND_API.G_RET_STS_SUCCESS; -- found duplicate
422 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
423 p_data => x_msg_data,
424 p_encoded => 'F');
425
426 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
427 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
428 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
429 p_data => x_msg_data,
430 p_encoded => 'F');
431
432 WHEN OTHERS THEN
433 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
434 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
435 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
436 FND_MESSAGE.Set_Token('REASON', SQLERRM);
437 FND_MSG_PUB.Add;
438
439 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
440
441 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
442 THEN
443 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
444 END IF;
445
446 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
447 p_data => x_msg_data,
448 p_encoded => 'F');
449
450 END Check_Duplicate_Entry;
451
452 PROCEDURE Validate_Create
453 (
454 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
455 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
456 p_msite_id IN NUMBER,
457 p_msite_information_context IN VARCHAR2,
458 p_msite_information1 IN VARCHAR2,
459 p_msite_information2 IN VARCHAR2,
460 p_msite_information3 IN VARCHAR2,
461 p_msite_information4 IN VARCHAR2,
462 p_msite_information5 IN VARCHAR2,
463 p_msite_information6 IN VARCHAR2,
464 p_msite_information7 IN VARCHAR2,
465 p_msite_information8 IN VARCHAR2,
466 p_msite_information9 IN VARCHAR2,
467 p_msite_information10 IN VARCHAR2,
468 p_msite_information11 IN VARCHAR2,
469 p_msite_information12 IN VARCHAR2,
470 p_msite_information13 IN VARCHAR2,
471 p_msite_information14 IN VARCHAR2,
472 p_msite_information15 IN VARCHAR2,
473 p_msite_information16 IN VARCHAR2,
474 p_msite_information17 IN VARCHAR2,
475 p_msite_information18 IN VARCHAR2,
476 p_msite_information19 IN VARCHAR2,
477 p_msite_information20 IN VARCHAR2,
478 p_attribute_category IN VARCHAR2,
479 p_attribute1 IN VARCHAR2,
480 p_attribute2 IN VARCHAR2,
481 p_attribute3 IN VARCHAR2,
482 p_attribute4 IN VARCHAR2,
483 p_attribute5 IN VARCHAR2,
484 p_attribute6 IN VARCHAR2,
485 p_attribute7 IN VARCHAR2,
486 p_attribute8 IN VARCHAR2,
487 p_attribute9 IN VARCHAR2,
488 p_attribute10 IN VARCHAR2,
489 p_attribute11 IN VARCHAR2,
490 p_attribute12 IN VARCHAR2,
491 p_attribute13 IN VARCHAR2,
492 p_attribute14 IN VARCHAR2,
493 p_attribute15 IN VARCHAR2,
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2
497 )
498 IS
499 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create';
500 l_api_version CONSTANT NUMBER := 1.0;
501
502 l_return_status VARCHAR2(30);
503 BEGIN
504
505 -- Initialize message list if p_init_msg_list is set to TRUE.
506 IF FND_API.to_Boolean(p_init_msg_list) THEN
507 FND_MSG_PUB.initialize;
508 END IF;
509
510 -- Initialize API return status to success
511 x_return_status := FND_API.G_RET_STS_SUCCESS;
512
513 --
514 -- Check null values for required fields
515 --
516
517 IF ((p_msite_id IS NULL) OR
518 (p_msite_id = FND_API.G_MISS_NUM))
519 THEN
520 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_MSITE_ID');
521 FND_MSG_PUB.Add;
522 RAISE FND_API.G_EXC_ERROR;
523 END IF;
524
525 IF ((p_msite_information_context IS NULL) OR
526 (p_msite_information_context = FND_API.G_MISS_CHAR))
527 THEN
528 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVALID_INFO_CTX');
529 FND_MSG_PUB.Add;
530 RAISE FND_API.G_EXC_ERROR;
531 END IF;
532 -- developer flex-fields
533 IF ((p_msite_information_context = 'PMT_MTHD') OR
534 (p_msite_information_context = 'CC_TYPE') OR
535 (p_msite_information_context = 'SHPMT_MTHD'))
536 THEN
537
538 IF ((p_msite_information1 IS NULL) OR
539 (p_msite_information1 = FND_API.G_MISS_CHAR))
540 THEN
541 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVALID_MSITE_INFO');
542 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
543 FND_MESSAGE.Set_Token('CONTEXT', p_msite_information_context);
544 FND_MSG_PUB.Add;
545 RAISE FND_API.G_EXC_ERROR;
546 END IF;
547
548 END IF;
549
550 --
551 -- non-null field validation
552 --
553
554 --
555 -- Foreign key integrity constraint check
556 --
557
558 -- msite_id
559 Validate_Msite_Id_Exists
560 (
561 p_msite_id => p_msite_id,
562 x_return_status => x_return_status,
563 x_msg_count => x_msg_count,
564 x_msg_data => x_msg_data
565 );
566
567 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
568 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_VLD_FAIL');
569 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
570 FND_MSG_PUB.Add;
571 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
572 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
573 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSITE_ID_VLD_FAIL');
574 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
575 FND_MSG_PUB.Add;
576 RAISE FND_API.G_EXC_ERROR; -- invalid msite_id
577 END IF;
578 -- developer flexfield
579 IF (p_msite_information_context = 'PMT_MTHD') THEN
580
581 Validate_Payment_Method_Code
582 (
583 p_payment_method_code => p_msite_information1,
584 x_return_status => x_return_status,
585 x_msg_count => x_msg_count,
586 x_msg_data => x_msg_data
587 );
588
589 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
590 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PM_VLD_FAIL');
591 FND_MESSAGE.Set_Token('PAYMENT_METHOD_CODE', p_msite_information1);
592 FND_MSG_PUB.Add;
593 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
594 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
595 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PM_VLD_FAIL');
596 FND_MESSAGE.Set_Token('PAYMENT_METHOD_CODE', p_msite_information1);
597 FND_MSG_PUB.Add;
598 RAISE FND_API.G_EXC_ERROR;
599 END IF;
600 ELSIF (p_msite_information_context = 'CC_TYPE') THEN
601
602 Validate_Credit_Card_Code
603 (
604 p_credit_card_code => p_msite_information1,
605 x_return_status => x_return_status,
606 x_msg_count => x_msg_count,
607 x_msg_data => x_msg_data
608 );
609
610 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
611 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CC_VLD_FAIL');
612 FND_MESSAGE.Set_Token('CREDIT_CARD_CODE', p_msite_information1);
613 FND_MSG_PUB.Add;
614 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
615 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
616 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CC_VLD_FAIL');
617 FND_MESSAGE.Set_Token('CREDIT_CARD_CODE', p_msite_information1);
618 FND_MSG_PUB.Add;
619 RAISE FND_API.G_EXC_ERROR;
620 END IF;
621 ELSIF (p_msite_information_context = 'SHPMT_MTHD') THEN
622
623 Validate_Shipment_Method_Code
624 (
625 p_shipment_method_code => p_msite_information1,
626 x_return_status => x_return_status,
627 x_msg_count => x_msg_count,
628 x_msg_data => x_msg_data
629 );
630
631 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
632 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_SM_VLD_FAIL');
633 FND_MESSAGE.Set_Token('SHIPMENT_METHOD_CODE', p_msite_information1);
634 FND_MSG_PUB.Add;
635 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
636 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
637 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_SM_VLD_FAIL');
638 FND_MESSAGE.Set_Token('SHIPMENT_METHOD_CODE', p_msite_information1);
639 FND_MSG_PUB.Add;
640 RAISE FND_API.G_EXC_ERROR;
641 END IF;
642
643 END IF;
644 --
645 -- check for duplicate entry
646 --
647 Check_Duplicate_Entry
648 (
649 p_msite_id => p_msite_id,
650 p_msite_information_context => p_msite_information_context,
651 p_msite_information1 => p_msite_information1,
652 p_msite_information2 => p_msite_information2,
653 p_msite_information3 => p_msite_information3,
654 p_msite_information4 => p_msite_information4,
655 p_msite_information5 => p_msite_information5,
656 p_msite_information6 => p_msite_information6,
657 p_msite_information7 => p_msite_information7,
658 p_msite_information8 => p_msite_information8,
659 p_msite_information9 => p_msite_information9,
660 p_msite_information10 => p_msite_information10,
661 p_msite_information11 => p_msite_information11,
662 p_msite_information12 => p_msite_information12,
663 p_msite_information13 => p_msite_information13,
664 p_msite_information14 => p_msite_information14,
665 p_msite_information15 => p_msite_information15,
666 p_msite_information16 => p_msite_information16,
667 p_msite_information17 => p_msite_information17,
668 p_msite_information18 => p_msite_information18,
669 p_msite_information19 => p_msite_information19,
670 p_msite_information20 => p_msite_information20,
671 x_return_status => l_return_status,
672 x_msg_count => x_msg_count,
673 x_msg_data => x_msg_data
674 );
675
676 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
677 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_UNEXP_DUP_CHECK');
678 FND_MSG_PUB.Add;
679 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
680 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
681 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_DUPLICATE_MSITE_INFO');
682 FND_MESSAGE.Set_Token('MSITE_ID', p_msite_id);
683 FND_MESSAGE.Set_Token('CONTEXT', p_msite_information_context);
684 FND_MSG_PUB.Add;
685 RAISE FND_API.G_EXC_ERROR; -- duplicate entry
686 END IF;
687 EXCEPTION
688
689 WHEN FND_API.G_EXC_ERROR THEN
690 x_return_status := FND_API.G_RET_STS_ERROR;
691 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
692 p_data => x_msg_data,
693 p_encoded => 'F');
694
695 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
696 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
697 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
698 p_data => x_msg_data,
699 p_encoded => 'F');
700
701 WHEN OTHERS THEN
702 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
703 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
704 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
705 FND_MESSAGE.Set_Token('REASON', SQLERRM);
706 FND_MSG_PUB.Add;
707
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
709
710 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
711 THEN
712 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
713 END IF;
714
715 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
716 p_data => x_msg_data,
717 p_encoded => 'F');
718
719 END Validate_Create;
720
721 --
722 --
723 --
724 PROCEDURE Validate_Update
725 (
726 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
727 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
728 p_msite_information_id IN NUMBER,
729 p_object_version_number IN NUMBER,
730 p_msite_information1 IN VARCHAR2,
731 p_msite_information2 IN VARCHAR2,
732 p_msite_information3 IN VARCHAR2,
733 p_msite_information4 IN VARCHAR2,
734 p_msite_information5 IN VARCHAR2,
735 p_msite_information6 IN VARCHAR2,
736 p_msite_information7 IN VARCHAR2,
737 p_msite_information8 IN VARCHAR2,
738 p_msite_information9 IN VARCHAR2,
739 p_msite_information10 IN VARCHAR2,
740 p_msite_information11 IN VARCHAR2,
741 p_msite_information12 IN VARCHAR2,
742 p_msite_information13 IN VARCHAR2,
743 p_msite_information14 IN VARCHAR2,
744 p_msite_information15 IN VARCHAR2,
745 p_msite_information16 IN VARCHAR2,
746 p_msite_information17 IN VARCHAR2,
747 p_msite_information18 IN VARCHAR2,
748 p_msite_information19 IN VARCHAR2,
749 p_msite_information20 IN VARCHAR2,
750 p_attribute_category IN VARCHAR2,
751 p_attribute1 IN VARCHAR2,
752 p_attribute2 IN VARCHAR2,
753 p_attribute3 IN VARCHAR2,
754 p_attribute4 IN VARCHAR2,
755 p_attribute5 IN VARCHAR2,
756 p_attribute6 IN VARCHAR2,
757 p_attribute7 IN VARCHAR2,
758 p_attribute8 IN VARCHAR2,
759 p_attribute9 IN VARCHAR2,
760 p_attribute10 IN VARCHAR2,
761 p_attribute11 IN VARCHAR2,
762 p_attribute12 IN VARCHAR2,
763 p_attribute13 IN VARCHAR2,
764 p_attribute14 IN VARCHAR2,
765 p_attribute15 IN VARCHAR2,
766 x_return_status OUT NOCOPY VARCHAR2,
767 x_msg_count OUT NOCOPY NUMBER,
768 x_msg_data OUT NOCOPY VARCHAR2
769 )
770 IS
771 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update';
772 l_api_version CONSTANT NUMBER := 1.0;
773
774 l_msite_id NUMBER;
775 l_msite_information_context VARCHAR2(40);
776
777 CURSOR c1(l_c_msite_information_id IN NUMBER) IS
778 SELECT msite_information_context, msite_id FROM ibe_msite_information
779 WHERE msite_information_id = l_c_msite_information_id;
780
781 BEGIN
782
783 -- Initialize message list if p_init_msg_list is set to TRUE.
784 IF FND_API.to_Boolean(p_init_msg_list) THEN
785 FND_MSG_PUB.initialize;
786 END IF;
787
788 -- Initialize API return status to success
789 x_return_status := FND_API.G_RET_STS_SUCCESS;
790
791 --
792 -- Check null values for required fields
793 --
794
795 -- msite_information_id
796 IF (p_msite_information_id IS NULL) THEN
797 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_MSINF_ID_IS_NULL');
798 FND_MSG_PUB.Add;
799 RAISE FND_API.G_EXC_ERROR;
800 END IF;
801
802 -- object_version_number
803 IF (p_object_version_number IS NULL)
804 THEN
805 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_OVN_IS_NULL');
806 FND_MSG_PUB.Add;
807 RAISE FND_API.G_EXC_ERROR;
808 END IF;
809
810 --
811 -- non-null field validation
812 --
813
814 -- Get the msite_information_context
815 OPEN c1(p_msite_information_id);
816 FETCH c1 INTO l_msite_information_context, l_msite_id;
817 IF (c1%NOTFOUND) THEN
818 CLOSE c1;
819 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_ID_NOT_FOUND');
820 FND_MESSAGE.Set_Token('MSITE_INFO_ID', p_msite_information_id);
821 FND_MSG_PUB.Add;
822 RAISE FND_API.G_EXC_ERROR;
823 END IF;
824 CLOSE c1;
825
826 -- developer flexfield - check for null value
827 IF ((l_msite_information_context = 'PMT_MTHD') OR
828 (l_msite_information_context = 'CC_TYPE') OR
829 (l_msite_information_context = 'SHPMT_MTHD'))
830 THEN
831
832 IF ((p_msite_information1 IS NULL) OR
833 (p_msite_information1 = FND_API.G_MISS_CHAR))
834 THEN
835 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INVALID_MSITE_INFO');
836 FND_MESSAGE.Set_Token('MSITE_ID', l_msite_id);
837 FND_MESSAGE.Set_Token('CONTEXT', l_msite_information_context);
838 FND_MSG_PUB.Add;
839 RAISE FND_API.G_EXC_ERROR;
840 END IF;
841
842 END IF;
843
844 -- developer flexfield
845 IF (l_msite_information_context = 'PMT_MTHD') THEN
846
847 Validate_Payment_Method_Code
848 (
849 p_payment_method_code => p_msite_information1,
850 x_return_status => x_return_status,
851 x_msg_count => x_msg_count,
852 x_msg_data => x_msg_data
853 );
854
855 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
856 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PM_VLD_FAIL');
857 FND_MESSAGE.Set_Token('PAYMENT_METHOD_CODE', p_msite_information1);
858 FND_MSG_PUB.Add;
859 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
860 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
861 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_PM_VLD_FAIL');
862 FND_MESSAGE.Set_Token('PAYMENT_METHOD_CODE', p_msite_information1);
863 FND_MSG_PUB.Add;
864 RAISE FND_API.G_EXC_ERROR;
865 END IF;
866
867 ELSIF (l_msite_information_context = 'CC_TYPE') THEN
868
869 Validate_Credit_Card_Code
870 (
871 p_credit_card_code => p_msite_information1,
872 x_return_status => x_return_status,
873 x_msg_count => x_msg_count,
874 x_msg_data => x_msg_data
875 );
876
877 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
878 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CC_VLD_FAIL');
879 FND_MESSAGE.Set_Token('CREDIT_CARD_CODE', p_msite_information1);
880 FND_MSG_PUB.Add;
881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
882 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
883 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_CC_VLD_FAIL');
884 FND_MESSAGE.Set_Token('CREDIT_CARD_CODE', p_msite_information1);
885 FND_MSG_PUB.Add;
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888
889 ELSIF (l_msite_information_context = 'SHPMT_MTHD') THEN
890
891 Validate_Shipment_Method_Code
892 (
893 p_shipment_method_code => p_msite_information1,
894 x_return_status => x_return_status,
895 x_msg_count => x_msg_count,
896 x_msg_data => x_msg_data
897 );
898
899 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
900 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_SM_VLD_FAIL');
901 FND_MESSAGE.Set_Token('SHIPMENT_METHOD_CODE', p_msite_information1);
902 FND_MSG_PUB.Add;
903 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
904 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
905 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_SM_VLD_FAIL');
906 FND_MESSAGE.Set_Token('SHIPMENT_METHOD_CODE', p_msite_information1);
907 FND_MSG_PUB.Add;
908 RAISE FND_API.G_EXC_ERROR;
909 END IF;
910
911 END IF;
912
913 EXCEPTION
914
915 WHEN FND_API.G_EXC_ERROR THEN
916 x_return_status := FND_API.G_RET_STS_ERROR;
917 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
918 p_data => x_msg_data,
919 p_encoded => 'F');
920
921 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
922 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
923 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
924 p_data => x_msg_data,
925 p_encoded => 'F');
926
927 WHEN OTHERS THEN
928 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
929 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
930 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
931 FND_MESSAGE.Set_Token('REASON', SQLERRM);
932 FND_MSG_PUB.Add;
933
934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
935
936 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
937 THEN
938 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
939 END IF;
940
941 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
942 p_data => x_msg_data,
943 p_encoded => 'F');
944
945 END Validate_Update;
946
947
948 PROCEDURE Create_Msite_Information
949 (
950 p_api_version IN NUMBER,
951 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
952 p_commit IN VARCHAR2 := FND_API.G_FALSE,
953 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
954 p_msite_id IN NUMBER,
955 p_msite_information_context IN VARCHAR2,
956 p_msite_information1 IN VARCHAR2,
957 p_msite_information2 IN VARCHAR2,
958 p_msite_information3 IN VARCHAR2,
959 p_msite_information4 IN VARCHAR2,
960 p_msite_information5 IN VARCHAR2,
961 p_msite_information6 IN VARCHAR2,
962 p_msite_information7 IN VARCHAR2,
963 p_msite_information8 IN VARCHAR2,
964 p_msite_information9 IN VARCHAR2,
965 p_msite_information10 IN VARCHAR2,
966 p_msite_information11 IN VARCHAR2,
967 p_msite_information12 IN VARCHAR2,
968 p_msite_information13 IN VARCHAR2,
969 p_msite_information14 IN VARCHAR2,
970 p_msite_information15 IN VARCHAR2,
971 p_msite_information16 IN VARCHAR2,
972 p_msite_information17 IN VARCHAR2,
973 p_msite_information18 IN VARCHAR2,
974 p_msite_information19 IN VARCHAR2,
975 p_msite_information20 IN VARCHAR2,
976 p_attribute_category IN VARCHAR2,
977 p_attribute1 IN VARCHAR2,
978 p_attribute2 IN VARCHAR2,
979 p_attribute3 IN VARCHAR2,
980 p_attribute4 IN VARCHAR2,
981 p_attribute5 IN VARCHAR2,
982 p_attribute6 IN VARCHAR2,
983 p_attribute7 IN VARCHAR2,
984 p_attribute8 IN VARCHAR2,
985 p_attribute9 IN VARCHAR2,
986 p_attribute10 IN VARCHAR2,
987 p_attribute11 IN VARCHAR2,
988 p_attribute12 IN VARCHAR2,
989 p_attribute13 IN VARCHAR2,
990 p_attribute14 IN VARCHAR2,
991 p_attribute15 IN VARCHAR2,
992 x_msite_information_id OUT NOCOPY NUMBER,
993 x_return_status OUT NOCOPY VARCHAR2,
994 x_msg_count OUT NOCOPY NUMBER,
995 x_msg_data OUT NOCOPY VARCHAR2
996 )
997 IS
998 l_api_name CONSTANT VARCHAR2(30) := 'Create_Msite_Information';
999 l_api_version CONSTANT NUMBER := 1.0;
1000
1001 l_object_version_number CONSTANT NUMBER := 1;
1002 l_rowid VARCHAR2(30);
1003
1004 BEGIN
1005
1006 -- Standard Start of API savepoint
1007 SAVEPOINT create_msite_information_pvt;
1008
1009 -- Standard call to check for call compatibility.
1010 IF NOT FND_API.Compatible_API_Call(l_api_version,
1011 p_api_version,
1012 l_api_name,
1013 G_PKG_NAME)
1014 THEN
1015 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1016 END IF;
1017
1018 -- Initialize message list if p_init_msg_list is set to TRUE.
1019 IF FND_API.to_Boolean(p_init_msg_list) THEN
1020 FND_MSG_PUB.initialize;
1021 END IF;
1022 -- Initialize API return status to success
1023 x_return_status := FND_API.G_RET_STS_SUCCESS;
1024
1025 -- API body
1026 -- CALL FLOW :
1027 -- 1. Check if everything is valid
1028 -- 2. Insert row
1029
1030
1031 --
1032 -- 1. Check if everything is valid
1033 --
1034 Validate_Create
1035 (
1036 p_init_msg_list => FND_API.G_FALSE,
1037 p_validation_level => p_validation_level,
1038 p_msite_id => p_msite_id,
1039 p_msite_information_context => p_msite_information_context,
1040 p_msite_information1 => p_msite_information1,
1041 p_msite_information2 => p_msite_information2,
1042 p_msite_information3 => p_msite_information3,
1043 p_msite_information4 => p_msite_information4,
1044 p_msite_information5 => p_msite_information5,
1045 p_msite_information6 => p_msite_information6,
1046 p_msite_information7 => p_msite_information7,
1047 p_msite_information8 => p_msite_information8,
1048 p_msite_information9 => p_msite_information9,
1049 p_msite_information10 => p_msite_information10,
1050 p_msite_information11 => p_msite_information11,
1051 p_msite_information12 => p_msite_information12,
1052 p_msite_information13 => p_msite_information13,
1053 p_msite_information14 => p_msite_information14,
1054 p_msite_information15 => p_msite_information15,
1055 p_msite_information16 => p_msite_information16,
1056 p_msite_information17 => p_msite_information17,
1057 p_msite_information18 => p_msite_information18,
1058 p_msite_information19 => p_msite_information19,
1059 p_msite_information20 => p_msite_information20,
1060 p_attribute_category => p_attribute_category,
1061 p_attribute1 => p_attribute1,
1062 p_attribute2 => p_attribute2,
1063 p_attribute3 => p_attribute3,
1064 p_attribute4 => p_attribute4,
1065 p_attribute5 => p_attribute5,
1066 p_attribute6 => p_attribute6,
1067 p_attribute7 => p_attribute7,
1068 p_attribute8 => p_attribute8,
1069 p_attribute9 => p_attribute9,
1070 p_attribute10 => p_attribute10,
1071 p_attribute11 => p_attribute11,
1072 p_attribute12 => p_attribute12,
1073 p_attribute13 => p_attribute13,
1074 p_attribute14 => p_attribute14,
1075 p_attribute15 => p_attribute15,
1076 x_return_status => x_return_status,
1077 x_msg_count => x_msg_count,
1078 x_msg_data => x_msg_data
1079 );
1080 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1081 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_INVALID_CREATE');
1082 FND_MSG_PUB.Add;
1083 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1084 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1085 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_INVALID_CREATE');
1086 FND_MSG_PUB.Add;
1087 RAISE FND_API.G_EXC_ERROR; -- invalid
1088 END IF;
1089 --
1090 -- 2. Insert row
1091 --
1092 BEGIN
1093 Ibe_Msite_Information_Pkg.insert_row
1094 (
1095 p_msite_information_id => FND_API.G_MISS_NUM,
1096 p_object_version_number => l_object_version_number,
1097 p_msite_id => p_msite_id,
1098 p_msite_information_context => p_msite_information_context,
1099 p_msite_information1 => p_msite_information1,
1100 p_msite_information2 => p_msite_information2,
1101 p_msite_information3 => p_msite_information3,
1102 p_msite_information4 => p_msite_information4,
1103 p_msite_information5 => p_msite_information5,
1104 p_msite_information6 => p_msite_information6,
1105 p_msite_information7 => p_msite_information7,
1106 p_msite_information8 => p_msite_information8,
1107 p_msite_information9 => p_msite_information9,
1108 p_msite_information10 => p_msite_information10,
1109 p_msite_information11 => p_msite_information11,
1110 p_msite_information12 => p_msite_information12,
1111 p_msite_information13 => p_msite_information13,
1112 p_msite_information14 => p_msite_information14,
1113 p_msite_information15 => p_msite_information15,
1114 p_msite_information16 => p_msite_information16,
1115 p_msite_information17 => p_msite_information17,
1116 p_msite_information18 => p_msite_information18,
1117 p_msite_information19 => p_msite_information19,
1118 p_msite_information20 => p_msite_information20,
1119 p_attribute_category => p_attribute_category,
1120 p_attribute1 => p_attribute1,
1121 p_attribute2 => p_attribute2,
1122 p_attribute3 => p_attribute3,
1123 p_attribute4 => p_attribute4,
1124 p_attribute5 => p_attribute5,
1125 p_attribute6 => p_attribute6,
1126 p_attribute7 => p_attribute7,
1127 p_attribute8 => p_attribute8,
1128 p_attribute9 => p_attribute9,
1129 p_attribute10 => p_attribute10,
1130 p_attribute11 => p_attribute11,
1131 p_attribute12 => p_attribute12,
1132 p_attribute13 => p_attribute13,
1133 p_attribute14 => p_attribute14,
1134 p_attribute15 => p_attribute15,
1135 p_creation_date => sysdate,
1136 p_created_by => FND_GLOBAL.user_id,
1137 p_last_update_date => sysdate,
1138 p_last_updated_by => FND_GLOBAL.user_id,
1139 p_last_update_login => FND_GLOBAL.login_id,
1140 x_rowid => l_rowid,
1141 x_msite_information_id => x_msite_information_id
1142 );
1143
1144 EXCEPTION
1145 WHEN NO_DATA_FOUND THEN
1146 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_INSERT_FL');
1147 FND_MSG_PUB.Add;
1148 RAISE FND_API.G_EXC_ERROR;
1149 WHEN OTHERS THEN
1150 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1151 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1152 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1153 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1154 FND_MSG_PUB.Add;
1155
1156 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_INSERT_FL');
1157 FND_MSG_PUB.Add;
1158 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1159 END;
1160
1161 --
1162 -- End of main API body.
1163
1164 -- Standard check of p_commit.
1165 IF (FND_API.To_Boolean(p_commit)) THEN
1166 COMMIT WORK;
1167 END IF;
1168
1169 -- Standard call to get message count and if count is 1, get message info.
1170 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1171 p_data => x_msg_data,
1172 p_encoded => 'F');
1173
1174 EXCEPTION
1175
1176 WHEN FND_API.G_EXC_ERROR THEN
1177 ROLLBACK TO create_msite_information_pvt;
1178 x_return_status := FND_API.G_RET_STS_ERROR;
1179 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1180 p_data => x_msg_data,
1181 p_encoded => 'F');
1182
1183 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1184 ROLLBACK TO create_msite_information_pvt;
1185 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1186 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1187 p_data => x_msg_data,
1188 p_encoded => 'F');
1189
1190 WHEN OTHERS THEN
1191 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1192 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1193 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1194 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1195 FND_MSG_PUB.Add;
1196
1197 ROLLBACK TO create_msite_information_pvt;
1198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1199
1200 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1201 THEN
1202 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1203 END IF;
1204
1205 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1206 p_data => x_msg_data,
1207 p_encoded => 'F');
1208
1209 END Create_Msite_Information;
1210
1211 PROCEDURE Update_Msite_Information
1212 (
1213 p_api_version IN NUMBER,
1214 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1215 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1216 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1217 p_msite_information_id IN NUMBER,
1218 p_object_version_number IN NUMBER,
1219 p_msite_information1 IN VARCHAR2,
1220 p_msite_information2 IN VARCHAR2,
1221 p_msite_information3 IN VARCHAR2,
1222 p_msite_information4 IN VARCHAR2,
1223 p_msite_information5 IN VARCHAR2,
1224 p_msite_information6 IN VARCHAR2,
1225 p_msite_information7 IN VARCHAR2,
1226 p_msite_information8 IN VARCHAR2,
1227 p_msite_information9 IN VARCHAR2,
1228 p_msite_information10 IN VARCHAR2,
1229 p_msite_information11 IN VARCHAR2,
1230 p_msite_information12 IN VARCHAR2,
1231 p_msite_information13 IN VARCHAR2,
1232 p_msite_information14 IN VARCHAR2,
1233 p_msite_information15 IN VARCHAR2,
1234 p_msite_information16 IN VARCHAR2,
1235 p_msite_information17 IN VARCHAR2,
1236 p_msite_information18 IN VARCHAR2,
1237 p_msite_information19 IN VARCHAR2,
1238 p_msite_information20 IN VARCHAR2,
1239 p_attribute_category IN VARCHAR2,
1240 p_attribute1 IN VARCHAR2,
1241 p_attribute2 IN VARCHAR2,
1242 p_attribute3 IN VARCHAR2,
1243 p_attribute4 IN VARCHAR2,
1244 p_attribute5 IN VARCHAR2,
1245 p_attribute6 IN VARCHAR2,
1246 p_attribute7 IN VARCHAR2,
1247 p_attribute8 IN VARCHAR2,
1248 p_attribute9 IN VARCHAR2,
1249 p_attribute10 IN VARCHAR2,
1250 p_attribute11 IN VARCHAR2,
1251 p_attribute12 IN VARCHAR2,
1252 p_attribute13 IN VARCHAR2,
1253 p_attribute14 IN VARCHAR2,
1254 p_attribute15 IN VARCHAR2,
1255 x_return_status OUT NOCOPY VARCHAR2,
1256 x_msg_count OUT NOCOPY NUMBER,
1257 x_msg_data OUT NOCOPY VARCHAR2
1258 )
1259 IS
1260 l_api_name CONSTANT VARCHAR2(30) := 'Update_Msite_Information';
1261 l_api_version CONSTANT NUMBER := 1.0;
1262
1263 BEGIN
1264
1265 -- Standard Start of API savepoint
1266 SAVEPOINT update_msite_information_pvt;
1267
1268 -- Standard call to check for call compatibility.
1269 IF NOT FND_API.Compatible_API_Call(l_api_version,
1270 p_api_version,
1271 l_api_name,
1272 G_PKG_NAME)
1273 THEN
1274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1275 END IF;
1276
1277 -- Initialize message list if p_init_msg_list is set to TRUE.
1278 IF FND_API.to_Boolean(p_init_msg_list) THEN
1279 FND_MSG_PUB.initialize;
1280 END IF;
1281
1282 -- Initialize API return status to success
1283 x_return_status := FND_API.G_RET_STS_SUCCESS;
1284
1285 -- API body
1286 -- CALL FLOW :
1287 -- 1. Check if everything is valid
1288 -- 2. Update row
1289 --
1290 -- 1. Validate the input data
1291 --
1292 Validate_Update
1293 (
1294 p_init_msg_list => FND_API.G_FALSE,
1295 p_validation_level => p_validation_level,
1296 p_msite_information_id => p_msite_information_id,
1297 p_object_version_number => p_object_version_number,
1298 p_msite_information1 => p_msite_information1,
1299 p_msite_information2 => p_msite_information2,
1300 p_msite_information3 => p_msite_information3,
1301 p_msite_information4 => p_msite_information4,
1302 p_msite_information5 => p_msite_information5,
1303 p_msite_information6 => p_msite_information6,
1304 p_msite_information7 => p_msite_information7,
1305 p_msite_information8 => p_msite_information8,
1306 p_msite_information9 => p_msite_information9,
1307 p_msite_information10 => p_msite_information10,
1308 p_msite_information11 => p_msite_information11,
1309 p_msite_information12 => p_msite_information12,
1310 p_msite_information13 => p_msite_information13,
1311 p_msite_information14 => p_msite_information14,
1312 p_msite_information15 => p_msite_information15,
1313 p_msite_information16 => p_msite_information16,
1314 p_msite_information17 => p_msite_information17,
1315 p_msite_information18 => p_msite_information18,
1316 p_msite_information19 => p_msite_information19,
1317 p_msite_information20 => p_msite_information20,
1318 p_attribute_category => p_attribute_category,
1319 p_attribute1 => p_attribute1,
1320 p_attribute2 => p_attribute2,
1321 p_attribute3 => p_attribute3,
1322 p_attribute4 => p_attribute4,
1323 p_attribute5 => p_attribute5,
1324 p_attribute6 => p_attribute6,
1325 p_attribute7 => p_attribute7,
1326 p_attribute8 => p_attribute8,
1327 p_attribute9 => p_attribute9,
1328 p_attribute10 => p_attribute10,
1329 p_attribute11 => p_attribute11,
1330 p_attribute12 => p_attribute12,
1331 p_attribute13 => p_attribute13,
1332 p_attribute14 => p_attribute14,
1333 p_attribute15 => p_attribute15,
1334 x_return_status => x_return_status,
1335 x_msg_count => x_msg_count,
1336 x_msg_data => x_msg_data
1337 );
1338
1339 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1340 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_INVALID_UPDATE');
1341 FND_MSG_PUB.Add;
1342 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1343 ELSIF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1344 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_INVALID_UPDATE');
1345 FND_MSG_PUB.Add;
1346 RAISE FND_API.G_EXC_ERROR; -- invalid
1347 END IF;
1348
1349 -- 2. update row with section data into section table
1350 BEGIN
1351 Ibe_Msite_Information_Pkg.update_row
1352 (
1353 p_msite_information_id => p_msite_information_id,
1354 p_object_version_number => p_object_version_number,
1355 p_msite_information1 => p_msite_information1,
1356 p_msite_information2 => p_msite_information2,
1357 p_msite_information3 => p_msite_information3,
1358 p_msite_information4 => p_msite_information4,
1359 p_msite_information5 => p_msite_information5,
1360 p_msite_information6 => p_msite_information6,
1361 p_msite_information7 => p_msite_information7,
1362 p_msite_information8 => p_msite_information8,
1363 p_msite_information9 => p_msite_information9,
1364 p_msite_information10 => p_msite_information10,
1365 p_msite_information11 => p_msite_information11,
1366 p_msite_information12 => p_msite_information12,
1367 p_msite_information13 => p_msite_information13,
1368 p_msite_information14 => p_msite_information14,
1369 p_msite_information15 => p_msite_information15,
1370 p_msite_information16 => p_msite_information16,
1371 p_msite_information17 => p_msite_information17,
1372 p_msite_information18 => p_msite_information18,
1373 p_msite_information19 => p_msite_information19,
1374 p_msite_information20 => p_msite_information20,
1375 p_attribute_category => p_attribute_category,
1376 p_attribute1 => p_attribute1,
1377 p_attribute2 => p_attribute2,
1378 p_attribute3 => p_attribute3,
1379 p_attribute4 => p_attribute4,
1380 p_attribute5 => p_attribute5,
1381 p_attribute6 => p_attribute6,
1382 p_attribute7 => p_attribute7,
1383 p_attribute8 => p_attribute8,
1384 p_attribute9 => p_attribute9,
1385 p_attribute10 => p_attribute10,
1386 p_attribute11 => p_attribute11,
1387 p_attribute12 => p_attribute12,
1388 p_attribute13 => p_attribute13,
1389 p_attribute14 => p_attribute14,
1390 p_attribute15 => p_attribute15,
1391 p_last_update_date => sysdate,
1392 p_last_updated_by => FND_GLOBAL.user_id,
1393 p_last_update_login => FND_GLOBAL.login_id
1394 );
1395
1396 EXCEPTION
1397 WHEN NO_DATA_FOUND THEN
1398 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_UPDATE_FL');
1399 FND_MSG_PUB.ADD;
1400 RAISE FND_API.G_EXC_ERROR;
1401 WHEN OTHERS THEN
1402 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1403 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1404 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1405 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1406 FND_MSG_PUB.Add;
1407
1408 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_UPDATE_FL');
1409 FND_MSG_PUB.ADD;
1410 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1411 END;
1412
1413 --
1414 -- end of main api body.
1415
1416 -- standard check of p_commit.
1417 IF (FND_API.to_boolean(p_commit)) THEN
1418 COMMIT WORK;
1419 END IF;
1420
1421 -- standard call to get message count and if count is 1, get message info.
1422 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1423 p_data => x_msg_data,
1424 p_encoded => 'F');
1425
1426 EXCEPTION
1427
1428 WHEN FND_API.G_EXC_ERROR THEN
1429 ROLLBACK TO update_msite_information_pvt;
1430 x_return_status := FND_API.G_RET_STS_ERROR;
1431 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1432 p_data => x_msg_data,
1433 p_encoded => 'F');
1434
1435 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1436 ROLLBACK TO update_msite_information_pvt;
1437 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1438 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1439 p_data => x_msg_data,
1440 p_encoded => 'F');
1441
1442 WHEN OTHERS THEN
1443 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1444 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1445 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1446 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1447 FND_MSG_PUB.Add;
1448
1449 ROLLBACK TO update_msite_information_pvt;
1450 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1451
1452 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1453 THEN
1454 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1455 END IF;
1456
1457 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1458 p_data => x_msg_data,
1459 p_encoded => 'F');
1460
1461 END Update_Msite_Information;
1462
1463 PROCEDURE Delete_Msite_Information
1464 (
1465 p_api_version IN NUMBER,
1466 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1467 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1468 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1469 p_msite_information_id IN NUMBER := FND_API.G_MISS_NUM,
1470 x_return_status OUT NOCOPY VARCHAR2,
1471 x_msg_count OUT NOCOPY NUMBER,
1472 x_msg_data OUT NOCOPY VARCHAR2
1473 )
1474 IS
1475 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Msite_Information';
1476 l_api_version CONSTANT NUMBER := 1.0;
1477
1478 BEGIN
1479
1480 -- Standard Start of API savepoint
1481 SAVEPOINT delete_msite_information_pvt;
1482
1483 -- Standard call to check for call compatibility.
1484 IF NOT FND_API.Compatible_API_Call(l_api_version,
1485 p_api_version,
1486 l_api_name,
1487 G_PKG_NAME)
1488 THEN
1489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1490 END IF;
1491
1492 -- Initialize message list if p_init_msg_list is set to TRUE.
1493 IF FND_API.to_Boolean(p_init_msg_list) THEN
1494 FND_MSG_PUB.initialize;
1495 END IF;
1496
1497 -- Initialize API return status to success
1498 x_return_status := FND_API.G_RET_STS_SUCCESS;
1499
1500 -- CALL FLOW
1501
1502 -- 1a. Check if msite_information_id is specified
1503 IF ((p_msite_information_id IS NULL) OR
1504 (p_msite_information_id = FND_API.G_MISS_NUM))
1505 THEN
1506 -- msite_information_id is not specified
1507 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_NO_MINFO_ID_SPEC');
1508 FND_MSG_PUB.Add;
1509 RAISE FND_API.G_EXC_ERROR;
1510 END IF;
1511
1512 -- delete for ibe_msite_information
1513 BEGIN
1514 Ibe_Msite_Information_Pkg.delete_row(p_msite_information_id);
1515 EXCEPTION
1516 WHEN NO_DATA_FOUND THEN
1517 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_DELETE_FL');
1518 FND_MSG_PUB.Add;
1519 RAISE FND_API.G_EXC_ERROR;
1520 WHEN OTHERS THEN
1521 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1522 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1523 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1524 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1525 FND_MSG_PUB.Add;
1526
1527 FND_MESSAGE.Set_Name('IBE', 'IBE_MSITE_INFO_DELETE_FL');
1528 FND_MSG_PUB.Add;
1529 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1530 END;
1531
1532 --
1533 -- end of main api body.
1534
1535 -- standard check of p_commit.
1536 IF (FND_API.to_boolean(p_commit)) THEN
1537 COMMIT WORK;
1538 END IF;
1539
1540 -- standard call to get message count and if count is 1, get message info.
1541 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1542 p_data => x_msg_data,
1543 p_encoded => 'F');
1544
1545 EXCEPTION
1546
1547 WHEN FND_API.G_EXC_ERROR THEN
1548 ROLLBACK TO delete_msite_information_pvt;
1549 x_return_status := FND_API.G_RET_STS_ERROR;
1550 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1551 p_data => x_msg_data,
1552 p_encoded => 'F');
1553
1554 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1555 ROLLBACK TO delete_msite_information_pvt;
1556 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1557 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1558 p_data => x_msg_data,
1559 p_encoded => 'F');
1560
1561 WHEN OTHERS THEN
1562 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1563 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1564 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1565 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1566 FND_MSG_PUB.Add;
1567
1568 ROLLBACK TO delete_msite_information_pvt;
1569 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1570
1571 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1572 THEN
1573 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1574 END IF;
1575
1576 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1577 p_data => x_msg_data,
1578 p_encoded => 'F');
1579
1580 END Delete_Msite_Information;
1581
1582 END Ibe_Msite_Information_Pvt;