View: BOM_IMPLOSIONS_V

Product:	BOM - Bills of Material
Description:	View for displaying BOM implosions
Implementation/DBA Data:	APPS.BOM_IMPLOSIONS_V

View Text

SELECT /*+ LEADING(T) */ 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
, MSIVL_PARENT.CONCATENATED_SEGMENTS AS CONCATENATED_SEGMENTS
, MSIVL_PARENT.DESCRIPTION AS DESCRIPTION
, MSIVL_PARENT.PRIMARY_UOM_CODE AS PRIMARY_UOM_CODE
, FL_ITEM_TYPE.MEANING AS LOOKUP_TYPE_MEANING
, T.ALTERNATE_DESIGNATOR
, T.OPERATION_SEQ_NUM
, MIRB_CURRENT.REVISION AS CURRENT_REVISION
, ML_BASIS_TYPE.MEANING AS BASIS_TYPE
, T.COMPONENT_QUANTITY
, T.CURRENT_ASSEMBLY_TYPE
, T.CHANGE_NOTICE
, T.REVISED_ITEM_SEQUENCE_ID
, T.IMPLEMENTED_FLAG
, MSIVL_PARENT.INVENTORY_ITEM_STATUS_CODE AS INVENTORY_ITEM_STATUS_CODE
, DECODE ( T.EFFECTIVITY_CONTROL
, 4
, ( SELECT MIRB.REVISION_LABEL
FROM MTL_ITEM_REVISIONS_B MIRB
WHERE MIRB.REVISION = T.FROM_END_ITEM_REVISION
AND MIRB.INVENTORY_ITEM_ID = T.PARENT_ITEM_ID
AND MIRB.ORGANIZATION_ID = T.ORGANIZATION_ID )
, 2
, T.FROM_END_ITEM_UNIT_NUMBER
, 3
, T.FROM_END_ITEM_UNIT_NUMBER
, TO_CHAR ( T.EFFECTIVITY_DATE
, 'DD-MON-YYYY HH24:MI:SS' ) ) COMPONENT_EFFECTIVE_FROM
, DECODE ( T.EFFECTIVITY_CONTROL
, 4
, DECODE ( T.TO_END_ITEM_REVISION
, NULL
, NULL
, ( SELECT MIRB.REVISION_LABEL
FROM MTL_ITEM_REVISIONS_B MIRB
WHERE MIRB.REVISION = T.TO_END_ITEM_REVISION
AND MIRB.INVENTORY_ITEM_ID = T.PARENT_ITEM_ID
AND MIRB.ORGANIZATION_ID = T.ORGANIZATION_ID ) )
, 2
, T.TO_END_ITEM_UNIT_NUMBER
, 3
, T.TO_END_ITEM_UNIT_NUMBER
, TO_CHAR ( T.DISABLE_DATE
, 'DD-MON-YYYY HH24:MI:SS' ) ) COMPONENT_EFFECTIVE_TO
, ML_EFFECTIVITY_CONTROL.MEANING
, DECODE(T.DISABLE_DATE
, NULL
, 2
, 1) DISABLED_FLAG
, HOU.NAME ORG_NAME
, MSIVL_CURRENT.CONCATENATED_SEGMENTS AS CURRENT_CONCATENATED_SEGMENTS
, MSIVL_CURRENT.DESCRIPTION AS CURRENT_DESCRIPTION
, CASE WHEN ( ( T.CURRENT_LEVEL > 0 )
AND ( T.COMPONENT_ITEM_REVISION_ID IS NOT NULL ) ) THEN ( SELECT MIR.REVISION_LABEL
FROM MTL_ITEM_REVISIONS_B MIR
WHERE MIR.REVISION_ID = T.COMPONENT_ITEM_REVISION_ID ) END AS REVISION_LABEL
, CASE WHEN ( T.CURRENT_LEVEL > 0 ) THEN ( BSTYPE_VL.STRUCTURE_TYPE_NAME ) END AS STRUCTURE_TYPE_NAME
, /*MICG_KFV.CONCATENATED_SEGMENTS AS CATALOG_CATEGORY
, */ FND_FLEX_SERVER.GET_KFV_CONCAT_SEGS_BY_CCID('COMPACT'
, 401
, 'MICG'
, 101
, MICG.ITEM_CATALOG_GROUP_ID
, NULL) AS CATALOG_CATEGORY
, ENG.CHANGE_ID
, T.PARENT_SORT_CODE
, NVL ( MIRB_IMPL.LIFECYCLE_ID
, MSIVL_PARENT.LIFECYCLE_ID ) AS LIFECYCLE_ID
, NVL ( MIRB_IMPL.CURRENT_PHASE_ID
, MSIVL_PARENT.CURRENT_PHASE_ID ) AS CURRENT_PHASE_ID
, T.TOP_ITEM_FLAG
, T.PARENT_PK1_VALUE
, T.PARENT_PK2_VALUE
, T.PARENT_OBJ_NAME
, T.CURRENT_PK1_VALUE
, T.CURRENT_PK2_VALUE
, MIRB_CURRENT.REVISION_ID AS REVISION_ID
, EGI.GTIN AS PARENT_GTIN_NUMBER
, EGI.DESCRIPTION AS PARENT_GTIN_DESCRIPTION
, EGI.TRADE_ITEM_DESCRIPTOR_DESC AS PARENT_TRADE_ITEM_DESCRIPTOR
, T.COMPONENT_SEQUENCE_ID
, T.LAST_UPDATE_DATE
, T.LAST_UPDATED_BY
, T.CREATION_DATE
, T.CREATED_BY
, T.LAST_UPDATE_LOGIN
, CASE WHEN ( T.CURRENT_LEVEL > 0 ) THEN ( T.STRUCTURE_TYPE_ID ) END AS STRUCTURE_TYPE_ID
, CASE WHEN ( T.CURRENT_LEVEL > 0 ) THEN ( BSTYPE_VL.DISPLAY_NAME ) END AS STRUCTURE_TYPE_DISP_NAME
, EGI.PRIMARY_UOM_CODE_DESC AS PRIMARY_UOM_DESCRIPTOR
, DECODE ( T.ALTERNATE_DESIGNATOR
, NULL
, BOM_GLOBALS.RETRIEVE_MESSAGE ( 'BOM'
, 'BOM_PRIMARY' )
, ( SELECT BAD_TL.DISPLAY_NAME
FROM BOM_ALTERNATE_DESIGNATORS_TL BAD_TL
WHERE BAD_TL.LANGUAGE = USERENV('LANG')
AND BAD_TL.ALTERNATE_DESIGNATOR_CODE = T.ALTERNATE_DESIGNATOR
AND BAD_TL.ORGANIZATION_ID = T.ORGANIZATION_ID ) )
, EGI.PUBLICATION_STATUS AS GTIN_PUBLICATION_STATUS
, T.ACCESS_FLAG
, MIRB_IMPL.REVISION AS REVISION
, MIRB_IMPL.REVISION_LABEL AS COMP_REVISION_ON_IMPL_DATE
, T.IS_EXCLUDED_BY_RULE IS_EXCLUDED_BY_RULE
, MIRB_IMPL.REVISION AS COMP_REVCODE_ON_IMPL_DATE
, CASE WHEN ( ( T.CURRENT_LEVEL > 0 )
AND ( T.COMPONENT_ITEM_REVISION_ID IS NOT NULL ) ) THEN ( SELECT MIR.REVISION
FROM MTL_ITEM_REVISIONS_B MIR
WHERE MIR.REVISION_ID = T.COMPONENT_ITEM_REVISION_ID ) END AS REVISION_CODE
FROM BOM_SMALL_IMPL_TEMP T
, MTL_PARAMETERS MP
, MTL_SYSTEM_ITEMS_VL MSIVL_PARENT
, MTL_SYSTEM_ITEMS_VL MSIVL_CURRENT
, FND_LOOKUP_VALUES FL_ITEM_TYPE
, MFG_LOOKUPS ML_BASIS_TYPE
, MFG_LOOKUPS ML_EFFECTIVITY_CONTROL
, HR_ORGANIZATION_UNITS HOU
, BOM_STRUCTURE_TYPES_VL BSTYPE_VL
, MTL_ITEM_CATALOG_GROUPS MICG
, ENG_ENGINEERING_CHANGES ENG
, MTL_ITEM_REVISIONS_B MIRB_IMPL
, MTL_ITEM_REVISIONS_B MIRB_CURRENT
, EGO_ITEMS_V EGI
WHERE T.ORGANIZATION_ID = MP.ORGANIZATION_ID
AND MSIVL_PARENT.INVENTORY_ITEM_ID = T.PARENT_PK1_VALUE
AND MSIVL_PARENT.ORGANIZATION_ID = T.PARENT_PK2_VALUE
AND MSIVL_PARENT.ITEM_TYPE = FL_ITEM_TYPE.LOOKUP_CODE(+)
AND FL_ITEM_TYPE.LOOKUP_TYPE(+) = 'ITEM_TYPE'
AND FL_ITEM_TYPE.LANGUAGE(+) = USERENV('LANG')
AND FL_ITEM_TYPE.VIEW_APPLICATION_ID(+) = 3
AND ( ( FL_ITEM_TYPE.LOOKUP_CODE IS NULL ) OR ( FL_ITEM_TYPE.SECURITY_GROUP_ID = FND_GLOBAL.LOOKUP_SECURITY_GROUP(FL_ITEM_TYPE.LOOKUP_TYPE
, FL_ITEM_TYPE.VIEW_APPLICATION_ID) ) )
AND ML_BASIS_TYPE.LOOKUP_TYPE (+) ='BOM_BASIS_TYPE'
AND ML_BASIS_TYPE.LOOKUP_CODE (+) = NVL(T.BASIS_TYPE
, 1)
AND ( ( ( T.EFFECTIVITY_CONTROL IS NULL )
AND ( ML_EFFECTIVITY_CONTROL.LOOKUP_CODE = 1 ) ) OR ( ML_EFFECTIVITY_CONTROL.LOOKUP_CODE = T.EFFECTIVITY_CONTROL ) )
AND ML_EFFECTIVITY_CONTROL.LOOKUP_TYPE = 'BOM_EFFECTIVITY_CONTROL'
AND HOU.ORGANIZATION_ID = T.ORGANIZATION_ID
AND MSIVL_CURRENT.INVENTORY_ITEM_ID = T.CURRENT_PK1_VALUE
AND MSIVL_CURRENT.ORGANIZATION_ID = T.CURRENT_PK2_VALUE
AND T.STRUCTURE_TYPE_ID = BSTYPE_VL.STRUCTURE_TYPE_ID(+)
AND MSIVL_PARENT.ITEM_CATALOG_GROUP_ID = MICG.ITEM_CATALOG_GROUP_ID(+)
AND T.CHANGE_NOTICE = ENG.CHANGE_NOTICE(+)
AND T.ORGANIZATION_ID = ENG.ORGANIZATION_ID(+)
AND MIRB_IMPL.EFFECTIVITY_DATE = ( SELECT MAX ( MIR1.EFFECTIVITY_DATE )
FROM MTL_ITEM_REVISIONS_B MIR1
WHERE MIR1.EFFECTIVITY_DATE <= T.IMPLOSION_DATE
AND MIR1.INVENTORY_ITEM_ID = T.PARENT_PK1_VALUE
AND MIR1.ORGANIZATION_ID = T.PARENT_PK2_VALUE )
AND MIRB_IMPL.INVENTORY_ITEM_ID = T.PARENT_PK1_VALUE
AND MIRB_IMPL.ORGANIZATION_ID = T.PARENT_PK2_VALUE
AND MIRB_CURRENT.EFFECTIVITY_DATE = ( SELECT MAX ( MIR2.EFFECTIVITY_DATE )
FROM MTL_ITEM_REVISIONS_B MIR2
WHERE MIR2.EFFECTIVITY_DATE <= SYSDATE
AND MIR2.IMPLEMENTATION_DATE IS NOT NULL
AND MIR2.INVENTORY_ITEM_ID = T.PARENT_PK1_VALUE
AND MIR2.ORGANIZATION_ID = T.PARENT_PK2_VALUE )
AND MIRB_CURRENT.INVENTORY_ITEM_ID = T.PARENT_PK1_VALUE
AND MIRB_CURRENT.ORGANIZATION_ID = T.PARENT_PK2_VALUE
AND EGI.INVENTORY_ITEM_ID = T.PARENT_PK1_VALUE
AND EGI.ORGANIZATION_ID = T.PARENT_PK2_VALUE

Columns