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