[Home] [Help]
PACKAGE BODY: APPS.OZF_OFFER_ADJUSTMENT_PVT
Source
1 PACKAGE BODY OZF_OFFER_ADJUSTMENT_PVT as
2 /* $Header: ozfvoadb.pls 120.6 2006/08/16 01:29:55 rssharma ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- OZF_Offer_Adjustment_PVT
7 -- Purpose
8 --
9 -- History
10 -- Thu Mar 30 2006:1/23 PM RSSHARMA New Adjustment changes. While activating, user offer_backdate api to get final status and budget updated flag.
11 -- Mon May 22 2006:12/1 PM RSSHARMA Fixed debug to print only on debug high
12 -- Tue Aug 15 2006:3/26 PM RSSHARMA Fixed bug # 5468261. Fixed query for if_lines_exist
13 -- NOTE
14 --
15 -- This Api is generated with Latest version of
16 -- Rosetta, where g_miss indicates NULL and
17 -- NULL indicates missing value. Rosetta Version 1.55
18 -- End of Comments
19 -- ===============================================================
20
21
22 G_PKG_NAME CONSTANT VARCHAR2(30):= 'OZF_Offer_Adjustment_PVT';
23 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvoadb.pls';
24
25 -- G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
26 -- G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
27 --
28 -- Foreward Procedure Declarations
29 --
30
31 PROCEDURE Default_Offer_Adj_Items (
32 p_offer_adj_rec IN offer_adj_rec_type ,
33 x_offer_adj_rec OUT NOCOPY offer_adj_rec_type
34 ) ;
35
36
37
38 -- ==============================================================================
39 -- Start of Comments
40 -- ==============================================================================
41 -- API Name
42 -- get_budget_start_date
43 -- Type
44 -- Private
45 -- Pre-Req
46 --
47 -- Parameters
48 --
49 -- IN
50 -- p_qp_list_header_id IN NUMBER Required
51 --
52 -- RETURNS
53 -- Min Start Date of all the budgets attached to the offer
54 -- History
55 -- Thu May 27 2004:2/29 PM RSSJARMA Created
56 -- NOTE
57 -- Concious decision to return min start date as this makes sure that all the budgets are active by the date of adjustment
58 -- End of Comments
59 -- ==============================================================================
60 FUNCTION get_budget_start_date
61 ( p_qp_list_header_id IN NUMBER)
62 RETURN DATE
63 IS
64 CURSOR c_budget_start_date(p_qp_list_header_id NUMBER) is
65 SELECT MIN(start_date_active) FROM ozf_funds_all_b
66 WHERE fund_id IN ( SELECT budget_source_id FROM ozf_act_budgets where arc_act_budget_used_by = 'OFFR' and act_budget_used_by_id = p_qp_list_header_id);
67 l_budget_start_date DATE;
68 BEGIN
69 OPEN c_budget_start_date(p_qp_list_header_id );
70 FETCH c_budget_start_date INTO l_budget_start_date;
71 CLOSE c_budget_start_date;
72 RETURN l_budget_start_date;
73 END get_budget_start_date;
74
75 FUNCTION isBudgetOffer(p_listHeaderId NUMBER)
76 RETURN VARCHAR2
77 IS
78 CURSOR c_budgetOffer(cp_listHeaderId NUMBER) IS
79 SELECT nvl(budget_offer_yn,'N')
80 FROM ozf_offers
81 WHERE qp_list_header_id = cp_listHeaderId;
82 l_budgetOffer VARCHAR2(1);
83 BEGIN
84 OPEN c_budgetOffer(cp_listHeaderId => p_listHeaderId);
85 FETCH c_budgetOffer INTO l_budgetOffer;
86 IF c_budgetOffer%NOTFOUND THEN
87 l_budgetOffer := 'U';
88 END IF;
89 CLOSE c_budgetOffer;
90 return l_budgetOffer;
91 END isBudgetOffer;
92
93
94 PROCEDURE raise_event(p_id IN NUMBER)
95 IS
96 l_item_key varchar2(30);
97 l_parameter_list wf_parameter_list_t;
98 BEGIN
99 l_item_key := p_id ||'_'|| TO_CHAR(SYSDATE,'DDMMRRRRHH24MISS');
100 l_parameter_list := WF_PARAMETER_LIST_T();
101
102 OZF_Offer_Adj_Line_PVT.debug_message('Id is :'||p_id );
103 wf_event.AddParameterToList(p_name => 'P_ID',
104 p_value => p_id,
105 p_parameterlist => l_parameter_list);
106 OZF_Offer_Adj_Line_PVT.debug_message('Item Key is :'||l_item_key);
107 wf_event.raise( p_event_name => 'oracle.apps.ozf.offer.OfferAdjApproval',
108 p_event_key => l_item_key,
109 p_parameters => l_parameter_list);
110 EXCEPTION
111 WHEN OTHERS THEN
112 RAISE Fnd_Api.g_exc_error;
113 OZF_Offer_Adj_Line_PVT.debug_message('Exception in raising business event');
114 END;
115
116 -- Hint: Primary key needs to be returned.
117 -- ==============================================================================
118 -- Start of Comments
119 -- ==============================================================================
120 -- API Name
121 -- Create_Offer_Adjustment
122 -- Type
123 -- Private
124 -- Pre-Req
125 --
126 -- Parameters
127 --
128 -- IN
129 -- p_api_version_number IN NUMBER Required
130 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
131 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
132 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
133 -- p_offer_adj_rec IN offer_adj_rec_type Required
134 --
135 -- OUT
136 -- x_return_status OUT VARCHAR2
137 -- x_msg_count OUT NUMBER
138 -- x_msg_data OUT VARCHAR2
139 -- Version : Current version 1.0
140 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
141 -- and basic operation, developer must manually add parameters and business logic as necessary.
142 --
143 -- History
144 --
145 -- NOTE
146 --
147 -- End of Comments
148 -- ==============================================================================
149
150 PROCEDURE Create_Offer_Adjustment(
151 p_api_version_number IN NUMBER,
152 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
153 p_commit IN VARCHAR2 := FND_API.G_FALSE,
154 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
155
156 x_return_status OUT NOCOPY VARCHAR2,
157 x_msg_count OUT NOCOPY NUMBER,
158 x_msg_data OUT NOCOPY VARCHAR2,
159
160 p_offer_adj_rec IN offer_adj_rec_type := g_miss_offer_adj_rec,
161 x_offer_adjustment_id OUT NOCOPY NUMBER
162 )
163
164 IS
165 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Offer_Adjustment';
166 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
167 l_return_status_full VARCHAR2(1);
168 l_object_version_number NUMBER := 1;
169 l_org_id NUMBER := FND_API.G_MISS_NUM;
170 l_offer_adjustment_id NUMBER;
171 l_dummy NUMBER;
172 CURSOR c_id IS
173 SELECT ozf_offer_adjustments_b_s.NEXTVAL
174 FROM dual;
175
176 CURSOR c_id_exists (l_id IN NUMBER) IS
177 SELECT 1
178 FROM OZF_OFFER_ADJUSTMENTS_B
179 WHERE offer_adjustment_id = l_id;
180 BEGIN
181 -- Standard Start of API savepoint
182 SAVEPOINT create_offer_adjustment_pvt;
183
184 -- Standard call to check for call compatibility.
185 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
186 p_api_version_number,
187 l_api_name,
188 G_PKG_NAME)
189 THEN
190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
191 END IF;
192
193
194 -- Initialize message list if p_init_msg_list is set to TRUE.
195 IF FND_API.to_Boolean( p_init_msg_list )
196 THEN
197 FND_MSG_PUB.initialize;
198 END IF;
199
200
201
202 -- Debug Message
203 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
204
205
206
207 -- Initialize API return status to SUCCESS
208 x_return_status := FND_API.G_RET_STS_SUCCESS;
209
210 -- Local variable initialization
211
212 IF p_offer_adj_rec.offer_adjustment_id IS NULL OR p_offer_adj_rec.offer_adjustment_id = FND_API.g_miss_num THEN
213 LOOP
214 l_dummy := NULL;
215 OPEN c_id;
216 FETCH c_id INTO l_offer_adjustment_id;
217 CLOSE c_id;
218
219 OPEN c_id_exists(l_offer_adjustment_id);
220 FETCH c_id_exists INTO l_dummy;
221 CLOSE c_id_exists;
222 EXIT WHEN l_dummy IS NULL;
223 END LOOP;
224 ELSE
225 l_offer_adjustment_id := p_offer_adj_rec.offer_adjustment_id;
226 END IF;
227
228 -- OZF_Offer_Adj_Line_PVT.debug_message('Private API: offer_adjustment_id: ' || p_offer_adj_rec.offer_adjustment_id || '::l_offer_adjustment_id: '||l_offer_adjustment_id); -- sangara
229
230 -- =========================================================================
231 -- Validate Environment
232 -- =========================================================================
233
234 IF FND_GLOBAL.USER_ID IS NULL
235 THEN
236 OZF_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
237 RAISE FND_API.G_EXC_ERROR;
238 END IF;
239
240
241
242 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
243 THEN
244 -- Debug message
245 OZF_Offer_Adj_Line_PVT.debug_message('Private API: Validate_Offer_Adjustment');
246
247 -- Invoke validation procedures
248 Validate_offer_adjustment(
249 p_api_version_number => 1.0,
250 p_init_msg_list => FND_API.G_FALSE,
251 p_validation_level => p_validation_level,
252 p_validation_mode => JTF_PLSQL_API.g_create,
253 p_offer_adj_rec => p_offer_adj_rec,
254 x_return_status => x_return_status,
255 x_msg_count => x_msg_count,
256 x_msg_data => x_msg_data);
257 END IF;
258
259 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
260 RAISE FND_API.G_EXC_ERROR;
261 END IF;
262
263
264 -- Debug Message
265 OZF_Offer_Adj_Line_PVT.debug_message( 'Private API: Calling create table handler ');
266
267 -- Invoke table handler(OZF_Offer_Adjustment_Pkg.Insert_Row)
268 OZF_Offer_Adjustment_Pkg.Insert_Row(
269 px_offer_adjustment_id => l_offer_adjustment_id,
270 p_effective_date => p_offer_adj_rec.effective_date,
271 p_approved_date => p_offer_adj_rec.approved_date,
272 p_settlement_code => p_offer_adj_rec.settlement_code,
273 p_status_code => p_offer_adj_rec.status_code,
274 p_list_header_id => p_offer_adj_rec.list_header_id,
275 p_version => p_offer_adj_rec.version,
276 p_budget_adjusted_flag => p_offer_adj_rec.budget_adjusted_flag,
277 p_last_update_date => SYSDATE,
278 p_last_updated_by => FND_GLOBAL.USER_ID,
279 p_creation_date => SYSDATE,
280 p_created_by => FND_GLOBAL.USER_ID,
281 p_last_update_login => FND_GLOBAL.conc_login_id,
282 px_object_version_number => l_object_version_number,
283 p_attribute1 => p_offer_adj_rec.attribute1,
284 p_attribute2 => p_offer_adj_rec.attribute2,
285 p_attribute3 => p_offer_adj_rec.attribute3,
286 p_attribute4 => p_offer_adj_rec.attribute4,
287 p_attribute5 => p_offer_adj_rec.attribute5,
288 p_attribute6 => p_offer_adj_rec.attribute6,
289 p_attribute7 => p_offer_adj_rec.attribute7,
290 p_attribute8 => p_offer_adj_rec.attribute8,
291 p_attribute9 => p_offer_adj_rec.attribute9,
292 p_attribute10 => p_offer_adj_rec.attribute10,
293 p_attribute11 => p_offer_adj_rec.attribute11,
294 p_attribute12 => p_offer_adj_rec.attribute12,
295 p_attribute13 => p_offer_adj_rec.attribute13,
296 p_attribute14 => p_offer_adj_rec.attribute14,
297 p_attribute15 => p_offer_adj_rec.attribute15
298 ,p_offer_adjustment_name => p_offer_adj_rec.offer_adjustment_name,
299 p_description => p_offer_adj_rec.description
300 );
301
302 x_offer_adjustment_id := l_offer_adjustment_id;
303 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
304 RAISE FND_API.G_EXC_ERROR;
305 END IF;
306 --
307 -- End of API body
308 --
309
310 -- Standard check for p_commit
311 IF FND_API.to_Boolean( p_commit )
312 THEN
313 -- OZF_Offer_Adj_Line_PVT.debug_message('Problemo hereo: ');
314 -- RAISE FND_API.G_EXC_ERROR;
315 COMMIT WORK;
316 END IF;
317
318
319 -- Debug Message
320 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
321
322
323 -- Standard call to get message count and if count is 1, get message info.
324 FND_MSG_PUB.Count_And_Get
325 (p_count => x_msg_count,
326 p_data => x_msg_data
327 );
328 EXCEPTION
329
330 WHEN OZF_Utility_PVT.resource_locked THEN
331 x_return_status := FND_API.g_ret_sts_error;
332 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
333
334 WHEN FND_API.G_EXC_ERROR THEN
335 ROLLBACK TO CREATE_Offer_Adjustment_PVT;
336 x_return_status := FND_API.G_RET_STS_ERROR;
337 -- Standard call to get message count and if count=1, get the message
338 FND_MSG_PUB.Count_And_Get (
339 p_encoded => FND_API.G_FALSE,
340 p_count => x_msg_count,
341 p_data => x_msg_data
342 );
343
344 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
345 ROLLBACK TO CREATE_Offer_Adjustment_PVT;
346 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
347 -- Standard call to get message count and if count=1, get the message
348 FND_MSG_PUB.Count_And_Get (
349 p_encoded => FND_API.G_FALSE,
350 p_count => x_msg_count,
351 p_data => x_msg_data
352 );
353
354 WHEN OTHERS THEN
355 ROLLBACK TO CREATE_Offer_Adjustment_PVT;
356 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
357 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
358 THEN
359 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
360 END IF;
361 -- Standard call to get message count and if count=1, get the message
362 FND_MSG_PUB.Count_And_Get (
363 p_encoded => FND_API.G_FALSE,
364 p_count => x_msg_count,
365 p_data => x_msg_data
366 );
367 End Create_Offer_Adjustment;
368
369
370 -- ==============================================================================
371 -- Start of Comments
372 -- ==============================================================================
373 -- API Name
374 -- Update_Offer_Adjustment
375 -- Type
376 -- Private
377 -- Pre-Req
378 --
379 -- Parameters
380 --
381 -- IN
382 -- p_api_version_number IN NUMBER Required
383 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
384 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
385 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
386 -- p_offer_adj_rec IN offer_adj_rec_type Required
387 --
391 -- x_msg_data OUT VARCHAR2
388 -- OUT
389 -- x_return_status OUT VARCHAR2
390 -- x_msg_count OUT NUMBER
392 -- Version : Current version 1.0
393 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
394 -- and basic operation, developer must manually add parameters and business logic as necessary.
395 --
396 -- History
397 --
398 -- NOTE
399 --
400 -- End of Comments
401 -- ==============================================================================
402
403 PROCEDURE Update_Offer_Adjustment(
404 p_api_version_number IN NUMBER,
405 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
406 p_commit IN VARCHAR2 := FND_API.G_FALSE,
407 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
408
409 x_return_status OUT NOCOPY VARCHAR2,
410 x_msg_count OUT NOCOPY NUMBER,
411 x_msg_data OUT NOCOPY VARCHAR2,
412
413 p_offer_adj_rec IN offer_adj_rec_type,
414 x_object_version_number OUT NOCOPY NUMBER
415 )
416
417 IS
418
419
420 CURSOR c_get_offer_adjustment(offer_adjustment_id NUMBER) IS
421 SELECT *
422 FROM OZF_OFFER_ADJUSTMENTS_B
423 WHERE offer_adjustment_id = p_offer_adj_rec.offer_adjustment_id;
424 -- Hint: Developer need to provide Where clause
425
426
427 CURSOR c_if_lines_exist(p_offer_adjustment_id NUMBER) IS
428 select 1
429 from ozf_offer_adjustment_lines
430 where offer_adjustment_id = p_offer_adjustment_id
431 union
432 select 1
433 from ozf_offer_adjustment_tiers
434 where offer_adjustment_id= p_offer_adjustment_id
435 union
436 SELECT 1
437 FROM ozf_offer_adj_new_lines
438 WHERE offer_adjustment_id = p_offer_adjustment_id
439 UNION
440 SELECT 1
441 FROM ozf_offer_adjustment_products
442 WHERE offer_adjustment_id = p_offer_adjustment_id;
443
444
445 -- code added by mthumu
446 CURSOR c_get_offer_status(p_offer_adjustment_id NUMBER) IS
447 SELECT status_code
448 FROM ozf_offer_adjustments_b
449 WHERE offer_adjustment_id = p_offer_adjustment_id ;
450 -- end mthumu
451
452 CURSOR c_custom_setup_id(p_list_header_id NUMBER) IS
453 SELECT custom_setup_id ,NVL(budget_offer_yn, 'N')
454 FROM ozf_offers
455 WHERE qp_list_header_id=p_list_header_id;
456 -- code to get custom setup for approval
457 CURSOR c_attr_available_flag(p_custom_setup_id NUMBER) IS
458 SELECT attr_available_flag
459 FROM ams_custom_setup_attr
460 WHERE custom_setup_id = p_custom_setup_id
461 AND object_attribute = 'ADJA';
462
463 CURSOR c_backdate_flag(p_list_header_id NUMBER, p_offer_adjustment_id NUMBER, p_effective_date DATE) IS
464 SELECT 1
465 FROM OZF_OFFER_ADJUSTMENTS_VL
466 WHERE (p_effective_date<SYSDATE)
467 AND list_header_id = p_list_header_id
468 AND offer_adjustment_id = p_offer_adjustment_id;
469
470
471 -- end of code
472
473 l_custom_setup_id NUMBER;
474 l_budget_offer_yn VARCHAR2(1);
475 l_attr_available_flag VARCHAR2(1);
476 l_backdate_flag VARCHAR2(1);
477
478 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Offer_Adjustment';
479 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
480 -- Local Variables
481 l_object_version_number NUMBER;
482 l_offer_adjustment_id NUMBER;
483 l_ref_offer_adj_rec c_get_Offer_Adjustment%ROWTYPE ;
484 l_tar_offer_adj_rec offer_adj_rec_type := P_offer_adj_rec;
485 l_rowid ROWID;
486 l_if_lines_exist NUMBER;
487
488
489 -- code added by mthumu
490 l_current_status_code VARCHAR2(30);
491 l_new_status_code VARCHAR2(30);
492 l_approve_date DATE;
493 l_budgetAdjFlag VARCHAR2(1);
494 -- end mthumu
495
496 BEGIN
497 -- Standard Start of API savepoint
498 SAVEPOINT update_offer_adjustment_pvt;
499
500 -- Standard call to check for call compatibility.
501 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
502 p_api_version_number,
503 l_api_name,
504 G_PKG_NAME)
505 THEN
506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
507 END IF;
508
509
510 -- Initialize message list if p_init_msg_list is set to TRUE.
511 IF FND_API.to_Boolean( p_init_msg_list )
512 THEN
513 FND_MSG_PUB.initialize;
514 END IF;
515
516
517
518 -- Debug Message
519 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
520
521
522 -- Initialize Object_Version_Number
523 l_object_version_number := p_offer_adj_rec.object_version_number ;
524
525 -- Initialize API return status to SUCCESS
526 x_return_status := FND_API.G_RET_STS_SUCCESS;
527
528 -- Debug Message
529 OZF_Offer_Adj_Line_PVT.debug_message('Private API: - Open Cursor to Select');
530
531 OPEN c_get_Offer_Adjustment( l_tar_offer_adj_rec.offer_adjustment_id);
532
536 OZF_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
533 FETCH c_get_Offer_Adjustment INTO l_ref_offer_adj_rec ;
534
535 If ( c_get_Offer_Adjustment%NOTFOUND) THEN
537 p_token_name => 'INFO',
538 p_token_value => 'Offer_Adjustment') ;
539 RAISE FND_API.G_EXC_ERROR;
540 END IF;
541
542 -- Debug Message
543 OZF_Offer_Adj_Line_PVT.debug_message('Private API: - Close Cursor');
544 CLOSE c_get_Offer_Adjustment;
545
546
547 If (l_tar_offer_adj_rec.object_version_number is NULL or
548 l_tar_offer_adj_rec.object_version_number = FND_API.G_MISS_NUM ) Then
549 OZF_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
550 p_token_name => 'COLUMN',
551 p_token_value => 'Last_Update_Date') ;
552 raise FND_API.G_EXC_ERROR;
553 End if;
554
555
556 -- Check Whether record has been changed by someone else
557
558 If (l_tar_offer_adj_rec.object_version_number <> l_ref_offer_adj_rec.object_version_number) Then
559 OZF_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
560 p_token_name => 'INFO',
561 p_token_value => 'Offer_Adjustment') ;
562 raise FND_API.G_EXC_ERROR;
563 End if;
564
565
566 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
567 THEN
568 -- Debug message
569 OZF_Offer_Adj_Line_PVT.debug_message('Private API: Validate_Offer_Adjustment');
570
571 -- Invoke validation procedures
572 Validate_offer_adjustment(
573 p_api_version_number => 1.0,
574 p_init_msg_list => FND_API.G_FALSE,
575 p_validation_level => p_validation_level,
576 p_validation_mode => JTF_PLSQL_API.g_update,
577 p_offer_adj_rec => p_offer_adj_rec,
578 x_return_status => x_return_status,
579 x_msg_count => x_msg_count,
580 x_msg_data => x_msg_data);
581 END IF;
582
583 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
584 RAISE FND_API.G_EXC_ERROR;
585 END IF;
586
587 -- code added by mthumu to call the budget approval
588 OPEN c_get_offer_status(p_offer_adj_rec.offer_adjustment_id);
589 FETCH c_get_offer_status INTO l_current_status_code;
590 CLOSE c_get_offer_status;
591
592 l_new_status_code := p_offer_adj_rec.status_code;
593
594 IF ( l_current_status_code <> l_new_status_code )
595 THEN
596
597 IF ( l_new_status_code = 'ACTIVE' )
598 THEN
599
600 -- Submit
601 OPEN c_if_lines_exist( p_offer_adj_rec.offer_adjustment_id );
602 FETCH c_if_lines_exist INTO l_if_lines_exist;
603 CLOSE c_if_lines_exist;
604
605 IF l_if_lines_exist IS NULL THEN
606 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFRADJ_DISCRULE');
607
608 RAISE FND_API.G_EXC_ERROR;
609 END IF;
610
611 -- Call Approval Work Flow
612 -- Select attr_available_flag
613 -- from ams_custom_setup_attr
614 -- where custom_setup_id = 91
615 -- and object_attribute = 'ADJA';
616
617 -- have to get the custom setup from offers table from another cursor
618
619 -- IF attr_available_flag= 'Y' Then
620 -- call workflow;
621 -- else
622 -- call update offer discounts; ( API as shown below)
623 -- end if;
624
625
626 OPEN c_custom_setup_id( p_offer_adj_rec.list_header_id );
627 FETCH c_custom_setup_id INTO l_custom_setup_id, l_budget_offer_yn;
628 CLOSE c_custom_setup_id;
629
630 OPEN c_attr_available_flag(l_custom_setup_id);
631 FETCH c_attr_available_flag INTO l_attr_available_flag;
632 CLOSE c_attr_available_flag;
633
634 raise_event(p_id => p_offer_adj_rec.offer_adjustment_id );
635 OZF_Offer_Adj_Line_PVT.debug_message(' l_attr_available_flag ' || l_attr_available_flag);
636 -- mthumu approve date fix
637 l_new_status_code := 'ACTIVE';
638 l_approve_date := sysdate;
639
640 IF l_budget_offer_yn = 'Y' THEN
641 AMS_GEN_APPROVAL_PVT.StartProcess
642 (p_activity_type => 'FAB_ADJ'
643 ,p_activity_id => p_offer_adj_rec.offer_adjustment_id
644 ,p_approval_type => 'BUDGET'
645 ,p_object_version_number =>p_offer_adj_rec.object_version_number
646 ,p_orig_stat_id => 0
647 ,p_new_stat_id => 0
648 ,p_reject_stat_id => 0
649 ,p_requester_userid => OZF_Utility_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
650 ,p_notes_from_requester => p_offer_adj_rec.description
651 ,p_workflowprocess => 'AMSGAPP'
652 ,p_item_type => 'AMSGAPP');
653
654 l_new_status_code := 'PENDING';
655 ELSIF l_attr_available_flag = 'Y' THEN
656 AMS_GEN_APPROVAL_PVT.StartProcess
657 (p_activity_type => 'OFFR'
658 ,p_activity_id => p_offer_adj_rec.offer_adjustment_id
659 ,p_approval_type => 'BUDGET'
663 ,p_reject_stat_id => 0
660 ,p_object_version_number =>p_offer_adj_rec.object_version_number
661 ,p_orig_stat_id => 0
662 ,p_new_stat_id => 0
664 ,p_requester_userid => OZF_Utility_PVT.get_resource_id(NVL(FND_GLOBAL.user_id,-1))
665 ,p_notes_from_requester => p_offer_adj_rec.description
666 ,p_workflowprocess => 'AMSGAPP'
667 ,p_item_type => 'AMSGAPP');
668
669 l_new_status_code := 'PENDING';
670 ELSE
671 OZF_Offer_Backdate_PVT.Update_Offer_Discounts (
672 p_init_msg_list => FND_API.G_FALSE
673 ,p_api_version => 1.0
674 ,p_commit => FND_API.G_FALSE
675 ,x_return_status => x_return_status
676 ,x_msg_count => x_msg_count
677 ,x_msg_data => x_msg_data
678 ,p_offer_adjustment_id => p_offer_adj_rec.offer_adjustment_id
679 -- ,p_close_adj => 'Y'
680 ) ;
681 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
682 RAISE FND_API.G_EXC_ERROR;
683 END IF;
684 OZF_Offer_Backdate_PVT.getCloseAdjustmentParams
685 (
686 p_offer_adjustment_id => p_offer_adj_rec.offer_adjustment_id
687 ,x_return_status => x_return_status
688 ,x_msg_count => x_msg_count
689 ,x_msg_data => x_msg_data
690 ,x_newStatus => l_new_status_code
691 ,x_budgetAdjFlag => l_budgetAdjFlag
692 );
693 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
694 RAISE FND_API.G_EXC_ERROR;
695 END IF;
696 END IF;
697 END IF;
698 END IF;
699 -- end of code mthumu
700 -- Debug Message
701 OZF_Offer_Adj_Line_PVT.debug_message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW||'Private API: Calling update table handler');
702 -- Invoke table handler(OZF_Offer_Adjustment_Pkg.Update_Row)
703 OZF_Offer_Adjustment_Pkg.Update_Row(
704 p_offer_adjustment_id => p_offer_adj_rec.offer_adjustment_id,
705 p_effective_date => p_offer_adj_rec.effective_date,
706 p_approved_date => l_approve_date,--p_offer_adj_rec.approved_date,
707 p_settlement_code => p_offer_adj_rec.settlement_code,
708 p_status_code => l_new_status_code, -- p_offer_adj_rec.status_code,
709 p_list_header_id => p_offer_adj_rec.list_header_id,
710 p_version => p_offer_adj_rec.version,
711 p_budget_adjusted_flag => l_budgetAdjFlag,
712 p_last_update_date => SYSDATE,
713 p_last_updated_by => FND_GLOBAL.USER_ID,
714 p_last_update_login => FND_GLOBAL.conc_login_id,
715 px_object_version_number => l_object_version_number,
716 p_attribute1 => p_offer_adj_rec.attribute1,
717 p_attribute2 => p_offer_adj_rec.attribute2,
718 p_attribute3 => p_offer_adj_rec.attribute3,
719 p_attribute4 => p_offer_adj_rec.attribute4,
720 p_attribute5 => p_offer_adj_rec.attribute5,
721 p_attribute6 => p_offer_adj_rec.attribute6,
722 p_attribute7 => p_offer_adj_rec.attribute7,
723 p_attribute8 => p_offer_adj_rec.attribute8,
724 p_attribute9 => p_offer_adj_rec.attribute9,
725 p_attribute10 => p_offer_adj_rec.attribute10,
726 p_attribute11 => p_offer_adj_rec.attribute11,
727 p_attribute12 => p_offer_adj_rec.attribute12,
728 p_attribute13 => p_offer_adj_rec.attribute13,
729 p_attribute14 => p_offer_adj_rec.attribute14,
730 p_attribute15 => p_offer_adj_rec.attribute15
731 ,p_offer_adjustment_name => p_offer_adj_rec.offer_adjustment_name,
732 p_description => p_offer_adj_rec.description
733 );
734
735 x_object_version_number := l_object_version_number;
736 --
737 -- End of API body.
738 --
739 IF FND_API.to_Boolean( p_commit )
740 THEN
741 COMMIT WORK;
742 END IF;
743 -- Debug Message
744 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
745 EXCEPTION
746 WHEN OZF_Utility_PVT.resource_locked THEN
747 x_return_status := FND_API.g_ret_sts_error;
748 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
749 WHEN FND_API.G_EXC_ERROR THEN
750 ROLLBACK TO UPDATE_Offer_Adjustment_PVT;
751 x_return_status := FND_API.G_RET_STS_ERROR;
752 -- Standard call to get message count and if count=1, get the message
753 FND_MSG_PUB.Count_And_Get (
754 p_encoded => FND_API.G_FALSE,
755 p_count => x_msg_count,
756 p_data => x_msg_data
757 );
758 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
759 ROLLBACK TO UPDATE_Offer_Adjustment_PVT;
760 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
764 p_count => x_msg_count,
761 -- Standard call to get message count and if count=1, get the message
762 FND_MSG_PUB.Count_And_Get (
763 p_encoded => FND_API.G_FALSE,
765 p_data => x_msg_data
766 );
767
768 WHEN OTHERS THEN
769 ROLLBACK TO UPDATE_Offer_Adjustment_PVT;
770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
771 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
772 THEN
773 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
774 END IF;
775 -- Standard call to get message count and if count=1, get the message
776 FND_MSG_PUB.Count_And_Get (
777 p_encoded => FND_API.G_FALSE,
778 p_count => x_msg_count,
779 p_data => x_msg_data
780 );
781 End Update_Offer_Adjustment;
782
783
784 -- ==============================================================================
785 -- Start of Comments
786 -- ==============================================================================
787 -- API Name
788 -- Delete_Offer_Adjustment
789 -- Type
790 -- Private
791 -- Pre-Req
792 --
793 -- Parameters
794 --
795 -- IN
796 -- p_api_version_number IN NUMBER Required
797 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
798 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
799 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
800 -- p_offer_adjustment_id IN NUMBER
801 -- p_object_version_number IN NUMBER Optional Default = NULL
802 --
803 -- OUT
804 -- x_return_status OUT VARCHAR2
805 -- x_msg_count OUT NUMBER
806 -- x_msg_data OUT VARCHAR2
807 -- Version : Current version 1.0
808 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
809 -- and basic operation, developer must manually add parameters and business logic as necessary.
810 --
811 -- History
812 --
813 -- NOTE
814 --
815 -- End of Comments
816 -- ==============================================================================
817
818 PROCEDURE Delete_Offer_Adjustment(
819 p_api_version_number IN NUMBER,
820 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
821 p_commit IN VARCHAR2 := FND_API.G_FALSE,
822 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
823 x_return_status OUT NOCOPY VARCHAR2,
824 x_msg_count OUT NOCOPY NUMBER,
825 x_msg_data OUT NOCOPY VARCHAR2,
826 p_offer_adjustment_id IN NUMBER,
827 p_object_version_number IN NUMBER
828 )
829
830 IS
831 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Offer_Adjustment';
832 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
833 l_object_version_number NUMBER;
834
835 BEGIN
836 -- Standard Start of API savepoint
837 SAVEPOINT delete_offer_adjustment_pvt;
838
839 -- Standard call to check for call compatibility.
840 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
841 p_api_version_number,
842 l_api_name,
843 G_PKG_NAME)
844 THEN
845 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
846 END IF;
847
848
849 -- Initialize message list if p_init_msg_list is set to TRUE.
850 IF FND_API.to_Boolean( p_init_msg_list )
851 THEN
852 FND_MSG_PUB.initialize;
853 END IF;
854
855
856
857 -- Debug Message
858 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
859
860
861
862 -- Initialize API return status to SUCCESS
863 x_return_status := FND_API.G_RET_STS_SUCCESS;
864
865 --
866 -- Api body
867 --
868 -- Debug Message
869 OZF_Offer_Adj_Line_PVT.debug_message( 'Private API: Calling delete table handler');
870
871 -- Invoke table handler(OZF_Offer_Adjustment_Pkg.Delete_Row)
872 OZF_Offer_Adjustment_Pkg.Delete_Row(
873 p_offer_adjustment_id => p_offer_adjustment_id,
874 p_object_version_number => p_object_version_number );
875 --
876 -- End of API body
877 --
878
879 -- Standard check for p_commit
880 IF FND_API.to_Boolean( p_commit )
881 THEN
882 COMMIT WORK;
883 END IF;
884
885
886 -- Debug Message
887 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
888
889
890 -- Standard call to get message count and if count is 1, get message info.
891 FND_MSG_PUB.Count_And_Get
892 (p_count => x_msg_count,
893 p_data => x_msg_data
894 );
895 EXCEPTION
896
897 WHEN OZF_Utility_PVT.resource_locked THEN
898 x_return_status := FND_API.g_ret_sts_error;
902 ROLLBACK TO DELETE_Offer_Adjustment_PVT;
899 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
900
901 WHEN FND_API.G_EXC_ERROR THEN
903 x_return_status := FND_API.G_RET_STS_ERROR;
904 -- Standard call to get message count and if count=1, get the message
905 FND_MSG_PUB.Count_And_Get (
906 p_encoded => FND_API.G_FALSE,
907 p_count => x_msg_count,
908 p_data => x_msg_data
909 );
910
911 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
912 ROLLBACK TO DELETE_Offer_Adjustment_PVT;
913 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
914 -- Standard call to get message count and if count=1, get the message
915 FND_MSG_PUB.Count_And_Get (
916 p_encoded => FND_API.G_FALSE,
917 p_count => x_msg_count,
918 p_data => x_msg_data
919 );
920
921 WHEN OTHERS THEN
922 ROLLBACK TO DELETE_Offer_Adjustment_PVT;
923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
924 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
925 THEN
926 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
927 END IF;
928 -- Standard call to get message count and if count=1, get the message
929 FND_MSG_PUB.Count_And_Get (
930 p_encoded => FND_API.G_FALSE,
931 p_count => x_msg_count,
932 p_data => x_msg_data
933 );
934 End Delete_Offer_Adjustment;
935
936
937
938 -- Hint: Primary key needs to be returned.
939 -- ==============================================================================
940 -- Start of Comments
941 -- ==============================================================================
942 -- API Name
943 -- Lock_Offer_Adjustment
944 -- Type
945 -- Private
946 -- Pre-Req
947 --
948 -- Parameters
949 --
950 -- IN
951 -- p_api_version_number IN NUMBER Required
952 -- p_init_msg_list IN VARCHAR2 Optional Default = FND_API_G_FALSE
953 -- p_commit IN VARCHAR2 Optional Default = FND_API.G_FALSE
954 -- p_validation_level IN NUMBER Optional Default = FND_API.G_VALID_LEVEL_FULL
955 -- p_offer_adj_rec IN offer_adj_rec_type Required
956 --
957 -- OUT
958 -- x_return_status OUT VARCHAR2
959 -- x_msg_count OUT NUMBER
960 -- x_msg_data OUT VARCHAR2
961 -- Version : Current version 1.0
962 -- Note: This automatic generated procedure definition, it includes standard IN/OUT parameters
963 -- and basic operation, developer must manually add parameters and business logic as necessary.
964 --
965 -- History
966 --
967 -- NOTE
968 --
969 -- End of Comments
970 -- ==============================================================================
971
972 PROCEDURE Lock_Offer_Adjustment(
973 p_api_version_number IN NUMBER,
974 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
975
976 x_return_status OUT NOCOPY VARCHAR2,
977 x_msg_count OUT NOCOPY NUMBER,
978 x_msg_data OUT NOCOPY VARCHAR2,
979
980 p_offer_adjustment_id IN NUMBER,
981 p_object_version IN NUMBER
982 )
983
984 IS
985 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Offer_Adjustment';
986 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
987 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
988 l_offer_adjustment_id NUMBER;
989
990 BEGIN
991
992 -- Debug Message
993 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
994
995
996 -- Initialize message list if p_init_msg_list is set to TRUE.
997 IF FND_API.to_Boolean( p_init_msg_list )
998 THEN
999 FND_MSG_PUB.initialize;
1000 END IF;
1001
1002
1003
1004 -- Standard call to check for call compatibility.
1005 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1006 p_api_version_number,
1007 l_api_name,
1008 G_PKG_NAME)
1009 THEN
1010 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1011 END IF;
1012
1013
1014
1015 -- Initialize API return status to SUCCESS
1016 x_return_status := FND_API.G_RET_STS_SUCCESS;
1017
1018
1019 ------------------------ lock -------------------------
1020 OZF_Offer_Adjustment_Pkg.Lock_Row(l_offer_adjustment_id,p_object_version);
1021
1022
1023 -------------------- finish --------------------------
1024 FND_MSG_PUB.count_and_get(
1025 p_encoded => FND_API.g_false,
1026 p_count => x_msg_count,
1027 p_data => x_msg_data);
1028 OZF_Offer_Adj_Line_PVT.debug_message(l_full_name ||': end');
1029 EXCEPTION
1030
1031 WHEN OZF_Utility_PVT.resource_locked THEN
1032 x_return_status := FND_API.g_ret_sts_error;
1033 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1034
1038 -- Standard call to get message count and if count=1, get the message
1035 WHEN FND_API.G_EXC_ERROR THEN
1036 ROLLBACK TO LOCK_Offer_Adjustment_PVT;
1037 x_return_status := FND_API.G_RET_STS_ERROR;
1039 FND_MSG_PUB.Count_And_Get (
1040 p_encoded => FND_API.G_FALSE,
1041 p_count => x_msg_count,
1042 p_data => x_msg_data
1043 );
1044
1045 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1046 ROLLBACK TO LOCK_Offer_Adjustment_PVT;
1047 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1048 -- Standard call to get message count and if count=1, get the message
1049 FND_MSG_PUB.Count_And_Get (
1050 p_encoded => FND_API.G_FALSE,
1051 p_count => x_msg_count,
1052 p_data => x_msg_data
1053 );
1054
1055 WHEN OTHERS THEN
1056 ROLLBACK TO LOCK_Offer_Adjustment_PVT;
1057 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1058 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1059 THEN
1060 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1061 END IF;
1062 -- Standard call to get message count and if count=1, get the message
1063 FND_MSG_PUB.Count_And_Get (
1064 p_encoded => FND_API.G_FALSE,
1065 p_count => x_msg_count,
1066 p_data => x_msg_data
1067 );
1068 End Lock_Offer_Adjustment;
1069
1070
1071
1072
1073 PROCEDURE check_Offer_Adj_Uk_Items(
1074 p_offer_adj_rec IN offer_adj_rec_type,
1075 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1076 x_return_status OUT NOCOPY VARCHAR2)
1077 IS
1078 -- Cursor to check, if 2 adjustments exist with same effective date
1079 CURSOR c_adj_exists_oncreate (l_objid IN NUMBER, l_effdt IN DATE) IS
1080 SELECT 1
1081 FROM OZF_OFFER_ADJUSTMENTS_B
1082 WHERE list_header_id = l_objid
1083 AND effective_date = l_effdt
1084 AND status_code NOT IN ('CANCELLED' ,'CLOSED');
1085
1086 CURSOR c_adj_exists_onupdate (l_objid IN NUMBER, l_adjid IN NUMBER, l_effdt IN DATE) IS
1087 SELECT 1
1088 FROM OZF_OFFER_ADJUSTMENTS_B
1089 WHERE list_header_id = l_objid
1090 AND offer_adjustment_id <> l_adjid
1091 AND effective_date = l_effdt
1092 AND status_code NOT IN ('CANCELLED' ,'CLOSED');
1093
1094 l_valid_flag VARCHAR2(1);
1095 l_dup_effective_dt NUMBER;
1096 l_strClosed VARCHAR2(20) := ' ''CLOSED'' ';
1097 l_strTerminated VARCHAR2(20) := ' ''TERMINATED'' ';
1098
1099 BEGIN
1100 x_return_status := FND_API.g_ret_sts_success;
1101 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1102 OZF_Offer_Adj_Line_PVT.debug_message('Inside Uk_Items::Create');
1103 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
1104 ' ozf_offer_adjustments_vl ',
1105 ' list_header_id = '||' '|| p_offer_adj_rec.list_header_id ||
1106 ' AND offer_adjustment_name ='||' '|| p_offer_adj_rec.offer_adjustment_name||''
1107 );
1108 -- Check for, 2 adjustments with same effective date -sangara
1109
1110 -- l_dup_effective_dt := OZF_Utility_PVT.check_uniqueness(
1111 -- 'ozf_offer_adjustments_b',
1112 -- ' list_header_id = '||' '|| p_offer_adj_rec.list_header_id ||''||
1113 -- ' AND status_code <> '||' '|| l_strTerminated ||''||
1114 -- ' OR status_code <> '||' '|| l_strClosed ||''||
1115 -- ' AND effective_date = '||' '|| p_offer_adj_rec.effective_date
1116 -- );
1117
1118 OPEN c_adj_exists_oncreate(p_offer_adj_rec.list_header_id, p_offer_adj_rec.effective_date);
1119 FETCH c_adj_exists_oncreate INTO l_dup_effective_dt ;
1120 CLOSE c_adj_exists_oncreate;
1121
1122 ELSE
1123 l_valid_flag := OZF_Utility_PVT.check_uniqueness(
1124 ' ozf_offer_adjustments_vl ',
1125 ' list_header_id = '||' '|| p_offer_adj_rec.list_header_id ||
1126 ' AND offer_adjustment_id <> '||' '|| p_offer_adj_rec.offer_adjustment_id||' '||
1127 ' AND offer_adjustment_name ='||' '|| p_offer_adj_rec.offer_adjustment_name||''
1128 );
1129 -- Check for, 2 adjustments with same effective date -sangara
1130 -- l_dup_effective_dt := OZF_Utility_PVT.check_uniqueness(
1131 -- 'ozf_offer_adjustments_b',
1132 -- 'list_header_id =' || p_offer_adj_rec.list_header_id ||
1133 -- ' AND offer_adjustment_id <> '||' '|| p_offer_adj_rec.offer_adjustment_id||' '||
1134 -- ' AND status_code <> '||' '|| l_strTerminated ||''||
1135 -- ' OR status_code <> '||' '|| l_strClosed ||''||
1136 -- ' AND effective_date = '||' '|| p_offer_adj_rec.effective_date
1137 -- );
1138
1139
1140 OPEN c_adj_exists_onupdate(p_offer_adj_rec.list_header_id, p_offer_adj_rec.offer_adjustment_id, p_offer_adj_rec.effective_date);
1141 FETCH c_adj_exists_onupdate INTO l_dup_effective_dt ;
1142 CLOSE c_adj_exists_onupdate;
1143
1144 END IF;
1145
1146
1147 IF l_valid_flag = FND_API.g_false THEN
1148 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFER_ADJ_NAME_DUP');
1149 x_return_status := FND_API.g_ret_sts_error;
1150 END IF;
1154 IF l_dup_effective_dt IS NOT NULL THEN
1151
1152 OZF_Offer_Adj_Line_PVT.debug_message('check_Offer_Adj_Uk_Items :: l_dup_effective_dt ' || l_dup_effective_dt );
1153
1155 IF l_dup_effective_dt = 1 THEN -- Show error, if 2 adjs. with same eff date exists. - sangara
1156 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_OFFADJ_DUP_EFFDT');
1157 x_return_status := FND_API.g_ret_sts_error;
1158 END IF;
1159 END IF;
1160
1161 END check_Offer_Adj_Uk_Items;
1162
1163
1164
1165 PROCEDURE check_Offer_Adj_Req_Items(
1166 p_offer_adj_rec IN offer_adj_rec_type,
1167 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1168 x_return_status OUT NOCOPY VARCHAR2
1169 )
1170 IS
1171 BEGIN
1172 x_return_status := FND_API.g_ret_sts_success;
1173
1174 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1175
1176
1177 -- IF p_offer_adj_rec.offer_adjustment_id = FND_API.G_MISS_NUM OR p_offer_adj_rec.offer_adjustment_id IS NULL THEN
1178 -- OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ADJUSTMENT_ID' );
1179 -- x_return_status := FND_API.g_ret_sts_error;
1180 -- END IF;
1181
1182 -- List Header Id check
1183 IF p_offer_adj_rec.list_header_id = FND_API.G_MISS_NUM OR p_offer_adj_rec.list_header_id IS NULL THEN
1184 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LIST_HEADER_ID' );
1185 x_return_status := FND_API.g_ret_sts_error;
1186 END IF;
1187
1188 -- Adjustment Name check
1189 IF p_offer_adj_rec.offer_adjustment_name = FND_API.G_MISS_CHAR OR p_offer_adj_rec.offer_adjustment_name IS NULL THEN
1190 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ADJUSTMENT_NAME' );
1191 x_return_status := FND_API.g_ret_sts_error;
1192 END IF;
1193
1194 -- Status Code check
1195 IF p_offer_adj_rec.status_code = FND_API.G_MISS_CHAR OR p_offer_adj_rec.offer_adjustment_name IS NULL THEN
1196 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'STATUS_CODE' );
1197 x_return_status := FND_API.g_ret_sts_error;
1198 END IF;
1199
1200 -- Effective Date check
1201 IF p_offer_adj_rec.effective_date = FND_API.G_MISS_DATE OR p_offer_adj_rec.effective_date IS NULL THEN
1202 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'EFFECTIVE_DATE' );
1203 x_return_status := FND_API.g_ret_sts_error;
1204 END IF;
1205
1206 ELSE
1207
1208 IF p_offer_adj_rec.offer_adjustment_id = FND_API.G_MISS_NUM THEN
1209 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'OFFER_ADJUSTMENT_ID' );
1210 x_return_status := FND_API.g_ret_sts_error;
1211 END IF;
1212
1213
1214 IF p_offer_adj_rec.list_header_id = FND_API.G_MISS_NUM THEN
1215 OZF_Utility_PVT.Error_Message('OZF_API_MISSING_FIELD', 'MISS_FIELD', 'LIST_HEADER_ID' );
1216 x_return_status := FND_API.g_ret_sts_error;
1217 END IF;
1218
1219 END IF;
1220
1221 END check_Offer_Adj_Req_Items;
1222
1223
1224
1225 PROCEDURE check_Offer_Adj_Fk_Items(
1226 p_offer_adj_rec IN offer_adj_rec_type,
1227 x_return_status OUT NOCOPY VARCHAR2
1228 )
1229 IS
1230 BEGIN
1231 x_return_status := FND_API.g_ret_sts_success;
1232
1233 -- Enter custom code here
1234
1235 END check_Offer_Adj_Fk_Items;
1236
1237
1238
1239 PROCEDURE check_Offer_Adj_Lookup_Items(
1240 p_offer_adj_rec IN offer_adj_rec_type,
1241 x_return_status OUT NOCOPY VARCHAR2
1242 )
1243 IS
1244 BEGIN
1245 x_return_status := FND_API.g_ret_sts_success;
1246
1247 -- Enter custom code here
1248
1249 END check_Offer_Adj_Lookup_Items;
1250
1251
1252
1253 PROCEDURE Check_Offer_Adj_Items (
1254 P_offer_adj_rec IN offer_adj_rec_type,
1255 p_validation_mode IN VARCHAR2,
1256 x_return_status OUT NOCOPY VARCHAR2
1257 )
1258 IS
1259 l_return_status VARCHAR2(1);
1260 BEGIN
1261
1262 l_return_status := FND_API.g_ret_sts_success;
1263 -- Check Items Uniqueness API calls
1264
1265 check_Offer_adj_Uk_Items(
1266 p_offer_adj_rec => p_offer_adj_rec,
1267 p_validation_mode => p_validation_mode,
1268 x_return_status => x_return_status);
1269 IF x_return_status <> FND_API.g_ret_sts_success THEN
1270 l_return_status := FND_API.g_ret_sts_error;
1271 END IF;
1272
1273 -- Check Items Required/NOT NULL API calls
1274
1275 check_offer_adj_req_items(
1276 p_offer_adj_rec => p_offer_adj_rec,
1277 p_validation_mode => p_validation_mode,
1278 x_return_status => x_return_status);
1279 IF x_return_status <> FND_API.g_ret_sts_success THEN
1280 l_return_status := FND_API.g_ret_sts_error;
1281 END IF;
1282 -- Check Items Foreign Keys API calls
1283
1284 check_offer_adj_FK_items(
1285 p_offer_adj_rec => p_offer_adj_rec,
1286 x_return_status => x_return_status);
1287 IF x_return_status <> FND_API.g_ret_sts_success THEN
1288 l_return_status := FND_API.g_ret_sts_error;
1289 END IF;
1290 -- Check Items Lookups
1291
1292 check_offer_adj_Lookup_items(
1293 p_offer_adj_rec => p_offer_adj_rec,
1297 END IF;
1294 x_return_status => x_return_status);
1295 IF x_return_status <> FND_API.g_ret_sts_success THEN
1296 l_return_status := FND_API.g_ret_sts_error;
1298
1299 x_return_status := l_return_status;
1300
1301 END Check_offer_adj_Items;
1302
1303
1304
1305
1306
1307 PROCEDURE Complete_Offer_Adj_Rec (
1308 p_offer_adj_rec IN offer_adj_rec_type,
1309 x_complete_rec OUT NOCOPY offer_adj_rec_type)
1310 IS
1311 l_return_status VARCHAR2(1);
1312
1313 CURSOR c_complete IS
1314 SELECT *
1315 FROM ozf_offer_adjustments_b
1316 WHERE offer_adjustment_id = p_offer_adj_rec.offer_adjustment_id;
1317 l_offer_adj_rec c_complete%ROWTYPE;
1318 BEGIN
1319 x_complete_rec := p_offer_adj_rec;
1320
1321
1322 OPEN c_complete;
1323 FETCH c_complete INTO l_offer_adj_rec;
1324 CLOSE c_complete;
1325
1326 -- offer_adjustment_id
1327 IF p_offer_adj_rec.offer_adjustment_id IS NULL THEN
1328 x_complete_rec.offer_adjustment_id := l_offer_adj_rec.offer_adjustment_id;
1329 END IF;
1330
1331 -- effective_date
1332 IF p_offer_adj_rec.effective_date IS NULL THEN
1333 x_complete_rec.effective_date := l_offer_adj_rec.effective_date;
1334 END IF;
1335
1336 -- approved_date
1337 IF p_offer_adj_rec.approved_date IS NULL THEN
1338 x_complete_rec.approved_date := l_offer_adj_rec.approved_date;
1339 END IF;
1340
1341 -- settlement_code
1342 IF p_offer_adj_rec.settlement_code IS NULL THEN
1343 x_complete_rec.settlement_code := l_offer_adj_rec.settlement_code;
1344 END IF;
1345
1346 -- status_code
1347 IF p_offer_adj_rec.status_code IS NULL THEN
1348 x_complete_rec.status_code := l_offer_adj_rec.status_code;
1349 END IF;
1350
1351 -- list_header_id
1352 IF p_offer_adj_rec.list_header_id IS NULL THEN
1353 x_complete_rec.list_header_id := l_offer_adj_rec.list_header_id;
1354 END IF;
1355
1356 -- version
1357 IF p_offer_adj_rec.version IS NULL THEN
1358 x_complete_rec.version := l_offer_adj_rec.version;
1359 END IF;
1360
1361 -- budget_adjusted_flag
1362 IF p_offer_adj_rec.budget_adjusted_flag IS NULL THEN
1363 x_complete_rec.budget_adjusted_flag := l_offer_adj_rec.budget_adjusted_flag;
1364 END IF;
1365
1366 -- last_update_date
1367 IF p_offer_adj_rec.last_update_date IS NULL THEN
1368 x_complete_rec.last_update_date := l_offer_adj_rec.last_update_date;
1369 END IF;
1370
1371 -- last_updated_by
1372 IF p_offer_adj_rec.last_updated_by IS NULL THEN
1373 x_complete_rec.last_updated_by := l_offer_adj_rec.last_updated_by;
1374 END IF;
1375
1376 -- creation_date
1377 IF p_offer_adj_rec.creation_date IS NULL THEN
1378 x_complete_rec.creation_date := l_offer_adj_rec.creation_date;
1379 END IF;
1380
1381 -- created_by
1382 IF p_offer_adj_rec.created_by IS NULL THEN
1383 x_complete_rec.created_by := l_offer_adj_rec.created_by;
1384 END IF;
1385
1386 -- last_update_login
1387 IF p_offer_adj_rec.last_update_login IS NULL THEN
1388 x_complete_rec.last_update_login := l_offer_adj_rec.last_update_login;
1389 END IF;
1390
1391 -- object_version_number
1392 IF p_offer_adj_rec.object_version_number IS NULL THEN
1393 x_complete_rec.object_version_number := l_offer_adj_rec.object_version_number;
1394 END IF;
1395
1396 -- attribute1
1397 IF p_offer_adj_rec.attribute1 IS NULL THEN
1398 x_complete_rec.attribute1 := l_offer_adj_rec.attribute1;
1399 END IF;
1400
1401 -- attribute2
1402 IF p_offer_adj_rec.attribute2 IS NULL THEN
1403 x_complete_rec.attribute2 := l_offer_adj_rec.attribute2;
1404 END IF;
1405
1406 -- attribute3
1407 IF p_offer_adj_rec.attribute3 IS NULL THEN
1408 x_complete_rec.attribute3 := l_offer_adj_rec.attribute3;
1409 END IF;
1410
1411 -- attribute4
1412 IF p_offer_adj_rec.attribute4 IS NULL THEN
1413 x_complete_rec.attribute4 := l_offer_adj_rec.attribute4;
1414 END IF;
1415
1416 -- attribute5
1417 IF p_offer_adj_rec.attribute5 IS NULL THEN
1418 x_complete_rec.attribute5 := l_offer_adj_rec.attribute5;
1419 END IF;
1420
1421 -- attribute6
1422 IF p_offer_adj_rec.attribute6 IS NULL THEN
1423 x_complete_rec.attribute6 := l_offer_adj_rec.attribute6;
1424 END IF;
1425
1426 -- attribute7
1427 IF p_offer_adj_rec.attribute7 IS NULL THEN
1428 x_complete_rec.attribute7 := l_offer_adj_rec.attribute7;
1429 END IF;
1430
1431 -- attribute8
1432 IF p_offer_adj_rec.attribute8 IS NULL THEN
1433 x_complete_rec.attribute8 := l_offer_adj_rec.attribute8;
1434 END IF;
1435
1436 -- attribute9
1437 IF p_offer_adj_rec.attribute9 IS NULL THEN
1438 x_complete_rec.attribute9 := l_offer_adj_rec.attribute9;
1439 END IF;
1440
1441 -- attribute10
1442 IF p_offer_adj_rec.attribute10 IS NULL THEN
1443 x_complete_rec.attribute10 := l_offer_adj_rec.attribute10;
1444 END IF;
1445
1446 -- attribute11
1447 IF p_offer_adj_rec.attribute11 IS NULL THEN
1448 x_complete_rec.attribute11 := l_offer_adj_rec.attribute11;
1449 END IF;
1450
1451 -- attribute12
1452 IF p_offer_adj_rec.attribute12 IS NULL THEN
1453 x_complete_rec.attribute12 := l_offer_adj_rec.attribute12;
1457 IF p_offer_adj_rec.attribute13 IS NULL THEN
1454 END IF;
1455
1456 -- attribute13
1458 x_complete_rec.attribute13 := l_offer_adj_rec.attribute13;
1459 END IF;
1460
1461 -- attribute14
1462 IF p_offer_adj_rec.attribute14 IS NULL THEN
1463 x_complete_rec.attribute14 := l_offer_adj_rec.attribute14;
1464 END IF;
1465
1466 -- attribute15
1467 IF p_offer_adj_rec.attribute15 IS NULL THEN
1468 x_complete_rec.attribute15 := l_offer_adj_rec.attribute15;
1469 END IF;
1470 -- Note: Developers need to modify the procedure
1471 -- to handle any business specific requirements.
1472 END Complete_Offer_Adj_Rec;
1473
1474
1475
1476
1477 PROCEDURE Default_Offer_Adj_Items ( p_offer_adj_rec IN offer_adj_rec_type ,
1478 x_offer_adj_rec OUT NOCOPY offer_adj_rec_type )
1479 IS
1480 l_offer_adj_rec offer_adj_rec_type := p_offer_adj_rec;
1481 BEGIN
1482 -- Developers should put their code to default the record type
1483 -- e.g. IF p_campaign_rec.status_code IS NULL
1484 -- OR p_campaign_rec.status_code = FND_API.G_MISS_CHAR THEN
1485 -- l_campaign_rec.status_code := 'NEW' ;
1486 -- END IF ;
1487 --
1488 NULL ;
1489 END;
1490
1491
1492
1493
1494 PROCEDURE Validate_Offer_Adjustment(
1495 p_api_version_number IN NUMBER,
1496 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1497 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1498 p_offer_adj_rec IN offer_adj_rec_type,
1499 p_validation_mode IN VARCHAR2,
1500 x_return_status OUT NOCOPY VARCHAR2,
1501 x_msg_count OUT NOCOPY NUMBER,
1502 x_msg_data OUT NOCOPY VARCHAR2
1503 )
1504 IS
1505 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Offer_Adjustment';
1506 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1507 l_object_version_number NUMBER;
1508 l_offer_adj_rec offer_adj_rec_type;
1509
1510 BEGIN
1511 -- Standard Start of API savepoint
1512 SAVEPOINT validate_offer_adjustment_;
1513
1514 -- Standard call to check for call compatibility.
1515 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1516 p_api_version_number,
1517 l_api_name,
1518 G_PKG_NAME)
1519 THEN
1520 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1521 END IF;
1522
1523
1524 -- Initialize message list if p_init_msg_list is set to TRUE.
1525 IF FND_API.to_Boolean( p_init_msg_list )
1526 THEN
1527 FND_MSG_PUB.initialize;
1528 END IF;
1529
1530
1531 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1532 Check_offer_adj_Items(
1533 p_offer_adj_rec => p_offer_adj_rec,
1534 p_validation_mode => p_validation_mode,
1535 x_return_status => x_return_status
1536 );
1537
1538 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1539 RAISE FND_API.G_EXC_ERROR;
1540 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1541 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1542 END IF;
1543 END IF;
1544
1545 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1546 Default_Offer_Adj_Items (p_offer_adj_rec => p_offer_adj_rec ,
1547 x_offer_adj_rec => l_offer_adj_rec) ;
1548 END IF ;
1549
1550
1551 Complete_offer_adj_Rec(
1552 p_offer_adj_rec => p_offer_adj_rec,
1553 x_complete_rec => l_offer_adj_rec
1554 );
1555
1556 -- If the Effective date is before the start date of any of the budgets dont create or update adjustments
1557 IF l_offer_adj_rec.effective_date < get_budget_start_date(l_offer_adj_rec.list_header_id) AND isBudgetOffer(p_listHeaderId => l_offer_adj_rec.list_header_id) = 'N' THEN
1558 ozf_utility_pvt.error_message('OZF_OFFR_ADJ_DT_LT_BUDGET_DT');
1559 RAISE FND_API.G_EXC_ERROR;
1560 END IF;
1561
1562 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1563 Validate_offer_adj_Rec(
1564 p_api_version_number => 1.0,
1568 x_msg_data => x_msg_data,
1565 p_init_msg_list => FND_API.G_FALSE,
1566 x_return_status => x_return_status,
1567 x_msg_count => x_msg_count,
1569 p_offer_adj_rec => l_offer_adj_rec);
1570
1571 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
1572 RAISE FND_API.G_EXC_ERROR;
1573 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1574 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1575 END IF;
1576 END IF;
1577
1578
1579 -- Debug Message
1580 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'start');
1581
1582
1583
1584 -- Initialize API return status to SUCCESS
1585 x_return_status := FND_API.G_RET_STS_SUCCESS;
1586
1587
1588 -- Debug Message
1589 OZF_Offer_Adj_Line_PVT.debug_message('Private API: ' || l_api_name || 'end');
1590
1591
1592 -- Standard call to get message count and if count is 1, get message info.
1593 FND_MSG_PUB.Count_And_Get
1594 (p_count => x_msg_count,
1595 p_data => x_msg_data
1596 );
1597 EXCEPTION
1598
1599 WHEN OZF_Utility_PVT.resource_locked THEN
1600 x_return_status := FND_API.g_ret_sts_error;
1601 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
1602
1603 WHEN FND_API.G_EXC_ERROR THEN
1604 ROLLBACK TO VALIDATE_Offer_Adjustment_;
1605 x_return_status := FND_API.G_RET_STS_ERROR;
1606 -- Standard call to get message count and if count=1, get the message
1607 FND_MSG_PUB.Count_And_Get (
1608 p_encoded => FND_API.G_FALSE,
1609 p_count => x_msg_count,
1610 p_data => x_msg_data
1611 );
1612
1613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1614 ROLLBACK TO VALIDATE_Offer_Adjustment_;
1615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1616 -- Standard call to get message count and if count=1, get the message
1617 FND_MSG_PUB.Count_And_Get (
1618 p_encoded => FND_API.G_FALSE,
1619 p_count => x_msg_count,
1620 p_data => x_msg_data
1621 );
1622
1623 WHEN OTHERS THEN
1624 ROLLBACK TO VALIDATE_Offer_Adjustment_;
1625 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1626 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1627 THEN
1628 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1629 END IF;
1630 -- Standard call to get message count and if count=1, get the message
1631 FND_MSG_PUB.Count_And_Get (
1632 p_encoded => FND_API.G_FALSE,
1633 p_count => x_msg_count,
1634 p_data => x_msg_data
1635 );
1636 End Validate_Offer_Adjustment;
1637
1638
1639 PROCEDURE Validate_Offer_Adj_Rec (
1640 p_api_version_number IN NUMBER,
1641 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1642 x_return_status OUT NOCOPY VARCHAR2,
1643 x_msg_count OUT NOCOPY NUMBER,
1644 x_msg_data OUT NOCOPY VARCHAR2,
1645 p_offer_adj_rec IN offer_adj_rec_type
1646 )
1647 IS
1648 BEGIN
1649 -- Initialize message list if p_init_msg_list is set to TRUE.
1650 IF FND_API.to_Boolean( p_init_msg_list )
1651 THEN
1652 FND_MSG_PUB.initialize;
1653 END IF;
1654
1655
1656
1657 -- Initialize API return status to SUCCESS
1658 x_return_status := FND_API.G_RET_STS_SUCCESS;
1659
1660 -- Hint: Validate data
1661 -- If data not valid
1662 -- THEN
1663 -- x_return_status := FND_API.G_RET_STS_ERROR;
1664
1665 -- Debug Message
1666 OZF_Offer_Adj_Line_PVT.debug_message('Private API: Validate_dm_model_rec');
1667 -- Standard call to get message count and if count is 1, get message info.
1668 FND_MSG_PUB.Count_And_Get
1669 (p_count => x_msg_count,
1670 p_data => x_msg_data
1671 );
1672 END Validate_offer_adj_Rec;
1673
1674 END OZF_Offer_Adjustment_PVT;