[Home] [Help]
PACKAGE BODY: APPS.IBE_PROD_RELATION_GRP
Source
1 PACKAGE BODY IBE_Prod_Relation_GRP AS
2 /* $Header: IBEGCRLB.pls 120.1 2011/01/07 06:27:07 scnagara ship $ */
3 FUNCTION Is_Relationship_Valid(
4 p_relation_type_code VARCHAR2) RETURN BOOLEAN
5 IS
6 l_start_date DATE;
7 l_end_date DATE;
8 BEGIN
9 SELECT start_date_active, end_date_active
10 INTO l_start_date, l_end_date
11 FROM FND_LOOKUPS
12 WHERE lookup_type = 'IBE_RELATIONSHIP_TYPES'
13 AND lookup_code = p_relation_type_code
14 AND enabled_flag = 'Y';
15 -- if relationship type code is inactive, return false
16 IF NVL(l_start_date, SYSDATE) > SYSDATE
17 OR NVL(l_end_date, SYSDATE) < SYSDATE THEN
18 RETURN FALSE;
19 ELSE
20 RETURN TRUE;
21 END IF;
22 EXCEPTION
23 WHEN NO_DATA_FOUND THEN -- Error: relationship type does not exist
24 RETURN FALSE;
25 END Is_Relationship_Valid;
26 FUNCTION Get_Bind_Arg_Num(
27 p_sql_stmt IN VARCHAR2) RETURN NUMBER
28 IS
29 l_length PLS_INTEGER;
30 i PLS_INTEGER := 0;
31 l_arg_num PLS_INTEGER := 0;
32 BEGIN
33 l_length := length(p_sql_stmt);
34 WHILE i < l_length LOOP
35 i := INSTR(p_sql_stmt, ':', i + 1, 1);
36 IF i = 0 THEN
37 EXIT;
38 END IF;
39 l_arg_num := l_arg_num + 1;
40 END LOOP;
41 RETURN l_arg_num;
42 END Get_Bind_Arg_Num;
43 FUNCTION Exists_In_MTL(
44 p_relation_type_code IN VARCHAR2) RETURN BOOLEAN
45 IS
46 BEGIN
47 IF p_relation_type_code = 'RELATED' OR
48 p_relation_type_code = 'SUBSTITUTE' OR
49 p_relation_type_code = 'CROSS_SELL' OR
50 p_relation_type_code = 'UP_SELL' OR
51 p_relation_type_code = 'SERVICE' OR
52 p_relation_type_code = 'PREREQUISITE' OR
53 p_relation_type_code = 'COLLATERAL' OR
54 p_relation_type_code = 'SUPERSEDED' OR
55 p_relation_type_code = 'COMPLIMENTARY' OR
56 p_relation_type_code = 'IMPACT' OR
57 p_relation_type_code = 'CONFLICT' OR
58 p_relation_type_code = 'MANDATORY_CHARGE' OR
59 p_relation_type_code = 'OPTIONAL_CHARGE' OR
60 p_relation_type_code = 'PROMOTIONAL_UPGRADE' THEN
61 RETURN TRUE;
62 ELSE
63 RETURN FALSE;
64 END IF;
65 END Exists_In_MTL;
66 FUNCTION isBelongToMinisite(p_item_id IN NUMBER, p_msite_id IN NUMBER) RETURN BOOLEAN
67 IS
68 rowsReturned NUMBER :=0;
69 BEGIN
70 SELECT COUNT(B.SECTION_ITEM_ID) INTO rowsReturned FROM IBE_DSP_SECTION_ITEMS s, IBE_DSP_MSITE_SCT_ITEMS b
71 WHERE S.SECTION_ITEM_ID = B.SECTION_ITEM_ID AND B.MINI_SITE_ID = p_msite_id
72 AND S.INVENTORY_ITEM_ID = p_item_id
73 AND NVL(S.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
74 AND NVL(S.END_DATE_ACTIVE,SYSDATE) >= SYSDATE;
75 IF rowsReturned > 0 THEN
76 RETURN TRUE;
77 ELSE
78 RETURN FALSE;
79 END IF;
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 RETURN FALSE;
83 END isBelongToMinisite;
84 -- Start of comments
85 -- API name : Get_Related_Items
86 -- Type : Public or Group or Private.
87 -- Function : If p_preview_flag = 'T' returns items whose
88 -- web_status is 'PUBLISHED' or 'UNPUBLISHED'.
89 -- Otherwise, only returns items whose web_status
90 -- is 'PUBLISHED'.
91 -- Pre-reqs : None.
92 -- Parameters :
93 -- IN : p_api_version IN NUMBER Required
94 -- p_init_msg_list IN VARCHAR2 Optional
95 -- Default = FND_API.G_FALSE
96 -- p_msite_id IN NUMBER
97 -- p_preview_flag IN VARCHAR2
98 -- p_item_id IN NUMBER
99 -- p_rel_type_code IN VARCHAR2
100 -- p_max_ret_num IN NUMBER
101 -- Default = NULL (Return all)
102 -- p_order_by_col IN VARCHAR2
103 -- Default = NULL (No order)
104 -- p_order_by_order IN VARCHAR2
105 -- Default = G_ASCEND_ORDER (Ascending order)
106 -- p_include_self_ref IN VARCHAR2
107 -- Default = FND_API.G_FALSE (Don't include self-referrals)
108 -- p_bind_varN IN VARCHAR2
109 -- Default = NULL (Bind variables for relationship rule defined by manual SQL)
110 -- OUT : x_return_status OUT VARCHAR2(1)
111 -- x_msg_count OUT NUMBER
112 -- x_msg_data OUT VARCHAR2(2000)
113 -- x_items_tbl OUT JTF_Number_Table
114 -- Version : Current version 1.0
115 --
116 -- previous version None
117 --
118 -- Initial version 1.0
119 --
120 -- Notes : Note text
121 --
122 -- End of comments
123 PROCEDURE Get_Related_Items
124 (
125 p_api_version IN NUMBER ,
126 p_init_msg_list IN VARCHAR2 := NULL ,
127 x_return_status OUT NOCOPY VARCHAR2 ,
128 x_msg_count OUT NOCOPY NUMBER ,
129 x_msg_data OUT NOCOPY VARCHAR2 ,
130 p_msite_id IN NUMBER ,
131 p_preview_flag IN VARCHAR2 := NULL ,
132 p_item_id IN NUMBER ,
133 p_rel_type_code IN VARCHAR2 ,
134 p_org_id IN NUMBER ,
135 p_max_ret_num IN NUMBER := NULL ,
136 p_order_by_clause IN VARCHAR2 := NULL ,
137 p_include_self_ref IN VARCHAR2 := NULL ,
138 p_rule_type IN VARCHAR2 := NULL ,
139 p_bind_arg1 IN VARCHAR2 := NULL ,
140 p_bind_arg2 IN VARCHAR2 := NULL ,
141 p_bind_arg3 IN VARCHAR2 := NULL ,
142 p_bind_arg4 IN VARCHAR2 := NULL ,
143 p_bind_arg5 IN VARCHAR2 := NULL ,
144 p_bind_arg6 IN VARCHAR2 := NULL ,
145 p_bind_arg7 IN VARCHAR2 := NULL ,
146 p_bind_arg8 IN VARCHAR2 := NULL ,
147 p_bind_arg9 IN VARCHAR2 := NULL ,
148 p_bind_arg10 IN VARCHAR2 := NULL ,
149 x_items_tbl OUT NOCOPY JTF_Number_Table
150 )
151 IS
152 TYPE rel_items_csr_type IS REF CURSOR;
153 l_api_name CONSTANT VARCHAR2(30) := 'Get_Related_Items';
154 l_api_version CONSTANT NUMBER := 1.0;
155 l_stmt VARCHAR2(2000);
156 l_rel_items_csr rel_items_csr_type;
157 l_rel_item_id NUMBER;
158 l_sql_stmt VARCHAR2(2000);
159 l_bind_arg_num PLS_INTEGER;
160 l_dummy VARCHAR2(30);
161 i PLS_INTEGER := 1;
162 include_mtl BOOLEAN;
163 l_items_tbl JTF_Number_Table;
164 l_debug VARCHAR2(1);
165 l_init_msg_list VARCHAR2(5);
166 l_preview_flag VARCHAR2(5);
167 l_include_self_ref VARCHAR2(5);
168 l_rule_type VARCHAR2(10);
169
170 BEGIN
171 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
172 -- Standard call to check for call compatibility.
173 IF NOT FND_API.Compatible_API_Call( l_api_version,
174 p_api_version,
175 l_api_name,
176 G_PKG_NAME )
177 THEN
178 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
179 END IF;
180
181 IF p_init_msg_list IS NULL THEN
182 l_init_msg_list := FND_API.G_FALSE;
183 END IF;
184
185 IF p_preview_flag IS NULL THEN
186 l_preview_flag := FND_API.G_FALSE;
187 END IF;
188
189 IF p_include_self_ref IS NULL THEN
190 l_include_self_ref := FND_API.G_FALSE;
191 END IF;
192
193 IF p_rule_type IS NULL THEN
194 l_rule_type := 'MAPPING';
195 END IF;
196
197 -- Initialize message list if l_init_msg_list is set to TRUE.
198 IF FND_API.to_Boolean( l_init_msg_list ) THEN
199 FND_MSG_PUB.initialize;
200 END IF;
201 -- Initialize API return status to success
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203 -- Initialize the return value table
204 x_items_tbl := JTF_Number_Table();
205 l_items_tbl.extend();
206 l_items_tbl(1) := p_item_id;
207 IF (l_debug = 'Y') THEN
208 IBE_UTIL.debug('IBE_Prod_Relation_GRP.Get_Related_Items(+)');
209 IBE_UTIL.debug('p_org_id : p_item_id : p_rel_type_code : l_rule_type = '
210 || p_org_id || ' : ' || p_item_id || ' : '
211 || p_rel_type_code || ' : ' || l_rule_type);
212 END IF;
213
214 Get_Related_Items(p_api_version,l_init_msg_list,x_return_status,x_msg_count,
215 x_msg_data,p_msite_id,l_preview_flag, l_items_tbl, p_rel_type_code,
216 p_org_id, p_max_ret_num, p_order_by_clause, l_include_self_ref,
217 l_rule_type,p_bind_arg1, p_bind_arg2, p_bind_arg3, p_bind_arg4,
218 p_bind_arg5, p_bind_arg6, p_bind_arg7, p_bind_arg8, p_bind_arg9,
219 p_bind_arg10, x_items_tbl);
220
221 IF (l_debug = 'Y') THEN
222 IBE_UTIL.debug('IBE_Prod_Relation_GRP.Get_Related_Items(-)');
223 END IF;
224 -- Standard call to get message count and if count is 1, get message info.
225 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
226 p_count => x_msg_count ,
227 p_data => x_msg_data );
228 EXCEPTION
229 WHEN FND_API.G_EXC_ERROR THEN
230 x_return_status := FND_API.G_RET_STS_ERROR;
231 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
232 p_count => x_msg_count ,
233 p_data => x_msg_data );
234 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
235 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
236 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
237 p_count => x_msg_count ,
238 p_data => x_msg_data );
239 WHEN OTHERS THEN
240 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
241 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
242 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
243 l_api_name );
244 END IF;
245 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
246 p_count => x_msg_count ,
247 p_data => x_msg_data );
248 END Get_Related_Items;
249 -- Start of comments
250 -- API name : Get_Related_Items
251 -- Type : Public or Group or Private.
252 -- Function :
253 -- Pre-reqs : None.
254 -- Parameters :
255 -- IN : p_api_version IN NUMBER Required
256 -- p_init_msg_list IN VARCHAR2 Optional
257 -- Default = FND_API.G_FALSE
258 -- p_item_id IN NUMBER
259 -- p_rel_type_code IN VARCHAR2
260 -- p_max_ret_num IN NUMBER
261 -- Default = NULL (Return all)
262 -- p_order_by_clause IN VARCHAR2
263 -- Default = NULL (No order)
264 -- l_include_self_ref IN VARCHAR2
265 -- Default = FND_API.G_FALSE (Don't include self-referrals)
266 -- p_bind_argN IN VARCHAR2
267 -- Default = NULL (Bind arguments for relationship rule defined by manual SQL)
268 -- OUT : x_return_status OUT VARCHAR2(1)
269 -- x_msg_count OUT NUMBER
270 -- x_msg_data OUT VARCHAR2(2000)
271 -- x_items_tbl OUT JTF_Number_Table
272 -- Version : Current version 1.0
273 --
274 -- previous version None
275 --
276 -- Initial version 1.0
277 --
278 -- Notes : Note text
279 --
280 -- End of comments
281 PROCEDURE Get_Related_Items(
282 p_api_version IN NUMBER ,
283 p_init_msg_list IN VARCHAR2 := NULL ,
284 x_return_status OUT NOCOPY VARCHAR2 ,
285 x_msg_count OUT NOCOPY NUMBER ,
286 x_msg_data OUT NOCOPY VARCHAR2 ,
287 p_item_id IN NUMBER ,
288 p_rel_type_code IN VARCHAR2 ,
289 p_org_id IN NUMBER ,
290 p_max_ret_num IN NUMBER := NULL ,
291 p_order_by_clause IN VARCHAR2 := NULL ,
292 p_include_self_ref IN VARCHAR2 := NULL ,
293 p_rule_type IN VARCHAR2 := NULL ,
294 p_bind_arg1 IN VARCHAR2 := NULL ,
295 p_bind_arg2 IN VARCHAR2 := NULL ,
296 p_bind_arg3 IN VARCHAR2 := NULL ,
297 p_bind_arg4 IN VARCHAR2 := NULL ,
298 p_bind_arg5 IN VARCHAR2 := NULL ,
299 p_bind_arg6 IN VARCHAR2 := NULL ,
300 p_bind_arg7 IN VARCHAR2 := NULL ,
301 p_bind_arg8 IN VARCHAR2 := NULL ,
302 p_bind_arg9 IN VARCHAR2 := NULL ,
303 p_bind_arg10 IN VARCHAR2 := NULL ,
304 x_items_tbl OUT NOCOPY JTF_Number_Table
305 )
306 IS
307 TYPE rel_items_csr_type IS REF CURSOR;
308 l_api_name CONSTANT VARCHAR2(30) := 'Get_Related_Items';
309 l_api_version CONSTANT NUMBER := 1.0;
310 l_without_mtl_stmt CONSTANT VARCHAR2(2000) :=
311 'SELECT DISTINCT ICRI.related_item_id
312 FROM ibe_ct_related_items ICRI,
313 mtl_system_items_b MSIB
314 WHERE ICRI.relation_type_code = :rel_type_code1
315 AND ICRI.inventory_item_id = :item_id2
316 AND NOT EXISTS( SELECT NULL
317 FROM ibe_ct_rel_exclusions ICRE
318 WHERE ICRE.relation_type_code = ICRI.relation_type_code
319 AND ICRE.inventory_item_id = ICRI.inventory_item_id
320 /*Bug 2922902*/ AND ICRE.organization_id = ICRI.organization_id
321 AND ICRE.related_item_id = ICRI.related_item_id )
322 AND MSIB.organization_id = :org_id3
323 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
324 AND MSIB.inventory_item_id = ICRI.related_item_id
325 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
326 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
327 AND MSIB.web_status = ''PUBLISHED'' ';
328 l_with_mtl_stmt1 CONSTANT VARCHAR2(2000) :=
329 'SELECT DISTINCT ICRI.related_item_id
330 FROM ibe_ct_related_items ICRI,
331 mtl_system_items_b MSIB
332 WHERE ICRI.relation_type_code = :rel_type_code1
333 AND ICRI.inventory_item_id = :item_id2
334 AND MSIB.organization_id = :org_id3
335 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
336 AND MSIB.inventory_item_id = ICRI.related_item_id
337 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
338 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
339 AND MSIB.web_status = ''PUBLISHED'' ';
340 l_with_mtl_stmt2 CONSTANT VARCHAR2(2500) :=
341 'UNION ALL
342 SELECT MRI.related_item_id
343 FROM mtl_related_items MRI,
344 mtl_system_items_b MSIB
345 WHERE MRI.relationship_type_id = DECODE(:rel_type_code5, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
346 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
347 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
348 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
349 AND MRI.inventory_item_id = :item_id6
350 AND MSIB.organization_id = :org_id7
351 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
352 AND MSIB.inventory_item_id = MRI.related_item_id
353 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
354 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
355 AND MSIB.web_status = ''PUBLISHED'' ';
356 l_with_mtl_stmt3 CONSTANT VARCHAR2(2500) :=
357 'UNION ALL
358 SELECT MRI.inventory_item_id
359 FROM mtl_related_items MRI,
360 mtl_system_items_b MSIB
361 WHERE MRI.relationship_type_id = DECODE(:rel_type_code9, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
362 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
363 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
364 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
365 AND MRI.related_item_id = :item_id10
366 AND MSIB.organization_id = :org_id11
367 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
368 AND MSIB.inventory_item_id = MRI.inventory_item_id
369 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
370 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
371 AND MSIB.web_status = ''PUBLISHED''
372 AND MRI.reciprocal_flag = ''Y'' ';
373 l_with_mtl_stmt4 CONSTANT VARCHAR2(2000) :=
374 'MINUS
375 SELECT ICRE.related_item_id
376 FROM ibe_ct_rel_exclusions ICRE
377 WHERE ICRE.relation_type_code = :rel_type_code13
378 AND ICRE.inventory_item_id = :item_id14
379 AND ICRE.organization_id = :org_id15 '; --Bug 2922902
380 l_without_mtl_bulk_stmt CONSTANT VARCHAR2(2000) :=
381 'BEGIN
382 SELECT DISTINCT ICRI.related_item_id
383 BULK COLLECT INTO :items_tbl1
384 FROM ibe_ct_related_items ICRI,
385 mtl_system_items_b MSIB
386 WHERE ICRI.relation_type_code = :rel_type_code2
387 AND ICRI.inventory_item_id = :item_id3
388 AND NOT EXISTS( SELECT NULL
389 FROM ibe_ct_rel_exclusions ICRE
390 WHERE ICRE.relation_type_code = ICRI.relation_type_code
391 AND ICRE.inventory_item_id = ICRI.inventory_item_id
392 /*Bug 2922902*/ AND ICRE.organization_id = ICRI.organization_id
393 AND ICRE.related_item_id = ICRI.related_item_id )
394 AND MSIB.organization_id = :org_id4
395 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
396 AND MSIB.inventory_item_id = ICRI.related_item_id
397 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
398 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
399 AND MSIB.web_status = ''PUBLISHED'' ';
400 l_stmt VARCHAR2(2000);
401 l_rel_items_csr rel_items_csr_type;
402 l_rel_item_id NUMBER;
403 l_sql_stmt VARCHAR2(2000);
404 l_bind_arg_num PLS_INTEGER;
405 l_dummy VARCHAR2(30);
406 i PLS_INTEGER := 1;
407 include_mtl BOOLEAN;
408 l_debug VARCHAR2(1);
409 l_init_msg_list VARCHAR2(5);
410 l_include_self_ref VARCHAR2(5);
411 l_rule_type VARCHAR2(10);
412
413 BEGIN
414 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
415 -- Standard call to check for call compatibility.
416 IF NOT FND_API.Compatible_API_Call( l_api_version,
417 p_api_version,
418 l_api_name,
419 G_PKG_NAME )
420 THEN
421 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
422 END IF;
423
424 IF p_init_msg_list IS NULL THEN
425 l_init_msg_list := FND_API.G_FALSE;
426 END IF;
427
428 IF p_include_self_ref IS NULL THEN
429 l_include_self_ref := FND_API.G_FALSE;
430 END IF;
431
432 IF p_rule_type IS NULL THEN
433 l_rule_type := 'MAPPING';
434 END IF;
435
436 -- Initialize message list if p_init_msg_list is set to TRUE.
437 IF FND_API.to_Boolean( p_init_msg_list ) THEN
438 FND_MSG_PUB.initialize;
439 END IF;
440 -- Initialize API return status to success
441 x_return_status := FND_API.G_RET_STS_SUCCESS;
442 -- Initialize the return value table
443 x_items_tbl := JTF_Number_Table();
444 IF (l_debug = 'Y') THEN
445 IBE_UTIL.debug('IBE_Prod_Relation_GRP.Get_Related_Items(+)');
446 IBE_UTIL.debug('p_org_id : p_item_id : p_rel_type_code : l_rule_type = '
447 || p_org_id || ' : ' || p_item_id || ' : '
448 || p_rel_type_code || ' : ' || l_rule_type);
449 END IF;
450 -- API body
451 -- 1. Check if the relationship exists and is active
452 IF NOT Is_Relationship_Valid(p_rel_type_code) THEN
453 IF (l_debug = 'Y') THEN
454 IBE_UTIL.debug('Relationship is not valid.');
455 END IF;
456 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_VALID');
457 FND_MESSAGE.Set_Token('RELATIONSHIP', p_rel_type_code);
458 FND_MSG_PUB.Add;
459 RAISE FND_API.G_EXC_ERROR;
460 END IF;
461 IF l_rule_type = 'SQL' THEN
462 -- 2. Get the related items using the manual SQL.
463 BEGIN -- begin sub-block to handle the SELECT statement's exception
464 SELECT ICRR.sql_statement
465 INTO l_sql_stmt
466 FROM ibe_ct_relation_rules ICRR
467 WHERE ICRR.relation_type_code = p_rel_type_code
468 AND ICRR.origin_object_type = 'N'
469 AND ICRR.dest_object_type = 'N';
470 l_bind_arg_num := Get_Bind_Arg_Num( l_sql_stmt );
471 IF l_bind_arg_num = 0 THEN
472 OPEN l_rel_items_csr FOR l_sql_stmt;
473 ELSIF l_bind_arg_num = 1 THEN
474 OPEN l_rel_items_csr FOR l_sql_stmt
475 USING p_bind_arg1;
476 ELSIF l_bind_arg_num = 2 THEN
477 OPEN l_rel_items_csr FOR l_sql_stmt
478 USING p_bind_arg1, p_bind_arg2;
479 ELSIF l_bind_arg_num = 3 THEN
480 OPEN l_rel_items_csr FOR l_sql_stmt
481 USING p_bind_arg1, p_bind_arg2, p_bind_arg3;
482 ELSIF l_bind_arg_num = 4 THEN
483 OPEN l_rel_items_csr FOR l_sql_stmt
484 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
485 p_bind_arg4;
486 ELSIF l_bind_arg_num = 5 THEN
487 OPEN l_rel_items_csr FOR l_sql_stmt
488 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
489 p_bind_arg4, p_bind_arg5;
490 ELSIF l_bind_arg_num = 6 THEN
491 OPEN l_rel_items_csr FOR l_sql_stmt
492 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
493 p_bind_arg4, p_bind_arg5, p_bind_arg6;
494 ELSIF l_bind_arg_num = 7 THEN
495 OPEN l_rel_items_csr FOR l_sql_stmt
496 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
497 p_bind_arg4, p_bind_arg5, p_bind_arg6,
498 p_bind_arg7;
499 ELSIF l_bind_arg_num = 8 THEN
500 OPEN l_rel_items_csr FOR l_sql_stmt
501 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
502 p_bind_arg4, p_bind_arg5, p_bind_arg6,
503 p_bind_arg7, p_bind_arg8;
504 ELSIF l_bind_arg_num = 9 THEN
505 OPEN l_rel_items_csr FOR l_sql_stmt
506 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
507 p_bind_arg4, p_bind_arg5, p_bind_arg6,
508 p_bind_arg7, p_bind_arg8, p_bind_arg9;
509 ELSIF l_bind_arg_num = 10 THEN
510 OPEN l_rel_items_csr FOR l_sql_stmt
511 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
512 p_bind_arg4, p_bind_arg5, p_bind_arg6,
513 p_bind_arg7, p_bind_arg8, p_bind_arg9,
514 p_bind_arg10;
515 END IF;
516 /*
517 -- BULK FETCH does not work in 8.1.6. When supported in 8.2,
518 -- we should enable for performance; replace the next IF block
519 -- the following.
520 IF p_max_ret_num IS NULL THEN
521 FETCH l_rel_items_csr BULK COLLECT INTO x_items_tbl;
522 ELSE
523 LOOP
524 FETCH l_rel_items_csr INTO l_rel_item_id;
525 EXIT WHEN l_rel_items_csr%NOTFOUND;
526 x_items_tbl.EXTEND;
527 x_items_tbl(i) := l_rel_item_id;
528 i := i + 1;
529 IF i > p_max_ret_num THEN
530 EXIT;
531 END IF;
532 END LOOP;
533 END IF;
534 */
535 IF p_max_ret_num IS NULL THEN
536 LOOP
537 FETCH l_rel_items_csr INTO l_rel_item_id;
538 EXIT WHEN l_rel_items_csr%NOTFOUND;
539 x_items_tbl.EXTEND;
540 x_items_tbl(i) := l_rel_item_id;
541 i := i + 1;
542 END LOOP;
543 ELSE
544 LOOP
545 FETCH l_rel_items_csr INTO l_rel_item_id;
546 EXIT WHEN l_rel_items_csr%NOTFOUND;
547 x_items_tbl.EXTEND;
548 x_items_tbl(i) := l_rel_item_id;
549 i := i + 1;
550 IF i > p_max_ret_num THEN
551 EXIT;
552 END IF;
553 END LOOP;
554 END IF;
555 CLOSE l_rel_items_csr;
556 RETURN;
557 EXCEPTION
558 WHEN OTHERS THEN
559 IF (l_debug = 'Y') THEN
560 IBE_UTIL.debug('SQL execution caused an error.');
561 END IF;
562 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_SQL_RULE_ERROR');
563 FND_MESSAGE.Set_Token('RELATIONSHIP', p_rel_type_code);
564 FND_MSG_PUB.Add;
565 RAISE FND_API.G_EXC_ERROR;
566 END; -- end sub-block to handle the SELECT statement's exception
567 ELSE -- l_rule_type = 'MAPPING'
568 -- 3. Get the related items from ibe_ct_related_items table
569 include_mtl := Exists_In_MTL(p_rel_type_code);
570 IF (p_max_ret_num IS NULL) AND (NOT include_mtl) THEN -- Can use bulk fetching
571 IF FND_API.to_Boolean( l_include_self_ref ) THEN -- include self referral
572 IF (l_debug = 'Y') THEN
573 IBE_UTIL.debug('Mapping rule: p_max_ret_num is NULL: relationship NOT in MTL: l_include_self_ref is TRUE.');
574 END IF;
575 EXECUTE IMMEDIATE l_without_mtl_bulk_stmt ||
576 '; END;'
577 USING OUT x_items_tbl, p_rel_type_code, p_item_id, p_org_id;
578 ELSE -- exclude self referral
579 IF (l_debug = 'Y') THEN
580 IBE_UTIL.debug('Mapping rule: p_max_ret_num is NULL: relationship NOT in MTL: l_include_self_ref is FALSE.');
581 END IF;
582 EXECUTE IMMEDIATE l_without_mtl_bulk_stmt ||
583 ' AND ICRI.related_item_id <> :item_id5 ' ||
584 '; END;'
585 USING OUT x_items_tbl, p_rel_type_code, p_item_id, p_org_id, p_item_id;
586 END IF;
587 ELSE -- Cannot use bulk fetching
588 IF include_mtl THEN -- must do union with mtl_related_items
589 IF FND_API.to_Boolean( l_include_self_ref ) THEN -- include self referral
590 IF (l_debug = 'Y') THEN
591 IBE_UTIL.debug('Mapping rule: relationship in MTL: l_include_self_ref is TRUE.');
592 END IF;
593 OPEN l_rel_items_csr FOR l_with_mtl_stmt1 ||
594 l_with_mtl_stmt2 ||
595 l_with_mtl_stmt3 ||
596 l_with_mtl_stmt4
597 USING p_rel_type_code, p_item_id, p_org_id,
598 p_rel_type_code, p_item_id, p_org_id,
599 p_rel_type_code, p_item_id, p_org_id,
600 p_rel_type_code, p_item_id, p_org_id; --Bug 2922902
601 ELSE -- exclude self referral
602 IF (l_debug = 'Y') THEN
603 IBE_UTIL.debug('Mapping rule: relationship in MTL: l_include_self_ref is FALSE.');
604 END IF;
605 OPEN l_rel_items_csr FOR l_with_mtl_stmt1 ||
606 ' AND ICRI.related_item_id <> :item_id4 ' ||
607 l_with_mtl_stmt2 ||
608 ' AND MRI.related_item_id <> :item_id8 ' ||
609 l_with_mtl_stmt3 ||
610 ' AND MRI.inventory_item_id <> :item_id12 ' ||
611 l_with_mtl_stmt4
612 USING p_rel_type_code, p_item_id, p_org_id, p_item_id,
613 p_rel_type_code, p_item_id, p_org_id, p_item_id,
614 p_rel_type_code, p_item_id, p_org_id, p_item_id,
615 p_rel_type_code, p_item_id, p_org_id; --Bug 2922902
616 END IF;
617 ELSE -- don't need to do union with mtl_related_items
618 IF FND_API.to_Boolean( l_include_self_ref ) THEN -- include self referral
619 IF (l_debug = 'Y') THEN
620 IBE_UTIL.debug('Mapping rule: p_max_ret_num is NOT NULL: relationship NOT in MTL: l_include_self_ref is TRUE.');
621 END IF;
622 OPEN l_rel_items_csr FOR l_without_mtl_stmt
623 USING p_rel_type_code, p_item_id, p_org_id;
624 ELSE -- exclude self referral
625 IF (l_debug = 'Y') THEN
626 IBE_UTIL.debug('Mapping rule: p_max_ret_num is NOT NULL: relationship NOT in MTL: l_include_self_ref is FALSE.');
627 END IF;
628 OPEN l_rel_items_csr FOR l_without_mtl_stmt ||
629 ' AND ICRI.related_item_id <> :item_id4 '
630 USING p_org_id, p_rel_type_code, p_item_id, p_item_id;
631 END IF;
632 END IF;
633 IF p_max_ret_num IS NULL THEN
634 LOOP
635 FETCH l_rel_items_csr INTO l_rel_item_id;
636 EXIT WHEN l_rel_items_csr%NOTFOUND;
637 x_items_tbl.EXTEND;
638 x_items_tbl(i) := l_rel_item_id;
639 i := i + 1;
640 END LOOP;
641 ELSE
642 LOOP
643 FETCH l_rel_items_csr INTO l_rel_item_id;
644 EXIT WHEN l_rel_items_csr%NOTFOUND;
645 x_items_tbl.EXTEND;
646 x_items_tbl(i) := l_rel_item_id;
647 i := i + 1;
648 IF i > p_max_ret_num THEN
649 EXIT;
650 END IF;
651 END LOOP;
652 END IF;
653 CLOSE l_rel_items_csr;
654 END IF;
655 END IF;
656 -- End of API body.
657 IF (l_debug = 'Y') THEN
658 IBE_UTIL.debug('IBE_Prod_Relation_GRP.Get_Related_Items(-)');
659 END IF;
660 -- Standard call to get message count and if count is 1, get message info.
661 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
662 p_count => x_msg_count ,
663 p_data => x_msg_data );
664 EXCEPTION
665 WHEN FND_API.G_EXC_ERROR THEN
666 x_return_status := FND_API.G_RET_STS_ERROR;
667 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
668 p_count => x_msg_count ,
669 p_data => x_msg_data );
670 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
671 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
672 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
673 p_count => x_msg_count ,
674 p_data => x_msg_data );
675 WHEN OTHERS THEN
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
677 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
678 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
679 l_api_name );
680 END IF;
681 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
682 p_count => x_msg_count ,
683 p_data => x_msg_data );
684 END Get_Related_Items;
685 PROCEDURE Get_Related_Items
686 (
687 p_api_version IN NUMBER ,
688 p_init_msg_list IN VARCHAR2 := NULL ,
689 x_return_status OUT NOCOPY VARCHAR2 ,
690 x_msg_count OUT NOCOPY NUMBER ,
691 x_msg_data OUT NOCOPY VARCHAR2 ,
692 p_msite_id IN NUMBER ,
693 p_preview_flag IN VARCHAR2 := NULL ,
694 p_item_ids IN JTF_Number_Table ,
695 p_rel_type_code IN VARCHAR2 ,
696 p_org_id IN NUMBER ,
697 p_max_ret_num IN NUMBER := NULL ,
698 p_order_by_clause IN VARCHAR2 := NULL ,
699 p_include_self_ref IN VARCHAR2 := NULL ,
700 p_rule_type IN VARCHAR2 := NULL ,
701 p_bind_arg1 IN VARCHAR2 := NULL ,
702 p_bind_arg2 IN VARCHAR2 := NULL ,
703 p_bind_arg3 IN VARCHAR2 := NULL ,
704 p_bind_arg4 IN VARCHAR2 := NULL ,
705 p_bind_arg5 IN VARCHAR2 := NULL ,
706 p_bind_arg6 IN VARCHAR2 := NULL ,
707 p_bind_arg7 IN VARCHAR2 := NULL ,
708 p_bind_arg8 IN VARCHAR2 := NULL ,
709 p_bind_arg9 IN VARCHAR2 := NULL ,
710 p_bind_arg10 IN VARCHAR2 := NULL ,
711 x_items_tbl OUT NOCOPY JTF_Number_Table
712 )
713 IS
714 TYPE rel_items_csr_type IS REF CURSOR;
715 l_api_name CONSTANT VARCHAR2(30) := 'Get_Related_Items';
716 l_api_version CONSTANT NUMBER := 1.0;
717 l_status VARCHAR2(5);
718 l_temp VARCHAR(20000);
719 l_temp_itemids_query CONSTANT VARCHAR2(200) := ' IN (select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)';
720 l_minisite_stmt CONSTANT VARCHAR2(2000) :=' AND EXISTS (SELECT 1 FROM IBE_DSP_SECTION_ITEMS s, IBE_DSP_MSITE_SCT_ITEMS b
721 WHERE S.SECTION_ITEM_ID = B.SECTION_ITEM_ID
722 AND B.MINI_SITE_ID = :msite_id
723 AND S.INVENTORY_ITEM_ID = ICRI.related_item_id
724 AND NVL(S.START_DATE_ACTIVE,SYSDATE) <= SYSDATE
725 AND NVL(S.END_DATE_ACTIVE,SYSDATE) >= SYSDATE )';
726 l_wout_mtl_mult_stmt CONSTANT VARCHAR2(2000) :=
727 ' SELECT DISTINCT ICRI.related_item_id FROM ibe_ct_related_items ICRI, mtl_system_items_b MSIB
728 WHERE ICRI.relation_type_code = :rel_type_code1
729 AND ICRI.inventory_item_id IN
730 ( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
731 AND NOT EXISTS( SELECT NULL
732 FROM ibe_ct_rel_exclusions ICRE
733 WHERE ICRE.relation_type_code = ICRI.relation_type_code
734 AND ICRE.inventory_item_id = ICRI.inventory_item_id
735 /*Bug 2922902 */
736 AND ICRE.organization_id = ICRI.organization_id
737 AND ICRE.related_item_id = ICRI.related_item_id )
738 AND MSIB.organization_id = :org_id3
739 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
740 AND MSIB.inventory_item_id = ICRI.related_item_id
741 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
742 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
743 AND MSIB.web_status = ''PUBLISHED'' ';
744 l_with_mtl_mult_stmt1 CONSTANT VARCHAR2(2000) :=
745 'SELECT DISTINCT ICRI.related_item_id FROM ibe_ct_related_items ICRI,
746 mtl_system_items_b MSIB
747 WHERE ICRI.relation_type_code = :rel_type_code1
748 AND ICRI.inventory_item_id
749 IN ( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
750 AND MSIB.organization_id = :org_id3
751 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
752 AND MSIB.inventory_item_id = ICRI.related_item_id
753 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
754 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
755 AND MSIB.web_status = ''PUBLISHED'' ';
756
757
758 l_with_mtl_mult_stmt2 CONSTANT VARCHAR2(2500) :=
759 'UNION ALL
760 SELECT MRI.related_item_id
761 FROM mtl_related_items MRI,
762 mtl_system_items_b MSIB
763 WHERE MRI.relationship_type_id = DECODE(:rel_type_code5, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
764 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
765 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
766 ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
767 AND MRI.inventory_item_id
768 IN ( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
769 AND MSIB.organization_id = :org_id7
770 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
771 AND MSIB.inventory_item_id = MRI.related_item_id
772 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
773 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
774 AND MSIB.web_status = ''PUBLISHED'' ';
775
776
777
778 l_with_mtl_mult_stmt3 CONSTANT VARCHAR2(2500) :=
779 'UNION ALL
780 SELECT MRI.inventory_item_id
781 FROM mtl_related_items MRI,
782 mtl_system_items_b MSIB
783 WHERE MRI.relationship_type_id = DECODE(:rel_type_code9, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
784 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
785 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
786 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
787 AND MRI.related_item_id IN ( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
788 AND MSIB.organization_id = :org_id11
789 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
790 AND MSIB.inventory_item_id = MRI.inventory_item_id
791 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
792 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
793 AND MSIB.web_status = ''PUBLISHED''
794 AND MRI.reciprocal_flag = ''Y'' ';
795
796 l_with_mtl_mult_stmt4 CONSTANT VARCHAR2(2000) :=
797 'MINUS
798 SELECT ICRE.related_item_id
799 FROM ibe_ct_rel_exclusions ICRE
800 WHERE ICRE.relation_type_code = :rel_type_code13
801 AND ICRE.inventory_item_id
802 IN ( select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
803 AND ICRE.organization_id = :org_id15 '; --Bug 2922902
804 l_wout_mtl_bulk_mult_stmt CONSTANT VARCHAR2(2000) :=
805 ' BEGIN
806 SELECT DISTINCT ICRI.related_item_id
807 BULK COLLECT INTO :items_tbl1
808 FROM ibe_ct_related_items ICRI,
809 mtl_system_items_b MSIB
810 WHERE ICRI.relation_type_code = :rel_type_code2
811 AND ICRI.inventory_item_id IN (
812 select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key )
813 AND NOT EXISTS( SELECT NULL
814 FROM ibe_ct_rel_exclusions ICRE
815 WHERE ICRE.relation_type_code = ICRI.relation_type_code
816 AND ICRE.inventory_item_id = ICRI.inventory_item_id
817 /*Bug 2922902*/
818 AND ICRE.organization_id = ICRI.organization_id
819 AND ICRE.related_item_id = ICRI.related_item_id )
820 AND MSIB.organization_id = :org_id4
821 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
822 AND MSIB.inventory_item_id = ICRI.related_item_id
823 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
824 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
825 AND MSIB.web_status = ''PUBLISHED'' ';
826 l_pv_wout_mtl_mult_stmt CONSTANT VARCHAR2(2000) :=
827 ' SELECT DISTINCT ICRI.related_item_id
828 FROM ibe_ct_related_items ICRI,
829 mtl_system_items_b MSIB
830 WHERE ICRI.relation_type_code = :rel_type_code1
831 AND ICRI.inventory_item_id IN (
832 select NUM_VAL from IBE_TEMP_TABLE where key = :l_temp_key)
833 AND NOT EXISTS( SELECT NULL
834 FROM ibe_ct_rel_exclusions ICRE
835 WHERE ICRE.relation_type_code = ICRI.relation_type_code
836 AND ICRE.inventory_item_id = ICRI.inventory_item_id
837 /*Bug 2922902*/ AND ICRE.organization_id = ICRI.organization_id
838 AND ICRE.related_item_id = ICRI.related_item_id )
839 AND MSIB.organization_id = :org_id3
840 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
841 AND MSIB.inventory_item_id = ICRI.related_item_id
842 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
843 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
844 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'') ';
845 l_pv_with_mtl_mult_stmt1 CONSTANT VARCHAR2(2000) :=
846 ' SELECT DISTINCT ICRI.related_item_id
847 FROM ibe_ct_related_items ICRI,
848 mtl_system_items_b MSIB
849 WHERE ICRI.relation_type_code = :rel_type_code1
850 AND ICRI.inventory_item_id = :item_id2
851 AND MSIB.organization_id = :org_id3
852 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
853 AND MSIB.inventory_item_id = ICRI.related_item_id
854 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
855 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
856 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'') ';
857 l_pv_with_mtl_mult_stmt2 CONSTANT VARCHAR2(2500) :=
858 ' UNION ALL
859 SELECT MRI.related_item_id
860 FROM mtl_related_items MRI,
861 mtl_system_items_b MSIB
862 WHERE MRI.relationship_type_id = DECODE(:rel_type_code5, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
863 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
864 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
865 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
866 AND MRI.inventory_item_id = :item_id6
867 AND MSIB.organization_id = :org_id7
868 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
869 AND MSIB.inventory_item_id = MRI.related_item_id
870 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
871 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
872 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'') ';
873 l_pv_with_mtl_mult_stmt3 CONSTANT VARCHAR2(2500) :=
874 ' UNION ALL
875 SELECT MRI.inventory_item_id
876 FROM mtl_related_items MRI, mtl_system_items_b MSIB
877 WHERE MRI.relationship_type_id = DECODE(:rel_type_code9, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
878 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
879 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
880 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
881 AND MRI.related_item_id = :item_id10
882 AND MSIB.organization_id = :org_id11
883 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
884 AND MSIB.inventory_item_id = MRI.inventory_item_id
885 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
886 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
887 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'')
888 AND MRI.reciprocal_flag = ''Y'' ';
889 l_pv_wout_mtl_bulk_mult_stmt CONSTANT VARCHAR2(2000) :=
890 ' BEGIN
891 SELECT DISTINCT ICRI.related_item_id
892 BULK COLLECT INTO :items_tbl1
893 FROM ibe_ct_related_items ICRI,
894 mtl_system_items_b MSIB
895 WHERE ICRI.relation_type_code = :rel_type_code2
896 AND ICRI.inventory_item_id = :item_id3
897 AND NOT EXISTS( SELECT NULL
898 FROM ibe_ct_rel_exclusions ICRE
899 WHERE ICRE.relation_type_code = ICRI.relation_type_code
900 AND ICRE.inventory_item_id = ICRI.inventory_item_id
901 /*Bug 2922902*/AND ICRE.organization_id = ICRI.organization_id
902 AND ICRE.related_item_id = ICRI.related_item_id )
903 AND MSIB.organization_id = :org_id4
904 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
905 AND MSIB.inventory_item_id = ICRI.related_item_id
906 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
907 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
908 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'') ';
909 l_wout_mtl_stmt CONSTANT VARCHAR2(2000) :=
910 ' SELECT DISTINCT ICRI.related_item_id
911 FROM ibe_ct_related_items ICRI,
912 mtl_system_items_b MSIB
913 WHERE ICRI.relation_type_code = :rel_type_code1
914 AND ICRI.inventory_item_id = :item_id2
915 AND NOT EXISTS( SELECT NULL
916 FROM ibe_ct_rel_exclusions ICRE
917 WHERE ICRE.relation_type_code = ICRI.relation_type_code
918 AND ICRE.inventory_item_id = ICRI.inventory_item_id
919 AND ICRE.organization_id = ICRI.organization_id /*Bug 2922902 */
920 AND ICRE.related_item_id = ICRI.related_item_id )
921 AND MSIB.organization_id = :org_id3
922 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
923 AND MSIB.inventory_item_id = ICRI.related_item_id
924 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
925 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
926 AND MSIB.web_status = ''PUBLISHED'' ';
927 l_with_mtl_stmt1 CONSTANT VARCHAR2(2000) :=
928 ' SELECT DISTINCT ICRI.related_item_id
929 FROM ibe_ct_related_items ICRI,
930 mtl_system_items_b MSIB
931 WHERE ICRI.relation_type_code = :rel_type_code1
932 AND ICRI.inventory_item_id = :item_id2
933 AND MSIB.organization_id = :org_id3
934 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
935 AND MSIB.inventory_item_id = ICRI.related_item_id
936 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
937 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
938 AND MSIB.web_status = ''PUBLISHED'' ';
939
940 l_with_mtl_stmt2 CONSTANT VARCHAR2(2500) :=
941 ' UNION ALL
942 SELECT MRI.related_item_id
943 FROM mtl_related_items MRI,
944 mtl_system_items_b MSIB
945 WHERE MRI.relationship_type_id = DECODE(:rel_type_code5, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
946 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
947 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
948 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
949 AND MRI.inventory_item_id = :item_id6
950 AND MSIB.organization_id = :org_id7
951 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
952 AND MSIB.inventory_item_id = MRI.related_item_id
953 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
954 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
955 AND MSIB.web_status = ''PUBLISHED'' ';
956
957 l_with_mtl_stmt3 CONSTANT VARCHAR2(2500) :=
958 ' UNION ALL
959 SELECT MRI.inventory_item_id
960 FROM mtl_related_items MRI,
961 mtl_system_items_b MSIB
962 WHERE MRI.relationship_type_id = DECODE(:rel_type_code9, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
963 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
964 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
965 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
966 AND MRI.related_item_id = :item_id10
967 AND MSIB.organization_id = :org_id11
968 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
969 AND MSIB.inventory_item_id = MRI.inventory_item_id
970 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
971 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
972 AND MSIB.web_status = ''PUBLISHED''
973 AND MRI.reciprocal_flag = ''Y'' ';
974
975 l_with_mtl_stmt4 CONSTANT VARCHAR2(2000) :=
976 ' MINUS
977 SELECT ICRE.related_item_id
978 FROM ibe_ct_rel_exclusions ICRE
979 WHERE ICRE.relation_type_code = :rel_type_code13
980 AND ICRE.inventory_item_id = :item_id14
981 AND ICRE.organization_id = :org_id15 '; --Bug 2922902
982
983 l_wout_mtl_bulk_stmt CONSTANT VARCHAR2(2000) :=
984 ' BEGIN
985 SELECT DISTINCT ICRI.related_item_id
986 BULK COLLECT INTO :items_tbl1
987 FROM ibe_ct_related_items ICRI,
988 mtl_system_items_b MSIB
989 WHERE ICRI.relation_type_code = :rel_type_code2
990 AND ICRI.inventory_item_id = :item_id3
991 AND NOT EXISTS( SELECT NULL
992 FROM ibe_ct_rel_exclusions ICRE
993 WHERE ICRE.relation_type_code = ICRI.relation_type_code
994 AND ICRE.inventory_item_id = ICRI.inventory_item_id
995 AND ICRE.organization_id = ICRI.organization_id /*Bug 2922902*/
996 AND ICRE.related_item_id = ICRI.related_item_id )
997 AND MSIB.organization_id = :org_id4
998 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
999 AND MSIB.inventory_item_id = ICRI.related_item_id
1000 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
1001 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
1002 AND MSIB.web_status = ''PUBLISHED'' ';
1003
1004 l_pv_wout_mtl_stmt CONSTANT VARCHAR2(2000) :=
1005 ' SELECT DISTINCT ICRI.related_item_id
1006 FROM ibe_ct_related_items ICRI,
1007 mtl_system_items_b MSIB
1008 WHERE ICRI.relation_type_code = :rel_type_code1
1009 AND ICRI.inventory_item_id = :item_id2
1010 AND NOT EXISTS( SELECT NULL
1011 FROM ibe_ct_rel_exclusions ICRE
1012 WHERE ICRE.relation_type_code = ICRI.relation_type_code
1013 AND ICRE.inventory_item_id = ICRI.inventory_item_id
1014 AND ICRE.organization_id = ICRI.organization_id /*Bug 2922902*/
1015 AND ICRE.related_item_id = ICRI.related_item_id )
1016 AND MSIB.organization_id = :org_id3
1017 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
1018 AND MSIB.inventory_item_id = ICRI.related_item_id
1019 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
1020 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
1021 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'') ';
1022
1023 l_pv_with_mtl_stmt1 CONSTANT VARCHAR2(2000) :=
1024 ' SELECT DISTINCT ICRI.related_item_id
1025 FROM ibe_ct_related_items ICRI,
1026 mtl_system_items_b MSIB
1027 WHERE ICRI.relation_type_code = :rel_type_code1
1028 AND ICRI.inventory_item_id = :item_id2
1029 AND MSIB.organization_id = :org_id3
1030 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
1031 AND MSIB.inventory_item_id = ICRI.related_item_id
1032 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
1033 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
1034 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'') ';
1035
1036 l_pv_with_mtl_stmt2 CONSTANT VARCHAR2(2500) :=
1037 ' UNION ALL
1038 SELECT MRI.related_item_id
1039 FROM mtl_related_items MRI,
1040 mtl_system_items_b MSIB
1041 WHERE MRI.relationship_type_id = DECODE(:rel_type_code5, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
1042 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
1043 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
1044 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
1045 AND MRI.inventory_item_id = :item_id6
1046 AND MSIB.organization_id = :org_id7
1047 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
1048 AND MSIB.inventory_item_id = MRI.related_item_id
1049 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
1050 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
1051 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'') ';
1052
1053 l_pv_with_mtl_stmt3 CONSTANT VARCHAR2(2500) :=
1054 ' UNION ALL
1055 SELECT MRI.inventory_item_id
1056 FROM mtl_related_items MRI,
1057 mtl_system_items_b MSIB
1058 WHERE MRI.relationship_type_id = DECODE(:rel_type_code9, ''RELATED'', 1, ''SUBSTITUTE'', 2, ''CROSS_SELL'',
1059 3, ''UP_SELL'', 4, ''SERVICE'', 5, ''PREREQUISITE'', 6, ''COLLATERAL'',
1060 7, ''SUPERSEDED'', 8, ''COMPLIMENTARY'', 9, ''IMPACT'', 10, ''CONFLICT'',
1061 11, ''MANDATORY_CHARGE'', 12, ''OPTIONAL_CHARGE'', 13, ''PROMOTIONAL_UPGRADE'' ,14)
1062 AND MRI.related_item_id = :item_id10
1063 AND MSIB.organization_id = :org_id11
1064 AND MSIB.organization_id = MRI.organization_id --Bug 2922902
1065 AND MSIB.inventory_item_id = MRI.inventory_item_id
1066 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
1067 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
1068 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'')
1069 AND MRI.reciprocal_flag = ''Y'' ';
1070
1071 l_pv_wout_mtl_bulk_stmt CONSTANT VARCHAR2(2000) :=
1072 ' BEGIN
1073 SELECT DISTINCT ICRI.related_item_id
1074 BULK COLLECT INTO :items_tbl1
1075 FROM ibe_ct_related_items ICRI,
1076 mtl_system_items_b MSIB
1077 WHERE ICRI.relation_type_code = :rel_type_code2
1078 AND ICRI.inventory_item_id = :item_id3
1079 AND NOT EXISTS( SELECT NULL
1080 FROM ibe_ct_rel_exclusions ICRE
1081 WHERE ICRE.relation_type_code = ICRI.relation_type_code
1082 AND ICRE.inventory_item_id = ICRI.inventory_item_id
1083 AND ICRE.organization_id = ICRI.organization_id /*Bug 2922902*/
1084 AND ICRE.related_item_id = ICRI.related_item_id )
1085 AND MSIB.organization_id = :org_id4
1086 AND MSIB.organization_id = ICRI.organization_id --Bug 2922902
1087 AND MSIB.inventory_item_id = ICRI.related_item_id
1088 AND NVL(MSIB.start_date_active, SYSDATE) <= SYSDATE
1089 AND NVL(MSIB.end_date_active, SYSDATE) >= SYSDATE
1090 AND (MSIB.web_status = ''PUBLISHED'' OR MSIB.web_status = ''UNPUBLISHED'') ';
1091 l_rel_items_csr rel_items_csr_type;
1092 l_stmt VARCHAR2(2000);
1093 l_rel_item_id NUMBER;
1094 l_sql_stmt VARCHAR2(2000);
1095 l_temp_key CONSTANT VARCHAR2(20) := 'ITEMIDS_RELATED';
1096 l_bind_arg_num PLS_INTEGER;
1097 l_dummy number;
1098 i PLS_INTEGER := 1;
1099 include_mtl BOOLEAN;
1100 l_debug VARCHAR2(1);
1101 l_item_ids VARCHAR2(2000) ;
1102 x_query_string VARCHAR2(2000);
1103 rowcount NUMBER :=0;
1104 l_init_msg_list VARCHAR2(5);
1105 l_preview_flag VARCHAR2(5);
1106 l_include_self_ref VARCHAR2(5);
1107 l_rule_type VARCHAR2(10);
1108
1109 BEGIN
1110 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
1111 l_item_ids := ' ';
1112
1113 -- Standard call to check for call compatibility.
1114 IF NOT FND_API.Compatible_API_Call( l_api_version,
1115 p_api_version,
1116 l_api_name,
1117 G_PKG_NAME )
1118 THEN
1119 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1120 END IF;
1121
1122 IF p_init_msg_list IS NULL THEN
1123 l_init_msg_list := FND_API.G_FALSE;
1124 END IF;
1125
1126 IF p_preview_flag IS NULL THEN
1127 l_preview_flag := FND_API.G_FALSE;
1128 END IF;
1129
1130 IF p_include_self_ref IS NULL THEN
1131 l_include_self_ref := FND_API.G_FALSE;
1132 END IF;
1133
1134 IF p_rule_type IS NULL THEN
1135 l_rule_type := 'MAPPING';
1136 END IF;
1137
1138 -- Initialize message list if p_init_msg_list is set to TRUE.
1139 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1140 FND_MSG_PUB.initialize;
1141 END IF;
1142
1143 -- Initialize API return status to success
1144 x_return_status := FND_API.G_RET_STS_SUCCESS;
1145 -- Initialize the return value table
1146 x_items_tbl := JTF_Number_Table();
1147
1148 IF (l_debug = 'Y') THEN
1149 IBE_UTIL.debug('IBE_Prod_Relation_GRP.Get_Related_Items(+)');
1150 IBE_UTIL.debug('p_org_id : p_rel_type_code : l_rule_type = '
1151 || p_org_id || ' : '
1152 || p_rel_type_code || ' : ' || l_rule_type);
1153 END IF;
1154
1155 -- API body
1156 -- 1. Check if the relationship exists and is active
1157 IF NOT Is_Relationship_Valid(p_rel_type_code) THEN
1158 IF (l_debug = 'Y') THEN
1159 IBE_UTIL.debug('Relationship is not valid.');
1160 END IF;
1161 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_VALID');
1162 FND_MESSAGE.Set_Token('RELATIONSHIP', p_rel_type_code);
1163 FND_MSG_PUB.Add;
1164 RAISE FND_API.G_EXC_ERROR;
1165 END IF;
1166
1167 -- Populate the itemIds into a temporary table.
1168 IF (l_debug = 'Y') THEN
1169 IBE_UTIL.debug('Inserting to ibe_temp_table.');
1170 END IF;
1171
1172 FOR i in p_item_ids.FIRST .. p_item_ids.LAST
1173 LOOP
1174 IBE_UTIL.INSERT_INTO_TEMP_TABLE(p_item_ids(i), 'NUM',l_temp_key, x_query_string);
1175 END LOOP;
1176
1177 IF (l_debug = 'Y') THEN
1178 IBE_UTIL.debug('IBE_Prod_Relation_GRP.Get_Related_Items(+)');
1179 IBE_UTIL.debug('l_item_ids : ' || l_item_ids );
1180 END IF;
1181 IF l_rule_type = 'SQL' THEN
1182 -- 2. Get the related items using the manual SQL.
1183 BEGIN -- begin sub-block to handle the SELECT statement's exception
1184 SELECT ICRR.sql_statement
1185 INTO l_sql_stmt
1186 FROM ibe_ct_relation_rules ICRR
1187 WHERE ICRR.relation_type_code = p_rel_type_code
1188 AND ICRR.origin_object_type = 'N'
1189 AND ICRR.dest_object_type = 'N';
1190 l_bind_arg_num := Get_Bind_Arg_Num( l_sql_stmt );
1191 IF l_bind_arg_num = 0 THEN
1192 OPEN l_rel_items_csr FOR l_sql_stmt;
1193 ELSIF l_bind_arg_num = 1 THEN
1194 OPEN l_rel_items_csr FOR l_sql_stmt
1195 USING p_bind_arg1;
1196 ELSIF l_bind_arg_num = 2 THEN
1197 OPEN l_rel_items_csr FOR l_sql_stmt
1198 USING p_bind_arg1, p_bind_arg2;
1199 ELSIF l_bind_arg_num = 3 THEN
1200 OPEN l_rel_items_csr FOR l_sql_stmt
1201 USING p_bind_arg1, p_bind_arg2, p_bind_arg3;
1202 ELSIF l_bind_arg_num = 4 THEN
1203 OPEN l_rel_items_csr FOR l_sql_stmt
1204 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
1205 p_bind_arg4;
1206 ELSIF l_bind_arg_num = 5 THEN
1207 OPEN l_rel_items_csr FOR l_sql_stmt
1208 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
1209 p_bind_arg4, p_bind_arg5;
1210 ELSIF l_bind_arg_num = 6 THEN
1211 OPEN l_rel_items_csr FOR l_sql_stmt
1212 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
1213 p_bind_arg4, p_bind_arg5, p_bind_arg6;
1214 ELSIF l_bind_arg_num = 7 THEN
1215 OPEN l_rel_items_csr FOR l_sql_stmt
1216 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
1217 p_bind_arg4, p_bind_arg5, p_bind_arg6,
1218 p_bind_arg7;
1219 ELSIF l_bind_arg_num = 8 THEN
1220 OPEN l_rel_items_csr FOR l_sql_stmt
1221 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
1222 p_bind_arg4, p_bind_arg5, p_bind_arg6,
1223 p_bind_arg7, p_bind_arg8;
1224 ELSIF l_bind_arg_num = 9 THEN
1225 OPEN l_rel_items_csr FOR l_sql_stmt
1226 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
1227 p_bind_arg4, p_bind_arg5, p_bind_arg6,
1228 p_bind_arg7, p_bind_arg8, p_bind_arg9;
1229 ELSIF l_bind_arg_num = 10 THEN
1230 OPEN l_rel_items_csr FOR l_sql_stmt
1231 USING p_bind_arg1, p_bind_arg2, p_bind_arg3,
1232 p_bind_arg4, p_bind_arg5, p_bind_arg6,
1233 p_bind_arg7, p_bind_arg8, p_bind_arg9,
1234 p_bind_arg10;
1235 END IF;
1236 /*
1237 -- BULK FETCH does not work in 8.1.6. When supported in 8.2,
1238 -- we should enable for performance; replace the next IF block
1239 -- the following.
1240 IF p_max_ret_num IS NULL THEN
1241 FETCH l_rel_items_csr BULK COLLECT INTO x_items_tbl;
1242 ELSE
1243 LOOP
1244 FETCH l_rel_items_csr INTO l_rel_item_id;
1245 EXIT WHEN l_rel_items_csr%NOTFOUND;
1246 x_items_tbl.EXTEND;
1247 x_items_tbl(i) := l_rel_item_id;
1248 i := i + 1;
1249 IF i > p_max_ret_num THEN
1250 EXIT;
1251 END IF;
1252 END LOOP;
1253 END IF;
1254 */
1255 IF p_max_ret_num IS NULL THEN
1256 LOOP
1257 FETCH l_rel_items_csr INTO l_rel_item_id;
1258 EXIT WHEN l_rel_items_csr%NOTFOUND;
1259 IF isBelongToMinisite(l_rel_item_id,p_msite_id) THEN
1260 x_items_tbl.EXTEND;
1261 x_items_tbl(i) := l_rel_item_id;
1262 i := i + 1;
1263 END IF;
1264 END LOOP;
1265 ELSE
1266 LOOP
1267 FETCH l_rel_items_csr INTO l_rel_item_id;
1268 EXIT WHEN l_rel_items_csr%NOTFOUND;
1269 IF isBelongToMinisite(l_rel_item_id,p_msite_id) THEN
1270 x_items_tbl.EXTEND;
1271 x_items_tbl(i) := l_rel_item_id;
1272 i := i + 1;
1273 END IF;
1274 IF i > p_max_ret_num THEN
1275 EXIT;
1276 END IF;
1277 END LOOP;
1278 END IF;
1279 CLOSE l_rel_items_csr;
1280 RETURN;
1281 EXCEPTION
1282 WHEN OTHERS THEN
1283 IF (l_debug = 'Y') THEN
1284 IBE_UTIL.debug('SQL execution caused an error.');
1285 END IF;
1286 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_SQL_RULE_ERROR');
1287 FND_MESSAGE.Set_Token('RELATIONSHIP', p_rel_type_code);
1288 FND_MSG_PUB.Add;
1289 RAISE FND_API.G_EXC_ERROR;
1290 END; -- end sub-block to handle the SELECT statement's exception
1291 ELSE -- l_rule_type = 'MAPPING'
1292 -- 3. Get the related items from ibe_ct_related_items table
1293 include_mtl := Exists_In_MTL(p_rel_type_code);
1294
1295 IF (p_max_ret_num IS NULL) AND (NOT include_mtl) THEN -- Can use bulk fetching
1296
1297 IF FND_API.to_Boolean( l_include_self_ref ) THEN -- include self referral
1298 IF (l_debug = 'Y') THEN
1299 IBE_UTIL.debug('Mapping rule: p_max_ret_num is NULL: relationship NOT in MTL: l_include_self_ref is TRUE.');
1300 END IF;
1301 IF FND_API.to_Boolean(l_preview_flag) THEN --preview mode
1302 IF (p_item_ids.LAST > 1) THEN -- multiple items as input
1303 EXECUTE IMMEDIATE l_pv_wout_mtl_bulk_mult_stmt || l_minisite_stmt || '; END;'
1304 USING OUT x_items_tbl, p_rel_type_code, l_temp_key, p_org_id, p_msite_id;
1305 ELSE
1306 EXECUTE IMMEDIATE l_pv_wout_mtl_bulk_stmt || l_minisite_stmt || '; END;'
1307 USING OUT x_items_tbl, p_rel_type_code, p_item_ids(1), p_org_id, p_msite_id;
1308 END IF ;
1309 ELSE --Customer UI mode
1310 IF (p_item_ids.LAST > 1) THEN -- multiple items as input
1311 EXECUTE IMMEDIATE l_wout_mtl_bulk_mult_stmt || l_minisite_stmt || '; END;'
1312 USING OUT x_items_tbl, p_rel_type_code, l_temp_key, p_org_id, p_msite_id;
1313 ELSE
1314 EXECUTE IMMEDIATE l_wout_mtl_bulk_stmt || l_minisite_stmt || '; END;'
1315 USING OUT x_items_tbl, p_rel_type_code, p_item_ids(1), p_org_id, p_msite_id;
1316 END IF;
1317 END IF;
1318 ELSE -- exclude self referral
1319 IF (l_debug = 'Y') THEN
1320 IBE_UTIL.debug('Mapping rule: p_max_ret_num is NULL: relationship NOT in MTL: l_include_self_ref is FALSE.');
1321 END IF;
1322 IF FND_API.to_Boolean(l_preview_flag) THEN --preview mode
1323 IF (p_item_ids.LAST > 1) THEN -- multiple items
1324 EXECUTE IMMEDIATE l_pv_wout_mtl_bulk_mult_stmt || l_minisite_stmt ||
1325 ' AND ICRI.related_item_id NOT ' || l_temp_itemids_query ||
1326 '; END;'
1327 USING OUT x_items_tbl, p_rel_type_code, l_temp_key, p_org_id, p_msite_id, l_temp_key;
1328 ELSE -- single item
1329 EXECUTE IMMEDIATE l_pv_wout_mtl_bulk_stmt || l_minisite_stmt ||
1330 ' AND ICRI.related_item_id <> :item_id5 ' ||
1331 '; END;'
1332 USING OUT x_items_tbl, p_rel_type_code, p_item_ids(1), p_org_id, p_msite_id, p_item_ids(1);
1333 END IF;
1334 ELSE -- Cust UI mode
1335 IF (p_item_ids.LAST > 1) THEN --multiple items
1336 EXECUTE IMMEDIATE l_wout_mtl_bulk_mult_stmt ||
1337 ' AND ICRI.related_item_id NOT ' || l_temp_itemids_query ||
1338 l_minisite_stmt ||
1339 '; END;'
1340 USING OUT x_items_tbl, p_rel_type_code, l_temp_key, p_org_id, p_msite_id;
1341 ELSE -- single item
1342 EXECUTE IMMEDIATE l_wout_mtl_bulk_stmt ||
1343 ' AND ICRI.related_item_id <> :item_id5 ' ||
1344 l_minisite_stmt ||
1345 '; END;'
1346 USING OUT x_items_tbl, p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1), p_msite_id;
1347 END IF;
1348 END IF;
1349 END IF; -- exclude self referral
1350 ELSE -- Cannot use bulk fetching
1351
1352 IF include_mtl THEN -- must do union with mtl_related_items
1353
1354 IF FND_API.to_Boolean( l_include_self_ref ) THEN -- include self referral
1355
1356 IF (l_debug = 'Y') THEN
1357 IBE_UTIL.debug('Mapping rule: relationship in MTL: l_include_self_ref is TRUE.');
1358
1359 END IF;
1360 IF FND_API.to_Boolean(l_preview_flag) THEN -- preview mode
1361 IF (p_item_ids.LAST > 1) THEN --multiple items
1362 OPEN l_rel_items_csr FOR l_pv_with_mtl_mult_stmt1 ||
1363 l_minisite_stmt ||
1364 l_pv_with_mtl_mult_stmt2 ||
1365 l_pv_with_mtl_mult_stmt3 ||
1366 l_with_mtl_mult_stmt4
1367 USING p_rel_type_code, l_temp_key, p_org_id,
1368 p_msite_id,
1369 p_rel_type_code, l_temp_key, p_org_id,
1370 p_rel_type_code, l_temp_key, p_org_id,
1371 p_rel_type_code, l_temp_key, p_org_id; --Bug 2922902
1372 ELSE --single item
1373 OPEN l_rel_items_csr FOR l_pv_with_mtl_stmt1 ||
1374 l_minisite_stmt ||
1375 l_pv_with_mtl_stmt2 ||
1376 l_pv_with_mtl_stmt3 ||
1377 l_with_mtl_stmt4
1378 USING p_rel_type_code, p_item_ids(1), p_org_id,
1379 p_msite_id,
1380 p_rel_type_code, p_item_ids(1), p_org_id,
1381 p_rel_type_code, p_item_ids(1), p_org_id,
1382 p_rel_type_code, p_item_ids(1), p_org_id; --Bug 2922902
1383 END IF;
1384 ELSE -- Cust UI Mode
1385 IF (p_item_ids.LAST > 1) THEN --multiple items
1386
1387 OPEN l_rel_items_csr FOR l_with_mtl_mult_stmt1 ||
1388 l_minisite_stmt ||
1389 l_with_mtl_mult_stmt2 ||
1390 l_with_mtl_mult_stmt3 ||
1391 l_with_mtl_mult_stmt4
1392 USING p_rel_type_code, l_temp_key, p_org_id,
1393 p_msite_id,
1394 p_rel_type_code, l_temp_key, p_org_id,
1395 p_rel_type_code, l_temp_key, p_org_id,
1396 p_rel_type_code, l_temp_key, p_org_id; --Bug 2922902
1397 ELSE --single item
1398 OPEN l_rel_items_csr FOR l_with_mtl_stmt1 ||
1399 l_minisite_stmt ||
1400 l_with_mtl_stmt2 ||
1401 l_with_mtl_stmt3 ||
1402 l_with_mtl_stmt4
1403 USING p_rel_type_code, p_item_ids(1), p_org_id,
1404 p_msite_id,
1405 p_rel_type_code, p_item_ids(1), p_org_id,
1406 p_rel_type_code, p_item_ids(1), p_org_id,
1407 p_rel_type_code, p_item_ids(1), p_org_id; --Bug 2922902
1408 END IF;
1409 END IF;
1410 ELSE -- exclude self referral
1411
1412 IF (l_debug = 'Y') THEN
1413 IBE_UTIL.debug('Mapping rule: relationship in MTL: l_include_self_ref is FALSE.');
1414 END IF;
1415
1416 IF FND_API.to_Boolean(l_preview_flag) THEN -- preview mode
1417 IF (p_item_ids.LAST > 1) THEN --multiple items
1418 OPEN l_rel_items_csr FOR l_pv_with_mtl_mult_stmt1 ||
1419 ' AND ICRI.related_item_id NOT ' || l_temp_itemids_query ||
1420 l_minisite_stmt ||
1421 l_pv_with_mtl_mult_stmt2 ||
1422 ' AND MRI.related_item_id NOT ' || l_temp_itemids_query ||
1423 l_pv_with_mtl_mult_stmt3 ||
1424 ' AND MRI.inventory_item_id NOT ' || l_temp_itemids_query ||
1425 l_with_mtl_mult_stmt4
1426 USING p_rel_type_code, l_temp_key, p_org_id, l_temp_key,
1427 p_msite_id,
1428 p_rel_type_code, l_temp_key, p_org_id, l_temp_key,
1429 p_rel_type_code, l_temp_key, p_org_id, l_temp_key ,
1430 p_rel_type_code, l_temp_key, p_org_id; --Bug 2922902
1431 ELSE --single item
1432 OPEN l_rel_items_csr FOR l_pv_with_mtl_stmt1 ||
1433 ' AND ICRI.related_item_id <> :item_id4 ' ||
1434 l_minisite_stmt ||
1435 l_pv_with_mtl_stmt2 ||
1436 ' AND MRI.related_item_id <> :item_id8 ' ||
1437 l_pv_with_mtl_stmt3 ||
1438 ' AND MRI.inventory_item_id <> :item_id12 ' ||
1439 l_with_mtl_stmt4
1440 USING p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1),
1441 p_msite_id,
1442 p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1),
1443 p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1) ,
1444 p_rel_type_code, p_item_ids(1), p_org_id; --Bug 2922902
1445 END IF;
1446 ELSE -- Cust UI Mode
1447 SELECT COUNT(*) INTO l_dummy from ibe_temp_table;
1448 IF (l_debug = 'Y') THEN
1449 IBE_UTIL.debug('B4 QUERY '||l_dummy||'type code '||p_rel_type_code ||'key '||l_temp_key||'org id '||p_org_id||'msite id' ||p_msite_id);
1450 END IF;
1451
1452 IF (p_item_ids.LAST > 1) THEN --multiple items
1453 OPEN l_rel_items_csr FOR l_with_mtl_mult_stmt1 ||
1454 ' AND ICRI.related_item_id NOT ' || l_temp_itemids_query ||
1455 l_minisite_stmt ||
1456 l_with_mtl_mult_stmt2 ||
1457 ' AND MRI.related_item_id NOT ' || l_temp_itemids_query ||
1458 l_with_mtl_mult_stmt3 ||
1459 ' AND MRI.inventory_item_id NOT ' || l_temp_itemids_query ||
1460 l_with_mtl_mult_stmt4
1461 USING p_rel_type_code, l_temp_key, p_org_id, l_temp_key,
1462 p_msite_id ,
1463 p_rel_type_code, l_temp_key, p_org_id, l_temp_key,
1464 p_rel_type_code, l_temp_key, p_org_id, l_temp_key ,
1465 p_rel_type_code, l_temp_key, p_org_id; --Bug 2922902
1466 IF (l_debug = 'Y') THEN
1467 IBE_UTIL.debug('Mapping rule: relationship in MTL: l_include_self_ref is FALSE. finished query'|| l_rel_items_csr%ROWCOUNT);
1468 END IF;
1469
1470 ELSE --single item
1471 OPEN l_rel_items_csr FOR l_with_mtl_stmt1 ||
1472 ' AND ICRI.related_item_id <> :item_id4 ' ||
1473 l_minisite_stmt ||
1474 l_with_mtl_stmt2 ||
1475 ' AND MRI.related_item_id <> :item_id8 ' ||
1476 l_with_mtl_stmt3 ||
1477 ' AND MRI.inventory_item_id <> :item_id12 '||
1478 l_with_mtl_stmt4
1479 USING p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1),
1480 p_msite_id ,
1481 p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1),
1482 p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1) ,
1483 p_rel_type_code, p_item_ids(1), p_org_id; --Bug 2922902
1484 END IF;
1485 END IF;
1486 END IF; --exclude self- referral
1487 ELSE -- don't need to do union with mtl_related_items
1488 IF FND_API.to_Boolean( l_include_self_ref ) THEN -- include self referral
1489 IF (l_debug = 'Y') THEN
1490 IBE_UTIL.debug('Mapping rule: p_max_ret_num is NOT NULL: relationship NOT in MTL: l_include_self_ref is TRUE.');
1491 END IF;
1492
1493 IF FND_API.to_Boolean(l_preview_flag) THEN -- preview mode
1494 IF (p_item_ids.LAST > 1) THEN --multiple items
1495 OPEN l_rel_items_csr FOR l_pv_wout_mtl_mult_stmt || l_minisite_stmt
1496 USING p_rel_type_code, p_item_ids, p_org_id, p_msite_id;
1497 ELSE --single item
1498 OPEN l_rel_items_csr FOR l_pv_wout_mtl_stmt || l_minisite_stmt
1499 USING p_rel_type_code, p_item_ids(1), p_org_id, p_msite_id;
1500 END IF;
1501 ELSE -- Cust UI mode
1502 IF (p_item_ids.LAST > 1) THEN --multiple items
1503 OPEN l_rel_items_csr FOR l_wout_mtl_mult_stmt || l_minisite_stmt
1504 USING p_rel_type_code, p_item_ids, p_org_id, p_msite_id;
1505 ELSE --single item
1506 OPEN l_rel_items_csr FOR l_wout_mtl_stmt || l_minisite_stmt
1507 USING p_rel_type_code, p_item_ids(1), p_org_id, p_msite_id;
1508 END IF;
1509 END IF;
1510 ELSE -- exclude self referral
1511 IF (l_debug = 'Y') THEN
1512 IBE_UTIL.debug('Mapping rule: p_max_ret_num is NOT NULL: relationship NOT in MTL: l_include_self_ref is FALSE.');
1513 END IF;
1514
1515 IF FND_API.to_Boolean(l_preview_flag) THEN -- preview mode
1516 IF (p_item_ids.LAST > 1) THEN --multiple items
1517 OPEN l_rel_items_csr FOR l_pv_wout_mtl_mult_stmt ||
1518 ' AND ICRI.related_item_id <> :item_id4 ' || l_minisite_stmt
1519 USING p_rel_type_code, p_item_ids, p_org_id, p_item_ids, p_msite_id;
1520 ELSE --single item
1521 OPEN l_rel_items_csr FOR l_pv_wout_mtl_stmt ||
1522 ' AND ICRI.related_item_id <> :item_id4 ' || l_minisite_stmt
1523 USING p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1), p_msite_id;
1524 END IF;
1525 ELSE -- Cust UI mod
1526 IF (p_item_ids.LAST > 1) THEN --multiple items
1527 OPEN l_rel_items_csr FOR l_wout_mtl_mult_stmt ||
1528 ' AND ICRI.related_item_id <> :item_id4 ' || l_minisite_stmt
1529 USING p_rel_type_code, p_item_ids, p_org_id, p_item_ids, p_msite_id;
1530 ELSE --single item
1531 OPEN l_rel_items_csr FOR l_wout_mtl_stmt ||
1532 ' AND ICRI.related_item_id <> :item_id4 ' || l_minisite_stmt
1533 USING p_rel_type_code, p_item_ids(1), p_org_id, p_item_ids(1), p_msite_id;
1534 END IF;
1535 END IF;
1536 END IF; -- end exclude self referral
1537 END IF; --end don't need to do union with mtl_related_items
1538
1539 IF p_max_ret_num IS NULL THEN
1540 LOOP
1541 FETCH l_rel_items_csr INTO l_rel_item_id;
1542 EXIT WHEN l_rel_items_csr%NOTFOUND;
1543 x_items_tbl.EXTEND;
1544 x_items_tbl(i) := l_rel_item_id;
1545 i := i + 1;
1546 END LOOP;
1547 ELSE
1548 LOOP
1549 FETCH l_rel_items_csr INTO l_rel_item_id;
1550 EXIT WHEN l_rel_items_csr%NOTFOUND;
1551 x_items_tbl.EXTEND;
1552 x_items_tbl(i) := l_rel_item_id;
1553 i := i + 1;
1554 IF i > p_max_ret_num THEN
1555 EXIT;
1556 END IF;
1557 END LOOP;
1558 END IF;
1559 CLOSE l_rel_items_csr;
1560 END IF;
1561 END IF;
1562
1563 --Remove the inserted ids from the temp table;
1564 l_status := IBE_UTIL.delete_from_temp_table(l_temp_key);
1565 -- End of API body.
1566 IF (l_debug = 'Y') THEN
1567 IBE_UTIL.debug('IBE_Prod_Relation_GRP.Get_Related_Items(-)');
1568 END IF;
1569 -- Standard call to get message count and if count is 1, get message info.
1570 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1571 p_count => x_msg_count ,
1572 p_data => x_msg_data );
1573 EXCEPTION
1574 WHEN FND_API.G_EXC_ERROR THEN
1575 x_return_status := FND_API.G_RET_STS_ERROR;
1576 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1577 p_count => x_msg_count ,
1578 p_data => x_msg_data );
1579 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1580 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1581 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1582 p_count => x_msg_count ,
1583 p_data => x_msg_data );
1584 WHEN OTHERS THEN
1585 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1586 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
1587 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
1588 l_api_name );
1589 END IF;
1590 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
1591 p_count => x_msg_count ,
1592 p_data => x_msg_data );
1593 END Get_Related_Items;
1594 END IBE_Prod_Relation_GRP;