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