ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B24731089D434781E0530100007FDA17Y select hca.account_number, hp.party_name, (select hp2.party_name from hz_parties hp2 where cii.owner_party_id=hp2.party_id) owner_party, msiv.concatenated_segments item, msiv.description item_description, xxen_util.meaning(msiv.item_type,'ITEM_TYPE',3) user_item_type, cii.instance_number, cii.serial_number, cii.lot_number, cis.name status, xxen_util.meaning(cii.instance_type_code,'CSI_INST_TYPE_CODE',542) instance_type, cii.quantity, cii.unit_of_measure, cii.inventory_revision, csv.name system, (select cii2.instance_number from csi_ii_relationships ciir, csi_item_instances cii2 where cii.instance_id=ciir.subject_id and ciir.relationship_type_code='COMPONENT-OF' and nvl(ciir.active_end_date,sysdate+1)>sysdate and ciir.object_id=cii2.instance_id) parent_instance, cii.install_date, cii.active_start_date, cii.active_end_date, ooha.order_number, ooha.cust_po_number, ( select listagg(y.contract_number,', ') within group (order by y.contract_number) from ( select distinct oki.object1_id1, okhab.contract_number from okc_k_items oki, okc_k_lines_b oklb, okc_statuses_b osb, okc_k_headers_all_b okhab where oki.jtot_object1_code='OKX_CUSTPROD' and oki.cle_id=oklb.id and oklb.sts_code=osb.code and osb.ste_code='ACTIVE' and oklb.dnz_chr_id=okhab.id ) y where to_char(cii.instance_id)=y.object1_id1 ) contract_number, xxen_util.meaning(cii.location_type_code,'CSI_INST_LOCATION_SOURCE_CODE',542) location_type, case cii.location_type_code when 'HZ_PARTY_SITES' then (select hl2.address1||nvl2(hl2.city,', '||hl2.city,null) from hz_party_sites hps2, hz_locations hl2 where cii.location_id=hps2.party_site_id and hps2.location_id=hl2.location_id) when 'HZ_LOCATIONS' then (select hl2.address1||nvl2(hl2.city,', '||hl2.city,null) from hz_locations hl2 where cii.location_id=hl2.location_id) when 'INTERNAL_SITE' then (select hla.location_code from hr_locations_all hla where cii.location_id=hla.location_id) when 'INVENTORY' then (select mp.organization_code from mtl_parameters mp where cii.inv_organization_id=mp.organization_id)||nvl2(cii.inv_subinventory_name,' '||cii.inv_subinventory_name,null) when 'WIP' then (select we.wip_entity_name from wip_entities we where cii.wip_job_id=we.wip_entity_id) when 'PROJECT' then (select ppa.segment1 from pa_projects_all ppa where cii.pa_project_id=ppa.project_id) end current_location, ftv.territory_short_name country, hl.city, hl.state, hl.address1||nvl2(hl.address1,nvl2(hl.address2,', ',null),null)||hl.address2 install_address1, hl.address3||nvl2(hl.address3,nvl2(hl.address4,', ',null),null)||hl.address4 install_address2, hl.postal_code &party_columns from csi_item_instances cii, csi_instance_statuses cis, mtl_system_items_vl msiv, hz_cust_accounts hca, hz_parties hp, hz_party_sites hps, hz_locations hl, fnd_territories_vl ftv, csi_systems_v csv, oe_order_lines_all oola, oe_order_headers_all ooha &party_from where 1=1 and cii.instance_status_id=cis.instance_status_id and cii.inventory_item_id=msiv.inventory_item_id(+) and cii.last_vld_organization_id=msiv.organization_id(+) and cii.owner_party_account_id=hca.cust_account_id(+) and hca.party_id=hp.party_id(+) and cii.install_location_id=hps.party_site_id(+) and hps.location_id=hl.location_id(+) and hl.country=ftv.territory_code(+) and cii.system_id=csv.system_id(+) and cii.last_oe_order_line_id=oola.line_id(+) and oola.header_id=ooha.header_id(+) &party_where order by hca.account_number, msiv.concatenated_segments, cii.instance_number &party_order Added owner party, lot, parent instance, install and active dates, contract number, location type and current location, parameters Serial Number, Sales Order, Owner Party, Install Date, Contract Coverage and Show Parties. CSI AR CSI Customer Products Summary Application: قاعدة التركيبات Source: CSI: تقرير إجمالي منتجات العميل (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG D CSI Customer Products Summary Application: Install Base Source: CSI: Kundenprodukte - Übersicht (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG E CSI Customer Products Summary Application: Install Base Source: CSI: Resumen de Productos del Cliente (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG F CSI Customer Products Summary Application: Install Base Source: CSI : Etat Synthèse des produits client (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG I CSI Customer Products Summary Application: Install Base Source: CSI: Rapporto sintetico Prodotti cliente (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG JA CSI Customer Products Summary Application: Install Base Source: CSI: 顧客製品要約レポート(XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG KO CSI Customer Products Summary Application: Install Base Source: CSI: 고객 제품 요약 보고서(XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG PTB CSI Customer Products Summary Application: Base Instalada Source: CSI: Relatório Resumido de Produtos do Cliente (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG RU CSI Customer Products Summary Application: База установок Source: CSI: сводный отчет о продуктах клиента (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG S CSI Customer Products Summary Application: Install Base Source: CSI: Kundprodukter - översiktsrapport (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG TR CSI Customer Products Summary Application: Müşteri Ürünleri Yönetimi Source: CSI: Müşteri Ürünleri Özet Raporu (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG US CSI Customer Products Summary Imported Oracle standard Installed Base Customer Products Summary report Source: CSI: Customer Products Summary Report (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG Owner Party is the owner of the instance, also for internal instances without an owner account. Parent Instance is the instance this one is an active component of. Contract Number lists the service contracts with a covered product line in status Active for the instance. Contract Coverage restricts the report to instances with or without such coverage. Show Parties adds one row per party and account role of the instance (owner, sold to, bill to, ship to, technical contact), repeating the instance columns on each row. ZHS CSI Customer Products Summary Application: 产品安装信息库 Source: CSI：客户产品汇总报表 (XML) Short Name: CSICPREP_XML DB package: CSI_CSICPREP_XMLP_PKG Enginatics &party_columns &party_from &party_order &party_where 1=1 1 10 1=1 hca.account_number=:account_number LOV AR Account Number 8E2FF36EDE7F79D2E0530100007F1FF2 select hca.account_number value, hp.party_name||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Account Number 2 20 1=1 upper(hp.party_name) like upper(:customer_name) LOV AR Customer Name 8E2FF36EDE8679D2E0530100007F1FF2 Y select hp.party_name value, hca.account_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp, hz_cust_accounts hca where hp.party_id=hca.party_id and hca.cust_account_id in ( select hcasa.cust_account_id from hr_all_organization_units_vl haouv, hz_cust_acct_sites_all hcasa where xxen_util.contains(:$flex$.operating_unit,haouv.name)='Y' and haouv.organization_id=hcasa.org_id union all select hca2.cust_account_id from hz_cust_accounts hca2 where :$flex$.operating_unit is null ) order by hp.party_name US Customer Name 3 30 1=1 cii.instance_number=:instance_number Char US Instance Number 4 40 1=1 msiv.concatenated_segments=:item LOV INV Item (master) 8E2FF36EDEF379D2E0530100007F1FF2 select msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv where msiv.organization_id=( select max(mp.master_organization_id) master_organization_id from mtl_parameters mp where mp.organization_id=fnd_profile.value('MFG_ORGANIZATION_ID') or fnd_profile.value('MFG_ORGANIZATION_ID') is null and mp.organization_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='INV') ) order by msiv.concatenated_segments US Item 5 50 1=1 cis.name=:status LOV CSI Instance Status B2523A13B47B0CC2E0530100007F9B67 select cis.name value, cis.description from csi_instance_statuses cis order by cis.name US Status Status 6 60 1=1 upper(hl.city) like upper(:city) LOV City B24FF34728CB70C2E0530100007FE8E9 select distinct initcap(hl.city) value, ftv.territory_short_name description from hz_locations hl, fnd_territories_vl ftv where trim(hl.city) is not null and hl.country=ftv.territory_code(+) and (:$flex$.country is null or xxen_util.contains(:$flex$.country,ftv.territory_short_name)='Y') order by value US City 7 70 1=1 ftv.territory_short_name=:country LOV Country 8E2FF36EDE8379D2E0530100007F1FF2 select ftv.territory_code id, ftv.territory_short_name value, ftv.territory_code||': '||ftv.description description from fnd_territories_vl ftv order by ftv.territory_short_name value US Country 8 80 1=1 cii.creation_date>=:creation_date_from Date US Creation Date From 9 90 1=1 cii.creation_date<:creation_date_to+1 Date US Creation Date To 10 100 1=1 cii.serial_number=:serial_number Char US Serial Number 11 110 1=1 ooha.order_number=:sales_order Number US Sales Order Sales order that last shipped or fulfilled the instance 12 120 1=1 cii.owner_party_id in (select hp2.party_id from hz_parties hp2 where hp2.party_name=:owner_party) LOV custom select hp.party_name value, hp.party_number||' ('||initcap(hp.party_type)||')' description from hz_parties hp where hp.party_id in (select cii.owner_party_id from csi_item_instances cii) order by hp.party_name US Owner Party 13 130 1=1 cii.install_date>=:install_date_from Date US Install Date From 14 140 1=1 cii.install_date<:install_date_to+1 Date :$flex$.install_date_from US Install Date To 15 150 1=1 to_char(cii.instance_id) in (select oki.object1_id1 from okc_k_items oki, okc_k_lines_b oklb, okc_statuses_b osb where oki.jtot_object1_code='OKX_CUSTPROD' and oki.cle_id=oklb.id and oklb.sts_code=osb.code and osb.ste_code='ACTIVE') LOV Oracle Yes_No select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y US Contract Coverage Instances with or without a service contract covered product line in status Active 16 1=1 to_char(cii.instance_id) not in (select oki.object1_id1 from okc_k_items oki, okc_k_lines_b oklb, okc_statuses_b osb where oki.jtot_object1_code='OKX_CUSTPROD' and oki.cle_id=oklb.id and oklb.sts_code=osb.code and osb.ste_code='ACTIVE' and oki.object1_id1 is not null) N US Contract Coverage 17 160 &party_columns , (select cirt.name from csi_ipa_relation_types cirt where cip.relationship_type_code=cirt.ipa_relation_type_code) party_role, xxen_util.meaning(cip.party_source_table,'CSI_PARTY_SOURCE_TABLE',542) party_type, case cip.party_source_table when 'HZ_PARTIES' then (select hp3.party_name from hz_parties hp3 where cip.party_id=hp3.party_id) when 'EMPLOYEE' then (select papf.full_name from per_all_people_f papf where cip.party_id=papf.person_id and trunc(sysdate) between papf.effective_start_date and papf.effective_end_date) when 'GROUP' then (select jrgv.group_name from jtf_rs_groups_vl jrgv where cip.party_id=jrgv.group_id) when 'TEAM' then (select jrtv.team_name from jtf_rs_teams_vl jrtv where cip.party_id=jrtv.team_id) when 'VENDOR' then (select pv.vendor_name from po_vendors pv where cip.party_id=pv.vendor_id) end role_party, (select hca2.account_number from hz_cust_accounts hca2 where cipa.party_account_id=hca2.cust_account_id) role_account_number, cip.active_start_date party_start_date, cip.active_end_date party_end_date LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US Show Parties Adds one row per party and account role of the instance 18 &party_from , csi_i_parties cip, csi_ip_accounts cipa US Show Parties 19 &party_order ,cip.relationship_type_code US Show Parties 20 &party_where and cii.instance_id=cip.instance_id(+) and nvl(cip.active_end_date(+),sysdate+1)>sysdate and cip.instance_party_id=cipa.instance_party_id(+) and nvl(cipa.active_end_date(+),sysdate+1)>sysdate US Show Parties 2C3D4E5F6A7B4C8D0E1F2A3B4C5D6E7F Installed Base Without Contract Coverage Customer installed products without an active service contract, for service contract renewal and sales follow-up. SYSADMIN 1 ACCOUNT_NUMBER 1 11 CITY 13 COUNTRY 10 INSTALL_ADDRESS1 9 INSTALL_DATE 5 INSTANCE_NUMBER 3 3 ITEM 2 4 ITEM_DESCRIPTION 14 ORDER_NUMBER 2 PARTY_NAME 8 QUANTITY 6 SERIAL_NUMBER 12 STATE 7 STATUS Contract Coverage N S Site 1B2C3D4E5F6A4B7C9D0E1F2A3B4C5D6E Installed Base by Customer Installed products per customer account and item with serial number, status, install date and location, and the active service contract. SYSADMIN 1 ACCOUNT_NUMBER 1 13 CITY 18 CONTRACT_NUMBER 15 COUNTRY 12 INSTALL_ADDRESS1 11 INSTALL_DATE 5 INSTANCE_NUMBER 3 3 ITEM 2 4 ITEM_DESCRIPTION 7 LOT_NUMBER 17 ORDER_NUMBER 16 PARENT_INSTANCE 2 PARTY_NAME 9 QUANTITY 6 SERIAL_NUMBER 14 STATE 8 STATUS 10 UNIT_OF_MEASURE S Site 0A1B2C3D4E5F4A6B8C9D0E1F2A3B4C5D Serial Number Lookup Find an installed base instance by serial number with its item, status, owner, current location, sales order and active contract. Enter the Serial Number parameter. SYSADMIN 7 ACCOUNT_NUMBER 11 CITY 15 CONTRACT_NUMBER 12 COUNTRY 9 CURRENT_LOCATION 10 INSTALL_ADDRESS1 13 INSTALL_DATE 2 INSTANCE_NUMBER 3 ITEM 4 ITEM_DESCRIPTION 8 LOCATION_TYPE 14 ORDER_NUMBER 6 OWNER_PARTY 16 PARENT_INSTANCE 1 SERIAL_NUMBER 5 STATUS S Site