title: Joining WIP_DISCRETE_JOBS to a Bill Of Materials table
body: <p>I have an SQL query in which I am trying to fetch items along with their components which have Bill Of Materials (BOM) enabled and whose creation date is withing the last three years.
  The problem is every item along with their component list gets repeated. Suppose an item has 5 components, and I query with the particular item number. I get 35 rows (7 repetitions of sets of 5 components). However, if I query with a particular WIP_ENTITY_NAME or if I use DISTINCT, I get a perfect 5 rows of data. I need to get this data (5 rows, no repetitions) when I query with a particular item number.
  Here is the query:</p>
  
  <pre><code>SELECT *
  FROM 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
  WHERE BOM.BILL_SEQUENCE_ID    = BIC.BILL_SEQUENCE_ID
  AND BIC.COMPONENT_SEQUENCE_ID = BCB.COMPONENT_SEQUENCE_ID
  AND BCB.BILL_SEQUENCE_ID      = BSB.BILL_SEQUENCE_ID
  AND BOM.STRUCTURE_TYPE_ID     = BST.STRUCTURE_TYPE_ID
  AND BOM.ASSEMBLY_ITEM_ID     = MSI.INVENTORY_ITEM_ID
  AND BOM.ORGANIZATION_ID      = MSI.ORGANIZATION_ID
  AND BOSV.ROUTING_SEQUENCE_ID = BORV.ROUTING_SEQUENCE_ID
  AND BORV.ASSEMBLY_ITEM_ID    = BOM.ASSEMBLY_ITEM_ID
  AND BORV.ORGANIZATION_ID     = BOM.ORGANIZATION_ID
  AND MSI.INVENTORY_ITEM_ID    = MSIT.INVENTORY_ITEM_ID
  AND MSI.ORGANIZATION_ID      = MSIT.ORGANIZATION_ID
  AND MSIT.ORGANIZATION_ID     = OOD.ORGANIZATION_ID
  AND MSIT.LANGUAGE            = USERENV('LANG')
  AND sysdate BETWEEN BCB.EFFECTIVITY_DATE AND NVL(BCB.DISABLE_DATE, sysdate)
  AND MSI.BOM_ENABLED_FLAG            = 'Y'
  AND NVL(MSI.ENABLED_FLAG,'X')       ='Y'
  AND 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    &gt; ADD_MONTHS( sysdate, -12*3 )
  AND WDJV.WIP_ENTITY_NAME = '28799'
    --and MSI.SEGMENT1='9064090'
  </code></pre>
  
  <p>Also, I have found that the sets of 5 differ from each other on the basis of their WE_ROW_ID, REQUEST_ID_7, SCHEDULED_START_DATE, SCHEDULED_COMPLETION_DATE and other columns which are present in the WIP_DISCREET_JOBS table. Is there any way to map these columns to any of the BOM tables? I am new to this, so please bear with me gurus.</p>