Short n Fat vs Tall n Slim: Apps Printer Drivers 66 lines per page A4 printing

In the old school days before the 3Ps - PASTA PDF Postscript and yonks before BIP there was character mode printing.

Actually, there still be character mode printing last time I checked. Gotta love those accountant's reports.

Being one of the ?unlucky? few operating outside the USA meant that the default printer driver initialization strings - or should that be initialisation - just didn't cut it with our tall n slim A4 paper vs short n fat US Letter paper. Hey, I'm not insinuating anything ;-)

And it was always a challenge to get character mode printing sorted for A4 paper without double spaced lines and printers that spat the dummy when you sent them US Letter. I could never understand why Oracle didn't deliver a few friendly print drivers for A4 paper.

Anyway one day many years ago I got out the handy character printing toolkit of:

• a PCL 4 syntax manual,
• the PCL escape character
• some nifty shell scripts

And decided to save a few trees and get portrait 80 x 66, landscape 132x66 and landwide 180x66 printing nicely making best use of space, fonts and not flowing 5 or so lines over to the next page.

Before this info is relegated to the age of dinosaurs, this is what I came up with:

A4 Paper Printer Driver Initialization Strings for HPLJ4 (or higher) printers

A4 Portrait 80x66 /eE/e&l0o26a7C/e(s0t0p11H/e&a5L/e&k2G
A4 Landscape 132x66 /eE/e(0N/e&l1o26a5.25c-340z150u0S/e(s0p13H/e&k2G
A4 Landwide 180x66 /eE/e(0N/e&l1o26a5.25c-340z150u0S/e(s0p16.66H/e&k6.75h2G/e(2N

Oh, while I'm here I should say that the o26 means A4 paper and the &k2G means set LF to CRLF which solves lines being printed in cascading style and disappearing off the right hand side of the page like this:

abcdefghijklmnopqrstuvwxyz
                          abcdefghijklmnopqrstuvwxyz
                                                    abcdefghijklmnopqrstuvwxyz
                                                                              abcdefghijklmnopqrstuvwxyz

Of course, you can also add a duplex mode via a bunch of additional drivers to save even more trees by adding one of:
&l1S - Duplex Long Edge
&l2S - Duplex Short Edge

Or even add some more drivers with relevant tray control with one of:
&l1H Feed from Tray 1 (upper)
&l2H Manual Feed
&l3H Manual Envelope Feed
&l4H Feed from Tray 2 (lower)
&l5H Feed from paper deck

A very quick and nasty way to get this sorted in an environment not using US Letter paper with printers setup as type HPLJ4SI is (not supported of course), oh and remember to restart the concurrent managers:

set define ~
update fnd_printer_drivers set
--user_printer_driver_name = 'LANDSCAPE A4 for Printer HPLJ4SI',
description = 'Init was ' || initialization,
initialization = '/eE/e(0N/e&l1o26a5.25c-340z150u0S/e(s0p13H/e&k2G',
last_update_date = sysdate
where printer_driver_name = 'LANDSCAPEHPLJ4SI';

update fnd_printer_drivers set
--user_printer_driver_name = 'LANDWIDE A4 for Printer HPLJ4SI',
description = 'Init was ' || initialization,
initialization = '/eE/e(0N/e&l1o26a5.25c-340z150u0S/e(s0p16.66H/e&k6.75h2G/e(2N',
last_update_date = sysdate
where printer_driver_name = 'LANDWIDEHPLJ4SI';

update fnd_printer_drivers set
--user_printer_driver_name = 'PORTRAIT A4 for Printer HPLJ4SI',
description = 'Init was ' || initialization,
initialization = '/eE/e&l0o26a7C/e(s0t0p11H/e&a5L/e&k2G',
last_update_date = sysdate
where printer_driver_name = 'PORTRAITHPLJ4SI';

commit;

Old school stuff, but can't remember how many times these statements have helped.

PS. Did I remember to say restart the concurrent managers after making printer driver changes?

PPS. Despite PASTA being a fantastic tool, I found myself modifying the following in order to get around a tad more setup to force US Letter to A4 paper

In file $FND_TOP/resource/pasta_pdf.cfg there is a line:
preprocess=pdftops {infile} {outfile}
Change this to the following (replace /usr/local/bin to wherever pdftops is installed):
preprocess=/usr/local/bin/pdftops -paper A4 {infile} {outfile}

Sweet.

Update: fixed some HTML formatting issues and missing || in the code, plus added a couple more comments.

Zero-Gee: the ultimate developers or gaming chair, err, throne

A few months back I attended an Unlimited Potential event on design and technology. A prototype of the NASA research based "Zero-Gee" chair - a 135° zero-gravity motorized ergonomic chair - made its appearance and got some interesting reactions from the audience. Well, its gone from design to prototype to reality. I had an email last week from Tom Rynaarts letting me know the first small batch is close to delivery. Talk about a great concept, bound to be a strong following in tech communities for this ultimate piece of hardware. Now, where did I put that spare Xmas money!

PS. Unfortunately they're not generally available yet, but keep an eye out ;-)

XLite outgoing calls via 2Talk get error 408 with Zone Alarm

A week or two back I signed up at New Zealand's 2talk on the free plan to try out some funky VOIP services ... you get a free New Zealand "mobile" phone and fax number and a range of services, voice mail, call forwarding, fax mailbox etc, I'm planning to use it next time I'm on holiday/business to avoid exorbitant roaming charges. Tried out the call forwarding to my mobile, worked fine.

Just tried to call out from my laptop with X-Lite and the call didn't initiate, just got error "request timeout" and a pretty female voice saying "the other party is busy" or something like that.
I switched on the XLite diagnostic log and saw:

[07-07-22]21:07:03.059 | Info | RESIP:DUM | "Got: SipResp: 408 tid=blahblah cseq=INVITE / 1 from(wire)" | 
[07-07-22]21:07:03.060 | Info | RESIP:DUM | "Failure:  error response: SipResp: 408 tid=blahblah cseq=INVITE / 1 from(wire)" |  

Quick surf identified Zone Alarm as the culprit blocking port 5060. I couldn't work out quickly how to setup my Zone Alarm (free version) to open port 5060, so here's an alternative.
Instead of opening port 5060, you can add the 2talk server to your trusted zone.
If you make a "failed" call then open up the ZoneAlarm Control Center, click Alerts and Logs, you'll see a Blocked (under Action Taken) Incoming (under Direction) with a source IP something like 202.180.xxx.yyy :5060. Right click on that line and choose Add to Zone: Trusted.
Note that making this change allows whatever your trusted zone settings are to/from the 2talk server. And 2talk may have more servers or change them, so you may need to do this again.

Voila... outgoing calls should now work.

Query: eBiz Setup of Books/Chart of Accounts keyflex segments

Thought I'd post a few of the common queries I run to save some retyping... here's the first! When I want a quick overview of the structure of clients chart of accounts, the following query comes in handy. Shows chart of accounts, set of books, accounting flexfield segments in use and their attributes, value sets for the key flexfield segments. Looks big, but quite a bit of repetition here to get the segment attributes.

select sob.name sob_name
,      sob.set_of_books_id sob_id
,      sob.chart_of_accounts_id coa_id
,      fifst.id_flex_structure_name struct_name
,      ifs.segment_name
,      ifs.application_column_name column_name
,      sav1.attribute_value  BALANCING
,      sav2.attribute_value COST_CENTER
,      sav3.attribute_value NATURAL_ACCOUNT
,      sav4.attribute_value  INTERCOMPANY
,      sav5.attribute_value SECONDARY_TRACKING
,      sav6.attribute_value GLOBAL
,      ffvs.flex_value_set_name
,      ffvs.flex_value_set_id
from   fnd_id_flex_structures fifs
,      fnd_id_flex_structures_tl fifst
,      fnd_segment_attribute_values  sav1
,      fnd_segment_attribute_values sav2
,      fnd_segment_attribute_values sav3
,      fnd_segment_attribute_values  sav4
,      fnd_segment_attribute_values sav5
,      fnd_segment_attribute_values sav6
,      fnd_id_flex_segments ifs
,      fnd_flex_value_sets ffvs
,      gl_sets_of_books sob
where  1=1
and    fifs.id_flex_code = 'GL#'
and    fifs.application_id =  fifst.application_id
and    fifs.id_flex_code = fifst.id_flex_code
and    fifs.id_flex_num = fifst.id_flex_num
and    fifs.application_id =  ifs.application_id
and    fifs.id_flex_code = ifs.id_flex_code
and    fifs.id_flex_num = ifs.id_flex_num
and    sav1.application_id =  ifs.application_id
and    sav1.id_flex_code = ifs.id_flex_code
and    sav1.id_flex_num = ifs.id_flex_num
and    sav1.application_column_name =  ifs.application_column_name
and    sav2.application_id =  ifs.application_id
and    sav2.id_flex_code = ifs.id_flex_code
and    sav2.id_flex_num = ifs.id_flex_num
and    sav2.application_column_name =  ifs.application_column_name
and    sav3.application_id =  ifs.application_id
and    sav3.id_flex_code = ifs.id_flex_code
and    sav3.id_flex_num = ifs.id_flex_num
and    sav3.application_column_name =  ifs.application_column_name
and    sav4.application_id =  ifs.application_id
and    sav4.id_flex_code = ifs.id_flex_code
and    sav4.id_flex_num = ifs.id_flex_num
and    sav4.application_column_name =  ifs.application_column_name
and    sav5.application_id =  ifs.application_id
and    sav5.id_flex_code = ifs.id_flex_code
and    sav5.id_flex_num = ifs.id_flex_num
and    sav5.application_column_name =  ifs.application_column_name
and    sav6.application_id =  ifs.application_id
and    sav6.id_flex_code = ifs.id_flex_code
and    sav6.id_flex_num = ifs.id_flex_num
and    sav6.application_column_name =  ifs.application_column_name
and    sav1.segment_attribute_type =  'GL_BALANCING'
and    sav2.segment_attribute_type = 'FA_COST_CTR'
and    sav3.segment_attribute_type = 'GL_ACCOUNT'
and    sav4.segment_attribute_type  = 'GL_INTERCOMPANY'
and    sav5.segment_attribute_type =  'GL_SECONDARY_TRACKING'
and    sav6.segment_attribute_type =  'GL_GLOBAL'
and    ifs.id_flex_num = sob.chart_of_accounts_id
and    ifs.flex_value_set_id = ffvs.flex_value_set_id
-- comment the next expression to show all books
-- currently it show the info for the site level set profile option value
and    sob.set_of_books_id = 
        nvl(fnd_profile.value('GL_SET_OF_BKS_ID'),sob.set_of_books_id)
order by  sob.name, sob.chart_of_accounts_id, ifs.application_column_name;

And just for good measure here's the sample output from 11.5.10.2 Vision Demo instance.

SOB_NAME                           SOB_ID     COA_ID STRUCT_NAME                    SEGMENT_NAME                   COLUMN_NAME                    B C N I S G FLEX_VALUE_SET_NAME                                          FLEX_VALUE_SET_ID
------------------------------ ---------- ---------- ------------------------------ ------------------------------ ------------------------------ - - - - - - ------------------------------------------------------------ -----------------
Vision Operations (USA)                 1        101 Operations Accounting Flex     Company                        SEGMENT1                       Y N N Y N Y Operations Company                                                     1002470
Vision Operations (USA)                 1        101 Operations Accounting Flex     Department                     SEGMENT2                       N Y N N N Y Operations Department                                                  1002471
Vision Operations (USA)                 1        101 Operations Accounting Flex     Account                        SEGMENT3                       N N Y N N Y Operations Account                                                     1002472
Vision Operations (USA)                 1        101 Operations Accounting Flex     Sub-Account                    SEGMENT4                       N N N N N Y Operations Sub-Account                                                 1002473
Vision Operations (USA)                 1        101 Operations Accounting Flex     Product                        SEGMENT5                       N N N N N Y Operations Product                                                     1002474

Nice.

Quick Search to Oracle Metalink

If you're a regular Oracle Metalink user, its a must to get Metalink Search in an ideal place in the browser. This page has the instructions and you can find out more about the solution at the OracleAppsLab blog page. Credit to the creators!

Apps DBA wish - adpatch under my influence

Update: This script also works without modification on Release 12. Fixed missing slashes in command to generate adpatchdef.txt.

I like shortcuts.

I like to keep track of what a I do.

So a typical Apps DBA session will be a flurry of PuTTY, Notepad, Firefox and lots of alt-tab, control-c, shift-ins key combos.

People look over my shoulder from time to time and struggle to keep up ;-)

Anyway, I digress ...

One of the most laborious Apps DBA is applying patches.

I'd like to just think about it and the patch gets applied automatically ... not quite gonna happen just yet.

In the early days of Apps I automated adpatch sessions by piping "stored answer" files into stdin ie. "adpatch << EOF". Later versions of adpatch with non-interactive mode mean thats no longer necessary. I really like adpatch non-interactive mode.

However, there were still a few things missing that I found myself repetitively doing:

1. Typing "adpatch ..." to many times
2. Unzipping the patch archive
3. Tracking adpatch runs, by patch applied and cumulative time to apply
4. Switching on and off maintenance mode (at least on the concurrent processing server)
5. Answering "Yes" at "do you want to restart"
6. Getting frustrated at different DBAs patch log file naming standards

So I threw together a very quick shell script that would do these plus provide a standard place to keep adpatch options, and make it easy to string together a list of patches to apply at midnight.

Admitedly its a bit blunt, but the number of times I use it has been well worth it.

So throw the script in your PATH and try it out on your next patch.

To apply a patch just type:
aa 1234567 u
where 1234567 = patch number you want to apply with zip file in /patch
and u = driver to apply ie. u1234567.drv

Here's the script:

#!/bin/sh
#
# adpatch_auto a.k.a aa
#
# Ver Date      Author     Description
# --- --------- ---------- ---------------------------
# 1.0 30-OCT-00 G. Roberts Created
# 1.1 09-MAY-07 G. Roberts Extended for zip files
#
# Automated adpatch for R11i
# Only use for patches you've already done manually, or in non-PROD environments
# Notes: You must have your environment set correctly
# You must have precreated defaults.txt
# Patch must be in PT (see config below)
# Patch driver number must be same as patch number

if [ "$#" != "2" -a "$#" != "3" -a "$#" != "4" -a "$#" != "5" ]; then
  echo "Usage: aa PATCH_NUM DRIVERFILE_PREFIX [RESTART=YN [INTERACTIVE=YN]]"
  echo " Simple  e.g: aa 123456 u"
  echo " Restart e.g: aa 123456 u Y Y"
  exit 1
fi

# CONFIGURATION ITEMS
# Set PT to where your base patch directory is
PT=/patch; export PT
# LOGFILE is a file keeping track of aa run history
LOGFILE=$APPL_TOP/admin/p.log; export LOGFILE
# DEFFILE is your defaults.txt probably defaults.txt or adalldefaults.txt
DEFFILE=$APPL_TOP/admin/${TWO_TASK}/adalldefaults.txt
# Set DOUNZIP to Y if you want aa to search for and unzip p*.zip
DOUNZIP=Y; export DOUNZIP
# Set SETMAINT to Y to automatically switch on and off maintenance mode
# Only needed for late'ish versions 11.5.10 or higher
SETMAINT=Y; export SETMAINT
# Date format for logging
DF="+%y%m%d %H:%M:%S"

# Check CONTEXT_NAME is set
if [ -z "${CONTEXT_NAME}" ]; then
  echo "CONTEXT_NAME is not set!"
  exit 1
else
  CTXT=${CONTEXT_NAME}; export CTXT
fi

ORIGDIR="`pwd`"

PNUM=$1; export PNUM
DRIV=$2; export DRIV
PATCH=$PNUM; export PATCH
if [ ! -z "$3" ]; then
  if [ "$3" = "N" ]; then
    REST="restart=no"; export REST
  else
    REST="restart=yes"; export REST
  fi
fi
if [ "$#" = "4" ]; then
  if [ "$4" = "Y" ]; then
    INTERACT=yes; export INTERACT
  else
    INTERACT=no; export INTERACT
  fi
else
  INTERACT=no; export INTERACT
fi

echo "Patch number set to $PNUM"
echo "Driver set to $DRIV"
echo "Restart set to $REST"
echo "Interactive set to $INTERACT"
echo
echo `date "$DF"`" Starting $DRIV $PNUM $REST $INTERACT"
echo `date "$DF"`" Starting $DRIV $PNUM $REST $INTERACT" >> $LOGFILE

cd $PT
if [ "$DOUNZIP" = "Y" ]; then
 if [ ! -d "$PNUM" ]; then
  echo `date "$DF"`" Looking for zip files to unzip"
  ZFILE=`ls -tr *$PNUM*.zip  awk '{print $1}'`; export ZFILE
  if [ -n "$ZFILE" ]; then
   if [ -f "$ZFILE" ]; then
    echo `date "$DF"`" Unzipping $ZFILE"
    echo `date "$DF"`" Unzipping $ZFILE" >> $LOGFILE
    unzip "$ZFILE"
   else
    echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist."
    echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist." >> $LOGFILE
    exit 1
   fi
  else
   echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist."
   echo `date "$DF"`" ERROR: File $PT/*$PNUM*.zip does not exist." >> $LOGFILE
   exit 1
  fi
 fi
fi

if [ ! -d $PNUM ]; then
  echo `date "$DF"`" ERROR: Directory $PT/$PNUM does not exist."
  echo `date "$DF"`" ERROR: Directory $PT/$PNUM does not exist." >> $LOGFILE
  exit 1
fi

if [ "$SETMAINT" = "Y" ]; then
  echo `date "$DF"`" Enable maintenance mode"
  echo `date "$DF"`" Enable maintenance mode" >> $LOGFILE
  sqlplus -s apps/`get_pw db apps` @$AD_TOP/patch/115/sql/adsetmmd.sql ENABLE
fi

echo `date "$DF"`" Calling adpatch"
echo `date "$DF"`" Calling adpatch" >> $LOGFILE
adpatch \
defaultsfile=$DEFFILE \
logfile=${DRIV}${PNUM}_$CTXT.log \
patchtop=$PT/$PATCH \
driver=${DRIV}${PNUM}.drv \
workers=6 interactive=$INTERACT options=novalidate $REST
#workers=6 interactive=$INTERACT options=novalidate,nocheckfile $REST

if [ -d $PT/$PATCH/backup ]; then
  echo `date "$DF"`" mv $PT/$PATCH/backup to $PT/$PATCH/backup_$CTXT"
  echo `date "$DF"`" mv $PT/$PATCH/backup to $PT/$PATCH/backup_$CTXT" >>$LOGFILE
  mv $PT/$PATCH/backup $PT/$PATCH/backup_$CTXT
fi

if [ "$SETMAINT" = "Y" ]; then
  echo `date "$DF"`" Disable maintenance mode"
  echo `date "$DF"`" Disable maintenance mode" >> $LOGFILE
  sqlplus -s apps/`get_pw db apps` @$AD_TOP/patch/115/sql/adsetmmd.sql DISABLE
fi

cd "$ORIGDIR"

echo `date "$DF"`" Finished applying $DRIV $PNUM $INTERACT"
echo `date "$DF"`" Finished applying $DRIV $PNUM $INTERACT" >> $LOGFILE

Some notes - ensure your system password is set in the $APPL_TOP/admin/${TWO_TASK}adalldefaults.txt file - for Production runs I'd recommend switching fully into maintenance mode. - this works for 11.5.x, but may require some tinkering for different versions - If you want a new defaults file run adpatch as follows to the point where it asks for the directory where the Oracle Applications patch has been unloaded. Type abort at this prompt.

adpatch options=hotpatch defaultsfile=$APPL_TOP/admin/${TWO_TASK}/adpatchdef.txt

In this script I reference a centralized password script - get_pw. Create this in your path and set the permissions as restrictive as possible. This script looks something like this:

if [ $# = 2 ]; then
TYP=$1
USR=$2
elif [ $# = 1 ]; then
TYP=DB
USR=$1
else
echo invalid_arguments
exit 1
fi
if [ "`echo $TYP  tr [A-Z] [a-z]`" = "db" ]; then
if [ "`echo $USR  tr [A-Z] [a-z]`" = "apps" ]; then
echo apps
exit 0
else
echo unknown_db_user
exit 1
fi
fi

For more details on AD utilities in non-interactive mode see:

Oracle Applications Maintenance Procedures

PS. Steven Chan recently posted adpatch non-interactive mode as number 5 on his list of Top 7 ways of reducing patching downtimes for Apps.