R12 Banks, Branches and Bank Accounts

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 senstive)

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 Note 737311.1: “How to Configure the Account Analysis Report in Release 12 for Large Reports”.

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.

OA Framework Pages: ICX_STANDARD_GLOBAL_MENU

The links located at the top-righthand corner of all OA Framework pages (“Home”, “Logout”, “Preferences”, etc.) belong to the standard Self Service Global Menu (ICX_STANDARD_GLOBAL_MENU). The links are defined as functions in this menu, and are outside of the Personalization framework.

The setup can be examined as follows…

Resp: Application Developer
Nav: Application > Menu

…and query back the ICX_STANDARD_GLOBAL_MENU entry.

The menus assigned to “Self-Service” responsibilities all have the  ICX_STANDARD_GLOBAL_MENU added as a sub-menu.

For example, see the XDO_TEMPLATE_MANAGER menu, assigned to the XML Publisher Administrator responsibility:

R12 Payables Open Interface Import profile option

Because of the extra layer (“Lines”) in the R12 AP invoices data model, the Payables Open Interface (POI) will create invoice line level DFF values by default.

In R11, the invoice distribution-level DFF was populated by the POI.

To create invoice distribution level DFF values in R12, the following profile option should be set to “Yes”:

AP: Copy Invoice Lines Flex field to Distributions during Import

 

 

Deriving Oracle GL Account Code Combination ID’s (CCID’s) in PL/SQL

Given a list of Oracle GL account code segment values…

E.g. “11030-001-0000-00000000-INF1018-000000”

…it is better to use Oracle’s API’s to derive the corresponding code_combination_id (ccid). Don’t just search in the gl_code_combinations table – using the following API’s will allow Oracle GL’s cross-validation rules to assess the validity of a given account code combination, and, if GL is configured to allow dynamic insertion of new account codes (see below), a non-existant code combination will be created automatically in gl_code_combinations:

Resp: General Ledger Super User
Nav: Setup > Financials > Flexfields > Key > Segments > Allow Dynamic Inserts

Note: if you are testing from a client PC (using a developer tool such as Toad or SQL*Developer), ensure that the PC’s nls_language is set:. For example:

ALTER SESSION SET nls_language='American'

If the language is not set, the following kind of error occurs:

Error: Value 11030 for the flexfield segment Nominal does not exist in the value set C4GL_NOMINAL.

…even though the segment value exists in Oracle GL.

There are two public API’s available:

1. PROCEDURE: fnd_flex_ext.get_combination_id

DECLARE
  v_ccid    NUMBER;
  v_return  BOOLEAN;
  v_err     VARCHAR2(2000);
  segments  fnd_flex_ext.segmentarray;
  
BEGIN
  segments(1) := '11030';
  segments(2) := '001';
  segments(3) := '0000';
  segments(4) := '00000000';
  segments(5) := 'INF1018';
  segments(6) := '000000';
  
  v_return := fnd_flex_ext.get_combination_id
              (application_short_name => 'SQLGL'
              ,key_flex_code          => 'GL#'
              ,structure_number       => 50143
              ,validation_date        => SYSDATE
              ,n_segments             => 6
              ,segments               => segments
              ,combination_id         => v_ccid
              --,data_set               => -1
              );
              
  IF (v_return) THEN
    dbms_output.put_line('CCID is '||v_ccid); 
  ELSE
    v_err := fnd_flex_ext.get_message;
    dbms_output.put_line('Error: '||v_err);
  END IF; 
                                                   
END;

Input parameters: the “structure_number” is the chart-of-accounts ID, and the segments of the account code combination are passed in an array variable (fnd_flex_ext.segmentarray).

Output: the “combination_id” OUT parameter contains the ccid, if it can be derived. The fnd_flex_ext.get_message function can be used to display an error message, if an error occurs. For example:

Error: Value INF1018xx is longer than its maximum length of 7 characters.

2. FUNCTION: fnd_flex_ext.get_ccid

A shorthand version of the above procedure, which takes a concatenated list of segment values as an input string:

DECLARE
  v_ccid   NUMBER;
  v_err    VARCHAR2(2000);
BEGIN
  v_ccid := fnd_flex_ext.get_ccid
            (application_short_name => 'SQLGL'
            ,key_flex_code          => 'GL#'
            ,structure_number       => 50143
            ,validation_date        => TO_CHAR(SYSDATE,'DD-MON-YYYY')
            ,concatenated_segments  =>'11030-001-0000-00000000-INF1018-000000'
            );
            
  dbms_output.put_line('CCID is '||v_ccid);

  v_err := fnd_flex_ext.get_message;

  IF (v_err IS NOT NULL) THEN
    dbms_output.put_line('Error is '||v_err);
  END IF;
  
END;

The validation_date is actually passed as a VARCHAR2, so it is imperitive to use one of two possible date formats:

‘DD-MON-YYYY’, or
‘YYYY/MM/DD HH24:MI:SS’

Using Oracle Applications Messages

1. Create a message in Oracle Apps

Resp: Application Developer
Nav: Application > Messages

(For example XXAP999_SUPPLIER_ERR, containing the token &VENDOR_NUM)

2. For a token-substituted message use the following PL/SQL to do the substitution:

BEGIN
  fnd_message.set_name('XXAP', 'XXAP999_SUPPLIER_ERR');
  fnd_message.set_token('VENDOR_NUM', '12345678');
END;

Then use fnd_message.get to retrieve the message string. E.g.

SELECT fnd_message.get
FROM   dual

For non-substituted messages, the set_name and set_token commands are not required. Just use:

SELECT fnd_message.get_string('XXAP', 'XXAP999_SUPPLIER_ERR')
FROM   dual

3. To print to standard concurrent program output or log files do the following:
Set a text string variable, for example

v_msg := fnd_message.get

Then print it to the log or output files:

fnd_file.put_line(fnd_file.log, 'Error message is: ' || v_msg);
fnd_file.put_line(fnd_file.output, 'Error message is: ' || v_msg);

BI Publish an Output File to the File System

Use a bursting control file attached to the BI Publisher Data Definition.

For example:
<?xml version="1.0" encoding="UTF-8" ?> 
<!--                                                                                           -->
<!-- ######################################################################################### -->
<!-- Application : XX General Ledger                                                           -->
<!-- Module      : C4GL075                                                                     -->
<!-- Author      : David Taylor                                                                -->
<!-- Date        : 03-FEB-2011                                                                 -->
<!-- Description : XML Publisher Bursting Control File to publish a HYPERION data extract, in  -->
<!--               MS Excel format, to the directory /erp/FTP/HYPERION/out                     -->
<!--                                                                                           -->
<!-- Modification History                                                                      -->
<!-- ====================                                                                      -->
<!-- Date	  Name	            Ver  Comments                                              -->
<!-- ===========  ================  ===  ===================================================== -->
<!-- 03-FEB-2011  David Taylor      1.0  Created.                                              -->
<!--                                                                                           -->
<!-- ######################################################################################### -->
<!--                                                                                           -->
<xapi:requestset xmlns:xapi="http://xmlns.oracle.com/oxp/xapi">
  <xapi:request select="/XXGL075/FILESET">
    <xapi:delivery>
      <xapi:filesystem id="file1" output="/erp/FTP/HYPERION/out/HSP_TEXT_CELL_VALUE.xls" />
    </xapi:delivery>
    <xapi:document output-type="excel" delivery="file1" >
      <xapi:template type="xsl-fo" location="xdo://XXGL.C4GL075.en.00" /> 
    </xapi:document>  
    <xapi:delivery>
      <xapi:filesystem id="file2" output="/erp/FTP/HYPERION/out/archive/HSP_TEXT_CELL_VALUE.xls.${REQUEST_ID}" />
    </xapi:delivery>
    <xapi:document output-type="excel" delivery="file2" >
      <xapi:template type="xsl-fo" location="xdo://XXGL.XXGL075.en.00" /> 
    </xapi:document>
  </xapi:request>
</xapi:requestset>