1 PACKAGE BODY BOM_CALC_TPCT as
2 /* $Header: bomtpctb.pls 115.5 2002/04/16 15:13:32 pkm ship $ */
3
4 function calc_tpct (
5 p_routing_sequence_id in number,
6 p_operation_type in varchar2
7 ) return number is
8 -- Declare exceptions
9 loop_found exception;
10
11 pragma exception_init(loop_found, -1436);
12
13 -- Declare variables
14 v_ttc number := 0; -- system calculated total time
15 v_tpct number := 0; -- total product cycle time
16 v_max_tpct number := 0; -- max total product cycle time
17
18 -- Select all the operations in the routing that do not have a 'from'
19 -- operation. These are valid starting points for multiple paths (i.e.
20 -- it includes feeder lines)
21 cursor start_ops_cur (cv_routing_sequence_id number,
22 cv_operation_type varchar2) is
23 select bos.operation_sequence_id start_operation_sequence_id
24 from bom_operation_sequences bos
25 where bos.routing_sequence_id = cv_routing_sequence_id
26 and bos.operation_type = cv_operation_type
27 minus
28 select bon.to_op_seq_id start_operation_sequence_id
29 from bom_operation_networks bon, bom_operation_sequences bos
30 where bon.to_op_seq_id = bos.operation_sequence_id
31 and bos.routing_sequence_id = cv_routing_sequence_id
32 and bos.operation_type = cv_operation_type
33 and nvl(bon.transition_type, 0) <> 3;
34
35 start_ops_rec start_ops_cur%rowtype;
36
37 -- For each of the starting points, traverse the network to select all the
38 -- 'to' operations until the end
39 cursor network_cur (cv_start_operation_sequence_id number) is
40 select bon.to_op_seq_id
41 from bom_operation_networks bon
42 connect by prior to_op_seq_id = from_op_seq_id
43 and
44 nvl(bon.transition_type, 0) not in (2, 3)
45 start with from_op_seq_id = cv_start_operation_sequence_id
46 and
47 nvl(bon.transition_type, 0) not in (2, 3);
48
49 network_rec network_cur%rowtype;
50
51 begin
52 -- Fetch all the starting points
53 for start_ops_rec in start_ops_cur (p_routing_sequence_id,
54 p_operation_type) loop
55 -- Select total_time_calc for the starting operation
56 select nvl(total_time_calc, 0)
57 into v_tpct
58 from bom_operation_sequences
59 where operation_sequence_id = start_ops_rec.start_operation_sequence_id;
60
61 -- Fetch all the to operations from the starting operation
62 for network_rec in network_cur
63 (start_ops_rec.start_operation_sequence_id) loop
64 -- Select total_time_calc for the to_operation
65 select nvl(total_time_calc, 0)
66 into v_ttc
67 from bom_operation_sequences
68 where operation_sequence_id = network_rec.to_op_seq_id;
69
70 -- Set the total product cycle time
71 v_tpct := v_tpct + v_ttc;
72 end loop; -- End loop for network_cur
73
74 -- Save the max total product cycle time
75 if v_tpct > v_max_tpct then
76 v_max_tpct := v_tpct;
77 end if;
78
79 -- Reset total product cycle time
80 v_tpct := 0;
81 end loop; -- End loop for start_ops_cur
82
83 v_max_tpct := Round(v_max_tpct,10);
84 -- Return the total product cycle time
85 return v_max_tpct;
86 end calc_tpct;
87
88 Procedure calculate_tpct (
89 p_routing_sequence_id in number,
90 p_operation_type in varchar2 ) IS
91
92 total_cycle_time NUMBER:= 0;
93 BEGIN
94 total_cycle_time := calc_tpct(p_routing_sequence_id,p_operation_type);
95 update bom_operational_routings
96 set total_product_cycle_time = total_cycle_time
97 where common_routing_sequence_id = p_routing_sequence_id;
98 END calculate_tpct;
99
100
101
102 END BOM_CALC_TPCT;
[Home] [Help]