, , , ,

How to Configure Oracle Approval Management (AME) for developer?

Follow the steps below to have access to AME.

Steps to AME configuration for any user:



1) Select the User Management responsibility.
2) Select the Users page.
3) Search for the user to whom you wish to grant AME roles.
4) In the results table, click Update. In the Update User page, you can view user details along with a list of roles available to the user.
5) Click Assign Roles.
6) Select following roles and click Apply.

1) Approvals Management Administrator
2) Approvals Management Analyst
3) Approvals Management System Viewer
4) Approvals Management System Administrator
5) Approvals Management Process Owner

7) Grant data access to users

1) Login as Administrator.
2) Select the Functional Administrator responsibility.
3) Select the Grants tab.
4) Click Create Grant.
5) Select Specific User as grantee type.
6) Select the user as grantee key.
7) Select AME Transaction Types as Object
8) Select AME Calling Applications as Set
9) Follow the screen and click on Finish

8) Run “Workflow Directory Services User/Role Validation”
9) Select Approval Management Business Analyst responsibility and check functionality.

/by
, , ,

Tricks for generating SQL statements in Excel 2003/2007

The semi-colon needs to be inside the last double quote with a closing param. When adding single quotes around a string, remember to add them outside your selected cell.
(spaces added for visibility – remove before inserting)
=CONCATENATE("insert into table (id, name) values (",C2,",' ",D2," ');")
Here is another view:
=CONCATENATE(“insert into table (id, date, price) values (“,C3,”,'”,D3,”‘,”,B3,”);”)
/by
, , , , , ,

How to view AP Invoice Interface from the Oracle R12 Front end?

Let’s say Invoices are imported from External Systems – irrespective of the transportation layer / method. Instead of viewing the Interface tables in the backend – say AP_INVOICES_INTERFACE , AP_INVOICE_LINES_INTERFACE, they can be viewed in the front-end.
Responsibility – Payables Manager
Navigation – Invoices – Entry – Open Interface Invoices

Clicking on the above link, opens the below form ..

Open the Query-mode to view the Invoice in the Interface table

Above is the header line. Click on Lines to view the lines information.

This would avoid looking for Invoices by querying in the back-end.
/by
, , , ,

R12 Clone using rman method

This article details the steps used to clone R12 RAC database to Single instance database using RMAN duplicate command, Notice that we used the control file as a catalog database and the Production will keep up and running.

The databases used here are:
Production: PROD
Cloned: CPCLONE
1- On the source system; Run the preclone script on both database and apps tiers.
Database:
$ cd ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME/
$ perl adpreclone.pl
Application:
$ cd $ADMIN_SCRIPTS_HOME/appsutil/scripts/$CONTEXT_NAME/
$ perl adpreclone.pl
2- Archive and move the Apps tier and Database home form the source to the target server:
For the application tier:
$ cd $APPL_TOP/../../..
$ tar cvf – apps| gzip > apps_PROD.tar.gz
For the database tier
$ cd $ORACLE_HOME/..
$ tar cvf – db | gzip > db_PROD.tar.gz
3- Create full RMAN backup for the source database:
$ export ORACLE_SID=PROD
$ rman target /
run
{
allocate channel d1 type disk;
allocate channel d2 type disk;
backup format ‘/backup/MASTER/ERP/df_prodt%t_s%s_p%p.bak’ database;
sql ‘alter system archive log current’;
backup format ‘/backup/MASTER/ERP/df_archive%t_s%s_p%p.arc’ archivelog all;
release channel d1; release channel d2;
}
4- Move the backup pieces generated from Rman to the target server to the same path or make a soft link as the same backup original path to let Rman see it.
Example $ ln –f -s /u02/CPCLONE/backup /backup/MASTER/ERP.
5- Extract the archive files on the Target servers
For the application tier:
$ tar cvf – apps| gzip > apps_PROD.tar.gz
For the database tier
$ tar cvf – db | gzip > db_PROD.tar.gz
Note: The current path depends on your configuration.
6- On the target system; Run the preclone script on the database tier.
$ cd $ORACLE_HOME/appsutil/clone/bin
$ perl adcfgclone.pl dbTechStack
7- Edit the new created init file under $ORACLE_HOME/dbs to include the following parameters; Note we are using ASM as data storage:
_no_recovery_through_resetlogs=TRUE
db_file_name_convert =(‘+DATA1/prod’, ‘/u01/CPCLONE/oradata’)
log_file_name_convert =(‘+DATA1/prod’, ‘/u01/CPCLONE/oradata’)
and edit the following parameters if not have those values
undo_management =AUTO
undo_tablespace =UNDOTBS1
db_name =cpclone
instance_name =cpclone
8- On the target system; Startup the database on nomount state:
$ export $ORACLE_SID=CPCLONE
$ sqlplus / as sysdba
SQL> startup nomount pfile=initCPCLONE.ora;
9- Make the production database accessible from the clone database:
$ cd $TNS_ADMIN
$ vi tnsnames.ora
And insert the follow entries:
PROD= (
DESCRIPTION=
(
ADDRESS=(PROTOCOL=tcp)(HOST=gfmisdb1)
(PORT=1521))
(CONNECT_DATA=
(SERVICE_NAME=PROD)
(INSTANCE_NAME=PROD1)
)
)

10- Test the connection to the production database:
$ tnsping PROD
$ sqlplus apps/apps@PROD
11- Lunch rman to start the duplicate process:
$ export $ORACLE_SID=CPCLONE
$ rman target sys/sys@prod auxiliary /
run
{
allocate auxiliary channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
duplicate target database to cpclone;
release channel C1;
release channel C2;
}
12- Shutdown the cpclone database and edit the parameter file as:
$ sqlplus / as sysdba
$ shutdown abort;
Edit the following parameters:
undo_management to =MANUAL
undo_tablespace=UNDOTBS1
13- Open the database in with resetlogs option:
$ sqlplus / as sysdba
Sql> startup mount;
Sql> alter database open resetlogs;
Sql> drop tablespace APPS_UNDOTS1 including contents;
Sql> CREATE UNDO TABLESPACE APPS_UNDOTS1 DATAFILE
‘/u02/CPCLONE/oradata/datafile/undo01.dbf’ SIZE 4000M AUTOEXTEND
ON NEXT 100M MAXSIZE 7000M
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON;
Then shutdown the database
Sql> shutdown immediate;
14- Edit the parameter file:
Remove _no_recovery_through_resetlogs=TRUE and edit
undo_management=AUTO
undo_tablespace=APPS_UNDOTS1
15- Change the database mode to be in no archive log mode:
$ sqlplus / as sysdba
Sql> startup mount;
Sql> alter database noarchivelog;
Sql> alter database open;
Execute the following script
Sql> @ $ORACLE_HOME/appsutil/install/[CONTEXT NAME]/ adupdlib.sql so
Sql> create spfile from pfile.
Sql> shutdown immediate;
Sql> startup;
16- After the database opened; execute the following script:
cd $ORACLE_HOME/appsutil/clone/bin
perl adcfgclone.pl dbconfig $ORACLE_HOME/appsutil/$CONTEXT_NAME.xml
17- On the application tier run the following script:
cd $COMMON_TOP/clone/bin
perl adcfgclone.pl appsTier
/by
, , , ,

Change Applications Tier hostname using AutoConfig

1. Deregister the current Applications server (Required)
De-register the current Applications server node, run the following command as the owner of the Oracle Applications file system and current database instance as follow:
perl $AD_TOP/bin/adgentns.pl appspass= contextfile= -removeserver
2. Shutdown the Applications Tier Services (Required):
$COMMON_TOP/admin/scripts//adstpall.sh apps

3. Change the hostname as required:
Change the hostname in /etc/hosts file
4. Change the hostname as required:
Change the hostname in /etc/hosts file
5. Reboot the Node
/by