VIEW: APPS.OE_ITEMS_MV#

Source

View Text - Preformatted

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

View Text - HTML Formatted

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