Results for “ams_ar_transaction_v”

AI-generated from documented ETRM metadata — verify critical details on the linked pages.

Overview

AMS_AR_TRANSACTION_V is an Oracle E-Business Suite view belonging to the AMS (Marketing) product family. It exposes Receivables transaction data in a denormalized, marketing-friendly form, joining the core AutoInvoice and Receivables transaction tables with customer and site-use master data. Its stated purpose is to present Receivables Transactions for consumption by Marketing modules, reporting, and integration logic that needs invoice-level detail alongside customer account and site-use context without navigating the raw transactional schema. In the context of a search for hz_cust_site_uses, this view is directly relevant: it is one of the primary AMS views that joins to HZ_CUST_SITE_USES three times (aliased HSC, HSB, HSS) to resolve sold-to, bill-to, and ship-to address purposes for each transaction. Documentation explicitly notes that the object is "Not implemented in this database" in the source environment, meaning the view must be deployed or compiled in the target instance before it can be queried.

Underlying Base Objects

The view is defined over the following base tables:

• RA_CUSTOMER_TRX (aliased TRX and PTX) — the master Receivables transaction header, and a self-join to retrieve the previous transaction number.
• AR_PAYMENT_SCHEDULES (PAY) — payment schedule, joined with an outer (+) operator to TRX.CUSTOMER_TRX_ID so transactions without schedules are retained.
• RA_CUST_TRX_TYPES (TTY) — transaction type name.
• RA_TERMS (TRM) — payment terms name.
• HZ_CUST_ACCOUNTS (HCC, HCB, HCS) — customer account names for the sold-to, bill-to, and ship-to accounts.
• HZ_CUST_SITE_USES (HSC, HSB, HSS) — site-use codes for the sold-to, bill-to, and ship-to sites, resolving each SITE_USE_ID into its purpose code.

All customer and site-use joins are inner joins, so a transaction lacking a valid account or site-use reference is excluded from the result set. No base objects are formally documented in the 12.2.2 metadata record; the authoritative definition is the view text itself.

Key Columns

• CUSTOMER_TRX_ID, TRX_NUMBER, TRX_DATE — transaction primary key, number, and date.
• CUST_TRX_TYPE_ID / NAME, COMPLETE_FLAG, STATUS_TRX — transaction type and lifecycle status.
• SOLD_TO_CUSTOMER_ID / ACCOUNT_NAME / SITE_USE_ID / SITE_USE_CODE / CONTACT_ID — sold-to party details derived from HZ_CUST_ACCOUNTS and HZ_CUST_SITE_USES.
• BILL_TO_* and SHIP_TO_* — parallel sets of customer ID, account name, site-use ID, site-use code, and contact ID.
• PREVIOUS_CUSTOMER_TRX_ID / PTX.TRX_NUMBER — credit or adjustment lineage.
• TERM_ID / TERM_NAME / TERM_DUE_DATE — payment terms context.
• Payment schedule columns (PAY.*) — AMOUNT_DUE_ORIGINAL, AMOUNT_DUE_REMAINING, AMOUNT_APPLIED, AMOUNT_ADJUSTED, AMOUNT_IN_DISPUTE, AMOUNT_CREDITED, tax, freight, discount originals and remainings, CASH_RECEIPT_ID, DUE_DATE, and STATUS.
• INVOICE_CURRENCY_CODE, SHIP_DATE_ACTUAL, WAYBILL_NUMBER — shipping and currency attributes.

Common Use Cases and Queries

Typical uses include marketing analytics on invoiced customers, exposure of receivables detail to AMS campaigns, and reconciliation reports that must display site-use purpose codes. A representative query filtering by site-use code (the likely intent of the hz_cust_site_uses search) is:

• SELECT TRX_NUMBER, SOLD_TO_ACCOUNT_NAME, SOLD_TO_SITE_USE_CODE, BILL_TO_SITE_USE_CODE, SHIP_TO_SITE_USE_CODE, AMOUNT_DUE_REMAINING FROM AMS_AR_TRANSACTION_V WHERE SOLD_TO_SITE_USE_CODE = 'SHIP_TO' AND TRX_DATE >= :p_start_date;
• Joining this view back to HZ_CUST_SITE_USES on SOLD_TO_SITE_USE_ID or BILL_TO_SITE_USE_ID to obtain full site address and location detail when only the code is exposed.
• Aggregating AMOUNT_DUE_REMAINING by BILL_TO_ACCOUNT_NAME for collections-oriented marketing segmentation.

Because the view is not implemented by default, confirm compilation against RA and HZ table structures in the target 12.1.1 or 12.2.2 instance before relying on it in concurrent programs or BI Publisher data models.