Product: | BOM - Bills of Material |
---|---|
Description: | View for Multi level copy structure |
Implementation/DBA Data: | APPS.BOM_COPY_EXPLOSIONS_V |
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) ) ) ) )