Category Archives: Blog

Create View and Datatypes

How easy it is to forget!

On creating a view with a literal (alphanumeric) value, Oracle will use the CHAR datatype.

 SQL> create table t1 (id number, val varchar2(20));

Table created.

SQL> create view v1 as select id, val, 'Tweedle' dum from t1;

View created.

SQL> desc v1;

Name Null? Type

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

ID                   NUMBER

VAL                VARCHAR2(20)

DUM             CHAR(7)

 

This is no problem, since the value is a constant. But what if we mix the datatypes?


SQL> create view v2 as select id, val, 'Doodle' dum from t1;

View created.

SQL> desc v2;
Name Null? Type
--------- -------- ---------------

ID                    NUMBER
VAL                 VARCHAR2(20)
DUM               CHAR(6)

SQL> create view v3 as select * from v1 union select * from v2;

View created.

SQL> desc v3;
Name Null? Type
--------- -------- ---------------

ID                   NUMBER
VAL                VARCHAR2(20)
DUM              VARCHAR2(7)

Oracle automagically changed the datatype to VARCHAR2. (This is also documented in the SQL Reference for UNION (ALL). But it can still lead to some funny results:


SQL> create table t2 (val char(7));

Table created.

SQL> insert into t2 values ('Doodle');

1 row created.

SQL> select * From v3 where dum in (select val from t2);

no rows selected

SQL> select * From v2 where dum in (select val from t2);

ID VAL DUM
---------- -------------------- ------
1 Test Doodle

SQL> select val||'x' from t2;

VAL||'X'
--------
Doodle x

Never a dull moment with Oracle and creative developers 🙂

FND_GLOBAL.APPS_INITIALIZE: Initialize eBS sessions in SQL and PL/SQL

To access eBS functionality through SQL clients or PL/SQL, it can be necessary to initialize an eBS session.

For example when you need to reference eBS views, access API’s or start concurrent requests from outside eBS.

To initialize an eBS session the API FND_GLOBAL.APPS_INITIALIZE can be used. It has 3 required parameters and 2 optional parameters:
USER_ID -> This is the FND_USER.USER_ID for which is a session is initialized.
RESP_ID -> The FND_RESPONSIBILITY.RESPONSIBILITY_ID that will be used in this session.
RESP_APPL_ID -> The FND_RESPONSIBILITY.APPLICATION_ID for the RESPONSIBILITY_ID.
SECURITY_GROUP_ID -> An optional SECURITY_GROUP_ID. (Default = 0)
SERVER_ID -> An optional SERVER_ID. (Optional, default = -1)

The SERVER_ID will mostly be used in case of a DMZ or server dependent services.
The APPS_INITIALIZE procedure will accept any numeric value for the parameters. But the session will only be initialized successfully with at least a valid USER_ID.

In this article, we will look at the session initialization through APPS_INITIALIZE. The following steps are executed:
– Set up a PL/SQL table in FND_GLOBAL with some session variables. These variables are accessible through functions in FND_GLOBAL.
– Set up a sys_context (‘FND’) with the same variables.
– Set the organization context.
– Set up the Security Profile in case of HR Security. I will document the details of the HR Security later and skip the details in this article.
– Set the Consumer Resource Group.
– Execute product specific initialization routines.
– Execute the Custom SQL Initialization routine.

FND_GLOBAL

In the FND_GLOBAL package body a PL/SQL table is set up to hold session variables. Not all available variables will be set through APPS_INITIALIZE (e.g. CONC_REQUEST_ID that is only set on submitting a concurrent request). And not all variables are available (e.g. CUSTOMER_ID which is derived from the customer field in FND_USER).

Undefined variables are set to -1 for NUMBER values and to NULL for VARCHAR values.

Name Description Remarks
USER_ID User_id from FND_USER Required, not validated
USER_NAME User_name from FND_USER Optional, based on USER_ID
RESP_ID Responsibility_id Optional, not validated
RESP_NAME Responsibility_name Optional, based on RESP_ID
RESP_APPL_ID Resp. application_id Optional, based on RESP_ID
APPLICATION_NAME Resp. application_name Optional, based on RESP_APPL_ID
APPLICATION_SHORT_NAME Resp. application_short_name Optional, based on RESP_APPL_ID
SECURITY_GROUP_ID Security Group ID Optional, validated
PER_BUSINESS_GROUP_ID Business Group ID Derived from Profile Option
PER_SECURITY_PROFILE_ID HR Security Profile Derived from Profile Option
CURRENT_LANGUAGE From userenv(‘LANG’)
BASE_LANGUAGE Base Language From FND_LANGUAGES
EMPLOYEE_ID User employee_id Optional, from FND_USER
CUSTOMER_ID User customer_id Optional, from FND_USER
SUPPLIER_ID User supplier_id Optional, from FND_USER
SESSION_ID Unique session identifier AUDSID from v$session
SERVER_ID Server_id if entered as param. Optional from FND_NODES
ORG_ID Operating Unit ID Derived from Profile Option
ORG_NAME Operating Unit Name Based on ORG_ID
PARTY_ID User party_id FND_USER.PERSON_PARTY_ID
NLS_LANGUAGE From V$NLS_PARAMETERS
NLS_NUMERIC_CHARACTERS From V$NLS_PARAMETERS
NLS_DATE_FORMAT From V$NLS_PARAMETERS
NLS_DATE_LANGUAGE From V$NLS_PARAMETERS
NLS_TERRITORY From V$NLS_PARAMETERS
NLS_SORT From V$NLS_PARAMETERS

These variables are also stored in a SYS_CONTEXT with name ‘FND’:

SQL> select FND_GLOBAL.USER_NAME from dual;

USER_NAME
 -------------------------------------------
 SYSADMIN

SQL> select sys_context('FND','USER_NAME') from dual;

SYS_CONTEXT('FND','USER_NAME')
 --------------------------------------------------------
 SYSADMIN

 

There will however be NO noticeable performance difference between the two. So I advise you to use FND_GLOBAL as it is the documented solution.

Organization Context

The algorithm to derive the Organization Context is as follows.

For pre-R12 environments: Use profile option ‘MO: Operating Unit’ (ORG_ID).

For R12 and beyond, the MOAC structure can be used. First check if profile option ‘MO: Security Profile’ (XLA_MO_SECURITY_PROFILE_LEVEL) is set.
If this profile is set, check profile option ‘MO: Default Operating Unit’ (DEFAULT_ORG_ID).
If DEFAULT_ORG_ID is not set, the ORG_ID will not be initialized.
If XLA_MO_SECURITY_PROFILE_LEVEL is not set, profile option ‘MO: Operating Unit’ (ORG_ID) is used.

The ORG_ID and SECURITY_GROUP_ID are then stored in the sessions CLIENT_INFO. ORG_ID starts at position 1 and is limited to 10 characters. The SECURITY_GROUP_ID starts at position 55.

The CLIENT_INFO is the most common check for eBS database views to verify Organization Context.

Profile Option Cache

After setting the variables, the Profile Option Value cache is initialized. However APPS_INITIALIZE does not set any profile option values yet. It only sets up the required cache (which is also a PL/SQL table).
A number of profile options are used for the initialization, but they are not yet stored in the Profile Option Value cache. In an upcoming article, I’ll go into more detail about the Profile Option Value Cache and the way eBS uses it

Security Context

Then the security context is initialized. The ORG_ID and SECURITY_GROUP_ID are set in the sessions CLIENT_INFO. ORG_ID is stored in the first 10 characters, while SECURITY_GROUP_ID is stored from the 55th position.

Resource Consumer Group

In the profile option ‘FND: Resource Consumer Group’ (FND_RESOURCE_CONSUMER_GROUP) a Resource Consumer Group can be selected. With this setting the database Resource Manager can limit the database resources that the user can use. If this profile option is set, APPS_GLOBAL.APPS_INITIALIZE will alter the session to use the required Resource Consumer Group.

Product Initialization

Individual Oracle Products can execute their own initialization routines. The defined routines are in table FND_PRODUCT_INITIALIZATION. Each initialization can be executed based on specific conditions. These conditions are stored in FND_PRODUCT_INIT_CONDITION.
The setup of these initialization routines is done through the package FND_PRODUCT_INITIALIZATION_PKG. The APPS_INITIALIZE will check the conditions and dependencies on the initialization routines and execute the routines in the required order.

Custom Initialization

You may be aware that you can also set up your own Custom Initialization Routine through the profile option: ‘Initialization SQL Statement – Custom’. If this profile is set, it will also be executed through FND_GLOBAL.APPS_INITIALIZE.

APPS_INITIALIZE has now finished initializing your session. Other eBS software can use the FND_GLOBAL functions, the SYS_CONTEXT or CLIENT_INFO to verify the session and security settings.

 

P.S. For quick scripts: stiffmaster.nl

UKOUG Apps 2017

Just a short note to say that I am proud to be selected as a speaker for UKOUG Apps 2017!

I will even give 2 presentations. On Monday I will present ‘Customizing Autoconfig/RapidClone’. Then on Tuesday I will present ‘Auditing the Oracle eBusiness Suite’.

The first presentation is about further optimizing the Autoconfig and RapidClone processes. Oracle allows users to customize these processes to match their specific requirements / environments. This allows for a very flexible process. Especially since you can add your own parameters to the XML Config file. Then you can add additional templates, or customize the existing templates with (conditional) changes / additions.

In this presentation, I will walk you through the whole process of customizing Autoconfig based on a fictional post-clone script that we’ll incorporate in the Autoconfig process.

The second presentation is of course about Auditing eBusiness Suite. We’ll go over the different options (the eBS provided signon Audit Trail and transaction Audit Trail, database audit trail, audit vault, OS level auditing). Each auditing option has a different purpose. And you will need different options to provide a meaningful and comprehensive audit trail.

One presentation is too short to build a full auditing solution. But I’ll try to give you a headstart on designing your own auditing.

 

eBS Flexfields and Canonical values

CANONICAL VALUES

 

Since I have been working a lot on NUMBER and DATE conversions lately, it’s time to write a short note about canonicals.

Oracle eBS Flexfields (and by extension concurrent program parameters) store all values in a VARCHAR2 column. In general it is a bad idea to store NUMBER and DATE in a VARCHAR2 type. See Datatypes and ORA-1722

 

But for Flexfields we have a controlled environment. Oracle solves problems related to conversions by storing the NUMBER and DATE format as so called ‘canonicals’. This means that all NUMBER and DATE values are stored in a fixed format. It is up tot he application to guarantee that only values in this canonical format are stored in the Flexfields.

Customizations

Especially for DFF values, they will often be used in customized software units. These customizations are also responsible for keeping the data in the canonical format. Best practice is to use the packages FND_NUMBER and FND_DATE. This will assure that your customizations don’t break when Oracle changes the canonical format. (e.g. change the precision on NUMBER).

 

FND_NUMBER

The FND_NUMBER package contains 2 procedures: NUMBER_TO_CANONICAL and CANONICAL_TO_NUMBER. Both take 1 parameter which is a NUMBER respectively a VARCHAR2.

The NUMBER canonical format is ‘FM999999999999999999999.99999999999999999999’. Those are 21 positions left and 20 positions right of the decimal ‘.’. Both functions round the result to 20 decimal positions.

For some reason FND development used a TRANSLATE statement to switch between te decimal separator fort he session and the ‘.’ used in the canonical format. (Hint: TO_NUMBER and TO_CHAR (number) accept the parameter ‘nls_numeric_characters’).

Example:

SQL> alter session set nls_numeric_characters=',.';

Session altered.



SQL> select fnd_number.number_to_canonical(to_number('12345,12')) from dual;

FND_NUMBER.NUMBER_TO_CANONICAL(TO_NUMBER('12345,12'))

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

12345.12



SQL> select fnd_number.canonical_to_number('12345.12') from dual;

FND_NUMBER.CANONICAL_TO_NUMBER('12345.12')

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

12345,12



SQL> alter session set nls_numeric_characters='.,';

Session altered.



SQL> select fnd_number.number_to_canonical(to_number('12345.12')) from dual;

FND_NUMBER.NUMBER_TO_CANONICAL(TO_NUMBER('12345.12'))

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

12345.12

 

FND_DATE

The FND_DATE package contains a lot of procedures for backward compatibility. But for canonical values in Flexfields we need CANONICAL_TO_DATE and DATE_TO_CANONICAL.

Since to TO_CHAR(date) and TO_DATE both accept a date mask, the procedure only contain a TO_CHAR/TO_DATE function.

The canonical mask for DATE is: ‘YYYY/MM/DD HH24:MI:SS’.

Example:

SQL> alter session set nls_date_format='DD-MON-YYYY HH:MI:SSAM';

Session altered.



SQL> select fnd_date.date_to_canonical(to_date('5-apr-2017 3:21:45PM','dd-mon-yyyy hh:mi:ssam')) from  dual;

FND_DATE.DATE_TO_CANONICAL(TO_DATE('5-APR-20173:21:45PM','DD-MON-YYYYHH:MI:SSAM'

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

2017/04/05 15:21:45



SQL> select fnd_date.canonical_to_date('2017/02/03 21:32:54') from dual;

FND_DATE.CANONICAL_TO_

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

03-FEB-2017 09:32:54PM

 

More goodies

Two other functions that are convenient to use when dealing with string values and dates are STRING_TO_DATE and STRING_TO_CANONICAL.

For dates MONTH, AM/PM and AD/BC values are language dependent. So based on the session settings they may return different results. STRING_TO_DATE and STRING_TO_CANONICAL handle this by trying different language settings. In order they try NLS_DATE_FORMAT (default), NLS_NUMERIC_LANGUAGE and the installed eBS languages from FND_LANGUAGES.

Both take a string that represents a date and the date-mask for the string. STRING_TO_DATE returns a DATE value, while STRING_TO_CANONICAL uses STRING_TO_DATE but converts the result to a canonical VARCHAR2.

Example:

SQL> alter session set nls_date_language=AMERICAN;

Session altered.



SQL> select nls_language

2  from fnd_languages

3  where installed_flag in ('B','I');

NLS_LANGUAGE

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

DUTCH

AMERICAN



SQL> select to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;

select to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual

*

ERROR at line 1:

ORA-01843: not a valid month



SQL> select fnd_date.string_to_date('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;

FND_DATE.STRING_TO_DAT

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

03-MAR-2017 11:45:12PM



SQL> select fnd_date.string_to_canonical('3-mrt-2017 23:45:12','dd-mon-yyyy hh24:mi:ss') from dual;

FND_DATE.STRING_TO_CANONICAL('3-MRT-201723:45:12','DD-MON-YYYYHH24:MI:SS')

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

2017/03/03 23:45:12

 

HTH. As always, feel free to leave comments and questions.

NLS_DATE_LANGUAGE and ORA-1856

Trying to write some information about the DATE datatype, I ran into the following problem:

SQL> insert into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC'));
insert into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC'))
*
ERROR at line 1:
ORA-01856: BC/B.C. or AD/A.D. required

Also different varieties (B.C. , bc, AD, A.D.) with or without a space, with different seperators all gave the same ORA-1856: BC/B.C. or AD/A.D. required.

Excuse me. That is what I just entered. What is going on here?

Enter the parameter NLS_DATE_LANGUAGE.

From the Database Globalization Support Guide:

NLS_DATE_LANGUAGE also determines the language used for:

  • Month and day abbreviations returned by the TO_CHAR and TO_DATE functions
  • Month and day abbreviations used by the default date format (NLS_DATE_FORMAT)
  • Abbreviations for AM, PM, AD, and BC” (emphasis added)

 

The abbreviation that you can use in to_date for AD/BC is dependent on the parameter NLS_DATE_LANGUAGE. Accidentally my setting was DUTCH. Let’s change it to AMERICAN:

SQL> alter session set nls_date_language=AMERICAN;

Session altered.

SQL>

SQL> insert all into ot_dt values (to_date('1-jan-4712 BC','dd-mon-yyyy BC'))
2 into ot_dt values (to_date('1-jan-2017 AD','dd-mon-yyyy BC'))
3 select * from dual;

2 rows created.

SQL> select to_char(dummy,'dd-mon-yyyy B.C.') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYY
-------------------------
01-jan-4712 B.C.
01-jan-2017 A.D.

SQL> alter session set nls_date_language=DUTCH;

Session altered.

SQL> select to_char(dummy,'dd-mon-yyyy BC') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYYYB
---------------------------
01-jan-4712 V.CHR.
01-jan-2017 N.CHR.

SQL> select to_char(dummy,'dd-mon-yyyy B.C.') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYYYB
---------------------------
01-jan-4712 V.CHR.
01-jan-2017 N.CHR.

SQL> select to_char(dummy,'dd-mon-yyyy bc') from ot_dt;

TO_CHAR(DUMMY,'DD-MON-YYYYB
---------------------------
01-jan-4712 v.Chr.
01-jan-2017 n.Chr.

The values for Dutch AD/BC are V.CHR/N.CHR or in lowercaps v.Chr. and n.Chr.

And that is also what you can use in your INSERT:

SQL> insert into ot_dt values (to_date('1-jan-4712 v.Chr.','dd-mon-yyyy bc'));

1 row created.

 

Good to know, #LearnSomethingNewEveryDay