[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_ADJ_LINE_PVT
Source
1 PACKAGE BODY OZF_Offer_Adj_Line_PVT as
2 /* $Header: ozfvoalb.pls 120.8 2006/07/28 13:20:34 gramanat noship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Offer_Adj_Line_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- This Api is generated with Latest version of
14 -- Rosetta, where g_miss indicates NULL and
15 -- NULL indicates missing value. Rosetta Version 1.55
16 -- End of Comments
17 -- Mon Dec 01 2003:7/36 PM RSSHARMA Added new procedure complete_rec_for_create to initialize list_line_id and
18 -- pricing_attribute_id of discount line rec to g_miss. In future all the creation specif initializations
19 -- should go here
20 -- Tue Dec 02 2003:1/49 PM RSSHARMA Added new procedure populate_volume_offer to populate line items
21 -- for a volume offer from tiers table
22 -- Mon Mar 29 2004:2/1 PM RSSHARMA Fixed bug # 3439211. Corrected debug_message
23 -- Thu May 12 2005:3/47 PM RSSHARMA Fixed bug # 4354567.
24 -- Corrected debug_message. Corrected populating list_line_id , arithmetic_operator and operand
25 -- for multi-tier lines
26 -- Wed Jan 11 2006:8/25 PM RSSHARMA Schriber fixes Fixed issue where user could not create an adjustmentline
27 -- if the td_discount was not entered.
28 -- 03/04/2006 rssharma Added discoutnEndDate column in call to insert_row and update_row
29 -- Mon May 22 2006:3/34 PM RSSHARMA Fixed bug # 5239763. Fixed debug message.
30 -- ===============================================================
31
32
33 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Offer_Adj_Line_PVT';
34 G_FILE_NAME CONSTANT VARCHAR2(12) := 'offvadjb.pls';
35 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
36 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
37
38 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
39 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
40 --
41 -- Foreward Procedure Declarations
42 --
43
44 PROCEDURE Default_Offadj_Line_Items (
45 p_offadj_line_rec IN offadj_line_rec_type ,
46 x_offadj_line_rec OUT NOCOPY offadj_line_rec_type
47 ) ;
48
49 --=================================================================================
50 -- Fixed bug # 4354567. Fixed debug_message procedure. the procedure was calling itself
51 -- recursively withOUT any counter or exit condition. This put the procedure in an infinite loop
52 -- and made the transaction hang.
53 --====================================================================================
54 PROCEDURE debug_message(
55 p_message_text IN VARCHAR2
56 )
57 IS
58 BEGIN
59 IF OZF_DEBUG_HIGH_ON THEN
60 OZF_UTILITY_PVT.debug_message(p_message_text);
61 END IF;
62
63 END debug_message;
64
65 PROCEDURE populate_order_value_rec(p_list_line_rec IN offadj_new_line_rec_type,
66 p_offadj_line_rec IN OUT NOCOPY offadj_line_rec_type )
67 IS
68 l_order_value_discount_type ozf_offers.order_value_discount_type%type;
69 CURSOR cur_get_ov_discount_type(p_list_header_id NUMBER) IS
70 SELECT order_value_discount_type
71 FROM ozf_offers
72 WHERE qp_list_header_id = p_list_header_id;
73 BEGIN
74 open cur_get_ov_discount_type(p_list_line_rec.list_header_id);
75 fetch cur_get_ov_discount_type into l_order_value_discount_type;
76 close cur_get_ov_discount_type;
77
78 IF l_order_value_discount_type = 'DIS' THEN
79 p_offadj_line_rec.arithmetic_operator := '%';
80 ELSIF l_order_value_discount_type = 'AMT' THEN
81 p_offadj_line_rec.arithmetic_operator := 'AMT';
82 END IF;
83
84 END populate_order_value_rec;
85 /*
86 This method sets the default values for creating offer discount rules
87 */
88 PROCEDURE complete_rec_for_create(
89 p_modifier_line_rec IN OUT NOCOPY ozf_offer_pvt.Modifier_Line_Rec_Type
90 )
91 IS
92 BEGIN
93 p_modifier_line_rec.list_line_id := FND_API.g_miss_num;
94 p_modifier_line_rec.pricing_attribute_id := FND_API.g_miss_num;
95 END complete_rec_for_create;
96
97
98 PROCEDURE populate_volume_offer(p_list_line_rec IN offadj_new_line_rec_type,
99 p_modifier_line_rec IN OUT NOCOPY ozf_offer_pvt.Modifier_Line_Rec_Type )
100 IS
101 CURSOR c_tier_data (p_list_header_id NUMBER) IS
102 SELECT discount , discount_type_code , uom_code , volume_type , tier_value_from , active,tier_value_to FROM ozf_volume_offer_tiers
103 WHERE qp_list_header_id = p_list_header_id
104 AND tier_value_from = (SELECT min(tier_value_from) FROM ozf_volume_offer_tiers WHERE qp_list_header_id = p_list_header_id ) ;
105
106 l_tier_data c_tier_data%rowtype;
107
108 BEGIN
109 debug_message('@Inside populate Volume Offer');
110 open c_tier_data(p_list_line_rec.list_header_id);
111 fetch c_tier_data into l_tier_data;
112 close c_tier_data;
113
114 p_modifier_line_rec.operand := l_tier_data.discount;
115 p_modifier_line_rec.arithmetic_operator := l_tier_data.discount_type_code;
116 p_modifier_line_rec.pricing_attr := l_tier_data.volume_type;
117 p_modifier_line_rec.pricing_attr_value_from := l_tier_data.tier_value_from;
118 p_modifier_line_rec.pricing_attr_value_to := l_tier_data.tier_value_to;
119 p_modifier_line_rec.product_uom_code := l_tier_data.uom_code;
120 debug_message('@UOmCode is '|| p_modifier_line_rec.product_uom_code);
121
122 END populate_volume_offer;
123
124
125 PROCEDURE populate_new_adj_rec(p_list_line_rec IN offadj_new_line_rec_type,
126 p_modifier_line_rec IN OUT NOCOPY ozf_offer_pvt.Modifier_Line_Rec_Type )
127 IS
128 CURSOR c_offer_type (p_list_header_id IN NUMBER) IS
129 SELECT offer_type
130 FROM ozf_offers
131 WHERE qp_list_header_id = p_list_header_id;
132 l_offer_type OZF_OFFERS.offer_type%type;
133 BEGIN
134 p_modifier_line_rec.LIST_HEADER_ID := p_list_line_rec.list_header_id;
135 p_modifier_line_rec.list_line_id := p_list_line_rec.list_line_id;
136 p_modifier_line_rec.LIST_LINE_TYPE_CODE := p_list_line_rec.list_line_type_code;
137 p_modifier_line_rec.OPERAND := p_list_line_rec.operand;
138 p_modifier_line_rec.ARITHMETIC_OPERATOR := p_list_line_rec.arithmetic_operator;
139 p_modifier_line_rec.INACTIVE_FLAG:= 'N';
140 p_modifier_line_rec.PRODUCT_ATTR := p_list_line_rec.product_attr;
141 p_modifier_line_rec.PRODUCT_ATTR_VAL := p_list_line_rec.product_attr_val;
142 p_modifier_line_rec.PRODUCT_UOM_CODE := p_list_line_rec.product_uom_code;
143
144 -- note pricing_attribute_id is necessary for updates on pricing_attributes
145 p_modifier_line_rec.pricing_attribute_id := p_list_line_rec.pricing_attribute_id;
146 p_modifier_line_rec.PRICING_ATTR := p_list_line_rec.pricing_attr;
147 p_modifier_line_rec.PRICING_ATTR_VALUE_FROM := p_list_line_rec.pricing_attr_value_from;
148 --p_modifier_line_rec.PRICING_ATTR_VALUE_TO := p_list_line_rec.pricing_attr_value_to;
149 p_modifier_line_rec.EXCLUDER_FLAG := 'N';
150
151 debug_message('list_line_id is '||p_modifier_line_rec.list_line_id);
152 debug_message('LIST_LINE_TYPE_CODE is '||p_modifier_line_rec.LIST_LINE_TYPE_CODE);
153 debug_message('OPERAND is '||p_modifier_line_rec.OPERAND);
154 debug_message('START_DATE_ACTIVE is '||p_modifier_line_rec.START_DATE_ACTIVE);
155 debug_message('ARITHMETIC_OPERATOR is '||p_modifier_line_rec.ARITHMETIC_OPERATOR);
156 debug_message('lsit header id is '||p_modifier_line_rec.list_header_id);
157 debug_message('pricing_attribute is '||p_modifier_line_rec.PRICING_ATTR );
158 debug_message('pricing_attribute_id is '||p_modifier_line_rec.pricing_attribute_id);
159 debug_message('pricing_attr_value_from is '||p_modifier_line_rec.PRICING_ATTR_VALUE_FROM );
160
161 /*debug_message('list_line_id is '||p_modifier_line_rec.list_line_id);
162 debug_message('list_line_id is '||p_modifier_line_rec.list_line_id);
163 */
164
165
166 p_modifier_line_rec.ORDER_VALUE_FROM := p_list_line_rec.order_value_from;
167 p_modifier_line_rec.ORDER_VALUE_TO := p_list_line_rec.order_value_to;
168 p_modifier_line_rec.QUALIFIER_ID := p_list_line_rec.qualifier_id;
169
170
171 p_modifier_line_rec.QD_OPERAND:= Fnd_Api.g_miss_num;
172 p_modifier_line_rec.QD_ARITHMETIC_OPERATOR:= Fnd_Api.g_miss_char;
173 p_modifier_line_rec.QD_RELATED_DEAL_LINES_ID:= Fnd_Api.g_miss_num;
174 p_modifier_line_rec.QD_OBJECT_VERSION_NUMBER:= Fnd_Api.g_miss_num;
175 p_modifier_line_rec.QD_ESTIMATED_QTY_IS_MAX:= Fnd_Api.g_miss_char;
176 p_modifier_line_rec.QD_LIST_LINE_ID := Fnd_Api.g_miss_num;
177 p_modifier_line_rec.QD_ESTIMATED_AMOUNT_IS_MAX := Fnd_Api.g_miss_char;
178 p_modifier_line_rec.ESTIM_GL_VALUE := Fnd_Api.g_miss_num;
179 p_modifier_line_rec.BENEFIT_PRICE_LIST_LINE_ID := p_list_line_rec.BENEFIT_PRICE_LIST_LINE_ID;
180 p_modifier_line_rec.BENEFIT_LIMIT := Fnd_Api.g_miss_num;
181 p_modifier_line_rec.BENEFIT_QTY := p_list_line_rec.BENEFIT_QTY;
182 p_modifier_line_rec.BENEFIT_UOM_CODE := p_list_line_rec.BENEFIT_UOM_CODE;
183 p_modifier_line_rec.SUBSTITUTION_CONTEXT := Fnd_Api.g_miss_char;
184 p_modifier_line_rec.SUBSTITUTION_ATTR := Fnd_Api.g_miss_char;
185 p_modifier_line_rec.SUBSTITUTION_VAL := Fnd_Api.g_miss_char;
186 p_modifier_line_rec.PRICE_BREAK_TYPE_CODE := Fnd_Api.g_miss_char;
187 --p_modifier_line_rec.PRICING_ATTRIBUTE_ID:= Fnd_Api.g_miss_num;
188
189
190 p_modifier_line_rec.COMMENTS := Fnd_Api.g_miss_char;
191 p_modifier_line_rec.CONTEXT := p_list_line_rec.CONTEXT;
192 p_modifier_line_rec.ATTRIBUTE1 := p_list_line_rec.ATTRIBUTE1;
193 p_modifier_line_rec.ATTRIBUTE2 := p_list_line_rec.ATTRIBUTE2;
194 p_modifier_line_rec.ATTRIBUTE3 := p_list_line_rec.ATTRIBUTE3;
195 p_modifier_line_rec.ATTRIBUTE4 := p_list_line_rec.attribute4;
196 p_modifier_line_rec.ATTRIBUTE5 := p_list_line_rec.attribute5;
197 p_modifier_line_rec.ATTRIBUTE6 := p_list_line_rec.attribute6;
198 p_modifier_line_rec.ATTRIBUTE7 := p_list_line_rec.attribute7;
199 p_modifier_line_rec.ATTRIBUTE8 := p_list_line_rec.attribute8;
200 p_modifier_line_rec.ATTRIBUTE9 := p_list_line_rec.attribute9;
201 p_modifier_line_rec.ATTRIBUTE10:= p_list_line_rec.attribute10;
202 p_modifier_line_rec.ATTRIBUTE11:= p_list_line_rec.attribute11;
203 p_modifier_line_rec.ATTRIBUTE12:= p_list_line_rec.attribute12;
204 p_modifier_line_rec.ATTRIBUTE13:= p_list_line_rec.attribute13;
205 p_modifier_line_rec.ATTRIBUTE14:= p_list_line_rec.attribute14;
206 p_modifier_line_rec.ATTRIBUTE15:= p_list_line_rec.attribute15;
207 p_modifier_line_rec.MAX_QTY_PER_ORDER:= Fnd_Api.g_miss_num;
208 p_modifier_line_rec.MAX_QTY_PER_ORDER_ID := Fnd_Api.g_miss_num;
209 p_modifier_line_rec.MAX_QTY_PER_CUSTOMER := Fnd_Api.g_miss_num;
210 p_modifier_line_rec.MAX_QTY_PER_CUSTOMER_ID := Fnd_Api.g_miss_num;
211 p_modifier_line_rec.MAX_QTY_PER_RULE := Fnd_Api.g_miss_num;
212 p_modifier_line_rec.MAX_QTY_PER_RULE_ID := Fnd_Api.g_miss_num;
213 p_modifier_line_rec.MAX_ORDERS_PER_CUSTOMER := Fnd_Api.g_miss_num;
214 p_modifier_line_rec.MAX_ORDERS_PER_CUSTOMER_ID := Fnd_Api.g_miss_num;
215 p_modifier_line_rec.MAX_AMOUNT_PER_RULE := Fnd_Api.g_miss_num;
216 p_modifier_line_rec.MAX_AMOUNT_PER_RULE_ID := Fnd_Api.g_miss_num;
217 p_modifier_line_rec.ESTIMATE_QTY_UOM := Fnd_Api.g_miss_char;
218 p_modifier_line_rec.generate_using_formula_id := FND_API.G_MISS_NUM;
219 p_modifier_line_rec.price_by_formula_id := FND_API.G_MISS_NUM;
220 p_modifier_line_rec.generate_using_formula := FND_API.G_MISS_CHAR;
221 p_modifier_line_rec.price_by_formula := FND_API.G_MISS_CHAR;
222
223 IF p_list_line_rec.operation = 'CREATE' THEN
224 complete_rec_for_create(p_modifier_line_rec => p_modifier_line_rec);
225 END IF;
226 open c_offer_type (p_list_line_rec.list_header_id);
227 fetch c_offer_type INTO l_offer_type;
228 close c_offer_type;
229
230 debug_message('@Offer Type is :'||l_offer_type );
231 IF l_offer_type = 'VOLUME_OFFER' THEN
232 populate_volume_offer(p_modifier_line_rec => p_modifier_line_rec
233 ,p_list_line_rec => p_list_line_rec);
234 END IF;
235
236 END populate_new_adj_rec;
237
238
239
240
241 PROCEDURE populate_pro_good_get_rec(p_list_line_rec IN offadj_new_line_rec_type,
242 p_modifier_line_rec IN OUT NOCOPY ozf_offer_pvt.Modifier_Line_Rec_Type )
243 IS
244 BEGIN
245 P_modifier_line_rec.list_line_type_code := 'DIS';
246 p_modifier_line_rec.list_line_id := FND_API.G_MISS_NUM;
247 p_modifier_line_rec.pricing_attribute_id := FND_API.G_MISS_NUM;
248 p_modifier_line_rec.product_attr := 'PRICING_ATTRIBUTE1';
249 -- l_orec[i].arithmetic_operator = (String) getAPIParamValue(this, "arithmeticOperator",l_orec[i].arithmetic_operator);
250 END populate_pro_good_get_rec;
251
252 --=================================================================================
253 -- Fixed bug # 4354567. THe procedure was not populating arithmetic operator and operand properly
254 -- for Multi-tier offer lines, since the values are created in the background and not passed in
255 -- the record.. Corrected the procedure to query the values created in the background and then
256 -- populate the record
257 --====================================================================================
258 PROCEDURE populate_adj_rec(p_offadj_line_rec IN OUT NOCOPY offadj_line_rec_type ,p_list_line_rec IN offadj_new_line_rec_type)
259 IS
260 CURSOR c_operator(p_list_line_id number)IS
261 SELECT arithmetic_operator,operand,list_line_type_code FROM qp_list_lines
262 WHERE list_line_id = p_list_line_id;
263 l_operator c_operator%rowtype;
264 BEGIN
265 p_offadj_line_rec.offer_adjustment_id := p_list_line_rec.offer_adjustment_id;
266
267 p_offadj_line_rec.list_header_id := p_list_line_rec.list_header_id;
268 p_offadj_line_rec.last_update_date := sysdate;
269 p_offadj_line_rec.last_updated_by := 1;
270 p_offadj_line_rec.creation_date := sysdate;
271 p_offadj_line_rec.created_by := 1;
272 p_offadj_line_rec.last_update_login := 1;
273 p_offadj_line_rec.quantity := 1;
274 p_offadj_line_rec.created_from_adjustments := 'Y';
275 p_offadj_line_rec.object_version_number := p_list_line_rec.object_version_number;
276
277 /*open cur_get_ov_discount_type(p_list_line_rec.list_header_id);
278 fetch cur_get_ov_discount_type into l_order_value_discount_type;
279 close cur_get_ov_discount_type;
280 */
281 debug_message('LIST_LINE_TYPE_CODE IS : '||p_list_line_rec.list_line_type_code);
282 IF p_list_line_rec.list_line_type_code = 'DIS' THEN
283 p_offadj_line_rec.arithmetic_operator := p_list_line_rec.arithmetic_operator;
284 p_offadj_line_rec.modified_discount := p_list_line_rec.operand;
285 p_offadj_line_rec.original_discount := p_list_line_rec.operand-1;
286 ELSIF p_list_line_rec.list_line_type_code = 'PBH' THEN
287 OPEN c_operator(p_offadj_line_rec.list_line_id);
288 FETCH c_operator into l_operator;
289 debug_message('ListLIneTypecode is '||l_operator.list_line_type_code||' : '|| 'operator is '||l_operator.arithmetic_operator);
290 p_offadj_line_rec.arithmetic_operator := l_operator.arithmetic_operator;
291 p_offadj_line_rec.modified_discount := l_operator.operand;
292 p_offadj_line_rec.original_discount := l_operator.operand-1;
293 CLOSE c_operator;
294 END IF;
295
296 END populate_adj_rec;
297
298
299 -- Hint: Primary key needs to be returned.
300 -- ==============================================================================
301 -- Start of Comments
302 -- ==============================================================================
303 -- API Name
304 -- Create_Offer_Adj_Line
305 -- Type
306 -- Private
307 -- Pre-Req
308 --
309 -- Parameters
310 --
311 -- IN
312 -- p_api_version_number IN NUMBER Required
313 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
314 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
315 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
316 -- p_offadj_line_rec IN offadj_line_rec_type Required
317 --
318 -- OUT
319 -- x_return_status OUT VARCHAR2
320 -- x_msg_count OUT NUMBER
321 -- x_msg_data OUT VARCHAR2
322 -- Version : Current version 1.0
323 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
324 -- and basic operation, developer must manually add parameters and business logic as necessary.
325 --
326 -- History
327 --
328 -- NOTE
329 --
330 -- End of Comments
331 -- ==============================================================================
332
333 PROCEDURE Create_Offer_Adj_Line(
334 p_api_version_number IN NUMBER,
335 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
336 p_commit IN VARCHAR2 := FND_API.G_FALSE,
337 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
338
339 x_return_status OUT NOCOPY VARCHAR2,
340 x_msg_count OUT NOCOPY NUMBER,
341 x_msg_data OUT NOCOPY VARCHAR2,
342
343 p_offadj_line_rec IN offadj_line_rec_type := g_miss_offadj_line_rec,
344 x_offer_adjustment_line_id OUT NOCOPY NUMBER
345 )
346
347 IS
348 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Offer_Adj_Line';
349 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
350 l_return_status_full VARCHAR2(1);
351 l_object_version_number NUMBER := 1;
352 l_org_id NUMBER := FND_API.G_MISS_NUM;
353 l_offer_adjustment_line_id NUMBER;
354 l_dummy NUMBER;
355 CURSOR c_id IS
356 SELECT ozf_offer_adjustment_lines_s.NEXTVAL
357 FROM dual;
358
359 CURSOR c_id_exists (l_id IN NUMBER) IS
360 SELECT 1
361 FROM ozf_OFFER_ADJUSTMENT_LINES
362 WHERE offer_adjustment_line_id = l_id;
363 BEGIN
364 -- Standard Start of API savepoint
365 SAVEPOINT create_offer_adj_line_pvt;
366
367 -- Standard call to check for call compatibility.
368 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
369 p_api_version_number,
370 l_api_name,
371 G_PKG_NAME)
372 THEN
373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
374 END IF;
375
376
377 -- Initialize message list if p_init_msg_list is set to TRUE.
378 IF FND_API.to_Boolean( p_init_msg_list )
379 THEN
380 FND_MSG_PUB.initialize;
381 END IF;
382
383
384
385 -- Debug Message
386 debug_message('Private API: ' || l_api_name || 'start');
387
388
389
390 -- Initialize API return status to SUCCESS
391 x_return_status := FND_API.G_RET_STS_SUCCESS;
392
393
394 -- Local variable initialization
395
396 IF p_offadj_line_rec.offer_adjustment_line_id IS NULL OR p_offadj_line_rec.offer_adjustment_line_id = FND_API.g_miss_num THEN
397 LOOP
398 l_dummy := NULL;
399 OPEN c_id;
400 FETCH c_id INTO l_offer_adjustment_line_id;
401 CLOSE c_id;
402 debug_message('Inside line id null'||l_offer_adjustment_line_id);
403 OPEN c_id_exists(l_offer_adjustment_line_id);
404 FETCH c_id_exists INTO l_dummy;
405 CLOSE c_id_exists;
406 EXIT WHEN l_dummy IS NULL;
407 END LOOP;
408 -- p_offadj_line_rec.offer_adjustment_line_id := l_offer_adjustment_line_id;
409 ELSE
410 l_offer_adjustment_line_id := p_offadj_line_rec.offer_adjustment_line_id;
411 END IF;
412
413 -- =========================================================================
414 -- Validate Environment
415 -- =========================================================================
416
417 IF FND_GLOBAL.USER_ID IS NULL
418 THEN
419 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
420 RAISE FND_API.G_EXC_ERROR;
421 END IF;
422
423
424
425 --IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
426 --THEN
427 -- Debug message
428 debug_message('Private API: Validate_Offer_Adj_Line');
429
430
431 -- Invoke validation procedures
432 Validate_offer_adj_line(
433 p_api_version_number => 1.0,
434 p_init_msg_list => FND_API.G_FALSE,
435 p_validation_level => p_validation_level,
436 p_validation_mode => JTF_PLSQL_API.g_create,
437 p_offadj_line_rec => p_offadj_line_rec,
438 x_return_status => x_return_status,
439 x_msg_count => x_msg_count,
440 x_msg_data => x_msg_data);
441
442 --END IF;
443
444 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
445 RAISE FND_API.G_EXC_ERROR;
446 END IF;
447
448
449 -- Debug Message
450 debug_message( 'Private API: Calling create table handler');
451
452
453 -- Invoke table handler(Ozf_Offer_Adj_Line_Pkg.Insert_Row)
454 Ozf_Offer_Adj_Line_Pkg.Insert_Row(
455 px_offer_adjustment_line_id => l_offer_adjustment_line_id,
456 p_offer_adjustment_id => p_offadj_line_rec.offer_adjustment_id,
457 p_list_line_id => p_offadj_line_rec.list_line_id,
458 p_arithmetic_operator => p_offadj_line_rec.arithmetic_operator,
459 p_original_discount => p_offadj_line_rec.original_discount,
460 p_modified_discount => p_offadj_line_rec.modified_discount,
461 p_last_update_date => SYSDATE,
462 p_last_updated_by => FND_GLOBAL.USER_ID,
463 p_creation_date => SYSDATE,
464 p_created_by => FND_GLOBAL.USER_ID,
465 p_last_update_login => FND_GLOBAL.conc_login_id,
466 px_object_version_number => l_object_version_number,
467 p_list_header_id => p_offadj_line_rec.list_header_id,
468 p_accrual_flag => p_offadj_line_rec.accrual_flag,
469 p_list_line_id_td => p_offadj_line_rec.list_line_id_td,
470 p_original_discount_td => p_offadj_line_rec.original_discount_td,
471 p_modified_discount_td => p_offadj_line_rec.modified_discount_td,
472 p_quantity => p_offadj_line_rec.quantity,
473 p_created_from_adjustments => p_offadj_line_rec.created_from_adjustments,
474 p_discount_end_date => p_offadj_line_rec.discount_end_date
475 );
476
477 x_offer_adjustment_line_id := l_offer_adjustment_line_id;
478 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
479 RAISE FND_API.G_EXC_ERROR;
480 END IF;
481 --
482 -- End of API body
483 --
484
485 -- Standard check for p_commit
486 IF FND_API.to_Boolean( p_commit )
487 THEN
488 COMMIT WORK;
489 END IF;
490
491
492 -- Debug Message
493 debug_message('Private API: ' || l_api_name || 'end');
494
495
496 -- Standard call to get message count and if count is 1, get message info.
497 FND_MSG_PUB.Count_And_Get
498 (p_count => x_msg_count,
499 p_data => x_msg_data
500 );
501
502 EXCEPTION
503
504 WHEN OZF_Utility_PVT.resource_locked THEN
505 x_return_status := FND_API.g_ret_sts_error;
506 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
507
508 WHEN FND_API.G_EXC_ERROR THEN
509 ROLLBACK TO CREATE_Offer_Adj_Line_PVT;
510 x_return_status := FND_API.G_RET_STS_ERROR;
511 -- Standard call to get message count and if count=1, get the message
512 FND_MSG_PUB.Count_And_Get (
513 p_encoded => FND_API.G_FALSE,
514 p_count => x_msg_count,
515 p_data => x_msg_data
516 );
517
518 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
519 ROLLBACK TO CREATE_Offer_Adj_Line_PVT;
520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
521 -- Standard call to get message count and if count=1, get the message
522 FND_MSG_PUB.Count_And_Get (
523 p_encoded => FND_API.G_FALSE,
524 p_count => x_msg_count,
525 p_data => x_msg_data
526 );
527
528 WHEN OTHERS THEN
529 ROLLBACK TO CREATE_Offer_Adj_Line_PVT;
530 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
531 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
532 THEN
533 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
534 END IF;
535 -- Standard call to get message count and if count=1, get the message
536 FND_MSG_PUB.Count_And_Get (
537 p_encoded => FND_API.G_FALSE,
538 p_count => x_msg_count,
539 p_data => x_msg_data
540 );
541 End Create_Offer_Adj_Line;
542
543
544 --=============================================================================
545 -- Fixed bug # 4354567. THe procedure was not populating list_line_id into properly into adjustment rec
546 -- list list_line_type_code = PBH ie. Multi-tier lines. The procedure was simply getting the first record in
547 -- the table of lines created and assigning the list_line_id to list_line_id in the adjustment rec.
548 -- changed the code to look at list_line_type_code = DIS before assigning the list_line_id to the adjustment rec
549 -- this works OK as only one DIS record is created. For multiple rec this cannot work since only one adjustment
550 -- line is created at a time
551 --======================================================================================
552 PROCEDURE Create_New_Offer_Adj_Line(
553 p_api_version_number IN NUMBER,
554 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
555 p_commit IN VARCHAR2 := FND_API.G_FALSE,
556 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
557
558 x_return_status OUT NOCOPY VARCHAR2,
559 x_msg_count OUT NOCOPY NUMBER,
560 x_msg_data OUT NOCOPY VARCHAR2,
561
562 -- p_offadj_line_rec IN offadj_line_rec_type := g_miss_offadj_line_rec,
563 p_list_line_rec IN offadj_new_line_rec_type := g_miss_offadj_new_line_rec,
564 x_offer_adjustment_line_id OUT NOCOPY NUMBER
565 )
566 IS
567 l_api_name CONSTANT VARCHAR2(30) := 'Create_New_Offer_Adj_Line';
568 l_api_version_number CONSTANT NUMBER := 1.0;
569 l_return_status_full VARCHAR2(1);
570 l_object_version_number NUMBER := 1;
571 l_org_id NUMBER := FND_API.G_MISS_NUM;
572 l_offer_adjustment_line_id NUMBER;
573 l_dummy NUMBER;
574 l_offer_type OZF_OFFERS.OFFER_TYPE%TYPE;
575 l_error_loc NUMBER;
576 l_modifier_line_tbl ozf_offer_pvt.MODIFIER_LINE_TBL_TYPE;
577 v_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
578 l_offadj_line_rec offadj_line_rec_type ;
579
580 i number := 1;
581 l_tier_count number := 0;
582 l_list_header_id NUMBER;
583 l_override_flag VARCHAR2(1);
584 l_accrual_flag VARCHAR2(1):= 'N';
585
586 CURSOR c_offer_type (p_list_header_id IN NUMBER) IS
587 SELECT offer_type
588 FROM ozf_offers
589 WHERE qp_list_header_id = p_list_header_id;
590
591 CURSOR cur_get_ov_discount_type(p_list_header_id NUMBER) IS
592 SELECT order_value_discount_type
593 FROM ozf_offers
594 WHERE qp_list_header_id = p_list_header_id;
595
596 BEGIN
597 -- Standard Start of API savepoint
598 SAVEPOINT create_new_offer_adj_line_pvt;
599
600 -- Standard call to check for call compatibility.
601 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
602 p_api_version_number,
603 l_api_name,
604 G_PKG_NAME)
605 THEN
606 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
607 END IF;
608
609
610 -- Initialize message list if p_init_msg_list is set to TRUE.
611 IF FND_API.to_Boolean( p_init_msg_list )
612 THEN
613 FND_MSG_PUB.initialize;
614 END IF;
615
616
617
618 -- Debug Message
619 debug_message('Private API: ' || l_api_name || 'start');
620
621
622
623 -- Initialize API return status to SUCCESS
624 x_return_status := FND_API.G_RET_STS_SUCCESS;
625
626 --CREATE NEW LIST LINE IN QP
627 /*
628 INactive flag not working right now.
629 Ask Jun to open up automatic flag
630 */
631 --===========================================================
632 --Populate record for Creating new LIne in QP
633 --===========================================================
634 l_modifier_line_tbl(i).operation := 'CREATE';
635 --l_modifier_line_tbl(i).list_line_id := Fnd_Api.g_miss_num
636 populate_new_adj_rec(p_modifier_line_rec => l_modifier_line_tbl(i)
637 , p_list_line_rec => p_list_line_rec);
638
639 open c_offer_type(p_list_line_rec.list_header_id);
640 fetch c_offer_type into l_offer_type;
641 close c_offer_type;
642
643 IF l_offer_type = 'ACCRUAL' THEN
644 l_accrual_flag := 'Y';
645 END IF;
646
647 --===============================================================
648 -- Populate values for promotional goods offer
649 --===============================================================
650 IF l_offer_type = 'OID' AND l_modifier_line_tbl(i).list_line_type_code = 'DIS' THEN
651 populate_pro_good_get_rec(p_modifier_line_rec => l_modifier_line_tbl(i)
652 ,p_list_line_rec => p_list_line_rec);
653 END IF;
654
655
656 debug_message('Return status is '||x_return_status);
657
658 debug_message('Calling process qp list lines');
659
660 debug_message('list line type code : '|| l_modifier_line_tbl(i).list_line_type_code);
661 debug_message('list_line_id is : '||l_modifier_line_tbl(i).list_line_id);
662 debug_message('pricing attribute id is :'||l_modifier_line_tbl(i).pricing_attribute_id);
663 debug_message('product attr is :'||l_modifier_line_tbl(i).product_attr);
664 debug_message('benefit price list id is : '||l_modifier_line_tbl(i).BENEFIT_PRICE_LIST_LINE_ID);
665 debug_message('benefit qty is :'||l_modifier_line_tbl(i).BENEFIT_QTY );
666 debug_message('benefit uom code is :'||l_modifier_line_tbl(i).BENEFIT_UOM_CODE);
667 debug_message('uom code is :'||l_modifier_line_tbl(i).product_uom_code);
668 --================================================================
669 -- create discount rule in qp
670 --================================================================
671 ozf_offer_pvt.process_qp_list_lines(
672 x_return_status => x_return_status,
673 x_msg_count => x_msg_count,
674 x_msg_data => x_msg_data,
675 p_offer_type => l_offer_type,
676 p_modifier_line_tbl => l_modifier_line_tbl,
677 p_list_header_id => p_list_line_rec.list_header_id,
678 x_modifier_line_tbl => v_modifiers_tbl,
679 x_error_location => l_error_loc
680 );
681
682 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
683 RAISE FND_API.G_EXC_ERROR;
684 END IF;
685
686 debug_message('Return status is '||x_return_status);
687 -- create a new adjustment line in ozf_offer_adjustments with created from ADJUSTMENTS ='Y'
688 debug_message('returned table size is '||v_modifiers_tbl.count);
689 FOR k IN v_modifiers_tbl.first..v_modifiers_tbl.last LOOP
690 IF (v_modifiers_tbl(k).list_line_type_code = 'DIS') THEN
691 debug_message('LINE : '|| k || ' list_line_id : '||v_modifiers_tbl(k).list_line_id || ' : '||v_modifiers_tbl(k).list_line_type_code);
692 l_offadj_line_rec.list_line_id := v_modifiers_tbl(k).list_line_id;
693 END IF;
694 END LOOP;
695
696
697 --============================================================
698 -- populate adjustment record
699 --============================================================
700 populate_adj_rec(p_offadj_line_rec => l_offadj_line_rec, p_list_line_rec => p_list_line_rec);
701
702 l_offadj_line_rec.accrual_flag := l_accrual_flag;
703
704
705 IF l_offer_type = 'ORDER' THEN
706 populate_order_value_rec (p_list_line_rec => p_list_line_rec,
707 p_offadj_line_rec => l_offadj_line_rec);
708 END IF;
709
710
711 --update qp_list_lines set automatic_flag = 'N' where list_line_id = v_modifiers_tbl(1).list_line_id;
712
713 --===============================================================
714 -- create adjustment line
715 --===============================================================
716 Create_Offer_Adj_Line(
717 p_api_version_number => 1.0,
718 p_init_msg_list => FND_API.G_FALSE,
719 p_commit => FND_API.G_FALSE,
720 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
721 x_return_status =>x_return_status,
722 x_msg_count => x_msg_count,
723 x_msg_data => x_msg_data,
724 p_offadj_line_rec => l_offadj_line_rec,
725 x_offer_adjustment_line_id => l_offer_adjustment_line_id
726 );
727
728 debug_message('New Created adjustment line id is '||l_offer_adjustment_line_id);
729 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
730 RAISE FND_API.G_EXC_ERROR;
731 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
732 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
733 END IF;
734
735 x_offer_adjustment_line_id := l_offer_adjustment_line_id;
736 --
737 -- End of API body
738 --
739
740 -- Standard check for p_commit
741 IF FND_API.to_Boolean( p_commit )
742 THEN
743 COMMIT WORK;
744 END IF;
745
746 -- Debug Message
747 debug_message('Private API: ' || l_api_name || 'end');
748
749
750 -- Standard call to get message count and if count is 1, get message info.
751 FND_MSG_PUB.Count_And_Get
752 (p_count => x_msg_count,
753 p_data => x_msg_data
754 );
755
756 EXCEPTION
757 WHEN OZF_UTILITY_PVT.resource_locked THEN
758 x_return_status := FND_API.g_ret_sts_error;
759 OZF_UTILITY_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
760
761 WHEN FND_API.G_EXC_ERROR THEN
762 ROLLBACK TO CREATE_new_Offer_Adj_Line_PVT;
763 x_return_status := FND_API.G_RET_STS_ERROR;
764 -- Standard call to get message count and if count=1, get the message
765 FND_MSG_PUB.Count_And_Get (
766 p_encoded => FND_API.G_FALSE,
767 p_count => x_msg_count,
768 p_data => x_msg_data
769 );
770
771 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
772 ROLLBACK TO CREATE_New_Offer_Adj_Line_PVT;
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
774 -- Standard call to get message count and if count=1, get the message
775 FND_MSG_PUB.Count_And_Get (
776 p_encoded => FND_API.G_FALSE,
777 p_count => x_msg_count,
778 p_data => x_msg_data
779 );
780
781 WHEN OTHERS THEN
782 ROLLBACK TO CREATE_New_Offer_Adj_Line_PVT;
783 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
784 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
785 THEN
786 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
787 END IF;
788 -- Standard call to get message count and if count=1, get the message
789 FND_MSG_PUB.Count_And_Get (
790 p_encoded => FND_API.G_FALSE,
791 p_count => x_msg_count,
792 p_data => x_msg_data
793 );
794
795 END Create_New_Offer_Adj_Line;
796
797 -- ==============================================================================
798 -- Start of Comments
799 -- ==============================================================================
800 -- API Name
801 -- Update_Offer_Adj_Line
802 -- Type
803 -- Private
804 -- Pre-Req
805 --
806 -- Parameters
807 --
808 -- IN
809 -- p_api_version_number IN NUMBER Required
810 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
811 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
812 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
813 -- p_offadj_line_rec IN offadj_line_rec_type Required
814 --
815 -- OUT
816 -- x_return_status OUT VARCHAR2
817 -- x_msg_count OUT NUMBER
818 -- x_msg_data OUT VARCHAR2
819 -- Version : Current version 1.0
820 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
821 -- and basic operation, developer must manually add parameters and business logic as necessary.
822 --
823 -- History
824 --
825 -- NOTE
826 --
827 -- End of Comments
828 -- ==============================================================================
829
830 PROCEDURE Update_Offer_Adj_Line(
831 p_api_version_number IN NUMBER,
832 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
833 p_commit IN VARCHAR2 := FND_API.G_FALSE,
834 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
835
836 x_return_status OUT NOCOPY VARCHAR2,
837 x_msg_count OUT NOCOPY NUMBER,
838 x_msg_data OUT NOCOPY VARCHAR2,
839
840 p_offadj_line_rec IN offadj_line_rec_type,
841 x_object_version_number OUT NOCOPY NUMBER
842 )
843
844 IS
845
846
847 CURSOR c_get_offer_adj_line(offer_adjustment_line_id NUMBER) IS
848 SELECT *
849 FROM ozf_OFFER_ADJUSTMENT_LINES
850 WHERE offer_adjustment_line_id = p_offadj_line_rec.offer_adjustment_line_id;
851 -- Hint: Developer need to provide Where clause
852
853
854 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Offer_Adj_Line';
855 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
856 -- Local Variables
857 l_object_version_number NUMBER;
858 l_offer_adjustment_line_id NUMBER;
859 l_ref_offadj_line_rec c_get_Offer_Adj_Line%ROWTYPE ;
860 l_tar_offadj_line_rec offadj_line_rec_type := P_offadj_line_rec;
861 l_rowid ROWID;
862
863 BEGIN
864 -- Standard Start of API savepoint
865 SAVEPOINT update_offer_adj_line_pvt;
866
867 -- Standard call to check for call compatibility.
868 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
869 p_api_version_number,
870 l_api_name,
871 G_PKG_NAME)
872 THEN
873 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
874 END IF;
875
876
877 -- Initialize message list if p_init_msg_list is set to TRUE.
878 IF FND_API.to_Boolean( p_init_msg_list )
879 THEN
880 FND_MSG_PUB.initialize;
881 END IF;
882
883
884
885 -- Debug Message
886 debug_message('Private API: ' || l_api_name || 'start');
887
888
889
890 -- Initialize API return status to SUCCESS
891 x_return_status := FND_API.G_RET_STS_SUCCESS;
892
893 -- Debug Message
894 debug_message('Private API: - Open Cursor to Select');
895
896 OPEN c_get_Offer_Adj_Line( l_tar_offadj_line_rec.offer_adjustment_line_id);
897
898 FETCH c_get_Offer_Adj_Line INTO l_ref_offadj_line_rec ;
899
900 If ( c_get_Offer_Adj_Line%NOTFOUND) THEN
901 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
902 p_token_name => 'INFO',
903 p_token_value => 'Offer_Adj_Line') ;
904 RAISE FND_API.G_EXC_ERROR;
905 END IF;
906 -- Debug Message
907 debug_message('Private API: - Close Cursor');
908 CLOSE c_get_Offer_Adj_Line;
909
910
911 If (l_tar_offadj_line_rec.object_version_number is NULL or
912 l_tar_offadj_line_rec.object_version_number = FND_API.G_MISS_NUM ) Then
913 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
914 p_token_name => 'COLUMN',
915 p_token_value => 'Last_Update_Date') ;
916 raise FND_API.G_EXC_ERROR;
917 End if;
918 -- Check Whether record has been changed by someone else
919 If (l_tar_offadj_line_rec.object_version_number <> l_ref_offadj_line_rec.object_version_number) Then
920 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
921 p_token_name => 'INFO',
922 p_token_value => 'Offer_Adj_Line') ;
923 raise FND_API.G_EXC_ERROR;
924 End if;
925
926
927 --IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
928 --THEN
929 -- Debug message
930 debug_message('Private API: Validate_Offer_Adj_Line');
931
932 -- Invoke validation procedures
933 Validate_offer_adj_line(
934 p_api_version_number => 1.0,
935 p_init_msg_list => FND_API.G_FALSE,
936 p_validation_level => p_validation_level,
937 p_validation_mode => JTF_PLSQL_API.g_update,
938 p_offadj_line_rec => p_offadj_line_rec,
939 x_return_status => x_return_status,
940 x_msg_count => x_msg_count,
941 x_msg_data => x_msg_data);
942 --END IF;
943
944
945 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
946 RAISE FND_API.G_EXC_ERROR;
947 END IF;
948
949 -- assigned to obj.ver.num variable which is passed to Update_Row() -- sangara
950 l_object_version_number := p_offadj_line_rec.object_version_number;
951
952 -- Debug Message
953 debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW || 'Private API: Calling update table handler');
954
955 -- Debug Message sangara
956 -- debug_message('OFFER_ADJUSTMENT_LINE_ID:' || p_offadj_line_rec.offer_adjustment_line_id);
957
958 -- Invoke table handler(Ozf_Offer_Adj_Line_Pkg.Update_Row)
959
960 debug_message('b4 update off inv discount='||p_offadj_line_rec.modified_discount);
961 debug_message('b4 update off acc discount='||p_offadj_line_rec.modified_discount_td);
962 -- RAISE FND_API.G_EXC_ERROR;
963 Ozf_Offer_Adj_Line_Pkg.Update_Row(
964 p_offer_adjustment_line_id => p_offadj_line_rec.offer_adjustment_line_id,
965 p_offer_adjustment_id => p_offadj_line_rec.offer_adjustment_id,
966 p_list_line_id => p_offadj_line_rec.list_line_id,
967 p_arithmetic_operator => p_offadj_line_rec.arithmetic_operator,
968 p_original_discount => p_offadj_line_rec.original_discount,
969 p_modified_discount => p_offadj_line_rec.modified_discount,
970 p_last_update_date => SYSDATE,
971 p_last_updated_by => FND_GLOBAL.USER_ID,
972 p_last_update_login => FND_GLOBAL.conc_login_id,
973 px_object_version_number => l_object_version_number,
974 p_list_header_id => p_offadj_line_rec.list_header_id,
975 p_accrual_flag => p_offadj_line_rec.accrual_flag,
976 p_list_line_id_td => p_offadj_line_rec.list_line_id_td,
977 p_original_discount_td => p_offadj_line_rec.original_discount_td,
978 p_modified_discount_td => p_offadj_line_rec.modified_discount_td,
979 p_quantity => p_offadj_line_rec.quantity,
980 p_created_from_adjustments => p_offadj_line_rec.created_from_adjustments,
981 p_discount_end_date => p_offadj_line_rec.discount_end_date
982 );
983 x_object_version_number := l_object_version_number;
984 --
985 -- End of API body.
986 --
987
988 -- Standard check for p_commit
989 IF FND_API.to_Boolean( p_commit )
990 THEN
991 COMMIT WORK;
992 END IF;
993
994
995 -- Debug Message
996 debug_message('Private API: ' || l_api_name || 'end');
997
998
999 -- Standard call to get message count and if count is 1, get message info.
1000 FND_MSG_PUB.Count_And_Get
1001 (p_count => x_msg_count,
1002 p_data => x_msg_data
1003 );
1004 EXCEPTION
1005
1006 WHEN OZF_Utility_PVT.resource_locked THEN
1007 x_return_status := FND_API.g_ret_sts_error;
1008 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1009
1010 WHEN FND_API.G_EXC_ERROR THEN
1011 ROLLBACK TO UPDATE_Offer_Adj_Line_PVT;
1012 x_return_status := FND_API.G_RET_STS_ERROR;
1013 -- Standard call to get message count and if count=1, get the message
1014 FND_MSG_PUB.Count_And_Get (
1015 p_encoded => FND_API.G_FALSE,
1016 p_count => x_msg_count,
1017 p_data => x_msg_data
1018 );
1019
1020 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1021 ROLLBACK TO UPDATE_Offer_Adj_Line_PVT;
1022 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1023 -- Standard call to get message count and if count=1, get the message
1024 FND_MSG_PUB.Count_And_Get (
1025 p_encoded => FND_API.G_FALSE,
1026 p_count => x_msg_count,
1027 p_data => x_msg_data
1028 );
1029
1030 WHEN OTHERS THEN
1031 ROLLBACK TO UPDATE_Offer_Adj_Line_PVT;
1032 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1033 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1034 THEN
1035 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1036 END IF;
1037 -- Standard call to get message count and if count=1, get the message
1038 FND_MSG_PUB.Count_And_Get (
1039 p_encoded => FND_API.G_FALSE,
1040 p_count => x_msg_count,
1041 p_data => x_msg_data
1042 );
1043 End Update_Offer_Adj_Line;
1044
1045
1046
1047 -- ==============================================================================
1048 -- Start of Comments
1049 -- ==============================================================================
1050 -- API Name
1051 -- Update_New_Offer_Adj_Line
1052 -- Type
1053 -- Private
1054 -- Pre-Req
1055 --
1056 -- Parameters
1057 --
1058 -- IN
1059 -- p_api_version_number IN NUMBER Required
1060 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1061 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1062 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1063 -- p_list_line_rec IN offadj_new_line_rec_type Required
1064 --
1065 -- OUT
1066 -- x_return_status OUT VARCHAR2
1067 -- x_msg_count OUT NUMBER
1068 -- x_msg_data OUT VARCHAR2
1069 -- Version : Current version 1.0
1070 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
1071 -- and basic operation, developer must manually add parameters and business logic as necessary.
1072 --
1073 -- History
1074 --
1075 -- NOTE
1076 --
1077 -- End of Comments
1078 -- Procedure updated discount lines created from adjustments.
1079 -- These new Discount rules will be created in QP with end date active already passed ie. sysdate -1 so that the rule is not active
1080 -- additionally for further safety the lines will be created with automatic flag = 'N'
1081 -- Since there is some diffeculty in making updates to qp_list_lines when automatic_flag = 'N'
1082 -- the API first updates the line and makes automatic_flag = 'Y', then executes the updates. then again makes automatic_flag = 'N'
1083 -- when the adjustment goes active the Adjustment line is made automatic.
1084 -- ==============================================================================
1085
1086 PROCEDURE Update_New_Offer_Adj_Line(
1087 p_api_version_number IN NUMBER,
1088 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1089 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1090 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1091
1092 x_return_status OUT NOCOPY VARCHAR2,
1093 x_msg_count OUT NOCOPY NUMBER,
1094 x_msg_data OUT NOCOPY VARCHAR2,
1095
1096 -- p_offadj_line_rec IN offadj_line_rec_type,
1097 p_list_line_rec IN offadj_new_line_rec_type,
1098 x_object_version_number OUT NOCOPY NUMBER
1099 )
1100 IS
1101 l_api_name CONSTANT VARCHAR2(30) := 'Update_New_Offer_Adj_Line';
1102 l_api_version_number CONSTANT NUMBER := 1.0;
1103 l_return_status_full VARCHAR2(1);
1104 l_object_version_number NUMBER := 1;
1105 l_org_id NUMBER := FND_API.G_MISS_NUM;
1106 l_offer_adjustment_line_id NUMBER;
1107 l_dummy NUMBER;
1108 l_offer_type OZF_OFFERS.OFFER_TYPE%TYPE;
1109 l_error_loc NUMBER;
1110
1111 l_modifier_line_tbl ozf_offer_pvt.MODIFIER_LINE_TBL_TYPE;
1112 v_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
1113 l_offadj_line_rec offadj_line_rec_type ;
1114
1115 i number := 1;
1116 l_tier_count number := 0;
1117 l_list_header_id NUMBER;
1118 l_override_flag VARCHAR2(1);
1119 l_accrual_flag VARCHAR2(1):= 'N';
1120
1121 CURSOR c_offer_type (p_list_header_id IN NUMBER) IS
1122 SELECT offer_type
1123 FROM ozf_offers
1124 WHERE qp_list_header_id = p_list_header_id;
1125
1126
1127 BEGIN
1128 -- Standard Start of API savepoint
1129 SAVEPOINT update_new_offer_adj_line_pvt;
1130
1131 -- Standard call to check for call compatibility.
1132 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1133 p_api_version_number,
1134 l_api_name,
1135 G_PKG_NAME)
1136 THEN
1137 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1138 END IF;
1139
1140
1141 -- Initialize message list if p_init_msg_list is set to TRUE.
1142 IF FND_API.to_Boolean( p_init_msg_list )
1143 THEN
1144 FND_MSG_PUB.initialize;
1145 END IF;
1146
1147 -- Debug Message
1148 debug_message('Private API: ' || l_api_name || 'start');
1149
1150 -- Initialize API return status to SUCCESS
1151 x_return_status := FND_API.G_RET_STS_SUCCESS;
1152
1153 --UPDATE LIST LINE IN QP
1154 /*
1155 INactive flag not working right now.
1156 Ask Jun to open up automatic flag
1157 */
1158 --======================================================================
1159 -- populate record for creating discount line in qp
1160 --======================================================================
1161 l_modifier_line_tbl(i).operation := 'UPDATE';
1162
1163 populate_new_adj_rec(p_modifier_line_rec => l_modifier_line_tbl(i)
1164 , p_list_line_rec => p_list_line_rec);
1165 open c_offer_type(p_list_line_rec.list_header_id);
1166 fetch c_offer_type into l_offer_type;
1167 close c_offer_type;
1168
1169 IF l_offer_type = 'ACCRUAL' THEN
1170 l_accrual_flag := 'Y';
1171 END IF;
1172
1173 /*
1174 Update qp_list_lines with the entered information
1175 */
1176 -- flipping automatic flag to active coz the update does not go thru, if the automatic fLAG is N
1177 --update qp_list_lines set automatic_flag = 'Y' where list_line_id = p_list_line_rec.list_line_id;
1178
1179 debug_message('Calling process qp_list lines');
1180
1181 --==========================================================================
1182 -- create discount rule in qp
1183 --==========================================================================
1184 ozf_offer_pvt.process_qp_list_lines(
1185 x_return_status => x_return_status,
1186 x_msg_count => x_msg_count,
1187 x_msg_data => x_msg_data,
1188 p_offer_type => l_offer_type,
1189 p_modifier_line_tbl => l_modifier_line_tbl,
1190 p_list_header_id => p_list_line_rec.list_header_id,
1191 x_modifier_line_tbl => v_modifiers_tbl,
1192 x_error_location => l_error_loc
1193 );
1194
1195 -- RAISE FND_API.G_EXC_ERROR;
1196 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1197 RAISE FND_API.G_EXC_ERROR;
1198 END IF;
1199
1200 -- flipping automatic flag back to N
1201 --update qp_list_lines set automatic_flag = 'N' where list_line_id = p_list_line_rec.list_line_id;
1202
1203
1204 -- create a new adjustment line in ozf_offer_adjustments with created from ADJUSTMENTS ='Y'
1205
1206 /*
1207 update adjustment_lines with entered information
1208 */
1209 -- initialize update recird
1210 --==========================================================================
1211 -- populate adjustment record
1212 --==========================================================================
1213 l_offadj_line_rec.list_line_id := p_list_line_rec.list_line_id;
1214 l_offadj_line_rec.offer_adjustment_line_id := p_list_line_rec.offer_adjustment_line_id;
1215
1216 populate_adj_rec(p_offadj_line_rec => l_offadj_line_rec,p_list_line_rec => p_list_line_rec);
1217
1218 l_offadj_line_rec.accrual_flag := l_accrual_flag;
1219
1220 --============================================================================
1221 -- Populate order valie records
1222 --============================================================================
1223 IF l_offer_type = 'ORDER' THEN
1224 debug_message('CALLING populate order value rec');
1225 populate_order_value_rec (p_list_line_rec => p_list_line_rec,
1226 p_offadj_line_rec => l_offadj_line_rec);
1227 END IF;
1228
1229 --============================================================================
1230 -- update qp list line
1231 --============================================================================
1232 -- call update api
1233 Update_Offer_Adj_Line(
1234 p_api_version_number => 1.0 ,
1235 p_init_msg_list => FND_API.G_TRUE,
1236 p_commit => FND_API.G_FALSE,
1237 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1238 x_return_status => x_return_status,
1239 x_msg_count => x_msg_count,
1240 x_msg_data => x_msg_data,
1241 p_offadj_line_rec =>l_offadj_line_rec,
1242 x_object_version_number => l_object_version_number
1243 );
1244 -- l_offadj_line_rec.created_from_adjustments := 'Y';
1245
1246 --update qp_list_lines set automatic_flag = 'N' where list_line_id = v_modifiers_tbl(1).list_line_id;
1247
1248 debug_message('Updated adjustment line id is '||l_offer_adjustment_line_id);
1249 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1250 RAISE FND_API.G_EXC_ERROR;
1251 END IF;
1252 --
1253 -- End of API body
1254 --
1255
1256 -- Standard check for p_commit
1257 IF FND_API.to_Boolean( p_commit )
1258 THEN
1259 COMMIT WORK;
1260 END IF;
1261
1262 -- Debug Message
1263 debug_message('Private API: ' || l_api_name || 'end');
1264
1265
1266 -- Standard call to get message count and if count is 1, get message info.
1267 FND_MSG_PUB.Count_And_Get
1268 (p_count => x_msg_count,
1269 p_data => x_msg_data
1270 );
1271
1272 EXCEPTION
1273 WHEN OZF_UTILITY_PVT.resource_locked THEN
1274 x_return_status := FND_API.g_ret_sts_error;
1275 OZF_UTILITY_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1276
1277 WHEN FND_API.G_EXC_ERROR THEN
1278 ROLLBACK TO Update_new_Offer_Adj_Line_PVT;
1279 x_return_status := FND_API.G_RET_STS_ERROR;
1280 -- Standard call to get message count and if count=1, get the message
1281 FND_MSG_PUB.Count_And_Get (
1282 p_encoded => FND_API.G_FALSE,
1283 p_count => x_msg_count,
1284 p_data => x_msg_data
1285 );
1286
1287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1288 ROLLBACK TO Update_New_Offer_Adj_Line_PVT;
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1290 -- Standard call to get message count and if count=1, get the message
1291 FND_MSG_PUB.Count_And_Get (
1292 p_encoded => FND_API.G_FALSE,
1293 p_count => x_msg_count,
1294 p_data => x_msg_data
1295 );
1296
1297 WHEN OTHERS THEN
1298 ROLLBACK TO Update_New_Offer_Adj_Line_PVT;
1299 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1300 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1301 THEN
1302 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1303 END IF;
1304 -- Standard call to get message count and if count=1, get the message
1305 FND_MSG_PUB.Count_And_Get (
1306 p_encoded => FND_API.G_FALSE,
1307 p_count => x_msg_count,
1308 p_data => x_msg_data
1309 );
1310
1311 END;
1312
1313
1314 -- ==============================================================================
1315 -- Start of Comments
1316 -- ==============================================================================
1317 -- API Name
1318 -- Delete_Offer_Adj_Line
1319 -- Type
1320 -- Private
1321 -- Pre-Req
1322 --
1323 -- Parameters
1324 --
1325 -- IN
1326 -- p_api_version_number IN NUMBER Required
1327 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1328 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1329 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1330 -- p_offer_adjustment_line_id IN NUMBER
1331 -- p_object_version_number IN NUMBER Optional Default = NULL
1332 --
1333 -- OUT
1334 -- x_return_status OUT VARCHAR2
1335 -- x_msg_count OUT NUMBER
1336 -- x_msg_data OUT VARCHAR2
1337 -- Version : Current version 1.0
1338 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
1339 -- and basic operation, developer must manually add parameters and business logic as necessary.
1340 --
1341 -- History
1342 --
1343 -- NOTE
1344 --
1345 -- End of Comments
1346 -- ==============================================================================
1347
1348 PROCEDURE Delete_Offer_Adj_Line(
1349 p_api_version_number IN NUMBER,
1350 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1351 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1352 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1353 x_return_status OUT NOCOPY VARCHAR2,
1354 x_msg_count OUT NOCOPY NUMBER,
1355 x_msg_data OUT NOCOPY VARCHAR2,
1356 p_offer_adjustment_line_id IN NUMBER,
1357 p_object_version_number IN NUMBER
1358 )
1359
1360 IS
1361 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Offer_Adj_Line';
1362 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1363 l_object_version_number NUMBER;
1364
1365 BEGIN
1366 -- Standard Start of API savepoint
1367 SAVEPOINT delete_offer_adj_line_pvt;
1368
1369 -- Standard call to check for call compatibility.
1370 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1371 p_api_version_number,
1372 l_api_name,
1373 G_PKG_NAME)
1374 THEN
1375 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1376 END IF;
1377
1378
1379 -- Initialize message list if p_init_msg_list is set to TRUE.
1380 IF FND_API.to_Boolean( p_init_msg_list )
1381 THEN
1382 FND_MSG_PUB.initialize;
1383 END IF;
1384
1385
1386
1387 -- Debug Message
1388 debug_message('Private API: ' || l_api_name || 'start');
1389
1390
1391
1392 -- Initialize API return status to SUCCESS
1393 x_return_status := FND_API.G_RET_STS_SUCCESS;
1394
1395 --
1396 -- Api body
1397 --
1398 -- Debug Message
1399 debug_message( 'Private API: Calling delete table handler');
1400
1401 -- Invoke table handler(Ozf_Offer_Adj_Line_Pkg.Delete_Row)
1402 Ozf_Offer_Adj_Line_Pkg.Delete_Row(
1403 p_offer_adjustment_line_id => p_offer_adjustment_line_id,
1404 p_object_version_number => p_object_version_number );
1405 --
1406 -- End of API body
1407 --
1408
1409 -- Standard check for p_commit
1410 IF FND_API.to_Boolean( p_commit )
1411 THEN
1412 COMMIT WORK;
1413 END IF;
1414
1415
1416 -- Debug Message
1417 debug_message('Private API: ' || l_api_name || 'end');
1418
1419
1420 -- Standard call to get message count and if count is 1, get message info.
1421 FND_MSG_PUB.Count_And_Get
1422 (p_count => x_msg_count,
1423 p_data => x_msg_data
1424 );
1425 EXCEPTION
1426
1427 WHEN OZF_Utility_PVT.resource_locked THEN
1428 x_return_status := FND_API.g_ret_sts_error;
1429 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1430
1431 WHEN FND_API.G_EXC_ERROR THEN
1432 ROLLBACK TO DELETE_Offer_Adj_Line_PVT;
1433 x_return_status := FND_API.G_RET_STS_ERROR;
1434 -- Standard call to get message count and if count=1, get the message
1435 FND_MSG_PUB.Count_And_Get (
1436 p_encoded => FND_API.G_FALSE,
1437 p_count => x_msg_count,
1438 p_data => x_msg_data
1439 );
1440
1441 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1442 ROLLBACK TO DELETE_Offer_Adj_Line_PVT;
1443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1444 -- Standard call to get message count and if count=1, get the message
1445 FND_MSG_PUB.Count_And_Get (
1446 p_encoded => FND_API.G_FALSE,
1447 p_count => x_msg_count,
1448 p_data => x_msg_data
1449 );
1450
1451 WHEN OTHERS THEN
1452 ROLLBACK TO DELETE_Offer_Adj_Line_PVT;
1453 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1454 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1455 THEN
1456 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1457 END IF;
1458 -- Standard call to get message count and if count=1, get the message
1459 FND_MSG_PUB.Count_And_Get (
1460 p_encoded => FND_API.G_FALSE,
1461 p_count => x_msg_count,
1462 p_data => x_msg_data
1463 );
1464 End Delete_Offer_Adj_Line;
1465
1466
1467
1468 -- Hint: Primary key needs to be returned.
1469 -- ==============================================================================
1470 -- Start of Comments
1471 -- ==============================================================================
1472 -- API Name
1473 -- Lock_Offer_Adj_Line
1474 -- Type
1475 -- Private
1476 -- Pre-Req
1477 --
1478 -- Parameters
1479 --
1480 -- IN
1481 -- p_api_version_number IN NUMBER Required
1482 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
1483 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
1484 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
1485 -- p_offadj_line_rec IN offadj_line_rec_type Required
1486 --
1487 -- OUT
1488 -- x_return_status OUT VARCHAR2
1489 -- x_msg_count OUT NUMBER
1490 -- x_msg_data OUT VARCHAR2
1491 -- Version : Current version 1.0
1492 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
1493 -- and basic operation, developer must manually add parameters and business logic as necessary.
1494 --
1495 -- History
1496 --
1497 -- NOTE
1498 --
1499 -- End of Comments
1500 -- ==============================================================================
1501
1502 PROCEDURE Lock_Offer_Adj_Line(
1503 p_api_version_number IN NUMBER,
1504 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1505
1506 x_return_status OUT NOCOPY VARCHAR2,
1507 x_msg_count OUT NOCOPY NUMBER,
1508 x_msg_data OUT NOCOPY VARCHAR2,
1509
1510 p_offer_adjustment_line_id IN NUMBER,
1511 p_object_version IN NUMBER
1512 )
1513
1514 IS
1515 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Offer_Adj_Line';
1516 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1517 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1518 l_offer_adjustment_line_id NUMBER;
1519
1520 BEGIN
1521
1522 -- Debug Message
1523 debug_message('Private API: ' || l_api_name || 'start');
1524
1525
1526 -- Initialize message list if p_init_msg_list is set to TRUE.
1527 IF FND_API.to_Boolean( p_init_msg_list )
1528 THEN
1529 FND_MSG_PUB.initialize;
1530 END IF;
1531
1532
1533
1534 -- Standard call to check for call compatibility.
1535 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1536 p_api_version_number,
1537 l_api_name,
1538 G_PKG_NAME)
1539 THEN
1540 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1541 END IF;
1542
1543
1544
1545 -- Initialize API return status to SUCCESS
1546 x_return_status := FND_API.G_RET_STS_SUCCESS;
1547
1548
1549 ------------------------ lock -------------------------
1550 OZF_Offer_Adj_Line_PKG.Lock_Row(l_offer_adjustment_line_id,p_object_version);
1551
1552
1553 -------------------- finish --------------------------
1554 FND_MSG_PUB.count_and_get(
1555 p_encoded => FND_API.g_false,
1556 p_count => x_msg_count,
1557 p_data => x_msg_data);
1558 debug_message(l_full_name ||': end');
1559 EXCEPTION
1560
1561 WHEN OZF_Utility_PVT.resource_locked THEN
1562 x_return_status := FND_API.g_ret_sts_error;
1563 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1564
1565 WHEN FND_API.G_EXC_ERROR THEN
1566 ROLLBACK TO LOCK_Offer_Adj_Line_PVT;
1567 x_return_status := FND_API.G_RET_STS_ERROR;
1568 -- Standard call to get message count and if count=1, get the message
1569 FND_MSG_PUB.Count_And_Get (
1570 p_encoded => FND_API.G_FALSE,
1571 p_count => x_msg_count,
1572 p_data => x_msg_data
1573 );
1574
1575 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1576 ROLLBACK TO LOCK_Offer_Adj_Line_PVT;
1577 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1578 -- Standard call to get message count and if count=1, get the message
1579 FND_MSG_PUB.Count_And_Get (
1580 p_encoded => FND_API.G_FALSE,
1581 p_count => x_msg_count,
1582 p_data => x_msg_data
1583 );
1584
1585 WHEN OTHERS THEN
1586 ROLLBACK TO LOCK_Offer_Adj_Line_PVT;
1587 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1588 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1589 THEN
1590 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1591 END IF;
1592 -- Standard call to get message count and if count=1, get the message
1593 FND_MSG_PUB.Count_And_Get (
1594 p_encoded => FND_API.G_FALSE,
1595 p_count => x_msg_count,
1596 p_data => x_msg_data
1597 );
1598 End Lock_Offer_Adj_Line;
1599
1600
1601
1602
1603 PROCEDURE check_Offadj_Line_Uk_Items(
1604 p_offadj_line_rec IN offadj_line_rec_type,
1605 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1606 x_return_status OUT NOCOPY VARCHAR2)
1607 IS
1608 l_valid_flag VARCHAR2(1);
1609
1610 BEGIN
1611 x_return_status := FND_API.g_ret_sts_success;
1612
1613 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1614 -- l_valid_flag := OZF_Utility_PVT.check_uniqueness(
1615 -- 'ozf_offer_adjustment_lines',
1616 -- 'offer_adjustment_line_id = ''' || p_offadj_line_rec.offer_adjustment_line_id ||''''
1617 -- );
1618 l_valid_flag := FND_API.g_true;
1619 ELSE
1620 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
1621 'ozf_offer_adjustment_lines',
1622 'offer_adjustment_line_id = ''' || p_offadj_line_rec.offer_adjustment_line_id ||
1623 ''' AND offer_adjustment_line_id <> ' || p_offadj_line_rec.offer_adjustment_line_id
1624 );
1625 END IF;
1626
1627 IF l_valid_flag = FND_API.g_false THEN
1628 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFER_ADJ_LINE_ID_DUP');
1629 x_return_status := FND_API.g_ret_sts_error;
1630 END IF;
1631
1632 END check_Offadj_Line_Uk_Items;
1633
1634
1635 PROCEDURE check_offadj_inter_attr
1636 (
1637 p_offadj_line_rec IN offadj_line_rec_type,
1638 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1639 x_return_status OUT NOCOPY VARCHAR2
1640 )
1641 IS
1642 BEGIN
1643 IF (
1644 p_offadj_line_rec.modified_discount IS NOT NULL AND p_offadj_line_rec.modified_discount <> FND_API.G_MISS_NUM
1645 )
1646 AND
1647 (
1648 p_offadj_line_rec.original_discount IS NOT NULL AND p_offadj_line_rec.original_discount <> FND_API.G_MISS_NUM
1649 )
1650 THEN
1651 IF
1652 (p_offadj_line_rec.original_discount < 0 AND p_offadj_line_rec.modified_discount > 0)
1653 OR
1654 (p_offadj_line_rec.original_discount > 0 AND p_offadj_line_rec.modified_discount < 0)
1655 THEN
1656 OZF_Utility_PVT.Error_Message('OZF_OFFADJ_DISC_DIFF');
1657 x_return_status := FND_API.g_ret_sts_error;
1658 return;
1659 END IF;
1660 END IF;
1661 IF (
1662 p_offadj_line_rec.modified_discount_td IS NOT NULL AND p_offadj_line_rec.modified_discount_td <> FND_API.G_MISS_NUM
1663 )
1664 AND
1665 (
1666 p_offadj_line_rec.original_discount_td IS NOT NULL AND p_offadj_line_rec.original_discount_td <> FND_API.G_MISS_NUM
1667 )
1668 THEN
1669 IF
1670 (p_offadj_line_rec.original_discount_td < 0 AND p_offadj_line_rec.modified_discount_td > 0)
1671 OR
1672 (p_offadj_line_rec.original_discount_td > 0 AND p_offadj_line_rec.modified_discount_td < 0)
1673 THEN
1674 OZF_Utility_PVT.Error_Message('OZF_OFFADJ_DISC_DIFF');
1675 x_return_status := FND_API.g_ret_sts_error;
1676 return;
1677 END IF;
1678 END IF;
1679
1680
1681 END check_offadj_inter_attr;
1682
1683 PROCEDURE check_Offadj_Line_Req_Items(
1684 p_offadj_line_rec IN offadj_line_rec_type,
1685 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1686 x_return_status OUT NOCOPY VARCHAR2
1687 )
1688 IS
1689 CURSOR c_offer_type(p_list_header_id NUMBER) is
1690 SELECT offer_type from ozf_offers where qp_list_header_id = p_list_header_id;
1691
1692 CURSOR c_list_line_type_Code(p_list_line_id NUMBER) is
1693 select list_line_type_code from qp_list_lines where list_line_id = p_list_line_id;
1694
1695 l_list_line_type_code qp_list_lines.list_line_type_code%type;
1696 l_offer_type ozf_offers.offer_type%type;
1697
1698 BEGIN
1699
1700 open c_offer_type(p_offadj_line_rec.list_header_id);
1701 fetch c_offer_type into l_offer_type;
1702 close c_offer_type;
1703
1704 open c_list_line_type_code(p_offadj_line_rec.list_line_id);
1705 fetch c_list_line_type_code into l_list_line_type_code;
1706 close c_list_line_type_code;
1707
1708
1709 debug_message('OfferType is '||l_offer_type);
1710 x_return_status := FND_API.g_ret_sts_success;
1711
1712 OZF_Offer_Adj_Line_PVT.debug_message('ValidationMOde is '||p_validation_mode || ' : '||JTF_PLSQL_API.g_create);
1713 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1714 IF p_offadj_line_rec.offer_adjustment_id = FND_API.G_MISS_NUM OR p_offadj_line_rec.offer_adjustment_id IS NULL THEN
1715 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ADJUSTMENT_ID' );
1716 x_return_status := FND_API.g_ret_sts_error;
1717 return;
1718 END IF;
1719 IF p_offadj_line_rec.list_line_id = FND_API.G_MISS_NUM OR p_offadj_line_rec.list_line_id IS NULL THEN
1720 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LIST_LINE_ID' );
1721 x_return_status := FND_API.g_ret_sts_error;
1722 return;
1723 END IF;
1724 IF p_offadj_line_rec.list_header_id = FND_API.G_MISS_NUM OR p_offadj_line_rec.list_header_id IS NULL THEN
1725 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LIST_HEADER_ID' );
1726 x_return_status := FND_API.g_ret_sts_error;
1727 return;
1728 END IF;
1729 debug_message('MOdified discount:' ||p_offadj_line_rec.modified_discount);
1730
1731 IF (l_offer_type = 'OID' AND l_list_line_type_code <> 'DIS') THEN
1732 debug_message('Promotional Goods buy: offer type' ||l_offer_type||' list line type code: '|| l_list_line_type_code);
1733 ELSIF l_offer_type = 'DEAL' THEN
1734 IF (p_offadj_line_rec.modified_discount = FND_API.G_MISS_NUM OR p_offadj_line_rec.modified_discount IS NULL)
1735 AND
1736 (p_offadj_line_rec.modified_discount_td = FND_API.G_MISS_NUM OR p_offadj_line_rec.modified_discount_td IS NULL)
1737 THEN
1738 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'MODIFIED_DISCOUNT/MODIFIED_DISCOUNT_TD' );
1739 x_return_status := FND_API.g_ret_sts_error;
1740 return;
1741 END IF;
1742 IF (p_offadj_line_rec.original_discount IS NULL OR p_offadj_line_rec.original_discount = FND_API.G_MISS_NUM )
1743 AND
1744 (p_offadj_line_rec.original_discount_td IS NULL OR p_offadj_line_rec.original_discount_td = FND_API.G_MISS_NUM)
1745
1746 THEN
1747 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','ORIGINAL_DISCOUNT/ORIGINAL_DISCOUNT_TD');
1748 x_return_status := FND_API.g_ret_sts_error;
1749 return;
1750 END IF;
1751 ELSE
1752 /* IF p_offadj_line_rec.quantity = FND_API.G_MISS_NUM OR p_offadj_line_rec.quantity IS NULL THEN
1753 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'Quantity' );
1754 -- x_return_status := FND_API.g_ret_sts_error;
1755 END IF;
1756 */
1757 debug_message('MOdified discount:' ||p_offadj_line_rec.modified_discount);
1758 IF p_offadj_line_rec.modified_discount = FND_API.G_MISS_NUM OR p_offadj_line_rec.modified_discount IS NULL THEN
1759 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'MODIFIED_DISCOUNT' );
1760 x_return_status := FND_API.g_ret_sts_error;
1761 return;
1762 END IF;
1763 IF p_offadj_line_rec.original_discount IS NULL OR p_offadj_line_rec.original_discount = FND_API.G_MISS_NUM THEN
1764 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','ORIGINAL_DISCOUNT');
1765 x_return_status := FND_API.g_ret_sts_error;
1766 return;
1767 END IF;
1768 END IF;
1769
1770 ELSE
1771 IF p_offadj_line_rec.offer_adjustment_line_id = FND_API.G_MISS_NUM THEN
1772 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ADJUSTMENT_LINE_ID' );
1773 x_return_status := FND_API.g_ret_sts_error;
1774 return;
1775 END IF;
1776
1777 IF p_offadj_line_rec.offer_adjustment_id = FND_API.G_MISS_NUM THEN
1778 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ADJUSTMENT_ID' );
1779 x_return_status := FND_API.g_ret_sts_error;
1780 return;
1781 END IF;
1782
1783
1784 IF p_offadj_line_rec.list_line_id = FND_API.G_MISS_NUM THEN
1785 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LIST_LINE_ID' );
1786 x_return_status := FND_API.g_ret_sts_error;
1787 return;
1788 END IF;
1789
1790 IF p_offadj_line_rec.list_header_id = FND_API.G_MISS_NUM THEN
1791 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LIST_HEADER_ID' );
1792 x_return_status := FND_API.g_ret_sts_error;
1793 return;
1794 END IF;
1795
1796 IF (l_offer_type = 'OID' AND l_list_line_type_code <> 'DIS') THEN
1797 debug_message('Promotional Goods buy: offer type' ||l_offer_type||' list line type code: '|| l_list_line_type_code);
1798 ELSIF l_offer_type = 'DEAL' THEN
1799 IF (p_offadj_line_rec.modified_discount = FND_API.G_MISS_NUM )
1800 AND
1801 (p_offadj_line_rec.modified_discount_td = FND_API.G_MISS_NUM )
1802 THEN
1803 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'MODIFIED_DISCOUNT/MODIFIED_DISCOUNT_TD' );
1804 x_return_status := FND_API.g_ret_sts_error;
1805 return;
1806 END IF;
1807 IF ( p_offadj_line_rec.original_discount = FND_API.G_MISS_NUM )
1808 AND
1809 (p_offadj_line_rec.original_discount_td = FND_API.G_MISS_NUM)
1810 THEN
1811 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','ORIGINAL_DISCOUNT/ORIGINAL_DISCOUNT_TD');
1812 x_return_status := FND_API.g_ret_sts_error;
1813 return;
1814 END IF;
1815 ELSE
1816 debug_message('MOdified discount:' ||p_offadj_line_rec.modified_discount);
1817 IF p_offadj_line_rec.modified_discount = FND_API.G_MISS_NUM OR p_offadj_line_rec.modified_discount IS NULL THEN
1818 IF p_offadj_line_rec.modified_discount_td = FND_API.G_MISS_NUM OR p_offadj_line_rec.modified_discount_td IS NULL THEN
1819 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'MODIFIED_DISCOUNT');
1820 x_return_status := FND_API.g_ret_sts_error;
1821 END IF;
1822 debug_message('Original discount:' ||p_offadj_line_rec.original_discount);
1823 IF p_offadj_line_rec.original_discount IS NULL OR p_offadj_line_rec.original_discount = FND_API.G_MISS_NUM THEN
1824 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD','MISS_FIELD','original_discount');
1825 x_return_status := FND_API.g_ret_sts_error;
1826 return;
1827 END IF;
1828
1829 END IF;
1830 END IF;
1831 END IF;
1832
1833 END check_Offadj_Line_Req_Items;
1834
1835
1836
1837 PROCEDURE check_Offadj_Line_Fk_Items(
1838 p_offadj_line_rec IN offadj_line_rec_type,
1839 x_return_status OUT NOCOPY VARCHAR2
1840 )
1841 IS
1842 BEGIN
1843 x_return_status := FND_API.g_ret_sts_success;
1844
1845 -- Enter custom code here
1846
1847 END check_Offadj_Line_Fk_Items;
1848
1849
1850
1851 PROCEDURE check_Offadj_Line_Lookup_Items(
1852 p_offadj_line_rec IN offadj_line_rec_type,
1853 x_return_status OUT NOCOPY VARCHAR2
1854 )
1855 IS
1856 BEGIN
1857 x_return_status := FND_API.g_ret_sts_success;
1858
1859 -- Enter custom code here
1860
1861 END check_Offadj_Line_Lookup_Items;
1862
1863
1864
1865 PROCEDURE Check_Offadj_Line_Items (
1866 P_offadj_line_rec IN offadj_line_rec_type,
1867 p_validation_mode IN VARCHAR2,
1868 x_return_status OUT NOCOPY VARCHAR2
1869 )
1870 IS
1871 l_return_status VARCHAR2(1);
1872 BEGIN
1873
1874 l_return_status := FND_API.g_ret_sts_success;
1875 debug_message('inside check offadj line Req items');
1876 check_offadj_line_req_items(
1877 p_offadj_line_rec => p_offadj_line_rec,
1878 p_validation_mode => p_validation_mode,
1879 x_return_status => x_return_status);
1880 IF x_return_status <> FND_API.g_ret_sts_success THEN
1881 l_return_status := FND_API.g_ret_sts_error;
1882 END IF;
1883
1884 -- Check Items Uniqueness API calls
1885
1886 debug_message('inside check offadj line Uk items');
1887 check_Offadj_line_Uk_Items(
1888 p_offadj_line_rec => p_offadj_line_rec,
1889 p_validation_mode => p_validation_mode,
1890 x_return_status => x_return_status);
1891 IF x_return_status <> FND_API.g_ret_sts_success THEN
1892 l_return_status := FND_API.g_ret_sts_error;
1893 END IF;
1894
1895 -- Check Items Required/NOT NULL API calls
1896
1897 -- Check Items Foreign Keys API calls
1898
1899 debug_message('inside check offadj line Fk items');
1900 check_offadj_line_FK_items(
1901 p_offadj_line_rec => p_offadj_line_rec,
1902 x_return_status => x_return_status);
1903 IF x_return_status <> FND_API.g_ret_sts_success THEN
1904 l_return_status := FND_API.g_ret_sts_error;
1905 END IF;
1906
1907 -- Check Items Lookups
1908 debug_message('inside check offadj line LkUP items');
1909 check_offadj_line_Lookup_items(
1910 p_offadj_line_rec => p_offadj_line_rec,
1911 x_return_status => x_return_status);
1912 IF x_return_status <> FND_API.g_ret_sts_success THEN
1913 l_return_status := FND_API.g_ret_sts_error;
1914 END IF;
1915
1916 check_offadj_inter_attr
1917 (
1918 p_offadj_line_rec => p_offadj_line_rec,
1919 p_validation_mode => p_validation_mode,
1920 x_return_status => x_return_status
1921 );
1922 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1923 RAISE FND_API.G_EXC_ERROR;
1924 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1925 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1926 END IF;
1927
1928 x_return_status := l_return_status;
1929
1930 END Check_offadj_line_Items;
1931
1932
1933
1934
1935
1936 PROCEDURE Complete_Offadj_Line_Rec (
1937 p_offadj_line_rec IN offadj_line_rec_type,
1938 x_complete_rec OUT NOCOPY offadj_line_rec_type)
1939 IS
1940 l_return_status VARCHAR2(1);
1941
1942 CURSOR c_complete IS
1943 SELECT *
1944 FROM ozf_offer_adjustment_lines
1945 WHERE offer_adjustment_line_id = p_offadj_line_rec.offer_adjustment_line_id and offer_adjustment_id = p_offadj_line_rec.offer_adjustment_id;
1946
1947 l_offadj_line_rec c_complete%ROWTYPE;
1948 BEGIN
1949 x_complete_rec := p_offadj_line_rec;
1950
1951
1952 OPEN c_complete;
1953 FETCH c_complete INTO l_offadj_line_rec;
1954 CLOSE c_complete;
1955
1956 -- offer_adjustment_line_id
1957 IF p_offadj_line_rec.offer_adjustment_line_id IS NULL THEN
1958 x_complete_rec.offer_adjustment_line_id := l_offadj_line_rec.offer_adjustment_line_id;
1959 END IF;
1960
1961 -- offer_adjustment_id
1962 IF p_offadj_line_rec.offer_adjustment_id IS NULL THEN
1963 x_complete_rec.offer_adjustment_id := l_offadj_line_rec.offer_adjustment_id;
1964 END IF;
1965
1966 -- list_line_id
1967 IF p_offadj_line_rec.list_line_id IS NULL THEN
1968 x_complete_rec.list_line_id := l_offadj_line_rec.list_line_id;
1969 END IF;
1970
1971 -- arithmetic_operator
1972 IF p_offadj_line_rec.arithmetic_operator IS NULL THEN
1973 x_complete_rec.arithmetic_operator := l_offadj_line_rec.arithmetic_operator;
1974 END IF;
1975
1976 -- original_discount
1977 IF p_offadj_line_rec.original_discount IS NULL THEN
1978 x_complete_rec.original_discount := l_offadj_line_rec.original_discount;
1979 END IF;
1980
1981 -- modified_discount
1982 -- IF p_offadj_line_rec.modified_discount IS NULL THEN
1983 -- x_complete_rec.modified_discount := l_offadj_line_rec.modified_discount;
1984 -- END IF;
1985
1986 -- last_update_date
1987 IF p_offadj_line_rec.last_update_date IS NULL THEN
1988 x_complete_rec.last_update_date := l_offadj_line_rec.last_update_date;
1989 END IF;
1990
1991 -- last_updated_by
1992 IF p_offadj_line_rec.last_updated_by IS NULL THEN
1993 x_complete_rec.last_updated_by := l_offadj_line_rec.last_updated_by;
1994 END IF;
1995
1996 -- creation_date
1997 IF p_offadj_line_rec.creation_date IS NULL THEN
1998 x_complete_rec.creation_date := l_offadj_line_rec.creation_date;
1999 END IF;
2000
2001 -- created_by
2002 IF p_offadj_line_rec.created_by IS NULL THEN
2003 x_complete_rec.created_by := l_offadj_line_rec.created_by;
2004 END IF;
2005
2006 -- last_update_login
2007 IF p_offadj_line_rec.last_update_login IS NULL THEN
2008 x_complete_rec.last_update_login := l_offadj_line_rec.last_update_login;
2009 END IF;
2010
2011 -- object_version_number
2012 IF p_offadj_line_rec.object_version_number IS NULL THEN
2013 x_complete_rec.object_version_number := l_offadj_line_rec.object_version_number;
2014 END IF;
2015
2016 -- list_header_id
2017 IF p_offadj_line_rec.list_header_id IS NULL THEN
2018 x_complete_rec.list_header_id := l_offadj_line_rec.list_header_id;
2019 END IF;
2020
2021 -- accrual_flag
2022 IF p_offadj_line_rec.accrual_flag IS NULL THEN
2023 x_complete_rec.accrual_flag := l_offadj_line_rec.accrual_flag;
2024 END IF;
2025
2026 -- list_line_id_td
2027 IF p_offadj_line_rec.list_line_id_td IS NULL THEN
2028 x_complete_rec.list_line_id_td := l_offadj_line_rec.list_line_id_td;
2029 END IF;
2030
2031 -- original_discount_td
2032 IF p_offadj_line_rec.original_discount_td IS NULL THEN
2033 x_complete_rec.original_discount_td := l_offadj_line_rec.original_discount_td;
2034 END IF;
2035
2036 -- modified_discount_td
2037 -- IF p_offadj_line_rec.modified_discount_td IS NULL THEN
2038 -- x_complete_rec.modified_discount_td := l_offadj_line_rec.modified_discount_td;
2039 -- END IF;
2040
2041 -- quantity
2042 IF p_offadj_line_rec.quantity IS NULL THEN
2043 x_complete_rec.quantity := l_offadj_line_rec.quantity;
2044 END IF;
2045
2046 -- Note: Developers need to modify the procedure
2047 -- to handle any business specific requirements.
2048 END Complete_Offadj_Line_Rec;
2049
2050
2051
2052
2053 PROCEDURE Default_Offadj_Line_Items ( p_offadj_line_rec IN offadj_line_rec_type ,
2054 x_offadj_line_rec OUT NOCOPY offadj_line_rec_type )
2055 IS
2056 l_offadj_line_rec offadj_line_rec_type := p_offadj_line_rec;
2057 BEGIN
2058 -- Developers should put their code to default the record type
2059 -- e.g. IF p_campaign_rec.status_code IS NULL
2060 -- OR p_campaign_rec.status_code = FND_API.G_MISS_CHAR THEN
2061 -- l_campaign_rec.status_code := 'NEW' ;
2062 -- END IF ;
2063 --
2064 x_offadj_line_rec.offer_adjustment_line_id := p_offadj_line_rec.offer_adjustment_line_id;
2065 x_offadj_line_rec.offer_adjustment_id := p_offadj_line_rec.offer_adjustment_id;
2066 x_offadj_line_rec.list_line_id := p_offadj_line_rec.list_line_id;
2067 x_offadj_line_rec.arithmetic_operator := p_offadj_line_rec.arithmetic_operator;
2068 x_offadj_line_rec.original_discount := p_offadj_line_rec.original_discount;
2069 x_offadj_line_rec.modified_discount := p_offadj_line_rec.modified_discount;
2070 x_offadj_line_rec.last_update_date := p_offadj_line_rec.last_update_date;
2071 x_offadj_line_rec.last_updated_by := p_offadj_line_rec.last_updated_by;
2072 x_offadj_line_rec.creation_date := p_offadj_line_rec.creation_date;
2073 x_offadj_line_rec.created_by := p_offadj_line_rec.created_by;
2074 x_offadj_line_rec.last_update_login := p_offadj_line_rec.last_update_login;
2075 x_offadj_line_rec.object_version_number := p_offadj_line_rec.object_version_number;
2076 x_offadj_line_rec.list_header_id := p_offadj_line_rec.list_header_id;
2077 x_offadj_line_rec.accrual_flag := p_offadj_line_rec.accrual_flag;
2078 x_offadj_line_rec.list_line_id_td := p_offadj_line_rec.list_line_id_td;
2079 x_offadj_line_rec.original_discount_td := p_offadj_line_rec.original_discount_td;
2080 x_offadj_line_rec.modified_discount_td := p_offadj_line_rec.modified_discount_td;
2081 x_offadj_line_rec.quantity := p_offadj_line_rec.quantity;
2082 NULL ;
2083 END;
2084
2085
2086
2087
2088 PROCEDURE Validate_Offer_Adj_Line(
2089 p_api_version_number IN NUMBER,
2090 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2091 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
2092 p_offadj_line_rec IN offadj_line_rec_type,
2093 p_validation_mode IN VARCHAR2,
2094 x_return_status OUT NOCOPY VARCHAR2,
2095 x_msg_count OUT NOCOPY NUMBER,
2096 x_msg_data OUT NOCOPY VARCHAR2
2097 )
2098 IS
2099 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Offer_Adj_Line';
2100 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2101 l_object_version_number NUMBER;
2102 l_offadj_line_rec offadj_line_rec_type;
2103
2104 BEGIN
2105 -- Standard Start of API savepoint
2106 SAVEPOINT validate_offer_adj_line_;
2107
2108 -- Standard call to check for call compatibility.
2109 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
2110 p_api_version_number,
2111 l_api_name,
2112 G_PKG_NAME)
2113 THEN
2114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2115 END IF;
2116
2117
2118 -- Initialize message list if p_init_msg_list is set to TRUE.
2119 IF FND_API.to_Boolean( p_init_msg_list )
2120 THEN
2121 FND_MSG_PUB.initialize;
2122 END IF;
2123
2124 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2125 Check_offadj_line_Items(
2126 p_offadj_line_rec => p_offadj_line_rec,
2127 p_validation_mode => p_validation_mode,
2128 x_return_status => x_return_status
2129 );
2130
2131 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2132 RAISE FND_API.G_EXC_ERROR;
2133 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2134 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2135 END IF;
2136 END IF;
2137
2138 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
2139 Default_Offadj_Line_Items (p_offadj_line_rec => p_offadj_line_rec ,
2140 x_offadj_line_rec => l_offadj_line_rec) ;
2141 END IF ;
2142
2143
2144 Complete_offadj_line_Rec(
2145 p_offadj_line_rec => p_offadj_line_rec,
2146 x_complete_rec => l_offadj_line_rec
2147 );
2148
2149 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
2150 Validate_offadj_line_Rec(
2151 p_api_version_number => 1.0,
2152 p_init_msg_list => FND_API.G_FALSE,
2153 x_return_status => x_return_status,
2154 x_msg_count => x_msg_count,
2155 x_msg_data => x_msg_data,
2156 p_offadj_line_rec => l_offadj_line_rec);
2157
2158 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2159 RAISE FND_API.G_EXC_ERROR;
2160 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2161 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2162 END IF;
2163 END IF;
2164
2165
2166 -- Debug Message
2167 debug_message('Private API: ' || l_api_name || 'start');
2168
2169
2170
2171 -- Initialize API return status to SUCCESS
2172 x_return_status := FND_API.G_RET_STS_SUCCESS;
2173
2174
2175 -- Debug Message
2176 debug_message('Private API: ' || l_api_name || 'end');
2177
2178
2179 -- Standard call to get message count and if count is 1, get message info.
2180 FND_MSG_PUB.Count_And_Get
2181 (p_count => x_msg_count,
2182 p_data => x_msg_data
2183 );
2184 EXCEPTION
2185
2186 WHEN OZF_Utility_PVT.resource_locked THEN
2187 x_return_status := FND_API.g_ret_sts_error;
2188 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
2189
2190 WHEN FND_API.G_EXC_ERROR THEN
2191 ROLLBACK TO VALIDATE_Offer_Adj_Line_;
2192 x_return_status := FND_API.G_RET_STS_ERROR;
2193 -- Standard call to get message count and if count=1, get the message
2194 FND_MSG_PUB.Count_And_Get (
2195 p_encoded => FND_API.G_FALSE,
2196 p_count => x_msg_count,
2197 p_data => x_msg_data
2198 );
2199
2200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2201 ROLLBACK TO VALIDATE_Offer_Adj_Line_;
2202 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2203 -- Standard call to get message count and if count=1, get the message
2204 FND_MSG_PUB.Count_And_Get (
2205 p_encoded => FND_API.G_FALSE,
2206 p_count => x_msg_count,
2207 p_data => x_msg_data
2208 );
2209
2210 WHEN OTHERS THEN
2211 ROLLBACK TO VALIDATE_Offer_Adj_Line_;
2212 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2213 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2214 THEN
2215 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2216 END IF;
2217 -- Standard call to get message count and if count=1, get the message
2218 FND_MSG_PUB.Count_And_Get (
2219 p_encoded => FND_API.G_FALSE,
2220 p_count => x_msg_count,
2221 p_data => x_msg_data
2222 );
2223 End Validate_Offer_Adj_Line;
2224
2225
2226 PROCEDURE Validate_Offadj_Line_Rec (
2227 p_api_version_number IN NUMBER,
2228 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2229 x_return_status OUT NOCOPY VARCHAR2,
2230 x_msg_count OUT NOCOPY NUMBER,
2231 x_msg_data OUT NOCOPY VARCHAR2,
2232 p_offadj_line_rec IN offadj_line_rec_type
2233 )
2234 IS
2235 BEGIN
2236 -- Initialize message list if p_init_msg_list is set to TRUE.
2237 IF FND_API.to_Boolean( p_init_msg_list )
2238 THEN
2239 FND_MSG_PUB.initialize;
2240 END IF;
2241
2242
2243
2244 -- Initialize API return status to SUCCESS
2245 x_return_status := FND_API.G_RET_STS_SUCCESS;
2246
2247 -- Hint: Validate data
2248 -- If data not valid
2249 -- THEN
2250 -- x_return_status := FND_API.G_RET_STS_ERROR;
2251
2252 -- Debug Message
2253 debug_message('Private API: Validate_dm_model_rec');
2254 -- Standard call to get message count and if count is 1, get message info.
2255 FND_MSG_PUB.Count_And_Get
2256 (p_count => x_msg_count,
2257 p_data => x_msg_data
2258 );
2259 END Validate_offadj_line_Rec;
2260
2261 FUNCTION get_price_list_name(p_list_line_id IN NUMBER)
2262 RETURN VARCHAR2
2263 IS
2264 l_price_list_name qp_list_headers_tl.name%type;
2265 cursor c_price_list_name(p_list_line_id IN NUMBER) IS
2266 SELECT name FROM qp_list_headers_vl
2267 WHERE list_header_id = (SELECT list_header_id FROM qp_list_lines WHERE list_line_id = p_list_line_id);
2268 BEGIN
2269 OPEN c_price_list_name(p_list_line_id);
2270 FETCH c_price_list_name INTO l_price_list_name;
2271 CLOSE c_price_list_name;
2272 return l_price_list_name;
2273 END ;
2274
2275 END OZF_Offer_Adj_Line_PVT;