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

Post a Comment

Popular posts from this blog

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

i've got angular2 reactive form . created formcontrol s , assigned input fields by [formcontrol]=... . understand creates nativeelement <-> formcontrol link. my question: possible nativeelement formcontrol ? wanna myformcontrol.nativeelement.focus() i can share 1 terrible solution works me. in reactive forms can use either 1) formcontroldirective ts mycontrol = new formcontrol('') template <input type="text" [formcontrol]="mycontrol"> or 2) formcontrolname ts myform: formgroup; constructor(private fb: formbuilder) {} ngoninit() { this.myform = this.fb.group({ foo: '' }); } template <form [formgroup]="myform"> <input type="text" formcontrolname="foo"> </form> so these directives write patch like 1) formcontroldirective const originformcontrolngonchanges = formcontroldirective.prototype.ngonchanges; formcontroldirective.proto...
Read more

unity3d - Rotate an object to face an opposite direction -

i have object , b. when click on object b, rotates object b (a faces b). b doesn't face a. need following : when faces b, need face opposite direction. have code rotating @ b. how rotate face opposite direction after that? vector3 targetdirection = target - transform.position; float step = speed * time.deltatime; vector3 newdirection = vector3.rotatetowards (turretdome.transform.forward, targetdirection, step, 0.0f); turretdome.transform.rotation = quaternion.lookrotation (newdirection); you object facing object b, want inverse direction of object after that? objecta.transform.rotation = quaternion.inverse(objecta.transform.rotation) but lets assume example turretdome object a, (negate direction): turretdome.transform.rotation = quaternion.lookrotation (-newdirection); naturally, both of these snippets not show how smoothen rotation, seem know how use time.deltatime. just incase unsure, quaternion.lerp this
Read more

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

i have 1 cluster 2 nodes. i trying understand best practise connect nodes, , check failover when there downtime on 1 node. from documentation : es = elasticsearch( ['esnode1', 'esnode2'], # sniff before doing sniff_on_start=true, # refresh nodes after node fails respond sniff_on_connection_fail=true, # , every 60 seconds sniffer_timeout=60 ) so tried connect nodes this: client = elasticsearch([ip1, ip2],sniff_on_start=true, sniffer_timeout=10,sniff_on_connection_fail=true) where ip1/ip2 machine ip's (for example 10.0.0.1, 10.0.0.2) in order test it, terminated ip2 (or put non existent if) now, when trying connect, get: transporterror: transporterror(n/a, 'unable sniff hosts - no viable hosts found.') even ip1 exist , up. if trying connect this: es = elasticsearch([ip1, ip2]) then can see in log if client not getting response ip2, move ip1, , return valid response. so missing here something? thought s...
Read more