[Home] [Help]
133: IF p_parent_instance_id IS NOT NULL THEN
134: BEGIN
135: SELECT cii.serial_number, cii.inventory_item_id, cii.last_vld_organization_id
136: INTO l_sn_1, l_inv_id_1, l_org_id_1
137: FROM csi_item_instances cii, mtl_system_items msi
138: WHERE cii.instance_id = p_instance_id AND cii.inventory_item_id = msi.inventory_item_id
139: AND cii.last_vld_organization_id = msi.organization_id AND msi.eam_item_type in (1,3)
140: AND msi.serial_number_control_code <> 1;
141: EXCEPTION
146: END;
147: BEGIN
148: SELECT cii.serial_number, cii.inventory_item_id, cii.last_vld_organization_id
149: INTO l_sn_2, l_inv_id_2, l_org_id_2
150: FROM csi_item_instances cii, mtl_system_items msi, mtl_parameters mp
151: WHERE cii.instance_id = p_parent_instance_id AND cii.inventory_item_id = msi.inventory_item_id
152: AND cii.last_vld_organization_id = msi.organization_id AND msi.eam_item_type in (1,3)
153: AND msi.serial_number_control_code <> 1 AND msi.organization_id = mp.organization_id
154: AND mp.maint_organization_id = l_org_id;
379:
380: -- API body
381:
382: -- Validate instance id
383: SELECT count(instance_id) INTO l_count FROM csi_item_instances cii, mtl_system_items msi
384: WHERE cii.instance_id = p_instance_id AND cii.inventory_item_id = msi.inventory_item_id
385: AND cii.last_vld_organization_id = msi.organization_id AND msi.eam_item_type in (1,3)
386: AND msi.serial_number_control_code <> 1;
387: IF l_count = 0 THEN
390: RAISE fnd_api.g_exc_error;
391: END IF;
392:
393: -- Select the maintenance organization id
394: SELECT mp.maint_organization_id INTO x_org_id FROM csi_item_instances cii, mtl_parameters mp
395: WHERE cii.instance_id = p_instance_id AND cii.last_vld_organization_id = mp.organization_id;
396:
397: IF x_org_id is NULL THEN
398: fnd_message.set_name('EAM', 'EAM_MAINT_ORG_MISSING');