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_COPY_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.SORT_ORDER , BE.TOP_ITEM_ID , BE.COMPONENT_QUANTITY , BE.BOM_ITEM_TYPE , BE.PICK_COMPONENTS_FLAG , BE.REPLENISH_TO_ORDER_FLAG , BE.ASSEMBLY_ITEM_ID , BE.NEW_COMPONENT_CODE , BE.LOOP_FLAG , BE.PARENT_BOM_ITEM_TYPE , BE.EFFECTIVITY_DATE , BE.DISABLE_DATE , BE.IMPLEMENTATION_DATE , BE.COMMON_BILL_SEQUENCE_ID , BE.COMP_BILL_SEQ_ID , BE.COMP_COMMON_BILL_SEQ_ID , BE.GROUP_ID , MSIBK.CONCATENATED_SEGMENTS AS CONCATENATED_SEGMENTS , 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, BE.CHANGE_NOTICE , BE.CHANGE_ID AS CHANGE_ID, BE.ENG_ITEM_FLAG, BE.PARENT_SORT_ORDER , BOM_EXPLODER_PUB.Get_Component_Revision(NVL(BE.component_sequence_id,0)) REVISION_CODE, BE.STRUCTURE_TYPE_ID , BE.PK1_VALUE, BE.PK2_VALUE, BE.PK3_VALUE, BE.PK4_VALUE, BE.PK5_VALUE, BE.OBJ_NAME, 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, BE.FROM_END_ITEM_REV_ID, BE.TO_END_ITEM_REV_ID, BE.FROM_END_ITEM_MINOR_REV_ID, BE.TO_END_ITEM_MINOR_REV_ID, BE.COMP_EFFECTIVITY_CONTROL 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, BE.BOM_IMPLEMENTATION_DATE BOM_IMPLEMENTATION_DATE , 0 MINOR_REVISION, BE.ASSEMBLY_TYPE, BE.ACD_TYPE, BE.FROM_END_ITEM_UNIT_NUMBER, BE.TO_END_ITEM_UNIT_NUMBER , BE.CHANGE_POLICY_VALUE , NVL(BE.IS_EXCLUDED_BY_RULE, BOM_EXPLODER_PUB.Check_Excluded_By_Rule(BE.NEW_COMPONENT_CODE)), 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 , MSIBK.ITEM_CATALOG_GROUP_ID AS CATALOG_CATEGORY_ID, BE.COMMON_ORGANIZATION_ID, MSIBK.INVENTORY_ITEM_STATUS_CODE, be.creation_date, be.created_by, be.last_update_date, be.last_updated_by FROM BOM_EXPLOSIONS_ALL BE , mtl_system_items_b_kfv MSIBK WHERE BE.group_id = BOM_EXPLODER_PUB.get_group_id AND MSIBK.INVENTORY_ITEM_ID = BE.COMPONENT_ITEM_ID AND MSIBK.ORGANIZATION_ID = BE.ORGANIZATION_ID AND ( be.obj_name IS NULL OR be.obj_name = 'EGO_ITEM' ) 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 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 revision_id = BE.to_end_item_rev_id ) ) ) OR ( BE.plan_level > 1 AND BOM_EXPLODER_PUB.Get_Component_Revision(BE.parent_comp_seq_id) >= ( SELECT revision FROM mtl_item_revisions_b WHERE revision_id = BE.from_end_item_rev_id ) AND ( BE.to_end_item_rev_id IS NULL OR BOM_EXPLODER_PUB.Get_Component_Revision(BE.parent_comp_seq_id) <= ( SELECT revision FROM mtl_item_revisions_b WHERE 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 revision_id = BE.to_end_item_rev_id ) ) OR ( BE.plan_level > 1 AND BOM_EXPLODER_PUB.Get_Component_Revision(BE.parent_comp_seq_id) <= ( SELECT revision FROM mtl_item_revisions_b WHERE 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) ) ) ) )

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.SORT_ORDER
, BE.TOP_ITEM_ID
, BE.COMPONENT_QUANTITY
, BE.BOM_ITEM_TYPE
, BE.PICK_COMPONENTS_FLAG
, BE.REPLENISH_TO_ORDER_FLAG
, BE.ASSEMBLY_ITEM_ID
, BE.NEW_COMPONENT_CODE
, BE.LOOP_FLAG
, BE.PARENT_BOM_ITEM_TYPE
, BE.EFFECTIVITY_DATE
, BE.DISABLE_DATE
, BE.IMPLEMENTATION_DATE
, BE.COMMON_BILL_SEQUENCE_ID
, BE.COMP_BILL_SEQ_ID
, BE.COMP_COMMON_BILL_SEQ_ID
, BE.GROUP_ID
, MSIBK.CONCATENATED_SEGMENTS AS CONCATENATED_SEGMENTS
, 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
, BE.CHANGE_NOTICE
, BE.CHANGE_ID AS CHANGE_ID
, BE.ENG_ITEM_FLAG
, BE.PARENT_SORT_ORDER
, BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(NVL(BE.COMPONENT_SEQUENCE_ID
, 0)) REVISION_CODE
, BE.STRUCTURE_TYPE_ID
, BE.PK1_VALUE
, BE.PK2_VALUE
, BE.PK3_VALUE
, BE.PK4_VALUE
, BE.PK5_VALUE
, BE.OBJ_NAME
, 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
, BE.FROM_END_ITEM_REV_ID
, BE.TO_END_ITEM_REV_ID
, BE.FROM_END_ITEM_MINOR_REV_ID
, BE.TO_END_ITEM_MINOR_REV_ID
, BE.COMP_EFFECTIVITY_CONTROL 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
, BE.BOM_IMPLEMENTATION_DATE BOM_IMPLEMENTATION_DATE
, 0 MINOR_REVISION
, BE.ASSEMBLY_TYPE
, BE.ACD_TYPE
, BE.FROM_END_ITEM_UNIT_NUMBER
, BE.TO_END_ITEM_UNIT_NUMBER
, BE.CHANGE_POLICY_VALUE
, NVL(BE.IS_EXCLUDED_BY_RULE
, BOM_EXPLODER_PUB.CHECK_EXCLUDED_BY_RULE(BE.NEW_COMPONENT_CODE))
, 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
, MSIBK.ITEM_CATALOG_GROUP_ID AS CATALOG_CATEGORY_ID
, BE.COMMON_ORGANIZATION_ID
, MSIBK.INVENTORY_ITEM_STATUS_CODE
, BE.CREATION_DATE
, BE.CREATED_BY
, BE.LAST_UPDATE_DATE
, BE.LAST_UPDATED_BY
FROM BOM_EXPLOSIONS_ALL BE
, MTL_SYSTEM_ITEMS_B_KFV MSIBK
WHERE BE.GROUP_ID = BOM_EXPLODER_PUB.GET_GROUP_ID
AND MSIBK.INVENTORY_ITEM_ID = BE.COMPONENT_ITEM_ID
AND MSIBK.ORGANIZATION_ID = BE.ORGANIZATION_ID
AND ( BE.OBJ_NAME IS NULL OR BE.OBJ_NAME = 'EGO_ITEM' )
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 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 REVISION_ID = BE.TO_END_ITEM_REV_ID ) ) ) OR ( BE.PLAN_LEVEL > 1
AND BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(BE.PARENT_COMP_SEQ_ID) >= ( SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE REVISION_ID = BE.FROM_END_ITEM_REV_ID )
AND ( BE.TO_END_ITEM_REV_ID IS NULL OR BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(BE.PARENT_COMP_SEQ_ID) <= ( SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE 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 REVISION_ID = BE.TO_END_ITEM_REV_ID ) ) OR ( BE.PLAN_LEVEL > 1
AND BOM_EXPLODER_PUB.GET_COMPONENT_REVISION(BE.PARENT_COMP_SEQ_ID) <= ( SELECT REVISION
FROM MTL_ITEM_REVISIONS_B
WHERE 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) ) ) ) )