[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_ADJ_PRODUCTS_PVT
Source
1 PACKAGE BODY OZF_OFFER_ADJ_PRODUCTS_PVT AS
2 /* $Header: ozfvoadpb.pls 120.9 2006/05/22 19:05:45 rssharma noship $ */
3 /**
4 Tue Aug 02 2005:10/26 PM RSSHARMA Created
5 Mon Oct 03 2005:6/44 PM RSSHARMA Added duplicate check for Volume Offer Adjustment Products
6 Mon May 15 2006:5/6 PM Fixed bug # 5131158. Added check entity method.If the Adjustment is backdated, it raises error message
7 saying, products cannot be added to a backdated volume offer adjustment
8 Mon May 22 2006:12/1 PM RSSHARMA Fixed debug to print only on debug high
9 */
10 g_pkg_name CONSTANT VARCHAR2(30) := 'OZF_OFFER_ADJ_PRODUCTS_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(30) := 'ozfvoadpb.pls';
12
13 PROCEDURE check_adj_prod_req_items
14 (
15 p_adj_prod IN offer_adj_prod_rec
16 , p_validation_mode IN VARCHAR2
17 , x_return_status OUT NOCOPY VARCHAR2
18 )
19 IS
20 BEGIN
21 x_return_status := FND_API.G_RET_STS_SUCCESS;
22 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
23 IF p_adj_prod.offer_adjustment_id IS NULL OR p_adj_prod.offer_adjustment_id = FND_API.G_MISS_NUM THEN
24 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','offer_adjustment_id');
25 x_return_status := FND_API.g_ret_sts_error;
26 return;
27 END IF;
28 /*
29 IF p_adj_prod.product_context IS NULL OR p_adj_prod.product_context = FND_API.G_MISS_CHAR THEN
30 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','product_context');
31 x_return_status := FND_API.g_ret_sts_error;
32 return;
33 END IF;
34
35 IF p_adj_prod.product_attribute IS NULL OR p_adj_prod.product_attribute = FND_API.G_MISS_CHAR THEN
36 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','product_attribute');
37 x_return_status := FND_API.g_ret_sts_error;
38 return;
39 END IF;
40 IF p_adj_prod.product_attr_value IS NULL OR p_adj_prod.product_attr_value = FND_API.G_MISS_CHAR THEN
41 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','product_attr_value');
42 x_return_status := FND_API.g_ret_sts_error;
43 return;
44 END IF;*/
45 ELSE
46 IF p_adj_prod.offer_adjustment_id = FND_API.G_MISS_NUM THEN
47 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','offer_adjustment_id');
48 x_return_status := FND_API.g_ret_sts_error;
49 return;
50 END IF;
51 IF p_adj_prod.offer_adjustment_product_id = FND_API.G_MISS_NUM THEN
52 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','OFFER_ADJUSTMENT_PRODUCT_ID');
53 x_return_status := FND_API.g_ret_sts_error;
54 return;
55 END IF;
56 IF p_adj_prod.object_version_number = FND_API.G_MISS_NUM THEN
57 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','OBJECT_VERSION_NUMBER');
58 x_return_status := FND_API.g_ret_sts_error;
59 return;
60 END IF;
61 END IF;
62 END check_adj_prod_req_items;
63
64 FUNCTION get_offer_type
65 (
66 p_offerAdjustmentId NUMBER
67 )
68 RETURN VARCHAR2
69 IS
70 CURSOR c_offerType(cp_offerAdjustmentId NUMBER) IS
71 SELECT a.offer_type FROM ozf_offers a, ozf_offer_adjustments_b b
72 WHERE a.qp_list_header_id = b.list_header_id
73 AND b.offer_adjustment_id = cp_offerAdjustmentId;
74 l_offerType VARCHAR2(30);
75 BEGIN
76 OPEN c_offerType(p_offerAdjustmentId);
77 FETCH c_offerType into l_offerType;
78 IF c_offerType%NOTFOUND THEN
79 l_offerType := NULL;
80 END IF;
81 RETURN l_offerType;
82 END get_offer_type;
83
84 PROCEDURE check_adj_prod_uk_items
85 (
86 p_adj_prod IN offer_adj_prod_rec
87 , p_validation_mode IN VARCHAR2
88 , x_return_status OUT NOCOPY VARCHAR2
89 )
90 IS
91 /*CURSOR c_prod(p_offer_adjustment_id NUMBER,p_product_context VARCHAR2, p_product_attribute VARCHAR2, p_product_attr_value VARCHAR2, p_excluder_flag VARCHAR2)
92 IS
93 SELECT 1 FROM dual WHERE EXISTS
94 (SELECT 'X' FROM ozf_offer_discount_products a, ozf_offer_adjustments_b b, ozf_offers c
95 WHERE
96 a.offer_id = c.offer_id
97 AND c.qp_list_header_id = b.list_header_id
98 AND b.offer_adjustment_id = p_offer_adjustment_id
99 AND product_context = p_product_context
100 AND product_attribute = p_product_attribute
101 AND p_product_attr_value = p_product_attr_value
102 AND excluder_flag = p_excluder_flag ;
103 */
104 CURSOR c_offerId (cp_offerAdjustmentId NUMBER) IS
105 SELECT a.offer_id
106 FROM ozf_offers a, ozf_offer_adjustments_b b
107 WHERE a.qp_list_header_id = b.list_header_id
108 AND b.offer_adjustment_id = cp_offerAdjustmentId;
109
110 l_attr varchar2(500) := 'product_attribute = ''' || p_adj_prod.product_attribute ||''' AND product_attr_value = '''|| p_adj_prod.product_attr_value ;
111 l_attr2 varchar2(500):= ''' AND apply_discount_flag = '''||p_adj_prod.apply_discount_flag || ''' AND offer_id = ';
112 l_valid_flag VARCHAR2(10);
113 l_offerType VARCHAR2(30);
114 l_offerId NUMBER;
115 BEGIN
116 x_return_status := FND_API.G_RET_STS_SUCCESS;
117 IF p_validation_mode = JTF_PLSQL_API.G_CREATE THEN
118 IF p_adj_prod.offer_adjustment_product_id IS NOT NULL AND p_adj_prod.offer_adjustment_product_id <> FND_API.G_MISS_NUM THEN
119 IF OZF_UTILITY_PVT.CHECK_UNIQUENESS('ozf_offer_adjustment_products','offer_adjustment_product_id = '|| p_adj_prod.offer_adjustment_product_id) = FND_API.G_FALSE THEN
120 OZF_Utility_PVT.Error_Message('OZF_OFFR_ADJ_PROD_ID_DUP');
121 x_return_status := FND_API.g_ret_sts_error;
122 return;
123 END IF;
124 END IF;
125 END IF;
126 IF
127 (p_adj_prod.product_context IS NOT NULL AND p_adj_prod.product_context <> FND_API.G_MISS_CHAR)
128 AND
129 (p_adj_prod.product_attribute IS NOT NULL AND p_adj_prod.product_attribute <> FND_API.G_MISS_CHAR)
130 AND
131 (p_adj_prod.product_attr_value IS NOT NULL AND p_adj_prod.product_attr_value <> FND_API.G_MISS_CHAR)
132 AND
133 (p_adj_prod.excluder_flag IS NOT NULL AND p_adj_prod.excluder_flag <> FND_API.G_MISS_CHAR)
134 THEN
135 null;
136 END IF;
137 l_offerType := get_offer_type(p_adj_prod.offer_adjustment_id);
138 OZF_Offer_Adj_Line_PVT.debug_message('OfferType is :'||l_offerType);
139 IF l_offerType = 'VOLUME_OFFER' THEN
140 OPEN c_offerId(p_adj_prod.offer_adjustment_id);
141 FETCH c_offerId INTO l_offerId;
142 CLOSE c_offerId;
143
144 IF (p_adj_prod.product_attr_value IS NOT NULL AND p_adj_prod.product_attr_value <> FND_API.g_miss_char) THEN
145 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
146 'ozf_offer_discount_products',
147 l_attr || l_attr2 || l_offerId
148 );
149 IF l_valid_flag = FND_API.g_false THEN
150 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_VO_PROD_DUP');
151 x_return_status := FND_API.g_ret_sts_error;
152 return;
153 END IF;
154
155 END IF;
156 END IF;
157
158 END check_adj_prod_uk_items;
159
160 PROCEDURE check_adj_prod_fk_items
161 (
162 p_adj_prod IN offer_adj_prod_rec
163 , p_validation_mode IN VARCHAR2
164 , x_return_status OUT NOCOPY VARCHAR2
165 )
166 IS
167 l_vo_prod_rec OZF_Volume_Offer_disc_PVT.vo_prod_rec_type;
168 BEGIN
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170 IF p_adj_prod.offer_adjustment_id IS NOT NULL AND p_adj_prod.offer_adjustment_id <> FND_API.G_MISS_NUM THEN
171 IF OZF_UTILITY_PVT.check_fk_exists('ozf_offer_adjustments_b','offer_adjustment_id',to_char(p_adj_prod.offer_adjustment_id)) = FND_API.G_FALSE THEN
172 OZF_Utility_PVT.Error_Message('OZF_OFFR_ADJ_PROD_INV_ADJ_ID');
173 x_return_status := FND_API.g_ret_sts_error;
174 return;
175 END IF;
176 END IF;
177 OZF_Offer_Adj_Line_PVT.debug_message('OfferDiscountLineid is: '||p_adj_prod.offer_discount_line_id);
178 IF p_adj_prod.offer_discount_line_id IS NOT NULL AND p_adj_prod.offer_discount_line_id <> FND_API.G_MISS_NUM THEN
179 IF OZF_UTILITY_PVT.check_fk_exists('OZF_OFFER_DISCOUNT_LINES', 'offer_discount_line_id', to_char(p_adj_prod.offer_discount_line_id)) = FND_API.G_FALSE THEN
180 OZF_Utility_PVT.Error_Message('OZF_OFFR_ADJ_PROD_INV_DISC_LINE');
181 x_return_status := FND_API.g_ret_sts_error;
182 return;
183 END IF;
184 END IF;
185 OZF_Offer_Adj_Line_PVT.debug_message('OfferDiscountProductId is : '||p_adj_prod.off_discount_product_id);
186 IF p_adj_prod.off_discount_product_id IS NOT NULL AND p_adj_prod.off_discount_product_id <> FND_API.G_MISS_NUM THEN
187 IF ozf_utility_pvt.CHECK_FK_EXISTS('ozf_offer_discount_products','off_discount_product_id',to_char(p_adj_prod.off_discount_product_id)) = FND_API.G_FALSE THEN
188 OZF_Utility_PVT.Error_Message('OZF_OFFR_ADJ_INV_PROD_LINE');
189 x_return_status := FND_API.g_ret_sts_error;
190 return;
191 END IF;
192 END IF;
193 OZF_Offer_Adj_Line_PVT.debug_message('Calling Product Ctx validation');
194
195 IF
196 (p_adj_prod.product_context IS NOT NULL AND p_adj_prod.product_context <> FND_API.G_MISS_CHAR )
197 AND
198 (p_adj_prod.product_attribute IS NOT NULL AND p_adj_prod.product_attribute <> FND_API.G_MISS_CHAR)
199 AND
200 (p_adj_prod.product_attr_value IS NOT NULL AND p_adj_prod.product_attr_value <> FND_API.G_MISS_CHAR)
201 THEN
202 l_vo_prod_rec.product_context := p_adj_prod.product_context;
203 l_vo_prod_rec.product_attribute := p_adj_prod.product_attribute;
204 l_vo_prod_rec.product_attr_value := p_adj_prod.product_attr_value;
205 OZF_Offer_Adj_Line_PVT.debug_message('Calling Product Ctx validation');
206 OZF_Volume_Offer_disc_PVT.Check_vo_product_attr(
207 p_vo_prod_rec => l_vo_prod_rec
208 , x_return_status => x_return_status
209 );
210 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
211 RAISE FND_API.G_EXC_ERROR;
212 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
213 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
214 END IF;
215
216 END IF;
217 END check_adj_prod_fk_items;
218
219 PROCEDURE check_adj_prod_lkup_items
220 (
221 p_adj_prod IN offer_adj_prod_rec
222 , p_validation_mode IN VARCHAR2
223 , x_return_status OUT NOCOPY VARCHAR2
224 )
225 IS
226 BEGIN
227 x_return_status := FND_API.G_RET_STS_SUCCESS;
228 END check_adj_prod_lkup_items;
229
230 PROCEDURE check_adj_prod_attr
231 (
232 p_adj_prod IN offer_adj_prod_rec
233 , p_validation_mode IN VARCHAR2
234 , x_return_status OUT NOCOPY VARCHAR2
235 )
236 IS
237 BEGIN
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239 IF p_adj_prod.excluder_flag IS NOT NULL AND p_adj_prod.excluder_flag <> fnd_api.g_miss_char THEN
240 IF p_adj_prod.excluder_flag <> 'Y' AND p_adj_prod.excluder_flag <> 'N' THEN
241 OZF_Utility_PVT.Error_Message('OZF_OFFR_ADJ_PROD_INV_EXCL');
242 x_return_status := FND_API.g_ret_sts_error;
243 return;
244 END IF;
245 END IF;
246 IF p_adj_prod.apply_discount_flag IS NOT NULL AND p_adj_prod.apply_discount_flag <> FND_API.G_MISS_CHAR THEN
247 IF p_adj_prod.apply_discount_flag <> 'Y' and p_adj_prod.apply_discount_flag <> 'N' THEN
248 OZF_Utility_PVT.Error_Message('OZF_OFFR_ADJ_PROD_INV_APP_DISC');
249 x_return_status := FND_API.g_ret_sts_error;
250 return;
251
252 END IF;
253 END IF;
254
255
256 IF p_adj_prod.include_volume_flag IS NOT NULL AND p_adj_prod.include_volume_flag <> FND_API.G_MISS_CHAR THEN
257 IF p_adj_prod.include_volume_flag <> 'Y' and p_adj_prod.include_volume_flag <> 'N' THEN
258 OZF_Utility_PVT.Error_Message('OZF_OFFR_ADJ_PROD_INV_INCL_VOL');
259 x_return_status := FND_API.g_ret_sts_error;
260 return;
261
262 END IF;
263 END IF;
264 -- if new product is added then product_context, product_attribute, product_attr_value, apply_discount_flag , include_volume_flag are required
265 IF p_adj_prod.off_discount_product_id IS NULL OR p_adj_prod.off_discount_product_id = FND_API.G_MISS_NUM THEN -- if this is a new product
266 IF p_validation_mode = JTF_PLSQL_API.G_CREATE THEN
267 IF p_adj_prod.product_context IS NULL OR p_adj_prod.product_context = FND_API.G_MISS_CHAR THEN
268 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','PRODUCT_CONTEXT');
269 x_return_status := FND_API.g_ret_sts_error;
270 return;
271 END IF;
272 IF p_adj_prod.product_attribute IS NULL OR p_adj_prod.product_attribute = FND_API.G_MISS_CHAR THEN
273 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','PRODUCT_ATTRIBUTE');
274 x_return_status := FND_API.g_ret_sts_error;
275 return;
276 END IF;
277 IF p_adj_prod.product_attr_value IS NULL OR p_adj_prod.product_attr_value = FND_API.G_MISS_CHAR THEN
278 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','PRODUCT_ATTR_VALUE');
279 x_return_status := FND_API.g_ret_sts_error;
280 return;
281 END IF;
282 IF p_adj_prod.apply_discount_flag IS NULL OR p_adj_prod.apply_discount_flag = FND_API.G_MISS_CHAR THEN
283 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','APPLY_DISCOUNT_FLAG');
284 x_return_status := FND_API.g_ret_sts_error;
285 return;
286 END IF;
287 IF p_adj_prod.include_volume_flag IS NULL OR p_adj_prod.include_volume_flag = FND_API.G_MISS_CHAR THEN
288 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','INCLUDE_VOLUME_FLAG');
289 x_return_status := FND_API.g_ret_sts_error;
290 return;
291 END IF;
292 ELSE
293 IF p_adj_prod.product_context = FND_API.G_MISS_CHAR THEN
294 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','product_context');
295 x_return_status := FND_API.g_ret_sts_error;
296 return;
297 END IF;
298 IF p_adj_prod.product_attribute = FND_API.G_MISS_CHAR THEN
299 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','product_attribute');
300 x_return_status := FND_API.g_ret_sts_error;
301 return;
302 END IF;
303 IF p_adj_prod.product_attr_value = FND_API.G_MISS_CHAR THEN
304 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','product_attr_value');
305 x_return_status := FND_API.g_ret_sts_error;
306 return;
307 END IF;
308 IF p_adj_prod.apply_discount_flag = FND_API.G_MISS_CHAR THEN
309 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','apply_discount_flag');
310 x_return_status := FND_API.g_ret_sts_error;
311 return;
312 END IF;
313 IF p_adj_prod.include_volume_flag = FND_API.G_MISS_CHAR THEN
314 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','include_volume_flag');
315 x_return_status := FND_API.g_ret_sts_error;
316 return;
317 END IF;
318 END IF;
319
320 END IF;
321 END check_adj_prod_attr;
322
323
324 PROCEDURE check_adj_prod_inter_attr
325 (
326 p_adj_prod IN offer_adj_prod_rec
327 , p_validation_mode IN VARCHAR2
328 , x_return_status OUT NOCOPY VARCHAR2
329 )
330 IS
331 CURSOR C(p_offer_discount_line_id NUMBER, p_off_discount_product_id NUMBER) is
332 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 'X'
333 FROM ozf_offer_discount_lines a
334 , ozf_offer_discount_products b
335 , ozf_offer_adjustments_b c
336 , ozf_offers d
337 WHERE a.offer_discount_line_id = b.offer_discount_line_id
338 AND a.offer_id = d.offer_id
339 AND d.qp_list_header_id = c.list_header_id
340 AND a.offer_discount_line_id = p_offer_discount_line_id
341 AND b.off_discount_product_id = p_off_discount_product_id);
342 --AND d.qp_list_header_id = c.list_header_id
343 --AND a.offer_id = d.offer_id);
344 l_dummy NUMBER;
345
346 BEGIN
347 x_return_status := FND_API.G_RET_STS_SUCCESS;
348 IF (p_adj_prod.offer_discount_line_id IS NOT NULL AND p_adj_prod.offer_discount_line_id <> fnd_api.g_miss_num)
349 AND
350 (p_adj_prod.off_discount_product_id IS NOT NULL AND p_adj_prod.off_discount_product_id <> FND_API.G_MISS_NUM )
351 THEN
352 OPEN c(P_ADJ_prod.offer_discount_line_id, p_adj_prod.off_discount_product_id);
353 FETCH c INTO l_dummy;
354 IF (C%NOTFOUND) THEN
355 CLOSE C;
356 OZF_Utility_PVT.Error_Message('OZF_ADJ_PROD_INV_DISC_PROD');
357 x_return_status := FND_API.g_ret_sts_error;
358 return;
359 END IF;
360 END IF;
361 -- a user can associate a product only to a pbh line
362 IF p_adj_prod.offer_discount_line_id IS NOT NULL AND p_adj_prod.offer_discount_line_id <> FND_API.G_MISS_NUM THEN
363 IF OZF_UTILITY_PVT.check_fk_exists('OZF_OFFER_DISCOUNT_LINES'
364 ,'OFFER_DISCOUNT_LINE_ID'
365 , to_char(p_adj_prod.offer_discount_line_id)
366 , OZF_UTILITY_PVT.g_number
367 , ' TIER_TYPE = ''PBH'''
368 ) = FND_API.G_FALSE THEN
369 OZF_Utility_PVT.Error_Message('OZF_INV_DISC_LINE_TYPE');
370 x_return_status := FND_API.g_ret_sts_error;
371 return;
372 END IF;
373 END IF;
374 END check_adj_prod_inter_attr;
375
376 PROCEDURE check_adj_prod_entity
377 (
378 p_adj_prod IN offer_adj_prod_rec
379 , p_validation_mode IN VARCHAR2
380 , x_return_status OUT NOCOPY VARCHAR2
381 )
382 IS
383 CURSOR c_effectiveDate(cp_offerAdjustmentId NUMBER) IS
384 SELECT effective_date
385 FROM ozf_offer_adjustments_b
386 WHERE offer_adjustment_id = cp_offerAdjustmentId;
387 l_effectiveDate DATE;
388 BEGIN
389 x_return_status := FND_API.G_RET_STS_SUCCESS;
390 OPEN c_effectiveDate(cp_offerAdjustmentId => p_adj_prod.offer_adjustment_id) ;
391 FETCH c_effectiveDate INTO l_effectiveDate;
392 IF c_effectiveDate%NOTFOUND THEN
393 l_effectiveDate := sysdate + 1;
394 END IF;
395 CLOSE c_effectiveDate;
396 OZF_Offer_Adj_Line_PVT.debug_message('Checking backdated');
397 IF l_effectiveDate > SYSDATE THEN
398 OZF_Offer_Adj_Line_PVT.debug_message('Not backdated');
399 null;
400 ELSE
401 OZF_Offer_Adj_Line_PVT.debug_message('backdated');
402 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_VO_ADJ_BACKDATE_NO_PROD');
403 x_return_status := FND_API.G_RET_STS_ERROR;
404 return;
405 END IF;
406 END check_adj_prod_entity;
407
408 PROCEDURE check_adj_products
409 (
410 p_adj_prod IN offer_adj_prod_rec
411 ,p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE
412 , x_return_status OUT NOCOPY VARCHAR2
413 )
414 IS
415 BEGIN
416 x_return_status := FND_API.G_RET_STS_SUCCESS;
417 check_adj_prod_req_items
418 (
419 p_adj_prod => p_adj_prod
420 , p_validation_mode => p_validation_mode
421 , x_return_status => x_return_status
422 );
423 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
424 RAISE FND_API.G_EXC_ERROR;
425 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
427 END IF;
428 check_adj_prod_attr
429 (
430 p_adj_prod => p_adj_prod
431 , p_validation_mode => p_validation_mode
432 , x_return_status => x_return_status
433 );
434 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
435 RAISE FND_API.G_EXC_ERROR;
436 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
438 END IF;
439 check_adj_prod_uk_items
440 (
441 p_adj_prod => p_adj_prod
442 , p_validation_mode => p_validation_mode
443 , x_return_status => x_return_status
444 );
445 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
446 RAISE FND_API.G_EXC_ERROR;
447 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
448 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
449 END IF;
450
451 check_adj_prod_fk_items
452 (
453 p_adj_prod => p_adj_prod
454 , p_validation_mode => p_validation_mode
455 , x_return_status => x_return_status
456 );
457 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
458 RAISE FND_API.G_EXC_ERROR;
459 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
460 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
461 END IF;
462 check_adj_prod_lkup_items
463 (
464 p_adj_prod => p_adj_prod
465 , p_validation_mode => p_validation_mode
466 , x_return_status => x_return_status
467 );
468 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
469 RAISE FND_API.G_EXC_ERROR;
470 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
471 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
472 END IF;
473 check_adj_prod_inter_attr
474 (
475 p_adj_prod => p_adj_prod
476 , p_validation_mode => p_validation_mode
477 , x_return_status => x_return_status
478 );
479 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
480 RAISE FND_API.G_EXC_ERROR;
481 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
482 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
483 END IF;
484 check_adj_prod_entity
485 (
486 p_adj_prod => p_adj_prod
487 , p_validation_mode => p_validation_mode
488 , x_return_status => x_return_status
489 );
490 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
491 RAISE FND_API.G_EXC_ERROR;
492 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
493 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
494 END IF;
495 END check_adj_products;
496
497 PROCEDURE VALIDATE_ADJ_PRODUCTS
498 (
499 p_api_version_number IN NUMBER,
500 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
501 p_commit IN VARCHAR2 := FND_API.G_FALSE,
502 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
503 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.G_CREATE,
504 x_return_status OUT NOCOPY VARCHAR2,
505 x_msg_count OUT NOCOPY NUMBER,
506 x_msg_data OUT NOCOPY VARCHAR2,
507 p_adj_prod IN offer_adj_prod_rec
508 )
509 IS
510 l_api_name CONSTANT VARCHAR2(30) := 'VALIDATE_ADJ_PRODUCTS';
511 BEGIN
512 x_return_status := FND_API.G_RET_STS_SUCCESS;
513 OZF_Offer_Adj_Line_PVT.debug_message('Private API: '|| l_api_name || ' Start');
514 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
515 check_adj_products(
516 p_adj_prod => p_adj_prod
517 , p_validation_mode => p_validation_mode
518 , x_return_status => x_return_status
519 );
520 END IF;
521 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
522 RAISE FND_API.G_EXC_ERROR;
523 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
524 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
525 END IF;
526 OZF_Offer_Adj_Line_PVT.debug_message('Private API: '|| l_api_name || ' End');
527
528 END VALIDATE_ADJ_PRODUCTS;
529
530 PROCEDURE CREATE_OFFER_ADJ_PRODUCT
531 (
532 p_api_version_number IN NUMBER,
533 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
534 p_commit IN VARCHAR2 := FND_API.G_FALSE,
535 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
536
537 x_return_status OUT NOCOPY VARCHAR2,
538 x_msg_count OUT NOCOPY NUMBER,
539 x_msg_data OUT NOCOPY VARCHAR2,
540 p_adj_prod IN offer_adj_prod_rec,
541 px_offer_adjustment_product_id OUT NOCOPY NUMBER
542 )
543 IS
544 l_api_name constant VARCHAR2(30) := 'CREATE_OFFER_ADJ_PRODUCT';
545 l_api_version_number CONSTANT NUMBER := 1.0;
546
547 l_adj_prod offer_adj_prod_rec;
548
549 l_object_version_number NUMBER;
550 CURSOR C_ID IS
551 SELECT ozf_offer_adj_products_s.nextval from dual;
552 l_offer_adjustment_product_id NUMBER;
553
554 CURSOR c_id_exists(l_offer_adjustment_product_id NUMBER)
555 IS
556 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 'X' FROM ozf_offer_adjustment_products where offer_adjustment_product_id = l_offer_adjustment_product_id);
557 l_dummy NUMBER;
558
559 l_excluder_flag VARCHAR2(1) := 'N';
560 BEGIN
561
562 SAVEPOINT CREATE_OFFER_ADJ_PRODUCT;
563
564 IF not fnd_api.compatible_api_call
565 (
566 p_api_version_number
567 , l_api_version_number
568 , G_PKG_NAME
569 , l_api_name
570 )
571 THEN
572 RAISE FND_API.g_exc_unexpected_error;
573 END IF;
574
575
576 IF fnd_api.to_boolean(p_init_msg_list) THEN
577 FND_MSG_PUB.INITIALIZE;
578 END IF;
579
580 OZF_Offer_Adj_Line_PVT.debug_message('Private API: '|| l_api_name || ' Start');
581 x_return_status := FND_API.G_RET_STS_SUCCESS;
582
583
584 IF FND_GLOBAL.USER_ID IS NULL
585 THEN
586 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
587 RAISE FND_API.G_EXC_ERROR;
588 END IF;
589
590 l_adj_prod := p_adj_prod;
591
592 IF p_adj_prod.offer_adjustment_product_id IS NULL OR p_adj_prod.offer_adjustment_product_id = FND_API.G_MISS_NUM THEN
593 LOOP
594 l_dummy := NULL;
595 OPEN c_id;
596 FETCH C_ID INTO l_offer_adjustment_product_id ;
597 CLOSE c_id;
598
599 OPEN c_id_exists(l_offer_adjustment_product_id);
600 FETCH c_id_exists INTO l_dummy;
601 CLOSE c_id_exists;
602
603 EXIT WHEN l_dummy IS NULL;
604 end loop;
605 ELSE
606 l_offer_adjustment_product_id := p_adj_prod.offer_adjustment_product_id;
607 END IF;
608
609 VALIDATE_ADJ_PRODUCTS
610 (
611 p_api_version_number => p_api_version_number
612 , p_init_msg_list => p_init_msg_list
613 , p_commit => p_commit
614 , p_validation_level => p_validation_level
615 , p_validation_mode => jtf_plsql_api.g_create
616 , x_return_status => x_return_status
617 , x_msg_count => x_msg_count
618 , x_msg_data => x_msg_data
619 , p_adj_prod => p_adj_prod
620 );
621
622 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
623 RAISE FND_API.G_EXC_ERROR;
624 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
625 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
626 END IF;
627 IF l_adj_prod.apply_discount_flag = 'N' AND l_adj_prod.include_volume_flag = 'N' THEN
628 l_excluder_flag := 'Y';
629 END IF;
630 OZF_OFFER_ADJ_PRODUCTS_PKG.INSERT_ROW
631 (
632 px_offer_adjustment_product_id => l_offer_adjustment_product_id
633 , p_offer_adjustment_id => p_adj_prod.offer_adjustment_id
634 , p_offer_discount_line_id => p_adj_prod.offer_discount_line_id
635 , p_off_discount_product_id => p_adj_prod.off_discount_product_id
636 , p_product_context => p_adj_prod.product_context
637 , p_product_attribute => p_adj_prod.product_attribute
638 , p_product_attr_value => p_adj_prod.product_attr_value
639 , p_excluder_flag => l_excluder_flag
640 , p_apply_discount_flag => p_adj_prod.apply_discount_flag
641 , p_include_volume_flag => p_adj_prod.include_volume_flag
642 , px_object_version_number => l_object_version_number
643 , p_creation_date => SYSDATE
644 , p_created_by => FND_GLOBAL.USER_ID
645 , p_last_updated_by => FND_GLOBAL.USER_ID
646 , p_last_update_date => SYSDATE
647 , p_last_update_login => FND_GLOBAL.conc_login_id
648 );
649 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
650 RAISE FND_API.G_EXC_ERROR;
651 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
652 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
653 END IF;
654
655 OZF_Offer_Adj_Line_PVT.debug_message('Private API '|| l_api_name || ' End');
656
657 IF FND_API.to_boolean(p_commit) THEN
658 COMMIT WORK;
659 END IF;
660
661 EXCEPTION
662 WHEN FND_API.G_EXC_ERROR THEN
663 ROLLBACK TO CREATE_OFFER_ADJ_PRODUCT;
664 x_return_status := FND_API.G_RET_STS_ERROR;
665 FND_MSG_PUB.COUNT_AND_GET
666 (
667 p_encoded => FND_API.G_FALSE
668 , p_count => x_msg_count
669 , p_data => x_msg_data
670 );
671
672 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
673 ROLLBACK TO CREATE_OFFER_ADJ_PRODUCT;
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
675 FND_MSG_PUB.COUNT_AND_GET
676 (
677 p_encoded => FND_API.G_FALSE
678 , p_count => x_msg_count
679 , p_data => x_msg_data
680 );
681
682 WHEN OTHERS THEN
683 ROLLBACK TO CREATE_OFFER_ADJ_PRODUCT;
684 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
685 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
686 THEN
687 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
688 END IF;
689 -- Standard call to get message count and if count=1, get the message
690 FND_MSG_PUB.Count_And_Get (
691 p_encoded => FND_API.G_FALSE,
692 p_count => x_msg_count,
693 p_data => x_msg_data
694 );
695 END CREATE_OFFER_ADJ_PRODUCT;
696
697
698 PROCEDURE UPDATE_OFFER_ADJ_PRODUCT
699 (
700 p_api_version_number IN NUMBER,
701 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
702 p_commit IN VARCHAR2 := FND_API.G_FALSE,
703 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
704
705 x_return_status OUT NOCOPY VARCHAR2,
706 x_msg_count OUT NOCOPY NUMBER,
707 x_msg_data OUT NOCOPY VARCHAR2,
708 p_adj_prod_rec IN offer_adj_prod_rec
709
710 )
711 IS
712 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_OFFER_ADJ_PRODUCT';
713 l_api_version_number CONSTANT NUMBER := 1.0;
714 l_tar_rec offer_adj_prod_rec := p_adj_prod_rec;
715 CURSOR c_get_adj_prod(p_adj_product_id NUMBER , p_object_version_number NUMBER)
716 IS
717 SELECT * FROM ozf_offer_adjustment_products
718 WHERE offer_adjustment_product_id = p_adj_product_id
719 AND object_version_number = p_object_version_number;
720 l_ref_rec c_get_adj_prod%rowtype;
721
722 l_excluder_flag VARCHAR2(1) := 'N';
723 BEGIN
724 SAVEPOINT UPDATE_OFFER_ADJ_PRODUCT;
725 IF not fnd_api.compatible_api_call
726 (
727 l_api_version_number
728 , p_api_version_number
729 , l_api_name
730 , g_pkg_name
731 )
732 THEN
733 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
734 END IF;
735
736 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
737 FND_MSG_PUB.INITIALIZE;
738 END IF;
739
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741
742 OZF_Offer_Adj_Line_PVT.debug_message('Private API: '|| l_api_name || ' Start');
743 OPEN c_get_adj_prod(l_tar_rec.offer_adjustment_product_id , l_tar_rec.object_version_number);
744 FETCH c_get_adj_prod INTO l_ref_rec;
745 IF (c_get_adj_prod%NOTFOUND) THEN
746 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
747 , p_token_name => 'INFO'
748 , p_token_value => 'OZF_OFFR_ADJ_PRODUCTS') ;
749 RAISE FND_API.G_EXC_ERROR;
750 END IF;
751 CLOSE c_get_adj_prod;
752
753 IF l_tar_rec.object_version_number IS NULL OR l_tar_rec.object_version_number = FND_API.G_MISS_NUM THEN
754 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
755 , p_token_name => 'COLUMN'
756 , p_token_value => 'object_version_number') ;
757 RAISE FND_API.G_EXC_ERROR;
758 END IF;
759
760 IF l_tar_rec.object_version_number <> l_ref_rec.object_version_number THEN
761 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
762 , p_token_name => 'INFO'
763 , p_token_value => 'Ozf_Market_Options') ;
764 RAISE FND_API.G_EXC_ERROR;
765 END IF;
766
767 VALIDATE_ADJ_PRODUCTS
768 (
769 p_api_version_number => p_api_version_number
770 , p_init_msg_list => p_init_msg_list
771 , p_commit => p_commit
772 , p_validation_level => p_validation_level
773 , p_validation_mode => jtf_plsql_api.g_update
774 , x_return_status => x_return_status
775 , x_msg_count => x_msg_count
776 , x_msg_data => x_msg_data
777 , p_adj_prod => l_tar_rec
778 );
779 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
780 RAISE FND_API.G_EXC_ERROR;
781 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
782 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
783 END IF;
784 IF l_tar_rec.apply_discount_flag = 'N' AND l_tar_rec.include_volume_flag = 'N' THEN
785 l_excluder_flag :='Y';
786 END IF;
787 OZF_Offer_Adj_Line_PVT.debug_message('eXCLUDER FLAG IS :'||l_excluder_flag||':123');
788 OZF_OFFER_ADJ_PRODUCTS_PKG.UPDATE_ROW
789 (
790 p_offer_adjustment_product_id => l_tar_rec.offer_adjustment_product_id
791 , p_offer_adjustment_id => l_tar_rec.offer_adjustment_id
792 , p_offer_discount_line_id => l_tar_rec.offer_discount_line_id
793 , p_off_discount_product_id => l_tar_rec.off_discount_product_id
794 , p_product_context => l_tar_rec.product_context
795 , p_product_attribute => l_tar_rec.product_attribute
796 , p_product_attr_value => l_tar_rec.product_attr_value
797 , p_excluder_flag => l_excluder_flag
798 , p_apply_discount_flag => l_tar_rec.apply_discount_flag
799 , p_include_volume_flag => l_tar_rec.include_volume_flag
800 , p_object_version_number => l_tar_rec.object_version_number
801 , p_last_update_date => sysdate
802 , p_last_updated_by => FND_GLOBAL.USER_ID
803 , p_last_update_login => FND_GLOBAL.conc_login_id
804 );
805 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
806 RAISE FND_API.G_EXC_ERROR;
807 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
809 END IF;
810 OZF_Offer_Adj_Line_PVT.debug_message('Private API: '|| l_api_name || ' End');
811 IF FND_API.TO_BOOLEAN(p_commit) THEN
812 COMMIT WORK;
813 END IF;
814
815 EXCEPTION
816 WHEN FND_API.G_EXC_ERROR THEN
817 ROLLBACK TO UPDATE_OFFER_ADJ_PRODUCT;
818 x_return_status := FND_API.G_RET_STS_ERROR;
819 FND_MSG_PUB.count_and_get(
820 p_encoded => FND_API.g_false
821 , p_count => x_msg_count
822 , p_data => x_msg_data
823 );
824 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
825 ROLLBACK TO UPDATE_OFFER_ADJ_PRODUCT;
826 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
827 FND_MSG_PUB.count_and_get(
828 p_encoded => FND_API.g_false
829 , p_count => x_msg_count
830 , p_data => x_msg_data
831 );
832 WHEN OTHERS THEN
833 ROLLBACK TO UPDATE_OFFER_ADJ_PRODUCT;
834 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
835 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
836 THEN
837 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
838 END IF;
839 FND_MSG_PUB.count_and_get(
840 p_encoded => FND_API.g_false
841 , p_count => x_msg_count
842 , p_data => x_msg_data
843 );
844 END UPDATE_OFFER_ADJ_PRODUCT;
845
846 PROCEDURE DELETE_OFFER_ADJ_PRODUCT
847 (
848 p_api_version_number IN NUMBER,
849 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
850 p_commit IN VARCHAR2 := FND_API.G_FALSE,
851 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
852
853 x_return_status OUT NOCOPY VARCHAR2,
854 x_msg_count OUT NOCOPY NUMBER,
855 x_msg_data OUT NOCOPY VARCHAR2,
856
857 p_offer_adjustment_product_id IN NUMBER,
858 p_object_version_number IN NUMBER
859 )
860 IS
861 l_api_name CONSTANT VARCHAR2(30) := 'DELETE_OFFER_ADJ_PRODUCT';
862 l_api_version_number CONSTANT NUMBER := 1.0;
863
864 BEGIN
865 SAVEPOINT DELETE_OFFER_ADJ_PRODUCT;
866 IF NOT FND_API.COMPATIBLE_API_CALL
867 (
868 l_api_version_number
869 , p_api_version_number
870 , l_api_name
871 , g_pkg_name
872 )
873 THEN
874 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
875 END IF;
876
877 IF FND_API.TO_BOOLEAN(P_INIT_MSG_LIST) THEN
878 FND_msg_pub.INITIALIZE;
879 END IF;
880
881 x_return_status := FND_API.G_RET_STS_SUCCESS;
882 OZF_Offer_Adj_Line_PVT.debug_message('Private API:'|| l_api_name ||' Start');
883
884 OZF_OFFER_ADJ_PRODUCTS_PKG.DELETE_ROW
885 (
886 p_offer_adjustment_product_id => p_offer_adjustment_product_id
887 , p_object_version_number => p_object_version_number
888 );
889 OZF_Offer_Adj_Line_PVT.debug_message('Private API:'|| l_api_name || ' End');
890 IF FND_API.TO_BOOLEAN(P_COMMIT) THEN
891 COMMIT WORK;
892 END IF;
893
894 EXCEPTION
895 WHEN FND_API.G_EXC_ERROR THEN
896 ROLLBACK TO DELETE_OFFER_ADJ_PRODUCT;
897 x_return_status := FND_API.G_RET_STS_ERROR;
898 FND_MSG_PUB.count_and_get(
899 p_encoded => FND_API.g_false
900 , p_count => x_msg_count
901 , p_data => x_msg_data
902 );
903 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
904 ROLLBACK TO DELETE_OFFER_ADJ_PRODUCT;
905 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
906 FND_MSG_PUB.count_and_get(
907 p_encoded => FND_API.g_false
908 , p_count => x_msg_count
909 , p_data => x_msg_data
910 );
911 WHEN OTHERS THEN
912 ROLLBACK TO DELETE_OFFER_ADJ_PRODUCT;
913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
914 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
915 THEN
916 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
917 END IF;
918 FND_MSG_PUB.count_and_get(
919 p_encoded => FND_API.g_false
920 , p_count => x_msg_count
921 , p_data => x_msg_data
922 );
923
924
925 END DELETE_OFFER_ADJ_PRODUCT;
926
927 END OZF_OFFER_ADJ_PRODUCTS_PVT;