cv040 vendors m
DESCRIPTION
CV040 Vendors mTRANSCRIPT
AIM
CV.040 CONVERSION DATA MAPPING
Privi Organics Pvt LtdCV040_Vendors
Author: Mithun Yaligar
Creation Date: 31-dec-2010
Last Updated: 06-JAN-2011
Document Ref: 1.0
Version: DRAFT 1B
Approvals:
<Approver 1> Amit Jain
<Approver 2> Manik Vengurlekar
CV.040 Conversion Data Mapping
Document Control
Change Record
1
Date Author Version
Change Reference
31-Dec-10
Balaji Shinde Draft 1a
No Previous Document
Reviewers
Name Position
Manik Vengurlekar Delivery Manager
Amit Jain Project Manager
Distribution
Copy No.
Name Location
1 Library Master Project Library2 Project Manager34
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.
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Contents
Document Control..........................................................................................ii
Introduction....................................................................................................4
Purpose....................................................................................................4Background..............................................................................................4Scope and Application.............................................................................4Audience..................................................................................................4
Application Business Object Reference Information....................................5
Conversion Mapping – Vendor......................................................................6
Extract File Layout.......................................................................................18
Data Clean-up..............................................................................................21
Data Normalization......................................................................................22
Objects.........................................................................................................23
Data Conversion Process.............................................................................24
Open and Closed Issues for this Deliverable..............................................25
Open Issues............................................................................................25Closed Issues.........................................................................................25
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Introduction
Purpose
The Conversion Data Mapping document describes the:
Detailed data mapping from the source Oracle Application 11i to the Oracle Application R12 for Vendors.
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, Midas Pvt Ltd technical staff.
Scope and Application
The Conversion Data Mapping identifies for use in designing conversion programs to convert Vendors:
Data sources
Target tables and columns
Validation
Audience
This document is intended for the following individuals:
Midas Pvt Ltd conversion project staff
Reviewers of data conversion deliverables
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Application Business Object Reference Information
In each of the following sections, a table maps the business objects for Vendors 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/Prog
OwnedBy
Open Interface Production Table Name(s)
Foreign Key Table Name(s)
Setup Business ObjectVendors P AP AP_SUPPLIERS
AP_SUPPLIER_SITES_ALLAP_SUPPLIER_CONTACTS
AP_SUPPLIER_SITES_ALLAP_SUPPLIER_CONTACTS
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Conversion Mapping – Vendor
Below is a table mapping the legacy data elements to the Oracle tables and columns.
Target Application: Oracle Application 12.1.1
Business Object: Vendor
Prepared by: Mithun Yaligar
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
1 AP_SUPPLIERSVENDOR_ID Number Y
Vendor id
2 AP_SUPPLIERSLAST_UPDATE_DATE
Date Y
Who tables
3 AP_SUPPLIERSLAST_UPDATED_BY
Number Y
Who tables
4 AP_SUPPLIERSVENDOR_NAME varchar
2Y MIDAS_XX_SUP
PLIERLOAD.csvVendor Name varchar
2
5
AP_SUPPLIERSVENDOR_NAME_ALT
Varchar2
MIDAS_XX_SUPPLIERLOAD.csv
This is alternate name by witch supplers is identified
6
AP_SUPPLIERSSEGMENT1 Varchar
2Y MIDAS_XX_SUP
PLIERLOAD.csvSupplier number entry option set to yes then it ignors and nex automatic number is assigned
varchar2
7AP_SUPPLIERS
SUMMARY_FLAG Varchar2
Y This is used internaly and defaults to N
8
AP_SUPPLIERSENABLED_FLAG Varchar
2Y Used by
purchasing to find supplier is enabled and default is N
9AP_SUPPLIERS SEGMENT2 Varchar
2N
10AP_SUPPLIERS SEGMENT3 Varchar
2N
11AP_SUPPLIERS SEGMENT4 Varchar
2N
12AP_SUPPLIERS SEGMENT5 Varchar
2N
13 AP_SUPPLIERS LAST_UPDATE_L Number N
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
OGIN 14 AP_SUPPLIERS CREATION_DATE Date N15 AP_SUPPLIERS CREATED_BY Number N
16
AP_SUPPLIERS EMPLOYEE_ID Number N This is for Employee type of suppliers
17AP_SUPPLIERS VENDOR_TYPE_L
OOKUP_CODE Varchar2
N PRIVI_XX_SUPPLIERLOAD.csv
Vendor Type varchar2
18
AP_SUPPLIERS CUSTOMER_NUM
Varchar2
N The no by which suppliers identify customer
19
AP_SUPPLIERS ONE_TIME_FLAG Varchar2
N This flag is enabled if repeat business is not anticipated with the supplier. Defaults to'N'.
20AP_SUPPLIERS PARENT_VENDO
R_ID Number N
21
AP_SUPPLIERS MIN_ORDER_AMOUNT
Number N This is the minimum purchase order amount for the supplier
22 AP_SUPPLIERS SHIP_TO_LOCATION_ID
Number N This is the unique identifier for the location where the supplier sends shipments forgoods and services. This corresponds to SHIP_TO_LOCATION_CODE. A value can beprovided for either or both columns. If both are provided, the system will check toensure that the values match a valid 'ID-CODE'
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
combination. This identifier defaultsfrom FINANCIALS_SYSTEM_PARAMS_ALL.SHIP_TO_LOCATION_ID.
23
AP_SUPPLIERS BILL_TO_LOCATION_ID
Number N This is the unique identifier for the location where the supplier sends invoices for goodsand services. This corresponds to BILL_TO_LOCATION_CODE. A value can beprovided for either or both columns. If both are provided, the system will check toensure that the values match a valid 'ID-CODE' combination. This identifier defaultsfrom FINANCIALS_SYSTEM_PARAMS_ALL.BILL_TO_LOCATION_ID.
24 AP_SUPPLIERS SHIP_VIA_LOOKUP_CODE
Varchar2
N This is the default freight carrier that is used with the supplier. This value defaults fromFINANCIALS_
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
SYSTEM_PARAMS_ALL.SHIP_VIA_LOOKUP_CODE.
25
AP_SUPPLIERS FREIGHT_TERMS_LOOKUP_CODE
Varchar2
N This is the default freight terms that have been negotiated with the supplier. This valuedefaults fromFINANCIALS_SYSTEM_PARAMS_ALL.FREIGHT_TERMS_LOOKUP_CODE
26
AP_SUPPLIERS FOB_LOOKUP_CODE
Varchar2
N This is the Free on Board (FOB) code for the supplier. This value defaults fromFINANCIALS_SYSTEM_PARAMS_ALL.FOB_LOOKUP_CODE.
27 AP_SUPPLIERS TERMS_ID Number N This is the unique identifier for the terms on invoices from the supplier. Thiscorresponds to TERMS_NAME. A value can be provided for either or both columns. Ifboth are provided, the system will check to ensure that the values match a validID-NAME
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
combination. This value defaults fromFINANCIALS_SYSTEM_PARAMS_ALL.TERMS_ID.
28
AP_SUPPLIERS SET_OF_BOOKS_ID
Number N This value defaults from the set of books associated with the user submitting theconcurrent request. This is derived from the System Profile Options: GL Set of Books ID.
29AP_SUPPLIERS CREDIT_STATUS
_LOOKUP_CODE Varchar2
N
30 AP_SUPPLIERS CREDIT_LIMIT Number N
31
AP_SUPPLIERS ALWAYS_TAKE_DISC_FLAG
Varchar2
N Enable this option if all payments for the supplier will be paid less the discount. Thisvalues defaults fromFINANCIALS_SYSTEM_PARAMS_ALL.ALWAYS_TAKE_DISC_FLAG
32 AP_SUPPLIERS PAY_DATE_BASIS_LOOKUP_CODE
Varchar2
N The Pay Date Basis determines the pay date for the supplier's invoice: Discount or Due.This value defaults fromFINANCIALS_SYSTEM_PAR
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
AMS_ALL.PAY_DATE_BASIS_LOOKUP_CODE.
33
AP_SUPPLIERS PAY_GROUP_LOOKUP_CODE
Varchar2
N PRIVI_XX_SUPPLIERLOAD.csv
This is the Pay Group for the supplier. This can be used in payment batch processing forgrouping categories of suppliers. This value is defaulted from
34
AP_SUPPLIERS PAYMENT_PRIORITY
Number N A number between 1 and 99 which represents the payment priority for the supplier.This value defaults to 99.
35
AP_SUPPLIERS INVOICE_CURRENCY_CODE
Varchar2
N PRIVI_XX_SUPPLIERLOAD.csv
This is the default currency for Purchasing documents and Payables transactions fromthe supplier. This value is defaulted fromAP_SYSTEM_PARAMETERS_ALL.INVOICE_CURRENCY_CODE.
36 AP_SUPPLIERS PAYMENT_CURRENCY_CODE
Varchar2
N This is the default currency for Payments to this supplier. This value is derived fromAP_SYSTEM_PARAMETERS_ALL.INVOICE_CURRENCY_C
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
ODE
37
AP_SUPPLIERS INVOICE_AMOUNT_LIMIT
Number N Invoice amount no Must be a number.
38
AP_SUPPLIERS EXCHANGE_DATE_LOOKUP_CODE
Varchar2
N
39
AP_SUPPLIERS HOLD_ALL_PAYMENTS_FLAG
Varchar2
N Enable if payments for the supplier are to be held. This value defaults to 'N'.
40
AP_SUPPLIERS HOLD_FUTURE_PAYMENTS_FLAG
Varchar2
N Enable if unvalidated invoices for the supplier should be held. This value defaults to'N'.
41
AP_SUPPLIERS HOLD_REASON Varchar2
N The reason invoices from the supplier are being held.
42 AP_SUPPLIERS DISTRIBUTION_SET_ID
Number N This column is valid at the supplier level only in a Non Multi-Org environment. Thiscolumn is the unique identifier for the distribution set that will be applied to invoicesfrom the supplier. This corresponds to DISTRIBUTION_SET_NAME. A value can beprovided for either or both columns. If both are provided, the
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
system will check toensure that the values represent a valid ID-NAME combination.
43
AP_SUPPLIERS ACCTS_PAY_CODE_COMBINATION_ID
Number N This column is valid at the supplier level only in a Non Multi-Org environment. Thiswill be the default liability account for supplier sites from the supplier. If a value for thiscolumn is entered into the interface table in a multi-org environment, that value will beignored and not brought into the PO_VENDORS table.
44
AP_SUPPLIERS DISC_LOST_CODE_COMBINATION_ID
Number N
45
AP_SUPPLIERS DISC_TAKEN_CODE_COMBINATION_ID
Number N
46
AP_SUPPLIERS EXPENSE_CODE_COMBINATION_ID
Number N
47 AP_SUPPLIERS PREPAY_CODE_COMBINATION_ID
Number N This column is valid at the supplier level only in a Non Multi-Org environment. This isthe account
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
that will be used for Prepayment Item distributions entered on invoices forthe supplier. If a value for this column is entered into the interface table in a mulit-orgenvironment, that value will be ignored and not brought into the PO_VENDORS table.
48
AP_SUPPLIERS NUM_1099 Varchar2
N Supplier Tax Identification Number (TIN).
49
AP_SUPPLIERS TYPE_1099 Varchar2
N This is the income tax type for a United States 1099 reportable supplier
50
AP_SUPPLIERS WITHHOLDING_STATUS_LOOKUP_CODE
Varchar2
N
51AP_SUPPLIERS WITHHOLDING_
START_DATE Date N
52
AP_SUPPLIERS ORGANIZATION_TYPE_LOOKUP_CODE
Varchar2
N PRIVI_XX_SUPPLIERLOAD.csv
This is the type of enterprise for the supplier
varchar2
53 AP_SUPPLIERS VAT_CODE varchar2
N This column is valid at the supplier level only in a Non Multi-Org environment. Thiswill be the default tax code for invoices
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
entered for the supplier. In Non Multi-Orgenviroments, this value defaults fromFINANCIALS_SYSTEM_PARAMS_ALL.VAT_CODE.
54
AP_SUPPLIERS START_DATE_ACTIVE
Date N This is the date on which the supplier will appear on supplier lists of values.
55
AP_SUPPLIERS END_DATE_ACTIVE
Date N This is the date on which the supplier will no longer appear on any supplier list ofvalues
56
AP_SUPPLIERS MINORITY_GROUP_LOOKUP_CODE
Varchar2
N If the supplier is a minority-owned business, enter the minority group in this column
57
AP_SUPPLIERS PAYMENT_METHOD_LOOKUP_CODE
Varchar2
N
58AP_SUPPLIERS BANK_ACCOUNT
_NAME Varchar2
N
59AP_SUPPLIERS BANK_ACCOUNT
_NUM Varchar2
N
60AP_SUPPLIERS BANK_NUM Varchar
2N
61AP_SUPPLIERS BANK_ACCOUNT
_TYPE Varchar2
N
62
AP_SUPPLIERS WOMEN_OWNED_FLAG
Varchar2
N Indicates if the supplier is a woman-owned business. This value defaults to 'N'.
63 AP_SUPPLIERS SMALL_BUSINESS_FLAG
Varchar2
N Indicates if the supplier is a small business. This value
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
defaults to 'N'.
65
AP_SUPPLIERS STANDARD_INDUSTRY_CLASS
Varchar2
N Standard Industry Code (SIC).
66
AP_SUPPLIERS HOLD_FLAG Varchar2
N Indicates whether the supplier is on purchasing hold. Purchase orders can be enteredfor the supplier but not approved. This value defaults to 'N'.
67
AP_SUPPLIERS PURCHASING_HOLD_REASON
Varchar2
N The reason purchase orders for this supplier cannot be approved.
68
AP_SUPPLIERS HOLD_BY Number N This is the unique identifier of the employee who placed the supplier on purchasinghold.
69
AP_SUPPLIERS HOLD_DATE Date N The date the supplier was placed on purchasing hold
70
AP_SUPPLIERS TERMS_DATE_BASIS
Varchar2
N PRIVI_XX_SUPPLIERLOAD.csv
The date from which Payables calculates a scheduled payment. This value defaults toAP_SYSTEM_PARAMETERS_ALL.TERMS.DATE_BASIS.
71AP_SUPPLIERS PRICE_TOLERAN
CE Number N
72 AP_SUPPLIERS INSPECTION_REQUIRED_FLAG
Varchar2
N These columns, taken
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
together, determine the level of match level approval that isrequired for invoices matched to purchase orders: 4-way, 3-way, or 2-way. These valuesdefault from PO_SYSTEM_PARAMETERS_ALL.INSPECTION_REQUIRED_FLAG andPO_SYSTEM_PARAMETERS_ALL.RECEIVING FLAG
73AP_SUPPLIERS RECEIPT_REQUI
RED_FLAG Varchar2
N
74
AP_SUPPLIERS QTY_RCV_TOLERANCE
Number N Oracle Purchasing uses this column during the receiving process. It identifies themaximum acceptable quantity received in excess of the quantity ordered on a purchaseorder for the supplier. This value defaults fromRCV_PARAMETERS.QTY_RCV_TOLERANCE
75 AP_SUPPLIERS QTY_RCV_EXCEPTION_CODE
Varchar2
N Oracle Purchasing uses this
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
column to determine the action to be taken when thequantity-received tolerance is violated during the receiving process. This value defaultsfrom RCV_PARAMETERS.QTY_RCV_EXCEPTION_CODE.
76
AP_SUPPLIERS ENFORCE_SHIP_TO_LOCATION_CODE
Varchar2
N Oracle Purchasing uses this column to determine the action to be taken when receivinglocation varies from the ship-to location. This value defaults fromRCV_PARAMETERS.ENFORCE_SHIP_TO_LOCATION
77
AP_SUPPLIERS DAYS_EARLY_RECEIPT_ALLOWED
Number N Oracle Purchasing uses this column to determine the maximum number of days itemscan be received early. This defaults fromRCV_PARAMETERS.DAYs_EARLY_RECEIPT_ALLOWED
78 AP_SUPPLIERS DAYS_LATE_REC Number N Oracle
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
EIPT_ALLOWED Purchasing uses this column to determine the maximum number of days itemscan be received late. RCV_PARAMETERS.DAYS_LATE_RECEIPT_ALLOWED
79
AP_SUPPLIERS RECEIPT_DAYS_EXCEPTION_CODE
Varchar2
N Oracle Purchasing uses this column to determine the action to be taken when items arereceived earlier or later than the allowed number of days specified. This value defaultsfrom RCV_PARAMETERS.RECEIPT_DAYS_EXCEPTION_CODE.
80
AP_SUPPLIERS RECEIVING_ROUTING_ID
Varchar2
N This is the receipt routing unique identifier used by Oracle Purchasing.RCV_PARAMETERS.RECEIVING_ROUTING_ID.
81 AP_SUPPLIERS ALLOW_SUBSTITUTE_RECEIPTS_FLAG
Varchar2
N Oracle Purchasing uses this column to determine if substitute items can be
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
received inplace of the ordered items. This value defaults fromRCV_PARAMETERS.ALLOW_SUBSTITUTE_RECEIPTS_FLAG.
82
AP_SUPPLIERS ALLOW_UNORDERED_RECEIPTS_FLAG
Varchar2
N Oracle Purchasing uses this column to determine if unordered receipts are allowed.This value defaults fromRCV_PARAMETERS.ALLOW_UNORDERED_RECEIPTS_FLAG
83 AP_SUPPLIERS HOLD_UNMATCHED_INVOICES_FLAG
Varchar2
N This column indicates if unmatched invoices should be put on hold. IfAP_SUPPLIERS_INT.VENDOR_TYPE_LOOKUP_CODE = 'EMPLOYEE', then thisvalue defaults fromAP_SYSTEM_PARAMETERS_ALL.HOLD_UNMATCHED_INVOICES_FLAG. If not,then this value defaults fromFINANCIALS_SYSTEM_PARAMS_ALL.HOLD_UNMATCHE
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
D_INVOICES_FLAG.
84
AP_SUPPLIERS EXCLUSIVE_PAYMENT_FLAG
Varchar2
N This column indicates whether or not separate payments should be created for eachinvoice for the supplier. This value defaults fromFINANCIALS_SYSTEM_PARAMS_ALL.EXCLUSIVE_PAYMENT_FLAG.
85
AP_SUPPLIERS AP_TAX_ROUNDING_RULE
Varchar2
N This column determines the rounding rule for tax values. This value defaults fromFINANCIALS_SYSTEM_PARAMETERS_ALL.TAX_ROUNDING_RULE
86
AP_SUPPLIERS AUTO_TAX_CALC_FLAG
Number N This column indicates the level for automatic tax calculation for supplier. This valuedefaults from AP_SYSTEM_PARAMETERS_ALL.AUTO_TAX_CALC_FLAG andcannot be changed in a Multi-Org environment
87 AP_SUPPLIERS AUTO_TAX_CALC_OVERRIDE
Number N This indicates if override of tax calculation is allowed at
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
supplier site level. This valuedefaults from AP_SYSTEM_PARAMETERS_ALL.AUTO_TAX_CALC_OVERRIDE andcannot be changed in a Multi-Org environment
88
AP_SUPPLIERS AMOUNT_INCLUDES_TAX_FLAG
Number N This column indicates if amounts on invoices from the supplier include tax. This valuedefaults from AP_SYSTEM_PARAMETERS_ALL.AMOUNT_INCLUDES_TAX_FLAGand cannot be changed in a Multi-Org environment.
89
AP_SUPPLIERS TAX_VERIFICATION_DATE
Date N The date tax verification for 1099 reporting purposes was received from the supplier.
90
AP_SUPPLIERS NAME_CONTROL Varchar2
N This column is the first four characters of the last name of a 1099 reportable supplier
91 AP_SUPPLIERS STATE_REPORTABLE_FLAG
Varchar2
N This column indicates participation in the United States Internal Revenue ServiceCombined
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
Filing Program and that the supplier is reportable to a state taxing authority. This value defaults to 'N'.
92
AP_SUPPLIERS FEDERAL_REPORTABLE_FLAG
Varchar2
N This column indicates that the supplier is reportable to the United States InternalRevenue Service for 1099 purposes. This value defaults to 'N'.
93
AP_SUPPLIERS ATTRIBUTE_CATEGORY
Varchar2
N This is the descriptive flexfield category for the descriptive flexfield information for thesupplier.
94 AP_SUPPLIERS ATTRIBUTE1..N Varchar2
N This is the descriptive flexfield information for the supplier. The import process will notvalidate the information entered in these columns. However, the structure of theinformation entered (data types, value sets) must match the structure of the descriptive
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
flexfield segments defined for suppliers or users will experience validation problemswhen trying to access the information through the application
95 AP_SUPPLIERS REQUEST_ID Number N
96AP_SUPPLIERS PROGRAM_APPLI
CATION_ID Number N
97 AP_SUPPLIERS PROGRAM_ID Number N
98AP_SUPPLIERS PROGRAM_UPDA
TE_DATE Date N
99AP_SUPPLIERS OFFSET_VAT_CO
DE Varchar2
N
100
AP_SUPPLIERS VAT_REGISTRATION_NUM
Varchar2
N Tax registration number.
101
AP_SUPPLIERS AUTO_CALCULATE_INTEREST_FLAG
Varchar2
N This column indicates if interest is to be automatically calculated on past due invoicesfor the supplier. This value defaults to 'N'.
102AP_SUPPLIERS VALIDATION_NU
MBER Number N
103
AP_SUPPLIERS EXCLUDE_FREIGHT_FROM_DISCOUNT
Varchar2
N This column indicates if the freight amount is to be excluded from the discount oninvoices from the supplier. This value defaults to 'N'.
104 AP_SUPPLIERS TAX_REPORTING_NAME
Varchar2
N This is the tax-reporting
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
name for a supplier subject to United States Internal RevenueService income tax reporting. This is only necessary if the supplier name is differentfrom the tax-reporting name
105AP_SUPPLIERS CHECK_DIGITS Varchar
2N
106AP_SUPPLIERS BANK_NUMBER Varchar
2N
107
AP_SUPPLIERS ALLOW_AWT_FLAG
Varchar2
N This column indicates whether or not automatic withholding is allowed for thesupplier. This value defaults to 'N'.
108 AP_SUPPLIERS AWT_GROUP_ID Number N
109AP_SUPPLIERS GLOBAL_ATTRIB
UTE1..NVarchar2
N
110AP_SUPPLIERS GLOBAL_ATTRIB
UTE_CATEGORY Varchar2
N
111AP_SUPPLIERS EDI_TRANSACTI
ON_HANDLING Varchar2
N
112AP_SUPPLIERS EDI_PAYMENT_M
ETHOD Varchar2
N
113AP_SUPPLIERS EDI_PAYMENT_F
ORMAT Varchar2
N
114AP_SUPPLIERS EDI_REMITTANC
E_METHOD Varchar2
N
115AP_SUPPLIERS EDI_REMITTANC
E_INSTRUCTION Varchar2
N
116AP_SUPPLIERS BANK_CHARGE_
BEARER Varchar2
N
117AP_SUPPLIERS BANK_BRANCH_
TYPE Varchar2
N
118 AP_SUPPLIERS MATCH_OPTION Number N119 AP_SUPPLIERS FUTURE_DATED Varchar N
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
_PAYMENT_CCID 2
120AP_SUPPLIERS CREATE_DEBIT_
MEMO_FLAG Varchar2
N
121AP_SUPPLIERS OFFSET_TAX_FL
AG Varchar2
N
122 AP_SUPPLIERS PARTY_ID Number N
123AP_SUPPLIERS PARENT_PARTY_
IDVarchar2
N
124AP_SUPPLIERS TCA_SYNC_NUM
_1099Varchar2
N
125AP_SUPPLIERS TCA_SYNC_VEN
DOR_NAMEVarchar2
N
126AP_SUPPLIERS TCA_SYNC_VAT_
REG_NUMVarchar2
N
127AP_SUPPLIERS INDIVIDUAL_109
9Varchar N
128AP_SUPPLIERS NI_NUMBER Varchar
2N
130AP_SUPPLIERS TCA_SYNC_NUM
_1099Varchar2
N
131AP_SUPPLIERS TCA_SYNC_VEN
DOR_NAMEVarchar2
N
132AP_SUPPLIERS TCA_SYNC_VAT_
REG_NUMVarchar2
N
133AP_SUPPLIER_SITES_ALL
VENDOR_SITE_ID
Number Y
134AP_SUPPLIER_SITES_ALL
LAST_UPDATE_DATE
Date Y
135AP_SUPPLIER_SITES_ALL
LAST_UPDATED_BY
Number Y
136AP_SUPPLIER_SITES_ALL
VENDOR_ID Number Y
137AP_SUPPLIER_SITES_ALL
VENDOR_SITE_CODE
Varchar2
Y MIDAS_XX_SUPPLIERSITELOAD
VENDOR_SITE_CODE
varchar2
138AP_SUPPLIER_SITES_ALL
VENDOR_SITE_CODE_ALT
Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
VENDOR_SITE_CODE_ALT
139AP_SUPPLIER_SITES_ALL
LAST_UPDATE_LOGIN
Number N
140AP_SUPPLIER_SITES_ALL
CREATION_DATE Date N
141AP_SUPPLIER_SITES_ALL
CREATED_BY Number N
142AP_SUPPLIER_SITES_ALL
PURCHASING_SITE_FLAG
Varchar2
N
143AP_SUPPLIER_SITES_ALL
RFQ_ONLY_SITE_FLAG
Varchar2
N
144AP_SUPPLIER_SITES_ALL
PAY_SITE_FLAG Varchar2
N
145AP_SUPPLIER_SITES_ALL
ATTENTION_AR_FLAG
Varchar2
N
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
145AP_SUPPLIER_SITES_ALL
ADDRESS_LINE1 Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
ADDRESS_LINE1
varchar2
146AP_SUPPLIER_SITES_ALL
ADDRESS_LINES_ALT
Varchar2
N
147AP_SUPPLIER_SITES_ALL
ADDRESS_LINE2 Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
ADDRESS_LINE2
varchar2
148AP_SUPPLIER_SITES_ALL
ADDRESS_LINE3 Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
ADDRESS_LINE3
varchar2
149AP_SUPPLIER_SITES_ALL
CITY Varchar2
N MIDAS_XX_SUPPLIERSITELOAD City
varchar2
150AP_SUPPLIER_SITES_ALL
STATE Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
State varchar2
151AP_SUPPLIER_SITES_ALL
ZIP Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
Zip varchar2
152AP_SUPPLIER_SITES_ALL
PROVINCE Varchar2
N
153AP_SUPPLIER_SITES_ALL
COUNTRY Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
COUNTRY varchar2
154AP_SUPPLIER_SITES_ALL
AREA_CODE Varchar2
N
155AP_SUPPLIER_SITES_ALL
PHONE Varchar2
N MIDAS_XX_SUPPLIERSITELOAD Phone
156AP_SUPPLIER_SITES_ALL
CUSTOMER_NUM
Varchar2
N
157AP_SUPPLIER_SITES_ALL
SHIP_TO_LOCATION_ID
Number N MIDAS_XX_SUPPLIERSITELOAD
SHIP_TO_LOCATION_ID
varchar2
158AP_SUPPLIER_SITES_ALL
BILL_TO_LOCATION_ID
Number N MIDAS_XX_SUPPLIERSITELOAD
BILL_TO_LOCATION_ID
varchar2
159AP_SUPPLIER_SITES_ALL
SHIP_VIA_LOOKUP_CODE
Varchar2
N
160
AP_SUPPLIER_SITES_ALL
FREIGHT_TERMS_LOOKUP_CODE
Varchar2
N PRIVI_XX_SUPPLIERSITELOAD
FREIGHT_TERMS_LOOKUP_CODE
Varchar2
161AP_SUPPLIER_SITES_ALL
FOB_LOOKUP_CODE
Varchar2
N
162AP_SUPPLIER_SITES_ALL
INACTIVE_DATE Date N
163AP_SUPPLIER_SITES_ALL
FAX Varchar2
N
164AP_SUPPLIER_SITES_ALL
FAX_AREA_CODE
Varchar2
N
165AP_SUPPLIER_SITES_ALL
TELEX Varchar2
N
166
AP_SUPPLIER_SITES_ALL
PAYMENT_METHOD_LOOKUP_CODE
Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
Payment_method
varchar2
167AP_SUPPLIER_SITES_ALL
BANK_ACCOUNT_NAME
Varchar2
N
168AP_SUPPLIER_SITES_ALL
BANK_ACCOUNT_NUM
Varchar2
N
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
169AP_SUPPLIER_SITES_ALL
BANK_NUM Varchar2
N
170AP_SUPPLIER_SITES_ALL
BANK_ACCOUNT_TYPE
Varchar2
N
171AP_SUPPLIER_SITES_ALL
TERMS_DATE_BASIS
Varchar2
N
172AP_SUPPLIER_SITES_ALL
CURRENT_CATALOG_NUM
Varchar2
N
173AP_SUPPLIER_SITES_ALL
VAT_CODE Varchar2
N
174AP_SUPPLIER_SITES_ALL
DISTRIBUTION_SET_ID
Number N
175
AP_SUPPLIER_SITES_ALL
ACCTS_PAY_CODE_COMBINATION_ID
Number N varchar2
176AP_SUPPLIER_SITES_ALL
PREPAY_CODE_COMBINATION_ID
Number N varchar2
177AP_SUPPLIER_SITES_ALL
PAY_GROUP_LOOKUP_CODE
Varchar2
N
178AP_SUPPLIER_SITES_ALL
PAYMENT_PRIORITY
Number N
179
AP_SUPPLIER_SITES_ALL
TERMS_ID Number N MIDAS_XX_SUPPLIERSITELOAD
PAYMENT_TERMS
varchar2
180AP_SUPPLIER_SITES_ALL
INVOICE_AMOUNT_LIMIT
Number N MIDAS_XX_SUPPLIERSITELOAD Amount_limit
181
AP_SUPPLIER_SITES_ALL
PAY_DATE_BASIS_LOOKUP_CODE
Varchar2
N
182AP_SUPPLIER_SITES_ALL
ALWAYS_TAKE_DISC_FLAG
Varchar2
N
183AP_SUPPLIER_SITES_ALL
INVOICE_CURRENCY_CODE
Varchar2
N
184AP_SUPPLIER_SITES_ALL
PAYMENT_CURRENCY_CODE
Varchar2
N
185AP_SUPPLIER_SITES_ALL
HOLD_ALL_PAYMENTS_FLAG
Varchar2
N
186
AP_SUPPLIER_SITES_ALL
HOLD_FUTURE_PAYMENTS_FLAG
Varchar2
N
187AP_SUPPLIER_SITES_ALL
HOLD_REASON Varchar2
N
188
AP_SUPPLIER_SITES_ALL
HOLD_UNMATCHED_INVOICES_FLAG
Varchar2
N
189AP_SUPPLIER_SITES_ALL
AP_TAX_ROUNDING_RULE
Varchar2
N
190AP_SUPPLIER_SITES_ALL
AUTO_TAX_CALC_FLAG
Varchar2
N
191 AP_SUPPLIER_SITES_ AUTO_TAX_CAL Varchar N
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
ALL C_OVERRIDE 2
192AP_SUPPLIER_SITES_ALL
AMOUNT_INCLUDES_TAX_FLAG
Varchar2
N
193AP_SUPPLIER_SITES_ALL
EXCLUSIVE_PAYMENT_FLAG
Varchar2
N
194
AP_SUPPLIER_SITES_ALL
TAX_REPORTING_SITE_FLAG
Varchar2
N PRIVI_XX_SUPPLIERSITELOAD
TAX_REPORTING_SITE_FLAG
varchar2
195AP_SUPPLIER_SITES_ALL
ATTRIBUTE_CATEGORY
Varchar2
N
196AP_SUPPLIER_SITES_ALL
ATTRIBUTE1..N Varchar2
N
197AP_SUPPLIER_SITES_ALL
REQUEST_ID Number N
198AP_SUPPLIER_SITES_ALL
PROGRAM_APPLICATION_ID
Number N
199AP_SUPPLIER_SITES_ALL
PROGRAM_ID Number N
200AP_SUPPLIER_SITES_ALL
PROGRAM_UPDATE_DATE
Date N
201AP_SUPPLIER_SITES_ALL
VALIDATION_NUMBER
Number N
202
AP_SUPPLIER_SITES_ALL
EXCLUDE_FREIGHT_FROM_DISCOUNT
Varchar2
N
203
AP_SUPPLIER_SITES_ALL
VAT_REGISTRATION_NUM
Varchar2
N MIDAS_XX_SUPPLIERSITELOAD
VAT_REGISTRATION_NUM
varchar2
204AP_SUPPLIER_SITES_ALL
OFFSET_VAT_CODE
Varchar2
N
205AP_SUPPLIER_SITES_ALL
ORG_ID Number N
206AP_SUPPLIER_SITES_ALL
CHECK_DIGITS Varchar2
N
207AP_SUPPLIER_SITES_ALL
BANK_NUMBER Varchar2
N
208AP_SUPPLIER_SITES_ALL
ADDRESS_LINE4 Varchar2
N
209AP_SUPPLIER_SITES_ALL
COUNTY Varchar2
N
210AP_SUPPLIER_SITES_ALL
ADDRESS_STYLE Varchar2
N
211AP_SUPPLIER_SITES_ALL
LANGUAGE Varchar2
N
212AP_SUPPLIER_SITES_ALL
ALLOW_AWT_FLAG
Varchar2
N
213AP_SUPPLIER_SITES_ALL
AWT_GROUP_ID Number N
214AP_SUPPLIER_SITES_ALL
GLOBAL_ATTRIBUTE1..N
Varchar2
N
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
215AP_SUPPLIER_SITES_ALL
GLOBAL_ATTRIBUTE_CATEGORY
Varchar2
N
216AP_SUPPLIER_SITES_ALL
EDI_TRANSACTION_HANDLING
Varchar2
N
217AP_SUPPLIER_SITES_ALL
EDI_ID_NUMBER Varchar2
N
218AP_SUPPLIER_SITES_ALL
EDI_PAYMENT_METHOD
Varchar2
N
219AP_SUPPLIER_SITES_ALL
EDI_PAYMENT_FORMAT
Varchar2
N
220AP_SUPPLIER_SITES_ALL
EDI_REMITTANCE_METHOD
Varchar2
N
221AP_SUPPLIER_SITES_ALL
BANK_CHARGE_BEARER
Varchar2
N
222AP_SUPPLIER_SITES_ALL
EDI_REMITTANCE_INSTRUCTION
Varchar2
N
223AP_SUPPLIER_SITES_ALL
BANK_BRANCH_TYPE
Varchar2
N
224AP_SUPPLIER_SITES_ALL
PAY_ON_CODE Varchar2
N
225AP_SUPPLIER_SITES_ALL
DEFAULT_PAY_SITE_ID
Number N
226
AP_SUPPLIER_SITES_ALL
PAY_ON_RECEIPT_SUMMARY_CODE
Varchar2
N
227AP_SUPPLIER_SITES_ALL
TP_HEADER_ID Number N
228AP_SUPPLIER_SITES_ALL
ECE_TP_LOCATION_CODE
Varchar2
N
229AP_SUPPLIER_SITES_ALL
PCARD_SITE_FLAG
Varchar2
N
230AP_SUPPLIER_SITES_ALL
MATCH_OPTION Varchar2
N
231AP_SUPPLIER_SITES_ALL
COUNTRY_OF_ORIGIN_CODE
Varchar2
N
232AP_SUPPLIER_SITES_ALL
FUTURE_DATED_PAYMENT_CCID
Number N
233AP_SUPPLIER_SITES_ALL
CREATE_DEBIT_MEMO_FLAG
Varchar2
N
234AP_SUPPLIER_SITES_ALL
OFFSET_TAX_FLAG
Varchar2
N
235AP_SUPPLIER_SITES_ALL
SUPPLIER_NOTIF_METHOD
Varchar2
N
236AP_SUPPLIER_SITES_ALL
EMAIL_ADDRESS Varchar2
N
237AP_SUPPLIER_SITES_ALL
REMITTANCE_EMAIL
Varchar2
N
238AP_SUPPLIER_SITES_ALL
PRIMARY_PAY_SITE_FLAG
Varchar2
N
239 AP_SUPPLIER_SITES_ SHIPPING_CONT Varchar N
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
(1) (2) (3) (4) (5) (6) (7) (8) (9)
Ref.#
Target Application Table
Target Application Table Column
Target Column
Datatype
Not Null
?
Source System File Name
Source System Field Name
Source System
Field Datatyp
e
Default Value
ALL ROL 2
240AP_SUPPLIER_SITES_AL
SELLING_COMPANY_IDENTIFIER
Varchar2
N
241AP_SUPPLIER_SITES_AL
GAPLESS_INV_NUM_FLAG
Varchar2
N
242AP_SUPPLIER_SITES_AL
DUNS_NUMBER Varchar2
N
243AP_SUPPLIER_SITES_AL
TOLERANCE_ID NUMBER N
244AP_SUPPLIER_SITES_AL
LOCATION_ID NUMBER N
245AP_SUPPLIER_SITES_AL
SERVICES_TOLERANCE_ID
NUMBER N
246AP_SUPPLIER_SITES_AL
RETAINAGE_RATE
NUMBER N
247AP_SUPPLIER_SITES_AL
TCA_SYNC_STATE
Varchar2
N
248AP_SUPPLIER_SITES_AL
TCA_SYNC_PROVINCE
Varchar2
N
249AP_SUPPLIER_SITES_AL
TCA_SYNC_COUNTY
Varchar2
N
250AP_SUPPLIER_SITES_AL
TCA_SYNC_CITY Varchar2
N
251AP_SUPPLIER_SITES_AL
TCA_SYNC_ZIP Varchar2
N
252AP_SUPPLIER_SITES_AL
TCA_SYNC_COUNTRY
Varchar2
N
253AP_SUPPLIER_SITES_AL
TCA_SYNC_COUNTY
Varchar2
N
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Extract File Layout
Below is a table documenting the extract file schema.
Datafile Field Name Datatype Position:From
Position:To
Oracle Table Oracle Column
PRIVI_XX_SUPPLIERLOAD VENDOR_NAME
varchar2 AP_SUPPLIERS VENDOR_NAME
PRIVI_XX_SUPPLIERLOAD
VENDOR_TYPE_LOOKUP_CODE
varchar2 AP_SUPPLIERS VENDOR_TYPE_LOOKUP_CODE
PRIVI_XX_SUPPLIERSITELOAD
VENDOR_SITE_CODE
varchar2 AP_SUPPLIER_SITES_ALL
VENDOR_SITE_CODE
PRIVI_XX_SUPPLIERSITELOAD ADDRESS_LINE1
varchar2 AP_SUPPLIER_SITES_ALL
ADDRESS_LINE1
PRIVI_XX_SUPPLIERSITELOAD ADDRESS_LINE2
varchar2 AP_SUPPLIER_SITES_ALL
ADDRESS_LINE2
PRIVI_XX_SUPPLIERSITELOAD ADDRESS_LINE3
varchar2 AP_SUPPLIER_SITES_ALL
ADDRESS_LINE3
PRIVI_XX_SUPPLIERSITELOAD CITY
varchar2 AP_SUPPLIER_SITES_ALL
CITY
PRIVI_XX_SUPPLIERSITELOAD STATE
varchar2 AP_SUPPLIER_SITES_ALL
STATE
PRIVI_XX_SUPPLIERSITELOAD ZIP
varchar2 AP_SUPPLIER_SITES_ALL
ZIP
PRIVI_XX_SUPPLIERSITELOAD
COUNTRYvarchar2 AP_SUPPLIER_SITES_
ALLCOUNTRY
PRIVI_XX_SUPPLIERSITELOAD
BILL_TO_LOCATION_ID
varchar2 AP_SUPPLIER_SITES_ALL
BILL_TO_LOCATION_ID
PRIVI_XX_SUPPLIERSITELOAD
SHIP_TO_LOCATION_
varchar2 AP_SUPPLIER_SITES_ALL
SHIP_TO_LOCATION_ID
PRIVI_XX_SUPPLIERSITELOAD
PAYMENT CURRENCY_CODE
varchar2 AP_SUPPLIER_SITES_ALL
PAYMENT_CURRENCY_CODE
PRIVI_XX_SUPPLIERSITELOAD
PAYMENT_ TERMS
number AP_SUPPLIER_SITES_ALL
TERMS_ID
XX_SUPPLIERSITECONTLOAD EMAIL_ADDRESS
varchar2 AP_SUPPLIER_SITES_ALL
EMAIL_ADDRESS
XX_SUPPLIERSITECONTLOAD
ACCTS_PAY_CODE_COMBINATION
varchar2 AP_SUPPLIER_SITES_ALL
ACCTS_PAY_CODE_COMBINATION_ID
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Datafile Field Name Datatype Position:From
Position:To
Oracle Table Oracle Column
XX_SUPPLIERSITECONTLOAD
PREPAY_CODE_COMBINATION
varchar2 AP_SUPPLIER_SITES_ALL
PREPAY_CODE_COMBINATION_ID
XX_SUPPLIERSITECONTLOAD
PAYMENT_METHOD_LOOKUP_CODE
varchar2 AP_SUPPLIER_SITES_ALL
PAYMENT_METHOD_LOOKUP_CODE
XX_SUPPLIERSITECONTLOAD
ORGANIZATION_TYPE_LOOKUP_CODE
varchar2 AP_SUPPLIERS ORGANIZATION_TYPE_LOOKUP_CODE
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Data Clean-up
N/A
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Data Normalization
N/A
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Objects
Data File : PRIVI_XX_SUPPLIERLOAD.csv, PRIVI_XX_SUPPLIERSITELOAD.csv
Control File : XX_SUPPLIERLOAD.CTL, XX_SUPPLIER_SITE_LOAD.CTL
Validation Script : PRIVI_XX_SUPPLIER_VAL_PRC, PRIVI_XX_SUP_SITE_VAL_PRC
Interface Script : PRIVI_XX_SUPPLIER_LOAD_PRC.sql, PRIVI_XX_SUPPLIER_SITE_LOAD.sql
Temp Tables : PRIVI_XX_SUPPLIER_STG_TBL, PRIVI_XX_SUP_SITES_STG_TBL
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
Data Conversion Process
1. Create the temp tables PRIVI_XX_SUPPLIER_STG_TBL, PRIVI_XX_SUP_SITES_STG_TBL.
2. Copy the data from the excel to PRIVI_XX_SUPPLIERLOAD.csv,
PRIVI_XX_SUPPLIERSITELOAD.csv dat files.
3. Move the dat and ctl files to $PO_TOP/bin in the server
4. Execute “PRIVI Suppier Load on Stage1” and “PRIVI Suppier Sites Load on Stage2.
Check the following backend query for the count of records in temp table
I. Select count (*) from PRIVI_XX_SUPPLIER_STG_TBL;
II. Select count (*) from PRIVI_XX_SUP_SITES_STG_TBL;
5. Execute “PRIVI_XX_SUP_SITE_VAL_PRC “, PRIVI_XX_SUPPLIER_VAL_PRC from the same
responsibility.
From TOAD/SQLPLUS execute:
Correct the errors by changing or correcting the data and repeat the steps from 2 to 6
until errors = 0.
6. Execute “PRIVI Supplier Load into Interface table” and “PRIVI Supplier Site Load into
Interface table” from the same responsibility.
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06
CV.040 Conversion Data Mapping
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
CV040_File Ref: document.doc (v. DRAFT 1)
Company Confidential - For internal use only
Conversion Mapping – Vendor 25 of 37
Doc Ref: 1.0 14 Mar 06