[Home] [Help]
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(
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)
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
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)
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
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)
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:
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)
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
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)
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;
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)
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
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,
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)
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
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)
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
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)
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:
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)
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
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)
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;
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)
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
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;
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 */
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 */