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!

Join Receipts with Invoices in Oracle Receivables

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

  1. RIAZ

    RIAZ Forum Advisor

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

    I am working on Oracle Receivables and want to join receipts with invoices (transactions).

    I am successfully getting the link from "GL to Receipt to Invoice".

    The problem is,getting multiple Receipts against a single Invoice and in each invoice i have multiple lines (LINE,TAX and FREIGHT). Here I am unable to capture the exact amounts.

    Example:
    Receipt 1 of Amount 50 (partially received against Invoice 1) and in Invoice 1 there are two lines (Line amounted 100 and Tax amounted 10)

    But when i am fetching the records, receipt amount is repeating :

    Receipt : Receipt_Amt : Invoice : Invoice Line
    1 : 50 : 1 : 1
    1 : 50 : 1 : 2
    2 : 60 : 1 : 1
    2 : 60 : 1 : 2

    Please help me...


    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
     
     
  2. RIAZ

    RIAZ Forum Advisor

    Messages:
    54
    Likes Received:
    0
    Trophy Points:
    130
    any body ... ????