Forms Link to Discoverer

When a discoverer report is done users access it via Discoverer Desktop or Discoverer Plus. Access to Discoverer Plus can be via an external
address or via a link within the Oracle, providing direct link from Oracle Applications is a user friendly approach.. To provide the link
firstly a function should be defined via application developer>application>function cells should be defined as Function:"Function_Name", User Function Name:"User_Function_Name","Type:"SSWA jsp function", Mode:"mode=DISCO&Plus=Y",
HTML Call:"OracleOasis.jsp" and Object:"Others". Then this function should be assigned to the wanted Menu.

Forms attachments

Users want to be able to attach files to some standard Forms or custom Forms and sometimes functional consultants provides this.
Firstly table, function, block names and primary key information should be collected for the Form.
Then application developer>attachments>document entities record should be created for the table
After that application developer>attachments>attachment functions categories, blocks and entities datas should be recorded. (Any standard form providing the attachment specialty can be used as an example for the records)

Named SQLs

Here are my useful - named SQLs for TOAD..

DBA objects

select * from dba_objects d
where 1=1
and D.OBJECT_NAME like '%%'
--and D.OBJECT_TYPE='TABLE';

Locked session

select * from v$session where lockwait is not null;

alter system kill session 'SID,SERIAL';

R12 security

exec MO_GLOBAL.SET_POLICY_CONTEXT('S',101);

Set schema

alter session set current_schema=APPS;

User Responsibilities

select user_name, responsibility_key
from apps.fnd_user fu , apps.fnd_user_resp_groups_direct rs, apps.fnd_responsibility rsp
where fu.user_id=rs.user_id
and rs.responsibility_id=rsp.responsibility_id
and user_name like '%'

User initialize

begin
apps.fnd_global.apps_initialize(user_id=>1092,resp_id=>50732,resp_appl_id=>20003);
end;

Table columns, Forms

Here are some useful tables, views and queries to find tables which contains a certain column or forms which contains a certain personalization action or a form's user name etc.

ALL_TAB_COLS
FND_FORMS_CUSTOM_RULES
FND_FORMS_CUSTOM_ACTIONS
FND_FORM
FND_FORM_TL

select table_name, column_name, data_type, data_length, qualified_col_name from ALL_TAB_COLS

select function_name, description, trigger_event, rl.sequence, rl.enabled, form_name, act.enabled
from FND_FORM_CUSTOM_rules rl, FND_FORM_CUSTOM_ACTIONS act
where rl.id=act.rule_id

select f.application_id, f.form_name, ft.language, ft.user_form_name, ft.description
from FND_FORM f, FND_FORM_TL ft
where f.form_id=ft.form_id

Calling an API

An R12 customer wanted to update supplier contact email addresses so i found an API and needed to call it. Below is the written code for this customers need:

create or replace procedure apps.xxxt_vendor_email_update
is
l_vendor_site_id number;
l_commit varchar2(4000) := FND_API.G_TRUE;
l_validation_level number := FND_API.G_VALID_LEVEL_FULL;
x_return_status varchar2(4000);
x_msg_count number;
x_msg_data varchar2(4000);
cursor c_import
is
select *
from xxxt_tedarikci_mail
where 1=1
-- and tedarikci_no='11316';
begin
for r_import in c_import
loop
if (r_import.vendor_name!='ASDF')
then
begin
select vendor_site_id--, aps.vendor_name, vendor_site_code, aps.segment1, aps.vendor_id
into l_vendor_site_id
from ap.ap_suppliers aps , ap.ap_supplier_sites_all apss, xxxt_tedarikci_mail xtm
where aps.vendor_id=apss.vendor_id
and aps.segment1=xtm.tedarikci_no
and xtm.tedarikci_no = r_import.tedarikci_no
and apss.org_id=83
--and aps.vendor_id!=179005;
exception when others then
update xxxt_tedarikci_mail
set error_message = r_import.tedarikci_no
where tedarikci_no=r_import.tedarikci_no;
end;
else
begin
select apss.vendor_site_id
into l_vendor_site_id
from xxxt_tedarikci_mail xtm , ap.ap_supplier_sites_all apss
where 1=1
and APSS.VENDOR_SITE_CODE = xtm.vendor_site_code
and xtm.vendor_site_code=r_import.vendor_site_code
and XTM.E_MAIL is not null
and apss.org_id=83;
exception when others then
update xxxt_tedarikci_mail
set error_message = r_import.tedarikci_no
where tedarikci_no=r_import.tedarikci_no;
end;
end if;
AP_VENDOR_PUB_PKG_w.update_vendor_site(
p_api_version => 1.0
, p_init_msg_list => fnd_api.g_false
, p_commit => fnd_api.g_true
, p_validation_level => l_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p7_a0 => null
, p7_a1 => null
, .
, .
, p7_a94 => r_import.e_mail
, .
, .
, p7_a154 => null
, p_vendor_site_id => l_vendor_site_id
);
end loop;
end;

Form Personalizations: Custom Menu, Changing LOV, Exporting Hidden Canvases

Creating a custom menu to call another form;
In R12 adding new menu(button) under Tools is quite easy. Selecting Action Type as "MENU" and then assigning a menu-special (one of the first 15) is enough. Though to start another action via this button, one more personalization is required which has the assigned menu as trigger event.

Changing LOV using Forms Personalization requires 2 actions:
1- Builtin> Builtin Type:Create Record Group from Query, Arguments:"your select", Group Name:"your select's name".
2- Property> Object Type: LOV, Value:"your select's name"
Selecting the exact columns for the LOV is critical; for example to change a lookup code, "lookup_code" should be selected besides "meaning"

Exporting hidden canvases;
Lets think of a case you want to export purchase order lines including the information in "note to supplier". Because of being under a different canvas exporting the same line twice is the solution. But there is a personalization for this (starting with R12):
when new form instance
object type: block
target object: line
property name: export hidden canvases
values : true

Note: For form personalizations using a database value or using a form value both possible! Example of usage for a forms value ":po_lines.attribute1", for a database
value: "${item.po_lines.attribute1.database_value}" and of course a query can be written to use the database value.

Integration repository

I-repository is a useful source to find out information about interfaces, api s. It has a catalog structure to find the source needed in addition to search tool. It was downloaded externally or could be reached at "irep.oracle.com/index.html" before R12. In R12 EBS it is included in the application via a responsibility named 'Integrated SOA Gateway'

Forms usage shorcut

In e-business suite using forms effectively via shortcuts saves important time, especially underlined letters are very useful. First; pressing 'Alt'+ 'underlined letter' then only 'underlined letter' can increase usage speed observably

Connect by, floor(), round()

Standart sql remains insufficient sometimes, Oracle develops new functions, clauses to solve these in time, here is some;
"connect by prior" clause can be used to select hierarchical information from parent-child related tables. "connect by level" clause can be used to multiply the line count.

"floor" rounds the number to the closest integer lower than itself:
select floor(3.6) from dual
"round" rounds the number to the closest integer though it provides options:
select round(3.6) from dual
select round(3.4) from dual
select round(111.444 , 1) from dual
select round(111.444 , -1) from dual

Query performance

Query performance and optimization is not always being regarded as an important issue, but most of the time it is. Cost of query may not show difference in startup stages of a system but in the long term it affects system performance significantly. So while creating a view, discoverer report or forms select, costs should be evaluated and optimized. First thing to do in selects is finding right columns to match (usually primary and foreign keys) and using indexes if exist. If not exist creating an index may affect dramatically. Using numbers while matching tables, using sub queries, avoiding full table scans are other tips to remember. Also using SQL instead of PL-SQL may reduce time because it saves the time database requires for transacting in PL-SQL mode.
In Forms design there is a difficult but useful option; folder structure. It provides better functional qualifications in addition to better performance provided by its query method.