Account Receivables

Account Receivables (AR)
Account Receivables defined as amount or money owed by the customer. The payment from the customer can be for goods or services. Organization send receipt to the customer for payment. Account receivable contains customer information, payment term, bank information, amount, tax applies, receivable and revenue account etc. 


Account Receivable consider as an asset because money is owed by client or customer, it will be converted into Cash. We will discuss more from functional in other posts. AR consists of many tables which having customer transaction details, amount, asset account, tax, credit etc. I will share technical queries link in end of this post. 

Account Receivables Tables

RA_CUSTOMER_TRX_ALL: Stores transaction information like bill_to_customer_id, trx_number, trx_date, invoice, debit, credit memo etc. Main columns are:

  • CUSTOMER_TRX_ID: Transaction id and unique for each record.
  • TRX_NUMBER: Transaction number
  • BILL_TO_CUSTOMER_ID: Customer ID link with HZ_CUST_ACCOUNTS.CUST_ACCOUNT_ID
  • TRX_DATE: Transaction Date
  • CUST_TRX_TYPE_SEQ_ID: Identify type of transaction i.e. INV, CM or DM.

RA_CUSTOMER_TRX_LINES_ALL: This table stores line information of invoice, debit memo, credit memo. Line information consist of amount, sales order number, quantity ordered, quantity invoiced, unit selling price, tax classifications etc. Below are main columns:

  • CUSTOMER_TRX_ID: Reference column from ra_customer_trx_all.
  • CUSTOMER_TRX_LINE_ID: Unique id for each line.
  • LINE_NUMBER: Line number of transaction.
  • QUANTITY_ORDERED: Quantity of an order
  • UNIT_SELLING_PRICE: Order selling price per quantity
  • EXTENDED_AMOUNT: Amount of each line
  • LINE_TYPE: Type of line such as LINE, TAX
  • LINK_TO_CUST_TRX_LINE_ID: Associated LINE line_type to a tax line.


RA_CUST_TRX_LINE_GL_DIST_ALL: This table stores information about earned, unearned, revenue, receivable etc. This table also stores accounting information such as GL_DATE, GL_POSTED_FLAG of related transaction or credit memo or debit memo. 

  • GL_DATE: Accounting date.
  • AMOUNT: Revenue, earned, unearned amount
  • CODE_COMBINATION_ID: Account code
  • GL_POSTED_DATE: Transaction posted date into GL

AR_PAYMENT_SCHEDULES_ALL: This table store payment installment of an transactions except adjustment and miscellaneous cash receipts. Main columns:

  • PAYMENT_SCHEDULE_ID
  • AMOUNT_DUE_ORIGINAL
  • AMOUNT_DUE_REMAINING
  • DUE_DATE
  • CUSTOMER_TRX_ID
  • CASH_RECEIPT_ID

AR_CASH_RECEIPTS_ALL: This table store receipt information. Main columns are:

  • CASH_RECEIPT_ID
  • AMOUNT
  • PAY_FROM_CUSTOMER: CUST_ACCOUNT_ID
  • STATUS: 
  • TYPE: REC for standard receipt and MISC for Miscellaneous receipt
  • RECEIPT_NUMBER
  • RECEIPT_DATE
  • RECEIPT_METHOD_ID

AR_RECEIVABLE_APPLICATIONS_ALL: This table store accounting information for cash and credit memo application. This also stores information about refund and write-off. Possible statuses can be APP, UNAPP, UNID and ACC. Main columns are:

  • AMOUNT_APPLIED: Amount applied on Credit memo or receipt.
  • GL_DATE: Accounting date
  • DISPLAY: Latest application
  • APPLY_DATE: Application date 
  • APPLICATION_TYPE: CASH or CM
  • STATUS: APP for apply, UNAPP for Unapply, UNID for unidentified
  • CASH_RECEIPT_ID: Receipt id
  • APPLIED_CUSTOMER_TRX_ID: Transaction Id applied on receipt or credit memo
  • APPLIED_PAYMENT_SCHEDULE_ID: Payment schedule id applied on receipt or credit memo.

HZ_PARTIES: This table stores basic information about parties that can be shared with any relationship that the party might establish with another party. Party can be supplier, customer, employee, bank. Party can be supplier, customer, employee, bank.

  • PARTY_ID: Party identifier
  • PARTY_NUMBER: Unique identification of party
  • PARTY_NAME: Name of party.
  • PARTY_TYPE : It can be Person, Organization, Group

HZ_CUST_ACCOUNTS: Stores customer details associated with party. Main columns are:

  • CUST_ACCOUNT_ID
  • ACCOUNT_NUMBER
  • PARTY_ID: 
  • CUSTOMER_TYPE: I for Internal , R for External.
  • CUSTOMER_CLASS_CODE


HZ_CUST_ACCT_SITES_ALL: Stores customer account site details.

  • CUST_ACCOUNT_ID
  • CUST_ACCT_SITE_ID
  • PARTY_SITE_ID
  • SHIP_TO_FLAG
  • BILL_TO_FLAG

HZ_CUST_SITE_USES_ALL: Stores customer site Bill To, Ship To.

  • SITE_USE_ID
  • SITE_USE_CODE
  • CUST_ACCT_SITE_ID
HZ_LOCATIONS: This table stores address information of parties such as street name, building number, postal code. LOCATION_ID is unique identifier. Main columns are:

  • ADDRESS1: Stores first line of address.
  • ADDRESS2: Stores second line of address.
  • ADDRESS3: Stores third line of address.
  • ADDRESS4: Stores fourth line of address
  • CITY: Stores city.
  • STATE: State
  • POSTAL_CODE: Stores postal or PIN code.
  • COUNTRY: Stores country code like IN, US. Identify from TERRITORY_CODE from FND_TERRITORY table.

đŸ‘‰Detailed Queries Links

Subscribe to: Comments (Atom)