EGP_EXPLOSIONS_V

Details

• Schema: FUSION

• Object owner: EGP

• Object type: VIEW

Columns

Name

TOP_BILL_SEQUENCE_ID BILL_SEQUENCE_ID COMPONENT_SEQUENCE_ID PLAN_LEVEL CREATION_DATE CREATED_BY LAST_UPDATE_DATE LAST_UPDATED_BY TOP_ITEM_ID ATTRIBUTE_CATEGORY ATTRIBUTE1 ATTRIBUTE2 ATTRIBUTE3 ATTRIBUTE4 ATTRIBUTE5 ATTRIBUTE6 ATTRIBUTE7 ATTRIBUTE8 ATTRIBUTE9 ATTRIBUTE10 ATTRIBUTE11 ATTRIBUTE12 ATTRIBUTE13 ATTRIBUTE14 ATTRIBUTE15 ATTRIBUTE16 ATTRIBUTE17 ATTRIBUTE18 ATTRIBUTE19 ATTRIBUTE20 ATTRIBUTE21 ATTRIBUTE22 ATTRIBUTE23 ATTRIBUTE24 ATTRIBUTE25 ATTRIBUTE26 ATTRIBUTE27 ATTRIBUTE28 ATTRIBUTE29 ATTRIBUTE30 BASIS_TYPE COMPONENT_QUANTITY EXTENDED_QUANTITY SO_BASIS OPTIONAL MUTUALLY_EXCLUSIVE_OPTIONS CHECK_ATP SHIPPING_ALLOWED REQUIRED_TO_SHIP REQUIRED_FOR_REVENUE INCLUDE_ON_SHIP_DOCS INCLUDE_ON_BILL_DOCS LOW_QUANTITY HIGH_QUANTITY PICK_COMPONENTS COMPONENT_PRIMARY_UOM_CODE PRIMARY_UOM_CODE BASE_ITEM_ID ATP_COMPONENTS_FLAG ATP_FLAG BOM_ITEM_TYPE PICK_COMPONENTS_FLAG REPLENISH_TO_ORDER_FLAG SHIPPABLE_ITEM_FLAG CUSTOMER_ORDER_FLAG INTERNAL_ORDER_FLAG CUSTOMER_ORDER_ENABLED_FLAG INTERNAL_ORDER_ENABLED_FLAG SO_TRANSACTIONS_FLAG COMPONENT_PATH LOOP_FLAG PARENT_BOM_ITEM_TYPE OPERATION_SEQ_NUM ITEM_NUM EFFECTIVITY_DATE DISABLE_DATE IMPLEMENTATION_DATE COMMON_BILL_SEQUENCE_ID SOURCE_BILL_SEQUENCE_ID AUTO_REQUEST_MATERIAL ITEM_NAME ITEM_STATUS ALTERNATE_BOM_DESIGNATOR COMPONENT_YIELD_FACTOR PLANNING_FACTOR USER_ITEM_TYPE CHANGE_ID REVISION PK1_VALUE PK2_VALUE PK3_VALUE PK4_VALUE PK5_VALUE PARENT_PK1_VALUE PARENT_PK2_VALUE PARENT_PK3_VALUE PARENT_PK4_VALUE PARENT_PK5_VALUE OBJ_NAME PARENT_OBJ_NAME ITEM_CATALOG_GROUP_ID REVISION_ID FROM_END_ITEM_REV_ID TO_END_ITEM_REV_ID ITEMS_EFFECTIVITY_CONTROL ACD_TYPE QUANTITY_RELATED SUPPLY_SUBINVENTORY COMPONENT_REMARKS WIP_SUPPLY_TYPE LOCATOR COMP_FIXED_REV_CODE COMMON_COMPONENT_SEQUENCE_ID INVERSE_QUANTITY PARENT_EFFECTIVITY_CONTROL REVISED_ITEM_SEQUENCE_ID FROM_END_ITEM_REVISION_CODE TO_END_ITEM_REVISION_CODE ENFORCE_INT_REQUIREMENTS STRUCTURE_DESC STYLE_ITEM_ID STYLE_ITEM_FLAG EXPL_PLAN_LEVEL LINK_BY_BILL_SEQ EXPL_PLAN_LEVEL_1 FROM_END_ITEM_UNIT_NUMBER TO_END_ITEM_UNIT_NUMBER IS_LEAF PARENT_COMP_SEQ_ID COMP_SEQ_PATH PARENT_COMP_SEQ_PATH CURRENT_PHASE_ID TRADE_ITEM_DESCRIPTOR PRIMARY_UNIT_OF_MEASURE DESCRIPTION CURRENT_PHASE_CODE COMP_ID_PATH INSTANTIABILITY_CODE SHOW_IN_SALES ATTRIBUTE_NUMBER1 ATTRIBUTE_NUMBER2 ATTRIBUTE_NUMBER3 ATTRIBUTE_NUMBER4 ATTRIBUTE_NUMBER5 ATTRIBUTE_NUMBER6 ATTRIBUTE_NUMBER7 ATTRIBUTE_NUMBER8 ATTRIBUTE_NUMBER9 ATTRIBUTE_NUMBER10 ATTRIBUTE_DATE1 ATTRIBUTE_DATE2 ATTRIBUTE_DATE3 ATTRIBUTE_DATE4 ATTRIBUTE_DATE5 ATTRIBUTE_TIMESTAMP1 ATTRIBUTE_TIMESTAMP2 ATTRIBUTE_TIMESTAMP3 ATTRIBUTE_TIMESTAMP4 ATTRIBUTE_TIMESTAMP5 REPLACED_FOR_COMPONENT REPLACED_BY_COMPONENT REPLACED_FOR_COMP_SEQ_ID USE_PLACEHOLDER SUGGESTED_OPERATION_SEQ_NUM COMP_UNIQUE_PATH

Query

SQL_Statement

( (SELECT SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') top_bill_sequence_id , bill_sequence_id, 0 component_sequence_id, 0 plan_level, B.creation_date, B.created_by, B.last_update_date, B.last_updated_by, pk1_value top_item_id, B.attribute_category, B.attribute1, B.attribute2, B.attribute3, B.attribute4, B.attribute5, B.attribute6, B.attribute7, B.attribute8, B.attribute9, B.attribute10, B.attribute11, B.attribute12, B.attribute13, B.attribute14, B.attribute15, B.attribute16, B.attribute17, B.attribute18, B.attribute19, B.attribute20, B.attribute21, B.attribute22, B.attribute23, B.attribute24, B.attribute25, B.attribute26, B.attribute27, B.attribute28, B.attribute29, B.attribute30, NULL basis_type, NULL component_quantity, NULL extended_quantity, NULL so_basis, NULL optional, NULL mutually_exclusive_options, NULL check_atp, NULL shipping_allowed, NULL required_to_ship, NULL required_for_revenue, NULL include_on_ship_docs, NULL include_on_bill_docs, NULL low_quantity, NULL high_quantity, NULL pick_components, NULL component_primary_uom_code, I.primary_uom_code, I.BASE_ITEM_ID base_item_id, I.ATP_COMPONENTS_FLAG atp_components_flag, I.ATP_FLAG atp_flag, I.BOM_ITEM_TYPE bom_item_type, I.PICK_COMPONENTS_FLAG pick_components_flag, I.REPLENISH_TO_ORDER_FLAG replenish_to_order_flag, I.SHIPPABLE_ITEM_FLAG shippable_item_flag, I.CUSTOMER_ORDER_FLAG customer_order_flag, I.INTERNAL_ORDER_FLAG internal_order_flag, I.CUSTOMER_ORDER_ENABLED_FLAG customer_order_enabled_flag, I.INTERNAL_ORDER_ENABLED_FLAG internal_order_enabled_flag, I.SO_TRANSACTIONS_FLAG so_transactions_flag, NULL component_path, NULL loop_flag, NULL parent_bom_item_type, NULL operation_seq_num, NULL item_num, NULL effectivity_date, NULL disable_date, NULL implementation_date, common_bill_sequence_id, source_bill_sequence_id, /*bill_sequence_id comp_bill_seq_id, common_bill_sequence_id comp_common_bill_seq_id, source_bill_sequence_id comp_source_bill_seq_id,*/ NULL auto_request_material, (SELECT item_number FROM EGP_SYSTEM_ITEMS_B_V WHERE inventory_item_id = pk1_value AND organization_id =pk2_value ) item_name, I.APPROVAL_STATUS item_status, alternate_bom_designator, NULL component_yield_factor, NULL planning_factor, I.ITEM_TYPE user_item_type, NULL change_id, R.REVISION , pk1_value, SYS_CONTEXT('EXPL_CTX', 'CTX_ORG') AS PK2_VALUE , pk3_value, pk4_value, pk5_value, NULL parent_pk1_value, NULL parent_pk2_value, NULL parent_pk3_value, NULL parent_pk4_value, NULL parent_pk5_value, obj_name, NULL parent_obj_name, I.item_CATALOG_group_ID item_catalog_group_id, R.revision_id, NULL from_end_item_rev_id, NULL to_end_item_rev_id, I.effectivity_control items_effectivity_control, NULL acd_type, NULL quantity_related, NULL supply_subinventory, NULL component_remarks, NULL wip_supply_type, NULL locator, NULL comp_fixed_rev_code, NULL common_component_sequence_id, NULL inverse_quantity, NULL parent_effectivity_control, NULL revised_item_sequence_id, NULL from_end_item_revision_code, NULL to_end_item_revision_code, NULL enforce_int_requirements, specific_assembly_comment structure_desc, I.STYLE_ITEM_ID style_item_id, I.STYLE_ITEM_FLAG style_item_flag, 0 expl_plan_level , To_Number(SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') )link_by_bill_seq, 0 expl_plan_level_1, NULL from_end_item_unit_number, NULL to_end_item_unit_number, 0 IS_LEAF, 0 PARENT_COMP_SEQ_ID, SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') COMP_SEQ_PATH, NULL PARENT_COMP_SEQ_PATH, I.CURRENT_PHASE_ID CURRENT_PHASE_ID, I.TRADE_ITEM_DESCRIPTOR, NULL PRIMARY_UNIT_OF_MEASURE, I.DESCRIPTION, I.current_phase_code, NULL comp_id_path, NULL instantiability_code, NULL show_in_sales, NULL ATTRIBUTE_NUMBER1, NULL ATTRIBUTE_NUMBER2, NULL ATTRIBUTE_NUMBER3, NULL ATTRIBUTE_NUMBER4, NULL ATTRIBUTE_NUMBER5, NULL ATTRIBUTE_NUMBER6, NULL ATTRIBUTE_NUMBER7, NULL ATTRIBUTE_NUMBER8, NULL ATTRIBUTE_NUMBER9, NULL ATTRIBUTE_NUMBER10, NULL ATTRIBUTE_DATE1, NULL ATTRIBUTE_DATE2, NULL ATTRIBUTE_DATE3, NULL ATTRIBUTE_DATE4, NULL ATTRIBUTE_DATE5, NULL ATTRIBUTE_TIMESTAMP1, NULL ATTRIBUTE_TIMESTAMP2, NULL ATTRIBUTE_TIMESTAMP3, NULL ATTRIBUTE_TIMESTAMP4, NULL ATTRIBUTE_TIMESTAMP5, NULL replaced_for_component, NULL replaced_by_component, NULL replaced_for_comp_seq_id, NULL use_placeholder, NULL suggested_operation_seq_num, 'Root' COMP_UNIQUE_PATH FROM EGP_STRUCTURES_B B, EGP_ITEM_REVISIONS_B R, EGP_SYSTEM_ITEMS_ALL_V I WHERE B.bill_sequence_id = SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') AND B.PK1_VALUE = I.INVENTORY_ITEM_ID AND B.PK2_VALUE = I.ORGANIZATION_ID AND R.INVENTORY_ITEM_ID = I.INVENTORY_ITEM_ID AND R.ORGANIZATION_ID = I.ITEM_DEFINITION_ORG_ID AND (( (SYS_CONTEXT('EXPL_CTX', 'DS_USE_DATA_SECURITY') = 'N') OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '1') AND (1=1) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '2') AND (1=2) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '3') AND ( (NVL(I.PUBLIC_FLAG, 'N') = 'Y') OR (EXISTS (SELECT NULL FROM fnd_grants gnt WHERE exists ( SELECT /*+ index(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) no_unnest */ null FROM fnd_session_role_sets WHERE session_role_set_key = fnd_global.session_role_set_key and role_guid = gnt.grantee_key UNION ALL SELECT fnd_global.user_guid AS path FROM (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) WHERE fnd_global.user_guid = gnt.grantee_key ) AND exists (select /*+ no_unnest */ null from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID') and cmf.menu_id = gnt.menu_id ) AND gnt.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND gnt.grant_type = 'ALLOW' AND gnt.instance_type = 'SET' AND gnt.start_date <= SYSDATE and ( gnt.end_date is null or gnt.end_date >= sysdate) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( gnt.instance_set_id = SYS_CONTEXT('EXPL_CTX', 'DS_INSTANCE_SET_ID') AND TO_CHAR(I.INVENTORY_ORGANIZATION_ID) = GNT.PARAMETER1 AND GNT.PARAMETER2 IN (SELECT TO_CHAR(PARENT_ITEM_CLASS_ID) FROM EGP_IC_HIERARCHY WHERE ITEM_CLASS_ID = I.ITEM_CATALOG_GROUP_ID) ) ) ) OR (exists (select null from fnd_grants gnt where ( GNT.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND ( (GNT.grantee_key in ( select /*+ INDEX(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) */ role_guid as path from fnd_session_role_sets where session_role_set_key = fnd_global.session_role_set_key union all select fnd_global.user_guid as path from (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) ) ) ) AND GNT.menu_id in (select cmf.menu_id from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID')) AND GNT.grant_type = 'ALLOW' AND GNT.start_date <= sysdate AND ( GNT.end_date IS NULL OR GNT.end_date >= sysdate ) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( (GNT.INSTANCE_TYPE = 'INSTANCE') AND (GNT.INSTANCE_PK1_VALUE = TO_CHAR(I.INVENTORY_ITEM_ID)) AND (GNT.INSTANCE_PK2_VALUE = TO_CHAR(I.ORGANIZATION_ID)) ) ) ) ) ) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '4') AND ( (NVL(I.PUBLIC_FLAG, 'N') = 'Y') OR (EXISTS (SELECT NULL FROM fnd_grants gnt WHERE exists ( SELECT /*+ index(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) no_unnest */ null FROM fnd_session_role_sets WHERE session_role_set_key = fnd_global.session_role_set_key and role_guid = gnt.grantee_key UNION ALL SELECT fnd_global.user_guid AS path FROM (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) WHERE fnd_global.user_guid = gnt.grantee_key ) AND exists (select /*+ no_unnest */ null from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID') and cmf.menu_id = gnt.menu_id ) AND gnt.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND gnt.grant_type = 'ALLOW' AND gnt.instance_type = 'SET' AND gnt.start_date <= SYSDATE and ( gnt.end_date is null or gnt.end_date >= sysdate) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( gnt.instance_set_id = SYS_CONTEXT('EXPL_CTX', 'DS_INSTANCE_SET_ID') AND TO_CHAR(I.INVENTORY_ORGANIZATION_ID) = GNT.PARAMETER1 AND GNT.PARAMETER2 IN (SELECT TO_CHAR(PARENT_ITEM_CLASS_ID) FROM EGP_IC_HIERARCHY WHERE ITEM_CLASS_ID = I.ITEM_CATALOG_GROUP_ID) ) ) ) ) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '5') AND ( (NVL(I.PUBLIC_FLAG, 'N') = 'Y') OR (exists (select null from fnd_grants gnt where ( GNT.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND ( (GNT.grantee_key in ( select /*+ INDEX(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) */ role_guid as path from fnd_session_role_sets where session_role_set_key = fnd_global.session_role_set_key union all select fnd_global.user_guid as path from (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) ) ) ) AND GNT.menu_id in (select cmf.menu_id from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID')) AND GNT.grant_type = 'ALLOW' AND GNT.start_date <= sysdate AND ( GNT.end_date IS NULL OR GNT.end_date >= sysdate ) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( (GNT.INSTANCE_TYPE = 'INSTANCE') AND (GNT.INSTANCE_PK1_VALUE = TO_CHAR(I.INVENTORY_ITEM_ID)) AND (GNT.INSTANCE_PK2_VALUE = TO_CHAR(I.ORGANIZATION_ID)) ) ) ) ) ) ) OR ( (1=2) ) )) AND R.implementation_date IS NOT NULL AND R.effectivity_date <= To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') AND ( R.end_effectivity_date IS NULL OR R.end_effectivity_date > To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') ) ) UNION ALL (SELECT SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') top_bill_sequence_id, hky.bill_sequence_id, hky.component_sequence_id, hky.expl_plan_level plan_level, hky.creation_date, hky.CREATED_BY, hky.LAST_UPDATE_DATE, hky.last_updated_by, SYS_CONTEXT('EXPL_CTX', 'TOP_ITEM_ID') TOP_ITEM_ID, hky.attribute_category, hky.ATTRIBUTE1, hky.ATTRIBUTE2, hky.ATTRIBUTE3, hky.ATTRIBUTE4, hky.ATTRIBUTE5, hky.ATTRIBUTE6, hky.ATTRIBUTE7, hky.ATTRIBUTE8, hky.ATTRIBUTE9, hky.ATTRIBUTE10, hky.ATTRIBUTE11, hky.ATTRIBUTE12, hky.ATTRIBUTE13, hky.ATTRIBUTE14, hky.ATTRIBUTE15, hky.ATTRIBUTE16 , hky.ATTRIBUTE17 , hky.ATTRIBUTE18, hky.ATTRIBUTE19, hky.ATTRIBUTE20, hky.ATTRIBUTE21, hky.ATTRIBUTE22, hky.ATTRIBUTE23, hky.ATTRIBUTE24, hky.ATTRIBUTE25, hky.ATTRIBUTE26, hky.ATTRIBUTE27, hky.ATTRIBUTE28, hky.ATTRIBUTE29, hky.ATTRIBUTE30, hky.BASIS_TYPE , hky.COMPONENT_QUANTITY , hky.EXTENDED_QUANTITY, hky.SO_BASIS , hky.OPTIONAL , hky.MUTUALLY_EXCLUSIVE_OPTIONS , hky.CHECK_ATP , hky.SHIPPING_ALLOWED , hky.REQUIRED_TO_SHIP , hky.REQUIRED_FOR_REVENUE , hky.INCLUDE_ON_SHIP_DOCS , hky.INCLUDE_ON_BILL_DOCS , hky.LOW_QUANTITY , hky.HIGH_QUANTITY , hky.PICK_COMPONENTS , hky.PRIMARY_UOM_CODE component_primary_uom_code, msi.PRIMARY_UOM_CODE, msi.BASE_ITEM_ID , msi.ATP_COMPONENTS_FLAG , msi.ATP_FLAG , msi.BOM_ITEM_TYPE , msi.PICK_COMPONENTS_FLAG , msi.REPLENISH_TO_ORDER_FLAG , msi.SHIPPABLE_ITEM_FLAG , msi.CUSTOMER_ORDER_FLAG , msi.INTERNAL_ORDER_FLAG , msi.CUSTOMER_ORDER_ENABLED_FLAG , msi.INTERNAL_ORDER_ENABLED_FLAG , msi.SO_TRANSACTIONS_FLAG , hky.COMPONENT_PATH , hky.LOOP_FLAG , hky.PARENT_BOM_ITEM_TYPE , hky.OPERATION_SEQ_NUM , hky.item_num, hky.effectivity_date, hky.disable_date, hky.implementation_date, hky.common_bill_sequence_id, hky.source_bill_sequence_id, /*hky.comp_bill_seq_id1, hky.comp_common_bill_seq_id, hky.comp_source_bill_seq_id,*/ hky.AUTO_REQUEST_MATERIAL , msi.item_number AS ITEM_NAME, msi.APPROVAL_STATUS ITEM_STATUS, hky.ALTERNATE_BOM_DESIGNATOR , hky.COMPONENT_YIELD_FACTOR , hky.PLANNING_FACTOR , msi.ITEM_TYPE USER_ITEM_TYPE, hky.CHANGE_ID , mir.REVISION , hky.PK1_VALUE , SYS_CONTEXT('EXPL_CTX', 'CTX_ORG') AS PK2_VALUE , hky.PK3_VALUE , hky.PK4_VALUE , hky.PK5_VALUE , hky.PARENT_PK1_VALUE , hky.PARENT_PK2_VALUE , hky.PARENT_PK3_VALUE , hky.PARENT_PK4_VALUE , hky.PARENT_PK5_VALUE , hky.OBJ_NAME , hky.PARENT_OBJ_NAME , msi.item_CATALOG_group_ID , mir.REVISION_ID , hky.FROM_END_ITEM_REV_ID , hky.TO_END_ITEM_REV_ID , msi.EFFECTIVITY_CONTROL ITEMS_EFFECTIVITY_CONTROL , hky.ACD_TYPE , hky.QUANTITY_RELATED , hky.SUPPLY_SUBINVENTORY , hky.COMPONENT_REMARKS , hky.WIP_SUPPLY_TYPE , /* (SELECT FND_FLEX_EXT.GET_SEGS('INV','MTLL', iil.STRUCTURE_INSTANCE_NUMBER, iil.INVENTORY_LOCATION_ID, iil.SUBINVENTORY_ID) FROM INV_ITEM_LOCATIONS iil WHERE iil.ORGANIZATION_ID = hky.Pk2_Value AND iil.INVENTORY_LOCATION_ID = hky.Supply_Locator_Id ) AS LOCATOR,*/ NULL LOCATOR, /* DECODE(hky.component_item_revision_id, NULL, NULL, (SELECT revision FROM EGP_ITEM_REVISIONS_B WHERE revision_id = hky.component_item_revision_id )) AS COMP_FIXED_REV_CODE, */ NULL COMP_FIXED_REV_CODE, hky.COMMON_COMPONENT_SEQUENCE_ID , DECODE(hky.COMPONENT_QUANTITY, 0, 0, 1/hky.COMPONENT_QUANTITY) INVERSE_QUANTITY , hky.PARENT_EFFECTIVITY_CONTROL , hky.REVISED_ITEM_SEQUENCE_ID , DECODE (hky.FROM_END_ITEM_REV_ID, NULL, NULL, (SELECT revision FROM EGP_ITEM_REVISIONS_B WHERE revision_id = hky.FROM_END_ITEM_REV_ID )) AS FROM_END_ITEM_REVISION_CODE , DECODE (hky.TO_END_ITEM_REV_ID, NULL, NULL, (SELECT revision FROM EGP_ITEM_REVISIONS_B WHERE revision_id = hky.TO_END_ITEM_REV_ID )) AS TO_END_ITEM_REVISION_CODE , hky.ENFORCE_INT_REQUIREMENTS , hky.STRUCTURE_DESC , msi.STYLE_ITEM_ID , msi.STYLE_ITEM_FLAG , hky.expl_plan_level, link_by_bill_seq, expl_plan_level_1, hky.from_end_item_unit_number, hky.to_end_item_unit_number, hky.IS_LEAF, hky.PARENT_COMP_SEQ_ID, SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') || hky.comp_seq_path COMP_SEQ_PATH, hky.PARENT_COMP_SEQ_PATH, msi.CURRENT_PHASE_ID, msi.TRADE_ITEM_DESCRIPTOR, NULL PRIMARY_UNIT_OF_MEASURE, msi.DESCRIPTION, msi.current_phase_code, hky.comp_id_path, hky.instantiability_code, hky.show_in_sales show_in_sales, hky.ATTRIBUTE_NUMBER1, hky.ATTRIBUTE_NUMBER2, hky.ATTRIBUTE_NUMBER3, hky.ATTRIBUTE_NUMBER4, hky.ATTRIBUTE_NUMBER5, hky.ATTRIBUTE_NUMBER6, hky.ATTRIBUTE_NUMBER7, hky.ATTRIBUTE_NUMBER8, hky.ATTRIBUTE_NUMBER9, hky.ATTRIBUTE_NUMBER10, hky.ATTRIBUTE_DATE1, hky.ATTRIBUTE_DATE2, hky.ATTRIBUTE_DATE3, hky.ATTRIBUTE_DATE4, hky.ATTRIBUTE_DATE5, hky.ATTRIBUTE_TIMESTAMP1, hky.ATTRIBUTE_TIMESTAMP2, hky.ATTRIBUTE_TIMESTAMP3, hky.ATTRIBUTE_TIMESTAMP4, hky.ATTRIBUTE_TIMESTAMP5, hky.REPLACED_FOR_COMPONENT, hky.REPLACED_BY_COMPONENT, hky.REPLACED_FOR_COMP_SEQ_ID, hky.USE_PLACEHOLDER, hky.SUGGESTED_OPERATION_SEQ_NUM, 'Root' || hky.COMP_UNIQUE_PATH FROM EGP_SYSTEM_ITEMS_ALL_V msi, EGP_ITEM_REVISIONS_B mir, ( /*WITH str AS (SELECT to_number(bsb.pk1_value) assembly_id, msi.item_number itemName, msi.bom_item_type, msi.pick_components_flag, bsb.* FROM EGP_STRUCTURES_B bsb, EGP_SYSTEM_ITEMS_B_V msi WHERE bsb.alternate_bom_designator=SYS_CONTEXT('EXPL_CTX', 'TOP_ALT') AND bsb.pk2_value = SYS_CONTEXT('EXPL_CTX', 'CTX_ORG') AND bsb.pk1_value = msi.inventory_item_id AND bsb.pk2_value = msi.organization_id ) */ SELECT bcb.*, DECODE ( bcb.basis_type, 1, egp_exploder_pub.calculate_extended_quantity ( SUBSTR(SYS_CONNECT_BY_PATH (((bcb.COMPONENT_QUANTITY * (bcb.PLANNING_FACTOR/100)) / bcb.COMPONENT_YIELD_FACTOR), '*'),2) ), bcb.COMPONENT_QUANTITY * (bcb.PLANNING_FACTOR/100) / bcb.COMPONENT_YIELD_FACTOR ) EXTENDED_QUANTITY, bsb.pk2_value bill_org_id, bsb.common_bill_sequence_id, bsb.source_bill_sequence_id, bsb.obj_name PARENT_OBJ_NAME, bsb.pk1_value PARENT_PK1_VALUE, bsb.pk2_value PARENT_PK2_VALUE, bsb.pk3_value PARENT_PK3_VALUE, bsb.pk4_value PARENT_PK4_VALUE, bsb.pk5_value PARENT_PK5_VALUE, bsb.effectivity_control PARENT_EFFECTIVITY_CONTROL, bsb.specific_assembly_comment STRUCTURE_DESC, bsb.alternate_bom_designator, PRIOR bcb.implementation_date PARENT_IMPLEMENTATION_DATE, PRIOR bcb.CHANGE_ID PARENT_CHANGE_ID, PRIOR bcb.bom_item_type PARENT_BOM_ITEM_TYPE, SUBSTR(SYS_CONNECT_BY_PATH(MSI2.item_number, '> '),2,LENGTH(SYS_CONNECT_BY_PATH(MSI2.item_number, '> '))) component_path , SUBSTR(SYS_CONNECT_BY_PATH(BSB.pk1_value, '> '),2,LENGTH(SYS_CONNECT_BY_PATH(BSB.pk1_value, '> '))) comp_id_path , connect_by_isleaf IS_LEAF , LEVEL expl_plan_level, connect_by_iscycle loop_flag, To_Number(SUBSTR ( regexp_substr(sys_connect_by_path(DECODE(NVL(PRIOR bcb.obj_name,'EGO_ITEM'),'EGO_ITEM_CLASS',9,0) ||'-' ||lpad(bcb.bill_sequence_id, 20, '0'), '='),'((=[^9]-)[0-9]*)((=9-)[0-9]*)*$'),4,20)) link_by_bill_seq, (LEVEL - regexp_count(sys_connect_by_path(DECODE(NVL(PRIOR bcb.obj_name,'EGO_ITEM'),'EGO_ITEM_CLASS',1,0),'-'), '-1')) expl_plan_level_1, PRIOR bcb.component_sequence_id PARENT_COMP_SEQ_ID, SYS_CONNECT_BY_PATH(BCB.component_sequence_id, '-') comp_seq_path, SUBSTR(SYS_CONNECT_BY_PATH(BCB.component_sequence_id, '-'),1, LENGTH(SYS_CONNECT_BY_PATH(BCB.component_sequence_id, '-')) - (LENGTH(BCB.component_sequence_id)+ 1)) PARENT_COMP_SEQ_PATH, SYS_CONNECT_BY_PATH(BCB.pk1_value || '~' || BCB.operation_seq_num, '-') COMP_UNIQUE_PATH FROM /*str bsb, EGP_COMPONENTS_B bcb WHERE bsb.alternate_bom_designator = SYS_CONTEXT('EXPL_CTX', 'TOP_ALT') AND ( ( bsb.obj_name ='EGO_ITEM' AND bsb.pk2_value = SYS_CONTEXT('EXPL_CTX', 'CTX_ORG') ) OR ( bsb.obj_name ='EGO_ITEM_CLASS' ) ) AND bcb.bill_sequence_id = bsb.common_bill_sequence_id */ EGP_STRUCTURES_B bsb, EGP_COMPONENTS_B bcb, EGP_SYSTEM_ITEMS_B_V msi2 WHERE bsb.alternate_bom_designator = SYS_CONTEXT('EXPL_CTX', 'TOP_ALT') AND bsb.pk2_value = SYS_CONTEXT('EXPL_CTX', 'CTX_ORG') AND bsb.pk1_value = msi2.inventory_item_id AND bsb.pk2_value = msi2.organization_id AND bcb.bill_sequence_id = bsb.common_bill_sequence_id START WITH ( bsb.pk1_value = SYS_CONTEXT('EXPL_CTX', 'TOP_ITEM_ID') AND bsb.pk2_value = SYS_CONTEXT('EXPL_CTX', 'CTX_ORG') AND bsb.alternate_bom_designator = SYS_CONTEXT('EXPL_CTX', 'TOP_ALT') AND (( (SYS_CONTEXT('EXPL_CTX', 'DS_USE_DATA_SECURITY') = 'N') OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '1') AND (1=1) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '2') AND (1=2) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '3') AND ( (NVL(msi2.PUBLIC_FLAG, 'N') = 'Y') OR (EXISTS (SELECT NULL FROM fnd_grants gnt WHERE exists ( SELECT /*+ index(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) no_unnest */ null FROM fnd_session_role_sets WHERE session_role_set_key = fnd_global.session_role_set_key and role_guid = gnt.grantee_key UNION ALL SELECT fnd_global.user_guid AS path FROM (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) WHERE fnd_global.user_guid = gnt.grantee_key ) AND exists (select /*+ no_unnest */ null from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID') and cmf.menu_id = gnt.menu_id ) AND gnt.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND gnt.grant_type = 'ALLOW' AND gnt.instance_type = 'SET' AND gnt.start_date <= SYSDATE and ( gnt.end_date is null or gnt.end_date >= sysdate) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( gnt.instance_set_id = SYS_CONTEXT('EXPL_CTX', 'DS_INSTANCE_SET_ID') AND TO_CHAR(msi2.INVENTORY_ORGANIZATION_ID) = GNT.PARAMETER1 AND GNT.PARAMETER2 IN (SELECT TO_CHAR(PARENT_ITEM_CLASS_ID) FROM EGP_IC_HIERARCHY WHERE ITEM_CLASS_ID = msi2.ITEM_CATALOG_GROUP_ID) ) ) ) OR (exists (select null from fnd_grants gnt where ( GNT.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND ( (GNT.grantee_key in ( select /*+ INDEX(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) */ role_guid as path from fnd_session_role_sets where session_role_set_key = fnd_global.session_role_set_key union all select fnd_global.user_guid as path from (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) ) ) ) AND GNT.menu_id in (select cmf.menu_id from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID')) AND GNT.grant_type = 'ALLOW' AND GNT.start_date <= sysdate AND ( GNT.end_date IS NULL OR GNT.end_date >= sysdate ) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( (GNT.INSTANCE_TYPE = 'INSTANCE') AND (GNT.INSTANCE_PK1_VALUE = TO_CHAR(msi2.INVENTORY_ITEM_ID)) AND (GNT.INSTANCE_PK2_VALUE = TO_CHAR(msi2.ORGANIZATION_ID)) ) ) ) ) ) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '4') AND ( (NVL(msi2.PUBLIC_FLAG, 'N') = 'Y') OR (EXISTS (SELECT NULL FROM fnd_grants gnt WHERE exists ( SELECT /*+ index(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) no_unnest */ null FROM fnd_session_role_sets WHERE session_role_set_key = fnd_global.session_role_set_key and role_guid = gnt.grantee_key UNION ALL SELECT fnd_global.user_guid AS path FROM (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) WHERE fnd_global.user_guid = gnt.grantee_key ) AND exists (select /*+ no_unnest */ null from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID') and cmf.menu_id = gnt.menu_id ) AND gnt.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND gnt.grant_type = 'ALLOW' AND gnt.instance_type = 'SET' AND gnt.start_date <= SYSDATE and ( gnt.end_date is null or gnt.end_date >= sysdate) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( gnt.instance_set_id = SYS_CONTEXT('EXPL_CTX', 'DS_INSTANCE_SET_ID') AND TO_CHAR(msi2.INVENTORY_ORGANIZATION_ID) = GNT.PARAMETER1 AND GNT.PARAMETER2 IN (SELECT TO_CHAR(PARENT_ITEM_CLASS_ID) FROM EGP_IC_HIERARCHY WHERE ITEM_CLASS_ID = msi2.ITEM_CATALOG_GROUP_ID) ) ) ) ) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '5') AND ( (NVL(msi2.PUBLIC_FLAG, 'N') = 'Y') OR (exists (select null from fnd_grants gnt where ( GNT.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND ( (GNT.grantee_key in ( select /*+ INDEX(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) */ role_guid as path from fnd_session_role_sets where session_role_set_key = fnd_global.session_role_set_key union all select fnd_global.user_guid as path from (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) ) ) ) AND GNT.menu_id in (select cmf.menu_id from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID')) AND GNT.grant_type = 'ALLOW' AND GNT.start_date <= sysdate AND ( GNT.end_date IS NULL OR GNT.end_date >= sysdate ) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( (GNT.INSTANCE_TYPE = 'INSTANCE') AND (GNT.INSTANCE_PK1_VALUE = TO_CHAR(msi2.INVENTORY_ITEM_ID)) AND (GNT.INSTANCE_PK2_VALUE = TO_CHAR(msi2.ORGANIZATION_ID)) ) ) ) ) ) ) OR ( (1=2) ) )) AND ( SYS_CONTEXT('EXPL_CTX', 'TOP_EFF_CTRL') = 1 AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') = 2 AND bcb.effectivity_date <= To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') AND ( bcb.disable_date IS NULL OR bcb.disable_date > To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') ) ) OR SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') =3 AND ( bcb.disable_date IS NULL OR bcb.disable_date > To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') ) OR SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') =1 ) OR ( SYS_CONTEXT('EXPL_CTX', 'TOP_EFF_CTRL') = 4 AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=2 AND bcb.disable_date IS NULL AND EXISTS (SELECT NULL FROM EGP_ITEM_REVISIONS_B mir1 WHERE mir1.revision_id = bcb.from_end_item_rev_id AND To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') BETWEEN mir1.effectivity_date AND mir1.END_EFFECTIVITY_DATE ) ) OR ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=3 AND ( bcb.to_end_item_rev_id IS NULL OR EXISTS (SELECT NULL FROM EGP_ITEM_REVISIONS_B mir2 WHERE mir2.revision_id = bcb.to_end_item_rev_id AND (mir2.END_EFFECTIVITY_DATE IS NULL OR mir2.END_EFFECTIVITY_DATE >= To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS')) ) ) ) OR ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=1 ) ) ) OR ( SYS_CONTEXT('EXPL_CTX', 'TOP_EFF_CTRL') IN (2,3) AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=2 AND bcb.disable_date IS NULL AND bcb.from_end_item_unit_number <= SYS_CONTEXT('EXPL_CTX', 'UNIT_NUMBER') AND ( bcb.to_end_item_unit_number IS NULL OR bcb.to_end_item_unit_number >= SYS_CONTEXT('EXPL_CTX', 'UNIT_NUMBER') ) ) OR ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') =3 AND bcb.disable_date IS NULL AND ( bcb.to_end_item_unit_number IS NULL OR bcb.to_end_item_unit_number >= SYS_CONTEXT('EXPL_CTX', 'UNIT_NUMBER') ) ) OR SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') =1 ) ) ) AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPL_TYPE')=2 AND MSI2.PICK_COMPONENTS_FLAG = 'Y' AND BCB.bom_item_type = 4 AND BCB.OPTIONAL = 2 ) OR ( SYS_CONTEXT('EXPL_CTX', 'EXPL_TYPE') =1 AND MSI2.BOM_ITEM_TYPE IN (1,2) AND ( BCB.BOM_ITEM_TYPE IN (1,2) OR ( BCB.BOM_ITEM_TYPE = 4 AND BCB.OPTIONAL = 1 ) ) ) OR (SYS_CONTEXT('EXPL_CTX', 'EXPL_TYPE') =3 /*'BOTH'*/ ) ) AND ( SYS_CONTEXT('EXPL_CTX', 'IMPL_ONLY') = 1 AND BCB.implementation_date IS NOT NULL OR ( SYS_CONTEXT('EXPL_CTX', 'IMPL_ONLY') = 2 AND ( BCB.implementation_date IS NOT NULL OR ( SYS_CONTEXT('EXPL_CTX', 'VERSION_ID') = -1 AND SYS_CONTEXT('EXPL_CTX', 'CHANGE_LINE_ID') = -1 ) OR ( SYS_CONTEXT('EXPL_CTX', 'VERSION_ID') <> -1 AND BCB.version_id = SYS_CONTEXT('EXPL_CTX', 'VERSION_ID') AND BCB.acd_type <> 3 ) OR ( SYS_CONTEXT('EXPL_CTX', 'CHANGE_LINE_ID') <> -1 AND BCB.change_line_id = SYS_CONTEXT('EXPL_CTX', 'CHANGE_LINE_ID') AND BCB.acd_type <> 3 ) ) ) ) ) CONNECT BY NOCYCLE (PRIOR bcb.pk1_value = bsb.pk1_value) AND bsb.alternate_bom_designator = SYS_CONTEXT('EXPL_CTX', 'TOP_ALT') AND bsb.pk2_value = SYS_CONTEXT('EXPL_CTX', 'CTX_ORG') AND (( (SYS_CONTEXT('EXPL_CTX', 'DS_USE_DATA_SECURITY') = 'N') OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '1') AND (1=1) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '2') AND (1=2) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '3') AND ( (NVL(msi2.PUBLIC_FLAG, 'N') = 'Y') OR (EXISTS (SELECT NULL FROM fnd_grants gnt WHERE exists ( SELECT /*+ index(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) no_unnest */ null FROM fnd_session_role_sets WHERE session_role_set_key = fnd_global.session_role_set_key and role_guid = gnt.grantee_key UNION ALL SELECT fnd_global.user_guid AS path FROM (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) WHERE fnd_global.user_guid = gnt.grantee_key ) AND exists (select /*+ no_unnest */ null from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID') and cmf.menu_id = gnt.menu_id ) AND gnt.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND gnt.grant_type = 'ALLOW' AND gnt.instance_type = 'SET' AND gnt.start_date <= SYSDATE and ( gnt.end_date is null or gnt.end_date >= sysdate) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( gnt.instance_set_id = SYS_CONTEXT('EXPL_CTX', 'DS_INSTANCE_SET_ID') AND TO_CHAR(msi2.INVENTORY_ORGANIZATION_ID) = GNT.PARAMETER1 AND GNT.PARAMETER2 IN (SELECT TO_CHAR(PARENT_ITEM_CLASS_ID) FROM EGP_IC_HIERARCHY WHERE ITEM_CLASS_ID = msi2.ITEM_CATALOG_GROUP_ID) ) ) ) OR (exists (select null from fnd_grants gnt where ( GNT.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND ( (GNT.grantee_key in ( select /*+ INDEX(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) */ role_guid as path from fnd_session_role_sets where session_role_set_key = fnd_global.session_role_set_key union all select fnd_global.user_guid as path from (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) ) ) ) AND GNT.menu_id in (select cmf.menu_id from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID')) AND GNT.grant_type = 'ALLOW' AND GNT.start_date <= sysdate AND ( GNT.end_date IS NULL OR GNT.end_date >= sysdate ) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( (GNT.INSTANCE_TYPE = 'INSTANCE') AND (GNT.INSTANCE_PK1_VALUE = TO_CHAR(msi2.INVENTORY_ITEM_ID)) AND (GNT.INSTANCE_PK2_VALUE = TO_CHAR(msi2.ORGANIZATION_ID)) ) ) ) ) ) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '4') AND ( (NVL(msi2.PUBLIC_FLAG, 'N') = 'Y') OR (EXISTS (SELECT NULL FROM fnd_grants gnt WHERE exists ( SELECT /*+ index(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) no_unnest */ null FROM fnd_session_role_sets WHERE session_role_set_key = fnd_global.session_role_set_key and role_guid = gnt.grantee_key UNION ALL SELECT fnd_global.user_guid AS path FROM (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) WHERE fnd_global.user_guid = gnt.grantee_key ) AND exists (select /*+ no_unnest */ null from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID') and cmf.menu_id = gnt.menu_id ) AND gnt.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND gnt.grant_type = 'ALLOW' AND gnt.instance_type = 'SET' AND gnt.start_date <= SYSDATE and ( gnt.end_date is null or gnt.end_date >= sysdate) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( gnt.instance_set_id = SYS_CONTEXT('EXPL_CTX', 'DS_INSTANCE_SET_ID') AND TO_CHAR(msi2.INVENTORY_ORGANIZATION_ID) = GNT.PARAMETER1 AND GNT.PARAMETER2 IN (SELECT TO_CHAR(PARENT_ITEM_CLASS_ID) FROM EGP_IC_HIERARCHY WHERE ITEM_CLASS_ID = msi2.ITEM_CATALOG_GROUP_ID) ) ) ) ) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '5') AND ( (NVL(msi2.PUBLIC_FLAG, 'N') = 'Y') OR (exists (select null from fnd_grants gnt where ( GNT.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND ( (GNT.grantee_key in ( select /*+ INDEX(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) */ role_guid as path from fnd_session_role_sets where session_role_set_key = fnd_global.session_role_set_key union all select fnd_global.user_guid as path from (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) ) ) ) AND GNT.menu_id in (select cmf.menu_id from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID')) AND GNT.grant_type = 'ALLOW' AND GNT.start_date <= sysdate AND ( GNT.end_date IS NULL OR GNT.end_date >= sysdate ) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( (GNT.INSTANCE_TYPE = 'INSTANCE') AND (GNT.INSTANCE_PK1_VALUE = TO_CHAR(msi2.INVENTORY_ITEM_ID)) AND (GNT.INSTANCE_PK2_VALUE = TO_CHAR(msi2.ORGANIZATION_ID)) ) ) ) ) ) ) OR ( (1=2) ) )) /* CZ changes to support the include_model parameter */ AND ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_MODEL') = 1 OR bcb.bill_sequence_id = SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') OR PRIOR bcb.bom_item_type <> 1 ) AND ( ( /*std_item_flag = 1*/ SYS_CONTEXT('EXPL_CTX', 'STD_ITEM_FLAG') = 1 AND bcb.bom_item_type = 4 ) OR /*std_item_flag = 1*/ SYS_CONTEXT('EXPL_CTX', 'STD_ITEM_FLAG')=2 ) AND ( bsb.effectivity_control = 1 AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=2 AND bcb.effectivity_date <= To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') AND ( bcb.disable_date IS NULL OR bcb.disable_date > To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') ) ) OR SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') =3 AND ( bcb.disable_date IS NULL OR bcb.disable_date > To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') ) OR SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') =1 ) OR ( bsb.effectivity_control = 4 AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=2 AND bcb.disable_date IS NULL AND EXISTS (SELECT NULL FROM EGP_ITEM_REVISIONS_B mir1 WHERE mir1.revision_id = bcb.from_end_item_rev_id AND To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS') BETWEEN mir1.END_EFFECTIVITY_DATE AND mir1.effectivity_date ) ) OR ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=3 AND ( bcb.to_end_item_rev_id IS NULL OR bcb.from_end_item_rev_id = bcb.to_end_item_rev_id OR NOT EXISTS (SELECT NULL FROM EGP_ITEM_REVISIONS_B mir2 WHERE mir2.revision_id = bcb.to_end_item_rev_id AND (mir2.END_EFFECTIVITY_DATE IS NULL OR mir2.END_EFFECTIVITY_DATE >= To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS')) ) ) ) OR ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=1 ) ) ) OR ( bsb.effectivity_control IN (2,3) AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION')=2 AND bcb.disable_date IS NULL AND bcb.from_end_item_unit_number <= SYS_CONTEXT('EXPL_CTX', 'UNIT_NUMBER') AND ( bcb.to_end_item_unit_number IS NULL OR bcb.to_end_item_unit_number >= SYS_CONTEXT('EXPL_CTX', 'UNIT_NUMBER') ) ) OR ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') =3 AND bcb.disable_date IS NULL AND ( bcb.to_end_item_unit_number IS NULL OR bcb.to_end_item_unit_number >= SYS_CONTEXT('EXPL_CTX', 'UNIT_NUMBER') ) ) OR SYS_CONTEXT('EXPL_CTX', 'EXPLODE_OPTION') =1 ) ) ) AND ( SYS_CONTEXT('EXPL_CTX', 'IMPL_ONLY') = 1 AND BCB.implementation_date IS NOT NULL OR ( SYS_CONTEXT('EXPL_CTX', 'IMPL_ONLY') = 2 AND ( BCB.implementation_date IS NOT NULL OR ( SYS_CONTEXT('EXPL_CTX', 'VERSION_ID') = -1 AND SYS_CONTEXT('EXPL_CTX', 'CHANGE_LINE_ID') = -1 ) OR ( SYS_CONTEXT('EXPL_CTX', 'VERSION_ID') <> -1 AND BCB.version_id = SYS_CONTEXT('EXPL_CTX', 'VERSION_ID') AND BCB.acd_type <> 3 ) OR ( SYS_CONTEXT('EXPL_CTX', 'CHANGE_LINE_ID') <> -1 AND BCB.change_line_id = SYS_CONTEXT('EXPL_CTX', 'CHANGE_LINE_ID') AND BCB.acd_type <> 3 ) ) ) ) AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPL_TYPE') =2 AND PRIOR BCB.PICK_COMPONENTS = 'Y' AND BCB.bom_item_type = 4 AND BCB.OPTIONAL = 2 ) OR ( SYS_CONTEXT('EXPL_CTX', 'EXPL_TYPE') =1 AND PRIOR BCB.BOM_ITEM_TYPE IN (1,2) AND ( BCB.BOM_ITEM_TYPE IN (1,2) OR ( BCB.BOM_ITEM_TYPE = 4 AND BCB.OPTIONAL = 1 ) ) ) OR (SYS_CONTEXT('EXPL_CTX', 'EXPL_TYPE') =3 /*'BOTH'*/ ) ) AND ( ( SYS_CONTEXT('EXPL_CTX', 'EXPLODE_ONLY_PHANTOM_SUBASSY')=1 AND ( ( bcb.bill_sequence_id <> SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') AND PRIOR bcb.wip_supply_type = 6 ) OR bcb.bill_sequence_id =SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') ) ) OR SYS_CONTEXT('EXPL_CTX', 'EXPLODE_ONLY_PHANTOM_SUBASSY') = 2 ) ) hky WHERE msi.inventory_item_id =hky.pk1_value AND msi.organization_id =hky.bill_org_id AND (( (SYS_CONTEXT('EXPL_CTX', 'DS_USE_DATA_SECURITY') = 'N') OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '1') AND (1=1) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '2') AND (1=2) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '3') AND ( (NVL(msi.PUBLIC_FLAG, 'N') = 'Y') OR (EXISTS (SELECT NULL FROM fnd_grants gnt WHERE exists ( SELECT /*+ index(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) no_unnest */ null FROM fnd_session_role_sets WHERE session_role_set_key = fnd_global.session_role_set_key and role_guid = gnt.grantee_key UNION ALL SELECT fnd_global.user_guid AS path FROM (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) WHERE fnd_global.user_guid = gnt.grantee_key ) AND exists (select /*+ no_unnest */ null from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID') and cmf.menu_id = gnt.menu_id ) AND gnt.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND gnt.grant_type = 'ALLOW' AND gnt.instance_type = 'SET' AND gnt.start_date <= SYSDATE and ( gnt.end_date is null or gnt.end_date >= sysdate) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( gnt.instance_set_id = SYS_CONTEXT('EXPL_CTX', 'DS_INSTANCE_SET_ID') AND TO_CHAR(msi.INVENTORY_ORGANIZATION_ID) = GNT.PARAMETER1 AND GNT.PARAMETER2 IN (SELECT TO_CHAR(PARENT_ITEM_CLASS_ID) FROM EGP_IC_HIERARCHY WHERE ITEM_CLASS_ID = msi.ITEM_CATALOG_GROUP_ID) ) ) ) OR (exists (select null from fnd_grants gnt where ( GNT.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND ( (GNT.grantee_key in ( select /*+ INDEX(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) */ role_guid as path from fnd_session_role_sets where session_role_set_key = fnd_global.session_role_set_key union all select fnd_global.user_guid as path from (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) ) ) ) AND GNT.menu_id in (select cmf.menu_id from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID')) AND GNT.grant_type = 'ALLOW' AND GNT.start_date <= sysdate AND ( GNT.end_date IS NULL OR GNT.end_date >= sysdate ) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( (GNT.INSTANCE_TYPE = 'INSTANCE') AND (GNT.INSTANCE_PK1_VALUE = TO_CHAR(msi.INVENTORY_ITEM_ID)) AND (GNT.INSTANCE_PK2_VALUE = TO_CHAR(msi.ORGANIZATION_ID)) ) ) ) ) ) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '4') AND ( (NVL(msi.PUBLIC_FLAG, 'N') = 'Y') OR (EXISTS (SELECT NULL FROM fnd_grants gnt WHERE exists ( SELECT /*+ index(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) no_unnest */ null FROM fnd_session_role_sets WHERE session_role_set_key = fnd_global.session_role_set_key and role_guid = gnt.grantee_key UNION ALL SELECT fnd_global.user_guid AS path FROM (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) WHERE fnd_global.user_guid = gnt.grantee_key ) AND exists (select /*+ no_unnest */ null from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID') and cmf.menu_id = gnt.menu_id ) AND gnt.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND gnt.grant_type = 'ALLOW' AND gnt.instance_type = 'SET' AND gnt.start_date <= SYSDATE and ( gnt.end_date is null or gnt.end_date >= sysdate) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( gnt.instance_set_id = SYS_CONTEXT('EXPL_CTX', 'DS_INSTANCE_SET_ID') AND TO_CHAR(msi.INVENTORY_ORGANIZATION_ID) = GNT.PARAMETER1 AND GNT.PARAMETER2 IN (SELECT TO_CHAR(PARENT_ITEM_CLASS_ID) FROM EGP_IC_HIERARCHY WHERE ITEM_CLASS_ID = msi.ITEM_CATALOG_GROUP_ID) ) ) ) ) ) OR ( (SYS_CONTEXT('EXPL_CTX', 'DS_PREDICATE_STYLE') = '5') AND ( (NVL(msi.PUBLIC_FLAG, 'N') = 'Y') OR (exists (select null from fnd_grants gnt where ( GNT.object_id = SYS_CONTEXT('EXPL_CTX', 'DS_OBJECT_ID') AND ( (GNT.grantee_key in ( select /*+ INDEX(fnd_session_role_sets FND_SESSION_ROLE_SETS_U1) */ role_guid as path from fnd_session_role_sets where session_role_set_key = fnd_global.session_role_set_key union all select fnd_global.user_guid as path from (SELECT 1 FROM egp_structure_types_b WHERE ROWNUM = 1) ) ) ) AND GNT.menu_id in (select cmf.menu_id from fnd_compiled_menu_functions cmf where cmf.function_id = SYS_CONTEXT('EXPL_CTX', 'DS_FUNCTION_ID')) AND GNT.grant_type = 'ALLOW' AND GNT.start_date <= sysdate AND ( GNT.end_date IS NULL OR GNT.end_date >= sysdate ) AND ( (gnt.CONTEXT_NAME is NULL) or ( gnt.context_name is not null and gnt.context_value like fnd_global.get_conn_ds_attribute(gnt.context_name) ) ) AND ( (GNT.INSTANCE_TYPE = 'INSTANCE') AND (GNT.INSTANCE_PK1_VALUE = TO_CHAR(msi.INVENTORY_ITEM_ID)) AND (GNT.INSTANCE_PK2_VALUE = TO_CHAR(msi.ORGANIZATION_ID)) ) ) ) ) ) ) OR ( (1=2) ) )) AND ( ( SYS_CONTEXT('EXPL_CTX', 'CALLER_TYPE') = 'NON_PIM' AND msi.approval_status = 'A' ) OR ( SYS_CONTEXT('EXPL_CTX', 'CALLER_TYPE') = 'PIM' ) ) AND mir.inventory_item_id = msi.inventory_item_id AND mir.organization_id = msi.ITEM_DEFINITION_ORG_ID AND mir.implementation_date IS NOT NULL AND ( ( mir.effectivity_date <= GREATEST(hky.effectivity_date, To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS')) AND ( mir.end_effectivity_date IS NULL OR mir.end_effectivity_date > GREATEST(hky.effectivity_date,To_Date(SYS_CONTEXT('EXPL_CTX', 'EXPL_DATE'),'DD-MM-YYYY HH24:MI:SS')) ) )) AND ( SYS_CONTEXT('EXPL_CTX', 'VERSION_ID') = -1 OR NOT EXISTS (SELECT NULL FROM EGP_COMPONENTS_B compTable WHERE compTable.bill_sequence_id = SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') AND ( compTable.version_id = SYS_CONTEXT('EXPL_CTX', 'VERSION_ID') ) AND compTable.implementation_date IS NULL AND ( compTable.old_component_sequence_id = hky.component_sequence_id ) AND compTable.effectivity_date > hky.effectivity_date ) ) AND ( SYS_CONTEXT('EXPL_CTX', 'CHANGE_LINE_ID') = -1 OR NOT EXISTS (SELECT NULL FROM EGP_COMPONENTS_B compTable WHERE compTable.bill_sequence_id = SYS_CONTEXT('EXPL_CTX', 'TOP_BILL_SEQ') AND compTable.change_line_id = SYS_CONTEXT('EXPL_CTX', 'CHANGE_LINE_ID') AND compTable.implementation_date IS NULL AND ( compTable.old_component_sequence_id = hky.component_sequence_id ) AND compTable.effectivity_date > hky.effectivity_date ) ) ) )