eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
MATERIALIZED VIEW: APPS.OE_ITEMS_MV

Source


SELECT 
    citems.customer_item_number ITEM
  , citems.customer_item_id ITEM_ID 
  , nvl(citems.customer_item_desc, T.description) ITEM_DESCRIPTION
  , B.inventory_item_id INVENTORY_ITEM_ID
  , CAST(B.concatenated_segments as VARCHAR2(1000)) INVENTORY_ITEM
  , 'CUST' ITEM_IDENTIFIER_TYPE
  , citems.customer_id SOLD_TO_ORG_ID
  , B.organization_id ORGANIZATION_ID
  , decode(citems.inactive_flag,'Y','INACTIVE','N','ACTIVE')   ITEM_STATUS 
  , decode(cxref.inactive_flag ,'Y', 'INACTIVE' ,'N','ACTIVE')  CROSS_REF_STATUS
  , LOC.ADDRESS1 || LOC.ADDRESS2 Address
  , decode(flv.lookup_code,3, LOC.CITY || loc.state || loc.postal_code,2,arl.meaning,1,null) CUST_ADDRESS
  , flv.meaning ITEM_DEFINITION_LEVEL
  , cxref.preference_number RANK
  , SITE.CUST_ACCT_SITE_ID SITE_USE_ID
  , flv.lookup_code ITEM_DEFINITION_LEVEL_CODE
  , T.Language LANGUAGE
  , IC.CATEGORY_ID
  , 'Y' ORG_INDEPENDENT_FLAG
  , B.organization_id CROSS_REF_ORG_ID
FROM
    mtl_customer_items citems
  , mtl_customer_item_xrefs cxref
  , MTL_SYSTEM_ITEMS_TL T 
  , MTL_SYSTEM_ITEMS_B_KFV B 
  , HZ_CUST_SITE_USES_ALL      SITE
  , HZ_PARTY_SITES             PARTY_SITE
  , HZ_LOCATIONS               LOC
  , HZ_CUST_ACCT_SITES_ALL     ACCT_SITE
  , AR_LOOKUPS ARL
  , fnd_lookup_values_vl flv
  , mtl_parameters mp
  , MTL_ITEM_CATEGORIES IC
  , MTL_DEFAULT_CATEGORY_SETS CS
WHERE 
    NVL(SITE.status, 'A')  = 'A' 
    AND NVL(SITE.site_use_code, 'SHIP_TO') = 'SHIP_TO'
    AND SITE.cust_acct_site_id (+) = citems.address_id    
    AND SITE.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID (+) 
    AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID (+)
    AND PARTY_SITE.LOCATION_ID = LOC.LOCATION_ID (+)
    AND citems.customer_item_id = cxref.customer_item_id 
    AND cxref.inventory_item_id = B.inventory_item_id
    AND B.customer_order_enabled_flag = 'Y' 
    AND B.bom_item_type in (1,4)
    AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID 
    AND B.ORGANIZATION_ID = T.ORGANIZATION_ID 
    AND citems.CUSTOMER_CATEGORY_CODE = ARL.LOOKUP_CODE(+) 
    AND ARL.LOOKUP_TYPE(+) = 'ADDRESS_CATEGORY'
    AND flv.lookup_type(+) = 'INV_ITEM_DEFINITION_LEVEL'
    AND citems.item_definition_level = flv.lookup_code(+)
    AND mp.organization_id = B.organization_id 
    AND cxref.master_organization_id = mp.master_organization_id 
    AND IC.ORGANIZATION_ID = B.organization_id
    AND IC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
    AND IC.CATEGORY_SET_ID = CS.CATEGORY_SET_ID
    AND CS.functional_area_id = 7
UNION ALL
SELECT
    CAST(B.concatenated_segments as VARCHAR2(1000)) ITEM   
  , B.inventory_item_id  ITEM_ID
  , T.description ITEM_DESCRIPTION
  , B.inventory_item_id INVENTORY_ITEM_ID
  , CAST(B.concatenated_segments as VARCHAR2(1000)) INVENTORY_ITEM   
  , 'INT' ITEM_IDENTIFIER_TYPE
  , cast(NULL as NUMBER) SOLD_TO_ORG_ID
  , B.organization_id ORGANIZATION_ID
  , cast(NULL as VARCHAR2(10)) ITEM_STATUS
  , cast(NULL as VARCHAR2(10)) CROSS_REF_STATUS
  , cast(NULL as VARCHAR2(1000)) ADDRESS
  , cast(NULL as VARCHAR2(1000)) CUST_ADDRESS
  , cast(NULL as VARCHAR2(100)) ITEM_DEFINITION_LEVEL
  , cast(NULL as NUMBER) RANK
  , cast(NULL as NUMBER) SITE_USE_ID
  , cast(NULL as VARCHAR2(100)) ITEM_DEFINITION_LEVEL_CODE
  , T.Language LANGUAGE
  , IC.CATEGORY_ID
  , 'Y' ORG_INDEPENDENT_FLAG
  , B.organization_id CROSS_REF_ORG_ID
FROM
     MTL_SYSTEM_ITEMS_TL T , 
     MTL_SYSTEM_ITEMS_B_KFV B,
     MTL_ITEM_CATEGORIES IC,
     MTL_DEFAULT_CATEGORY_SETS CS
WHERE
     B.customer_order_enabled_flag = 'Y'
     AND bom_item_type in (1,4)
     AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID 
     AND B.ORGANIZATION_ID = T.ORGANIZATION_ID 
     AND IC.ORGANIZATION_ID = B.organization_id
    AND IC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
    AND IC.CATEGORY_SET_ID = CS.CATEGORY_SET_ID
    AND CS.functional_area_id = 7
UNION ALL
SELECT 
    items.cross_reference ITEM
  , cast(NULL as NUMBER) ITEM_ID
  , nvl(items_tl.description, T.description) ITEM_DESCRIPTION
  , B.inventory_item_id INVENTORY_ITEM_ID
  , CAST(B.concatenated_segments as VARCHAR2(1000)) INVENTORY_ITEM
  , items.cross_reference_type ITEM_IDENTIFIER_TYPE
  , cast(NULL as NUMBER) SOLD_TO_ORG_ID
  , B.organization_id ORGANIZATION_ID
  , cast(NULL as VARCHAR2(10)) ITEM_STATUS
  , decode(sign(nvl(types.disable_date,sysdate)- sysdate),-1,'INACTIVE','ACTIVE') CROSS_REF_STATUS
  , cast(NULL as VARCHAR2(1000)) ADDRESS
  , cast(NULL as VARCHAR2(1000)) CUST_ADDRESS
  , cast(NULL as VARCHAR2(100)) ITEM_DEFINITION_LEVEL
  , cast(NULL as NUMBER) RANK
  , cast(NULL as NUMBER) SITE_USE_ID
  , cast(NULL as VARCHAR2(100)) ITEM_DEFINITION_LEVEL_CODE
  , T.Language LANGUAGE
  , IC.CATEGORY_ID
  , ITEMS.ORG_INDEPENDENT_FLAG
  , ITEMS.ORGANIZATION_ID CROSS_REF_ORG_ID
FROM
    mtl_cross_reference_types types
  , mtl_cross_references_B items
  , mtl_cross_references_TL items_tl
  , MTL_SYSTEM_ITEMS_B_KFV B 
  , MTL_SYSTEM_ITEMS_TL T 
  , MTL_ITEM_CATEGORIES IC
  , MTL_DEFAULT_CATEGORY_SETS CS
WHERE   
    items.cross_reference_type = types.cross_reference_type 
    AND items_tl.CROSS_REFERENCE_ID = items.CROSS_REFERENCE_ID
    AND b.inventory_item_id = items.inventory_item_id
    AND B.customer_order_enabled_flag = 'Y'
    and b.bom_item_type in (1,4)  
    AND T.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID 
    AND T.ORGANIZATION_ID = B.ORGANIZATION_ID 
    AND T.language = items_tl.language
    AND IC.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
    AND IC.ORGANIZATION_ID = B.organization_id
    AND IC.CATEGORY_SET_ID = CS.CATEGORY_SET_ID
    AND CS.functional_area_id = 7