Forms Personalization Vs Custom.PLL

Query to retrieve forms personalization from database:


SELECT FP.ENABLED,FP.DESCRIPTION PERSONALIZATION_DESC,FP.condition,FP.TRIGGER_OBJECT,FP.SEQUENCE,FP.FORM_NAME,FM.USER_FORM_NAME,F.FUNCTION_NAME,FT.USER_FUNCTION_NAME,F
.TYPE FROM FND_FORM_CUSTOM_RULES FP,FND_FORM_FUNCTIONS F,FND_FORM_FUNCTIONS_TL FT,FND_FORM_TL FM



WHERE F.FUNCTION_ID = FT.FUNCTION_IDAND FM.FORM_ID = F.FORM_IDAND FP.FUNCTION_NAME = F.FUNCTION_NAME--AND F.FUNCTION_NAME like '%'--AND UPPER (condition) LIKE '%%'AND FP.FUNCTION_NAME like 'ONT_OEXOEORD%'ORDER

What is called first? Forms Personalization or CUSTOM.pll
First your form personalization is called, and then CUSTOM.pll is called.

Commonly used events that are trapped using CUSTOM.pll are:-   ZOOM
   WHEN-NEW-FORM-INSTANCE
   WHEN-NEW-BLOCK-INSTANCE
   WHEN-NEW-RECORD-INSTANCE
   WHEN-NEW-ITEM-INSTANCE
   WHEN-VALIDATE-RECORD

However, for example in HRMS, you can also write code in CUSTOM.pll to trap below listed events :-PRE-DELETE and POST-DELETE
PRE-UPDATE and POST-UPDATE
PRE-INSERT and POST-INSERT
POST-FORMS-COMMIT
WHEN-CREATE-RECORD
KEY-DELREC
How and why does CUSTOM.pll work?
Every form in Oracle Apps is created using something called as TEMPLATE.fmb. But some of the modules like HRMS have their own HR Specific Templates[HRTEMPLT.fmb]. These template files have form level triggers that make call to CUSTOM.pll. The triggers that can be trapped using CUSTOM.pll in HRMS screen can be different than those which can be trapped for other modules.

Custom.pll scenarios
1. Change the label of a fieldapp_item_property2.set_property ('BLOCK.FIELD',label,'New Label');

2. Default a value
copy (TO_CHAR (n_person_id),'PERSON_BLOCK.PERSON_ID' );

3. Alter the SQL for LOV Query
PROCEDURE filter_customers_in_lov IS
  v_customer_group_id recordgroup;
  n_temp_id           NUMBER;
  v_customer_lov      lov;
BEGIN
  v_customer_group_id := create_group_from_query('XX_CUSTOMER_GROUP'
                                   ,'select ... from hz_cust_accounts where ..your custom criteria here..');
  n_temp_id           := populate_group(v_customer_group_id);
  v_customer_lov    := find_lov('EXISTING_LOV_NAME_HERE');
  IF get_lov_property(v_customer_lov,group_name) = 'EXISTING_GROUP_NAME_HERE'
  THEN
         set_lov_property(v_customer_lov,group_name,'XX_CUSTOMER_GROUP');
  END IF;
END filter_customers_in_lov;


4. Make a field mandatory
app_item_property2.set_property ('XXBLOCK_NAME.XXFIELD_NAME',required,property_true);
Similarly you can enable or disable the fields too.

5. You can display messages, for example
FND_MESSAGE.CLEAR;
fnd_message.set_name('APPL_SHORT_NAME_HERE', 'MSG_NAME_HERE'); or fnd_message.set_string('message text');
fnd_message.warn or fnd_message.error or fnd_message.


6. Enable or Disable Special Menu
PROCEDURE manage_special_menu IS
  mi_id menuitem;
BEGIN
  mi_id := find_menu_item('SPECIAL.SPECIAL15');
  IF name_in('system.cursor_block') = 'INVOICE_HEADER' THEN
    app_special2.instantiate('SPECIAL15', 'Print Invoice');
    set_menu_item_property(mi_id, displayed, property_true);
    set_menu_item_property(mi_id, enabled, property_true);
  ELSE
    set_menu_item_property(mi_id, displayed, property_false);
  END IF;
END manage_special_menu;


7. Handle the click on Special Menu
  IF event_name = 'SPECIAL15' THEN
    IF form_name = 'INVOICE_FORM' THEN
      xx_invoice_form.process_printing;
    END IF;
    IF form_name = 'SUPPLIER_FORM' THEN
      xx_supplier_form.email_supplier;
    END IF;
  END IF;


8. Ask user a question, and take appropriate action  v_token_value      VARCHAR2(1000);
  n_button_selection INTEGER;
BEGIN
  fnd_message.set_name('APPL', 'MESSAGE');
  fnd_message.set_token('XXTOKEN1', v_token_value);
  n_button_selection := fnd_message.question('Email Invoice', 'Fax Invoice', '', 1, 2, 'question');
  IF n_button_selection = 1 THEN
    xx_call_invoice_print;
  ELSE
    xx_fax_invoice;
  END IF;

9. Call Another form functionfnd_function.EXECUTE(
   function_name => 'XX_FORM_FUNCTION_NAME_HERE'
  ,open_flag        => 'Y'
  ,session_flag     => 'SESSION'
  ,other_params   => 'P_INVOICE_ID = "' || n_invoice_header_id || '"'
  ,activate_flag    => 'Y');


10. Make some segments of a KeyFlexfield Display-Only depending upon some conditionFor example to make 1st segment of a KFF display-only, we can use
IF v_check_result='xyz' THEN
FND_KEY_FLEX.UPDATE_DEFINITION(
     BLOCK => 'BLOCKNAMEHERE'
    ,FIELD => 'FLEXFIELDNAME'
   ,ENABLED => 'Y'
   ,DISPLAYABLE => 1);
END IF ;
BY FP.FORM_NAME, FP.SEQUENCE, F.FUNCTION_NAME

SQL Loader Basics

SQL Loader: -
    userid -- ORACLE username/password          
   control -- Control file name                 
       log -- Log file name                     
       bad -- Bad file name                     
      data -- Data file name                    
   discard -- Discard file name                 
discardmax -- Number of discards to allow          (Default all)
      skip -- Number of logical records to skip    (Default 0)
      load -- Number of logical records to load    (Default all)
    errors -- Number of errors to allow            (Default 50)
      rows -- Number of rows in conventional path bind array or between
direct path data saves
               (Default: Conventional path 64, Direct path all)
  bindsize -- Size of conventional path bind array in bytes  (Default 256000)
    silent -- Suppress messages during run (header,feedback,errors,discards,partitions)
    direct -- use direct path                      (Default FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
  parallel -- do parallel load                     (Default FALSE)
      file -- File to allocate extents from     
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default
FALSE)
  readsize -- Size of Read buffer                  (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_
USED)
columnarrayrows -- Number of rows for direct path column array  (Default 5000)
streamsize -- Size of direct path stream buffer in bytes  (Default 256000)
multithreading -- use multithreading in direct path 
 resumable -- enable or disable resumable for current session  (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
date_cache -- size (in entries) of date conversion cache  (Default 1000)
Notes: -
Data Loading Methods
SQL*Loader provides two methods for loading data:
Conventional Path Load
Direct Path Load

Direct Path Load: Inserts directly in datafiles
(Parallel Loads.  Available with direct path data loads only, this option allows multiple SQL*Loader jobs to execute concurrently)
conventional path loader loads the data by using standard insert statements

GENERATE_ONLY creates a log for External Table
(EXTERNAL_TABLE instructs SQL*Loader whether or not to load data using the external tables option)
Features -
Calling Function in SQL Loader

 dname POSITION(03:16) CHAR "LDR_PCK.NAME(:dname, :deptno)",

  DESCRIPTION "'Salary: '||:SALARY||' -> '||                          
               DECODE (GET_BONUS (:DEPARTMENT), NULL, 'No bonus',
               'Bonus: '||TO_CHAR (GET_BONUS (:DEPARTMENT), 'S09D90'))",
 SALARY      "TO_NUMBER (:SALARY, '99999D99',                         
               'NLS_NUMERIC_CHARACTERS='',.''')",

sqlloader Versus External Table-

• External Table is first introduced in 9i Version of Database.

Steps-1) Create Directory 2) Grant Access on Directory 3) Create External Table 4) Place physical file at location 5) Access Table to Load the data.

External Table uses Oracle Loader Drivers internally.

Example:
CREATE OR REPLACE DIRECTORY ext AS 'c:\external';
GRANT READ ON DIRECTORY ext TO uwclass;
GRANT WRITE ON DIRECTORY ext TO uwclass;

CREATE TABLE ext_tab (
empno  CHAR(4),
ename  CHAR(20),
job    CHAR(20),
deptno CHAR(2))
ORGANIZATION EXTERNAL (
  TYPE oracle_loader
  DEFAULT DIRECTORY ext
    ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE 'bad_%a_%p.bad'
    LOGFILE 'log_%a_%p.log'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (empno, ename, job, deptno))
    LOCATION ('demo1.dat')
  )
PARALLEL
REJECT LIMIT 0
NOMONITORING;

External table can take file name dynamically:
Approach: alter table ext_table location ('new_filename');

Advantage/DisAdv External Table:
The big advantage of external tables is that we can query them from inside the database using SQL
No support for DML. Oracle external tables are read-only.
Poor response for high-volume queries.
No index creation is allowed on an external tables.
An external table cannot load data into a LONG column
You can, although, create views and synonyms for external tables
The external table can be queried and joined directly, in parallel using the SQL statement SELECT
The actual data resides outside the database in OS files, hence the clause ORGANIZATION EXTERNAL organization.
No supported: Use of CONTINUEIF or CONCATENATE to combine multiple physical records into a single logical record
Use of the following database column types: LONGs, nested tables, VARRAYs, REFs, primary key REFs, and SIDs
If there are multiple primary input datafiles with SQL*Loader loads, a bad file and a discard file are created for each input datafile. With external table loads, there is only one bad file and one discard file for all input datafiles. If parallel access drivers are used for the external table load, each access driver has its own bad file and discard file.

External Table to Extract Data
Oracle 10g lets you create a new external table from data in your database, which goes into a flat file pushed from the database using the ORACLE_DATAPUMP access driver.This flat file is in an Oracle-proprietary format that can be read by DataPump.The key difference is that you must specify the access driver, ORACLE_DATAPUMP, since the access driver defaults to ORACLE_LOADER.

Example:SQL> create table export_empl_info
 organization external
 ( type oracle_datapump
 default directory xtern_data_dir
 location ('empl_info_rpt.dmp')
 ) as select * from empl_info;


tables for external table definition:
DBA_EXTERNAL_TABLES
DBA_EXTERNAL_LOCATIONS

Mutating Triggers

Use an "after" trigger - If you must use a trigger, it's best to avoid the mutating table error by using an "after" trigger, to avoid the currency issues associated with a mutating table.  For example, using a trigger ":after update on xxx", the original update has completed and the table will not be mutating.
 

Re-work the trigger syntax - Dr. Hall has some great notes on mutating table errors, and offers other ways to avoid mutating tables with a combination of row-level and statement-level triggers.
 

Use autonomous transactions - You can avoid the mutating table error by marking your trigger as an autonomous transaction, making it independent from the table that calls the procedure.