VIEW: APPS.BOM_EXPL_INQUIRY_VIEW

Source

View Text - Preformatted

SELECT /* ORDERED FIRST_ROWS INDEX(MSI MTL_SYSTEM_ITEMS_U1) INDEX(BOM BOM_BILL_OF_MATERIALS_U2) INDEX(LU10 MFG_LOOKUPS_U1) INDEX(FCL FND_COMMON_LOOKUPS_U1) */ rowidtochar(bet.rowid) row_id, bet.top_bill_sequence_id, bet.bill_sequence_id, bet.component_sequence_id, bet.component_item_id, bet.organization_id, bet.operation_seq_num, bet.plan_level, bet.extended_quantity, bet.item_num, bet.sort_order, bet.group_id, bet.item_cost, nvl(msi.reservable_type, 2) reservable_type, bet.extend_cost_flag, bet.top_alternate_designator, bet.top_item_id, bet.implementation_date, decode(bet.implementation_date, null, 2, 1) implemented_flag, bet.optional, bet.planning_factor, bet.component_yield_factor, bet.include_in_rollup_flag, bet.wip_supply_type wip_supply_type, ml2.meaning supply_type, bet.supply_subinventory, bet.supply_locator_id, bet.locator, bet.component_remarks, bet.change_notice, lu11.meaning basis_type, bet.component_quantity, bet.operation_lead_time_percent, bet.operation_offset, bet.mutually_exclusive_options, bet.check_atp, bet.required_to_ship, lu10.meaning so_basis, bet.required_for_revenue, bet.include_on_ship_docs, bet.low_quantity minimum_quantity, bet.high_quantity maximum_quantity, msi.primary_uom_code unit_of_measure, msi.item_type item_type, fcl.meaning item_type_description, msi.INVENTORY_ITEM_STATUS_CODE item_status, INV_MEANING_SEL.C_ITEM_DESCRIPTION(msi.inventory_item_id,msi.organization_id) description, msi.eng_item_flag, msi.cum_manufacturing_lead_time, msi.cumulative_total_lead_time, msi.full_lead_time manufacturing_lead_time, bom.assembly_item_id parent_item_id, bom.alternate_bom_designator parent_alternate, decode(bom.alternate_bom_designator, null, 2, 1) parent_alternate_flag, nvl(bom1.assembly_type,1) eng_bill, LPAD(PLAN_LEVEL, LEAST(PLAN_LEVEL, 8), '.') DISPLAY_PLAN_LEVEL, TO_CHAR(BET.EFFECTIVITY_DATE, 'HH24:MI') EFFECTIVITY_TIME, TO_CHAR(BET.DISABLE_DATE, 'HH24:MI') DISABLE_TIME, BET.EXTENDED_QUANTITY, DECODE(BET.EXTEND_COST_FLAG, 1, BET.ITEM_COST * BET.EXTENDED_QUANTITY, 2, 0, NULL) EXTENDED_COST, BET.OPERATION_SEQ_NUM, MSI.BOM_ITEM_TYPE bom_item_type, MSI.REPLENISH_TO_ORDER_FLAG ATO_FLAG, MSI.PICK_COMPONENTS_FLAG, MSI.ATP_COMPONENTS_FLAG, MSI.ATP_FLAG, MSI.SHIPPABLE_ITEM_FLAG, MSI.MRP_PLANNING_CODE, MSI.SO_TRANSACTIONS_FLAG, MSI.CUSTOMER_ORDER_ENABLED_FLAG, MSI.INVOICE_ENABLED_FLAG, MSI.INVENTORY_ASSET_FLAG, BOM_GLOBALS.Get_Concat_Segs(MSI.INVENTORY_ITEM_ID,MSI.ORGANIZATION_ID), MSI.PADDED_CONCATENATED_SEGMENTS, BET.CURRENT_REVISION, BET.FROM_END_ITEM_UNIT_NUMBER, BET.TO_END_ITEM_UNIT_NUMBER, ML3.MEANING, decode(bet.from_end_item_unit_number, null, 2, decode(bet.disable_date, null, 2, 1)) disabled_flag, DECODE(bet.FROM_END_ITEM_UNIT_NUMBER, NULL,BET.EFFECTIVITY_DATE,NULL) FROM_EFFECTIVITY_DATE, DECODE(bet.FROM_END_ITEM_UNIT_NUMBER, NULL,BET.DISABLE_DATE,NULL) TO_EFFECTIVITY_DATE FROM MFG_LOOKUPS ml2 , FND_COMMON_LOOKUPS fcl , MFG_LOOKUPS ml3 , MFG_LOOKUPS lu10 , MFG_LOOKUPS lu11 , BOM_BILL_OF_MATERIALS bom , BOM_BILL_OF_MATERIALS bom1 , MTL_SYSTEM_ITEMS_KFV msi , BOM_SMALL_EXPL_TEMP bet WHERE bet.component_item_id = msi.inventory_item_id and bet.bill_sequence_id = bom.bill_sequence_id and bet.organization_id = msi.organization_id and lu10.lookup_code (+) = bet.so_basis and lu10.lookup_type (+) = 'BOM_SO_BASIS' and lu11.lookup_code = nvl(bet.basis_type,1) and lu11.lookup_type (+) = 'BOM_BASIS_TYPE' and ml3.lookup_code = decode(BET.FROM_END_ITEM_UNIT_NUMBER, NULL , 1, 2) and ml3.lookup_type (+) = 'MTL_EFFECTIVITY_CONTROL' and fcl.lookup_code (+) = msi.item_type and fcl.lookup_type (+) = 'ITEM_TYPE' and fcl.application_id (+) = 401 and ml2.lookup_code (+) = bet.wip_supply_type and ml2.lookup_type (+) = 'WIP_SUPPLY' and bet.component_item_id = bom1.assembly_item_id(+) and bet.organization_id = bom1.organization_id(+) and ( (bet.top_alternate_designator is null and bom1.alternate_bom_designator is null ) or (bet.top_alternate_designator is not null and bom1.alternate_bom_designator = bet.top_alternate_designator ) or ( bet.top_alternate_designator is not null and bom1.alternate_bom_designator is null and not exists ( select 'x' from bom_bill_of_materials bom2 where bom2.organization_id = bet.organization_id and bom2.assembly_item_id = bet.component_item_id and bom2.alternate_bom_designator = bet.top_alternate_designator ) ) )

View Text - HTML Formatted

SELECT /* ORDERED FIRST_ROWS INDEX(MSI MTL_SYSTEM_ITEMS_U1) INDEX(BOM BOM_BILL_OF_MATERIALS_U2) INDEX(LU10 MFG_LOOKUPS_U1) INDEX(FCL FND_COMMON_LOOKUPS_U1) */ ROWIDTOCHAR(BET.ROWID) ROW_ID
, BET.TOP_BILL_SEQUENCE_ID
, BET.BILL_SEQUENCE_ID
, BET.COMPONENT_SEQUENCE_ID
, BET.COMPONENT_ITEM_ID
, BET.ORGANIZATION_ID
, BET.OPERATION_SEQ_NUM
, BET.PLAN_LEVEL
, BET.EXTENDED_QUANTITY
, BET.ITEM_NUM
, BET.SORT_ORDER
, BET.GROUP_ID
, BET.ITEM_COST
, NVL(MSI.RESERVABLE_TYPE
, 2) RESERVABLE_TYPE
, BET.EXTEND_COST_FLAG
, BET.TOP_ALTERNATE_DESIGNATOR
, BET.TOP_ITEM_ID
, BET.IMPLEMENTATION_DATE
, DECODE(BET.IMPLEMENTATION_DATE
, NULL
, 2
, 1) IMPLEMENTED_FLAG
, BET.OPTIONAL
, BET.PLANNING_FACTOR
, BET.COMPONENT_YIELD_FACTOR
, BET.INCLUDE_IN_ROLLUP_FLAG
, BET.WIP_SUPPLY_TYPE WIP_SUPPLY_TYPE
, ML2.MEANING SUPPLY_TYPE
, BET.SUPPLY_SUBINVENTORY
, BET.SUPPLY_LOCATOR_ID
, BET.LOCATOR
, BET.COMPONENT_REMARKS
, BET.CHANGE_NOTICE
, LU11.MEANING BASIS_TYPE
, BET.COMPONENT_QUANTITY
, BET.OPERATION_LEAD_TIME_PERCENT
, BET.OPERATION_OFFSET
, BET.MUTUALLY_EXCLUSIVE_OPTIONS
, BET.CHECK_ATP
, BET.REQUIRED_TO_SHIP
, LU10.MEANING SO_BASIS
, BET.REQUIRED_FOR_REVENUE
, BET.INCLUDE_ON_SHIP_DOCS
, BET.LOW_QUANTITY MINIMUM_QUANTITY
, BET.HIGH_QUANTITY MAXIMUM_QUANTITY
, MSI.PRIMARY_UOM_CODE UNIT_OF_MEASURE
, MSI.ITEM_TYPE ITEM_TYPE
, FCL.MEANING ITEM_TYPE_DESCRIPTION
, MSI.INVENTORY_ITEM_STATUS_CODE ITEM_STATUS
, INV_MEANING_SEL.C_ITEM_DESCRIPTION(MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID) DESCRIPTION
, MSI.ENG_ITEM_FLAG
, MSI.CUM_MANUFACTURING_LEAD_TIME
, MSI.CUMULATIVE_TOTAL_LEAD_TIME
, MSI.FULL_LEAD_TIME MANUFACTURING_LEAD_TIME
, BOM.ASSEMBLY_ITEM_ID PARENT_ITEM_ID
, BOM.ALTERNATE_BOM_DESIGNATOR PARENT_ALTERNATE
, DECODE(BOM.ALTERNATE_BOM_DESIGNATOR
, NULL
, 2
, 1) PARENT_ALTERNATE_FLAG
, NVL(BOM1.ASSEMBLY_TYPE
, 1) ENG_BILL
, LPAD(PLAN_LEVEL
, LEAST(PLAN_LEVEL
, 8)
, '.') DISPLAY_PLAN_LEVEL
, TO_CHAR(BET.EFFECTIVITY_DATE
, 'HH24:MI') EFFECTIVITY_TIME
, TO_CHAR(BET.DISABLE_DATE
, 'HH24:MI') DISABLE_TIME
, BET.EXTENDED_QUANTITY
, DECODE(BET.EXTEND_COST_FLAG
, 1
, BET.ITEM_COST * BET.EXTENDED_QUANTITY
, 2
, 0
, NULL) EXTENDED_COST
, BET.OPERATION_SEQ_NUM
, MSI.BOM_ITEM_TYPE BOM_ITEM_TYPE
, MSI.REPLENISH_TO_ORDER_FLAG ATO_FLAG
, MSI.PICK_COMPONENTS_FLAG
, MSI.ATP_COMPONENTS_FLAG
, MSI.ATP_FLAG
, MSI.SHIPPABLE_ITEM_FLAG
, MSI.MRP_PLANNING_CODE
, MSI.SO_TRANSACTIONS_FLAG
, MSI.CUSTOMER_ORDER_ENABLED_FLAG
, MSI.INVOICE_ENABLED_FLAG
, MSI.INVENTORY_ASSET_FLAG
, BOM_GLOBALS.GET_CONCAT_SEGS(MSI.INVENTORY_ITEM_ID
, MSI.ORGANIZATION_ID)
, MSI.PADDED_CONCATENATED_SEGMENTS
, BET.CURRENT_REVISION
, BET.FROM_END_ITEM_UNIT_NUMBER
, BET.TO_END_ITEM_UNIT_NUMBER
, ML3.MEANING
, DECODE(BET.FROM_END_ITEM_UNIT_NUMBER
, NULL
, 2
, DECODE(BET.DISABLE_DATE
, NULL
, 2
, 1)) DISABLED_FLAG
, DECODE(BET.FROM_END_ITEM_UNIT_NUMBER
, NULL
, BET.EFFECTIVITY_DATE
, NULL) FROM_EFFECTIVITY_DATE
, DECODE(BET.FROM_END_ITEM_UNIT_NUMBER
, NULL
, BET.DISABLE_DATE
, NULL) TO_EFFECTIVITY_DATE
FROM MFG_LOOKUPS ML2
, FND_COMMON_LOOKUPS FCL
, MFG_LOOKUPS ML3
, MFG_LOOKUPS LU10
, MFG_LOOKUPS LU11
, BOM_BILL_OF_MATERIALS BOM
, BOM_BILL_OF_MATERIALS BOM1
, MTL_SYSTEM_ITEMS_KFV MSI
, BOM_SMALL_EXPL_TEMP BET
WHERE BET.COMPONENT_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND BET.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
AND BET.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND LU10.LOOKUP_CODE (+) = BET.SO_BASIS
AND LU10.LOOKUP_TYPE (+) = 'BOM_SO_BASIS'
AND LU11.LOOKUP_CODE = NVL(BET.BASIS_TYPE
, 1)
AND LU11.LOOKUP_TYPE (+) = 'BOM_BASIS_TYPE'
AND ML3.LOOKUP_CODE = DECODE(BET.FROM_END_ITEM_UNIT_NUMBER
, NULL
, 1
, 2)
AND ML3.LOOKUP_TYPE (+) = 'MTL_EFFECTIVITY_CONTROL'
AND FCL.LOOKUP_CODE (+) = MSI.ITEM_TYPE
AND FCL.LOOKUP_TYPE (+) = 'ITEM_TYPE'
AND FCL.APPLICATION_ID (+) = 401
AND ML2.LOOKUP_CODE (+) = BET.WIP_SUPPLY_TYPE
AND ML2.LOOKUP_TYPE (+) = 'WIP_SUPPLY'
AND BET.COMPONENT_ITEM_ID = BOM1.ASSEMBLY_ITEM_ID(+)
AND BET.ORGANIZATION_ID = BOM1.ORGANIZATION_ID(+)
AND ( (BET.TOP_ALTERNATE_DESIGNATOR IS NULL
AND BOM1.ALTERNATE_BOM_DESIGNATOR IS NULL ) OR (BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
AND BOM1.ALTERNATE_BOM_DESIGNATOR = BET.TOP_ALTERNATE_DESIGNATOR ) OR ( BET.TOP_ALTERNATE_DESIGNATOR IS NOT NULL
AND BOM1.ALTERNATE_BOM_DESIGNATOR IS NULL
AND NOT EXISTS ( SELECT 'X'
FROM BOM_BILL_OF_MATERIALS BOM2
WHERE BOM2.ORGANIZATION_ID = BET.ORGANIZATION_ID
AND BOM2.ASSEMBLY_ITEM_ID = BET.COMPONENT_ITEM_ID
AND BOM2.ALTERNATE_BOM_DESIGNATOR = BET.TOP_ALTERNATE_DESIGNATOR ) ) )