eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.BOM_IMPLOSIONS_V

Source

View Text - Preformatted


SELECT 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, 1 AS 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

View Text - HTML Formatted

SELECT 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
, 1 AS 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