[Home] [Help]
PACKAGE BODY: APPS.OZF_PRICE_LIST_PVT
Source
1 PACKAGE BODY OZF_PRICE_LIST_PVT as
2 /* $Header: ozfvprlb.pls 120.0.12020000.2 2012/10/17 10:21:26 nepanda ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_PRICE_LIST_PVT
7 -- Purpose
8 --
9 -- History
10 -- 10/17/2012 nepanda Fix for Bug 14724414 - tst1213:getting error while approving accrual offer
11 --
12 -- NOTE
13 --
14 -- End of Comments
15 -- ===============================================================
16
17 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_PRICE_LIST_PVT';
18 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvprlb.pls';
19
20
21
22 FUNCTION get_user_status_name(p_user_status_id IN NUMBER) return VARCHAR2 IS
23 l_user_status_name VARCHAR2(120);
24 CURSOR cur_user_status_name IS
25 SELECT name
26 FROM ams_user_statuses_vl
27 WHERE user_status_id = p_user_status_id;
28
29 BEGIN
30
31 OPEN cur_user_status_name;
32 FETCH cur_user_status_name into l_user_status_name;
33 CLOSE cur_user_status_name;
34 return l_user_status_name;
35
36 END;
37
38 PROCEDURE Check_Uk_Items
39 (
40 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
41 p_ozf_price_list_rec IN OZF_PRICE_LIST_Rec_Type,
42 x_return_status OUT NOCOPY VARCHAR2
43 )
44 IS
45
46 l_uk_flag VARCHAR2(1);
47
48 BEGIN
49
50 x_return_status := FND_API.g_ret_sts_success;
51
52 IF p_validation_mode = JTF_PLSQL_API.g_create
53 AND p_ozf_price_list_rec.price_list_attribute_id IS NOT NULL AND p_ozf_price_list_rec.price_list_attribute_id <> FND_API.G_MISS_NUM
54 THEN
55 l_uk_flag := OZF_Utility_PVT.check_uniqueness
56 (
57 'OZF_PRICE_LIST_ATTRIBUTES',
58 'price_list_attribute_id = ' || p_ozf_price_list_rec.price_list_attribute_id
59 );
60 END IF;
61 IF l_uk_flag = FND_API.g_false THEN
62
63 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
64 FND_MESSAGE.set_name('OZF', 'OZF_PRICE_LIST_DUP_PK');
65 FND_MSG_PUB.add;
66 END IF;
67
68 x_return_status := FND_API.g_ret_sts_error;
69 RETURN;
70 END IF;
71
72 IF p_validation_mode = JTF_PLSQL_API.g_create
73 AND p_ozf_price_list_rec.qp_list_header_id IS NOT NULL
74 THEN
75 l_uk_flag := OZF_Utility_PVT.check_uniqueness
76 (
77 'OZF_PRICE_LIST_ATTRIBUTES',
78 'qp_list_header_id = ' || p_ozf_price_list_rec.qp_list_header_id
79 );
80 END IF;
81
82 IF l_uk_flag = FND_API.g_false THEN
83
84 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
85 FND_MESSAGE.set_name('OZF', 'OZF_PRICE_LIST_DUP_PK');
86 FND_MSG_PUB.add;
87 END IF;
88
89 x_return_status := FND_API.g_ret_sts_error;
90 RETURN;
91 END IF;
92 END;
93
94 PROCEDURE Check_Fk_Items
95 (
96 p_ozf_price_list_rec IN ozf_price_list_rec_type,
97 x_return_status OUT NOCOPY VARCHAR2
98 )
99 IS
100 l_fk_flag VARCHAR2(1);
101 BEGIN
102
103 x_return_status := FND_API.g_ret_sts_success;
104
105 IF p_ozf_price_list_rec.qp_list_header_id <> FND_API.g_miss_num THEN
106 l_fk_flag := OZF_Utility_PVT.check_fk_exists
107 (
108 'QP_LIST_HEADERS_ALL_B', -- Fix for bug # 14724414
109 'list_header_id',
110 p_ozf_price_list_rec.qp_list_header_id
111 );
112
113 IF l_fk_flag = FND_API.g_false THEN
114 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
115 FND_MESSAGE.set_name('OZF', 'OZF_PRICE_LIST_NO_QP_LIST');
116 FND_MSG_PUB.add;
117 END IF;
118
119 x_return_status := FND_API.g_ret_sts_error;
120 RETURN;
121 END IF;
122 END IF;
123 END;
124
125 PROCEDURE Check_Lookup_Items
126 (
127 p_ozf_price_list_rec IN ozf_price_list_rec_type,
128 x_return_status OUT NOCOPY VARCHAR2
129 )
130 IS
131
132 BEGIN
133
134 x_return_status := FND_API.g_ret_sts_success;
135
136 IF p_ozf_price_list_rec.status_code <> FND_API.g_miss_char
137 AND p_ozf_price_list_rec.status_code IS NOT NULL
138 THEN
139 IF OZF_Utility_PVT.check_lookup_exists(
140 p_lookup_type => 'OZF_PRICELIST_STATUS',
141 p_lookup_code => p_ozf_price_list_rec.status_code
142 ) = FND_API.g_false
143 THEN
144 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
145 THEN
146 FND_MESSAGE.set_name('OZF', 'OZ0_CAMP_BAD_STATUS_CHANGE');
147 FND_MSG_PUB.add;
148 END IF;
149 x_return_status := FND_API.g_ret_sts_error;
150 RETURN;
151 END IF;
152 END IF;
153 NULL;
154 END;
155
156 PROCEDURE Check_Req_Items
157 (
158 p_validation_mode IN VARCHAR2,
159 p_ozf_price_list_rec IN ozf_price_list_rec_type,
160 x_return_status OUT NOCOPY VARCHAR2
161 )
162 IS
163
164 BEGIN
165
166 x_return_status := FND_API.g_ret_sts_success;
167 /*
168 IF (p_ozf_price_list_rec.price_list_attribute_id IS NULL OR p_ozf_price_list_rec.price_list_attribute_id = FND_API.g_miss_num)
169 AND p_validation_mode = JTF_PLSQL_API.g_update
170 THEN
171 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
172 FND_MESSAGE.set_name('OZF', 'OZF_API_NO_PRICE_LIST_ATTR_ID');
173 FND_MSG_PUB.add;
174 END IF;
175
176 x_return_status := FND_API.g_ret_sts_error;
177 RETURN;
178 END IF;
179 */
180 IF (p_ozf_price_list_rec.object_version_number IS NULL OR p_ozf_price_list_rec.object_version_number = FND_API.g_miss_num)
181 AND p_validation_mode = JTF_PLSQL_API.g_update
182 THEN
183 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
184 FND_MESSAGE.set_name('OZF', 'OZF_API_NO_OBJ_VER_NUM');
185 FND_MSG_PUB.add;
186 END IF;
187
188 x_return_status := FND_API.g_ret_sts_error;
189 RETURN;
190 END IF;
191
192 IF ( p_ozf_price_list_rec.qp_list_header_id IS NULL OR p_ozf_price_list_rec.qp_list_header_id = FND_API.g_miss_num )
193 AND p_validation_mode = JTF_PLSQL_API.g_create
194 THEN
195 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
196 FND_MESSAGE.set_name('OZF', 'OZF_ACT_OFFER_NO_LIST_HEAD_ID');
197 FND_MSG_PUB.add;
198 END IF;
199
200 x_return_status := FND_API.g_ret_sts_error;
201 RETURN;
202 END IF;
203
204 IF ( p_ozf_price_list_rec.status_code IS NULL OR p_ozf_price_list_rec.status_code = FND_API.g_miss_char )
205 AND p_validation_mode = JTF_PLSQL_API.g_create
206 THEN
207 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
208 FND_MESSAGE.set_name('OZF', 'OZF_CAMP_NO_STATUS_CODE');
209 FND_MSG_PUB.add;
210 END IF;
211
212 x_return_status := FND_API.g_ret_sts_error;
213 RETURN;
214 END IF;
215
216 IF ( p_ozf_price_list_rec.owner_id IS NULL OR p_ozf_price_list_rec.owner_id = FND_API.g_miss_num )
217 AND p_validation_mode = JTF_PLSQL_API.g_create
218 THEN
219 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
220 FND_MESSAGE.set_name('OZF', 'OZF_PRIC_NO_OWNER_ID');
221 FND_MSG_PUB.add;
222 END IF;
223
224 x_return_status := FND_API.g_ret_sts_error;
225 RETURN;
226 END IF;
227
228 IF ( p_ozf_price_list_rec.user_status_id IS NULL OR p_ozf_price_list_rec.user_status_id = FND_API.g_miss_num )
229 AND p_validation_mode = JTF_PLSQL_API.g_create
230 THEN
231 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
232 FND_MESSAGE.set_name('OZF', 'OZF_CAMP_NO_USER_STATUS_ID');
233 FND_MSG_PUB.add;
234 END IF;
235
236 x_return_status := FND_API.g_ret_sts_error;
237 RETURN;
238 END IF;
239
240 IF ( p_ozf_price_list_rec.custom_setup_id IS NULL OR p_ozf_price_list_rec.custom_setup_id = FND_API.g_miss_num )
241 AND p_validation_mode = JTF_PLSQL_API.g_create
242 THEN
243 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
244 FND_MESSAGE.set_name('OZF', 'OZF_PRIC_NO_CUSTOM_SETUP_ID');
245 FND_MSG_PUB.add;
246 END IF;
247
248 x_return_status := FND_API.g_ret_sts_error;
249 RETURN;
250 END IF;
251 END;
252
253 PROCEDURE Check_OZF_PRICE_LIST_Items (
254 P_OZF_PRICE_LIST_Rec IN OZF_PRICE_LIST_Rec_Type,
255 p_validation_mode IN VARCHAR2,
256 x_return_status OUT NOCOPY VARCHAR2
257 )
258 IS
259 BEGIN
260 x_return_status := FND_API.g_ret_sts_success;
261
262 check_req_items
263 (
264 p_validation_mode => p_validation_mode,
265 P_OZF_PRICE_LIST_Rec => P_OZF_PRICE_LIST_Rec,
266 x_return_status => x_return_status
267 );
268
269 IF x_return_status <> FND_API.g_ret_sts_success THEN
270 RETURN;
271 END IF;
272
273 check_uk_items
274 (
275 p_validation_mode => p_validation_mode,
276 P_OZF_PRICE_LIST_Rec => P_OZF_PRICE_LIST_Rec,
277 x_return_status => x_return_status
278 );
279
280 IF x_return_status <> FND_API.g_ret_sts_success THEN
281 RETURN;
282 END IF;
283
284 -- check foreign key items
285 check_fk_items
286 (
287 P_OZF_PRICE_LIST_Rec => P_OZF_PRICE_LIST_Rec,
288 x_return_status => x_return_status
289 );
290
291 IF x_return_status <> FND_API.g_ret_sts_success THEN
292 RETURN;
293 END IF;
294
295 -- check lookup items
296 check_lookup_items
297 (
298 P_OZF_PRICE_LIST_Rec => P_OZF_PRICE_LIST_Rec,
299 x_return_status => x_return_status
300 );
301
302 IF x_return_status <> FND_API.g_ret_sts_success THEN
303 RETURN;
304 END IF;
305
306
307 END Check_OZF_PRICE_LIST_Items;
308
309
310 PROCEDURE Validate_OZF_PRICE_LIST_rec(
311 P_Api_Version_Number IN NUMBER,
312 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
313 X_Return_Status OUT NOCOPY VARCHAR2,
314 X_Msg_Count OUT NOCOPY NUMBER,
315 X_Msg_Data OUT NOCOPY VARCHAR2,
316 P_OZF_PRICE_LIST_Rec IN OZF_PRICE_LIST_Rec_Type
317 )
318 IS
319 BEGIN
320
321 IF FND_API.to_Boolean( p_init_msg_list )
322 THEN
323 FND_MSG_PUB.initialize;
324 END IF;
325 NULL;
326 x_return_status := FND_API.G_RET_STS_SUCCESS;
327
328 FND_MSG_PUB.Count_And_Get
329 (p_count => x_msg_count,
330 p_data => x_msg_data
331 );
332 END Validate_OZF_PRICE_LIST_Rec;
333
334 PROCEDURE Complete_OZF_PRICE_LIST_Rec(
335 p_OZF_PRICE_LIST_rec IN OZF_PRICE_LIST_Rec_Type,
336 x_complete_rec OUT NOCOPY OZF_PRICE_LIST_Rec_Type
337 )
338 IS
339 CURSOR c_ozf_price_list IS
340 SELECT *
341 FROM OZF_price_list_attributes
342 -- WHERE price_list_attribute_id = p_ozf_price_list_rec.price_list_attribute_id;
343 WHERE qp_list_header_id = p_ozf_price_list_rec.qp_list_header_id;
344
345 l_price_list_rec c_ozf_price_list%ROWTYPE;
346
347 BEGIN
348
349 x_complete_rec := p_OZF_PRICE_LIST_rec;
350
351 OPEN c_ozf_price_list;
352 FETCH c_ozf_price_list INTO l_price_list_rec;
353
354 IF (c_ozf_price_list%NOTFOUND) THEN
355 CLOSE c_ozf_price_list;
356 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
357 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
358 FND_MSG_PUB.add;
359 END IF;
360 RAISE FND_API.g_exc_error;
361 END IF;
362 CLOSE c_ozf_price_list;
363
364
365 IF p_OZF_PRICE_LIST_rec.status_code = FND_API.g_miss_char THEN
366 x_complete_rec.status_code := NULL;
367 END IF;
368 IF p_OZF_PRICE_LIST_rec.status_code IS NULL THEN
369 x_complete_rec.status_code := l_price_list_rec.status_code;
370 END IF;
371
372 IF p_OZF_PRICE_LIST_rec.status_date = FND_API.g_miss_date
373 OR p_OZF_PRICE_LIST_rec.status_date IS NULL
374 THEN
375 IF x_complete_rec.status_date = l_price_list_rec.status_date THEN
376 -- no status change, set it to be the original value
377 x_complete_rec.status_date := l_price_list_rec.status_date;
378 ELSE
379 -- status changed, set it to be SYSDATE
380 x_complete_rec.status_date := SYSDATE;
381 END IF;
382 END IF;
383 /*
384 IF p_OZF_PRICE_LIST_rec.qp_list_header_id = FND_API.g_miss_num THEN
385 x_complete_rec.qp_list_header_id := NULL;
386 END IF;
387 IF p_OZF_PRICE_LIST_rec.qp_list_header_id IS NULL THEN
388 x_complete_rec.qp_list_header_id := l_price_list_rec.qp_list_header_id;
389 END IF;
390 */
391 IF p_OZF_PRICE_LIST_rec.price_list_attribute_id = FND_API.g_miss_num THEN
392 x_complete_rec.price_list_attribute_id := NULL;
393 END IF;
394 IF p_OZF_PRICE_LIST_rec.price_list_attribute_id IS NULL THEN
395 x_complete_rec.price_list_attribute_id := l_price_list_rec.price_list_attribute_id;
396 END IF;
397
398
399 IF p_OZF_PRICE_LIST_rec.user_status_id = FND_API.g_miss_num THEN
400 x_complete_rec.user_status_id := NULL;
401 END IF;
402 IF p_OZF_PRICE_LIST_rec.user_status_id IS NULL THEN
403 x_complete_rec.user_status_id := l_price_list_rec.user_status_id;
404 END IF;
405
406 IF p_OZF_PRICE_LIST_rec.custom_setup_id = FND_API.g_miss_num THEN
407 x_complete_rec.custom_setup_id := NULL;
408 END IF;
409 IF p_OZF_PRICE_LIST_rec.custom_setup_id IS NULL THEN
410 x_complete_rec.custom_setup_id := l_price_list_rec.custom_setup_id;
411 END IF;
412
413 IF p_OZF_PRICE_LIST_rec.owner_id = FND_API.g_miss_num THEN
414 x_complete_rec.owner_id := NULL;
415 END IF;
416 IF p_OZF_PRICE_LIST_rec.owner_id IS NULL THEN
417 x_complete_rec.owner_id := l_price_list_rec.owner_id;
418 END IF;
419
420 IF p_OZF_PRICE_LIST_rec.wf_item_key = FND_API.g_miss_char THEN
421 x_complete_rec.wf_item_key := NULL;
422 END IF;
423 IF p_OZF_PRICE_LIST_rec.wf_item_key IS NULL THEN
424 x_complete_rec.wf_item_key := l_price_list_rec.wf_item_key;
425 END IF;
426
427
428 END ;
429
430 PROCEDURE Validate_price_list(
431 P_Api_Version_Number IN NUMBER,
432 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
433 P_Validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
434 p_Validation_mode IN VARCHAR2 := 'CREATE',
435 P_OZF_PRICE_LIST_Rec IN OZF_PRICE_LIST_Rec_Type,
436 X_Return_Status OUT NOCOPY VARCHAR2,
437 X_Msg_Count OUT NOCOPY NUMBER,
438 X_Msg_Data OUT NOCOPY VARCHAR2
439 )
440 IS
441 l_api_name CONSTANT VARCHAR2(30) := 'Validate_price_list';
442 l_api_version_number CONSTANT NUMBER := 1.0;
443 l_object_version_number NUMBER;
444 l_OZF_PRICE_LIST_rec OZF_PRICE_LIST_Rec_Type := P_OZF_PRICE_LIST_Rec;
445 BEGIN
446
447 SAVEPOINT VALIDATE_PRICE_LIST_;
448
449 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
450 p_api_version_number,
451 l_api_name,
452 G_PKG_NAME)
453 THEN
454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 END IF;
456
457 IF FND_API.to_Boolean( p_init_msg_list )
458 THEN
459 FND_MSG_PUB.initialize;
460 END IF;
461
462 If p_validation_mode = 'UPDATE' THEN
463 Complete_OZF_PRICE_LIST_Rec(
464 p_OZF_PRICE_LIST_rec => p_OZF_PRICE_LIST_rec,
465 x_complete_rec => l_OZF_PRICE_LIST_rec
466 );
467 END IF;
468 ozf_utility_pvt.debug_message(l_OZF_PRICE_LIST_rec.status_code);
469 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
470 Check_OZF_PRICE_LIST_Items(
471 p_OZF_PRICE_LIST_rec => l_OZF_PRICE_LIST_rec,
472 p_validation_mode => p_validation_mode,
473 x_return_status => x_return_status
474 );
475 ozf_utility_pvt.debug_message(x_return_status);
476 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
477 RAISE FND_API.G_EXC_ERROR;
478 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
480 END IF;
481 END IF;
482
483 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
484
485 Validate_OZF_PRICE_LIST_Rec(
486 p_api_version_number => 1.0,
487 p_init_msg_list => FND_API.G_FALSE,
488 x_return_status => x_return_status,
489 x_msg_count => x_msg_count,
490 x_msg_data => x_msg_data,
491 P_OZF_PRICE_LIST_Rec => l_OZF_PRICE_LIST_Rec);
492
493 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
494 RAISE FND_API.G_EXC_ERROR;
495 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
497 END IF;
498 END IF;
499
500 -- Initialize API return status to SUCCESS
501 x_return_status := FND_API.G_RET_STS_SUCCESS;
502
503 -- Standard call to get message count and if count is 1, get message info.
504 FND_MSG_PUB.Count_And_Get
505 (p_count => x_msg_count,
506 p_data => x_msg_data
507 );
508 EXCEPTION
509 WHEN FND_API.G_EXC_ERROR THEN
510 ROLLBACK TO VALIDATE_PRICE_LIST_;
511 x_return_status := FND_API.G_RET_STS_ERROR;
512 -- Standard call to get message count and if count=1, get the message
513 FND_MSG_PUB.Count_And_Get (
514 p_encoded => FND_API.G_FALSE,
515 p_count => x_msg_count,
516 p_data => x_msg_data
517 );
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519 ROLLBACK TO VALIDATE_PRICE_LIST_;
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521 -- Standard call to get message count and if count=1, get the message
522 FND_MSG_PUB.Count_And_Get (
523 p_encoded => FND_API.G_FALSE,
524 p_count => x_msg_count,
525 p_data => x_msg_data
526 );
527 WHEN OTHERS THEN
528 ROLLBACK TO VALIDATE_PRICE_LIST_;
529 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
530 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
531 THEN
532 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
533 END IF;
534 -- Standard call to get message count and if count=1, get the message
535 FND_MSG_PUB.Count_And_Get (
536 p_encoded => FND_API.G_FALSE,
537 p_count => x_msg_count,
538 p_data => x_msg_data
539 );
540 End Validate_price_list;
541
542
543 PROCEDURE Lock_Row(
544 p_PRICE_LIST_ATTRIBUTE_ID NUMBER,
545 p_USER_STATUS_ID NUMBER,
546 p_STATUS_CODE VARCHAR2,
547 p_OWNER_ID NUMBER,
548 p_QP_LIST_HEADER_ID NUMBER,
549 p_OBJECT_VERSION_NUMBER NUMBER,
550 p_STATUS_DATE DATE,
551 p_WF_ITEM_KEY VARCHAR2,
552 p_CREATED_BY NUMBER,
553 p_CREATION_DATE DATE,
554 p_LAST_UPDATE_DATE DATE,
555 p_LAST_UPDATE_LOGIN NUMBER,
556 p_LAST_UPDATED_BY NUMBER)
557
558 IS
559 CURSOR C IS
560 SELECT *
561 FROM OZF_PRICE_LIST_ATTRIBUTES
562 WHERE PRICE_LIST_ATTRIBUTE_ID = p_PRICE_LIST_ATTRIBUTE_ID
563 FOR UPDATE of PRICE_LIST_ATTRIBUTE_ID NOWAIT;
564 Recinfo C%ROWTYPE;
565 BEGIN
566 NULL;
567 END Lock_Row;
568
569
570 PROCEDURE Create_price_list(
571 P_Api_Version_Number IN NUMBER,
572 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
573 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
574 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
575 X_Return_Status OUT NOCOPY VARCHAR2,
576 X_Msg_Count OUT NOCOPY NUMBER,
577 X_Msg_Data OUT NOCOPY VARCHAR2,
578 P_OZF_PRICE_LIST_Rec IN OZF_PRICE_LIST_Rec_Type := G_MISS_OZF_PRICE_LIST_REC,
579 X_PRICE_LIST_ATTRIBUTE_ID OUT NOCOPY NUMBER
580 )
581
582 IS
583 l_api_name CONSTANT VARCHAR2(30) := 'Create_price_list';
584 l_api_version_number CONSTANT NUMBER := 1.0;
585 l_return_status_full VARCHAR2(1);
586 l_object_version_number NUMBER := 1;
587 l_org_id NUMBER := FND_API.G_MISS_NUM;
588 l_PRICE_LIST_ATTRIBUTE_ID NUMBER;
589
590 CURSOR C2 IS SELECT OZF_PRICE_LIST_ATTRIBUTES_S.nextval FROM sys.dual;
591
592 BEGIN
593
594 SAVEPOINT CREATE_PRICE_LIST_PVT;
595
596
597 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
598 p_api_version_number,
599 l_api_name,
600 G_PKG_NAME)
601 THEN
602 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
603 END IF;
604
605
606 IF FND_API.to_Boolean( p_init_msg_list )
607 THEN
608 FND_MSG_PUB.initialize;
609 END IF;
610
611 x_return_status := FND_API.G_RET_STS_SUCCESS;
612
613 IF (P_OZF_PRICE_LIST_Rec.PRICE_LIST_ATTRIBUTE_ID IS NULL OR P_OZF_PRICE_LIST_Rec.PRICE_LIST_ATTRIBUTE_ID = FND_API.G_MISS_NUM) THEN
614 OPEN C2;
615 FETCH C2 INTO l_PRICE_LIST_ATTRIBUTE_ID;
616 CLOSE C2;
617 ELSE
618 L_PRICE_LIST_ATTRIBUTE_ID := P_OZF_PRICE_LIST_Rec.PRICE_LIST_ATTRIBUTE_ID;
619 END IF;
620
621 IF (P_OZF_PRICE_LIST_Rec.OBJECT_VERSION_NUMBER IS NULL OR
622 P_OZF_PRICE_LIST_Rec.OBJECT_VERSION_NUMBER = FND_API.G_MISS_NUM) THEN
623 l_OBJECT_VERSION_NUMBER := 1;
624 END IF;
625
626
627 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
628 THEN
629
630 Validate_price_list(
631 p_api_version_number => 1.0,
632 p_init_msg_list => FND_API.G_FALSE,
633 p_validation_level => p_validation_level,
634 p_validation_mode => 'CREATE',
635 P_OZF_PRICE_LIST_Rec => P_OZF_PRICE_LIST_Rec,
636 x_return_status => x_return_status,
637 x_msg_count => x_msg_count,
638 x_msg_data => x_msg_data);
639
640 END IF;
641
642 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
643 RAISE FND_API.G_EXC_ERROR;
644 END IF;
645 INSERT INTO OZF_PRICE_LIST_ATTRIBUTES(
646 PRICE_LIST_ATTRIBUTE_ID,
647 USER_STATUS_ID,
648 CUSTOM_SETUP_ID,
649 STATUS_CODE,
650 OWNER_ID,
651 QP_LIST_HEADER_ID,
652 OBJECT_VERSION_NUMBER,
653 STATUS_DATE,
654 WF_ITEM_KEY,
655 CREATED_BY,
656 CREATION_DATE,
657 LAST_UPDATE_DATE,
658 LAST_UPDATE_LOGIN,
659 LAST_UPDATED_BY
660 ) VALUES (
661 l_price_list_attribute_id,
662 decode( p_OZF_PRICE_LIST_rec.USER_STATUS_ID, FND_API.G_MISS_NUM, NULL, p_OZF_PRICE_LIST_rec.USER_STATUS_ID),
663 decode( p_OZF_PRICE_LIST_rec.CUSTOM_SETUP_ID, FND_API.G_MISS_NUM, NULL, p_OZF_PRICE_LIST_rec.CUSTOM_SETUP_ID),
664 decode( p_OZF_PRICE_LIST_rec.STATUS_CODE, FND_API.G_MISS_CHAR, NULL, p_OZF_PRICE_LIST_rec.STATUS_CODE),
665 decode( p_OZF_PRICE_LIST_rec.OWNER_ID, FND_API.G_MISS_NUM, NULL, p_OZF_PRICE_LIST_rec.OWNER_ID),
666 decode( p_OZF_PRICE_LIST_rec.QP_LIST_HEADER_ID, FND_API.G_MISS_NUM, NULL, p_OZF_PRICE_LIST_rec.QP_LIST_HEADER_ID),
667 1,
668 decode( p_OZF_PRICE_LIST_rec.STATUS_DATE, FND_API.G_MISS_DATE, NULL, p_OZF_PRICE_LIST_rec.STATUS_DATE),
669 decode( p_OZF_PRICE_LIST_rec.WF_ITEM_KEY, FND_API.G_MISS_CHAR, NULL, p_OZF_PRICE_LIST_rec.WF_ITEM_KEY),
670 FND_GLOBAL.USER_ID,
671 SYSDATE,
672 SYSDATE,
673 FND_GLOBAL.CONC_LOGIN_ID,
674 FND_GLOBAL.USER_ID);
675
676 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
677 RAISE FND_API.G_EXC_ERROR;
678 END IF;
679
680 IF FND_API.to_Boolean( p_commit )
681 THEN
682 COMMIT WORK;
683 END IF;
684 x_price_list_attribute_id := l_price_list_attribute_id;
685
686 FND_MSG_PUB.Count_And_Get
687 (p_count => x_msg_count,
688 p_data => x_msg_data
689 );
690 EXCEPTION
691 WHEN FND_API.G_EXC_ERROR THEN
692 ROLLBACK TO CREATE_PRICE_LIST_PVT;
693 x_return_status := FND_API.G_RET_STS_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 FND_API.G_EXC_UNEXPECTED_ERROR THEN
701 ROLLBACK TO CREATE_PRICE_LIST_PVT;
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703 -- Standard call to get message count and if count=1, get the message
704 FND_MSG_PUB.Count_And_Get (
705 p_encoded => FND_API.G_FALSE,
706 p_count => x_msg_count,
707 p_data => x_msg_data
708 );
709 WHEN OTHERS THEN
710 ROLLBACK TO CREATE_PRICE_LIST_PVT;
711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
712 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
713 THEN
714 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
715 END IF;
716 -- Standard call to get message count and if count=1, get the message
717 FND_MSG_PUB.Count_And_Get (
718 p_encoded => FND_API.G_FALSE,
719 p_count => x_msg_count,
720 p_data => x_msg_data
721 );
722 End Create_price_list;
723
724 PROCEDURE Update_price_list(
725 P_Api_Version_Number IN NUMBER,
726 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
727 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
728 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
729 X_Return_Status OUT NOCOPY VARCHAR2,
730 X_Msg_Count OUT NOCOPY NUMBER,
731 X_Msg_Data OUT NOCOPY VARCHAR2,
732 P_OZF_PRICE_LIST_Rec IN OZF_PRICE_LIST_Rec_Type,
733 X_Object_Version_Number OUT NOCOPY NUMBER
734 )
735 IS
736
737 l_api_name CONSTANT VARCHAR2(30) := 'Update_price_list';
738 l_api_version_number CONSTANT NUMBER := 1.0;
739 -- Local Variables
740 l_object_version_number NUMBER;
741 l_PRICE_LIST_ATTRIBUTE_ID NUMBER;
742 l_rowid ROWID;
743
744 CURSOR c_is_qp_pricelist(p_id NUMBER) IS
745 SELECT 'N'
746 FROM ozf_price_list_attributes
747 WHERE qp_list_header_id = p_id;
748 l_is_qp_pricelist VARCHAR2(1);
749 BEGIN
750
751 SAVEPOINT UPDATE_PRICE_LIST_PVT;
752
753
754 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
755 p_api_version_number,
756 l_api_name,
757 G_PKG_NAME)
758 THEN
759 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
760 END IF;
761
762
763 IF FND_API.to_Boolean( p_init_msg_list )
764 THEN
765 FND_MSG_PUB.initialize;
766 END IF;
767
768 x_return_status := FND_API.G_RET_STS_SUCCESS;
769
770 OPEN c_is_qp_pricelist(p_ozf_price_list_rec.qp_list_header_id);
771 FETCH c_is_qp_pricelist INTO l_is_qp_pricelist;
772 CLOSE c_is_qp_pricelist;
773
774 IF l_is_qp_pricelist = 'N' THEN -- bug 3780070 exception when updating price list created from QP
775 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
776 THEN
777 -- Debug message
778 Validate_price_list(
779 p_api_version_number => 1.0,
780 p_init_msg_list => FND_API.G_FALSE,
781 p_validation_level => p_validation_level,
782 p_validation_mode => 'UPDATE',
783 P_OZF_PRICE_LIST_Rec => P_OZF_PRICE_LIST_Rec,
784 x_return_status => x_return_status,
785 x_msg_count => x_msg_count,
786 x_msg_data => x_msg_data);
787 END IF;
788
789 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
790 RAISE FND_API.G_EXC_ERROR;
791 END IF;
792
793 Update OZF_PRICE_LIST_ATTRIBUTES
794 SET
795 PRICE_LIST_ATTRIBUTE_ID = decode( p_OZF_PRICE_LIST_rec.PRICE_LIST_ATTRIBUTE_ID, FND_API.G_MISS_NUM, PRICE_LIST_ATTRIBUTE_ID, p_OZF_PRICE_LIST_rec.PRICE_LIST_ATTRIBUTE_ID),
796 USER_STATUS_ID = decode( p_OZF_PRICE_LIST_rec.USER_STATUS_ID, FND_API.G_MISS_NUM, USER_STATUS_ID, p_OZF_PRICE_LIST_rec.USER_STATUS_ID),
797 CUSTOM_SETUP_ID = decode( p_OZF_PRICE_LIST_rec.CUSTOM_SETUP_ID, FND_API.G_MISS_NUM, CUSTOM_SETUP_ID, p_OZF_PRICE_LIST_rec.CUSTOM_SETUP_ID),
798 STATUS_CODE = decode( p_OZF_PRICE_LIST_rec.STATUS_CODE, FND_API.G_MISS_CHAR, STATUS_CODE, p_OZF_PRICE_LIST_rec.STATUS_CODE),
799 OWNER_ID = decode( p_OZF_PRICE_LIST_rec.OWNER_ID, FND_API.G_MISS_NUM, OWNER_ID, p_OZF_PRICE_LIST_rec.OWNER_ID),
800 QP_LIST_HEADER_ID = decode( p_OZF_PRICE_LIST_rec.QP_LIST_HEADER_ID, FND_API.G_MISS_NUM, QP_LIST_HEADER_ID, p_OZF_PRICE_LIST_rec.QP_LIST_HEADER_ID),
801 OBJECT_VERSION_NUMBER = p_OZF_PRICE_LIST_rec.object_version_number + 1,
802 STATUS_DATE = decode( p_OZF_PRICE_LIST_rec.STATUS_DATE, FND_API.G_MISS_DATE, STATUS_DATE, p_OZF_PRICE_LIST_rec.STATUS_DATE),
803 WF_ITEM_KEY = decode( p_OZF_PRICE_LIST_rec.WF_ITEM_KEY, FND_API.G_MISS_CHAR, WF_ITEM_KEY, p_OZF_PRICE_LIST_rec.WF_ITEM_KEY),
804 LAST_UPDATE_DATE = SYSDATE,
805 LAST_UPDATE_LOGIN = FND_GLOBAL.CONC_LOGIN_ID,
806 LAST_UPDATED_BY = FND_GLOBAL.USER_ID
807 where qp_list_header_id = p_OZF_PRICE_LIST_rec.qp_list_header_id
808 and object_version_number = p_OZF_PRICE_LIST_rec.object_version_number;
809
810 IF (SQL%NOTFOUND) THEN
811 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
812 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
813 FND_MSG_PUB.add;
814 END IF;
815 RAISE FND_API.g_exc_error;
816 END IF;
817 END IF;
818
819 x_object_version_number := p_OZF_PRICE_LIST_rec.object_version_number + 1;
820
821 IF FND_API.to_Boolean( p_commit )
822 THEN
823 COMMIT WORK;
824 END IF;
825
826 FND_MSG_PUB.Count_And_Get
827 (p_count => x_msg_count,
828 p_data => x_msg_data
829 );
830 EXCEPTION
831 WHEN FND_API.G_EXC_ERROR THEN
832 ROLLBACK TO UPDATE_PRICE_LIST_PVT;
833 x_return_status := FND_API.G_RET_STS_ERROR;
834
835 FND_MSG_PUB.Count_And_Get (
836 p_encoded => FND_API.G_FALSE,
837 p_count => x_msg_count,
838 p_data => x_msg_data
839 );
840 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
841 ROLLBACK TO UPDATE_PRICE_LIST_PVT;
842 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
843
844 FND_MSG_PUB.Count_And_Get (
845 p_encoded => FND_API.G_FALSE,
846 p_count => x_msg_count,
847 p_data => x_msg_data
848 );
849 WHEN OTHERS THEN
850 ROLLBACK TO UPDATE_PRICE_LIST_PVT;
851 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
852 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
853 THEN
854 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
855 END IF;
856
857 FND_MSG_PUB.Count_And_Get (
858 p_encoded => FND_API.G_FALSE,
859 p_count => x_msg_count,
860 p_data => x_msg_data
861 );
862 End Update_price_list;
863
864
865 PROCEDURE Delete_price_list(
866 P_Api_Version_Number IN NUMBER,
867 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
868 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
869 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
870 X_Return_Status OUT NOCOPY VARCHAR2,
871 X_Msg_Count OUT NOCOPY NUMBER,
872 X_Msg_Data OUT NOCOPY VARCHAR2,
873 P_PRICE_LIST_ATTRIBUTE_ID IN NUMBER,
874 P_Object_Version_Number IN NUMBER
875 )
876
877 IS
878 l_api_name CONSTANT VARCHAR2(30) := 'Delete_price_list';
879 l_api_version_number CONSTANT NUMBER := 1.0;
880 l_object_version_number NUMBER;
881
882 BEGIN
883
884 SAVEPOINT DELETE_PRICE_LIST_PVT;
885
886
887 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
888 p_api_version_number,
889 l_api_name,
890 G_PKG_NAME)
891 THEN
892 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
893 END IF;
894
895
896 IF FND_API.to_Boolean( p_init_msg_list )
897 THEN
898 FND_MSG_PUB.initialize;
899 END IF;
900
901 x_return_status := FND_API.G_RET_STS_SUCCESS;
902
903 DELETE FROM OZF_PRICE_LIST_ATTRIBUTES
904 WHERE PRICE_LIST_ATTRIBUTE_ID = p_PRICE_LIST_ATTRIBUTE_ID
905 AND object_version_number = p_object_version_number;
906
907 IF (SQL%NOTFOUND) THEN
908 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
909 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
910 FND_MSG_PUB.add;
911 END IF;
912 RAISE FND_API.g_exc_error;
913 END IF;
914
915
916
917 IF FND_API.to_Boolean( p_commit )
918 THEN
919 COMMIT WORK;
920 END IF;
921
922 FND_MSG_PUB.Count_And_Get
923 (p_count => x_msg_count,
924 p_data => x_msg_data
925 );
926 EXCEPTION
927 WHEN FND_API.G_EXC_ERROR THEN
928 ROLLBACK TO DELETE_PRICE_LIST_PVT;
929 x_return_status := FND_API.G_RET_STS_ERROR;
930 FND_MSG_PUB.Count_And_Get (
931 p_encoded => FND_API.G_FALSE,
932 p_count => x_msg_count,
933 p_data => x_msg_data
934 );
935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
936 ROLLBACK TO DELETE_PRICE_LIST_PVT;
937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
938 FND_MSG_PUB.Count_And_Get (
939 p_encoded => FND_API.G_FALSE,
940 p_count => x_msg_count,
941 p_data => x_msg_data
942 );
943 WHEN OTHERS THEN
944 ROLLBACK TO DELETE_PRICE_LIST_PVT;
945 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
946 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
947 THEN
948 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
949 END IF;
950 FND_MSG_PUB.Count_And_Get (
951 p_encoded => FND_API.G_FALSE,
952 p_count => x_msg_count,
953 p_data => x_msg_data
954 );
955 End Delete_price_list;
956
957
958 End OZF_PRICE_LIST_PVT;