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.


Comments

Popular posts from this blog

elasticsearch python client - work with many nodes - how to work with sniffer -

unity3d - Rotate an object to face an opposite direction -

angular - Is it possible to get native element for formControl? -