problem with query

[Smeagoll]

Good afternoon folks!!

I have the query below and the following problem:
I am with proble in the mooring of the query is returning duplicate line and I do not think the problem. Can someone help me ?
Query is this.

Select all

select ri.operation_id         "Nº Rec" 
      ,ri.invoice_num          "Nota Fiscal" 
      ,ri.creation_date        "Emissão" 
      ,ri.series               "Serie" 
      ,ril.icms_amount         "Valor do ICMS" 
      ,ril.ipi_base_amount     "Valor do IPI" 
      ,reo.creation_date       "Dt Rec" 
      ,reo.status              "Status Nota" 
      ,pv.vendor_name          "Fornecedor" 
      ,at.name                 "Condição de Pagto" 
      ,pha.segment1            "Numero OC" 
      ,msib.segment1           "Cod Item" 
      ,msit.description        "Descrição" 
      ,rfo.cfo_code            "CFO" 
      ,ril.uom                 "UN Medida" 
      ,ril.quantity            "Qtde" 
      ,ril.unit_price          "Preço UN" 
      ,ril.total_amount        "Total Nota" 
      ,ril.icms_tax            "Alicota ICMS" 
 from  apps.rec_invoices            ri 
      ,apps.rec_invoice_lines       ril 
      ,apps.rec_entry_operations    reo 
      ,apps.po_vendors              pv 
      ,apps.ap_terms                at 
      ,apps.rec_fiscal_entities_all rfea 
      ,apps.po_vendor_sites_all     pvsa 
      ,apps.po_headers_all          pha 
      ,apps.po_lines_all            pla 
      ,apps.po_line_locations_all   plla 
      ,apps.mtl_system_items_b      msib 
      ,apps.mtl_system_items_tl     msit 
      ,apps.rec_fiscal_operations   rfo 
where ri.invoice_id         = ril.invoice_id 
  and ri.organization_id    = reo.organization_id 
  and ri.operation_id       = reo.operation_id 
  and ri.entity_id          = rfea.entity_id 
  and ri.terms_id           = at.term_id 
  and pvsa.vendor_site_id   = rfea.vendor_site_id 
  and pvsa.vendor_id        = pv.vendor_id 
  and plla.line_location_id = ril.line_location_id 
  and plla.po_header_id     = pha.po_header_id 
  and pha.po_header_id      = pla.po_header_id 
--  and ri.operation_id       = 100000 
--  and ri.organization_id    = 56 
  and msib.inventory_item_id = ril.item_id 
  and msib.organization_id   = ri.organization_id 
  and msit.organization_id   = msib.organization_id 
  and msit.inventory_item_id = msib.inventory_item_id 
  and msit.language          = 'PTB' 
  and rfo.cfo_id             = ril.cfo_id 
  and ri.creation_date between to_date('01/01/2005','dd/mm/yyyy') 
  and                          to_date('10/01/2005','dd/mm/yyyy') 
order by ri.operation_id;

: Shock:

[dr_gori]

It is impossible to tell you where the problem is, for we do not know the structure of the tables.

We need to know existing relationships in each table involved (key-primary). What may be happening is the forgetfulness of some join. And that, we can not guess ...

[Smeagoll]

You're right, my friend ... it was a really stuck relationship.

[ricardorauber]

Bah Smeagoll, I do not know if you still use the forum but I'm with exactly this problem in EBS !! Just missed saying what the right relationship was going to be a jewelry!

[rodfbar]

You should observe which table is bringing different data for each line, which causes this product ...

or try to put a distinct in front of the select ...

99] to verify a possible lack of relationship you can use the "explan-plain" na query, there is a topic explaining how to use it ...

other way to locate which Table is entering this product is taking one by one from its last relationship (the least significant) and leaving only the base tables of your query (type tables that you only connect to bring a client name for example)

of the kinder that this is really difficult to tell you the exact point that you need to change because we do not have the structures and data of your tables ...
Abrçs

[ricardorauber]

RodfBar

These are tables of Oracle Applications, in my case the e-business suite. For the tables of the standard Applications modules, Oracle offers relationship information in Metalink but for the REC module (which is exclusive to Brazil), there is no.

For those who do not know, within Oracle Applications, all keys and constraints are not in the bank (eg: user_constraints) and yes in Oracle configuration tables (eg fnd_tables, fnd_primary_keys).

My problem is that just because you do not find the correct relationships of the hundreds of fields that each table has, I can not find a relationship that is 1 to 1.