Data Migration vs. Data conversion

Data Migration vs. Data conversion

When we need to enter data into oracle Apps, following are the few techniques:

• The Data can be entered using the application Screens (for small amount of data, like creating PO, entering sales orders using Oracle Apps screens). 
• The data can be entered using Oracle’s Open System Interface (for regular operations e.g. for moving data from one module to another).
• The data can be stored in the database table directly (Not recommended by oracle and can be very risky, because on any event data is going to be stored in many tables and data should be validated before inserting into tables that may cause data integrity and inconsistency problem, sometimes it may corrupt the data completely.).
• Using third party tools like data loader (It is also can be used when data is relatively small (25-200 records) because it captures the keystrokes and works like manually entering the data into Oracle form but much faster as process is automated).

What is the need of Migration/Conversion?

Migration/Conversion are required when we are upgrading to one version to another (e.g. Oracle Apps 11.5.7 to Oracle 11.5.10) or moving data from some legacy system to Oracle Apps. There will be bulk  of data (sometimes millions or even more than that) that needs to be moved from one system to another  and  before moving the data it should be validated and only valid records should be entered into Oracle Apps.

If both the systems (Target and source) are not having same structure for data (Tables are not same/Table Structure is not same/The data is being stored in database is not same), it needs to be translated (e.g. upgrading from Oracle 11i to R12 where table structures are not same) then we say it as conversion (any kind of translation of data on Source data to make it suitable for Target system) otherwise migration.

 

What is Migration?

Migration of data means moving the data from one system to another using Interface Programs/APIs where both the systems have same structure of data.

Process of Migrating of data:
• Identify the data to be imported to new system (Business requirement).
• Extract the data into flat file/Staging table
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface)

What is Conversion?

Conversion of data means translating the data to suite target system (data should be formatted according to target system )  and then move the translated data using Interface Programs/APIs.
• Identify the data to be imported to new system (Business requirement).
• Extract into flat file/Staging table
• Translate/Convert/Format the data
• Load the data into Interface Table(using SQL* Loader/DB Link/Others) after validation(If loading the data using Interface) and then launch standard Interface concurrent program to load the data to Oracle Apps Base Tables
• If using API, fetch the data, validate it and then call API to import the data

 

How conversion/Migration and interface differ?

There are good numbers of parameter on which they can be categorized. Take few of them:
Frequency 
• Conversions/Migration are a one time event 
• interfaces are ongoing 
Occurrence in the project timeline
• conversions/Migration executed before production 
• interfaces executed during production 
Manner of execution
• Conversions/Migration are batch 
• Interfaces may be batch or real time 
Complexity
• Conversion/Migration does have very complex, it’s totally depends upon the data mapping activity. 
• Coordinating with other systems make interfaces more complex 
Maintenance
• Maintenance of interface is bit cost intensive task.

Trading Community Architecture (TCA) in Oracle Apps

Trading Community Architecture (TCA) in Oracle Apps

 

Before TCA:

• There are multiple customer definitions across the enterprise.
• It was very difficult to track current and historical information about the customers.
• There was a lack of support for mixed business.
• It was quite tough to understand relationships between customers and others (suppliers, partners, competitors).

Customers: More important than anything else!

In any business, Customers and their data are always important. More than that what is important is to understand who your customer interacts with inside and outside the enterprise.

What is Trading Community?

The summation of all entities, inclusive of partners, suppliers, and competitors, that are related to your customers is called a Trading Community.

Trading Community Architecture:

Trading Community Architecture is the implementation of technology and applications to allow users to create and maintain relationships among entities. It is a way to understand who your customer interacts with inside and outside the enterprise.

It’s Main Purpose:

• Create a central repository for the entire E-Business Suite to store information relating to all members of a trading community versus separate tables for each member-Prospects, Customers, Contacts, Employees, Partners, Distributors, Suppliers, Banks, etc.
• Record complex business relationships between Trading Community entities (including 3rd party relationships).
• Support all business models, industries, and geographies.

TCA Data Model Components:

1] Party:

It represents any entity that can enter into business relationships with your organization – Organization, Person, or Group.

• Person - A unique individual (dead or alive) of interest to the user.
• Organization  - A legal entity recognized by some government authority.
• Group  - A combination of two or more people, organizations or groups.

2] Party Relationship:

It is a binary relationship between two parties such as a partnership.

• Has a Role - Specifies the nature of the relationship between parties (e.g., member of, contact at, married to).
• Indicates the Nature of the relationship - hierarchy or matrix.
• Indicates the Direction of the relationship - superior – subordinate.
• Can become a Party - a Relationship becomes a party in itself.

The relationship model enables you to:

• Understand the complex relationships among members of your trading community
• Use this information to make better business decisions

3] Location:

A Location is a point in geographical space described by a street address. In previous releases of Oracle, there was a risk of some data redundancy if more than one customer shared the same site or location. The new model eliminates this redundancy.

• Any number of location types can be defined. (e.g., bill-to, ship-to, mail-to).
• There is no duplication of an address.
• It is possible to maintain Customer History per address.
• It is also possible to maintain Important Install Base info.

4] Party Site:

It links a Party with a Location

• Describes the usage of that Location for the Party  (e.g., mailing address, billing address, home address, etc.).
• Allows Parties to be associated to one or more Locations and any one Location to be associated with Parties.

5] Contact:

A Contact is a person in the context of an organization, modeled as a relationship between an organization and a person or between two people, (this can be either a party contact or an account contact).

6] Contact Point:

A Contact Point is a means of contacting a party, for example, a phone number, e-mail address, or fax number.

This can be applied to:

• A Party (person, organization, group or relationship)
• A Site or Location
• A Party at a Site or Location

An entity may have one or more Contact Points.

7] Customer Account:

A Customer Account represents the business (selling) relationship that a company deploying Oracle Applications has with a party.

• Stores details about the Financial relationship between a Party and your business.
• A Party may have one or more Customer Accounts.

8] Customer Account Site:

A Customer Account Site is a party site that is used by a customer account, for example, for billing or shipping purposes.

9] Customer Account Contacts:

A party contact that is used as a means of contacting the customer regarding his/her account.

Parties vs. Accounts

• From an application perspective, one of the most important things to understand about the TCA model is that the concept of “customer” is separated into two layers: The Party layer and the Account layer. 
• When CRM applications refer to “Customer” they are referring to the Party Layer.
• On the other hand, when ERP applications refer to “Customer” they are referring to the Account Layer. 

New Trading Entities in R12

Below are the new entities that are merged in TCA architecture in R12.

• Banks & Bank Branches
• Suppliers
• Legal Entity

Difference between Party and Customer

Difference between Party and Customer

PARTY	CUSTOMER
Prospective Customer and more relevant for CRM Purposes	A Customer which is used both in CRM as well as in OM, Financials or any other module Example (A Sales Order in OM or Invoice in Receivables cannot be created without creating a Customer record for the Party).
No Business Transactions involved (Sales Order, Sales Invoice, Debit Memo, Credit Memo, Receipt etc.,)	A Business Transaction like a Sales Order, Invoice, Debit Memo, Credit Memo,Receipt can be created.
A Party does not have account but have Sites	A Customer will have account and as well as Sites.
A Party can exist without Customer Record	A Party record is must to create a Customer Record linked through party_id.
A Party Record will not have record in following tables     HZ_CUST_ACCOUNTS     HZ_CUST_ACCT_SITES_ALL     HZ_CUST_SITE_USES_ALL     HZ_CUST_ACCOUNT_ROLES     HZ_CUST_ACCT_RELATE_ALL	A Customer Record will have records in following tables     HZ_CUST_ACCOUNTS     HZ_CUST_ACCT_SITES_ALL     HZ_CUST_SITE_USES_ALL     HZ_CUST_ACCOUNT_ROLES     HZ_CUST_ACCT_RELATE_ALL      with reference to party_id column.

Value Set Basics in Oracle Apps R12

Value Set Basics in Oracle Apps R12

1.Overview

Value-set is a group of values. It can also be thought of as a container of values. The values could be of any data type (Char, Number etc.) A value set is used in Oracle Applications to restrict the values entered by a user. For example, when submitting a concurrent program, we would like user to enter only valid values in the parameter. This is achieved by associating a value set to a concurrent program parameter. 

A Value Set is characterized by value set name and validation. There are two kinds of validations,format validation and Value validation. In the format validation, we decide the data type, length and range of the values. In the value validation, we define the valid values. 

The valid values could be defined explicitly, or could come implicitly from different source (like table, another value-set etc.) 

2. Uses 

Value-set is an important component of Oracle Applications used in defining Concurrent program parameters, Key Flex field and descriptive flex field set of values.

Some of the scenarios where value-set is used are given below:

1. In a concurrent program, we may want users to enter only number between 1 and 100 for a particular parameter.
2. In a concurrent program, we may want users to enter only Yes or No for a particular parameter.
3. Suppose a concurrent program has two parameters. First parameter is department and second parameter is employee name. On selecting a particular department, we want to show only those employee names which belongs to the selected department.
4. In a descriptive flex field enabled on a particular screen, we want to show only a designated list of values for selection by a user.
5. In case of accounting reports, we may want users to enter a range of key flex field values (concatenated segments).

3. Steps To Create Value Set:

Navigation:

Value Set Form:

The various fields are explained below:

Value Set Name         : Any user defined unique name

Description                 : Description of the value set

List type                      : Three choices are available for this field:

·         List of Values

·         Long List of Values

·         Pop-List

Security type              : Three choices are available for this field:

• No Security
• Hierarchical Security
• Non-Hierarchical Security

Format Validation

Format Type

                Possible values for this field are:

                          Char

                          Date

                          Date Time

                          Number

                          Standard Date

                          Standard Date Time

                          Time

Maximum Size        : Maximum size of the value

Precision                  : Applicable when format type is number

Numbers Only         : When this is checked, only numbers are allowed

Upper Case Only     : This is applicable when Format type is Char

Right Justify and Zero-Fill Numbers: Applicable only for Numbers

Min Value                 : Min Value allowed

Max Value                : Max Value Allowed

Value Validation

Possible values of the value validations are as follows:

 None You use a None type value set when you want to allow users to enter any value so long as that value meets the value set formatting rules. That is, the value must not exceed the maximum length you define for your value set, and it must meet any format requirements for that value set.

Independent:

When this is chosen, the individual values are defined using the navigation shown below

Values Screen Navigation:

Values Find Screen:

Dependent:

Same like Independent Value Set, except the List of Values shown to you will depend on which the Independent value you have selected in the Prior Segment. Must define your independent value set before you define the dependent value set that depends on it. Must create at least one dependent value for each independent value

In the above Value set screen, if you choose type as “DEPENDENT” and clicked the “Edit Information” Button, we get the below screen

Table:

Ø      The list of value is created based on database tables.

Ø      Allows to write simple queries, joins, order by etc

Ø      The value, meaning and ID can be used to display a value, description to the value but return ID to the calling program or screen.

Ø      Additional columns can also be displayed.

Ø      Can also create dependent values to filter LOV data based on parameter value selected earlier. This can be done using :$FLEX:.value_set_name in the where clause.

In the above Value set screen, if you choose type as “TABLE” and clicked the “Edit Information” Button, we get the below screen

 Special & Pair Value Sets:

Pair validation value set allows to select a range of concatenated Flex field segments as parameters to the report. The special value set is used to perform special validation. This is used to enter the entire key flex field segment in the single parameter of the report/calling entity.

Special and pair value sets provide a mechanism to allow a "flexfield-within-a-flexfield". These value sets are primarily used for Standard Request Submission parameters. You do not generally use these value sets for normal flex field segments.

Special and Pair value sets use special validation routines you define. For example, you can define validation routines to provide another flexfield as a value set for a single segment or to provide a range flex field as a value set for a pair of segments.

In the above Value set screen, if you choose type as “SPECIAL” and clicked the “Edit Information” Button, we get the below screen

4. FNDLOAD & Value SETS:

FNDLOAD can be used to migrate the value set information from one instance to another and below is the code sample

<-- Command to be executed -->

FNDLOAD $usrname/$Passwd

                   O Y

                   DOWNLOAD

                   $FND_TOP/patch/115/import/afffload.lct

                   file_name.ldt

                   VALUE_SET   FLEX_VALUE_SET_NAME=LEARN_VALUESET

Concurrent Program (CP) Phases

Concurrent Program (CP) Phases

Phase	Phase Description	Phase Status	Phase Status Description
Pending	The Concurrent Program (CP) is waiting to be run.	Normal	The CP is waiting for a concurrent manager (CM)
		Waiting	A child request is waiting for the parent process to let it run.
		Standby	The CP1 (in Standby) is waiting for another CP2 to finish. The CP1 may run only after CP2 complete.
		Scheduled	The execution time for the CP has not been reached.
Running	The CP is running.	Normal	The CP process is running.
		Paused	The parent CP is paused because is waiting for a child process to complete.
		Resuming	After the "Paused" Status when the parent CP restart working.
		Terminating	The CP is being terminated.
Completed	The CP has been finished the execution.	Normal	The CP completed successfully.
		Warning	The CP completed successfully with a warning message.
		Error	The CP failed to complete due to an error.
		Terminated	The CP was stopped before completion.
		Canceled	The CP was canceled before it started  Example: The CP was scheduled for tomorrow but today I cancel the CP.
Inactive	Describes a CP that cannot be run immediately.	Disabled	The CP has not been enabled for execution.
		On Hold	The CP has been placed in on hold.
		No                Manager	There is no managers for that type of request.