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