[Home] [Help]
PACKAGE BODY: APPS.OZF_VOLUME_OFFER_DISC_PVT
Source
1 PACKAGE BODY OZF_Volume_Offer_disc_PVT AS
2 /* $Header: ozfvvodb.pls 120.23 2006/08/14 09:59:55 gramanat noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Volume_Offer_disc_PVT';
5 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvodlb.pls';
6
7 -- ==============================================================================
8 -- Start of Comments
9 -- ==============================================================================
10 -- API Name
11 -- check_vo_discounts_Uk_Items
12 -- Type
13 -- Private
14 -- Pre-Req
15 -- Parameters
16 --
17 -- IN
18 -- p_vo_disc_rec IN vo_disc_rec_type,
19 -- p_validation_mode IN VARCHAR2,
20 --
21 -- OUT NOCOPY
22 -- x_return_status OUT NOCOPY VARCHAR2,
23 -- Version : Current version 1.0
24 --
25 -- History
26 -- Fri May 06 2005:6/32 PM RSSHARMA Created
27 -- Mon Jun 20 2005:2/19 PM RSSHARMA Added new procedure copy_vo_discounts
28 -- Wed Aug 24 2005:1/39 AM RSSHARMA Made all inout and out params nocopy
29 -- added new procedure check_XXX_attr. ALso added return at the end of every error
30 -- Description
31 -- Wed Sep 28 2005:6/4 PM RSSHARMA Put % validation ie. if disocunttype = % then discount cannot be > 100
32 -- only if the discount is a static value. Incase the discount is derived using a formula the validation is
33 -- not fired
34 -- Thu Sep 29 2005:12/48 PM RSSHARMA Added Buplication check. Dont allow duplicate products in the offer.
35 -- the duplication check is based on product level, product id and apply_discount_flag
36 -- Thu Sep 29 2005:2/34 PM RSSHARMA Added no parent validation to discount lines and products.
37 -- in create mode the parent_discount_line_id (to discount lines) and offer_discount_line_id (to discount products)
38 -- is passed in as -1. This surpassed the required field validations but fails in FK validations with cryptic messages.
39 -- catch this situation before hand.
40 -- Sat Oct 01 2005:6/18 PM Corrected copy_vo_discounts to chech for existance of tier before copying.
41 -- added function get_discount_line_exists to check if a pbh line exists.
42 -- Tue Oct 11 2005:2/47 PM RSSHARMA Add debug messages only if debug level is high
43 -- added tier level validations for overlapping and discontinuous tiers
44 -- Thu Dec 29 2005:4:00 PM GRAMANAT Changed QP_CATEGORY_MAPPING_RULE.Validate_UOM to use QP_Validate.Product_Uom
45 -- Thu Feb 23 2006:12/41 PM RSSHARMA Fixed big # 5024225. In check_vo_product_Uk_Items, product_attr_value
46 -- was compared to g_miss_num, which gived charracter to number conversion error.
47 -- Also for the timebeing commented out discountinuous_tiers_exist call, till the function is corrected
48 -- Wed Mar 29 2006:6/3 PM RSSHARMA Return product id properly
49 -- Thu Apr 06 2006:12/12 PM RSSHARMA Fixed bug # 5142859. Added Null currency validation. If the Currency is null then
50 -- the discount type cannot be anything other than percent.
51 -- Mon May 22 2006:5/26 PM RSSHARMA Fixed bug # 5213655. For attributes which are enterable from the UI, retrieve the ak promots to display in required field error messages.
52 -- End of Comments
53 -- ==============================================================================
54
55
56 OZF_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
57 OZF_DEBUG_LOW_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
58 OZF_DEBUG_MEDIUM_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
59
60
61 PROCEDURE debug_message(p_message IN VARCHAR2)
62 IS
63 BEGIN
64 IF (OZF_DEBUG_HIGH_ON) THEN
65 ozf_utility_pvt.debug_message(p_message);
66 END IF;
67 END debug_message;
68
69
70 FUNCTION get_discount_line_exists
71 ( p_offerDiscountLineId IN NUMBER)
72 RETURN VARCHAR2
73 IS
74 CURSOR c_discountLineExists(cp_offerDiscountLineId NUMBER) is
75 SELECT 'Y' from dual where exists (SELECT 'X' FROM ozf_offer_discount_lines WHERE offer_discount_line_id = cp_offerDiscountLineId AND tier_type = 'PBH');
76 l_discountLineExists VARCHAR2(1) := 'Y';
77 BEGIN
78 OPEN c_discountLineExists(p_offerDiscountLineId );
79 FETCH c_discountLineExists INTO l_discountLineExists;
80 IF c_discountLineExists%NOTFOUND THEN
81 l_discountLineExists := 'N';
82 END IF;
83 return l_discountLineExists;
84 CLOSE c_discountLineExists;
85 END get_discount_line_exists ;
86
87
88 PROCEDURE check_vo_discounts_Uk_Items(
89 p_vo_disc_rec IN vo_disc_rec_type,
90 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
91 x_return_status OUT NOCOPY VARCHAR2)
92 IS
93 l_valid_flag VARCHAR2(1);
94 CURSOR c_name(p_name IN VARCHAR2 , p_offer_id IN NUMBER)
95 IS
96 SELECT 1 FROM DUAL WHERE EXISTS
97 (
98 SELECT 1 FROM OZF_OFFR_DISC_STRUCT_NAME_TL tl, ozf_offr_disc_struct_name_b b, ozf_offer_discount_lines l
99 WHERE b.offr_disc_struct_name_id = tl.offr_disc_struct_name_id
100 AND tl.LANGUAGE = userenv('LANG')
101 AND l.offer_discount_line_id = b.offer_discount_line_id
102 AND tl.discount_table_name = p_name
103 AND l.offer_id = p_offer_id
104 );
105 l_name c_name%rowtype;
106 BEGIN
107
108 x_return_status := FND_API.g_ret_sts_success;
109
110 IF p_validation_mode = JTF_PLSQL_API.g_create
111 AND p_vo_disc_rec.offer_discount_line_id IS NOT NULL
112 THEN
113 IF OZF_Utility_PVT.check_uniqueness('ozf_offer_discount_lines','offer_discount_line_id = ''' || p_vo_disc_rec.offer_discount_line_id ||'''') = FND_API.g_false THEN
114 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_DISC_LINE_ID_DUP');
115 x_return_status := FND_API.g_ret_sts_error;
116 END IF;
117 END IF;
118
119 IF p_validation_mode = JTF_PLSQL_API.g_create
120 AND p_vo_disc_rec.offr_disc_struct_name_id IS NOT NULL
121 THEN
122 IF OZF_Utility_PVT.check_uniqueness('ozf_offr_disc_struct_name_b','OFFR_DISC_STRUCT_NAME_ID = ''' || p_vo_disc_rec.offr_disc_struct_name_id ||'''') = FND_API.g_false THEN
123 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_DISC_NAME_ID_DUP');
124 x_return_status := FND_API.g_ret_sts_error;
125 END IF;
126 END IF;
127
128 IF p_validation_mode = JTF_PLSQL_API.g_create
129 AND p_vo_disc_rec.name IS NOT NULL
130 THEN
131 OPEN c_name(p_vo_disc_rec.name, p_vo_disc_rec.offer_id);
132 FETCH c_name INTO l_name;
133 If ( c_name%NOTFOUND) THEN
134 NULL;
135 ELSE
136 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_DISC_NAME_DUP');
137 x_return_status := FND_API.g_ret_sts_error;
138 END IF;
139 END IF;
140
141
142
143 END check_vo_discounts_Uk_Items;
144
145
146
147 -- ==============================================================================
148 -- Start of Comments
149 -- ==============================================================================
150 -- API Name
151 -- check_PBH_req_items
152 -- Type
153 -- Private
154 -- Pre-Req
155 -- Parameters
156 --
157 -- IN
158 -- p_vo_disc_rec IN vo_disc_rec_type,
159 -- p_validation_mode IN VARCHAR2,
160 --
161 -- OUT NOCOPY
162 -- x_return_status OUT NOCOPY VARCHAR2,
163 -- Version : Current version 1.0
164 --
165 -- History
166 -- Fri May 06 2005:6/32 PM RSSHARMA Created
167 --
168 -- Description
169 -- End of Comments
170 -- ==============================================================================
171
172 PROCEDURE check_PBH_req_items(
173 p_vo_disc_rec IN vo_disc_rec_type,
174 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
175 x_return_status OUT NOCOPY VARCHAR2
176 )
177 IS
178 l_api_name CONSTANT VARCHAR2(30) := 'check_PBH_req_items';
179 CURSOR c_volume_type(p_offer_discount_line_id NUMBER)
180 IS
181 SELECT volume_type FROM ozf_offer_discount_lines
182 WHERE offer_discount_line_id = p_offer_discount_line_id;
183 l_volume_type OZF_OFFER_DISCOUNT_LINES.volume_type%type;
184 BEGIN
185 --initialize
186 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
187
188 x_return_status := FND_API.g_ret_sts_success;
189 -- volume type required
190 IF p_vo_disc_rec.volume_type = FND_API.G_MISS_CHAR OR p_vo_disc_rec.volume_type IS NULL THEN
191 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_TIERS_BY') );
192 x_return_status := FND_API.g_ret_sts_error;
193 END IF;
194 -- volume_break_type required
195 IF p_vo_disc_rec.volume_break_type = FND_API.G_MISS_CHAR OR p_vo_disc_rec.volume_break_type IS NULL THEN
196 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'VOLUME_BREAK_TYPE' );
197 x_return_status := FND_API.g_ret_sts_error;
198 END IF;
199 -- discount type required
200 IF p_vo_disc_rec.discount_type = FND_API.G_MISS_CHAR OR p_vo_disc_rec.discount_type IS NULL THEN
201 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_DISCOUNT_BY') );
202 x_return_status := FND_API.g_ret_sts_error;
203 END IF;
204
205
206 IF p_vo_disc_rec.name = FND_API.G_MISS_CHAR OR p_vo_disc_rec.name IS NULL THEN
207 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_OFFER_DISC_TBL_NAME') );
208 x_return_status := FND_API.g_ret_sts_error;
209 END IF;
210
211 -- get volume_type for the discount line
212 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
213 l_volume_type := p_vo_disc_rec.volume_type;
214 ELSIF p_validation_mode = JTF_PLSQL_API.g_update then
215 IF p_vo_disc_rec.volume_type IS NULL OR p_vo_disc_rec.volume_type = FND_API.G_MISS_CHAR THEN
216 OPEN c_volume_type(p_vo_disc_rec.offer_discount_line_id);
217 FETCH c_volume_type INTO l_volume_type ;
218 CLOSE c_volume_type;
219 ELSE
220 l_volume_type := p_vo_disc_rec.volume_type;
221 END IF;
222 ELSE
223 OZF_Offer_Adj_Line_PVT.debug_message('INVALID VALIDATION MODE');
224 x_return_status := FND_API.g_ret_sts_error;
225 END IF;
226
227 -- if volume_type = quantity (PRICING_ATTRIBUTE10) UOM CODE is required
228 IF l_volume_type = 'PRICING_ATTRIBUTE10' THEN
229 IF p_vo_disc_rec.uom_code = FND_API.G_MISS_CHAR OR p_vo_disc_rec.uom_code IS NULL THEN
230 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_UOM') );
231 x_return_status := FND_API.g_ret_sts_error;
232 END IF;
233 END IF;
234
235 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
236
237 END check_pbh_req_items;
238
239
240 -- ==============================================================================
241 -- Start of Comments
242 -- ==============================================================================
243 -- API Name
244 -- check_dis_req_items
245 -- Type
246 -- Private
247 -- Pre-Req
248 -- Parameters
249 --
250 -- IN
251 -- p_vo_disc_rec IN vo_disc_rec_type,
252 -- p_validation_mode IN VARCHAR2,
253 --
254 -- OUT NOCOPY
255 -- x_return_status OUT NOCOPY VARCHAR2,
256 -- Version : Current version 1.0
257 --
258 -- History
259 -- Fri May 06 2005:6/32 PM RSSHARMA Created
260 --
261 -- Description
262 -- End of Comments
263 -- ==============================================================================
264
265 PROCEDURE check_dis_req_items(
266 p_vo_disc_rec IN vo_disc_rec_type,
267 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
268 x_return_status OUT NOCOPY VARCHAR2
269 )
270 IS
271 l_api_name CONSTANT VARCHAR2(30) := 'check_dis_req_items';
272 l_discStr VARCHAR2(240);
273 BEGIN
274 --initialize
275 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
276 x_return_status := FND_API.g_ret_sts_success;
277 l_discStr := OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_DISCOUNT' );
278 l_discStr := l_discStr || ' ' || OZF_UTILITY_PVT.getAttributeName(p_attributeCode =>'IEC_ALG_OR' , p_applicationId => 545) ;
279 l_discStr := l_discStr || ' '|| OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_FORMULA');
280
281 IF p_validation_mode = jtf_plsql_api.g_create THEN
282 --parent discount line id is required
283 IF p_vo_disc_rec.parent_discount_line_id = FND_API.G_MISS_NUM OR p_vo_disc_rec.parent_discount_line_id IS NULL THEN
284 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'PARENT_DISCOUNT_LINE_ID' );
285 x_return_status := FND_API.g_ret_sts_error;
286 return;
287 END IF;
288 -- volume_from is required
289 IF p_vo_disc_rec.VOLUME_FROM = FND_API.G_MISS_NUM OR p_vo_disc_rec.VOLUME_FROM IS NULL THEN
290 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode =>'OZF_FROM') );
291 x_return_status := FND_API.g_ret_sts_error;
292 return;
293 END IF;
294 -- volume_to is required
295 IF p_vo_disc_rec.volume_to = FND_API.G_MISS_NUM OR p_vo_disc_rec.volume_to IS NULL THEN
296 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode =>'OZF_SCREEN_TO') );
297 x_return_status := FND_API.g_ret_sts_error;
298 return;
299 END IF;
300 --volume_operator is required
301 IF p_vo_disc_rec.volume_operator = FND_API.G_MISS_CHAR OR p_vo_disc_rec.volume_operator IS NULL THEN
302 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'VOLUME_OPERATOR' );
303 x_return_status := FND_API.g_ret_sts_error;
304 RETURN;
305 END IF;
306 -- formula_id or discount are required. Both cannot be null
307 IF (p_vo_disc_rec.discount = FND_API.G_MISS_NUM OR p_vo_disc_rec.discount IS NULL)
308 AND
309 (p_vo_disc_rec.formula_id = FND_API.G_MISS_NUM OR p_vo_disc_rec.formula_id IS NULL)
310 THEN
311 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD'
312 , l_discStr
313 );
314 x_return_status := FND_API.g_ret_sts_error;
315 return;
316 END IF;
317 ELSE
318 IF p_vo_disc_rec.offer_discount_line_id = FND_API.G_MISS_NUM THEN
319 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','OFFER_DISCOUNT_LINE_ID');
320 x_return_status := FND_API.g_ret_sts_error;
321 return;
322 END IF;
323 IF p_vo_disc_rec.object_version_number = FND_API.G_MISS_NUM THEN
324 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','object_version_number');
325 x_return_status := FND_API.g_ret_sts_error;
326 return;
327 END IF;
328
329 IF p_vo_disc_rec.parent_discount_line_id = FND_API.G_MISS_NUM THEN
330 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'PARENT_DISCOUNT_LINE_ID' );
331 x_return_status := FND_API.g_ret_sts_error;
332 return;
333 END IF;
334 -- volume_from is required
335 IF p_vo_disc_rec.VOLUME_FROM = FND_API.G_MISS_NUM THEN
336 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode =>'OZF_FROM') );
337 x_return_status := FND_API.g_ret_sts_error;
338 return;
339 END IF;
340 -- volume_to is required
341 IF p_vo_disc_rec.volume_to = FND_API.G_MISS_NUM THEN
342 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode =>'OZF_SCREEN_TO') );
343 x_return_status := FND_API.g_ret_sts_error;
344 return;
345 END IF;
346 --volume_operator is required
347 IF p_vo_disc_rec.volume_operator = FND_API.G_MISS_CHAR THEN
348 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'VOLUME_OPERATOR' );
349 x_return_status := FND_API.g_ret_sts_error;
350 RETURN;
351 END IF;
352 -- formula_id or discount are required. Both cannot be null
353 IF (p_vo_disc_rec.discount = FND_API.G_MISS_NUM )
354 AND
355 (p_vo_disc_rec.formula_id = FND_API.G_MISS_NUM )
356 THEN
357 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD'
358 , l_discStr
359 );
360 x_return_status := FND_API.g_ret_sts_error;
361 return;
362 END IF;
363
364 END IF;
365
366 IF p_vo_disc_rec.offer_discount_line_id = -1 THEN
367 OZF_Utility_PVT.Error_Message('OZF_DIS_LINE_NO_PARENT' );
368 x_return_status := FND_API.g_ret_sts_error;
369 return;
370 END IF;
371
372 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
373
374 END check_dis_req_items;
375
376
377
378 -- ==============================================================================
379 -- Start of Comments
380 -- ==============================================================================
381 -- API Name
382 -- check_vo_req_items
383 -- Type
384 -- Private
385 -- Pre-Req
386 -- Parameters
387 --
388 -- IN
389 -- p_vo_disc_rec IN vo_disc_rec_type,
390 -- p_validation_mode IN VARCHAR2,
391 --
392 -- OUT NOCOPY
393 -- x_return_status OUT NOCOPY VARCHAR2,
394 -- Version : Current version 1.0
395 --
396 -- History
397 -- Fri May 06 2005:6/32 PM RSSHARMA Created
398 --
399 -- Description
400 -- End of Comments
401 -- ==============================================================================
402 PROCEDURE check_vo_req_items(
403 p_vo_disc_rec IN vo_disc_rec_type,
404 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
405 x_return_status OUT NOCOPY VARCHAR2
406 )
407 IS
408 CURSOR c_offer_type(p_offer_id NUMBER) IS
409 SELECT offer_type FROM ozf_offers
410 WHERE offer_id = p_offer_id;
411
412 CURSOR c_tier_type(p_offer_discount_line_id number) IS
413 SELECT tier_type FROM ozf_offer_discount_lines
414 WHERE offer_discount_line_id = p_offer_discount_line_id;
415
416 l_offer_type OZF_OFFERS.offer_type%type;
417 l_tier_type OZF_OFFER_DISCOUNT_LINES.tier_type%type;
418
419 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_req_items';
420
421 BEGIN
422 --initialize
423 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
424
425 x_return_status := FND_API.g_ret_sts_success;
426
427 -- get offer type
428 OPEN c_offer_type(p_vo_disc_rec.offer_id);
429 FETCH c_offer_type INTO l_offer_type ;
430 CLOSE c_offer_type;
431 OZF_Offer_Adj_Line_PVT.debug_message('Offer Type is ' || l_offer_type || 'Offer Id is :'|| p_vo_disc_rec.offer_id);
432 -- if offer_type is not volume_offer return error message
433 IF l_offer_type = 'VOLUME_OFFER' THEN
434 -- get tier_type for the line either from db or from record itself
435 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
436 l_tier_type := p_vo_disc_rec.tier_type;
437 ELSIF p_validation_mode = JTF_PLSQL_API.g_update then
438 IF p_vo_disc_rec.tier_type IS NULL OR p_vo_disc_rec.tier_type = FND_API.G_MISS_CHAR THEN
439 OPEN c_tier_type(p_vo_disc_rec.offer_discount_line_id);
440 FETCH c_tier_type INTO l_tier_type ;
441 CLOSE c_tier_type;
442 ELSE
443 l_tier_type := p_vo_disc_rec.tier_type;
444 END IF;
445 ELSE
446 OZF_Offer_Adj_Line_PVT.debug_message('INVALID VALIDATION MODE');
447 x_return_status := FND_API.g_ret_sts_error;
448 END IF;
449 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
450 RAISE FND_API.G_EXC_ERROR;
451 END IF;
452
453 IF l_tier_type = 'PBH' THEN
454 check_PBH_req_items(
455 p_vo_disc_rec => p_vo_disc_rec
456 , p_validation_mode => p_validation_mode
457 , x_return_status => x_return_status
458 );
459 ELSIF l_tier_type = 'DIS' THEN
460 check_dis_req_items(
461 p_vo_disc_rec => p_vo_disc_rec
462 , p_validation_mode => p_validation_mode
463 , x_return_status => x_return_status
464 );
465 ELSE
466 x_return_status := FND_API.g_ret_sts_error;
467 -- populate error message for invalid tier type
468 END IF;
469
470 ELSE
471 OZF_Offer_Adj_Line_PVT.debug_message('Offer Type is ' || l_offer_type || 'Offer Id is :'|| p_vo_disc_rec.offer_id);
472 x_return_status := FND_API.g_ret_sts_error;
473 -- populate error message for invalid offer type
474 END IF;
475
476 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
477 RAISE FND_API.G_EXC_ERROR;
478 END IF;
479
480 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
481
482
483 END check_vo_req_items;
484
485 -- ==============================================================================
486 -- Start of Comments
487 -- ==============================================================================
488 -- API Name
489 -- check_vo_discounts_req_items
490 -- Type
491 -- Private
492 -- Pre-Req
493 -- Parameters
494 --
495 -- IN
496 -- p_vo_disc_rec IN vo_disc_rec_type,
497 -- p_validation_mode IN VARCHAR2,
498 --
499 -- OUT NOCOPY
500 -- x_return_status OUT NOCOPY VARCHAR2,
501 -- Version : Current version 1.0
502 --
503 -- History
504 -- Fri May 06 2005:6/32 PM RSSHARMA Created
505 --
506 -- Description
507 -- End of Comments
508 -- ==============================================================================
509 PROCEDURE check_vo_discounts_req_items(
510 p_vo_disc_rec IN vo_disc_rec_type,
511 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
512 x_return_status OUT NOCOPY VARCHAR2
513 )
514 IS
515 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_discounts_req_items';
516 BEGIN
517 --initialize
518 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
519
520 x_return_status := FND_API.g_ret_sts_success;
521 -- check required items
522 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
523 /*
524 IF p_vo_disc_rec.offer_discount_line_id = FND_API.G_MISS_NUM OR p_vo_disc_rec.offer_discount_line_id IS NULL THEN
525 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_DISCOUNT_LINE_ID' );
526 x_return_status := FND_API.g_ret_sts_error;
527 return;
528 END IF;
529 */
530 IF p_vo_disc_rec.offer_id = FND_API.G_MISS_NUM OR p_vo_disc_rec.offer_id IS NULL THEN
531 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ID' );
532 x_return_status := FND_API.g_ret_sts_error;
533 return;
534 END IF;
535
536
537
538 ELSE
539
540 IF p_vo_disc_rec.offer_discount_line_id = FND_API.G_MISS_NUM THEN
541 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_DISCOUNT_LINE_ID' );
542 x_return_status := FND_API.g_ret_sts_error;
543 return;
544 END IF;
545
546 IF p_vo_disc_rec.offer_id = FND_API.G_MISS_NUM THEN
547 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ID' );
548 x_return_status := FND_API.g_ret_sts_error;
549 return;
550 END IF;
551
552
553 END IF;
554
555 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
556 RAISE FND_API.G_EXC_ERROR;
557 END IF;
558
559 check_vo_req_items(
560 p_vo_disc_rec => p_vo_disc_rec
561 , p_validation_mode => p_validation_mode
562 , x_return_status => x_return_status
563 );
564 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
565 RAISE FND_API.G_EXC_ERROR;
566 END IF;
567
568 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
569
570
571 END check_vo_discounts_req_items;
572
573 -- ==============================================================================
574 -- Start of Comments
575 -- ==============================================================================
576 -- API Name
577 -- check_vo_discounts_FK_items
578 -- Type
579 -- Private
580 -- Pre-Req
581 -- Parameters
582 --
583 -- IN
584 -- p_vo_disc_rec IN vo_disc_rec_type,
585 -- p_validation_mode IN VARCHAR2,
586 --
587 -- OUT NOCOPY
588 -- x_return_status OUT NOCOPY VARCHAR2,
589 -- Version : Current version 1.0
590 --
591 -- History
592 -- Fri May 06 2005:6/32 PM RSSHARMA Created
593 --
594 -- Description
595 -- End of Comments
596 -- ==============================================================================
597 PROCEDURE check_vo_discounts_FK_items(
598 p_vo_disc_rec IN vo_disc_rec_type,
599 x_return_status OUT NOCOPY VARCHAR2
600 )
601 IS
602 BEGIN
603
604 x_return_status := FND_API.g_ret_sts_success;
605 -- Enter custom code here
606
607 IF p_vo_disc_rec.offer_id IS NOT NULL AND p_vo_disc_rec.offer_id <> FND_API.G_MISS_NUM
608 THEN
609 IF ozf_utility_pvt.check_fk_exists('OZF_OFFERS','OFFER_ID',to_char(p_vo_disc_rec.offer_id)) = FND_API.g_false THEN
610 OZF_Utility_PVT.Error_Message('OZF_INVALID_OFFER_ID' );
611 x_return_status := FND_API.g_ret_sts_error;
612 return;
613 END IF;
614 END IF;
615
616 IF p_vo_disc_rec.parent_discount_line_id IS NOT NULL AND p_vo_disc_rec.PARENT_DISCOUNT_LINE_ID <> FND_API.G_MISS_NUM
617 THEN
618 OZF_Offer_Adj_Line_PVT.debug_message('Parent Id is :'||p_vo_disc_rec.parent_discount_line_id);
619 IF ozf_utility_pvt.check_fk_exists('OZF_OFFER_DISCOUNT_LINES','OFFER_DISCOUNT_LINE_ID',to_char(p_vo_disc_rec.parent_discount_line_id)) = FND_API.g_false THEN
620 OZF_Utility_PVT.Error_Message('OZF_INVALID_PARENT_ID' );
621 x_return_status := FND_API.g_ret_sts_error;
622 return;
623 END IF;
624 END IF;
625
626 IF p_vo_disc_rec.formula_id IS NOT NULL AND p_vo_disc_rec.formula_id <> FND_API.G_MISS_NUM
627 THEN
628 IF ozf_utility_pvt.check_fk_exists('QP_PRICE_FORMULAS_B','PRICE_FORMULA_ID',to_char(p_vo_disc_rec.formula_id)) = FND_API.g_false THEN
629 OZF_Utility_PVT.Error_Message('OZF_INVALID_FORMULA' );
630 x_return_status := FND_API.g_ret_sts_error;
631 return;
632 END IF;
633 END IF;
634
635 IF p_vo_disc_rec.uom_code IS NOT NULL AND p_vo_disc_rec.uom_code <> FND_API.G_MISS_CHAR
636 THEN
637 IF ozf_utility_pvt.check_fk_exists('MTL_UNITS_OF_MEASURE','UOM_CODE',to_char(p_vo_disc_rec.uom_code)) = FND_API.g_false THEN
638 OZF_Utility_PVT.Error_Message('OZF_INVALID_UOM' );
639 x_return_status := FND_API.g_ret_sts_error;
640 return;
641 END IF;
642 END IF;
643
644
645
646 null;
647 END check_vo_discounts_FK_items;
648
649
650 -- ==============================================================================
651 -- Start of Comments
652 -- ==============================================================================
653 -- API Name
654 -- check_vo_discounts_Lkup_Items
655 -- Type
656 -- Private
657 -- Pre-Req
658 -- Parameters
659 --
660 -- IN
661 -- p_vo_disc_rec IN vo_disc_rec_type,
662 -- p_validation_mode IN VARCHAR2,
663 --
664 -- OUT NOCOPY
665 -- x_return_status OUT NOCOPY VARCHAR2,
666 -- Version : Current version 1.0
667 --
668 -- History
669 -- Fri May 06 2005:6/32 PM RSSHARMA Created
670 --
671 -- Description
672 -- End of Comments
673 -- ==============================================================================
674 PROCEDURE check_vo_discounts_Lkup_Items(
675 p_vo_disc_rec IN vo_disc_rec_type,
676 x_return_status OUT NOCOPY VARCHAR2
677 )
678 IS
679 BEGIN
680 x_return_status := FND_API.g_ret_sts_success;
681
682 IF p_vo_disc_rec.volume_type IS NOT NULL AND p_vo_disc_rec.volume_type <> FND_API.G_MISS_CHAR
683 THEN
684 IF OZF_UTILITY_PVT.check_lookup_exists('OZF_LOOKUPS', 'OZF_QP_VOLUME_TYPE', p_vo_disc_rec.volume_type) = FND_API.g_false THEN
685 OZF_Utility_PVT.Error_Message('OZF_INVALID_VOLUME_TYPE' );
686 x_return_status := FND_API.g_ret_sts_error;
687 return;
688 END IF;
689 END IF;
690
691 IF p_vo_disc_rec.discount_type IS NOT NULL AND p_vo_disc_rec.discount_type <> FND_API.G_MISS_CHAR
692 THEN
693 IF OZF_UTILITY_PVT.check_lookup_exists('QP_LOOKUPS', 'ARITHMETIC_OPERATOR', p_vo_disc_rec.discount_type) = FND_API.g_false THEN
694 OZF_Utility_PVT.Error_Message('OZF_INVALID_DISCOUNT_TYPE' );
695 x_return_status := FND_API.g_ret_sts_error;
696 return;
697 END IF;
698 END IF;
699
700
701 IF p_vo_disc_rec.volume_operator IS NOT NULL AND p_vo_disc_rec.volume_operator <> FND_API.G_MISS_CHAR
702 THEN
703 IF OZF_UTILITY_PVT.check_lookup_exists('QP_LOOKUPS', 'COMPARISON_OPERATOR', p_vo_disc_rec.volume_operator) = FND_API.g_false THEN
704 OZF_Utility_PVT.Error_Message('OZF_INVALID_OPERATOR' );
705 x_return_status := FND_API.g_ret_sts_error;
706 return;
707 END IF;
708 END IF;
709
710 IF p_vo_disc_rec.volume_break_type IS NOT NULL AND p_vo_disc_rec.volume_break_type <> FND_API.G_MISS_CHAR
711 THEN
712 IF OZF_UTILITY_PVT.check_lookup_exists('QP_LOOKUPS', 'PRICE_BREAK_TYPE_CODE', p_vo_disc_rec.volume_break_type) = FND_API.g_false THEN
713 OZF_Utility_PVT.Error_Message('OZF_INVALID_BREAK_TYPE' );
714 x_return_status := FND_API.g_ret_sts_error;
715 return;
716 END IF;
717 END IF;
718
719 IF p_vo_disc_rec.tier_type IS NOT NULL AND p_vo_disc_rec.tier_type <> FND_API.G_MISS_CHAR
720 THEN
721 IF OZF_UTILITY_PVT.check_lookup_exists('OZF_LOOKUPS', 'OZF_OFFER_TIER_TYPE', p_vo_disc_rec.tier_type) = FND_API.g_false THEN
722 OZF_Utility_PVT.Error_Message('OZF_INVALID_TIER_TYPE' );
723 x_return_status := FND_API.g_ret_sts_error;
724 return;
725 END IF;
726 END IF;
727
728 -- Enter custom code here
729 END check_vo_discounts_Lkup_Items;
730
731
732 -- ==============================================================================
733 -- Start of Comments
734 -- ==============================================================================
735 -- API Name
736 -- check_vo_discounts_attr
737 -- Type
738 -- Private
739 -- Pre-Req
740 -- Parameters
741 --
742 -- IN
743 -- p_vo_disc_rec IN vo_disc_rec_type,
744 --
745 -- OUT NOCOPY
746 -- x_return_status OUT NOCOPY VARCHAR2,
747 -- Version : Current version 1.0
748 --
749 -- History
750 -- Fri May 06 2005:6/32 PM RSSHARMA Created
751 --
752 -- Description
753 -- End of Comments
754 -- ==============================================================================
755
756 PROCEDURE check_vo_discounts_attr(
757 p_vo_disc_rec IN vo_disc_rec_type,
758 x_return_status OUT NOCOPY VARCHAR2
759 )
760 IS
761 BEGIN
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763 IF p_vo_disc_rec.volume_from IS NOT NULL AND p_vo_disc_rec.volume_from <> FND_API.G_MISS_NUM THEN
764 IF p_vo_disc_rec.volume_from < 0 THEN
765 OZF_Utility_PVT.Error_Message('OZF_NEGATIVE_QTY' );
766 x_return_status := FND_API.g_ret_sts_error;
767 return;
768 END IF;
769 END IF;
770 IF p_vo_disc_rec.volume_to IS NOT NULL AND p_vo_disc_rec.volume_to <> FND_API.G_MISS_NUM THEN
771 IF p_vo_disc_rec.volume_to < 0 THEN
772 OZF_Utility_PVT.Error_Message('OZF_NEGATIVE_QTY' );
773 x_return_status := FND_API.g_ret_sts_error;
774 return;
775 END IF;
776 END IF;
777 IF p_vo_disc_rec.parent_discount_line_id = -1 THEN
778 OZF_Utility_PVT.Error_Message('OZF_DIS_LINE_NO_PARENT' );
779 x_return_status := FND_API.g_ret_sts_error;
780 return;
781 END IF;
782 END check_vo_discounts_attr;
783
784
785
786 -- ==============================================================================
787 -- Start of Comments
788 -- ==============================================================================
789 -- API Name
790 -- check_vo_discounts_inter_attr
791 -- Type
792 -- Private
793 -- Pre-Req
794 -- Parameters
795 --
796 -- IN
797 -- p_vo_disc_rec IN vo_disc_rec_type,
798 --
799 -- OUT NOCOPY
800 -- x_return_status OUT NOCOPY VARCHAR2,
801 -- Version : Current version 1.0
802 --
803 -- History
804 -- Fri May 06 2005:6/32 PM RSSHARMA Created
805 --
806 -- Description
807 -- End of Comments
808 -- ==============================================================================
809
810 PROCEDURE check_vo_discounts_inter_attr(
811 p_vo_disc_rec IN vo_disc_rec_type,
812 x_return_status OUT NOCOPY VARCHAR2
813 )
814 IS
815 BEGIN
816 x_return_status := FND_API.G_RET_STS_SUCCESS;
817 IF (p_vo_disc_rec.volume_from IS NOT NULL AND p_vo_disc_rec.volume_from <> FND_API.G_MISS_NUM )
818 AND
819 (p_vo_disc_rec.volume_to IS NOT NULL AND p_vo_disc_rec.volume_to <> FND_API.G_MISS_NUM )
820 THEN
821 IF p_vo_disc_rec.volume_to < p_vo_disc_rec.volume_from THEN
822 OZF_Utility_PVT.Error_Message('OZF_FROM_GT_TO' );
823 x_return_status := FND_API.g_ret_sts_error;
824 return;
825 END IF;
826 END IF;
827 END check_vo_discounts_inter_attr;
828
829
830
831 FUNCTION overlapping_tiers_exist
832 (
833 p_volumeFrom NUMBER
834 , p_volumeTo NUMBER
835 , p_offerId NUMBER
836 , p_parentDiscountLineId NUMBER
837 , p_offerDiscountLineId NUMBER
838 )
839 RETURN VARCHAR2
840 IS
841 CURSOR c_getOverlapTiers(cp_volumeFrom NUMBER
842 ,cp_volumeTo NUMBER
843 , cp_offerId NUMBER
844 , cp_parentDiscountLineId NUMBER
845 , cp_offerDiscountLineId NUMBER
846 ) IS
847 SELECT 1 FROM dual WHERE EXISTS(SELECT 'X'
848 FROM ozf_offer_discount_lines
849 WHERE tier_type = 'DIS'
850 AND (
851 ( cp_volumeFrom BETWEEN volume_from AND volume_to)
852 OR
853 (cp_volumeTo BETWEEN volume_from AND volume_to )
854 )
855 AND offer_id = cp_offerId
856 AND parent_discount_line_id = cp_parentDiscountLineId
857 AND offer_discount_line_id <> cp_offerDiscountLineId
858 );
859 l_getOverlapTiers NUMBER;
860 l_return VARCHAR2(10) := null;
861 BEGIN
862 l_return := null;
863 OZF_Offer_Adj_Line_PVT.debug_message('Volume From :'||p_volumeFrom || ' : volume to : '||p_volumeTo || ' OfferDiscountLineId :'||p_parentDiscountLineId || ' : OfferDiscountLIneId is : '||p_offerDiscountLineId|| 'OfferId :'||p_offerId);
864 OPEN c_getOverlapTiers(cp_volumeFrom => p_volumeFrom
865 , cp_volumeTo => p_volumeTo
866 , cp_offerId => p_offerId
867 , cp_parentDiscountLineId => p_parentDiscountLineId
868 , cp_offerDiscountLineId => p_offerDiscountLineId
869 );
870 FETCH c_getOverlapTiers INTO l_getOverlapTiers;
871 IF (c_getOverlapTiers%NOTFOUND) THEN
872 l_return := 'N';
873 ELSE
874 l_return := 'Y';
875 END IF;
876 CLOSE c_getOverlapTiers;
877 return l_return;
878 END overlapping_tiers_exist;
879
880 FUNCTION discontinuous_tiers_exist(p_volumeFrom NUMBER, p_volumeTo NUMBER, p_offerId NUMBER , p_offerDiscountLineId NUMBER)
881 RETURN VARCHAR2 IS
882 CURSOR c_getContinuousTiers(cp_volumeFrom NUMBER,cp_volumeTo NUMBER , cp_offerId NUMBER, cp_offerDiscountLineId NUMBER) IS
883 SELECT 1 FROM dual WHERE EXISTS(SELECT 'X'
884 FROM ozf_offer_discount_lines
885 WHERE tier_type = 'DIS'
886 AND (
887 (volume_to IN (cp_volumeFrom , cp_volumeFrom -1 ))
888 OR
889 (volume_from IN ( cp_volumeTo , cp_volumeTo + 1 ))
890 )
891 AND offer_id = cp_offerId
892 AND parent_discount_line_id = cp_offerDiscountLineId
893 );
894
895 CURSOR c_tiersExist( cp_parentDiscountLineId NUMBER )IS
896 SELECT 'Y'
897 FROM dual
898 WHERE EXISTS (SELECT 'X'
899 FROM ozf_offer_discount_lines
900 WHERE parent_discount_line_id = cp_parentDiscountLineId
901 );
902 l_tiersExist VARCHAR2(1) := null;
903 l_getContinuousTiers NUMBER;
904 l_return VARCHAR2(10) := null;
905 BEGIN
906 l_return := null;
907 l_tiersExist := null;
908 OPEN c_tiersExist(cp_parentDiscountLineId => p_offerDiscountLineId);
909 FETCH c_tiersExist INTO l_tiersExist;
910 IF (c_tiersExist%NOTFOUND) THEN
911 l_return := 'N';
912 ELSE
913 OPEN c_getContinuousTiers(cp_volumeFrom => p_volumeFrom , cp_volumeTo => p_volumeTo, cp_offerId => p_offerId , cp_offerDiscountLineId => p_offerDiscountLineId);
914 FETCH c_getContinuousTiers INTO l_getContinuousTiers;
915 IF ( c_getContinuousTiers%NOTFOUND )THEN
916 l_return := 'N';
917 ELSE
918 l_return := 'Y';
919 END IF;
920 CLOSE c_getContinuousTiers;
921 END IF;
922 CLOSE c_tiersExist;
923 return l_return;
924 END discontinuous_tiers_exist;
925
926
927 FUNCTION getOfferId
928 (
929 p_offerDiscountLineId IN NUMBER
930 )
931 RETURN NUMBER
932 IS
933 l_offerId NUMBER;
934 CURSOR c_offerId(cp_offerDiscountLineId NUMBER)
935 IS
936 SELECT offer_id
937 FROM ozf_offer_discount_lines
938 WHERE offer_discount_line_id = cp_offerDiscountLineId;
939 BEGIN
940 OPEN c_offerId(cp_offerDiscountLineId => p_offerDiscountLineId);
941 FETCH c_offerId INTO l_offerId;
942 IF c_offerId%NOTFOUND THEN
943 l_offerId := null;
944 END IF;
945 CLOSE c_offerId;
946 return l_offerId;
947 END getOfferId;
948
949
950 PROCEDURE validatePbhLines
951 (
952 x_return_status OUT NOCOPY VARCHAR2
953 , p_vo_disc_rec IN vo_disc_rec_type
954 )
955 IS
956 CURSOR c_currency(cp_offerId NUMBER)
957 IS
958 SELECT transaction_currency_code
959 FROM ozf_offers
960 WHERE offer_id = cp_offerId;
961 l_currency ozf_offers.transaction_currency_code%TYPE;
962 BEGIN
963 x_return_status := FND_API.G_RET_STS_SUCCESS;
964 OPEN c_currency(cp_offerId => nvl(p_vo_disc_rec.offer_id,getOfferId(p_offerDiscountLineId => p_vo_disc_rec.offer_discount_line_id)));
965 FETCH c_currency INTO l_currency;
966 CLOSE c_currency;
967 IF l_currency IS NULL THEN
968 IF (p_vo_disc_rec.tier_type <> FND_API.G_MISS_CHAR AND p_vo_disc_rec.tier_type IS NOT NULL)
969 THEN
970 IF
971 (p_vo_disc_rec.tier_type = 'PBH' AND p_vo_disc_rec.discount_type <> '%' )
972 THEN
973 OZF_Utility_PVT.error_message('OZF_OFFR_OPT_CURR_PCNT');
974 x_return_status := FND_API.G_RET_STS_ERROR;
975 RAISE FND_API.g_exc_error;
976 END IF;
977 END IF;
978 END IF;
979 END validatePbhLines;
980
981 -- ==============================================================================
982 -- Start of Comments
983 -- ==============================================================================
984 -- API Name
985 -- check_vo_discounts_inter_attr
986 -- Type
987 -- Private
988 -- Pre-Req
989 -- Parameters
990 --
991 -- IN
992 -- p_vo_disc_rec IN vo_disc_rec_type,
993 --
994 -- OUT NOCOPY
995 -- x_return_status OUT NOCOPY VARCHAR2,
996 -- Version : Current version 1.0
997 --
998 -- History
999 -- Fri May 06 2005:6/32 PM RSSHARMA Created
1000 --
1001 -- Description
1002 -- End of Comments
1003 -- ==============================================================================
1004
1005 PROCEDURE check_vo_discounts_entity(
1006 p_vo_disc_rec IN vo_disc_rec_type,
1007 x_return_status OUT NOCOPY VARCHAR2
1008 )
1009 IS
1010 l_discount_type OZF_OFFER_DISCOUNT_LINES.DISCOUNT_TYPE%TYPE;
1011 BEGIN
1012 x_return_status := FND_API.G_RET_STS_SUCCESS;
1013 OZF_Offer_Adj_Line_PVT.debug_message('uom is ' || p_vo_disc_rec.uom_code);
1014 IF p_vo_disc_rec.uom_code IS NOT NULL AND p_vo_disc_rec.uom_code <> fnd_api.g_miss_char AND p_vo_disc_rec.volume_type <> 'PRICING_ATTRIBUTE10' THEN
1015 OZF_Utility_PVT.Error_Message('OZF_VO_UOM_INV' );
1016 x_return_status := FND_API.g_ret_sts_error;
1017 RETURN;
1018 END IF;
1019
1020 IF p_vo_disc_rec.tier_type = 'DIS' AND (p_vo_disc_rec.parent_discount_line_id IS NOT NULL AND p_vo_disc_rec.parent_discount_line_id <> FND_API.G_MISS_NUM) THEN
1021 SELECT discount_type INTO l_discount_type FROM OZF_OFFER_DISCOUNT_LINES
1022 WHERE OFFER_DISCOUNT_LINE_ID = p_vo_disc_rec.parent_discount_line_id;
1023 OZF_Offer_Adj_Line_PVT.debug_message('Discount is :'||p_vo_disc_rec.discount);
1024 IF p_vo_disc_rec.discount IS NOT NULL AND p_vo_disc_rec.discount <> FND_API.G_MISS_NUM THEN
1025 IF l_discount_type ='%' AND p_vo_disc_rec.discount > 100 THEN
1026 OZF_Utility_PVT.Error_Message('OZF_PER_DISC_INV' );
1027 x_return_status := FND_API.g_ret_sts_error;
1028 return;
1029 END IF;
1030 END IF;
1031
1032 END IF;
1033 -- Validation for Volume From to be less than Volume to
1034 IF (p_vo_disc_rec.volume_from IS NOT NULL AND p_vo_disc_rec.volume_from <> FND_API.G_MISS_NUM )
1035 AND
1036 (p_vo_disc_rec.volume_to IS NOT NULL AND p_vo_disc_rec.volume_to <> FND_API.G_MISS_NUM )
1037 THEN
1038 IF p_vo_disc_rec.volume_to < p_vo_disc_rec.volume_from THEN
1039 OZF_Utility_PVT.Error_Message('OZF_FROM_GT_TO' );
1040 x_return_status := FND_API.g_ret_sts_error;
1041 return;
1042 END IF;
1043 END IF;
1044 /*
1045 IF (p_vo_disc_rec.volume_from IS NOT NULL AND p_vo_disc_rec.volume_from <> FND_API.G_MISS_NUM )
1046 AND
1047 (p_vo_disc_rec.volume_to IS NOT NULL AND p_vo_disc_rec.volume_to <> FND_API.G_MISS_NUM)
1048 THEN
1049 */
1050 -- Validation for non-overlapping tiers
1051 -- Removing the validation of 10 between 0 and 10 as it will always be true.
1052 /*
1053 IF overlapping_tiers_exist(
1054 p_vo_disc_rec.volume_from
1055 , p_vo_disc_rec.volume_to
1056 , p_vo_disc_rec.offer_id
1057 , p_vo_disc_rec.parent_discount_line_id
1058 , p_vo_disc_rec.offer_discount_line_id
1059 ) = 'Y' THEN
1060 OZF_Utility_PVT.Error_Message('OZF_VO_INVALID_TIERS' );
1061 x_return_status := FND_API.g_ret_sts_error;
1062 return;
1063 END IF;
1064 */
1065 -- validation for discountinuous tiers
1066 /* IF discontinuous_tiers_exist( p_vo_disc_rec.volume_from
1067 , p_vo_disc_rec.volume_to
1068 , p_vo_disc_rec.offer_id
1069 , p_vo_disc_rec.parent_discount_line_id
1070 ) = 'Y' THEN
1071 OZF_Utility_PVT.Error_Message('OZF_VO_DISCNT_TIERS' );
1072 x_return_status := FND_API.g_ret_sts_error;
1073 return;
1074 END IF;
1075 */
1076 /*
1077 END IF;
1078 */
1079 validatePbhLines
1080 (
1081 x_return_status => x_return_status
1082 , p_vo_disc_rec => p_vo_disc_rec
1083 );
1084 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1085 RAISE FND_API.G_EXC_ERROR;
1086 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1087 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1088 END IF;
1089 END check_vo_discounts_entity;
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101 -- ==============================================================================
1102 -- Start of Comments
1103 -- ==============================================================================
1104 -- API Name
1105 -- Check_vo_discount_Items
1106 -- Type
1107 -- Private
1108 -- Pre-Req
1109 -- check_vo_discounts_Uk_Items,check_vo_discounts_req_items,check_vo_discounts_FK_items,check_vo_discounts_Lkup_Items
1110 -- Parameters
1111 --
1112 -- IN
1113 -- p_vo_disc_rec IN vo_disc_rec_type,
1114 -- p_validation_mode IN VARCHAR2,
1115 --
1116 -- OUT NOCOPY
1117 -- x_return_status OUT NOCOPY VARCHAR2,
1118 -- Version : Current version 1.0
1119 --
1120 -- History
1121 -- Fri May 06 2005:6/32 PM RSSHARMA Created
1122 --
1123 -- Description
1124 -- End of Comments
1125 -- ==============================================================================
1126
1127 PROCEDURE Check_vo_discount_Items (
1128 p_vo_disc_rec IN vo_disc_rec_type,
1129 p_validation_mode IN VARCHAR2,
1130 x_return_status OUT NOCOPY VARCHAR2
1131 )
1132 IS
1133 l_api_name CONSTANT VARCHAR2(30) := 'Check_vo_discount_Items';
1134 BEGIN
1135 x_return_status := FND_API.G_RET_STS_SUCCESS;
1136 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
1137
1138 check_vo_discounts_req_items(
1139 p_vo_disc_rec => p_vo_disc_rec,
1140 p_validation_mode => p_validation_mode,
1141 x_return_status => x_return_status);
1142
1143 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1144 RAISE FND_API.G_EXC_ERROR;
1145 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1147 END IF;
1148
1149 check_vo_discounts_attr(
1150 p_vo_disc_rec => p_vo_disc_rec,
1151 x_return_status => x_return_status
1152 );
1153
1154 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1155 RAISE FND_API.G_EXC_ERROR;
1156 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1157 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1158 END IF;
1159
1160 -- Check Items Uniqueness API calls
1161 check_vo_discounts_Uk_Items(
1162 p_vo_disc_rec => p_vo_disc_rec,
1163 p_validation_mode => p_validation_mode,
1164 x_return_status => x_return_status);
1165
1166 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1167 RAISE FND_API.G_EXC_ERROR;
1168 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1169 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1170 END IF;
1171
1172
1173
1174 -- Check Items Foreign Keys API calls
1175
1176 check_vo_discounts_FK_items(
1177 p_vo_disc_rec => p_vo_disc_rec,
1178 x_return_status => x_return_status);
1179 -- Check Items Lookups
1180
1181 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1182 RAISE FND_API.G_EXC_ERROR;
1183 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1185 END IF;
1186
1187 check_vo_discounts_Lkup_Items(
1188 p_vo_disc_rec => p_vo_disc_rec,
1189 x_return_status => x_return_status);
1190
1191 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1192 RAISE FND_API.G_EXC_ERROR;
1193 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1195 END IF;
1196
1197
1198 check_vo_discounts_inter_attr(
1199 p_vo_disc_rec => p_vo_disc_rec,
1200 x_return_status => x_return_status
1201 );
1202 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1203 RAISE FND_API.G_EXC_ERROR;
1204 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1206 END IF;
1207
1208
1209 check_vo_discounts_entity(
1210 p_vo_disc_rec => p_vo_disc_rec,
1211 x_return_status => x_return_status
1212 );
1213 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1214 RAISE FND_API.G_EXC_ERROR;
1215 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1216 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1217 END IF;
1218
1219 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
1220
1221 END Check_vo_discount_Items;
1222
1223
1224
1225 PROCEDURE Complete_pbh_Rec (
1226 p_vo_disc_rec IN vo_disc_rec_type,
1227 x_complete_rec OUT NOCOPY vo_disc_rec_type)
1228 IS
1229 BEGIN
1230 OZF_Offer_Adj_Line_PVT.debug_message('Complete_pbh_Rec');
1231 x_complete_rec := p_vo_disc_rec;
1232 x_complete_rec.parent_discount_line_id := FND_API.G_MISS_NUM;
1233 x_complete_rec.volume_from := FND_API.G_MISS_NUM;
1234 x_complete_rec.volume_to := FND_API.G_MISS_NUM;
1235 x_complete_rec.volume_operator := FND_API.G_MISS_CHAR;
1236 x_complete_rec.discount := FND_API.G_MISS_NUM;
1237 x_complete_rec.incompatibility_group := FND_API.G_MISS_CHAR;
1238 x_complete_rec.precedence := FND_API.G_MISS_NUM;
1239 x_complete_rec.bucket := FND_API.G_MISS_CHAR;
1240 x_complete_rec.scan_value := FND_API.G_MISS_NUM;
1241 x_complete_rec.scan_data_quantity := FND_API.G_MISS_NUM;
1242 x_complete_rec.scan_unit_forecast := FND_API.G_MISS_NUM;
1243 x_complete_rec.channel_id := FND_API.G_MISS_NUM;
1244 x_complete_rec.discount_by_code := FND_API.G_MISS_CHAR;
1245 x_complete_rec.formula_id := FND_API.G_MISS_NUM;
1246 x_complete_rec.adjustment_flag := FND_API.G_MISS_CHAR;
1247 IF x_complete_rec.volume_type = 'PRICING_ATTRIBUTE12' THEN
1248 x_complete_rec.uom_code := FND_API.G_MISS_CHAR;
1249 END IF;
1250 END Complete_pbh_Rec;
1251
1252 PROCEDURE Complete_dis_Rec (
1253 p_vo_disc_rec IN vo_disc_rec_type,
1254 x_complete_rec OUT NOCOPY vo_disc_rec_type)
1255 IS
1256 BEGIN
1257 OZF_Offer_Adj_Line_PVT.debug_message('Complete_dis_Rec');
1258 x_complete_rec := p_vo_disc_rec;
1259 x_complete_rec.incompatibility_group := FND_API.G_MISS_CHAR;
1260 x_complete_rec.precedence := FND_API.G_MISS_NUM;
1261 x_complete_rec.bucket := FND_API.G_MISS_CHAR;
1262 x_complete_rec.scan_value := FND_API.G_MISS_NUM;
1263 x_complete_rec.scan_data_quantity := FND_API.G_MISS_NUM;
1264 x_complete_rec.scan_unit_forecast := FND_API.G_MISS_NUM;
1265 x_complete_rec.channel_id := FND_API.G_MISS_NUM;
1266 x_complete_rec.discount_by_code := FND_API.G_MISS_CHAR;
1267 x_complete_rec.adjustment_flag := FND_API.G_MISS_CHAR;
1268 x_complete_rec.volume_type := FND_API.G_MISS_CHAR;
1269 x_complete_rec.volume_break_type := FND_API.G_MISS_CHAR;
1270 x_complete_rec.discount_type := FND_API.G_MISS_CHAR;
1271 x_complete_rec.name := FND_API.G_MISS_CHAR;
1272 x_complete_rec.description := FND_API.G_MISS_CHAR;
1273
1274 END Complete_dis_Rec;
1275
1276 PROCEDURE Complete_vo_discount_Rec (
1277 p_vo_disc_rec IN vo_disc_rec_type,
1278 x_complete_rec OUT NOCOPY vo_disc_rec_type)
1279 IS
1280 BEGIN
1281 x_complete_rec := p_vo_disc_rec;
1282 IF p_vo_disc_rec.tier_type = 'PBH' THEN
1283 Complete_pbh_Rec (
1284 p_vo_disc_rec => p_vo_disc_rec,
1285 x_complete_rec => x_complete_rec);
1286 ELSE
1287 Complete_dis_Rec (
1288 p_vo_disc_rec => p_vo_disc_rec,
1289 x_complete_rec => x_complete_rec);
1290
1291 END IF;
1292 END Complete_vo_discount_Rec;
1293
1294
1295 -- ==============================================================================
1296 -- Start of Comments
1297 -- ==============================================================================
1298 -- API Name
1299 -- Validate_vo_discounts_rec
1300 -- Type
1301 -- Private
1302 -- Pre-Req
1303 -- Parameters
1304 --
1305 -- IN
1306 -- p_api_version_number IN NUMBER
1307 -- p_init_msg_list IN VARCHAR2
1308 -- p_vo_disc_rec IN vo_disc_rec_type
1309 --
1310 -- OUT NOCOPY
1311 -- x_return_status OUT NOCOPY VARCHAR2
1312 -- x_msg_count OUT NOCOPY NUMBER
1313 -- x_msg_data OUT NOCOPY VARCHAR2
1314 -- Version : Current version 1.0
1315 --
1316 -- History
1317 -- Fri May 06 2005:6/32 PM RSSHARMA Created
1318 --
1319 -- Description
1320 -- End of Comments
1321 -- ==============================================================================
1322
1323
1324 PROCEDURE Validate_vo_discounts_rec (
1325 p_api_version_number IN NUMBER,
1326 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1327 x_return_status OUT NOCOPY VARCHAR2,
1328 x_msg_count OUT NOCOPY NUMBER,
1329 x_msg_data OUT NOCOPY VARCHAR2,
1330 p_vo_disc_rec IN vo_disc_rec_type
1331 )
1332 IS
1333 BEGIN
1334 -- Initialize message list if p_init_msg_list is set to TRUE.
1335 IF FND_API.to_Boolean( p_init_msg_list )
1336 THEN
1337 FND_MSG_PUB.initialize;
1338 END IF;
1339
1340 -- Initialize API return status to SUCCESS
1341 x_return_status := FND_API.G_RET_STS_SUCCESS;
1342
1343 -- Hint: Validate data
1344 -- If data not valid
1345 -- THEN
1346 -- x_return_status := FND_API.G_RET_STS_ERROR;
1347
1348 -- Debug Message
1349 OZF_Offer_Adj_Line_PVT.debug_message('Private API: Validate_dm_model_rec');
1350 -- Standard call to get message count and if count is 1, get message info.
1351 FND_MSG_PUB.Count_And_Get
1352 (p_count => x_msg_count,
1353 p_data => x_msg_data
1354 );
1355 END Validate_vo_discounts_rec;
1356
1357 -- ==============================================================================
1358 -- Start of Comments
1359 -- ==============================================================================
1360 -- API Name
1361 -- Validate_vo_discounts
1362 -- Type
1363 -- Private
1364 -- Pre-Req
1365 -- Check_vo_discount_Items,check_vo_discounts_Uk_Items,check_vo_discounts_req_items,check_vo_discounts_FK_items,check_vo_discounts_Lkup_Items
1366 -- Parameters
1367 --
1368 -- IN
1369 -- p_api_version_number IN NUMBER,
1370 -- p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1371 -- p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1372 -- p_vo_disc_rec IN vo_disc_rec_type,
1373 -- p_validation_mode IN VARCHAR2,
1374 --
1375 -- OUT NOCOPY
1376 -- x_return_status OUT NOCOPY VARCHAR2,
1377 -- x_msg_count OUT NOCOPY NUMBER,
1378 -- x_msg_data OUT NOCOPY VARCHAR2
1379 -- Version : Current version 1.0
1380 --
1381 -- History
1382 -- Fri May 06 2005:6/32 PM RSSHARMA Created
1383 --
1384 -- Description
1385 -- : Helper method to validate discount line record
1386 -- End of Comments
1387 -- ==============================================================================
1388
1389 PROCEDURE Validate_vo_discounts(
1390 p_api_version_number IN NUMBER,
1391 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1392 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1393 px_vo_disc_rec IN OUT NOCOPY vo_disc_rec_type,
1394 p_validation_mode IN VARCHAR2,
1395 x_return_status OUT NOCOPY VARCHAR2,
1396 x_msg_count OUT NOCOPY NUMBER,
1397 x_msg_data OUT NOCOPY VARCHAR2
1398 )
1399 IS
1400 l_api_name CONSTANT VARCHAR2(30) := 'Validate_vo_discounts';
1401 l_api_version_number CONSTANT NUMBER := 1.0;
1402 l_object_version_number NUMBER;
1403 l_vo_disc_rec vo_disc_rec_type;
1404 BEGIN
1405 -- Standard Start of API savepoint
1406 SAVEPOINT Validate_vo_discounts_pvt;
1407
1408 -- Standard call to check for call compatibility.
1409 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1410 p_api_version_number,
1411 l_api_name,
1412 G_PKG_NAME)
1413 THEN
1414 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1415 END IF;
1416 -- Initialize message list if p_init_msg_list is set to TRUE.
1417 IF FND_API.to_Boolean( p_init_msg_list )
1418 THEN
1419 FND_MSG_PUB.initialize;
1420 END IF;
1421
1422 -- Debug Message
1423 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
1424
1425 -- Initialize API return status to SUCCESS
1426 x_return_status := FND_API.G_RET_STS_SUCCESS;
1427
1428 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
1429
1430 /*IF p_validation_mode <> JTF_PLSQL_API.g_create THEN
1431 Complete_vo_discount_Rec(
1432 p_vo_disc_rec => px_vo_disc_rec,
1433 x_complete_rec => px_vo_disc_rec
1434 );
1435 END IF;*/
1436 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1437 Check_vo_discount_Items(
1438 p_vo_disc_rec => px_vo_disc_rec,
1439 p_validation_mode => p_validation_mode,
1440 x_return_status => x_return_status
1441 );
1442
1443 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1444 RAISE FND_API.G_EXC_ERROR;
1445 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1446 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1447 END IF;
1448 END IF;
1449 -- IF p_validation_mode = JTF_PLSQL_API.g_update THEN
1450
1451 -- END IF;
1452 /* IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1453 Validate_vo_discounts_rec(
1454 p_api_version_number => 1.0,
1455 p_init_msg_list => FND_API.G_FALSE,
1456 x_return_status => x_return_status,
1457 x_msg_count => x_msg_count,
1458 x_msg_data => x_msg_data,
1459 p_vo_disc_rec => l_vo_disc_rec);
1460
1461 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1462 RAISE FND_API.G_EXC_ERROR;
1463 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1464 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1465 END IF;
1466 END IF;
1467 */
1468
1469 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
1470
1471 -- Debug Message
1472 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
1473
1474
1475 -- Standard call to get message count and if count is 1, get message info.
1476 FND_MSG_PUB.Count_And_Get
1477 (p_count => x_msg_count,
1478 p_data => x_msg_data
1479 );
1480 EXCEPTION
1481
1482 WHEN OZF_Utility_PVT.resource_locked THEN
1483 x_return_status := FND_API.g_ret_sts_error;
1484 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1485
1486 WHEN FND_API.G_EXC_ERROR THEN
1487 ROLLBACK TO Validate_vo_discounts_pvt;
1488 x_return_status := FND_API.G_RET_STS_ERROR;
1489 -- Standard call to get message count and if count=1, get the message
1490 FND_MSG_PUB.Count_And_Get (
1491 p_encoded => FND_API.G_FALSE,
1492 p_count => x_msg_count,
1493 p_data => x_msg_data
1494 );
1495
1496 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1497 ROLLBACK TO Validate_vo_discounts_pvt;
1498 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1499 -- Standard call to get message count and if count=1, get the message
1500 FND_MSG_PUB.Count_And_Get (
1501 p_encoded => FND_API.G_FALSE,
1502 p_count => x_msg_count,
1503 p_data => x_msg_data
1504 );
1505
1506 WHEN OTHERS THEN
1507 ROLLBACK TO Validate_vo_discounts_pvt;
1508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1509 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1510 THEN
1511 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1512 END IF;
1513 -- Standard call to get message count and if count=1, get the message
1514 FND_MSG_PUB.Count_And_Get (
1515 p_encoded => FND_API.G_FALSE,
1516 p_count => x_msg_count,
1517 p_data => x_msg_data
1518 );
1519
1520 End Validate_vo_discounts;
1521
1522
1523
1524
1525
1526
1527 -- ==============================================================================
1528 -- Start of Comments
1529 -- ==============================================================================
1530 -- API Name
1531 -- Delete_vo_discount
1532 -- Type
1533 -- Private
1534 -- Pre-Req
1535 -- OZF_Create_Ozf_Prod_Line_PKG.Delete_Product,OZF_DISC_LINE_PKG.Delete_Row
1536 -- Parameters
1537 --
1538 -- IN
1539 -- p_api_version_number IN NUMBER Required
1540 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1541 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1542 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1543 -- p_offer_discount_line_id IN NUMBER Required Discount Line id to be deleted
1544 -- p_object_version_number IN NUMBER Required Object Version No. Of Discount Line to be deleted
1545 --
1546 -- OUT NOCOPY
1547 -- x_return_status OUT NOCOPY VARCHAR2
1548 -- x_msg_count OUT NOCOPY NUMBER
1549 -- x_msg_data OUT NOCOPY VARCHAR2
1550 -- Version : Current version 1.0
1551 --
1552 -- History
1553 -- Wed Oct 01 2003:5/21 PM RSSHARMA Created
1554 --
1555 -- Description
1556 -- : Helper method to Hard Delete a Discount Line and all the Related Product Lines for a volume offer
1557 -- End of Comments
1558 -- ==============================================================================
1559
1560 PROCEDURE Delete_vo_discount(
1561 p_api_version_number IN NUMBER,
1562 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1563 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1564 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1565
1566 x_return_status OUT NOCOPY VARCHAR2,
1567 x_msg_count OUT NOCOPY NUMBER,
1568 x_msg_data OUT NOCOPY VARCHAR2,
1569
1570 p_offer_discount_line_id IN NUMBER,
1571 p_object_version_number IN NUMBER
1572 )
1573 IS
1574 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_vo_discount';
1575 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1576 l_object_version_number NUMBER;
1577 l_offr_disc_struct_name_id NUMBER;
1578 BEGIN
1579 -- Standard Start of API savepoint
1580 SAVEPOINT Delete_vo_discount_PVT;
1581
1582 -- Standard call to check for call compatibility.
1583 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1584 p_api_version_number,
1585 l_api_name,
1586 G_PKG_NAME)
1587 THEN
1588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1589 END IF;
1590
1591 -- Initialize message list if p_init_msg_list is set to TRUE.
1592 IF FND_API.to_Boolean( p_init_msg_list )
1593 THEN
1594 FND_MSG_PUB.initialize;
1595 END IF;
1596
1597 -- Initialize API return status to SUCCESS
1598 x_return_status := FND_API.G_RET_STS_SUCCESS;
1599
1600 --
1601 -- Api body
1602 --
1603
1604 -- Invoke table handler(OZF_Promotional_Offers_PKG.Delete_Row)
1605 BEGIN
1606 OZF_Create_Ozf_Prod_Line_PKG.Delete_Product(
1607 p_offer_discount_line_id => p_offer_discount_line_id
1608 );
1609 EXCEPTION
1610 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1611 null;
1612 END;
1613 OZF_Offer_Adj_Line_PVT.debug_message('Done calling Delete product');
1614
1615 BEGIN
1616 SELECT object_version_number, offr_disc_struct_name_id INTO l_object_version_number, l_offr_disc_struct_name_id
1617 FROM ozf_offr_disc_struct_name_b
1618 WHERE offer_discount_line_id = p_offer_discount_line_id;
1619
1620 OZF_VO_DISC_STRUCT_NAME_PKG.Delete_Row(
1621 p_offr_disc_struct_name_id => l_offr_disc_struct_name_id,
1622 p_object_version_number => l_object_version_number);
1623
1624 EXCEPTION
1625 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1626 null;
1627 END;
1628 OZF_Offer_Adj_Line_PVT.debug_message('Done calling Delete name');
1629
1630
1631 BEGIN
1632 OZF_DISC_LINE_PKG.delete_tiers(p_offer_discount_line_id => p_offer_discount_line_id);
1633 EXCEPTION
1634 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1635 null;
1636 END;
1637
1638 OZF_Offer_Adj_Line_PVT.debug_message('Done deleting children');
1639
1640 BEGIN
1641 OZF_DISC_LINE_PKG.Delete_Row(
1642 p_offer_discount_line_id => p_offer_discount_line_id,
1643 p_object_version_number => p_object_version_number
1644 );
1645 END;
1646 OZF_Offer_Adj_Line_PVT.debug_message('Done deleting');
1647
1648
1649 --
1650 -- End of API body
1651 --
1652
1653 -- Standard check for p_commit
1654 IF FND_API.to_Boolean( p_commit )
1655 THEN
1656 COMMIT WORK;
1657 END IF;
1658
1659 -- Standard call to get message count and if count is 1, get message info.
1660 FND_MSG_PUB.Count_And_Get
1661 (p_count => x_msg_count,
1662 p_data => x_msg_data
1663 );
1664 EXCEPTION
1665
1666 WHEN OZF_Utility_PVT.resource_locked THEN
1667 x_return_status := FND_API.g_ret_sts_error;
1668 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1669
1670 WHEN FND_API.G_EXC_ERROR THEN
1671 ROLLBACK TO Delete_vo_discount_PVT;
1672 x_return_status := FND_API.G_RET_STS_ERROR;
1673 -- Standard call to get message count and if count=1, get the message
1674 FND_MSG_PUB.Count_And_Get (
1675 p_encoded => FND_API.G_FALSE,
1676 p_count => x_msg_count,
1677 p_data => x_msg_data
1678 );
1679
1680 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1681 ROLLBACK TO Delete_vo_discount_PVT;
1682 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1683 -- Standard call to get message count and if count=1, get the message
1684 FND_MSG_PUB.Count_And_Get (
1685 p_encoded => FND_API.G_FALSE,
1686 p_count => x_msg_count,
1687 p_data => x_msg_data
1688 );
1689
1690 WHEN OTHERS THEN
1691 ROLLBACK TO Delete_vo_discount_PVT;
1692 OZF_Offer_Adj_Line_PVT.debug_message(SUBSTR(SQLERRM, 1, 100));
1693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1694 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1695 THEN
1696 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1697 END IF;
1698 -- Standard call to get message count and if count=1, get the message
1699 FND_MSG_PUB.Count_And_Get (
1700 p_encoded => FND_API.G_FALSE,
1701 p_count => x_msg_count,
1702 p_data => x_msg_data
1703 );
1704
1705 END Delete_vo_discount;
1706
1707
1708 PROCEDURE copy_vo_discounts
1709 (
1710 p_api_version_number IN NUMBER,
1711 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1712 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1713 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1714 x_return_status OUT NOCOPY VARCHAR2,
1715 x_msg_count OUT NOCOPY NUMBER,
1716 x_msg_data OUT NOCOPY VARCHAR2,
1717 p_discount_line_id IN NUMBER ,
1718 p_vo_disc_rec IN vo_disc_rec_type,
1719 x_vo_discount_line_id OUT NOCOPY NUMBER
1720 )
1721 IS
1722 CURSOR c_pbh_discount(p_offer_discount_line_id NUMBER) is
1723 SELECT a.offer_id
1724 , a.tier_type
1725 , a.volume_type
1726 , a.volume_break_type
1727 , a.discount_type
1728 , a.start_date_active
1729 , a.end_date_active
1730 , a.uom_code
1731 , tl.discount_table_name
1732 , tl.description
1733 , a.tier_level
1734 FROM OZF_OFFER_DISCOUNT_LINES a, ozf_offr_disc_struct_name_b b, ozf_offr_disc_struct_name_tl tl
1735 WHERE a.offer_discount_line_id = b.offer_discount_line_id
1736 AND b.offr_disc_struct_name_id = tl.offr_disc_struct_name_id
1737 AND tl.language = userenv('LANG')
1738 AND a.offer_discount_line_id = p_offer_discount_line_id;
1739
1740 l_pbh_discount c_pbh_discount%rowtype;
1741
1742 CURSOR c_dis_discount(p_offer_discount_line_id NUMBER) IS
1743 SELECT offer_id
1744 , tier_type
1745 , volume_from
1746 , volume_to
1747 , volume_operator
1748 , volume_break_type
1749 , discount
1750 , start_date_active
1751 , end_date_active
1752 , formula_id
1753 , tier_level
1754 FROM ozf_offer_discount_lines
1755 WHERE parent_discount_line_id = p_offer_discount_line_id;
1756
1757
1758
1759 l_api_name CONSTANT VARCHAR2(30) := 'copy_vo_discounts';
1760 l_api_version_number CONSTANT NUMBER := 1.0;
1761
1762 l_vo_dis_rec vo_disc_rec_type;
1763 l_vo_pbh_rec vo_disc_rec_type;
1764
1765
1766 l_vo_id NUMBER;
1767 l_vo_discount_line_id NUMBER;
1768
1769 CURSOR c_get_vo_disc_line(p_offer_discount_line_id NUMBER, p_object_version_number NUMBER) IS
1770 SELECT *
1771 FROM OZF_OFFER_DISCOUNT_LINES
1772 WHERE offer_discount_line_id = p_offer_discount_line_id
1773 AND object_version_number = p_object_version_number;
1774 -- Hint: Developer need to provide Where clause
1775 l_get_vo_disc_line c_get_vo_disc_line%ROWTYPE;
1776 -- Local Variables
1777 l_offer_discount_line_id NUMBER;
1778
1779 BEGIN
1780
1781 SAVEPOINT copy_vo_discounts_pvt;
1782
1783 -- Standard call to check for call compatibility.
1784 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1785 p_api_version_number,
1786 l_api_name,
1787 G_PKG_NAME)
1788 THEN
1789 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1790 END IF;
1791
1792 -- Initialize message list if p_init_msg_list is set to TRUE.
1793 IF FND_API.to_Boolean( p_init_msg_list )
1794 THEN
1795 FND_MSG_PUB.initialize;
1796 END IF;
1797
1798 -- Debug Message
1799 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
1800
1801 -- Initialize API return status to SUCCESS
1802 x_return_status := FND_API.G_RET_STS_SUCCESS;
1803
1804 -- =========================================================================
1805 -- Validate Environment
1806 -- =========================================================================
1807
1808 IF FND_GLOBAL.USER_ID IS NULL
1809 THEN
1810 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
1811 RAISE FND_API.G_EXC_ERROR;
1812 END IF;
1813 OZF_Offer_Adj_Line_PVT.debug_message('OfferDiscountLineId is : '||p_discount_line_id);
1814
1815
1816 OPEN c_get_vo_disc_line( p_vo_disc_rec.offer_discount_line_id,p_vo_disc_rec.object_version_number);
1817 FETCH c_get_vo_disc_line INTO l_get_vo_disc_line ;
1818 If ( c_get_vo_disc_line%NOTFOUND) THEN
1819 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
1820 , p_token_name => 'INFO'
1821 , p_token_value => 'DISCOUNT LINE') ;
1822 RAISE FND_API.G_EXC_ERROR;
1823 END IF;
1824 CLOSE c_get_vo_disc_line;
1825
1826 If (p_vo_disc_rec.object_version_number is NULL or
1827 p_vo_disc_rec.object_version_number = FND_API.G_MISS_NUM ) Then
1828 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
1829 , p_token_name => 'COLUMN'
1830 , p_token_value => 'Last_Update_Date') ;
1831 RAISE FND_API.G_EXC_ERROR;
1832 End if;
1833 -- Check Whether record has been changed by someone else
1834 If (p_vo_disc_rec.object_version_number <> l_get_vo_disc_line.object_version_number) Then
1835 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
1836 , p_token_name => 'INFO'
1837 , p_token_value => 'DISCOUNT LINE') ;
1838 RAISE FND_API.G_EXC_ERROR;
1839 End if;
1840
1841
1842 OPEN c_pbh_discount(p_discount_line_id) ;
1843 FETCH c_pbh_discount INTO l_pbh_discount ;
1844 IF c_pbh_discount%FOUND THEN
1845 OZF_Offer_Adj_Line_PVT.debug_message('offerId 2 is : '||l_pbh_discount.offer_id);
1846 CLOSE c_pbh_discount ;
1847
1848 l_vo_pbh_rec.offer_id := l_pbh_discount.offer_id;
1849 l_vo_pbh_rec.volume_type := l_pbh_discount.volume_type;
1850 l_vo_pbh_rec.volume_break_type := 'POINT';
1851
1852 l_vo_pbh_rec.discount_type := l_pbh_discount.discount_type;
1853 l_vo_pbh_rec.tier_type := 'PBH';
1854 l_vo_pbh_rec.tier_level := 'HEADER';
1855 l_vo_pbh_rec.uom_code := l_pbh_discount.uom_code;
1856 l_vo_pbh_rec.start_date_active := l_pbh_discount.start_date_active;
1857 l_vo_pbh_rec.end_date_active := l_pbh_discount.end_date_active;
1858 l_vo_pbh_rec.name := p_vo_disc_rec.name;
1859 --l_vo_pbh_rec.description := l_pbh_discount.description;
1860
1861 OZF_Offer_Adj_Line_PVT.debug_message('Offer id1 is :'|| l_vo_pbh_rec.offer_id);
1862
1863 Create_vo_discount(
1864 p_api_version_number => p_api_version_number
1865 , p_init_msg_list => p_init_msg_list
1866 , p_commit => p_commit
1867 , p_validation_level => p_validation_level
1868 , x_return_status => x_return_status
1869 , x_msg_count => x_msg_count
1870 , x_msg_data => x_msg_data
1871 , p_vo_disc_rec => l_vo_pbh_rec
1872 , x_vo_discount_line_id => x_vo_discount_line_id
1873 );
1874
1875 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1876 RAISE FND_API.G_EXC_ERROR;
1877 END IF;
1878
1879 OZF_Offer_Adj_Line_PVT.debug_message('discount line id1 is :'||p_discount_line_id);
1880 FOR l_dis_discount IN c_dis_discount(p_discount_line_id) LOOP
1881 l_vo_dis_rec.offer_id := l_dis_discount.offer_id;
1882 l_vo_dis_rec.parent_discount_line_id := x_vo_discount_line_id;
1883 l_vo_dis_rec.volume_from := l_dis_discount.volume_from;
1884 l_vo_dis_rec.volume_to := l_dis_discount.volume_to;
1885 l_vo_dis_rec.volume_operator := l_dis_discount.volume_operator;
1886 l_vo_dis_rec.discount := l_dis_discount.discount;
1887 l_vo_dis_rec.tier_type := 'DIS';
1888 l_vo_dis_rec.tier_level := 'HEADER';
1889 l_vo_dis_rec.formula_id := l_dis_discount.formula_id;
1890 Create_vo_discount(
1891 p_api_version_number => p_api_version_number
1892 , p_init_msg_list => p_init_msg_list
1893 , p_commit => p_commit
1894 , p_validation_level => p_validation_level
1895 , x_return_status => x_return_status
1896 , x_msg_count => x_msg_count
1897 , x_msg_data => x_msg_data
1898 , p_vo_disc_rec => l_vo_dis_rec
1899 , x_vo_discount_line_id => l_vo_id
1900 );
1901 END LOOP;
1902 ELSE
1903 OZF_Offer_Adj_Line_PVT.debug_message('PBH not found');
1904 CLOSE c_pbh_discount;
1905 END IF;
1906 OZF_Offer_Adj_Line_PVT.debug_message('New Discount line id is :'|| x_vo_discount_line_id);
1907
1908
1909 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
1910 RAISE FND_API.G_EXC_ERROR;
1911 END IF;
1912
1913
1914 EXCEPTION
1915 WHEN FND_API.G_EXC_ERROR THEN
1916 ROLLBACK TO copy_vo_discounts_pvt;
1917 x_return_status := FND_API.G_RET_STS_ERROR;
1918 -- Standard call to get message count and if count=1, get the message
1919 FND_MSG_PUB.Count_And_Get (
1920 p_encoded => FND_API.G_FALSE,
1921 p_count => x_msg_count,
1922 p_data => x_msg_data
1923 );
1924
1925 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1926 ROLLBACK TO copy_vo_discounts_pvt;
1927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1928 -- Standard call to get message count and if count=1, get the message
1929 FND_MSG_PUB.Count_And_Get (
1930 p_encoded => FND_API.G_FALSE,
1931 p_count => x_msg_count,
1932 p_data => x_msg_data
1933 );
1934
1935 WHEN OTHERS THEN
1936 ROLLBACK TO copy_vo_discounts_pvt;
1937 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1938 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1939 THEN
1940 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1941 END IF;
1942 -- Standard call to get message count and if count=1, get the message
1943 FND_MSG_PUB.Count_And_Get (
1944 p_encoded => FND_API.G_FALSE,
1945 p_count => x_msg_count,
1946 p_data => x_msg_data
1947 );
1948 END copy_vo_discounts;
1949 -- ==============================================================================
1950 -- Start of Comments
1951 -- ==============================================================================
1952 -- API Name
1953 -- Create_vo_discount
1954 -- Type
1955 -- Private
1956 -- Pre-Req
1957 -- Create_Ozf_Disc_Line,Create Product
1958 -- Parameters
1959 --
1960 -- IN
1961 -- p_api_version_number IN NUMBER Required
1962 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1963 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1964 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1965 -- p_vo_disc_rec IN vo_disc_rec_type Required Record containing Discount Line Data
1966 -- p_ozf_prod_rec IN ozf_prod_rec_type Required Record containing Product Data
1967 -- OUT NOCOPY
1968 -- x_return_status OUT NOCOPY VARCHAR2
1969 -- x_msg_count OUT NOCOPY NUMBER
1970 -- x_msg_data OUT NOCOPY VARCHAR2
1971 -- x_offer_discount_line_id OUT NOCOPY NUMBER. Discount Line Id of Discount Line Created
1972 -- Version : Current version 1.0
1973 --
1974 -- History
1975 -- Wed Oct 01 2003:5/21 PM RSSHARMA Created
1976 --
1977 -- Description
1978 -- : Method to Create New Discount Lines.
1979 -- End of Comments
1980 -- ==============================================================================
1981
1982 PROCEDURE Create_vo_discount(
1983 p_api_version_number IN NUMBER,
1984 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1985 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1986 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1987
1988 x_return_status OUT NOCOPY VARCHAR2,
1989 x_msg_count OUT NOCOPY NUMBER,
1990 x_msg_data OUT NOCOPY VARCHAR2,
1991
1992 p_vo_disc_rec IN vo_disc_rec_type ,
1993 x_vo_discount_line_id OUT NOCOPY NUMBER
1994 )
1995 IS
1996 l_api_name CONSTANT VARCHAR2(30) := 'Create_vo_discount';
1997 l_api_version_number CONSTANT NUMBER := 1.0;
1998 l_vo_discount_rec vo_disc_rec_type;
1999 l_vo_discount_line_id NUMBER;
2000 l_object_version_number NUMBER;
2001 l_dummy NUMBER;
2002 CURSOR c_id IS
2003 SELECT ozf_offer_discount_lines_s.NEXTVAL
2004 FROM dual;
2005
2006 CURSOR c_id_exists (l_id IN NUMBER) IS
2007 SELECT 1
2008 FROM OZF_OFFER_DISCOUNT_LINES
2009 WHERE offer_discount_line_id = l_id;
2010
2011 CURSOR c_struct_id IS
2012 SELECT ozf_offr_disc_struct_name_s.NEXTVAL
2013 FROM dual;
2014
2015 CURSOR c_struct_id_exists (l_id IN NUMBER) IS
2016 SELECT 1
2017 FROM ozf_offr_disc_struct_name_b
2018 WHERE OFFR_DISC_STRUCT_NAME_ID = l_id;
2019 l_struct_object_version NUMBER;
2020 l_offr_disc_struct_name_id NUMBER;
2021 l_struct_dummy NUMBER;
2022 BEGIN
2023 --initialize
2024 -- Standard Start of API savepoint
2025 SAVEPOINT Create_vo_discount_pvt;
2026
2027 -- Standard call to check for call compatibility.
2028 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2029 p_api_version_number,
2030 l_api_name,
2031 G_PKG_NAME)
2032 THEN
2033 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2034 END IF;
2035
2036 -- Initialize message list if p_init_msg_list is set to TRUE.
2037 IF FND_API.to_Boolean( p_init_msg_list )
2038 THEN
2039 FND_MSG_PUB.initialize;
2040 END IF;
2041
2042 -- Debug Message
2043 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
2044
2045 -- Initialize API return status to SUCCESS
2046 x_return_status := FND_API.G_RET_STS_SUCCESS;
2047
2048 -- =========================================================================
2049 -- Validate Environment
2050 -- =========================================================================
2051
2052 IF FND_GLOBAL.USER_ID IS NULL
2053 THEN
2054 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
2055 RAISE FND_API.G_EXC_ERROR;
2056 END IF;
2057
2058 l_vo_discount_rec := p_vo_disc_rec;
2059
2060
2061 IF p_vo_disc_rec.offer_discount_line_id IS NULL OR p_vo_disc_rec.offer_discount_line_id = FND_API.g_miss_num THEN
2062 LOOP
2063 l_dummy := NULL;
2064 OPEN c_id;
2065 FETCH c_id INTO l_vo_discount_line_id;
2066 CLOSE c_id;
2067
2068 OPEN c_id_exists(l_vo_discount_line_id);
2069 FETCH c_id_exists INTO l_dummy;
2070 CLOSE c_id_exists;
2071 EXIT WHEN l_dummy IS NULL;
2072 END LOOP;
2073 ELSE
2074 l_vo_discount_line_id := p_vo_disc_rec.offer_discount_line_id;
2075 END IF;
2076
2077
2078 IF p_vo_disc_rec.offr_disc_struct_name_id IS NULL OR p_vo_disc_rec.offr_disc_struct_name_id = FND_API.g_miss_num THEN
2079 LOOP
2080 l_dummy := NULL;
2081 OPEN c_struct_id;
2082 FETCH c_struct_id INTO l_offr_disc_struct_name_id;
2083 CLOSE c_struct_id;
2084 OZF_Offer_Adj_Line_PVT.debug_message('disc struct id is :'|| l_offr_disc_struct_name_id);
2085 OPEN c_struct_id_exists(l_offr_disc_struct_name_id);
2086 FETCH c_struct_id_exists INTO l_struct_dummy;
2087 CLOSE c_struct_id_exists;
2088 EXIT WHEN l_struct_dummy IS NULL;
2089 END LOOP;
2090 ELSE
2091 l_offr_disc_struct_name_id := p_vo_disc_rec.offr_disc_struct_name_id;
2092 END IF;
2093
2094
2095
2096
2097 l_vo_discount_rec.offer_discount_line_id := l_vo_discount_line_id ;
2098
2099 l_vo_discount_rec.offr_disc_struct_name_id := l_offr_disc_struct_name_id ;
2100
2101
2102 OZF_Offer_Adj_Line_PVT.debug_message('Calling Validate Discounts: Return Status is :' || x_return_status );
2103
2104
2105 -- validate discounts
2106 Validate_vo_discounts(
2107 p_api_version_number => p_api_version_number
2108 , p_init_msg_list => p_init_msg_list
2109 , p_validation_level => p_validation_level
2110 , px_vo_disc_rec => l_vo_discount_rec
2111 , p_validation_mode => JTF_PLSQL_API.g_create
2112 , x_return_status => x_return_status
2113 , x_msg_count => x_msg_count
2114 , x_msg_data => x_msg_data
2115 );
2116 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2117 RAISE FND_API.G_EXC_ERROR;
2118 END IF;
2119 -- create discounts
2120 -- Debug Message
2121 OZF_Offer_Adj_Line_PVT.debug_message( 'Private API: Calling create table handler');
2122 -- Invoke table handler(OZF_DISC_LINE_PKG.Insert_Row)
2123 OZF_DISC_LINE_PKG.Insert_Row(
2124 px_offer_discount_line_id => l_vo_discount_line_id,
2125 p_parent_discount_line_id => l_vo_discount_rec.parent_discount_line_id,
2126 p_volume_from => l_vo_discount_rec.volume_from,
2127 p_volume_to => l_vo_discount_rec.volume_to,
2128 p_volume_operator => l_vo_discount_rec.volume_operator,
2129 p_volume_type => l_vo_discount_rec.volume_type,
2130 p_volume_break_type => l_vo_discount_rec.volume_break_type,
2131 p_discount => l_vo_discount_rec.discount,
2132 p_discount_type => l_vo_discount_rec.discount_type,
2133 p_tier_type => l_vo_discount_rec.tier_type,
2134 p_tier_level => l_vo_discount_rec.tier_level,
2135 p_incompatibility_group => l_vo_discount_rec.incompatibility_group,
2136 p_precedence => l_vo_discount_rec.precedence,
2137 p_bucket => l_vo_discount_rec.bucket,
2138 p_scan_value => l_vo_discount_rec.scan_value,
2139 p_scan_data_quantity => l_vo_discount_rec.scan_data_quantity,
2140 p_scan_unit_forecast => l_vo_discount_rec.scan_unit_forecast,
2141 p_channel_id => l_vo_discount_rec.channel_id,
2142 p_adjustment_flag => l_vo_discount_rec.adjustment_flag,
2143 p_start_date_active => l_vo_discount_rec.start_date_active,
2144 p_end_date_active => l_vo_discount_rec.end_date_active,
2145 p_uom_code => l_vo_discount_rec.uom_code,
2146 p_creation_date => SYSDATE,
2147 p_created_by => FND_GLOBAL.USER_ID,
2148 p_last_update_date => SYSDATE,
2149 p_last_updated_by => FND_GLOBAL.USER_ID,
2150 p_last_update_login => FND_GLOBAL.conc_login_id,
2151 px_object_version_number => l_object_version_number,
2152 p_offer_id => l_vo_discount_rec.offer_id,
2153 p_context => l_vo_discount_rec.context,
2154 p_attribute1 => l_vo_discount_rec.attribute1,
2155 p_attribute2 => l_vo_discount_rec.attribute2,
2156 p_attribute3 => l_vo_discount_rec.attribute3,
2157 p_attribute4 => l_vo_discount_rec.attribute4,
2158 p_attribute5 => l_vo_discount_rec.attribute5,
2159 p_attribute6 => l_vo_discount_rec.attribute6,
2160 p_attribute7 => l_vo_discount_rec.attribute7,
2161 p_attribute8 => l_vo_discount_rec.attribute8,
2162 p_attribute9 => l_vo_discount_rec.attribute9,
2163 p_attribute10 => l_vo_discount_rec.attribute10,
2164 p_attribute11 => l_vo_discount_rec.attribute11,
2165 p_attribute12 => l_vo_discount_rec.attribute12,
2166 p_attribute13 => l_vo_discount_rec.attribute13,
2167 p_attribute14 => l_vo_discount_rec.attribute14,
2168 p_attribute15 => l_vo_discount_rec.attribute15,
2169 p_formula_id => l_vo_discount_rec.formula_id
2170 );
2171 x_vo_discount_line_id := l_vo_discount_line_id ;
2172 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2173 RAISE FND_API.G_EXC_ERROR;
2174 END IF;
2175
2176 IF l_vo_discount_rec.tier_type = 'PBH' THEN -- create tier names only for pbh tiers
2177 OZF_VO_DISC_STRUCT_NAME_PKG.Insert_Row(
2178 px_offr_disc_struct_name_id => l_offr_disc_struct_name_id
2179 , p_offer_discount_line_id => x_vo_discount_line_id
2180 , p_creation_date => SYSDATE
2181 , p_created_by => FND_GLOBAL.USER_ID
2182 , p_last_updated_by => FND_GLOBAL.USER_ID
2183 , p_last_update_date => SYSDATE
2184 , p_last_update_login => FND_GLOBAL.conc_login_id
2185 , p_name => l_vo_discount_rec.name
2186 , p_description => l_vo_discount_rec.description
2187 , px_object_version_number => l_struct_object_version
2188 );
2189
2190 END IF;
2191
2192 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
2193 RAISE FND_API.G_EXC_ERROR;
2194 END IF;
2195 --
2196 -- End of API body
2197 --
2198 -- Standard check for p_commit
2199 IF FND_API.to_Boolean( p_commit )
2200 THEN
2201 COMMIT WORK;
2202 END IF;
2203 -- Debug Message
2204 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
2205 -- Standard call to get message count and if count is 1, get message info.
2206 FND_MSG_PUB.Count_And_Get
2207 (p_count => x_msg_count,
2208 p_data => x_msg_data
2209 );
2210 EXCEPTION
2211 WHEN FND_API.G_EXC_ERROR THEN
2212 ROLLBACK TO Create_vo_discount_pvt;
2213 x_return_status := FND_API.G_RET_STS_ERROR;
2214 -- Standard call to get message count and if count=1, get the message
2215 FND_MSG_PUB.Count_And_Get (
2216 p_encoded => FND_API.G_FALSE,
2217 p_count => x_msg_count,
2218 p_data => x_msg_data
2219 );
2220
2221 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2222 ROLLBACK TO Create_vo_discount_pvt;
2223 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2224 -- Standard call to get message count and if count=1, get the message
2225 FND_MSG_PUB.Count_And_Get (
2226 p_encoded => FND_API.G_FALSE,
2227 p_count => x_msg_count,
2228 p_data => x_msg_data
2229 );
2230
2231 WHEN OTHERS THEN
2232 ROLLBACK TO Create_vo_discount_pvt;
2233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2234 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2235 THEN
2236 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2237 END IF;
2238 -- Standard call to get message count and if count=1, get the message
2239 FND_MSG_PUB.Count_And_Get (
2240 p_encoded => FND_API.G_FALSE,
2241 p_count => x_msg_count,
2242 p_data => x_msg_data
2243 );
2244 END Create_vo_discount;
2245
2246
2247
2248 -- ==============================================================================
2249 -- Start of Comments
2250 -- ==============================================================================
2251 -- API Name
2252 -- Update_vo_discount
2253 -- Type
2254 -- Private
2255 -- Pre-Req
2256 -- Create_Ozf_Disc_Line,Create Product
2257 -- Parameters
2258 --
2259 -- IN
2260 -- p_api_version_number IN NUMBER Required
2261 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
2262 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
2263 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
2264 -- p_ozf_discount_line_rec IN ozf_discount_line_rec_type Required Record Containing Discount Line Data
2265 -- x_return_status OUT NOCOPY VARCHAR2
2266 -- x_msg_count OUT NOCOPY NUMBER
2267 -- x_msg_data OUT NOCOPY VARCHAR2
2268 -- Version : Current version 1.0
2269 --
2270 -- History
2271 -- Wed Oct 01 2003:5/21 PM RSSHARMA Created
2272 --
2273 -- Description
2274 -- : Method to Update Discount Lines.
2275 -- End of Comments
2276 -- ==============================================================================
2277 PROCEDURE Update_vo_discount(
2278 p_api_version_number IN NUMBER,
2279 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2280 p_commit IN VARCHAR2 := FND_API.G_FALSE,
2281 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2282
2283 x_return_status OUT NOCOPY VARCHAR2,
2284 x_msg_count OUT NOCOPY NUMBER,
2285 x_msg_data OUT NOCOPY VARCHAR2,
2286
2287 p_vo_disc_rec IN vo_disc_rec_type
2288 )IS
2289
2290 CURSOR c_get_vo_disc_line(p_offer_discount_line_id NUMBER, p_object_version_number NUMBER) IS
2291 SELECT *
2292 FROM OZF_OFFER_DISCOUNT_LINES
2293 WHERE offer_discount_line_id = p_offer_discount_line_id
2294 AND object_version_number = p_object_version_number;
2295 -- Hint: Developer need to provide Where clause
2296
2297 l_api_name CONSTANT VARCHAR2(30) := 'Update_vo_discount';
2298 l_api_version_number CONSTANT NUMBER := 1.0;
2299 -- Local Variables
2300 l_object_version_number NUMBER;
2301 l_offer_discount_line_id NUMBER;
2302 l_ref_vo_disc_line_rec c_get_vo_disc_line%ROWTYPE ;
2303 l_tar_vo_disc_line_rec vo_disc_rec_type := p_vo_disc_rec ;
2304 l_rowid ROWID;
2305 l_struct_object_version NUMBER;
2306 l_offr_disc_struct_name_id NUMBER;
2307 l_tier_type OZF_OFFER_DISCOUNT_LINES.TIER_TYPE%TYPE;
2308 BEGIN
2309 --initialize
2310 -- Standard Start of API savepoint
2311 SAVEPOINT update_ozf_disc_line_pvt;
2312 -- Standard call to check for call compatibility.
2313 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2314 p_api_version_number,
2315 l_api_name,
2316 G_PKG_NAME)
2317 THEN
2318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2319 END IF;
2320 -- Initialize message list if p_init_msg_list is set to TRUE.
2321 IF FND_API.to_Boolean( p_init_msg_list )
2322 THEN
2323 FND_MSG_PUB.initialize;
2324 END IF;
2325 -- Debug Message
2326 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
2327
2328 -- Initialize API return status to SUCCESS
2329 x_return_status := FND_API.G_RET_STS_SUCCESS;
2330
2331 -- Debug Message
2332 OZF_Offer_Adj_Line_PVT.debug_message('Private API: - Open Cursor to Select');
2333 OZF_Offer_Adj_Line_PVT.debug_message('INputs : '|| l_tar_vo_disc_line_rec.offer_discount_line_id || ' : ' || l_tar_vo_disc_line_rec.object_version_number);
2334 OPEN c_get_vo_disc_line( l_tar_vo_disc_line_rec.offer_discount_line_id,l_tar_vo_disc_line_rec.object_version_number);
2335 FETCH c_get_vo_disc_line INTO l_ref_vo_disc_line_rec ;
2336 If ( c_get_vo_disc_line%NOTFOUND) THEN
2337 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
2338 , p_token_name => 'INFO'
2339 , p_token_value => 'Ozf_Disc_Line') ;
2340 RAISE FND_API.G_EXC_ERROR;
2341 END IF;
2342 CLOSE c_get_vo_disc_line;
2343
2344 If (l_tar_vo_disc_line_rec.object_version_number is NULL or
2345 l_tar_vo_disc_line_rec.object_version_number = FND_API.G_MISS_NUM ) Then
2346 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
2347 , p_token_name => 'COLUMN'
2348 , p_token_value => 'Last_Update_Date') ;
2349 RAISE FND_API.G_EXC_ERROR;
2350 End if;
2351 -- Check Whether record has been changed by someone else
2352 If (l_tar_vo_disc_line_rec.object_version_number <> l_ref_vo_disc_line_rec.object_version_number) Then
2353 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
2354 , p_token_name => 'INFO'
2355 , p_token_value => 'Ozf_Disc_Line') ;
2356 RAISE FND_API.G_EXC_ERROR;
2357 End if;
2358
2359 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
2360 THEN
2361 -- Debug message
2362 OZF_Offer_Adj_Line_PVT.debug_message('Private API: Validate_vo_discounts');
2363 -- validate data
2364 Validate_vo_discounts(
2365 p_api_version_number => p_api_version_number
2366 , p_init_msg_list => p_init_msg_list
2367 , p_validation_level => p_validation_level
2368 , px_vo_disc_rec => l_tar_vo_disc_line_rec
2369 , p_validation_mode => JTF_PLSQL_API.g_update
2370 , x_return_status => x_return_status
2371 , x_msg_count => x_msg_count
2372 , x_msg_data => x_msg_data
2373 );
2374 END IF;
2375
2376 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
2377 RAISE FND_API.G_EXC_ERROR;
2378 END IF;
2379 --update data in table
2380 OZF_DISC_LINE_PKG.Update_Row(
2381 p_offer_discount_line_id => l_tar_vo_disc_line_rec.offer_discount_line_id,
2382 p_parent_discount_line_id => l_tar_vo_disc_line_rec.parent_discount_line_id,
2383 p_volume_from => l_tar_vo_disc_line_rec.volume_from,
2384 p_volume_to => l_tar_vo_disc_line_rec.volume_to,
2385 p_volume_operator => l_tar_vo_disc_line_rec.volume_operator,
2386 p_volume_type => l_tar_vo_disc_line_rec.volume_type,
2387 p_volume_break_type => l_tar_vo_disc_line_rec.volume_break_type,
2388 p_discount => l_tar_vo_disc_line_rec.discount,
2389 p_discount_type => l_tar_vo_disc_line_rec.discount_type,
2390 p_tier_type => l_tar_vo_disc_line_rec.tier_type,
2391 p_tier_level => l_tar_vo_disc_line_rec.tier_level,
2392 p_incompatibility_group => l_tar_vo_disc_line_rec.incompatibility_group,
2393 p_precedence => l_tar_vo_disc_line_rec.precedence,
2394 p_bucket => l_tar_vo_disc_line_rec.bucket,
2395 p_scan_value => l_tar_vo_disc_line_rec.scan_value,
2396 p_scan_data_quantity => l_tar_vo_disc_line_rec.scan_data_quantity,
2397 p_scan_unit_forecast => l_tar_vo_disc_line_rec.scan_unit_forecast,
2398 p_channel_id => l_tar_vo_disc_line_rec.channel_id,
2399 p_adjustment_flag => l_tar_vo_disc_line_rec.adjustment_flag,
2400 p_start_date_active => l_tar_vo_disc_line_rec.start_date_active,
2401 p_end_date_active => l_tar_vo_disc_line_rec.end_date_active,
2402 p_uom_code => l_tar_vo_disc_line_rec.uom_code,
2403 p_last_update_date => SYSDATE,
2404 p_last_updated_by => FND_GLOBAL.USER_ID,
2405 p_last_update_login => FND_GLOBAL.conc_login_id,
2406 p_object_version_number => l_tar_vo_disc_line_rec.object_version_number,
2407 p_context => l_tar_vo_disc_line_rec.context,
2408 p_attribute1 => l_tar_vo_disc_line_rec.attribute1,
2409 p_attribute2 => l_tar_vo_disc_line_rec.attribute2,
2410 p_attribute3 => l_tar_vo_disc_line_rec.attribute3,
2411 p_attribute4 => l_tar_vo_disc_line_rec.attribute4,
2412 p_attribute5 => l_tar_vo_disc_line_rec.attribute5,
2413 p_attribute6 => l_tar_vo_disc_line_rec.attribute6,
2414 p_attribute7 => l_tar_vo_disc_line_rec.attribute7,
2415 p_attribute8 => l_tar_vo_disc_line_rec.attribute8,
2416 p_attribute9 => l_tar_vo_disc_line_rec.attribute9,
2417 p_attribute10 => l_tar_vo_disc_line_rec.attribute10,
2418 p_attribute11 => l_tar_vo_disc_line_rec.attribute11,
2419 p_attribute12 => l_tar_vo_disc_line_rec.attribute12,
2420 p_attribute13 => l_tar_vo_disc_line_rec.attribute13,
2421 p_attribute14 => l_tar_vo_disc_line_rec.attribute14,
2422 p_attribute15 => l_tar_vo_disc_line_rec.attribute15,
2423 p_offer_id => l_tar_vo_disc_line_rec.offer_id,
2424 p_formula_id => l_tar_vo_disc_line_rec.formula_id
2425 );
2426
2427 SELECT tier_type into l_tier_type FROM ozf_offer_discount_lines
2428 WHERE offer_discount_line_id = p_vo_disc_rec.offer_discount_line_id;
2429
2430 IF l_tier_type = 'PBH' THEN
2431 SELECT object_version_number, offr_disc_struct_name_id into l_struct_object_version , l_offr_disc_struct_name_id
2432 FROM ozf_offr_disc_struct_name_b
2433 WHERE offer_discount_line_id = p_vo_disc_rec.offer_discount_line_id;
2434
2435 OZF_VO_DISC_STRUCT_NAME_PKG.Update_Row(
2436 p_offr_disc_struct_name_id => l_offr_disc_struct_name_id
2437 , p_offer_discount_line_id => p_vo_disc_rec.offer_discount_line_id
2438 , p_last_updated_by => FND_GLOBAL.USER_ID
2439 , p_last_update_date => SYSDATE
2440 , p_last_update_login => FND_GLOBAL.conc_login_id
2441 , p_name => p_vo_disc_rec.name
2442 , p_description => p_vo_disc_rec.description
2443 , px_object_version_number => l_struct_object_version
2444 );
2445 END IF;
2446 --
2447 -- End of API body.
2448 --
2449 -- Standard check for p_commit
2450 IF FND_API.to_Boolean( p_commit )
2451 THEN
2452 COMMIT WORK;
2453 END IF;
2454 -- Debug Message
2455 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
2456 -- Standard call to get message count and if count is 1, get message info.
2457 FND_MSG_PUB.Count_And_Get
2458 (p_count => x_msg_count,
2459 p_data => x_msg_data
2460 );
2461 -- exception handling
2462 EXCEPTION
2463
2464 WHEN OZF_Utility_PVT.resource_locked THEN
2465 x_return_status := FND_API.g_ret_sts_error;
2466 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
2467
2468 WHEN FND_API.G_EXC_ERROR THEN
2469 ROLLBACK TO UPDATE_Ozf_Disc_Line_PVT;
2470 x_return_status := FND_API.G_RET_STS_ERROR;
2471 -- Standard call to get message count and if count=1, get the message
2472 FND_MSG_PUB.Count_And_Get (
2473 p_encoded => FND_API.G_FALSE,
2474 p_count => x_msg_count,
2475 p_data => x_msg_data
2476 );
2477
2478 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2479 ROLLBACK TO UPDATE_Ozf_Disc_Line_PVT;
2480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2481 -- Standard call to get message count and if count=1, get the message
2482 FND_MSG_PUB.Count_And_Get (
2483 p_encoded => FND_API.G_FALSE,
2484 p_count => x_msg_count,
2485 p_data => x_msg_data
2486 );
2487
2488 WHEN OTHERS THEN
2489 ROLLBACK TO UPDATE_Ozf_Disc_Line_PVT;
2490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2491 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2492 THEN
2493 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2494 END IF;
2495 -- Standard call to get message count and if count=1, get the message
2496 FND_MSG_PUB.Count_And_Get (
2497 p_encoded => FND_API.G_FALSE,
2498 p_count => x_msg_count,
2499 p_data => x_msg_data
2500 );
2501 END Update_vo_discount;
2502
2503
2504
2505 -- ==============================================================================
2506 -- Start of Comments
2507 -- ==============================================================================
2508 -- API Name
2509 -- check_vo_product_Uk_Items
2510 -- Type
2511 -- Private
2512 -- Pre-Req
2513 -- Parameters
2514 --
2515 -- IN
2516 -- p_validation_mode IN VARCHAR2
2517 -- p_vo_disc_rec IN vo_disc_rec_type
2518 --
2519 -- OUT NOCOPY
2520 -- x_return_status OUT NOCOPY VARCHAR2
2521 -- Version : Current version 1.0
2522 --
2523 -- History
2524 -- Fri May 06 2005:6/32 PM RSSHARMA Created
2525 --
2526 -- Description
2527 -- End of Comments
2528 -- ==============================================================================
2529
2530 PROCEDURE check_vo_product_Uk_Items(
2531 p_vo_prod_rec IN vo_prod_rec_type
2532 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
2533 , x_return_status OUT NOCOPY VARCHAR2
2534 )
2535 IS
2536 l_valid_flag VARCHAR2(1);
2537 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_Uk_Items';
2538 BEGIN
2539 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
2540 x_return_status := FND_API.g_ret_sts_success;
2541 IF p_validation_mode = JTF_PLSQL_API.g_create
2542 AND (p_vo_prod_rec.off_discount_product_id IS NOT NULL AND p_vo_prod_rec.off_discount_product_id <> FND_API.g_miss_num)
2543 THEN
2544 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
2545 'ozf_offer_discount_products',
2546 'OFF_DISCOUNT_PRODUCT_ID = ''' || p_vo_prod_rec.off_discount_product_id ||''''
2547 );
2548 OZF_Offer_Adj_Line_PVT.debug_message('Off Discount Product Id is '||p_vo_prod_rec.off_discount_product_id);
2549 IF l_valid_flag = FND_API.g_false THEN
2550 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_PROD_LINE_ID_DUP');
2551 x_return_status := FND_API.g_ret_sts_error;
2552 return;
2553 END IF;
2554 END IF;
2555
2556 declare
2557 l_attr varchar2(500) := 'product_attribute = ''' || p_vo_prod_rec.product_attribute ||''' AND product_attr_value = '''|| p_vo_prod_rec.product_attr_value ;
2558 l_attr2 varchar2(500):= ''' AND apply_discount_flag = '''||p_vo_prod_rec.apply_discount_flag || ''' AND offer_id = '|| p_vo_prod_rec.offer_id;
2559 begin
2560 IF (p_vo_prod_rec.product_attr_value IS NOT NULL AND p_vo_prod_rec.product_attr_value <> FND_API.g_miss_char) THEN
2561
2562 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
2563 'ozf_offer_discount_products',
2564 l_attr || l_attr2
2565 );
2566 OZF_Offer_Adj_Line_PVT.debug_message('Valid Flag for duplicate products is:'||l_valid_flag);
2567 IF l_valid_flag = FND_API.g_false THEN
2568 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_VO_PROD_DUP');
2569 x_return_status := FND_API.g_ret_sts_error;
2570 return;
2571 END IF;
2572
2573 END IF;
2574 end;
2575 END check_vo_product_Uk_Items;
2576
2577
2578 -- Check Items Foreign Keys API calls
2579
2580 -- ==============================================================================
2581 -- Start of Comments
2582 -- ==============================================================================
2583 -- API Name
2584 -- check_vo_product_req_items
2585 -- Type
2586 -- Private
2587 -- Pre-Req
2588 -- Parameters
2589 --
2590 -- IN
2591 -- p_validation_mode IN VARCHAR2
2592 -- p_vo_disc_rec IN vo_disc_rec_type
2593 --
2594 -- OUT NOCOPY
2595 -- x_return_status OUT NOCOPY VARCHAR2
2596 -- Version : Current version 1.0
2597 --
2598 -- History
2599 -- Fri May 06 2005:6/32 PM RSSHARMA Created
2600 --
2601 -- Description
2602 -- End of Comments
2603 -- ==============================================================================
2604
2605 PROCEDURE check_vo_product_req_items(
2606 p_vo_prod_rec IN vo_prod_rec_type
2607 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
2608 , x_return_status OUT NOCOPY VARCHAR2
2609 )
2610 IS
2611 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_req_items';
2612 BEGIN
2613 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
2614 OZF_Offer_Adj_Line_PVT.debug_message('Validation Mode is : ' || p_validation_mode || ' '|| JTF_PLSQL_API.g_create);
2615 x_return_status := FND_API.g_ret_sts_success;
2616 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2617 IF p_vo_prod_rec.off_discount_product_id = FND_API.G_MISS_NUM OR p_vo_prod_rec.off_discount_product_id IS NULL THEN
2618 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'off_discount_product_id' );
2619 x_return_status := FND_API.g_ret_sts_error;
2620 return;
2621 END IF;
2622
2623 IF p_vo_prod_rec.offer_discount_line_id = FND_API.G_MISS_NUM OR p_vo_prod_rec.offer_discount_line_id IS NULL THEN
2624 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_DISCOUNT_LINE_ID' );
2625 x_return_status := FND_API.g_ret_sts_error;
2626 return;
2627 END IF;
2628
2629 IF p_vo_prod_rec.offer_id = FND_API.G_MISS_NUM OR p_vo_prod_rec.offer_id IS NULL THEN
2630 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ID' );
2631 x_return_status := FND_API.g_ret_sts_error;
2632 return;
2633 END IF;
2634
2635 IF p_vo_prod_rec.product_context = FND_API.G_MISS_CHAR OR p_vo_prod_rec.product_context IS NULL THEN
2636 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'PRODUCT_CONTEXT' );
2637 x_return_status := FND_API.g_ret_sts_error;
2638 return;
2639 END IF;
2640
2641 IF p_vo_prod_rec.product_attr_value = FND_API.G_MISS_CHAR OR p_vo_prod_rec.product_attr_value IS NULL THEN
2642 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_NAME') );
2643 x_return_status := FND_API.g_ret_sts_error;
2644 return;
2645 END IF;
2646
2647 IF p_vo_prod_rec.product_attribute = FND_API.G_MISS_CHAR OR p_vo_prod_rec.product_attribute IS NULL THEN
2648 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_PROD_LEVEL') );
2649 x_return_status := FND_API.g_ret_sts_error;
2650 return;
2651 END IF;
2652
2653 IF p_vo_prod_rec.apply_discount_flag = FND_API.G_MISS_CHAR OR p_vo_prod_rec.apply_discount_flag IS NULL THEN
2654 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_OFFR_APPLY_DISC') );
2655 x_return_status := FND_API.g_ret_sts_error;
2656 return;
2657 END IF;
2658
2659 IF p_vo_prod_rec.include_volume_flag = FND_API.G_MISS_CHAR OR p_vo_prod_rec.include_volume_flag IS NULL THEN
2660 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', OZF_UTILITY_PVT.getAttributeName(p_attributeCode => 'OZF_OFFR_INCL_VOL') );
2661 x_return_status := FND_API.g_ret_sts_error;
2662 return;
2663 END IF;
2664
2665 ELSE
2666 OZF_Offer_Adj_Line_PVT.debug_message('In Update Mode');
2667 IF p_vo_prod_rec.off_discount_product_id = FND_API.G_MISS_NUM THEN
2668 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'off_discount_product_id' );
2669 x_return_status := FND_API.g_ret_sts_error;
2670 return;
2671 END IF;
2672
2673 IF p_vo_prod_rec.offer_discount_line_id = FND_API.G_MISS_NUM THEN
2674 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_DISCOUNT_LINE_ID' );
2675 x_return_status := FND_API.g_ret_sts_error;
2676 return;
2677 END IF;
2678 OZF_Offer_Adj_Line_PVT.debug_message('OFFER_ID IS '||p_vo_prod_rec.offer_id);
2679 IF p_vo_prod_rec.offer_id = FND_API.G_MISS_NUM THEN
2680 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ID' );
2681 x_return_status := FND_API.g_ret_sts_error;
2682 return;
2683 END IF;
2684
2685 END IF;
2686
2687 IF p_vo_prod_rec.offer_discount_line_id = -1 THEN
2688 OZF_Utility_PVT.Error_Message('OZF_DIS_LINE_NO_PARENT' );
2689 x_return_status := FND_API.g_ret_sts_error;
2690 return;
2691 END IF;
2692
2693 END check_vo_product_req_items;
2694
2695
2696 -- ==============================================================================
2697 -- Start of Comments
2698 -- ==============================================================================
2699 -- API Name
2700 -- check_vo_product_FK_items
2701 -- Type
2702 -- Private
2703 -- Pre-Req
2704 -- Parameters
2705 --
2706 -- IN
2707 -- p_validation_mode IN VARCHAR2
2708 -- p_vo_disc_rec IN vo_disc_rec_type
2709 --
2710 -- OUT NOCOPY
2711 -- x_return_status OUT NOCOPY VARCHAR2
2712 -- Version : Current version 1.0
2713 --
2714 -- History
2715 -- Fri May 06 2005:6/32 PM RSSHARMA Created
2716 --
2717 -- Description
2718 -- End of Comments
2719 -- ==============================================================================
2720
2721 PROCEDURE check_vo_product_FK_items(
2722 p_vo_prod_rec IN vo_prod_rec_type
2723 , x_return_status OUT NOCOPY VARCHAR2
2724 )
2725 IS
2726 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_FK_items';
2727 BEGIN
2728 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
2729 x_return_status := FND_API.G_RET_STS_SUCCESS;
2730 IF p_vo_prod_rec.offer_id IS NOT NULL AND p_vo_prod_rec.offer_id <> FND_API.G_MISS_NUM
2731 THEN
2732 IF ozf_utility_pvt.check_fk_exists('OZF_OFFERS','OFFER_ID',to_char(p_vo_prod_rec.offer_id)) = FND_API.g_false THEN
2733 OZF_Utility_PVT.Error_Message('OZF_INVALID_OFFER_ID' );
2734 x_return_status := FND_API.g_ret_sts_error;
2735 return;
2736 END IF;
2737 END IF;
2738 OZF_Offer_Adj_Line_PVT.debug_message('Offer Discount LIne Id is: ' || p_vo_prod_rec.offer_discount_line_id || 'end');
2739 IF p_vo_prod_rec.offer_discount_line_id IS NOT NULL AND p_vo_prod_rec.offer_discount_line_id <> FND_API.G_MISS_NUM
2740 THEN
2741 IF ozf_utility_pvt.check_fk_exists('OZF_OFFER_DISCOUNT_LINES','OFFER_DISCOUNT_LINE_ID',to_char(p_vo_prod_rec.offer_discount_line_id)) = FND_API.g_false THEN
2742 OZF_Utility_PVT.Error_Message('OZF_INVALID_DISCOUNT_ID' );
2743 x_return_status := FND_API.g_ret_sts_error;
2744 return;
2745 END IF;
2746 END IF;
2747 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
2748 END check_vo_product_FK_items;
2749
2750 -- ==============================================================================
2751 -- Start of Comments
2752 -- ==============================================================================
2753 -- API Name
2754 -- check_vo_product_Lkup_Items
2755 -- Type
2756 -- Private
2757 -- Pre-Req
2758 -- Parameters
2759 --
2760 -- IN
2761 -- p_validation_mode IN VARCHAR2
2762 -- p_vo_disc_rec IN vo_disc_rec_type
2763 --
2764 -- OUT NOCOPY
2765 -- x_return_status OUT NOCOPY VARCHAR2
2766 -- Version : Current version 1.0
2767 --
2768 -- History
2769 -- Fri May 06 2005:6/32 PM RSSHARMA Created
2770 --
2771 -- Description
2772 -- End of Comments
2773 -- ==============================================================================
2774 PROCEDURE check_vo_product_Lkup_Items(
2775 p_vo_prod_rec IN vo_prod_rec_type
2776 , x_return_status OUT NOCOPY VARCHAR2
2777 )
2778 IS
2779 CURSOR C_UOM_CODE_EXISTS (p_uom_code VARCHAR2,p_organization_id NUMBER,p_inventory_item_id NUMBER)
2780 IS
2781 SELECT 1 FROM mtl_item_uoms_view
2782 WHERE organization_id = p_organization_id
2783 AND uom_code = p_uom_code
2784 AND inventory_item_id = p_inventory_item_id;
2785
2786 l_organization_id NUMBER := -999;
2787 l_UOM_CODE_EXISTS C_UOM_CODE_EXISTS%ROWTYPE;
2788
2789 CURSOR c_uom_code(p_discount_line_id NUMBER)
2790 IS
2791 SELECT uom_code , volume_type, offer_id
2792 FROM ozf_offer_discount_lines
2793 WHERE offer_discount_line_id = p_discount_line_id;
2794
2795 l_uom_code c_uom_code%rowtype;
2796
2797 CURSOR c_general_uom(p_uom_code VARCHAR2)
2798 IS
2799 SELECT 1 FROM DUAL WHERE EXISTS (SELECT 1
2800 FROM mtl_units_of_measure_vl
2801 WHERE uom_code = p_uom_code);
2802 l_general_uom c_general_uom%rowtype;
2803 l_list_header_id NUMBER;
2804 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_Lkup_Items';
2805 BEGIN
2806 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
2807 x_return_status := FND_API.G_RET_STS_SUCCESS;
2808 --=====================================================================
2809 -- uom validation begin
2810 --=====================================================================
2811 OPEN c_uom_code(p_vo_prod_rec.offer_discount_line_id);
2812
2813 FETCH c_uom_code INTO l_uom_code;
2814
2815 IF ( c_uom_code%NOTFOUND) THEN
2816 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_INVALID_DISCOUNT_ID') ;
2817 x_return_status := FND_API.G_RET_STS_ERROR;
2818 END IF;
2819
2820 CLOSE c_uom_code;
2821
2822 IF l_uom_code.volume_type = 'PRICING_ATTRIBUTE10' THEN
2823
2824 l_organization_id := QP_UTIL.Get_Item_Validation_Org;
2825
2826 IF(p_vo_prod_rec.product_attribute = 'PRICING_ATTRIBUTE1') THEN
2827
2828 OPEN c_uom_code_exists(l_uom_code.uom_code,l_organization_id,p_vo_prod_rec.product_attr_value);
2829
2830 FETCH c_uom_code_exists INTO l_uom_code_exists;
2831
2832 IF ( c_uom_code_exists%NOTFOUND) THEN
2833 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2834 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
2835 FND_MSG_PUB.add;
2836 x_return_status := FND_API.G_RET_STS_ERROR;
2837 END IF;
2838 END IF;
2839 CLOSE c_uom_code_exists;
2840
2841 ELSIF(p_vo_prod_rec.product_attribute = 'PRICING_ATTRIBUTE2') THEN
2842 /*
2843 IF QP_CATEGORY_MAPPING_RULE.Validate_UOM(
2844 l_organization_id,
2845 to_number(p_vo_prod_rec.product_attr_value),
2846 l_uom_code.uom_code) = 'N'
2847 */
2848 select qp_list_header_id into l_list_header_id from ozf_offers where offer_id = l_uom_code.offer_id;
2849 IF NOT QP_Validate.Product_Uom (
2850 l_uom_code.uom_code,
2851 to_number(p_vo_prod_rec.product_attr_value),
2852 l_list_header_id)
2853 THEN
2854 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2855 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
2856 FND_MSG_PUB.add;
2857 x_return_status := FND_API.G_RET_STS_ERROR;
2858 END IF;
2859 END IF;
2860 ELSE
2861 OPEN c_general_uom(l_uom_code.uom_code);
2862 FETCH c_general_uom INTO l_general_uom;
2863 IF ( c_general_uom%NOTFOUND) THEN
2864 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2865 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_UOM');
2866 FND_MSG_PUB.add;
2867 x_return_status := FND_API.G_RET_STS_ERROR;
2868 END IF;
2869 END IF;
2870 CLOSE c_general_uom;
2871 END IF;
2872 --===========================================================================
2873 -- uom validation end
2874 --===========================================================================
2875
2876 END IF;
2877
2878 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
2879
2880 END check_vo_product_Lkup_Items;
2881
2882
2883 -- ==============================================================================
2884 -- Start of Comments
2885 -- ==============================================================================
2886 -- API Name
2887 -- check_vo_product_attr
2888 -- Type
2889 -- Private
2890 -- Pre-Req
2891 -- Parameters
2892 --
2893 -- IN
2894 -- p_validation_mode IN VARCHAR2
2895 -- p_vo_disc_rec IN vo_disc_rec_type
2896 --
2897 -- OUT NOCOPY
2898 -- x_return_status OUT NOCOPY VARCHAR2
2899 -- Version : Current version 1.0
2900 --
2901 -- History
2902 -- Mon May 16 2005:5/41 PM RSSHARMA Created
2903 --
2904 -- Description
2905 -- End of Comments
2906 -- ==============================================================================
2907
2908 PROCEDURE check_vo_product_attr(
2909 p_vo_prod_rec IN vo_prod_rec_type
2910 , x_return_status OUT NOCOPY VARCHAR2
2911 )
2912 IS
2913 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_attr';
2914 l_context_flag VARCHAR2(1);
2915 l_attribute_flag VARCHAR2(1);
2916 l_value_flag VARCHAR2(1);
2917 l_datatype VARCHAR2(1);
2918 l_precedence NUMBER;
2919 l_error_code NUMBER := 0;
2920 BEGIN
2921 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
2922 x_return_status := FND_API.G_RET_STS_SUCCESS;
2923
2924
2925
2926 QP_UTIL.validate_qp_flexfield(flexfield_name =>'QP_ATTR_DEFNS_PRICING'
2927 ,context =>p_vo_prod_rec.product_context
2928 ,attribute =>p_vo_prod_rec.product_attribute
2929 ,value =>p_vo_prod_rec.product_attr_value
2930 ,application_short_name => 'QP'
2931 ,context_flag =>l_context_flag
2932 ,attribute_flag =>l_attribute_flag
2933 ,value_flag =>l_value_flag
2934 ,datatype =>l_datatype
2935 ,precedence =>l_precedence
2936 ,error_code =>l_error_code
2937 );
2938 If (l_context_flag = 'N' AND l_error_code = 7) -- invalid context
2939 Then
2940 x_return_status := FND_API.G_RET_STS_ERROR;
2941 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2942 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_CONTEXT' );
2943 FND_MSG_PUB.add;
2944 END IF;
2945 End If;
2946
2947 If (l_attribute_flag = 'N' AND l_error_code = 8) -- invalid attribute
2948 Then
2949 x_return_status := FND_API.G_RET_STS_ERROR;
2950 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2951 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_ATTR' );
2952 FND_MSG_PUB.add;
2953 END IF;
2954 End If;
2955
2956 If (l_value_flag = 'N' AND l_error_code = 9) -- invalid value
2957 Then
2958 x_return_status := FND_API.G_RET_STS_ERROR;
2959 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2960 FND_MESSAGE.SET_NAME('QP','QP_INVALID_PROD_VALUE' );
2961 FND_MSG_PUB.add;
2962 END IF;
2963 End If;
2964
2965
2966
2967 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
2968 END check_vo_product_attr;
2969
2970 -- ==============================================================================
2971 -- Start of Comments
2972 -- ==============================================================================
2973 -- API Name
2974 -- check_vo_product_attr
2975 -- Type
2976 -- Private
2977 -- Pre-Req
2978 -- Parameters
2979 --
2980 -- IN
2981 -- p_validation_mode IN VARCHAR2
2982 -- p_vo_disc_rec IN vo_disc_rec_type
2983 --
2984 -- OUT NOCOPY
2985 -- x_return_status OUT NOCOPY VARCHAR2
2986 -- Version : Current version 1.0
2987 --
2988 -- History
2989 -- Mon May 16 2005:5/41 PM RSSHARMA Created
2990 --
2991 -- Description
2992 -- End of Comments
2993 -- ==============================================================================
2994
2995 PROCEDURE check_vo_product_inter_attr(
2996 p_vo_prod_rec IN vo_prod_rec_type
2997 , x_return_status OUT NOCOPY VARCHAR2
2998 )
2999 IS
3000 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_inter_attr';
3001 BEGIN
3002 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
3003 x_return_status := FND_API.G_RET_STS_SUCCESS;
3004 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
3005 END check_vo_product_inter_attr;
3006
3007 PROCEDURE check_vo_product_entity_attr(
3008 p_vo_prod_rec IN vo_prod_rec_type
3009 , x_return_status OUT NOCOPY VARCHAR2
3010 )
3011 IS
3012 CURSOR c_discount_volume_type(p_offer_discount_line_id NUMBER) IS
3013 SELECT volume_type FROM ozf_offer_discount_lines
3014 WHERE offer_discount_line_id = p_offer_discount_line_id;
3015
3016 l_discount_volume_type c_discount_volume_type%rowtype;
3017
3018 l_api_name CONSTANT VARCHAR2(30) := 'check_vo_product_entity_attr';
3019 BEGIN
3020 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
3021 x_return_status := FND_API.G_RET_STS_SUCCESS;
3022 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
3023 END check_vo_product_entity_attr;
3024
3025 -- ==============================================================================
3026 -- Start of Comments
3027 -- ==============================================================================
3028 -- API Name
3029 -- Check_vo_product_Items
3030 -- Type
3031 -- Private
3032 -- Pre-Req
3033 -- Parameters
3034 --
3035 -- IN
3036 -- p_validation_mode IN VARCHAR2
3037 -- p_vo_disc_rec IN vo_disc_rec_type
3038 --
3039 -- OUT NOCOPY
3040 -- x_return_status OUT NOCOPY VARCHAR2
3041 -- Version : Current version 1.0
3042 --
3043 -- History
3044 -- Mon May 16 2005:5/41 PM RSSHARMA Created
3045 --
3046 -- Description
3047 -- End of Comments
3048 -- ==============================================================================
3049 PROCEDURE Check_vo_product_Items(
3050 p_vo_prod_rec IN vo_prod_rec_type
3051 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
3052 , x_return_status OUT NOCOPY VARCHAR2
3053 )
3054 IS
3055 l_api_name CONSTANT VARCHAR2(30) := 'Check_vo_Product_Items';
3056 BEGIN
3057 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
3058 x_return_status := FND_API.G_RET_STS_SUCCESS;
3059
3060 check_vo_product_req_items(
3061 p_vo_prod_rec => p_vo_prod_rec,
3062 p_validation_mode => p_validation_mode,
3063 x_return_status => x_return_status);
3064
3065 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3066 RAISE FND_API.G_EXC_ERROR;
3067 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3068 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3069 END IF;
3070
3071 -- Check Items Uniqueness API calls
3072 check_vo_product_Uk_Items(
3073 p_vo_prod_rec => p_vo_prod_rec,
3074 p_validation_mode => p_validation_mode,
3075 x_return_status => x_return_status);
3076
3077 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3078 RAISE FND_API.G_EXC_ERROR;
3079 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3080 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3081 END IF;
3082
3083 check_vo_product_attr(
3084 p_vo_prod_rec => p_vo_prod_rec,
3085 x_return_status => x_return_status
3086 );
3087
3088 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3089 RAISE FND_API.G_EXC_ERROR;
3090 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3092 END IF;
3093
3094
3095 -- Check Items Foreign Keys API calls
3096
3097 check_vo_product_FK_items(
3098 p_vo_prod_rec => p_vo_prod_rec,
3099 x_return_status => x_return_status);
3100
3101 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3102 RAISE FND_API.G_EXC_ERROR;
3103 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3104 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3105 END IF;
3106
3107 -- Check Items Lookups
3108
3109 check_vo_product_Lkup_Items(
3110 p_vo_prod_rec => p_vo_prod_rec,
3111 x_return_status => x_return_status);
3112
3113 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3114 RAISE FND_API.G_EXC_ERROR;
3115 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3117 END IF;
3118
3119
3120
3121 check_vo_product_inter_attr(
3122 p_vo_prod_rec => p_vo_prod_rec,
3123 x_return_status => x_return_status
3124 );
3125
3126 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3127 RAISE FND_API.G_EXC_ERROR;
3128 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3130 END IF;
3131
3132 check_vo_product_entity_attr(
3133 p_vo_prod_rec => p_vo_prod_rec,
3134 x_return_status => x_return_status
3135 );
3136
3137 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3138 RAISE FND_API.G_EXC_ERROR;
3139 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3141 END IF;
3142
3143 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
3144 END Check_vo_product_Items;
3145
3146
3147 -- ==============================================================================
3148 -- Start of Comments
3149 -- ==============================================================================
3150 -- API Name
3151 -- Validate_vo_products
3152 -- Type
3153 -- Private
3154 -- Pre-Req
3155 -- Parameters
3156 --
3157 -- IN
3158 -- p_api_version_number IN NUMBER
3159 -- p_init_msg_list IN VARCHAR2
3160 -- p_validation_level IN NUMBER
3161 -- p_vo_prod_rec IN vo_prod_rec_type
3162 -- p_validation_mode IN VARCHAR2
3163 --
3164 -- OUT NOCOPY
3165 -- x_return_status OUT NOCOPY VARCHAR2
3166 -- x_msg_count OUT NOCOPY NUMBER
3167 -- x_msg_data OUT NOCOPY VARCHAR2
3168
3169 -- Version : Current version 1.0
3170 --
3171 -- History
3172 -- Mon May 16 2005:5/41 PM RSSHARMA Created
3173 --
3174 -- Description
3175 -- End of Comments
3176 -- ==============================================================================
3177
3178 PROCEDURE Validate_vo_products(
3179 p_api_version_number IN NUMBER
3180 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
3181 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
3182 , p_vo_prod_rec IN vo_prod_rec_type
3183 , p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create
3184 , x_return_status OUT NOCOPY VARCHAR2
3185 , x_msg_count OUT NOCOPY NUMBER
3186 , x_msg_data OUT NOCOPY VARCHAR2
3187 )
3188 IS
3189 l_api_name CONSTANT VARCHAR2(30) := 'Validate_vo_products';
3190 l_api_version_number CONSTANT NUMBER := 1.0;
3191 l_object_version_number NUMBER;
3192 l_vo_prod_rec vo_prod_rec_type;
3193 BEGIN
3194 -- Standard Start of API savepoint
3195 SAVEPOINT Validate_vo_products_pvt;
3196
3197 -- Standard call to check for call compatibility.
3198 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3199 p_api_version_number,
3200 l_api_name,
3201 G_PKG_NAME)
3202 THEN
3203 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3204 END IF;
3205 -- Initialize message list if p_init_msg_list is set to TRUE.
3206 IF FND_API.to_Boolean( p_init_msg_list )
3207 THEN
3208 FND_MSG_PUB.initialize;
3209 END IF;
3210
3211 -- Debug Message
3212 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
3213
3214 -- Initialize API return status to SUCCESS
3215 x_return_status := FND_API.G_RET_STS_SUCCESS;
3216
3217 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
3218
3219 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
3220 Check_vo_product_Items(
3221 p_vo_prod_rec => p_vo_prod_rec,
3222 p_validation_mode => p_validation_mode,
3223 x_return_status => x_return_status
3224 );
3225 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3226 RAISE FND_API.G_EXC_ERROR;
3227 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3228 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3229 END IF;
3230 END IF;
3231 -- IF p_validation_mode = JTF_PLSQL_API.g_update THEN
3232 /* Complete_vo_discount_Rec(
3233 p_vo_disc_rec => l_vo_disc_rec,
3234 x_complete_rec => l_vo_disc_rec
3235 );
3236 */
3237 -- END IF;
3238 /* IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
3239 Validate_vo_discounts_rec(
3240 p_api_version_number => 1.0,
3241 p_init_msg_list => FND_API.G_FALSE,
3242 x_return_status => x_return_status,
3243 x_msg_count => x_msg_count,
3244 x_msg_data => x_msg_data,
3245 p_vo_disc_rec => l_vo_disc_rec);
3246
3247 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
3248 RAISE FND_API.G_EXC_ERROR;
3249 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
3250 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3251 END IF;
3252 END IF;
3253 */
3254
3255 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'Return status is : '|| x_return_status);
3256
3257 -- Debug Message
3258 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
3259
3260
3261 -- Standard call to get message count and if count is 1, get message info.
3262 FND_MSG_PUB.Count_And_Get
3263 (p_count => x_msg_count,
3264 p_data => x_msg_data
3265 );
3266 EXCEPTION
3267
3268 WHEN OZF_Utility_PVT.resource_locked THEN
3269 x_return_status := FND_API.g_ret_sts_error;
3270 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
3271
3272 WHEN FND_API.G_EXC_ERROR THEN
3273 ROLLBACK TO Validate_vo_products_pvt;
3274 x_return_status := FND_API.G_RET_STS_ERROR;
3275 -- Standard call to get message count and if count=1, get the message
3276 FND_MSG_PUB.Count_And_Get (
3277 p_encoded => FND_API.G_FALSE,
3278 p_count => x_msg_count,
3279 p_data => x_msg_data
3280 );
3281
3282 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3283 ROLLBACK TO Validate_vo_products_pvt;
3284 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3285 -- Standard call to get message count and if count=1, get the message
3286 FND_MSG_PUB.Count_And_Get (
3287 p_encoded => FND_API.G_FALSE,
3288 p_count => x_msg_count,
3289 p_data => x_msg_data
3290 );
3291
3292 WHEN OTHERS THEN
3293 ROLLBACK TO Validate_vo_products_pvt;
3294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3295 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3296 THEN
3297 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3298 END IF;
3299 -- Standard call to get message count and if count=1, get the message
3300 FND_MSG_PUB.Count_And_Get (
3301 p_encoded => FND_API.G_FALSE,
3302 p_count => x_msg_count,
3303 p_data => x_msg_data
3304 );
3305 END Validate_vo_products;
3306
3307 -- ==============================================================================
3308 -- Start of Comments
3309 -- ==============================================================================
3310 -- API Name
3311 -- Create_vo_Product
3312 -- Type
3313 -- Private
3314 -- Pre-Req
3315 -- Parameters
3316 --
3317 -- IN
3318 -- p_api_version_number IN NUMBER
3319 -- p_init_msg_list IN VARCHAR2
3320 -- p_validation_level IN NUMBER
3321 -- p_vo_prod_rec IN vo_prod_rec_type
3322 -- p_validation_mode IN VARCHAR2
3323 --
3324 -- OUT NOCOPY
3325 -- x_return_status OUT NOCOPY VARCHAR2
3326 -- x_msg_count OUT NOCOPY NUMBER
3327 -- x_msg_data OUT NOCOPY VARCHAR2
3328
3329 -- Version : Current version 1.0
3330 --
3331 -- History
3332 -- Mon May 16 2005:5/41 PM RSSHARMA Created
3333 --
3334 -- Description
3335 -- End of Comments
3336 -- ==============================================================================
3337
3338 PROCEDURE Create_vo_Product(
3339 p_api_version_number IN NUMBER,
3340 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3341 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3342 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3343
3344 x_return_status OUT NOCOPY VARCHAR2,
3345 x_msg_count OUT NOCOPY NUMBER,
3346 x_msg_data OUT NOCOPY VARCHAR2,
3347
3348 p_vo_prod_rec IN vo_prod_rec_type ,
3349 x_off_discount_product_id OUT NOCOPY NUMBER
3350 )
3351 IS
3352 l_api_name CONSTANT VARCHAR2(30) := 'Create_vo_Product';
3353 l_api_version_number CONSTANT NUMBER := 1.0;
3354 l_vo_prod_rec vo_prod_rec_type;
3355 l_vo_discount_line_id NUMBER;
3356 l_off_discount_product_id NUMBER;
3357 l_vo_prod_id NUMBER;
3358 l_object_version_number NUMBER;
3359 l_dummy NUMBER;
3360 CURSOR c_id IS
3361 SELECT ozf_offer_discount_products_s.NEXTVAL
3362 FROM dual;
3363
3364 CURSOR c_id_exists (l_id IN NUMBER) IS
3365 SELECT 1
3366 FROM OZF_OFFER_DISCOUNT_PRODUCTS
3367 WHERE OFF_DISCOUNT_PRODUCT_ID = l_id;
3368
3369 BEGIN
3370 --initialize
3371
3372 SAVEPOINT Create_vo_Product_pvt;
3373
3374 -- Standard call to check for call compatibility.
3375 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3376 p_api_version_number,
3377 l_api_name,
3378 G_PKG_NAME)
3379 THEN
3380 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3381 END IF;
3382
3383 -- Initialize message list if p_init_msg_list is set to TRUE.
3384 IF FND_API.to_Boolean( p_init_msg_list )
3385 THEN
3386 FND_MSG_PUB.initialize;
3387 END IF;
3388
3389 -- Debug Message
3390 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
3391
3392 -- Initialize API return status to SUCCESS
3393 x_return_status := FND_API.G_RET_STS_SUCCESS;
3394
3395 -- =========================================================================
3396 -- Validate Environment
3397 -- =========================================================================
3398
3399 IF FND_GLOBAL.USER_ID IS NULL
3400 THEN
3401 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
3402 RAISE FND_API.G_EXC_ERROR;
3403 END IF;
3404
3405 l_vo_prod_rec := p_vo_prod_rec;
3406
3407
3408
3409 IF p_vo_prod_rec.off_discount_product_id IS NULL OR p_vo_prod_rec.off_discount_product_id = FND_API.g_miss_num THEN
3410 LOOP
3411 l_dummy := NULL;
3412 OPEN c_id;
3413 FETCH c_id INTO l_vo_prod_id;
3414 CLOSE c_id;
3415
3416 OPEN c_id_exists(l_vo_prod_id);
3417 FETCH c_id_exists INTO l_dummy;
3418 CLOSE c_id_exists;
3419 EXIT WHEN l_dummy IS NULL;
3420 END LOOP;
3421 ELSE
3422 l_vo_prod_id := p_vo_prod_rec.off_discount_product_id;
3423 END IF;
3424
3425
3426 l_vo_prod_rec.off_discount_product_id := l_vo_prod_id ;
3427
3428 OZF_Offer_Adj_Line_PVT.debug_message('Calling Validate Discounts: Return Status is :' || x_return_status );
3429
3430 -- validate
3431
3432 Validate_vo_products(
3433 p_api_version_number => p_api_version_number
3434 , p_init_msg_list => p_init_msg_list
3435 , p_validation_level => p_validation_level
3436 , p_vo_prod_rec => l_vo_prod_rec
3437 , p_validation_mode => JTF_PLSQL_API.g_create
3438 , x_return_status => x_return_status
3439 , x_msg_count => x_msg_count
3440 , x_msg_data => x_msg_data
3441 );
3442 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3443 RAISE FND_API.G_EXC_ERROR;
3444 END IF;
3445
3446 -- insert
3447 Ozf_Create_Ozf_Prod_Line_Pkg.Insert_Row(
3448 px_off_discount_product_id => l_vo_prod_id,
3449 p_parent_off_disc_prod_id => l_vo_prod_rec.parent_off_disc_prod_id,
3450 p_product_level => l_vo_prod_rec.product_level,
3451 p_product_id => l_vo_prod_rec.product_id,
3452 p_excluder_flag => l_vo_prod_rec.excluder_flag,
3453 p_uom_code => l_vo_prod_rec.uom_code,
3454 p_start_date_active => l_vo_prod_rec.start_date_active,
3455 p_end_date_active => l_vo_prod_rec.end_date_active,
3456 p_offer_discount_line_id => l_vo_prod_rec.offer_discount_line_id,
3457 p_offer_id => l_vo_prod_rec.offer_id,
3458 p_creation_date => SYSDATE,
3459 p_created_by => FND_GLOBAL.USER_ID,
3460 p_last_update_date => SYSDATE,
3461 p_last_updated_by => FND_GLOBAL.USER_ID,
3462 p_last_update_login => FND_GLOBAL.conc_login_id,
3463 p_product_context => l_vo_prod_rec.product_context,
3464 p_product_attribute => l_vo_prod_rec.product_attribute,
3465 p_product_attr_value => l_vo_prod_rec.product_attr_value,
3466 p_apply_discount_flag => l_vo_prod_rec.apply_discount_flag,
3467 p_include_volume_flag => l_vo_prod_rec.include_volume_flag,
3468 px_object_version_number => l_object_version_number
3469 );
3470
3471 x_off_discount_product_id := l_vo_prod_id;
3472 -- exception
3473 EXCEPTION
3474 WHEN FND_API.G_EXC_ERROR THEN
3475 ROLLBACK TO Create_vo_Product_pvt;
3476 x_return_status := FND_API.G_RET_STS_ERROR;
3477 -- Standard call to get message count and if count=1, get the message
3478 FND_MSG_PUB.Count_And_Get (
3479 p_encoded => FND_API.G_FALSE,
3480 p_count => x_msg_count,
3481 p_data => x_msg_data
3482 );
3483
3484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3485 ROLLBACK TO Create_vo_Product_pvt;
3486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3487 -- Standard call to get message count and if count=1, get the message
3488 FND_MSG_PUB.Count_And_Get (
3489 p_encoded => FND_API.G_FALSE,
3490 p_count => x_msg_count,
3491 p_data => x_msg_data
3492 );
3493
3494 WHEN OTHERS THEN
3495 ROLLBACK TO Create_vo_Product_pvt;
3496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3497 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3498 THEN
3499 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3500 END IF;
3501 -- Standard call to get message count and if count=1, get the message
3502 FND_MSG_PUB.Count_And_Get (
3503 p_encoded => FND_API.G_FALSE,
3504 p_count => x_msg_count,
3505 p_data => x_msg_data
3506 );
3507
3508 END Create_vo_Product;
3509
3510
3511 -- ==============================================================================
3512 -- Start of Comments
3513 -- ==============================================================================
3514 -- API Name
3515 -- Update_vo_Product
3516 -- Type
3517 -- Private
3518 -- Pre-Req
3519 -- Parameters
3520 --
3521 -- IN
3522 -- p_api_version_number IN NUMBER
3523 -- p_init_msg_list IN VARCHAR2
3524 -- p_validation_level IN NUMBER
3525 -- p_vo_prod_rec IN vo_prod_rec_type
3526 -- p_validation_mode IN VARCHAR2
3527 --
3528 -- OUT NOCOPY
3529 -- x_return_status OUT NOCOPY VARCHAR2
3530 -- x_msg_count OUT NOCOPY NUMBER
3531 -- x_msg_data OUT NOCOPY VARCHAR2
3532
3533 -- Version : Current version 1.0
3534 --
3535 -- History
3536 -- Mon May 16 2005:5/41 PM RSSHARMA Created
3537 --
3538 -- Description
3539 -- End of Comments
3540 -- ==============================================================================
3541
3542 PROCEDURE Update_vo_Product(
3543 p_api_version_number IN NUMBER,
3544 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3545 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3546 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3547
3548 x_return_status OUT NOCOPY VARCHAR2,
3549 x_msg_count OUT NOCOPY NUMBER,
3550 x_msg_data OUT NOCOPY VARCHAR2,
3551
3552 p_vo_prod_rec IN vo_prod_rec_type
3553 )
3554 IS
3555 CURSOR c_get_vo_prod_line(p_offer_prod_id NUMBER, p_object_version_number NUMBER) IS
3556 SELECT *
3557 FROM OZF_OFFER_DISCOUNT_PRODUCTS
3558 WHERE OFF_DISCOUNT_PRODUCT_ID = p_offer_prod_id
3559 AND object_version_number = p_object_version_number;
3560 -- Hint: Developer need to provide Where clause
3561
3562 l_api_name CONSTANT VARCHAR2(30) := 'Update_vo_Product';
3563 l_api_version_number CONSTANT NUMBER := 1.0;
3564 -- Local Variables
3565 l_object_version_number NUMBER;
3566 l_offer_prod_id NUMBER;
3567 l_ref_vo_prod_rec c_get_vo_prod_line%ROWTYPE ;
3568 l_tar_vo_prod_rec vo_prod_rec_type := p_vo_prod_rec ;
3569 l_rowid ROWID;
3570
3571 BEGIN
3572 -- iniialize
3573 -- Standard Start of API savepoint
3574 SAVEPOINT update_vo_prod_pvt;
3575 -- Standard call to check for call compatibility.
3576 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3577 p_api_version_number,
3578 l_api_name,
3579 G_PKG_NAME)
3580 THEN
3581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3582 END IF;
3583 -- Initialize message list if p_init_msg_list is set to TRUE.
3584 IF FND_API.to_Boolean( p_init_msg_list )
3585 THEN
3586 FND_MSG_PUB.initialize;
3587 END IF;
3588 -- Debug Message
3589 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
3590
3591 -- Initialize API return status to SUCCESS
3592 x_return_status := FND_API.G_RET_STS_SUCCESS;
3593
3594 -- validate
3595 OZF_Offer_Adj_Line_PVT.debug_message('Private API: - Open Cursor to Select');
3596 OPEN c_get_vo_prod_line( l_tar_vo_prod_rec.OFF_DISCOUNT_PRODUCT_ID,l_tar_vo_prod_rec.object_version_number);
3597 FETCH c_get_vo_prod_line INTO l_ref_vo_prod_rec ;
3598 If ( c_get_vo_prod_line%NOTFOUND) THEN
3599 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET'
3600 , p_token_name => 'INFO'
3601 , p_token_value => 'VO_PRODUCT_LINE') ;
3602 RAISE FND_API.G_EXC_ERROR;
3603 END IF;
3604 CLOSE c_get_vo_prod_line;
3605
3606 If (l_tar_vo_prod_rec.object_version_number is NULL or
3607 l_tar_vo_prod_rec.object_version_number = FND_API.G_MISS_NUM ) Then
3608 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING'
3609 , p_token_name => 'COLUMN'
3610 , p_token_value => 'Last_Update_Date') ;
3611 RAISE FND_API.G_EXC_ERROR;
3612 End if;
3613 -- Check Whether record has been changed by someone else
3614 If (l_tar_vo_prod_rec.object_version_number <> l_ref_vo_prod_rec.object_version_number) Then
3615 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED'
3616 , p_token_name => 'INFO'
3617 , p_token_value => 'VO_PRODUCT_LINE') ;
3618 RAISE FND_API.G_EXC_ERROR;
3619 End if;
3620
3621 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
3622 THEN
3623 -- Debug message
3624 OZF_Offer_Adj_Line_PVT.debug_message('Private API: Validate_vo_discounts');
3625 -- validate data
3626 Validate_vo_products(
3627 p_api_version_number => p_api_version_number
3628 , p_init_msg_list => p_init_msg_list
3629 , p_validation_level => p_validation_level
3630 , p_vo_prod_rec => p_vo_prod_rec
3631 , p_validation_mode => JTF_PLSQL_API.g_update
3632 , x_return_status => x_return_status
3633 , x_msg_count => x_msg_count
3634 , x_msg_data => x_msg_data
3635 );
3636
3637 END IF;
3638
3639 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
3640 RAISE FND_API.G_EXC_ERROR;
3641 END IF;
3642 -- update
3643 OZF_Create_Ozf_Prod_Line_PKG.Update_Row(
3644 p_off_discount_product_id => p_vo_prod_rec.off_discount_product_id,
3645 p_parent_off_disc_prod_id => p_vo_prod_rec.parent_off_disc_prod_id,
3646 p_product_level => p_vo_prod_rec.product_level,
3647 p_product_id => p_vo_prod_rec.product_id,
3648 p_excluder_flag => p_vo_prod_rec.excluder_flag,
3649 p_uom_code => p_vo_prod_rec.uom_code,
3650 p_start_date_active => p_vo_prod_rec.start_date_active,
3651 p_end_date_active => p_vo_prod_rec.end_date_active,
3652 p_offer_discount_line_id => p_vo_prod_rec.offer_discount_line_id,
3653 p_offer_id => p_vo_prod_rec.offer_id,
3654 p_last_update_date => SYSDATE,
3655 p_last_updated_by => FND_GLOBAL.USER_ID,
3656 p_last_update_login => FND_GLOBAL.conc_login_id,
3657 p_product_context => p_vo_prod_rec.product_context,
3658 p_product_attribute => p_vo_prod_rec.product_attribute,
3659 p_product_attr_value => p_vo_prod_rec.product_attr_value,
3660 p_apply_discount_flag => p_vo_prod_rec.apply_discount_flag,
3661 p_include_volume_flag => p_vo_prod_rec.include_volume_flag,
3662 p_object_version_number => p_vo_prod_rec.object_version_number
3663 );
3664 --
3665 -- End of API body.
3666 --
3667 -- Standard check for p_commit
3668 IF FND_API.to_Boolean( p_commit )
3669 THEN
3670 COMMIT WORK;
3671 END IF;
3672 -- Debug Message
3673 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
3674 -- Standard call to get message count and if count is 1, get message info.
3675 FND_MSG_PUB.Count_And_Get
3676 (p_count => x_msg_count,
3677 p_data => x_msg_data
3678 );
3679 -- exception handling
3680 EXCEPTION
3681
3682 WHEN OZF_Utility_PVT.resource_locked THEN
3683 x_return_status := FND_API.g_ret_sts_error;
3684 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
3685
3686 WHEN FND_API.G_EXC_ERROR THEN
3687 ROLLBACK TO update_vo_prod_pvt;
3688 x_return_status := FND_API.G_RET_STS_ERROR;
3689 -- Standard call to get message count and if count=1, get the message
3690 FND_MSG_PUB.Count_And_Get (
3691 p_encoded => FND_API.G_FALSE,
3692 p_count => x_msg_count,
3693 p_data => x_msg_data
3694 );
3695
3696 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3697 ROLLBACK TO update_vo_prod_pvt;
3698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3699 -- Standard call to get message count and if count=1, get the message
3700 FND_MSG_PUB.Count_And_Get (
3701 p_encoded => FND_API.G_FALSE,
3702 p_count => x_msg_count,
3703 p_data => x_msg_data
3704 );
3705
3706 WHEN OTHERS THEN
3707 ROLLBACK TO update_vo_prod_pvt;
3708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3709 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3710 THEN
3711 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3712 END IF;
3713 -- Standard call to get message count and if count=1, get the message
3714 FND_MSG_PUB.Count_And_Get (
3715 p_encoded => FND_API.G_FALSE,
3716 p_count => x_msg_count,
3717 p_data => x_msg_data
3718 );
3719
3720 END Update_vo_Product;
3721
3722
3723 -- ==============================================================================
3724 -- Start of Comments
3725 -- ==============================================================================
3726 -- API Name
3727 -- Delete_vo_Product
3728 -- Type
3729 -- Private
3730 -- Pre-Req
3731 -- Parameters
3732 --
3733 -- IN
3734 -- p_api_version_number IN NUMBER
3735 -- p_init_msg_list IN VARCHAR2
3736 -- p_commit IN VARCHAR2
3737 -- p_validation_level IN NUMBER
3738 -- p_off_discount_product_id IN NUMBER
3739 -- p_object_version_number IN NUMBER
3740
3741 --
3742 -- OUT NOCOPY
3743 -- x_return_status OUT NOCOPY VARCHAR2
3744 -- x_msg_count OUT NOCOPY NUMBER
3745 -- x_msg_data OUT NOCOPY VARCHAR2
3746
3747 -- Version : Current version 1.0
3748 --
3749 -- History
3750 -- Mon May 16 2005:5/41 PM RSSHARMA Created
3751 --
3752 -- Description
3753 -- End of Comments
3754 -- ==============================================================================
3755
3756 PROCEDURE Delete_vo_Product(
3757 p_api_version_number IN NUMBER,
3758 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
3759 p_commit IN VARCHAR2 := FND_API.G_FALSE,
3760 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
3761 x_return_status OUT NOCOPY VARCHAR2,
3762 x_msg_count OUT NOCOPY NUMBER,
3763 x_msg_data OUT NOCOPY VARCHAR2,
3764 p_off_discount_product_id IN NUMBER,
3765 p_object_version_number IN NUMBER
3766 )
3767 IS
3768 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_vo_Product';
3769 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
3770 l_object_version_number NUMBER;
3771 BEGIN
3772 -- Standard Start of API savepoint
3773 SAVEPOINT Delete_vo_Product_PVT;
3774
3775 -- Standard call to check for call compatibility.
3776 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
3777 p_api_version_number,
3778 l_api_name,
3779 G_PKG_NAME)
3780 THEN
3781 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3782 END IF;
3783
3784 -- Initialize message list if p_init_msg_list is set to TRUE.
3785 IF FND_API.to_Boolean( p_init_msg_list )
3786 THEN
3787 FND_MSG_PUB.initialize;
3788 END IF;
3789
3790 -- Initialize API return status to SUCCESS
3791 x_return_status := FND_API.G_RET_STS_SUCCESS;
3792
3793 --
3794 -- Api body
3795 --
3796
3797 -- Invoke table handler(OZF_Promotional_Offers_PKG.Delete_Row)
3798 OZF_Create_Ozf_Prod_Line_PKG.Delete_row(
3799 p_off_discount_product_id => p_off_discount_product_id
3800 , p_object_version_number => p_object_version_number
3801 );
3802
3803
3804 --
3805 -- End of API body
3806 --
3807
3808 -- Standard check for p_commit
3809 IF FND_API.to_Boolean( p_commit )
3810 THEN
3811 COMMIT WORK;
3812 END IF;
3813
3814 -- Standard call to get message count and if count is 1, get message info.
3815 FND_MSG_PUB.Count_And_Get
3816 (p_count => x_msg_count,
3817 p_data => x_msg_data
3818 );
3819 EXCEPTION
3820
3821 WHEN OZF_Utility_PVT.resource_locked THEN
3822 x_return_status := FND_API.g_ret_sts_error;
3823 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
3824
3825 WHEN FND_API.G_EXC_ERROR THEN
3826 ROLLBACK TO Delete_vo_Product_PVT;
3827 x_return_status := FND_API.G_RET_STS_ERROR;
3828 -- Standard call to get message count and if count=1, get the message
3829 FND_MSG_PUB.Count_And_Get (
3830 p_encoded => FND_API.G_FALSE,
3831 p_count => x_msg_count,
3832 p_data => x_msg_data
3833 );
3834
3835 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3836 ROLLBACK TO Delete_vo_Product_PVT;
3837 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3838 -- Standard call to get message count and if count=1, get the message
3839 FND_MSG_PUB.Count_And_Get (
3840 p_encoded => FND_API.G_FALSE,
3841 p_count => x_msg_count,
3842 p_data => x_msg_data
3843 );
3844
3845 WHEN OTHERS THEN
3846 ROLLBACK TO Delete_vo_Product_PVT;
3847 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3848 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3849 THEN
3850 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3851 END IF;
3852 -- Standard call to get message count and if count=1, get the message
3853 FND_MSG_PUB.Count_And_Get (
3854 p_encoded => FND_API.G_FALSE,
3855 p_count => x_msg_count,
3856 p_data => x_msg_data
3857 );
3858
3859 END Delete_vo_Product;
3860 END OZF_Volume_Offer_disc_PVT;