oracle receivables-importing dun & bradstreet(d&b) data using bulk import webcast
DESCRIPTION
Oracle Receivables-Importing Dun & Bradstreet(D&B) Data Using Bulk Import WebcastTRANSCRIPT
1© 2013 Oracle Corporation – Proprietary and Confidential
Avoid the unexpectedDon’t leave value on the tableLower overall organizational costs through preventative
Are You Ready To Get Proactive?
Lower overall organizational costs through preventative maintenanceReduce risks and maximize uptime Achieve resolution fasterStreamline and simplify your daily operationsGet even more through connectionDiscover more about Get Proactive
https://support.oracle.com/CSP/main/a? & &rticle?cmd=show&type=ATT&id=138516
5.1:DISCOVER
ACT Get ProactiveAccess proactive capabilities available
for your products by visiting the product pages at My Oracle Support; Article ID 432.1
Contact the Get Proactive team today for help getting started
2© 2013 Oracle Corporation – Proprietary and Confidential
FYI: New Portal With Same DocIDTh U i W b t S h d l d A hi dThe Upcoming Webcast Schedule and Archived Webcasts documents have been consolidated into one document.
Doc ID 740966.1
Doc ID 740964.1
Advisor WebcastsAdvisor Webcasts Current Schedule
and Archived Recordings
3© 2013 Oracle Corporation – Proprietary and Confidential
Doc ID 740966.1
Upcoming Community Webcasts
Day, Date, 2004time p.m. ET
20-March-13, 11 AM ET
VOICE STREAMING
Upcoming Community Webcasts:10-APR-13, Boost Your Efficiency With Oracle EBS Get
Teleconference Access:North America: xxxxInternational: xxxx
VOICE STREAMINGENABLED
North America:
, yProactive! (Doc ID 1532094.1)
11-APR-13, Overview and Usage of Oracle Receivables Information Centers (Doc ID 1519628.1)International: xxxx
Password: Advisor877-664-9143
International:706 634 8961
( )
For complete details on all upcoming Oracle E-Business S it Ad i W b t E t d t706-634-8961
Conference ID: 89615487
Suite Advisor Webcast Events and to access recordings, please review Document ID 740966.1.
Do you have any topic requests for future Advisor Webcast E ents? Please email o r s ggestions to
Doc ID: 1519631.1
Webcast Events? Please email your suggestions to [email protected], subject: Topics of Interest.
4© 2013 Oracle Corporation – Proprietary and Confidential
Safe Harbor Statement
The following is intended to outline our general product direction. It is intended for information purposes only, and may not be incorporated into any contract It is not a commitment to deliver anyany contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decision. The p g p gdevelopment, release, and timing of any features or functionality described for Oracle’s products
i t th l di ti f O lremains at the sole discretion of Oracle.
5© 2013 Oracle Corporation – Proprietary and Confidential
Importing Dun & BradstreetImporting Dun & Bradstreet (D&B) Data Using Bulk ImportImport
Sandy ShookyPrincipal Technical Support EngineerMelody JohnsonPrincipal Technical Support EngineerJackie LiPrincipal Technical Support Engineer
© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
7© 2013 Oracle Corporation – Proprietary and Confidential
What Is Dun & Bradstreet (D&B)?
• Global database of over 95 million business records.
• Standard process used to populate data in Oracle.
• A contract is required with D&B in order to purchase their data.q p
8© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
9© 2013 Oracle Corporation – Proprietary and Confidential
Manually Create A Directory
• A Directory Object is a database object, which stores the absolute h f h i l di h d b dpath of a physical directory on the database node.
• Give this object the name HZ_DNB_SOURCE_DIR.
• Make sure the database server can read and write from the location identified by the directory object.y y j
• For more information on Directory Objects please refer to Oracle Server Technology DocumentationServer Technology Documentation.
10© 2013 Oracle Corporation – Proprietary and Confidential
Manually Create A Directory
• Example to Create Directory: CREATE or replace DIRECTORY HZ_DNB_SOURCE_DIR AS ‘/tmp';
• Must Grant Access to APPS GRANT READ ON DIRECTORY HZ_DNB_SOURCE_DIR TO apps;GRANT WRITE ON DIRECTORY HZ_DNB_SOURCE_DIR TO apps;
11© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
12© 2013 Oracle Corporation – Proprietary and Confidential
Copy/Move The D&B Flat File
Flat File Name Example: dnbdemo.csvDirectory Object Example: /tmp
Local BoxDB Box in Root \tmp directoryDB Box in Root \tmp directory
Copy or Move
13© 2013 Oracle Corporation – Proprietary and Confidential
Copy/Move The D&B Flat File Example of Flat File Data - Opened in Excel
Example of Flat File Data - Opened in Text Format
14© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
15© 2013 Oracle Corporation – Proprietary and Confidential
Create Batch ID• A batch can be created manually using a SQL script.
• See Doc ID 383072.1 for a sample script.• Example of Manual Script:Example of Manual Script:
16© 2013 Oracle Corporation – Proprietary and Confidential
Create Batch IDMove this script to HZ_DNB_SOURCE_DIR
New Directory
Move or Copy
17© 2013 Oracle Corporation – Proprietary and Confidential
Create Batch ID• Enter the following values when prompted (examples provided):
batchname = testfilebatchname testfilebatchdescription = testorigsys = DNBloadtype = DNB
• Ensure the origsys D&B is defined and active in your Source System management.
• Source System defined via Oracle Customers Online • Superuser Administration > Source Systems• Map and Manage cross reference source system names and record
id tifiidentifiers.• For example you can retain their legacy customer number
references.
18© 2013 Oracle Corporation – Proprietary and Confidential
Create Batch_IDThe output should generate the Batch_ID, for example:
SQL> @create batch idQ @ _ _25/Enter value for batchname: testfileEnter value for batchdescription: testEnter value for origsys: DNBEnter value for loadtype: DNBypold 10: hz_imp_batch_summary_v2pub.create_import_batch(NULL,’&batchname’,’&batchdescription’,’&ORIGSYS’, ‘&loadtype’, 12, x_batch_id, x_return_status, x_msg_count, x_msg_data);New 10: hz_imp_batch_summary_v2pub.create_import_batch(NULL, ’testfile’, ’test’, ‘DNB’, ‘DNB’, 12, x_batch_id, x_return_status, x_msg_count, x_msg_data);x_batch_id = 22000x_return_status = Sx_msg_count = 0x_msg_data =
PL/SQL procedure successfully completedPL/SQL procedure successfully completed.
19© 2013 Oracle Corporation – Proprietary and Confidential
Create Batch ID• In Release12, a batch can be created via Oracle Customers
Online Data Librarian• Responsibility: Customers Online Data Librarian• Navigation: Import Tab > Generate Batch
Do not activateDo not activateDo not activate the batch at this point
Do not activate the batch at this point
20© 2013 Oracle Corporation – Proprietary and Confidential
Create Batch IDNavigation: Enter Parameters > Apply
21© 2013 Oracle Corporation – Proprietary and Confidential
Create Batch ID
Batch_ID created:
22© 2013 Oracle Corporation – Proprietary and Confidential
Create Batch ID
• SQL to verify the newly created batch_id:
SQL> select batch_id, batch_status from hz_imp_batch_summary;
BATCH_ID BATCH_STATUS---------- ------------------------------13000 COMPLETED13001 ACTION_REQUIRED22000 <-----Newly Created BATCH_ID
23© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
24© 2013 Oracle Corporation – Proprietary and Confidential
Import Flat FileResponsibility: Trading Community ManagerNavigation: Control > Request > Run
a. Choose the Request Set option and run the Request Set
25© 2013 Oracle Corporation – Proprietary and Confidential
Import Flat Fileb. Run D&B Import Adapter using the following parameters:
i. Parameter: D&B Flat File Upload Table Creation
N f th D&B fil t bName of the D&B file to be imported – case-sensitive. Default
OK
26© 2013 Oracle Corporation – Proprietary and Confidential
Import Flat File
ii. Parameter: D&B File Load – Pass 1
Select from the List of Values (LOV)
27© 2013 Oracle Corporation – Proprietary and Confidential
Import Flat File
• The following concurrent processes will kick off at the submit:
• HZ_DNB_ADAPTER2 module: Request Set D&B Import Adapter ARHLDDNB4 module: D&B File Load Preprocessing ARHLDDNB3 module: D&B Flat File Upload Table Creation ARHLDDNB1 module: D&B File Load - Pass 1 ARHLDDNB2 module: D&B File Load - Pass 2 ARHLDDNB5 module: D&B File Load Postprocessing
• Verify all complete successfully• Verify all complete successfully.
• For more information on errors related to these concurrent programs, see Doc ID 1449553.1, Trading Community Architecture (TCA): Listingsee Doc ID 1449553.1, Trading Community Architecture (TCA): Listing of ORA Errors.
28© 2013 Oracle Corporation – Proprietary and Confidential
Import Flat File
• Stage 10 – Stage: D&B File Load Preprocessing
D&B Import Adapter – Concurrent set stages:
Stage 10 Stage: D&B File Load Preprocessing (Preprocessing for D&B Import Adapter)
• Stage 20 Stage: D&B Flat file Upload Table Creation SQL Code• Stage 20 – Stage: D&B Flat file Upload Table Creation - SQL Code ARHLDDNB3(arhldnb3.sql: To create the external table for D&B upload purposes CREATE TABLE
HZ_DNB_XTBL )(C t E t l T bl f D&B B t h Fl t fil U l d)(Creates External Table for D&B Batch Flat file Upload)
• Stage 30 – Stage: D&B File Load - Pass 1 – SQL code ARHLDDNB1 (arhldnb1.sql: To upload the D&B Batch Flat file. This will upload all the entities except the
related duns info.)(Loads D&B Batch flat file into HZ interface tables - first pass)
29© 2013 Oracle Corporation – Proprietary and Confidential
Import Flat File
D&B Import Adapter – Concurrent set stages (continued):
• Stage 40 - Stage: D&B File Load - Pass 2 – SQL Code ARHLDDNB2 (arhldnb2.sql: To upload the D&B Batch Flat file. This will upload the related duns
information into parties, address and CP.)(L d D&B B t h fl t fil i t HZ i t f t bl d )(Load D&B Batch flat file into HZ interface tables - second pass)
• State 50 - Stage: D&B File Load Postprocessing (D&B Batch File Postprocessing for Records Loaded Through D&B Import Adapter)( p g g p p )
30© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
31© 2013 Oracle Corporation – Proprietary and Confidential
Run Bulk LoaderRun the Import Batch to TCA Registry request
Responsibility: Trading Community ManagerNavigation: Control > Request > Run > Choose the Single Request
This should match the Batch IDThis should match the Batch ID created.22000
32© 2013 Oracle Corporation – Proprietary and Confidential
Run Bulk LoaderTh t ki k ff th f ll i• The concurrent process kicks off the following processes:
ARHIMAIN module: Import Batch to TCA Registry ARHLWRPB TCA Import Batch Data LoadARHLWRPB TCA Import - Batch Data Load ARHLSG1W Module TCA Import - Data Load Stage 1 ARHLSG2W Module TCA Import - Data Load Stage 2 ARHLSG3W Module TCA Import - Data Load Stage 3 p gARHLPPLB TCA Import Postprocessing DQM Sync Index 1 (DQM Sync Index) DQM Sync Index 2 (DQM Sync Index) DQM Sync Index 4 (DQM Sync Index) DQM Sync Index 3 (DQM Sync Index)
• Verify all complete successfully• Verify all complete successfully.• For more information on errors related to these concurrent programs,
see Doc ID 1449553.1, Trading Community Architecture (TCA): Listing of ORA Errors.
33© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
34© 2013 Oracle Corporation – Proprietary and Confidential
Import TablesThe following query can be run to view the data in the HZ table:
@SQL> Select party number,party id||','||party type||','||party name||', @ Q p y_ ,p y_ || , ||p y_ yp || , ||p y_ || ,||orig_system_reference||','||status
from hz_parties where orig_system_reference in
(select party_orig_system_reference from hz imp parties int where batch id= 22000);_ p_p _ _ );
Results:PARTY_NUMBER -----------------------PARTY_ID||','||PARTY_TYPE||','||PARTY_NAME||','||ORIG_SYSTEM_REFERENCE||','||STATUS
61780
648152, ORGANIZATION, MARGI Import 05A,11111,A
35© 2013 Oracle Corporation – Proprietary and Confidential
Import TablesHZ_IMP_PARTIES_INT HZ_IMP_ADDRESSES_INT HZ IMP ADDRESSUSES INTHZ_IMP_ADDRESSUSES_INT HZ_IMP_CONTACTS_INT HZ_IMP_CONTACTPTS_INT HZ_IMP_CONTACTROLES_INT HZ_IMP_RELSHIPS_INT HZ IMP CLASSIFICS INTHZ_IMP_CLASSIFICS_INT HZ_IMP_CREDITRTNGS_INT HZ_IMP_FINNUMBERS_INT HZ IMP FINREPORTS INTHZ_IMP_FINREPORTS_INT HZ_IMP_BATCH_SUMMARY HZ_IMP_ERRORS
36© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
37© 2013 Oracle Corporation – Proprietary and Confidential
Profile Options Specific To Bulk ImportProfile Option DescriptionHZ: Allow Import of Records with Disabled Lookups
Controls whether or not records with values referencing disabled lookup codes p g pcan be loaded with TCA import. Values = Yes/No
HZ: Validate Flexfields During Controls whether Flexfields are validated Import during TCA import or not. Values = Yes/No
HZ: Use HR Security During Import
Controls whether HR security is enabled during TCA import or not. Values = Yes/No
HZ: Use Data Sharing and Security During Import
Controls whether Data Sharing and Security is enabled during TCA import or not. Values = Yes/No
HZ: Work Unit Size for Import Sets the work unit size used during TCA import. Values = Yes/No
HZ: Number of Workers for Import Sets the number of workers used during TCA import Values Yes/No
38© 2013 Oracle Corporation – Proprietary and Confidential
TCA import. Values = Yes/No
Profile Options Specific To Bulk ImportProfile Option DescriptionHZ: Error Limit for Import Sets the number of error records allowed before
TCA import should stop processing.HZ: Character Value to Indicate Null During Import
Sets the value which indicates that a TCA character column should be made null during TCA import.
HZ N i V l t S t th l hi h i di t th t TCAHZ: Numeric Value to Indicate Null During Import
Sets the value which indicates that a TCA numeric column should be made null during TCA import.
HZ: Date Value to Indicate Sets the value which indicates that a TCA dateHZ: Date Value to Indicate Null During Import
Sets the value which indicates that a TCA date column should be made null during TCA import.
HZ: Allow updates of Address Records during
If Yes, imported addresses will update the existing addresses. g
Import g
Matching addresses, if address source id in interface table matches an existing SSM address mapping, interface address compared to existing address
39© 2013 Oracle Corporation – Proprietary and Confidential
address.
Profile Options Specific To Bulk ImportIMC Profile Options
IMC: Automate CSV File Load into TCA IMC: Import Preview Processing Results
IMC: CSV Batch De-Duplication Match Rule
IMC: Import Run Address Validation
IMC: CSV Batch De-Duplication Action IMC: Import Run Batch De-Duplicationp p p
IMC: CSV Batch Registry Match Rule IMC: Import Run Registry Match
IMC: Display Notes for Merge Request Mapping
IMC: Method for Defining ‘Recently Created’Mapping Created
IMC: Enable Manage Reports IMC: ‘Recently Created’ Value Definition
IMC: Run CSV Batch De-Duplication IMC: Run CSV Batch Address Validation
IMC: Run CSV Batch Registry Match IMC: Visualization Solution Type
40© 2013 Oracle Corporation – Proprietary and Confidential
Program Agenda
• What is Dun & Bradstreet (D&B)?( )
• Manually Create a Directory Object
• Copy/Move the D&B Flat File
• Create Batch ID
• Import Flat Files
R B lk L d• Run Bulk Loader
• Import Tables
• Profile OptionsProfile Options
• Troubleshooting
41© 2013 Oracle Corporation – Proprietary and Confidential
Troubleshooting
Responsibility: Oracle Customers Online SuperUserNavigation: Import -> Batch Import History
I t St t C l ill h E f f il d b t h• Import Status Column will show Error for any failed batch• Select the erred Batch ID to show the import batch details
• Click on the View Errors button to see a listing of errorsS l t th D t il i t t ifi i f ti ti l• Select the Details icon to get specific information on a particular error
42© 2013 Oracle Corporation – Proprietary and Confidential
Troubleshooting
• The following two tables will help you diagnose errors you might get when importing data into TCA
• HZ IMP ERRORS used by TCA import process to record all• HZ_IMP_ERRORS – used by TCA import process to record all errors that occur during import.
• HZ_IMP_TMP_ERRORS – used by TCA import process to record all errors that occur during import.g p
• These errors are written into HZ_IMP_ERRORS table at the end of the processing
select message_name, interface_table_name, count(*) from "AR"."HZ_IMP_ERRORS“ where batch_id = &BATCH_ID group by message_name, interface_table_name;
43© 2013 Oracle Corporation – Proprietary and Confidential
Demonstration
44© 2013 Oracle Corporation – Proprietary and Confidential
For More Information
• Doc ID 283760.1 – Example About How To Run Bulk Import For Parties And, Address Information
• Doc ID 383072 1 How to Import D&B flat file provided by D&B• Doc ID 383072.1 – How to Import D&B flat file provided by D&B into the Bulk Loader Interface tables
• Doc ID 279924.1 – How To Import Parties Using Bulk Import Process
• Doc ID 1451645.1 – How to import party using TCA Bulk Import Registry Interface Tables
• Continue the discussion! For follow up questions on topicsContinue the discussion! For follow up questions on topics covered in this webcast, connect via this Receivables thread: https://communities.oracle.com/portal/server.pt/community/view_discussion_topic/216?threadid=587394
• The recorded version of this webcast will be available within the next 48 hours and can be downloaded from Doc ID 740966.1, Advisor Webcasts: Current Schedule and Archived Recordings
45© 2013 Oracle Corporation – Proprietary and Confidential
Advisor Webcasts: Current Schedule and Archived Recordings.
FYI: New Portal With Same DocIDTh U i W b t S h d l d A hi dThe Upcoming Webcast Schedule and Archived Webcasts documents have been consolidated into one document.
Doc ID 740966.1
Doc ID 740964.1
Advisor WebcastsAdvisor Webcasts Current Schedule
and Archived Recordings
46© 2013 Oracle Corporation – Proprietary and Confidential
Doc ID 740966.1
Advisor Webcasts Schedule & Archives
Select your product:product:
e.g. E-Business Suite
Financials
47© 2013 Oracle Corporation – Proprietary and Confidential
Advisor Webcasts Schedule & Archives
Archives
Schedule
48© 2013 Oracle Corporation – Proprietary and Confidential
Visit My Oracle CommunitiesCollaborate with a large network of your industry peers, support professionals, and Oracle experts to exchange information, ask questions & get answers. Find out how your peers are using Oracle technologies and services to better meet their support and business needs.
• Exchange Knowledge
• Resolve Issues
• Gain Expertise
Visit the My Oracle Support Community now!!1 Log into My Oracle Support1. Log into My Oracle Support.
2. Select the Community tab.
3. Select the Enter Here button.
4 Select the Community Name4. Select the Community Name link under the E-Business Suite section of the My Communities Menu on the left side of the window
49
side of the window.
© 2013 Oracle Corporation – Proprietary and Confidential
Oracle Premier SupportGet Proactive
My Oracle Support Doc Id
432 1
50
432.1
© 2013 Oracle Corporation – Proprietary and Confidential
• To ask a question on the phone line select *1 on yourphone line, select 1 on your phone.
• To ask a question online, use the Chat icon.
• Your question will be d l d i th dread aloud in the order
received.• Questions can also be askedQuestions can also be asked
on the My Oracle Support Communities
51© 2013 Oracle Corporation – Proprietary and Confidential
THANK YOU
52© 2013 Oracle Corporation – Proprietary and Confidential
53
54