UNIX shell scripting : extract the value of XML-tagged data from a text file

UNIX Shell scripts are often used to perform interface data file manipulation tasks (basic validation, re-naming, archiving, etc). This is fine when the attributes being manipulated are, say, in the file name. But what if the script needs to grab a value from inside the data file? And maybe the file is in XML format.

For example , the XML data file contains the following string:

<FileSequenceNum >0000015</FileSequenceNum>

…and the value of FileSequenceNum is required by the shell script. There must be loads of ways to do this, but here are three examples:

TAG=FileSequenceNum
FILE=testfile.txt

VALUE=`sed -ne "/$TAG/s/[^0-9]*([0-9]*)..*/1/p" $FILE`

Or

VALUE=`grep -w $ TAG $FILE | awk -F'>' '{ print $2 }' | awk -F'<' '{print $1}'`

Or

TAG1="FileSequenceNum "
TAG2="FileSequenceNum"

grep -w $TAG1 dt_test.xml | sed -e 's/^[ t]*//' | sed "s/<$TAG1>(.*)</$TAG2.*/1/"

echo "Value is: $VALUE"

In the last example, two $TAG variables are used because the data file contains a trailing space in the opening XML tag name, but no space in the closing tag name:

<FileSequenceNum >0000015<FileSequenceNum>

How to re-start the Apache OC4J instance following OAF extensions/personalisations

Log on to the database/applications server, and run the following UNIX commands…

cd $ADMIN_SCRIPTS_HOME
adapcctl.sh stop; adoacorectl.sh stop;
adoacorectl.sh start; adapcctl.sh start;

Or, without changing directory…

$ADMIN_SCRIPTS_HOME/adapcctl.sh stop; $ADMIN_SCRIPTS_HOME/adoacorectl.sh stop;
$ADMIN_SCRIPTS_HOME/adoacorectl.sh start; $ADMIN_SCRIPTS_HOME/adapcctl.sh start;

Alternatively, the entire Applications tier can be stopped and re-started using the following two commands (the APPS username and password must be provided)…

$ADMIN_SCRIPTS_HOME/adstpall.sh
$ADMIN_SCRIPTS_HOME/adstrtal.sh

Note: after stopping the applications, make sure that the FNDSM (Service Manager) process has finished before re-starting:

ps -fu $LOGNAME | grep FNDSM

Which Oracle responsibility should I use to run a concurrent program?

ALTER SESSION set nls_language = 'American';

SELECT fa.application_short_name
,      frg.request_group_name
,      frt.responsibility_name
FROM   fnd_concurrent_programs_vl   fcpv
,      fnd_request_group_units      frgu
,      fnd_request_groups           frg
,      fnd_application              fa
,      fnd_responsibility           fr
,      fnd_responsibility_tl        frt
WHERE  1 = 1
AND    fcpv.concurrent_program_id = frgu.request_unit_id
AND    frg.request_group_id = frgu.request_group_id
AND    fa.application_id = frg.application_id
AND    frg.request_group_id = fr.request_group_id
AND    NVL(fr.end_date,SYSDATE + 1) > SYSDATE
AND    fr.responsibility_id = frt.responsibility_id
AND    fcpv.user_concurrent_program_name = 'Aged Creditors Report'
;

Oracle Discoverer – creating non-mandatory parameters with lists of values (LOV’s)

1. Firstly create a Custom Dicoverer Folder to return the list of values, as follows. In Discoverer Administrator, Right-click on the Business Area containing your reports-related Discoverer objects (Folders, etc.), and choose New Custom Folder…

2. Paste in the SQL statement which will reurn the required list of values. For example, and General Ledger accounting segment value:

SELECT 'All'                      DEPARTMENT
FROM   dual
UNION
SELECT ffv.flex_value             DEPARTMENT
FROM   apps.fnd_flex_value_sets   ffvs
,      apps.fnd_flex_values       ffv
WHERE  ffv.flex_value_set_id = ffvs.flex_value_set_id
AND    ffvs.flex_value_set_name = 'DEPARTMENT'
AND    ffv.enabled_flag = 'Y'
AND    ffv.summary_flag = 'N'

Click on Validate SQL to ensure that the syntax is correct, and that the database object names are valid.

3. Right-click on the new folder, choose Properties, and and assign useful values for the Name, Description and Identifier fields; say GL_DEPARTMENT_LOV.

4. Expand the folder to show the Department Item, right-click on the item, and choose New Item Class. Click Next > three times, accepting the default attribute values, then finally giving the new item class a meaningful name: say GL_DEPARTMENT_LOV. The Item Class should now be visible in the Item classes tab in Discoverer Administrator.

5. Return to the Data tab, and expand the folder on which the actual Discoverer report is based. Find the item for which this list of values is being built, right-click on it, and click Properties. In the Item class attribute, choose your new item class from the dropdown, and click Apply.

6. In Disoverer Desktop, re-connect to the database, to reflect the changes just made to the EUL, and open the Disoverer Workbook.

7. Go to Tools > Parameters > New, to create a new parameter (based on the Department field, in this example). Choosing the Department field in the For Item field means that the resulting parameter will automatically use the ist of values created above.

8. In order to provide an “All” option for the list of values (ie. to make the parameter effectively non-mandatory), you must ensure that the list-of-values SQL query contains a UNION (see above example).

9. Then, Discoverer Desktop, go to Tools > Conditions, highlight the condition created for the new Parameter, and click Edit.

10. Click Advanced >>, and then Add. Using the OR grouping, create a new condition :GL_DEPARTMENT = ‘All’. Click OK to save.

Profile Options Used By OA Personalization Framework (see Metalink 275876.1)

Log on using the System Administrator responsibility.

The Profile Options used by the OA Personalization Framework (see Metalink Note 275876.1), are as follows:

1. Disable Self-service Personal (FND_DISABLE_OA_CUSTOMIZATIONS)

2. FND: Personalization Region Link Enabled (FND_PERSONALIZATION_REGION_LINK_ENABLED)

3. Personalize Self-service Defn (FND_CUSTOM_OA_DEFINTION)

4. FND:OA:Enable Defaults

5. FND: Developer Mode

6. FND: Disable Partial Page Rendering

7. FND: Diagnostics (FND_DIAGNOSTICS)  to enable “About This Page” link

8. Self Service Accessibility Features (ICX_ACCESSIBILITY_FEATURES) for configurable pages

For more information about the “About” Page, refer to the Discovering Page,
Technology Stack and Session Information section in the Testing and Debugging
chapter of the Oracle Application Framework Developer’s Guide.

R12 Banks, Branches and Bank Accounts

The following query yields the bank, branch and and account numbers in Oracle eBusiness R12:

SELECT hp.party_name BANK_NAME
, hop.organization_name BANK_BRANCH_NAME
, hop.bank_or_branch_number BRANCH_SORT_CODE
, ieba.masked_bank_account_num BANK_ACCT_MASKED
, ieba.bank_account_num BANK_ACCT
FROM hz_parties hp
, hz_organization_profiles hop
, iby_ext_bank_accounts ieba
WHERE hp.party_type(+) = 'ORGANIZATION'
AND hop.party_id(+) = ieba.branch_id
AND hp.party_id(+) = ieba.bank_id
AND NVL(hp.status(+),'X') = 'A'
AND NVL(hop.effective_end_date(+),SYSDATE + 1) > SYSDATE
AND ieba.bank_account_num = '20286184'
;

And account owners…

SELECT *
FROM iby_account_owners
WHERE ext_bank_account_id = 48586
;

BI Publisher: Configuring Concurrent Programs to Publish Large Quantities of Data

When a BI-published concurrent program uses an XML Data Template  to generate large quantities of data (for example the standard Account Analysis Report [XLAAARPT], in the Subledger Accounting application), the report may fail with the following error (in the log file):

Calling XDO Data Engine...
****Warning!!! Due to high volume of data, got out of memory exception...***
****Please retry with scalable option or modify the Data template to run in scalable mode...***
********************************************************************************

The problem is caused by the standard Java Concurrent Program, XML Publisher Data Template Executable [XDODTEXE], and the memory allocated to the program’s Java Virtual Machine (JVM) which is created at runtime by Oracle Applications.

There appear to be a number of ways to tackle this problem.

The quickest way seems to be entering the text string
-Xss2048k -Xmx2048m
…to the Executable Options field in the Concurrent Program definition screen:

-Xss: sets the JVM stack size (to 2MB in the above example).

-Xmx: sets the maximum memory (“heap size”) allocated to the JVM. In above example it is set to 2GB, which is the maximum usable memory for the 32-bit JVM used by the eBusiness Suite.

The JVM’s heap size can also be set (by a DBA) at site level, although I haven’t found it necessary to do this. To see what the value is set to, run the following query:

SELECT developer_parameters
FROM   fnd_cp_services
WHERE  service_id =
(
SELECT manager_type
FROM   fnd_concurrent_queues
WHERE  concurrent_queue_name = 'FNDCPOPP'
)

It is updated using SQL, and then the Concurrent Managers must be bounced:

UPDATE fnd_cp_services
SET    developer_parameters = 'J:oracle.apps.fnd.cp.gsf.GSMServiceController:-mx2048m'
WHERE  service_id =
(
SELECT manager_type
FROM   fnd_concurrent_queues
WHERE  concurrent_queue_name = 'FNDCPOPP'
)

Oracle also recommend creating a Scalable Flag for the concurrent program (see Metalink Note 737311.1), but, again, I haven’t found it necessary to do this.

The scalability option is set by performing these steps:

Resp: System Administrator
Nav: Concurrent > Program > Define

Query back the concurrent program, and add a parameter called P_SCALABLE_FLAG:

Enabled: Yes
Value Set: yes_no
Default Type: Constant
Default Value: Y
Display: No
Token: ScalableFlag (this value is case sensitive)

You can also create a parameter called P_DEBUG_FLAG so that the log file includes the following:

  • Report SQL
  • Calling XDO Data Engine and the Scalable mode on

The parameter should have the following values:

Enabled: Yes
Value Set: yes_no
Default Type: Constant
Default Value: Y
Display: No
Token: DebugFlag (this value is case sensitive)

Other memory related issues…

If the concurrent program finishes with a Warning status, and the following message appears in the log file:

+------------- 1) PUBLISH -------------+
Beginning post-processing of request 4099006 on node C-ERP-DEVORA01 at 30-MAY-2012 10:31:15.
Post-processing of request 4099006 failed at 30-MAY-2012 10:31:16 with the error message:
One or more post-processing actions failed. Consult the OPP service log for details.
+--------------------------------------+

…consult the OPP service log file which is found in $APPLCSF/$APPLLOG, and which is called something like FNDOPP107254.txt.

(The contents of the OPP service log file can also be viewed from the Applications, Resp: Systems Administrator, Nav: Concurrent > Manager > Administer, select the “Output Post Processor” and click on the Processes button, choose the Active process and click on the Manager Log button.)

If a java.lang.OutOfMemoryError appears for your concurrent request ID, configure the XML Publisher Administrator Configuration settings as follows:

Resp: XML Publisher Administrator
Nav: Home > Administration > Configuration > Properties > General > Temporary Directory

Check that the temporary directory location (/tmp, in the above example) is at least 5GB or 20 times larger than largest XML data file that you generate. You can check the available space using the UNIX command df -h, as follows:

server1$ df -h /tmp
Filesystem             size   used  avail capacity  Mounted on
swap                    34G    17M    34G     1%    /tmp

It may also be necessary to set some FO Procesing properties on this page (although I haven’t had to do this, and the settings may require the application of one or two BI-Publisher-related patches – see Metalink Note 737311.1):

Use XML Publisher’s XSLT processor = True
Enable scalable feature of XSLT processor = False
Enable XSLT runtime optimization = True

For further information, see Metalink Notes:

737311.1: “How to Configure the Account Analysis Report in Release 12 for Large Reports”.

1410160.1: “Troubleshooting Known XML Publisher and E-Business Suite (EBS)”. “Integration Issues

Using the FND_STANDARD_DATE valueset in Oracle Applications R12

Date parameters for concurrent programs in Oracle Applications R12 must be set up using the FND_STANDARD_DATE valueset.

The date value entered by the user at runtime is passed to the underlying program as a DATE-TIME STRING, not as a DATE.

This format of this VARCHAR2 data item is “YYYY/MM/DD HH24:MI:SS” (e.g. “2011/10/01 00:00:00”).

In order to use this VARCHAR2 value in a WHERE clause predicate, it can be converted to a DATE value using the fnd_date.canonical_to_date function, as shown in the following example:

SELECT user_name
FROM   fnd_user
WHERE  last_update_date >  fnd_date.canonical_to_date('2011/12/31 00:00:00')

Personalise the Oracle Applications R12 Login Page to include client logos, etc.

Some of the regions on the standard Oracle Applications Login Page can be personalised to give the page a client-specific look-and-feel.

The areas that can be personalised are outlined and numbered 1 to 5 in the screenshot below:

To personalise these regions, assign your applications user the Functional Administrator responsibility.

Under the Functional Administrator responsibility, navigate to the “Personalization” tab, and enter the following string in the “Document Path” field:

/oracle/apps/fnd/sso/login/webui/MainLoginPG

…which corresponds to the XML definition of the Login Page. Then click on “Go”.

In the results table, click on the “Personalize Page” icon for the above Doc Name.

Ensure that the “Site” checkbox is ticked and then click “Apply”.

In the “Personalization Structure” page region, click on “Expand All”.

The numbered areas in the screenshot above can be found in the list of objects on this page, as follows:

  1. Image: (corporateBrandingImage)   /OA_MEDIA/FNDSSCORP.gif
  2. Image    topLines.gif
  3. Image: globalTop image   globalTop.jpg
  4. Image: global image   global.jpg
  5. Image: people image   people.jpg

The default filenames, shown in blue, are the ones that appear in the screenshot, and can all be found in the $OA_MEDIA directory on the applications server.

In order to personalize the Login Page, you can create your own versions of these images, using the same file names prefixed with the client initials (e.g. DTFNDSSCORP.gif). These files should be copied to the $OA_MEDIA directory.

To personalise region #1, click on the Pencil icon in the “Personalize” column for the “ Image: (corporateBrandingImage)” item.

Find the “Image URI” attribute, and type the following in the field in the “Site” column: “DTFNDSSCORP.gif”. Then click “Apply”.

This can be repeated for the remaining four image objects.

Note: the standard “ORACLE” corporateBrandingImage which appears at the top-lefthand corner of all OA Framework (“Self-Service pages”) can be replaced with a custom image by specifying the name of the custom image file (e.g. DTFNDSSCORP.gif) in the “Corporate Branding Image for Oracle Applications” profile option at Site level.

Note: An image can also be inserted into workflow notification headers by personalising the following page (via the Functional Administrator responsibility):

/oracle/apps/fnd/wf/worklist/webui/NotifDetailsPG

Typically, an image file (say, XXFNDHOMEBRAND.gif) is copied to the $OA_MEDIA directory, and referenced in the personalisation, e.g.:

Image URI = /OA_MEDIA/C4FNDHOMEBRAND.gif