[Home] [Help]
PACKAGE BODY: APPS.AMS_PS_RULE_PVT
Source
1 PACKAGE BODY AMS_Ps_Rule_PVT as
2 /* $Header: amsvrulb.pls 115.20 2003/01/27 10:20:17 sikalyan ship $ */
3 -- ===============================================================
4 -- Start of Comments
5 -- Package name
6 -- AMS_Ps_Rule_PVT
7 -- Purpose
8 --
9 -- History
10 --
11 -- NOTE
12 --
13 -- End of Comments
14 -- ===============================================================
15
16 G_PKG_NAME CONSTANT VARCHAR2(30):= 'AMS_Ps_Rule_PVT';
17 G_FILE_NAME CONSTANT VARCHAR2(12) := 'amsvrulb.pls';
18
19 -- Hint: Primary key needs to be returned.
20
21 AMS_DEBUG_HIGH_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
22 AMS_DEBUG_LOW_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
23 AMS_DEBUG_MEDIUM_ON constant boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM);
24
25 PROCEDURE Create_Ps_Rule(
26 p_api_version_number IN NUMBER,
27 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
28 p_commit IN VARCHAR2 := FND_API.G_FALSE,
29 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
30
31 x_return_status OUT NOCOPY VARCHAR2,
32 x_msg_count OUT NOCOPY NUMBER,
33 x_msg_data OUT NOCOPY VARCHAR2,
34
35 p_ps_rules_rec IN ps_rules_rec_type := g_miss_ps_rules_rec,
36 p_visitor_rec IN visitor_type_rec := NULL,
37
38 x_rule_id OUT NOCOPY NUMBER
39
40 )
41
42 IS
43
44 L_API_NAME CONSTANT VARCHAR2(30) := 'Create_Ps_Rule';
45 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
46
47 TYPE num_tab is TABLE OF NUMBER INDEX BY BINARY_INTEGER;
48 TYPE vt_tab is TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
49
50 l_return_status_full VARCHAR2(1);
51 l_object_version_number NUMBER := 1;
52 --l_org_id NUMBER := FND_API.G_MISS_NUM;
53 l_org_id NUMBER;
54 l_RULE_ID NUMBER;
55 l_dummy NUMBER;
56 l_ii NUMBER;
57 l_num num_tab;
58 l_vt vt_tab;
59
60 l_strat_type VARCHAR2(30);
61 l_exec_priority NUMBER;
62 l_strategy_id NUMBER;
63 l_content_type VARCHAR2(30);
64 l_posting_id NUMBER;
65 l_rulegroup_id NUMBER;
66
67 -- patch Begin 2225359
68
69 l_no_of_records NUMBER;
70
71 --patch end 2225359
72
73 CURSOR c_id IS
74 SELECT AMS_IBA_PS_RULES_s.NEXTVAL
75 FROM dual;
76
77 CURSOR c_id_exists (l_id IN NUMBER) IS
78 SELECT 1
79 FROM AMS_IBA_PS_RULES
80 WHERE RULE_ID = l_id;
81
82 CURSOR c_strategy_id_exists(l_p_id IN NUMBER,l_r_id IN NUMBER) IS
83 SELECT DISTINCT STRATEGY_ID
84 FROM AMS_IBA_PS_RULES
85 WHERE (POSTING_ID = l_p_id AND RULEGROUP_ID = l_r_id);
86
87 BEGIN
88 -- Standard Start of API savepoint
89
90 SAVEPOINT CREATE_Ps_Rule_PVT;
91
92 -- Standard call to check for call compatibility.
93
94 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
95 p_api_version_number,
96 l_api_name,
97 G_PKG_NAME)
98
99 THEN
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102
103 -- Initialize message list if p_init_msg_list is set to TRUE.
104
105
106 IF FND_API.to_Boolean( p_init_msg_list )
107
108 THEN
109 FND_MSG_PUB.initialize;
110 END IF;
111
112
113 -- Debug Message
114
115
116 IF (AMS_DEBUG_HIGH_ON) THEN
117
118
119
120
121
122 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
123
124
125 END IF;
126
127
128 -- Initialize API return status to SUCCESS
129
130 x_return_status := FND_API.G_RET_STS_SUCCESS;
131
132 -- Local variable initialization
133
134 /*
135 IF p_ps_rules_rec.RULE_ID IS NULL OR p_ps_rules_rec.RULE_ID = FND_API.g_miss_num THEN
136 LOOP
137 l_dummy := NULL;
138 OPEN c_id;
139 FETCH c_id INTO l_RULE_ID;
140 CLOSE c_id;
141
142 OPEN c_id_exists(l_RULE_ID);
143 FETCH c_id_exists INTO l_dummy;
144 CLOSE c_id_exists;
145 EXIT WHEN l_dummy IS NULL;
146 END LOOP;
147 END IF;
148 */
149
150 -- ========================================================
151 -- Validate Environment
152 -- ========================================================
153
154 IF FND_GLOBAL.User_Id IS NULL
155
156 THEN
157
158 AMS_Utility_PVT.Error_Message(p_message_name => 'USER_PROFILE_MISSING');
159 RAISE FND_API.G_EXC_ERROR;
160 END IF;
161
162 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
163 THEN
164 -- Debug message
165 IF (AMS_DEBUG_HIGH_ON) THEN
166
167 AMS_UTILITY_PVT.debug_message('Private API: Validate_Ps_Rule');
168 END IF;
169
170 -- Invoke validation procedures
171
172 Validate_ps_rule(
173
174 p_api_version_number => 1.0,
175 p_init_msg_list => FND_API.G_FALSE,
176 p_validation_level => p_validation_level,
177 p_ps_rules_rec => p_ps_rules_rec,
178 x_return_status => x_return_status,
179 x_msg_count => x_msg_count,
180 x_msg_data => x_msg_data);
181
182 END IF;
183
184 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 -- patch Begin Bug 2225359
189
190 IF (AMS_DEBUG_HIGH_ON) THEN
191
192
193
194 AMS_UTILITY_PVT.debug_message('posting_id, rulegroup_id :' || p_ps_rules_rec.posting_id||' '||p_ps_rules_rec.rulegroup_id);
195
196 END IF;
197 IF (AMS_DEBUG_HIGH_ON) THEN
198
199 AMS_UTILITY_PVT.debug_message('CLAUSEVALUE2,CLAUSEVALUE3 :' || p_ps_rules_rec.CLAUSEVALUE2||' '||p_ps_rules_rec.CLAUSEVALUE3 );
200 END IF;
201
202 SELECT COUNT(1) into l_no_of_records FROM AMS_IBA_PS_RULES ps_rule
203 WHERE (ps_rule.posting_id = p_ps_rules_rec.posting_id AND ps_rule.rulegroup_id = p_ps_rules_rec.rulegroup_id
204 AND ps_rule.CLAUSEVALUE2 = p_ps_rules_rec.CLAUSEVALUE2 AND ps_rule.CLAUSEVALUE3 = p_ps_rules_rec.CLAUSEVALUE3 );
205
206 IF l_no_of_records > 0 THEN
207 RETURN;
208 END IF;
209
210
211 -- patch end 2225359
212
213 -- Debug Message
214 IF (AMS_DEBUG_HIGH_ON) THEN
215
216 AMS_UTILITY_PVT.debug_message('Private API: Calling create table handler');
217 END IF;
218
219 -- update strategy_id and exec_priority for new Segment and List rows
220
221 -- returns only one row because posting_id and rulegroup_id form unique key
222
223 IF (AMS_DEBUG_HIGH_ON) THEN
224
225
226
227 AMS_UTILITY_PVT.debug_message('posting_id, rulegroup_id :'|| p_ps_rules_rec.posting_id||' '||p_ps_rules_rec.rulegroup_id);
228
229 END IF;
230
231
232 SELECT strategy_type, exec_priority INTO l_strat_type, l_exec_priority
233 FROM ams_iba_ps_rulegrps_b
234 WHERE posting_id = p_ps_rules_rec.posting_id AND rulegroup_id = p_ps_rules_rec.rulegroup_id;
235
236 -- returns only one row because posting_id is the primary key
237
238 Select content_type into l_content_type from ams_iba_ps_postings_b
239 where posting_id = p_ps_rules_rec.posting_id;
240
241
242 IF l_content_type = 'PRODUCT' THEN
243
244 IF l_strat_type = 'PRODUCT_RELATIONSHIP' THEN
245 l_strategy_id := 1;
246 elsif l_strat_type = 'INFERRED_OP' then
247 l_strategy_id := 4;
248 elsif l_strat_type = 'MANUAL_SELECTION' then
249 l_strategy_id := 7;
250 END IF;
251
252 elsif l_content_type = 'OFFER' THEN
253
254 IF l_strat_type = 'PRODUCT_RELATIONSHIP' THEN
255 l_strategy_id := 2;
256 elsif l_strat_type = 'INFERRED_OP' then
257 l_strategy_id := 5;
258 elsif l_strat_type = 'MANUAL_SELECTION' then
259 l_strategy_id := 9;
260 END IF;
261
262 elsif l_content_type = 'SCHEDULE' THEN
263
264 IF l_strat_type = 'PRODUCT_RELATIONSHIP' THEN
265 l_strategy_id := 3;
266 elsif l_strat_type = 'INFERRED_OP' then
267 l_strategy_id := 6;
268 elsif l_strat_type = 'MANUAL_SELECTION' then
269 l_strategy_id := 8;
270 END IF;
271
272 elsif ((l_content_type = 'SCHEDULE' OR l_content_type = 'PRODUCT'
273 OR l_content_type = 'OFFER') AND l_strat_type = 'CUSTOM' ) THEN
274
275 l_strategy_id := p_ps_rules_rec.strategy_id;
276
277 END IF;
278
279 -- Fix for Custom Strategy
280
281 BEGIN
282 IF l_strategy_id IS NULL THEN
283 IF l_strat_type = 'CUSTOM' THEN
284 l_rulegroup_id := p_ps_rules_rec.rulegroup_id;
285 l_posting_id := p_ps_rules_rec.posting_id;
286 OPEN c_strategy_id_exists(l_posting_id,l_rulegroup_id);
287 LOOP
288 FETCH c_strategy_id_exists INTO l_strategy_id;
289 EXIT WHEN c_strategy_id_exists%NOTFOUND;
290 IF l_strategy_id IS NULL THEN
291 EXIT;
292 END IF;
293 END LOOP;
294 CLOSE c_strategy_id_exists;
295 END IF;
296 END IF;
297
298 EXCEPTION
299 WHEN OTHERS THEN
300 l_strategy_id := p_ps_rules_rec.strategy_id;
301
302 END;
303
304 -- End Fix
305
306 IF (p_visitor_rec.anon is null AND
307 p_visitor_rec.rgoh is null AND
308 p_visitor_rec.rgnoh is null)
309 THEN
310
311 -- Invoke table handler(AMS_IBA_PS_RULES_PKG.Insert_Row)
312
313 IF p_ps_rules_rec.RULE_ID IS NULL OR p_ps_rules_rec.RULE_ID = FND_API.g_miss_num THEN
314
315 LOOP
316 l_dummy := NULL;
317 OPEN c_id;
318 FETCH c_id INTO l_RULE_ID;
319 CLOSE c_id;
320
321 OPEN c_id_exists(l_RULE_ID);
322 FETCH c_id_exists INTO l_dummy;
323 CLOSE c_id_exists;
324 EXIT WHEN l_dummy IS NULL;
325 END LOOP;
326 END IF;
327
328
329 AMS_IBA_PS_RULES_PKG.Insert_Row(
330
331 p_created_by => FND_GLOBAL.USER_ID,
332 p_creation_date => SYSDATE,
333 p_last_updated_by => FND_GLOBAL.USER_ID,
334 p_last_update_date => SYSDATE,
335 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
336 px_object_version_number => l_object_version_number,
337 px_rule_id => l_RULE_ID,
338 p_rulegroup_id => p_ps_rules_rec.rulegroup_id,
339 p_posting_id => p_ps_rules_rec.posting_id,
340 -- p_strategy_id => p_ps_rules_rec.strategy_id,
341 p_strategy_id => l_strategy_id,
342 p_exec_priority => l_exec_priority,
343 -- p_exec_priority => p_ps_rules_rec.exec_priority,
344 p_bus_priority_code => p_ps_rules_rec.bus_priority_code,
345 p_bus_priority_disp_order => p_ps_rules_rec.bus_priority_disp_order,
346 p_clausevalue1 => p_ps_rules_rec.clausevalue1,
347 p_clausevalue2 => p_ps_rules_rec.clausevalue2,
348 p_clausevalue3 => p_ps_rules_rec.clausevalue3,
349 p_clausevalue4 => p_ps_rules_rec.clausevalue4,
350 p_clausevalue5 => p_ps_rules_rec.clausevalue5,
351 p_clausevalue6 => p_ps_rules_rec.clausevalue6,
352 p_clausevalue7 => p_ps_rules_rec.clausevalue7,
353 p_clausevalue8 => p_ps_rules_rec.clausevalue8,
354 p_clausevalue9 => p_ps_rules_rec.clausevalue9,
355 p_clausevalue10 => p_ps_rules_rec.clausevalue10,
356 p_use_clause6 => p_ps_rules_rec.use_clause6,
357 p_use_clause7 => p_ps_rules_rec.use_clause7,
358 p_use_clause8 => p_ps_rules_rec.use_clause8,
359 p_use_clause9 => p_ps_rules_rec.use_clause9,
360 p_use_clause10 => p_ps_rules_rec.use_clause10);
361
362
363 ELSE
364
365 l_num(1) := 0;
366 l_num(2) := 0;
367 l_num(3) := 0;
368
369 IF p_visitor_rec.anon THEN l_num(1) := 1; END IF;
370 IF p_visitor_rec.rgoh THEN l_num(2) := 1; END IF;
371 IF p_visitor_rec.rgnoh THEN l_num(3) := 1; END IF;
372
373 l_vt(1) := 'ANON';
374 l_vt(2) := 'RGOH';
375 l_vt(3) := 'RGNOH';
376
377 FOR l_ii IN 1..3 LOOP
378 IF l_num(l_ii) = 1 THEN
379
380 IF p_ps_rules_rec.RULE_ID IS NULL OR p_ps_rules_rec.RULE_ID = FND_API.g_miss_num THEN
381 LOOP
382 l_dummy := NULL;
383 OPEN c_id;
384 FETCH c_id INTO l_RULE_ID;
385 CLOSE c_id;
386
387 OPEN c_id_exists(l_RULE_ID);
388 FETCH c_id_exists INTO l_dummy;
389 CLOSE c_id_exists;
390 EXIT WHEN l_dummy IS NULL;
391 END LOOP;
392 END IF;
393
394 -- Invoke table handler(AMS_IBA_PS_RULES_PKG.Insert_Row)
395
396 AMS_IBA_PS_RULES_PKG.Insert_Row(
397
398 p_created_by => FND_GLOBAL.USER_ID,
399 p_creation_date => SYSDATE,
403 px_object_version_number => l_object_version_number,
400 p_last_updated_by => FND_GLOBAL.USER_ID,
401 p_last_update_date => SYSDATE,
402 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
404 px_rule_id => l_RULE_ID,
405 p_rulegroup_id => p_ps_rules_rec.rulegroup_id,
406 p_posting_id => p_ps_rules_rec.posting_id,
407 -- p_strategy_id => p_ps_rules_rec.strategy_id,
408 -- p_exec_priority => p_ps_rules_rec.exec_priority,
409 p_strategy_id => l_strategy_id,
410 p_exec_priority => l_exec_priority,
411
412 p_bus_priority_code => p_ps_rules_rec.bus_priority_code,
413 p_bus_priority_disp_order => p_ps_rules_rec.bus_priority_disp_order,
414 -- p_clausevalue1 => p_ps_rules_rec.clausevalue1,
415 p_clausevalue1 => l_vt(l_ii),
416 p_clausevalue2 => p_ps_rules_rec.clausevalue2,
417 p_clausevalue3 => p_ps_rules_rec.clausevalue3,
418 p_clausevalue4 => p_ps_rules_rec.clausevalue4,
419 p_clausevalue5 => p_ps_rules_rec.clausevalue5,
420 p_clausevalue6 => p_ps_rules_rec.clausevalue6,
421 p_clausevalue7 => p_ps_rules_rec.clausevalue7,
422 p_clausevalue8 => p_ps_rules_rec.clausevalue8,
423 p_clausevalue9 => p_ps_rules_rec.clausevalue9,
424 p_clausevalue10 => p_ps_rules_rec.clausevalue10,
425 p_use_clause6 => p_ps_rules_rec.use_clause6,
426 p_use_clause7 => p_ps_rules_rec.use_clause7,
427 p_use_clause8 => p_ps_rules_rec.use_clause8,
428 p_use_clause9 => p_ps_rules_rec.use_clause9,
429 p_use_clause10 => p_ps_rules_rec.use_clause10);
430
431 END IF;
432 END LOOP;
433 END IF;
434
435
436 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
437 RAISE FND_API.G_EXC_ERROR;
438 END IF;
439 --
440 -- End of API body
441 --
442
443
444 -- Standard check for p_commit
445
446 IF FND_API.to_Boolean( p_commit )
447
448 THEN
449 COMMIT WORK;
450 END IF;
451
452
453 x_rule_id := l_RULE_ID;
454
455 -- Debug Message
456 IF (AMS_DEBUG_HIGH_ON) THEN
457
458 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
459 END IF;
460
461 -- Standard call to get message count and if count is 1, get message info.
462 FND_MSG_PUB.Count_And_Get
463 (p_count => x_msg_count,
464 p_data => x_msg_data
465 );
466
467
468 EXCEPTION
469
470 WHEN AMS_Utility_PVT.resource_locked THEN
471 x_return_status := FND_API.g_ret_sts_error;
472 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
473
474 WHEN FND_API.G_EXC_ERROR THEN
475 ROLLBACK TO CREATE_Ps_Rule_PVT;
476 x_return_status := FND_API.G_RET_STS_ERROR;
477 -- Standard call to get message count and if count=1, get the message
478 FND_MSG_PUB.Count_And_Get (
479 p_encoded => FND_API.G_FALSE,
480 p_count => x_msg_count,
481 p_data => x_msg_data
482 );
483
484 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
485 ROLLBACK TO CREATE_Ps_Rule_PVT;
486 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
487 -- Standard call to get message count and if count=1, get the message
488 FND_MSG_PUB.Count_And_Get (
489 p_encoded => FND_API.G_FALSE,
490 p_count => x_msg_count,
491 p_data => x_msg_data
492 );
493
494 WHEN OTHERS THEN
495 ROLLBACK TO CREATE_Ps_Rule_PVT;
496 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
497 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
498 THEN
499 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
500 END IF;
501 -- Standard call to get message count and if count=1, get the message
502 FND_MSG_PUB.Count_And_Get (
503 p_encoded => FND_API.G_FALSE,
504 p_count => x_msg_count,
505 p_data => x_msg_data
506 );
507
508 End Create_Ps_Rule;
509
510
511
512 PROCEDURE Update_Ps_Rule(
513 p_api_version_number IN NUMBER,
514 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
515 p_commit IN VARCHAR2 := FND_API.G_FALSE,
516 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
517
518 x_return_status OUT NOCOPY VARCHAR2,
519 x_msg_count OUT NOCOPY NUMBER,
520 x_msg_data OUT NOCOPY VARCHAR2,
521
522 p_ps_rules_rec IN ps_rules_rec_type,
523 p_visitor_rec IN visitor_type_rec,
524 p_ps_filter_tbl IN ps_rules_tuple_tbl_type,
525 p_ps_strategy_tbl IN ps_rules_tuple_tbl_type,
526
527 x_object_version_number OUT NOCOPY NUMBER
528 )
529
530 IS
531 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Ps_Rule';
532 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
533 -- Local Variables
534 l_object_version NUMBER;
535 l_RULE_ID NUMBER;
539 FROM AMS_IBA_PS_RULES
536
537 CURSOR c_object_version(rgp_id IN NUMBER) IS
538 SELECT object_version_number
540 WHERE rulegroup_id = rgp_id
541 and rownum <= 1;
542
543 BEGIN
544 -- Standard Start of API savepoint
545 SAVEPOINT UPDATE_Ps_Rule_PVT;
546
547 -- Standard call to check for call compatibility.
548 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
549 p_api_version_number,
550 l_api_name,
551 G_PKG_NAME)
552 THEN
553 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
554 END IF;
555
556 -- Initialize message list if p_init_msg_list is set to TRUE.
557 IF FND_API.to_Boolean( p_init_msg_list )
558 THEN
559 FND_MSG_PUB.initialize;
560 END IF;
561
562 -- Debug Message
563 IF (AMS_DEBUG_HIGH_ON) THEN
564
565 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
566 END IF;
567
568 -- Initialize API return status to SUCCESS
569 x_return_status := FND_API.G_RET_STS_SUCCESS;
570
571 -- Debug Message
572 IF (AMS_DEBUG_HIGH_ON) THEN
573
574 AMS_UTILITY_PVT.debug_message('Private API: - Open Cursor to Select');
575 END IF;
576
577 OPEN c_object_version(p_ps_rules_rec.rulegroup_id);
578
579 FETCH c_object_version INTO l_object_version;
580
581 If ( c_object_version%NOTFOUND) THEN
582 AMS_Utility_PVT.Error_Message(p_message_name => 'API_MISSING_UPDATE_TARGET',
583 p_token_name => 'INFO',
584 p_token_value => 'Rule');
585 RAISE FND_API.G_EXC_ERROR;
586 END IF;
587 -- Debug Message
588
589 IF (AMS_DEBUG_HIGH_ON) THEN
590
591
592
593 AMS_UTILITY_PVT.debug_message('Private API: - Close Cursor');
594
595 END IF;
596 CLOSE c_object_version;
597
598 IF (p_ps_rules_rec.object_version_number is NULL or
599 p_ps_rules_rec.object_version_number = FND_API.G_MISS_NUM ) THEN
600 AMS_Utility_PVT.Error_Message(p_message_name => 'API_VERSION_MISSING',
601 p_token_name => 'COLUMN',
602 p_token_value => 'object_version_number') ;
603 raise FND_API.G_EXC_ERROR;
604 END IF;
605
606 -- Check Whether record has been changed by someone else
607 IF (p_ps_rules_rec.object_version_number <> l_object_version) THEN
608 AMS_Utility_PVT.Error_Message(p_message_name => 'API_RECORD_CHANGED',
609 p_token_name => 'INFO',
610 p_token_value => 'Rule') ;
611 raise FND_API.G_EXC_ERROR;
612 END IF;
613
614 IF ( P_validation_level >= FND_API.G_VALID_LEVEL_FULL)
615 THEN
616 -- Debug message
617 IF (AMS_DEBUG_HIGH_ON) THEN
618
619 AMS_UTILITY_PVT.debug_message('Private API: Validate_Ps_Rule');
620 END IF;
621
622 -- Invoke validation procedures
623 Validate_ps_rule(
624 p_api_version_number => 1.0,
625 p_init_msg_list => FND_API.G_FALSE,
626 p_validation_level => p_validation_level,
627 p_ps_rules_rec => p_ps_rules_rec,
628 x_return_status => x_return_status,
629 x_msg_count => x_msg_count,
630 x_msg_data => x_msg_data);
631 END IF;
632
633 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
634 RAISE FND_API.G_EXC_ERROR;
635 END IF;
636
637
638 update_filters(p_ps_rules_rec.rulegroup_id, p_ps_filter_tbl, x_return_status);
639
640 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
641 RAISE FND_API.G_EXC_ERROR;
642 END IF;
643
644 update_strategy_params(p_ps_rules_rec.rulegroup_id, p_ps_strategy_tbl, x_return_status);
645
646 IF x_return_status<>FND_API.G_RET_STS_SUCCESS THEN
647 RAISE FND_API.G_EXC_ERROR;
648 END IF;
649
650 -- Debug Message
651 IF (AMS_DEBUG_HIGH_ON) THEN
652
653 AMS_UTILITY_PVT.debug_message('Private API: Calling update ');
654 END IF;
655
656 Update AMS_IBA_PS_RULES
657 SET
658 last_updated_by = FND_GLOBAL.user_id,
659 last_update_date = SYSDATE,
660 last_update_login = FND_GLOBAL.conc_login_id,
661 object_version_number = p_ps_rules_rec.object_version_number+1,
662 exec_priority = DECODE(p_ps_rules_rec.exec_priority,FND_API.g_miss_num,exec_priority,p_ps_rules_rec.exec_priority),--added this line to update priority of a rule also:anchaudh 2003/01/27.
663 strategy_id = DECODE( p_ps_rules_rec.strategy_id, FND_API.g_miss_num,strategy_id, p_ps_rules_rec.strategy_id),
664 bus_priority_code = DECODE( p_ps_rules_rec.bus_priority_code, FND_API.g_miss_char, bus_priority_code, p_ps_rules_rec.bus_priority_code),
665 bus_priority_disp_order = DECODE( p_ps_rules_rec.bus_priority_disp_order, FND_API.g_miss_char, bus_priority_disp_order, p_ps_rules_rec.bus_priority_disp_order),
666 clausevalue1 = DECODE( p_ps_rules_rec.clausevalue1, FND_API.g_miss_char, clausevalue1, p_ps_rules_rec.clausevalue1),
670 clausevalue5 = DECODE( p_ps_rules_rec.clausevalue5, FND_API.g_miss_num, clausevalue5, p_ps_rules_rec.clausevalue5),
667 clausevalue2 = DECODE( p_ps_rules_rec.clausevalue2, FND_API.g_miss_num, clausevalue2, p_ps_rules_rec.clausevalue2),
668 clausevalue3 = DECODE( p_ps_rules_rec.clausevalue3, FND_API.g_miss_char, clausevalue3, p_ps_rules_rec.clausevalue3),
669 clausevalue4 = DECODE( p_ps_rules_rec.clausevalue4, FND_API.g_miss_char, clausevalue4, p_ps_rules_rec.clausevalue4),
671 clausevalue6 = DECODE( p_ps_rules_rec.clausevalue6, FND_API.g_miss_char, clausevalue6, p_ps_rules_rec.clausevalue6),
672 clausevalue7 = DECODE( p_ps_rules_rec.clausevalue7, FND_API.g_miss_char, clausevalue7, p_ps_rules_rec.clausevalue7),
673 clausevalue8 = DECODE( p_ps_rules_rec.clausevalue8, FND_API.g_miss_char, clausevalue8, p_ps_rules_rec.clausevalue8),
674 clausevalue9 = DECODE( p_ps_rules_rec.clausevalue9, FND_API.g_miss_char, clausevalue9, p_ps_rules_rec.clausevalue9),
675 clausevalue10 = DECODE(p_ps_rules_rec.clausevalue10, FND_API.g_miss_char, clausevalue10, p_ps_rules_rec.clausevalue10),
676 use_clause6 = DECODE( p_ps_rules_rec.use_clause6, FND_API.g_miss_char, use_clause6, p_ps_rules_rec.use_clause6),
677 use_clause7 = DECODE( p_ps_rules_rec.use_clause7, FND_API.g_miss_char, use_clause7, p_ps_rules_rec.use_clause7),
678 use_clause8 = DECODE( p_ps_rules_rec.use_clause8, FND_API.g_miss_char, use_clause8, p_ps_rules_rec.use_clause8),
679 use_clause9 = DECODE( p_ps_rules_rec.use_clause9, FND_API.g_miss_char, use_clause9, p_ps_rules_rec.use_clause9),
680 use_clause10 = DECODE( p_ps_rules_rec.use_clause10, FND_API.g_miss_char, use_clause10, p_ps_rules_rec.use_clause10)
681
682 WHERE RULEGROUP_ID = p_ps_rules_rec.RULEGROUP_ID;
683
684 IF (SQL%NOTFOUND) THEN
685 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
686 END IF;
687
688 --
689 -- End of API body.
690 --
691
692 -- Standard check for p_commit
693 IF FND_API.to_Boolean( p_commit )
694 THEN
695 COMMIT WORK;
696 END IF;
697
698 -- Debug Message
699 IF (AMS_DEBUG_HIGH_ON) THEN
700
701 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
702 END IF;
703
704 -- Standard call to get message count and if count is 1, get message info.
705 FND_MSG_PUB.Count_And_Get
706 (p_count => x_msg_count,
707 p_data => x_msg_data
708 );
709 EXCEPTION
710
711 WHEN AMS_Utility_PVT.resource_locked THEN
712 x_return_status := FND_API.g_ret_sts_error;
713 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
714
715 WHEN FND_API.G_EXC_ERROR THEN
716 ROLLBACK TO UPDATE_Ps_Rule_PVT;
717 x_return_status := FND_API.G_RET_STS_ERROR;
718 -- Standard call to get message count and if count=1, get the message
719 FND_MSG_PUB.Count_And_Get (
720 p_encoded => FND_API.G_FALSE,
721 p_count => x_msg_count,
722 p_data => x_msg_data
723 );
724
725 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
726 ROLLBACK TO UPDATE_Ps_Rule_PVT;
727 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
728 -- Standard call to get message count and if count=1, get the message
729 FND_MSG_PUB.Count_And_Get (
730 p_encoded => FND_API.G_FALSE,
731 p_count => x_msg_count,
732 p_data => x_msg_data
733 );
734
735 WHEN OTHERS THEN
736 ROLLBACK TO UPDATE_Ps_Rule_PVT;
737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
738 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
739 THEN
740 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
741 END IF;
742 -- Standard call to get message count and if count=1, get the message
743 FND_MSG_PUB.Count_And_Get (
744 p_encoded => FND_API.G_FALSE,
745 p_count => x_msg_count,
746 p_data => x_msg_data
747 );
748
749 End Update_Ps_Rule;
750
751
752
753 /*
754 The Procedure Update_Ps_Rule_Alt is called only in the PsRuleEO.
755 It is only called when there is a visitor type and/or
756 clauses CL4 - CL10 change.
757 */
758
759
760 PROCEDURE Update_Ps_Rule_Alt(
761 p_api_version_number IN NUMBER,
762 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
763 p_commit IN VARCHAR2 := FND_API.G_FALSE,
764 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
765
766 x_return_status OUT NOCOPY VARCHAR2,
767 x_msg_count OUT NOCOPY NUMBER,
768 x_msg_data OUT NOCOPY VARCHAR2,
769
770 p_ps_rules_rec IN ps_rules_rec_type,
771 p_visitor_rec IN visitor_type_rec,
772 p_ps_filter_tbl IN ps_rules_tuple_tbl_type,
773 p_ps_strategy_tbl IN ps_rules_tuple_tbl_type,
774 p_vistype_change IN BOOLEAN,
775 p_rem_change IN BOOLEAN,
776
777 x_object_version_number OUT NOCOPY NUMBER
778 )
779
780 IS
781
782 L_API_NAME CONSTANT VARCHAR2(30) := 'Update_Ps_Rule_Alt';
783 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
784 l_object_version_number NUMBER;
785 l_RULE_ID NUMBER;
789 TYPE Str_tab IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;
786 l_dummy NUMBER;
787
788 TYPE Num_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
790
791 /*
792 l_rgid Num_tab;
793 l_pstng_id Num_tab;
794 l_strat_id Num_tab;
795 l_ex_pty Num_tab;
796 */
797 l_cl2 Num_tab;
798 /*
799 l_bus_pc Str_tab;
800 l_bus_do Str_tab;
801 */
802 l_cl3 Str_tab;
803
804 l_num Num_tab;
805 l_vt Str_tab;
806
807 l_vtCount NUMBER := 0;
808 i NUMBER;
809 j NUMBER;
810
811 CURSOR c_id IS
812 SELECT AMS_IBA_PS_RULES_s.NEXTVAL FROM dual;
813
814 CURSOR c_id_exists (l_id IN NUMBER) IS
815 SELECT 1
816 FROM AMS_IBA_PS_RULES
817 WHERE RULE_ID = l_id;
818
819 BEGIN
820 /*
821 oe_debug_pub.initialize;
822
823 dbms_output.put_line(oe_debug_pub.set_debug_mode('FILE'));
824
825 oe_debug_pub.debug_on;
826
827 oe_debug_pub.add('Begining of Update_Ps_Rule_Alt');
828 */
829 -- Standard Start of API savepoint
830 SAVEPOINT UPDATE_Ps_Rule_PVT;
831
832 -- Standard call to check for call compatibility.
833 IF NOT FND_API.Compatible_API_Call (l_api_version_number,
834 p_api_version_number,
835 l_api_name,
836 G_PKG_NAME)
837 THEN
838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
839 END IF;
840
841 -- Initialize message list if p_init_msg_list is set to TRUE.
842 IF FND_API.to_Boolean( p_init_msg_list )
843 THEN
844 FND_MSG_PUB.initialize;
845 END IF;
846
847 -- Debug Message
848 IF (AMS_DEBUG_HIGH_ON) THEN
849
850 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
851 END IF;
852
853 -- Initialize API return status to SUCCESS
854 x_return_status := FND_API.G_RET_STS_SUCCESS;
855
856 IF p_vistype_change THEN
857 -- oe_debug_pub.add('Vistor type change condition');
858
859 l_num(1) := 0;
860 l_num(2) := 0;
861 l_num(3) := 0;
862
863 -- Calculate the # of new visitor types
864
865 IF p_visitor_rec.anon THEN
866 l_vtCount := l_vtCount + 1;
867 l_num(1) := 1;
868 END IF;
869
870 IF p_visitor_rec.rgoh THEN
871 l_vtCount := l_vtCount + 1;
872 l_num(2) := 1;
873 END IF;
874
875 IF p_visitor_rec.rgnoh THEN
876 l_vtCount := l_vtCount + 1;
877 l_num(3) := 1;
878 END IF;
879
880 -- IF p_visitor_rec.anon THEN l_num(1) := 1; END IF;
881 -- IF p_visitor_rec.rgoh THEN l_num(2) := 1; END IF;
882 -- IF p_visitor_rec.rgnoh THEN l_num(3) := 1; END IF;
883
884 l_vt(1) := 'ANON';
885 l_vt(2) := 'RGOH';
886 l_vt(3) := 'RGNOH';
887
888 SELECT DISTINCT clausevalue2, clausevalue3
889 -- , rulegroup_id,
890 -- posting_id, strategy_id, exec_priority,
891 -- bus_priority_code, bus_priority_disp_order
892 BULK COLLECT INTO l_cl2, l_cl3 -- l_rgid, l_pstng_id, l_strat_id
893 -- ,l_ex_pty, l_bus_pc, l_bus_do
894 FROM ams_iba_ps_rules
895 WHERE posting_id = p_ps_rules_rec.posting_id
896 AND rulegroup_id = p_ps_rules_rec.rulegroup_id;
897
898 -- END IF; -- REMOVE THIS AFTER UNCOMMENTING BLOCK BELOW
899
900 DELETE FROM ams_iba_ps_rules
901 WHERE posting_id = p_ps_rules_rec.posting_id
902 AND rulegroup_id = p_ps_rules_rec.rulegroup_id
903 AND clausevalue1 IS NOT NULL;
904
905 IF l_vtCount > 0 THEN
906 FOR I in 1..l_cl2.count
907 LOOP
908
909 FOR J in 1..3 -- 3 times for 3 visitor types
910 LOOP
911 IF l_num(j) = 1 THEN
912 -- Generate new rule_id
913 LOOP
914 l_dummy := NULL;
915 OPEN c_id;
916 FETCH c_id INTO l_RULE_ID;
917 CLOSE c_id;
918
919 OPEN c_id_exists(l_RULE_ID);
920 FETCH c_id_exists INTO l_dummy;
921 CLOSE c_id_exists;
922 EXIT WHEN l_dummy IS NULL;
923 END LOOP;
924
925 AMS_IBA_PS_RULES_PKG.Insert_Row(
926 p_created_by => FND_GLOBAL.USER_ID,
927 p_creation_date => SYSDATE,
928 p_last_updated_by => FND_GLOBAL.USER_ID,
929 p_last_update_date => SYSDATE,
930 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
931 px_object_version_number => l_object_version_number,
932 px_rule_id => l_RULE_ID,
933 p_rulegroup_id => p_ps_rules_rec.rulegroup_id,
934 p_posting_id => p_ps_rules_rec.posting_id,
935 p_strategy_id => p_ps_rules_rec.strategy_id,
936 p_exec_priority => p_ps_rules_rec.exec_priority,
937 p_bus_priority_code => p_ps_rules_rec.bus_priority_code,
941 p_clausevalue2 => l_cl2(i),
938 p_bus_priority_disp_order => p_ps_rules_rec.bus_priority_disp_order,
939 -- p_clausevalue1 => p_ps_rules_rec.clausevalue1,
940 p_clausevalue1 => l_vt(j),
942 p_clausevalue3 => l_cl3(i),
943 p_clausevalue4 => p_ps_rules_rec.clausevalue4,
944 p_clausevalue5 => p_ps_rules_rec.clausevalue5,
945 p_clausevalue6 => p_ps_rules_rec.clausevalue6,
946 p_clausevalue7 => p_ps_rules_rec.clausevalue7,
947 p_clausevalue8 => p_ps_rules_rec.clausevalue8,
948 p_clausevalue9 => p_ps_rules_rec.clausevalue9,
949 p_clausevalue10 => p_ps_rules_rec.clausevalue10,
950 p_use_clause6 => p_ps_rules_rec.use_clause6,
951 p_use_clause7 => p_ps_rules_rec.use_clause7,
952 p_use_clause8 => p_ps_rules_rec.use_clause8,
953 p_use_clause9 => p_ps_rules_rec.use_clause9,
954 p_use_clause10 => p_ps_rules_rec.use_clause10);
955
956 END IF;
957 END LOOP; -- l_vtCount loop
958
959 END LOOP; -- outer loop
960 ELSE
961 -- No visitor types - border case
962 FOR I in 1..l_cl2.count
963 LOOP
964 IF l_cl2(i) is not null AND l_cl3(i) is not null THEN
965 -- Generate rule_id
966 LOOP
967 l_dummy := NULL;
968 OPEN c_id;
969 FETCH c_id INTO l_RULE_ID;
970 CLOSE c_id;
971
972 OPEN c_id_exists(l_RULE_ID);
973 FETCH c_id_exists INTO l_dummy;
974 CLOSE c_id_exists;
975 EXIT WHEN l_dummy IS NULL;
976 END LOOP;
977
978 AMS_IBA_PS_RULES_PKG.Insert_Row(
979 p_created_by => FND_GLOBAL.USER_ID,
980 p_creation_date => SYSDATE,
981 p_last_updated_by => FND_GLOBAL.USER_ID,
982 p_last_update_date => SYSDATE,
983 p_last_update_login => FND_GLOBAL.CONC_LOGIN_ID,
984 px_object_version_number => l_object_version_number,
985 px_rule_id => l_RULE_ID,
986 p_rulegroup_id => p_ps_rules_rec.rulegroup_id,
987 p_posting_id => p_ps_rules_rec.posting_id,
988 p_strategy_id => p_ps_rules_rec.strategy_id,
989 p_exec_priority => p_ps_rules_rec.exec_priority,
990 p_bus_priority_code => p_ps_rules_rec.bus_priority_code,
991 p_bus_priority_disp_order => p_ps_rules_rec.bus_priority_disp_order,
992 -- p_clausevalue1 => p_ps_rules_rec.clausevalue1,
993 p_clausevalue1 => null,
994 p_clausevalue2 => l_cl2(i),
995 p_clausevalue3 => l_cl3(i),
996 p_clausevalue4 => p_ps_rules_rec.clausevalue4,
997 p_clausevalue5 => p_ps_rules_rec.clausevalue5,
998 p_clausevalue6 => p_ps_rules_rec.clausevalue6,
999 p_clausevalue7 => p_ps_rules_rec.clausevalue7,
1000 p_clausevalue8 => p_ps_rules_rec.clausevalue8,
1001 p_clausevalue9 => p_ps_rules_rec.clausevalue9,
1002 p_clausevalue10 => p_ps_rules_rec.clausevalue10,
1003 p_use_clause6 => p_ps_rules_rec.use_clause6,
1004 p_use_clause7 => p_ps_rules_rec.use_clause7,
1005 p_use_clause8 => p_ps_rules_rec.use_clause8,
1006 p_use_clause9 => p_ps_rules_rec.use_clause9,
1007 p_use_clause10 => p_ps_rules_rec.use_clause10);
1008
1009 END IF;
1010 END LOOP;
1011 END IF;
1012 ELSE
1013
1014 -- Any of CL4 to CL10 has changed.
1015 UPDATE ams_iba_ps_rules
1016 SET
1017 clausevalue4 = p_ps_rules_rec.clausevalue4,
1018 clausevalue5 = p_ps_rules_rec.clausevalue5,
1019 clausevalue6 = p_ps_rules_rec.clausevalue6,
1020 clausevalue7 = p_ps_rules_rec.clausevalue7,
1021 clausevalue8 = p_ps_rules_rec.clausevalue8,
1022 clausevalue9 = p_ps_rules_rec.clausevalue9,
1023 clausevalue10 = p_ps_rules_rec.clausevalue10,
1024 use_clause6 = p_ps_rules_rec.use_clause6,
1025 use_clause7 = p_ps_rules_rec.use_clause7,
1026 use_clause8 = p_ps_rules_rec.use_clause8,
1027 use_clause9 = p_ps_rules_rec.use_clause9,
1028 use_clause10 = p_ps_rules_rec.use_clause10
1029
1030 WHERE posting_id = p_ps_rules_rec.posting_id
1031 AND rulegroup_id = p_ps_rules_rec.rulegroup_id;
1032
1033 END IF;
1034
1035 END Update_Ps_Rule_Alt;
1036
1037
1038 PROCEDURE Delete_Ps_Rule(
1039 p_api_version_number IN NUMBER,
1040 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1041 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1042 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1043 x_return_status OUT NOCOPY VARCHAR2,
1044 x_msg_count OUT NOCOPY NUMBER,
1045 x_msg_data OUT NOCOPY VARCHAR2,
1046 p_rule_id IN NUMBER,
1047 p_object_version_number IN NUMBER
1051 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Ps_Rule';
1048 )
1049
1050 IS
1052 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1053 l_object_version_number NUMBER;
1054
1055 BEGIN
1056 -- Standard Start of API savepoint
1057 SAVEPOINT DELETE_Ps_Rule_PVT;
1058
1059 -- Standard call to check for call compatibility.
1060 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1061 p_api_version_number,
1062 l_api_name,
1063 G_PKG_NAME)
1064 THEN
1065 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1066 END IF;
1067
1068 -- Initialize message list if p_init_msg_list is set to TRUE.
1069 IF FND_API.to_Boolean( p_init_msg_list )
1070 THEN
1071 FND_MSG_PUB.initialize;
1072 END IF;
1073
1074 -- Debug Message
1075 IF (AMS_DEBUG_HIGH_ON) THEN
1076
1077 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1078 END IF;
1079
1080 -- Initialize API return status to SUCCESS
1081 x_return_status := FND_API.G_RET_STS_SUCCESS;
1082
1083 --
1084 -- Api body
1085 --
1086 -- Debug Message
1087 IF (AMS_DEBUG_HIGH_ON) THEN
1088
1089 AMS_UTILITY_PVT.debug_message('Private API: Calling delete table handler');
1090 END IF;
1091
1092 -- Invoke table handler(AMS_IBA_PS_RULES_PKG.Delete_Row)
1093 AMS_IBA_PS_RULES_PKG.Delete_Row(
1094 p_RULE_ID => p_RULE_ID);
1095 --
1096 -- End of API body
1097 --
1098
1099 -- Standard check for p_commit
1100 IF FND_API.to_Boolean( p_commit )
1101 THEN
1102 COMMIT WORK;
1103 END IF;
1104
1105 -- Debug Message
1106 IF (AMS_DEBUG_HIGH_ON) THEN
1107
1108 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1109 END IF;
1110
1111 -- Standard call to get message count and if count is 1, get message info.
1112 FND_MSG_PUB.Count_And_Get
1113 (p_count => x_msg_count,
1114 p_data => x_msg_data
1115 );
1116 EXCEPTION
1117
1118 WHEN AMS_Utility_PVT.resource_locked THEN
1119 x_return_status := FND_API.g_ret_sts_error;
1120 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1121
1122 WHEN FND_API.G_EXC_ERROR THEN
1123 ROLLBACK TO DELETE_Ps_Rule_PVT;
1124 x_return_status := FND_API.G_RET_STS_ERROR;
1125 -- Standard call to get message count and if count=1, get the message
1126 FND_MSG_PUB.Count_And_Get (
1127 p_encoded => FND_API.G_FALSE,
1128 p_count => x_msg_count,
1129 p_data => x_msg_data
1130 );
1131
1132 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1133 ROLLBACK TO DELETE_Ps_Rule_PVT;
1134 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1135 -- Standard call to get message count and if count=1, get the message
1136 FND_MSG_PUB.Count_And_Get (
1137 p_encoded => FND_API.G_FALSE,
1138 p_count => x_msg_count,
1139 p_data => x_msg_data
1140 );
1141
1142 WHEN OTHERS THEN
1143 ROLLBACK TO DELETE_Ps_Rule_PVT;
1144 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1145 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1146 THEN
1147 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1148 END IF;
1149 -- Standard call to get message count and if count=1, get the message
1150 FND_MSG_PUB.Count_And_Get (
1151 p_encoded => FND_API.G_FALSE,
1152 p_count => x_msg_count,
1153 p_data => x_msg_data
1154 );
1155 End Delete_Ps_Rule;
1156
1157 PROCEDURE Delete_Ps_Rule_Alt(
1158 p_api_version_number IN NUMBER,
1159 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1160 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1161 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1162 x_return_status OUT NOCOPY VARCHAR2,
1163 x_msg_count OUT NOCOPY NUMBER,
1164 x_msg_data OUT NOCOPY VARCHAR2,
1165 p_ps_rules_rec IN ps_rules_rec_type,
1166 p_object_version_number IN NUMBER
1167 )
1168
1169 IS
1170 L_API_NAME CONSTANT VARCHAR2(30) := 'Delete_Ps_Rule_Alt';
1171 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1172 l_object_version_number NUMBER;
1173
1174 BEGIN
1175 -- Standard Start of API savepoint
1176 SAVEPOINT DELETE_Ps_Rule_PVT;
1177
1178 -- Standard call to check for call compatibility.
1179 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1180 p_api_version_number,
1181 l_api_name,
1182 G_PKG_NAME)
1183 THEN
1184 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1185 END IF;
1186
1190 FND_MSG_PUB.initialize;
1187 -- Initialize message list if p_init_msg_list is set to TRUE.
1188 IF FND_API.to_Boolean( p_init_msg_list )
1189 THEN
1191 END IF;
1192
1193 -- Debug Message
1194 IF (AMS_DEBUG_HIGH_ON) THEN
1195
1196 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1197 END IF;
1198
1199 -- Initialize API return status to SUCCESS
1200 x_return_status := FND_API.G_RET_STS_SUCCESS;
1201
1202 --
1203 -- Api body
1204 --
1205 -- Debug Message
1206 IF (AMS_DEBUG_HIGH_ON) THEN
1207
1208 AMS_UTILITY_PVT.debug_message( 'Private API: Calling delete table handler');
1209 END IF;
1210
1211 -- Special delete case
1212 DELETE FROM ams_iba_ps_rules
1213 WHERE posting_id = p_ps_rules_rec.posting_id
1214 AND rulegroup_id = p_ps_rules_rec.rulegroup_id
1215 AND clausevalue2 = p_ps_rules_rec.clausevalue2
1216 AND clausevalue3 = p_ps_rules_rec.clausevalue3;
1217 --
1218 -- End of API body
1219 --
1220
1221 -- Standard check for p_commit
1222 IF FND_API.to_Boolean( p_commit )
1223 THEN
1224 COMMIT WORK;
1225 END IF;
1226
1227 -- Debug Message
1228 IF (AMS_DEBUG_HIGH_ON) THEN
1229
1230 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
1231 END IF;
1232
1233 -- Standard call to get message count and if count is 1, get message info.
1234 FND_MSG_PUB.Count_And_Get
1235 (p_count => x_msg_count,
1236 p_data => x_msg_data
1237 );
1238 EXCEPTION
1239
1240 WHEN AMS_Utility_PVT.resource_locked THEN
1241 x_return_status := FND_API.g_ret_sts_error;
1242 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1243
1244 WHEN FND_API.G_EXC_ERROR THEN
1245 ROLLBACK TO DELETE_Ps_Rule_PVT;
1246 x_return_status := FND_API.G_RET_STS_ERROR;
1247 -- Standard call to get message count and if count=1, get the message
1248 FND_MSG_PUB.Count_And_Get (
1249 p_encoded => FND_API.G_FALSE,
1250 p_count => x_msg_count,
1251 p_data => x_msg_data
1252 );
1253
1254 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1255 ROLLBACK TO DELETE_Ps_Rule_PVT;
1256 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1257 -- Standard call to get message count and if count=1, get the message
1258 FND_MSG_PUB.Count_And_Get (
1259 p_encoded => FND_API.G_FALSE,
1260 p_count => x_msg_count,
1261 p_data => x_msg_data
1262 );
1263
1264 WHEN OTHERS THEN
1265 ROLLBACK TO DELETE_Ps_Rule_PVT;
1266 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1267 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1268 THEN
1269 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1270 END IF;
1271 -- Standard call to get message count and if count=1, get the message
1272 FND_MSG_PUB.Count_And_Get (
1273 p_encoded => FND_API.G_FALSE,
1274 p_count => x_msg_count,
1275 p_data => x_msg_data
1276 );
1277 End Delete_Ps_Rule_Alt;
1278
1279 -- Hint: Primary key needs to be returned.
1280 PROCEDURE Lock_Ps_Rule(
1281 p_api_version_number IN NUMBER,
1282 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1283
1284 x_return_status OUT NOCOPY VARCHAR2,
1285 x_msg_count OUT NOCOPY NUMBER,
1286 x_msg_data OUT NOCOPY VARCHAR2,
1287
1288 p_rule_id IN NUMBER,
1289 p_object_version IN NUMBER
1290 )
1291
1292 IS
1293 L_API_NAME CONSTANT VARCHAR2(30) := 'Lock_Ps_Rule';
1294 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1295 L_FULL_NAME CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
1296 l_RULE_ID NUMBER;
1297
1298 CURSOR c_Ps_Rule IS
1299 SELECT RULE_ID
1300 FROM AMS_IBA_PS_RULES
1301 WHERE RULE_ID = p_RULE_ID
1302 AND object_version_number = p_object_version
1303 FOR UPDATE NOWAIT;
1304
1305 BEGIN
1306
1307 -- Debug Message
1308 IF (AMS_DEBUG_HIGH_ON) THEN
1309
1310 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
1311 END IF;
1312
1313 -- Initialize message list if p_init_msg_list is set to TRUE.
1314 IF FND_API.to_Boolean( p_init_msg_list )
1315 THEN
1316 FND_MSG_PUB.initialize;
1317 END IF;
1318
1319 -- Standard call to check for call compatibility.
1320 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1321 p_api_version_number,
1322 l_api_name,
1323 G_PKG_NAME)
1324 THEN
1325 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1326 END IF;
1327
1328
1329 -- Initialize API return status to SUCCESS
1330 x_return_status := FND_API.G_RET_STS_SUCCESS;
1331
1332
1333 ------------------------ lock -------------------------
1334
1335 IF (AMS_DEBUG_HIGH_ON) THEN
1336
1337
1338
1342 OPEN c_Ps_Rule;
1339 AMS_Utility_PVT.debug_message(l_full_name||': start');
1340
1341 END IF;
1343
1344 FETCH c_Ps_Rule INTO l_RULE_ID;
1345
1346 IF (c_Ps_Rule%NOTFOUND) THEN
1347 CLOSE c_Ps_Rule;
1348 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1349 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1350 FND_MSG_PUB.add;
1351 END IF;
1352 RAISE FND_API.g_exc_error;
1353 END IF;
1354
1355 CLOSE c_Ps_Rule;
1356
1357 -------------------- finish --------------------------
1358 FND_MSG_PUB.count_and_get(
1359 p_encoded => FND_API.g_false,
1360 p_count => x_msg_count,
1361 p_data => x_msg_data);
1362 IF (AMS_DEBUG_HIGH_ON) THEN
1363
1364 AMS_Utility_PVT.debug_message(l_full_name ||': end');
1365 END IF;
1366 EXCEPTION
1367
1368 WHEN AMS_Utility_PVT.resource_locked THEN
1369 x_return_status := FND_API.g_ret_sts_error;
1370 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
1371
1372 WHEN FND_API.G_EXC_ERROR THEN
1373 ROLLBACK TO LOCK_Ps_Rule_PVT;
1374 x_return_status := FND_API.G_RET_STS_ERROR;
1375 -- Standard call to get message count and if count=1, get the message
1376 FND_MSG_PUB.Count_And_Get (
1377 p_encoded => FND_API.G_FALSE,
1378 p_count => x_msg_count,
1379 p_data => x_msg_data
1380 );
1381
1382 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1383 ROLLBACK TO LOCK_Ps_Rule_PVT;
1384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1385 -- Standard call to get message count and if count=1, get the message
1386 FND_MSG_PUB.Count_And_Get (
1387 p_encoded => FND_API.G_FALSE,
1388 p_count => x_msg_count,
1389 p_data => x_msg_data
1390 );
1391
1392 WHEN OTHERS THEN
1393 ROLLBACK TO LOCK_Ps_Rule_PVT;
1394 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1395 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1396 THEN
1397 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1398 END IF;
1399 -- Standard call to get message count and if count=1, get the message
1400 FND_MSG_PUB.Count_And_Get (
1401 p_encoded => FND_API.G_FALSE,
1402 p_count => x_msg_count,
1403 p_data => x_msg_data
1404 );
1405 End Lock_Ps_Rule;
1406
1407
1408 PROCEDURE check_ps_rules_uk_items(
1409 p_ps_rules_rec IN ps_rules_rec_type,
1410 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1411 x_return_status OUT NOCOPY VARCHAR2)
1412 IS
1413 l_valid_flag VARCHAR2(1);
1414
1415 BEGIN
1416 x_return_status := FND_API.g_ret_sts_success;
1417 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1418 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1419 'AMS_IBA_PS_RULES',
1420 'RULE_ID = ''' || p_ps_rules_rec.RULE_ID ||''''
1421 );
1422 ELSE
1423 l_valid_flag := AMS_Utility_PVT.check_uniqueness(
1424 'AMS_IBA_PS_RULES',
1425 'RULE_ID = ''' || p_ps_rules_rec.RULE_ID ||
1426 ''' AND RULE_ID <> ' || p_ps_rules_rec.RULE_ID
1427 );
1428 END IF;
1429
1430 IF l_valid_flag = FND_API.g_false THEN
1431 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_RULE_ID_DUPLICATE');
1432 x_return_status := FND_API.g_ret_sts_error;
1433 RETURN;
1434 END IF;
1435
1436 END check_ps_rules_uk_items;
1437
1438 PROCEDURE check_ps_rules_req_items(
1439 p_ps_rules_rec IN ps_rules_rec_type,
1440 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
1441 x_return_status OUT NOCOPY VARCHAR2
1442 )
1443 IS
1444 BEGIN
1445 x_return_status := FND_API.g_ret_sts_success;
1446
1447 IF p_validation_mode = JTF_PLSQL_API.g_create THEN
1448
1449
1450 IF p_ps_rules_rec.created_by = FND_API.g_miss_num OR p_ps_rules_rec.created_by IS NULL THEN
1451 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_created_by');
1452 x_return_status := FND_API.g_ret_sts_error;
1453 RETURN;
1454 END IF;
1455
1456
1457 IF p_ps_rules_rec.creation_date = FND_API.g_miss_date OR p_ps_rules_rec.creation_date IS NULL THEN
1458 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_creation_date');
1459 x_return_status := FND_API.g_ret_sts_error;
1460 RETURN;
1461 END IF;
1462
1463
1464 IF p_ps_rules_rec.last_updated_by = FND_API.g_miss_num OR p_ps_rules_rec.last_updated_by IS NULL THEN
1465 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_last_updated_by');
1466 x_return_status := FND_API.g_ret_sts_error;
1467 RETURN;
1468 END IF;
1469
1470
1471 IF p_ps_rules_rec.last_update_date = FND_API.g_miss_date OR p_ps_rules_rec.last_update_date IS NULL THEN
1472 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_last_update_date');
1473 x_return_status := FND_API.g_ret_sts_error;
1474 RETURN;
1475 END IF;
1476
1477
1478 IF p_ps_rules_rec.rule_id = FND_API.g_miss_num OR p_ps_rules_rec.rule_id IS NULL THEN
1482 END IF;
1479 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_rule_id');
1480 x_return_status := FND_API.g_ret_sts_error;
1481 RETURN;
1483
1484
1485 IF p_ps_rules_rec.rulegroup_id = FND_API.g_miss_num OR p_ps_rules_rec.rulegroup_id IS NULL THEN
1486 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_rulegroup_id');
1487 x_return_status := FND_API.g_ret_sts_error;
1488 RETURN;
1489 END IF;
1490
1491
1492 IF p_ps_rules_rec.posting_id = FND_API.g_miss_num OR p_ps_rules_rec.posting_id IS NULL THEN
1493 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_posting_id');
1494 x_return_status := FND_API.g_ret_sts_error;
1495 RETURN;
1496 END IF;
1497
1498
1499 IF p_ps_rules_rec.strategy_id = FND_API.g_miss_num OR p_ps_rules_rec.strategy_id IS NULL THEN
1500 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_strategy_id');
1501 x_return_status := FND_API.g_ret_sts_error;
1502 RETURN;
1503 END IF;
1504 ELSE
1505
1506
1507 IF p_ps_rules_rec.created_by IS NULL THEN
1508 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_created_by');
1509 x_return_status := FND_API.g_ret_sts_error;
1510 RETURN;
1511 END IF;
1512
1513
1514 IF p_ps_rules_rec.creation_date IS NULL THEN
1515 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_creation_date');
1516 x_return_status := FND_API.g_ret_sts_error;
1517 RETURN;
1518 END IF;
1519
1520
1521 IF p_ps_rules_rec.last_updated_by IS NULL THEN
1522 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_last_updated_by');
1523 x_return_status := FND_API.g_ret_sts_error;
1524 RETURN;
1525 END IF;
1526
1527
1528 IF p_ps_rules_rec.last_update_date IS NULL THEN
1529 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_last_update_date');
1530 x_return_status := FND_API.g_ret_sts_error;
1531 RETURN;
1532 END IF;
1533
1534
1535 IF p_ps_rules_rec.rule_id IS NULL THEN
1536 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_rule_id');
1537 x_return_status := FND_API.g_ret_sts_error;
1538 RETURN;
1539 END IF;
1540
1541
1542 IF p_ps_rules_rec.rulegroup_id IS NULL THEN
1543 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_rulegroup_id');
1544 x_return_status := FND_API.g_ret_sts_error;
1545 RETURN;
1546 END IF;
1547
1548
1549 IF p_ps_rules_rec.posting_id IS NULL THEN
1550 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_posting_id');
1551 x_return_status := FND_API.g_ret_sts_error;
1552 RETURN;
1553 END IF;
1554
1555
1556 IF p_ps_rules_rec.strategy_id IS NULL THEN
1557 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_ps_rules_NO_strategy_id');
1558 x_return_status := FND_API.g_ret_sts_error;
1559 RETURN;
1560 END IF;
1561 END IF;
1562
1563 END check_ps_rules_req_items;
1564
1565 PROCEDURE check_ps_rules_FK_items(
1566 p_ps_rules_rec IN ps_rules_rec_type,
1567 x_return_status OUT NOCOPY VARCHAR2
1568 )
1569 IS
1570 BEGIN
1571 x_return_status := FND_API.g_ret_sts_success;
1572
1573 -- Enter custom code here
1574
1575 END check_ps_rules_FK_items;
1576
1577 PROCEDURE check_ps_rules_Lookup_items(
1578 p_ps_rules_rec IN ps_rules_rec_type,
1579 x_return_status OUT NOCOPY VARCHAR2
1580 )
1581 IS
1582 BEGIN
1583 x_return_status := FND_API.g_ret_sts_success;
1584
1585 -- Enter custom code here
1586
1587 END check_ps_rules_Lookup_items;
1588
1589 PROCEDURE Check_ps_rules_Items (
1590 P_ps_rules_rec IN ps_rules_rec_type,
1591 p_validation_mode IN VARCHAR2,
1592 x_return_status OUT NOCOPY VARCHAR2
1593 )
1594 IS
1595 BEGIN
1596 x_return_status := FND_API.g_ret_sts_success;
1597
1598 END Check_ps_rules_Items;
1599
1600
1601
1602
1603 PROCEDURE update_filters(
1604 p_rulegroup_id IN NUMBER,
1605 p_ps_filter_tbl IN ps_rules_tuple_tbl_type,
1606 x_return_status OUT NOCOPY VARCHAR2
1607 )
1608 IS
1609 l_tuple ps_rules_tuple_rec_type;
1610 l_id NUMBER;
1611 l_index NUMBER;
1612 l_dummy NUMBER;
1613
1614 CURSOR c_id IS
1615 SELECT AMS_IBA_PS_RL_ST_FLTRS_s.NEXTVAL
1616 FROM dual;
1617
1618 CURSOR c_id_exists (l_id IN NUMBER) IS
1619 SELECT 1
1620 FROM AMS_IBA_PS_RL_ST_FLTRS
1621 WHERE RULE_STRAT_FILTER_ID = l_id;
1622
1623 BEGIN
1624 x_return_status := FND_API.g_ret_sts_success;
1625
1626 --deletes existing filters
1627
1628 IF (AMS_DEBUG_HIGH_ON) THEN
1629
1630
1631
1632 AMS_UTILITY_PVT.debug_message('Private API: - deleting existing filters');
1633
1634 END IF;
1635
1636 DELETE FROM AMS_IBA_PS_RL_ST_FLTRS
1637 WHERE rulegroup_id = p_rulegroup_id;
1638
1642
1639 IF (AMS_DEBUG_HIGH_ON) THEN
1640
1641
1643 AMS_UTILITY_PVT.debug_message('Private API: - deleted existing filters');
1644
1645 END IF;
1646
1647
1648 --adds new filters
1649
1650 IF (AMS_DEBUG_HIGH_ON) THEN
1651
1652
1653
1654 AMS_UTILITY_PVT.debug_message('Private API: - adding new filters');
1655
1656 END IF;
1657
1658 l_index := p_ps_filter_tbl.FIRST;
1659
1660
1661 LOOP
1662 EXIT WHEN l_index IS NULL;
1663
1664 l_tuple := p_ps_filter_tbl(l_index);
1665
1666 LOOP
1667 l_dummy := NULL;
1668 OPEN c_id;
1669 FETCH c_id INTO l_id;
1670 CLOSE c_id;
1671
1672 OPEN c_id_exists(l_id);
1673 FETCH c_id_exists INTO l_dummy;
1674 CLOSE c_id_exists;
1675 EXIT WHEN l_dummy IS NULL;
1676 END LOOP;
1677
1678 INSERT INTO AMS_IBA_PS_RL_ST_FLTRS(
1679 created_by,
1680 creation_date,
1681 last_updated_by,
1682 last_update_date,
1683 last_update_login,
1684 object_version_number,
1685 rule_strat_filter_id,
1686 rulegroup_id,
1687 filter_id,
1688 filter_ref_code
1689 ) VALUES (
1690 FND_GLOBAL.USER_ID,
1691 SYSDATE,
1692 FND_GLOBAL.USER_ID,
1693 SYSDATE,
1694 FND_GLOBAL.CONC_LOGIN_ID,
1695 1,
1696 l_id,
1697 DECODE( p_rulegroup_id, FND_API.g_miss_num, NULL, p_rulegroup_id),
1698 DECODE( to_number(l_tuple.name), FND_API.g_miss_num, NULL, to_number(l_tuple.name)),
1699 DECODE( l_tuple.value, FND_API.g_miss_char, NULL, l_tuple.value));
1700
1701 l_index := p_ps_filter_tbl.NEXT(l_index);
1702 END LOOP;
1703
1704 IF (AMS_DEBUG_HIGH_ON) THEN
1705
1706
1707
1708 AMS_UTILITY_PVT.debug_message('Private API: - added new filters');
1709
1710 END IF;
1711
1712 END update_filters;
1713
1714
1715
1716
1717 PROCEDURE update_strategy_params(
1718 p_rulegroup_id IN NUMBER,
1719 p_ps_strategy_tbl IN ps_rules_tuple_tbl_type,
1720 x_return_status OUT NOCOPY VARCHAR2
1721 )
1722 IS
1723 l_tuple ps_rules_tuple_rec_type;
1724 l_id NUMBER;
1725 l_index NUMBER;
1726 l_dummy NUMBER;
1727
1728 CURSOR c_id IS
1729 SELECT AMS_IBA_PS_RL_ST_PARAMS_s.NEXTVAL
1730 FROM dual;
1731
1732 CURSOR c_id_exists (l_id IN NUMBER) IS
1733 SELECT 1
1734 FROM AMS_IBA_PS_RL_ST_PARAMS
1735 WHERE RULE_STRAT_PARAM_ID = l_id;
1736
1737 BEGIN
1738 x_return_status := FND_API.g_ret_sts_success;
1739
1740 --deletes existing strategy params
1741
1742 IF (AMS_DEBUG_HIGH_ON) THEN
1743
1744
1745
1746 AMS_UTILITY_PVT.debug_message('Private API: - deleting existing strategy parameters');
1747
1748 END IF;
1749
1750 DELETE FROM AMS_IBA_PS_RL_ST_PARAMS
1751 WHERE rulegroup_id = p_rulegroup_id;
1752
1753 IF (AMS_DEBUG_HIGH_ON) THEN
1754
1755
1756
1757 AMS_UTILITY_PVT.debug_message('Private API: - deleted existing strategy parameters');
1758
1759 END IF;
1760
1761
1762 --adds new strategy params
1763
1764 IF (AMS_DEBUG_HIGH_ON) THEN
1765
1766
1767
1768 AMS_UTILITY_PVT.debug_message('Private API: - adding new strategy parameters');
1769
1770 END IF;
1771
1772 l_index := p_ps_strategy_tbl.FIRST;
1773
1774
1775 LOOP
1776 EXIT WHEN l_index IS NULL;
1777
1778 l_tuple := p_ps_strategy_tbl(l_index);
1779 IF(l_tuple.name IS NOT NULL and
1780 l_tuple.value IS NOT NULL) THEN
1781
1782 LOOP
1783 l_dummy := NULL;
1784 OPEN c_id;
1785 FETCH c_id INTO l_id;
1786 CLOSE c_id;
1787
1788 OPEN c_id_exists(l_id);
1789 FETCH c_id_exists INTO l_dummy;
1790 CLOSE c_id_exists;
1791 EXIT WHEN l_dummy IS NULL;
1792 END LOOP;
1793
1794 INSERT INTO AMS_IBA_PS_RL_ST_PARAMS(
1795 created_by,
1796 creation_date,
1797 last_updated_by,
1798 last_update_date,
1799 last_update_login,
1800 object_version_number,
1801 rule_strat_param_id,
1802 rulegroup_id,
1803 parameter_name,
1804 parameter_value
1805 ) VALUES (
1806 FND_GLOBAL.USER_ID,
1807 SYSDATE,
1808 FND_GLOBAL.USER_ID,
1809 SYSDATE,
1810 FND_GLOBAL.CONC_LOGIN_ID,
1811 1,
1812 l_id,
1813 DECODE( p_rulegroup_id, FND_API.g_miss_num, NULL, p_rulegroup_id),
1814 DECODE( l_tuple.name, FND_API.g_miss_char, NULL, l_tuple.name),
1815 DECODE( l_tuple.value, FND_API.g_miss_char, NULL, l_tuple.value));
1816 END IF;
1817
1818 l_index := p_ps_strategy_tbl.NEXT(l_index);
1819 END LOOP;
1820
1824
1821 IF (AMS_DEBUG_HIGH_ON) THEN
1822
1823
1825 AMS_UTILITY_PVT.debug_message('Private API: - added new strategy parameters');
1826
1827 END IF;
1828
1829 END update_strategy_params;
1830
1831
1832 PROCEDURE Complete_ps_rules_Rec (
1833 p_ps_rules_rec IN ps_rules_rec_type,
1834 x_complete_rec OUT NOCOPY ps_rules_rec_type)
1835 IS
1836 l_return_status VARCHAR2(1);
1837
1838 CURSOR c_complete IS
1839 SELECT *
1840 FROM ams_iba_ps_rules
1841 WHERE rule_id = p_ps_rules_rec.rule_id;
1842 l_ps_rules_rec c_complete%ROWTYPE;
1843 BEGIN
1844 x_complete_rec := p_ps_rules_rec;
1845
1846
1847 OPEN c_complete;
1848 FETCH c_complete INTO l_ps_rules_rec;
1849 CLOSE c_complete;
1850
1851 -- created_by
1852 IF p_ps_rules_rec.created_by = FND_API.g_miss_num THEN
1853 x_complete_rec.created_by := l_ps_rules_rec.created_by;
1854 END IF;
1855
1856 -- creation_date
1857 IF p_ps_rules_rec.creation_date = FND_API.g_miss_date THEN
1858 x_complete_rec.creation_date := l_ps_rules_rec.creation_date;
1859 END IF;
1860
1861 -- last_updated_by
1862 IF p_ps_rules_rec.last_updated_by = FND_API.g_miss_num THEN
1863 x_complete_rec.last_updated_by := l_ps_rules_rec.last_updated_by;
1864 END IF;
1865
1866 -- last_update_date
1867 IF p_ps_rules_rec.last_update_date = FND_API.g_miss_date THEN
1868 x_complete_rec.last_update_date := l_ps_rules_rec.last_update_date;
1869 END IF;
1870
1871 -- last_update_login
1872 IF p_ps_rules_rec.last_update_login = FND_API.g_miss_num THEN
1873 x_complete_rec.last_update_login := l_ps_rules_rec.last_update_login;
1874 END IF;
1875
1876 -- object_version_number
1877 IF p_ps_rules_rec.object_version_number = FND_API.g_miss_num THEN
1878 x_complete_rec.object_version_number := l_ps_rules_rec.object_version_number;
1879 END IF;
1880
1881 -- rule_id
1882 IF p_ps_rules_rec.rule_id = FND_API.g_miss_num THEN
1883 x_complete_rec.rule_id := l_ps_rules_rec.rule_id;
1884 END IF;
1885
1886 -- rulegroup_id
1887 IF p_ps_rules_rec.rulegroup_id = FND_API.g_miss_num THEN
1888 x_complete_rec.rulegroup_id := l_ps_rules_rec.rulegroup_id;
1889 END IF;
1890
1891 -- posting_id
1892 IF p_ps_rules_rec.posting_id = FND_API.g_miss_num THEN
1893 x_complete_rec.posting_id := l_ps_rules_rec.posting_id;
1894 END IF;
1895
1896 -- strategy_id
1897 IF p_ps_rules_rec.strategy_id = FND_API.g_miss_num THEN
1898 x_complete_rec.strategy_id := l_ps_rules_rec.strategy_id;
1899 END IF;
1900
1901 -- bus_priority_code
1902 IF p_ps_rules_rec.bus_priority_code = FND_API.g_miss_char THEN
1903 x_complete_rec.bus_priority_code := l_ps_rules_rec.bus_priority_code;
1904 END IF;
1905
1906 -- bus_priority_disp_order
1907 IF p_ps_rules_rec.bus_priority_disp_order = FND_API.g_miss_char THEN
1908 x_complete_rec.bus_priority_disp_order := l_ps_rules_rec.bus_priority_disp_order;
1909 END IF;
1910
1911 -- clausevalue1
1912 IF p_ps_rules_rec.clausevalue1 = FND_API.g_miss_char THEN
1913 x_complete_rec.clausevalue1 := l_ps_rules_rec.clausevalue1;
1914 END IF;
1915
1916 -- clausevalue2
1917 IF p_ps_rules_rec.clausevalue2 = FND_API.g_miss_num THEN
1918 x_complete_rec.clausevalue2 := l_ps_rules_rec.clausevalue2;
1919 END IF;
1920
1921 -- clausevalue3
1922 IF p_ps_rules_rec.clausevalue3 = FND_API.g_miss_char THEN
1923 x_complete_rec.clausevalue3 := l_ps_rules_rec.clausevalue3;
1924 END IF;
1925
1926 -- clausevalue4
1927 IF p_ps_rules_rec.clausevalue4 = FND_API.g_miss_char THEN
1928 x_complete_rec.clausevalue4 := l_ps_rules_rec.clausevalue4;
1929 END IF;
1930
1931 -- clausevalue5
1932 IF p_ps_rules_rec.clausevalue5 = FND_API.g_miss_num THEN
1933 x_complete_rec.clausevalue5 := l_ps_rules_rec.clausevalue5;
1934 END IF;
1935
1936 -- clausevalue6
1937 IF p_ps_rules_rec.clausevalue6 = FND_API.g_miss_char THEN
1938 x_complete_rec.clausevalue6 := l_ps_rules_rec.clausevalue6;
1939 END IF;
1940
1941 -- clausevalue7
1942 IF p_ps_rules_rec.clausevalue7 = FND_API.g_miss_char THEN
1943 x_complete_rec.clausevalue7 := l_ps_rules_rec.clausevalue7;
1944 END IF;
1945
1946 -- clausevalue8
1947 IF p_ps_rules_rec.clausevalue8 = FND_API.g_miss_char THEN
1948 x_complete_rec.clausevalue8 := l_ps_rules_rec.clausevalue8;
1949 END IF;
1950
1951 -- clausevalue9
1952 IF p_ps_rules_rec.clausevalue9 = FND_API.g_miss_char THEN
1953 x_complete_rec.clausevalue9 := l_ps_rules_rec.clausevalue9;
1954 END IF;
1955
1956 -- clausevalue10
1957 IF p_ps_rules_rec.clausevalue10 = FND_API.g_miss_char THEN
1958 x_complete_rec.clausevalue10 := l_ps_rules_rec.clausevalue10;
1959 END IF;
1960 -- Note: Developers need to modify the procedure
1961 -- to handle any business specific requirements.
1962 END Complete_ps_rules_Rec;
1963
1964 PROCEDURE Validate_ps_rule(
1965 p_api_version_number IN NUMBER,
1966 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1967 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1968 p_ps_rules_rec IN ps_rules_rec_type,
1969 x_return_status OUT NOCOPY VARCHAR2,
1970 x_msg_count OUT NOCOPY NUMBER,
1971 x_msg_data OUT NOCOPY VARCHAR2
1972 )
1973 IS
1974 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Ps_Rule';
1975 L_API_VERSION_NUMBER CONSTANT NUMBER := 1.0;
1976 l_object_version_number NUMBER;
1977 l_ps_rules_rec AMS_Ps_Rule_PVT.ps_rules_rec_type;
1978
1979 BEGIN
1980 -- Standard Start of API savepoint
1981 SAVEPOINT VALIDATE_Ps_Rule_;
1982
1983 -- Standard call to check for call compatibility.
1984 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
1985 p_api_version_number,
1986 l_api_name,
1990 END IF;
1987 G_PKG_NAME)
1988 THEN
1989 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1991
1992 -- Initialize message list if p_init_msg_list is set to TRUE.
1993 IF FND_API.to_Boolean( p_init_msg_list )
1994 THEN
1995 FND_MSG_PUB.initialize;
1996 END IF;
1997 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1998 Check_ps_rules_Items(
1999 p_ps_rules_rec => p_ps_rules_rec,
2000 p_validation_mode => JTF_PLSQL_API.g_update,
2001 x_return_status => x_return_status
2002 );
2003
2004 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
2005 RAISE FND_API.G_EXC_ERROR;
2006 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
2007 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2008 END IF;
2009 END IF;
2010
2011 -- Debug Message
2012 IF (AMS_DEBUG_HIGH_ON) THEN
2013
2014 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'start');
2015 END IF;
2016
2017 -- Initialize API return status to SUCCESS
2018 x_return_status := FND_API.G_RET_STS_SUCCESS;
2019
2020 -- Debug Message
2021 IF (AMS_DEBUG_HIGH_ON) THEN
2022
2023 AMS_UTILITY_PVT.debug_message('Private API: ' || l_api_name || 'end');
2024 END IF;
2025
2026 -- Standard call to get message count and if count is 1, get message info.
2027 FND_MSG_PUB.Count_And_Get
2028 (p_count => x_msg_count,
2029 p_data => x_msg_data
2030 );
2031 EXCEPTION
2032
2033 WHEN AMS_Utility_PVT.resource_locked THEN
2034 x_return_status := FND_API.g_ret_sts_error;
2035 AMS_Utility_PVT.Error_Message(p_message_name => 'AMS_API_RESOURCE_LOCKED');
2036
2037 WHEN FND_API.G_EXC_ERROR THEN
2038 ROLLBACK TO VALIDATE_Ps_Rule_;
2039 x_return_status := FND_API.G_RET_STS_ERROR;
2040 -- Standard call to get message count and if count=1, get the message
2041 FND_MSG_PUB.Count_And_Get (
2042 p_encoded => FND_API.G_FALSE,
2043 p_count => x_msg_count,
2044 p_data => x_msg_data
2045 );
2046
2047 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2048 ROLLBACK TO VALIDATE_Ps_Rule_;
2049 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2050 -- Standard call to get message count and if count=1, get the message
2051 FND_MSG_PUB.Count_And_Get (
2052 p_encoded => FND_API.G_FALSE,
2053 p_count => x_msg_count,
2054 p_data => x_msg_data
2055 );
2056
2057 WHEN OTHERS THEN
2058 ROLLBACK TO VALIDATE_Ps_Rule_;
2059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2060 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2061 THEN
2062 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
2063 END IF;
2064 -- Standard call to get message count and if count=1, get the message
2065 FND_MSG_PUB.Count_And_Get (
2066 p_encoded => FND_API.G_FALSE,
2067 p_count => x_msg_count,
2068 p_data => x_msg_data
2069 );
2070 End Validate_Ps_Rule;
2071
2072
2073 PROCEDURE Validate_ps_rules_rec(
2074 p_api_version_number IN NUMBER,
2075 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2076 x_return_status OUT NOCOPY VARCHAR2,
2077 x_msg_count OUT NOCOPY NUMBER,
2078 x_msg_data OUT NOCOPY VARCHAR2,
2079 p_ps_rules_rec IN ps_rules_rec_type
2080 )
2081 IS
2082 BEGIN
2083 -- Initialize message list if p_init_msg_list is set to TRUE.
2084 IF FND_API.to_Boolean( p_init_msg_list )
2085 THEN
2086 FND_MSG_PUB.initialize;
2087 END IF;
2088
2089 -- Initialize API return status to SUCCESS
2090 x_return_status := FND_API.G_RET_STS_SUCCESS;
2091
2092 -- Hint: Validate data
2093 -- If data not valid
2094 -- THEN
2095 -- x_return_status := FND_API.G_RET_STS_ERROR;
2096
2097 -- Debug Message
2098 IF (AMS_DEBUG_HIGH_ON) THEN
2099
2100 AMS_UTILITY_PVT.debug_message('Private API: Validate_dm_model_rec');
2101 END IF;
2102 -- Standard call to get message count and if count is 1, get message info.
2103 FND_MSG_PUB.Count_And_Get
2104 (p_count => x_msg_count,
2105 p_data => x_msg_data
2106 );
2107 END Validate_ps_rules_Rec;
2108
2109 END AMS_Ps_Rule_PVT;