[Home] [Help]
PACKAGE BODY: APPS.IBE_DSP_SECTION_ITEM_PVT
Source
1 PACKAGE BODY IBE_DSP_SECTION_ITEM_PVT AS
2 /* $Header: IBEVCISB.pls 120.0 2005/05/30 02:31:07 appldev noship $ */
3
4 --
5 --
6 -- Start of Comments
7 --
8 -- NAME
9 -- IBE_DSP_SECTION_ITEM_PVT
10 --
11 -- PURPOSE
12 -- Private API for saving, retrieving and updating section items
13 --
14 -- NOTES
15 -- This is a pulicly accessible package. It should be used by all
16 -- sources for saving, retrieving and updating section items
17
18 -- HISTORY
19 -- 11/28/99 VPALAIYA Created
20 -- 12/12/02 SCHAK Modified for NOCOPY (Bug # 2691704) and Debug (Bug # 2691710) Changes.
21 -- 12/19/02 SCHAK Modified for reverting Debug (IBEUtil) Changes.
22 -- 12/21/02 SCHAK Modified for NOCOPY (Bug # 2691704)) Changes and adding exceptions.
23 -- **********************************************************************************************************
24
25 G_PKG_NAME CONSTANT VARCHAR2(30):= 'IBE_DSP_SECTION_ITEM_PVT';
26 G_FILE_NAME CONSTANT VARCHAR2(12):= 'IBEVCISB.pls';
27
28
29 -- ****************************************************************************
30 -- ****************************************************************************
31 -- TABLE HANDLERS
32 -- 1. insert_row
33 -- 2. update_row
34 -- 3. delete_row
35 -- ****************************************************************************
36 -- ****************************************************************************
37
38
39 -- ****************************************************************************
40 -- insert row into section-items
41 -- ****************************************************************************
42
43 PROCEDURE insert_row
44 (
45 p_section_item_id IN NUMBER,
46 p_object_version_number IN NUMBER,
47 p_section_id IN NUMBER,
48 p_inventory_item_id IN NUMBER,
49 p_organization_id IN NUMBER,
50 p_start_date_active IN DATE,
51 p_end_date_active IN DATE,
52 p_usage_name IN VARCHAR2,
53 p_sort_order IN NUMBER,
54 p_association_reason_code IN VARCHAR2,
55 p_creation_date IN DATE,
56 p_created_by IN NUMBER,
57 p_last_update_date IN DATE,
58 p_last_updated_by IN NUMBER,
59 p_last_update_login IN NUMBER,
60 x_rowid OUT NOCOPY VARCHAR2,
61 x_section_item_id OUT NOCOPY NUMBER
62 )
63 IS
64 CURSOR c IS SELECT rowid FROM ibe_dsp_section_items
65 WHERE section_item_id = x_section_item_id;
66 CURSOR c2 IS SELECT ibe_dsp_section_items_s1.nextval FROM dual;
67
68 BEGIN
69
70 -- Primary key validation check
71 x_section_item_id := p_section_item_id;
72 IF ((x_section_item_id IS NULL) OR
73 (x_section_item_id = FND_API.G_MISS_NUM))
74 THEN
75 OPEN c2;
76 FETCH c2 INTO x_section_item_id;
77 CLOSE c2;
78 END IF;
79
80 -- insert base
81 INSERT INTO ibe_dsp_section_items
82 (
83 section_item_id,
84 object_version_number,
85 section_id,
86 inventory_item_id,
87 organization_id,
88 start_date_active,
89 end_date_active,
90 usage_name,
91 sort_order,
92 association_reason_code,
93 creation_date,
94 created_by,
95 last_update_date,
96 last_updated_by,
97 last_update_login
98 )
99 VALUES
100 (
101 x_section_item_id,
102 p_object_version_number,
103 p_section_id,
104 p_inventory_item_id,
105 p_organization_id,
106 p_start_date_active,
107 decode(p_end_date_active, FND_API.G_MISS_DATE, NULL, p_end_date_active),
108 decode(p_usage_name, FND_API.G_MISS_CHAR, NULL, p_usage_name),
109 decode(p_sort_order, FND_API.G_MISS_NUM, NULL, p_sort_order),
110 decode(p_association_reason_code, FND_API.G_MISS_CHAR,
111 NULL, p_association_reason_code),
112 decode(p_creation_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
113 p_creation_date),
114 decode(p_created_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
115 NULL, FND_GLOBAL.user_id, p_created_by),
116 decode(p_last_update_date, FND_API.G_MISS_DATE, sysdate, NULL, sysdate,
117 p_last_update_date),
118 decode(p_last_updated_by, FND_API.G_MISS_NUM, FND_GLOBAL.user_id,
119 NULL, FND_GLOBAL.user_id, p_last_updated_by),
120 decode(p_last_update_login, FND_API.G_MISS_NUM, FND_GLOBAL.login_id,
121 NULL, FND_GLOBAL.login_id, p_last_update_login)
122 );
123
124 OPEN c;
125 FETCH c INTO x_rowid;
126 IF (c%NOTFOUND)
127 THEN
128 CLOSE c;
129 RAISE NO_DATA_FOUND;
130 END IF;
131 CLOSE c;
132
133 END insert_row;
134
135 -- ****************************************************************************
136 -- update row
137 -- ****************************************************************************
138
139 PROCEDURE update_row
140 (
141 p_section_item_id IN NUMBER,
142 p_object_version_number IN NUMBER := FND_API.G_MISS_NUM,
143 p_start_date_active IN DATE,
144 p_end_date_active IN DATE,
145 p_usage_name IN VARCHAR2,
146 p_sort_order IN NUMBER,
147 p_association_reason_code IN VARCHAR2,
148 p_last_update_date IN DATE,
149 p_last_updated_by IN NUMBER,
150 p_last_update_login IN NUMBER
151 )
152 IS
153 BEGIN
154
155 -- update base
156 UPDATE ibe_dsp_section_items SET
157 object_version_number = object_version_number + 1,
158 sort_order = decode(p_sort_order, FND_API.G_MISS_NUM,
159 sort_order, p_sort_order),
160 association_reason_code =
161 decode(p_association_reason_code, FND_API.G_MISS_CHAR,
162 association_reason_code, p_association_reason_code),
163 start_date_active = decode(p_start_date_active, FND_API.G_MISS_DATE,
164 start_date_active, p_start_date_active),
165 end_date_active = decode(p_end_date_active, FND_API.G_MISS_DATE,
166 end_date_active, p_end_date_active),
167 usage_name = decode(p_usage_name, FND_API.G_MISS_CHAR, usage_name, p_usage_name),
168 last_update_date = decode(p_last_update_date, FND_API.G_MISS_DATE,
169 sysdate, NULL, sysdate, p_last_update_date),
170 last_updated_by = decode(p_last_updated_by, FND_API.G_MISS_NUM,
171 FND_GLOBAL.user_id, NULL, FND_GLOBAL.user_id,
172 p_last_updated_by),
173 last_update_login = decode(p_last_update_login, FND_API.G_MISS_NUM,
174 FND_GLOBAL.login_id, NULL,
175 FND_GLOBAL.login_id, p_last_update_login)
176 WHERE section_item_id = p_section_item_id
177 AND object_version_number = decode(p_object_version_number,
178 FND_API.G_MISS_NUM,
179 object_version_number,
180 p_object_version_number);
181
182 IF (sql%NOTFOUND) THEN
183 RAISE NO_DATA_FOUND;
184 END IF;
185
186 END update_row;
187
188
189 -- ****************************************************************************
190 -- delete row
191 -- ****************************************************************************
192
193 PROCEDURE delete_row
194 (
195 p_section_item_id IN NUMBER
196 )
197 IS
198 BEGIN
199
200 DELETE FROM ibe_dsp_section_items
201 WHERE section_item_id = p_section_item_id;
202
203 IF (sql%NOTFOUND) THEN
204 RAISE NO_DATA_FOUND;
205 END IF;
206
207 END delete_row;
208
209 -- ****************************************************************************
210 --*****************************************************************************
211 --
212 --APIs
213 --
214 -- 1. Create_Section_Item
215 -- 2. Update_Section_Item
216 -- 3. Delete_Section_Item
217 -- 4. Check_Duplicate_Entry
218 --
219 --*****************************************************************************
220 --*****************************************************************************
221
222
223 --*****************************************************************************
224 -- PROCEDURE Check_Duplicate_Entry()
225 --*****************************************************************************
226
227 --
228 -- x_return_status = FND_API.G_RET_STS_SUCCESS, if the section is duplicate
229 -- x_return_status = FND_API.G_RET_STS_ERROR, if the section is not duplicate
230 --
231 --
232 PROCEDURE Check_Duplicate_Entry
233 (
234 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
235 p_section_id IN NUMBER,
236 p_inventory_item_id IN NUMBER,
237 p_organization_id IN NUMBER,
238 x_return_status OUT NOCOPY VARCHAR2,
239 x_msg_count OUT NOCOPY NUMBER,
240 x_msg_data OUT NOCOPY VARCHAR2
241 )
242 IS
243 l_api_name CONSTANT VARCHAR2(30) := 'Check_Duplicate_Entry';
244 l_api_version CONSTANT NUMBER := 1.0;
245
246 l_tmp_section_item_id NUMBER;
247 BEGIN
248
249 -- Initialize message list if p_init_msg_list is set to TRUE.
250 IF FND_API.to_Boolean(p_init_msg_list) THEN
251 FND_MSG_PUB.initialize;
252 END IF;
253
254 -- Initialize API return status to error, i.e, its not duplicate
255 x_return_status := FND_API.G_RET_STS_ERROR;
256
257 -- Check duplicate entry
258 BEGIN
259
260 SELECT section_item_id INTO l_tmp_section_item_id
261 FROM ibe_dsp_section_items
262 WHERE section_id = p_section_id
263 AND inventory_item_id = p_inventory_item_id
264 AND organization_id = p_organization_id;
265
266 EXCEPTION
267
268 WHEN NO_DATA_FOUND THEN
269 -- not duplicate
270 -- do nothing
271 NULL;
272
273 WHEN OTHERS THEN
274 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
275 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
276 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
277 FND_MESSAGE.Set_Token('REASON', SQLERRM);
278 FND_MSG_PUB.Add;
279
280 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
281
282 END;
283
284 IF (l_tmp_section_item_id IS NOT NULL) THEN
285 -- found duplicate
286 RAISE FND_API.G_EXC_ERROR;
287 END IF;
288
289 EXCEPTION
290
291 WHEN FND_API.G_EXC_ERROR THEN
292 x_return_status := FND_API.G_RET_STS_SUCCESS; -- found duplicate
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 FND_API.G_EXC_UNEXPECTED_ERROR THEN
298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
299 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
300 p_data => x_msg_data,
301 p_encoded => 'F');
302
303 WHEN OTHERS THEN
304 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
305 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
306 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
307 FND_MESSAGE.Set_Token('REASON', SQLERRM);
308 FND_MSG_PUB.Add;
309
310 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
311
312 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
313 THEN
314 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
315 END IF;
316
317 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
318 p_data => x_msg_data,
319 p_encoded => 'F');
320
321 END Check_Duplicate_Entry;
322
323
324 --*****************************************************************************
325 -- PROCEDURE Validate_Create()
326 --*****************************************************************************
327 -- IF x_return_status := FND_API.G_RET_STS_ERROR, then invalid
328 -- IF x_return_status := FND_API.G_RET_STS_SUCCESS, then valid
329
330 PROCEDURE Validate_Create
331 (
332 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
333 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
334 p_section_id IN NUMBER,
335 p_inventory_item_id IN NUMBER,
336 p_organization_id IN NUMBER,
337 p_start_date_active IN DATE,
338 p_end_date_active IN DATE,
339 p_sort_order IN NUMBER,
340 p_association_reason_code IN VARCHAR2,
341 x_return_status OUT NOCOPY VARCHAR2,
342 x_msg_count OUT NOCOPY NUMBER,
343 x_msg_data OUT NOCOPY VARCHAR2
344 )
345 IS
346 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Create';
347 l_api_version CONSTANT NUMBER := 1.0;
348 l_msg_count NUMBER;
349 l_msg_data VARCHAR2(2000);
350
351 l_section_item_id NUMBER;
352 l_section_id NUMBER;
353 l_inventory_item_id NUMBER;
354 l_organization_id NUMBER;
355 l_return_status VARCHAR2(1);
356 l_tmp_id NUMBER;
357 /*
358 CURSOR c1(l_c_section_id IN NUMBER)
359 IS SELECT mini_site_section_section_id FROM ibe_dsp_msite_sct_sects
360 WHERE parent_section_id = l_c_section_id
361 AND EXISTS (SELECT msite_id FROM ibe_msites_b
362 WHERE msite_id = mini_site_id
363 AND master_msite_flag = 'Y');
364 */
365 l_master_mini_site_id NUMBER;
366 l_master_root_section_id NUMBER;
367 CURSOR c1(l_c_section_id IN NUMBER, l_c_minisite_id IN NUMBER)
368 IS SELECT mini_site_section_section_id FROM ibe_dsp_msite_sct_sects
369 WHERE parent_section_id = l_c_section_id
370 AND mini_site_id = l_c_minisite_id;
371 BEGIN
372
373 -- Initialize message list if p_init_msg_list is set to TRUE.
374 IF FND_API.to_Boolean(p_init_msg_list) THEN
375 FND_MSG_PUB.initialize;
376 END IF;
377
378 -- Initialize API return status to success
379 x_return_status := FND_API.G_RET_STS_SUCCESS;
380
381 --
382 -- Check null values for required fields
383 --
384
385 -- section id
386 IF ((p_section_id IS NULL) OR
387 (p_section_id = FND_API.G_MISS_NUM))
388 THEN
389 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_SCT_ID');
390 FND_MSG_PUB.Add;
391 RAISE FND_API.G_EXC_ERROR;
392 END IF;
393
394 -- inventory item id
395 IF ((p_inventory_item_id IS NULL) OR
396 (p_inventory_item_id = FND_API.G_MISS_NUM))
397 THEN
398 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_INV_ITEM_ID');
399 FND_MSG_PUB.Add;
400 RAISE FND_API.G_EXC_ERROR;
401 END IF;
402
403 -- organization id
404 IF ((p_organization_id IS NULL) OR
405 (p_organization_id = FND_API.G_MISS_NUM))
406 THEN
407 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_INV_ORG_ID');
408 FND_MSG_PUB.Add;
409 RAISE FND_API.G_EXC_ERROR;
410 END IF;
411
412 -- start_date_active
413 IF ((p_start_date_active IS NULL) OR
414 (p_start_date_active = FND_API.G_MISS_DATE))
415 THEN
416 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_START_DATE');
417 FND_MSG_PUB.Add;
418 RAISE FND_API.G_EXC_ERROR;
419 END IF;
420
421 --
422 -- non-null field validation
423 --
424
425 -- sort order
426 IF ((p_sort_order IS NOT NULL) AND
427 (p_sort_order <> FND_API.G_MISS_NUM))
428 THEN
429 IF(p_sort_order < 0) THEN
430 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_SCT_SORT_ORDER');
431 FND_MSG_PUB.Add;
432 RAISE FND_API.G_EXC_ERROR;
433 END IF;
434 END IF;
435
436 --
437 -- Foreign key integrity constraint check
438 --
439
440 -- section id
441 -- note that section id cannot be null due to previous checks
442 BEGIN
443 SELECT section_id INTO l_section_id FROM ibe_dsp_sections_b
444 WHERE section_id = p_section_id;
445 EXCEPTION
446 WHEN NO_DATA_FOUND THEN
447 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_NO_SCT_ID');
448 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
449 FND_MSG_PUB.Add;
450 RAISE FND_API.G_EXC_ERROR;
451 WHEN OTHERS THEN
452 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
453 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
454 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
455 FND_MESSAGE.Set_Token('REASON', SQLERRM);
456 FND_MSG_PUB.Add;
457
458 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_SCT_ID');
459 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
460 FND_MSG_PUB.Add;
461 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
462 END;
463
464 -- inventory item id and organization id
465 BEGIN
466
467 SELECT inventory_item_id INTO l_inventory_item_id
468 FROM mtl_system_items
469 WHERE inventory_item_id = p_inventory_item_id
470 AND organization_id = p_organization_id;
471
472 SELECT organization_id INTO l_organization_id
473 FROM mtl_system_items
474 WHERE inventory_item_id = p_inventory_item_id
475 AND organization_id = p_organization_id;
476
477 EXCEPTION
478 WHEN NO_DATA_FOUND THEN
479 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_INV_ITEM_ID');
480 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
481 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
482 FND_MSG_PUB.Add;
483 RAISE FND_API.G_EXC_ERROR;
484 WHEN OTHERS THEN
485 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
486 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
487 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
488 FND_MESSAGE.Set_Token('REASON', SQLERRM);
489 FND_MSG_PUB.Add;
490
491 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_GET_INV_ITEM_ID');
492 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
493 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
494 FND_MSG_PUB.Add;
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 END;
497
498 -- Validate if the entry is duplicate
499 Check_Duplicate_Entry
500 (
501 p_init_msg_list => FND_API.G_FALSE,
502 p_section_id => p_section_id,
503 p_inventory_item_id => p_inventory_item_id,
504 p_organization_id => p_organization_id,
505 x_return_status => l_return_status,
506 x_msg_count => l_msg_count,
507 x_msg_data => l_msg_data);
508
509 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 ELSIF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
512 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_DUPLICATE_ENTRY');
513 FND_MSG_PUB.Add;
514 RAISE FND_API.G_EXC_ERROR; -- duplicate entry
515 END IF;
516
517 --
518 -- Check if the p_section_id doesn't have any child as sections
519 -- Cannot create items for a section which has child sections
520 --
521 -- Performance bug fix 2854734
522 IBE_DSP_HIERARCHY_SETUP_PVT.Get_Master_Mini_Site_Id(
523 x_mini_site_id => l_master_mini_site_id,
524 x_root_section_id => l_master_root_section_id);
525 -- OPEN c1(p_section_id);
526 OPEN c1(p_section_id, l_master_mini_site_id);
527 FETCH c1 INTO l_tmp_id;
528 IF (c1%FOUND) THEN
529 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_HAS_CHILD_SCT');
530 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
531 FND_MSG_PUB.Add;
532 CLOSE c1;
533 RAISE FND_API.G_EXC_ERROR;
534 END IF;
535 CLOSE c1;
536
537 EXCEPTION
538
539 WHEN FND_API.G_EXC_ERROR THEN
540 x_return_status := FND_API.G_RET_STS_ERROR;
541 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
542 p_data => x_msg_data,
543 p_encoded => 'F');
544
545 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
546 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
547 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
548 p_data => x_msg_data,
549 p_encoded => 'F');
550
551 WHEN OTHERS THEN
552 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
553 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
554 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
555 FND_MESSAGE.Set_Token('REASON', SQLERRM);
556 FND_MSG_PUB.Add;
557
558 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
559
560 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
561 THEN
562 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
563 END IF;
564
565 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
566 p_data => x_msg_data,
567 p_encoded => 'F');
568
569 END Validate_Create;
570
571
572 --*****************************************************************************
573 -- PROCEDURE Validate_Update()
574 --*****************************************************************************
575 -- IF x_return_status := FND_API.G_RET_STS_ERROR, then invalid
576 -- IF x_return_status := FND_API.G_RET_STS_SUCCESS, then valid
577
578 PROCEDURE Validate_Update
579 (
580 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
581 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
582 p_section_item_id IN NUMBER,
583 p_object_version_number IN NUMBER,
584 p_start_date_active IN DATE,
585 p_end_date_active IN DATE,
586 p_sort_order IN NUMBER,
587 p_association_reason_code IN VARCHAR2,
588 x_return_status OUT NOCOPY VARCHAR2,
589 x_msg_count OUT NOCOPY NUMBER,
590 x_msg_data OUT NOCOPY VARCHAR2
591 )
592 IS
593 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Update';
594 l_api_version CONSTANT NUMBER := 1.0;
595 l_msg_count NUMBER;
596 l_msg_data VARCHAR2(2000);
597
598 l_section_item_id NUMBER;
599 l_section_id NUMBER;
600 l_inventory_item_id NUMBER;
601 l_organization_id NUMBER;
602 l_return_status VARCHAR2(1);
603
604 BEGIN
605
606 -- Initialize message list if p_init_msg_list is set to TRUE.
607 IF FND_API.to_Boolean(p_init_msg_list) THEN
608 FND_MSG_PUB.initialize;
609 END IF;
610
611 -- Initialize API return status to success
612 x_return_status := FND_API.G_RET_STS_SUCCESS;
613
614 --
615 -- Check null values for required fields
616 --
617
618 -- section_item_id
619 IF (p_section_item_id IS NULL) THEN
620 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_PRIMARY_KEY');
621 FND_MSG_PUB.Add;
622 RAISE FND_API.G_EXC_ERROR;
623 END IF;
624
625 -- start_date_active
626 IF (p_start_date_active IS NULL) THEN
627 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_START_DATE');
628 FND_MSG_PUB.Add;
629 RAISE FND_API.G_EXC_ERROR;
630 END IF;
631
632 --
633 -- non-null field validation
634 --
635
636 -- sort order
637 IF ((p_sort_order IS NOT NULL) AND
638 (p_sort_order <> FND_API.G_MISS_NUM))
639 THEN
640 IF(p_sort_order < 0) THEN
641 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_INVALID_SCT_SORT_ORDER');
642 FND_MSG_PUB.Add;
643 RAISE FND_API.G_EXC_ERROR;
644 END IF;
645 END IF;
646
647 EXCEPTION
648
649 WHEN FND_API.G_EXC_ERROR THEN
650 x_return_status := FND_API.G_RET_STS_ERROR;
651 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
652 p_data => x_msg_data,
653 p_encoded => 'F');
654
655 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
656 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
657 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
658 p_data => x_msg_data,
659 p_encoded => 'F');
660
661 WHEN OTHERS THEN
662 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
663 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
664 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
665 FND_MESSAGE.Set_Token('REASON', SQLERRM);
666 FND_MSG_PUB.Add;
667
668 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
669
670 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
671 THEN
672 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
673 END IF;
674
675 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
676 p_data => x_msg_data,
677 p_encoded => 'F');
678
679 END Validate_Update;
680
681
682 -- ****************************************************************************
683 --*****************************************************************************
684
685 PROCEDURE Create_Section_Item
686 (
687 p_api_version IN NUMBER,
688 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
689 p_commit IN VARCHAR2 := FND_API.G_FALSE,
690 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
691 p_section_id IN NUMBER,
692 p_inventory_item_id IN NUMBER,
693 p_organization_id IN NUMBER,
694 p_start_date_active IN DATE,
695 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
696 p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
697 p_association_reason_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
698 x_section_item_id OUT NOCOPY NUMBER,
699 x_return_status OUT NOCOPY VARCHAR2,
700 x_msg_count OUT NOCOPY NUMBER,
701 x_msg_data OUT NOCOPY VARCHAR2
702 )
703 IS
704 l_api_name CONSTANT VARCHAR2(30) := 'Create_Section_Item';
705 l_api_version CONSTANT NUMBER := 1.0;
706 l_msg_count NUMBER;
707 l_msg_data VARCHAR2(2000);
708 l_return_status VARCHAR2(1);
709
710 l_object_version_number CONSTANT NUMBER := 1;
711 l_rowid VARCHAR2(30);
712
713 BEGIN
714
715 -- Standard Start of API savepoint
716 SAVEPOINT CREATE_SECTION_ITEM_PVT;
717
718 -- Standard call to check for call compatibility.
719 IF NOT FND_API.Compatible_API_Call(l_api_version,
720 p_api_version,
721 l_api_name,
722 G_PKG_NAME)
723 THEN
724 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
725 END IF;
726
727 -- Initialize message list if p_init_msg_list is set to TRUE.
728 IF FND_API.to_Boolean(p_init_msg_list) THEN
729 FND_MSG_PUB.initialize;
730 END IF;
731
732 -- Initialize API return status to success
733 x_return_status := FND_API.G_RET_STS_SUCCESS;
734
735 -- API body
736 -- CALL FLOW :
737 -- 1. Validate
738 -- 2. Insert row
739 --
740
741 --
742 -- 1. Validate
743 --
744 Validate_Create
745 (
746 p_init_msg_list => FND_API.G_FALSE,
747 p_validation_level => p_validation_level,
748 p_section_id => p_section_id,
749 p_inventory_item_id => p_inventory_item_id,
750 p_organization_id => p_organization_id,
751 p_start_date_active => p_start_date_active,
752 p_end_date_active => p_end_date_active,
753 p_sort_order => p_sort_order,
754 p_association_reason_code => p_association_reason_code,
755 x_return_status => l_return_status,
756 x_msg_count => l_msg_count,
757 x_msg_data => l_msg_data
758 );
759
760 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
761 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
762 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
763 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_INVALID_CREATE');
764 FND_MSG_PUB.Add;
765 RAISE FND_API.G_EXC_ERROR; -- invalid
766 END IF;
767
768 --
769 -- 2. Insert row
770 --
771 BEGIN
772 insert_row
773 (
774 FND_API.G_MISS_NUM,
775 l_object_version_number,
776 p_section_id,
777 p_inventory_item_id,
778 p_organization_id,
779 p_start_date_active,
780 p_end_date_active,
781 null,
782 p_sort_order,
783 p_association_reason_code,
784 SYSDATE,
785 FND_GLOBAL.user_id,
786 SYSDATE,
787 FND_GLOBAL.user_id,
788 FND_GLOBAL.login_id,
789 l_rowid,
790 x_section_item_id
791 );
792 EXCEPTION
793 WHEN NO_DATA_FOUND THEN
794 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_INSERT_FAIL');
795 FND_MSG_PUB.Add;
796 RAISE FND_API.G_EXC_ERROR;
797 WHEN OTHERS THEN
798 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
799 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
800 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
801 FND_MESSAGE.Set_Token('REASON', SQLERRM);
802 FND_MSG_PUB.Add;
803
804 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_INSERT_FAIL');
805 FND_MSG_PUB.Add;
806 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
807 END;
808
809 --
810 -- Call api which makes changes in relationship tables
811 --
812 IBE_PROD_RELATION_PVT.Item_Section_Inserted
813 (
814 p_section_id => p_section_id,
815 p_inventory_item_id => p_inventory_item_id,
816 p_organization_id => p_organization_id --Bug 2922902
817 );
818
819 --
820 -- End of main API body.
821
822 -- Standard check of p_commit.
823 IF (FND_API.To_Boolean(p_commit)) THEN
824 COMMIT WORK;
825 END IF;
826
827 -- Standard call to get message count and if count is 1, get message info.
828 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
829 p_data => x_msg_data,
830 p_encoded => 'F');
831
832 EXCEPTION
833
834 WHEN FND_API.G_EXC_ERROR THEN
835 ROLLBACK TO CREATE_SECTION_ITEM_PVT;
836 x_return_status := FND_API.G_RET_STS_ERROR;
837 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
838 p_data => x_msg_data,
839 p_encoded => 'F');
840
841 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
842 ROLLBACK TO CREATE_SECTION_ITEM_PVT;
843 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
844 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
845 p_data => x_msg_data,
846 p_encoded => 'F');
847
848 WHEN OTHERS THEN
849 ROLLBACK TO CREATE_SECTION_ITEM_PVT;
850
851 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
852 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
853 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
854 FND_MESSAGE.Set_Token('REASON', SQLERRM);
855 FND_MSG_PUB.Add;
856
857 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
858
859 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
860 THEN
861 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
862 END IF;
863
864 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
865 p_data => x_msg_data,
866 p_encoded => 'F');
867
868 END Create_Section_Item;
869
870 PROCEDURE Update_Section_Item
871 (
872 p_api_version IN NUMBER,
873 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
874 p_commit IN VARCHAR2 := FND_API.G_FALSE,
875 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
876 p_section_item_id IN NUMBER := FND_API.G_MISS_NUM,
877 p_object_version_number IN NUMBER,
878 p_section_id IN NUMBER := FND_API.G_MISS_NUM,
879 p_inventory_item_id IN NUMBER := FND_API.G_MISS_NUM,
880 p_organization_id IN NUMBER := FND_API.G_MISS_NUM,
881 p_start_date_active IN DATE := FND_API.G_MISS_DATE,
882 p_end_date_active IN DATE := FND_API.G_MISS_DATE,
883 p_usage_name IN VARCHAR2 := FND_API.G_MISS_CHAR,
884 p_sort_order IN NUMBER := FND_API.G_MISS_NUM,
885 p_association_reason_code IN VARCHAR2 := FND_API.G_MISS_CHAR,
886 x_return_status OUT NOCOPY VARCHAR2,
887 x_msg_count OUT NOCOPY NUMBER,
888 x_msg_data OUT NOCOPY VARCHAR2
889 )
890 IS
891 l_api_name CONSTANT VARCHAR2(30) := 'Update_Section_Item';
892 l_api_version CONSTANT NUMBER := 1.0;
893 l_msg_count NUMBER;
894 l_msg_data VARCHAR2(2000);
895
896 l_section_item_id NUMBER;
897 l_return_status VARCHAR2(1);
898
899 BEGIN
900
901 -- Standard Start of API savepoint
902 SAVEPOINT UPDATE_SECTION_ITEM_PVT;
903
904 -- Standard call to check for call compatibility.
905 IF NOT FND_API.Compatible_API_Call(l_api_version,
906 p_api_version,
907 l_api_name,
908 G_PKG_NAME)
909 THEN
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END IF;
912
913 -- Initialize message list if p_init_msg_list is set to TRUE.
914 IF FND_API.to_Boolean(p_init_msg_list) THEN
915 FND_MSG_PUB.initialize;
916 END IF;
917
918 -- Initialize API return status to success
919 x_return_status := FND_API.G_RET_STS_SUCCESS;
920
921 -- API body
922 -- CALL FLOW :
923 -- 1. Check if either section_item_id or combination of
924 -- section_id, inventory_item_id and organization_id is specified
925 -- 2. Update row
926 --
927
928 -- 1. Check if either section_item_id or combination of
929 -- section_id, inventory_item_id and organization_id is specified
930 IF ((p_section_item_id IS NOT NULL) AND
931 (p_section_item_id <> FND_API.G_MISS_NUM))
932 THEN
933 -- section_item_id specified, continue
934 l_section_item_id := p_section_item_id;
935 ELSIF ((p_section_id IS NOT NULL) AND
936 (p_section_id <> FND_API.G_MISS_NUM) AND
937 (p_inventory_item_id IS NOT NULL) AND
938 (p_inventory_item_id <> FND_API.G_MISS_NUM) AND
939 (p_organization_id IS NOT NULL) AND
940 (p_organization_id <> FND_API.G_MISS_NUM))
941 THEN
942 -- If combination of section_id, inventory_item_id and organization_id
943 -- is specified, then query for section_item_id
944 BEGIN
945
946 SELECT section_item_id INTO l_section_item_id
947 FROM ibe_dsp_section_items
948 WHERE section_id = p_section_id
949 AND inventory_item_id = p_inventory_item_id
950 AND organization_id = p_organization_id;
951
952 EXCEPTION
953 WHEN NO_DATA_FOUND THEN
954 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_SI_ID');
955 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
956 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
957 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
958 FND_MSG_PUB.Add;
959 RAISE FND_API.G_EXC_ERROR;
960 WHEN OTHERS THEN
961 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
962 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
963 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
964 FND_MESSAGE.Set_Token('REASON', SQLERRM);
965 FND_MSG_PUB.Add;
966
967 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_SI_ID');
968 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
969 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
970 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
971 FND_MSG_PUB.Add;
972 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
973 END;
974
975 ELSE
976 -- neither section_item_id nor combination of
977 -- section_id, inventory_item_id and organization_id is specified
978 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_SI_IDS_SPEC');
979 FND_MSG_PUB.Add;
980 RAISE FND_API.G_EXC_ERROR;
981 END IF;
982
983 --
984 -- 1. Validate the input data
985 --
986 Validate_Update
987 (
988 p_init_msg_list => FND_API.G_FALSE,
989 p_validation_level => p_validation_level,
990 p_section_item_id => l_section_item_id,
991 p_object_version_number => p_object_version_number,
992 p_start_date_active => p_start_date_active,
993 p_end_date_active => p_end_date_active,
994 p_sort_order => p_sort_order,
995 p_association_reason_code => p_association_reason_code,
996 x_return_status => l_return_status,
997 x_msg_count => l_msg_count,
998 x_msg_data => l_msg_data
999 );
1000
1001 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1004 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_INVALID_UPDATE');
1005 FND_MSG_PUB.Add;
1006 RAISE FND_API.G_EXC_ERROR; -- invalid
1007 END IF;
1008
1009 -- 2. update row
1010 BEGIN
1011 update_row
1012 (
1013 l_section_item_id,
1014 p_object_version_number,
1015 p_start_date_active,
1016 p_end_date_active,
1017 p_usage_name,
1018 p_sort_order,
1019 p_association_reason_code,
1020 SYSDATE,
1021 FND_GLOBAL.user_id,
1022 FND_GLOBAL.login_id
1023 );
1024 EXCEPTION
1025 WHEN NO_DATA_FOUND THEN
1026 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_UPDATE_FAIL');
1027 FND_MSG_PUB.Add;
1028 RAISE FND_API.G_EXC_ERROR;
1029 WHEN OTHERS THEN
1030 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1031 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1032 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1033 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1034 FND_MSG_PUB.Add;
1035
1036 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SI_UPDATE_FAIL');
1037 FND_MSG_PUB.Add;
1038 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1039 END;
1040
1041 --
1042 -- End of main API body.
1043
1044 -- Standard check of p_commit.
1045 IF (FND_API.To_Boolean(p_commit)) THEN
1046 COMMIT WORK;
1047 END IF;
1048
1049 -- Standard call to get message count and if count is 1, get message info.
1050 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1051 p_data => x_msg_data,
1052 p_encoded => 'F');
1053
1054 EXCEPTION
1055
1056 WHEN FND_API.G_EXC_ERROR THEN
1057 ROLLBACK TO UPDATE_SECTION_ITEM_PVT;
1058 x_return_status := FND_API.G_RET_STS_ERROR;
1059 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1060 p_data => x_msg_data,
1061 p_encoded => 'F');
1062
1063 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1064 ROLLBACK TO UPDATE_SECTION_ITEM_PVT;
1065 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1066 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1067 p_data => x_msg_data,
1068 p_encoded => 'F');
1069
1070 WHEN OTHERS THEN
1071 ROLLBACK TO UPDATE_SECTION_ITEM_PVT;
1072
1073 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1074 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1075 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1076 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1077 FND_MSG_PUB.Add;
1078
1079 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1080
1081 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1082 THEN
1083 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1084 END IF;
1085
1086 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1087 p_data => x_msg_data,
1088 p_encoded => 'F');
1089
1090 END Update_Section_Item;
1091
1092 PROCEDURE Delete_Section_Item
1093 (
1094 p_api_version IN NUMBER,
1095 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1096 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1097 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1098 p_call_from_trigger IN BOOLEAN := FALSE,
1099 p_section_item_id IN NUMBER := FND_API.G_MISS_NUM,
1100 p_section_id IN NUMBER := FND_API.G_MISS_NUM,
1101 p_inventory_item_id IN NUMBER := FND_API.G_MISS_NUM,
1102 p_organization_id IN NUMBER := FND_API.G_MISS_NUM,
1103 x_return_status OUT NOCOPY VARCHAR2,
1104 x_msg_count OUT NOCOPY NUMBER,
1105 x_msg_data OUT NOCOPY VARCHAR2
1106 )
1107 IS
1108
1109
1110 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Section_Item';
1111 l_api_version CONSTANT NUMBER := 1.0;
1112
1113 l_section_item_id NUMBER;
1114 l_section_id NUMBER;
1115 l_inventory_item_id NUMBER;
1116 l_organization_id NUMBER;
1117
1118 CURSOR c1(l_c_section_item_id IN NUMBER)
1119 IS SELECT mini_site_section_item_id FROM ibe_dsp_msite_sct_items
1120 WHERE section_item_id = l_c_section_item_id;
1121
1122 CURSOR c2(l_c_section_item_id IN NUMBER)
1123 IS SELECT section_id, inventory_item_id, organization_id
1124 FROM ibe_dsp_section_items
1125 WHERE section_item_id = l_c_section_item_id;
1126
1127 BEGIN
1128 -- Call savepoint only when not called from trigger
1129 IF (p_call_from_trigger = FALSE) THEN
1130 -- Standard Start of API savepoint
1131 SAVEPOINT DELETE_SECTION_ITEM_PVT;
1132 END IF;
1133
1134 -- Standard call to check for call compatibility.
1135 IF NOT FND_API.Compatible_API_Call(l_api_version,
1136 p_api_version,
1137 l_api_name,
1138 G_PKG_NAME)
1139 THEN
1140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1141 END IF;
1142
1143 -- Initialize message list if p_init_msg_list is set to TRUE.
1144 IF FND_API.to_Boolean(p_init_msg_list) THEN
1145 FND_MSG_PUB.initialize;
1146 END IF;
1147
1148 -- Initialize API return status to success
1149 x_return_status := FND_API.G_RET_STS_SUCCESS;
1150
1151 -- CALL FLOW
1152 -- 1. If section_item_id specified, delete all references for it
1153 -- 2. If combination of section_id, inventory_item_id and organization_id
1154 -- is specified, then query for section_item_id and delete
1155 -- all references
1156
1157 -- 1. If section_item_id specified, delete all references for it
1158 IF ((p_section_item_id IS NOT NULL) AND
1159 (p_section_item_id <> FND_API.G_MISS_NUM))
1160 THEN
1161 -- section_item_id specified, continue
1162 l_section_item_id := p_section_item_id;
1163
1164 OPEN c2(l_section_item_id);
1165 FETCH c2 INTO l_section_id, l_inventory_item_id, l_organization_id;
1166 IF (c2%NOTFOUND) THEN
1167 CLOSE c2;
1168 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_SCT_GET_SI_ID');
1169 FND_MESSAGE.Set_Token('SECTION_ITEM_ID', l_section_item_id);
1170 FND_MSG_PUB.Add;
1171 RAISE FND_API.G_EXC_ERROR;
1172 END IF;
1173 CLOSE c2;
1174
1175 ELSIF ((p_section_id IS NOT NULL) AND
1176 (p_section_id <> FND_API.G_MISS_NUM) AND
1177 (p_inventory_item_id IS NOT NULL) AND
1178 (p_inventory_item_id <> FND_API.G_MISS_NUM) AND
1179 (p_organization_id IS NOT NULL) AND
1180 (p_organization_id <> FND_API.G_MISS_NUM))
1181 THEN
1182 -- If combination of section_id, inventory_item_id and organization_id
1183 -- is specified, then query for section_item_id
1184 l_section_id := p_section_id;
1185 l_inventory_item_id := p_inventory_item_id;
1186 l_organization_id := p_organization_id;
1187
1188 BEGIN
1189
1190 SELECT section_item_id INTO l_section_item_id
1191 FROM ibe_dsp_section_items
1192 WHERE section_id = p_section_id
1193 AND inventory_item_id = p_inventory_item_id
1194 AND organization_id = p_organization_id;
1195
1196 EXCEPTION
1197 WHEN NO_DATA_FOUND THEN
1198 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_SI_ID');
1199 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
1200 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
1201 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
1202 FND_MSG_PUB.Add;
1203 RAISE FND_API.G_EXC_ERROR;
1204 WHEN OTHERS THEN
1205 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1206 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1207 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1208 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1209 FND_MSG_PUB.Add;
1210
1211 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_SI_ID');
1212 FND_MESSAGE.Set_Token('SECTION_ID', p_section_id);
1213 FND_MESSAGE.Set_Token('INVENTORY_ITEM_ID', p_inventory_item_id);
1214 FND_MESSAGE.Set_Token('ORGANIZATION_ID', p_organization_id);
1215 FND_MSG_PUB.Add;
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217 END;
1218
1219 ELSE
1220 -- neither section_item_id nor combination of
1221 -- section_id, inventory_item_id and organization_id is specified
1222 FND_MESSAGE.Set_Name('IBE', 'IBE_DSP_NO_SI_IDS_SPEC');
1223 FND_MSG_PUB.Add;
1224 RAISE FND_API.G_EXC_ERROR;
1225 END IF;
1226
1227 -- delete from ibe_dsp_msite_sct_items table
1228 FOR r1 IN c1(l_section_item_id) LOOP
1229 IBE_DSP_MSITE_SCT_ITEM_PVT.Delete_MSite_Section_Item
1230 (
1231 p_api_version => p_api_version,
1232 p_init_msg_list => FND_API.G_FALSE,
1233 p_commit => FND_API.G_FALSE,
1234 p_validation_level => p_validation_level,
1235 p_call_from_trigger => p_call_from_trigger,
1236 p_mini_site_section_item_id => r1.mini_site_section_item_id,
1237 p_mini_site_id => FND_API.G_MISS_NUM,
1238 p_section_item_id => FND_API.G_MISS_NUM,
1239 x_return_status => x_return_status,
1240 x_msg_count => x_msg_count,
1241 x_msg_data => x_msg_data
1242 );
1243
1244 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1245 RAISE FND_API.G_EXC_ERROR;
1246 ELSIF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1247 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1248 END IF;
1249
1250 END LOOP;
1251
1252 -- delete from relationship tables
1253 IBE_PROD_RELATION_PVT.Item_Section_Deleted
1254 (
1255 p_section_id => l_section_id,
1256 p_inventory_item_id => l_inventory_item_id ,
1257 p_organization_id => l_organization_id --Bug 2922902
1258 );
1259
1260 -- delete row
1261 delete_row(l_section_item_id);
1262
1263 EXCEPTION
1264
1265 WHEN FND_API.G_EXC_ERROR THEN
1266 IF (p_call_from_trigger = FALSE) THEN
1267 ROLLBACK TO DELETE_SECTION_ITEM_PVT;
1268 END IF;
1269 x_return_status := FND_API.G_RET_STS_ERROR;
1270 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1271 p_data => x_msg_data,
1272 p_encoded => 'F');
1273
1274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1275 IF (p_call_from_trigger = FALSE) THEN
1276 ROLLBACK TO DELETE_SECTION_ITEM_PVT;
1277 END IF;
1278 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1279 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1280 p_data => x_msg_data,
1281 p_encoded => 'F');
1282
1283 WHEN OTHERS THEN
1284 IF (p_call_from_trigger = FALSE) THEN
1285 ROLLBACK TO DELETE_SECTION_ITEM_PVT;
1286 END IF;
1287
1288 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1289 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1290 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1291 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1292 FND_MSG_PUB.Add;
1293
1294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1295
1296 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1297 THEN
1298 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1299 END IF;
1300
1301 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1302 p_data => x_msg_data,
1303 p_encoded => 'F');
1304
1305 END Delete_Section_Item;
1306
1307 --
1308 -- delete section items for input of inventory item id and organization id
1309 --
1310 PROCEDURE Delete_Section_Items_For_Item
1311 (
1312 p_inventory_item_id IN NUMBER := FND_API.G_MISS_NUM,
1313 p_organization_id IN NUMBER := FND_API.G_MISS_NUM
1314 )
1315 IS
1316 l_api_name CONSTANT VARCHAR2(30) :='Delete_Section_Items_For_Item';
1317 l_api_version CONSTANT NUMBER := 1.0;
1318
1319 l_msg_count NUMBER;
1320 l_msg_data VARCHAR2(2000);
1321 l_return_status VARCHAR2(1);
1322
1323 CURSOR c1(l_c_inventory_item_id IN NUMBER, l_c_organization_id IN NUMBER) IS
1324 SELECT section_item_id FROM ibe_dsp_section_items
1325 WHERE inventory_item_id = l_c_inventory_item_id AND
1326 organization_id = l_c_organization_id;
1327 BEGIN
1328
1329 FOR r1 IN c1(p_inventory_item_id, p_organization_id) LOOP
1330
1331 Delete_Section_Item
1332 (
1333 p_api_version => l_api_version,
1334 p_init_msg_list => FND_API.G_FALSE,
1335 p_commit => FND_API.G_FALSE,
1336 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1337 p_call_from_trigger => TRUE,
1338 p_section_item_id => r1.section_item_id,
1339 p_section_id => FND_API.G_MISS_NUM,
1340 p_inventory_item_id => FND_API.G_MISS_NUM,
1341 p_organization_id => FND_API.G_MISS_NUM,
1342 x_return_status => l_return_status,
1343 x_msg_count => l_msg_count,
1344 x_msg_data => l_msg_data
1345 );
1346
1347 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1348 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1349 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1350 RAISE FND_API.G_EXC_ERROR;
1351 END IF;
1352
1353 END LOOP;
1354
1355 END Delete_Section_Items_For_Item;
1356
1357 --
1358 -- to update and delete multiple entries. Delete the entries whose flag is
1359 -- set to "Y"
1360 --
1361 PROCEDURE Update_Delete_Sct_Itms
1362 (
1363 p_api_version IN NUMBER,
1364 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1365 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1366 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1367 p_section_item_ids IN JTF_NUMBER_TABLE,
1368 p_object_version_numbers IN JTF_NUMBER_TABLE,
1369 p_start_date_actives IN JTF_DATE_TABLE,
1370 p_end_date_actives IN JTF_DATE_TABLE,
1371 p_usage_names IN JTF_VARCHAR2_TABLE_300,
1372 p_sort_orders IN JTF_NUMBER_TABLE,
1373 p_association_reason_codes IN JTF_VARCHAR2_TABLE_300,
1374 p_delete_flags IN JTF_VARCHAR2_TABLE_300,
1375 x_return_status OUT NOCOPY VARCHAR2,
1376 x_msg_count OUT NOCOPY NUMBER,
1377 x_msg_data OUT NOCOPY VARCHAR2
1378 )
1379 IS
1380 l_api_name CONSTANT VARCHAR2(30) := 'Update_Delete_Sct_Itms';
1381 l_api_version CONSTANT NUMBER := 1.0;
1382 l_msg_count NUMBER;
1383 l_msg_data VARCHAR2(2000);
1384
1385 l_section_item_id NUMBER;
1386 l_return_status VARCHAR2(1);
1387
1388 BEGIN
1389
1390 -- Standard Start of API savepoint
1391 SAVEPOINT UPDATE_DELETE_SCT_ITMS_PVT;
1392
1393 -- Standard call to check for call compatibility.
1394 IF NOT FND_API.Compatible_API_Call(l_api_version,
1395 p_api_version,
1396 l_api_name,
1397 G_PKG_NAME)
1398 THEN
1399 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1400 END IF;
1401
1402 -- Initialize message list if p_init_msg_list is set to TRUE.
1403 IF FND_API.to_Boolean(p_init_msg_list) THEN
1404 FND_MSG_PUB.initialize;
1405 END IF;
1406
1407 -- Initialize API return status to success
1408 x_return_status := FND_API.G_RET_STS_SUCCESS;
1409
1410 -- API body
1411 -- CALL FLOW :
1412 -- 1.
1413 FOR i IN 1..p_section_item_ids.COUNT LOOP
1414
1415 IF (p_delete_flags(i) = 'Y') THEN
1416
1417 Delete_Section_Item
1418 (
1419 p_api_version => p_api_version,
1420 p_init_msg_list => FND_API.G_FALSE,
1421 p_commit => FND_API.G_FALSE,
1422 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1423 p_section_item_id => p_section_item_ids(i),
1424 p_section_id => FND_API.G_MISS_NUM,
1425 p_inventory_item_id => FND_API.G_MISS_NUM,
1426 p_organization_id => FND_API.G_MISS_NUM,
1427 x_return_status => x_return_status,
1428 x_msg_count => x_msg_count,
1429 x_msg_data => x_msg_data
1430 );
1431
1432
1433 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1434 RAISE FND_API.G_EXC_ERROR;
1435 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1436 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1437 END IF;
1438
1439 ELSE
1440 Update_Section_Item
1441 (
1442 p_api_version => p_api_version,
1443 p_init_msg_list => FND_API.G_FALSE,
1444 p_commit => FND_API.G_FALSE,
1445 p_validation_level => p_validation_level,
1446 p_section_item_id => p_section_item_ids(i),
1447 p_object_version_number => p_object_version_numbers(i),
1448 p_section_id => FND_API.G_MISS_NUM,
1449 p_inventory_item_id => FND_API.G_MISS_NUM,
1450 p_organization_id => FND_API.G_MISS_NUM,
1451 p_start_date_active => p_start_date_actives(i),
1452 p_end_date_active => p_end_date_actives(i),
1453 p_usage_name => p_usage_names(i),
1454 p_sort_order => p_sort_orders(i),
1455 p_association_reason_code => p_association_reason_codes(i),
1456 x_return_status => l_return_status,
1457 x_msg_count => x_msg_count,
1458 x_msg_data => x_msg_data
1459 );
1460
1461 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1462 RAISE FND_API.G_EXC_ERROR;
1463 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1465 END IF;
1466
1467 END IF;
1468
1469
1470 END LOOP;
1471
1472 --
1473 -- End of main API body.
1474
1475 -- Standard check of p_commit.
1476 IF (FND_API.To_Boolean(p_commit)) THEN
1477 COMMIT WORK;
1478 END IF;
1479
1480 -- Standard call to get message count and if count is 1, get message info.
1481 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1482 p_data => x_msg_data,
1483 p_encoded => 'F');
1484
1485 EXCEPTION
1486
1487 WHEN FND_API.G_EXC_ERROR THEN
1488 ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;
1489 x_return_status := FND_API.G_RET_STS_ERROR;
1490 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1491 p_data => x_msg_data,
1492 p_encoded => 'F');
1493
1494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1495 ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;
1496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1497 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1498 p_data => x_msg_data,
1499 p_encoded => 'F');
1500
1501 WHEN OTHERS THEN
1502 ROLLBACK TO UPDATE_DELETE_SCT_ITMS_PVT;
1503
1504 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
1505 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
1506 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
1507 FND_MESSAGE.Set_Token('REASON', SQLERRM);
1508 FND_MSG_PUB.Add;
1509
1510 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1511
1512 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1513 THEN
1514 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1515 END IF;
1516
1517 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
1518 p_data => x_msg_data,
1519 p_encoded => 'F');
1520
1521 END Update_Delete_Sct_Itms;
1522
1523 END IBE_DSP_SECTION_ITEM_PVT;