Search Results customer_postal_code

Overview

AS_COLLATERAL_REQUESTS_V is a reporting view in the Oracle EBS Sales Foundation (AS) module that consolidates collateral request header data with the customer, contact, address, promotion, word processor, and territory information needed to fulfill and track collateral mailings. In Oracle EBS 12.1.1 and 12.2.2, collateral requests support marketing and sales initiatives by generating personalized letters or documents that are shipped to customers or contacts. The view joins the transactional collateral request record (AS_COLLATERAL_REQ) to the descriptive data required for printing, shipping, and territory assignment, exposing both structured address data and descriptive lookup meanings in a single queryable object.

The view is especially useful because it flattens several one-to-many relationships into usable, denormalized columns. Users searching for "customer_postal_code" will find the postal code available through the ADDR.POSTAL_CODE column (customer/contact address) and the PADDR.POSTAL_CODE column (person or party address). This makes the view a common source for address validation, mailing-list extraction, and collateral fulfillment reporting.

Underlying Base Objects

The view is defined over the following documented base objects:

• AS_COLLATERAL_REQ (CR) — the primary collateral request transaction table.
• AS_PROMOTIONS (P) — the letter/promotion definition, supplying CODE, NAME, and EXTERNAL_FILE_NAME.
• AS_WORD_PROCESSORS (WP) — the word processor configuration, supplying PATH, STARTUP_COMMAND, LETTER_LOCATION_PATH, and MERGE_FILE_LOCATION_PATH.
• RA_CUSTOMERS (CUST) — the customer master, supplying CUSTOMER_NAME.
• RA_CONTACTS (CONT) — contact details, supplying FIRST_NAME, LAST_NAME, CONTACT_NUMBER, and TITLE.
• RA_ADDRESSES (ADDR and PADDR) — the address table, aliased twice to provide both the customer/contact address and the person/party address, including POSTAL_CODE, CITY, STATE, PROVINCE, and COUNTY.
• AS_LOOKUPS (ASLKP1, ASLKP2) — lookup meanings for status and delivery method.
• FND_TERRITORIES_TL (TERR, PTERR) — translated territory names and codes.

The joins are driven primarily from CR.LETTER_ID = P.PROMOTION_ID and CR.ADDRESS_ID = ADDR.ADDRESS_ID, with additional joins to contacts, customers, and territories. The ETRM metadata notes that the view is not implemented in the reference database, meaning its presence depends on the specific installation and module configuration.

Key Columns

• COLLATERAL_REQUEST_ID, STATUS, PARTIAL_SHIP_FLAG, SCHEDULED_DATE, COMPLETION_DATE — request identity and lifecycle state.
• CUSTOMER_ID, CONTACT_ID, PERSON_ID, ADDRESS_ID — foreign keys to the customer, contact, person, and address records.
• CUSTOMER_NAME, FIRST_NAME, LAST_NAME, CONTACT_NUMBER, TITLE — recipient identity details.
• ADDR.POSTAL_CODE, ADDR.CITY, ADDR.STATE, ADDR.PROVINCE, ADDR.COUNTY and the PADDR equivalents — postal and geographic data used for mailing.
• TERRITORY_SHORT_NAME and TERRITORY_CODE — territory assignment for both the primary and person addresses.
• LETTER_ID, CODE, NAME, EXTERNAL_FILE_NAME — the promotion/letter definition.
• PRINTER_NAME, SHIPMENT_METHOD, DELIVERY_METHOD, SHIPMENT_INSTRUCTIONS, MERGE_FILE_NAME — fulfillment and dispatch configuration.
• ATTRIBUTE1 through ATTRIBUTE15 and ATTRIBUTE_CATEGORY — descriptive flexfield support.
• ASLKP1.MEANING and ASLKP2.MEANING — decoded lookup values.

Common Use Cases and Queries

Typical uses include building mailing lists, verifying customer postal codes, reconciling scheduled versus completed collateral shipments, and auditing territory assignment. A representative query extracting postal codes and recipient details is:

SELECT cr.collateral_request_id, cust.customer_name, cont.last_name, addr.postal_code, paddr.postal_code AS person_postal_code, cr.status FROM as_collateral_requests_v cr WHERE cr.postal_code IS NOT NULL;

Analysts also use the view to join to order or shipment data via ORDER_NUMBER, and to drive concurrent programs that generate merged letters. Because it exposes standard WHO columns (CREATED_BY, LAST_UPDATE_DATE), it integrates cleanly with Oracle BI Publisher and other EBS reporting tools.