ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 2B8C4D6E8F0A4B1C9D3E5F7A9B1C3D5EY select ccv.name counter, ccv.description counter_description, xxen_util.meaning(ccv.counter_type,'CSI_COUNTER_TYPE',542) counter_type, ccv.uom_code uom, xxen_util.meaning(to_char(ccv.reading_type),'CSI_COUNTER_READING_TYPE',542) reading_type, cii.instance_number, msiv.concatenated_segments item, msiv.description item_description, cii.serial_number, mp.organization_code, (select hp.party_name from hz_parties hp where cii.owner_party_id=hp.party_id) owner, coalesce(okhab.contract_number, ( select listagg(y.contract_number,', ') within group (order by y.contract_number) from (select distinct oki.object1_id1, okhab2.contract_number from okc_k_items oki, okc_k_headers_all_b okhab2 where oki.jtot_object1_code='OKX_COUNTER' and oki.dnz_chr_id=okhab2.id) y where to_char(ccr.counter_id)=y.object1_id1 )) contract_number, oklb.line_number contract_line, ccr.value_timestamp reading_date, ccr.counter_reading reading, ccr.net_reading, ccr.life_to_date_reading, ccr.net_reading- ( select max(ccr2.net_reading) keep (dense_rank last order by ccr2.value_timestamp, ccr2.counter_value_id) from csi_counter_readings ccr2 where ccr.counter_id=ccr2.counter_id and ccr2.value_timestamp<=ccr.value_timestamp and (ccr2.value_timestamp<ccr.value_timestamp or ccr2.counter_value_id<ccr.counter_value_id) and nvl(ccr2.disabled_flag,'N')='N' ) reading_change, xxen_util.meaning(ccr.reset_mode,'CSI_CTR_READING_RESET_TYPE',542) reset_mode, ccr.reset_reason, xxen_util.meaning(ccr.adjustment_type,'CSI_CTR_ADJUST_READING_TYPE',542) adjustment_type, ccr.adjustment_reading, xxen_util.yes(ccr.disabled_flag) disabled, ccr.comments, ctt.source_txn_type_name source, coalesce( (select we.wip_entity_name from wip_entities we where ct.transaction_type_id=92 and ct.source_header_ref_id=we.wip_entity_id), (select we.wip_entity_name from eam_meter_readings emr, wip_entities we where ccr.counter_value_id=emr.meter_reading_id and emr.wip_entity_id=we.wip_entity_id) ) work_order, xxen_util.user_name(ccr.created_by) created_by, ccr.creation_date from ( select ccr.*, (select max(cca.source_object_id) keep (dense_rank last order by cca.start_date_active) from csi_counter_associations cca where ccr.counter_id=cca.counter_id and cca.source_object_code='CP') instance_id, (select max(cca.source_object_id) keep (dense_rank last order by cca.start_date_active) from csi_counter_associations cca where ccr.counter_id=cca.counter_id and cca.source_object_code='CONTRACT_LINE') contract_line_id from csi_counter_readings ccr ) ccr, csi_counters_vl ccv, csi_item_instances cii, mtl_system_items_vl msiv, mtl_parameters mp, okc_k_lines_b oklb, okc_k_headers_all_b okhab, csi_transactions ct, csi_txn_types ctt where 1=1 and (cii.last_vld_organization_id is null or msiv.eam_item_type is null or cii.last_vld_organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id)) and ccr.counter_id=ccv.counter_id and ccr.instance_id=cii.instance_id(+) and cii.inventory_item_id=msiv.inventory_item_id(+) and cii.last_vld_organization_id=msiv.organization_id(+) and cii.last_vld_organization_id=mp.organization_id(+) and ccr.contract_line_id=oklb.id(+) and oklb.dnz_chr_id=okhab.id(+) and ccr.transaction_id=ct.transaction_id(+) and ct.transaction_type_id=ctt.transaction_type_id(+) order by ccv.name, cii.instance_number, ccr.value_timestamp, ccr.counter_value_id New report. US CSI Counter Readings Counter and meter readings, one row per reading, with the item instance or maintainable asset and the service contract the counter belongs to. Reading Change is the net reading minus the net reading of the counter's previous enabled reading, so it shows the usage since the last reading, also across resets and adjustments. It is blank for the first reading of a counter. Work Order is the maintenance work order on whose completion the reading was recorded. Contract Number lists the contract of a counter attached to a contract line, or the contracts whose usage lines cover the counter. Enginatics R12 only 1=1 1 10 1=1 msiv.eam_item_type in (1,3) LOV custom Y select 'A' id, 'Asset Meters' value, 'Meters of maintainable assets and rebuildables' description from dual union all select 'C' id, 'Contract Usage Counters' value, 'Counters billed on service contract usage lines or attached to a contract line' description from dual A US Counter Usage 2 1=1 (ccr.contract_line_id is not null or to_char(ccr.counter_id) in (select oki.object1_id1 from okc_k_items oki where oki.jtot_object1_code='OKX_COUNTER')) C US Counter Usage 3 20 1=1 ccr.value_timestamp>=:reading_date_from Date US Reading Date From 4 30 1=1 ccr.value_timestamp<:reading_date_to+1 Date :$flex$.reading_date_from US Reading Date To 5 40 1=1 mp.organization_code=:organization_code LOV INV Organization Code 8E2FF36EDEB279D2E0530100007F1FF2 Y select ood.organization_code value, ood.organization_name||' ('||ftv.territory_short_name||', '||hla.town_or_city||decode(mp.organization_id,mp.master_organization_id,null,', '||mp0.organization_code)||')' description from org_organization_definitions ood, mtl_parameters mp, mtl_parameters mp0, hr_all_organization_units haou, hr_locations_all hla, fnd_territories_vl ftv where ood.organization_id=mp.organization_id and mp.organization_id<>mp.master_organization_id and ( ood.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') or not exists (select null from xxen_report_org_access xroa where xroa.access_type='INV') and ood.operating_unit in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='OU') ) and (:$flex$.operating_unit is null or ood.operating_unit in (select haouv.organization_id from hr_all_organization_units_vl haouv where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y')) and (:$flex$.ledger is null or ood.set_of_books_id in (select gl.ledger_id from gl_ledgers gl where xxen_util.contains(:$flex$.ledger,gl.name)='Y')) and nvl(ood.disable_date,sysdate)>=sysdate and mp.master_organization_id=mp0.organization_id and ood.organization_id=haou.organization_id(+) and haou.location_id=hla.location_id(+) and hla.country=ftv.territory_code(+) order by ood.organization_code US Organization Code 6 50 1=1 msiv.concatenated_segments=:item LOV INV Item 8E2FF36EDEF179D2E0530100007F1FF2 Y Y select msiv.concatenated_segments value, msiv.description||' ('||mp.organization_code||')' description from mtl_system_items_vl msiv, mtl_parameters mp where ( :$flex$.organization_code is null and ( fnd_profile.value('MFG_ORGANIZATION_ID') is null and msiv.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) or msiv.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') ) or xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' ) and msiv.organization_id=mp.organization_id order by mp.organization_code, msiv.concatenated_segments US Item 7 60 1=1 cii.instance_number=:instance_number Char US Instance Number Asset number of a maintainable asset, or the item instance number 8 70 1=1 ccv.name=:counter LOV custom Y Y select ccv.name value, ccv.description from csi_counters_vl ccv where ccv.counter_id in (select ccr.counter_id from csi_counter_readings ccr) order by ccv.name US Counter 9 80 1=1 (okhab.contract_number=:contract_number or to_char(ccr.counter_id) in (select oki.object1_id1 from okc_k_items oki, okc_k_headers_all_b okhab2 where oki.jtot_object1_code='OKX_COUNTER' and oki.dnz_chr_id=okhab2.id and okhab2.contract_number=:contract_number)) Char US Contract Number 10 90 1=1 nvl(ccr.disabled_flag,'N')='N' LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Exclude Disabled 4E6D0B1C2A3F4E5D8C7B9A0F1E2D3C4B EAM Meter Readings Meter readings of maintainable assets by organization, asset and meter, with the usage since the previous reading, resets and the work order that recorded the reading. SYSADMIN 15 COMMENTS 5 COUNTER 3 16 CREATED_BY 13 DISABLED 2 INSTANCE_NUMBER 2 3 ITEM 4 ITEM_DESCRIPTION 10 LIFE_TO_DATE_READING 1 ORGANIZATION_CODE 1 8 READING 9 READING_CHANGE 7 READING_DATE 4 11 RESET_MODE 12 RESET_REASON 6 UOM 14 WORK_ORDER Counter Usage A S Site 5F7E1C2D3B4A4F6E9D8C0B1A2F3E4D5C Usage Counter Readings Readings of counters billed on service contract usage lines, by contract and counter, with the customer, product instance and the usage since the previous reading. SYSADMIN 15 ADJUSTMENT_READING 14 ADJUSTMENT_TYPE 2 CONTRACT_LINE 1 CONTRACT_NUMBER 1 7 COUNTER 2 16 DISABLED 4 INSTANCE_NUMBER 5 ITEM 11 NET_READING 3 OWNER 10 READING 12 READING_CHANGE 9 READING_DATE 3 13 RESET_MODE 6 SERIAL_NUMBER 17 SOURCE 8 UOM Counter Usage C S Site