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_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 , DECODE(BE.PLAN_LEVEL, 0, -1, BIC.BASIS_TYPE) BASIS_TYPE, BE.COMPONENT_QUANTITY , 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 , (SELECT description from mtl_units_of_measure where uom_code = BE.PRIMARY_UOM_CODE) 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, */ BOM_EXPLODER_PUB.Get_Component_Revision(nvl(BE.component_sequence_id,0)) current_revision, BOM_EXPLODER_PUB.Get_Component_Revision_Label(nvl(BE.component_sequence_id,0)) 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 FND_FLEX_SERVER.get_kfv_concat_segs_by_ccid('COMPACT',401,'MICG',101,MCGKFV.item_catalog_group_id ,NULL) FROM MTL_ITEM_CATALOG_GROUPS 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 , */ BOM_EXPLODER_PUB.Get_Component_Revision(nvl(BE.component_sequence_id,0)) 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, */ BOM_EXPLODER_PUB.Get_Component_Revision_Id(nvl(BE.component_sequence_id,0)) REVISION_ID, BE.FROM_MINOR_REVISION_ID, BE.TO_MINOR_REVISION_ID, EMR1.MINOR_REVISION FROM_MINOR_REVISION_LABEL, EMR2.MINOR_REVISION 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, EMR3.MINOR_REVISION FROM_END_ITEM_MINOR_REV_LABEL, EMR4.MINOR_REVISION 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 , DECODE( BOM_EXPLODER_PUB.Get_Component_Access_Flag(nvl(BE.parent_comp_seq_id,0)), 'T', BOM_EXPLODER_PUB.Get_Component_Access_Flag(nvl(BE.component_sequence_id,0)), BOM_EXPLODER_PUB.Get_Component_Access_Flag(nvl(BE.parent_comp_seq_id,0)) ) ACCESS_FLAG, /* 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 , EMR5.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 ,*/ BOM_EXPLODER_PUB.Get_Change_Policy(nvl(BE.Component_sequence_Id,0)) AS 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, BE.SOURCE_BILL_SEQUENCE_ID, BE.COMMON_COMPONENT_SEQUENCE_ID, BE.COMP_SOURCE_BILL_SEQ_ID ,DECODE(BE.COMPONENT_QUANTITY,0,0,1/BE.COMPONENT_QUANTITY) INVERSE_QUANTITY ,BE.COMP_EFFECTIVITY_CONTROL , BIC.REVISED_ITEM_SEQUENCE_ID, (SELECT revision FROM mtl_item_revisions_b WHERE revision_id = BE.FROM_END_ITEM_REV_ID) FROM_END_ITEM_REVISION_CODE, (SELECT revision FROM mtl_item_revisions_b WHERE revision_id = BE.TO_END_ITEM_REV_ID) TO_END_ITEM_REVISION_CODE, BIC.ENFORCE_INT_REQUIREMENTS, BOM_EXPLODER_PUB.Get_Component_Revision(NVL(BE.parent_comp_seq_id,0)) PARENT_REVISION_CODE, BOM_EXPLODER_PUB.Get_Component_Revision_Label(NVL(BE.parent_comp_seq_id,0)) PARENT_REVISION_LABEL, BOM_EXPLODER_PUB.Get_Component_Revision_Id(NVL(BE.parent_comp_seq_id,0)) PARENT_REVISION_ID, Decode(BE.comp_source_bill_seq_id, BE.comp_bill_seq_id, NULL, CASE WHEN (BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN (SELECT MSIKFV.CONCATENATED_SEGMENTS FROM MTL_SYSTEM_ITEMS_KFV MSIKFV, BOM_STRUCTURES_B BOM WHERE MSIKFV.INVENTORY_ITEM_ID = BOM.PK1_VALUE AND MSIKFV.ORGANIZATION_ID = BOM.PK2_VALUE AND BOM.BILL_SEQUENCE_ID = BE.COMP_SOURCE_BILL_SEQ_ID ) WHEN BE.OBJ_NAME = 'DDD_CADVIEW' THEN (SELECT OBJECT_NAME FROM DDD_MODEL_OBJECTS cadcomp, BOM_STRUCTURES_B BOM WHERE cadcomp.component_id = BOM.PK1_VALUE AND BOM.BILL_SEQUENCE_ID = BE.COMP_SOURCE_BILL_SEQ_ID ) END ) COMMON_ITEM_NAME, Decode(BE.comp_source_bill_seq_id, BE.comp_bill_seq_id, NULL, CASE WHEN (BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN (SELECT NAME FROM HR_ALL_ORGANIZATION_UNITS_TL HRORG, BOM_STRUCTURES_B BOM WHERE HRORG.ORGANIZATION_ID = BOM.PK2_VALUE AND BOM.BILL_SEQUENCE_ID = BE.COMP_SOURCE_BILL_SEQ_ID AND HRORG.LANGUAGE = USERENV('LANG') ) WHEN BE.OBJ_NAME = 'DDD_CADVIEW' THEN (SELECT NULL FROM DUAL ) END ) COMMON_ORGANIZATION_NAME, (SELECT specific_assembly_comment FROM bom_structures_b where bill_sequence_id = BE.comp_bill_seq_id ) AS STRUCTURE_DESC, CASE WHEN ( BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM' ) THEN ( SELECT msib.STYLE_ITEM_ID FROM MTL_SYSTEM_ITEMS_B msib WHERE msib.INVENTORY_ITEM_ID = BE.PK1_VALUE AND msib.ORGANIZATION_ID = BE.PK2_VALUE ) ELSE NULL END STYLE_ITEM_ID, CASE WHEN ( BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM' ) THEN ( SELECT msib.STYLE_ITEM_FLAG FROM MTL_SYSTEM_ITEMS_B msib WHERE msib.INVENTORY_ITEM_ID = BE.PK1_VALUE AND msib.ORGANIZATION_ID = BE.PK2_VALUE ) ELSE NULL END STYLE_ITEM_FLAG FROM BOM_EXPLOSIONS_ALL BE , BOM_COMPONENTS_B BIC, ego_minor_revisions EMR1 , ego_minor_revisions EMR2 , ego_minor_revisions EMR3 , ego_minor_revisions EMR4 , ego_minor_revisions EMR5 WHERE BE.group_id = BOM_EXPLODER_PUB.get_group_id AND BE.COMPONENT_SEQUENCE_ID=BIC.COMPONENT_SEQUENCE_ID (+) AND ( BOM_EXPLODER_PUB.get_explode_option = 1 OR BE.plan_level = 0 OR ( NVL(BE.EFFECTIVITY_CONTROL,1) =1 /* Date Effectivity */ AND ( (BE.implementation_date IS NULL AND BE.acd_type = 3 AND decode(BE.comp_fixed_revision_id,null,BOM_EXPLODER_PUB.get_explosion_date,BOM_EXPLODER_PUB.Get_Revision_HighDate(BE.comp_fixed_revision_id)) >= BE.trimmed_effectivity_date) OR (BOM_EXPLODER_PUB.get_explode_option = 2 AND decode(BE.comp_fixed_revision_id,null,BOM_EXPLODER_PUB.get_explosion_date,BOM_EXPLODER_PUB.Get_Revision_HighDate(BE.comp_fixed_revision_id)) >= BE.trimmed_effectivity_date AND decode(BE.comp_fixed_revision_id,null,BOM_EXPLODER_PUB.get_explosion_date,BOM_EXPLODER_PUB.Get_Revision_HighDate(BE.comp_fixed_revision_id)) < nvl(BE.trimmed_disable_date,TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'))) OR (BOM_EXPLODER_PUB.get_explode_option = 3 AND decode(BE.comp_fixed_revision_id,null,BOM_EXPLODER_PUB.get_explosion_date,BOM_EXPLODER_PUB.Get_Revision_HighDate(BE.comp_fixed_revision_id)) < nvl(BE.trimmed_disable_date,TO_DATE('9999/12/31 00:00:00','yyyy/mm/dd hh24:mi:ss'))) ) ) OR ( NVL(BE.EFFECTIVITY_CONTROL,1) =4 /* Rev Effectivity */ AND ( ( BOM_EXPLODER_PUB.get_explode_option = 2 AND ((BOM_EXPLODER_PUB.Get_Expl_End_Item_Rev_Code >= (SELECT revision FROM mtl_item_revisions_b WHERE inventory_item_id = BE.end_item_id AND organization_id = BE.end_item_org_id AND revision_id = BE.from_end_item_rev_id) AND (BE.to_end_item_rev_id IS NULL OR BOM_EXPLODER_PUB.Get_Expl_End_Item_Rev_Code <= (SELECT revision FROM mtl_item_revisions_b WHERE inventory_item_id = BE.end_item_id AND organization_id = BE.end_item_org_id AND revision_id = BE.to_end_item_rev_id))) OR (BE.plan_level > 1 AND BOM_EXPLODER_PUB.Get_Component_Revision(NVL(BE.parent_comp_seq_id,0)) >= (SELECT revision FROM mtl_item_revisions_b WHERE inventory_item_id = BE.assembly_item_id AND organization_id = BE.organization_id AND revision_id = BE.from_end_item_rev_id) AND (BE.to_end_item_rev_id IS NULL OR BOM_EXPLODER_PUB.Get_Component_Revision(NVL(BE.parent_comp_seq_id,0)) <= (SELECT revision FROM mtl_item_revisions_b WHERE inventory_item_id = BE.assembly_item_id AND organization_id = BE.organization_id AND revision_id = BE.to_end_item_rev_id))))) OR ( BOM_EXPLODER_PUB.get_explode_option = 3 AND ((BE.to_end_item_rev_id IS NULL) OR (BOM_EXPLODER_PUB.Get_Expl_End_Item_Rev_Code <= (SELECT revision FROM mtl_item_revisions_b WHERE inventory_item_id = BE.end_item_id AND organization_id = BE.end_item_org_id AND revision_id = BE.to_end_item_rev_id) ) OR (BE.plan_level > 1 AND BOM_EXPLODER_PUB.Get_Component_Revision(NVL(BE.parent_comp_seq_id,0)) <= (SELECT revision FROM mtl_item_revisions_b WHERE inventory_item_id = BE.assembly_item_id AND organization_id = BE.organization_id AND revision_id = BE.to_end_item_rev_id)))) ) ) OR ( NVL(BE.EFFECTIVITY_CONTROL,1) =2 /* Unit/Serial Effectivity */ AND ( (BOM_EXPLODER_PUB.get_explode_option = 2 AND BOM_EXPLODER_PUB.get_expl_unit_number BETWEEN BE.trimmed_from_unit_number AND nvl(BE.trimmed_to_unit_number, BOM_EXPLODER_PUB.get_expl_unit_number)) OR (BOM_EXPLODER_PUB.get_explode_option = 3 AND BOM_EXPLODER_PUB.get_expl_unit_number <= nvl(BE.trimmed_to_unit_number,BOM_EXPLODER_PUB.get_expl_unit_number)) ) ) ) AND BE.FROM_MINOR_REVISION_ID = EMR1.MINOR_REVISION_ID(+) AND BE.TO_MINOR_REVISION_ID = EMR2.MINOR_REVISION_ID(+) AND BE.FROM_END_ITEM_MINOR_REV_ID = EMR3.MINOR_REVISION_ID(+) AND BE.TO_END_ITEM_MINOR_REV_ID = EMR4.MINOR_REVISION_ID(+) AND EMR5.MINOR_REVISION_ID(+) = BE.MINOR_REVISION_ID UNION ALL SELECT BET.TOP_BILL_SEQUENCE_ID, BET.BILL_SEQUENCE_ID, BET.ORGANIZATION_ID, BET.EXPLOSION_TYPE, ATDOCS.SEQ_NUM COMPONENT_SEQUENCE_ID, ATDOCS.ATTACHED_DOCUMENT_ID COMPONENT_ITEM_ID, BET.PLAN_LEVEL +1 PLAN_LEVEL , TO_NUMBER(NULL) EXTENDED_QUANTITY , bet.sort_order||'99'||LPAD(ROWNUM, 6, '0') SORT_ORDER, BET.CREATION_DATE , BET.CREATED_BY , ATDOCS.LAST_UPDATE_DATE , ATDOCS.LAST_UPDATED_BY , BET.TOP_ITEM_ID, TO_CHAR(NULL) CONTEXT , TO_CHAR(NULL) ATTRIBUTE1 , TO_CHAR(NULL) ATTRIBUTE2 , TO_CHAR(NULL) ATTRIBUTE3 , TO_CHAR(NULL) ATTRIBUTE4 , TO_CHAR(NULL) ATTRIBUTE5 , TO_CHAR(NULL) ATTRIBUTE6 , TO_CHAR(NULL) ATTRIBUTE7 , TO_CHAR(NULL) ATTRIBUTE8 , TO_CHAR(NULL) ATTRIBUTE9 , TO_CHAR(NULL) ATTRIBUTE10 , TO_CHAR(NULL) ATTRIBUTE11 , TO_CHAR(NULL) ATTRIBUTE12 , TO_CHAR(NULL) ATTRIBUTE13 , TO_CHAR(NULL) ATTRIBUTE14 , TO_CHAR(NULL) ATTRIBUTE15 , TO_NUMBER(NULL) BASIS_TYPE, TO_NUMBER(1) COMPONENT_QUANTITY, TO_NUMBER(NULL) SO_BASIS, TO_NUMBER(NULL) OPTIONAL, TO_NUMBER(NULL) MUTUALLY_EXCLUSIVE_OPTIONS , TO_NUMBER(NULL) CHECK_ATP , TO_NUMBER(NULL) SHIPPING_ALLOWED , TO_NUMBER(NULL) REQUIRED_TO_SHIP , TO_NUMBER(NULL) REQUIRED_FOR_REVENUE , TO_NUMBER(NULL) INCLUDE_ON_SHIP_DOCS , TO_NUMBER(NULL) INCLUDE_ON_BILL_DOCS , TO_NUMBER(NULL) LOW_QUANTITY , TO_NUMBER(NULL) HIGH_QUANTITY , TO_NUMBER(NULL) PICK_COMPONENTS , TO_CHAR(NULL) PRIMARY_UOM_CODE , TO_CHAR(NULL) PRIMARY_UNIT_OF_MEASURE , TO_NUMBER(NULL) BASE_ITEM_ID , TO_CHAR(NULL) ATP_COMPONENTS_FLAG , TO_CHAR(NULL) ATP_FLAG , TO_NUMBER(NULL) BOM_ITEM_TYPE , TO_CHAR(NULL) PICK_COMPONENTS_FLAG , TO_CHAR(NULL) REPLENISH_TO_ORDER_FLAG , TO_CHAR(NULL) SHIPPABLE_ITEM_FLAG , TO_CHAR(NULL) CUSTOMER_ORDER_FLAG , TO_CHAR(NULL) INTERNAL_ORDER_FLAG , TO_CHAR(NULL) CUSTOMER_ORDER_ENABLED_FLAG , TO_CHAR(NULL) INTERNAL_ORDER_ENABLED_FLAG , TO_CHAR(NULL) SO_TRANSACTIONS_FLAG , TO_CHAR(DOCTL.DESCRIPTION) DESCRIPTION, BET.COMPONENT_ITEM_ID ASSEMBLY_ITEM_ID , TO_CHAR(NULL) COMPONENT_CODE , TO_NUMBER(NULL) LOOP_FLAG , TO_NUMBER(NULL) PARENT_BOM_ITEM_TYPE , TO_NUMBER(NULL) OPERATION_SEQ_NUM , ATDOCS.ATTACHED_DOCUMENT_ID ITEM_NUM , TO_DATE(NULL) EFFECTIVITY_DATE , TO_DATE(NULL) DISABLE_DATE , SYSDATE IMPLEMENTATION_DATE , TO_NUMBER(NULL) REXPLODE_FLAG , TO_NUMBER(NULL) COMMON_BILL_SEQUENCE_ID , BET.COMP_BILL_SEQ_ID , TO_NUMBER(NULL) COMP_COMMON_BILL_SEQ_ID , BET.GROUP_ID , TO_NUMBER(NULL) NUM_COL1 , TO_NUMBER(NULL) NUM_COL2 , TO_NUMBER(NULL) NUM_COL3 , TO_DATE(NULL) DATE_COL1 , TO_DATE(NULL) DATE_COL2 , TO_DATE(NULL) DATE_COL3 , TO_CHAR(NULL) CHAR_COL1 , TO_CHAR(NULL) CHAR_COL2 , TO_CHAR(NULL) CHAR_COL3 , TO_CHAR(NULL) AUTO_REQUEST_MATERIAL , Nvl(TO_CHAR(DOCTL.TITLE), TO_CHAR(DOCS.FILE_NAME)) ITEM_NAME, ( SELECT meaning FROM fnd_lookups WHERE lookup_type = 'FND_DM_ATTACHED_DOC_STATUS' AND lookup_code = ATDOCS.STATUS ) ITEM_STATUS, TO_CHAR(NULL) IMAGE_SOURCE , BOM_GLOBALS.GET_ALTERNATE(BET.BILL_SEQUENCE_ID) ALTERNATE_BOM_DESIGNATOR, TO_CHAR(NULL) CURRENT_ITEM_REVISION , TO_CHAR(NULL) REVISION_LABEL , TO_NUMBER(NULL) INCLUDE_IN_COST_ROLLUP , TO_NUMBER(NULL) COMPONENT_YIELD_FACTOR , TO_NUMBER(NULL) PLANNING_FACTOR , TO_CHAR(NULL) CHANGE_NOTICE , TO_CHAR(NULL) STRUCTURE_TYPE , 'Document' USER_ITEM_TYPE , TO_NUMBER(NULL) CHANGE_ID, ( SELECT user_name FROM fnd_document_categories_vl WHERE category_id = nvl(ATDOCS.CATEGORY_ID,DOCS.CATEGORY_ID) ) CATALOG_CATEGORY, TO_CHAR(NULL) ENG_ITEM_FLAG, BET.SORT_ORDER PARENT_SORT_ORDER, TO_CHAR(NULL) LONG_DESCRIPTION, TO_CHAR(NULL) REVISION_CODE, BET.STRUCTURE_TYPE_ID STRUCTURE_TYPE_ID , TO_CHAR(NULL) SUGGESTED_VENDOR_NAME, TO_NUMBER(NULL) VENDOR_ID, TO_NUMBER(NULL) UNIT_PRICE, TO_CHAR(ATDOCS.ATTACHED_DOCUMENT_ID) PK1_VALUE, TO_CHAR(NULL) PK2_VALUE, TO_CHAR(NULL) PK3_VALUE, TO_CHAR(NULL) PK4_VALUE, TO_CHAR(NULL) PK5_VALUE, TO_CHAR(NULL) OBJ_NAME, 'Y' HGRID_FLAG , nvl(ATDOCS.CATEGORY_ID,DOCS.CATEGORY_ID) CATALOG_CATEGORY_ID , BET.REVISION_ID REVISION_ID , TO_NUMBER(NULL) FROM_MINOR_REVISION_ID, TO_NUMBER(NULL) TO_MINOR_REVISION_ID, TO_CHAR(NULL) FROM_MINOR_REVISION_LABEL, TO_CHAR(NULL) TO_MINOR_REVISION_LABEL, TO_NUMBER(NULL) FROM_END_ITEM_REV_ID, TO_NUMBER(NULL) TO_END_ITEM_REV_ID, TO_CHAR(NULL) FROM_END_ITEM_REVISION_LABEL, TO_CHAR(NULL) TO_END_ITEM_REVISION_LABEL, TO_NUMBER(NULL) FROM_END_ITEM_MINOR_REV_ID, TO_NUMBER(NULL) TO_END_ITEM_MINOR_REV_ID, TO_CHAR(NULL) FROM_END_ITEM_MINOR_REV_LABEL, TO_CHAR(NULL) TO_END_ITEM_MINOR_REV_LABEL , TO_NUMBER(NULL) OBJECT_VERSION, TO_CHAR(NULL) MAPPED_ITEM_NAME, TO_CHAR(NULL) MAPPED_ITEM_REVISION, TO_NUMBER(NULL) MAP_STATUS, TO_CHAR(NULL) FROM_END_ITEM , TO_CHAR(NULL) TO_END_ITEM , BET.EFFECTIVITY_CONTROL EFFECTIVITY_CONTROL, BOM_EXPLODER_PUB.Get_Component_Access_Flag(nvl(BET.component_sequence_id,0)) ACCESS_FLAG, TO_CHAR(NULL) TEMPLATE_NAME , TO_DATE(NULL) BOM_IMPLEMENTATION_DATE , TO_CHAR(NULL) MINOR_REVISION, TO_NUMBER(NULL) ASSEMBLY_TYPE, TO_CHAR(NULL) GTIN_NUMBER, TO_CHAR(NULL) GTIN_DESCRIPTION, TO_CHAR(NULL) TRADE_ITEM_DESCRIPTOR, TO_CHAR(NULL) TOP_GTIN_NUMBER, TO_CHAR(NULL) TOP_GTIN_DESCRIPTION, TO_CHAR(NULL) TOP_TRADE_ITEM_DESCRIPTOR, TO_CHAR(NULL) PARENT_GTIN_NUMBER, TO_CHAR(NULL) PARENT_GTIN_DESCRIPTION, TO_CHAR(NULL) PARENT_TRADE_ITEM_DESCRIPTOR, TO_NUMBER(NULL) QUANTITY_OF_CHILDREN, TO_NUMBER(NULL) TOTAL_QTY_AT_NEXT_LEVEL, TO_CHAR(NULL) IS_PREFERRED, TO_CHAR(NULL) TRADE_ITEM_DESCRIPTOR_DESC, TO_CHAR(NULL) GTIN_PUBLICATION_STATUS, Nvl(TO_CHAR(DOCTL.TITLE), TO_CHAR(DOCS.FILE_NAME)) ATTACHMENT_NAME, TO_NUMBER(NULL) ACD_TYPE, TO_NUMBER(NULL) QUANTITY_RELATED, TO_CHAR(NULL) SUPPLY_SUBINVENTORY, TO_CHAR(NULL) FROM_END_ITEM_UNIT_NUMBER, TO_CHAR(NULL) TO_END_ITEM_UNIT_NUMBER , TO_CHAR(NULL) STRUCTURE_DISPLAY_NAME, TO_CHAR(NULL) CHANGE_POLICY_VALUE , TO_CHAR(NULL) COMPONENT_REMARKS , TO_CHAR(NULL) IS_EXCLUDED_BY_RULE , TO_NUMBER(NULL) WIP_SUPPLY_TYPE, TO_CHAR(NULL) LOCATOR, BET.GROUP_ID , SYSDATE PARENT_IMPLEMENTATION_DATE, TO_CHAR(NULL) PARENT_CHANGE_NOTICE, TO_CHAR(NULL) COMP_FIXED_REV_CODE, TO_NUMBER(NULL) SOURCE_BILL_SEQUENCE_ID, TO_NUMBER(NULL) COMMON_COMPONENT_SEQUENCE_ID, TO_NUMBER(NULL) COMP_SOURCE_BILL_SEQ_ID , TO_NUMBER(NULL) INVERSE_QUANTITY, TO_NUMBER(NULL) COMP_EFFECTIVITY_CONTROL, TO_NUMBER(NULL) REVISED_ITEM_SEQUENCE_ID , TO_CHAR(NULL) FROM_END_ITEM_REVISION_CODE, TO_CHAR(NULL) TO_END_ITEM_REVISION_CODE, TO_NUMBER(NULL) ENFORCE_INT_REQUIREMENTS, TO_CHAR(NULL) PARENT_REVISION_CODE, TO_CHAR(NULL) PARENT_REVISION_LABEL, TO_NUMBER(NULL) PARENT_REVISION_ID, To_Char(NULL) COMMON_ITEM_NAME, To_Char(NULL) COMMON_ORGANIZATION_NAME, To_Char(NULL) STRUCTURE_DESC, TO_NUMBER(NULL) STYLE_ITEM_ID, TO_CHAR(NULL) STYLE_ITEM_FLAG FROM BOM_EXPLOSIONS_ALL BET , BOM_STRUCTURES_B STRB, FND_ATTACHED_DOCUMENTS ATDOCS, FND_DOCUMENTS DOCS, FND_DOCUMENTS_TL DOCTL WHERE BET.group_id = BOM_EXPLODER_PUB.get_group_id AND ( ( ATDOCS.ENTITY_NAME = 'MTL_SYSTEM_ITEMS' AND ATDOCS.PK1_VALUE = to_char(BET.ORGANIZATION_ID) AND ATDOCS.PK2_VALUE = to_char(BET.COMPONENT_ITEM_ID) ) OR ( ATDOCS.ENTITY_NAME = 'MTL_ITEM_REVISIONS' AND ATDOCS.PK1_VALUE = to_char(BET.ORGANIZATION_ID) AND ATDOCS.PK2_VALUE = to_char(BET.COMPONENT_ITEM_ID) AND ATDOCS.PK3_VALUE = TO_CHAR( BOM_EXPLODER_PUB.Get_Component_Revision_Id(NVL(BET.component_sequence_id,0)) ) ) ) AND ATDOCS.DOCUMENT_ID = DOCS.DOCUMENT_ID AND ATDOCS.DOCUMENT_ID = DOCTL.DOCUMENT_ID AND DOCTL.LANGUAGE = USERENV('LANG') AND BET.BILL_SEQUENCE_ID = STRB.BILL_SEQUENCE_ID AND nvl(ATDOCS.CATEGORY_ID,DOCS.CATEGORY_ID) IN ( SELECT BIA.attach_category_id FROM BOM_ITEM_ATTACH_CATEGORY_ASSOC BIA WHERE BIA.STRUCTURE_TYPE_ID IN ( SELECT bst1.structure_type_id FROM BOM_STRUCTURE_TYPES_B bst1 CONNECT BY PRIOR bst1.parent_structure_type_id = bst1.structure_type_id START WITH bst1.structure_type_id = STRB.structure_type_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
, DECODE(BE.PLAN_LEVEL
, 0
, -1
, BIC.BASIS_TYPE) BASIS_TYPE
, BE.COMPONENT_QUANTITY
, 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
, (SELECT DESCRIPTION
FROM MTL_UNITS_OF_MEASURE
WHERE UOM_CODE = BE.PRIMARY_UOM_CODE) 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
, */ BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(NVL(BE.COMPONENT_SEQUENCE_ID
, 0)) CURRENT_REVISION
, BOM_EXPLODER_PUB.GET_COMPONENT_REVISION_LABEL(NVL(BE.COMPONENT_SEQUENCE_ID
, 0)) 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 FND_FLEX_SERVER.GET_KFV_CONCAT_SEGS_BY_CCID('COMPACT'
, 401
, 'MICG'
, 101
, MCGKFV.ITEM_CATALOG_GROUP_ID
, NULL)
FROM MTL_ITEM_CATALOG_GROUPS 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
, */ BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(NVL(BE.COMPONENT_SEQUENCE_ID
, 0)) 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
, */ BOM_EXPLODER_PUB.GET_COMPONENT_REVISION_ID(NVL(BE.COMPONENT_SEQUENCE_ID
, 0)) REVISION_ID
, BE.FROM_MINOR_REVISION_ID
, BE.TO_MINOR_REVISION_ID
, EMR1.MINOR_REVISION FROM_MINOR_REVISION_LABEL
, EMR2.MINOR_REVISION 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
, EMR3.MINOR_REVISION FROM_END_ITEM_MINOR_REV_LABEL
, EMR4.MINOR_REVISION 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
, DECODE( BOM_EXPLODER_PUB.GET_COMPONENT_ACCESS_FLAG(NVL(BE.PARENT_COMP_SEQ_ID
, 0))
, 'T'
, BOM_EXPLODER_PUB.GET_COMPONENT_ACCESS_FLAG(NVL(BE.COMPONENT_SEQUENCE_ID
, 0))
, BOM_EXPLODER_PUB.GET_COMPONENT_ACCESS_FLAG(NVL(BE.PARENT_COMP_SEQ_ID
, 0)) ) ACCESS_FLAG
, /* 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
, EMR5.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
, */ BOM_EXPLODER_PUB.GET_CHANGE_POLICY(NVL(BE.COMPONENT_SEQUENCE_ID
, 0)) AS 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
, BE.SOURCE_BILL_SEQUENCE_ID
, BE.COMMON_COMPONENT_SEQUENCE_ID
, BE.COMP_SOURCE_BILL_SEQ_ID
, DECODE(BE.COMPONENT_QUANTITY
, 0
, 0
, 1/BE.COMPONENT_QUANTITY) INVERSE_QUANTITY
, BE.COMP_EFFECTIVITY_CONTROL
, BIC.REVISED_ITEM_SEQUENCE_ID
, (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = BE.FROM_END_ITEM_REV_ID) FROM_END_ITEM_REVISION_CODE
, (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = BE.TO_END_ITEM_REV_ID) TO_END_ITEM_REVISION_CODE
, BIC.ENFORCE_INT_REQUIREMENTS
, BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(NVL(BE.PARENT_COMP_SEQ_ID
, 0)) PARENT_REVISION_CODE
, BOM_EXPLODER_PUB.GET_COMPONENT_REVISION_LABEL(NVL(BE.PARENT_COMP_SEQ_ID
, 0)) PARENT_REVISION_LABEL
, BOM_EXPLODER_PUB.GET_COMPONENT_REVISION_ID(NVL(BE.PARENT_COMP_SEQ_ID
, 0)) PARENT_REVISION_ID
, DECODE(BE.COMP_SOURCE_BILL_SEQ_ID
, BE.COMP_BILL_SEQ_ID
, NULL
, CASE WHEN (BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN (SELECT MSIKFV.CONCATENATED_SEGMENTS
FROM MTL_SYSTEM_ITEMS_KFV MSIKFV
, BOM_STRUCTURES_B BOM
WHERE MSIKFV.INVENTORY_ITEM_ID = BOM.PK1_VALUE
AND MSIKFV.ORGANIZATION_ID = BOM.PK2_VALUE
AND BOM.BILL_SEQUENCE_ID = BE.COMP_SOURCE_BILL_SEQ_ID ) WHEN BE.OBJ_NAME = 'DDD_CADVIEW' THEN (SELECT OBJECT_NAME
FROM DDD_MODEL_OBJECTS CADCOMP
, BOM_STRUCTURES_B BOM
WHERE CADCOMP.COMPONENT_ID = BOM.PK1_VALUE
AND BOM.BILL_SEQUENCE_ID = BE.COMP_SOURCE_BILL_SEQ_ID ) END ) COMMON_ITEM_NAME
, DECODE(BE.COMP_SOURCE_BILL_SEQ_ID
, BE.COMP_BILL_SEQ_ID
, NULL
, CASE WHEN (BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM') THEN (SELECT NAME
FROM HR_ALL_ORGANIZATION_UNITS_TL HRORG
, BOM_STRUCTURES_B BOM
WHERE HRORG.ORGANIZATION_ID = BOM.PK2_VALUE
AND BOM.BILL_SEQUENCE_ID = BE.COMP_SOURCE_BILL_SEQ_ID
AND HRORG.LANGUAGE = USERENV('LANG') ) WHEN BE.OBJ_NAME = 'DDD_CADVIEW' THEN (SELECT NULL
FROM DUAL ) END ) COMMON_ORGANIZATION_NAME
, (SELECT SPECIFIC_ASSEMBLY_COMMENT
FROM BOM_STRUCTURES_B
WHERE BILL_SEQUENCE_ID = BE.COMP_BILL_SEQ_ID ) AS STRUCTURE_DESC
, CASE WHEN ( BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM' ) THEN ( SELECT MSIB.STYLE_ITEM_ID
FROM MTL_SYSTEM_ITEMS_B MSIB
WHERE MSIB.INVENTORY_ITEM_ID = BE.PK1_VALUE
AND MSIB.ORGANIZATION_ID = BE.PK2_VALUE ) ELSE NULL END STYLE_ITEM_ID
, CASE WHEN ( BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM' ) THEN ( SELECT MSIB.STYLE_ITEM_FLAG
FROM MTL_SYSTEM_ITEMS_B MSIB
WHERE MSIB.INVENTORY_ITEM_ID = BE.PK1_VALUE
AND MSIB.ORGANIZATION_ID = BE.PK2_VALUE ) ELSE NULL END STYLE_ITEM_FLAG
FROM BOM_EXPLOSIONS_ALL BE
, BOM_COMPONENTS_B BIC
, EGO_MINOR_REVISIONS EMR1
, EGO_MINOR_REVISIONS EMR2
, EGO_MINOR_REVISIONS EMR3
, EGO_MINOR_REVISIONS EMR4
, EGO_MINOR_REVISIONS EMR5
WHERE BE.GROUP_ID = BOM_EXPLODER_PUB.GET_GROUP_ID
AND BE.COMPONENT_SEQUENCE_ID=BIC.COMPONENT_SEQUENCE_ID (+)
AND ( BOM_EXPLODER_PUB.GET_EXPLODE_OPTION = 1 OR BE.PLAN_LEVEL = 0 OR ( NVL(BE.EFFECTIVITY_CONTROL
, 1) =1 /* DATE EFFECTIVITY */
AND ( (BE.IMPLEMENTATION_DATE IS NULL
AND BE.ACD_TYPE = 3
AND DECODE(BE.COMP_FIXED_REVISION_ID
, NULL
, BOM_EXPLODER_PUB.GET_EXPLOSION_DATE
, BOM_EXPLODER_PUB.GET_REVISION_HIGHDATE(BE.COMP_FIXED_REVISION_ID)) >= BE.TRIMMED_EFFECTIVITY_DATE) OR (BOM_EXPLODER_PUB.GET_EXPLODE_OPTION = 2
AND DECODE(BE.COMP_FIXED_REVISION_ID
, NULL
, BOM_EXPLODER_PUB.GET_EXPLOSION_DATE
, BOM_EXPLODER_PUB.GET_REVISION_HIGHDATE(BE.COMP_FIXED_REVISION_ID)) >= BE.TRIMMED_EFFECTIVITY_DATE
AND DECODE(BE.COMP_FIXED_REVISION_ID
, NULL
, BOM_EXPLODER_PUB.GET_EXPLOSION_DATE
, BOM_EXPLODER_PUB.GET_REVISION_HIGHDATE(BE.COMP_FIXED_REVISION_ID)) < NVL(BE.TRIMMED_DISABLE_DATE
, TO_DATE('9999/12/31 00:00:00'
, 'YYYY/MM/DD HH24:MI:SS'))) OR (BOM_EXPLODER_PUB.GET_EXPLODE_OPTION = 3
AND DECODE(BE.COMP_FIXED_REVISION_ID
, NULL
, BOM_EXPLODER_PUB.GET_EXPLOSION_DATE
, BOM_EXPLODER_PUB.GET_REVISION_HIGHDATE(BE.COMP_FIXED_REVISION_ID)) < NVL(BE.TRIMMED_DISABLE_DATE
, TO_DATE('9999/12/31 00:00:00'
, 'YYYY/MM/DD HH24:MI:SS'))) ) ) OR ( NVL(BE.EFFECTIVITY_CONTROL
, 1) =4 /* REV EFFECTIVITY */
AND ( ( BOM_EXPLODER_PUB.GET_EXPLODE_OPTION = 2
AND ((BOM_EXPLODER_PUB.GET_EXPL_END_ITEM_REV_CODE >= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BE.END_ITEM_ID
AND ORGANIZATION_ID = BE.END_ITEM_ORG_ID
AND REVISION_ID = BE.FROM_END_ITEM_REV_ID)
AND (BE.TO_END_ITEM_REV_ID IS NULL OR BOM_EXPLODER_PUB.GET_EXPL_END_ITEM_REV_CODE <= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BE.END_ITEM_ID
AND ORGANIZATION_ID = BE.END_ITEM_ORG_ID
AND REVISION_ID = BE.TO_END_ITEM_REV_ID))) OR (BE.PLAN_LEVEL > 1
AND BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(NVL(BE.PARENT_COMP_SEQ_ID
, 0)) >= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BE.ASSEMBLY_ITEM_ID
AND ORGANIZATION_ID = BE.ORGANIZATION_ID
AND REVISION_ID = BE.FROM_END_ITEM_REV_ID)
AND (BE.TO_END_ITEM_REV_ID IS NULL OR BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(NVL(BE.PARENT_COMP_SEQ_ID
, 0)) <= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BE.ASSEMBLY_ITEM_ID
AND ORGANIZATION_ID = BE.ORGANIZATION_ID
AND REVISION_ID = BE.TO_END_ITEM_REV_ID))))) OR ( BOM_EXPLODER_PUB.GET_EXPLODE_OPTION = 3
AND ((BE.TO_END_ITEM_REV_ID IS NULL) OR (BOM_EXPLODER_PUB.GET_EXPL_END_ITEM_REV_CODE <= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BE.END_ITEM_ID
AND ORGANIZATION_ID = BE.END_ITEM_ORG_ID
AND REVISION_ID = BE.TO_END_ITEM_REV_ID) ) OR (BE.PLAN_LEVEL > 1
AND BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(NVL(BE.PARENT_COMP_SEQ_ID
, 0)) <= (SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE INVENTORY_ITEM_ID = BE.ASSEMBLY_ITEM_ID
AND ORGANIZATION_ID = BE.ORGANIZATION_ID
AND REVISION_ID = BE.TO_END_ITEM_REV_ID)))) ) ) OR ( NVL(BE.EFFECTIVITY_CONTROL
, 1) =2 /* UNIT/SERIAL EFFECTIVITY */
AND ( (BOM_EXPLODER_PUB.GET_EXPLODE_OPTION = 2
AND BOM_EXPLODER_PUB.GET_EXPL_UNIT_NUMBER BETWEEN BE.TRIMMED_FROM_UNIT_NUMBER
AND NVL(BE.TRIMMED_TO_UNIT_NUMBER
, BOM_EXPLODER_PUB.GET_EXPL_UNIT_NUMBER)) OR (BOM_EXPLODER_PUB.GET_EXPLODE_OPTION = 3
AND BOM_EXPLODER_PUB.GET_EXPL_UNIT_NUMBER <= NVL(BE.TRIMMED_TO_UNIT_NUMBER
, BOM_EXPLODER_PUB.GET_EXPL_UNIT_NUMBER)) ) ) )
AND BE.FROM_MINOR_REVISION_ID = EMR1.MINOR_REVISION_ID(+)
AND BE.TO_MINOR_REVISION_ID = EMR2.MINOR_REVISION_ID(+)
AND BE.FROM_END_ITEM_MINOR_REV_ID = EMR3.MINOR_REVISION_ID(+)
AND BE.TO_END_ITEM_MINOR_REV_ID = EMR4.MINOR_REVISION_ID(+)
AND EMR5.MINOR_REVISION_ID(+) = BE.MINOR_REVISION_ID UNION ALL SELECT BET.TOP_BILL_SEQUENCE_ID
, BET.BILL_SEQUENCE_ID
, BET.ORGANIZATION_ID
, BET.EXPLOSION_TYPE
, ATDOCS.SEQ_NUM COMPONENT_SEQUENCE_ID
, ATDOCS.ATTACHED_DOCUMENT_ID COMPONENT_ITEM_ID
, BET.PLAN_LEVEL +1 PLAN_LEVEL
, TO_NUMBER(NULL) EXTENDED_QUANTITY
, BET.SORT_ORDER||'99'||LPAD(ROWNUM
, 6
, '0') SORT_ORDER
, BET.CREATION_DATE
, BET.CREATED_BY
, ATDOCS.LAST_UPDATE_DATE
, ATDOCS.LAST_UPDATED_BY
, BET.TOP_ITEM_ID
, TO_CHAR(NULL) CONTEXT
, TO_CHAR(NULL) ATTRIBUTE1
, TO_CHAR(NULL) ATTRIBUTE2
, TO_CHAR(NULL) ATTRIBUTE3
, TO_CHAR(NULL) ATTRIBUTE4
, TO_CHAR(NULL) ATTRIBUTE5
, TO_CHAR(NULL) ATTRIBUTE6
, TO_CHAR(NULL) ATTRIBUTE7
, TO_CHAR(NULL) ATTRIBUTE8
, TO_CHAR(NULL) ATTRIBUTE9
, TO_CHAR(NULL) ATTRIBUTE10
, TO_CHAR(NULL) ATTRIBUTE11
, TO_CHAR(NULL) ATTRIBUTE12
, TO_CHAR(NULL) ATTRIBUTE13
, TO_CHAR(NULL) ATTRIBUTE14
, TO_CHAR(NULL) ATTRIBUTE15
, TO_NUMBER(NULL) BASIS_TYPE
, TO_NUMBER(1) COMPONENT_QUANTITY
, TO_NUMBER(NULL) SO_BASIS
, TO_NUMBER(NULL) OPTIONAL
, TO_NUMBER(NULL) MUTUALLY_EXCLUSIVE_OPTIONS
, TO_NUMBER(NULL) CHECK_ATP
, TO_NUMBER(NULL) SHIPPING_ALLOWED
, TO_NUMBER(NULL) REQUIRED_TO_SHIP
, TO_NUMBER(NULL) REQUIRED_FOR_REVENUE
, TO_NUMBER(NULL) INCLUDE_ON_SHIP_DOCS
, TO_NUMBER(NULL) INCLUDE_ON_BILL_DOCS
, TO_NUMBER(NULL) LOW_QUANTITY
, TO_NUMBER(NULL) HIGH_QUANTITY
, TO_NUMBER(NULL) PICK_COMPONENTS
, TO_CHAR(NULL) PRIMARY_UOM_CODE
, TO_CHAR(NULL) PRIMARY_UNIT_OF_MEASURE
, TO_NUMBER(NULL) BASE_ITEM_ID
, TO_CHAR(NULL) ATP_COMPONENTS_FLAG
, TO_CHAR(NULL) ATP_FLAG
, TO_NUMBER(NULL) BOM_ITEM_TYPE
, TO_CHAR(NULL) PICK_COMPONENTS_FLAG
, TO_CHAR(NULL) REPLENISH_TO_ORDER_FLAG
, TO_CHAR(NULL) SHIPPABLE_ITEM_FLAG
, TO_CHAR(NULL) CUSTOMER_ORDER_FLAG
, TO_CHAR(NULL) INTERNAL_ORDER_FLAG
, TO_CHAR(NULL) CUSTOMER_ORDER_ENABLED_FLAG
, TO_CHAR(NULL) INTERNAL_ORDER_ENABLED_FLAG
, TO_CHAR(NULL) SO_TRANSACTIONS_FLAG
, TO_CHAR(DOCTL.DESCRIPTION) DESCRIPTION
, BET.COMPONENT_ITEM_ID ASSEMBLY_ITEM_ID
, TO_CHAR(NULL) COMPONENT_CODE
, TO_NUMBER(NULL) LOOP_FLAG
, TO_NUMBER(NULL) PARENT_BOM_ITEM_TYPE
, TO_NUMBER(NULL) OPERATION_SEQ_NUM
, ATDOCS.ATTACHED_DOCUMENT_ID ITEM_NUM
, TO_DATE(NULL) EFFECTIVITY_DATE
, TO_DATE(NULL) DISABLE_DATE
, SYSDATE IMPLEMENTATION_DATE
, TO_NUMBER(NULL) REXPLODE_FLAG
, TO_NUMBER(NULL) COMMON_BILL_SEQUENCE_ID
, BET.COMP_BILL_SEQ_ID
, TO_NUMBER(NULL) COMP_COMMON_BILL_SEQ_ID
, BET.GROUP_ID
, TO_NUMBER(NULL) NUM_COL1
, TO_NUMBER(NULL) NUM_COL2
, TO_NUMBER(NULL) NUM_COL3
, TO_DATE(NULL) DATE_COL1
, TO_DATE(NULL) DATE_COL2
, TO_DATE(NULL) DATE_COL3
, TO_CHAR(NULL) CHAR_COL1
, TO_CHAR(NULL) CHAR_COL2
, TO_CHAR(NULL) CHAR_COL3
, TO_CHAR(NULL) AUTO_REQUEST_MATERIAL
, NVL(TO_CHAR(DOCTL.TITLE)
, TO_CHAR(DOCS.FILE_NAME)) ITEM_NAME
, ( SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE = 'FND_DM_ATTACHED_DOC_STATUS'
AND LOOKUP_CODE = ATDOCS.STATUS ) ITEM_STATUS
, TO_CHAR(NULL) IMAGE_SOURCE
, BOM_GLOBALS.GET_ALTERNATE(BET.BILL_SEQUENCE_ID) ALTERNATE_BOM_DESIGNATOR
, TO_CHAR(NULL) CURRENT_ITEM_REVISION
, TO_CHAR(NULL) REVISION_LABEL
, TO_NUMBER(NULL) INCLUDE_IN_COST_ROLLUP
, TO_NUMBER(NULL) COMPONENT_YIELD_FACTOR
, TO_NUMBER(NULL) PLANNING_FACTOR
, TO_CHAR(NULL) CHANGE_NOTICE
, TO_CHAR(NULL) STRUCTURE_TYPE
, 'DOCUMENT' USER_ITEM_TYPE
, TO_NUMBER(NULL) CHANGE_ID
, ( SELECT USER_NAME
FROM FND_DOCUMENT_CATEGORIES_VL
WHERE CATEGORY_ID = NVL(ATDOCS.CATEGORY_ID
, DOCS.CATEGORY_ID) ) CATALOG_CATEGORY
, TO_CHAR(NULL) ENG_ITEM_FLAG
, BET.SORT_ORDER PARENT_SORT_ORDER
, TO_CHAR(NULL) LONG_DESCRIPTION
, TO_CHAR(NULL) REVISION_CODE
, BET.STRUCTURE_TYPE_ID STRUCTURE_TYPE_ID
, TO_CHAR(NULL) SUGGESTED_VENDOR_NAME
, TO_NUMBER(NULL) VENDOR_ID
, TO_NUMBER(NULL) UNIT_PRICE
, TO_CHAR(ATDOCS.ATTACHED_DOCUMENT_ID) PK1_VALUE
, TO_CHAR(NULL) PK2_VALUE
, TO_CHAR(NULL) PK3_VALUE
, TO_CHAR(NULL) PK4_VALUE
, TO_CHAR(NULL) PK5_VALUE
, TO_CHAR(NULL) OBJ_NAME
, 'Y' HGRID_FLAG
, NVL(ATDOCS.CATEGORY_ID
, DOCS.CATEGORY_ID) CATALOG_CATEGORY_ID
, BET.REVISION_ID REVISION_ID
, TO_NUMBER(NULL) FROM_MINOR_REVISION_ID
, TO_NUMBER(NULL) TO_MINOR_REVISION_ID
, TO_CHAR(NULL) FROM_MINOR_REVISION_LABEL
, TO_CHAR(NULL) TO_MINOR_REVISION_LABEL
, TO_NUMBER(NULL) FROM_END_ITEM_REV_ID
, TO_NUMBER(NULL) TO_END_ITEM_REV_ID
, TO_CHAR(NULL) FROM_END_ITEM_REVISION_LABEL
, TO_CHAR(NULL) TO_END_ITEM_REVISION_LABEL
, TO_NUMBER(NULL) FROM_END_ITEM_MINOR_REV_ID
, TO_NUMBER(NULL) TO_END_ITEM_MINOR_REV_ID
, TO_CHAR(NULL) FROM_END_ITEM_MINOR_REV_LABEL
, TO_CHAR(NULL) TO_END_ITEM_MINOR_REV_LABEL
, TO_NUMBER(NULL) OBJECT_VERSION
, TO_CHAR(NULL) MAPPED_ITEM_NAME
, TO_CHAR(NULL) MAPPED_ITEM_REVISION
, TO_NUMBER(NULL) MAP_STATUS
, TO_CHAR(NULL) FROM_END_ITEM
, TO_CHAR(NULL) TO_END_ITEM
, BET.EFFECTIVITY_CONTROL EFFECTIVITY_CONTROL
, BOM_EXPLODER_PUB.GET_COMPONENT_ACCESS_FLAG(NVL(BET.COMPONENT_SEQUENCE_ID
, 0)) ACCESS_FLAG
, TO_CHAR(NULL) TEMPLATE_NAME
, TO_DATE(NULL) BOM_IMPLEMENTATION_DATE
, TO_CHAR(NULL) MINOR_REVISION
, TO_NUMBER(NULL) ASSEMBLY_TYPE
, TO_CHAR(NULL) GTIN_NUMBER
, TO_CHAR(NULL) GTIN_DESCRIPTION
, TO_CHAR(NULL) TRADE_ITEM_DESCRIPTOR
, TO_CHAR(NULL) TOP_GTIN_NUMBER
, TO_CHAR(NULL) TOP_GTIN_DESCRIPTION
, TO_CHAR(NULL) TOP_TRADE_ITEM_DESCRIPTOR
, TO_CHAR(NULL) PARENT_GTIN_NUMBER
, TO_CHAR(NULL) PARENT_GTIN_DESCRIPTION
, TO_CHAR(NULL) PARENT_TRADE_ITEM_DESCRIPTOR
, TO_NUMBER(NULL) QUANTITY_OF_CHILDREN
, TO_NUMBER(NULL) TOTAL_QTY_AT_NEXT_LEVEL
, TO_CHAR(NULL) IS_PREFERRED
, TO_CHAR(NULL) TRADE_ITEM_DESCRIPTOR_DESC
, TO_CHAR(NULL) GTIN_PUBLICATION_STATUS
, NVL(TO_CHAR(DOCTL.TITLE)
, TO_CHAR(DOCS.FILE_NAME)) ATTACHMENT_NAME
, TO_NUMBER(NULL) ACD_TYPE
, TO_NUMBER(NULL) QUANTITY_RELATED
, TO_CHAR(NULL) SUPPLY_SUBINVENTORY
, TO_CHAR(NULL) FROM_END_ITEM_UNIT_NUMBER
, TO_CHAR(NULL) TO_END_ITEM_UNIT_NUMBER
, TO_CHAR(NULL) STRUCTURE_DISPLAY_NAME
, TO_CHAR(NULL) CHANGE_POLICY_VALUE
, TO_CHAR(NULL) COMPONENT_REMARKS
, TO_CHAR(NULL) IS_EXCLUDED_BY_RULE
, TO_NUMBER(NULL) WIP_SUPPLY_TYPE
, TO_CHAR(NULL) LOCATOR
, BET.GROUP_ID
, SYSDATE PARENT_IMPLEMENTATION_DATE
, TO_CHAR(NULL) PARENT_CHANGE_NOTICE
, TO_CHAR(NULL) COMP_FIXED_REV_CODE
, TO_NUMBER(NULL) SOURCE_BILL_SEQUENCE_ID
, TO_NUMBER(NULL) COMMON_COMPONENT_SEQUENCE_ID
, TO_NUMBER(NULL) COMP_SOURCE_BILL_SEQ_ID
, TO_NUMBER(NULL) INVERSE_QUANTITY
, TO_NUMBER(NULL) COMP_EFFECTIVITY_CONTROL
, TO_NUMBER(NULL) REVISED_ITEM_SEQUENCE_ID
, TO_CHAR(NULL) FROM_END_ITEM_REVISION_CODE
, TO_CHAR(NULL) TO_END_ITEM_REVISION_CODE
, TO_NUMBER(NULL) ENFORCE_INT_REQUIREMENTS
, TO_CHAR(NULL) PARENT_REVISION_CODE
, TO_CHAR(NULL) PARENT_REVISION_LABEL
, TO_NUMBER(NULL) PARENT_REVISION_ID
, TO_CHAR(NULL) COMMON_ITEM_NAME
, TO_CHAR(NULL) COMMON_ORGANIZATION_NAME
, TO_CHAR(NULL) STRUCTURE_DESC
, TO_NUMBER(NULL) STYLE_ITEM_ID
, TO_CHAR(NULL) STYLE_ITEM_FLAG
FROM BOM_EXPLOSIONS_ALL BET
, BOM_STRUCTURES_B STRB
, FND_ATTACHED_DOCUMENTS ATDOCS
, FND_DOCUMENTS DOCS
, FND_DOCUMENTS_TL DOCTL
WHERE BET.GROUP_ID = BOM_EXPLODER_PUB.GET_GROUP_ID
AND ( ( ATDOCS.ENTITY_NAME = 'MTL_SYSTEM_ITEMS'
AND ATDOCS.PK1_VALUE = TO_CHAR(BET.ORGANIZATION_ID)
AND ATDOCS.PK2_VALUE = TO_CHAR(BET.COMPONENT_ITEM_ID) ) OR ( ATDOCS.ENTITY_NAME = 'MTL_ITEM_REVISIONS'
AND ATDOCS.PK1_VALUE = TO_CHAR(BET.ORGANIZATION_ID)
AND ATDOCS.PK2_VALUE = TO_CHAR(BET.COMPONENT_ITEM_ID)
AND ATDOCS.PK3_VALUE = TO_CHAR( BOM_EXPLODER_PUB.GET_COMPONENT_REVISION_ID(NVL(BET.COMPONENT_SEQUENCE_ID
, 0)) ) ) )
AND ATDOCS.DOCUMENT_ID = DOCS.DOCUMENT_ID
AND ATDOCS.DOCUMENT_ID = DOCTL.DOCUMENT_ID
AND DOCTL.LANGUAGE = USERENV('LANG')
AND BET.BILL_SEQUENCE_ID = STRB.BILL_SEQUENCE_ID
AND NVL(ATDOCS.CATEGORY_ID
, DOCS.CATEGORY_ID) IN ( SELECT BIA.ATTACH_CATEGORY_ID
FROM BOM_ITEM_ATTACH_CATEGORY_ASSOC BIA
WHERE BIA.STRUCTURE_TYPE_ID IN ( SELECT BST1.STRUCTURE_TYPE_ID
FROM BOM_STRUCTURE_TYPES_B BST1 CONNECT BY PRIOR BST1.PARENT_STRUCTURE_TYPE_ID = BST1.STRUCTURE_TYPE_ID START WITH BST1.STRUCTURE_TYPE_ID = STRB.STRUCTURE_TYPE_ID ) )