[Home] [Help]
PACKAGE BODY: APPS.AMS_LISTACTION_PVT
Source
1 PACKAGE BODY AMS_ListAction_PVT as
2 /* $Header: amsvlsab.pls 120.3 2006/04/28 03:59:39 bmuthukr ship $ */
3 ------------------------------------------------------------------
4 -- Copyright (c) 1999 Oracle Corporation --
5 -- Redwood Shores, California, USA --
6 -- All rights reserved. --
7 ------------------------------------------------------------------
8 -- Start of Comments
9 --
10 -- PACKAGE
11 -- AMS_ListAction_PVT
12
13 -- Procedures:
14
15 -- Create_ListAction
16 -- Update_ListAction
17 -- Delete_ListAction
18 -- Lock_ListAction
19 -- Validate_ListAction
20
21 -- check_action_req_items
22 -- check_action_uk_items
23 -- check_action_fk_items
24 -- check_action_lookup_items
25 --
26 -- check_action_items
27 -- check_action_record
28 -- complete_action_rec
29 -- init_action_rec
30 -- HISTORY
31 -- 19-Apr-2001 choang Excluded MODL and SCOR from rank validation
32 -- 13-Sep-2001 choang Modified validation of ARC_INCL_OBJECT_FROM to use
33 -- AMS_DM_SOURCE_TYPE for lookup validation if the
34 -- arc used by is MODL or SCOR.
35 -- 14-Oct-2002 nyostos Added callout to Model/Score code to INVALIDATE
36 -- Model/Scoring Run when list select actions records
37 -- are added, deleted or updated.
38
39 G_PKG_NAME CONSTANT VARCHAR2(30):='AMS_ListAction_PVT';
40 G_FILE_NAME CONSTANT VARCHAR2(12):='amsvlsab.pls';
41
42 AMS_DEBUG_HIGH_ON boolean := FND_MSG_PUB.CHECK_MSG_LEVEL(FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH);
43
44
45 ---------------------------------------------------------------------
46 -- PROCEDURE
47 -- check_list_action_changes
48 --
49 -- HISTORY
50 -- 14-Oct-2002 nyostos Check if the list select action has changed
51 -- and then make a callout to Model/Score code
52 -- to Invalidate if appropraite.
53 ---------------------------------------------------------------------
54 PROCEDURE check_list_action_changes(
55 p_action_rec IN action_rec_type,
56 x_rec_changed OUT NOCOPY VARCHAR2
57 );
58
59
60 ---------------------------------------------------------------------
61 -- PROCEDURE
62 -- check_action_record
63 --
64 -- HISTORY
65 -- 01/22/2001 vbhandar modified to check whether action is INCLUDE
66 -- for the lowest order number.
67 ---------------------------------------------------------------------
68 PROCEDURE check_action_record(
69 p_action_rec IN action_rec_type,
70 p_complete_rec IN action_rec_type,
71 x_return_status OUT NOCOPY VARCHAR2
72 )
73 IS
74 --Checking that the same incl_object_name and arc_incl_object_from
75 --is not included more than once in the set of list actions.
76
77 Cursor C_Is_Source_Unique IS
78 SELECT count(*)
79 FROM ams_list_select_actions
80 WHERE action_used_by_id = p_action_rec.action_used_by_id
81 AND arc_action_used_by = p_action_rec.arc_action_used_by
82 AND list_select_action_id <> p_action_rec.list_select_action_id
83 AND incl_object_id = p_action_rec.incl_object_id
84 AND arc_incl_object_from = p_action_rec.arc_incl_object_from;
85
86 --stores the result of C_Is_Source_Unique cursor.
87 l_source_count number;
88
89 --Checking that the same order number does not exist among the set of list actions for the list.
90 Cursor C_Is_Ord_No_Unique Is
91 SELECT Count(*)
92 FROM ams_list_select_actions
93 WHERE action_used_by_id = p_action_rec.action_used_by_id
94 AND arc_action_used_by = p_action_rec.arc_action_used_by
95 AND list_select_action_id <> p_action_rec.list_select_action_id
96 AND order_number = p_action_rec.order_number;
97
98 --stores the result of the C_Is_Ord_No_Unique cursor.
99 l_ord_no_count number;
100
101
102 --Getting The Count of Actions excluding the current one for this list.
103 Cursor C_Actions_Exist IS
104 SELECT count(*)
105 FROM ams_list_select_actions
106 WHERE action_used_by_id = p_action_rec.action_used_by_id
107 AND arc_action_used_by = p_action_rec.arc_action_used_by
108 AND list_select_action_id <> p_action_rec.list_select_action_id;
109
110
111 l_min_Order number;
112 cursor c_min_order is
113 SELECT nvl(Min(order_number),-1)
114 FROM ams_list_select_actions
115 WHERE action_used_by_id = p_action_rec.action_used_by_id
116 AND arc_action_used_by = p_action_rec.arc_action_used_by
117 AND list_select_action_id <> p_action_rec.list_select_action_id;
118
119 --Getting list select action type where order num is min .
120 Cursor C_Min_List_Selection_Type IS
121 SELECT list_action_type
122 FROM ams_list_select_actions
123 WHERE action_used_by_id = p_action_rec.action_used_by_id
124 AND arc_action_used_by = p_action_rec.arc_action_used_by
125 and order_number = l_min_order;
126 l_action_type varchar2(30);
127
128 --stores the count of C_Actions_Exist
129 l_action_count number;
130
131
132 BEGIN
133 x_return_status := FND_API.g_ret_sts_success;
134 --getting the count of actions for the list.
135 open c_actions_exist;
136 fetch c_actions_exist into l_action_count;
137 close c_actions_exist;
138
139 IF p_action_rec.ORDER_NUMBER <> FND_API.G_MISS_NUM
140 AND p_action_rec.ORDER_NUMBER IS NOT NULL THEN
141 IF(l_action_count >0 ) THEN
142 open c_is_ord_no_unique;
143 fetch c_is_ord_no_unique into l_ord_no_count;
144 close c_is_ord_no_unique;
145
146 IF(l_ord_no_count > 0 ) THEN
147 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
148 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_BAD_ORD_NUM');
149 FND_MSG_PUB.Add;
150 END IF;
151
152 x_return_status := FND_API.G_RET_STS_ERROR;
153 -- If any errors happen abort API/Procedure.
154 RAISE FND_API.G_EXC_ERROR;
155
156 ELSE
157 x_return_status := FND_API.G_RET_STS_SUCCESS;
158 END IF;
159 ELSE
160 x_return_status := FND_API.G_RET_STS_SUCCESS;
161 END IF;
162 END IF;
163
164 --checking that the action with lowest order number is INCLUDE
165 open c_min_order;
166 fetch c_min_order into l_min_Order;
167 close c_min_order;
168
169 if (l_min_Order <> -1 ) and (l_min_Order < p_action_rec.order_number ) then
170 OPEN C_Min_List_Selection_Type;
171 FETCH C_Min_List_Selection_Type INTO l_action_type;
172 CLOSE C_Min_List_Selection_Type;
173 else
174 l_action_type := p_action_rec.list_action_type;
175 end if;
176 IF l_action_type <> FND_API.G_MISS_CHAR
177 AND l_action_type IS NOT NULL THEN
178 IF(l_action_type <>'INCLUDE')THEN
179 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
180 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_FIRST_INCLUDE');
181 FND_MSG_PUB.Add;
182 END IF;
183 x_return_status := FND_API.G_RET_STS_ERROR;
184 RAISE FND_API.G_EXC_ERROR;
185 END IF; --end if l_action_type <>'INCLUDE'
186 END IF;-- end IF l_action_type <> FND_API.G_MISS_CHAR
187
188 --checking that the same action source is not included more than once in the list.
189 IF ( p_action_rec.ARC_INCL_OBJECT_FROM <> FND_API.G_MISS_CHAR AND p_action_rec.ARC_INCL_OBJECT_FROM IS NOT NULL)
190 AND
191 ( p_action_rec.INCL_OBJECT_ID <> FND_API.G_MISS_NUM AND p_action_rec.INCL_OBJECT_ID IS NOT NULL ) THEN
192
193 IF(l_action_count >0 ) THEN
194 OPEN c_is_source_unique;
195 FETCH c_is_source_unique into l_source_count;
196 CLOSE c_is_source_unique;
197
198 IF(l_source_count = 0 )THEN
199 x_return_status := FND_API.G_RET_STS_SUCCESS;
200 ELSE
201 -- Error, check the msg level and added an error message to the
202 -- API message list
203 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
204 THEN
205 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_DUPE_ACTION');
206 FND_MESSAGE.set_token('ACT_SOURCE', p_action_rec.arc_incl_object_from ||'-->'|| p_action_rec.incl_object_id );
207 FND_MSG_PUB.Add;
208 END IF;
209
210 x_return_status := FND_API.G_RET_STS_ERROR;
211 -- If any errors happen abort API/Procedure.
212 RAISE FND_API.G_EXC_ERROR;
213 END IF;
214 ELSE
215 x_return_status := FND_API.G_RET_STS_SUCCESS;
216 END IF;
217 END IF;
218
219 IF(p_action_rec.DISTRIBUTION_PCT IS NOT NULL AND p_action_rec.DISTRIBUTION_PCT <> FND_API.G_MISS_NUM ) THEN
220 IF (p_action_rec.DISTRIBUTION_PCT > 100 )THEN
221 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_DIST_PCT_INVALID');
222 FND_MSG_PUB.Add;
223 x_return_status := FND_API.G_RET_STS_ERROR;
224 RAISE FND_API.G_EXC_ERROR;
225 END IF;
226 IF (p_action_rec.DISTRIBUTION_PCT <= 0 )THEN
227 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_DIST_PCT_INVALID');
228 FND_MSG_PUB.Add;
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 RAISE FND_API.G_EXC_ERROR;
231 END IF;
232 END IF;
233
234 IF(p_action_rec.list_action_type <> 'INCLUDE' ) THEN
235 IF(p_action_rec.DISTRIBUTION_PCT IS NOT NULL AND p_action_rec.DISTRIBUTION_PCT <> FND_API.G_MISS_NUM ) THEN
236 FND_MESSAGE.set_name('AMS', 'AMS_DIST_PCT_NULL');
237 FND_MSG_PUB.Add;
238 x_return_status := FND_API.G_RET_STS_ERROR;
239 RAISE FND_API.G_EXC_ERROR;
240 END IF;
241 END IF;
242
243
244 -- do other record level checkings
245
246 END check_action_record;
247
248
249
250 ---------------------------------------------------------------------
251 -- PROCEDURE
252 -- check_action_uk_items
253 --
254 -- HISTORY
255 -- 10/14/99 tdonohoe Created.
256 -- 01/22/01 vbhandar modified to change list header id to action
257 -- used by id and arc action used by
258 -- check uniqueness of rank
259 ---------------------------------------------------------------------
260 PROCEDURE check_action_uk_items(
261 p_action_rec IN action_rec_type,
262 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
263 x_return_status OUT NOCOPY VARCHAR2
264 )
265 IS
266
267 BEGIN
268
269 x_return_status := FND_API.g_ret_sts_success;
270
271 -- For create_action, when action_id is passed in, we need to
272 -- check if this action_id is unique.
273 IF p_validation_mode = JTF_PLSQL_API.g_create
274 AND p_action_rec.list_select_action_id IS NOT NULL
275 THEN
276 IF AMS_Utility_PVT.check_uniqueness(
277 'ams_list_select_actions',
278 'list_select_action_id = ' || p_action_rec.list_select_action_id
279 ) = FND_API.g_false
280 THEN
281 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
282 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_DUPE_ACTION');
283 FND_MSG_PUB.add;
284 END IF;
285 x_return_status := FND_API.g_ret_sts_error;
286 RETURN;
287 END IF;
288 END IF;
289
290 IF p_validation_mode = JTF_PLSQL_API.g_create
291 AND p_action_rec.order_number IS NOT NULL
292 THEN
293 IF AMS_Utility_PVT.check_uniqueness(
294 'ams_list_select_actions',
295 'order_number = ' || p_action_rec.order_number||
296 ' and action_used_by_id = '||p_action_rec.action_used_by_id
297 ||' and arc_action_used_by = '||p_action_rec.arc_action_used_by
298 ) = FND_API.g_false
299 THEN
300 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
301 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_BAD_ORD_NUM');
302 FND_MSG_PUB.add;
303 END IF;
304 x_return_status := FND_API.g_ret_sts_error;
305 RETURN;
306 END IF;
307 END IF;
308
309 IF p_validation_mode = JTF_PLSQL_API.g_create
310 AND p_action_rec.rank IS NOT NULL
311 THEN
312 IF AMS_Utility_PVT.check_uniqueness(
313 'ams_list_select_actions',
314 'rank = ' || p_action_rec.rank||
315 ' and action_used_by_id = '||
316 p_action_rec.action_used_by_id
317 ||' and arc_action_used_by = '||
318 p_action_rec.arc_action_used_by
319 ) = FND_API.g_false
320 THEN
321 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
322 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_RANK_NUM');
323 FND_MSG_PUB.add;
324 END IF;
325 x_return_status := FND_API.g_ret_sts_error;
326 RETURN;
327 END IF;
328 END IF;
329
330 END check_action_uk_items;
331
332 ---------------------------------------------------------------------
333 -- PROCEDURE
334 -- check_action_fk_items
335 --
336 -- HISTORY
337 -- 10/14/99 tdonohoe Created.
338 ---------------------------------------------------------------------
339 PROCEDURE check_action_fk_items(
340 p_action_rec IN action_rec_type,
341 x_return_status OUT NOCOPY VARCHAR2
342 )
343 IS
344
345 l_arc_incl_object_from varchar2(30);
346 l_table_name varchar2(100);
347 l_pk_name varchar2(100);
348
349 BEGIN
350
351 x_return_status := FND_API.g_ret_sts_success;
352
353 IF p_action_rec.arc_action_used_by <> FND_API.g_miss_char THEN
354
355 AMS_Utility_PVT.get_qual_table_name_and_pk(
356 p_sys_qual => p_action_rec.arc_action_used_by,
357 x_return_status => x_return_status,
358 x_table_name => l_table_name,
359 x_pk_name => l_pk_name
360 );
361
362 IF x_return_status <> FND_API.g_ret_sts_success THEN
363 RETURN;
364 END IF;
365
366 IF p_action_rec.action_used_by_id <> FND_API.g_miss_num THEN
367 IF ( AMS_Utility_PVT.Check_FK_Exists(l_table_name
368 , l_pk_name
369 , p_action_rec.action_used_by_id)
370 = FND_API.G_TRUE)
371 THEN
372 x_return_status := FND_API.G_RET_STS_SUCCESS;
373
374 ELSE
375 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
376 THEN
377 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ID_MISSING');
378 FND_MSG_PUB.Add;
379 END IF;
380 x_return_status := FND_API.G_RET_STS_ERROR;
381 RAISE FND_API.G_EXC_ERROR;
382 END IF; -- end AMS_Utility_PVT.Check_FK_Exists
383 END IF; -- end p_action_rec.action_used_by_id
384 END IF; --end p_action_rec.arc_action_used_by <> FND_API.g_miss_char
385
386 IF p_action_rec.arc_incl_object_from <> FND_API.g_miss_char THEN
387
388 if p_action_rec.arc_incl_object_from = 'STANDARD' then
389 l_arc_incl_object_from := 'LIST';
390 elsif p_action_rec.arc_incl_object_from = 'MANUAL' then
391 l_arc_incl_object_from := 'LIST';
392 else
393 l_arc_incl_object_from := p_action_rec.arc_incl_object_from;
394 end if;
395 AMS_Utility_PVT.get_qual_table_name_and_pk(
396 p_sys_qual => l_arc_incl_object_from,
397 x_return_status => x_return_status,
398 x_table_name => l_table_name,
402 IF x_return_status <> FND_API.g_ret_sts_success THEN
399 x_pk_name => l_pk_name
400 );
401
403 RETURN;
404 END IF;
405
406 IF p_action_rec.incl_object_id <> FND_API.g_miss_num THEN
407 IF ( AMS_Utility_PVT.Check_FK_Exists(l_table_name
408 , l_pk_name
409 , p_action_rec.incl_object_id)
410 = FND_API.G_TRUE)
411 THEN
412 x_return_status := FND_API.G_RET_STS_SUCCESS;
413
414 ELSE
415 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
416 THEN
417 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_SRC_INVALID');
418 FND_MESSAGE.set_token('LIST_SOURCE', p_action_rec.arc_incl_object_from ||'-->'|| p_action_rec.incl_object_id );
419 FND_MSG_PUB.Add;
420 END IF;
421 x_return_status := FND_API.G_RET_STS_ERROR;
422 RAISE FND_API.G_EXC_ERROR;
423 END IF; -- end AMS_Utility_PVT.Check_FK_Exists
424 END IF; -- p_action_rec.incl_object_id <> FND_API.g_miss_num
425 END IF; --end p_action_rec.arc_incl_object_from <> FND_API.g_miss_char
426 -- check other fk items
427 END check_action_fk_items;
428
429 PROCEDURE check_action_other_items(
430 p_action_rec IN action_rec_type,
431 x_return_status OUT NOCOPY VARCHAR2
432 )
433 IS
434
435 l_table_name varchar2(100);
436 l_pk_name varchar2(100);
437
438 BEGIN
439
440 x_return_status := FND_API.g_ret_sts_success;
441
442 IF p_action_rec.order_number <> FND_API.g_miss_num THEN
443 if p_action_rec.order_number < 1 then
444 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ORDER_NUMBER_RANK');
445 FND_MESSAGE.set_token('FIELD', 'ORDER_NUMBER');
446 FND_MSG_PUB.add;
447 x_return_status := FND_API.g_ret_sts_error;
448 return ;
449 end if;
450 if trunc(p_action_rec.order_number) <> p_action_rec.order_number then
451 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ORDER_NUMBER_RANK');
452 FND_MESSAGE.set_token('FIELD', 'ORDER_NUMBER');
453 FND_MSG_PUB.add;
454 x_return_status := FND_API.g_ret_sts_error;
455 return ;
456 end if;
457
458 END IF;
459
460
461 IF p_action_rec.rank <> FND_API.g_miss_num AND p_action_rec.arc_action_used_by NOT IN ('MODL', 'SCOR') THEN
462 if p_action_rec.rank < 1 then
463 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ORDER_NUMBER_RANK');
464 FND_MESSAGE.set_token('FIELD', 'RANK');
465 FND_MSG_PUB.add;
466 x_return_status := FND_API.g_ret_sts_error;
467 return ;
468 end if;
469 if trunc(p_action_rec.rank) <> p_action_rec.rank then
470 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ORDER_NUMBER_RANK');
471 FND_MESSAGE.set_token('FIELD', 'RANK');
472 FND_MSG_PUB.add;
473 x_return_status := FND_API.g_ret_sts_error;
474 return ;
475 end if;
476
477 END IF;
478
479 IF (p_action_rec.list_action_type = 'INCLUDE' AND p_action_rec.arc_action_used_by NOT IN ('MODL', 'SCOR')) THEN
480 IF (p_action_rec.rank = FND_API.g_miss_num ) THEN
481 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_RANK_MISSING');
482 FND_MSG_PUB.add;
483 x_return_status := FND_API.g_ret_sts_error;
484 return ;
485 end if;
486 IF (p_action_rec.rank is null ) THEN
487 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_RANK_MISSING');
488 FND_MSG_PUB.add;
489 x_return_status := FND_API.g_ret_sts_error;
490 return ;
491 end if;
492
493
494 END IF;
495
496 IF p_action_rec.incl_control_group <> FND_API.g_miss_char
497 AND p_action_rec.incl_control_group IS NOT NULL THEN
498 IF AMS_Utility_PVT.is_Y_or_N(p_action_rec.incl_control_group) = FND_API.g_false THEN
499 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
500 FND_MESSAGE.set_name('AMS', 'AMS_LIST_BAD_FLAG');
501 FND_MSG_PUB.add;
502 END IF;
503 x_return_status := FND_API.g_ret_sts_error;
504 RETURN;
505 END IF;
506 END IF;
507 -- check other other items
508 END check_action_other_items;
509
510 ---------------------------------------------------------------------
511 -- PROCEDURE
512 -- check_action_lookup_items
513 --
514 -- HISTORY
515 -- 10/14/99 tdonohoe Created.
516 -- 01/22/01 vbhandar check lookup for AMS_SELECT_ACTION_USED_BY
517 ---------------------------------------------------------------------
518 PROCEDURE check_action_lookup_items(
519 p_action_rec IN action_rec_type,
520 x_return_status OUT NOCOPY VARCHAR2
521 )
522 IS
523 BEGIN
524
525 x_return_status := FND_API.g_ret_sts_success;
526
527 ----------------------- arc_action_used_by ------------------------
528 IF p_action_rec.arc_action_used_by <> FND_API.g_miss_char THEN
529 IF AMS_Utility_PVT.check_lookup_exists(
530 p_lookup_type => 'AMS_SELECT_ACTION_USED_BY',
534 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
531 p_lookup_code => p_action_rec.arc_action_used_by
532 ) = FND_API.g_false
533 THEN
535 THEN
536 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACTION_USEDBY_INVALID');
537 FND_MESSAGE.set_token('ACTION_USED_BY', p_action_rec.arc_action_used_by);
538 FND_MSG_PUB.add;
539 END IF;
540
541 x_return_status := FND_API.g_ret_sts_error;
542 RETURN;
543 END IF;
544 END IF;
545
546 ----------------------- list_action_type ------------------------
547 IF p_action_rec.list_action_type <> FND_API.g_miss_char THEN
548 IF AMS_Utility_PVT.check_lookup_exists(
549 p_lookup_type => 'AMS_LIST_SELECT_ACTION',
550 p_lookup_code => p_action_rec.list_action_type
551 ) = FND_API.g_false
552 THEN
553 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
554 THEN
555 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_BAD_TYPE');
556 FND_MSG_PUB.add;
557 END IF;
558
559 x_return_status := FND_API.g_ret_sts_error;
560 RETURN;
561 END IF;
562 END IF;
563
564 ----------------------- arc_incl_object_type ------------------------
565 IF p_action_rec.arc_incl_object_from <> FND_API.g_miss_char THEN
566 IF p_action_rec.arc_action_used_by NOT IN ('MODL', 'SCOR') THEN
567 IF AMS_Utility_PVT.check_lookup_exists(
568 p_lookup_type => 'AMS_LIST_SELECT_TYPE',
569 p_lookup_code => p_action_rec.arc_incl_object_from
570 ) = FND_API.g_false
571 THEN
572 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
573 THEN
574 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_SRC_INVALID');
575 FND_MESSAGE.set_token('LIST_SOURCE', p_action_rec.arc_incl_object_from);
576 FND_MSG_PUB.add;
577 END IF;
578
579 x_return_status := FND_API.g_ret_sts_error;
580 RETURN;
581 END IF;
582 ELSE -- use different lookup for model and score
583 -- CSCH included in the lookup breaks the rendering
584 -- of the loyalty selection screen.
585 IF p_action_rec.arc_incl_object_from <> 'CSCH' THEN
586 IF AMS_Utility_PVT.check_lookup_exists(
587 p_lookup_type => 'AMS_DM_SOURCE_TYPE',
588 p_lookup_code => p_action_rec.arc_incl_object_from
589 ) = FND_API.g_false
590 THEN
591 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
592 THEN
593 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_SRC_INVALID');
594 FND_MESSAGE.set_token('LIST_SOURCE', p_action_rec.arc_incl_object_from);
595 FND_MSG_PUB.add;
596 END IF;
597
598 x_return_status := FND_API.g_ret_sts_error;
599 END IF;
600 END IF;
601 END IF;
602 END IF;
603
604
605
606 -- check other lookup codes
607
608 END check_action_lookup_items;
609
610
611 -- Start of Comments
612 --
613 -- NAME
614 -- check_action_req_items
615 --
616 -- PURPOSE
617 -- This procedure is to check required parameters that satisfy caller needs
618 --
619 -- NOTES
620 --
621 --
622 -- HISTORY
623 -- 05/13/1999 tdonohoe created
624 -- 01/22/2001 vbhandar modified
625 -- check for action_used_by_id instead of list header id
626 -- check for arc_action_used_by
627 -- remove incl_object_name as required column
628 -- End of Comments
629 PROCEDURE check_action_req_items
630 ( p_action_rec IN action_rec_type,
631 x_return_status OUT NOCOPY VARCHAR2
632 ) IS
633
634 BEGIN
635 --Initialize API/Procedure return status to success
636 x_return_status := FND_API.G_RET_STS_SUCCESS;
637
638 IF (p_action_rec.arc_action_used_by IS NULL) THEN
639 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
640 FND_MESSAGE.Set_Name('AMS', 'AMS_ACTION_USEDBY_MISSING');
641 FND_MSG_PUB.Add;
642 END IF;
643 x_return_status := FND_API.G_RET_STS_ERROR;
644 return;
645 END IF;
646
647 IF (p_action_rec.action_used_by_id IS NULL) THEN
648 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
649 FND_MESSAGE.Set_Name('AMS', 'AMS_USEDBY_ID_MISSING');
650 FND_MSG_PUB.Add;
651 END IF;
652 x_return_status := FND_API.G_RET_STS_ERROR;
653 return;
654 END IF;
655
656 IF (p_action_rec.order_number IS NULL) THEN
657 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
658 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_ACT_ORDNO_MISSING');
659 FND_MSG_PUB.Add;
660 END IF;
661 x_return_status := FND_API.G_RET_STS_ERROR;
662 return;
663 END IF;
664
668 FND_MSG_PUB.Add;
665 IF (p_action_rec.list_action_type IS NULL) THEN
666 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
667 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_ACT_TYPE_MISSING');
669 END IF;
670 x_return_status := FND_API.G_RET_STS_ERROR;
671 return;
672 END IF;
673
674 -- IF(p_action_rec.incl_object_name IS NULL) THEN
675 -- IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
676 -- FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_ACT_SRC_NAME_MISSING');
677 -- FND_MSG_PUB.Add;
678 -- END IF;
679 -- x_return_status := FND_API.G_RET_STS_ERROR;
680 -- return;
681 -- END IF;
682
683 IF (p_action_rec.arc_incl_object_from IS NULL) THEN
684 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
685 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_ARC_INCLUDE_FROM');
686 FND_MSG_PUB.Add;
687 END IF;
688 x_return_status := FND_API.G_RET_STS_ERROR;
689 return;
690 END IF;
691
692 IF (p_action_rec.incl_object_id IS NULL) THEN
693 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
694 FND_MESSAGE.Set_Name('AMS', 'AMS_LIST_ARC_INCLUDE_FROM_ID');
695 FND_MSG_PUB.Add;
696 END IF;
697 x_return_status := FND_API.G_RET_STS_ERROR;
698 return;
699 END IF;
700
701
702 EXCEPTION
703 WHEN OTHERS THEN
704 NULL;
705
706 END check_action_req_items;
707
708
709
710 ---------------------------------------------------------------------
711 -- PROCEDURE
712 -- check_action_items
713 --
714 -- HISTORY
715 -- 10/14/99 tdonohoe Created.
716 ---------------------------------------------------------------------
717 PROCEDURE check_action_items(
718 p_action_rec IN action_rec_type,
719 p_validation_mode IN VARCHAR2 := JTF_PLSQL_API.g_create,
720 x_return_status OUT NOCOPY VARCHAR2
721 )
722 IS
723 BEGIN
724
725 check_action_req_items(
726 p_action_rec => p_action_rec,
727 x_return_status => x_return_status
728 );
729
730 IF x_return_status <> FND_API.g_ret_sts_success THEN
731 RETURN;
732 END IF;
733
734
735 check_action_lookup_items(
736 p_action_rec => p_action_rec,
737 x_return_status => x_return_status
738 );
739 IF x_return_status <> FND_API.g_ret_sts_success THEN
740 RETURN;
741 END IF;
742
743 check_action_fk_items(
744 p_action_rec => p_action_rec,
745 x_return_status => x_return_status
746 );
747
748 IF x_return_status <> FND_API.g_ret_sts_success THEN
749 RETURN;
750 END IF;
751
752 check_action_other_items(
753 p_action_rec => p_action_rec,
754 x_return_status => x_return_status
755 );
756
757 IF x_return_status <> FND_API.g_ret_sts_success THEN
758 RETURN;
759 END IF;
760
761 check_action_uk_items(
762 p_action_rec => p_action_rec,
763 p_validation_mode => p_validation_mode,
764 x_return_status => x_return_status
765 );
766
767 IF x_return_status <> FND_API.g_ret_sts_success THEN
768 RETURN;
769 END IF;
770
771
772
773 END check_action_items;
774
775
776 -- Start of Comments
777 ---------------------------------------------------------------------
778 -- PROCEDURE
779 -- Validate_ListAction
780 --
781 -- PURPOSE
782 -- Validate a List Action.
783 --
784 -- PARAMETERS
785 -- p_action_rec: the list action record to be validated
786 --
787 -- NOTES
788 -- 1. p_action_rec should be the complete list action record. There
789 -- should not be any FND_API.g_miss_char/num/date in it.
790 ----------------------------------------------------------------------
791 -- End Of Comments
792 PROCEDURE Validate_ListAction
793 ( p_api_version IN NUMBER,
794 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
795 p_validation_level IN NUMBER
796 := FND_API.G_VALID_LEVEL_FULL,
797 x_return_status OUT NOCOPY VARCHAR2,
798 x_msg_count OUT NOCOPY NUMBER,
799 x_msg_data OUT NOCOPY VARCHAR2,
800
801 p_action_rec IN action_rec_type
802 ) IS
803
804 l_api_name CONSTANT VARCHAR2(30) := 'Validate_ListAction';
805 l_api_version CONSTANT NUMBER := 1.0;
806
807 -- Status Local Variables
808 l_return_status VARCHAR2(1); -- Return value from procedures
809
810
811
812
813 BEGIN
814 -- Standard call to check for call compatibility.
815 IF NOT FND_API.Compatible_API_Call ( l_api_version,
819 THEN
816 p_api_version,
817 l_api_name,
818 G_PKG_NAME)
820 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
821 END IF;
822
823 -- Initialize message list if p_init_msg_list is set to TRUE.
824 IF FND_API.to_Boolean( p_init_msg_list ) THEN
825 FND_MSG_PUB.initialize;
826 END IF;
827
828 -- Debug Message
829 /* ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
830 THEN
831 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
832 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_ListAction_Record: Start', TRUE);
833 FND_MSG_PUB.Add;
834 END IF; */
835
836 IF (AMS_DEBUG_HIGH_ON) THEN
837 AMS_Utility_PVT.debug_message(l_api_name||': Start ');
838 END IF;
839
840
841
842 -- Initialize API return status to success
843 x_return_status := FND_API.G_RET_STS_SUCCESS;
844
845 ---------------------- validate ------------------------
846 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
847 check_action_items(
848 p_action_rec => p_action_rec,
849 p_validation_mode => JTF_PLSQL_API.g_create,
850 x_return_status => l_return_status
851 );
852
853 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
854 RAISE FND_API.g_exc_unexpected_error;
855 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
856 RAISE FND_API.g_exc_error;
857 END IF;
858 END IF;
859
860
861 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
862 check_action_record(
863 p_action_rec => p_action_rec,
864 p_complete_rec => NULL,
865 x_return_status => l_return_status
866 );
867
868 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
869 RAISE FND_API.g_exc_unexpected_error;
870 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
871 RAISE FND_API.g_exc_error;
872 END IF;
873 END IF;
874
875 IF(p_action_rec.DISTRIBUTION_PCT IS NOT NULL) THEN
876 IF (p_action_rec.DISTRIBUTION_PCT > 100 )THEN
877 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_DIST_PCT_INVALID');
878 FND_MSG_PUB.Add;
879 x_return_status := FND_API.G_RET_STS_ERROR;
880 RAISE FND_API.G_EXC_ERROR;
881 END IF;
882 IF (p_action_rec.DISTRIBUTION_PCT <= 0 )THEN
883 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACT_DIST_PCT_INVALID');
884 FND_MSG_PUB.Add;
885 x_return_status := FND_API.G_RET_STS_ERROR;
886 RAISE FND_API.G_EXC_ERROR;
887 END IF;
888 END IF;
889
890
891 -- Success Message
892 -- MMSG
893 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
894 THEN
895 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
896 -- bmuthukr changes for bug 5185128. Implemented changes as suggested in bug 5191606.
897 -- FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_ListAction_Record', TRUE);
898 FND_MESSAGE.Set_Token('ROW', 'AMS_ListHeader_PVT.Validate_ListAction_Record', FALSE);
899 FND_MSG_PUB.Add;
900 END IF;
901
902
903 /* ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
904 THEN
905 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
906 FND_MESSAGE.Set_Token('ROW',
907 'AMS_ListHeader_PVT.Validate_ListAction_Record: END', TRUE);
908 FND_MSG_PUB.Add;
909 END IF; */
910
911
912 IF (AMS_DEBUG_HIGH_ON) THEN
913 AMS_Utility_PVT.debug_message(l_api_name||': End ');
914 END IF;
915
916
917 -- Standard call to get message count AND IF count is 1, get message info.
918 FND_MSG_PUB.Count_AND_Get
919 ( p_count => x_msg_count,
920 p_data => x_msg_data,
921 p_encoded => FND_API.G_FALSE
922 );
923
924
925
926 EXCEPTION
927 WHEN FND_API.G_EXC_ERROR THEN
928 x_return_status := FND_API.G_RET_STS_ERROR ;
929
930 FND_MSG_PUB.Count_AND_Get
931 ( p_count => x_msg_count,
932 p_data => x_msg_data,
933 p_encoded => FND_API.G_FALSE
934 );
935
936
937 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
938 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
939 FND_MSG_PUB.Count_AND_Get
940 ( p_count => x_msg_count,
941 p_data => x_msg_data,
942 p_encoded => FND_API.G_FALSE
943 );
944
945 WHEN OTHERS THEN
946 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
947 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
948 THEN
949 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
950 END IF;
951
952 FND_MSG_PUB.Count_AND_Get
953 ( p_count => x_msg_count,
954 p_data => x_msg_data,
955 p_encoded => FND_API.G_FALSE
956 );
957
958 END Validate_ListAction;
959
963 --
960 ---------------------------------------------------------------------
961 -- PROCEDURE
962 -- Create_ListAction
964 -- PURPOSE
965 -- Create a new List Select Action.
966 --
967 -- PARAMETERS
968 -- p_action_rec: the new record to be inserted
969 -- x_action_id: return the campaign_id of the new campaign
970 --
971 -- NOTES
972 -- 1. object_version_number will be set to 1.
973 -- 2. If action_id is passed in, the uniqueness will be checked.
974 -- Raise exception in case of duplicates.
975 -- 3. If action_id is not passed in, generate a unique one from
976 -- the sequence.
977 -- 4. If a flag column is passed in, check if it is 'Y' or 'N'.
978 -- Raise exception for invalid flag.
979 -- 5. If a flag column is not passed in, default it to 'Y' or 'N'.
980 -- 6. Please don't pass in any FND_API.g_mess_char/num/date.
981 ---------------------------------------------------------------------
982 PROCEDURE Create_ListAction
983 ( p_api_version IN NUMBER,
984 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
985 p_commit IN VARCHAR2 := FND_API.G_FALSE,
986 p_validation_level IN NUMBER
987 := FND_API.G_VALID_LEVEL_FULL,
988 x_return_status OUT NOCOPY VARCHAR2,
989 x_msg_count OUT NOCOPY NUMBER,
990 x_msg_data OUT NOCOPY VARCHAR2,
991
992 p_action_rec IN action_rec_type,
993 x_action_id OUT NOCOPY NUMBER
994 ) IS
995
996 l_api_name CONSTANT VARCHAR2(30) := 'Create_ListAction';
997 l_api_version CONSTANT NUMBER := 1.0;
998
999 -- Status Local Variables
1000 l_return_status VARCHAR2(1); -- Return value from procedures
1001 l_action_rec action_rec_type := p_action_rec;
1002
1003 l_list_select_action_id AMS_LIST_SELECT_ACTIONS.list_select_action_id%TYPE;
1004 l_action_count number;
1005
1006 l_sqlerrm varchar2(600);
1007 l_sqlcode varchar2(100);
1008
1009 CURSOR c_action_seq IS
1010 SELECT ams_list_select_actions_s.NEXTVAL
1011 FROM DUAL;
1012
1013 CURSOR c_action_count(action_id IN NUMBER) IS
1014 SELECT COUNT(*)
1015 FROM ams_list_select_actions
1016 WHERE list_select_action_id = action_id;
1017
1018 BEGIN
1019
1020 -- Standard Start of API savepoint
1021 SAVEPOINT Create_ListAction_PVT;
1022
1023 -- Standard call to check for call compatibility.
1024 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1025 p_api_version,
1026 l_api_name,
1027 G_PKG_NAME)
1028 THEN
1029 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1030 END IF;
1031
1032 -- Initialize message list IF p_init_msg_list is set to TRUE.
1033 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1034 FND_MSG_PUB.initialize;
1035 END IF;
1036
1037 -- Debug Message
1038 /*ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1039 THEN
1040 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1041 FND_MESSAGE.Set_Token('ROW','AMS_ListAction_PVT.Create_ListAction: Start', TRUE);
1042 FND_MSG_PUB.Add;
1043 END IF; */
1044
1045 IF (AMS_DEBUG_HIGH_ON) THEN
1046 AMS_Utility_PVT.debug_message(l_api_name||': Start ');
1047 END IF;
1048
1049
1050 -- Initialize API return status to success
1051 x_return_status := FND_API.G_RET_STS_SUCCESS;
1052
1053 --
1054 -- API body
1055 --
1056
1057 -- Perform the database operation
1058
1059 IF l_action_rec.list_select_action_id IS NULL OR
1060 l_action_rec.list_select_action_id = FND_API.g_miss_num THEN
1061 LOOP
1062 OPEN c_action_seq;
1063 FETCH c_action_seq INTO l_action_rec.list_select_action_id;
1064 CLOSE c_action_seq;
1065
1066 OPEN c_action_count(l_action_rec.list_select_action_id);
1067 FETCH c_action_count INTO l_action_count;
1068 CLOSE c_action_count;
1069 EXIT WHEN l_action_count = 0;
1070 END LOOP;
1071 END IF;
1072
1073
1074 Validate_ListAction
1075 ( p_api_version => 1.0
1076 ,p_init_msg_list => p_init_msg_list
1077 ,p_validation_level => p_validation_level
1078 ,x_return_status => l_return_status
1079 ,x_msg_count => x_msg_count
1080 ,x_msg_data => x_msg_data
1081 ,p_action_rec => l_action_rec
1082 );
1083
1084
1085 -- If any errors happen abort API.
1086 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1087 RAISE FND_API.G_EXC_ERROR;
1091
1088 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1089 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1090 END IF;
1092
1093
1094
1095 INSERT into AMS_LIST_SELECT_ACTIONS
1096 (LIST_SELECT_ACTION_ID
1097 ,LAST_UPDATE_DATE
1098 ,LAST_UPDATED_BY
1099 ,CREATION_DATE
1100 ,CREATED_BY
1101 ,LAST_UPDATE_LOGIN
1102 ,OBJECT_VERSION_NUMBER
1103 ,ORDER_NUMBER
1104 ,LIST_ACTION_TYPE
1105 --,INCL_OBJECT_NAME
1106 ,ARC_INCL_OBJECT_FROM
1107 ,INCL_OBJECT_ID
1108 --,INCL_OBJECT_WB_SHEET
1109 --,INCL_OBJECT_WB_OWNER
1110 --,INCL_OBJECT_CELL_CODE
1111 ,RANK
1112 ,NO_OF_ROWS_AVAILABLE
1113 ,NO_OF_ROWS_REQUESTED
1114 ,NO_OF_ROWS_USED
1115 ,DISTRIBUTION_PCT
1116 ,RESULT_TEXT
1117 ,DESCRIPTION
1118 ,ARC_ACTION_USED_BY
1119 ,ACTION_USED_BY_ID
1120 , incl_control_group
1121 ,NO_OF_ROWS_TARGETED
1122 )
1123
1124 VALUES
1125 ( l_action_rec.list_select_action_id
1126
1127 -- standard who columns
1128 ,sysdate
1129 ,FND_GLOBAL.User_Id
1130 ,sysdate
1131 ,FND_GLOBAL.User_Id
1132 ,FND_GLOBAL.Conc_Login_Id
1133 ,1--object_version_number
1134 ,l_action_rec.order_number
1135 ,l_action_rec.list_action_type
1136 -- ,l_action_rec.incl_object_name
1137 ,l_action_rec.arc_incl_object_from
1138 ,l_action_rec.incl_object_id
1139 -- ,l_action_rec.incl_object_wb_sheet
1140 -- ,l_action_rec.incl_object_wb_owner
1141 -- ,l_action_rec.incl_object_cell_code
1142 ,l_action_rec.rank
1143 ,NVL(l_action_rec.no_of_rows_available,0)
1144 ,NVL(l_action_rec.no_of_rows_requested,0)
1145 ,NVL(l_action_rec.no_of_rows_used,0)
1146 ,l_action_rec.distribution_pct
1147 ,l_action_rec.result_text
1148 ,l_action_rec.description
1149 ,l_action_rec.arc_action_used_by
1150 ,l_action_rec.action_used_by_id
1151 ,l_action_rec.incl_control_group
1152 ,l_action_rec.no_of_rows_targeted
1153 );
1154
1155
1156 -- set OUT value
1157 x_action_id := l_action_rec.list_select_action_id;
1158
1159
1160 -- Added by nyostos on Oct 14, 2002
1161 -- Adding List Select Action record to a Model/Scoring Run data sets
1162 -- may INVALIDATE the Model if it has already been built or the Scoring
1163 -- Run if it has already run. Call the appropriate procedure to check.
1164 IF l_action_rec.arc_action_used_by = 'MODL' THEN
1165 AMS_DM_MODEL_PVT.handle_data_selection_changes(l_action_rec.action_used_by_id);
1166 ELSIF l_action_rec.arc_action_used_by = 'SCOR' THEN
1167 AMS_DM_SCORE_PVT.handle_data_selection_changes(l_action_rec.action_used_by_id);
1168 END IF;
1169 -- End of addition by nyostos.
1170
1171
1172 -- Standard check of p_commit.
1173 IF FND_API.To_Boolean ( p_commit )
1174 THEN
1175 COMMIT WORK;
1176 END IF;
1177
1178 -- Success Message
1179 -- MMSG
1180 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1181 THEN
1182 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
1183 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Create_ListAction', TRUE);
1184 FND_MSG_PUB.Add;
1185 END IF;
1186
1187 /*ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1188 THEN
1189 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1190 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Create_ListAction: END', TRUE);
1191 FND_MSG_PUB.Add;
1192 END IF; */
1193
1194
1195 -- Standard call to get message count AND IF count is 1, get message info.
1196 FND_MSG_PUB.Count_AND_Get
1197 ( p_count => x_msg_count,
1198 p_data => x_msg_data,
1199 p_encoded => FND_API.G_FALSE
1200 );
1201
1202 EXCEPTION
1203
1204 WHEN FND_API.G_EXC_ERROR THEN
1205
1206 ROLLBACK TO Create_ListAction_PVT;
1207 x_return_status := FND_API.G_RET_STS_ERROR ;
1208 l_sqlerrm := SQLERRM;
1209 l_sqlcode := SQLCODE;
1210
1211 FND_MSG_PUB.Count_AND_Get
1212 ( p_count => x_msg_count,
1213 p_data => x_msg_data,
1214 p_encoded => FND_API.G_FALSE
1215 );
1216
1217
1218 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1219
1220 ROLLBACK TO Create_ListAction_PVT;
1221 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1222 l_sqlerrm := SQLERRM;
1223 l_sqlcode := SQLCODE;
1224
1225 FND_MSG_PUB.Count_AND_Get
1226 ( p_count => x_msg_count,
1227 p_data => x_msg_data,
1228 p_encoded => FND_API.G_FALSE
1229 );
1230
1231 WHEN OTHERS THEN
1232
1233 ROLLBACK TO Create_ListAction_PVT;
1234 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1235 l_sqlerrm := SQLERRM;
1236 l_sqlcode := SQLCODE;
1237
1238 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1242
1239 THEN
1240 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1241 END IF;
1243 FND_MSG_PUB.Count_AND_Get
1244 ( p_count => x_msg_count,
1245 p_data => x_msg_data,
1246 p_encoded => FND_API.G_FALSE
1247 );
1248
1249 END Create_ListAction;
1250
1251 -- Start of Comments
1252 ---------------------------------------------------------------------
1253 -- PROCEDURE
1254 -- Update_ListAction
1255 --
1256 -- PURPOSE
1257 -- Update a List Action.
1258 --
1259 -- PARAMETERS
1260 -- p_action_rec: the record with new items
1261 --
1262 -- NOTES
1263 -- 1. Raise exception if the object_version_number doesn't match.
1264 -- 2. If an attribute is passed in as FND_API.g_miss_char/num/date,
1265 -- that column won't be updated.
1266 ----------------------------------------------------------------------
1267 -- End Of Comments
1268 PROCEDURE Update_ListAction
1269 ( p_api_version IN NUMBER,
1270 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1271 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1272 p_validation_level IN NUMBER
1273 := FND_API.G_VALID_LEVEL_FULL,
1274 x_return_status OUT NOCOPY VARCHAR2,
1275 x_msg_count OUT NOCOPY NUMBER,
1276 x_msg_data OUT NOCOPY VARCHAR2,
1277
1278 p_action_rec IN action_rec_type
1279 ) IS
1280
1281 l_api_name CONSTANT VARCHAR2(30) := 'Update_ListAction';
1282 l_api_version CONSTANT NUMBER := 1.0;
1283
1284 -- Status Local Variables
1285 l_return_status VARCHAR2(1); -- Return value from procedures
1286 l_action_rec action_rec_type := p_action_rec;
1287
1288 l_sqlerrm varchar2(600);
1289 l_sqlcode varchar2(100);
1290 l_rec_changed varchar2(1) := 'N';
1291
1292 Begin
1293
1294 -- Standard Start of API savepoint
1295 SAVEPOINT Update_ListAction_PVT;
1296
1297 -- Standard call to check for call compatibility.
1298 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1299 p_api_version,
1300 l_api_name,
1301 G_PKG_NAME)
1302 THEN
1303 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1304 END IF;
1305
1306 -- Initialize message list IF p_init_msg_list is set to TRUE.
1307 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1308 FND_MSG_PUB.initialize;
1309 END IF;
1310
1311 -- Debug Message
1312 /* ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1313 THEN
1314 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1315 FND_MESSAGE.Set_Token('ROW','AMS_ListAction_PVT.Update_ListAction: Start', TRUE);
1316 FND_MSG_PUB.Add;
1317 END IF; */
1318
1319
1320 IF (AMS_DEBUG_HIGH_ON) THEN
1321 AMS_Utility_PVT.debug_message(l_api_name||': Start ');
1322 END IF;
1323
1324
1325 init_action_rec(x_action_rec => l_action_rec);
1326 complete_action_rec(p_action_rec, l_action_rec);
1327
1328 -- Initialize API return status to success
1329 x_return_status := FND_API.G_RET_STS_SUCCESS;
1330 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_item THEN
1331 check_action_items(
1332 p_action_rec => l_action_rec,
1333 p_validation_mode => JTF_PLSQL_API.g_update,
1334 x_return_status => l_return_status);
1335
1336 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1337 RAISE FND_API.g_exc_unexpected_error;
1338 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1339 RAISE FND_API.g_exc_error;
1340 END IF;
1341 END IF;
1342
1343
1344 -- replace g_miss_char/num/date with current column values
1345
1346 IF p_validation_level >= JTF_PLSQL_API.g_valid_level_record THEN
1347 check_action_record(
1348 p_action_rec => l_action_rec,
1349 p_complete_rec => l_action_rec,
1350 x_return_status => l_return_status
1351 );
1352
1353 IF l_return_status = FND_API.g_ret_sts_unexp_error THEN
1354 RAISE FND_API.g_exc_unexpected_error;
1355 ELSIF l_return_status = FND_API.g_ret_sts_error THEN
1356 RAISE FND_API.g_exc_error;
1357 END IF;
1358 END IF;
1359
1360
1361 -- Perform the database operation
1362
1363 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1364 THEN
1365 -------------------------------------------
1366 -- choang 07-jan-2000
1367 -- Replace the dbms_output with create_log
1368 -- due to problem with adchkdrv.
1369 -------------------------------------------
1370 AMS_Utility_PVT.Create_Log (
1371 x_return_status => l_return_status,
1372 p_arc_log_used_by => 'LISTACTION',
1373 p_log_used_by_id => TO_CHAR(l_action_rec.list_select_action_id),
1377 END IF;
1374 p_msg_data => G_PKG_NAME || ' - Update ams_list_actions'
1375 );
1376
1378
1379 -- Added by nyostos on Oct 14, 2002
1380 -- Check if the record has changed before updating and set a flag
1381 -- that will be used later to make a callout to Model/Score code.
1382 IF l_action_rec.arc_action_used_by IN ('MODL', 'SCOR') THEN
1383 l_rec_changed := 'N';
1384 check_list_action_changes(l_action_rec, l_rec_changed);
1385 END IF;
1386
1387 UPDATE ams_list_select_actions
1388 SET
1389 last_update_date = sysdate
1390 ,last_updated_by = FND_GLOBAL.User_Id
1391 ,last_update_login = FND_GLOBAL.Conc_Login_Id
1392 ,object_version_number = l_action_rec.object_version_number + 1
1393 ,order_number = l_action_rec.order_number
1394 ,list_action_type = l_action_rec.list_action_type
1395 -- ,incl_object_name = l_action_rec.incl_object_name
1396 ,arc_incl_object_from = l_action_rec.arc_incl_object_from
1397 ,incl_object_id = l_action_rec.incl_object_id
1398 -- ,incl_object_wb_sheet = l_action_rec.incl_object_wb_sheet
1399 -- ,incl_object_wb_owner = l_action_rec.incl_object_wb_owner
1400 ,rank = l_action_rec.rank
1401 ,no_of_rows_available = l_action_rec.no_of_rows_available
1402 ,no_of_rows_requested = l_action_rec.no_of_rows_requested
1403 ,no_of_rows_used = l_action_rec.no_of_rows_used
1404 ,distribution_pct = l_action_rec.distribution_pct
1405 ,result_text = l_action_rec.result_text
1406 ,description = l_action_rec.description
1407 ,incl_control_group = l_action_rec.incl_control_group
1408 ,no_of_rows_targeted = l_action_rec.no_of_rows_targeted
1409 WHERE
1410 list_select_action_id = l_action_rec.list_select_action_id
1411 AND
1412 object_version_number = l_action_rec.object_version_number;
1413
1414
1415 IF (SQL%NOTFOUND) THEN
1416 -- Error, check the msg level and added an error message to the
1417 -- API message list
1418 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1419 THEN -- MMSG
1420 FND_MESSAGE.set_name('AMS', 'API_UNEXP_ERROR_IN_PROCESSING');
1421 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Update_ListAction API', TRUE);
1422 FND_MSG_PUB.Add;
1423 END IF;
1424
1425 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1426 END IF;
1427
1428 -- Added by nyostos on Oct 14, 2002
1429 -- If the Select Action record changed, Call the appropriate Model/Score
1430 -- procedure to check as this may may INVALIDATE the Model if it has
1431 -- already been built or the Scoring Run if it has already run.
1432 IF (l_action_rec.arc_action_used_by IN ('MODL', 'SCOR')) AND l_rec_changed = 'Y' THEN
1433
1434 IF l_action_rec.arc_action_used_by = 'MODL' THEN
1435 AMS_DM_MODEL_PVT.handle_data_selection_changes(l_action_rec.action_used_by_id);
1436 ELSIF l_action_rec.arc_action_used_by = 'SCOR' THEN
1437 AMS_DM_SCORE_PVT.handle_data_selection_changes(l_action_rec.action_used_by_id);
1438 END IF;
1439
1440 END IF;
1441 -- End of addition by nyostos.
1442
1443
1444 -- Standard check of p_commit.
1445 IF FND_API.To_Boolean ( p_commit )
1446 THEN
1447 COMMIT WORK;
1448 END IF;
1449
1450
1451
1452 -- Success Message
1453 -- MMSG
1454 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1455 THEN
1456 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
1457 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Update_ListAction', TRUE);
1458 FND_MSG_PUB.Add;
1459 END IF;
1460
1461
1462 /* ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1463 THEN
1464 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1465 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Update_ListAction: END', TRUE);
1466 FND_MSG_PUB.Add;
1467 END IF; */
1468
1469
1470 -- Standard call to get message count AND IF count is 1, get message info.
1471 FND_MSG_PUB.Count_AND_Get
1472 ( p_count => x_msg_count,
1473 p_data => x_msg_data,
1474 p_encoded => FND_API.G_FALSE
1475 );
1476
1477 EXCEPTION
1478
1479 WHEN FND_API.G_EXC_ERROR THEN
1480
1481 ROLLBACK TO Update_ListAction_PVT;
1482 x_return_status := FND_API.G_RET_STS_ERROR ;
1483 l_sqlerrm := SQLERRM;
1484 l_sqlcode := SQLCODE;
1485 --dbms_output.put_line('AMS_ListAction_PVT.Update_listaction:'||l_sqlerrm||l_sqlcode);
1486
1487 FND_MSG_PUB.Count_AND_Get
1488 ( p_count => x_msg_count,
1489 p_data => x_msg_data,
1490 p_encoded => FND_API.G_FALSE
1491 );
1492
1493
1494 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1495
1496 ROLLBACK TO Update_ListAction_PVT;
1497 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1498 l_sqlerrm := SQLERRM;
1499 l_sqlcode := SQLCODE;
1503 ( p_count => x_msg_count,
1500 --dbms_output.put_line('AMS_ListAction_PVT.Update_listaction:'||l_sqlerrm||l_sqlcode);
1501
1502 FND_MSG_PUB.Count_AND_Get
1504 p_data => x_msg_data,
1505 p_encoded => FND_API.G_FALSE
1506 );
1507
1508
1509 WHEN OTHERS THEN
1510
1511 ROLLBACK TO Update_ListAction_PVT;
1512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1513 l_sqlerrm := SQLERRM;
1514 l_sqlcode := SQLCODE;
1515 --dbms_output.put_line('AMS_ListAction_PVT.Update_listaction:'||l_sqlerrm||l_sqlcode);
1516
1517 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1518 THEN
1519 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1520 END IF;
1521
1522 FND_MSG_PUB.Count_AND_Get
1523 ( p_count => x_msg_count,
1524 p_data => x_msg_data,
1525 p_encoded => FND_API.G_FALSE
1526 );
1527
1528 End Update_ListAction;
1529
1530 -- Start of Comments
1531 --------------------------------------------------------------------
1532 -- PROCEDURE
1533 -- Delete_ListAction
1534 --
1535 -- PURPOSE
1536 -- Delete a List Action.
1537 --
1538 -- PARAMETERS
1539 -- p_action_id: the action_id
1540 -- p_object_version: the object_version_number
1541 --
1542 -- NOTES
1543 -- 1. Raise exception if the object_version_number doesn't match.
1544 --------------------------------------------------------------------
1545 -- End Of Comments
1546 PROCEDURE Delete_ListAction
1547 ( p_api_version IN NUMBER,
1548 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1549 p_commit IN VARCHAR2 := FND_API.G_FALSE,
1550 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1551
1552 x_return_status OUT NOCOPY VARCHAR2,
1553 x_msg_count OUT NOCOPY NUMBER,
1554 x_msg_data OUT NOCOPY VARCHAR2,
1555
1556 p_action_id IN NUMBER
1557 ) IS
1558
1559 l_api_name CONSTANT VARCHAR2(30) := 'Delete_ListAction';
1560 l_api_version CONSTANT NUMBER := 1.0;
1561
1562 -- Status Local Variables
1563 l_return_status VARCHAR2(1); -- Return value from procedures
1564 l_init_action_rec action_rec_type;
1565 l_complete_action_rec action_rec_type;
1566 l_init_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
1567 l_complete_list_header_rec AMS_ListHeader_PVT.list_header_rec_type;
1568 BEGIN
1569
1570 -- Standard Start of API savepoint
1571 SAVEPOINT Delete_ListAction_PVT;
1572
1573 -- Standard call to check for call compatibility.
1574 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1575 p_api_version,
1576 l_api_name,
1577 G_PKG_NAME)
1578 THEN
1579 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1580 END IF;
1581
1582
1583
1584 -- Initialize message list IF p_init_msg_list is set to TRUE.
1585 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1586 FND_MSG_PUB.initialize;
1587 END IF;
1588
1589 -- Debug Message
1590 /* ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1591 THEN
1592 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1593 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Delete_ListAction: Start', TRUE);
1594 FND_MSG_PUB.Add;
1595 END IF; */
1596
1597 IF (AMS_DEBUG_HIGH_ON) THEN
1598 AMS_Utility_PVT.debug_message(l_api_name||': Start ');
1599 END IF;
1600
1601
1602 -- Initialize API return status to success
1603 x_return_status := FND_API.G_RET_STS_SUCCESS;
1604
1605
1606 -- Perform the database operation
1607
1608 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW)
1609 THEN
1610 NULL;
1611 --dbms_output.put_line('AMS_List_SelectAction_PVT - DELETE FROM ams_list_select_actions');
1612 END IF;
1613
1614 /* Delete from ams_list_entries
1615 where list_select_action_id = p_action_id;
1616 */
1617
1618
1619 init_action_rec(x_action_rec => l_init_action_rec);
1620 l_init_action_rec.list_select_action_id := p_action_id;
1621
1622 -- replace g_miss_char/num/date with current column values
1623 complete_action_rec(l_init_action_rec, l_complete_action_rec);
1624
1625 IF (l_complete_action_rec.arc_action_used_by='LIST')
1626 THEN
1627
1628 AMS_ListHeader_PVT.Init_ListHeader_rec(x_listheader_rec => l_init_list_header_rec);
1629 l_init_list_header_rec.list_header_id:=l_complete_action_rec.action_used_by_id;
1630
1631 -- replace g_miss_char/num/date with current column values
1635 -- donot allow delete if list header status is generating,locked,archived,expired
1632 AMS_ListHeader_PVT.Complete_ListHeader_rec(l_init_list_header_rec, l_complete_list_header_rec);
1633
1634 -- allow delete only if list header status is Draft/ Scheduled/Available/Cancelled
1636 -- call reset_status in listheader pvt this will cancel the workflow and change the staus to draft
1637
1638 IF (l_complete_list_header_rec.status_code='CANCELLED') OR
1639 (l_complete_list_header_rec.status_code='DRAFT') OR
1640 (l_complete_list_header_rec.status_code='AVAILABLE')OR
1641 (l_complete_list_header_rec.status_code='SCHEDULED')
1642 THEN
1643 DELETE FROM ams_list_select_actions
1644 WHERE list_select_action_id = p_action_id;
1645
1646 -- AMS_ListHeader_PVT.Reset_Status(l_complete_list_header_rec.action_used_by_id,)
1647
1648 ELSIF(l_complete_list_header_rec.status_code='GENERATING') OR
1649 (l_complete_list_header_rec.status_code='LOCKED') OR
1650 (l_complete_list_header_rec.status_code='ARCHIVED') OR
1651 (l_complete_list_header_rec.status_code='EXPIRED')
1652 THEN
1653 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error)
1654 THEN
1655 FND_MESSAGE.set_name('AMS', 'AMS_LIST_ACTION_NO_DEL');
1656 FND_MSG_PUB.add;
1657 END IF;
1658
1659 x_return_status := FND_API.g_ret_sts_error;
1660 RAISE FND_API.G_EXC_ERROR;
1661 -- RETURN;
1662 END IF;
1663
1664 ELSIF (l_complete_action_rec.arc_action_used_by='MODL')
1665 THEN
1666
1667 DELETE FROM ams_list_select_actions
1668 WHERE list_select_action_id = p_action_id;
1669
1670 -- Added on Oct 14, 2002 by nyostos
1671 -- call the plugin to update the model status
1672 AMS_DM_MODEL_PVT.handle_data_selection_changes(l_complete_action_rec.action_used_by_id);
1673
1674 ELSIF (l_complete_action_rec.arc_action_used_by='SCOR')
1675 THEN
1676 /* call the plugin to update the model status ??*/
1677 DELETE FROM ams_list_select_actions
1678 WHERE list_select_action_id = p_action_id;
1679
1680 -- Added on Oct 14, 2002 by nyostos
1681 -- call the plugin to update the model status
1682 AMS_DM_SCORE_PVT.handle_data_selection_changes(l_complete_action_rec.action_used_by_id);
1683
1684 END IF; --l_complete_action_rec.arc_action_used_by='LIST'
1685
1686 --
1687 -- END of API body.
1688 --
1689
1690 -- Standard check of p_commit.
1691 IF FND_API.To_Boolean ( p_commit )
1692 THEN
1693 COMMIT WORK;
1694 END IF;
1695
1696 -- Success Message
1697 -- MMSG
1698 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1699 THEN
1700 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
1701 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Delete_ListAction', TRUE);
1702 FND_MSG_PUB.Add;
1703 END IF;
1704
1705
1706 /* ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1707 THEN
1708 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1709 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Delete_ListAction: END', TRUE);
1710 FND_MSG_PUB.Add;
1711 END IF; */
1712
1713
1714 -- Standard call to get message count AND IF count is 1, get message info.
1715 FND_MSG_PUB.Count_AND_Get
1716 ( p_count => x_msg_count,
1717 p_data => x_msg_data,
1718 p_encoded => FND_API.G_FALSE
1719 );
1720
1721 EXCEPTION
1722
1723 WHEN FND_API.G_EXC_ERROR THEN
1724
1725 ROLLBACK TO Delete_ListAction_PVT;
1726 x_return_status := FND_API.G_RET_STS_ERROR ;
1727
1728 FND_MSG_PUB.Count_AND_Get
1729 ( p_count => x_msg_count,
1730 p_data => x_msg_data,
1731 p_encoded => FND_API.G_FALSE
1732 );
1733
1734 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1735
1736 ROLLBACK TO Delete_ListAction_PVT;
1737 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1738
1739 FND_MSG_PUB.Count_AND_Get
1740 ( p_count => x_msg_count,
1741 p_data => x_msg_data,
1742 p_encoded => FND_API.G_FALSE
1743 );
1744
1745
1746 WHEN OTHERS THEN
1747
1748 ROLLBACK TO Delete_ListAction_PVT;
1749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1750
1751 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1752 THEN
1753 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1754 END IF;
1755
1756 FND_MSG_PUB.Count_AND_Get
1757 ( p_count => x_msg_count,
1758 p_data => x_msg_data,
1759 p_encoded => FND_API.G_FALSE
1763 END Delete_ListAction;
1760 );
1761
1762
1764
1765 -- Start of Comments
1766 -------------------------------------------------------------------
1767 -- PROCEDURE
1768 -- Lock_ListAction
1769 --
1770 -- PURPOSE
1771 -- Lock a List Action.
1772 --
1773 -- PARAMETERS
1774 -- p_action_id: the action_id
1775 -- p_object_version: the object_version_number
1776 --
1777 -- NOTES
1778 -- 1. Raise exception if the object_version_number doesn't match.
1779 --------------------------------------------------------------------
1780 -- End Of Comments
1781 PROCEDURE Lock_ListAction
1782 ( p_api_version IN NUMBER,
1783 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1784 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
1785
1786 x_return_status OUT NOCOPY VARCHAR2,
1787 x_msg_count OUT NOCOPY NUMBER,
1788 x_msg_data OUT NOCOPY VARCHAR2,
1789
1790 p_action_id IN NUMBER,
1791 p_object_version IN NUMBER
1792 ) IS
1793
1794
1795 l_api_name CONSTANT VARCHAR2(30) := 'Lock_ListAction';
1796 l_api_version CONSTANT NUMBER := 1.0;
1797
1798 -- Status Local Variables
1799 l_return_status VARCHAR2(1); -- Return value from procedures
1800
1801 CURSOR c_list_actions IS
1802 SELECT list_select_action_id
1803 FROM ams_list_select_actions
1804 WHERE list_select_action_id = p_action_id
1805 AND object_version_number = p_object_version
1806 FOR UPDATE OF list_select_action_id NOWAIT;
1807
1808 l_list_select_action_id number;
1809
1810 BEGIN
1811
1812
1813 -- Standard call to check for call compatibility.
1814 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1815 p_api_version,
1816 l_api_name,
1817 G_PKG_NAME)
1818 THEN
1819 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1820 END IF;
1821
1822
1823
1824 -- Initialize message list if p_init_msg_list is set to TRUE.
1825 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1826 FND_MSG_PUB.initialize;
1827 END IF;
1828
1829
1830 -- Debug Message
1831 /* ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1832 THEN
1833 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1834 FND_MESSAGE.Set_Token('ROW', 'AMS_ListAction_PVT.Lock_ListAction: Start', TRUE);
1835 FND_MSG_PUB.Add;
1836 END IF; */
1837
1838 IF (AMS_DEBUG_HIGH_ON) THEN
1839 AMS_Utility_PVT.debug_message(l_api_name||': Start ');
1840 END IF;
1841
1842 -- Initialize API return status to success
1843 x_return_status := FND_API.G_RET_STS_SUCCESS;
1844
1845 OPEN c_list_actions;
1846 FETCH c_list_actions INTO l_list_select_action_id;
1847 IF (c_list_actions%NOTFOUND) THEN
1848 CLOSE c_list_actions;
1849 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
1850 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
1851 FND_MSG_PUB.add;
1852 END IF;
1853 RAISE FND_API.g_exc_error;
1854 END IF;
1855 CLOSE c_list_actions;
1856
1857
1858 -- Success Message
1859 -- MMSG
1860 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1861 THEN
1862 FND_MESSAGE.Set_Name('AMS', 'API_SUCCESS');
1863 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Lock_ListAction', TRUE);
1864 FND_MSG_PUB.Add;
1865 END IF;
1866
1867
1868 /* ckapoor IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH)
1869 THEN
1870 FND_MESSAGE.set_name('AMS', 'API_DEBUG_MESSAGE');
1871 FND_MESSAGE.Set_Token('ROW', 'AMS_listheader_PVT.Lock_ListAction: END', TRUE);
1872 FND_MSG_PUB.Add;
1873 END IF; */
1874
1875 -- Standard call to get message count AND IF count is 1, get message info.
1876 FND_MSG_PUB.Count_AND_Get
1877 ( p_count => x_msg_count,
1878 p_data => x_msg_data,
1879 p_encoded => FND_API.G_FALSE
1880 );
1881
1882
1883
1884 EXCEPTION
1885
1886 WHEN FND_API.G_EXC_ERROR THEN
1887
1888 x_return_status := FND_API.G_RET_STS_ERROR ;
1889
1890 FND_MSG_PUB.Count_AND_Get
1891 ( p_count => x_msg_count,
1892 p_data => x_msg_data,
1893 p_encoded => FND_API.G_FALSE
1894 );
1895
1896
1897 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1898
1899 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1900
1901 FND_MSG_PUB.Count_AND_Get
1905 );
1902 ( p_count => x_msg_count,
1903 p_data => x_msg_data,
1904 p_encoded => FND_API.G_FALSE
1906
1907 WHEN AMS_Utility_PVT.RESOURCE_LOCKED THEN
1908
1909 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1910
1911 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
1912 THEN -- MMSG
1913 FND_MESSAGE.SET_NAME('AMS','API_RESOURCE_LOCKED');
1914 FND_MSG_PUB.Add;
1915 END IF;
1916
1917 FND_MSG_PUB.Count_AND_Get
1918 ( p_count => x_msg_count,
1919 p_data => x_msg_data,
1920 p_encoded => FND_API.G_FALSE
1921 );
1922
1923 WHEN OTHERS THEN
1924
1925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1926
1927 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
1928 THEN
1929 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1930 END IF;
1931
1932 FND_MSG_PUB.Count_AND_Get
1933 ( p_count => x_msg_count,
1934 p_data => x_msg_data,
1935 p_encoded => FND_API.G_FALSE
1936 );
1937
1938 END Lock_ListAction;
1939
1940 ---------------------------------------------------------------------
1941 -- PROCEDURE
1942 -- init_action_rec
1943 --
1944 -- HISTORY
1945 -- 10/01/99 tdonohoe Created.
1946 ---------------------------------------------------------------------
1947 PROCEDURE init_action_rec(
1948 x_action_rec OUT NOCOPY action_rec_type
1949 )
1950 IS
1951 BEGIN
1952
1953 x_action_rec.list_select_action_id := FND_API.g_miss_num;
1954 x_action_rec.last_update_date := FND_API.g_miss_date;
1955 x_action_rec.last_updated_by := FND_API.g_miss_num;
1956 x_action_rec.creation_date := FND_API.g_miss_date;
1957 x_action_rec.created_by := FND_API.g_miss_num;
1958 x_action_rec.last_update_login := FND_API.g_miss_num;
1959 x_action_rec.object_version_number := FND_API.g_miss_num;
1960 x_action_rec.order_number := FND_API.g_miss_num;
1961 x_action_rec.list_action_type := FND_API.g_miss_char;
1962 x_action_rec.arc_incl_object_from := FND_API.g_miss_char;
1963 x_action_rec.incl_object_id := FND_API.g_miss_num;
1964 /* x_action_rec.INCL_OBJECT_NAME := FND_API.g_miss_char;
1965 x_action_rec.INCL_OBJECT_WB_SHEET := FND_API.g_miss_char;
1966 x_action_rec.INCL_OBJECT_WB_OWNER := FND_API.g_miss_num;
1967 x_action_rec.INCL_OBJECT_CELL_CODE := FND_API.g_miss_char;
1968 */
1969 x_action_rec.rank := FND_API.g_miss_num;
1970 x_action_rec.no_of_rows_available := FND_API.g_miss_num;
1971 x_action_rec.no_of_rows_requested := FND_API.g_miss_num;
1972 x_action_rec.no_of_rows_used := FND_API.g_miss_num;
1973 x_action_rec.distribution_pct := FND_API.g_miss_num;
1974 x_action_rec.result_text := FND_API.g_miss_char;
1975 x_action_rec.description := FND_API.g_miss_char;
1976 x_action_rec.arc_action_used_by := FND_API.g_miss_char;
1977 x_action_rec.action_used_by_id := FND_API.g_miss_num;
1978 x_action_rec.incl_control_group := FND_API.g_miss_char;
1979 x_action_rec.no_of_rows_targeted := FND_API.g_miss_num;
1980
1981
1982 End Init_Action_Rec;
1983
1984 ---------------------------------------------------------------------
1985 -- PROCEDURE
1986 -- complete_action_rec
1987 --
1988 -- HISTORY
1989 -- 10/01/99 tdonohoe Created.
1990 ---------------------------------------------------------------------
1991 PROCEDURE complete_action_rec(
1992 p_action_rec IN action_rec_type,
1993 x_complete_rec OUT NOCOPY action_rec_type
1994 )
1995 IS
1996
1997 CURSOR c_action IS
1998 SELECT *
1999 FROM ams_list_select_actions
2000 WHERE list_select_action_id = p_action_rec.list_select_action_id;
2001
2002 l_action_rec c_action%ROWTYPE;
2003
2004 BEGIN
2005
2006 x_complete_rec := p_action_rec;
2007
2008 OPEN c_action;
2009 FETCH c_action INTO l_action_rec;
2010 IF c_action%NOTFOUND THEN
2011 CLOSE c_action;
2012 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2013 FND_MESSAGE.set_name('AMS', 'AMS_API_RECORD_NOT_FOUND');
2014 FND_MSG_PUB.add;
2015 END IF;
2016 RAISE FND_API.g_exc_error;
2017 END IF;
2018 CLOSE c_action;
2019
2020
2021 IF p_action_rec.list_select_action_id = FND_API.g_miss_num THEN
2022 x_complete_rec.list_select_action_id := l_action_rec.list_select_action_id;
2023 END IF;
2024
2025 IF p_action_rec.last_update_date = FND_API.g_miss_date THEN
2026 x_complete_rec.last_update_date := l_action_rec.last_update_date;
2027 END IF;
2028
2029 IF p_action_rec.last_updated_by = FND_API.g_miss_num THEN
2030 x_complete_rec.last_updated_by := l_action_rec.last_updated_by;
2031 END IF;
2032
2033 IF p_action_rec.creation_date = FND_API.g_miss_date THEN
2034 x_complete_rec.creation_date := l_action_rec.creation_date;
2038 x_complete_rec.created_by := l_action_rec.created_by;
2035 END IF;
2036
2037 IF p_action_rec.created_by = FND_API.g_miss_num THEN
2039 END IF;
2040
2041 IF p_action_rec.last_update_login = FND_API.g_miss_num THEN
2042 x_complete_rec.last_update_login := l_action_rec.last_update_login;
2043 END IF;
2044
2045 IF p_action_rec.object_version_number = FND_API.g_miss_num THEN
2046 x_complete_rec.object_version_number := l_action_rec.object_version_number;
2047 END IF;
2048
2049
2050 IF p_action_rec.order_number = FND_API.g_miss_num THEN
2051 x_complete_rec.order_number := l_action_rec.order_number;
2052 END IF;
2053
2054 IF p_action_rec.list_action_type = FND_API.g_miss_char THEN
2055 x_complete_rec.list_action_type := l_action_rec.list_action_type;
2056 END IF;
2057
2058
2059 IF p_action_rec.arc_incl_object_from = FND_API.g_miss_char THEN
2060 x_complete_rec.arc_incl_object_from := l_action_rec.arc_incl_object_from;
2061 END IF;
2062
2063 IF p_action_rec.incl_object_id = FND_API.g_miss_num THEN
2064 x_complete_rec.incl_object_id := l_action_rec.incl_object_id;
2065 END IF;
2066
2067 /*
2068 IF p_action_rec.INCL_OBJECT_NAME = FND_API.g_miss_char THEN
2069 x_complete_rec.INCL_OBJECT_NAME := l_action_rec.INCL_OBJECT_NAME;
2070 END IF;
2071
2072 IF p_action_rec.INCL_OBJECT_WB_SHEET = FND_API.g_miss_char THEN
2073 x_complete_rec.INCL_OBJECT_WB_SHEET := l_action_rec.INCL_OBJECT_WB_SHEET;
2074 END IF;
2075
2076 IF p_action_rec.INCL_OBJECT_WB_OWNER = FND_API.g_miss_num THEN
2077 x_complete_rec.INCL_OBJECT_WB_OWNER := l_action_rec.INCL_OBJECT_WB_OWNER;
2078 END IF;
2079
2080 IF p_action_rec.INCL_OBJECT_CELL_CODE = FND_API.g_miss_char THEN
2081 x_complete_rec.INCL_OBJECT_CELL_CODE := l_action_rec.INCL_OBJECT_CELL_CODE;
2082 END IF;
2083 */
2084 IF p_action_rec.rank = FND_API.g_miss_num THEN
2085 x_complete_rec.rank := l_action_rec.rank ;
2086 END IF;
2087
2088 IF p_action_rec.no_of_rows_available = FND_API.g_miss_num THEN
2089 x_complete_rec.no_of_rows_available := l_action_rec.no_of_rows_available;
2090 END IF;
2091
2092 IF p_action_rec.no_of_rows_requested = FND_API.g_miss_num THEN
2093 x_complete_rec.no_of_rows_requested := l_action_rec.no_of_rows_requested;
2094 END IF;
2095
2096 IF p_action_rec.no_of_rows_used = FND_API.g_miss_num THEN
2097 x_complete_rec.no_of_rows_used := l_action_rec.no_of_rows_used;
2098 END IF;
2099
2100 IF p_action_rec.distribution_pct = FND_API.g_miss_num THEN
2101 x_complete_rec.distribution_pct := l_action_rec.distribution_pct;
2102 END IF;
2103
2104 IF p_action_rec.result_text = FND_API.g_miss_char THEN
2105 x_complete_rec.result_text := l_action_rec.result_text;
2106 END IF;
2107
2108 IF p_action_rec.description = FND_API.g_miss_char THEN
2109 x_complete_rec.description := l_action_rec.description;
2110 END IF;
2111
2112
2113 IF p_action_rec.arc_action_used_by = FND_API.g_miss_char THEN
2114 x_complete_rec.arc_action_used_by := l_action_rec.arc_action_used_by;
2115 END IF;
2116
2117 IF p_action_rec.action_used_by_id = FND_API.g_miss_num THEN
2118 x_complete_rec.action_used_by_id := l_action_rec.action_used_by_id;
2119 END IF;
2120
2121 IF p_action_rec.incl_control_group= FND_API.g_miss_char THEN
2122 x_complete_rec.incl_control_group:= l_action_rec.incl_control_group;
2123 END IF;
2124 IF p_action_rec.no_of_rows_targeted = FND_API.g_miss_num THEN
2125 x_complete_rec.no_of_rows_targeted := l_action_rec.no_of_rows_targeted;
2126 END IF;
2127
2128
2129 END complete_action_rec;
2130
2131 ---------------------------------------------------------------------
2132 -- PROCEDURE
2133 -- check_list_action_changes
2134 --
2135 -- HISTORY
2136 -- 14-Oct-2002 nyostos Check if the list select action has changed
2137 -- and then make a callout to Model/Score code
2138 -- to Invalidate if appropraite.
2139 ---------------------------------------------------------------------
2140 PROCEDURE check_list_action_changes(
2141 p_action_rec IN action_rec_type,
2142 x_rec_changed OUT NOCOPY VARCHAR2
2143 )
2144 IS
2145 CURSOR c_action IS
2146 SELECT *
2147 FROM ams_list_select_actions
2148 WHERE list_select_action_id = p_action_rec.list_select_action_id;
2149
2150 l_ref_action c_action%ROWTYPE;
2151
2152 BEGIN
2153
2154 -- Initialize record changed flag to 'N'
2155 x_rec_changed := 'N';
2156
2157 -- Open cursor to get the reference action record.
2158 OPEN c_action;
2159 FETCH c_action INTO l_ref_action;
2160 CLOSE c_action;
2161
2162 -- order_number
2163 IF (l_ref_action.order_number IS NULL AND p_action_rec.order_number IS NOT NULL) OR
2164 (l_ref_action.order_number <> p_action_rec.order_number) THEN
2165 x_rec_changed := 'Y';
2166 RETURN;
2167 END IF;
2168
2169 -- list_action_type
2170 IF (l_ref_action.list_action_type IS NULL AND p_action_rec.list_action_type IS NOT NULL) OR
2171 (l_ref_action.list_action_type <> p_action_rec.list_action_type) THEN
2172 x_rec_changed := 'Y';
2173 RETURN;
2174 END IF;
2175
2176 -- arc_incl_object_from
2177 IF (l_ref_action.arc_incl_object_from IS NULL AND p_action_rec.arc_incl_object_from IS NOT NULL) OR
2178 (l_ref_action.arc_incl_object_from <> p_action_rec.arc_incl_object_from) THEN
2179 x_rec_changed := 'Y';
2180 RETURN;
2181 END IF;
2182
2183 -- incl_object_id
2184 IF (l_ref_action.incl_object_id IS NULL AND p_action_rec.incl_object_id IS NOT NULL) OR
2185 (l_ref_action.incl_object_id <> p_action_rec.incl_object_id) THEN
2186 x_rec_changed := 'Y';
2187 RETURN;
2188 END IF;
2189
2190 END check_list_action_changes;
2191
2192 END AMS_ListAction_PVT;