View: BOM_IMPL_INQUIRY_V

Product:	BOM - Bills of Material
Description:	Item usage information
Implementation/DBA Data:	APPS.BOM_IMPL_INQUIRY_V

View Text

SELECT /*+ LEADING(T) USE_NL(T R F M) INDEX(R MTL_ITEM_REVISIONS_N1) NO_EXPAND */ T.ROWID
, MP.ORGANIZATION_CODE
, T.SEQUENCE_ID
, T.LOWEST_ITEM_ID
, T.CURRENT_ITEM_ID
, T.SORT_CODE
, T.CURRENT_LEVEL
, T.PARENT_ITEM_ID
, T.ORGANIZATION_ID
, BOM_GLOBALS.GET_CONCAT_SEGS(F.INVENTORY_ITEM_ID
, F.ORGANIZATION_ID)
, INV_MEANING_SEL.C_ITEM_DESCRIPTION(F.INVENTORY_ITEM_ID
, F.ORGANIZATION_ID)
, M.PRIMARY_UOM_CODE
, LU.MEANING ITEM_TYPE
, T.ALTERNATE_DESIGNATOR
, T.OPERATION_SEQ_NUM
, MIR.REVISION
, LU2.MEANING BASIS_TYPE
, T.COMPONENT_QUANTITY
, T.CURRENT_ASSEMBLY_TYPE
, T.CHANGE_NOTICE
, T.REVISED_ITEM_SEQUENCE_ID
, T.IMPLEMENTED_FLAG
, F.INVENTORY_ITEM_STATUS_CODE
, T.FROM_END_ITEM_UNIT_NUMBER
, T.TO_END_ITEM_UNIT_NUMBER
, ML2.MEANING
, DECODE(T.FROM_END_ITEM_UNIT_NUMBER
, NULL
, 2
, DECODE(T.DISABLE_DATE
, NULL
, 2
, 1)) DISABLED_FLAG
, HOU.NAME ORG_NAME
, BOM_GLOBALS.GET_CONCAT_SEGS(M.INVENTORY_ITEM_ID
, M.ORGANIZATION_ID)
, MIR.REVISION_LABEL
, T.PARENT_SORT_CODE
, MIR.LIFECYCLE_ID
, MIR.CURRENT_PHASE_ID
, DECODE(T.FROM_END_ITEM_UNIT_NUMBER
, NULL
, T.EFFECTIVITY_DATE
, NULL) COMPONENT_EFFECTIVE_FROM_DATE
, DECODE(T.FROM_END_ITEM_UNIT_NUMBER
, NULL
, T.DISABLE_DATE
, NULL) COMPONENT_EFFECTIVE_TO_DATE
FROM MTL_SYSTEM_ITEMS_B_KFV F
, MTL_SYSTEM_ITEMS_B_KFV M
, MFG_LOOKUPS ML2
, FND_LOOKUP_VALUES LU
, BOM_SMALL_IMPL_TEMP T
, MTL_PARAMETERS MP
, HR_ORGANIZATION_UNITS HOU
, MTL_ITEM_REVISIONS_B MIR
, MFG_LOOKUPS LU2
WHERE T.PARENT_ITEM_ID = F.INVENTORY_ITEM_ID
AND T.ORGANIZATION_ID = F.ORGANIZATION_ID
AND T.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND ((T.FROM_END_ITEM_UNIT_NUMBER IS NULL
AND ML2.LOOKUP_CODE = 1) OR (T.FROM_END_ITEM_UNIT_NUMBER IS NOT NULL
AND ML2.LOOKUP_CODE = 2))
AND ML2.LOOKUP_TYPE (+) = 'MTL_EFFECTIVITY_CONTROL'
AND M.INVENTORY_ITEM_ID = T.CURRENT_ITEM_ID
AND M.ORGANIZATION_ID = T.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = T.ORGANIZATION_ID
AND F.ITEM_TYPE = LU.LOOKUP_CODE(+)
AND LU.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND LU.LANGUAGE(+) = USERENV('LANG')
AND LU.VIEW_APPLICATION_ID(+) = 3
AND ( LU.LOOKUP_CODE IS NULL OR LU.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(LU.LOOKUP_TYPE
, LU.VIEW_APPLICATION_ID))
AND MIR.INVENTORY_ITEM_ID = T.PARENT_ITEM_ID
AND MIR.ORGANIZATION_ID = T.ORGANIZATION_ID
AND MIR.EFFECTIVITY_DATE =(SELECT MAX(MIR1.EFFECTIVITY_DATE)
FROM MTL_ITEM_REVISIONS_B MIR1
WHERE MIR1.INVENTORY_ITEM_ID =T.PARENT_ITEM_ID
AND MIR1.ORGANIZATION_ID = T.ORGANIZATION_ID
AND MIR1.EFFECTIVITY_DATE <=T.IMPLOSION_DATE
AND ((T.IMPLEMENTED_FLAG =1
AND MIR1.IMPLEMENTATION_DATE IS NOT NULL) OR(T.IMPLEMENTED_FLAG=2)))
AND LU2.LOOKUP_TYPE (+) = 'BOM_BASIS_TYPE'
AND LU2.LOOKUP_CODE = NVL (T.BASIS_TYPE
, 1)

Columns