[Home] [Help]
PACKAGE BODY: APPS.OZF_QP_PRODUCTS_PVT
Source
1 PACKAGE BODY OZF_QP_PRODUCTS_PVT AS
2 /* $Header: ozfvoqppb.pls 120.3 2005/08/25 04:19:26 rssharma noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 --
7 -- Purpose
8 --
9 -- History
10 -- Thu Jul 07 2005:7/12 PM RSSHARMA Created
11 -- NOTE
12 -- End of Comments
13 -- ===============================================================
14 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_QP_PRODUCTS_PVT';
15 G_FILE_NAME CONSTANT VARCHAR2(15) := 'ozfvoqppb.pls';
16
17
18
19 PROCEDURE check_qp_prod_req_items(
20 p_qp_product_rec IN qp_product_rec_type
21 , p_validation_mode IN VARCHAR2
22 , x_return_status OUT NOCOPY VARCHAR2
23 )
24 IS
25 BEGIN
26 x_return_status := FND_API.G_RET_STS_SUCCESS;
27 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
28 IF p_qp_product_rec.off_discount_product_id IS NULL OR p_qp_product_rec.off_discount_product_id = FND_API.G_MISS_NUM THEN
29 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','off_discount_product_id');
30 x_return_status := FND_API.g_ret_sts_error;
31 return;
32 END IF;
33 IF p_qp_product_rec.pricing_attribute_id IS NULL OR p_qp_product_rec.pricing_attribute_id = FND_API.G_MISS_NUM THEN
34 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','PRICING_ATTRIBUTE_ID');
35 x_return_status := FND_API.g_ret_sts_error;
36 return;
37 END IF;
38 ELSIF p_validation_mode = JTF_PLSQL_API.g_update THEN
39 IF p_qp_product_rec.qp_product_id = FND_API.G_MISS_NUM THEN
40 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','QP_PRODUCT_ID');
41 x_return_status := FND_API.g_ret_sts_error;
42 return;
43 END IF;
44 IF p_qp_product_rec.off_discount_product_id = FND_API.G_MISS_NUM THEN
45 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','OFF_DISCOUNT_PRODUCT_ID');
46 x_return_status := FND_API.g_ret_sts_error;
47 return;
48 END IF;
49 IF p_qp_product_rec.pricing_attribute_id = FND_API.G_MISS_NUM THEN
50 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','PRICING_ATTRIBUTE_ID');
51 x_return_status := FND_API.g_ret_sts_error;
52 return;
53 END IF;
54 IF p_qp_product_rec.object_version_number = FND_API.G_MISS_NUM THEN
55 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','OBJECT_VERSION_NUMBER');
56 x_return_status := FND_API.g_ret_sts_error;
57 return;
58 END IF;
59 END IF;
60 END check_qp_prod_req_items;
61
62
63 PROCEDURE check_qp_prod_uk_items(
64 p_qp_product_rec IN qp_product_rec_type
65 , p_validation_mode IN VARCHAR2
66 , x_return_status OUT NOCOPY VARCHAR2
67 )
68 IS
69 BEGIN
70 x_return_status := FND_API.G_RET_STS_SUCCESS;
71 IF p_validation_mode = JTF_PLSQL_API.G_CREATE THEN
72 IF p_qp_product_rec.qp_product_id IS NOT NULL AND p_qp_product_rec.qp_product_id <> FND_API.G_MISS_NUM THEN
73 IF OZF_Utility_PVT.check_uniqueness('ozf_qp_products','qp_product_id = ''' || p_qp_product_rec.qp_product_id ||'''') = FND_API.g_false THEN
74 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_QP_PROD_ID_DUP');
75 x_return_status := FND_API.g_ret_sts_error;
76 return;
77 END IF;
78 END IF;
79
80 IF
81 (
82 p_qp_product_rec.off_discount_product_id IS NOT NULL AND p_qp_product_rec.off_discount_product_id <> FND_API.G_MISS_NUM
83 )
84 AND
85 (
86 p_qp_product_rec.pricing_attribute_id IS NOT NULL AND p_qp_product_rec.pricing_attribute_id <> FND_API.G_MISS_NUM
87 )
88 THEN
89 IF OZF_Utility_PVT.check_uniqueness('ozf_qp_products','off_discount_product_id = ' || p_qp_product_rec.off_discount_product_id || ' AND pricing_attribute_id = ' ||p_qp_product_rec.pricing_attribute_id) = FND_API.g_false THEN
90 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_QP_PROD_DUP');
91 x_return_status := FND_API.g_ret_sts_error;
92 return;
93 END IF;
94 END IF;
95 END IF;
96
97
98 END check_qp_prod_uk_items;
99
100
101 PROCEDURE check_qp_prod_fk_items(
102 p_qp_product_rec IN qp_product_rec_type
103 , p_validation_mode IN VARCHAR2
104 , x_return_status OUT NOCOPY VARCHAR2
105 )
106 IS
107 BEGIN
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109 IF p_qp_product_rec.off_discount_product_id IS NOT NULL AND p_qp_product_rec.off_discount_product_id <> FND_API.G_MISS_NUM THEN
110 IF ozf_utility_pvt.check_fk_exists('ozf_offer_discount_products','off_discount_product_id',to_char(p_qp_product_rec.off_discount_product_id)) = FND_API.g_false THEN
111 OZF_Utility_PVT.Error_Message('OZF_INVALID_OZF_PROD_ID' );
112 x_return_status := FND_API.g_ret_sts_error;
113 return;
114 END IF;
115 END IF;
116
117 IF p_qp_product_rec.pricing_attribute_id IS NOT NULL AND p_qp_product_rec.pricing_attribute_id <> FND_API.G_MISS_NUM THEN
118 IF ozf_utility_pvt.check_fk_exists('qp_pricing_attributes','pricing_attribute_id',to_char(p_qp_product_rec.pricing_attribute_id)) = FND_API.g_false THEN
119 OZF_Utility_PVT.Error_Message('OZF_INVALID_OZF_PROD_ID' );
120 x_return_status := FND_API.g_ret_sts_error;
121 return;
122 END IF;
123 END IF;
124 END check_qp_prod_fk_items;
125
126
127 PROCEDURE check_qp_prod_attr(
128 p_qp_product_rec IN qp_product_rec_type
129 , p_validation_mode IN VARCHAR2
130 , x_return_status OUT NOCOPY VARCHAR2
131 )
132 IS
133 CURSOR c_list_header(p_pricing_attribute_id NUMBER)
134 IS
135 SELECT list_header_id FROM qp_pricing_attributes WHERE pricing_attribute_id = p_pricing_attribute_id;
136 l_list_header NUMBER;
137 CURSOR c_list_header2 (p_off_discount_product_id NUMBER)
138 IS
139 SELECT qp_list_header_id FROM ozf_offers WHERE offer_id = (SELECT offer_id FROM ozf_offer_discount_products WHERE off_discount_product_id = p_off_discount_product_id);
140 l_list_header2 NUMBER;
141 BEGIN
142 x_return_status := FND_API.G_RET_STS_SUCCESS;
143 OPEN c_list_header(p_qp_product_rec.pricing_attribute_id);
144 FETCH c_list_header INTO l_list_header;
145 CLOSE c_list_header;
146
147 OPEN c_list_header2(p_qp_product_rec.off_discount_product_id);
148 FETCH c_list_header2 INTO l_list_header2;
149 CLOSE c_list_header2;
150
151 IF l_list_header2 <> l_list_header THEN
152 OZF_Utility_PVT.Error_Message('OZF_OFFR_INVALID_PROD_PAIR');
153 x_return_status := FND_API.g_ret_sts_error;
154 END IF;
155 END check_qp_prod_attr;
156
157 PROCEDURE check_ozf_qp_prod_items(
158 p_qp_product_rec IN qp_product_rec_type
159 , p_validation_mode IN VARCHAR2
160 , x_return_status OUT NOCOPY VARCHAR2
161 )
162 IS
163 BEGIN
164 x_return_status := FND_API.G_RET_STS_SUCCESS;
165 check_qp_prod_req_items
166 (
167 p_qp_product_rec => p_qp_product_rec
168 , p_validation_mode => p_validation_mode
169 , x_return_status => x_return_status
170 );
171 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
172 RAISE FND_API.G_EXC_ERROR;
173 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 END IF;
176
177 check_qp_prod_uk_items
178 (
179 p_qp_product_rec => p_qp_product_rec
180 , p_validation_mode => p_validation_mode
181 , x_return_status => x_return_status
182 );
183 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
184 RAISE FND_API.G_EXC_ERROR;
185 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 END IF;
188
189 check_qp_prod_fk_items
190 (
191 p_qp_product_rec => p_qp_product_rec
192 , p_validation_mode => p_validation_mode
193 , x_return_status => x_return_status
194 );
195 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
196 RAISE FND_API.G_EXC_ERROR;
197 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
198 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
199 END IF;
200
201 /* check_qp_prod_attr(
202 p_qp_product_rec => p_qp_product_rec
203 , p_validation_mode => p_validation_mode
204 , x_return_status => x_return_status
205 );
206 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
207 RAISE FND_API.G_EXC_ERROR;
208 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
209 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
210 END IF;
211 */
212 END check_ozf_qp_prod_items;
213
214
215 PROCEDURE Validate_ozf_qp_products
216 (
217 p_api_version_number IN NUMBER,
218 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
219 p_commit IN VARCHAR2 := FND_API.G_FALSE,
220 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
221 p_validation_mode IN VARCHAR2,
222
223 x_return_status OUT NOCOPY VARCHAR2,
224 x_msg_count OUT NOCOPY NUMBER,
225 x_msg_data OUT NOCOPY VARCHAR2,
226
227 p_qp_product_rec IN qp_product_rec_type
228 )
229 IS
230 l_api_name CONSTANT VARCHAR2(30) := 'Validate_ozf_qp_products';
231 l_api_version_number CONSTANT NUMBER := 1.0;
232 l_object_version_number NUMBER;
233 l_qp_product_rec qp_product_rec_type;
234
235 BEGIN
236 -- initialize
237
238 -- Standard call to check for call compatibility.
239 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
240 p_api_version_number,
241 l_api_name,
242 G_PKG_NAME)
243 THEN
244 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
245 END IF;
246 -- Initialize message list if p_init_msg_list is set to TRUE.
247
248 -- Debug Message
249 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
250
251 -- Initialize API return status to SUCCESS
252 x_return_status := FND_API.G_RET_STS_SUCCESS;
253
254 -- check items
255 IF p_validation_level >= JTF_PLSQL_API.G_VALID_LEVEL_ITEM THEN
256 check_ozf_qp_prod_items(
257 p_qp_product_rec => p_qp_product_rec
258 , p_validation_mode => p_validation_mode
259 , x_return_status => x_return_status
260 );
261 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
262 RAISE FND_API.G_EXC_ERROR;
263 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
264 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
265 END IF;
266 END IF;
267 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
268
269 EXCEPTION
270
271 WHEN OZF_Utility_PVT.resource_locked THEN
272 x_return_status := FND_API.g_ret_sts_error;
273 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
274
275 WHEN FND_API.G_EXC_ERROR THEN
276 -- ROLLBACK TO validate_market_options_pvt;
277 x_return_status := FND_API.G_RET_STS_ERROR;
278 -- Standard call to get message count and if count=1, get the message
279 FND_MSG_PUB.Count_And_Get (
280 p_encoded => FND_API.G_FALSE,
281 p_count => x_msg_count,
282 p_data => x_msg_data
283 );
284
285 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
286 -- ROLLBACK TO validate_market_options_pvt;
287 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
288 -- Standard call to get message count and if count=1, get the message
289 FND_MSG_PUB.Count_And_Get (
290 p_encoded => FND_API.G_FALSE,
291 p_count => x_msg_count,
292 p_data => x_msg_data
293 );
294
295 WHEN OTHERS THEN
296 -- ROLLBACK TO validate_market_options_pvt;
297 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
298 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
299 THEN
300 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
301 END IF;
302 -- Standard call to get message count and if count=1, get the message
303 FND_MSG_PUB.Count_And_Get (
304 p_encoded => FND_API.G_FALSE,
305 p_count => x_msg_count,
306 p_data => x_msg_data
307 );
308
309 END Validate_ozf_qp_products;
310
311 -- ==============================================================================
312 -- Start of Comments
313 -- ==============================================================================
314 -- API Name
315 -- Create_ozf_qp_product
316 -- Type
317 -- Private
318 -- Pre-Req
319 -- Parameters
320 --
321 -- IN
322 -- p_api_version_number IN NUMBER Required
323 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
324 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
325 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
326 -- p_qp_product_rec IN qp_product_rec_type
327 -- OUT NOCOPY
328 -- x_return_status OUT NOCOPY VARCHAR2
329 -- x_msg_count OUT NOCOPY NUMBER
330 -- x_msg_data OUT NOCOPY VARCHAR2
331 -- x_qp_product_id OUT NOCOPY NUMBER. qp product id of the market option just created
332 -- Version : Current version 1.0
333 --
334 -- History
335 --
336 -- Description
337 -- : Method to Create relation between ozf and qp products
338 -- End of Comments
339 -- ==============================================================================
340
341 PROCEDURE Create_ozf_qp_product(
342 p_api_version_number IN NUMBER,
343 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
344 p_commit IN VARCHAR2 := FND_API.G_FALSE,
345 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
346
347 x_return_status OUT NOCOPY VARCHAR2,
348 x_msg_count OUT NOCOPY NUMBER,
349 x_msg_data OUT NOCOPY VARCHAR2,
350
351 p_qp_product_rec IN qp_product_rec_type ,
352 x_qp_product_id OUT NOCOPY NUMBER
353 )
354 IS
355 l_api_version_number NUMBER := 1.0;
356 l_api_name VARCHAR2(30) := 'Create_ozf_qp_product';
357 l_qp_product_rec qp_product_rec_type;
358 l_qp_product_id NUMBER;
359 l_dummy NUMBER;
360 l_object_version_number NUMBER;
361
362 CURSOR c_id
363 IS
364 SELECT ozf_qp_products_s.nextval FROM dual;
365 CURSOR c_id_exists (p_id IN NUMBER) IS
366 SELECT 1
367 FROM ozf_qp_products
368 WHERE qp_product_id = p_id;
369
370 BEGIN
371 --INITIALIZE
372 -- save point
373 SAVEPOINT Create_ozf_qp_product_pvt;
374 -- check api compatibility
375 IF NOT FND_API.Compatible_Api_Call(
376 l_api_version_number
377 , p_api_version_number
378 , l_api_name
379 , G_PKG_NAME) THEN
380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
381 END IF;
382 -- initialize messages
383 IF FND_API.to_boolean(p_init_msg_list) THEN
384 FND_MSG_PUB.initialize;
385 END IF;
386 -- debug message start
387 ozf_utility_pvt.debug_message('Private API: '|| l_api_name|| 'start');
388 -- set return status
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390
391 IF FND_GLOBAL.USER_ID IS NULL THEN
392 OZF_Utility_PVT.Error_Message('USER_PROFILE_MISSING');
393 x_return_status := FND_API.G_RET_STS_ERROR;
394 END IF;
395
396 l_qp_product_rec := p_qp_product_rec;
397
398 IF p_qp_product_rec.qp_product_id IS NULL OR p_qp_product_rec.qp_product_id = FND_API.g_miss_num THEN
399 LOOP
400 l_dummy := NULL;
401 OPEN c_id;
402 FETCH c_id INTO l_qp_product_id;
403 CLOSE c_id;
404
405 OPEN c_id_exists(l_qp_product_id);
406 FETCH c_id_exists INTO l_dummy;
407 CLOSE c_id_exists;
408 EXIT WHEN l_dummy IS NULL;
409 END LOOP;
410 ELSE
411 l_qp_product_id := p_qp_product_rec.qp_product_id;
412 END IF;
413
414 -- validate
415 validate_ozf_qp_products
416 (
417 p_api_version_number => p_api_version_number
418 , p_init_msg_list => p_init_msg_list
419 , p_validation_level => p_validation_level
420 , p_validation_mode => JTF_PLSQL_API.G_CREATE
421 , x_return_status => x_return_status
422 , x_msg_count => x_msg_count
423 , x_msg_data => x_msg_data
424 , p_qp_product_rec => l_qp_product_rec
425 );
426 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
427 RAISE FND_API.G_EXC_ERROR;
428 END IF;
429 -- insert
430 OZF_QP_PRODUCTS_PKG.Insert_row
431 (
432 px_qp_product_id => l_qp_product_id
433 , p_off_discount_product_id => l_qp_product_rec.off_discount_product_id
434 , p_pricing_attribute_id => l_qp_product_rec.pricing_attribute_id
435 , px_object_version_number => l_object_version_number
436 , p_creation_date => sysdate
437 , p_created_by => FND_GLOBAL.USER_ID
438 , p_last_update_date => sysdate
439 , p_last_updated_by => FND_GLOBAL.USER_ID
440 , p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID
441 );
442
443 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
444 RAISE FND_API.G_EXC_ERROR;
445 END IF;
446
447 x_qp_product_id := l_qp_product_id;
448
449 IF FND_API.to_boolean(p_commit) THEN
450 COMMIT WORK;
451 END IF;
452
453 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
454
455 FND_MSG_PUB.COUNT_AND_GET
456 (
457 p_count => x_msg_count
458 ,p_data => x_msg_data
459 );
460
461 -- exception
462 EXCEPTION
463 WHEN FND_API.G_EXC_ERROR THEN
464 ROLLBACK TO create_ozf_qp_product_pvt;
465 x_return_status := FND_API.G_RET_STS_ERROR;
466 FND_MSG_PUB.COUNT_AND_GET
467 (
468 p_encoded => FND_API.G_FALSE
469 , p_count => x_msg_count
470 , p_data => x_msg_data
471 );
472
473 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
474 ROLLBACK TO create_ozf_qp_products_pvt;
475 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
476 FND_MSG_PUB.COUNT_AND_GET
477 (
478 p_encoded => FND_API.G_FALSE
479 , p_count => x_msg_count
480 , p_data => x_msg_data
481 );
482
483 WHEN OTHERS THEN
484 ROLLBACK TO create_ozf_qp_products_pvt;
485 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
486 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
487 THEN
488 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
489 END IF;
490 -- Standard call to get message count and if count=1, get the message
491 FND_MSG_PUB.Count_And_Get (
492 p_encoded => FND_API.G_FALSE,
493 p_count => x_msg_count,
494 p_data => x_msg_data
495 );
496
497 END Create_ozf_qp_product;
498
499
500 -- ==============================================================================
501 -- Start of Comments
502 -- ==============================================================================
503 -- API Name
504 -- Update_market_options
505 -- Type
506 -- Private
507 -- Pre-Req
508 -- validate_market_options
509 -- Parameters
510 --
511 -- IN
512 -- p_api_version_number IN NUMBER Required
513 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
514 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
515 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
516 -- p_qp_product_rec IN qp_product_rec_type
517 -- OUT
518 -- x_return_status OUT NOCOPY VARCHAR2
519 -- x_msg_count OUT NOCOPY NUMBER
520 -- x_msg_data OUT NOCOPY VARCHAR2
521 -- Version : Current version 1.0
522 --
523 -- History
524 --
525 -- Description
526 -- : Method to Update ozf qp product relation
527 -- End of Comments
528 -- ==============================================================================
529 PROCEDURE Update_ozf_qp_product(
530 p_api_version_number IN NUMBER,
531 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
532 p_commit IN VARCHAR2 := FND_API.G_FALSE,
533 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
534
535 x_return_status OUT NOCOPY VARCHAR2,
536 x_msg_count OUT NOCOPY NUMBER,
537 x_msg_data OUT NOCOPY VARCHAR2,
538
539 p_qp_product_rec IN qp_product_rec_type
540 )
541 IS
542
543 l_api_name CONSTANT VARCHAR2(30) := 'Update_ozf_qp_product';
544 l_api_version_number CONSTANT NUMBER:= 1.0;
545
546
547 CURSOR c_get_qp_prod(p_qp_product_id NUMBER, p_object_version_number NUMBER) IS
548 SELECT *
549 FROM ozf_qp_products
550 WHERE qp_product_id = p_qp_product_id
551 AND object_version_number = p_object_version_number;
552 -- Hint: Developer need to provide Where clause
553
554 -- Local Variables
555 l_object_version_number NUMBER;
556 l_market_option_id NUMBER;
557 l_ref_qp_prod_rec c_get_qp_prod%ROWTYPE ;
558 l_tar_qp_prod_rec qp_product_rec_type := p_qp_product_rec ;
559 l_rowid ROWID;
560
561 BEGIN
562 -- initialize
563 SAVEPOINT Update_ozf_qp_product_pvt;
564 IF NOT FND_API.Compatible_api_call(l_api_version_number
565 , p_api_version_number
566 , l_api_name
567 , g_pkg_name)
568 THEN
569 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
570 END IF;
571
572 IF FND_API.to_Boolean( p_init_msg_list )
573 THEN
574 FND_MSG_PUB.initialize;
575 END IF;
576 -- Debug Message
577 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
578
579 -- Initialize API return status to SUCCESS
580 x_return_status := FND_API.G_RET_STS_SUCCESS;
581
582 OPEN c_get_qp_prod( l_tar_qp_prod_rec.qp_product_id,l_tar_qp_prod_rec.object_version_number);
583 FETCH c_get_qp_prod INTO l_ref_qp_prod_rec ;
584 If ( c_get_qp_prod%NOTFOUND) THEN
585 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
586 , p_token_name => 'INFO'
587 , p_token_value => 'OZF_MARKET_OPTIONS') ;
588 RAISE FND_API.G_EXC_ERROR;
589 END IF;
590 CLOSE c_get_qp_prod;
591
592 If (l_tar_qp_prod_rec.object_version_number is NULL or
593 l_tar_qp_prod_rec.object_version_number = FND_API.G_MISS_NUM ) Then
594 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
595 , p_token_name => 'COLUMN'
596 , p_token_value => 'Last_Update_Date') ;
597 RAISE FND_API.G_EXC_ERROR;
598 End if;
599 -- Check Whether record has been changed by someone else
600 If (l_tar_qp_prod_rec.object_version_number <> l_ref_qp_prod_rec.object_version_number) Then
601 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
602 , p_token_name => 'INFO'
603 , p_token_value => 'Ozf_Market_Options') ;
604 RAISE FND_API.G_EXC_ERROR;
605 End if;
606 -- validate
607 validate_ozf_qp_products
608 (
609 p_api_version_number => p_api_version_number
610 , p_init_msg_list => p_init_msg_list
611 , p_validation_level => p_validation_level
612 , p_validation_mode => JTF_PLSQL_API.G_UPDATE
613 , x_return_status => x_return_status
614 , x_msg_count => x_msg_count
615 , x_msg_data => x_msg_data
616 , p_qp_product_rec => l_tar_qp_prod_rec
617 );
618 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
619 RAISE FND_API.G_EXC_ERROR;
620 END IF;
621
622 -- update
623 OZF_QP_PRODUCTS_PKG.Update_Row(
624 p_qp_product_id => l_tar_qp_prod_rec.qp_product_id
625 , p_off_discount_product_id => l_tar_qp_prod_rec.off_discount_product_id
626 , p_pricing_attribute_id => l_tar_qp_prod_rec.pricing_attribute_id
627 , p_object_version_number => l_tar_qp_prod_rec.object_version_number
628 , p_last_update_date => sysdate
629 , p_last_updated_by => FND_GLOBAL.USER_ID
630 , p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID
631 );
632 -- get messages
633 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
634 RAISE FND_API.G_EXC_ERROR;
635 END IF;
636 -- commit
637 IF FND_API.to_boolean(p_commit) THEN
638 COMMIT WORK;
639 END IF;
640 ozf_utility_pvt.debug_message('Private API : '||l_api_name || ' End');
641 FND_MSG_PUB.count_and_get
642 (
643 p_count => x_msg_count
644 , p_data => x_msg_data
645 );
646 -- exception
647 EXCEPTION
648 WHEN OZF_Utility_PVT.resource_locked THEN
649 x_return_status := FND_API.g_ret_sts_error;
650 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
651
652 WHEN FND_API.G_EXC_ERROR THEN
653 ROLLBACK TO Update_ozf_qp_product_pvt;
654 x_return_status := FND_API.G_RET_STS_ERROR;
655 FND_MSG_PUB.count_and_get(
656 p_encoded => FND_API.g_false
657 , p_count => x_msg_count
658 , p_data => x_msg_data
659 );
660 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
661 ROLLBACK TO Update_ozf_qp_product_pvt;
662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
663 FND_MSG_PUB.count_and_get(
664 p_encoded => FND_API.G_FALSE
665 , p_count => x_msg_count
666 , p_data => x_msg_data
667 );
668 WHEN OTHERS THEN
669 ROLLBACK TO Update_ozf_qp_product_pvt;
670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
671 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
672 THEN
673 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
674 END IF;
675 -- Standard call to get message count and if count=1, get the message
676 FND_MSG_PUB.Count_And_Get (
677 p_encoded => FND_API.G_FALSE,
678 p_count => x_msg_count,
679 p_data => x_msg_data
680 );
681
682 NULL;
683 END Update_ozf_qp_product;
684
685 -- ==============================================================================
686 -- Start of Comments
687 -- ==============================================================================
688 -- API Name
689 -- Delete_ozf_qp_product
690 -- Type
691 -- Private
692 -- Pre-Req
693 -- Parameters
694 --
695 -- IN
696 -- p_api_version_number IN NUMBER
697 -- p_init_msg_list IN VARCHAR2
698 -- p_commit IN VARCHAR2
699 -- p_validation_level IN NUMBER
700 -- p_qp_product_id IN NUMBER
701 -- p_object_version_number IN NUMBER
702
703 --
704 -- OUT
705 -- x_return_status OUT NOCOPY VARCHAR2
706 -- x_msg_count OUT NOCOPY NUMBER
707 -- x_msg_data OUT NOCOPY VARCHAR2
708
709 -- Version : Current version 1.0
710 --
711 -- History
712 -- Mon Jun 20 2005:7/55 PM Created
713 --
714 -- Description
715 -- End of Comments
716 -- ==============================================================================
717 PROCEDURE Delete_ozf_qp_product(
718 p_api_version_number IN NUMBER,
719 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
720 p_commit IN VARCHAR2 := FND_API.G_FALSE,
721 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
722 x_return_status OUT NOCOPY VARCHAR2,
723 x_msg_count OUT NOCOPY NUMBER,
724 x_msg_data OUT NOCOPY VARCHAR2,
725 p_qp_product_id IN NUMBER,
726 p_object_version_number IN NUMBER
727 )
728 IS
729 l_api_version_number CONSTANT number := 1.0;
730 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ozf_qp_product';
731 BEGIN
732 -- initialize
733 SAVEPOINT Delete_ozf_qp_product_pvt;
734 IF NOT FND_API.Compatible_API_call
735 (
736 l_api_version_number
737 , p_api_version_number
738 , l_api_name
739 , g_pkg_name
740 ) THEN
741 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
742 END IF;
743 IF FND_API.to_boolean(p_init_msg_list) THEN
744 FND_MSG_PUB.initialize;
745 END IF;
746 x_return_status := FND_API.G_RET_STS_SUCCESS;
747 -- delete
748 OZF_QP_PRODUCTS_PKG.Delete_Row(
749 p_qp_product_id => p_qp_product_id
750 , p_object_version_number => p_object_version_number
751 );
752
753 -- commit
754 IF FND_API.to_boolean(p_commit) THEN
755 COMMIT WORK;
756 END IF;
757 -- get messages
758 FND_MSG_PUB.count_and_get(
759 p_count => x_msg_count
760 , p_data => x_msg_data
761 );
762 -- exception
763 EXCEPTION
764 WHEN OZF_UTILITY_PVT.resource_locked THEN
765 OZF_Utility_PVT.Error_Message('OZF_API_RESOURCE_LOCKED');
766 x_return_status := FND_API.g_ret_sts_error;
767 WHEN FND_API.G_EXC_ERROR THEN
768 rollback to Delete_ozf_qp_product_pvt;
769 x_return_status := FND_API.G_RET_STS_ERROR;
770 FND_MSG_PUB.COUNT_AND_GET(
771 p_encoded => FND_API.G_FALSE
772 , p_count => x_msg_count
773 , p_data => x_msg_data
774 );
775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
776 ROLLBACK TO Delete_ozf_qp_product_pvt;
777 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
778 FND_MSG_PUB.count_and_get(
779 p_encoded => FND_API.G_FALSE
780 , p_count => x_msg_count
781 , p_data => x_msg_data
782 );
783 WHEN OTHERS THEN
784 ROLLBACK TO Delete_market_options_PVT;
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
787 THEN
788 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
789 END IF;
790 -- Standard call to get message count and if count=1, get the message
791 FND_MSG_PUB.Count_And_Get (
792 p_encoded => FND_API.G_FALSE,
793 p_count => x_msg_count,
794 p_data => x_msg_data
795 );
796
797 END Delete_ozf_qp_product;
798
799
800 END OZF_QP_PRODUCTS_PVT;
801