[Home] [Help]
PACKAGE BODY: APPS.WMS_RULES_PKG
Source
1 package body WMS_RULES_PKG as
2 /* $Header: WMSHPPRB.pls 120.0 2005/05/25 09:01:35 appldev noship $ */
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,
363
360 ATTRIBUTE15 = X_ATTRIBUTE15
361 ,ALLOCATION_MODE_ID = X_ALLOCATION_MODE_ID
362 where RULE_ID = X_RULE_ID;
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 begin
387 delete from WMS_RULES_TL
388 where RULE_ID = X_RULE_ID;
389
390 if (sql%notfound) then
391 raise no_data_found;
392 end if;
393
394 delete from WMS_RULES_B
395 where RULE_ID = X_RULE_ID;
396
397 if (sql%notfound) then
398 raise no_data_found;
399 end if;
400 end DELETE_ROW;
401
402 procedure ADD_LANGUAGE
403 is
404 begin
405 delete from WMS_RULES_TL T
406 where not exists
407 (select NULL
408 from WMS_RULES_B B
409 where B.RULE_ID = T.RULE_ID
410 );
411
412 update WMS_RULES_TL T set (
413 NAME,
414 DESCRIPTION
415 ) = (select
416 B.NAME,
417 B.DESCRIPTION
418 from WMS_RULES_TL B
419 where B.RULE_ID = T.RULE_ID
420 and B.LANGUAGE = T.SOURCE_LANG)
421 where (
422 T.RULE_ID,
423 T.LANGUAGE
424 ) in (select
425 SUBT.RULE_ID,
426 SUBT.LANGUAGE
427 from WMS_RULES_TL SUBB, WMS_RULES_TL SUBT
428 where SUBB.RULE_ID = SUBT.RULE_ID
429 and SUBB.LANGUAGE = SUBT.SOURCE_LANG
430 and (SUBB.NAME <> SUBT.NAME
431 or SUBB.DESCRIPTION <> SUBT.DESCRIPTION
432 or (SUBB.DESCRIPTION is null and SUBT.DESCRIPTION is not null)
433 or (SUBB.DESCRIPTION is not null and SUBT.DESCRIPTION is null)
434 ));
435
436 insert into WMS_RULES_TL (
437 RULE_ID,
438 LAST_UPDATED_BY,
439 LAST_UPDATE_DATE,
440 CREATED_BY,
441 CREATION_DATE,
442 LAST_UPDATE_LOGIN,
443 NAME,
444 DESCRIPTION,
445 LANGUAGE,
446 SOURCE_LANG
447 ) select
448 B.RULE_ID,
449 B.LAST_UPDATED_BY,
450 B.LAST_UPDATE_DATE,
451 B.CREATED_BY,
452 B.CREATION_DATE,
453 B.LAST_UPDATE_LOGIN,
454 B.NAME,
455 B.DESCRIPTION,
456 L.LANGUAGE_CODE,
457 B.SOURCE_LANG
458 from WMS_RULES_TL B, FND_LANGUAGES L
459 where L.INSTALLED_FLAG in ('I', 'B')
460 and B.LANGUAGE = userenv('LANG')
461 and not exists
462 (select NULL
463 from WMS_RULES_TL T
464 where T.RULE_ID = B.RULE_ID
465 and T.LANGUAGE = L.LANGUAGE_CODE);
466 end ADD_LANGUAGE;
467 --
468 procedure TRANSLATE_ROW
469 (x_rule_id IN VARCHAR2,
470 x_name IN VARCHAR2,
471 x_description IN VARCHAR2,
472 x_owner IN VARCHAR2
473 ) IS
474 BEGIN
475 UPDATE wms_rules_tl SET
476 name = x_name,
477 description = x_description,
478 last_update_date = Sysdate,
479 last_updated_by = Decode(x_owner, 'SEED', 1, 0),
480 last_update_login = 0,
481 source_lang = userenv('LANG')
482 WHERE rule_id = fnd_number.canonical_to_number(x_rule_id)
483 AND userenv('LANG') IN (language, source_lang);
484 END translate_row;
485 --
486 procedure LOAD_ROW (
487 x_rule_id IN VARCHAR2,
488 x_owner IN VARCHAR2,
489 x_organization_code IN VARCHAR2,
490 x_type_code IN VARCHAR2,
491 x_qty_function_parameter_id IN VARCHAR2,
492 x_enabled_flag IN VARCHAR2,
493 x_user_defined_flag IN VARCHAR2,
494 x_type_hdr_id IN VARCHAR2,
495 x_rule_weight IN VARCHAR2,
496 X_MIN_PICK_TASKS_FLAG IN VARCHAR2,
497 x_name IN VARCHAR2,
498 x_description in VARCHAR2,
499 x_attribute_category IN VARCHAR2,
500 x_attribute1 IN VARCHAR2,
501 x_attribute2 IN VARCHAR2,
502 x_attribute3 IN VARCHAR2,
503 x_attribute4 IN VARCHAR2,
504 x_attribute5 IN VARCHAR2,
505 x_attribute6 IN VARCHAR2,
506 x_attribute7 IN VARCHAR2,
507 x_attribute8 IN VARCHAR2,
508 x_attribute9 IN VARCHAR2,
509 x_attribute10 IN VARCHAR2,
510 x_attribute11 IN VARCHAR2,
511 x_attribute12 IN VARCHAR2,
512 x_attribute13 IN VARCHAR2,
513 x_attribute14 IN VARCHAR2,
514 x_attribute15 IN VARCHAR2
515 ,x_allocation_mode_id IN NUMBER
516 ) IS
517 BEGIN
518 DECLARE
519 l_rule_id NUMBER;
520 l_organization_id NUMBER;
521 l_qty_func_para_id NUMBER;
522 l_user_id NUMBER := 0 ;
523 l_row_id VARCHAR2(64);
524 BEGIN
525 IF (x_owner = 'SEED') THEN
526 l_user_id := 1;
527 END IF;
528 --
529 IF ( x_organization_code = '-1') THEN
530 l_organization_id := -1;
531 ELSE
532 SELECT organization_id INTO l_organization_id
533 FROM mtl_parameters
534 WHERE organization_code = x_organization_code;
535 END IF;
536 --
537 /* SELECT parameter_id INTO l_qty_func_para_id
538 FROM wms_parameters
539 WHERE name = x_qty_function_parameter_name; */
540 --
541 l_rule_id := fnd_number.canonical_to_number(x_rule_id);
542 l_qty_func_para_id := fnd_number.canonical_to_number(x_qty_function_parameter_id);
543 wms_rules_pkg.update_row
544 (
545 X_RULE_ID => l_rule_id
546 ,X_ATTRIBUTE11 => x_attribute11
547 ,X_ATTRIBUTE12 => x_attribute12
548 ,X_ORGANIZATION_ID => l_organization_id
549 ,X_TYPE_CODE => fnd_number.canonical_to_number(x_type_code)
550 ,X_QTY_FUNCTION_PARAMETER_ID => l_qty_func_para_id
551 ,X_ENABLED_FLAG => x_enabled_flag
552 ,X_USER_DEFINED_FLAG => x_user_defined_flag
553 ,X_MIN_PICK_TASKS_FLAG => x_min_pick_tasks_flag
554 ,X_ATTRIBUTE_CATEGORY => x_attribute_category
555 ,X_ATTRIBUTE1 => x_attribute1
556 ,X_ATTRIBUTE2 => x_attribute2
557 ,X_ATTRIBUTE3 => x_attribute3
558 ,X_ATTRIBUTE4 => x_attribute4
559 ,X_ATTRIBUTE5 => x_attribute5
560 ,X_ATTRIBUTE6 => x_attribute6
561 ,X_ATTRIBUTE7 => x_attribute7
562 ,X_ATTRIBUTE8 => x_attribute8
563 ,X_ATTRIBUTE9 => x_attribute9
564 ,X_ATTRIBUTE10 => x_attribute10
565 ,X_ATTRIBUTE13 => x_attribute13
566 ,X_ATTRIBUTE14 => x_attribute14
567 ,X_ATTRIBUTE15 => x_attribute15
568 ,X_NAME => x_name
569 ,X_DESCRIPTION => x_description
570 ,X_LAST_UPDATE_DATE => sysdate
571 ,X_LAST_UPDATED_BY => l_user_id
572 ,X_LAST_UPDATE_LOGIN => 0
573 ,X_TYPE_HEADER_ID => NULL
574 ,X_RULE_WEIGHT => NULL
575 ,X_ALLOCATION_MODE_ID => x_allocation_mode_id
576 );
577 EXCEPTION
578 WHEN no_data_found THEN
579 IF ( l_rule_id IS NULL ) THEN
580 SELECT wms_rules_s.NEXTVAL INTO l_rule_id FROM dual;
581 END IF;
582 wms_rules_pkg.insert_row
583 (
584 X_ROWID => l_row_id
585 ,X_RULE_ID => l_rule_id
586 ,X_ATTRIBUTE11 => x_attribute11
587 ,X_ATTRIBUTE12 => x_attribute12
588 ,X_ORGANIZATION_ID => l_organization_id
589 ,X_TYPE_CODE => fnd_number.canonical_to_number(x_type_code)
590 ,X_QTY_FUNCTION_PARAMETER_ID => l_qty_func_para_id
591 ,X_ENABLED_FLAG => x_enabled_flag
592 ,X_USER_DEFINED_FLAG => x_user_defined_flag
593 ,X_MIN_PICK_TASKS_FLAG => x_min_pick_tasks_flag
594 ,X_ATTRIBUTE_CATEGORY => x_attribute_category
595 ,X_ATTRIBUTE1 => x_attribute1
596 ,X_ATTRIBUTE2 => x_attribute2
597 ,X_ATTRIBUTE3 => x_attribute3
598 ,X_ATTRIBUTE4 => x_attribute4
599 ,X_ATTRIBUTE5 => x_attribute5
600 ,X_ATTRIBUTE6 => x_attribute6
601 ,X_ATTRIBUTE7 => x_attribute7
602 ,X_ATTRIBUTE8 => x_attribute8
603 ,X_ATTRIBUTE9 => x_attribute9
604 ,X_ATTRIBUTE10 => x_attribute10
605 ,X_ATTRIBUTE13 => x_attribute13
606 ,X_ATTRIBUTE14 => x_attribute14
607 ,X_ATTRIBUTE15 => x_attribute15
608 ,X_NAME => x_name
609 ,X_DESCRIPTION => x_description
610 ,X_CREATION_DATE => Sysdate
611 ,X_CREATED_BY => l_user_id
612 ,X_LAST_UPDATE_DATE => Sysdate
613 ,X_LAST_UPDATED_BY => l_user_id
614 ,X_LAST_UPDATE_LOGIN => 0
615 ,X_TYPE_HEADER_ID => NULL
616 ,X_RULE_WEIGHT => NULL
617 ,X_ALLOCATION_MODE_ID => x_allocation_mode_id
618 );
619 END;
620 END load_row;
621 end WMS_RULES_PKG;