2 Replies Latest reply: Dec 29, 2011 12:23 PM by rukbat RSS

    Need  help to tune this  view.

      SELECT hca.account_number AS customer_account, hp.party_name AS customer_name,
      mci.customer_item_number AS customer_part_number,
      msi.segment1 AS part_number, msi.segment2 AS supplier,
      msi.inventory_item_status_code AS supplier_part_status,
      (SELECT CASE WHEN c_ext_attr1 IN ('K', 'V') THEN c_ext_attr7
      WHEN c_ext_attr1 IN ('O', 'L', 'C') THEN c_ext_attr3
      END AS supplier_part_number
      FROM apps.ego_mtl_sy_items_ext_b emsb
      WHERE 1 = 1
      AND emsb.c_ext_attr1 IN ('K', 'V', 'O', 'L', 'C')
      AND emsb.c_ext_attr10 = 'Conversion only NE'
      AND emsb.organization_id = mcix.master_organization_id
      AND emsb.inventory_item_id = mcix.inventory_item_id)
      AS supplier_part_number,
      mcix.attribute3 AS approved_status,
      mcix.preference_number AS cpn_rank,
      CASE WHEN LENGTH (mcix.attribute1) IN (9,11) THEN TO_DATE (mcix.attribute1,'DD-MON-RRRR')
      WHEN instr(mcix.attribute1,'-') = 5 THEN TO_DATE (mcix.attribute1,'RRRR-MM-DD')
      WHEN LENGTH (mcix.attribute1) = 10 THEN TO_DATE (mcix.attribute1,'DD-MM-RRRR')
      END AS valid_from_date,
      mcix.attribute4 AS end_customer1, mcix.attribute5 AS end_customer2,
      mcix.attribute6 AS end_customer3, mcix.attribute7 AS end_customer4,
      mcix.attribute8 AS end_customer5,
      mcix.attribute9 AS end_customer1_bill_to_site,
      mcix.attribute10 AS end_customer2_bill_to_site,
      mcix.attribute11 AS end_customer3_bill_to_site,
      mcix.attribute12 AS end_customer4_bill_to_site,
      mcix.attribute13 AS end_customer5_bill_to_site,
      (select RTRIM (XMLAGG (XMLELEMENT (e, hcsua.location || '.')).EXTRACT ('//text()'), '.') as bill_to_number
      from apps.hz_cust_accounts hca1,
      apps.hz_cust_site_uses_all hcsua,
      apps.hz_cust_acct_sites_all hcasa,
      apps.hz_party_sites hps,
      apps.hz_parties hp
      where hcsua.cust_acct_site_id = hcasa.cust_acct_site_id
      AND hcasa.party_site_id = hps.party_site_id
      AND hca1.cust_account_id = hcasa.cust_account_id
      AND site_use_code = 'BILL_TO'
      AND hcasa.status = 'A'
      AND hca1.party_id = hp.party_id
      AND hcsua.primary_flag = 'Y'
      AND hca1.account_number = hca.account_number
      ) as bill_to_number
      FROM apps.mtl_customer_item_xrefs mcix,
      apps.mtl_customer_items mci,
      apps.hz_cust_accounts hca,
      apps.hz_parties hp,
      (SELECT mcix.inventory_item_id, mci.customer_id,
      MAX (mci.customer_item_id) customer_item_id
      FROM apps.mtl_customer_items mci,
      apps.mtl_customer_item_xrefs mcix
      WHERE 1 = 1
      AND mci.customer_item_id = mcix.customer_item_id
      AND master_organization_id = 81
      AND mcix.inactive_flag = 'N'
      GROUP BY mcix.inventory_item_id, mci.customer_id) mcu,
      apps.mtl_system_items_b msi
      WHERE 1 = 1
      AND mcix.customer_item_id = mci.customer_item_id
      AND mcix.master_organization_id = 81
      AND mci.customer_item_id = mcu.customer_item_id
      AND mcix.inventory_item_id = mcu.inventory_item_id
      AND mci.customer_id = hca.cust_account_id
      AND hca.party_id = hp.party_id
      AND mcix.inactive_flag = 'N'
      AND hca.status = 'A'
      AND mcix.master_organization_id = msi.organization_id --in (174,175)
      AND mcix.inventory_item_id = msi.inventory_item_id
      AND INSTR (mci.customer_item_number, '^') = 0
      AND EXISTS (
      SELECT 1
      FROM apps.hz_cust_accounts accts,
      apps.hz_cust_acct_sites_all hcas,
      apps.hr_organization_units hou
      WHERE accts.cust_account_id = hcas.cust_account_id
      AND accts.cust_account_id = hca.cust_account_id
      AND hcas.status = 'A'
      AND hou.organization_id = hcas.org_id
      AND hou.attribute1 = 'NE'
      AND ROWNUM = 1);