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