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