[Home] [Help]
PACKAGE BODY: APPS.IBE_PROD_RELATION_PVT
Source
1 PACKAGE BODY IBE_Prod_Relation_PVT AS
2 /* $Header: IBEVCRLB.pls 120.0 2005/05/30 02:21:45 appldev noship $ */
3
4
5 PROCEDURE Insert_Relationship(
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_description IN VARCHAR2 := NULL ,
14 p_start_date_active IN DATE := NULL ,
15 p_end_date_active IN DATE := NULL
16 )
17 IS
18 L_API_NAME CONSTANT VARCHAR2(30) := 'Insert_Relationship';
19 L_API_VERSION CONSTANT NUMBER := 1.0;
20 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
21 l_rowid VARCHAR2(30);
22 l_debug VARCHAR2(1);
23
24 BEGIN
25 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
26
27 -- Standard Start of API savepoint
28 SAVEPOINT Insert_Relationship_PVT;
29
30 -- Standard call to check for call compatibility.
31 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
32 p_api_version,
33 L_API_NAME,
34 G_PKG_NAME )
35 THEN
36 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
37 END IF;
38
39 -- Initialize message list if p_init_msg_list is set to TRUE.
40 IF FND_API.to_Boolean( p_init_msg_list ) THEN
41 FND_MSG_PUB.initialize;
42 END IF;
43
44 -- Initialize API return status to success
45 x_return_status := FND_API.G_RET_STS_SUCCESS;
46
47 IF (l_debug = 'Y') THEN
48 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Insert_Relationship(+)');
49 END IF;
50 -- API body
51 BEGIN
52
53 fnd_lookup_values_pkg.insert_row
54 (
55 X_ROWID => l_rowid,
56 X_LOOKUP_TYPE => 'IBE_RELATIONSHIP_TYPES',
57 X_VIEW_APPLICATION_ID => l_view_application_id,
58 X_LOOKUP_CODE => p_rel_type_code,
59 X_TAG => NULL,
60 X_ATTRIBUTE_CATEGORY => NULL,
61 X_ATTRIBUTE1 => NULL,
62 X_ATTRIBUTE2 => NULL,
63 X_ATTRIBUTE3 => NULL,
64 X_ATTRIBUTE4 => NULL,
65 X_ENABLED_FLAG => 'Y',
66 X_START_DATE_ACTIVE => p_start_date_active,
67 X_END_DATE_ACTIVE => p_end_date_active,
68 X_TERRITORY_CODE => NULL,
69 X_ATTRIBUTE5 => NULL,
70 X_ATTRIBUTE6 => NULL,
71 X_ATTRIBUTE7 => NULL,
72 X_ATTRIBUTE8 => NULL,
73 X_ATTRIBUTE9 => NULL,
74 X_ATTRIBUTE10 => NULL,
75 X_ATTRIBUTE11 => NULL,
76 X_ATTRIBUTE12 => NULL,
77 X_ATTRIBUTE13 => NULL,
78 X_ATTRIBUTE14 => NULL,
79 X_ATTRIBUTE15 => NULL,
80 X_MEANING => p_rel_type_code,
81 X_DESCRIPTION => p_description,
82 X_CREATION_DATE => sysdate,
83 X_CREATED_BY => l_user_id,
84 X_LAST_UPDATE_DATE => sysdate,
85 X_LAST_UPDATED_BY => l_user_id,
86 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
87 );
88
89 EXCEPTION
90 WHEN NO_DATA_FOUND THEN
91 IF (l_debug = 'Y') THEN
92 IBE_UTIL.debug('Insert statement failed.');
93 END IF;
94 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_CREATED');
95 FND_MSG_PUB.Add;
96 RAISE FND_API.G_EXC_ERROR;
97 WHEN OTHERS THEN
98 IF (l_debug = 'Y') THEN
99 IBE_UTIL.debug('Insert statement failed.');
100 END IF;
101 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
102 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
103 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
104 FND_MESSAGE.Set_Token('REASON', SQLERRM);
105 FND_MSG_PUB.Add;
106
107 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_CREATED');
108 FND_MSG_PUB.Add;
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END;
111
112 -- End of API body.
113 IF (l_debug = 'Y') THEN
114 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Insert_Relationship(-)');
115 END IF;
116
117 -- Standard check of p_commit.
118 IF FND_API.To_Boolean( p_commit ) THEN
119 COMMIT WORK;
120 END IF;
121
122 -- Standard call to get message count and if count is 1, get message info.
123 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
124 p_count => x_msg_count ,
125 p_data => x_msg_data );
126
127 EXCEPTION
128 WHEN FND_API.G_EXC_ERROR THEN
129 ROLLBACK TO Insert_Relationship_PVT;
130 x_return_status := FND_API.G_RET_STS_ERROR;
131 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
132 p_count => x_msg_count ,
133 p_data => x_msg_data );
134
135 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
136 ROLLBACK TO Insert_Relationship_PVT;
137 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
138 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
139 p_count => x_msg_count ,
140 p_data => x_msg_data );
141
142 WHEN OTHERS THEN
143 ROLLBACK TO Insert_Relationship_PVT;
144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
145
146 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
147 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
148 L_API_NAME );
149 END IF;
150 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
151 p_count => x_msg_count ,
152 p_data => x_msg_data );
153 END Insert_Relationship;
154
155
156 PROCEDURE Update_Relationship(
157 p_api_version IN NUMBER ,
158 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
159 p_commit IN VARCHAR2 := FND_API.G_FALSE,
160 x_return_status OUT NOCOPY VARCHAR2 ,
161 x_msg_count OUT NOCOPY NUMBER ,
162 x_msg_data OUT NOCOPY VARCHAR2 ,
163 p_rel_type_code IN VARCHAR2 ,
164 p_description IN VARCHAR2 ,
165 p_start_date IN DATE ,
166 p_end_date IN DATE
167 )
168 IS
169 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Relationship';
170 L_API_VERSION CONSTANT NUMBER := 1.0;
171 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
172 i PLS_INTEGER;
173 l_debug VARCHAR2(1);
174
175 BEGIN
176 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
177
178 -- Standard Start of API savepoint
179 SAVEPOINT Update_Relationship_PVT;
180
181 -- Standard call to check for call compatibility.
182 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
183 p_api_version,
184 L_API_NAME,
185 G_PKG_NAME )
186 THEN
187 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
188 END IF;
189
190 -- Initialize message list if p_init_msg_list is set to TRUE.
191 IF FND_API.to_Boolean( p_init_msg_list ) THEN
192 FND_MSG_PUB.initialize;
193 END IF;
194
195 -- Initialize API return status to success
196 x_return_status := FND_API.G_RET_STS_SUCCESS;
197
198 IF (l_debug = 'Y') THEN
199 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Update_Relationship(+)');
200 END IF;
201 -- API body
202 BEGIN
203 fnd_lookup_values_pkg.update_row
204 (
205 X_LOOKUP_TYPE => 'IBE_RELATIONSHIP_TYPES',
206 X_VIEW_APPLICATION_ID => l_view_application_id,
207 X_LOOKUP_CODE => p_rel_type_code,
208 X_TAG => NULL,
209 X_ATTRIBUTE_CATEGORY => NULL,
210 X_ATTRIBUTE1 => NULL,
211 X_ATTRIBUTE2 => NULL,
212 X_ATTRIBUTE3 => NULL,
213 X_ATTRIBUTE4 => NULL,
214 X_ENABLED_FLAG => 'Y',
215 X_START_DATE_ACTIVE => p_start_date,
216 X_END_DATE_ACTIVE => p_end_date,
217 X_TERRITORY_CODE => NULL,
218 X_ATTRIBUTE5 => NULL,
219 X_ATTRIBUTE6 => NULL,
220 X_ATTRIBUTE7 => NULL,
221 X_ATTRIBUTE8 => NULL,
222 X_ATTRIBUTE9 => NULL,
223 X_ATTRIBUTE10 => NULL,
224 X_ATTRIBUTE11 => NULL,
225 X_ATTRIBUTE12 => NULL,
226 X_ATTRIBUTE13 => NULL,
227 X_ATTRIBUTE14 => NULL,
228 X_ATTRIBUTE15 => NULL,
229 X_MEANING => p_rel_type_code,
230 X_DESCRIPTION => p_description,
231 X_LAST_UPDATE_DATE => sysdate,
232 X_LAST_UPDATED_BY => l_user_id,
233 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
234 );
235 EXCEPTION
236 WHEN NO_DATA_FOUND THEN
237 IF (l_debug = 'Y') THEN
238 IBE_UTIL.debug('Update statement failed.');
239 END IF;
240 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_EXIST');
241 FND_MSG_PUB.Add;
242 RAISE FND_API.G_EXC_ERROR;
243 WHEN OTHERS THEN
244 IF (l_debug = 'Y') THEN
245 IBE_UTIL.debug('Update statement failed.');
246 END IF;
247 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
248 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
249 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
250 FND_MESSAGE.Set_Token('REASON', SQLERRM);
251 FND_MSG_PUB.Add;
252
253 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_EXIST');
254 FND_MSG_PUB.Add;
255 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
256 END;
257
258 -- End of API body.
259 IF (l_debug = 'Y') THEN
260 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Update_Relationship(-)');
261 END IF;
262
263 -- Standard check of p_commit.
264 IF FND_API.To_Boolean( p_commit ) THEN
265 COMMIT WORK;
266 END IF;
267
268 -- Standard call to get message count and if count is 1, get message info.
269 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
270 p_count => x_msg_count ,
271 p_data => x_msg_data );
272 EXCEPTION
273 WHEN FND_API.G_EXC_ERROR THEN
274 ROLLBACK TO Update_Relationship_PVT;
275 x_return_status := FND_API.G_RET_STS_ERROR;
276 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
277 p_count => x_msg_count ,
278 p_data => x_msg_data );
279
280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
281 ROLLBACK TO Update_Relationship_PVT;
282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
283 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
284 p_count => x_msg_count ,
285 p_data => x_msg_data );
286
287 WHEN OTHERS THEN
288 ROLLBACK TO Update_Relationship_PVT;
289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
290
291 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
292 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
293 L_API_NAME );
294 END IF;
295
296 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
297 p_count => x_msg_count ,
298 p_data => x_msg_data );
299 END Update_Relationship;
300
301
302 PROCEDURE Update_Relationship_Detail(
303 p_api_version IN NUMBER ,
304 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
305 p_commit IN VARCHAR2 := FND_API.G_FALSE,
306 x_return_status OUT NOCOPY VARCHAR2 ,
307 x_msg_count OUT NOCOPY NUMBER ,
308 x_msg_data OUT NOCOPY VARCHAR2 ,
309 p_rel_type_code IN VARCHAR2 ,
310 p_meaning IN VARCHAR2 ,
311 p_description IN VARCHAR2 ,
312 p_start_date IN DATE ,
313 p_end_date IN DATE
314 )
315 IS
316 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Relationship_Detail';
317 L_API_VERSION CONSTANT NUMBER := 1.0;
318 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
319 i PLS_INTEGER;
320 l_debug VARCHAR2(1);
321
322 BEGIN
323 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
324
325 -- Standard Start of API savepoint
326 SAVEPOINT Update_Relationship_Detail_PVT;
327
328 -- Standard call to check for call compatibility.
329 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
330 p_api_version,
331 L_API_NAME,
332 G_PKG_NAME )
333 THEN
334 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
335 END IF;
336
337 -- Initialize message list if p_init_msg_list is set to TRUE.
338 IF FND_API.to_Boolean( p_init_msg_list ) THEN
339 FND_MSG_PUB.initialize;
340 END IF;
341
342 -- Initialize API return status to success
343 x_return_status := FND_API.G_RET_STS_SUCCESS;
344
345 IF (l_debug = 'Y') THEN
346 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Update_Relationship_Detail(+)');
347 END IF;
348 -- API body
349 BEGIN
350 fnd_lookup_values_pkg.update_row
351 (
352 X_LOOKUP_TYPE => 'IBE_RELATIONSHIP_TYPES',
353 X_VIEW_APPLICATION_ID => l_view_application_id,
354 X_LOOKUP_CODE => p_rel_type_code,
355 X_TAG => NULL,
356 X_ATTRIBUTE_CATEGORY => NULL,
357 X_ATTRIBUTE1 => NULL,
358 X_ATTRIBUTE2 => NULL,
359 X_ATTRIBUTE3 => NULL,
360 X_ATTRIBUTE4 => NULL,
361 X_ENABLED_FLAG => 'Y',
362 X_START_DATE_ACTIVE => p_start_date,
363 X_END_DATE_ACTIVE => p_end_date,
364 X_TERRITORY_CODE => NULL,
365 X_ATTRIBUTE5 => NULL,
366 X_ATTRIBUTE6 => NULL,
367 X_ATTRIBUTE7 => NULL,
368 X_ATTRIBUTE8 => NULL,
369 X_ATTRIBUTE9 => NULL,
370 X_ATTRIBUTE10 => NULL,
371 X_ATTRIBUTE11 => NULL,
372 X_ATTRIBUTE12 => NULL,
373 X_ATTRIBUTE13 => NULL,
374 X_ATTRIBUTE14 => NULL,
375 X_ATTRIBUTE15 => NULL,
376 X_MEANING => p_meaning,
377 X_DESCRIPTION => p_description,
378 X_LAST_UPDATE_DATE => sysdate,
379 X_LAST_UPDATED_BY => l_user_id,
380 X_LAST_UPDATE_LOGIN => FND_GLOBAL.login_id
381 );
382 EXCEPTION
383 WHEN NO_DATA_FOUND THEN
384 IF (l_debug = 'Y') THEN
385 IBE_UTIL.debug('Update statement failed.');
386 END IF;
387 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_EXIST');
388 FND_MSG_PUB.Add;
389 RAISE FND_API.G_EXC_ERROR;
390 WHEN OTHERS THEN
391 IF (l_debug = 'Y') THEN
392 IBE_UTIL.debug('Update statement failed.');
393 END IF;
394 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
395 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
396 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
397 FND_MESSAGE.Set_Token('REASON', SQLERRM);
398 FND_MSG_PUB.Add;
399
400 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_EXIST');
401 FND_MSG_PUB.Add;
402 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
403 END;
404
405 -- End of API body.
406 IF (l_debug = 'Y') THEN
407 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Update_Relationship_Detail(-)');
408 END IF;
409
410 -- Standard check of p_commit.
411 IF FND_API.To_Boolean( p_commit ) THEN
412 COMMIT WORK;
413 END IF;
414
415 -- Standard call to get message count and if count is 1, get message info.
416 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
417 p_count => x_msg_count ,
418 p_data => x_msg_data );
419 EXCEPTION
420 WHEN FND_API.G_EXC_ERROR THEN
421 ROLLBACK TO Update_Relationship_Detail_PVT;
422 x_return_status := FND_API.G_RET_STS_ERROR;
423 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
424 p_count => x_msg_count ,
425 p_data => x_msg_data );
426
427 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
428 ROLLBACK TO Update_Relationship_Detail_PVT;
429 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
430 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
431 p_count => x_msg_count ,
432 p_data => x_msg_data );
433
434 WHEN OTHERS THEN
435 ROLLBACK TO Update_Relationship_Detail_PVT;
436 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
437
438 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
439 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
440 L_API_NAME );
441 END IF;
442
443 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
444 p_count => x_msg_count ,
445 p_data => x_msg_data );
446 END Update_Relationship_Detail;
447
448
449 PROCEDURE Delete_Relationships(
450 p_api_version IN NUMBER ,
451 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
452 p_commit IN VARCHAR2 := FND_API.G_FALSE,
453 x_return_status OUT NOCOPY VARCHAR2 ,
454 x_msg_count OUT NOCOPY NUMBER ,
455 x_msg_data OUT NOCOPY VARCHAR2 ,
456 p_rel_type_code_tbl IN JTF_Varchar2_Table_100
457 )
458 IS
459 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Relationships';
460 L_API_VERSION CONSTANT NUMBER := 1.0;
461 i PLS_INTEGER;
462 l_debug VARCHAR2(1);
463
464 BEGIN
465 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
466
467 -- Standard Start of API savepoint
468 SAVEPOINT Delete_Relationship_PVT;
469
470 -- Standard call to check for call compatibility.
471 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
472 p_api_version,
473 L_API_NAME,
474 G_PKG_NAME )
475 THEN
476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
477 END IF;
478
479 -- Initialize message list if p_init_msg_list is set to TRUE.
480 IF FND_API.to_Boolean( p_init_msg_list ) THEN
481 FND_MSG_PUB.initialize;
482 END IF;
483
484 -- Initialize API return status to success
485 x_return_status := FND_API.G_RET_STS_SUCCESS;
486
487 IF (p_rel_type_code_tbl IS NULL OR p_rel_type_code_tbl.COUNT <= 0) THEN
488 RETURN;
489 END IF;
490
491 IF (l_debug = 'Y') THEN
492 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Delete_Relationship(+)');
493 END IF;
494 -- API body
495 FOR i IN 1..p_rel_type_code_tbl.COUNT LOOP
496
497 BEGIN
498 fnd_lookup_values_pkg.delete_row
499 (
500 X_LOOKUP_TYPE => 'IBE_RELATIONSHIP_TYPES',
501 X_VIEW_APPLICATION_ID => l_view_application_id,
502 X_LOOKUP_CODE => p_rel_type_code_tbl(i)
503 );
504
505 EXCEPTION
506 WHEN NO_DATA_FOUND THEN
507 IF (l_debug = 'Y') THEN
508 IBE_UTIL.debug('Delete statement failed.');
509 END IF;
510 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_EXIST');
511 FND_MSG_PUB.Add;
512 RAISE FND_API.G_EXC_ERROR;
513 WHEN OTHERS THEN
514 IF (l_debug = 'Y') THEN
515 IBE_UTIL.debug('Delete statement failed.');
516 END IF;
517 FND_MESSAGE.Set_Name('FND', 'SQL_PLSQL_ERROR');
518 FND_MESSAGE.Set_Token('ROUTINE', l_api_name);
519 FND_MESSAGE.Set_Token('ERRNO', SQLCODE);
520 FND_MESSAGE.Set_Token('REASON', SQLERRM);
521 FND_MSG_PUB.Add;
522
523 FND_MESSAGE.Set_Name('IBE', 'IBE_CT_REL_NOT_EXIST');
524 FND_MSG_PUB.Add;
525 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
526 END;
527
528 END LOOP;
529
530 IF (l_debug = 'Y') THEN
531 IBE_UTIL.debug('Deleting rows in IBE_CT_RELATION_RULES.');
532 END IF;
533 FORALL i IN p_rel_type_code_tbl.FIRST..p_rel_type_code_tbl.LAST
534 DELETE
535 FROM ibe_ct_relation_rules
536 WHERE relation_type_code = p_rel_type_code_tbl(i);
537
538 IF (l_debug = 'Y') THEN
539 IBE_UTIL.debug('Deleting rows in IBE_CT_RELATED_ITEMS.');
540 END IF;
541 FORALL i IN p_rel_type_code_tbl.FIRST..p_rel_type_code_tbl.LAST
542 DELETE
543 FROM ibe_ct_related_items
544 WHERE relation_type_code = p_rel_type_code_tbl(i);
545
546 IF (l_debug = 'Y') THEN
547 IBE_UTIL.debug('Deleting rows in IBE_CT_REL_EXCLUSIONS.');
548 END IF;
549 FORALL i IN p_rel_type_code_tbl.FIRST..p_rel_type_code_tbl.LAST
550 DELETE
551 FROM ibe_ct_rel_exclusions
552 WHERE relation_type_code = p_rel_type_code_tbl(i);
553 -- End of API body.
554 IF (l_debug = 'Y') THEN
555 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Delete_Relationship(-)');
556 END IF;
557
558 -- Standard check of p_commit.
559 IF FND_API.To_Boolean( p_commit ) THEN
560 COMMIT WORK;
561 END IF;
562
563 -- Standard call to get message count and if count is 1, get message info.
564 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
565 p_count => x_msg_count ,
566 p_data => x_msg_data );
567 EXCEPTION
568 WHEN FND_API.G_EXC_ERROR THEN
569 ROLLBACK TO Delete_Relationship_PVT;
570 x_return_status := FND_API.G_RET_STS_ERROR;
571 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
572 p_count => x_msg_count ,
573 p_data => x_msg_data );
574
575 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
576 ROLLBACK TO Delete_Relationship_PVT;
577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
578 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
579 p_count => x_msg_count ,
580 p_data => x_msg_data );
581
582 WHEN OTHERS THEN
583 ROLLBACK TO Delete_Relationship_PVT;
584 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
585
586 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
587 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
588 L_API_NAME );
589 END IF;
590
591 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
592 p_count => x_msg_count ,
593 p_data => x_msg_data );
594 END Delete_Relationships;
595
596
597 PROCEDURE Exclude_Related_Items(
598 p_api_version IN NUMBER ,
599 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
600 p_commit IN VARCHAR2 := FND_API.G_FALSE,
601 x_return_status OUT NOCOPY VARCHAR2 ,
602 x_msg_count OUT NOCOPY NUMBER ,
603 x_msg_data OUT NOCOPY VARCHAR2 ,
604 p_rel_type_code IN VARCHAR2 ,
605 p_inventory_item_id_tbl IN JTF_Number_Table ,
606 p_related_item_id_tbl IN JTF_Number_Table
607 )
608 IS
609 L_API_NAME CONSTANT VARCHAR2(30) := 'Exclude_Related_Items';
610 L_API_VERSION CONSTANT NUMBER := 1.0;
611 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
612 l_debug VARCHAR2(1);
613
614 BEGIN
615 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
616
617 -- Standard Start of API savepoint
618 SAVEPOINT Exclude_Related_Items_PVT;
619
620 -- Standard call to check for call compatibility.
621 IF NOT FND_API.Compatible_API_Call( L_API_VERSION,
622 p_api_version,
623 L_API_NAME,
624 G_PKG_NAME )
625 THEN
626 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
627 END IF;
628
629 -- Initialize message list if p_init_msg_list is set to TRUE.
630 IF FND_API.to_Boolean( p_init_msg_list ) THEN
631 FND_MSG_PUB.initialize;
632 END IF;
633
634 -- Initialize API return status to success
635 x_return_status := FND_API.G_RET_STS_SUCCESS;
636
637 IF (l_debug = 'Y') THEN
638 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Exclude_Related_Items(+)');
639 END IF;
640 -- API body
641 FORALL i IN p_inventory_item_id_tbl.FIRST..p_inventory_item_id_tbl.LAST
642 INSERT INTO IBE_CT_REL_EXCLUSIONS(
643 organization_id, relation_type_code, inventory_item_id,
644 related_item_id, object_version_number, created_by,
645 creation_date, last_updated_by, last_update_date
646 )
647 VALUES(
648 L_ORGANIZATION_ID, p_rel_type_code, p_inventory_item_id_tbl(i),
649 p_related_item_id_tbl(i), 1, L_USER_ID,
650 SYSDATE, L_USER_ID, SYSDATE
651 );
652 -- End of API body.
653 IF (l_debug = 'Y') THEN
654 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Exclude_Related_Items(-)');
655 END IF;
656
657 -- Standard check of p_commit.
658 IF FND_API.To_Boolean( p_commit ) THEN
659 COMMIT WORK;
660 END IF;
661
662 -- Standard call to get message count and if count is 1, get message info.
663 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
664 p_count => x_msg_count ,
665 p_data => x_msg_data );
666 EXCEPTION
667 WHEN FND_API.G_EXC_ERROR THEN
668 ROLLBACK TO Exclude_Related_Items_PVT;
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
671 p_count => x_msg_count ,
672 p_data => x_msg_data );
673
674 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
675 ROLLBACK TO Exclude_Related_Items_PVT;
676 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
677 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
678 p_count => x_msg_count ,
679 p_data => x_msg_data );
680
681 WHEN OTHERS THEN
682 ROLLBACK TO Exclude_Related_Items_PVT;
683 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
684
685 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
686 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
687 L_API_NAME );
688 END IF;
689
690 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
691 p_count => x_msg_count ,
692 p_data => x_msg_data );
693 END Exclude_Related_Items;
694
695
696 PROCEDURE Include_Related_Items(
697 p_api_version IN NUMBER ,
698 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
699 p_commit IN VARCHAR2 := FND_API.G_FALSE,
700 x_return_status OUT NOCOPY VARCHAR2 ,
701 x_msg_count OUT NOCOPY NUMBER ,
702 x_msg_data OUT NOCOPY VARCHAR2 ,
703 p_rel_type_code IN VARCHAR2 ,
704 p_inventory_item_id_tbl IN JTF_Number_Table ,
705 p_related_item_id_tbl IN JTF_Number_Table
706 )
707 IS
708 L_API_NAME CONSTANT VARCHAR2(30) := 'Include_Related_Items';
709 L_API_VERSION CONSTANT NUMBER := 1.0;
710 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
711 l_debug VARCHAR2(1);
712
713 BEGIN
714 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
715
716 -- Standard Start of API savepoint
717 SAVEPOINT Include_Related_Items_PVT;
718
719 -- Standard call to check for call compatibility.
720 IF NOT FND_API.Compatible_API_Call(L_API_VERSION,
721 p_api_version,
722 L_API_NAME ,
723 G_PKG_NAME )
724 THEN
725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
726 END IF;
727
728 -- Initialize message list if p_init_msg_list is set to TRUE.
729 IF FND_API.to_Boolean( p_init_msg_list ) THEN
730 FND_MSG_PUB.initialize;
731 END IF;
732
733 -- Initialize API return status to success
734 x_return_status := FND_API.G_RET_STS_SUCCESS;
735
736 IF (l_debug = 'Y') THEN
737 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Include_Related_Items(+)');
738 END IF;
739 -- API body
740 FORALL i IN p_inventory_item_id_tbl.FIRST..p_inventory_item_id_tbl.LAST
741 DELETE
742 FROM IBE_CT_REL_EXCLUSIONS
743 WHERE relation_type_code = p_rel_type_code
744 AND inventory_item_id = p_inventory_item_id_tbl(i)
745 AND related_item_id = p_related_item_id_tbl(i)
746 AND organization_id = L_ORGANIZATION_ID; --Bug 2922902
747 -- End of API body.
748 IF (l_debug = 'Y') THEN
749 IBE_UTIL.debug('IBE_Prod_Relation_PVT.Include_Related_Items(-)');
750 END IF;
751
752 -- Standard check of p_commit.
753 IF FND_API.To_Boolean( p_commit ) THEN
754 COMMIT WORK;
755 END IF;
756
757 -- Standard call to get message count and if count is 1, get message info.
758 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
759 p_count => x_msg_count ,
760 p_data => x_msg_data );
761 EXCEPTION
762 WHEN FND_API.G_EXC_ERROR THEN
763 ROLLBACK TO Include_Related_Items_PVT;
764 x_return_status := FND_API.G_RET_STS_ERROR;
765 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
766 p_count => x_msg_count ,
767 p_data => x_msg_data );
768
769 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
770 ROLLBACK TO Include_Related_Items_PVT;
771 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
772 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
773 p_count => x_msg_count ,
774 p_data => x_msg_data );
775
776 WHEN OTHERS THEN
777 ROLLBACK TO Include_Related_Items_PVT;
778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
779
780 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
781 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,
782 L_API_NAME );
783 END IF;
784
785 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
786 p_count => x_msg_count ,
787 p_data => x_msg_data );
788 END Include_Related_Items;
789
790
791 --changes for bug 2922902
792 PROCEDURE Insert_Related_Items_Rows(
793 p_rel_type_code IN VARCHAR2,
794 p_rel_rule_id IN NUMBER ,
795 p_origin_object_type IN VARCHAR2,
796 p_dest_object_type IN VARCHAR2,
797 p_origin_object_id IN NUMBER ,
798 p_dest_object_id IN NUMBER
799 ) IS
800 BEGIN
801 Insert_Related_Items_Rows(
802 p_rel_type_code,
803 p_rel_rule_id,
804 p_origin_object_type,
805 p_dest_object_type,
806 p_origin_object_id,
807 p_dest_object_id,
808 L_ORGANIZATION_ID
809 );
810
811 END Insert_Related_Items_Rows;
812
813
814
815 PROCEDURE Insert_Related_Items_Rows(
816 p_rel_type_code IN VARCHAR2,
817 p_rel_rule_id IN NUMBER ,
818 p_origin_object_type IN VARCHAR2,
819 p_dest_object_type IN VARCHAR2,
820 p_origin_object_id IN NUMBER ,
821 p_dest_object_id IN NUMBER ,
822 p_organization_id IN NUMBER
823 )
824 IS
825 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
826 l_category_set_id_str VARCHAR2(30);
827 l_debug VARCHAR2(1);
828
829 BEGIN
830 l_debug := NVL(FND_PROFILE.VALUE('IBE_DEBUG'),'N');
831
832 l_category_set_id_str := FND_PROFILE.VALUE_SPECIFIC('IBE_CATEGORY_SET', null, null, 671);
833 IF (l_debug = 'Y') THEN
834 IBE_UTIL.debug('Category set id from the profile = '||l_category_set_id_str);
835 END IF;
836
837 IF p_origin_object_type = 'N' AND p_dest_object_type = 'N' THEN
838 NULL;
839 ELSIF p_origin_object_type = 'S' AND p_dest_object_type = 'S' THEN
840 INSERT INTO IBE_CT_RELATED_ITEMS(
841 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
842 related_item_id, object_version_number, created_by, creation_date,
843 last_updated_by, last_update_date
844 )
845 SELECT JDSI1.organization_id, p_rel_type_code, p_rel_rule_id, JDSI1.inventory_item_id,
846 JDSI2.inventory_item_id, 1, L_USER_ID, SYSDATE,
847 L_USER_ID, SYSDATE
848 FROM IBE_DSP_SECTION_ITEMS JDSI1,
849 IBE_DSP_SECTION_ITEMS JDSI2
850 WHERE JDSI1.section_id = p_origin_object_id
851 AND JDSI2.section_id = p_dest_object_id
852 AND JDSI1.organization_id = JDSI2.organization_id; --Bug 2922902
853
854 ELSIF p_origin_object_type = 'S' AND p_dest_object_type = 'C' THEN
855 INSERT INTO IBE_CT_RELATED_ITEMS(
856 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
857 related_item_id, object_version_number, created_by, creation_date,
858 last_updated_by, last_update_date
859 )
860 SELECT DISTINCT MIC.organization_id, p_rel_type_code, p_rel_rule_id,
861 JDSI.inventory_item_id, MIC.inventory_item_id, 1,
862 L_USER_ID, SYSDATE, L_USER_ID, SYSDATE
863 FROM IBE_DSP_SECTION_ITEMS JDSI,
864 MTL_ITEM_CATEGORIES MIC
865 WHERE JDSI.section_id = p_origin_object_id
866 AND MIC.organization_id = JDSI.organization_id --Bug 2922902
867 AND MIC.category_id = p_dest_object_id
868 AND MIC.category_set_id = l_category_set_id_str; --Bug 2630696
869
870 ELSIF p_origin_object_type = 'S' AND p_dest_object_type = 'I' THEN
871 INSERT INTO IBE_CT_RELATED_ITEMS(
872 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
873 related_item_id, object_version_number, created_by, creation_date,
874 last_updated_by, last_update_date
875 )
876 SELECT p_organization_id, p_rel_type_code, p_rel_rule_id, JDSI.inventory_item_id,
877 p_dest_object_id, 1, L_USER_ID, SYSDATE,
878 L_USER_ID, SYSDATE
879 FROM IBE_DSP_SECTION_ITEMS JDSI
880 WHERE JDSI.section_id = p_origin_object_id
881 AND JDSI.organization_id = p_organization_id; --Bug 2922902
882
883 ELSIF p_origin_object_type = 'C' AND p_dest_object_type = 'S' THEN
884 INSERT INTO IBE_CT_RELATED_ITEMS(
885 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
886 related_item_id, object_version_number, created_by, creation_date,
887 last_updated_by, last_update_date
888 )
889 SELECT DISTINCT MIC.organization_id, p_rel_type_code, p_rel_rule_id, MIC.inventory_item_id,
890 JDSI.inventory_item_id, 1, L_USER_ID, SYSDATE,
891 L_USER_ID, SYSDATE
892 FROM MTL_ITEM_CATEGORIES MIC,
893 IBE_DSP_SECTION_ITEMS JDSI
894 WHERE MIC.organization_id = JDSI.organization_id --Bug 2922902
895 AND MIC.category_id = p_origin_object_id
896 AND JDSI.section_id = p_dest_object_id
897 AND MIC.category_set_id = l_category_set_id_str; --Bug 2630696
898
899 ELSIF p_origin_object_type = 'C' AND p_dest_object_type = 'C' THEN
900 INSERT INTO IBE_CT_RELATED_ITEMS(
901 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
902 related_item_id, object_version_number, created_by, creation_date,
903 last_updated_by, last_update_date
904 )
905 SELECT DISTINCT MIC1.organization_id, p_rel_type_code, p_rel_rule_id, MIC1.inventory_item_id,
906 MIC2.inventory_item_id, 1, L_USER_ID, SYSDATE,
907 L_USER_ID, SYSDATE
908 FROM MTL_ITEM_CATEGORIES MIC1,
909 MTL_ITEM_CATEGORIES MIC2
910 WHERE MIC1.organization_id = MIC2.organization_id --Bug 2922902
911 AND MIC1.category_id = p_origin_object_id
912 AND MIC2.category_id = p_dest_object_id
913 AND MIC1.organization_id = MIC2.organization_id --Bug 2630696
914 AND MIC1.category_set_id = MIC2.category_set_id --Bug 2630696
915 AND MIC1.category_set_id = l_category_set_id_str; --bug 2630696
916
917 ELSIF p_origin_object_type = 'C' AND p_dest_object_type = 'I' THEN
918 INSERT INTO IBE_CT_RELATED_ITEMS(
919 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
920 related_item_id, object_version_number, created_by, creation_date,
921 last_updated_by, last_update_date
922 )
923 SELECT DISTINCT p_organization_id, p_rel_type_code, p_rel_rule_id, MIC.inventory_item_id,
924 p_dest_object_id, 1, L_USER_ID, SYSDATE,
925 L_USER_ID, SYSDATE
926 FROM MTL_ITEM_CATEGORIES MIC
927 WHERE MIC.organization_id = p_organization_id
928 AND MIC.category_id = p_origin_object_id
929 AND MIC.category_set_id = l_category_set_id_str; --Bug 2630696
930
931 ELSIF p_origin_object_type = 'I' AND p_dest_object_type = 'S' THEN
932 INSERT INTO IBE_CT_RELATED_ITEMS(
933 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
934 related_item_id, object_version_number, created_by, creation_date,
935 last_updated_by, last_update_date
936 )
937 SELECT p_organization_id, p_rel_type_code, p_rel_rule_id, p_origin_object_id,
938 JDSI.inventory_item_id, 1, L_USER_ID, SYSDATE,
939 L_USER_ID, SYSDATE
940 FROM IBE_DSP_SECTION_ITEMS JDSI
941 WHERE JDSI.section_id = p_dest_object_id
942 AND JDSI.organization_id = p_organization_id; --Bug 2922902
943
944 ELSIF p_origin_object_type = 'I' AND p_dest_object_type = 'C' THEN
945 INSERT INTO IBE_CT_RELATED_ITEMS(
946 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
947 related_item_id, object_version_number, created_by, creation_date,
948 last_updated_by, last_update_date
949 )
950 SELECT DISTINCT p_organization_id, p_rel_type_code, p_rel_rule_id, p_origin_object_id,
951 MIC.inventory_item_id, 1, L_USER_ID, SYSDATE,
952 L_USER_ID, SYSDATE
953 FROM MTL_ITEM_CATEGORIES MIC
954 WHERE MIC.organization_id = p_organization_id
955 AND MIC.category_id = p_dest_object_id
956 AND MIC.category_set_id = l_category_set_id_str; --Bug 2630696
957
958 ELSIF p_origin_object_type = 'I' AND p_dest_object_type = 'I' THEN
959 INSERT INTO IBE_CT_RELATED_ITEMS(
960 organization_id, relation_type_code, relation_rule_id, inventory_item_id,
961 related_item_id, object_version_number, created_by, creation_date,
962 last_updated_by, last_update_date
963 )
964 VALUES(
965 p_organization_id, p_rel_type_code, p_rel_rule_id, p_origin_object_id,
966 p_dest_object_id, 1, L_USER_ID, SYSDATE,
967 L_USER_ID, SYSDATE
968 );
969 END IF;
970 END Insert_Related_Items_Rows;
971
972
973 PROCEDURE Remove_Invalid_Exclusions
974 IS
975 BEGIN
976 DELETE
977 FROM IBE_CT_REL_EXCLUSIONS ICRE
978 WHERE NOT EXISTS( SELECT NULL
979 FROM IBE_CT_RELATED_ITEMS ICRI
980 WHERE ICRI.relation_type_code = ICRE.relation_type_code
981 AND ICRI.inventory_item_id = ICRE.inventory_item_id
982 AND ICRI.organization_id = ICRE.organization_id --Bug 2922902
983 AND ICRI.related_item_id = ICRE.related_item_id );
984 END Remove_Invalid_Exclusions;
985
986
987 PROCEDURE Item_Category_Inserted(
988 p_category_id IN NUMBER,
989 p_inventory_item_id IN NUMBER,
990 p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
991 IS
992 TYPE rel_rule_csr_type IS REF CURSOR;
993 l_rel_rule_csr rel_rule_csr_type;
994 l_rel_rule_id NUMBER(15);
995 l_rel_type_code VARCHAR2(30);
996 l_object_type VARCHAR2(30);
997 l_object_id NUMBER(15);
998 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
999 BEGIN
1000 -- 1. Where Category p_category_id is origin
1001 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, dest_object_type, dest_object_id ' ||
1002 'FROM ibe_ct_relation_rules ' ||
1003 'WHERE origin_object_type = ''C'' ' ||
1004 'AND origin_object_id = :category_id '
1005 USING p_category_id;
1006
1007 LOOP
1008 FETCH l_rel_rule_csr INTO l_rel_type_code, l_rel_rule_id, l_object_type, l_object_id;
1009 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1010
1011 IF (p_organization_id is NULL) THEN
1012 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1013 p_rel_rule_id => l_rel_rule_id ,
1014 p_origin_object_type => 'I' ,
1015 p_dest_object_type => l_object_type ,
1016 p_origin_object_id => p_inventory_item_id,
1017 p_dest_object_id => l_object_id ,
1018 /*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
1019 ELSE
1020 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1021 p_rel_rule_id => l_rel_rule_id ,
1022 p_origin_object_type => 'I' ,
1023 p_dest_object_type => l_object_type ,
1024 p_origin_object_id => p_inventory_item_id,
1025 p_dest_object_id => l_object_id ,
1026 /*Bug 2922902*/ p_organization_id => p_organization_id);
1027 END IF;
1028 END LOOP;
1029 CLOSE l_rel_rule_csr;
1030
1031 -- 2. Where Category p_category_id is destination
1032 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, origin_object_type, origin_object_id ' ||
1033 'FROM ibe_ct_relation_rules ' ||
1034 'WHERE dest_object_type = ''C'' ' ||
1035 'AND dest_object_id = :category_id '
1036 USING p_category_id;
1037
1038 LOOP
1039 FETCH l_rel_rule_csr INTO l_rel_type_code, l_rel_rule_id, l_object_type, l_object_id;
1040 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1041
1042 IF (p_organization_id is NULL) THEN
1043 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1044 p_rel_rule_id => l_rel_rule_id ,
1045 p_origin_object_type => l_object_type ,
1046 p_dest_object_type => 'I' ,
1047 p_origin_object_id => l_object_id ,
1048 p_dest_object_id => p_inventory_item_id ,
1049 /*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
1050 ELSE
1051 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1052 p_rel_rule_id => l_rel_rule_id ,
1053 p_origin_object_type => l_object_type ,
1054 p_dest_object_type => 'I' ,
1055 p_origin_object_id => l_object_id ,
1056 p_dest_object_id => p_inventory_item_id ,
1057 /*Bug 2922902*/ p_organization_id => p_organization_id);
1058 END IF;
1059 END LOOP;
1060 CLOSE l_rel_rule_csr;
1061
1062 END Item_Category_Inserted;
1063
1064
1065 PROCEDURE Item_Section_Inserted(
1066 p_section_id IN NUMBER,
1067 p_inventory_item_id IN NUMBER,
1068 p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
1069 IS
1070 TYPE rel_rule_csr_type IS REF CURSOR;
1071 l_rel_rule_csr rel_rule_csr_type;
1072 l_rel_rule_id NUMBER(15);
1073 l_rel_type_code VARCHAR2(30);
1074 l_object_type VARCHAR2(30);
1075 l_object_id NUMBER(15);
1076 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
1077 BEGIN
1078 -- 1. Where Section p_section_id is origin
1079 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, dest_object_type, dest_object_id ' ||
1080 'FROM ibe_ct_relation_rules ' ||
1081 'WHERE origin_object_type = ''S'' ' ||
1082 'AND origin_object_id = :section_id ' ||
1083 'AND relation_type_code <> ''AUTOPLACEMENT'' '
1084 USING p_section_id;
1085
1086 LOOP
1087 FETCH l_rel_rule_csr INTO l_rel_type_code, l_rel_rule_id, l_object_type, l_object_id;
1088 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1089
1090 IF (p_organization_id IS NULL) THEN
1091 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1092 p_rel_rule_id => l_rel_rule_id ,
1093 p_origin_object_type => 'I' ,
1094 p_dest_object_type => l_object_type ,
1095 p_origin_object_id => p_inventory_item_id,
1096 p_dest_object_id => l_object_id ,
1097 /*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
1098 ELSE
1099 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1100 p_rel_rule_id => l_rel_rule_id ,
1101 p_origin_object_type => 'I' ,
1102 p_dest_object_type => l_object_type ,
1103 p_origin_object_id => p_inventory_item_id,
1104 p_dest_object_id => l_object_id ,
1105 /*Bug 2922902*/ p_organization_id => p_organization_id);
1106 END IF;
1107 END LOOP;
1108 CLOSE l_rel_rule_csr;
1109
1110 -- 2. Where Section p_section_id is destination
1111 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, origin_object_type, origin_object_id ' ||
1112 'FROM ibe_ct_relation_rules ' ||
1113 'WHERE dest_object_type = ''S'' ' ||
1114 'AND dest_object_id = :section_id '
1115 USING p_section_id;
1116
1117 LOOP
1118 FETCH l_rel_rule_csr INTO l_rel_type_code, l_rel_rule_id, l_object_type, l_object_id;
1119 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1120
1121 IF (p_organization_id IS NULL) THEN
1122 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1123 p_rel_rule_id => l_rel_rule_id ,
1124 p_origin_object_type => l_object_type ,
1125 p_dest_object_type => 'I' ,
1126 p_origin_object_id => l_object_id ,
1127 p_dest_object_id => p_inventory_item_id,
1128 /*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
1129 ELSE
1130 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1131 p_rel_rule_id => l_rel_rule_id ,
1132 p_origin_object_type => l_object_type ,
1133 p_dest_object_type => 'I' ,
1134 p_origin_object_id => l_object_id ,
1135 p_dest_object_id => p_inventory_item_id,
1136 /*Bug 2922902*/ p_organization_id => p_organization_id);
1137 END IF;
1138 END LOOP;
1139 CLOSE l_rel_rule_csr;
1140
1141 END Item_Section_Inserted;
1142
1143
1144 PROCEDURE Item_Category_Deleted(
1145 p_category_id IN NUMBER,
1146 p_inventory_item_id IN NUMBER,
1147 p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
1148 IS
1149 TYPE rel_rule_csr_type IS REF CURSOR;
1150 l_rel_rule_csr rel_rule_csr_type;
1151 l_relation_type_code VARCHAR2(30);
1152 l_rel_rule_id NUMBER(15);
1153 BEGIN
1154 -- 1. Work on the rules where origin_type is Category
1155 -- origin_object_id is p_category_id
1156 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
1157 'relation_rule_id ' ||
1158 'FROM ibe_ct_relation_rules ' ||
1159 'WHERE origin_object_type = ''C'' ' ||
1160 'AND origin_object_id = :category_id '
1161 USING p_category_id;
1162 LOOP
1163 FETCH l_rel_rule_csr INTO l_relation_type_code,
1164 l_rel_rule_id;
1165 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1166
1167 -- Delete all the related items created by the rule
1168 -- affected by the given category and deleted item
1169 IF (p_organization_id IS NULL) THEN
1170 DELETE IBE_CT_RELATED_ITEMS
1171 WHERE relation_type_code = l_relation_type_code
1172 AND relation_rule_id = l_rel_rule_id
1173 AND inventory_item_id = p_inventory_item_id
1174 AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
1175 ELSE
1176 DELETE IBE_CT_RELATED_ITEMS
1177 WHERE relation_type_code = l_relation_type_code
1178 AND relation_rule_id = l_rel_rule_id
1179 AND inventory_item_id = p_inventory_item_id
1180 AND organization_id = p_organization_id; --Bug 2922902
1181 END IF;
1182 END LOOP;
1183
1184 CLOSE l_rel_rule_csr;
1185
1186 -- 2. Work on the rules where dest_type is Category
1187 -- dest_object_id is p_category_id
1188 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
1189 'relation_rule_id ' ||
1190 'FROM ibe_ct_relation_rules ' ||
1191 'WHERE dest_object_type = ''C'' ' ||
1192 'AND dest_object_id = :category_id '
1193 USING p_category_id;
1194
1195 LOOP
1196 FETCH l_rel_rule_csr INTO l_relation_type_code,
1197 l_rel_rule_id;
1198 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1199
1200 -- Delete all the related items created by the rule
1201 -- affected by the given category and deleted item
1202 IF (p_organization_id IS NULL) THEN
1203 DELETE IBE_CT_RELATED_ITEMS
1204 WHERE relation_type_code = l_relation_type_code
1205 AND relation_rule_id = l_rel_rule_id
1206 AND related_item_id = p_inventory_item_id
1207 AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
1208 ELSE
1209 DELETE IBE_CT_RELATED_ITEMS
1210 WHERE relation_type_code = l_relation_type_code
1211 AND relation_rule_id = l_rel_rule_id
1212 AND related_item_id = p_inventory_item_id
1213 AND organization_id = p_organization_id; --Bug 2922902
1214 END IF;
1215 END LOOP;
1216
1217 CLOSE l_rel_rule_csr;
1218
1219 Remove_Invalid_Exclusions();
1220 END Item_Category_Deleted;
1221
1222
1223 PROCEDURE Item_Section_Deleted(
1224 p_section_id IN NUMBER,
1225 p_inventory_item_id IN NUMBER,
1226 p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
1227 IS
1228 TYPE rel_rule_csr_type IS REF CURSOR;
1229 l_rel_rule_csr rel_rule_csr_type;
1230 l_relation_type_code VARCHAR2(30);
1231 l_rel_rule_id NUMBER(15);
1232 BEGIN
1233 -- 1. Work on the rules where origin_type is Section
1234 -- origin_object_id is p_section_id
1235 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, '||
1236 'relation_rule_id ' ||
1237 'FROM ibe_ct_relation_rules ' ||
1238 'WHERE origin_object_type = ''S'' ' ||
1239 'AND origin_object_id = :section_id ' ||
1240 'AND relation_type_code <> ''AUTOPLACEMENT'' '
1241 USING p_section_id;
1242
1243 LOOP
1244 FETCH l_rel_rule_csr INTO l_relation_type_code,
1245 l_rel_rule_id;
1246 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1247
1248 -- Delete all the related items created by the rule
1249 -- affected by the given section and deleted item
1250 IF (p_organization_id IS NULL) THEN
1251 DELETE IBE_CT_RELATED_ITEMS
1252 WHERE relation_type_code = l_relation_type_code
1253 AND relation_rule_id = l_rel_rule_id
1254 AND inventory_item_id = p_inventory_item_id
1255 AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
1256 ELSE
1257 DELETE IBE_CT_RELATED_ITEMS
1258 WHERE relation_type_code = l_relation_type_code
1259 AND relation_rule_id = l_rel_rule_id
1260 AND inventory_item_id = p_inventory_item_id
1261 AND organization_id = p_organization_id; --Bug 2922902
1262 END IF;
1263 END LOOP;
1264 CLOSE l_rel_rule_csr;
1265
1266 -- 2. Work on the rules where dest_type is Section
1267 -- dest_object_id is p_section_id
1268 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
1269 'relation_rule_id ' ||
1270 'FROM ibe_ct_relation_rules ' ||
1271 'WHERE dest_object_type = ''S'' ' ||
1272 'AND dest_object_id = :section_id '
1273 USING p_section_id;
1274
1275 LOOP
1276 FETCH l_rel_rule_csr INTO l_relation_type_code,
1277 l_rel_rule_id;
1278 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1279
1280 -- Delete all the related items created by the rule
1281 -- affected by the given category and deleted item
1282 IF (p_organization_id IS NULL) THEN
1283 DELETE IBE_CT_RELATED_ITEMS
1284 WHERE relation_type_code = l_relation_type_code
1285 AND relation_rule_id = l_rel_rule_id
1286 AND related_item_id = p_inventory_item_id
1287 AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
1288 ELSE
1289 DELETE IBE_CT_RELATED_ITEMS
1290 WHERE relation_type_code = l_relation_type_code
1291 AND relation_rule_id = l_rel_rule_id
1292 AND related_item_id = p_inventory_item_id
1293 AND organization_id = p_organization_id; --Bug 2922902
1294 END IF;
1295 END LOOP;
1296
1297 CLOSE l_rel_rule_csr;
1298
1299 Remove_Invalid_Exclusions();
1300 END Item_Section_Deleted;
1301
1302
1303 PROCEDURE Category_Deleted(
1304 p_category_id IN NUMBER)
1305 IS
1306 BEGIN
1307 -- 1. Delete all the rules where origin_object_type is Category
1308 -- and origin_object_id is p_section_id
1309 DELETE IBE_CT_RELATION_RULES
1310 WHERE origin_object_type = 'C'
1311 AND origin_object_id = p_category_id;
1312
1313 -- 2. Delete all the rules where dest_object_type is Category
1314 -- and dest_object_id is p_section_id
1315 DELETE IBE_CT_RELATION_RULES
1316 WHERE dest_object_type = 'C'
1317 AND dest_object_id = p_category_id;
1318 END Category_Deleted;
1319
1320
1321 PROCEDURE Section_Deleted(p_section_id IN NUMBER)
1322 IS
1323 BEGIN
1324 -- 1. Delete all the rules where origin_object_type is Section
1325 -- and origin_object_id is p_section_id
1326 DELETE IBE_CT_RELATION_RULES
1327 WHERE origin_object_type = 'S'
1328 AND origin_object_id = p_section_id;
1329
1330 -- 2. Delete all the rules where dest_object_type is Section
1331 -- and dest_object_id is p_section_id
1332 DELETE IBE_CT_RELATION_RULES
1333 WHERE dest_object_type = 'S'
1334 AND dest_object_id = p_section_id;
1335 END Section_Deleted;
1336
1337
1338 PROCEDURE Item_Inserted(p_inventory_item_id IN NUMBER)
1339 IS
1340 BEGIN
1341 NULL;
1342 END Item_Inserted;
1343
1344
1345 PROCEDURE Item_Deleted(
1346 p_organization_id IN NUMBER,
1347 p_inventory_item_id IN NUMBER
1348 )
1349 IS
1350 BEGIN
1351 -- 1. Remove all the rules that have the deleted item
1352 -- as an origin object
1353 DELETE
1354 FROM ibe_ct_relation_rules
1355 WHERE origin_object_type = 'I'
1356 AND origin_object_id = p_inventory_item_id;
1357
1358 -- 2. Remove all the rules that have the deleted item
1359 -- as a destination object
1360 DELETE
1361 FROM ibe_ct_relation_rules
1362 WHERE dest_object_type = 'I'
1363 AND dest_object_id = p_inventory_item_id;
1364
1365 -- 3. Remove all the rows in Related Items table
1366 -- that have the deleted item as inventory item
1367 DELETE
1368 FROM IBE_CT_RELATED_ITEMS
1369 WHERE inventory_item_id = p_inventory_item_id
1370 AND organization_id = p_organization_id;
1371
1372 -- 4. Remove all the rows in Related Items table
1373 -- that have the deleted item as related item
1374 DELETE
1375 FROM IBE_CT_RELATED_ITEMS
1376 WHERE related_item_id = p_inventory_item_id
1377 AND organization_id = p_organization_id;
1378
1379 Remove_Invalid_Exclusions();
1380 END Item_Deleted;
1381
1382 END IBE_Prod_Relation_PVT;