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_EXPLOSIONS_ALL_V

Source

View Text - Preformatted


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, */ NULL 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, */ NULL 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, */ NULL 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 , */ NULL 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 , BIC.INHERIT_FLAG FROM BOM_EXPLOSIONS_ALL BE , BOM_COMPONENTS_B BIC WHERE BE.COMPONENT_SEQUENCE_ID=BIC.COMPONENT_SEQUENCE_ID(+)

View Text - HTML Formatted

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
, */ NULL 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
, */ NULL 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
, */ NULL 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
, */ NULL 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
, BIC.INHERIT_FLAG
FROM BOM_EXPLOSIONS_ALL BE
, BOM_COMPONENTS_B BIC
WHERE BE.COMPONENT_SEQUENCE_ID=BIC.COMPONENT_SEQUENCE_ID(+)