Several years back Oracle published a whitepaper on the topic “Check Printing with XML Publisher”.  It provides step by step instructions on how to install MICR Fonts, design a check template, link it to a concurrent program and print checks.  Since then there have been several releases of XML Publisher each with new enhancements.  One of the latest enhancements in XML Publisher (Now called BI Publisher) provides even an easier way to use MICR Fonts.  I hope to have a draft of a new whitepaper that details out all of the steps for check printing specifically to Oracle EBS R12 in the new future.  Keep on eye out on the Centroid Blog for it.  In the meantime I will provide some insight on how to install and use MICR Fonts for check printing.

I am making the assumptions that you already have a check template to use, saved a copy of the xml output from a check run, have BI Publisher desktop installed on your pc,  using MS Word to update the template, configured R12 to be able to generate and print pdf and are familiar with BI Publisher steps in creating data sources and uploading your templates.  I will not cover the steps necessary to input an invoice and generate the payment instructions only the steps necessary for proper linkage and getting the check to print with MICR Fonts.

Now let’s get started.

Steps:

1. Acquire a MICR Font: Commercial and Freeware fonts can be used
2. Install the MICR Font on your pc.  The font should be placed in the directory c:\windows\fonts  This will allow you to use it with MS Word
3. Open your check template using MS Word, place the MICR strings and fields where necessary.  Highlight the fields and change the font to the MICR font installed earlier
4. The next 5 steps require you to use the XML Publisher Administrator responsibility in R12
5. Create your data definition using XML Publisher Administrator responsibility
6. Upload your check template into XML Publisher and link it to the new data definition you just created
7. Upload the MICR Font to XML Publisher by creating a new font file
8. Create a new font mapping and link the MICR Font to this new mapping
9. Change the configuration for the FO Processing property and add the new font mapping set you just created.  You can do this for the site level or set it for the data definition level
10. Change responsibility to Payments Setup Administrator and complete the setups to use Payments.  Specifically under formats, create a new format and link it to the new template you have just uploaded.  I wont go into detail about the other steps necessary to complete the payment administration setups you can refer to the user guide for this

You are now ready to test out check printing with MICR Fonts in R12.

In an upcoming blog I will address how to include signature(s) on the check and again all without having to have additional hardware or software.  The wonderful power of XML Publisher.  Stay tuned.

My day typically starts at around 7:15 am.   I read through email and respond to messages that have come in overnight from clients in Asia and Europe.   My next task is to check in with my leads via Instant Messenger to see how they are doing and to find out if there are any development or support situations I need to know about.   Once that is taken care of I check in with clients  (usually via IM or phone) to see if they have any concerns or any issues they want to discuss.  On average, I spend 4-6 hours per day in meetings and phone calls either with or pertaining to our clients.  I have blocks of time allocated to analyzing data and metrics to ensure that we are meeting our service level agreements with our client, identify any process improvements, or to spot issues or trends that need to be reviewed.  I allocate a few hours every week to focus on Centroid internal processes and process improvements, usually working with the partners or my team leads.  Each day I schedule one hour to exercise.  I usually wrap up around 5:30 PM, but usually have meetings later in the evening at least once a week.

In the current economy you’re probably looking under every rock to see where you can cut costs and software support and maintenance fees may be something for you to consider. These fees can be significant. Take your Oracle support fees for example, roughly 22% of original license fees paid annually.

If it’s been a while since you last reviewed your support agreements, start there. You may be paying support fees for applications you no longer use. Those should be a no-brainer, get them on the chopping block ASAP. Others may not be so straightforward. A common example are those modules you purchased licenses for, but shelved for whatever reason. They were cut from the scope of the original implementation or maybe they were purchased with thoughts of using down the road. Now you are paying support fees for something you’re not using and may or may not use in the future. What should you do now?

If you’re considering terminating the support on these modules, there are a few things to keep in mind. Oracle requires that you drop the software licenses when you stop paying for annual support, which means, when/if you decide to implement those modules down the road, you’ll need to purchase new licenses. Oracle will also hit you up with reinstatement fees including retroactive support and maintenance fees and additional penalties. So keep this in mind when kicking around the options.

So I guess the answer to my original question is….it depends. It depends on many factors that you’ll need to consider before making any changes to your support agreements. Before making any changes, review your current agreements and read over the latest cancellation policies and do what makes sense for you.

 The following SQL shows you the top resource-consuming pieces of SQL in your instance:

select ash.SQL_ID ,
     sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
     sum(decode(ash.session_state,'WAITING',1,0))    -
     sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0))    "WAIT" ,
     sum(decode(ash.session_state,'WAITING', decode(en.wait_class, 'User I/O',1,0),0))    "IO" ,
     sum(decode(ash.session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash,
       v$event_name en
where SQL_ID is not NULL
  and en.event#=ash.event#
group by sql_id
order by sum(decode(session_state,'ON CPU',1,1))   desc 

As DBAs, we’re faced with this question all the time.  In order to quickly supply an accurate answer to this question, an experienced Oracle DBA needs to have a few tools in his belt - and I’m not talking about any special software or monitoring solutions, I’m talking simply SQL*Plus scripts and access to a database account with access to the V$ views.

Here’s what I do when someone asks me this question:

Step #1:

Take a look at V$SESSION_WAIT.  This will show you details about sessions currently and actively waiting on named Oracle wait events.  More often than not, if things are “slow”,  a session or sessions is waiting on an instrumented Oracle wait event.  The query I use to do this (works on 9i-11g) is below:
select     A.sid,
decode(A.event,'null event','CPU Exec',A.event) WaitEvent,
decode(A.event,'slave wait','N/A',
'PX Deq: Execution Msg','N/A',
'PX Deq Credit: send blk','N/A',
'latch free','N/A',
'enqueue',
chr(bitand(A.p1,-16777216)/16777215)||chr(bitand(A.p1,16711680)/65535),
'file open','-1',to_char(A.p1)) p1,
decode(A.event,'enqueue',decode(mod(A.p1,16),'6','ROW-LOCK','4','ITL','3',
'FK?','OTHER'),
'file open',
-1,
A.p2) p2,
decode(A.event,'latch free','N/A','enqueue',null,'PX qref latch','-1',
'buffer busy waits',to_char(A.p3), A.p3) p3,
decode(A.state,'WAITING','WTG',
'WAITED UNKNOWN TIME','UNK',
'WAITED SHORT TIME','WST',
'WAITED KNOWN TIME','WKT') wait_type,
decode(A.state,'WAITING',A.seconds_in_wait,
'WAITED UNKNOWN TIME',-999,
'WAITED SHORT TIME',A.wait_time,
'WAITED KNOWN TIME',A.WAIT_TIME) wt,
round((last_call_et/60),2) lc,
substr(nvl(b.module,b.program),1,15) pgm
from    v$session_wait A,
v$session B
where  A.event not in ('Queue Monitor Slave Wait','wait for unread message on broadcast channel','Queue Monitor Wait','jobq slave wait','queue messages','SQL*Net message to client','Null event','rdbms ipc message','i/o slave wait','io done')
and A.event <> 'pipe get'
and A.event not like '%akeup%'
and A.event not like 'Streams AQ%'
and A.state in ('WAITING','WAITED KNOWN TIME')
and A.sid=B.sid
and B.status='ACTIVE'
order by 1
/

Sample output is below (you’ll have to set column headings and other SQL*plus formatting options, but you get the point):

Sid Wait Event                     P1         P2         P3         Typ     Time       last call What
------ ------------------------------ ---------- ---------- ---------- --- -------- ---------------
4518 gc buffer busy                 24         38019      65537      WTG        0             .00
4519 gc buffer busy                 24         38019      65537      WTG        0             .00 XXVG_INV_PICKLI
4680 gc buffer busy                 24         38019      65537      WTG        0             .00
4830 gc buffer busy                 24         38019      65537      WTG        0             .00 FNDRSSUB
4886 smon timer                     300        0          0          WTG       29        18447.47 oracle@usplsvpe
4887 control file parallel write    2          4          2          WTG        0        18447.47 oracle@usplsvpe
4893 gcs remote message             24         0          0          WTG        0        18447.47 oracle@usplsvpe
4895 gcs remote message             24         0          0          WTG        0        18447.47 oracle@usplsvpe
4896 ges remote message             64         0          0          WTG      152        18447.47 oracle@usplsvpe
4899 DIAG idle wait                 1          1          200        WTG  1106848        18447.47 oracle@usplsvpe
4900 pmon timer                     300        0          0          WTG      680        18447.47 oracle@usplsvpe

In this output, you’ll see a handful of sessions waiting on “gc buffer busy” wait events.  At this point, it’s time for the Oracle DBA to study up on what the wait events mean; in this case, sessions are waiting on RAC-related global buffer busy waits, which means that blocks are being used and are pinned in another instance’s cache.   I won’t go into a description on what all the wait events mean here - you can look them up at any of the following URLs:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents.htm#REFRN101
http://download.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm#BGGIBDJI
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/instance_tune.htm#i22670
http://www.adp-gmbh.ch/ora/tuning/event.html
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=34405.1
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=NOT&p_id=62172.1

At this point,  you know who’s waiting on what and you can use the output to look for anomalies for the current environment.  A couple of things to note:

• There are a handful of common wait events in any “busy” Oracle environment; specifically, “db file sequential read”, “db file scattered read”, latch-related, enqueue-related (locks), etc.  You should be familiar with what types of waits are “normal” for a given system.
• You should become familiar with the relative quantity of each type of wait for each system at various times during the day.  For example, at client A, with a new implementation, low transaction volume, not many users, you may never see more than a handful of I/O-related waits at any given time.  At this client, if you see several dozen sessions waiting on the same type or class of wait event, it’s probably a cause for concern.  At a different client, it may be typical to see 20 or 30 I/O-related waits at any given time.  Bottom line is this - you need to have familiarity with the system you’re monitoring.
• Any DBA with his salt should become study Oracle’s wait interface and become familiar with what each of the major wait events means
• You can use Centroid’s “CCEO Infra Wait Interface.ppt” document as a quick reference on the wait interface

Step #2:

Grab the SQL for the sessions that show up repeatedly and frequently in the output from the above query.  Note the SID (Session Identifier) and use it as input to the following script:

select
t.sql_fulltext ,
t.buffer_gets, t.disk_reads,t.executions
from v$session s,
v$sql t
where s.sql_address =t.address and s.sql_hash_value =t.hash_value
and s.sid = &&1
/

Format the out of this if you plan on running an execution plan on it.

If you want additional detail about the session(s) from V$SESSION_WAIT, you can query V$SESSION.

Step #3:

If the SQL statements extracted from the previous step are waiting on I/O-related or contention-related waits,  you should grab an execution plan/explain plan by taking the formatted SQL and plugging into the below script:

set lines 120
explain plan for
<< insert SQL here >>
select * from table(dbms_xplan.display(null, null,'all'));

Step #4:

If the slowness is related to, for example, locks (enqueue waits), find out who the lock holder(s) is by querying V$LOCK or DBA_WAITERS and make a judgement call as to whether to kill the session(s) holding the lock, communicate with the end-user, etc.

Step #5:

Fix it.  This could be a quick-fix (resolving a lock), or more likely will take some time to assess.  If your cause of slowness is I/O-related waits, for example, you need to determine whether the SQL is optimized, whether indexes will help, whether concurrency patterns are abnormal (i.e., are there 50 simultaneous executions of a batch job that should only be running serially?), etc.  SQL optimization is a science in itself that requires knowledge of the underlying data structures and data volumes, as well as an understanding of Oracle’s optimizer.

Step #6:

What if V$SESSION_WAIT doesn’t tell you anything meaningful?  This is when you should consult ASH (Active Session History) views to give you time breakdown details:
select
decode(nvl(to_char(s.sid),-1),-1,'DISCONNECTED','CONNECTED')
"STATUS",
topsession.sid             "SID",
topsession.program                  "PROGRAM",
max(topsession.CPU)              "CPU",
max(topsession.WAIT)       "WAITING",
max(topsession.IO)                  "IO",
max(topsession.TOTAL)           "TOTAL"
from (
select * from (
select
ash.session_id sid,
ash.session_serial# serial#,
ash.user_id user_id,
ash.program,
sum(decode(ash.session_state,'ON CPU',1,0))     "CPU",
sum(decode(ash.session_state,'WAITING',1,0))    -
sum(decode(ash.session_state,'WAITING',
decode(wait_class,'User I/O',1, 0 ), 0))    "WAIT" ,
sum(decode(ash.session_state,'WAITING',
decode(wait_class,'User I/O',1, 0 ), 0))    "IO" ,
sum(decode(session_state,'ON CPU',1,1))     "TOTAL"
from v$active_session_history ash
group by session_id,user_id,session_serial#,program
order by sum(decode(session_state,'ON CPU',1,1)) desc
) where rownum < 10
)    topsession,
v$session s,
all_users u
where
u.user_id =topsession.user_id and
/* outer join to v$session because the session might be disconnected */
topsession.sid         = s.sid         (+) and
topsession.serial# = s.serial#   (+)
group by  topsession.sid, topsession.serial#,
topsession.user_id, topsession.program, s.username,
s.sid,s.paddr,u.username
order by max(topsession.TOTAL) desc
/

The output may look like this:

STATUS         Sid PROGRAM                           CPU    WAITING    IO  TOTAL
------------ ----- ------------------------- ----------- ---------- ----- ------
DISCONNECTED  4518                                 11220          9    83  11312
CONNECTED     4584                                  9620         25    50   9695
DISCONNECTED  4683 das@usplsvpba002.verigy.n        5598        258   735   6591
et (TNS V1-V3)

DISCONNECTED  4614                                   158         75   552    785
DISCONNECTED  4698 sqlservr.exe                       52         20   695    767
DISCONNECTED  4491 sqlservr.exe                      102         35   496    633
DISCONNECTED  4698 sqlservr.exe                       14          9   578    601

You can use the methods in Steps 2-3 above to get details about the sessions above.

Step #7:

If nothing stands out at this point, consult system logs and Oracle alert logs, as well as O/S performance tools (sar, top, glance, etc)

As a DBA, how many times have you been asked… “what happened at 3:00am today, everything slowed down?”  Try answering this question with V$SESSION_WAIT – you can’t.  Try answering by looking at AWR, Automatic Workload Repository, or Statpack reports – you may be able to make some guesses, but they’d only be guesses.  Try answering with data in the alert log or data in O/S logs – you’ll only be able to answer this question if you get very lucky.  Try answering by looking at Oracle Enterprise Manager Grid Control 10g.  Well actually, this gets you on the right track, because Grid Control will show you ASH, Activity Session History data!

ASH is built into the kernel in Oracle 10g and MMON and MMNL do the work!  ASH collects data every second from V$SESSION, V$SESSTAT, V$SESSION_WAIT, etc, and populates V$ACTIVE_SESSION_HISTORY.  An hour of data is kept in memory for easy access, just query V$ACTIVE_SESSION_HISTORY (think of it as V$SESSION_WAIT plus some more stuff, but longer lasting).  Data is flushed to disk every hour and externalized via views WRH$_<ASH name>.  The collection interval and volume self-adjusts based on load.  ASH will collect more aggressively when the system is busy, less so when not.

In short, ASH gives you a way to use all your “right now” tuning methods for time periods in the past.  When someone asks you what happened at X:00AM when you were fast asleep, you have an option now.  Give it a try!

Look for deeper dives into ASH in future posts.  Stay tuned!

With the release of R12, Oracle has released some new features on how data is accessed through the applications and standard programs by the use of Multiple Organization Access Control (MOAC) . MOAC allows you to create a security group which can contain many operating units and assign that to the User’s responsibility. All the forms that process OU striped data now allow you to pick an OU to work in from a list that contains all the OU you have access to. You will also find all the OU based reports have a parameter added for OU.

The Set Up is straight forward. You can define a security profile in the HR Security Profile form, adding Operating Units to it, then you must run Security List Maintenance program before you can assign the security profile to the profile option MO: Security Profile for a responsibility.

MOAC is initialized when you open a Form, OA page or a Report. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operations units to which access is allowed is fetched and the list of values (LOV) is populated. Then default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”. This is how MOAC works in Release 12 when the value of “MO: Security Profile” is set.

When the profile “MO: Security Profile” does not have a value MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set. This enables us to use both R12 MOAC behavior and 11i behavior simultaneously in R 12. You can also choose to completely use one of them.

How Does the R12 MOAC Defaulting Rules Work?

1. If the profile option “MO: Security Profile” is not set, then “MO:
   Operating Unit” value is used as the default Operating Unit even if “MO:
   Default Operating Unit” profile is set to a different value.
2. If the profile option “MO: Security Profile” is set and gives access to
   one Operating Unit, the default Operating Unit will return this value even if
   “MO: Default Operating Unit” is set to a different value.
3. If the profile option “MO: Security Profile” is set and gives access to
   multiple Operating Units, then the profile value “MO: Default Operating Unit”
   if set is validated against the list of Operating Units in “MO: Security
   Profile”. If the Operating Unit is included in the security profile then it
   is returned as the default value. Otherwise there is no Operating Unit
   default. Moreover, if the Profile Option “MO: Default Operating Unit” is not
   set, then there is no default Operating Unit.

What is the impact to you?
With R12 the views owned by “apps” schema are replaced by synonyms to the base tables. These synonyms have security policies attached to them to provide the proper org_id(s) in the where clause to retrieve the data (the database rewrites the SQL statement to include the conditions set by the security policy). You can read up on virtual private database (VPD) and its features in the metalink note mentioned at top of the article. If your report or program uses these synonyms then you will want to choose the proper value for the Operating Unit Mode field. However, if your report or program does use the base tables then this field is not as important.

To understand which applications have multiple organizations access control feature enabled you can query a new table “FND_MO_PRODUCT_INIT). If the product that is enabled has a “Y” for status field then that product can use the MOAC feature.

Pre R12 Multi-Org Features

• Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
• A view in the APPS schema provides the Multi-Org filtering based on the following statement in the where clause.   SUBSTRB(USERENV (’CLIENT_INFO’), 1, 10)

R12 Multi-Org Features

• Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
• A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private Database feature of the Oracle 10G DB Server.

Pre-R12 you could set your SQL session context for multi-org with the following:

BEGIN
dbms_application_info.set_client_info(101);
END;

• In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.

In R12 you can set your SQL session context for a single OU with the following:

BEGIN
execute mo_global.set_policy_context(’S',101);
END;

• The ‘S’ means Single Org Context
• 101 is the ORG_ID you want set

Also In R12 you can set your SQL session context for multiple OU’s with the following:
BEGIN
execute mo_global.set_org_access(NULL,111,‘ONT’);
END;

• 111 is the Security Profile you want to use
• ‘ONT’ is the application short name associated with the responsibility you will be using to find the security profiles:

The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them

select psp.SECURITY_PROFILE_NAME, psp.SECURITY_PROFILE_ID, hou.NAME, hou.ORGANIZATION_ID                from PER_SECURITY_PROFILES psp, PER_SECURITY_ORGANIZATIONS pso, HR_OPERATING_UNITS hou            where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID  and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

Change Management, the mere mention of these two words make people cringe.

To paraphrase Gordon Gekko,

Change is Good…Change Works.

In this bloggers’ opinion, Change Management is a grossly misused, and misunderstood phrase.

At it’s core, Change Management should around change for the positive:

• Does change Increase Revenue?
• Does change Reduce Cost?
• Does change Improve Efficiency?
• Does it provide an environment for growth?
• Etc.

Often during Oracle EBS implementations, we see change is touted along with best practice, as a mantra for the project. People get nervous, raise their defenses, and the project suffers. Change is not something to enforce upon a business, most businesses have gotten to where they are working the way they’ve worked – “if it ain’t broke, don’t fix it”.

Change Management needs to be viewed not as an enforcer of change, but rather an enabler of change. Change allows businesses to move away from inefficient processes, to streamline practices, explore new avenues of growth. Change does not mean that everything you’ve been doing until now is wrong, just means there may be a better way.

Therein lies the art that is Change Management, helping business adopt change as a philosophy, a state of mind.

Oracle has implemented a new parameter on the Concurrent Parameters form to control how to handle Operating Units. The real purpose of this tip is to explain this parameter / new field “Operating Unit Mode” at the concurrent program definition level. For more detail on Multiple Organization Access Control’s “MOAC” impact to custom code and its features you can review Metalink note: 420787.1 and also review Oracle Applications Multiple Organizations Implementation Guide R12.

Where to locate the new field
Within the concurrent program definition, a new field “Operating Unit Mode” is added in the OA Framework pages. A user should have the responsibility “System Administration” assigned to them. This is the web version of System Administrator. To locate this new field Navigate to Concurrent programs. It will open up web page: Concurrent Processing. Query up the concurrent program you want to view and or change the value of Operating Unit Mode. Click on the update button. On the tab “Request” there is a LOV “Operating Unit Mode”. This LOV has three values:

1. NULL – default setting
2. Single – run only for a specific Operating Unit specified by ‘MO: Operating Unit’ profile option
3. Multi – run for multiple Operating Units based on the ‘MO: Security Profile’ profile option

This Operating Unit Mode parameter is used to identify:

• How the program executes the multiple organizations initialization
• When to display Operating Unit prompt in the Submit Requests window and Schedule Requests window.

This impacts how the Submit Requests form evaluates Concurrent Request Parameter List of Values. If you don’t see what you want try changing this setting.
If the Operating Unit Mode field is set to either Single or Multiple then the multiple organizations context is automatically initialized by the concurrent program. The user can also select a value from the operating unit field’s list of values when the mode is Single. The value of the “Operating Unit Mode” must be Single for a majority of the existing operating unit context sensitive reports.

Single Organization Reports
The operating unit mode for single organization reports are flagged as ‘SINGLE’ in the Define Concurrent Programs page. The parameter – Operating Unit is available for single request and request sets. You cannot enter any value in this field if the Operating Unit mode is Multiple or none. When submitting the report, the concurrent program captures the current organization specified in the Operating Unit parameter.

Cross Organization Reports
The Operating Unit mode for cross organization reports are flagged as ‘MULTIPLE’ in the Define Concurrent Programs page. At runtime, multiple organizations initialization populates the temporary table with one or multiple operating units depending on the access control status of the product that owns the cross organization report.