Search Results magnetic_format_code

Overview

AR_CUSTOMER_BILLS_TRX_V is a seeded Oracle E-Business Suite view owned by the APPS schema and registered against the Receivables (AR) product. It presents a denormalized, transaction-level projection of customer bills — that is, customer transactions of the invoice and credit memo families — enriched with the attributes required to print, format, and transmit billing documents to customers. In Release 12.1.1 and 12.2.2 the object remains valid and is consumed principally by the Receivables billing and printing programs, including the invoice print and consolidated billing extract processes, and by downstream reporting and integration layers that require the billing-relevant subset of transaction data.

Because FORMAT_PROGRAM_ID is projected directly from RA_CUST_TRX_TYPES_ALL, the view is the documented source through which the concurrent program associated with a transaction type's printing format is resolved. This makes it a natural entry point for any query that must determine which format program will execute for a given customer transaction, or that must reconcile printed output to its originating format definition.

Underlying Base Objects

The view is defined over a fixed set of documented base objects. Core transaction data originates from RA_CUSTOMER_TRX, joined to RA_BATCH_SOURCES_ALL, RA_CUST_TRX_TYPES_ALL, and CE_BANK_ACCOUNTS. Customer master and site data are drawn from the HZ_ family, including HZ_PARTIES, HZ_CUST_ACCOUNTS, HZ_CUST_ACCOUNT_ROLES, HZ_CUST_ACCT_SITES, HZ_CUST_SITE_USES, HZ_LOCATIONS, HZ_PARTY_SITES, HZ_ORGANIZATION_PROFILES, and HZ_RELATIONSHIPS. Remittance and payment-related information is supplied by CE_BANK_ACCT_USES_OU_V, IBY_EXT_BANK_ACCOUNTS, IBY_TRXN_EXTENSIONS_V, and the IBY_FNDCPT_TRXN_PUB package. Reference and utility objects include AR_LOOKUPS, AR_TRANSACTION_HISTORY, FND_TERRITORIES_VL, FND_GLOBAL, ARH_ADDR_PKG, and ARPT_SQL_FUNC_UTIL.

Address formatting is performed inline through ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE, and organization-level context is resolved via FND_GLOBAL, giving the view multi-organization awareness.

Key Columns

• FORMAT_PROGRAM_ID — the concurrent program identifier controlling the print format associated with the transaction's cust_trx_type; sourced from RA_CUST_TRX_TYPES_ALL.
• CUSTOMER_TRX_ID, TRX_NUMBER, TRX_DATE, DOC_SEQUENCE_VALUE — transaction identity and document sequencing information.
• CUST_TRX_TYPE_ID, SIGNED_FLAG, DRAWEE_ISSUED_FLAG, POST_TO_GL, DEFAULT_PRINTING_OPTION, MAGNETIC_FORMAT_CODE — type-level billing and accounting controls.
• BATCH_SOURCE_ID, AUTO_TRX_NUMBERING_FLAG, BATCH_ID — batch source and batch grouping.
• PRINTING_LAST_PRINTED, PRINTING_OPTION, COMPLETE_FLAG, BR_UNPAID_FLAG, BR_ON_HOLD_FLAG — printing state and bill-review status.
• CUSTOMER_REFERENCE, COMMENTS, SPECIAL_INSTRUCTIONS, ATTRIBUTE1..15 — free-form and descriptive fields.
• DRAWEE_ID, DRAWEE_SITE_USE_ID, ACCOUNT_NUMBER, JGZZ_FISCAL_CODE, TAX_REFERENCE, formatted address line — remit-to and drawee identification.

Common Use Cases and Queries

The most frequent application is resolving the print format for a transaction type, particularly when diagnosing why an invoice printed with an unexpected layout or when auditing format program assignments.

SELECT trx_number, cust_trx_type_id, format_program_id
FROM   apps.ar_customer_bills_trx_v
WHERE  customer_trx_id = :p_customer_trx_id;

A second scenario extracts all unprinted transactions for a batch or batch source, filtering on printing state and type attributes:

SELECT trx_number, trx_date, batch_source_id, printing_option
FROM   apps.ar_customer_bills_trx_v
WHERE  printing_last_printed IS NULL
AND    batch_source_id = :p_batch_source_id;

A third pattern joins the format program identifier to FND_CONCURRENT_PROGRAMS to surface the human-readable program name, supporting documentation and impact analysis:

SELECT v.trx_number, p.concurrent_program_name, p.user_concurrent_program_name
FROM   apps.ar_customer_bills_trx_v v,
       apps.fnd_concurrent_programs_vl p
WHERE  v.format_program_id = p.concurrent_program_id
AND    v.customer_trx_id = :p_customer_trx_id;