eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

DBA Data [Home] [Help] [Dependency Information]

TABLE: AR.AR_TRX_SUMMARY

Object Details

Object Name:	AR_TRX_SUMMARY
Object Type:	TABLE
Owner:	AR
FND Design Data:	AR.AR_TRX_SUMMARY
Subobject Name:
Status:	VALID

This table stores the fields, such as 'Total Invoices Value', 'Total Invoices Count', 'Total Cash Receipts Value', etc., that require summarization for a specified period of time. The lowest level of granularity at which the data can be stored and retrieved from this table is as of a specified date for a given currency at a specified 'BILL_TO' location for a specific account of a party.

Storage Details

Tablespace:	APPS_TS_TX_DATA
PCT Free:	10
PCT Used:

Indexes

Index	Type	Uniqueness	Tablespace	Column
AR_TRX_SUMMARY_U1	NORMAL	UNIQUE	APPS_TS_TX_IDX	CUST_ACCOUNT_ID SITE_USE_ID CURRENCY AS_OF_DATE ORG_ID
AR_TRX_SUMMARY_N1	NORMAL	NONUNIQUE	APPS_TS_TX_IDX	SITE_USE_ID CURRENCY AS_OF_DATE

Columns

Name	Datatype	Length	Mandatory	Comments
CUST_ACCOUNT_ID	NUMBER	(15)	Yes	Customer Account Identifier
SITE_USE_ID	NUMBER	(15)	Yes	Site Use Identifier
ORG_ID	NUMBER	(15)		Organization identifier
CURRENCY	VARCHAR2	(30)	Yes	Currency code
AS_OF_DATE	DATE		Yes	Specific date for which the current summary record has been created.
LAST_UPDATE_DATE	DATE		Yes	Standard Who column - date when a user last updated this row.
LAST_UPDATED_BY	NUMBER	(15)	Yes	Standard who column - user who last updated this row (foreign key to FND_USER.USER_ID).
CREATION_DATE	DATE		Yes	Standard who column - date when this row was created.
CREATED_BY	NUMBER	(15)	Yes	Standard who column - user who created this row (foreign key to FND_USER.USER_ID).
LAST_UPDATE_LOGIN	NUMBER	(15)		Standard who column - operating system login of user who last updated this row (foreign key to FND_LOGINS.LOGIN_ID).
OP_BAL_HIGH_WATERMARK	NUMBER			This column value contains the highest Open Receivables Balance for the specific Account, Site, Currency and date (identified by as_of_date). The Open Receivables Balance is calculated by summing the amount_due_remaining on the payment schedules of all the debit items and unresolved cash on all cash receipts.
OP_BAL_HIGH_WATERMARK_DATE	DATE			Highest open receivables balance, which is the sum of the amount_due_remaining on the payment schedules of the debit items and unresolved cash on all cash receipts, for a specific account, site, currency, and date.
TOTAL_CASH_RECEIPTS_VALUE	NUMBER			Sum of the amounts of all Cash Receipts for the specific Account, Site, Currency and a date.
TOTAL_CASH_RECEIPTS_COUNT	NUMBER			Count of payment schedules (installments) of all Chargebacks for the specific Account, Site, Currency and a date.
TOTAL_INVOICES_VALUE	NUMBER			Sum of the amount_due_original on payment schedules (installments) of all Invoices for the specific Account, Site, Currency and a date.
TOTAL_INVOICES_COUNT	NUMBER			Count of the payment schedules (installments) of all Invoices for the specific Account, Site, Currency and a date.
INV_PAID_AMOUNT	NUMBER			Sum of the total amount paid against closed invoices for a specific Account, Site, Currency and as of a given date.
INV_INST_PMT_DAYS_SUM	NUMBER			Summation of the product of number of days that it took to make payments against invoice installments and the paid amount for a specific Account, Site, Currency and a date i.e.(amount_applied*(apply_date - due_date)).
TOTAL_BILLS_RECEIVABLES_VALUE	NUMBER			Sum of the amount_due_original on payment schedules (installments) of all Bills Receivables for the specific Account, Site, Currency and a date.
TOTAL_BILLS_RECEIVABLES_COUNT	NUMBER			Count of payment schedules (installments) of all Bills Receivables for the specific Account, Site, Currency and a date.
TOTAL_CREDIT_MEMOS_VALUE	NUMBER			Sum of the amount_due_original on payment schedules (installments) of all Credit Memos for the specific Account, Site, Currency and a date.
TOTAL_CREDIT_MEMOS_COUNT	NUMBER			Count of payment schedules (installments) of all Credit Memos for the specific Account, Site, Currency and a date.
TOTAL_DEBIT_MEMOS_VALUE	NUMBER			Sum of the amount_due_original on payment schedules (installments) of all Debit Memos for the specific Account, Site, Currency and a date.
TOTAL_DEBIT_MEMOS_COUNT	NUMBER			Count of payment schedules (installments) of all Debit Memos for the specific Account, Site, Currency and a date.
TOTAL_CHARGEBACK_VALUE	NUMBER			um of the amount_due_original on payment schedules (installments) of all Chargebacks for the specific Account, Site, Currency and a date.
TOTAL_CHARGEBACK_COUNT	NUMBER			um of the amount_due_original on payment schedules (installments) of all Chargebacks for the specific Account, Site, Currency and a date.
TOTAL_EARNED_DISC_VALUE	NUMBER			Sum of the Earned Discounts that were given on the closed payment schedules (installments) of all invoices for the specific Account, Site, Currency and a date.
TOTAL_EARNED_DISC_COUNT	NUMBER			Sum of the Earned Discounts that were given on the closed payment schedules (installments) of all invoices for the specific Account, Site, Currency and a date.
TOTAL_UNEARNED_DISC_VALUE	NUMBER			Sum of the Unearned Discounts that was given on the closed payment schedules (installments) of all invoices for the specific Account, Site, Currency and a date.
TOTAL_UNEARNED_DISC_COUNT	NUMBER			Count of the Unearned Discounts that were given on the closed payment schedules (installments) of all invoices for the specific Account, Site, Currency and a date.
TOTAL_ADJUSTMENTS_VALUE	NUMBER			Sum of the adjustment amounts on the payment schedules of all the debit items for the specific Account, Site, Currency and a date (identified by as_of_date column). Special adjustments, such as Adjustment Reversal, are not included because they are used for internal accounting purposes only.
TOTAL_ADJUSTMENTS_COUNT	NUMBER			Count of the adjustments on the payment schedules of all the debit items for the specific Account, Site, Currency and a date. Special adjustments, such as Adjustment Reversal are not included.
TOTAL_DEPOSITS_VALUE	NUMBER			Sum of the amount_due_original on payment schedules (installments) of all Deposits for the specific Account, Site, Currency and a date.
TOTAL_DEPOSITS_COUNT	NUMBER			Count of payment schedules (installments) of all Deposits for the specific Account, Site, Currency and a date.
SUM_APP_AMT_DAYS_LATE	NUMBER			Summation of the product of amount applied and the difference in apply date and due date for all the cash receipt applications against the Invoices Installments for the specific Account, Site, Currency and a date i.e. (amount_applied*(apply_date - due_date)).
SUM_APP_AMT	NUMBER			Sum of the amount applied against the closed payment schedules (installments) of all the Invoices for the specific Account, Site, Currency and date.
COUNT_OF_TOT_INV_INST_PAID	NUMBER			Count of the payment schedules (installments), that have been paid off completely, i.e., status = CL, of all Invoices for the specific Account, Site, Currency and a date.
COUNT_OF_INV_INST_PAID_LATE	NUMBER			Count of the payment schedules (installments) that have been paid late, i.e., status = CL and actual_date_closed > due_date of all Invoices for the specific Account, Site, Currency and a date.
COUNT_OF_DISC_INV_INST	NUMBER			Count of the payment schedules (installments) with earned or unearned discounts applied to Invoices for the specific Account, Site, Currency and a date
LARGEST_INV_AMOUNT	NUMBER			Transaction amount corresponding to the largest invoice for the specific Account, Site, Currency and a date.
LARGEST_INV_DATE	DATE			Transaction date corresponding to the largest invoice for the specific Account, Site, Currency and a date.
LARGEST_INV_CUST_TRX_ID	NUMBER	(15)		Unique identifier of the largest invoice for the specific Account, Site, Currency and a date.
DAYS_CREDIT_GRANTED_SUM	NUMBER			Summation of the product of amount_due_original and difference of the due_date and trx_date, i.e., (amount_due_original * (due_date - trx_date)), for the payment schedules (installments) of all Invoices for the specific Account, Site, Currency and a date.
NSF_STOP_PAYMENT_COUNT	NUMBER			Count of all cash receipts having a status of Reversed with a Reversal Category of Non Sufficient Funds for the specific Account, Site, Currency and a date.
NSF_STOP_PAYMENT_AMOUNT	NUMBER			Sum of the amount on all Cash Receipts having a status of Reversed with a Reversal Category of Non Sufficient Funds for the specific Account, Site, Currency and a date.
REFERENCE_1	VARCHAR2	(240)		Reference columns for future usage
REFERENCE_2	VARCHAR2	(240)		Reference columns for future usage
REFERENCE_3	VARCHAR2	(240)		Reference columns for future usage
REFERENCE_4	VARCHAR2	(240)		Reference columns for future usage
REFERENCE_5	VARCHAR2	(240)		Reference columns for future usage

Query Text

Cut, paste (and edit) the following text to query this object:


SELECT CUST_ACCOUNT_ID
,      SITE_USE_ID
,      ORG_ID
,      CURRENCY
,      AS_OF_DATE
,      LAST_UPDATE_DATE
,      LAST_UPDATED_BY
,      CREATION_DATE
,      CREATED_BY
,      LAST_UPDATE_LOGIN
,      OP_BAL_HIGH_WATERMARK
,      OP_BAL_HIGH_WATERMARK_DATE
,      TOTAL_CASH_RECEIPTS_VALUE
,      TOTAL_CASH_RECEIPTS_COUNT
,      TOTAL_INVOICES_VALUE
,      TOTAL_INVOICES_COUNT
,      INV_PAID_AMOUNT
,      INV_INST_PMT_DAYS_SUM
,      TOTAL_BILLS_RECEIVABLES_VALUE
,      TOTAL_BILLS_RECEIVABLES_COUNT
,      TOTAL_CREDIT_MEMOS_VALUE
,      TOTAL_CREDIT_MEMOS_COUNT
,      TOTAL_DEBIT_MEMOS_VALUE
,      TOTAL_DEBIT_MEMOS_COUNT
,      TOTAL_CHARGEBACK_VALUE
,      TOTAL_CHARGEBACK_COUNT
,      TOTAL_EARNED_DISC_VALUE
,      TOTAL_EARNED_DISC_COUNT
,      TOTAL_UNEARNED_DISC_VALUE
,      TOTAL_UNEARNED_DISC_COUNT
,      TOTAL_ADJUSTMENTS_VALUE
,      TOTAL_ADJUSTMENTS_COUNT
,      TOTAL_DEPOSITS_VALUE
,      TOTAL_DEPOSITS_COUNT
,      SUM_APP_AMT_DAYS_LATE
,      SUM_APP_AMT
,      COUNT_OF_TOT_INV_INST_PAID
,      COUNT_OF_INV_INST_PAID_LATE
,      COUNT_OF_DISC_INV_INST
,      LARGEST_INV_AMOUNT
,      LARGEST_INV_DATE
,      LARGEST_INV_CUST_TRX_ID
,      DAYS_CREDIT_GRANTED_SUM
,      NSF_STOP_PAYMENT_COUNT
,      NSF_STOP_PAYMENT_AMOUNT
,      REFERENCE_1
,      REFERENCE_2
,      REFERENCE_3
,      REFERENCE_4
,      REFERENCE_5
FROM AR.AR_TRX_SUMMARY;

Dependencies

[top of page]

AR.AR_TRX_SUMMARY does not reference any database object

AR.AR_TRX_SUMMARY is referenced by following:

AR: AR_TRX_SUMMARY#