[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_BACKDATE_PVT
Source
1 PACKAGE BODY OZF_Offer_Backdate_PVT as
2 /* $Header: ozfvobdb.pls 120.9 2006/07/20 12:15:00 mgudivak ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Offer_Backdate_PVT
7 -- Purpose
8 --
9 -- History
10 -- 05-DEC-2002 julou 1. sql performance fix
11 -- 11-DEC-2002 julou change ams_offer_adjustments to ozf_offer_adjustments_b
12 -- Tue Dec 02 2003:7/44 PM RSSHARMA Fixed process_new_adjustments removed reference to all ams tables
13 -- Wed Nov 24 2004:4/15 PM RSSHARMA Fixed bug # 4027062(11.5.9),4085552 (11.5.10). Introduced new procedure update_volume_offer_discounts
14 -- to update the disocunts and tiers for Volume Offer.
15 -- Mon Aug 01 2005:2/12 AM rssharma Fixed bug # 4522172. Send arithmetic operator while activating new adjustment lines
16 -- else the discount type is converted to amount
17 -- Wed Sep 21 2005:5/16 PM RSSHARMA. Fixed bug #4626103. Changes approach for adjusting tiers. RIght now we
18 -- are just updating the tiers in QP and expecting the Accrual engine to call the update_offer_discounts api
19 -- to update discounts after the adjustment is effective
20 -- NOTE
21 --
22 -- End of Comments
23 -- Thu Aug 19 1999:6/43 AM RSSHARMA Added procedure process_vo_adjustments for processing volume offer adjustments
24 -- Tue Sep 27 2005:6/50 PM RSSHARMA Added logic to end date adjustments
25 -- Mon Oct 03 2005:6/39 PM RSSHARMA Added start date and end dates to new products
26 -- Mon Oct 03 2005:8/57 PM RSSHARMA Fixed issue with adjustment did not go active if volumeType on the tier was quantity
27 -- with error message Benefit Quantity/Benefit UOM are required. The issue was that arithmetic_operator was not passed
28 -- in hence some if conditions failed which populated values into benefit_qty and benefit_uom which should not have.
29 -- Pass arithmetic_operator to fix the issue
30 -- Wed Mar 29 2006:5/46 PM RSSHARMA Added new procedures to close adjustments and changed update_offer_discounts for new adjustments functionality
31 -- ===============================================================
32
33
34 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Offer_Backdate_PVT';
35 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvobdb.pls';
36
37
38 -- Hint: Primary key needs to be returned.
39 PROCEDURE Create_Offer_Backdate(
40 p_api_version_number IN NUMBER,
41 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
42 p_commit IN VARCHAR2 := FND_API.G_FALSE,
43 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
44
45 x_return_status OUT NOCOPY VARCHAR2,
46 x_msg_count OUT NOCOPY NUMBER,
47 x_msg_data OUT NOCOPY VARCHAR2,
48
49 p_offer_backdate_rec IN offer_backdate_rec_type := g_miss_offer_backdate_rec,
50 x_offer_adjustment_id OUT NOCOPY NUMBER
51 )
52
53 IS
54 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Offer_Backdate';
55 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
56 l_return_status_full VARCHAR2(1);
57 l_object_version_number NUMBER := 1;
58 l_org_id NUMBER := FND_API.G_MISS_NUM;
59 l_OFFER_ADJUSTMENT_ID NUMBER;
60 l_dummy NUMBER;
61
62 CURSOR c_id IS
63 SELECT ozf_OFFER_ADJUSTMENTS_B_s.NEXTVAL
64 FROM dual;
65
66 CURSOR c_id_exists (l_id IN NUMBER) IS
67 SELECT 1
68 FROM ozf_OFFER_ADJUSTMENTS_B
69 WHERE OFFER_ADJUSTMENT_ID = l_id;
70
71 BEGIN
72 -- Standard Start of API savepoint
73 SAVEPOINT CREATE_Offer_Backdate_PVT;
74
75 -- Standard call to check for call compatibility.
76 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
77 p_api_version_number,
78 l_api_name,
79 G_PKG_NAME)
80 THEN
81 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
82 END IF;
83
84 -- Initialize message list if p_init_msg_list is set to TRUE.
85 IF FND_API.to_Boolean( p_init_msg_list )
86 THEN
87 FND_MSG_PUB.initialize;
88 END IF;
89
90 -- Debug Message
91 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
92
93
94 -- Initialize API return status to SUCCESS
95 x_return_status := FND_API.G_RET_STS_SUCCESS;
96
97 -- Local variable initialization
98
99 IF p_offer_backdate_rec.OFFER_ADJUSTMENT_ID IS NULL OR p_offer_backdate_rec.OFFER_ADJUSTMENT_ID = FND_API.g_miss_num THEN
100 LOOP
101 l_dummy := NULL;
102 OPEN c_id;
103 FETCH c_id INTO l_OFFER_ADJUSTMENT_ID;
104 CLOSE c_id;
105
106 OPEN c_id_exists(l_OFFER_ADJUSTMENT_ID);
107 FETCH c_id_exists INTO l_dummy;
108 CLOSE c_id_exists;
109 EXIT WHEN l_dummy IS NULL;
110 END LOOP;
111 --ELSE
112 --p_offer_backdate_rec.offer_adjustment_id := l_offer_adjustment_id;
113 END IF;
114
115 -- =========================================================================
116 -- Validate Environment
117 -- =========================================================================
118
119 IF FND_GLOBAL.User_Id IS NULL
120 THEN
121 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
122 RAISE FND_API.G_EXC_ERROR;
123 END IF;
124
125 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
126 THEN
127 -- Debug message
128 OZF_UTILITY_PVT.debug_message('Private API: Validate_Offer_Backdate');
129
130 -- Invoke validation procedures
131 Validate_offer_backdate(
132 p_api_version_number => 1.0,
133 p_init_msg_list => FND_API.G_FALSE,
134 p_validation_level => p_validation_level,
135 p_validation_mode => JTF_PLSQL_API.g_create,
136 p_offer_backdate_rec => p_offer_backdate_rec,
137 x_return_status => x_return_status,
138 x_msg_count => x_msg_count,
139 x_msg_data => x_msg_data);
140 END IF;
141
142 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
143 RAISE FND_API.G_EXC_ERROR;
144 END IF;
145
146
147 -- Debug Message
148 OZF_UTILITY_PVT.debug_message( 'Private API: Calling create table handler');
149
150 -- Invoke table handler(ozf_OFFER_ADJUSTMENTS_PKG.Insert_Row)
151 ozf_OFFER_ADJUSTMENTS_PKG.Insert_Row(
152 px_offer_adjustment_id => l_offer_adjustment_id,
153 p_effective_date => p_offer_backdate_rec.effective_date,
154 p_approved_date => p_offer_backdate_rec.approved_date,
155 p_settlement_code => p_offer_backdate_rec.settlement_code,
156 p_status_code => p_offer_backdate_rec.status_code,
157 p_list_header_id => p_offer_backdate_rec.list_header_id,
158 p_version => p_offer_backdate_rec.version,
159 p_budget_adjusted_flag => p_offer_backdate_rec.budget_adjusted_flag,
160 p_comments => p_offer_backdate_rec.comments,
161 p_last_update_date => SYSDATE,
162 p_last_updated_by => FND_GLOBAL.USER_ID,
163 p_creation_date => SYSDATE,
164 p_created_by => FND_GLOBAL.USER_ID,
165 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
166 px_object_version_number => l_object_version_number,
167 p_security_group_id => p_offer_backdate_rec.security_group_id);
168
169 x_offer_adjustment_id := l_offer_adjustment_id;
170 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
171 RAISE FND_API.G_EXC_ERROR;
172 END IF;
173 --
174 -- End of API body
175 --
176
177 -- Standard check for p_commit
178 IF FND_API.to_Boolean( p_commit )
179 THEN
180 COMMIT WORK;
181 END IF;
182
183
184 -- Debug Message
185 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
186
187 -- Standard call to get message count and if count is 1, get message info.
188 FND_MSG_PUB.Count_And_Get
189 (p_count => x_msg_count,
190 p_data => x_msg_data
191 );
192 EXCEPTION
193
194 WHEN OZF_Utility_PVT.resource_locked THEN
195 x_return_status := FND_API.g_ret_sts_error;
196 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
197
198 WHEN FND_API.G_EXC_ERROR THEN
199 ROLLBACK TO CREATE_Offer_Backdate_PVT;
200 x_return_status := FND_API.G_RET_STS_ERROR;
201 -- Standard call to get message count and if count=1, get the message
202 FND_MSG_PUB.Count_And_Get (
203 p_encoded => FND_API.G_FALSE,
204 p_count => x_msg_count,
205 p_data => x_msg_data
206 );
207
208 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
209 ROLLBACK TO CREATE_Offer_Backdate_PVT;
210 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
211 -- Standard call to get message count and if count=1, get the message
212 FND_MSG_PUB.Count_And_Get (
213 p_encoded => FND_API.G_FALSE,
214 p_count => x_msg_count,
215 p_data => x_msg_data
216 );
217
218 WHEN OTHERS THEN
219 ROLLBACK TO CREATE_Offer_Backdate_PVT;
220 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
221 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
222 THEN
223 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
224 END IF;
225 -- Standard call to get message count and if count=1, get the message
226 FND_MSG_PUB.Count_And_Get (
227 p_encoded => FND_API.G_FALSE,
228 p_count => x_msg_count,
229 p_data => x_msg_data
230 );
231 End Create_Offer_Backdate;
232
233
234 PROCEDURE Update_Offer_Backdate(
235 p_api_version_number IN NUMBER,
236 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
237 p_commit IN VARCHAR2 := FND_API.G_FALSE,
238 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
239
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2,
243
244 p_offer_backdate_rec IN offer_backdate_rec_type,
245 x_object_version_number OUT NOCOPY NUMBER
246 )
247
248 IS
249
250 CURSOR c_get_Offer_Backdate(p_offer_adjustment_id NUMBER) IS
251 SELECT *
252 FROM ozf_offer_adjustments_B
253 WHERE offer_adjustment_id = p_offer_adjustment_id ;
254
255 CURSOR c_get_offer_status(p_offer_adjustment_id NUMBER) IS
256 SELECT status_code
257 FROM ozf_offer_adjustments_B
258 WHERE offer_adjustment_id = p_offer_adjustment_id ;
259
260 l_current_status_code VARCHAR2(30);
261 l_new_status_code VARCHAR2(30);
262
263 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Offer_Backdate';
264 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
265 -- Local Variables
266 l_object_version_number NUMBER;
267 l_OFFER_ADJUSTMENT_ID NUMBER;
268 l_ref_offer_backdate_rec c_get_Offer_Backdate%ROWTYPE ;
269 l_tar_offer_backdate_rec OZF_Offer_Backdate_PVT.offer_backdate_rec_type := P_offer_backdate_rec;
270 l_rowid ROWID;
271
272 BEGIN
273 -- Standard Start of API savepoint
274 SAVEPOINT UPDATE_Offer_Backdate_PVT;
275
276 -- Standard call to check for call compatibility.
277 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
278 p_api_version_number,
279 l_api_name,
280 G_PKG_NAME)
281 THEN
282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
283 END IF;
284
285 -- Initialize message list if p_init_msg_list is set to TRUE.
286 IF FND_API.to_Boolean( p_init_msg_list )
287 THEN
288 FND_MSG_PUB.initialize;
289 END IF;
290
291 -- Debug Message
292 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
293
294
295 -- Initialize API return status to SUCCESS
296 x_return_status := FND_API.G_RET_STS_SUCCESS;
297
298 -- Debug Message
299 OZF_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
300
301 /*
302 OPEN c_get_Offer_Backdate( l_tar_offer_backdate_rec.offer_adjustment_id);
303
304 FETCH c_get_Offer_Backdate INTO l_ref_offer_backdate_rec ;
305
306 If ( c_get_Offer_Backdate%NOTFOUND) THEN
307 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
308 p_token_name => 'INFO',
309 p_token_value => 'Offer_Backdate') ;
310 RAISE FND_API.G_EXC_ERROR;
311 END IF;
312 -- Debug Message
313 OZF_UTILITY_PVT.debug_message('Private API: - Close Cursor');
314 CLOSE c_get_Offer_Backdate;
315 */
316
317
318 If (l_tar_offer_backdate_rec.object_version_number is NULL or
319 l_tar_offer_backdate_rec.object_version_number = FND_API.G_MISS_NUM ) Then
320 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
321 p_token_name => 'COLUMN',
322 p_token_value => 'Last_Update_Date') ;
323 raise FND_API.G_EXC_ERROR;
324 End if;
325 -- Check Whether record has been changed by someone else
326 If (l_tar_offer_backdate_rec.object_version_number <> l_ref_offer_backdate_rec.object_version_number) Then
327 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
328 p_token_name => 'INFO',
329 p_token_value => 'Offer_Backdate') ;
330 raise FND_API.G_EXC_ERROR;
331 End if;
332 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
333 THEN
334 -- Debug message
335 OZF_UTILITY_PVT.debug_message('Private API: Validate_Offer_Backdate');
336
337 -- Invoke validation procedures
338 Validate_offer_backdate(
339 p_api_version_number => 1.0,
340 p_init_msg_list => FND_API.G_FALSE,
341 p_validation_level => p_validation_level,
342 p_validation_mode => JTF_PLSQL_API.g_update,
343 p_offer_backdate_rec => p_offer_backdate_rec,
344 x_return_status => x_return_status,
345 x_msg_count => x_msg_count,
346 x_msg_data => x_msg_data);
347 END IF;
348
349 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
350 RAISE FND_API.G_EXC_ERROR;
351 END IF;
352
353 OPEN c_get_offer_status(p_offer_backdate_rec.offer_adjustment_id);
354 FETCH c_get_offer_status INTO l_current_status_code;
355 CLOSE c_get_offer_status;
356
357 l_new_status_code := p_offer_backdate_rec.status_code;
358
359 IF ( l_current_status_code <> l_new_status_code )
360 THEN
361
362 IF ( l_new_status_code = 'ACTIVE' )
363 THEN
364 -- Call Approval Work Flow
365
366
367
368 AMS_GEN_APPROVAL_PVT.StartProcess
369 (p_activity_type => 'OFFR'
370 ,p_activity_id => p_offer_backdate_rec.offer_adjustment_id
371 ,p_approval_type => 'BUDGET'
372 ,p_object_version_number =>p_offer_backdate_rec.object_version_number
373 ,p_orig_stat_id => 0
374 ,p_new_stat_id => 0
375 ,p_reject_stat_id => 0
376 ,p_requester_userid => OZF_Utility_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
377 ,p_notes_from_requester => p_offer_backdate_rec.comments
378 ,p_workflowprocess => 'OZFGAPP'
379 ,p_item_type => 'OZFGAPP');
380
381
382 l_new_status_code := 'PENDING';
383
384 END IF;
385 END IF;
386
387
388
389 -- Debug Message
390 -- OZF_UTILITY_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW, 'Private API: Calling update table handler');
391
392 -- Invoke table handler(ozf_OFFER_ADJUSTMENTS_PKG.Update_Row)
393 ozf_OFFER_ADJUSTMENTS_PKG.Update_Row(
394 p_offer_adjustment_id => p_offer_backdate_rec.offer_adjustment_id,
395 p_effective_date => p_offer_backdate_rec.effective_date,
396 p_approved_date => p_offer_backdate_rec.approved_date,
397 p_settlement_code => p_offer_backdate_rec.settlement_code,
398 p_status_code => l_new_status_code, -- p_offer_backdate_rec.status_code,
399 p_list_header_id => p_offer_backdate_rec.list_header_id,
400 p_version => p_offer_backdate_rec.version,
401 p_budget_adjusted_flag => p_offer_backdate_rec.budget_adjusted_flag,
402 p_comments => p_offer_backdate_rec.comments,
403 p_last_update_date => SYSDATE,
404 p_last_updated_by => FND_GLOBAL.USER_ID,
405 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
406 p_object_version_number => p_offer_backdate_rec.object_version_number,
407 p_security_group_id => p_offer_backdate_rec.security_group_id);
408
409 x_object_version_number := p_offer_backdate_rec.object_version_number + 1;
410
411 --
412 -- End of API body.
413 --
414
415 -- Standard check for p_commit
416 IF FND_API.to_Boolean( p_commit )
417 THEN
418 COMMIT WORK;
419 END IF;
420
421
422 -- Debug Message
423 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
424
425 -- Standard call to get message count and if count is 1, get message info.
426 FND_MSG_PUB.Count_And_Get
427 (p_count => x_msg_count,
428 p_data => x_msg_data
429 );
430 EXCEPTION
431
432 WHEN OZF_Utility_PVT.resource_locked THEN
433 x_return_status := FND_API.g_ret_sts_error;
434 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
435
436 WHEN FND_API.G_EXC_ERROR THEN
437 ROLLBACK TO UPDATE_Offer_Backdate_PVT;
438 x_return_status := FND_API.G_RET_STS_ERROR;
439 -- Standard call to get message count and if count=1, get the message
440 FND_MSG_PUB.Count_And_Get (
441 p_encoded => FND_API.G_FALSE,
442 p_count => x_msg_count,
443 p_data => x_msg_data
444 );
445
446 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
447 ROLLBACK TO UPDATE_Offer_Backdate_PVT;
448 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
449 -- Standard call to get message count and if count=1, get the message
450 FND_MSG_PUB.Count_And_Get (
451 p_encoded => FND_API.G_FALSE,
452 p_count => x_msg_count,
453 p_data => x_msg_data
454 );
455
456 WHEN OTHERS THEN
457 ROLLBACK TO UPDATE_Offer_Backdate_PVT;
458 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
459 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
460 THEN
461 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
462 END IF;
463 -- Standard call to get message count and if count=1, get the message
464 FND_MSG_PUB.Count_And_Get (
465 p_encoded => FND_API.G_FALSE,
466 p_count => x_msg_count,
467 p_data => x_msg_data
468 );
469 End Update_Offer_Backdate;
470
471
472 PROCEDURE Delete_Offer_Backdate(
473 p_api_version_number IN NUMBER,
474 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
475 p_commit IN VARCHAR2 := FND_API.G_FALSE,
476 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
477 x_return_status OUT NOCOPY VARCHAR2,
478 x_msg_count OUT NOCOPY NUMBER,
479 x_msg_data OUT NOCOPY VARCHAR2,
480 p_offer_adjustment_id IN NUMBER,
481 p_object_version_number IN NUMBER
482 )
483
484 IS
485 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Offer_Backdate';
486 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
487 l_object_version_number NUMBER;
488
489 BEGIN
490 -- Standard Start of API savepoint
491 SAVEPOINT DELETE_Offer_Backdate_PVT;
492
493 -- Standard call to check for call compatibility.
494 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
495 p_api_version_number,
496 l_api_name,
497 G_PKG_NAME)
498 THEN
499 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
500 END IF;
501
502 -- Initialize message list if p_init_msg_list is set to TRUE.
503 IF FND_API.to_Boolean( p_init_msg_list )
504 THEN
505 FND_MSG_PUB.initialize;
506 END IF;
507
508 -- Debug Message
509 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
510
511
512 -- Initialize API return status to SUCCESS
513 x_return_status := FND_API.G_RET_STS_SUCCESS;
514
515 --
516 -- Api body
517 --
518 -- Debug Message
519 OZF_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
520
521 -- Invoke table handler(ozf_OFFER_ADJUSTMENTS_PKG.Delete_Row)
522 ozf_OFFER_ADJUSTMENTS_PKG.Delete_Row(
523 p_OFFER_ADJUSTMENT_ID => p_OFFER_ADJUSTMENT_ID);
524 --
525 -- End of API body
526 --
527
528 -- Standard check for p_commit
529 IF FND_API.to_Boolean( p_commit )
530 THEN
531 COMMIT WORK;
532 END IF;
533
534
535 -- Debug Message
536 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
537
538 -- Standard call to get message count and if count is 1, get message info.
539 FND_MSG_PUB.Count_And_Get
540 (p_count => x_msg_count,
541 p_data => x_msg_data
542 );
543 EXCEPTION
544
545 WHEN OZF_Utility_PVT.resource_locked THEN
546 x_return_status := FND_API.g_ret_sts_error;
547 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
548
549 WHEN FND_API.G_EXC_ERROR THEN
550 ROLLBACK TO DELETE_Offer_Backdate_PVT;
551 x_return_status := FND_API.G_RET_STS_ERROR;
552 -- Standard call to get message count and if count=1, get the message
553 FND_MSG_PUB.Count_And_Get (
554 p_encoded => FND_API.G_FALSE,
555 p_count => x_msg_count,
556 p_data => x_msg_data
557 );
558
559 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
560 ROLLBACK TO DELETE_Offer_Backdate_PVT;
561 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
562 -- Standard call to get message count and if count=1, get the message
563 FND_MSG_PUB.Count_And_Get (
564 p_encoded => FND_API.G_FALSE,
565 p_count => x_msg_count,
566 p_data => x_msg_data
567 );
568
569 WHEN OTHERS THEN
570 ROLLBACK TO DELETE_Offer_Backdate_PVT;
571 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
572 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
573 THEN
574 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
575 END IF;
576 -- Standard call to get message count and if count=1, get the message
577 FND_MSG_PUB.Count_And_Get (
578 p_encoded => FND_API.G_FALSE,
579 p_count => x_msg_count,
580 p_data => x_msg_data
581 );
582 End Delete_Offer_Backdate;
583
584
585
586 -- Hint: Primary key needs to be returned.
587 PROCEDURE Lock_Offer_Backdate(
588 p_api_version_number IN NUMBER,
589 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
590
591 x_return_status OUT NOCOPY VARCHAR2,
592 x_msg_count OUT NOCOPY NUMBER,
593 x_msg_data OUT NOCOPY VARCHAR2,
594
595 p_offer_adjustment_id IN NUMBER,
596 p_object_version IN NUMBER
597 )
598
599 IS
600 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Offer_Backdate';
601 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
602 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
603 l_OFFER_ADJUSTMENT_ID NUMBER;
604
605 CURSOR c_Offer_Backdate IS
606 SELECT OFFER_ADJUSTMENT_ID
607 FROM ozf_OFFER_ADJUSTMENTS_B
608 WHERE OFFER_ADJUSTMENT_ID = p_OFFER_ADJUSTMENT_ID
609 AND object_version_number = p_object_version
610 FOR UPDATE NOWAIT;
611
612 BEGIN
613
614 -- Debug Message
615 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
616
617 -- Initialize message list if p_init_msg_list is set to TRUE.
618 IF FND_API.to_Boolean( p_init_msg_list )
619 THEN
620 FND_MSG_PUB.initialize;
621 END IF;
622
623 -- Standard call to check for call compatibility.
624 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
625 p_api_version_number,
626 l_api_name,
627 G_PKG_NAME)
628 THEN
629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
630 END IF;
631
632
633 -- Initialize API return status to SUCCESS
634 x_return_status := FND_API.G_RET_STS_SUCCESS;
635
636
637 ------------------------ lock -------------------------
638
639 OZF_Utility_PVT.debug_message(l_full_name||': start');
640 OPEN c_Offer_Backdate;
641
642 FETCH c_Offer_Backdate INTO l_OFFER_ADJUSTMENT_ID;
643
644 IF (c_Offer_Backdate%NOTFOUND) THEN
645 CLOSE c_Offer_Backdate;
646 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
647 FND_MESSAGE.set_name('OZF', 'OZF_API_RECORD_NOT_FOUND');
648 FND_MSG_PUB.add;
649 END IF;
650 RAISE FND_API.g_exc_error;
651 END IF;
652
653 CLOSE c_Offer_Backdate;
654
655 -------------------- finish --------------------------
656 FND_MSG_PUB.count_and_get(
657 p_encoded => FND_API.g_false,
658 p_count => x_msg_count,
659 p_data => x_msg_data);
660 OZF_Utility_PVT.debug_message(l_full_name ||': end');
661 EXCEPTION
662
663 WHEN OZF_Utility_PVT.resource_locked THEN
664 x_return_status := FND_API.g_ret_sts_error;
665 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
666
667 WHEN FND_API.G_EXC_ERROR THEN
668 ROLLBACK TO LOCK_Offer_Backdate_PVT;
669 x_return_status := FND_API.G_RET_STS_ERROR;
670 -- Standard call to get message count and if count=1, get the message
671 FND_MSG_PUB.Count_And_Get (
672 p_encoded => FND_API.G_FALSE,
673 p_count => x_msg_count,
674 p_data => x_msg_data
675 );
676
677 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
678 ROLLBACK TO LOCK_Offer_Backdate_PVT;
679 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
680 -- Standard call to get message count and if count=1, get the message
681 FND_MSG_PUB.Count_And_Get (
682 p_encoded => FND_API.G_FALSE,
683 p_count => x_msg_count,
684 p_data => x_msg_data
685 );
686
687 WHEN OTHERS THEN
688 ROLLBACK TO LOCK_Offer_Backdate_PVT;
689 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
690 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
691 THEN
692 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
693 END IF;
694 -- Standard call to get message count and if count=1, get the message
695 FND_MSG_PUB.Count_And_Get (
696 p_encoded => FND_API.G_FALSE,
697 p_count => x_msg_count,
698 p_data => x_msg_data
699 );
700 End Lock_Offer_Backdate;
701
702
703 PROCEDURE check_offer_backdate_uk_items(
704 p_offer_backdate_rec IN offer_backdate_rec_type,
705 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
706 x_return_status OUT NOCOPY VARCHAR2)
707 IS
708 l_valid_flag VARCHAR2(1);
709
710 BEGIN
711 x_return_status := FND_API.g_ret_sts_success;
712 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
713 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
714 'OZF_OFFER_ADJUSTMENTS_B',
715 'OFFER_ADJUSTMENT_ID = ''' || p_offer_backdate_rec.OFFER_ADJUSTMENT_ID ||''''
716 );
717 ELSE
718 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
719 'OZF_OFFER_ADJUSTMENTS_B',
720 'OFFER_ADJUSTMENT_ID = ''' || p_offer_backdate_rec.OFFER_ADJUSTMENT_ID ||
721 ''' AND OFFER_ADJUSTMENT_ID <> ' || p_offer_backdate_rec.OFFER_ADJUSTMENT_ID
722 );
723 END IF;
724
725 IF l_valid_flag = FND_API.g_false THEN
726 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFER_ADJ_ID_DUPLICATE');
727 x_return_status := FND_API.g_ret_sts_error;
728 RETURN;
729 END IF;
730
731 END check_offer_backdate_uk_items;
732
733 PROCEDURE check_offer_backdate_req_items(
734 p_offer_backdate_rec IN offer_backdate_rec_type,
735 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
736 x_return_status OUT NOCOPY VARCHAR2
737 )
738 IS
739 BEGIN
740 x_return_status := FND_API.g_ret_sts_success;
741
742 --IF p_validation_mode = JTF_PLSQL_API.g_create THEN
743 --ELSE
744 --END IF;
745
746 END check_offer_backdate_req_items;
747
748 PROCEDURE check_offer_backdate_FK_items(
749 p_offer_backdate_rec IN offer_backdate_rec_type,
750 x_return_status OUT NOCOPY VARCHAR2
751 )
752 IS
753 BEGIN
754 x_return_status := FND_API.g_ret_sts_success;
755
756 -- Enter custom code here
757
758 END check_offer_backdate_FK_items;
759
760 /*PROCEDURE check_offer_backdate_Lookup_items(
761 p_offer_backdate_rec IN offer_backdate_rec_type,
762 x_return_status OUT NOCOPY VARCHAR2
763 )
764 IS
765 BEGIN
766 x_return_status := FND_API.g_ret_sts_success;
767
768 -- Enter custom code here
769
770 END check_offer_backdate_Lookup_items;*/
771
772 PROCEDURE Check_offer_backdate_Items (
773 P_offer_backdate_rec IN offer_backdate_rec_type,
774 p_validation_mode IN VARCHAR2,
775 x_return_status OUT NOCOPY VARCHAR2
776 )
777 IS
778 BEGIN
779
780 -- Check Items Uniqueness API calls
781
782 check_offer_backdate_uk_items(
783 p_offer_backdate_rec => p_offer_backdate_rec,
784 p_validation_mode => p_validation_mode,
785 x_return_status => x_return_status);
786 IF x_return_status <> FND_API.g_ret_sts_success THEN
787 RETURN;
788 END IF;
789
790 -- Check Items Required/NOT NULL API calls
791
792 check_offer_backdate_req_items(
793 p_offer_backdate_rec => p_offer_backdate_rec,
794 p_validation_mode => p_validation_mode,
795 x_return_status => x_return_status);
796 IF x_return_status <> FND_API.g_ret_sts_success THEN
797 RETURN;
798 END IF;
799 -- Check Items Foreign Keys API calls
800
801 check_offer_backdate_FK_items(
802 p_offer_backdate_rec => p_offer_backdate_rec,
803 x_return_status => x_return_status);
804 IF x_return_status <> FND_API.g_ret_sts_success THEN
805 RETURN;
806 END IF;
807 -- Check Items Lookups
808
809 /* check_offer_backdate_Lookup_items(
810 p_offer_backdate_rec => p_offer_backdate_rec,
811 x_return_status => x_return_status);
812 IF x_return_status <> FND_API.g_ret_sts_success THEN
813 RETURN;
814 END IF; */
815
816 END Check_offer_backdate_Items;
817
818
819
820 PROCEDURE Complete_offer_backdate_Rec (
821 p_offer_backdate_rec IN offer_backdate_rec_type,
822 x_complete_rec OUT NOCOPY offer_backdate_rec_type)
823 IS
824 l_return_status VARCHAR2(1);
825
826 CURSOR c_complete IS
827 SELECT *
828 FROM ozf_offer_adjustments_b
829 WHERE offer_adjustment_id = p_offer_backdate_rec.offer_adjustment_id;
830 l_offer_backdate_rec c_complete%ROWTYPE;
831 BEGIN
832 x_complete_rec := p_offer_backdate_rec;
833
834
835 OPEN c_complete;
836 FETCH c_complete INTO l_offer_backdate_rec;
837 CLOSE c_complete;
838
839 -- offer_adjustment_id
840 IF p_offer_backdate_rec.offer_adjustment_id = FND_API.g_miss_num THEN
841 x_complete_rec.offer_adjustment_id := NULL;
842 END IF;
843 IF p_offer_backdate_rec.offer_adjustment_id IS NULL THEN
844 x_complete_rec.offer_adjustment_id := l_offer_backdate_rec.offer_adjustment_id;
845 END IF;
846
847 -- effective_date
848 IF p_offer_backdate_rec.effective_date = FND_API.g_miss_date THEN
849 x_complete_rec.effective_date := NULL;
850 END IF;
851 IF p_offer_backdate_rec.effective_date IS NULL THEN
852 x_complete_rec.effective_date := l_offer_backdate_rec.effective_date;
853 END IF;
854
855 -- approved_date
856 IF p_offer_backdate_rec.approved_date = FND_API.g_miss_date THEN
857 x_complete_rec.approved_date := NULL;
858 END IF;
859 IF p_offer_backdate_rec.approved_date IS NULL THEN
860 x_complete_rec.approved_date := l_offer_backdate_rec.approved_date;
861 END IF;
862
863 -- settlement_code
864 IF p_offer_backdate_rec.settlement_code = FND_API.g_miss_char THEN
865 x_complete_rec.settlement_code := NULL;
866 END IF;
867 IF p_offer_backdate_rec.settlement_code IS NULL THEN
868 x_complete_rec.settlement_code := l_offer_backdate_rec.settlement_code;
869 END IF;
870
871 -- status_code
872 IF p_offer_backdate_rec.status_code = FND_API.g_miss_char THEN
873 x_complete_rec.status_code := NULL;
874 END IF;
875 IF p_offer_backdate_rec.status_code IS NULL THEN
876 x_complete_rec.status_code := l_offer_backdate_rec.status_code;
877 END IF;
878
879 -- list_header_id
880 IF p_offer_backdate_rec.list_header_id = FND_API.g_miss_num THEN
881 x_complete_rec.list_header_id := NULL;
882 END IF;
883 IF p_offer_backdate_rec.list_header_id IS NULL THEN
884 x_complete_rec.list_header_id := l_offer_backdate_rec.list_header_id;
885 END IF;
886
887 -- version
888 IF p_offer_backdate_rec.version = FND_API.g_miss_num THEN
889 x_complete_rec.version := NULL;
890 END IF;
891 IF p_offer_backdate_rec.version IS NULL THEN
892 x_complete_rec.version := l_offer_backdate_rec.version;
893 END IF;
894
895 -- budget_adjusted_flag
896 IF p_offer_backdate_rec.budget_adjusted_flag = FND_API.g_miss_char THEN
897 x_complete_rec.budget_adjusted_flag := NULL;
898 END IF;
899 IF p_offer_backdate_rec.budget_adjusted_flag IS NULL THEN
900 x_complete_rec.budget_adjusted_flag := l_offer_backdate_rec.budget_adjusted_flag;
901 END IF;
902
903 -- comments
904 IF p_offer_backdate_rec.comments = FND_API.g_miss_char THEN
905 x_complete_rec.comments := NULL;
906 END IF;
907 IF p_offer_backdate_rec.comments IS NULL THEN
908 x_complete_rec.comments := l_offer_backdate_rec.comments;
909 END IF;
910
911 -- last_update_date
912 IF p_offer_backdate_rec.last_update_date = FND_API.g_miss_date THEN
913 x_complete_rec.last_update_date := NULL;
914 END IF;
915 IF p_offer_backdate_rec.last_update_date IS NULL THEN
916 x_complete_rec.last_update_date := l_offer_backdate_rec.last_update_date;
917 END IF;
918
919 -- last_updated_by
920 IF p_offer_backdate_rec.last_updated_by = FND_API.g_miss_num THEN
921 x_complete_rec.last_updated_by := NULL;
922 END IF;
923 IF p_offer_backdate_rec.last_updated_by IS NULL THEN
924 x_complete_rec.last_updated_by := l_offer_backdate_rec.last_updated_by;
925 END IF;
926
927 -- creation_date
928 IF p_offer_backdate_rec.creation_date = FND_API.g_miss_date THEN
929 x_complete_rec.creation_date := NULL;
930 END IF;
931 IF p_offer_backdate_rec.creation_date IS NULL THEN
932 x_complete_rec.creation_date := l_offer_backdate_rec.creation_date;
933 END IF;
934
935 -- created_by
936 IF p_offer_backdate_rec.created_by = FND_API.g_miss_num THEN
937 x_complete_rec.created_by := NULL;
938 END IF;
939 IF p_offer_backdate_rec.created_by IS NULL THEN
940 x_complete_rec.created_by := l_offer_backdate_rec.created_by;
941 END IF;
942
943 -- last_update_login
944 IF p_offer_backdate_rec.last_update_login = FND_API.g_miss_num THEN
945 x_complete_rec.last_update_login := NULL;
946 END IF;
947 IF p_offer_backdate_rec.last_update_login IS NULL THEN
948 x_complete_rec.last_update_login := l_offer_backdate_rec.last_update_login;
949 END IF;
950
951 -- object_version_number
952 IF p_offer_backdate_rec.object_version_number = FND_API.g_miss_num THEN
953 x_complete_rec.object_version_number := NULL;
954 END IF;
955 IF p_offer_backdate_rec.object_version_number IS NULL THEN
956 x_complete_rec.object_version_number := l_offer_backdate_rec.object_version_number;
957 END IF;
958
959 -- security_group_id
960 IF p_offer_backdate_rec.security_group_id = FND_API.g_miss_num THEN
961 x_complete_rec.security_group_id := NULL;
962 END IF;
963 IF p_offer_backdate_rec.security_group_id IS NULL THEN
964 x_complete_rec.security_group_id := l_offer_backdate_rec.security_group_id;
965 END IF;
966 -- Note: Developers need to modify the procedure
967 -- to handle any business specific requirements.
968 END Complete_offer_backdate_Rec;
969 PROCEDURE Validate_offer_backdate(
970 p_api_version_number IN NUMBER,
971 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
972 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
973 p_offer_backdate_rec IN offer_backdate_rec_type,
974 p_validation_mode IN VARCHAR2,
975 x_return_status OUT NOCOPY VARCHAR2,
976 x_msg_count OUT NOCOPY NUMBER,
977 x_msg_data OUT NOCOPY VARCHAR2
978 )
979 IS
980 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Offer_Backdate';
981 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
982 l_object_version_number NUMBER;
983 l_offer_backdate_rec OZF_Offer_Backdate_PVT.offer_backdate_rec_type;
984
985 BEGIN
986 -- Standard Start of API savepoint
987 SAVEPOINT VALIDATE_Offer_Backdate_;
988
989 -- Standard call to check for call compatibility.
990 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
991 p_api_version_number,
992 l_api_name,
993 G_PKG_NAME)
994 THEN
995 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
996 END IF;
997
998 -- Initialize message list if p_init_msg_list is set to TRUE.
999 IF FND_API.to_Boolean( p_init_msg_list )
1000 THEN
1001 FND_MSG_PUB.initialize;
1002 END IF;
1003 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1004 Check_offer_backdate_Items(
1005 p_offer_backdate_rec => p_offer_backdate_rec,
1006 p_validation_mode => p_validation_mode,
1007 x_return_status => x_return_status
1008 );
1009
1010 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1011 RAISE FND_API.G_EXC_ERROR;
1012 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1013 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1014 END IF;
1015 END IF;
1016
1017 Complete_offer_backdate_Rec(
1018 p_offer_backdate_rec => p_offer_backdate_rec,
1019 x_complete_rec => l_offer_backdate_rec
1020 );
1021
1022 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1023 Validate_offer_backdate_Rec(
1024 p_api_version_number => 1.0,
1025 p_init_msg_list => FND_API.G_FALSE,
1026 x_return_status => x_return_status,
1027 x_msg_count => x_msg_count,
1028 x_msg_data => x_msg_data,
1029 p_offer_backdate_rec => l_offer_backdate_rec);
1030
1031 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1032 RAISE FND_API.G_EXC_ERROR;
1033 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1034 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1035 END IF;
1036 END IF;
1037
1038
1039 -- Debug Message
1040 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1041
1042
1043 -- Initialize API return status to SUCCESS
1044 x_return_status := FND_API.G_RET_STS_SUCCESS;
1045
1046
1047 -- Debug Message
1048 OZF_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1049
1050 -- Standard call to get message count and if count is 1, get message info.
1051 FND_MSG_PUB.Count_And_Get
1052 (p_count => x_msg_count,
1053 p_data => x_msg_data
1054 );
1055 EXCEPTION
1056
1057 WHEN OZF_Utility_PVT.resource_locked THEN
1058 x_return_status := FND_API.g_ret_sts_error;
1059 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1060
1061 WHEN FND_API.G_EXC_ERROR THEN
1062 ROLLBACK TO VALIDATE_Offer_Backdate_;
1063 x_return_status := FND_API.G_RET_STS_ERROR;
1064 -- Standard call to get message count and if count=1, get the message
1065 FND_MSG_PUB.Count_And_Get (
1066 p_encoded => FND_API.G_FALSE,
1067 p_count => x_msg_count,
1068 p_data => x_msg_data
1069 );
1070
1071 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1072 ROLLBACK TO VALIDATE_Offer_Backdate_;
1073 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1074 -- Standard call to get message count and if count=1, get the message
1075 FND_MSG_PUB.Count_And_Get (
1076 p_encoded => FND_API.G_FALSE,
1077 p_count => x_msg_count,
1078 p_data => x_msg_data
1079 );
1080
1081 WHEN OTHERS THEN
1082 ROLLBACK TO VALIDATE_Offer_Backdate_;
1083 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1084 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1085 THEN
1086 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1087 END IF;
1088 -- Standard call to get message count and if count=1, get the message
1089 FND_MSG_PUB.Count_And_Get (
1090 p_encoded => FND_API.G_FALSE,
1091 p_count => x_msg_count,
1092 p_data => x_msg_data
1093 );
1094 End Validate_Offer_Backdate;
1095
1096
1097 PROCEDURE Validate_offer_backdate_rec(
1098 p_api_version_number IN NUMBER,
1099 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1100 x_return_status OUT NOCOPY VARCHAR2,
1101 x_msg_count OUT NOCOPY NUMBER,
1102 x_msg_data OUT NOCOPY VARCHAR2,
1103 p_offer_backdate_rec IN offer_backdate_rec_type
1104 )
1105 IS
1106 BEGIN
1107 -- Initialize message list if p_init_msg_list is set to TRUE.
1108 IF FND_API.to_Boolean( p_init_msg_list )
1109 THEN
1110 FND_MSG_PUB.initialize;
1111 END IF;
1112
1113 -- Initialize API return status to SUCCESS
1114 x_return_status := FND_API.G_RET_STS_SUCCESS;
1115
1116 -- Hint: Validate data
1117 -- If data not valid
1118 -- THEN
1119 -- x_return_status := FND_API.G_RET_STS_ERROR;
1120
1121 -- Debug Message
1122 OZF_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
1123 -- Standard call to get message count and if count is 1, get message info.
1124 FND_MSG_PUB.Count_And_Get
1125 (p_count => x_msg_count,
1126 p_data => x_msg_data
1127 );
1128 END Validate_offer_backdate_Rec;
1129
1130 PROCEDURE Create_Initial_Adj(
1131 p_api_version IN NUMBER,
1132 p_init_msg_list IN VARCHAR2 := FND_API.g_false,
1133 p_commit IN VARCHAR2 := FND_API.g_false,
1134 p_obj_id IN NUMBER,
1135 p_obj_type IN VARCHAR2 ,
1136 x_return_status OUT NOCOPY VARCHAR2,
1137 x_msg_count OUT NOCOPY NUMBER,
1138 x_msg_data OUT NOCOPY VARCHAR2
1139 )
1140 IS
1141
1142 l_OFFER_ADJUSTMENT_ID NUMBER;
1143 l_OFFER_ADJUSTMENT_LINE_ID NUMBER;
1144 l_dummy NUMBER;
1145 l_pricing_attribute_id NUMBER;
1146 l_list_line_id NUMBER;
1147 l_arithmetic_operator VARCHAR2(30);
1148 l_arithmetic_name VARCHAR2(30);
1149 l_operand NUMBER;
1150 L_API_NAME CONSTANT VARCHAR2(30) := 'CREATE_INITIAL_ADJ';
1151 l_count NUMBER := 0;
1152 CURSOR c_id IS
1153 SELECT ozf_OFFER_ADJUSTMENTS_B_s.NEXTVAL
1154 FROM dual;
1155
1156 CURSOR c_line_id IS
1157 SELECT ozf_OFFER_ADJUSTMENT_LINES_s.NEXTVAL
1158 FROM dual;
1159
1160 CURSOR c_id_exists (l_id IN NUMBER) IS
1161 SELECT 1
1162 FROM ozf_OFFER_ADJUSTMENTS_B
1163 WHERE OFFER_ADJUSTMENT_ID = l_id;
1164
1165 CURSOR c_offer_data (l_id IN NUMBER) IS
1166 select qp.pricing_attribute_id,
1167 ql.list_line_id,
1168 ql.arithmetic_operator,
1169 ql.operand
1170 from qp_pricing_attributes qp,
1171 qp_list_lines ql
1172 where ql.list_header_id = l_id
1173 and qp.list_header_id = l_id -- julou added for sql performance xxfix
1174 and ql.list_line_id = qp.list_line_id
1175 and qp.excluder_flag = 'N';
1176
1177
1178 BEGIN
1179
1180 LOOP
1181 l_dummy := NULL;
1182 OPEN c_id;
1183 FETCH c_id INTO l_OFFER_ADJUSTMENT_ID;
1184 CLOSE c_id;
1185
1186 OPEN c_id_exists(l_OFFER_ADJUSTMENT_ID);
1187 FETCH c_id_exists INTO l_dummy;
1188 CLOSE c_id_exists;
1189 EXIT WHEN l_dummy IS NULL;
1190 END LOOP;
1191
1192 INSERT INTO ozf_OFFER_ADJUSTMENTS_B(
1193 offer_adjustment_id,
1194 list_header_id,
1195 status_code,
1196 last_update_date,
1197 last_updated_by,
1198 creation_date,
1199 created_by,
1200 last_update_login,
1201 object_version_number
1202 ) VALUES (
1203 l_OFFER_ADJUSTMENT_ID,
1204 p_obj_id,
1205 'DRAFT',
1206 SYSDATE,
1207 FND_GLOBAL.USER_ID,
1208 SYSDATE,
1209 FND_GLOBAL.USER_ID,
1210 FND_GLOBAL.CONC_LOGIN_ID,
1211 1
1212 );
1213
1214 OPEN c_offer_data(p_obj_id);
1215 LOOP
1216 FETCH c_offer_data INTO l_pricing_attribute_id,l_list_line_id,l_arithmetic_operator,l_operand;
1217 LOOP
1218 l_dummy := NULL;
1219 OPEN c_id;
1220 FETCH c_id INTO l_OFFER_ADJUSTMENT_LINE_ID;
1221 CLOSE c_id;
1222
1223 OPEN c_id_exists(l_OFFER_ADJUSTMENT_LINE_ID);
1224 FETCH c_id_exists INTO l_dummy;
1225 CLOSE c_id_exists;
1226 EXIT WHEN l_dummy IS NULL;
1227 END LOOP;
1228 EXIT WHEN c_offer_data%NOTFOUND;
1229
1230 INSERT INTO ozf_OFFER_ADJUSTMENT_LINES(
1231 offer_adjustment_line_id,
1232 offer_adjustment_id,
1233 list_line_id,
1234 arithmetic_operator,
1235 original_discount,
1236 last_update_date,
1237 last_updated_by,
1238 creation_date,
1239 created_by,
1240 last_update_login,
1241 object_version_number
1242 ) VALUES (
1243 l_OFFER_ADJUSTMENT_LINE_ID,
1244 l_OFFER_ADJUSTMENT_ID,
1245 l_list_line_id,
1246 l_arithmetic_operator,
1247 l_operand,
1248 SYSDATE,
1249 FND_GLOBAL.USER_ID,
1250 SYSDATE,
1251 FND_GLOBAL.USER_ID,
1252 FND_GLOBAL.CONC_LOGIN_ID,
1253 1
1254 );
1255
1256 -- process data record
1257 END LOOP;
1258 CLOSE c_offer_data;
1259
1260 EXCEPTION
1261
1262 WHEN OTHERS THEN
1263 x_return_status := FND_API.g_ret_sts_unexp_error;
1264
1265 FND_MESSAGE.set_name('OZF', 'OZF_CREATE_OFFR_ADJ_FAILED');
1266 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
1267 FND_MSG_PUB.add;
1268
1269 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
1270 THEN
1271 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
1272 END IF;
1273
1274 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
1275 p_count => x_msg_count,
1276 p_data => x_msg_data);
1277
1278 END Create_Initial_Adj;
1279
1280 -------------------------------------------------------
1281 -- Start of Comments
1282 --
1283 -- NAME
1284 -- process_new_adjustments
1285 --
1286 -- PURPOSE
1287 -- This Procedure Activates the Discount Lines added to an Offer thru. Offer Adjustment.
1288 -- this is supposed to be called internally by update_offer_discounts
1289 --
1290 -- IN
1291 -- p_init_msg_list IN VARCHAR2,
1292 -- p_api_version IN NUMBER,
1293 -- p_commit IN VARCHAR2,
1294 -- p_offer_adjustment_id IN NUMBER
1295 --
1296 -- OUT
1297 -- x_return_status OUT VARCHAR2,
1298 -- x_msg_count OUT NUMBER,
1299 -- x_msg_data OUT VARCHAR2,
1300 --
1301 -- NOTES
1302 --
1303 -- HISTORY
1304 -- Mon Dec 01 2003:7/26 PM rssharma created
1305 -- End of Comments
1306 ---------------------------------------------------------
1307 procedure process_new_adjustments(
1308 p_init_msg_list IN VARCHAR2 := FND_API.g_false
1309 ,p_api_version IN NUMBER
1310 ,p_commit IN VARCHAR2 := FND_API.g_false
1311 ,x_return_status OUT NOCOPY VARCHAR2
1312 ,x_msg_count OUT NOCOPY NUMBER
1313 ,x_msg_data OUT NOCOPY VARCHAR2
1314 ,p_offer_adjustment_id IN NUMBER
1315 )
1316 IS
1317 l_api_name VARCHAR2(30) := 'process_new_adjustments';
1318 l_qp_list_header_id NUMBER;
1319 l_error_location NUMBER;
1320 l_modifier_list_rec ozf_offer_pvt.modifier_list_rec_type;
1321 l_modifier_line_tbl ozf_offer_pvt.modifier_line_tbl_type ;
1322
1323 l_line_ctr NUMBER := 1;
1324 l_offer_type VARCHAR2(30);
1325
1326 CURSOR get_offer_type IS
1327 SELECT o.offer_type
1328 FROM ozf_offers o,
1329 ozf_offer_adjustments_vl a
1330 WHERE a.offer_adjustment_id = p_offer_adjustment_id
1331 and a.list_header_id = o.qp_list_header_id ;
1332
1333 -- julou backdated offer for Promotional Goods, Trade Deal, Tiered Discount
1334 CURSOR c_qp_line_detail(l_list_line_id NUMBER) IS
1335 SELECT *
1336 FROM qp_list_lines
1337 WHERE list_line_id = l_list_line_id;
1338 l_qp_line_detail c_qp_line_detail%ROWTYPE;
1339 l_qp_rltd_line_detail c_qp_line_detail%ROWTYPE;
1340
1341 CURSOR c_effectiveDate(cp_offerAdjustmentId NUMBER) IS
1342 SELECT effective_date
1343 FROM ozf_offer_adjustments_b
1344 WHERE offer_adjustment_id = cp_offerAdjustmentId;
1345 l_effectiveDate DATE;
1346
1347
1348 CURSOR c_adj_lines IS
1349 SELECT list_line_id
1350 ,list_line_id_td
1351 ,list_header_id
1352 , arithmetic_operator
1353 , discount_end_date
1354 FROM ozf_offer_adjustment_lines
1355 WHERE offer_adjustment_id = p_offer_adjustment_id
1356 AND created_from_adjustments = 'Y';
1357
1358 CURSOR c_rltd_line(p_list_header_id NUMBER, p_list_line_id NUMBER) IS
1359 SELECT related_deal_lines_id
1360 ,modifier_id
1361 ,related_modifier_id
1362 ,object_version_number
1363 FROM ozf_related_deal_lines
1364 WHERE qp_list_header_id = p_list_header_id
1365 AND modifier_id = p_list_line_id;
1366 l_rltd_line c_rltd_line%ROWTYPE;
1367
1368 l_index NUMBER := 0;
1369 l_dummy NUMBER;
1370 -- julou end
1371 BEGIN
1372
1373 ozf_utility_pvt.debug_message('inside process new adjustments');
1374 IF Fnd_Api.to_boolean(p_init_msg_list)
1375 THEN
1376
1377 Fnd_Msg_Pub.initialize;
1378
1379
1380 END IF;
1381
1382 IF NOT Fnd_Api.compatible_api_call ( p_api_version,
1383 p_api_version,
1384 l_api_name,
1385 g_pkg_name
1386 )
1387 THEN
1388
1389 RAISE Fnd_Api.g_exc_unexpected_error;
1390
1391 END IF;
1392
1393 x_return_status := Fnd_Api.g_ret_sts_success;
1394
1395 -- julou backdated offer for Promotional Goods, Trade Deal, Tiered Discount
1396 -- set operation to NULL should prevent operation on list header and offer tables
1397 l_modifier_list_rec.offer_operation := NULL;
1398 l_modifier_list_rec.modifier_operation := NULL;
1399
1400 OPEN get_offer_type;
1401 FETCH get_offer_type INTO l_offer_type;
1402 CLOSE get_offer_type;
1403
1404 OPEN c_effectiveDate(p_offer_adjustment_id);
1405 FETCH c_effectiveDate INTO l_effectiveDate;
1406 CLOSE c_effectiveDate;
1407 ozf_utility_pvt.debug_message('offer_type is '||l_offer_type);
1408 IF l_offer_type IN ('OID', 'ACCRUAL', 'OFF_INVOICE', 'ORDER', 'DEAL','VOLUME_OFFER') THEN
1409 FOR l_adj_line IN c_adj_lines LOOP
1410 -- initialize
1411 l_qp_line_detail := NULL;
1412 l_qp_rltd_line_detail := NULL;
1413 l_rltd_line := NULL;
1414
1415 l_index := l_index + 1;
1416
1417 l_modifier_line_tbl(l_index).operation := 'UPDATE';
1418 l_modifier_line_tbl(l_index).list_header_id := l_adj_line.list_header_id;--l_qp_line_detail.list_header_id;
1419 l_modifier_line_tbl(l_index).inactive_flag := 'Y';
1420 l_modifier_line_tbl(l_index).end_date_active := l_adj_line.discount_end_date;
1421 l_modifier_line_tbl(l_index).start_date_active := l_effectiveDate;
1422 l_modifier_line_tbl(l_index).arithmetic_operator := l_adj_line.arithmetic_operator;
1423 IF l_offer_type IN ('OID', 'ACCRUAL', 'OFF_INVOICE', 'ORDER','VOLUME_OFFER') THEN
1424 l_modifier_line_tbl(l_index).list_line_id := l_adj_line.list_line_id;
1425 ELSIF l_offer_type = 'DEAL' THEN
1426 OPEN c_rltd_line(l_adj_line.list_header_id,l_adj_line.list_line_id);
1427 FETCH c_rltd_line INTO l_rltd_line;
1428 CLOSE c_rltd_line;
1429 l_modifier_line_tbl(l_index).qd_related_deal_lines_id := l_rltd_line.related_deal_lines_id;
1430 l_modifier_line_tbl(l_index).list_line_id := l_adj_line.list_line_id;
1431 l_modifier_line_tbl(l_index).qd_object_version_number := l_rltd_line.object_version_number;
1432 l_modifier_line_tbl(l_index).qd_list_line_id := l_adj_line.list_line_id_td;
1433 END IF;
1434 END LOOP;
1435 ozf_offer_pvt.process_modifiers ( p_init_msg_list
1436 ,p_api_version
1437 ,p_commit
1438 ,x_return_status
1439 ,x_msg_count
1440 ,x_msg_data
1441 ,l_offer_type
1442 ,l_modifier_list_rec
1443 ,l_modifier_line_tbl
1444 ,l_qp_list_header_id
1445 ,l_error_location
1446 );
1447 END IF; -- end l_offer_type
1448 END process_new_adjustments;
1449
1450
1451 -------------------------------------------------------------------------------------------
1452 -- Procedure :
1453 -- Name : update_volume_offer_discounts
1454 -- Updates the tiers and Discounts for Volume Offer tiers
1455 -------------------------------------------------------------------------------------------
1456 PROCEDURE update_volume_offer_discounts
1457 (
1458 p_init_msg_list IN VARCHAR2 := FND_API.g_false
1459 ,p_api_version IN NUMBER
1460 ,p_commit IN VARCHAR2 := FND_API.g_false
1461 ,x_return_status OUT NOCOPY VARCHAR2
1462 ,x_msg_count OUT NOCOPY NUMBER
1463 ,x_msg_data OUT NOCOPY VARCHAR2
1464 ,p_offer_adjustment_id IN NUMBER
1465 )
1466 IS
1467 l_qp_list_header_id NUMBER;
1468 l_error_location NUMBER;
1469 l_modifier_list_rec ozf_offer_pvt.modifier_list_rec_type;
1470 l_modifier_line_tbl ozf_offer_pvt.modifier_line_tbl_type ;
1471
1472 l_offer_type VARCHAR2(30);
1473
1474 CURSOR c_qp_line_detail(p_list_header_id NUMBER) IS
1475 SELECT *
1476 FROM qp_list_lines
1477 WHERE list_header_id = p_list_header_id;
1478 l_qp_line_detail c_qp_line_detail%ROWTYPE;
1479
1480
1481
1482 CURSOR c_adj_lines IS
1483 SELECT modified_discount , qp_list_header_id
1484 FROM OZF_OFFER_ADJUSTMENT_TIERS
1485 WHERE
1486 offer_adjustment_id = p_offer_adjustment_id
1487 AND original_discount =
1488 (SELECT operand FROM qp_list_lines WHERE list_header_id
1489 = (select qp_list_header_id FROM ozf_offer_adjustment_tiers WHERE offer_adjustment_id = p_offer_adjustment_id and rownum < 2)
1490 and rownum < 2)
1491 ;
1492
1493
1494 CURSOR c_pricing_attr(l_list_header_id NUMBER, l_list_line_id NUMBER) IS
1495 SELECT pricing_attribute_id
1496 ,product_attribute_context
1497 ,product_attribute
1498 ,product_attr_value
1499 ,product_uom_code
1500 ,pricing_attribute_context
1501 ,pricing_attribute
1502 ,pricing_attr_value_from
1503 ,pricing_attr_value_to
1504 ,excluder_flag
1505 FROM qp_pricing_attributes
1506 WHERE list_header_id = l_list_header_id
1507 AND list_line_id = l_list_line_id;
1508
1509 CURSOR c_qualifier(l_list_header_id NUMBER, l_list_line_id NUMBER) IS
1510 SELECT qualifier_id
1511 ,qualifier_attr_value
1512 ,qualifier_attr_value_to
1513 FROM qp_qualifiers
1514 WHERE list_header_id = l_list_header_id
1515 AND list_line_id = l_list_line_id;
1516
1517 CURSOR c_adj_tiers IS
1518 SELECT volume_offer_tiers_id
1519 ,modified_discount
1520 FROM ozf_offer_adjustment_tiers
1521 WHERE offer_adjustment_id = p_offer_adjustment_id;
1522
1523 CURSOR c_tier_detail(l_tier_id NUMBER) IS
1524 SELECT *
1525 FROM ozf_volume_offer_tiers
1526 WHERE volume_offer_tiers_id = l_tier_id;
1527 l_tier_detail c_tier_detail%ROWTYPE;
1528
1529 l_vol_offr_tier_rec ozf_vol_offr_pvt.vol_offr_tier_rec_type;
1530 l_index NUMBER := 0;
1531 l_dummy NUMBER;
1532 l_adj_lines c_adj_lines%rowtype;
1533
1534 BEGIN
1535 x_return_status := Fnd_Api.g_ret_sts_success;
1536 ---------Update Volume Offer tiers ----------------------------
1537 FOR l_adj_tier IN c_adj_tiers LOOP
1538 OPEN c_tier_detail(l_adj_tier.volume_offer_tiers_id);
1539 FETCH c_tier_detail INTO l_tier_detail;
1540 CLOSE c_tier_detail;
1541
1542 l_vol_offr_tier_rec.volume_offer_tiers_id := l_tier_detail.volume_offer_tiers_id;
1543 l_vol_offr_tier_rec.qp_list_header_id := l_tier_detail.qp_list_header_id;
1544 l_vol_offr_tier_rec.discount_type_code := l_tier_detail.discount_type_code;
1545 l_vol_offr_tier_rec.break_type_code := l_tier_detail.break_type_code;
1546 l_vol_offr_tier_rec.tier_value_from := l_tier_detail.tier_value_from;
1547 l_vol_offr_tier_rec.tier_value_to := l_tier_detail.tier_value_to;
1548 l_vol_offr_tier_rec.volume_type := l_tier_detail.volume_type;
1549 l_vol_offr_tier_rec.active := l_tier_detail.active;
1550 l_vol_offr_tier_rec.uom_code := l_tier_detail.uom_code;
1551 l_vol_offr_tier_rec.object_version_number := l_tier_detail.object_version_number;
1552 l_vol_offr_tier_rec.discount := l_adj_tier.modified_discount;
1553
1554 OZF_Vol_Offr_PVT.Update_Vol_Offr(p_api_version
1555 ,p_init_msg_list
1556 ,p_commit
1557 ,FND_API.G_VALID_LEVEL_FULL -- validation level
1558 ,x_return_status
1559 ,x_msg_count
1560 ,x_msg_data
1561 ,l_vol_offr_tier_rec
1562 ,l_dummy);
1563 END LOOP;
1564 -------Done updating Volume Offer Tiers-----------------
1565 open c_adj_lines;
1566 fetch c_adj_lines into l_adj_lines;
1567 close c_adj_lines;
1568
1569 l_modifier_list_rec.qp_list_header_id := l_adj_lines.qp_list_header_id;
1570 l_offer_type := 'VOLUME_OFFER';
1571 -- initialize
1572 l_qp_line_detail := NULL;
1573 FOR l_qp_line_detail in c_qp_line_detail(l_adj_lines.qp_list_header_id) LOOP
1574 l_index := l_index + 1;
1575 l_modifier_line_tbl(l_index).operation := 'UPDATE';
1576 l_modifier_line_tbl(l_index).list_line_id := l_qp_line_detail.list_line_id;
1577 l_modifier_line_tbl(l_index).list_header_id := l_qp_line_detail.list_header_id;
1578 l_modifier_line_tbl(l_index).list_line_type_code := l_qp_line_detail.list_line_type_code;
1579 l_modifier_line_tbl(l_index).start_date_active := l_qp_line_detail.start_date_active;
1580 l_modifier_line_tbl(l_index).end_date_active := l_qp_line_detail.end_date_active;
1581 IF l_qp_line_detail.end_date_active <> FND_API.G_MISS_DATE
1582 AND l_qp_line_detail.end_date_active IS NOT NULL
1583 THEN
1584 l_modifier_line_tbl(l_index).inactive_flag := 'N';
1585 ELSE
1586 l_modifier_line_tbl(l_index).inactive_flag := 'Y';
1587 END IF;
1588 -- end benefit quantity
1589
1590 -- get pricing attribute from qp_pricing_attributes
1591 OPEN c_pricing_attr(l_qp_line_detail.list_header_id, l_qp_line_detail.list_line_id);
1592 FETCH c_pricing_attr INTO l_modifier_line_tbl(l_index).pricing_attribute_id
1593 ,l_modifier_line_tbl(l_index).product_attribute_context
1594 ,l_modifier_line_tbl(l_index).product_attr
1595 ,l_modifier_line_tbl(l_index).product_attr_val
1596 ,l_modifier_line_tbl(l_index).product_uom_code
1597 ,l_modifier_line_tbl(l_index).pricing_attribute_context
1598 ,l_modifier_line_tbl(l_index).pricing_attr
1599 ,l_modifier_line_tbl(l_index).pricing_attr_value_from
1600 ,l_modifier_line_tbl(l_index).pricing_attr_value_to
1601 ,l_modifier_line_tbl(l_index).excluder_flag;
1602 CLOSE c_pricing_attr;
1603
1604 OPEN c_qualifier(l_qp_line_detail.list_header_id, l_qp_line_detail.list_line_id);
1605 FETCH c_qualifier INTO l_modifier_line_tbl(l_index).qualifier_id
1606 ,l_modifier_line_tbl(l_index).order_value_from
1607 ,l_modifier_line_tbl(l_index).order_value_to;
1608 CLOSE c_qualifier;
1609
1610 l_modifier_line_tbl(l_index).operand := l_adj_lines.modified_discount;
1611 l_modifier_line_tbl(l_index).arithmetic_operator := l_qp_line_detail.arithmetic_operator;
1612
1613 END LOOP;
1614 -- calling offer API to update lines
1615 ozf_offer_pvt.process_modifiers ( p_init_msg_list
1616 ,p_api_version
1617 ,p_commit
1618 ,x_return_status
1619 ,x_msg_count
1620 ,x_msg_data
1621 ,l_offer_type
1622 ,l_modifier_list_rec
1623 ,l_modifier_line_tbl
1624 ,l_qp_list_header_id
1625 ,l_error_location
1626 );
1627 ----------------------------------------------------------------------------------------------
1628 END update_volume_offer_discounts;
1629
1630
1631 /*
1632 Done with normal cases without exclusions and apply discounts = n
1633 currently only inserts without checking for duplicates, so depends on judicious calling of the api
1634 */
1635 PROCEDURE process_vo_adj_products
1636 (
1637 p_init_msg_list IN VARCHAR2 := FND_API.g_false
1638 , p_api_version IN NUMBER
1639 , p_commit IN VARCHAR2 := FND_API.g_false
1640 , x_return_status OUT NOCOPY VARCHAR2
1641 , x_msg_count OUT NOCOPY NUMBER
1642 , x_msg_data OUT NOCOPY VARCHAR2
1643 , p_offer_adjustment_id IN NUMBER
1644 )
1645 IS
1646
1647
1648 l_vo_prod_rec OZF_Volume_Offer_disc_PVT.vo_prod_rec_type;
1649
1650 l_off_discount_product_id NUMBER;
1651 l_api_name CONSTANT VARCHAR2(30) := 'process_vo_adj_products';
1652 l_api_version_number CONSTANT NUMBER := 1.0;
1653
1654
1655 l_reln_rec OZF_OFFER_PVT.ozf_qp_reln_rec_type ;
1656
1657 BEGIN
1658 -- initialize
1659 SAVEPOINT process_vo_adj_products;
1660 IF NOT FND_API.COMPATIBLE_API_CALL
1661 (
1662 p_api_version
1663 , l_api_version_number
1664 , l_api_name
1665 , g_pkg_name
1666 )
1667 THEN
1668 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1669 END IF;
1670 x_return_status := FND_API.G_RET_STS_SUCCESS;
1671
1672 ozf_utility_pvt.debug_message('Private API:'|| l_api_name || ' Start');
1673 -- create new discont products
1674 DECLARE
1675 CURSOR C_ADJ_PROD(p_adjustment_id NUMBER) IS
1676 SELECT decode(apply_discount_flag,'N',decode(include_volume_flag,'N','Y','N'),'N') excluder_flag
1677 , offer_discount_line_id
1678 , offer_id
1679 , product_context
1680 , product_attribute
1681 , product_attr_value
1682 , apply_discount_flag
1683 , include_volume_flag
1684 FROM ozf_offer_adjustment_products a , ozf_offer_adjustments_b b, ozf_offers c
1685 WHERE a.offer_adjustment_id = b.offer_adjustment_id
1686 AND b.list_header_id = c.qp_list_header_id
1687 AND a.offer_adjustment_id = p_adjustment_id;
1688
1689 BEGIN
1690 FOR l_adj_prod in c_adj_prod(p_offer_adjustment_id) LOOP
1691 l_vo_prod_rec.excluder_flag := l_adj_prod.excluder_flag ;
1692 l_vo_prod_rec.offer_discount_line_id := l_adj_prod.offer_discount_line_id;
1693 l_vo_prod_rec.offer_id := l_adj_prod.offer_id;
1694 l_vo_prod_rec.product_context := l_adj_prod.product_context;
1695 l_vo_prod_rec.product_attribute := l_adj_prod.product_attribute;
1696 l_vo_prod_rec.product_attr_value := l_adj_prod.product_attr_value;
1697 l_vo_prod_rec.apply_discount_flag := l_adj_prod.apply_discount_flag;
1698 l_vo_prod_rec.include_volume_flag := l_adj_prod.include_volume_flag;
1699 ozf_utility_pvt.debug_message('Excluder Flag :'||l_vo_prod_rec.excluder_flag);
1700 ozf_utility_pvt.debug_message('Offer Discount Line Id :'||l_vo_prod_rec.offer_discount_line_id);
1701 ozf_utility_pvt.debug_message('Offer Id :'||l_vo_prod_rec.offer_id);
1702 ozf_utility_pvt.debug_message('Product Context :'||l_vo_prod_rec.product_context);
1703 ozf_utility_pvt.debug_message('product_attribute :'||l_vo_prod_rec.product_attribute);
1704 ozf_utility_pvt.debug_message('Product Attr val :'||l_vo_prod_rec.product_attr_value);
1705 ozf_utility_pvt.debug_message('Apply discount Flag :'||l_vo_prod_rec.apply_discount_flag);
1706 ozf_utility_pvt.debug_message('Include Volume :'||l_vo_prod_rec.include_volume_flag);
1707 OZF_Volume_Offer_disc_PVT.Create_vo_product(
1708 p_api_version_number => 1.0
1709 , p_init_msg_list => FND_API.G_FALSE
1710 , p_commit => FND_API.G_FALSE
1711 , p_validation_level => FND_API.G_VALID_LEVEL_FULL
1712
1713 , x_return_status => x_return_status
1714 , x_msg_count => x_msg_count
1715 , x_msg_data => x_msg_data
1716
1717 , p_vo_prod_rec => l_vo_prod_rec
1718 , x_off_discount_product_id => l_off_discount_product_id
1719 );
1720 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1721 RAISE FND_API.G_EXC_ERROR;
1722 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1723 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1724 END IF;
1725
1726 END LOOP;
1727 END;
1728
1729 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1730 RAISE FND_API.G_EXC_ERROR;
1731 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1732 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1733 END IF;
1734
1735 ozf_utility_pvt.debug_message('Return status 1 is '||x_return_status);
1736 -- loop thru new products and create new list lines and pricing attributes
1737 declare
1738 CURSOR c_adj_prod_disc(p_adjustment_id NUMBER)
1739 IS
1740 SELECT a.product_context
1741 , a.product_attribute
1742 , a.product_attr_value
1743 , c.uom_code
1744 , c.volume_type
1745 , d.qp_list_header_id list_header_id
1746 , d.volume_offer_type
1747 , d.modifier_level_code
1748 , b.offer_discount_line_id
1749 , c.discount_type
1750 , e.effective_date
1751 , a.apply_discount_flag
1752 , a.include_volume_flag
1753 FROM ozf_offer_adjustment_products a, ozf_offer_discount_products b , ozf_offer_discount_lines c , ozf_offers d , ozf_offer_adjustments_b e
1754 WHERE a.offer_discount_line_id = c.offer_discount_line_id
1755 AND b.offer_discount_line_id = c.offer_discount_line_id
1756 AND a.product_context = b.product_context
1757 AND a.product_attribute = b.product_attribute
1758 AND a.product_attr_value = b.product_attr_value
1759 AND c.offer_id = d.offer_id
1760 AND e.offer_adjustment_id = a.offer_adjustment_id
1761 --AND b.offer_discount_line_id = p_offer_discount_line_id
1762 AND a.offer_adjustment_id = p_offer_adjustment_id;
1763
1764 CURSOR c_adj_prod_dis(p_offer_discount_line_id number) IS
1765 SELECT volume_from, volume_to, discount_type, discount
1766 FROM ozf_offer_discount_lines where parent_discount_line_id = p_offer_discount_line_id;
1767
1768 i NUMBER:= 0;
1769 k NUMBER := 0;
1770 l_modifiers_tbl Qp_Modifiers_Pub.modifiers_tbl_type;
1771 l_pricing_attr_tbl Qp_Modifiers_Pub.pricing_attr_tbl_type;
1772 v_modifier_list_rec qp_modifiers_pub.modifier_list_rec_type;
1773 v_modifier_list_val_rec qp_modifiers_pub.modifier_list_val_rec_type;
1774 v_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
1775 v_modifiers_val_tbl qp_modifiers_pub.modifiers_val_tbl_type;
1776 v_qualifiers_tbl qp_qualifier_rules_pub.qualifiers_tbl_type;
1777 v_qualifiers_val_tbl qp_qualifier_rules_pub.qualifiers_val_tbl_type;
1778 v_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
1779 v_pricing_attr_val_tbl qp_modifiers_pub.pricing_attr_val_tbl_type;
1780 l_control_rec qp_globals.control_rec_type;
1781
1782 BEGIN
1783 for l_adj_prod_disc IN c_adj_prod_disc(p_offer_adjustment_id) LOOP
1784 i := k;
1785 i := i + 1;
1786 ozf_utility_pvt.debug_message('i:'||i);
1787 l_pricing_attr_tbl(i).product_attribute_context := l_adj_prod_disc.product_context;
1788 l_pricing_attr_tbl(i).product_attribute := l_adj_prod_disc.product_attribute;
1789 l_pricing_attr_tbl(i).product_attr_value := l_adj_prod_disc.product_attr_value;
1790 l_pricing_attr_tbl(i).product_uom_code := l_adj_prod_disc.uom_code;
1791
1792 l_pricing_attr_tbl(i).pricing_attribute_context := 'VOLUME';
1793 l_pricing_attr_tbl(i).pricing_attribute := l_adj_prod_disc.volume_type;
1794 l_pricing_attr_tbl(i).comparison_operator_code := 'BETWEEN';
1795
1796 l_pricing_attr_tbl(i).modifiers_index := i;
1797 l_pricing_attr_tbl(i).operation := 'CREATE';
1798
1799 l_modifiers_tbl(i).operation := 'CREATE';
1800 l_modifiers_tbl(i).list_header_id := l_adj_prod_disc.list_header_id;
1801
1802 IF l_adj_prod_disc.volume_offer_type = 'ACCRUAL' THEN
1803 l_modifiers_tbl(i).accrual_flag := 'Y';
1804 ELSE
1805 l_modifiers_tbl(i).accrual_flag := 'N';
1806 END IF;
1807
1808 l_modifiers_tbl(i).proration_type_code := 'N';
1809 l_modifiers_tbl(i).product_precedence := 10;
1810
1811 IF l_adj_prod_disc.modifier_level_code <> 'ORDER' THEN
1812 l_modifiers_tbl(i).pricing_group_sequence := 1;
1813 IF l_adj_prod_disc.modifier_level_code = 'LINEGROUP' THEN
1814 l_modifiers_tbl(i).pricing_phase_id := 3;
1815 ELSIF l_adj_prod_disc.modifier_level_code = 'LINE' THEN
1816 l_modifiers_tbl(i).pricing_phase_id := 2;
1817 ELSE
1818 l_modifiers_tbl(i).pricing_phase_id := 3;
1819 END IF;
1820 ELSE
1821 l_modifiers_tbl(i).pricing_phase_id := 4;
1822 END IF;
1823
1824 l_modifiers_tbl(i).print_on_invoice_flag := 'Y';
1825 l_modifiers_tbl(i).modifier_level_code := l_adj_prod_disc.modifier_level_code;
1826 l_modifiers_tbl(i).automatic_flag := 'Y';
1827 l_modifiers_tbl(i).price_break_type_code := 'RANGE';--l_products.volume_break_type;
1828 l_modifiers_tbl(i).start_date_active := l_adj_prod_disc.effective_date;
1829
1830 IF l_adj_prod_disc.apply_discount_flag = 'N' AND l_adj_prod_disc.include_volume_flag = 'Y' THEN
1831 ozf_utility_pvt.debug_message('Apply discount = n3-range');
1832 l_pricing_attr_tbl(i).pricing_attr_value_from := 1;
1833 l_pricing_attr_tbl(i).pricing_attr_value_to := 999999999;
1834 l_modifiers_tbl(i).list_line_type_code := 'DIS';
1835 l_modifiers_tbl(i).price_break_type_code := 'POINT'; -- RANGE GIVES ERROR
1836 l_modifiers_tbl(i).arithmetic_operator := 'AMT';
1837 l_modifiers_tbl(i).operand := 0;
1838 k := k+1;
1839 ELSE
1840 l_modifiers_tbl(i).list_line_type_code := 'PBH';
1841
1842 -- l_modifiers_tbl(i).start_date_active := l_adj_prod_disc.effective_date;
1843 ozf_utility_pvt.debug_message('l_adj_prod_disc.offer_discount_line_id:'||l_adj_prod_disc.offer_discount_line_id);
1844 k := i;
1845 FOR l_adj_prod_dis IN c_adj_prod_dis(l_adj_prod_disc.offer_discount_line_id) LOOP
1846 k := k + 1;
1847 ozf_utility_pvt.debug_message('k:'||k);
1848 l_modifiers_tbl(k).operation := 'CREATE';
1849 l_modifiers_tbl(k).list_header_id := l_adj_prod_disc.list_header_id;
1850 IF l_adj_prod_disc.volume_offer_type = 'ACCRUAL' THEN
1851 l_modifiers_tbl(k).accrual_flag := 'Y';
1852 END IF;
1853 l_modifiers_tbl(k).list_line_type_code := 'DIS';
1854 l_modifiers_tbl(k).proration_type_code := 'N';
1855 l_modifiers_tbl(k).product_precedence := 10;
1856 IF l_adj_prod_disc.modifier_level_code <> 'ORDER' THEN
1857 l_modifiers_tbl(k).pricing_group_sequence := 1;
1858 END IF;
1859 l_modifiers_tbl(k).print_on_invoice_flag := 'Y';
1860 IF l_adj_prod_disc.modifier_level_code <> 'ORDER' THEN
1861 l_modifiers_tbl(k).pricing_group_sequence := 1;
1862 IF l_adj_prod_disc.modifier_level_code = 'LINEGROUP' THEN
1863 l_modifiers_tbl(k).pricing_phase_id := 3;
1864 ELSIF l_adj_prod_disc.modifier_level_code = 'LINE' THEN
1865 l_modifiers_tbl(k).pricing_phase_id := 2;
1866 ELSE
1867 l_modifiers_tbl(k).pricing_phase_id := 3;
1868 END IF;
1869 ELSE
1870 l_modifiers_tbl(k).pricing_phase_id := 4;
1871 END IF;
1872
1873
1874 l_modifiers_tbl(k).modifier_level_code := l_adj_prod_disc.modifier_level_code;
1875 l_modifiers_tbl(k).automatic_flag := 'Y';
1876 l_modifiers_tbl(k).price_break_type_code := 'POINT';
1877 ozf_utility_pvt.debug_message('l_adj_prod_dis.discount_type:'||l_adj_prod_disc.discount_type);
1878 l_modifiers_tbl(k).arithmetic_operator := l_adj_prod_disc.discount_type;
1879 l_modifiers_tbl(k).operand := l_adj_prod_dis.discount;
1880 -- l_modifiers_tbl(k).generate_using_formula_id := l_disc_struct_dis.formula_id;
1881 -- l_modifiers_tbl(k).modifiers_index := k;
1882 l_modifiers_tbl(k).rltd_modifier_grp_type := 'PRICE BREAK';
1883 l_modifiers_tbl(k).rltd_modifier_grp_no := 1;
1884 l_modifiers_tbl(k).modifier_parent_index := i;
1885 ozf_utility_pvt.debug_message('Parent index is :'|| i || ' for : '||k);
1886
1887 -- process products for discounts
1888 l_pricing_attr_tbl(k) := l_pricing_attr_tbl(i);
1889 l_pricing_attr_tbl(k).pricing_attr_value_from := l_adj_prod_dis.volume_from;
1890 l_pricing_attr_tbl(k).pricing_attr_value_to := l_adj_prod_dis.volume_to;
1891 l_pricing_attr_tbl(k).modifiers_index := k;
1892 END LOOP;
1893 END IF;
1894 END LOOP;
1895
1896 ozf_utility_pvt.debug_message('l_pricing_attr_tbl'||l_pricing_attr_tbl.COUNT);
1897 QP_Modifiers_PUB.process_modifiers(
1898 p_api_version_number => 1.0,
1899 p_init_msg_list => FND_API.G_FALSE,
1900 p_return_values => FND_API.G_FALSE,
1901 x_return_status => x_return_status,
1902 x_msg_count => x_msg_count,
1903 x_msg_data => x_msg_data,
1904 p_modifiers_tbl => l_modifiers_tbl,
1905 p_pricing_attr_tbl => l_pricing_attr_tbl,
1906 x_modifier_list_rec => v_modifier_list_rec,
1907 x_modifier_list_val_rec => v_modifier_list_val_rec,
1908 x_modifiers_tbl => v_modifiers_tbl,
1909 x_modifiers_val_tbl => v_modifiers_val_tbl,
1910 x_qualifiers_tbl => v_qualifiers_tbl,
1911 x_qualifiers_val_tbl => v_qualifiers_val_tbl,
1912 x_pricing_attr_tbl => v_pricing_attr_tbl,
1913 x_pricing_attr_val_tbl => v_pricing_attr_val_tbl
1914 );
1915 ozf_utility_pvt.debug_message('Return status 2 is '||x_return_status);
1916 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1917 RAISE FND_API.G_EXC_ERROR;
1918 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1919 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1920 END IF;
1921 ozf_utility_pvt.debug_message('Return status 3 is '||x_return_status);
1922 END;
1923
1924 -- loop thru new products and create new discount and product relations
1925
1926 DECLARE
1927 /*CURSOR c_disc_reln(p_offer_adjustment_id NUMBER) IS
1928 SELECT distinct b.off_discount_product_id , d.pricing_attribute_id
1929 FROM ozf_offer_adjustment_products a, ozf_offer_discount_products b , ozf_offer_adjustments_b c, qp_pricing_attributes d
1930 WHERE a.offer_discount_line_id = b.offer_discount_line_id
1931 AND a.product_attribute = b.product_attribute
1932 AND a.product_attr_value = b.product_attr_value
1933 AND a.excluder_flag = b.excluder_flag
1934 AND a.offer_adjustment_id = c.offer_adjustment_id
1935 AND c.list_header_id = d.list_header_id
1936 AND a.product_attribute = d.product_attribute
1937 AND a.product_attr_value = d.product_attr_value
1938 AND a.offer_adjustment_id = p_offer_adjustment_id;
1939 CURSOR c_prod_reln(p_offer_adjustment_id NUMBER) IS
1940 SELECT b.offer_discount_line_id , d.list_line_id
1941 FROM ozf_offer_adjustment_products a, ozf_offer_discount_lines b, ozf_offer_adjustments_b c, qp_list_lines d
1942 WHERE a.offer_discount_line_id = DECODE(b.tier_type,'PBH',offer_discount_line_id,parent_discount_line_id)
1943 AND b.offer_adjustment_id = a.offer_adjustment_id
1944 AND a.offer_adjustment_id = p_offer_adjustment_id
1945 AND c.list_header_id = d.list_header_id
1946 AND b.tier_type = d.list_line_type_code
1947 AND nvl(b.discount,-1) = nvl(d.operand,-1);
1948 --AND nvl(b.discount_type,'-1') = nvl(d.arithmetic_operator)
1949 */
1950 CURSOR c_create_reln(p_offer_adjustment_id NUMBER) IS
1951 SELECT c.offer_discount_line_id, b.off_discount_product_id , d.list_line_id , d.pricing_attribute_id
1952 FROM ozf_offer_adjustment_products a, ozf_offer_discount_products b, ozf_offer_discount_lines c , ozf_offer_adjustments_b e ,qp_pricing_attributes d
1953 WHERE a.offer_discount_line_id = b.offer_discount_line_id
1954 AND a.product_attr_value = b.product_attr_value
1955 AND a.offer_adjustment_id = p_offer_adjustment_id
1956 AND b.offer_discount_line_id = decode(c.tier_type , 'DIS',c.parent_discount_line_id, c.offer_discount_line_id)
1957 AND e.offer_adjustment_id = a.offer_adjustment_id
1958 AND d.list_header_id = e.list_header_id
1959 AND d.product_attr_value = a.product_attr_value
1960 AND to_number(nvl(d.pricing_attr_value_from,0)) = nvl(c.volume_from,0)
1961 AND to_number(nvl(d.pricing_attr_value_to,0)) = nvl(c.volume_to,0)
1962 AND a.apply_discount_flag = 'Y';
1963
1964 BEGIN
1965 ozf_utility_pvt.debug_message('apply Discounts = Y');
1966 FOR l_create_reln IN c_create_reln(p_offer_adjustment_id ) LOOP
1967 l_reln_rec.pricing_attribute_id := l_create_reln.pricing_attribute_id;
1968 l_reln_rec.qp_list_line_id := l_create_reln.list_line_id;
1969 l_reln_rec.offer_discount_line_id := l_create_reln.offer_discount_line_id;
1970 l_reln_rec.off_discount_product_id := l_create_reln.off_discount_product_id;
1971 ozf_utility_pvt.debug_message('Prc attr : '||l_reln_rec.pricing_attribute_id);
1972 ozf_utility_pvt.debug_message('ListLIneId :'||l_reln_rec.qp_list_line_id);
1973 ozf_utility_pvt.debug_message('Discount line id :'||l_reln_rec.offer_discount_line_id);
1974 ozf_utility_pvt.debug_message('Prod id :'||l_reln_rec.off_discount_product_id);
1975 -- mgudivak Bug 5400931
1976 -- Commenting the call since the following procedure has been
1977 -- obsoleted in ozfvofrs.pls 120.12
1978 /*
1979 OZF_OFFER_PVT.relate_qp_ozf_lines
1980 (
1981 p_api_version_number => p_api_version,
1982 p_init_msg_list => p_init_msg_list,
1983 p_commit => p_commit,
1984 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1985
1986 x_return_status => x_return_status,
1987 x_msg_count => x_msg_count,
1988 x_msg_data => x_msg_data,
1989 p_ozf_qp_reln_rec => l_reln_rec
1990 );
1991 */
1992 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1993 RAISE FND_API.G_EXC_ERROR;
1994 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1995 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1996 END IF;
1997
1998 END LOOP;
1999
2000 END;
2001
2002 DECLARE
2003 CURSOR c_dis_products(p_offer_adjustment_id NUMBER) IS
2004 SELECT c.off_discount_product_id , d.pricing_attribute_id
2005 FROM
2006 ozf_offer_adjustment_products a, ozf_offer_adjustments_b b , ozf_offer_discount_products c, qp_pricing_attributes d
2007 WHERE a.offer_adjustment_id = b.offer_adjustment_id
2008 AND a.offer_adjustment_id = p_offer_adjustment_id
2009 AND a.offer_discount_line_id = c.offer_discount_line_id
2010 AND a.product_attribute = c.product_attribute
2011 AND a.product_attr_value = c.product_attr_value
2012 AND b.list_header_id = d.list_header_id
2013 AND c.product_attribute = d.product_attribute
2014 AND c.product_attr_value = d.product_attr_value
2015 AND a.apply_discount_flag = 'N';
2016
2017 l_prod_rec OZF_QP_PRODUCTS_PVT.qp_product_rec_type;
2018
2019 BEGIN
2020 ozf_utility_pvt.debug_message('apply Discounts = n');
2021 FOR l_dis_products in c_dis_products(p_offer_adjustment_id) LOOP
2022 l_reln_rec := null;
2023 l_reln_rec.pricing_attribute_id := l_dis_products.pricing_attribute_id;
2024 l_reln_rec.off_discount_product_id := l_dis_products.off_discount_product_id;
2025 ozf_utility_pvt.debug_message('Prc attr : '||l_reln_rec.pricing_attribute_id);
2026 ozf_utility_pvt.debug_message('ListLIneId :'||l_reln_rec.qp_list_line_id);
2027 ozf_utility_pvt.debug_message('Discount line id :'||l_reln_rec.offer_discount_line_id);
2028 ozf_utility_pvt.debug_message('Prod id :'||l_reln_rec.off_discount_product_id);
2029 -- mgudivak - Bug 5400931
2030 -- Commenting the call since the following procedure has been
2031 -- obsoleted in ozfvofrs.pls 120.12
2032 /*
2033 OZF_OFFER_PVT.relate_qp_ozf_lines
2034 (
2035 p_api_version_number => p_api_version,
2036 p_init_msg_list => p_init_msg_list,
2037 p_commit => p_commit,
2038 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2039
2040 x_return_status => x_return_status,
2041 x_msg_count => x_msg_count,
2042 x_msg_data => x_msg_data,
2043 p_ozf_qp_reln_rec => l_reln_rec
2044 );
2045 */
2046 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2047 RAISE FND_API.G_EXC_ERROR;
2048 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2050 END IF;
2051 END LOOP;
2052 end;
2053
2054 --exception
2055 null;
2056 ozf_utility_pvt.debug_message('Private API:'|| l_api_name || ' Start');
2057
2058 EXCEPTION
2059 WHEN FND_API.G_EXC_ERROR THEN
2060 rollback to process_vo_adj_products;
2061 x_return_status := FND_API.G_RET_STS_ERROR;
2062 FND_MSG_PUB.count_and_get(
2063 p_encoded => FND_API.g_false
2064 , p_count => x_msg_count
2065 , p_data => x_msg_data
2066 );
2067 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2068 ROLLBACK TO process_vo_adj_products;
2069 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2070 FND_MSG_PUB.count_and_get(
2071 p_encoded => FND_API.g_false
2072 , p_count => x_msg_count
2073 , p_data => x_msg_data
2074 );
2075 WHEN OTHERS THEN
2076 ROLLBACK TO process_vo_adj_products;
2077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2078 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2079 THEN
2080 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2081 END IF;
2082 FND_MSG_PUB.count_and_get(
2083 p_encoded => FND_API.g_false
2084 , p_count => x_msg_count
2085 , p_data => x_msg_data
2086 );
2087 END process_vo_adj_products;
2088
2089
2090 /**
2091 THis procedure has been trimmed down in its approach due to the following issues.
2092 Ideally the best way to adjust tiers would be to end date qp_list_lines with the effective date of the offer adjustment
2093 and create new qp_list_lines with new discounts and effective date for new tiers.
2094 Since qp overlapping logic does not account for the end_Date of a list line, this approach does not work.
2095 Second approach is to end date the whole pbh qp_list_line and recreate new pbh qp_list_line.
2096 But this is a lot of work and at this stage this will be hard to pull up since it is a sensitive code and mistake here will
2097 mess up the whole volume offer.
2098 The approach taken here is just update the existing lines.
2099
2100 */
2101 PROCEDURE process_vo_adj_tiers
2102 (
2103 p_init_msg_list IN VARCHAR2 := FND_API.g_false
2104 ,p_api_version IN NUMBER
2105 ,p_commit IN VARCHAR2 := FND_API.g_false
2106 ,x_return_status OUT NOCOPY VARCHAR2
2107 ,x_msg_count OUT NOCOPY NUMBER
2108 ,x_msg_data OUT NOCOPY VARCHAR2
2109 ,p_offer_adjustment_id IN NUMBER
2110
2111 )
2112 IS
2113 i NUMBER := 1;
2114 j NUMBER;
2115 l_qp_list_header_id NUMBER;
2116 l_error_location NUMBER;
2117 l_modifier_line_tbl OZF_OFFER_PVT.MODIFIER_LINE_TBL_TYPE;
2118 l_modifier_line_create_tbl OZF_OFFER_PVT.MODIFIER_LINE_TBL_TYPE;
2119 l_modifier_list_rec OZF_OFFER_PVT.MODIFIER_LIST_REC_TYPE;
2120 l_offer_type VARCHAR2(30) := 'VOLUME_OFFER';
2121
2122
2123 l_modifiers_tbl qp_modifiers_pub.modifiers_tbl_type;
2124 l_pricing_attr_tbl qp_modifiers_pub.pricing_attr_tbl_type;
2125
2126
2127 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2128 L_API_NAME CONSTANT VARCHAR2(30) := 'process_vo_adj_tiers';
2129
2130 BEGIN
2131 SAVEPOINT process_vo_adj_tiers;
2132 /*
2133 IF NOT FND_API.COMPATIBLE_API_CALL(
2134 l_api_version_number
2135 ,p_api_version
2136 ,l_api_name
2137 ,G_PKG_NAME
2138 )
2139 THEN
2140 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2141 END IF;
2142
2143 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
2144 FND_MSG_PUB.INITIALIZE;
2145 END IF;*/
2146 x_return_status := FND_API.G_RET_STS_SUCCESS;
2147 ozf_utility_pvt.debug_message('Private API:'|| l_api_name || ' Start');
2148 -- inactivate existing qp_list_lines
2149 DECLARE
2150 CURSOR c_qp_list_lines(p_adjustment_id NUMBER)
2151 IS
2152 SELECT c.list_line_id , c.list_header_id , d.effective_date , a.modified_discount, c.arithmetic_operator
2153 FROM ozf_offer_adjustment_tiers a, ozf_qp_discounts b , qp_list_Lines c, ozf_offer_adjustments_b d
2154 WHERE a.offer_discount_line_id = b.offer_discount_line_id
2155 AND b.list_line_id = c.list_line_id
2156 AND a.offer_adjustment_id = d.offer_adjustment_id
2157 AND a.offer_adjustment_id = p_adjustment_id;
2158
2159 BEGIN
2160 for l_qp_list_lines in c_qp_list_lines(p_offer_adjustment_id) LOOP
2161 i := i+1;
2162 l_modifier_list_rec.offer_type := 'VOLUME_OFFER';
2163 l_modifier_list_rec.qp_list_header_id := l_qp_list_lines.list_header_id;
2164
2165 l_modifier_line_tbl(i).list_line_id := l_qp_list_lines.list_line_id;
2166 l_modifier_line_tbl(i).list_header_id := l_qp_list_lines.list_header_id;
2167 --l_modifier_line_tbl(i).end_date_active := l_qp_list_lines.effective_date;
2168 l_modifier_line_tbl(i).operand := l_qp_list_lines.modified_discount;
2169 l_modifier_line_tbl(i).arithmetic_operator := l_qp_list_lines.arithmetic_operator;
2170
2171 l_modifier_line_tbl(i).operation := 'UPDATE';
2172 ozf_utility_pvt.debug_message('List Line Id is :'||l_qp_list_lines.list_line_id || ' : '||l_qp_list_lines.list_header_id || ' : '||l_qp_list_lines.modified_discount);
2173 ozf_utility_pvt.debug_message('List Line Id is1 :'||l_modifier_line_tbl(i).list_line_id || ' : '||l_modifier_line_tbl(i).list_header_id || ' : '||l_modifier_line_tbl(i).operand);
2174
2175 END LOOP;
2176 OZF_OFFER_PVT.process_modifiers
2177 (
2178 p_init_msg_list => FND_API.G_FALSE
2179 , p_api_version => 1.0
2180 , p_commit => FND_API.G_FALSE
2181 , x_return_status => x_return_status
2182 , x_msg_count => x_msg_count
2183 , x_msg_data => x_msg_data
2184 , p_offer_type => l_offer_type
2185 , p_modifier_list_rec => l_modifier_list_rec
2186 , p_modifier_line_tbl => l_modifier_line_tbl
2187 , x_qp_list_header_id => l_qp_list_header_id
2188 , x_error_location => l_error_location
2189 );
2190 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2191 RAISE FND_API.G_EXC_ERROR;
2192 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2193 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2194 END IF;
2195 END;
2196 -- update existing tiers
2197 DECLARE
2198 CURSOR c_discounts(p_offer_adjustment_id NUMBER) IS
2199 SELECT
2200 a.modified_discount, a.offer_discount_line_id , b.object_version_number , b.offer_id
2201 FROM ozf_offer_adjustment_tiers a, ozf_offer_discount_lines b
2202 WHERE a.offer_adjustment_id = p_offer_adjustment_id
2203 AND a.offer_discount_line_id = b.offer_discount_line_id;
2204 l_vo_disc_rec OZF_Volume_Offer_disc_PVT.vo_disc_rec_type;
2205 BEGIN
2206 FOR l_discounts in c_discounts(p_offer_adjustment_id) LOOP
2207 l_vo_disc_rec.offer_discount_line_id := l_discounts.offer_discount_line_id;
2208 l_vo_disc_rec.discount := l_discounts.modified_discount;
2209 l_vo_disc_rec.object_version_number := l_discounts.object_version_number;
2210 l_vo_disc_rec.offer_id := l_discounts.offer_id;
2211 OZF_Volume_Offer_disc_PVT.Update_vo_discount(
2212 p_api_version_number => 1.0
2213 ,p_init_msg_list => FND_API.G_FALSE
2214 ,p_commit => FND_API.G_FALSE
2215 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
2216
2217 , x_return_status => x_return_status
2218 , x_msg_count => x_msg_count
2219 , x_msg_data => x_msg_data
2220
2221 , p_vo_disc_rec => l_vo_disc_rec
2222 );
2223 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2224 RAISE FND_API.G_EXC_ERROR;
2225 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2227 END IF;
2228 END LOOP;
2229 END;
2230 /*
2231 -- create new discount tiers
2232 DECLARE
2233 CURSOR c_create_qp_list_lines(p_offer_adjustment_id NUMBER)
2234 IS
2235 SELECT d.from_rltd_modifier_id list_line_id
2236 , c.list_header_id list_header_id
2237 , f.price_break_type_code price_break_type_code
2238 , c.pricing_attr_value_from pricing_attr_value_from
2239 , c.pricing_attr_value_to pricing_attr_value_to
2240 , a.modified_discount operand
2241 , f.arithmetic_operator arithmetic_operator
2242 , c.pricing_attribute pricing_attribute
2243 , e.effective_date start_date_active
2244 FROM ozf_offer_adjustment_tiers a, ozf_qp_discounts b, qp_pricing_attributes c, qp_rltd_modifiers d
2245 , ozf_offer_adjustments_b e, qp_list_lines f
2246 WHERE a.offer_discount_line_id = b.offer_discount_line_id
2247 AND b.list_line_id = c.list_line_id
2248 AND c.list_line_id = d.to_rltd_modifier_id
2249 AND a.offer_adjustment_id = e.offer_adjustment_id
2250 AND b.list_line_id = f.list_line_id
2251 AND a.offer_adjustment_id = p_offer_adjustment_id;
2252 BEGIN
2253 i := 1;
2254 FOR l_create_qp_list_lines IN c_create_qp_list_lines(p_offer_adjustment_id) LOOP
2255 l_modifier_line_create_tbl(i).list_header_id := l_create_qp_list_lines.list_header_id;
2256 l_modifier_line_create_tbl(i).list_line_id := l_create_qp_list_lines.list_line_id;
2257 l_modifier_line_create_tbl(i).price_break_type_code := l_create_qp_list_lines.price_break_type_code;
2258 l_modifier_line_create_tbl(i).pricing_attr_value_from := l_create_qp_list_lines.pricing_attr_value_from;
2259 l_modifier_line_create_tbl(i).pricing_attr_value_to := l_create_qp_list_lines.pricing_attr_value_to;
2260 l_modifier_line_create_tbl(i).operand := l_create_qp_list_lines.operand;
2261 l_modifier_line_create_tbl(i).arithmetic_operator := l_create_qp_list_lines.arithmetic_operator;
2262 l_modifier_line_create_tbl(i).pricing_attribute_id := FND_API.G_MISS_NUM;
2263 l_modifier_line_create_tbl(i).pricing_attr := l_create_qp_list_lines.pricing_attribute;
2264 l_modifier_line_create_tbl(i).start_date_active := l_create_qp_list_lines.start_date_active;
2265 l_modifier_line_create_tbl(i).operation := 'CREATE';
2266 i := i + 1;
2267 END LOOP;
2268 dbms_output.put_line('Size is '||l_modifier_line_create_tbl.count);
2269 OZF_OFFER_PVT.create_offer_tiers
2270 (
2271 p_init_msg_list => p_init_msg_list
2272 ,p_api_version => p_api_version
2273 ,p_commit => p_commit
2274 ,x_return_status => x_return_status
2275 ,x_msg_count => x_msg_count
2276 ,x_msg_data => x_msg_data
2277 ,p_modifier_line_tbl => l_modifier_line_create_tbl
2278 ,x_error_location => l_error_location
2279 ,x_modifiers_tbl => l_modifiers_tbl
2280 ,x_pricing_attr_tbl => l_pricing_attr_tbl
2281 );
2282 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2283 RAISE FND_API.G_EXC_ERROR;
2284 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2286 END IF;
2287 END;
2288 */
2289 /*
2290 -- create new relationships
2291 DECLARE
2292 CURSOR c_create_reln(p_offer_adjustment_id NUMBER)
2293 IS
2294 SELECT a.offer_discount_line_id , f.off_discount_product_id , e.pricing_attribute_id , d.list_line_id
2295 FROM ozf_offer_adjustment_tiers a, ozf_offer_adjustments_b b , ozf_offer_discount_lines c , qp_list_lines d , qp_pricing_attributes e , ozf_offer_discount_products f
2296 WHERE a.offer_adjustment_id = b.offer_adjustment_id
2297 AND c.offer_discount_line_id = a.offer_discount_line_id
2298 AND b.list_header_id = d.list_header_id
2299 AND d.list_line_id = e.list_line_id
2300 AND to_number(e.pricing_attr_value_from) = c.volume_from -- takes care of no apply discount products
2301 AND to_number(e.pricing_attr_value_to) = c.volume_to
2302 AND d.operand = a.modified_discount --c.discount
2303 AND f.offer_discount_line_id = c.parent_discount_line_id
2304 AND f.product_attribute = e.product_attribute
2305 and f.product_attr_value = e.product_attr_value
2306 AND d.start_date_active >= b.effective_date
2307 AND a.offer_adjustment_id = p_offer_adjustment_id;
2308 l_reln_rec OZF_OFFER_PVT.ozf_qp_reln_rec_type;
2309 BEGIN
2310 FOR l_create_reln in c_create_reln(p_offer_adjustment_id ) LOOP
2311 l_reln_rec.pricing_attribute_id := l_create_reln.pricing_attribute_id;
2312 l_reln_rec.qp_list_line_id := l_create_reln.list_line_id;
2313 l_reln_rec.offer_discount_line_id := l_create_reln.offer_discount_line_id;
2314 l_reln_rec.off_discount_product_id := l_create_reln.off_discount_product_id;
2315 dbms_output.put_line('Prc attr : '||l_reln_rec.pricing_attribute_id);
2316 dbms_output.put_line('ListLIneId :'||l_reln_rec.qp_list_line_id);
2317 dbms_output.put_line('Discount line id :'||l_reln_rec.offer_discount_line_id);
2318 dbms_output.put_line('Prod id :'||l_reln_rec.off_discount_product_id);
2319 OZF_OFFER_PVT.relate_qp_ozf_lines
2320 (
2321 p_api_version_number => p_api_version,
2322 p_init_msg_list => p_init_msg_list,
2323 p_commit => p_commit,
2324 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2325
2326 x_return_status => x_return_status,
2327 x_msg_count => x_msg_count,
2328 x_msg_data => x_msg_data,
2329 p_ozf_qp_reln_rec => l_reln_rec
2330 );
2331 END LOOP;
2332 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2333 RAISE FND_API.G_EXC_ERROR;
2334 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2335 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2336 END IF;
2337 end;
2338 */
2339 ozf_utility_pvt.debug_message('Private API:'|| l_api_name || ' End');
2340
2341 IF FND_API.to_boolean(p_commit) THEN
2342 COMMIT WORK;
2343 END IF;
2344
2345 EXCEPTION
2346 WHEN FND_API.G_EXC_ERROR THEN
2347 ROLLBACK TO process_vo_adj_tiers;
2348 x_return_status := FND_API.G_RET_STS_ERROR;
2349 FND_MSG_PUB.count_and_get(
2350 p_encoded => FND_API.g_false
2351 , p_count => x_msg_count
2352 , p_data => x_msg_data
2353 );
2354 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2355 ROLLBACK TO process_vo_adj_tiers;
2356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2357 FND_MSG_PUB.count_and_get(
2358 p_encoded => FND_API.g_false
2359 , p_count => x_msg_count
2360 , p_data => x_msg_data
2361 );
2362 WHEN OTHERS THEN
2363 rollback to process_vo_adj_tiers;
2364 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2365 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2366 THEN
2367 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2368 END IF;
2369 FND_MSG_PUB.count_and_get(
2370 p_encoded => FND_API.g_false
2371 , p_count => x_msg_count
2372 , p_data => x_msg_data
2373 );
2374 END process_vo_adj_tiers;
2375
2376
2377
2378 PROCEDURE process_vo_adjustments
2379 (
2380 p_init_msg_list IN VARCHAR2 := FND_API.g_false
2381 ,p_api_version IN NUMBER
2382 ,p_commit IN VARCHAR2 := FND_API.g_false
2383 ,x_return_status OUT NOCOPY VARCHAR2
2384 ,x_msg_count OUT NOCOPY NUMBER
2385 ,x_msg_data OUT NOCOPY VARCHAR2
2386 ,p_offer_adjustment_id IN NUMBER
2387
2388 )
2389 IS
2390
2391 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
2392 L_API_NAME CONSTANT VARCHAR2(30) := 'process_vo_adjustments';
2393
2394 BEGIN
2395 SAVEPOINT process_vo_adjustments;
2396
2397 IF NOT FND_API.COMPATIBLE_API_CALL(
2398 l_api_version_number
2399 ,p_api_version
2400 ,l_api_name
2401 ,G_PKG_NAME
2402 )
2403 THEN
2404 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2405 END IF;
2406
2407 IF FND_API.TO_BOOLEAN(p_init_msg_list) THEN
2408 FND_MSG_PUB.INITIALIZE;
2409 END IF;
2410
2411 x_return_status := FND_API.G_RET_STS_SUCCESS;
2412 ozf_utility_pvt.debug_message('Private API:'|| l_api_name || ' Start');
2413 process_vo_adj_tiers
2414 (
2415 p_init_msg_list => FND_API.g_false
2416 ,p_api_version => 1.0
2417 ,p_commit => FND_API.g_false
2418 ,x_return_status => x_return_status
2419 ,x_msg_count => x_msg_count
2420 ,x_msg_data => x_msg_data
2421 ,p_offer_adjustment_id => p_offer_adjustment_id
2422 );
2423 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2424 RAISE FND_API.G_EXC_ERROR;
2425 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2426 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2427 END IF;
2428 process_vo_adj_products
2429 (
2430 p_init_msg_list => FND_API.g_false
2431 ,p_api_version => 1.0
2432 ,p_commit => FND_API.g_false
2433 ,x_return_status => x_return_status
2434 ,x_msg_count => x_msg_count
2435 ,x_msg_data => x_msg_data
2436 ,p_offer_adjustment_id => p_offer_adjustment_id
2437 );
2438 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2439 RAISE FND_API.G_EXC_ERROR;
2440 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2441 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2442 END IF;
2443
2444 ozf_utility_pvt.debug_message('Private API:'|| l_api_name || ' End');
2445 EXCEPTION
2446 WHEN FND_API.G_EXC_ERROR THEN
2447 ROLLBACK TO process_vo_adjustments;
2448 x_return_status := FND_API.G_RET_STS_ERROR;
2449 FND_MSG_PUB.count_and_get(
2450 p_encoded => FND_API.g_false
2451 , p_count => x_msg_count
2452 , p_data => x_msg_data
2453 );
2454 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2455 ROLLBACK TO process_vo_adjustments;
2456 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2457 FND_MSG_PUB.count_and_get(
2458 p_encoded => FND_API.g_false
2459 , p_count => x_msg_count
2460 , p_data => x_msg_data
2461 );
2462 WHEN OTHERS THEN
2463 ROLLBACK TO process_vo_adjustments;
2464 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2465 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2466 THEN
2467 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2468 END IF;
2469 FND_MSG_PUB.count_and_get(
2470 p_encoded => FND_API.g_false
2471 , p_count => x_msg_count
2472 , p_data => x_msg_data
2473 );
2474
2475 END process_vo_adjustments;
2476
2477
2478 PROCEDURE getCloseAdjustmentParams
2479 (
2480 p_offer_adjustment_id IN NUMBER
2481 ,x_return_status OUT NOCOPY VARCHAR2
2482 ,x_msg_count OUT NOCOPY NUMBER
2483 ,x_msg_data OUT NOCOPY VARCHAR2
2484 ,x_newStatus OUT NOCOPY VARCHAR2
2485 ,x_budgetAdjFlag OUT NOCOPY VARCHAR2
2486 )
2487 IS
2488
2489 l_newStatus OZF_OFFER_ADJUSTMENTS_B.STATUS_CODE%TYPE;
2490 CURSOR c_closeAdjustment(cp_offerAdjustmentId NUMBER) IS
2491 SELECT decode(greatest(a.effective_date,sysdate) , a.effective_date, 'Y','N') close_adjustment
2492 FROM ozf_offer_adjustments_b a
2493 WHERE offer_adjustment_id = cp_offerAdjustmentId;
2494 l_closeAdjustment VARCHAR2(1);
2495 l_budgetAdjFlag VARCHAR2(1);
2496
2497 BEGIN
2498 x_return_status := FND_API.G_RET_STS_SUCCESS;
2499 l_closeAdjustment := 'N';
2500 OPEN c_closeAdjustment(p_offer_adjustment_id);
2501 FETCH c_closeAdjustment INTO l_closeAdjustment;
2502 IF c_closeAdjustment%NOTFOUND THEN
2503 l_closeAdjustment := 'N';
2504 END IF;
2505 CLOSE c_closeAdjustment;
2506
2507 IF l_closeAdjustment = 'Y' THEN
2508 x_newStatus := 'CLOSED';
2509 x_budgetAdjFlag := 'Y';
2510 ELSE
2511 x_newStatus := 'ACTIVE';
2512 x_budgetAdjFlag := null;
2513 END IF;
2514 END getCloseAdjustmentParams;
2515
2516 /**
2517 * Closes a Future dated adjustment and activates a back dated adjustment.
2518 * This procedure calls update statement directly since, this procedure may be called from update_offer_adjustments procedure
2519 * and calling this procedure again to update will lead to an recursive call without exit condition
2520 * p_offer_adjustment_id Primary key of the adjustment to be closed/activated
2521 */
2522 PROCEDURE close_adjustment
2523 (
2524 p_offer_adjustment_id IN NUMBER
2525 ,x_return_status OUT NOCOPY VARCHAR2
2526 ,x_msg_count OUT NOCOPY NUMBER
2527 ,x_msg_data OUT NOCOPY VARCHAR2
2528 )
2529 IS
2530 l_newStatus OZF_OFFER_ADJUSTMENTS_B.STATUS_CODE%TYPE;
2531
2532 CURSOR c_closeAdjustment(cp_offerAdjustmentId NUMBER) IS
2533 SELECT decode(greatest(a.effective_date,sysdate) , a.effective_date, 'Y','N') close_adjustment,
2534 list_header_id offer_id
2535 FROM ozf_offer_adjustments_b a
2536 WHERE offer_adjustment_id = cp_offerAdjustmentId;
2537
2538 l_closeAdjustment VARCHAR2(1);
2539 l_budgetAdjFlag VARCHAR2(1);
2540 l_list_header_id NUMBER;
2541 BEGIN
2542 x_return_status := FND_API.G_RET_STS_SUCCESS;
2543 l_closeAdjustment := 'N';
2544 OPEN c_closeAdjustment(p_offer_adjustment_id);
2545 FETCH c_closeAdjustment INTO l_closeAdjustment,l_list_header_id;
2546 IF c_closeAdjustment%NOTFOUND THEN
2547 l_closeAdjustment := 'N';
2548 END IF;
2549 CLOSE c_closeAdjustment;
2550
2551 IF l_closeAdjustment = 'Y' THEN
2552 l_newStatus := 'CLOSED';
2553 l_budgetAdjFlag := 'Y';
2554 ELSE
2555 l_newStatus := 'ACTIVE';
2556 l_budgetAdjFlag := null;
2557 END IF;
2558
2559 UPDATE ozf_offer_adjustments_b
2560 SET budget_adjusted_flag = l_budgetAdjFlag,
2561 object_version_number = object_version_number + 1,
2562 approved_date = sysdate,
2563 status_code = l_newStatus
2564 WHERE offer_adjustment_id = p_offer_adjustment_id;
2565
2566
2567 EXCEPTION
2568 WHEN OTHERS THEN
2569 x_return_status := FND_API.G_RET_STS_ERROR;
2570 Fnd_Msg_Pub.Count_AND_Get
2571 ( p_count => x_msg_count,
2572 p_data => x_msg_data,
2573 p_encoded => Fnd_Api.G_FALSE
2574 );
2575 END CLOSE_ADJUSTMENT;
2576
2577
2578 PROCEDURE Update_Offer_Discounts
2579 (
2580 p_init_msg_list IN VARCHAR2 := FND_API.g_false
2581 ,p_api_version IN NUMBER
2582 ,p_commit IN VARCHAR2 := FND_API.g_false
2583 ,x_return_status OUT NOCOPY VARCHAR2
2584 ,x_msg_count OUT NOCOPY NUMBER
2585 ,x_msg_data OUT NOCOPY VARCHAR2
2586 ,p_offer_adjustment_id IN NUMBER
2587
2588 )
2589 IS
2590 l_api_version CONSTANT NUMBER := 1.0;
2591 l_api_name CONSTANT VARCHAR2(30) := 'update_offer_discounts';
2592 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
2593 l_list_header_id NUMBER;
2594 BEGIN
2595 -- initialize
2596 -- push data to qp
2597 -- close/activate the adjustment
2598 SAVEPOINT update_offer_discounts ;
2599
2600 IF Fnd_Api.to_boolean(p_init_msg_list)
2601 THEN
2602 Fnd_Msg_Pub.initialize;
2603 END IF;
2604 IF NOT Fnd_Api.compatible_api_call ( l_api_version,
2605 p_api_version,
2606 l_api_name,
2607 g_pkg_name
2608 )
2609 THEN
2610 RAISE Fnd_Api.g_exc_unexpected_error;
2611 END IF;
2612 x_return_status := Fnd_Api.g_ret_sts_success;
2613 -- julou backdated offer for Promotional Goods, Trade Deal, Tiered Discount
2614 -- populate l_modifier_list_rec
2615 -- set operation to NULL should prevent operation on list header and offer tables
2616 OZF_OFFER_ADJ_PVT.process_adjustment
2617 (
2618 p_init_msg_list => FND_API.g_false
2619 ,p_api_version => 1.0
2620 ,p_commit => FND_API.g_false
2621 ,x_return_status => x_return_status
2622 ,x_msg_count => x_msg_count
2623 ,x_msg_data => x_msg_data
2624 ,p_offerAdjustmentId => p_offer_adjustment_id
2625 );
2626 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2627 RAISE FND_API.G_EXC_ERROR;
2628 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2630 END IF;
2631
2632 SELECT list_header_id into l_list_header_id
2633 FROM ozf_offer_adjustments_b a
2634 WHERE offer_adjustment_id = p_offer_adjustment_id;
2635
2636 --insert into ozf_events values('After process_adjustment'||p_offer_adjustment_id,sysdate);
2637 OZF_OFFER_PVT.raise_offer_event(l_list_header_id, p_offer_adjustment_id);
2638
2639 IF p_commit = Fnd_Api.g_true
2640 THEN
2641 COMMIT WORK;
2642 END IF;
2643
2644 EXCEPTION
2645 WHEN Fnd_Api.G_EXC_ERROR THEN
2646 x_return_status := Fnd_Api.g_ret_sts_error ;
2647 ROLLBACK TO update_offer_discounts;
2648 Fnd_Msg_Pub.Count_AND_Get
2649 ( p_count => x_msg_count,
2650 p_data => x_msg_data,
2651 p_encoded => Fnd_Api.G_FALSE
2652 );
2653 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2654 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2655 ROLLBACK TO update_offer_discounts;
2656 Fnd_Msg_Pub.Count_AND_Get
2657 ( p_count => x_msg_count,
2658 p_data => x_msg_data,
2659 p_encoded => Fnd_Api.G_FALSE
2660 );
2661 WHEN OTHERS THEN
2662 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
2663 ROLLBACK TO update_offer_discounts ;
2664 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
2665 THEN
2666 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2667 END IF;
2668 Fnd_Msg_Pub.Count_AND_Get
2669 ( p_count => x_msg_count,
2670 p_data => x_msg_data,
2671 p_encoded => Fnd_Api.G_FALSE
2672 );
2673 END Update_Offer_Discounts ;
2674
2675
2676
2677 END OZF_Offer_Backdate_PVT;