[Home] [Help]
PACKAGE BODY: APPS.IBE_PROD_RELATION_RULE_PVT
Source
1 PACKAGE BODY IBE_Prod_Relation_Rule_PVT AS
2 /* $Header: IBEVCRRB.pls 120.0 2005/05/30 02:53:44 appldev noship $ */
3
4
5 PROCEDURE Insert_SQL_Rule(
6 p_api_version IN NUMBER ,
7 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
8 p_commit IN VARCHAR2 := FND_API.G_FALSE,
9 x_return_status OUT NOCOPY VARCHAR2 ,
10 x_msg_count OUT NOCOPY NUMBER ,
11 x_msg_data OUT NOCOPY VARCHAR2 ,
12 p_rel_type_code IN VARCHAR2 ,
13 p_sql_statement IN VARCHAR2
14 )
15 IS
16 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_SQL_Rule';
17 L_API_VERSION CONSTANT NUMBER := 1.0;
18 l_debug VARCHAR2(1);
19
20 BEGIN
21 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
22
23 -- Standard Start of API savepoint
24 SAVEPOINT Insert_SQL_Rule_PVT;
25
26 -- Standard call to check for call compatibility.
27 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
28 p_api_version,
29 L_API_NAME,
30 G_PKG_NAME )
31 THEN
32 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
33 END IF;
34
35 -- Initialize message list if p_init_msg_list is set to TRUE.
36 IF FND_API.to_Boolean( p_init_msg_list ) THEN
37 FND_MSG_PUB.initialize;
38 END IF;
39
40 -- Initialize API return status to success
41 x_return_status := FND_API.G_RET_STS_SUCCESS;
42
43 IF (l_debug = 'Y') THEN
44 IBE_UTIL.debug('IBE_Prod_Relation_Rule_PVT.Insert_SQL_Rule(+)');
45 END IF;
46 -- API body
47 IF NOT Is_SQL_Valid( p_sql_statement ) THEN
48 IF (l_debug = 'Y') THEN
49 IBE_UTIL.debug('Invalid SQL statement');
50 END IF;
51 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_INVALID_SQL_RULE');
52 FND_MSG_PUB.Add;
53 RAISE FND_API.G_EXC_ERROR;
54 END IF;
55
56 INSERT INTO IBE_CT_RELATION_RULES(
57 relation_rule_id, object_version_number, created_by,
58 creation_date, last_updated_by, last_update_date,
59 relation_type_code, origin_object_type,
60 dest_object_type, sql_statement
61 )
62 VALUES(
63 IBE_CT_RELATION_RULES_S1.NEXTVAL, 1, L_USER_ID,
64 SYSDATE, L_USER_ID, SYSDATE,
65 p_rel_type_code, 'N',
66 'N', p_sql_statement
67 );
68
69 IF SQL%NOTFOUND THEN
70 IF (l_debug = 'Y') THEN
71 IBE_UTIL.debug('Failed to insert the SQL rule.');
72 END IF;
73 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_RULE_NOT_CREATED');
74 FND_MSG_PUB.Add;
75 RAISE FND_API.G_EXC_ERROR;
76 END IF;
77 -- End of API body.
78 IF (l_debug = 'Y') THEN
79 IBE_UTIL.debug('IBE_Prod_Relation_Rule_PVT.Insert_SQL_Rule(-)');
80 END IF;
81
82 -- Standard check of p_commit.
83 IF FND_API.To_Boolean( p_commit ) THEN
84 COMMIT WORK;
85 END IF;
86
87 -- Standard call to get message count and if count is 1, get message info.
88 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
89 p_count => x_msg_count ,
90 p_data => x_msg_data );
91
92 EXCEPTION
93 WHEN FND_API.G_EXC_ERROR THEN
94 ROLLBACK TO Insert_SQL_Rule_PVT;
95 x_return_status := FND_API.G_RET_STS_ERROR;
96 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
97 p_count => x_msg_count ,
98 p_data => x_msg_data );
99
100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
101 ROLLBACK TO Insert_SQL_Rule_PVT;
102 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
103 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
104 p_count => x_msg_count ,
105 p_data => x_msg_data );
106
107 WHEN OTHERS THEN
108 ROLLBACK TO Insert_SQL_Rule_PVT;
109 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
110
111 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
112 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
113 L_API_NAME );
114 END IF;
115
116 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
117 p_count => x_msg_count ,
118 p_data => x_msg_data );
119 END Insert_SQL_Rule;
120
121
122 PROCEDURE Insert_Mapping_Rules(
123 p_api_version IN NUMBER ,
124 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
125 p_commit IN VARCHAR2 := FND_API.G_FALSE,
126 x_return_status OUT NOCOPY VARCHAR2 ,
127 x_msg_count OUT NOCOPY NUMBER ,
128 x_msg_data OUT NOCOPY VARCHAR2 ,
129 p_rel_type_code IN VARCHAR2 ,
130 p_origin_object_type_tbl IN JTF_Varchar2_Table_100 ,
131 p_dest_object_type_tbl IN JTF_Varchar2_Table_100 ,
132 p_origin_object_id_tbl IN JTF_Number_Table ,
133 p_dest_object_id_tbl IN JTF_Number_Table ,
134 p_preview IN VARCHAR2 := FND_API.G_FALSE
135 )
136 IS
137 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Mapping_Rules';
138 L_API_VERSION CONSTANT NUMBER := 1.0;
139 i PLS_INTEGER;
140 j PLS_INTEGER;
141 l_rule_id NUMBER;
142 l_debug VARCHAR2(1);
143
144 BEGIN
145 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
146
147 -- Standard Start of API savepoint
148 SAVEPOINT Insert_Mapping_Rules_PVT;
149
150 -- Standard call to check for call compatibility.
151 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
152 p_api_version,
153 L_API_NAME,
154 G_PKG_NAME )
155 THEN
156 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
157 END IF;
158
159 -- Initialize message list if p_init_msg_list is set to TRUE.
160 IF FND_API.to_Boolean( p_init_msg_list ) THEN
161 FND_MSG_PUB.initialize;
162 END IF;
163
164 -- Initialize API return status to success
165 x_return_status := FND_API.G_RET_STS_SUCCESS;
166
167 IF (l_debug = 'Y') THEN
168 IBE_UTIL.debug('IBE_Prod_Relation_Rule_PVT.Insert_Mapping_Rules(+)');
169 END IF;
170 -- API body
171 IF FND_API.to_Boolean( p_preview ) THEN
172 IF (l_debug = 'Y') THEN
173 IBE_UTIL.debug('Inserting the mapping rules from the Preview page.');
174 END IF;
175 FOR i IN 1..p_origin_object_type_tbl.COUNT LOOP
176 BEGIN
177 INSERT INTO IBE_CT_RELATION_RULES(
178 relation_rule_id, object_version_number, created_by,
179 creation_date, last_updated_by, last_update_date,
180 relation_type_code, origin_object_type,
181 dest_object_type, origin_object_id, dest_object_id
182 )
183 VALUES(
184 ibe_ct_relation_rules_s1.nextval, 1, L_USER_ID,
185 SYSDATE, L_USER_ID, SYSDATE,
186 p_rel_type_code, p_origin_object_type_tbl(i),
187 p_dest_object_type_tbl(i), p_origin_object_id_tbl(i), p_dest_object_id_tbl(i)
188 )
189 RETURNING relation_rule_id INTO l_rule_id;
190
191 IF SQL%NOTFOUND THEN
192 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_RULE_NOT_CREATED');
193 FND_MSG_PUB.Add;
194 RAISE FND_API.G_EXC_ERROR;
195 END IF;
196 -- bug fix 3676064
197 IF (p_rel_type_code <>'AUTOPLACEMENT') THEN
198 IBE_Prod_Relation_PVT.Insert_Related_Items_Rows(
199 p_rel_type_code => p_rel_type_code ,
200 p_rel_rule_id => l_rule_id ,
201 p_origin_object_type => p_origin_object_type_tbl(i),
202 p_dest_object_type => p_dest_object_type_tbl(i) ,
203 p_origin_object_id => p_origin_object_id_tbl(i) ,
204 p_dest_object_id => p_dest_object_id_tbl(i) );
205 END If;
206 EXCEPTION
207 WHEN DUP_VAL_ON_INDEX THEN
208 NULL;
209 END;
210 END LOOP;
211 ELSE
212 IF (l_debug = 'Y') THEN
213 IBE_UTIL.debug('Inserting the mapping rules from the Create Rules page.');
214 END IF;
215 FOR i IN 1..p_origin_object_type_tbl.COUNT LOOP
216 FOR j IN 1..p_dest_object_type_tbl.COUNT LOOP
217 BEGIN
218 INSERT INTO IBE_CT_RELATION_RULES(
219 relation_rule_id, object_version_number, created_by,
220 creation_date, last_updated_by, last_update_date,
221 relation_type_code, origin_object_type,
222 dest_object_type, origin_object_id, dest_object_id
223 )
224 VALUES(
225 ibe_ct_relation_rules_s1.nextval, 1, L_USER_ID,
226 SYSDATE, L_USER_ID, SYSDATE,
227 p_rel_type_code, p_origin_object_type_tbl(i),
228 p_dest_object_type_tbl(j), p_origin_object_id_tbl(i), p_dest_object_id_tbl(j)
229 )
230 RETURNING relation_rule_id INTO l_rule_id;
231
232 IF SQL%NOTFOUND THEN
233 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_RULE_NOT_CREATED');
234 FND_MSG_PUB.Add;
235 RAISE FND_API.G_EXC_ERROR;
236 END IF;
237 -- bug fix 3676064
238 IF (p_rel_type_code <>'AUTOPLACEMENT') THEN
239 IBE_Prod_Relation_PVT.Insert_Related_Items_Rows(
240 p_rel_type_code => p_rel_type_code ,
241 p_rel_rule_id => l_rule_id ,
242 p_origin_object_type => p_origin_object_type_tbl(i),
243 p_dest_object_type => p_dest_object_type_tbl(j) ,
244 p_origin_object_id => p_origin_object_id_tbl(i) ,
245 p_dest_object_id => p_dest_object_id_tbl(j) );
246 END IF;
247 EXCEPTION
248 WHEN DUP_VAL_ON_INDEX THEN
249 NULL;
250 END;
251 END LOOP;
252 END LOOP;
253 END IF;
254 -- End of API body.
255 IF (l_debug = 'Y') THEN
256 IBE_UTIL.debug('IBE_Prod_Relation_Rule_PVT.Insert_Mapping_Rules(-)');
257 END IF;
258
259 -- Standard check of p_commit.
260 IF FND_API.To_Boolean( p_commit ) THEN
261 COMMIT WORK;
262 END IF;
263
264 -- Standard call to get message count and if count is 1, get message info.
265 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
266 p_count => x_msg_count ,
267 p_data => x_msg_data );
268
269 EXCEPTION
270 WHEN FND_API.G_EXC_ERROR THEN
271 ROLLBACK TO Insert_Mapping_Rules_PVT;
272 x_return_status := FND_API.G_RET_STS_ERROR;
273 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
274 p_count => x_msg_count ,
275 p_data => x_msg_data );
276
277 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
278 ROLLBACK TO Insert_Mapping_Rules_PVT;
279 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
280 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
281 p_count => x_msg_count ,
282 p_data => x_msg_data );
283
284 WHEN OTHERS THEN
285 ROLLBACK TO Insert_Mapping_Rules_PVT;
286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
287
288 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
289 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
290 L_API_NAME );
291 END IF;
292
293 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
294 p_count => x_msg_count ,
295 p_data => x_msg_data );
296 END Insert_Mapping_Rules;
297
298
299 PROCEDURE Update_Rule(
300 p_api_version IN NUMBER ,
301 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
302 p_commit IN VARCHAR2 := FND_API.G_FALSE,
303 x_return_status OUT NOCOPY VARCHAR2 ,
304 x_msg_count OUT NOCOPY NUMBER ,
305 x_msg_data OUT NOCOPY VARCHAR2 ,
306 p_rel_rule_id IN NUMBER ,
307 p_obj_ver_num IN NUMBER ,
308 p_sql_statement IN VARCHAR2 := NULL
309 )
310 IS
311 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Rule';
312 L_API_VERSION CONSTANT NUMBER := 1.0;
313 l_debug VARCHAR2(1);
314
315 BEGIN
316 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
317
318 -- Standard Start of API savepoint
319 SAVEPOINT Update_Rule_PVT;
320
321 -- Standard call to check for call compatibility.
322 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
323 p_api_version,
324 L_API_NAME,
325 G_PKG_NAME )
326 THEN
327 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
328 END IF;
329
330 -- Initialize message list if p_init_msg_list is set to TRUE.
331 IF FND_API.to_Boolean( p_init_msg_list ) THEN
332 FND_MSG_PUB.initialize;
333 END IF;
334
335 -- Initialize API return status to success
336 x_return_status := FND_API.G_RET_STS_SUCCESS;
337
338 IF (l_debug = 'Y') THEN
339 IBE_UTIL.debug('IBE_Prod_Relation_Rule_PVT.Update_Rule(+)');
340 END IF;
341 -- API body
342 IF p_sql_statement IS NOT NULL AND
343 NOT Is_SQL_Valid( p_sql_statement ) THEN
344 IF (l_debug = 'Y') THEN
345 IBE_UTIL.debug('Invalid SQL statement.');
346 END IF;
347 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_INVALID_SQL_RULE');
348 FND_MSG_PUB.Add;
349 RAISE FND_API.G_EXC_ERROR;
350 END IF;
351
352 UPDATE IBE_CT_RELATION_RULES
353 SET object_version_number = object_version_number + 1,
354 sql_statement = p_sql_statement
355 WHERE relation_rule_id = p_rel_rule_id
356 AND object_version_number = p_obj_ver_num;
357
358 IF SQL%NOTFOUND THEN
359 IF (l_debug = 'Y') THEN
360 IBE_UTIL.debug('Update statement failed.');
361 END IF;
362 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_RULE_NOT_UPDATED');
363 FND_MSG_PUB.Add;
364 RAISE FND_API.G_EXC_ERROR;
365 END IF;
366 -- End of API body.
367 IF (l_debug = 'Y') THEN
368 IBE_UTIL.debug('IBE_Prod_Relation_Rule_PVT.Update_Rule(-)');
369 END IF;
370
371 -- Standard check of p_commit.
372 IF FND_API.To_Boolean( p_commit ) THEN
373 COMMIT WORK;
374 END IF;
375
376 -- Standard call to get message count and if count is 1, get message info.
377 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
378 p_count => x_msg_count ,
379 p_data => x_msg_data );
380 EXCEPTION
381 WHEN FND_API.G_EXC_ERROR THEN
382 ROLLBACK TO Update_Rule_PVT;
383 x_return_status := FND_API.G_RET_STS_ERROR;
384 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
385 p_count => x_msg_count ,
386 p_data => x_msg_data );
387
388 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
389 ROLLBACK TO Update_Rule_PVT;
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
392 p_count => x_msg_count ,
393 p_data => x_msg_data );
394
395 WHEN OTHERS THEN
396 ROLLBACK TO Update_Rule_PVT;
397 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
398
399 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
400 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
401 L_API_NAME );
402 END IF;
403
404 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
405 p_count => x_msg_count ,
406 p_data => x_msg_data );
407 END Update_Rule;
408
409
410 PROCEDURE Delete_Rules(
411 p_api_version IN NUMBER ,
412 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
413 p_commit IN VARCHAR2 := FND_API.G_FALSE,
414 x_return_status OUT NOCOPY VARCHAR2 ,
415 x_msg_count OUT NOCOPY NUMBER ,
416 x_msg_data OUT NOCOPY VARCHAR2 ,
417 p_rel_rule_id_tbl IN JTF_Varchar2_Table_100 ,
418 p_obj_ver_num_tbl IN JTF_Varchar2_Table_100
419 )
420 IS
421 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Rule';
422 L_API_VERSION CONSTANT NUMBER := 1.0;
423 l_debug VARCHAR2(1);
424
425 BEGIN
426 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
427
428 -- Standard Start of API savepoint
429 SAVEPOINT Delete_Rule_PVT;
430
431 -- Standard call to check for call compatibility.
432 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
433 p_api_version,
434 L_API_NAME,
435 G_PKG_NAME )
436 THEN
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 END IF;
439
440 -- Initialize message list if p_init_msg_list is set to TRUE.
441 IF FND_API.to_Boolean( p_init_msg_list ) THEN
442 FND_MSG_PUB.initialize;
443 END IF;
444
445 -- Initialize API return status to success
446 x_return_status := FND_API.G_RET_STS_SUCCESS;
447
448 IF (l_debug = 'Y') THEN
449 IBE_UTIL.debug('IBE_Prod_Relation_Rule_PVT.Delete_Rule(+)');
450 END IF;
451 -- API body
452 IF (l_debug = 'Y') THEN
453 IBE_UTIL.debug('Deleting rows in IBE_CT_RELATION_RULES.');
454 END IF;
455 FORALL i IN p_rel_rule_id_tbl.FIRST..p_rel_rule_id_tbl.LAST
456 DELETE
457 FROM ibe_ct_relation_rules
458 WHERE relation_rule_id = p_rel_rule_id_tbl(i)
459 AND object_version_number = p_obj_ver_num_tbl(i);
460
461 IF SQL%NOTFOUND THEN
462 IF (l_debug = 'Y') THEN
463 IBE_UTIL.debug('Failed delete statement for IBE_CT_RELATION_RULES.');
464 END IF;
465 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_RULE_NOT_DELETED');
466 FND_MSG_PUB.Add;
467 RAISE FND_API.G_EXC_ERROR;
468 END IF;
469
470 IF (l_debug = 'Y') THEN
471 IBE_UTIL.debug('Deleting rows in IBE_CT_RELATED_ITEMS.');
472 END IF;
473 FORALL i IN p_rel_rule_id_tbl.FIRST..p_rel_rule_id_tbl.LAST
474 DELETE
475 FROM ibe_ct_related_items
476 WHERE relation_rule_id = p_rel_rule_id_tbl(i);
477
478 IF (l_debug = 'Y') THEN
479 IBE_UTIL.debug('Deleting rows in IBE_CT_REL_EXCLUSIONS.');
480 END IF;
481 IBE_Prod_Relation_PVT.Remove_Invalid_Exclusions();
482 -- End of API body.
483 IF (l_debug = 'Y') THEN
484 IBE_UTIL.debug('IBE_Prod_Relation_Rule_PVT.Delete_Rule(-)');
485 END IF;
486
487 -- Standard check of p_commit.
488 IF FND_API.To_Boolean( p_commit ) THEN
489 COMMIT WORK;
490 END IF;
491
492 -- Standard call to get message count and if count is 1, get message info.
493 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
494 p_count => x_msg_count ,
495 p_data => x_msg_data );
496 EXCEPTION
497 WHEN FND_API.G_EXC_ERROR THEN
498 ROLLBACK TO Delete_Rule_PVT;
499 x_return_status := FND_API.G_RET_STS_ERROR;
500 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
501 p_count => x_msg_count ,
502 p_data => x_msg_data );
503
504 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
505 ROLLBACK TO Delete_Rule_PVT;
506 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
507 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
508 p_count => x_msg_count ,
509 p_data => x_msg_data );
510
511 WHEN OTHERS THEN
512 ROLLBACK TO Delete_Rule_PVT;
513 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
514
515 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
516 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
517 L_API_NAME );
518 END IF;
519
520 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
521 p_count => x_msg_count ,
522 p_data => x_msg_data );
523 END Delete_Rules;
524
525
526 FUNCTION Get_Rule_Type(p_origin_object_type IN VARCHAR2,
527 p_dest_object_type IN VARCHAR2)
528 RETURN VARCHAR2
529 IS
530 l_rule_type_code VARCHAR2(2) := p_origin_object_type || p_dest_object_type;
531 l_rule_type VARCHAR2(50);
532 BEGIN
533 SELECT meaning
534 INTO l_rule_type
535 FROM fnd_lookups
536 WHERE lookup_type = 'IBE_REL_MAPPING_RULE_TYPES'
537 AND lookup_code = l_rule_type_code;
538
539 RETURN l_rule_type;
540 END Get_Rule_Type;
541
542
543 FUNCTION Get_Display_Name(p_object_type IN VARCHAR2,
544 p_object_id IN NUMBER)
545 RETURN VARCHAR2
546 IS
547 TYPE section_path_csr_type IS REF CURSOR;
548 l_section_path_csr section_path_csr_type;
549 l_section_id NUMBER;
550 l_section_disp_name VARCHAR2(120);
551 l_master_msite_id NUMBER;
552 l_display_name VARCHAR2(240);
553
554 BEGIN
555 IF p_object_type = 'C' THEN
556 SELECT MCV.description
557 INTO l_display_name
558 FROM mtl_categories_vl MCV
559 WHERE MCV.category_id = p_object_id;
560 ELSIF p_object_type = 'S' THEN
561 -- Get the master minisite id
562 SELECT JMB.msite_id
563 INTO l_master_msite_id
564 FROM ibe_msites_b JMB
565 WHERE JMB.master_msite_flag = 'Y' AND JMB.site_type = 'I';
566
567 -- Open a cursor that retrieves the sections path from the root section
568 -- to p_object_id's immediate parent, in the reverse order
569 OPEN l_section_path_csr FOR
570 'SELECT JDMSS.parent_section_id ' ||
571 'FROM ibe_dsp_msite_sct_sects JDMSS ' ||
572 'START WITH JDMSS.child_section_id = :section_id ' ||
573 'AND JDMSS.mini_site_id = :master_mini_site_id1 ' ||
574 'CONNECT BY JDMSS.child_section_id = PRIOR JDMSS.parent_section_id ' ||
575 'AND JDMSS.mini_site_id = :master_mini_site_id2 ' ||
576 'AND JDMSS.parent_section_id IS NOT NULL'
577 USING p_object_id, l_master_msite_id, l_master_msite_id;
578
579 -- Loop through the cursor constructing the section path string
580 LOOP
581 FETCH l_section_path_csr INTO l_section_id;
582 EXIT WHEN l_section_path_csr%NOTFOUND;
583
584 IF l_section_id IS NOT NULL THEN
585 SELECT JDSV.display_name
586 INTO l_section_disp_name
587 FROM ibe_dsp_sections_vl JDSV
588 WHERE JDSV.section_id = l_section_id;
589
590 l_display_name := l_section_disp_name || '/' || l_display_name;
591 END IF;
592 END LOOP;
593
594 CLOSE l_section_path_csr;
595
596 SELECT JDSV.display_name
597 INTO l_section_disp_name
598 FROM ibe_dsp_sections_vl JDSV
599 WHERE JDSV.section_id = p_object_id;
600
601 l_display_name := l_display_name || l_section_disp_name;
602 ELSE
603 SELECT MSIV.description
604 INTO l_display_name
605 FROM mtl_system_items_vl MSIV
606 WHERE inventory_item_id = p_object_id
607 AND organization_id = L_ORGANIZATION_ID;
608 END IF;
609
610 RETURN l_display_name;
611 END Get_Display_Name;
612
613
614 FUNCTION Is_SQL_Valid(p_sql_stmt IN VARCHAR2)
615 RETURN BOOLEAN
616 IS
617 l_cursor NUMBER;
618 l_is_valid BOOLEAN;
619 BEGIN
620 l_cursor := DBMS_SQL.Open_Cursor;
621 BEGIN
622 DBMS_SQL.Parse(l_cursor, p_sql_stmt, DBMS_SQL.NATIVE);
623 l_is_valid := TRUE;
624 EXCEPTION
625 WHEN OTHERS THEN
626 l_is_valid := FALSE;
627 END;
628
629 DBMS_SQL.Close_Cursor(l_cursor);
630 RETURN l_is_valid;
631 END Is_SQL_Valid;
632
633 END IBE_Prod_Relation_Rule_PVT;