Customer Conversion in Oracle Apps

validation:

1)Party Name already exist or not. If party already exist do no load party, just load only address site information.
2)Address validation based on address style setup like address 1 , state , country.
3)Party name should not be null.
4)Account number should not be null.
5)Tax code validation.
6)VAT REGISTRATION number validation.
7)Sales rep validation if field data exist in data file.
8)Price list validation
9)Customer Profile class validation.
10)Address should be either billto or shipto or can be both.

Query to get Source System Id related to Employee in Fusion HCM Cloud

Please change the source system owner for which you want to check data before running below queries.

Person:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP h
,PER_PERSON_NAMES_F_V P
where 1=1
and h.object_name='Person'
and h.source_system_owner='WORKDAY'
and h.surrogate_id=p."PERSON_ID";

PersonName:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_PERSON_NAMES_F_V P
WHERE 1=1
and h.object_name ='PersonName'
and h.surrogate_id=p.person_name_id
and h.SOURCE_SYSTEM_OWNER ='WORKDAY';


Assignment:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_ALL_ASSIGNMENTS_M PA
where 1=1
and H.object_name='Assignment'
and H.source_system_owner='WORKDAY'
and H.surrogate_id=PA."ASSIGNMENT_ID"

Assignment Supervisor:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_ASSIGNMENT_SUPERVISORS_F_V PA
,PER_PERSON_NAMES_F_V PER_PERSON_NAMES_F_V
,PER_ALL_ASSIGNMENTS_M PER_ALL_ASSIGNMENTS_M
where 1=1
and H.object_name='AssignmentSupervisor'
and H.source_system_owner='WORKDAY'
and H.surrogate_id=pa.assignment_supervisor_id
and pa.person_id="PER_PERSON_NAMES_F_V"."PERSON_ID"
and pa.assignment_id="PER_ALL_ASSIGNMENTS_M"."ASSIGNMENT_ID"

Work Term Assignment

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_ALL_ASSIGNMENTS_M PER_ALL_ASSIGNMENTS_M
where 1=1
and H.object_name='Assignment'
and H.source_system_owner='WORKDAY'
and H.surrogate_id="PER_ALL_ASSIGNMENTS_M".work_terms_assignment_id;

Address:

select source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_PERSON_ADDRESSES_V PER_PERSON_ADDRESSES_V
WHERE 1=1
and h.object_name = 'Address'
and h.surrogate_id=PER_PERSON_ADDRESSES_V.address_id
and h.SOURCE_SYSTEM_OWNER ='WORKDAY';

Email Address:

select H.source_system_id
from fusion.HRC_INTEGRATION_KEY_MAP H
,PER_EMAIL_ADDRESSES_V PER_EMAIL_ADDRESSES_V
,PER_PERSON_NAMES_F_V PER_PERSON_NAMES_F_V
WHERE 1=1
and h.object_name = 'EmailAddress'
and h.SOURCE_SYSTEM_OWNER ='WORKDAY'
and h.surrogate_id=PER_EMAIL_ADDRESSES_V.email_address_id
and PER_EMAIL_ADDRESSES_V.PERSON_ID="PER_PERSON_NAMES_F_V"."PERSON_ID"
and TRUNC(SYSDATE) between DATE_FROM and NVL(DATE_TO,SYSDATE+1)

Unicode And Non-Unicode

Unicode : 

A Unicode character takes more bytes to store the data in the database. As we all know, many global industries want to increase their business worldwide and grow at the same time, they would want to widen their business by providing services to the customers worldwide by supporting different languages like Chinese, Japanese, Korean, and Arabic. Many websites these days are supporting international languages to do their business and to attract more and more customers and that makes life easier for both parties.

Non-Unicode :

Non Unicode is exactly the opposite of Unicode. Using non-Unicode it is easy to store languages like ‘English’ but not other Asian languages that need more bits to store correctly otherwise truncation will occur.

Now, let’s see some of the advantages of not storing the data in the Unicode format:

1. It takes less space to store the data in the database hence we will save a lot of hard disk space. 
2. Moving database files from one server to another takes less time. 

What’s the difference between a Unicode and non-Unicode Tools install?

SOLUTION:
If UNICODE_ENABLED=1 on the PSSTATUS table, then you definitely have a Unicode installation. If UNICODE_ENABLED=0, then you do NOT have a Unicode installation.

Non-Unicode	Unicode
(char, varchar, text)	(nchar, nvarchar, ntext)
Stores data in fixed or variable length	Same as non-Unicode
char: data is padded with blanks to fill the field size. For example, if a char(10) field contains 5 characters the system will pad it with 5 blanks	nchar: same as char
varchar: stores actual value and does not pad with blanks	nvarchar: same as varchar
requires 1 byte of storage	requires 2 bytes of storage
char and varchar: can store up to 8000 characters	nchar and nvarchar: can store up to 4000 characters
Best suited for the US English: "One problem with data types that use 1 byte to encode each character is that the data type can only represent 256 different characters. This forces multiple encoding specifications (or code pages) for different alphabets such as European alphabets, which are relatively small. It is also impossible to handle systems such as the Japanese Kanji or Korean Hangul alphabets that have thousands of characters."1	Best suited for systems that need to support at least one foreign language: "The Unicode specification defines a single encoding scheme for most characters widely used in businesses around the world. All computers consistently translate the bit patterns in Unicode data into characters using the single Unicode specification. This ensures that the same bit pattern is always converted to the same character on all computers. Data can be freely transferred from one database or computer to another without concern that the receiving system will translate the bit patterns into characters incorrectly.

PeopleSoft Compare Reports

PeopleSoft Compare Reports

PeopleSoft Compare Reports provide a means to compare PeopleSoft objects present in one database against a different database or a file. This can be done at a Project level or at database level.

Compare reports come in handy during an upgrade. In this post we will discuss on the different statuses that are shown on a PeopleSoft Compare Reports when one project is compared against another database.

Statuses in PeopleSoft Compare Reports

PeopleSoft shows the following statuses on Compare Reports. Each status has it own meaning and significance during an upgrade. Let’s look at each one of them.

Same

The compared definitions are defined the same in both databases that are compared. Such definitions need not be migrated to the other database.

Absent

This status is shown when the definition is present in one of the databases but not in the other. If the target database does not have the definition, it may have to be migrated.

Unknown

This is the default status for all non-comparison definitions. PeopleSoft doesn’t compare the definitions that show this status. They may have to be compared manually or using a non-PeopleSoft utility.

Changed

This status states that there is a change in the definitions that are being compared, across the databases. This also states that the change was carried out by PeopleSoft (Oprid PPLSOFT)

*Changed

This status states that there is a change in the definitions that are being compared, across the databases. But unlike the previous status, in this case, the change was carried out NOT by PeopleSoft.

Unchanged

Unlike the meaning of this status, there were changes found in this case as well. But the date time stamp on such definitions is prior to the release date time stamp on the compared databases. It also states that the change was carried out by PeopleSoft.

*Unchanged

This status is similar in meaning to the above status except that the changes to the objects in this case was carried out NOT by PeopleSoft.

Upgrade Oracle Applications from 11i to R12

Upgrade Oracle Applications from 11.5.10.2 to R12

Version 11.5.10.2
Oracle Applications Vision Instance
OS: Redhat Linux update 5
Type: Single Node instance
Applications OS User: applmgrupd
Installed directory: /d01/oracle/singlenode
Database 9i OS User : oracleupd
9i Database Home = /d01/oracle/singlenode/testdb/9.2.0
10g Database Home = /d01/oracle/singlenode/testdb/10.2.0
Instance SID: TEST
Host: linux1

Task – To Upgrade Oracle Applications 11.5.10.2 to R12



Upgrade path

• Applications R11.5.6 and below requires to first upgrade to R11.5.10.2/10gR2

• Applications R11.5.7 and up can be directly upgraded to R12

• Database in all releases must have been upgraded to 10gR2 (11.5.9.2 / 11.5.10.2) or should be done during upgrade to R12 (11.5.9.1/11.5.10.1 and below


Here are steps to upgrade Oracle Application 11.5.10.2 to R12, let’s split the activity into 6 steps

1> Please do upgrade the database from 9.2.0.6 to 10.2.0.3,

2> Apply the below patches in your existing 11.5.10.2 environment

Shut down the entire E-Business Suite environment and perform a full backup
4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12
5726010 - AD Preparation Scripts for R12

4712852 - Minipack 11i.AD.I.4
5753359 - ALPPNR, NO RESPONSE ACTION PROCESSOR CONC PGM ENDS IN ERROR
*5467526 - OATM MIGRATION UTILITY: ORA-22853 IN ERROR LOG
5120936 - TUMS for R12: TO DELIVER TUMS UTILITY FOR UPGRADES FROM 11I TO R12

sqlplus apps/apps @adtums.sql /usr/tmp

-- Review TUMS report in /usr/tmp

5726010 - AD Preparation Scripts for R12

Enable the maintenance mode

-- Apply patch 5726010
unzip p5726010_11i_GENERIC.zip

cd 5726010/

sqlplus apps/apps @adgncons.sql apps apps
# modify the adcrtbsp.sql to specify the path for new tablespaces

sqlplus system/manager @adcrtbsp.sql

su - oracleupd

mkdir -p $ORACLE_HOME/appsutil/admin
cd $ORACLE_HOME/appsutil/admin
cp /software/upgrades/11.5.10.2toR12/r12/5726010/adgrants.sql .

sqlplus '/ as sysdba' @adgrants.sql applsys

3> Run rapidwiz to create R12 code tree

The installation will install R12 applications components and the database home
Select upgrade to Oracle Applications R12
Select upgrade actions screen
select create upgrade file system

enter the parameters required to set up your new environment run rapid install Here are the screen shot


The Installation creates APPL_TOP, COMMON_TOP, INST_TOP and 10g Oracle Home
Move the new environment file into .bash_profile

su - applmgrupd
cd /d01/oracle/apps/apps_st/appl
mv .bash_profile .bash_profile_11.5.10
cat APPSVIS_linux1.env >> /home/applmgrupd/.bash_profile

4> Apply the below patches in R12 environment

4502962 – R12 Minipack

Now onwards, you are connected to R12 application
-- Apply patch AD.A 4502962


-- Apply patch 4440000
Run the NLS upgrade patch driver (conditional)
• Download the NLS Release 12 patch (4440000) for each active language in the system.
• Run each driver (u4440000.drv) with adpatch
• NLS patch driver has the same name as the American English patch driver.

Synchronize NLS and American English product patches (conditional)
• Generate manifest using perl $AD_TOP/bin/adgennls.pl
• Backup manifest file $APPL_TOP/admin/$TWO_TASK/out/adgennls.txt
• http://updates.oracle.com/TransSync
• Don’t check Translation Level Updates
• Upload manifest
• Download and apply patch

-- Configuration Phase
Disable maintenance mode
Run autoconfig

Run rapidwiz to configure and start processes
Sign on to R12 applications check concurrent managers

Run autoconfig
su - applmgrupd
perl $AD_TOP/bin/admkappsutil.pl
su - oracleupd
cd $ORACLE_HOME
cp /d01/oracle/inst/apps/TEST_linux1/admin/out/appsutil.zip .
unzip -o appsutil.zip
cd $ORACLE_HOME/appsutil/scripts/TEST_linux1
sh adautocfg.sh


5> Configuration Phase – R12

Run rapidwiz to configure and start processes
-----------------------------------------
rapidwiz - Upgrade to Oracle Applications Release 12 -> Upgrade Action -> Select Configure Upgraded

Release 12 instance
$INST_TOP/admin/VIS_linux1.xml
/d01/oracle/inst/apps/TEST_linux1/appl/admin/TEST_linux1.xml




Sign on to R12 applications check concurrent managers





Connect as sysadmin/sysadmin




6> Apply the online help

cd $AU_TOP/patch/115/driver/

Please enter the name of your AutoPatch driver file : u5051400.drv

Gather schema statistics for CBO

Release 12 employs cost-based optimization, which examines FND table statistics to determine the most efficient access paths and join methods for executing SQL statements. These statistics are gathered by the FND_STATS process, which you initiate by running the
Gather Schema Statistics concurrent program.

From your Release 11i APPL_TOP, complete the following steps:
1. Log in to Oracle Applications with the System Administrator responsibility.
2. Navigate to the Submit Request window (Request > Run).
3. Submit the Gather Statistics program. (Schema)