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!

Ar_receivable_applications_all

Discussion in 'Oracle Financials' started by RIAZ, Jan 25, 2010.

  1. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    hi all,

    I am working on the table of receivable AR_RECEIVABLE_APPLICATIONS_ALL and want to get the REVENUE/TAX/FREIGHT account details (entered when invoice created that i can see in distribution).
    I have seen APPLIED_CUSTOMER_TRX_LINE_ID to join with transaction's line but this field is NULL (for all the records).

    Any one help me in this regard ???

    Note: There are multiple TAX/LINES in an invoice, that is a challenge :(


    Sharing my query (not giving correct results)

    Code (SQL):

    SELECT
          gcc.SEGMENT4 AS GL_ACCT
    ,     decode(rctla.LINE_TYPE,'LINE',araa.LINE_APPLIED,'TAX',araa.TAX_APPLIED,'FREIGHT',araa.FREIGHT_APPLIED,0) AS AMT
    FROM
           AP_BANK_ACCOUNTS_ALL             abaa
    ,      GL_JE_HEADERS                    gjh
    ,      GL_JE_LINES                      gjl
    ,      AR_CASH_RECEIPT_HISTORY_ALL      acrha
    ,      AR_CASH_RECEIPTS_ALL             acra
    ,      AR_RECEIVABLE_APPLICATIONS_ALL   araa
    ,      RA_CUSTOMER_TRX_ALL              rcta
    ,      RA_CUSTOMER_TRX_LINES_ALL        rctla      
    ,      GL_CODE_COMBINATIONS             gcc
    ,      RA_CUST_TRX_LINE_GL_DIST_ALL     rctlgda
    WHERE
           abaa.INACTIVE_DATE IS NULL -- get only active banks --
    AND    gjh.JE_SOURCE = 'Receivables' -- posted source is Account Receivable --
    AND    gjl.STATUS = 'P' -- get only posted transactions --
    AND    gjh.JE_HEADER_ID = gjl.JE_HEADER_ID
    AND    gjl.REFERENCE_8 IN ('TRADE') --,'DM','CM')
    AND    gjl.CODE_COMBINATION_ID = abaa.ASSET_CODE_COMBINATION_ID
    AND    trunc(gjl.EFFECTIVE_DATE) <= :p_date -- for testing purpose
    AND    SUBSTR(gjl.REFERENCE_2,INSTR(gjl.REFERENCE_2,'C')+1) = acrha.CASH_RECEIPT_HISTORY_ID
    AND    acrha.CASH_RECEIPT_ID = acra.CASH_RECEIPT_ID
    AND    acra.STATUS IN ('APP','UNAPP')
    AND    acrha.CASH_RECEIPT_HISTORY_ID = araa.CASH_RECEIPT_HISTORY_ID
    AND    acrha.CASH_RECEIPT_ID = araa.CASH_RECEIPT_ID
    AND    araa.STATUS IN ('APP')
    AND    rcta.CUSTOMER_TRX_ID = araa.APPLIED_CUSTOMER_TRX_ID
    AND    rcta.CUSTOMER_TRX_ID = rctla.CUSTOMER_TRX_ID
    AND    rctlgda.CUSTOMER_TRX_LINE_ID = rctla.CUSTOMER_TRX_LINE_ID
    AND    gcc.CODE_COMBINATION_ID = rctlgda.CODE_COMBINATION_ID
    --GROUP BY gcc.SEGMENT4