setting up and using debug logs in oracle applications OA Framework pages

This extract is taken from one of the metalink notes and is a really good paper on how to handle debugging issues in OAF pages in Oracle Applications.

 

Applies to:

iProcurement - Version: 11.5.9 to 12.0.5

Oracle Purchasing - Version: 12.0 to 12.0.5

Information in this document applies to any platform.

 

Purpose

This paper will help explain what to do, and what to provide, in case of an error occurs in any OA Framework page within the application.

Last Review Date

June 13, 2008

Instructions for the Reader

A Troubleshooting Guide is provided to assist in debugging a specific issue. When possible, diagnostic tools are included in the document to assist in troubleshooting.

Troubleshooting Details

INTRODUCTION

 

Since 11.5.10 release, iProcurement is 100% OA Framework technology, that’s why is very important to know how to troubleshoot issues within the pages in the application.

 

Note: 11.5.9 is 50-50, which means that 50% of the pages is OA Framework and the other 50% still uses AK Developer framework.

 

For Purchasing, in R12 release the "Buyer Work Center" was implemented, which is basically transforming the application from Oracle Forms to OA Framework technology. For instance: Orders Form.

 

DOCUMENTATION

 

Since OA Framework is an environment created by Oracle to create applications, is very important to understand the architecture, structure, language and the development plataform used. This will simplify people from the IT/support departments in the companies to handle and troubleshoot the issues.

 

Important notes that could help as reference are in Note 275880.1 -  Oracle Application Framework Release 11i Documentation Roadmap.

 

There are several notes that can help as reference for OA Framework issues:

 

Note 313195.1 - 11.5.10 Oracle iProcurement Architecture Overview - Extensibility

Note 275846.1 - Oracle Applications Framework Support Guidelines for Customers

Note 275880.1 - Oracle Applications Framework Release 11i Documentation Roadmap

Note 290614.1 - How To Get Log Files In iProcurement

Note 395445.1 - Oracle Application Framework Profile Options - Release 12

Note 395446.1 - Oracle Application Framework Troubleshooting Release 12

Note 275876.1 - Oracle Application Framework Profile Options - Release 11i

Note 283158.1 - Using OA Extensions to extend existing Apps 11i OA Framework pages (step by step)

 

SETUP

There are couple of profiles that affects the OA Framework pages, these are the following:

 

Personalize Self-Service Defn / FND_CUSTOM_OA_DEFINTION

 

 

This is intended for system administrators who wish to personalize regions at the localization, site, verticalization, org and responsibility levels. On enabling this profile option for the administrator, every OA Framework page will contain a global Personalize button. By clicking on this global button, the administrator can personalize the regions available on that page.

 

Disable Self-service Personal / FND_DISABLE_OA_CUSTOMIZATIONS

 

This is a system profile option specifically created for use by Oracle Support. You can set this profile option to "Yes" or "No" at the site or application level. If this system profile option is set to Yes, any personalizations made by the customer, regardless of the level at which the personalizations were made, will not be applied. All pages using OA Framework will now display the regions based on their original definitions.

Note: When this profile is set to "Yes", a warning message that all personalizations are disabled is displayed on every page to which a user navigates.

 

FND: Personalization Seeding Mode / FND_PERSONALIZATION_SEEDING_MODE

 

For Oracle Applications development use only. Replaces the deprecated profile, FND_CREATE_SEEDED_PERSONALIZATIONS. When this profile is set to "Yes", it sets the developerMode flag to "Yes" for any function and user personalization created in Oracle Applications development. These personalizations are then protected against update/delete at the customer's site.

 

FND: Personalization Region Link Enabled / FND_PERSONALIZATION_REGION_LINK_ENABLED

 

Enables the "Personalize Region" links on a page if the Personalize Self-Service Defn / FND_CUSTOM_OA_DEFINTION profile is set to Yes. Valid values:

 

Yes - renders the "Personalize Region" links above each region in a page. Each link takes you first to the Choose Personalization Context page, then to the Page Hierarchy Personalization page with focus on the region node from which you selected the "Personalize Region" link.The scope is always set to the region itself. You can not navigate up to the region's parent. For example, the "Personalize Region" link for /oracle/apps/abc/xyz.region1 focuses the HGrid on region1. No locator breadcrumbs are provided for you to navigate up to region1's parent.If a region on the page extends another region, the personalization context is the region being extended. For example, if /oracle/apps/abc/xyz.region1 extends /oracle/apps/abc/SharedRegionX, the scope is /oracle/apps/abc/ShareRegionX. This means the personalization occurs on ShareRegionX if you personalize region1.

No - "Personalize Region" links are not rendered. You must select the global Personalize button to personalize the page.

Minimal - renders the "Personalize Region" links, but minimizes the number of links displayed. The rule for minimization is that if a region's direct children all display a "Personalize Region" link when the profile value is set to "Yes", then the region itself will not display a link when the profile value is set to "Minimal".The scope is always set to the document where the region node resides. For example, if the region is /oracle/apps/abc/xyz.region1, the scope of the personalization context will be /oracle/apps/abc/xyz and the Page Hierarchy Personalization page will focus on region1. When the "Personalize Region" links are minimized, you can always navigate up to a region's parent, grandparent, and so on, using the locator breadcrumbs shown on the HGrid.Each link takes you to the Choose Personalization Context page, then to the Page Hierarchy Personalization page with focus on the region node from which you selected the "Personalize Region" link.If a region on the page extends another region, the personalization context still remains the current page. For example, if /oracle/apps/abc/xyz.region1 actually extends/oracle/apps/abc/SharedRegionX, the scope remains /oracle/apps/abc/xyz. This means a per-instance personalization occurs if you personalize region1. If you wish to personalize the shared region, you can navigate to the Choose Context page and select the shared region as the scope.

Note: Enabling the "Personalize Region" links allows users to also personalize regions that are dynamically added to the page from custom code in the controller (that is, regions added using createWebBean(OAPageContext pageContext, String reference, String name, boolean isMDS) in the oracle.apps.fnd.framework.webui.OAWebBeanFactory class). These dynamically-added regions always display "Personalize Region" links, even if the profile value is set to Minimal.

 

 

TROUBLESHOOTING

When an error message occurs in a OA Framework page, the application shows an error like this:

  . You have encountered an unexpected error. Please contact the System Administrator for assistance.

This error message is the generic error and do not help us too much. What we need to do is basically the following:

- Using System Administrator responsibility, set profile option at the user level: FND: Diagnostics = Yes

- Log in with user or responsibility with profile enabled.

Now, reproduce the issue. The application will display now a page with a link in order to click like the following:

 

 

 

After you click on the link the application will display the full java stack error message, this is more meanfull for us and will help us to diagnose your issue.

The following screen shot will help you to understand what the stack error shows:

 

 

 

 

Some times the java stack error is not enough, that’s why we need to enable the debug or also the database trace functionality.

 

 

 

LOGGING

There are several ways to enable the logging when an OA Framework issue occurs:

 

A. Enable the logging in the page, to do this please execute the following:

 

Using System Administrator responsibility, set profile option at the user level: FND: Diagnostics = Yes

Log in with user or responsibility with profile enabled.

Once you enabled FND: Diagnostics to Yes, a link Diagnostics will show up at the top of the page.

Click Diagnostics link.

Use option Show Log on Screen.

Click Go.

Choose Log Level Statement (1).

Enter in Module %

Click Go to see the log (debug.log). You will see that all the pages will include the log in the bottom of the page, something like this:

 

 

 

 

B. Enable the logging and storage those from the table, to do this please execute the Following:

 

Using System Administrator responsibility, set profile options at the user level:

 

 

FND: Debug Log Enabled : Yes

FND: Debug Log Filename : leave it as null

FND: Debug Log Level : Statement

FND: Debug Log Module : %

Truncate table APPLSYS.FND_LOG_MESSAGES

Bounce iAS/Apache

Reproduce the issue

Get the table dump of fnd_log_messages in spreadsheet format.

C. Enable the logging and storage those into a file, to do this please execute the following:

 

Using System Administrator responsibility, set profile options at the user level:

 

 

FND: Log Enabled = Yes

FND: Log Level = Statement

FND: Log Filename = /usr/tmp/debug.log (or any other directory with write privileges)

FND: Log Module = %

Reproduce the issue in the application. Go to /usr/tmp/ and get the debug.log

Any of these three methods are accepted by support and development.

 

 

 

TRACING

When we talk about tracing, we are talking to collect the access executed by the application to the database in terms of queries or DML scripts, like UPDATE, INSERT or DELETE. To enable the trace, there are also several methods:

 

A. Traditional trace enabled within the application:

 

Set profile FND: Diagnostics to Yes at user level.

Login as the above user

Click on Diagnostics icon.

Select 'Set Trace Level' and click Go

Select the desired trace level with binds and waits and click Save

Perform the activity to reproduce the issue

Click Diagnostics icon again

Select 'Set Trace Level' and click Go

Take note of the all trace file numbers on the left side of the page

 

 

 

 

Disable trace

Find the trace files with contains the trace numbers under the UDUMP directory on the database server

Execute tkprof in the trace files

Upload raw and tkprof files

B. There is another old method that is still valid for 11i or R12.

 

 

Log onto core Applications with the Application Developer responsibility.

Navigate to the Profile menu.

Query up the profile name "FND_INIT_SQL"

In the bottom block of the form, make sure that ALL checkboxes are checked.

Typically, you will have to enable the checkboxes under "User Access" to make it "Visible" and "Updatable".

Save changes and exit this form.

Switch responsibility to System Administrator.

Navigate: Profile > System.

On the "Find System Profile Values" form, make sure the checkboxes for "User" and "Profiles with no Values" are checked.

Beside the "User" checkbox use the LOV to select the user who's activity you need to trace.

In the "Profile" field, type: %Initialization% Then click the "Find" button.

In the "System Profile Values" form, enter the following in the field for the profile "Initialization SQL Statement - Custom" for the specific User:

begin FND_CTL.FND_SESS_CTL('','', '', 'TRUE','','ALTER SESSION SET

TRACEFILE_IDENTIFIER='||''''||'USERXXX' ||''''||' EVENTS ='||''''||' 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12 '||''''); end;

NOTE: It is all single quotes. if there is a typo, the user will not be able to login. Optional to replace USERXXX by the user executing trace.

Save changes and exit the form.

Find where the trace files are created in the instance. Check table v$parameter for user_dump_dest directory location. Delete all trace files from this directory.

Log onto applications as the user for whom turned on tracing,and promptly recreate the problem, then log off.

Execute tkprof in the trace files. Upload the tkprofed trace file(s) created.

Make sure to clear the "Initialization SQL Statement - Custom" profile option for the user who's activity you traced.

Both tracing methods are valid and accepted by support and development teams.

 

 

 

DATA AND QUERIES

The queries related to OA Framework are tied to the JDR tables. These tables storage the data of the pages created using OA Framework technology. The tables are:

 

 

 

JDR_Components

JDR_Attributes

JDR_Attributes_Trans

JDR_Paths

 

Explain each of these tables will take time and involve OA Framework knowledge, so I will just name them.

 

Developer has created a PL/SQL package to make the things easier in order to get information about OA Framework pages. This package was called: jdr_utils. I will provide some examples of the procedures documented in this package.

 

1. execute jdr_utils.listdocuments ('/oracle/apps/fnd/wf/worklist/webui', TRUE);

 

Printing documents for /oracle/apps/fnd/wf/worklist/webui recursively

 

/oracle/apps/fnd/wf/worklist/webui/AdvancWorklistPG

/oracle/apps/fnd/wf/worklist/webui/AdvancWorklistRG

/oracle/apps/fnd/wf/worklist/webui/BasicWorklistPG

/oracle/apps/fnd/wf/worklist/webui/MoreInfoPG

/oracle/apps/fnd/wf/worklist/webui/MoreInfoAnswerRG

/oracle/apps/fnd/wf/worklist/webui/NotifDetailsRG

/oracle/apps/fnd/wf/worklist/webui/NotifReassCtrlRG

/oracle/apps/fnd/wf/worklist/webui/NotifReassignPG

/oracle/apps/fnd/wf/worklist/webui/FullWorklistPG

 

2. execute jdr_utils.printdocument ('/oracle/apps/fnd/wf/worklist/webui/FullWorklistPG');

 

Print the content of the document.

 

<?xml version='1.0' encoding='UTF-8'?>

<page xmlns=http://xmlns.oracle.com/jrad xmlns:ui="http://xmlns.oracle.com/uix/ui" xmlns:oa="http://xmlns.oracle.com/oa"

xmlns:user="http://xmlns.oracle.com/jrad/user"

file-version="$Header: FullWorklistPG.xml 115.13 2003/04/03 20:12:40 vbhatia

noship $" version="9.0.3.6.3_419" xml:lang="en-US">

<content>

<oa:pageLayout id="WFNTFWLFULLPAGE" akRegionCode="WFNTFWLFULLPAGE"

regionName="Worklist"

amDefName="oracle.apps.fnd.wf.worklist.internal.server.FullWorklistAM"

helpTargetAppShortName="fnd" helpTarget="FND_WFNTFWLFULLPAGE">

 

 

 3. execute jdr_utils.listcustomizations ('/oracle/apps/fnd/wf/worklist/webui');

 

List all the customizations created for that particular route.

 

/oracle/apps/fnd/customizations/user/0/wf/worklist/webui

/oracle/apps/fnd/customizations/localization/IN/wf/worklist/webui

/oracle/apps/fnd/customizations/responsibility/22918/wf/worklist/webui

/oracle/apps/fnd/customizations/user/2662/wf/worklist/webui

/oracle/apps/fnd/customizations

/Site/0/wf/worklist/webui

/oracle/apps/fnd/customizations/site/0/wf/worklist/webui

/oracle/apps/fnd/customizations/user/seededdeveloper/wf/worklist/webui

/oracle/apps/fnd/customizations/user/2257/wf/worklist/webui

/oracle/apps/fnd/customizations/user/2663/wf/worklist/webui

/oracle/apps/fnd/customizations/responsibility/21616/wf/worklist/webui

 

4. execute jdr_utils.listlanguages ('/oracle/apps/fnd/wf/worklist/webui/FullWorklistPG');

Printing languages for document

/oracle/apps/fnd/wf/worklist/webui/FullWorklistPG

ar-AE

ko-KR

 

5. execute jdr_utils.deletedocument ('/oracle/apps/fnd/customizations/user/2662/wf/worklist/webui/FullWorklistPG');

Deletes from the JDR repository the document….be carefull. You can use this method to delete customizations.

execute jdr_utils.printdocument ('/oracle/apps/fnd/customizations/user/2662/wf/worklist/webui/FullWorklistPG');

Error: Could not find document

/oracle/apps/fnd/customizations/user/2662/wf/worklist/webui/FullWorklistPG

 

 

 

CODE FILES

Every page within the application has OA Framework page associated, in order to know what is the name and how to find the version you need to do the following:

 

 

 

As System Administrator, change profile FND: Diagnostics at USER level to Yes.

New Links “Diagnostics” in the top-right side and “About this page” in the botton-left will be displayed when you login again.

After login, go to the iProcurement page that you want to check the page and the file version.

For Instance, we will find out the page name and version for the iProcurement HomePage.

 

 

 

Scroll to the very botton and click on the “About this Page” link

 

 

 

In the new page displayed, the document ID and the version

 

 

 

In this case the information found is:

 

Document ID: /oracle/apps/icx/icatalog/shopping/webui/ShoppingHomePG

File name: ShoppingHomePG.xml

File version: 115.36

 

All the xxxxxxPG pages has .xml extension. The file version can also find it in the middle tier apache. Do the following:

 

- Navigate to $ICX_TOP/mds/icatalog/shopping/webui/

- Run the following unix command:

 $ adident Header: ShoppingHomePG.xml

 

 

 

Things to have in mind:

 

1. The PG files are under $ICX_TOP which is tha Home directory for ICX – Self Service Web application (iProcurement)

2. mds is a sub-directory within the application home directory, this path is used to storage all the files that will reside in the server. Mds means Meta-Data Source.

 

For the controller classes (CO) the files are located under $JAVA_TOP/oracle/apps/icx and then theFor instance: oracle.apps.icx.icatalog.shopping.webui.ShoppingHomeCO.class

 

- Navigate to $JAVA_TOP/oracle/apps/icx/icatalog/shopping/webui/

- Run the following unix command:

$ adident Header: ShoppingHomeCO.class

 

PATCHES

 OA Framework technology goes hand by hand with the ATG releases. The latest patches are:

 

 

ATG Release Explanatory Note Patch

ATG_PF.RUP1 Note 296154.1 Patch 4017300

ATG_PF.RUP2 Note 312572.1 Patch 4125550

ATG_PF.RUP3 Note 337274.1  Patch 4334965

ATG_PF.RUP4  Note 365228.1  Patch 4676589 

ATG_PF.RUP5

 Note 375682.1 Patch 5473858 

ATG_PF.RUP6 Note 405970.1 Patch 5903765

12.0.1 Note 417917.1 Patch 5082400

12.0.2 Note 430114.1 Patch 6014659 

12.0.3 Note 445192.1 Patch 6272715

12.0.4 Note 465776.1 Patch 6510214

 

 

Now, when an error occurs within the application an involves a patch release, the patch is released for that specific issue.

 

 

References

Note 290614.1 - How To Get Log Files In iProcurement

Business Event example (BES)

BES Event Example Subscribing

Oracle Applications iSetup Simplified Instructions Guide

R12 iSetup Instruction Guide

Oracle Absence Management

Oracle Absence Management

How to set the payment file out directory and payment file suffix for EFT payments in Payments Module

How to to specify the output location and the payment file suffix for EFT bank files. This is a fairly simple and common requirement for EFT bank files generated out of iPayments module in E-Business.

 

The following is the navigation path to set the same:

 

Oracle Payments Setup -> Payment Process Profiles -> Payment File Information section

 

The fields Outbound Payment File Prefix, Outbound Payment File Extension and Outbound Payment File Directory allow you to configure through setup

 

RE: How to use CUSTOM.PLL to change list of values in an Oracle applications form

The client had a requirement to add a filter to the memo line LOV on the AR Transactions form

We tried to achieve this using Forms Personalization, which is the best way to go about implementing this requirement.

However, there is a restriction in Forms Personalization which does not let you create a record group with a SQL query more than 4000 characters in length.

Therefore, we went for the next best option – CUSTOM.PLL

To the uninitiated, CUSTOM.PLL is a library which allows you to introduce extensions to Oracle Applications Forms behaviour.

The steps to implement the requirement are given below:

·         Create a new library procedure called XXARXTWMAI.pll with one procedure “Custom_memo_line_lov”. The code is shown below, it essentially will create a new record group with the new query, then find the LOV and attach the new record group to it. It will also try and populate the record group with records.

PROCEDURE custom_memo_line_lov IS

 

rg_id RECORDGROUP;

rg_name VARCHAR2(100) := 'XXAR_TLIN_MEMO_LINE';

v_sql_string VARCHAR2(32000);

errcode NUMBER;

l_error_message VARCHAR2(200);

the_rowcount NUMBER;

v_lov      lov;

 

BEGIN

               

 

                /**

            The SQL String is taken from standard AR_TLIN_MEMO_LINE record group

                    and we have just added the line

                    --AND aml.attribute1 = :tgw_header.ctt_type_name--

                    to filter and show only memo lines related to the transaction type selected

                **/

 

 

  v_sql_string := 'SELECT   aml.memo_line_id, aml.description "aml_description", aml.NAME,

         al.meaning, aml.line_type,

         DECODE (aml.line_type, ''TAX'', 0, ''FREIGHT'', 0, '''') quantity,

         DECODE

               (:parameter.art_class,

                ''CM'', :tlin_lines.uom_code,

                DECODE (aml.line_type,

                        ''TAX'', '''',

                        ''FREIGHT'', '''',

                        ''CHARGES'', '''',

                        DECODE (SIGN (  TRUNC (SYSDATE)

                                      - NVL (TRUNC (uom.disable_date),

                                             TRUNC (SYSDATE)

                                            )

                                     ),

                                -1, NVL (uom.uom_code, :tlin_lines.uom_code),

                                0, NVL (uom.uom_code, :tlin_lines.uom_code),

                                1, :tlin_lines.uom_code

                               )

                       )

               ) uom_code,

         DECODE

            (:parameter.art_class,

             ''CM'', :tlin_lines.uom_unit_of_measure_name,

             DECODE (aml.line_type,

                     ''TAX'', '''',

                     ''FREIGHT'', '''',

                     ''CHARGES'', '''',

                     DECODE (SIGN (  TRUNC (SYSDATE)

                                   - NVL (TRUNC (uom.disable_date),

                                          TRUNC (SYSDATE)

                                         )

                                  ),

                             -1, NVL (uom.unit_of_measure,

                                      :tlin_lines.uom_unit_of_measure_name

                                     ),

                             0, NVL (uom.unit_of_measure,

                                     :tlin_lines.uom_unit_of_measure_name

                                    ),

                             1, :tlin_lines.uom_unit_of_measure_name

                            )

                    )

            ) unit_of_measure,

         DECODE (aml.line_type,

                 ''TAX'', DECODE (:parameter.art_class, ''CM'', '''', 0),

                 ''FREIGHT'', DECODE (:parameter.art_class, ''CM'', '''', 0),

                 aml.unit_std_price / NVL (:parameter.art_exchange_rate, 1)

                ) unit_std_price,

                       /* unit selling price (same as unit standard price): */

         DECODE (aml.line_type,

                 ''TAX'', 0,

                 ''FREIGHT'', 0,

                 aml.unit_std_price / NVL (:parameter.art_exchange_rate, 1)

                ) unit_selling_price,

         DECODE (aml.line_type, ''TAX'', 0, ''FREIGHT'', 0, '''') extended_amount,

         DECODE

            (:parameter.art_class,

             ''CM'', :tlin_lines.accounting_rule_id,

             DECODE (:tlin_lines.rr_accounting_rule_name,

                     NULL, DECODE (:parameter.art_invoicing_rule_id,

                                   NULL, :tlin_lines.accounting_rule_id,

                                   DECODE (rr.status,

                                           ''A'', rr.rule_id,

                                           :tlin_lines.accounting_rule_id

                                          )

                                  ),

                     :tlin_lines.accounting_rule_id

                    )

            ) accounting_rule_id,

         DECODE

            (:parameter.art_class,

             ''CM'', :tlin_lines.rr_accounting_rule_name,

             DECODE (:tlin_lines.rr_accounting_rule_name,

                     NULL, DECODE (:parameter.art_invoicing_rule_id,

                                   NULL, :tlin_lines.rr_accounting_rule_name,

                                   DECODE (rr.status,

                                           ''A'', rr.NAME,

                                           :tlin_lines.rr_accounting_rule_name

                                          )

                                  ),

                     :tlin_lines.rr_accounting_rule_name

                    )

            ) accounting_rule_name,

         DECODE

            (:parameter.art_class,

             ''CM'', :tlin_lines.accounting_rule_duration,

             DECODE (:tlin_lines.rr_accounting_rule_name,

                     NULL, DECODE (:parameter.art_invoicing_rule_id,

                                   NULL, :tlin_lines.accounting_rule_duration,DECODE

                                         (rr.status,

                                          ''A'', DECODE (rr.TYPE,

                                                       ''ACC_DUR'', 1,

                                                       ''A'', rr.occurrences

                                                      ),

                                          :tlin_lines.accounting_rule_duration

                                         )

                                  ),

                     :tlin_lines.accounting_rule_duration

                    )

            ) accounting_rule_duration,

         DECODE

              (:parameter.art_class,

               ''CM'', :tlin_lines.rr_frequency,

               DECODE (:tlin_lines.rr_accounting_rule_name,

                       NULL, DECODE (:parameter.art_invoicing_rule_id,

                                     NULL, :tlin_lines.rr_frequency,

                                     DECODE (rr.status,

                                             ''A'', rr.frequency,

                                             :tlin_lines.rr_frequency

                                            )

                                    ),

                       :tlin_lines.rr_frequency

                      )

              ) rr_frequency,

         DECODE

            (:parameter.art_class,

             ''CM'', :tlin_lines.rr_accounting_rule_type,

             DECODE (:tlin_lines.rr_accounting_rule_name,

                     NULL, DECODE (:parameter.art_invoicing_rule_id,

                                   NULL, :tlin_lines.rr_accounting_rule_type,

                                   DECODE (rr.status,

                                           ''A'', rr.TYPE,

                                           :tlin_lines.rr_accounting_rule_type

                                          )

                                  ),

                     :tlin_lines.rr_accounting_rule_type

                    )

            ) accounting_rule_type,

         DECODE

            (:parameter.art_class,

             ''CM'', TO_DATE (:tlin_lines.rule_start_date, ''DD-MON-RRRR''),

             DECODE

                (:tlin_lines.rr_accounting_rule_name,

                 NULL, DECODE

                    (:parameter.art_invoicing_rule_id,

                     NULL, TO_DATE (:tlin_lines.rule_start_date,

                                    ''DD-MON-RRRR''),

                     DECODE (rr.status,

                             ''A'', DECODE

                                     (rr.frequency,

                                      ''SPECIFIC'', MIN (rs.rule_date),

                                      DECODE

                                            (:parameter.art_invoicing_rule_id,

                                             -2, TO_DATE

                                                     (:parameter.art_trx_date,

                                                      ''DD-MON-RRRR''

                                                     ),

                                             -3, SYSDATE

                                            )

                                     ),

                             TO_DATE (:tlin_lines.rule_start_date,

                                      ''DD-MON-RRRR''

                                     )

                            )

                    ),

                 TO_DATE (:tlin_lines.rule_start_date, ''DD-MON-RRRR'')

                )

            ) rule_start_date

    FROM ar_memo_lines aml,

         ar_lookups al,

         mtl_units_of_measure uom,

         ra_rules rr,

         ra_rule_schedules rs

   WHERE al.lookup_type = ''STD_LINE_TYPE''

     AND al.lookup_code = aml.line_type

     AND aml.uom_code = uom.uom_code(+)

     AND aml.accounting_rule_id = rr.rule_id(+)

     AND rr.rule_id = rs.rule_id(+)

     AND :tlin_lines.inventory_item_id IS NULL

     AND aml.line_type IN

            (''CHARGES'', ''FREIGHT'', ''LINE'', ''TAX'') /* freight is only allowed if no header freight was entered and if allow freight = yes */

     AND (   aml.line_type != ''FREIGHT''          OR (    aml.line_type = ''FREIGHT''

              AND :parameter.art_allow_frt_flag = ''Y''

              AND NOT EXISTS (

                     SELECT ''header freight exists''

                       FROM ra_customer_trx_lines frt

                      WHERE customer_trx_id = :parameter.art_customer_trx_id

                        AND line_type = ''FREIGHT''

                        AND link_to_cust_trx_line_id IS NULL)

             )

         ) /* may pick charges line for dms and cms only */

     AND (   aml.line_type != ''CHARGES''

          OR (    aml.line_type = ''CHARGES''

              AND :parameter.art_class IN (''DM'', ''CM'')

             )

         ) /* if profile ar_allow_manual_tax_lines is no do not show */ /* tax memo lines */

     AND (   aml.line_type != ''TAX''

          OR (    aml.line_type = ''TAX''

              AND :ar_world.ar_allow_manual_tax_lines = ''Y''

             )

         ) /* if update, show only memo lines of the current type */

     AND aml.line_type =

            DECODE

               (:tlin_lines.customer_trx_line_id,

                '''', aml.line_type,

                NVL (:tlin_lines.ml_memo_line_type, ''LINE'')

               ) /* if chargeback, you can choose only line_type line */

     AND aml.line_type =

                    DECODE (:parameter.art_class,

                            ''CB'', ''LINE'',

                            aml.line_type

                           )

     AND :parameter.art_trx_date BETWEEN NVL (TRUNC (aml.start_date),

                                              :parameter.art_trx_date

                                             )

                                     AND NVL (TRUNC (aml.end_date),

                                              :parameter.art_trx_date

                                             )                                

     AND aml.attribute1 = :tgw_header.ctt_type_name

GROUP BY aml.memo_line_id,

         al.meaning,

         aml.NAME,

         aml.description,

         aml.line_type,

         uom.disable_date,

         uom.uom_code,

         uom.unit_of_measure,

         aml.unit_std_price,

         rr.rule_id,

         rr.status,

         rr.NAME,

         rr.TYPE,

         rr.occurrences,

         rr.frequency

ORDER BY al.meaning, aml.NAME';

 

 

  /** Make sure group doesn't already exist */

  rg_id := Find_Group( rg_name );

   

  /* ** If it does not exist, create it */

  IF Id_Null(rg_id) THEN

 

                rg_id := Create_Group_From_Query

                ( rg_name,

                v_sql_string

                );

               

  END IF;

 

 

  /* ** Populate the record group */

  errcode := Populate_Group( rg_id );

--  the_rowcount := Get_Group_Row_Count( rg_id );

 

  /* **Change the LOV record group */

  v_lov    := find_lov('AR_TLIN_MEMO_LINE');

 

  IF get_lov_property(v_lov,group_name) = 'AR_TLIN_MEMO_LINE'

  THEN

     set_lov_property(v_lov,group_name,rg_name);

  END IF;

 

END;

·         Extract the CUSTOM.pll from the $AU_TOP/resource folder on the application/web tier

·         Attach the library XXARXTWMAI.pll with portable option (don’t include directory path)

·         Write code in the “event” procedure to call the custom procedure:

On WHEN-NEW_ITEM_INSTANCE event for the ARXTWMAI form, TLIN_LINES block and DESCRIPTION item, call the procedure custom_memo_line_lov;

 

To compile the custom.pll, use the command as below

frmcmp Module=CUSTOM.pll  Userid=$apps_user/$apps_pwd Module_Type=LIBRARY  Logon=YES        Output_File=CUSTOM.plx Batch=YES Compile_All=SPECIAL

 

Also remember that for custom.pll changes to take effect, it is advisable to logout and login to the applications.