Monday, April 7, 2008

Standard Forms Customization(Part1--Custom PLL )Draft

There are different ways to handle Forms Level Customizations.
1.Using Custom.PLL
2.Using Forms Personalizations
3.Copy the Standard form Object and Change the Code

First lets see Custom.pll how to use it?


What is Custom.PLL??
The CUSTOM.pll library is a standard Oracle Forms PL/SQL library that is supplied by Oracle with the Oracle
Applications. This is Oracle’s built-in feature that allows the customer to enhance the standard functionality of the
Applications by implementing site-specific business rules. Every Oracle Forms -based eBusiness screen, and any
custom form developed using the Oracle Application development standards, will access the CUSTOM library.
This makes an ideal point of creating business rules that effect the entire organization.

Where is this located?
Custom.pll is located in $AU_TOP/resource Directory.

How to add code to this ?
open this pll using the Form builder.make changes to the program units

How to compile this PLL ?
Once you make changes you need to compile the pll.use the F60gen to compile it
f60gen module=custom.pll userid=APPS/ output_file=$AU_TOP/resource/custom.plx module_type=library batch=no compile_all=special

What are Different Triggers that is supported?

WHEN-NEW-FORM-INSTANCE – initially entering a form

WHEN-NEW-BLOCK-INSTANCE – entering a zone (or block) within a form

WHEN-NEW-ITEM-INSTANCE – moving into a new field within the form

WHEN-NEW-RECORD-INSTANCE - creating a new record

WHEN-FORM-NAVIGATE – navigating thru a form using the mouse

WHEN-VALIDATE-RECORD – saving (committing) the information to the database

EXPORT – triggered by using the Export feature Some events are field specific

ZOOM – Pre -11 feature for moving to another form and querying up specific records

Some events are form specific

SPECIALn - (where n is a number between 1 and 45) used to generate entries in the ‘Special’ menu of the
tool bar and the code is triggered by selecting a menu choices from the ‘Special’ option on the toolbar

KEY-Fn – (where n is a number between 1 and 8) triggered by pressing the corresponding function key

Some events are application specific:
Application Object Library

WHEN-LOGIN-CHANGED – when a user logs on as a different user
WHEN-RESPONSIBILITY-CHANGED – when a user changes responsibilities
WHEN-PASSWORD-CHANGED – when a user changes their password

How to make changes get affected?
Once you make the changes compile the pll and generate the PLX
Since the CUSTOM library is loaded once for a given session, a user must log out of the
application and sign-on again before any changes will become apparent.



Examples--Metalink:

1. Sample code to make all the responsibilities read only for a specific user.
BEGIN

IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN
IF FND_PROFILE.VALUE('USER_NAME')='' THEN

BEGIN
COPY('Entering app_form.query_only_mode.','global.frd_debug');
COPY('YES', 'PARAMETER.QUERY_ONLY');
APP_MENU2.SET_PROP('FILE.SAVE', ENABLED,PROPERTY_OFF);
APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED,PROPERTY_OFF);
formname := NAME_IN('system.current_form');
blockname := GET_FORM_PROPERY(formname, FIRST_BLOCK);

WHILE (blockname is not null) LOOP

IF (GET_BLOCK_PROPERTY(blockname, BASE_TABLE) is not NULL) THEN

SET_BLOCK_PROPERTY(blockname, INSERT_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, UPDATE_ALLOWED, PROPERTY_FALSE);
SET_BLOCK_PROPERTY(blockname, DELETE_ALLOWED, PROPERTY_FALSE);

END IF;

blockname := GET_BLOCK_PROPERTY(blockname, NEXTBLOCK);

END LOOP;


END query_only_mode;


END;

2.How does one restrict or reduce the LOV?
"The customer LOV can be overriden using the when-new-item-instance or when-new-form-instance event at the form level through CUSTOM.pll."
You will need to write custom code using that specific event in the custom.pll

Some sample code
if (event_name = 'WHEN-NEW-FORM-INSTANCE' and form_name = 'form name')then
r:=find_group('group name');
if not id_null(r) then
delete_group('group name');
end if;
v:='select colum1,column2
from table';

r:=create_group_from_query('group name',v);

set_lov_property('lov NAME',group_name,r);--lov

See that the column names should be same as the old query so that the mappings still holds good



3.How to make the attachment function in specific responsibilities to act as read-only mode so that users who log into these specific responsibilities can only view attachments, while for the rest of the responsibilities allow users to add, update and delete attachments?


// Source File Name: custom.pll
// Source File path: $AU_TOP/resource

form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (event_name = 'WHEN-NEW-FORM-INSTANCE') then
if (form_name = 'FNDATTCH') then
if (FND_GLOBAL.RESP_NAME Like '') then --
Set_item_Property( SEQ_NUM, ENABLED,PROPERTY_FALSE);
Set_item_Property( CATEGORY_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DOCUMENT_DESCRIPTION, ENABLED,PROPERTY_FALSE);
Set_item_Property( DATATYPE_NAME, ENABLED,PROPERTY_FALSE);
Set_item_Property( FILE_NAME_DISPLAY, ENABLED,PROPERTY_FALSE);
end if;
end if;
end if;

4. How to make the customisation CustomPO Number not less than PO 4 digits in sales order form?

procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
item_name varchar2(30) := name_in('system.cursor_item');

Begin
if (form_name = 'OEXOEORD'and block_name = 'ORDER') then
if LENGTH(name_in('ORDER.CUST_PO_NUMBER')) > 3 then
fnd_message.set_name('FND','Cust PO Number should be less than 4 digits');
fnd_message.Error;
RAISE FORM_TRIGGER_FAILURE;
End if;
End if;
End Event;

16 comments:

AshishD said...

Hi Gunjan ,
you r doing awesome work for freshers like us who just started to learn oracle-apps.
I have small doubt recently i worked on forms 4.5 in which Template.fmb have 5-6 pre-exsiting blocks i.e. Calender , does every template file have those regardless of versions.

Thanks And Regards
Ashish D

Raj said...

Hi Gunjan,
This is article is really nice.Is there any way we can
make all the responsibilities read only for "Multiple users".

Thanks,
Raj

Anonymous said...

Hi Murthy,
Thanks a lot for knowledge sharing!Keep posting useful information!

Thanks
R.V.Varma

kapil said...

Hi Murthy

Need to change the form mode to query only through form personalization only based upon value returned from one of my function.
if it needs to be done at block level that we can do through form personalization but if at form level we need to change the form mode to read only then that doesn't seems to be possible can u help in this case

Thanks
Kapil Kumar

venkat said...

Hi Murthy,

Great Work.... Keep it up...

Thanks and Regards,
Venkatesan.R

Cadbury Girl said...

Hello,
I m a newbie in Oracle Apps and an object I'm developing wants a customization.
So, in, INVENTORY > Setup > Items > Categories > Category Codes

Objective: If Structure Name is SWN PO Categories, require DFF ATTRIBUTE1, 2, and 3.

Can you tell me how this can be done with custom.pll?
Thanks ahead !

S Reddy said...

Hi Murthy,
Thanks for knowledge sharing!

Thanks
S Reddy

Ramya said...

Hi All,

I have a requirement saying if person type is employee then make "Employee Category" item as Mandatory fields.I did like this.

Trigger Event:When_new_record_instance
Trigger Object:ASSGT
Condition: ${item.ASSGT.EMPLOYEE_CATEGORY_MEANING.value} is null

Context:USER LEVEL

Actions Property

Object Type:item
target Object: ASSGT.EMPLOYEE_CATEGORY_MEANING
Property Name:REQUIRED
VALUE:TRUE

I saved it and created a new employee but even though "Employee Category" is null i can able to save.Only the field color get changed to orange.

I want it to show some error message when saving the record, if the "Employee Category" is null.

Please help me to achieve this..

Thanks in advance
Ramya

Ravinder Gogula said...

Hi Gunjan,

Very good article.

I have a requirement, need to enable and disable attachment icon on oracle form toolbar between header and lines section/level of Purchase Requisition.

Header level -> Enable
Lines level -> Disable

Could you please tell me the steps to achieve this.

Your help highly appreciated.
Thanks,
Ravi

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...
This comment has been removed by a blog administrator.
srujan goud said...

Hi Gunjan,

Very good article,

i have requirement custom.pll , In PO to make creation date and creation date +1 to creation date +30days .

when i create requsition ( creation_date : 12/10/2015 ) if need by date is ( 12/11/2015 ) if the difference is one , while do doing auto creation in po , the need by date should update to creation date +30.

Thanks in advance

Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...
This comment has been removed by the author.
Sridevi Koduru said...


Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

Unknown said...

Hi ,
Need a form personlization on button click. Need to do validation of fields. The trigger is when form navigate