[Home] [Help]
PACKAGE BODY: APPS.FLM_EXECUTION_UTIL
Source
1 PACKAGE BODY flm_execution_util AS
2 /* $Header: FLMEXUTB.pls 120.22.12020000.3 2012/08/16 10:04:31 sisankar ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'FLM_EXECUTION_UTIL';
5 G_ORG_ID NUMBER := -1;
6 G_LINE_ID NUMBER := -1;
7 G_STD_OP_ID NUMBER := -1;
8 G_VIEW_ALL_SCHEDULES VARCHAR2(1) := 'U';
9 G_WORKSTATION_ENABLED VARCHAR2(10) := 'U';
10
11 FUNCTION get_data_entry_mode(
12 p_organization_id IN NUMBER,
13 p_line_id IN NUMBER,
14 p_operation_id IN NUMBER
15 )
16 RETURN VARCHAR2
17 IS
18 l_org_id NUMBER;
19 l_line_id NUMBER;
20 l_operation_id NUMBER;
21 l_data_entry_mode VARCHAR2(1);
22
23 CURSOR c_dem_lineop IS
24 SELECT data_entry_mode
25 FROM FLM_EXE_PREFERENCES
26 WHERE ORGANIZATION_ID = l_org_id
27 AND LINE_ID = l_line_id
28 AND STANDARD_OPERATION_ID = l_operation_id;
29
30 CURSOR c_dem_line IS
31 SELECT data_entry_mode
32 FROM FLM_EXE_PREFERENCES
33 WHERE ORGANIZATION_ID = l_org_id
34 AND LINE_ID = l_line_id
35 AND STANDARD_OPERATION_ID IS NULL;
36
37 CURSOR c_dem_org IS
38 SELECT data_entry_mode
39 FROM FLM_EXE_PREFERENCES
40 WHERE ORGANIZATION_ID = l_org_id
41 AND LINE_ID IS NULL
42 AND STANDARD_OPERATION_ID IS NULL;
43
44 BEGIN
45 l_org_id := p_organization_id;
46 l_line_id := p_line_id;
47 l_operation_id := p_operation_id;
48
49 --Find preference at line op level
50 FOR dem_lineop_rec in c_dem_lineop LOOP
51 l_data_entry_mode := dem_lineop_rec.data_entry_mode;
52 END LOOP;
53
54 --If lineop level pref doesn't exist, find line level
55 IF (l_data_entry_mode is null) THEN
56 FOR dem_line_rec in c_dem_line LOOP
57 l_data_entry_mode := dem_line_rec.data_entry_mode;
58 END LOOP;
59 END IF;
60
61 --If line level pref doesn't exist, find org level
62 IF (l_data_entry_mode is null) THEN
63 FOR dem_org_rec in c_dem_org LOOP
64 l_data_entry_mode := dem_org_rec.data_entry_mode;
65 END LOOP;
66 END IF;
67
68 --Finally if no level exist, return default value
69 IF(l_data_entry_mode is null) THEN
70 l_data_entry_mode := '1';
71 END IF;
72
73 RETURN l_data_entry_mode;
74
75 EXCEPTION
76 WHEN OTHERS THEN
77 RETURN '1';
78 END get_data_entry_mode;
79
80 FUNCTION get_view_all_schedules(
81 p_organization_id IN NUMBER,
82 p_line_id IN NUMBER,
83 p_operation_id IN NUMBER
84 )
85 RETURN VARCHAR2
86 IS
87 l_org_id NUMBER;
88 l_line_id NUMBER;
89 l_operation_id NUMBER;
90 l_view_all_sch VARCHAR2(1);
91
92 CURSOR c_view_all_lineop IS
93 SELECT VIEW_ALL_SCHEDULES
94 FROM FLM_EXE_PREFERENCES
95 WHERE ORGANIZATION_ID = l_org_id
96 AND LINE_ID = l_line_id
97 AND STANDARD_OPERATION_ID = l_operation_id;
98
99 CURSOR c_view_all_line IS
100 SELECT VIEW_ALL_SCHEDULES
101 FROM FLM_EXE_PREFERENCES
102 WHERE ORGANIZATION_ID = l_org_id
103 AND LINE_ID = l_line_id
104 AND STANDARD_OPERATION_ID IS NULL;
105
106 CURSOR c_view_all_org IS
107 SELECT VIEW_ALL_SCHEDULES
108 FROM FLM_EXE_PREFERENCES
109 WHERE ORGANIZATION_ID = l_org_id
110 AND LINE_ID IS NULL
111 AND STANDARD_OPERATION_ID IS NULL;
112
113 BEGIN
114 l_org_id := p_organization_id;
115 l_line_id := p_line_id;
116 l_operation_id := p_operation_id;
117
118 --Find preference at line op level
119 FOR view_all_lineop_rec in c_view_all_lineop LOOP
120 l_view_all_sch := view_all_lineop_rec.view_all_schedules;
121 END LOOP;
122
123 --If lineop level pref doesn't exist, find line level
124 IF (l_view_all_sch is null) THEN
125 FOR view_all_line_rec in c_view_all_line LOOP
126 l_view_all_sch := view_all_line_rec.view_all_schedules;
127 END LOOP;
128 END IF;
129
130 --If line level pref doesn't exist, find org level
131 IF (l_view_all_sch is null) THEN
132 FOR view_all_org_rec in c_view_all_org LOOP
133 l_view_all_sch := view_all_org_rec.view_all_schedules;
134 END LOOP;
135 END IF;
136
137 --Finally if no level exist, return default value
138 IF(l_view_all_sch is null) THEN
139 l_view_all_sch := 'N';
140 END IF;
141
142 RETURN l_view_all_sch;
143
144 EXCEPTION
145 WHEN OTHERS THEN
146 RETURN 'N';
147 END get_view_all_schedules;
148
149
150 FUNCTION view_all_schedules(i_op_seq_id IN NUMBER) RETURN VARCHAR2 IS
151 l_org_id NUMBER;
152 l_line_id NUMBER;
153 l_std_op_id NUMBER;
154 l_view_all_sch VARCHAR2(1);
155
156 CURSOR c_rtg IS
157 select bor.organization_id,
158 bor.line_id,
159 bos.standard_operation_id
160 from bom_operational_routings bor,
161 bom_operation_sequences bos
162 where bos.routing_sequence_id = bor.routing_sequence_id
163 and bos.operation_sequence_id = i_op_seq_id;
164
165 BEGIN
166
167 FOR c_rtg_rec in c_rtg LOOP
168 l_org_id := c_rtg_rec.organization_id;
169 l_line_id := c_rtg_rec.line_id;
170 l_std_op_id := c_rtg_rec.standard_operation_id;
171 END LOOP;
172 --Bug 13812197: caching the value for view_all_schedules parameter
173 IF (G_VIEW_ALL_SCHEDULES = 'U') then
174 l_view_all_sch := get_view_all_schedules(l_org_id, l_line_id, l_std_op_id);
175 G_VIEW_ALL_SCHEDULES := l_view_all_sch;--caching the value
176 ELSE
177 IF(l_org_id = G_ORG_ID AND l_line_id = G_LINE_ID AND l_std_op_id = G_STD_OP_ID) THEN
178 l_view_all_sch := G_VIEW_ALL_SCHEDULES;--getting cached value
179 END IF;
180 END IF;
181
182 return (l_view_all_sch);
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 RETURN 'N';
187 END view_all_schedules;
188
189
190
191 PROCEDURE debug_output(info VARCHAR2) IS
192 BEGIN
193 --dbms_output.put_line(info);
194 null;
195 END;
196
197 /******************************************************************
198 * To get workstation_enabled flag for given preference by *
199 * (org_id, line_id, operation_id). If the pref. does not exist, *
200 * retrieve it from its upper-leve; if the upper-level does not *
201 * exist, return the default flag 'Y' *
202 ******************************************************************/
203 PROCEDURE get_workstation_enabled(
204 p_organization_id IN NUMBER,
205 p_line_id IN NUMBER,
206 p_operation_id IN NUMBER,
207 p_init_msg_list IN VARCHAR2,
208 x_workstation_enabled OUT NOCOPY VARCHAR2,
209 x_return_status OUT NOCOPY VARCHAR2,
210 x_msg_count OUT NOCOPY NUMBER,
211 x_msg_data OUT NOCOPY VARCHAR2
212 )
213 IS
214 l_org_id NUMBER;
215 l_line_id NUMBER;
216 l_operation_id NUMBER;
217
218 CURSOR c_wkstn_enabled IS
219 SELECT
220 WORKSTATION_ENABLED
221 FROM
222 FLM_EXE_PREFERENCES
223 WHERE
224 ORGANIZATION_ID = l_org_id AND
225 NVL(LINE_ID,-1) = NVL(l_line_id,-1) AND
226 NVL(STANDARD_OPERATION_ID,-1) = nvl(l_operation_id,-1);
227
228 BEGIN
229
230 --SAVEPOINT get_workstation_enabled;
231
232 IF p_init_msg_list IS NOT NULL AND FND_API.TO_BOOLEAN(p_init_msg_list)
233 THEN
234 FND_MSG_PUB.initialize;
235 END IF;
236
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238 x_workstation_enabled := 'Y';
239
240 l_org_id := p_organization_id;
241 l_line_id := p_line_id;
242 l_operation_id := p_operation_id;
243
244 OPEN c_wkstn_enabled;
245
246 FETCH c_wkstn_enabled INTO x_workstation_enabled;
247
248 IF c_wkstn_enabled%NOTFOUND THEN
249 CLOSE c_wkstn_enabled;
250
251 -- look at upper-level preference
252 IF (p_line_id is not null) THEN
253 l_line_id := null;
254 l_operation_id := null;
255
256 IF (p_operation_id is not null) THEN
257 l_line_id := p_line_id;
258 END IF;
259
260 OPEN c_wkstn_enabled;
261 FETCH c_wkstn_enabled INTO x_workstation_enabled;
262 IF c_wkstn_enabled%NOTFOUND THEN --line level does not exist, fetch org level
263 CLOSE c_wkstn_enabled;
264 l_line_id := null;
265 l_operation_id := null;
266 OPEN c_wkstn_enabled;
267 FETCH c_wkstn_enabled INTO x_workstation_enabled;
268 ELSE
269 CLOSE c_wkstn_enabled;
270 END IF;
271
272 END IF;
273 ELSE
274 -- preference found
275 CLOSE c_wkstn_enabled;
276 END IF;
277
278 EXCEPTION
279 WHEN OTHERS THEN
280 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
281 x_workstation_enabled := 'N';
282 ROLLBACK TO get_workstation_enabled;
283
284 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
285 FND_MSG_PUB.Add_Exc_Msg ('flm_exe_pref' ,'get_workstation_enabled');
286 END IF;
287
288 FND_MSG_PUB.Count_And_Get (p_count => x_msg_count ,p_data => x_msg_data);
289
290 END get_workstation_enabled;
291
292
293 FUNCTION workstation_enabled(i_op_seq_id IN NUMBER) RETURN VARCHAR2 IS
294 l_org_id NUMBER;
295 l_line_id NUMBER;
296 l_std_op_id NUMBER;
297 l_enabled VARCHAR2(10);
298 l_status VARCHAR2(10);
299 l_msg_cnt NUMBER;
300 l_msg VARCHAR2(2000);
301 BEGIN
302 -- get parameter
303 select bor.organization_id, bor.line_id, bos.standard_operation_id
304 into l_org_id, l_line_id, l_std_op_id
305 from bom_operational_routings bor,
306 bom_operation_sequences bos
307 where bos.routing_sequence_id = bor.routing_sequence_id
308 and bos.operation_sequence_id = i_op_seq_id;
309 -- get enabled
310 IF(G_WORKSTATION_ENABLED = 'U') THEN
311 get_workstation_enabled(
312 p_organization_id => l_org_id,
313 p_line_id => l_line_id,
314 p_operation_id => l_std_op_id,
315 p_init_msg_list => 'T',
316 x_workstation_enabled => l_enabled,
317 x_return_status => l_status,
318 x_msg_count => l_msg_cnt,
319 x_msg_data => l_msg);
320 G_WORKSTATION_ENABLED := l_enabled;
321 ELSE
322 IF(l_org_id = G_ORG_ID AND l_line_id = G_LINE_ID AND l_std_op_id = G_STD_OP_ID) THEN
323 l_enabled := G_WORKSTATION_ENABLED;
324 END IF;
325 END IF;
326 RETURN l_enabled;
327 EXCEPTION
328 WHEN OTHERS THEN
329 RETURN 'N';
330 END workstation_enabled;
331
332 FUNCTION operation_eligible_completion(
333 i_org_id IN NUMBER,
334 i_wip_entity_id IN NUMBER,
335 i_std_op_id IN NUMBER) RETURN VARCHAR2 IS
336 l_cnt NUMBER := 0;
337 l_op_seq_id NUMBER := 0;
338
339 BEGIN
340 select bos.operation_sequence_id into l_op_seq_id
341 from bom_operation_sequences bos,
342 wip_flow_schedules wfs,
343 bom_operational_routings bor
344 where wfs.wip_entity_id = i_wip_entity_id
345 and bor.organization_id = i_org_id
346 and bor.assembly_item_id = wfs.primary_item_id
347 and nvl(bor.alternate_routing_designator, '########') = nvl(wfs.alternate_routing_designator, '########')
348 and bor.common_routing_sequence_id = bos.routing_sequence_id
349 and bos.operation_type = 3 -- line operation
350 and bos.standard_operation_id = i_std_op_id;
351
352 select count(*)
353 into l_cnt
354 from flm_exe_operations
355 where wip_entity_id = i_wip_entity_id
356 and operation_sequence_id = l_op_seq_id
357 and organization_id = i_org_id;
358 IF l_cnt > 0 THEN
359 RETURN 'N';
360 ELSE
361 RETURN 'Y';
362 END IF;
363 END operation_eligible_completion;
364
365 FUNCTION operation_eligible_vas_n(
366 i_org_id IN NUMBER,
367 i_wip_entity_id IN NUMBER,
368 i_op_seq_id IN NUMBER) RETURN VARCHAR2 IS
369
370 cursor c_bon_exists IS
371 select from_op_seq_id
372 from bom_operation_networks
373 where transition_type in (1, 2)
374 start with to_op_seq_id = i_op_seq_id
375 connect by level = 1 and prior from_op_seq_id = to_op_seq_id
376 and flm_execution_util.workstation_enabled(from_op_seq_id) = 'Y';
377
378 v_bon_exists c_bon_exists%ROWTYPE;
379 l_retStatus varchar2(1);
380 l_count number;
381
382 BEGIN
383 if i_op_seq_id is null then
384 return 'N';
385 end if;
386 -- Check Current op.
387 select count(1) into l_count
388 from flm_exe_operations
389 where wip_entity_id = i_wip_entity_id
390 and next_op_seq_id = i_op_seq_id
391 and organization_id = i_org_id
392 and current_flag = 'Y';
393 if l_count > 0 THEN
394 return 'Y';
395 end if;
396 -- Check completed op.
397 select count(1) into l_count
398 from flm_exe_operations
399 where wip_entity_id = i_wip_entity_id
400 and operation_sequence_id = i_op_seq_id
401 and organization_id = i_org_id;
402 if l_count > 0 then
403 return 'N';
404 end if;
405 open c_bon_exists;
406 fetch c_bon_exists into v_bon_exists;
407 l_count := c_bon_exists%ROWCOUNT;
408 close c_bon_exists;
409 if (l_count = 0) then
410 return 'Y';
411 else
412 select count(1) into l_count
413 from flm_exe_operations
414 where operation_sequence_id = v_bon_exists.from_op_seq_id
415 and wip_entity_id = i_wip_entity_id
416 and organization_id = i_org_id;
417 if l_count = 0 then
418 return 'N';
419 end if;
420 end if;
421 RETURN 'N';
422 EXCEPTION
423 WHEN OTHERS THEN
424 RETURN 'N';
425 END operation_eligible_vas_n;
426
427 FUNCTION Operation_Eligible(i_org_id IN NUMBER,
428 i_wip_entity_id IN NUMBER,
429 i_std_op_id IN NUMBER) RETURN VARCHAR2 IS
430
431 l_cnt NUMBER := 0;
432 l_op_seq_id NUMBER := 0;
433 l_line_id NUMBER := 0;
434
435 -- Cursor to find the sequence id of the operation in the routing
436 -- corresponding to the schedule that references the specified
437 -- standard operation.
438 CURSOR op_seq_csr IS
439 select bos.operation_sequence_id
440 from bom_operation_sequences bos,
441 wip_flow_schedules wfs,
442 bom_operational_routings bor
443 where wfs.wip_entity_id = i_wip_entity_id
444 and bor.organization_id = i_org_id
445 and bor.assembly_item_id = wfs.primary_item_id
446 and nvl(bor.alternate_routing_designator, '########') = nvl(wfs.alternate_routing_designator, '########')
447 and bor.common_routing_sequence_id = bos.routing_sequence_id
448 and bos.operation_type = 3 -- line operation
449 and bos.standard_operation_id = i_std_op_id;
450 -- Cursor to find out starting operations of this routing
451 CURSOR start_op_csr IS
452 select bos.operation_sequence_id seq_id,
453 bos.operation_seq_num seq_num
454 from bom_operation_sequences bos,
455 wip_flow_schedules wfs,
456 bom_operational_routings bor
457 where wfs.wip_entity_id = i_wip_entity_id
458 and bor.organization_id = i_org_id
459 and bor.assembly_item_id = wfs.primary_item_id
460 and nvl(bor.alternate_routing_designator, '@@@@@@@@') = nvl(wfs.alternate_routing_designator, '@@@@@@@@')
461 and bor.common_routing_sequence_id = bos.routing_sequence_id
462 and bos.operation_type = 3 -- line operation
463 and not exists (select '1'
464 from bom_operation_networks bon
465 where bon.to_op_seq_id = bos.operation_sequence_id
466 and bon.transition_type in (1, 2))
467 order by bos.operation_seq_num;
468
469 --cursor to find out if some operation is current for a schedule and disbaled
470 CURSOR sch_cur_op_csr IS
471 select next_op_seq_id seq_id
472 from flm_exe_operations
473 where wip_entity_id = i_wip_entity_id
474 and flm_execution_util.workstation_enabled(next_op_seq_id) = 'N'
475 order by next_op_seq_id;
476 l_view_all_sch VARCHAR2(1);
477 l_retStatus VARCHAR2(1);
478 l_enabled VARCHAR2(1);
479
480 BEGIN
481
482 --wip_logger.log('Start: Operation_Eligible i_org_id: ' || i_org_id || ' i_wip_entity_id: ' || i_wip_entity_id || ' i_std_op_id: ' || i_std_op_id, l_retStatus);
483 select line_id into l_line_id
484 from wip_flow_schedules wfs
485 where wfs.wip_entity_id = i_wip_entity_id
486 and wfs.organization_id = i_org_id;
487
488 --Bug 13812197: Initializing/Clearing cache
489 if(G_ORG_ID = -1) then --when no cache exists
490 G_ORG_ID := i_org_id;
491 G_LINE_ID := l_line_id;
492 G_STD_OP_ID := i_std_op_id;
493 else --when cache exits
494 if(G_ORG_ID <> i_org_id OR G_LINE_ID <> l_line_id OR G_STD_OP_ID <> i_std_op_id) then
495 --if any of the 3 parameters are changed the reset the cache
496 G_VIEW_ALL_SCHEDULES := 'U';
497 G_WORKSTATION_ENABLED := 'U';
498 end if;
499 end if;
500
501 -- Is this operation in the routing of the assembly for the schedule?
502 OPEN op_seq_csr;
503 FETCH op_seq_csr INTO l_op_seq_id;
504 IF op_seq_csr%NOTFOUND THEN
505 CLOSE op_seq_csr;
506 --debug_output('op not in routing');
507 --wip_logger.log('op not in routing N', l_retStatus);
508 RETURN 'N';
509 END IF;
510 CLOSE op_seq_csr;
511
512 --if view_all_schedules preference is set to yes, then only need to
513 --perform validation that schedule is not already completed on this op
514 l_view_all_sch := view_all_schedules(l_op_seq_id);
515 if(l_view_all_sch = 'Y' ) then
516 -- has this operation been completed?
517 select count(*)
518 into l_cnt
519 from flm_exe_operations
520 where wip_entity_id = i_wip_entity_id
521 and operation_sequence_id = l_op_seq_id
522 and organization_id = i_org_id;
523 IF l_cnt > 0 THEN
524 --wip_logger.log('1.0 records exist in FEO N', l_retStatus);
525 RETURN 'N';
526 ELSE
527 --wip_logger.log('1.0 records DONT exist in FEO Y', l_retStatus);
528 RETURN 'Y';
529 END IF;
530 end if;
531
532 -- Is this operation current?
533 select count(*)
534 into l_cnt
535 from flm_exe_operations
536 where wip_entity_id = i_wip_entity_id
537 and next_op_seq_id = l_op_seq_id
538 and organization_id = i_org_id
539 and current_flag = 'Y';
540 IF l_cnt > 0 THEN
541 --wip_logger.log('operation is current Y', l_retStatus);
542 RETURN 'Y';
543 END IF;
544
545 -- has this operation been completed?
546 select count(*)
547 into l_cnt
548 from flm_exe_operations
549 where wip_entity_id = i_wip_entity_id
550 and operation_sequence_id = l_op_seq_id
551 and organization_id = i_org_id;
552 IF l_cnt > 0 THEN
553 --wip_logger.log('2.0 records exist in FEO N', l_retStatus);
554 RETURN 'N';
555 END IF;
556
557 -- Is this operation the start point of its path (or are all operations
558 -- before it on the path workstation-disabled?)
559 select count(*)
560 into l_cnt
561 from bom_operation_networks
562 where to_op_seq_id = l_op_seq_id
563 and transition_type in (1,2);
564 IF l_cnt <= 0 THEN
565 --debug_output('start of network');
566 --wip_logger.log('start of network Y', l_retStatus);
567 RETURN 'Y';
568 END IF;
569
570 --find out if connection from start op to this operation consist of all disbaled workstations
571 FOR op_rec IN start_op_csr LOOP
572 l_enabled := flm_execution_util.workstation_enabled(op_rec.seq_id);
573 if(l_enabled = 'N') then
574 select count(*)
575 into l_cnt
576 from bom_operation_networks
577 where to_op_seq_id = l_op_seq_id
578 and flm_execution_util.workstation_enabled(from_op_seq_id) = 'N'
579 --and flm_execution_util.workstation_enabled(op_rec.seq_id) = 'N'
580 start with from_op_seq_id = op_rec.seq_id
581 connect by prior to_op_seq_id = from_op_seq_id
582 and transition_type in (1, 2)
583 and flm_execution_util.workstation_enabled(from_op_seq_id) = 'N';
584 IF l_cnt > 0 THEN
585 --wip_logger.log('disbaled workstations validation Y', l_retStatus);
586 RETURN 'Y';
587 END IF;
588 end if;
589 END LOOP;
590
591 --return true if there is connection between current op and given lineop consist of all disabled ws
592 FOR op_rec IN sch_cur_op_csr LOOP
593 select count(*)
594 into l_cnt
595 from bom_operation_networks
596 where to_op_seq_id = l_op_seq_id
597 and flm_execution_util.workstation_enabled(from_op_seq_id) = 'N'
598 start with from_op_seq_id = op_rec.seq_id
599 connect by prior to_op_seq_id = from_op_seq_id
600 and transition_type in (1, 2)
601 and flm_execution_util.workstation_enabled(from_op_seq_id) = 'N';
602 IF l_cnt > 0 THEN
603 --wip_logger.log('connection between current op and given lineop Y', l_retStatus);
604 RETURN 'Y';
605 END IF;
606 END LOOP;
607
608 --wip_logger.log('End: Operation_Eligible', l_retStatus);
609 --if none of the conditions are true then this schedule is not current for this operation
610 RETURN 'N';
611
612 EXCEPTION
613 WHEN OTHERS THEN
614 --wip_logger.log('exception N', l_retStatus);
615 RETURN 'N';
616
617 END operation_eligible;
618
619
620 PROCEDURE complete_operation(i_org_id number,
621 i_wip_entity_id number,
622 i_op_seq_id number,
623 i_next_op_id number) IS
624 BEGIN
625 insert into flm_exe_operations (
626 wip_entity_id,
627 organization_id,
628 operation_sequence_id,
629 next_op_seq_id,
630 current_flag,
631 created_by,
632 creation_date,
633 last_updated_by,
634 last_update_date,
635 last_update_login,
636 object_version_number
637 ) values (
638 i_wip_entity_id,
639 i_org_id,
640 i_op_seq_id,
641 i_next_op_id,
642 'Y',
643 1111,
644 sysdate,
645 1111,
646 sysdate,
647 1111,
648 1
649 );
650
651 update flm_exe_operations
652 set current_flag = 'N',
653 object_version_number = object_version_number + 1
654 where next_op_seq_id = i_op_seq_id
655 and wip_entity_id = i_wip_entity_id;
656
657 END complete_operation;
658
659
660 /******************************************************************
661 * To get the components for schedule assembly bom *
662 ******************************************************************/
663 PROCEDURE get_custom_attributes (p_wip_entity_id IN NUMBER,
664 p_op_seq_id IN NUMBER,
665 p_op_type IN NUMBER, --1event,2process,3lineop
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2,
669 x_cust_attrib_tab OUT NOCOPY System.FlmCustomPropRecTab) IS
670 l_ret_status VARCHAR2(1);
671 l_msg_count NUMBER;
672 l_msg_data VARCHAR2(2000);
673 l_cust_attrib_tab FLM_CUST_ATTRIBUTE_TBL;
674 l_out_attrib_tab System.FlmCustomPropRecTab := System.FlmCustomPropRecTab();
675
676 BEGIN
677 get_attributes (1.0, --api_version
678 p_wip_entity_id,
679 p_op_seq_id,
680 p_op_type,
681 l_cust_attrib_tab,
682 l_ret_status,
683 l_msg_count,
684 l_msg_data);
685
686 IF(l_cust_attrib_tab.COUNT > 0) THEN
687 l_out_attrib_tab.EXTEND(l_cust_attrib_tab.COUNT);
688 FOR i in l_cust_attrib_tab.FIRST .. l_cust_attrib_tab.LAST
689 LOOP
690 l_out_attrib_tab(i) := System.FlmCustomPropRecType(
691 l_cust_attrib_tab(i).ATTRIBUTE_NAME,
692 l_cust_attrib_tab(i).ATTRIBUTE_VALUE);
693
694 END LOOP;
695
696 x_cust_attrib_tab := l_out_attrib_tab;
697
698 END IF;
699
700 EXCEPTION
701 WHEN OTHERS THEN
702 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
703
704 END get_custom_attributes;
705
706
707 PROCEDURE get_attributes (p_api_version_number IN NUMBER,
708 p_wip_entity_id IN NUMBER,
709 p_op_seq_id IN NUMBER,
710 p_op_type IN NUMBER,
711 p_cust_attrib_tab OUT NOCOPY FLM_CUST_ATTRIBUTE_TBL,
712 x_return_status OUT NOCOPY VARCHAR2,
713 x_msg_count OUT NOCOPY NUMBER,
714 x_msg_data OUT NOCOPY VARCHAR2) IS
715 l_api_version_number CONSTANT NUMBER := 1.0;
716 l_api_name CONSTANT VARCHAR2(30) := 'Get_Attributes';
717
718 BEGIN
719 IF NOT FND_API.Compatible_API_Call
720 ( l_api_version_number,
721 p_api_version_number,
722 l_api_name,
723 G_PKG_NAME)
724 THEN
725 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
726 END IF;
727
728 --Add custom code here
729 --Example
730 FOR i in 1 .. 5 LOOP
731 p_cust_attrib_tab(i).ATTRIBUTE_NAME := 'Property'||to_char(i)||' Name';
732 p_cust_attrib_tab(i).ATTRIBUTE_VALUE := 'Property'||to_char(i)||' Value';
733 END LOOP;
734 --End of custom code
735
736 x_return_status := FND_API.G_RET_STS_SUCCESS;
737
738 -- Get message count and data
739 FND_MSG_PUB.Count_And_Get
740 ( p_count => x_msg_count,
741 p_data => x_msg_data
742 );
743
744 EXCEPTION
745 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
746 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
747
748 -- Get message count and data
749 FND_MSG_PUB.Count_And_Get
750 ( p_count => x_msg_count
751 , p_data => x_msg_data
752 );
753
754 WHEN OTHERS THEN
755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
756 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
757 FND_MSG_PUB.Add_Exc_Msg
758 ( G_PKG_NAME
759 , 'Get_Attribute'
760 );
761 END IF;
762
763 -- Get message count and data
764 FND_MSG_PUB.Count_And_Get
765 ( p_count => x_msg_count
766 , p_data => x_msg_data
767 );
768
769 END get_attributes;
770
771
772 FUNCTION check_phantom (p_top_bill_seq_id NUMBER,
773 p_explosion_type VARCHAR2,
774 p_org_id IN NUMBER,
775 p_comp_seq_id IN NUMBER,
776 p_sort_order IN VARCHAR2) RETURN NUMBER IS
777
778 l_sort_order VARCHAR2(240);
779 l_sort_order_length NUMBER;
780 l_loop_count NUMBER;
781 l_temp_sort_order VARCHAR2(240);
782 l_overall_count NUMBER := 0;
783 l_count NUMBER := 0;
784 l_sort_code_width NUMBER := BOM_COMMON_DEFINITIONS.G_Bom_SortCode_Width;
785
786 CURSOR bom_cursor (bill_seq IN NUMBER,
787 exp_type IN VARCHAR2,
788 sort_ord IN VARCHAR2) IS
789 select count(top_bill_sequence_id) count
790 from bom_explosions be, bom_inventory_components bic
791 where top_bill_sequence_id = bill_seq
792 and explosion_type = exp_type
793 and sort_order = sort_ord
794 and be.component_sequence_id = bic.component_sequence_id
795 and bic.wip_supply_type <> 6;
796
797 BEGIN
798 l_sort_order := p_sort_order;
799 l_sort_order := substr(l_sort_order, 0, length(l_sort_order)-l_sort_code_width);
800 l_sort_order_length := length(l_sort_order);
801 l_loop_count := l_sort_order_length/l_sort_code_width;
802
803 FOR i in 2 .. l_loop_count LOOP
804 l_temp_sort_order := substr(l_sort_order,0,i*l_sort_code_width);
805 l_count := 0;
806
807 FOR l_bom_cr in bom_cursor(p_top_bill_seq_id, p_explosion_type,l_temp_sort_order) LOOP
808 l_count := l_bom_cr.count;
809 END loop;
810
811 l_overall_count := l_overall_count + l_count;
812
813 END LOOP;
814
815 return l_overall_count;
816
817 END check_phantom;
818
819
820 FUNCTION get_current_rev (p_org_id NUMBER,
821 p_component_item_id NUMBER) RETURN VARCHAR2 IS
822 l_current_rev VARCHAR2(3);
823 BEGIN
824
825
826 bom_revisions.Get_Revision(
827 type => 'PART',
828 eco_status => 'ALL',
829 examine_type => 'IMPL_ONLY',
830 org_id => p_org_id,
831 item_id => p_component_item_id,
832 rev_date => sysdate,
833 itm_rev => l_current_rev);
834
835 return l_current_rev;
836
837 END get_current_rev;
838
839
840 FUNCTION get_reference_designator(p_comp_seq_id NUMBER) RETURN VARCHAR2 IS
841 CURSOR ref_desig IS
842 select component_reference_designator
843 from bom_reference_designators
844 where component_sequence_id = p_comp_seq_id
845 order by component_reference_designator;
846 desig_string VARCHAR2(40);
847 ref_count NUMBER;
848 begin
849
850 ref_count := 0;
851 for ref_desig_c IN ref_desig LOOP
852 ref_count := ref_count+1;
853 if(ref_count = 1) then
854 desig_string := ref_desig_c.component_reference_designator;
855 elsif( (ref_count > 1) AND (ref_count < g_ref_desig_max_count+1) ) then
856 desig_string := desig_string||g_ref_desig_separator||ref_desig_c.component_reference_designator;
857 elsif(ref_count > g_ref_desig_max_count) then
858 desig_string := desig_string||g_ref_desig_terminator;
859 exit;
860 end if;
861 end loop;
862 return desig_string;
863
864 end get_reference_designator;
865
866
867 procedure pick_release(p_wip_entity_id NUMBER,
868 p_org_id NUMBER,
869 x_return_status OUT NOCOPY VARCHAR2,
870 x_msg_data OUT NOCOPY VARCHAR2) IS
871 l_alloc_tbl wip_picking_pub.allocate_tbl_t;
872 l_plan_tasks BOOLEAN;
873 l_cutoff_date DATE;
874 l_mo_req_number VARCHAR2(30);
875 l_conc_req_id NUMBER;
876 l_print_pickslips VARCHAR2(1);
877 l_grouping_rule NUMBER := -1;
878 l_return_status VARCHAR2(1);
879 l_msg_data VARCHAR2(2000);
880
881 CURSOR c_default_pick_group IS
882 select pickslip_grouping_rule_id
883 from wip_parameters
884 where organization_id = p_org_id;
885
886 begin
887
888 l_alloc_tbl(1).wip_entity_id := p_wip_entity_id;
889
890 l_alloc_tbl(1).use_pickset_flag := 'Y';
891 l_cutoff_date := null;
892 l_plan_tasks := FALSE;
893 l_print_pickslips := 'T';
894 --l_grouping_rule := ????;
895
896 FOR l_pick_grp in c_default_pick_group LOOP
897 l_grouping_rule := l_pick_grp.pickslip_grouping_rule_id;
898 END LOOP;
899
900 if(l_grouping_rule = -1) then--no grouping rule found
901 x_return_status := 'F';
902 return;
903 end if;
904
905 wip_picking_pub.allocate(p_alloc_tbl => l_alloc_tbl,
906 p_wip_entity_type => 4,
907 p_cutoff_date => l_cutoff_date,
908 p_organization_id => p_org_id,
909 p_pick_grouping_rule_id => l_grouping_rule,
910 p_print_pick_slip => l_print_pickslips,
911 p_plan_tasks => l_plan_tasks,
912 x_mo_req_number => l_mo_req_number,
913 x_conc_req_id => l_conc_req_id,
914 x_return_status => l_return_status,
915 x_msg_data => l_msg_data);
916
917 --return status = S for Successful, F for Fail
918 if(l_return_status = 'P') then
919 x_return_status := 'F';
920 elsif(l_return_status = 'N') then
921 x_return_status := 'F';
922 elsif(l_return_status = fnd_api.g_ret_sts_success) then
923 x_return_status := 'S';
924 else
925 x_return_status := 'S';
926 end if;
927 x_msg_data := l_msg_data;
928 return;
929
930 end pick_release;
931
932
933
934 /****************************************************
935 * This function finds out if the current move *
936 * is within from primary path or from feeder line *
937 * return_status = 'Y' for feeder move *
938 * return_status = 'N' for primary path move *
939 ***************************************************/
940 function is_move_from_feeder(p_from_op_seq_id NUMBER,
941 p_to_op_seq_id NUMBER) return VARCHAR2 IS
942
943 l_ret_val_no VARCHAR2(1) := 'N';
944 l_ret_val_yes VARCHAR2(1) := 'Y';
945 l_op_seq_id NUMBER;
946 l_from_op_seq_id NUMBER;
947 l_to_op_seq_id NUMBER;
948
949 CURSOR start_op_csr IS
950 select min(operation_seq_num) operation_seq_num from
951 (
952 select myFrom, operation_seq_num from
953 (
954 select from_op_seq_id myFrom, to_op_seq_id, transition_type, operation_seq_num
955 from bom_operation_networks, bom_operation_sequences
956 where from_op_seq_id = operation_sequence_id
957 start with to_op_seq_id = l_op_seq_id and transition_type in (1,2)
958 connect by PRIOR from_op_seq_id = to_op_seq_id
959 and transition_type in (1,2)
960 )
961 where not exists
962 (select from_op_seq_id
963 from bom_operation_networks
964 where to_op_seq_id = myFrom
965 and transition_type in (1,2)
966 )
967 );
968
969 cursor incoming_op_csr IS
970 select from_op_seq_id
971 from bom_operation_networks
972 where to_op_seq_id = l_to_op_seq_id
973 and transition_type in (1,2)
974 and from_op_seq_id not in (l_from_op_seq_id);
975
976 cursor op_seq_num_csr IS
977 select operation_seq_num
978 from bom_operation_sequences
979 where operation_sequence_id = l_op_seq_id;
980
981 cursor incoming_op_count_csr IS
982 select count(from_op_seq_id) op_count
983 from bom_operation_networks
984 where to_op_seq_id = l_to_op_seq_id
985 and transition_type in (1,2);
986
987
988 l_cur_min_op_seq_num NUMBER := -1;
989 l_other_min_op_seq_num NUMBER := -1;
990 BEGIN
991
992 --if only one operation, then its not the feeder injection
993 l_to_op_seq_id := p_to_op_seq_id;
994 for incoming_count_csr in incoming_op_count_csr loop
995 if(nvl(incoming_count_csr.op_count,0) < 2) then
996 return l_ret_val_no;
997 end if;
998 end loop;
999
1000 --first find out the minimum starting op seq on the current path
1001 l_op_seq_id := p_from_op_seq_id;
1002 for l_start in start_op_csr loop
1003 l_cur_min_op_seq_num := l_start.operation_seq_num;
1004 end loop;
1005 if(l_cur_min_op_seq_num = -1) then
1006 l_op_seq_id := p_from_op_seq_id;
1007 for l_seq in op_seq_num_csr loop
1008 l_cur_min_op_seq_num := l_seq.operation_seq_num;
1009 end loop;
1010 end if;
1011
1012 --if current starting op is greater than any start op on other path
1013 --then this definitely is a feeder
1014 l_from_op_seq_id := p_from_op_seq_id;
1015 l_to_op_seq_id := p_to_op_seq_id;
1016 for incoming_csr in incoming_op_csr loop
1017
1018 l_op_seq_id := incoming_csr.from_op_seq_id;
1019 l_other_min_op_seq_num := -1;
1020 for l_start in start_op_csr loop
1021 l_other_min_op_seq_num := nvl(l_start.operation_seq_num,-1);
1022 end loop;
1023
1024 if(l_other_min_op_seq_num = -1) then
1025 for l_seq in op_seq_num_csr loop
1026 l_other_min_op_seq_num := l_seq.operation_seq_num;
1027 end loop;
1028 end if;
1029
1030 if(l_cur_min_op_seq_num > l_other_min_op_seq_num) then
1031 return l_ret_val_yes;
1032 end if;
1033
1034 end loop;
1035
1036 -- return no if not feeder yet
1037 return l_ret_val_no;
1038
1039 EXCEPTION
1040 when others then
1041 return l_ret_val_no;
1042
1043 end is_move_from_feeder;
1044
1045
1046 procedure generate_serial_to_record(p_org_id IN NUMBER,
1047 p_wip_entity_id IN NUMBER,
1048 p_primary_item_id IN NUMBER,
1049 p_gen_qty IN NUMBER,
1050 x_ret_code OUT NOCOPY VARCHAR2,
1051 x_msg_buf OUT NOCOPY VARCHAR2) IS
1052
1053 l_org_id NUMBER;
1054 l_wip_entity_id NUMBER;
1055 l_primary_item_id NUMBER;
1056 l_gen_qty NUMBER;
1057 l_ret_code VARCHAR2(10);
1058 l_err_buf VARCHAR2(2000);
1059 BEGIN
1060 l_org_id := p_org_id;
1061 l_wip_entity_id := p_wip_entity_id;
1062 l_primary_item_id := p_primary_item_id;
1063 l_gen_qty := p_gen_qty;
1064
1065 INV_SERIAL_NUMBER_PUB.GENERATE_SERIALS
1066 (
1067 x_retcode => l_ret_code,
1068 x_errbuf => l_err_buf,
1069 p_org_id => l_org_id,
1070 p_item_id => l_primary_item_id,
1071 p_qty => l_gen_qty,
1072 p_serial_code => null,
1073 p_wip_id => null,
1074 p_rev => null,
1075 p_lot => null
1076 );
1077 x_ret_code := l_ret_code;
1078 x_msg_buf := l_err_buf;
1079
1080 END generate_serial_to_record;
1081
1082
1083 PROCEDURE generate_lot_to_record (p_org_id IN NUMBER,
1084 p_primary_item_id IN NUMBER,
1085 o_lot_number OUT NOCOPY VARCHAR2,
1086 x_return_status OUT NOCOPY VARCHAR2,
1087 x_msg_count OUT NOCOPY NUMBER,
1088 x_msg_data OUT NOCOPY VARCHAR2) IS
1089 l_lot_number VARCHAR2(30);
1090 l_object_id NUMBER;
1091 l_exp_date DATE := null;
1092 BEGIN
1093 l_lot_number := inv_lot_api_pub.auto_gen_lot(
1094 p_org_id => p_org_id,
1095 p_inventory_item_id => p_primary_item_id,
1096 p_api_version => 1.0,
1097 p_commit => fnd_api.g_true,
1098 x_return_status => x_return_status,
1099 x_msg_count => x_msg_count,
1100 x_msg_data => x_msg_data);
1101 if(x_return_status = 'S') then
1102 INV_LOT_API_PUB.InsertLot (
1103 p_api_version => 1.0,
1104 p_init_msg_list => 'F',
1105 p_commit => 'T',
1106 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1107 p_inventory_item_id => p_primary_item_id,
1108 p_organization_id => p_org_id,
1109 p_lot_number => l_lot_number,
1110 p_expiration_date => l_exp_date,
1111 x_object_id => l_object_id,
1112 x_return_status => x_return_status,
1113 x_msg_count => x_msg_count,
1114 x_msg_data => x_msg_data );
1115
1116 if(x_return_status = 'S') then
1117 o_lot_number := l_lot_number;
1118 else
1119 o_lot_number := null;
1120 end if;
1121 END IF;
1122 END generate_lot_to_record;
1123
1124
1125 PROCEDURE get_eligible_ops (p_org_id IN NUMBER,
1126 p_line_id IN NUMBER,
1127 p_rtg_seq_id IN NUMBER,
1128 p_wip_entity_id IN NUMBER,
1129 x_lop_tbl OUT NOCOPY operation_seq_tbl_type) IS
1130 CURSOR all_ops(p_rtg_seq_id NUMBER) IS
1131 select operation_sequence_id
1132 from bom_operation_sequences
1133 where routing_sequence_id = p_rtg_seq_id
1134 and operation_type = 3;
1135
1136 CURSOR lowest_op(p_rtg_seq_id NUMBER) IS
1137 select operation_sequence_id
1138 from bom_operation_sequences
1139 where routing_sequence_id = p_rtg_seq_id
1140 and operation_type = 3
1141 and operation_seq_num = (
1142 select min(operation_seq_num)
1143 from bom_operation_sequences
1144 where routing_sequence_id = p_rtg_seq_id
1145 and operation_type = 3);
1146
1147
1148 CURSOR completed_ops(p_org_id NUMBER,
1149 p_wip_ent_id NUMBER) IS
1150 select distinct operation_sequence_id
1151 from flm_exe_operations
1152 where wip_entity_id = p_wip_ent_id
1153 and organization_id = p_org_id
1154 order by operation_sequence_id;
1155
1156 CURSOR completed_operations(p_org_id NUMBER,
1157 p_wip_ent_id NUMBER) IS
1158 select distinct operation_sequence_id, next_op_seq_id
1159 from flm_exe_operations
1160 where wip_entity_id = p_wip_ent_id
1161 and organization_id = p_org_id
1162 order by operation_sequence_id;
1163
1164 CURSOR primary_ops(p_op_seq_id NUMBER) IS
1165 select from_op_seq_id, to_op_seq_id, transition_type, operation_seq_num
1166 from bom_operation_networks, bom_operation_sequences
1167 where from_op_seq_id = operation_sequence_id and transition_type=1
1168 start with from_op_seq_id = p_op_seq_id
1169 connect by PRIOR to_op_seq_id = from_op_seq_id
1170 and prior transition_type =1;
1171
1172 CURSOR event_seq_num (p_rtg_seq_id NUMBER, p_lop_seq_id NUMBER) IS
1173 select operation_seq_num
1174 from bom_operation_sequences
1175 where routing_sequence_id = p_rtg_seq_id
1176 and line_op_seq_id = p_lop_seq_id
1177 and operation_type = 1;
1178
1179 CURSOR next_op_count(p_org_id NUMBER, p_wip_ent_id NUMBER, p_from_op_seq NUMBER) IS
1180 select count(operation_sequence_id) opcount
1181 from flm_exe_operations
1182 where wip_entity_id = p_wip_ent_id
1183 and organization_id = p_org_id
1184 and operation_sequence_id = p_from_op_seq;
1185
1186 l_op_seq_tbl operation_seq_tbl_type;
1187 l_all_op_seq_tbl operation_seq_tbl_type;
1188 v_idx NUMBER;
1189 l_bf_option NUMBER;
1190 l_event_seq_num_tbl operation_seq_tbl_type;
1191 TYPE event_seq_num_tbl_type IS TABLE OF APPS.BOM_OPERATION_SEQUENCES.OPERATION_SEQ_NUM%TYPE;
1192 l_event_seq_num_tbl1 event_seq_num_tbl_type;
1193 l_completed_op_exist boolean := false;
1194 l_op_count NUMBER;
1195
1196 BEGIN
1197 l_bf_option := get_backflush_option(p_org_id, p_line_id);
1198
1199 if(l_bf_option = G_BFLUSH_OPTION_ALL) then --All operations to be included in backflush
1200 for c_all_ops in all_ops(p_rtg_seq_id) loop
1201 l_all_op_seq_tbl(c_all_ops.operation_sequence_id) := c_all_ops.operation_sequence_id;
1202 end loop;
1203 elsif(l_bf_option = G_BFLUSH_OPTION_ACT_PRI) then --Only Actual/Primary operations to be included in backflush
1204 /*
1205 for c_completed_ops in completed_ops(p_org_id, p_wip_entity_id) LOOP
1206 l_completed_op_exist := true;
1207 l_op_seq_tbl(c_completed_ops.operation_sequence_id) := c_completed_ops.operation_sequence_id;
1208 for c_primary_ops in primary_ops(c_completed_ops.operation_sequence_id) LOOP
1209 l_all_op_seq_tbl(c_primary_ops.from_op_seq_id) := c_primary_ops.from_op_seq_id;
1210 l_all_op_seq_tbl(c_primary_ops.to_op_seq_id) := c_primary_ops.to_op_seq_id;
1211 end loop;
1212 end loop;
1213 */
1214 for c_completed_ops in completed_operations(p_org_id, p_wip_entity_id) LOOP
1215 l_completed_op_exist := true;
1216 l_all_op_seq_tbl(c_completed_ops.operation_sequence_id) := c_completed_ops.operation_sequence_id;
1217 -- Bug 13257899.
1218 if c_completed_ops.next_op_seq_id is not null then
1219 l_all_op_seq_tbl(c_completed_ops.next_op_seq_id) := c_completed_ops.next_op_seq_id;
1220 end if;
1221 --bug 5599353
1222 --find if any operation is completed after this op, if yes, then just follow this path,
1223 --otherwise follow primary path from here
1224 l_op_count := 0;
1225 for c_next_op_count in next_op_count(p_org_id, p_wip_entity_id, c_completed_ops.next_op_seq_id) loop
1226 l_op_count := c_next_op_count.opcount;
1227 if (l_op_count = 0) then --follow primary path
1228 for c_primary_ops in primary_ops(c_completed_ops.next_op_seq_id) LOOP
1229 l_all_op_seq_tbl(c_primary_ops.from_op_seq_id) := c_primary_ops.from_op_seq_id;
1230 l_all_op_seq_tbl(c_primary_ops.to_op_seq_id) := c_primary_ops.to_op_seq_id;
1231 end loop;
1232 else
1233 null; --do nothing
1234 end if;
1235 end loop;
1236 end loop;
1237
1238 if(l_completed_op_exist <> true) then --if no completed op exist, then we find out lowest op seq num and follow primary path
1239 for c_lowest_op in lowest_op(p_rtg_seq_id) loop
1240 l_op_seq_tbl(c_lowest_op.operation_sequence_id) := c_lowest_op.operation_sequence_id;
1241 for c_primary_ops in primary_ops(c_lowest_op.operation_sequence_id) LOOP
1242 l_all_op_seq_tbl(c_primary_ops.from_op_seq_id) := c_primary_ops.from_op_seq_id;
1243 l_all_op_seq_tbl(c_primary_ops.to_op_seq_id) := c_primary_ops.to_op_seq_id;
1244 end loop;
1245 end loop;
1246 end if;
1247 end if;
1248
1249 v_idx := l_all_op_seq_tbl.FIRST;
1250 WHILE v_idx IS NOT NULL LOOP
1251 for c_event_seq_num in event_seq_num(p_rtg_seq_id, l_all_op_seq_tbl(v_idx)) loop
1252 l_event_seq_num_tbl(c_event_seq_num.operation_seq_num) := c_event_seq_num.operation_seq_num;
1253 end loop;
1254 v_idx := l_all_op_seq_tbl.NEXT(v_idx);
1255 END LOOP;
1256
1257 x_lop_tbl := l_event_seq_num_tbl;
1258 /*
1259 v_idx := x_lop_tbl.FIRST;
1260 WHILE v_idx IS NOT NULL LOOP
1261 --dbms_output.put_line('eligible ops='||x_lop_tbl(v_idx));
1262 v_idx := x_lop_tbl.NEXT(v_idx);
1263 END LOOP;
1264 */
1265
1266 END get_eligible_ops;
1267
1268
1269
1270 PROCEDURE get_recorded_event_seq_num (p_org_id IN NUMBER,
1271 p_wip_entity_id IN NUMBER,
1272 x_event_tbl OUT NOCOPY operation_seq_tbl_type) IS
1273
1274 CURSOR recorded_ops(p_org_id NUMBER,
1275 p_wip_ent_id NUMBER) IS
1276 select distinct operation_seq_num
1277 from flm_exe_req_operations fero
1278 where fero.organization_id = p_org_id
1279 and fero.wip_entity_id = p_wip_ent_id;
1280
1281 BEGIN
1282 for c_recorded_ops in recorded_ops(p_org_id, p_wip_entity_id) loop
1283 x_event_tbl(c_recorded_ops.operation_seq_num) := c_recorded_ops.operation_seq_num;
1284 end loop;
1285
1286
1287 END get_recorded_event_seq_num;
1288
1289
1290
1291
1292 FUNCTION get_backflush_option(p_org_id IN NUMBER, p_line_id IN NUMBER) RETURN NUMBER IS
1293 --1 = Actual/Primary
1294 --2 = ALL
1295
1296 CURSOR line_bf_option(p_org_id IN NUMBER, p_line_id IN NUMBER) IS
1297 select nvl(backflush_option, G_BFLUSH_OPTION_ALL) backflush_option
1298 from flm_exe_preferences
1299 where organization_id = p_org_id
1300 and line_id = p_line_id;
1301
1302 CURSOR org_bf_option(p_org_id IN NUMBER) IS
1303 select nvl(backflush_option,G_BFLUSH_OPTION_ALL) backflush_option
1304 from flm_exe_preferences
1305 where organization_id = p_org_id;
1306 l_bf_option NUMBER;
1307
1308 BEGIN
1309 for c_line_bf_option in line_bf_option(p_org_id, p_line_id) loop
1310 l_bf_option := c_line_bf_option.backflush_option;
1311 end loop;
1312 if(l_bf_option is null) then
1313 for c_org_bf_option in org_bf_option(p_org_id) loop
1314 l_bf_option := c_org_bf_option.backflush_option;
1315 end loop;
1316 end if;
1317
1318 if l_bf_option is null then
1319 l_bf_option := G_BFLUSH_OPTION_ALL;
1320 end if;
1321
1322 return l_bf_option;
1323
1324 EXCEPTION when others then
1325 l_bf_option := G_BFLUSH_OPTION_ALL;
1326 return l_bf_option;
1327
1328 END get_backflush_option;
1329
1330
1331 PROCEDURE get_backflush_comps(
1332 p_wip_ent_id in number default NULL,
1333 p_line_id in number default NULL,
1334 p_assyID in number,
1335 p_orgID in number,
1336 p_qty in number,
1337 p_altBomDesig in varchar2,
1338 p_altOption in number,
1339 p_bomRevDate in date default NULL,
1340 p_txnDate in date,
1341 p_projectID in number,
1342 p_taskID in number,
1343 p_toOpSeqNum in number,
1344 p_altRoutDesig in varchar2,
1345 x_compInfo in out nocopy system.wip_lot_serial_obj_t,
1346 x_returnStatus out nocopy varchar2) IS
1347
1348 CURSOR routing_seq_no_alt(p_org_id NUMBER, p_assembly_id NUMBER) IS
1349 select common_routing_sequence_id
1350 from bom_operational_routings
1351 where organization_id = p_org_id
1352 and assembly_item_id = p_assembly_id;
1353
1354 CURSOR routing_seq_alt(p_org_id NUMBER, p_assembly_id NUMBER, p_alt_desig VARCHAR2) IS
1355 select common_routing_sequence_id
1356 from bom_operational_routings
1357 where organization_id = p_org_id
1358 and assembly_item_id = p_assembly_id
1359 and alternate_routing_designator = p_alt_desig;
1360
1361
1362 l_compTbl system.wip_component_tbl_t;
1363 l_compLotSerTbl system.wip_lot_serial_obj_t;
1364 l_rtg_seq_id NUMBER;
1365 l_curItem system.wip_component_obj_t;
1366
1367 BEGIN
1368
1369 --if this is not scheduled completion, then no records can be merged
1370 if(p_wip_ent_id is null) then
1371 return;
1372 end if;
1373
1374 if(p_altRoutDesig is null) then
1375 for c_routing_seq_no_alt in routing_seq_no_alt(p_orgID, p_assyID) loop
1376 l_rtg_seq_id := c_routing_seq_no_alt.common_routing_sequence_id;
1377 end loop;
1378 else
1379 for c_routing_seq_alt in routing_seq_alt(p_orgID, p_assyID, p_altRoutDesig) loop
1380 l_rtg_seq_id := c_routing_seq_alt.common_routing_sequence_id;
1381 end loop;
1382 end if;
1383
1384 --if this item has no routing, then no records can be merged
1385 if(l_rtg_seq_id is null) then
1386 return;
1387 end if;
1388
1389 flm_execution_util.merge_backflush_comps(
1390 p_wip_ent_id => p_wip_ent_id,
1391 p_line_id => p_line_id,
1392 p_assyID => p_assyID,
1393 p_orgID => p_orgID,
1394 p_qty => p_qty,
1395 p_altBomDesig => p_altBomDesig,
1396 p_altOption => p_altOption,
1397 p_bomRevDate => p_bomRevDate,
1398 p_txnDate => p_txnDate,
1399 p_projectID => p_projectID,
1400 p_taskID => p_taskID,
1401 p_toOpSeqNum => p_toOpSeqNum,
1402 p_rtg_seq_id => l_rtg_seq_id,
1403 x_compTbl => x_compInfo,
1404 x_returnStatus => x_returnStatus);
1405
1406
1407 --Now we want to default the lot and serials if exist
1408 default_comp_lot_serials(
1409 p_wip_ent_id => p_wip_ent_id,
1410 p_line_id => p_line_id,
1411 p_assyID => p_assyID,
1412 p_orgID => p_orgID,
1413 p_qty => p_qty,
1414 p_altBomDesig => p_altBomDesig,
1415 p_altOption => p_altOption,
1416 p_bomRevDate => p_bomRevDate,
1417 p_txnDate => p_txnDate,
1418 p_projectID => p_projectID,
1419 p_taskID => p_taskID,
1420 p_toOpSeqNum => p_toOpSeqNum,
1421 p_altRoutDesig => p_altRoutDesig,
1422 x_compTbl => x_compInfo,
1423 x_returnStatus => x_returnStatus);
1424
1425 END get_backflush_comps;
1426
1427
1428
1429 PROCEDURE merge_backflush_comps(
1430 p_wip_ent_id in number default NULL,
1431 p_line_id in number default NULL,
1432 p_assyID in number,
1433 p_orgID in number,
1434 p_qty in number,
1435 p_altBomDesig in varchar2,
1436 p_altOption in number,
1437 p_bomRevDate in date default NULL,
1438 p_txnDate in date,
1439 p_projectID in number,
1440 p_taskID in number,
1441 p_toOpSeqNum in number,
1442 p_rtg_seq_id in number,
1443 x_compTbl in out nocopy system.wip_lot_serial_obj_t,
1444 x_returnStatus out nocopy varchar2) is
1445
1446 CURSOR sub_exist(p_org_id NUMBER, p_wip_ent_id NUMBER) IS
1447 select 1 as subs
1448 from dual
1449 where exists (
1450 select inventory_item_id
1451 from flm_exe_req_operations
1452 where organization_id = p_org_id
1453 and wip_entity_id = p_wip_ent_id
1454 and inventory_item_id <> -1);
1455
1456
1457 CURSOR op_events (p_rtg_seq_id NUMBER, p_lop_seq_id NUMBER) IS
1458 select operation_sequence_id, operation_seq_num
1459 from bom_operation_sequences
1460 where routing_sequence_id = p_rtg_seq_id
1461 and line_op_seq_id = p_lop_seq_id
1462 and operation_type = 1;
1463
1464 CURSOR recorded_comps (p_org_id NUMBER, p_wip_ent_id NUMBER) IS
1465 select fero.organization_id,
1466 fero.inventory_item_id,
1467 fero.operation_seq_num,
1468 fero.quantity_per_assembly,
1469 fero.supply_subinventory,
1470 fero.supply_locator_id,
1471 fero.component_sequence_id,
1472 nvl(fero.basis_type,WIP_CONSTANTS.ITEM_BASED_MTL) basis_type,
1473 msi.primary_uom_code,
1474 flm_util.get_key_flex_item(fero.inventory_item_id,fero.organization_id) inventory_item_name,
1475 msi.serial_number_control_code,
1476 msi.lot_control_code,
1477 msi.restrict_subinventories_code,
1478 msi.restrict_locators_code,
1479 msi.description,
1480 msi.revision_qty_control_code,
1481 msi.location_control_code
1482 from flm_exe_req_operations fero,
1483 mtl_system_items msi
1484 where fero.organization_id = p_org_id
1485 and fero.wip_entity_id = p_wip_ent_id
1486 and fero.inventory_item_id = msi.inventory_item_id
1487 and msi.organization_id = fero.organization_id
1488 order by msi.serial_number_control_code desc, msi.lot_control_code desc, operation_seq_num; --bug 13812141
1489
1490
1491 l_compTbl system.wip_component_tbl_t;
1492 l_bf_option NUMBER;
1493 l_sub_exist NUMBER := 2;
1494 l_op_seq_tbl operation_seq_tbl_type;
1495 l_count NUMBER := 1;
1496 v_idx NUMBER;
1497 l_comp_ev_seq_num NUMBER;
1498 l_rec_event_seq_num_tbl operation_seq_tbl_type;
1499 l_revision VARCHAR2(3);
1500 l_qty NUMBER;
1501 l_insertPhantom number := WIP_CONSTANTS.NO;
1502 l_msiSubinv varchar2(10);
1503 l_msiLocatorID number;
1504 l_wpSubinv varchar2(10);
1505 l_wpLocatorID number;
1506 l_success boolean;
1507 l_locatorID number;
1508
1509 BEGIN
1510
1511 l_compTbl := x_compTbl.items;
1512
1513 for c_sub_exist in sub_exist(p_orgId, p_wip_ent_id) loop
1514 l_sub_exist := c_sub_exist.subs;
1515 end loop;
1516
1517 get_eligible_ops(p_orgID,
1518 p_line_id,
1519 p_rtg_seq_id,
1520 p_wip_ent_id,
1521 l_op_seq_tbl);
1522
1523
1524 --first pass, remove all components from unneeded events
1525 l_bf_option := get_backflush_option(p_orgID, p_line_id);
1526 if(l_bf_option = G_BFLUSH_OPTION_ALL) then --All operations to be included in backflush
1527 null; --dont need to remove any component
1528 elsif(l_bf_option = G_BFLUSH_OPTION_ACT_PRI) then --Only Actual/Primary operations to be included in backflush
1529 v_idx := l_compTbl.FIRST;
1530 WHILE v_idx IS NOT NULL LOOP
1531 l_comp_ev_seq_num := l_compTbl(v_idx).operation_seq_num;
1532 IF NOT l_op_seq_tbl.EXISTS(l_comp_ev_seq_num) then
1533 l_compTbl.delete(v_idx);
1534 END IF;
1535 v_idx := l_compTbl.NEXT(v_idx);
1536 END LOOP;
1537 end if;
1538
1539
1540 --remove the phantom comps if bom param for use_phantom_routing is not set to yes
1541 l_insertPhantom := wip_globals.use_phantom_routings(p_orgID);
1542 v_idx := l_compTbl.FIRST;
1543 WHILE v_idx IS NOT NULL LOOP
1544 if(nvl(l_compTbl(v_idx).wip_supply_type, 1) = WIP_CONSTANTS.PHANTOM) then
1545 if(l_insertPhantom <> WIP_CONSTANTS.YES) then
1546 l_compTbl.delete(v_idx);
1547 elsif(l_insertPhantom = WIP_CONSTANTS.YES) then
1548 if(l_compTbl(v_idx).operation_seq_num > 0) then
1549 l_compTbl(v_idx).operation_seq_num := -1*abs(l_compTbl(v_idx).operation_seq_num);
1550 end if;
1551 end if;
1552 end if;
1553 v_idx := l_compTbl.NEXT(v_idx);
1554 END LOOP;
1555
1556
1557 if((l_sub_exist is null) or (l_sub_exist = 2)) then --no substitutions
1558 x_compTbl.items := l_compTbl;
1559 return;
1560 end if;
1561
1562
1563 --at this point we need to merge recorded components
1564 --first remove the components of operation that are recorded
1565 get_recorded_event_seq_num(p_orgID,
1566 p_wip_ent_id,
1567 l_rec_event_seq_num_tbl);
1568 v_idx := l_compTbl.FIRST;
1569 WHILE v_idx IS NOT NULL LOOP
1570 l_comp_ev_seq_num := l_compTbl(v_idx).operation_seq_num;
1571 IF l_rec_event_seq_num_tbl.EXISTS(l_comp_ev_seq_num) then
1572 l_compTbl.delete(v_idx);
1573 END IF;
1574 v_idx := l_compTbl.NEXT(v_idx);
1575 END LOOP;
1576
1577 --then add the recorded components
1578 --l_compTbl.extend(2); --todo, change with component count
1579
1580 x_compTbl.items := l_compTbl;
1581
1582 v_idx := nvl(l_compTbl.LAST,0);
1583
1584 for c_recorded_comps in recorded_comps(p_orgID, p_wip_ent_id) loop
1585 v_idx := v_idx +1;
1586 l_compTbl.extend;
1587 if(c_recorded_comps.revision_qty_control_code = wip_constants.revision_controlled) then
1588 bom_revisions.get_revision(examine_type => 'ALL',
1589 org_id => p_orgID,
1590 item_id => c_recorded_comps.inventory_item_id,
1591 rev_date => p_txnDate,
1592 itm_rev => l_revision);
1593 else
1594 l_revision := null;
1595 end if;
1596 if(c_recorded_comps.basis_type = WIP_CONSTANTS.ITEM_BASED_MTL) then
1597 l_qty := nvl(c_recorded_comps.quantity_per_assembly * p_qty,0);
1598 else
1599 l_qty := nvl(c_recorded_comps.quantity_per_assembly,0);
1600 end if;
1601
1602 if ( c_recorded_comps.supply_subinventory is null ) then
1603 select msi.wip_supply_subinventory,
1604 msi.wip_supply_locator_id,
1605 wp.default_pull_supply_subinv,
1606 wp.default_pull_supply_locator_id
1607 into l_msiSubinv,
1608 l_msiLocatorID,
1609 l_wpSubinv,
1610 l_wpLocatorID
1611 from mtl_system_items msi,
1612 wip_parameters wp
1613 where msi.organization_id = wp.organization_id
1614 and msi.organization_id = p_orgID
1615 and msi.inventory_item_id = c_recorded_comps.inventory_item_id;
1616 if ( l_msiSubinv is not null ) then
1617 c_recorded_comps.supply_subinventory := l_msiSubinv;
1618 l_locatorID := l_msiLocatorID;
1619 else
1620 c_recorded_comps.supply_subinventory := l_wpSubinv;
1621 l_locatorID := l_wpLocatorID;
1622 end if;
1623 else
1624 if c_recorded_comps.supply_locator_id is not null then
1625 l_locatorID := c_recorded_comps.supply_locator_id;
1626 else
1627 l_locatorID := null;
1628 end if;
1629 end if;
1630
1631 if(l_locatorID is not null) then
1632 l_success := pjm_project_locator.get_component_projectSupply(
1633 p_organization_id => p_orgID,
1634 p_project_id => p_projectID,
1635 p_task_id => p_taskID,
1636 p_wip_entity_id => null,--unused
1637 p_supply_sub => c_recorded_comps.supply_subinventory,
1638 p_supply_loc_id => l_locatorID,
1639 p_item_id => c_recorded_comps.inventory_item_id,
1640 p_org_loc_control => null); --unused
1641 c_recorded_comps.supply_locator_id := l_locatorID;
1642 end if;
1643
1644 if(c_recorded_comps.quantity_per_assembly <> -9999) then --bug 5181888, add this clause to remove the deleted comps in final merge
1645 x_compTbl.addItem
1646 (p_opSeqNum => c_recorded_comps.operation_seq_num,
1647 p_itemID => c_recorded_comps.inventory_item_id,
1648 p_itemName => c_recorded_comps.inventory_item_name ,
1649 p_priQty => l_qty,
1650 p_priUomCode => c_recorded_comps.primary_uom_code,
1651 p_supplySubinv => c_recorded_comps.supply_subinventory,
1652 p_supplyLocID => c_recorded_comps.supply_locator_id,
1653 p_wipSupplyType => wip_constants.assy_pull,
1654 p_txnActionID => wip_constants.isscomp_action,
1655 p_mtlTxnsEnabledFlag => null,
1656 p_serialControlCode => c_recorded_comps.serial_number_control_code,
1657 p_lotControlCode => c_recorded_comps.lot_control_code,
1658 p_revision => l_revision,
1659 p_departmentID => 1,
1660 p_restrictSubsCode => c_recorded_comps.restrict_subinventories_code,
1661 p_restrictLocsCode =>c_recorded_comps.restrict_locators_code,
1662 p_projectID => p_projectID,
1663 p_taskID => p_taskID,
1664 p_componentSeqID => c_recorded_comps.component_sequence_id,
1665 p_cmpTxnID => null,
1666 p_itemDescription => c_recorded_comps.description,
1667 p_locatorName => flm_util.get_key_flex_location(c_recorded_comps.supply_locator_id,p_orgID),
1668 p_revisionContolCode => c_recorded_comps.revision_qty_control_code,
1669 p_locationControlCode => c_recorded_comps.location_control_code,
1670 p_locatorProjectID => null,
1671 p_locatorTaskID => null);
1672 end if;
1673 end loop;
1674
1675 END merge_backflush_comps;
1676
1677
1678
1679
1680 PROCEDURE default_comp_lot_serials(
1681 p_wip_ent_id in number default NULL,
1682 p_line_id in number default NULL,
1683 p_assyID in number,
1684 p_orgID in number,
1685 p_qty in number,
1686 p_altBomDesig in varchar2,
1687 p_altOption in number,
1688 p_bomRevDate in date default NULL,
1689 p_txnDate in date,
1690 p_projectID in number,
1691 p_taskID in number,
1692 p_toOpSeqNum in number,
1693 p_altRoutDesig in varchar2,
1694 x_compTbl in out nocopy system.wip_lot_serial_obj_t,
1695 x_returnStatus out nocopy varchar2) IS
1696 l_curItem system.wip_component_obj_t;
1697
1698 cursor comp_lot (p_wip_ent_id NUMBER,
1699 p_org_id NUMBER,
1700 p_op_seq_num NUMBER,
1701 p_inv_item_id NUMBER) IS
1702 select lot_number, lot_quantity, parent_lot_number, creation_date
1703 from flm_exe_lot_numbers
1704 where organization_id = p_org_id
1705 and wip_entity_id = p_wip_ent_id
1706 and inventory_item_id = p_inv_item_id
1707 and operation_seq_num = p_op_seq_num
1708 order by creation_date, lot_number;
1709
1710 cursor comp_serial (p_wip_ent_id NUMBER,
1711 p_org_id NUMBER,
1712 p_op_seq_num NUMBER,
1713 p_inv_item_id NUMBER) IS
1714 select fm_serial_number, to_serial_number, serial_quantity, lot_number, parent_serial_number, creation_date
1715 from flm_exe_serial_numbers
1716 where organization_id = p_org_id
1717 and wip_entity_id = p_wip_ent_id
1718 and inventory_item_id = p_inv_item_id
1719 and operation_seq_num = p_op_seq_num
1720 order by creation_date, fm_serial_number;
1721
1722 cursor comp_lot_serial (p_wip_ent_id NUMBER,
1723 p_org_id NUMBER,
1724 p_op_seq_num NUMBER,
1725 p_inv_item_id NUMBER,
1726 p_comp_lot_num VARCHAR2) IS
1727 select fm_serial_number, to_serial_number, serial_quantity, lot_number, parent_serial_number, creation_date
1728 from flm_exe_serial_numbers
1729 where organization_id = p_org_id
1730 and wip_entity_id = p_wip_ent_id
1731 and inventory_item_id = p_inv_item_id
1732 and operation_seq_num = p_op_seq_num
1733 and lot_number = p_comp_lot_num
1734 order by creation_date, fm_serial_number;
1735
1736 compReqQty NUMBER := 0;
1737 compRemainQty NUMBER :=0;
1738 compAvailToTxnLotQty NUMBER := 0;
1739 compAvailToTxnSerQty NUMBER := 0;
1740
1741 BEGIN
1742 x_compTbl.reset;
1743 loop
1744 if(x_compTbl.getCurrentItem(l_curItem)) then
1745
1746 --try to default lot numbers for component
1747 if(l_curItem.lot_control_code = 2) then
1748
1749 compReqQty := 0;
1750 compRemainQty := 0;
1751 compReqQty := l_curItem.primary_quantity;
1752 compRemainQty := compReqQty;
1753
1754 for c_comp_lot in comp_lot(p_wip_ent_id, p_orgID,
1755 l_curItem.operation_seq_num, l_curItem.inventory_item_id) LOOP
1756 compAvailToTxnLotQty := c_comp_lot.lot_quantity;
1757 x_compTbl.addLot(p_lotNumber => c_comp_lot.lot_number,
1758 p_priQty => least(compRemainQty, compAvailToTxnLotQty),
1759 p_attributes => null);
1760 compRemainQty := compRemainQty - least(compRemainQty, compAvailToTxnLotQty);
1761 if(l_curItem.serial_number_control_code in (2,5,6)) then --if item is under both lot and serial control, bug 5572880
1762 for c_comp_ls in comp_lot_serial(p_wip_ent_id, p_orgID,
1763 l_curItem.operation_seq_num, l_curItem.inventory_item_id,
1764 c_comp_lot.lot_number) LOOP
1765 x_compTbl.addLotSerial
1766 (p_fmSerial => c_comp_ls.fm_serial_number,
1767 p_toSerial => c_comp_ls.to_serial_number,
1768 p_parentSerial => c_comp_ls.parent_serial_number,
1769 p_priQty => c_comp_ls.serial_quantity,
1770 p_attributes => null);
1771 END LOOP; -- component serial loop
1772 end if;
1773
1774 if(compRemainQty = 0) then
1775 exit;
1776 end if;
1777 END LOOP; -- component lot loop
1778 end if;
1779
1780 --only serial control
1781 --try to default serial number for component for predefined,at receipt, so issue
1782 if((l_curItem.lot_control_code <> 2) AND
1783 (l_curItem.serial_number_control_code in ( 2,5,6))) then --bug 5572880
1784 compReqQty := 0;
1785 compRemainQty := 0;
1786 compReqQty := l_curItem.primary_quantity;
1787 compRemainQty := compReqQty;
1788
1789 for c_comp_serial in comp_serial(p_wip_ent_id,
1790 p_orgID,
1791 l_curItem.operation_seq_num,
1792 l_curItem.inventory_item_id) LOOP
1793 compAvailToTxnSerQty := c_comp_serial.serial_quantity;
1794 x_compTbl.addSerial(p_fmSerial => c_comp_serial.fm_serial_number,
1795 p_toSerial => c_comp_serial.to_serial_number,
1796 p_parentSerial => c_comp_serial.parent_serial_number,
1797 p_priQty => c_comp_serial.serial_quantity,
1798 p_attributes => null);
1799 compRemainQty := compRemainQty - least(compRemainQty, compAvailToTxnSerQty);
1800 if(compRemainQty = 0) then
1801 exit;
1802 end if;
1803 END LOOP;
1804 end if;
1805 end if;
1806 exit when not x_compTbl.setNextItem;
1807 end loop;
1808
1809 END default_comp_lot_serials;
1810
1811
1812 FUNCTION scheduleRecordedDetailsExist(orgId Number, schNum Varchar2)
1813 return VARCHAR2 IS
1814 l_wip_ent_id NUMBER := 0;
1815 BEGIN
1816
1817 select wip_entity_id
1818 into l_wip_ent_id
1819 from wip_flow_schedules
1820 where organization_id = orgId
1821 and schedule_number = schNum;
1822
1823 return (scheduleRecordedDetailsExist(orgId, l_wip_ent_id));
1824
1825 END scheduleRecordedDetailsExist;
1826
1827
1828 FUNCTION scheduleRecordedDetailsExist(orgId Number, wipEntId Number)
1829 return VARCHAR2 IS
1830 CURSOR recordedOperation(p_orgId Number, p_wipEntId Number ) IS
1831 select count(wip_entity_id) count
1832 from flm_exe_operations
1833 where organization_id = p_orgId
1834 and wip_entity_id = p_wipEntId;
1835
1836 CURSOR recordedDetails(p_orgId Number, p_wipEntId Number) IS
1837 select count(wip_entity_id) count
1838 from flm_exe_req_operations
1839 where organization_id = p_orgId
1840 and wip_entity_id = p_wipEntId;
1841 l_count NUMBER := 0;
1842 BEGIN
1843
1844 for c_recordedOperation in recordedOperation(orgId, wipEntId) loop
1845 l_count := c_recordedOperation.count;
1846 end loop;
1847 if(l_count > 0) then
1848 return 'Y';
1849 else
1850 for c_recordedDetails in recordedDetails(orgId, wipEntId) loop
1851 l_count := c_recordedDetails.count;
1852 end loop;
1853 if(l_count > 0) then
1854 return 'Y';
1855 end if;
1856 end if;
1857 return 'N';
1858
1859 exception when others then
1860 return 'N';
1861
1862
1863 END scheduleRecordedDetailsExist;
1864
1865
1866 FUNCTION kanban_card_activity_exist(p_wip_entity_id IN NUMBER)
1867 RETURN NUMBER IS
1868 l_exists NUMBER := 0;
1869 CURSOR kanban_card_csr(l_wip_entity_id IN NUMBER) IS
1870 select 1 as kanban_exists
1871 from dual
1872 where exists
1873 (select kanban_card_id
1874 from mtl_kanban_card_activity
1875 where source_wip_entity_id = l_wip_entity_id
1876 );
1877 BEGIN
1878 for c_kanban_card_csr in kanban_card_csr(p_wip_entity_id) loop
1879 l_exists := c_kanban_card_csr.kanban_exists;
1880 end loop;
1881
1882 if(l_exists = 1) then
1883 return 1;
1884 else
1885 return 2;
1886 end if;
1887
1888 EXCEPTION
1889 when others then
1890 return 2;
1891
1892 END Kanban_card_activity_exist;
1893
1894
1895 PROCEDURE exp_ser_single_op(p_org_id IN NUMBER, p_wip_entity_id NUMBER,
1896 p_operation_seq_num NUMBER) IS
1897
1898 CURSOR op_items(p_org_id IN NUMBER, p_wip_entity_id NUMBER,
1899 p_operation_seq_num NUMBER) IS
1900 select organization_id, wip_entity_id, operation_seq_num, inventory_item_id
1901 from flm_exe_req_operations
1902 where organization_id = p_org_id
1903 and wip_entity_id = p_wip_entity_id
1904 and operation_seq_num = p_operation_seq_num;
1905
1906 BEGIN
1907 for c_op_items in op_items(p_org_id, p_wip_entity_id, p_operation_seq_num) LOOP
1908 exp_ser_single_item(c_op_items.organization_id,
1909 c_op_items.wip_entity_id,
1910 c_op_items.operation_seq_num,
1911 c_op_items.inventory_item_id);
1912 END LOOP;
1913 END exp_ser_single_op;
1914
1915
1916 PROCEDURE exp_ser_single_item(p_org_id IN NUMBER, p_wip_entity_id NUMBER,
1917 p_operation_seq_num NUMBER, p_inventory_item_id NUMBER) IS
1918
1919 CURSOR item_serials(p_org_id IN NUMBER, p_wip_entity_id NUMBER,
1920 p_operation_seq_num NUMBER, p_inventory_item_id NUMBER) IS
1921 select organization_id, wip_entity_id, operation_seq_num, inventory_item_id,
1922 fm_serial_number, to_serial_number, parent_serial_number, lot_number
1923 from flm_exe_serial_numbers fesn
1924 where organization_id = p_org_id
1925 and wip_entity_id = p_wip_entity_id
1926 and operation_seq_num = p_operation_seq_num
1927 and inventory_item_id = p_inventory_item_id;
1928
1929 l_fm_serial VARCHAR2(30);
1930 l_to_serial VARCHAR2(30);
1931
1932 BEGIN
1933 for c_item_serial in item_serials(p_org_id, p_wip_entity_id, p_operation_seq_num, p_inventory_item_id) LOOP
1934 if(c_item_serial.fm_serial_number = c_item_serial.to_serial_number) then
1935 null; --no need to explode
1936 else
1937 exp_ser_single_range(c_item_serial.organization_id,
1938 c_item_serial.wip_entity_id,
1939 c_item_serial.operation_seq_num,
1940 c_item_serial.inventory_item_id,
1941 c_item_serial.fm_serial_number,
1942 c_item_serial.to_serial_number,
1943 c_item_serial.parent_serial_number,
1944 c_item_serial.lot_number);
1945 end if;
1946 END LOOP;
1947 END exp_ser_single_item;
1948
1949
1950
1951 PROCEDURE exp_ser_single_range(p_org_id IN NUMBER, p_wip_entity_id NUMBER,
1952 p_operation_seq_num NUMBER, p_inventory_item_id NUMBER, p_fm_serial VARCHAR2,
1953 p_to_serial VARCHAR2, p_parent_serial_number VARCHAR2, p_lot_number VARCHAR2) IS
1954
1955 l_from_ser_number NUMBER;
1956 l_to_ser_number NUMBER;
1957 l_range_numbers NUMBER;
1958 l_cur_ser_number NUMBER;
1959 l_cur_serial_number VARCHAR2(30);
1960 l_temp_prefix VARCHAR2(30);
1961 l_user_id NUMBER;
1962 l_login_id NUMBER;
1963 BEGIN
1964 l_user_id := FND_GLOBAL.user_id;
1965 l_login_id := FND_GLOBAL.login_id;
1966
1967 --get the number part of from serial
1968 inv_validate.number_from_sequence(p_fm_serial, l_temp_prefix, l_from_ser_number);
1969 -- get the number part of the to serial
1970 inv_validate.number_from_sequence(p_to_serial, l_temp_prefix, l_to_ser_number);
1971 -- total number of serials inserted
1972 l_range_numbers := l_to_ser_number - l_from_ser_number + 1;
1973
1974 FOR i IN 1 .. l_range_numbers LOOP
1975 l_cur_ser_number := l_from_ser_number + i - 1;
1976
1977 -- concatenate the serial number to be inserted
1978 l_cur_serial_number := SUBSTR(p_fm_serial, 1, LENGTH(p_fm_serial) - LENGTH(l_cur_ser_number))
1979 || l_cur_ser_number;
1980 insert into flm_exe_serial_numbers(
1981 requirement_serial_id,
1982 organization_id,
1983 wip_entity_id,
1984 operation_seq_num,
1985 inventory_item_id,
1986 fm_serial_number,
1987 to_serial_number,
1988 parent_serial_number,
1989 lot_number,
1990 serial_quantity,
1991 object_version_number,
1992 created_by,
1993 creation_date,
1994 last_update_login,
1995 last_update_date,
1996 last_updated_by)
1997 values
1998 (
1999 flm_exe_serial_numbers_s.nextval,
2000 p_org_id,
2001 p_wip_entity_id,
2002 p_operation_seq_num,
2003 p_inventory_item_id,
2004 l_cur_serial_number,
2005 l_cur_serial_number,
2006 p_parent_serial_number,
2007 p_lot_number,
2008 1,
2009 1,
2010 l_user_id,
2011 sysdate,
2012 l_login_id,
2013 sysdate,
2014 l_user_id
2015 );
2016 END LOOP;
2017 --now delete the original range row
2018 delete from flm_exe_serial_numbers
2019 where organization_id = p_org_id
2020 and wip_entity_id = p_wip_entity_id
2021 and operation_seq_num = p_operation_seq_num
2022 and inventory_item_id = p_inventory_item_id
2023 and fm_serial_number = p_fm_serial
2024 and to_serial_number = p_to_serial;
2025
2026 END exp_ser_single_range;
2027
2028
2029 FUNCTION get_single_assy_ser(p_org_id IN NUMBER, p_inv_item_id IN NUMBER) RETURN VARCHAR2 IS
2030 CURSOR assy_serials IS
2031 select serial_number
2032 from mtl_serial_numbers msn,
2033 mtl_transaction_types mtt
2034 where (msn.group_mark_id is null or msn.group_mark_id = -1)
2035 and msn.current_status in (1, 4 )
2036 and msn.inventory_item_id = p_inv_item_id
2037 and msn.current_organization_id = p_org_id
2038 and mtt.transaction_type_id = 44
2039 and inv_material_status_grp.is_status_applicable(
2040 null,
2041 null,
2042 mtt.transaction_type_id
2043 , NULL
2044 , null
2045 , msn.current_organization_id
2046 , msn.inventory_item_id
2047 , NULL
2048 , NULL
2049 , NULL
2050 , serial_number
2051 , 'S') = 'Y';
2052 ser_cnt NUMBER := 0;
2053 ser_num VARCHAR2(30);
2054 assy_ser VARCHAR2(30) := null;
2055 BEGIN
2056 for c_assy_ser in assy_serials loop
2057 ser_num := c_assy_ser.serial_number;
2058 ser_cnt := ser_cnt+1;
2059 end loop;
2060
2061 if ser_cnt = 1 then
2062 assy_ser := ser_num;
2063 end if;
2064
2065 return assy_ser;
2066
2067 END get_single_assy_ser;
2068
2069
2070 FUNCTION get_single_assy_lot(p_org_id IN NUMBER, p_inv_item_id IN NUMBER) RETURN VARCHAR2 IS
2071 CURSOR assy_lots IS
2072 select lot_number
2073 from mtl_lot_numbers mln,
2074 mtl_transaction_types mtt
2075 where mln.inventory_item_id = p_inv_item_id
2076 and mln.organization_id = p_org_id
2077 and mtt.transaction_type_id = 44
2078 and inv_material_status_grp.is_status_applicable(
2079 null
2080 , null
2081 , mtt.transaction_type_id
2082 , null
2083 , NULL
2084 , mln.organization_id
2085 , mln.inventory_item_id
2086 , NULL
2087 , NULL
2088 , lot_number
2089 , NULL
2090 , 'O') = 'Y'
2091 and nvl(disable_flag,2)=2;
2092 lot_cnt NUMBER := 0;
2093 lot_num VARCHAR2(80);
2094 assy_lot VARCHAR2(80) := null;
2095 BEGIN
2096 for c_assy_lot in assy_lots loop
2097 lot_num := c_assy_lot.lot_number;
2098 lot_cnt := lot_cnt+1;
2099 end loop;
2100
2101 if lot_cnt = 1 then
2102 assy_lot := lot_num;
2103 end if;
2104
2105 return assy_lot;
2106
2107 END get_single_assy_lot;
2108
2109
2110 FUNCTION get_txn_bfcomp_cnt(txn_intf_id NUMBER)
2111 RETURN NUMBER IS
2112 l_cnt NUMBER := 0;
2113 BEGIN
2114 if(txn_intf_id is not null) then
2115 select count(transaction_interface_id)
2116 into l_cnt
2117 from mtl_transactions_interface
2118 where parent_id is not null
2119 and parent_id = txn_intf_id;
2120 end if;
2121 return l_cnt;
2122 END get_txn_bfcomp_cnt;
2123
2124
2125 FUNCTION get_ser_range_cnt(p_fm_serial VARCHAR2, p_to_serial VARCHAR2)
2126 RETURN NUMBER IS
2127 l_cnt NUMBER := 0;
2128 l_from_ser_number NUMBER;
2129 l_to_ser_number NUMBER;
2130 l_temp_prefix VARCHAR2(30);
2131 BEGIN
2132
2133 --get the number part of from serial
2134 inv_validate.number_from_sequence(p_fm_serial, l_temp_prefix, l_from_ser_number);
2135 -- get the number part of the to serial
2136 inv_validate.number_from_sequence(p_to_serial, l_temp_prefix, l_to_ser_number);
2137 -- total number of serials inserted
2138 l_cnt := l_to_ser_number - l_from_ser_number + 1;
2139
2140 return l_cnt;
2141 Exception
2142 when others then
2143 return -1;
2144 END get_ser_range_cnt;
2145
2146
2147 FUNCTION non_txncomp_exist(p_wip_entity_id IN NUMBER, p_org_id IN NUMBER)
2148 RETURN NUMBER IS
2149
2150 l_bill_seq_id NUMBER;
2151 l_bom_rev_date DATE;
2152 l_nontxn_comp_cnt NUMBER;
2153 l_ret_val NUMBER := 2;
2154
2155 CURSOR bill_seq IS
2156 select bbom.bill_sequence_id,
2157 wfs.bom_revision_date
2158 from bom_bill_of_materials bbom,
2159 wip_flow_schedules wfs
2160 where wfs.wip_entity_id = p_wip_entity_id
2161 and wfs.organization_id = p_org_id
2162 and bbom.assembly_item_id = wfs.primary_item_id
2163 and bbom.organization_id = wfs.organization_id
2164 and nvl(bbom.alternate_bom_designator, 'NULL') = nvl(wfs.alternate_bom_designator, 'NULL');
2165
2166 CURSOR nontxn_comp_cnt IS
2167 select count(component_item_id) comp_cnt
2168 from bom_inventory_components bic,
2169 mtl_system_items msi
2170 where bill_sequence_id = l_bill_seq_id
2171 and msi.inventory_item_id = bic.component_item_id
2172 and bic.effectivity_date < nvl(l_bom_rev_date,sysdate)
2173 and ((bic.disable_date is null) or
2174 (bic.disable_date is not null and
2175 bic.disable_date > nvl(l_bom_rev_date, sysdate)))
2176 and msi.organization_id = p_org_id
2177 and nvl(msi.mtl_transactions_enabled_flag,'N') = 'N'
2178 and nvl(bic.wip_supply_type,msi.wip_supply_type) <> 6;
2179
2180 BEGIN
2181
2182 for c_bill_seq in bill_seq LOOP
2183 l_bill_seq_id := c_bill_seq.bill_sequence_id;
2184 l_bom_rev_date := c_bill_seq.bom_revision_date;
2185 END LOOP;
2186
2187 if(l_bill_seq_id is NULL) then
2188 l_ret_val := 2;
2189 return l_ret_val;
2190 end if;
2191
2192 for c_nontxn_comp_cnt in nontxn_comp_cnt LOOP
2193 l_nontxn_comp_cnt := c_nontxn_comp_cnt.comp_cnt;
2194 if(l_nontxn_comp_cnt > 0) then
2195 l_ret_val := 1;
2196 else
2197 l_ret_val := 2;
2198 end if;
2199 END LOOP;
2200
2201 return l_ret_val;
2202
2203 END non_txncomp_exist;
2204
2205 /*Added for bugfix 6152984 */
2206 /****************************************************
2207 * This function finds out if any event is *
2208 * attached to the operation seq based on passed *
2209 * std op in the routing for this schedule *
2210 * return_status = 'Y' for One or more Event Exist *
2211 * return_status = 'N' for No event exist *
2212 ***************************************************/
2213 function event_exist(p_org_id NUMBER,
2214 p_wip_entity_id NUMBER,
2215 p_std_op_id NUMBER) return VARCHAR2 IS
2216
2217 l_event_exist VARCHAR2(1) := 'N';
2218 l_common_routing_seq_id NUMBER;
2219 l_operation_seq_id NUMBER;
2220 l_rtg_rev_date DATE;
2221
2222 -- Cursor to find the sequence id of the operation in the routing
2223 -- corresponding to the schedule that references the specified
2224 -- standard operation.
2225 CURSOR op_seq_csr IS
2226 select bos.operation_sequence_id, bor.common_routing_sequence_id,
2227 nvl(wfs.routing_revision_date, wfs.scheduled_completion_date)
2228 as routing_revision_date
2229 from bom_operation_sequences bos,
2230 wip_flow_schedules wfs,
2231 bom_operational_routings bor
2232 where wfs.wip_entity_id = p_wip_entity_id
2233 and wfs.organization_id = p_org_id
2234 and bor.organization_id = p_org_id
2235 and bor.assembly_item_id = wfs.primary_item_id
2236 and nvl(bor.alternate_routing_designator, '########') =
2237 nvl(wfs.alternate_routing_designator, '########')
2238 and bor.common_routing_sequence_id = bos.routing_sequence_id
2239 and bos.operation_type = 3 -- line operation
2240 and bos.standard_operation_id = p_std_op_id;
2241
2242
2243 CURSOR event_exist_csr IS
2244 select 'Y' as event_exist
2245 from dual
2246 where exists (
2247 select bos.operation_sequence_id
2248 from bom_operation_sequences bos
2249 where bos.routing_sequence_id = l_common_routing_seq_id
2250 and bos.line_op_seq_id = l_operation_seq_id
2251 and bos.operation_type = 1
2252 and bos.effectivity_date <= l_rtg_rev_date
2253 and nvl(bos.disable_date,l_rtg_rev_date+1) > l_rtg_rev_date );
2254
2255
2256 BEGIN
2257
2258 for c_op_seq in op_seq_csr loop
2259 l_common_routing_seq_id := c_op_seq.common_routing_sequence_id;
2260 l_operation_seq_id := c_op_seq.operation_sequence_id;
2261 l_rtg_rev_date := c_op_seq.routing_revision_date;
2262 end loop;
2263
2264 if((l_common_routing_seq_id is not null) AND
2265 (l_operation_seq_id is not null))
2266 then
2267 for c_event_exist in event_exist_csr loop
2268 l_event_exist := c_event_exist.event_exist;
2269 end loop;
2270 end if;
2271 return l_event_exist;
2272
2273 END event_exist;
2274
2275 END flm_execution_util;