[Home] [Help]
PACKAGE BODY: APPS.WMS_RULE_FORM_PKG
Source
1 package body wms_rule_form_pkg AS
2 /* $Header: WMSFPPRB.pls 120.1 2005/06/21 10:01:18 appldev ship $ */
3
4 g_pkg_name constant varchar2(30) := 'WMSRule_Form_PKG';
5
6 -- private function
7 -- return true if no existing rule has the same id as the input
8 FUNCTION check_existence
9 (
10 p_rule_id IN NUMBER
11 ) RETURN BOOLEAN IS
12 CURSOR c IS SELECT rule_id FROM wms_rules_b
13 WHERE rule_id = p_rule_id;
14 l_rule_id NUMBER;
15 l_exist BOOLEAN;
16 BEGIN
17 OPEN c;
18 FETCH c INTO l_rule_id;
19 l_exist := NOT(c%notfound);
20 CLOSE c;
21 RETURN l_exist;
22 END check_existence;
23
24 -- private validation routine
25 PROCEDURE validate_input
26 (
27 x_return_status out NOCOPY VARCHAR2
28 ,p_action IN VARCHAR2
29 ,p_rule_id in NUMBER
30 ,p_organization_id in NUMBER
31 ,p_type_code in NUMBER
32 ,p_qty_function_parameter_id in NUMBER
33 ,p_enabled_flag in VARCHAR2
34 ,p_user_defined_flag in VARCHAR2
35 ,p_attribute_category in VARCHAR2
36 ,p_attribute1 in VARCHAR2
37 ,p_attribute2 in VARCHAR2
38 ,p_attribute3 in VARCHAR2
39 ,p_attribute4 in VARCHAR2
40 ,p_attribute5 in VARCHAR2
41 ,p_attribute6 in VARCHAR2
42 ,p_attribute7 in VARCHAR2
43 ,p_attribute8 in VARCHAR2
44 ,p_attribute9 in VARCHAR2
45 ,p_attribute10 in VARCHAR2
46 ,p_attribute11 in VARCHAR2
47 ,p_attribute12 in VARCHAR2
48 ,p_attribute13 in VARCHAR2
49 ,p_attribute14 in VARCHAR2
50 ,p_attribute15 in VARCHAR2
51 ,p_name in VARCHAR2
52 ,p_description in VARCHAR2
53 ,p_allocation_mode_id in number
54 ) IS
55 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
56 BEGIN
57
58 /* check p_action */
59 IF p_action NOT IN ('INSERT', 'UPDATE', 'LOCK', 'DELETE') THEN
60 -- unknown exception
61 RAISE fnd_api.g_exc_unexpected_error;
62 END IF;
63
64 /* check existence */
65 IF p_action IN ('DELETE','LOCK','UPDATE') THEN
66 IF check_existence(p_rule_id) THEN
67 fnd_message.set_name('WMS', 'WMS_RULE_NOT_FOUND');
68 fnd_msg_pub.ADD;
69 RAISE fnd_api.g_exc_error;
70 END IF;
71 END IF;
72
73 /* more input validation update and insert */
74 IF p_action IN ('UPDATE', 'INSERT') THEN
75 /* check foreign keys */
76 /* check organization id here by calling api */
77
78 /* validate enabled_flag */
79 IF p_enabled_flag NOT IN ('Y', 'N') THEN
80 fnd_message.set_name('WMS', 'WMS_INVALID_ENABLED_FLAG');
81 fnd_message.set_token('FLAG',p_enabled_flag);
82 fnd_msg_pub.ADD;
83 RAISE fnd_api.g_exc_error;
84 END IF;
85
86 /* validate user_defined_flag */
87 IF p_user_defined_flag NOT IN ('Y', 'N') THEN
88 fnd_message.set_name('WMS', 'WMS_INVALID_USER_DEFINED_FLAG');
89 fnd_message.set_token('FLAG',p_user_defined_flag);
90 fnd_msg_pub.ADD;
91 RAISE fnd_api.g_exc_error;
92 END IF;
93
94 /*commented out on 12/6/99 by jcearley, since type code can be > 2*/
95 /* validate type code */
96 -- IF p_type_code NOT IN (1,2) THEN
97 -- fnd_message.set_name('WMS','WMS_INVALID_PP_TYPE_CODE');
98 -- fnd_msg_pub.ADD;
99 -- RAISE fnd_api.g_exc_error;
100 -- END IF;
101
102 /* validate qty parameter id */
103 -- IF wms_parameter_form_pkg(p_qty_function_parameter_id) THEN
104 -- fnd_message.set_name('WMS','WMS_INVALID_QTY_PARAMETER');
105 -- fnd_msg_pub.ADD;
106 -- RAISE fnd_api.g_exc_error;
107 -- END IF;
108 END IF;
109
110 /* check that if the rule is in use in a strategy */
111 /* delete is not allowed */
112 -- IF p_action = 'DELETE' THEN
113 -- IF wms_strategy_form_pkg.rule_in_used(rule_id) THEN
114 -- fnd_message.set_name('WMS','WMS_RULE_IN_USE');
115 -- fnd_msg_pub.ADD;
116 -- RAISE fnd_api.g_exc_error;
117 -- END IF;
118 -- END IF;
119 x_return_status := l_return_status;
120
121 EXCEPTION
122 WHEN fnd_api.g_exc_error THEN
123 x_return_status := fnd_api.g_ret_sts_error;
124
125 WHEN fnd_api.g_exc_unexpected_error THEN
126 x_return_status := fnd_api.g_ret_sts_unexp_error;
127
128 WHEN OTHERS THEN
129 x_return_status := fnd_api.g_ret_sts_unexp_error;
130
131 END validate_input;
132
133 procedure insert_rule
134 (
135 x_return_status out NOCOPY varchar2
136 ,x_msg_count out NOCOPY number
137 ,x_msg_data out NOCOPY varchar2
138 ,x_rule_id out NOCOPY NUMBER
139 , p_api_version in number
140 ,p_organization_id in NUMBER
141 ,p_type_code in NUMBER
142 ,p_qty_function_parameter_id in NUMBER
143 ,p_enabled_flag in VARCHAR2
144 ,p_user_defined_flag in VARCHAR2
145 ,p_min_pick_tasks_flag in VARCHAR2
146 ,p_attribute_category in VARCHAR2
147 ,p_attribute1 in VARCHAR2
148 ,p_attribute2 in VARCHAR2
149 ,p_attribute3 in VARCHAR2
150 ,p_attribute4 in VARCHAR2
151 ,p_attribute5 in VARCHAR2
152 ,p_attribute6 in VARCHAR2
153 ,p_attribute7 in VARCHAR2
154 ,p_attribute8 in VARCHAR2
155 ,p_attribute9 in VARCHAR2
156 ,p_attribute10 in VARCHAR2
157 ,p_attribute11 in VARCHAR2
158 ,p_attribute12 in VARCHAR2
159 ,p_attribute13 in VARCHAR2
160 ,p_attribute14 in VARCHAR2
161 ,p_attribute15 in VARCHAR2
162 ,p_name in VARCHAR2
163 ,p_description in VARCHAR2
164 ,p_type_header_id in NUMBER
165 ,p_rule_weight in NUMBER
166 ,p_init_msg_list in varchar2 DEFAULT fnd_api.g_false
167 ,p_validation_level in number DEFAULT fnd_api.g_valid_level_full
168 ,p_allocation_mode_id in number
169 ) IS
170 -- API standard variables
171 l_api_version constant number := 1.0;
172 l_api_name constant varchar2(30) := 'Insert_Rule';
173 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
174 l_row_id VARCHAR2(20);
175 l_date DATE;
176 l_user_id NUMBER;
177 l_login_id NUMBER;
178 l_found BOOLEAN;
179 l_rule_id NUMBER;
180 begin
181
182 -- Standard call to check for call compatibility
183 if not fnd_api.compatible_api_call( l_api_version
184 ,p_api_version
185 ,l_api_name
186 ,g_pkg_name ) then
187 raise fnd_api.g_exc_unexpected_error;
188 end if;
189
190 -- Initialize message list if p_init_msg_list is set to TRUE
191 if fnd_api.to_boolean( p_init_msg_list ) then
192 fnd_msg_pub.initialize;
193 end if;
194
195 SELECT wms_rules_s.NEXTVAL INTO l_rule_id FROM dual;
196
197 validate_input
198 (
199 x_return_status => l_return_status
200 ,p_action => 'INSERT'
201 ,p_rule_id => l_rule_id
202 ,p_organization_id => p_organization_id
203 ,p_type_code => p_type_code
204 ,p_qty_function_parameter_id => p_qty_function_parameter_id
205 ,p_enabled_flag => p_enabled_flag
206 ,p_user_defined_flag => p_user_defined_flag
207 ,p_attribute_category => p_attribute_category
208 ,p_attribute1 => p_attribute1
209 ,p_attribute2 => p_attribute2
210 ,p_attribute3 => p_attribute3
211 ,p_attribute4 => p_attribute4
212 ,p_attribute5 => p_attribute5
213 ,p_attribute6 => p_attribute6
214 ,p_attribute7 => p_attribute7
215 ,p_attribute8 => p_attribute8
216 ,p_attribute9 => p_attribute9
217 ,p_attribute10 => p_attribute10
218 ,p_attribute11 => p_attribute11
219 ,p_attribute12 => p_attribute12
220 ,p_attribute13 => p_attribute13
221 ,p_attribute14 => p_attribute14
222 ,p_attribute15 => p_attribute15
223 ,p_name => p_name
224 ,p_description => p_description
225 ,p_allocation_mode_id => p_allocation_mode_id
226 );
227
228 if x_return_status = fnd_api.g_ret_sts_unexp_error then
229 raise fnd_api.g_exc_unexpected_error;
230 elsif x_return_status = fnd_api.g_ret_sts_error then
231 raise fnd_api.g_exc_error;
232 end if;
233
234 /* get who column information */
235 SELECT Sysdate INTO l_date FROM dual;
236 l_user_id := fnd_global.user_id;
237 l_login_id := fnd_global.login_id;
238
239 /* call the table handler to do the insert */
240 wms_rules_pkg.insert_row
241 (
242 x_rowid => l_row_id
243 ,x_rule_id => l_rule_id
244 ,x_organization_id => p_organization_id
245 ,x_type_code => p_type_code
246 ,x_qty_function_parameter_id => p_qty_function_parameter_id
247 ,x_enabled_flag => p_enabled_flag
248 ,x_user_defined_flag => p_user_defined_flag
249 ,x_min_pick_tasks_flag => p_min_pick_tasks_flag
250 ,x_attribute_category => p_attribute_category
251 ,x_attribute1 => p_attribute1
252 ,x_attribute2 => p_attribute2
253 ,x_attribute3 => p_attribute3
254 ,x_attribute4 => p_attribute4
255 ,x_attribute5 => p_attribute5
256 ,x_attribute6 => p_attribute6
257 ,x_attribute7 => p_attribute7
258 ,x_attribute8 => p_attribute8
259 ,x_attribute9 => p_attribute9
260 ,x_attribute10 => p_attribute10
261 ,x_attribute11 => p_attribute11
262 ,x_attribute12 => p_attribute12
263 ,x_attribute13 => p_attribute13
264 ,x_attribute14 => p_attribute14
265 ,x_attribute15 => p_attribute15
266 ,x_name => p_name
267 ,x_description => p_description
268 ,x_creation_date => l_date
269 ,x_created_by => l_user_id
270 ,x_last_update_date => l_date
271 ,x_last_updated_by => l_user_id
272 ,x_last_update_login => l_login_id
273 ,x_type_header_id => p_type_header_id
274 ,x_rule_weight => p_rule_weight
275 ,x_allocation_mode_id => p_allocation_mode_id
276 );
277
278 x_return_status := l_return_status;
279 x_rule_id := l_rule_id;
280
281 EXCEPTION
282 when fnd_api.g_exc_error then
283 x_return_status := fnd_api.g_ret_sts_error;
284 fnd_msg_pub.count_and_get( p_count => x_msg_count
285 ,p_data => x_msg_data );
286
287 when fnd_api.g_exc_unexpected_error then
288 x_return_status := fnd_api.g_ret_sts_unexp_error;
289 fnd_msg_pub.count_and_get( p_count => x_msg_count
290 ,p_data => x_msg_data );
291
292 when others then
293 x_return_status := fnd_api.g_ret_sts_unexp_error;
294 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
295 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
296 end if;
297 fnd_msg_pub.count_and_get( p_count => x_msg_count
298 ,p_data => x_msg_data );
299
300 end insert_rule;
301
302 procedure lock_rule (
303 x_return_status out NOCOPY VARCHAR2,
304 x_msg_count out NOCOPY NUMBER,
305 x_msg_data out NOCOPY VARCHAR2,
306 p_rule_id in NUMBER,
307 p_organization_id in NUMBER,
308 p_type_code in NUMBER,
309 p_qty_function_parameter_id in NUMBER,
310 p_enabled_flag in VARCHAR2,
311 p_user_defined_flag in VARCHAR2,
312 p_min_pick_tasks_flag in VARCHAR2,
313 p_attribute_category in VARCHAR2,
314 p_attribute1 in VARCHAR2,
315 p_attribute2 in VARCHAR2,
316 p_attribute3 in VARCHAR2,
317 p_attribute4 in VARCHAR2,
318 p_attribute5 in VARCHAR2,
319 p_attribute6 in VARCHAR2,
320 p_attribute7 in VARCHAR2,
321 p_attribute8 in VARCHAR2,
322 p_attribute9 in VARCHAR2,
323 p_attribute10 in VARCHAR2,
324 p_attribute11 in VARCHAR2,
325 p_attribute12 in VARCHAR2,
326 p_attribute13 in VARCHAR2,
327 p_attribute14 in VARCHAR2,
328 p_attribute15 in VARCHAR2,
329 p_name in VARCHAR2,
330 p_description in VARCHAR2,
331 p_type_header_id in NUMBER,
332 p_rule_weight in NUMBER,
333 p_api_version in NUMBER,
334 p_init_msg_list in varchar2 DEFAULT fnd_api.g_false,
335 p_validation_level in number DEFAULT fnd_api.g_valid_level_full
336 ,p_allocation_mode_id in number
337 ) is
338 -- API standard variables
339 l_api_version constant number := 1.0;
340 l_api_name constant varchar2(30) := 'Lock_Rule';
341 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
342 begin
343 -- Standard call to check for call compatibility
344 if not fnd_api.compatible_api_call( l_api_version
345 ,p_api_version
346 ,l_api_name
347 ,g_pkg_name ) then
348 raise fnd_api.g_exc_unexpected_error;
349 end if;
350
351 -- Initialize message list if p_init_msg_list is set to TRUE
352 if fnd_api.to_boolean( p_init_msg_list ) then
353 fnd_msg_pub.initialize;
354 end if;
355
356 validate_input
357 (
358 x_return_status => l_return_status
359 ,p_action => 'LOCK'
360 ,p_rule_id => p_rule_id
361 ,p_organization_id => p_organization_id
362 ,p_type_code => p_type_code
363 ,p_qty_function_parameter_id => p_qty_function_parameter_id
364 ,p_enabled_flag => p_enabled_flag
365 ,p_user_defined_flag => p_user_defined_flag
366 ,p_attribute_category => p_attribute_category
367 ,p_attribute1 => p_attribute1
368 ,p_attribute2 => p_attribute2
369 ,p_attribute3 => p_attribute3
370 ,p_attribute4 => p_attribute4
371 ,p_attribute5 => p_attribute5
372 ,p_attribute6 => p_attribute6
373 ,p_attribute7 => p_attribute7
374 ,p_attribute8 => p_attribute8
375 ,p_attribute9 => p_attribute9
376 ,p_attribute10 => p_attribute10
377 ,p_attribute11 => p_attribute11
378 ,p_attribute12 => p_attribute12
379 ,p_attribute13 => p_attribute13
380 ,p_attribute14 => p_attribute14
381 ,p_attribute15 => p_attribute15
382 ,p_name => p_name
383 ,p_description => p_description
384 ,p_allocation_mode_id => p_allocation_mode_id
385 );
386
387 if x_return_status = fnd_api.g_ret_sts_unexp_error then
388 raise fnd_api.g_exc_unexpected_error;
389 elsif x_return_status = fnd_api.g_ret_sts_error then
390 raise fnd_api.g_exc_error;
391 end if;
392
393 wms_rules_pkg.lock_row
394 (
395 x_rule_id => p_rule_id
396 ,x_organization_id => p_organization_id
397 ,x_type_code => p_type_code
398 ,x_qty_function_parameter_id => p_qty_function_parameter_id
399 ,x_enabled_flag => p_enabled_flag
400 ,x_user_defined_flag => p_user_defined_flag
401 ,x_min_pick_tasks_flag => p_min_pick_tasks_flag
402 ,x_attribute_category => p_attribute_category
403 ,x_attribute1 => p_attribute1
404 ,x_attribute2 => p_attribute2
405 ,x_attribute3 => p_attribute3
406 ,x_attribute4 => p_attribute4
407 ,x_attribute5 => p_attribute5
408 ,x_attribute6 => p_attribute6
409 ,x_attribute7 => p_attribute7
410 ,x_attribute8 => p_attribute8
411 ,x_attribute9 => p_attribute9
412 ,x_attribute10 => p_attribute10
413 ,x_attribute11 => p_attribute11
414 ,x_attribute12 => p_attribute12
415 ,x_attribute13 => p_attribute13
416 ,x_attribute14 => p_attribute14
417 ,x_attribute15 => p_attribute15
418 ,x_name => p_name
419 ,x_description => p_description
420 ,x_type_header_id => p_type_header_id
421 ,x_rule_weight => p_rule_weight
422 ,x_allocation_mode_id => p_allocation_mode_id
423 );
424
425 x_return_status := l_return_status;
426
427 EXCEPTION
428 WHEN no_data_found THEN
429 fnd_message.set_name('WMS','WMS_RULE_NOT_FOUND');
430 fnd_msg_pub.ADD;
431 x_return_status := fnd_api.g_ret_sts_unexp_error;
432
433 WHEN OTHERS THEN
434 x_return_status := fnd_api.g_ret_sts_unexp_error;
435 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
436 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
437 end if;
438 fnd_msg_pub.count_and_get( p_count => x_msg_count
439 ,p_data => x_msg_data );
440
441 end lock_rule ;
442
443 procedure update_rule (
444 x_return_status out NOCOPY VARCHAR2,
445 x_msg_count out NOCOPY NUMBER,
446 x_msg_data out NOCOPY VARCHAR2,
447 p_api_version in NUMBER,
448 p_rule_id in NUMBER,
449 p_organization_id in NUMBER,
450 p_type_code in NUMBER,
451 p_qty_function_parameter_id in NUMBER,
452 p_enabled_flag in VARCHAR2,
453 p_user_defined_flag in VARCHAR2,
454 p_min_pick_tasks_flag in VARCHAR2,
455 p_attribute_category in VARCHAR2,
456 p_attribute1 in VARCHAR2,
457 p_attribute2 in VARCHAR2,
458 p_attribute3 in VARCHAR2,
459 p_attribute4 in VARCHAR2,
460 p_attribute5 in VARCHAR2,
461 p_attribute6 in VARCHAR2,
462 p_attribute7 in VARCHAR2,
463 p_attribute8 in VARCHAR2,
464 p_attribute9 in VARCHAR2,
465 p_attribute10 in VARCHAR2,
466 p_attribute11 in VARCHAR2,
467 p_attribute12 in VARCHAR2,
468 p_attribute13 in VARCHAR2,
469 p_attribute14 in VARCHAR2,
470 p_attribute15 in VARCHAR2,
471 p_name in VARCHAR2,
472 p_description in VARCHAR2,
473 p_type_header_id in NUMBER,
474 p_rule_weight in NUMBER,
475 p_last_update_date in DATE,
476 p_last_updated_by in NUMBER,
477 p_last_update_login in NUMBER,
478 p_init_msg_list in varchar2 DEFAULT fnd_api.g_false,
479 p_validation_level in number DEFAULT fnd_api.g_valid_level_full
480 ,p_allocation_mode_id in number
481 ) is
482 -- API standard variables
483 l_api_version constant number := 1.0;
484 l_api_name constant varchar2(30) := 'Update_Rule';
485 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
486 l_row_id VARCHAR2(20);
487 l_date DATE;
488 l_user_id NUMBER;
489 l_login_id NUMBER;
490 l_found BOOLEAN;
491 begin
492 -- Standard call to check for call compatibility
493 if not fnd_api.compatible_api_call( l_api_version
494 ,p_api_version
495 ,l_api_name
496 ,g_pkg_name ) then
497 raise fnd_api.g_exc_unexpected_error;
498 end if;
499
500 -- Initialize message list if p_init_msg_list is set to TRUE
501 if fnd_api.to_boolean( p_init_msg_list ) then
502 fnd_msg_pub.initialize;
503 end if;
504
505 validate_input
506 (
507 x_return_status => l_return_status
508 ,p_action => 'UPDATE'
509 ,p_rule_id => p_rule_id
510 ,p_organization_id => p_organization_id
511 ,p_type_code => p_type_code
512 ,p_qty_function_parameter_id => p_qty_function_parameter_id
513 ,p_enabled_flag => p_enabled_flag
514 ,p_user_defined_flag => p_user_defined_flag
515 ,p_attribute_category => p_attribute_category
516 ,p_attribute1 => p_attribute1
517 ,p_attribute2 => p_attribute2
518 ,p_attribute3 => p_attribute3
519 ,p_attribute4 => p_attribute4
520 ,p_attribute5 => p_attribute5
521 ,p_attribute6 => p_attribute6
522 ,p_attribute7 => p_attribute7
523 ,p_attribute8 => p_attribute8
524 ,p_attribute9 => p_attribute9
525 ,p_attribute10 => p_attribute10
526 ,p_attribute11 => p_attribute11
527 ,p_attribute12 => p_attribute12
528 ,p_attribute13 => p_attribute13
529 ,p_attribute14 => p_attribute14
530 ,p_attribute15 => p_attribute15
531 ,p_name => p_name
532 ,p_description => p_description
533 ,p_allocation_mode_id => p_allocation_mode_id
534 );
535
536 if x_return_status = fnd_api.g_ret_sts_unexp_error then
537 raise fnd_api.g_exc_unexpected_error;
538 elsif x_return_status = fnd_api.g_ret_sts_error then
539 raise fnd_api.g_exc_error;
540 end if;
541
542 /* get who column information */
543 SELECT Sysdate INTO l_date FROM dual;
544 l_user_id := fnd_global.user_id;
545 l_login_id := fnd_global.login_id;
546
547 /* call the table handler to do the update */
548 wms_rules_pkg.update_row
549 (
550 x_rule_id => p_rule_id
551 ,x_organization_id => p_organization_id
552 ,x_type_code => p_type_code
553 ,x_qty_function_parameter_id => p_qty_function_parameter_id
554 ,x_enabled_flag => p_enabled_flag
555 ,x_user_defined_flag => p_user_defined_flag
556 ,x_min_pick_tasks_flag => p_min_pick_tasks_flag
557 ,x_attribute_category => p_attribute_category
558 ,x_attribute1 => p_attribute1
559 ,x_attribute2 => p_attribute2
560 ,x_attribute3 => p_attribute3
561 ,x_attribute4 => p_attribute4
562 ,x_attribute5 => p_attribute5
563 ,x_attribute6 => p_attribute6
564 ,x_attribute7 => p_attribute7
565 ,x_attribute8 => p_attribute8
566 ,x_attribute9 => p_attribute9
567 ,x_attribute10 => p_attribute10
568 ,x_attribute11 => p_attribute11
569 ,x_attribute12 => p_attribute12
570 ,x_attribute13 => p_attribute13
571 ,x_attribute14 => p_attribute14
572 ,x_attribute15 => p_attribute15
573 ,x_name => p_name
574 ,x_description => p_description
575 ,x_last_update_date => l_date
576 ,x_last_updated_by => l_user_id
577 ,x_last_update_login => l_login_id
578 ,x_type_header_id => p_type_header_id
579 ,x_rule_weight => p_rule_weight
580 ,x_allocation_mode_id => p_allocation_mode_id
581 );
582
583 x_return_status := l_return_status;
584
585 EXCEPTION
586 when fnd_api.g_exc_error then
587 x_return_status := fnd_api.g_ret_sts_error;
588 fnd_msg_pub.count_and_get( p_count => x_msg_count
589 ,p_data => x_msg_data );
590
591 when fnd_api.g_exc_unexpected_error then
592 x_return_status := fnd_api.g_ret_sts_unexp_error;
593 fnd_msg_pub.count_and_get( p_count => x_msg_count
594 ,p_data => x_msg_data );
595
596 when others then
597 x_return_status := fnd_api.g_ret_sts_unexp_error;
598 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
599 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
600 end if;
601 fnd_msg_pub.count_and_get( p_count => x_msg_count
602 ,p_data => x_msg_data );
603
604 end update_rule ;
605
606 procedure find_rule (
607 x_return_status out NOCOPY VARCHAR2,
608 x_msg_count out NOCOPY NUMBER,
609 x_msg_data out NOCOPY VARCHAR2,
610 x_found out NOCOPY BOOLEAN,
611 p_rule_id IN NUMBER,
612 p_api_version in NUMBER,
613 p_init_msg_list in varchar2 DEFAULT fnd_api.g_false,
614 p_validation_level in number DEFAULT fnd_api.g_valid_level_full
615 ) IS
616 -- API standard variables
617 l_api_version constant number := 1.0;
618 l_api_name constant varchar2(30) := 'Find_Rule';
619
620 CURSOR l_cur IS
621 SELECT 'Y'
622 FROM wms_rules_b
623 WHERE rule_id = p_rule_id;
624
625 l_dummy VARCHAR2(1);
626 BEGIN
627 -- Standard call to check for call compatibility
628 if not fnd_api.compatible_api_call( l_api_version
629 ,p_api_version
630 ,l_api_name
631 ,g_pkg_name ) then
632 raise fnd_api.g_exc_unexpected_error;
633 end if;
634
635 -- Initialize message list if p_init_msg_list is set to TRUE
636 if fnd_api.to_boolean( p_init_msg_list ) then
637 fnd_msg_pub.initialize;
638 end if;
639
640 OPEN l_cur;
641 FETCH l_cur INTO l_dummy;
642
643 IF l_cur%notfound THEN
644 x_found := FALSE;
645 ELSE
646 x_found := TRUE;
647 END IF;
648 CLOSE l_cur;
649
650 EXCEPTION
651 when fnd_api.g_exc_error then
652 x_return_status := fnd_api.g_ret_sts_error;
653 fnd_msg_pub.count_and_get( p_count => x_msg_count
654 ,p_data => x_msg_data );
655
656 when fnd_api.g_exc_unexpected_error then
657 x_return_status := fnd_api.g_ret_sts_unexp_error;
658 fnd_msg_pub.count_and_get( p_count => x_msg_count
659 ,p_data => x_msg_data );
660
661 when others then
662 x_return_status := fnd_api.g_ret_sts_unexp_error;
663 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
664 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
665 end if;
666 fnd_msg_pub.count_and_get( p_count => x_msg_count
667 ,p_data => x_msg_data );
668
669 END find_rule;
670
671 procedure delete_rule (
672 x_return_status out NOCOPY VARCHAR2,
673 x_msg_count out NOCOPY NUMBER,
674 x_msg_data out NOCOPY VARCHAR2,
675 p_rule_id IN NUMBER,
676 p_api_version in NUMBER,
677 p_init_msg_list in varchar2 DEFAULT fnd_api.g_false,
678 p_validation_level in number DEFAULT fnd_api.g_valid_level_full
679 ) is
680 -- API standard variables
681 l_api_version constant number := 1.0;
682 l_api_name constant varchar2(30) := 'Delete_Rule';
683 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
684 BEGIN
685
686 -- Standard call to check for call compatibility
687 if not fnd_api.compatible_api_call( l_api_version
688 ,p_api_version
689 ,l_api_name
690 ,g_pkg_name ) then
691 raise fnd_api.g_exc_unexpected_error;
692 end if;
693
694 -- Initialize message list if p_init_msg_list is set to TRUE
695 if fnd_api.to_boolean( p_init_msg_list ) then
696 fnd_msg_pub.initialize;
697 end if;
698
699 SAVEPOINT delete_rule_sa;
700
701 validate_input
702 (
703 x_return_status => l_return_status
704 ,p_action => 'DELETE'
705 ,p_rule_id => p_rule_id
706 ,p_organization_id => fnd_api.g_miss_num
707 ,p_type_code => fnd_api.g_miss_num
708 ,p_qty_function_parameter_id => fnd_api.g_miss_num
709 ,p_enabled_flag => fnd_api.g_miss_char
710 ,p_user_defined_flag => fnd_api.g_miss_char
711 ,p_attribute_category => fnd_api.g_miss_char
712 ,p_attribute1 => fnd_api.g_miss_char
713 ,p_attribute2 => fnd_api.g_miss_char
714 ,p_attribute3 => fnd_api.g_miss_char
715 ,p_attribute4 => fnd_api.g_miss_char
716 ,p_attribute5 => fnd_api.g_miss_char
717 ,p_attribute6 => fnd_api.g_miss_char
718 ,p_attribute7 => fnd_api.g_miss_char
719 ,p_attribute8 => fnd_api.g_miss_char
720 ,p_attribute9 => fnd_api.g_miss_char
721 ,p_attribute10 => fnd_api.g_miss_char
722 ,p_attribute11 => fnd_api.g_miss_char
723 ,p_attribute12 => fnd_api.g_miss_char
724 ,p_attribute13 => fnd_api.g_miss_char
725 ,p_attribute14 => fnd_api.g_miss_char
726 ,p_attribute15 => fnd_api.g_miss_char
727 ,p_name => fnd_api.g_miss_char
728 ,p_description => fnd_api.g_miss_char
729 ,p_allocation_mode_id => fnd_api.g_miss_num
730 );
731
732 if x_return_status = fnd_api.g_ret_sts_unexp_error then
733 raise fnd_api.g_exc_unexpected_error;
734 elsif x_return_status = fnd_api.g_ret_sts_error then
735 raise fnd_api.g_exc_error;
736 end if;
737
738 /* special treatment */
739 /* we want to delete all restrictions and sort criteria records that tie to */
740 /* this rule */
741 wms_restriction_form_pkg.delete_restrictions(p_rule_id);
742 /* wms_sort_criteria_form_pkg.delete_sort_criteria(p_rule_id); */
743 wms_rules_pkg.delete_row(p_rule_id);
744
745 x_return_status := l_return_status;
746
747 EXCEPTION
748 when fnd_api.g_exc_error THEN
749 ROLLBACK TO delete_rule_sa;
750 x_return_status := fnd_api.g_ret_sts_error;
751 fnd_msg_pub.count_and_get( p_count => x_msg_count
752 ,p_data => x_msg_data );
753
754 when fnd_api.g_exc_unexpected_error then
755 ROLLBACK TO delete_rule_sa;
756 x_return_status := fnd_api.g_ret_sts_unexp_error;
757 fnd_msg_pub.count_and_get( p_count => x_msg_count
758 ,p_data => x_msg_data );
759
760 WHEN OTHERS THEN
761 ROLLBACK TO delete_rule_sa;
762 x_return_status := fnd_api.g_ret_sts_unexp_error;
763 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
764 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
765 end if;
766 fnd_msg_pub.count_and_get( p_count => x_msg_count
767 ,p_data => x_msg_data );
768
769 end delete_rule ;
770
771 PROCEDURE copy_rule
772 (
773 x_return_status out NOCOPY VARCHAR2
774 ,x_msg_count out NOCOPY NUMBER
775 ,x_msg_data out NOCOPY VARCHAR2
776 ,x_new_rule_id out NOCOPY NUMBER
777 ,p_orig_rule_id IN NUMBER
778 ,p_new_rule_name IN VARCHAR2
779 ,p_new_description IN VARCHAR2
780 ,p_new_organization_id IN NUMBER
781 ,p_new_type_code IN NUMBER
782 ,p_copy_restriction_flag IN VARCHAR2
783 ,p_copy_sort_criteria_flag IN VARCHAR2
784 ,p_api_version in NUMBER
785 ,p_init_msg_list in varchar2 DEFAULT fnd_api.g_false
786 ,p_validation_level in number DEFAULT fnd_api.g_valid_level_full
787 ) IS
788
789 l_api_version constant number := 1.0;
790 l_api_name constant varchar2(30) := 'Copy_Rule';
791 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
792 l_rowid VARCHAR2(2000);
793
794 l_consistency_id NUMBER;
795 l_date DATE;
796
797
798 CURSOR l_orig_rule_cur IS
799 SELECT
800 organization_id
801 ,rule_id
802 ,type_code
803 ,qty_function_parameter_id
804 ,enabled_flag
805 ,user_defined_flag
806 ,min_pick_tasks_flag
807 ,attribute_category
808 ,attribute1
809 ,attribute2
810 ,attribute3
811 ,attribute4
812 ,attribute5
813 ,attribute6
814 ,attribute7
815 ,attribute8
816 ,attribute9
817 ,attribute10
818 ,attribute11
819 ,attribute12
820 ,attribute13
821 ,attribute14
822 ,attribute15
823 ,name
824 ,description
825 ,TYPE_HDR_ID
826 ,RULE_WEIGHT
827 ,allocation_mode_id
828 FROM wms_rules
829 WHERE rule_id = p_orig_rule_id;
830
831 l_rule_rec l_orig_rule_cur%ROWTYPE;
832
833 CURSOR l_orig_restriction_cur IS
834 SELECT
835 rule_id
836 ,sequence_number
837 ,parameter_id
838 ,operator_code
839 ,operand_type_code
840 ,operand_constant_number
841 ,operand_constant_character
842 ,operand_constant_date
843 ,operand_parameter_id
844 ,operand_expression
845 ,operand_flex_value_set_id
846 ,logical_operator_code
847 ,bracket_open
848 ,bracket_close
849 ,attribute_category
850 ,attribute1
851 ,attribute2
852 ,attribute3
853 ,attribute4
854 ,attribute5
855 ,attribute6
856 ,attribute7
857 ,attribute8
858 ,attribute9
859 ,attribute10
860 ,attribute11
861 ,attribute12
862 ,attribute13
863 ,attribute14
864 ,attribute15
865 FROM wms_restrictions
866 WHERE rule_id = p_orig_rule_id;
867
868 l_restriction_rec l_orig_restriction_cur%ROWTYPE;
869
870 CURSOR l_orig_sort_cur IS
871 SELECT
872 rule_id
873 ,sequence_number
874 ,parameter_id
875 ,order_code
876 ,attribute_category
877 ,attribute1
878 ,attribute2
879 ,attribute3
880 ,attribute4
881 ,attribute5
882 ,attribute6
883 ,attribute7
884 ,attribute8
885 ,attribute9
886 ,attribute10
887 ,attribute11
888 ,attribute12
889 ,attribute13
890 ,attribute14
891 ,attribute15
892 FROM wms_sort_criteria
893 WHERE rule_id = p_orig_rule_id;
894
895 l_sort_rec l_orig_sort_cur%ROWTYPE;
896
897 -- Part of Bugfix 2279644
898 CURSOR l_orig_consistency_cur IS
899 SELECT
900 rule_id
901 ,consistency_id
902 ,parameter_id
903 ,attribute_category
904 ,attribute1
905 ,attribute2
906 ,attribute3
907 ,attribute4
908 ,attribute5
909 ,attribute6
910 ,attribute7
911 ,attribute8
912 ,attribute9
913 ,attribute10
914 ,attribute11
915 ,attribute12
916 ,attribute13
917 ,attribute14
918 ,attribute15
919 FROM wms_rule_consistencies
920 WHERE rule_id = p_orig_rule_id;
921
922 l_consistency_rec l_orig_consistency_cur%ROWTYPE;
923
924
925 l_rule_id NUMBER;
926 BEGIN
927
928 -- Standard call to check for call compatibility
929 if not fnd_api.compatible_api_call( l_api_version
930 ,p_api_version
931 ,l_api_name
932 ,g_pkg_name ) then
933 raise fnd_api.g_exc_unexpected_error;
934 end if;
935
936 -- Initialize message list if p_init_msg_list is set to TRUE
937 if fnd_api.to_boolean( p_init_msg_list ) then
938 fnd_msg_pub.initialize;
939 end if;
940
941 SAVEPOINT copy_rule_sp;
942
943 /* copy rule */
944 OPEN l_orig_rule_cur;
945 FETCH l_orig_rule_cur INTO l_rule_rec;
946 IF l_orig_rule_cur%NOTFOUND THEN
947 fnd_message.set_name('WMS','WMS_RULE_NOT_FOUND');
948 fnd_msg_pub.ADD;
949 RAISE fnd_api.g_exc_error;
950 END IF;
951
952 /* no validation is done here */
953 /* should be added later */
954 l_rule_rec.organization_id := p_new_organization_id;
955 l_rule_rec.name := p_new_rule_name;
956 l_rule_rec.description := p_new_description;
957 l_rule_rec.type_code := p_new_type_code;
958 l_rule_rec.enabled_flag := 'N';
959 l_rule_rec.user_defined_flag := 'Y'; /*As a part of bug 1903620 */
960
961 wms_rule_form_pkg.insert_rule
962 (
963 x_return_status => l_return_status
964 ,x_msg_count => x_msg_count
965 ,x_msg_data => x_msg_data
966 ,x_rule_id => l_rule_id
967 , p_api_version => 1.0
968 ,p_organization_id => l_rule_rec.organization_id
969 ,p_type_code => l_rule_rec.type_code
970 ,p_qty_function_parameter_id => l_rule_rec.qty_function_parameter_id
971 ,p_enabled_flag => l_rule_rec.enabled_flag
972 ,p_user_defined_flag => l_rule_rec.user_defined_flag
973 ,p_min_pick_tasks_flag => l_rule_rec.min_pick_tasks_flag
974 ,p_attribute_category => l_rule_rec.attribute_category
975 ,p_attribute1 => l_rule_rec.attribute1
976 ,p_attribute2 => l_rule_rec.attribute2
977 ,p_attribute3 => l_rule_rec.attribute3
978 ,p_attribute4 => l_rule_rec.attribute4
979 ,p_attribute5 => l_rule_rec.attribute5
980 ,p_attribute6 => l_rule_rec.attribute6
981 ,p_attribute7 => l_rule_rec.attribute7
982 ,p_attribute8 => l_rule_rec.attribute8
983 ,p_attribute9 => l_rule_rec.attribute9
984 ,p_attribute10 => l_rule_rec.attribute10
985 ,p_attribute11 => l_rule_rec.attribute11
986 ,p_attribute12 => l_rule_rec.attribute12
987 ,p_attribute13 => l_rule_rec.attribute13
988 ,p_attribute14 => l_rule_rec.attribute14
989 ,p_attribute15 => l_rule_rec.attribute15
990 ,p_name => l_rule_rec.name
991 ,p_description => l_rule_rec.description
992 ,p_type_header_id => l_rule_rec.type_hdr_id
993 ,p_rule_weight => l_rule_rec.rule_weight
994 ,p_init_msg_list => fnd_api.g_false
995 ,p_validation_level => fnd_api.g_valid_level_full
996 ,p_allocation_mode_id => l_rule_rec.allocation_mode_id
997 );
998 /* changed the x_return_status to l_return_status below as part of bug1678742 */
999
1000 if l_return_status = fnd_api.g_ret_sts_unexp_error then
1001 raise fnd_api.g_exc_unexpected_error;
1002 elsif l_return_status = fnd_api.g_ret_sts_error then
1003 raise fnd_api.g_exc_error;
1004 end if;
1005
1006 CLOSE l_orig_rule_cur;
1007
1008 /* copy restrictions */
1009
1010 IF p_copy_restriction_flag = 'Y' THEN
1011 OPEN l_orig_restriction_cur;
1012 LOOP
1013 FETCH l_orig_restriction_cur INTO l_restriction_rec;
1014 EXIT WHEN l_orig_restriction_cur%NOTFOUND;
1015 l_restriction_rec.rule_id := l_rule_id;
1016 wms_restriction_form_pkg.insert_restriction
1017 (
1018 p_api_version => 1.0
1019 ,p_init_msg_list => fnd_api.g_false
1020 ,p_validation_level => fnd_api.g_valid_level_full
1021 ,x_return_status => l_return_status
1022 ,x_msg_count => x_msg_count
1023 ,x_msg_data => x_msg_data
1024 ,p_rowid => l_rowid
1025 ,p_rule_id => l_restriction_rec.rule_id
1026 ,p_sequence_number => l_restriction_rec.sequence_number
1027 ,p_parameter_id => l_restriction_rec.parameter_id
1028 ,p_operator_code => l_restriction_rec.operator_code
1029 ,p_operand_type_code => l_restriction_rec.operand_type_code
1030 ,p_operand_constant_number => l_restriction_rec.operand_constant_number
1031 ,p_operand_constant_character => l_restriction_rec.operand_constant_character
1032 ,p_operand_constant_date => l_restriction_rec.operand_constant_date
1033 ,p_operand_parameter_id => l_restriction_rec.operand_parameter_id
1034 ,p_operand_expression => l_restriction_rec.operand_expression
1035 ,p_operand_flex_value_set_id => l_restriction_rec.operand_flex_value_set_id
1036 ,p_logical_operator_code => l_restriction_rec.logical_operator_code
1037 ,p_bracket_open => l_restriction_rec.bracket_open
1038 ,p_bracket_close => l_restriction_rec.bracket_close
1039 ,p_attribute_category => l_restriction_rec.attribute_category
1040 ,p_attribute1 => l_restriction_rec.attribute1
1041 ,p_attribute2 => l_restriction_rec.attribute2
1042 ,p_attribute3 => l_restriction_rec.attribute3
1043 ,p_attribute4 => l_restriction_rec.attribute4
1044 ,p_attribute5 => l_restriction_rec.attribute5
1045 ,p_attribute6 => l_restriction_rec.attribute6
1046 ,p_attribute7 => l_restriction_rec.attribute7
1047 ,p_attribute8 => l_restriction_rec.attribute8
1048 ,p_attribute9 => l_restriction_rec.attribute9
1049 ,p_attribute10 => l_restriction_rec.attribute10
1050 ,p_attribute11 => l_restriction_rec.attribute11
1051 ,p_attribute12 => l_restriction_rec.attribute12
1052 ,p_attribute13 => l_restriction_rec.attribute13
1053 ,p_attribute14 => l_restriction_rec.attribute14
1054 ,p_attribute15 => l_restriction_rec.attribute15
1055 );
1056 /* changed the x_return_status to l_return_status below as part of bug1678742 */
1057 if l_return_status = fnd_api.g_ret_sts_unexp_error then
1058 raise fnd_api.g_exc_unexpected_error;
1059 elsif l_return_status = fnd_api.g_ret_sts_error then
1060 raise fnd_api.g_exc_error;
1061 end if;
1062 END LOOP;
1063 CLOSE l_orig_restriction_cur;
1064 END IF;
1065
1066 /* copy sort criteria */
1067 IF p_copy_sort_criteria_flag = 'Y' THEN
1068 OPEN l_orig_sort_cur;
1069 WHILE TRUE LOOP
1070 FETCH l_orig_sort_cur INTO l_sort_rec;
1071 EXIT WHEN l_orig_sort_cur%NOTFOUND;
1072
1073 l_sort_rec.rule_id := l_rule_id;
1074 wms_sort_criteria_form_pkg.insert_sort_criteria
1075 (
1076 p_api_version => 1.0
1077 ,p_init_msg_list => fnd_api.g_false
1078 ,p_validation_level => fnd_api.g_valid_level_full
1079 ,x_return_status => l_return_status
1080 ,x_msg_count => x_msg_count
1081 ,x_msg_data => x_msg_data
1082 ,p_rowid => l_rowid
1083 ,p_rule_id => l_sort_rec.rule_id
1084 ,p_sequence_number => l_sort_rec.sequence_number
1085 ,p_parameter_id => l_sort_rec.parameter_id
1086 ,p_order_code => l_sort_rec.order_code
1087 ,p_attribute_category => l_sort_rec.attribute_category
1088 ,p_attribute1 => l_sort_rec.attribute1
1089 ,p_attribute2 => l_sort_rec.attribute2
1090 ,p_attribute3 => l_sort_rec.attribute3
1091 ,p_attribute4 => l_sort_rec.attribute4
1092 ,p_attribute5 => l_sort_rec.attribute5
1093 ,p_attribute6 => l_sort_rec.attribute6
1094 ,p_attribute7 => l_sort_rec.attribute7
1095 ,p_attribute8 => l_sort_rec.attribute8
1096 ,p_attribute9 => l_sort_rec.attribute9
1097 ,p_attribute10 => l_sort_rec.attribute10
1098 ,p_attribute11 => l_sort_rec.attribute11
1099 ,p_attribute12 => l_sort_rec.attribute12
1100 ,p_attribute13 => l_sort_rec.attribute13
1101 ,p_attribute14 => l_sort_rec.attribute14
1102 ,p_attribute15 => l_sort_rec.attribute15
1103 );
1104 /* changed the x_return_status to l_return_status below as part of bug1678742 */
1105 if l_return_status = fnd_api.g_ret_sts_unexp_error then
1106 raise fnd_api.g_exc_unexpected_error;
1107 elsif l_return_status = fnd_api.g_ret_sts_error then
1108 raise fnd_api.g_exc_error;
1109 end if;
1110 END LOOP;
1111 CLOSE l_orig_sort_cur;
1112 END IF;
1113
1114 -- Part of Bugfix 2279644
1115 /* Copy Rule_consistencies */
1116 -- IF x_copy_consistency_criteria_flag = 'Y' THEN
1117 SELECT SYSDATE INTO l_date FROM dual;
1118 OPEN l_orig_consistency_cur;
1119 WHILE TRUE LOOP
1120 FETCH l_orig_consistency_cur INTO l_consistency_rec;
1121 EXIT WHEN l_orig_consistency_cur%NOTFOUND;
1122
1123 l_consistency_rec.rule_id := l_rule_id;
1124 SELECT wms_rule_consistencies_s.NEXTVAL INTO l_consistency_id FROM dual;
1125 wms_rule_consistencies_pkg.insert_row
1126 (
1127 x_rowid => l_rowid
1128 ,x_consistency_id => l_consistency_id
1129 ,x_rule_id => l_consistency_rec.rule_id
1130 ,x_parameter_id => l_consistency_rec.parameter_id
1131 ,x_creation_date => l_date
1132 ,x_created_by => fnd_global.user_id
1133 ,x_last_update_date => l_date
1134 ,x_last_updated_by => fnd_global.user_id
1135 ,x_last_update_login => fnd_global.login_id
1136 ,x_attribute_category => l_consistency_rec.attribute_category
1137 ,x_attribute1 => l_consistency_rec.attribute1
1138 ,x_attribute2 => l_consistency_rec.attribute2
1139 ,x_attribute3 => l_consistency_rec.attribute3
1140 ,x_attribute4 => l_consistency_rec.attribute4
1141 ,x_attribute5 => l_consistency_rec.attribute5
1142 ,x_attribute6 => l_consistency_rec.attribute6
1143 ,x_attribute7 => l_consistency_rec.attribute7
1144 ,x_attribute8 => l_consistency_rec.attribute8
1145 ,x_attribute9 => l_consistency_rec.attribute9
1146 ,x_attribute10 => l_consistency_rec.attribute10
1147 ,x_attribute11 => l_consistency_rec.attribute11
1148 ,x_attribute12 => l_consistency_rec.attribute12
1149 ,x_attribute13 => l_consistency_rec.attribute13
1150 ,x_attribute14 => l_consistency_rec.attribute14
1151 ,x_attribute15 => l_consistency_rec.attribute15
1152 );
1153
1154 END LOOP;
1155 CLOSE l_orig_consistency_cur;
1156 -- END IF;
1157
1158 x_new_rule_id := l_rule_id;
1159 x_return_status := l_return_status;
1160
1161 EXCEPTION
1162 when fnd_api.g_exc_error THEN
1163 ROLLBACK TO copy_rule_sp;
1164 IF l_orig_rule_cur%ISOPEN THEN
1165 CLOSE l_orig_rule_cur;
1166 END IF;
1167 IF l_orig_restriction_cur%ISOPEN THEN
1168 CLOSE l_orig_restriction_cur;
1169 END IF;
1170 IF l_orig_sort_cur%ISOPEN THEN
1171 CLOSE l_orig_sort_cur;
1172 END IF;
1173 -- Part of Bugfix 2279644
1174 IF l_orig_consistency_cur%ISOPEN THEN
1175 CLOSE l_orig_consistency_cur;
1176 END IF;
1177
1178 x_return_status := fnd_api.g_ret_sts_error;
1179 fnd_msg_pub.count_and_get( p_count => x_msg_count
1180 ,p_data => x_msg_data );
1181
1182 when fnd_api.g_exc_unexpected_error then
1183 ROLLBACK TO copy_rule_sp;
1184 IF l_orig_rule_cur%ISOPEN THEN
1185 CLOSE l_orig_rule_cur;
1186 END IF;
1187 IF l_orig_restriction_cur%ISOPEN THEN
1188 CLOSE l_orig_restriction_cur;
1189 END IF;
1190 IF l_orig_sort_cur%ISOPEN THEN
1191 CLOSE l_orig_sort_cur;
1192 END IF;
1193 -- Part of Bugfix 2279644
1194 IF l_orig_consistency_cur%ISOPEN THEN
1195 CLOSE l_orig_consistency_cur;
1196 END IF;
1197
1198 x_return_status := fnd_api.g_ret_sts_unexp_error;
1199 fnd_msg_pub.count_and_get( p_count => x_msg_count
1200 ,p_data => x_msg_data );
1201
1202 when others then
1203 ROLLBACK TO copy_rule_sp;
1204 IF l_orig_rule_cur%ISOPEN THEN
1205 CLOSE l_orig_rule_cur;
1206 END IF;
1207 IF l_orig_restriction_cur%ISOPEN THEN
1208 CLOSE l_orig_restriction_cur;
1209 END IF;
1210 IF l_orig_sort_cur%ISOPEN THEN
1211 CLOSE l_orig_sort_cur;
1212 END IF;
1213 -- Part of Bugfix 2279644
1214 IF l_orig_consistency_cur%ISOPEN THEN
1215 CLOSE l_orig_consistency_cur;
1216 END IF;
1217
1218 x_return_status := fnd_api.g_ret_sts_unexp_error;
1219 if fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error) then
1220 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1221 end if;
1222
1223 END copy_rule;
1224
1225 --Added to fix the Bug #3941280
1226 FUNCTION check_wms_rule_exists ( p_std_operation_id IN NUMBER )
1227 RETURN BOOLEAN
1228 AS
1229 l_exist NUMBER := 0;
1230 BEGIN
1231 BEGIN
1232 select 1
1233 into l_exist
1234 from wms_rules_b
1235 where type_hdr_id = p_std_operation_id
1236 and type_code = 3
1237 and rownum < 2 ;
1238
1239 EXCEPTION
1240 WHEN NO_DATA_FOUND THEN
1241 l_exist := 0;
1242 WHEN OTHERS THEN
1243 RAISE fnd_api.g_exc_unexpected_error;
1244 END;
1245
1246 IF l_exist = 1 THEN
1247 RETURN TRUE;
1248 ELSE
1249 RETURN FALSE;
1250 END IF;
1251
1252 END check_wms_rule_exists;
1253
1254 end WMS_RULE_FORM_PKG;