[Home] [Help]
PACKAGE BODY: APPS.INV_AUTODETAIL
Source
1 PACKAGE BODY INV_AUTODETAIL AS
2 /* $Header: INVRSV4B.pls 120.2 2006/02/17 14:48:23 rambrose noship $ */
3 --
4 -- Global constant holding package name
5 g_pkg_name constant varchar2(50) := 'INV_AUTODETAIL';
6 --
7 -- Package Globals
8 --
9 TYPE g_pp_temp_rec_type IS RECORD
10 (
11 transaction_quantity NUMBER,
12 transaction_uom VARCHAR2(3),
13 primary_quantity NUMBER,
14 primary_uom VARCHAR2(3),
15 from_subinventory_code VARCHAR2(10),
16 from_locator_id NUMBER,
17 revision VARCHAR2(3),
18 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
19 lot_number VARCHAR2(80),
20 to_subinventory_code VARCHAR2(10),
21 to_locator_id NUMBER,
22 lot_expiration_date DATE,
23 reservation_id NUMBER,
24 from_cost_group_id NUMBER,
25 to_cost_group_id NUMBER
26 );
27 --
28 TYPE g_pp_temp_tbl_type IS TABLE OF g_pp_temp_rec_type INDEX BY BINARY_INTEGER;
29 --
30 -- sql statement built for detailing
31 g_stmt long;
32 --
33 g_tree_id NUMBER;
34
35 -- Globals added for pick release performance changes
36 g_inventory_item_id NUMBER;
37 g_organization_id NUMBER;
38 g_rule_id NUMBER;
39 --
40 -- a constant that stores the \n that works in different natural languages
41 g_line_feed VARCHAR2(1) := '
42 ';
43
44 --whether pjm is enabled or not
45 g_unit_eff_enabled VARCHAR2(1) := NULL;
46 --
47 -- Functions and Procedures
48 --
49 -- --------------------------------------------------------------------------
50 -- What does it do:
51 -- Fetches picking rule. First from mtl_system_items. If absent there, tries
52 -- mtl_parameters.
53 -- --------------------------------------------------------------------------
54 PROCEDURE get_pick_rule
55 ( p_organization_id IN NUMBER
56 ,p_inventory_item_id IN NUMBER
57 ,x_rule_id OUT NOCOPY NUMBER
58 ,x_return_status OUT NOCOPY VARCHAR2
59 ,x_msg_count OUT NOCOPY NUMBER
60 ,x_msg_data OUT NOCOPY VARCHAR2
61 )
62 IS
63 -- constants
64 l_api_name CONSTANT VARCHAR(30) := 'get_pick_rule';
65 --
66 l_rule_id NUMBER;
67 BEGIN
68
69 x_return_status := fnd_api.g_ret_sts_success ;
70 -- find rule from mtl_system_items
71 IF ((nvl(g_inventory_item_id,-9999) <> p_inventory_item_id) OR
72 (nvl(g_organization_id,-9999) <> p_organization_id)) THEN
73 IF inv_cache.set_item_rec(p_organization_id, p_inventory_item_id) THEN
74 g_rule_id := inv_cache.item_rec.picking_rule_id;
75 ELSE
76 g_rule_id := NULL;
77 END IF;
78 -- if failed, find rule from mtl_org_parameters
79 IF g_rule_id IS NULL THEN
80 IF inv_cache.set_org_rec(p_organization_id) THEN
81 g_rule_id := inv_cache.org_rec.default_picking_rule_id;
82 ELSE
83 g_rule_id := NULL;
84 END IF;
85 END IF;
86 g_inventory_item_id := p_inventory_item_id;
87 g_organization_id := p_organization_id;
88 END IF;
89 x_rule_id := g_rule_id;
90
91 EXCEPTION
92 WHEN fnd_api.g_exc_error THEN
93 x_return_status := fnd_api.g_ret_sts_error ;
94 --
95 WHEN fnd_api.g_exc_unexpected_error THEN
96 x_return_status := fnd_api.g_ret_sts_unexp_error ;
97 --
98 WHEN OTHERS THEN
99 x_return_status := fnd_api.g_ret_sts_unexp_error ;
100 --
101 IF (fnd_msg_pub.check_msg_level
102 (fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
103 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
104 END IF;
105 --
106 END get_pick_rule ;
107 --
108 --
109 -- --------------------------------------------------------------------------
110 -- What does it do:
111 -- Determines final location control based on location controls defined at
112 -- organization, subinventory and item level.
113 -- --------------------------------------------------------------------------
114 --
115 FUNCTION loc_control
116 ( p_org_control IN NUMBER
117 ,p_sub_control IN NUMBER
118 ,p_item_control IN NUMBER DEFAULT NULL
119 ,x_return_status OUT NOCOPY VARCHAR2
120 ,x_msg_count OUT NOCOPY NUMBER
121 ,x_msg_data OUT NOCOPY VARCHAR2
122 ) RETURN NUMBER
123 IS
124 --
125 -- constants
126 l_api_name CONSTANT VARCHAR(30) := 'loc_control';
127 --
128 -- return variable
129 l_locator_control NUMBER;
130 --
131 -- exception
132 invalid_loc_control_exception EXCEPTION;
133 --
134 BEGIN
135 IF (p_org_control = 1) THEN
136 l_locator_control := 1;
137 ELSIF (p_org_control = 2) THEN
138 l_locator_control := 2;
139 ELSIF (p_org_control = 3) THEN
140 l_locator_control := 2 ;
141 ELSIF (p_org_control = 4) THEN
142 IF (p_sub_control = 1) THEN
143 l_locator_control := 1;
144 ELSIF (p_sub_control = 2) THEN
145 l_locator_control := 2;
146 ELSIF (p_sub_control = 3) THEN
147 l_locator_control := 2;
148 ELSIF (p_sub_control = 5) THEN
149 IF (p_item_control = 1) THEN
150 l_locator_control := 1;
151 ELSIF (p_item_control = 2) THEN
152 l_locator_control := 2;
153 ELSIF (p_item_control = 3) THEN
154 l_locator_control := 2;
155 ELSIF (p_item_control IS NULL) THEN
156 l_locator_control := p_sub_control;
157 ELSE
158 RAISE invalid_loc_control_exception;
159 END IF;
160 ELSE
161 RAISE invalid_loc_control_exception;
162 END IF;
163 ELSE
164 RAISE invalid_loc_control_exception;
165 END IF;
166 --
167 x_return_status := fnd_api.g_ret_sts_success;
168 RETURN l_locator_control;
169 EXCEPTION
170 WHEN invalid_loc_control_exception THEN
171 fnd_message.set_name('INV','INV_INVALID_LOC_CONTROL');
172 fnd_msg_pub.ADD;
173 --
174 x_return_status := fnd_api.g_ret_sts_error ;
175 l_locator_control := -1 ;
176 RETURN l_locator_control ;
177 --
178 WHEN fnd_api.g_exc_error THEN
179 x_return_status := fnd_api.g_ret_sts_error ;
180 l_locator_control := -1 ;
181 RETURN l_locator_control ;
182 --
183 WHEN fnd_api.g_exc_unexpected_error THEN
184 x_return_status := fnd_api.g_ret_sts_unexp_error ;
185 l_locator_control := -1 ;
186 RETURN l_locator_control ;
187 --
188 WHEN OTHERS THEN
189 x_return_status := fnd_api.g_ret_sts_unexp_error ;
190 --
191 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
192 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
193 END IF;
194 --
195 l_locator_control := -1 ;
196 RETURN l_locator_control ;
197 --
198 END loc_control;
199 --
200 --
201 -- --------------------------------------------------------------------------
202 -- What does it do:
203 -- Fetches default putaway sub/locations. If the input sub/loc are not null,
204 -- they are retained as putaway sub/locations
205 -- --------------------------------------------------------------------------
206 --
207 PROCEDURE get_putaway_defaults
208 ( p_organization_id IN NUMBER,
209 p_inventory_item_id IN NUMBER,
210 p_to_subinventory_code IN VARCHAR2,
211 p_to_locator_id IN NUMBER,
212 p_to_cost_group_id IN NUMBER,
213 p_org_locator_control_code IN NUMBER,
214 p_item_locator_control_code IN NUMBER,
215 p_transaction_type_id IN NUMBER,
216 x_putaway_sub OUT NOCOPY VARCHAR2,
217 x_putaway_loc OUT NOCOPY NUMBER,
218 x_putaway_cost_group_id OUT NOCOPY NUMBER,
219 x_return_status OUT NOCOPY VARCHAR2,
220 x_msg_count OUT NOCOPY NUMBER,
221 x_msg_data OUT NOCOPY VARCHAR2
222 )
223 IS
224 -- constants
225 l_api_name CONSTANT VARCHAR(30) := 'get_putaway_defaults';
226 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
227 --
228 -- variable
229 l_sub_loc_control NUMBER;
230 l_loc_control NUMBER;
231 l_putaway_sub VARCHAR2(30);
232 l_putaway_loc NUMBER;
233 l_putaway_cg NUMBER := NULL;
234 l_putaway_cg_org NUMBER;
235 l_inventory_item_id NUMBER;
236 l_organization_id NUMBER;
237 l_sub_status NUMBER;
238 l_loc_status NUMBER;
239 l_allowed VARCHAR2(1);
240 l_primary_cost_method NUMBER;
241 l_sub_found BOOLEAN;
242 --
243 CURSOR l_subinventory_code_csr IS
244 SELECT subinventory_code
245 FROM mtl_item_sub_defaults
246 WHERE inventory_item_id = l_inventory_item_id
247 AND organization_id = l_organization_id
248 AND default_type = 3; -- default transfer order sub
249 --
250 /* CURSOR l_sub_status_csr IS
251 SELECT status_id
252 FROM mtl_secondary_inventories
253 WHERE secondary_inventory_name = l_putaway_sub
254 AND organization_id = l_organization_id ;
255 --
256 CURSOR l_locator_type_csr IS
257 SELECT locator_type
258 FROM mtl_secondary_inventories
259 WHERE secondary_inventory_name = l_putaway_sub
260 AND organization_id = l_organization_id ;
261 */ --
262 CURSOR l_locator_status_csr IS
263 SELECT status_id
264 FROM mtl_item_locations
265 WHERE inventory_location_id = l_putaway_loc
266 AND organization_id = l_organization_id ;
267 --
268 CURSOR l_locator_csr IS
269 SELECT locator_id
270 FROM mtl_item_loc_defaults mtld,
271 mtl_item_locations mil
272 WHERE mtld.locator_id = mil.inventory_location_id
273 AND mtld.organization_id = mil.organization_id
274 AND mtld.inventory_item_id = l_inventory_item_id
275 AND mtld.organization_id = l_organization_id
276 AND mtld.subinventory_code = l_putaway_sub
277 AND mtld.default_type = 3
278 AND nvl(mil.disable_date,sysdate + 1) > sysdate;
279
280 /* CURSOR l_cost_method IS
281 SELECT primary_cost_method
282 ,default_cost_group_id
283 FROM mtl_parameters mp
284 WHERE mp.organization_id = p_organization_id;
285
286 CURSOR l_cost_group_csr IS
287 SELECT default_cost_group_id
288 FROM mtl_secondary_inventories msi
289 WHERE msi.secondary_inventory_name = l_putaway_sub
290 AND msi.organization_id = p_organization_id;
291 */
292
293 BEGIN
294 l_organization_id := p_organization_id;
295 l_inventory_item_id := p_inventory_item_id;
296 -- search for default sub if to_sub in input row is null
297 IF p_to_subinventory_code IS NULL THEN
298 OPEN l_subinventory_code_csr;
299 FETCH l_subinventory_code_csr INTO l_putaway_sub;
300 IF l_subinventory_code_csr%notfound OR
301 l_putaway_sub IS NULL THEN
302 CLOSE l_subinventory_code_csr;
303 fnd_message.set_name('INV','INV_NO_DEFAULT_SUB');
304 fnd_msg_pub.ADD;
305 RAISE fnd_api.g_exc_error;
306 END IF;
307 CLOSE l_subinventory_code_csr;
308
309 ELSE
310 l_putaway_sub := p_to_subinventory_code ;
311 END IF;
312
313 l_sub_found := INV_CACHE.set_tosub_rec(l_organization_id, l_putaway_sub);
314 IF inv_install.adv_inv_installed(NULL) THEN
315
316 IF l_sub_found THEN
317 l_sub_status := INV_CACHE.tosub_rec.status_id;
318 ELSE
319 l_sub_status := NULL;
320 END IF;
321
322 --Bug Number :3457530(cheking for a transaction_type_id also)
323
324 IF l_sub_status IS NOT NULL AND p_transaction_type_id <> 64 THEN
325 l_allowed := inv_material_status_grp.is_trx_allowed(
326 p_status_id => l_sub_status
327 ,p_transaction_type_id => p_transaction_type_id
328 ,x_return_status => l_return_status
329 ,x_msg_count => x_msg_count
330 ,x_msg_data => x_msg_data);
331 IF l_return_status = fnd_api.g_ret_sts_error THEN
332 RAISE fnd_api.g_exc_error ;
333 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
334 RAISE fnd_api.g_exc_unexpected_error;
335 END IF;
336
337 IF l_allowed <> 'Y' THEN
338 fnd_message.set_name('INV', 'INV_DETAIL_SUB_STATUS');
339 fnd_msg_pub.add;
340 raise fnd_api.g_exc_error;
341 END IF;
342 END IF;
343 END IF;
344 --
345 -- now get the locator control and then determine if
346 -- default locator needs to be selected from item defaults
347 --
348 IF NOT l_sub_found THEN
349 fnd_message.set_name('INV','INV_NO_SUB_LOC_CONTROL');
350 fnd_msg_pub.ADD;
351 RAISE fnd_api.g_exc_error;
352 END if;
353
354 l_sub_loc_control := INV_CACHE.tosub_rec.locator_type;
355
356 -- find out the real locator control
357 l_loc_control := loc_control
358 ( p_org_locator_control_code
359 ,l_sub_loc_control
360 ,p_item_locator_control_code
361 ,l_return_status
362 ,x_msg_count
363 ,x_msg_data);
364 IF l_return_status = fnd_api.g_ret_sts_error THEN
365 RAISE fnd_api.g_exc_error ;
366 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
367 RAISE fnd_api.g_exc_unexpected_error;
368 END IF;
369 --
370 IF l_loc_control = 2 THEN -- has locator control
371 -- if no to_loc was supplied then get from defaults
372 IF p_to_locator_id IS NULL THEN
373 OPEN l_locator_csr;
374 FETCH l_locator_csr INTO l_putaway_loc;
375 IF l_locator_csr%notfound OR l_putaway_loc IS NULL THEN
376 CLOSE l_locator_csr;
377 fnd_message.set_name('INV','INV_NO_DEFAULT_LOC');
378 fnd_msg_pub.ADD;
379 RAISE fnd_api.g_exc_error;
380 END IF;
381 ELSE
382 l_putaway_loc := p_to_locator_id ;
383 END IF;
384
385 IF inv_install.adv_inv_installed(NULL) THEN
386
387 OPEN l_locator_status_csr;
388 FETCH l_locator_status_csr INTO l_loc_status;
389 IF l_locator_status_csr%NOTFOUND THEN
390 l_loc_status := NULL;
391 END IF;
392 CLOSE l_locator_status_csr;
393
394 --Bug Number :3457530(cheking for a transaction_type_id also for locator)
395
396 IF l_loc_status IS NOT NULL AND p_transaction_type_id <> 64 THEN
397 l_allowed := inv_material_status_grp.is_trx_allowed(
398 p_status_id => l_loc_status
399 ,p_transaction_type_id => p_transaction_type_id
400 ,x_return_status => l_return_status
401 ,x_msg_count => x_msg_count
402 ,x_msg_data => x_msg_data);
403
404 IF l_return_status = fnd_api.g_ret_sts_error THEN
405 RAISE fnd_api.g_exc_error ;
406 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
407 RAISE fnd_api.g_exc_unexpected_error;
408 END IF;
409
410 IF l_allowed <> 'Y' THEN
411 fnd_message.set_name('INV', 'INV_DETAIL_LOC_STATUS');
412 fnd_msg_pub.add;
413 raise fnd_api.g_exc_error;
414 END IF;
415 END IF;
416
417 END IF;
418 END IF;
419
420 -- Now get the cost group. If the to_cost_group is specified
421 -- on the move order, then use that. If not, query the default
422 -- cost group for the subinventory if in a standard costing org.
423 -- If not defined there, or if avg. costing org
424 -- try to get the default cost group from the organization
425 IF p_to_cost_group_id IS NULL THEN
426 IF INV_CACHE.set_org_rec(l_organization_id) THEN
427 l_primary_cost_method := INV_CACHE.org_rec.primary_cost_method;
428 l_putaway_cg_org := INV_CACHE.org_rec.default_cost_group_id;
429 ELSE
430 l_primary_cost_method := 2;
431 l_putaway_cg_org := NULL;
432 End If;
433
434 If l_primary_cost_method = 1 Then
435 IF l_sub_found THEN
436 l_putaway_cg := INV_CACHE.tosub_rec.default_cost_group_id;
437 ELSE
438 l_putaway_cg := NULL;
439 end if;
440 End If;
441
442 If l_putaway_cg IS NULL Then
443 l_putaway_cg := l_putaway_cg_org;
444 if l_putaway_cg IS NULL then
445 fnd_message.set_name('INV','INV_NO_DEFAULT_COST_GROUP');
446 fnd_msg_pub.ADD;
447 RAISE fnd_api.g_exc_error;
448 end if;
449 End If;
450 ELSE
451 l_putaway_cg := p_to_cost_group_id;
452 END IF;
453
454
455
456 x_putaway_sub := l_putaway_sub;
457 x_putaway_loc := l_putaway_loc;
458 x_putaway_cost_group_id := l_putaway_cg;
459 x_return_status := l_return_status;
460 --
461 EXCEPTION
462 WHEN fnd_api.g_exc_error THEN
463 x_return_status := fnd_api.g_ret_sts_error ;
464 x_putaway_loc := NULL;
465 x_putaway_sub := NULL;
466 x_putaway_cost_group_id := NULL;
467 --
468 WHEN fnd_api.g_exc_unexpected_error THEN
469 x_return_status := fnd_api.g_ret_sts_unexp_error ;
470 x_putaway_loc := NULL;
471 x_putaway_sub := NULL;
472 x_putaway_cost_group_id := NULL;
473 --
474 WHEN OTHERS THEN
475 x_return_status := fnd_api.g_ret_sts_unexp_error ;
476 x_putaway_loc := NULL;
477 x_putaway_sub := NULL;
478 x_putaway_cost_group_id := NULL;
479 --
480 IF (fnd_msg_pub.check_msg_level
481 (fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
482 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
483 END IF;
484 --
485 END get_putaway_defaults ;
486 --
487 -- --------------------------------------------------------------------------
488 -- What does it do:
489 -- Builds string to filter passed project/task.
490 -- --------------------------------------------------------------------------
491 --
492 PROCEDURE project_where
493 ( p_project_id IN NUMBER
494 ,p_task_id IN NUMBER
495 ,x_where_clause OUT NOCOPY long
496 ,x_return_status OUT NOCOPY VARCHAR2
497 ,x_msg_count OUT NOCOPY NUMBER
498 ,x_msg_data OUT NOCOPY VARCHAR2
499 ,p_end_assembly_pegging_code IN NUMBER
500 ) IS
501 --
502 -- constants
503 l_api_name CONSTANT VARCHAR(30) := 'project_where';
504 --
505 l_identifier VARCHAR2(80);
506 l_id2 VARCHAR2(80);
507 BEGIN
508 x_return_status := fnd_api.g_ret_sts_success ;
509 --
510 --soft pegged always allocates common; hard pegged items allocate
511 -- project/task
512 IF p_project_id IS NULL AND p_task_id IS NULL
513 THEN
514 --
515 -- no project or task referenced, pick from common inventory only
516 --
517 x_where_clause := ' AND '||
518 ' ((base.locator_id IS NULL) OR ' || g_line_feed ||
519 ' (base.locator_id IS NOT NULL AND (EXISTS ( ' || g_line_feed ||
520 ' SELECT inventory_location_id ' || g_line_feed ||
521 ' FROM mtl_item_locations ' || g_line_feed ||
522 ' WHERE inventory_location_id = base.locator_id'|| g_line_feed ||
523 ' AND organization_id = base.organization_id' || g_line_feed ||
524 ' AND project_id IS NULL ' || g_line_feed ||
525 ' AND task_id IS NULL)))) ';
526 --
527 ELSIF p_end_assembly_pegging_code = 1 THEN
528 --
529 -- soft pegged item can also pick from common inventory
530 --
531 IF p_task_id IS NULL THEN
532 l_identifier := inv_sql_binding_pvt.initbindvar(p_project_id);
533 x_where_clause :=' AND '||
534 ' ((base.locator_id IS NOT NULL) AND (EXISTS ( ' || g_line_feed ||
535 ' SELECT inventory_location_id ' || g_line_feed ||
536 ' FROM mtl_item_locations ' || g_line_feed ||
537 ' WHERE inventory_location_id = base.locator_id ' || g_line_feed ||
538 ' AND organization_id = base.organization_id ' || g_line_feed ||
539 ' AND nvl(project_id,' || l_identifier || ') = ' || g_line_feed ||
540 l_identifier || g_line_feed ||
541 ' AND task_id IS NULL))) ';
542 ELSE
543 -- referencing project and task, pick only from those locators or common inventory
544 --
545 l_identifier := inv_sql_binding_pvt.initbindvar(p_project_id);
546 l_id2 := inv_sql_binding_pvt.initbindvar(p_task_id);
547 x_where_clause :=' AND '||
548 ' ((base.locator_id IS NOT NULL) AND (EXISTS ( '|| g_line_feed ||
549 ' SELECT inventory_location_id ' || g_line_feed ||
550 ' FROM mtl_item_locations ' || g_line_feed ||
551 ' WHERE inventory_location_id = base.locator_id ' || g_line_feed ||
552 ' AND organization_id = base.organization_id ' || g_line_feed ||
553 ' AND ((project_id = ' || g_line_feed ||
554 l_identifier || g_line_feed ||
555 ' AND task_id = ' || g_line_feed ||
556 l_id2 || g_line_feed ||
557 ') OR (project_id IS NULL ' || g_line_feed ||
558 ' AND task_id IS NUL))' || g_line_feed ||
559 '))) ';
560 END IF;
561
562 ELSIF p_task_id IS NULL THEN
563 --
564 -- no task referenced, pick from inventory corresponding to this
565 -- project only
566 --
567 l_identifier := inv_sql_binding_pvt.initbindvar(p_project_id);
568 x_where_clause :=' AND '||
569 ' ((base.locator_id IS NOT NULL) AND (EXISTS ( ' || g_line_feed ||
570 ' SELECT inventory_location_id ' || g_line_feed ||
571 ' FROM mtl_item_locations ' || g_line_feed ||
572 ' WHERE inventory_location_id = base.locator_id ' || g_line_feed ||
573 ' AND organization_id = base.organization_id ' || g_line_feed ||
574 ' AND project_id = ' || g_line_feed ||
575 l_identifier || g_line_feed ||
576 ' AND task_id IS NULL))) ';
577 --
578 ELSE
579 -- referencing project and task, pick only from those locators
580 --
581 l_identifier := inv_sql_binding_pvt.initbindvar(p_project_id);
582 l_id2 := inv_sql_binding_pvt.initbindvar(p_task_id);
583 x_where_clause :=' AND '||
584 ' ((base.locator_id IS NOT NULL) AND (EXISTS ( '|| g_line_feed ||
585 ' SELECT inventory_location_id ' || g_line_feed ||
586 ' FROM mtl_item_locations ' || g_line_feed ||
587 ' WHERE inventory_location_id = base.locator_id ' || g_line_feed ||
588 ' AND organization_id = base.organization_id ' || g_line_feed ||
589 ' AND project_id = ' || g_line_feed ||
590 l_identifier || g_line_feed ||
591 ' AND task_id = ' || g_line_feed ||
592 l_id2 || g_line_feed ||
593 '))) ';
594
595 END IF;
596 --
597 EXCEPTION
598 WHEN fnd_api.g_exc_error THEN
599 x_return_status := fnd_api.g_ret_sts_error ;
600 x_where_clause := NULL;
601 --
602 WHEN fnd_api.g_exc_unexpected_error THEN
603 x_return_status := fnd_api.g_ret_sts_unexp_error ;
604 x_where_clause := NULL;
605 --
606 WHEN OTHERS THEN
607 x_return_status := fnd_api.g_ret_sts_unexp_error ;
608 x_where_clause := NULL;
609 --
610 IF (fnd_msg_pub.check_msg_level
611 (fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
612 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
613 END IF;
614 --
615 END project_where;
616 --
617 --
618 -- --------------------------------------------------------------------------
619 -- What does it do:
620 -- Intelligently builds SQL based on picking rule.
621 -- --------------------------------------------------------------------------
622 --
623 PROCEDURE get_sql_for_rule
624 (
625 p_rule_id IN NUMBER,
626 p_pp_temp_rec IN g_pp_temp_rec_type,
627 p_request_context IN inv_detail_util_pvt.g_request_context_rec_type,
628 p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type,
629 x_return_status OUT NOCOPY VARCHAR2,
630 x_msg_count OUT NOCOPY NUMBER,
631 x_msg_data OUT NOCOPY VARCHAR2
632 )
633 IS
634 --
635 -- constants
636 l_api_name CONSTANT VARCHAR(30) := 'get_sql_for_rule';
637 --
638 -- Variables
639 l_rev_rule NUMBER ;
640 l_lot_rule NUMBER ;
641 l_sub_rule NUMBER ;
642 l_loc_rule NUMBER ;
643 l_is_lot_control BOOLEAN;
644 --
645 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
646 --
647 l_sub_select VARCHAR2(3000);
648 l_loc_select VARCHAR2(3000);
649 l_rev_where VARCHAR2(3000);
650 l_lot_where VARCHAR2(3000);
651 l_sub_where VARCHAR2(3000);
652 l_loc_where VARCHAR2(3000);
653 l_cg_where VARCHAR2(3000);
654 l_stat_where VARCHAR2(3000);
655 l_rev_group VARCHAR2(3000);
656 l_lot_group VARCHAR2(3000);
657 l_sub_group VARCHAR2(3000);
658 l_loc_group VARCHAR2(3000);
659 l_project_where long;
660 l_from VARCHAR2(3000);
661 l_where long;
662 l_tmp1 VARCHAR2(30);
663 l_tmp2 VARCHAR2(30);
664 l_tmp3 VARCHAR2(30);
665 l_identifier VARCHAR2(80);
666 l_group_by VARCHAR2(1000);
667 l_order_by VARCHAR2(1000);
668 l_pos NUMBER;
669 --bug3094709
670 l_pjm_org NUMBER;
671 --
672 CURSOR l_rule_csr IS
673 SELECT
674 revision_rule
675 ,lot_rule
676 ,subinventory_rule
677 ,locator_rule
678 FROM mtl_picking_rules
679 WHERE picking_rule_id = p_rule_id ;
680 --
681 l_temp1 long;
682 l_temp2 long;
683 BEGIN
684 x_return_status := fnd_api.g_ret_sts_success ;
685 l_rev_where := ' ';
686 l_lot_where := ' ';
687 l_sub_where := ' ';
688 l_loc_where := ' ';
689 l_cg_where := ' ';
690 l_stat_where:= ' ';
691 l_from := ' ';
692 inv_sql_binding_pvt.initbindtables;
693 --
694 IF p_rule_id IS NOT NULL THEN
695 OPEN l_rule_csr;
696 FETCH l_rule_csr INTO l_rev_rule, l_lot_rule, l_sub_rule, l_loc_rule;
697 IF l_rule_csr%notfound THEN
698 CLOSE l_rule_csr;
699 fnd_message.set_name('INV','INV_INVALID_PICKING_RULE');
700 fnd_msg_pub.ADD;
701 RAISE fnd_api.g_exc_error;
702 END IF;
703 CLOSE l_rule_csr;
704 --
705 IF l_rev_rule IS NULL OR l_lot_rule IS NULL OR
706 l_sub_rule IS NULL OR l_loc_rule IS NULL THEN
707 fnd_message.set_name('INV','INV_RULE_DEFINITION_ERROR');
708 fnd_msg_pub.ADD;
709 RAISE fnd_api.g_exc_error;
710 END IF;
711 ELSE
712 l_rev_rule := 0;
713 l_lot_rule := 0;
714 l_sub_rule := 0;
715 l_loc_rule := 0;
716 END IF;
717 --
718 -- Fix for bug #1063622
719 -- Items which were under lot control were not being processed correctly,
720 -- because this code was formerly comparing the item_revision_control to 2
721 /* l_is_lot_control := (p_request_context.item_lot_control_code = 2);
722 --
723 inv_quantity_tree_pvt.build_sql
724 (
725 x_return_status => l_return_status
726 ,p_mode => inv_quantity_tree_pvt.g_transaction_mode
727 ,p_is_lot_control => l_is_lot_control
728 ,p_asset_sub_only => FALSE
729 ,p_include_suggestion => TRUE
730 ,p_lot_expiration_date => NULL
731 ,x_sql_statement => g_stmt
732 );
733 */
734
735 inv_detail_util_pvt.build_sql
736 (
737 x_return_status => l_return_status
738 ,x_sql_statement => g_stmt);
739 --
740 IF l_return_status = fnd_api.g_ret_sts_error THEN
741 RAISE fnd_api.g_exc_error ;
742 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
743 RAISE fnd_api.g_exc_unexpected_error;
744 END IF;
745 --
746 -- replace some hardcoded bind variables
747 -- Bug 1384720 - performance improvements
748 -- the demand source info no longer in qty tree
749 l_identifier := inv_sql_binding_pvt.initbindvar
750 (p_request_line_rec.organization_id);
751 g_stmt := REPLACE(g_stmt,':organization_id', l_identifier);
752 --
753 l_identifier := inv_sql_binding_pvt.initbindvar
754 (p_request_line_rec.inventory_item_id);
755 g_stmt := REPLACE(g_stmt,':inventory_item_id', l_identifier);
756 --
757 --demand source line id is in sql only if pjm is enabled
758 -- (see inv_quantity_tree_pvt.build_cursor)
759 /*IF g_unit_eff_enabled IS NULL THEN
760 g_unit_eff_enabled := pjm_unit_eff.enabled;
761 END IF;
762 IF g_unit_eff_enabled = 'Y' THEN
763 l_identifier := Inv_sql_binding_pvt.initbindvar
764 (p_request_context.txn_line_id);
765 g_stmt := REPLACE(g_stmt,':demand_source_line_id', l_identifier);
766 END IF;
767 --
768 */
769 /*
770 l_identifier := inv_sql_binding_pvt.initbindvar
771 (p_request_context.transaction_source_type_id);
772 g_stmt := REPLACE(g_stmt,':demand_source_type_id', l_identifier);
773 --
774 l_identifier := inv_sql_binding_pvt.initbindvar
775 (p_request_context.txn_header_id);
776 g_stmt := REPLACE(g_stmt,':demand_source_header_id',l_identifier);
777 --
778 l_identifier := Inv_sql_binding_pvt.initbindvar
779 (p_request_context.txn_line_detail);
780 g_stmt := REPLACE(g_stmt,':demand_source_delivery', l_identifier);
781 --
782 g_stmt := REPLACE(g_stmt,':demand_source_name', 'NULL');
783 --
784 */
785 IF p_request_context.item_revision_control = 2 THEN
786 -- if revision is passed, include it
787 IF p_pp_temp_rec.revision IS NOT NULL THEN
788 l_identifier := inv_sql_binding_pvt.initbindvar
789 (p_pp_temp_rec.revision);
790 l_rev_where := g_line_feed
791 || ' AND base.revision = '||l_identifier;
792 END IF;
793 END IF;
794 --
795 IF p_request_context.item_lot_control_code = 2 THEN
796 IF p_pp_temp_rec.lot_number IS NOT NULL THEN
797 l_identifier := inv_sql_binding_pvt.initbindvar
798 (p_pp_temp_rec.lot_number);
799 l_lot_where := l_lot_where
800 || g_line_feed || ' AND base.lot_number = '||l_identifier;
801 END IF;
802 IF p_pp_temp_rec.lot_expiration_date IS NOT NULL THEN
803 l_identifier :=
804 inv_sql_binding_pvt.initbindvar
805 (p_pp_temp_rec.lot_expiration_date);
806 l_lot_where := l_lot_where || g_line_feed
807 || ' AND (base.lot_expiration_date >= '
808 ||l_identifier
809 ||' OR base.lot_expiration_date IS NULL ';
810 END IF;
811 END IF;
812 --
813 IF p_pp_temp_rec.from_subinventory_code IS NOT NULL THEN
814 l_identifier :=
815 inv_sql_binding_pvt.initbindvar
816 (p_pp_temp_rec.from_subinventory_code);
817 l_sub_where := g_line_feed || ' AND base.subinventory_code = '
818 ||l_identifier;
819 ELSE
820 --if sub is null and item is reservable,
821 -- we need to make sure that we don't detail from
822 -- a nonreservable sub
823 if p_request_context.item_reservable_type = 1 then
824 l_sub_where:= l_sub_where || g_line_feed ||
825 ' AND NVL(base.reservable_type,2) = 1';
826 end if;
827 END IF;
828 IF p_pp_temp_rec.from_locator_id IS NOT NULL THEN
829 l_identifier := inv_sql_binding_pvt.initbindvar
830 (p_pp_temp_rec.from_locator_id);
831 l_loc_where := g_line_feed ||' AND base.locator_id = '||l_identifier;
832 END IF;
833 --
834 IF p_pp_temp_rec.from_cost_group_id IS NOT NULL THEN
835 l_identifier := inv_sql_binding_pvt.initbindvar
836 (p_pp_temp_rec.from_cost_group_id);
837 l_cg_where := g_line_feed ||' AND base.cost_group_id = '||l_identifier;
838 END IF;
839 --
840 /* Bug# 3094709: If the move order is of type Issue to Project and the org.
841 * is a non-pjm org then pass NULL as project_id and task_id
842 */
843 select project_reference_enabled
844 into l_pjm_org
845 from mtl_parameters
846 where organization_id = p_request_line_rec.organization_id;
847
848 if(l_pjm_org = 2) then
849
850 project_where
851 (
852 NULL
853 ,NULL
854 ,l_project_where
855 ,l_return_status
856 ,x_msg_count
857 ,x_msg_data
858 ,p_request_context.end_assembly_pegging_code
859 );
860
861 else
862 project_where
863 (
864 p_request_line_rec.project_id
865 ,p_request_line_rec.task_id
866 ,l_project_where
867 ,l_return_status
868 ,x_msg_count
869 ,x_msg_data
870 ,p_request_context.end_assembly_pegging_code
871 );
872 end if;
873 --
874 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
875 RAISE fnd_api.g_exc_unexpected_error ;
876 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
877 RAISE fnd_api.g_exc_error ;
878 END IF;
879 --
880
881 --check status of lot, locator, subinventory if Advanced Inventory
882 -- Removed for performance reasons. This check will be done in detail_xfer_or_pick
883 -- on each line returned
884 /* IF inv_install.adv_inv_installed(NULL) THEN
885 l_stat_where:= l_stat_where
886 || 'AND inv_detail_util_pvt.is_sub_loc_lot_trx_allowed('
887 || p_request_line_rec.transaction_type_id
888 || ', base.organization_id, base.inventory_item_id, base.subinventory_code'
889 || ', base.locator_id, base.lot_number) = ''Y''';
890 END IF;
891 */
892
893 -- --------------------------------------------------------
894 -- Now make use of the picking rules and sort appropriately
895 -- --------------------------------------------------------
896 -- Begin: Building SQL based on picking rule
897 -- --------------------------------------------------------
898 -- First, picking rule pertaining to revision
899 -- --------------------------------------------------------
900 l_group_by := ' ';
901 l_order_by := ' ';
902 IF p_request_context.item_revision_control = 2 THEN -- Revision Controlled
903 IF l_rev_rule = 2 THEN -- Revision rule
904 l_order_by := l_order_by || ',base.revision DESC';
905 END IF;
906 --
907 IF l_rev_rule = 1 THEN -- Revision or Effective date
908 --
909 l_from := l_from || ', mtl_item_revisions mir';
910 l_rev_where := l_rev_where
911 || g_line_feed ||' AND base.inventory_item_id = mir.inventory_item_id '
912 || g_line_feed ||' AND base.organization_id = mir.organization_id '
913 || g_line_feed ||' AND base.revision = mir.revision ';
914 -- l_group_by := l_group_by || ',mir.effectivity_date';
915 l_order_by := l_order_by || ',mir.effectivity_date';
916 END IF;
917 END IF;
918 -- ---------------------------------------
919 -- Second, picking rule pertaining to lot
920 -- ---------------------------------------
921 IF p_request_context.item_lot_control_code = 2 THEN -- Lot Controlled
922 IF l_lot_rule = 1 THEN -- Expiration date
923 l_order_by := l_order_by || ',base.lot_expiration_date asc'; -- lot_expiration_date
924 END IF;
925 IF l_lot_rule = 2 THEN -- Receipt Date
926 l_order_by := l_order_by || ',base.date_received'; -- date_received
927 END IF;
928 IF l_lot_rule = 3 THEN -- Lot Number
929 l_order_by := l_order_by || ',base.lot_number '; -- lot_number
930 END IF;
931 END IF;
932 -- ----------------------------------------------
933 -- Third, picking rule pertaining to subinventory
934 -- ----------------------------------------------
935 IF l_sub_rule = 2 THEN -- Subinventory Picking Order
936 l_from := l_from || ',mtl_secondary_inventories msi';
937 l_sub_where := l_sub_where
938 || g_line_feed
939 || ' AND base.subinventory_code = msi.secondary_inventory_name '
940 || g_line_feed
941 || ' AND base.organization_id = msi.organization_id';
942 --l_group_by := l_group_by || ',msi.picking_order ';
943 l_sub_select := ' ,msi.picking_order';
944 l_order_by := l_order_by || ',msi.picking_order '; -- msi.picking_order
945 END IF;
946 IF l_sub_rule = 3 THEN -- Earliest receipt date
947 l_pos := Instr(l_order_by,'base.date_received');
948 IF l_pos = 0 THEN
949 l_order_by := l_order_by || ',base.date_received'; -- date_received
950 END IF;
951 END IF;
952 -- -------------------------------------------
953 -- Fourth, picking rule pertaining to locator
954 -- -------------------------------------------
955 IF l_loc_rule = 2 THEN -- Locator Picking Order
956 l_from := l_from || ',mtl_item_locations mil';
957 l_loc_where := l_loc_where
958 || g_line_feed || ' AND base.locator_id = mil.inventory_location_id(+) '
959 || g_line_feed || ' AND base.organization_id = mil.organization_id (+) ';
960 l_loc_select := ' , mil.picking_order';
961 -- l_group_by := l_group_by || ',mil.picking_order ';
962 l_order_by := l_order_by || ',mil.picking_order ';
963 END IF;
964 IF l_loc_rule = 3 THEN -- Locator Pick Order/Earliest rcpt.date
965 l_pos := Instr(l_order_by,'base.date_received');
966 IF l_pos = 0 THEN
967 l_order_by := l_order_by || ',base.date_received'; -- date_received
968 END IF;
969 END IF;
970 --
971 -- Finalize
972 --
973 IF l_rev_where = ' ' AND
974 l_lot_where = ' ' AND
975 l_sub_where = ' ' AND
976 l_loc_where = ' ' AND
977 l_cg_where = ' ' AND
978 l_stat_where = ' ' AND
979 l_project_where = ' ' THEN
980 l_tmp1 := ' ';
981 ELSE
982 l_tmp1 := ' where 1=1 ';
983 END IF;
984 IF l_order_by = ' ' THEN
985 l_tmp3 := ' ';
986 ELSE
987 l_tmp3 := ' order by ';
988 l_order_by := Substr(l_order_by,3); -- skip the first space and the , symbol
989 END IF;
990 --
991 --jaysingh bug #2735447
992 l_pos := Instr(l_order_by,'base.date_received');
993 IF l_pos = 0 THEN
994 IF l_order_by = ' ' THEN
995 l_order_by := l_order_by || ' order by base.date_received'; -- date_received
996 ELSE
997 l_order_by := l_order_by || ' ,base.date_received'; -- date_received
998 END IF;
999 END IF;
1000 --jaysingh
1001
1002 l_temp1 :=
1003 ' SELECT base.revision
1004 ,base.lot_number
1005 ,base.lot_expiration_date
1006 ,base.subinventory_code
1007 ,base.locator_id
1008 ,base.cost_group_id '
1009 || g_line_feed || ', base.date_received '
1010 || g_line_feed || ', base.primary_quantity'
1011 || g_line_feed || l_sub_select || l_loc_select
1012 || g_line_feed || ' FROM ('||g_stmt||') base '
1013 || g_line_feed || l_from ;
1014 --
1015 l_temp2 := l_tmp1
1016 || l_rev_where || l_lot_where || l_sub_where || l_loc_where || l_cg_where
1017 || g_line_feed || l_stat_where || l_project_where
1018 /* || g_line_feed || ' HAVING SUM(Decode(x.quantity_type,2,-1,1)*x.primary_quantity) > 0 '
1019 || g_line_feed || ' GROUP BY x.revision '
1020 || g_line_feed || ' ,x.lot_number '
1021 || g_line_feed || ' ,x.lot_expiration_date '
1022 || g_line_feed || ' ,x.subinventory_code '
1023 || g_line_feed || ' ,x.locator_id '
1024 || g_line_feed || ' ,x.cost_group_id '
1025 || g_line_feed || ' ,x.date_received '
1026 || l_group_by */
1027 || l_tmp3 || l_order_by;
1028 --
1029 g_stmt := l_temp1 || l_temp2;
1030 -- inv_sql_binding_pvt.showsql('>>'||l_temp1);
1031 -- inv_sql_binding_pvt.showsql('>>'||l_temp2);
1032 -- inv_sql_binding_pvt.showsql(g_stmt);
1033 --inv_pp_debug.send_long_to_pipe(g_stmt);
1034 --
1035 x_return_status := l_return_status;
1036 EXCEPTION
1037 WHEN fnd_api.g_exc_error THEN
1038 x_return_status := fnd_api.g_ret_sts_error ;
1039 --
1040 WHEN fnd_api.g_exc_unexpected_error THEN
1041 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1042 --
1043 WHEN OTHERS THEN
1044 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1045 --
1046 IF (fnd_msg_pub.check_msg_level
1047 (fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1048 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1049 END IF;
1050 --
1051 END get_sql_for_rule ;
1052 --
1053 --
1054 PROCEDURE init_qty_tree
1055 (x_return_status OUT NOCOPY VARCHAR2,
1056 p_request_context IN inv_detail_util_pvt.g_request_context_rec_type,
1057 p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type,
1058 x_tree_id OUT NOCOPY NUMBER,
1059 x_msg_count OUT NOCOPY NUMBER,
1060 x_msg_data OUT NOCOPY VARCHAR2
1061 )
1062 IS
1063 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1064 l_api_name VARCHAR2(30) := 'init_qty_tree';
1065 l_rev_flag BOOLEAN;
1066 l_lot_flag BOOLEAN;
1067 l_tree_id NUMBER;
1068 BEGIN
1069 -- convert revision/lot control indicators into boolean
1070 IF p_request_context.item_revision_control = 2 THEN
1071 l_rev_flag := TRUE;
1072 ELSE
1073 l_rev_flag := FALSE;
1074 END IF;
1075 --
1076 IF p_request_context.item_lot_control_code = 2 THEN
1077 l_lot_flag := TRUE;
1078 ELSE
1079 l_lot_flag := FALSE;
1080 END IF;
1081 --
1082 -- create tree
1083 -- Bug 1890424 - Pass sysdate to create_tree so
1084 -- expired lots don't appear as available
1085
1086 inv_quantity_tree_pvt.create_tree
1087 (
1088 p_api_version_number => 1.0
1089 ,p_init_msg_lst => fnd_api.g_false
1090 ,x_return_status => l_return_status
1091 ,x_msg_count => x_msg_count
1092 ,x_msg_data => x_msg_data
1093 ,p_organization_id => p_request_line_rec.organization_id
1094 ,p_inventory_item_id => p_request_line_rec.inventory_item_id
1095 ,p_tree_mode => inv_quantity_tree_pvt.g_transaction_mode
1096 ,p_is_revision_control => l_rev_flag
1097 ,p_is_lot_control => l_lot_flag
1098 ,p_is_serial_control => FALSE
1099 ,p_asset_sub_only => FALSE
1100 ,p_include_suggestion => FALSE
1101 ,p_demand_source_type_id => p_request_context.transaction_source_type_id
1102 ,p_demand_source_header_id => p_request_context.txn_header_id
1103 ,p_demand_source_line_id => p_request_context.txn_line_id
1104 ,p_demand_source_delivery => p_request_context.txn_line_detail
1105 ,p_demand_source_name => NULL
1106 ,p_lot_expiration_date => sysdate
1107 ,x_tree_id => l_tree_id
1108 ,p_exclusive => inv_quantity_tree_pvt.g_exclusive
1109 ,p_pick_release => inv_quantity_tree_pvt.g_pick_release_yes
1110 );
1111 --
1112 IF l_return_status = fnd_api.g_ret_sts_error THEN
1113 RAISE fnd_api.g_exc_error ;
1114 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1115 RAISE fnd_api.g_exc_unexpected_error;
1116 END IF;
1117 --
1118 x_tree_id := l_tree_id;
1119 x_return_status := l_return_status;
1120 END init_qty_tree;
1121 --
1122 PROCEDURE free_qty_tree (p_tree_id IN NUMBER,x_return_status OUT NOCOPY VARCHAR2)
1123 IS
1124 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1125 l_msg_count NUMBER;
1126 l_msg_data VARCHAR2(240);
1127 BEGIN
1128 -- If tree already exists, free it. This may occur if procedure is
1129 -- called on multiple occassions within the same session.
1130 IF 0 > 0 THEN
1131 inv_quantity_tree_pvt.free_tree
1132 (
1133 p_api_version_number => 1.0
1134 ,p_init_msg_lst => fnd_api.g_false
1135 ,x_return_status => l_return_status
1136 ,x_msg_count => l_msg_count
1137 ,x_msg_data => l_msg_data
1138 ,p_tree_id => 0
1139 );
1140 IF l_return_status = fnd_api.g_ret_sts_error THEN
1141 RAISE fnd_api.g_exc_error ;
1142 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1143 RAISE fnd_api.g_exc_unexpected_error;
1144 END IF;
1145 END IF;
1146 --
1147 x_return_status := l_return_status;
1148 END free_qty_tree;
1149 --
1150 -- --------------------------------------------------------------------------
1151 -- What does it do:
1152 -- Detail a transfer or picking only request record using given pick rule,
1153 -- and save outputs by calling inv_detail_util_pvt.add_output
1154 -- --------------------------------------------------------------------------
1155 --
1156 PROCEDURE detail_xfer_or_pick
1157 (p_pp_temp_rec IN g_pp_temp_rec_type,
1158 p_request_context IN inv_detail_util_pvt.g_request_context_rec_type,
1159 p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type,
1160 p_rule_id IN NUMBER,
1161 p_tree_id IN NUMBER,
1162 x_return_status OUT NOCOPY VARCHAR2,
1163 x_msg_count OUT NOCOPY NUMBER,
1164 x_msg_data OUT NOCOPY VARCHAR2
1165 )
1166 IS
1167 --
1168 -- constants
1169 l_api_name CONSTANT VARCHAR(30) := 'detail_xfer_or_pick';
1170 --
1171 -- Variables
1172 l_cursor INTEGER;
1173 --
1174 l_revision VARCHAR2(3);
1175 -- Increased lot size to 80 Char - Mercy Thomas - B4625329
1176 l_lot_number VARCHAR2(80);
1177 l_lot_expiration_date DATE;
1178 l_from_subinventory VARCHAR2(10);
1179 l_from_locator_id NUMBER;
1180 l_to_subinventory VARCHAR2(10);
1181 l_to_locator_id NUMBER ;
1182 l_from_cost_group_id NUMBER;
1183 l_to_cost_group_id NUMBER;
1184 --
1185 l_discard INTEGER;
1186 --
1187 l_index NUMBER;
1188 l_serial_index NUMBER;
1189 l_qty_to_detail NUMBER;
1190 l_required_sl_qty NUMBER;
1191 l_qty_to_update NUMBER;
1192 --
1193 l_tree_id INTEGER ;
1194 l_qoh NUMBER ;
1195 l_rqoh NUMBER;
1196 l_qr NUMBER;
1197 l_qs NUMBER;
1198 l_att NUMBER;
1199 l_atr NUMBER;
1200 --
1201 l_putaway_sub VARCHAR2(30);
1202 l_putaway_loc NUMBER ;
1203 l_putaway_cg NUMBER ;
1204 --
1205 use_this_row BOOLEAN;
1206 --
1207 l_rev_flag BOOLEAN;
1208 l_lot_flag BOOLEAN;
1209 --
1210 l_output_serial_rows INV_DETAIL_UTIL_PVT.g_serial_row_table;
1211 l_output_process_rec inv_detail_util_pvt.g_output_process_rec_type;
1212 --
1213 l_serial_number VARCHAR2(30);
1214
1215 l_from_sub_rec MTL_SECONDARY_INVENTORIES%ROWTYPE;
1216 l_to_sub_rec MTL_SECONDARY_INVENTORIES%ROWTYPE;
1217 l_org_rec MTL_PARAMETERS%ROWTYPE;
1218 l_item_rec MTL_SYSTEM_ITEMS%ROWTYPE;
1219 l_ret_value NUMBER;
1220 l_move_order_type NUMBER;
1221 --Bug Number 3449739
1222 l_indivisible_flag VARCHAR2(10) := 'N';
1223 l_att_om_indivisible NUMBER;
1224
1225 /* CURSOR l_from_sub_cursor IS
1226 SELECT *
1227 FROM MTL_SECONDARY_INVENTORIES
1228 WHERE secondary_inventory_name = l_from_subinventory
1229 AND organization_id = p_request_line_rec.organization_id;
1230
1231 CURSOR l_to_sub_cursor IS
1232 SELECT *
1233 FROM MTL_SECONDARY_INVENTORIES
1234 WHERE secondary_inventory_name = l_to_subinventory
1235 AND organization_id = p_request_line_rec.organization_id;
1236
1237 CURSOR l_org_cursor IS
1238 SELECT *
1239 FROM MTL_PARAMETERS
1240 WHERE organization_id = p_request_line_rec.organization_id;
1241
1242 CURSOR l_item_cursor IS
1243 SELECT *
1244 FROM MTL_SYSTEM_ITEMS
1245 WHERE inventory_item_id = p_request_line_rec.inventory_item_id
1246 AND organization_id = p_request_line_rec.organization_id;
1247
1248 CURSOR c_move_order_type IS
1249 SELECT move_order_type
1250 FROM mtl_txn_request_headers
1251 WHERE header_id = p_request_line_rec.header_id;
1252 */
1253
1254 BEGIN
1255 x_return_status := fnd_api.g_ret_sts_success ;
1256 --
1257 --inv_sql_binding_pvt.showsql(g_stmt);
1258 l_cursor := dbms_sql.open_cursor ;
1259 dbms_sql.parse(l_cursor, g_stmt, dbms_sql.v7);
1260 -- bind input variables
1261 inv_sql_binding_pvt.bindvars(l_cursor);
1262 -- now define the output variables
1263 dbms_sql.define_column(l_cursor, 1, l_revision, 3);
1264 dbms_sql.define_column(l_cursor, 2, l_lot_number, 30);
1265 dbms_sql.define_column(l_cursor, 3, l_lot_expiration_date);
1266 dbms_sql.define_column(l_cursor, 4, l_from_subinventory, 10);
1267 dbms_sql.define_column(l_cursor, 5, l_from_locator_id);
1268 dbms_sql.define_column(l_cursor, 6, l_from_cost_group_id);
1269 --
1270 l_discard := dbms_sql.execute(l_cursor);
1271 --
1272 -- set the qty that has to be detailed
1273 l_qty_to_detail := p_pp_temp_rec.primary_quantity ;
1274 --
1275 IF p_request_context.transfer_flag THEN
1276 get_putaway_defaults ( p_request_line_rec.organization_id,
1277 p_request_line_rec.inventory_item_id,
1278 p_pp_temp_rec.to_subinventory_code,
1279 p_pp_temp_rec.to_locator_id,
1280 p_pp_temp_rec.to_cost_group_id,
1281 p_request_context.org_locator_control_code,
1282 p_request_context.item_locator_control_code,
1283 p_request_line_rec.transaction_type_id,
1284 l_putaway_sub ,
1285 l_putaway_loc,
1286 l_putaway_cg,
1287 x_return_status,
1288 x_msg_count,
1289 x_msg_data
1290 );
1291 --
1292 IF x_return_status = fnd_api.g_ret_sts_error THEN
1293 RAISE fnd_api.g_exc_error ;
1294 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1295 RAISE fnd_api.g_exc_unexpected_error;
1296 END IF;
1297 END IF;
1298 --
1299 l_to_subinventory := l_putaway_sub;
1300 l_to_locator_id := l_putaway_loc;
1301 l_to_cost_group_id:= l_putaway_cg;
1302
1303 /* Use cached record instead of c_move_order_type cursor */
1304 IF inv_cache.set_mtrh_rec(p_request_line_rec.header_id) THEN
1305 l_move_order_type := NVL(inv_cache.mtrh_rec.move_order_type, 1);
1306 ELSE
1307 l_move_order_type := 1;
1308 END IF;
1309
1310 --
1311 --Bug 3449739
1312
1313 IF l_move_order_type = 3 THEN --Pick Wave move orders
1314
1315 IF ( inv_cache.set_item_rec(p_request_line_rec.organization_id, p_request_line_rec.inventory_item_id) ) THEN
1316 l_indivisible_flag := nvl(inv_cache.item_rec.indivisible_flag,'N');
1317 END IF;
1318 IF (l_indivisible_flag = 'Y') THEN
1319 l_qty_to_detail := floor(l_qty_to_detail);
1320 --inv_pick_wave_pick_confirm_pub.tracelog('l_qty_to_detail = '||l_qty_to_detail, 'INVRSV4B');
1321 END IF;
1322 END IF;
1323
1324 LOOP
1325 -- when no more rows to fetch, then exit
1326 --
1327 IF dbms_sql.fetch_rows(l_cursor) = 0 THEN
1328 EXIT ;
1329 END IF;
1330 --
1331 -- now retrieve the rows one at a time
1332 --
1333 dbms_sql.column_value(l_cursor, 1, l_revision);
1334 dbms_sql.column_value(l_cursor, 2, l_lot_number);
1335 dbms_sql.column_value(l_cursor, 3, l_lot_expiration_date);
1336 dbms_sql.column_value(l_cursor, 4, l_from_subinventory);
1337 dbms_sql.column_value(l_cursor, 5, l_from_locator_id);
1338 dbms_sql.column_value(l_cursor, 6, l_from_cost_group_id);
1339 --
1340 -- Some initializations
1341 --
1342 use_this_row := true;
1343 --
1344 -- Check to determine if this transaction is allowed for lot and locator
1345 IF inv_detail_util_pvt.is_sub_loc_lot_trx_allowed(p_request_line_rec.transaction_type_id, p_request_line_rec.organization_id, p_request_line_rec.inventory_item_id, l_from_subinventory, l_from_locator_id, l_lot_number) <> 'Y' THEN
1346 use_this_row := FALSE;
1347 END IF;
1348
1349 -- make sure that if this is a non-pick wave move order, that
1350 -- we don't pick from the destination sub
1351 IF l_move_order_type <> 3 AND
1352 p_request_context.transfer_flag AND
1353 l_from_subinventory = l_to_subinventory AND
1354 (l_from_locator_id = l_to_locator_id OR
1355 l_from_locator_id IS NULL AND
1356 l_to_locator_id IS NULL) AND
1357 use_this_row THEN
1358 use_this_row := FALSE;
1359 END IF;
1360
1361 -- Check to make sure transfer is allowed between from sub and to sub
1362 -- calls validation API's, which checks whether the from sub is
1363 --- asset or expense, and if expense to asset transfers are allowed.
1364 IF p_request_context.transfer_flag AND use_this_row THEN
1365
1366 -- First, get the line for from sub and to sub
1367 IF NOT (inv_cache.set_fromsub_rec(p_request_line_rec.organization_id, l_from_subinventory)) THEN
1368 fnd_message.set_name('INV', 'INV_VALIDATE_SUB_FAILED');
1369 fnd_msg_pub.add;
1370 RAISE fnd_api.g_exc_unexpected_error;
1371 END IF;
1372
1373 IF NOT (inv_cache.set_tosub_rec(p_request_line_rec.organization_id, l_to_subinventory)) THEN
1374 fnd_message.set_name('INV', 'INV_VALIDATE_SUB_FAILED');
1375 fnd_msg_pub.add;
1376 RAISE fnd_api.g_exc_unexpected_error;
1377 END IF;
1378
1379 IF NOT (inv_cache.set_org_rec(p_request_line_rec.organization_id)) THEN
1380 fnd_message.set_name('INV', 'INV_VALIDATE_SUB_FAILED');
1381 fnd_msg_pub.add;
1382 RAISE fnd_api.g_exc_unexpected_error;
1383 END IF;
1384 IF NOT (inv_cache.set_item_rec(p_request_line_rec.organization_id, p_request_line_rec.inventory_item_id)) THEN
1385 fnd_message.set_name('INV', 'INV_VALIDATE_SUB_FAILED');
1386 fnd_msg_pub.add;
1387 RAISE fnd_api.g_exc_unexpected_error;
1388 END IF;
1389
1390 -- then, call api
1391 l_ret_value := INV_VALIDATE.To_Subinventory(
1392 p_sub => inv_cache.tosub_rec
1393 ,p_org => inv_cache.org_rec
1394 ,p_item => inv_cache.item_rec
1395 ,p_from_sub => inv_cache.fromsub_rec
1396 ,p_acct_txn => 0);
1397 -- If return 0, don't use the from sub
1398 IF l_ret_value = 0 THEN
1399 use_this_row := FALSE;
1400 END IF;
1401
1402 END IF;
1403 --
1404 IF use_this_row THEN
1405 -- now call quantity tree to validate that there is enough quantity
1406 -- at this SKU
1407 --
1408 -- Query Tree
1409 -- Bug 1349981 - no longer passing in cost group when querying
1410 -- the quantity tree. It works out okay, since only one cg per
1411 -- subinventory
1412 inv_quantity_tree_pvt.query_tree
1413 (
1414 p_api_version_number => 1.0
1415 ,p_init_msg_lst => NULL
1416 ,x_return_status => x_return_status
1417 ,x_msg_count => x_msg_count
1418 ,x_msg_data => x_msg_data
1419 ,p_tree_id => p_tree_id
1420 ,p_revision => l_revision
1421 ,p_lot_number => l_lot_number
1422 ,p_subinventory_code => l_from_subinventory
1423 ,p_locator_id => l_from_locator_id
1424 ,x_qoh => l_qoh
1425 ,x_rqoh => l_rqoh
1426 ,x_qr => l_qr
1427 ,x_qs => l_qs
1428 ,x_att => l_att
1429 ,x_atr => l_atr
1430 ,p_transfer_subinventory_code => NULL
1431 ,p_cost_group_id => NULL
1432 );
1433 IF x_return_status = fnd_api.g_ret_sts_error THEN
1434 RAISE fnd_api.g_exc_error ;
1435 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1436 RAISE fnd_api.g_exc_unexpected_error;
1437 END IF;
1438 --
1439 --
1440 -- now compare the available qty against the needed qty and
1441 -- decrement the needed qty appropriately. then update the tree
1442 --
1443 IF l_att > 0 THEN
1444 IF l_att >= l_qty_to_detail THEN
1445 l_qty_to_update := l_qty_to_detail;
1446 ELSE
1447 --Bug Number 3449739
1448 IF (l_move_order_type = 3 and l_indivisible_flag = 'Y') THEN
1449 l_att_om_indivisible := floor(l_att);
1450 l_qty_to_update := l_att_om_indivisible;
1451 --inv_pick_wave_pick_confirm_pub.tracelog('l_att_om_indivisible = '||l_att_om_indivisible, 'INVRSV4B');
1452 ELSE
1453 l_qty_to_update := l_att;
1454 END IF;
1455 END IF;
1456 l_required_sl_qty := l_qty_to_update;
1457
1458 --
1459 -- If serial controlled, fetch serial nos
1460 -- for the autodetailed row. They are loaded into g_output_serial_rows.
1461 -- Changed 7/12/00 so all validation for calling this function
1462 -- is in INVVDEUB.pls
1463 l_serial_index := NULL;
1464
1465 IF p_request_context.detail_serial THEN
1466 inv_detail_util_pvt.get_serial_numbers (
1467 p_inventory_item_id => p_request_line_rec.inventory_item_id
1468 , p_organization_id => p_request_line_rec.organization_id
1469 , p_revision => l_revision
1470 , p_lot_number => l_lot_number
1471 , p_subinventory_code => l_from_subinventory
1472 , p_locator_id => l_from_locator_id
1473 , p_required_sl_qty => l_required_sl_qty
1474 , p_from_range => p_request_line_rec.serial_number_start
1475 , p_to_range => p_request_line_rec.serial_number_end
1476 , p_unit_number => p_request_line_rec.unit_number
1477 , p_detail_any_serial => p_request_context.detail_any_serial
1478 , p_cost_group_id => l_from_cost_group_id
1479 , p_transaction_type_id => p_request_line_rec.transaction_type_id
1480 , x_available_sl_qty => l_qty_to_update
1481 , x_serial_index => l_serial_index
1482 , x_return_status => x_return_status
1483 , x_msg_count => x_msg_count
1484 , x_msg_data => x_msg_data
1485 , p_demand_source_type_id => p_request_line_rec.transaction_source_type_id
1486 , p_demand_source_header_id => p_request_line_rec.transaction_header_id
1487 , p_demand_source_line_id => p_request_line_rec.txn_source_line_id );
1488
1489 IF x_return_status = fnd_api.g_ret_sts_error THEN
1490 RAISE fnd_api.g_exc_error ;
1491 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1492 RAISE fnd_api.g_exc_unexpected_error;
1493 END IF;
1494
1495 END IF;
1496 IF l_qty_to_update > 0 THEN
1497 inv_quantity_tree_pvt.update_quantities
1498 (
1499 p_api_version_number => 1
1500 ,p_init_msg_lst => fnd_api.g_false
1501 ,x_return_status => x_return_status
1502 ,x_msg_count => x_msg_count
1503 ,x_msg_data => x_msg_data
1504 ,p_tree_id => p_tree_id
1505 ,p_revision => l_revision
1506 ,p_lot_number => l_lot_number
1507 ,p_subinventory_code => l_from_subinventory
1508 ,p_locator_id => l_from_locator_id
1509 ,p_primary_quantity => l_qty_to_update
1510 ,p_quantity_type => inv_quantity_tree_pvt.g_qs_txn
1511 ,x_qoh => l_qoh
1512 ,x_rqoh => l_rqoh
1513 ,x_qr => l_qr
1514 ,x_qs => l_qs
1515 ,x_att => l_att
1516 ,x_atr => l_atr
1517 ,p_cost_group_id => l_from_cost_group_id
1518 ) ;
1519 --
1520 IF x_return_status = fnd_api.g_ret_sts_error THEN
1521 RAISE fnd_api.g_exc_error ;
1522 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
1523 RAISE fnd_api.g_exc_unexpected_error;
1524 END IF;
1525 -- ok, lets add to the output process table for output
1526 -- generation later
1527 l_output_process_rec.revision := l_revision;
1528 l_output_process_rec.from_subinventory_code := l_from_subinventory;
1529 l_output_process_rec.from_locator_id := l_from_locator_id;
1530 l_output_process_rec.to_subinventory_code := l_to_subinventory;
1531 l_output_process_rec.to_locator_id := l_to_locator_id;
1532 l_output_process_rec.from_cost_group_id := l_from_cost_group_id;
1533 l_output_process_rec.to_cost_group_id := l_to_cost_group_id;
1534 l_output_process_rec.lot_number := l_lot_number;
1535 l_output_process_rec.lot_expiration_date := l_lot_expiration_date;
1536 l_output_process_rec.pick_rule_id := p_rule_id;
1537 l_output_process_rec.put_away_rule_id := NULL;
1538 l_output_process_rec.reservation_id := p_pp_temp_rec.reservation_id;
1539 IF l_serial_index IS NOT NULL THEN -- has serial numbers detailed
1540 l_output_process_rec.primary_quantity := 1;
1541 l_output_process_rec.transaction_quantity :=
1542 inv_convert.inv_um_convert
1543 (
1544 p_request_line_rec.inventory_item_id,
1545 NULL,
1546 1,
1547 p_request_context.primary_uom_code,
1548 p_request_context.transaction_uom_code,
1549 NULL,
1550 NULL);
1551 FOR l_loop_index IN 1..l_qty_to_update LOOP
1552 l_serial_number :=
1553 inv_detail_util_pvt.g_output_serial_rows
1554 (l_loop_index+l_serial_index-1).serial_number;
1555 l_output_process_rec.serial_number_start := l_serial_number;
1556 l_output_process_rec.serial_number_end := l_serial_number;
1557 -- add it to the output process table for processing later
1558 inv_detail_util_pvt.add_output(l_output_process_rec);
1559 END LOOP;
1560 ELSE
1561 l_output_process_rec.primary_quantity := l_qty_to_update;
1562 l_output_process_rec.transaction_quantity :=
1563 inv_convert.inv_um_convert
1564 (
1565 p_request_line_rec.inventory_item_id,
1566 NULL,
1567 l_qty_to_update,
1568 p_request_context.primary_uom_code,
1569 p_request_context.transaction_uom_code,
1570 NULL,
1571 NULL);
1572 l_output_process_rec.serial_number_start := NULL;
1573 l_output_process_rec.serial_number_end := NULL;
1574 inv_detail_util_pvt.add_output(l_output_process_rec);
1575 END IF;
1576 -- update the quantity remained to detail
1577 l_qty_to_detail := l_qty_to_detail - l_qty_to_update;
1578 --
1579 END IF; -- (l_qty_to_update > 0) --
1580 END IF; -- (l_att > 0 ) --
1581 END IF; -- (use_this_rowu) --
1582 EXIT WHEN l_qty_to_detail = 0;
1583 END LOOP;
1584 --
1585 -- close cursor now
1586 dbms_sql.close_cursor(l_cursor);
1587 --
1588 --
1589 EXCEPTION
1590 WHEN fnd_api.g_exc_error THEN
1591 x_return_status := fnd_api.g_ret_sts_error ;
1592 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1593 --
1594 WHEN fnd_api.g_exc_unexpected_error THEN
1595 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1596 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1597 --
1598 WHEN OTHERS THEN
1599 DBMS_SQL.CLOSE_CURSOR(l_cursor);
1600 --
1601 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1602 --
1603 IF (fnd_msg_pub.check_msg_level
1604 (fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1605 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1606 END IF;
1607 --
1608 END detail_xfer_or_pick;
1609 --
1610 PROCEDURE detail_putaway_only
1611 (p_request_line_rec inv_detail_util_pvt.g_request_line_rec_type,
1612 p_request_context IN inv_detail_util_pvt.g_request_context_rec_type,
1613 x_return_status OUT NOCOPY VARCHAR2,
1614 x_msg_count OUT NOCOPY NUMBER ,
1615 x_msg_data OUT NOCOPY VARCHAR2
1616 )
1617 IS
1618 l_api_name CONSTANT VARCHAR(30) := 'detail_subtransfer';
1619 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1620 l_putaway_sub VARCHAR2(30);
1621 l_putaway_loc NUMBER;
1622 l_putaway_cg NUMBER;
1623 l_output_process_rec inv_detail_util_pvt.g_output_process_rec_type;
1624 BEGIN
1625 get_putaway_defaults ( p_request_line_rec.organization_id,
1626 p_request_line_rec.inventory_item_id,
1627 p_request_line_rec.to_subinventory_code,
1628 p_request_line_rec.to_locator_id,
1629 p_request_line_rec.to_cost_group_id,
1630 p_request_context.org_locator_control_code,
1631 p_request_context.item_locator_control_code,
1632 p_request_line_rec.transaction_type_id,
1633 l_putaway_sub ,
1634 l_putaway_loc,
1635 l_putaway_cg,
1636 l_return_status,
1637 x_msg_count,
1638 x_msg_data
1639 );
1640 --
1641 IF l_return_status = fnd_api.g_ret_sts_error THEN
1642 RAISE fnd_api.g_exc_error ;
1643 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1644 RAISE fnd_api.g_exc_unexpected_error;
1645 END IF;
1646 --
1647 l_output_process_rec.revision := p_request_line_rec.revision;
1648 l_output_process_rec.to_subinventory_code := l_putaway_sub;
1649 l_output_process_rec.to_locator_id := l_putaway_loc;
1650 l_output_process_rec.to_cost_group_id := l_putaway_cg;
1651 l_output_process_rec.lot_number := p_request_line_rec.lot_number;
1652 l_output_process_rec.lot_expiration_date :=
1653 p_request_context.lot_expiration_date;
1654 l_output_process_rec.serial_number_start :=
1655 p_request_line_rec.serial_number_start;
1656 l_output_process_rec.serial_number_end :=
1657 p_request_line_rec.serial_number_end;
1658 l_output_process_rec.primary_quantity :=
1659 p_request_line_rec.primary_quantity;
1660 l_output_process_rec.transaction_quantity :=
1661 p_request_line_rec.quantity - p_request_line_rec.quantity_detailed;
1662 l_output_process_rec.pick_rule_id := NULL;
1663 l_output_process_rec.put_away_rule_id := NULL;
1664 l_output_process_rec.reservation_id := NULL;
1665 -- add it to the output process table for processing later
1666 inv_detail_util_pvt.add_output(l_output_process_rec);
1667 --
1668 x_return_status := l_return_status;
1669 EXCEPTION
1670 WHEN fnd_api.g_exc_error THEN
1671 x_return_status := fnd_api.g_ret_sts_error ;
1672 --
1673 WHEN fnd_api.g_exc_unexpected_error THEN
1674 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1675 --
1676 WHEN OTHERS THEN
1677 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1678 --
1679 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1680 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1681 END IF;
1682 --
1683 END detail_putaway_only;
1684
1685 --
1686 -- --------------------------------------------------------------------------
1687 -- What does it do:
1688 -- detailing picking and put away locations and generate output into
1689 -- transactions temporary tables (mtl_material_transactions_temp, etc.)
1690 -- --------------------------------------------------------------------------
1691 --
1692 PROCEDURE detail_request
1693 (p_request_context IN inv_detail_util_pvt.g_request_context_rec_type,
1694 p_request_line_rec IN inv_detail_util_pvt.g_request_line_rec_type,
1695 p_reservations IN inv_reservation_global.mtl_reservation_tbl_type,
1696 x_return_status OUT NOCOPY VARCHAR2,
1697 x_msg_count OUT NOCOPY NUMBER ,
1698 x_msg_data OUT NOCOPY VARCHAR2
1699 ) IS
1700 l_api_name CONSTANT VARCHAR(30) := 'detail_subtransfer';
1701 l_rule_id NUMBER;
1702 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success;
1703 l_pp_temp_tbl g_pp_temp_tbl_type;
1704 l_pp_temp_tbl_size NUMBER;
1705 l_detail_level_tbl inv_detail_util_pvt.g_detail_level_tbl_type;
1706 l_detail_level_tbl_size NUMBER;
1707 l_tree_id NUMBER;
1708 l_remaining_quantity NUMBER;
1709 BEGIN
1710 -- init the output process table
1711 inv_detail_util_pvt.init_output_process_tbl;
1712 IF p_request_context.transfer_flag OR
1713 p_request_context.type_code = 2 THEN -- pick only or transfer
1714 inv_detail_util_pvt.compute_pick_detail_level
1715 (l_return_status ,
1716 p_request_line_rec ,
1717 p_request_context ,
1718 p_reservations ,
1719 l_detail_level_tbl ,
1720 l_detail_level_tbl_size ,
1721 l_remaining_quantity
1722 );
1723 -- copy the revision, lot, etc., to the temporary table
1724 -- to be worked on later
1725 FOR l_index IN 1..l_detail_level_tbl_size LOOP
1726 l_pp_temp_tbl(l_index).revision :=
1727 l_detail_level_tbl(l_index).revision;
1728 l_pp_temp_tbl(l_index).lot_number :=
1729 l_detail_level_tbl(l_index).lot_number;
1730 l_pp_temp_tbl(l_index).from_subinventory_code :=
1731 l_detail_level_tbl(l_index).subinventory_code;
1732 l_pp_temp_tbl(l_index).from_locator_id :=
1733 l_detail_level_tbl(l_index).locator_id;
1734 l_pp_temp_tbl(l_index).to_subinventory_code :=
1735 p_request_line_rec.to_subinventory_code;
1736 l_pp_temp_tbl(l_index).to_locator_id :=
1737 p_request_line_rec.to_locator_id;
1738 l_pp_temp_tbl(l_index).primary_quantity :=
1739 l_detail_level_tbl(l_index).primary_quantity;
1740 l_pp_temp_tbl(l_index).transaction_quantity :=
1741 l_detail_level_tbl(l_index).transaction_quantity;
1742 l_pp_temp_tbl(l_index).reservation_id :=
1743 l_detail_level_tbl(l_index).reservation_id;
1744 l_pp_temp_tbl(l_index).from_cost_group_id :=
1745 p_request_line_rec.from_cost_group_id;
1746 l_pp_temp_tbl(l_index).to_cost_group_id :=
1747 p_request_line_rec.to_cost_group_id;
1748 END LOOP;
1749 l_pp_temp_tbl_size := l_detail_level_tbl_size;
1750 --
1751 -- get the pick rule at item or org level
1752 --
1753 get_pick_rule (p_request_line_rec.organization_id,
1754 p_request_line_rec.inventory_item_id,
1755 l_rule_id,
1756 l_return_status ,
1757 x_msg_count ,
1758 x_msg_data
1759 );
1760 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1761 RAISE fnd_api.g_exc_unexpected_error ;
1762 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1763 RAISE fnd_api.g_exc_error ;
1764 END IF;
1765 init_qty_tree(l_return_status,
1766 p_request_context,
1767 p_request_line_rec,
1768 l_tree_id,
1769 x_msg_count,
1770 x_msg_data
1771 );
1772 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1773 RAISE fnd_api.g_exc_unexpected_error ;
1774 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1775 RAISE fnd_api.g_exc_error ;
1776 END IF;
1777 -- detail individual records
1778 FOR l_index IN 1..l_pp_temp_tbl_size LOOP
1779 --
1780 -- Using the picking rules, build the SQL dynamically
1781 --
1782 get_sql_for_rule(l_rule_id,
1783 l_pp_temp_tbl(l_index) ,
1784 p_request_context,
1785 p_request_line_rec,
1786 l_return_status,
1787 x_msg_count,
1788 x_msg_data
1789 );
1790 --
1791 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1792 RAISE fnd_api.g_exc_unexpected_error ;
1793 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1794 RAISE fnd_api.g_exc_error ;
1795 END IF;
1796 --
1797 -- detailing by execute the picking sql to find pick location
1798 -- and find out put away locations for transfer
1799 --
1800 detail_xfer_or_pick( l_pp_temp_tbl(l_index),
1801 p_request_context,
1802 p_request_line_rec,
1803 l_rule_id,
1804 l_tree_id,
1805 l_return_status,
1806 x_msg_count,
1807 x_msg_data
1808 );
1809 IF l_return_status = fnd_api.g_ret_sts_error THEN
1810 RAISE fnd_api.g_exc_error ;
1811 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1812 RAISE fnd_api.g_exc_unexpected_error;
1813 END IF;
1814 END LOOP;
1815 -- free the quantity tree after inserting output records
1816 -- Bug 1384720 - performance improvements
1817 -- No need to free tree; save it for other queries
1818 /*
1819 inv_quantity_tree_pvt.free_tree
1820 (
1821 p_api_version_number => 1.0
1822 ,p_init_msg_lst => fnd_api.g_false
1823 ,x_return_status => l_return_status
1824 ,x_msg_count => x_msg_count
1825 ,x_msg_data => x_msg_data
1826 ,p_tree_id => l_tree_id
1827 );
1828 IF l_return_status = fnd_api.g_ret_sts_error THEN
1829 RAISE fnd_api.g_exc_error ;
1830 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1831 RAISE fnd_api.g_exc_unexpected_error;
1832 END IF;
1833 */
1834 ELSE
1835 detail_putaway_only (p_request_line_rec ,
1836 p_request_context,
1837 l_return_status ,
1838 x_msg_count ,
1839 x_msg_data
1840 );
1841 END IF;
1842 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1843 RAISE fnd_api.g_exc_unexpected_error ;
1844 ELSIF l_return_status = fnd_api.g_ret_sts_error THEN
1845 raise fnd_api.g_exc_error ;
1846 END IF;
1847 -- if no locations has been found, lets report error
1848 IF inv_detail_util_pvt.g_output_process_tbl.COUNT = 0 THEN
1849 --edited out called to fnd_message by jcearley, 12/2/99
1850 --fnd_message.set_name('INV','INV_SUGGESTION_FAILED');
1851 -- Suggestions not or only partially created through applying strategy
1852 --fnd_msg_pub.add;
1853 --RAISE fnd_api.g_exc_error;
1854 RETURN; -- do not raise exeception since it is not an error if can't find qty
1855 END IF;
1856 -- now we can generate records into transactions temp tables
1857 inv_detail_util_pvt.process_output(l_return_status,
1858 p_request_line_rec,
1859 p_request_context
1860 );
1861 IF l_return_status = fnd_api.g_ret_sts_error THEN
1862 RAISE fnd_api.g_exc_error ;
1863 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1864 RAISE fnd_api.g_exc_unexpected_error;
1865 END IF;
1866 x_return_status := l_return_status;
1867 EXCEPTION
1868 WHEN fnd_api.g_exc_error THEN
1869 x_return_status := fnd_api.g_ret_sts_error ;
1870 --
1871 WHEN fnd_api.g_exc_unexpected_error THEN
1872 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1873 --
1874 WHEN OTHERS THEN
1875 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1876 --
1877 IF (fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)) THEN
1878 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name);
1879 END IF;
1880 --
1881 END detail_request;
1882 --
1883 -- Description
1884 -- Create transaction suggestions api based on rules in mtl_picking_rules.
1885 --
1886 -- The output of this procedure is records in mtl_material_transactions_temp
1887 -- , mtl_transaction_lots_temp, and mtl_serial_numbers_temp.
1888 --
1889 -- Notes
1890 -- 1. Integration with reservations
1891 -- If table p_reservations passed by the calling is not empty, the
1892 -- engine will detailing based on a combination of the info in the
1893 -- move order line (the record that represents detailing request),
1894 -- and the info in p_reservations. For example, a sales order line
1895 -- can have two reservations, one for revision A in quantity of 10,
1896 -- and one for revision B in quantity of 5, and the line quantity
1897 -- can be 15; so when the pick release api calls the engine
1898 -- p_reservations will have two records of the reservations. So
1899 -- if the move order line based on the sales order line does not
1900 -- specify a revision, the engine will merge the information from
1901 -- move order line and p_reservations to create the input for
1902 -- detailing as two records, one for revision A, and one for revision
1903 -- B. Please see documentation for the pick release API for more
1904 -- details.
1905 --
1906 -- 2. Serial Number Detailing in Picking
1907 -- Currently the serial number detailing is quite simple. If the caller
1908 -- gives a range (start, and end) serial numbers in the move order line
1909 -- and pass p_suggest_serial as fnd_api.true, the engine will filter
1910 -- the locations found from a rule, and suggest unused serial numbers
1911 -- in the locator. If p_suggest_serial is passed as fnd_api.g_false
1912 -- (default), the engine will not give serial numbers in the output.
1913 --
1914 -- Input Parameters
1915 -- p_api_version_number standard input parameter
1916 -- p_init_msg_lst standard input parameter
1917 -- p_commit standard input parameter
1918 -- p_validation_level standard input parameter
1919 -- p_transaction_temp_id equals to the move order line id
1920 -- for the detailing request
1921 -- p_reservations reservations for the demand source
1922 -- as the transaction source
1923 -- in the move order line.
1924 -- p_suggest_serial whether or not the engine should suggest
1925 -- serial numbers in the detailing
1926 --
1927 -- Output Parameters
1928 -- x_return_status standard output parameters
1929 -- x_msg_count standard output parameters
1930 -- x_msg_data standard output parameters
1931 --
1932 PROCEDURE create_suggestions
1933 (p_api_version IN NUMBER,
1934 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_false,
1935 p_commit IN VARCHAR2 DEFAULT fnd_api.g_false,
1936 p_validation_level IN NUMBER DEFAULT fnd_api.g_valid_level_none,
1937 x_return_status OUT NOCOPY VARCHAR2,
1938 x_msg_count OUT NOCOPY NUMBER,
1939 x_msg_data OUT NOCOPY VARCHAR2,
1940 p_transaction_temp_id IN NUMBER,
1941 p_reservations IN inv_reservation_global.mtl_reservation_tbl_type,
1942 p_suggest_serial IN VARCHAR2 DEFAULT fnd_api.g_false
1943 ) IS
1944 --
1945 -- constants
1946 l_api_name CONSTANT VARCHAR(30) := 'create_suggestions';
1947 c_api_version_number CONSTANT NUMBER := 1.0;
1948 l_return_status VARCHAR2(1) := fnd_api.g_ret_sts_success ;
1949 l_request_context inv_detail_util_pvt.g_request_context_rec_type;
1950 l_request_line_rec inv_detail_util_pvt.g_request_line_rec_type;
1951 BEGIN
1952 -- Standard start of API savepoint
1953 SAVEPOINT create_suggestions_sa;
1954 --
1955 --Standard compatibility check
1956 IF NOT fnd_api.compatible_api_call(
1957 c_api_version_number
1958 , p_api_version
1959 , l_api_name
1960 , g_pkg_name) THEN
1961 RAISE fnd_api.g_exc_unexpected_error;
1962 END IF;
1963 --
1964 -- Initialize message list
1965 IF fnd_api.to_boolean(p_init_msg_list) THEN
1966 fnd_msg_pub.initialize;
1967 END IF;
1968 --
1969 x_return_status := fnd_api.g_ret_sts_success ;
1970 --
1971 -- validate input and initialize
1972 inv_detail_util_pvt.validate_and_init
1973 (l_return_status ,
1974 p_transaction_temp_id ,
1975 p_suggest_serial ,
1976 l_request_line_rec ,
1977 l_request_context
1978 );
1979 IF l_return_status = fnd_api.g_ret_sts_error THEN
1980 RAISE fnd_api.g_exc_error;
1981 END IF;
1982 IF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
1983 RAISE fnd_api.g_exc_unexpected_error;
1984 END IF;
1985
1986 --don't detail serial numbers for items that are serial controlled only
1987 -- at sales order issue
1988 IF l_request_context.item_serial_control_code = 6 THEN
1989 l_request_context.item_serial_control_code := 1;
1990 END IF;
1991 --
1992 detail_request (l_request_context ,
1993 l_request_line_rec,
1994 p_reservations ,
1995 l_return_status ,
1996 x_msg_count ,
1997 x_msg_data
1998 );
1999 IF (l_return_status = fnd_api.g_ret_sts_unexp_error ) THEN
2000 RAISE fnd_api.g_exc_unexpected_error ;
2001 ELSIF ( l_return_status = fnd_api.g_ret_sts_error ) THEN
2002 RAISE fnd_api.g_exc_error ;
2003 END IF;
2004 --
2005 -- Standard check of p_commit
2006 IF fnd_api.to_boolean(p_commit) THEN
2007 COMMIT;
2008 END IF;
2009 --
2010 --
2011 x_return_status := l_return_status;
2012 --
2013 EXCEPTION
2014 WHEN fnd_api.g_exc_error THEN
2015 --
2016 -- debugging section
2017 -- can be commented ut for final code
2018 IF inv_pp_debug.is_debug_mode THEN
2019 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2020 -- the message retrieved here since it is no longer on the stack
2021 inv_pp_debug.set_last_error_message(Sqlerrm);
2022 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2023 inv_pp_debug.send_last_error_message;
2024 END IF;
2025 -- end of debugging section
2026 --
2027 ROLLBACK TO create_suggestions_sa;
2028 x_return_status := fnd_api.g_ret_sts_error;
2029 fnd_msg_pub.Count_And_Get
2030 ( p_count => x_msg_count
2031 ,p_data => x_msg_data);
2032 --
2033 WHEN fnd_api.g_exc_unexpected_error THEN
2034 --
2035 -- debugging section
2036 -- can be commented ut for final code
2037 IF inv_pp_debug.is_debug_mode THEN
2038 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2039 -- the message retrieved here since it is no longer on the stack
2040 inv_pp_debug.set_last_error_message(Sqlerrm);
2041 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2042 inv_pp_debug.send_last_error_message;
2043 END IF;
2044 -- end of debugging section
2045 --
2046 ROLLBACK TO create_suggestions_sa;
2047 x_return_status := fnd_api.g_ret_sts_unexp_error;
2048 fnd_msg_pub.Count_And_Get
2049 ( p_count => x_msg_count
2050 ,p_data => x_msg_data);
2051 --
2052 WHEN OTHERS THEN
2053 --
2054 -- debugging section
2055 -- can be commented ut for final code
2056 IF inv_pp_debug.is_debug_mode THEN
2057 -- Note: in debug mode, later call to fnd_msg_pub.get will not get
2058 -- the message retrieved here since it is no longer on the stack
2059 inv_pp_debug.set_last_error_message(Sqlerrm);
2060 inv_pp_debug.send_message_to_pipe('exception in '||l_api_name);
2061 inv_pp_debug.send_last_error_message;
2062 END IF;
2063 -- end of debugging section
2064 --
2065 ROLLBACK TO create_suggestions_sa;
2066 x_return_status := fnd_api.g_ret_sts_unexp_error;
2067 IF fnd_msg_pub.Check_Msg_Level(fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2068 fnd_msg_pub.Add_Exc_Msg(g_pkg_name, l_api_name);
2069 END IF;
2070 fnd_msg_pub.count_and_get
2071 ( p_count => x_msg_count
2072 ,p_data => x_msg_data);
2073 END create_suggestions;
2074 END INV_AUTODETAIL;