1. Tables and Views for SCM
  2. INV_ITEM_UOMS_V

INV_ITEM_UOMS_V

Details

  • Schema: FUSION

  • Object owner: RCS

  • Object type: VIEW

Columns

Name

ORGANIZATION_ID

INVENTORY_ITEM_ID

UOM_TYPE

UOM_CODE

UNIT_OF_MEASURE

UOM_PLURAL_DESC

UOM_RECIPROCAL_DESC

DESCRIPTION

UOM_CLASS

Query

SQL_Statement

SELECT ITEMS.ORGANIZATION_ID ,

ITEMS.INVENTORY_ITEM_ID ,

ITEMS.ALLOWED_UNITS_LOOKUP_CODE AS UOM_TYPE ,

UOM.UOM_CODE ,

UOM.UNIT_OF_MEASURE ,

UOM.UOM_PLURAL_DESC,

UOM.UOM_RECIPROCAL_DESC,

UOM.DESCRIPTION ,

UOM.UOM_CLASS

FROM EGP_SYSTEM_ITEMS_VL ITEMS,

INV_UNITS_OF_MEASURE_VL UOM,

INV_UOM_CONVERSIONS STDANDINTRACONV

WHERE( (

rcs_interface.convert_is_feature_enabled_boolean_to_char('COMBO','ORA_RCS_USE_ITEM_UOMS') = 'N' AND

( ( ITEMS.ALLOWED_UNITS_LOOKUP_CODE IN (1,3)

AND STDANDINTRACONV.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID

OR ( STDANDINTRACONV.INVENTORY_ITEM_ID = 0

AND UOM.BASE_UOM_FLAG = 'Y'

AND UOM.UOM_CLASS = STDANDINTRACONV.UOM_CLASS

AND STDANDINTRACONV.UOM_CLASS IN

(SELECT UOM1.UOM_CLASS

FROM INV_UNITS_OF_MEASURE_B UOM1

WHERE UOM1.UOM_CODE = ITEMS.PRIMARY_UOM_CODE

))

OR (STDANDINTRACONV.INVENTORY_ITEM_ID = 0

AND STDANDINTRACONV.UOM_CODE IN

(SELECT INTERCLASSCONV.TO_UOM_CODE

FROM INV_UOM_CLASS_CONVERSIONS INTERCLASSCONV

WHERE INTERCLASSCONV.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID

AND (INTERCLASSCONV.DISABLE_DATE is null or INTERCLASSCONV.DISABLE_DATE>=SYSDATE)

)))

OR ( ITEMS.ALLOWED_UNITS_LOOKUP_CODE IN (2,3)

AND STDANDINTRACONV.INVENTORY_ITEM_ID = 0

AND ( STDANDINTRACONV.UOM_CLASS IN

(SELECT INTERCLASSCONV2.TO_UOM_CLASS

FROM INV_UOM_CLASS_CONVERSIONS INTERCLASSCONV2

WHERE INTERCLASSCONV2.INVENTORY_ITEM_ID = ITEMS.INVENTORY_ITEM_ID

AND (INTERCLASSCONV2.DISABLE_DATE is null or INTERCLASSCONV2.DISABLE_DATE>=SYSDATE)

)

OR STDANDINTRACONV.UOM_CLASS =

(SELECT UOM2.UOM_CLASS

FROM INV_UNITS_OF_MEASURE_B UOM2

WHERE UOM2.UOM_CODE = ITEMS.PRIMARY_UOM_CODE

) ) ) )

AND (STDANDINTRACONV.DISABLE_DATE is null or STDANDINTRACONV.DISABLE_DATE>=SYSDATE)

AND (UOM.DISABLE_DATE is null or UOM.DISABLE_DATE>=SYSDATE)

AND UOM.UOM_CODE = STDANDINTRACONV.UOM_CODE

)

)

UNION

SELECT ITEM_UOM.ORGANIZATION_ID ,

ITEM_UOM.INVENTORY_ITEM_ID ,

ITEM_UOM.UOM_TYPE ,

ITEM_UOM.UOM_CODE ,

ITEM_UOM.UNIT_OF_MEASURE ,

ITEM_UOM.UOM_PLURAL_DESC,

ITEM_UOM.UOM_RECIPROCAL_DESC,

ITEM_UOM.DESCRIPTION ,

ITEM_UOM.UOM_CLASS FROM INV_ITEM_UOMS_EXPLOSION_V ITEM_UOM