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