1 PACKAGE BODY WIP_Default_Shopfloormove AS
2 /* $Header: WIPDSFMB.pls 120.14.12020000.2 2013/04/13 00:57:49 ankohli ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'WIP_Default_Shopfloormove';
7
8 -- Package global used within the package.
9
10 g_ShopFloorMove_rec WIP_Transaction_PUB.Shopfloormove_Rec_Type;
11 g_OSP_rec WIP_Transaction_PUB.Res_Rec_Type;
12 g_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type;
13 g_osp_mov_details WIP_Transaction_PUB.OSP_Move_Details_Type;
14
15 -- Get functions.
16
17 PROCEDURE get_we_attr
18 IS
19 l_Wip_Entities_rec WIP_Work_Order_PUB.Wip_Entities_Rec_Type :=
20 WIP_Work_Order_PUB.G_MISS_WIP_ENTITIES_REC;
21 BEGIN
22
23 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL THEN
24
25 l_Wip_Entities_rec := WIP_Wip_Entities_Util.Query_Row(g_ShopFloorMove_rec.wip_entity_id);
26 IF g_ShopFloorMove_rec.wip_entity_name IS NULL THEN
27 g_Wip_Entities_rec.wip_entity_name := l_Wip_Entities_rec.wip_entity_name;
28 END IF;
29 IF g_ShopFloorMove_rec.primary_item_id IS NULL THEN
30 g_Wip_Entities_rec.primary_item_id := l_Wip_Entities_rec.primary_item_id;
31 END IF;
32 IF g_ShopFloorMove_rec.entity_type IS NULL THEN
33 g_Wip_Entities_rec.entity_type := l_Wip_Entities_rec.entity_type;
34 END IF;
35
36 END IF;
37
38 EXCEPTION
39 WHEN OTHERS THEN
40 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_we_attr');
41 END get_we_attr;
42
43 PROCEDURE validate_default_sub_loc (p_wip_entity_id IN NUMBER,
44 p_org_id IN NUMBER,
45 p_line_id IN NUMBER,
46 x_valid_ez_complete OUT NOCOPY NUMBER) IS
47 BEGIN
48
49 /* you must have a default completion subinventory/locator
50 for move-completions and return-moves
51 */
52
53 select count(*) into x_valid_ez_complete
54 from WIP_ENTITIES WE
55 where we.wip_entity_id = p_wip_entity_id
56 and we.organization_id = p_org_id
57 and (((we.entity_type = WIP_CONSTANTS.DISCRETE)
58 and not exists (select 'X'
59 from WIP_DISCRETE_JOBS DJ
60 where DJ.WIP_ENTITY_ID = p_wip_entity_id
61 and DJ.ORGANIZATION_ID = p_org_id
62 and DJ.COMPLETION_SUBINVENTORY IS NOT NULL))
63 or (we.entity_type = WIP_CONSTANTS.REPETITIVE)
64 and not exists (select 'X' from WIP_REPETITIVE_ITEMS WRI
65 where WRI.WIP_ENTITY_ID = p_wip_entity_id
66 and WRI.ORGANIZATION_ID = p_org_id
67 and WRI.LINE_ID = p_line_id
68 and WRI.COMPLETION_SUBINVENTORY IS NOT NULL));
69
70 exception
71 when others then
72 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','validate_default_sub_loc');
73
74 END validate_default_sub_loc;
75
76 PROCEDURE validate_lot_control (p_wip_entity_id IN NUMBER,
77 p_org_id IN NUMBER,
78 p_line_id IN NUMBER,
79 x_valid_ez_complete OUT NOCOPY NUMBER) IS
80 BEGIN
81
82
83 -- If it's discrete, and does not have a default lot then can't move_complete/return
84 -- if it's repetitive then can't move_complete/return_move
85
86 select count(*) into x_valid_ez_complete
87 from wip_entities we, mtl_system_items msi
88 where we.wip_entity_id = p_wip_entity_id
89 and we.organization_id = p_org_id
90 and msi.inventory_item_id = we.primary_item_id
91 and msi.organization_id = we.organization_id
92 and msi.lot_control_code = WIP_CONSTANTS.LOT
93 and ( ( we.entity_type = WIP_CONSTANTS.DISCRETE
94 and exists (select 'X'
95 from wip_discrete_jobs wdj
96 where wdj.wip_entity_id = we.wip_entity_id
97 and wdj.organization_id = we.organization_id
98 and wdj.lot_number is null))
99 or (we.entity_type = WIP_CONSTANTS.REPETITIVE) );
100
101 exception
102 when others then
103 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','validate_lot_control');
104 END validate_lot_control;
105
106 PROCEDURE validate_serial_control(p_wip_entity_id IN NUMBER,
107 p_org_id IN NUMBER,
108 x_valid_ez_complete OUT NOCOPY NUMBER) IS
109 BEGIN
110
111 select count(*) into x_valid_ez_complete
112 from MTL_SYSTEM_ITEMS msi, WIP_ENTITIES we
113 where we.organization_id = p_org_id
114 and we.wip_entity_id = p_wip_entity_id
115 and msi.inventory_item_id = we.primary_item_id
116 and msi.organization_id = we.organization_id
117 and msi.serial_number_control_code in (2,5);
118
119 exception
120 when others then
121 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','validate_serial_control');
122 END validate_serial_control;
123
124 PROCEDURE validate_shop_floor_status (p_wip_entity_id IN NUMBER,
125 p_org_id IN NUMBER,
126 p_line_id IN NUMBER,
127 p_rep_sched_id IN NUMBER,
128 x_valid_ez_complete OUT NOCOPY NUMBER) IS
129 BEGIN
130
131
132 select count(*) into x_valid_ez_complete
133 from WIP_SHOP_FLOOR_STATUSES wsfs, WIP_OPERATIONS wo,
134 WIP_SHOP_FLOOR_STATUS_CODES wsfsc
135 where wsfs.wip_entity_id = p_wip_entity_id
136 and wsfs.organization_id = p_org_id
137 and nvl (wsfs.line_id, -1) = nvl (p_line_id, -1)
138 and wo.wip_entity_id = wsfs.wip_entity_id
139 and nvl (wo.repetitive_schedule_id, -1) = nvl (p_rep_sched_id, -1)
140 and wo.organization_id = wsfs.organization_id
141 and wo.next_operation_seq_num is null
142 and wo.operation_seq_num = wsfs.operation_seq_num
143 and wsfs.intraoperation_step_type=WIP_CONSTANTS.TOMOVE
144 and wsfsc.shop_floor_status_code = wsfs.shop_floor_status_code
145 and wsfsc.organization_id = wsfs.organization_id
146 and wsfsc.status_move_flag = 2
147 and nvl(wsfsc.disable_date, sysdate + 1) > sysdate;
148
149 exception
150 when others then
151 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','validate_shop_floor_status');
152 END validate_shop_floor_status;
153
154 PROCEDURE validate_item_revision (p_wip_entity_id IN NUMBER,
155 p_org_id IN NUMBER,
156 p_line_id IN NUMBER,
157 p_rep_sched_id IN NUMBER,
158 x_valid_ez_complete OUT NOCOPY NUMBER) IS
159 BEGIN
160
161 /*item revision must exist as a BOM revision */
162 /* Fix for bug 4095809: Need to do the comparison only if a bill
163 is referenced in the job ie., common_bill_sequence_id IS NOT NULL */
164 select count(*) into x_valid_ez_complete
165 from wip_entities we
166 where we.wip_entity_id = p_wip_entity_id
167 and we.organization_id = p_org_id
168 and exists (
169 select 'X'
170 from MTL_SYSTEM_ITEMS msi
171 where msi.organization_id = p_org_id
172 and msi.inventory_item_id = we.primary_item_id
173 and msi.revision_qty_control_code=2)
174 and ( ( we.entity_type = WIP_CONSTANTS.DISCRETE
175 and not exists (
176 select 'X'
177 from WIP_DISCRETE_JOBS wdj, MTL_ITEM_REVISIONS MIR
178 where wdj.organization_id = we.organization_id
179 and wdj.wip_entity_id = we.wip_entity_id
180 and mir.organization_id = wdj.organization_id
181 and mir.inventory_item_id = we.primary_item_id
182 and ( wdj.common_bom_sequence_id is null
183 or ( wdj.common_bom_sequence_id is not null
184 and mir.revision = wdj.bom_revision
185 )
186 )
187 ))
188 or (we.entity_type = WIP_CONSTANTS.REPETITIVE
189 and not exists (
190 select 'X'
191 from WIP_REPETITIVE_SCHEDULES WRS, MTL_ITEM_REVISIONS MIR
192 where wrs.organization_id = we.organization_id
193 and wrs.repetitive_schedule_id = p_rep_sched_id
194 and mir.inventory_item_id = we.primary_item_id
195 and mir.organization_id = we.organization_id
196 and mir.inventory_item_id = we.primary_item_id
197 and ( wrs.common_bom_sequence_id is null
198 or ( wrs.common_bom_sequence_id is not null
199 and mir.revision = wrs.bom_revision
200 )
201 )
202 )) );
203
204 exception
205 when others then
206 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','validate_item_revision');
207 END validate_item_revision;
208
209 PROCEDURE validate_last_op (p_wip_entity_id IN NUMBER,
210 p_org_id IN NUMBER,
211 p_line_id IN NUMBER,
212 p_rep_sched_id IN NUMBER,
213 x_valid_ez_complete OUT NOCOPY NUMBER) IS
214 begin
215
216 validate_default_sub_loc (p_wip_entity_id, p_org_id, p_line_id,
217 x_valid_ez_complete);
218 if (x_valid_ez_complete <> VALID) then
219 return ;
220 end if;
221
222 validate_lot_control (p_wip_entity_id, p_org_id, p_line_id,
223 x_valid_ez_complete);
224 if (x_valid_ez_complete <> VALID) then
225 return ;
226 end if;
227
228 validate_serial_control (p_wip_entity_id, p_org_id,
229 x_valid_ez_complete);
230 if (x_valid_ez_complete <> VALID) then
231 return ;
232 end if;
233
234 validate_shop_floor_status (p_wip_entity_id, p_org_id, p_line_id,
235 p_rep_sched_id, x_valid_ez_complete);
236 if (x_valid_ez_complete <> VALID) then
237 return ;
238 end if;
239
240 validate_item_revision (p_wip_entity_id, p_org_id, p_line_id,
241 p_rep_sched_id, x_valid_ez_complete);
242
243 END validate_last_op;
244
245 PROCEDURE get_actual_move_ops(p_correction_type IN NUMBER,
246 p_org_id IN NUMBER,
247 p_wip_entity_id IN NUMBER,
248 p_operation_seq_num IN NUMBER,
249 p_rep_sched_id IN NUMBER,
250 p_line_id IN NUMBER,
251 p_next_op IN OUT NOCOPY NUMBER,
252 p_current_op IN OUT NOCOPY NUMBER,
253 p_next_dept_id IN OUT NOCOPY NUMBER,
254 p_current_dept_id IN OUT NOCOPY NUMBER,
255 x_next_step OUT NOCOPY NUMBER,
256 x_current_step OUT NOCOPY NUMBER,
257 x_txn_type OUT NOCOPY NUMBER) IS
258
259 x_temp NUMBER;
260 x_valid_ez_complete NUMBER;
261 l_ncp_after_current_op NUMBER; /*Added for bug 6146597*/
262 l_lot_control_code NUMBER; /*Added for bug 8826087*/
263 l_shelf_life_code NUMBER; /*Added for bug 8826087*/
264
265 BEGIN
266 x_txn_type := WIP_CONSTANTS.MOVE_TXN;
267 if (p_correction_type = MOVE_FORWARD) then
268
269 if (p_next_op > p_current_op) then
270 x_current_step := WIP_CONSTANTS.QUEUE;
271 x_next_step := WIP_CONSTANTS.QUEUE;
272 else
273 validate_last_op(p_wip_entity_id, p_org_id, p_line_id,
274 p_rep_sched_id, x_valid_ez_complete);
275
276 /*Bug 6146597: Added the following query to check if there any non count point operations
277 after the OSP operation. The code reaches this point beacuse OSP operation
278 is the last count point operation */
279 select count(*)
280 into l_ncp_after_current_op
281 from wip_operations
282 where operation_seq_num > p_current_op
283 and count_point_type = 2
284 and wip_entity_id = p_wip_entity_id ;
285
286 /*Added for bug 8826087, check that if assembly is set to lot control and lot expiration is set to USER_DEFINED,
287 Do move transaction instead of easy completion to aviod error WIP_USER_DEF_EXP_NOT_ALLOW*/
288
289 begin
290 select msi.lot_control_code, msi.shelf_life_code
291 into l_lot_control_code, l_shelf_life_code
292 from mtl_system_items msi, wip_entities we
293 where we.wip_entity_id = p_wip_entity_id
294 and we.organization_id = p_org_id
295 and msi.organization_id = we.organization_id
296 and INVENTORY_ITEM_ID = we.primary_item_id;
297 exception
298 when others then
299 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_actual_move_ops: select from MSI,WE');
300 end;
301
302
303
304 /*Fix for bug 8826087*/
305 if (l_ncp_after_current_op > 0 or (l_lot_control_code = WIP_CONSTANTS.LOT and l_shelf_life_code = WIP_CONSTANTS.USER_DEFINED_EXP)) then
306 /*To make the completion invalid because there are Non count point operation
307 after this operation*/
308 x_valid_ez_complete := -999 ;
309 end if ;
310
311
312 if (x_valid_ez_complete = VALID) then
313 x_txn_type := WIP_CONSTANTS.COMP_TXN;
314 end if;
315
316 p_next_op := p_current_op;
317 p_next_dept_id := p_current_dept_id;
318 x_current_step := WIP_CONSTANTS.QUEUE;
319 x_next_step := WIP_CONSTANTS.TOMOVE;
320
321 end if;
322 else
323 if (p_next_op > p_current_op) then
324 x_temp := p_current_op;
325 p_current_op := p_next_op;
326 p_next_op := x_temp;
327 x_temp := p_current_dept_id;
328 p_current_dept_id := p_next_dept_id;
329 p_next_dept_id := x_temp;
330 x_current_step := WIP_CONSTANTS.QUEUE;
331 x_next_step := WIP_CONSTANTS.QUEUE;
332 else
333 validate_last_op(p_wip_entity_id, p_org_id, p_line_id,
334 p_rep_sched_id, x_valid_ez_complete);
335 if (x_valid_ez_complete = VALID) then
336 x_txn_type := WIP_CONSTANTS.RET_TXN;
337 end if;
338 p_next_op := p_current_op;
339 p_next_dept_id := p_current_dept_id;
340 x_current_step := WIP_CONSTANTS.TOMOVE;
341 x_next_step := WIP_CONSTANTS.QUEUE;
342 end if;
343 end if;
344
345 exception
346 when others then
347 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_actual_move_ops');
348 END get_actual_move_ops;
349
350 PROCEDURE get_osp_move_details
351 IS
352 l_transaction_type NUMBER := FND_API.G_MISS_NUM;
353 l_transaction_quantity NUMBER := FND_API.G_MISS_NUM;
354 l_primary_quantity NUMBER := FND_API.G_MISS_NUM;
355 l_fm_operation_seq_num NUMBER := FND_API.G_MISS_NUM;
356 l_fm_intraop_step_type NUMBER := FND_API.G_MISS_NUM;
357 l_fm_department_id NUMBER := FND_API.G_MISS_NUM;
358 l_to_operation_seq_num NUMBER := FND_API.G_MISS_NUM;
359 l_to_intraop_step_type NUMBER := FND_API.G_MISS_NUM;
360 l_to_department_id NUMBER := FND_API.G_MISS_NUM;
361 l_correction_type NUMBER;
362 l_wip_entity_id NUMBER := FND_API.G_MISS_NUM;
363 l_organization_id NUMBER := FND_API.G_MISS_NUM;
364 l_osp_op_seq NUMBER := FND_API.G_MISS_NUM;
365 l_line_id NUMBER := FND_API.G_MISS_NUM;
366 l_rep_sch_id NUMBER := FND_API.G_MISS_NUM;
367 l_cur_op_seq NUMBER := FND_API.G_MISS_NUM;
368 l_cur_qty_in_queue NUMBER := FND_API.G_MISS_NUM;
369 l_cur_qty_to_move NUMBER := FND_API.G_MISS_NUM;
370 l_next_op_seq NUMBER := FND_API.G_MISS_NUM;
371 l_next_qty_in_queue NUMBER := FND_API.G_MISS_NUM;
372 l_next_qty_to_move NUMBER := FND_API.G_MISS_NUM;
373 l_basis_type NUMBER := FND_API.G_MISS_NUM;
374 l_neg_qty_indicator boolean;
375 BEGIN
376
377 l_wip_entity_id := g_OSP_rec.wip_entity_id;
378 l_organization_id := g_OSP_rec.organization_id;
379 l_osp_op_seq := g_OSP_rec.operation_seq_num;
380 l_line_id := g_OSP_rec.line_id;
381 l_rep_sch_id := g_OSP_rec.repetitive_schedule_id;
382 l_basis_type := g_OSP_rec.basis_type;
383
384 SELECT Decode(msi.outside_operation_uom_type,
385 'ASSEMBLY' , rti.primary_quantity,
386 Decode(Nvl(g_OSP_rec.usage_rate_or_amount,0) ,
387 0 , 0,
388 (rti.primary_quantity/g_OSP_rec.usage_rate_or_amount)))
389 INTO
390 l_primary_quantity
391 FROM
392 mtl_system_items msi,
393 rcv_transactions_interface rti
394 WHERE rti.interface_transaction_id = g_OSP_rec.source_line_id
395 AND msi.inventory_item_id = rti.item_id
396 AND msi.organization_id = g_OSP_rec.organization_id;
397
398 if(l_primary_quantity < 0) then
399 l_neg_qty_indicator := true;
400 l_primary_quantity := l_primary_quantity * -1;
401 else
402 l_neg_qty_indicator := false;
403 end if;
404 --at this point, l_primary_quantity is always positive
405 l_transaction_quantity := l_primary_quantity;
406
407 IF g_osp_mov_details.transaction_quantity IS NULL THEN
408 g_osp_mov_details.transaction_quantity := l_transaction_quantity;
409 END IF;
410
411 IF g_OSP_rec.action = WIP_Transaction_PUB.G_ACT_OSP_RET_TO_RCV
412 OR g_OSP_rec.action = WIP_Transaction_PUB.G_ACT_OSP_RET_TO_VEND
413 THEN
414 l_correction_type := MOVE_BACKWARD;
415 ELSIF g_OSP_rec.action = WIP_Transaction_PUB.G_ACT_OSP_CORRECT_TO_RCV
416 THEN
417 IF l_neg_qty_indicator
418 THEN
419 l_correction_type := MOVE_FORWARD;
420 ELSE
421 l_correction_type := MOVE_BACKWARD;
422 END IF;
423 ELSE
424 IF l_neg_qty_indicator
425 THEN
426 l_correction_type := MOVE_BACKWARD;
427 ELSE
428 l_correction_type := MOVE_FORWARD;
429 END IF;
430 END IF;
431
432 BEGIN
433 SELECT wop.operation_seq_num,
434 wop.department_id,
435 nvl(nwop.operation_seq_num, -1),
436 nvl(nwop.department_id, -1),
437 wop.operation_seq_num,
438 wop.quantity_in_queue,
439 decode(wop.quantity_waiting_to_move, 0,
440 wop.quantity_completed,
441 wop.quantity_waiting_to_move),
442 nvl(nwop.operation_seq_num, -1),
443 nwop.quantity_in_queue,
444 decode(nwop.quantity_waiting_to_move, 0,
445 nwop.quantity_completed,
446 nwop.quantity_waiting_to_move)
447 INTO l_fm_operation_seq_num,
448 l_fm_department_id,
449 l_to_operation_seq_num,
450 l_to_department_id,
451 l_cur_op_seq,
452 l_cur_qty_in_queue,
453 l_cur_qty_to_move,
454 l_next_op_seq,
455 l_next_qty_in_queue,
456 l_next_qty_to_move
457 /*Bug 6146597 Start*/
458 FROM (select wip_entity_id,
459 organization_id,
460 operation_seq_num,
461 department_id,
462 quantity_in_queue,
463 quantity_waiting_to_move,
464 quantity_completed,
465 repetitive_schedule_id
466 from wip_operations
467 where wip_entity_id= l_wip_entity_id
468 and nvl (repetitive_schedule_id, -1) = nvl (l_rep_sch_id, -1)
469 and organization_id= l_organization_id
470 and operation_seq_num > l_osp_op_seq
471 and count_point_type = 1
472 and ROWNUM=1
473 ORDER BY operation_seq_num) nwop,
474 /*Bug 6146597 End*/
475 wip_operations wop
476 WHERE wop.organization_id = l_organization_id
477 and wop.wip_entity_id = l_wip_entity_id
478 and wop.operation_seq_num = l_osp_op_seq
479 and nvl (wop.repetitive_schedule_id, -1) =
480 nvl (l_rep_sch_id, -1)
481 and wop.organization_id = nwop.organization_id(+)
482 and wop.wip_entity_id = nwop.wip_entity_id(+)
483 /*Bug 6146597 and wop.next_operation_seq_num = nwop.operation_seq_num(+)*/
484 and nvl(wop.repetitive_schedule_id, -1) =
485 nvl(nwop.repetitive_schedule_id(+), -1);
486
487 exception
488 when others then
489 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_osp_move_details');
490 end ;
491
492 get_actual_move_ops(
493 p_correction_type =>l_correction_type,
494 p_org_id =>l_organization_id,
495 p_wip_entity_id =>l_wip_entity_id,
496 p_operation_seq_num =>l_osp_op_seq,
497 p_rep_sched_id =>l_rep_sch_id,
498 p_line_id =>l_line_id,
499 p_next_op =>l_to_operation_seq_num,
500 p_current_op =>l_fm_operation_seq_num,
501 p_next_dept_id =>l_to_department_id,
502 p_current_dept_id =>l_fm_department_id,
503 x_next_step =>l_to_intraop_step_type,
504 x_current_step =>l_fm_intraop_step_type,
505 x_txn_type =>l_transaction_type);
506
507 -- set the primary quantity to job qty if basis type is LOT based
508 -- fix bug 1832111
509 if (l_basis_type = 2) then -- lot based resource
510 if (l_fm_operation_seq_num = l_cur_op_seq) then
511 if (l_fm_intraop_step_type = WIP_CONSTANTS.QUEUE) then
512 l_primary_quantity := l_cur_qty_in_queue;
513 elsif (l_fm_intraop_step_type = WIP_CONSTANTS.TOMOVE) then
514 l_primary_quantity := l_cur_qty_to_move;
515 end if;
516 elsif (l_fm_operation_seq_num = l_next_op_seq) then
517 if (l_fm_intraop_step_type = WIP_CONSTANTS.QUEUE) then
518 l_primary_quantity := l_next_qty_in_queue;
519 elsif (l_fm_intraop_step_type = WIP_CONSTANTS.TOMOVE) then
520 l_primary_quantity := l_next_qty_to_move;
521 end if;
522 end if;
523 -- need to also change the transaction_quantity
524 g_osp_mov_details.transaction_quantity := l_primary_quantity;
525 end if; -- end of basis_type = 2 (lot based resource)
526
527 -- Default all the local variables.
528
529 IF g_osp_mov_details.move_direction IS NULL THEN
530 g_osp_mov_details.move_direction := l_correction_type;
531 END IF;
532
533 IF g_osp_mov_details.transaction_type IS NULL THEN
534 g_osp_mov_details.transaction_type := l_transaction_type;
535 END IF;
536
537 IF g_osp_mov_details.primary_quantity IS NULL THEN
538 g_osp_mov_details.primary_quantity := l_primary_quantity;
539 END IF;
540
541 IF g_osp_mov_details.fm_operation_seq_num IS NULL THEN
542 g_osp_mov_details.fm_operation_seq_num := l_fm_operation_seq_num;
543 END IF;
544
545 IF g_osp_mov_details.fm_intraop_step_type IS NULL THEN
546 g_osp_mov_details.fm_intraop_step_type := l_fm_intraop_step_type;
547 END IF;
548
549 IF g_osp_mov_details.fm_department_id IS NULL THEN
550 g_osp_mov_details.fm_department_id := l_fm_department_id;
551 END IF;
552
553 IF g_osp_mov_details.to_operation_seq_num IS NULL THEN
554 g_osp_mov_details.to_operation_seq_num := l_to_operation_seq_num;
555 END IF;
556
557 IF g_osp_mov_details.to_intraop_step_type IS NULL THEN
558 g_osp_mov_details.to_intraop_step_type := l_to_intraop_step_type;
559 END IF;
560
561 IF g_osp_mov_details.to_department_id IS NULL THEN
562 g_osp_mov_details.to_department_id := l_to_department_id;
563 END IF;
564
565
566 EXCEPTION
567 WHEN OTHERS THEN
568 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_osp_move_details');
569 END get_osp_move_details;
570
571
572 FUNCTION Get_Acct_Period
573 RETURN NUMBER
574 IS
575 BEGIN
576
577 IF g_ShopFloorMove_rec.acct_period_id IS NOT NULL THEN
578 RETURN g_ShopFloorMove_rec.acct_period_id;
579 END IF;
580
581 IF g_OSP_rec.acct_period_id IS NOT NULL THEN
582 RETURN g_OSP_rec.acct_period_id;
583 END IF;
584
585
586 RETURN NULL;
587
588 EXCEPTION
589 WHEN OTHERS THEN
590 RETURN FND_API.G_MISS_NUM;
591
592 END Get_Acct_Period;
593
594 FUNCTION Get_Created_By_Name
595 RETURN VARCHAR2
596 IS
597 BEGIN
598
599 IF g_ShopFloorMove_rec.created_by_name IS NOT NULL THEN
600 RETURN g_ShopFloorMove_rec.created_by_name;
601 END IF;
602
603 IF g_OSP_rec.created_by_name IS NOT NULL THEN
604 RETURN g_OSP_rec.created_by_name;
605 END IF;
606
607
608 RETURN NULL;
609
610 EXCEPTION
611 WHEN OTHERS THEN
612 RETURN FND_API.G_MISS_CHAR;
613
614 END Get_Created_By_Name;
615
616 FUNCTION Get_Created_By
617 RETURN VARCHAR2
618 IS
619 BEGIN
620
621 IF g_ShopFloorMove_rec.created_by IS NOT NULL THEN
622 RETURN g_ShopFloorMove_rec.created_by;
623 END IF;
624
625 IF g_OSP_rec.created_by IS NOT NULL THEN
626 RETURN g_OSP_rec.created_by;
627 END IF;
628
629 RETURN NULL;
630
631 EXCEPTION
632 WHEN OTHERS THEN
633 RETURN FND_API.G_MISS_CHAR;
634
635 END Get_Created_By;
636
637 FUNCTION Get_Entity_Type
638 RETURN NUMBER
639 IS
640 BEGIN
641
642 IF g_ShopFloorMove_rec.entity_type IS NOT NULL THEN
643 RETURN g_ShopFloorMove_rec.entity_type;
644 END IF;
645
646 IF g_OSP_rec.entity_type IS NOT NULL THEN
647 RETURN g_OSP_rec.entity_type;
648 END IF;
649
650 IF g_Wip_Entities_rec.entity_type IS NOT NULL THEN
651 RETURN g_Wip_Entities_rec.entity_type;
652 END IF;
653
654 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL THEN
655 get_we_attr();
656 RETURN g_ShopFloorMove_rec.entity_type;
657 END IF;
658
659 RETURN NULL;
660
661 EXCEPTION
662 WHEN OTHERS THEN
663 RETURN FND_API.G_MISS_NUM;
664
665 END Get_Entity_Type;
666
667 FUNCTION Get_Fm_Department_Code
668 RETURN VARCHAR2
669 IS
670 BEGIN
671
672 IF g_ShopFloorMove_rec.fm_department_code IS NOT NULL THEN
673 RETURN g_ShopFloorMove_rec.fm_department_code;
674 END IF;
675
676 IF g_ShopFloorMove_rec.fm_department_id IS NOT NULL then
677
678 SELECT department_code
679 INTO g_ShopFloorMove_rec.fm_department_code
680 FROM bom_departments
681 WHERE department_id = g_ShopFloorMove_rec.fm_department_id;
682
683 RETURN g_ShopFloorMove_rec.fm_department_code;
684 END IF;
685
686 RETURN NULL;
687
688 EXCEPTION
689 WHEN OTHERS THEN
690 RETURN FND_API.G_MISS_CHAR;
691
692 END Get_Fm_Department_Code;
693
694 FUNCTION Get_Fm_Department_Id
695 RETURN NUMBER
696 IS
697 BEGIN
698
699 IF g_ShopFloorMove_rec.fm_department_id IS NOT NULL THEN
700 RETURN g_ShopFloorMove_rec.fm_department_id;
701 END IF;
702
703 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
704 IF g_osp_mov_details.fm_department_id IS NOT NULL THEN
705 RETURN g_osp_mov_details.fm_department_id;
706 ELSE
707 get_osp_move_details();
708 RETURN g_osp_mov_details.fm_department_id;
709 END IF;
710 END IF;
711
712 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL
713 AND g_ShopFloorMove_rec.fm_operation_seq_num IS NOT NULL
714 AND g_ShopFloorMove_rec.organization_id IS NOT NULL THEN
715
716 SELECT department_id
717 INTO g_ShopFloorMove_rec.fm_department_id
718 FROM wip_operations
719 WHERE wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
720 AND organization_id = g_ShopFloorMove_rec.organization_id
721 AND operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
722 AND (repetitive_schedule_id IS NULL
723 OR repetitive_schedule_id = g_ShopFloorMove_rec.repetitive_schedule_id);
724
725 RETURN g_ShopFloorMove_rec.fm_department_id;
726 END IF;
727
728 RETURN NULL;
729
730 EXCEPTION
731 WHEN OTHERS THEN
732 RETURN FND_API.G_MISS_NUM;
733
734 END Get_Fm_Department_Id;
735
736 FUNCTION Get_Fm_Intraop_Step_Type
737 RETURN NUMBER
738 IS
739 BEGIN
740
741 IF g_ShopFloorMove_rec.fm_intraop_step_type IS NOT NULL THEN
742 RETURN g_ShopFloorMove_rec.fm_intraop_step_type;
743 END IF;
744
745 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
746 IF g_osp_mov_details.fm_intraop_step_type IS NOT NULL THEN
747 RETURN g_osp_mov_details.fm_intraop_step_type;
748 ELSE
749 get_osp_move_details();
750 RETURN g_osp_mov_details.fm_intraop_step_type;
751 END IF;
752 END IF;
753
754 RETURN NULL;
755
756 EXCEPTION
757 WHEN OTHERS THEN
758 RETURN FND_API.G_MISS_NUM;
759
760 END Get_Fm_Intraop_Step_Type;
761
762 FUNCTION Get_Fm_Operation
763 RETURN VARCHAR2
764 IS
765 BEGIN
766
767 IF g_ShopFloorMove_rec.fm_operation_code IS NOT NULL THEN
768 RETURN g_ShopFloorMove_rec.fm_operation_code;
769 END IF;
770
771 RETURN NULL;
772
773 EXCEPTION
774 WHEN OTHERS THEN
775 RETURN FND_API.G_MISS_CHAR;
776
777 END Get_Fm_Operation;
778
779 FUNCTION Get_Fm_Operation_Seq_Num
780 RETURN NUMBER
781 IS
782 BEGIN
783
784
785 IF g_ShopFloorMove_rec.fm_operation_seq_num IS NOT NULL THEN
786 RETURN g_ShopFloorMove_rec.fm_operation_seq_num;
787 END IF;
788
789 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
790 IF g_osp_mov_details.fm_operation_seq_num IS NOT NULL THEN
791 RETURN g_osp_mov_details.fm_operation_seq_num;
792 ELSE
793 get_osp_move_details();
794 RETURN g_osp_mov_details.fm_operation_seq_num;
795 END IF;
796 END IF;
797
798 RETURN NULL;
799
800 EXCEPTION
801 WHEN OTHERS THEN
802 RETURN FND_API.G_MISS_NUM;
803
804 END Get_Fm_Operation_Seq_Num;
805
806 FUNCTION Get_Group
807 RETURN NUMBER
808 IS
809 BEGIN
810
811 IF g_ShopFloorMove_rec.group_id IS NOT NULL THEN
812 RETURN g_ShopFloorMove_rec.group_id;
813 END IF;
814
815 RETURN NULL;
816
817 EXCEPTION
818 WHEN OTHERS THEN
819 RETURN FND_API.G_MISS_NUM;
820
821 END Get_Group;
822
823 FUNCTION Get_Kanban_Card
824 RETURN NUMBER
825 IS
826 BEGIN
827
828 IF g_ShopFloorMove_rec.kanban_card_id IS NOT NULL THEN
829 RETURN g_ShopFloorMove_rec.kanban_card_id;
830 END IF;
831
832 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL
833 -- AND g_ShopFloorMove_rec.transaction_type = --easy complete
834 AND g_ShopFloorMove_rec.entity_type = 1 THEN
835 -- get_wdj_attr();
836 RETURN g_ShopFloorMove_rec.kanban_card_id;
837 END IF;
838
839 RETURN NULL;
840
841 EXCEPTION
842 WHEN OTHERS THEN
843 RETURN FND_API.G_MISS_NUM;
844
845 END Get_Kanban_Card;
846
847 FUNCTION Get_Last_Updated_By_Name
848 RETURN VARCHAR2
849 IS
850 BEGIN
851
852 IF g_ShopFloorMove_rec.last_updated_by_name IS NOT NULL THEN
853 RETURN g_ShopFloorMove_rec.last_updated_by_name;
854 END IF;
855
856 IF g_OSP_rec.last_updated_by_name IS NOT NULL THEN
857 RETURN g_OSP_rec.last_updated_by_name;
858 END IF;
859
860 RETURN NULL;
861
862 EXCEPTION
863 WHEN OTHERS THEN
864 RETURN FND_API.G_MISS_CHAR;
865
866 END Get_Last_Updated_By_Name;
867
868 FUNCTION Get_Last_Update_Login
869 RETURN VARCHAR2
870 IS
871 BEGIN
872
873 IF g_ShopFloorMove_rec.last_update_login IS NOT NULL THEN
874 RETURN g_ShopFloorMove_rec.last_update_login;
875 END IF;
876
877 IF g_OSP_rec.last_update_login IS NOT NULL THEN
878 RETURN g_OSP_rec.last_update_login;
879 END IF;
880
881 RETURN NULL;
882
883 EXCEPTION
884 WHEN OTHERS THEN
885 RETURN FND_API.G_MISS_CHAR;
886
887 END Get_Last_Update_Login;
888
889 FUNCTION Get_Request_Id
890 RETURN VARCHAR2
891 IS
892 BEGIN
893
894 IF g_ShopFloorMove_rec.request_id IS NOT NULL THEN
895 RETURN g_ShopFloorMove_rec.request_id;
896 END IF;
897
898 IF g_OSP_rec.request_id IS NOT NULL THEN
899 RETURN g_OSP_rec.request_id;
900 END IF;
901
902 RETURN NULL;
903
904 EXCEPTION
905 WHEN OTHERS THEN
906 RETURN FND_API.G_MISS_CHAR;
907
908 END Get_Request_Id;
909
910 FUNCTION Get_Program_Id
911 RETURN VARCHAR2
912 IS
913 BEGIN
914
915 IF g_ShopFloorMove_rec.program_id IS NOT NULL THEN
916 RETURN g_ShopFloorMove_rec.program_id;
917 END IF;
918
919 IF g_OSP_rec.program_id IS NOT NULL THEN
920 RETURN g_OSP_rec.program_id;
921 END IF;
922
923 RETURN NULL;
924
925 EXCEPTION
926 WHEN OTHERS THEN
927 RETURN FND_API.G_MISS_CHAR;
928
929 END Get_Program_Id;
930
931 FUNCTION Get_Program_Application_Id
932 RETURN VARCHAR2
933 IS
934 BEGIN
935
936 IF g_ShopFloorMove_rec.program_application_id IS NOT NULL THEN
937 RETURN g_ShopFloorMove_rec.program_application_id;
938 END IF;
939
940 IF g_OSP_rec.program_application_id IS NOT NULL THEN
941 RETURN g_OSP_rec.program_application_id;
942 END IF;
943
944 RETURN NULL;
945
946 EXCEPTION
947 WHEN OTHERS THEN
948 RETURN FND_API.G_MISS_CHAR;
949
950 END Get_Program_Application_Id;
951
952 FUNCTION Get_Program_Update_Date
953 RETURN DATE
954 IS
955 BEGIN
956
957 IF g_ShopFloorMove_rec.program_update_date IS NOT NULL THEN
958 RETURN g_ShopFloorMove_rec.program_update_date;
959 END IF;
960
961 IF g_OSP_rec.program_update_date IS NOT NULL THEN
962 RETURN g_OSP_rec.program_update_date;
963 END IF;
964
965 RETURN NULL;
966
967 EXCEPTION
968 WHEN OTHERS THEN
969 RETURN FND_API.G_MISS_DATE;
970
971 END Get_Program_Update_Date;
972
973 FUNCTION Get_Last_Updated_By
974 RETURN VARCHAR2
975 IS
976 BEGIN
977
978 IF g_ShopFloorMove_rec.last_updated_by IS NOT NULL THEN
979 RETURN g_ShopFloorMove_rec.last_updated_by;
980 END IF;
981
982 IF g_OSP_rec.last_updated_by IS NOT NULL THEN
983 RETURN g_OSP_rec.last_updated_by;
984 END IF;
985
986 RETURN NULL;
987
988 EXCEPTION
989 WHEN OTHERS THEN
990 RETURN FND_API.G_MISS_CHAR;
991
992 END Get_Last_Updated_By;
993
994 FUNCTION Get_Line_Code
995 RETURN VARCHAR2
996 IS
997 l_line_code VARCHAR2(10);
998 BEGIN
999
1000 IF g_ShopFloorMove_rec.line_code IS NOT NULL THEN
1001 RETURN g_ShopFloorMove_rec.line_code;
1002 END IF;
1003
1004 IF g_ShopFloorMove_rec.line_id IS NOT NULL
1005 AND g_ShopFloorMove_rec.organization_id IS NOT NULL THEN
1006 SELECT line_code
1007 INTO l_line_code
1008 FROM wip_lines
1009 WHERE line_id = g_ShopFloorMove_rec.line_id
1010 AND organization_id = g_ShopFloorMove_rec.organization_id;
1011
1012 RETURN l_line_code;
1013 END IF;
1014
1015 RETURN NULL;
1016
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 RETURN FND_API.G_MISS_CHAR;
1020
1021 END Get_Line_Code;
1022
1023 FUNCTION Get_Line_Id
1024 RETURN NUMBER
1025 IS
1026 BEGIN
1027
1028 IF g_ShopFloorMove_rec.line_id IS NOT NULL THEN
1029 RETURN g_ShopFloorMove_rec.line_id;
1030 END IF;
1031
1032 IF g_OSP_rec.line_id IS NOT NULL THEN
1033 RETURN g_OSP_rec.line_id;
1034 END IF;
1035
1036 RETURN NULL;
1037
1038 EXCEPTION
1039 WHEN OTHERS THEN
1040 RETURN FND_API.G_MISS_NUM;
1041
1042 END Get_Line_Id;
1043
1044 FUNCTION Get_Organization_Code
1045 RETURN VARCHAR2
1046 IS
1047 BEGIN
1048
1049 IF g_ShopFloorMove_rec.organization_code IS NOT NULL THEN
1050 RETURN g_ShopFloorMove_rec.organization_code;
1051 END IF;
1052
1053 IF g_ShopFloorMove_rec.organization_id IS NOT NULL THEN
1054 SELECT organization_code
1055 INTO g_ShopFloorMove_rec.organization_code
1056 FROM mtl_parameters
1057 WHERE organization_id = g_ShopFloorMove_rec.organization_id;
1058
1059 RETURN g_ShopFloorMove_rec.organization_code;
1060 END IF;
1061
1062 RETURN NULL;
1063
1064 EXCEPTION
1065 WHEN OTHERS THEN
1066 RETURN FND_API.G_MISS_CHAR;
1067
1068 END Get_Organization_Code;
1069
1070 FUNCTION Get_Organization_Id
1071 RETURN NUMBER
1072 IS
1073 BEGIN
1074
1075 IF g_ShopFloorMove_rec.organization_id IS NOT NULL THEN
1076 RETURN g_ShopFloorMove_rec.organization_id;
1077 END IF;
1078
1079 IF g_OSP_rec.organization_id IS NOT NULL THEN
1080 RETURN g_OSP_rec.organization_id;
1081 END IF;
1082
1083 RETURN NULL;
1084
1085 EXCEPTION
1086 WHEN OTHERS THEN
1087 RETURN FND_API.G_MISS_NUM;
1088
1089 END Get_Organization_Id;
1090
1091 FUNCTION Get_Overcpl_Primary_Qty
1092 RETURN NUMBER
1093 IS
1094 l_overcpl_primary_qty NUMBER;
1095
1096 l_total_po_qty_delivered NUMBER := 0;--assigned for bug 9889616
1097 l_total_pri_qty_delivered NUMBER;
1098 l_total_prev_qty_delivered NUMBER;
1099 l_curr_qty_delivered NUMBER;
1100 l_quantity_in_queue NUMBER;
1101 l_scheduled_quantity NUMBER;
1102 l_usage_rate_or_amount NUMBER;
1103 l_po_uom_code mtl_units_of_measure.uom_code%type;
1104 l_interface_txn_id NUMBER;
1105 l_osp_item_id NUMBER;
1106 l_remaining_qty NUMBER;
1107
1108 /* Added for bug 6649174 */
1109 l_logLevel NUMBER := fnd_log.g_current_runtime_level;
1110 l_returnStatus VARCHAR2(1);
1111 l_quantity_completed NUMBER;
1112 l_quantity_running NUMBER;
1113 l_total_qty NUMBER;
1114
1115 BEGIN
1116
1117 IF g_ShopFloorMove_rec.overcpl_primary_qty IS NOT NULL THEN
1118 RETURN g_ShopFloorMove_rec.overcpl_primary_qty;
1119 END IF;
1120
1121 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1122 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL
1123 AND nvl(g_ShopFloorMove_rec.repetitive_schedule_id, -1) IS NOT NULL
1124 AND g_ShopFloorMove_rec.organization_id IS NOT NULL
1125 AND g_ShopFloorMove_rec.fm_operation_seq_num IS NOT NULL
1126 AND g_ShopFloorMove_rec.primary_quantity IS NOT NULL
1127 AND g_osp_mov_details.move_direction = MOVE_FORWARD THEN
1128
1129 /* Added wip_resource_seq_num and transaction_type condition for Bug#3248542 */
1130 /* Modified for bug 6649174. commented condition on wip_repetitive_schedule_id
1131 and added condition on wip_line_id */
1132
1133 /*Changed this SQL for performance bug 9888616. */
1134 IF g_ShopFloorMove_rec.repetitive_schedule_id IS NULL THEN
1135 SELECT sum(quantity_delivered)
1136 INTO l_total_po_qty_delivered
1137 FROM po_distributions_all pda
1138 WHERE pda.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
1139 AND pda.destination_organization_id = g_ShopFloorMove_rec.organization_id
1140 AND pda.wip_operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
1141 AND pda.wip_resource_seq_num = g_OSP_rec.resource_seq_num
1142 AND pda.quantity_delivered IS NOT NULL;
1143 ELSE
1144 SELECT sum(quantity_delivered)
1145 INTO l_total_po_qty_delivered
1146 FROM po_distributions_all pda
1147 WHERE pda.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
1148 AND pda.wip_line_id = g_osp_rec.line_id
1149 AND pda.destination_organization_id = g_ShopFloorMove_rec.organization_id
1150 AND pda.wip_operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
1151 AND pda.wip_resource_seq_num = g_OSP_rec.resource_seq_num
1152 AND pda.quantity_delivered IS NOT NULL;
1153 END IF;
1154 /*Changes done SQL for performance bug 9888616. */
1155
1156 IF (l_logLevel <= wip_constants.full_logging) THEN
1157 wip_logger.log('Get_Overcpl_Primary_Qty: l_total_po_qty_delivered: '||l_total_po_qty_delivered,l_returnStatus);
1158 END IF ;
1159
1160 begin
1161 SELECT wor.usage_rate_or_amount
1162 INTO l_usage_rate_or_amount
1163 FROM wip_operation_resources wor
1164 WHERE wor.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
1165 and NVL(wor.repetitive_schedule_id, -1) = NVL(g_ShopFloorMove_rec.repetitive_schedule_id, -1)
1166 and wor.operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
1167 and wor.organization_id = g_ShopFloorMove_rec.organization_id
1168 and wor.resource_seq_num = g_osp_rec.Resource_Seq_Num;
1169 exception
1170 when others then
1171 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from WOR');
1172 end;
1173
1174 /* Modified sql for performance bug 9552415 (FP 9398894) */
1175 begin
1176 select uom.uom_code
1177 into l_po_uom_code
1178 from mtl_units_of_measure_vl uom
1179 where uom.unit_of_measure =
1180 ( select PL.unit_meas_lookup_code
1181 from po_lines_all Pl
1182 where (pl.po_header_id,pl.po_line_id) =
1183 ( select pd.po_header_id, pd.po_line_id
1184 from po_distributions_all pd
1185 where pd.po_distribution_id =
1186 ( select rt.po_distribution_id
1187 from rcv_transactions rt
1188 where rt.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
1189 and nvl(rt.wip_repetitive_schedule_id, -1)
1190 = nvl(g_ShopFloorMove_rec.repetitive_schedule_id, -1)
1191 and rt.organization_id = g_ShopFloorMove_rec.organization_id
1192 and rt.wip_operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num
1193 and rt.po_distribution_id is not null
1194 and rownum = 1 -- Fix bug 9552415 (FP 9398894)
1195 )
1196 and rownum = 1
1197 )
1198 );
1199 exception
1200 when others then
1201 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from UOM');
1202 end;
1203
1204
1205
1206 begin
1207 SELECT interface_transaction_id
1208 INTO l_interface_txn_id
1209 FROM rcv_transactions
1210 WHERE transaction_id = g_osp_rec.rcv_transaction_id;
1211 exception
1212 when others then
1213 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from RT');
1214 end;
1215
1216 IF (l_interface_txn_id IS NULL) THEN
1217 RAISE fnd_api.g_exc_error;
1218 END IF;
1219
1220 begin
1221 SELECT item_id
1222 INTO l_osp_item_id
1223 FROM rcv_transactions_interface
1224 WHERE interface_transaction_id = l_interface_txn_id;
1225 exception
1226 when others then
1227 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from RTI');
1228 end;
1229
1230 begin
1231 select decode (msi.outside_operation_uom_type,
1232 'ASSEMBLY',
1233 inv_convert.inv_um_convert(
1234 l_osp_item_id, -- item_id
1235 NULL, -- precision
1236 l_total_po_qty_delivered, -- from_quantity
1237 l_po_uom_code, -- from_unit
1238 g_ShopFloorMove_rec.primary_uom, -- to_unit
1239 NULL, -- from_name
1240 NULL -- to_name
1241 ),
1242 decode (nvl(l_usage_rate_or_amount, 0) ,
1243 0, 0,
1244 l_total_po_qty_delivered/l_usage_rate_or_amount))
1245 into l_total_pri_qty_delivered
1246 from mtl_system_items msi
1247 where msi.inventory_item_id = l_osp_item_id
1248 and msi.organization_id = g_ShopFloorMove_rec.organization_id;
1249 exception
1250 when others then
1251 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from MSI');
1252 end;
1253
1254 IF (l_logLevel <= wip_constants.full_logging) THEN
1255 wip_logger.log('Get_Overcpl_Primary_Qty: l_total_pri_qty_delivered: '||l_total_pri_qty_delivered,l_returnStatus);
1256 END IF;
1257
1258 begin
1259
1260 /* Modified for bug 6649174 */
1261
1262 select quantity_in_queue,
1263 quantity_running,
1264 quantity_completed,
1265 SCHEDULED_QUANTITY
1266 into l_quantity_in_queue,
1267 l_quantity_running,
1268 l_quantity_completed,
1269 l_scheduled_quantity
1270 from wip_operations wo
1271 where wo.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
1272 and nvl(wo.repetitive_schedule_id, -1) = nvl(g_ShopFloorMove_rec.repetitive_schedule_id, -1)
1273 and wo.organization_id = g_ShopFloorMove_rec.organization_id
1274 and wo.operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num;
1275
1276 /* Fix for Bug#5912963. Sum quantity in queue for repetitive schedule of same item
1277 on the same line
1278 */
1279
1280 if g_ShopFloorMove_rec.repetitive_schedule_id is not null then
1281
1282 /* Modified for bug 6649174 */
1283
1284 select sum(quantity_in_queue) ,
1285 sum(wo.quantity_running) ,
1286 sum(wo.quantity_completed)
1287 into l_quantity_in_queue,
1288 l_quantity_running,
1289 l_quantity_completed
1290 from wip_operations wo,
1291 wip_repetitive_schedules wrs
1292 where wo.wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
1293 and wrs.wip_entity_id = wo.wip_entity_id
1294 and wo.organization_id = wrs.organization_id
1295 and wo.repetitive_schedule_id = wrs.repetitive_schedule_id
1296 and wrs.line_id = g_osp_rec.line_id
1297 and wrs.status_type = WIP_CONSTANTS.RELEASED
1298 and wo.organization_id = g_ShopFloorMove_rec.organization_id
1299 and wo.operation_seq_num = g_ShopFloorMove_rec.fm_operation_seq_num;
1300
1301 end if ;
1302
1303 l_total_qty := l_quantity_in_queue + l_quantity_running + l_quantity_completed ;
1304
1305 IF (l_logLevel <= wip_constants.full_logging) THEN
1306 wip_logger.log('Get_Overcpl_Primary_Qty: l_total_qty: '||l_total_qty,l_returnStatus);
1307 wip_logger.log('Get_Overcpl_Primary_Qty: l_quantity_in_queue: '||l_quantity_in_queue,l_returnStatus);
1308 wip_logger.log('Get_Overcpl_Primary_Qty: l_quantity_running: '||l_quantity_running,l_returnStatus);
1309 wip_logger.log('Get_Overcpl_Primary_Qty: l_quantity_completed: '||l_quantity_completed,l_returnStatus);
1310 END IF;
1311
1312 --Bug 16099295 (FP 13526041): Noticed that if there are consecutive OSP operations and they are
1313 --received at the same time, then total_qty for the second one will be 0, and hence it will show
1314 --the quantity being received as OverCompletion, when it is NOT. This condition will cause it to
1315 --avoid that.
1316 if (l_total_qty = 0) then
1317 l_total_qty := l_scheduled_quantity;
1318 IF (l_logLevel <= wip_constants.full_logging) THEN
1319 wip_logger.log('Get_Overcpl_Primary_Qty: l_total_qty 2: '||l_total_qty,l_returnStatus);
1320 END IF;
1321 end if;
1322
1323 exception
1324 when others then
1325 fnd_msg_pub.add_exc_msg('WIP_Default_Shopfloormove','get_overcpl_primary_qty: select from WO');
1326 end;
1327
1328
1329 l_curr_qty_delivered := nvl(g_ShopFloorMove_rec.primary_quantity,0);
1330 l_total_prev_qty_delivered := l_total_pri_qty_delivered - l_curr_qty_delivered;
1331
1332 IF (l_logLevel <= wip_constants.full_logging) THEN
1333 wip_logger.log('Get_Overcpl_Primary_Qty: l_curr_qty_delivered: '||l_curr_qty_delivered,l_returnStatus);
1334 wip_logger.log('Get_Overcpl_Primary_Qty: l_total_prev_qty_delivered: '||l_total_prev_qty_delivered,l_returnStatus);
1335 END IF;
1336
1337 /* Fix for Bug#5020591. OverCompletion quantity should be completed only when Current delivered Qty
1338 is more than remaining quantity to be completed on Operation. Greatest condition is added
1339 to take care of test case mentioned in #4232649. This fix actually same as fix made in 11.5.9
1340 11.5.9 code has flaw when half quantity is delivered . This case is also fixed with new if
1341 condition.
1342
1343 Reverted changes done in bug 4686257; FP 4769587
1344 */
1345
1346 /* Modified for bug 6649174 */
1347 --l_remaining_qty := greatest(l_scheduled_quantity, l_quantity_in_queue) - l_total_prev_qty_delivered ; -- Open Qty
1348 l_remaining_qty := l_total_qty - l_total_prev_qty_delivered ; -- Open Qty
1349
1350 IF (l_logLevel <= wip_constants.full_logging) THEN
1351 wip_logger.log('Get_Overcpl_Primary_Qty: l_remaining_qty: '||l_remaining_qty,l_returnStatus);
1352 END IF;
1353
1354 /* Modified calculation logic for bug 6649174 */
1355 if l_curr_qty_delivered > l_remaining_qty then -- Current Qty is going to overcomplete an Op.
1356
1357 If ( l_total_prev_qty_delivered >= l_total_qty ) then -- You delivered more quantity last time
1358 l_overcpl_primary_qty := l_curr_qty_delivered;
1359 IF (l_logLevel <= wip_constants.full_logging) THEN
1360 wip_logger.log('Get_Overcpl_Primary_Qty: l_overcpl_primary_qty1: '||l_overcpl_primary_qty,l_returnStatus);
1361 END IF;
1362 elsif (l_total_pri_qty_delivered > l_total_qty) then -- You are over receiving this time
1363 l_overcpl_primary_qty := l_total_pri_qty_delivered - l_total_qty;
1364 IF (l_logLevel <= wip_constants.full_logging) THEN
1365 wip_logger.log('Get_Overcpl_Primary_Qty: l_overcpl_primary_qty2: '||l_overcpl_primary_qty,l_returnStatus);
1366 END IF;
1367 end if;
1368
1369 end if ;
1370
1371
1372 END IF;
1373 END IF;
1374
1375 IF (l_logLevel <= wip_constants.full_logging) THEN
1376 wip_logger.log('Get_Overcpl_Primary_Qty: l_overcpl_primary_qty3: '||l_overcpl_primary_qty,l_returnStatus);
1377 END IF;
1378
1379 RETURN l_overcpl_primary_qty;
1380
1381 EXCEPTION
1382 WHEN OTHERS THEN
1383 RETURN FND_API.G_MISS_NUM;
1384 END Get_Overcpl_Primary_Qty;
1385
1386 FUNCTION Get_Overcpl_Transaction
1387 RETURN NUMBER
1388 IS
1389 BEGIN
1390
1391 IF g_ShopFloorMove_rec.overcpl_transaction_id IS NOT NULL THEN
1392 RETURN g_ShopFloorMove_rec.overcpl_transaction_id;
1393 END IF;
1394
1395 RETURN NULL;
1396
1397 EXCEPTION
1398 WHEN OTHERS THEN
1399 RETURN FND_API.G_MISS_NUM;
1400
1401 END Get_Overcpl_Transaction;
1402
1403 FUNCTION Get_Overcpl_Transaction_Qty
1404 RETURN NUMBER
1405 IS
1406 l_overcpl_transaction_qty NUMBER;
1407 BEGIN
1408
1409 IF g_ShopFloorMove_rec.overcpl_transaction_qty IS NOT NULL THEN
1410 RETURN g_ShopFloorMove_rec.overcpl_transaction_qty;
1411 END IF;
1412
1413 IF g_ShopFloorMove_rec.overcpl_primary_qty IS NOT NULL
1414 AND g_ShopFloorMove_rec.primary_uom IS NOT NULL
1415 AND g_ShopFloorMove_rec.transaction_uom IS NOT NULL
1416 AND g_ShopFloorMove_rec.primary_item_id IS NOT NULL THEN
1417
1418 l_overcpl_transaction_qty := inv_convert.inv_um_convert (
1419 g_ShopFloorMove_rec.primary_item_id, -- item_id
1420 NULL, -- precision
1421 g_ShopFloorMove_rec.overcpl_primary_qty,-- from_quantity
1422 g_ShopFloorMove_rec.primary_uom, -- from_unit
1423 g_ShopFloorMove_rec.transaction_uom, -- to_unit
1424 NULL, -- from_name
1425 NULL -- to_name
1426 );
1427
1428 return l_overcpl_transaction_qty;
1429
1430 END IF;
1431
1432 RETURN NULL;
1433
1434 EXCEPTION
1435 WHEN OTHERS THEN
1436 RETURN FND_API.G_MISS_NUM;
1437
1438 END Get_Overcpl_Transaction_Qty;
1439
1440 FUNCTION Get_Primary_Item
1441 RETURN NUMBER
1442 IS
1443 BEGIN
1444
1445 IF g_ShopFloorMove_rec.primary_item_id IS NOT NULL THEN
1446 RETURN g_ShopFloorMove_rec.primary_item_id;
1447 END IF;
1448
1449 IF g_OSP_rec.primary_item_id IS NOT NULL THEN
1450 RETURN g_OSP_rec.primary_item_id;
1451 END IF;
1452
1453 IF g_Wip_Entities_rec.primary_item_id IS NOT NULL THEN
1454 RETURN g_Wip_Entities_rec.primary_item_id;
1455 END IF;
1456
1457 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL THEN
1458 get_we_attr();
1459 RETURN g_ShopFloorMove_rec.primary_item_id;
1460 END IF;
1461
1462 RETURN NULL;
1463
1464 EXCEPTION
1465 WHEN OTHERS THEN
1466 RETURN FND_API.G_MISS_NUM;
1467
1468 END Get_Primary_Item;
1469
1470 FUNCTION Get_Primary_Quantity
1471 RETURN NUMBER
1472 IS
1473 BEGIN
1474
1475 IF g_ShopFloorMove_rec.primary_quantity IS NOT NULL THEN
1476 RETURN g_ShopFloorMove_rec.primary_quantity;
1477 END IF;
1478
1479 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1480 IF g_osp_mov_details.primary_quantity IS NOT NULL THEN
1481 RETURN g_osp_mov_details.primary_quantity;
1482 ELSE
1483 get_osp_move_details();
1484 RETURN g_osp_mov_details.primary_quantity;
1485 END IF;
1486 END IF;
1487
1488 RETURN NULL;
1489
1490 EXCEPTION
1491 WHEN OTHERS THEN
1492 RETURN FND_API.G_MISS_NUM;
1493
1494 END Get_Primary_Quantity;
1495
1496 FUNCTION Get_Primary_Uom
1497 RETURN VARCHAR2
1498 IS
1499 l_primary_uom VARCHAR2(3);
1500 BEGIN
1501
1502 IF g_ShopFloorMove_rec.primary_uom IS NOT NULL THEN
1503 RETURN g_ShopFloorMove_rec.primary_uom;
1504 END IF;
1505
1506 IF g_ShopFloorMove_rec.primary_item_id IS NOT NULL
1507 AND g_ShopFloorMove_rec.organization_id IS NOT NULL THEN
1508
1509 SELECT primary_uom_code
1510 INTO l_primary_uom
1511 FROM mtl_system_items
1512 WHERE inventory_item_id = g_ShopFloorMove_rec.primary_item_id
1513 AND organization_id = g_ShopFloorMove_rec.organization_id;
1514
1515 RETURN l_primary_uom;
1516 END IF;
1517
1518 RETURN NULL;
1519
1520 EXCEPTION
1521 WHEN OTHERS THEN
1522 RETURN FND_API.G_MISS_CHAR;
1523
1524 END Get_Primary_Uom;
1525
1526 FUNCTION Get_Process_Phase
1527 RETURN NUMBER
1528 IS
1529 BEGIN
1530
1531 IF g_ShopFloorMove_rec.process_phase IS NOT NULL THEN
1532 RETURN g_ShopFloorMove_rec.process_phase;
1533 END IF;
1534
1535 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1536 RETURN WIP_CONSTANTS.MOVE_VAL;
1537 END IF;
1538
1539 RETURN NULL;
1540
1541 EXCEPTION
1542 WHEN OTHERS THEN
1543 RETURN FND_API.G_MISS_NUM;
1544
1545 END Get_Process_Phase;
1546
1547 FUNCTION Get_Process_Status
1548 RETURN NUMBER
1549 IS
1550 BEGIN
1551
1552 IF g_ShopFloorMove_rec.process_status IS NOT NULL THEN
1553 RETURN g_ShopFloorMove_rec.process_status;
1554 END IF;
1555
1556 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1557 RETURN WIP_CONSTANTS.PENDING;
1558 END IF;
1559
1560 RETURN NULL;
1561
1562 EXCEPTION
1563 WHEN OTHERS THEN
1564 RETURN FND_API.G_MISS_NUM;
1565
1566 END Get_Process_Status;
1567
1568 FUNCTION Get_Qa_Collection
1569 RETURN NUMBER
1570 IS
1571 BEGIN
1572
1573 IF g_ShopFloorMove_rec.qa_collection_id IS NOT NULL THEN
1574 RETURN g_ShopFloorMove_rec.qa_collection_id;
1575 END IF;
1576
1577 RETURN NULL;
1578
1579 EXCEPTION
1580 WHEN OTHERS THEN
1581 RETURN FND_API.G_MISS_NUM;
1582
1583 END Get_Qa_Collection;
1584
1585 FUNCTION Get_Reason
1586 RETURN NUMBER
1587 IS
1588 BEGIN
1589
1590 IF g_ShopFloorMove_rec.reason_id IS NOT NULL THEN
1591 RETURN g_ShopFloorMove_rec.reason_id;
1592 END IF;
1593
1594 IF g_OSP_rec.reason_id IS NOT NULL THEN
1595 RETURN g_OSP_rec.reason_id;
1596 END IF;
1597
1598 RETURN NULL;
1599
1600 EXCEPTION
1601 WHEN OTHERS THEN
1602 RETURN FND_API.G_MISS_NUM;
1603
1604 END Get_Reason;
1605
1606 FUNCTION Get_Reason_Name
1607 RETURN VARCHAR2
1608 IS
1609 BEGIN
1610
1611 IF g_ShopFloorMove_rec.reason_name IS NOT NULL THEN
1612 RETURN g_ShopFloorMove_rec.reason_name;
1613 END IF;
1614
1615 IF g_OSP_rec.reason_name IS NOT NULL THEN
1616 RETURN g_OSP_rec.reason_name;
1617 END IF;
1618
1619 RETURN NULL;
1620
1621 EXCEPTION
1622 WHEN OTHERS THEN
1623 RETURN FND_API.G_MISS_CHAR;
1624
1625 END Get_Reason_Name;
1626
1627 FUNCTION Get_Reference
1628 RETURN VARCHAR2
1629 IS
1630 BEGIN
1631
1632 IF g_ShopFloorMove_rec.reference IS NOT NULL THEN
1633 RETURN g_ShopFloorMove_rec.reference;
1634 END IF;
1635
1636 IF g_OSP_rec.reference IS NOT NULL THEN
1637 RETURN g_OSP_rec.reference;
1638 END IF;
1639
1640 RETURN NULL;
1641
1642 EXCEPTION
1643 WHEN OTHERS THEN
1644 RETURN FND_API.G_MISS_NUM;
1645
1646 END Get_Reference;
1647
1648 FUNCTION Get_Repetitive_Schedule
1649 RETURN NUMBER
1650 IS
1651 BEGIN
1652
1653 IF g_ShopFloorMove_rec.repetitive_schedule_id IS NOT NULL THEN
1654 RETURN g_ShopFloorMove_rec.repetitive_schedule_id;
1655 END IF;
1656
1657 IF g_OSP_rec.repetitive_schedule_id IS NOT NULL THEN
1658 RETURN g_OSP_rec.repetitive_schedule_id;
1659 END IF;
1660
1661 RETURN NULL;
1662
1663 EXCEPTION
1664 WHEN OTHERS THEN
1665 RETURN FND_API.G_MISS_NUM;
1666
1667 END Get_Repetitive_Schedule;
1668
1669 FUNCTION Get_Scrap_Account
1670 RETURN NUMBER
1671 IS
1672 BEGIN
1673
1674 IF g_ShopFloorMove_rec.scrap_account_id IS NOT NULL THEN
1675 RETURN g_ShopFloorMove_rec.scrap_account_id;
1676 END IF;
1677
1678 RETURN NULL;
1679
1680 EXCEPTION
1681 WHEN OTHERS THEN
1682 RETURN FND_API.G_MISS_NUM;
1683
1684 END Get_Scrap_Account;
1685
1686 FUNCTION Get_Source
1687 RETURN VARCHAR2
1688 IS
1689 BEGIN
1690
1691 IF g_ShopFloorMove_rec.source_code IS NOT NULL THEN
1692 RETURN g_ShopFloorMove_rec.source_code;
1693 END IF;
1694
1695 IF g_OSP_rec.source_code IS NOT NULL THEN
1696 RETURN g_OSP_rec.source_code;
1697 END IF;
1698
1699 RETURN NULL;
1700
1701 EXCEPTION
1702 WHEN OTHERS THEN
1703 RETURN FND_API.G_MISS_CHAR;
1704
1705 END Get_Source;
1706
1707 FUNCTION Get_Source_Line
1708 RETURN NUMBER
1709 IS
1710 BEGIN
1711
1712 IF g_ShopFloorMove_rec.source_line_id IS NOT NULL THEN
1713 RETURN g_ShopFloorMove_rec.source_line_id;
1714 END IF;
1715
1716 IF g_OSP_rec.source_line_id IS NOT NULL THEN
1717 RETURN g_OSP_rec.source_line_id;
1718 END IF;
1719
1720 RETURN NULL;
1721
1722 EXCEPTION
1723 WHEN OTHERS THEN
1724 RETURN FND_API.G_MISS_NUM;
1725
1726 END Get_Source_Line;
1727
1728 FUNCTION Get_To_Department_Code
1729 RETURN VARCHAR2
1730 IS
1731 BEGIN
1732
1733 IF g_ShopFloorMove_rec.to_department_code IS NOT NULL THEN
1734 RETURN g_ShopFloorMove_rec.to_department_code;
1735 END IF;
1736
1737 /* IF g_OSP_rec.department_code IS NOT NULL THEN
1738 RETURN g_OSP_rec.department_code;
1739 END IF;
1740 */
1741
1742 IF g_ShopFloorMove_rec.to_department_id IS NOT NULL then
1743
1744 SELECT department_code
1745 INTO g_ShopFloorMove_rec.to_department_code
1746 FROM bom_departments
1747 WHERE department_id = g_ShopFloorMove_rec.to_department_id;
1748
1749 RETURN g_ShopFloorMove_rec.to_department_code;
1750 END IF;
1751
1752 RETURN NULL;
1753
1754 EXCEPTION
1755 WHEN OTHERS THEN
1756 RETURN FND_API.G_MISS_CHAR;
1757
1758 END Get_To_Department_Code;
1759
1760 FUNCTION Get_To_Department_Id
1761 RETURN NUMBER
1762 IS
1763 BEGIN
1764
1765 IF g_ShopFloorMove_rec.to_department_id IS NOT NULL THEN
1766 RETURN g_ShopFloorMove_rec.to_department_id;
1767 END IF;
1768
1769 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1770 IF g_osp_mov_details.to_department_id IS NOT NULL THEN
1771 RETURN g_osp_mov_details.to_department_id;
1772 ELSE
1773 get_osp_move_details();
1774 RETURN g_osp_mov_details.to_department_id;
1775 END IF;
1776 END IF;
1777
1778 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL
1779 AND g_ShopFloorMove_rec.to_operation_seq_num IS NOT NULL
1780 AND g_ShopFloorMove_rec.organization_id IS NOT NULL THEN
1781
1782 SELECT department_id
1783 INTO g_ShopFloorMove_rec.to_department_id
1784 FROM wip_operations
1785 WHERE wip_entity_id = g_ShopFloorMove_rec.wip_entity_id
1786 AND operation_seq_num = g_ShopFloorMove_rec.to_operation_seq_num
1787 AND organization_id = g_ShopFloorMove_rec.organization_id
1788 AND (repetitive_schedule_id IS NULL
1789 OR repetitive_schedule_id = g_ShopFloorMove_rec.repetitive_schedule_id);
1790
1791 RETURN g_ShopFloorMove_rec.to_department_id;
1792 END IF;
1793
1794 RETURN NULL;
1795
1796 EXCEPTION
1797 WHEN OTHERS THEN
1798 RETURN FND_API.G_MISS_NUM;
1799
1800 END Get_To_Department_Id;
1801
1802 FUNCTION Get_To_Intraop_Step_Type
1803 RETURN NUMBER
1804 IS
1805 BEGIN
1806
1807 IF g_ShopFloorMove_rec.to_intraop_step_type IS NOT NULL THEN
1808 RETURN g_ShopFloorMove_rec.to_intraop_step_type;
1809 END IF;
1810
1811 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1812 IF g_osp_mov_details.to_intraop_step_type IS NOT NULL THEN
1813 RETURN g_osp_mov_details.to_intraop_step_type;
1814 ELSE
1815 get_osp_move_details();
1816 RETURN g_osp_mov_details.to_intraop_step_type;
1817 END IF;
1818 END IF;
1819
1820 RETURN NULL;
1821
1822 EXCEPTION
1823 WHEN OTHERS THEN
1824 RETURN FND_API.G_MISS_NUM;
1825
1826 END Get_To_Intraop_Step_Type;
1827
1828 FUNCTION Get_To_Operation
1829 RETURN VARCHAR2
1830 IS
1831 BEGIN
1832
1833 IF g_ShopFloorMove_rec.to_operation_code IS NOT NULL THEN
1834 RETURN g_ShopFloorMove_rec.to_operation_code;
1835 END IF;
1836
1837 RETURN NULL;
1838
1839 EXCEPTION
1840 WHEN OTHERS THEN
1841 RETURN FND_API.G_MISS_CHAR;
1842
1843 END Get_To_Operation;
1844
1845 FUNCTION Get_To_Operation_Seq_Num
1846 RETURN NUMBER
1847 IS
1848 BEGIN
1849
1850 IF g_ShopFloorMove_rec.to_operation_seq_num IS NOT NULL THEN
1851 RETURN g_ShopFloorMove_rec.to_operation_seq_num;
1852 END IF;
1853
1854 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1855 IF g_osp_mov_details.to_operation_seq_num IS NOT NULL THEN
1856 RETURN g_osp_mov_details.to_operation_seq_num;
1857 ELSE
1858 get_osp_move_details();
1859 RETURN g_osp_mov_details.to_operation_seq_num;
1860 END IF;
1861 END IF;
1862
1863 RETURN NULL;
1864
1865 EXCEPTION
1866 WHEN OTHERS THEN
1867 RETURN FND_API.G_MISS_NUM;
1868
1869 END Get_To_Operation_Seq_Num;
1870
1871 FUNCTION Get_Transaction_Date
1872 RETURN DATE
1873 IS
1874 BEGIN
1875
1876 IF g_ShopFloorMove_rec.transaction_date IS NOT NULL THEN
1877 RETURN g_ShopFloorMove_rec.transaction_date;
1878 END IF;
1879
1880 IF g_OSP_rec.transaction_date IS NOT NULL THEN
1881 RETURN g_OSP_rec.transaction_date;
1882 END IF;
1883
1884 RETURN NULL;
1885
1886 EXCEPTION
1887 WHEN OTHERS THEN
1888 RETURN FND_API.G_MISS_DATE;
1889
1890 END Get_Transaction_Date;
1891
1892 FUNCTION Get_Transaction
1893 RETURN NUMBER
1894 IS
1895 BEGIN
1896
1897 IF g_ShopFloorMove_rec.transaction_id IS NOT NULL THEN
1898 RETURN g_ShopFloorMove_rec.transaction_id;
1899 END IF;
1900
1901 RETURN NULL;
1902
1903 EXCEPTION
1904 WHEN OTHERS THEN
1905 RETURN FND_API.G_MISS_NUM;
1906
1907 END Get_Transaction;
1908
1909 FUNCTION Get_Transaction_Quantity
1910 RETURN NUMBER
1911 IS
1912 BEGIN
1913
1914 IF g_ShopFloorMove_rec.transaction_quantity IS NOT NULL THEN
1915 RETURN g_ShopFloorMove_rec.transaction_quantity;
1916 END IF;
1917
1918 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1919 IF g_osp_mov_details.transaction_quantity IS NOT NULL THEN
1920 RETURN g_osp_mov_details.transaction_quantity;
1921 ELSE
1922 get_osp_move_details();
1923 RETURN g_osp_mov_details.transaction_quantity;
1924 END IF;
1925 END IF;
1926
1927 RETURN NULL;
1928
1929 EXCEPTION
1930 WHEN OTHERS THEN
1931 RETURN FND_API.G_MISS_NUM;
1932
1933 END Get_Transaction_Quantity;
1934
1935 FUNCTION Get_Transaction_Type
1936 RETURN NUMBER
1937 IS
1938 BEGIN
1939
1940 IF g_ShopFloorMove_rec.transaction_type IS NOT NULL THEN
1941 RETURN g_ShopFloorMove_rec.transaction_type;
1942 END IF;
1943
1944 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1945 IF g_osp_mov_details.transaction_type IS NOT NULL THEN
1946 RETURN g_osp_mov_details.transaction_type;
1947 ELSE
1948 get_osp_move_details();
1949 RETURN g_osp_mov_details.transaction_type;
1950 END IF;
1951 END IF;
1952
1953 RETURN NULL;
1954
1955 EXCEPTION
1956 WHEN OTHERS THEN
1957 RETURN FND_API.G_MISS_NUM;
1958
1959 END Get_Transaction_Type;
1960
1961 FUNCTION Get_Transaction_Uom
1962 RETURN VARCHAR2
1963 IS
1964 BEGIN
1965
1966 IF g_ShopFloorMove_rec.transaction_uom IS NOT NULL THEN
1967 RETURN g_ShopFloorMove_rec.transaction_uom;
1968 END IF;
1969
1970 IF g_ShopFloorMove_rec.source_code = 'RCV' THEN
1971 RETURN g_ShopFloorMove_rec.primary_uom;
1972 END IF;
1973
1974 RETURN NULL;
1975
1976 EXCEPTION
1977 WHEN OTHERS THEN
1978 RETURN FND_API.G_MISS_NUM;
1979
1980 END Get_Transaction_Uom;
1981
1982 FUNCTION Get_Wip_Entity
1983 RETURN NUMBER
1984 IS
1985 BEGIN
1986
1987 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL THEN
1988 RETURN g_ShopFloorMove_rec.wip_entity_id;
1989 END IF;
1990
1991 IF g_OSP_rec.wip_entity_id IS NOT NULL THEN
1992 RETURN g_OSP_rec.wip_entity_id;
1993 END IF;
1994
1995 RETURN NULL;
1996
1997 EXCEPTION
1998 WHEN OTHERS THEN
1999 RETURN FND_API.G_MISS_NUM;
2000
2001 END Get_Wip_Entity;
2002
2003 FUNCTION Get_Wip_Entity_Name
2004 RETURN VARCHAR2
2005 IS
2006 BEGIN
2007
2008 IF g_ShopFloorMove_rec.wip_entity_name IS NOT NULL THEN
2009 RETURN g_ShopFloorMove_rec.wip_entity_name;
2010 END IF;
2011
2012 IF g_OSP_rec.wip_entity_name IS NOT NULL THEN
2013 RETURN g_OSP_rec.wip_entity_name;
2014 END IF;
2015
2016 IF g_Wip_Entities_rec.wip_entity_name IS NOT NULL THEN
2017 RETURN g_Wip_Entities_rec.wip_entity_name;
2018 END IF;
2019
2020 IF g_ShopFloorMove_rec.wip_entity_id IS NOT NULL THEN
2021 get_we_attr();
2022 RETURN g_ShopFloorMove_rec.wip_entity_name;
2023 END IF;
2024
2025 RETURN NULL;
2026
2027 EXCEPTION
2028 WHEN OTHERS THEN
2029 RETURN FND_API.G_MISS_CHAR;
2030
2031 END Get_Wip_Entity_Name;
2032
2033 PROCEDURE Get_Flex_Shopfloormove
2034 IS
2035 BEGIN
2036
2037 -- In the future call Flex APIs for defaults
2038
2039 IF g_ShopFloorMove_rec.attribute1 = FND_API.G_MISS_CHAR THEN
2040 g_ShopFloorMove_rec.attribute1 := NULL;
2041 END IF;
2042
2043 IF g_ShopFloorMove_rec.attribute10 = FND_API.G_MISS_CHAR THEN
2044 g_ShopFloorMove_rec.attribute10 := NULL;
2045 END IF;
2046
2047 IF g_ShopFloorMove_rec.attribute11 = FND_API.G_MISS_CHAR THEN
2048 g_ShopFloorMove_rec.attribute11 := NULL;
2049 END IF;
2050
2051 IF g_ShopFloorMove_rec.attribute12 = FND_API.G_MISS_CHAR THEN
2052 g_ShopFloorMove_rec.attribute12 := NULL;
2053 END IF;
2054
2055 IF g_ShopFloorMove_rec.attribute13 = FND_API.G_MISS_CHAR THEN
2056 g_ShopFloorMove_rec.attribute13 := NULL;
2057 END IF;
2058
2059 IF g_ShopFloorMove_rec.attribute14 = FND_API.G_MISS_CHAR THEN
2060 g_ShopFloorMove_rec.attribute14 := NULL;
2061 END IF;
2062
2063 IF g_ShopFloorMove_rec.attribute15 = FND_API.G_MISS_CHAR THEN
2064 g_ShopFloorMove_rec.attribute15 := NULL;
2065 END IF;
2066
2067 IF g_ShopFloorMove_rec.attribute2 = FND_API.G_MISS_CHAR THEN
2068 g_ShopFloorMove_rec.attribute2 := NULL;
2069 END IF;
2070
2071 IF g_ShopFloorMove_rec.attribute3 = FND_API.G_MISS_CHAR THEN
2072 g_ShopFloorMove_rec.attribute3 := NULL;
2073 END IF;
2074
2075 IF g_ShopFloorMove_rec.attribute4 = FND_API.G_MISS_CHAR THEN
2076 g_ShopFloorMove_rec.attribute4 := NULL;
2077 END IF;
2078
2079 IF g_ShopFloorMove_rec.attribute5 = FND_API.G_MISS_CHAR THEN
2080 g_ShopFloorMove_rec.attribute5 := NULL;
2081 END IF;
2082
2083 IF g_ShopFloorMove_rec.attribute6 = FND_API.G_MISS_CHAR THEN
2084 g_ShopFloorMove_rec.attribute6 := NULL;
2085 END IF;
2086
2087 IF g_ShopFloorMove_rec.attribute7 = FND_API.G_MISS_CHAR THEN
2088 g_ShopFloorMove_rec.attribute7 := NULL;
2089 END IF;
2090
2091 IF g_ShopFloorMove_rec.attribute8 = FND_API.G_MISS_CHAR THEN
2092 g_ShopFloorMove_rec.attribute8 := NULL;
2093 END IF;
2094
2095 IF g_ShopFloorMove_rec.attribute9 = FND_API.G_MISS_CHAR THEN
2096 g_ShopFloorMove_rec.attribute9 := NULL;
2097 END IF;
2098
2099 IF g_ShopFloorMove_rec.attribute_category = FND_API.G_MISS_CHAR THEN
2100 g_ShopFloorMove_rec.attribute_category := NULL;
2101 END IF;
2102
2103 END Get_Flex_Shopfloormove;
2104
2105 -- Procedure Attributes
2106 PROCEDURE Attributes
2107 ( p_ShopFloorMove_rec IN WIP_Transaction_PUB.Shopfloormove_Rec_Type
2108 , p_iteration IN NUMBER := NULL
2109 , x_ShopFloorMove_rec IN OUT NOCOPY WIP_Transaction_PUB.Shopfloormove_Rec_Type
2110 , p_OSP_rec IN WIP_Transaction_PUB.Res_Rec_Type
2111 )
2112 IS
2113 BEGIN
2114
2115 -- Check number of iterations.
2116
2117 IF nvl(p_iteration,1) > WIP_GLOBALS.G_MAX_DEF_ITERATIONS THEN
2118
2119 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2120 THEN
2121
2122 FND_MESSAGE.SET_NAME('WIP','WIP_DEF_MAX_ITERATION');
2123 FND_MSG_PUB.Add;
2124
2125 END IF;
2126
2127 RAISE FND_API.G_EXC_ERROR;
2128
2129 END IF;
2130
2131 -- Initialize global variables
2132
2133 g_ShopFloorMove_rec := WIP_Transaction_PUB.G_MISS_SHOPFLOORMOVE_REC;
2134 g_OSP_rec := WIP_Transaction_PUB.G_MISS_RES_REC;
2135 g_Wip_Entities_rec := WIP_Work_Order_PUB.G_MISS_WIP_ENTITIES_REC;
2136 g_osp_mov_details := WIP_Transaction_PUB.G_MISS_OSP_MOVE_DET_REC;
2137
2138 g_ShopFloorMove_rec := p_ShopFloorMove_rec;
2139 g_OSP_rec := p_OSP_rec;
2140
2141 -- Default missing attributes.
2142 g_ShopFloorMove_rec.source_code := Get_Source;
2143 g_ShopFloorMove_rec.source_line_id := Get_Source_Line;
2144 g_ShopFloorMove_rec.acct_period_id := Get_Acct_Period;
2145 g_ShopFloorMove_rec.created_by_name := Get_Created_By_Name;
2146 g_ShopFloorMove_rec.created_by := Get_Created_By;
2147 g_ShopFloorMove_rec.last_updated_by_name := Get_Last_Updated_By_Name;
2148 g_ShopFloorMove_rec.last_updated_by := Get_Last_Updated_By;
2149 g_ShopFloorMove_rec.last_update_login := Get_Last_Update_Login;
2150 g_ShopFloorMove_rec.request_id := Get_Request_Id;
2151 g_ShopFloorMove_rec.program_id := Get_Program_Id;
2152 g_ShopFloorMove_rec.program_application_id := Get_Program_Application_Id;
2153 g_ShopFloorMove_rec.program_update_date := Get_Program_Update_Date;
2154 g_ShopFloorMove_rec.organization_id := Get_Organization_Id;
2155 g_ShopFloorMove_rec.organization_code := Get_Organization_Code;
2156 g_ShopFloorMove_rec.primary_item_id := Get_Primary_Item;
2157 g_ShopFloorMove_rec.primary_uom := Get_Primary_Uom;
2158 g_ShopFloorMove_rec.transaction_uom := Get_Transaction_Uom;
2159 g_ShopFloorMove_rec.reason_id := Get_Reason;
2160 g_ShopFloorMove_rec.reference := Get_Reference;
2161 g_ShopFloorMove_rec.repetitive_schedule_id := Get_Repetitive_Schedule;
2162 g_ShopFloorMove_rec.line_id := Get_Line_Id;
2163 g_ShopFloorMove_rec.transaction_date := Get_Transaction_Date;
2164 g_ShopFloorMove_rec.wip_entity_id := Get_Wip_Entity;
2165 g_ShopFloorMove_rec.wip_entity_name := Get_Wip_Entity_Name;
2166 g_ShopFloorMove_rec.entity_type := Get_Entity_Type;
2167 g_ShopFloorMove_rec.fm_department_id := Get_Fm_Department_Id;
2168 g_ShopFloorMove_rec.fm_department_code := Get_Fm_Department_Code;
2169 g_ShopFloorMove_rec.fm_intraop_step_type := Get_Fm_Intraop_Step_Type;
2170 g_ShopFloorMove_rec.fm_operation_seq_num := Get_Fm_Operation_Seq_Num;
2171 g_ShopFloorMove_rec.fm_operation_code := Get_Fm_Operation;
2172 g_ShopFloorMove_rec.to_department_id := Get_To_Department_Id;
2173 g_ShopFloorMove_rec.to_department_code := Get_To_Department_Code;
2174 g_ShopFloorMove_rec.to_intraop_step_type := Get_To_Intraop_Step_Type;
2175 g_ShopFloorMove_rec.to_operation_seq_num := Get_To_Operation_Seq_Num;
2176 g_ShopFloorMove_rec.to_operation_code := Get_To_Operation;
2177 g_ShopFloorMove_rec.primary_quantity := Get_Primary_Quantity;
2178 g_ShopFloorMove_rec.transaction_quantity := Get_Transaction_Quantity;
2179 g_ShopFloorMove_rec.transaction_type := Get_Transaction_Type;
2180 g_ShopFloorMove_rec.group_id := Get_Group;
2181 g_ShopFloorMove_rec.kanban_card_id := Get_Kanban_Card;
2182 g_ShopFloorMove_rec.line_code := Get_Line_Code;
2183 g_ShopFloorMove_rec.overcpl_primary_qty := Get_Overcpl_Primary_Qty;
2184 g_ShopFloorMove_rec.overcpl_transaction_id := Get_Overcpl_Transaction;
2185 g_ShopFloorMove_rec.overcpl_transaction_qty := Get_Overcpl_Transaction_Qty;
2186 g_ShopFloorMove_rec.process_phase := Get_Process_Phase;
2187 g_ShopFloorMove_rec.process_status := Get_Process_Status;
2188 g_ShopFloorMove_rec.qa_collection_id := Get_Qa_Collection;
2189 g_ShopFloorMove_rec.reason_name := Get_Reason_Name;
2190 g_ShopFloorMove_rec.scrap_account_id := Get_Scrap_Account;
2191 g_ShopFloorMove_rec.transaction_id := Get_Transaction;
2192
2193 IF g_ShopFloorMove_rec.creation_date IS NULL THEN
2194 g_ShopFloorMove_rec.creation_date := Sysdate;
2195 END IF;
2196
2197 IF g_ShopFloorMove_rec.last_update_date IS NULL THEN
2198 g_ShopFloorMove_rec.last_update_date := Sysdate;
2199 END IF;
2200
2201 x_ShopFloorMove_rec := g_ShopFloorMove_rec;
2202
2203 END Attributes;
2204
2205 END WIP_Default_Shopfloormove;
[Home] [Help]