In a recent project the instruction was to validate a telephone input field. Simple enough you might think in peoplecode?

The answer is actually yes, but searching for a reference code I quickly discovered this has not been done a lot in peoplecode. So I decide to share a solution code snippets in contrast to Javascript en Java validation of which I have a relative experience.

The code snippet in the image uses a regex (Regular Expressions) used in both Javascript en Java to do sophisticated pattern matching, which can often be helpful in form validation. Which can be used to check whether a phone number or an email address entered into a form field is syntactically correct.

Code: http://blog.stevenlevithan.com/archives/validate-phone-number

The regex imports the regex Java class and uses it in the same way as Javascript.

Code: http://www.mkyong.com/java/how-do-validate-phone-number-in-java-regular-expression/

To replicate the above Javascript and Java phone validation code in peoplecode, the checks below was built into the phone field of the Component.

Note: The format below took account of not so standard numbers like 09000-000 etc. So it still depends on you to determine the required format and adjust the code accordingly.

1. Login to the database using Toad, and create a job as follows

2. Specify first Execution and subsequent executions as follows and type the SQL in “What to execute text box”

3. Following is the PL/SQL for job scheduler to execute the SQL command once on the first of each month

4. Following is the SQL which was scheduled

5. Complete code

DECLARE

X NUMBER;

BEGIN

SYS.DBMS_JOB.SUBMIT

(

job => X

,what => ‘delete from table_name

where date_column <= to_date(”” 01-01-2009 ””,””dd-mm-yyyy””);’

,next_date => to_date(’09/09/2009 00:14:10′,’mm/dd/yyyy hh24:mi:ss’)

,interval => ‘TRUNC(LAST_DAY(SYSDATE)) + 1′

,no_parse => FALSE

);

:JobNumber := to_char(X);

END;

In Release 11i you could setup suspense accounting via enabling a checkbox and filling a code combination field. However in R12 there is no checkbox anymore. So be aware when you upgrade from 11i to R12 that the suspense accounting can be activated without knowing.

In R12 Oracle only looks if you have filled the code combination field. So if you have switched off the suspense account checkbox in 11i (on Set Of Books)  but the code combination field was filled, the suspense accounting will automatically be activated when you upgrade to R12.

Within Oracle R12 it’s possible to Personalize Self-Service pages. To enable this option you need to set the the following  profile options at user level.

• Personalize Self-Service Defn
• FND: Personalization Region Link Enabled

After setting the above profile options you can see now different region links which you can click on and do personalization for.

• Navigate> Functional Administrator Responsibility
• Click Personalization tab
• Fill in the Application Name or Document Path
• Select you personalization
• Choose one of the options like;  Delete Personalization or Deactivate

In Release 12 Oracle has introduced new functionality Called ‘Alternate Account’. This new functionality is very usefull when when you have major changes in your account code combinations. Recently we have made major changes in the Accounting Flexfield for a customer and we have end-dated all active code combinations and defined a whole new range of code combinations. The new range was succesfully added to the old disabled range using the alternate account field.

With this great functionality we were able to implement a great and major change fast and at the same time we did not had to change the accounting created on already entered transactions (with old and disabled code combinations) in the subledgers (AP/AR). 

When Transactions in the subledgers are send to GL with old and end-dated code combinations, GL automatically replaces the code combination during Journal import what results into the alternate account when you post the journal. Of course there are some attention points that you have to take into account when implementing this solution but I’m very exited about this functionality!  See below picture the screen where you can use this new field.

Some of the attention points you have to consider are:

1. Alternate account functionality does not work (yet) for encumbrance accounting.
2. When you reconcile GL code combination balances with Subledger (AP/AR) balances at period-end you have to include the alternate account functionality because in the Subledger (AP/AR) the Balance is on the disabled account and in GL the Balance is on the Alternate account.

If you’re interested in using this new and great functionality and you want to know the whole story please let me know.