Results for “oe_deliver_to_orgs_v”

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

Overview

OE_DELIVER_TO_ORGS_V is a read-only view owned by the APPS schema in Oracle E-Business Suite, defined under the Order Management (ONT) product. As its ETRM description states, the view "is used to get and validate the delivery to organizations." In practice, it presents a flattened, denormalized set of ship-to or deliver-to customer site records, filtered to those customer site uses whose SITE_USE_CODE equals 'DELIVER_TO'. The view is exposed under a legacy-style column layout reminiscent of the Oracle Receivables / Order Management organization structures, but its data source is entirely the Trading Community Architecture (TCA) customer model.

Because it returns delivery-capable customer sites joined to their addresses and accounts, the view is commonly used at runtime by Order Management logic and by extensions that need to resolve a deliver-to site into its location, address, and shipping attributes. It is a validation lookup rather than a transactional table; it does not store data independently.

Underlying Base Objects

Per the documented 12.2.2 metadata, OE_DELIVER_TO_ORGS_V is defined over four synchronized base objects:

• HZ_CUST_SITE_USES_ALL (aliased SITE) — the driving table, providing SITE_USE_ID, SITE_USE_CODE, STATUS, PRIMARY_FLAG, ORG_ID, and shipping attributes such as warehouse, freight terms, FOB point, ship-via, and partial-shipment flags.
• HZ_CUST_ACCT_SITES (aliased ACCT_SITE) — supplies CUST_ACCOUNT_ID (exposed as CUSTOMER_ID) and the account-site status.
• HZ_PARTY_SITES (aliased PARTY_SITE) — links customer accounts and site uses to physical sites; it is part of the documented join but contributes no output columns in the excerpted view text.
• HZ_LOCATIONS (aliased LOC) — provides the address lines, city, state/province, postal code, and country.

The view is filtered by SITE.SITE_USE_CODE = 'DELIVER_TO', restricting output to delivery-related sites. Many organization-style columns (BUSINESS_GROUP_ID, COST_ALLOCATION_KEYFLEX_ID, SOFT_CODING_KEYFLEX_ID, attribute columns, and audit columns) are populated with literal NULL placeholders, confirming this is a compatibility projection over TCA rather than the legacy organization table.

Key Columns

• ORGANIZATION_ID / LOCATION_ID — both mapped from HZ_CUST_SITE_USES_ALL.SITE_USE_ID; they identify the deliver-to site record.
• NAME / LOCATION_CODE — the site-use name and the site use location reference.
• CUSTOMER_ID — from HZ_CUST_ACCT_SITES.CUST_ACCOUNT_ID.
• ADDRESS_LINE_1..4, TOWN_OR_CITY, STATE/PROVINCE, POSTAL_CODE, COUNTRY — the physical address from HZ_LOCATIONS.
• SOLD_FROM_ORG_ID / SHIP_FROM_ORG_ID — both drawn from SITE.WAREHOUSE_ID.
• FOB_POINT_CODE, FREIGHT_TERMS_CODE, SHIPPING_METHOD_CODE, SHIP_PARTIAL_ALLOWED — default shipping and freight attributes used during order entry.
• PRIMARY_FLAG, STATUS, ORG_ID — site primary indicator, status, and operating unit context.
• BILL_TO_SITE_USE_ID, PRICE_LIST_ID, CONTACT_ID — associated billing site, price list, and contact references.

Common Use Cases and Queries

The view is typically used to validate that a proposed deliver-to organization exists and is active for a given customer, and to retrieve the default shipping address and freight parameters. Because cross-schema access is required, APPS privileges or a synonym are needed.

List active deliver-to sites for a customer:

SELECT organization_id, name, address_line_1, town_or_city, postal_code, country, status
FROM oe_deliver_to_orgs_v
WHERE customer_id = :p_customer_id
AND status = 'A';

Retrieve shipping defaults for order entry:

SELECT location_id, ship_from_org_id, fob_point_code, freight_terms_code,
shipping_method_code, ship_partial_allowed
FROM oe_deliver_to_orgs_v
WHERE organization_id = :p_site_use_id;

Validate a deliver-to location before submission:

SELECT COUNT(*)
FROM oe_deliver_to_orgs_v
WHERE organization_id = :p_site_use_id
AND org_id = :p_org_id;