Setting Up Employee Self Service V2 Part 4 - Configuring EBS for ICS

Continuing our series on looking into the newest version of Oracle's EBS Mobile Applications,  we now move on to Part III - in which we amend EBS Vision Payroll Configuration so it makes for a reasonable demo.

Create a New EBS User

1. Create a new schema user in EBS and call it XXMC.
2. Grant the user the necessary privileges:

   GRANT CREATE SESSION TO XXMC;
   GRANT CREATE PROCEDURE TO XXMC;
   GRANT CREATE TYPE to XXMC;
   

QL> create user XXMC identified by r1234567;

User created.

SQL> GRANT CREATE SESSION TO XXMC;

GRANT CREATE PROCEDURE TO XXMC;

GRANT CREATE TYPE to XXMC;

Grant succeeded.

SQL>

Grant succeeded.

SQL>

Grant succeeded.

SQL>

This user will be a system user than can access and update employee timecard records in EBS. We'll later give this user the least privileged credentials that are needed for the solution to work (the ability to call the package XXMC_Utilities on Apps.

Create Custom Views and Packages

As the APPS user, run the provided SQL scripts in the following order to create the necessary views and packages:

1. EBS/init/01_create_XXMC_HXC_WORKED_HOURS_V.sql
2. EBS/init/02_create_XXMC_HXC_TC_WORKED_HOURS_V.sql

[oracle@bd8bc7 init]$ sqlplus apps @01_create_XXMC_HXC_WORKED_HOURS_V.sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 11:34:19 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Oct 12 2016 11:30:52 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

View created.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@bd8bc7 init]$ sqlplus apps @02_create_XXMC_HXC_TC_WORKED_HOURS_V.sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 11:34:28 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Oct 12 2016 11:34:20 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

View created.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@bd8bc7 init]$

1. EBS/plsql/XXMC_Utilities.pks
2. EBS/plsql/XXMC_Utilities.pkb

[oracle@bd8bc7 plsql]$ sqlplus apps @XXMC_Utilities.pks

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 11:35:09 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Oct 12 2016 11:34:28 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Package created.

No errors.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@bd8bc7 plsql]$ sqlplus apps @XXMC_Utilities.pkb

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 11:35:15 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Oct 12 2016 11:35:10 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Package body created.

No errors.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@bd8bc7 plsql]$

[oracle@bd8bc7 plsql]$

Note: The XXMC_Utilities package is set up with definer's rights.

Grant the XXMC User Permission to Execute the Newly Created Package

• As the APPS user, run the following command to grant the XXMC user permission to invoke the package you created on Apps:

  GRANT EXECUTE ON APPS.XXMC_Utilities TO XXMC;

SQL> GRANT EXECUTE ON APPS.XXMC_Utilities TO XXMC;

Grant succeeded.

SQL>

Make a note of these least privileged user credentials. These are the database credentials you will be using later on in ICS. These packages give the Integration Cloud Service a standard way to interact with the data in EBS.

Create the Wrapper Types and Package That Can Be Called from ICS

• As the XXMC user, run the EBS/integration/XXMC_ICS_TIMECARD_create.sql script.

Errors for PACKAGE XXMC_ICS_TIMECARD:

LINE/COL ERROR

-------- -----------------------------------------------------------------

3/9      PL/SQL: Declaration ignored

4/25     PLS-00754: illegal reference to editioned object

         APPS.XXMC_UTILITIES

SQL> alter user XXMC enabled editions;

alter user XXMC enabled editions

                *

ERROR at line 1:

ORA-00922: missing or invalid option

SQL> alter user XXMC enable editions;

User altered.

SQL>  select editions_enabled from dba_users where username in ('APPS','XXMC');

E

-

Y

Y

SQL>

drop it and run again

[oracle@bd8bc7 integration]$ sqlplus xxmc/r1234567 @XXMC_ICS_TIMECARD_create.sql

SQL*Plus: Release 12.1.0.2.0 Production on Wed Oct 12 11:45:54 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Last Successful login time: Wed Oct 12 2016 11:45:45 -04:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Type created.

No errors.

Package created.

No errors.

Package body created.

No errors.

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@bd8bc7 integration]$

Note the Database Host, Port, SID (or service name), and Restricted User Credentials

These will be needed for ICS setup.

10.196.134.222
bd8bc7.compute-a423042.oraclecloud.internal
EBSDB
XXMC/R11234567

OK next step is to configure ICS !