AIM

CV.040 CONVERSION DATA

MAPPING
CSC-Competency Conversion
Project
AR Customer Conversion

Author: Rajesh Pulagora

Creation Date: June 26, 2009
Last Updated: July 1, 2009
Document Ref: <Document Reference Number>
Version: DRAFT 1A

Approvals:

<Approver 1> Krishna Kodamasimham

<Approver 2>
CV.040 Conversion Data Mapping

Document Control

Change Record
1

Date Author Version Change Reference

26-Jun-09 Rajesh Pulagora Draft 1a No Previous Document

Reviewers

Name Position

Distribution

Copy No. Name Location

1
Library Master Project Library
2 Project Manager
3
4

Note to Holders:

If you receive an electronic copy of this document and print it out, please write your
name on the equivalent of the cover page, for document control purposes.

If you receive a hard copy of this document, please write your name on the front cover,
for document control purposes.

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
CV.040 Conversion Data Mapping

Contents

Document Control...................................................................................................................ii

Introduction..............................................................................................................................1
Purpose...............................................................................................................................1
Background........................................................................................................................1
Scope and Application.....................................................................................................1
Audience............................................................................................................................1
Application Business Object Reference Information..........................................................3

Conversion Mapping - CUSTOMERS..................................................................................4

Mapping for PARTIES.....................................................................................................5
Mapping for CUSTOMER ACCOUNT.........................................................................5
Mapping for CUSTOMER PROFILES...........................................................................7
Mapping for CUSTOMER ACCOUNT SITES.............................................................8
Mapping for CUSTOMER SITE USES...........................................................................8
Extract File Layout.................................................................................................................10

Data Clean-up.........................................................................................................................11

Data Normalization...............................................................................................................12

Open and Closed Issues for this Deliverable....................................................................13

Open Issues......................................................................................................................13
Closed Issues...................................................................................................................13

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
Introduction

Purpose

The Conversion Data Mapping document describes the:

 Detailed data mapping from the source legacy system to the R12 Oracle
Application for AR Customer Conversion
 File layout to be used for extraction of the data from the source system

Background

The information in this document has been defined as the result of discussions between
project staff, CSC technical staff, and <Consulting Services Provider> consultants.

Scope and Application

The Conversion Data Mapping identifies for use in designing conversion programs to
convert CUSTOMER:

*0 Data sources
*1 Target tables and columns
*2 Validation
*3 Processing
*4 Translation
*5 Filter
*6 Foreign key rules

Audience

This document is intended for the following individuals:

 CSC conversion project staff

 Outside consultants
 Reviewers of data conversion deliverables

 Conversion Process Diagram

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
Conversion Process Diagram:

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
Application Business Object Reference Information

In each of the following sections, a table maps the business objects for R12 Oracle Application to the Oracle
Application tables. The foreign key relationships between the Oracle Application tables are also indicated.
Column (2) suggests whether the business object is a candidate for programmatic conversion. In column
(4), the standard Oracle interface is documented if one exists for facilitating the conversion of a specific
business object.

Business Object Man/ Owned Open Interface Production Table Foreign Key Table
Prog By Name(s) Name(s)

Setup Business Object

M none
Calendar Types M
Set of Books M

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
Conversion Mapping - CUSTOMERS

Below is a table mapping the legacy data elements to the Oracle tables and columns. The following processing ID
codes are used in the mapping spreadsheet:

CUSTPR = Processing Rule

CUSTTR = Translation Rule

CUSTFR = Filter Rule

CUSTFKR = Foreign Key Rule

CUSTDR = Derivation Rule

CUSTDV = Default Value Rule

 CUSTPR

o NONE

 CUSTTR

o NONE

 CUSTFR

o CUSTTR01 : Consider Only Active Customers

 CUSTFKR

o CUSTFR01: Valid Customer details available If Customer as a Supplier

o CUSTFR02: Ship to location and Bill to Location

o CUSTFR03: Payment Term

o CUSTFR04: GL Set of Books

o CUSTFR05 : GL Code Combinations

 CUSTDR

o CUSTDR01: Cutomer Lookup Code.

o CUSTDR02: Customer Profile Lookup Code

o CUSTDR03 : Transaction Lookup Code

o CUSTDR04 : Collections Lookup Code

o CUSTDR05 : Receipts Lookup Code

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
 o CUSTDR6 : Credit Management Lookup Code

o CUSTDR7 : Demand Class Lookup

CUSTDV

o NONE

The data extracted from Oracle Applications 11i represents the comprehensive dataset for conversion to Oracle R12.

Mapping for PARTIES

(1) (2) (3) (4) (5 (6) (7) (8) (9) (10)

)

Ref.# Target Target Target Column Not Source Source System Source System Default Valida
Application Table Application Datatype Null System Field Name Field Datatype Value tion
Table Column ? File Name

1 HZ_PARTIES PARTY_NAME VARCHAR2(360) HZ_PARTIES PARTY_NAME VARCHAR2(360) Y P

n
sh
n
N
2 ADDRESS1 VARCHAR2(240) ADDRESS1 VARCHAR2(240)
3 CITY VARCHAR2(60) CITY VARCHAR2(60)
4 POSTAL_CODE VARCHAR2(60) POSTAL_CODE VARCHAR2(60)
5 STATE VARCHAR2(60) STATE VARCHAR2(60)
6 PROVINCE VARCHAR2(60) PROVINCE VARCHAR2(60)
7 COUNTRY VARCHAR2(60) COUNTRY VARCHAR2(60)
8 COUNTY VARCHAR2(60) COUNTY VARCHAR2(60) Y C
n
sh
b
d

Mapping for CUSTOMER ACCOUNT

(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)

Re Target Target Application Table Target Column Not Source Source System Field Source System Default Vali Rule #
f.# Application Table Column Datatype Null? System Name Field Datatype Value dati
File on
Name

1 HZ_CUST_ACCO ACCOUNT_NAME VARCHAR2(240) HZ_CUST_ACCT_SITE VARCHAR2(

UNTS S_ALL ACCOUNT_NA 240)
ME
2 STATUS VARCHAR2(1) STATUS VARCHAR2( Y Validates
1) the
Acoount
status is
defined
or not
3 CUSTOMER_TYPE VARCHAR2(30) VARCHAR2(
CUSTOMER_TYP 30)
E

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)

Re Target Target Application Table Target Column Not Source Source System Field Source System Default Vali Rule #
f.# Application Table Column Datatype Null? System Name Field Datatype Value dati
File on
Name

4 VARCHAR2(50) VARCHAR2(
CUSTOMER_CLASS_COD CUSTOMER_CL 50)
E ASS_CODE
5 VARCHAR2(100) VARCHAR2(
SALES_CHANNEL_CODE SALES_CHANNE 100)
L_CODE
6 FOB_POINT VARCHAR2(30) FOB_POINT VARCHAR2(
30)
7 TAX_CODE VARCHAR2(50) TAX_CODE VARCHAR2(
50)
8 FREIGHT_TERM VARCHAR2(30) FREIGHT_TERM VARCHAR2(
30)
9 SHIP_VIA VARCHAR2(30) SHIP_VIA VARCHAR2(
30)
10 COTERMINATE_DAY_M VARCHAR2(6) COTERMINATE_ VARCHAR2(
ONTH DAY_MONTH 6)
11 HOLD_BILL_FLAG VARCHAR2(1) HOLD_BILL_FLA VARCHAR2(
G 1)
12 DEPOSIT_REFUND_MET VARCHAR2(20) DEPOSIT_REFUN VARCHAR2(
HOD D_METHOD 20)
13 SOURCE_CODE VARCHAR2(150) SOURCE_CODE VARCHAR2(
150)
14 OVER_SHIPMENT_TOLE NUMBER OVER_SHIPMEN NUMBER
RANCE T_TOLERANCE
15 UNDER_SHIPMENT_TOL NUMBER UNDER_SHIPME NUMBER
ERANCE NT_TOLERANCE
16 OVER_RETURN_TOLERA NUMBER OVER_RETURN_ NUMBER
NCE TOLERANCE
17 UNDER_RETURN_TOLER NUMBER UNDER_RETUR NUMBER
ANCE N_TOLERANCE
18 ITEM_CROSS_REF_PREF VARCHAR2(30) ITEM_CROSS_RE VARCHAR2(
F_PREF 30)
19 SHIP_SETS_INCLUDE_LI VARCHAR2(1) SHIP_SETS_INCL VARCHAR2(
NES_FLAG UDE_LINES_FLA 1)
G
20 ARRIVALSETS_INCLUDE VARCHAR2(1) ARRIVALSETS_I VARCHAR2(
_LINES_FLAG NCLUDE_LINES 1)
_FLAG
21 SCHED_DATE_PUSH_FL VARCHAR2(1) SCHED_DATE_P VARCHAR2(
AG USH_FLAG 1)
22 INVOICE_QUANTITY_R VARCHAR2(30) INVOICE_QUAN VARCHAR2(
ULE TITY_RULE 30)
23 AUTOPAY_FLAG VARCHAR2(1) AUTOPAY_FLA VARCHAR2(
G 1)

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
 (2) (3) (4) (5) (6) (7) (8) (9) (10) (11

Re Target Application Table Target Application Target Column Not Source Source System Field Source System Default Vali Rul
f.# Table Column Datatype Null? System Name Field Datatype Value dati
File on
Name

1 HZ_CUSTOMER_PROFIL CREDIT_CHECKIN VARCHAR2(1) HZ_CUSTOMER_PROF CREDIT_CHECKI VARCHAR

ES G ILES NG 2(1)
2 TOLERANCE NUMBER TOLERANCE NUMBER
3 VARCHAR2(1) VARCHAR
DISCOUNT_TERM DISCOUNT_TER 2(1)
S MS
4 VARCHAR2(1 VARCHAR
DUNNING_LETTE DUNNING_LETT 2(1
RS ERS

5 VARCHAR2(1) VARCHAR
INTEREST_CHARG INTEREST_CHA 2(1)
ES RGES
6 VARCHAR2(1) VARCHAR
SEND_STATEMEN SEND_STATEME 2(1)
TS NTS
7 CREDIT_BALANCE VARCHAR2(1) CREDIT_BALAN VARCHAR
_STATEMENTS CE_STATEMENT 2(1)
S
8 CREDIT_HOLD VARCHAR2(1) CREDIT_HOLD VARCHAR
2(1)
9 CREDIT_RATING VARCHAR2(30) Y Cred
CREDIT_RATING VARCHAR ratin
2(30) shou
be
defi
d
10 RISK_CODE VARCHAR2(30) RISK_CODE
VARCHAR
2(30)
11 STANDARD_TERM NUMBER(15) STANDARD_TER
S MS NUMBER(1
5)
12 OVERRIDE_TERMS VARCHAR2(1) OVERRIDE_TER VARCHAR
MS 2(1)
13 ACCOUNT_STATU VARCHAR2(30) ACCOUNT_STA VARCHAR
S TUS 2(30)
14 PERCENT_COLLEC NUMBER PERCENT_COLL NUMBER
TABLE ECTABLE
15 AUTO_REC_INCL_ VARCHAR2(1) AUTO_REC_INC VARCHAR
DISPUTED_FLAG L_DISPUTED_FL 2(1)
AG
16 TAX_PRINTING_O VARCHAR2(30) TAX_PRINTING_ VARCHAR
PTION OPTION 2(30)
17 CHARGE_ON_FIN VARCHAR2(1) CHARGE_ON_FI VARCHAR
ANCE_CHARGE_F NANCE_CHARG 2(1)
LAG E_FLAG
18 CLEARING_DAYS NUMBER(5) CLEARING_DAY NUMBER(5
S )
19 CONS_INV_FLAG VARCHAR2(1) CONS_INV_FLA VARCHAR
G 2(1)
20 CONS_INV_TYPE VARCHAR2(1) CONS_INV_TYP VARCHAR
E 2(1)
21 REVIEW_CYCLE VARCHAR2(30) REVIEW_CYCLE VARCHAR
2(30)
22 CREDIT_CLASSIFI VARCHAR2(30) CREDIT_CLASSI VARCHAR
CATION FICATION 2(30)
23 STATEMENT_CYC NUMBER(15) N STATEMENT_CY NUMBER(1
LE_ID CLE_ID 5)

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
 (2) (3) (4) (5) (6) (7) (8) (9) (10) (11

Re Target Application Table Target Application Target Column Not Source Source System Field Source System Default Vali Rul
f.# Table Column Datatype Null? System Name Field Datatype Value dati
File on
Name

24 COLLECTOR_ID NUMBER(15) N COLLECTOR_ID NUMBER(1

5)

Mapping for CUSTOMER PROFILES

(2) (3) (4) (5) (6) (7) (8) (9) (10) (11

Ref Target Target Application Target Column Not Source Source System Field Source System Default Validation Ru
.# Applicati Table Column Datatype Null System Name Field Datatype Value #
on Table ? File Name

1 HZ_CUST BILL_TO_FLAG VARCHAR2(1) HZ_CUST_ACCT_SITE BILL_TO_FLAG VARCHA

_ACCT_SI S_ALL R2(1)
TES_ALL
2 MARKET_FLAG VARCHAR2(1) MARKET_FLAG VARCHA
R2(1)
3 SHIP_TO_FLAG VARCHAR2(1) SHIP_TO_FLAG VARCHA
R2(1)
4 CUSTOMER_CATE VARCHAR2(30) CUSTOMER_CA VARCHA
GORY_CODE TEGORY_CODE R2(30)
5 KEY_ACCOUNT_F VARCHAR2(1) KEY_ACCOUNT VARCHA
LAG _FLAG R2(1)
6 ECE_TP_LOCATIO VARCHAR2(40) ECE_TP_LOCATI VARCHA
N_CODE ON_CODE R2(40)
7 TRANSLATED_CU VARCHAR2(50) TRANSLATED_C VARCHA
STOMER_NAME USTOMER_NAM R2(50)
E
8 LANGUAGE VARCHAR2(10) LANGUAGE VARCHA
R2(10)

Mapping for CUSTOMER ACCOUNT SITES

Mapping for CUSTOMER SITE USES

(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)

Ref. Target Application Target Application Target Column Not Source Source System Field Source System Def Valida Rule #
# Table Table Column Datatype Null? System Name Field Datatype ault tion
File Val
Name ue

1 HZ_CUST_SITE_USES_ SITE_USE_CODE VARCHAR2(30) HZ_CUST_SITE_USES_ SITE_USE_CODE Y Site use

ALL ALL VAR code be
CHA defined
2(30)
2 PRIMARY_FLAG VARCHAR2(1) PRIMARY_FLAG
VAR
CHA

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)

Ref. Target Application Target Application Target Column Not Source Source System Field Source System Def Valida Rule #
# Table Table Column Datatype Null? System Name Field Datatype ault tion
File Val
Name ue

R2(1)
3 STATUS VARCHAR2(1) STATUS Y Validates
VAR the
CHA customer
R2(1) status
active or
inactive
4 LOCATION VARCHAR2(40) LOCATION
VAR
CHA
R2(40
)
5 SIC_CODE VARCHAR2(30) SIC_CODE
VAR
CHA
R2(30
)
6 GSA_INDICATOR VARCHAR2(30) GSA_INDICATO
R VAR
CHA
R2(30
)
7 TAX_REFERENCE VARCHAR2(50) TAX_REFERENC VAR
E CHA
R2(50
)
8 DEMAND_CLASS_ VARCHAR2(30) DEMAND_CLAS VAR
CODE S_CODE CHA
R2(30
)
9 PRICING_EVENT VARCHAR2(30) PRICING_EVENT VAR
CHA
R2(30
)
10 GL_ID_REC NUMBER(15) GL_ID_REC NUM
BER(
15)
11 GL_ID_REV NUMBER(15) GL_ID_REV NUM
BER(
15)
12 GL_ID_TAX NUMBER(15) GL_ID_TAX NUM
BER(
15)
13 GL_ID_FREIGHT NUMBER(15) GL_ID_FREIGHT NUM
BER(
15)
14 GL_ID_CLEARING NUMBER(15) GL_ID_CLEARIN NUM
G BER(
15)
15 GL_ID_UNBILLED NUMBER(15) GL_ID_UNBILLE NUM
D BER(
15)
16 GL_ID_UNPAID_R NUMBER(15) GL_ID_UNPAID_ NUM
EC REC BER(
15)
17 GL_ID_REMITTAN NUMBER(15) GL_ID_REMITTA NUM
CE NCE BER(
15)
18 GL_ID_FACTOR NUMBER(15) GL_ID_FACTOR NUM
BER(

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
(1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11)

Ref. Target Application Target Application Target Column Not Source Source System Field Source System Def Valida Rule #
# Table Table Column Datatype Null? System Name Field Datatype ault tion
File Val
Name ue

15)
19 TAX_CLASSIFICAT VARCHAR2(30) TAX_CLASSIFIC VAR
ION ATION CHA
R2(30
)

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
Extract File Layout
Below is a table documenting the extract file schema. This table is only needed when the extract file is position-based.

Note: Repeat this component for each extract file. See the chapter on the Conversion process in the AIM Process and Task
Reference for more information.

Datafile Field Name Datatype Position: Position: Oracle Table Oracle Column Comments
From To

NOT APPLICABLE. EXTRACT FILE USES FIELD DELIMITER

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
Data Clean-up
In this section please describe high-level data clean-up requirements. Specify WHAT is
required, ot necessarily specific information on HOW the cleanup is to be done.

 Will the data clean-up done programatically or manually?

o Any data cleanup of Customers will be done manually in the source

instances.

 What data requires clean-up?

o Customer (Name), Sites (Site Code, Address), Contacts is the primary

fields that require cleanup.

 Automated data clean up requirements?

o N/A

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
Data Normalization
Below is a description of the data normalization that will occur for the conversion of
CUSTOMERS:

*0
*1

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc
Open and Closed Issues for this Deliverable

Open Issues

ID Issue Resolution Responsibility Target Date Impact Date

Closed Issues

ID Issue Resolution Responsibility Target Date Impact Date

Conversion Mapping - CUSTOMERS 7 of 15

456539601.doc