[Home] [Help]
PACKAGE BODY: APPS.AMS_REL_ITEM_PVT
Source
1 PACKAGE BODY AMS_REL_ITEM_PVT as
2 /* $Header: amsvritb.pls 115.11 2002/11/14 00:56:47 abhola ship $ */
3 -- Start of Comments
4 -- Package name : AMS_REL_ITEM_PVT
5 -- Purpose :
6 -- History :
7 -- 08-FEB-2001 abhola created
8 -- 17-MAY-2002 abhola removed references to g_user_id
9 -- NOTE :
10 -- End of Comments
11
12
13 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_REL_ITEM_PVT';
14 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvritb.pls';
15
16
17
18 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
19 AMS_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
20 AMS_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
21
22 PROCEDURE Complete_REL_ITEM_Rec (
23 P_REL_ITEM_Rec IN REL_ITEM_Rec_Type,
24 x_complete_rec OUT NOCOPY REL_ITEM_Rec_Type
25 );
26
27
28 -- Hint: Primary key needs to be returned.
29 PROCEDURE Create_rel_item(
30 P_Api_Version_Number IN NUMBER,
31 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
32 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
33 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
34
35 X_Return_Status OUT NOCOPY VARCHAR2,
36 X_Msg_Count OUT NOCOPY NUMBER,
37 X_Msg_Data OUT NOCOPY VARCHAR2,
38
39 P_REL_ITEM_Rec IN REL_ITEM_Rec_Type := G_MISS_REL_ITEM_REC
40 )
41
42 IS
43 l_api_name CONSTANT VARCHAR2(30) := 'Create_rel_item';
44 l_api_version_number CONSTANT NUMBER := 1.0;
45 l_return_status_full VARCHAR2(1);
46 l_object_version_number NUMBER := 1;
47 l_org_id NUMBER := FND_API.G_MISS_NUM;
48 l_owner_id NUMBER;
49 l_return_status_cue VARCHAR2(1);
50
51 Cursor Check_item IS
52 Select rowid,
53 INVENTORY_ITEM_ID,
54 ORGANIZATION_ID,
55 RELATED_ITEM_ID,
56 RELATIONSHIP_TYPE_ID,
57 RECIPROCAL_FLAG,
58 LAST_UPDATE_DATE,
59 LAST_UPDATED_BY,
60 CREATION_DATE,
61 CREATED_BY,
62 LAST_UPDATE_LOGIN,
63 REQUEST_ID,
64 PROGRAM_APPLICATION_ID,
65 PROGRAM_ID,
66 PROGRAM_UPDATE_DATE
67 From MTL_RELATED_ITEMS
68 WHERE inventory_item_id = P_REL_ITEM_Rec.inventory_item_id
69 AND organization_id = P_REL_ITEM_Rec.organization_id
70 AND related_item_id = P_REL_ITEM_Rec.related_item_id
71 AND relationship_type_id = P_REL_ITEM_Rec.relationship_type_id;
72
73 check_item_row Check_item%ROWTYPE;
74
75 Cursor Get_owner_id IS
76 SELECT item_owner_id
77 FROM ams_item_attributes
78 WHERE inventory_item_id = P_REL_ITEM_Rec.inventory_item_id
79 AND organization_id = P_REL_ITEM_rec.organization_id;
80
81 BEGIN
82 -- Standard Start of API savepoint
83 SAVEPOINT CREATE_REL_ITEM_PVT;
84
85 -- Standard call to check for call compatibility.
86 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
87 p_api_version_number,
88 l_api_name,
89 G_PKG_NAME)
90 THEN
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 -- Initialize message list if p_init_msg_list is set to TRUE.
95 IF FND_API.to_Boolean( p_init_msg_list )
96 THEN
97 FND_MSG_PUB.initialize;
98 END IF;
99
100 -- Debug Message
101 IF (AMS_DEBUG_HIGH_ON) THEN
102
103 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
104 END IF;
105
106
107 -- Initialize API return status to SUCCESS
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 -- ******************************************************************
111 -- Validate Environment
112 -- ******************************************************************
113 IF FND_GLOBAL.User_Id IS NULL
114 THEN
115 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
116 THEN
117 FND_MESSAGE.Set_Name('AMS', 'USER_PROFILE_MISSING');
118 FND_MSG_PUB.ADD;
119 END IF;
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122
123
124 -- *******************************************************************
125 -- Check for Duplicate Items
126 -- ******************************************************************
127
128 OPEN Check_item;
129 FETCH Check_item INTO Check_item_row;
130
131 if (Check_item%FOUND) then
132 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
133 THEN
134 FND_MESSAGE.Set_Name('AMS', 'AMS_PROD_DUP_REL');
135 FND_MSG_PUB.ADD;
136 END IF;
137 CLOSE Check_item;
138 RAISE FND_API.G_EXC_ERROR ;
139 end if;
140
141 CLOSE Check_item;
142
143
144 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
145 THEN
146 -- Debug message
147 IF (AMS_DEBUG_HIGH_ON) THEN
148
149 AMS_UTILITY_PVT.debug_message('Private API: Validate_rel_item');
150 END IF;
151
152 -- Invoke validation procedures
153 Validate_rel_item(
154 p_api_version_number => 1.0,
155 p_init_msg_list => FND_API.G_FALSE,
156 p_validation_level => p_validation_level,
157 P_REL_ITEM_Rec => P_REL_ITEM_Rec,
158 x_return_status => x_return_status,
159 x_msg_count => x_msg_count,
160 x_msg_data => x_msg_data);
161 END IF;
162
163 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
164 RAISE FND_API.G_EXC_ERROR;
165 END IF;
166
167
168 -- Debug Message
169 IF (AMS_DEBUG_HIGH_ON) THEN
170
171 AMS_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
172 END IF;
173
174 -- ******************************************************************
175 -- Item Cannot be realted to it self.
176 -- ******************************************************************
177 IF (p_REL_ITEM_rec.INVENTORY_ITEM_ID = p_REL_ITEM_rec.RELATED_ITEM_ID)
178 THEN
179 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
180 THEN
181 FND_MESSAGE.Set_Name('AMS', 'AMS_INVALID_ITM_REL');
182 FND_MSG_PUB.ADD;
183 END IF;
184 RAISE FND_API.G_EXC_ERROR;
185 END IF;
186
187 -- *******************************************************************
188 -- Invoke table handler(AMS_RELATED_ITEMS_PKG.Insert_Row)
189 -- ******************************************************************
190
191 AMS_RELATED_ITEMS_PKG.Insert_Row(
192 p_INVENTORY_ITEM_ID => p_REL_ITEM_rec.INVENTORY_ITEM_ID,
193 p_ORGANIZATION_ID => p_REL_ITEM_rec.ORGANIZATION_ID,
194 p_RELATED_ITEM_ID => p_REL_ITEM_rec.RELATED_ITEM_ID,
195 p_RELATIONSHIP_TYPE_ID => p_REL_ITEM_rec.RELATIONSHIP_TYPE_ID,
196 p_RECIPROCAL_FLAG => p_REL_ITEM_rec.RECIPROCAL_FLAG,
197 p_LAST_UPDATE_DATE => SYSDATE,
198 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
199 p_CREATION_DATE => SYSDATE,
200 p_CREATED_BY => FND_GLOBAL.USER_ID,
201 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
202 p_REQUEST_ID => p_REL_ITEM_rec.REQUEST_ID,
203 p_PROGRAM_APPLICATION_ID => p_REL_ITEM_rec.PROGRAM_APPLICATION_ID,
204 p_PROGRAM_ID => p_REL_ITEM_rec.PROGRAM_ID,
205 p_PROGRAM_UPDATE_DATE => p_REL_ITEM_rec.PROGRAM_UPDATE_DATE);
206 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
207 RAISE FND_API.G_EXC_ERROR;
208 END IF;
209
210 /** commneted by abhola 05/17/01
211 ELSE
212 OPEN get_owner_id;
213 FETCH get_owner_id INTO l_owner_id;
214 CLOSE get_owner_id;
215
216 -- ************************************************************************
217 -- call for cue cards.
218 -- ************************************************************************
219
220 AMS_ObjectAttribute_PVT.modify_object_attribute(
221 p_api_version => l_api_version_number,
222 p_init_msg_list => FND_API.g_false,
223 p_commit => FND_API.g_false,
224 p_validation_level => FND_API.g_valid_level_full,
225
226 x_return_status => l_return_status_cue,
227 x_msg_count => x_msg_count,
228 x_msg_data => x_msg_data,
229
230 p_object_type => 'PROD',
231 p_object_id => l_owner_id ,
232 p_attr => 'RPRD',
233 p_attr_defined_flag => 'Y'
234 );
235
236 END IF;
237 **/
238
239
240
241
242
243 -- End of API body
244 --
245
246 -- Standard check for p_commit
247 IF FND_API.to_Boolean( p_commit )
248 THEN
249 COMMIT WORK;
250 END IF;
251
252
253 -- Debug Message
254 IF (AMS_DEBUG_HIGH_ON) THEN
255
256 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
257 END IF;
258
259 -- Standard call to get message count and if count is 1, get message info.
260 FND_MSG_PUB.Count_And_Get
261 (p_count => x_msg_count,
262 p_data => x_msg_data
263 );
264 EXCEPTION
265 WHEN FND_API.G_EXC_ERROR THEN
266 ROLLBACK TO CREATE_REL_ITEM_PVT;
267 x_return_status := FND_API.G_RET_STS_ERROR;
268 -- Standard call to get message count and if count=1, get the message
269 FND_MSG_PUB.Count_And_Get (
270 p_encoded => FND_API.G_FALSE,
271 p_count => x_msg_count,
272 p_data => x_msg_data
273 );
274 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
275 ROLLBACK TO CREATE_REL_ITEM_PVT;
276 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
277 -- Standard call to get message count and if count=1, get the message
278 FND_MSG_PUB.Count_And_Get (
279 p_encoded => FND_API.G_FALSE,
280 p_count => x_msg_count,
281 p_data => x_msg_data
282 );
283 WHEN OTHERS THEN
284 ROLLBACK TO CREATE_REL_ITEM_PVT;
285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
286 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
287 THEN
288 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
289 END IF;
290 -- Standard call to get message count and if count=1, get the message
291 FND_MSG_PUB.Count_And_Get (
292 p_encoded => FND_API.G_FALSE,
293 p_count => x_msg_count,
294 p_data => x_msg_data
295 );
296 End Create_rel_item;
297
298
299 PROCEDURE Update_rel_item(
300 P_Api_Version_Number IN NUMBER,
301 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
302 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
303 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
304
305 X_Return_Status OUT NOCOPY VARCHAR2,
306 X_Msg_Count OUT NOCOPY NUMBER,
307 X_Msg_Data OUT NOCOPY VARCHAR2,
308
309 P_REL_ITEM_Rec IN REL_ITEM_Rec_Type
310 )
311
312 IS
313
314 Cursor C_Get_rel_item IS
315 Select rowid,
316 INVENTORY_ITEM_ID,
317 ORGANIZATION_ID,
318 RELATED_ITEM_ID,
319 RELATIONSHIP_TYPE_ID,
320 RECIPROCAL_FLAG,
321 LAST_UPDATE_DATE,
322 LAST_UPDATED_BY,
323 CREATION_DATE,
324 CREATED_BY,
325 LAST_UPDATE_LOGIN,
326 REQUEST_ID,
327 PROGRAM_APPLICATION_ID,
328 PROGRAM_ID,
329 PROGRAM_UPDATE_DATE
330 From MTL_RELATED_ITEMS
331 WHERE inventory_item_id = P_REL_ITEM_Rec.inventory_item_id
332 AND organization_id = P_REL_ITEM_Rec.organization_id
333 AND related_item_id = P_REL_ITEM_Rec.related_item_id
334 AND relationship_type_id = P_REL_ITEM_Rec.relationship_type_id;
335
336
337 l_api_name CONSTANT VARCHAR2(30) := 'Update_rel_item';
338 l_api_version_number CONSTANT NUMBER := 1.0;
339 -- Local Variables
340 l_object_version_number NUMBER;
341 l_RELATED_ITEM_ID NUMBER;
342 l_ref_REL_ITEM_rec AMS_rel_item_PVT.REL_ITEM_Rec_Type;
343 l_tar_REL_ITEM_rec AMS_rel_item_PVT.REL_ITEM_Rec_Type := P_REL_ITEM_Rec;
344 l_rowid ROWID;
345
346 BEGIN
347 -- Standard Start of API savepoint
348 SAVEPOINT UPDATE_REL_ITEM_PVT;
349
350 -- Standard call to check for call compatibility.
351 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
352 p_api_version_number,
353 l_api_name,
354 G_PKG_NAME)
355 THEN
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END IF;
358
359 -- Initialize message list if p_init_msg_list is set to TRUE.
360 IF FND_API.to_Boolean( p_init_msg_list )
361 THEN
362 FND_MSG_PUB.initialize;
363 END IF;
364
365 -- Debug Message
366 IF (AMS_DEBUG_HIGH_ON) THEN
367
368 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
369 END IF;
370
371
372 -- Initialize API return status to SUCCESS
373 x_return_status := FND_API.G_RET_STS_SUCCESS;
374
375 -- Debug Message
376 IF (AMS_DEBUG_HIGH_ON) THEN
377
378 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
379 END IF;
380
381
382 Open C_Get_rel_item;
383
384 Fetch C_Get_rel_item into
385 l_rowid,
386 l_ref_REL_ITEM_rec.INVENTORY_ITEM_ID,
387 l_ref_REL_ITEM_rec.ORGANIZATION_ID,
388 l_ref_REL_ITEM_rec.RELATED_ITEM_ID,
389 l_ref_REL_ITEM_rec.RELATIONSHIP_TYPE_ID,
390 l_ref_REL_ITEM_rec.RECIPROCAL_FLAG,
391 l_ref_REL_ITEM_rec.LAST_UPDATE_DATE,
392 l_ref_REL_ITEM_rec.LAST_UPDATED_BY,
393 l_ref_REL_ITEM_rec.CREATION_DATE,
394 l_ref_REL_ITEM_rec.CREATED_BY,
395 l_ref_REL_ITEM_rec.LAST_UPDATE_LOGIN,
396 l_ref_REL_ITEM_rec.REQUEST_ID,
397 l_ref_REL_ITEM_rec.PROGRAM_APPLICATION_ID,
398 l_ref_REL_ITEM_rec.PROGRAM_ID,
399 l_ref_REL_ITEM_rec.PROGRAM_UPDATE_DATE;
400
401 If ( C_Get_rel_item%NOTFOUND) Then
402 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
403 THEN
404 FND_MESSAGE.Set_Name('AMS', 'API_MISSING_UPDATE_TARGET');
405 FND_MESSAGE.Set_Token ('INFO', 'rel_item', FALSE);
406 FND_MSG_PUB.Add;
407 END IF;
408 raise FND_API.G_EXC_ERROR;
409 END IF;
410 -- Debug Message
411 IF (AMS_DEBUG_HIGH_ON) THEN
412
413 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
414 END IF;
415 Close C_Get_rel_item;
416
417 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
418 THEN
419 -- Debug message
420 IF (AMS_DEBUG_HIGH_ON) THEN
421
422 AMS_UTILITY_PVT.debug_message('Private API: Validate_rel_item');
423 END IF;
424
425 -- Invoke validation procedures
426 Validate_rel_item(
427 p_api_version_number => 1.0,
428 p_init_msg_list => FND_API.G_FALSE,
429 p_validation_level => p_validation_level,
430 P_REL_ITEM_Rec => P_REL_ITEM_Rec,
431 x_return_status => x_return_status,
432 x_msg_count => x_msg_count,
433 x_msg_data => x_msg_data);
434 END IF;
435
436 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439
440
441 -- Debug Message
442
443 -- Invoke table handler(AMS_RELATED_ITEMS_PKG.Update_Row)
444 AMS_RELATED_ITEMS_PKG.Update_Row(
445 p_INVENTORY_ITEM_ID => p_REL_ITEM_rec.INVENTORY_ITEM_ID,
446 p_ORGANIZATION_ID => p_REL_ITEM_rec.ORGANIZATION_ID,
447 p_RELATED_ITEM_ID => p_REL_ITEM_rec.RELATED_ITEM_ID,
448 p_RELATIONSHIP_TYPE_ID => p_REL_ITEM_rec.RELATIONSHIP_TYPE_ID,
449 p_RECIPROCAL_FLAG => p_REL_ITEM_rec.RECIPROCAL_FLAG,
450 p_LAST_UPDATE_DATE => SYSDATE,
451 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
452 p_CREATION_DATE => SYSDATE,
453 p_CREATED_BY => FND_GLOBAL.USER_ID,
454 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
455 p_REQUEST_ID => p_REL_ITEM_rec.REQUEST_ID,
456 p_PROGRAM_APPLICATION_ID => p_REL_ITEM_rec.PROGRAM_APPLICATION_ID,
457 p_PROGRAM_ID => p_REL_ITEM_rec.PROGRAM_ID,
458 p_PROGRAM_UPDATE_DATE => p_REL_ITEM_rec.PROGRAM_UPDATE_DATE);
459 --
460 -- End of API body.
461 --
462
463 -- Standard check for p_commit
464 IF FND_API.to_Boolean( p_commit )
465 THEN
466 COMMIT WORK;
467 END IF;
468
469
470 -- Debug Message
471 IF (AMS_DEBUG_HIGH_ON) THEN
472
473 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
474 END IF;
475
476 -- Standard call to get message count and if count is 1, get message info.
477 FND_MSG_PUB.Count_And_Get
478 (p_count => x_msg_count,
479 p_data => x_msg_data
480 );
481 EXCEPTION
482 WHEN FND_API.G_EXC_ERROR THEN
483 ROLLBACK TO UPDATE_REL_ITEM_PVT;
484 x_return_status := FND_API.G_RET_STS_ERROR;
485 -- Standard call to get message count and if count=1, get the message
486 FND_MSG_PUB.Count_And_Get (
487 p_encoded => FND_API.G_FALSE,
488 p_count => x_msg_count,
489 p_data => x_msg_data
490 );
491 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
492 ROLLBACK TO UPDATE_REL_ITEM_PVT;
493 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
494 -- Standard call to get message count and if count=1, get the message
495 FND_MSG_PUB.Count_And_Get (
496 p_encoded => FND_API.G_FALSE,
497 p_count => x_msg_count,
498 p_data => x_msg_data
499 );
500 WHEN OTHERS THEN
501 ROLLBACK TO UPDATE_REL_ITEM_PVT;
502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
503 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
504 THEN
505 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
506 END IF;
507 -- Standard call to get message count and if count=1, get the message
508 FND_MSG_PUB.Count_And_Get (
509 p_encoded => FND_API.G_FALSE,
510 p_count => x_msg_count,
511 p_data => x_msg_data
512 );
513 End Update_rel_item;
514
515
516 PROCEDURE Delete_rel_item(
517 P_Api_Version_Number IN NUMBER,
518 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
519 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
520 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
521 X_Return_Status OUT NOCOPY VARCHAR2,
522 X_Msg_Count OUT NOCOPY NUMBER,
523 X_Msg_Data OUT NOCOPY VARCHAR2,
524 P_REL_ITEM_Rec IN REL_ITEM_Rec_Type
525 )
526
527 IS
528 l_api_name CONSTANT VARCHAR2(30) := 'Delete_rel_item';
529 l_api_version_number CONSTANT NUMBER := 1.0;
530
531 l_dummy number :=0;
532 l_return_status VARCHAR2(1) ;
533 l_item_owner_id NUMBER;
534
535 Cursor check_item is
536 SELECT 1
537 FROM mtl_related_items
538 WHERE organization_id = P_REL_ITEM_Rec.organization_id
539 AND inventory_item_id = p_REL_ITEM_Rec.inventory_item_id;
540 --AND related_item_id = P_REL_ITEM_Rec.related_item_id
541 --AND relationship_type_id = P_REL_ITEM_Rec.relationship_type_id;
542
543
544
545 Cursor Get_owner_id IS
546 SELECT item_owner_id
547 FROM ams_item_attributes
548 WHERE inventory_item_id = P_REL_ITEM_Rec.inventory_item_id
549 AND organization_id = P_REL_ITEM_rec.organization_id;
550
551 BEGIN
552 -- Standard Start of API savepoint
553 SAVEPOINT DELETE_REL_ITEM_PVT;
554
555 -- Standard call to check for call compatibility.
556 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
557 p_api_version_number,
558 l_api_name,
559 G_PKG_NAME)
560 THEN
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562 END IF;
563
564 -- Initialize message list if p_init_msg_list is set to TRUE.
565 IF FND_API.to_Boolean( p_init_msg_list )
566 THEN
567 FND_MSG_PUB.initialize;
568 END IF;
569
570 -- Debug Message
571 IF (AMS_DEBUG_HIGH_ON) THEN
572
573 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
574 END IF;
575
576
577 -- Initialize API return status to SUCCESS
578 x_return_status := FND_API.G_RET_STS_SUCCESS;
579
580 --
581 -- Api body
582 --
583 -- Debug Message
584 IF (AMS_DEBUG_HIGH_ON) THEN
585
586 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
587 END IF;
588
589 -- Invoke table handler(AMS_RELATED_ITEMS_PKG.Delete_Row
590
591 AMS_RELATED_ITEMS_PKG.Delete_Row(
592 p_INVENTORY_ITEM_ID => p_REL_ITEM_rec.INVENTORY_ITEM_ID,
593 p_ORGANIZATION_ID => p_REL_ITEM_rec.ORGANIZATION_ID,
594 p_RELATED_ITEM_ID => p_REL_ITEM_rec.RELATED_ITEM_ID,
595 p_RELATIONSHIP_TYPE_ID => p_REL_ITEM_rec.RELATIONSHIP_TYPE_ID,
596 p_RECIPROCAL_FLAG => p_REL_ITEM_rec.RECIPROCAL_FLAG,
597 p_LAST_UPDATE_DATE => SYSDATE,
598 p_LAST_UPDATED_BY => FND_GLOBAL.USER_ID,
599 p_CREATION_DATE => SYSDATE,
600 p_CREATED_BY => FND_GLOBAL.USER_ID,
601 p_LAST_UPDATE_LOGIN => FND_GLOBAL.CONC_LOGIN_ID,
602 p_REQUEST_ID => p_REL_ITEM_rec.REQUEST_ID,
603 p_PROGRAM_APPLICATION_ID => p_REL_ITEM_rec.PROGRAM_APPLICATION_ID,
604 p_PROGRAM_ID => p_REL_ITEM_rec.PROGRAM_ID,
605 p_PROGRAM_UPDATE_DATE => p_REL_ITEM_rec.PROGRAM_UPDATE_DATE);
606
607 /** commented CUE CARD code on 5/17/01 by ABHOLA
608 -- ************************************************************
609 -- Call for cue card
610 -- ************************************************************
611 OPEN check_item;
612 FETCH check_item INTO l_dummy;
613 CLOSE check_item;
614
615 OPEN get_owner_id;
616 FETCH get_owner_id INTO l_item_owner_id;
617 CLOSE get_owner_id;
618
619 IF l_dummy =1 THEN
620
621 AMS_ObjectAttribute_PVT.modify_object_attribute(
622 p_api_version => l_api_version_number,
623 p_init_msg_list => FND_API.g_false,
624 p_commit => FND_API.g_false,
625 p_validation_level => FND_API.g_valid_level_full,
626
627 x_return_status => l_return_status,
628 x_msg_count => x_msg_count,
629 x_msg_data => x_msg_data,
630
631 p_object_type => 'PROD',
632 p_object_id => l_item_owner_id ,
633 p_attr => 'RPRD',
634 p_attr_defined_flag => 'Y'
635 );
636
637 ELSE
638
639 AMS_ObjectAttribute_PVT.modify_object_attribute(
640 p_api_version => l_api_version_number,
641 p_init_msg_list => FND_API.g_false,
642 p_commit => FND_API.g_false,
643 p_validation_level => FND_API.g_valid_level_full,
644
645 x_return_status => l_return_status,
646 x_msg_count => x_msg_count,
647 x_msg_data => x_msg_data,
648
649 p_object_type => 'PROD',
650 p_object_id => l_item_owner_id ,
651 p_attr => 'RPRD',
652 p_attr_defined_flag => 'N'
653 );
654
655 END IF;
656
657 ************* end of commented code ****************************/
658
659 --
660 -- End of API body
661 --
662
663 -- Standard check for p_commit
664 IF FND_API.to_Boolean( p_commit )
665 THEN
666 COMMIT WORK;
667 END IF;
668
669
670 -- Debug Message
671 IF (AMS_DEBUG_HIGH_ON) THEN
672
673 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
674 END IF;
675
676 -- Standard call to get message count and if count is 1, get message info.
677 FND_MSG_PUB.Count_And_Get
678 (p_count => x_msg_count,
679 p_data => x_msg_data
680 );
681 EXCEPTION
682 WHEN FND_API.G_EXC_ERROR THEN
683 ROLLBACK TO DELETE_REL_ITEM_PVT;
684 x_return_status := FND_API.G_RET_STS_ERROR;
685 -- Standard call to get message count and if count=1, get the message
686 FND_MSG_PUB.Count_And_Get (
687 p_encoded => FND_API.G_FALSE,
688 p_count => x_msg_count,
689 p_data => x_msg_data
690 );
691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
692 ROLLBACK TO DELETE_REL_ITEM_PVT;
693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
694 -- Standard call to get message count and if count=1, get the message
695 FND_MSG_PUB.Count_And_Get (
696 p_encoded => FND_API.G_FALSE,
697 p_count => x_msg_count,
698 p_data => x_msg_data
699 );
700 WHEN OTHERS THEN
701 ROLLBACK TO DELETE_REL_ITEM_PVT;
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
704 THEN
705 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
706 END IF;
707 -- Standard call to get message count and if count=1, get the message
708 FND_MSG_PUB.Count_And_Get (
709 p_encoded => FND_API.G_FALSE,
710 p_count => x_msg_count,
711 p_data => x_msg_data
712 );
713 End Delete_rel_item;
714
715
716 PROCEDURE Check_REL_ITEM_Items (
717 P_REL_ITEM_Rec IN REL_ITEM_Rec_Type,
718 p_validation_mode IN VARCHAR2,
719 x_return_status OUT NOCOPY VARCHAR2
720 )
721 IS
722 BEGIN
723
724 --
725 -- Check Items API calls
726 NULL;
727 --
728
729 END Check_REL_ITEM_Items;
730
731 PROCEDURE Complete_REL_ITEM_Rec (
732 P_REL_ITEM_Rec IN REL_ITEM_Rec_Type,
733 x_complete_rec OUT NOCOPY REL_ITEM_Rec_Type
734 )
735 IS
736
737
738 CURSOR c_rel_item_rec IS
739 SELECT *
740 FROM mtl_related_items
741 WHERE inventory_item_id = P_REL_ITEM_Rec.inventory_item_id
742 AND organization_id = P_REL_ITEM_Rec.organization_id
743 AND related_item_id = P_REL_ITEM_Rec.related_item_id
744 AND relationship_type_id = P_REL_ITEM_Rec.relationship_type_id;
745
746 l_rel_item_rec c_rel_item_rec%ROWTYPE;
747
748 BEGIN
749
750 x_complete_rec := P_REL_ITEM_Rec;
751
752 OPEN c_rel_item_rec;
753 FETCH c_rel_item_rec INTO l_rel_item_rec;
754 IF c_rel_item_rec%NOTFOUND THEN
755 CLOSE c_rel_item_rec;
756 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
757 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
758 FND_MSG_PUB.add;
759 END IF;
760 RAISE FND_API.g_exc_error;
761 END IF;
762 CLOSE c_rel_item_rec;
763
764 IF P_REL_ITEM_Rec.inventory_item_id = FND_API.g_miss_num THEN
765 x_complete_rec.inventory_item_id := l_rel_item_rec.inventory_item_id;
766 END IF;
767
768 IF P_REL_ITEM_Rec.organization_id = FND_API.g_miss_num THEN
769 x_complete_rec.organization_id := l_rel_item_rec.organization_id;
770 END IF;
771
772
773 IF P_REL_ITEM_Rec.related_item_id = FND_API.g_miss_num THEN
774 x_complete_rec.related_item_id := l_rel_item_rec.related_item_id;
775 END IF;
776
777
778 IF P_REL_ITEM_Rec.relationship_type_id = FND_API.g_miss_num THEN
779 x_complete_rec.relationship_type_id := l_rel_item_rec.relationship_type_id;
780 END IF;
781
782
783 IF P_REL_ITEM_Rec.reciprocal_flag = FND_API.g_miss_char THEN
784 x_complete_rec.reciprocal_flag := l_rel_item_rec.reciprocal_flag;
785 END IF;
786
787
788 IF P_REL_ITEM_Rec.request_id = FND_API.g_miss_num THEN
789 x_complete_rec.request_id := l_rel_item_rec.request_id;
790 END IF;
791
792
793 IF P_REL_ITEM_Rec.program_application_id = FND_API.g_miss_num THEN
794 x_complete_rec.program_application_id := l_rel_item_rec.program_application_id;
795 END IF;
796
797
798 IF P_REL_ITEM_Rec.program_id = FND_API.g_miss_num THEN
799 x_complete_rec.program_id := l_rel_item_rec.program_id;
800 END IF;
801
802
803 IF P_REL_ITEM_Rec.program_update_date = FND_API.g_miss_date THEN
804 x_complete_rec.program_update_date := l_rel_item_rec.program_update_date;
805 END IF;
806
807 END Complete_REL_ITEM_Rec;
808
809 PROCEDURE Validate_rel_item(
810 P_Api_Version_Number IN NUMBER,
811 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
812 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
813 P_REL_ITEM_Rec IN REL_ITEM_Rec_Type,
814 X_Return_Status OUT NOCOPY VARCHAR2,
815 X_Msg_Count OUT NOCOPY NUMBER,
816 X_Msg_Data OUT NOCOPY VARCHAR2
817 )
818 IS
819 l_api_name CONSTANT VARCHAR2(30) := 'Validate_rel_item';
820 l_api_version_number CONSTANT NUMBER := 1.0;
821 l_object_version_number NUMBER;
822 l_REL_ITEM_rec AMS_rel_item_PVT.REL_ITEM_Rec_Type;
823
824 BEGIN
825 -- Standard Start of API savepoint
826 SAVEPOINT VALIDATE_REL_ITEM_;
827
828 -- Standard call to check for call compatibility.
829 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
830 p_api_version_number,
831 l_api_name,
832 G_PKG_NAME)
833 THEN
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 END IF;
836
837 -- Initialize message list if p_init_msg_list is set to TRUE.
838 IF FND_API.to_Boolean( p_init_msg_list )
839 THEN
840 FND_MSG_PUB.initialize;
841 END IF;
842 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
843 Check_REL_ITEM_Items(
844 p_REL_ITEM_rec => p_REL_ITEM_rec,
845 p_validation_mode => JTF_PLSQL_API.g_update,
846 x_return_status => x_return_status
847 );
848
849 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
850 RAISE FND_API.G_EXC_ERROR;
851 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
852 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
853 END IF;
854 END IF;
855
856 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
857 Validate_REL_ITEM_Rec(
858 p_api_version_number => 1.0,
859 p_init_msg_list => FND_API.G_FALSE,
860 x_return_status => x_return_status,
861 x_msg_count => x_msg_count,
862 x_msg_data => x_msg_data,
863 P_REL_ITEM_Rec => l_REL_ITEM_Rec);
864
865 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
866 RAISE FND_API.G_EXC_ERROR;
867 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
869 END IF;
870 END IF;
871
872
873 -- Debug Message
874 IF (AMS_DEBUG_HIGH_ON) THEN
875
876 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
877 END IF;
878
879
880 -- Initialize API return status to SUCCESS
881 x_return_status := FND_API.G_RET_STS_SUCCESS;
882
883
884 -- Debug Message
885 IF (AMS_DEBUG_HIGH_ON) THEN
886
887 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
888 END IF;
889
890 -- Standard call to get message count and if count is 1, get message info.
891 FND_MSG_PUB.Count_And_Get
892 (p_count => x_msg_count,
893 p_data => x_msg_data
894 );
895 EXCEPTION
896 WHEN FND_API.G_EXC_ERROR THEN
897 ROLLBACK TO VALIDATE_REL_ITEM_;
898 x_return_status := FND_API.G_RET_STS_ERROR;
899 -- Standard call to get message count and if count=1, get the message
900 FND_MSG_PUB.Count_And_Get (
901 p_encoded => FND_API.G_FALSE,
902 p_count => x_msg_count,
903 p_data => x_msg_data
904 );
905 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
906 ROLLBACK TO VALIDATE_REL_ITEM_;
907 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
908 -- Standard call to get message count and if count=1, get the message
909 FND_MSG_PUB.Count_And_Get (
910 p_encoded => FND_API.G_FALSE,
911 p_count => x_msg_count,
912 p_data => x_msg_data
913 );
914 WHEN OTHERS THEN
915 ROLLBACK TO VALIDATE_REL_ITEM_;
916 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
917 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
918 THEN
919 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
920 END IF;
921 -- Standard call to get message count and if count=1, get the message
922 FND_MSG_PUB.Count_And_Get (
923 p_encoded => FND_API.G_FALSE,
924 p_count => x_msg_count,
925 p_data => x_msg_data
926 );
927 End Validate_rel_item;
928
929
930 PROCEDURE Validate_REL_ITEM_rec(
931 P_Api_Version_Number IN NUMBER,
932 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
933 X_Return_Status OUT NOCOPY VARCHAR2,
934 X_Msg_Count OUT NOCOPY NUMBER,
935 X_Msg_Data OUT NOCOPY VARCHAR2,
936 P_REL_ITEM_Rec IN REL_ITEM_Rec_Type
937 )
938 IS
939 BEGIN
940 -- Initialize message list if p_init_msg_list is set to TRUE.
941 IF FND_API.to_Boolean( p_init_msg_list )
942 THEN
943 FND_MSG_PUB.initialize;
944 END IF;
945
946 -- Initialize API return status to SUCCESS
947 x_return_status := FND_API.G_RET_STS_SUCCESS;
948
949
950 -- Debug Message
951 -- IF (AMS_DEBUG_HIGH_ON) THEN AMS_UTILITY_PVT.debug_message('API_INVALID_RECORD'); END IF;
952
953
954
955 FND_MSG_PUB.Count_And_Get
956 (p_count => x_msg_count,
957 p_data => x_msg_data
958 );
959 END Validate_REL_ITEM_Rec;
960
961 End AMS_REL_ITEM_PVT;