, , ,

How to use If Else Conditions in XML Publisher RTF Template

XML Publisher supports the common programming construct “if”,”if-then-else”. This is extremely useful when you need to test a condition and conditionally show a result.

We can use if condition directly by writing <?IF?> condition <?END IF?>

Example:
<?if:P_PM_YN=’N’?> Yes <?end if?>
But for If-else we need to use extended function.
 
Method 1:

<?xdofx:if element_condition then result1 else result2 end if?>

Example:

<?xdofx:if INVOICE_AMOUNT > 5000 then ’Higher’
else
if INVOICE_AMOUNT <3000 then ’Lower’
else
’Equal’
end if?>
 
Method 2:
Syntax:

<?xdoxslt:ifelse(condition,true,false)?>

Example: 

<?xdoxslt:ifelse(20=21,’yes 20 and 21 are equal’,’No 20 and 21 are not equal’)?>

Ans: No 20 and 21 are not equal

 

Using OR Condition in XML RTF Template:

Syntax:

<?if:XMLfield=value1 or XMLfield=value2?> display value <?end if?>

Example:

<?if:sum(AVALUE)=0 or sum(BVALUE)=0?>0<?end if?>

You can use whichever is applicable to your requirement.
/by
, , ,

How to Use Flexfields in Custom forms

There are several complicated things you may need to do with flexfields in your forms but one of them is simple and very common: create a foreign key on the accounting flexfield. It is easy to get lost in the Oracle Application Developer’s Guide because it tries to cover comprehensively what can be done with flexfields. But there is nothing to show you the basics step-by-step, which is what we will attempt in this article.
The end result of what we show is illustrated in Figure 1: one field that brings up the standard flexfield popup when the user asks for a list of values.
Figure 1: The end result of this step-by-step article.
A basic conceptual understanding of what flexfields are is necessary in order to understand what follows. There is a very good 2-3 page explanation in the chapter “Flexfields” of the Oracle Application Developer’s Guide.

Step 1: Create a FK Column

First you need to define a column in the base table maintained by your form. This column will be the foreign key on the unique ID of the combination table, in our case: GL_CODE_COMBINATIONS. So you need a number(38) mandatory column in your table.

Step 2: Create a Flexfield hidden item

In your form you need to create a hidden item for the FK column. This field needs to be hidden, that is set to canvas null. It should use the TEXT_ITEM property class which comes from the Oracle Application template. Set the query length to 2000 just to be on the safe side. Figure 2 shows an example of such hidden ID.
Figure 2: Hidden field to hold the Flexfield reference. 
Figure 2: Hidden field to hold the Flexfield reference.

Step 3: Create a Flexfield display item

Now we want to create a non-database text item that will display the concatenated values of the segments of the accounting field. This item should use the TEXT_ITEM property class and be assigned to the appropriate canvas where you want users to see it. You then need to assign to it the dummy LOV ‘ENABLE_LIST_LAMP’ which comes from the Oracle Application template. Make sure that property ‘Validate from list’ is set to No. This ensures that the List lamp works properly for your flexfield. Figure 3 shows an example of such displayed field. We only show the most relevant item properties.
Figure 3: Displayed field to show the concatenated values of the Flexfield. 
Figure 3: Displayed field to show the concatenated values of the Flexfield.

Step 4: Create the Flexfield definition

Then there is a little bit of PL/SQL to do in order to define your flexfield. This is done with the WHEN-NEW-FORM-INSTANCE trigger. It is always a good practice to do the actual work in a procedure or package as is illustrated in listing A. There are several reasons for this but this is out of the scope of this article. Note: the code in listing A is an abbreviated version of the real code: we have left out the comments and the error handling in order to keep this article as short as possible.
Listing A: Dynamic Flexfield definition

procedure initialize is     cursor get_cao is        select  to_char(id_flex_num)        from    fnd_id_flex_structures        where   id_flex_structure_code = ‘ACCOUNTING_FLEXFIELD’                and id_flex_code = ‘GL#’;     v_cao    varchar2(1000);            begin    open get_cao;    fetch get_cao into v_cao;    if get_cao%notfound then        v_cao := ‘101’;    end if;    close get_cao;       app_standard.event(‘WHEN-NEW-FORM-INSTANCE’);    fnd_key_flex.define(      BLOCK=>’COMBO’,      FIELD=>’ACCOUNT_FLEXFIELD’,      APPL_SHORT_NAME=>’SQLGL’,      CODE=>’GL#’,      NUM=>v_cao,      ID=>’CODE_COMBINATION_ID’,      DESCRIPTION=>”,      TITLE=>’____ Your nice user friendly title here _____’,      VALIDATE=>’FULL’,      QBE_IN=>’Y’,      DERIVE_ALWAYS=>’Y’,      updateable => ”,      VRULE=>’\nSUMMARY_FLAG\nI\nAPPL=SQLGL;NAME=GL_NO_PARENT_SEGMENT_ALLOWED\nN’,      where_clause => ‘summary_flag != ”Y”’,      QUERY_SECURITY =>’Y’);  end initialize;

Step 5: Code several event triggers

Then, all that is left to do is to program a few events:
  • WHEN-VALIDATE-ITEM
  • WHEN-NEW-ITEM-INSTANCE
  • POST-QUERY
  • PRE-QUERY
  • KEY-LISTVAL
The code is shown in Listing B.
We always try to put this code in the form level triggers as it is more convenient and consistent than doing it at block or item level, especially when you have more than one foreign key flexfield in the form. Only in the case of very large and complicated forms would we do otherwise.
If you are updating someone else’s form, you may need to check that no block or item triggers are overriding your form level triggers. Check also the execution style of your form level triggers. Whether your trigger should fire in mode Before, After or Override will depend on your context.
Listing B: Event programming for the Flexfields.

WHEN-VALIDATE-ITEM    if ( :system.mode = ‘NORMAL’ ) then       fnd_flex.event( ‘WHEN-VALIDATE-ITEM’ );    end if;WHEN-NEW-ITEM-INSTANCE    app_standard.event(‘WHEN-NEW-ITEM-INSTANCE’);    fnd_flex.event(‘WHEN-NEW-ITEM-INSTANCE’ );POST-QUERY–Loads the flexfields (in our case, it populates–the concatenated field on execute query).    FND_FLEX.EVENT(‘POST-QUERY’);PRE-QUERY–If you don’t do this, whatever query criteria you may enter in— the concatenated flex field, it is not taken into account.    FND_FLEX.EVENT(‘PRE-QUERY’ );KEY-LISTVAL    APP_STANDARD.EVENT(‘KEY-LISTVAL’);    FND_FLEX.EVENT(‘KEY-LISTVAL’ );

/by
, ,

Setting Org Context in Oracle Apps 11i and R12

Oracle Apps tables ending with _ALL holds transaction data for multiple org ( Operating Units).

In 11i we had views on these tables. It requires setting context in order to fetch data from these views. This  is for security concerns as these objects holds transaction details.

The SQL command to set the ORG_ID prior to running a script is:

SQL> EXECUTE DBMS_APPLICATION_INFO.SET_CLIENT_INFO(&ORG_ID);

Enter the org_id when prompted.

If using Toad:

BEGIN
     FND_CLIENT_INFO.SET_ORG_CONTEXT (&ORG_ID);
END;   

In R12 oracle uses VPD (Virtual Private Database) to secure these transactional data .This is one of major difference in application architecture between 11i and R12.

In order to retrieve data from transactional objects, set policy context first ( as below ) –

BEGIN
     MO_GLOBAL.SET_POLICY_CONTEXT(‘MODE’ CHAR(1),ORG_ID NUMBER);
END;

    MODE – This is  either “S” – For Single Operating Unit OR “M” – For Multiple Operating Unit

    Org_ID – Operating unit (Value from column ORG_ID in all transactional objects)
                         This is mandatory for “S” mode.
Example :-

BEGIN
    MO_GLOBAL.SET_POLICY_CONTEXT(‘S’, 123);
END;

/by
, , , , , ,

How to Get IP address and host address in Oracle

This article presents a mixed bag of Oracle functionality relating to the identification of host names and IP addresses for Oracle clients and servers.

UTL_INADDR
SYS_CONTEXT
V$INSTANCE
V$SESSION

    UTL_INADDR

    The UTL_INADDR provide a means of retrieving host names and IP addresses of remote hosts from PL/SQL.
    The GET_HOST_ADDRESS function returns the IP address of the specified host name.
    SQL> SELECT UTL_INADDR.get_host_address('TEST') FROM dual;

    UTL_INADDR.GET_HOST_ADDRESS('TEST')
    --------------------------------------------------------------------------------
    192.167.1.56

    SQL>

     The IP address of the database server is returned if the specified host name is NULL or is omitted.

    SQL> SELECT UTL_INADDR.get_host_address from dual;

    GET_HOST_ADDRESS
    --------------------------------------------------------------------------------
    192.161.1.55

    SYS_CONTEXT

    The SYS_CONTEXT function is able to return the following host and IP address information for the current session:
    • TERMINAL – An operating system identifier for the current session. This is often the client machine name.
    • HOST – The host name of the client machine.
    • IP_ADDRESS – The IP address of the client machine.
    • SERVER_HOST – The host name of the server running the database instance.
    SQL> SELECT SYS_CONTEXT('USERENV','TERMINAL') FROM dual;

    SYS_CONTEXT('USERENV','TERMINAL')
    --------------------------------------------------------------------
    TEST10

    SQL> SELECT SYS_CONTEXT('USERENV','IP_ADDRESS') FROM dual;

    SYS_CONTEXT('USERENV','IP_ADDRESS')
    --------------------------------------------------------------------
    192.167.1.55

    SQL> SELECT SYS_CONTEXT('USERENV','SERVER_HOST') FROM dual;

    SYS_CONTEXT('USERENV','SERVER_HOST')
    --------------------------------------------------------------------
    Z4210gr11

    V$INSTANCE

    The HOST_NAME column of the V$INSTANCE view contains the host name of the server running the instance.
    SQL> SELECT host_name FROM v$instance;

    HOST_NAME
    ------------------------------------------------
    Z4210gR11

    V$SESSION

    The V$SESSION view contains the following host information for all database sessions:

    TERMINAL – The operating system terminal name for the client. This is often set to the client machine name.

    MACHINE – The operating system name for the client machine. This may include the domain name if present.

    The following examples show the typical output for each column.

    SQL> SELECT terminal, machine FROM v$session WHERE username = 'OEAG';

    TERMINAL                       MACHINE
    ------------------------------ ----------------------------------------------------
    TEST10                          ORACLE-BASETEST10

    /by
    , , , ,

    R12 – Meaning for Concurrent Request Status Codes and Phase Codes

    Concurrent Request Phase Codes:

    SELECT LOOKUP_CODE, MEANING
      FROM FND_LOOKUP_VALUES
     WHERE LOOKUP_TYPE = ‘CP_PHASE_CODE’ AND LANGUAGE = ‘US’
           AND ENABLED_FLAG = ‘Y’;

    LOOKUP_CODE
    MEANING
    C
    Completed
    I
    Inactive
    P
    Pending
    R
    Running

    Concurrent Request Status Codes:

    SELECT LOOKUP_CODE, MEANING
      FROM FND_LOOKUP_VALUES
     WHERE LOOKUP_TYPE = ‘CP_STATUS_CODE’ AND LANGUAGE = ‘US’
           AND ENABLED_FLAG = ‘Y’;

    LOOKUP_CODE
    MEANING
    R
    Normal
    I
    Normal
    Z
    Waiting
    D
    Cancelled
    U
    Disabled
    E
    Error
    M
    No Manager
    C
    Normal
    H
    On Hold
    W
    Paused
    B
    Resuming
    P
    Scheduled
    Q
    Standby
    S
    Suspended
    X
    Terminated
    T
    Terminating
    A
    Waiting
    G
    Warning

    Normally a concurrent request proceeds through three, possibly four, life cycle stages or phases,

    Phase Code
    Meaning with Description
    Pending
    Request is waiting to be run
    Running
    Request is running
    Completed
    Request has finished
    Inactive
    Request cannot be run

    Within each phase, a request’s condition or status may change. Below appears a listing of each phase and the various states that a concurrent request can go through.

    The status and the description of each meaning given below:

    Phase
    Status
    Description
    PENDING
    Normal
    Request is waiting for the next available manager.
    Standby
    Program to run request is incompatible with other program(s) currently running.
    Scheduled
    Request is scheduled to start at a future time or date.
    Waiting
    A child request is waiting for its Parent request to mark it ready to run. For example, a report in a report set that runs sequentially must wait for a prior report to complete.



    RUNNING
    Normal
    Request is running normally.
    Paused
    Parent request pauses for all its child requests to complete. For example, a report set pauses for all reports in the set to complete.
    Resuming
    All requests submitted by the same parent request have completed running. The Parent request is waiting to be restarted.
    Terminating
    Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.



    COMPLETED
    Normal
    Request completes normally.
    Error
    Request failed to complete successfully.
    Warning
    Request completes with warnings. For example, a report is generated successfully but fails to print.
    Cancelled
    Pending or Inactive request is cancelled, by selecting Cancel in the Status field of the Request Details zone.
    Terminated
    Running request is terminated, by selecting Terminate in the Status field of   the Request Details zone.



    INACTIVE
    Disabled
    Program to run request is not enabled. Contact your system administrator.
    On Hold
    Pending request is placed on hold, by selecting Hold in the Status field of the Request Details zone.
    No Manager
    No manager is defined to run the request. Check with your system administrator.
    /by