This chapter describes using Third Party Data Integration to purchase and batch load information from D&B, load legacy data into the TCA Registry after the data has been rationalized by D&B, review information from data sources, and run reports for more information about the D&B data you have acquired.
This chapter covers the following topics:
Use Oracle Trading Community Architecture (TCA) Third Party Data Integration to purchase third party data from D&B to enrich the data in the TCA Registry. D&B information is integrated with the party records to maintain accurate information that you can use to evaluate credit risks.
The D&B information coexists with user-entered data in the Registry. In Oracle applications, users can view and use information from different data sources, such as user entered and D&B. Your administrator can control how the D&B and user-entered data are used and displayed in Oracle applications, as well as set up Third Party Data Integration.
Important: The embedded Dun and Bradstreet toolkit is not compatible in an IBM environment.
Note: Even though all purchased D&B data are stored in TCA tables, some information might not appear in user interfaces, based on the Single Source of Truth (SST) setup.
See: Setting Up Third Party Data Integration, Oracle Trading Community Architecture Administration Guide and Single Source of Truth Overview, Oracle Trading Community Architecture Administration Guide.
In the TCA Registry, a party exists separately from any business relationship that it enters into with other parties. A customer account for a party represents a business relationship that parties can enter into. The account has information about the terms and conditions of doing business with the party. The D&B information that you purchase is stored in TCA for parties, not for customer accounts. See: Customers Overview.
D&B provides profile, demographic, credit risk, and financial information about parties to help you make business decisions about the parties that you transact with.
To acquire third party information from D&B, you can use either of these methods:
You can also load legacy data that D&B has rationalized into the TCA Registry .
For specific parties, view and compare the data from different data sources, such as user entered and D&B. See: Viewing Information from Data Sources.
You can also run two reports for more information about the D&B data in the TCA Registry:
Duplicate DUNS Report: To review parties with the same D-U-N-S Number. See: Duplicate DUNS Report.
DNB Global Data Products Request Report: To determine what D&B data products have been requested. See: DNB Global Data Products Request Report.
Related Topics
Source System Management Impact
Details on Creating Parties with D&B Information
Using Oracle Trading Community Architecture
Dun & Bradstreet (D&B) maintains a growing global database of more than 70 million businesses worldwide. This database provides key information such as D&B's unique identifier, the D-U-N-S Number, and executive contacts, as well as demographic, financial, and credit risk data. D&B provides information in the form of data products and Business Information Reports that contain a variety of data elements.
If you have a D&B relationship manager assigned to your company, you should contact that individual for any D&B issues or information, for example, to interpret credit ratings and other information that D&B provides.
If you do not have a relationship manager, contact D&B's Global Service Center at (888) 243-4566, e-mail dnb4oracle@dnb.com, or visit http://www.dnb.com.
Related Topics
Business Information Report (BIR)
Third Party Data Integration Overview
D&B data products meet the needs of businesses that transact business worldwide. Each data product provides different sets of information called data elements to meet your business decision-making criteria. The data products contain information such as:
Business identity
Number of employees
Years in operation
Industry
Corporate structure
Financial history
Proprietary D&B scores and ratings that indicate credit risk
You can use Third Party Data Integration to obtain any of these data products:
Business Verification
Quick Check
Delinquency Score
Global Failure Risk Score
Financial Standing
Decision Support
Enterprise Management
Commercial Credit Score
Vendor Management
The Business Verification Global Data Product (GDP) provides the information necessary to verify a company's existence and validate its location with background information such as primary name, address, phone, SIC codes, branch indicator, and D&B D-U-N-S Number.
The Quick Check GDP provides information that you use to perform low-risk credit assessments with D&B's core credit evaluation information. You can prescreen prospective customer accounts, evaluate a party's creditworthiness, and develop appropriate credit terms. In addition to the information in the Business Verification GDP, Quick Check includes financial event indicators, basic financial data, number of employees, payment activity summary, and the D&B Rating.
The Delinquency Score GDP contains a statistically modeled D&B score that indicates the risk of a company to make delinquent payments, based on payment history information from the D&B file. The higher the Delinquency Score, the lower the probability of payment delinquency. D&B's Delinquency Score lets you rank the customer accounts in your credit portfolio from highest to lowest risk of payment delinquency. You can also quickly divide new and existing accounts into various risk segments to determine appropriate marketing or credit policies. Most of the information included in the Quick Check GDP is also in the Delinquency Score GDP.
The Global Failure Risk Score GDP contains a statistically derived predictive score that helps you assess the risk of business failure when dealing with global companies and managing the customer accounts in your global credit portfolios. The Global Failure Risk Score is a single, uniform measure predicting the risk of business failure over a 12-month period. This data product is ideal for companies that have centralized credit processes and policies or those with decentralized credit processing that plan to globally or regionally standardize decision making. The Global Failure Risk Score GDP contains the information included in the Delinquency Score GDP and other financial data.
The Financial Standing GDP provides key financial information such as sales volume, net worth, assets, and liabilities to help you assess a party's financial condition. You can determine the financial strength of a business to set credit terms and conditions, perform research, or determine the strengths and weaknesses of a prospective customer account, existing customer account, or supplier. Key income statement and balance sheet information are provided, as well as information included in the Global Failure Risk Score GDP.
The Decision Support GDP provides information that you use with a decision support system or manual credit evaluation processes. You can improve the timeliness and consistency of credit decisions by matching your company's credit policies and requirements to D&B credit scores and ratings, as well as the party's financial information. This data product helps you set credit terms and conditions for medium- to high-risk value decisions, prioritize collection efforts, evaluate potential merger and acquisition candidates, and introduce rapid, accurate, and consistent credit decision making. The Decision Support GDP contains the data included in the Financial Standing GDP, as well as additional scores and ratings information.
The Enterprise Management GDP provides detailed demographic, corporate structure, risk, and financial information. You can use this information to improve risk assessment procedures, make better informed credit decisions, and improve the acquisition and retention of customer accounts. Enterprise Management includes the most complete set of data, scores, and ratings available about the party.
You can view the corporate structure that this GDP provides in a hierarchical display. The hierarchical relationships within the structure make up the D&B hierarchy, which you can access using Relationship Manager. See: D&B Hierarchy.
The Commercial Credit Score data product includes the Commercial Credit Score and other data elements that you can use to predict the likelihood that a company would pay invoices in a severely delinquent manner within the next 12 months. D&B defines severe delinquency as the receipt of payment more than 90 days past the due date. This data product also includes information about bankruptcies, delinquent payments, and payment history.
Note: This data product is only available to D&B customers in the US for businesses located in the US.
The Vendor Management data product provides detailed business information to help you evaluate a supplier and ensure the stability and efficiency of your supply chain. Information includes headquarters location, parent company, risk indicators, payment information and risk scores.
Related Topics
The Business Information Report (BIR) provides many of the data elements from the D&B database in a standard report format. You can order a BIR, store the report in your database, and view the report whenever necessary.
The BIR usually includes:
D&B rating
General information such as number of employees, business history, and so on
Financial statements
Payment performance information, such as the D&B PAYDEX score
Related Topics
The complete D&B database includes over 150 key business data elements. Each data product consists of a fixed set of data elements. These data elements provide information that you can use to identify, contact, and evaluate the credit risk of parties.
A few of the available data elements are:
D-U-N-S Number: Unique business identification number assigned by D&B to each commercial entity in the D&B database. If an organization has multiple locations, each location has a unique D-U-N-S Number.
Note: If DNB is not the highest ranking source or a user overwrite rule exists for the D-U-N-S number, the D-U-N-S number from the Single Source of Truth (SST) record as well as the hz_parties record might be different from D-U-N-S number from DNB.
D&B Rating: Rating that indicates a company's creditworthiness. The rating usually consists of a financial strength code and a risk indicator.
Note: For a key to the D&B Ratings, contact your local D&B representative or D&B's Global Service Center at (888) 243-4566, or visit Customer Service at http://www.dnb.com.
Local Business ID: The primary business identification number assigned to the business by a government agency, chamber of commerce, or association.
Parent Bankruptcy Chapter Conversion: Indicator of whether the parent of the business has converted its bankruptcy filing from one chapter to another; for example, from Chapter 11 to Chapter 7. This data element is only available as part of the Commercial Credit Score data product.
Related Topics
General Data Elements, Oracle Trading Community Architecture Reference Guide
Financial Data Elements, Oracle Trading Community Architecture Reference Guide
Source System Management (SSM) maps records in the TCA Registry with the source system that the data comes from. See: Source Systems Overview, Oracle Trading Community Architecture Administration Guide.
For D&B, the D-U-N-S Number is used as the source ID, which identifies the record in the source system. Each record from D&B with a unique D-U-N-S Number can map to only one party record in the TCA Registry. Likewise, if multiple parties in the Registry has the same D-U-N-S Number, only one of those records can be mapped to D&B. The party that is actively mapped to D&B has what is called the mapped D-U-N-S Number.
Important: When you repurchase D&B data through batch load, and several parties have the same D-U-N-S Number, only the party with the mapped D-U-N-S Number is updated.
For both online purchase and batch load, when you acquire D&B data with a D-U-N-S Number that does not yet exist in TCA, the party is automatically mapped to D&B. For example, you get D&B data for Vision Corporation for the first time, including the D-U-N-S Number for Vision. If that D-U-N-S Number is not already mapped, then Vision Corporation is created as a new party, with the mapped D-U-N-S Number.
If you repurchase D&B information for Vision Corporation, and D&B provides a different D-U-N-S Number, then Vision gets a new, mapped D-U-N-S Number.
You can also later acquire D&B information with the same D-U-N-S Number for another party, but only one of the two parties can have the mapped D-U-N-S Number. The mapping would automatically switch to the party that you are working with. For example:
You are acquiring D&B information for Vision Corporation for the first time, and you get D-U-N-S Number A for Vision. Another party, however, already has that D-U-N-S Number and is mapped to D&B. The existing mapping would be inactivated, and Vision would get the mapped D-U-N-S Number.
You are acquiring D&B information to update Vision, which currently has D-U-N-S Number A. D&B provides D-U-N-S Number B to update Vision, but another party already has D-U-N-S Number B and is mapped to D&B. Again, the mapping for D-U-N-S Number B would go to Vision.
D-U-N-S Number A is no longer a mapped D-U-N-S Number, until you purchase D&B data with D-U-N-S Number A for a party.
Tip: Multiple parties with the same D-U-N-S Number are allowed to support different company situations. You should use the shared D-U-N-S Number to identify and merge duplicates, since only the record with the mapped D-U-N-S Number is updated through batch load.
Related Topics
Third Party Data Integration Overview
When you purchase D&B data online or batch load D&B information, certain D&B data elements are mapped to the appropriate columns in the TCA Registry. Most of the columns have the same names as the D&B data elements, but other columns have more general names that differ from those used by D&B.
Online purchasing: The Mapping API utility maps and loads the D&B data elements from the HZ_PARTY_INTERFACE table into the TCA Registry. See: Mapping API Utility.
Batch loading: The D&B Import Adapter request set maps and loads the D&B data elements to the appropriate import interface table columns. The mapped data is then ready for import into the Registry. See: D&B Import Adapter.
If the party is the headquarters, the HQ D-U-N-S Number is the same as the D-U-N-S Number for the party. This case also applies to related parties. If the D-U-N-S Number of the related party is the same as the party D-U-N-S Number, two possible scenarios arise:
If the party is a Domestic Ultimate, Global Ultimate, or Headquarters, a relationship is created between the party and its related party. Both of the companies in the relationship will be the same, to convey that the party itself is of type Domestic Ultimate, Global Ultimate, or Headquarters.
If the related party does not have a parent, D&B does not provide parent information.
Outside of North America, D&B does not store information at the branch level. If you request information for a company that is a branch location outside of North America, D&B provides data for its headquarters. The D-U-N-S Number for the branch's headquarters is used to select the D&B data for the headquarters. The D-U-N-S Number for the branch is displayed as the Enquiry D-U-N-S Number. If a headquarters record does not exist in the TCA Registry, Third Party Data Integration automatically creates one.
Related Topics
Third Party Data Integration Overview
When you purchase online or batch load D&B information for a party that does not yet exist in the TCA Registry, you create a new party and load its D&B data. For this party, both a D&B and a user-entered record are created in the following table:
However, user entered records are not created in the following tables:
Related Topics
Third Party Data Integration Overview
Use the online purchasing functionality of Third Party Data Integration to search for and purchase Dun & Bradstreet (D&B) information on specific companies from D&B's database. You can update the information obtained from D&B during online sessions or by using the batch loading process.
You can purchase and retrieve D&B information for organizations that exist or do not exist in the TCA Registry. When you obtain D&B information about a company that does not exist in the TCA Registry, a new party is created using the D&B data. See: Details on Creating Parties with D&B Information.
Related Topics
Third Party Data Integration Overview
This diagram describes the process flow for purchasing D&B information online. You can correct errors that might occur in this process. See: Correcting Mapping API Utility Errors.
If the party exists, you view the information it currently has from data sources, if any. You can search the D&B database for that company if you want to initially purchase or update D&B information for the selected party.
If the party does not exist, you can search the D&B database to create a new party with purchased D&B information.
If the company exists in the D&B database:
You can continue and select the data product or Business Information Report (BIR) to purchase.
And if the D&B information for the selected company is already purchased for at least one existing party, you can either:
Continue purchase by selecting a data product or BIR for your selected or new party.
Cancel the purchase.
If the company does not exist in the D&B database, you can request a D&B investigation for a party.
After the purchase process completes, view the updated or purchased D&B information for your party. If you purchased D&B information for a party that did not exist in the TCA Registry, a new party is created with that D&B information.
You search for a party in the TCA Registry to:
Find the party that you want to purchase D&B information for.
Confirm that a party does not already exist before purchasing D&B information to create a new party.
View its existing third party and user-entered information.
Tip: With the advanced search criteria, you can search for only parties with active mapped D-U-N-S Numbers. This criterion is available only if D-U-N-S Numbers exist in the Registry. See: Source System Management Impact.
If your search criteria do not match any existing party in the TCA Registry, or the specific party that you are searching for is not displayed, you can create a new party with the purchased D&B data. See: Searching for Companies in the D&B Database.
If your search criteria match the party you are searching for, click the party name to view its third party and user-entered data, if any, and to optionally continue with purchasing D&B data. See: Viewing Information from Data Sources.
In other Oracle applications, accordingly search for the party or customer. For example, in Customers Online, you search for an organization as usual and, from the overview and other pages for that organization, you can proceed to view existing information from data sources and purchase D&B data.
Related Topics
Search the D&B database to determine if D&B's database contains information about your party. You can search for either a party that you want to create using D&B information or for an existing party that you want to purchase or update D&B information for.
Note: If you search by unique identifiers, you do not need to enter required values for searching by company name and location, but you must enter at least one identifier.
If you are purchasing data for an existing party that has no D&B information, the identifying address is used as the default address search criterion. If the party already has D&B information, then the address that D&B provided is the default.
Note: The most commonly used and effective search criterion is the D-U-N-S Number. Second is the local business ID, which can be assigned by a governmental agency or a nongovernmental organization.
From the search results, you can:
If you are trying to purchase D&B information, and D&B data for the selected company is already purchased for at least one other existing party, you can either:
Continue to purchase the D&B data for the party that you are creating or updating. See: Selecting Data Products or BIRs.
Note: Possible duplicate parties might exist in the TCA Registry if the same D&B information applies to more than one party. You should consider merging parties that are actually duplicates.
Cancel the purchase.
Related Topics
After you find the party in the D&B database that you want to purchase information for, you select one of the available data products or Business Information Reports (BIR) to purchase. See: Data Products and Business Information Report (BIR).
Tip: For a new or existing party that has no existing D&B data, you might initially want to gather basic information about a company in the form of a BIR.
The following table shows which data product provides information to the subtabs where you can view D&B information. See: Viewing Information from Data Sources.
Subtab | Data Product |
---|---|
Summary | All |
Business Verification | All |
Special Event |
|
Relationships | Enterprise Management |
BIR | Business Information Report (BIR) |
Credit and Risk |
|
Financial Analysis |
|
After the D&B data is purchased, you can review the D&B information. See: Viewing Information from Data Sources.
Related Topics
Request an investigation for a company that you cannot find in the D&B database. Because of the time required to conduct an investigation, you cannot use D&B data to create a new party at the same time that you request an investigation from D&B.
Information about the party defaults into this page to identify the party that you want to investigate. If the party does not exist in the TCA Registry, the criteria entered for the D&B database search provide the default identifying information. See: Searching for Companies in the D&B Database.
Email To: The address that D&B uses to send an acknowledgement of your request and to notify you when the investigation has been completed. A separate e-mail provides you with a BIR containing the information that D&B gathered about your party.
The e-mail address defaults with the one that your system administrator entered in your user definition, if any.
See: Users Window, Oracle Applications System Administrator's Guide - Security.
Service: The level of priority for your investigation request.
In the US, a Regular investigation is usually carried out by D&B within five business days. A Priority investigation is completed the next business day but incurs a premium price from D&B.
Additional Information: More details to identify your party or other instructions to D&B.
Related Topics
When you purchase D&B information online, the information is first loaded into the HZ_PARTY_INTERFACE table. The Mapping API utility takes the data from the interface table, and maps and loads them into the TCA Registry. See: Mapping of D&B Data Elements.
The utility processes party data in two phases:
Data for the party is processed.
The utility retrieves records one by one for the parties from the interface table and then processes them. Online requests for information cause the processing of only one record.
New records in the interface table are identified with a STATUS data element value of N. When the utility successfully processes a record, the utility changes the STATUS value for the record to P1. If D&B does not have any information about a business, the utility changes the party's D-U-N-S Number to 000000000 and the STATUS value to B for blank.
If the utility cannot successfully process the record, it changes the STATUS value to E1, to indicate that an error occurred. In some cases, the utility can automatically correct invalid values from D&B, and it changes the STATUS value for the corrected record to W1. See: Automatic Correction of Invalid D&B Values.
Information for related parties associated with the party is processed. Related parties, such as the Parent, Headquarters, Domestic Ultimate, and Global Ultimate locate the party within an organization's hierarchy.
When you request D&B data for more than one party, the utility must process all of the party records in the first step before processing data for the organizations associated with each party. Only if a party record was successfully processed in the first step can the utility process data for the organizations associated with that party.
When the utility successfully processes the organization data associated with a party, it changes the STATUS value for the record to P2. If the utility cannot successfully process the data, it changes the STATUS value to E2 to indicate that an error occurred. In some cases, the utility can automatically correct invalid values from D&B, and it changes the STATUS value for the corrected record to W2. See: Automatic Correction of Invalid D&B Values.
The received data from online purchase is loaded into the HZ_PARTY_INTERFACE table as one record. The record is automatically assigned a unique ID in the GROUP_ID column. If there are no errors, the data automatically transfers into the appropriate TCA tables for you to view and use.
In some cases, you need to manually transfer the data. If errors arise with the online purchase, the data remains in the interface table. For example, D&B might provide values in a format that is not valid for TCA number and date columns. Most E1 and E2 errors occur due to changes in information codes that D&B provides for the data elements. All errors are listed in the HZ_PARTY_INTERFACE_ERRORS table.
You should be able to access the interface table and correct any errors. After making the necessary corrections, change the STATUS codes from E1 to N or from E2 to P1 so that the corrected data will be appropriately processed next time.
After resolving the issues, use the Load D&B Data program to import the data from the interface table into the TCA tables. In the Group ID parameter of the program, you can specify the group ID of the record that you want to import, for example the one that originally incurred an error. See: Load D&B Data.
The application can automatically correct invalid values from D&B for specific attributes. Corrected values are loaded into the TCA Registry along with all other valid values.
For specific attributes that need a Y or N value, if D&B provides something other than Y, N, or null, then the invalid value is automatically changed to N. Information about the correction is stored in the HZ_PARTY_INTERFACE_ERRORS table, except in the cases of exceptions listed below.
The affected attributes and any exceptions are:
ANNUAL_SALES_CONSOL_IND
AUDIT_IND
BANKRUPTCY_IND
Exception: If D&B provides null, the corrected value is N. Likewise, B is changed to Y.
BRANCH_FLAG
CLAIMS_IND
CONSOLIDATED_IND
CRIMINAL_PROCEEDING_IND
DISADV_8A_IND
DISASTER_IND
ESTIMATED_IND
EXPORT_IND
FINAL_IND
FINCL_EMBT_IND
FINCL_LGL_EVETN_IND
FISCAL_IND
FORECAST_IND
IMPORT_IND
JUDGEMENT_IND
Exception: If D&B provides null, the corrected value is N. Likewise, J is changed to Y.
LABOR_SURPLUS_IND
LIEN_IND
MINORITY_OWNED_IND
NO_TRADE_IND
Exception: If D&B provides null, the corrected value is Y.
OOB_IND
Exception: If D&B provides OB, the corrected value is Y.
OPENING_IND
OPRG_SPEC_EVNT_IND
OTHER_SPEC_EVNT_IND
PARENT_SUB_IND
PROFORMA_IND
Exception: If D&B provides null, the corrected value is N.
PRNT_HQ_BKCY_IND
QUALIFIED_IND
RESTATED_IND
SECURED_FLNG_IND
SIGNED_BY_PRINCIPALS_IND
SMALL_BUS_IND
SUIT_IND
Exception: If D&B provides null, the corrected value is N. Likewise, J is changed to Y.
TRIAL_BALANCE_IND
UNBALANCED_IND
WOMAN_OWNED_IND
For specific attributes, invalid D&B values are changed to null or a correct value. Information about the correction is stored in the HZ_PARTY_INTERFACE_ERRORS table.
This table shows the attributes that are validated against tables and the corrected values.
Attribute | Table Name | Corrected Value |
---|---|---|
ANNUAL_SALES_CURRENCY | FND_CURRENCIES | Null |
CAPITAL_CURRENCY_CODE | FND_CURRENCIES | Null |
FINANCIAL_NUMBER_CURRENCY | FND_CURRENCIES | Null |
MAX_CREDIT_CURRENCY | FND_CURRENCIES | Null |
PHONE_COUNTRY_CODE | HZ_PHONE_COUNTRY_CODES | Null |
PREF_FUNCTIONAL_CURRENCY | FND_CURRENCIES | Null |
TANGIBLE_NET_WORTH_CURR | FND_CURRENCIES | Null |
This table shows the attributes that are validated against lookup types and the corrected values.
Attribute | Lookup Type | Corrected Value |
---|---|---|
HQ_BRANCH_IND | HQ_BRANCH_IND | Null If D&B provides any of these values, they are corrected as follows:
|
LOCAL_BUS_IDEN_TYPE | LOCAL_BUS_IDEN_TYPE | Null |
RENT_OWN_IND | OWN_RENT_IND | Null |
For specific attributes that are validated against lookups, invalid D&B values are accepted and automatically added as a valid lookup code to the corresponding lookup type. Information about this correction is not stored in the HZ_PARTY_INTERFACE_ERRORS table.
This table shows the attributes and the corresponding lookup types.
Attribute | Lookup Type |
---|---|
CREDIT_SCORE_COMMENTARY1 to CREDIT_SCORE_COMMENTARY10 | CREDIT_SCORE_COMMENTARY |
CREDIT_SCORE_OVERRIDE_CODE | FAILURE_SCORE_OVERRIDE_CODE |
EMP_AT_PRIMARY_ADR_EST_IND | EMP_AT_PRIMARY_ADR_EST_IND |
EMP_AT_PRIMARY_ADR_MIN_IND | EMP_AT_PRIMARY_ADR_MIN_IND |
FAILURE_SCORE_COMMENTARY1 to FAILURE_SCORE_COMMENTARY10 | FAILURE_SCORE_COMMENTARY |
FAILURE_SCORE_OVERRIDE_CODE | FAILURE_SCORE_OVERRIDE_CODE |
LEGAL_STATUS | LEGAL_STATUS |
LOCAL_ACTIVITY_CODE | LOCAL_ACTIVITY_CODE_TYPE provided by D&B |
REGISTRATION_TYPE | REGISTRATION_TYPE |
SIC_CODE1 to SIC_CODE6 | SIC_CODE_TYPE provided by D&B |
TOTAL_EMP_EST_IND | TOTAL_EMP_EST_IND |
TOTAL_EMP_MIN_IND | TOTAL_EMP_MIN_IND |
TOTAL _EMPLOYEES_IND | TOTAL_EMPLOYEES_INDICATOR |
The LOCAL_ACTIVITY_CODE_TYPE attribute is validated against the LOCAL_ACTIVITY_CODE_TYPE lookup type. If D&B provides an invalid value, then the LOCAL_ACTIVY_CODE_TYPE and LOCAL_ACTIVITY_CODE attributes are both corrected to null.
If D&B provides null for the LOCAL_ACTIVITY_CODE_TYPE attribute, and the LOCAL_ACTIVITY_CODE attribute is not null, then the LOCAL_ACTIVITY_CODE attribute is corrected to null.
The SIC_CODE_TYPE attribute is validated against the SIC_CODE_TYPE lookup type. If D&B provides an invalid value, then the SIC_CODE_TYPE and SIC_CODE1 to SIC_CODE6 attributes are all corrected to null.
If D&B provides a value for any of the SIC_CODE attributes and null for the SIC_CODE_TYPE attribute, then the SIC_CODE_TYPE attribute is corrected to 1985 SIC. Both SIC_CODE_TYPE and SIC_CODE attributes must have a value or none at all. If D&B provides null for all SIC_CODE attributes, then the SIC_CODE_TYPE attribute is corrected to null.
Related Topics
Use the Load D&B Data program to load corrected, online-purchased D&B information from the HZ_PARTY_INTERFACE table into the appropriate TCA tables. See: Correcting Mapping API Utility Errors. The Mapping API utility ensures that the information in the D&B data elements populate the appropriate fields and TCA tables.
Before you can run the Load D&B Data program, the HZ_PARTY_INTERFACE table must be populated with data from online purchasing.
Use the following parameter to submit the Load D&B Data program.
By default, every record from online purchase is automatically assigned a group ID. Enter the group ID of the record that you want to reload after it was corrected. If every record in the interface table has a group ID, the program processes nothing if you leave the parameter blank.
Related Topics
Use the batch load process to update D&B information for a large number of parties on a regular schedule or whenever you need. You can update all parties, gather data only for new parties, or update parties that have not been updated since a specified date. Batch retrieval and loading of D&B information is usually a regularly scheduled, automated process that you set up and run.
When you obtain D&B information about a company that does not exist in the TCA Registry, a new party is created using the D&B data. See: Details on Creating Parties with D&B Information.
If you are batch loading to update parties that already have D&B data, only the records with a mapped D-U-N-S Number are updated. See: Source System Management Impact.
Related Topics
Third Party Data Integration Overview
The batch loading process you use depends on the database version you are on.
Receive a properly formatted file, ready for loading, from D&B.
Run the D&B Import Adapter request set to load D&B information into the interface tables.
Note: If you use a method other than the D&B Import Adapter, then you must pass a valid D&B value in the CONTENT_SOURCE_TYPE column to make the information from D&B available for the Single Source of Truth record. See: Single Source of Truth Overview, Oracle Trading Community Architecture Administration Guide.
Optionally verify that the D&B information is loaded into the interface tables as expected.
Review the settings for import profile options and modify if needed. See: Bulk Import Deployment Category, Oracle Trading Community Architecture Administration Guide.
Run the Import Batch to TCA Registry program to load D&B information from the interface tables into the appropriate HZ tables. For the Import Run Options parameter, select to run both the preimport and import processes.
Correct errors, if any. Most errors occur due to changes in information codes that D&B provides for the data elements.
Receive a properly formatted file, ready for loading, from D&B.
Run the SQL*Loader utility to load the provided D&B file into the HZ_PARTY_INTERFACE table. The SQL*Loader command is:
Sqlldr userid=<User Name>/<Password>@<Database Name> control=ARHDNBGX.ctl data=<Data File Name>
The data file name is the name of the flat file from D&B. You must pass a valid D&B value in the CONTENT_SOURCE_TYPE column to make the information from D&B available for the Single Source of Truth record.
Each newly loaded record in the interface table has a null GROUP_ID column. If you want to import only a subset of records from the interface table into the TCA tables, you can manually populate that column with the same value for all records in the subset. You can then specify that group ID to transfer the subset of records from the interface table into TCA tables. See: Load D&B Data.
Leave the Group ID parameter blank to process all records without a group ID in the interface table. By default, the records that you batch load into the HZ_PARTY_INTERFACE table from the D&B file have no group ID.
If you do assign a group ID to any of the records, you can:
Leave the parameter blank to process all records except those assigned a group ID.
Enter a group ID to process a subset of records with the same ID.
Use the Generate Request List For DNB Batch Load program to select and generate a text file of the parties that you require updated D&B information for. The request list includes the following data elements:
D-U-N-S Number
Party name
Address1
Address2
Address3
Address4
City
State
Country
Postal code
Party ID
Phone number
After the Generate Request List For DNB Batch Load program successfully completes, you can find the request list in the usual directory for concurrent request output files.
The Party ID for each party is included in the output file that D&B returns to you after you submit your request list and is used to match D&B information to the appropriate party in the TCA Registry.
Use the following parameters to submit the Generate Request List For DNB Batch Load program.
Select the type of request list that you want to generate:
All parties
Only parties not having D&B data
Only parties whose D&B data was last updated prior to the date in the last update date parameter
Important: If you select options 1 or 2, of the parties that already have D&B data, only the ones with the mapped D-U-N-S Number are included in the request list.
Enter the last update date that you want to base the request on. You need to specify this date only if you are using request list option 3.
Related Topics
You must make specific arrangements with D&B for the transfer of the file that identifies the parties that you are requesting data for. Through a manual process, you usually sends the file to D&B on a cartridge or a diskette. The file can also be sent to D&B using File Transfer Protocol (FTP) or as an e-mail attachment.
Related Topics
Run the D&B Import Adapter request set to load data, from the ASCII flat file that D&B provides for batch loading, into the interface tables used for TCA Bulk Import. The flat file contains D&B information for the parties that you requested.
A flat file is loaded as a batch into the interface tables. At any given time, only one D&B batch can be actively loading into the interface tables, but the interface tables can store multiple D&B batches.
Note: The D&B Import Adapter request set supports only standard D&B bulk files for the Enterprise Management data product.
Manually create a directory object on the same environment as your TCA database. A directory object is a database object that stores the absolute path of a physical directory on the database node. Name this object HZ_DNB_SOURCE_DIR, and make sure the database server can read and write from the location identified by the directory object.
For example, create the directory object in APPS as follows:
CREATE or replace DIRECTORY HZ_DNB_SOURCE_DIR AS '/emslog/dnb'
If the object is not in APPS, you must also grant access to APPS:
GRANT READ ON DIRECTORY HZ_DNB_SOURCE_DIR TO apps; GRANT WRITE ON DIRECTORY HZ_DNB_SOURCE_DIR TO apps;
Note: You only need to create this directory object once, to be used for all D&B Import Adapter requests.
Copy or FTP the flat file from D&B to the location that the directory object identifies.
Sort the flat file based on D-U-N-S Number to make sure that all D-U-N-S Numbers in the flat file are unique. For example, in UNIX:
sort -u -t, +0 -1 <file with duplicate DUNS Number> -o <output file name>
The D&B Import Adapter request set includes two programs that you must specify parameters for.
Object Containing File Location: Enter the name of the database object you created. You can override the HZ_DNB_SOURCE_DIR default if you used another name.
Data File Name: Enter the name of the flat file that D&B provided.
Log File Name: Enter the case-sensitive name of the log file that is to be created for the upload.
Bad File Name: Enter the case-sensitive name of the file that is to be created for the upload to store all bad records.
Discard File Name: Enter the case-sensitive name of the file that is to be created for the upload to store all discarded records.
Batch Identifier: Enter the batch ID that the Create Import Batch API generated. See Batch IDs.
Explicit NULL character value (G_MISS_CHAR): Enter the character value in the interface tables that, when imported into the TCA tables, would make the corresponding TCA character column null. If an interface table column is null, the corresponding TCA column, empty or not, is not affected by the import.
Related Topics
Loading Data into the Interface Tables
When you migrate from a legacy system to Oracle Applications, you might need to transfer legacy data into the TCA tables. If you want to cleanse your legacy data before loading the data into the TCA tables, you can use D&B rationalization services. You can then use the Import Batch to TCA Registry program to create new parties in the TCA tables for the rationalized legacy data and use the Customer Interface program to create customer accounts for those parties.
Important: Do not upload cleansed data from other third-party content providers when using this functionality.
Compile a list of the parties in your legacy system that you want D&B to rationalize. Each party in the list must include a unique identifier. This unique identifier maps to the Original System Reference field in the TCA tables.
Transfer your list to D&B. For more information, see: Transferring a Request List File to D&B.
D&B compares the companies in your list with the companies in the D&B database and rationalizes the data on your list. After rationalizing your data, D&B sends three flat ASCII files back to you:
First file: Matched, unmatched, and duplicate records as well as information appended from D&B's database for each company
Second file: All the duplicate records, listed in order of D-U-N-S number
Third file: Records that D&B could not find a D-U-N-S number for
Run the Import Batch to TCA Registry program to load D&B information from the interface tables into the appropriate TCA tables. The program creates parties in the TCA tables and maps the unique identifier that you identified in step 1 to the Original System Reference field.
Run the Customer Interface program to associate legacy customer accounts with your newly created parties. The Customer Interface program matches customer accounts to parties using the Original System Reference field. See: Customer Interface.
Related Topics
Third Party Data Integration Overview
Third Party Data Integration lets you view information from all relevant data sources for any given party. Before you acquire any third party data about a party, you can only view the user-entered party information. After you purchase and retrieve D&B information for a party, for example, you can also view the D&B information.
User-entered information is entered or loaded by users of Oracle Applications. The displayed D&B information depends on which data product you purchase for that party, either ordered online or batch loaded. See: Selecting Data Products or BIRs.
Search for the party that you want to view. See: Searching for Existing Parties in the TCA Registry.
For details about the information you can view for the party, see: Subtabs of Information.
Optionally choose to purchase new or updated D&B information for that party. See: Searching for Companies in the D&B Database.
Related Topics
Third Party Data Integration Overview
The Summary and Special Event subtabs display the user-entered and D&B information for the party. The Business Verification subtab can show the Single Source of Truth (SST) information, depending on the SST administration. See: Single Source of Truth Overview, Oracle Trading Community Architecture Administration Guide.
If the SST record is set up, you see the D&B information in the D&B column and the SST information in the Single Source of Truth column. If SST is not set up, you see the user-entered information in the Customer Record column.
The subtabs provide the following:
Summary: The Summary subtab provides general information about the party such as D-U-N-S Number, address, and the last update date.
Business Verification: The Business Verification subtab provides:
Demographic information such as CEO name, fiscal year-end month, potential revenue, and functional currency.
Business keys such as legal status, business structure, SIC code, and local business identification.
Employee statistics such as the total number of employees and the number of employees at the location that you purchased D&B data for.
Special Event: The Special Event subtab provides information about legal, financial, and operational events or activities, including disasters, that might affect a company's creditworthiness.
These subtabs only display information provided by D&B. The data product purchased for the party determines which data elements in these subtabs contain values.
Relationships: The Relationships subtab displays business and legal structure information, including branch to headquarters and parent to subsidiary relationships, for up to five related entities or organizations: Primary Organization, Headquarters, Parent, Domestic Ultimate, and Global Ultimate. For each relationship, the displayed information can consist of the related party's name, D-U-N-S Number, address, and telephone number.
BIR: The BIR (Business Information Report) subtab displays an image of the BIR for the party, providing detailed business and financial information.
Credit / Risk: The Credit / Risk subtab displays payment and risk information. Some of the data elements included are Paydex data, D&B Rating, delinquency scores, and failure scores.
Note: For a key to the D&B Ratings, contact your local D&B representative or D&B's Global Service Center at (888)243-4566, or visit Customer Service at http://www.dnb.com.
Financial Analysis: The Financial Analysis subtab displays available income statement and balance sheet data as well as financial ratios. This subtab also indicates whether the financial statements are consolidated, audited, restated, and so on.
Related Topics
Viewing Information from Data Sources
Use the Duplicate DUNS report to identify parties with the same D-U-N-S Number in the TCA Registry. You can run the report to find duplicates for a particular D-U-N-S number or duplicates for any D-U-N-S Number.
For each party with the same D-U-N-S Number, the report displays:
Party name
Party number
Address
City
State
Country
Use the following parameters to run the Duplicate DUNS report.
Enter the D-U-N-S Number that you want to identify duplicates for.
If you enter a D-U-N-S Number, the report only displays a result if two or more parties use that same D-U-N-S Number. If you leave the parameter blank, the report displays any duplicated D-U-N-S Numbers and the parties associated with those D-U-N-S Numbers.
Related Topics
Third Party Data Integration Overview
Use the DNB Global Data Products Request report to find out when D&B products were requested, who requested each product, and which GDP was purchased. The report provides information that you can use to control the purchase of and access to D&B information.
The report contains the following details about each request:
Party name
Party number
Requested product
Note: If Batch Load appears in this column, the last update to this party's D&B information was completed using the batch load process.
Purchaser
Purchased date
Use the following parameters to run the DNB Global Data Products Request report.
Enter the date that you want to report from.
Enter the date that you want to report to.
Related Topics