1 PACKAGE BODY eam_workbench_tree AS
2 /* $Header: EAMWBTRB.pls 120.5 2006/03/29 05:46:33 kmurthy noship $ */
3
4 /**
5 * Given the search criteria, this function finds out all the asset numbers
6 * and insert those into the temp table under a group id which is returned.
7 * It returns -1 if nothing found.
8 */
9
10 function find_all_asset_numbers(p_org_id number,
11 p_instance_id number,
12 p_location_id number,
13 p_category_id number,
14 p_owning_dept_id number,
15 p_asset_group_id number,
16 p_asset_number varchar2,
17 p_transferred_asset varchar2,
18 p_set_name_id number) return number is
19 l_group_id number;
20 l_count_first number;
21 l_count_second number;
22 l_return_status number;
23 begin
24 l_return_status := 0;
25 l_count_first := 0;
26 l_count_second := 0;
27
28 select eam_asset_explosion_temp_s.nextval
29 into l_group_id from dual;
30
31
32 INSERT INTO eam_asset_explosion_temp(
33 group_id,
34 asset_group_id,
35 asset_number,
36 low_level_code)
37 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
38 FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp,eam_org_maint_defaults eomd
39 WHERE msi.eam_item_type = 1 AND msi.inventory_item_id = cii.inventory_item_id
40 AND msi.organization_id = cii.last_vld_organization_id AND msi.serial_number_control_code <> 1
41 AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate
42 AND msi.organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id
43 AND cii.instance_id = eomd.object_id (+) AND eomd.object_type (+) = 50
44 AND (p_location_id IS NULL OR eomd.area_id = p_location_id)
45 AND (p_category_id IS NULL OR cii.category_id = p_category_id)
46 AND (p_owning_dept_id IS NULL OR eomd.owning_department_id = p_owning_dept_id)
47 AND (p_asset_group_id IS NULL OR cii.inventory_item_id = p_asset_group_id)
48 AND (p_asset_number IS NULL OR cii.serial_number = p_asset_number)
49 AND (p_instance_id IS NULL OR cii.instance_id = p_instance_id)
50 AND eomd.organization_id(+) = p_org_id
51 AND (p_set_name_id is null OR
52 (
53 (cii.instance_id,3) in
54 (select maintenance_object_id,maintenance_object_type
55 from eam_pm_schedulings where set_name_id = p_set_name_id )
56 ) );
57
58 IF SQL%ROWCOUNT = 0 THEN
59 l_count_first := 1;
60 END IF;
61
62 -- Transferred Assets
63 IF (p_transferred_asset = 'Y') THEN
64 INSERT INTO eam_asset_explosion_temp(
65 group_id,
66 asset_group_id,
67 asset_number,
68 low_level_code)
69 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
70 FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp,
71 (select * from eam_org_maint_defaults where organization_id = p_org_id) eomd
72 WHERE msi.eam_item_type = 1
73 AND msi.inventory_item_id = cii.inventory_item_id
74 AND msi.organization_id = cii.last_vld_organization_id
75 AND msi.serial_number_control_code <> 1
76 AND nvl(cii.active_start_date, sysdate-1) <= sysdate
77 AND nvl(cii.active_end_date, sysdate+1) >= sysdate
78 AND msi.organization_id = mp.organization_id
79 AND mp.maint_organization_id <> p_org_id
80 AND cii.instance_id = eomd.object_id (+)
81 AND eomd.object_type (+) = 50
82 AND (p_location_id IS NULL OR eomd.area_id = p_location_id)
83 AND (p_category_id IS NULL OR cii.category_id = p_category_id)
84 AND (p_owning_dept_id IS NULL OR eomd.owning_department_id = p_owning_dept_id)
85 AND (p_asset_group_id IS NULL OR cii.inventory_item_id = p_asset_group_id)
86 AND (p_asset_number IS NULL OR cii.serial_number = p_asset_number)
87 AND (p_instance_id IS NULL OR cii.instance_id = p_instance_id)
88 AND (p_set_name_id is null OR
89 (
90 (cii.instance_id,3) in
91 (select maintenance_object_id,maintenance_object_type
92 from eam_pm_schedulings where set_name_id = p_set_name_id )
93 ) )
94 AND EXISTS (SELECT 1
95 FROM wip_discrete_jobs
96 WHERE organization_id = p_org_id
97 AND maintenance_object_id = cii.instance_id) ;
98 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
99 l_count_first := 1;
100 ELSE
101 l_count_first := 0;
102 END IF;
103 END IF;
104
105 IF (p_instance_id is not null) then
106 INSERT INTO eam_asset_explosion_temp(
107 group_id,
108 asset_group_id,
109 asset_number,
110 low_level_code)
111 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
112 FROM mtl_eam_network_assets mena, csi_item_instances cii, mtl_parameters mp
113 WHERE p_instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
114 AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id;
115
116 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
117 l_count_first := 1;
118 ELSE
119 l_count_first := 0;
120 END IF;
121
122 -- Transferred Assets
123 IF (p_transferred_asset = 'Y') THEN
124 INSERT INTO eam_asset_explosion_temp(
125 group_id,
126 asset_group_id,
127 asset_number,
128 low_level_code)
129 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
130 FROM mtl_eam_network_assets mena, csi_item_instances cii, mtl_parameters mp
131 WHERE p_instance_id = mena.network_object_id
132 AND mena.maintenance_object_id = cii.instance_id
133 AND cii.last_vld_organization_id = mp.organization_id
134 AND mp.maint_organization_id <> p_org_id
135 AND EXISTS (SELECT 1
136 FROM wip_discrete_jobs
137 WHERE organization_id = p_org_id
138 AND maintenance_object_id = cii.instance_id) ;
139 END IF;
140 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
141 l_count_first := 1;
142 ELSE
143 l_count_first := 0;
144 END IF;
145
146 ELSIF (p_asset_number IS NOT NULL AND p_asset_group_id IS NOT NULL) THEN
147 INSERT INTO eam_asset_explosion_temp(
148 group_id,
149 asset_group_id,
150 asset_number,
151 low_level_code)
152 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
153 FROM mtl_eam_network_assets mena, csi_item_instances ciin, csi_item_instances cii, mtl_parameters mp
154 WHERE ciin.serial_number = p_asset_number AND ciin.inventory_item_id = p_asset_group_id
155 AND ciin.instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
156 AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id;
157
158 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
159 l_count_first := 1;
160 ELSE
161 l_count_first := 0;
162 END IF;
163 -- Transferred Assets
164 IF (p_transferred_asset = 'Y') THEN
165 INSERT INTO eam_asset_explosion_temp(
166 group_id,
167 asset_group_id,
168 asset_number,
169 low_level_code)
170 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
171 FROM mtl_eam_network_assets mena, csi_item_instances ciin, csi_item_instances cii, mtl_parameters mp
172 WHERE ciin.serial_number = p_asset_number
173 AND ciin.inventory_item_id = p_asset_group_id
174 AND ciin.instance_id = mena.network_object_id
175 AND mena.maintenance_object_id = cii.instance_id
176 AND cii.last_vld_organization_id = mp.organization_id
177 AND mp.maint_organization_id <> p_org_id
178 AND EXISTS (SELECT 1
179 FROM wip_discrete_jobs
180 WHERE organization_id = p_org_id
181 AND maintenance_object_id = cii.instance_id) ;
182 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
183 l_count_first := 1;
184 ELSE
185 l_count_first := 0;
186 END IF;
187 END IF;
188 END IF;
189
190 IF l_count_first = 1 THEN
191 l_return_status := 1;
192 END IF;
193
194 if l_return_status = 1 then
195 return -1;
196 else
197 return l_group_id;
198 end if;
199
200 EXCEPTION
201 WHEN NO_DATA_FOUND THEN
202 return -1;
203
204 end find_all_asset_numbers;
205
206
207 /**
208 * Given the search criteria, this procedure finds out all the applicable
209 * asset numbers and builds the hierarchy trees.
210 */
211 function construct_hierarchy_forest(p_org_id number,
212 p_instance_id number,
213 p_location_id number,
214 p_category_id number,
215 p_owning_dept_id number,
216 p_asset_group_id number,
217 p_asset_number varchar2,
218 p_set_name_id number) return number as
219 language java name 'oracle.apps.eam.workbench.WorkBenchTree.constructTree(
220 java.lang.Long,
221 java.lang.Long,
222 java.lang.Long,
223 java.lang.Long,
224 java.lang.Long,
225 java.lang.Long,
226 java.lang.String,
227 java.lang.Long) return long';
228
229
230 /**
231 * Procedure construct_hierarchy_forest must be called before this function can
232 * be called. Otherwise, it will cause unexpected behavior.
233 * Given the asset number and asset group id, this function will copy the
234 * subtree of the given asset number to the temp table. It returns the group_id
235 * back so the user can reference it. It returns NULL if the given asset number
236 * is not found.
237 */
238 function copy_subtree_to_temp_table(p_asset_group_id number,
239 p_asset_number varchar2) return number as
240 language java name 'oracle.apps.eam.workbench.WorkBenchTree.getSubtree(
241 long,
242 java.lang.String) return long';
243
244 /**
245 * This procedure releases the resource taken explicity.
246 */
247 procedure clear_forest as
248 language java name 'oracle.apps.eam.workbench.WorkBenchTree.clear()';
249
250 /** added by sraval to include rebuildables in activity workbench
251 */
252 --p_include_rebuildable param will have a value of null from activity workbench and 'Y' from planenrs workbench
253 function find_all_asset_numbers(p_org_id number,
254 p_instance_id number,
255 p_location_id number,
256 p_category_id number,
257 p_owning_dept_id number,
258 p_asset_group_id number,
259 p_asset_number varchar2,
260 p_include_rebuildable varchar2,
261 p_transferred_asset varchar2,
262 p_set_name_id number
263 ) return number is
264 l_group_id number;
265 l_count_first number;
266 l_return_status number;
267
268 begin
269 l_return_status := 0;
270 l_count_first := 0;
271 select eam_asset_explosion_temp_s.nextval
272 into l_group_id from dual;
273
274 INSERT INTO eam_asset_explosion_temp(
275 group_id,
276 asset_group_id,
277 asset_number,
278 low_level_code)
279 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
280 FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp,eam_org_maint_defaults eomd
281 WHERE msi.eam_item_type in (1,3) AND msi.inventory_item_id = cii.inventory_item_id
282 AND msi.organization_id = cii.last_vld_organization_id AND msi.serial_number_control_code <> 1
283 AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate
284 AND msi.organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id
285 AND cii.instance_id = eomd.object_id (+) AND eomd.object_type (+) = 50
286 AND (p_location_id IS NULL OR eomd.area_id = p_location_id)
287 AND (p_category_id IS NULL OR cii.category_id = p_category_id)
288 AND (p_owning_dept_id IS NULL OR eomd.owning_department_id = p_owning_dept_id)
289 AND (p_asset_group_id IS NULL OR cii.inventory_item_id = p_asset_group_id)
290 AND (p_asset_number IS NULL OR cii.serial_number = p_asset_number)
291 AND (p_instance_id IS NULL OR cii.instance_id = p_instance_id)
292 AND (p_set_name_id is null OR
293 (
294 (cii.instance_id,3) in
295 (select maintenance_object_id,maintenance_object_type
296 from eam_pm_schedulings where set_name_id = p_set_name_id )
297 ) )
298 AND eomd.organization_id(+) = p_org_id;
299 -- Is this stii required, WE team please check ????
300 -- (p_include_rebuildable='Y' and msn.current_status in (1,3,4))))) --planners workbench will pass p_include_rebuildable as 'Y'
301
302 IF SQL%ROWCOUNT = 0 THEN
303 l_count_first := 1;
304 END IF;
305
306 -- Transferred Assets
307 IF (p_transferred_asset = 'Y') THEN
308 INSERT INTO eam_asset_explosion_temp(
309 group_id,
310 asset_group_id,
311 asset_number,
312 low_level_code)
313 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
314 FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp,
315 (select * from eam_org_maint_defaults where organization_id = p_org_id) eomd
316 WHERE msi.eam_item_type in (1,3) AND msi.inventory_item_id = cii.inventory_item_id
317 AND msi.organization_id = cii.last_vld_organization_id AND msi.serial_number_control_code <> 1
318 AND nvl(cii.active_start_date, sysdate-1) <= sysdate AND nvl(cii.active_end_date, sysdate+1) >= sysdate
319 AND msi.organization_id = mp.organization_id AND mp.maint_organization_id <> p_org_id
323 AND (p_owning_dept_id IS NULL OR eomd.owning_department_id = p_owning_dept_id)
320 AND cii.instance_id = eomd.object_id (+) AND eomd.object_type (+) = 50
321 AND (p_location_id IS NULL OR eomd.area_id = p_location_id)
322 AND (p_category_id IS NULL OR cii.category_id = p_category_id)
324 AND (p_asset_group_id IS NULL OR cii.inventory_item_id = p_asset_group_id)
325 AND (p_asset_number IS NULL OR cii.serial_number = p_asset_number)
326 AND (p_instance_id IS NULL OR cii.instance_id = p_instance_id)
327 AND (p_set_name_id is null OR
328 (
329 (cii.instance_id,3) in
330 (select maintenance_object_id,maintenance_object_type
331 from eam_pm_schedulings where set_name_id = p_set_name_id )
332 ) )
333 AND EXISTS (SELECT 1
334 FROM wip_discrete_jobs
335 WHERE organization_id = p_org_id
336 AND maintenance_object_id = cii.instance_id) ;
337 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
338 l_count_first := 1;
339 ELSE
340 l_count_first := 0;
341 END IF;
342 END IF;
343
344 IF (p_instance_id is not null) then
345 INSERT INTO eam_asset_explosion_temp(
346 group_id,
347 asset_group_id,
348 asset_number,
349 low_level_code)
350 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
351 FROM mtl_eam_network_assets mena, csi_item_instances cii, mtl_parameters mp
352 WHERE p_instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
353 AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id;
354
355 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
356 l_count_first := 1;
357 ELSE
358 l_count_first := 0;
359 END IF;
360 -- Transferred Assets
361 IF (p_transferred_asset = 'Y') THEN
362 INSERT INTO eam_asset_explosion_temp(
363 group_id,
364 asset_group_id,
365 asset_number,
366 low_level_code)
367 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
368 FROM mtl_eam_network_assets mena, csi_item_instances cii, mtl_parameters mp
369 WHERE p_instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
370 AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id <> p_org_id
371 AND EXISTS (SELECT 1
372 FROM wip_discrete_jobs
373 WHERE organization_id = p_org_id
374 AND maintenance_object_id = cii.instance_id) ;
375 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
376 l_count_first := 1;
377 ELSE
378 l_count_first := 0;
379 END IF;
380 END IF;
381 ELSIF (p_asset_number is not null AND p_asset_group_id IS NOT NULL) THEN
382 INSERT INTO eam_asset_explosion_temp(
383 group_id,
384 asset_group_id,
385 asset_number,
386 low_level_code)
387 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
388 FROM mtl_eam_network_assets mena, csi_item_instances ciin, csi_item_instances cii, mtl_parameters mp
389 WHERE ciin.serial_number = p_asset_number AND ciin.inventory_item_id = p_asset_group_id
390 AND ciin.instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
391 AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id = p_org_id;
392
393 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
394 l_count_first := 1;
395 ELSE
396 l_count_first := 0;
397 END IF;
398 -- Transferred Assets
399 IF (p_transferred_asset = 'Y') THEN
400 INSERT INTO eam_asset_explosion_temp(
401 group_id,
402 asset_group_id,
403 asset_number,
404 low_level_code)
405 SELECT eam_asset_explosion_temp_s.currval, cii.inventory_item_id, cii.serial_number, 1
406 FROM mtl_eam_network_assets mena, csi_item_instances ciin, csi_item_instances cii, mtl_parameters mp
407 WHERE ciin.serial_number = p_asset_number AND ciin.inventory_item_id = p_asset_group_id
408 AND ciin.instance_id = mena.network_object_id AND mena.maintenance_object_id = cii.instance_id
409 AND cii.last_vld_organization_id = mp.organization_id AND mp.maint_organization_id <> p_org_id
410 AND EXISTS (SELECT 1
411 FROM wip_discrete_jobs
412 WHERE organization_id = p_org_id
413 AND maintenance_object_id = cii.instance_id) ;
414 IF (SQL%ROWCOUNT = 0) AND (l_count_first = 1) THEN
415 l_count_first := 1;
416 ELSE
417 l_count_first := 0;
418 END IF;
419 END IF;
420 END IF;
421
422 IF SQL%ROWCOUNT = 0 AND l_count_first = 1 THEN
423 l_return_status := 1;
424 END IF;
425
426 if l_return_status = 1 then
427 return -1;
428 else
429 return l_group_id ;
430 end if;
431
432 EXCEPTION
433 WHEN NO_DATA_FOUND THEN
434 return -1;
435
436 end find_all_asset_numbers;
437
438 /** added by sraval to include rebuildables in activity workbench
439 */
440 function construct_hierarchy_forest(p_org_id number,
441 p_instance_id number,
442 p_location_id number,
443 p_category_id number,
444 p_owning_dept_id number,
445 p_asset_group_id number,
446 p_asset_number varchar2,
447 p_include_rebuildable varchar2,
448 p_set_name_id number) return number as
449 language java name 'oracle.apps.eam.workbench.WorkBenchTree.constructTree(
450 java.lang.Long,
451 java.lang.Long,
452 java.lang.Long,
453 java.lang.Long,
454 java.lang.Long,
455 java.lang.Long,
456 java.lang.String,
457 java.lang.String,
458 java.lang.Long) return long';
459
460
461 /* This procedure is used to delete the session data from eam_asset_explosion_temp
462 table. This is added for the bug #2688078
463 */
464 procedure clear_eam_asset(p_group_id IN NUMBER) is
465 PRAGMA AUTONOMOUS_TRANSACTION;
466
467 begin
468 -- removed redundant IF condition for deleting the rows . Bug 3616034
469 delete from eam_asset_explosion_temp where group_id = p_group_id;
470 commit;
471 end;
472
473 /* Code Added for bug 3982343 Start */
474 procedure clear_eam_asset
475 (
476 p_global_group_ids IN global_group_ids
477 )
478 IS
479 PRAGMA AUTONOMOUS_TRANSACTION;
480 BEGIN -- Bug 4175235 replaced FOR LOOP with FORALL
481 FORALL i in p_global_group_ids.FIRST..p_global_group_ids.LAST
482 DELETE
483 FROM eam_asset_explosion_temp
484 WHERE group_id = p_global_group_ids(i);
485 COMMIT;
486 END;
487 /* Code Added for bug 3982343 End */
488
489 END eam_workbench_tree;
[Home] [Help]