[Home] [Help]
PACKAGE BODY: APPS.OZF_ADJ_NEW_PROD_PVT
Source
1 PACKAGE BODY OZF_Adj_New_Prod_PVT as
2 /* $Header: ozfvanpb.pls 120.1 2006/03/30 13:52:58 rssharma noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Adj_New_Prod_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Adj_New_Prod_PVT';
16 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvanpb.pls';
17
18 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
19 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
20
21 -- Hint: Primary key needs to be returned.
22 PROCEDURE Create_Adj_New_Prod(
23 p_api_version_number IN NUMBER,
24 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
25 p_commit IN VARCHAR2 := FND_API.G_FALSE,
26 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
27
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2,
31
32 p_adj_new_prod_rec IN adj_new_prod_rec_type := g_miss_adj_new_prod_rec,
33 x_offer_adj_new_product_id OUT NOCOPY NUMBER
34 )
35
36 IS
37 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Adj_New_Prod';
38 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
39 l_return_status_full VARCHAR2(1);
40 l_object_version_number NUMBER := 1;
41 l_org_id NUMBER := NULL;
42 l_OFFER_ADJ_NEW_PRODUCT_ID NUMBER;
43 l_dummy NUMBER;
44
45 CURSOR c_id IS
46 SELECT OZF_OFFER_ADJ_NEW_PRODUCTS_s.NEXTVAL
47 FROM dual;
48
49 CURSOR c_id_exists (l_id IN NUMBER) IS
50 SELECT 1
51 FROM OZF_OFFER_ADJ_NEW_PRODUCTS
52 WHERE OFFER_ADJ_NEW_PRODUCT_ID = l_id;
53
54 BEGIN
55 -- Standard Start of API savepoint
56 SAVEPOINT CREATE_Adj_New_Prod_PVT;
57
58 -- Standard call to check for call compatibility.
59 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
60 p_api_version_number,
61 l_api_name,
62 G_PKG_NAME)
63 THEN
64 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
65 END IF;
66
67 -- Initialize message list if p_init_msg_list is set to TRUE.
68 IF FND_API.to_Boolean( p_init_msg_list )
69 THEN
70 FND_MSG_PUB.initialize;
71 END IF;
72
73 -- Debug Message
74 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
75
76
77 -- Initialize API return status to SUCCESS
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 -- Local variable initialization
81
82 IF p_adj_new_prod_rec.OFFER_ADJ_NEW_PRODUCT_ID IS NULL OR p_adj_new_prod_rec.OFFER_ADJ_NEW_PRODUCT_ID = FND_API.g_miss_num THEN
83 LOOP
84 l_dummy := NULL;
85 OPEN c_id;
86 FETCH c_id INTO l_OFFER_ADJ_NEW_PRODUCT_ID;
87 CLOSE c_id;
88
89 OPEN c_id_exists(l_OFFER_ADJ_NEW_PRODUCT_ID);
90 FETCH c_id_exists INTO l_dummy;
91 CLOSE c_id_exists;
92 EXIT WHEN l_dummy IS NULL;
93 END LOOP;
94 END IF;
95
96 -- =========================================================================
97 -- Validate Environment
98 -- =========================================================================
99
100 IF FND_GLOBAL.User_Id IS NULL
101 THEN
102 OZF_UTILITY_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
103 RAISE FND_API.G_EXC_ERROR;
104 END IF;
105
106 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
107 THEN
108 -- Debug message
109 OZF_UTILITY_PVT.debug_message('Private API: Validate_Adj_New_Prod');
110
111 -- Invoke validation procedures
112 Validate_adj_new_prod(
113 p_api_version_number => 1.0,
114 p_init_msg_list => FND_API.G_FALSE,
115 p_validation_level => p_validation_level,
116 p_validation_mode => JTF_PLSQL_API.g_create,
117 p_adj_new_prod_rec => p_adj_new_prod_rec,
118 x_return_status => x_return_status,
119 x_msg_count => x_msg_count,
120 x_msg_data => x_msg_data);
121 END IF;
122
123 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
124 RAISE FND_API.G_EXC_ERROR;
125 END IF;
126
127
128 -- Debug Message
129 OZF_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
130
131 -- Invoke table handler(OZF_OFFER_ADJ_NEW_PRODUCTS_PKG.Insert_Row)
132 OZF_OFFER_ADJ_NEW_PRODUCTS_PKG.Insert_Row(
133 px_offer_adj_new_product_id => l_offer_adj_new_product_id,
134 p_offer_adj_new_line_id => p_adj_new_prod_rec.offer_adj_new_line_id,
135 p_offer_adjustment_id => p_adj_new_prod_rec.offer_adjustment_id,
136 p_product_context => p_adj_new_prod_rec.product_context,
137 p_product_attribute => p_adj_new_prod_rec.product_attribute,
138 p_product_attr_value => p_adj_new_prod_rec.product_attr_value,
139 p_excluder_flag => p_adj_new_prod_rec.excluder_flag,
140 p_uom_code => p_adj_new_prod_rec.uom_code,
141 p_creation_date => SYSDATE,
142 p_created_by => G_USER_ID,
143 p_last_update_date => SYSDATE,
144 p_last_updated_by => G_USER_ID,
145 p_last_update_login => G_LOGIN_ID,
146 px_object_version_number => l_object_version_number);
147 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
148 RAISE FND_API.G_EXC_ERROR;
149 END IF;
150 --
151 -- End of API body
152 --
153
154 x_offer_adj_new_product_id:= l_offer_adj_new_product_id;
155 -- Standard check for p_commit
156 IF FND_API.to_Boolean( p_commit )
157 THEN
158 COMMIT WORK;
159 END IF;
160
161
162 -- Debug Message
163 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
164
165 -- Standard call to get message count and if count is 1, get message info.
166 FND_MSG_PUB.Count_And_Get
167 (p_count => x_msg_count,
168 p_data => x_msg_data
169 );
170 EXCEPTION
171
172 WHEN OZF_UTILITY_PVT.resource_locked THEN
173 x_return_status := FND_API.g_ret_sts_error;
174 OZF_UTILITY_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
175
176 WHEN FND_API.G_EXC_ERROR THEN
177 ROLLBACK TO CREATE_Adj_New_Prod_PVT;
178 x_return_status := FND_API.G_RET_STS_ERROR;
179 -- Standard call to get message count and if count=1, get the message
180 FND_MSG_PUB.Count_And_Get (
181 p_encoded => FND_API.G_FALSE,
182 p_count => x_msg_count,
183 p_data => x_msg_data
184 );
185
186 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
187 ROLLBACK TO CREATE_Adj_New_Prod_PVT;
188 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
189 -- Standard call to get message count and if count=1, get the message
190 FND_MSG_PUB.Count_And_Get (
191 p_encoded => FND_API.G_FALSE,
192 p_count => x_msg_count,
193 p_data => x_msg_data
194 );
195
196 WHEN OTHERS THEN
197 ROLLBACK TO CREATE_Adj_New_Prod_PVT;
198 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
199 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
200 THEN
201 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
202 END IF;
203 -- Standard call to get message count and if count=1, get the message
204 FND_MSG_PUB.Count_And_Get (
205 p_encoded => FND_API.G_FALSE,
206 p_count => x_msg_count,
207 p_data => x_msg_data
208 );
209 End Create_Adj_New_Prod;
210
211
212 PROCEDURE Update_Adj_New_Prod(
213 p_api_version_number IN NUMBER,
214 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
215 p_commit IN VARCHAR2 := FND_API.G_FALSE,
216 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
217
218 x_return_status OUT NOCOPY VARCHAR2,
219 x_msg_count OUT NOCOPY NUMBER,
220 x_msg_data OUT NOCOPY VARCHAR2,
221
222 p_adj_new_prod_rec IN adj_new_prod_rec_type,
223 x_object_version_number OUT NOCOPY NUMBER
224 )
225
226 IS
227 CURSOR c_get_adj_new_prod(cp_offerAdjNewProductId NUMBER, cp_objectVersionNumber NUMBER) IS
228 SELECT *
229 FROM OZF_OFFER_ADJ_NEW_PRODUCTS
230 WHERE offer_adj_new_product_id = cp_offerAdjNewProductId
231 AND object_version_number = cp_objectVersionNumber;
232 -- Hint: Developer need to provide Where clause
233 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Adj_New_Prod';
234 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
235 -- Local Variables
236 l_object_version_number NUMBER;
237 l_OFFER_ADJ_NEW_PRODUCT_ID NUMBER;
238 l_ref_adj_new_prod_rec c_get_Adj_New_Prod%ROWTYPE ;
239 l_tar_adj_new_prod_rec OZF_Adj_New_Prod_PVT.adj_new_prod_rec_type := P_adj_new_prod_rec;
240 l_rowid ROWID;
241
242 BEGIN
243 -- Standard Start of API savepoint
244 SAVEPOINT UPDATE_Adj_New_Prod_PVT;
245
246 -- Standard call to check for call compatibility.
247 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
248 p_api_version_number,
249 l_api_name,
250 G_PKG_NAME)
251 THEN
252 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
253 END IF;
254
255 -- Initialize message list if p_init_msg_list is set to TRUE.
256 IF FND_API.to_Boolean( p_init_msg_list )
257 THEN
258 FND_MSG_PUB.initialize;
259 END IF;
260
261 -- Debug Message
262 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
263 -- Initialize API return status to SUCCESS
264 x_return_status := FND_API.G_RET_STS_SUCCESS;
265 -- Debug Message
266 OZF_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
267 OPEN c_get_Adj_New_Prod( l_tar_adj_new_prod_rec.offer_adj_new_product_id , l_tar_adj_new_prod_rec.object_version_number);
268 FETCH c_get_Adj_New_Prod INTO l_ref_adj_new_prod_rec ;
269 If ( c_get_Adj_New_Prod%NOTFOUND) THEN
270 OZF_UTILITY_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
271 p_token_name => 'INFO',
272 p_token_value => 'Adj_New_Prod') ;
273 RAISE FND_API.G_EXC_ERROR;
274 END IF;
275 -- Debug Message
276 OZF_UTILITY_PVT.debug_message('Private API: - Close Cursor');
277 CLOSE c_get_Adj_New_Prod;
278
279 If (l_tar_adj_new_prod_rec.object_version_number is NULL or
280 l_tar_adj_new_prod_rec.object_version_number = FND_API.G_MISS_NUM ) Then
281 OZF_UTILITY_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
282 p_token_name => 'COLUMN',
283 p_token_value => 'Last_Update_Date') ;
284 raise FND_API.G_EXC_ERROR;
285 End if;
286 -- Check Whether record has been changed by someone else
287 If (l_tar_adj_new_prod_rec.object_version_number <> l_ref_adj_new_prod_rec.object_version_number) Then
288 OZF_UTILITY_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
289 p_token_name => 'INFO',
290 p_token_value => 'Adj_New_Prod') ;
291 raise FND_API.G_EXC_ERROR;
292 End if;
293 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
294 THEN
295 -- Debug message
296 OZF_UTILITY_PVT.debug_message('Private API: Validate_Adj_New_Prod');
300 p_init_msg_list => FND_API.G_FALSE,
297 -- Invoke validation procedures
298 Validate_adj_new_prod(
299 p_api_version_number => 1.0,
301 p_validation_level => p_validation_level,
302 p_validation_mode => JTF_PLSQL_API.g_update,
303 p_adj_new_prod_rec => p_adj_new_prod_rec,
304 x_return_status => x_return_status,
305 x_msg_count => x_msg_count,
306 x_msg_data => x_msg_data);
307 END IF;
308
309 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
310 RAISE FND_API.G_EXC_ERROR;
311 END IF;
312
313 -- Invoke table handler(OZF_OFFER_ADJ_NEW_PRODUCTS_PKG.Update_Row)
314 OZF_OFFER_ADJ_NEW_PRODUCTS_PKG.Update_Row(
315 p_offer_adj_new_product_id => p_adj_new_prod_rec.offer_adj_new_product_id,
316 p_offer_adj_new_line_id => p_adj_new_prod_rec.offer_adj_new_line_id,
317 p_offer_adjustment_id => p_adj_new_prod_rec.offer_adjustment_id,
318 p_product_context => p_adj_new_prod_rec.product_context,
319 p_product_attribute => p_adj_new_prod_rec.product_attribute,
320 p_product_attr_value => p_adj_new_prod_rec.product_attr_value,
321 p_excluder_flag => p_adj_new_prod_rec.excluder_flag,
322 p_uom_code => p_adj_new_prod_rec.uom_code,
323 p_creation_date => SYSDATE,
324 p_created_by => G_USER_ID,
325 p_last_update_date => SYSDATE,
326 p_last_updated_by => G_USER_ID,
327 p_last_update_login => G_LOGIN_ID,
328 p_object_version_number => p_adj_new_prod_rec.object_version_number);
329 --
330 -- End of API body.
331 --
332 -- Standard check for p_commit
333 IF FND_API.to_Boolean( p_commit )
334 THEN
335 COMMIT WORK;
336 END IF;
337 -- Debug Message
338 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
339
340 -- Standard call to get message count and if count is 1, get message info.
341 FND_MSG_PUB.Count_And_Get
342 (p_count => x_msg_count,
343 p_data => x_msg_data
344 );
345 EXCEPTION
346
347 WHEN OZF_UTILITY_PVT.resource_locked THEN
348 x_return_status := FND_API.g_ret_sts_error;
349 OZF_UTILITY_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
350
351 WHEN FND_API.G_EXC_ERROR THEN
352 ROLLBACK TO UPDATE_Adj_New_Prod_PVT;
353 x_return_status := FND_API.G_RET_STS_ERROR;
354 -- Standard call to get message count and if count=1, get the message
355 FND_MSG_PUB.Count_And_Get (
356 p_encoded => FND_API.G_FALSE,
357 p_count => x_msg_count,
358 p_data => x_msg_data
359 );
360
361 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
362 ROLLBACK TO UPDATE_Adj_New_Prod_PVT;
363 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
364 -- Standard call to get message count and if count=1, get the message
365 FND_MSG_PUB.Count_And_Get (
366 p_encoded => FND_API.G_FALSE,
367 p_count => x_msg_count,
368 p_data => x_msg_data
369 );
370
371 WHEN OTHERS THEN
372 ROLLBACK TO UPDATE_Adj_New_Prod_PVT;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
375 THEN
376 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
377 END IF;
378 -- Standard call to get message count and if count=1, get the message
379 FND_MSG_PUB.Count_And_Get (
380 p_encoded => FND_API.G_FALSE,
381 p_count => x_msg_count,
382 p_data => x_msg_data
383 );
384 End Update_Adj_New_Prod;
385
386
387 PROCEDURE Delete_Adj_New_Prod(
388 p_api_version_number IN NUMBER,
389 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
390 p_commit IN VARCHAR2 := FND_API.G_FALSE,
391 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
392 x_return_status OUT NOCOPY VARCHAR2,
393 x_msg_count OUT NOCOPY NUMBER,
394 x_msg_data OUT NOCOPY VARCHAR2,
395 p_offer_adj_new_product_id IN NUMBER,
396 p_object_version_number IN NUMBER
397 )
398
399 IS
400 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Adj_New_Prod';
401 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
402 l_object_version_number NUMBER;
403
404 BEGIN
405 -- Standard Start of API savepoint
406 SAVEPOINT DELETE_Adj_New_Prod_PVT;
407
408 -- Standard call to check for call compatibility.
409 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
410 p_api_version_number,
411 l_api_name,
412 G_PKG_NAME)
413 THEN
414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
415 END IF;
416
417 -- Initialize message list if p_init_msg_list is set to TRUE.
418 IF FND_API.to_Boolean( p_init_msg_list )
419 THEN
420 FND_MSG_PUB.initialize;
421 END IF;
422 -- Debug Message
423 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
424 -- Initialize API return status to SUCCESS
425 x_return_status := FND_API.G_RET_STS_SUCCESS;
426 --
427 -- Api body
428 --
429 -- Debug Message
430 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
434 --
431 -- Invoke table handler(OZF_OFFER_ADJ_NEW_PRODUCTS_PKG.Delete_Row)
432 OZF_OFFER_ADJ_NEW_PRODUCTS_PKG.Delete_Row(
433 p_OFFER_ADJ_NEW_PRODUCT_ID => p_OFFER_ADJ_NEW_PRODUCT_ID);
435 -- End of API body
436 --
437 -- Standard check for p_commit
438 IF FND_API.to_Boolean( p_commit )
439 THEN
440 COMMIT WORK;
441 END IF;
442 -- Debug Message
443 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
444 -- Standard call to get message count and if count is 1, get message info.
445 FND_MSG_PUB.Count_And_Get
446 (p_count => x_msg_count,
447 p_data => x_msg_data
448 );
449 EXCEPTION
450
451 WHEN OZF_UTILITY_PVT.resource_locked THEN
452 x_return_status := FND_API.g_ret_sts_error;
453 OZF_UTILITY_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
454
455 WHEN FND_API.G_EXC_ERROR THEN
456 ROLLBACK TO DELETE_Adj_New_Prod_PVT;
457 x_return_status := FND_API.G_RET_STS_ERROR;
458 -- Standard call to get message count and if count=1, get the message
459 FND_MSG_PUB.Count_And_Get (
460 p_encoded => FND_API.G_FALSE,
461 p_count => x_msg_count,
462 p_data => x_msg_data
463 );
464
465 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
466 ROLLBACK TO DELETE_Adj_New_Prod_PVT;
467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
468 -- Standard call to get message count and if count=1, get the message
469 FND_MSG_PUB.Count_And_Get (
470 p_encoded => FND_API.G_FALSE,
471 p_count => x_msg_count,
472 p_data => x_msg_data
473 );
474
475 WHEN OTHERS THEN
476 ROLLBACK TO DELETE_Adj_New_Prod_PVT;
477 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
478 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
479 THEN
480 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
481 END IF;
482 -- Standard call to get message count and if count=1, get the message
483 FND_MSG_PUB.Count_And_Get (
484 p_encoded => FND_API.G_FALSE,
485 p_count => x_msg_count,
486 p_data => x_msg_data
487 );
488 End Delete_Adj_New_Prod;
489 -- Hint: Primary key needs to be returned.
490 PROCEDURE Lock_Adj_New_Prod(
491 p_api_version_number IN NUMBER,
492 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
493
494 x_return_status OUT NOCOPY VARCHAR2,
495 x_msg_count OUT NOCOPY NUMBER,
496 x_msg_data OUT NOCOPY VARCHAR2,
497
498 p_offer_adj_new_product_id IN NUMBER,
499 p_object_version IN NUMBER
500 )
501
502 IS
503 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Adj_New_Prod';
504 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
505 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
506 l_OFFER_ADJ_NEW_PRODUCT_ID NUMBER;
507
508 CURSOR c_Adj_New_Prod IS
509 SELECT OFFER_ADJ_NEW_PRODUCT_ID
510 FROM OZF_OFFER_ADJ_NEW_PRODUCTS
511 WHERE OFFER_ADJ_NEW_PRODUCT_ID = p_OFFER_ADJ_NEW_PRODUCT_ID
512 AND object_version_number = p_object_version
513 FOR UPDATE NOWAIT;
514
515 BEGIN
516
517 -- Debug Message
518 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
519
520 -- Initialize message list if p_init_msg_list is set to TRUE.
521 IF FND_API.to_Boolean( p_init_msg_list )
522 THEN
523 FND_MSG_PUB.initialize;
524 END IF;
525
526 -- Standard call to check for call compatibility.
527 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
528 p_api_version_number,
529 l_api_name,
530 G_PKG_NAME)
531 THEN
532 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
533 END IF;
534
535
536 -- Initialize API return status to SUCCESS
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538 ------------------------ lock -------------------------
539
540 OZF_UTILITY_PVT.debug_message(l_full_name||': start');
541 OPEN c_Adj_New_Prod;
542
543 FETCH c_Adj_New_Prod INTO l_OFFER_ADJ_NEW_PRODUCT_ID;
544
545 IF (c_Adj_New_Prod%NOTFOUND) THEN
546 CLOSE c_Adj_New_Prod;
547 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
548 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
549 FND_MSG_PUB.add;
550 END IF;
551 RAISE FND_API.g_exc_error;
552 END IF;
553
554 CLOSE c_Adj_New_Prod;
555 -------------------- finish --------------------------
556 FND_MSG_PUB.count_and_get(
557 p_encoded => FND_API.g_false,
558 p_count => x_msg_count,
559 p_data => x_msg_data);
560 OZF_UTILITY_PVT.debug_message(l_full_name ||': end');
561 EXCEPTION
562
563 WHEN OZF_UTILITY_PVT.resource_locked THEN
564 x_return_status := FND_API.g_ret_sts_error;
565 OZF_UTILITY_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
566
567 WHEN FND_API.G_EXC_ERROR THEN
568 ROLLBACK TO LOCK_Adj_New_Prod_PVT;
569 x_return_status := FND_API.G_RET_STS_ERROR;
570 -- Standard call to get message count and if count=1, get the message
571 FND_MSG_PUB.Count_And_Get (
572 p_encoded => FND_API.G_FALSE,
573 p_count => x_msg_count,
574 p_data => x_msg_data
575 );
576
580 -- Standard call to get message count and if count=1, get the message
577 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
578 ROLLBACK TO LOCK_Adj_New_Prod_PVT;
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
581 FND_MSG_PUB.Count_And_Get (
582 p_encoded => FND_API.G_FALSE,
583 p_count => x_msg_count,
584 p_data => x_msg_data
585 );
586
587 WHEN OTHERS THEN
588 ROLLBACK TO LOCK_Adj_New_Prod_PVT;
589 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
590 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
591 THEN
592 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
593 END IF;
594 -- Standard call to get message count and if count=1, get the message
595 FND_MSG_PUB.Count_And_Get (
596 p_encoded => FND_API.G_FALSE,
597 p_count => x_msg_count,
598 p_data => x_msg_data
599 );
600 End Lock_Adj_New_Prod;
601
602
603 PROCEDURE check_adj_new_prod_uk_items(
604 p_adj_new_prod_rec IN adj_new_prod_rec_type,
605 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
606 x_return_status OUT NOCOPY VARCHAR2)
607 IS
608 l_valid_flag VARCHAR2(1);
609
610 BEGIN
611 x_return_status := FND_API.g_ret_sts_success;
612 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
613 l_valid_flag := OZF_UTILITY_PVT.check_uniqueness(
614 'OZF_OFFER_ADJ_NEW_PRODUCTS',
615 'OFFER_ADJ_NEW_PRODUCT_ID = ''' || p_adj_new_prod_rec.OFFER_ADJ_NEW_PRODUCT_ID ||''''
616 );
617 ELSE
618 l_valid_flag := OZF_UTILITY_PVT.check_uniqueness(
619 'OZF_OFFER_ADJ_NEW_PRODUCTS',
620 'OFFER_ADJ_NEW_PRODUCT_ID = ''' || p_adj_new_prod_rec.OFFER_ADJ_NEW_PRODUCT_ID ||
621 ''' AND OFFER_ADJ_NEW_PRODUCT_ID <> ' || p_adj_new_prod_rec.OFFER_ADJ_NEW_PRODUCT_ID
622 );
623 END IF;
624
625 IF l_valid_flag = FND_API.g_false THEN
626 OZF_UTILITY_PVT.Error_Message(p_message_name => 'AMS_OFFER_ADJ_NEW_PRODUCT_ID_DUPLICATE');
627 x_return_status := FND_API.g_ret_sts_error;
628 RETURN;
629 END IF;
630
631 END check_adj_new_prod_uk_items;
632
633 PROCEDURE check_adj_new_prod_req_items(
634 p_adj_new_prod_rec IN adj_new_prod_rec_type,
635 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
636 x_return_status OUT NOCOPY VARCHAR2
637 )
638 IS
639 BEGIN
640 x_return_status := FND_API.g_ret_sts_success;
641 ozf_utility_pvt.debug_message('Validation Mode is:'||p_validation_mode||' : '||JTF_PLSQL_API.g_create);
642 ozf_utility_pvt.debug_message('Product Ctx is :'||p_adj_new_prod_rec.product_context);
643 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
644 IF p_adj_new_prod_rec.offer_adj_new_line_id = FND_API.g_miss_num OR p_adj_new_prod_rec.offer_adj_new_line_id IS NULL THEN
645 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'offer_adj_new_line_id' );
646 x_return_status := FND_API.g_ret_sts_error;
647 RETURN;
648 END IF;
649 IF p_adj_new_prod_rec.excluder_flag = FND_API.g_miss_char OR p_adj_new_prod_rec.excluder_flag IS NULL THEN
650 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'excluder_flag' );
651 x_return_status := FND_API.g_ret_sts_error;
652 RETURN;
653 END IF;
654 IF p_adj_new_prod_rec.product_context = FND_API.g_miss_char OR p_adj_new_prod_rec.product_context IS NULL THEN
655 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'product_context' );
656 x_return_status := FND_API.g_ret_sts_error;
657 RETURN;
658 END IF;
659 IF p_adj_new_prod_rec.product_attribute = FND_API.g_miss_char OR p_adj_new_prod_rec.product_attribute IS NULL THEN
660 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'product_attribute' );
661 x_return_status := FND_API.g_ret_sts_error;
662 RETURN;
663 END IF;
664 IF p_adj_new_prod_rec.product_attr_value = FND_API.g_miss_char OR p_adj_new_prod_rec.product_attr_value IS NULL THEN
665 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'product_attr_value' );
666 x_return_status := FND_API.g_ret_sts_error;
667 RETURN;
668 END IF;
669
670 -- IF p_adj_new_prod_rec.offer_type = 'VOLUME_OFFER' THEN
671 IF p_adj_new_prod_rec.offer_adjustment_id IS NULL OR p_adj_new_prod_rec.offer_adjustment_id = FND_API.G_MISS_NUM THEN
672 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'offer_adjustment_id' );
673 x_return_status := FND_API.g_ret_sts_error;
674 RETURN;
675 END IF;
676 -- END IF;
677
678 ELSE
679 IF p_adj_new_prod_rec.offer_adj_new_product_id = FND_API.G_MISS_NUM THEN
680 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'offer_adj_new_product_id' );
681 x_return_status := FND_API.g_ret_sts_error;
682 RETURN;
683 END IF;
684 IF p_adj_new_prod_rec.offer_adj_new_line_id = FND_API.G_MISS_NUM THEN
685 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'offer_adj_new_line_id' );
686 x_return_status := FND_API.g_ret_sts_error;
687 RETURN;
688 END IF;
689 IF p_adj_new_prod_rec.excluder_flag = FND_API.G_MISS_CHAR THEN
690 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'excluder_flag' );
691 x_return_status := FND_API.g_ret_sts_error;
692 RETURN;
693 END IF;
694 IF p_adj_new_prod_rec.object_version_number = FND_API.G_MISS_NUM THEN
695 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'object_version_number' );
699
696 x_return_status := FND_API.g_ret_sts_error;
697 RETURN;
698 END IF;
700 IF p_adj_new_prod_rec.product_context = FND_API.g_miss_char THEN
701 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'product_context' );
702 x_return_status := FND_API.g_ret_sts_error;
703 RETURN;
704 END IF;
705 IF p_adj_new_prod_rec.product_attribute = FND_API.g_miss_char THEN
706 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'product_attribute' );
707 x_return_status := FND_API.g_ret_sts_error;
708 RETURN;
709 END IF;
710 IF p_adj_new_prod_rec.product_attr_value = FND_API.g_miss_char THEN
711 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'product_attr_value' );
712 x_return_status := FND_API.g_ret_sts_error;
713 RETURN;
714 END IF;
715
716 -- IF p_adj_new_prod_rec.offer_type = 'VOLUME_OFFER' THEN
717 IF p_adj_new_prod_rec.offer_adjustment_id = FND_API.G_MISS_NUM THEN
718 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'offer_adjustment_id' );
719 x_return_status := FND_API.g_ret_sts_error;
720 RETURN;
721 END IF;
722 -- END IF;
723 END IF;
724 END check_adj_new_prod_req_items;
725
726 PROCEDURE check_adj_new_prod_FK_items(
727 p_adj_new_prod_rec IN adj_new_prod_rec_type,
728 x_return_status OUT NOCOPY VARCHAR2
729 )
730 IS
731 BEGIN
732 x_return_status := FND_API.g_ret_sts_success;
733 ozf_utility_pvt.debug_message('Offer Type is :'||p_adj_new_prod_rec.offer_type);
734 IF p_adj_new_prod_rec.offer_type <> 'VOLUME_OFFER' THEN
735 IF ozf_utility_pvt.check_fk_exists('OZF_OFFER_ADJ_NEW_LINES','OFFER_ADJ_NEW_LINE_ID',to_char(p_adj_new_prod_rec.OFFER_ADJ_NEW_LINE_ID)) = FND_API.g_false THEN
736 OZF_Utility_PVT.Error_Message('OZF_INVALID_ADJ_LINE_ID' );
737 x_return_status := FND_API.g_ret_sts_error;
738 return;
739 END IF;
740 END IF;
741 IF p_adj_new_prod_rec.offer_adjustment_id IS NOT NULL AND p_adj_new_prod_rec.offer_adjustment_id <> FND_API.G_MISS_NUM THEN
742 IF ozf_utility_pvt.check_fk_exists('OZF_OFFER_ADJUSTMENTS_B','OFFER_ADJUSTMENT_ID',to_char(p_adj_new_prod_rec.offer_adjustment_id)) = FND_API.g_false THEN
743 OZF_Utility_PVT.Error_Message('OZF_INVALID_OFFER_ADJ_ID' );
744 x_return_status := FND_API.g_ret_sts_error;
745 return;
746 END IF;
747 END IF;
748 -- Enter custom code here
749 END check_adj_new_prod_FK_items;
750
751 PROCEDURE check_adj_prod_Lkup_items(
752 p_adj_new_prod_rec IN adj_new_prod_rec_type,
753 x_return_status OUT NOCOPY VARCHAR2
754 )
755 IS
756 CURSOR C_UOM_CODE_EXISTS (p_uom_code VARCHAR2,p_organization_id NUMBER,p_inventory_item_id NUMBER)
757 IS
758 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1 FROM mtl_item_uoms_view
759 WHERE ( organization_id = p_organization_id
760 OR p_organization_id is NULL )
761 AND uom_code = p_uom_code
762 AND inventory_item_id = p_inventory_item_id);
763 l_organization_id NUMBER := -999;
764 l_UOM_CODE_EXISTS C_UOM_CODE_EXISTS%ROWTYPE;
765
766 CURSOR c_general_uom(p_uom_code VARCHAR2)
767 IS
768 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1
769 FROM mtl_units_of_measure_vl
770 WHERE uom_code = p_uom_code);
771 l_general_uom c_general_uom%rowtype;
772
773 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_Lkup_Items';
774 CURSOR c_listHeaderId(cp_offerAdjNewLineId NUMBER)
775 IS
776 SELECT list_header_id
777 FROM ozf_offer_adjustments_b a, ozf_offer_adj_new_lines b
778 WHERE a.offer_adjustment_id = b.offer_adjustment_id
779 AND b.offer_adj_new_line_id = cp_offerAdjNewLineId;
780
781 l_listHeaderId NUMBER;
782 BEGIN
783 x_return_status := FND_API.G_RET_STS_SUCCESS;
784 -- Enter custom code here
785 --=====================================================================
786 -- uom validation begin
787 --=====================================================================
788
789 l_organization_id := FND_PROFILE.Value('QP_ORGANIZATION_ID');--QP_UTIL.Get_Item_Validation_Org;
790 IF p_adj_new_prod_rec.uom_code IS NOT NULL AND p_adj_new_prod_rec.uom_code <> FND_API.G_MISS_CHAR THEN
791 IF(p_adj_new_prod_rec.product_attribute = 'PRICING_ATTRIBUTE1') THEN
792
793 OPEN c_uom_code_exists(p_adj_new_prod_rec.uom_code,l_organization_id,p_adj_new_prod_rec.product_attr_value);
794 FETCH c_uom_code_exists INTO l_uom_code_exists;
795 IF ( c_uom_code_exists%NOTFOUND) THEN
796 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
797 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
798 FND_MSG_PUB.add;
799 x_return_status := FND_API.G_RET_STS_ERROR;
800 END IF;
801 END IF;
802 CLOSE c_uom_code_exists;
803 ELSIF(p_adj_new_prod_rec.product_attribute = 'PRICING_ATTRIBUTE2') THEN
804 open c_listHeaderId(cp_offerAdjNewLineId => p_adj_new_prod_rec.offer_adj_new_line_id);
805 FETCH c_listHeaderId INTO l_listHeaderId;
806 CLOSE c_listHeaderId;
807 IF QP_Validate.Product_Uom ( p_product_uom_code => p_adj_new_prod_rec.uom_code
808 ,p_category_id => to_number(p_adj_new_prod_rec.product_attr_value)
809 ,p_list_header_id => l_listHeaderId) THEN
810
811 /* IF QP_CATEGORY_MAPPING_RULE.Validate_UOM(
812 l_organization_id,
816 */
813 to_number(p_adj_new_prod_rec.product_attr_value),
814 p_adj_new_prod_rec.uom_code) = 'N'
815 THEN
817 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
818 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
819 FND_MSG_PUB.add;
820 x_return_status := FND_API.G_RET_STS_ERROR;
821 END IF;
822 END IF;
823 ELSE
824 OPEN c_general_uom(p_adj_new_prod_rec.uom_code);
825 FETCH c_general_uom INTO l_general_uom;
826 IF ( c_general_uom%NOTFOUND) THEN
827 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
828 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
829 FND_MSG_PUB.add;
830 x_return_status := FND_API.G_RET_STS_ERROR;
831 END IF;
832 END IF;
833 CLOSE c_general_uom;
834 END IF;
835 END IF;
836 END check_adj_prod_Lkup_items;
837
838 PROCEDURE Check_adj_new_prod_inter_attr(
839 p_adj_new_prod_rec IN adj_new_prod_rec_type
840 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
841 , x_return_status OUT NOCOPY VARCHAR2
842 )
843 IS
844
845 CURSOR c_volumeType(cp_offerAdjLineId NUMBER) IS
846 SELECT volume_type , parent_adj_line_id FROM ozf_offer_adj_new_lines
847 WHERE offer_adj_new_line_id = cp_offerAdjLineId;
848 l_volumeType c_volumeType%ROWTYPE;
849 CURSOR c_listLineType(cp_offerAdjLineId NUMBER) IS
850 SELECT tier_type
851 FROM ozf_offer_adj_new_lines
852 WHERE offer_adj_new_line_id = cp_offerAdjLineId;
853 l_listLineType VARCHAR2(30);
854 BEGIN
855 x_return_status := FND_API.G_RET_STS_SUCCESS;
856
857 OPEN c_volumeType(p_adj_new_prod_rec.offer_adj_new_line_id);
858 FETCH c_volumeType INTO l_volumeType;
859 CLOSE c_volumeType;
860 IF l_volumeType.parent_adj_line_id IS NULL AND l_volumeType.volume_type = 'PRICING_ATTRIBUTE10' THEN
861 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
862 IF p_adj_new_prod_rec.uom_code = FND_API.G_MISS_CHAR OR p_adj_new_prod_rec.uom_code IS NULL
863 THEN
864 OZF_Utility_PVT.Error_Message('OZF_UOM_QTY_REQD' );
865 x_return_status := FND_API.g_ret_sts_error;
866 return;
867 END IF;
868 ELSIF p_validation_mode = JTF_PLSQL_API.g_update THEN
869 IF p_adj_new_prod_rec.uom_code = FND_API.G_MISS_CHAR THEN
870 OZF_Utility_PVT.Error_Message('OZF_UOM_QTY_REQD' );
871 x_return_status := FND_API.g_ret_sts_error;
872 return;
873 END IF;
874 END IF;
875 END IF;
876
877 OPEN c_listLineType(p_adj_new_prod_rec.offer_adj_new_line_id);
878 FETCH c_listLineType INTO l_listLineType;
879 IF c_listLineType%NOTFOUND THEN
880 l_listLineType := null;
881 END IF;
882 CLOSE c_listLineType;
883
884 IF l_listLineType = 'PBH' THEN
885 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
886 IF p_adj_new_prod_rec.uom_code = FND_API.G_MISS_CHAR OR p_adj_new_prod_rec.uom_code IS NULL THEN
887 OZF_Utility_PVT.Error_Message('OZF_PBH_UOM_REQD' );
888 x_return_status := FND_API.g_ret_sts_error;
889 return;
890 END IF;
891 ELSIF p_validation_mode = JTF_PLSQL_API.g_update THEN
892 IF p_adj_new_prod_rec.uom_code = FND_API.G_MISS_CHAR THEN
893 OZF_Utility_PVT.Error_Message('OZF_PBH_UOM_REQD' );
894 x_return_status := FND_API.g_ret_sts_error;
895 return;
896 END IF;
897 END IF;
898 END IF;
899 END Check_adj_new_prod_inter_attr;
900
901
902 PROCEDURE Check_adj_new_prod_attr(
903 p_adj_new_prod_rec IN adj_new_prod_rec_type
904 , x_return_status OUT NOCOPY VARCHAR2
905 )
906 IS
907 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_attr';
908 l_context_flag VARCHAR2(1);
909 l_attribute_flag VARCHAR2(1);
910 l_value_flag VARCHAR2(1);
911 l_datatype VARCHAR2(1);
912 l_precedence NUMBER;
913 l_error_code NUMBER := 0;
914 BEGIN
915 x_return_status := FND_API.G_RET_STS_SUCCESS;
916 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_PRICING'
917 ,context =>p_adj_new_prod_rec.product_context
918 ,attribute =>p_adj_new_prod_rec.product_attribute
919 ,value =>p_adj_new_prod_rec.product_attr_value
920 ,application_short_name => 'QP'
921 ,context_flag =>l_context_flag
922 ,attribute_flag =>l_attribute_flag
923 ,value_flag =>l_value_flag
924 ,datatype =>l_datatype
925 ,precedence =>l_precedence
926 ,error_code =>l_error_code
927 );
928 If (l_context_flag = 'N' AND l_error_code = 7) -- invalid context
929 Then
930 x_return_status := FND_API.G_RET_STS_ERROR;
931 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
932 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_CONTEXT' );
933 FND_MSG_PUB.add;
934 END IF;
935 End If;
936
940 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
937 If (l_attribute_flag = 'N' AND l_error_code = 8) -- invalid attribute
938 Then
939 x_return_status := FND_API.G_RET_STS_ERROR;
941 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_ATTR' );
942 FND_MSG_PUB.add;
943 END IF;
944 End If;
945
946 If (l_value_flag = 'N' AND l_error_code = 9) -- invalid value
947 Then
948 x_return_status := FND_API.G_RET_STS_ERROR;
949 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
950 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_VALUE' );
951 FND_MSG_PUB.add;
952 END IF;
953 End If;
954 END Check_adj_new_prod_attr;
955
956 PROCEDURE Check_adj_new_prod_Items (
957 P_adj_new_prod_rec IN adj_new_prod_rec_type,
958 p_validation_mode IN VARCHAR2,
959 x_return_status OUT NOCOPY VARCHAR2
960 )
961 IS
962 BEGIN
963 -- Check Items Required/NOT NULL API calls
964
965 check_adj_new_prod_req_items(
966 p_adj_new_prod_rec => p_adj_new_prod_rec,
967 p_validation_mode => p_validation_mode,
968 x_return_status => x_return_status);
969 IF x_return_status <> FND_API.g_ret_sts_success THEN
970 RETURN;
971 END IF;
972
973 Check_adj_new_prod_attr(
974 p_adj_new_prod_rec => p_adj_new_prod_rec,
975 x_return_status => x_return_status);
976 IF x_return_status <> FND_API.g_ret_sts_success THEN
977 RETURN;
978 END IF;
979
980 Check_adj_new_prod_inter_attr(
981 p_adj_new_prod_rec => p_adj_new_prod_rec,
982 p_validation_mode => p_validation_mode,
983 x_return_status => x_return_status);
984 IF x_return_status <> FND_API.g_ret_sts_success THEN
985 RETURN;
986 END IF;
987
988 -- Check Items Uniqueness API calls
989 check_adj_new_prod_uk_items(
990 p_adj_new_prod_rec => p_adj_new_prod_rec,
991 p_validation_mode => p_validation_mode,
992 x_return_status => x_return_status);
993 IF x_return_status <> FND_API.g_ret_sts_success THEN
994 RETURN;
995 END IF;
996
997 -- Check Items Foreign Keys API calls
998
999 check_adj_new_prod_FK_items(
1000 p_adj_new_prod_rec => p_adj_new_prod_rec,
1001 x_return_status => x_return_status);
1002 IF x_return_status <> FND_API.g_ret_sts_success THEN
1003 RETURN;
1004 END IF;
1005 -- Check Items Lookups
1006
1007 check_adj_prod_Lkup_items(
1008 p_adj_new_prod_rec => p_adj_new_prod_rec,
1009 x_return_status => x_return_status);
1010 IF x_return_status <> FND_API.g_ret_sts_success THEN
1011 RETURN;
1012 END IF;
1013
1014
1015 END Check_adj_new_prod_Items;
1016
1017
1018 PROCEDURE Complete_adj_new_prod_Rec (
1019 p_adj_new_prod_rec IN adj_new_prod_rec_type,
1020 x_complete_rec OUT NOCOPY adj_new_prod_rec_type)
1021 IS
1022 l_return_status VARCHAR2(1);
1023
1024 CURSOR c_complete IS
1025 SELECT *
1026 FROM ozf_offer_adj_new_products
1027 WHERE offer_adj_new_product_id = p_adj_new_prod_rec.offer_adj_new_product_id;
1028 l_adj_new_prod_rec c_complete%ROWTYPE;
1029 BEGIN
1030 x_complete_rec := p_adj_new_prod_rec;
1031
1032
1033 OPEN c_complete;
1034 FETCH c_complete INTO l_adj_new_prod_rec;
1035 CLOSE c_complete;
1036
1037 -- offer_adj_new_product_id
1038 IF p_adj_new_prod_rec.offer_adj_new_product_id = FND_API.g_miss_num THEN
1039 x_complete_rec.offer_adj_new_product_id := l_adj_new_prod_rec.offer_adj_new_product_id;
1040 END IF;
1041
1042 -- offer_adj_new_line_id
1043 IF p_adj_new_prod_rec.offer_adj_new_line_id = FND_API.g_miss_num THEN
1044 x_complete_rec.offer_adj_new_line_id := l_adj_new_prod_rec.offer_adj_new_line_id;
1045 END IF;
1046
1047 -- product_context
1048 IF p_adj_new_prod_rec.product_context = FND_API.g_miss_char THEN
1049 x_complete_rec.product_context := l_adj_new_prod_rec.product_context;
1050 END IF;
1051
1052 -- product_attribute
1053 IF p_adj_new_prod_rec.product_attribute = FND_API.g_miss_char THEN
1054 x_complete_rec.product_attribute := l_adj_new_prod_rec.product_attribute;
1055 END IF;
1056
1057 -- product_attr_value
1058 IF p_adj_new_prod_rec.product_attr_value = FND_API.g_miss_char THEN
1059 x_complete_rec.product_attr_value := l_adj_new_prod_rec.product_attr_value;
1060 END IF;
1061
1062 -- excluder_flag
1063 IF p_adj_new_prod_rec.excluder_flag = FND_API.g_miss_char THEN
1064 x_complete_rec.excluder_flag := l_adj_new_prod_rec.excluder_flag;
1065 END IF;
1066
1067 -- uom_code
1068 IF p_adj_new_prod_rec.uom_code = FND_API.g_miss_char THEN
1069 x_complete_rec.uom_code := l_adj_new_prod_rec.uom_code;
1070 END IF;
1071
1072 -- creation_date
1073 IF p_adj_new_prod_rec.creation_date = FND_API.g_miss_date THEN
1074 x_complete_rec.creation_date := l_adj_new_prod_rec.creation_date;
1075 END IF;
1076
1077 -- created_by
1078 IF p_adj_new_prod_rec.created_by = FND_API.g_miss_num THEN
1079 x_complete_rec.created_by := l_adj_new_prod_rec.created_by;
1080 END IF;
1081
1082 -- last_update_date
1083 IF p_adj_new_prod_rec.last_update_date = FND_API.g_miss_date THEN
1084 x_complete_rec.last_update_date := l_adj_new_prod_rec.last_update_date;
1085 END IF;
1086
1087 -- last_updated_by
1088 IF p_adj_new_prod_rec.last_updated_by = FND_API.g_miss_num THEN
1089 x_complete_rec.last_updated_by := l_adj_new_prod_rec.last_updated_by;
1090 END IF;
1094 x_complete_rec.last_update_login := l_adj_new_prod_rec.last_update_login;
1091
1092 -- last_update_login
1093 IF p_adj_new_prod_rec.last_update_login = FND_API.g_miss_num THEN
1095 END IF;
1096
1097 -- object_version_number
1098 IF p_adj_new_prod_rec.object_version_number = FND_API.g_miss_num THEN
1099 x_complete_rec.object_version_number := l_adj_new_prod_rec.object_version_number;
1100 END IF;
1101 -- Note: Developers need to modify the procedure
1102 -- to handle any business specific requirements.
1103 END Complete_adj_new_prod_Rec;
1104
1105 PROCEDURE Validate_adj_new_prod(
1106 p_api_version_number IN NUMBER,
1107 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1108 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1109 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_update,
1110 p_adj_new_prod_rec IN adj_new_prod_rec_type,
1111 x_return_status OUT NOCOPY VARCHAR2,
1112 x_msg_count OUT NOCOPY NUMBER,
1113 x_msg_data OUT NOCOPY VARCHAR2
1114 )
1115 IS
1116 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Adj_New_Prod';
1117 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1118 l_object_version_number NUMBER;
1119 l_adj_new_prod_rec OZF_Adj_New_Prod_PVT.adj_new_prod_rec_type;
1120
1121 BEGIN
1122 -- Standard Start of API savepoint
1123 SAVEPOINT VALIDATE_Adj_New_Prod_;
1124
1125 -- Standard call to check for call compatibility.
1126 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1127 p_api_version_number,
1128 l_api_name,
1129 G_PKG_NAME)
1130 THEN
1131 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1132 END IF;
1133
1134 -- Initialize message list if p_init_msg_list is set to TRUE.
1135 IF FND_API.to_Boolean( p_init_msg_list )
1136 THEN
1137 FND_MSG_PUB.initialize;
1138 END IF;
1139 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1140 Check_adj_new_prod_Items(
1141 p_adj_new_prod_rec => p_adj_new_prod_rec,
1142 p_validation_mode => p_validation_mode,
1143 x_return_status => x_return_status
1144 );
1145
1146 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1147 RAISE FND_API.G_EXC_ERROR;
1148 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1149 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1150 END IF;
1151 END IF;
1152
1153 Complete_adj_new_prod_Rec(
1154 p_adj_new_prod_rec => p_adj_new_prod_rec,
1155 x_complete_rec => l_adj_new_prod_rec
1156 );
1157
1158 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1159 Validate_adj_new_prod_Rec(
1160 p_api_version_number => 1.0,
1161 p_init_msg_list => FND_API.G_FALSE,
1162 x_return_status => x_return_status,
1163 x_msg_count => x_msg_count,
1164 x_msg_data => x_msg_data,
1165 p_adj_new_prod_rec => l_adj_new_prod_rec);
1166
1167 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1168 RAISE FND_API.G_EXC_ERROR;
1169 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1170 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1171 END IF;
1172 END IF;
1173
1174
1175 -- Debug Message
1176 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1177
1178
1179 -- Initialize API return status to SUCCESS
1180 x_return_status := FND_API.G_RET_STS_SUCCESS;
1181
1182
1183 -- Debug Message
1184 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1185
1186 -- Standard call to get message count and if count is 1, get message info.
1187 FND_MSG_PUB.Count_And_Get
1188 (p_count => x_msg_count,
1189 p_data => x_msg_data
1190 );
1191 EXCEPTION
1192
1193 WHEN OZF_UTILITY_PVT.resource_locked THEN
1194 x_return_status := FND_API.g_ret_sts_error;
1195 OZF_UTILITY_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1196
1197 WHEN FND_API.G_EXC_ERROR THEN
1198 ROLLBACK TO VALIDATE_Adj_New_Prod_;
1199 x_return_status := FND_API.G_RET_STS_ERROR;
1200 -- Standard call to get message count and if count=1, get the message
1201 FND_MSG_PUB.Count_And_Get (
1202 p_encoded => FND_API.G_FALSE,
1203 p_count => x_msg_count,
1204 p_data => x_msg_data
1205 );
1206
1207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1208 ROLLBACK TO VALIDATE_Adj_New_Prod_;
1209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1210 -- Standard call to get message count and if count=1, get the message
1211 FND_MSG_PUB.Count_And_Get (
1212 p_encoded => FND_API.G_FALSE,
1213 p_count => x_msg_count,
1214 p_data => x_msg_data
1215 );
1216
1217 WHEN OTHERS THEN
1218 ROLLBACK TO VALIDATE_Adj_New_Prod_;
1219 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1220 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1221 THEN
1222 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1223 END IF;
1224 -- Standard call to get message count and if count=1, get the message
1225 FND_MSG_PUB.Count_And_Get (
1226 p_encoded => FND_API.G_FALSE,
1227 p_count => x_msg_count,
1228 p_data => x_msg_data
1229 );
1230 End Validate_Adj_New_Prod;
1231
1232
1233 PROCEDURE Validate_adj_new_prod_rec(
1234 p_api_version_number IN NUMBER,
1235 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1236 x_return_status OUT NOCOPY VARCHAR2,
1237 x_msg_count OUT NOCOPY NUMBER,
1238 x_msg_data OUT NOCOPY VARCHAR2,
1239 p_adj_new_prod_rec IN adj_new_prod_rec_type
1240 )
1241 IS
1242 BEGIN
1243 -- Initialize message list if p_init_msg_list is set to TRUE.
1244 IF FND_API.to_Boolean( p_init_msg_list )
1245 THEN
1246 FND_MSG_PUB.initialize;
1247 END IF;
1248
1249 -- Initialize API return status to SUCCESS
1250 x_return_status := FND_API.G_RET_STS_SUCCESS;
1251
1252 -- Hint: Validate data
1253 -- If data not valid
1254 -- THEN
1255 -- x_return_status := FND_API.G_RET_STS_ERROR;
1256
1257 -- Debug Message
1258 OZF_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1259 -- Standard call to get message count and if count is 1, get message info.
1260 FND_MSG_PUB.Count_And_Get
1261 (p_count => x_msg_count,
1262 p_data => x_msg_data
1263 );
1264 END Validate_adj_new_prod_Rec;
1265
1266 END OZF_Adj_New_Prod_PVT;