Chitika Add

Thursday, October 24

Forms Personalization and CUSTOM.pll introduction



Some Introduction on Forms personalization and CUSTOM.pll

Forms personalization: is a new feature provided by Oracle starting from 11.5.10 version. This is a user interface for altering/customizing the oracle apps behavior.
Below is the navigation for Forms Personalization.

Menu Navigation: Help-->Diagnostics-->Custom Code-->Personalize.

The personalization form should be used to implement the custom rules on a specific form. The
specificform refers to the desired form on which you want to apply the custom business logic or
modify the form behavior.

CUSTOM.pll: is a pl/sql library which is present in UNIX Box in $AU_TOP/resource. It is a single library which is used for altering/Customizing the Oracle apps behavior.

Below tasks can be performed by both Forms personalization and CUSTOM.pll
1) Disabling the Button/field or any item on the form
2) Make the fields/DFF read only.
3) Pop some warning/error messages/ interactive messages.
4) populate other fields based on the other input field.
5) create some LOV on fly
6) Change the navigation/block in the form.

Advantages of Forms personalization
i) Forms personalization can be applied on any form easily with simple navigation from the form.
Menu Navigation: Help-->Diagnostics-->Custom Code-->Personalize.
ii) Multiple users can work on Forms personalization
iii) Can be easily migrated from instance to instance by using FNDLOAD
iv) The forms personalization rules are stored in the FND tables

Advantages of CUSTOM.pll
a) Not all triggers can be performed by Forms personalization, like using 'WHEN-NEW-ITEM-INSTANCE'
b) User interactive messages and actions can be added.

Alerts in Oracle Apps in R12



Alerts are basically used automate the system maintenance, provide reports in the format chosen,for database activity/Business Requirement as it occurs..

few scenarios:
- A database event which was supposed to be monitored for instance insert into some table(event based alert).
- A SQL statementwhich provides specific information (event based alert).
- The frequency we want the report of a SQL statement(periodic alert)

Responsibility Name: Alert Manager

Pre-requisites:
Navigation: Alert Manager --> System --> Options
-- Electronic Account Mail setup
-- Oracle Alert Mailbox
Navigation: Alert Manager --> System --> Installations
-- Setup if there are any CUSTOM application where the Alert needs to be created.

Types of Alerts: Event based and Periodic
-- Both types of alerts are defined by SQL statement specified in the SQL section.

Periodic Alert: Navigate to alert form and select the periodic Alert option and then below tasks to be performed.
- Application Name : Application name that owns the alert
- Alert Name : Name of the alert(up to 50 chars) with some meaningful description(up to 240 chars) and check the enabled field.
- Frequency : Select the frequency of the alert,below are the available options.
On Demand
On Day of the month
On Day of the week
Every N calendar Days
Every Day
Every Other Day
Every N Business Days
Every Business Day
Every Other Business Day
Also Start time and end time, number of times in 24 hours and end date if not required after some date.

- SQL : Enter a SQL Statementthat retrieves all the alert information, that can used for actions to be planned.The statementshould have INTO clause and one

output field.
Example: Input field    :INPUT_NAME
     Output field   &OUTPUT_NAME
Oracle Alert does not support pl/sql statements, but we have a provision to create the database function and use in the SQL statement.
Example: SELECT PACKAGE.FUNCTION_NAME(:INPUT_VALUE) INTO &OUTPUT_VALUE FROM DUAL;

IMPORT Option: Rather than creating a SQL statement, we can also import from file and can use in the application.

Event Alert: Navigate to alert form and select the Event Alert option and then below tasks to be performed.
- Application Name : Application name that owns the alert
- Alert Name : Name of the alert(up to 50 chars) with some meaningful description(up to 240 chars) and check the enabled field
- Specify the event table name (application be different that the table resides) but privilegesshould be present.
- Check the insert/update, when the alert has to be fired.

- SQL : Enter a SQL Statementthat retrieves all the alert information, that can used for actions to be planned.The statementshould have INTO clause and one

output field.
Example: Input field    :INPUT_NAME
     Output field   &OUTPUT_NAME
Oracle Alert does not support pl/sql statements, but we have a provision to create the database function and use in the SQL statement.
Also make sure there is a condition based on the event table in the SQL, by using :ROWID condition.

Example:
SELECT user_name INTO &NEWUSER FROM fnd_user WHERE rowid = :ROWID;

The SQL statement should be verified, run then it should be saved in the alert.

- Alert Details: Provide the input,output and installation/user id details.

- Action: Create the action Name and click on the action details.
Action type can be set as Message,SQL Statementscript and operating system script.
and provide the details for the message details like To list and message data.

- Action sets:Once the alert actions are created,those alert actions can be included inthe action sets.