Oracle Cloud offers a broad portfolio of software as a service applications, platform as a service, and social capabilities, all on a subscription basis. Oracle Cloud delivers instant value and productivity for end users, administrators, and developers alike through functionally rich, integrated, secure, enterprise cloud services.
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

Subscribe to the OracleAppsHub to receive notifications when there are new posts:

 get RSS feed
 Oracle Fusion Applications (OFA) is a portfolio of next generation suite of software applications from Oracle Corporation. It is distributed across various product families; including financial management, human capital management, customer relationship management, supply chain management, procurement, governance, and project portfolio management
 Get a Free Magzine ...Profit:The Executive's Guide to Oracle Applications

AR : Back to Basic(Technical Foundation)

Posted on February 18th, 2008 by Sanjit Anand |Print This Post Print This Post |Email This Post Email This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today

This post is more on basic table structure of Oracle Account Receivable.

1.Table that holds AR Invoice data

The following tables can give most of the invoice information.

  • RA_CUSTOMER_TRX_ALL stores invoice header information. RA_CUSTOMER_TRX_LINES_ALL stores information about invoice, debit memo, credit memo, bills receivable, and commitment lines.
  • The AR_PAYMENT_SCHEDULES_ALL table stores all transactions except adjustments and miscellaneous cash receipts.
  • RA_CUST_TRX_LINE_GL_DIST_ALL stores accounting distribution records for all transaction lines except bills receivable.The RA_CUST_TRX_LINE_GL_DIST_ALL table stores the accounting records for revenue, unearned revenue, and unbilled receivables for each invoice or credit memo line.
  • RA_CUSTOMERS - Customer information
  • RA_CUST_TRX_TYPES_ALL - Customer Transaction Type
  • AR_PAYMENT_SCHEDULES_ALL
  • RA_CUSTOMER_TRX_LINES_ALL - Transaction Line information
  • MTL_SYSTEM_ITEMS - Base table for item

The ER Diagram for a customer Transaction can be easily understood as:

CustomerInvoice

2.Customer Payment

These are the main tables which holds Customer Payment information

  • AR_CASH_RECEIPTS_ALL stores one record for each receipt entry.
  • AR_CASH_RECEIPT_HISTORY_ALL stores all of the activity that is contained for the life cycle of a receipt.
  • Each row represents one step., Possible statuses are Approved, Cleared,Confirmed, Remitted, and Reversed.
  • AR_MISC_CASH_DISTRIBUTIONS_ALL stores all accounting entries for miscellaneous cash applications.
  • AR_DISTRIBUTIONS_ALL stores the accounting distributions for cash receipts, miscellaneous receipts, adjustments,credit memo applications, cash receipt applications, and bills receivable transactions.
  • AR_RECEIVABLE_APPLICATIONS_ALL stores all accounting entries for cash and credit memo applications.
  • Each row includes the amount applied, status, and accounting flexfield information.

The ER Diagram for a customer Payment can be easily understood as:

Customerpayment

3. Accounting Link between the tables

  • For Invoice:

ra_customers (This is for capturing customers information)
ra_customer_trx_all,
ra_cust_trx_types_all,
ar_payment_schedules_all,
ra_customer_trx_lines_all

and joins are customer_trx_id for ar_payment_schedules_all & ra_customer_trx_all & ra_customer_trx_lines_all .
ra_cust_trx_types_all.type IN ('INV')

  • For the Receipts:

ar_receivable_applications_all,
ra_customer_trx_all,
ra_customer_trx_lines_all,
ar_cash_receipts_all

ar_receivable_applications_all.application_type = 'CASH',
ar_receivable_applications_all.cash_receipt_id = ar_cash_receipts_all.cash_receipt_id,
ar_receivable_applications_all.applied_customer_trx_id = ra_customer_trx_all.customer_trx_id

Related Posts

Posted in Oracle Receivable | 10 Comments »Email This Post Email This Post | Print This Post Print This Post

Have you tried OracleappsHub in ipad/iphone/smart Phone? Don't wait. try it today
10 Responses
  1. Prativa Says:

    Thank you so much for the nice posting.

    can you please post the month end closing for AP, AR and GL.

  2. Anil Bejugam Says:

    This information is very useful specially for Technical consultants.

  3. zameer Says:

    Hi Freinds,
    Can any one help me in getting all the receipts (applied/unapplied) for a particular invoice.
    Thanks,
    Zameer.

  4. Arun Says:

    Hi,

    Could anyone please help me in creating a transaction with balance of Unearned Revenue account not ’0′ (Zero).

    Please reply ASAP.

    Thanks

    –Arun

  5. Ram Says:

    Very helpful and clear picture of techinical info given….

  6. sneha Says:

    i want to know whether this query and the relates joins are correct or not..

    select c.party_id,c.party_name,c.party_number,t.name,p.profile_class_id,
    psa.payment_schedule_id,ca.account_number,pca.profile_class_amount_id,
    ar.amount_applied,ar.receivable_application_id,tl.customer_trx_line_id,
    tl.extended_amount,racta.trx_number
    from cust_dim c,
    transaction_dim t,
    profile_dim p,
    ar_payment_schedules_all psa,
    hz_cust_accounts_all ca,
    hz_cust_prof_class_amts pca,
    ar_receivable_applications_all ar,
    ra_customer_trx_lines_all tl,
    ra_customer_trx_all racta
    where c.site_use_id=psa.customer_site_use_id
    and c.cust_account_id=ca.cust_account_id
    and t.cust_trx_type_id=psa.cust_trx_type_id
    and p.profile_class_id=pca.profile_class_id
    and tl.customer_trx_id=ar.applied_customer_trx_id
    and psa.customer_trx_id=tl.customer_trx_id
    AND racta.customer_trx_id = tl.customer_trx_id;
    –AND ar.customer_trx_id = racta.customer_trx_id
    –and psa.payment_schedule_id=ar.payment_schedule_id;

  7. krishna Says:

    Hi fnds,
    I need join-condition for ra_customer_trx_all and
    ar_cash_receipts_all.

    Please help me….

  8. Aiyaz Says:

    Hello all,

    I need a more possible joins between ar_payment_schedules_all and ra_customer_trx_v…When i run my query i am getting more than 1 row for a trx_number.
    Kindly help me my mail id is
    aiyaz_23@yahoo.com

    Thanks
    Aiyaz

    Query
    ——
    select
    v.RAC_BILL_TO_CUSTOMER_NAME party_name,
    v.RAC_BILL_TO_CUSTOMER_NUM party_number,
    v.TRX_NUMBER transaction_number,
    v.CTT_TYPE_NAME invoice,
    ra.FOB_POINT fob_point,
    v.RAS_PRIMARY_SALESREP_NAME sales_rep,
    v.RAT_TERM_NAME terms_name,
    v.BILL_TO_CUSTOMER_ID cust_account_id,
    v.STATUS_TRX status,
    v.INVOICE_CURRENCY_CODE invoice_currency_code,
    v.CUSTOMER_TRX_ID customer_trx_id,
    ra.CUST_TRX_TYPE_ID cust_trx_type_id,
    v.PURCHASE_ORDER purchase_order,
    v.INTERFACE_HEADER_ATTRIBUTE1 order_number,
    v.WAYBILL_NUMBER waybill,
    v.SU_BILL_TO_LOCATION||’,'||v.RAA_BILL_TO_ADDRESS1||v.RAA_BILL_TO_ADDRESS3||v.RAA_BILL_TO_ADDRESS3 bill_to,
    v.SU_SHIP_TO_LOCATION||’,'||v.RAA_SHIP_TO_ADDRESS1 ||v.RAA_SHIP_TO_ADDRESS1 ||v.RAA_SHIP_TO_ADDRESS1 ship_to
    from
    ra_customer_trx_v v,
    ra_customer_trx ra,
    ar_payment_schedules ar
    where
    v.customer_trx_id = ra.customer_trx_id(+) and
    v.customer_trx_id =ar.customer_trx_id (+) and
    ar.amount_due_remaining > 0 and
    ar.class = ‘INV’ and
    v.TRX_NUMBER=’107820′

  9. jawwad Says:

    SELECT
    ot.name ORDER_TYPE ,
    PARTY.PARTY_NAME,
    r.description Product,
    sum(round((nvl(r.quantity_invoiced,0) * nvl(r.unit_selling_price,0)+nvl(r.tax_recoverable,0)))) Sales,
    sum(nvl(r.quantity_invoiced,0)) qty
    FROM
    ra_customer_trx_lines_all r,
    mtl_parameters ship_from_org,
    hz_cust_site_uses_all ship_su,
    hz_party_sites ship_ps,
    hz_locations ship_loc,
    hz_cust_acct_sites_all ship_cas,
    hz_cust_site_uses_all bill_su,
    hz_party_sites bill_ps,
    hz_locations bill_loc,
    hz_cust_acct_sites_all bill_cas,
    hz_parties party,
    hz_cust_accounts cust_acct,
    ra_terms_tl term,
    oe_order_headers_all h,
    oe_order_lines_all l,
    hz_cust_account_roles sold_roles,
    hz_parties sold_party,
    hz_cust_accounts sold_acct,
    hz_relationships sold_rel,
    ar_lookups sold_arl,
    hz_cust_account_roles ship_roles,
    hz_parties ship_party,
    hz_relationships ship_rel,
    hz_cust_accounts ship_acct,
    ar_lookups ship_arl,
    hz_cust_account_roles invoice_roles,
    hz_parties invoice_party,
    hz_relationships invoice_rel,
    hz_cust_accounts invoice_acct,
    ar_lookups invoice_arl,
    fnd_currencies fndcur,
    oe_transaction_types_tl ot,
    qp_list_headers_tl pl,
    ra_rules invrule,
    ra_rules accrule
    WHERE
    r.interface_line_attribute6 = l.line_id(+) and
    h.header_id = l.header_id
    and h.order_type_id = ot.transaction_type_id
    AND h.price_list_id = pl.list_header_id(+)
    AND h.invoicing_rule_id = invrule.rule_id(+)
    AND h.accounting_rule_id = accrule.rule_id(+)
    AND h.payment_term_id = term.term_id(+)
    AND h.transactional_curr_code = fndcur.currency_code
    AND h.sold_to_org_id = cust_acct.cust_account_id(+)
    AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID(+)
    AND h.ship_from_org_id = ship_from_org.organization_id (+)
    AND h.ship_to_org_id = ship_su.site_use_id(+)
    AND ship_su.cust_acct_site_id= ship_cas.cust_acct_site_id(+)
    AND ship_cas.party_site_id = ship_ps.party_site_id(+)
    AND ship_loc.location_id(+) = ship_ps.location_id
    AND h.invoice_to_org_id = bill_su.site_use_id(+)
    AND bill_su.cust_acct_site_id= bill_cas.cust_acct_site_id(+)
    AND bill_cas.party_site_id = bill_ps.party_site_id(+)
    AND bill_loc.location_id(+) = bill_ps.location_id
    AND h.sold_to_contact_id = sold_roles.cust_account_role_id(+)
    AND sold_roles.party_id = sold_rel.party_id(+)
    AND sold_roles.cust_account_id = sold_acct.cust_account_id(+)
    AND nvl(sold_rel.object_id,-1) = nvl(sold_acct.party_id,-1)
    AND sold_rel.subject_id = sold_party.party_id(+)
    AND sold_arl.lookup_code(+) = sold_party.person_pre_name_adjunct
    AND h.ship_to_contact_id = ship_roles.cust_account_role_id(+)
    AND ship_roles.party_id = ship_rel.party_id(+)
    AND ship_roles.cust_account_id = ship_acct.cust_account_id(+)
    AND nvl(ship_rel.object_id,-1) = nvl(ship_acct.party_id,-1)
    AND ship_rel.subject_id = ship_party.party_id(+)
    AND ship_arl.lookup_code(+) = ship_party.person_pre_name_adjunct
    AND h.invoice_to_contact_id = invoice_roles.cust_account_role_id(+)
    AND invoice_roles.party_id = invoice_rel.party_id(+)
    AND invoice_roles.cust_account_id = invoice_acct.cust_account_id(+)
    AND nvl(invoice_rel.object_id,-1) = nvl(invoice_acct.party_id,-1)
    AND invoice_rel.subject_id = invoice_party.party_id(+)
    AND invoice_arl.lookup_code(+) = invoice_party.person_pre_name_adjunct
    and h.ordered_date between :SD and :ED
    –and h.order_number = 40803656
    –and l.flow_status_code =’CLOSED’
    group by
    to_char(h.ordered_date,’DD-Mon-yyyy’) ,r.description ,
    ot.name ,
    PARTY.PARTY_NAME
    order by
    ot.name

  10. Vamshi Krishna Says:

    how to link between ap_invoices_all and
    ra_customer_trx_all

Leave a Comment

Please note: Comment moderation is enabled and may delay your comment. There is no need to resubmit your comment.