[Home] [Help]
PACKAGE BODY: APPS.WMS_RULES_PKG
Source
1 package body WMS_RULES_PKG as
2 /* $Header: WMSHPPRB.pls 120.1 2011/03/24 10:44:10 avuppala ship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,
5 X_RULE_ID in NUMBER,
6 X_ORGANIZATION_ID in NUMBER,
7 X_TYPE_CODE in NUMBER,
8 X_NAME in VARCHAR2,
9 X_DESCRIPTION in VARCHAR2,
10 X_QTY_FUNCTION_PARAMETER_ID in NUMBER,
11 X_ENABLED_FLAG in VARCHAR2,
12 X_USER_DEFINED_FLAG in VARCHAR2,
13 X_MIN_PICK_TASKS_FLAG in VARCHAR2,
14 X_CREATION_DATE in DATE,
15 X_CREATED_BY in NUMBER,
16 X_LAST_UPDATE_DATE in DATE,
17 X_LAST_UPDATED_BY in NUMBER,
18 X_LAST_UPDATE_LOGIN in NUMBER,
19 X_TYPE_HEADER_ID in NUMBER,
20 X_RULE_WEIGHT in NUMBER,
21 X_ATTRIBUTE1 in VARCHAR2,
22 X_ATTRIBUTE2 in VARCHAR2,
23 X_ATTRIBUTE3 in VARCHAR2,
24 X_ATTRIBUTE4 in VARCHAR2,
25 X_ATTRIBUTE5 in VARCHAR2,
26 X_ATTRIBUTE6 in VARCHAR2,
27 X_ATTRIBUTE7 in VARCHAR2,
28 X_ATTRIBUTE8 in VARCHAR2,
29 X_ATTRIBUTE9 in VARCHAR2,
30 X_ATTRIBUTE10 in VARCHAR2,
31 X_ATTRIBUTE11 in VARCHAR2,
32 X_ATTRIBUTE12 in VARCHAR2,
33 X_ATTRIBUTE13 in VARCHAR2,
34 X_ATTRIBUTE14 in VARCHAR2,
35 X_ATTRIBUTE15 in VARCHAR2,
36 X_ATTRIBUTE_CATEGORY in VARCHAR2
37 ,X_ALLOCATION_MODE_ID in NUMBER
38 ,X_wms_enabled_flag in VARCHAR2 DEFAULT NULL
39 ) is
40 cursor C is select ROWID from WMS_RULES_B
41 where RULE_ID = X_RULE_ID ;
42 begin
43
44 insert into WMS_RULES_B (
45 ATTRIBUTE11,
46 ATTRIBUTE12,
47 RULE_ID,
48 ORGANIZATION_ID,
49 TYPE_CODE,
50 QTY_FUNCTION_PARAMETER_ID,
51 ENABLED_FLAG,
52 USER_DEFINED_FLAG,
53 MIN_PICK_TASKS_FLAG,
54 TYPE_HDR_ID,
55 RULE_WEIGHT,
56 ATTRIBUTE_CATEGORY,
57 ATTRIBUTE1,
58 ATTRIBUTE2,
59 ATTRIBUTE3,
60 ATTRIBUTE4,
61 ATTRIBUTE5,
62 ATTRIBUTE6,
63 ATTRIBUTE7,
64 ATTRIBUTE8,
65 ATTRIBUTE9,
66 ATTRIBUTE10,
67 ATTRIBUTE13,
68 ATTRIBUTE14,
69 ATTRIBUTE15,
70 CREATION_DATE,
71 CREATED_BY,
72 LAST_UPDATE_DATE,
73 LAST_UPDATED_BY,
74 LAST_UPDATE_LOGIN
75 ,ALLOCATION_MODE_ID
76 ,wms_enabled_flag
77 ) values (
78 X_ATTRIBUTE11,
79 X_ATTRIBUTE12,
80 X_RULE_ID,
81 X_ORGANIZATION_ID,
82 X_TYPE_CODE,
83 X_QTY_FUNCTION_PARAMETER_ID,
84 X_ENABLED_FLAG,
85 X_USER_DEFINED_FLAG,
86 X_MIN_PICK_TASKS_FLAG,
87 X_TYPE_HEADER_ID,
88 X_RULE_WEIGHT,
89 X_ATTRIBUTE_CATEGORY,
90 X_ATTRIBUTE1,
91 X_ATTRIBUTE2,
92 X_ATTRIBUTE3,
93 X_ATTRIBUTE4,
94 X_ATTRIBUTE5,
95 X_ATTRIBUTE6,
96 X_ATTRIBUTE7,
97 X_ATTRIBUTE8,
98 X_ATTRIBUTE9,
99 X_ATTRIBUTE10,
100 X_ATTRIBUTE13,
101 X_ATTRIBUTE14,
102 X_ATTRIBUTE15,
103 X_CREATION_DATE,
104 X_CREATED_BY,
105 X_LAST_UPDATE_DATE,
106 X_LAST_UPDATED_BY,
107 X_LAST_UPDATE_LOGIN
108 ,X_ALLOCATION_MODE_ID
109 ,X_wms_enabled_flag
110 );
111
112 insert into WMS_RULES_TL (
113 RULE_ID,
114 LAST_UPDATED_BY,
115 LAST_UPDATE_DATE,
116 CREATED_BY,
117 CREATION_DATE,
118 LAST_UPDATE_LOGIN,
119 NAME,
120 DESCRIPTION,
121 LANGUAGE,
122 SOURCE_LANG
123 ) select
124 X_RULE_ID,
125 X_LAST_UPDATED_BY,
126 X_LAST_UPDATE_DATE,
127 X_CREATED_BY,
128 X_CREATION_DATE,
129 X_LAST_UPDATE_LOGIN,
130 X_NAME,
131 X_DESCRIPTION,
132 L.LANGUAGE_CODE,
133 userenv('LANG')
134 from FND_LANGUAGES L
135 where L.INSTALLED_FLAG in ('I', 'B')
136 and not exists
137 (select NULL
138 from WMS_RULES_TL T
139 where T.RULE_ID = X_RULE_ID
140 and T.LANGUAGE = L.LANGUAGE_CODE);
141
142 open c;
143 fetch c into X_ROWID;
144 if (c%notfound) then
145 close c;
146 raise no_data_found;
147 end if;
148 close c;
149
150 end INSERT_ROW;
151 --
152 procedure LOCK_ROW (
153 X_RULE_ID in NUMBER,
154 X_ORGANIZATION_ID in NUMBER,
155 X_TYPE_CODE in NUMBER,
156 X_NAME in VARCHAR2,
157 X_DESCRIPTION in VARCHAR2,
158 X_QTY_FUNCTION_PARAMETER_ID in NUMBER,
159 X_ENABLED_FLAG in VARCHAR2,
160 X_USER_DEFINED_FLAG in VARCHAR2,
161 X_MIN_PICK_TASKS_FLAG in VARCHAR2,
162 X_TYPE_HEADER_ID in NUMBER,
163 X_RULE_WEIGHT in NUMBER,
164 X_ATTRIBUTE1 in VARCHAR2,
165 X_ATTRIBUTE2 in VARCHAR2,
166 X_ATTRIBUTE3 in VARCHAR2,
167 X_ATTRIBUTE4 in VARCHAR2,
168 X_ATTRIBUTE5 in VARCHAR2,
169 X_ATTRIBUTE6 in VARCHAR2,
170 X_ATTRIBUTE7 in VARCHAR2,
171 X_ATTRIBUTE8 in VARCHAR2,
172 X_ATTRIBUTE9 in VARCHAR2,
173 X_ATTRIBUTE10 in VARCHAR2,
174 X_ATTRIBUTE11 in VARCHAR2,
175 X_ATTRIBUTE12 in VARCHAR2,
176 X_ATTRIBUTE13 in VARCHAR2,
177 X_ATTRIBUTE14 in VARCHAR2,
178 X_ATTRIBUTE15 in VARCHAR2,
179 X_ATTRIBUTE_CATEGORY in VARCHAR2
180 ,X_ALLOCATION_MODE_ID in NUMBER
181 ) is
182 cursor c is select
183 ATTRIBUTE11,
184 ATTRIBUTE12,
185 ORGANIZATION_ID,
186 TYPE_CODE,
187 QTY_FUNCTION_PARAMETER_ID,
188 ENABLED_FLAG,
189 USER_DEFINED_FLAG,
190 MIN_PICK_TASKS_FLAG,
191 TYPE_HDR_ID,
192 RULE_WEIGHT,
193 ATTRIBUTE_CATEGORY,
194 ATTRIBUTE1,
195 ATTRIBUTE2,
196 ATTRIBUTE3,
197 ATTRIBUTE4,
198 ATTRIBUTE5,
199 ATTRIBUTE6,
200 ATTRIBUTE7,
201 ATTRIBUTE8,
202 ATTRIBUTE9,
203 ATTRIBUTE10,
204 ATTRIBUTE13,
205 ATTRIBUTE14,
206 ATTRIBUTE15
207 ,ALLOCATION_MODE_ID
208 from WMS_RULES_B
209 where RULE_ID = X_RULE_ID
210 for update of RULE_ID nowait;
211 recinfo c%rowtype;
212
213 cursor c1 is select
214 NAME,
215 DESCRIPTION,
216 decode(LANGUAGE, userenv('LANG'), 'Y', 'N') BASELANG
217 from WMS_RULES_TL
218 where RULE_ID = X_RULE_ID
219 and userenv('LANG') in (LANGUAGE, SOURCE_LANG)
220 for update of RULE_ID nowait;
221 begin
222 open c;
223 fetch c into recinfo;
224 if (c%notfound) then
225 close c;
226 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
227 app_exception.raise_exception;
228 end if;
229 close c;
230 if ( ((recinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
231 OR ((recinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
232 AND ((recinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
233 OR ((recinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
234 AND (recinfo.ORGANIZATION_ID = X_ORGANIZATION_ID)
235 AND (recinfo.TYPE_CODE = X_TYPE_CODE)
236 AND ((recinfo.QTY_FUNCTION_PARAMETER_ID = X_QTY_FUNCTION_PARAMETER_ID)
237 OR ((recinfo.QTY_FUNCTION_PARAMETER_ID is null) AND
238 (X_QTY_FUNCTION_PARAMETER_ID is null)))
239 AND (recinfo.ENABLED_FLAG = X_ENABLED_FLAG)
240 AND (recinfo.USER_DEFINED_FLAG = X_USER_DEFINED_FLAG)
241 AND (recinfo.MIN_PICK_TASKS_FLAG = X_MIN_PICK_TASKS_FLAG)
242 AND ((recinfo.TYPE_HDR_ID = X_TYPE_HEADER_ID)
243 OR ((recinfo.TYPE_HDR_ID is null) AND
244 (X_TYPE_HEADER_ID is null)))
245 AND ((recinfo.RULE_WEIGHT = X_RULE_WEIGHT)
246 OR ((recinfo.RULE_WEIGHT is null) AND
247 (X_RULE_WEIGHT is null)))
248 AND ((recinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
249 OR ((recinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
250 AND ((recinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
251 OR ((recinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
252 AND ((recinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
253 OR ((recinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
254 AND ((recinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
255 OR ((recinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
256 AND ((recinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
257 OR ((recinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
258 AND ((recinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
259 OR ((recinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
260 AND ((recinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
261 OR ((recinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
262 AND ((recinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
263 OR ((recinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
264 AND ((recinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
265 OR ((recinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
266 AND ((recinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
267 OR ((recinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
268 AND ((recinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
269 OR ((recinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
270 AND ((recinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
271 OR ((recinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
272 AND ((recinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
273 OR ((recinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
274 AND ((recinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
275 OR ((recinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
276 ) then
277 null;
278 else
279 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
280 app_exception.raise_exception;
281 end if;
282
283 for tlinfo in c1 loop
284 if (tlinfo.BASELANG = 'Y') then
285 if ( (tlinfo.NAME = X_NAME)
286 AND ((tlinfo.DESCRIPTION = X_DESCRIPTION)
287 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
288 ) then
289 null;
290 else
291 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
292 app_exception.raise_exception;
293 end if;
294 end if;
295 end loop;
296 return;
297 end LOCK_ROW;
298
299 procedure UPDATE_ROW (
300 X_RULE_ID in NUMBER,
301 X_ORGANIZATION_ID in NUMBER,
302 X_TYPE_CODE in NUMBER,
303 X_NAME in VARCHAR2,
304 X_DESCRIPTION in VARCHAR2,
305 X_QTY_FUNCTION_PARAMETER_ID in NUMBER,
306 X_ENABLED_FLAG in VARCHAR2,
307 X_USER_DEFINED_FLAG in VARCHAR2,
308 X_MIN_PICK_TASKS_FLAG in VARCHAR2,
309 X_LAST_UPDATED_BY in NUMBER,
310 X_LAST_UPDATE_DATE in DATE,
311 X_LAST_UPDATE_LOGIN in NUMBER,
312 X_TYPE_HEADER_ID in NUMBER,
313 X_RULE_WEIGHT in NUMBER,
314 X_ATTRIBUTE1 in VARCHAR2,
315 X_ATTRIBUTE2 in VARCHAR2,
316 X_ATTRIBUTE3 in VARCHAR2,
317 X_ATTRIBUTE4 in VARCHAR2,
318 X_ATTRIBUTE5 in VARCHAR2,
319 X_ATTRIBUTE6 in VARCHAR2,
320 X_ATTRIBUTE7 in VARCHAR2,
321 X_ATTRIBUTE8 in VARCHAR2,
322 X_ATTRIBUTE9 in VARCHAR2,
323 X_ATTRIBUTE10 in VARCHAR2,
324 X_ATTRIBUTE11 in VARCHAR2,
325 X_ATTRIBUTE12 in VARCHAR2,
326 X_ATTRIBUTE13 in VARCHAR2,
327 X_ATTRIBUTE14 in VARCHAR2,
328 X_ATTRIBUTE15 in VARCHAR2,
329 X_ATTRIBUTE_CATEGORY in VARCHAR2
330 ,X_ALLOCATION_MODE_ID in NUMBER
331 ) is
332 begin
333 update WMS_RULES_B set
334 ORGANIZATION_ID = X_ORGANIZATION_ID,
335 TYPE_CODE = X_TYPE_CODE,
336 QTY_FUNCTION_PARAMETER_ID = X_QTY_FUNCTION_PARAMETER_ID,
337 ENABLED_FLAG = X_ENABLED_FLAG,
338 USER_DEFINED_FLAG = X_USER_DEFINED_FLAG,
339 MIN_PICK_TASKS_FLAG = X_MIN_PICK_TASKS_FLAG,
340 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
341 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
342 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
343 TYPE_HDR_ID = X_TYPE_HEADER_ID,
344 RULE_WEIGHT = X_RULE_WEIGHT,
345 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
346 ATTRIBUTE1 = X_ATTRIBUTE1,
347 ATTRIBUTE2 = X_ATTRIBUTE2,
348 ATTRIBUTE3 = X_ATTRIBUTE3,
349 ATTRIBUTE4 = X_ATTRIBUTE4,
350 ATTRIBUTE5 = X_ATTRIBUTE5,
351 ATTRIBUTE6 = X_ATTRIBUTE6,
352 ATTRIBUTE7 = X_ATTRIBUTE7,
353 ATTRIBUTE8 = X_ATTRIBUTE8,
354 ATTRIBUTE9 = X_ATTRIBUTE9,
355 ATTRIBUTE10 = X_ATTRIBUTE10,
356 ATTRIBUTE11 = X_ATTRIBUTE11,
357 ATTRIBUTE12 = X_ATTRIBUTE12,
358 ATTRIBUTE13 = X_ATTRIBUTE13,
359 ATTRIBUTE14 = X_ATTRIBUTE14,
360 ATTRIBUTE15 = X_ATTRIBUTE15
361 ,ALLOCATION_MODE_ID = X_ALLOCATION_MODE_ID
362 where RULE_ID = X_RULE_ID;
363
364 if (sql%notfound) then
365 raise no_data_found;
366 end if;
367
368 update WMS_RULES_TL set
369 NAME = X_NAME,
370 DESCRIPTION = X_DESCRIPTION,
371 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
372 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
373 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
374 SOURCE_LANG = userenv('LANG')
375 where RULE_ID = X_RULE_ID
376 and userenv('LANG') in (LANGUAGE, SOURCE_LANG);
377
378 if (sql%notfound) then
379 raise no_data_found;
380 end if;
381 end UPDATE_ROW;
382
383 procedure DELETE_ROW (
384 X_RULE_ID in NUMBER
385 ) is
386 l_cursor INTEGER;
387 l_sql VARCHAR2(300) ;
388 l_package_name VARCHAR2(300);
389
390 begin
391 /* 11858604 Start */
392 l_cursor := DBMS_SQL.open_cursor;
393 l_package_name := 'WMS_RULE_' || x_rule_id;
394 l_sql := 'DROP PACKAGE ' || l_package_name ;
395 /* 11858604 End */
396 delete from WMS_RULES_TL
397 where RULE_ID = X_RULE_ID;
398
399 if (sql%notfound) then
400 raise no_data_found;
401 end if;
402
403 delete from WMS_RULES_B
404 where RULE_ID = X_RULE_ID;
405
406 if (sql%notfound) then
407 raise no_data_found;
408 end if;
409 /* 11858604 Start */
410 --parse cursor
411 DBMS_SQL.parse(l_cursor, l_sql, DBMS_SQL.native);
412 --close cursor
413 DBMS_SQL.close_cursor(l_cursor);
414 EXCEPTION
415 WHEN OTHERS THEN
416 DBMS_SQL.close_cursor(l_cursor);
417 /* 11858604 End */
418 end DELETE_ROW;
419
420 procedure ADD_LANGUAGE
421 is
422 begin
423 delete from WMS_RULES_TL T
424 where not exists
425 (select NULL
426 from WMS_RULES_B B
427 where B.RULE_ID = T.RULE_ID
428 );
429
430 update WMS_RULES_TL T set (
431 NAME,
432 DESCRIPTION
433 ) = (select
434 B.NAME,
435 B.DESCRIPTION
436 from WMS_RULES_TL B
437 where B.RULE_ID = T.RULE_ID
438 and B.LANGUAGE = T.SOURCE_LANG)
439 where (
440 T.RULE_ID,
441 T.LANGUAGE
442 ) in (select
443 SUBT.RULE_ID,
444 SUBT.LANGUAGE
445 from WMS_RULES_TL SUBB, WMS_RULES_TL SUBT
446 where SUBB.RULE_ID = SUBT.RULE_ID
447 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
448 and (SUBB.NAME <> SUBT.NAME
449 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
450 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
451 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
452 ));
453
454 insert into WMS_RULES_TL (
455 RULE_ID,
456 LAST_UPDATED_BY,
457 LAST_UPDATE_DATE,
458 CREATED_BY,
459 CREATION_DATE,
460 LAST_UPDATE_LOGIN,
461 NAME,
462 DESCRIPTION,
463 LANGUAGE,
464 SOURCE_LANG
465 ) select
466 B.RULE_ID,
467 B.LAST_UPDATED_BY,
468 B.LAST_UPDATE_DATE,
469 B.CREATED_BY,
470 B.CREATION_DATE,
471 B.LAST_UPDATE_LOGIN,
472 B.NAME,
473 B.DESCRIPTION,
474 L.LANGUAGE_CODE,
475 B.SOURCE_LANG
476 from WMS_RULES_TL B, FND_LANGUAGES L
477 where L.INSTALLED_FLAG in ('I', 'B')
478 and B.LANGUAGE = userenv('LANG')
479 and not exists
480 (select NULL
481 from WMS_RULES_TL T
482 where T.RULE_ID = B.RULE_ID
483 and T.LANGUAGE = L.LANGUAGE_CODE);
484 end ADD_LANGUAGE;
485 --
486 procedure TRANSLATE_ROW
487 (x_rule_id IN VARCHAR2,
488 x_name IN VARCHAR2,
489 x_description IN VARCHAR2,
490 x_owner IN VARCHAR2
491 ) IS
492 BEGIN
493 UPDATE wms_rules_tl SET
494 name = x_name,
495 description = x_description,
496 last_update_date = Sysdate,
497 last_updated_by = Decode(x_owner, 'SEED', 1, 0),
498 last_update_login = 0,
499 source_lang = userenv('LANG')
500 WHERE rule_id = fnd_number.canonical_to_number(x_rule_id)
501 AND userenv('LANG') IN (language, source_lang);
502 END translate_row;
503 --
504 procedure LOAD_ROW (
505 x_rule_id IN VARCHAR2,
506 x_owner IN VARCHAR2,
507 x_organization_code IN VARCHAR2,
508 x_type_code IN VARCHAR2,
509 x_qty_function_parameter_id IN VARCHAR2,
510 x_enabled_flag IN VARCHAR2,
511 x_user_defined_flag IN VARCHAR2,
512 x_type_hdr_id IN VARCHAR2,
513 x_rule_weight IN VARCHAR2,
514 X_MIN_PICK_TASKS_FLAG IN VARCHAR2,
515 x_name IN VARCHAR2,
516 x_description in VARCHAR2,
517 x_attribute_category IN VARCHAR2,
518 x_attribute1 IN VARCHAR2,
519 x_attribute2 IN VARCHAR2,
520 x_attribute3 IN VARCHAR2,
521 x_attribute4 IN VARCHAR2,
522 x_attribute5 IN VARCHAR2,
523 x_attribute6 IN VARCHAR2,
524 x_attribute7 IN VARCHAR2,
525 x_attribute8 IN VARCHAR2,
526 x_attribute9 IN VARCHAR2,
527 x_attribute10 IN VARCHAR2,
528 x_attribute11 IN VARCHAR2,
529 x_attribute12 IN VARCHAR2,
530 x_attribute13 IN VARCHAR2,
531 x_attribute14 IN VARCHAR2,
532 x_attribute15 IN VARCHAR2
533 ,x_allocation_mode_id IN NUMBER
534 ) IS
535 BEGIN
536 DECLARE
537 l_rule_id NUMBER;
538 l_organization_id NUMBER;
539 l_qty_func_para_id NUMBER;
540 l_user_id NUMBER := 0 ;
541 l_row_id VARCHAR2(64);
542 BEGIN
543 IF (x_owner = 'SEED') THEN
544 l_user_id := 1;
545 END IF;
546 --
547 IF ( x_organization_code = '-1') THEN
548 l_organization_id := -1;
549 ELSE
550 SELECT organization_id INTO l_organization_id
551 FROM mtl_parameters
552 WHERE organization_code = x_organization_code;
553 END IF;
554 --
555 /* SELECT parameter_id INTO l_qty_func_para_id
556 FROM wms_parameters
557 WHERE name = x_qty_function_parameter_name; */
558 --
559 l_rule_id := fnd_number.canonical_to_number(x_rule_id);
560 l_qty_func_para_id := fnd_number.canonical_to_number(x_qty_function_parameter_id);
561 wms_rules_pkg.update_row
562 (
563 X_RULE_ID => l_rule_id
564 ,X_ATTRIBUTE11 => x_attribute11
565 ,X_ATTRIBUTE12 => x_attribute12
566 ,X_ORGANIZATION_ID => l_organization_id
567 ,X_TYPE_CODE => fnd_number.canonical_to_number(x_type_code)
568 ,X_QTY_FUNCTION_PARAMETER_ID => l_qty_func_para_id
569 ,X_ENABLED_FLAG => x_enabled_flag
570 ,X_USER_DEFINED_FLAG => x_user_defined_flag
571 ,X_MIN_PICK_TASKS_FLAG => x_min_pick_tasks_flag
572 ,X_ATTRIBUTE_CATEGORY => x_attribute_category
573 ,X_ATTRIBUTE1 => x_attribute1
574 ,X_ATTRIBUTE2 => x_attribute2
575 ,X_ATTRIBUTE3 => x_attribute3
576 ,X_ATTRIBUTE4 => x_attribute4
577 ,X_ATTRIBUTE5 => x_attribute5
578 ,X_ATTRIBUTE6 => x_attribute6
579 ,X_ATTRIBUTE7 => x_attribute7
580 ,X_ATTRIBUTE8 => x_attribute8
581 ,X_ATTRIBUTE9 => x_attribute9
582 ,X_ATTRIBUTE10 => x_attribute10
583 ,X_ATTRIBUTE13 => x_attribute13
584 ,X_ATTRIBUTE14 => x_attribute14
585 ,X_ATTRIBUTE15 => x_attribute15
586 ,X_NAME => x_name
587 ,X_DESCRIPTION => x_description
588 ,X_LAST_UPDATE_DATE => sysdate
589 ,X_LAST_UPDATED_BY => l_user_id
590 ,X_LAST_UPDATE_LOGIN => 0
591 ,X_TYPE_HEADER_ID => NULL
592 ,X_RULE_WEIGHT => NULL
593 ,X_ALLOCATION_MODE_ID => x_allocation_mode_id
594 );
595 EXCEPTION
596 WHEN no_data_found THEN
597 IF ( l_rule_id IS NULL ) THEN
598 SELECT wms_rules_s.NEXTVAL INTO l_rule_id FROM dual;
599 END IF;
600 wms_rules_pkg.insert_row
601 (
602 X_ROWID => l_row_id
603 ,X_RULE_ID => l_rule_id
604 ,X_ATTRIBUTE11 => x_attribute11
605 ,X_ATTRIBUTE12 => x_attribute12
606 ,X_ORGANIZATION_ID => l_organization_id
607 ,X_TYPE_CODE => fnd_number.canonical_to_number(x_type_code)
608 ,X_QTY_FUNCTION_PARAMETER_ID => l_qty_func_para_id
609 ,X_ENABLED_FLAG => x_enabled_flag
610 ,X_USER_DEFINED_FLAG => x_user_defined_flag
611 ,X_MIN_PICK_TASKS_FLAG => x_min_pick_tasks_flag
612 ,X_ATTRIBUTE_CATEGORY => x_attribute_category
613 ,X_ATTRIBUTE1 => x_attribute1
614 ,X_ATTRIBUTE2 => x_attribute2
615 ,X_ATTRIBUTE3 => x_attribute3
616 ,X_ATTRIBUTE4 => x_attribute4
617 ,X_ATTRIBUTE5 => x_attribute5
618 ,X_ATTRIBUTE6 => x_attribute6
619 ,X_ATTRIBUTE7 => x_attribute7
620 ,X_ATTRIBUTE8 => x_attribute8
621 ,X_ATTRIBUTE9 => x_attribute9
622 ,X_ATTRIBUTE10 => x_attribute10
623 ,X_ATTRIBUTE13 => x_attribute13
624 ,X_ATTRIBUTE14 => x_attribute14
625 ,X_ATTRIBUTE15 => x_attribute15
626 ,X_NAME => x_name
627 ,X_DESCRIPTION => x_description
628 ,X_CREATION_DATE => Sysdate
629 ,X_CREATED_BY => l_user_id
630 ,X_LAST_UPDATE_DATE => Sysdate
631 ,X_LAST_UPDATED_BY => l_user_id
632 ,X_LAST_UPDATE_LOGIN => 0
633 ,X_TYPE_HEADER_ID => NULL
634 ,X_RULE_WEIGHT => NULL
635 ,X_ALLOCATION_MODE_ID => x_allocation_mode_id
636 );
637 END;
638 END load_row;
639 end WMS_RULES_PKG;