VIEW: APPS.OE_ITEMS_V

Source

View Text - Preformatted

SELECT citems.customer_item_number ITEM , citems.customer_item_id ITEM_ID , citems.customer_item_desc ITEM_DESCRIPTION , B.inventory_item_id INVENTORY_ITEM_ID , B.concatenated_segments 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 FROM mtl_customer_items citems , mtl_customer_item_xrefs cxref , 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 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 citems.customer_item_desc IS NOT NULL AND cxref.inventory_item_id = B.inventory_item_id AND B.customer_order_enabled_flag = 'Y' AND B.organization_id = oe_sys_parameters.Value('MASTER_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 UNION ALL SELECT citems.customer_item_number ITEM , citems.customer_item_id ITEM_ID , T.description ITEM_DESCRIPTION , B.inventory_item_id INVENTORY_ITEM_ID , B.concatenated_segments 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 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 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 citems.customer_item_desc IS NULL AND cxref.inventory_item_id = B.inventory_item_id AND B.customer_order_enabled_flag = 'Y' AND B.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') AND T.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = T.ORGANIZATION_ID AND T.LANGUAGE = userenv('LANG') 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 UNION ALL SELECT B.concatenated_segments ITEM , B.inventory_item_id ITEM_ID , T.description ITEM_DESCRIPTION , B.inventory_item_id INVENTORY_ITEM_ID , B.concatenated_segments INVENTORY_ITEM , 'INT' ITEM_IDENTIFIER_TYPE , to_number(null) SOLD_TO_ORG_ID , B.organization_id ORGANIZATION_ID , NULL ITEM_STATUS , NULL CROSS_REF_STATUS , NULL ADDRESS , NULL CUST_ADDRESS , NULL ITEM_DEFINITION_LEVEL , NULL RANK FROM MTL_SYSTEM_ITEMS_TL T , MTL_SYSTEM_ITEMS_B_KFV B WHERE B.customer_order_enabled_flag = 'Y' AND bom_item_type in (1,4) AND B.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') AND T.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = T.ORGANIZATION_ID AND T.LANGUAGE = userenv('LANG') UNION ALL SELECT items.cross_reference ITEM , to_number(null) ITEM_ID , items.description ITEM_DESCRIPTION , B.inventory_item_id INVENTORY_ITEM_ID , B.concatenated_segments INVENTORY_ITEM , items.cross_reference_type ITEM_IDENTIFIER_TYPE , to_number(null) SOLD_TO_ORG_ID , B.organization_id ORGANIZATION_ID , NULL ITEM_STATUS , decode(sign(nvl(types.disable_date,sysdate)- sysdate),-1,'INACTIVE','ACTIVE') CROSS_REF_STATUS , NULL ADDRESS , NULL CUST_ADDRESS , NULL ITEM_DEFINITION_LEVEL , NULL RANK FROM mtl_cross_reference_types types , mtl_cross_references items , MTL_SYSTEM_ITEMS_B_KFV B WHERE types.cross_reference_type = items.cross_reference_type AND items.description IS NOT NULL AND items.inventory_item_id = B.inventory_item_id AND B.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') AND B.customer_order_enabled_flag = 'Y' AND ( items.org_independent_flag = 'Y' OR items.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') ) UNION ALL SELECT items.cross_reference ITEM , to_number(null) ITEM_ID , T.description ITEM_DESCRIPTION , B.inventory_item_id INVENTORY_ITEM_ID , B.concatenated_segments INVENTORY_ITEM , items.cross_reference_type ITEM_IDENTIFIER_TYPE , to_number(null) SOLD_TO_ORG_ID , B.organization_id ORGANIZATION_ID , NULL ITEM_STATUS , decode(sign(nvl(types.disable_date,sysdate)- sysdate),-1,'INACTIVE','ACTIVE') CROSS_REF_STATUS , NULL ADDRESS , NULL CUST_ADDRESS , NULL ITEM_DEFINITION_LEVEL , NULL RANK FROM mtl_cross_reference_types types , mtl_cross_references items , MTL_SYSTEM_ITEMS_TL T , MTL_SYSTEM_ITEMS_B_KFV B WHERE types.cross_reference_type = items.cross_reference_type AND items.inventory_item_id = B.inventory_item_id AND items.description IS NULL AND B.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') AND T.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = T.ORGANIZATION_ID AND T.LANGUAGE = userenv('LANG') AND B.customer_order_enabled_flag = 'Y' AND ( items.org_independent_flag = 'Y' OR items.organization_id = oe_sys_parameters.Value('MASTER_ORGANIZATION_ID') )

View Text - HTML Formatted

SELECT CITEMS.CUSTOMER_ITEM_NUMBER ITEM
, CITEMS.CUSTOMER_ITEM_ID ITEM_ID
, CITEMS.CUSTOMER_ITEM_DESC ITEM_DESCRIPTION
, B.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS 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
FROM MTL_CUSTOMER_ITEMS CITEMS
, MTL_CUSTOMER_ITEM_XREFS CXREF
, 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
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 CITEMS.CUSTOMER_ITEM_DESC IS NOT NULL
AND CXREF.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND B.CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
AND B.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_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 UNION ALL SELECT CITEMS.CUSTOMER_ITEM_NUMBER ITEM
, CITEMS.CUSTOMER_ITEM_ID ITEM_ID
, T.DESCRIPTION ITEM_DESCRIPTION
, B.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS 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
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
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 CITEMS.CUSTOMER_ITEM_DESC IS NULL
AND CXREF.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND B.CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
AND B.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
AND T.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND T.LANGUAGE = USERENV('LANG')
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 UNION ALL SELECT B.CONCATENATED_SEGMENTS ITEM
, B.INVENTORY_ITEM_ID ITEM_ID
, T.DESCRIPTION ITEM_DESCRIPTION
, B.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS INVENTORY_ITEM
, 'INT' ITEM_IDENTIFIER_TYPE
, TO_NUMBER(NULL) SOLD_TO_ORG_ID
, B.ORGANIZATION_ID ORGANIZATION_ID
, NULL ITEM_STATUS
, NULL CROSS_REF_STATUS
, NULL ADDRESS
, NULL CUST_ADDRESS
, NULL ITEM_DEFINITION_LEVEL
, NULL RANK
FROM MTL_SYSTEM_ITEMS_TL T
, MTL_SYSTEM_ITEMS_B_KFV B
WHERE B.CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
AND BOM_ITEM_TYPE IN (1
, 4)
AND B.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
AND T.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND T.LANGUAGE = USERENV('LANG') UNION ALL SELECT ITEMS.CROSS_REFERENCE ITEM
, TO_NUMBER(NULL) ITEM_ID
, ITEMS.DESCRIPTION ITEM_DESCRIPTION
, B.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS INVENTORY_ITEM
, ITEMS.CROSS_REFERENCE_TYPE ITEM_IDENTIFIER_TYPE
, TO_NUMBER(NULL) SOLD_TO_ORG_ID
, B.ORGANIZATION_ID ORGANIZATION_ID
, NULL ITEM_STATUS
, DECODE(SIGN(NVL(TYPES.DISABLE_DATE
, SYSDATE)- SYSDATE)
, -1
, 'INACTIVE'
, 'ACTIVE') CROSS_REF_STATUS
, NULL ADDRESS
, NULL CUST_ADDRESS
, NULL ITEM_DEFINITION_LEVEL
, NULL RANK
FROM MTL_CROSS_REFERENCE_TYPES TYPES
, MTL_CROSS_REFERENCES ITEMS
, MTL_SYSTEM_ITEMS_B_KFV B
WHERE TYPES.CROSS_REFERENCE_TYPE = ITEMS.CROSS_REFERENCE_TYPE
AND ITEMS.DESCRIPTION IS NOT NULL
AND ITEMS.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
AND B.CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
AND ( ITEMS.ORG_INDEPENDENT_FLAG = 'Y' OR ITEMS.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID') ) UNION ALL SELECT ITEMS.CROSS_REFERENCE ITEM
, TO_NUMBER(NULL) ITEM_ID
, T.DESCRIPTION ITEM_DESCRIPTION
, B.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, B.CONCATENATED_SEGMENTS INVENTORY_ITEM
, ITEMS.CROSS_REFERENCE_TYPE ITEM_IDENTIFIER_TYPE
, TO_NUMBER(NULL) SOLD_TO_ORG_ID
, B.ORGANIZATION_ID ORGANIZATION_ID
, NULL ITEM_STATUS
, DECODE(SIGN(NVL(TYPES.DISABLE_DATE
, SYSDATE)- SYSDATE)
, -1
, 'INACTIVE'
, 'ACTIVE') CROSS_REF_STATUS
, NULL ADDRESS
, NULL CUST_ADDRESS
, NULL ITEM_DEFINITION_LEVEL
, NULL RANK
FROM MTL_CROSS_REFERENCE_TYPES TYPES
, MTL_CROSS_REFERENCES ITEMS
, MTL_SYSTEM_ITEMS_TL T
, MTL_SYSTEM_ITEMS_B_KFV B
WHERE TYPES.CROSS_REFERENCE_TYPE = ITEMS.CROSS_REFERENCE_TYPE
AND ITEMS.INVENTORY_ITEM_ID = B.INVENTORY_ITEM_ID
AND ITEMS.DESCRIPTION IS NULL
AND B.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
AND T.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID')
AND B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID
AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
AND T.LANGUAGE = USERENV('LANG')
AND B.CUSTOMER_ORDER_ENABLED_FLAG = 'Y'
AND ( ITEMS.ORG_INDEPENDENT_FLAG = 'Y' OR ITEMS.ORGANIZATION_ID = OE_SYS_PARAMETERS.VALUE('MASTER_ORGANIZATION_ID') )