Wednesday Nov 26, 2014

Extending OUAF Scripting

Whilst the Scripting tool available within the OUAF provides implementers with a new method of developing algorithms, the language does have a number of limitations which would increase the complexity of solutions (or require the development of customisations in Java). An instance of this limitation which I have encountered recently related to the need to calculate the remainder of a division and utilise this remainder. Scripting does not provide a method of issuing a MOD functional call, however Query Zones do allow this function to be executed.

So.. to resolve the limitation (without reverting to Java coding), we developed the following approach:


1.    A Query zone was developed as:

Zone

Description

Zone Type

Application Service

Width

Configuration Area Display Options

User Filter 1

User Filter 2

Initial Display Columns

SQL Condition

SQL Statement

Number of Rows to Retrieve for SQL

Column 1

CM-DOMOD

Perform a Modulus Calc

F1-DE-SINGLE

F1-DFLTS

Full

FilterArea=NEVER DragDropArea=NEVER

type=NUMBER

type=NUMBER

C1

F1 F2

SELECT MOD(:F1,:F2) FROM DUAL

1

source=SQLCOL sqlcol=1



2.    This was then wrapped inside a Business Service as:

Business Service

Description

Detailed Description

Service Name

Application Service

Schema

CM-DOMOD

Perform a Modulus Calc

Perform a Modulus Calc

FWLZDEXP

F1-DFLTAPS

<schema>

<zone mapField="ZONE_CD" default="CM-DOMOD" private="true"/>

<PrimaryValue mapField="F1_VALUE"/>

<Divisor mapField="F2_VALUE"/>

<rowCount mapField="ROW_CNT"/>

<results type="list" mapList="DE">

<MODResult dataType="number" mapField="COL_VALUE">

<row mapList="DE_VAL">

<SEQNO is="1"/>

</row>

</MODResult>

</results>

</schema>



3.    The Script-based algorithm contains the majority of the required logic, including all pre-population of the required source variable <PrimaryValue> and the divisor <Divisor> as:

.

.

for ($accountInstance in "CM-AccountPersonSearch/results")

if ("$accountInstance/accountId = C1-AccountMainPerson/accountId")

if ("$accountInstance/accountNumber != $BLANK")

move "$accountInstance/accountNumber" to $accountNumber;

move "substring(string($accountInstance/accountNumber), string-length($accountInstance/accountNumber), 1)" to $temp_acct_1;

move "substring(string($accountInstance/accountNumber), string-length($accountInstance/accountNumber)-1, 1)" to $temp_acct_2;

move "substring(string($accountInstance/accountNumber), string-length($accountInstance/accountNumber)-2, 1)" to $temp_acct_3;

move "substring(string($accountInstance/accountNumber), string-length($accountInstance/accountNumber)-3, 1)" to $temp_acct_4;

move "substring(string($accountInstance/accountNumber), string-length($accountInstance/accountNumber)-4, 1)" to $temp_acct_5;

move "substring(string($accountInstance/accountNumber), string-length($accountInstance/accountNumber)-5, 1)" to $temp_acct_6;

move "substring(string($accountInstance/accountNumber), string-length($accountInstance/accountNumber)-6, 1)" to $temp_acct_7;

move "substring(string($accountInstance/accountNumber), string-length($accountInstance/accountNumber)-7, 1)" to $temp_acct_8;

move "(number($temp_acct_1) * 7) + (number($temp_acct_2) * 3) + (number($temp_acct_3) * 1) + (number($temp_acct_4) * 7) + (number($temp_acct_5) * 3) + (number($temp_acct_6) * 1) + (number($temp_acct_7) * 7) + (number($temp_acct_8) * 3)" to $temp_subtotal;

move "$temp_subtotal" to "CM-DOMOD/PrimaryValue";

move '10' to "CM-DOMOD/Divisor";

invokeBS 'CM-DOMOD' using "CM-DOMOD";

if ("CM-DOMOD/results[1]/MODResult = 0")

move '0' to $checkDigit;

else

move "10 - CM-DOMOD/results[1]/MODResult" to $checkDigit;

end-if;

else

move ' ' to $checkDigit;

end-if;

 .

.



This approach, allows implementations to leverage the strengths of each component (Script, Query Zone, Business Service, etc) to extend what is possible within the framework.

Wednesday Dec 11, 2013

Upgrading Versions

When running the CC&B (and OUAF) framework upgrade scripts, it is not uncommon to encounter "duplicate record" type errors, usually caused by custom records inserted with values which do not conform to the standard naming convention (custom fields are recommended to be prefixed with CM), or instances where accelerator-related fields are bundled into the base product. In instances where these errors are encountered it is recommended that the application architects review the records and either change the existing values to an unused code, or alternatively delete the records altogether before rerunning the script step (the script should pause once these errors are listed allowing the review and correction to occur).

These errors are typically encountered as part of the initial Package Release/ Config Master upgrade and as a result any rollbacks required are usually fast (as a result of the fact that these instances have extremely low volumes of transactional data present in the database).

It is vitally important to recognise that all data errors must be analysed before taking any corrective action, as the ability to corrupt the installed instance is very high, and that any direct data fixes circumvent the existing business object based validation rules built into the product. Ideally all deletions should be performed via the CC&B front-end (which will require a database restore back to a known version which aligns with the application version) to ensure that the referential integrity is maintained.

I have also encountered issues with these scripts when running against full volume database instances, specifically related to instances where the upgrade script is attempting to add or change column definitions on tables with large row counts. In these instances there are really only a couple of methods available to speed up the upgrade:

1. Make use of Oracle 11g features which speed up column changes (when compared to the older Oracle 10g method of applying these changes).

 2. Do a full table extract, change the table definition manually, and then reload the data back into the table.

3. turn on parallelism for ddl operations (alter session enable parallel ddl), note that this may not be appropriate based on the number of partitions, processors and disk channels available to you.

I tend to recommend option 2, as it is the easiest to implement across all installations, and is guaranteed to have the desired effect (as long as the changes applied manually align with the upgrade script expectations, otherwise the script will simply attempt to align the table definition, and negate all of the manual effort expended).

About

Stuart Ramage

I am a Consulting Technical Director for Oracle Corporation, and a member of the OU Black Belt Team, based in Hobart Tasmania.
I have worked in the Utility arena since 1999 on the Oracle UGBU product line, in a variety of roles including Conversion, Technical and Functional Architect.

Contact me on:

Search

Categories
Archives
« April 2015
SunMonTueWedThuFriSat
   
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
  
       
Today