[Home] [Help]
PACKAGE BODY: APPS.INV_RULE_GEN_PVT
Source
1 PACKAGE BODY inv_rule_gen_pvt AS
2 /* $Header: INVRLGNB.pls 120.4 2006/09/26 17:30:48 lgao noship $ */
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..6 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 := null;
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 := null;
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 := null;
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 := null;
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 /* grade for SO */
492 IF p_mtl_picking_rule_rec.apply_to_source = 1
493 and nvl(p_mtl_picking_rule_rec.cust_spec_match_flag,'N') = 'N'
494 THEN
495 IF i = 2 THEN -- grade rule
496 debug('grade rule ');
497 -- always insert the grade rule
498 l_sequence_number := 20;
499 l_parameter_id := 60183; -- SO.preferred_grade
500 l_operand_parameter_id := 60141; -- lot.grade_code
501 l_operator_code := '3'; -- '='
502 l_operand_type_code := '4'; -- ?
503 l_operand_expression := null;
504 l_operand_constant_number := null;
505 l_operand_constant_character := null;
506 l_operand_constant_date := null;
507 l_logical_operator_code := null;
508 l_bracket_open := '((';
509 l_bracket_close := null;
510 IF (p_mtl_picking_rule_rec.shelf_days is not null)
511 THEN
512 l_logical_operator_code := 1; -- 'AND' if rule already exists
513 END IF;
514
515 l_go_ahead := 1;
516 debug('grade rule go ahead ?'||l_go_ahead);
517 END IF;
518 IF i = 3 THEN -- grade control rule
519 -- and ool.grade_code is not null
520 debug('grade ctl rule ');
521 -- always insert the grade rule
522 l_sequence_number := 30;
523 l_parameter_id := 60183; -- ool.preferred_grade
524 l_operand_parameter_id := null;
525 l_operator_code := '12'; -- 'is NOT NULL'
526 l_operand_type_code := 7;
527 l_operand_expression := null;
528 l_operand_constant_number := null;
529 l_operand_constant_character := null;
530 l_operand_constant_date := null;
531 l_logical_operator_code := null;
532 l_bracket_open := null;
533 l_bracket_close := ')';
534
535 l_logical_operator_code := 1; -- 'AND' grade is inserted already
536 l_go_ahead := 1;
537 debug('grade rule go ahead ?'||l_go_ahead);
538 END IF;
539 IF i = 4 THEN -- grade control rule
540 -- or ool.grade_code is null
541 debug('grade ctl rule ');
542 -- always insert the grade rule
543 l_sequence_number := 40;
544 l_parameter_id := 60183; -- ool.preferred_grade
545 l_operand_parameter_id := null;
546 l_operator_code := '11'; -- 'is NOT NULL'
547 l_operand_type_code := 7;
548 l_operand_expression := null;
549 l_operand_constant_number := null;
550 l_operand_constant_character := null;
551 l_operand_constant_date := null;
552 l_logical_operator_code := null;
553 l_bracket_open := null;
554 l_bracket_close := ')';
555
556 l_logical_operator_code := 2; -- 'AND' grade is inserted already
557 l_go_ahead := 1;
558 debug('grade rule go ahead ?'||l_go_ahead);
559 END IF;
560
561 END IF;
562 IF i = 5 AND p_mtl_picking_rule_rec.apply_to_source = 1
563 AND p_mtl_picking_rule_rec.cust_spec_match_flag = 'Y'
564 THEN
565 debug('cust spec match ');
566 l_sequence_number := 50;
567 l_parameter_id := 60187; -- SO.customer_spec_match
568 l_operand_parameter_id := null;
569 l_operator_code := '3'; -- '='
570 l_operand_type_code := '2'; -- constant character
571 l_operand_expression := null;
572 l_operand_constant_number := null;
573 l_operand_constant_character := 'ACCEPTABLE';
574 l_operand_constant_date := null;
575 l_bracket_open := null;
576 l_bracket_close := null;
577 IF (p_mtl_picking_rule_rec.shelf_days is not null)
578 THEN
579 l_logical_operator_code := 1; -- 'AND' if rule already exists
580 END IF;
581
582 l_go_ahead := 1;
583 debug('cust spec go ahead ?'||l_go_ahead);
584 END IF;
585 IF i = 6 AND l_is_mo_line = 1 THEN
586 debug('add moline.line_id into the where clause');
587 l_sequence_number := 60;
588 l_parameter_id := 60195; -- moline.line_id
589 l_operand_parameter_id := null;
590 l_operator_code := '3'; -- '='
591 l_operand_type_code := '5'; -- expression
592 l_operand_expression := 'mptdtv.line_id'; --null;
593 l_operand_constant_number := null;
594 l_operand_constant_character := null; --'mptdtv.line_id';
595 l_operand_constant_date := null;
596 l_bracket_open := null;
597 l_bracket_close := null;
598 IF (p_mtl_picking_rule_rec.shelf_days is not null)
599 THEN
600 l_logical_operator_code := 1; -- 'AND' if rule already exists
601 END IF;
602
603 l_go_ahead := 1;
604 debug('cust spec go ahead ?'||l_go_ahead);
605 END IF;
606
607 IF l_go_ahead = 1 THEN
608 debug('call wms restriction insert '||l_go_ahead);
609 wms_RESTRICTIONS_PKG.Insert_Row(
610 X_Rowid => l_Row_Id,
611 X_Rule_Id => l_picking_rule_rec.WMS_Rule_Id,
612 X_Sequence_Number => l_Sequence_Number,
613 X_Last_Updated_By => fnd_global.user_id,
614 X_Last_Update_Date => sysdate,
615 X_Created_By => fnd_global.user_id,
616 X_Creation_Date => sysdate,
617 X_Last_Update_Login => fnd_global.login_id,
618 X_Parameter_Id => l_Parameter_Id,
619 X_Operator_Code => l_Operator_Code,
620 X_Operand_Type_Code => l_Operand_Type_Code,
621 X_Operand_Constant_Number=> l_Operand_Constant_Number,
622 X_Operand_Constant_Character=> l_Operand_Constant_Character,
623 X_Operand_Constant_Date=> l_Operand_Constant_Date,
624 X_Operand_Parameter_Id => l_Operand_Parameter_Id,
625 X_Operand_Expression => l_Operand_Expression,
626 X_Operand_Flex_Value_Set_Id=> l_Operand_Flex_Value_Set_Id,
627 X_Logical_Operator_Code=> l_Logical_Operator_Code,
628 X_Bracket_Open => l_Bracket_Open,
629 X_Bracket_Close => l_Bracket_Close,
630 X_Attribute_Category => null,
631 X_Attribute1 => null,
632 X_Attribute2 => null,
633 X_Attribute3 => null,
634 X_Attribute4 => null,
635 X_Attribute5 => null,
636 X_Attribute6 => null,
637 X_Attribute7 => null,
638 X_Attribute8 => null,
639 X_Attribute9 => null,
640 X_Attribute10 => null,
641 X_Attribute11 => null,
642 X_Attribute12 => null,
643 X_Attribute13 => null,
644 X_Attribute14 => null,
645 X_Attribute15 => null
646 );
647 END IF;
648 END LOOP;
649 EXCEPTION
650 WHEN FND_API.G_EXC_ERROR THEN
651 x_return_status := FND_API.G_RET_STS_ERROR;
652 FND_MSG_Pub.count_and_get
653 ( p_count => x_msg_count
654 , p_data => x_msg_data
655 );
656 WHEN OTHERS THEN
657 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
658 x_return_status := SQLCODE;
659 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
660 , l_api_name
661 );
662 /* Get message count and data */
663 FND_MSG_Pub.count_and_get
664 ( p_count => x_msg_count
665 , p_data => x_msg_data
666 );
667 END restrictions_insert;
668
669 /*Restrictions update for the form will consist two parts,
670 * 1) delete the current rows for the rule_id
671 * 2) insert the new rows for the current p_mtl_picking_rule_rec
672 */
673 PROCEDURE Restrictions_update
674 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
675 , x_return_status OUT NOCOPY VARCHAR2
676 , x_msg_data OUT NOCOPY VARCHAR2
677 , x_msg_count OUT NOCOPY NUMBER)
678 IS
679 l_api_name CONSTANT VARCHAR2 (30) := 'Restrictions_Update';
680 Begin
681 x_return_status := FND_API.G_RET_STS_SUCCESS;
682 inv_rule_gen_pvt.Restrictions_delete
683 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
684 , x_return_status => x_return_status
685 , x_msg_data => x_msg_data
686 , x_msg_count => x_msg_count
687 );
688 inv_rule_gen_pvt.Restrictions_insert
689 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
690 , x_return_status => x_return_status
691 , x_msg_data => x_msg_data
692 , x_msg_count => x_msg_count
693 );
694 EXCEPTION
695 WHEN FND_API.G_EXC_ERROR THEN
696 x_return_status := FND_API.G_RET_STS_ERROR;
697 FND_MSG_Pub.count_and_get
698 ( p_count => x_msg_count
699 , p_data => x_msg_data
700 );
701 WHEN OTHERS THEN
702 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
703 x_return_status := SQLCODE;
704 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
705 , l_api_name
706 );
707 /* Get message count and data */
708 FND_MSG_Pub.count_and_get
709 ( p_count => x_msg_count
710 , p_data => x_msg_data
711 );
712 END restrictions_update;
713
714 PROCEDURE Restrictions_delete
715 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
716 , x_return_status OUT NOCOPY VARCHAR2
717 , x_msg_data OUT NOCOPY VARCHAR2
718 , x_msg_count OUT NOCOPY NUMBER)
719 IS
720 l_api_name CONSTANT VARCHAR2 (30) := 'Restrictions_Delete';
721 Begin
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723 Delete wms_restrictions where rule_id = p_mtl_picking_rule_rec.wms_rule_id;
724 EXCEPTION
725 WHEN FND_API.G_EXC_ERROR THEN
726 x_return_status := FND_API.G_RET_STS_ERROR;
727 FND_MSG_Pub.count_and_get
728 ( p_count => x_msg_count
729 , p_data => x_msg_data
730 );
731 WHEN OTHERS THEN
732 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
733 x_return_status := SQLCODE;
734 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
735 , l_api_name
736 );
737 /* Get message count and data */
738 FND_MSG_Pub.count_and_get
739 ( p_count => x_msg_count
740 , p_data => x_msg_data
741 );
742 END restrictions_delete;
743
744 PROCEDURE consistency_insert
745 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
746 , x_return_status OUT NOCOPY VARCHAR2
747 , x_msg_data OUT NOCOPY VARCHAR2
748 , x_msg_count OUT NOCOPY NUMBER
749 )
750 IS
751 l_api_name CONSTANT VARCHAR2 (30) := 'consistency_insert';
752 l_picking_rule_rec INV_RULE_GEN_PVT.picking_rule_rec;
753 l_consistency_id NUMBER;
754 l_row_id VARCHAR2(500);
755 l_parameter_id NUMBER;
756 Begin
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758 l_picking_rule_rec := p_mtl_picking_rule_rec;
759 If p_mtl_picking_rule_rec.single_lot = 'Y' then
760 -- find the parameter for lot.lot_number
761 l_parameter_id := 60006; -- lot.lot_Number
762 select wms_rule_consistencies_s.nextval into l_consistency_Id from dual;
763
764 WMS_RULE_CONSISTENCIES_PKG.INSERT_ROW(
765 X_ROWID => l_ROW_ID,
766 X_CONSISTENCY_ID => l_CONSISTENCY_ID,
767 X_RULE_ID => l_picking_rule_rec.WMS_RULE_ID,
768 X_CREATION_DATE => sysdate,
769 X_CREATED_BY => fnd_global.user_id,
770 X_LAST_UPDATE_DATE => sysdate,
771 X_LAST_UPDATED_BY => fnd_global.user_id,
772 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
773 X_PARAMETER_ID => l_parameter_id,
774 X_ATTRIBUTE_CATEGORY => '',
775 X_ATTRIBUTE1 => '',
776 X_ATTRIBUTE2 => '',
777 X_ATTRIBUTE3 => '',
778 X_ATTRIBUTE4 => '',
779 X_ATTRIBUTE5 => '',
780 X_ATTRIBUTE6 => '',
781 X_ATTRIBUTE7 => '',
782 X_ATTRIBUTE8 => '',
783 X_ATTRIBUTE9 => '',
784 X_ATTRIBUTE10 => '',
785 X_ATTRIBUTE11 => '',
786 X_ATTRIBUTE12 => '',
787 X_ATTRIBUTE13 => '',
788 X_ATTRIBUTE14 => '',
789 X_ATTRIBUTE15 => ''
790 );
791
792 end if;
793 EXCEPTION
794 WHEN FND_API.G_EXC_ERROR THEN
795 x_return_status := FND_API.G_RET_STS_ERROR;
796 FND_MSG_Pub.count_and_get
797 ( p_count => x_msg_count
798 , p_data => x_msg_data
799 );
800 WHEN OTHERS THEN
801 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
802 x_return_status := SQLCODE;
803 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
804 , l_api_name
805 );
806 /* Get message count and data */
807 FND_MSG_Pub.count_and_get
808 ( p_count => x_msg_count
809 , p_data => x_msg_data
810 );
811 END consistency_insert;
812
813 PROCEDURE Consistency_update
814 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
815 , x_return_status OUT NOCOPY VARCHAR2
816 , x_msg_data OUT NOCOPY VARCHAR2
817 , x_msg_count OUT NOCOPY NUMBER
818 )
819 IS
820 l_api_name CONSTANT VARCHAR2 (30) := 'Consistency_Update';
821 Begin
822 x_return_status := FND_API.G_RET_STS_SUCCESS;
823 inv_rule_gen_pvt.consistency_delete
824 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
825 , x_return_status => x_return_status
826 , x_msg_data => x_msg_data
827 , x_msg_count => x_msg_count
828 );
829 inv_rule_gen_pvt.consistency_insert
830 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
831 , x_return_status => x_return_status
832 , x_msg_data => x_msg_data
833 , x_msg_count => x_msg_count
834 );
835 EXCEPTION
836 WHEN FND_API.G_EXC_ERROR THEN
837 x_return_status := FND_API.G_RET_STS_ERROR;
838 FND_MSG_Pub.count_and_get
839 ( p_count => x_msg_count
840 , p_data => x_msg_data
841 );
842 WHEN OTHERS THEN
843 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
844 x_return_status := SQLCODE;
845 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
846 , l_api_name
847 );
848 /* Get message count and data */
849 FND_MSG_Pub.count_and_get
850 ( p_count => x_msg_count
851 , p_data => x_msg_data
852 );
853 END consistency_update;
854
855 PROCEDURE Consistency_delete
856 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
857 , x_return_status OUT NOCOPY VARCHAR2
858 , x_msg_data OUT NOCOPY VARCHAR2
859 , x_msg_count OUT NOCOPY NUMBER
860 )
861 IS
862 l_api_name CONSTANT VARCHAR2 (30) := 'Consistency_Delete';
863 Begin
864 x_return_status := FND_API.G_RET_STS_SUCCESS;
865 Delete wms_rule_consistencies where rule_id = p_mtl_picking_Rule_rec.wms_rule_id;
866 EXCEPTION
867 WHEN FND_API.G_EXC_ERROR THEN
868 x_return_status := FND_API.G_RET_STS_ERROR;
869 FND_MSG_Pub.count_and_get
870 ( p_count => x_msg_count
871 , p_data => x_msg_data
872 );
873 WHEN OTHERS THEN
874 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
875 x_return_status := SQLCODE;
876 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
877 , l_api_name
878 );
879 /* Get message count and data */
880 FND_MSG_Pub.count_and_get
881 ( p_count => x_msg_count
882 , p_data => x_msg_data
883 );
884 END consistency_delete;
885
886 PROCEDURE Sorting_criteria_insert
887 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
888 , x_return_status OUT NOCOPY VARCHAR2
889 , x_msg_data OUT NOCOPY VARCHAR2
890 , x_msg_count OUT NOCOPY NUMBER)
891 IS
892 l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_insert';
893 l_sort_order NUMBER;
894 l_sequence NUMBER;
895 l_row_id VARCHAR2(500);
896 l_parameter_id NUMBER;
897 l_order_code NUMBER;
898 i NUMBER;
899 l_go_ahead NUMBER;
900 Begin
901 x_return_status := FND_API.G_RET_STS_SUCCESS;
902 debug('sort insert, lot_sort_rank '||p_mtl_picking_rule_rec.lot_sort_rank);
903 debug('sort insert, revision_sort_rank '||p_mtl_picking_rule_rec.revision_sort_rank);
904 debug('sort insert, sub_sort_rank '||p_mtl_picking_rule_rec.subinventory_sort_rank);
905 debug('sort insert, locator_sort_rank '||p_mtl_picking_rule_rec.locator_sort_rank);
906 FOR i IN 1..4 LOOP
907 l_go_ahead := 0; -- NO insert
908 If i = 1 AND p_mtl_picking_rule_rec.lot_sort IS NOT NULL Then
909 --Get the parameter_id for lot.FIFO/FEFO
910 --Get the parameter_id lot_number, base.lot_number 10004
911 l_sequence := p_mtl_picking_rule_rec.lot_sort_rank;
912 l_sort_order := p_mtl_picking_rule_rec.lot_sort;
913 if l_sort_order = 3 THEN -- FIFO
914 l_parameter_id := 10008; -- Receipt Date --60012; -- creation_date
915 l_order_code := 1; -- Asceding
916 elsif l_sort_order = 4 THEN -- FEFO
917 l_parameter_id := 60018; -- expiration_date
918 l_order_code := 1; -- Asceding
919 elsif l_sort_order = 1 THEN -- Lot Number Asc
920 l_parameter_id := 10004; -- Lot number
921 l_order_code := 1; -- Asceding
922 elsif l_sort_order = 2 THEN -- Lot Number Desc
923 l_parameter_id := 10004; -- Lot number
924 l_order_code := 2; -- Descending
925 end if;
926 l_go_ahead := 1;
927 End if;
928 If i = 2 AND p_mtl_picking_rule_rec.revision_sort IS NOT NULL Then
929 --Get the parameter_id for object mtl_item_revisions.revision object_id=5
930 --Get the parameter_id for object mtl_item_revisions.effectivity_date object_id=5 50013
931 l_sequence := p_mtl_picking_rule_rec.revision_sort_rank;
932 l_sort_order := p_mtl_picking_rule_rec.revision_sort;
933 if l_sort_order = 1 THEN -- revision asceding
934 l_parameter_id := 10003; -- revision
935 l_order_code := 1; -- Asceding
936 elsif l_sort_order = 2 THEN -- revision desceding
937 l_parameter_id := 10003; -- expiration_date
938 l_order_code := 2; -- Desceding
939 elsif l_sort_order = 3 THEN -- effective date asceding
940 l_parameter_id := 50013; -- effective_date
941 l_order_code := 1; -- Asceding
942 elsif l_sort_order = 4 THEN -- effective date desceding
943 l_parameter_id := 50013; -- effective_date
944 l_order_code := 2; -- Desceding
945 end if;
946 l_go_ahead := 1;
947 End if;
948 If i = 3 AND p_mtl_picking_rule_rec.locator_sort IS NOT NULL Then
949 --Get the parameter_id for object source locator.locator identifier
950 -- mtl_item_locations.picking_order object_id=8
951 -- stock on hand. receipt date object_id=54
952 l_sequence := p_mtl_picking_rule_rec.locator_sort_rank;
953 l_sort_order := p_mtl_picking_rule_rec.locator_sort;
954 if l_sort_order = 1 THEN -- locator asceding
955 l_parameter_id := 80012; -- picking order
956 l_order_code := 1; -- Asceding
957 elsif l_sort_order = 2 THEN -- revision desceding
958 l_parameter_id := 80012; -- picking order
959 l_order_code := 2; -- Desceding
960 elsif l_sort_order = 3 THEN -- receipt date asceding
961 l_parameter_id := 10008; -- receipt date
962 l_order_code := 1; -- Asceding
963 elsif l_sort_order = 4 THEN -- receipt date desceding
964 l_parameter_id := 10008; -- receipt date
965 l_order_code := 2; -- Desceding
966 end if;
967 l_go_ahead := 1;
968 End if;
969 If i = 4 AND p_mtl_picking_rule_rec.subinventory_sort IS NOT NULL Then
970 --Get the parameter_id for object source subinventory
971 -- mtl_secondary_inventories.picking order object_id=7
972 -- stock on hand. receipt date object_id=54
973 l_sequence := p_mtl_picking_rule_rec.subinventory_sort_rank;
974 l_sort_order := p_mtl_picking_rule_rec.subinventory_sort;
975 if l_sort_order = 1 THEN -- locator asceding
976 l_parameter_id := 70015; -- picking order
977 l_order_code := 1; -- Asceding
978 elsif l_sort_order = 2 THEN -- revision desceding
979 l_parameter_id := 70015; -- picking order
980 l_order_code := 2; -- Desceding
981 elsif l_sort_order = 3 THEN -- receipt date asceding
982 l_parameter_id := 10008; -- receipt date
983 l_order_code := 1; -- Asceding
984 elsif l_sort_order = 4 THEN -- receipt date desceding
985 l_parameter_id := 10008; -- receipt date
986 l_order_code := 2; -- Desceding
987 end if;
988 l_go_ahead := 1;
989 End if;
990
991 if l_go_ahead = 1 THEN
992 debug('sort insert, '|| i);
993 debug('sort insert, sequence '||l_sequence);
994 debug('sort insert, wms_rule_id '||p_mtl_picking_rule_rec.wms_rule_id);
995 wms_SORT_CRITERIA_PKG.Insert_Row
996 (
997 X_Rowid => l_Row_Id,
998 X_Rule_Id => p_mtl_picking_rule_rec.WMS_Rule_Id,
999 X_Sequence_Number => l_Sequence,
1000 X_Parameter_Id => l_Parameter_Id,
1001 X_Order_Code => l_Order_Code,
1002 X_Created_By => fnd_global.user_id,
1003 X_Creation_Date => sysdate,
1004 X_Last_Updated_By => fnd_global.user_id,
1005 X_Last_Update_Date => sysdate,
1006 X_Last_Update_Login => fnd_global.login_id,
1007 X_Attribute1 => null,
1008 X_Attribute2 => null,
1009 X_Attribute3 => null,
1010 X_Attribute4 => null,
1011 X_Attribute5 => null,
1012 X_Attribute6 => null,
1013 X_Attribute7 => null,
1014 X_Attribute8 => null,
1015 X_Attribute9 => null,
1016 X_Attribute10 => null,
1017 X_Attribute11 => null,
1018 X_Attribute12 => null,
1019 X_Attribute13 => null,
1020 X_Attribute14 => null,
1021 X_Attribute15 => null,
1022 X_Attribute_Category => null
1023 );
1024 end if;
1025 END LOOP;
1026 EXCEPTION
1027 WHEN FND_API.G_EXC_ERROR THEN
1028 x_return_status := FND_API.G_RET_STS_ERROR;
1029 FND_MSG_Pub.count_and_get
1030 ( p_count => x_msg_count
1031 , p_data => x_msg_data
1032 );
1033 WHEN OTHERS THEN
1034 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1035 x_return_status := SQLCODE;
1036 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1037 , l_api_name
1038 );
1039 /* Get message count and data */
1040 FND_MSG_Pub.count_and_get
1041 ( p_count => x_msg_count
1042 , p_data => x_msg_data
1043 );
1044 END sorting_criteria_insert;
1045
1046 /* Update consist two parts
1047 * 1) Delete the current row for the rule_id
1048 * 2) Insert new rows for the current setup
1049 */
1050
1051 PROCEDURE sorting_criteria_update
1052 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1053 , x_return_status OUT NOCOPY VARCHAR2
1054 , x_msg_data OUT NOCOPY VARCHAR2
1055 , x_msg_count OUT NOCOPY NUMBER)
1056 IS
1057 l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_Update';
1058 Begin
1059 x_return_status := FND_API.G_RET_STS_SUCCESS;
1060 inv_rule_gen_pvt.sorting_criteria_delete
1061 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1062 , x_return_status => x_return_status
1063 , x_msg_data => x_msg_data
1064 , x_msg_count => x_msg_count
1065 );
1066 inv_rule_gen_pvt.sorting_criteria_insert
1067 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1068 , x_return_status => x_return_status
1069 , x_msg_data => x_msg_data
1070 , x_msg_count => x_msg_count
1071 );
1072 EXCEPTION
1073 WHEN FND_API.G_EXC_ERROR THEN
1074 x_return_status := FND_API.G_RET_STS_ERROR;
1075 FND_MSG_Pub.count_and_get
1076 ( p_count => x_msg_count
1077 , p_data => x_msg_data
1078 );
1079 WHEN OTHERS THEN
1080 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1081 x_return_status := SQLCODE;
1082 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1083 , l_api_name
1084 );
1085 /* Get message count and data */
1086 FND_MSG_Pub.count_and_get
1087 ( p_count => x_msg_count
1088 , p_data => x_msg_data
1089 );
1090 END sorting_criteria_update;
1091
1092 PROCEDURE Sorting_criteria_delete
1093 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1094 , x_return_status OUT NOCOPY VARCHAR2
1095 , x_msg_data OUT NOCOPY VARCHAR2
1096 , x_msg_count OUT NOCOPY NUMBER)
1097 IS
1098 l_api_name CONSTANT VARCHAR2 (30) := 'Sorting_criteria_Delete';
1099 Begin
1100 x_return_status := FND_API.G_RET_STS_SUCCESS;
1101 debug('delete sorting criteria');
1102 Delete wms_sort_criteria where rule_id = p_mtl_picking_Rule_rec.wms_rule_id;
1103 EXCEPTION
1104 WHEN FND_API.G_EXC_ERROR THEN
1105 x_return_status := FND_API.G_RET_STS_ERROR;
1106 FND_MSG_Pub.count_and_get
1107 ( p_count => x_msg_count
1108 , p_data => x_msg_data
1109 );
1110 WHEN OTHERS THEN
1111 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1112 x_return_status := SQLCODE;
1113 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1114 , l_api_name
1115 );
1116 /* Get message count and data */
1117 FND_MSG_Pub.count_and_get
1118 ( p_count => x_msg_count
1119 , p_data => x_msg_data
1120 );
1121 END sorting_criteria_delete;
1122
1123 PROCEDURE Strategy_insert
1124 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1125 , x_return_status OUT NOCOPY VARCHAR2
1126 , x_msg_data OUT NOCOPY VARCHAR2
1127 , x_msg_count OUT NOCOPY NUMBER
1128 )
1129 IS
1130 l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_insert';
1131 l_strategy_id NUMBER;
1132 l_strategy_name VARCHAR2(50);
1133 l_rowid VARCHAR2(500);
1134 Begin
1135 x_return_status := FND_API.G_RET_STS_SUCCESS;
1136 --check enabled flag for the rule, only insert strategy for enabled rules
1137 debug('Procedure Strategy_insert');
1138 if p_mtl_picking_rule_rec.enabled_flag <> 'Y' Then
1139 return;
1140 end if;
1141 -- insert the strategy with the same name of the rule
1142 select WMS_strategies_s.nextval into l_strategy_id from sys.dual;
1143 l_strategy_name := p_mtl_picking_rule_rec.name;
1144 debug('strategy_id '||l_strategy_id);
1145 debug('strategy_name '||l_strategy_name);
1146 WMS_STRATEGIES_PKG.Insert_Row
1147 (
1148 X_Rowid => l_ROWID
1149 , X_Strategy_Id => l_Strategy_Id
1150 , X_Organization_Id => -1
1151 , X_Type_Code => 2
1152 , X_Name => l_strategy_name
1153 , X_Description => l_strategy_name
1154 , X_Enabled_Flag => 'Y'
1155 , X_User_Defined_Flag => 'Y'
1156 , X_Created_By => fnd_global.user_id
1157 , X_Creation_Date => SYSDATE
1158 , X_Last_Updated_By => fnd_global.user_id
1159 , X_Last_Update_Date => SYSDATE
1160 , X_Last_Update_Login => fnd_global.login_id
1161 , X_Attribute1 => null
1162 , X_Attribute2 => null
1163 , X_Attribute3 => null
1164 , X_Attribute4 => null
1165 , X_Attribute5 => null
1166 , X_Attribute6 => null
1167 , X_Attribute7 => null
1168 , X_Attribute8 => null
1169 , X_Attribute9 => null
1170 , X_Attribute10 => null
1171 , X_Attribute11 => null
1172 , X_Attribute12 => null
1173 , X_Attribute13 => null
1174 , X_Attribute14 => null
1175 , X_Attribute15 => null
1176 , X_Attribute_Category => null
1177 );
1178 p_mtl_picking_rule_rec.wms_strategy_id := l_strategy_id;
1179 /* insert strategy_members */
1180 debug('calling insert strategy members ');
1181 WMS_STRATEGY_MEMBERS_PKG.Insert_Row(
1182 X_Rowid => l_RowId
1183 , X_Strategy_Id => l_Strategy_Id
1184 , X_Sequence_Number => 10
1185 , X_Rule_Id => p_mtl_picking_rule_rec.wms_Rule_Id
1186 , X_Partial_Success_Allowed_Flag=> p_mtl_picking_rule_rec.Partial_Allowed_Flag
1187 , X_Effective_From => null
1188 , X_Effective_To => null
1189 , X_Created_By => fnd_global.user_id
1190 , X_Creation_Date => sysdate
1191 , X_Last_Updated_By => fnd_global.user_id
1192 , X_Last_Update_Date => sysdate
1193 , X_Last_Update_Login => fnd_global.login_id
1194 , X_Attribute1 => null
1195 , X_Attribute2 => null
1196 , X_Attribute3 => null
1197 , X_Attribute4 => null
1198 , X_Attribute5 => null
1199 , X_Attribute6 => null
1200 , X_Attribute7 => null
1201 , X_Attribute8 => null
1202 , X_Attribute9 => null
1203 , X_Attribute10 => null
1204 , X_Attribute11 => null
1205 , X_Attribute12 => null
1206 , X_Attribute13 => null
1207 , X_Attribute14 => null
1208 , X_Attribute15 => null
1209 , X_Attribute_Category => null
1210 , X_Date_Type_Code => 11 -- always
1211 , X_Date_Type_Lookup_Type => null
1212 , X_Date_Type_From => null
1213 , X_Date_Type_To => null
1214 );
1215 EXCEPTION
1216 WHEN FND_API.G_EXC_ERROR THEN
1217 x_return_status := FND_API.G_RET_STS_ERROR;
1218 FND_MSG_Pub.count_and_get
1219 ( p_count => x_msg_count
1220 , p_data => x_msg_data
1221 );
1222 WHEN OTHERS THEN
1223 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1224 x_return_status := SQLCODE;
1225 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1226 , l_api_name
1227 );
1228 /* Get message count and data */
1229 FND_MSG_Pub.count_and_get
1230 ( p_count => x_msg_count
1231 , p_data => x_msg_data
1232 );
1233 End strategy_insert;
1234
1235 /* Only enabled flag can be updated. */
1236 /* disable the strategy when rule is disabled */
1237 PROCEDURE Strategy_update
1238 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1239 , x_return_status OUT NOCOPY VARCHAR2
1240 , x_msg_data OUT NOCOPY VARCHAR2
1241 , x_msg_count OUT NOCOPY NUMBER
1242 )
1243 IS
1244 l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Update';
1245 Begin
1246 x_return_status := FND_API.G_RET_STS_SUCCESS;
1247 if p_mtl_picking_rule_rec.enabled_flag = 'N' then
1248 WMS_STRATEGIES_PKG.Update_Row(
1249 X_Strategy_Id => p_mtl_picking_rule_rec.wms_Strategy_Id,
1250 X_Organization_Id => -1,
1251 X_Type_Code => 2,
1252 X_Name => p_mtl_picking_rule_rec.name,
1253 X_Description => p_mtl_picking_rule_rec.name,
1254 X_Enabled_Flag => p_mtl_picking_rule_rec.enabled_flag,
1255 X_User_Defined_Flag => 'Y',
1256 X_Last_Updated_By => fnd_global.user_id,
1257 X_Last_Update_Date => sysdate,
1258 X_Last_Update_Login => fnd_global.user_id,
1259 X_Attribute1 => null,
1260 X_Attribute2 => null,
1261 X_Attribute3 => null,
1262 X_Attribute4 => null,
1263 X_Attribute5 => null,
1264 X_Attribute6 => null,
1265 X_Attribute7 => null,
1266 X_Attribute8 => null,
1267 X_Attribute9 => null,
1268 X_Attribute10 => null,
1269 X_Attribute11 => null,
1270 X_Attribute12 => null,
1271 X_Attribute13 => null,
1272 X_Attribute14 => null,
1273 X_Attribute15 => null,
1274 X_Attribute_Category => null
1275 );
1276 end if;
1277 EXCEPTION
1278 WHEN FND_API.G_EXC_ERROR THEN
1279 x_return_status := FND_API.G_RET_STS_ERROR;
1280 FND_MSG_Pub.count_and_get
1281 ( p_count => x_msg_count
1282 , p_data => x_msg_data
1283 );
1284 WHEN OTHERS THEN
1285 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1286 x_return_status := SQLCODE;
1287 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1288 , l_api_name
1289 );
1290 /* Get message count and data */
1291 FND_MSG_Pub.count_and_get
1292 ( p_count => x_msg_count
1293 , p_data => x_msg_data
1294 );
1295 END;
1296
1297 /* when rule is deleted, strategy is also deleted */
1298 PROCEDURE Strategy_delete
1299 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1300 , x_return_status OUT NOCOPY VARCHAR2
1301 , x_msg_data OUT NOCOPY VARCHAR2
1302 , x_msg_count OUT NOCOPY NUMBER
1303 )
1304 IS
1305 l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Delete';
1306 Begin
1307 x_return_status := FND_API.G_RET_STS_SUCCESS;
1308 null;
1309 EXCEPTION
1310 WHEN FND_API.G_EXC_ERROR THEN
1311 x_return_status := FND_API.G_RET_STS_ERROR;
1312 FND_MSG_Pub.count_and_get
1313 ( p_count => x_msg_count
1314 , p_data => x_msg_data
1315 );
1316 WHEN OTHERS THEN
1317 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1318 x_return_status := SQLCODE;
1319 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1320 , l_api_name
1321 );
1322 /* Get message count and data */
1323 FND_MSG_Pub.count_and_get
1324 ( p_count => x_msg_count
1325 , p_data => x_msg_data
1326 );
1327 END;
1328
1329 PROCEDURE Rule_Enabled_Flag
1330 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1331 , x_return_status OUT NOCOPY VARCHAR2
1332 , x_msg_data OUT NOCOPY VARCHAR2
1333 , x_msg_count OUT NOCOPY NUMBER)
1334 IS
1335 l_api_name CONSTANT VARCHAR2 (30) := 'Strategy_Delete';
1336 l_picking_rule_rec INV_RULE_GEN_PVT.picking_rule_rec;
1337 l_return_status VARCHAR2(1);
1338 l_msg_data VARCHAR2(2000);
1339 l_msg_count NUMBER;
1340 v_type_code number;
1341 BEGIN
1342 x_return_status := FND_API.G_RET_STS_SUCCESS;
1343 l_picking_rule_rec := p_mtl_picking_rule_rec;
1344 debug('Procedure rule enable flag');
1345 IF l_picking_rule_rec.Enabled_Flag = 'Y' THEN
1346 l_picking_rule_rec.enabled_flag:='Y';
1347 -- Check rule Syntax
1348 debug('checksyntax');
1349 WMS_rule_PVT.CheckSyntax (
1350 p_api_version => 1.0
1351 ,p_init_msg_list => FND_API.G_TRUE
1352 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1353 ,x_return_status => l_return_status
1354 ,x_msg_count => l_msg_count
1355 ,x_msg_data => l_msg_data
1356 ,p_rule_id => l_picking_rule_rec.wms_rule_ID
1357 );
1358 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1359 debug('checksyntax failed');
1360 --INV_GLOBAL_PKG.Show_Errors;
1361 l_picking_rule_rec.Enabled_Flag := 'N';
1362 END IF;
1363 --- Calling The Generate l_mtl_picking_rule_rec.list pkg
1364 IF l_picking_rule_rec.Enabled_Flag = 'Y' THEN
1365 debug('generateruleexecpkgs');
1366 WMS_rule_gen_pkgs.GenerateRuleExecPkgs
1367 (
1368 p_api_version => 1.0
1369 ,p_init_msg_list => FND_API.G_TRUE
1370 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1371 ,x_return_status => l_return_status
1372 ,x_msg_count => l_msg_count
1373 ,x_msg_data => l_msg_data
1374 ,p_pick_code => 2
1375 ,p_put_code => null
1376 ,p_task_code => null
1377 ,p_label_code => null
1378 ,p_CG_code => null
1379 ,p_op_code => null
1380 ,p_pkg_type => 'B'
1381 );
1382
1383 /* if l_picking_rule_rec.wms_strategy_id is null THEN
1384 inv_rule_gen_pvt.Strategy_insert
1385 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1386 , x_return_status => x_return_status
1387 , x_msg_data => x_msg_data
1388 , x_msg_count => x_msg_count
1389 );
1390 Else
1391 inv_rule_gen_pvt.Strategy_Update
1392 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1393 , x_return_status => x_return_status
1394 , x_msg_data => x_msg_data
1395 , x_msg_count => x_msg_count
1396 );
1397 End if;
1398 */
1399 debug('after strategy_insert '||p_mtl_picking_rule_rec.wms_strategy_id);
1400 update wms_rules_b
1401 set enabled_flag = 'Y'
1402 where rule_id=p_mtl_picking_rule_rec.wms_rule_id;
1403 commit;
1404 End if;
1405 ELSE -- disable the flag
1406 l_picking_rule_rec.enabled_flag:='N';
1407 debug('disabling the rule and strategy');
1408 /*
1409 inv_rule_gen_pvt.Strategy_Update
1410 (p_mtl_picking_rule_rec=> p_mtl_picking_rule_rec
1411 , x_return_status => x_return_status
1412 , x_msg_data => x_msg_data
1413 , x_msg_count => x_msg_count
1414 );
1415 */
1416 debug('after strategy_update '||p_mtl_picking_rule_rec.wms_strategy_id);
1417 update wms_rules_b
1418 set enabled_flag = 'N'
1419 where rule_id=p_mtl_picking_rule_rec.wms_rule_id;
1420 commit;
1421 END IF;
1422 EXCEPTION
1423 WHEN FND_API.G_EXC_ERROR THEN
1424 x_return_status := FND_API.G_RET_STS_ERROR;
1425 FND_MSG_Pub.count_and_get
1426 ( p_count => x_msg_count
1427 , p_data => x_msg_data
1428 );
1429 WHEN OTHERS THEN
1430 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1431 x_return_status := SQLCODE;
1432 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1433 , l_api_name
1434 );
1435 /* Get message count and data */
1436 FND_MSG_Pub.count_and_get
1437 ( p_count => x_msg_count
1438 , p_data => x_msg_data
1439 );
1440 END Rule_Enabled_Flag;
1441
1442 FUNCTION rule_assigned_to_strategy
1443 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1444 , x_return_status OUT NOCOPY VARCHAR2
1445 , x_msg_data OUT NOCOPY VARCHAR2
1446 , x_msg_count OUT NOCOPY NUMBER)
1447 RETURN BOOLEAN IS
1448 l_api_name CONSTANT VARCHAR2 (30) := 'rule_assigned_to_strategy';
1449 /*
1450 CURSOR L_curStrategyMembers(p_rule_id IN NUMBER) IS
1451 SELECT 'X'
1452 FROM wms_strategy_members MPSM, wms_strategies_b S
1453 WHERE MPSM.rule_id = p_rule_id
1454 AND S.Strategy_Id = MPSM.Strategy_Id
1455 AND S.Enabled_Flag = 'Y'
1456 AND NVL(MPSM.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
1457 AND NVL(MPSM.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
1458 AND rownum < 2;
1459
1460 CURSOR StratAssignments_old (p_strategy_id in NUMBER) IS
1461 SELECT 'X'
1462 FROM wms_selection_criteria_txn WSCT
1463 WHERE WSCT.return_type_code = 'S'
1464 AND WSCT.return_type_id = p_strategy_id
1465 AND WSCT.enabled_flag = 1
1466 AND NVL(WSCT.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
1467 AND NVL(WSCT.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
1468 AND rownum < 2;
1469 */
1470 ---- New Cursor added for checking if the rule is assigned directly in the assignment matrix
1471
1472 CURSOR StratAssignments_new (p_rule_id IN NUMBER) IS
1473 SELECT 'X'
1474 FROM wms_selection_criteria_txn WSCT
1475 WHERE WSCT.return_type_code = 'R'
1476 AND WSCT.return_type_id = p_rule_id
1477 AND WSCT.enabled_flag = 1
1478 AND NVL(WSCT.Effective_From,TO_DATE('01011900','DDMMYYYY')) <= TRUNC(sysdate)
1479 AND NVL(WSCT.Effective_To,TO_DATE('31124000','DDMMYYYY')) >= TRUNC(sysdate)
1480 AND rownum < 2;
1481
1482 --- Setting the profile option to be deleted later on
1483 l_rules_engine_mode NUMBER := NVL(FND_PROFILE.VALUE('WMS_RULES_ENGINE_MODE'), 0);
1484 l_nDummy VARCHAR2(1);
1485 l_bReturn BOOLEAN;
1486 BEGIN
1487 x_return_status := FND_API.G_RET_STS_SUCCESS;
1488 debug('check rule is assigned');
1489 debug('rule_id '|| p_mtl_picking_rule_rec.wms_rule_id);
1490 debug('strategy_id '|| p_mtl_picking_rule_rec.wms_strategy_id);
1491 /*
1492 -- Check if rule is assigned to strategy member
1493 OPEN L_curStrategyMembers(p_mtl_picking_rule_rec.wms_rule_id);
1494 FETCH L_curStrategyMembers INTO L_nDummy;
1495 IF L_curStrategyMembers%NOTFOUND THEN
1496 L_bReturn := FALSE;
1497 ELSE
1498 L_bReturn := TRUE;
1499 END IF;
1500
1501 OPEN StratAssignments_old(p_mtl_picking_rule_rec.wms_strategy_id);
1502 FETCH StratAssignments_old into L_nDummy;
1503 IF StratAssignments_old%NOTFOUND THEN
1504 L_bReturn := FALSE;
1505 ELSE
1506 L_bReturn := TRUE;
1507 END IF;
1508 CLOSE StratAssignments_old;
1509 */
1510 --- checking if the rule is assigned directly in the assignment matrix
1511
1512 IF (l_rules_engine_mode = 1) then
1513
1514 OPEN StratAssignments_new(p_mtl_picking_rule_rec.wms_strategy_id);
1515 FETCH StratAssignments_new into L_nDummy;
1516 IF StratAssignments_new%NOTFOUND THEN
1517 L_bReturn := FALSE;
1518 ELSE
1519 L_bReturn := TRUE;
1520 END IF;
1521 CLOSE StratAssignments_new;
1522 end if;
1523
1524 RETURN(l_bReturn);
1525 EXCEPTION
1526 WHEN FND_API.G_EXC_ERROR THEN
1527 x_return_status := FND_API.G_RET_STS_ERROR;
1528 FND_MSG_Pub.count_and_get
1529 ( p_count => x_msg_count
1530 , p_data => x_msg_data
1531 );
1532 WHEN OTHERS THEN
1533 debug('sqlerror'|| SUBSTRB(SQLERRM, 1, 100));
1534 x_return_status := SQLCODE;
1535 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1536 , l_api_name
1537 );
1538 /* Get message count and data */
1539 FND_MSG_Pub.count_and_get
1540 ( p_count => x_msg_count
1541 , p_data => x_msg_data
1542 );
1543 END rule_assigned_to_strategy;
1544
1545 PROCEDURE GenerateRulePKG
1546 (p_mtl_picking_rule_rec IN OUT NOCOPY INV_RULE_GEN_PVT.picking_rule_rec
1547 , x_return_status OUT NOCOPY VARCHAR2
1548 , x_msg_data OUT NOCOPY VARCHAR2
1549 , x_msg_count OUT NOCOPY NUMBER)
1550 IS
1551 l_api_name CONSTANT VARCHAR2 (30) := 'GenerateRulePKG';
1552 l_return_status VARCHAR2(1);
1553 l_msg_data VARCHAR2(2000);
1554 l_msg_count NUMBER;
1555 rec_status VARCHAR2(25);
1556 BEGIN
1557 x_return_status := FND_API.G_RET_STS_SUCCESS;
1558 IF p_mtl_picking_rule_rec.Enabled_Flag = 'Y' THEN
1559 WMS_Rule_PVT.GenerateRulePackage
1560 (
1561 p_api_version => 1.0
1562 ,p_init_msg_list => FND_API.G_TRUE
1563 ,p_validation_level => FND_API.G_VALID_LEVEL_NONE
1564 ,x_return_status => x_return_status
1565 ,x_msg_count => x_msg_count
1566 ,x_msg_data => x_msg_data
1567 ,p_rule_id => p_mtl_picking_rule_rec.wms_RULE_ID
1568 );
1569 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS)
1570 THEN
1571 p_mtl_picking_rule_rec.Enabled_Flag := 'N';
1572 RAISE FND_API.G_EXC_ERROR;
1573 ELSE
1574 fnd_message.set_name('WMS','WMS_PACKAGE_REGENERATE');
1575 FND_MSG_PUB.Add;
1576 RAISE FND_API.G_EXC_ERROR;
1577 END IF;
1578 END IF;
1579 EXCEPTION
1580 WHEN FND_API.G_EXC_ERROR THEN
1581 x_return_status := FND_API.G_RET_STS_ERROR;
1582 FND_MSG_Pub.count_and_get
1583 ( p_count => x_msg_count
1584 , p_data => x_msg_data
1585 );
1586 debug('regenerate rule pkgs. error exception');
1587 WHEN OTHERS THEN
1588 debug('regenerate others'||SQLCODE||'.');
1589 x_return_status := SQLCODE;
1590 FND_MSG_PUB.Add_Exc_Msg (g_pkg_name
1591 , l_api_name
1592 );
1593 /* Get message count and data */
1594 FND_MSG_Pub.count_and_get
1595 ( p_count => x_msg_count
1596 , p_data => x_msg_data
1597 );
1598 END GenerateRulePKG;
1599
1600 END inv_rule_gen_pvt;