1 PACKAGE BODY flm_routing AS
2 /* $Header: FLMRTGDB.pls 120.1 2006/05/16 14:51:15 yulin noship $ */
3
4 PROCEDURE retrieve_items (
5 i_org_id IN NUMBER,
6 i_from_item IN VARCHAR2,
7 i_to_item IN VARCHAR2,
8 i_product_family_id IN NUMBER,
9 i_category_set_id IN NUMBER,
10 i_category_id IN NUMBER,
11 i_planner_code IN VARCHAR2,
12 i_alternate_routing_designator IN VARCHAR2,
13 o_item_tbl OUT NOCOPY item_tbl_type,
14 o_return_code OUT NOCOPY NUMBER) IS
15
16 CURSOR all_items IS
17 select msik.inventory_item_id id
18 from mtl_system_items_kfv msik
19 where msik.organization_id = i_org_id
20 and msik.bom_enabled_flag = 'Y'
21 and msik.inventory_item_flag ='Y'
22 and msik.bom_item_type <> 3
23 and msik.pick_components_flag = 'N'
24 and msik.eng_item_flag = 'N'
25 and ((msik.concatenated_segments >= i_from_item or
26 i_from_item is null) and
27 (msik.concatenated_segments <= i_to_item or
28 i_to_item is null))
29 and (i_product_family_id is null or
30 msik.inventory_item_id in (
31 select bic.component_item_id
32 from bom_bill_of_materials bbom,
33 bom_inventory_components bic
34 where bbom.assembly_item_id = i_product_family_id
35 and bbom.organization_id = i_org_id
36 and bbom.alternate_bom_designator is null
37 and bbom.bill_sequence_id = bic.bill_sequence_id))
38 and (i_category_id is null or
39 msik.inventory_item_id in (
40 select mic.inventory_item_id
41 from mtl_item_categories mic
42 where mic.organization_id = i_org_id
43 and mic.category_set_id = i_category_set_id
44 and mic.category_id = i_category_id))
45 and (i_planner_code is null or
46 msik.planner_code = i_planner_code)
47 and not exists (
48 select bor.routing_sequence_id
49 from bom_operational_routings bor
50 where bor.assembly_item_id = msik.inventory_item_id
51 and bor.organization_id = i_org_id
52 -- Added for enhancement #2647023
53 and nvl(bor.alternate_routing_designator, '~$~') = nvl(i_alternate_routing_designator, '~$~') )
54 order by msik.concatenated_segments;
55
56 l_index NUMBER;
57 l_routing_id NUMBER;
58 BEGIN
59 o_item_tbl.delete;
60 l_index := 1;
61 FOR item IN all_items LOOP
62 o_item_tbl(l_index) := item.id;
63 l_index := l_index+1;
64 END LOOP;
65 o_return_code := 0;
66 EXCEPTION
67 WHEN OTHERS THEN
68 o_return_code := 1;
69 END retrieve_items;
70
71
72 PROCEDURE a_retrieve_option_items (
73 i_org_id IN NUMBER,
74 i_from_item IN VARCHAR2,
75 i_to_item IN VARCHAR2,
76 i_product_family_id IN NUMBER,
77 i_category_set_id IN NUMBER,
78 i_category_id IN NUMBER,
79 i_planner_code IN VARCHAR2,
80 i_alternate_routing_designator IN VARCHAR2,
81 i_assembly_item_id IN NUMBER,
82 i_alt_designator IN VARCHAR2,
83 o_return_code OUT NOCOPY NUMBER) IS
84
85 /* perf bug 5204346 - change in (...) to be a regular query */
86 CURSOR all_option_items IS
87 select id from (
88 select distinct msik.inventory_item_id id, msik.concatenated_segments c_name
89 from mtl_system_items_kfv msik, bom_bill_of_materials bbom2,
90 bom_inventory_components bic2
91 where msik.organization_id = i_org_id
92 and msik.bom_enabled_flag = 'Y'
93 and msik.inventory_item_flag = 'Y'
94 and msik.bom_item_type = 2
95 and msik.pick_components_flag = 'N'
96 and msik.eng_item_flag = 'N'
97 and ((msik.concatenated_segments >= i_from_item or i_from_item is null) and
98 (msik.concatenated_segments <= i_to_item or i_to_item is null))
99 and (i_product_family_id is null or
100 msik.inventory_item_id in
101 (select bic.component_item_id
102 from bom_bill_of_materials bbom, bom_inventory_components bic
103 where bbom.assembly_item_id = i_product_family_id
104 and bbom.organization_id = i_org_id
105 and bbom.alternate_bom_designator is null
106 and bbom.bill_sequence_id = bic.bill_sequence_id))
107 and (i_category_id is null or
108 msik.inventory_item_id in
109 (select mic.inventory_item_id
110 from mtl_item_categories mic
111 where mic.organization_id = i_org_id
112 and mic.category_set_id = i_category_set_id
113 and mic.category_id = i_category_id))
114 and (i_planner_code is null or msik.planner_code = i_planner_code)
115 and not exists
116 (select bor.routing_sequence_id
117 from bom_operational_routings bor
118 where bor.assembly_item_id = msik.inventory_item_id
119 and bor.organization_id = i_org_id
120 -- added for enhancement #2647023
121 and nvl(bor.alternate_routing_designator, '~$~') =
122 nvl(i_alternate_routing_designator, '~$~'))
123 and bbom2.organization_id = i_org_id
124 and bbom2.assembly_item_id = i_assembly_item_id
125 and nvl(bbom2.alternate_bom_designator, '$$$') =
126 nvl(i_alt_designator, '$$$')
127 and bic2.bill_sequence_id = bbom2.common_bill_sequence_id
128 and nvl(bic2.effectivity_date, sysdate - 1) < sysdate
129 and nvl(bic2.disable_date, sysdate + 1) > sysdate
130 and msik.inventory_item_id = bic2.component_item_id
131 ) order by c_name;
132
133 /* select msik.inventory_item_id id
134 from mtl_system_items_kfv msik
135 where msik.organization_id = i_org_id
136 and msik.bom_enabled_flag = 'Y'
137 and msik.inventory_item_flag = 'Y'
138 and msik.bom_item_type =2
139 and msik.pick_components_flag = 'N'
140 and msik.eng_item_flag = 'N'
141 and ((msik.concatenated_segments >= i_from_item or
142 i_from_item is null) and
143 (msik.concatenated_segments <= i_to_item or
144 i_to_item is null))
145 and (i_product_family_id is null or
146 msik.inventory_item_id in (
147 select bic.component_item_id
148 from bom_bill_of_materials bbom,
149 bom_inventory_components bic
150 where bbom.assembly_item_id = i_product_family_id
151 and bbom.organization_id = i_org_id
152 and bbom.alternate_bom_designator is null
153 and bbom.bill_sequence_id = bic.bill_sequence_id))
154 and (i_category_id is null or
155 msik.inventory_item_id in (
156 select mic.inventory_item_id
157 from mtl_item_categories mic
158 where mic.organization_id = i_org_id
159 and mic.category_set_id = i_category_set_id
160 and mic.category_id = i_category_id))
161 and (i_planner_code is null or
162 msik.planner_code = i_planner_code)
163 and not exists (
164 select bor.routing_sequence_id
165 from bom_operational_routings bor
166 where bor.assembly_item_id = msik.inventory_item_id
167 and bor.organization_id = i_org_id
168 -- Added for enhancement #2647023
169 and nvl(bor.alternate_routing_designator, '~$~') = nvl(i_alternate_routing_designator, '~$~') )
170 and msik.inventory_item_id in (
171 select bic2.component_item_id
172 from mtl_system_items_b msi2,
173 bom_bill_of_materials bbom2,
174 bom_inventory_components bic2
175 where bbom2.organization_id = i_org_id and
176 bbom2.assembly_item_id = i_assembly_item_id and
177 nvl(bbom2.alternate_bom_designator,'$$$') = nvl(i_alt_designator,'$$$') and
178 bic2.bill_sequence_id = bbom2.common_bill_sequence_id and
179 msi2.organization_id = i_org_id and
180 bic2.component_item_id = msi2.inventory_item_id and
181 msi2.bom_item_type = 2 and
182 nvl(bic2.effectivity_date,sysdate-1) < sysdate and
183 nvl(bic2.disable_date,sysdate+1) > sysdate)
184 order by msik.concatenated_segments; */
185
186 l_index NUMBER;
187 l_routing_id NUMBER;
188 l_count NUMBER;
189 l_bill_count NUMBER := 0;
190 item_already_exist BOOLEAN := FALSE;
191 BEGIN
192
193 select count(*)
194 into l_bill_count
195 from bom_bill_of_materials
196 where assembly_item_id = i_assembly_item_id and
197 organization_id = i_org_id and
198 nvl(alternate_bom_designator,'$$$') =
199 nvl(i_alt_designator,'$$$');
200
201 if(l_bill_count = 0) then
202 return;
203 end if;
204
205 l_index := 1;
206 FOR item IN all_option_items LOOP
207
208 --check that this item not exist in table(check for loop also)
209 item_already_exist := false;
210 if(g_item_tbl.COUNT > 1) then
211 for i in g_item_tbl.FIRST .. g_item_tbl.LAST
212 LOOP
213 if(g_item_tbl(i) = item.id) then
214 item_already_exist := true;
215 end if;
216 END LOOP;
217 end if;
218
219 if(item_already_exist = false) then
220 g_item_tbl(g_tbl_index) := item.id;
221 g_tbl_index := g_tbl_index+1;
222 a_retrieve_option_items(i_org_id,
223 i_from_item,
224 i_to_item,
225 i_product_family_id,
226 i_category_set_id,
227 i_category_id,
228 i_planner_code,
229 i_alternate_routing_designator,
230 item.id,
231 i_alt_designator,
232 o_return_code);
233 end if;
234 END LOOP;
235 o_return_code := 0;
236
237 EXCEPTION
238 WHEN OTHERS THEN
239 o_return_code := 1;
240
241 END a_retrieve_option_items;
242
243
244 PROCEDURE retrieve_option_items (
245 i_org_id IN NUMBER,
246 i_from_item IN VARCHAR2,
247 i_to_item IN VARCHAR2,
248 i_product_family_id IN NUMBER,
249 i_category_set_id IN NUMBER,
250 i_category_id IN NUMBER,
251 i_planner_code IN VARCHAR2,
252 i_alternate_routing_designator IN VARCHAR2,
253 i_assembly_item_id IN NUMBER,
254 i_alt_designator IN VARCHAR2,
255 o_item_tbl OUT NOCOPY item_tbl_type,
256 o_return_code OUT NOCOPY NUMBER) IS
257 BEGIN
258
259 g_item_tbl.delete;
260 g_tbl_index := 0;
261
262 a_retrieve_option_items(i_org_id,
263 i_from_item,
264 i_to_item,
265 i_product_family_id,
266 i_category_set_id,
267 i_category_id,
268 i_planner_code,
269 i_alternate_routing_designator,
270 i_assembly_item_id,
271 i_alt_designator,
272 o_return_code);
273 --return the table of items
274 o_item_tbl := g_item_tbl;
275
276 EXCEPTION
277 WHEN OTHERS THEN
278 o_return_code := 1;
279
280 END retrieve_option_items;
281
282
283 PROCEDURE retrieve_mass_change_items (
284 i_org_id IN NUMBER,
285 i_line_id IN NUMBER,
286 i_from_item IN VARCHAR2,
287 i_to_item IN VARCHAR2,
288 i_product_family_id IN NUMBER,
289 i_category_set_id IN NUMBER,
290 i_category_id IN NUMBER,
291 i_planner_code IN VARCHAR2,
292 i_alt_desig_code IN VARCHAR2,
293 i_alt_desig_check IN NUMBER,
294 i_item_type_pf IN NUMBER,
295 o_item_tbl OUT NOCOPY item_rtg_tbl_type,
296 o_return_code OUT NOCOPY NUMBER) IS
297
298 CURSOR all_items IS
299 select msik.inventory_item_id id, bor1.alternate_routing_designator alt
300 from mtl_system_items_kfv msik, bom_operational_routings bor1
301 where msik.organization_id = i_org_id
302 and msik.organization_id = bor1.organization_id
303 and msik.inventory_item_id = bor1.assembly_item_id
304 and msik.bom_enabled_flag = 'Y'
305 and msik.inventory_item_flag = 'Y'
306 and msik.bom_item_type <> 3
307 and msik.pick_components_flag = 'N'
308 and bor1.routing_type = 1
309 and msik.eng_item_flag = 'N'
310 and ((msik.concatenated_segments >= i_from_item or
311 i_from_item is null) and
312 (msik.concatenated_segments <= i_to_item or
313 i_to_item is null))
314 and (i_product_family_id is null or
315 msik.inventory_item_id in (
316 select bic.component_item_id
317 from bom_bill_of_materials bbom,
318 bom_inventory_components bic
319 where bbom.assembly_item_id = i_product_family_id
320 and bbom.organization_id = i_org_id
321 and bbom.alternate_bom_designator is null
322 and bbom.bill_sequence_id = bic.bill_sequence_id))
323 and (i_category_id is null or
324 msik.inventory_item_id in (
325 select mic.inventory_item_id
326 from mtl_item_categories mic
327 where mic.organization_id = i_org_id
328 and mic.category_set_id = i_category_set_id
329 and mic.category_id = i_category_id))
330 and (i_planner_code is null or
331 msik.planner_code = i_planner_code)
332 and ( (nvl(i_alt_desig_check,2) = 2) or
333 (i_alt_desig_check = 1 and
334 nvl(bor1.alternate_routing_designator,'$$$') =
335 nvl(i_alt_desig_code,'$$$') ) )
336 and ( (i_item_type_pf = 1 and msik.bom_item_type = 5) or
337 (i_item_type_pf = 2 and msik.bom_item_type <> 5)
338 )
339 order by msik.concatenated_segments;
340
341 l_index NUMBER;
342 l_routing_id NUMBER;
343 l_routing_found NUMBER:=0;
344 l_max_level NUMBER;
345 dummy NUMBER;
346 BEGIN
347
348 o_item_tbl.delete;
349 l_index := 1;
350
351 FOR item IN all_items LOOP
352
353 l_routing_found := 0;
354
355 select count(routing_sequence_id)
356 into l_routing_found
357 from bom_operational_routings
358 where organization_id = i_org_id and
359 line_id = i_line_id and
360 assembly_item_id = item.id and
361 nvl(alternate_routing_designator,'$$$') =
362 nvl(item.alt,'$$$');
363
364 if(l_routing_found > 0) then
365 o_item_tbl(l_index).item_id := item.id;
366 o_item_tbl(l_index).routing_designator := item.alt;
367 l_index := l_index+1;
368 end if;
369
370 END LOOP;
371 o_return_code := 0;
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 o_return_code := 1;
376
377 END retrieve_mass_change_items;
378
379 END flm_routing;
[Home] [Help]