1 PACKAGE BODY AMS_RUNTIME_PROD_PVT as
2 /* $Header: amsvrpdb.pls 115.20 2004/07/27 14:06:48 sikalyan ship $*/
3
4 AMS_DEBUG_HIGH_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
5 AMS_DEBUG_LOW_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
6 AMS_DEBUG_MEDIUM_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
7
8 FUNCTION Is_Relationship_Valid(
9 p_relation_type_code VARCHAR2) RETURN BOOLEAN
10 IS
11 l_start_date DATE;
12 l_end_date DATE;
13 BEGIN
14 SELECT start_date_active, end_date_active
15 INTO l_start_date, l_end_date
16 FROM FND_LOOKUPS
17 WHERE lookup_type = 'IBE_RELATIONSHIP_TYPES'
18 AND lookup_code = p_relation_type_code
19 AND enabled_flag = 'Y';
20
21 -- if relationship type code is inactive, return false
22 IF NVL(l_start_date, SYSDATE) > SYSDATE
23 OR NVL(l_end_date, SYSDATE) < SYSDATE THEN
24 RETURN FALSE;
25 ELSE
26 RETURN TRUE;
27 END IF;
28
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN -- Error: relationship type does not exist
31 RETURN FALSE;
32 END Is_Relationship_Valid;
33
34
35 FUNCTION Exists_In_MTL(
36 p_relation_type_code IN VARCHAR2) RETURN BOOLEAN
37 IS
38 BEGIN
39 IF p_relation_type_code = 'RELATED' OR
40 p_relation_type_code = 'SUBSTITUTE' OR
41 p_relation_type_code = 'CROSS_SELL' OR
42 p_relation_type_code = 'UP_SELL' OR
43 p_relation_type_code = 'SERVICE' OR
44 p_relation_type_code = 'PREREQUISITE' OR
45 p_relation_type_code = 'COLLATERAL' OR
46 p_relation_type_code = 'SUPERSEDED' OR
47 p_relation_type_code = 'COMPLIMENTARY' OR
48 p_relation_type_code = 'IMPACT' OR
49 p_relation_type_code = 'CONFLICT' OR
50 p_relation_type_code = 'MANDATORY_CHARGE' OR
51 p_relation_type_code = 'OPTIONAL_CHARGE' OR
52 p_relation_type_code = 'PROMOTIONAL_UPGRADE' THEN
53 RETURN TRUE;
54 ELSE
55 RETURN FALSE;
56 END IF;
57 END Exists_In_MTL;
58
59
60 FUNCTION isBelongToMinisite(p_item_id IN NUMBER, p_msite_id IN NUMBER) RETURN BOOLEAN
61 IS
62 rowsReturned NUMBER :=0;
63 BEGIN
64 SELECT COUNT(B.SECTION_ITEM_ID) INTO rowsReturned FROM JTF_DSP_SECTION_ITEMS s, JTF_DSP_MSITE_SCT_ITEMS b
65 WHERE S.SECTION_ITEM_ID = B.SECTION_ITEM_ID AND B.MINI_SITE_ID = p_msite_id
66 AND S.INVENTORY_ITEM_ID = p_item_id
67 AND NVL(S.START_DATE_ACTIVE,SYSDATE) < SYSDATE
68 AND NVL(S.END_DATE_ACTIVE,SYSDATE) > SYSDATE;
69 IF rowsReturned > 0 THEN
70 RETURN TRUE;
71 ELSE
72 RETURN FALSE;
73 END IF;
74 EXCEPTION
75 WHEN NO_DATA_FOUND THEN
76 RETURN FALSE;
77 END isBelongToMinisite;
78
79
80 -- Start of comments
81 -- API name : Get_Related_Items
82 -- Type : Public or Group or Private.
83 -- Function :
84 -- Pre-reqs : None.
85 -- Parameters :
86 -- OUT :
87 --
88 -- Notes : Note text
89 --
90 -- End of comments
91 PROCEDURE getRelatedItems(
92 p_api_version_number IN NUMBER ,
93 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
94 p_application_id IN NUMBER ,
95 p_msite_id IN NUMBER ,
96 p_top_section_id IN NUMBER ,
97 p_incl_section IN VARCHAR2 := NULL ,
98 p_prod_lst IN JTF_NUMBER_TABLE ,
99 p_rel_type_code IN VARCHAR2 ,
100 p_org_id IN NUMBER ,
101 p_max_ret_num IN NUMBER := NULL ,
102 p_order_by_clause IN VARCHAR2 := NULL ,
103 x_items_tbl OUT NOCOPY JTF_Number_Table ,
104 x_return_status OUT NOCOPY VARCHAR2 ,
105 x_msg_count OUT NOCOPY NUMBER ,
106 x_msg_data OUT NOCOPY VARCHAR2
107 )
108 IS
109 l_api_name CONSTANT VARCHAR2(30) := 'getRelatedItems';
110 l_api_version CONSTANT NUMBER := 1.0;
111
112 l_minisite_stmt VARCHAR2(2000) :=
113 ' AND ICRI.related_item_id IN (SELECT D.item_id FROM AMS_IBA_MS_ITEMS_DENORM D
114 WHERE D.MINISITE_ID = :msite_id
115 AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
116 AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE )';
117
118 l_minisite_section_stmt VARCHAR2(2000) :=
119 ' AND ICRI.related_item_id IN (SELECT D.item_id FROM AMS_IBA_MS_ITEMS_DENORM D
120 WHERE D.MINISITE_ID = :msite_id
121 AND D.TOP_SECTION_ID = :top_section_id
122 AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
123 AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE )';
124
125 l_minisite_not_in_section_stmt VARCHAR2(2000) :=
126 ' AND ICRI.related_item_id NOT IN (SELECT D.item_id FROM AMS_IBA_MS_ITEMS_DENORM D
127 WHERE D.MINISITE_ID = :msite_id
128 AND D.TOP_SECTION_ID = :top_section_id
129 AND D.ITEM_ID = ICRI.related_item_id
130 AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
131 AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE )';
132
133 l_without_mtl_stmt VARCHAR2(4000) :=
134 'SELECT DISTINCT ICRI.related_item_id
135 FROM ibe_ct_related_items ICRI,
136 mtl_system_items_b MSIB
137 WHERE ICRI.relation_type_code = :rel_type_code1
138 AND NOT EXISTS( SELECT NULL
139 FROM ibe_ct_rel_exclusions ICRE
140 WHERE ICRE.relation_type_code = ICRI.relation_type_code
141 AND ICRE.inventory_item_id = ICRI.inventory_item_id
142 AND ICRE.related_item_id = ICRI.related_item_id )
143 AND MSIB.organization_id = :org_id3
144 AND MSIB.inventory_item_id = ICRI.related_item_id
145 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
146 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
147 AND MSIB.web_status = ''PUBLISHED''
148 AND ICRI.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
149
150 l_with_mtl_stmt1 VARCHAR2(4000) :=
151 ' SELECT DISTINCT ICRI.related_item_id
152 FROM ibe_ct_related_items ICRI,
153 mtl_system_items_b MSIB
154 WHERE ICRI.relation_type_code = :rel_type_code1
155 AND MSIB.organization_id = :org_id2
156 AND MSIB.inventory_item_id = ICRI.related_item_id
157 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
158 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
159 AND MSIB.web_status = ''PUBLISHED''
160 AND ICRI.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
161
162 l_with_mtl_stmt4 VARCHAR2(2000) :=
163 ' AND ICRI.related_item_id NOT IN (
164 SELECT ICRE.related_item_id
165 FROM ibe_ct_rel_exclusions ICRE
166 WHERE ICRE.relation_type_code = :rel_type_code1
167 AND ICRE.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
168
169 l_without_mtl_bulk_stmt VARCHAR2(4000) :=
170 'BEGIN
171 SELECT DISTINCT ICRI.related_item_id
172 BULK COLLECT INTO :items_tbl1
173 FROM ibe_ct_related_items ICRI,
174 mtl_system_items_b MSIB
175 WHERE ICRI.relation_type_code = :rel_type_code2
176 AND NOT EXISTS( SELECT NULL
177 FROM ibe_ct_rel_exclusions ICRE
178 WHERE ICRE.relation_type_code = ICRI.relation_type_code
179 AND ICRE.inventory_item_id = ICRI.inventory_item_id
180 AND ICRE.related_item_id = ICRI.related_item_id )
181 AND MSIB.organization_id = :org_id4
182 AND MSIB.inventory_item_id = ICRI.related_item_id
183 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
184 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
185 AND MSIB.web_status = ''PUBLISHED''
186 AND ICRI.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
187
188 l_stmt VARCHAR2(2000);
189 l_rel_items_csr prod_cursor;
190 l_rel_item_id NUMBER;
191 l_sql_stmt VARCHAR2(2000);
192 l_bind_arg_num PLS_INTEGER;
193 l_dummy VARCHAR2(30);
194 i PLS_INTEGER := 1;
195 include_mtl BOOLEAN;
196 p_index BINARY_INTEGER;
197 l_items_in_clause VARCHAR2(32760);
198
199
200 BEGIN
201 -- Standard call to check for call compatibility.
202 IF NOT FND_API.Compatible_API_Call( l_api_version,
203 p_api_version_number,
204 l_api_name,
205 G_PKG_NAME )
206 THEN
207 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
208 END IF;
209
210 -- Initialize message list if p_init_msg_list is set to TRUE.
211 IF FND_API.to_Boolean( p_init_msg_list ) THEN
212 FND_MSG_PUB.initialize;
213 END IF;
214
215 -- Initialize API return status to success
216 x_return_status := FND_API.G_RET_STS_SUCCESS;
217
218 IF (AMS_DEBUG_HIGH_ON) THEN
219
220
221
222 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelatedItems starts');
223
224 END IF;
225
226 -- Initialize the return value table
227 x_items_tbl := JTF_Number_Table();
228
229 IF (AMS_DEBUG_HIGH_ON) THEN
230
231
232
233 AMS_UTILITY_PVT.debug_message('p_org_id : p_rel_type_code '
234 || p_org_id || ' : '
235 || p_rel_type_code);
236
237 END IF;
238
239 -- Commented IN Clause BugFix 3776065
240
241 -- p_Index := p_prod_lst.FIRST;
242 -- FOR pNum IN 1..( p_prod_lst.COUNT - 1 ) LOOP
243 -- l_items_in_clause := l_items_in_clause || TO_CHAR( p_prod_lst( p_Index ) ) || ', ';
244 -- p_Index := p_prod_lst.NEXT( p_Index );
245 -- END LOOP;
246
247 -- p_Index := p_prod_lst.LAST;
248 -- l_items_in_clause := l_items_in_clause || TO_CHAR( p_prod_lst( p_Index ) ) || ')';
249
250 -- 1. Get the related items from ibe_ct_related_items table
251
252 include_mtl := Exists_In_MTL(p_rel_type_code);
253
254 IF (p_max_ret_num IS NULL) AND (NOT include_mtl) THEN -- Can use bulk fetching
255
256 IF (AMS_DEBUG_HIGH_ON) THEN
257
258 AMS_UTILITY_PVT.debug_message('p_max_ret_num is NULL: relationship NOT in MTL. Top Section Id ' || To_CHAR(p_top_section_id));
259
260 END IF;
261
262 IF (p_top_section_id IS NULL OR p_incl_section IS NULL) THEN
263 --no Section Filtering, only Minisite Filtering
264 EXECUTE IMMEDIATE l_without_mtl_bulk_stmt ||
265 l_minisite_stmt ||
266 '; END;'
267 USING OUT x_items_tbl, p_rel_type_code, p_org_id, p_prod_lst, p_msite_id;
268 ELSIF (p_incl_section = FND_API.G_TRUE) THEN
269 --Include Items in top Section and Minisite
270 EXECUTE IMMEDIATE l_without_mtl_bulk_stmt ||
271 l_minisite_section_stmt ||
272 '; END;'
273 USING OUT x_items_tbl, p_rel_type_code, p_org_id, p_prod_lst, p_msite_id, p_top_section_id;
274 ELSE
275 --Include Items in Minisite but not in top section
276 EXECUTE IMMEDIATE l_without_mtl_bulk_stmt ||
277 l_minisite_stmt ||
278 l_minisite_not_in_section_stmt||
279 '; END;'
280 USING OUT x_items_tbl, p_rel_type_code, p_org_id, p_prod_lst, p_msite_id, p_msite_id, p_top_section_id;
281 END IF;
282
283 ELSE -- Cannot use bulk fetching
284
285 IF include_mtl THEN
286 IF (AMS_DEBUG_HIGH_ON) THEN
287
288 AMS_UTILITY_PVT.debug_message('relationship in MTL. Top Section Id ' || To_CHAR(p_top_section_id));
289 END IF;
290 IF (p_top_section_id IS NULL OR p_incl_section IS NULL) THEN
291 --no Section Filtering, only Minisite Filtering
292 OPEN l_rel_items_csr FOR
293 l_with_mtl_stmt1 ||
294 l_minisite_stmt ||
295 l_with_mtl_stmt4 ||
296 ')'
297 USING p_rel_type_code, p_org_id, p_prod_lst, p_msite_id, p_rel_type_code, p_prod_lst;
298 ELSIF (p_incl_section = FND_API.G_TRUE) THEN
299 --Include Items in top Section and Minisite
300 OPEN l_rel_items_csr FOR
301 l_with_mtl_stmt1 ||
302 l_minisite_section_stmt ||
303 l_with_mtl_stmt4 ||
304 ')'
305 USING p_rel_type_code, p_org_id, p_prod_lst, p_msite_id, p_top_section_id, p_rel_type_code, p_prod_lst;
306 ELSE
307 --Include Items in Minisite but not in top section
308 OPEN l_rel_items_csr FOR
309 l_with_mtl_stmt1 ||
310 l_minisite_stmt ||
311 l_minisite_not_in_section_stmt ||
312 l_with_mtl_stmt4 ||
313 ')'
314 USING p_rel_type_code, p_org_id, p_prod_lst, p_msite_id, p_msite_id, p_top_section_id, p_rel_type_code, p_prod_lst;
315 END IF;
316
317 ELSE -- don't need to do union with mtl_related_items
318 IF (AMS_DEBUG_HIGH_ON) THEN
319
320 AMS_UTILITY_PVT.debug_message('p_max_ret_num is not NULL: relationship in MTL. Top Section Id ' || To_CHAR(p_top_section_id));
321 END IF;
322
323 IF (p_top_section_id IS NULL OR p_incl_section IS NULL) THEN
324 --no Section Filtering, only Minisite Filtering
325 OPEN l_rel_items_csr FOR
326 l_without_mtl_stmt ||
327 l_minisite_stmt
328 USING p_rel_type_code, p_org_id, p_prod_lst, p_msite_id;
329 ELSIF (p_incl_section = FND_API.G_TRUE) THEN
330 --Include Items in top Section and Minisite
331 OPEN l_rel_items_csr FOR
332 l_without_mtl_stmt ||
333 l_minisite_section_stmt
334 USING p_rel_type_code, p_org_id, p_prod_lst, p_msite_id, p_top_section_id;
335 ELSE
336 --Include Items in Minisite but not in top section
337 OPEN l_rel_items_csr FOR
338 l_without_mtl_stmt ||
339 l_minisite_stmt ||
340 l_minisite_not_in_section_stmt
341 USING p_rel_type_code, p_org_id, p_prod_lst, p_msite_id, p_msite_id, p_top_section_id;
342 END IF;
343
344 END IF;
345
346 IF p_max_ret_num IS NULL THEN
347 LOOP
348 FETCH l_rel_items_csr INTO l_rel_item_id;
349 EXIT WHEN l_rel_items_csr%NOTFOUND;
350 x_items_tbl.EXTEND;
351 x_items_tbl(i) := l_rel_item_id;
352 i := i + 1;
353 END LOOP;
354 ELSE
355 LOOP
356 FETCH l_rel_items_csr INTO l_rel_item_id;
357 EXIT WHEN l_rel_items_csr%NOTFOUND;
358 x_items_tbl.EXTEND;
359 x_items_tbl(i) := l_rel_item_id;
360 i := i + 1;
361
362 IF i > p_max_ret_num THEN
363 EXIT;
364 END IF;
365 END LOOP;
366 END IF;
367
368
369 CLOSE l_rel_items_csr;
370 END IF;
371
372 IF (AMS_DEBUG_HIGH_ON) THEN
373
374
375
376 AMS_UTILITY_PVT.debug_message('No of items collected ' || To_CHAR(x_items_tbl.COUNT));
377
378 END IF;
379
380 -- End of API body.
381
382 IF (AMS_DEBUG_HIGH_ON) THEN
383 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelatedItems ends');
384 END IF;
385
386 -- Standard call to get message count and if count is 1, get message info.
387 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
388 p_count => x_msg_count,
389 p_data => x_msg_data);
390
391 EXCEPTION
392 WHEN FND_API.G_EXC_ERROR THEN
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
395 p_count => x_msg_count,
396 p_data => x_msg_data);
397
398 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
399 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
400 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
401 p_count => x_msg_count,
402 p_data => x_msg_data);
403
404 WHEN OTHERS THEN
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406
407 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
408 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
409 l_api_name );
410 END IF;
411 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
412 p_count => x_msg_count,
413 p_data => x_msg_data);
414
415 END getRelatedItems;
416
417 -- Start of comments
418 -- API name : Get_Related_Items
419 -- Type : Public or Group or Private.
420 -- Function :
421 -- Pre-reqs : None.
422 -- Parameters :
423 -- Notes : Note text
424 --
425 -- End of comments
426 PROCEDURE getRelatedItems(
427 p_api_version_number IN NUMBER ,
428 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
429 p_application_id IN NUMBER ,
430 p_prod_lst IN JTF_NUMBER_TABLE ,
431 p_rel_type_code IN VARCHAR2 ,
432 p_org_id IN NUMBER ,
433 p_max_ret_num IN NUMBER := NULL ,
434 p_order_by_clause IN VARCHAR2 := NULL ,
435 x_items_tbl OUT NOCOPY JTF_Number_Table ,
436 x_return_status OUT NOCOPY VARCHAR2 ,
437 x_msg_count OUT NOCOPY NUMBER ,
438 x_msg_data OUT NOCOPY VARCHAR2
439 )
440 IS
441 l_api_name CONSTANT VARCHAR2(30) := 'getRelatedItems';
442 l_api_version CONSTANT NUMBER := 1.0;
443
444 l_without_mtl_stmt VARCHAR2(3000) :=
445 ' SELECT DISTINCT ICRI.related_item_id
446 FROM ibe_ct_related_items ICRI,
447 mtl_system_items_b MSIB
448 WHERE ICRI.relation_type_code = :rel_type_code1
449 AND NOT EXISTS( SELECT NULL
450 FROM ibe_ct_rel_exclusions ICRE
451 WHERE ICRE.relation_type_code = ICRI.relation_type_code
452 AND ICRE.inventory_item_id = ICRI.inventory_item_id
453 AND ICRE.related_item_id = ICRI.related_item_id )
454 AND MSIB.organization_id = :org_id2
455 AND MSIB.inventory_item_id = ICRI.related_item_id
456 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
457 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
458 AND MSIB.web_status = ''PUBLISHED''
459 AND ICRI.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
460
461 l_with_mtl_stmt1 VARCHAR2(2500) :=
462
463 ' SELECT DISTINCT ICRI.related_item_id
464 FROM ibe_ct_related_items ICRI,
465 mtl_system_items_b MSIB
466 WHERE ICRI.relation_type_code = :rel_type_code1
467 AND MSIB.organization_id = :org_id2
468 AND MSIB.inventory_item_id = ICRI.related_item_id
469 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
470 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
471 AND MSIB.web_status = ''PUBLISHED''
472 AND ICRI.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
473
474
475 l_with_mtl_stmt2 VARCHAR2(3000) :=
476 ' UNION ALL
477 SELECT MRI.related_item_id
478 FROM mtl_related_items MRI,
479 mtl_system_items_b MSIB
480 WHERE MRI.relationship_type_id = DECODE(:rel_type_code5, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
481 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
482 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
483 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
484 AND MSIB.organization_id = :org_id7
485 AND MSIB.inventory_item_id = MRI.related_item_id
486 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
487 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
488 AND MSIB.web_status = ''PUBLISHED''
489 AND MRI.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
490
491 l_with_mtl_stmt3 VARCHAR2(3000) :=
492 ' UNION ALL
493 SELECT MRI.inventory_item_id
494 FROM mtl_related_items MRI,
495 mtl_system_items_b MSIB
496 WHERE MRI.relationship_type_id = DECODE(:rel_type_code9, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
497 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
498 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
499 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
500 AND MSIB.organization_id = :org_id11
501 AND MSIB.inventory_item_id = MRI.inventory_item_id
502 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
503 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
504 AND MSIB.web_status = ''PUBLISHED''
505 AND MRI.reciprocal_flag = ''Y''
506 AND MRI.related_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
507
508 l_with_mtl_stmt4 VARCHAR2(2000) :=
509 ' MINUS
510 SELECT ICRE.related_item_id
511 FROM ibe_ct_rel_exclusions ICRE
512 WHERE ICRE.relation_type_code = :rel_type_code13
513 AND ICRE.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
514
515 l_without_mtl_bulk_stmt VARCHAR2(2500) :=
516 'BEGIN
517 SELECT DISTINCT ICRI.related_item_id
518 BULK COLLECT INTO :items_tbl1
519 FROM ibe_ct_related_items ICRI,
520 mtl_system_items_b MSIB
521 WHERE ICRI.relation_type_code = :rel_type_code2
522 AND NOT EXISTS( SELECT NULL
523 FROM ibe_ct_rel_exclusions ICRE
524 WHERE ICRE.relation_type_code = ICRI.relation_type_code
525 AND ICRE.inventory_item_id = ICRI.inventory_item_id
526 AND ICRE.related_item_id = ICRI.related_item_id )
527 AND MSIB.organization_id = :org_id4
528 AND MSIB.inventory_item_id = ICRI.related_item_id
529 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
530 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
531 AND MSIB.web_status = ''PUBLISHED''
532 AND ICRI.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
533
534 l_stmt VARCHAR2(2000);
535 l_rel_items_csr prod_cursor;
536 l_rel_item_id NUMBER;
537 l_sql_stmt VARCHAR2(2000);
538 l_bind_arg_num PLS_INTEGER;
539 l_dummy VARCHAR2(30);
540 i PLS_INTEGER := 1;
541 include_mtl BOOLEAN;
542 p_index BINARY_INTEGER;
543 l_items_in_clause VARCHAR2(32760);
544
545
546 BEGIN
547 -- Standard call to check for call compatibility.
548 IF NOT FND_API.Compatible_API_Call( l_api_version,
549 p_api_version_number,
550 l_api_name,
551 G_PKG_NAME )
552 THEN
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
554 END IF;
555
556 -- Initialize message list if p_init_msg_list is set to TRUE.
557 IF FND_API.to_Boolean( p_init_msg_list ) THEN
558 FND_MSG_PUB.initialize;
559 END IF;
560
561 -- Initialize API return status to success
562 x_return_status := FND_API.G_RET_STS_SUCCESS;
563
564 IF (AMS_DEBUG_HIGH_ON) THEN
565
566
567
568 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelatedItems starts');
569
570 END IF;
571
572 -- Initialize the return value table
573 x_items_tbl := JTF_Number_Table();
574
575 IF (AMS_DEBUG_HIGH_ON) THEN
576 AMS_UTILITY_PVT.debug_message('p_org_id : p_rel_type_code = '
577 || TO_CHAR(p_org_id) || ' : ' || p_rel_type_code);
578 END IF;
579 -- Commented IN Clause BugFix 3776065
580 -- p_Index := p_prod_lst.FIRST;
581 -- FOR pNum IN 1..( p_prod_lst.COUNT - 1 ) LOOP
582 -- l_items_in_clause := l_items_in_clause || TO_CHAR( p_prod_lst( p_Index ) ) || ', ';
583 -- p_Index := p_prod_lst.NEXT( p_Index );
584 -- END LOOP;
585
586 -- p_Index := p_prod_lst.LAST;
587 -- l_items_in_clause := l_items_in_clause || TO_CHAR( p_prod_lst( p_Index ) ) || ')';
588
589 -- API body
590
591 -- 1. Get the related items from ibe_ct_related_items table
592
593 include_mtl := Exists_In_MTL(p_rel_type_code);
594
595 IF (p_max_ret_num IS NULL) AND (NOT include_mtl) THEN -- Can use bulk fetching
596 IF (AMS_DEBUG_HIGH_ON) THEN
597
598 AMS_UTILITY_PVT.debug_message(' p_max_ret_num is NULL: relationship NOT in MTL.', FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR);
599 END IF;
600
601 EXECUTE IMMEDIATE l_without_mtl_bulk_stmt ||
602 '; END;'
603 USING OUT x_items_tbl, p_rel_type_code, p_org_id ,p_prod_lst;
604
605 ELSE -- Cannot use bulk fetching
606 IF (AMS_DEBUG_HIGH_ON) THEN
607 AMS_UTILITY_PVT.debug_message(' relationship in MTL.');
608 END IF;
609
610 IF include_mtl THEN -- must do union with mtl_related_items
611 IF (AMS_DEBUG_HIGH_ON) THEN
612 AMS_UTILITY_PVT.debug_message('Mapping rule: relationship in MTL.'||l_items_in_clause);
613 END IF;
614
615 OPEN l_rel_items_csr FOR l_with_mtl_stmt1 ||
616 l_with_mtl_stmt2 ||
617 l_with_mtl_stmt3 ||
618 l_with_mtl_stmt4
619
620 USING p_rel_type_code, p_org_id, p_prod_lst,
621 p_rel_type_code, p_org_id, p_prod_lst,
622 p_rel_type_code, p_org_id, p_prod_lst,
623 p_rel_type_code, p_prod_lst;
624
625 ELSE -- don't need to do union with mtl_related_items
626 IF (AMS_DEBUG_HIGH_ON) THEN
627
628 AMS_UTILITY_PVT.debug_message('p_max_ret_num is NOT NULL: relationship in MTL.');
629 END IF;
630
631 OPEN l_rel_items_csr FOR l_without_mtl_stmt
632 USING p_rel_type_code, p_org_id, p_prod_lst;
633 END IF;
634
635 IF p_max_ret_num IS NULL THEN
636 LOOP
637 FETCH l_rel_items_csr INTO l_rel_item_id;
638 EXIT WHEN l_rel_items_csr%NOTFOUND;
639 x_items_tbl.EXTEND;
640 x_items_tbl(i) := l_rel_item_id;
641 i := i + 1;
642 END LOOP;
643 ELSE
644 LOOP
645 IF (AMS_DEBUG_HIGH_ON) THEN
646
647 AMS_UTILITY_PVT.debug_message('Loop ');
648 END IF;
649 FETCH l_rel_items_csr INTO l_rel_item_id;
650 EXIT WHEN l_rel_items_csr%NOTFOUND;
651 x_items_tbl.EXTEND;
652 x_items_tbl(i) := l_rel_item_id;
653 i := i + 1;
654
655 IF i > p_max_ret_num THEN
656 EXIT;
657 END IF;
658 END LOOP;
659 END IF;
660
661 CLOSE l_rel_items_csr;
662 END IF;
663
664 -- End of API body.
665
666 IF (AMS_DEBUG_HIGH_ON) THEN
667 AMS_UTILITY_PVT.debug_message('Max ' || To_CHAR(p_max_ret_num));
668 END IF;
669
670 IF (AMS_DEBUG_HIGH_ON) THEN
671 AMS_UTILITY_PVT.debug_message('No of items collected ' || To_CHAR(x_items_tbl.COUNT));
672 END IF;
673
674 IF (AMS_DEBUG_HIGH_ON) THEN
675 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelatedItems ends');
676 END IF;
677
678 -- Standard call to get message count and if count is 1, get message info.
679 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
680 p_count => x_msg_count,
681 p_data => x_msg_data);
682
683 EXCEPTION
684 WHEN FND_API.G_EXC_ERROR THEN
685 x_return_status := FND_API.G_RET_STS_ERROR;
686 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
687 p_count => x_msg_count,
688 p_data => x_msg_data);
689
690 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
691 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
692 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
693 p_count => x_msg_count,
694 p_data => x_msg_data);
695
696
697 WHEN OTHERS THEN
698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
699
700 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
701 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
702 l_api_name );
703 END IF;
704 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
705 p_count => x_msg_count,
706 p_data => x_msg_data);
707
708 END getRelatedItems;
709
710
711 PROCEDURE sortRandom
712 (
713 p_input_lst IN JTF_NUMBER_TABLE,
714 p_max_ret_num IN NUMBER := NULL,
715 x_output_lst OUT NOCOPY JTF_Number_Table
716 )
717 IS
718 l_input_lst JTF_NUMBER_TABLE;
719 l_randoms JTF_NUMBER_TABLE;
720 i PLS_INTEGER := 1;
721 j PLS_INTEGER := 1;
722 limit PLS_INTEGER;
723 temp NUMBER;
724 BEGIN
725
726 IF (AMS_DEBUG_HIGH_ON) THEN
727
728
729
730 AMS_UTILITY_PVT.debug_message('random sorting starts');
731
732 END IF;
733 l_input_lst := JTF_NUMBER_TABLE();
734 x_output_lst := JTF_NUMBER_TABLE();
735 for i in 1..p_input_lst.COUNT
736 loop
737 l_input_lst.EXTEND;
738 l_input_lst(i) := p_input_lst(i);
739 end loop;
740
741 l_randoms := JTF_NUMBER_TABLE();
742 IF(p_input_lst.COUNT > 1) THEN
743
744 --first generate all random numbers
745 for i in 1..p_input_lst.COUNT
746 loop
747 l_randoms.EXTEND;
748 l_randoms(i) := dbms_random.value;
749 IF (AMS_DEBUG_HIGH_ON) THEN
750
751 AMS_UTILITY_PVT.debug_message('random value '||to_char(l_randoms(i))||' for '||p_input_lst(i));
752 END IF;
753 end loop;
754
755 -- then , do bubble sort the ids based on random numbers values
756 -- outer loop
757 for i in 1..p_input_lst.COUNT
758 loop
759 --inner loop
760 limit := p_input_lst.COUNT-i+1;
761 for j in 1..limit-1
762 loop
763 --exchange positions if greater
764 IF(l_randoms(j) > l_randoms(j+1)) THEN
765 temp := l_randoms(j);
766 l_randoms(j) := l_randoms(j+1);
767 l_randoms(j+1) := temp;
768
769 temp := l_input_lst(j);
770 l_input_lst(j) := l_input_lst(j+1);
771 l_input_lst(j+1) := temp;
772 END IF;
773 end loop;
774 end loop;
775 ELSE
776 null;
777 END IF;
778
779 --collect max no elements for random prioritization now
780 IF(p_max_ret_num IS NULL) THEN
781 x_output_lst := l_input_lst;
782 ELSE
783 IF(p_max_ret_num < l_input_lst.COUNT) THEN
784 limit := p_max_ret_num;
785 ELSE
786 limit := l_input_lst.COUNT;
787 END IF;
788 for i in 1..limit
789 loop
790 x_output_lst.EXTEND;
791 x_output_lst(i) := l_input_lst(i);
792 end loop;
793 END IF;
794 IF (AMS_DEBUG_HIGH_ON) THEN
795
796 AMS_UTILITY_PVT.debug_message('random sorting ends');
797 END IF;
798 END sortRandom;
799
800
801 PROCEDURE getFilteredProdsFromList
802 (p_api_version_number IN NUMBER,
803 p_init_msg_list IN VARCHAR2,
804 p_application_id IN NUMBER,
805 p_party_id IN NUMBER,
806 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
807 p_currency_code IN VARCHAR2 := NULL,
808 p_prod_lst IN JTF_NUMBER_TABLE,
809 p_msite_id IN NUMBER := NULL,
810 p_top_section_id IN NUMBER := NULL,
811 p_org_id IN NUMBER,
812 p_bus_prior IN VARCHAR2 := NULL,
813 p_bus_prior_order IN VARCHAR2 := NULL,
814 p_filter_ref_code IN VARCHAR2 := NULL,
815 p_price_list_id IN NUMBER := NULL,
816 p_max_ret_num IN NUMBER := NULL,
817 x_prod_lst OUT NOCOPY JTF_Number_Table,
818 x_return_status OUT NOCOPY VARCHAR2,
819 x_msg_count OUT NOCOPY NUMBER,
820 x_msg_data OUT NOCOPY VARCHAR2
821 )
822 IS
823 l_api_name CONSTANT VARCHAR2(30) := 'getFilteredProdsFromList';
824 l_api_version CONSTANT NUMBER := 1.0;
825
826 l_minisite_bulk_stmt VARCHAR2(2500) :=
827 'BEGIN SELECT MSIB.inventory_item_id
828 BULK COLLECT INTO :items_tbl1
829 FROM mtl_system_items_b MSIB,
830 ams_iba_ms_items_denorm D
831 WHERE MSIB.organization_id = :org_id2
832 AND MSIB.inventory_item_id = D.item_id
833 AND D.MINISITE_ID = :msite_id3
834 AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
835 AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE
836 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
837 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
838 AND MSIB.web_status = ''PUBLISHED''
839 AND MSIB.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
840
841 l_minisite_stmt VARCHAR2(2500) :=
842 'SELECT MSIB.inventory_item_id
843 FROM mtl_system_items_b MSIB,
844 ams_iba_ms_items_denorm D
845 WHERE MSIB.organization_id = :org_id2
846 AND MSIB.inventory_item_id = D.item_id
847 AND D.MINISITE_ID = :msite_id3
848 AND NVL(D.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
849 AND NVL(D.END_DATE_ACTIVE,SYSDATE) >= SYSDATE
850 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
851 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
852 AND MSIB.web_status = ''PUBLISHED''
853 AND MSIB.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
854
855 l_minisite_section_stmt VARCHAR2(2000) :=
856 ' AND D.top_section_id = :top_section_id3';
857
858 l_minisite_not_in_section_stmt VARCHAR2(2000) :=
859 ' AND MSIB.inventory_item_id NOT IN (
860 SELECT D.item_id
861 FROM ams_iba_ms_items_denorm D
862 WHERE D.MINISITE_ID = :msite_id
863 AND D.top_section_id = :top_section_id)';
864
865 l_mtl_bulk_stmt VARCHAR2(2500) :=
866 'BEGIN SELECT MSIB.inventory_item_id
867 BULK COLLECT INTO :items_tbl1
868 FROM mtl_system_items_b MSIB
869 WHERE MSIB.organization_id = :org_id2
870 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
871 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
872 AND MSIB.web_status = ''PUBLISHED''
873 AND MSIB.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
874
875 l_mtl_stmt VARCHAR2(2500) :=
876 'SELECT MSIB.inventory_item_id
877 FROM mtl_system_items_b MSIB
878 WHERE MSIB.organization_id = :org_id2
879 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
880 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
881 AND MSIB.web_status = ''PUBLISHED''
882 AND MSIB.inventory_item_id IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_prod_lst AS JTF_NUMBER_TABLE)) t)';
883
884 l_stmt VARCHAR2(2000);
885 l_rel_items_csr prod_cursor;
886 l_rel_item_id NUMBER;
887 l_items_in_clause VARCHAR2(32760);
888 l_prod_lst JTF_NUMBER_TABLE;
889 l_item_id NUMBER;
890 l_limit NUMBER;
891 p_index BINARY_INTEGER;
892 i PLS_INTEGER := 1;
893 l_random NUMBER;
894
895 BEGIN
896 -- Standard call to check for call compatibility.
897 IF NOT FND_API.Compatible_API_Call( l_api_version,
898 p_api_version_number,
899 l_api_name,
900 G_PKG_NAME )
901 THEN
902 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
903 END IF;
904
905 -- Initialize message list if p_init_msg_list is set to TRUE.
906 IF FND_API.to_Boolean( p_init_msg_list ) THEN
907 FND_MSG_PUB.initialize;
908 END IF;
909
910 -- Initialize API return status to success
911 x_return_status := FND_API.G_RET_STS_SUCCESS;
912
913 IF (AMS_DEBUG_HIGH_ON) THEN
914 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getFilteredProdsFromList starts');
915 END IF;
916
917 -- Initialize the return value table
918 x_prod_lst := JTF_Number_Table();
919 l_prod_lst := JTF_NUMBER_TABLE();
920 -- Commented IN Clause BugFix 3776065
921 -- p_Index := p_prod_lst.FIRST;
922 -- FOR pNum IN 1..( p_prod_lst.COUNT - 1 ) LOOP
923 -- l_items_in_clause := l_items_in_clause || TO_CHAR( p_prod_lst( p_Index ) ) || ', ';
924 -- p_Index := p_prod_lst.NEXT( p_Index );
925 -- END LOOP;
926
927 -- p_Index := p_prod_lst.LAST;
928 -- l_items_in_clause := l_items_in_clause || TO_CHAR( p_prod_lst( p_Index ) ) || ')';
929
930 -- IF (AMS_DEBUG_HIGH_ON) THEN
931 -- AMS_UTILITY_PVT.debug_message('items in clause '||l_items_in_clause);
932 -- END IF;
933
934 IF(p_max_ret_num IS NULL) THEN
935 --max return no is null
936
937 IF(p_msite_id IS NOT NULL) THEN
938 IF(p_top_section_id IS NOT NULL) THEN
939 IF(p_filter_ref_code IS NULL ) THEN
940 --only Minisite filtering, no top section filtering
941
942 EXECUTE IMMEDIATE l_minisite_bulk_stmt ||
943 '; END;'
944 USING OUT l_prod_lst, p_org_id, p_msite_id, p_prod_lst;
945 ELSIF (p_filter_ref_code = 'INCL_PROD_SECTION') THEN
946 --Both Minisite and include top section filtering
947
948 EXECUTE IMMEDIATE l_minisite_bulk_stmt ||
949 l_minisite_section_stmt ||
950 '; END;'
951 USING OUT l_prod_lst, p_org_id, p_msite_id, p_prod_lst, p_top_section_id;
952 ELSIF(p_filter_ref_code = 'EXCL_PROD_SECTION') THEN
953 --exclude top section in filtering
954
955 EXECUTE IMMEDIATE l_minisite_bulk_stmt ||
956 l_minisite_section_stmt ||
957 l_minisite_not_in_section_stmt ||
958 '; END;'
959 USING OUT l_prod_lst, p_org_id, p_msite_id,p_prod_lst, p_top_section_id, p_msite_id, p_top_section_id;
960 END IF;
961 ELSE
962 --only minisite filtering
963 EXECUTE IMMEDIATE l_minisite_bulk_stmt ||
964 '; END;'
965 USING OUT l_prod_lst, p_org_id, p_msite_id ,p_prod_lst;
966 END IF;
967 ELSE
968 --no minisite filtering
969
970 EXECUTE IMMEDIATE l_mtl_bulk_stmt ||
971 '; END;'
972 USING OUT l_prod_lst, p_org_id ,p_prod_lst;
973 END IF;
974
975 ELSE
976 --max return no is non-null
977
978 IF(p_msite_id IS NOT NULL) THEN
979 IF(p_top_section_id IS NOT NULL) THEN
980 IF(p_filter_ref_code IS NULL) THEN
981 --only Minisite filtering, no top section filtering
982
983 OPEN l_rel_items_csr FOR l_minisite_stmt
984 USING p_org_id, p_msite_id ,p_prod_lst;
985
986 ELSIF(p_filter_ref_code = 'INCL_PROD_SECTION') THEN
987 --include top section in filering
988
989 OPEN l_rel_items_csr FOR l_minisite_stmt ||
990 l_minisite_section_stmt
991 USING p_org_id, p_msite_id,p_prod_lst, p_top_section_id;
992 ELSIF(p_filter_ref_code = 'EXCL_PROD_SECTION') THEN
993 --exclude top section in filtering
994
995 OPEN l_rel_items_csr FOR l_minisite_stmt ||
996 l_minisite_section_stmt ||
997 l_minisite_not_in_section_stmt
998 USING p_org_id, p_msite_id, p_prod_lst, p_top_section_id, p_msite_id, p_top_section_id;
999 END IF;
1000 ELSE
1001 --only minisite filtering
1002 OPEN l_rel_items_csr FOR l_minisite_stmt
1003 USING p_org_id, p_msite_id ,p_prod_lst;
1004 END IF;
1005 ELSE
1006 --no minisite filtering
1007
1008 OPEN l_rel_items_csr FOR l_mtl_stmt
1009 USING p_org_id,p_prod_lst;
1010 END IF;
1011
1012 IF (p_max_ret_num IS NULL OR p_bus_prior = 'RANDOM'
1013 OR (p_bus_prior = 'PROD_LIST_PRICE' AND p_price_list_id IS NOT NULL)) THEN
1014 IF (AMS_DEBUG_HIGH_ON) THEN
1015 AMS_UTILITY_PVT.debug_message('random');
1016 END IF;
1017 LOOP
1018 FETCH l_rel_items_csr INTO l_rel_item_id;
1019 EXIT WHEN l_rel_items_csr%NOTFOUND;
1020 l_prod_lst.EXTEND;
1021 l_prod_lst(i) := l_rel_item_id;
1022 i := i + 1;
1023 END LOOP;
1024 ELSE
1025 LOOP
1026 FETCH l_rel_items_csr INTO l_rel_item_id;
1027 EXIT WHEN l_rel_items_csr%NOTFOUND;
1028 l_prod_lst.EXTEND;
1029 l_prod_lst(i) := l_rel_item_id;
1030 i := i + 1;
1031
1032 IF i > p_max_ret_num THEN
1033 EXIT;
1034 END IF;
1035 END LOOP;
1036 END IF;
1037
1038 CLOSE l_rel_items_csr;
1039 END IF;
1040
1041 -- 4. Get Prioritized Products if any Product Priority is given
1042 IF(p_bus_prior = 'PROD_LIST_PRICE' AND p_price_list_id IS NOT NULL) THEN
1043 getPrioritizedProds(
1044 p_api_version_number
1045 , FND_API.G_FALSE
1046 , p_application_id
1047 , p_party_id
1048 , p_cust_account_id
1049 , p_currency_code
1050 , l_prod_lst
1051 , p_org_id
1052 , p_bus_prior
1053 , p_bus_prior_order
1054 , p_price_list_id
1055 , p_max_ret_num
1056 , x_prod_lst
1057 , x_return_status
1058 , x_msg_count
1059 , x_msg_data
1060 );
1061 ELSIF(p_bus_prior = 'RANDOM') THEN
1062 sortRandom(
1063 l_prod_lst,
1064 p_max_ret_num,
1065 x_prod_lst
1066 );
1067 ELSE
1068 x_prod_lst := l_prod_lst;
1069 END IF;
1070
1071 -- End of API body.
1072
1073 IF (AMS_DEBUG_HIGH_ON) THEN
1074 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getFilteredProdsFromList ends');
1075 END IF;
1076
1077 -- Standard call to get message count and if count is 1, get message info.
1078 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1079 p_count => x_msg_count,
1080 p_data => x_msg_data);
1081
1082 EXCEPTION
1083 WHEN FND_API.G_EXC_ERROR THEN
1084 x_return_status := FND_API.G_RET_STS_ERROR;
1085 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1086 p_count => x_msg_count,
1087 p_data => x_msg_data);
1088
1089 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1091 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1092 p_count => x_msg_count,
1093 p_data => x_msg_data);
1094
1095
1096 WHEN OTHERS THEN
1097 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1098
1099 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1100 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1101 l_api_name);
1102 END IF;
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
1107 END getFilteredProdsFromList;
1108
1109
1110
1111 PROCEDURE getRelProdsForQuoteAndCust
1112 (p_api_version_number IN NUMBER,
1113 p_init_msg_list IN VARCHAR2,
1114 p_application_id IN NUMBER,
1115 p_party_id IN NUMBER,
1116 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
1117 p_currency_code IN VARCHAR2 := NULL,
1118 p_quote_id IN NUMBER,
1119 p_msite_id IN NUMBER,
1120 p_top_section_id IN NUMBER,
1121 p_org_id IN NUMBER,
1122 p_rel_type_code IN VARCHAR2,
1123 p_bus_prior IN VARCHAR2,
1124 p_bus_prior_order IN VARCHAR2,
1125 p_filter_ref_code IN VARCHAR2,
1126 p_price_list_id IN NUMBER := NULL,
1127 p_max_ret_num IN NUMBER := NULL,
1128 x_prod_lst OUT NOCOPY JTF_NUMBER_TABLE,
1129 x_return_status OUT NOCOPY VARCHAR2,
1130 x_msg_count OUT NOCOPY NUMBER,
1131 x_msg_data OUT NOCOPY VARCHAR2
1132 )
1133 IS
1134 l_api_name CONSTANT VARCHAR2(30) := 'getRelProdsForQuoteAndCust';
1135 l_api_version CONSTANT NUMBER := 1.0;
1136
1137 l_return_status VARCHAR2( 10 );
1138 l_msg_count NUMBER;
1139 l_msg_data VARCHAR2( 1000 );
1140 l_quote_prod_lst JTF_NUMBER_TABLE;
1141 l_prod_lst JTF_NUMBER_TABLE;
1142 l_null CHAR(1);
1143 l_incl_top_section VARCHAR2(1) := NULL;
1144 l_item_id NUMBER;
1145 l_random NUMBER;
1146 l_max NUMBER;
1147 l_limit NUMBER;
1148
1149 BEGIN
1150 -- Standard call to check for call compatibility.
1151 IF NOT FND_API.Compatible_API_Call( l_api_version,
1152 p_api_version_number,
1153 l_api_name,
1154 G_PKG_NAME )
1155 THEN
1156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1157 END IF;
1158
1159 -- Initialize message list if p_init_msg_list is set to TRUE.
1160 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1161 FND_MSG_PUB.initialize;
1162 END IF;
1163
1164 -- Initialize API return status to success
1165 x_return_status := FND_API.G_RET_STS_SUCCESS;
1166
1167 IF (AMS_DEBUG_HIGH_ON) THEN
1168 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelProdsForQuoteAndCust starts');
1169 END IF;
1170
1171 -- API body
1172 -- 1. Check if the relationship exists and is active
1173 IF NOT Is_Relationship_Valid(p_rel_type_code) THEN
1174 IF (AMS_DEBUG_HIGH_ON) THEN
1175
1176 AMS_UTILITY_PVT.debug_message('Relationship is not valid.');
1177 END IF;
1178 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_VALID');
1179 FND_MESSAGE.Set_Token('RELATIONSHIP', p_rel_type_code);
1180 FND_MSG_PUB.Add;
1181 RAISE FND_API.G_EXC_ERROR;
1182 END IF;
1183
1184 IF (p_filter_ref_code = 'INCL_PROD_SECTION') THEN
1185 l_incl_top_section := FND_API.G_TRUE;
1186 ELSIF(p_filter_ref_code = 'EXCL_PROD_SECTION') THEN
1187 l_incl_top_section := FND_API.G_FALSE;
1188 END IF;
1189
1190 IF(p_bus_prior = 'RANDOM'
1191 OR (p_bus_prior = 'PROD_LIST_PRICE' AND p_price_list_id IS NOT NULL)) THEN
1192 l_max := NULL;
1193 ELSE
1194 l_max := p_max_ret_num;
1195 END IF;
1196
1197 -- 2. Collect Shopping Cart items
1198 -- GetXSellForQuote Start
1199 l_quote_prod_lst := JTF_NUMBER_TABLE();
1200
1201 select inventory_item_id
1202 bulk collect into l_quote_prod_lst
1203 from aso_quote_lines_all_v
1204 where quote_header_id = p_quote_id;
1205
1206 IF SQL%ROWCOUNT = 0 THEN
1207 x_prod_lst := JTF_Number_Table();
1208 IF (AMS_DEBUG_HIGH_ON) THEN
1209
1210 AMS_UTILITY_PVT.debug_message('No Items found for Quote Id : '||TO_CHAR(p_quote_id));
1211 END IF;
1212 ELSIF l_quote_prod_lst.COUNT = 0 THEN
1213 x_prod_lst := JTF_Number_Table();
1214 IF (AMS_DEBUG_HIGH_ON) THEN
1215
1216 AMS_UTILITY_PVT.debug_message('No Items found for Quote Id : '||TO_CHAR(p_quote_id));
1217 END IF;
1218 ELSE
1219 -- 3. Collect related items
1220 IF (p_msite_id IS NULL) THEN
1221 EXECUTE IMMEDIATE 'BEGIN AMS_RUNTIME_PROD_PVT.getRelatedItems( :1, :2, :3, :4, :5,
1222 :6, :7, :8, :9, :10, :11, :12 ); END;'
1223 USING IN l_api_version, IN FND_API.G_FALSE,
1224 IN p_application_id, IN l_quote_prod_lst,
1225 IN p_rel_type_code, IN p_org_id,
1226 IN l_max, IN l_null,
1227 OUT l_prod_lst,
1228 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
1229 ELSE
1230 EXECUTE IMMEDIATE 'BEGIN AMS_RUNTIME_PROD_PVT.getRelatedItems( :1, :2, :3, :4, :5,
1231 :6, :7, :8, :9, :10, :11, :12, :13, :14, :15 ); END;'
1232 USING IN l_api_version, IN FND_API.G_FALSE,
1233 IN p_application_id, IN p_msite_id,
1234 IN p_top_section_id, IN l_incl_top_section,
1235 IN l_quote_prod_lst, IN p_rel_type_code,
1236 IN p_org_id, IN l_max,
1237 IN l_null, OUT l_prod_lst,
1238 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
1239 END IF;
1240
1241 -- 4. Get Prioritized Products if any Product Priority is given
1242 IF(p_bus_prior = 'PROD_LIST_PRICE' AND p_price_list_id IS NOT NULL) THEN
1243 getPrioritizedProds(
1244 p_api_version_number
1245 , FND_API.G_FALSE
1246 , p_application_id
1247 , p_party_id
1248 , p_cust_account_id
1249 , p_currency_code
1250 , l_prod_lst
1251 , p_org_id
1252 , p_bus_prior
1253 , p_bus_prior_order
1254 , p_price_list_id
1255 , p_max_ret_num
1256 , x_prod_lst
1257 , x_return_status
1258 , x_msg_count
1259 , x_msg_data
1260 );
1261 ELSIF(p_bus_prior = 'RANDOM') THEN
1262 sortRandom(
1263 l_prod_lst,
1264 p_max_ret_num,
1265 x_prod_lst
1266 );
1267 ELSE
1268 x_prod_lst := l_prod_lst;
1269 END IF;
1270
1271 END IF;
1272
1273 -- End of API body.
1274
1275 IF (AMS_DEBUG_HIGH_ON) THEN
1276 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelProdsForQuoteAndCust ends');
1277 END IF;
1278
1279 -- Standard call to get message count and if count is 1, get message info.
1280 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1281 p_count => x_msg_count,
1282 p_data => x_msg_data);
1283
1284 EXCEPTION
1285 WHEN FND_API.G_EXC_ERROR THEN
1286 x_return_status := FND_API.G_RET_STS_ERROR;
1287 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1288 p_count => x_msg_count,
1289 p_data => x_msg_data);
1290
1291 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1292 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1293 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1294 p_count => x_msg_count,
1295 p_data => x_msg_data);
1296
1297
1298 WHEN OTHERS THEN
1299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1300
1301 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1302 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1303 l_api_name );
1304 END IF;
1305 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1306 p_count => x_msg_count ,
1307 p_data => x_msg_data );
1308
1309 END getRelProdsForQuoteAndCust;
1310
1311
1312
1313 PROCEDURE getRelProdsForProdAndCust
1314 (p_api_version_number IN NUMBER,
1315 p_init_msg_list IN VARCHAR2,
1316 p_application_id IN NUMBER,
1317 p_party_id IN NUMBER,
1318 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
1319 p_currency_code IN VARCHAR2 := NULL,
1320 p_prod_lst IN JTF_NUMBER_TABLE,
1321 p_msite_id IN NUMBER,
1322 p_top_section_id IN NUMBER,
1323 p_org_id IN NUMBER,
1324 p_rel_type_code IN VARCHAR2,
1325 p_bus_prior IN VARCHAR2,
1326 p_bus_prior_order IN VARCHAR2,
1327 p_filter_ref_code IN VARCHAR2,
1328 p_price_list_id IN NUMBER := NULL,
1329 p_max_ret_num IN NUMBER := NULL,
1330 x_prod_lst OUT NOCOPY JTF_NUMBER_TABLE,
1331 x_return_status OUT NOCOPY VARCHAR2,
1332 x_msg_count OUT NOCOPY NUMBER,
1333 x_msg_data OUT NOCOPY VARCHAR2
1334 )
1335 IS
1336 l_api_name CONSTANT VARCHAR2(30) := 'getRelProdsForProdAndCust';
1337 l_api_version CONSTANT NUMBER := 1.0;
1338
1339 l_return_status VARCHAR2( 10 );
1340 l_msg_count NUMBER;
1341 l_msg_data VARCHAR2( 1000 );
1342 l_prod_lst JTF_NUMBER_TABLE;
1343 l_null CHAR(1);
1344 l_incl_top_section VARCHAR2(1) := NULL;
1345 l_item_id NUMBER;
1346 l_random NUMBER;
1347 l_max NUMBER;
1348 l_limit NUMBER;
1349
1350 BEGIN
1351 -- Standard call to check for call compatibility.
1352 IF NOT FND_API.Compatible_API_Call( l_api_version,
1353 p_api_version_number,
1354 l_api_name,
1355 G_PKG_NAME )
1356 THEN
1357 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1358 END IF;
1359
1360 -- Initialize message list if p_init_msg_list is set to TRUE.
1361 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1362 FND_MSG_PUB.initialize;
1363 END IF;
1364
1365 -- Initialize API return status to success
1366 x_return_status := FND_API.G_RET_STS_SUCCESS;
1367
1368 IF (AMS_DEBUG_HIGH_ON) THEN
1369 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelProdsForProdAndCust starts');
1370 END IF;
1371
1372 -- API body
1373 -- 1. Check if the relationship exists and is active
1374 IF NOT Is_Relationship_Valid(p_rel_type_code) THEN
1375 IF (AMS_DEBUG_HIGH_ON) THEN
1376
1377 AMS_UTILITY_PVT.debug_message('Relationship is not valid.');
1378 END IF;
1379 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_VALID');
1380 FND_MESSAGE.Set_Token('RELATIONSHIP', p_rel_type_code);
1381 FND_MSG_PUB.Add;
1382 RAISE FND_API.G_EXC_ERROR;
1383 END IF;
1384
1385 IF (p_filter_ref_code = 'INCL_PROD_SECTION') THEN
1386 l_incl_top_section := FND_API.G_TRUE;
1387 ELSIF(p_filter_ref_code = 'EXCL_PROD_SECTION') THEN
1388 l_incl_top_section := FND_API.G_FALSE;
1389 END IF;
1390
1391 IF(p_bus_prior = 'RANDOM'
1392 OR (p_bus_prior = 'PROD_LIST_PRICE' AND p_price_list_id IS NOT NULL)) THEN
1393 l_max := NULL;
1394 ELSE
1395 l_max := p_max_ret_num;
1396 END IF;
1397
1398 -- 2. Collect related items
1399 IF (p_msite_id IS NULL) THEN
1400 EXECUTE IMMEDIATE 'BEGIN AMS_RUNTIME_PROD_PVT.getRelatedItems( :1, :2, :3, :4, :5,
1401 :6, :7, :8, :9, :10, :11, :12 ); END;'
1402 USING IN l_api_version, IN FND_API.G_FALSE,
1403 IN p_application_id, IN p_prod_lst,
1404 IN p_rel_type_code, IN p_org_id,
1405 IN l_max, IN l_null,
1406 OUT l_prod_lst,
1407 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
1408 ELSE
1409 EXECUTE IMMEDIATE 'BEGIN AMS_RUNTIME_PROD_PVT.getRelatedItems( :1, :2, :3, :4, :5,
1410 :6, :7, :8, :9, :10, :11, :12, :13, :14, :15 ); END;'
1411 USING IN l_api_version, IN FND_API.G_FALSE,
1412 IN p_application_id, IN p_msite_id,
1413 IN p_top_section_id, IN l_incl_top_section,
1414 IN p_prod_lst, IN p_rel_type_code,
1415 IN p_org_id, IN l_max,
1416 IN l_null, OUT l_prod_lst,
1417 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
1418 END IF;
1419
1420 -- 3. Get Prioritized Products if any Product Priority is given
1421 IF(p_bus_prior = 'PROD_LIST_PRICE' AND p_price_list_id IS NOT NULL) THEN
1422 IF (AMS_DEBUG_HIGH_ON) THEN
1423
1424 AMS_UTILITY_PVT.debug_message('Calling Price priority');
1425 END IF;
1426 getPrioritizedProds(
1427 p_api_version_number
1428 , FND_API.G_FALSE
1429 , p_application_id
1430 , p_party_id
1431 , p_cust_account_id
1432 , p_currency_code
1433 , l_prod_lst
1434 , p_org_id
1435 , p_bus_prior
1436 , p_bus_prior_order
1437 , p_price_list_id
1438 , p_max_ret_num
1439 , x_prod_lst
1440 , x_return_status
1441 , x_msg_count
1442 , x_msg_data
1443 );
1444 ELSIF(p_bus_prior = 'RANDOM') THEN
1445 sortRandom(
1446 l_prod_lst,
1447 p_max_ret_num,
1448 x_prod_lst
1449 );
1450 ELSE
1451 x_prod_lst := l_prod_lst;
1452 END IF;
1453
1454 -- End of API body.
1455
1456
1457 IF (AMS_DEBUG_HIGH_ON) THEN
1458
1459
1460
1461
1462
1463 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelProdsForProdAndCust ends');
1464
1465
1466 END IF;
1467
1468 -- Standard call to get message count and if count is 1, get message info.
1469 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1470 p_count => x_msg_count ,
1471 p_data => x_msg_data );
1472
1473 EXCEPTION
1474 WHEN FND_API.G_EXC_ERROR THEN
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
1480 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
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
1486
1487 WHEN OTHERS THEN
1488 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1489
1490 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1491 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1492 l_api_name );
1493 END IF;
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
1498 END getRelProdsForProdAndCust;
1499
1500
1501 PROCEDURE getPrioritizedProds
1502 (p_api_version_number IN NUMBER,
1503 p_init_msg_list IN VARCHAR2,
1504 p_application_id IN NUMBER,
1505 p_party_id IN NUMBER,
1506 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
1507 p_currency_code IN VARCHAR2 := NULL,
1508 p_prod_lst IN JTF_NUMBER_TABLE,
1509 p_org_id IN NUMBER,
1510 p_bus_prior IN VARCHAR2,
1511 p_bus_prior_order IN VARCHAR2,
1512 p_price_list_id IN NUMBER := NULL,
1513 p_max_ret_num IN NUMBER := NULL,
1514 x_prod_lst OUT NOCOPY JTF_NUMBER_TABLE,
1515 x_return_status OUT NOCOPY VARCHAR2,
1516 x_msg_count OUT NOCOPY NUMBER,
1517 x_msg_data OUT NOCOPY VARCHAR2
1518 )
1519 IS
1520 l_api_name CONSTANT VARCHAR2(30) := 'getPrioritizedProds';
1521 l_api_version CONSTANT NUMBER := 1.0;
1522
1523 l_price_stmt VARCHAR2(2500) :=
1524 'SELECT TO_NUMBER(PA.product_attr_value)
1525 FROM qp_list_lines PL,
1526 qp_pricing_attributes PA
1527 WHERE PA.list_header_id = :price_list_id
1528 AND PL.list_line_id = PA.list_line_id
1529 AND PA.product_attr_value IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_in_prod_lst AS JTF_VARCHAR2_TABLE_100)) t)';
1530
1531
1532 l_return_status VARCHAR2( 10 );
1533 l_msg_count NUMBER;
1534 l_msg_data VARCHAR2( 1000 );
1535 l_items_csr prod_cursor;
1536 l_item_id NUMBER;
1537 l_order VARCHAR2(10);
1538 l_items_in_clause VARCHAR2(32760);
1539 l_index BINARY_INTEGER;
1540 ll_index BINARY_INTEGER;
1541 i PLS_INTEGER := 1;
1542 j PLS_INTEGER := 1;
1543 found BOOLEAN;
1544 p_in_prod_lst JTF_VARCHAR2_TABLE_100;
1545
1546 BEGIN
1547 -- Standard call to check for call compatibility.
1548 IF NOT FND_API.Compatible_API_Call( l_api_version,
1549 p_api_version_number,
1550 l_api_name,
1551 G_PKG_NAME )
1552 THEN
1553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1554 END IF;
1555
1556 -- Initialize message list if p_init_msg_list is set to TRUE.
1557 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1558 FND_MSG_PUB.initialize;
1559 END IF;
1560
1561 -- Initialize API return status to success
1562 x_return_status := FND_API.G_RET_STS_SUCCESS;
1563
1564 IF (AMS_DEBUG_HIGH_ON) THEN
1565 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getPrioritizedProds starts');
1566 END IF;
1567
1568 IF(p_bus_prior_order IS NULL OR p_bus_prior_order = 'ASC' OR p_bus_prior_order <> 'DESC') THEN
1569 l_order := 'ASC';
1570 ELSE
1571 l_order := 'DESC';
1572 END IF;
1573
1574 IF (AMS_DEBUG_HIGH_ON) THEN
1575 AMS_UTILITY_PVT.debug_message('Order of Sorting is : '||l_order);
1576 END IF;
1577
1578 x_prod_lst := JTF_Number_Table();
1579 p_in_prod_lst := JTF_VARCHAR2_TABLE_100();
1580
1581 IF(p_bus_prior = 'PROD_LIST_PRICE' AND p_price_list_id IS NOT NULL AND p_prod_lst.COUNT > 1) THEN
1582 IF (AMS_DEBUG_HIGH_ON) THEN
1583
1584 AMS_UTILITY_PVT.debug_message('sort Products by Product List Price in order '||l_order);
1585 END IF;
1586 -- Commented IN Clause BugFix 3776065
1587 -- l_Index := p_prod_lst.FIRST;
1588 -- FOR pNum IN 1..( p_prod_lst.COUNT - 1 ) LOOP
1589 -- l_items_in_clause := l_items_in_clause || '''' || TO_CHAR( p_prod_lst( l_index ) ) || '''' || ', ';
1590 -- l_Index := p_prod_lst.NEXT( l_index );
1591 -- END LOOP;
1592
1593 -- l_Index := p_prod_lst.LAST;
1594 -- l_items_in_clause := l_items_in_clause || '''' || TO_CHAR( p_prod_lst( l_index ) ) || '''' || ')';
1595
1596
1597 FOR j IN 1..p_prod_lst.COUNT LOOP
1598 p_in_prod_lst(j) := p_prod_lst(j);
1599 END LOOP;
1600
1601 OPEN l_items_csr FOR l_price_stmt ||
1602 ' order by PL.list_price ' ||
1603 l_order
1604 USING p_price_list_id , p_in_prod_lst;
1605
1606 -- IF (AMS_DEBUG_HIGH_ON) THEN
1607 -- AMS_UTILITY_PVT.debug_message('item list '||l_items_in_clause);
1608 -- END IF;
1609
1610 i := 1;
1611 IF p_max_ret_num IS NULL THEN
1612 -- take all returned items in output prod list
1613 LOOP
1614 FETCH l_items_csr INTO l_item_id;
1615 EXIT WHEN l_items_csr%NOTFOUND;
1616 x_prod_lst.EXTEND;
1617 x_prod_lst(i) := l_item_id;
1618 i := i + 1;
1619 END LOOP;
1620 ELSE
1621 -- takes only required no. of items in output list
1622 LOOP
1623 FETCH l_items_csr INTO l_item_id;
1624 EXIT WHEN l_items_csr%NOTFOUND;
1625 x_prod_lst.EXTEND;
1626 x_prod_lst(i) := l_item_id;
1627 i := i + 1;
1628
1629 IF i > p_max_ret_num THEN
1630 EXIT;
1631 END IF;
1632 END LOOP;
1633 END IF;
1634
1635 CLOSE l_items_csr;
1636
1637 END IF;
1638
1639 -- in the output list we might have fewer items than max
1640 -- because either
1641 -- 1. No Sorting was done
1642 -- 2. There is no Price for some items - hence we received
1643 -- fewer items than passed in.
1644
1645 i := 1;
1646 j := x_prod_lst.COUNT;
1647 IF (x_prod_lst.COUNT <> p_prod_lst.COUNT) THEN
1648 IF p_max_ret_num IS NULL THEN
1649 -- there is no max
1650 l_index := p_prod_lst.FIRST;
1651 FOR pNum IN 1..( p_prod_lst.COUNT ) LOOP
1652 l_item_id := p_prod_lst(l_index);
1653 found := FALSE;
1654
1655 ll_index := x_prod_lst.FIRST;
1656 FOR ppNum IN 1..(x_prod_lst.COUNT) LOOP
1657 IF (l_item_id = x_prod_lst(ll_index)) THEN
1658 -- item found in output list
1659 found := TRUE;
1660 EXIT;
1661 END IF;
1662 ll_Index := x_prod_lst.NEXT( ll_Index );
1663 END LOOP;
1664
1665 IF(found = FALSE) THEN
1666 -- item missing in output list, so put it there
1667 x_prod_lst.EXTEND;
1668 x_prod_lst(i) := l_item_id;
1669 i := i + 1;
1670 END IF;
1671
1672 l_index := p_prod_lst.NEXT( l_index );
1673 END LOOP;
1674 ELSIF (j < p_max_ret_num) THEN
1675 -- there is max
1676 l_index := p_prod_lst.FIRST;
1677 FOR pNum IN 1..( p_prod_lst.COUNT ) LOOP
1678 l_item_id := p_prod_lst(l_index);
1679 found := FALSE;
1680
1681 ll_index := x_prod_lst.FIRST;
1682 FOR ppNum IN 1..(x_prod_lst.COUNT) LOOP
1683 IF (l_item_id = x_prod_lst(ll_index)) THEN
1684 -- item foun din output list
1685 found := TRUE;
1686 EXIT;
1687 END IF;
1688 ll_Index := x_prod_lst.NEXT( ll_Index );
1689 END LOOP;
1690
1691 IF(found = FALSE) THEN
1692 -- item missing in output list, so put it there
1693 x_prod_lst.EXTEND;
1694 x_prod_lst(i) := l_item_id;
1695 i := i + 1;
1696 END IF;
1697
1698 IF i > p_max_ret_num THEN
1699 EXIT;
1700 END IF;
1701
1702 l_index := p_prod_lst.NEXT( l_index );
1703 END LOOP;
1704
1705 END IF;
1706 END IF;
1707
1708 IF (AMS_DEBUG_HIGH_ON) THEN
1709 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getPrioritizedProds ends');
1710 END IF;
1711
1712 -- End of API body.
1713
1714 -- Standard call to get message count and if count is 1, get message info.
1715 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1716 p_count => x_msg_count,
1717 p_data => x_msg_data);
1718
1719 EXCEPTION
1720 WHEN FND_API.G_EXC_ERROR THEN
1721 x_return_status := FND_API.G_RET_STS_ERROR;
1722 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1723 p_count => x_msg_count,
1724 p_data => x_msg_data);
1725
1726 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1728 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1729 p_count => x_msg_count,
1730 p_data => x_msg_data);
1731
1732
1733 WHEN OTHERS THEN
1734 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1735
1736 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1737 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1738 l_api_name );
1739 END IF;
1740 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1741 p_count => x_msg_count,
1742 p_data => x_msg_data);
1743
1744 END getPrioritizedProds;
1745
1746
1747 procedure loadItemDetails
1748 (p_api_version IN NUMBER,
1749 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1750 p_application_id IN NUMBER,
1751 p_party_id IN NUMBER,
1752 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
1753 p_currency_code IN VARCHAR2 := NULL,
1754 p_itmid_tbl IN JTF_NUMBER_TABLE,
1755 p_organization_id IN NUMBER,
1756 p_category_set_id IN NUMBER,
1757 p_retrieve_price IN VARCHAR2 := FND_API.G_FALSE,
1758 p_price_list_id IN NUMBER := NULL,
1759 p_price_request_type IN VARCHAR2 := NULL,
1760 p_price_event IN VARCHAR2 := NULL,
1761 x_item_csr OUT NOCOPY prod_cursor,
1762 x_category_id_csr OUT NOCOPY prod_cursor,
1763 x_listprice_tbl OUT nocopy JTF_NUMBER_TABLE,
1764 x_bestprice_tbl OUT nocopy JTF_NUMBER_TABLE,
1765 x_price_status_code_tbl OUT nocopy JTF_VARCHAR2_TABLE_100,
1766 x_price_status_text_tbl OUT nocopy JTF_VARCHAR2_TABLE_300,
1767 x_price_return_status OUT NOCOPY VARCHAR2,
1768 x_price_return_status_text OUT NOCOPY VARCHAR2,
1769 x_item_return_status OUT NOCOPY VARCHAR2,
1770 x_msg_count OUT NOCOPY NUMBER,
1771 x_msg_data OUT NOCOPY VARCHAR2
1772 )
1773 IS
1774 l_api_name CONSTANT VARCHAR2(30) := 'loadItemDetails';
1775 l_api_version CONSTANT NUMBER := 1.0;
1776
1777 cursor l_uom_csr(l_itmid NUMBER) IS
1778 select MSIV.primary_uom_code
1779 from mtl_system_items_vl MSIV
1780 where MSIV.inventory_item_id = l_itmid;
1781
1782 l_item_stmt VARCHAR2(4000) :=
1783 'SELECT MSIV.INVENTORY_ITEM_ID, MSIV.CONCATENATED_SEGMENTS,' ||
1784 ' MSIV.ORDERABLE_ON_WEB_FLAG, MSIV.PRIMARY_UNIT_OF_MEASURE,' ||
1785 ' MSIV.PRIMARY_UOM_CODE, MSIV.DESCRIPTION, MSIV.LONG_DESCRIPTION,' ||
1786 ' MSIV.MINIMUM_ORDER_QUANTITY, MSIV.MAXIMUM_ORDER_QUANTITY' ||
1787 ' FROM MTL_SYSTEM_ITEMS_VL MSIV ' ||
1788 ' WHERE MSIV.ORGANIZATION_ID = :org_id' ||
1789 ' AND MSIV.WEB_STATUS = ''PUBLISHED'''||
1790 ' AND NVL(MSIV.START_DATE_ACTIVE, SYSDATE) <= SYSDATE ' ||
1791 ' AND NVL(MSIV.END_DATE_ACTIVE, SYSDATE) >= SYSDATE ' ||
1792 ' AND MSIV.INVENTORY_ITEM_ID IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_itmid_tbl AS JTF_NUMBER_TABLE)) t)';
1793
1794 l_category_stmt VARCHAR2(4000) :=
1795 'SELECT MSIV.INVENTORY_ITEM_ID, MIC.CATEGORY_ID' ||
1796 ' FROM MTL_SYSTEM_ITEMS_VL MSIV, MTL_ITEM_CATEGORIES MIC' ||
1797 ' WHERE MSIV.ORGANIZATION_ID = :org_id' ||
1798 ' AND NVL(MSIV.START_DATE_ACTIVE, SYSDATE) <= SYSDATE' ||
1799 ' AND NVL(MSIV.END_DATE_ACTIVE, SYSDATE) >= SYSDATE' ||
1800 ' AND MSIV.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID' ||
1801 ' AND MSIV.ORGANIZATION_ID = MIC.ORGANIZATION_ID' ||
1802 ' AND MIC.CATEGORY_SET_ID = :category_set' ||
1803 ' AND MSIV.INVENTORY_ITEM_ID IN (SELECT t.COLUMN_VALUE FROM TABLE(CAST(:p_itmid_tbl AS JTF_NUMBER_TABLE)) t)';
1804
1805 i PLS_INTEGER := 1;
1806 found BOOLEAN;
1807 l_items_in_clause VARCHAR2(32760);
1808 l_itmid_tbl JTF_NUMBER_TABLE;
1809 l_index BINARY_INTEGER;
1810 l_itmid NUMBER;
1811 l_uomcode VARCHAR2(3);
1812 l_uomcode_tbl JTF_VARCHAR2_TABLE_100;
1813
1814 BEGIN
1815 -- Standard call to check for call compatibility.
1816 IF NOT FND_API.Compatible_API_Call( l_api_version,
1817 p_api_version,
1818 l_api_name,
1819 G_PKG_NAME )
1820 THEN
1821 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1822 END IF;
1823
1824 -- Initialize message list if p_init_msg_list is set to TRUE.
1825 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1826 FND_MSG_PUB.initialize;
1827 END IF;
1828
1829 -- Initialize API return status to success
1830 x_item_return_status := FND_API.G_RET_STS_SUCCESS;
1831
1832 IF (AMS_DEBUG_HIGH_ON) THEN
1833 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.loadItemDetails starts');
1834 END IF;
1835
1836
1837 IF p_itmid_tbl.COUNT = 0 THEN
1838 IF (AMS_DEBUG_HIGH_ON) THEN
1839 AMS_UTILITY_PVT.debug_message('No Products returned');
1840 END IF;
1841 IF (AMS_DEBUG_HIGH_ON) THEN
1842 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.loadItemDetails ends');
1843 END IF;
1844
1845 -- Standard call to get message count and if count is 1, get message info.
1846 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1847 p_count => x_msg_count ,
1848 p_data => x_msg_data );
1849 return;
1850 END IF;
1851 -- Commented IN Clause BugFix 3776065
1852 -- l_Index := p_itmid_tbl.FIRST;
1853 -- FOR pNum IN 1..( p_itmid_tbl.COUNT - 1 ) LOOP
1854 -- l_items_in_clause := l_items_in_clause || TO_CHAR( p_itmid_tbl( l_index ) ) || ', ';
1855 -- l_Index := p_itmid_tbl.NEXT( l_index );
1856 -- END LOOP;
1857
1858 -- l_Index := p_itmid_tbl.LAST;
1859 -- l_items_in_clause := l_items_in_clause || TO_CHAR( p_itmid_tbl( l_index ) ) || ')';
1860
1861 -- open the item cursor for return
1862 OPEN x_item_csr FOR l_item_stmt
1863 USING p_organization_id,p_itmid_tbl;
1864
1865 -- open category id cursor for return if category set id is not null
1866 IF (p_category_set_id IS NOT NULL) THEN
1867 OPEN x_category_id_csr FOR l_category_stmt
1868 USING p_organization_id, p_category_set_id,p_itmid_tbl;
1869 END IF;
1870
1871 -- calls pricing engine APIs if retrieve price is true
1872 IF FND_API.to_Boolean(p_retrieve_price) THEN
1873
1874 l_uomcode_tbl := JTF_VARCHAR2_TABLE_100();
1875
1876 FOR l_Index IN 1..p_itmid_tbl.COUNT LOOP
1877
1878 --opens uom cursor to build uom table
1879 l_itmid := p_itmid_tbl(l_Index);
1880 OPEN l_uom_csr(l_itmid);
1881 FETCH l_uom_csr INTO l_uomcode;
1882 l_uomcode_tbl.EXTEND;
1883 IF l_uom_csr%FOUND THEN
1884 l_uomcode_tbl(l_Index) := l_uomcode;
1885 ELSE
1886 l_uomcode_tbl(l_Index) := FND_API.G_MISS_CHAR;
1887 END IF;
1888 CLOSE l_uom_csr;
1889
1890 END LOOP;
1891
1892 l_itmid_tbl := p_itmid_tbl;
1893
1894 -- now prepare for the pricing call
1895 x_listprice_tbl := NULL;
1896 x_bestprice_tbl := NULL;
1897 x_price_status_code_tbl := NULL;
1898 x_price_status_text_tbl := NULL;
1899
1900 -- call to Get Prices from qp
1901 AMS_PRICE_PVT.GetPrices
1902 (p_price_list_id, p_party_id, p_cust_account_id, p_currency_code, l_itmid_tbl,
1903 l_uomcode_tbl, p_price_request_type,
1904 p_price_event, x_listprice_tbl, x_bestprice_tbl, x_price_status_code_tbl,
1905 x_price_status_text_tbl, x_price_return_status, x_price_return_status_text);
1906
1907 END IF;
1908
1909 IF (AMS_DEBUG_HIGH_ON) THEN
1910 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.loadItemDetails ends');
1911 END IF;
1912
1913 -- End of API body.
1914
1915 -- Standard call to get message count and if count is 1, get message info.
1916 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1917 p_count => x_msg_count,
1918 p_data => x_msg_data);
1919
1920 EXCEPTION
1921 WHEN FND_API.G_EXC_ERROR THEN
1922 x_item_return_status := FND_API.G_RET_STS_ERROR;
1923 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1924 p_count => x_msg_count,
1925 p_data => x_msg_data);
1926
1927 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1928 x_item_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1929 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1930 p_count => x_msg_count,
1931 p_data => x_msg_data);
1932
1933
1934 WHEN OTHERS THEN
1935 x_item_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1936
1937 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1938 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME, l_api_name );
1939 END IF;
1940
1941 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1942 p_count => x_msg_count,
1943 p_data => x_msg_data);
1944
1945 END loadItemDetails;
1946
1947 -- web lite code starts here
1948
1949
1950 PROCEDURE getRelProdsForCart
1951 (p_api_version_number IN NUMBER,
1952 p_init_msg_list IN VARCHAR2,
1953 p_application_id IN NUMBER,
1954 p_party_id IN NUMBER,
1955 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
1956 p_currency_code IN VARCHAR2 := NULL,
1957 p_quote_id IN NUMBER,
1958 p_msite_id IN NUMBER,
1959 p_top_section_id IN NUMBER,
1960 p_org_id IN NUMBER,
1961 p_rel_type_code IN VARCHAR2,
1962 p_max_ret_num IN NUMBER := NULL,
1963 x_prod_lst OUT NOCOPY JTF_NUMBER_TABLE,
1964 x_return_status OUT NOCOPY VARCHAR2,
1965 x_msg_count OUT NOCOPY NUMBER,
1966 x_msg_data OUT NOCOPY VARCHAR2
1967 )
1968 IS
1969 l_api_name CONSTANT VARCHAR2(30) := 'getRelProdsForQuoteAndCust';
1970 l_api_version CONSTANT NUMBER := 1.0;
1971 l_return_status VARCHAR2( 10 );
1972 l_msg_count NUMBER;
1973 l_msg_data VARCHAR2( 1000 );
1974 l_quote_prod_lst JTF_NUMBER_TABLE;
1975 l_prod_lst JTF_NUMBER_TABLE;
1976 l_null CHAR(1);
1977 l_incl_top_section VARCHAR2(1) := NULL;
1978 l_item_id NUMBER;
1979 l_random NUMBER;
1980 l_max NUMBER;
1981 l_limit NUMBER;
1982 l_index BINARY_INTEGER;
1983
1984
1985 BEGIN
1986
1987 -- Standard call to check for call compatibility.
1988 IF NOT FND_API.Compatible_API_Call( l_api_version,
1989 p_api_version_number,
1990 l_api_name,
1991 G_PKG_NAME )
1992 THEN
1993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1994 END IF;
1995
1996 -- Initialize message list if p_init_msg_list is set to TRUE.
1997
1998 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1999 FND_MSG_PUB.initialize;
2000 END IF;
2001
2002 -- Initialize API return status to success
2003
2004 x_return_status := FND_API.G_RET_STS_SUCCESS;
2005
2006 IF (AMS_DEBUG_HIGH_ON) THEN
2007 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelProdsForCart starts');
2008 END IF;
2009
2010
2011 -- API body
2012
2013 -- 1. Check if the relationship exists and is active
2014
2015 IF NOT Is_Relationship_Valid(p_rel_type_code) THEN
2016
2017 IF (AMS_DEBUG_HIGH_ON) THEN
2018 AMS_UTILITY_PVT.debug_message('Relationship is not valid.');
2019 END IF;
2020 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_VALID');
2021 FND_MESSAGE.Set_Token('RELATIONSHIP', p_rel_type_code);
2022 FND_MSG_PUB.Add;
2023 RAISE FND_API.G_EXC_ERROR;
2024 END IF;
2025
2026 l_max := p_max_ret_num;
2027
2028 -- 2. Collect Shopping Cart items
2029
2030 -- GetXSellForQuote Start
2031
2032 l_quote_prod_lst := JTF_NUMBER_TABLE();
2033 select inventory_item_id
2034 bulk collect into l_quote_prod_lst
2035 from aso_quote_lines_all_v
2036 where quote_header_id = p_quote_id;
2037
2038 IF (SQL%ROWCOUNT = 0) THEN
2039 x_prod_lst := JTF_Number_Table();
2040 IF (AMS_DEBUG_HIGH_ON) THEN
2041 AMS_UTILITY_PVT.debug_message('No Items found for Quote Id : '||TO_CHAR(p_quote_id));
2042 END IF;
2043
2044 ELSIF (l_quote_prod_lst.COUNT = 0) THEN
2045 x_prod_lst := JTF_Number_Table();
2046 IF (AMS_DEBUG_HIGH_ON) THEN
2047 AMS_UTILITY_PVT.debug_message('No Items found for Quote Id : '||TO_CHAR(p_quote_id));
2048 END IF;
2049 ELSE
2050 -- 3. Collect related items
2051 IF (p_msite_id IS NULL) THEN
2052 EXECUTE IMMEDIATE 'BEGIN AMS_RUNTIME_PROD_PVT.getRelatedItems( :1, :2, :3, :4, :5,
2053 :6, :7, :8, :9, :10, :11, :12 ); END;'
2054 USING IN l_api_version, IN FND_API.G_FALSE,
2055 IN p_application_id, IN l_quote_prod_lst,
2056 IN p_rel_type_code, IN p_org_id,
2057 IN l_max, IN l_null,
2058 OUT l_prod_lst,
2059 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
2060 ELSE
2061 EXECUTE IMMEDIATE 'BEGIN AMS_RUNTIME_PROD_PVT.getRelatedItems( :1, :2, :3, :4, :5,
2062 :6, :7, :8, :9, :10, :11, :12, :13, :14, :15 ); END;'
2063 USING IN l_api_version, IN FND_API.G_FALSE,
2064 IN p_application_id, IN p_msite_id,
2065 IN p_top_section_id, IN l_incl_top_section,
2066 IN l_quote_prod_lst, IN p_rel_type_code,
2067 IN p_org_id, IN l_max,
2068 IN l_null, OUT l_prod_lst,
2069 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
2070 END IF;
2071
2072
2073 -- l_index := l_prod_lst.FIRST;
2074 -- FOR pNum IN 1..l_max LOOP
2075 -- x_prod_lst := l_prod_lst( l_index );
2076 -- l_index := l_prod_lst.NEXT( l_index );
2077 -- END LOOP;
2078
2079 x_prod_lst := l_prod_lst;
2080
2081 END IF;
2082
2083 -- End of API body.
2084
2085
2086 IF (AMS_DEBUG_HIGH_ON) THEN
2087 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelProdsForQuoteAndCust ends');
2088 END IF;
2089
2090 -- Standard call to get message count and if count is 1, get message info.
2091 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2092 p_count => x_msg_count,
2093 p_data => x_msg_data);
2094
2095 EXCEPTION
2096 WHEN FND_API.G_EXC_ERROR THEN
2097 x_return_status := FND_API.G_RET_STS_ERROR;
2098 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2099 p_count => x_msg_count,
2100 p_data => x_msg_data);
2101
2102 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2104 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2105 p_count => x_msg_count,
2106 p_data => x_msg_data);
2107
2108
2109 WHEN OTHERS THEN
2110 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2111
2112 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2113 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2114 l_api_name );
2115 END IF;
2116 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2117 p_count => x_msg_count ,
2118 p_data => x_msg_data );
2119
2120 END getRelProdsForCart;
2121
2122
2123
2124 PROCEDURE getRelProdsForProd
2125 (p_api_version_number IN NUMBER,
2126 p_init_msg_list IN VARCHAR2,
2127 p_application_id IN NUMBER,
2128 p_party_id IN NUMBER,
2129 p_cust_account_id IN NUMBER := FND_API.G_MISS_NUM,
2130 p_currency_code IN VARCHAR2 := NULL,
2131 p_prod_lst IN JTF_NUMBER_TABLE,
2132 p_msite_id IN NUMBER,
2133 p_top_section_id IN NUMBER,
2134 p_org_id IN NUMBER,
2135 p_rel_type_code IN VARCHAR2,
2136 p_max_ret_num IN NUMBER := NULL,
2137 x_prod_lst OUT NOCOPY JTF_NUMBER_TABLE,
2138 x_return_status OUT NOCOPY VARCHAR2,
2139 x_msg_count OUT NOCOPY NUMBER,
2140 x_msg_data OUT NOCOPY VARCHAR2
2141 )
2142 IS
2143 l_api_name CONSTANT VARCHAR2(30) := 'getRelProdsForProd';
2144 l_api_version CONSTANT NUMBER := 1.0;
2145
2146 l_return_status VARCHAR2( 10 );
2147 l_msg_count NUMBER;
2148 l_msg_data VARCHAR2( 1000 );
2149 l_prod_lst JTF_NUMBER_TABLE;
2150 l_null CHAR(1);
2151 l_incl_top_section VARCHAR2(1) := NULL;
2152 l_item_id NUMBER;
2153 l_random NUMBER;
2154 l_max NUMBER;
2155 l_limit NUMBER;
2156 l_index BINARY_INTEGER;
2157
2158 BEGIN
2159 -- Standard call to check for call compatibility.
2160 IF NOT FND_API.Compatible_API_Call( l_api_version,
2161 p_api_version_number,
2162 l_api_name,
2163 G_PKG_NAME )
2164 THEN
2165 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2166 END IF;
2167
2168
2169 -- Initialize message list if p_init_msg_list is set to TRUE.
2170 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2171 FND_MSG_PUB.initialize;
2172 END IF;
2173
2174 -- Initialize API return status to success
2175 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
2176
2177 IF (AMS_DEBUG_HIGH_ON) THEN
2178 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelProdsForProd starts');
2179 END IF;
2180
2181 -- API body
2182 -- 1. Check if the relationship exists and is active
2183 IF NOT Is_Relationship_Valid(p_rel_type_code) THEN
2184 IF (AMS_DEBUG_HIGH_ON) THEN
2185 AMS_UTILITY_PVT.debug_message('Relationship is not valid.');
2186 END IF;
2187
2188 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_VALID');
2189 FND_MESSAGE.Set_Token('RELATIONSHIP', p_rel_type_code);
2190 FND_MSG_PUB.Add;
2191 RAISE FND_API.G_EXC_ERROR;
2192
2193 END IF;
2194
2195 l_max := p_max_ret_num;
2196
2197 -- 2. Collect related items
2198
2199 IF (p_msite_id IS NULL) THEN
2200
2201 EXECUTE IMMEDIATE 'BEGIN AMS_RUNTIME_PROD_PVT.getRelatedItems( :1, :2, :3, :4, :5,:6, :7, :8, :9, :10, :11, :12 ); END;'
2202 USING IN l_api_version, IN FND_API.G_FALSE,
2203 IN p_application_id, IN p_prod_lst,
2204 IN p_rel_type_code, IN p_org_id,
2205 IN l_max, IN l_null,
2206 OUT l_prod_lst,
2207 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
2208
2209 ELSE
2210
2211 EXECUTE IMMEDIATE 'BEGIN AMS_RUNTIME_PROD_PVT.getRelatedItems( :1, :2, :3, :4, :5, :6, :7, :8, :9, :10, :11, :12, :13, :14, :15 ); END;'
2212 USING IN l_api_version, IN FND_API.G_FALSE,
2213 IN p_application_id, IN p_msite_id,
2214 IN p_top_section_id, IN l_incl_top_section,
2215 IN p_prod_lst, IN p_rel_type_code,
2216 IN p_org_id, IN l_max,
2217 IN l_null, OUT l_prod_lst,
2218 OUT l_return_status, OUT l_msg_count, OUT l_msg_data;
2219 END IF;
2220
2221 -- l_index := l_prod_lst.FIRST;
2222 -- FOR pNum IN 1..l_max LOOP
2223 -- x_prod_lst := l_prod_lst( l_index );
2224 --l_index := l_prod_lst.NEXT( l_index );
2225 -- END LOOP;
2226
2227 x_prod_lst := l_prod_lst;
2228
2229 -- End of API body.
2230
2231
2232 IF (AMS_DEBUG_HIGH_ON) THEN
2233 AMS_UTILITY_PVT.debug_message('AMS_RUNTIME_PROD_PVT.getRelProdsForProdAndCust ends');
2234 END IF;
2235
2236 -- Standard call to get message count and if count is 1, get message info.
2237 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2238 p_count => x_msg_count ,
2239 p_data => x_msg_data );
2240
2241 EXCEPTION
2242 WHEN FND_API.G_EXC_ERROR THEN
2243 x_return_status := FND_API.G_RET_STS_ERROR;
2244 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2245 p_count => x_msg_count ,
2246 p_data => x_msg_data );
2247
2248 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2249 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2250 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2251 p_count => x_msg_count ,
2252 p_data => x_msg_data );
2253
2254
2255 WHEN OTHERS THEN
2256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2257
2258 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
2259 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
2260 l_api_name );
2261 END IF;
2262 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
2263 p_count => x_msg_count ,
2264 p_data => x_msg_data );
2265
2266
2267
2268 END getRelProdsForProd;
2269
2270
2271 -- web lite code Ends here
2272
2273 END AMS_RUNTIME_PROD_PVT;
[Home] [Help]