View: BOM_EXPLOSIONS_ALL_V

Product:	BOM - Bills of Material
Description:	View over BOM_EXPLOSIONS_ALL Table
Implementation/DBA Data:	APPS.BOM_EXPLOSIONS_ALL_V

View Text

SELECT BE.TOP_BILL_SEQUENCE_ID
, BE.BILL_SEQUENCE_ID
, BE.ORGANIZATION_ID
, BE.EXPLOSION_TYPE
, BE.COMPONENT_SEQUENCE_ID
, NVL(BE.COMPONENT_ITEM_ID
, -1) COMPONENT_ITEM_ID
, BE.PLAN_LEVEL
, BE.EXTENDED_QUANTITY
, BE.SORT_ORDER
, BE.CREATION_DATE
, BE.CREATED_BY
, BE.LAST_UPDATE_DATE
, BE.LAST_UPDATED_BY
, BE.TOP_ITEM_ID
, BIC.ATTRIBUTE_CATEGORY CONTEXT
, BIC.ATTRIBUTE1
, BIC.ATTRIBUTE2
, BIC.ATTRIBUTE3
, BIC.ATTRIBUTE4
, BIC.ATTRIBUTE5
, BIC.ATTRIBUTE6
, BIC.ATTRIBUTE7
, BIC.ATTRIBUTE8
, BIC.ATTRIBUTE9
, BIC.ATTRIBUTE10
, BIC.ATTRIBUTE11
, BIC.ATTRIBUTE12
, BIC.ATTRIBUTE13
, BIC.ATTRIBUTE14
, BIC.ATTRIBUTE15
, BE.COMPONENT_QUANTITY
, BIC.BASIS_TYPE
, BIC.SO_BASIS
, BIC.OPTIONAL
, BIC.MUTUALLY_EXCLUSIVE_OPTIONS
, BIC.CHECK_ATP
, BIC.SHIPPING_ALLOWED
, BIC.REQUIRED_TO_SHIP
, BIC.REQUIRED_FOR_REVENUE
, BIC.INCLUDE_ON_SHIP_DOCS
, BIC.INCLUDE_ON_BILL_DOCS
, BIC.LOW_QUANTITY
, BIC.HIGH_QUANTITY
, BIC.PICK_COMPONENTS
, BE.PRIMARY_UOM_CODE
, BE.PRIMARY_UNIT_OF_MEASURE
, BE.BASE_ITEM_ID
, BE.ATP_COMPONENTS_FLAG
, BE.ATP_FLAG
, BE.BOM_ITEM_TYPE
, BE.PICK_COMPONENTS_FLAG
, BE.REPLENISH_TO_ORDER_FLAG
, BE.SHIPPABLE_ITEM_FLAG
, BE.CUSTOMER_ORDER_FLAG
, BE.INTERNAL_ORDER_FLAG
, BE.CUSTOMER_ORDER_ENABLED_FLAG
, BE.INTERNAL_ORDER_ENABLED_FLAG
, BE.SO_TRANSACTIONS_FLAG
, CASE WHEN ( BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') /* OBJ_NAME NULL MEANS IT IS AN MSI ITEM */ THEN ( SELECT MSITL.DESCRIPTION
FROM MTL_SYSTEM_ITEMS_TL MSITL
WHERE MSITL.INVENTORY_ITEM_ID = BE.PK1_VALUE /* COMPONENT_ITEM_ID */
AND MSITL.ORGANIZATION_ID = BE.PK2_VALUE /* ORGANIZATION_ID */
AND MSITL.LANGUAGE = USERENV('LANG') ) WHEN BE.OBJ_NAME = 'DDD_CADVIEW' THEN ( SELECT OBJECT_NAME
FROM DDD_MODEL_OBJECTS CADCOMP WHERE CADCOMP.COMPONENT_ID = BE.PK1_VALUE /* COMPONENT_ID FOR CADCOMPS */ ) WHEN (BE.OBJ_NAME IS NOT NULL
AND BE.OBJ_NAME ='ATTACHMENT') THEN (SELECT DOCTL.DESCRIPTION
FROM FND_DOCUMENTS_TL DOCTL
, FND_ATTACHED_DOCUMENTS ATDOCS WHERE DOCTL.DOCUMENT_ID = ATDOCS.DOCUMENT_ID /* PK1_VALUE STAMPED AS THE ATTACHMENT DOCUMENT_ID WHEN INSERTING ATTACHMENTS */
AND DOCTL.LANGUAGE = USERENV('LANG')
AND ATDOCS.ATTACHED_DOCUMENT_ID = BE.PK1_VALUE ) END AS DESCRIPTION
, BE.ASSEMBLY_ITEM_ID
, BE.NEW_COMPONENT_CODE
, BE.LOOP_FLAG
, BE.PARENT_BOM_ITEM_TYPE
, BIC.OPERATION_SEQ_NUM
, BIC.ITEM_NUM
, BE.EFFECTIVITY_DATE
, BE.DISABLE_DATE
, BE.IMPLEMENTATION_DATE
, BE.REXPLODE_FLAG
, BE.COMMON_BILL_SEQUENCE_ID
, BE.COMP_BILL_SEQ_ID
, BE.COMP_COMMON_BILL_SEQ_ID
, BE.GROUP_ID
, BE.NUM_COL1
, BE.NUM_COL2
, BE.NUM_COL3
, BE.DATE_COL1
, BE.DATE_COL2
, BE.DATE_COL3
, BE.CHAR_COL1
, BE.CHAR_COL2
, BE.CHAR_COL3
, BIC.AUTO_REQUEST_MATERIAL
, CASE WHEN (BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN ( SELECT MSIKFV.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE MSIKFV.INVENTORY_ITEM_ID = BE.PK1_VALUE /* COMPONENT_ITEM_ID */
AND MSIKFV.ORGANIZATION_ID = BE.PK2_VALUE /* ORGANIZATION_ID */ ) WHEN BE.OBJ_NAME = 'DDD_CADVIEW' THEN ( SELECT OBJECT_NAME
FROM DDD_MODEL_OBJECTS CADCOMP
WHERE CADCOMP.COMPONENT_ID = BE.PK1_VALUE /* COMPONENT_ID FOR CADCOMPS */ ) WHEN BE.OBJ_NAME = 'ATTACHMENT' THEN (SELECT DOCTL.FILE_NAME
FROM FND_DOCUMENTS_TL DOCTL
, FND_ATTACHED_DOCUMENTS ATDOCS WHERE DOCTL.DOCUMENT_ID = ATDOCS.DOCUMENT_ID
AND DOCTL.LANGUAGE = USERENV('LANG')
AND ATDOCS.ATTACHED_DOCUMENT_ID = BE.PK1_VALUE ) END AS CONCATENATED_SEGMENTS
, CASE WHEN (BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN ( SELECT MSIKFV.INVENTORY_ITEM_STATUS_CODE
FROM MTL_SYSTEM_ITEMS_KFV MSIKFV
WHERE MSIKFV.INVENTORY_ITEM_ID = BE.PK1_VALUE /* COMPONENT_ITEM_ID */
AND MSIKFV.ORGANIZATION_ID = BE.PK2_VALUE /* ORGANIZATION_ID */ ) WHEN (BE.OBJ_NAME IS NOT NULL
AND BE.OBJ_NAME ='ATTACHMENT') THEN (SELECT MEANING
FROM FND_LOOKUPS
, FND_ATTACHED_DOCUMENTS ATDOCS
WHERE ATDOCS.ATTACHED_DOCUMENT_ID = BE.COMPONENT_ITEM_ID
AND LOOKUP_TYPE = 'FND_DM_ATTACHED_DOC_STATUS'
AND LOOKUP_CODE = NVL(ATDOCS.STATUS
, 'UNAPPROVED') /*COMPONENT ID REUSED FOR STORING ATTACHMENT_DOCUMENT_ID WHEN ATTACHMENT ARE INSERTED IN EXPLOSIONS */ ) END AS ITEM_STATUS
, 'ASSEMBLY_ENABLED.GIF' IMAGE_SOURCE
, BOM_GLOBALS.GET_ALTERNATE(BE.BILL_SEQUENCE_ID) ALT_BOM_DESIG
, NVL(BE.CURRENT_REVISION
, BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BE.COMPONENT_ITEM_ID
, BE.ORGANIZATION_ID
, DECODE(BE.COMP_FIXED_REVISION_ID
, NULL
, BOM_EXPLODER_PUB.GET_EXPLOSION_DATE
, BOM_EXPLODER_PUB.GET_REVISION_HIGHDATE(BE.COMP_FIXED_REVISION_ID)))) CURRENT_REVISION
, NVL(BE.REVISION_LABEL
, BOM_EXPLODER_PUB.GET_CURRENT_REVISION_LABEL) REVISION_LABEL
, BIC.INCLUDE_IN_COST_ROLLUP
, BE.COMPONENT_YIELD_FACTOR
, BE.PLANNING_FACTOR
, BIC.CHANGE_NOTICE
, BOM_GLOBALS.GET_STRUCTURE_TYPE(BE.BILL_SEQUENCE_ID
, BE.ORGANIZATION_ID) DISPLAY_NAME
, /*BOM_GLOBALS.GET_ITEM_TYPE(MSIKFV.ITEM_TYPE)*/ CASE WHEN (BE.OBJ_NAME IS NOT NULL
AND BE.OBJ_NAME = 'ATTACHMENT') THEN (SELECT 'DOCUMENT'
FROM DUAL) END AS USER_ITEM_TYPE /*USER_ITEM_TYPE */
, CASE WHEN ( BE.CHANGE_NOTICE IS NULL ) THEN TO_NUMBER(NULL) WHEN ( BE.CHANGE_NOTICE IS NOT NULL ) THEN ( SELECT CHANGE_ID
FROM ENG_ENGINEERING_CHANGES ECHG
WHERE ECHG.CHANGE_NOTICE = BE.CHANGE_NOTICE
AND ECHG.ORGANIZATION_ID = BE.ORGANIZATION_ID ) END AS CHANGE_ID
, CASE WHEN ( BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN (SELECT MCGKFV.CONCATENATED_SEGMENTS
FROM MTL_ITEM_CATALOG_GROUPS_KFV MCGKFV
, MTL_SYSTEM_ITEMS_B MSIB WHERE MSIB.INVENTORY_ITEM_ID = BE.PK1_VALUE
AND MSIB.ORGANIZATION_ID = BE.PK2_VALUE
AND MSIB.ITEM_CATALOG_GROUP_ID = MCGKFV.ITEM_CATALOG_GROUP_ID (+) ) WHEN (BE.OBJ_NAME IS NOT NULL
AND BE.OBJ_NAME ='ATTACHMENT') THEN (SELECT USER_NAME
FROM FND_DOCUMENT_CATEGORIES_VL WHERE CATEGORY_ID = BE.LINE_ID /* LINE_ID IS REUSED FOR ATTACHMENT CATEGORY ID */ ) END AS CATALOG_CATEGORY
, BE.ENG_ITEM_FLAG
, BE.PARENT_SORT_ORDER
, CASE WHEN (BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN ( SELECT LONG_DESCRIPTION
FROM MTL_SYSTEM_ITEMS_TL MSITL
WHERE INVENTORY_ITEM_ID = BE.PK1_VALUE
AND ORGANIZATION_ID = BE.PK2_VALUE
AND LANGUAGE = USERENV('LANG') ) WHEN (BE.OBJ_NAME = 'DDD_CADVIEW') THEN ( SELECT BE.DESCRIPTION
FROM DUAL ) END AS LONG_DESCRIPTION
, CASE WHEN (BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN ( SELECT NVL(BE.CURRENT_REVISION
, BOM_EXPLODER_PUB.GET_CURRENT_REVISIONDETAILS(BE.COMPONENT_ITEM_ID
, BE.ORGANIZATION_ID
, DECODE(BE.COMP_FIXED_REVISION_ID
, NULL
, BOM_EXPLODER_PUB.GET_EXPLOSION_DATE
, BOM_EXPLODER_PUB.GET_REVISION_HIGHDATE(BE.COMP_FIXED_REVISION_ID))))
FROM DUAL ) WHEN (BE.OBJ_NAME = 'DDD_CADVIEW') THEN ( SELECT NULL
FROM DUAL ) END AS REVISION_CODE
, BE.STRUCTURE_TYPE_ID
, BIC.SUGGESTED_VENDOR_NAME
, BIC.VENDOR_ID
, BIC.UNIT_PRICE
, BE.PK1_VALUE
, BE.PK2_VALUE
, BE.PK3_VALUE
, BE.PK4_VALUE
, BE.PK5_VALUE
, BE.OBJ_NAME
, BE.HGRID_FLAG
, CASE WHEN ( BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN (SELECT MSIB.ITEM_CATALOG_GROUP_ID
FROM MTL_SYSTEM_ITEMS_B MSIB WHERE MSIB.INVENTORY_ITEM_ID = BE.PK1_VALUE
AND MSIB.ORGANIZATION_ID = BE.PK2_VALUE ) WHEN (BE.OBJ_NAME IS NOT NULL
AND BE.OBJ_NAME ='ATTACHMENT') THEN ( SELECT BE.LINE_ID
FROM DUAL) END AS CATALOG_CATEGORY_ID
, NVL(BE.REVISION_ID
, BOM_EXPLODER_PUB.GET_CURRENT_REVISION_ID) REVISION_ID
, BE.FROM_MINOR_REVISION_ID
, BE.TO_MINOR_REVISION_ID
, NULL FROM_MINOR_REVISION_LABEL
, NULL TO_MINOR_REVISION_LABEL
, BE.FROM_END_ITEM_REV_ID
, BE.TO_END_ITEM_REV_ID
, (SELECT REVISION_LABEL
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = BE.FROM_END_ITEM_REV_ID) FROM_END_ITEM_REVISION_LABEL
, (SELECT REVISION_LABEL
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = BE.TO_END_ITEM_REV_ID) TO_END_ITEM_REVISION_LABEL
, BE.FROM_END_ITEM_MINOR_REV_ID
, BE.TO_END_ITEM_MINOR_REV_ID
, NULL FROM_END_ITEM_MINOR_REV_LABEL
, NULL TO_END_ITEM_MINOR_REV_LABEL
, CASE WHEN (BE.OBJ_NAME = 'DDD_CADVIEW') THEN (SELECT OBJECT_VERSION
FROM DDD_ITEM_MAPPING_INFO DDD_MAP
WHERE BE.PK1_VALUE = DDD_MAP.COMPONENT_ID ) END AS OBJECT_VERSION
, CASE WHEN (BE.OBJ_NAME = 'DDD_CADVIEW') THEN (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_VL MSI
, DDD_ITEM_MAPPING_INFO DDD_MAP
WHERE BE.PK1_VALUE = DDD_MAP.COMPONENT_ID
AND DDD_MAP.PK2_VALUE = MSI.INVENTORY_ITEM_ID
AND DDD_MAP.PK1_VALUE = MSI.ORGANIZATION_ID ) END AS MAPPED_ITEM_NAME
, CASE WHEN (BE.OBJ_NAME = 'DDD_CADVIEW') THEN (SELECT REVISION||'-'||REVISION_LABEL
FROM MTL_ITEM_REVISIONS_VL MSI_REV
, DDD_ITEM_MAPPING_INFO DDD_MAP
WHERE BE.PK1_VALUE = DDD_MAP.COMPONENT_ID
AND DDD_MAP.PK3_VALUE = MSI_REV.REVISION_ID ) END AS MAPPED_ITEM_REVISION
, CASE WHEN (BE.OBJ_NAME = 'DDD_CADVIEW') THEN (SELECT MAP_STATUS
FROM MTL_ITEM_REVISIONS_VL MSI_REV
, DDD_ITEM_MAPPING_INFO DDD_MAP
WHERE BE.PK1_VALUE = DDD_MAP.COMPONENT_ID
AND DDD_MAP.PK3_VALUE = MSI_REV.REVISION_ID ) END AS MAP_STATUS
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV K
, MTL_ITEM_REVISIONS MIR
WHERE MIR.REVISION_ID = BE.FROM_END_ITEM_REV_ID
AND K.INVENTORY_ITEM_ID = MIR.INVENTORY_ITEM_ID
AND K.ORGANIZATION_ID = MIR.ORGANIZATION_ID) FROM_END_ITEM
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV K
, MTL_ITEM_REVISIONS MIR
WHERE MIR.REVISION_ID = BE.TO_END_ITEM_REV_ID
AND K.INVENTORY_ITEM_ID = MIR.INVENTORY_ITEM_ID
AND K.ORGANIZATION_ID = MIR.ORGANIZATION_ID) TO_END_ITEM
, BE.EFFECTIVITY_CONTROL
, BOM_SECURITY_PUB.CHECK_USER_PRIVILEGE( 1
, BOM_SECURITY_PUB.GET_FUNCTION_NAME_TO_CHECK
, 'EGO_ITEM'
, BE.PK1_VALUE
, BE.ORGANIZATION_ID
, NULL
, NULL
, NULL
, BOM_EXPLODER_PUB.GET_EGO_USER) ACCESS_FLAG
, TO_CHAR(NULL) TEMPLATE_NAME
, BE.BOM_IMPLEMENTATION_DATE BOM_IMPLEMENTATION_DATE
, NULL MINOR_REVISION
, BE.ASSEMBLY_TYPE
, BE.GTIN_NUMBER
, BE.GTIN_DESCRIPTION
, BE.TRADE_ITEM_DESCRIPTOR
, BE.TOP_GTIN_NUMBER
, BE.TOP_GTIN_DESCRIPTION
, BE.TOP_TRADE_ITEM_DESCRIPTOR
, BE.PARENT_GTIN_NUMBER
, BE.PARENT_GTIN_DESCRIPTION
, BE.PARENT_TRADE_ITEM_DESCRIPTOR
, BE.QUANTITY_OF_CHILDREN
, BE.TOTAL_QTY_AT_NEXT_LEVEL
, BE.IS_PREFERRED
, BE.TRADE_ITEM_DESCRIPTOR_DESC
, BE.GTIN_PUBLICATION_STATUS
, NULL ATTACHMENT_NAME
, BIC.ACD_TYPE
, BIC.QUANTITY_RELATED
, BIC.SUPPLY_SUBINVENTORY
, BE.FROM_END_ITEM_UNIT_NUMBER
, BE.TO_END_ITEM_UNIT_NUMBER
, DECODE(BE.ALTERNATE_BOM_DESIGNATOR
, NULL
, BOM_GLOBALS.RETRIEVE_MESSAGE('BOM'
, 'BOM_PRIMARY')
, ( SELECT BAD.DISPLAY_NAME
FROM BOM_ALTERNATE_DESIGNATORS_TL BAD
WHERE BAD.LANGUAGE = USERENV('LANG')
AND BE.ALTERNATE_BOM_DESIGNATOR = BAD.ALTERNATE_DESIGNATOR_CODE
AND BE.ORGANIZATION_ID = BAD.ORGANIZATION_ID ) ) STRUCTURE_DISPLAY_NAME
, BE.CHANGE_POLICY_VALUE
, BE.COMPONENT_REMARKS
, NVL(BE. IS_EXCLUDED_BY_RULE
, BOM_EXPLODER_PUB.CHECK_EXCLUDED_BY_RULE(BE.NEW_COMPONENT_CODE))
, BIC.WIP_SUPPLY_TYPE
, BE.LOCATOR
, BE.GROUP_ID
, BE.PARENT_IMPLEMENTATION_DATE
, BE.PARENT_CHANGE_NOTICE
, BOM_EXPLODER_PUB.GET_REVISION_CODE(BE.COMPONENT_ITEM_REVISION_ID) COMP_FIXED_REV_CODE
FROM BOM_EXPLOSIONS_ALL BE
, BOM_COMPONENTS_B BIC
WHERE BE.COMPONENT_SEQUENCE_ID=BIC.COMPONENT_SEQUENCE_ID(+)

Columns