[Home] [Help]
PACKAGE BODY: APPS.WMS_RESTRICTION_FORM_PKG
Source
1 package body wms_restriction_form_pkg AS
2 /* $Header: WMSFPREB.pls 120.1 2005/06/20 05:15:29 appldev ship $ */
3
4 g_pkg_name constant varchar2(30) := 'WMSRestriction_Form_PKG';
5
6 -- private function
7 -- return true if no existing restriction has the same id as the input
8 FUNCTION check_existence
9 (
10 p_rule_id IN NUMBER
11 ,p_sequence_number IN NUMBER
12 ) RETURN BOOLEAN IS
13 CURSOR c IS SELECT rule_id FROM wms_restrictions
14 WHERE rule_id = p_rule_id
15 AND sequence_number = p_sequence_number;
16 l_exist BOOLEAN;
17 l_rule_id NUMBER;
18 BEGIN
19 OPEN c;
20 FETCH c INTO l_rule_id;
21 l_exist := NOT(c%notfound);
22 CLOSE c;
23 RETURN l_exist;
24 END check_existence;
25
26 -- private validation routine
27 PROCEDURE validate_input
28 (
29 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
30 ,p_action IN VARCHAR2
31 ,p_rule_id IN NUMBER
32 ,p_sequence_number IN NUMBER
33 ,p_parameter_id IN NUMBER
34 ,p_operator_code IN NUMBER
35 ,p_operand_type_code IN NUMBER
36 ,p_operand_constant_number IN NUMBER
37 ,p_operand_constant_character IN VARCHAR2
38 ,p_operand_constant_date IN DATE
39 ,p_operand_parameter_id IN NUMBER
40 ,p_operand_expression IN VARCHAR2
41 ,p_operand_flex_value_set_id IN NUMBER
42 ,p_logical_operator_code IN NUMBER
43 ,p_bracket_open IN VARCHAR2
44 ,p_bracket_close IN VARCHAR2
45 ,p_attribute_category IN VARCHAR2
46 ,p_attribute1 IN VARCHAR2
47 ,p_attribute2 IN VARCHAR2
48 ,p_attribute3 IN VARCHAR2
49 ,p_attribute4 IN VARCHAR2
50 ,p_attribute5 IN VARCHAR2
51 ,p_attribute6 IN VARCHAR2
52 ,p_attribute7 IN VARCHAR2
53 ,p_attribute8 IN VARCHAR2
54 ,p_attribute9 IN VARCHAR2
55 ,p_attribute10 IN VARCHAR2
56 ,p_attribute11 IN VARCHAR2
57 ,p_attribute12 IN VARCHAR2
58 ,p_attribute13 IN VARCHAR2
59 ,p_attribute14 IN VARCHAR2
60 ,p_attribute15 IN VARCHAR2
61 ) IS
62 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
63 l_found BOOLEAN;
64 l_msg_count NUMBER ;
65 l_msg_data VARCHAR2(240);
66 BEGIN
67
68 IF p_action NOT IN ('INSERT', 'UPDATE', 'LOCK', 'DELETE') THEN
69 -- unknown exception
70 RAISE fnd_api.g_exc_unexpected_error;
71 END IF;
72
73 /* check existence */
74 IF check_existence(p_rule_id, p_sequence_number) THEN
75 IF p_action = 'INSERT' THEN
76 fnd_message.set_name('WMS', 'WMS_RESTRICTION_EXISTS');
77 fnd_msg_pub.ADD;
78 RAISE fnd_api.g_exc_error;
79 END IF;
80 ELSE
81 IF p_action IN ('DELETE','LOCK','UPDATE') THEN
82 fnd_message.set_name('WMS', 'WMS_RESTRICTION_NOT_FOUND');
83 fnd_msg_pub.ADD;
84 RAISE fnd_api.g_exc_error;
85 END IF;
86 END IF;
87
88 /* more input validation update and insert */
89 IF p_action IN ('UPDATE', 'INSERT') THEN
90 /* check foreign keys */
91 wms_rule_form_pkg.find_rule
92 (
93 p_api_version => 1.0
94 ,p_init_msg_list => fnd_api.g_false
95 ,x_return_status => l_return_status
96 ,x_msg_count => l_msg_count
97 ,x_msg_data => l_msg_data
98 ,p_rule_id => p_rule_id
99 ,x_found => l_found
100 );
101
102 if x_return_status = fnd_api.g_ret_sts_unexp_error then
103 raise fnd_api.g_exc_unexpected_error;
104 elsif x_return_status = fnd_api.g_ret_sts_error then
105 raise fnd_api.g_exc_error;
106 end if;
107
108 IF l_found = FALSE THEN
109 fnd_message.set_name('WMS', 'WMS_RULE_NOT_FOUND');
110 fnd_msg_pub.ADD;
111 RAISE fnd_api.g_exc_error;
112 END IF;
113
114 /* validate p_parameter_id */
115 -- wms_parameter_form_pkg.find_parameter(
116 -- p_api_version => 1.0
117 -- ,p_init_msg_list => fnd_api.g_false
118 -- ,x_return_status => l_return_status
119 -- ,x_msg_count => l_msg_count
120 -- ,x_msg_data => l_msg_data
121 -- ,p_parameter_id => p_parameter_id
122 -- ,x_found => l_found
123 -- );
124 -- if x_return_status = fnd_api.g_ret_sts_unexp_error then
125 -- raise fnd_api.g_exc_unexpected_error;
126 -- elsif x_return_status = fnd_api.g_ret_sts_error then
127 -- raise fnd_api.g_exc_error;
128 -- end if;
129
130 -- IF l_found = FALSE THEN
131 -- fnd_message.set_name('WMS', 'WMS_PARAMETER_NOT_FOUND');
132 -- fnd_msg_pub.ADD;
133 -- RAISE fnd_api.g_exc_error;
134 -- END IF;
135
136 -- other input parameters are not validated currently
137 END IF;
138
139 x_return_status := l_return_status;
140
141 EXCEPTION
142 WHEN fnd_api.g_exc_error THEN
143 x_return_status := fnd_api.g_ret_sts_error;
144
145 WHEN fnd_api.g_exc_unexpected_error THEN
146 x_return_status := fnd_api.g_ret_sts_unexp_error;
147
148 WHEN OTHERS THEN
149 x_return_status := fnd_api.g_ret_sts_unexp_error;
150
151 END validate_input;
152
153 procedure insert_restriction
154 (
155 p_api_version IN NUMBER
156 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
157 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
158 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
159 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
160 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
161 ,p_rowid IN OUT NOCOPY /* file.sql.39 change */ VARCHAR2
162 ,p_rule_id IN NUMBER
163 ,p_sequence_number IN NUMBER
164 ,p_parameter_id IN NUMBER
165 ,p_operator_code IN NUMBER
166 ,p_operand_type_code IN NUMBER
167 ,p_operand_constant_number IN NUMBER
168 ,p_operand_constant_character IN VARCHAR2
169 ,p_operand_constant_date IN DATE
170 ,p_operand_parameter_id IN NUMBER
171 ,p_operand_expression IN VARCHAR2
172 ,p_operand_flex_value_set_id IN NUMBER
173 ,p_logical_operator_code IN NUMBER
174 ,p_bracket_open IN VARCHAR2
175 ,p_bracket_close IN VARCHAR2
176 ,p_attribute_category IN VARCHAR2
177 ,p_attribute1 IN VARCHAR2
178 ,p_attribute2 IN VARCHAR2
179 ,p_attribute3 IN VARCHAR2
180 ,p_attribute4 IN VARCHAR2
181 ,p_attribute5 IN VARCHAR2
182 ,p_attribute6 IN VARCHAR2
183 ,p_attribute7 IN VARCHAR2
184 ,p_attribute8 IN VARCHAR2
185 ,p_attribute9 IN VARCHAR2
186 ,p_attribute10 IN VARCHAR2
187 ,p_attribute11 IN VARCHAR2
188 ,p_attribute12 IN VARCHAR2
189 ,p_attribute13 IN VARCHAR2
190 ,p_attribute14 IN VARCHAR2
191 ,p_attribute15 IN VARCHAR2
192 ) IS
193 -- API standard variables
194 l_api_version constant number := 1.0;
195 l_api_name constant varchar2(30) := 'Insert_Restriction';
196 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
197 l_row_id VARCHAR2(20);
198 l_date DATE;
199 l_user_id NUMBER;
200 l_login_id NUMBER;
201 l_found BOOLEAN;
202 begin
203
204 -- Standard call to check for call compatibility
205 if not fnd_api.compatible_api_call( l_api_version
206 ,p_api_version
207 ,l_api_name
208 ,g_pkg_name ) then
209 raise fnd_api.g_exc_unexpected_error;
210 end if;
211
212 -- Initialize message list if p_init_msg_list is set to TRUE
213 if fnd_api.to_boolean( p_init_msg_list ) then
214 fnd_msg_pub.initialize;
215 end if;
216
217 /* add validation here */
218 /* get who column information */
219 SELECT Sysdate INTO l_date FROM dual;
220 l_user_id := fnd_global.user_id;
221 l_login_id := fnd_global.login_id;
222
223 validate_input
224 (
225 x_return_status => x_return_status
226 ,p_action => 'INSERT'
227 ,p_rule_id => p_rule_id
228 ,p_sequence_number => p_sequence_number
229 ,p_parameter_id => p_parameter_id
230 ,p_operator_code => p_operator_code
231 ,p_operand_type_code => p_operand_type_code
232 ,p_operand_constant_number => p_operand_constant_number
233 ,p_operand_constant_character => p_operand_constant_character
234 ,p_operand_constant_date => p_operand_constant_date
235 ,p_operand_parameter_id => p_operand_parameter_id
236 ,p_operand_expression => p_operand_expression
237 ,p_operand_flex_value_set_id => p_operand_flex_value_set_id
238 ,p_logical_operator_code => p_logical_operator_code
239 ,p_bracket_open => p_bracket_open
240 ,p_bracket_close => p_bracket_close
241 ,p_attribute_category => p_attribute_category
242 ,p_attribute1 => p_attribute1
243 ,p_attribute2 => p_attribute2
244 ,p_attribute3 => p_attribute3
245 ,p_attribute4 => p_attribute4
246 ,p_attribute5 => p_attribute5
247 ,p_attribute6 => p_attribute6
248 ,p_attribute7 => p_attribute7
249 ,p_attribute8 => p_attribute8
250 ,p_attribute9 => p_attribute9
251 ,p_attribute10 => p_attribute10
252 ,p_attribute11 => p_attribute11
253 ,p_attribute12 => p_attribute12
254 ,p_attribute13 => p_attribute13
255 ,p_attribute14 => p_attribute14
256 ,p_attribute15 => p_attribute15
257 );
258
259 if x_return_status = fnd_api.g_ret_sts_unexp_error then
260 raise fnd_api.g_exc_unexpected_error;
261 elsif x_return_status = fnd_api.g_ret_sts_error then
262 raise fnd_api.g_exc_error;
263 end if;
264
265 /* call the table handler to do the insert */
266 wms_restrictions_pkg.insert_row
267 (
268 x_rowid => p_rowid
269 ,x_rule_id => p_rule_id
270 ,x_sequence_number => p_sequence_number
271 ,x_last_updated_by => l_user_id
272 ,x_last_update_date => l_date
273 ,x_created_by => l_user_id
274 ,x_creation_date => l_date
275 ,x_last_update_login => l_login_id
276 ,x_parameter_id => p_parameter_id
277 ,x_operator_code => p_operator_code
278 ,x_operand_type_code => p_operand_type_code
279 ,x_operand_constant_number => p_operand_constant_number
280 ,x_operand_constant_character => p_operand_constant_character
281 ,x_operand_constant_date => p_operand_constant_date
282 ,x_operand_parameter_id => p_operand_parameter_id
283 ,x_operand_expression => p_operand_expression
284 ,x_operand_flex_value_set_id => p_operand_flex_value_set_id
285 ,x_logical_operator_code => p_logical_operator_code
286 ,x_bracket_open => p_bracket_open
287 ,x_bracket_close => p_bracket_close
288 ,x_attribute_category => p_attribute_category
289 ,x_attribute1 => p_attribute1
290 ,x_attribute2 => p_attribute2
291 ,x_attribute3 => p_attribute3
292 ,x_attribute4 => p_attribute4
293 ,x_attribute5 => p_attribute5
294 ,x_attribute6 => p_attribute6
295 ,x_attribute7 => p_attribute7
296 ,x_attribute8 => p_attribute8
297 ,x_attribute9 => p_attribute9
298 ,x_attribute10 => p_attribute10
299 ,x_attribute11 => p_attribute11
300 ,x_attribute12 => p_attribute12
301 ,x_attribute13 => p_attribute13
302 ,x_attribute14 => p_attribute14
303 ,x_attribute15 => p_attribute15
304 );
305
306 x_return_status := l_return_status;
307
308 EXCEPTION
309 when fnd_api.g_exc_error then
310 x_return_status := fnd_api.g_ret_sts_error;
311 fnd_msg_pub.count_and_get( p_count => x_msg_count
312 ,p_data => x_msg_data );
313
314 when fnd_api.g_exc_unexpected_error then
315 x_return_status := fnd_api.g_ret_sts_unexp_error;
316 fnd_msg_pub.count_and_get( p_count => x_msg_count
317 ,p_data => x_msg_data );
318
319 when others then
320 x_return_status := fnd_api.g_ret_sts_unexp_error;
321 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
322 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
323 end if;
324 fnd_msg_pub.count_and_get( p_count => x_msg_count
325 ,p_data => x_msg_data );
326
327 end insert_restriction;
328
329 procedure lock_restriction (
330 p_api_version IN NUMBER
331 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
332 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
333 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
334 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
335 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
336 ,p_rowid IN VARCHAR2
337 ,p_rule_id IN NUMBER
338 ,p_sequence_number IN NUMBER
339 ,p_parameter_id IN NUMBER
340 ,p_operator_code IN NUMBER
341 ,p_operand_type_code IN NUMBER
342 ,p_operand_constant_number IN NUMBER
343 ,p_operand_constant_character IN VARCHAR2
344 ,p_operand_constant_date IN DATE
345 ,p_operand_parameter_id IN NUMBER
346 ,p_operand_expression IN VARCHAR2
347 ,p_operand_flex_value_set_id IN NUMBER
348 ,p_logical_operator_code IN NUMBER
349 ,p_bracket_open IN VARCHAR2
350 ,p_bracket_close IN VARCHAR2
351 ,p_attribute_category IN VARCHAR2
352 ,p_attribute1 IN VARCHAR2
353 ,p_attribute2 IN VARCHAR2
354 ,p_attribute3 IN VARCHAR2
355 ,p_attribute4 IN VARCHAR2
356 ,p_attribute5 IN VARCHAR2
357 ,p_attribute6 IN VARCHAR2
358 ,p_attribute7 IN VARCHAR2
359 ,p_attribute8 IN VARCHAR2
360 ,p_attribute9 IN VARCHAR2
361 ,p_attribute10 IN VARCHAR2
362 ,p_attribute11 IN VARCHAR2
363 ,p_attribute12 IN VARCHAR2
364 ,p_attribute13 IN VARCHAR2
365 ,p_attribute14 IN VARCHAR2
366 ,p_attribute15 IN VARCHAR2
367 ) is
368 -- API standard variables
369 l_api_version constant number := 1.0;
370 l_api_name constant varchar2(30) := 'Lock_Restriction';
371 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
372 begin
373 -- Standard call to check for call compatibility
374 if not fnd_api.compatible_api_call( l_api_version
375 ,p_api_version
376 ,l_api_name
377 ,g_pkg_name ) then
378 raise fnd_api.g_exc_unexpected_error;
379 end if;
380
381 -- Initialize message list if p_init_msg_list is set to TRUE
382 if fnd_api.to_boolean( p_init_msg_list ) then
383 fnd_msg_pub.initialize;
384 end if;
385
386 validate_input
387 (
388 x_return_status => x_return_status
389 ,p_action => 'LOCK'
390 ,p_rule_id => p_rule_id
391 ,p_sequence_number => p_sequence_number
392 ,p_parameter_id => p_parameter_id
393 ,p_operator_code => p_operator_code
394 ,p_operand_type_code => p_operand_type_code
395 ,p_operand_constant_number => p_operand_constant_number
396 ,p_operand_constant_character => p_operand_constant_character
397 ,p_operand_constant_date => p_operand_constant_date
398 ,p_operand_parameter_id => p_operand_parameter_id
399 ,p_operand_expression => p_operand_expression
400 ,p_operand_flex_value_set_id => p_operand_flex_value_set_id
401 ,p_logical_operator_code => p_logical_operator_code
402 ,p_bracket_open => p_bracket_open
403 ,p_bracket_close => p_bracket_close
404 ,p_attribute_category => p_attribute_category
405 ,p_attribute1 => p_attribute1
406 ,p_attribute2 => p_attribute2
407 ,p_attribute3 => p_attribute3
408 ,p_attribute4 => p_attribute4
409 ,p_attribute5 => p_attribute5
410 ,p_attribute6 => p_attribute6
411 ,p_attribute7 => p_attribute7
412 ,p_attribute8 => p_attribute8
413 ,p_attribute9 => p_attribute9
414 ,p_attribute10 => p_attribute10
415 ,p_attribute11 => p_attribute11
416 ,p_attribute12 => p_attribute12
417 ,p_attribute13 => p_attribute13
418 ,p_attribute14 => p_attribute14
419 ,p_attribute15 => p_attribute15
420 );
421
422 if x_return_status = fnd_api.g_ret_sts_unexp_error then
423 raise fnd_api.g_exc_unexpected_error;
424 elsif x_return_status = fnd_api.g_ret_sts_error then
425 raise fnd_api.g_exc_error;
426 end if;
427
428 wms_restrictions_pkg.lock_row (
429 x_rowid => p_rowid
430 ,x_rule_id => p_rule_id
431 ,x_sequence_number => p_sequence_number
432 ,x_parameter_id => p_parameter_id
433 ,x_operator_code => p_operator_code
434 ,x_operand_type_code => p_operand_type_code
435 ,x_operand_constant_number => p_operand_constant_number
436 ,x_operand_constant_character => p_operand_constant_character
437 ,x_operand_constant_date => p_operand_constant_date
438 ,x_operand_parameter_id => p_operand_parameter_id
439 ,x_operand_expression => p_operand_expression
440 ,x_operand_flex_value_set_id => p_operand_flex_value_set_id
441 ,x_logical_operator_code => p_logical_operator_code
442 ,x_bracket_open => p_bracket_open
443 ,x_bracket_close => p_bracket_close
444 ,x_attribute_category => p_attribute_category
445 ,x_attribute1 => p_attribute1
446 ,x_attribute2 => p_attribute2
447 ,x_attribute3 => p_attribute3
448 ,x_attribute4 => p_attribute4
449 ,x_attribute5 => p_attribute5
450 ,x_attribute6 => p_attribute6
451 ,x_attribute7 => p_attribute7
452 ,x_attribute8 => p_attribute8
453 ,x_attribute9 => p_attribute9
454 ,x_attribute10 => p_attribute10
455 ,x_attribute11 => p_attribute11
456 ,x_attribute12 => p_attribute12
457 ,x_attribute13 => p_attribute13
458 ,x_attribute14 => p_attribute14
459 ,x_attribute15 => p_attribute15
460 );
461
462 x_return_status := l_return_status;
463
464 EXCEPTION
465 WHEN fnd_api.g_exc_error THEN
466 x_return_status := fnd_api.g_ret_sts_error;
467 fnd_msg_pub.count_and_get( p_count => x_msg_count
468 ,p_data => x_msg_data );
469
470 WHEN fnd_api.g_exc_unexpected_error THEN
471 x_return_status := fnd_api.g_ret_sts_unexp_error;
472 fnd_msg_pub.count_and_get( p_count => x_msg_count
473 ,p_data => x_msg_data );
474
475 WHEN OTHERS THEN
476 x_return_status := fnd_api.g_ret_sts_unexp_error;
477 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
478 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
479 end if;
480 fnd_msg_pub.count_and_get( p_count => x_msg_count
481 ,p_data => x_msg_data );
482
483 end lock_restriction ;
484
485 procedure update_restriction (
486 p_api_version IN NUMBER
487 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
488 ,p_validation_level IN NUMBER := fnd_api.g_valid_level_full
489 ,x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2
490 ,x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER
491 ,x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2
492 ,p_rowid IN VARCHAR2
493 ,p_rule_id IN NUMBER
494 ,p_sequence_number IN NUMBER
495 ,p_parameter_id IN NUMBER
496 ,p_operator_code IN NUMBER
497 ,p_operand_type_code IN NUMBER
498 ,p_operand_constant_number IN NUMBER
499 ,p_operand_constant_character IN VARCHAR2
500 ,p_operand_constant_date IN DATE
501 ,p_operand_parameter_id IN NUMBER
502 ,p_operand_expression IN VARCHAR2
503 ,p_operand_flex_value_set_id IN NUMBER
504 ,p_logical_operator_code IN NUMBER
505 ,p_bracket_open IN VARCHAR2
506 ,p_bracket_close IN VARCHAR2
507 ,p_attribute_category IN VARCHAR2
508 ,p_attribute1 IN VARCHAR2
509 ,p_attribute2 IN VARCHAR2
510 ,p_attribute3 IN VARCHAR2
511 ,p_attribute4 IN VARCHAR2
512 ,p_attribute5 IN VARCHAR2
513 ,p_attribute6 IN VARCHAR2
514 ,p_attribute7 IN VARCHAR2
515 ,p_attribute8 IN VARCHAR2
516 ,p_attribute9 IN VARCHAR2
517 ,p_attribute10 IN VARCHAR2
518 ,p_attribute11 IN VARCHAR2
519 ,p_attribute12 IN VARCHAR2
520 ,p_attribute13 IN VARCHAR2
521 ,p_attribute14 IN VARCHAR2
522 ,p_attribute15 IN VARCHAR2
523 ) is
524 -- API standard variables
525 l_api_version constant number := 1.0;
526 l_api_name constant varchar2(30) := 'Update_Restriction';
527 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
528 l_row_id VARCHAR2(20);
529 l_date DATE;
530 l_user_id NUMBER;
531 l_login_id NUMBER;
532 l_found BOOLEAN;
533 begin
534 -- Standard call to check for call compatibility
535 if not fnd_api.compatible_api_call( l_api_version
536 ,p_api_version
537 ,l_api_name
538 ,g_pkg_name ) then
539 raise fnd_api.g_exc_unexpected_error;
540 end if;
541
542 -- Initialize message list if p_init_msg_list is set to TRUE
543 if fnd_api.to_boolean( p_init_msg_list ) then
544 fnd_msg_pub.initialize;
545 end if;
546
547 validate_input
548 (
549 x_return_status => x_return_status
550 ,p_action => 'UPDATE'
551 ,p_rule_id => p_rule_id
552 ,p_sequence_number => p_sequence_number
553 ,p_parameter_id => p_parameter_id
554 ,p_operator_code => p_operator_code
555 ,p_operand_type_code => p_operand_type_code
556 ,p_operand_constant_number => p_operand_constant_number
557 ,p_operand_constant_character => p_operand_constant_character
558 ,p_operand_constant_date => p_operand_constant_date
559 ,p_operand_parameter_id => p_operand_parameter_id
560 ,p_operand_expression => p_operand_expression
561 ,p_operand_flex_value_set_id => p_operand_flex_value_set_id
562 ,p_logical_operator_code => p_logical_operator_code
563 ,p_bracket_open => p_bracket_open
564 ,p_bracket_close => p_bracket_close
565 ,p_attribute_category => p_attribute_category
566 ,p_attribute1 => p_attribute1
567 ,p_attribute2 => p_attribute2
568 ,p_attribute3 => p_attribute3
569 ,p_attribute4 => p_attribute4
570 ,p_attribute5 => p_attribute5
571 ,p_attribute6 => p_attribute6
572 ,p_attribute7 => p_attribute7
573 ,p_attribute8 => p_attribute8
574 ,p_attribute9 => p_attribute9
575 ,p_attribute10 => p_attribute10
576 ,p_attribute11 => p_attribute11
577 ,p_attribute12 => p_attribute12
578 ,p_attribute13 => p_attribute13
579 ,p_attribute14 => p_attribute14
580 ,p_attribute15 => p_attribute15
581 );
582
583 if x_return_status = fnd_api.g_ret_sts_unexp_error then
584 raise fnd_api.g_exc_unexpected_error;
585 elsif x_return_status = fnd_api.g_ret_sts_error then
586 raise fnd_api.g_exc_error;
587 end if;
588
589 /* get who column information */
590 SELECT Sysdate INTO l_date FROM dual;
591 l_user_id := fnd_global.user_id;
592 l_login_id := fnd_global.login_id;
593
594 /* call the table handler to do the update */
595 wms_restrictions_pkg.update_row
596 (
597 x_rowid => p_rowid
598 ,x_rule_id => p_rule_id
599 ,x_sequence_number => p_sequence_number
600 ,x_last_updated_by => l_user_id
601 ,x_last_update_date => l_date
602 ,x_last_update_login => l_login_id
603 ,x_parameter_id => p_parameter_id
604 ,x_operator_code => p_operator_code
605 ,x_operand_type_code => p_operand_type_code
606 ,x_operand_constant_number => p_operand_constant_number
607 ,x_operand_constant_character => p_operand_constant_character
608 ,x_operand_constant_date => p_operand_constant_date
609 ,x_operand_parameter_id => p_operand_parameter_id
610 ,x_operand_expression => p_operand_expression
611 ,x_operand_flex_value_set_id => p_operand_flex_value_set_id
612 ,x_logical_operator_code => p_logical_operator_code
613 ,x_bracket_open => p_bracket_open
614 ,x_bracket_close => p_bracket_close
615 ,x_attribute_category => p_attribute_category
616 ,x_attribute1 => p_attribute1
617 ,x_attribute2 => p_attribute2
618 ,x_attribute3 => p_attribute3
619 ,x_attribute4 => p_attribute4
620 ,x_attribute5 => p_attribute5
621 ,x_attribute6 => p_attribute6
622 ,x_attribute7 => p_attribute7
623 ,x_attribute8 => p_attribute8
624 ,x_attribute9 => p_attribute9
625 ,x_attribute10 => p_attribute10
626 ,x_attribute11 => p_attribute11
627 ,x_attribute12 => p_attribute12
628 ,x_attribute13 => p_attribute13
629 ,x_attribute14 => p_attribute14
630 ,x_attribute15 => p_attribute15
631 );
632
633 x_return_status := l_return_status;
634
635 EXCEPTION
636 WHEN fnd_api.g_exc_error THEN
637 x_return_status := fnd_api.g_ret_sts_error;
638 fnd_msg_pub.count_and_get( p_count => x_msg_count
639 ,p_data => x_msg_data );
640
641 WHEN fnd_api.g_exc_unexpected_error THEN
642 x_return_status := fnd_api.g_ret_sts_unexp_error;
643 fnd_msg_pub.count_and_get( p_count => x_msg_count
644 ,p_data => x_msg_data );
645
646 when others then
647 x_return_status := fnd_api.g_ret_sts_unexp_error;
648 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
649 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
650 end if;
651 fnd_msg_pub.count_and_get( p_count => x_msg_count
652 ,p_data => x_msg_data );
653
654 end update_restriction ;
655
656 procedure delete_restriction (
657 p_api_version in NUMBER,
658 p_init_msg_list in varchar2 := fnd_api.g_false,
659 p_validation_level in number := fnd_api.g_valid_level_full,
660 x_return_status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
661 x_msg_count OUT NOCOPY /* file.sql.39 change */ NUMBER,
662 x_msg_data OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
663 p_rowid IN VARCHAR2,
664 p_rule_id IN NUMBER,
665 p_sequence_number IN NUMBER
666 ) is
667 -- API standard variables
668 l_api_version constant number := 1.0;
669 l_api_name constant varchar2(30) := 'Delete_Restriction';
670 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
671 l_dummy_rowid VARCHAR2(18);
672 BEGIN
673
674 -- Standard call to check for call compatibility
675 if not fnd_api.compatible_api_call( l_api_version
676 ,p_api_version
677 ,l_api_name
678 ,g_pkg_name ) then
679 raise fnd_api.g_exc_unexpected_error;
680 end if;
681
682 -- Initialize message list if p_init_msg_list is set to TRUE
683 if fnd_api.to_boolean( p_init_msg_list ) then
684 fnd_msg_pub.initialize;
685 end if;
686
687 validate_input
688 (
689 x_return_status => x_return_status
690 ,p_action => 'DELETE'
691 ,p_rule_id => p_rule_id
692 ,p_sequence_number => p_sequence_number
693 ,p_parameter_id => fnd_api.g_miss_num -- dont care
694 ,p_operator_code => fnd_api.g_miss_num
695 ,p_operand_type_code => fnd_api.g_miss_num
696 ,p_operand_constant_number => fnd_api.g_miss_num
697 ,p_operand_constant_character => fnd_api.g_miss_char
698 ,p_operand_constant_date => fnd_api.g_miss_date
699 ,p_operand_parameter_id => fnd_api.g_miss_num
700 ,p_operand_expression => fnd_api.g_miss_char
701 ,p_operand_flex_value_set_id => fnd_api.g_miss_num
702 ,p_logical_operator_code => fnd_api.g_miss_num
703 ,p_bracket_open => fnd_api.g_miss_char
704 ,p_bracket_close => fnd_api.g_miss_char
705 ,p_attribute_category => fnd_api.g_miss_char
706 ,p_attribute1 => fnd_api.g_miss_char
707 ,p_attribute2 => fnd_api.g_miss_char
708 ,p_attribute3 => fnd_api.g_miss_char
709 ,p_attribute4 => fnd_api.g_miss_char
710 ,p_attribute5 => fnd_api.g_miss_char
711 ,p_attribute6 => fnd_api.g_miss_char
712 ,p_attribute7 => fnd_api.g_miss_char
713 ,p_attribute8 => fnd_api.g_miss_char
714 ,p_attribute9 => fnd_api.g_miss_char
715 ,p_attribute10 => fnd_api.g_miss_char
716 ,p_attribute11 => fnd_api.g_miss_char
717 ,p_attribute12 => fnd_api.g_miss_char
718 ,p_attribute13 => fnd_api.g_miss_char
719 ,p_attribute14 => fnd_api.g_miss_char
720 ,p_attribute15 => fnd_api.g_miss_char
721 );
722
723 if x_return_status = fnd_api.g_ret_sts_unexp_error then
724 raise fnd_api.g_exc_unexpected_error;
725 elsif x_return_status = fnd_api.g_ret_sts_error then
726 raise fnd_api.g_exc_error;
727 end if;
728
729 wms_restrictions_pkg.delete_row(p_rowid);
730 fnd_msg_pub.count_and_get( p_count => x_msg_count
731 ,p_data => x_msg_data );
732 x_return_status := l_return_status;
733
734 EXCEPTION
735 WHEN fnd_api.g_exc_error THEN
736 x_return_status := fnd_api.g_ret_sts_error;
737 fnd_msg_pub.count_and_get( p_count => x_msg_count
738 ,p_data => x_msg_data );
739
740 WHEN fnd_api.g_exc_unexpected_error THEN
741 x_return_status := fnd_api.g_ret_sts_unexp_error;
742 fnd_msg_pub.count_and_get( p_count => x_msg_count
743 ,p_data => x_msg_data );
744
745 WHEN OTHERS THEN
746 x_return_status := fnd_api.g_ret_sts_unexp_error;
747 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
748 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
749 end if;
750 fnd_msg_pub.count_and_get( p_count => x_msg_count
751 ,p_data => x_msg_data );
752
753 end delete_restriction ;
754
755 -- private procedure should be used only by the wms_rule_form_pkg.delete_rule
756 -- no validation is done whatsoever
757 procedure delete_restrictions (
758 p_rule_id IN NUMBER
759 ) is
760 BEGIN
761 DELETE FROM wms_restrictions
762 WHERE rule_id = p_rule_id;
763 END delete_restrictions;
764
765 end WMS_RESTRICTION_FORM_PKG;