Customizing LCT File: Concurrent Program download LCT File(afcpprog.lct)

Usually we do not require ALL the Concurrent Programs for a Custom schema to be downloaded to an LCT File unless it’s an initial CUSTOM Object migration (or) Data reconciliation (or) Mass update of some fields.

The FNDLOAD Command SYNTAX to download the Program is as below
FNDLOAD apps/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXXYY_TEST_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXXYY" CONCURRENT_PROGRAM_NAME="XXXYY_TEST_PRG"


This would download a specific concurrent program and does the job, and I believe an human error can cause a misery to the case if any of the parameters say for ex:CONCURRENT_PROGRAM_NAME is misspell or an underscore missing. Let’s assume the same command as below replacing CONCURRENT_PROGRAM_NAME with CONCURRENT_PROGRAM_NAME1. The FNDLOAD command ignores the second parameter and fetches the complete Application level Programs to an LDT File.

FNDLOAD apps/<pwd> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXXYY_TEST_CP.ldt PROGRAM APPLICATION_SHORT_NAME="XXXYY" CONCURRENT_PROGRAM_NAME="XXXYY_TEST_PRG"

Sometimes this discrepancy can be identified/caught by opening the file, File size, doing tests in the test environment and these can still be missed at a crucial time causing haywire

And also Suppose there are only 2-5 Concurrent Programs then those 2-5 Concurrent Programs will be downloaded instead of a particular program causing you to believe the size of the file.

To avoid this, the customized the LCT File to download the Program only if CONCURTRENT_PROGRAM_NAME parameter is provided. This goes to the underlying Logic in the LCT File where it considers NULL Application names and NULL Concurrent Program names. Deleting the respective Clauses in the SQL Statements should do the trick for you.

CUSTOM LCT File Upload

Print This Post

FND-CP-ESP: Child: exec:: No such file or directory

There could be 2 reasons this error could arise,

1. No Proper Symbolic link created or
    To resolve this create a Symbolic Link as below, without the file extension

    ln –s $FND_TOP/bin/fndcpesr $XXXX_TOP/bin/XXXXXXX

    XXXX – Custom TOP Name
    XXXXXXX – Executable name of the Program

2. The PROG File that’s created might have accidentally converted to DOS due to File Copy Mode.
 
    To resolve this, Convert the File to UNIX fileformat as below,
    dos2unix <FileName>.txt

Print This Post

SQL Tuning Advisor

The SQL Tuning Advisor analyses high-volume SQL statements and offers tuning recommendations. The SQL Tuning Advisor provides advice in the form of precise SQL actions for tuning the SQL statements along with their expected performance benefits.

Open a New SQL Worksheet and prepare the SQL for which you want to run the SQL Tuning Advisor for and Press Ctrl + F12 or the icon

The output would be as below with the recommendations,

Statistics: In this analysis, objects with stale or missing statistics are identified and appropriate recommendations are made to remedy the problem.

SQL Profile: In this section,  SQL Tuning advisor recommends to improve the execution plan by the generation of a SQL Profile, which is the optimized plan to be used for that SQL

Indexes: This recommends whether the SQL statement might benefit from an index. If necessary, new indexes that can significantly enhance query performances are identified and recommended.

Restructure SQL:  In this analysis, relevant suggestions are made to restructure the selected SQL statements for improved performance.

In order to run the SQL Tuning advisor, user needs to have ‘ADVISOR’ and ADMINISTER SQL TUNING SET privilege.


Print This Post

Identify Oracle Database Locks

The below SQL gives the list of locks on the Database Objects and the corresponding Terminal

select nvl(S.USERNAME,'Internal') username,
       nvl(S.TERMINAL,'None') terminal,
       L.SID||','||S.SERIAL# Kill,
       U1.NAME||'.'||substr(T1.NAME,1,20) tab,
       decode(L.LMODE,1,'No Lock',
                      2,'Row Share',
                      3,'Row Exclusive',
                      4,'Share',
                      5,'Share Row Exclusive',
                      6,'Exclusive',null) lmode,
       decode(L.REQUEST,1,'No Lock',
                       2,'Row Share',
                       3,'Row Exclusive',
                       4,'Share',
                       5,'Share Row Exclusive',
                       6,'Exclusive',null) request
 from V$LOCK L,
      V$SESSION S,
      SYS.USER$ U1,
      SYS.OBJ$ T1
where L.SID = S.SID
  and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
  and U1.USER# = T1.OWNER#
  and S.TYPE != 'BACKGROUND'
  and S.sid in(select blocking_session from v$session where blocking_session is not null)
order by 1,2,5


Print This Post

Enabling Oracle Apps 11i/R12 Regional EMEA APAC LATIN-AMERICAN Localizations

When you implement Oracle E-Business suite for different Regional Rollouts, you would want to adhere and implement Country Specific Statutory requirements.

Today, in this topic I would like to show how could we enable Regional Country Specific Localizations

Firstly, we check whether any of the Following Localizations are applied:

1. JA Asia/Pacific Localizations      
2. JE European Localizations         
3. JG Regional Localizations         
4. JL Latin-American Localizations

SELECT application_id, patch_level, product_version, decode(upper(status), 'N', 'Not Installed', 'I','Installed', 'L', 'Custom') "Status"
FROM fnd_product_installations fpi
WHERE application_id in (7000,  7002, 7003, 7004);

Application Id   Patch level  Product Version  Status
---------------      ------------ ---------------- ----------
7000             R12.JA.B.3   12.0.0           Not Installed
7002             R12.JE.B.3   12.0.0           Not Installed
7003             R12.JG.B.3   12.0.0           Not Installed
7004             R12.JL.B.3   12.0.0           Not Installed

If and when you want to use those country Specific Localization Functionality, you need to Licence the

Products, which is just registering of those products as below,

System Administartor -> Oracle Applications manager -> Licence Manager -> Site Map -> Country Specific Functionalities -> Selected Country specific functionality

Asia/Pacific countries for JA
European countries for JE - As an example here France is selected
Latin-American countries for JL

Click on Next






Click on Submit




And Finally the Specific Localizations should come as installed

SELECT application_id, patch_level, product_version, decode(upper(status), 'N', 'Not Installed', 'I','Installed', 'L', 'Custom') "Status"
FROM fnd_product_installations fpi
WHERE application_id in (7000,  7002, 7003, 7004);

Application Id   Patch level  Product Version  Status
---------------  ------------ ---------------- ----------
7000             R12.JA.B.3   12.0.0           Not Installed
7002             R12.JE.B.3   12.0.0           Installed
7003             R12.JG.B.3   12.0.0           Not Installed
7004             R12.JL.B.3   12.0.0           Not Installed

Print This Post

R12: Add on Localization Patches

Whenever any company implements Americas, Europe, the Middle East & Africa, APAC specific Rollouts of the Oracle EBS Implementation, it is necessary to check the Add-on Localization Features provided by ORACLE and apply the specific patches to the respective Development/Test environments.

All the Add on Localization Patches for R12 are under the ORACLE Meta link Note: 429042.1

Video to guide you how to look for Add on Localization patches for R12

Print This Post

Correct version of JDeveloper for OA Extensions to use with E-Business suite 11i to R12.x

As the usage of JDeveloper has increased developing OA Extensions to ADF Faces, we need to understand which version of JDeveloper is to be installed depending on our need.

All the details of which version of JDeveloper to use is explained in ORACLE metalink note: 416708.1

Print This Post

Importing Supplier Bank Accounts using Supplier Open Interface

In R12, You can create Supplier Bank Accounts and associate them to a Payee during Supplier Open Interface Program.

Follow below steps:
1. Load Supplier & Supplier Sites Data to the respective Interface Tables AP_SUPPLIERS_INT and AP_SUPPLIER_SITES_INT.
2. Load the respective Suppliers bank Accounts Data to the ‘IBY_TEMP_EXT_BANK_ACCTS’ Table
3. Run the Suppliers Open Interface Import

In R12, While validating/creating Supplier and Supplier Sites Data and inserting to AP and HZ Tables, a Payee is created in to the IBY Table. If the Payee is created successfully then a check is made for any related rows in ‘IBY_TEMP_EXT_BANK_ACCTS’ Table and an IBY API is called to create a Bank Account and associate it to the respective Payee.

Following conditions are to be met for successful import of Bank Account:
- Suppliers
IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref1 = AP_SUPPLIERS_INT.vendor_interface_id
-Supplier Site
IBY_TEMP_EXT_BANK_ACCTS.calling_app_unique_ref2  = AP_SUPPLIER_SITES_INT.vendor_site_interface_id

Note:  Bank and Bank Branch should have already been defined in the System for the successful Bank Account Import.

Print This Post

Customizing: POR_CUSTOM_PKG

POR_CUSTOM_PKG is an Oracle standard package to allow Customization at the Requisition header, line and Distribution levels, its located at $ICX_TOP/patch/115/sql/PORCUSTB.pls

Following are the profiles related to POR_CUSTOM_PKG to enable the Customizations respectively,

POR: Enable Req Header Customization
POR: Enable Requisition Line Customization
POR: Enable Req Distribution Customization

For the profile changes to have an impact, Apache Bounce is mandatory after they have been changed.

The Custom package has 3 major procedures CUSTOM_DEFAULT_REQ_HEADER, CUSTOM_DEFAULT_REQ_LINE, and CUSTOM_DEFAULT_REQ_DIST referring to a Requisition header, Line and Distributions, where the customization can be implemented.

These procedures are called only when a NEW Requisition Headers, lines and Distributions are created rather than an UPDATE performed on them.

The POR_CUSTOM_PKG can be modified to default Descriptive Flexfield values from header to Line and Distribution and to update other values on Requisitions.
To enable the customization, the user has to navigate to the Page where the Requisition DFF value is active and Requisition Header DFF Additional header Information, Requisition Line DFF Additional Line Information, and Requisition Distribution DFF Additional Distribution Information on the Checkout page

Print This Post

Missing Custom integrators in Create Document in R12

Custom Integrator not available to Create Document is due to Function Security as per changes in R12.

You will be able to view all the Custom Integrators when you set the Profile option 'BNE Allow No Security Rule' to ‘Yes’, but the recommended Value for this Profile option is ‘No’.
                             
                                

To be able to view the Custom Integrator in Create Document, you will have to Add a Function ‘Desktop Integration - Create Document (Code: BNE_ADI_CREATE_DOCUMENT)’ to the Custom Integrator. And also make sure that the Function is added to any Custom Responsibility Menu you want to access it from.

Print This Post

AD_DD Package: Oracle Table Registration

Custom Application Tables are registered using a PL/SQL routing in the AD_DD Package

You only need to register the Table if you are to use them with Oracle Alerts or Flexfields. You can also use the AD_DD API to delete the registrations of tables and columns from Oracle Application Object Library tables should you later modify your tables.

If you alter the table later, then you may need to include revised or new calls to the table registration routines. To alter a registration you should first delete the existing registration, then reregister the table or column.

You should delete the column registration first, then the table registration.

The AD_DD API does not check for the existence of the registered table or column in the database schema, but only updates the required AOL tables. You must ensure that the tables and columns registered actually exist and have the same format as that defined using the AD_DD API.

You need not register views.

Procedures in the AD_DD Package:

procedure register_table (p_appl_short_name in varchar2,
                                         p_tab_name             in varchar2,          
                                         p_tab_type               in varchar2,            
                                         p_next_extent          in number default 512,
                                         p_pct_free                in number default 10,
                                         p_pct_used               in number default 70);

procedure register_column (p_appl_short_name in varchar2,
                                             p_tab_name   in varchar2,
                                             p_col_name   in varchar2,
                                             p_col_seq    in number,
                                             p_col_type   in varchar2,
                                             p_col_width  in number,
                                             p_nullable   in varchar2,
                                             p_translate  in varchar2,
                                             p_precision  in number default null,
                                             p_scale      in number default null);

procedure delete_table  (p_appl_short_name in varchar2,
                                        p_tab_name             in varchar2);

procedure delete_column (p_appl_short_name in varchar2,
                                           p_tab_name             in varchar2,
                                           p_col_name             in varchar2);




Example of Using the AD_DD Package:

Here is an example of using the AD_DD package to register a flexfield table and its columns:

EXECUTE ad_dd.register_table('FND', 'CUST_FLEX_TEST', 'T', 8, 10, 90);
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'APPLICATION_ID', 1, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ID_FLEX_CODE', 2, 'VARCHAR2', 30, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATE_DATE', 3, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'LAST_UPDATED_BY', 4, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN', 5, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'UNIQUE_ID_COLUMN2', 6, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SET_DEFINING_COLUMN', 7, 'NUMBER', 38, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SUMMARY_FLAG', 8, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'ENABLED_FLAG', 9, 'VARCHAR2', 1, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'START_DATE_ACTIVE', 10, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'END_DATE_ACTIVE', 11, 'DATE', 9, 'N', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT1', 12, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT2', 13, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT3', 14, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT4', 15, 'VARCHAR2', 60, 'Y', 'N');
EXECUTE ad_dd.register_column('FND', 'CUST_FLEX_TEST', 'SEGMENT5', 16, 'VARCHAR2', 60, 'Y', 'N');


Print This Post

Oracle SLA: Supporting References in R12

Using Support references is an implementation time approach

In 11i additional data is captured using Descriptive Flex fields, but in R12 this feature is enhanced to include additional information at the Event Class level and passed from the Subledgers AP/AR/GL etc., Sources are assigned to the Supporting References to refer to the Supporting References details at the Line Level.

SLA -> Accounting Methods Builder(AMB) -> Journal Entry Setups -> Supporting references

Click on the Create Button and you can define the Supporting references to your need.

You can define maximum of five Supporting Reference details.

Suppose you want to update a Supporting reference, Query the Supporting Reference and click on pencil icon in the Actions section.

These supporting References are stored in XLA_AE_LINES_ACS and relates to XLA_AE_LINES with ae_header_id/ae_line_num.


Print This Post
 

Migrating Web ADI template from one Instance to another

Following are the components that are associated to the Web ADI that can be migrated from one instance to another:

• Integrators
• Layouts
• Mappings
• Contents

Integrators:

Identify the Integrator to migrate as below,

select integrator_code, application_short_name
  from apps.bne_integrators_vl  bnv
      ,apps.fnd_application_vl  fav
 where bnv.user_name = &integrator_name
   and fav.application_id = bnv.application_id;

For the Integrators, you can

Download the Integrator:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bneintegrator.lct INTEGRATOR_LDTFILE.ldt BNE_INTEGRATORS INTEGRATOR_ASN="appl_short_name" INTEGRATOR_CODE="integrator_name"

Upload the Integrator:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/patch/115/import/bneintegrator.lct INTEGRATOR_LDTFILE.ldt

Layouts:

Identify the Layout to migrate as below,

select layout_code
  from apps.bne_layouts_vl
 where user_name = &integrator_name;

For the Above Layouts, you can

Download the Layout:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnelay.lct LAYOUT_LDT.ldt BNE_LAYOUTS LAYOUT_ASN="appl_short_name" LAYOUT_CODE="Layout_code"

Upload the Layout:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnelay.lct LAYOUT_LDT.ldt

Mappings:

Identify the Mapping to migrate as below,


select mapping_code
  from apps.bne_mappings_vl
 where integrator_code = &integrator_code

For the Above Mappings, you can,

Download the Mapping:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnemap.lct MAPPING_LDT.ldt BNE_MAPPINGS MAPPING_ASN="appl_short_name" MAPPING_CODE="Mapping_code"

Upload the Mapping:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnemap.lct MAPPING_LDT.ldt

Contents:

Identify the Contents to migrate as below,

select content_code
   from apps.bne_contents_vl
  where integrator_code = &integrator_code

For the Above Contents, you can

Download the Contents:
FNDLOAD apps/$APPS_PASSWORD 0 Y DOWNLOAD $BNE_TOP/patch/115/import/bnecont.lct CONTENT_LDT.ldt BNE_CONTENTS CONTENT_ASN="appl_short_name" CONTENT_CODE="Content_code"

Upload the Contents:
FNDLOAD apps/$APPS_PASSWORD 0 Y UPLOAD $BNE_TOP/admin/import/bnecont.lct CONTENT_LDT.ldt

Print This Post

Web ADI: Custom Integrators Update Icon

The Update Icon for Custom Web ADI's is greyed out, reason for this is that the Integrator may be associated to the ORACLE Standard User.

For the Custom Web ADI's to be updateable, the Integrators Source should be set to 'C'(Custom) rather than 'O'(Oracle).

If the Custom integrators source is Set to 'O' - Oracle in the table BNE_INTEGRATORS_B , please reset it to 'C' - Custom to make it updateable as below,

update BNE_INTEGRATORS_B
   set SOURCE = 'C'
 where APPLICATION_ID = &application_id
   and INTEGRATOR_CODE = &integrator_code;

Print This Post

Unable to access the 'Web ADI - Update Interface Column Prompts' integrator

The Integrator 'Web ADI - Update Interface Column Prompts' is designed for HR and if HR is not installed/implemented then there is a chance that you will not be able to find it in the list.
To access the integrator, you have to add the Function ‘HR_ADI_CAG_INTERFACE_COLS’ to the Menu ‘Desktop Integration menu’

  Print This Post