[Home] [Help]
PACKAGE BODY: APPS.WIP_PICKING_PUB
Source
1 package body wip_picking_pub as
2 /* $Header: wippckpb.pls 120.9.12020000.2 2013/01/22 08:17:24 sisankar ship $ */
3
4 /* This procedure is callback for INV to set backorder qty in WRO, and should only be called
5 for all but flow */
6 procedure pre_allocate_material(p_wip_entity_id in NUMBER,
7 p_operation_seq_num in NUMBER,
8 p_inventory_item_id in NUMBER,
9 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
10 p_use_pickset_flag in VARCHAR2, -- null is no,
11 p_allocate_quantity in NUMBER,
12 x_return_status OUT NOCOPY VARCHAR2,
13 x_msg_data OUT NOCOPY VARCHAR2) IS
14
15 l_entityType NUMBER;
16 l_sysDate DATE := sysdate;
17 l_userId NUMBER := fnd_global.user_id;
18 l_loginId NUMBER := fnd_global.login_id;
19
20 begin
21
22 SAVEPOINT WIP_PRE_ALLOC_MATERIAL_START;
23 x_return_status := FND_API.G_RET_STS_SUCCESS;
24
25 select entity_type
26 into l_entityType
27 from wip_entities
28 where wip_entity_id = p_wip_entity_id;
29
30 if(l_entityType = wip_constants.flow) then --flow schedule
31 return;
32 end if;
33
34 --for repetitive, lot-based and discrete...
35 if(p_repetitive_schedule_id IS NULL) then
36 if(p_use_pickset_flag = 'N') then
37 update wip_requirement_operations
38 set quantity_backordered = p_allocate_quantity,
39 last_update_date = l_sysDate,
40 last_updated_by = l_userId,
41 last_update_login = l_loginId
42 where inventory_item_id = p_inventory_item_id
43 and wip_entity_id = p_wip_entity_id
44 and operation_seq_num = p_operation_seq_num;
45 end if;
46 else
47 if(p_use_pickset_flag = 'N') then
48 update wip_requirement_operations
49 set quantity_backordered = p_allocate_quantity,
50 last_update_date = l_sysDate,
51 last_updated_by = l_userId,
52 last_update_login = l_loginId
53 where inventory_item_id = p_inventory_item_id
54 and wip_entity_id = p_wip_entity_id
55 and repetitive_schedule_id = p_repetitive_schedule_id
56 and operation_seq_num = p_operation_seq_num;
57 end if;
58 end if;
59
60 exception
61 when RECORDS_LOCKED then
62 ROLLBACK TO WIP_PRE_ALLOC_MATERIAL_START;
63 x_return_status := 'L';
64 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
65 x_msg_data := fnd_message.get;
66 when others then
67 ROLLBACK TO WIP_PRE_ALLOC_MATERIAL_START;
68 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
69 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
70 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.pre_allocate_material: ' || SQLERRM);
71 x_msg_data := fnd_message.get;
72
73 end pre_allocate_material;
74
75 procedure issue_material(p_wip_entity_id in NUMBER,
76 p_operation_seq_num in NUMBER,
77 p_inventory_item_id in NUMBER,
78 p_repetitive_line_id in NUMBER DEFAULT NULL,
79 p_transaction_id in NUMBER DEFAULT NULL,
80 p_primary_quantity in NUMBER,
81 x_return_status OUT NOCOPY VARCHAR2,
82 x_msg_data OUT NOCOPY VARCHAR2) IS
83 l_entityType NUMBER;
84 l_status NUMBER;
85 l_statusCode VARCHAR2(80);
86 l_rmnQty NUMBER;
87 l_allocQty NUMBER;
88 l_updQty NUMBER;
89 l_repSchedID NUMBER;
90
91 cursor c_rep is
92 SELECT WRO.REPETITIVE_SCHEDULE_ID,
93 WRO.ORGANIZATION_ID,
94 WRO.ROWID,
95 LEAST(GREATEST((WRO.REQUIRED_QUANTITY - WRO.QUANTITY_ISSUED), 0),
96 -- nvl(wro.quantity_allocated, 0)) open_quantity,
97 nvl(wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM, WRO.ORGANIZATION_ID,
98 WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, WRO.QUANTITY_ISSUED),0)) open_quantity,
99 -- wro.quantity_allocated
100 wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM, WRO.ORGANIZATION_ID,
101 WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,WRO.QUANTITY_ISSUED) quantity_allocated
102 FROM WIP_REQUIREMENT_OPERATIONS WRO,
103 WIP_REPETITIVE_SCHEDULES WRS
104 WHERE WRO.WIP_ENTITY_ID = p_wip_entity_id
105 AND WRO.INVENTORY_ITEM_ID = p_inventory_item_id
106 AND WRO.OPERATION_SEQ_NUM = p_operation_seq_num
107 AND WRS.REPETITIVE_SCHEDULE_ID = WRO.REPETITIVE_SCHEDULE_ID
108 AND WRS.ORGANIZATION_ID = wro.organization_id
109 AND WRS.WIP_ENTITY_ID = p_wip_entity_id
110 AND WRS.LINE_ID = p_repetitive_line_id
111 AND WRS.STATUS_TYPE in (3,4)
112 ORDER BY WRS.FIRST_UNIT_START_DATE
113 for update of wro.quantity_issued, wro.quantity_allocated;
114
115 begin
116 SAVEPOINT WIP_ISSUE_MATERIAL_START;
117 x_return_status := FND_API.G_RET_STS_SUCCESS;
118 select entity_type
119 into l_entityType
120 from wip_entities
121 where wip_entity_id = p_wip_entity_id;
122
123 if(l_entityType <> wip_constants.REPETITIVE) then
124 select wdj.status_type
125 into l_status
126 from wip_discrete_jobs wdj, wip_requirement_operations wro
127 where wdj.wip_entity_id = p_wip_entity_id
128 and wro.wip_entity_id = wdj.wip_entity_id
129 and wro.inventory_item_id = p_inventory_item_id
130 and wro.operation_seq_num = p_operation_seq_num
131 and wro.wip_supply_type = 1
132 for update of wro.quantity_issued, wro.quantity_allocated nowait;
133
134 if(l_status NOT IN (3,4)) then
135 select meaning
136 into l_statusCode
137 from mfg_lookups
138 where lookup_type = 'WIP_JOB_STATUS'
139 and lookup_code = l_status;
140 raise FND_API.G_EXC_UNEXPECTED_ERROR;
141 end if;
142
143 --up issued
144 --lower allocated
145 update wip_requirement_operations
146 set quantity_issued = quantity_issued - p_primary_quantity,
147 quantity_allocated = greatest(0, quantity_allocated + p_primary_quantity)
148 where wip_entity_id = p_wip_entity_id
149 and operation_seq_num = p_operation_seq_num
150 and inventory_item_id = p_inventory_item_id
151 and wip_supply_type = 1; -- a push component
152
153 else --repetitive schedules
154 l_rmnQty := -1 * p_primary_quantity;
155 FOR l_rec in c_rep loop
156 if(l_rmnQty > l_rec.open_quantity) then
157 l_updQty := l_rec.open_quantity;
158 l_rmnQty := l_rmnQty - l_rec.open_quantity;
159 else
160 l_updQty := l_rmnQty;
161 l_rmnQty := 0;
162 end if;
163 update wip_requirement_operations
164 set quantity_issued = quantity_issued + l_updQty,
165 quantity_allocated = greatest(0, quantity_allocated - l_updQty)
166 where repetitive_schedule_id = l_rec.repetitive_schedule_id
167 and operation_seq_num = p_operation_seq_num
168 and inventory_item_id = p_inventory_item_id
169 and wip_supply_type = 1; -- a push component
170
171 insert into mtl_material_txn_allocations(
172 transaction_id,
173 repetitive_schedule_id,
174 organization_id,
175 last_update_date,
176 last_updated_by,
177 creation_date,
178 created_by,
179 primary_quantity,
180 transaction_quantity,
181 transaction_date)
182 values(
183 p_transaction_id,
184 l_rec.repetitive_schedule_id,
185 l_rec.organization_id,
186 sysdate,
187 fnd_global.user_id,
188 sysdate,
189 fnd_global.user_id,
190 -1 * round(l_updQty, 6),
191 -1 * round(l_updQty, 6),
192 sysdate);
193
194 exit when l_rmnQty = 0;
195 l_repSchedID := l_rec.repetitive_schedule_id;
196 end loop;
197 if(l_rmnQty > 0) then
198 --apply remaining qty to last open schedule
199 update wip_requirement_operations
200 set quantity_issued = quantity_issued + l_rmnQty,
201 quantity_allocated = 0
202 where repetitive_schedule_id = l_repSchedID
203 and operation_seq_num = p_operation_seq_num
204 and inventory_item_id = p_inventory_item_id
205 and wip_supply_type = 1; -- a push component
206
207 update mtl_material_txn_allocations
208 set primary_quantity = primary_quantity - ROUND(l_rmnQty, 6),
209 transaction_quantity = transaction_quantity - ROUND(l_rmnQty, 6)
210 where repetitive_schedule_id = l_repSchedID
211 and transaction_id = p_transaction_id;
212 end if;
213 end if;
214 exception
215 when NO_DATA_FOUND then
216 ROLLBACK TO WIP_ISSUE_MATERIAL_START;
217 x_return_status := FND_API.G_RET_STS_ERROR;
218 fnd_message.set_name('WIP', 'WIP_NO_PUSH_REQUIREMENT');
219 x_msg_data := fnd_message.get;
220 when RECORDS_LOCKED then
221 ROLLBACK TO WIP_ISSUE_MATERIAL_START;
222 x_return_status := 'L';
223 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
224 x_msg_data := fnd_message.get;
225 when FND_API.G_EXC_UNEXPECTED_ERROR then
226 ROLLBACK TO WIP_ISSUE_MATERIAL_START;
227 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
228 fnd_message.set_name('WIP', 'WIP_PICKING_STATUS_ERROR');
229 fnd_message.set_token('STATUS', l_statusCode);
230 x_msg_data := fnd_message.get;
231 when others then
232 ROLLBACK TO WIP_ISSUE_MATERIAL_START;
233 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
234 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
235 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.issue_material: ' || SQLERRM);
236 x_msg_data := fnd_message.get;
237 end issue_material;
238
239 procedure allocate_material(p_wip_entity_id in NUMBER,
240 p_operation_seq_num in NUMBER,
241 p_inventory_item_id in NUMBER,
242 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
243 p_primary_quantity in NUMBER,
244 x_quantity_allocated out nocopy NUMBER,
245 x_return_status OUT NOCOPY VARCHAR2,
246 x_msg_data OUT NOCOPY VARCHAR2) IS
247 l_rowid ROWID;
248 l_backordered NUMBER;
249 l_allocated NUMBER;
250 l_entityType NUMBER;
251 l_flow VARCHAR2(1);
252 l_openQty NUMBER;
253 begin
254 SAVEPOINT WIP_ALLOCATE_MATERIAL_START;
255 x_return_status := FND_API.G_RET_STS_SUCCESS;
256 x_quantity_allocated := 0;
257
258 select entity_type
259 into l_entityType
260 from wip_entities
261 where wip_entity_id = p_wip_entity_id;
262 if(l_entityType = wip_constants.flow) then --flow schedule
263 select allocated_flag
264 into l_flow
265 from wip_flow_schedules
266 where wip_entity_id = p_wip_entity_id
267 for update of allocated_flag nowait; --set error code if row is locked
268 -- Bug 14773855. Update allocated Flag when Move order is back ordered.
269 if p_primary_quantity > 0 then
270 update wip_flow_schedules
271 set allocated_flag = 'Y'
272 where wip_entity_id = p_wip_entity_id;
273 return;
274 else
275 update wip_flow_schedules
276 set allocated_flag = 'N'
277 where wip_entity_id = p_wip_entity_id;
278 return;
279 end if;
280 end if;
281 --for repetitive, lot-based and discrete...
282 if(p_repetitive_schedule_id IS NULL) then
283 select nvl(quantity_allocated, 0),
284 nvl(quantity_backordered,0),
285 p_primary_quantity,
286 -- above line replaces line below. quantity_allocated() is not equivalent
287 -- to wro.quantity_allocated column in this case. And the former was
288 -- used to replace the latter. The difference is the api is already
289 -- updated with the current pick release, and the column still shows
290 -- qty allocate before the current pick release.
291 /*least(greatest(required_quantity - quantity_issued - nvl(
292 quantity_allocated(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
293 INVENTORY_ITEM_ID, QUANTITY_ISSUED),0), 0),
294 p_primary_quantity), */
295 rowid
296 into l_allocated,
297 l_backordered,
298 l_openQty,
299 l_rowid
300 from wip_requirement_operations
301 where wip_entity_id = p_wip_entity_id
302 and operation_seq_num = p_operation_seq_num
303 and inventory_item_id = p_inventory_item_id
304 for update of quantity_backordered, quantity_allocated nowait;
305 else
306 select nvl(quantity_allocated, 0),
307 nvl(quantity_backordered,0),
308 p_primary_quantity,
309 -- above line replaces line below. quantity_allocated() is not equivalent
310 -- to wro.quantity_allocated column in this case. And the former was
311 -- used to replace the latter. The difference is the api is already
312 -- updated with the current pick release, and the column still shows
313 -- qty allocate before the current pick release.
314 /*least(greatest(required_quantity - quantity_issued - nvl(
315 quantity_allocated(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
316 INVENTORY_ITEM_ID, QUANTITY_ISSUED),0), 0),
317 p_primary_quantity), */
318 rowid
319 into l_allocated,
320 l_backordered,
321 l_openQty,
322 l_rowid
323 from wip_requirement_operations
324 where wip_entity_id = p_wip_entity_id
325 and operation_seq_num = p_operation_seq_num
326 and inventory_item_id = p_inventory_item_id
327 and repetitive_schedule_id = p_repetitive_schedule_id
328 for update of quantity_backordered, quantity_allocated nowait;
329 end if;
330
331 x_quantity_allocated := l_openQty;
332
333 --if unallocating, make sure you are unallocating less than or equal to
334 --what has been allocated
335
336 /* Comment out following for Bug#5962196. Quantity_allocated will be zero
337 when components are issued to a job manually in stead of transacting move
338 order line. Following condition will raise error while backordering line
339 */
340
341 -- if((p_primary_quantity < 0) and ((p_primary_quantity * -1) > l_allocated)) then
342 -- raise fnd_api.G_EXC_UNEXPECTED_ERROR;
343 -- end if;
344
345 if(p_primary_quantity > l_backordered) then
346 update wip_requirement_operations
347 set quantity_backordered = 0,
348 quantity_allocated = l_allocated + l_openQty
349 where rowid = l_rowid;
350 else
351 /* Fix for Bug#5962196. Added decode */
352 update wip_requirement_operations
353 set quantity_backordered = l_backordered - l_openQty,
354 quantity_allocated = decode(sign(l_allocated + l_openQty), -1, l_allocated, 0,
355 0,
356 (l_allocated + l_openQty))
357 where rowid = l_rowid;
358 end if;
359 exception
360 when NO_DATA_FOUND then
361 x_quantity_allocated := 0;
362 when fnd_api.G_EXC_UNEXPECTED_ERROR then
363 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
364 fnd_message.set_name('WIP', 'WIP_PICKING_DEALLOCATE_ERROR');
365 x_msg_data := fnd_message.get;
366 when RECORDS_LOCKED then
367 ROLLBACK TO WIP_ALLOCATE_MATERIAL_START;
368 x_return_status := 'L';
369 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
370 x_msg_data := fnd_message.get;
371 when others then
372 ROLLBACK TO WIP_ALLOCATE_MATERIAL_START;
373 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
374 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
375 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.allocate_material: ' || SQLERRM);
376 x_msg_data := fnd_message.get;
377 end allocate_material;
378
379 procedure unallocate_material(p_wip_entity_id in NUMBER,
380 p_operation_seq_num in NUMBER,
381 p_inventory_item_id in NUMBER,
382 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
383 p_primary_quantity in NUMBER,
384 x_return_status OUT NOCOPY VARCHAR2,
385 x_msg_data OUT NOCOPY VARCHAR2) is
386 l_quantityAllocated NUMBER; --dummy
387 begin
388 --can just call allocate material w/a negative quantity
389 allocate_material(p_wip_entity_id => p_wip_entity_id,
390 p_operation_seq_num => p_operation_seq_num,
391 p_inventory_item_id => p_inventory_item_id,
392 p_repetitive_schedule_id => p_repetitive_schedule_id,
393 p_primary_quantity => (-1) * p_primary_quantity,
394 x_quantity_allocated => l_quantityAllocated,
395 x_return_status => x_return_status,
396 x_msg_data => x_msg_data);
397 end unallocate_material;
398
399
400 procedure cancel_allocations(p_wip_entity_id NUMBER,
401 p_wip_entity_type NUMBER,
402 p_repetitive_schedule_id NUMBER DEFAULT NULL,
403 x_return_status OUT NOCOPY VARCHAR2,
404 x_msg_data OUT NOCOPY VARCHAR2) IS
405 BEGIN
406 wip_picking_pvt.cancel_allocations(p_wip_entity_id => p_wip_entity_id,
407 p_wip_entity_type => p_wip_entity_type,
408 p_repetitive_schedule_id => p_repetitive_schedule_id,
409 x_return_status => x_return_status,
410 x_msg_data => x_msg_data);
411 END cancel_allocations;
412
413
414
415
416 Procedure cancel_comp_allocations(p_wip_entity_id NUMBER,
417 p_operation_seq_num NUMBER,
418 p_inventory_item_id NUMBER,
419 p_wip_entity_type NUMBER,
420 p_repetitive_schedule_id NUMBER DEFAULT NULL,
421 x_return_status OUT NOCOPY VARCHAR2,
422 x_msg_data OUT NOCOPY VARCHAR2) IS
423 BEGIN
424 wip_picking_pvt.cancel_comp_allocations(p_wip_entity_id => p_wip_entity_id,
425 p_operation_seq_num => p_operation_seq_num,
426 p_inventory_item_id => p_inventory_item_id,
427 p_wip_entity_type => p_wip_entity_type,
428 p_repetitive_schedule_id => p_repetitive_schedule_id,
429 x_return_status => x_return_status,
430 x_msg_data => x_msg_data);
431 END cancel_comp_allocations;
432
433
434 procedure reduce_comp_allocations(p_comp_tbl IN allocate_comp_tbl_t,
435 p_wip_entity_type NUMBER,
436 p_organization_id NUMBER,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_msg_data OUT NOCOPY VARCHAR2) IS
439 BEGIN
440 wip_picking_pvt.reduce_comp_allocations(p_comp_tbl => p_comp_tbl,
441 p_wip_entity_type => p_wip_entity_type,
442 p_organization_id => p_organization_id,
443 x_return_status => x_return_status,
444 x_msg_data => x_msg_data);
445 END reduce_comp_allocations;
446
447
448 procedure allocate(p_alloc_tbl IN OUT NOCOPY allocate_tbl_t,
449 p_days_to_alloc NUMBER := NULL, --only used for rep scheds
450 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
451 p_start_date DATE DEFAULT NULL,
452 p_cutoff_date DATE,
453 p_operation_seq_num_low NUMBER DEFAULT NULL,
454 p_operation_seq_num_high NUMBER DEFAULT NULL,
455 p_wip_entity_type NUMBER,
456 p_organization_id NUMBER,
457 p_pick_grouping_rule_id NUMBER := NULL,
458 p_print_pick_slip VARCHAR2 DEFAULT NULL,
459 p_plan_tasks BOOLEAN DEFAULT NULL,
460 x_conc_req_id OUT NOCOPY NUMBER,
461 x_mo_req_number OUT NOCOPY VARCHAR2,
462 x_return_status OUT NOCOPY VARCHAR2,
463 x_msg_data OUT NOCOPY VARCHAR2) IS
464 BEGIN
465 wip_picking_pvt.allocate(p_alloc_tbl => p_alloc_tbl,
466 p_days_to_alloc => p_days_to_alloc,
467 p_auto_detail_flag => p_auto_detail_flag,
468 p_start_date => p_start_date,
469 p_cutoff_date => p_cutoff_date,
470 p_operation_seq_num_low => p_operation_seq_num_low,
471 p_operation_seq_num_high => p_operation_seq_num_high,
472 p_wip_entity_type => p_wip_entity_type,
473 p_organization_id => p_organization_id,
474 p_pick_grouping_rule_id => p_pick_grouping_rule_id,
475 p_print_pick_slip => p_print_pick_slip,
476 p_plan_tasks => p_plan_tasks,
477 x_conc_req_id => x_conc_req_id,
478 x_mo_req_number => x_mo_req_number,
479 x_return_status => x_return_status,
480 x_msg_data => x_msg_data);
481 END allocate;
482
483
484
485 procedure allocate_comp(p_alloc_comp_tbl IN OUT NOCOPY allocate_comp_tbl_t,
486 p_days_to_alloc NUMBER DEFAULT NULL, --only used for rep scheds
487 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
488 p_cutoff_date DATE,
489 p_wip_entity_type NUMBER,
490 p_organization_id NUMBER,
491 p_pick_grouping_rule_id NUMBER := NULL,
492 p_print_pick_slip VARCHAR2 DEFAULT NULL,
493 p_plan_tasks BOOLEAN DEFAULT NULL,
494 x_conc_req_id OUT NOCOPY NUMBER,
495 x_mo_req_number OUT NOCOPY VARCHAR2,
496 x_return_status OUT NOCOPY VARCHAR2,
497 x_msg_data OUT NOCOPY VARCHAR2) IS
498 BEGIN
499 wip_picking_pvt.allocate_comp(p_alloc_comp_tbl => p_alloc_comp_tbl,
500 p_days_to_alloc => p_days_to_alloc, --only used for rep scheds
501 p_auto_detail_flag => p_auto_detail_flag,
502 p_cutoff_date => p_cutoff_date,
503 p_wip_entity_type => p_wip_entity_type,
504 p_organization_id => p_organization_id,
505 p_pick_grouping_rule_id => p_pick_grouping_rule_id,
506 p_print_pick_slip => p_print_pick_slip,
507 p_plan_tasks => p_plan_tasks,
508 x_conc_req_id => x_conc_req_id,
509 x_mo_req_number => x_mo_req_number,
510 x_return_status => x_return_status,
511 x_msg_data => x_msg_data);
512 END allocate_comp;
513
514
515 /*
516 This function replaces the quantity_allocated column in WRO.
517 Note: for pull components:
518 l_quantity_allocated := l_mtrl_quantity - l_quantity_issued
519 but when calculating open_quantity:
520 open_qty = required_quantity - quantity_issued - Quantity_Allocated()
521 so, the quantity_issued cancels and is not considered for open_qty
522 */
523 Function quantity_allocated(p_wip_entity_id IN NUMBER,
524 p_operation_seq_num IN NUMBER,
525 p_organization_id IN NUMBER,
526 p_inventory_item_id IN NUMBER,
527 p_repetitive_schedule_id IN NUMBER DEFAULT NULL,
528 p_quantity_issued IN NUMBER DEFAULT NULL)
529 return NUMBER
530 is
531 l_quantity_allocated number;
532 l_mtrl_quantity number;
533 l_quantity_issued number := NULL;
534 l_txn_type_id number;
535 l_dummy2 VARCHAR2(1);
536 l_wro_quantity_allocated number := 0 ; /* 5468293 */
537 l_dummy number := 0 ; /* 5468293 */
538 begin
539 begin
540 -- Modified Query for performance bug 8561606 (FP 8523754).
541 select mtrl.transaction_type_id
542 into l_txn_type_id
543 from MTL_TXN_REQUEST_LINES mtrl
544 where
545 mtrl.TXN_SOURCE_ID = p_wip_entity_id and
546 mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
547 mtrl.organization_id = p_organization_id and
548 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
549 rownum = 1;
550 exception
551 when TOO_MANY_ROWS then
552 wip_logger.log('Error occured in wip_picking_pub.quantity_allocated():', l_dummy2);
553 wip_logger.log(' Inconsistent transaction_type_id in rows of MTRL table!', l_dummy2);
554 -- Bug 5336791. saugupta 17th-Jun-2006
555 -- When calling wip_picking_pub.quantity_allocated for a newly released
556 -- repetitive schedule for which no component picking move order lines exist,
557 -- it is throwing a NO_DATA_FOUND exception.
558 when NO_DATA_FOUND then
559 wip_logger.log('Function wip_picking_pub.quantity_allocated(): No data found', l_dummy2);
560 return 0;
561 end;
562
563 l_quantity_issued := p_quantity_issued;
564 if l_quantity_issued is null then
565 select quantity_issued into l_quantity_issued
566 from WIP_REQUIREMENT_OPERATIONS
567 where wip_entity_id = p_wip_entity_id
568 and operation_seq_num = p_operation_seq_num
569 and repetitive_schedule_id = p_repetitive_schedule_id
570 and organization_id = p_organization_id
571 and inventory_item_id = p_inventory_item_id;
572 end if;
573
574 begin
575 if (l_txn_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_WIP_ISSUE) then
576 select sum(nvl(mtrl.quantity,0) - nvl(mtrl.quantity_delivered,0))
577 into l_quantity_allocated
578 from MTL_TXN_REQUEST_LINES mtrl
579 where
580 mtrl.TXN_SOURCE_ID = p_wip_entity_id and
581 mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
582 ( p_repetitive_schedule_id is null or
583 mtrl.reference_id = p_repetitive_schedule_id) and
584 mtrl.organization_id = p_organization_id and
585 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
586 -- preapproved status or open lines
587 mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
588 group by mtrl.organization_id, mtrl.TXN_SOURCE_ID,
589 mtrl.TXN_SOURCE_LINE_ID, mtrl.INVENTORY_ITEM_ID;
590 else
591 select sum(nvl(mtrl.quantity,0))
592 into l_mtrl_quantity
593 from MTL_TXN_REQUEST_LINES mtrl
594 where
595 mtrl.TXN_SOURCE_ID = p_wip_entity_id and
596 mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
597 ( p_repetitive_schedule_id is null or
598 mtrl.reference_id = p_repetitive_schedule_id) and
599 mtrl.organization_id = p_organization_id and
600 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
601 mtrl.line_status in (INV_GLOBALS.G_TO_STATUS_CLOSED,
602 INV_GLOBALS.G_TO_STATUS_PREAPPROVED) -- preapproved or closed
603 group by mtrl.organization_id, mtrl.TXN_SOURCE_ID,
604 mtrl.TXN_SOURCE_LINE_ID, mtrl.INVENTORY_ITEM_ID;
605
606 /* Start for the Fix for Bug#5468293.
607 Check for Cross Docking.
608 backorder_deliver_detail_id is wip_entity_id for cross dock.
609 However Operation info is not populated in mtrl.
610 Hence get it from WRO.
611 */
612 begin
613
614 select 1
615 into l_dummy
616 from dual
617 where exists
618 ( select 1
619 from MTL_TXN_REQUEST_LINES mtrl
620 where
621 mtrl.backorder_delivery_detail_id = p_wip_entity_id and
622 mtrl.TXN_SOURCE_LINE_ID is null and
623 ( p_repetitive_schedule_id is null or
624 mtrl.reference_id = p_repetitive_schedule_id) and
625 mtrl.crossdock_type = 2 and -- WIP
626 mtrl.organization_id = p_organization_id and
627 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
628 mtrl.line_status in (INV_GLOBALS.G_TO_STATUS_CLOSED,
629 INV_GLOBALS.G_TO_STATUS_PREAPPROVED)) ; -- preapproved or closed
630
631 select quantity_allocated
632 into l_wro_quantity_allocated
633 from wip_requirement_operations
634 where wip_entity_id = p_wip_entity_id
635 and inventory_item_id = p_inventory_item_id
636 and operation_seq_num = p_operation_seq_num
637 and organization_id = p_organization_id
638 and nvl(repetitive_schedule_id, -1 ) = nvl(p_repetitive_schedule_id, -1) ;
639
640
641 l_mtrl_quantity := l_wro_quantity_allocated ;
642
643 exception
644 when no_data_found then
645 l_wro_quantity_allocated := 0 ;
646 end ;
647 /* End for the #5468293 */
648
649 l_quantity_allocated := l_mtrl_quantity - l_quantity_issued;
650 end if;
651 exception
652 when NO_DATA_FOUND then
653 l_quantity_allocated := 0;
654 end;
655
656 return l_quantity_allocated;
657 end;
658
659
660 function Is_Component_Pick_Released(p_wip_entity_id in number,
661 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
662 p_org_id in NUMBER,
663 p_operation_seq_num in NUMBER,
664 p_inventory_item_id in NUMBER) return BOOLEAN IS
665 l_dummy NUMBER := 0;
666 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
667
668 Begin
669 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
670 begin
671 /*Bug 12861942 The original query don't make sense because its purpose is for checking if
672 there are existing/pending move order lines, while quantity_backordered doesn't have much relation
673 existing/pending move order, modified query below to use MTL_TXN_REQUEST_LINES instead
674 Select 1 Into l_dummy
675 from wip_requirement_operations
676 Where wip_entity_id = p_wip_entity_id
677 And Organization_id = p_org_id
678 And operation_seq_num = p_operation_seq_num
679 And inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
680 And quantity_backordered is not null
681 And quantity_backordered <> 0;
682 */
683 select 1 into l_dummy
684 from MTL_TXN_REQUEST_LINES
685 Where txn_source_id = p_wip_entity_id
686 And Organization_id = p_org_id
687 --And operation_seq_num = p_operation_seq_num
688 And inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
689 And rownum = 1;
690 exception
691 when no_data_found then
692 null;
693 end;
694 Else
695 begin
696 /*
697 Bug 12861942 The original query don't make sense because its purpose is for checking if
698 there are existing/pending move order lines, while quantity_backordered doesn't have much relation
699 existing/pending move order, modified query below to use MTL_TXN_REQUEST_LINES instead
700
701 Select 1 Into l_dummy
702 from wip_requirement_operations
703 Where wip_entity_id = p_wip_entity_id
704 And repetitive_schedule_id = p_repetitive_schedule_id
705 And Organization_id = p_org_id
706 And operation_seq_num = p_operation_seq_num
707 And inventory_item_id = nvl(p_inventory_item_id, inventory_item_id) */
708
709
710 Select 1 Into l_dummy
711 from MTL_TXN_REQUEST_LINES
712 Where txn_source_id = p_wip_entity_id
713 And repetitive_schedule_id = p_repetitive_schedule_id
714 And Organization_id = p_org_id
715 --And operation_seq_num = p_operation_seq_num
716 And inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
717 And rownum = 1;
718 exception
719 when no_data_found then
720 null;
721 end;
722 End if;
723
724 return (l_dummy = 1);
725
726 EXCEPTION
727 when others then
728 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
729 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.Is_Component_Pick_Released: ' || SQLERRM);
730 raise fnd_api.g_exc_unexpected_error;
731
732 End Is_Component_Pick_Released;
733
734 Function Is_Job_Pick_Released(p_wip_entity_id in number,
735 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
736 p_org_id in NUMBER) RETURN BOOLEAN IS
737 l_dummy NUMBER := 0;
738 Begin
739
740 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
741 begin
742 /* Bug 12861942 The original query don't make sense because its purpose is for checking if
743 there are existing/pending move order lines, while quantity_backordered doesn't have much relation
744 existing/pending move order, modified query below to use MTL_TXN_REQUEST_LINES instead
745 Select 1 Into l_dummy
746 From dual
747 Where exists
748 (select 1
749 from wip_requirement_operations
750 Where wip_entity_id = p_wip_entity_id
751 And Organization_id = p_org_id
752 And quantity_backordered is not null
753 And quantity_backordered <> 0);
754 */
755 select 1 into l_dummy
756 from MTL_TXN_REQUEST_LINES
757 Where txn_source_id = p_wip_entity_id
758 And Organization_id = p_org_id
759 And rownum = 1;
760 exception
761 when no_data_found then
762 null;
763 end;
764 Else
765 begin
766 /* Bug 12861942 The original query don't make sense because its purpose is for checking if
767 there are existing/pending move order lines, while quantity_backordered doesn't have much relation
768 existing/pending move order, modified query below to use MTL_TXN_REQUEST_LINES instead
769 Select 1 Into l_dummy
770 From dual
771 Where exists
772 (select 1
773 from wip_requirement_operations
774 Where wip_entity_id = p_wip_entity_id
775 And repetitive_schedule_id = p_repetitive_schedule_id
776 And Organization_id = p_org_id
777 And quantity_backordered is not null
778 And quantity_backordered <> 0);
779 */
780 Select 1 Into l_dummy
781 from MTL_TXN_REQUEST_LINES
782 Where txn_source_id = p_wip_entity_id
783 And repetitive_schedule_id = p_repetitive_schedule_id
784 And Organization_id = p_org_id
785 And rownum = 1;
786
787 exception
788 when no_data_found then
789 null;
790 end;
791 End if;
792 Return( l_dummy = 1);
793 EXCEPTION
794 when others then
795 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
796 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.Is_Job_Pick_Released: ' || SQLERRM);
797 raise fnd_api.g_exc_unexpected_error;
798 End Is_Job_Pick_Released;
799
800 Procedure Update_Requirement_SubinvLoc(p_wip_entity_id number,
801 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
802 p_operation_seq_num in NUMBER,
803 p_supply_subinventory in VARCHAR2,
804 p_supply_locator_id in NUMBER,
805 x_return_status OUT NOCOPY VARCHAR2,
806 x_msg_data OUT NOCOPY VARCHAR2) IS
807 l_entityType NUMBER;
808 l_sysDate DATE := sysdate;
809 l_userId NUMBER := fnd_global.user_id;
810 l_loginId NUMBER := fnd_global.login_id;
811
812 Begin
813 SAVEPOINT WIP_UPDATE_REQ_SUBINVLOC_START;
814 x_return_status := FND_API.G_RET_STS_SUCCESS;
815
816 --for repetitive, lot-based and discrete...
817 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
818 update wip_requirement_operations
819 set supply_subinventory = p_supply_subinventory,
820 supply_locator_id = p_supply_locator_id
821 where wip_entity_id = p_wip_entity_id
822 and operation_seq_num = p_operation_seq_num
823 and wip_supply_type in
824 (wip_constants.assy_pull, wip_constants.op_pull);
825 else
826 update wip_requirement_operations
827 set supply_subinventory = p_supply_subinventory,
828 supply_locator_id = p_supply_locator_id
829 where wip_entity_id = p_wip_entity_id
830 and operation_seq_num = p_operation_seq_num
831 and p_repetitive_schedule_id = p_repetitive_schedule_id
832 and wip_supply_type in
833 (wip_constants.assy_pull, wip_constants.op_pull);
834 end if;
835
836 exception
837 when no_data_found then
838 null;
839 when RECORDS_LOCKED then
840 ROLLBACK TO WIP_UPDATE_REQ_SUBINVLOC_START;
841 x_return_status := 'L';
842 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
843 x_msg_data := fnd_message.get;
844 when others then
845 ROLLBACK TO WIP_UPDATE_REQ_SUBINVLOC_START;
846 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
847 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
848 fnd_message.set_token('ERROR_TEXT',
849 'wip_utilities.Update_Requirement_SubinvLoc: ' || SQLERRM);
850 x_msg_data := fnd_message.get;
851 End Update_Requirement_SubinvLoc;
852
853 Procedure Update_Component_BackOrdQty(p_wip_entity_id number,
854 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
855 p_operation_seq_num in NUMBER,
856 p_new_component_qty in NUMBER,
857 p_inventory_item_id in NUMBER DEFAULT NULL,
858 x_return_status OUT NOCOPY VARCHAR2,
859 x_msg_data OUT NOCOPY VARCHAR2) IS
860 l_entityType NUMBER;
861 l_sysDate DATE := sysdate;
862 l_userId NUMBER := fnd_global.user_id;
863 l_loginId NUMBER := fnd_global.login_id;
864 l_dummy VARCHAR2(1);
865 Begin
866 SAVEPOINT WIP_COMP_BACKORDQTY_START;
867 x_return_status := FND_API.G_RET_STS_SUCCESS;
868
869 select entity_type
870 into l_entityType
871 from wip_entities
872 where wip_entity_id = p_wip_entity_id;
873
874 If (l_entityType = wip_constants.flow) then
875 Return;
876 End if;
877
878 --for repetitive, lot-based and discrete...
879 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
880 Update wip_requirement_operations wro
881 set quantity_backordered = GREATEST(p_new_component_qty
882 -- replaced wro.quantity_allocated with function quantity_allocated
883 -- - quantity_issued - quantity_allocated , 0)
884 - quantity_issued - wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
885 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED) , 0)
886 where wip_entity_id = p_wip_entity_id
887 and operation_seq_num = p_operation_seq_num
888 and inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
889 and (
890 ('Y' = (select allocate_backflush_components from wip_parameters wp
891 where organization_id = wro.organization_id)
892 and wip_supply_type in
893 (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
894 or wip_supply_type = wip_constants.push
895 );
896 else
897 Update wip_requirement_operations wro
898 set quantity_backordered = GREATEST(p_new_component_qty
899 -- replaced wro.quantity_allocated with function quantity_allocated
900 -- - quantity_issued - quantity_allocated , 0)
901 - quantity_issued - wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
902 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED) , 0)
903 where wip_entity_id=p_wip_entity_id
904 and operation_seq_num=p_operation_seq_num
905 and p_repetitive_schedule_id = p_repetitive_schedule_id
906 and inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
907 and (
908 ('Y' = (select allocate_backflush_components
909 from wip_parameters wp
910 where organization_id = wro.organization_id)
911 and wip_supply_type in
912 (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
913 or wip_supply_type = wip_constants.push
914 );
915 end if;
916
917 exception
918 when RECORDS_LOCKED then
919 ROLLBACK TO WIP_COMP_BACKORDQTY_START;
920 x_return_status := 'L';
921 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
922 x_msg_data := fnd_message.get;
923 when others then
924 ROLLBACK TO WIP_COMP_BACKORDQTY_START;
925 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
926 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
927 fnd_message.set_token('ERROR_TEXT', 'wip_picking_private.Update_Component_BackOrdQty: '
928 || SQLERRM);
929 x_msg_data := fnd_message.get;
930 End Update_Component_BackOrdQty;
931
932 Procedure Update_Job_BackOrdQty(p_wip_entity_id number,
933 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
934 p_new_job_qty in NUMBER,
935 x_return_status OUT NOCOPY VARCHAR2,
936 x_msg_data OUT NOCOPY VARCHAR2) IS
937 l_entityType NUMBER;
938 l_sysDate DATE := sysdate;
939 l_userId NUMBER := fnd_global.user_id;
940 l_loginId NUMBER := fnd_global.login_id;
941 Begin
942 SAVEPOINT WIP_JOB_BACKORDQTY_START;
943 x_return_status := FND_API.G_RET_STS_SUCCESS;
944
945 select entity_type
946 into l_entityType
947 from wip_entities
948 where wip_entity_id = p_wip_entity_id;
949
950 If (l_entityType = wip_constants.flow) then
951 Return;
952 End if;
953
954 --for repetitive, lot-based and discrete
955 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
956 Update wip_requirement_operations wro
957 set quantity_backordered = GREATEST(p_new_job_qty* quantity_per_assembly -
958 wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
959 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID, QUANTITY_ISSUED)
960 - quantity_issued, 0)
961 where wip_entity_id=p_wip_entity_id
962 and quantity_backordered is not null
963 and (
964 ('Y' = (select allocate_backflush_components
965 from wip_parameters wp
966 where organization_id = wro.organization_id)
967 and wip_supply_type in
968 (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
969 or wip_supply_type = wip_constants.push
970 );
971 else
972 Update wip_requirement_operations wro
973 set quantity_backordered = GREATEST(p_new_job_qty* quantity_per_assembly
974 - wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
975 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED)
976 - quantity_issued, 0)
977 where wip_entity_id=p_wip_entity_id
978 and repetitive_schedule_id = p_repetitive_schedule_id
979 and quantity_backordered is not null
980 and (
981 ('Y' = (select allocate_backflush_components
982 from wip_parameters wp
983 where organization_id = wro.organization_id)
984 and wro.wip_supply_type in
985 (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
986 or wro.wip_supply_type = wip_constants.push
987 ) ;
988 end if;
989
990 exception
991 when RECORDS_LOCKED then
992 ROLLBACK TO WIP_JOB_BACKORDQTY_START;
993 x_return_status := 'L';
994 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
995 x_msg_data := fnd_message.get;
996 when others then
997 ROLLBACK TO WIP_JOB_BACKORDQTY_START;
998 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
999 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
1000 fnd_message.set_token('ERROR_TEXT', 'wip_picking_private.Update_Job_BackOrdQty: '
1001 || SQLERRM);
1002 x_msg_data := fnd_message.get;
1003 End Update_Job_BackOrdQty;
1004
1005 end wip_picking_pub;