This discussion is archived
2 Replies Latest reply: Dec 29, 2011 10:23 AM by rukbat RSS

Need  help to tune this  view.

907814 Newbie
Currently Being Moderated
CREATE OR REPLACE FORCE VIEW "APPS"."XXX_QS_CPN" ("CUSTOMER_ACCOUNT", "CUSTOMER_NAME", "CUSTOMER_PART_NUMBER", "PART_NUMBER", "SUPPLIER", "INVENTORY_ITEM_ID", "SUPPLIER_PART_STATUS", "SUPPLIER_PART_NUMBER", "APPROVED_STATUS", "CPN_RANK", "VALID_FROM_DATE", "END_CUSTOMER1", "END_CUSTOMER2", "END_CUSTOMER3", "END_CUSTOMER4", "END_CUSTOMER5", "END_CUSTOMER1_BILL_TO_SITE", "END_CUSTOMER2_BILL_TO_SITE", "END_CUSTOMER3_BILL_TO_SITE", "END_CUSTOMER4_BILL_TO_SITE", "END_CUSTOMER5_BILL_TO_SITE", "BILL_TO_NUMBER") AS
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_id,
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
ELSE NULL
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')
ELSE TRUNC (SYSDATE)
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);

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points