[Home] [Help]
PACKAGE BODY: APPS.INV_RULE_GEN_PVT
Source
1 PACKAGE BODY inv_rule_gen_pvt AS
2 /* $Header: INVRLGNB.pls 120.11.12020000.2 2012/07/09 08:16:11 asugandh ship $ */
3 --
4 -- File : INVRLGNB.pls
5 -- Content : INV_RULE_GEN_PVT
6 -- Description : wms rules engine private API's
7 -- Notes :
8 -- Modified : 08/30/04 ckuenzel created orginal file in inventory
9 --
10
11 /* Global variables */
12 G_PKG_NAME CONSTANT VARCHAR2(30):='INV_RULE_GEN_PVT';
13 /* Save procedure will save the record to the following
14 Mtl_picking_rules
15 Wms_rules_b
16 Wms_rules_tl
17 Wms_restrictions - if any
18 Wms_rule_consistencies - if any
19 Wms_sort_criteria - if any
20 */
21 --Procedures for logging messages
22 PROCEDURE debug(p_message VARCHAR2) IS
23 l_module VARCHAR2(255);
24 BEGIN
25 --l_module := 'inv.plsql.' || g_pkg_name || '.' || p_api_name || '.' || p_label;
26 inv_log_util.trace(p_message, g_pkg_name, 9);
27 gmi_reservation_util.println(l_module ||' '|| p_message);
28 END debug;
29
30
31 PROCEDURE Save
32 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
33 , x_return_status OUT NOCOPY VARCHAR2
34 , x_msg_data OUT NOCOPY VARCHAR2
35 , x_msg_count OUT NOCOPY NUMBER
36 ) IS
37 l_api_name CONSTANT VARCHAR2 (30) := 'SAVE';
38 l_parameter_id NUMBER;
39 l_is_new_rec NUMBER;
40 cursor check_exist(p_inv_rule_id IN NUMBER) is
41 Select 1
42 From mtl_inv_picking_rules
43 Where inv_rule_id = p_inv_rule_id;
44
45 BEGIN
46 x_return_status := FND_API.G_RET_STS_SUCCESS;
47 debug('Procedure Save');
48 l_is_new_rec := 1;
49 if p_mtl_picking_rule_rec.inv_rule_id is not null then
50 l_is_new_rec := 0;
51 end if;
52 debug('new record ? 1-y,0-n: '||l_is_new_rec);
53
54 save_to_wms_rule
55 (
56 p_mtl_picking_rule_rec => p_mtl_picking_rule_rec
57 , x_return_status => x_return_status
58 , x_msg_data => x_msg_data
59 , x_msg_count => x_msg_count
60 );
61
62 save_to_mtl_picking_rules
63 (
64 p_mtl_picking_rule_rec => p_mtl_picking_rule_rec
65 , x_return_status => x_return_status
66 , x_msg_data => x_msg_data
67 , x_msg_count => x_msg_count
68 );
69
70
71 If l_is_new_rec = 1 then
72 inv_rule_gen_pvt.Restrictions_insert
73 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
74 , x_return_status => x_return_status
75 , x_msg_data => x_msg_data
76 , x_msg_count => x_msg_count
77 );
78 inv_rule_gen_pvt.consistency_insert
79 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
80 , x_return_status => x_return_status
81 , x_msg_data => x_msg_data
82 , x_msg_count => x_msg_count
83 );
84 inv_rule_gen_pvt.sorting_criteria_insert
85 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
86 , x_return_status => x_return_status
87 , x_msg_data => x_msg_data
88 , x_msg_count => x_msg_count
89 );
90 Else
91 inv_rule_gen_pvt.restrictions_update
92 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
93 , x_return_status => x_return_status
94 , x_msg_data => x_msg_data
95 , x_msg_count => x_msg_count
96 );
97 inv_rule_gen_pvt.consistency_update
98 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
99 , x_return_status => x_return_status
100 , x_msg_data => x_msg_data
101 , x_msg_count => x_msg_count
102 );
103
104 inv_rule_gen_pvt.sorting_criteria_update
105 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
106 , x_return_status => x_return_status
107 , x_msg_data => x_msg_data
108 , x_msg_count => x_msg_count
109 );
110 END IF;
111 EXCEPTION
112 WHEN FND_API.G_EXC_ERROR THEN
113 x_return_status := FND_API.G_RET_STS_ERROR;
114 FND_MSG_Pub.count_and_get
115 ( p_count => x_msg_count
116 , p_data => x_msg_data
117 );
118 debug('save. error exception');
119 WHEN OTHERS THEN
120 debug('save others'||SQLCODE||'.');
121 x_return_status := SQLCODE;
122 FND_MSG_PUB.Add_Exc_Msg ( g_pkg_name
123 , l_api_name
124 );
125 /* Get message count and data */
126 FND_MSG_Pub.count_and_get
127 ( p_count => x_msg_count
128 , p_data => x_msg_data
129 );
130 END save;
131
132 PROCEDURE Save_to_mtl_picking_rules
133 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
134 , x_return_status OUT NOCOPY VARCHAR2
135 , x_msg_data OUT NOCOPY VARCHAR2
136 , x_msg_count OUT NOCOPY NUMBER)
137 IS
138 l_api_name CONSTANT VARCHAR2 (30) := 'Save_to_mtl_picking_rules';
139 l_inv_rule_id NUMBER;
140 BEGIN
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142 l_inv_rule_id := p_mtl_picking_rule_rec.inv_rule_id;
143 debug('Procedure Save_to_mtl_picking_rules');
144 If l_inv_rule_id is null then
145 select mtl_inv_picking_rules_s.nextval into l_inv_Rule_id from dual;
146 debug('insert rule: rule_id '||l_inv_rule_id);
147 debug('insert rule: fnd_global.user_id '||fnd_global.user_id);
148 Insert into mtl_inv_picking_rules
149 (
150 INV_RULE_ID
151 , SHELF_DAYS
152 , SINGLE_LOT
153 , PARTIAL_ALLOWED_FLAG
154 , CUST_SPEC_MATCH_FLAG
155 , LOT_SORT
156 , LOT_SORT_RANK
157 , REVISION_SORT
158 , REVISION_SORT_RANK
159 , SUBINVENTORY_SORT
160 , SUBINVENTORY_SORT_RANK
161 , LOCATOR_SORT
162 , LOCATOR_SORT_RANK
163 , WMS_RULE_ID
164 , WMS_STRATEGY_ID
165 , APPLY_TO_SOURCE
166 , CREATION_DATE
167 , CREATED_BY
168 , LAST_UPDATE_DATE
169 , LAST_UPDATED_BY
170 , LAST_UPDATE_LOGIN
171 , PROGRAM_APPLICATION_ID
172 , PROGRAM_ID
173 , REQUEST_ID
174 )
175 values
176 (
177 l_inv_rule_id
178 , p_mtl_picking_rule_rec.shelf_days
179 , p_mtl_picking_rule_rec.single_lot
180 , p_mtl_picking_rule_rec.partial_allowed_flag
181 , p_mtl_picking_rule_rec.cust_spec_match_flag
182 , p_mtl_picking_rule_rec.lot_sort
183 , p_mtl_picking_rule_rec.lot_sort_rank
184 , p_mtl_picking_rule_rec.revision_sort
185 , p_mtl_picking_rule_rec.revision_sort_rank
186 , p_mtl_picking_rule_rec.subinventory_sort
187 , p_mtl_picking_rule_rec.subinventory_sort_rank
188 , p_mtl_picking_rule_rec.locator_sort
189 , p_mtl_picking_rule_rec.locator_sort_rank
190 , p_mtl_picking_rule_rec.wms_rule_id
191 , p_mtl_picking_rule_rec.wms_strategy_id
192 , p_mtl_picking_rule_rec.apply_to_source
193 , sysdate
194 , fnd_global.user_id
195 , sysdate
196 , fnd_global.user_id
197 , fnd_global.login_id
198 , null
199 , null
200 , null
201 );
202 p_mtl_picking_rule_rec.inv_rule_id := l_inv_rule_id;
203 debug('insert, inv_rule_id '||l_inv_rule_id);
204 else -- update
205 update mtl_inv_picking_rules
206 set
207 SHELF_DAYS = p_mtl_picking_rule_rec.shelf_days
208 , SINGLE_LOT = p_mtl_picking_rule_rec.single_lot
209 , PARTIAL_ALLOWED_FLAG = p_mtl_picking_rule_rec.partial_allowed_flag
210 , CUST_SPEC_MATCH_FLAG = p_mtl_picking_rule_rec.cust_spec_match_flag
211 , LOT_SORT = p_mtl_picking_rule_rec.lot_sort
212 , LOT_SORT_RANK = p_mtl_picking_rule_rec.lot_sort_rank
213 , REVISION_SORT = p_mtl_picking_rule_rec.revision_sort
214 , REVISION_SORT_RANK = p_mtl_picking_rule_rec.revision_sort_rank
215 , SUBINVENTORY_SORT = p_mtl_picking_rule_rec.subinventory_sort
216 , SUBINVENTORY_SORT_RANK = p_mtl_picking_rule_rec.subinventory_sort_rank
217 , LOCATOR_SORT = p_mtl_picking_rule_rec.locator_sort
218 , LOCATOR_SORT_RANK = p_mtl_picking_rule_rec.locator_sort_rank
219 , WMS_RULE_ID = p_mtl_picking_rule_rec.wms_rule_id
220 , WMS_STRATEGY_ID = NULL /* p_mtl_picking_rule_rec.wms_strategy_id */
221 , APPLY_TO_SOURCE = p_mtl_picking_rule_rec.apply_to_source
222 , LAST_UPDATE_DATE = sysdate
223 , LAST_UPDATED_BY = fnd_global.user_id
224 , LAST_UPDATE_LOGIN = fnd_global.login_id
225 where inv_rule_id = l_inv_rule_id;
226 end if;
227 EXCEPTION
228 WHEN FND_API.G_EXC_ERROR THEN
229 x_return_status := FND_API.G_RET_STS_ERROR;
230 FND_MSG_Pub.count_and_get
231 ( p_count => x_msg_count
232 , p_data => x_msg_data
233 );
234 WHEN OTHERS THEN
235 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
236 x_return_status := SQLCODE;
237 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
238 , l_api_name
239 );
240 /* Get message count and data */
241 FND_MSG_Pub.count_and_get
242 ( p_count => x_msg_count
243 , p_data => x_msg_data
244 );
245 END save_to_mtl_picking_rules;
246
247 PROCEDURE Save_to_wms_rule
248 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
249 , x_return_status OUT NOCOPY VARCHAR2
250 , x_msg_data OUT NOCOPY VARCHAR2
251 , x_msg_count OUT NOCOPY NUMBER
252 ) IS
253 l_api_name CONSTANT VARCHAR2 (30) := 'Save_to_wms_rule';
254 l_picking_rule_rec INV_RULE_GEN_PVT.picking_rule_rec;
255 l_wms_rule_id NUMBER;
256 l_row_id VARCHAR2(500);
257 l_organization_id NUMBER;
258 l_allocation_mode_id NUMBER;
259
260 BEGIN
261 x_return_status := FND_API.G_RET_STS_SUCCESS;
262 l_picking_rule_rec := p_mtl_picking_rule_rec;
263 l_organization_id := -1; -- all orgs
264 l_allocation_mode_id := 3; -- NON LPN
265 l_wms_rule_id := p_mtl_picking_rule_rec.wms_rule_id;
266 debug('Save_to_wms_rule ');
267 debug('wms_rule_id '||l_wms_rule_id);
268 If l_wms_rule_id is null then
269 select wms_rules_s.nextval into l_wms_rule_Id from dual;
270 debug('insert wms rule ');
271 wms_RULES_PKG.Insert_Row
272 (
273 X_Rowid => l_Row_Id,
274 X_Rule_Id => l_wms_rule_Id,
275 X_Organization_Id => l_Organization_Id,
276 X_Type_Code => 2,
277 X_Name => l_picking_rule_rec.Name,
278 X_Description => l_picking_rule_rec.Description,
279 X_Qty_Function_Parameter_Id => 10009,
280 X_Enabled_Flag => l_picking_rule_rec.Enabled_Flag,
281 X_min_pick_tasks_flag => 'N',
282 X_User_Defined_Flag => 'Y',
283 X_Creation_Date => sysdate,
284 X_Created_By => fnd_global.user_id,
285 X_Last_Update_Date => sysdate,
286 X_Last_Updated_By => fnd_global.user_id,
287 X_Last_Update_Login => fnd_global.login_id,
288 X_Type_header_id => null,
289 X_Rule_Weight => null,
290 X_Attribute1 => null,
291 X_Attribute2 => null,
292 X_Attribute3 => null,
293 X_Attribute4 => null,
294 X_Attribute5 => null,
295 X_Attribute6 => null,
296 X_Attribute7 => null,
297 X_Attribute8 => null,
298 X_Attribute9 => null,
299 X_Attribute10 => null,
300 X_Attribute11 => null,
301 X_Attribute12 => null,
302 X_Attribute13 => null,
303 X_Attribute14 => null,
304 X_Attribute15 => null,
305 X_Attribute_Category => null,
306 X_Allocation_mode_id => l_Allocation_mode_id,
307 X_wms_enabled_flag => 'N'
308 );
309
310 /* update the rec */
311 l_picking_rule_rec.wms_rule_id := l_wms_rule_id;
312 debug('insert wms_rule_id '||l_wms_rule_id);
313 else -- update
314 debug('update wms rule ');
315 debug('update wms rule, enabled_flag '||l_picking_rule_rec.enabled_flag);
316 wms_RULES_PKG.Update_Row
317 (
318 X_Rule_Id => l_picking_rule_rec.wms_Rule_Id,
319 X_Organization_Id => l_Organization_Id,
320 X_Type_Code => 2,
321 X_Name => l_picking_rule_rec.Name,
322 X_Description => l_picking_rule_rec.Description,
323 X_Qty_Function_Parameter_Id => 10009,
324 X_Enabled_Flag => l_picking_rule_rec.Enabled_Flag,
325 X_User_Defined_Flag => 'Y',
326 X_min_pick_tasks_flag => 'N',
327 X_Last_Updated_By => fnd_global.user_id,
328 X_Last_Update_Date => sysdate,
329 X_Last_Update_Login => fnd_global.user_id,
330 X_Type_header_id => null,
331 X_Rule_Weight => null,
332 X_Attribute1 => null,
333 X_Attribute2 => null,
334 X_Attribute3 => null,
335 X_Attribute4 => null,
336 X_Attribute5 => null,
337 X_Attribute6 => null,
338 X_Attribute7 => null,
339 X_Attribute8 => null,
340 X_Attribute9 => null,
341 X_Attribute10 => null,
342 X_Attribute11 => null,
343 X_Attribute12 => null,
344 X_Attribute13 => null,
345 X_Attribute14 => null,
346 X_Attribute15 => null,
347 X_Attribute_Category => null,
348 X_Allocation_mode_id => l_Allocation_mode_id
349 );
350 end if;
351
352 l_picking_rule_rec.created_by := fnd_global.user_id;
353 l_picking_rule_rec.last_updated_by := fnd_global.user_id;
354 l_picking_rule_rec.last_update_login := fnd_global.login_id;
355 l_picking_rule_rec.creation_date := sysdate;
356 l_picking_rule_rec.last_update_date := sysdate;
357
358 p_mtl_picking_rule_rec := l_picking_rule_rec;
359
360 EXCEPTION
361 WHEN FND_API.G_EXC_ERROR THEN
362 x_return_status := FND_API.G_RET_STS_ERROR;
363 FND_MSG_Pub.count_and_get
364 ( p_count => x_msg_count
365 , p_data => x_msg_data
366 );
367 WHEN OTHERS THEN
368 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
369 x_return_status := SQLCODE;
370 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
371 , l_api_name
372 );
373 /* Get message count and data */
374 FND_MSG_Pub.count_and_get
375 ( p_count => x_msg_count
376 , p_data => x_msg_data
377 );
378 END save_to_wms_rule;
379
380 PROCEDURE Restrictions_insert
381 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
382 , x_return_status OUT NOCOPY VARCHAR2
383 , x_msg_data OUT NOCOPY VARCHAR2
384 , x_msg_count OUT NOCOPY NUMBER)
385 IS
386
387 l_api_name CONSTANT VARCHAR2 (30) := 'Restrictions_insert';
388 l_picking_rule_rec INV_RULE_GEN_PVT.picking_rule_rec;
389 l_parameter_id NUMBER;
390 l_Row_Id VARCHAR2(500) ;
391 l_Sequence_Number NUMBER;
392 l_shelf_days NUMBER;
393 l_Operator_Code NUMBER;
394 l_Operand_Type_Code NUMBER;
395 l_Operand_Constant_Number NUMBER ;
396 l_Operand_Constant_Character VARCHAR2(50);
397 l_Operand_Constant_Date DATE;
398 l_Operand_Parameter_Id NUMBER;
399 l_Operand_Expression VARCHAR2(500);
400 l_Operand_Flex_Value_Set_Id NUMBER;
401 l_Logical_Operator_Code NUMBER;
402 l_Bracket_Open VARCHAR2(3);
403 l_Bracket_Close VARCHAR2(3);
404 l_apply_to_source NUMBER;
405 l_go_ahead NUMBER;
406 l_is_mo_line NUMBER;
407 i NUMBER;
408
409 BEGIN
410 /* total 3 restrictions
411 * 1) shelf days
412 * 2) grade for SO
413 * 3) customer spec match for SO
414 */
415 x_return_status := FND_API.G_RET_STS_SUCCESS;
416 l_picking_rule_rec := p_mtl_picking_rule_rec;
417 l_apply_to_source := l_picking_rule_rec.apply_to_source;
418
419 For i in 1..8 Loop
420 l_go_ahead := 0;
421 debug('Procedure restrictions_insert i loop '||i);
422 debug('source to apply '||l_apply_to_source);
423 /* shelf days */
424 IF (i = 1 AND (p_mtl_picking_rule_rec.shelf_days is not null) ) then -- shelf days
425 l_shelf_days := p_mtl_picking_rule_rec.shelf_days;
426 l_sequence_number := 10;
427 debug('shelf days '||l_shelf_days);
428 If l_apply_to_source = 1 then -- SO
429 /* SO.schedule_ship_date */
430 l_parameter_id := 60018; -- lot.expiration_date
431 l_operand_parameter_id := 60185; -- SO.schedule_ship_date
432 l_operator_code := '5'; -- '>='
433 l_operand_type_code := '4'; -- ?
434 l_operand_expression := '+'||to_char(l_shelf_days); -- '+ shelf days'
435 l_operand_constant_number := null;
436 l_operand_constant_character := null;
437 l_operand_constant_date := null;
438 l_logical_operator_code := null;
439 l_bracket_open := '(';
440 l_bracket_close := null;
441
442 l_go_ahead := 1;
443 elsif l_apply_to_source = 2 then -- GME
444 /* GMEMD.plan_start_date */
445 l_parameter_id := 60018; -- the lot.expiration_date
446 l_operand_parameter_id := 5001003; -- gmebh.plan_start_date
447 l_operator_code := '5'; -- '>='
448 l_operand_type_code := '4'; -- ?
449 l_operand_expression := '+'||to_char(l_shelf_days); -- '+ shelf days'
450 l_operand_constant_number := null;
451 l_operand_constant_character := null;
452 l_operand_constant_date := null;
453 l_logical_operator_code := null;
454 l_bracket_open := '(';
455 l_bracket_close := null;
456
457 l_go_ahead := 1;
458 elsif l_apply_to_source = 3 then -- WIP
459 /* Lot.date */
460 l_parameter_id := 60018; -- lot.expiration_date
461 l_operand_parameter_id := 10010; -- current date
462 l_operator_code := '5'; -- '>='
463 l_operand_type_code := '4'; -- ?
464 l_operand_expression := '+'||to_char(l_shelf_days); -- '+ shelf days'
465 l_operand_constant_number := null;
466 l_operand_constant_character := null;
467 l_operand_constant_date := null;
468 l_logical_operator_code := null;
469 l_bracket_open := '(';
470 l_bracket_close := null;
471
472 l_go_ahead := 1;
473 elsif l_apply_to_source IS NULL then -- MO line
474 l_parameter_id := 60018; -- lot.expiration_date
475 l_operand_parameter_id := 60193; -- mo_date_required
476 l_operator_code := '5'; -- '>='
477 l_operand_type_code := '4'; -- ?
478 l_operand_expression := '+'||to_char(l_shelf_days); -- '+ shelf days'
479 l_operand_constant_number := null;
480 l_operand_constant_character := null;
481 l_operand_constant_date := null;
482 l_logical_operator_code := null;
483 l_bracket_open := '(';
484 l_bracket_close := null;
485
486 l_is_mo_line := 1;
487
488 l_go_ahead := 1;
489 end if;
490 END IF;
491
492 /* shelf days */
493 IF (i = 8 AND (p_mtl_picking_rule_rec.shelf_days is not null) ) then -- shelf days
494 debug('Adding OR ..');
495
496 l_sequence_number := 15;
497 l_parameter_id := 60018; -- lot.expiration_date
498 l_operand_parameter_id := null;
499 l_operator_code := '11'; -- 'is null'
500 l_operand_type_code := '7'; -- ?
501 l_operand_expression := null;
502 l_operand_constant_number := null;
503 l_operand_constant_character := null;
504 l_operand_constant_date := null;
505 l_logical_operator_code := 2; -- 'OR'
506 l_bracket_open := null;
507 l_bracket_close := ')';
508
509 l_go_ahead := 1;
510 debug('for shelf days ?'||l_go_ahead);
511 END IF;
512
513 /* grade for SO */
514 IF p_mtl_picking_rule_rec.apply_to_source = 1
515 and nvl(p_mtl_picking_rule_rec.cust_spec_match_flag,'N') = 'N'
516 THEN
517 IF i = 2 THEN -- grade rule
518 debug('grade rule ');
519 -- always insert the grade rule
520 l_sequence_number := 20;
521 l_parameter_id := 60183; -- SO.preferred_grade
522 l_operand_parameter_id := 60141; -- lot.grade_code
523 l_operator_code := '3'; -- '='
524 l_operand_type_code := '4'; -- ?
525 l_operand_expression := null;
526 l_operand_constant_number := null;
527 l_operand_constant_character := null;
528 l_operand_constant_date := null;
529 l_logical_operator_code := null;
530 l_bracket_open := '((';
531 l_bracket_close := null;
532 IF (p_mtl_picking_rule_rec.shelf_days is not null)
533 THEN
534 l_logical_operator_code := 1; -- 'AND' if rule already exists
535 END IF;
536
537 l_go_ahead := 1;
538 debug('grade rule go ahead ?'||l_go_ahead);
539 END IF;
540 IF i = 3 THEN -- grade control rule
541 -- and ool.grade_code is not null
542 debug('grade ctl rule ');
543 -- always insert the grade rule
544 l_sequence_number := 30;
545 l_parameter_id := 60183; -- ool.preferred_grade
546 l_operand_parameter_id := null;
547 l_operator_code := '12'; -- 'is NOT NULL'
548 l_operand_type_code := 7;
549 l_operand_expression := null;
550 l_operand_constant_number := null;
551 l_operand_constant_character := null;
552 l_operand_constant_date := null;
553 l_logical_operator_code := null;
554 l_bracket_open := null;
555 l_bracket_close := ')';
556
557 l_logical_operator_code := 1; -- 'AND' grade is inserted already
558 l_go_ahead := 1;
559 debug('grade rule go ahead ?'||l_go_ahead);
560 END IF;
561 IF i = 4 THEN -- grade control rule
562 -- or ool.grade_code is null
563 debug('grade ctl rule ');
564 -- always insert the grade rule
565 l_sequence_number := 40;
566 l_parameter_id := 60183; -- ool.preferred_grade
567 l_operand_parameter_id := null;
568 l_operator_code := '11'; -- 'is NOT NULL'
569 l_operand_type_code := 7;
570 l_operand_expression := null;
571 l_operand_constant_number := null;
572 l_operand_constant_character := null;
573 l_operand_constant_date := null;
574 l_logical_operator_code := null;
575 l_bracket_open := null;
576 l_bracket_close := null;
577
578 l_logical_operator_code := 2; -- 'AND' grade is inserted already
579 l_go_ahead := 1;
580 debug('grade rule go ahead ?'||l_go_ahead);
581 END IF;
582 -- for the bug 10269269
583 IF i = 5 THEN -- grade control rule
584 -- or ool.grade_code is null
585 debug('grade ctl rule ');
586 -- always insert the grade rule
587 l_sequence_number := 50;
588 l_parameter_id := 10019; --1010061; -- 'Y' -- Added parameter_id as 10019 for Bug 14107303
589 l_operand_parameter_id := null;
590 l_operator_code := 4; -- means <>
591 l_operand_type_code := 5; -- means Expression
592 l_operand_expression := 'NVL(FND_PROFILE.VALUE(''INV_TARGET_PREFERRED_GRADE''),''N'')'; -- Added NVL for Bug 14107303
593 l_operand_constant_number := null;
594 l_operand_constant_character := null;
595 l_operand_constant_date := null;
596 l_logical_operator_code := null;
597 l_bracket_open := null;
598 l_bracket_close := ')';
599
600 l_logical_operator_code := 2; -- 'AND' grade is inserted already
601 l_go_ahead := 1;
602 debug('grade rule go ahead ?'||l_go_ahead);
603 END IF;
604 -- end of for the bug 10269269
605 END IF;
606 IF i = 6 AND p_mtl_picking_rule_rec.apply_to_source = 1
607 AND p_mtl_picking_rule_rec.cust_spec_match_flag = 'Y'
608 THEN
609 debug('cust spec match ');
610 l_sequence_number := 50;
611 l_parameter_id := 60187; -- SO.customer_spec_match
612 l_operand_parameter_id := null;
613 l_operator_code := '3'; -- '='
614 l_operand_type_code := '2'; -- constant character
615 l_operand_expression := null;
616 l_operand_constant_number := null;
617 l_operand_constant_character := 'ACCEPTABLE';
618 l_operand_constant_date := null;
619 l_bracket_open := null;
620 l_bracket_close := null;
621 IF (p_mtl_picking_rule_rec.shelf_days is not null)
622 THEN
623 l_logical_operator_code := 1; -- 'AND' if rule already exists
624 END IF;
625
626 l_go_ahead := 1;
627 debug('cust spec go ahead ?'||l_go_ahead);
628 END IF;
629 IF i = 7 AND l_is_mo_line = 1 THEN
630 debug('add moline.line_id into the where clause');
631 l_sequence_number := 60;
632 l_parameter_id := 60195; -- moline.line_id
633 l_operand_parameter_id := null;
634 l_operator_code := '3'; -- '='
635 l_operand_type_code := '5'; -- expression
636 l_operand_expression := 'mptdtv.line_id'; --null;
637 l_operand_constant_number := null;
638 l_operand_constant_character := null; --'mptdtv.line_id';
639 l_operand_constant_date := null;
640 l_bracket_open := null;
641 l_bracket_close := null;
642 IF (p_mtl_picking_rule_rec.shelf_days is not null)
643 THEN
644 l_logical_operator_code := 1; -- 'AND' if rule already exists
645 END IF;
646
647 l_go_ahead := 1;
648 debug('cust spec go ahead ?'||l_go_ahead);
649 END IF;
650
651 IF l_go_ahead = 1 THEN
652 debug('call wms restriction insert '||l_go_ahead);
653 wms_RESTRICTIONS_PKG.Insert_Row(
654 X_Rowid => l_Row_Id,
655 X_Rule_Id => l_picking_rule_rec.WMS_Rule_Id,
656 X_Sequence_Number => l_Sequence_Number,
657 X_Last_Updated_By => fnd_global.user_id,
658 X_Last_Update_Date => sysdate,
659 X_Created_By => fnd_global.user_id,
660 X_Creation_Date => sysdate,
661 X_Last_Update_Login => fnd_global.login_id,
662 X_Parameter_Id => l_Parameter_Id,
663 X_Operator_Code => l_Operator_Code,
664 X_Operand_Type_Code => l_Operand_Type_Code,
665 X_Operand_Constant_Number=> l_Operand_Constant_Number,
666 X_Operand_Constant_Character=> l_Operand_Constant_Character,
667 X_Operand_Constant_Date=> l_Operand_Constant_Date,
668 X_Operand_Parameter_Id => l_Operand_Parameter_Id,
669 X_Operand_Expression => l_Operand_Expression,
670 X_Operand_Flex_Value_Set_Id=> l_Operand_Flex_Value_Set_Id,
671 X_Logical_Operator_Code=> l_Logical_Operator_Code,
672 X_Bracket_Open => l_Bracket_Open,
673 X_Bracket_Close => l_Bracket_Close,
674 X_Attribute_Category => null,
675 X_Attribute1 => null,
676 X_Attribute2 => null,
677 X_Attribute3 => null,
678 X_Attribute4 => null,
679 X_Attribute5 => null,
680 X_Attribute6 => null,
681 X_Attribute7 => null,
682 X_Attribute8 => null,
683 X_Attribute9 => null,
684 X_Attribute10 => null,
685 X_Attribute11 => null,
686 X_Attribute12 => null,
687 X_Attribute13 => null,
688 X_Attribute14 => null,
689 X_Attribute15 => null
690 );
691 END IF;
692 END LOOP;
693 EXCEPTION
694 WHEN FND_API.G_EXC_ERROR THEN
695 x_return_status := FND_API.G_RET_STS_ERROR;
696 FND_MSG_Pub.count_and_get
697 ( p_count => x_msg_count
698 , p_data => x_msg_data
699 );
700 WHEN OTHERS THEN
701 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
702 x_return_status := SQLCODE;
703 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
704 , l_api_name
705 );
706 /* Get message count and data */
707 FND_MSG_Pub.count_and_get
708 ( p_count => x_msg_count
709 , p_data => x_msg_data
710 );
711 END restrictions_insert;
712
713 /*Restrictions update for the form will consist two parts,
714 * 1) delete the current rows for the rule_id
715 * 2) insert the new rows for the current p_mtl_picking_rule_rec
716 */
717 PROCEDURE Restrictions_update
718 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
719 , x_return_status OUT NOCOPY VARCHAR2
720 , x_msg_data OUT NOCOPY VARCHAR2
721 , x_msg_count OUT NOCOPY NUMBER)
722 IS
723 l_api_name CONSTANT VARCHAR2 (30) := 'Restrictions_Update';
724 Begin
725 x_return_status := FND_API.G_RET_STS_SUCCESS;
726 inv_rule_gen_pvt.Restrictions_delete
727 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
728 , x_return_status => x_return_status
729 , x_msg_data => x_msg_data
730 , x_msg_count => x_msg_count
731 );
732 inv_rule_gen_pvt.Restrictions_insert
733 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
734 , x_return_status => x_return_status
735 , x_msg_data => x_msg_data
736 , x_msg_count => x_msg_count
737 );
738 EXCEPTION
739 WHEN FND_API.G_EXC_ERROR THEN
740 x_return_status := FND_API.G_RET_STS_ERROR;
741 FND_MSG_Pub.count_and_get
742 ( p_count => x_msg_count
743 , p_data => x_msg_data
744 );
745 WHEN OTHERS THEN
746 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
747 x_return_status := SQLCODE;
748 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
749 , l_api_name
750 );
751 /* Get message count and data */
752 FND_MSG_Pub.count_and_get
753 ( p_count => x_msg_count
754 , p_data => x_msg_data
755 );
756 END restrictions_update;
757
758 PROCEDURE Restrictions_delete
759 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
760 , x_return_status OUT NOCOPY VARCHAR2
761 , x_msg_data OUT NOCOPY VARCHAR2
762 , x_msg_count OUT NOCOPY NUMBER)
763 IS
764 l_api_name CONSTANT VARCHAR2 (30) := 'Restrictions_Delete';
765 Begin
766 x_return_status := FND_API.G_RET_STS_SUCCESS;
767 Delete wms_restrictions where rule_id = p_mtl_picking_rule_rec.wms_rule_id;
768 EXCEPTION
769 WHEN FND_API.G_EXC_ERROR THEN
770 x_return_status := FND_API.G_RET_STS_ERROR;
771 FND_MSG_Pub.count_and_get
772 ( p_count => x_msg_count
773 , p_data => x_msg_data
774 );
775 WHEN OTHERS THEN
776 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
777 x_return_status := SQLCODE;
778 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
779 , l_api_name
780 );
781 /* Get message count and data */
782 FND_MSG_Pub.count_and_get
783 ( p_count => x_msg_count
784 , p_data => x_msg_data
785 );
786 END restrictions_delete;
787
788 PROCEDURE consistency_insert
789 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
790 , x_return_status OUT NOCOPY VARCHAR2
791 , x_msg_data OUT NOCOPY VARCHAR2
792 , x_msg_count OUT NOCOPY NUMBER
793 )
794 IS
795 l_api_name CONSTANT VARCHAR2 (30) := 'consistency_insert';
796 l_picking_rule_rec INV_RULE_GEN_PVT.picking_rule_rec;
797 l_consistency_id NUMBER;
798 l_row_id VARCHAR2(500);
799 l_parameter_id NUMBER;
800 Begin
801 x_return_status := FND_API.G_RET_STS_SUCCESS;
802 l_picking_rule_rec := p_mtl_picking_rule_rec;
803 If p_mtl_picking_rule_rec.single_lot = 'Y' then
804 -- find the parameter for lot.lot_number
805 l_parameter_id := 60006; -- lot.lot_Number
806 select wms_rule_consistencies_s.nextval into l_consistency_Id from dual;
807
808 WMS_RULE_CONSISTENCIES_PKG.INSERT_ROW(
809 X_ROWID => l_ROW_ID,
810 X_CONSISTENCY_ID => l_CONSISTENCY_ID,
811 X_RULE_ID => l_picking_rule_rec.WMS_RULE_ID,
812 X_CREATION_DATE => sysdate,
813 X_CREATED_BY => fnd_global.user_id,
814 X_LAST_UPDATE_DATE => sysdate,
815 X_LAST_UPDATED_BY => fnd_global.user_id,
816 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
817 X_PARAMETER_ID => l_parameter_id,
818 X_ATTRIBUTE_CATEGORY => '',
819 X_ATTRIBUTE1 => '',
820 X_ATTRIBUTE2 => '',
821 X_ATTRIBUTE3 => '',
822 X_ATTRIBUTE4 => '',
823 X_ATTRIBUTE5 => '',
824 X_ATTRIBUTE6 => '',
825 X_ATTRIBUTE7 => '',
826 X_ATTRIBUTE8 => '',
827 X_ATTRIBUTE9 => '',
828 X_ATTRIBUTE10 => '',
829 X_ATTRIBUTE11 => '',
830 X_ATTRIBUTE12 => '',
831 X_ATTRIBUTE13 => '',
832 X_ATTRIBUTE14 => '',
833 X_ATTRIBUTE15 => ''
834 );
835
836 end if;
837 EXCEPTION
838 WHEN FND_API.G_EXC_ERROR THEN
839 x_return_status := FND_API.G_RET_STS_ERROR;
840 FND_MSG_Pub.count_and_get
841 ( p_count => x_msg_count
842 , p_data => x_msg_data
843 );
844 WHEN OTHERS THEN
845 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
846 x_return_status := SQLCODE;
847 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
848 , l_api_name
849 );
850 /* Get message count and data */
851 FND_MSG_Pub.count_and_get
852 ( p_count => x_msg_count
853 , p_data => x_msg_data
854 );
855 END consistency_insert;
856
857 PROCEDURE Consistency_update
858 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
859 , x_return_status OUT NOCOPY VARCHAR2
860 , x_msg_data OUT NOCOPY VARCHAR2
861 , x_msg_count OUT NOCOPY NUMBER
862 )
863 IS
864 l_api_name CONSTANT VARCHAR2 (30) := 'Consistency_Update';
865 Begin
866 x_return_status := FND_API.G_RET_STS_SUCCESS;
867 inv_rule_gen_pvt.consistency_delete
868 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
869 , x_return_status => x_return_status
870 , x_msg_data => x_msg_data
871 , x_msg_count => x_msg_count
872 );
873 inv_rule_gen_pvt.consistency_insert
874 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
875 , x_return_status => x_return_status
876 , x_msg_data => x_msg_data
877 , x_msg_count => x_msg_count
878 );
879 EXCEPTION
880 WHEN FND_API.G_EXC_ERROR THEN
881 x_return_status := FND_API.G_RET_STS_ERROR;
882 FND_MSG_Pub.count_and_get
883 ( p_count => x_msg_count
884 , p_data => x_msg_data
885 );
886 WHEN OTHERS THEN
887 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
888 x_return_status := SQLCODE;
889 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
890 , l_api_name
891 );
892 /* Get message count and data */
893 FND_MSG_Pub.count_and_get
894 ( p_count => x_msg_count
895 , p_data => x_msg_data
896 );
897 END consistency_update;
898
899 PROCEDURE Consistency_delete
900 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
901 , x_return_status OUT NOCOPY VARCHAR2
902 , x_msg_data OUT NOCOPY VARCHAR2
903 , x_msg_count OUT NOCOPY NUMBER
904 )
905 IS
906 l_api_name CONSTANT VARCHAR2 (30) := 'Consistency_Delete';
907 Begin
908 x_return_status := FND_API.G_RET_STS_SUCCESS;
909 Delete wms_rule_consistencies where rule_id = p_mtl_picking_Rule_rec.wms_rule_id;
910 EXCEPTION
911 WHEN FND_API.G_EXC_ERROR THEN
912 x_return_status := FND_API.G_RET_STS_ERROR;
913 FND_MSG_Pub.count_and_get
914 ( p_count => x_msg_count
915 , p_data => x_msg_data
916 );
917 WHEN OTHERS THEN
918 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
919 x_return_status := SQLCODE;
920 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
921 , l_api_name
922 );
923 /* Get message count and data */
924 FND_MSG_Pub.count_and_get
925 ( p_count => x_msg_count
926 , p_data => x_msg_data
927 );
928 END consistency_delete;
929
930 PROCEDURE Sorting_criteria_insert
931 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
932 , x_return_status OUT NOCOPY VARCHAR2
933 , x_msg_data OUT NOCOPY VARCHAR2
934 , x_msg_count OUT NOCOPY NUMBER)
935 IS
936 l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_insert';
937 l_sort_order NUMBER;
938 l_sequence NUMBER;
939 l_row_id VARCHAR2(500);
940 l_parameter_id NUMBER;
941 l_order_code NUMBER;
942 i NUMBER;
943 l_go_ahead NUMBER;
944 Begin
945 x_return_status := FND_API.G_RET_STS_SUCCESS;
946 debug('sort insert, lot_sort_rank '||p_mtl_picking_rule_rec.lot_sort_rank);
947 debug('sort insert, revision_sort_rank '||p_mtl_picking_rule_rec.revision_sort_rank);
948 debug('sort insert, sub_sort_rank '||p_mtl_picking_rule_rec.subinventory_sort_rank);
949 debug('sort insert, locator_sort_rank '||p_mtl_picking_rule_rec.locator_sort_rank);
950 FOR i IN 1..4 LOOP
951 l_go_ahead := 0; -- NO insert
952 If i = 1 AND p_mtl_picking_rule_rec.lot_sort IS NOT NULL Then
953 --Get the parameter_id for lot.FIFO/FEFO
954 --Get the parameter_id lot_number, base.lot_number 10004
955 l_sequence := p_mtl_picking_rule_rec.lot_sort_rank;
956 l_sort_order := p_mtl_picking_rule_rec.lot_sort;
957 if l_sort_order = 3 THEN -- FIFO
958 l_parameter_id := 10008; -- Receipt Date --60012; -- creation_date
959 l_order_code := 1; -- Asceding
960 elsif l_sort_order = 4 THEN -- FEFO
961 l_parameter_id := 60018; -- expiration_date
962 l_order_code := 1; -- Asceding
963 elsif l_sort_order = 1 THEN -- Lot Number Asc
964 l_parameter_id := 10004; -- Lot number
965 l_order_code := 1; -- Asceding
966 elsif l_sort_order = 2 THEN -- Lot Number Desc
967 l_parameter_id := 10004; -- Lot number
968 l_order_code := 2; -- Descending
969 end if;
970 l_go_ahead := 1;
971 End if;
972 If i = 2 AND p_mtl_picking_rule_rec.revision_sort IS NOT NULL Then
973 --Get the parameter_id for object mtl_item_revisions.revision object_id=5
974 --Get the parameter_id for object mtl_item_revisions.effectivity_date object_id=5 50013
975 l_sequence := p_mtl_picking_rule_rec.revision_sort_rank;
976 l_sort_order := p_mtl_picking_rule_rec.revision_sort;
977 if l_sort_order = 1 THEN -- revision asceding
978 l_parameter_id := 10003; -- revision
979 l_order_code := 1; -- Asceding
980 elsif l_sort_order = 2 THEN -- revision desceding
981 l_parameter_id := 10003; -- expiration_date
982 l_order_code := 2; -- Desceding
983 elsif l_sort_order = 3 THEN -- effective date asceding
984 l_parameter_id := 50013; -- effective_date
985 l_order_code := 1; -- Asceding
986 elsif l_sort_order = 4 THEN -- effective date desceding
987 l_parameter_id := 50013; -- effective_date
988 l_order_code := 2; -- Desceding
989 end if;
990 l_go_ahead := 1;
991 End if;
992 If i = 3 AND p_mtl_picking_rule_rec.locator_sort IS NOT NULL Then
993 --Get the parameter_id for object source locator.locator identifier
994 -- mtl_item_locations.picking_order object_id=8
995 -- stock on hand. receipt date object_id=54
996 l_sequence := p_mtl_picking_rule_rec.locator_sort_rank;
997 l_sort_order := p_mtl_picking_rule_rec.locator_sort;
998 if l_sort_order = 1 THEN -- locator asceding
999 l_parameter_id := 80012; -- picking order
1000 l_order_code := 1; -- Asceding
1001 elsif l_sort_order = 2 THEN -- revision desceding
1002 l_parameter_id := 80012; -- picking order
1003 l_order_code := 2; -- Desceding
1004 elsif l_sort_order = 3 THEN -- receipt date asceding
1005 l_parameter_id := 10008; -- receipt date
1006 l_order_code := 1; -- Asceding
1007 elsif l_sort_order = 4 THEN -- receipt date desceding
1008 l_parameter_id := 10008; -- receipt date
1009 l_order_code := 2; -- Desceding
1010 end if;
1011 l_go_ahead := 1;
1012 End if;
1013 If i = 4 AND p_mtl_picking_rule_rec.subinventory_sort IS NOT NULL Then
1014 --Get the parameter_id for object source subinventory
1015 -- mtl_secondary_inventories.picking order object_id=7
1016 -- stock on hand. receipt date object_id=54
1017 l_sequence := p_mtl_picking_rule_rec.subinventory_sort_rank;
1018 l_sort_order := p_mtl_picking_rule_rec.subinventory_sort;
1019 if l_sort_order = 1 THEN -- locator asceding
1020 l_parameter_id := 70015; -- picking order
1021 l_order_code := 1; -- Asceding
1022 elsif l_sort_order = 2 THEN -- revision desceding
1023 l_parameter_id := 70015; -- picking order
1024 l_order_code := 2; -- Desceding
1025 elsif l_sort_order = 3 THEN -- receipt date asceding
1026 l_parameter_id := 10008; -- receipt date
1027 l_order_code := 1; -- Asceding
1028 elsif l_sort_order = 4 THEN -- receipt date desceding
1029 l_parameter_id := 10008; -- receipt date
1030 l_order_code := 2; -- Desceding
1031 end if;
1032 l_go_ahead := 1;
1033 End if;
1034
1035 if l_go_ahead = 1 THEN
1036 debug('sort insert, '|| i);
1037 debug('sort insert, sequence '||l_sequence);
1038 debug('sort insert, wms_rule_id '||p_mtl_picking_rule_rec.wms_rule_id);
1039 wms_SORT_CRITERIA_PKG.Insert_Row
1040 (
1041 X_Rowid => l_Row_Id,
1042 X_Rule_Id => p_mtl_picking_rule_rec.WMS_Rule_Id,
1043 X_Sequence_Number => l_Sequence,
1044 X_Parameter_Id => l_Parameter_Id,
1045 X_Order_Code => l_Order_Code,
1046 X_Created_By => fnd_global.user_id,
1047 X_Creation_Date => sysdate,
1048 X_Last_Updated_By => fnd_global.user_id,
1049 X_Last_Update_Date => sysdate,
1050 X_Last_Update_Login => fnd_global.login_id,
1051 X_Attribute1 => null,
1052 X_Attribute2 => null,
1053 X_Attribute3 => null,
1054 X_Attribute4 => null,
1055 X_Attribute5 => null,
1056 X_Attribute6 => null,
1057 X_Attribute7 => null,
1058 X_Attribute8 => null,
1059 X_Attribute9 => null,
1060 X_Attribute10 => null,
1061 X_Attribute11 => null,
1062 X_Attribute12 => null,
1063 X_Attribute13 => null,
1064 X_Attribute14 => null,
1065 X_Attribute15 => null,
1066 X_Attribute_Category => null
1067 );
1068 end if;
1069 END LOOP;
1070 EXCEPTION
1071 WHEN FND_API.G_EXC_ERROR THEN
1072 x_return_status := FND_API.G_RET_STS_ERROR;
1073 FND_MSG_Pub.count_and_get
1074 ( p_count => x_msg_count
1075 , p_data => x_msg_data
1076 );
1077 WHEN OTHERS THEN
1078 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1079 x_return_status := SQLCODE;
1080 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1081 , l_api_name
1082 );
1083 /* Get message count and data */
1084 FND_MSG_Pub.count_and_get
1085 ( p_count => x_msg_count
1086 , p_data => x_msg_data
1087 );
1088 END sorting_criteria_insert;
1089
1090 /* Update consist two parts
1091 * 1) Delete the current row for the rule_id
1092 * 2) Insert new rows for the current setup
1093 */
1094
1095 PROCEDURE sorting_criteria_update
1096 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1097 , x_return_status OUT NOCOPY VARCHAR2
1098 , x_msg_data OUT NOCOPY VARCHAR2
1099 , x_msg_count OUT NOCOPY NUMBER)
1100 IS
1101 l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_Update';
1102 Begin
1103 x_return_status := FND_API.G_RET_STS_SUCCESS;
1104 inv_rule_gen_pvt.sorting_criteria_delete
1105 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1106 , x_return_status => x_return_status
1107 , x_msg_data => x_msg_data
1108 , x_msg_count => x_msg_count
1109 );
1110 inv_rule_gen_pvt.sorting_criteria_insert
1111 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1112 , x_return_status => x_return_status
1113 , x_msg_data => x_msg_data
1114 , x_msg_count => x_msg_count
1115 );
1116 EXCEPTION
1117 WHEN FND_API.G_EXC_ERROR THEN
1118 x_return_status := FND_API.G_RET_STS_ERROR;
1119 FND_MSG_Pub.count_and_get
1120 ( p_count => x_msg_count
1121 , p_data => x_msg_data
1122 );
1123 WHEN OTHERS THEN
1124 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1125 x_return_status := SQLCODE;
1126 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1127 , l_api_name
1128 );
1129 /* Get message count and data */
1130 FND_MSG_Pub.count_and_get
1131 ( p_count => x_msg_count
1132 , p_data => x_msg_data
1133 );
1134 END sorting_criteria_update;
1135
1136 PROCEDURE Sorting_criteria_delete
1137 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1138 , x_return_status OUT NOCOPY VARCHAR2
1139 , x_msg_data OUT NOCOPY VARCHAR2
1140 , x_msg_count OUT NOCOPY NUMBER)
1141 IS
1142 l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_Delete';
1143 Begin
1144 x_return_status := FND_API.G_RET_STS_SUCCESS;
1145 debug('delete sorting criteria');
1146 Delete wms_sort_criteria where rule_id = p_mtl_picking_Rule_rec.wms_rule_id;
1147 EXCEPTION
1148 WHEN FND_API.G_EXC_ERROR THEN
1149 x_return_status := FND_API.G_RET_STS_ERROR;
1150 FND_MSG_Pub.count_and_get
1151 ( p_count => x_msg_count
1152 , p_data => x_msg_data
1153 );
1154 WHEN OTHERS THEN
1155 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1156 x_return_status := SQLCODE;
1157 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1158 , l_api_name
1159 );
1160 /* Get message count and data */
1161 FND_MSG_Pub.count_and_get
1162 ( p_count => x_msg_count
1163 , p_data => x_msg_data
1164 );
1165 END sorting_criteria_delete;
1166
1167 PROCEDURE Strategy_insert
1168 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1169 , x_return_status OUT NOCOPY VARCHAR2
1170 , x_msg_data OUT NOCOPY VARCHAR2
1171 , x_msg_count OUT NOCOPY NUMBER
1172 )
1173 IS
1174 l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_insert';
1175 l_strategy_id NUMBER;
1176 l_strategy_name VARCHAR2(50);
1177 l_rowid VARCHAR2(500);
1178 Begin
1179 x_return_status := FND_API.G_RET_STS_SUCCESS;
1180 --check enabled flag for the rule, only insert strategy for enabled rules
1181 debug('Procedure Strategy_insert');
1182 if p_mtl_picking_rule_rec.enabled_flag <> 'Y' Then
1183 return;
1184 end if;
1185 -- insert the strategy with the same name of the rule
1186 select WMS_strategies_s.nextval into l_strategy_id from sys.dual;
1187 l_strategy_name := p_mtl_picking_rule_rec.name;
1188 debug('strategy_id '||l_strategy_id);
1189 debug('strategy_name '||l_strategy_name);
1190 WMS_STRATEGIES_PKG.Insert_Row
1191 (
1192 X_Rowid => l_ROWID
1193 , X_Strategy_Id => l_Strategy_Id
1194 , X_Organization_Id => -1
1195 , X_Type_Code => 2
1196 , X_Name => l_strategy_name
1197 , X_Description => l_strategy_name
1198 , X_Enabled_Flag => 'Y'
1199 , X_User_Defined_Flag => 'Y'
1200 , X_Created_By => fnd_global.user_id
1201 , X_Creation_Date => SYSDATE
1202 , X_Last_Updated_By => fnd_global.user_id
1203 , X_Last_Update_Date => SYSDATE
1204 , X_Last_Update_Login => fnd_global.login_id
1205 , X_Attribute1 => null
1206 , X_Attribute2 => null
1207 , X_Attribute3 => null
1208 , X_Attribute4 => null
1209 , X_Attribute5 => null
1210 , X_Attribute6 => null
1211 , X_Attribute7 => null
1212 , X_Attribute8 => null
1213 , X_Attribute9 => null
1214 , X_Attribute10 => null
1215 , X_Attribute11 => null
1216 , X_Attribute12 => null
1217 , X_Attribute13 => null
1218 , X_Attribute14 => null
1219 , X_Attribute15 => null
1220 , X_Attribute_Category => null
1221 );
1222 p_mtl_picking_rule_rec.wms_strategy_id := l_strategy_id;
1223 /* insert strategy_members */
1224 debug('calling insert strategy members ');
1225 WMS_STRATEGY_MEMBERS_PKG.Insert_Row(
1226 X_Rowid => l_RowId
1227 , X_Strategy_Id => l_Strategy_Id
1228 , X_Sequence_Number => 10
1229 , X_Rule_Id => p_mtl_picking_rule_rec.wms_Rule_Id
1230 , X_Partial_Success_Allowed_Flag=> p_mtl_picking_rule_rec.Partial_Allowed_Flag
1231 , X_Effective_From => null
1232 , X_Effective_To => null
1233 , X_Created_By => fnd_global.user_id
1234 , X_Creation_Date => sysdate
1235 , X_Last_Updated_By => fnd_global.user_id
1236 , X_Last_Update_Date => sysdate
1237 , X_Last_Update_Login => fnd_global.login_id
1238 , X_Attribute1 => null
1239 , X_Attribute2 => null
1240 , X_Attribute3 => null
1241 , X_Attribute4 => null
1242 , X_Attribute5 => null
1243 , X_Attribute6 => null
1244 , X_Attribute7 => null
1245 , X_Attribute8 => null
1246 , X_Attribute9 => null
1247 , X_Attribute10 => null
1248 , X_Attribute11 => null
1249 , X_Attribute12 => null
1250 , X_Attribute13 => null
1251 , X_Attribute14 => null
1252 , X_Attribute15 => null
1253 , X_Attribute_Category => null
1254 , X_Date_Type_Code => 11 -- always
1255 , X_Date_Type_Lookup_Type => null
1256 , X_Date_Type_From => null
1257 , X_Date_Type_To => null
1258 );
1259 EXCEPTION
1260 WHEN FND_API.G_EXC_ERROR THEN
1261 x_return_status := FND_API.G_RET_STS_ERROR;
1262 FND_MSG_Pub.count_and_get
1263 ( p_count => x_msg_count
1264 , p_data => x_msg_data
1265 );
1266 WHEN OTHERS THEN
1267 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1268 x_return_status := SQLCODE;
1269 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1270 , l_api_name
1271 );
1272 /* Get message count and data */
1273 FND_MSG_Pub.count_and_get
1274 ( p_count => x_msg_count
1275 , p_data => x_msg_data
1276 );
1277 End strategy_insert;
1278
1279 /* Only enabled flag can be updated. */
1280 /* disable the strategy when rule is disabled */
1281 PROCEDURE Strategy_update
1282 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1283 , x_return_status OUT NOCOPY VARCHAR2
1284 , x_msg_data OUT NOCOPY VARCHAR2
1285 , x_msg_count OUT NOCOPY NUMBER
1286 )
1287 IS
1288 l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Update';
1289 Begin
1290 x_return_status := FND_API.G_RET_STS_SUCCESS;
1291 if p_mtl_picking_rule_rec.enabled_flag = 'N' then
1292 WMS_STRATEGIES_PKG.Update_Row(
1293 X_Strategy_Id => p_mtl_picking_rule_rec.wms_Strategy_Id,
1294 X_Organization_Id => -1,
1295 X_Type_Code => 2,
1296 X_Name => p_mtl_picking_rule_rec.name,
1297 X_Description => p_mtl_picking_rule_rec.name,
1298 X_Enabled_Flag => p_mtl_picking_rule_rec.enabled_flag,
1299 X_User_Defined_Flag => 'Y',
1300 X_Last_Updated_By => fnd_global.user_id,
1301 X_Last_Update_Date => sysdate,
1302 X_Last_Update_Login => fnd_global.user_id,
1303 X_Attribute1 => null,
1304 X_Attribute2 => null,
1305 X_Attribute3 => null,
1306 X_Attribute4 => null,
1307 X_Attribute5 => null,
1308 X_Attribute6 => null,
1309 X_Attribute7 => null,
1310 X_Attribute8 => null,
1311 X_Attribute9 => null,
1312 X_Attribute10 => null,
1313 X_Attribute11 => null,
1314 X_Attribute12 => null,
1315 X_Attribute13 => null,
1316 X_Attribute14 => null,
1317 X_Attribute15 => null,
1318 X_Attribute_Category => null
1319 );
1320 end if;
1321 EXCEPTION
1322 WHEN FND_API.G_EXC_ERROR THEN
1323 x_return_status := FND_API.G_RET_STS_ERROR;
1324 FND_MSG_Pub.count_and_get
1325 ( p_count => x_msg_count
1326 , p_data => x_msg_data
1327 );
1328 WHEN OTHERS THEN
1329 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1330 x_return_status := SQLCODE;
1331 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1332 , l_api_name
1333 );
1334 /* Get message count and data */
1335 FND_MSG_Pub.count_and_get
1336 ( p_count => x_msg_count
1337 , p_data => x_msg_data
1338 );
1339 END;
1340
1341 /* when rule is deleted, strategy is also deleted */
1342 PROCEDURE Strategy_delete
1343 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1344 , x_return_status OUT NOCOPY VARCHAR2
1345 , x_msg_data OUT NOCOPY VARCHAR2
1346 , x_msg_count OUT NOCOPY NUMBER
1347 )
1348 IS
1349 l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Delete';
1350 Begin
1351 x_return_status := FND_API.G_RET_STS_SUCCESS;
1352 null;
1353 EXCEPTION
1354 WHEN FND_API.G_EXC_ERROR THEN
1355 x_return_status := FND_API.G_RET_STS_ERROR;
1356 FND_MSG_Pub.count_and_get
1357 ( p_count => x_msg_count
1358 , p_data => x_msg_data
1359 );
1360 WHEN OTHERS THEN
1361 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1362 x_return_status := SQLCODE;
1363 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1364 , l_api_name
1365 );
1366 /* Get message count and data */
1367 FND_MSG_Pub.count_and_get
1368 ( p_count => x_msg_count
1369 , p_data => x_msg_data
1370 );
1371 END;
1372
1373 PROCEDURE Rule_Enabled_Flag
1374 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1375 , x_return_status OUT NOCOPY VARCHAR2
1376 , x_msg_data OUT NOCOPY VARCHAR2
1377 , x_msg_count OUT NOCOPY NUMBER)
1378 IS
1379 l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Delete';
1380 l_picking_rule_rec INV_RULE_GEN_PVT.picking_rule_rec;
1381 l_return_status VARCHAR2(1);
1382 l_msg_data VARCHAR2(2000);
1383 l_msg_count NUMBER;
1384 v_type_code number;
1385 BEGIN
1386 x_return_status := FND_API.G_RET_STS_SUCCESS;
1387 l_picking_rule_rec := p_mtl_picking_rule_rec;
1388 debug('Procedure rule enable flag');
1389 IF l_picking_rule_rec.Enabled_Flag = 'Y' THEN
1390 l_picking_rule_rec.enabled_flag:='Y';
1391 -- Check rule Syntax
1392 debug('checksyntax');
1393 WMS_rule_PVT.CheckSyntax (
1394 p_api_version => 1.0
1395 ,p_init_msg_list => FND_API.G_TRUE
1396 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1397 ,x_return_status => l_return_status
1398 ,x_msg_count => l_msg_count
1399 ,x_msg_data => l_msg_data
1400 ,p_rule_id => l_picking_rule_rec.wms_rule_ID
1401 );
1402 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1403 debug('checksyntax failed');
1404 --INV_GLOBAL_PKG.Show_Errors;
1405 l_picking_rule_rec.Enabled_Flag := 'N';
1406 END IF;
1407 --- Calling The Generate l_mtl_picking_rule_rec.list pkg
1408 IF l_picking_rule_rec.Enabled_Flag = 'Y' THEN
1409 debug('generateruleexecpkgs');
1410 WMS_rule_gen_pkgs.GenerateRuleExecPkgs
1411 (
1412 p_api_version => 1.0
1413 ,p_init_msg_list => FND_API.G_TRUE
1414 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1415 ,x_return_status => l_return_status
1416 ,x_msg_count => l_msg_count
1417 ,x_msg_data => l_msg_data
1418 ,p_pick_code => 2
1419 ,p_put_code => null
1420 ,p_task_code => null
1421 ,p_label_code => null
1422 ,p_CG_code => null
1423 ,p_op_code => null
1424 ,p_pkg_type => 'B'
1425 );
1426
1427 /* if l_picking_rule_rec.wms_strategy_id is null THEN
1428 inv_rule_gen_pvt.Strategy_insert
1429 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1430 , x_return_status => x_return_status
1431 , x_msg_data => x_msg_data
1432 , x_msg_count => x_msg_count
1433 );
1434 Else
1435 inv_rule_gen_pvt.Strategy_Update
1436 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1437 , x_return_status => x_return_status
1438 , x_msg_data => x_msg_data
1439 , x_msg_count => x_msg_count
1440 );
1441 End if;
1442 */
1443 debug('after strategy_insert '||p_mtl_picking_rule_rec.wms_strategy_id);
1444 update wms_rules_b
1445 set enabled_flag = 'Y'
1446 where rule_id=p_mtl_picking_rule_rec.wms_rule_id;
1447 commit;
1448 End if;
1449 ELSE -- disable the flag
1450 l_picking_rule_rec.enabled_flag:='N';
1451 debug('disabling the rule and strategy');
1452 /*
1453 inv_rule_gen_pvt.Strategy_Update
1454 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1455 , x_return_status => x_return_status
1456 , x_msg_data => x_msg_data
1457 , x_msg_count => x_msg_count
1458 );
1459 */
1460 debug('after strategy_update '||p_mtl_picking_rule_rec.wms_strategy_id);
1461 update wms_rules_b
1462 set enabled_flag = 'N'
1463 where rule_id=p_mtl_picking_rule_rec.wms_rule_id;
1464 commit;
1465 END IF;
1466 EXCEPTION
1467 WHEN FND_API.G_EXC_ERROR THEN
1468 x_return_status := FND_API.G_RET_STS_ERROR;
1469 FND_MSG_Pub.count_and_get
1470 ( p_count => x_msg_count
1471 , p_data => x_msg_data
1472 );
1473 WHEN OTHERS THEN
1474 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1475 x_return_status := SQLCODE;
1476 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1477 , l_api_name
1478 );
1479 /* Get message count and data */
1480 FND_MSG_Pub.count_and_get
1481 ( p_count => x_msg_count
1482 , p_data => x_msg_data
1483 );
1484 END Rule_Enabled_Flag;
1485
1486 FUNCTION rule_assigned_to_strategy
1487 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1488 , x_return_status OUT NOCOPY VARCHAR2
1489 , x_msg_data OUT NOCOPY VARCHAR2
1490 , x_msg_count OUT NOCOPY NUMBER)
1491 RETURN BOOLEAN IS
1492 l_api_name CONSTANT VARCHAR2 (30) := 'rule_assigned_to_strategy';
1493 /*
1494 CURSOR L_curStrategyMembers(p_rule_id IN NUMBER) IS
1495 SELECT 'X'
1496 FROM wms_strategy_members MPSM, wms_strategies_b S
1497 WHERE MPSM.rule_id = p_rule_id
1498 AND S.Strategy_Id = MPSM.Strategy_Id
1499 AND S.Enabled_Flag = 'Y'
1500 AND NVL(MPSM.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
1501 AND NVL(MPSM.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
1502 AND rownum < 2;
1503
1504 CURSOR StratAssignments_old (p_strategy_id in NUMBER) IS
1505 SELECT 'X'
1506 FROM wms_selection_criteria_txn WSCT
1507 WHERE WSCT.return_type_code = 'S'
1508 AND WSCT.return_type_id = p_strategy_id
1509 AND WSCT.enabled_flag = 1
1510 AND NVL(WSCT.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
1511 AND NVL(WSCT.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
1512 AND rownum < 2;
1513 */
1514 ---- New Cursor added for checking if the rule is assigned directly in the assignment matrix
1515
1516 CURSOR StratAssignments_new (p_rule_id IN NUMBER) IS
1517 SELECT 'X'
1518 FROM wms_selection_criteria_txn WSCT
1519 WHERE WSCT.return_type_code = 'R'
1520 AND WSCT.return_type_id = p_rule_id
1521 AND WSCT.enabled_flag = 1
1522 AND NVL(WSCT.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
1523 AND NVL(WSCT.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
1524 AND rownum < 2;
1525
1526 --- Setting the profile option to be deleted later on
1527 -- Bug# 7502663 Defaulted the value of rules engine to 1 instead of picking from profile
1528 -- l_rules_engine_mode NUMBER := NVL(FND_PROFILE.VALUE('WMS_RULES_ENGINE_MODE'), 0);
1529 l_rules_engine_mode NUMBER := 1;
1530
1531 l_nDummy VARCHAR2(1);
1532 l_bReturn BOOLEAN;
1533 BEGIN
1534 x_return_status := FND_API.G_RET_STS_SUCCESS;
1535 debug('check rule is assigned');
1536 debug('rule_id '|| p_mtl_picking_rule_rec.wms_rule_id);
1537 debug('strategy_id '|| p_mtl_picking_rule_rec.wms_strategy_id);
1538 /*
1539 -- Check if rule is assigned to strategy member
1540 OPEN L_curStrategyMembers(p_mtl_picking_rule_rec.wms_rule_id);
1541 FETCH L_curStrategyMembers INTO L_nDummy;
1542 IF L_curStrategyMembers%NOTFOUND THEN
1543 L_bReturn := FALSE;
1544 ELSE
1545 L_bReturn := TRUE;
1546 END IF;
1547
1548 OPEN StratAssignments_old(p_mtl_picking_rule_rec.wms_strategy_id);
1549 FETCH StratAssignments_old into L_nDummy;
1550 IF StratAssignments_old%NOTFOUND THEN
1551 L_bReturn := FALSE;
1552 ELSE
1553 L_bReturn := TRUE;
1554 END IF;
1555 CLOSE StratAssignments_old;
1556 */
1557 --- checking if the rule is assigned directly in the assignment matrix
1558
1559 IF (l_rules_engine_mode = 1) then
1560
1561 -- Bug# 7502663 Modified the parameter for opening the cursor to
1562 -- wms_rule_id instead of wms_strategy_id
1563 -- OPEN StratAssignments_new(p_mtl_picking_rule_rec.wms_strategy_id);
1564 OPEN StratAssignments_new(p_mtl_picking_rule_rec.wms_rule_id);
1565
1566 FETCH StratAssignments_new into L_nDummy;
1567 IF StratAssignments_new%NOTFOUND THEN
1568 L_bReturn := FALSE;
1569 ELSE
1570 L_bReturn := TRUE;
1571 END IF;
1572 CLOSE StratAssignments_new;
1573 end if;
1574
1575 RETURN(l_bReturn);
1576 EXCEPTION
1577 WHEN FND_API.G_EXC_ERROR THEN
1578 x_return_status := FND_API.G_RET_STS_ERROR;
1579 FND_MSG_Pub.count_and_get
1580 ( p_count => x_msg_count
1581 , p_data => x_msg_data
1582 );
1583 WHEN OTHERS THEN
1584 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1585 x_return_status := SQLCODE;
1586 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1587 , l_api_name
1588 );
1589 /* Get message count and data */
1590 FND_MSG_Pub.count_and_get
1591 ( p_count => x_msg_count
1592 , p_data => x_msg_data
1593 );
1594 END rule_assigned_to_strategy;
1595
1596 PROCEDURE GenerateRulePKG
1597 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1598 , x_return_status OUT NOCOPY VARCHAR2
1599 , x_msg_data OUT NOCOPY VARCHAR2
1600 , x_msg_count OUT NOCOPY NUMBER)
1601 IS
1602 l_api_name CONSTANT VARCHAR2 (30) := 'GenerateRulePKG';
1603 l_return_status VARCHAR2(1);
1604 l_msg_data VARCHAR2(2000);
1605 l_msg_count NUMBER;
1606 rec_status VARCHAR2(25);
1607 BEGIN
1608 x_return_status := FND_API.G_RET_STS_SUCCESS;
1609 IF p_mtl_picking_rule_rec.Enabled_Flag = 'Y' THEN
1610 WMS_Rule_PVT.GenerateRulePackage
1611 (
1612 p_api_version => 1.0
1613 ,p_init_msg_list => FND_API.G_TRUE
1614 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1615 ,x_return_status => x_return_status
1616 ,x_msg_count => x_msg_count
1617 ,x_msg_data => x_msg_data
1618 ,p_rule_id => p_mtl_picking_rule_rec.wms_RULE_ID
1619 );
1620 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1621 THEN
1622 p_mtl_picking_rule_rec.Enabled_Flag := 'N';
1623 RAISE FND_API.G_EXC_ERROR;
1624 ELSE
1625 fnd_message.set_name('WMS','WMS_PACKAGE_REGENERATE');
1626 FND_MSG_PUB.Add;
1627 RAISE FND_API.G_EXC_ERROR;
1628 END IF;
1629 END IF;
1630 EXCEPTION
1631 WHEN FND_API.G_EXC_ERROR THEN
1632 x_return_status := FND_API.G_RET_STS_ERROR;
1633 FND_MSG_Pub.count_and_get
1634 ( p_count => x_msg_count
1635 , p_data => x_msg_data
1636 );
1637 debug('regenerate rule pkgs. error exception');
1638 WHEN OTHERS THEN
1639 debug('regenerate others'||SQLCODE||'.');
1640 x_return_status := SQLCODE;
1641 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1642 , l_api_name
1643 );
1644 /* Get message count and data */
1645 FND_MSG_Pub.count_and_get
1646 ( p_count => x_msg_count
1647 , p_data => x_msg_data
1648 );
1649 END GenerateRulePKG;
1650
1651 END inv_rule_gen_pvt;