Address validation in R12
Document Purpose
This document is intended to provide the reader with an overview of the functionality and usage of Address Validation (AV), which is designed to help a deploying company to improve the accuracy and completeness of their customer address data.
The primary audience for this document includes individuals within a deploying company who are responsible for maintaining the integrity and postal validity of customer address data and consultants who are implementing Address Validation on behalf of a deploying company.
This white paper provides an insight on the concept of Address Validation, its implementation and usage. It also provides a high level view of Location Services Infrastructure (LSI) that serves as the foundation for Address Validation.
The document primarily focuses on the services provided in the initial release of LSI, namely Address Validation on large volumes of data. This functionality is rendered in two modes:
• Standalone Mode – Address Validation Concurrent program that can be run by the Trading Community Manager to validate addresses already existing within TCA registry. This concurrent program can run on-demand or be scheduled to run automatically at fixed intervals.
• Batch Import Mode – TCA’s Import program can optionally invoke Address Validation either during pre-import or directly during import while loading customer data into TCA registry.
Functionality
LSI acts as an XML open–standards based black box that enables integration with third – party service providers and their custom made solutions. In case of address validation, LSI acts as the “middle layer” that provides integration with vendor or customer developed adapters. As illustrated in Fig.1, callers such as Bulk Import or the Address Validation concurrent program invoke LSI that sends and receives address data to and from the adapters .
Scope for Initial Release
Calls to LSI are restricted to just Bulk Import and the Address Validation concurrent program within the E-Business suite. Hence, the functionality to call this infrastructure from V2 Apis, CPUI
1 or any
other caller is not available in this release.
This document specifically describes the Address Validation functionality, as released in 11i.HZ.L minipack.
In this release the only location service that is being delivered is Address Validation and other services namely Geocoding
2, Location Enrichment
3 etc.are excluded.
The address validation solution is optimized for synchronous processing of bulk requests and cannot be used for single, asynchronous requests. In simpler words, the user can invoke address validation on groups of locations submitted to the adapter in batches . The flexibility to validate individual addresses, at the time of creation from UI, does not exist.
Address Validation is the process of comparing a raw or user-entered address against a good or authorized data source to determine if the address is recognized and factually correct. An example of a good data source for US addresses is United States Postal Service.
One of the outcomes of address validation is address correction in which a user-entered address is evaluated, corrected, completed and returned for further processing.
Need for Address Validation
In today’s business scenario, enterprises have an increasing need for a clean and consolidated view of customer data. Therefore, investing in data quality tools that support cleansing and enrichment of customer data is regarded as instrumental in ensuring that access to most reliable and up-to-date data is possible for decision–making.
Address Validation is a technique that enhances data quality by providing a quick and reliable way for improving the accuracy and completeness of customer address data. Significant cost savings on postage; printing and staff resources can be realized by ensuring that the addresses in the enterprise database are complete, correctly formatted, and verified as valid by a postal authority.
How AV Works
Setups
Before address validation can be triggered, some basic setups need to be done. These setups include configuring either single or multiple vendor adapters to validate location records belonging to various countries.
In HZ.L minipack, a new subtab called Adapters was introduced under the Administration tab. This subtab contains the adapter search and configuration pages. An overview of the adapter configuration parameters is as mentioned in this table:
| Adapter Configuration parameters | Description |
|---|
| Adapter Name |
The content source that identifies the vendor adapter.
|
| Adapter Meaning | Descriptive text used to define the meaning or usage for adapter |
| Adapter Description | User entered description for adapter |
| Invoke Method |
Identifies the communication protocol used in sending and receiving messages from the adapter. Currently only HTTP Post is supported.
|
| Host Address | Host address of the adapter engine |
| Default Batch Size | The default number of records that a single batch can handle. This parameter is not used if the maximum batch size is mentioned in the configuration. |
| Maximum Batch Size | Maximum number of records that the adapter can handle in a single batch. |
| Message Format | Message format used in the xml messages while communicating with the vendor adapter. The initial release had elements from OAG 4 XML 8.0 standard with a few oracle customizations. The final format will conform to OAG XML 9.0 standard. |
| Default Replace Status Code | This is a default location service threshold that must be exceeded before an address from vendor adapter gets accepted for create/update. |
After the adapter’s configuration parameters have been set, additional details regarding the countries that the adapter’s libraries support need to be configured. The user can enable different countries whose addresses the adapter can validate. One can also specify the adapter to be thedefault choice for validating addresses belonging to a particular country. Thus, using the pages under the Adapters subtab, users can configure single or multiple adapters for validating addresses.
Customization
During customization the system profiles that can be enabled / disabled at site level are as follows:
• HZ: Default Loc Adapter – This profile, when enabled at site level, specifies the system-wide default adapter that would be selected for address validation when no country default adapter is configured.
• HZ: Maintain Location History -The value for this profile determines whether a history for location records would be maintained in the HZ_Location_Profiles table.
• HZ: Allow Update to Standardized Address – This profile is instrumental in deciding whether validated locations can be updated. The only caveat to this being that if the Date_Validated and the Validation_Status_Code columns in HZ_Locations is NULL for a particular record, then that particular location still gets updated irrespective of the value set for this profile.
• HZ: Create Log for Adapters – This profile regulates the logging of details for the outbound and inbound xml docs in the HZ_Adapter_Logs table.
• Ecx: Log File Path – This profile is tied to XML Gateway and its value specifies the directory in which the gateway will write its log file. The directory specified for this profile must be valid, accessible, and have write permissions for the gateway.
Execution Modes
Address Validation can be invoked in either Standalone mode or Batch Import Mode.
Standalone mode
AV is usually called in this mode in order to verify the validity of existing location records present in HZ_Locations. User must have Trading Community Manager (TCM) responsibility in order to run the concurrent programs. There are two concurrent programs that are invoked in this mode:
• Address Validation: This is the main concurrent program invoked whenever a user submits a set of parameters to select existing locations for address validation.
• Address Validation Worker: This is the child concurrent program spawned for every individual batch submitted for validation.
Parameters for Main Concurrent Program
The user-entered parameters for selecting locations for address validation may involve various permutations of the following:
• Validation Status Code
• Validation Status Filter – A filter that imposes a set of relational operator conditions viz. (<, <=, =, >, >=) to the Validation Status Code parameter.
• Date Validated – Date the record was last validated.
• Date Validated Filter – A filter that imposes a set of relational operator conditions viz. (<, <=, =, >, >=) to the Date Validated parameter.
• Last Update Date – Date the record was last updated
• Last Update Date Filter – A filter that imposes a set of relational operator conditions viz. (<, <=, =, >,>=) to the Last Update Date parameter.
• Country – Country code corresponding to the address
• Adapter – Name of an enabled or active adapter
• Overwrite Threshold
For validation status code, date validated, last update date and their corresponding filters the user entered value can be null .
A value must always be provided for overwrite threshold.
In order to select an adapter for validating a batch of addresses the user may provide either the adapter’s name or a valid country code. In the latter case, the default adapter enabled for thatcountry is selected for address validation.
For validation status code, date validated, last update date and their corresponding filters the user entered value can be null .
A value must always be provided for overwrite threshold.
In order to select an adapter for validating a batch of addresses the user may provide either the adapter’s name or a valid country code. In the latter case, the default adapter enabled for thatcountry is selected for address validation.
However, if the user specifies neither the country code nor the adapter’s name then the main concurrent program will look for the system-wide default adapter that has been enabled at site level in the system profile: ”HZ: Default Loc Adapter”.
Based on user entered parameters and the adapter configuration settings, the main program decides the following:
• Adapter Id – This is the id corresponding to the particular adapter chosen for validating the batch.
• Address Records – The records from HZ_Locations table that need to be submitted to the vendor adapter.
• Number of batches – Number of distinct batches of locations that would be submitted to vendor adapter.
The way this number is decided is based on the following formula:
Number of addresses to be validated
Number of batches = ——————————————-
Maximum Batch Size
Corresponding to each distinct batch a new AV Worker program is spawned. After ascertaining the aforesaid parameters and spawning the required worker programs the main concurrent program exits.
Each AV worker then functions independently of one another and is responsible for just validating the location records present in its batch. The validation process consists of two steps:
1) Sending addresses to vendor adapter – This involves generating an outbound xml message for address validation. Generation of outbound xml requires the AV worker to raise a workflow event. The workflow event uses an oracle tool called Message Designer
7 to create mapping between objects so as to generate outbound xml document. For the outbound xml mapping, the source elements consist of records from HZ_Locations table (retrieved by main concurrent program) and the target consists of xml doc elements. After the mapping is completed using functions defined in XML gateway, the xml document is generated. The AV worker program then raises a BES (business event subscription) event for sending the outbound xml.
2) Processing the returned validated records – This involves a sequence of steps, as mentioned below:
• Parsing : Parsing of the inbound xml is triggered by a workflow event raised by the AV worker. The inbound xml document received from the vendor adapter is parsed to obtain the distinct elements of standardized addresses. This is accomplished by creating a mapping between the source (the inbound xml document) and the target (elements defined in the calling api: hz_location_services_pub.get_validated_xml ()).
• Tax Validation : After the inbound xml is received and parsed all the address records are individually subjected to tax validation.
Purpose of Tax Validation
Tax validation is important whenever addresses are updated. In fact eTax’s tax validation is currently part of the TCA v2 apis. LSI is architected so that the v2 apis continue to check for changes in addresses that violate the tax validation rules.
The whole purpose of tax validation is that updates to addresses should not be allowed if modification to city, county, state, province, country and postal code will violate the tax rules for that jurisdiction.
Tax Validation Impact on Address Update
As illustrated in the figure, if the adapter returned location passes tax validation then the validated address may be used to update both HZ_Locations and HZ_Location_Profiles table. In case the tables get updated then loc_id needs to be regenerated for loc_assignment table if the values of city, country, state,county,province and postal code are modified.
However if the returned location fails tax validation then further action on that adapter returned location is decided on the basis of the system profile value for ‘HZ: Maintain_Loc_History’ and the corresponding location profile record. If the maintain history profile option is ‘Yes’ and thereexists a location profile record with the same actual_content_source (as the current adapter used for validating the location),then no action is taken. Otherwise a new record is inserted in the HZ_Location_Profiles table with SST flag set to ‘No’
• Determine whether to create/update the validated address : After the adapter returned records pass tax validation, the validation status code of each record is compared to the overwrite threshold. Location records with adapter returned validation status code less than the overwrite threshold are eligible candidates for creation /update within HZ_Locations and HZ_Location_Profiles table. Based on the customization done for the system profiles the mechanism of updating location and location profiles records is described in the tables below:
Allow Update to Standardized Address Profile Option Set to No
| Content Source | Maintain Location History:Yes | Maintain Location History: No |
|---|
Same content source in profile exists
| If original record has sst flag =’Y', create profile record for content source with SST flag = ‘N’ and end_date = sysdate. Update Locations record and set date_validated and validation_status_code to ‘NULL’ |
If original record has sst flag = ‘Y’, No update in either Location or Location profile record
|
Same content source in profile exists
| If original record has sst flag = ‘N’, end date the existing profile record and create a new profile record for the content source with SST flag = ‘N’. Update Locations record and set date_validated and validation_status_code to ‘NULL’ | If original record has sst flag = ‘N’, No update in Location record and just update the profile record |
| Same content source in profile does not exist | Create a new profile record for the content source with SST flag = ‘N’. Update Locations record and set date_validated and validation_status_code to ‘NULL’ | Create a new profile record for the content source with SST flag = ‘N’. Update Locations record and set date_validated and validation_status_code to ‘NULL’ |
Allow Update to Standardized Address Profile Option Set to Yes
| Content Source | Maintain Location History:Yes | Maintain Location History: No |
|---|
Same content source in profile exists
| End date the existing location profile record. Create a new profile record for the content source with SST flag = ‘Y’ |
Update profile of that content source with SST flag = ‘Y’.
|
Same content source in profile exists
| Update location record by marking the date_validated and validation_status_code columns. | Update location record by marking the date_validated and validation_status_code columns. |
| Same content source in profile does not exist | Create profile for the content source with SST flag = ‘Y’. Update location record by marking the date_validated and validation_status_code columns. | Create profile for the content source with SST flag = ‘Y’.Update location record by marking the date_validated and validation_status_codecolumns |
Note: The communication protocol used in sending the outbound xml and receiving the inbound xml is HTTP v1.1 Post. In order to accommodate communication failures that may occur during the HTTP request, a default timeout parameter is set in TCA for 300 seconds. The value of this parameter is controlled by the system profile “HZ: Time Out Limit for Address Validation” which can be modified by the system administrator, at the site level.
Batch Import Mode
In batch import mode, address validation can be executed as an optional pre-processing step before importing the location records within TCA registry. Before AV can be executed in this mode, party related data needs to be loaded in the HZ Interface tables (for eg. addresses should be loaded in HZ_IMP_ADDRESSES_INT). Loading of data can be accomplished using either one of these methods:
· CSV File Load: With OCO User / Superuser or OCO Data Librarian User / Superuser responsibilities, a user can load party related data which is present in CSV file format. This can be accomplished from the OCO UI by loading a new file from File Load subtab present under Import.
· D&B Import Adapter Request set: A user can execute this request set via Forms with Trading Community Manager (TCM) responsibility. Before running this request set a batch id needs to be generated by invoking the hz_imp_batch_summary_v2pub.create_import_batch () api.
· Normal Load: This can be done using SQL scripts or ETL tools like Oracle Warehouse Builder(OWB).
After data is loaded in the interface tables and the status of the batch is ‘Active’, a user with OCO Data Librarian Super User responsibility can initiate batch import and in the process trigger AV as a pre-processing step.
Address Validation Flow In Batch Import
As illustrated in the figure, following steps are involved while running AV during Import:
Step 1 : For the particular batch being imported, the first step involves scanning the country column for all the location records that are present in the interface table: HZ_IMP_ADDRESSES_INT.The value in the country column of this table is compared to the territory codes present in FND_Territories table.
Step 2 : The OCO DL Super user has the flexibility to override default country adapter settings by specifying different vendor adapters for different countries. In this case the user’s adapter selections for the various countries present in a batch are saved in HZ_IMP_ADAPTERS table.
At the time of creation of AV batches, for a particular country the HZ_IMP_ADAPTERS table is scanned to obtain the adapter’s name, corresponding to that country. If an adapter is linked to a country in this table, then that adapter is chosen to validate all records with the same country code.
In the event when a valid country code is not present for a location record or no adapter is specified either by the user or as a default then the system – wide default adapter is chosen for validating those location records.
Step 3 : After the selection of adapters is accomplished, the maximum batch size pertaining to these chosen adapters is obtained from HZ_ADAPTERS table. Based on the maximum batch size and the number of records present in the batch, either single or multiple AV batches are created. An AV batch is basically a XML file that is created by invoking some functions defined in the XML gateway and contains multiple addresses for validation.
Step 4 : These AV batches are then sent as an outbound xml doc to the vendor adapter for validation.
Step 5 : The validated addresses from the adapter are returned as an inbound xml doc to the UI Console for parsing and retrieving the new address components.
Step 6 : The new address components are marked in the _STD columns present in HZ_IMP_ADDRESSES_INT table. The other columns in the same table that are also marked include ADAPTER_CONTENT_SOURCE, ADDR_VALID_STATUS_CODE and the DATE_VALIDATED. Only those location records that have a validation status code greater than the Default Replace Status Code are accepted and have their ACCEPT_STANDARDIZED_FLAG set to ‘Y ‘ in the interface table.
Data Model Impact of AV
The tables in the database that store information pertaining to the adapter configurations done during setup include:
• HZ_ADAPTERS: This table stores information about the vendor adapters that are configured for providing address validation services.
• HZ_ADAPTER_TERRITORIES: This table stores information about the countries that are supported by a particular vendor adapter.
• HZ_ADAPTER_LOGS: This table tracks information for the outbound and inbound xml docs. Among other parameters the logs in this table identify the created_by_module,created_by_module_id and the http_status_code for a particular request.
The final results of address validation on location records in both modes is stored in the following columns:
HZ_LOCATIONS.VALIDATION_STATUS_CODE
HZ_LOCATIONS.DATE_VALIDATED
HZ_LOCATIONS. ADDRESS1
HZ_LOCATIONS. ADDRESS2
HZ_LOCATIONS. ADDRESS3
HZ_LOCATIONS. ADDRESS4
HZ_LOCATIONS. CITY
HZ_LOCATIONS. POSTAL_CODE
HZ_LOCATIONS. STATE
HZ_LOCATIONS. PROVINCE
HZ_LOCATIONS. COUNTY
HZ_LOCATIONS. COUNTRY
Based on the customization done for the system profile – HZ: Maintain Location History, the results may also be stored in the following columns:
HZ_LOCATION_PROFILES. VALIDATION_STATUS_CODE
HZ_LOCATION_PROFILES. DATE_VALIDATED
HZ_LOCATION_PROFILES. ADDRESS1
HZ_LOCATION_PROFILES. ADDRESS2
HZ_LOCATION_PROFILES. ADDRESS3
HZ_LOCATION_PROFILES. ADDRESS4
HZ_LOCATION_PROFILES. CITY
HZ_LOCATION_PROFILES. POSTAL_CODE
HZ_LOCATION_PROFILES. COUNTY
HZ_LOCATION_PROFILES. COUNTRY
HZ_LOCATION_PROFILES. PROV_STATE_ADMIN_CODE
In batch import mode when AV is invoked in either Pre-Import stage (as part of What-If analysis) or just during Import, the results are also stored in the following interface table columns:
HZ_IMP_ADDRESSES_INT. ADDR_VALID_STATUS_CODE
HZ_IMP_ADDRESSES_INT. DATE_VALIDATED
HZ_IMP_ADDRESSES_INT.ADDRESS1_STD
HZ_IMP_ADDRESSES_INT.ADDRESS2_STD
HZ_IMP_ADDRESSES_INT.ADDRESS3_STD
HZ_IMP_ADDRESSES_INT.ADDRESS4_STD
HZ_IMP_ADDRESSES_INT. CITY_STD
HZ_IMP_ADDRESSES_INT. PROV_STATE_ADMIN_CODE_STD
HZ_IMP_ADDRESSES_INT. COUNTY_STD
HZ_IMP_ADDRESSES_INT. COUNTRY_STD
HZ_IMP_ADDRESSES_INT. POSTAL_CODE_STD
HZ_IMP_ADDRESSES_INT. ACCEPT_STANDARDIZED_FLAG
In batch import mode, apart from the above columns in the HZ_IMP_ADDRESSES_INT table, the statistics of the validation exercise on a particular batch pertaining to a particular country are captured in the following columns:
HZ_IMP_ADAPTERS. RECORDS_PASSED_VALIDATION
HZ_IMP_ADAPTERS. RECORDS_FAILED_VALIDATION
HZ_IMP_ADAPTERS.TOTAL_RECORDS_VALIDATED
If the country records exist in the HZ_IMP_ADAPTERS table then the aforesaid columns are updated, but if these records do not exist then new records are created with all the pertinent information.
XML Message Format
The XML message format that is used in address validation communication between LSI and the vendor adapter is as follows:
<? xml version = “1.0”?>
<LocationService>
<ADAPTERCONTENTSOURCE></ADAPTERCONTENTSOURCE>
<OVERWRITETHRESHOLD></OVERWRITETHRESHOLD>
<REQUESTTYPE></REQUESTTYPE>
<Location>
<Id></Id>
<Address>
<AddressLine index=”1”></AddressLine>
<AddressLine index=”2”></AddressLine>
<AddressLine index=”3”></AddressLine>
<AddressLine index=”4”></AddressLine>
<City></City>
<County></County>
<CountrySubEntity></CountrySubEntity>
<Country></Country>
<PostalCode></PostalCode>
</Address>
<UserArea>
<LocationService.Location.UserArea>
<LocationService.Status></LocationService.Status>
</LocationService.Location.UserArea>
</UserArea>
</Location>
<LocationService>
Invariable Elements in XML Message
Certain elements in the xml message format cannot be modified by vendors and should be left intact. These include:
· <Id>…</Id> – Id maps to Location_id from HZ_Locations table and is unique for every record and must not be modified.
· <ADAPTERCONTENTSOURCE> – The value between these elements maps to the Adapter_id, from HZ_Adapters table and
· <OVERWRITETHRESHOLD> – This displays the user entered value specifying the threshold for accepting the address returned from the vendor adapter.
· <REQUESTTYPE> – The default value that is usually present between these elements is: Validate
· <LocationService.Status> – This is a mandatory part of the XML response message from the vendor adapter to LSI. The value returned within this element is deemed as status code returned by the particular adapter after validating the address record against its own libraries.
Note: The status code is extracted from the xml document and is compared to the Overwrite Threshold (in case of Standalone Mode) and Default Replace Status Code (in case of Batch Import Mode) to determine whether to accept or reject returning address from adapter.
As mentioned in the Adapter Development Guide, a list of acceptable values with a short description of these codes is mentioned in this table below.
| Code | Meaning | Description |
|---|
0
| Valid Address |
The highest level of validation. The address has been certified deliverable based on postal authority certifications.
|
1
| Multiple or Ambiguous Address | The address has been validated to the street level, but there is ambiguity to locate the specific address |
| 2 | Street or Thoroughfare Match | The address has been validated to the street level, but no further. |
| 3 | Municipality Match | The address has been validated to the city or town level, but no further. |
| 4 | Administrative Subdivision Match | The address has been validated to be within the country’s main administration subdivision such as province or state |
| 5 | Country Match | The address has only been validated to the country level, but no further |
| 6 | No Match |
The address could not be validated in any way whatsoever.
|
These status codes are vendor-independent and need to be mapped to TCA’s validation status codes. The mapping between the vendor status codes and TCA’s validation status code is stored outside of TCA registry.
· Non-address elements such as “Attn: Mr…” or “C/O” should be left intact within Addressline elements. After validation, it is expected that the adapter will return these elements intact.
· <REQUESTTYPE> – The value between these elements will always be set to “Validate” in the first release
Variable Elements in XML Message
A listing of some of the variable elements in the xml message and their corresponding mapping with columns in the HZ_Locations table is mentioned in this table below.
| XML Tag | Column(s) in HZ_Locations |
|---|
| <AddressLine> |
Address1…4
|
| <City> | City |
| <County> | County |
| <CountrySubEntity> | State/Province |
| <Country> | Country |
| <PostalCode> | Postal code |
Apart from the elements mentioned in the table above, extra variable elements can be introduced between the tags <LocationService.Location.UserArea> </LocationService.Location.UserArea>. Vendors can introduce additional tags within these tags to render extra information about the particular address.
Format for Multiple Addresses
Multiple location records can be transmitted in a single xml document. In order to accomplish this, the same structure from the <Location></Location> tabs needs to be repeated as necessary.
For example if two addresses were passed to the vendor adapter for processing (say 1100 Howard Ave, San Carlos, CA, 94070 and 100 Van Ness Ave, San Francisco, CA, 94102) then the XML message would resemble the following format:
<? xml version = “1.0”?>
<LocationService>
<ADAPTERCONTENTSOURCE></ADAPTERCONTENTSOURCE>
<OVERWRITETHRESHOLD></OVERWRITETHRESHOLD>
<REQUESTTYPE></REQUESTTYPE>
<Location>
<Id>1401</Id>
<Address>
<AddressLine index=”1”> 1100 Howard Ave </AddressLine>
<AddressLine index=”2”></AddressLine>
<AddressLine index=”3”></AddressLine>
<AddressLine index=”4”></AddressLine>
<City> San Carlos</City>
<County></County>
<CountrySubEntity>CA</CountrySubEntity>
<Country>US</Country>
<PostalCode>94070</PostalCode>
</Address>
<UserArea>
<LocationService.Location.UserArea>
<LocationService.Status></LocationService.Status>
<LocationService.Location.UserArea>
</UserArea>
</Location>
<Location>
<Id>1902</Id>
<Address>
<AddressLine index=”1”> 100 Van Ness Ave </AddressLine>
<AddressLine index=”2”></AddressLine>
<AddressLine index=”3”></AddressLine>
<AddressLine index=”4”></AddressLine>
<City> San Francisco </City>
<County></County>
<CountrySubEntity>CA</CountrySubEntity>
<Country>US</Country>
<PostalCode>94102</PostalCode>
</Address>
<UserArea>
<LocationService.Location.UserArea>
<LocationService.Status></LocationService.Status>
<LocationService.Location.UserArea>
</UserArea>
</Location>
<LocationService>
API Signature
A description of the APIs with their corresponding procedures and parameters is mentioned below. (Source: Detail Level Design Location Services and Technical Design CDM Address Validation for Import)
Procedure Name:Address _validation
Signature
address_validation ( Errbuf OUT NOCOPY VARCHAR2,Retcode OUT NOCOPY VARCHAR2,p_validation_status_op IN VARCHAR2, p_validation_status_code IN VARCHAR2, p_date_validated_op IN VARCHAR2, p_date_validated IN VARCHAR2, p_last_update_date_op IN VARCHAR2, p_last_update_date IN VARCHAR2, p_country IN VARCHAR2, p_adapter_content_source IN VARCHAR2, p_overwrite_threshold IN VARCHAR2)
Comments
This procedure does the following:
1) Accepts parameters from conc. program and retrieves rows from HZ_LOCATIONS that conform to the input parameters
2) Raises wf event to generate xml document based on the rows retrieved. It may split up all rows into different batches due to the maximum batch size defined for each adapter.
Procedure Name :outdoc_rule
Signature
outdoc_rule ( p_subscription_guid IN RAW, p_event IN OUT NOCOPY wf_event_t ) RETURN VARCHAR2;
Comments
As address_validation raises wf event to generate xml, this function rule gets invoked. The name of the wf event that is raised is oracle.apps.ar.hz.locservice.generatexml.
This function rule does the following:
1) Gets the generated xml doc
2) Passes the xml doc to submit_addrval_doc
3) Gets the returned, validated xml doc and raises another wf event to parse the validated addresses
Procedure Name:submit_addrval_doc
Signature
submit_addrval_doc ( p_addrval_doc IN OUT NOCOPY NCLOB, p_adapter_id IN NUMBER DEFAULT NULL, p_country_code IN VARCHAR2 DEFAULT NULL, p_module IN VARCHAR2 DEFAULT NULL, p_module_id IN NUMBER DEFAULT NULL, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2 );
Comments This procedure is invoked from function rule:outdoc_rule. The procedure is used to send an xml doc to a vendor’s adapter. It requires an adapter_id or country_code to call address validation against different vendor adapters. It raises an error if both the required parameters are missing.
This procedure does the following:
1) Sets timeout restriction for a request
2) Begins request
3) Sends XML doc to vendor
4) Receives response from vendor
5) Returns XML doc
Procedure Name:get_validated_xml
Signature
get_validated_xml ( p_adapter_id IN NUMBER, p_overwrite_threshold IN VARCHAR2, p_location_id IN NUMBER, p_country IN VARCHAR2, p_address1 IN VARCHAR2, p_address2 IN VARCHAR2, p_address3 IN VARCHAR2, p_address4 IN VARCHAR2, p_county IN VARCHAR2, p_city IN VARCHAR2, p_prov_state_admin_code IN VARCHAR2, p_postal_code IN VARCHAR2, p_validation_status_code IN VARCHAR2 );
Comments
This procedure is invoked from function rule:outdoc_rule. The procedure gets triggered as the outdoc_rule raises a wf event to parse xml. The name of the wf event that is raised is oracle.apps.ar.hz.locservice.parsexml.
This function rule does the following:
1) Gets the parsed xml doc
2) Checks for tax validation
3) Based on the results of tax validation, decides to create/update the validated address.
HZ_LOCATION_PROFILE_PVT
Procedure Name:create_location_profile
Signature
create_location_profile ( p_location_profile_rec IN location_profile_rec_type, x_location_profile_id OUT NOCOPY NUMBER, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2 )
Comments
This procedure is used to create a location profile record. While creating the record, it checks for mandatory fields (ie.actual_content_source, location_id, address1 and country). After the record is created,it returns the location_profile_id to the caller program
Procedure Name:update_location_profile
Signature
update_location_profile ( p_location_profile_rec IN location_profile_rec_type, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2 )
Comment
This procedure is used to either create or update the location profile record.
HZ_ADAPTER_PUB
Procedure Name:create_adapter
Signature
create_adapter ( p_adapter_rec IN adapter_rec_type, x_adapter_id OUT NOCOPY NUMBER, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2)
Comments
This procedure is used to create an adapter record. While creating the record, it checks for mandatory fields (ie. adapter_content_source, message_format_code, invoke_method_code, maximum_batch_size and default_batch_size). After the record is created,it returns the adapter_id to the caller program. It also creates a lookup code for the adapter.
Procedure Name:create_adapter_terr
Signature
create_adapter_terr ( p_adapter_terr_rec IN adapter_terr_rec_type, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2)
Comment
This procedure is used to create an adapter territory record.
Procedure Name:update_adapter
Signature
update_adapter ( p_adapter_rec IN adapter_rec_type, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2)
Comments
This procedure is used to update an adapter territory record. While updating, it checks for mandatory fields (ie. adapter_content_source, message_format_code, invoke_method_code, maximum_batch_size and default_batch_size).
Procedure Name:update_adapter_terr
Signature
update_adapter_terr ( p_adapter_terr_rec IN adapter_terr_rec_type, x_return_status OUT NOCOPY VARCHAR2, x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2 )
Comments
This procedure is used to update adapter territory record. When updating adapter territory record, it will check if adapter already exists.
HZ_IMP_ADDRESS_VAL_PKG
Procedure Name:address_validation_main
Signature
address_validation_main( Errbuf OUT NOCOPY VARCHAR2, Retcode OUT NOCOPY VARCHAR2, p_batch_id IN NUMBER)
Comment
This procedure is called by UI Console wrapper concurrent program for each batch.
Procedure Name:address_validation_child
Signature
address_validation_child ( Errbuf OUT NOCOPY VARCHAR2 Retcode OUT NOCOPY VARCHAR2, p_batch_id IN NUMBER, p_val_subset_id IN NUMBER, p_country_code IN VARCHAR2, p_module IN VARCHAR2, p_module_id IN NUMBER, p_overwrite_threshold IN VARCHAR2, p_orig_system IN VARCHAR2, p_adapter_id IN NUMBER )
Comments
This procedure is called by the address_validation_main procedure for each batch. This procedure will internally call ‘oracle.apps.ar.hz.import.outboundxml’ event subscription.
Procedure Name:update_validated_address
Signature
update_validated_address (p_site_orig_system_reference IN VARCHAR2,p_site_orig_system IN VARCHAR2,p_batch_id IN NUMBER,p_address1 IN VARCHAR2,p_address2 IN VARCHAR2,p_address3 IN VARCHAR2,p_address4 IN VARCHAR2,p_city IN VARCHAR2,p_county IN VARCHAR2,p_countrysubentity IN VARCHAR2,p_country IN VARCHAR2,p_postal_code IN VARCHAR2,p_status IN VARCHAR2,p_overwrite_threshold IN VARCHAR2)
Comment
This procedure is called by the xml gateway through mapcode, as a procedure call.
Additional Reading
Additional Details for developing Address Validation adapters can be found at:
ORACLE TCA ADAPTER DEVELOPMENT GUIDE (Metalink DOC Id Note: 259842.1)
Glossary
1 CPUI refers to Common Party UI components that were built using OA Framework to display information pertaining to party and party related entities. These components are used across several applications in the E-Business suite.
2 Geocoding is a form of location enrichment that provides geo-spatial information (such as latitude/longitude) for a specific address.
3 Location Enrichment (a.k.a Address Enrichment) is the process of adding supplemental information to an address to further enhance the utility of that address for the deploying company.
4 OAG (Open Applications Group) is an open and fully independent Open Standards Organization focused on promoting interoperability among business applications and creating business language standards.
5Validation Status Code is a standardized code within TCA that depicts the result of address validation on a particular location record. Its value is used to measure the level of accuracy to which the address has been validated .
6Overwrite Threshold is a user entered value specifying the threshold for accepting the address returned from the vendor adapter. Location records with adapter returned validation status code less than the overwrite threshold become eligible for update within TCA Registry.
7Message Designer is a tool that facilitates creation of a mapping between database objects (ie. table or view) to xml or just xml to xml.
