[Home] [Help]
PACKAGE BODY: APPS.WIP_LEADTIME_TEMP_PKG
Source
1 PACKAGE BODY WIP_LEADTIME_TEMP_PKG AS
2 /* $Header: wipltcab.pls 120.0.12000000.2 2007/02/20 23:58:38 vjambhek ship $ */
3
4 function wip_populate_leadtime_temp ( p_routing_sequence_id IN number,
5 p_debug_level IN number) return number
6 IS
7
8 cursor network_csr (p_seq_num number)
9 is
10 select to_seq_num
11 from bom_operation_networks_v
12 where routing_sequence_id = p_routing_sequence_id
13 and from_seq_num = p_seq_num
14 and transition_type = 1 ;
15
16 cursor routing_csr
17 is
18 select distinct operation_seq_num
19 from bom_operation_sequences
20 where routing_sequence_id = p_routing_sequence_id
21 and implementation_date is not null ;
22
23
24 l_from_op number ;
25 l_next_op number ;
26 l_order number := 1 ;
27 network_found number := 1 ;
28
29 procedure insert_temp (p_routing_seq_id number,
30 p_from_op number,
31 p_order number)
32 is
33 begin
34 insert into wip_leadtime_temp ( routing_sequence_id,
35 operation_seq_num,
36 network_order
37 )
38 values ( p_routing_seq_id,
39 p_from_op,
40 p_order
41 );
42
43 end insert_temp ;
44
45 begin
46
47 -- Find Root node and populate temp ;
48
49 begin
50
51 -- Fixed bug #5691032 - FP for bug#5572730.
52 -- Added distinct in the select clause below.
53 select distinct from_seq_num
54 into l_from_op
55 from bom_operation_networks_v a
56 where routing_sequence_id = p_routing_sequence_id
57 and not exists (select 1
58 from bom_operation_networks_v b
59 where a.routing_sequence_id = b.routing_sequence_id
60 and b.to_seq_num = a.from_seq_num
61 and b.transition_type = 1
62 )
63 and a.transition_type = 1 ;
64
65 if p_debug_level = 1 then
66 fnd_file.put_line (fnd_file.log, 'Inserting into wip_leadtime_temp for Network routing ' || p_routing_sequence_id );
67 fnd_file.put_line(fnd_file.log, 'Operation Seq.Num ' || l_from_op || ' Order ' || l_order) ;
68 end if ;
69
70 insert_temp(p_routing_sequence_id, l_from_op, l_order) ;
71
72 exception
73 when no_data_found then
74 network_found := 0 ;
75 if (p_debug_level = 1) then
76 fnd_file.put_line (fnd_file.log, 'Inserting into wip_leadtime_temp for Non Network routing ' || p_routing_sequence_id ); end if ;
77 for routing_rec in routing_csr loop
78 if (p_debug_level = 1) then
79 fnd_file.put_line(fnd_file.log, 'Operation Seq.Num ' || routing_rec.operation_seq_num) ;
80 end if ;
81 insert_temp(p_routing_sequence_id, routing_rec.operation_seq_num, l_order) ;
82 end loop ;
83
84 end ;
85
86 if network_found = 0 then
87 return 1 ;
88 end if ;
89
90
91
92 -- Populate rest of the operations
93 loop
94 open network_csr (l_from_op) ;
95 fetch network_csr into l_next_op ;
96 exit when network_csr%NOTFOUND ;
97 close network_csr ;
98 l_from_op := l_next_op ;
99 l_order := l_order + 1 ;
100 if (p_debug_level = 1) then
101 fnd_file.put_line(fnd_file.log, 'Operation Seq.Num ' || l_from_op || ' Order ' || l_order) ;
102 end if ;
103 insert_temp(p_routing_sequence_id, l_from_op, l_order) ;
104 end loop ;
105
106 if network_csr%ISOPEN then
107 close network_csr ;
108 end if ;
109
110 return 1 ;
111
112 exception
113 when others then
114 return 0 ; -- Unsuccessfull
115
116 end wip_populate_leadtime_temp ;
117
118 function wip_delete_leadtime_temp (p_debug_level IN number) return number
119 as
120 begin
121
122 delete wip_leadtime_temp ;
123 if (p_debug_level = 1) then
124 fnd_file.put_line (fnd_file.log, 'Deleted rows ' || sql%ROWCOUNT ||' from wip_leadtime_temp ' );
125 end if ;
126
127 return 1 ; -- successfull
128
129 exception
130 when others then
131 return 0 ; -- Unsuccessfull
132
133 end wip_delete_leadtime_temp ;
134
135 END WIP_LEADTIME_TEMP_PKG ;