1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to solve data duplication

Discussion in 'Oracle Financials' started by Soha, Nov 19, 2017.

Tags:
  1. Soha

    Soha Newly Initiated

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    30
    Location:
    Egypt
    i'm trying to create a Trial Balance Report , but I have a Data Duplication in only one Field which is "GLL.NAME" but the rest of Data are Correct ,
    And this is my code ...

    Code (SQL):

    SELECT  
      GLB.BEGIN_BALANCE_DR - GLB.BEGIN_BALANCE_CR  AS BEGIN_BALANCE,
         GLB.BEGIN_BALANCE_DR - GLB.BEGIN_BALANCE_CR +  GLB.PERIOD_NET_DR - GLB.PERIOD_NET_CR AS END_BALANCE,
      GLB.CURRENCY_CODE,    
      GLB.PERIOD_NAME,  
      GLB.PERIOD_NET_DR - GLB.PERIOD_NET_CR AS ACT  ,
         GLL.NAME  ,
      (GLC.SEGMENT1 || '-' || GLC.SEGMENT2 || '-' || GLC.SEGMENT3 || '-' || GLC.SEGMENT4 || '-' || GLC.SEGMENT5 || '-' || GLC.SEGMENT6 || '-' || GLC.SEGMENT7 || '-' || GLC.SEGMENT8 || '-' || GLC.SEGMENT9 || '-' || GLC.SEGMENT10) AS CODE_COMBINATION
     
      FROM GL_BALANCES GLB,
             GL_LEDGERS GLL,
        GL_CODE_COMBINATIONS GLC
     
     
      WHERE (GLB.LEDGER_ID = GLL.LEDGER_ID(+))
      AND ( (GLL.OBJECT_TYPE_CODE = 'L' ) )
      AND GLC.CODE_COMBINATION_ID=GLL.RET_EARN_CODE_COMBINATION_ID
      AND GLC.CREATED_BY=GLL.CREATED_BY
      AND GLC.LAST_UPDATED_BY=GLB.LAST_UPDATED_BY
      AND GLB.OBJECT_VERSION_NUMBER=GLC.OBJECT_VERSION_NUMBER
      AND GLL.ACCOUNTED_PERIOD_TYPE=GLB.PERIOD_TYPE
      AND GLL.CHART_OF_ACCOUNTS_ID=GLC.CHART_OF_ACCOUNTS_ID
      AND GLL.RET_EARN_CODE_COMBINATION_ID=GLC.CODE_COMBINATION_ID
    Can anyone Guide my how can I fix it ?
     
    Last edited: Nov 20, 2017
  2. krasnoslobodtsev_si

    krasnoslobodtsev_si Forum Genius

    Messages:
    739
    Likes Received:
    145
    Trophy Points:
    830
    Location:
    Russian Federation
    Perhaps the source of duplicates is the view of "GLL".
    What is key(logical PK-key or business-key) for the view GLL ?
    It is also necessary to check the join condition in the general query.
     
    Last edited: Nov 20, 2017
    Soha likes this.
  3. james shallow

    james shallow Active Member

    Messages:
    32
    Likes Received:
    1
    Trophy Points:
    110
    Location:
    uk
    Firstly, can you describe the tables so that we can see the relationships.
    Secondly, you can tidy up your code by removing some of the joins, for example, where are you joining on audit columns? created_by or updated_by are just audit columns and anyone can update any column so this may affect your query output.

    Also you don't need these brackets everywhere, for example on the last select concatenated selected columns and the second where clause.

    Code (SQL):
    GLC.SEGMENT1 || '-' || GLC.SEGMENT2 || '-' || GLC.SEGMENT3 || '-' || GLC.SEGMENT4 || '-' || GLC.SEGMENT5 || '-' || GLC.SEGMENT6 || '-' || GLC.SEGMENT7 || '-' || GLC.SEGMENT8 || '-' || GLC.SEGMENT9 || '-' || GLC.SEGMENT10 AS CODE_COMBINATION
    Code (SQL):
      WHERE (GLB.LEDGER_ID = GLL.LEDGER_ID(+)
      AND      GLL.OBJECT_TYPE_CODE = 'L' )
     
    Soha likes this.