Friday 31 January 2014

R12 Subledger Accounting Process Events

You can run accounting in two ways, one is Online other is batch mode. Online accounting is ideal when you want to generate accounting event for one transaction. Batch mode accounting process will be initiated by submitting accounting concurrent request (Create Accounting, Submit accounting)

There is no difference if you run online accounting or batch accounting in terms of the event generation. Only different is in detailed reporting

Tables that are involved in this process are

XLA_TRANSACTION_ENTITIES
XLA_EVENTS
XLA_AE_HEADERS
XLA_AE_LINES
GL_IMPORT_REFERENCES
GL_JE_LINES
GL_JE_HEADERS
GL_BALANCES

Links Between these tables

GL_JE_LINES (JE_HEADER_ID, JE_LINE_NUM)   
   

GL_IMPORT_REFERENCES (JE_HEADER_ID, JE_LINE_NUM)

GL_IMPORT_REFERENCES (GL_SL_LINK_TABLE, GL_SL_LINK_ID
   

XLA_AE_LINES (GL_SL_LINK_TABLE, GL_SL_LINK_ID)

XLA_AE_LINES (APPLICAITON_ID, AE_HEADER_ID)
   

XLA_AE_HEADERS (APPLICATION_ID, AE_HEADER_ID)

XLA_AE_HEADERS (APPLICATION_ID, EVENT_ID)
   

XLA_EVENTS (APPLICATION_ID, EVENT_ID)

XLA_EVENTS (APPLICATION_ID, ENTITY_ID)
   

XLA_TRANSACTION_ENTITIES (APPLICATION_ID, ENTITY_ID)



















Draft : Draft will create journal entries, which are not final, which means they are not ready to be transferred to GL.

You can create accounting on this transaction again and again, which will delete the old journal entries and create new ones. You can’t transfer these journal entries to GL.

xla_events.process_status_code = D
xla_events.event_status_code = U
xla_ae_headers.accounting_entry_status_code = D

Final : Final will create journal entries, which can be transferred to GL. Once it is finally accounted you can’t run create accounting on the particular transaction (specifically on that event).

Run Transfer Journal Entries to GL program

xla_events.process_status_code = P
xla_events.event_status_code = P
xla_ae_headers.accounting_entry_status_code = F

Final Post: Final Post will create journal entries in final mode, transfer them to GL and post them.

xla_ae_headers.accounting_entry_status_code = F
xla_ae_headers.transfer_status_code = Y
xla_events.process_status_code = P
xla_events.event_status_code = P

How to find log files locations in 11i and R12

The following log files location could help you to find-out issues and errors from your application 11i instance.

Database Tier Logs are

Alert Log File location:
$ORACLE_HOME/admin/$CONTEXT_NAME/bdump/alert_$SID.log

Trace file location:
$ORACLE_HOME/admin/SID_Hostname/udump

Application Tier Logs

Start/Stop script log files location:
$COMMON_TOP/admin/log/CONTEXT_NAME/ 

OPMN log file location
$ORACLE_HOME/opmn/logs/ipm.log

Apache, Jserv, JVM log files locations:
$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_engine_log
$IAS_ORACLE_HOME/Apache/Apache/logs/ssl_request_log
$IAS_ORACLE_HOME/Apache/Apache/logs/access_log
$IAS_ORACLE_HOME/Apache/Apache/logs/error_log
$IAS_ORACLE_HOME/Apache/JServ/logs

Concurrent log file location:
$APPL_TOP/admin/PROD/log or $APPLLOG/$APPLCSF

Patch log file location:
$APPL_TOP/admin/PROD/log

Worker Log file location:
$APPL_TOP/admin/PROD/log

AutoConfig log files location:
Application Tier:
$APPL_TOP/admin/SID_Hostname/log//DDMMTime/adconfig.log

Database Tier:
$ORACLE_HOME/appsutil/log/SID_Hostname/DDMMTime/adconfig.log

Error log file location:
Application Tier:
$APPL_TOP/admin/PROD/log

Database Tier :
$ORACLE_HOME/appsutil/log/SID_Hostname

In Oracle Applications R12, the log files are located in $LOG_HOME (which translates to $INST_TOP/logs)
Below list of log file locations could be helpful for you:

Concurrent Reqeust related logs
$LOG_HOME/appl/conc - > location for concurrent requests log and out files
$LOG_HOME/appl/admin - > location for mid tier startup scripts log files

Apache Logs (10.1.3 Oracle Home which is equivalent to iAS Oracle Home - Apache, OC4J and OPMN)
$LOG_HOME/ora/10.1.3/Apache - > Location for Apache Error and Access log files
$LOG_HOME/ora/10.1.3/j2ee - > location for j2ee related log files
$LOG_HOME/ora/10.1.3/opmn - > location for opmn related log files

Forms & Reports related logs (10.1.2 Oracle home which is equivalent to 806 Oracle Home)
$LOG_HOME/ora/10.1.2/forms
$LOG_HOME/ora/10.1.2/reports

Startup/Shutdown Log files location:
$INST_TOP/apps/$CONTEXT_NAME/logs/appl/admin/log

Patch log files location:
$APPL_TOP/admin/$SID/log/ 

Clone and AutoConfig log files location in Oracle E-Business Suite Release 12 

Logs for the adpreclone.pl are located: 
On the database tier: 
RDBMS $ORACLE_HOME/appsutil/log/< context >/StageDBTier_< timestamp >.log 

On the application tier: 
$INST_TOP/admin/log/StageAppsTier_< timestamp >.log 

Where the logs for the admkappsutil.pl are located? 
On the application tier: 
$INST_TOP/admin/log/MakeAppsUtil_< timestamp >.log 

Logs for the adcfgclone.pl are located:


On the database tier: 
RDBMS $ORACLE_HOME/appsutil/log/< context >/ApplyDBTier_< timestamp >.log 

On the application tier: 
$INST_TOP/admin/log/ApplyAppsTier_< timestamp >.log 
Logs for the adconfig are located: 

On the database tier: 
RDBMS $ORACLE_HOME/appsutil/log/< context >/< timestamp >/adconfig.log 
RDBMS $ORACLE_HOME/appsutil/log/< context >/< timestamp >/NetServiceHandler.log 

On the application tier: 
$INST_TOP/admin/log/< timestamp >/adconfig.log 
$INST_TOP/admin/log/< timestamp >/NetServiceHandler.log

Thursday 30 January 2014

Regular Expression Support in Oracle (REGEXP_COUNT, REGEXP_INSTR, REGEXP_REPLACE, REGEXP_SUBSTR, REGEXP_LIKE)

Related articles.

Introduction

Oracle 10g introduced support support for regular expressions in SQL and PL/SQL with the following functions.
  • REGEXP_INSTR - Similar to INSTR except it uses a regular expression rather than a literal as the search string.
  • REGEXP_LIKE - Similar to LIKE except it uses a regular expression as the search string. REGEXP_LIKE is really an operator, not a function.
  • REGEXP_REPLACE - Similar to REPLACE except it uses a regular expression as the search string.
  • REGEXP_SUBSTR - Returns the string matching the regular expression. Not really similar to SUBSTR.
Oracle 11g introduced two new features related to regular expressions.
  • REGEXP_COUNT - Returns the number of occurrences of the regular expression in the string.
  • Sub-expression support was added to all regular expression functions by adding a parameter to each function to specify the sub-expression in the pattern match.
Learning to write regular expressions takes a little time. If you don't do it regularly, it can be a voyage of discovery each time. The general rules for writing regular expressions are available here. You can read the Oracle Regular Expression Support here.
Rather than trying to repeat the formal definitions, I'll present a number of problems I've been asked to look at over the years, where a solution using a regular expression has been appropriate.

Example 1 : REGEXP_SUBSTR

The data in a column is free text, but may include a 4 digit year.
DROP TABLE t1;
CREATE TABLE t1 (
  data VARCHAR2(50)
);

INSERT INTO t1 VALUES ('FALL 2014');
INSERT INTO t1 VALUES ('2014 CODE-B');
INSERT INTO t1 VALUES ('CODE-A 2014 CODE-D');
INSERT INTO t1 VALUES ('ADSHLHSALK');
INSERT INTO t1 VALUES ('FALL 2004');
COMMIT;

SELECT * FROM t1;

DATA
----------------------------------------------------------------------------------------------------
FALL 2014
2014 CODE-B
CODE-A 2014 CODE-D
ADSHLHSALK
FALL 2004

5 rows selected.

SQL>
If we needed to return rows containing a specific year we could use the LIKE operator (WHERE data LIKE '%2014%'), but how do we return rows using a comparison (<, <=, >, >=, <>)?
One way to approach this is to pull out the 4 figure year and convert it to a number, so we don't accidentally do an ASCII comparison. That's pretty easy using regular expressions.
We can identify digits using the "\d" or "[0-9]" operators. We want a group of four of them, which is represented by the "{4}" operator. So our regular expression will be "\d{4}" or "[0-9]{4}". The REGEXP_SUBSTR function returns the string matching the regular expression, so that can be used to extract the text of interest. We then just need to convert it to a number and perform our comparison.
SELECT *
FROM   t1
WHERE  TO_NUMBER(REGEXP_SUBSTR(data, '\d{4}')) >= 2014;

DATA
----------------------------------------------------------------------------------------------------
FALL 2014
2014 CODE-B
CODE-A 2014 CODE-D

3 rows selected.

SQL>

Example 2 : REGEXP_SUBSTR

Given a source string, how do we split it up into separate columns, based on changes of case and alpha-to-numeric, such that this.
ArtADB1234567e9876540 
Becomes this.
Art ADB 1234567 e 9876540
The source data is set up like this.
DROP TABLE t1;
CREATE TABLE t1 (
  data VARCHAR2(50)
);

INSERT INTO t1 VALUES ('ArtADB1234567e9876540');
COMMIT;
The first part of the string is an initcap word, so it starts with a capital letter between "A" and "Z". We identify a single character using the "[]" operator, and ranges are represented using "-", like "A-Z", "a-z" or "0-9". So if we are looking for a single character that is a capital letter, we need to look for "[A-Z]". That needs to be followed by lower case letters, which we now know is "[a-z]", but we need 1 or more of them, which is signified by the "+" operator. So to find an initcap word, we need to search for "[A-Z][a-z]+". Since we want the first occurrence of this, we can use the following.
REGEXP_SUBSTR(data, '[A-Z][a-z]+', 1, 1)
The second part of the string is a group of 1 or more uppercase letters. We know we need to use the "[A-Z]+" pattern, but we need to make sure we don't get the first capital letter, so we look for the second occurrence.
REGEXP_SUBSTR(data, '[A-Z]+', 1, 2)
The next part is the first occurrence of a group of numbers.
REGEXP_SUBSTR(data, '[0-9]+', 1, 1)
The next part is a group of lower case letters. We don't to pick up those from the initcap word, so we must look for the second occurrence of lower case letters.
REGEXP_SUBSTR(data, '[a-z]+', 1, 2)
Finally, we have a group of numbers, which is the second occurrence of this pattern.
REGEXP_SUBSTR(data, '[0-9]+', 1, 2)
Putting that all together, we have the following query, which splits the data into separate columns.
COLUMN col1 FORMAT A15
COLUMN col2 FORMAT A15
COLUMN col3 FORMAT A15
COLUMN col4 FORMAT A15
COLUMN col5 FORMAT A15

SELECT REGEXP_SUBSTR(data, '[A-Z][a-z]+', 1, 1) col1,
       REGEXP_SUBSTR(data, '[A-Z]+', 1, 2) col2,
       REGEXP_SUBSTR(data, '[0-9]+', 1, 1) col3,
       REGEXP_SUBSTR(data, '[a-z]+', 1, 2) col4,
       REGEXP_SUBSTR(data, '[0-9]+', 1, 2) col5
FROM   t1;

COL1            COL2            COL3            COL4            COL5
--------------- --------------- --------------- --------------- ---------------
Art             ADB             1234567         e               9876540

1 row selected.

SQL>

Example 3 : REGEXP_SUBSTR

We need to pull out a group of characters from a "/" delimited string, optionally enclosed by double quotes. The data looks like this.
DROP TABLE t1;
CREATE TABLE t1 (
  data VARCHAR2(50)
);

INSERT INTO t1 VALUES ('978/955086/GZ120804/10-FEB-12');
INSERT INTO t1 VALUES ('97/95508/BANANA/10-FEB-12');
INSERT INTO t1 VALUES ('97/95508/"APPLE"/10-FEB-12');
COMMIT;
We are looking for 1 or more characters that are not "/", which we do using "[^/]+". The "^" in the brackets represents NOT and "+" means 1 or more. We also want to remove optional double quotes, so we add that as a character we don't want, giving us "[^/"]+". So if we want the data from the third column, we need the third occurrence of this pattern.
SELECT REGEXP_SUBSTR(data, '[^/"]+', 1, 3) AS element3
FROM   t1;

ELEMENT3
----------------------------------------------------------------------------------------------------
GZ120804
BANANA
APPLE

3 rows selected.

SQL>

Example 4 : REGEXP_REPLACE

We need to take an initcap string and separate the words. The data looks like this.
DROP TABLE t1;
CREATE TABLE t1 (
  data VARCHAR2(50)
);

INSERT INTO t1 VALUES ('SocialSecurityNumber');
INSERT INTO t1 VALUES ('HouseNumber');
COMMIT;
We need to find each uppercase character "[A-Z]". We want to keep that character we find, so we will make that pattern a sub-expression "([A-Z])", allowing us to refer to it later. For each match, we want to replace it with a space, plus the matching character. The space is pretty obvious, but we need to use "\1" to signify the text matching the first sub expression. So we will replace the matching pattern with a space and itself, " \1". We don't want to replace the first letter of the string, so we will start at the second occurrence.
SELECT REGEXP_REPLACE(data, '([A-Z])', ' \1', 2) AS hyphen_text
FROM   t1;
  
HYPHEN_TEXT
----------------------------------------------------------------------------------------------------
Social Security Number
House Number

2 rows selected.

SQL>

Example 5 : REGEXP_INSTR

We have a specific pattern of digits (9 99:99:99) and we want to know the location of the pattern in our data.
DROP TABLE t1;
CREATE TABLE t1 (
  data VARCHAR2(50)
);

INSERT INTO t1 VALUES ('1 01:01:01');
INSERT INTO t1 VALUES ('.2 02:02:02');
INSERT INTO t1 VALUES ('..3 03:03:03');
COMMIT;
We know we are looking for groups of numbers, so we can use "[0-9]" or "\d". We know the amount of digits in each group, which we can indicate using the "{n}" operator, so we simply describe the pattern we are looking for.
SELECT REGEXP_INSTR(data, '[0-9] [0-9]{2}:[0-9]{2}:[0-9]{2}') AS string_loc_1,
       REGEXP_INSTR(data, '\d \d{2}:\d{2}:\d{2}') AS string_loc_2
FROM   t1;

STRING_LOC_1 STRING_LOC_2
------------ ------------
           1            1
           2            2
           3            3

3 rows selected.

SQL>

Example 6 : REGEXP_LIKE and REGEXP_SUBSTR

We have strings containing parentheses. We want to return the text within the parentheses for those rows that contain parentheses.
DROP TABLE t1;
CREATE TABLE t1 (
  data VARCHAR2(50)
);

INSERT INTO t1 VALUES ('This is some text (with parentheses) in it.');
INSERT INTO t1 VALUES ('This text has no parentheses.');
INSERT INTO t1 VALUES ('This text has (parentheses too).');
COMMIT;
The basic pattern for text between parentheses is "\(.*\)". The "\" characters are escapes for the parentheses, making them literals. Without the escapes they would be assumed to define a sub-expression. That pattern alone is fine to identify the rows of interest using a REGEXP_LIKE operator, but it is not appropriate in a REGEXP_SUBSTR, as it would return the parentheses also. To omit the parentheses we need to include a sub-expression inside the literal parentheses "\((.*)\)". We can then REGEXP_SUBSTR using the first sub expression.
COLUMN with_parentheses FORMAT A20
COLUMN without_parentheses FORMAT A20

SELECT data,
       REGEXP_SUBSTR(data, '\(.*\)') AS with_parentheses,
       REGEXP_SUBSTR(data, '\((.*)\)', 1, 1, 'i', 1) AS without_parentheses
FROM   t1
WHERE  REGEXP_LIKE(data, '\(.*\)');

DATA                                               WITH_PARENTHESES     WITHOUT_PARENTHESES
-------------------------------------------------- -------------------- --------------------
This is some text (with parentheses) in it.        (with parentheses)   with parentheses
This text has (parentheses too).                   (parentheses too)    parentheses too

2 rows selected.

SQL>

Example 7 : REGEXP_COUNT

We need to know how many times a block of 4 digits appears in text. The data looks like this.
DROP TABLE t1;
CREATE TABLE t1 (
  data VARCHAR2(50)
);

INSERT INTO t1 VALUES ('1234');
INSERT INTO t1 VALUES ('1234 1234');
INSERT INTO t1 VALUES ('1234 1234 1234');
COMMIT;
We can identify digits using "\d" or "[0-9]" and the "{4}" operator signifies 4 of them, so using "\d{4}" or "[0-9]{4}" with the REGEXP_COUNT function seems to be a valid option.
SELECT REGEXP_COUNT(data, '[0-9]{4}') AS pattern_count_1,
       REGEXP_COUNT(data, '\d{4}') AS pattern_count_2
FROM   t1;

PATTERN_COUNT_1 PATTERN_COUNT_2
--------------- ---------------
              1               1
              2               2
              3               3

3 rows selected.

SQL>

Example 8 : REGEXP_LIKE

We need to identify invalid email addresses. The data looks like this.
DROP TABLE t1;
CREATE TABLE t1 (
  data VARCHAR2(50)
);

INSERT INTO t1 VALUES ('me@example.com');
INSERT INTO t1 VALUES ('me@example');
INSERT INTO t1 VALUES ('@exmaple.com');
INSERT INTO t1 VALUES ('me.me@example.com');
INSERT INTO t1 VALUES ('me.me@ example.com');
INSERT INTO t1 VALUES ('me.me@example-example.com');
COMMIT;
The following test gives us email addresses that approximate to invalid email address formats.
SELECT data
FROM   t1
WHERE  NOT REGEXP_LIKE(data, '[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}', 'i');

DATA
--------------------------------------------------
me@example
@exmaple.com
me.me@ example.com

3 rows selected.

SQL>
=======================================================
Divide the row to multi - row
===================
 REGEXP_SUBSTR (
              REPLACE (REPLACE (COLUMN_NAME, '::', ' '), ':', ''),
              '[0-9]+',
              1,
              1
           )
              "name123456",
           REGEXP_SUBSTR (
              REPLACE (REPLACE (COLUMN_NAME, '::', ','), ':', ''),
              '[0-9]+',
              1,
              2
           )
              "name",


For more information see:
Hope this helps. Regards Tim...

nternal error has occurred in the program xla_accounting_pkg.ValidateAAD

The error is farely common in FAH. In normal case, if the Accounting Application Definition (AAD) is not validated, EBS Create Accounting - Accounting Program ends with this error.

An internal error has occurred in the program xla_accounting_pkg.ValidateAAD.  ORA-0000: normal, successful completion.
If you run Validate Application Accounting Definition program from EBS View menu > Run, in most cases the problem gets resolved. You can rerun the problematic Create Accounting again.

I have an experience where running this program didn't resolve the issue. I had to validate my AAD from the FAH AAD window, by clicking the Validate button on that screen. It resolved the issue and I could run Accounting program normally.

Wednesday 29 January 2014

Oracle D2K form ( Block Detail )

Block is logical owner of items. It provides a mechanism for grouping related items into a functional unit for storing, displaying and manipulating records.

Types of Blocks

1. Data Blocks

Data blocks are associated with data (table columns) within a database.
By default, the association between a data block and the database allows operators to automatically query, update, insert, and delete rows within a database.

Data blocks can be based on database tables, views, procedures, or transactional
triggers.

2. Control Blocks

A control block is not associated with the database, and the items in a control block do not relate to table columns within a database.

All blocks are either single-record or multi-record blocks:

A single-record block displays one record at a time.
A multi-record block displays more than one record at a time.

In addition, a data block can also be a master or detail block:

Master block displays a master record associated with detail records displayed in a detail block.

A detail block displays detail records associated with a master record displayed in master block.

Block Built - ins
1. BLOCK_MENU built-in
Displays a list of values (LOV) containing the sequence number and names of valid blocks in your form. Form Builder sets the input focus to the first enterable item in the block you select from the LOV.

Example:
/*
** Built–in: BLOCK_MENU ** Example: Calls up the list of blocks in the form when the
** user clicks a button, and prints a message if ** the user chooses a new block out of the list
to ** which to navigate. */

DECLARE
prev_blk VARCHAR2(40) := :System.Cursor_Block;
BEGIN
BLOCK_MENU;
IF :System.Cursor_Block <> prev_blk THEN
Message(’You successfully navigated to a new block!’);
END IF;
END;

2. CLEAR_BLOCK built-in


Causes Form Builder to remove all records from, or "flush," the current block.

Clear_Block(No_Validate);


COMMIT_MODE
The optional action parameter takes the following possible constants as arguments:

ASK_COMMIT
Form Builder prompts the end user to commit the changes during CLEAR_BLOCK
processing.

DO_COMMIT
Form Builder validates the changes, performs a commit, and flushes the current block without prompting the end user.

NO_COMMIT
Form Builder validates the changes and flushes the current block without performing a commit or prompting the end user.

NO_VALIDATE
Form Builder flushes the current block without validating the changes, committing the changes, or prompting the end user.

3. FIND_BLOCK

Searches the list of valid blocks and returns a unique block ID. You must define an appropriately typed variable to accept the return value. Define the variable with a type of Block.

4. GET_BLOCK_PROPERTY

Returns information about a specified block. You must issue a call to the built-in once for each property value you want to retrieve.

Syntax:
GET_BLOCK_PROPERTY( block_id, property);
GET_BLOCK_PROPERTY( block_name, property);


** Determine the (1) Current Record the cursor is in,
** (2) Current Record which is visible at the
** first (top) line of the multirecord
** block.
*/
cur_rec := Get_Block_Property( bk_id, CURRENT_RECORD);
top_rec := Get_Block_Property( bk_id, TOP_RECORD);

5. GO_BLOCK

GO_BLOCK navigates to an indicated block. If the target block is non-enterable , an
error occurs.

6. ID_NULL

Returns a BOOLEAN value that indicates whether the object ID is available.

7. NEXT_BLOCK

Navigates to the first navigable item in the next enterable block in the navigation
sequence

8.PREVIOUS_BLOCK

Navigates to the first navigable item in the previous enterable block in the navigation
sequence

9.SET_BLOCK_PROPERTY

Sets the given block characteristic of the given block.

Syntax:
SET_BLOCK_PROPERTY( block_id, property, value);
SET_BLOCK_PROPERTY( block_name, property, value);


Example:
/* ** Built–in: SET_BLOCK_PROPERTY
** Example: Prevent future inserts, updates, and deletes to ** queried records in the block whose name is ** passed as an argument to this procedure. */

PROCEDURE Make_Block_Query_Only( blk_name IN VARCHAR2 )
IS
blk_id Block;
BEGIN
/* Lookup the block’s internal ID */

blk_id := Find_Block(blk_name);

/* ** If the block exists (ie the ID is Not NULL) then set ** the three properties for this block. Otherwise signal ** an error. */

IF NOT Id_Null(blk_id) THEN
Set_Block_Property(blk_id,INSERT_ALLOWED,PROPERTY_FALSE);
Set_Block_Property(blk_id,UPDATE_ALLOWED,PROPERTY_FALSE);
Set_Block_Property(blk_id,DELETE_ALLOWED,PROPERTY_FALSE);
ELSE
Message(’Block ’blk_name’ does not exist.’);
RAISE Form_Trigger_Failure;
END IF;
END;


Block - System Variables

1.SYSTEM.BLOCK_STATUS

SYSTEM.BLOCK_STATUS represents the status of a Data block where the cursor is located, or the current data block during trigger processing. The value can be one of three character strings:

CHANGED Indicates that the block contains at least one Changed record.
NEW Indicates that the block contains only New records.
QUERY Indicates that the block contains only Valid records that have been retrieved
from the database.

Example:
Assume that you want to create a trigger that performs a commit before clearing a block if there are changes to commit within that block.

The following Key–CLRBLK trigger performs this function.

IF :System.Block_Status = ’CHANGED’
THEN Commit_Form;
END IF;
Clear_Block;


2.SYSTEM.CURRENT_BLOCK

The value that the SYSTEM.CURRENT_BLOCK system variable represents depends on the
current navigation unit:

If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item,
Record, and Block triggers), the value of SYSTEM.CURRENT_BLOCK is the name of the block
that Form Builder is processing or that the cursor is in.

If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value of
SYSTEM.CURRENT_BLOCK is NULL.

3.SYSTEM.CURSOR_BLOCK

The value that the SYSTEM.CURSOR_BLOCK system variable represents depends on the
current navigation unit:

If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item,
Record, and Block triggers), the value of SYSTEM.CURSOR_BLOCK is the name of the block where the cursor is located. The value is always a character string.

If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value of
SYSTEM.CURSOR_BLOCK is NULL.

Example:

Assume that you want to create a Key–NXTBLK trigger at the form level that navigates depending on what the current block is. The following trigger performs this function, using
:SYSTEM.CURSOR_BLOCK stored in a local variable.

DECLARE
curblk VARCHAR2(30);
BEGIN
curblk := :System.Cursor_Block;

IF curblk = ’ORDERS’ THEN
Go_Block(’ITEMS’);
ELSIF curblk = ’ITEMS’ THEN
Go_Block(’CUSTOMERS’);
ELSIF curblk = ’CUSTOMERS’ THEN
Go_Block(’ORDERS’);
END IF;
END;

4. SYSTEM.MASTER_BLOCK

This system variable works with its companion SYSTEM.COORDINATION_OPERATION to help an On-Clear-Details trigger determine what type of coordination-causing operation fired the trigger, and on which master block of a master/detail relation.

5. SYSTEM.TRIGGER_BLOCK

SYSTEM.TRIGGER_BLOCK represents the name of the block where the cursor was located when the current trigger initially fired. The value is NULL if the current trigger is a Pre- or Post-Form trigger. The value is always a character string.


Example:

Assume that you want to write a form–level procedure that navigates to the block where the cursor was when the current trigger initially fired. The following statement performs this function.

Go_Block(Name_In(’System.Trigger_Block’));

Block – Based Triggers [Block Processing Trigger]

When-Create-Record, When-Clear-Block, When-Database-Record, When-Remove-Record

How to Convert the Column To Row in Table

SELECT      (SELECT   'Manager Note - '||APPROVER_COMMENTS
               FROM   < Table Name >
              WHERE   approval_seq = 1 AND leave_req_id = :p_leave_req_id)
         || ' '
         || (SELECT  'HR Note - ' || APPROVER_COMMENTS
               FROM   < Table Name >               WHERE   approval_seq = 2 AND leave_req_id = :p_leave_req_id) mana_hr_note
--  INTO   l_man_hr_note
  FROM   DUAL;
 
 
  This leave request approve by Elangovan Ragavan.. Approved by Arun
 
 
select user_id,listagg (login_id, ',') WITHIN GROUP (ORDER BY login_id) login_id from (select user_id,login_id from fnd_logins
where SESSION_NUMBER < (200)
and user_id in (1179,1178)
) group by user_id

select listagg (APPROVER_COMMENTS, ',') WITHIN GROUP (ORDER BY APPROVER_COMMENTS) login_id
from < Table Name >   where leave_req_id = p_leave_req_id

select listagg (APPROVER_COMMENTS, ',') WITHIN GROUP (ORDER BY approval_seq) login_id
from < Table Name >where leave_req_id = :p_leave_req_id

column width in an APEX report


Most of you would say: "Why is that so special? Just go to
Report Attributes>Column Attributes>Column Formatting>CSS Style and add for example: width:100px"

Like the help says:
"Use this attribute to apply a style to a column value. For example, setting this attribute to 'color:#FF0000;' will result in the following html being generated:
<span style="color:#FF0000">Sample Data</span>
This will change the text color of the column to red."

You all are right but there is a problem with Firefox which is ignoring this setting.
Why? Take a look here:
http://www.velocityreviews.com/forums/t163666-firefox-ignores-the-style-width-attribute-in-the-span-tag.html

How to fix that now? I use the div tag in my sql statement for the report:
  1. SELECT '<b><div style="width: 150px; text-align: left">Employee no.: ' || "EMP"."EMPNO" ||  
  2.        '</div></b>' as "EMPNO",  
  3.        '<div style="border: 1px solid rgb(204, 204, 204); padding: 2px; width: 150px; background-color: rgb(225, 225, 225); text-align: center">' ||  
  4.        "EMP"."ENAME" || '</div>' as "ENAME",  
  5.        '<div style="border: 1px solid rgb(204, 204, 204); padding: 2px; width: 100px; background-color: rgb(225, 225, 225); text-align: center">' ||  
  6.        "EMP"."HIREDATE" || '</div>' as "HIREDATE",  
  7.        '<div style="border: 1px solid rgb(204, 204, 204); padding: 2px; width: 100px; background-color: rgb(225, 225, 225); text-align: center">' ||  
  8.        "EMP"."SAL" || '</div>' as "SAL",  
  9.        '<div style="border: 1px solid rgb(204, 204, 204); padding: 2px; width: 100px; background-color: rgb(225, 225, 225); text-align: center">' ||  
  10.        "EMP"."COMM" || '</div>' as "COMM"  
  11.   FROM "EMP"  
  12. -- </div> - div tag is needed to create the css style attributes  
  13. -- "width: 150px;" - for column width  
  14. -- "text-align: left" - for text alignment  
  15. -- "background-color: rgb(225, 225, 225)" - for column background color  
  16. -- "border: 1px solid rgb(204, 204, 204)" - for border color  
  17. -- "padding: 2px" - for empty place between text and border  


Example application in action: http://apex.oracle.com/pls/otn/f?p=25472:30

Forum entry to it: http://forums.oracle.com/forums/thread.jspa?forumID=137&threadID=854151

Honestly I think there is another way to fix this issue which I don't know yet but I would really appreciate to get to know about it. :D

To all: Enjoy the weekend, have fun and DON'T think about the world economic crisis. :)

Tuesday 28 January 2014

How to change the font style in a Apex text field

You can change the font style of a text displayed in a text field or text area by specifying the style attributes in "HTML Form Element Attributes" of a field as shown in below.

How to add hyper link to Apex Report

Assume we have a student details DB table having necessary student details including a column having home page link of individual students if they have one. Student details needs to be displayed in an apex report. If a student has a home page then it should be displayed as a hyper link in the report else cell should be blank.

EP_STUDENT_DETAILS Table structure

When adding the select query to pull the data for your Apex Report you have to add a case statement as given below. Which would check if the link column is empty, if not will generate a html hyper link tag and place the Home page link value for the href attribute



select "EP_STUDENT_DETAILS"."STUDENT_ID" as "STUDENT_ID",
"EP_STUDENT_DETAILS"."NAME" as "NAME",
"EP_STUDENT_DETAILS"."AGE" as "AGE",
         CASE WHEN "HOME_PAGE" IS NOT NULL THEN '<a href="'||HOME_PAGE||'">Home Page</a>' END AS "Home Page"
 from "EP_STUDENT_DETAILS" "EP_STUDENT_DETAILS"


HOME_PAGE : This is the column in the EP_STUDENT_DETAILS table have the URL for the home pages.

Tuesday 21 January 2014

Attaching the concurrent program to the request group

BEGIN
   FND_PROGRAM.add_to_group('XXMZ_TEST_EMPLOYEE' -- program_short_name
                            , 'XXMZ Custom' -- application
                            , 'xxmz Request Group' -- Report Group Name
                            , 'XXMZ'); -- Report Group Application
                        COMMIT;
END;est

Registering the Concurrent program from back end

BEGIN
       FND_PROGRAM.register('Concurrent program for Employee Information' -- program
                            , 'XXMZ Custom' -- application
                            , 'Y' -- enable
                            , 'XXMZ_TEST_EMPLOYEE' -- short_name
                            , 'TEST Employee Information' -- description
                            , 'XXMZ_TEST_EMPLOYEE' -- executable_short_name
                            , 'XXMZ Custom' -- executable_application
                            , '' -- execution_options
                            , '' -- priority
                            , 'Y' -- save_output
                            , 'Y' -- print
                            , '' -- cols
                            , '' -- rows
                            , '' -- style
                            , 'N' -- style_required
                            , '' -- printer
                            , '' -- request_type
                            , '' -- request_type_application
                            , 'Y' -- use_in_srs
                            , 'N' -- allow_disabled_values
                            , 'N' -- run_alone
                            , 'TEXT' output_type
                            , 'N' -- enable_trace
                            , 'Y' -- restart
                            , 'Y' -- nls_compliant
                            , '' -- icon_name      
                            , 'US'); -- language_code
                        COMMIT;
            END;

how to run the concurrent program in backend

    BEGIN
        FND_PROGRAM.executable('XXMZ_TEST_EMPLOYEE' -- executable
                          , 'XXMZ Custom' -- application
                          , 'XXMZ_TEST_EMPLOYEE' -- short_name
                          , 'Executable for Employee INFORMATION' -- description
                          , 'PL/SQL Stored Procedure' -- execution_method
                          , 'XXMZ_TEST_EMPLOYEE' -- execution_file_name
                          , '' -- subroutine_name
                          , '' -- Execution File Path
                          , 'US' -- language_code
                          , '');
             COMMIT;
         END;

Monday 20 January 2014

XML Basic You Need to Know

Well, what can I say... It's been a long time. We've got involved in many of BI Publisher or Advanced Reporting related projects in past months and couldn't find a time to update the blog. I know that's such a BS, so let's move on.... ;-)

Today, I'll talk about the basic of XML as part of the 'Before you start RTF Template' series.

I’m sure you have already seen XML files and known what the files look like. Especially the BI Publisher technology is designed around XML so it’s almost impossible not to see XML files when you develop any report. However, some of you might not have spent a time to learn what really the XML is more than what it looks. So, let’s spend some time (not a lot, so don’t worry!) to visit the XML basic.

What is XML ?

Mozilla Firefox


XML stands for eXtensible Markup Language. It is a W3C standard, and it was designed to describe data and to focus on what data is while a similar language called HTML was designed to display data and to focus on how data looks. XML uses a Document Type Definition(DTD) or an XML Schema to describe the structure of XML documents. Now it’s more common to use XML schema than the DTD to describe the data. And BI Publisher also uses XML Schema when it’s needed.

What are they? (Terminology)

Instead of going through all the detail of XML, I’ll focus on what’s really important for BI Publisher report developers. First, what are those tags inside the XML file? When you open the XML files you can see a bunch of tags and all of them looks same. However, there is some difference among them and each of them has its own name. The names you want to remember are the followings.

- Element
- Attribute
- Value
- Namespace
- Root
- Parent
- Child
- Sibling
- CDATA

Element

Element name is actually the main part of the XML and it acts as something like metadata. For example with the above sample XML the is the element name. With BI Publisher you use this as a place folder name when you map into RTF Template.

Attribute

Attribute is an additional metadata that tag along with the Element and add more meaning to the Element. For example with the above sample XML, ‘age’ is the attribute, which describes age of the employee in the EMPLOYEE element. And you can have multiple attributes per Element.

And Element and Attribute are the main component of the XML and the backbone of the file.

Value

Value is literally the value. Both the Element and Attribute can have their own values. The Element value is surrounded by Element start tag and Element end tag. e.g. Peter. The attribute value must be presented with double or single quotes surrounded. e.g. Peter

Node

Node is similar to something like group, unit, or family. And node can contain another node or multiple nodes inside and each node can act as an unit inside the higher level of nodes. With above sample, DEPARMENT is a node and you can find EMPLOYEE node inside the DEPARTMENT node.

Namespace

Namespace is used to differentiate the elements that have same name but means different.

Root Element

Root is something like the ‘root’ that is used for Unix file system. It means a top level Element and there is only one in one XML file.

Parent/Child/Sibling

These are the terms to describe relationships of each element. And this acts as exactly same as your family hierarchy or file system. So the node that is at one level above from where you are is called Parent node while the ones that are at one level below are called Child node. With above sample ORACLE node is parent node for DEPARTMENT element and EMPLOYEE element is child node. And this is a relative concept, so while DEPARTMENT node is a child node for ORACLE node, DEPARTMENT node is at the same time a parent node for EMPLOYEE node.

CDATA

CDATA is used for a section for a comment out area, where you can type anything. Any XML standard processor will not process the content in this area. We’ll cover in the next section about XML standard rules but the content stored in this CDATA section doesn’t apply such rules so you can basically type anything here for your use. BI Publisher uses this area with Data Template to store SQL query.

Sunday 19 January 2014

Oracle EBS Forms Personalization: Calculating an Item Value

how can i get result from the following equation through personalization
K_LINES.LINE_VALUE = K_HEADER.K_VALUE/K_HEADER.PRIME_K_NUMBER
i want to know what is the syntax and what is the required steps to get the result automatically
thanx
First off, I was flattered that anyone reads these ramblings, especially stuff from 2011.  Second I was intrigued as my immediate answer was…
hmmmmm… don’t know….
So I did a little tinkering around with forms personalization and found a solution.  Now the context of the below solution is likely not the same as the what the commenter is looking at, but I think it should still apply to his situation as I’m just looking at 2 fields and performing some arithmetic on it.   For my example, I’m using the standard Purchase Order form.  I want to automatically calculate the following:
PO_LINES.ATTRIBUTE13 = PO_LINES.UNIT_PRICE / PO_LINES.QUANTITY

In this case: Attribute13 = 35/10

To make this happen, fire up forms personalization (Help -> Diagnostics -> Custom Code -> Personalization) and first decide on when you want this calculation to happen.  In my case, I just want it to happen every time a new PO_LINE record is navigated to, so I made the Trigger Event be WHEN-NEW-RECORD-INSTANCE and the Trigger Object be PO_LINES:

You can make this be as complicated as you would like, it all depends on when you want the calculation to happen.
Now, the meat of the problem: how to do the actual calculation.  One of the nice things about forms personalization is that you can just use a query to set item values in a form.  This query can be anything (one caveat though… it has to return a charvalue!).  So for our example, a query that would achieve our goal would be:
select po_lines.unit_price / po_lines.quantity from dual
Easy enough right?  Well yes and no.  This IS the query you need, but in order to have Forms Personalization correctly interpret it there are 2 things you need to worry about.
  1. Syntax
  2. It must return a char value -> NO NUMBERS
For the syntax, you just need to know that in order to reference the actual values in the items you are using to calculate your value you need to use the syntax:
$(item.block.item.value}
For example:
select ${item.PO_LINES.UNIT_PRICE.value}/${item.PO_LINES.QUANTITY.value} from dual
For #2, it must return a CHAR.  So use the SQL function TO_CHAR!
select TO_CHAR(${item.PO_LINES.UNIT_PRICE.value}/${item.PO_LINES.QUANTITY.value}) from dual
Now plug it all into the Forms Personalization form… and you’re done.

As you can see from the above, I’m setting PO_LINES.ATTRIBUTE13′s VALUE property to the return of the select statement that we built and voila, you have a calculated field.

Thursday 9 January 2014

PERSONALIZATION - REQUISITION AMOUNT AND PROJECT BUDGET AMOUNT VALIDATION



Requirment :
If user enter requisition amount more than project budget amount then the project amount should not save.
For this purpose personalization is done on the Requisition form. Here shows the steps of personalization.
Navigation:           
  
Purchasing--> Requisitions--> Requisitions
The screen to do form personalization is
The navigation for personalization is given above
Personalization steps are given below
DESCRIPTION : Project Budget Amount line
 
Trigger Event: WHEN VALIDATE RECORD
Trigger Object: LINES
Condition:          1=1
  
Now click the actions tab. Four global variables are created for the requirement. They are
  1. Global Variable Name: XX_BUDG_AMT
               Property Name            : VALUE
                Value                             : =(select apps.xx_req_pa_budget_amt(:DISTRIBUTIONS.PROJECT_ID,:DISTRIBUTIONS.TASK_ID,:LINES.UNIT_PRICE,:DISTRIBUTIONS.REQ_LINE_QUANTITY,:DISTRIBUTIONS.DISTRIBUTION_ID,:LINES.REQUISITION_LINE_ID) from dual)
For this Global Variable a function is created. The query is given as
CREATE OR REPLACE FUNCTION APPS.xx_req_pa_budget_amt (
   p_project_id        IN   NUMBER,
   p_task_id           IN   NUMBER,
   p_unit_price        IN   NUMBER,
   p_quantity          IN   NUMBER,
   p_distribution_id   IN   NUMBER,
   p_line_id           IN   NUMBER
)
   RETURN CHAR
IS
   v_project_id        NUMBER;
   v_task_id           NUMBER;
   v_unit_price        NUMBER;
   v_quantity          NUMBER;
   v_distribution_id   NUMBER;
   l_budget_amt        NUMBER;
   l_pr_amt            NUMBER;
   l_total_pr_amt      NUMBER;
BEGIN
   v_project_id := p_project_id;
   v_task_id := p_task_id;
   v_unit_price := p_unit_price;
   v_quantity := p_quantity;
   v_distribution_id := p_distribution_id;
   IF p_distribution_id IS NULL
   THEN
      SELECT project_id, task_id, distribution_id,req_line_quantity
        INTO v_project_id, v_task_id, v_distribution_id,v_quantity
        FROM po_req_distributions_all
       WHERE requisition_line_id = p_line_id AND ROWNUM = 1;
   END IF;
   IF p_unit_price IS NULL
   THEN
      SELECT prla.unit_price
        INTO v_unit_price
        FROM po_requisition_lines_all prla, po_req_distributions_all prda
       WHERE prla.requisition_line_id = prda.requisition_line_id
         AND prda.distribution_id = p_distribution_id
         AND ROWNUM = 1;
   END IF;
   BEGIN
      SELECT NVL (SUM (burdened_cost), 0)
        INTO l_budget_amt
        FROM pa_budget_lines_v
       WHERE project_id = v_project_id
         AND task_id = v_task_id
         AND budget_version_id =
                (SELECT MAX (budget_version_id)
                   FROM pa_budget_versions pbv
                  WHERE pbv.project_id = v_project_id
                    AND pbv.budget_type_code = 'AC'
                    AND pbv.budget_status_code IN ('W', 'S'));
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         l_budget_amt := 0;
   END;
   BEGIN
      SELECT NVL (SUM (prl.quantity * prl.unit_price), 0)
        INTO l_pr_amt
        FROM po_req_distributions_all prd, po_requisition_lines_all prl
       WHERE prd.requisition_line_id = prl.requisition_line_id
         AND prd.project_id = v_project_id
         AND prd.task_id = v_task_id
         AND prd.distribution_id != NVL (v_distribution_id, 99999);
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         l_pr_amt := 0;
   END;
   l_total_pr_amt := l_pr_amt + (v_quantity * v_unit_price);
   --fnd_message.set_string('Budget Amount is '||l_budget_amt|| ' AED. Previously entered value including current PR Amount is '||l_total_pr_amt||' AED');
   --fnd_message.show;
   IF l_budget_amt != 0 AND l_budget_amt < l_total_pr_amt
   THEN
      RETURN (   'The entered amount is exceeding the allocated budget amount. Budget Amount is '
              || l_budget_amt
              || ' AED. Previously entered value including current PR Amount is '
              || l_total_pr_amt
              || ' AED'
             );
   ELSE
      RETURN NULL;
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      RETURN NULL;
END;
/
DESCRIPTION : Project Budget Amount dist
Trigger Event: WHEN VALIDATE RECORD
Trigger Object: DISTRIBUTIONS
Condition:          1=1
Now click the actions tab. Four global variables are created for the requirement. They are





Global Variable Name: XX_BUDG_AMT_DIST
               Property Name            : VALUE
                Value                             : =(select apps.xx_req_pa_budget_amt(:DISTRIBUTIONS.PROJECT_ID,:DISTRIBUTIONS.TASK_ID,:LINES.UNIT_PRICE,:DISTRIBUTIONS.REQ_LINE_QUANTITY,:DISTRIBUTIONS.DISTRIBUTION_ID,:LINES.REQUISITION_LINE_ID) from dual)



DESCRIPTION : Project Budget Amount MSG
Trigger Event: WHEN VALIDATE RECORD
Trigger Object: LINES
Condition:          :global.XX_BUDG_AMT  is  NOT NULL
Now click the actions tab. Four global variables are created for the requirement. They are
1.The message to be shown while inactivating the item is given as
                               Message Type  : Show
                               Message :          =:global.XX_BUDG_AMT
2.The Builtin should be called  Builtin Type : RAISE_FORM_TRIGGER_FAILURE.
DESCRIPTION : Project Budget Amount Msg Dist
Trigger Event: WHEN VALIDATE RECORD
Trigger Object: DISTRIBUTIONS
Condition:          :global.XX_BUDG_AMT_DIST  is  NOT NULL
Now click the actions tab. Four global variables are created for the requirement. They are
1.The message to be shown while inactivating the item is given as
                               Message Type  : Show
                               Message :          =:global.XX_BUDG_AMT_DIST4
2.The Builtin should be called
                               Builtin Type : RAISE_FORM_TRIGGER_FAILURE.
Validate these conditions and save it.
The form personalization works as above