[Home] [Help]
63: /* Bug # 5154927 : Corrected the query for both correctness and performance */
64: CURSOR METER_READING_IDS(P_WIP_ENTITY_ID NUMBER) IS
65: select ccr.counter_value_id meter_reading_id
66: from csi_counter_readings ccr, csi_transactions cct,
67: csi_counter_associations cca, wip_discrete_jobs wdj
68: where ccr.transaction_id = cct.transaction_id
69: and cct.transaction_type_id = 92
70: and cct.source_header_ref_id = wdj.wip_entity_id
71: and wdj.wip_entity_id = p_wip_entity_id
117:
118: -- Get the activity_association_id for this work order
119:
120: select meaa.activity_association_id into l_activity_association_id
121: from wip_discrete_jobs wdj, mtl_eam_asset_activities meaa
122: where
123: wdj.wip_entity_id=p_wip_entity_id and
124: wdj.maintenance_object_id=meaa.maintenance_object_id and
125: wdj.maintenance_object_type=meaa.maintenance_object_type and
158: transaction_type=1
159: and transaction_id in
160: (select max(ejct.transaction_id) from
161: eam_job_completion_txns ejct,
162: wip_discrete_jobs wdj1,
163: wip_discrete_jobs wdj2
164: where wdj1.wip_entity_id=p_wip_entity_id
165: and wdj1.maintenance_object_type = wdj2.maintenance_object_type
166: and wdj1.maintenance_object_id = wdj2.maintenance_object_id
159: and transaction_id in
160: (select max(ejct.transaction_id) from
161: eam_job_completion_txns ejct,
162: wip_discrete_jobs wdj1,
163: wip_discrete_jobs wdj2
164: where wdj1.wip_entity_id=p_wip_entity_id
165: and wdj1.maintenance_object_type = wdj2.maintenance_object_type
166: and wdj1.maintenance_object_id = wdj2.maintenance_object_id
167: and nvl(wdj1.primary_item_id, -99) = nvl(wdj2.primary_item_id, -99)
304: x_asset_group_id,
305: x_asset_number,
306: x_asset_activity_id,
307: x_rebuild_id
308: from wip_discrete_jobs
309: where wip_entity_id = p_wip_entity_id;
310:
311: -- do nothing for rebuildable work order
312: if ( x_rebuild_id is not null ) then