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!

Oracle ar sql help

Discussion in 'Oracle Financials' started by VSS, Mar 15, 2017.

  1. VSS

    VSS Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    USA
    I am trying to write a query to extract adjustments related information. I would need the following information:

    • Transaction Source Name, Adjustment Amount, Adjustment Apply Date, Adjustment Amount Applied, Adjustment GL_DATE, Invoice Number, Invoice Date, BIll-To Name, Ship-To Name, Adjustment Code Combination Segment1, Adjustment Code Combination Segment2 , Adjustment SET_OF_BOOKS_ID, Adjustment COMMENTS , Adjustment REASON_CODE, Adjustment Activity, Adjustment ACCTD_AMOUNT, ADJUSTMENT_NUMBER

    Code (SQL):
    SELECT DISTINCT C.NAME ,
        A.AMOUNT           ,
        A.APPLY_DATE       ,
        A.SET_OF_BOOKS_ID  ,
        A.COMMENTS         ,
        A.REASON_CODE      ,
        A.ACCTD_AMOUNT     ,
        A.ADJUSTMENT_NUMBER
      FROM AR_ADJUSTMENTS_ALL A,
        RA_CUSTOMER_TRX_ALL B  ,
        RA_BATCH_SOURCES_ALL C
      WHERE 1               =1
      AND A.CUSTOMER_TRX_ID = B.CUSTOMER_TRX_ID
      AND B.BATCH_SOURCE_ID = C.BATCH_SOURCE_ID
      AND A.ORG_ID          = C.ORG_ID
      AND A.STATUS          = 'A'
      AND A.CREATION_DATE BETWEEN '01-JAN-2012' AND '28-FEB-2017';
    I progressed the query into the one below, however, I am getting completely wrong information or it takes too long to run. E.g. trx_number does not match the bill-to and ship-to name.

    Code (SQL):
    SELECT DISTINCT A.AMOUNT        ,
        A.APPLY_DATE                ,
        A.GL_DATE "APPLIED GL DATE" ,
        C.TRX_NUMBER                ,
        C.TRX_DATE                  ,
        F.GL_DATE                   ,
        C.BILL_TO_CUSTOMER_ID       ,
        E.PARTY_NAME ADVERTISER     ,
        C.SHIP_TO_CUSTOMER_ID       ,
        /*--E.PARTY_NAME AGENCY ,*/
        A.CODE_COMBINATION_ID "CCID" ,
        B.SEGMENT1                   ,
        B.SEGMENT2                   ,
        A.SET_OF_BOOKS_ID            ,
        A.COMMENTS                   ,
        A.REASON_CODE                ,
        A.ACCTD_AMOUNT               ,
        A.ADJUSTMENT_NUMBER
      FROM AR_ADJUSTMENTS_ALL A ,
        GL_CODE_COMBINATIONS B  ,
        RA_CUSTOMER_TRX_ALL C   ,
        AR.HZ_CUST_ACCOUNTS D   ,
        AR.HZ_PARTIES E         ,
        RA_CUST_TRX_LINE_GL_DIST_ALL F
      WHERE 1                   = 1
      AND A.CODE_COMBINATION_ID = B.CODE_COMBINATION_ID
      AND A.CUSTOMER_TRX_ID     = C.CUSTOMER_TRX_ID
      AND A.CUSTOMER_TRX_ID     = F.CUSTOMER_TRX_ID
      AND B.CODE_COMBINATION_ID = F.CODE_COMBINATION_ID
      AND C.CUSTOMER_TRX_ID     = F.CUSTOMER_TRX_ID
      AND C.BILL_TO_CUSTOMER_ID = D.ACCOUNT_NUMBER
      AND D.PARTY_ID            = E.PARTY_ID
      AND A.STATUS              = 'A'
      AND A.CREATION_DATE BETWEEN '01-JAN-2012' AND '31-JAN-2012'
      ORDER BY C.TRX_NUMBER ASC;  
    I'm actually thoroughly lost on how I can do this. Any help would be extremely appreciated.