Mapping Sets Diagrammed

Mapping Sets Diagrammed

• Essentially Mapping Sets are Hash Maps (Key ==> Value)
• Source column values are mapped to Segment Values in Mapping Sets
• Mapping Sets are then placed in Account Derivation Rules(ADR) to map the value in the source column to the corresponding Segment Value is the set

Key==>Value mapping

Target is the account segment:

The value in the TRXN_TYPE column of the transaction is matched to the corresponding value in the Mapping Set(Key==>Value) and the Output value is returned as that segment's value for the transaction line

E-Business Suite Globalization Center (Doc ID 973912.1)

E-Business Suite Globalization Center (Doc ID 973912.1)

Log into Metalink and do a search on ID 973912.1 and then navigate to the “E-Business Suite Globalization Center” page.  There you will find all the R12 localization info.

How to remove CR from csv files

tr -d '\r' < profile_options.csv > profile_options.csv2

Simple Command-line Compile Command

sqlplus -s APPS/***** @/d01/oracle/PROD/apps/apps_st/appl/ad/12.0.0/sql/adutlrcmp.sql APPLSYS ***** APPS ***** ***** 4  0 NONE FALSE

Patches (Key)

Patches added to fix issues






AP

p12983048_R12.POS.B_R12_GENERIC  This Patch resolves the issue of "Error When Trying to Assign Address to a Contact"

PO
p12332819_R12.PO.B_R12_LINUX (Fixes issue in iSupplier where submit ends in error about valid buyer)

AGIS
p11895700_R12.FUN.B_R12_GENERIC (Fixes no access to System Options)
p14323227_R12.FUN.B_R12_GENERIC (Fixes issue with access to Intra-company)

Adding Ledgers GL Accounting Setups
p14506616_R12.PER.B_R12_GENERIC (on Completing Ledger setup Java error where accounting setups does not create Data Access sets nor access to OU's)  (java.sql.SQLException: ORA-20001: HR_7220_INVALID_PRIMARY_KEY: ORA-06512: at "APPS.HR_ORGANIZATION_API", line 4910 ORA-06512: at "APPS.GL_LEDGERS_PKG", line 112 ORA-06512: at line 1) 

After patch is applied successfully go back to Accounting Setups and go to Balancing Segments and click on 'Complete" and a message like the  following should appear:
The General Ledger Accounting Setup Program has been submitted. Please review the concurrent request id: 394778, and make sure the request completes successfully before you enter transactions.

How to Build a Project’s Business Case

One of the many tasks business analysts are inevitably called on to do is create a business case. As defined by the Business Analysis Body of Knowledge, a business case justifies the investment required to deliver a proposed solution.

A business case is part of the due diligence process. By measuring the benefits, costs and risks associated with the investment being made in a particular project or product, it assesses and evaluates the options available to solve the business issue.

Switch Responsibility Form is Showing Duplicate Responsibilities when Changing Responsibility

Switch Responsibility Form is Showing Duplicate Responsibilities when Changing Responsibility [ID 801393.1]

For R 12.1.1, download Patch 8678076.

How to add a context dependent descriptive flexfield on OAF Control Item page (type dependent)

Author: Cyriel van Schaik

We all know (I think :-)) the way in Oracle Forms to create context dependent descriptive flexfields. That is displaying additional segments within the form based on some other value in the form or maybe a value on a profile option.

These context dependent flexfields were at first not possible in framework because the references like BLOCK etc (which you can use in Forms) are not applicable to framework pages. Starting from release 12.0.6 of E-Business Suite there is a way to implement context descriptive flexfields on framework pages.

As an example I'll use some example we encountered during an implementation. There was a requirement that additional details should be given to a control item in Project Management. Control Items in Project Management can be used to record your issues and risk on your running projects. In this case we have 2 Control Item Types being Issue and Risk. Based on the type we need to display additional segments in a DFF for the end user to use. This DFF must change with every type of issue.

To implement this behaviour we need to do a number of things:

1. Implement the DFF and add segments on it for 2 context values (Issue and Risk)
2. Implement a reference value used by the DFF which is the Control Item Type
3. Enable the Descriptive Flexfield on the Control Item page of a project
4. Add a so called Flex Map to the Descriptive Flexfield on the OAF page to hold the control item type (and used by the DFF definition
5. Bounce Apache

Key thing in the above is step number 4. Starting from release 12.0.6 E-Business Suite introduces the functionality of Flex Maps. Follow the below steps to implement the behaviour. Of course the same method can be used in other parts of E-Business Suite if the page has a seeded DFF. If that's not the case than also a self made descriptive flexfield can be added but I'll write a post about that in near future.

Step 1: Implement the DFF for Control Items
a) Go to Application Developer - Flexfield - Decriptive - Segments and query the Control Item flexfield with the name Control Items Desc Flex.

b) Unfreeze the Flexfield Definition if needed. Tick the checkbox Synchronize with Reference Field to make sure the context is updated when you move along control items. Also untick the Displayed checkbox as we don't want to show the Context field to the end user. Set the Reference Field. Add the syntax :$PROFILES$.<Name>. As a name anything can be used. Remember that name because we need it in a later step. In my example I'll set it to :$PROFILES$.TypeCICyriel

c) Add Context Field values. I'll add 2 values named Issue and Risk. It's very important that these names represent the exact naming of your control item types. Add as many as you like representing each option you have for the types. Add your segments to the contexts and assign them to one of the attribute fields.

By default the segments are set to be a required segment. If applicable turn this off by opening the segment (click on Open) and untick the required checkbox.

d) When done adding your context field values and the related segments save the work and freeze the Flexfield definition. Your DFF should compile without problems.

Step 2: Handle the DFF on the framework page (Control Items)

Go to your Control Item page. If you don't have the Personalize options enable the profile options and set it to the username who's going to do the change. Change the below profile options and set it for the specific username on value Y (for Yes).

• Create Seeded Personalizations
• FND: Personalization Region Link Enabled
• FND: Personalization Seeding Mode
• HR: Enable User Personalization
• Personalize Self-Service Defn

Make sure the DFF is enabled (Shown) for the page. If that's not the case than click the Personalize pencil on the row Flex: (PaDescFlex) and change the rendered setting to Yes.

Step 3: Add a Flex Map (bean) to the DFF in OAF through personalization
a) Click on Create Item on the row Flex: (PaDescFlex). You get a new screen where you define the Flex Map settings.

b) Enter the ID which can be anything you want. Leave the Attribute Set and Comments blank. For Name you have to use the exact name as defined in the reference field setting in the DFF definition without the :$PROFILES$. I used the reference field name settting :$PROFILES$.TypeCICyriel so the name of the Reference Value of the Flex Map must be TypeCICyriel.

The Reference Value is very important and will be the placeholder for in this case the Control Item Type name. This setting can use the so called SPEL language with which you reference an attribute of the page or view object of the OAF page. In this case the Control Item Type name is a seeded attribute of the ControlItemVO object of the page. We enter ${oa.ControlItemVO.CiTypeName} as the reference value. The Refers Context option needs to be set to true as we use this Flex Map as a context item.

c) Save all your work and bounce apache if the changes are not reflexted immediately.

The end result will be that when you create a new control item being, in our example, a risk or an issue the applicable additional segments are shown for the type. We've unticked the Displayed checkbox within the DFF definition so it's also not visible in the OAF page. See below the shots for the end results. Good luck with using this feature :-)

Risk showing the 2 defined risk segments of the DFF (Context hidden)

Issue showing the 2 defined issue segments of the DFF (Context hidden)

Using FND Loader

What worked for me:
FNDLOAD apps/biggie O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct TEST2.ldt VALUE_SET FLEX_VALUE_SET_NAME='MAKRO ACCOUNT'

Change the name of the Value set inside the "File.ldt" file and if necessary change the values.  I think a Java program could write out these ldt files and then they could be used as the starting point instead of a go between.

From Oracle:

Some Tips About FNDLOAD

Data Synchronization?
Data Synchronization is a process in which some setup data would be synchronized, and this would be more important when you are working in Oracle application development/implementation project. The equally important that ATG data Migration takes place necessary to synchronize the data across databases instance during
· Installations (New descriptive flex field creations etc)
· Upgrades (Apps upgrade etc.)
· Maintenance (Value set changes etc.)
Generic Loader (FNDLOAD)
The Generic Loader (FNDLOAD) is a concurrent program that can move Oracle Applications data between database and text file representations. The loader reads a configuration file to determine what data to access. For information on specific configuration files consult the Open Interfaces Guide for your product group.
From an FNDLOAD perspective, there is absolute no limitation on parallel execution.  Parallel upload of concurrent programs and responsibilities should through fine unless upload logic in LCT file restrict the same.
There is no way to limit the download of the value hierarchy data with the existing flexfield loader logic. 
If this data is removed manually from the .ldt file generated when downloading data, the upload might error out as a result and this is NOT a supported method of using fndload and the corresponding generated data.
Overview
The Generic Loader can download data from an application entity into a portable, editable text file. This file can then be uploaded into any other database to copy the data. Conversion between database store and file format is specified by a configuration file that is read by the loader.

The Generic Loader downloads data from a database according to a configuration (.lct) file, and converts the data into a data file (.ldt file). The Generic Loader can then upload this data to another database using a configuration file.

The loader operates in one of two modes: download or upload. In the download mode, data is downloaded from the database to a text file; in the upload mode, data is uploaded from a text file to the database.

Data structures supported by the loader include master-detail relationships and foreign key reference relationships.

In both downloading and uploading, the structure of the data involved is described by a configuration file. The configuration file describes the structure of the data and also the access methods to use to copy the data into or out of the database. The same configuration file may be used for both uploading and downloading.

When downloading, the Generic Loader creates a second file, called the data file that contains the structured data selected for downloading. The data file has a standard syntax for representing the data that has been downloaded. When uploading, the Generic Loader reads a data file to get the data that it is to upload. In most cases, the data file was produced by a previous download, but may have come from another source. The data file cannot be interpreted without the corresponding configuration file available.

What can be done?
These are the extensive list which can be done through FNDLOAD
· Concurrent Programs, Executables
· Request Groups, Request Sets
· Profile Options
· Key and Descriptive Flexfields
· Menus and Responsibilities
· Forms and Form Functions/Personalizations
· Attachments
· Messages
· Value Sets and Values
· Lookup Types
· User Responsibilities
· Printer Definitions
· FND Dictionary
· Help Configuration
· Document Sequences
· Alerts
· Concurrent Manager Schedules
The FNDLOAD: Concurrent Program – FNDLOAD can be executed as a concurrent program.
Some advantages when using FNDLOAD
1. Because downloaded data is stored in a text file, version administration is possible
2. There is nothing to worry to go to purchase because Investment = 0$
3. No learning curve. this is relief for developer and dba
4. Fully supported and recommended by Oracle
5. Capture the migrations in a file and use it during installations, clones etc. to migrate in batch
6. Pin-point when something happened and where (database) easily
7. Your AOL data migration process is now simplified and streamlined goal attained
Some disadvantages when using FNDLOAD
1. Applications patching mechanisms use FNDLOAD heavily possibility of negative impact is not zero
2. UPLOAD_MODE=REPLACE only for menus
3. No validation against migrating database/instance sensitive data
Syntax
The Generic Loader is a concurrent program named FNDLOAD. The concurrent executable takes the following parameters:

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ...]
where

<apps/pwd>
The APPS schema and password in the form username/password[@connect_string]. If
connect_string is omitted, it is taken in a platform-specific manner from the environment
using the name TWO_TASK.

< 0 Y >
Concurrent program flags.

mode
UPLOAD or DOWNLOAD. UPLOAD causes the datafile to be uploaded to the database. DOWNLOAD causes the loader to fetch rows and write them to the datafile.

<congle>
The configuration file to use (usually with a suffix of .lct, but not enforced or supplied by the loader).

<datale>
The data file to write (usually with a suffix of .ldt, but not enforced or supplied by the loader). If the data file already exists, it will be overwritten.

<entity>
The entity(ies) to upload or download. When uploading, always upload all entities, so specify a "-" to upload all entities.

< [param] >
Zero or more additional parameters are used to provide bind values in the access SQL (both UPLOAD and DOWNLOAD). Each parameter is in the form NAME=VALUE. NAME should not conflict with an attribute name for the entities being loaded.
Modes of Operation
This is important because it would drive the whole flow, and it always be either Upload or Download.
Example of download
FNDLOAD apps/pwd 0 Y DOWNLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt \ PROGRAM CONCURRENT_PROGRAM_NAME= concurrent_program_short_name> APPLICATION_SHORT_NAME=<application_short_name>
Example of Upload
FNDLOAD apps/pwd 0 Y UPLOAD ${FND_TOP}/patch/115/import/afcpprog.lct myfile.ldt - CUSTOM_MODE=FORCE undocumented parameter

What are FNDLOAD Options?
· Application level OR single entity level download
o (Example) Download all the profile options of Inventory or just the INV: Default Primary UOM
· Entire OR Partial upload of a data file
o (Example) Upload the entire myfile.ldt or just a single entity indicated by - and mode UPLOAD or  UPLOAD_PARTIAL
o Entity name required for UPLOAD_PARTIAL mode
· Custom mode force update
o To override Oracle’s upload algorithm and update the custom AOL data regardless, use CUSTOM_MODE= FORCE
o UPLOAD_MODE= REPLACE (only for menus)
· Support for NLS uploads
o NLS data uploads for translated columns are supported, use UPLOAD_MODE= NLS

Where is Configuration File Located
By default Oracle delivers most of configuration files that can be used to download certain entities.
· Configuration files with extension .lct
o On Unix - all the configuration files are in $FND_TOP/patch/115/import directory
o On Unix Oracle also places the original configuration files in $FND_TOP/admin/import directory
· Data files with extension .ldt
FNDLOAD File Structure
· The configuration files (.lct) are delivered and maintained by Oracle.
· It has entity definitions, parent-child relationships and user input parameters identified by :NAME.
· Downloading a parent automatically downloads all children - (Example) Concurrent Program download.
· The data files (.ldt) have both entity definition and the data.
· It also shows the version and the location of the configuration file (.lct) that was used.
· Without the configuration file, a data file is useless.
· Without the data file, a configuration file is meaningless.
FNDLOAD Files
· Key files: .lct and .ldt
· FNDLOAD must be run as the apps user not as applsys or any other user, otherwise an Ora-6550 error will be received.
· Both are easily readable, editable and portable.
· Do not modify Oracle .lct files.
· Use a favorite editor to manipulate only the .ldt files but be cautious about data type, length, delimiter placements etc.
· Use the log file outputs or .ldt file contents creatively for quick file comparisons and answer questions as: (Why can this be accessed? What is that profile option name, value and level? What is the value set used for that DFF segment attribute10 etc.)
· Partial string searches (which value set has Priority something in its where clause, etc)

Some sample examples

1 - Printer Styles

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcppstl.lct file_name.ldt STYLE PRINTER_STYLE_NAME=”printer style name”

2 - Lookups

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=”prod” LOOKUP_TYPE=”lookup name”

3 - Descriptive Flexfield with all of specific Contexts

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX P_LEVEL=:COL_ALL:REF_ALL:CTX_ONE:SEG_ALL? APPLICATION_SHORT_NAME=”prod” DESCRIPTIVE_FLEXFIELD_NAME=”desc flex name” P_CONTEXT_CODE=”context name”

4 - Multiple Flexfields

Use a combination of APPLICATION_SHORT_NAME and DESCRIPTIVE_FLEXFIELD_NAME names ie. APPLICATION_SHORT_NAME=PER >> will download all PER flexfields DESCRIPTIVE_FLEXFIELD_NAME=PER_% >> will download all flexfields that start with 'PER_'.

FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct
file_name.ldt DESC_FLEX DESCRIPTIVE_FLEXFIELD_NAME="PER_%"

5 - Key Flexfield Structures

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt KEY_FLEX P_LEVEL=:COL_ALL:FQL_ALL:SQL_ALL:STR_ONE:WFP_ALL:SHA_ALL:CVR_ALL:SEG_ALL? APPLICATION_SHORT_NAME=”prod” ID_FLEX_CODE=”key flex code” P_STRUCTURE_CODE=”structure name”

6 - Concurrent Programs

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”prod” CONCURRENT_PROGRAM_NAME=”concurrent name”

7 - Value Sets

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET FLEX_VALUE_SET_NAME=”value set name”

 8 - Value Sets with values

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt VALUE_SET_VALUE FLEX_VALUE_SET_NAME=”value set name”

9 - Profile Options

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct file_name.ldt PROFILE PROFILE_NAME=”profile option” APPLICATION_SHORT_NAME=”prod” 

10 - Request Group

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcpreqg.lct file_name.ldt REQUEST_GROUP REQUEST_GROUP_NAME=”request group” APPLICATION_SHORT_NAME=”prod”

11 - Request Sets

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afcprset.lct file_name.ldt REQ_SET APPLICATION_SHORT_NAME=”prod” REQUEST_SET_NAME=”request set”

12 - Responsibilities 

FNDLOAD apps/pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_RESPONSIBILITY RESP_KEY=”responsibility"

13 - Responsibilities with all Security Groups

FNDLOAD apps/<PASSWORD> 0 Y DOWNLOAD FND_TOP/patch/115/import/afscursp.lct <USER>.ldt
FND_USER USER_NAME="<USER>" SECURITY_GROUP=% DATA_GROUP_NAME=%

14 - Menus

FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=”menu_name”

15 - Forms/Functions/Personalizations: Refer to the System Administrator's Guide on dependencies

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct
<filename.ldt> FND_FORM_CUSTOM_RULES form_name=<form name>

OR

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FUNCTION FUNCTION_NAME=<function_name>

OR

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt FORM FORM_NAME=<form_name>

OR

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt> FND_FORM_CUSTOM_RULES function_name=<function name>

16 - User/Responsibilities 

FNDLOAD apps/ pwd@seeddb 0 Y DOWNLOAD $FND_TOP/patch/115/import/afscursp.lct file_name.ldt FND_USER

17 - Alert

FNDLOAD apps/pwd 0 Y DOWNLOAD
$ALR_TOP/patch/115/import/alr.lct my_file.ldt ALR_ALERTS
APPLICATION_SHORT_NAME=FND ALERT_NAME=Alert name to downloa

18 - Blob

With Release 12.1.1, FNDLOAD supports BLOB data (upload / download ) to better serve content-rich applications.

FNDLOAD apps/pwd 0 Y mode configfile datafile entity [ param ... ]

 19 - Overwrite custom definitions

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/.lct $
XX_TOP/import/.ldt CUSTOM_MODE=FORCE

20 - Load an NLS Language

FNDLOAD <APPS USR>/<APPS PWD> 0 Y UPLOAD <controlfile.lct> <datafile.ldt> \
- UPLOAD_MODE=NLS CUSTOM_MODE=FORCE WARNINGS=TRUE

21 - Migrate the role registration process from one instance to another
a. Please navigate to the path: $FND_TOP /patch/115/import/US/umxrgsvc.ldt

b. The following command can be used to download:

FNDLOAD apps/<PASSWD>@(instance name) O Y DOWNLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX

c. The following command can be used to upload:

FNDLOAD apps/<PASSWD>@(instance name) O Y UPLOAD $FND_TOP/patch/115/import/umxrgsvc.lct umxrgsvc.ldt UMX_REG_SERVICES REG_SERVICE_CODE UMX

22 - Transfer Custom Messages to another Instance
a. Download the message from the source instance.

FNDLOAD apps/apps 0 Y DOWNLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND MESSAGE_NAME=PASSWORD-INVALID-NO-SPEC-CHAR

b. Move the custom LDT file (password.ldt) over to the destination instance.

c. Upload the custom message to the destination instance.

FNDLOAD apps/apps 0 Y UPLOAD @FND:patch/115/import/afmdmsg.lct password.ldt FND_NEW_MESSAGES APPLICATION_SHORT_NAME=FND CUSTOM_MODE=FORCE

23 - Download UMX Roles and Role Assignment data from one instance and upload to another.
To download from one instance:

FNDLOAD <username/pwd@sid> 0 Y DOWNLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt WF_ROLE ORIG_SYSTEM=UMX%

To upload to another instance:

FNDLOAD <username/pwd@sid> 0 Y UPLOAD $FND_TOP/patch/115/import/afrole.lct
umxroles.ldt


References:
· Oracle Applications Systems Administrator Guide - Configuration
Notes:
1. Give special attention when downloading Menus or Responsibilities.  In the case for several developers modifying Responsibilities and Menus, then be very careful.  Not being careful will mean that untested Forms, Functions, and Menus will become available in the clients Production environment besides the tested Forms, Functions, and Menus.
2. Be very careful when downloading flexfields that reference value sets with independent values for GL Segment Codes.  By doing so, downloading and extracting all the test data in GL Codes that might not be applicable for production.
3. There are several variations possible for FNDLOAD.  For example, restricting the download and upload to specific segments within Descriptive Flexfields.
4. FNDLOAD is very reliable and stable, if used properly.
5. Please test the FNDLOAD properly, so as to ensure that no unexpected data occurs.
6. As the name suggests, FNDLOAD is usedful for FND related objects.  However, in any implementation, it's required to migrate the Setups in Financials and Oracle HRMS from one environment to another.  Oracle iSetup can be used for this.  Some of the things that can be migrated using Oracle iSetup are GL Set of Books, HR Organization Structures, HRMS Employees, Profile Options Setup, Suppliers, Customers, Tax Codes & Tax Rates, Financials Setup, Accounting Calendars, Chart of Accounts, GL Currencies.

Determine DFF in Forms

The first step in enabling a Descriptive Flex Field (DFF) in a seeded Form is to find out the name of the DFF. Identifying a DFF involves the following steps:

1. Navigate to the Form which contains the DFF which needs to be identified. In this example, we will consider the Transactions Form under the Receivables Manager responsibility.
2. Click on the DFF and then go to Help>Diagnostics>Examine to open the  ’Examine Field and Variable Values’ window, note down the Block and Field names.

3. In the ‘Examine Field and Variable Values’ window, select  $DESCRIPTIVE_FLEXFIELD$ as the Block and enter <BLOCK_NAME>.<FIELD_NAME> as the Field. <BLOCK_NAME> and <FIELD_NAME> are the values obtained in Step#2. Press the TAB key or click on the Value field. The name of the DFF will be displayed in the Value field along with the application under which it is registered.
4. You can now navigate to Application Developer>Flexfield>Descriptive>Register  and execute a query with the DFF name (obtained in Step#3) in the Title field to obtain the complete details of the DFF.

Set Application User Signon Profile Values

The following E-Business Suite profile options address the basic User Authentication level in an Applications Security model.

• Signon Password Failure Limit - By default, there is no account lockout after a failed number of login attempts. This is just asking to be hacked! I recommend setting a failure limit using the Signon Password Failure Limit profile option. Prior to release 11.5.10, you needed to implement an alert (periodic), custom workflow or report to notify security administrators; now the system ‘locks’ the account. In addition, I recommend notifying security administrators of a lockout by monitoring FND_UNSUCCESSFUL_LOGINS and ICX.ICX_FAILURES tables. Both the FND_UNSUCCESSFUL_LOGINS and ICX.ICX_FAILURES tables capture failed login attempts from the Personal Home Page (Self Service/Web Interface), but failed Forms sessions are only logged to FND_UNSUCCESSFUL_LOGINS.
• Signon Password Hard to Guess - The Signon Password Hard to Guess profile option sets internal rules for verifying passwords to ensure that they will be "hard to guess." Oracle defines a password as hard-to-guess if it follows these rules:
  o The password contains at least one letter and at least one number
  o The password does not contain repeating characters.
  o The password does not contain the username.
  
• Signon Password Length - Signon Password Length sets the minimum length of an Oracle Applications password value. The default length is 5 and I recommended 8.
  
• Signon Password No Reuse - This profile option is set to the number of days that must pass before a user is allowed to reuse a password.
  
• Signon Password Custom - This profile option is used if you want to define your own password scheme (validated by custom Java code) in a custom Java class. This would be used if you have a more advanced and complex password value requirement that is not supported by the site profiles described in this paper. For example, your password policy could state that the password value must have a numeric value, an uppercase value, and a special character. If this were the case, you would not be able to enforce that password policy with the existing Oracle E-Business Suite profiles, so you would need to create a custom password java class and set the profile value to that class name for Signon Password Custom.
  
• Signon Password Case - This profile option is used to force case sensitivity in user passwords. By default in Release 11i, this profile is not populated and the system action defaults to being ‘Insensitive’. This option allows for tighter security, as well as for better integration with Oracle Internet Directory, because it also allows case sensitive passwords. The Define Users form and the Signon form now accept case-sensitive passwords. I recommend setting the Signon Password Case profile value to “Sensitive” at the site level. Setting this profile on an existing system has no affect on existing passwords already stored in the system. The case sensitivity will start to take affect the next time a password value is changed – it is then that the rule is applied.

Note that this profile option was introduced with 11i.ATG_PF_H Rollup 4. There was an earlier attempt in 11i.ATG_PF_H Rollup 3 to implement a similar profile option, ‘Set Password Case’ with three possible values, ‘Insensitive’, Sensitive’, and ‘Mixed’. MetaLink Note 337274.1, “About Oracle Applications Technology 11i.ATG_PF_H Rollup 3 describes this deprecated profile. You will note that the profile option name for this profile did not match the naming convention used by the other password profiles. It appears that Oracle has corrected this with MetaLink Note 365228.1, “About Oracle Applications 11i.ATG_PF.H Rollup 4 (RUP 4)” says that “Mixed” is no longer supported. Users can see, but not update this profile option.

o Insensitive - Passwords are treated as case insensitive. In this mode, the passwords are stored and compared in uppercase, similar to that in earlier releases. During user authentication password validation, the entered password and the decrypted password are compared in uppercase. If the passwords do not match, an error is displayed.
o Sensitive - Passwords are stored and compared as they are, with the password case preserved. During validation, the entered password must match the decrypted version otherwise an error message is displayed. With Release 12, this option is the default behavior. All newly created or changed passwords are treated as case sensitive.
Note: Users who have not changed their passwords since the installation of Release 12 are not affected until they do change their passwords.
Note that by default, Oracle generally does not set these parameters for you. You will need to set up or change the default values to explicitly configure/enable the profile parameters.

Set E-Business Suite Timeout Parameters and Profiles

• ICX Timeout Profile Values

The following E-Business Suite profile options control screen timeouts for Forms, as well as Self Service sessions.  Again, please note, some of the ICX profiles also control Forms Session timeouts!  This can be confusing since Inter-Cartridge Exchange (ICX) is often associated with Self Service applications. This is no longer the case since the release of Framework for the ICX Profiles control the timeout functionality.

Parameter	Default	Recommendation
ICX:Session Timeout	None	30 (minutes)
ICX: Limit Time	4 (hours)	4 (hours)
ICX: Limit Connect	1000	2000

·         ICX:Session Timeout - This profile option determines the length of time (in minutes) of inactivity in a user's form session before the session is disabled.  Note that disabled does not mean terminated or killed.  The user is provided the opportunity to re-authenticate and re-enable their timed-out session. If the re-authentication is successful, the disabled session is re-enabled and no work is lost. Otherwise, the session is terminated without saving pending work.  This functionality is available via Patch 2012308 (included in 11.5.7, FND.E).  Note: Setting the profile value to greater than 30 minutes can drain the JVM resources and cause ‘out of memory’ errors.

·         ICX: Limit time - This profile option defines the maximum connection time for a connection – regardless of user activity.  If 'ICX:Session Timeout' is set to NULL, then the session will last only as long as 'ICX: Limit Time', regardless of user activity. 

·         ICX: Limit connect - This profile option defines the maximum number of connection requests a user can make in a single session. Note that other EBS internal checks will generate connection requests during a user session, so it is not just user activity that can increment the count.

Enabling Forms Socket Mode In EBS/Apps

Oracle Applications Release12 by default configures Forms 10g stack in servlet mode, as this is the preferred and recommended deployment model for deploying forms on the web.  In this mode, there is a java servlet called the Forms Listener Servlet, manages the communication between the Forms Java Client and OracleAS Forms Services. The Forms Listener Servlet architecture operates through the HTTP server port alone and does not need extra ports to handle communication between the client and the application server. The servlet architecture is designed to work with industry standards, and also to be fully supported  for advanced network configurations with no additional ports or SSL configuration.

To check if forms is implemented in socket /servlet mode perform the following steps: 
Step 1: Source the middle-tier environment file from <Install_Home>/apps/apps_st/appl directory
Note:  Replace <Install_Home>  with the base directory where Oracle Application R12 is installed in your system
Step 2: Open the appsweb.cfg file pointed by environment variable "FORMS_WEB_CONFIG_FILE" and check the values for following parameters:

• serverURL=
• connectMode=

Note:  Refer the table below for the values that they must contain based on the implementation mode 
 

In servlet mode	In Socket mode
serverURL=/forms/lservlet connectMode=servlet	serverURL=(should be blank) connectMode=Socket

Although forms servlet is the preferred deployment method, there may be circumstances where customers need to switch from Forms servlet mode to socket mode, which allows desktop clients to access the Forms server directly. This may be required in the following situations:

• Customers network topology is multinode and the Forms Services are configured on a node different from the node on which Web services(Web Entry Point and Web Applications) are configured.
• Customers constrained by network bandwidth, or machine resources may consider socket mode as an alternative to improve performance. 
  
• To reduce network traffic. The servlet mode uses http protocol on each transaction between a client and the Forms Server requiring the exchange of cookies and http headers which increases network traffic. 
• To reduce consumption of resources use by the JVMS needed in servlet mode architecture. 

Forms10g	Comments
Listener executable name is frmsrv	Both executables are present in $ORACLE_HOME/bin.
Listener supports only non secure socketmode and servlet mode	In Oracle Applications Release 12 customers who need http or https must use the servlet implementation.
Forms metric server configuration is obsolete in Forms10g	In Oracle Applications Release 12 customers who want  load balancing must use the servlet implementation in which forms servers can be balanced across multiple application servers by using a hardware load balancer along with the 10g Application Server.
CGI implementation is obsolete and executable is not present in 10g	Oracle Applications Release 12 the initial request that dynamically generates the html page to start the forms applet is processed by the Forms Servlet, although the servlet only receives one request per forms session.
Forms socket mode implements a default 40 bit encryption based on keys randomly generated at runtime	Oracle do not promote the default encryption as a "secure solution". Customers  who require high levels of security should use the Forms servlet configured for https.  If they need to use socket mode, they should use a secure VPN connection to provide the required level of security.

Imp Note - Forms Socket mode in Oracle Applications Release 12 is currently not supported on Windows platforms.

Process for Enabling Forms Socket Mode

Execute the following steps to switch from Forms Servlet mode to Forms Socket mode:

-- Source the environment on the application tier.

-- Stop all the application tier services:

$INST_TOP/admin/scripts/adstpall.sh

-- Run the following command to enable Forms Socket Mode:

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \

[-contextfile=<CONTEXT_FILE>] \

-mode=socket \

[-port=<Forms port number>] \

-runautoconfig=<No or Yes> \

-appspass=<APPS password>

$FND_TOP/bin/txkrun.pl -script=ChangeFormsMode \

-contextfile=$INST_TOP/appl/admin/mycontext.xml \

-mode=socket \

-port=9095 \

-runautoconfig=No

-- If Autoconfig was not automatically executed as part of the preceding step, then run AutoConfig on the application tier:

$INST_TOP/admin/scripts/adautocfg.sh

-- Start all the application tier services:

$INST_TOP/admin/scripts/adstrtal.sh

-- Check whether Forms Server is running:

$INST_TOP/admin/scripts/adformsrvctl.sh status

-- Log in to Oracle Applications and  launch a Forms-based application.

-- Open the Sun Java Console (from Tools Menu in Internet Explorer).

-- Check whether the "mode" directive displayed in Sun Java Console when launching forms-based applications is set to socket.

-- The direct launch URL for Forms Socket Mode is:

<web_protocol>://<web_host>.<web_domain>:<web_port>/OA_HTML/frmservlet
http://webport>/OA_HTML/frmservlet?serverURL=&serverPort=9000connectMode=socket
http://ora12.t-shelf.com:8000/OA_HTML/frmservlet?serverURL=&serverPort=9000&connectMode=socket

6601055

STOPPING FORM SERVER (FRMSRV) DOES NOT STOP CHILD FRMWEB PROCESSES

Apply patch 6329757.

Location of the context files for Autoconfig

Apps side
/d01/oracle/PROD/inst/apps/PROD_ora12/appl/admin/PROD_ora12.xml

DB side
/d01/oracle/PROD/db/tech_st/11.1.0/appsutil/PROD_ora12.xml

Autoconfig scripts located in the respective APPS and DB startup locations.

Oracle Financials Latest Patch recommendations RPC

EBS: R12.1 Oracle Financials Recommended Patch Collection (RPC) [ID 954704.1]

Change APPS y APPLSYS password

FNDCPASS apps/apps 0 Y system/manager ALLORACLE WELCOME
FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

A). To change the APPS and APPLSYS schema password:
http://mtalmasri.blogspot.com.es/2012/01/changing-apps-password-using-fndcpass.html
Use the following command to change passwords for schema that are used by shared components of Oracle Applications.

FNDCPASS <logon> 0 Y <system/password> SYSTEM <username> <new_password>
FNDCPASS uses the following arguments when changing the APPLSYS password. When specifying the SYSTEM token, FNDCPASS expects the next arguments to be the APPLSYS username and the new password.
How to Change Applications Passwords using Applications Schema Password Change Utility (FNDCPASS or AFPASSWD) [ID 437260.1]


logon                           The Oracle username/password.
system/password     The username and password for the SYSTEM DBA account.
username                   The APPLSYS username. For example, 'applsys'.
new_password         The new password.
This command does the following:

Validates APPLSYS.
Re-registers password in Oracle Applications.
Changes the APPLSYS and all APPS passwords (for multi-APPS schema installations) to the same password.
Because everything with a Privilege Level [set to any of ('E', 'U', 'D')] in the FND_ORACLE_USERID table must always have the same password, FNDCPASS updates these passwords as well as APPLSYS's password.
For example, the APPS password will be updated when the APPLSYS password is changed.
ALTER USER is executed to change the ORACLE password for the above ORACLE users.
For instance, the following command changes the APPLSYS password to 'WELCOME'.

FNDCPASS apps/apps 0 Y system/manager SYSTEM APPLSYS WELCOME

Oracle Apps R12: Check if the patch applied from sqlplus

Two tables to check if the patch is applied or not:

This table includes the defined bugs on the system:
SELECT   bug_number
FROM     apps.ad_bugs
WHERE   bug_number LIKE '%' ||3594604 || '%';

This table includes patches applied on the system:
SELECT patch_name
FROM   apps.ad_applied_patches
WHERE patch_name LIKE '%' ||3594604 || '%'

Oracle Space Management Tutorial

Oracle Space Management Tutorial

Your Oracle Database has three basic blocks.

1. Blocks
2. Extents
3. Segments

Blocks

Block is the lowest storage unit of database. Block must equal or multiple of the os block size to mazimize the I/O performance. Define the lowest block size during database creation using following init parameter DB_BLOCK_SIZE.

A newly created block contain free space for new rows or index [only one type of data is allowed in block]. When block data exceed PCTFREE of a block new row or updated row will be assigned to a new block with a pointer pointing the new block from old block.

PCTFREE parameter defines the free space inside a block which is kept for future update of row value.

Starting with Oracle9i Release 2, you can use Automatic Segment Space Management (ASSM) to manage free space within blocks; you enable ASSM in locally managed tablespaces by using the segment space management auto keywords in the create tablespace command (although this is the default for locally managed tablespaces).

Extents

Extents holds the object and consists of several blocks. When a table is created, an initial extent is allocated. Once the space is used in the initial extent, incremental extents are allocated. In a locally managed tablespace, these subsequent extents can either be the same size (using the UNIFORM keyword when the tablespace is created) or optimally sized by Oracle (AUTOALLOCATE). For extents that are optimally sized, Oracle starts with aminimum extent size of 64KB and increases the size of subsequent extents as multiples of the initial extent as the segment grows.

For checking extents information use the following sql command sets.

SQL> create table xxxx (a char(2000))

2 storage (initial 1m next 2m pctincrease 50)

3 tablespace users;

SQL> begin

2 for i in 1..3000 loop

3 insert into xxxx ('a');

4 end loop;

5 end;

6 /

SQL> select segment_name, extent_id, bytes, blocks from user_extents where segment_name = 'XXXX';

Unless a table is truncated or the table is dropped, any blocks allocated to an extent remain allocated for the table, even if all rows have been deleted from the table. The maximum number of blocks ever allocated for a table is known as the high-water mark (HWM).

Segments

Groups of extents are allocated for a single segment. A segment must be wholly contained within one and only one tablespace. Every segment represents one and only one type of database object, such as a table, a partition of a partitioned table, an index, or a temporary segment. For partitioned tables, every partition resides in its own segment; however, a cluster (with two or more tables) resides within a single segment. Similarly, a partitioned index consists of one segment for each index partition.

Database space usage views

The following objects gives the details of database space usage.

1. DBA_TABLESPACES
2. DBA_SEGMENTS
3. DBA_EXTENTS
4. DBA_FREE_SPACE
5. DBA_LMT_FREE_SPACE
6. DBA_THRESHOLDS
7. DBA_OUTSTANDING_ALERTS
8. DBA_ALERT_HISTORY
9. V$ALERT_TYPES
   select reason_id, object_type, scope, internal_metric_category,
   internal_metric_name from v$alert_types
   where group_name = 'Space';

Sample select queries.

• select tablespace_name, block_size, contents, extent_management from dba_tablespaces;
• select tablespace_name, count(*) NUM_OBJECTS,sum(bytes), sum(blocks), sum(extents) from dba_segments group by rollup (tablespace_name);
• select owner, segment_name, tablespace_name,extent_id, file_id, block_id, bytes from dba_extents where segment_name = 'XXX$';
• select tablespace_name, sum(bytes) from dba_free_space group by tablespace_name;

OMF

OMF has been introduced in oracle database by which without mentioning the exact file location we can create the datafiles, redo log or backup files in specified destination. The specific destinations are mentioned in the following init parameter.

DB_CREATE_FILE_DEST = The default operating system file directory where datafiles and tempfiles are created if no pathname is specified in the create tablespace command.

This location is used for redo log files and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.

DB_CREATE_ONLINE_LOG_DEST_n = Specifies the default location to store redo log files and control files when no pathname is specified for redo log files or control files at database-creation time. Up to five destinations can be specified with this parameter, allowing up to five multiplexed control files and five members of each redo log group.

DB_RECOVERY_FILE_DEST = Defines the default pathname in the server’s file system where RMAN backups, archived redo logs, and flashback logs are located. Also used for redo

log files and control files if neither DB_CREATE_FILE_DEST nor DB_CREATE_ONLINE_LOG_DEST_n is specified.

How to know about the Segments That Cannot Allocate Additional Extents?

select s.tablespace_name, s.segment_name,

s.segment_type, s.owner

from dba_segments s

where s.next_extent >=

(select max(f.bytes)

from dba_free_space f

where f.tablespace_name = s.tablespace_name)

or s.extents = s.max_extents

order by tablespace_name, segmen

How to know the datafile and tablespace space usage and free space?

http://www.oracle-latest-technology.com/2011/12/oracle-dba-health-checkup-and.html

Check the DIAGNOSTIC_DEST parameter for logfile, trace file and alert file location.

How to set Space Usage Warning Levels in Oracle database ?

SQL> select metrics_name, warning_operator warn, warning_value wval,

critical_operator crit, critical_value cval,

consecutive_occurrences consec

from dba_thresholds;

--

-- PL/SQL anonymous procedure to update the Tablespace Space Usage threshold

--

declare

/* OUT */

warning_operator number;

warning_value varchar2(100);

critical_operator number;

critical_value varchar2(100);

observation_period number;

consecutive_occurrences number;

/* IN */

metrics_id number;

instance_name varchar2(50);

object_type number;

object_name varchar2(50);

new_warning_value varchar2(100) := '60';

begin

metrics_id := DBMS_SERVER_ALERT.TABLESPACE_PCT_FULL;

object_type := DBMS_SERVER_ALERT.OBJECT_TYPE_TABLESPACE;

instance_name := 'xx';

object_name := NULL;

-- retrieve the current values with get_threshold

dbms_server_alert.get_threshold(

metrics_id, warning_operator, warning_value,

critical_operator, critical_value,

observation_period, consecutive_occurrences,

instance_name, object_type, object_name);

-- update the warning threshold value from 85 to 60

dbms_server_alert.set_threshold(

metrics_id, warning_operator, new_warning_value,

critical_operator, critical_value,

observation_period, consecutive_occurrences,

instance_name, object_type, object_name);

end;

Now check the status from dba_thresholds again.

How to check and monitor space usage by index

SQL> alter index hr.emp_ix monitoring usage;

Index altered.

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING

--------------- --------------- --- ---- -------------------

EMP_IX EMPLOYEES YES NO 12/23/2012 10:00:00

SQL> alter index hr.emp_ix nomonitoring usage;

SQL> select * from v$object_usage;

INDEX_NAME TABLE_NAME MON USED START_MONITORING END_MONITORING

---------- --------------- --- ---- ------------------- ---------------

EMP_IX EMPLOYEES NO YES 12/23/2012 10:04:55 12/24/2012 11:00:00

Sure enough, the index appears to be used at least once during a typical day.

SQL> analyze index hr.emp_ix validate structure;

SQL> select pct_used from index_stats where name = 'EM_IX';

PCT_USED

----------

76

SQL> analyze index hr.emp_ix validate structure;

SQL> select pct_used from index_stats where name = 'EMP_IX';

PCT_USED

----------

26

SQL> alter index hr.emp_job_ix rebuild online;

Index altered.

SEGMENT Space Management

Frequent inserts, updates, and deletes on a table may, over time, leave the space within a table fragmented. Oracle can perform segment shrink on a table or index.

SQL> alter table hr.emp add (work_record varchar2(4000));

SQL> alter table hr.emp enable row movement;

We have enabled ROW MOVEMENT in the table so that shrink operations can be performed on the table if recommended by Segment Advisor.

variable task_id number

-- PL/SQL block follows

declare

name varchar2(100);

descr varchar2(500);

obj_id number;

begin

name := ''; -- unique name generated from create_task

descr := 'Check HR.EMPLOYEE table';

dbms_advisor.create_task('Segment Advisor', :task_id, name, descr, NULL);

dbms_advisor.create_object(name, 'TABLE', 'HR', 'EMP', NULL, NULL, obj_id);

dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE');

dbms_advisor.execute_task(name);

end;

/

SQL> print task_id

TASK_ID

----------

380

SQL>

Now that we have a task number from invoking Segment Advisor, we can query DBA_ADVISOR_FINDINGS to see what we can do to improve the space utilization of the HR.EMP table.

SQL> select owner, task_id, task_name, type, message, more_info from dba_advisor_findings where task_id = 380;

WORK_RECORD columns may create blocks in the table with free space that can be reclaimed. The view DBA_ADVISOR_RECOMMENDATIONS provides similar information.

SQL> select owner, task_id, task_name, benefit_type from dba_advisor_recommendations

where task_id = 380;

OWNER TASK_ID TASK_NAME

---------- ------- ----------

RJB 380 TASK_00003

BENEFIT_TYPE

--------------------------------------------------

Perform shrink, estimated savings is 107602 bytes.

In any case, we will shrink the segment HR.EMP to reclaim the free space. As an added time-saving benefit to the DBA, the SQL needed to perform the shrink is provided in the view DBA_ADVISOR_ACTIONS.

SQL> select owner, task_id, task_name, command, attr1 from dba_advisor_actions where task_id = 384;

OWNER TASK_ID TASK_NAME COMMAND

---------- ------- ---------- -----------------

RJB 6 TASK_00003 SHRINK SPACE

ATTR1

-----------------------------------------------------

alter table HR.EMPLOYEES shrink space

1 row selected.

SQL> alter table HR.EMPLOYEES shrink space;

Table altered.

As mentioned earlier, the shrink operation does not require extra disk space and does not prevent access to the table during the operation, except for a very short period of time at the end of the process to free the unused space.