[Home] [Help]
PACKAGE BODY: APPS.WIP_PICKING_PUB
Source
1 package body wip_picking_pub as
2 /* $Header: wippckpb.pls 120.3.12000000.2 2007/04/19 23:59:41 kbavadek 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
269 update wip_flow_schedules
270 set allocated_flag = 'Y'
271 where wip_entity_id = p_wip_entity_id;
272 return;
273 end if;
274 --for repetitive, lot-based and discrete...
275 if(p_repetitive_schedule_id IS NULL) then
276 select nvl(quantity_allocated, 0),
277 nvl(quantity_backordered,0),
278 p_primary_quantity,
279 -- above line replaces line below. quantity_allocated() is not equivalent
280 -- to wro.quantity_allocated column in this case. And the former was
281 -- used to replace the latter. The difference is the api is already
282 -- updated with the current pick release, and the column still shows
283 -- qty allocate before the current pick release.
284 /*least(greatest(required_quantity - quantity_issued - nvl(
285 quantity_allocated(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
286 INVENTORY_ITEM_ID, QUANTITY_ISSUED),0), 0),
287 p_primary_quantity), */
288 rowid
289 into l_allocated,
290 l_backordered,
291 l_openQty,
292 l_rowid
293 from wip_requirement_operations
294 where wip_entity_id = p_wip_entity_id
295 and operation_seq_num = p_operation_seq_num
296 and inventory_item_id = p_inventory_item_id
297 for update of quantity_backordered, quantity_allocated nowait;
298 else
299 select nvl(quantity_allocated, 0),
300 nvl(quantity_backordered,0),
301 p_primary_quantity,
302 -- above line replaces line below. quantity_allocated() is not equivalent
303 -- to wro.quantity_allocated column in this case. And the former was
304 -- used to replace the latter. The difference is the api is already
305 -- updated with the current pick release, and the column still shows
306 -- qty allocate before the current pick release.
307 /*least(greatest(required_quantity - quantity_issued - nvl(
308 quantity_allocated(WIP_ENTITY_ID, OPERATION_SEQ_NUM, ORGANIZATION_ID,
309 INVENTORY_ITEM_ID, QUANTITY_ISSUED),0), 0),
310 p_primary_quantity), */
311 rowid
312 into l_allocated,
313 l_backordered,
314 l_openQty,
315 l_rowid
316 from wip_requirement_operations
317 where wip_entity_id = p_wip_entity_id
318 and operation_seq_num = p_operation_seq_num
319 and inventory_item_id = p_inventory_item_id
320 and repetitive_schedule_id = p_repetitive_schedule_id
321 for update of quantity_backordered, quantity_allocated nowait;
322 end if;
323
324 x_quantity_allocated := l_openQty;
325
326 --if unallocating, make sure you are unallocating less than or equal to
327 --what has been allocated
328
329 /* Comment out following for Bug#5962196. Quantity_allocated will be zero
330 when components are issued to a job manually in stead of transacting move
331 order line. Following condition will raise error while backordering line
332 */
333
334 -- if((p_primary_quantity < 0) and ((p_primary_quantity * -1) > l_allocated)) then
335 -- raise fnd_api.G_EXC_UNEXPECTED_ERROR;
336 -- end if;
337
338 if(p_primary_quantity > l_backordered) then
339 update wip_requirement_operations
340 set quantity_backordered = 0,
341 quantity_allocated = l_allocated + l_openQty
342 where rowid = l_rowid;
343 else
344 /* Fix for Bug#5962196. Added decode */
345 update wip_requirement_operations
346 set quantity_backordered = l_backordered - l_openQty,
347 quantity_allocated = decode(sign(l_allocated + l_openQty), -1, l_allocated, 0,
348 0,
349 (l_allocated + l_openQty))
350 where rowid = l_rowid;
354 x_quantity_allocated := 0;
351 end if;
352 exception
353 when NO_DATA_FOUND then
355 when fnd_api.G_EXC_UNEXPECTED_ERROR then
356 x_return_status := fnd_api.G_RET_STS_UNEXP_ERROR;
357 fnd_message.set_name('WIP', 'WIP_PICKING_DEALLOCATE_ERROR');
358 x_msg_data := fnd_message.get;
359 when RECORDS_LOCKED then
360 ROLLBACK TO WIP_ALLOCATE_MATERIAL_START;
361 x_return_status := 'L';
362 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
363 x_msg_data := fnd_message.get;
364 when others then
365 ROLLBACK TO WIP_ALLOCATE_MATERIAL_START;
366 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
367 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
368 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.allocate_material: ' || SQLERRM);
369 x_msg_data := fnd_message.get;
370 end allocate_material;
371
372 procedure unallocate_material(p_wip_entity_id in NUMBER,
373 p_operation_seq_num in NUMBER,
374 p_inventory_item_id in NUMBER,
375 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
376 p_primary_quantity in NUMBER,
377 x_return_status OUT NOCOPY VARCHAR2,
378 x_msg_data OUT NOCOPY VARCHAR2) is
379 l_quantityAllocated NUMBER; --dummy
380 begin
381 --can just call allocate material w/a negative quantity
382 allocate_material(p_wip_entity_id => p_wip_entity_id,
383 p_operation_seq_num => p_operation_seq_num,
384 p_inventory_item_id => p_inventory_item_id,
385 p_repetitive_schedule_id => p_repetitive_schedule_id,
386 p_primary_quantity => (-1) * p_primary_quantity,
387 x_quantity_allocated => l_quantityAllocated,
388 x_return_status => x_return_status,
389 x_msg_data => x_msg_data);
390 end unallocate_material;
391
392
393 procedure cancel_allocations(p_wip_entity_id NUMBER,
394 p_wip_entity_type NUMBER,
395 p_repetitive_schedule_id NUMBER DEFAULT NULL,
396 x_return_status OUT NOCOPY VARCHAR2,
397 x_msg_data OUT NOCOPY VARCHAR2) IS
398 BEGIN
399 wip_picking_pvt.cancel_allocations(p_wip_entity_id => p_wip_entity_id,
400 p_wip_entity_type => p_wip_entity_type,
401 p_repetitive_schedule_id => p_repetitive_schedule_id,
402 x_return_status => x_return_status,
403 x_msg_data => x_msg_data);
404 END cancel_allocations;
405
406
407
408
409 Procedure cancel_comp_allocations(p_wip_entity_id NUMBER,
410 p_operation_seq_num NUMBER,
411 p_inventory_item_id NUMBER,
412 p_wip_entity_type NUMBER,
413 p_repetitive_schedule_id NUMBER DEFAULT NULL,
414 x_return_status OUT NOCOPY VARCHAR2,
415 x_msg_data OUT NOCOPY VARCHAR2) IS
416 BEGIN
417 wip_picking_pvt.cancel_comp_allocations(p_wip_entity_id => p_wip_entity_id,
418 p_operation_seq_num => p_operation_seq_num,
419 p_inventory_item_id => p_inventory_item_id,
420 p_wip_entity_type => p_wip_entity_type,
421 p_repetitive_schedule_id => p_repetitive_schedule_id,
422 x_return_status => x_return_status,
423 x_msg_data => x_msg_data);
424 END cancel_comp_allocations;
425
426
427 procedure reduce_comp_allocations(p_comp_tbl IN allocate_comp_tbl_t,
428 p_wip_entity_type NUMBER,
429 p_organization_id NUMBER,
430 x_return_status OUT NOCOPY VARCHAR2,
431 x_msg_data OUT NOCOPY VARCHAR2) IS
432 BEGIN
433 wip_picking_pvt.reduce_comp_allocations(p_comp_tbl => p_comp_tbl,
434 p_wip_entity_type => p_wip_entity_type,
435 p_organization_id => p_organization_id,
436 x_return_status => x_return_status,
437 x_msg_data => x_msg_data);
438 END reduce_comp_allocations;
439
440
441 procedure allocate(p_alloc_tbl IN OUT NOCOPY allocate_tbl_t,
442 p_days_to_alloc NUMBER := NULL, --only used for rep scheds
443 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
444 p_start_date DATE DEFAULT NULL,
445 p_cutoff_date DATE,
446 p_operation_seq_num_low NUMBER DEFAULT NULL,
447 p_operation_seq_num_high NUMBER DEFAULT NULL,
448 p_wip_entity_type NUMBER,
449 p_organization_id NUMBER,
450 p_pick_grouping_rule_id NUMBER := NULL,
451 p_print_pick_slip VARCHAR2 DEFAULT NULL,
452 p_plan_tasks BOOLEAN DEFAULT NULL,
453 x_conc_req_id OUT NOCOPY NUMBER,
454 x_mo_req_number OUT NOCOPY VARCHAR2,
455 x_return_status OUT NOCOPY VARCHAR2,
459 p_days_to_alloc => p_days_to_alloc,
456 x_msg_data OUT NOCOPY VARCHAR2) IS
457 BEGIN
458 wip_picking_pvt.allocate(p_alloc_tbl => p_alloc_tbl,
460 p_auto_detail_flag => p_auto_detail_flag,
461 p_start_date => p_start_date,
462 p_cutoff_date => p_cutoff_date,
463 p_operation_seq_num_low => p_operation_seq_num_low,
464 p_operation_seq_num_high => p_operation_seq_num_high,
465 p_wip_entity_type => p_wip_entity_type,
466 p_organization_id => p_organization_id,
467 p_pick_grouping_rule_id => p_pick_grouping_rule_id,
468 p_print_pick_slip => p_print_pick_slip,
469 p_plan_tasks => p_plan_tasks,
470 x_conc_req_id => x_conc_req_id,
471 x_mo_req_number => x_mo_req_number,
472 x_return_status => x_return_status,
473 x_msg_data => x_msg_data);
474 END allocate;
475
476
477
478 procedure allocate_comp(p_alloc_comp_tbl IN OUT NOCOPY allocate_comp_tbl_t,
479 p_days_to_alloc NUMBER DEFAULT NULL, --only used for rep scheds
480 p_auto_detail_flag VARCHAR2 DEFAULT NULL,
481 p_cutoff_date DATE,
482 p_wip_entity_type NUMBER,
483 p_organization_id NUMBER,
484 p_pick_grouping_rule_id NUMBER := NULL,
485 p_print_pick_slip VARCHAR2 DEFAULT NULL,
486 p_plan_tasks BOOLEAN DEFAULT NULL,
487 x_conc_req_id OUT NOCOPY NUMBER,
488 x_mo_req_number OUT NOCOPY VARCHAR2,
489 x_return_status OUT NOCOPY VARCHAR2,
490 x_msg_data OUT NOCOPY VARCHAR2) IS
491 BEGIN
492 wip_picking_pvt.allocate_comp(p_alloc_comp_tbl => p_alloc_comp_tbl,
493 p_days_to_alloc => p_days_to_alloc, --only used for rep scheds
494 p_auto_detail_flag => p_auto_detail_flag,
495 p_cutoff_date => p_cutoff_date,
496 p_wip_entity_type => p_wip_entity_type,
497 p_organization_id => p_organization_id,
498 p_pick_grouping_rule_id => p_pick_grouping_rule_id,
499 p_print_pick_slip => p_print_pick_slip,
500 p_plan_tasks => p_plan_tasks,
501 x_conc_req_id => x_conc_req_id,
502 x_mo_req_number => x_mo_req_number,
503 x_return_status => x_return_status,
504 x_msg_data => x_msg_data);
505 END allocate_comp;
506
507
508 /*
509 This function replaces the quantity_allocated column in WRO.
510 Note: for pull components:
511 l_quantity_allocated := l_mtrl_quantity - l_quantity_issued
512 but when calculating open_quantity:
513 open_qty = required_quantity - quantity_issued - Quantity_Allocated()
514 so, the quantity_issued cancels and is not considered for open_qty
515 */
516 Function quantity_allocated(p_wip_entity_id IN NUMBER,
517 p_operation_seq_num IN NUMBER,
518 p_organization_id IN NUMBER,
519 p_inventory_item_id IN NUMBER,
520 p_repetitive_schedule_id IN NUMBER DEFAULT NULL,
521 p_quantity_issued IN NUMBER DEFAULT NULL)
522 return NUMBER
523 is
524 l_quantity_allocated number;
525 l_mtrl_quantity number;
526 l_quantity_issued number := NULL;
527 l_txn_type_id number;
528 l_dummy2 VARCHAR2(1);
529 l_wro_quantity_allocated number := 0 ; /* 5468293 */
530 l_dummy number := 0 ; /* 5468293 */
531 begin
532 begin
533 select distinct mtrl.transaction_type_id
534 into l_txn_type_id
535 from MTL_TXN_REQUEST_LINES mtrl
536 where
537 mtrl.TXN_SOURCE_ID = p_wip_entity_id and
538 mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
539 mtrl.organization_id = p_organization_id and
540 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id;
541 exception
542 when TOO_MANY_ROWS then
543 wip_logger.log('Error occured in wip_picking_pub.quantity_allocated():', l_dummy2);
544 wip_logger.log(' Inconsistent transaction_type_id in rows of MTRL table!', l_dummy2);
545 -- Bug 5336791. saugupta 17th-Jun-2006
546 -- When calling wip_picking_pub.quantity_allocated for a newly released
547 -- repetitive schedule for which no component picking move order lines exist,
548 -- it is throwing a NO_DATA_FOUND exception.
549 when NO_DATA_FOUND then
550 wip_logger.log('Function wip_picking_pub.quantity_allocated(): No data found', l_dummy2);
551 return 0;
552 end;
553
554 l_quantity_issued := p_quantity_issued;
555 if l_quantity_issued is null then
556 select quantity_issued into l_quantity_issued
557 from WIP_REQUIREMENT_OPERATIONS
558 where wip_entity_id = p_wip_entity_id
559 and operation_seq_num = p_operation_seq_num
560 and repetitive_schedule_id = p_repetitive_schedule_id
561 and organization_id = p_organization_id
562 and inventory_item_id = p_inventory_item_id;
563 end if;
564
565 begin
569 from MTL_TXN_REQUEST_LINES mtrl
566 if (l_txn_type_id = INV_GLOBALS.G_TYPE_XFER_ORDER_WIP_ISSUE) then
567 select sum(nvl(mtrl.quantity,0) - nvl(mtrl.quantity_delivered,0))
568 into l_quantity_allocated
570 where
571 mtrl.TXN_SOURCE_ID = p_wip_entity_id and
572 mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
573 ( p_repetitive_schedule_id is null or
574 mtrl.reference_id = p_repetitive_schedule_id) and
575 mtrl.organization_id = p_organization_id and
576 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
577 -- preapproved status or open lines
578 mtrl.line_status = INV_GLOBALS.G_TO_STATUS_PREAPPROVED
579 group by mtrl.organization_id, mtrl.TXN_SOURCE_ID,
580 mtrl.TXN_SOURCE_LINE_ID, mtrl.INVENTORY_ITEM_ID;
581 else
582 select sum(nvl(mtrl.quantity,0))
583 into l_mtrl_quantity
584 from MTL_TXN_REQUEST_LINES mtrl
585 where
586 mtrl.TXN_SOURCE_ID = p_wip_entity_id and
587 mtrl.TXN_SOURCE_LINE_ID = p_operation_seq_num and
588 ( p_repetitive_schedule_id is null or
589 mtrl.reference_id = p_repetitive_schedule_id) and
590 mtrl.organization_id = p_organization_id and
591 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
592 mtrl.line_status in (INV_GLOBALS.G_TO_STATUS_CLOSED,
593 INV_GLOBALS.G_TO_STATUS_PREAPPROVED) -- preapproved or closed
594 group by mtrl.organization_id, mtrl.TXN_SOURCE_ID,
595 mtrl.TXN_SOURCE_LINE_ID, mtrl.INVENTORY_ITEM_ID;
596
597 /* Start for the Fix for Bug#5468293.
598 Check for Cross Docking.
599 backorder_deliver_detail_id is wip_entity_id for cross dock.
600 However Operation info is not populated in mtrl.
601 Hence get it from WRO.
602 */
603 begin
604
605 select 1
606 into l_dummy
607 from dual
608 where exists
609 ( select 1
610 from MTL_TXN_REQUEST_LINES mtrl
611 where
612 mtrl.backorder_delivery_detail_id = p_wip_entity_id and
613 mtrl.TXN_SOURCE_LINE_ID is null and
614 ( p_repetitive_schedule_id is null or
615 mtrl.reference_id = p_repetitive_schedule_id) and
616 mtrl.crossdock_type = 2 and -- WIP
617 mtrl.organization_id = p_organization_id and
618 mtrl.INVENTORY_ITEM_ID = p_inventory_item_id and
619 mtrl.line_status in (INV_GLOBALS.G_TO_STATUS_CLOSED,
620 INV_GLOBALS.G_TO_STATUS_PREAPPROVED)) ; -- preapproved or closed
621
622 select quantity_allocated
623 into l_wro_quantity_allocated
624 from wip_requirement_operations
625 where wip_entity_id = p_wip_entity_id
626 and inventory_item_id = p_inventory_item_id
627 and operation_seq_num = p_operation_seq_num
628 and organization_id = p_organization_id
629 and nvl(repetitive_schedule_id, -1 ) = nvl(p_repetitive_schedule_id, -1) ;
630
631
632 l_mtrl_quantity := l_wro_quantity_allocated ;
633
634 exception
635 when no_data_found then
636 l_wro_quantity_allocated := 0 ;
637 end ;
638 /* End for the #5468293 */
639
640 l_quantity_allocated := l_mtrl_quantity - l_quantity_issued;
641 end if;
642 exception
643 when NO_DATA_FOUND then
644 l_quantity_allocated := 0;
645 end;
646
647 return l_quantity_allocated;
648 end;
649
650
651 function Is_Component_Pick_Released(p_wip_entity_id in number,
652 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
653 p_org_id in NUMBER,
654 p_operation_seq_num in NUMBER,
655 p_inventory_item_id in NUMBER) return BOOLEAN IS
656 l_dummy NUMBER := 0;
657 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
658
659 Begin
660 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
661 begin
662 Select 1 Into l_dummy
663 from wip_requirement_operations
664 Where wip_entity_id = p_wip_entity_id
665 And Organization_id = p_org_id
666 And operation_seq_num = p_operation_seq_num
667 And inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
668 And quantity_backordered is not null;
669 exception
670 when no_data_found then
671 null;
672 end;
673 Else
674 begin
675 Select 1 Into l_dummy
676 from wip_requirement_operations
680 And operation_seq_num = p_operation_seq_num
677 Where wip_entity_id = p_wip_entity_id
678 And repetitive_schedule_id = p_repetitive_schedule_id
679 And Organization_id = p_org_id
681 And inventory_item_id = nvl(p_inventory_item_id, inventory_item_id)
682 And quantity_backordered is not null;
683 exception
684 when no_data_found then
685 null;
686 end;
687 End if;
688
689 return (l_dummy = 1);
690
691 EXCEPTION
692 when others then
693 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
694 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.Is_Component_Pick_Released: ' || SQLERRM);
695 raise fnd_api.g_exc_unexpected_error;
696
697 End Is_Component_Pick_Released;
698
699 Function Is_Job_Pick_Released(p_wip_entity_id in number,
700 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
701 p_org_id in NUMBER) RETURN BOOLEAN IS
702 l_dummy NUMBER := 0;
703 Begin
704
705 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
706 begin
707 Select 1 Into l_dummy
708 From dual
709 Where exists
710 (select 1
711 from wip_requirement_operations
712 Where wip_entity_id = p_wip_entity_id
713 And Organization_id = p_org_id
714 And quantity_backordered is not null);
715 exception
716 when no_data_found then
717 null;
718 end;
719 Else
720 begin
721 Select 1 Into l_dummy
722 From dual
723 Where exists
724 (select 1
725 from wip_requirement_operations
726 Where wip_entity_id = p_wip_entity_id
727 And repetitive_schedule_id = p_repetitive_schedule_id
728 And Organization_id = p_org_id
729 And quantity_backordered is not null);
730 exception
731 when no_data_found then
732 null;
733 end;
734 End if;
735 Return( l_dummy = 1);
736 EXCEPTION
737 when others then
738 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
739 fnd_message.set_token('ERROR_TEXT', 'wip_picking_pub.Is_Job_Pick_Released: ' || SQLERRM);
740 raise fnd_api.g_exc_unexpected_error;
741 End Is_Job_Pick_Released;
742
743 Procedure Update_Requirement_SubinvLoc(p_wip_entity_id number,
744 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
745 p_operation_seq_num in NUMBER,
746 p_supply_subinventory in VARCHAR2,
747 p_supply_locator_id in NUMBER,
748 x_return_status OUT NOCOPY VARCHAR2,
749 x_msg_data OUT NOCOPY VARCHAR2) IS
750 l_entityType NUMBER;
751 l_sysDate DATE := sysdate;
752 l_userId NUMBER := fnd_global.user_id;
753 l_loginId NUMBER := fnd_global.login_id;
754
755 Begin
756 SAVEPOINT WIP_UPDATE_REQ_SUBINVLOC_START;
757 x_return_status := FND_API.G_RET_STS_SUCCESS;
758
759 --for repetitive, lot-based and discrete...
760 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
761 update wip_requirement_operations
762 set supply_subinventory = p_supply_subinventory,
763 supply_locator_id = p_supply_locator_id
764 where wip_entity_id = p_wip_entity_id
765 and operation_seq_num = p_operation_seq_num
766 and wip_supply_type in
767 (wip_constants.assy_pull, wip_constants.op_pull);
768 else
769 update wip_requirement_operations
770 set supply_subinventory = p_supply_subinventory,
771 supply_locator_id = p_supply_locator_id
772 where wip_entity_id = p_wip_entity_id
773 and operation_seq_num = p_operation_seq_num
774 and p_repetitive_schedule_id = p_repetitive_schedule_id
775 and wip_supply_type in
776 (wip_constants.assy_pull, wip_constants.op_pull);
777 end if;
778
779 exception
780 when no_data_found then
781 null;
782 when RECORDS_LOCKED then
783 ROLLBACK TO WIP_UPDATE_REQ_SUBINVLOC_START;
784 x_return_status := 'L';
785 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
786 x_msg_data := fnd_message.get;
787 when others then
788 ROLLBACK TO WIP_UPDATE_REQ_SUBINVLOC_START;
789 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
790 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
791 fnd_message.set_token('ERROR_TEXT',
792 'wip_utilities.Update_Requirement_SubinvLoc: ' || SQLERRM);
793 x_msg_data := fnd_message.get;
794 End Update_Requirement_SubinvLoc;
795
796 Procedure Update_Component_BackOrdQty(p_wip_entity_id number,
797 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
798 p_operation_seq_num in NUMBER,
799 p_new_component_qty in NUMBER,
800 p_inventory_item_id in NUMBER DEFAULT NULL,
801 x_return_status OUT NOCOPY VARCHAR2,
802 x_msg_data OUT NOCOPY VARCHAR2) IS
803 l_entityType NUMBER;
804 l_sysDate DATE := sysdate;
808 Begin
805 l_userId NUMBER := fnd_global.user_id;
806 l_loginId NUMBER := fnd_global.login_id;
807 l_dummy VARCHAR2(1);
809 SAVEPOINT WIP_COMP_BACKORDQTY_START;
810 x_return_status := FND_API.G_RET_STS_SUCCESS;
811
812 select entity_type
813 into l_entityType
814 from wip_entities
815 where wip_entity_id = p_wip_entity_id;
816
817 If (l_entityType = wip_constants.flow) then
818 Return;
819 End if;
820
821 --for repetitive, lot-based and discrete...
822 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
823 Update wip_requirement_operations wro
824 set quantity_backordered = GREATEST(p_new_component_qty
825 -- replaced wro.quantity_allocated with function quantity_allocated
826 -- - quantity_issued - quantity_allocated , 0)
827 - quantity_issued - wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
828 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED) , 0)
829 where wip_entity_id = p_wip_entity_id
830 and operation_seq_num = p_operation_seq_num
831 and inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
832 and (
833 ('Y' = (select allocate_backflush_components from wip_parameters wp
834 where organization_id = wro.organization_id)
835 and wip_supply_type in
836 (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
837 or wip_supply_type = wip_constants.push
838 );
839 else
840 Update wip_requirement_operations wro
841 set quantity_backordered = GREATEST(p_new_component_qty
842 -- replaced wro.quantity_allocated with function quantity_allocated
843 -- - quantity_issued - quantity_allocated , 0)
844 - quantity_issued - wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
845 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED) , 0)
846 where wip_entity_id=p_wip_entity_id
847 and operation_seq_num=p_operation_seq_num
848 and p_repetitive_schedule_id = p_repetitive_schedule_id
849 and inventory_item_id = NVL(p_inventory_item_id, inventory_item_id)
850 and (
851 ('Y' = (select allocate_backflush_components
852 from wip_parameters wp
853 where organization_id = wro.organization_id)
854 and wip_supply_type in
855 (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
856 or wip_supply_type = wip_constants.push
857 );
858 end if;
859
860 exception
861 when RECORDS_LOCKED then
862 ROLLBACK TO WIP_COMP_BACKORDQTY_START;
863 x_return_status := 'L';
864 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
865 x_msg_data := fnd_message.get;
866 when others then
867 ROLLBACK TO WIP_COMP_BACKORDQTY_START;
868 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
869 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
870 fnd_message.set_token('ERROR_TEXT', 'wip_picking_private.Update_Component_BackOrdQty: '
871 || SQLERRM);
872 x_msg_data := fnd_message.get;
873 End Update_Component_BackOrdQty;
874
875 Procedure Update_Job_BackOrdQty(p_wip_entity_id number,
876 p_repetitive_schedule_id in NUMBER DEFAULT NULL,
877 p_new_job_qty in NUMBER,
878 x_return_status OUT NOCOPY VARCHAR2,
879 x_msg_data OUT NOCOPY VARCHAR2) IS
880 l_entityType NUMBER;
881 l_sysDate DATE := sysdate;
882 l_userId NUMBER := fnd_global.user_id;
883 l_loginId NUMBER := fnd_global.login_id;
884 Begin
885 SAVEPOINT WIP_JOB_BACKORDQTY_START;
886 x_return_status := FND_API.G_RET_STS_SUCCESS;
887
888 select entity_type
889 into l_entityType
890 from wip_entities
891 where wip_entity_id = p_wip_entity_id;
892
893 If (l_entityType = wip_constants.flow) then
894 Return;
895 End if;
896
897 --for repetitive, lot-based and discrete
898 if (p_repetitive_schedule_id is null or p_repetitive_schedule_id = 0) then
899 Update wip_requirement_operations wro
900 set quantity_backordered = GREATEST(p_new_job_qty* quantity_per_assembly -
901 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)
903 - quantity_issued, 0)
904 where wip_entity_id=p_wip_entity_id
905 and quantity_backordered is not null
906 and (
907 ('Y' = (select allocate_backflush_components
908 from wip_parameters wp
909 where organization_id = wro.organization_id)
910 and wip_supply_type in
911 (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
912 or wip_supply_type = wip_constants.push
913 );
914 else
915 Update wip_requirement_operations wro
916 set quantity_backordered = GREATEST(p_new_job_qty* quantity_per_assembly
917 - wip_picking_pub.quantity_allocated(WRO.WIP_ENTITY_ID, WRO.OPERATION_SEQ_NUM,
918 WRO.ORGANIZATION_ID, WRO.INVENTORY_ITEM_ID, WRO.REPETITIVE_SCHEDULE_ID,QUANTITY_ISSUED)
919 - quantity_issued, 0)
920 where wip_entity_id=p_wip_entity_id
921 and repetitive_schedule_id = p_repetitive_schedule_id
922 and quantity_backordered is not null
923 and (
924 ('Y' = (select allocate_backflush_components
925 from wip_parameters wp
926 where organization_id = wro.organization_id)
927 and wro.wip_supply_type in
928 (wip_constants.push, wip_constants.op_pull, wip_constants.assy_pull))
929 or wro.wip_supply_type = wip_constants.push
930 ) ;
931 end if;
932
933 exception
934 when RECORDS_LOCKED then
935 ROLLBACK TO WIP_JOB_BACKORDQTY_START;
936 x_return_status := 'L';
937 fnd_message.set_name('FND', 'FORM_UNABLE_TO_RESERVE_RECORD');
938 x_msg_data := fnd_message.get;
939 when others then
940 ROLLBACK TO WIP_JOB_BACKORDQTY_START;
941 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
942 fnd_message.set_name('WIP', 'WIP_UNEXPECTED_ERROR');
943 fnd_message.set_token('ERROR_TEXT', 'wip_picking_private.Update_Job_BackOrdQty: '
944 || SQLERRM);
945 x_msg_data := fnd_message.get;
946 End Update_Job_BackOrdQty;
947
948 end wip_picking_pub;