[Home] [Help]
PACKAGE BODY: APPS.IBE_SHOP_LIST_PVT
Source
1 PACKAGE BODY IBE_Shop_List_PVT AS
2 /* $Header: IBEVQSLB.pls 120.3 2005/10/05 04:09:16 banatara noship $ */
3
4 l_true VARCHAR2(1) := FND_API.G_TRUE;
5
6 FUNCTION Find_Index(
7 p_id IN NUMBER,
8 p_num_tbl IN jtf_number_table
9 )
10 RETURN NUMBER
11 IS
12 l_index NUMBER := 0;
13 BEGIN
14 IF p_num_tbl.COUNT > 0 THEN
15 FOR i IN p_num_tbl.FIRST..p_num_tbl.LAST LOOP
16 IF p_num_tbl(i) = p_id THEN
17 l_index := i;
18 EXIT;
19 END IF;
20 END LOOP;
21 END IF;
22
23 RETURN l_index;
24 END Find_Index;
25
26
27 FUNCTION Find_Index(
28 p_id IN NUMBER,
29 p_id_tbl IN jtf_number_table,
30 p_index_tbl IN jtf_number_table
31 )
32 RETURN NUMBER
33 IS
34 l_index NUMBER := 0;
35 BEGIN
36 IF p_id_tbl.COUNT > 0 THEN
37 FOR i IN p_id_tbl.FIRST..p_id_tbl.LAST LOOP
38 IF p_id_tbl(i) = p_id THEN
39 l_index := p_index_tbl(i);
40 EXIT;
41 END IF;
42 END LOOP;
43 END IF;
44
45 RETURN l_index;
46 END Find_Index;
47
48
49 FUNCTION Find_Same_Item_In_Qte_Line_Tbl(
50 p_qte_line_tbl IN ASO_Quote_Pub.Qte_Line_Tbl_Type,
51 p_inventory_item_id IN NUMBER ,
52 p_uom_code IN VARCHAR2
53 )
54 RETURN NUMBER
55 IS
56 l_index NUMBER := 0;
57 BEGIN
58 IF p_qte_line_tbl.COUNT > 0 THEN
59 FOR i IN p_qte_line_tbl.FIRST..p_qte_line_tbl.LAST LOOP
60 IF p_qte_line_tbl(i).item_type_code = 'STD'
61 AND p_qte_line_tbl(i).inventory_item_id = p_inventory_item_id
62 AND p_qte_line_tbl(i).uom_code = p_uom_code THEN
63 l_index := i;
64 EXIT;
65 END IF;
66 END LOOP;
67 END IF;
68
69 RETURN l_index;
70 END Find_Same_Item_In_Qte_Line_Tbl;
71
72
73 FUNCTION Find_Same_Item_In_Lst_Line_Tbl(
74 p_list_line_tbl IN IBE_Shop_List_PVT.SL_Line_Tbl_Type,
75 p_inventory_item_id IN NUMBER ,
76 p_uom_code IN VARCHAR2
77 )
78 RETURN NUMBER
79 IS
80 l_index NUMBER := 0;
81 BEGIN
82 IF p_list_line_tbl.COUNT > 0 THEN
83 FOR i IN p_list_line_tbl.FIRST..p_list_line_tbl.LAST LOOP
84 IF p_list_line_tbl(i).item_type_code = 'STD'
85 AND p_list_line_tbl(i).inventory_item_id = p_inventory_item_id
86 AND p_list_line_tbl(i).uom_code = p_uom_code THEN
87 l_index := i;
88 EXIT;
89 END IF;
90 END LOOP;
91 END IF;
92 RETURN l_index;
93 END Find_Same_Item_In_Lst_Line_Tbl;
94
95
96 FUNCTION Get_Line_IDs_From_Headers(p_list_header_id_tbl IN jtf_number_table)
97 RETURN jtf_number_table
98 IS
99 TYPE Csr_Type IS REF CURSOR;
100 line_csr Csr_Type;
101 rel_line_csr Csr_Type;
102 l_line_id NUMBER;
103 l_rel_line_id NUMBER;
104 l_item_type_code VARCHAR2(30);
105 l_list_line_id_tbl jtf_number_table := jtf_number_table();
106 l_list_header_id NUMBER;
107 BEGIN
108 FOR i IN p_list_header_id_tbl.FIRST..p_list_header_id_tbl.LAST LOOP
109 l_list_header_id := p_list_header_id_tbl(i);
110
111 OPEN line_csr FOR SELECT I.shp_list_item_id,
112 I.item_type_code
113 FROM ibe_sh_shp_list_items I
114 WHERE I.shp_list_id = l_list_header_id
115 AND I.item_type_code NOT IN ('CFG', 'OPT', 'SRV');
116 LOOP
117 FETCH line_csr INTO l_line_id,
118 l_item_type_code;
119 EXIT WHEN line_csr%NOTFOUND;
120
121 l_list_line_id_tbl.EXTEND;
122 l_list_line_id_tbl(l_list_line_id_tbl.LAST) := l_line_id;
123
124 IF l_item_type_code = 'MDL' OR l_item_type_code = 'SVA' THEN
125 OPEN rel_line_csr FOR SELECT R.related_shp_list_item_id
126 FROM ibe_sh_shlitem_rels R
127 START WITH R.shp_list_item_id = l_line_id
128 CONNECT BY R.shp_list_item_id = PRIOR R.related_shp_list_item_id;
129 LOOP
130 FETCH rel_line_csr INTO l_rel_line_id;
131 EXIT WHEN rel_line_csr%NOTFOUND;
132
133 l_list_line_id_tbl.EXTEND;
134 l_list_line_id_tbl(l_list_line_id_tbl.LAST) := l_rel_line_id;
135 END LOOP;
136
137 CLOSE rel_line_csr;
138 END IF;
139 END LOOP;
140
141 CLOSE line_csr;
142 END LOOP;
143
144 RETURN l_list_line_id_tbl;
145 END Get_Line_IDs_From_Headers;
146
147
148 /*
149 * This procedure returns the out parameter x_list_line_id_tbl
150 * which includes all the related items of p_list_line_id_tbl.
151 * This also construct the out paramter x_qte_line_relation_tbl.
152 */
153 PROCEDURE Include_Related_Lines(
154 p_qte_line_rel_tbl IN VARCHAR2 ,
155 p_list_line_id_tbl IN jtf_number_table,
156 x_list_line_id_tbl OUT NOCOPY jtf_number_table,
157 x_qte_line_relation_tbl OUT NOCOPY ASO_Quote_Pub.Line_Rltship_Tbl_Type,
158 x_list_line_relation_tbl OUT NOCOPY SL_Line_Rel_Tbl_Type)
159 IS
160 TYPE rel_line_csr_type IS REF CURSOR;
161 rel_line_csr rel_line_csr_type;
162 l_line_id NUMBER;
163 l_rel_line_id NUMBER;
164 l_relationship_type_code VARCHAR2(30);
165 k PLS_INTEGER := 1; -- index for x_qte_line_relation_tbl
166 BEGIN
167 x_list_line_id_tbl := jtf_number_table();
168
169 FOR i IN p_list_line_id_tbl.FIRST..p_list_line_id_tbl.LAST LOOP
170 x_list_line_id_tbl.EXTEND;
171 x_list_line_id_tbl(x_list_line_id_tbl.LAST) := p_list_line_id_tbl(i);
172
173 /*
174 * Hierarchical query to retrieve all the descendents of a line
175 */
176 OPEN rel_line_csr FOR 'SELECT shp_list_item_id, '||
177 'related_shp_list_item_id, ' ||
178 'relationship_type_code ' ||
179 'FROM ibe_sh_shlitem_rels ' ||
180 'START WITH shp_list_item_id = :1 '||
181 'CONNECT BY shp_list_item_id = PRIOR related_shp_list_item_id'
182 USING p_list_line_id_tbl(i);
183 LOOP
184 FETCH rel_line_csr INTO l_line_id,
185 l_rel_line_id,
186 l_relationship_type_code;
187 EXIT WHEN rel_line_csr%NOTFOUND;
188
189 -- added 3/28/03: avoid saving SRV info
190 IF (l_relationship_type_code <> 'SERVICE') then
191 x_list_line_id_tbl.EXTEND;
192 x_list_line_id_tbl(x_list_line_id_tbl.LAST) := l_rel_line_id;
193
194 IF FND_API.to_Boolean(p_qte_line_rel_tbl) THEN
195 FOR j IN x_list_line_id_tbl.FIRST..x_list_line_id_tbl.LAST LOOP
196 IF x_list_line_id_tbl(j) = l_line_id THEN
197 x_qte_line_relation_tbl(k).qte_line_index := j;
198 EXIT;
199 END IF;
200 END LOOP;
201
202 x_qte_line_relation_tbl(k).related_qte_line_index := x_list_line_id_tbl.LAST;
203 x_qte_line_relation_tbl(k).relationship_type_code := l_relationship_type_code;
204 x_qte_line_relation_tbl(k).operation_code := 'CREATE';
205 k := k + 1;
206 ELSE
207 FOR j IN x_list_line_id_tbl.FIRST..x_list_line_id_tbl.LAST LOOP
208 IF x_list_line_id_tbl(j) = l_line_id THEN
209 x_list_line_relation_tbl(k).line_index := j;
210 EXIT;
211 END IF;
212 END LOOP;
213
214 x_list_line_relation_tbl(k).related_line_index := x_list_line_id_tbl.LAST;
215 x_list_line_relation_tbl(k).relationship_type_code := l_relationship_type_code;
216 x_list_line_relation_tbl(k).operation_code := 'CREATE';
217 k := k + 1;
218 END IF;
219 END IF;
220 END LOOP;
221
222 CLOSE rel_line_csr;
223 END LOOP;
224 END Include_Related_Lines;
225
226
227 /* Constructs x_qte_line_rel_tbl if p_to_create_quote is TRUE, that is, this
228 * procedure is called to create a quote from lists.
229 * Constructs x_list_line_rel_tbl if p_to_create_quote is FALSE, that is, this
230 * procedure is called to create a list from lists.
231 */
232 PROCEDURE Get_Line_Rels_From_Lines(
233 p_to_create_quote IN BOOLEAN ,
234 p_list_header_id_tbl IN jtf_number_table,
235 p_list_line_id_tbl IN jtf_number_table,
236 p_line_index_tbl IN jtf_number_table,
237 x_qte_line_rel_tbl OUT NOCOPY ASO_Quote_Pub.Line_Rltship_Tbl_Type,
238 x_list_line_rel_tbl OUT NOCOPY SL_Line_Rel_Tbl_Type
239 )
240 IS
241 TYPE Csr_Type IS REF CURSOR;
242 l_csr Csr_Type;
243 j PLS_INTEGER := 1; -- index for l_qte_line_relation_tbl
244 l_line_id NUMBER;
245 l_related_line_id NUMBER;
246 l_relationship_type_code VARCHAR2(30);
247 l_list_header_id NUMBER;
248 BEGIN
249 FOR i IN p_list_header_id_tbl.FIRST..p_list_header_id_tbl.LAST LOOP
250 l_list_header_id := p_list_header_id_tbl(i);
251 OPEN l_csr FOR SELECT R.shp_list_item_id,
252 R.related_shp_list_item_id,
253 R.relationship_type_code
254 FROM ibe_sh_shp_list_items L,
255 ibe_sh_shlitem_rels R
256 WHERE L.shp_list_id = l_list_header_id
257 AND R.shp_list_item_id = L.shp_list_item_id;
258 LOOP
259 FETCH l_csr INTO l_line_id,
260 l_related_line_id,
261 l_relationship_type_code;
262 EXIT WHEN l_csr%NOTFOUND;
263
264 -- added 3/28/03: avoid saving SRV info
265 if (l_relationship_type_code <> 'SERVICE') then
266 /* This procedure is called to create a quote from lists */
267 IF p_to_create_quote THEN
268 x_qte_line_rel_tbl(j).operation_code := 'CREATE';
269 x_qte_line_rel_tbl(j).qte_line_index
270 := Find_Index(l_line_id, p_list_line_id_tbl, p_line_index_tbl);
271 x_qte_line_rel_tbl(j).related_qte_line_index
272 := Find_Index(l_related_line_id, p_list_line_id_tbl, p_line_index_tbl);
273 x_qte_line_rel_tbl(j).relationship_type_code
274 := l_relationship_type_code;
275 /* This procedure is called to create a list from lists */
276 ELSE
277 x_list_line_rel_tbl(j).operation_code := 'CREATE';
278 x_list_line_rel_tbl(j).line_index
279 := Find_Index(l_line_id, p_list_line_id_tbl, p_line_index_tbl);
280 x_list_line_rel_tbl(j).related_line_index
281 := Find_Index(l_related_line_id, p_list_line_id_tbl, p_line_index_tbl);
282 x_list_line_rel_tbl(j).relationship_type_code
283 := l_relationship_type_code;
284 END IF;
285
286 j := j + 1;
287 end if;
288 END LOOP;
289
290 CLOSE l_csr;
291 END LOOP;
292 END Get_Line_Rels_From_Lines;
293
294
295 PROCEDURE Set_List_Lines_From_List_Lines(
296 p_list_line_id_tbl IN jtf_number_table ,
297 p_list_header_id IN NUMBER ,
298 p_combine_same_item IN VARCHAR2 := FND_API.G_MISS_CHAR ,
299 x_list_line_tbl OUT NOCOPY IBE_Shop_List_PVT.SL_Line_Tbl_Type,
300 x_list_line_id_map_tbl OUT NOCOPY jtf_number_table ,
301 x_list_line_index_tbl OUT NOCOPY jtf_number_table
302 )
303 IS
304 TYPE Csr_Type IS REF CURSOR;
305 l_csr Csr_Type;
306 l_shp_list_item_id NUMBER;
307 l_inventory_item_id NUMBER;
308 l_organization_id NUMBER;
309 l_uom_code VARCHAR2(3);
310 l_quantity NUMBER;
311 l_item_type_code VARCHAR2(30);
312 l_config_header_id NUMBER;
313 l_config_revision_num NUMBER;
314 l_complete_configuration_flag VARCHAR2(3);
315 l_valid_configuration_flag VARCHAR2(3);
316 l_relationship_type_code VARCHAR2(30);
317 l_attribute_category VARCHAR2(30);
318 l_attribute1 VARCHAR2(150);
319 l_attribute2 VARCHAR2(150);
320 l_attribute3 VARCHAR2(150);
321 l_attribute4 VARCHAR2(150);
322 l_attribute5 VARCHAR2(150);
323 l_attribute6 VARCHAR2(150);
324 l_attribute7 VARCHAR2(150);
325 l_attribute8 VARCHAR2(150);
326 l_attribute9 VARCHAR2(150);
327 l_attribute10 VARCHAR2(150);
328 l_attribute11 VARCHAR2(150);
329 l_attribute12 VARCHAR2(150);
330 l_attribute13 VARCHAR2(150);
331 l_attribute14 VARCHAR2(150);
332 l_attribute15 VARCHAR2(150);
333
334 l_list_line_id NUMBER;
335 l_list_line_id_tbl jtf_number_table := jtf_number_table();
336 i PLS_INTEGER := 1; -- index for x_qte_line_tbl
337 j PLS_INTEGER := 1; -- index for x_qte_line_detail_tbl
338 l_list_line_tbl_index PLS_INTEGER;
339 l_line_id NUMBER;
340 BEGIN
341 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
342 IBE_Util.Debug('Set_List_Lines_From_List_Lines(+)...');
343 END IF;
344 x_list_line_id_map_tbl := jtf_number_table();
345 x_list_line_index_tbl := jtf_number_table();
346
347 FOR k IN 1..p_list_line_id_tbl.COUNT LOOP
348 SELECT shp_list_item_id,
349 inventory_item_id,
350 quantity,
351 uom_code,
352 organization_id,
353 config_header_id,
354 config_revision_num,
355 complete_configuration_flag,
356 valid_configuration_flag,
357 item_type_code,
358 attribute_category,
359 attribute1,
360 attribute2,
361 attribute3,
362 attribute4,
363 attribute5,
364 attribute6,
365 attribute7,
366 attribute8,
367 attribute9,
368 attribute10,
369 attribute11,
370 attribute12,
371 attribute13,
372 attribute14,
373 attribute15
374 INTO l_shp_list_item_id,
375 l_inventory_item_id,
376 l_quantity,
377 l_uom_code,
378 l_organization_id,
379 l_config_header_id,
380 l_config_revision_num,
381 l_complete_configuration_flag,
382 l_valid_configuration_flag,
383 l_item_type_code,
384 l_attribute_category,
385 l_attribute1,
386 l_attribute2,
387 l_attribute3,
388 l_attribute4,
389 l_attribute5,
390 l_attribute6,
391 l_attribute7,
392 l_attribute8,
393 l_attribute9,
394 l_attribute10,
395 l_attribute11,
396 l_attribute12,
397 l_attribute13,
398 l_attribute14,
399 l_attribute15
400 FROM ibe_sh_shp_list_items
401 WHERE shp_list_item_id = p_list_line_id_tbl(k);
402
403 IF p_combine_same_item = 'Y' AND l_item_type_code = 'STD' THEN
404 l_list_line_tbl_index := Find_Same_Item_In_Lst_Line_Tbl(
405 p_list_line_tbl => x_list_line_tbl ,
406 p_inventory_item_id => l_inventory_item_id,
407 p_uom_code => l_uom_code);
408
409 /*
410 * if l_list_line_tbl_index <> 0, there is already a line in x_qte_line_tbl
411 * for same standard item. Don't add a new line and just update the quantity.
412 */
413 IF l_list_line_tbl_index <> 0 THEN
414 x_list_line_tbl(l_list_line_tbl_index).quantity
415 := x_list_line_tbl(l_list_line_tbl_index).quantity + l_quantity;
416 ELSE
417 x_list_line_tbl(i).shp_list_id := p_list_header_id;
418 x_list_line_tbl(i).inventory_item_id := l_inventory_item_id;
419 x_list_line_tbl(i).organization_id := l_organization_id;
420 x_list_line_tbl(i).uom_code := l_uom_code;
421 x_list_line_tbl(i).item_type_code := l_item_type_code;
422 x_list_line_tbl(i).quantity := l_quantity;
423
424 /*
425 * find same item IN aso_quote_lines view where item_type_code is 'STD',
426 * and inventory_item_id, organization_id, and uom_code matches with this line
427 */
428 BEGIN
429 SELECT shp_list_item_id,
430 quantity
431 INTO l_list_line_id,
432 l_quantity
433 FROM ibe_sh_shp_list_items
434 WHERE shp_list_id = p_list_header_id
435 AND organization_id = l_organization_id
436 AND item_type_code = 'STD'
437 AND inventory_item_id = l_inventory_item_id
438 AND uom_code = l_uom_code;
439
440 x_list_line_tbl(i).operation_code := 'UPDATE';
441 x_list_line_tbl(i).shp_list_item_id := l_list_line_id;
442 x_list_line_tbl(i).quantity := x_list_line_tbl(i).quantity + l_quantity;
443 EXCEPTION
444 /*
445 * We get to NO_DATA_FOUND block when there is no line in
446 * x_list_line_tbl nor in ibe_sh_shp_list_items table for the same item.
447 */
448 WHEN NO_DATA_FOUND THEN
449 x_list_line_tbl(i).operation_code := 'CREATE';
450 --commented by makulkar
451 /*
452 x_list_line_tbl(i).attribute_category := l_attribute_category;
453 x_list_line_tbl(i).attribute1 := l_attribute1;
454 x_list_line_tbl(i).attribute2 := l_attribute2;
455 x_list_line_tbl(i).attribute3 := l_attribute3;
456 x_list_line_tbl(i).attribute4 := l_attribute4;
457 x_list_line_tbl(i).attribute5 := l_attribute5;
458 x_list_line_tbl(i).attribute6 := l_attribute6;
459 x_list_line_tbl(i).attribute7 := l_attribute7;
460 x_list_line_tbl(i).attribute8 := l_attribute8;
461 x_list_line_tbl(i).attribute9 := l_attribute9;
462 x_list_line_tbl(i).attribute10 := l_attribute10;
463 x_list_line_tbl(i).attribute11 := l_attribute11;
464 x_list_line_tbl(i).attribute12 := l_attribute12;
465 x_list_line_tbl(i).attribute13 := l_attribute13;
466 x_list_line_tbl(i).attribute14 := l_attribute14;
467 x_list_line_tbl(i).attribute15 := l_attribute15;
468 */
469 END;
470
471 x_list_line_id_map_tbl.EXTEND;
472 x_list_line_index_tbl.EXTEND;
473 x_list_line_id_map_tbl(x_list_line_id_map_tbl.LAST)
474 := p_list_line_id_tbl(k);
475 x_list_line_index_tbl(x_list_line_index_tbl.LAST) := i;
476
477 i := i + 1;
478 END IF;
479 /*
480 * p_combine_same_item = 'N' OR l_item_type_code <> 'STD',
481 * which means that we are safe to add a new line
482 */
483 ELSE
484 x_list_line_tbl(i).operation_code := 'CREATE';
485 x_list_line_tbl(i).shp_list_id := p_list_header_id;
486 x_list_line_tbl(i).inventory_item_id := l_inventory_item_id;
487 x_list_line_tbl(i).organization_id := l_organization_id;
488 x_list_line_tbl(i).uom_code := l_uom_code;
489 x_list_line_tbl(i).item_type_code := l_item_type_code;
490 x_list_line_tbl(i).quantity := l_quantity;
491 x_list_line_tbl(i).config_header_id := l_config_header_id;
492 x_list_line_tbl(i).config_revision_num := l_config_revision_num;
493 x_list_line_tbl(i).complete_configuration_flag := l_complete_configuration_flag;
494 x_list_line_tbl(i).valid_configuration_flag := l_valid_configuration_flag;
495 --commented by makulkar
496 /*
497 x_list_line_tbl(i).attribute_category := l_attribute_category;
498 x_list_line_tbl(i).attribute1 := l_attribute1;
499 x_list_line_tbl(i).attribute2 := l_attribute2;
500 x_list_line_tbl(i).attribute3 := l_attribute3;
501 x_list_line_tbl(i).attribute4 := l_attribute4;
502 x_list_line_tbl(i).attribute5 := l_attribute5;
503 x_list_line_tbl(i).attribute6 := l_attribute6;
504 x_list_line_tbl(i).attribute7 := l_attribute7;
505 x_list_line_tbl(i).attribute8 := l_attribute8;
506 x_list_line_tbl(i).attribute9 := l_attribute9;
507 x_list_line_tbl(i).attribute10 := l_attribute10;
508 x_list_line_tbl(i).attribute11 := l_attribute11;
509 x_list_line_tbl(i).attribute12 := l_attribute12;
510 x_list_line_tbl(i).attribute13 := l_attribute13;
511 x_list_line_tbl(i).attribute14 := l_attribute14;
512 x_list_line_tbl(i).attribute15 := l_attribute15;
513 */
514
515 x_list_line_id_map_tbl.EXTEND;
516 x_list_line_index_tbl.EXTEND;
517 x_list_line_id_map_tbl(x_list_line_id_map_tbl.LAST)
518 := p_list_line_id_tbl(k);
519 x_list_line_index_tbl(x_list_line_index_tbl.LAST) := i;
520
521 i := i + 1;
522 END IF;
523 END LOOP;
524
525 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
526 IBE_Util.Debug('Set_List_Lines_From_List_Lines(-)...');
527 END IF;
528 END Set_List_Lines_From_List_Lines;
529
530 PROCEDURE Delete_Config_From_Shp_List(
531 p_shp_list_ids IN jtf_number_table,
532 p_shp_list_line_ids IN jtf_number_table,
533 p_usage_exists IN OUT NOCOPY NUMBER,
534 p_error_message IN OUT NOCOPY VARCHAR2,
535 p_return_value IN OUT NOCOPY NUMBER
536 )
537 IS
538
539 TYPE csr_type is REF CURSOR;
540 C_GET_CONFIG_LINES csr_type;
541 l_line_config_id NUMBER;
542 l_line_config_revnum NUMBER;
543 l_list_ids VARCHAR2(300);
544 l_list_line_ids VARCHAR2(300);
545 l_cursor_string CONSTANT VARCHAR2(200) := 'SELECT DISTINCT ' ||
546 'config_header_id, ' ||
547 'config_revision_num '||
548 ' FROM ibe_sh_shp_list_items ' ||
549 ' WHERE';
550
551 BEGIN
552 IF p_shp_list_ids IS NOT NULL OR p_shp_list_line_ids IS NOT NULL THEN
553 IF p_shp_list_ids IS NOT NULL THEN
554 FOR i IN 1..p_shp_list_ids.count LOOP
555 IF i > 1 THEN
556 l_list_ids := l_list_ids || ',';
557 END IF;
558 l_list_ids := l_list_ids || p_shp_list_ids(i);
559 END LOOP;
560 l_list_ids := l_list_ids || ')';
561 OPEN C_GET_CONFIG_LINES FOR l_cursor_string || ' shp_list_id in (' || l_list_ids;
562 ELSIF p_shp_list_line_ids IS NOT NULL THEN
563 FOR i IN 1..p_shp_list_line_ids.count LOOP
564 IF i > 1 THEN
565 l_list_line_ids := l_list_line_ids || ',';
566 END IF;
567 l_list_line_ids := l_list_line_ids || p_shp_list_line_ids(i);
568 END LOOP;
569 l_list_line_ids := l_list_line_ids || ')';
570 OPEN C_GET_CONFIG_LINES FOR l_cursor_string || ' shp_list_item_id in (' || l_list_line_ids;
571 END IF;
572 LOOP
573 FETCH C_GET_CONFIG_LINES INTO l_line_config_id, l_line_config_revnum;
574 EXIT WHEN C_GET_CONFIG_LINES%NOTFOUND;
575 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
576 IBE_Util.Debug('Calling Delete_Configuration for' || l_line_config_id || ', ' || l_line_config_revnum);
577 END IF;
578 CZ_CF_API.delete_configuration(
579 config_hdr_id => l_line_config_id ,
580 config_rev_nbr => l_line_config_revnum,
581 usage_exists => p_usage_exists ,
582 error_message => p_error_message ,
583 return_value => p_return_value);
584 END LOOP;
585 END IF;
586 END Delete_Config_From_Shp_List;
587
588 PROCEDURE Delete(
589 p_api_version IN NUMBER := 1 ,
590 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
591 p_commit IN VARCHAR2 := FND_API.G_FALSE,
592 x_return_status OUT NOCOPY VARCHAR2 ,
593 x_msg_count OUT NOCOPY NUMBER ,
594 x_msg_data OUT NOCOPY VARCHAR2 ,
595 p_shop_list_ids IN jtf_number_table ,
596 p_obj_ver_numbers IN jtf_number_table
597 )
598 IS
599 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete';
600 L_API_VERSION CONSTANT NUMBER := 1.0;
601 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
602 l_shop_list_line_id NUMBER;
603 l_shop_list_rel_line_id NUMBER;
604 l_return_value NUMBER;
605 l_usage_exists NUMBER;
606
607 CURSOR C_GET_LIST_LINES(l_shp_list_id NUMBER) IS
608 SELECT shp_list_item_id
609 FROM ibe_sh_shp_list_items
610 WHERE shp_list_id = l_shp_list_id;
611
612 CURSOR C_GET_REL_LINES(l_shp_list_id NUMBER) IS
613 SELECT ISSR.shlitem_rel_id
614 FROM ibe_sh_shlitem_rels ISSR,
615 ibe_sh_shp_list_items ISSLI
616 WHERE ISSR.shp_list_item_id = ISSLI.shp_list_item_id
617 AND ISSLI.shp_list_id = l_shp_list_id;
618
619 BEGIN
620 -- Standard Start of API savepoint
621 SAVEPOINT Delete_PVT;
622
623 -- Standard call to check for call compatibility.
624 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
625 p_api_version,
626 L_API_NAME,
627 G_PKG_NAME )
628 THEN
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631
632 -- Initialize message list IF p_init_msg_list is set to TRUE.
633 IF FND_API.to_Boolean( p_init_msg_list ) THEN
634 FND_MSG_PUB.initialize;
635 END IF;
636
637 -- Initialize API return status to success
638 x_return_status := FND_API.G_RET_STS_SUCCESS;
639
640 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
641 IBE_Util.Debug('IBE_Shop_List_PVT.Delete(+)');
642 -- API body
643
644 IBE_Util.Debug('Delete Configuration Using list ids - Start');
645 END IF;
646 Delete_Config_From_Shp_List(
647 p_shp_list_ids => p_shop_list_ids,
648 p_shp_list_line_ids => NULL,
649 p_usage_exists => l_usage_exists,
650 p_error_message => x_msg_data,
651 p_return_value => l_return_value);
652 IF l_return_value = 0 THEN
653 RAISE FND_API.G_EXC_ERROR;
654 END IF;
655 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
656 IBE_Util.Debug('Done CZ_CF_API.delete_configuration at '
657 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
658 END IF;
659
660 FOR i IN 1..p_shop_list_ids.count LOOP
661 -- delete line relationships
662 OPEN C_GET_REL_LINES(p_shop_list_ids(i));
663 LOOP
664 FETCH C_GET_REL_LINES into l_shop_list_rel_line_id;
665 EXIT WHEN C_GET_REL_LINES%NOTFOUND;
666
667 IBE_ShopList_Line_Relation_PKG.Delete_Row(
668 p_SHLITEM_REL_ID => l_shop_list_rel_line_id);
669 END LOOP;
670 CLOSE C_GET_REL_LINES;
671
672 -- delete list lines
673 OPEN C_GET_LIST_LINES(p_shop_list_ids(i));
674 LOOP
675 FETCH C_GET_LIST_LINES into l_shop_list_line_id;
676 EXIT WHEN C_GET_LIST_LINES%NOTFOUND;
677
678 IBE_Shop_List_Line_PKG.Delete_Row(
679 p_SHP_LIST_ITEM_ID => l_shop_list_line_id);
680 END LOOP;
681 CLOSE C_GET_LIST_LINES;
682
683 BEGIN
684 -- delete list header
685 IBE_Shop_List_Header_PKG.Delete_Row(
686 p_SHP_LIST_ID => p_shop_list_ids(i),
687 p_OBJECT_VERSION_NUMBER => p_obj_ver_numbers(i));
688 EXCEPTION
689 WHEN NO_DATA_FOUND THEN
690 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
691 FND_MSG_PUB.add;
692 RAISE FND_API.G_EXC_ERROR;
693 END;
694 END LOOP;
695
696 -- End of API body.
697 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
698 IBE_Util.Debug('IBE_Shop_List_PVT.Delete(-)');
699 END IF;
700
701 -- Standard check of p_commit.
702 IF FND_API.To_Boolean( p_commit ) THEN
703 COMMIT WORK;
704 END IF;
705
706 -- Standard call to get message count and IF count is 1, get message info.
707 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
708 p_count => x_msg_count ,
709 p_data => x_msg_data );
710 EXCEPTION
711 WHEN FND_API.G_EXC_ERROR THEN
712 ROLLBACK TO Delete_PVT;
713 x_return_status := FND_API.G_RET_STS_ERROR;
714 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
715 p_count => x_msg_count,
716 p_data => x_msg_data);
717 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
718 ROLLBACK TO Delete_PVT;
719 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
720 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
721 p_count => x_msg_count,
722 p_data => x_msg_data);
723 WHEN OTHERS THEN
724 ROLLBACK TO Delete_PVT;
725 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
726
727 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
728 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
729 l_api_name);
730 END IF;
731
732 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
733 p_count => x_msg_count,
734 p_data => x_msg_data);
735 END Delete;
736
737
738 PROCEDURE Delete_All_Lines(
739 p_api_version IN NUMBER := 1 ,
740 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
741 p_commit IN VARCHAR2 := FND_API.G_FALSE,
742 x_return_status OUT NOCOPY VARCHAR2 ,
743 x_msg_count OUT NOCOPY NUMBER ,
744 x_msg_data OUT NOCOPY VARCHAR2 ,
745 p_shop_list_ids IN jtf_number_table ,
746 p_obj_ver_numbers IN jtf_number_table
747 )
748 IS
749 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_All_Lines';
750 L_API_VERSION CONSTANT NUMBER := 1.0;
751 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
752 l_shop_list_line_id NUMBER;
753 l_shop_list_rel_line_id NUMBER;
754 l_return_value NUMBER;
755 l_usage_exists NUMBER;
756
757 CURSOR C_GET_LIST_LINES(l_shp_list_id NUMBER) IS
758
759 SELECT SHP_LIST_ITEM_ID
760 FROM IBE_SH_SHP_LIST_ITEMS
761 WHERE SHP_LIST_ID = l_Shp_List_Id;
762
763 CURSOR C_GET_REL_LINES(l_shp_list_id NUMBER) IS
764
765 SELECT IBE_SH_SHLITEM_RELS.SHLITEM_REL_ID
766 FROM IBE_SH_SHLITEM_RELS, IBE_SH_SHP_LIST_ITEMS
767 WHERE IBE_SH_SHLITEM_RELS.SHP_LIST_ITEM_ID = IBE_SH_SHP_LIST_ITEMS.SHP_LIST_ITEM_ID
768 AND IBE_SH_SHP_LIST_ITEMS.SHP_LIST_ID = l_Shp_List_Id;
769
770 BEGIN
771 -- Standard Start of API savepoint
772 SAVEPOINT Delete_All_Lines_PVT;
773
774 -- Standard call to check for call compatibility.
775 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
776 p_api_version,
777 L_API_NAME,
778 G_PKG_NAME )
779 THEN
780 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
781 END IF;
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 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
792 IBE_Util.Debug('IBE_Shop_List_PVT.Delete_All_Lines(+)');
793 -- API body
794
795 IBE_Util.Debug('Delete Configuration Using list ids - Start');
796 END IF;
797 Delete_Config_From_Shp_List(
798 p_shp_list_ids => p_shop_list_ids,
799 p_shp_list_line_ids => NULL,
800 p_usage_exists => l_usage_exists,
801 p_error_message => x_msg_data,
802 p_return_value => l_return_value);
803 IF l_return_value = 0 THEN
804 RAISE FND_API.G_EXC_ERROR;
805 END IF;
806 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
807 IBE_Util.Debug('Done CZ_CF_API.delete_configuration at '
808 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
809 END IF;
810
811 FOR i IN 1..p_shop_list_ids.count LOOP
812 -- delete line relationships
813 OPEN C_GET_REL_LINES(p_shop_list_ids(i));
814 LOOP
815 FETCH C_GET_REL_LINES into l_shop_list_rel_line_id;
816 EXIT WHEN C_GET_REL_LINES%NOTFOUND;
817
818 IBE_ShopList_Line_Relation_PKG.Delete_Row(
819 p_SHLITEM_REL_ID => l_shop_list_rel_line_id);
820 END LOOP;
821 CLOSE C_GET_REL_LINES;
822
823 -- delete list lines
824 OPEN C_GET_LIST_LINES(p_shop_list_ids(i));
825 LOOP
826 FETCH C_GET_LIST_LINES into l_shop_list_line_id;
827 EXIT WHEN C_GET_LIST_LINES%NOTFOUND;
828
829 IBE_Shop_List_Line_PKG.Delete_Row(
830 p_SHP_LIST_ITEM_ID => l_shop_list_line_id);
831 END LOOP;
832 CLOSE C_GET_LIST_LINES;
833
834 -- update object version NUMBER for shopping list
835 UPDATE IBE_SH_SHP_LISTS
836 SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
837 WHERE SHP_LIST_ID = p_shop_list_ids(i)
838 AND OBJECT_VERSION_NUMBER = p_obj_ver_numbers(i);
839
840 IF (SQL%NOTFOUND) THEN
841 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
842 FND_MSG_PUB.add;
843 RAISE FND_API.G_EXC_ERROR;
844 END IF;
845 END LOOP;
846
847 -- End of API body.
848 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
849 IBE_Util.Debug('IBE_Shop_List_PVT.Delete_All_Lines(-)');
850 END IF;
851
852 -- Standard check of p_commit.
853 IF FND_API.To_Boolean( p_commit ) THEN
854 COMMIT WORK;
855 END IF;
856
857 -- Standard call to get message count and IF count is 1, get message info.
858 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
859 p_count => x_msg_count ,
860 p_data => x_msg_data );
861
862 EXCEPTION
863 WHEN FND_API.G_EXC_ERROR THEN
864 ROLLBACK TO Delete_All_Lines_PVT;
865 x_return_status := FND_API.G_RET_STS_ERROR;
866 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
867 p_count => x_msg_count,
868 p_data => x_msg_data);
869 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
870 ROLLBACK TO Delete_All_Lines_PVT;
871 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
872 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
873 p_count => x_msg_count,
874 p_data => x_msg_data);
875 WHEN OTHERS THEN
876 ROLLBACK TO Delete_All_Lines_PVT;
877 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
878
879 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
880 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
881 l_api_name);
882 END IF;
883
884 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
885 p_count => x_msg_count,
886 p_data => x_msg_data);
887 END Delete_All_Lines;
888
889
890 PROCEDURE Delete_Line(
891 p_object_version_number IN NUMBER,
892 p_Shp_List_Item_Id IN NUMBER
893 )
894 IS
895 l_shp_list_item_id_tbl jtf_number_table;
896 l_index NUMBER;
897 l_count NUMBER;
898 l_shp_list_item_id NUMBER;
899 l_shop_list_rel_line_id NUMBER;
900
901 CURSOR c1 IS
902 SELECT RELATED_SHP_LIST_ITEM_ID
903 FROM IBE_SH_SHLITEM_RELS
904 WHERE SHP_LIST_ITEM_ID = p_Shp_List_Item_Id;
905
906 CURSOR C_GET_REL_LINES(l_shp_list_line_id NUMBER) IS
907 SELECT SHLITEM_REL_ID
908 FROM IBE_SH_SHLITEM_RELS
909 WHERE SHP_LIST_ITEM_ID = l_shp_list_line_id;
910
911 BEGIN
912 -- DBMS_OUTPUT.PUT_LINE('Inside Delete_line p_Shp_List_Item_Id = ' || p_Shp_List_Item_Id);
913 -- DBMS_OUTPUT.PUT_LINE('Inside Delete_line p_object_version_number = ' || p_object_version_number);
914
915 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
916 IBE_Util.Debug('IBE_Shop_List_PVT.Delete_Line(+)...');
917 END IF;
918
919 l_shp_list_item_id_tbl := jtf_number_table();
920
921 SELECT COUNT(*) into l_count
922 FROM IBE_SH_SHLITEM_RELS
923 WHERE SHP_LIST_ITEM_ID = p_Shp_List_Item_Id;
924
925 l_shp_list_item_id_tbl.extend(l_count);
926
927 l_index := 1;
928
929 OPEN c1;
930
931 LOOP
932 FETCH c1 into l_shp_list_item_id;
933 EXIT WHEN c1%NOTFOUND;
934
935 l_shp_list_item_id_tbl(l_index) := l_shp_list_item_id;
936
937 -- increment counter
938 l_index := l_index + 1;
939 END LOOP;
940
941 CLOSE c1;
942
943 FOR i IN 1..l_count LOOP
944 IBE_Shop_List_PVT.Delete_Line(
945 p_object_version_number => p_object_version_number,
946 p_Shp_List_Item_Id => l_shp_list_item_id_tbl(i));
947
948 END LOOP;
949
950 -- delete line relationships
951 OPEN C_GET_REL_LINES(p_SHP_LIST_ITEM_ID);
952
953 LOOP
954
955 FETCH C_GET_REL_LINES into l_shop_list_rel_line_id;
956 EXIT WHEN C_GET_REL_LINES%NOTFOUND;
957
958 IBE_ShopList_Line_Relation_PKG.Delete_Row(
959 p_SHLITEM_REL_ID => l_shop_list_rel_line_id);
960
961 END LOOP;
962
963 CLOSE C_GET_REL_LINES;
964
965 IBE_Shop_List_Line_PKG.Delete_Row(
966 p_SHP_LIST_ITEM_ID => p_SHP_LIST_ITEM_ID,
967 p_object_version_number => p_object_version_number);
968
969 END Delete_Line;
970
971 PROCEDURE Delete_Lines(
972 p_api_version IN NUMBER := 1 ,
973 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
974 p_commit IN VARCHAR2 := FND_API.G_FALSE,
975 x_return_status OUT NOCOPY VARCHAR2 ,
976 x_msg_count OUT NOCOPY NUMBER ,
977 x_msg_data OUT NOCOPY VARCHAR2 ,
978 p_shop_list_line_ids IN jtf_number_table ,
979 p_obj_ver_numbers IN jtf_number_table
980 )
981 IS
982 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Lines';
983 L_API_VERSION CONSTANT NUMBER := 1.0;
984 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
985 l_shop_list_id NUMBER;
986 l_shop_list_line_id NUMBER;
987 l_shop_list_rel_line_id NUMBER;
988 l_count NUMBER;
989 l_usage_exists NUMBER;
990 l_return_value NUMBER;
991
992 BEGIN
993 -- Standard Start of API savepoint
994 SAVEPOINT Delete_Lines_PVT;
995
996 -- Standard call to check for call compatibility.
997 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
998 p_api_version,
999 L_API_NAME,
1000 G_PKG_NAME )
1001 THEN
1002 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1003 END IF;
1004
1005 -- Initialize message list IF p_init_msg_list is set to TRUE.
1006 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1007 FND_MSG_PUB.initialize;
1008 END IF;
1009
1010 -- Initialize API return status to success
1011 x_return_status := FND_API.G_RET_STS_SUCCESS;
1012
1013 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1014 IBE_Util.Debug('IBE_Shop_List_PVT.Delete_Lines(+)');
1015 END IF;
1016 -- API body
1017
1018 l_count := p_shop_list_line_ids.COUNT;
1019
1020 -- DBMS_OUTPUT.PUT_LINE('L_COUNT = ' || l_count);
1021 -- DBMS_OUTPUT.PUT_LINE('l_shop_list_id = ' || l_shop_list_id);
1022 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1023 IBE_Util.Debug('Delete Configuration Using line ids - Start');
1024 END IF;
1025 Delete_Config_From_Shp_List(
1026 p_shp_list_ids => NULL,
1027 p_shp_list_line_ids => p_shop_list_line_ids,
1028 p_usage_exists => l_usage_exists,
1029 p_error_message => x_msg_data,
1030 p_return_value => l_return_value);
1031
1032 IF l_return_value = 0 THEN
1033 RAISE FND_API.G_EXC_ERROR;
1034 END IF;
1035 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1036 IBE_Util.Debug('Done CZ_CF_API.delete_configuration at '
1037 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
1038 END IF;
1039
1040 -- select shp_list_id to update object_version_number of the list
1041 IF l_count > 0 THEN
1042 BEGIN
1043 select SHP_LIST_ID into l_shop_list_id
1044 from IBE_SH_SHP_LIST_ITEMS
1045 where SHP_LIST_ITEM_ID = p_shop_list_line_ids(1);
1046 EXCEPTION
1047 WHEN NO_DATA_FOUND THEN
1048 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
1049 FND_MSG_PUB.add;
1050 RAISE FND_API.G_EXC_ERROR;
1051 END;
1052 END IF;
1053
1054 -- DBMS_OUTPUT.PUT_LINE('l_shop_list_id = ' || l_shop_list_id);
1055
1056 FOR i IN 1..l_count LOOP
1057 BEGIN
1058 -- DBMS_OUTPUT.PUT_LINE('Calling Delete_line p_shop_list_line_id = ' || p_shop_list_line_ids(i));
1059 -- DBMS_OUTPUT.PUT_LINE('Calling Delete_line p_object_version_number = ' || p_obj_ver_numbers(i));
1060
1061 Delete_Line(
1062 p_object_version_number => p_obj_ver_numbers(i),
1063 p_Shp_List_Item_Id => p_shop_list_line_ids(i));
1064 EXCEPTION
1065 WHEN NO_DATA_FOUND THEN
1066 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
1067 FND_MSG_PUB.add;
1068 RAISE FND_API.G_EXC_ERROR;
1069 END;
1070 END LOOP;
1071
1072 -- dbms_output.put_line('now updating header');
1073 -- update object version NUMBER for shopping list
1074 UPDATE IBE_SH_SHP_LISTS
1075 SET OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1
1076 WHERE SHP_LIST_ID = l_shop_list_id;
1077
1078 IF (SQL%NOTFOUND) THEN
1079 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
1080 FND_MSG_PUB.add;
1081 RAISE FND_API.G_EXC_ERROR;
1082 END IF;
1083
1084 -- End of API body.
1085 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1086 IBE_Util.Debug('IBE_Shop_List_PVT.Delete_Lines(-)');
1087 END IF;
1088
1089 -- Standard check of p_commit.
1090 IF FND_API.To_Boolean( p_commit ) THEN
1091 COMMIT WORK;
1092 END IF;
1093
1094 -- Standard call to get message count and IF count is 1, get message info.
1095 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1096 p_count => x_msg_count ,
1097 p_data => x_msg_data );
1098
1099 EXCEPTION
1100 WHEN FND_API.G_EXC_ERROR THEN
1101 ROLLBACK TO Delete_Lines_PVT;
1102 x_return_status := FND_API.G_RET_STS_ERROR;
1103 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1104 p_count => x_msg_count,
1105 p_data => x_msg_data);
1106 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1107 ROLLBACK TO Delete_Lines_PVT;
1108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1109 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1110 p_count => x_msg_count,
1111 p_data => x_msg_data);
1112 WHEN OTHERS THEN
1113 ROLLBACK TO Delete_Lines_PVT;
1114 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1115
1116 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1117 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1118 l_api_name);
1119 END IF;
1120
1121 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1122 p_count => x_msg_count,
1123 p_data => x_msg_data);
1124 END Delete_Lines;
1125
1126 PROCEDURE Save(
1127 p_api_version IN NUMBER := 1 ,
1128 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1129 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1130 x_return_status OUT NOCOPY VARCHAR2 ,
1131 x_msg_count OUT NOCOPY NUMBER ,
1132 x_msg_data OUT NOCOPY VARCHAR2 ,
1133 p_combine_same_item IN VARCHAR2 := FND_API.G_MISS_CHAR ,
1134 p_sl_header_rec IN SL_Header_Rec_Type ,
1135 p_sl_line_tbl IN SL_Line_Tbl_Type := G_MISS_SL_LINE_TBL ,
1136 p_sl_line_rel_tbl IN SL_Line_Rel_Tbl_Type := G_MISS_SL_LINE_REL_TBL,
1137 x_sl_header_id OUT NOCOPY NUMBER
1138 )
1139 IS
1140 L_API_NAME CONSTANT VARCHAR2(30) := 'Save';
1141 L_API_VERSION CONSTANT NUMBER := 1.0;
1142 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
1143 TYPE Number_Tbl_Type IS TABLE OF NUMBER
1144 INDEX BY BINARY_INTEGER;
1145 l_line_id_tbl Number_Tbl_Type;
1146 i PLS_INTEGER;
1147 l_shp_list_item_id NUMBER;
1148 l_quantity NUMBER;
1149 l_sl_line_rel_id NUMBER;
1150 l_combine_same_item VARCHAR2(30);
1151 l_sl_line_rec SL_Line_Rec_Type;
1152 BEGIN
1153 -- Standard Start of API savepoint
1154 SAVEPOINT Save_PVT;
1155
1156 -- Standard call to check for call compatibility.
1157 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
1158 p_api_version,
1159 L_API_NAME,
1160 G_PKG_NAME )
1161 THEN
1162 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1163 END IF;
1164
1165 -- Initialize message list IF p_init_msg_list is set to TRUE.
1166 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1167 FND_MSG_PUB.initialize;
1168 END IF;
1169
1170 -- Initialize API return status to success
1171 x_return_status := FND_API.G_RET_STS_SUCCESS;
1172
1173 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1174 IBE_Util.Debug('IBE_Shop_List_PVT.Save(+)');
1175 END IF;
1176 --dbms_output.put_line('IBE_Shop_List_PVT.Save(+)');
1177 -- API body
1178
1179 IF p_combine_same_item = FND_API.G_MISS_CHAR THEN
1180 l_combine_same_item := FND_Profile.Value('IBE_SC_MERGE_SHOPCART_LINES');
1181 ELSE
1182 l_combine_same_item := p_combine_same_item;
1183 END IF;
1184
1185 --dbms_output.put_line('Saving list header');
1186
1187 IF p_sl_header_rec.shp_list_id = FND_API.G_MISS_NUM THEN
1188 -- A new shopping list. Create a shopping list.
1189 --dbms_output.put_line('New line to be created for header');
1190
1191 BEGIN
1192
1193 --dbms_output.put_line('p_sl_header_rec.party_id = ' || p_sl_header_rec.party_id);
1194 --dbms_output.put_line('p_sl_header_rec.cust_account_id = ' || p_sl_header_rec.cust_account_id);
1195 --dbms_output.put_line('p_sl_header_rec.shopping_list_name = ' || p_sl_header_rec.shopping_list_name);
1196 --dbms_output.put_line('p_sl_header_rec.description = ' || p_sl_header_rec.description);
1197 --dbms_output.put_line('p_sl_header_rec.org_id = ' || p_sl_header_rec.org_id);
1198 --dbms_output.put_line('p_sl_header_rec.shp_list_id = ' || p_sl_header_rec.shp_list_id);
1199
1200 IBE_Shop_List_Header_PKG.Insert_Row(
1201 x_shp_list_id => x_sl_header_id ,
1202 p_request_id => p_sl_header_rec.request_id ,
1203 p_program_application_id => p_sl_header_rec.program_application_id ,
1204 p_program_id => p_sl_header_rec.program_id ,
1205 p_program_update_date => p_sl_header_rec.program_update_date ,
1206 p_object_version_number => p_sl_header_rec.object_version_number ,
1207 p_created_by => p_sl_header_rec.created_by ,
1208 p_creation_date => p_sl_header_rec.creation_date ,
1209 p_last_updated_by => p_sl_header_rec.last_updated_by ,
1210 p_last_update_date => p_sl_header_rec.last_update_date ,
1211 p_last_update_login => p_sl_header_rec.last_update_login ,
1212 p_party_id => p_sl_header_rec.party_id ,
1213 p_cust_account_id => p_sl_header_rec.cust_account_id ,
1214 p_shopping_list_name => p_sl_header_rec.shopping_list_name ,
1215 p_description => p_sl_header_rec.description ,
1216 p_attribute_category => p_sl_header_rec.attribute_category ,
1217 p_attribute1 => p_sl_header_rec.attribute1 ,
1218 p_attribute2 => p_sl_header_rec.attribute2 ,
1219 p_attribute3 => p_sl_header_rec.attribute3 ,
1220 p_attribute4 => p_sl_header_rec.attribute4 ,
1221 p_attribute5 => p_sl_header_rec.attribute5 ,
1222 p_attribute6 => p_sl_header_rec.attribute6 ,
1223 p_attribute7 => p_sl_header_rec.attribute7 ,
1224 p_attribute8 => p_sl_header_rec.attribute8 ,
1225 p_attribute9 => p_sl_header_rec.attribute9 ,
1226 p_attribute10 => p_sl_header_rec.attribute10 ,
1227 p_attribute11 => p_sl_header_rec.attribute11 ,
1228 p_attribute12 => p_sl_header_rec.attribute12 ,
1229 p_attribute13 => p_sl_header_rec.attribute13 ,
1230 p_attribute14 => p_sl_header_rec.attribute14 ,
1231 p_attribute15 => p_sl_header_rec.attribute15 ,
1232 p_org_id => p_sl_header_rec.org_id);
1233
1234 --dbms_output.put_line('New line created for header, shp_list_id = ' || x_sl_header_id);
1235 EXCEPTION
1236 WHEN DUP_VAL_ON_INDEX THEN
1237 FND_MESSAGE.set_name('IBE', 'IBE_SL_DUPLICATE_LISTNAME');
1238 FND_MSG_PUB.add;
1239 RAISE FND_API.G_EXC_ERROR;
1240
1241 WHEN NO_DATA_FOUND THEN
1242 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
1243 FND_MSG_PUB.add;
1244 RAISE FND_API.G_EXC_ERROR;
1245 END;
1246 ELSE
1247 -- Existing shopping list. Update the shopping list.
1248 --dbms_output.put_line('Update header');
1249 BEGIN
1250 IBE_Shop_List_Header_PKG.Update_Row(
1251 p_shp_list_id => p_sl_header_rec.shp_list_id ,
1252 p_request_id => p_sl_header_rec.request_id ,
1253 p_program_application_id => p_sl_header_rec.program_application_id ,
1254 p_program_id => p_sl_header_rec.program_id ,
1255 p_program_update_date => p_sl_header_rec.program_update_date ,
1256 p_object_version_number => p_sl_header_rec.object_version_number ,
1257 p_created_by => p_sl_header_rec.created_by ,
1258 p_creation_date => p_sl_header_rec.creation_date ,
1259 p_last_updated_by => p_sl_header_rec.last_updated_by ,
1260 p_last_update_date => p_sl_header_rec.last_update_date ,
1261 p_last_update_login => p_sl_header_rec.last_update_login ,
1262 p_party_id => p_sl_header_rec.party_id ,
1263 p_cust_account_id => p_sl_header_rec.cust_account_id ,
1264 p_shopping_list_name => p_sl_header_rec.shopping_list_name ,
1265 p_description => p_sl_header_rec.description ,
1266 p_attribute_category => p_sl_header_rec.attribute_category ,
1267 p_attribute1 => p_sl_header_rec.attribute1 ,
1268 p_attribute2 => p_sl_header_rec.attribute2 ,
1269 p_attribute3 => p_sl_header_rec.attribute3 ,
1270 p_attribute4 => p_sl_header_rec.attribute4 ,
1271 p_attribute5 => p_sl_header_rec.attribute5 ,
1272 p_attribute6 => p_sl_header_rec.attribute6 ,
1273 p_attribute7 => p_sl_header_rec.attribute7 ,
1274 p_attribute8 => p_sl_header_rec.attribute8 ,
1275 p_attribute9 => p_sl_header_rec.attribute9 ,
1276 p_attribute10 => p_sl_header_rec.attribute10 ,
1277 p_attribute11 => p_sl_header_rec.attribute11 ,
1278 p_attribute12 => p_sl_header_rec.attribute12 ,
1279 p_attribute13 => p_sl_header_rec.attribute13 ,
1280 p_attribute14 => p_sl_header_rec.attribute14 ,
1281 p_attribute15 => p_sl_header_rec.attribute15 ,
1282 p_org_id => p_sl_header_rec.org_id);
1283
1284 x_sl_header_id := p_sl_header_rec.shp_list_id;
1285 --dbms_output.put_line('header updated, shp_list_id = ' || x_sl_header_id);
1286 EXCEPTION
1287 WHEN DUP_VAL_ON_INDEX THEN
1288 FND_MESSAGE.set_name('IBE', 'IBE_SL_DUPLICATE_LISTNAME');
1289 FND_MSG_PUB.add;
1290 RAISE FND_API.G_EXC_ERROR;
1291
1292 WHEN NO_DATA_FOUND THEN
1293 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
1294 FND_MSG_PUB.add;
1295 RAISE FND_API.G_EXC_ERROR;
1296 END;
1297 END IF;
1298
1299 --dbms_output.put_line('After saving header x_sl_header_id = ' || x_sl_header_id);
1300 --dbms_output.put_line('p_sl_line_tbl.count = ' || p_sl_line_tbl.count);
1301
1302 IF p_sl_line_tbl.COUNT > 0 THEN
1303 -- There are shopping list line
1304 FOR i IN 1..p_sl_line_tbl.COUNT LOOP
1305 l_sl_line_rec := p_sl_line_tbl(i);
1306
1307 --dbms_output.put_line('Checking whether to update the quantity or insert new line');
1308
1309 IF p_sl_header_rec.shp_list_id <> FND_API.G_MISS_NUM
1310 AND l_sl_line_rec.shp_list_item_id = FND_API.G_MISS_NUM
1311 AND l_sl_line_rec.item_type_code = 'STD'
1312 AND l_combine_same_item = 'Y' THEN
1313 BEGIN
1314 SELECT shp_list_item_id, quantity
1315 INTO l_shp_list_item_id, l_quantity
1316 FROM ibe_sh_shp_list_items
1317 WHERE shp_list_id = p_sl_header_rec.shp_list_id
1318 AND inventory_item_id = l_sl_line_rec.inventory_item_id
1319 AND organization_id = l_sl_line_rec.organization_id
1320 AND uom_code = l_sl_line_rec.uom_code
1321 AND item_type_code = 'STD';
1322
1323 l_sl_line_rec.shp_list_item_id := l_shp_list_item_id;
1324 l_sl_line_rec.quantity := l_sl_line_rec.quantity + l_quantity;
1325 EXCEPTION
1326 WHEN NO_DATA_FOUND THEN
1327 NULL;
1328 END;
1329 END IF;
1330
1331 --dbms_output.put_line('Checking for the line whether to insert or update the line');
1332
1333 IF l_sl_line_rec.shp_list_item_id = FND_API.G_MISS_NUM THEN
1334 -- New line. Add a line.
1335 --dbms_output.put_line('Inserting line ' || i);
1336
1337 IBE_Shop_List_Line_PKG.Insert_Row(
1338 x_shp_list_item_id => l_shp_list_item_id ,
1339 p_object_version_number => l_sl_line_rec.object_version_number ,
1340 p_creation_date => l_sl_line_rec.creation_date ,
1341 p_created_by => l_sl_line_rec.created_by ,
1342 p_last_updated_by => l_sl_line_rec.last_updated_by ,
1343 p_last_update_date => l_sl_line_rec.last_update_date ,
1344 p_last_update_login => l_sl_line_rec.last_update_login ,
1345 p_request_id => l_sl_line_rec.request_id ,
1346 p_program_id => l_sl_line_rec.program_id ,
1347 p_program_application_id => l_sl_line_rec.program_application_id ,
1348 p_program_update_date => l_sl_line_rec.program_update_date ,
1349 p_shp_list_id => x_sl_header_id ,
1350 p_inventory_item_id => l_sl_line_rec.inventory_item_id ,
1351 p_organization_id => l_sl_line_rec.organization_id ,
1352 p_uom_code => l_sl_line_rec.uom_code ,
1353 p_quantity => l_sl_line_rec.quantity ,
1354 p_config_header_id => l_sl_line_rec.config_header_id ,
1355 p_config_revision_num => l_sl_line_rec.config_revision_num ,
1356 p_complete_configuration_flag => l_sl_line_rec.complete_configuration_flag ,
1357 p_valid_configuration_flag => l_sl_line_rec.valid_configuration_flag ,
1358 p_item_type_code => l_sl_line_rec.item_type_code ,
1359 p_attribute_category => l_sl_line_rec.attribute_category ,
1360 p_attribute1 => l_sl_line_rec.attribute1 ,
1361 p_attribute2 => l_sl_line_rec.attribute2 ,
1362 p_attribute3 => l_sl_line_rec.attribute3 ,
1363 p_attribute4 => l_sl_line_rec.attribute4 ,
1364 p_attribute5 => l_sl_line_rec.attribute5 ,
1365 p_attribute6 => l_sl_line_rec.attribute6 ,
1366 p_attribute7 => l_sl_line_rec.attribute7 ,
1367 p_attribute8 => l_sl_line_rec.attribute8 ,
1368 p_attribute9 => l_sl_line_rec.attribute9 ,
1369 p_attribute10 => l_sl_line_rec.attribute10 ,
1370 p_attribute11 => l_sl_line_rec.attribute11 ,
1371 p_attribute12 => l_sl_line_rec.attribute12 ,
1372 p_attribute13 => l_sl_line_rec.attribute13 ,
1373 p_attribute14 => l_sl_line_rec.attribute14 ,
1374 p_attribute15 => l_sl_line_rec.attribute15 ,
1375 p_org_id => l_sl_line_rec.org_id);
1376 l_line_id_tbl(i) := l_shp_list_item_id;
1377
1378 --dbms_output.put_line('l_line_id_tbl(' || i || ') = ' || l_line_id_tbl(i));
1379
1380 ELSE
1381 -- Existing line. Update the line.
1382 --dbms_output.put_line('Updating line ' || i);
1383 IBE_Shop_List_Line_PKG.Update_Row(
1384 p_shp_list_item_id => l_sl_line_rec.shp_list_item_id ,
1385 p_object_version_number => l_sl_line_rec.object_version_number ,
1386 p_creation_date => l_sl_line_rec.creation_date ,
1387 p_created_by => l_sl_line_rec.created_by ,
1388 p_last_updated_by => l_sl_line_rec.last_updated_by ,
1389 p_last_update_date => l_sl_line_rec.last_update_date ,
1390 p_last_update_login => l_sl_line_rec.last_update_login ,
1391 p_request_id => l_sl_line_rec.request_id ,
1392 p_program_id => l_sl_line_rec.program_id ,
1393 p_program_application_id => l_sl_line_rec.program_application_id ,
1394 p_program_update_date => l_sl_line_rec.program_update_date ,
1395 p_shp_list_id => l_sl_line_rec.shp_list_id ,
1396 p_inventory_item_id => l_sl_line_rec.inventory_item_id ,
1397 p_organization_id => l_sl_line_rec.organization_id ,
1398 p_uom_code => l_sl_line_rec.uom_code ,
1399 p_quantity => l_sl_line_rec.quantity ,
1400 p_config_header_id => l_sl_line_rec.config_header_id ,
1401 p_config_revision_num => l_sl_line_rec.config_revision_num ,
1402 p_complete_configuration_flag => l_sl_line_rec.complete_configuration_flag ,
1403 p_valid_configuration_flag => l_sl_line_rec.valid_configuration_flag ,
1404 p_item_type_code => l_sl_line_rec.item_type_code ,
1405 p_attribute_category => l_sl_line_rec.attribute_category ,
1406 p_attribute1 => l_sl_line_rec.attribute1 ,
1407 p_attribute2 => l_sl_line_rec.attribute2 ,
1408 p_attribute3 => l_sl_line_rec.attribute3 ,
1409 p_attribute4 => l_sl_line_rec.attribute4 ,
1410 p_attribute5 => l_sl_line_rec.attribute5 ,
1411 p_attribute6 => l_sl_line_rec.attribute6 ,
1412 p_attribute7 => l_sl_line_rec.attribute7 ,
1413 p_attribute8 => l_sl_line_rec.attribute8 ,
1414 p_attribute9 => l_sl_line_rec.attribute9 ,
1415 p_attribute10 => l_sl_line_rec.attribute10 ,
1416 p_attribute11 => l_sl_line_rec.attribute11 ,
1417 p_attribute12 => l_sl_line_rec.attribute12 ,
1418 p_attribute13 => l_sl_line_rec.attribute13 ,
1419 p_attribute14 => l_sl_line_rec.attribute14 ,
1420 p_attribute15 => l_sl_line_rec.attribute15 ,
1421 p_org_id => l_sl_line_rec.org_id);
1422 l_line_id_tbl(i) := l_sl_line_rec.shp_list_item_id;
1423 END IF;
1424 END LOOP;
1425
1426 --dbms_output.put_line('After saving list lines');
1427 --dbms_output.put_line('Before saving related lines');
1428
1429 IF p_sl_line_rel_tbl.COUNT > 0 THEN
1430 -- There are shopping list line relationships
1431 FOR i IN 1..p_sl_line_rel_tbl.COUNT LOOP
1432 IF p_sl_line_rel_tbl(i).shp_list_item_id = FND_API.G_MISS_NUM THEN
1433 -- New line relationship. Add a line relationship.
1434 IBE_ShopList_Line_Relation_PKG.Insert_Row(
1435 x_shlitem_rel_id => l_sl_line_rel_id ,
1436 p_request_id => p_sl_line_rel_tbl(i).request_id ,
1437 p_program_application_id => p_sl_line_rel_tbl(i).program_application_id ,
1438 p_program_id => p_sl_line_rel_tbl(i).program_id ,
1439 p_program_update_date => p_sl_line_rel_tbl(i).program_update_date ,
1440 p_object_version_number => p_sl_line_rel_tbl(i).object_version_number ,
1441 p_created_by => p_sl_line_rel_tbl(i).created_by ,
1442 p_creation_date => p_sl_line_rel_tbl(i).creation_date ,
1443 p_last_updated_by => p_sl_line_rel_tbl(i).last_updated_by ,
1444 p_last_update_date => p_sl_line_rel_tbl(i).last_update_date ,
1445 p_last_update_login => p_sl_line_rel_tbl(i).last_update_login ,
1446 p_shp_list_item_id => l_line_id_tbl(p_sl_line_rel_tbl(i).line_index),
1447 p_related_shp_list_item_id => l_line_id_tbl(p_sl_line_rel_tbl(i).related_line_index),
1448 p_relationship_type_code => p_sl_line_rel_tbl(i).relationship_type_code);
1449 END IF;
1450 END LOOP;
1451 END IF;
1452
1453 --dbms_output.put_line('After saving related lines');
1454
1455 END IF;
1456 -- End of API body.
1457 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1458 IBE_Util.Debug('IBE_Shop_List_PVT.Save(-)');
1459 END IF;
1460 --dbms_output.put_line('IBE_Shop_List_PVT.Save(-)');
1461
1462 -- Standard check of p_commit.
1463 IF FND_API.To_Boolean( p_commit ) THEN
1464 COMMIT WORK;
1465 END IF;
1466
1467 -- Standard call to get message count and IF count is 1, get message info.
1468 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1469 p_count => x_msg_count ,
1470 p_data => x_msg_data );
1471
1472 EXCEPTION
1473 WHEN FND_API.G_EXC_ERROR THEN
1474 ROLLBACK TO Save_PVT;
1475 x_return_status := FND_API.G_RET_STS_ERROR;
1476 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1477 p_count => x_msg_count,
1478 p_data => x_msg_data);
1479 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1480 ROLLBACK TO Save_PVT;
1481 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1482 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1483 p_count => x_msg_count,
1484 p_data => x_msg_data);
1485 WHEN OTHERS THEN
1486 ROLLBACK TO Save_PVT;
1487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1488
1489 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1490 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1491 l_api_name);
1492 END IF;
1493
1494 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1495 p_count => x_msg_count,
1496 p_data => x_msg_data);
1497 END Save;
1498
1499
1500 PROCEDURE Update_Config_Item_Lines(
1501 x_return_status OUT NOCOPY VARCHAR2,
1502 x_msg_count OUT NOCOPY NUMBER ,
1503 x_msg_data OUT NOCOPY VARCHAR2,
1504 px_sl_line_tbl IN OUT NOCOPY SL_Line_Tbl_Type
1505 )
1506 IS
1507 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Config_Item_Lines';
1508 l_old_config_header_id NUMBER;
1509 l_new_config_header_id NUMBER;
1510 l_old_config_revision_num NUMBER;
1511 l_new_config_revision_num NUMBER;
1512
1513 -- ER#4025142
1514 --l_return_value NUMBER;
1515 l_api_version CONSTANT NUMBER := 1.0;
1516 l_ret_status VARCHAR2(1);
1517 l_msg_count INTEGER;
1518 l_orig_item_id_tbl CZ_API_PUB.number_tbl_type;
1519 l_new_item_id_tbl CZ_API_PUB.number_tbl_type;
1520
1521 BEGIN
1522 -- Initialize API return status to success
1523 x_return_status := FND_API.G_RET_STS_SUCCESS;
1524
1525 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1526 IBE_Util.Debug('IBE_Shop_List_PVT.Update_Config_Item_Lines(+)');
1527 END IF;
1528
1529 -- API body
1530 FOR i IN 1..px_sl_line_tbl.COUNT LOOP
1531 IF px_sl_line_tbl(i).item_type_code = 'MDL' THEN
1532 l_old_config_header_id := px_sl_line_tbl(i).config_header_id;
1533 l_old_config_revision_num := px_sl_line_tbl(i).config_revision_num;
1534
1535 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1536 IBE_Util.Debug('old config header id = '|| l_old_config_header_id);
1537 IBE_Util.Debug('old config revision NUMBER = '|| l_old_config_revision_num);
1538 IBE_Util.Debug('Call CZ_CONFIG_API_PUB.copy_configuration at'
1539 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
1540 END IF;
1541
1542 --ER#4025142
1543 CZ_CONFIG_API_PUB.copy_configuration(p_api_version => l_api_version
1544 ,p_config_hdr_id => l_old_config_header_id
1545 ,p_config_rev_nbr => l_old_config_revision_num
1546 ,p_copy_mode => CZ_API_PUB.G_NEW_HEADER_COPY_MODE
1547 ,x_config_hdr_id => l_new_config_header_id
1548 ,x_config_rev_nbr => l_new_config_revision_num
1549 ,x_orig_item_id_tbl => l_orig_item_id_tbl
1550 ,x_new_item_id_tbl => l_new_item_id_tbl
1551 ,x_return_status => l_ret_status
1552 ,x_msg_count => l_msg_count
1553 ,x_msg_data => x_msg_data);
1554 IF (l_ret_status <> FND_API.G_RET_STS_SUCCESS) THEN
1555 RAISE FND_API.G_EXC_ERROR;
1556 END IF;
1557
1558 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1559 IBE_Util.Debug('Done CZ_CONFIG_API_PUB.Copy_Configuration at'
1560 || TO_CHAR(SYSDATE, 'mm/dd/yyyy:hh24:MI:SS'));
1561 END IF;
1562
1563 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1564 IBE_Util.Debug('new config header id = '|| l_new_config_header_id);
1565 IBE_Util.Debug('new config revision NUMBER = '|| l_new_config_revision_num);
1566 END IF;
1567
1568 -- update all other dtl table
1569 FOR j IN 1..px_sl_line_tbl.COUNT LOOP
1570 IF px_sl_line_tbl(j).config_header_id = l_old_config_header_id
1571 AND px_sl_line_tbl(j).config_revision_num = l_old_config_revision_num THEN
1572 px_sl_line_tbl(j).config_header_id := l_new_config_header_id;
1573 px_sl_line_tbl(j).config_revision_num := l_new_config_revision_num;
1574 END IF;
1575 END LOOP;
1576 END IF;
1577 END LOOP;
1578 -- End of API body.
1579
1580 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1581 IBE_Util.Debug('IBE_Shop_List_PVT.Update_Config_Item_Lines(-)');
1582 END IF;
1583
1584 -- Standard call to get message count and IF count is 1, get message info.
1585 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1586 p_count => x_msg_count ,
1587 p_data => x_msg_data );
1588
1589 EXCEPTION
1590 WHEN FND_API.G_EXC_ERROR THEN
1591 x_return_status := FND_API.G_RET_STS_ERROR;
1592 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1593 p_count => x_msg_count,
1594 p_data => x_msg_data);
1595 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1596 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1597 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1598 p_count => x_msg_count,
1599 p_data => x_msg_data);
1600 WHEN OTHERS THEN
1601 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1602
1603 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1604 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1605 l_api_name);
1606 END IF;
1607
1608 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1609 p_count => x_msg_count,
1610 p_data => x_msg_data);
1611 END Update_Config_Item_Lines;
1612
1613
1614 PROCEDURE Save_List_From_Items(
1615 p_api_version IN NUMBER := 1 ,
1616 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1617 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1618 x_return_status OUT NOCOPY VARCHAR2 ,
1619 x_msg_count OUT NOCOPY NUMBER ,
1620 x_msg_data OUT NOCOPY VARCHAR2 ,
1621 p_sl_line_ids IN jtf_number_table ,
1622 p_sl_line_ovns IN jtf_number_table := NULL ,
1623 p_mode IN VARCHAR2 := 'MERGE' ,
1624 p_combine_same_item IN VARCHAR2 := FND_API.G_MISS_CHAR,
1625 p_sl_header_rec IN SL_Header_Rec_Type ,
1626 x_sl_header_id OUT NOCOPY NUMBER
1627 )
1628 IS
1629 L_API_NAME CONSTANT VARCHAR2(30) := 'Save_List_From_Items';
1630 L_API_VERSION CONSTANT NUMBER := 1.0;
1631 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
1632
1633 l_list_line_id_map_tbl jtf_number_table := jtf_number_table();
1634 l_list_line_index_tbl jtf_number_table := jtf_number_table();
1635 l_list_header_id_tbl jtf_number_table;
1636 l_obj_ver_num_tbl jtf_number_table;
1637 l_list_line_id_tbl jtf_number_table := jtf_number_table();
1638 l_sl_header_rec SL_Header_Rec_Type;
1639 l_sl_line_tbl SL_Line_Tbl_Type;
1640 l_sl_line_rel_tbl SL_Line_Rel_Tbl_Type;
1641 l_qte_line_relation_tbl ASO_Quote_Pub.Line_Rltship_Tbl_Type;
1642 BEGIN
1643 -- Standard Start of API savepoint
1644 SAVEPOINT Save_List_From_Items_PVT;
1645
1646 -- Standard call to check for call compatibility.
1647 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
1648 p_api_version,
1649 L_API_NAME,
1650 G_PKG_NAME)
1651 THEN
1652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1653 END IF;
1654
1655 -- Initialize message list IF p_init_msg_list is set to TRUE.
1656 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1657 FND_MSG_PUB.initialize;
1658 END IF;
1659
1660 -- Initialize API return status to success
1661 x_return_status := FND_API.G_RET_STS_SUCCESS;
1662
1663 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1664 IBE_Util.Debug('IBE_Shop_List_PVT.Save_List_From_Items(+)');
1665 END IF;
1666 -- API body
1667
1668 -- IF p_sl_header_rec.shp_list_id is not null, i.e. mode will be either 'ADDTO' or 'REPLACE'.
1669 -- IN this case we just take leave header information as it is, but is p_sl_header_rec.shp_list_id
1670 -- is null, we create new line for shopping list header.
1671
1672 --dbms_output.put_line('p_sl_header_rec.shp_list_id = ' || p_sl_header_rec.shp_list_id);
1673 l_sl_header_rec := p_sl_header_rec;
1674
1675 IF (l_sl_header_rec.shp_list_id <> FND_API.G_MISS_NUM) THEN
1676 IF (p_mode = 'REPLACE') THEN
1677 -- create jtf_number_table of list id and obj. ver num to pass to Delete_All_Lines
1678 l_list_header_id_tbl := jtf_number_table(l_sl_header_rec.shp_list_id);
1679 l_obj_ver_num_tbl := jtf_number_table(l_sl_header_rec.object_version_number);
1680
1681 IBE_Shop_List_PVT.Delete_All_Lines(
1682 p_api_version => p_api_version,
1683 p_init_msg_list => p_init_msg_list,
1684 p_commit => p_commit,
1685 x_return_status => x_return_status,
1686 x_msg_count => x_msg_count,
1687 x_msg_data => x_msg_data,
1688 p_shop_list_ids => l_list_header_id_tbl,
1689 p_obj_ver_numbers => l_obj_ver_num_tbl);
1690
1691 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1692 RAISE FND_API.G_EXC_ERROR;
1693 END IF;
1694
1695 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1696 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1697 END IF;
1698
1699 -- Delete_All_Lines updates object_version_number of shopping list
1700 -- header, therefore IF we pass p_object_version_number IN save THEN
1701 -- it will fail as it has been incremented by one. Set it to
1702 -- FND_API.G_MISS_NUM so that save() will not fail.
1703 l_sl_header_rec.object_version_number := FND_API.G_MISS_NUM;
1704 END IF;
1705 END IF;
1706
1707 /*
1708 * Call Include_Related_Lines() to include all the related lines, i.e.,
1709 * all the children lines of configurable items.
1710 */
1711 Include_Related_Lines(
1712 p_qte_line_rel_tbl => FND_API.G_FALSE ,
1713 p_list_line_id_tbl => p_sl_line_ids ,
1714 x_list_line_id_tbl => l_list_line_id_tbl,
1715 x_qte_line_relation_tbl => l_qte_line_relation_tbl,
1716 x_list_line_relation_tbl => l_sl_line_rel_tbl);
1717
1718 Set_List_Lines_From_List_Lines(
1719 p_list_line_id_tbl => l_list_line_id_tbl ,
1720 p_list_header_id => l_sl_header_rec.shp_list_id,
1721 p_combine_same_item => p_combine_same_item ,
1722 x_list_line_tbl => l_sl_line_tbl ,
1723 x_list_line_id_map_tbl => l_list_line_id_map_tbl ,
1724 x_list_line_index_tbl => l_list_line_index_tbl);
1725
1726 Update_Config_Item_Lines(
1727 x_return_status => x_return_status,
1728 x_msg_count => x_msg_count ,
1729 x_msg_data => x_msg_data ,
1730 px_sl_line_tbl => l_sl_line_tbl);
1731
1732 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1733 RAISE FND_API.G_EXC_ERROR;
1734 END IF;
1735
1736 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1737 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1738 END IF;
1739
1740 --dbms_output.put_line('p_sl_header_rec.shp_list_id = ' || p_sl_header_rec.shp_list_id);
1741 --dbms_output.put_line('p_sl_header_rec.party_id = ' || p_sl_header_rec.party_id);
1742 --dbms_output.put_line('p_sl_header_rec.cust_account_id = ' || p_sl_header_rec.cust_account_id);
1743 --dbms_output.put_line('p_sl_header_rec.shopping_list_name = ' || p_sl_header_rec.shopping_list_name);
1744 --dbms_output.put_line('p_sl_header_rec.description = ' || p_sl_header_rec.description);
1745 --dbms_output.put_line('p_sl_header_rec.org_id = ' || p_sl_header_rec.org_id);
1746
1747
1748 --dbms_output.put_line('l_sl_line_tbl.count = ' || l_sl_line_tbl.count);
1749 /*
1750 for k IN 1..l_sl_line_tbl.count loop
1751 --dbms_output.put_line('l_sl_line_tbl(' || k || ').inventory_item_id = ' || l_sl_line_tbl(k).inventory_item_id);
1752 --dbms_output.put_line('l_sl_line_tbl(' || k || ').organization_id = ' || l_sl_line_tbl(k).organization_id);
1753 --dbms_output.put_line('l_sl_line_tbl(' || k || ').uom_code = ' || l_sl_line_tbl(k).uom_code);
1754 --dbms_output.put_line('l_sl_line_tbl(' || k || ').quantity = ' || l_sl_line_tbl(k).quantity);
1755 --dbms_output.put_line('l_sl_line_tbl(' || k || ').item_type_code = ' || l_sl_line_tbl(k).item_type_code);
1756 --dbms_output.put_line('l_sl_line_tbl(' || k || ').org_id = ' || l_sl_line_tbl(k).org_id);
1757 end loop;
1758 */
1759 --dbms_output.put_line('l_sl_line_rel_tbl.count = ' || l_sl_line_rel_tbl.count);
1760 /*
1761 for l IN 1..l_sl_line_rel_tbl.count loop
1762 --dbms_output.put_line('l_sl_line_rel_tbl(' || l || ').line_index = ' || l_sl_line_rel_tbl(l).line_index);
1763 --dbms_output.put_line('l_sl_line_rel_tbl(' || l || ').related_line_index = ' || l_sl_line_rel_tbl(l).related_line_index);
1764 --dbms_output.put_line('l_sl_line_rel_tbl(' || l || ').relationship_type_code =' || l_sl_line_rel_tbl(l).relationship_type_code);
1765 end loop;
1766 */
1767 --dbms_output.put_line('Calling Save...');
1768
1769 BEGIN
1770 IBE_Shop_List_PVT.Save(
1771 p_api_version => p_api_version,
1772 p_init_msg_list => p_init_msg_list,
1773 p_commit => p_commit,
1774 x_return_status => x_return_status,
1775 x_msg_count => x_msg_count,
1776 x_msg_data => x_msg_data,
1777 p_combine_same_item => p_combine_same_item,
1778 p_sl_header_rec => l_sl_header_rec,
1779 p_sl_line_tbl => l_sl_line_tbl,
1780 p_sl_line_rel_tbl => l_sl_line_rel_tbl,
1781 x_sl_header_id => x_sl_header_id
1782 );
1783 --dbms_output.put_line('x_return_status = ' || x_return_status);
1784 --dbms_output.put_line('x_msg_count = ' || x_msg_count);
1785 --dbms_output.put_line('x_msg_data = ' || x_msg_data);
1786 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1787 RAISE FND_API.G_EXC_ERROR;
1788 END IF;
1789
1790 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1791 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1792 END IF;
1793 EXCEPTION
1794 WHEN NO_DATA_FOUND THEN
1795 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
1796 FND_MSG_PUB.add;
1797 RAISE FND_API.G_EXC_ERROR;
1798 END;
1799
1800 --dbms_output.put_line('After Save...');
1801
1802 -- End of API body.
1803 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1804 IBE_Util.Debug('IBE_Shop_List_PVT.Save_List_From_Items(-)');
1805 END IF;
1806 --dbms_output.put_line('IBE_Shop_List_PVT.Save_List_From_Items(-)');
1807
1808 -- Standard check of p_commit.
1809 IF FND_API.To_Boolean( p_commit ) THEN
1810 COMMIT WORK;
1811 END IF;
1812
1813 -- Standard call to get message count and IF count is 1, get message info.
1814 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1815 p_count => x_msg_count ,
1816 p_data => x_msg_data );
1817 EXCEPTION
1818 WHEN FND_API.G_EXC_ERROR THEN
1819 ROLLBACK TO Save_List_From_Items_PVT;
1820 x_return_status := FND_API.G_RET_STS_ERROR;
1821 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1822 p_count => x_msg_count,
1823 p_data => x_msg_data);
1824 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1825 ROLLBACK TO Save_List_From_Items_PVT;
1826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1827 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1828 p_count => x_msg_count,
1829 p_data => x_msg_data);
1830 WHEN OTHERS THEN
1831 ROLLBACK TO Save_List_From_Items_PVT;
1832 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1833
1834 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1835 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
1836 l_api_name);
1837 END IF;
1838
1839 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1840 p_count => x_msg_count,
1841 p_data => x_msg_data);
1842 END Save_List_From_Items;
1843
1844
1845 PROCEDURE Save_List_From_Quote(
1846 p_api_version IN NUMBER := 1 ,
1847 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
1848 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
1849 x_return_status OUT NOCOPY VARCHAR2 ,
1850 x_msg_count OUT NOCOPY NUMBER ,
1851 x_msg_data OUT NOCOPY VARCHAR2 ,
1852 p_quote_header_id IN NUMBER ,
1853 p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM ,
1854 p_minisite_id IN NUMBER := FND_API.G_MISS_NUM ,
1855 p_last_update_date IN DATE := FND_API.G_MISS_DATE,
1856 p_mode IN VARCHAR2 := 'MERGE' ,
1857 p_combine_same_item IN VARCHAR2 := FND_API.G_MISS_CHAR,
1858 p_sl_header_rec IN SL_Header_Rec_Type ,
1859 x_sl_header_id OUT NOCOPY NUMBER
1860 )
1861 IS
1862 L_API_NAME CONSTANT VARCHAR2(30) := 'Save_List_From_Quote';
1863 L_API_VERSION CONSTANT NUMBER := 1.0;
1864 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
1865 L_ORG_ID CONSTANT NUMBER := FND_Profile.Value('ORG_ID');
1866
1867 TYPE Csr_Type IS REF CURSOR;
1868 l_csr Csr_Type;
1869 l_csr_rel Csr_Type;
1870 l_sl_header_rec SL_Header_Rec_Type;
1871 l_sl_line_tbl SL_Line_Tbl_Type
1872 := IBE_Shop_List_PVT.G_MISS_SL_LINE_TBL;
1873 l_sl_line_rel_tbl SL_Line_Rel_Tbl_Type
1874 := IBE_Shop_List_PVT.G_MISS_SL_LINE_REL_TBL;
1875
1876 i NUMBER := 1; -- index for l_sl_line_tbl
1877 j NUMBER := 1; -- index for l_sl_line_rel_tbl
1878
1879 l_list_header_id_tbl jtf_number_table;
1880 l_obj_ver_num_tbl jtf_number_table;
1881
1882 l_quote_line_id NUMBER;
1883 l_inventory_item_id NUMBER;
1884 l_quantity NUMBER;
1885 l_uom_code VARCHAR2(30);
1886 l_organization_id NUMBER;
1887 l_shp_list_item_id NUMBER;
1888 l_config_header_id NUMBER := -2;
1889 l_config_revision_num NUMBER;
1890 l_complete_configuration_flag VARCHAR2(3);
1891 l_valid_configuration_flag VARCHAR2(3);
1892 l_item_type_code VARCHAR2(30);
1893 l_attribute_category VARCHAR2(30);
1894 l_attribute1 VARCHAR2(150);
1895 l_attribute2 VARCHAR2(150);
1896 l_attribute3 VARCHAR2(150);
1897 l_attribute4 VARCHAR2(150);
1898 l_attribute5 VARCHAR2(150);
1899 l_attribute6 VARCHAR2(150);
1900 l_attribute7 VARCHAR2(150);
1901 l_attribute8 VARCHAR2(150);
1902 l_attribute9 VARCHAR2(150);
1903 l_attribute10 VARCHAR2(150);
1904 l_attribute11 VARCHAR2(150);
1905 l_attribute12 VARCHAR2(150);
1906 l_attribute13 VARCHAR2(150);
1907 l_attribute14 VARCHAR2(150);
1908 l_attribute15 VARCHAR2(150);
1909 l_pricing_line_type_indicator VARCHAR2(3);
1910
1911 l_quote_line_id_tbl jtf_number_table := jtf_number_table();
1912 l_quote_line_index_tbl jtf_number_table := jtf_number_table();
1913
1914 l_related_quote_line_id NUMBER;
1915 l_relationship_type_code VARCHAR2(30);
1916
1917 l_PRG_configHdrId_tbl jtf_number_table := jtf_number_table();
1918 l_PRGchildren_lineId_tbl jtf_number_table := jtf_number_table();
1919 l_checkPRGChild NUMBER := 0;
1920
1921 BEGIN
1922 -- Standard Start of API savepoint
1923 SAVEPOINT Save_List_From_Quote_PVT;
1924
1925 -- Standard call to check for call compatibility.
1926 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
1927 p_api_version,
1928 L_API_NAME,
1929 G_PKG_NAME )
1930 THEN
1931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1932 END IF;
1933
1934 -- Initialize message list IF p_init_msg_list is set to TRUE.
1935 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1936 FND_MSG_PUB.initialize;
1937 END IF;
1938
1939 -- Initialize API return status to success
1940 x_return_status := FND_API.G_RET_STS_SUCCESS;
1941
1942 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
1943 IBE_Util.Debug('IBE_Shop_List_PVT.Save_List_From_Quote(+)');
1944 END IF;
1945 -- API body
1946
1947
1948 -- User Authentication
1949 IBE_Quote_Misc_pvt.Validate_User_Update
1950 ( p_init_msg_list => FND_API.G_TRUE
1951 ,p_quote_header_id => p_quote_header_id
1952 ,p_party_id => p_sl_header_rec.party_id
1953 ,p_cust_account_id => p_sl_header_rec.cust_account_id
1954 ,p_quote_retrieval_number => p_quote_retrieval_number
1955 ,p_validate_user => FND_API.G_TRUE
1956 ,x_return_status => x_return_status
1957 ,x_msg_count => x_msg_count
1958 ,x_msg_data => x_msg_data
1959 );
1960 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1961 RAISE FND_API.G_EXC_ERROR;
1962 END IF;
1963
1964 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1965 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1966 END IF;
1967
1968 -- IF p_sl_header_rec.shp_list_id is not null, i.e. mode will be either 'ADDTO' or 'REPLACE'.
1969 -- In this case we just take leave header information as it is, but is p_sl_header_rec.shp_list_id
1970 -- is null, we create new line for shopping list header.
1971 l_sl_header_rec := p_sl_header_rec;
1972
1973 IF (p_sl_header_rec.shp_list_id <> FND_API.G_MISS_NUM) THEN
1974 IF (p_mode = 'REPLACE') THEN
1975 -- create jtf_number_table of list id and obj. ver num to pass to Delete_All_Lines
1976 l_list_header_id_tbl := jtf_number_table(l_sl_header_rec.shp_list_id);
1977 l_obj_ver_num_tbl := jtf_number_table(l_sl_header_rec.object_version_number);
1978
1979 IBE_Shop_List_PVT.Delete_All_Lines(
1980 p_api_version => p_api_version,
1981 p_init_msg_list => p_init_msg_list,
1982 p_commit => p_commit,
1983 x_return_status => x_return_status,
1984 x_msg_count => x_msg_count,
1985 x_msg_data => x_msg_data,
1986 p_shop_list_ids => l_list_header_id_tbl,
1987 p_obj_ver_numbers => l_obj_ver_num_tbl);
1988
1989 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
1990 RAISE FND_API.G_EXC_ERROR;
1991 END IF;
1992 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1994 END IF;
1995
1996 -- Delete_All_Lines updates object_version_number of shopping list
1997 -- header, therefore IF we pass p_object_version_number IN save THEN
1998 -- it will fail as it has been incremented by one. Set it to
1999 -- FND_API.G_MISS_NUM so that save() will not fail.
2000 l_sl_header_rec.object_version_number := FND_API.G_MISS_NUM;
2001 END IF;
2002 END IF;
2003
2004 --commented by makulkar
2005 /*
2006 SELECT DECODE(l_sl_header_rec.attribute_category, FND_API.G_MISS_CHAR, attribute_category, l_sl_header_rec.attribute_CATEGORY),
2007 DECODE(l_sl_header_rec.attribute1, FND_API.G_MISS_CHAR, attribute1, l_sl_header_rec.attribute1),
2008 DECODE(l_sl_header_rec.attribute2, FND_API.G_MISS_CHAR, attribute2, l_sl_header_rec.attribute2),
2009 DECODE(l_sl_header_rec.attribute3, FND_API.G_MISS_CHAR, attribute3, l_sl_header_rec.attribute3),
2010 DECODE(l_sl_header_rec.attribute4, FND_API.G_MISS_CHAR, attribute4, l_sl_header_rec.attribute4),
2011 DECODE(l_sl_header_rec.attribute5, FND_API.G_MISS_CHAR, attribute5, l_sl_header_rec.attribute5),
2012 DECODE(l_sl_header_rec.attribute6, FND_API.G_MISS_CHAR, attribute6, l_sl_header_rec.attribute6),
2013 DECODE(l_sl_header_rec.attribute7, FND_API.G_MISS_CHAR, attribute7, l_sl_header_rec.attribute7),
2014 DECODE(l_sl_header_rec.attribute8, FND_API.G_MISS_CHAR, attribute8, l_sl_header_rec.attribute8),
2015 DECODE(l_sl_header_rec.attribute9, FND_API.G_MISS_CHAR, attribute9, l_sl_header_rec.attribute9),
2016 DECODE(l_sl_header_rec.attribute10, FND_API.G_MISS_CHAR, attribute10, l_sl_header_rec.attribute10),
2017 DECODE(l_sl_header_rec.attribute11, FND_API.G_MISS_CHAR, attribute11, l_sl_header_rec.attribute11),
2018 DECODE(l_sl_header_rec.attribute12, FND_API.G_MISS_CHAR, attribute12, l_sl_header_rec.attribute12),
2019 DECODE(l_sl_header_rec.attribute13, FND_API.G_MISS_CHAR, attribute13, l_sl_header_rec.attribute13),
2020 DECODE(l_sl_header_rec.attribute14, FND_API.G_MISS_CHAR, attribute14, l_sl_header_rec.attribute14),
2021 DECODE(l_sl_header_rec.attribute15, FND_API.G_MISS_CHAR, attribute15, l_sl_header_rec.attribute15)
2022 INTO l_sl_header_rec.attribute_category,
2023 l_sl_header_rec.attribute1,
2024 l_sl_header_rec.attribute2,
2025 l_sl_header_rec.attribute3,
2026 l_sl_header_rec.attribute4,
2027 l_sl_header_rec.attribute5,
2028 l_sl_header_rec.attribute6,
2029 l_sl_header_rec.attribute7,
2030 l_sl_header_rec.attribute8,
2031 l_sl_header_rec.attribute9,
2032 l_sl_header_rec.attribute10,
2033 l_sl_header_rec.attribute11,
2034 l_sl_header_rec.attribute12,
2035 l_sl_header_rec.attribute13,
2036 l_sl_header_rec.attribute14,
2037 l_sl_header_rec.attribute15
2038 FROM aso_quote_headers
2039 WHERE quote_header_id = p_quote_header_id;
2040 */
2041 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2042 IBE_Util.Debug('IBE_Shop_List_PVT pl1-' || p_quote_header_id);
2043 END IF;
2044
2045 OPEN l_csr FOR SELECT AQL.quote_line_id ,
2046 AQL.inventory_item_id ,
2047 AQL.quantity ,
2048 AQL.uom_code ,
2049 AQL.organization_id ,
2050 AQL.item_type_code ,
2051 AQLD.config_header_id ,
2052 AQLD.config_revision_num ,
2053 AQLD.complete_configuration_flag,
2054 AQLD.valid_configuration_flag ,
2055 AQL.attribute_category ,
2056 AQL.attribute1 ,
2057 AQL.attribute2 ,
2058 AQL.attribute3 ,
2059 AQL.attribute4 ,
2060 AQL.attribute5 ,
2061 AQL.attribute6 ,
2062 AQL.attribute7 ,
2063 AQL.attribute8 ,
2064 AQL.attribute9 ,
2065 AQL.attribute10 ,
2066 AQL.attribute11 ,
2067 AQL.attribute12 ,
2068 AQL.attribute13 ,
2069 AQL.attribute14 ,
2070 AQL.attribute15 ,
2071 AQL.pricing_line_type_indicator
2072 FROM aso_quote_lines AQL,
2073 aso_quote_line_details AQLD
2074 WHERE AQL.quote_header_id = p_quote_header_id
2075 AND AQL.quote_line_id = AQLD.quote_line_id(+)
2076 ORDER BY AQL.quote_line_id;
2077
2078
2079 LOOP
2080 FETCH l_csr INTO l_quote_line_id ,
2081 l_inventory_item_id ,
2082 l_quantity ,
2083 l_uom_code ,
2084 l_organization_id ,
2085 l_item_type_code ,
2086 l_config_header_id ,
2087 l_config_revision_num ,
2088 l_complete_configuration_flag,
2089 l_valid_configuration_flag ,
2090 l_attribute_category ,
2091 l_attribute1 ,
2092 l_attribute2 ,
2093 l_attribute3 ,
2094 l_attribute4 ,
2095 l_attribute5 ,
2096 l_attribute6 ,
2097 l_attribute7 ,
2098 l_attribute8 ,
2099 l_attribute9 ,
2100 l_attribute10 ,
2101 l_attribute11 ,
2102 l_attribute12 ,
2103 l_attribute13 ,
2104 l_attribute14 ,
2105 l_attribute15 ,
2106 l_pricing_line_type_indicator;
2107 EXIT WHEN l_csr%NOTFOUND;
2108
2109
2110 -- added 12/24/03: PRG shop list
2111 if (l_pricing_line_type_indicator = 'F') then
2112 l_PRG_configHdrId_tbl.EXTEND;
2113 l_PRG_configHdrId_tbl(l_PRG_configHdrId_tbl.LAST) := l_config_header_id;
2114
2115 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2116 IBE_Util.Debug('IBE_Shop_List_PVT: FreeLine! l_PRG_configHdrId=' || l_config_header_id);
2117 end if;
2118 end if;
2119
2120 -- check to see if the config child's parent is a PRG
2121 l_checkPRGChild := Find_Index(l_config_header_id, l_PRG_configHdrId_tbl);
2122 if (l_checkPRGChild <> 0) then
2123 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2124 IBE_Util.Debug('IBE_Shop_List_PVT: l_config_header_id is that of the PRG MDL!');
2125 end if;
2126 l_PRGchildren_lineId_tbl.EXTEND;
2127 l_PRGchildren_lineId_tbl(l_PRGchildren_lineId_tbl.LAST) := l_quote_line_Id;
2128 end if;
2129
2130 -- add 3/26/03: avoid saving SRV info and PRG lines
2131 if ((l_item_type_code <> 'SRV') and
2132 ((l_pricing_line_type_indicator is null) or (l_pricing_line_type_indicator <> 'F')) and
2133 ((l_config_header_id is null) or (l_checkPRGChild = 0)) ) then
2134
2135 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2136 IBE_Util.Debug('IBE_Shop_List_PVT: !SRV and !PRG');
2137 end if;
2138
2139 l_sl_line_tbl(i).inventory_item_id := l_inventory_item_id;
2140 l_sl_line_tbl(i).quantity := l_quantity;
2141 l_sl_line_tbl(i).uom_code := l_uom_code;
2142 l_sl_line_tbl(i).organization_id := l_organization_id;
2143 l_sl_line_tbl(i).item_type_code := l_item_type_code;
2144 l_sl_line_tbl(i).config_header_id := l_config_header_id;
2145 l_sl_line_tbl(i).config_revision_num := l_config_revision_num;
2146 l_sl_line_tbl(i).complete_configuration_flag := l_complete_configuration_flag;
2147 l_sl_line_tbl(i).valid_configuration_flag := l_valid_configuration_flag;
2148 l_sl_line_tbl(i).org_id := L_ORG_ID;
2149
2150 --commented by makulkar
2151 /*
2152 l_sl_line_tbl(i).attribute_category := l_attribute_category;
2153 l_sl_line_tbl(i).attribute1 := l_attribute1;
2154 l_sl_line_tbl(i).attribute2 := l_attribute2;
2155 l_sl_line_tbl(i).attribute3 := l_attribute3;
2156 l_sl_line_tbl(i).attribute4 := l_attribute4;
2157 l_sl_line_tbl(i).attribute5 := l_attribute5;
2158 l_sl_line_tbl(i).attribute6 := l_attribute6;
2159 l_sl_line_tbl(i).attribute7 := l_attribute7;
2160 l_sl_line_tbl(i).attribute8 := l_attribute8;
2161 l_sl_line_tbl(i).attribute9 := l_attribute9;
2162 l_sl_line_tbl(i).attribute10 := l_attribute10;
2163 l_sl_line_tbl(i).attribute11 := l_attribute11;
2164 l_sl_line_tbl(i).attribute12 := l_attribute12;
2165 l_sl_line_tbl(i).attribute13 := l_attribute13;
2166 l_sl_line_tbl(i).attribute14 := l_attribute14;
2167 l_sl_line_tbl(i).attribute15 := l_attribute15;
2168 */
2169 l_quote_line_id_tbl.EXTEND;
2170 l_quote_line_index_tbl.EXTEND;
2171 l_quote_line_id_tbl(l_quote_line_id_tbl.LAST) := l_quote_line_id;
2172 l_quote_line_index_tbl(l_quote_line_index_tbl.LAST) := i;
2173
2174 i := i + 1;
2175
2176 end if; --if ((l_item_type_code <> 'SRV') and (l_pricing_line_type_indicator <> 'F'))
2177
2178 END LOOP;
2179 CLOSE l_csr;
2180
2181 -- get the related lines
2182 OPEN l_csr_rel FOR SELECT ALR.quote_line_id,
2183 ALR.related_quote_line_id,
2184 ALR.relationship_type_code
2185 FROM aso_line_relationships ALR,
2186 aso_quote_lines AQL
2187 WHERE ALR.quote_line_id = AQL.quote_line_id
2188 AND AQL.quote_header_id = p_quote_header_id;
2189
2190 LOOP
2191 FETCH l_csr_rel INTO l_quote_line_id ,
2192 l_related_quote_line_id,
2193 l_relationship_type_code;
2194 EXIT WHEN l_csr_rel%NOTFOUND;
2195
2196 l_checkPRGChild := Find_Index(l_quote_line_id, l_PRGchildren_lineId_tbl);
2197 -- 3/26/03: avoid saving SRV info
2198 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2199 IBE_Util.Debug('IBE_Shop_List_PVT: l_checkPRGChild='||l_checkPRGChild);
2200 end if;
2201
2202 if ((l_relationship_type_code <> 'SERVICE') and (l_checkPRGChild = 0)) then
2203
2204 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2205 IBE_Util.Debug('IBE_Shop_List_PVT: relationship -- !SRV and !PRG');
2206 end if;
2207
2208 l_sl_line_rel_tbl(j).line_index
2209 := Find_Index(l_quote_line_id, l_quote_line_id_tbl, l_quote_line_index_tbl);
2210 l_sl_line_rel_tbl(j).related_line_index
2211 := Find_Index(l_related_quote_line_id, l_quote_line_id_tbl, l_quote_line_index_tbl);
2212 l_sl_line_rel_tbl(j).relationship_type_code := l_relationship_type_code;
2213
2214 j := j + 1;
2215
2216 end if;
2217 END LOOP;
2218 CLOSE l_csr_rel;
2219
2220 --dbms_output.put_line('p_sl_header_rec.shp_list_id = ' || p_sl_header_rec.shp_list_id);
2221 --dbms_output.put_line('p_sl_header_rec.party_id = ' || p_sl_header_rec.party_id);
2222 --dbms_output.put_line('p_sl_header_rec.cust_account_id = ' || p_sl_header_rec.cust_account_id);
2223 --dbms_output.put_line('p_sl_header_rec.shopping_list_name = ' || p_sl_header_rec.shopping_list_name);
2224 --dbms_output.put_line('p_sl_header_rec.description = ' || p_sl_header_rec.description);
2225 --dbms_output.put_line('p_sl_header_rec.org_id = ' || p_sl_header_rec.org_id);
2226
2227 --dbms_output.put_line('l_sl_line_tbl.count = ' || l_sl_line_tbl.count);
2228 /*
2229 for k IN 1..l_sl_line_tbl.count loop
2230 --dbms_output.put_line('l_sl_line_tbl(' || k || ').inventory_item_id = ' || l_sl_line_tbl(k).inventory_item_id);
2231 --dbms_output.put_line('l_sl_line_tbl(' || k || ').organization_id = ' || l_sl_line_tbl(k).organization_id);
2232 --dbms_output.put_line('l_sl_line_tbl(' || k || ').uom_code = ' || l_sl_line_tbl(k).uom_code);
2233 --dbms_output.put_line('l_sl_line_tbl(' || k || ').quantity = ' || l_sl_line_tbl(k).quantity);
2234 --dbms_output.put_line('l_sl_line_tbl(' || k || ').item_type_code = ' || l_sl_line_tbl(k).item_type_code);
2235 --dbms_output.put_line('l_sl_line_tbl(' || k || ').org_id = ' || l_sl_line_tbl(k).org_id);
2236 end loop;
2237
2238 --dbms_output.put_line('l_sl_line_rel_tbl.count = ' || l_sl_line_rel_tbl.count);
2239 for l IN 1..l_sl_line_rel_tbl.count loop
2240 --dbms_output.put_line('l_sl_line_rel_tbl(' || l || ').line_index = ' || l_sl_line_rel_tbl(l).line_index);
2241 --dbms_output.put_line('l_sl_line_rel_tbl(' || l || ').related_line_index = ' || l_sl_line_rel_tbl(l).related_line_index);
2242 --dbms_output.put_line('l_sl_line_rel_tbl(' || l || ').relationship_type_code =' || l_sl_line_rel_tbl(l).relationship_type_code);
2243 end loop;
2244 */
2245 --dbms_output.put_line('Calling Save...');
2246
2247 Update_Config_Item_Lines(
2248 x_return_status => x_return_status,
2249 x_msg_count => x_msg_count ,
2250 x_msg_data => x_msg_data ,
2251 px_sl_line_tbl => l_sl_line_tbl);
2252
2253 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2254 RAISE FND_API.G_EXC_ERROR;
2255 END IF;
2256
2257 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2258 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2259 END IF;
2260
2261 BEGIN
2262
2263 IBE_Shop_List_PVT.Save(
2264 p_api_version => p_api_version,
2265 p_init_msg_list => p_init_msg_list,
2266 p_commit => p_commit,
2267 x_return_status => x_return_status,
2268 x_msg_count => x_msg_count,
2269 x_msg_data => x_msg_data,
2270 p_combine_same_item => p_combine_same_item,
2271 p_sl_header_rec => l_sl_header_rec,
2272 p_sl_line_tbl => l_sl_line_tbl,
2273 p_sl_line_rel_tbl => l_sl_line_rel_tbl,
2274 x_sl_header_id => x_sl_header_id
2275 );
2276
2277 --dbms_output.put_line('x_return_status = ' || x_return_status);
2278 --dbms_output.put_line('x_msg_count = ' || x_msg_count);
2279 --dbms_output.put_line('x_msg_data = ' || x_msg_data);
2280
2281 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2282 RAISE FND_API.G_EXC_ERROR;
2283 END IF;
2284 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2286 END IF;
2287
2288 EXCEPTION
2289 WHEN NO_DATA_FOUND THEN
2290
2291 FND_MESSAGE.set_name('IBE', 'IBE_SL_UPDATE_TO_LIST_ERROR');
2292 FND_MSG_PUB.add;
2293 RAISE FND_API.G_EXC_ERROR;
2294 END;
2295
2296 --dbms_output.put_line('After Save...');
2297
2298 -- End of API body.
2299 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2300 IBE_Util.Debug('IBE_Shop_List_PVT.Save_List_From_Quote(-)');
2301 END IF;
2302
2303 -- Standard check of p_commit.
2304 IF FND_API.To_Boolean( p_commit ) THEN
2305 COMMIT WORK;
2306 END IF;
2307
2308 -- Standard call to get message count and IF count is 1, get message info.
2309 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2310 p_count => x_msg_count ,
2311 p_data => x_msg_data );
2312
2313 EXCEPTION
2314 WHEN FND_API.G_EXC_ERROR THEN
2315 ROLLBACK TO Save_List_From_Quote_PVT;
2316 x_return_status := FND_API.G_RET_STS_ERROR;
2317 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2318 p_count => x_msg_count,
2319 p_data => x_msg_data);
2320 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2321 ROLLBACK TO Save_List_From_Quote_PVT;
2322 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2323 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2324 p_count => x_msg_count,
2325 p_data => x_msg_data);
2326 WHEN OTHERS THEN
2327 ROLLBACK TO Save_List_From_Quote_PVT;
2328 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2329
2330 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2331 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
2332 l_api_name);
2333 END IF;
2334
2335 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2336 p_count => x_msg_count,
2337 p_data => x_msg_data);
2338 END Save_List_From_Quote;
2339
2340 PROCEDURE Set_Qte_Lines_From_List_Lines(
2341 p_list_line_id_tbl IN jtf_number_table ,
2342 p_quote_header_id IN NUMBER ,
2343 p_price_list_id IN NUMBER ,
2344 p_currency_code IN VARCHAR2 ,
2345 p_combine_same_item IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2346 p_minisite_id IN NUMBER := FND_API.G_MISS_CHAR ,
2347 x_qte_line_tbl OUT NOCOPY ASO_Quote_Pub.Qte_Line_Tbl_Type ,
2348 x_qte_line_detail_tbl OUT NOCOPY ASO_Quote_Pub.Qte_Line_Dtl_Tbl_Type,
2349 x_list_line_id_map_tbl OUT NOCOPY jtf_number_table ,
2350 x_qte_line_index_tbl OUT NOCOPY jtf_number_table ,
2351 x_ql_line_codes OUT NOCOPY jtf_number_table ,
2352 x_contMDL OUT NOCOPY VARCHAR2
2353 )
2354 IS
2355 TYPE Csr_Type IS REF CURSOR;
2356 l_csr Csr_Type;
2357 l_shp_list_item_id NUMBER;
2358 l_inventory_item_id NUMBER;
2359 l_organization_id NUMBER;
2360 l_uom_code VARCHAR2(3);
2361 l_quantity NUMBER;
2362 l_item_type_code VARCHAR2(30);
2363 l_config_header_id NUMBER;
2364 l_config_revision_num NUMBER;
2365 l_complete_configuration_flag VARCHAR2(3);
2366 l_valid_configuration_flag VARCHAR2(3);
2367 l_relationship_type_code VARCHAR2(30);
2368 l_attribute_category VARCHAR2(30);
2369 l_attribute1 VARCHAR2(150);
2370 l_attribute2 VARCHAR2(150);
2371 l_attribute3 VARCHAR2(150);
2372 l_attribute4 VARCHAR2(150);
2373 l_attribute5 VARCHAR2(150);
2374 l_attribute6 VARCHAR2(150);
2375 l_attribute7 VARCHAR2(150);
2376 l_attribute8 VARCHAR2(150);
2377 l_attribute9 VARCHAR2(150);
2378 l_attribute10 VARCHAR2(150);
2379 l_attribute11 VARCHAR2(150);
2380 l_attribute12 VARCHAR2(150);
2381 l_attribute13 VARCHAR2(150);
2382 l_attribute14 VARCHAR2(150);
2383 l_attribute15 VARCHAR2(150);
2384
2385 l_quote_line_id NUMBER;
2386 l_list_line_id_tbl jtf_number_table := jtf_number_table();
2387 i PLS_INTEGER := 1; -- index for x_qte_line_tbl
2388 j PLS_INTEGER := 1; -- index for x_qte_line_detail_tbl
2389 l_qte_line_tbl_index PLS_INTEGER;
2390 l_line_id NUMBER;
2391 l_component_code VARCHAR2(1000);
2392
2393 BEGIN
2394 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2395 IBE_Util.Debug('Set_Qte_Lines_From_List_Lines(+)...');
2396 END IF;
2397 x_list_line_id_map_tbl := jtf_number_table();
2398 x_qte_line_index_tbl := jtf_number_table();
2399
2400 -- add 3/26/03: to be used for ibe_quote_save_pvt.save api
2401 x_ql_line_codes := jtf_number_table();
2402
2403 -- added on 5/30/03: SBM -- for checking to see if the cart has a MDL item
2404 x_contMDL := 'N';
2405 FOR k IN 1..p_list_line_id_tbl.COUNT LOOP
2406 SELECT shp_list_item_id,
2407 inventory_item_id,
2408 quantity,
2409 uom_code,
2410 organization_id,
2411 config_header_id,
2412 config_revision_num,
2413 complete_configuration_flag,
2414 valid_configuration_flag,
2415 item_type_code,
2416 attribute_category,
2417 attribute1,
2418 attribute2,
2419 attribute3,
2420 attribute4,
2421 attribute5,
2422 attribute6,
2423 attribute7,
2424 attribute8,
2425 attribute9,
2426 attribute10,
2427 attribute11,
2428 attribute12,
2429 attribute13,
2430 attribute14,
2431 attribute15
2432 INTO l_shp_list_item_id,
2433 l_inventory_item_id,
2434 l_quantity,
2435 l_uom_code,
2436 l_organization_id,
2437 l_config_header_id,
2438 l_config_revision_num,
2439 l_complete_configuration_flag,
2440 l_valid_configuration_flag,
2441 l_item_type_code,
2442 l_attribute_category,
2443 l_attribute1,
2444 l_attribute2,
2445 l_attribute3,
2446 l_attribute4,
2447 l_attribute5,
2448 l_attribute6,
2449 l_attribute7,
2450 l_attribute8,
2451 l_attribute9,
2452 l_attribute10,
2453 l_attribute11,
2454 l_attribute12,
2455 l_attribute13,
2456 l_attribute14,
2457 l_attribute15
2458 FROM ibe_sh_shp_list_items
2459 WHERE shp_list_item_id = p_list_line_id_tbl(k);
2460
2461 -- added 3/26/03: avoid saving SRV info
2462 if (l_item_type_code <> 'SRV') then
2463
2464 IF p_combine_same_item = 'Y' AND l_item_type_code = 'STD' THEN
2465 l_qte_line_tbl_index := Find_Same_Item_In_qte_Line_Tbl(
2466 p_qte_line_tbl => x_qte_line_tbl ,
2467 p_inventory_item_id => l_inventory_item_id,
2468 p_uom_code => l_uom_code);
2469
2470 /*
2471 * if l_qte_line_tbl_index <> 0, there is already a line in x_qte_line_tbl
2472 * for same standard item. Don't add a new line and just update the quantity.
2473 */
2474 IF l_qte_line_tbl_index <> 0 THEN
2475 x_qte_line_tbl(l_qte_line_tbl_index).quantity
2476 := x_qte_line_tbl(l_qte_line_tbl_index).quantity + l_quantity;
2477 ELSE
2478 x_qte_line_tbl(i).quote_header_id := p_quote_header_id;
2479 x_qte_line_tbl(i).inventory_item_id := l_inventory_item_id;
2480 x_qte_line_tbl(i).organization_id := l_organization_id;
2481 x_qte_line_tbl(i).uom_code := l_uom_code;
2482 x_qte_line_tbl(i).item_type_code := l_item_type_code;
2483 x_qte_line_tbl(i).quantity := l_quantity;
2484 x_qte_line_tbl(i).minisite_id := p_minisite_id;
2485
2486 /*
2487 * find same item IN aso_quote_lines view where item_type_code is 'STD',
2488 * and inventory_item_id, organization_id, and uom_code matches with this line
2489 */
2490 BEGIN
2491 SELECT quote_line_id,
2492 quantity
2493 INTO l_quote_line_id,
2494 l_quantity
2495 FROM aso_quote_lines
2496 WHERE quote_header_id = p_quote_header_id
2497 AND organization_id = l_organization_id
2498 AND item_type_code = 'STD'
2499 AND inventory_item_id = l_inventory_item_id
2500 AND uom_code = l_uom_code
2501 AND currency_code = p_currency_code;
2502
2503 x_qte_line_tbl(i).operation_code := 'UPDATE';
2504 x_qte_line_tbl(i).quote_line_id := l_quote_line_id;
2505 x_qte_line_tbl(i).quantity := x_qte_line_tbl(i).quantity + l_quantity;
2506 EXCEPTION
2507 /*
2508 * We get to NO_DATA_FOUND block when there is no line in
2509 * x_qte_line_tbl nor in aso_quote_lines table for the same item.
2510 */
2511 WHEN NO_DATA_FOUND THEN
2512 x_qte_line_tbl(i).operation_code := 'CREATE';
2513 --commented by makulkar
2514 /*
2515 x_qte_line_tbl(i).attribute_category := l_attribute_category;
2516 x_qte_line_tbl(i).attribute1 := l_attribute1;
2517 x_qte_line_tbl(i).attribute2 := l_attribute2;
2518 x_qte_line_tbl(i).attribute3 := l_attribute3;
2519 x_qte_line_tbl(i).attribute4 := l_attribute4;
2520 x_qte_line_tbl(i).attribute5 := l_attribute5;
2521 x_qte_line_tbl(i).attribute6 := l_attribute6;
2522 x_qte_line_tbl(i).attribute7 := l_attribute7;
2523 x_qte_line_tbl(i).attribute8 := l_attribute8;
2524 x_qte_line_tbl(i).attribute9 := l_attribute9;
2525 x_qte_line_tbl(i).attribute10 := l_attribute10;
2526 x_qte_line_tbl(i).attribute11 := l_attribute11;
2527 x_qte_line_tbl(i).attribute12 := l_attribute12;
2528 x_qte_line_tbl(i).attribute13 := l_attribute13;
2529 x_qte_line_tbl(i).attribute14 := l_attribute14;
2530 x_qte_line_tbl(i).attribute15 := l_attribute15;
2531 */
2532 END;
2533
2534 x_list_line_id_map_tbl.EXTEND;
2535 x_qte_line_index_tbl.EXTEND;
2536 x_list_line_id_map_tbl(x_list_line_id_map_tbl.LAST)
2537 := p_list_line_id_tbl(k);
2538 x_qte_line_index_tbl(x_qte_line_index_tbl.LAST) := i;
2539
2540 i := i + 1;
2541 END IF;
2542 /*
2543 * p_combine_same_item = 'N' OR l_item_type_code <> 'STD',
2544 * which means that we are safe to add a new line
2545 */
2546 ELSE
2547 -- added on 5/21/03: SBM -- don't populate Configuration Children lines
2548 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2549 IBE_Util.Debug('Else not STD and combine items');
2550 END IF;
2551
2552 if ((l_config_header_id is null) or (l_item_type_code = 'MDL') ) then
2553
2554 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2555 IBE_Util.Debug('if non-config or MDL: j='||j);
2556 END IF;
2557
2558 x_qte_line_tbl(i).operation_code := 'CREATE';
2559 x_qte_line_tbl(i).quote_header_id := p_quote_header_id;
2560 x_qte_line_tbl(i).inventory_item_id := l_inventory_item_id;
2561 x_qte_line_tbl(i).organization_id := l_organization_id;
2562 x_qte_line_tbl(i).uom_code := l_uom_code;
2563 x_qte_line_tbl(i).item_type_code := l_item_type_code;
2564 x_qte_line_tbl(i).quantity := l_quantity;
2565 x_qte_line_tbl(i).minisite_id := p_minisite_id;
2566
2567 --commented by makulkar
2568 /*
2569 x_qte_line_tbl(i).attribute_category := l_attribute_category;
2570 x_qte_line_tbl(i).attribute1 := l_attribute1;
2571 x_qte_line_tbl(i).attribute2 := l_attribute2;
2572 x_qte_line_tbl(i).attribute3 := l_attribute3;
2573 x_qte_line_tbl(i).attribute4 := l_attribute4;
2574 x_qte_line_tbl(i).attribute5 := l_attribute5;
2575 x_qte_line_tbl(i).attribute6 := l_attribute6;
2576 x_qte_line_tbl(i).attribute7 := l_attribute7;
2577 x_qte_line_tbl(i).attribute8 := l_attribute8;
2578 x_qte_line_tbl(i).attribute9 := l_attribute9;
2579 x_qte_line_tbl(i).attribute10 := l_attribute10;
2580 x_qte_line_tbl(i).attribute11 := l_attribute11;
2581 x_qte_line_tbl(i).attribute12 := l_attribute12;
2582 x_qte_line_tbl(i).attribute13 := l_attribute13;
2583 x_qte_line_tbl(i).attribute14 := l_attribute14;
2584 x_qte_line_tbl(i).attribute15 := l_attribute15;
2585 */
2586 IF l_config_header_id IS NOT NULL THEN
2587 IF l_item_type_code = 'MDL' THEN
2588 --x_qte_line_tbl(i).currency_code := p_currency_code;-- Bug fix 3378817
2589 x_contMDL := 'Y';
2590 END IF;
2591
2592 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2593 IBE_Util.Debug('for configuration items ,: j='||j ||'and i='||i);
2594 END IF;
2595 --x_qte_line_tbl(i).price_list_id := p_price_list_id;-- Bug fix 3378817
2596
2597 x_qte_line_detail_tbl(j).qte_line_index := i;
2598 x_qte_line_detail_tbl(j).config_header_id := l_config_header_id;
2599 x_qte_line_detail_tbl(j).config_revision_num := l_config_revision_num;
2600 x_qte_line_detail_tbl(j).complete_configuration_flag
2601 := l_complete_configuration_flag;
2602 x_qte_line_detail_tbl(j).valid_configuration_flag
2603 := l_valid_configuration_flag;
2604 x_qte_line_detail_tbl(j).operation_code := 'CREATE';
2605
2606 l_component_code := TO_CHAR(x_qte_line_tbl(i).inventory_item_id);
2607
2608 /* commented out on 5/21/03: SBM -- we won't be needing component code info
2609 OPEN l_csr FOR 'SELECT shp_list_item_id '||
2610 'FROM ibe_sh_shlitem_rels ' ||
2611 'START WITH related_shp_list_item_id = :1 '||
2612 'CONNECT BY related_shp_list_item_id = PRIOR shp_list_item_id'
2613 USING p_list_line_id_tbl(k);
2614 LOOP
2615 FETCH l_csr INTO l_line_id;
2616 EXIT WHEN l_csr%NOTFOUND;
2617 l_component_code := TO_CHAR(x_qte_line_tbl(Find_Index(l_line_id, x_list_line_id_map_tbl, x_qte_line_index_tbl)).inventory_item_id)
2618 || '-' || l_component_code;
2619 END LOOP;
2620
2621 CLOSE l_csr;
2622
2623 x_qte_line_detail_tbl(j).component_code := l_component_code;
2624 */
2625
2626 j := j + 1;
2627 END IF;
2628
2629 x_list_line_id_map_tbl.EXTEND;
2630 x_qte_line_index_tbl.EXTEND;
2631 x_list_line_id_map_tbl(x_list_line_id_map_tbl.LAST)
2632 := p_list_line_id_tbl(k);
2633 x_qte_line_index_tbl(x_qte_line_index_tbl.LAST) := i;
2634
2635 i := i + 1;
2636
2637 END IF; -- if <non-Config Items> or <MDL Items>
2638 END IF;
2639
2640 -- add 3/26/03: to be used for ibe_quote_save_pvt.save api
2641 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2642 IBE_Util.Debug('l_item_type_code='||l_item_type_code);
2643 END IF;
2644 x_ql_line_codes.extend;
2645 if (l_item_type_code = 'SVA') then
2646 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2647 IBE_Util.Debug('l_item_type_code=SVA!');
2648 END IF;
2649 x_ql_line_codes(x_ql_line_codes.LAST) := IBE_QUOTE_SAVE_PVT.SERVICEABLE_LINE_CODE;
2650
2651 elsif (l_item_type_code = 'STD') then
2652 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2653 IBE_Util.Debug('l_item_type_code=STD!');
2654 END IF;
2655 x_ql_line_codes(x_ql_line_codes.LAST) := IBE_QUOTE_SAVE_PVT.STANDARD_LINE_CODE;
2656 end if;
2657
2658 END IF; -- if (l_item_type_code <> 'SRV') then
2659 END LOOP;
2660
2661 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2662 IBE_Util.Debug('Set_Qte_Lines_From_List_Lines: x_contMDL='||x_contMDL);
2663 END IF;
2664
2665 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2666 IBE_Util.Debug('Set_Qte_Lines_From_List_Lines(-)...');
2667 END IF;
2668 END Set_Qte_Lines_From_List_Lines;
2669
2670
2671 PROCEDURE Save_Quote_From_List_Items(
2672 p_api_version IN NUMBER := 1 ,
2673 p_init_msg_list IN VARCHAR2 := FND_API.G_TRUE ,
2674 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
2675 x_return_status OUT NOCOPY VARCHAR2 ,
2676 x_msg_count OUT NOCOPY NUMBER ,
2677 x_msg_data OUT NOCOPY VARCHAR2 ,
2678 p_sl_line_ids IN jtf_number_table ,
2679 p_sl_line_ovns IN jtf_number_table := NULL ,
2680 p_quote_retrieval_number IN NUMBER := FND_API.G_MISS_NUM ,
2681 p_recipient_party_id IN NUMBER := FND_API.G_MISS_NUM ,
2682 p_recipient_cust_account_id IN NUMBER := FND_API.G_MISS_NUM ,
2683 p_minisite_id IN NUMBER := FND_API.G_MISS_NUM ,
2684 p_mode IN VARCHAR2 := 'MERGE' ,
2685 p_combine_same_item IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2686 p_control_rec IN ASO_Quote_Pub.control_rec_type ,
2687 p_q_header_rec IN ASO_Quote_Pub.qte_header_rec_type,
2688 p_password IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2689 p_email_address IN jtf_varchar2_table_2000 := NULL ,
2690 p_privilege_type IN jtf_varchar2_table_100 := NULL ,
2691 p_url IN VARCHAR2 ,
2692 p_comments IN VARCHAR2 ,
2693 p_promocode IN VARCHAR2 := FND_API.G_MISS_CHAR ,
2694 x_q_header_id OUT NOCOPY NUMBER
2695 )
2696 IS
2697 L_API_NAME CONSTANT VARCHAR2(30) := 'Save_Quote_From_List_Items';
2698 L_API_VERSION CONSTANT NUMBER := 1.0;
2699 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
2700
2701 l_list_line_id_tbl jtf_number_table;
2702 l_list_line_id_map_tbl jtf_number_table;
2703 l_qte_line_index_tbl jtf_number_table;
2704 l_qte_line_tbl ASO_Quote_Pub.Qte_Line_Tbl_Type;
2705 l_qte_line_detail_tbl ASO_Quote_Pub.Qte_Line_Dtl_Tbl_Type;
2706 l_qte_line_relation_tbl ASO_Quote_Pub.Line_Rltship_Tbl_Type;
2707 l_list_line_relation_tbl IBE_Shop_List_PVT.SL_Line_Rel_Tbl_Type;
2708 lx_quote_header_id NUMBER;
2709 lx_last_update_date DATE;
2710
2711 -- add 3/26/03: to be used for ibe_quote_save_pvt.save api
2712 l_ql_line_codes jtf_number_table;
2713 lx_Qte_Line_Tbl ASO_Quote_Pub.Qte_Line_Tbl_Type;
2714
2715 -- added on 5/21/03: SBM --
2716 l_qte_line_detail_indx number;
2717 l_qte_line_id number;
2718 l_control_rec ASO_Quote_Pub.control_rec_type;
2719 lx_contMDL varchar2(1);
2720
2721 --Maithili- added for Offer code integration.
2722 l_Hd_Price_Attributes_Tbl ASO_Quote_Pub.Price_Attributes_Tbl_Type;
2723
2724 Cursor c_get_promo_details(p_promocode VARCHAR2, p_currency_code VARCHAR2) is
2725 SELECT list_header_id
2726 FROM qp_list_headers_vl
2727 WHERE NVL(start_date_active, SYSDATE) <= SYSDATE
2728 AND NVL(end_date_active+1, SYSDATE) >= SYSDATE
2729 AND list_type_code = 'PRO'
2730 AND active_flag = 'Y'
2731 AND automatic_flag = 'Y'
2732 AND ask_for_flag = 'Y'
2733 AND currency_code = p_currency_code
2734 AND name = p_promocode;
2735
2736 l_pricing_attribute1 varchar2(240);
2737 BEGIN
2738 -- Standard Start of API savepoint
2739 SAVEPOINT Save_Quote_From_List_Items_PVT;
2740
2741 -- Standard call to check for call compatibility.
2742 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
2743 p_api_version,
2744 L_API_NAME,
2745 G_PKG_NAME )
2746 THEN
2747 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2748 END IF;
2749
2750 -- Initialize message list IF p_init_msg_list is set to TRUE.
2751 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2752 FND_MSG_PUB.initialize;
2753 END IF;
2754
2755 -- Initialize API return status to success
2756 x_return_status := FND_API.G_RET_STS_SUCCESS;
2757
2758 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2759 IBE_Util.Debug('IBE_Shop_List_PVT.Save_Quote_From_List_Items(+)');
2760 END IF;
2761 --dbms_output.put_line('IBE_Shop_List_PVT.Save_Quote_From_List_Items(+)');
2762 -- API body
2763
2764 -- IF p_q_header_rec.quote_header_id is not null, i.e. mode will be either 'ADDTO' or 'REPLACE'.
2765 -- IN this case we just take leave header information as it is, but IF p_q_header_rec.quote_header_id
2766 -- is null, we create new line for quote header.
2767
2768 IF (p_q_header_rec.quote_header_id <> FND_API.G_MISS_NUM) THEN
2769 IF (p_mode = 'REPLACE') THEN
2770 --dbms_output.put_line('calling IBE_Shop_List_PVT.DeleteAllLines()..');
2771 IBE_Quote_Save_pvt.DeleteAllLines(
2772 p_api_version_number => p_api_version,
2773 p_init_msg_list => FND_API.G_TRUE,
2774 p_commit => FND_API.G_FALSE,
2775 x_return_status => x_return_status,
2776 x_msg_count => x_msg_count,
2777 x_msg_data => x_msg_data,
2778 p_quote_header_id => p_q_header_rec.quote_header_id,
2779 p_last_update_date => p_q_header_rec.last_update_date,
2780 p_sharee_number => p_quote_retrieval_number,
2781 x_quote_header_id => lx_quote_header_id,
2782 x_last_update_date => lx_last_update_date);
2783
2784 --dbms_output.put_line('back from IBE_Shop_List_PVT.DeleteAllLines()..');
2785 --dbms_output.put_line('x_return_status = ' || x_return_status);
2786 --dbms_output.put_line('x_msg_count = ' || x_msg_count);
2787 --dbms_output.put_line('x_msg_data = ' || x_msg_data);
2788 /*
2789 FOR I IN 1..x_msg_count LOOP
2790 --dbms_OUTPUT.Put_Line(FND_MSG_PUB.Get(p_encoded => FND_API.g_false));
2791 END LOOP;
2792 */
2793 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2794 RAISE FND_API.G_EXC_ERROR;
2795 END IF;
2796
2797 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2798 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2799 END IF;
2800 END IF;
2801 END IF;
2802
2803 --dbms_output.put_line('p_q_header_rec.quote_header_id = ' || p_q_header_rec.quote_header_id);
2804 --dbms_output.put_line('p_q_header_rec.total_list_price = ' || p_q_header_rec.total_list_price);
2805 --dbms_output.put_line('p_q_header_rec.quote_source_code = ' || p_q_header_rec.quote_source_code);
2806 --dbms_output.put_line('p_q_header_rec.currency_code = ' || p_q_header_rec.currency_code);
2807 --dbms_output.put_line('p_q_header_rec.party_id = ' || p_q_header_rec.party_id);
2808 --dbms_output.put_line('p_q_header_rec.cust_account_id = ' || p_q_header_rec.cust_account_id);
2809 --dbms_output.put_line('p_q_header_rec.quote_name = ' || p_q_header_rec.quote_name);
2810 --dbms_output.put_line('p_q_header_rec.order_type_id = ' || p_q_header_rec.order_type_id);
2811 --dbms_output.put_line('p_q_header_rec.price_list_id = ' || p_q_header_rec.price_list_id);
2812 --dbms_output.put_line('p_q_header_rec.quote_category_code = ' || p_q_header_rec.quote_category_code);
2813 --dbms_output.put_line('calling Set_Qte_Lines_From_List_Lines()..');
2814
2815 /*
2816 * Call Include_Related_Lines() to include all the related lines, i.e.,
2817 * all the children lines of configurable items.
2818 */
2819
2820 /* commented out on 5/21/03: SBM
2821 Include_Related_Lines(
2822 p_qte_line_rel_tbl => FND_API.G_TRUE ,
2823 p_list_line_id_tbl => p_sl_line_ids ,
2824 x_list_line_id_tbl => l_list_line_id_tbl,
2825 x_qte_line_relation_tbl => l_qte_line_relation_tbl,
2826 x_list_line_relation_tbl => l_list_line_relation_tbl);
2827 */
2828
2829 Set_Qte_Lines_From_List_Lines(
2830 p_list_line_id_tbl => p_sl_line_ids ,
2831 p_quote_header_id => p_q_header_rec.quote_header_id,
2832 p_price_list_id => p_q_header_rec.price_list_id ,
2833 p_currency_code => p_q_header_rec.currency_code ,
2834 p_combine_same_item => p_combine_same_item ,
2835 p_minisite_id => p_minisite_id ,
2836 x_qte_line_tbl => l_qte_line_tbl ,
2837 x_qte_line_detail_tbl => l_qte_line_detail_tbl ,
2838 x_list_line_id_map_tbl => l_list_line_id_map_tbl ,
2839 x_qte_line_index_tbl => l_qte_line_index_tbl ,
2840 x_ql_line_codes => l_ql_line_codes ,
2841 x_contMDL => lx_contMDL);
2842
2843 if (l_ql_line_codes is not null) then
2844 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2845 IBE_Util.Debug('back from Set_Qte_Lines_From_List_Lines -- l_ql_line_codes is not null!='||l_ql_line_codes(1));
2846 IBE_Util.Debug('back from Set_Qte_Lines_From_List_Lines -- lx_contMDL='||lx_contMDL);
2847 END IF;
2848 end if;
2849
2850 --dbms_output.put_line('back from Set_Qte_Lines_From_List_Lines()..');
2851 --dbms_output.put_line('l_qte_line_tbl.count = ' || l_qte_line_tbl.count);
2852 /*
2853 for i IN 1..l_qte_line_tbl.count loop
2854
2855 --dbms_output.put_line('l_qte_line_tbl(' || i || ').inventory_item_id = ' || l_qte_line_tbl(i).inventory_item_id);
2856 --dbms_output.put_line('l_qte_line_tbl(' || i || ').uom_code = ' || l_qte_line_tbl(i).uom_code);
2857 --dbms_output.put_line('l_qte_line_tbl(' || i || ').quantity = ' || l_qte_line_tbl(i).quantity);
2858 --dbms_output.put_line('l_qte_line_tbl(' || i || ').organization_id = ' || l_qte_line_tbl(i).organization_id);
2859 --dbms_output.put_line('l_qte_line_tbl(' || i || ').item_type_code = ' || l_qte_line_tbl(i).item_type_code);
2860 --dbms_output.put_line('l_qte_line_tbl(' || i || ').currency_code = ' || l_qte_line_tbl(i).currency_code);
2861
2862 end loop;
2863 */
2864 --dbms_output.put_line('l_qte_line_relation_tbl.count = ' || l_qte_line_relation_tbl.count);
2865
2866 --dbms_output.put_line('Calling IBE_Quote_Save_pvt.Save()...');
2867
2868 IBE_Quote_Misc_pvt.Update_Config_Item_Lines(
2869 x_return_status => x_return_status,
2870 x_msg_count => x_msg_count ,
2871 x_msg_data => x_msg_data ,
2872 px_qte_line_dtl_tbl => l_qte_line_detail_tbl);
2873
2874 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2875 RAISE FND_API.G_EXC_ERROR;
2876 END IF;
2877
2878 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2879 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2880 END IF;
2881
2882 -- added on 5/21/03: SBM
2883 -- if the lx_contMDL = Y, then there must be at least a MDL in the shop list
2884 -- 1) make the first transaction call (Save) not do a pricing call
2885 l_control_rec := p_control_rec;
2886 if (lx_contMDL = 'Y') then
2887 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2888 IBE_Util.Debug('Coming back from update_config_item_lines: lx_contMDL is Y');
2889 END IF;
2890 l_control_rec := aso_quote_pub.G_MISS_Control_Rec;
2891 end if;
2892
2893 --Maithili
2894 IF (p_promocode is not null and p_promocode <> FND_API.G_MISS_CHAR) THEN
2895 -- set header pricing attribute record with promo code and other stuff.
2896 OPEN c_get_promo_details(p_promocode,p_q_header_rec.currency_code);
2897 FETCH c_get_promo_details INTO l_pricing_attribute1;
2898 CLOSE c_get_promo_details;
2899
2900 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2901 IBE_Util.Debug('promocode = '||p_promocode||',currency code = '||p_q_header_rec.currency_code||'and pricing attribute1='||l_pricing_attribute1);
2902 END IF;
2903
2904 IF l_pricing_attribute1 is not null THEN
2905 l_Hd_Price_Attributes_Tbl(1).pricing_attribute1 := l_pricing_attribute1;
2906 l_Hd_Price_Attributes_Tbl(1).flex_title := 'QP_ATTR_DEFNS_QUALIFIER';
2907 l_Hd_Price_Attributes_Tbl(1).pricing_context := 'MODLIST';
2908 l_Hd_Price_Attributes_Tbl(1).quote_header_id := p_q_header_rec.quote_header_id;
2909 l_Hd_Price_Attributes_Tbl(1).operation_code := 'CREATE';
2910 END IF;
2911
2912 END IF; --p_promocode not null
2913
2914 IBE_Quote_Save_pvt.AddItemsToCart(
2915 p_api_version_number => p_api_version,
2916 p_init_msg_list => FND_API.G_TRUE,
2917 p_commit => FND_API.G_FALSE,
2918 x_return_status => x_return_status,
2919 x_msg_count => x_msg_count,
2920 x_msg_data => x_msg_data,
2921 p_combineSameItem => p_combine_same_item,
2922 p_sharee_Number => p_quote_retrieval_number,
2923 p_sharee_party_id => p_recipient_party_id,
2924 p_sharee_cust_account_id => p_recipient_cust_account_id,
2925 p_minisite_id => p_minisite_id,
2926 p_Control_Rec => l_control_rec,
2927 p_Qte_Header_Rec => p_q_header_rec,
2928 p_hd_Price_Attributes_Tbl=> l_Hd_Price_Attributes_Tbl,
2929 p_Qte_Line_Tbl => l_qte_line_tbl,
2930 --p_Qte_Line_Dtl_Tbl => l_qte_line_detail_tbl,
2931 --p_line_rltship_tbl => l_qte_line_relation_tbl,
2932 p_ql_line_codes => l_ql_line_codes,
2933 x_quote_header_id => lx_quote_header_id,
2934 x_last_update_date => lx_last_update_date,
2935 x_Qte_Line_Tbl => lx_Qte_Line_Tbl);
2936
2937 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
2938 RAISE FND_API.G_EXC_ERROR;
2939 END IF;
2940
2941 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
2942 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2943 END IF;
2944
2945 --dbms_output.put_line('After Save() .. lx_quote_header_id = ' || lx_quote_header_id);
2946
2947 -- added on 5/21/03: SBM --
2948 -- if lx_contMDL = Y, then there must be at least a MDL in the shop list
2949 -- 1) get line det record from 1st index of line det Tbl
2950 if (lx_contMDL = 'Y') then
2951 for i in 1..l_qte_line_detail_tbl.count loop
2952 l_control_rec := aso_quote_pub.G_MISS_Control_Rec;
2953 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2954 IBE_Util.Debug('Begin ASO_CFG_PUB.get_config_details');
2955 END IF;
2956
2957 l_qte_line_detail_indx := l_qte_line_detail_tbl(i).qte_line_index;
2958 l_qte_line_detail_tbl(i).quote_line_id := lx_Qte_Line_Tbl(l_qte_line_detail_indx).quote_line_id;
2959 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2960 IBE_Util.Debug('l_qte_line_detail_indx='||l_qte_line_detail_indx);
2961 END IF;
2962
2963 -- make the Last transaction call do a pricing call
2964 if (i = l_qte_line_detail_tbl.count) then
2965 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2966 IBE_Util.Debug('This is the last item in the line details record -- making a pricing call');
2967 END IF;
2968 l_control_rec := p_control_rec;
2969 end if;
2970
2971 /*Call GET_CONFIG_DETAILS here*/
2972 if (lx_Qte_Line_Tbl(l_qte_line_detail_indx).item_type_code = 'MDL') then
2973
2974 aso_cfg_pub.get_config_details(P_Api_Version_Number => p_api_version,
2975 P_Init_Msg_List => p_init_msg_list,
2976 p_commit => p_commit ,
2977 p_control_rec => l_control_rec,
2978 p_config_rec => l_qte_line_detail_tbl(i),
2979 p_model_line_rec => lx_Qte_Line_Tbl(l_qte_line_detail_indx),
2980 p_config_hdr_id => l_qte_line_detail_tbl(i).config_header_id ,
2981 p_config_rev_nbr => l_qte_line_detail_tbl(i).config_revision_num,
2982 p_quote_header_id => lx_quote_header_id ,
2983
2984 x_return_status => x_return_status,
2985 x_msg_count => x_msg_count,
2986 x_msg_data => x_msg_data
2987 );
2988 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
2989 IBE_Util.Debug('End ASO_CFG_PUB.get_config_details');
2990 END IF;
2991
2992 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2993 RAISE FND_API.G_EXC_ERROR;
2994 END IF;
2995
2996 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2997 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2998 END IF;
2999
3000 end if;
3001
3002 end loop;
3003 end if; -- if contMDL = 'Y'
3004
3005 IF (p_email_address IS NOT NULL) THEN
3006 --dbms_output.put_line('Calling IBE_QUOTE_SAVESHARE_pvt.ShareQuote()...');
3007 IBE_QUOTE_SAVESHARE_pvt.ShareQuote(
3008 p_api_version_number => p_api_version,
3009 p_init_msg_list => FND_API.G_FALSE,
3010 p_commit => FND_API.G_FALSE,
3011 x_return_status => x_return_status,
3012 x_msg_count => x_msg_count,
3013 x_msg_data => x_msg_data,
3014 p_quote_header_id => lx_quote_header_id,
3015 p_url => p_url,
3016 p_sharee_email_address => p_email_address,
3017 p_sharee_privilege_type => p_privilege_type,
3018 p_comments => p_comments);
3019
3020 IF (x_return_status = FND_API.G_RET_STS_ERROR) THEN
3021 RAISE FND_API.G_EXC_ERROR;
3022 END IF;
3023 IF (x_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3025 END IF;
3026 END IF;
3027
3028 x_q_header_id := lx_quote_header_id;
3029 -- End of API body.
3030 IF (IBE_UTIL.G_DEBUGON = l_true) THEN
3031 IBE_Util.Debug('IBE_Shop_List_PVT.Save_Quote_From_List_Items(-)');
3032 END IF;
3033
3034 -- Standard check of p_commit.
3035 IF FND_API.To_Boolean( p_commit ) THEN
3036 COMMIT WORK;
3037 END IF;
3038
3039 -- Standard call to get message count and IF count is 1, get message info.
3040 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3041 p_count => x_msg_count ,
3042 p_data => x_msg_data );
3043
3044 EXCEPTION
3045 WHEN FND_API.G_EXC_ERROR THEN
3046 ROLLBACK TO Save_Quote_From_List_Items_PVT;
3047 x_return_status := FND_API.G_RET_STS_ERROR;
3048 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3049 p_count => x_msg_count,
3050 p_data => x_msg_data);
3051 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3052 ROLLBACK TO Save_Quote_From_List_Items_PVT;
3053 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3054 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3055 p_count => x_msg_count,
3056 p_data => x_msg_data);
3057 WHEN OTHERS THEN
3058 ROLLBACK TO Save_Quote_From_List_Items_PVT;
3059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3060
3061 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3062 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3063 l_api_name);
3064 END IF;
3065
3066 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3067 p_count => x_msg_count,
3068 p_data => x_msg_data);
3069 END Save_Quote_From_List_Items;
3070
3071 END IBE_Shop_List_PVT;