Results for “state_code”

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

Overview

AR_REMIT_TO_STATE is a reporting and validation view owned by the APPS schema in Oracle E-Business Suite Receivables. Its purpose is to expose a consolidated list of state values that can be used as valid "remit-to state" entries during receivables processing, particularly in the context of customer remittance addresses and remittance bank information. The view derives its content from two distinct sources and merges them into a single result set using a UNION ALL operation.

The view is documented as VALID in both Oracle EBS 12.1.1 and 12.2.2 and carries the Receivables product designation (AR). It does not store data of its own; instead it presents a read-only, dynamically resolved list at query time. Because it combines physical address state values with a default lookup code, it functions as a lookup-style reference object for forms, concurrent programs, and custom integrations that need to determine the acceptable set of state values for remittance purposes.

Underlying Base Objects

The ETRM metadata documents two referenced base objects: AR_LOOKUPS (itself a VIEW) and HZ_LOCATIONS (exposed through a SYNONYM). The view definition operates as follows:

• First branch: It selects COUNTRY and STATE from HZ_LOCATIONS, grouping by COUNTRY and STATE to produce the distinct set of country/state combinations maintained in the Trading Community Architecture location model.
• Second branch: It selects LOOKUP_CODE, LOOKUP_CODE, and MEANING from AR_LOOKUPS where LOOKUP_TYPE = 'REMIT', adding the default remit lookup code as an additional, non-geographic option.

The UNION ALL concatenates both result sets without eliminating duplicates, ensuring the default remit lookup entry remains available even if it coincides with a geographic state value.

Key Columns

The view exposes three columns, mapped in the documented structure as COUNTRY, STATE_CODE, and DESCRIPTION:

• COUNTRY — In the first branch this is the country value from HZ_LOCATIONS; in the second branch it carries the LOOKUP_CODE from AR_LOOKUPS.
• STATE_CODE — In the first branch this is the state value from HZ_LOCATIONS; in the second branch it repeats the LOOKUP_CODE.
• DESCRIPTION — For geographic rows this mirrors the state value; for the default remit lookup row it carries the MEANING of the REMIT lookup code.

This dual mapping explains why the first branch projects STATE twice: the view author aligned column positions so that both branches return a consistent three-column shape, with the third position reserved for the lookup MEANING.

Common Use Cases and Queries

Typical applications include populating state lists on remittance-related forms, validating user-entered remit-to states, and driving custom reports on receivables remittance configuration. A representative query retrieving all available state options is:

• SELECT country, state_code, description FROM apps.ar_remit_to_state ORDER BY 1, 2;
• Filtering to a single country: SELECT state_code FROM apps.ar_remit_to_state WHERE country = 'US';
• Isolating the default remit lookup row: SELECT * FROM apps.ar_remit_to_state WHERE country = state_code AND description <> state_code;

Because the view relies on HZ_LOCATIONS and AR_LOOKUPS, result sets reflect the current TCA location data and the REMIT lookup configuration. No DML should be issued against the view, and performance depends on the volume of HZ_LOCATIONS rows, since the first branch performs a GROUP BY across all location records.