[Home] [Help]
PACKAGE BODY: APPS.WIP_PICKING_PVT
Source
1 package body wip_picking_pvt as
2 /* $Header: wippckvb.pls 120.18.12010000.4 2009/01/05 21:35:59 hliew ship $ */
3
4 procedure explode(p_organization_id NUMBER,
5 p_bill_sequence_id NUMBER,
6 p_revision_date DATE,
7 p_primary_item_id NUMBER,
8 p_alternate_bom_designator VARCHAR2,
9 p_user_id NUMBER,
10 x_return_status OUT NOCOPY VARCHAR2,
11 x_msg_data OUT NOCOPY VARCHAR2) IS
12 l_errCode NUMBER;
13 l_count NUMBER;
14 CURSOR c_components IS
15 select row_id, component_quantity, component_item_id,
16 component_code, operation_seq_num
17 from wip_explosions_v
18 where top_bill_sequence_id = p_bill_sequence_id
19 and organization_id = p_organization_id
20 and wip_supply_type = wip_constants.phantom
21 order by component_code;
22
23 CURSOR c_lock
24 IS select '1'
25 from bom_explosions
26 where top_bill_sequence_id = p_bill_sequence_id
27 and organization_id = p_organization_id
28 for update nowait;
29 BEGIN
30 SAVEPOINT WIP_EXPLODE_START;
31 --delete any previous explosions as effectivity/disable dates could be changed
32 --make sure the records are not locked.
33 open c_lock;
34
35 delete bom_explosions
36 where top_bill_sequence_id = p_bill_sequence_id
37 and organization_id = p_organization_id;
38
39
40
41 bom_oe_exploder_pkg.be_exploder(arg_org_id => p_organization_id,
42 arg_starting_rev_date => p_revision_date,
43 arg_expl_type => 'ALL',
44 arg_item_id => p_primary_item_id,
45 arg_alt_bom_desig => p_alternate_bom_designator,
46 arg_error_code => l_errCode,
47 arg_err_msg => x_msg_data);
48 if(l_errCode <> 0) then
49 raise fnd_api.G_EXC_UNEXPECTED_ERROR;
50 else
51 x_return_status := fnd_api.g_ret_sts_success;
52 end if;
53
54 FOR l_compRec IN c_components LOOP
55 update bom_explosions
56 set component_quantity = component_quantity * l_compRec.component_quantity,
57 operation_seq_num = l_compRec.operation_seq_num
58 where component_code <> l_compRec.component_code
59 and component_code like l_compRec.component_code || '%'
60 and top_bill_sequence_id = p_bill_sequence_id
61 and organization_id = p_organization_id;
62 END LOOP;
63
64 close c_lock; -- done processing...remove the lock on the rows, however note that the delete statement
65 -- has placed a new lock on the rows.
66 exception
67 when RECORDS_LOCKED then
68 ROLLBACK TO WIP_EXPLODE_START;
69 x_return_status := 'L';
70 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
71 x_msg_data := fnd_message.get;
72 when others then
73 if(c_lock%ISOPEN) then
74 close c_lock;
75 end if;
76 ROLLBACK TO WIP_EXPLODE_START;
77 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
78 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
79 fnd_message.set_token('ERROR_TEXT', SQLERRM);
80 /*This line is added as a fix for bug#2629809*/
81 x_msg_data := fnd_message.get;
82 end explode;
83
84 procedure cancel_allocations(p_wip_entity_id NUMBER,
85 p_wip_entity_type NUMBER,
86 p_repetitive_schedule_id NUMBER := NULL,
87 x_return_status OUT NOCOPY VARCHAR2,
88 x_msg_data OUT NOCOPY VARCHAR2) IS
89 l_msgCount NUMBER;
90 l_lineID NUMBER;
91 l_wroRowID ROWID;
92 l_openQty NUMBER;
93
94 /* Bugfix 4743190 : Modified the query to avoid merge join (cartesian). Instead of joining with
95 bind variables, use table joins. Also added rownum in the subquery */
96
97 CURSOR c_repLines IS
98 select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
99 from mtl_txn_request_lines mtrl, wip_requirement_operations wro
100 where wro.wip_entity_id = p_wip_entity_id
101 and wro.repetitive_schedule_id = p_repetitive_schedule_id
102 and mtrl.txn_source_id = wro.wip_entity_id
103 and mtrl.reference_id = wro.repetitive_schedule_id
104 and mtrl.txn_source_line_id = wro.operation_seq_num
105 and mtrl.inventory_item_id = wro.inventory_item_id
106 and mtrl.organization_id = wro.organization_id
107 and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
108 and exists(select 1
109 from mtl_txn_request_headers mtrh
110 where mtrl.header_id = mtrh.header_id
111 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK
112 and rownum = 1)
113
114 for update of wro.quantity_allocated, quantity_backordered nowait;
115
116 /* Bugfix 4211265: Cursor c_discLines is doing an FTS on mtrl. We will join with wip_entities to get the organization_id and join with mtrl thru wro so that index MTL_TXN_REQUEST_LINES_N1 can be used. */
117
118 CURSOR c_discLines IS
119 select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
120 from mtl_txn_request_lines mtrl, wip_requirement_operations wro, wip_entities we /* bug 4211266 */
121 where we.wip_entity_id = p_wip_entity_id
122 and wro.organization_id = we.organization_id
123 and wro.wip_entity_id = we.wip_entity_id
124 and mtrl.organization_id = wro.organization_id
125 and mtrl.inventory_item_id = wro.inventory_item_id
126 and mtrl.txn_source_id = we.wip_entity_id
127 and mtrl.txn_source_line_id = wro.operation_seq_num
128 and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
129 and exists(select 1
130 from mtl_txn_request_headers mtrh
131 where mtrl.header_id = mtrh.header_id
132 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK
133 and rownum = 1)
134
135 for update of wro.quantity_allocated, quantity_backordered nowait;
136
137 /* End of Bugfix 4743190 */
138
139 CURSOR c_flowLines IS
140 select line_id
141 from mtl_txn_request_lines mtrl, wip_entities we
142 where mtrl.TRANSACTION_TYPE_ID = INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR -- 51
143 and mtrl.TRANSACTION_SOURCE_TYPE_ID = INV_Globals.G_SOURCETYPE_INVENTORY --13
144 and mtrl.txn_source_id = we.wip_entity_id
145 and mtrl.organization_id = we.organization_id
146 and we.wip_entity_id = p_wip_entity_id
147 and exists(select 1
148 from mtl_txn_request_headers mtrh
149 where mtrl.header_id = mtrh.header_id
150 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK);
151
152 BEGIN
153 if(p_wip_entity_type not in (wip_constants.discrete,
154 wip_constants.lotbased,
155 wip_constants.repetitive,
156 wip_constants.flow,
157 wip_constants.eam)) then
158 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
159 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
160 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.Incorrect Entity type passed');
161 x_msg_data := fnd_message.get;
162 return ;
163 end if ;
164
165 SAVEPOINT WIP_CANCEL_ALLOCS_START;
166
167 x_return_status := FND_API.G_RET_STS_SUCCESS; -- in case no open lines are found
168 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
169 open c_discLines;
170 elsif(p_wip_entity_type = wip_constants.repetitive) then
171 open c_repLines;
172 elsif(p_wip_entity_type = wip_constants.flow) then
173 open c_flowLines;
174 end if;
175 loop --need to handle failure conditions!!!
176 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
177 fetch c_discLines into l_lineID, l_openQty, l_wroRowID;
178 exit when c_discLines%NOTFOUND;
179 elsif(p_wip_entity_type = wip_constants.repetitive) then
180 fetch c_repLines into l_lineID, l_openQty, l_wroRowID;
181 exit when c_repLines%NOTFOUND;
182 elsif(p_wip_entity_type = wip_constants.flow) then
183 fetch c_flowLines into l_lineID;
184 exit when c_flowLines%NOTFOUND;
185 end if;
186
187 cancel_MO_line(p_lineId =>l_lineID ,
188 p_rowId => l_wroRowID,
189 p_wip_entity_type => p_wip_entity_type,
190 p_openQty => l_openQty,
191 x_return_status => x_return_status,
192 x_msg_data => x_msg_data);
193
194 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
195 if(c_discLines%ISOPEN) then
196 close c_discLines;
197 elsif(c_repLines%ISOPEN) then
198 close c_repLines;
199 elsif(c_flowLines%ISOPEN) then
200 close c_flowLines;
201 end if;
202 raise FND_API.G_EXC_UNEXPECTED_ERROR;
203 end if;
204 end loop;
205 if(c_discLines%ISOPEN) then
206 close c_discLines;
207 elsif(c_repLines%ISOPEN) then
208 close c_repLines;
209 elsif(c_flowLines%ISOPEN) then
210 close c_flowLines;
211 end if;
212
213 --finally update all backordered quantity to 0
214 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
215 update wip_requirement_operations
216 set quantity_backordered = 0
217 where wip_entity_id = p_wip_entity_id;
218 elsif(p_wip_entity_type = wip_constants.repetitive) then
219 update wip_requirement_operations
220 set quantity_backordered = 0
221 where wip_entity_id = p_wip_entity_id
222 and repetitive_schedule_id = p_repetitive_schedule_id;
223 end if;
224 exception
225 when fnd_api.g_exc_unexpected_error then
226 ROLLBACK TO WIP_CANCEL_ALLOCS_START;
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 when others then
229 if(c_discLines%ISOPEN) then
230 close c_discLines;
231 elsif(c_repLines%ISOPEN) then
232 close c_repLines;
233 elsif(c_flowLines%ISOPEN) then
234 close c_flowLines;
235 end if;
236 ROLLBACK TO WIP_CANCEL_ALLOCS_START;
237 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
238 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
239 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.cancel_allocations: ' || SQLERRM);
240 x_msg_data := fnd_message.get;
241 end cancel_allocations;
242
243
244 Procedure cancel_comp_allocations(p_wip_entity_id NUMBER,
245 p_operation_seq_num NUMBER,
246 p_inventory_item_id NUMBER,
247 p_wip_entity_type NUMBER,
248 p_repetitive_schedule_id NUMBER DEFAULT NULL,
249 x_return_status OUT NOCOPY VARCHAR2,
250 x_msg_data OUT NOCOPY VARCHAR2) IS
251 l_msgCount NUMBER;
252 l_lineID NUMBER;
253 l_wroRowID ROWID;
254 l_openQty NUMBER;
255 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
256 l_dummy VARCHAR2(1);
257
258 CURSOR c_repLines IS
259 select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
260 from mtl_txn_request_lines mtrl, wip_requirement_operations wro
261 where mtrl.txn_source_id = p_wip_entity_id
262 and mtrl.reference_id = p_repetitive_schedule_id
263 and wro.wip_entity_id = p_wip_entity_id
264 and wro.repetitive_schedule_id = p_repetitive_schedule_id
265 and wro.operation_seq_num = p_operation_seq_num
266 and mtrl.txn_source_line_id = wro.operation_seq_num
267 and wro.inventory_item_id = p_inventory_item_id
268 and mtrl.inventory_item_id = wro.inventory_item_id
269 and mtrl.organization_id = wro.organization_id
270 and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
271 and exists(select 1
272 from mtl_txn_request_headers mtrh
273 where mtrl.header_id = mtrh.header_id
274 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
275
276 for update of wro.quantity_allocated, quantity_backordered nowait;
277
278 CURSOR c_discLines IS
279 select line_id, (nvl(quantity_detailed,0) - nvl(quantity_delivered,0)) open_quantity, wro.rowid
280 from mtl_txn_request_lines mtrl, wip_requirement_operations wro
281 where mtrl.txn_source_id = p_wip_entity_id
282 and wro.wip_entity_id = p_wip_entity_id
283 and wro.operation_seq_num = p_operation_seq_num
284 and mtrl.txn_source_line_id = wro.operation_seq_num
285 and wro.inventory_item_id = p_inventory_item_id
286 and mtrl.inventory_item_id = wro.inventory_item_id
287 and mtrl.organization_id = wro.organization_id
288 and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
289 and exists(select 1
290 from mtl_txn_request_headers mtrh
291 where mtrl.header_id = mtrh.header_id
292 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
293
294 for update of wro.quantity_allocated, quantity_backordered nowait;
295
296
297 BEGIN
298 SAVEPOINT WIP_CANCEL_COMP_ALLOCS_START;
299 x_return_status := FND_API.G_RET_STS_SUCCESS; -- in case no open lines are found
300
301 if (l_logLevel <= wip_constants.trace_logging) then
302 wip_logger.log('In wip_picking_pvt.cancel_comp_allocations', l_dummy);
303 end if;
304 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
305 open c_discLines;
306 elsif(p_wip_entity_type = wip_constants.repetitive) then
307 open c_repLines;
308 else
309 if (l_logLevel <= wip_constants.trace_logging) then
310 wip_logger.log('Invalid entity type passed to wip_picking_pvt.cancel_comp_allocations:'
311 || p_wip_entity_type, l_dummy);
312 end if;
313 end if;
314
315 loop
316 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
317 fetch c_discLines into l_lineID, l_openQty, l_wroRowID;
318 exit when c_discLines%NOTFOUND;
319 elsif(p_wip_entity_type = wip_constants.repetitive) then
320 fetch c_repLines into l_lineID, l_openQty, l_wroRowID;
321 exit when c_repLines%NOTFOUND;
322 end if;
323
324 if (l_logLevel <= wip_constants.trace_logging) then
325 wip_logger.log('line ID:' || l_lineID || ',Open Qty:' || l_openQty || ',WRO RowID:'
326 || l_wroRowID || ',p_wip_entity_type:' || p_wip_entity_type, l_dummy);
327 end if;
328
329 cancel_MO_line(p_lineId =>l_lineID ,
330 p_rowId => l_wroRowID,
331 p_wip_entity_type => p_wip_entity_type,
332 p_openQty => l_openQty,
333 x_return_status => x_return_status,
334 x_msg_data => x_msg_data);
335
336 end loop;
337
338 if(c_discLines%ISOPEN) then
339 close c_discLines;
340 elsif(c_repLines%ISOPEN) then
341 close c_repLines;
342 end if;
343 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
344 if(c_discLines%ISOPEN) then
345 close c_discLines;
346 elsif(c_repLines%ISOPEN) then
347 close c_repLines;
348 end if;
349 raise FND_API.G_EXC_UNEXPECTED_ERROR;
350 end if;
351
352 --finally update backordered quantity to 0
353 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
354 update wip_requirement_operations
355 set quantity_backordered = 0
356 where wip_entity_id = p_wip_entity_id
357 and operation_seq_num = p_operation_seq_num
358 and inventory_item_id = p_inventory_item_id;
359 elsif(p_wip_entity_type = wip_constants.repetitive) then
360 update wip_requirement_operations
361 set quantity_backordered = 0
362 where wip_entity_id = p_wip_entity_id
363 and repetitive_schedule_id = p_repetitive_schedule_id
364 and operation_seq_num = p_operation_seq_num
365 and inventory_item_id = p_inventory_item_id;
366 end if;
367 exception
368 when fnd_api.g_exc_unexpected_error then
369 ROLLBACK TO WIP_CANCEL_COMP_ALLOCS_START;
370 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
371 when others then
372 if(c_discLines%ISOPEN) then
373 close c_discLines;
374 elsif(c_repLines%ISOPEN) then
375 close c_repLines;
376 end if;
377 ROLLBACK TO WIP_CANCEL_COMP_ALLOCS_START;
378 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
379 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
380 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.cancel_comp_allocations: ' || SQLERRM);
381 x_msg_data := fnd_message.get;
382 END cancel_comp_allocations;
383
384 Procedure cancel_MO_line(p_lineId IN NUMBER,
385 p_rowId ROWID,
386 p_wip_entity_type NUMBER,
387 p_openQty NUMBER,
388 x_return_status OUT NOCOPY VARCHAR2,
389 x_msg_data OUT NOCOPY VARCHAR2
390 ) IS
391
392 l_msgCount NUMBER;
393
394 BEGIN
395 update wip_requirement_operations
396 set quantity_allocated = nvl(quantity_allocated,0) - p_openQty,
397 last_update_date = sysdate,
398 last_updated_by = fnd_global.user_id,
399 last_update_login = fnd_global.login_id
400 where rowid = p_rowId;
401
402 INV_MO_Cancel_PVT.Cancel_Move_Order_Line(x_return_status => x_return_status,
403 x_msg_count => l_msgCount,
404 x_msg_data => x_msg_data,
405 p_line_id => p_lineId,
406 p_delete_reservations => 'N');
407 END cancel_MO_line;
408
409 procedure reduce_comp_allocations(p_comp_tbl IN wip_picking_pub.allocate_comp_tbl_t,
410 p_wip_entity_type NUMBER,
411 p_organization_id NUMBER,
412 x_return_status OUT NOCOPY VARCHAR2,
413 x_msg_data OUT NOCOPY VARCHAR2)IS
414
415 l_msgCount NUMBER;
416 l_lineID NUMBER;
417 l_wroRowID ROWID;
418 l_openQty NUMBER;
419 l_reductionQty NUMBER;
420
421 CURSOR c_repLines(v_wip_entity_id NUMBER, v_repetitive_schedule_id NUMBER, v_operation_seq_num NUMBER, v_inventory_item_id NUMBER) IS
422 select line_id, nvl(mtrl.required_quantity,(nvl(quantity_detailed,0) - nvl(quantity_delivered,0))) open_quantity, wro.rowid
423 from mtl_txn_request_lines mtrl, wip_requirement_operations wro
424 where mtrl.txn_source_id = v_wip_entity_id
425 and mtrl.reference_id = v_repetitive_schedule_id
426 and wro.wip_entity_id = v_wip_entity_id
427 and wro.repetitive_schedule_id = v_repetitive_schedule_id
428 and wro.operation_seq_num = v_operation_seq_num
429 and mtrl.txn_source_line_id = wro.operation_seq_num
430 and wro.inventory_item_id = v_inventory_item_id
431 and mtrl.inventory_item_id = wro.inventory_item_id
432 and mtrl.organization_id = wro.organization_id
433 and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
434 and exists(select 1
435 from mtl_txn_request_headers mtrh
436 where mtrl.header_id = mtrh.header_id
437 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
438
439 order by mtrl.creation_date desc /* Traverse thru move orders in LIFO fashion */
440
441 for update of wro.quantity_allocated, quantity_backordered nowait;
442
443 CURSOR c_discLines(v_wip_entity_id NUMBER, v_operation_seq_num NUMBER, v_inventory_item_id NUMBER) IS
444 select line_id, nvl(mtrl.required_quantity,(nvl(quantity_detailed,0) - nvl(quantity_delivered,0))) open_quantity, wro.rowid
445 from mtl_txn_request_lines mtrl, wip_requirement_operations wro
446 where mtrl.txn_source_id = v_wip_entity_id
447 and wro.wip_entity_id = v_wip_entity_id
448 and wro.operation_seq_num = v_operation_seq_num
449 and mtrl.txn_source_line_id = wro.operation_seq_num
450 and wro.inventory_item_id = v_inventory_item_id
451 and mtrl.inventory_item_id = wro.inventory_item_id
452 and mtrl.organization_id = wro.organization_id
453 and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
454 and exists(select 1
455 from mtl_txn_request_headers mtrh
456 where mtrl.header_id = mtrh.header_id
457 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
458
459 order by mtrl.creation_date desc /* Traverse thru move orders in LIFO fashion */
460 for update of wro.quantity_allocated, quantity_backordered nowait;
461
462
463 BEGIN
464 SAVEPOINT WIP_REDUCE_COMP_ALLOCS_START;
465 x_return_status := FND_API.G_RET_STS_SUCCESS; -- in case no open lines are found
466 for i in 1..p_comp_tbl.COUNT LOOP /* Component Loop*/
467 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
468 open c_discLines(p_comp_tbl(i).wip_entity_id, p_comp_tbl(i).operation_seq_num, p_comp_tbl(i).inventory_item_id);
469 elsif(p_wip_entity_type = wip_constants.repetitive) then
470 open c_repLines(p_comp_tbl(i).wip_entity_id, p_comp_tbl(i).repetitive_schedule_id, p_comp_tbl(i).operation_seq_num, p_comp_tbl(i).inventory_item_id);
471 end if;
472 l_reductionQty := p_comp_tbl(i).requested_quantity;
473
474 Loop /* Loop through all the MO lines for this component */
475
476 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
477 fetch c_discLines into l_lineID, l_openQty, l_wroRowID;
478 exit when c_discLines%NOTFOUND;
479 elsif(p_wip_entity_type = wip_constants.repetitive) then
480 fetch c_repLines into l_lineID, l_openQty, l_wroRowID;
481 exit when c_repLines%NOTFOUND;
482 end if;
483
484 if(l_openQty <= l_reductionQty) then
485 INV_MO_Cancel_PVT.Cancel_Move_Order_Line(x_return_status => x_return_status,
486 x_msg_count => l_msgCount,
487 x_msg_data => x_msg_data,
488 p_line_id => l_lineId,
489 p_delete_reservations => 'N');
490 l_reductionQty := l_reductionQty - l_openQty;
491 else
492 INV_MO_Cancel_PVT.Reduce_Move_Order_Quantity(x_return_status => x_return_status,
493 x_msg_count => l_msgCount,
494 x_msg_data => x_msg_data,
495 p_line_id => l_lineId,
496 p_reduction_quantity => l_reductionQty);
497 l_reductionQty := 0;
498 end if;
499
500 if (l_reductionQty = 0) then
501 exit ;
502 end if;
503 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
504 if(c_discLines%ISOPEN) then
505 close c_discLines;
506 elsif(c_repLines%ISOPEN) then
507 close c_repLines;
508 end if;
509 raise FND_API.G_EXC_UNEXPECTED_ERROR;
510 end if;
511
512 end Loop; /* End of MO lines loop */
513
514 if(l_reductionQty <> 0) then /* if requested quantity is more than total allocated quantity */
515 x_return_status := 'P'; /*then, return a warning message that only allocated quantity has been reduced*/
516 fnd_message.set_name('WIP', 'WIP_PICKING_PARTIAL_REDUCTION');
517 x_msg_data := fnd_message.get;
518 end if;
519
520 if(c_discLines%ISOPEN) then
521 close c_discLines;
522 elsif(c_repLines%ISOPEN) then
523 close c_repLines;
524 end if;
525
526 /*finally update backordered quantity and allocated quantity for this component*/
527 if(p_wip_entity_type in (wip_constants.discrete, wip_constants.lotbased, wip_constants.eam)) then
528 update wip_requirement_operations
529 set quantity_backordered = greatest((quantity_backordered - p_comp_tbl(i).requested_quantity), 0),
530 quantity_allocated = quantity_allocated - p_comp_tbl(i).requested_quantity
531 where wip_entity_id = p_comp_tbl(i).wip_entity_id
532 and operation_seq_num = p_comp_tbl(i).operation_seq_num
533 and inventory_item_id = p_comp_tbl(i).inventory_item_id;
534 elsif(p_wip_entity_type = wip_constants.repetitive) then
535 update wip_requirement_operations
536 set quantity_backordered = greatest(quantity_backordered - p_comp_tbl(i).requested_quantity, 0),
537 quantity_allocated = quantity_allocated - p_comp_tbl(i).requested_quantity
538 where wip_entity_id = p_comp_tbl(i).wip_entity_id
539 and repetitive_schedule_id = p_comp_tbl(i).repetitive_schedule_id
540 and operation_seq_num = p_comp_tbl(i).operation_seq_num
541 and inventory_item_id = p_comp_tbl(i).inventory_item_id;
542 end if;
543 end LOOP; /*End of component loop*/
544 exception
545 when fnd_api.g_exc_unexpected_error then
546 ROLLBACK TO WIP_REDUCE_COMP_ALLOCS_START;
547 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
548 when others then
549 if(c_discLines%ISOPEN) then
550 close c_discLines;
551 elsif(c_repLines%ISOPEN) then
552 close c_repLines;
553 end if;
554 ROLLBACK TO WIP_REDUCE_COMP_ALLOCS_START;
555 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
556 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
557 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.cancel_allocations: ' || SQLERRM);
558 x_msg_data := fnd_message.get;
559 END reduce_comp_allocations;
560
561
562
563
564 procedure update_allocation_op_seqs(p_wip_entity_id IN NUMBER,
565 p_repetitive_schedule_id IN NUMBER := null,
566 p_operation_seq_num IN NUMBER,
567 x_return_status OUT NOCOPY VARCHAR2,
568 x_msg_data OUT NOCOPY VARCHAR2) IS
569 CURSOR c_repLines IS
570 select line_id
571 from mtl_txn_request_lines mtrl, wip_requirement_operations wro
572 where mtrl.txn_source_id = p_wip_entity_id
573 and mtrl.txn_source_line_id = p_repetitive_schedule_id
574 and wro.wip_entity_id = p_wip_entity_id
575 and wro.repetitive_schedule_id = p_repetitive_schedule_id
576 and wro.operation_seq_num = 1
577 and mtrl.txn_source_line_id = wro.operation_seq_num
578 and mtrl.inventory_item_id = wro.inventory_item_id
579 and mtrl.organization_id = wro.organization_id
580 and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
581 and exists(select 1
582 from mtl_txn_request_headers mtrh
583 where mtrl.header_id = mtrh.header_id
584 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
585
586 for update of wro.quantity_allocated, wro.quantity_backordered nowait; --make sure no orders are transacted in the meantime
587
588 CURSOR c_discLines IS
589 select line_id
590 from mtl_txn_request_lines mtrl, wip_requirement_operations wro
591 where mtrl.txn_source_id = p_wip_entity_id
592 and wro.wip_entity_id = p_wip_entity_id
593 and wro.operation_seq_num = 1
594 and mtrl.txn_source_line_id = wro.operation_seq_num
595 and mtrl.inventory_item_id = wro.inventory_item_id
596 and mtrl.organization_id = wro.organization_id
597 and mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
598 and exists(select 1
599 from mtl_txn_request_headers mtrh
600 where mtrl.header_id = mtrh.header_id
601 and mtrh.move_order_type = INV_Globals.G_MOVE_ORDER_MFG_PICK)
602 for update of wro.quantity_allocated, wro.quantity_backordered nowait;--make sure no orders are transacted in the meantime
603
604 l_lineID NUMBER;
605 l_msgCount NUMBER;
606 BEGIN
607 SAVEPOINT WIP_UPDATE_ALLOCS_START;
608 x_return_status := FND_API.G_RET_STS_SUCCESS; -- in case no open lines are found
609 if(p_repetitive_schedule_id is null) then
610 open c_discLines;
611 else
612 open c_repLines;
613 end if;
614 loop --need to handle failure conditions!!!
615 if(p_repetitive_schedule_id is null) then
616 fetch c_discLines into l_lineID;
617 exit when c_discLines%NOTFOUND;
618 else
619 fetch c_repLines into l_lineID;
620 exit when c_repLines%NOTFOUND;
621 end if;
622
623 inv_wip_picking_pvt.update_mol_for_wip(p_move_order_line_id => l_lineID,
624 p_op_seq_num => p_operation_seq_num,
625 x_msg_count => l_msgCount,
626 x_msg_data => x_msg_data,
627 x_return_status => x_return_status);
628 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
629 if(c_discLines%ISOPEN) then
630 close c_discLines;
631 elsif(c_repLines%ISOPEN) then
632 close c_repLines;
633 end if;
634 raise FND_API.G_EXC_UNEXPECTED_ERROR;
635 end if;
636 end loop;
637 if(c_discLines%ISOPEN) then
638 close c_discLines;
639 elsif(c_repLines%ISOPEN) then
640 close c_repLines;
641 end if;
642
643 exception
644 when fnd_api.g_exc_unexpected_error then
645 ROLLBACK TO WIP_UPDATE_ALLOCS_START;
646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
647 when others then
648 if(c_discLines%ISOPEN) then
649 close c_discLines;
650 elsif(c_repLines%ISOPEN) then
651 close c_repLines;
652 end if;
653 ROLLBACK TO WIP_UPDATE_ALLOCS_START;
654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
655 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
656 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.update_allocation_op_seqs: ' || SQLERRM);
657 end update_allocation_op_seqs;
658
659 /* Starting for p_index element of p_alloc_tbl, find number of consective flow schedules
660 that contain the same assembly, assembly qty, and required date. This number allows
661 allocate() to explode the bom just once for the whole group
662 Parameters:
663 p_alloc_tbl: table of schedules to search through
664 p_index: index of the table to start at
665 p_item_id, p_ably_qty, p_req_date:
666 assembly, assembly qty, and required date of the schedule that
667 all subsequent schedules compare to to see if there is a match.
668 x_opt_total: the total of number schedules that can be grouped together.
669 */
670 procedure getOptimalFlowSchGrp(p_alloc_tbl IN wip_picking_pub.allocate_tbl_t,
671 p_index IN NUMBER,
672 p_item_id IN NUMBER,
673 p_ably_qty IN NUMBER,
674 p_req_date IN DATE,
675 x_opt_total OUT NOCOPY NUMBER) IS
676 l_orgid NUMBER;
677 l_billSeqId NUMBER;
678 l_itemID NUMBER;
679 l_reqDate DATE;
680 l_assemblyQty NUMBER;
681
682 CURSOR c_flowSchedules(v_wip_entity_id NUMBER) IS
683 select a.organization_id,
684 a.bill_sequence_id,
685 wfs.primary_item_id,
686 wfs.SCHEDULED_COMPLETION_DATE,
687 wfs.planned_quantity
688 from bom_bill_of_materials a, bom_bill_of_materials b, wip_flow_schedules wfs
689 where a.bill_sequence_id = b.common_bill_sequence_id
690 and b.assembly_item_id = wfs.primary_item_id
691 and wfs.wip_entity_id = v_wip_entity_id
692 and b.organization_id = wfs.organization_id
693 and ( nvl(b.alternate_bom_designator, 'none') = nvl(wfs.alternate_bom_designator, 'none')
694 or ( b.alternate_bom_designator IS NULL
695 and not exists(select 'x'
696 from bom_bill_of_materials c
697 where c.assembly_item_id = wfs.primary_item_id
698 and c.organization_id = wfs.organization_id
699 and c.alternate_bom_designator = wfs.alternate_bom_designator
700 )
701 )
702 )
703 for update of wfs.allocated_flag nowait;
704
705 begin
706 x_opt_total := 1;
707
708 for i in (p_index+1)..p_alloc_tbl.COUNT LOOP
709 open c_flowSchedules(p_alloc_tbl(i).wip_entity_id);
710 fetch c_flowSchedules into l_orgid, l_billSeqId, l_itemID,
711 l_reqDate, l_assemblyQty;
712 exit when c_flowSchedules%NOTFOUND;
713
714 if (p_alloc_tbl(p_index).bill_org_id <> l_orgid or
715 p_alloc_tbl(p_index).bill_seq_id <> l_billSeqId or
716 p_item_id <> l_itemID or
717 trunc(p_req_date) <> trunc(l_reqDate) or
718 p_ably_qty <> l_assemblyQty) then
719 exit;
720 end if;
721
722 x_opt_total := x_opt_total + 1;
723 close c_flowSchedules;
724 end loop;
725
726 end;
727
728 /* After finding the optimzed group of schedules to group together(that has the same
729 assembly, job qty, and required date), make sure the bom exploded at the begin
730 of the required date and at the end of required date are the same, otherwise,
731 this group is not optimzable, because there is likely a component on the BOM that
732 is disabled in the middle of the date.
733 Parameters:
734 All p_* input parameters: used to explode the bom
735 x_comp_sql_tbl: return the exploded bom
736 x_explode_status: status of explosion code
737 x_optimize_status: status indicatign if group can be optimzed, i.e. explode
738 once for the whole group
739 x_msg_data: any error messages
740 */
741 procedure isFlowSchGrpOptimizable(p_organization_id NUMBER,
742 p_assembly_item_id NUMBER,
743 p_assembly_qty NUMBER,
744 p_alt_bom_desig VARCHAR2,
745 p_rev_date DATE,
746 p_project_id NUMBER,
747 p_task_id NUMBER,
748 p_alt_rout_desig VARCHAR2,
749 x_comp_sql_tbl OUT NOCOPY wip_picking_pub.allocate_comp_tbl_t,
750 x_explode_status OUT NOCOPY VARCHAR2,
751 x_optimize_status OUT NOCOPY VARCHAR2,
752 x_msg_data OUT NOCOPY VARCHAR2) IS
753 l_comp_sql_tbl wip_picking_pub.allocate_comp_tbl_t;
754 l_dummy VARCHAR2(1);
755
756 begin
757
758 explodeMultiLevel(p_organization_id => p_organization_id,
759 p_assembly_item_id => p_assembly_item_id,
760 p_alt_option => 1,
761 p_assembly_qty => p_assembly_qty,
762 p_alt_bom_desig => p_alt_bom_desig,
763 p_rev_date => trunc(p_rev_date),
764 p_project_id => p_project_id,
765 p_task_id => p_task_id,
766 -- explode components at all line ops/events
767 p_to_op_seq_num => null,
768 p_alt_rout_desig => p_alt_rout_desig,
769 x_comp_sql_tbl => x_comp_sql_tbl,
770 x_return_status => l_dummy,
771 x_msg_data => x_msg_data);
772 if(l_dummy <> FND_API.G_RET_STS_SUCCESS) then
773 x_explode_status := l_dummy;
774 x_optimize_status := FND_API.G_RET_STS_UNEXP_ERROR;
775 return;
776 end if;
777
778 explodeMultiLevel(p_organization_id => p_organization_id,
779 p_assembly_item_id => p_assembly_item_id,
780 p_alt_option => 1,
781 p_assembly_qty => p_assembly_qty,
782 p_alt_bom_desig => p_alt_bom_desig,
783 p_rev_date => trunc(p_rev_date) + 1,
784 p_project_id => p_project_id,
785 p_task_id => p_task_id,
786 -- explode components at all line ops/events
787 p_to_op_seq_num => null,
788 p_alt_rout_desig => p_alt_rout_desig,
789 x_comp_sql_tbl => l_comp_sql_tbl,
790 x_return_status => l_dummy,
791 x_msg_data => x_msg_data);
792 if(l_dummy <> FND_API.G_RET_STS_SUCCESS) then
793 x_explode_status := l_dummy;
794 x_optimize_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 return;
796 end if;
797
798 x_explode_status := FND_API.G_RET_STS_SUCCESS;
799 if l_comp_sql_tbl.count <> x_comp_sql_tbl.count then
800 x_optimize_status := 'F';
801 end if;
802
803 for i in 1..x_comp_sql_tbl.count LOOP
804 -- compared the exploded bom of the first and last flow sched in the optimized
805 -- group, ie with same assembly
806 if (l_comp_sql_tbl(i).inventory_item_id <> x_comp_sql_tbl(i).inventory_item_id or
807 l_comp_sql_tbl(i).operation_seq_num <> x_comp_sql_tbl(i).operation_seq_num) then
808 x_optimize_status := 'F';
809 -- if not optimizable, return table of components exploded
810 -- from untruncated time/date
811 explodeMultiLevel(p_organization_id => p_organization_id,
812 p_assembly_item_id => p_assembly_item_id,
813 p_alt_option => 1,
814 p_assembly_qty => p_assembly_qty,
815 p_alt_bom_desig => p_alt_bom_desig,
816 p_rev_date => p_rev_date,
817 p_project_id => p_project_id,
818 p_task_id => p_task_id,
819 -- explode components at all line ops/events
820 p_to_op_seq_num => null,
821 p_alt_rout_desig => p_alt_rout_desig,
822 x_comp_sql_tbl => x_comp_sql_tbl,
823 x_return_status => l_dummy,
824 x_msg_data => x_msg_data);
825 return;
826 end if;
827 end loop;
828
829 x_optimize_status := FND_API.G_RET_STS_SUCCESS;
830
831 end;
832
833
834 --this version is used by the form and
835 --called by the conc. request wrapper version
836 procedure allocate(p_alloc_tbl IN OUT NOCOPY wip_picking_pub.allocate_tbl_t,
837 p_days_to_alloc NUMBER := NULL, --only used for rep scheds
838 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
839 p_start_date DATE DEFAULT NULL, /* Enh#2824753 */
840 p_cutoff_date DATE,
841 p_wip_entity_type NUMBER,
842 p_organization_id NUMBER,
843 p_operation_seq_num_low NUMBER := NULL, /* Enh#2824753 */
844 p_operation_seq_num_high NUMBER := NULL,
845 p_pick_grouping_rule_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
846 p_print_pick_slip VARCHAR2 DEFAULT NULL, /* Added as part of Enhancement#2578514*/
847 p_plan_tasks BOOLEAN DEFAULT NULL, /* Added as part of Enhancement#2578514*/
848 x_conc_req_id OUT NOCOPY NUMBER,
849 x_mo_req_number OUT NOCOPY VARCHAR2,
850 x_return_status OUT NOCOPY VARCHAR2,
851 x_msg_data OUT NOCOPY VARCHAR2) IS
852 l_explodeStatus VARCHAR2(1) := fnd_api.g_ret_sts_success;
853 l_explodeMessage VARCHAR2(2000);
854 l_lineCount NUMBER := 0;
855 l_lineCountHolder NUMBER;
856 l_pickStatus VARCHAR2(1) := 'N';
857 l_backflushStatus VARCHAR2(1) := fnd_api.g_ret_sts_success;
858 l_optimizeStatus VARCHAR2(1) := fnd_api.g_ret_sts_success;
859 l_pickSetId NUMBER := 1;
860 l_itemID NUMBER;
861 l_operationSeqNum NUMBER;
862 l_subinv VARCHAR2(10);
863 l_locator NUMBER;
864 l_uom VARCHAR2(4);
865 l_supplyType NUMBER;
866 l_reqDate DATE;
867 l_openQty NUMBER;
868 l_assemblyQty NUMBER;
869 l_linesTable INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
870 l_linesRec INV_MOVE_ORDER_PUB.Trolin_Rec_Type;
871 l_hdrRec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type;
872 l_defaultSub VARCHAR2(10);
873 l_defaultLocator NUMBER;
874 l_disc NUMBER := wip_constants.discrete;
875 l_eam NUMBER := wip_constants.eam;
876 l_lotbased NUMBER := wip_constants.lotbased;
877 l_repetitive NUMBER := wip_constants.repetitive;
878 l_msgCount NUMBER;
879 l_dummy VARCHAR2(1);
880 l_abm VARCHAR2(10);
881 l_revControlCode varchar2(3);
882 l_carton_grouping_id NUMBER :=0;
883 l_string1 VARCHAR2(200);
884 l_string2 VARCHAR2(200);
885 l_flowCompTbl wip_picking_pub.allocate_comp_tbl_t;
886 l_routing_seq_id NUMBER;
887 j NUMBER := 1;
888 h NUMBER;
889 k NUMBER;
890 l_dummy2 VARCHAR2(1);
891 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
892 l_opt_cnt NUMBER := 0;
893 l_include_yield NUMBER; /*Component Yield Enhancement(Bug 4369064)*/
894 x_MOLineErrTbl INV_WIP_Picking_Pvt.Trolin_ErrTbl_type;
895 l_include_comp_yield_factor NUMBER := 1 ;
896
897
898 CURSOR c_discJobs(v_wip_entity_id NUMBER ) IS
899 select wro.operation_seq_num,
900 wro.inventory_item_id,
901 /*Component Yield Enhancement(Bug 4369064)-> Derive quantity to allocate based on yield parameter*/
902 (wro.required_quantity * decode(l_include_yield,2,nvl(wro.component_yield_factor,1),1)
903 - wro.quantity_issued
904 -- - nvl(wro.quantity_allocated, 0)
905 - nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
906 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
907 - nvl(wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY /
908 decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)), 0)) open_quantity,
909 wro.supply_subinventory,
910 wro.supply_locator_id,
911 msi.primary_uom_code,
912 wro.wip_supply_type,
913 wro.date_required,
914 msi.revision_qty_control_code
915
916 from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
917 where wro.wip_entity_id = v_wip_entity_id
918 and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
919 and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
920 and (
921 ( 'Y' = (select allocate_backflush_components
922 from wip_parameters wp
923 where organization_id = wro.organization_id)
924 and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
925 )
926 or wro.wip_supply_type = wip_constants.push
927 )
928 /*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
929 0.00001 (least amount inventory can transfer)*/
930 and (wro.required_quantity - (wro.quantity_issued
931 + nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
932 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) >= 0.00001)
933 and wro.inventory_item_id = msi.inventory_item_id
934 and wro.wip_entity_id = wo.wip_entity_id (+)
935 and ( p_operation_seq_num_low IS NULL OR wro.operation_seq_num >= p_operation_seq_num_low)
936 and ( p_operation_seq_num_high IS NULL OR wro.operation_seq_num <= p_operation_seq_num_high)
937 and wro.operation_seq_num = wo.operation_seq_num (+)
938 and (wo.count_point_type is null or wo.count_point_type in (1,2))
939 and wro.organization_id = msi.organization_id
940 and wro.organization_id = p_organization_id
941 and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
942 for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
943
944 CURSOR c_eamwo(v_wip_entity_id NUMBER) IS
945 select wro.operation_seq_num,
946 wro.inventory_item_id,
947 /*Component Yield Enhancement(Bug 4369064)-> Derive quantity to allocate based on yield parameter*/
948 (wro.required_quantity * decode(l_include_yield,2,nvl(wro.component_yield_factor,1),1)
949 - wro.quantity_issued
950 -- - nvl(wro.quantity_allocated, 0)
951 - nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
952 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
953 - nvl(wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY /
954 decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)), 0)) open_quantity,
955 wro.supply_subinventory,
956 wro.supply_locator_id,
957 msi.primary_uom_code,
958 wro.wip_supply_type,
959 wro.date_required,
960 msi.revision_qty_control_code
961
962 from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
963 where wro.wip_entity_id = v_wip_entity_id
964 and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
965 and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
966 /*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
967 0.00001 (least amount inventory can transfer)*/
968 and (wro.required_quantity - (wro.quantity_issued +
969 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
970 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) >= 0.00001)
971 and wro.inventory_item_id = msi.inventory_item_id
972 and wro.wip_entity_id = wo.wip_entity_id (+)
973 and ( p_operation_seq_num_low IS NULL OR wro.operation_seq_num >= p_operation_seq_num_low) /* Enh#2824753: op seq converted to range for EAM work orders */
974 and ( p_operation_seq_num_high IS NULL OR wro.operation_seq_num <= p_operation_seq_num_high)
975 and wro.operation_seq_num = wo.operation_seq_num (+)
976 and (wo.count_point_type is null or wo.count_point_type in (1,2))
977 and wro.auto_request_material = 'Y'
978 and wro.organization_id = msi.organization_id
979 and wro.organization_id = p_organization_id
980 and wro.wip_supply_type <> wip_constants.bulk
981 and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
982 for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
983 /*Bug 5119650 and 5119601 */
984 /* Modified the calculation of open quantity considering lot basis of component and yield factor*/
985 CURSOR c_lotJobs(v_wip_entity_id NUMBER) IS
986 select wro.operation_seq_num,
987 wro.inventory_item_id,
988 ((decode (nvl(wro.basis_type,wip_constants.item_based_mtl),wip_constants.item_based_mtl,
989 (wo.quantity_in_queue + wo.quantity_running + wo.quantity_completed),1) *
990 wro.quantity_per_assembly /
991 decode(l_include_yield,l_include_comp_yield_factor,nvl(wro.component_yield_factor,1),1))
992 - wro.quantity_issued
993 - nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
994 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) open_quantity,
995 wro.supply_subinventory,
996 wro.supply_locator_id,
997 msi.primary_uom_code,
998 wro.wip_supply_type,
999 wro.date_required,
1000 msi.revision_qty_control_code
1001
1002 from wip_requirement_operations wro, wip_operations wo, mtl_system_items_b msi
1003 where wro.wip_entity_id = v_wip_entity_id
1004 and wro.wip_entity_id = wo.wip_entity_id
1005 and wro.operation_seq_num = wo.operation_seq_num
1006 and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
1007 and ((p_cutoff_date is NULL) OR (wro.date_required <= p_cutoff_date))
1008 and (
1009 ( 'Y' = (select allocate_backflush_components
1010 from wip_parameters wp
1011 where organization_id = wro.organization_id)
1012 and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
1013 )
1014 or wro.wip_supply_type = wip_constants.push
1015 )
1016 -- and (nvl(wro.quantity_allocated,0) + wro.quantity_issued) < ((wo.quantity_in_queue + wo.quantity_running +
1017 -- wo.quantity_completed) * wro.quantity_per_assembly)
1018 /*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
1019 0.00001 (least amount inventory can transfer)*/
1020 and (((wo.quantity_in_queue + wo.quantity_running + wo.quantity_completed) * wro.quantity_per_assembly) -
1021 (wro.quantity_issued +
1022 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1023 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) >= 0.00001)
1024 and wro.inventory_item_id = msi.inventory_item_id
1025 and wro.organization_id = msi.organization_id
1026 and wro.organization_id = p_organization_id
1027 and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
1028 for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
1029
1030 CURSOR c_rep(v_rep_sched_id NUMBER) IS
1031 select wro.operation_seq_num,
1032 wro.inventory_item_id,
1033 /*Component Yield Enhancement(Bug 4369064)-> Derive quantity to allocate based on yield parameter*/
1034 least( --open qty is the smaller of the remaining quantity and the quantity determined by the #days to allocate for
1035 (wro.required_quantity * decode(l_include_yield,2,nvl(wro.component_yield_factor,1),1)
1036 - wro.quantity_issued
1037 - nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1038 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID,WRO. REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
1039 - nvl(wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY /
1040 decode(l_include_yield,2,1,nvl(wro.component_yield_factor,1)),0)),
1041 (wrs.daily_production_rate * wro.quantity_per_assembly * p_days_to_alloc) + nvl(wro.quantity_backordered, 0)) open_quantity,
1042 wro.supply_subinventory,
1043 wro.supply_locator_id,
1044 msi.primary_uom_code,
1045 wro.wip_supply_type,
1046 wro.date_required,
1047 msi.revision_qty_control_code
1048
1049 from wip_requirement_operations wro,
1050 wip_repetitive_schedules wrs,
1051 mtl_system_items_b msi,
1052 wip_operations wo
1053 where wro.repetitive_schedule_id = v_rep_sched_id
1054 and wrs.repetitive_schedule_id = v_rep_sched_id
1055 and ((p_start_date is NULL) OR (wro.date_required >= p_start_date)) /* Enh#2824753 */
1056 and ((p_cutoff_date is null) OR (wro.date_required <= p_cutoff_date))
1057 and (
1058 ( 'Y' = (select allocate_backflush_components
1059 from wip_parameters wp
1060 where organization_id = wro.organization_id)
1061 and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
1062 )
1063 or wro.wip_supply_type = wip_constants.push
1064 )
1065 /*Bug 5255566 (FP Bug 5504661) : Changed below condition to show only components having open quantity greater then
1066 0.00001 (least amount inventory can transfer)*/
1067 and (wro.required_quantity - (wro.quantity_issued +
1068 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1069 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,WRO.QUANTITY_ISSUED),0)) >= 0.00001)
1070 and wro.inventory_item_id = msi.inventory_item_id
1071 and wro.organization_id = msi.organization_id
1072 and wro.organization_id = p_organization_id
1073 and wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
1074 and wro.operation_seq_num = wo.operation_seq_num (+)
1075 and (wo.count_point_type is null or wo.count_point_type in (1,2))
1076 and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
1077 for update of wro.quantity_allocated, wro.quantity_backordered, quantity_issued nowait;
1078
1079 --The following variables are used for performance reasons in order
1080 --to avoid multiple unnecessary cross package calls when creating lines.
1081 l_sysDate DATE := sysdate;
1082 l_backflushTxnType NUMBER := INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR;
1083 l_issueTxnType NUMBER := INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE;
1084 l_push NUMBER := wip_constants.push;
1085 /* Bug 4917429 */
1086 l_jobname VARCHAR2(240);
1087 l_item VARCHAR2(2000);
1088
1089 BEGIN
1090 SAVEPOINT WIP_ALLOCATE_PVT_START;
1091 x_msg_data := null;
1092 select default_pull_supply_subinv, default_pull_supply_locator_id
1093 into l_defaultSub, l_defaultLocator
1094 from wip_parameters
1095 where organization_id = p_organization_id;
1096
1097 /*Component Yield Enhancement(Bug 4369064) -> Fetch the value of component yield parameter*/
1098 select nvl(include_component_yield,1)
1099 into l_include_yield
1100 from wip_parameters
1101 where organization_id = p_organization_id;
1102 if (l_logLevel <= wip_constants.trace_logging) then
1103 wip_logger.log('wip_picking_pvt.allocate => job count in table passed into allocate(): '
1104 || p_alloc_tbl.COUNT, l_dummy2);
1105 wip_logger.log('p_days_to_alloc=' || p_days_to_alloc || ',p_auto_detail_flag=' || p_auto_detail_flag ||
1106 ',p_start_date=' || p_start_date || ',p_cutoff_date=' || p_cutoff_date ||
1107 ',p_wip_entity_type=' || p_wip_entity_type || ',p_organization_id=' || p_organization_id ||
1108 ',p_operation_seq_num_low=' || p_operation_seq_num_low ||
1109 ',p_operation_seq_num_high=' || p_operation_seq_num_high ||
1110 ',p_pick_grouping_rule_id=' || p_pick_grouping_rule_id || ',p_print_pick_slip=' ||
1111 p_print_pick_slip, l_dummy2);
1112 wip_logger.log('wip_picking_pvt.allocate => add comp:', l_dummy2);
1113 end if;
1114
1115 for i in 1..p_alloc_tbl.COUNT LOOP
1116
1117 if(p_wip_entity_type = l_disc) then
1118 open c_discJobs(p_alloc_tbl(i).wip_entity_id);
1119 elsif(p_wip_entity_type = l_eam) then
1120 open c_eamwo(p_alloc_tbl(i).wip_entity_id);
1121 elsif(p_wip_entity_type = l_lotbased) then
1122 open c_lotJobs(p_alloc_tbl(i).wip_entity_id);
1123 elsif(p_wip_entity_type = l_repetitive) then
1124 open c_rep(p_alloc_tbl(i).repetitive_schedule_id);
1125 elsif(p_wip_entity_type = wip_constants.flow) then --flow
1126
1127 if (l_logLevel <= wip_constants.trace_logging) then
1128 wip_logger.log('wip_picking_pvt.allocate => processing job: (wei)'
1129 || p_alloc_tbl(i).wip_entity_id
1130 || ';job counter=' || i || ';total=' || p_alloc_tbl.COUNT, l_dummy2);
1131 end if;
1132
1133
1134 --flow schedules, lock the allocated flag column as to not allocate a flow schedule 2x.
1135 --checking the allocated flag takes place in the flow cursor
1136 if(p_alloc_tbl(i).bill_seq_id is null or p_alloc_tbl(i).bill_org_id is null) then
1137 begin
1138 select a.organization_id,
1139 a.bill_sequence_id,
1140 wfs.primary_item_id,
1141 wfs.alternate_bom_designator,
1142 wfs.alternate_routing_designator,
1143 wfs.SCHEDULED_COMPLETION_DATE,
1144 wfs.planned_quantity
1145 into p_alloc_tbl(i).bill_org_id, p_alloc_tbl(i).bill_seq_id, l_itemID, l_abm,
1146 p_alloc_tbl(i).alt_rtg_dsg, l_reqDate, l_assemblyQty
1147 from bom_bill_of_materials a, bom_bill_of_materials b, wip_flow_schedules wfs
1148 where a.bill_sequence_id = b.common_bill_sequence_id
1149 and b.assembly_item_id = wfs.primary_item_id
1150 and wfs.wip_entity_id = p_alloc_tbl(i).wip_entity_id
1151 and b.organization_id = wfs.organization_id
1152 and ( nvl(b.alternate_bom_designator, 'none') = nvl(wfs.alternate_bom_designator, 'none')
1153 or ( b.alternate_bom_designator IS NULL
1154 and not exists(select 'x'
1155 from bom_bill_of_materials c
1156 where c.assembly_item_id = wfs.primary_item_id
1157 and c.organization_id = wfs.organization_id
1158 and c.alternate_bom_designator = wfs.alternate_bom_designator
1159 )
1160 )
1161 )
1162 for update of wfs.allocated_flag nowait;
1163 exception
1164 when NO_DATA_FOUND then
1165 wip_logger.log('wip_picking_pvt.allocate => ' ||
1166 'no BOM found for wip_entity_id:' ||
1167 p_alloc_tbl(i).wip_entity_id, l_dummy2 );
1168 GOTO END_OF_FOR_LOOP;
1169 end;
1170
1171 begin
1172 select a.routing_sequence_id
1173 into l_routing_seq_id
1174 from bom_operational_routings a, bom_operational_routings b, wip_flow_schedules wfs
1175 where a.routing_sequence_id = b.common_routing_sequence_id
1176 and b.assembly_item_id = wfs.primary_item_id
1177 and wfs.wip_entity_id = p_alloc_tbl(i).wip_entity_id
1178 and b.organization_id = wfs.organization_id
1179 and ( nvl(b.alternate_routing_designator, 'none') = nvl(wfs.alternate_routing_designator, 'none')
1180 or ( b.alternate_routing_designator IS NULL
1181 and not exists(select 'x'
1182 from bom_operational_routings c
1183 where c.assembly_item_id = wfs.primary_item_id
1184 and c.organization_id = wfs.organization_id
1185 and c.alternate_routing_designator = wfs.alternate_routing_designator
1186 )
1187 )
1188 );
1189
1190 exception
1191 when NO_DATA_FOUND then
1192 /* BugFix 6964780: Corrected the wip_logger.log API call */
1193 wip_logger.log('wip_picking_pvt.allocate => no routing found for wip_entity_id:'|| p_alloc_tbl(i).wip_entity_id, l_dummy2 );
1194 end;
1195
1196 -- bug#3409239 Begin: Improve Flow picking performance by grouping
1197 -- if 0, then this schedule is not grouped with the schedule
1198 -- in the previous iteration
1199 p_alloc_tbl(i).required_date := l_reqDate;
1200 if (l_opt_cnt = 0) then
1201 getOptimalFlowSchGrp(p_alloc_tbl => p_alloc_tbl,
1202 p_index => i,
1203 p_item_id => l_itemID,
1204 p_ably_qty => l_assemblyQty,
1205 p_req_date => p_alloc_tbl(i).required_date,
1206 x_opt_total => l_opt_cnt);
1207
1208 if (l_logLevel <= wip_constants.trace_logging) then
1209 wip_logger.log('wip_picking_pvt.allocate => ' ||
1210 'flow optimal group total: ' || l_opt_cnt, l_dummy2);
1211 end if;
1212 -- no flow sched can be grouped together
1213 if (l_opt_cnt < 3) then
1214 l_opt_cnt := 0;
1215 explodeMultiLevel(p_organization_id => p_alloc_tbl(i).bill_org_id,
1216 p_assembly_item_id => l_itemID,
1217 p_alt_option => 1,
1218 p_assembly_qty => l_assemblyQty,
1219 p_alt_bom_desig => l_abm,
1220 p_rev_date => p_alloc_tbl(i).required_date,
1221 p_project_id => p_alloc_tbl(i).project_id,
1222 p_task_id => p_alloc_tbl(i).task_id,
1223 -- explode components at all line ops/events
1224 p_to_op_seq_num => null,
1225 p_alt_rout_desig => p_alloc_tbl(i).alt_rtg_dsg,
1226 x_comp_sql_tbl => l_flowCompTbl,
1227 x_return_status => l_dummy,
1228 x_msg_data => l_explodeMessage);
1229 if(l_dummy <> FND_API.G_RET_STS_SUCCESS) then
1230 l_explodeStatus := l_dummy;
1231 p_alloc_tbl(i).bill_seq_id := null; --reset the bill seq and org as the assy's bill could not be exploded
1232 P_alloc_tbl(i).bill_org_id := null;
1233 end if;
1234 else -- if (l_opt_cnt >= 3)
1235 -- some flow sch can be grouped together
1236
1237 -- check if same bom for the grouped flow schedule
1238 isFlowSchGrpOptimizable(p_organization_id => p_alloc_tbl(i).bill_org_id,
1239 p_assembly_item_id => l_itemID,
1240 p_assembly_qty => l_assemblyQty,
1241 p_alt_bom_desig => l_abm,
1242 p_rev_date => p_alloc_tbl(i).required_date,
1243 p_project_id => p_alloc_tbl(i).project_id,
1244 p_task_id => p_alloc_tbl(i).task_id,
1245 p_alt_rout_desig => p_alloc_tbl(i).alt_rtg_dsg,
1246 x_comp_sql_tbl => l_flowCompTbl,
1247 x_explode_status => l_explodeStatus,
1248 x_optimize_status => l_optimizeStatus,
1249 x_msg_data => l_explodeMessage);
1250 if(l_explodeStatus <> FND_API.G_RET_STS_SUCCESS) then
1251 -- assy's bill could not be exploded, then reset the bill seq and org
1252 l_opt_cnt := 0;
1253 p_alloc_tbl(i).bill_seq_id := null;
1254 P_alloc_tbl(i).bill_org_id := null;
1255 elsif (l_optimizeStatus <> FND_API.G_RET_STS_SUCCESS) then
1256 -- can not optimize, b/c bom is not the same for the group,
1257 -- reset counter, and proceed as normal(one record at a time)
1258 l_opt_cnt := 0;
1259
1260 if (l_logLevel <= wip_constants.trace_logging) then
1261 wip_logger.log('wip_picking_pvt.allocate => ' ||
1262 'flow optimal group NOT optimzable', l_dummy2);
1263 end if;
1264 else
1265 -- can be optimized, do nothing here, it will be processed
1266 -- as a whole right before passing to INV
1267 if (l_logLevel <= wip_constants.trace_logging) then
1268 wip_logger.log('wip_picking_pvt.allocate => ' ||
1269 'flow optimal group IS optimzable', l_dummy2);
1270 end if;
1271 end if;
1272 end if;
1273 else -- if (l_opt_cnt > 0)
1274 -- in the middle of an optimized flow sched group, jump to the end
1275 -- of the jobs loop, since this whole group has been added to
1276 -- l_linesTable to pass to INV
1277 if (l_logLevel <= wip_constants.trace_logging) then
1278 wip_logger.log('wip_picking_pvt.allocate => ' ||
1279 'processing flow optimal group..', l_dummy2);
1280 end if;
1281 l_opt_cnt := l_opt_cnt-1;
1282 GOTO END_OF_FOR_LOOP;
1283 end if; -- bug#3409239 End
1284 else -- if(p_alloc_tbl(i).bill_seq_id is null..
1285 select allocated_flag
1286 into l_dummy
1287 from wip_flow_schedules
1288 where wip_entity_id = p_alloc_tbl(i).wip_entity_id
1289 for update of allocated_flag nowait;
1290 end if;
1291
1292 if(p_alloc_tbl(i).bill_seq_id is null or p_alloc_tbl(i).bill_org_id is null) then
1293 GOTO END_OF_FOR_LOOP; --since the bill could not be exploded, skip it!!!
1294
1295 end if;
1296
1297 else -- if(p_wip_entity_type = l_disc)
1298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1299 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
1300 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pvt.allocate: invalid entity_type' );
1301 x_msg_data := fnd_message.get;
1302 return;
1303 end if;
1304
1305 /* Added as part of Enhancement#2578514*/
1306 select wsh_delivery_group_s.nextval
1307 into l_carton_grouping_id
1308 from dual;
1309
1310 j := 0;
1311
1312 LOOP /*Component Loop*/
1313 if(p_wip_entity_type = l_repetitive) then
1314 fetch c_rep into l_operationSeqNum, l_itemID, l_openQty, l_subinv, l_locator, l_uom, l_supplyType, l_reqDate, l_revControlCode;
1315 exit when c_rep%NOTFOUND;
1316 elsif(p_wip_entity_type = l_disc) then
1317 fetch c_discJobs into l_operationSeqNum, l_itemID, l_openQty, l_subinv, l_locator, l_uom, l_supplyType, l_reqDate,l_revControlCode;
1318 exit when c_discJobs%NOTFOUND;
1319 elsif(p_wip_entity_type = l_eam) then
1320 fetch c_eamwo into l_operationSeqNum, l_itemID, l_openQty, l_subinv, l_locator, l_uom, l_supplyType, l_reqDate,l_revControlCode;
1321 exit when c_eamwo%NOTFOUND;
1322
1323 elsif(p_wip_entity_type = l_lotbased) then
1324 fetch c_lotJobs into l_operationSeqNum, l_itemID, l_openQty, l_subinv, l_locator, l_uom, l_supplyType, l_reqDate,l_revControlCode;
1325 exit when c_lotJobs%NOTFOUND;
1326
1327 elsif(p_wip_entity_type = wip_constants.flow) then --flow
1328 j := j+1;
1329 if (j > l_flowCompTbl.COUNT) then
1330 exit;
1331 end if;
1332
1333 -- Get Line Op
1334 begin
1335 SELECT bos1.operation_seq_num
1336 into l_operationSeqNum
1337 FROM bom_operation_sequences bos1
1338 WHERE bos1.operation_sequence_id = (select bos2.line_op_seq_id from bom_operation_sequences bos2
1339 where bos2.routing_sequence_id = l_routing_seq_id
1340 and bos2.operation_seq_num = l_flowCompTbl(j).operation_seq_num
1341 and bos2.operation_type = 1 );
1342
1343 exception
1344 when NO_DATA_FOUND then
1345 -- Bug#2784239, for event/op seq 1 that's not part of the BOM routing, then the component was added in BOM Bom
1346 -- before routing was created, which defaults op seq/event to 1. In which case, we set op seq num = 1;
1347 l_operationSeqNum := 1;
1348 end;
1349
1350 l_itemID := l_flowCompTbl(j).inventory_item_id;
1351 -- bug#2769993 quantity returned is total open quantity and no longer qty per.
1352 l_openQty := l_flowCompTbl(j).requested_quantity;
1353 l_subinv := l_flowCompTbl(j).source_subinventory_code;
1354 l_locator := l_flowCompTbl(j).source_locator_id;
1355 l_uom := l_flowCompTbl(j).primary_uom_code;
1356 l_revControlCode := l_flowCompTbl(j).revision;
1357 l_supplyType := wip_constants.ASSY_PULL; --although defined as push, flow allocations always behave as pull components
1358 end if;
1359
1360 if (l_logLevel <= wip_constants.trace_logging) then
1361 wip_logger.log(' op_seq_num=' || l_operationSeqNum || ',item_id=' || l_itemID || ',open qty=' || l_openQty
1362 || ',subinv=' || l_subinv || ',loc=' || l_locator || ',supplyType=' || l_supplyType
1363 || ',reqDate=' || l_reqDate, l_dummy2);
1364 end if;
1365
1366 get_HdrLinesRec( p_wip_entity_id => p_alloc_tbl(i).wip_entity_id,
1367 p_project_id => p_alloc_tbl(i).project_id,
1368 p_task_id => p_alloc_tbl(i).task_id,
1369 p_wip_entity_type => p_wip_entity_type,
1370 p_repetitive_schedule_id => p_alloc_tbl(i).repetitive_schedule_id,
1371 p_operation_seq_num => l_operationSeqNum,
1372 p_inventory_item_id => l_itemID,
1373 p_use_pickset_flag => p_alloc_tbl(i).use_pickset_flag,
1374 p_pickset_id =>l_pickSetId,
1375 p_open_qty => l_openQty,
1376 p_to_subinv => l_subinv,
1377 p_to_locator => l_locator,
1378 p_default_subinv => l_defaultSub,
1379 p_default_locator => l_defaultLocator,
1380 p_uom => l_uom,
1381 p_supply_type => l_supplyType,
1382 p_req_date => l_reqDate,
1383 p_rev_control_code =>l_revControlCode ,
1384 p_organization_id => p_organization_id,
1385 p_pick_grouping_rule_id => p_pick_grouping_rule_id,
1386 p_carton_grouping_id => l_carton_grouping_id,
1387 p_hdrRec => l_hdrRec,
1388 p_linesRec => l_linesRec,
1389 x_return_status => x_return_status,
1390 x_msg_data => x_msg_data);
1391
1392 if(x_return_status <> fnd_api.g_ret_sts_success) then
1393 raise FND_API.G_EXC_ERROR;
1394 end if;
1395
1396 --we must do this check rather than supply types as a push component with a supply subinv provided only triggers a
1397 --sub transfer, not a wip issue
1398 if(l_linesRec.to_subinventory_code is null) then
1399 l_linesRec.transaction_type_id := l_issueTxnType;
1400 else
1401 l_linesRec.transaction_type_id := l_backflushTxnType;
1402 end if;
1403 /* Both issue and backflush lines will be stored in the same PLSQL table and INV API is called only once for both types. This change is as part of Enhancement#2578514*/
1404 /* Start Enhancement EAM source-subinventory project for 12.1*/
1405
1406 if (p_wip_entity_type = l_eam) then
1407 l_linesRec.from_subinventory_code := l_subinv;
1408 l_linesRec.from_locator_id := l_locator;
1409 end if;
1410
1411 /* End Enhancement EAM source-subinventory project for 12.1*/
1412
1413 l_lineCount := l_lineCount + 1;
1414 l_linesTable(l_lineCount) := l_linesRec;
1415 end LOOP; --end per component loop
1416 l_pickSetId := l_pickSetId + 1; --increment regardless
1417 --close per entity cursors
1418 if(c_rep%ISOPEN) then
1419 close c_rep;
1420 elsif(c_discJobs%ISOPEN) then
1421 close c_discJobs;
1422 elsif(c_eamwo%ISOPEN) then
1423 close c_eamwo;
1424 elsif(c_lotJobs%ISOPEN) then
1425 close c_lotJobs;
1426 else -- for flow, clear table that stores the results of explodeMultiLevel exploder.
1427 -- bug#3409239 Begin
1428 if l_opt_cnt > 0 then
1429 -- found an optimizable group of l_opt_cnt number of flow schedules
1430 -- add l_linesRec to l_linesTable 'l_opt_cnt' number of times
1431 l_lineCountHolder := l_lineCount;
1432 For h in 1..(l_opt_cnt-1) LOOP
1433 k := l_flowCompTbl.COUNT-1;
1434 LOOP
1435 if k < 0 then
1436 exit;
1437 end if;
1438 l_linesRec := l_linesTable(l_lineCountHolder-k);
1439 l_linesRec.txn_source_id := p_alloc_tbl(i+h).wip_entity_id;
1440 -- required_date currently is not being passed in from picking form
1441 -- so must use the time truncated date of the first flow schedule
1442 -- in the optimized group
1443 --l_linesRec.date_required := p_alloc_tbl(i+h).required_date;
1444 l_linesRec.date_required := trunc(p_alloc_tbl(i).required_date);
1445 l_linesRec.ship_set_id := l_pickSetId+h;
1446 l_lineCount := l_lineCount + 1;
1447 l_linesTable(l_lineCount) := l_linesRec;
1448 k := k-1;
1449 end LOOP;
1450 end LOOP;
1451 l_opt_cnt := l_opt_cnt-1;
1452 end if;
1453 -- bug#3409239 End
1454
1455 l_flowCompTbl.delete;
1456 end if;
1457 <<END_OF_FOR_LOOP>>
1458 null;
1459 end loop; --end per entity loop
1460
1461 if (l_logLevel <= wip_constants.trace_logging) then
1462 wip_logger.log('wip_picking_pvt.allocate => line count passed to inv_wip_picking_pvt.release_pick_batch: '
1463 || l_lineCount || ';total=' || p_alloc_tbl.COUNT, l_dummy2);
1464 end if;
1465 if(l_lineCount > 0) then
1466 l_hdrRec.move_order_type := INV_Globals.G_MOVE_ORDER_MFG_PICK;
1467 inv_wip_picking_pvt.release_pick_batch(p_mo_header_rec => l_hdrRec,
1468 p_mo_line_rec_tbl => l_linesTable,
1469 p_auto_detail_flag => nvl(p_auto_detail_flag,'T'),
1470 p_print_pick_slip => nvl(p_print_pick_slip,'F'),
1471 p_plan_tasks => nvl(p_plan_tasks,FALSE),
1472 x_conc_req_id => x_conc_req_id,
1473 x_return_status => l_pickStatus,
1474 x_msg_data => x_msg_data,
1475 x_msg_count => l_msgCount,
1476 p_init_msg_lst => fnd_api.g_true,
1477 x_mo_line_errrec_tbl => x_MOLineErrTbl);
1478 fnd_file.put_line(which => fnd_file.log, buff => 'return status = ' ||l_pickStatus);
1479
1480 if (l_logLevel <= wip_constants.trace_logging) then
1481 wip_logger.log('returned status from call to inv_wip_picking_pvt.release_pick_batch() = ' || l_pickStatus || ';total=' || p_alloc_tbl.COUNT, l_dummy2);
1482 end if;
1483
1484 if(l_pickStatus not in ('P','N', FND_API.G_RET_STS_SUCCESS)) then
1485 x_return_status := l_pickStatus;
1486 raise FND_API.G_EXC_ERROR;
1487 end if;
1488
1489 --bugfix 4435437
1490 /* if thru srs, print the records that errored out and set the global variable so that we can
1491 set the request to warning. Decided to use global variable instead of a new parameter
1492 to avoid chain-dependencies. */
1493
1494 if (nvl(fnd_global.CONC_REQUEST_ID, -1) <> -1) then
1495 if x_MOLineErrTbl.count > 0 then
1496
1497 WIP_PICKING_PVT.g_PickRelease_Failed := TRUE;
1498 fnd_file.put_line(which => fnd_file.log, buff => 'The errored Records are as follows:');
1499
1500 for i in x_MOLineErrTbl.FIRST..x_MOLineErrTbl.LAST
1501 loop
1502 if( x_MOLineErrTbl.exists(i) ) then
1503 if x_MOLineErrTbl(i).txn_source_id = FND_API.G_MISS_NUM then
1504 x_MOLineErrTbl(i).txn_source_id := null;
1505 end if;
1506 if x_MOLineErrTbl(i).txn_source_line_id = FND_API.G_MISS_NUM then
1507 x_MOLineErrTbl(i).txn_source_line_id := null;
1508 end if;
1509 if x_MOLineErrTbl(i).inventory_item_id = FND_API.G_MISS_NUM then
1510 x_MOLineErrTbl(i).inventory_item_id := null;
1511 end if;
1512 if x_MOLineErrTbl(i).reference_id = FND_API.G_MISS_NUM then
1513 x_MOLineErrTbl(i).reference_id := null;
1514 end if;
1515 /* Bug 4917429 */
1516 if x_MOLineErrTbl(i).organization_id = FND_API.G_MISS_NUM then
1517 x_MOLineErrTbl(i).organization_id := null;
1518 end if;
1519 /* Fix for bug 4917429: Print job name and item number to make
1520 the messages user-friendly */
1521 begin
1522 select we.wip_entity_name, msi.concatenated_segments
1523 into l_jobname, l_item
1524 from wip_entities we, mtl_system_items_vl msi
1525 where we.wip_entity_id = x_MOLineErrTbl(i).txn_source_id
1526 and msi.inventory_item_id = x_MOLineErrTbl(i).inventory_item_id
1527 and msi.organization_id = x_MOLineErrTbl(i).organization_id;
1528 exception
1529 when NO_DATA_FOUND then
1530 l_jobname := NULL;
1531 l_item := NULL;
1532 end;
1533
1534 fnd_file.put_line(
1535 which => fnd_file.log,
1536 buff => 'Wip_Entity_Id:' || x_MOLineErrTbl(i).txn_source_id ||
1537 ' Job Name: ' || l_jobName || /* Bug 4917429 */
1538 ' OpSeqNum: ' || x_MOLineErrTbl(i).txn_source_line_id||
1539 ' Repetitve Schedule Id: ' || x_MOLineErrTbl(i).reference_id||
1540 ' Inventory Item Id : ' || x_MOLineErrTbl(i).inventory_item_id||
1541 ' Item: ' || l_item /* Bug 4917429 */);
1542 fnd_file.put_line(
1543 which => fnd_file.log,
1544 buff => '==>Error:'||x_MOLineErrTbl(i).error_message);
1545 end if;
1546 end loop;
1547 end if;
1548 end if; /* end of conc_req_id <> -1 condition */
1549 end if; /* end of l_lineCount > 0 */
1550
1551 if (l_logLevel <= wip_constants.trace_logging) then
1552 wip_logger.log('wip_picking_pvt.allocate => inv_wip_picking_pvt.release_pick_batch return_status='
1553 || l_pickStatus, l_dummy2);
1554 wip_logger.log('wip_picking_pvt.allocate => inv_wip_picking_pvt.release_pick_batch return_msg='
1555 || x_msg_data, l_dummy2);
1556 end if;
1557
1558 if(nvl(p_print_pick_slip,'T') = 'T') then
1559 fnd_message.set_name('WIP','WIP_PICKING_PRINT_SLIP');
1560 fnd_message.set_token('REQ_NUMBER',x_conc_req_id);
1561 l_string2 := fnd_message.get;
1562 else
1563 l_string2 := ' ';
1564 end if;
1565 if((l_pickStatus = 'P') OR l_explodeStatus <> fnd_api.g_ret_sts_success) then --if either the issue or backflush partially allocated, or one or more flow assemblies' bill could not be exploded
1566 x_return_status := 'P'; --return partial status
1567 fnd_message.set_name('WIP','WIP_PICKING_MO_NUMBER');
1568 fnd_message.set_token('MO_NUMBER',l_hdrRec.request_number);
1569 l_string1 := fnd_message.get;
1570 fnd_message.set_name('WIP', 'WIP_PICKING_PARTIAL_ALLOCATION');
1571 fnd_message.set_token('WIP_PICKING_MO_NUMBER', l_string1);
1572 fnd_message.set_token('WIP_PICKING_PRINT_SLIP', l_string2);
1573 x_msg_data := fnd_message.get;
1574 elsif (l_pickStatus = 'N') then
1575 -- 'N' could also result from l_lineCount = 0, and inv_wip_picking_pvt.release_pick_batch
1576 -- was never called
1577 x_return_status := 'N';
1578 fnd_message.set_name('WIP','WIP_PICKING_NO_ALLOCATION');
1579 x_msg_data := fnd_message.get;
1580 else
1581 x_return_status := fnd_api.g_ret_sts_success; --above ifs test for other return statuses from inv call
1582 fnd_message.set_name('WIP','WIP_PICKING_MO_NUMBER');
1583 fnd_message.set_token('MO_NUMBER',l_hdrRec.request_number);
1584 l_string1 := fnd_message.get;
1585 fnd_message.set_name('WIP','WIP_PICKING_SUCCESS_ALLOCATION');
1586 fnd_message.set_token('WIP_PICKING_MO_NUMBER', l_string1);
1587 fnd_message.set_token('WIP_PICKING_PRINT_SLIP', l_string2);
1588 x_msg_data := fnd_message.get;
1589 end if;
1590 x_mo_req_number := l_hdrRec.request_number; /* Added as part of enhancement 2478446 */
1591
1592 if (l_logLevel <= wip_constants.trace_logging) then
1593 wip_logger.cleanup(l_dummy2);
1594 end if;
1595
1596 exception
1597 when FND_API.G_EXC_ERROR then --msg_data, return_status set by inventory calls
1598 -- ROLLBACK TO WIP_ALLOCATE_PVT_START; -- Fix bug 4341138
1599 if (l_logLevel <= wip_constants.trace_logging) then
1600 wip_logger.log(x_msg_data, l_dummy2);
1601 wip_logger.cleanup(l_dummy2);
1602 end if;
1603 when RECORDS_LOCKED then
1604 -- ROLLBACK TO WIP_ALLOCATE_PVT_START; -- Fix bug 4341138
1605 x_return_status := 'L';
1606 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
1607 x_msg_data := fnd_message.get;
1608 if (l_logLevel <= wip_constants.trace_logging) then
1609 wip_logger.log(x_msg_data, l_dummy2);
1610 wip_logger.cleanup(l_dummy2);
1611 end if;
1612 when others then
1613 -- ROLLBACK TO WIP_ALLOCATE_PVT_START; -- Fix bug 4341138
1614 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1615 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
1616 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pvt.allocate: ' || SQLERRM);
1617 x_msg_data := fnd_message.get;
1618 if (l_logLevel <= wip_constants.trace_logging) then
1619 wip_logger.log(x_msg_data, l_dummy2);
1620 wip_logger.cleanup(l_dummy2);
1621 end if;
1622 end allocate;
1623
1624
1625 /* This procedure is called by allocate to update WRO and fill in header and lines record appropriately */
1626 procedure get_HdrLinesRec( p_wip_entity_id NUMBER,
1627 p_project_id NUMBER,
1628 p_task_id NUMBER,
1629 p_wip_entity_type NUMBER,
1630 p_repetitive_schedule_id NUMBER,
1631 p_operation_seq_num NUMBER,
1632 p_inventory_item_id NUMBER,
1633 p_use_pickset_flag VARCHAR2,
1634 p_pickset_id NUMBER,
1635 p_open_qty NUMBER,
1636 p_to_subinv VARCHAR2,
1637 p_to_locator NUMBER,
1638 p_default_subinv VARCHAR2,
1639 p_default_locator NUMBER,
1640 p_uom VARCHAR2 ,
1641 p_supply_type NUMBER ,
1642 p_req_date DATE,
1643 p_rev_control_code VARCHAR2 ,
1644 p_organization_id NUMBER,
1645 p_pick_grouping_rule_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
1646 p_carton_grouping_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
1647 p_hdrRec IN OUT NOCOPY INV_MOVE_ORDER_PUB.Trohdr_Rec_Type,
1648 p_linesRec IN OUT NOCOPY INV_MOVE_ORDER_PUB.Trolin_Rec_Type,
1649 x_return_status OUT NOCOPY VARCHAR2,
1650 x_msg_data OUT NOCOPY VARCHAR2)
1651
1652 IS
1653
1654 l_sysDate DATE := sysdate;
1655 l_userId NUMBER := fnd_global.user_id;
1656 l_loginId NUMBER := fnd_global.login_id;
1657 l_lineStatus NUMBER := INV_Globals.G_TO_STATUS_PREAPPROVED;
1658 l_push NUMBER := wip_constants.push;
1659 l_pick_grouping_rule_id NUMBER;
1660
1661 BEGIN
1662
1663 SAVEPOINT GET_HDR_LINES_START;
1664 x_msg_data := NULL;
1665 x_return_status := fnd_api.g_ret_sts_success;
1666 p_hdrRec.created_by := l_userId;
1667 p_hdrRec.creation_date := l_sysDate;
1668 p_hdrRec.last_updated_by := l_userId;
1669 p_hdrRec.last_update_date := l_sysDate;
1670 p_hdrRec.last_update_login := l_loginId;
1671 p_hdrRec.organization_id := p_organization_id;
1672 p_hdrRec.operation := INV_Globals.G_OPR_CREATE;
1673 p_hdrRec.header_status := INV_Globals.G_TO_STATUS_PREAPPROVED;
1674 p_linesRec.created_by := l_userId;
1675 p_linesRec.creation_date := l_sysDate;
1676 p_linesRec.last_updated_by := l_userId;
1677 p_linesRec.last_update_date := l_sysDate;
1678 p_linesRec.last_update_login := l_loginId;
1679 p_linesRec.organization_id := p_organization_id;
1680 p_linesRec.line_status := l_lineStatus;
1681 p_linesRec.operation := INV_Globals.G_OPR_CREATE;
1682
1683 /* for EAM entity type, select picksilp grouping rule from wip_parameters. If no rule is defined, raise an exception with suitable message */
1684 if(p_wip_entity_type = wip_constants.eam) then
1685
1686 select pickslip_grouping_rule_id
1687 into l_pick_grouping_rule_id
1688 from wip_parameters
1689 where organization_id = p_organization_id;
1690
1691 if(l_pick_grouping_rule_id IS NULL) then
1692 fnd_message.set_name('WIP','WIP_PICKING_EAM_PICKSLIP_ERROR');
1693 x_msg_data := fnd_message.get;
1694 x_return_status := FND_API.G_RET_STS_ERROR;
1695 raise FND_API.G_EXC_ERROR;
1696 else
1697 p_hdrRec.grouping_rule_id := l_pick_grouping_rule_id;
1698 end if;
1699 else /* for other entity types, get the rule from the parameter passed to allocate() */
1700 p_hdrRec.grouping_rule_id := p_pick_grouping_rule_id;
1701 end if;
1702
1703 /* moved to pre_allocate_material in wippckpb.pls per bug#2642679
1704 if(p_wip_entity_type in(wip_constants.repetitive,wip_constants.discrete,wip_constants.eam,wip_constants.lotbased)) then
1705 if(p_use_pickset_flag = 'N') then
1706 update wip_requirement_operations
1707 set quantity_backordered = p_open_qty,
1708 last_update_date = l_sysDate,
1709 last_updated_by = l_userId,
1710 last_update_login = l_loginId
1711 where inventory_item_id = p_inventory_item_id
1712 and organization_id = p_organization_id
1713 and wip_entity_id = p_wip_entity_id
1714 and operation_seq_num = p_operation_seq_num;
1715 end if;
1716 end if;
1717 */
1718
1719 p_linesRec.txn_source_id := p_wip_entity_id;
1720 p_linesRec.txn_source_line_id := p_operation_seq_num;
1721 p_linesRec.reference_id := p_repetitive_schedule_id;
1722 p_linesRec.date_required := p_req_date;
1723 if(p_wip_entity_type = wip_constants.eam) then
1724 p_linesRec.to_subinventory_code := NULL;
1725 p_linesRec.to_locator_id := NULL;
1726 elsif(p_supply_type = l_push) then --push
1727 p_linesRec.to_subinventory_code := p_to_subinv;
1728 p_linesRec.to_locator_id := p_to_locator;
1729 elsif(p_to_subinv is null) then
1730 p_linesRec.to_subinventory_code := p_default_subinv;
1731 p_linesRec.to_locator_id := p_default_locator;
1732 else
1733 p_linesRec.to_subinventory_code := p_to_subinv;
1734 p_linesRec.to_locator_id := p_to_locator;
1735 end if;
1736 p_linesRec.inventory_item_id := p_inventory_item_id;
1737 if(p_use_pickset_flag = 'Y') then
1738 p_linesRec.ship_set_id := p_pickset_id;
1739 else
1740 p_linesRec.ship_set_id := null;
1741 end if;
1742 p_linesRec.quantity := p_open_qty;
1743 p_linesRec.uom_code := p_uom;
1744 p_linesRec.carton_grouping_id := p_carton_grouping_id; /* Added as part of Enhancement#2578514*/
1745 p_linesRec.project_id := p_project_id;
1746 p_linesRec.task_id := p_task_id;
1747
1748 /* Fix for bug#3683053. get_revision call is commented out.
1749 Now WIP populate null revision in lines record
1750 if(p_rev_control_code = 2) then
1751 Fix for bug 3028470: Passing eco_status as 'EXCLUDE_OPEN_HOLD'
1752 to prevent unimplemented revisions from being transacted
1753 bom_revisions.get_revision(examine_type => 'ALL',
1754 eco_status => 'EXCLUDE_OPEN_HOLD',
1755 org_id => p_organization_id,
1756 item_id => p_inventory_item_id,
1757 rev_date => l_sysDate,
1758 itm_rev => p_linesRec.revision);
1759 else
1760 p_linesRec.revision := null;
1761 end if;
1762 */
1763 p_linesRec.revision := null;
1764 exception
1765
1766 when FND_API.G_EXC_ERROR then /* return status and message_data are set at the place of raising this error */
1767 ROLLBACK TO GET_HDR_LINES_START;
1768 when others then
1769 ROLLBACK TO GET_HDR_LINES_START;
1770 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1771 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
1772 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pvt.get_HdrLinesRec: ' || SQLERRM);
1773 x_msg_data := fnd_message.get;
1774
1775
1776 end get_HdrLinesRec;
1777
1778
1779 --this procedure is used for allocation of specific material
1780
1781 procedure allocate_comp(p_alloc_comp_tbl IN OUT NOCOPY wip_picking_pub.allocate_comp_tbl_t,
1782 p_days_to_alloc NUMBER DEFAULT NULL, --only used for rep scheds
1783 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
1784 p_cutoff_date DATE,
1785 p_wip_entity_type NUMBER,
1786 p_organization_id NUMBER,
1787 p_pick_grouping_rule_id NUMBER := NULL,
1788 p_print_pick_slip VARCHAR2 DEFAULT NULL,
1789 p_plan_tasks BOOLEAN DEFAULT NULL,
1790 x_conc_req_id OUT NOCOPY NUMBER,
1791 x_mo_req_number OUT NOCOPY VARCHAR2,
1792 x_return_status OUT NOCOPY VARCHAR2,
1793 x_msg_data OUT NOCOPY VARCHAR2) IS
1794 l_lineCount NUMBER := 0;
1795 l_pickStatus VARCHAR2(1) := 'N';
1796 l_backflushStatus VARCHAR2(1) := fnd_api.g_ret_sts_success;
1797 l_pickSetId NUMBER := 1;
1798 l_subinv VARCHAR2(10);
1799 l_locator NUMBER;
1800 l_uom VARCHAR2(4);
1801 l_supplyType NUMBER;
1802 l_openQty NUMBER;
1803 l_reqDate DATE;
1804 l_linesTable INV_MOVE_ORDER_PUB.Trolin_Tbl_Type;
1805 l_linesRec INV_MOVE_ORDER_PUB.Trolin_Rec_Type;
1806 l_hdrRec INV_MOVE_ORDER_PUB.Trohdr_Rec_Type;
1807 l_defaultSub VARCHAR2(10);
1808 l_defaultLocator NUMBER;
1809 l_disc NUMBER := wip_constants.discrete;
1810 l_eam NUMBER := wip_constants.eam;
1811 l_lotbased NUMBER := wip_constants.lotbased;
1812 l_repetitive NUMBER := wip_constants.repetitive;
1813 l_msgCount NUMBER;
1814 l_revControlCode VARCHAR2(3);
1815 l_backflushTxnType NUMBER := INV_Globals.G_TYPE_XFER_ORDER_REPL_SUBXFR;
1816 l_issueTxnType NUMBER := INV_Globals.G_TYPE_XFER_ORDER_WIP_ISSUE;
1817 l_push NUMBER := wip_constants.push;
1818 l_carton_grouping_id NUMBER;
1819 TYPE carton_tbl_t IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
1820 carton_tbl carton_tbl_t;
1821 l_string1 VARCHAR2(200);
1822 l_string2 VARCHAR2(200);
1823 x_MOLineErrTbl INV_WIP_Picking_Pvt.Trolin_ErrTbl_type; --bugfix 4435437
1824 BEGIN
1825 SAVEPOINT WIP_ALLOCATE_COMP_PVT_START;
1826 x_msg_data := null;
1827
1828 select default_pull_supply_subinv, default_pull_supply_locator_id
1829 into l_defaultSub, l_defaultLocator
1830 from wip_parameters
1831 where organization_id = p_organization_id;
1832
1833 for i in 1..p_alloc_comp_tbl.COUNT LOOP
1834 if(p_wip_entity_type = l_disc) then
1835 select (wro.required_quantity - wro.quantity_issued
1836 - nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1837 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
1838 - wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY) open_quantity,
1839 wro.supply_subinventory,
1840 wro.supply_locator_id,
1841 msi.primary_uom_code,
1842 wro.wip_supply_type,
1843 wro.date_required,
1844 msi.revision_qty_control_code
1845
1846 into l_openQty,
1847 l_subinv,
1848 l_locator,
1849 l_uom,
1850 l_supplyType,
1851 l_reqDate,
1852 l_revControlCode
1853
1854 from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
1855 where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1856 and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
1857 and (
1858 ( 'Y' = (select allocate_backflush_components
1859 from wip_parameters wp
1860 where organization_id = wro.organization_id)
1861 and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
1862 )
1863 or wro.wip_supply_type = wip_constants.push
1864 )
1865 and wro.required_quantity > (wro.quantity_issued +
1866 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1867 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
1868 and wro.inventory_item_id = p_alloc_comp_tbl(i).inventory_item_id
1869 and wro.inventory_item_id = msi.inventory_item_id
1870 and wro.wip_entity_id = wo.wip_entity_id (+)
1871 and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
1872 and wro.operation_seq_num = wo.operation_seq_num (+)
1873 and wro.organization_id = msi.organization_id
1874 and wro.organization_id = p_organization_id
1875 and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
1876 for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
1877
1878 elsif(p_wip_entity_type = l_eam) then
1879 select (wro.required_quantity - wro.quantity_issued
1880 - nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1881 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
1882 - wo.CUMULATIVE_SCRAP_QUANTITY*wro.QUANTITY_PER_ASSEMBLY) open_quantity,
1883 wro.supply_subinventory,
1884 wro.supply_locator_id,
1885 msi.primary_uom_code,
1886 wro.wip_supply_type,
1887 wro.date_required,
1888 msi.revision_qty_control_code
1889
1890 into l_openQty,
1891 l_subinv,
1892 l_locator,
1893 l_uom,
1894 l_supplyType,
1895 l_reqDate,
1896 l_revControlCode
1897
1898 from wip_requirement_operations wro, mtl_system_items_b msi, wip_operations wo
1899 where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1900 and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
1901 and wro.required_quantity > (wro.quantity_issued +
1902 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1903 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
1904 and wro.inventory_item_id = p_alloc_comp_tbl(i).inventory_item_id
1905 and wro.inventory_item_id = msi.inventory_item_id
1906 and wro.wip_entity_id = wo.wip_entity_id (+)
1907 and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
1908 and wro.operation_seq_num = wo.operation_seq_num (+)
1909 and wro.organization_id = msi.organization_id
1910 and wro.organization_id = p_organization_id
1911 and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
1912 for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
1913
1914 elsif(p_wip_entity_type = l_lotbased) then
1915 select (((wo.quantity_in_queue + wo.quantity_running + wo.quantity_completed) * wro.quantity_per_assembly)
1916 - wro.quantity_issued
1917 - nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1918 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) open_quantity,
1919 wro.supply_subinventory,
1920 wro.supply_locator_id,
1921 msi.primary_uom_code,
1922 wro.wip_supply_type,
1923 wro.date_required,
1924 msi.revision_qty_control_code
1925
1926 into l_openQty,
1927 l_subinv,
1928 l_locator,
1929 l_uom,
1930 l_supplyType,
1931 l_reqDate,
1932 l_revControlCode
1933
1934 from wip_requirement_operations wro, wip_operations wo, mtl_system_items_b msi
1935 where wro.wip_entity_id = p_alloc_comp_tbl(i).wip_entity_id
1936 and wro.wip_entity_id = wo.wip_entity_id
1937 and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
1938 and wro.operation_seq_num = wo.operation_seq_num
1939 and ((p_cutoff_date is NULL) OR (wro.date_required < p_cutoff_date))
1940 and (
1941 ( 'Y' = (select allocate_backflush_components
1942 from wip_parameters wp
1943 where organization_id = wro.organization_id)
1944 and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
1945 )
1946 or wro.wip_supply_type = wip_constants.push
1947 )
1948 and ((wo.quantity_in_queue + wo.quantity_running + wo.quantity_completed) * wro.quantity_per_assembly) > (wro.quantity_issued +
1949 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1950 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
1951 and wro.inventory_item_id = p_alloc_comp_tbl(i).inventory_item_id
1952 and wro.inventory_item_id = msi.inventory_item_id
1953 and wro.organization_id = msi.organization_id
1954 and wro.organization_id = p_organization_id
1955 and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
1956 for update of wro.quantity_allocated, wro.quantity_backordered, wro.quantity_issued nowait;
1957 elsif(p_wip_entity_type = l_repetitive) then
1958
1959 select least( --open qty is the smaller of the remaining quantity and the quantity determined by the #days to allocate for
1960 (wro.required_quantity - wro.quantity_issued
1961 - nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1962 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)
1963 - wo.CUMULATIVE_SCRAP_QUANTITY*wro. QUANTITY_PER_ASSEMBLY),
1964 (wrs.daily_production_rate * wro.quantity_per_assembly * p_days_to_alloc) + nvl(wro.quantity_backordered, 0)) open_quantity,
1965 wro.supply_subinventory,
1966 wro.supply_locator_id,
1967 msi.primary_uom_code,
1968 wro.wip_supply_type,
1969 wro.date_required,
1970 msi.revision_qty_control_code
1971
1972 into l_openQty,
1973 l_subinv,
1974 l_locator,
1975 l_uom,
1976 l_supplyType,
1977 l_reqDate,
1978 l_revControlCode
1979
1980 from wip_requirement_operations wro,
1981 wip_repetitive_schedules wrs,
1982 mtl_system_items_b msi,
1983 wip_operations wo
1984 where wro.repetitive_schedule_id = p_alloc_comp_tbl(i).repetitive_schedule_id
1985 and wrs.repetitive_schedule_id = p_alloc_comp_tbl(i).repetitive_schedule_id
1986 and ((p_cutoff_date is null) OR (wro.date_required < p_cutoff_date))
1987 and (
1988 ( 'Y' = (select allocate_backflush_components
1989 from wip_parameters wp
1990 where organization_id = wro.organization_id)
1991 and wro.wip_supply_type in (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull)
1992 )
1993 or wro.wip_supply_type = wip_constants.push
1994 )
1995 and wro.required_quantity > (wro.quantity_issued +
1996 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
1997 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
1998 and wro.inventory_item_id = p_alloc_comp_tbl(i).inventory_item_id
1999 and wro.inventory_item_id = msi.inventory_item_id
2000 and wro.organization_id = msi.organization_id
2001 and wro.organization_id = p_organization_id
2002 and wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
2003 and wro.operation_seq_num = p_alloc_comp_tbl(i).operation_seq_num
2004 and wro.operation_seq_num = wo.operation_seq_num (+)
2005 and msi.mtl_transactions_enabled_flag = 'Y' /* fix for bug# 2468515 */
2006 for update of wro.quantity_allocated, wro.quantity_backordered, quantity_issued nowait;
2007
2008 end if;
2009 /* Carton grouping id should be same for all components of same entity. It should differ from entity to entity*/
2010 if(carton_tbl.EXISTS(p_alloc_comp_tbl(i).wip_entity_id)) then
2011 l_carton_grouping_id := carton_tbl(p_alloc_comp_tbl(i).wip_entity_id);
2012 else
2013 select wsh_delivery_group_s.nextval
2014 into l_carton_grouping_id
2015 from dual;
2016 carton_tbl(p_alloc_comp_tbl(i).wip_entity_id) := l_carton_grouping_id;
2017 end if;
2018
2019 get_HdrLinesRec( p_wip_entity_id => p_alloc_comp_tbl(i).wip_entity_id,
2020 p_project_id => p_alloc_comp_tbl(i).project_id,
2021 p_task_id => p_alloc_comp_tbl(i).task_id,
2022 p_wip_entity_type => p_wip_entity_type,
2023 p_repetitive_schedule_id => p_alloc_comp_tbl(i).repetitive_schedule_id,
2024 p_operation_seq_num => p_alloc_comp_tbl(i).operation_seq_num,
2025 p_inventory_item_id => p_alloc_comp_tbl(i).inventory_item_id,
2026 p_use_pickset_flag => p_alloc_comp_tbl(i).use_pickset_flag,
2027 p_pickset_id =>l_pickSetId,
2028 p_open_qty => l_openQty,
2029 p_to_subinv => l_subinv,
2030 p_to_locator => l_locator,
2031 p_default_subinv => l_defaultSub,
2032 p_default_locator => l_defaultLocator,
2033 p_uom => l_uom,
2034 p_supply_type => l_supplyType,
2035 p_req_date => l_reqDate,
2036 p_rev_control_code =>l_revControlCode ,
2037 p_organization_id => p_organization_id,
2038 p_pick_grouping_rule_id => p_pick_grouping_rule_id,
2039 p_carton_grouping_id => l_carton_grouping_id,
2040 p_hdrRec => l_hdrRec,
2041 p_linesRec => l_linesRec,
2042 x_return_status => x_return_status,
2043 x_msg_data => x_msg_data);
2044 if(x_return_status <> fnd_api.g_ret_sts_success) then
2045 raise FND_API.G_EXC_ERROR;
2046 end if;
2047 if (p_alloc_comp_tbl(i).requested_quantity IS NOT NULL) then
2048 if(p_alloc_comp_tbl(i).requested_quantity > l_linesRec.quantity) then /* Do not allow overpick */
2049 fnd_message.set_name('WIP','WIP_PICKING_OVERPICK_ERROR');
2050 x_msg_data := fnd_message.get;
2051 x_return_status := FND_API.G_RET_STS_ERROR;
2052 raise FND_API.G_EXC_ERROR;
2053 else
2054 l_linesRec.quantity := p_alloc_comp_tbl(i).requested_quantity;
2055 end if;
2056
2057 end if;
2058
2059 l_linesRec.from_subinventory_code := p_alloc_comp_tbl(i).source_subinventory_code;
2060 l_linesRec.from_locator_id := p_alloc_comp_tbl(i).source_locator_id;
2061 l_linesRec.lot_number := p_alloc_comp_tbl(i).lot_number;
2062 l_linesRec.serial_number_start := p_alloc_comp_tbl(i).start_serial;
2063 l_linesRec.serial_number_end := p_alloc_comp_tbl(i).end_serial;
2064
2065 --we must do this check rather than supply types as a push component with a supply subinv provided only triggers a
2066 --sub transfer, not a wip issue
2067 if(l_linesRec.to_subinventory_code is null) then
2068 l_linesRec.transaction_type_id := l_issueTxnType;
2069 else
2070 l_linesRec.transaction_type_id := l_backflushTxnType;
2071 end if;
2072 /* Both issue and backflush lines will stored in the same PLSQL table and INV API is called only once for both types. This change is as part of Enhancement#2578514*/
2073 l_lineCount := l_lineCount + 1;
2074 l_linesTable(l_lineCount) := l_linesRec;
2075 l_pickSetId := l_pickSetId + 1; --increment regardless
2076 end loop;
2077
2078 if(l_lineCount > 0) then
2079 l_hdrRec.move_order_type := INV_Globals.G_MOVE_ORDER_MFG_PICK;
2080 inv_wip_picking_pvt.release_pick_batch(p_mo_header_rec => l_hdrRec,
2081 p_mo_line_rec_tbl => l_linesTable,
2082 p_auto_detail_flag => nvl(p_auto_detail_flag,'T'),
2083 p_print_pick_slip => nvl(p_print_pick_slip,'F'),
2084 p_plan_tasks => nvl(p_plan_tasks,FALSE),
2085 x_conc_req_id => x_conc_req_id,
2086 x_return_status => l_pickStatus,
2087 x_msg_data => x_msg_data,
2088 x_msg_count => l_msgCount,
2089 p_init_msg_lst => fnd_api.g_true,
2090 x_mo_line_errrec_tbl => x_MOLineErrTbl); --bugfix 4435437
2091
2092 if(l_pickStatus not in ('P','N',FND_API.G_RET_STS_SUCCESS)) then
2093 x_return_status := l_pickStatus;
2094 raise FND_API.G_EXC_ERROR;
2095 end if;
2096 end if;
2097
2098 if(nvl(p_print_pick_slip,'T') = 'T') then
2099 fnd_message.set_name('WIP','WIP_PICKING_PRINT_SLIP');
2100 fnd_message.set_token('REQ_NUMBER',x_conc_req_id);
2101 l_string2 := fnd_message.get;
2102 else
2103 l_string2 := ' ';
2104 end if;
2105
2106 if(l_pickStatus = 'P') then --if either the issue or backflush partially allocated
2107 x_return_status := 'P'; --return partial status
2108 fnd_message.set_name('WIP','WIP_PICKING_MO_NUMBER');
2109 fnd_message.set_token('MO_NUMBER',l_hdrRec.request_number);
2110 l_string1 := fnd_message.get;
2111 fnd_message.set_name('WIP', 'WIP_PICKING_PARTIAL_ALLOCATION');
2112 fnd_message.set_token('WIP_PICKING_MO_NUMBER', l_string1);
2113 fnd_message.set_token('WIP_PICKING_PRINT_SLIP', l_string2);
2114 x_msg_data := fnd_message.get;
2115 elsif (l_pickStatus = 'N') then
2116 -- 'N' could also result from l_lineCount = 0, and inv_wip_picking_pvt.release_pick_batch
2117 -- was never called
2118 x_return_status := 'N';
2119 fnd_message.set_name('WIP','WIP_PICKING_NO_ALLOCATION');
2120 x_msg_data := fnd_message.get;
2121 else
2122 x_return_status := fnd_api.g_ret_sts_success; --above ifs test for other return statuses from inv call
2123 fnd_message.set_name('WIP','WIP_PICKING_MO_NUMBER');
2124 fnd_message.set_token('MO_NUMBER',l_hdrRec.request_number);
2125 l_string1 := fnd_message.get;
2126 fnd_message.set_name('WIP','WIP_PICKING_SUCCESS_ALLOCATION');
2127 fnd_message.set_token('WIP_PICKING_MO_NUMBER', l_string1);
2128 fnd_message.set_token('WIP_PICKING_PRINT_SLIP', l_string2);
2129 x_msg_data := fnd_message.get;
2130 end if;
2131 x_mo_req_number := l_hdrRec.request_number; /* Added as part of enhancement 2478446 */
2132 exception
2133 when FND_API.G_EXC_ERROR then --msg_data, return_status set by inventory calls
2134 ROLLBACK TO WIP_ALLOCATE_COMP_PVT_START;
2135 when RECORDS_LOCKED then
2136 ROLLBACK TO WIP_ALLOCATE_COMP_PVT_START;
2137 x_return_status := 'L';
2138 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
2139 x_msg_data := fnd_message.get;
2140 when others then
2141 ROLLBACK TO WIP_ALLOCATE_COMP_PVT_START;
2142 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2143 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
2144 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pvt.allocate: ' || SQLERRM);
2145 x_msg_data := fnd_message.get;
2146 end allocate_comp;
2147
2148 --this version is used in the concurrent request
2149 --note the commit at the end of this procedure
2150 procedure allocate(errbuf OUT NOCOPY VARCHAR2,
2151 retcode OUT NOCOPY NUMBER,
2152 p_wip_entity_type NUMBER,
2153 p_job_type NUMBER DEFAULT 4, /*Bug 5932126 (FP of 5880558): Added one new parameter for job type*/
2154 p_days_forward NUMBER,
2155 p_organization_id NUMBER,
2156 p_use_pickset_indicator NUMBER,
2157 p_pick_grouping_rule_id NUMBER := NULL, /* Added as part of Enhancement#2578514*/
2158 p_print_pickslips NUMBER DEFAULT NULL, /* lookup code is 1 for default YES. This parameter is added as part of Enhancement#2578514*/
2159 p_plan_tasks NUMBER DEFAULT NULL, /* lookup code is 2 for default NO. This parameter is added as part of Enhancement#2578514*/
2160 p_days_to_alloc NUMBER := NULL) --only used for rep scheds
2161 IS
2162
2163
2164
2165 CURSOR c_jobs is
2166 select distinct we.wip_entity_id,
2167 we.wip_entity_name,
2168 wdj.project_id,
2169 wdj.task_id
2170 from wip_entities we,
2171 wip_discrete_jobs wdj,
2172 wip_requirement_operations wro,
2173 wip_parameters wp,
2174 wip_operations wo
2175 where we.wip_entity_id = wdj.wip_entity_id
2176 and we.entity_type = 1
2177 and wdj.status_type in (3,4)
2178 and wdj.job_type = decode(p_job_type, 4, wdj.job_type, p_job_type) /*Bug 5932126 (FP of 5880558): Added to check job type*/
2179 and wro.wip_entity_id = we.wip_entity_id
2180 and wp.organization_id = we.organization_id
2181 and wro.wip_entity_id = wo.wip_entity_id (+)
2182 and wro.operation_seq_num = wo.operation_seq_num(+)
2183 and (wo.count_point_type in (1,2) or wo.count_point_type is null)
2184 and wro.required_quantity > (wro.quantity_issued +
2185 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
2186 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
2187 and ( ( wp.allocate_backflush_components = 'Y' and wro.wip_supply_type in (1,2,3) ) or ( ( wp.allocate_backflush_components = 'N' or wp.allocate_backflush_components is null )
2188 and wro.wip_supply_type = 1 ) )
2189 and wro.date_required < sysdate + p_days_forward
2190 and we.organization_id = p_organization_id
2191 order by we.wip_entity_name;
2192
2193
2194 CURSOR c_lotjobs is
2195 select distinct we.wip_entity_id,
2196 we.wip_entity_name,
2197 wdj.project_id,
2198 wdj.task_id
2199 from wip_entities we,
2200 wip_discrete_jobs wdj,
2201 wip_requirement_operations wro,
2202 wip_parameters wp,
2203 wip_operations wo
2204 where wo.wip_entity_id = we.wip_entity_id
2205 and wo.operation_seq_num = wro.operation_seq_num
2206 and we.wip_entity_id = wdj.wip_entity_id
2207 and we.entity_type = 5
2208 and wdj.status_type in (3,4)
2209 and wro.wip_entity_id = we.wip_entity_id
2210 and wp.organization_id = we.organization_id
2211 and (wo.quantity_in_queue + quantity_running +
2212 wo.quantity_waiting_to_move + wo.quantity_rejected +
2213 wo.quantity_scrapped + wo.quantity_completed) *
2214 wro.quantity_per_assembly > wro.quantity_issued
2215 + nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
2216 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED), 0)
2217 and (
2218 ( wp.allocate_backflush_components = 'Y'
2219 and wro.wip_supply_type in (1,2,3)
2220 )
2221 or (
2222 (wp.allocate_backflush_components = 'N' or wp.allocate_backflush_components is null)
2223 and wro.wip_supply_type = 1
2224 )
2225 )
2226 and wro.date_required < sysdate + p_days_forward
2227 and we.organization_id = p_organization_id
2228 order by we.wip_entity_name;
2229
2230 CURSOR c_rep is
2231 select distinct we.wip_entity_id,
2232 (wl.line_code || ':' || msik.concatenated_segments || ':' || wrs.first_unit_start_date ) title,
2233 wrs.repetitive_schedule_id
2234 from wip_entities we,
2235 mtl_system_items_kfv msik,
2236 wip_repetitive_schedules wrs,
2237 wip_repetitive_items wri,
2238 wip_requirement_operations wro,
2239 wip_parameters wp,
2240 wip_lines wl,
2241 wip_operations wo
2242 where we.wip_entity_id = wrs.wip_entity_id
2243 and wrs.repetitive_schedule_id = wro.repetitive_schedule_id
2244 and we.wip_entity_id = wri.wip_entity_id
2245 and we.primary_item_id = msik.inventory_item_id
2246 and wrs.status_type in (3,4)
2247 and wrs.line_id = wl.line_id
2248 and wri.line_id = wl.line_id
2249 and msik.organization_id = we.organization_id
2250 and wro.wip_entity_id = we.wip_entity_id
2251 and wp.organization_id = we.organization_id
2252 and wro.wip_entity_id = wo.wip_entity_id (+)
2253 and wro.repetitive_schedule_id = wo.repetitive_schedule_id (+)
2254 and wro.operation_seq_num = wo.operation_seq_num(+)
2255 and (wo.count_point_type in (1,2) or wo.count_point_type is null)
2256 and wro.required_quantity > (wro.quantity_issued +
2257 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
2258 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0))
2259 and ( ( wp.allocate_backflush_components = 'Y' and wro.wip_supply_type in (1,2,3) ) or ( ( wp.allocate_backflush_components = 'N' or wp.allocate_backflush_components is null ) and wro.wip_supply_type = 1 ) )
2260 and wro.date_required < sysdate + p_days_forward
2261 and we.organization_id = p_organization_id
2262 order by title;
2263
2264 /* BugFix 6964780: Modified the query */
2265 CURSOR c_flow is
2266 select wip_entity_id,
2267 (line_code || ':' || primary_item_id || ':' || schedule_number) title,
2268 project_id,
2269 task_id
2270 from wip_flow_open_allocations_v
2271 where scheduled_start_date < sysdate + p_days_forward
2272 and organization_id = p_organization_id
2273 order by title;
2274
2275 l_wipEntityID NUMBER;
2276 l_repSchedID NUMBER;
2277 l_projectID NUMBER;
2278 l_taskID NUMBER;
2279 i NUMBER := 0;
2280 l_allocTbl wip_picking_pub.allocate_tbl_t;
2281 l_returnStatus VARCHAR2(1);
2282 l_msgData VARCHAR2(2000);
2283 l_picksetFlag VARCHAR2(1);
2284 l_outBuffer VARCHAR2(240);
2285 l_conc_req_id NUMBER;
2286 l_msgCount NUMBER := 0;
2287 l_mo_req_number VARCHAR2(30);
2288 l_print_pickslips varchar2(1);
2289 l_plan_tasks BOOLEAN;
2290
2291 BEGIN
2292
2293 retcode := 0;
2294 savepoint wip_allocate_concurrent;
2295
2296 if(p_wip_entity_type = wip_constants.discrete) then
2297 open c_jobs;
2298 elsif(p_wip_entity_type = wip_constants.lotbased) then
2299 open c_lotjobs;
2300 elsif(p_wip_entity_type = wip_constants.repetitive) then
2301 open c_rep;
2302 else
2303 open c_flow;
2304 end if;
2305
2306 if(p_use_pickset_indicator = 2) then
2307 l_picksetFlag := 'N';
2308 else
2309 l_picksetFlag := 'Y';
2310 end if;
2311 if((p_print_pickslips IS NULL) OR (p_print_pickslips = 1)) then
2312 l_print_pickslips:= 'T';
2313 else
2314 l_print_pickslips:='F';
2315 end if;
2316
2317 if (p_plan_tasks = 1) then
2318 l_plan_tasks:= TRUE;
2319 else
2320 l_plan_tasks:= FALSE;
2321 end if;
2322
2323 fnd_message.set_name('FND', 'CONC-PARAMETERS');
2324 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2325
2326
2327 select meaning
2328 into l_outBuffer
2329 from mfg_lookups
2330 where lookup_type = 'SYS_YES_NO'
2331 and lookup_code = p_use_pickset_indicator;
2332
2333 fnd_message.set_name('WIP', 'WIP_PICKING_USE_PICKSETS');
2334 fnd_file.put_line(which => fnd_file.output, buff => ' ' || fnd_message.get || ' ' || l_outBuffer);
2335 /* Added as part of Enhancement#2578514*/
2336 select meaning
2337 into l_outBuffer
2338 from mfg_lookups
2339 where lookup_type = 'SYS_YES_NO'
2340 and lookup_code = p_print_pickslips;
2341
2342 fnd_message.set_name('WIP', 'WIP_PICKING_PRINT_PICKSLIPS');
2343 fnd_file.put_line(which => fnd_file.output, buff => ' ' || fnd_message.get || ' ' || l_outBuffer);
2344 /* Added as part of Enhancement#2578514*/
2345 if(p_pick_grouping_rule_id IS NOT NULL) then
2346 select name
2347 into l_outBuffer
2348 from wsh_pick_grouping_rules
2349 where pick_grouping_rule_id = p_pick_grouping_rule_id;
2350
2351 fnd_message.set_name('WIP', 'WIP_PICKING_PICK_GROUPING_RULE');
2352 fnd_file.put_line(which => fnd_file.output, buff => ' ' || fnd_message.get || ' ' || l_outBuffer);
2353 end if;
2354
2355 /* Added as part of Enhancement#2578514*/
2356 select meaning
2357 into l_outBuffer
2358 from mfg_lookups
2359 where lookup_type = 'SYS_YES_NO'
2360 and lookup_code = p_plan_tasks;
2361
2362 fnd_message.set_name('WIP', 'WIP_PICKING_PLAN_TASKS');
2363 fnd_file.put_line(which => fnd_file.output, buff => ' ' || fnd_message.get || ' ' || l_outBuffer);
2364
2365 /* Fixed bug 4890898 Changed table from org_organization_definitions to MTL_PARAMETERS */
2366 select organization_code
2367 into l_outBuffer
2368 from MTL_PARAMETERS
2369 where organization_id = p_organization_id;
2370
2371 fnd_message.set_name('WIP', 'WIP_PICKING_ORG');
2372 fnd_file.put_line(which => fnd_file.output, buff => ' ' || fnd_message.get || ' ' || l_outBuffer);
2373
2374 fnd_message.set_name('WIP', 'WIP_PICKING_DAYS_FORWARD');
2375 fnd_file.put_line(which => fnd_file.output, buff => ' ' || fnd_message.get || ' ' || p_days_forward);
2376
2377 select meaning
2378 into l_outBuffer
2379 from mfg_lookups
2380 where lookup_type = 'WIP_ENTITY'
2381 and lookup_code = p_wip_entity_type;
2382
2383 fnd_message.set_name('WIP', 'WIP_PICKING_MFG_MODE');
2384 fnd_file.put_line(which => fnd_file.output, buff => ' ' || fnd_message.get || ' ' || l_outBuffer);
2385
2386 /* Bug 6046963: Added below if to print job type parameter in case of Discrete Job Comp Pick Release request only*/
2387 if(p_wip_entity_type = wip_constants.discrete) then
2388 /*Start - Bug 5932126 (FP of 5880558): Added following code to print new parameter for job type*/
2389 select meaning
2390 into l_outBuffer
2391 from mfg_lookups
2392 where lookup_type = 'WIP_ENTITIES'
2393 and lookup_code = p_job_type ;
2394
2395 fnd_message.set_name('WIP', 'WIP_PICKING_JOB_TYPE');
2396 fnd_file.put_line(which => fnd_file.output, buff => ' ' || fnd_message.get || ' ' || l_outBuffer);
2397 /*End - Bug 5932126 (FP of 5880558): Added code to print new parameter for job type*/
2398 end if;
2399
2400 fnd_file.put_line(which => fnd_file.output, buff => '');
2401 fnd_file.put_line(which => fnd_file.output, buff => '');
2402
2403 LOOP
2404 if(p_wip_entity_type = wip_constants.discrete) then
2405 fetch c_jobs into l_wipEntityID, l_outBuffer, l_projectID, l_taskID;
2406 exit when c_jobs%NOTFOUND;
2407 elsif(p_wip_entity_type = wip_constants.lotbased) then
2408 fetch c_lotjobs into l_wipEntityID, l_outBuffer, l_projectID, l_taskID;
2409 exit when c_lotjobs%NOTFOUND;
2410 elsif(p_wip_entity_type = wip_constants.repetitive) then
2411 fetch c_rep into l_wipEntityID, l_outBuffer, l_repSchedID;
2412 exit when c_rep%NOTFOUND;
2413 else
2414 fetch c_flow into l_wipEntityID, l_outBuffer, l_projectID, l_taskID;
2415 exit when c_flow%NOTFOUND;
2416 end if;
2417 fnd_file.put_line(which => fnd_file.output, buff => l_outBuffer);
2418 i := i + 1;
2419 l_allocTbl(i).wip_entity_id := l_wipEntityID;
2420 l_allocTbl(i).repetitive_schedule_id := l_repSchedID;
2421 l_allocTbl(i).project_id := l_projectID;
2422 l_allocTbl(i).task_id := l_taskID;
2423 l_allocTbl(i).use_pickset_flag := l_picksetFlag;
2424 end loop;
2425
2426 if(c_jobs%ISOPEN) then
2427 close c_jobs;
2428 elsif(c_lotjobs%ISOPEN) then
2429 close c_lotjobs;
2430 elsif(c_rep%ISOPEN) then
2431 close c_rep;
2432 elsif(c_flow%ISOPEN) then
2433 close c_flow;
2434 end if;
2435
2436 fnd_file.put_line(which => fnd_file.output, buff => 'Total job count: ' || i);
2437 if(i > 0) then
2438 allocate(p_alloc_tbl => l_allocTbl,
2439 p_days_to_alloc => p_days_to_alloc, --not null only for rep scheds
2440 p_cutoff_date => sysdate + p_days_forward,
2441 p_wip_entity_type => p_wip_entity_type,
2442 p_organization_id => p_organization_id,
2443 p_pick_grouping_rule_id => p_pick_grouping_rule_id, /* Added as part of Enhancement#2578514*/
2444 p_print_pick_slip => l_print_pickslips, /* Added as part of Enhancement#2578514*/
2445 p_plan_tasks => l_plan_tasks, /* Added as part of Enhancement#2578514*/
2446 x_conc_req_id => l_conc_req_id,
2447 x_mo_req_number => l_mo_req_number,
2448 x_return_status => l_returnStatus,
2449 x_msg_data => errbuf);
2450
2451 if(l_returnStatus = 'P') then
2452 fnd_message.set_name('WIP','WIP_PICKING_MO_NUMBER');
2453 fnd_message.set_token('MO_NUMBER',l_mo_req_number);
2454 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2455 fnd_message.set_name('WIP', 'WIP_PICKING_PARTIAL_ALLOC_CONC');
2456 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2457 if(l_print_pickslips = 'T') then
2458 fnd_message.set_name('WIP','WIP_PICKING_PRINT_SLIP');
2459 fnd_message.set_token('REQ_NUMBER',l_conc_req_id);
2460 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2461 end if;
2462
2463 elsif(l_returnStatus = 'S') then
2464 fnd_message.set_name('WIP','WIP_PICKING_MO_NUMBER');
2465 fnd_message.set_token('MO_NUMBER',l_mo_req_number);
2466 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2467 if(l_print_pickslips = 'T') then
2468 fnd_message.set_name('WIP','WIP_PICKING_PRINT_SLIP');
2469 fnd_message.set_token('REQ_NUMBER',l_conc_req_id);
2470 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2471 end if;
2472 fnd_message.set_name('WIP', 'WIP_TXN_COMPLETED');
2473 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2474
2475 elsif(l_returnStatus = 'N') then
2476 fnd_message.set_name('WIP','WIP_PICKING_NO_ALLOCATION_CONC');
2477 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2478
2479 else
2480 fnd_file.put_line(which => fnd_file.output, buff => '');
2481 fnd_message.set_name('WIP', 'ERROR_DIALOG_TITLE');
2482 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2483 fnd_file.put_line(which => fnd_file.output,
2484 buff => 'Returned status: ' || l_returnStatus);
2485 fnd_file.put_line(which => fnd_file.output,
2486 buff => 'Returned message: ' || errbuf);
2487 fnd_file.put_line(which => fnd_file.output,
2488 buff => 'Parameters passed to allocate():');
2489 fnd_file.put_line(which => fnd_file.output, buff => 'p_days_to_alloc=' ||
2490 p_days_to_alloc || ';p_days_forward=' || p_days_forward ||
2491 ';p_wip_entity_type=' || p_wip_entity_type || ';p_organization_id=' ||
2492 p_organization_id || ';p_pick_grouping_rule_id=' || p_pick_grouping_rule_id
2493 || ';l_print_pickslips=' || l_print_pickslips );
2494 end if;
2495 end if;
2496 /*bugfix 4435437: set the process to warning if failed */
2497 if (WIP_PICKING_PVT.g_PickRelease_Failed = TRUE) then
2498 retcode := 1 ;
2499 end if;
2500 commit;
2501 exception
2502 when others then
2503 errbuf := SQLERRM;
2504 retcode := 2;
2505 fnd_file.put_line(which => fnd_file.output, buff => '');
2506 fnd_file.put_line(which => fnd_file.output, buff => '');
2507 fnd_message.set_name('WIP', 'ERROR_DIALOG_TITLE');
2508 fnd_file.put_line(which => fnd_file.output, buff => fnd_message.get);
2509 for i in 1..l_msgCount loop
2510 fnd_file.put_line(which => fnd_file.output, buff => fnd_msg_pub.get(p_msg_index => l_msgCount - i + 1, p_encoded => fnd_api.g_false));
2511 end loop;
2512 rollback to wip_allocate_concurrent;
2513 end allocate;
2514
2515
2516 /**
2517 * Explodes an item's bom and returns the components in a pl/sql table
2518 * p_organization_id The organization.
2519 * p_assembly_item_id The assembly.
2520 * p_alt_option 2 if an exact match to the alternate bom designator is necessary
2521 * 1 if the alternate is not found, the main bom will be used.
2522 * p_assembly_qty Qty to explode. Pass a negative value for returns.
2523 * p_alt_bom_desig The alternate bom designator if one was provided. Null otherwise.
2524 * p_rev_date The date of the transaction. This is used to retrieve the correct bom.
2525 */
2526 procedure explodeMultiLevel(p_organization_id NUMBER,
2527 p_assembly_item_id NUMBER,
2528 p_alt_option NUMBER,
2529 p_assembly_qty NUMBER,
2530 p_alt_bom_desig VARCHAR2,
2531 p_rev_date DATE,
2532 p_project_id NUMBER,
2533 p_task_id NUMBER,
2534 p_to_op_seq_num NUMBER,
2535 p_alt_rout_desig VARCHAR2,
2536 x_comp_sql_tbl OUT NOCOPY wip_picking_pub.allocate_comp_tbl_t,
2537 x_return_status OUT NOCOPY VARCHAR2,
2538 x_msg_data OUT NOCOPY VARCHAR2) IS
2539 l_comp_sys_rec SYSTEM.WIP_COMPONENT_OBJ_T;
2540 l_comp_sys_table SYSTEM.WIP_COMPONENT_TBL_T;
2541 l_comp_sql_rec wip_picking_pub.allocate_comp_rec_t;
2542 i NUMBER;
2543 j NUMBER;
2544 begin
2545
2546 wip_flowUtil_priv.explodeRequirementsAndDefault(p_assyID => p_assembly_item_id,
2547 p_orgID => p_organization_id,
2548 p_qty => p_assembly_qty,
2549 p_altBomDesig => p_alt_bom_desig,
2550 p_altOption => p_alt_option,
2551 p_txnDate => p_rev_date,
2552 p_implFlag => 1,
2553 p_projectID => p_project_id,
2554 p_taskID => p_task_id,
2555 p_toOpSeqNum => p_to_op_seq_num,
2556 p_altRoutDesig => p_alt_rout_desig,
2557 /* fix for bug 4538135*/ p_txnFlag => true, /* ER 4369064 */
2558 x_compTbl => l_comp_sys_table,
2559 x_returnStatus => x_return_status);
2560
2561 if(x_return_status <> FND_API.G_RET_STS_SUCCESS) then
2562 wip_utilities.get_message_stack(p_msg => x_msg_data,
2563 p_separator => ' ',
2564 p_delete_stack => fnd_api.g_true);
2565 return;
2566 end if;
2567
2568 i := l_comp_sys_table.FIRST; -- get subscript of first element
2569 j := 1;
2570 WHILE i IS NOT NULL LOOP
2571 l_comp_sys_rec := l_comp_sys_table(i);
2572 --all to pl/sql table
2573 if (l_comp_sys_rec.wip_supply_type = wip_constants.push) then
2574 l_comp_sql_rec.operation_seq_num := l_comp_sys_rec.operation_seq_num ;
2575 l_comp_sql_rec.inventory_item_id := l_comp_sys_rec.inventory_item_id ;
2576 l_comp_sql_rec.requested_quantity := l_comp_sys_rec.primary_quantity ;
2577 l_comp_sql_rec.source_subinventory_code := l_comp_sys_rec.supply_subinventory ;
2578 l_comp_sql_rec.source_locator_id := l_comp_sys_rec.supply_locator_id ;
2579 l_comp_sql_rec.revision := l_comp_sys_rec.revision;
2580 l_comp_sql_rec.primary_uom_code := l_comp_sys_rec.primary_uom_code;
2581 l_comp_sql_rec.item_name:= l_comp_sys_rec.item_name;
2582 if(round(abs(l_comp_sys_rec.primary_quantity), WIP_CONSTANTS.INV_MAX_PRECISION) <> 0) then
2583 x_comp_sql_tbl(j) := l_comp_sql_rec;
2584 j := j+1;
2585 end if;
2586 end if;
2587 i := l_comp_sys_table.NEXT(i); -- get subscript of next element
2588 END LOOP;
2589
2590 end explodeMultiLevel;
2591
2592 Procedure Post_Explosion_CleanUp(p_wip_entity_id in number,
2593 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
2594 p_org_id in NUMBER,
2595 x_return_status OUT NOCOPY VARCHAR2,
2596 x_msg_data OUT NOCOPY VARCHAR2 ) IS
2597 l_supply_subinventory VARCHAR2(30) := NULL;
2598 l_supply_locator_id NUMBER := NULL;
2599 l_operation_seq_num NUMBER;
2600 l_resource_seq_num NUMBER;
2601 l_dummy2 VARCHAR2(1);
2602 l_logLevel number;
2603
2604 CURSOR c_disc_operations(v_wip_entity_id NUMBER, v_organization_id NUMBER) IS
2605 select unique operation_seq_num
2606 from wip_requirement_operations
2607 where wip_entity_id = v_wip_entity_id
2608 and organization_id = v_organization_id
2609 and wip_supply_type in (wip_constants.op_pull, wip_constants.assy_pull);
2610
2611 CURSOR c_rep_operations(v_wip_entity_id NUMBER, v_repetitive_schedule_id NUMBER,
2612 v_organization_id NUMBER) IS
2613 select unique operation_seq_num
2614 from wip_requirement_operations
2615 where wip_entity_id = v_wip_entity_id
2616 and organization_id = v_organization_id
2617 and repetitive_schedule_id = v_repetitive_schedule_id
2618 and wip_supply_type in (wip_constants.op_pull, wip_constants.assy_pull);
2619 Begin
2620 x_return_status := FND_API.G_RET_STS_SUCCESS;
2621
2622 l_logLevel := fnd_log.g_current_runtime_level;
2623 if (l_logLevel <= wip_constants.trace_logging) then
2624 wip_logger.log('In wip_picking_pvt.Post_Explosion_CleanUp():'
2625 || p_wip_entity_id || ':' || p_org_id
2626 || ':' || p_repetitive_schedule_id, l_dummy2);
2627 end if;
2628
2629 If (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
2630 Open c_disc_operations(p_wip_entity_id, p_org_id);
2631 Else
2632 Open c_rep_operations(p_wip_entity_id, p_repetitive_schedule_id, p_org_id);
2633 End if;
2634
2635 LOOP
2636 l_supply_subinventory := null;
2637 l_supply_locator_id := null;
2638
2639 If (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
2640 fetch c_disc_operations into l_operation_seq_num;
2641 exit when c_disc_operations%NOTFOUND;
2642
2643 begin
2644 select br1.supply_subinventory, br1.supply_locator_id
2645 into l_supply_subinventory, l_supply_locator_id
2646 from bom_resources br1, wip_operation_resources wor1
2647 where br1.resource_id = wor1.resource_id
2648 and br1.organization_id = wor1.organization_id
2649 and wor1.wip_entity_id = p_wip_entity_id
2650 and wor1.organization_id = p_org_id
2651 and wor1.operation_seq_num = l_operation_seq_num
2652 and wor1.resource_seq_num =
2653 (select min(wor2.resource_seq_num)
2654 from bom_resources br2, wip_operation_resources wor2
2655 where wor2.wip_entity_id = wor1.wip_entity_id
2656 and wor2.organization_id= wor1.organization_id
2657 and wor2.operation_seq_num = wor1.operation_seq_num
2658 and br2.supply_subinventory is not null
2659 and br2.organization_id = wor2.organization_id
2660 and br2.resource_id = wor2.resource_id
2661 and br2.resource_type= 1); -- machine type
2662 exception
2663 when no_data_found then
2664 null;
2665 end;
2666
2667 if (l_supply_subinventory is not null) then
2668 wip_picking_pub.Update_Requirement_SubinvLoc(p_wip_entity_id => p_wip_entity_id,
2669 p_repetitive_schedule_id => p_repetitive_schedule_id,
2670 p_operation_seq_num => l_operation_seq_num,
2671 p_supply_subinventory => l_supply_subinventory,
2672 p_supply_locator_id => l_supply_locator_id,
2673 x_return_status => x_return_status,
2674 x_msg_data => x_msg_data);
2675
2676 if (x_return_status <> fnd_api.g_ret_sts_success) then
2677 if (l_logLevel <= wip_constants.trace_logging) then
2678 wip_logger.log('wip_picking_pvt.Post_Explosion_CleanUp: ' ||
2679 'wip_picking_pub.Update_Requirement_SubinvLoc failed..', l_dummy2);
2680 end if;
2681 Return;
2682 End if;
2683 end if;
2684 else
2685
2686 fetch c_rep_operations into l_operation_seq_num;
2687 exit when c_rep_operations%NOTFOUND;
2688
2689 begin
2690 select br1.supply_subinventory, br1.supply_locator_id
2691 into l_supply_subinventory, l_supply_locator_id
2692 from bom_resources br1, wip_operation_resources wor1
2693 where br1.resource_id = wor1.resource_id
2694 and br1.organization_id = wor1.organization_id
2695 and wor1.wip_entity_id = p_wip_entity_id
2696 and wor1.repetitive_schedule_id = p_repetitive_schedule_id
2697 and wor1.organization_id = p_org_id
2698 and wor1.operation_seq_num = l_operation_seq_num
2699 and wor1.resource_seq_num =
2700 (select min(wor2.resource_seq_num)
2701 from bom_resources br2, wip_operation_resources wor2
2702 where wor2.wip_entity_id = wor1.wip_entity_id
2703 and wor2.organization_id= wor1.organization_id
2704 and wor2.operation_seq_num = wor1.operation_seq_num
2705 and br2.supply_subinventory is not null
2706 and br2.organization_id = wor2.organization_id
2707 and br2.resource_id = wor2.resource_id
2708 and br2.resource_type= 1); -- machine type
2709 exception
2710 when no_data_found then
2711 null;
2712 end;
2713
2714 if l_supply_subinventory is not null then
2715 wip_picking_pub.Update_Requirement_SubinvLoc(p_wip_entity_id => p_wip_entity_id,
2716 p_repetitive_schedule_id => p_repetitive_schedule_id,
2717 p_operation_seq_num => l_operation_seq_num,
2718 p_supply_subinventory => l_supply_subinventory,
2719 p_supply_locator_id => l_supply_locator_id,
2720 x_return_status => x_return_status,
2721 x_msg_data => x_msg_data);
2722
2723 if (x_return_status <> fnd_api.g_ret_sts_success) then
2724 if (l_logLevel <= wip_constants.trace_logging) then
2725 wip_logger.log('wip_picking_pvt.Post_Explosion_CleanUp: ' ||
2726 'wip_picking_pub.Update_Requirement_SubinvLoc failed..', l_dummy2);
2727 end if;
2728 Return;
2729 End if;
2730 end if;
2731 end if;
2732
2733 End Loop;
2734
2735 if(c_disc_operations%ISOPEN) then
2736 close c_disc_operations;
2737 elsif(c_rep_operations%ISOPEN) then
2738 close c_rep_operations;
2739 end if;
2740
2741 EXCEPTION
2742 when RECORDS_LOCKED then
2743 x_return_status := 'L';
2744 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
2745 x_msg_data := fnd_message.get;
2746 if (l_logLevel <= wip_constants.trace_logging) then
2747 wip_logger.log( x_msg_data, l_dummy2);
2748 wip_logger.cleanup(l_dummy2);
2749 end if;
2750 when others then
2751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2752 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
2753 fnd_message.set_token('ERROR_TEXT', 'WIP_EXPLODER_UTITLITIES.Post_Explosion_CleanUp:'
2754 || SQLERRM);
2755 x_msg_data := fnd_message.get;
2756 if (l_logLevel <= wip_constants.trace_logging) then
2757 wip_logger.log( x_msg_data, l_dummy2);
2758 wip_logger.cleanup(l_dummy2);
2759 end if;
2760 End Post_Explosion_CleanUp;
2761
2762
2763 end wip_picking_pvt;