[Home] [Help]
PACKAGE BODY: APPS.IBE_PROD_RELATION_PVT
Source
1 PACKAGE BODY IBE_Prod_Relation_PVT AS
2 /* $Header: IBEVCRLB.pls 120.0.12020000.2 2013/01/09 06:12:34 amaheshw ship $ */
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 --09/01/12 AMAHESHW Bug 16078175 Ignore AUTOPLACEMENT relationship during Item_Category_Inserted proc
1002 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, dest_object_type, dest_object_id ' ||
1003 'FROM ibe_ct_relation_rules ' ||
1004 'WHERE origin_object_type = ''C'' ' ||
1005 'AND origin_object_id = :category_id ' ||
1006 'AND relation_type_code <> ''AUTOPLACEMENT'' '
1007 USING p_category_id;
1008
1009 LOOP
1010 FETCH l_rel_rule_csr INTO l_rel_type_code, l_rel_rule_id, l_object_type, l_object_id;
1011 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1012
1013 IF (p_organization_id is NULL) THEN
1014 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1015 p_rel_rule_id => l_rel_rule_id ,
1016 p_origin_object_type => 'I' ,
1017 p_dest_object_type => l_object_type ,
1018 p_origin_object_id => p_inventory_item_id,
1019 p_dest_object_id => l_object_id ,
1020 /*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
1021 ELSE
1022 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1023 p_rel_rule_id => l_rel_rule_id ,
1024 p_origin_object_type => 'I' ,
1025 p_dest_object_type => l_object_type ,
1026 p_origin_object_id => p_inventory_item_id,
1027 p_dest_object_id => l_object_id ,
1028 /*Bug 2922902*/ p_organization_id => p_organization_id);
1029 END IF;
1030 END LOOP;
1031 CLOSE l_rel_rule_csr;
1032
1033 -- 2. Where Category p_category_id is destination
1034 --09/01/12 AMAHESHW Bug 16078175 Ignore AUTOPLACEMENT relationship during Item_Category_Inserted proc
1035 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, origin_object_type, origin_object_id ' ||
1036 'FROM ibe_ct_relation_rules ' ||
1037 'WHERE dest_object_type = ''C'' ' ||
1038 'AND dest_object_id = :category_id ' ||
1039 'AND relation_type_code <> ''AUTOPLACEMENT'' '
1040 USING p_category_id;
1041
1042 LOOP
1043 FETCH l_rel_rule_csr INTO l_rel_type_code, l_rel_rule_id, l_object_type, l_object_id;
1044 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1045
1046 IF (p_organization_id is NULL) THEN
1047 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1048 p_rel_rule_id => l_rel_rule_id ,
1049 p_origin_object_type => l_object_type ,
1050 p_dest_object_type => 'I' ,
1051 p_origin_object_id => l_object_id ,
1052 p_dest_object_id => p_inventory_item_id ,
1053 /*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
1054 ELSE
1055 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1056 p_rel_rule_id => l_rel_rule_id ,
1057 p_origin_object_type => l_object_type ,
1058 p_dest_object_type => 'I' ,
1059 p_origin_object_id => l_object_id ,
1060 p_dest_object_id => p_inventory_item_id ,
1061 /*Bug 2922902*/ p_organization_id => p_organization_id);
1062 END IF;
1063 END LOOP;
1064 CLOSE l_rel_rule_csr;
1065
1066 END Item_Category_Inserted;
1067
1068
1069 PROCEDURE Item_Section_Inserted(
1070 p_section_id IN NUMBER,
1071 p_inventory_item_id IN NUMBER,
1072 p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
1073 IS
1074 TYPE rel_rule_csr_type IS REF CURSOR;
1075 l_rel_rule_csr rel_rule_csr_type;
1076 l_rel_rule_id NUMBER(15);
1077 l_rel_type_code VARCHAR2(30);
1078 l_object_type VARCHAR2(30);
1079 l_object_id NUMBER(15);
1080 L_USER_ID CONSTANT NUMBER := FND_GLOBAL.User_ID;
1081 BEGIN
1082 -- 1. Where Section p_section_id is origin
1083 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, dest_object_type, dest_object_id ' ||
1084 'FROM ibe_ct_relation_rules ' ||
1085 'WHERE origin_object_type = ''S'' ' ||
1086 'AND origin_object_id = :section_id ' ||
1087 'AND relation_type_code <> ''AUTOPLACEMENT'' '
1088 USING p_section_id;
1089
1090 LOOP
1091 FETCH l_rel_rule_csr INTO l_rel_type_code, l_rel_rule_id, l_object_type, l_object_id;
1092 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1093
1094 IF (p_organization_id IS NULL) THEN
1095 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1096 p_rel_rule_id => l_rel_rule_id ,
1097 p_origin_object_type => 'I' ,
1098 p_dest_object_type => l_object_type ,
1099 p_origin_object_id => p_inventory_item_id,
1100 p_dest_object_id => l_object_id ,
1101 /*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
1102 ELSE
1103 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1104 p_rel_rule_id => l_rel_rule_id ,
1105 p_origin_object_type => 'I' ,
1106 p_dest_object_type => l_object_type ,
1107 p_origin_object_id => p_inventory_item_id,
1108 p_dest_object_id => l_object_id ,
1109 /*Bug 2922902*/ p_organization_id => p_organization_id);
1110 END IF;
1111 END LOOP;
1112 CLOSE l_rel_rule_csr;
1113
1114 -- 2. Where Section p_section_id is destination
1115 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, relation_rule_id, origin_object_type, origin_object_id ' ||
1116 'FROM ibe_ct_relation_rules ' ||
1117 'WHERE dest_object_type = ''S'' ' ||
1118 'AND dest_object_id = :section_id '
1119 USING p_section_id;
1120
1121 LOOP
1122 FETCH l_rel_rule_csr INTO l_rel_type_code, l_rel_rule_id, l_object_type, l_object_id;
1123 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1124
1125 IF (p_organization_id IS NULL) THEN
1126 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1127 p_rel_rule_id => l_rel_rule_id ,
1128 p_origin_object_type => l_object_type ,
1129 p_dest_object_type => 'I' ,
1130 p_origin_object_id => l_object_id ,
1131 p_dest_object_id => p_inventory_item_id,
1132 /*Bug 3001591*/ p_organization_id => L_ORGANIZATION_ID);
1133 ELSE
1134 Insert_Related_Items_Rows( p_rel_type_code => l_rel_type_code ,
1135 p_rel_rule_id => l_rel_rule_id ,
1136 p_origin_object_type => l_object_type ,
1137 p_dest_object_type => 'I' ,
1138 p_origin_object_id => l_object_id ,
1139 p_dest_object_id => p_inventory_item_id,
1140 /*Bug 2922902*/ p_organization_id => p_organization_id);
1141 END IF;
1142 END LOOP;
1143 CLOSE l_rel_rule_csr;
1144
1145 END Item_Section_Inserted;
1146
1147
1148 PROCEDURE Item_Category_Deleted(
1149 p_category_id IN NUMBER,
1150 p_inventory_item_id IN NUMBER,
1151 p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
1152 IS
1153 TYPE rel_rule_csr_type IS REF CURSOR;
1154 l_rel_rule_csr rel_rule_csr_type;
1155 l_relation_type_code VARCHAR2(30);
1156 l_rel_rule_id NUMBER(15);
1157 BEGIN
1158 -- 1. Work on the rules where origin_type is Category
1159 -- origin_object_id is p_category_id
1160 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
1161 'relation_rule_id ' ||
1162 'FROM ibe_ct_relation_rules ' ||
1163 'WHERE origin_object_type = ''C'' ' ||
1164 'AND origin_object_id = :category_id '
1165 USING p_category_id;
1166 LOOP
1167 FETCH l_rel_rule_csr INTO l_relation_type_code,
1168 l_rel_rule_id;
1169 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1170
1171 -- Delete all the related items created by the rule
1172 -- affected by the given category and deleted item
1173 IF (p_organization_id IS NULL) THEN
1174 DELETE IBE_CT_RELATED_ITEMS
1175 WHERE relation_type_code = l_relation_type_code
1176 AND relation_rule_id = l_rel_rule_id
1177 AND inventory_item_id = p_inventory_item_id
1178 AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
1179 ELSE
1180 DELETE IBE_CT_RELATED_ITEMS
1181 WHERE relation_type_code = l_relation_type_code
1182 AND relation_rule_id = l_rel_rule_id
1183 AND inventory_item_id = p_inventory_item_id
1184 AND organization_id = p_organization_id; --Bug 2922902
1185 END IF;
1186 END LOOP;
1187
1188 CLOSE l_rel_rule_csr;
1189
1190 -- 2. Work on the rules where dest_type is Category
1191 -- dest_object_id is p_category_id
1192 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
1193 'relation_rule_id ' ||
1194 'FROM ibe_ct_relation_rules ' ||
1195 'WHERE dest_object_type = ''C'' ' ||
1196 'AND dest_object_id = :category_id '
1197 USING p_category_id;
1198
1199 LOOP
1200 FETCH l_rel_rule_csr INTO l_relation_type_code,
1201 l_rel_rule_id;
1202 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1203
1204 -- Delete all the related items created by the rule
1205 -- affected by the given category and deleted item
1206 IF (p_organization_id IS NULL) THEN
1207 DELETE IBE_CT_RELATED_ITEMS
1208 WHERE relation_type_code = l_relation_type_code
1209 AND relation_rule_id = l_rel_rule_id
1210 AND related_item_id = p_inventory_item_id
1211 AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
1212 ELSE
1213 DELETE IBE_CT_RELATED_ITEMS
1214 WHERE relation_type_code = l_relation_type_code
1215 AND relation_rule_id = l_rel_rule_id
1216 AND related_item_id = p_inventory_item_id
1217 AND organization_id = p_organization_id; --Bug 2922902
1218 END IF;
1219 END LOOP;
1220
1221 CLOSE l_rel_rule_csr;
1222
1223 Remove_Invalid_Exclusions();
1224 END Item_Category_Deleted;
1225
1226
1227 PROCEDURE Item_Section_Deleted(
1228 p_section_id IN NUMBER,
1229 p_inventory_item_id IN NUMBER,
1230 p_organization_id IN NUMBER DEFAULT NULL) --Bug 2922902,3001591
1231 IS
1232 TYPE rel_rule_csr_type IS REF CURSOR;
1233 l_rel_rule_csr rel_rule_csr_type;
1234 l_relation_type_code VARCHAR2(30);
1235 l_rel_rule_id NUMBER(15);
1236 BEGIN
1237 -- 1. Work on the rules where origin_type is Section
1238 -- origin_object_id is p_section_id
1239 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, '||
1240 'relation_rule_id ' ||
1241 'FROM ibe_ct_relation_rules ' ||
1242 'WHERE origin_object_type = ''S'' ' ||
1243 'AND origin_object_id = :section_id ' ||
1244 'AND relation_type_code <> ''AUTOPLACEMENT'' '
1245 USING p_section_id;
1246
1247 LOOP
1248 FETCH l_rel_rule_csr INTO l_relation_type_code,
1249 l_rel_rule_id;
1250 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1251
1252 -- Delete all the related items created by the rule
1253 -- affected by the given section and deleted item
1254 IF (p_organization_id IS NULL) THEN
1255 DELETE IBE_CT_RELATED_ITEMS
1256 WHERE relation_type_code = l_relation_type_code
1257 AND relation_rule_id = l_rel_rule_id
1258 AND inventory_item_id = p_inventory_item_id
1259 AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
1260 ELSE
1261 DELETE IBE_CT_RELATED_ITEMS
1262 WHERE relation_type_code = l_relation_type_code
1263 AND relation_rule_id = l_rel_rule_id
1264 AND inventory_item_id = p_inventory_item_id
1265 AND organization_id = p_organization_id; --Bug 2922902
1266 END IF;
1267 END LOOP;
1268 CLOSE l_rel_rule_csr;
1269
1270 -- 2. Work on the rules where dest_type is Section
1271 -- dest_object_id is p_section_id
1272 OPEN l_rel_rule_csr FOR 'SELECT relation_type_code, ' ||
1273 'relation_rule_id ' ||
1274 'FROM ibe_ct_relation_rules ' ||
1275 'WHERE dest_object_type = ''S'' ' ||
1276 'AND dest_object_id = :section_id '
1277 USING p_section_id;
1278
1279 LOOP
1280 FETCH l_rel_rule_csr INTO l_relation_type_code,
1281 l_rel_rule_id;
1282 EXIT WHEN l_rel_rule_csr%NOTFOUND;
1283
1284 -- Delete all the related items created by the rule
1285 -- affected by the given category and deleted item
1286 IF (p_organization_id IS NULL) THEN
1287 DELETE IBE_CT_RELATED_ITEMS
1288 WHERE relation_type_code = l_relation_type_code
1289 AND relation_rule_id = l_rel_rule_id
1290 AND related_item_id = p_inventory_item_id
1291 AND organization_id = L_ORGANIZATION_ID; --Bug 3001591
1292 ELSE
1293 DELETE IBE_CT_RELATED_ITEMS
1294 WHERE relation_type_code = l_relation_type_code
1295 AND relation_rule_id = l_rel_rule_id
1296 AND related_item_id = p_inventory_item_id
1297 AND organization_id = p_organization_id; --Bug 2922902
1298 END IF;
1299 END LOOP;
1300
1301 CLOSE l_rel_rule_csr;
1302
1303 Remove_Invalid_Exclusions();
1304 END Item_Section_Deleted;
1305
1306
1307 PROCEDURE Category_Deleted(
1308 p_category_id IN NUMBER)
1309 IS
1310 BEGIN
1311 -- 1. Delete all the rules where origin_object_type is Category
1312 -- and origin_object_id is p_section_id
1313 DELETE IBE_CT_RELATION_RULES
1314 WHERE origin_object_type = 'C'
1315 AND origin_object_id = p_category_id;
1316
1317 -- 2. Delete all the rules where dest_object_type is Category
1318 -- and dest_object_id is p_section_id
1319 DELETE IBE_CT_RELATION_RULES
1320 WHERE dest_object_type = 'C'
1321 AND dest_object_id = p_category_id;
1322 END Category_Deleted;
1323
1324
1325 PROCEDURE Section_Deleted(p_section_id IN NUMBER)
1326 IS
1327 BEGIN
1328 -- 1. Delete all the rules where origin_object_type is Section
1329 -- and origin_object_id is p_section_id
1330 DELETE IBE_CT_RELATION_RULES
1331 WHERE origin_object_type = 'S'
1332 AND origin_object_id = p_section_id;
1333
1334 -- 2. Delete all the rules where dest_object_type is Section
1335 -- and dest_object_id is p_section_id
1336 DELETE IBE_CT_RELATION_RULES
1337 WHERE dest_object_type = 'S'
1338 AND dest_object_id = p_section_id;
1339 END Section_Deleted;
1340
1341
1342 PROCEDURE Item_Inserted(p_inventory_item_id IN NUMBER)
1343 IS
1344 BEGIN
1345 NULL;
1346 END Item_Inserted;
1347
1348
1349 PROCEDURE Item_Deleted(
1350 p_organization_id IN NUMBER,
1351 p_inventory_item_id IN NUMBER
1352 )
1353 IS
1354 BEGIN
1355 -- 1. Remove all the rules that have the deleted item
1356 -- as an origin object
1357 DELETE
1358 FROM ibe_ct_relation_rules
1359 WHERE origin_object_type = 'I'
1360 AND origin_object_id = p_inventory_item_id;
1361
1362 -- 2. Remove all the rules that have the deleted item
1363 -- as a destination object
1364 DELETE
1365 FROM ibe_ct_relation_rules
1366 WHERE dest_object_type = 'I'
1367 AND dest_object_id = p_inventory_item_id;
1368
1369 -- 3. Remove all the rows in Related Items table
1370 -- that have the deleted item as inventory item
1371 DELETE
1372 FROM IBE_CT_RELATED_ITEMS
1373 WHERE inventory_item_id = p_inventory_item_id
1374 AND organization_id = p_organization_id;
1375
1376 -- 4. Remove all the rows in Related Items table
1377 -- that have the deleted item as related item
1378 DELETE
1379 FROM IBE_CT_RELATED_ITEMS
1380 WHERE related_item_id = p_inventory_item_id
1381 AND organization_id = p_organization_id;
1382
1383 Remove_Invalid_Exclusions();
1384 END Item_Deleted;
1385
1386 END IBE_Prod_Relation_PVT;