sql - Joining WIP_DISCRETE_JOBS to a Bill Of Materials table -

i have sql query in trying fetch items along components have bill of materials (bom) enabled , creation date withing last 3 years. problem every item along component list gets repeated. suppose item has 5 components, , query particular item number. 35 rows (7 repetitions of sets of 5 components). however, if query particular wip_entity_name or if use distinct, perfect 5 rows of data. need data (5 rows, no repetitions) when query particular item number. here query:

select * bom_bill_of_materials bom,   bom_inventory_components bic,   bom_components_b bcb,   bom_operational_routings_v borv,   bom_operation_sequences_v bosv,   bom_structures_b bsb,   bom_structure_types_b bst,   mtl_system_items_b msi,   mtl_system_items_tl msit,   org_organization_definitions ood,   wip_discrete_jobs_v wdjv bom.bill_sequence_id    = bic.bill_sequence_id , bic.component_sequence_id = bcb.component_sequence_id , bcb.bill_sequence_id      = bsb.bill_sequence_id , bom.structure_type_id     = bst.structure_type_id , bom.assembly_item_id     = msi.inventory_item_id , bom.organization_id      = msi.organization_id , bosv.routing_sequence_id = borv.routing_sequence_id , borv.assembly_item_id    = bom.assembly_item_id , borv.organization_id     = bom.organization_id , msi.inventory_item_id    = msit.inventory_item_id , msi.organization_id      = msit.organization_id , msit.organization_id     = ood.organization_id , msit.language            = userenv('lang') , sysdate between bcb.effectivity_date , nvl(bcb.disable_date, sysdate) , msi.bom_enabled_flag            = 'y' , nvl(msi.enabled_flag,'x')       ='y' , ood.organization_id            in (203,204,328) , borv.common_routing_sequence_id = wdjv.common_routing_sequence_id , borv.assembly_item_id = wdjv.primary_item_id , wdjv.creation_date    > add_months( sysdate, -12*3 ) , wdjv.wip_entity_name = '28799'   --and msi.segment1='9064090' 

also, have found sets of 5 differ each other on basis of we_row_id, request_id_7, scheduled_start_date, scheduled_completion_date , other columns present in wip_discreet_jobs table. there way map these columns of bom tables? new this, please bear me gurus.

i see asking oracle e-business suite data model.

trying fetch items ... , creation date withing last 3 years.

that not query doing. getting details of items made (via wip discrete job) within last 3 years -- , getting duplicate of item every time made (i.e., every occurrence in wip_discrete_jobs_v).

if want items made in last 3 years, want each item once, should use exists (semi-join) filter rows instead of joining wip_discrete_jobs_v, cause duplicates). more along these lines:

select *   bom_bill_of_materials bom,        bom_inventory_components bic,        bom_components_b bcb,        bom_operational_routings_v borv,        bom_operation_sequences_v bosv,        bom_structures_b bsb,        bom_structure_types_b bst,        mtl_system_items_b msi,        mtl_system_items_tl msit,        org_organization_definitions ood --,        --wip_discrete_jobs_v wdjv  bom.bill_sequence_id = bic.bill_sequence_id ,    bic.component_sequence_id = bcb.component_sequence_id ,    bcb.bill_sequence_id = bsb.bill_sequence_id ,    bom.structure_type_id = bst.structure_type_id ,    bom.assembly_item_id = msi.inventory_item_id ,    bom.organization_id = msi.organization_id ,    bosv.routing_sequence_id = borv.routing_sequence_id ,    borv.assembly_item_id = bom.assembly_item_id ,    borv.organization_id = bom.organization_id ,    msi.inventory_item_id = msit.inventory_item_id ,    msi.organization_id = msit.organization_id ,    msit.organization_id = ood.organization_id ,    msit.language = userenv ('lang') ,    sysdate between bcb.effectivity_date , nvl (bcb.disable_date, sysdate) ,    msi.bom_enabled_flag = 'y' ,    nvl (msi.enabled_flag, 'x') = 'y' ,    ood.organization_id in (203, 204, 328) --and    borv.common_routing_sequence_id = wdjv.common_routing_sequence_id --and    borv.assembly_item_id = wdjv.primary_item_id --and    wdjv.creation_date > add_months (sysdate, -12 * 3) --and    wdjv.wip_entity_name = '28799' ,    exists ( select 'discrete job within last 3 years'                   wip_discrete_jobs_v wdjv                  wdjv.common_routing_sequence_id = borv.common_routing_sequence_id                 ,    wdjv.primary_item_id = borv.assembly_item_id                 ,    wdjv.creation_date >= add_months(sysdate, -12*3)                 ) --and msi.segment1='9064090' 

note -- kept close original query possible , didn't verify join conditions used. there may other bugs in initial query have inadvertently duplicated.

one other thing, it's not practice use forms views, wip_discrete_jobs_v or bom_operational_routings_v. these views provide data online forms. not can performance suffer (because contain joins may not need), but, if go oracle's etrm site (http://etrm.oracle.com/pls/etrm/etrm_pnav.show_object?c_name=bom_operational_routings_v&c_owner=apps&c_type=view), see warning:

warning: oracle not recommend query or alter datausing view. may change dramatically in subsequent minor or major releases.

it better select base tables.