Saturday, December 27, 2014

R12 Multi-Org Access Control


R12 Multi-Org Access Control (MOAC)


In previous releases, data was restricted to a single operating unit using views that striped base table data based on the current operating unit setting.

In release 12 a more flexible architecture has been put in place to support Multi-Org Access Control (MOAC). This architecture allows users to define security profiles so that users may access data for more than one operating unit within a single responsibility.

To accomplish this
• Multi-org views have been removed, and replaced with synonyms. For example, MY_TABLE
would no longer be a view defined on MY_TABLE_ALL, but rather a synonym which points
to MY_TABLE_ALL
• The data restriction is accomplished by assigning a virtual private database (VPD) policy to
the synonym. This policy allows the system to dynamically generate restricting conditions
when queries are run against the synonym.

Data relating to defined VPD policies is viewable in the data dictionary view DBA_POLICIES. These policies associate a function with an object, and when the object is accessed, this function can return additional restrictions on the object to restrict the data returned. The particular policy used to
implement Multi-Org in release 12 is:

• Policy_name: ORG_SEC
• Policy_group: SYS_DEFAULT
• Package: MO_GLOBAL
• Function: ORG_SECURITY


MOAC Setup

The setup for MOAC can be found in the HR Foundation Responsibility. Use the Navigation Path
Security => Global Profile. This Profile should be set up to have access to all Operating Units defined
in the E-business Suite. To do this a Security Type of Secure organization by organization hierarchy and/or organization list should be assigned to the Global Profile and each of the valid Operating Units
within your business should be added to the list of Organization Names. E.g.



Additional profiles can be added through the Security => Profile screen. Each profile can be set up to
access one or a number of operating units. A profile must be set up for each combination of operating
units you wish to access through the E-business suite.

We will use a worked example throughout this document to demonstrate the MOAC functionality. For the example, I have set the Global Profile to include 2 operating units (these operating units had org  id’s of 82 and 84 respectively).
Security Profiles can then be assigned to the MO: Security Profile profile at Site, Application,
Responsibility, Organization and User levels. For the purpose of this document I have set it up at user level for my own user.




When I now try to access data through the UI can see data spanning the 2 Operating Units.
Demonstrate this we will look at 2 the Standard AR Receipts screen (Below). We can clearly see that
the screen is showing Receipts from 2 different Operating Units.



Database Multi-Org Access Control

In previous versions of the E-business Suite you may have used standard applications procedures such as
DBMS_APPLICATION_INFO.SET_CLIENT_INFO or FND_CLIENT_INFO.SET_ORG_CONTEXT.
These are superseded in Oracle Release 12 by the procedure MO_GLOBAL.INIT.

The MO_GLOBAL.INIT procedure accepts one parameter, Application Short Name. In order for the procedure to work, the FND_GLOBAL.APPS_INITIALIZE procedure must also be run so that theMO_GLOBAL package can see the Application Profile Options Values defined in the in the E-business Suite.
To use MOAC at database level through an SQL Editing tool such as Toad or SQL Developer, both
these procedures must be run.

Before we do this however, we demonstrate the effect of trying to access data through the new standard synonyms without running the procedures first. We again turn to our previous example, where we displayed 2 AR Receipts.
Firstly we try to access the information from the base table AR_CASH_RECEIPTS_ALL without
setting any session variables. This demonstrates that the data is there and that the 2 Receipts span both Operating Units:





We now try to access the data using AR_CASH_RECEIPTS synonym (remember that this is now a
synonym that points to the base table, but has a VPD Policy assigned to it). We demonstrate here that
the data cannot be seen.



We now setup all the variables needed for MOAC by running the 2 procedures. (In this example I have
already looked up my own User ID = 1299, the Responsibility ID for Receivables = 50538 and the
Receivables Application ID = 222). We then run the same query using AR_CASH_RECEIPTS and the data is now visible for both Operating Units.


Understanding the VPD
We have already seen that we must execute the MO_GLOBAL.INIT procedure to access data via
synonyms with VPD assignments. When we do this the MO_GLOBAL.INIT procedure calls the
function MO_GLOBAL.ORG_SECURITY with the following parameters:
• obj_schema - the object schema, in this case APPS
• obj_name – the object name (e.g., MY_TABLE)
The function then returns additional where clause conditions to restrict the data accessible from the
object. The structure of this function will dynamically generate conditions that will either:

1. Restrict the data to a single operating unit if the access mode is Single
2. Restrict the data to multiple operating units if the access mode is Multiple
3. Restrict the data to eliminate only seed data rows is the access mode is All
4. Restrict the data to not return any rows if the access mode is None

The conditions returned in each case are as follows:
Single OU Access
org_id = sys_context('multi_org2','current_org_id')

Only data for the current operating unit is accessible. The value of
sys_context('multi_org2','current_org_id') would have to be set to the current operating unit.

Multiple OU Access

EXISTS (SELECT 1
          FROM mo_glob_org_access_tmp oa
              WHERE oa.organization_id = org_id)

The user will be able to access data for any org_id which has been populated into
mo_glob_org_access_tmp. When a session is initialized in the applications, values will be populated into
mo_glob_org_access_tmp for each of the operating units the user has access to based on their "MO:
Security Profile" setting.

All OU Access
org_id <> -3113
Seed template records, which are used to create new seed data when a new operating unit is created, are created with an org_id of –3113. So in this mode, only these template records, which do not correspond to any actual operating unit, will be filtered out.

No OU Access

1 = 2
The condition is never satisfied. No data will be returned from the object.

Switching Off MOAC
MOAC can be easily switched off by simply not setting the MO: Security Profile profile at any level.
The MO_GLOBAL.INIT procedure will then simple set the context to for the Single Operating Unit as defined in the profile MO: Operating Unit.

Backwards Compatibility
When running queries on multi-org objects in SQL, you can still use the old CLIENT_INFO settings to gather data and run queries against multi-org objects if the following profile option is set to "Yes":

User Profile Name MO: Set Client_Info for Debugging
Profile Nam: FND_MO_INIT_CI_DEBUG

When this profile option is set to “Yes” and the global access mode setting is null (as it would be in a
SQL*Plus or other client session unless specifically set), the VPD function
MO_GLOBAL.ORG_SECURITY will return the following as the additional where clause condition for the object:

org_id = substrb(userenv('CLIENT_INFO'),1,10)

This will limit the data returned by the object to the current value set in CLIENT_INFO.

Monday, October 13, 2014

Restrict duplicate records in Oracle forms during entering new records




The purpose is to reject two records that contain duplicated values  while entering the records.

 In the below diagram dept no & DName are repeated.




The technique used to solve this problem comes from the Kevin D Clarke’s calculated item famous solution.

It uses two calculated items, one in the data bock and another in a control block.



The first calculated item (:DEPT.MATCH_FOUND) is added to the DEPT block. It contains the formula as follow:

Comparaison(:ctrl.charsave, :dept.deptno||:dept.dname)

Notice in this case,that we want to avoid duplicates on both DEPTNO and DNAME values.

Function COMPARAISON (val1 varchar2, val2 varchar2)
Return number
Is
   answer number := 0;
Begin
   if val1 = val2 then
      answer := 1;
   end if;
   return(answer);
End;

COMPARAISON is a program unit stored in the Forms module.

The two values are compared to each other, then the function returns 1 (a value greatest than 0) if both the values are identical.
The first value (:ctrl.charsave) contains the bakup value of the current record.

The DEPT block must have the following properties setting:

Query all records
YES


The CTRL block must have the following properties setting:

Query all records
YES
Single record
YES
Database data block
NO


The second calculated item (:CTRL.MATCH_FOUND) is added to the CTRL block.
It summarize the values contained in all the rows of the DEPT block (dept.match_found).
If the total is greater than 1, we have two duplicated data.

Thursday, August 21, 2014

Difference between Org_id ,organization_id and operating_unit

For instance let us say you are having Inventory Store.
You have Inventory Stores in two different countries like India and USA
You have installed Oracle Apps single instance and entered all the suppliers information,customers data,tax rules etc.

And your business requirement is like this, all the rules and the data corresponding to indian store shouldnt be applicable/available to american store and vice versa.

So to meet the above requirements you will define two operating units one for india and one for USA.

Now each operating unit will have an ID known as ORG ID.

And we use ORG ID to secure our business data.

Now to do transactions for indian operations you need to define one responsibility and attach indian ORG ID to this responsibility.By this an indian will not see any data/rules applicable to USA.

Similarly you will define another responsibility for USA and attach USA's ORG ID to this responsibility so that an american will not see any data/rules corresponding to India.

This is the concept of ORG ID.

Now let us see what is Organization ID.

Within India you have got various branches in different parts of the country in different states like AP,UP,MP etc.
And the business requirement is like this in AP you will maintain stock of Rice,UP for Wheat and MP for Maize.
To meet this business requirement you will define 3 Inventory Organizations under Indian Operating Unit to maintain the respective stocks of the states..

Each Inventory Organization will have an Organization ID.

This is the outline of the differences between ORG ID and Organization ID.

Navigation: Inventory (Responsibility) >> Setup >> Organizations >> Organizations

Both operating unit and inventory organization are organizations defined in the Oracle EBS (E-Business Suite). They differ in the organization classification which is highlighted in the red box in below images.

The information about all the organizations (including inventory organizations) are available in HR_ORGANIZATION_UNITS table.

The information about all the operating units are available in the HR_OPERATING_UNITStable.

An inventory organization is uniquely identified by ORGANIZATION_ID.


An operating unit is uniquely identified by ORG_ID in different tables for which the value is the ORGANIZATION_ID from HR_OPERATING_UNITS table.


Queries:

SELECT *
  FROM hr_organization_units;

SELECT *
  FROM hr_operating_units;

MOAC (Multiple Organization Access Control) in Oracle E-Business Suite Release 12

With the release of EBS R12, Oracle has released some new features on how data is accessed through the applications and standard programs by the use of Multiple Organization Access Control (MOAC). MOAC allows you to create a security group which can contain many operating units and assign that to the User’s responsibility. All the forms that process OU striped data now allow you to pick an OU to work in from a list that contains all the OU you have access to. You will also find all the OU based reports have a parameter added for OU.

The new feature in R12 enables companies wanting to implement a shared services operating model to efficiently process business transactions by allowing them to access, process and report on data for an unlimited number of operating units within a single applications’ responsibility.

The setup is straight forward. You can define a security profile in the HR Security Profile form, adding Operating Units to it, and then you must run Security List Maintenance program before you can assign the security profile to the profile option MO: Security Profile for a responsibility.

MOAC is initialized when you open a Form, OA page or a Report. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operations units to which access is allowed is fetched and the list of values (LOV) is populated. Then default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”. This is how MOAC works in Oracle Release 12 when the value of “MO: Security Profile” is set.

When the profile “MO: Security Profile” does not have a value MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.

The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set. This enables us to use both R12 MOAC behavior and 11i behavior simultaneously in R 12. You can also choose to completely use one of them.

With MOAC, users can:
§  Perform multiple tasks across Operating Units without changing responsibilities such as invoice entry, order processing, bank payments etc. thus improving the efficiency of transactions for companies that have centralized business functions or operate Shared Service Centers
§  Obtain better information for decision making such as, accessing supplier and customer site levels details across multiple OUs
§  Speed up data entry
§  Reduce setup and maintenance of many responsibilities



How Does the R12 MOAC Defaulting Rules Work?

1.       If the profile option “MO: Security Profile” is not set, then “MO:
Operating Unit” value is used as the default Operating Unit even if “MO:
Default Operating Unit” profile is set to a different value.
2.       If the profile option “MO: Security Profile” is set and gives access to
one Operating Unit, the default Operating Unit will return this value even if
“MO: Default Operating Unit” is set to a different value.
3.       If the profile option “MO: Security Profile” is set and gives access to
multiple Operating Units, then the profile value “MO: Default Operating Unit”
if set is validated against the list of Operating Units in “MO: Security
Profile”. If the Operating Unit is included in the security profile then it
is returned as the default value. Otherwise there is no Operating Unit
default. Moreover, if the Profile Option “MO: Default Operating Unit” is not
set, then there is no default Operating Unit.

What is the impact to you?

With R12 the views owned by “apps” schema are replaced by synonyms to the base tables. These synonyms have security policies attached to them to provide the proper org_ID(s) in the where clause to retrieve the data (the database rewrites the SQL statement to include the conditions set by the security policy). You can read up on virtual private database (VPD) and its features in the Oracle Metalink note mentioned at top of the article. If your report or program uses these synonyms then you will want to choose the proper value for the Operating Unit Mode field. However, if your report or program does use the base tables then this field is not as important.

To understand which applications have multiple organizations access control feature enabled you can query a new table “FND_MO_PRODUCT_INIT). If the product that is enabled has a “Y” for status field then that product can use the MOAC feature.

Pre R12 Multi-Org Features

·         Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
·         A view in the APPS schema provides the Multi-Org filtering based on the following statement in the where clause.   SUBSTRB(USERENV (’CLIENT_INFO’), 1, 10)

R12 Multi-Org Features

·         Base data tables exist in the product schema with a naming convention of %_ALL. The data in this table is striped by ORG_ID (Operating Unit).
·         A synonym in the APPS schema provides the Multi-Org filtering based the Virtual Private Database feature of the Oracle 10G DB Server.
Pre-R12 you could set your SQL session context for multi-org with the following:
BEGIN
dbms_application_info.set_client_info(101);
END;
·         In this example 101 is the ORG_ID for the Operating Unit or you could have used FND_GLOBAL.APPS_INITIALIZE to set your context.
In R12 you can set your SQL session context for a single OU with the following:
BEGIN
execute mo_global.set_policy_context(’S',101);
END;
·         The ‘S’ means Single Org Context
·         101 is the ORG_ID you want set
Also In R12 you can set your SQL session context for multiple OUs with the following:
BEGIN
execute mo_global.set_org_access(NULL,111,‘ONT’);
END;
·         111 is the Security Profile you want to use
·         ‘ONT’ is the application short name associated with the responsibility you will be using to find the security profiles:
The following SQL will dump out the Security Profiles and Operating Unit Names assigned to them:
select psp.SECURITY_PROFILE_NAME, psp.SECURITY_PROFILE_ID, hou.NAME, hou.ORGANIZATION_ID               
from PER_SECURITY_PROFILES psp, PER_SECURITY_ORGANIZATIONS pso, HR_OPERATING_UNITS hou           
where pso.SECURITY_PROFILE_ID = psp.SECURITY_PROFILE_ID  and pso.ORGANIZATION_ID = hou.ORGANIZATION_ID;

How MOAC works technically:
MOAC is initialized when you open a Form, Oracle EBS page or a Report or submit the concurrent program. The first MOAC call checks if the profile “MO: Security Profile” has a value. If Yes, then the list of operating units to which access is allowed is fetched and the list of values (LOV) is populated .This list of values is nothing but list of OUs associated with the Security Profile attached to MO: Security Profile. Security profiles are defined with the help of the HR responsibility. Then, default value of the LOV is set to the operating unit specified in “MO: Default Operating Unit”.
When the profile “MO: Security Profile” does not have a value, MOAC switches to the 11i single organization mode. As in 11i, the profile “MO: Operating Unit” is checked and the operating unit is initialized to the one defined in it.
The important point to note here is that the profile “MO: Operating Unit” is ignored when the profile “MO: Security Profile” is set.
MOAC setups:
Following are the basic steps to be performed in order to enable MOAC feature:
1.     Define Security Profiles (using form function ‘Define Global Security Profile’)
§  Enter a unique name for the security profile.
§  To restrict access by discrete list of organizations, select ‘Secure organizations by organization hierarchy and/or organization list for the Security Type’.
§  Check the Exclude Business Group check box to remove the business group in the list of organizations.
§  Use the Classification field to limit the list of values (LOV) in the Organization Name field. For example, if you select the classification to Operating Unit, only operating units will display in the LOV.
§  In the organization name field, select the Operating Unit for which you want access.
Repeat until you have included all organizations to which you need access.





2.     Run the concurrent program “Security List Maintenance Program” from the standard request submission form. The “Security List Maintenance Program” can be run for a single named security profile to prevent impact to other security profiles.
3.     Assign appropriate security to the profile option “MO: Security Profile” for your users and responsibilities
§  Navigate to the “System Administrator” responsibility > System Profile Options
§  Assign the security profiles to MO: Security Profile for your responsibilities and/or users.
4.     Assign a value for profile option “MO: Default Operating Unit” (Optional)
§  Navigate to System Administrator Responsibility > System Profile Options
§  Assign a default operating unit to “MO: Default Operating Unit” profile option for your responsibilities and/or user.
5.     Assign MO: Operating Unit (Mandatory for only Single Org or if MO: Security Profile is not defined)
§  Navigate to System Administrator Responsibility > System Profile Options
§  Assign the Operating unit to MO: Operating Unit profile option for your responsibility or user.
Note – From the above screen shots we can conclude that user with purchasing responsibility will be able to access data from two Operating Units Vision Operations and Vision Services.
Developer’s Insight:
To increase the flexibility and performance in a multiple organizations environment and provide the same level of data security, the DBMS Virtual Private Database (VPD) feature replaces the CLIENT_INFO function.
The Virtual Private Database (VPD) feature allows developers to enforce security by attaching a security policy to database objects such as tables, views and synonyms. It attaches a predicate function to every SQL statement to the objects by applying security policies. When a user directly or indirectly accesses the secure objects, the database rewrites the user’s SQL statement to include conditions set by security policy that are visible to the user.
MOAC –Changes to Custom Code while upgrading to R12 from 11i-–During R12 upgrade the major task is to enable the MOAC feature to custom code. Following is the recommended approach to achieve MOAC implemented in real aspect to custom code:
1.     Multiple Organizations Views/Tables Changes
Single Organization View 
§  Drop the single organization view
§  Create a synonym with the same name as the obsolete single organization view
§  Attach a policy function to the synonym
Reference Views 
§  Add the ORG_ID column if it does not exist
§  Replace single organization views with _ALL tables for all except one, which must be a secured synonym
§  Include the ORG_ID filter in the where clause of the view to avoid the cartesian product, if the ORG_ID is the driving key or part of the composite key
§  Include the ORG_ID parameter in the columns based on functions, if necessary
2.     Enhancements to Forms
The multiple organizations setup and transaction forms must display the Operating Unit field. This allows users to select the operating unit and enter the setup or transaction for the operating unit. Oracle recommends deriving the operating units from the transaction attributes.
3.      Enhancements to Reports and Concurrent Programs


§  You must remove references of CLIENT_INFO and NVL function to the ‘ORG_ID’ column in the reports.
§  Single Organization Reports—The operating unit mode for single organization reports are flagged as     ’SINGLE’ in the Define Concurrent Programs page.
§  Cross Organization Reports–The Operating Unit mode for cross organization reports are flagged as ‘MULTIPLE’ in the Define Concurrent Programs page.
4.     Enhancements to Public APIs
§  Do not use the multiple organizations temporary table directly in the SQL query.
§  Rewrite the SQL joins with two or more views to use just one secured synonym depending on the driving table for the query and replace the remaining views by _ALL tables.
§  Add the ORG_ID to the WHERE clause of the SQL to avoid cartesian joins for tables that include ORG_ID the composite or driving key.
§  Use MO_GLOBAL.Set_Policy_Context.
This API has 2 parameters –1. Operating unit 2. Context
Context has 2 values 1. M  2. S
When policy context is set to ‘M’, data from all accessible Operating Units will be returned.
When policy context is set to ‘S’, then only data from the specified Org_Id will be returned.
§  Products must call the MO_GLOBAL.init() API to execute the multiple organizations initialization.
5.     Enhancements to Workflows

With multiple organizations access control, you must set the current organization ID and not the CLIENT_INFO org context. You must derive the current organization ID from item keys. Do not rely on MO: Security Profile, MO: Default Operating Unit, and MO: Operating Unit profile options when setting the organization context because the operating unit must be validated before initiating the workflow.



Wednesday, July 2, 2014

Oracle Forms trigger

Oracle Forms trigger execution order

Form Trigger priority item, to block, to the form level, If another to set Execution HIERARCH property (override, before, after), in accordance with the order set. If the override is executed to finish this level triggers do not perform up before is executed after this trigger level, if the upper level of this flip-flop also continue to perform, is to go after perform on a level trigger, then come back and perform. Feeling trigger more difficult to understand or each of the trigger to fire, and in the development of how to use. Current understanding of the flip-flop is pre-..., when-new-...-instance, when-...-validate, post-query and several on-...

Action Trigger Firing Order Level
------ -------------------- -----
Runform 1. Pre-Logon Form
2. On-Logon Form
3. Post-Logon Form
4. Pre-Form Form
5. When-Create-Record Block
6. Pre-Block Block
7. Pre-Record Block
8. Pre-Text-Item Item
9. When-New-Form-Instance Form
10. When-New-Block-Instance Block
11. When-New-Record-Instance Block
12. When-New-Item-Instance Item

***********
Enter Query 1. Post-Text-Item Item
2. Post-Record Block
3. When-Clear-Block Block
4. When-New-Record-Instance Block
5. When-New-Item-Instance Item

Note: If you define the Key-ENTQRY trigger, this is the only trigger
which fires in an Enter Query situation.


*****************
Execute Query
After Enter Query 1. Pre-Query Block
2. Pre-Select Block
3. On-Select Block
4. When-Clear-Block Block
5. Post-Select Block
6. On-Fetch Block
7. On-Close Form
8. When-Clear-Block Block

Note: If you define the Key-EXEQRY trigger, this is the only trigger
which fires in an Execute Query situation.


*************
Execute Query
Without Enter
Query 1. Post-Text-Item Block
2. Pre-Query Block
3. Pre-Select Block
4. On-Select Block
5. Post-Select Block
6. On-Fetch Block
7. On-Close Form
8. When_Create_Record Block
9. Pre-Record Block
10. Pre-Text-Item Item
11. When-New-Record-Instance Block
12. When-New-Item-Instance Item


****
Exit 1. Post-Text-Item Item
2. Post-Record Block
3. Post-Block Block
4. Post-Form Form
5. On-Rollback Form
6. Pre-Logout Form
7. On-Logout Form
8. Post-Logout Form


**************
Next Field and
Previous field 1. When-New-Item-Instance Item


***************
Next Record and
Previous Record 1. When-New-Record-Instance Block
2. When-New-Item-Instance Item

**************
Next Block and
Previous Block 1. Post-Text-Item Item
2. Post-Record Block
3. Post-Block Block
4. When-Create-Record Block
5. Pre-Block Block
6. Pre-Record Block
7. Pre-Text-Item Block
8. When-New-Block-Instance Block
9. When-New-Record-Instance Block
10. When-New-Item-Instance Form


*******************
Records Are Queried 1. Post-Query Block
2. Post-Change Block
3. Post-Change Item
4. Post-Change Block
5. Post-Change Block
Go back to Post-Query

NOTE: This cycle is repeated for each record retrieved.


**********************
No Records Are Queried 1. When-New-Record-Instance Block
2. When-New-Item-Instance Item

NOTE: To observe this Trigger Firing Order:
a. Enter a query.
b. Enter a nonexistent record.
c. Execute the query.

The two triggers listed above, the Enter Query triggers, and
the Execute Query triggers fire.


*************
Create Record 1. Post-Change Block
2. When-Validate-Item Block
3. Post-Text-Item Block
4. When-Validate-Record Block
5. Post-Record Block
6. Post-Block Block
7. On-Savepoint Form
8. Pre-Commit Form
9. Pre-Insert Block
10. On-Insert Form
11. Post-Insert Block
12. Post-Forms-Commit Form
13. On-Commit Form
14. Post-Database-Commit Form
15. Pre-Block Block
16. Pre-Record Block
17. Pre-Text-Item Block
18. When-New-Item-Instance Form


*************
Update Record 1. When-Database-Record Block
2. Post-Change Block
3. When-Validate-Item Block
4. Post-Text-Item Block
5. When-Validate-Record Block
6. Post-Record Block
7. Post-Block Block
8. On-Savepoint Form
9. Pre-Commit Form
10. Pre-Update Block
11. On-Update Block
12. Post-Update Block
13. Post-Forms-Commit Form
14. On-Commit Form

Here the transaction is complete and one record added.

15. Post-Database-Commit Form
16. Pre-Text-Item Block
17. When-New-Item-Instance Form

NOTE: To observe this Trigger Firing Order:

a. Execute a query.
b. Change a value.
c. Choose Action->Save from the menu.
d. Record the triggers from that point.


*************
Delete Record 1. On-Lock Block
2. When-Remove-Record Block
3. Post-Change Block
4. Post-Change Block
5. Post-Change Block
6. Post-Change Block
7. Post-Change Block
8. Post-Change Block
9. Post-Change Item
10. Post-Query Block
11. Post-Text-Item Block
12. Post-Record Block
13. Pre-Record Block
14. Pre-Text-Item Block
15. When-New-Record-Instance Block
16. When-New-Item-Instance Form

------------------More detailed explanation --------------------------

Trigger execution order:

1. When you open the FORM:
(1) Pre-form
(2) PRE-block (BLOCK-level)
(3) WHEN-NEW-FORM-INSTANCE
(4) WHEN-NEW-BLOCK-INSTANCE
(5) WHEN-NEW-RECORD-INSTANCE
(6) WHEN-NEW-ITEM-INSTANCE
2. When the the fill line recording is completed, the cursor moves to the next record when:
(1) WHEN-VALIDATE-RECORD
(Only fill out the records in the database record for verification only, if only the data on the page repetitive and not in the database and its duplicate values ??will not complain.)
(2) WHEN-NEW-RECORD-INSTANCE
(3) WHEN-NEW-ITEM-INSTANCE
3. When click "Save"
(1) WHEN-VALIDATE-RECORD
(All data on the page is submitted to the database, the page duplicate data, shall be submitted to the first successful but only the data written to the database in a similar place of temporary table, in the submission of the second duplicate records time error, perform the transaction is rolled back, the original successful implementation of the directive will be undone)
(2) PRE-INSERT
(3) ON-INSERT
(4) POST-INSERT
(5) POST-FORMS-COMMIT
(6) PRE-block (BLOCK-level)
(7) KEY-COMMIT
(8) WHEN-NEW-ITEM-INSTANCE
4. When the cursor moves to the already displayed in the current data block on the line:
(1) WHEN-REMOVE-RECORD
(2) WHEN-NEW-RECORD-INSTANCE
(3) WHEN-NEW-ITEM-INSTANCE
When the movement of the line ITEM:
(4) WHEN-NEW-ITEM-INSTANCE
5. When to be modified (modified) on an item in the record:
(1) ON-LOCK
6. Save the modification is complete:
(1) WHEN-VALIDATE-RECORD
(2) PRE-UPDATE
(3) ON-UPDATE
(4) POST-FORMS-COMMIT
(5) PRE-block (BLOCK-level)
(6) KEY-COMMIT
(7) WHEN-NEW-ITEM-INSTANCE
7. Delete a record:
(1) ON-LOCK
(2) WHEN-REMOVE-RECORD
(3) Key-DELREC
(4) WHEN-NEW-RECORD-INSTANCE
(5) WHEN-NEW-ITEM-INSTANCE
8. Process of F11 inquiries:
(1) WHEN-CLEAR-BLOCK
(2) WHEN-NEW-RECORD-INSTANCE
(3) WHEN-NEW-ITEM-INSTANCE
Point in the input query conditions CTRL + F11:
(4) Pre-QUERY
(5) WHEN-CLEAR-BLOCK
(6) POST-QUERY
(7) WHEN-NEW-RECORD-INSTANCE
(8) WHEN-NEW-ITEM-INSTANCE
9. CRRL + F11:
(1) WHEN-CLEAR-BLOCK
(2) PRE-QUERY
(3) WHEN-CLEAR-BLOCK
(4) POST-QUERY (each check a record, trigger time)
(5) WHEN-NEW-RECORD-INSTANCE
(6) WHEN-NEW-ITEM-INSTANCE
10. From the query status (F11) to the input state (F4):
(1) WHEN-CLEAR-BLOCK
(2) Key-EXIT
(3) WHEN-NEW-RECORD-INSTANCE
(4) WHEN-NEW-ITEM-INSTANCE
11. Process flashlight query:
(1) QUERY_FIND (block-level)
The query is entered, click "Search" button:
(2) WHEN-CLEAR-BLOCK
(3) PRE-QUERY
(4) WHEN-CLEAR-BLOCK
(5) POST-QUERY
(6) WHEN-NEW-RECORD-INSTANCE
(7) WHEN-NEW-ITEM-INSTANCE
12. Click the "New" when:
(1) WHEN-NEW-RECORD-INSTANCE
(2) WHEN-NEW-ITEM-INSTANCE
13. Time click "EditField":
(1) KEY-EDIT
14. Click the WindowHelp:
(1) Key-HELP
15. Time click "ClearRecord":
(1) WHEN-REMOVE-RECORD
(2) POST-QUERY
(3) WHEN-NEW-RECORD-INSTANCE
(4) WHEN-NEW-ITEM-INSTANCE
16. Click F4 off:
(1) Key-EXIT
(2) POST-form
17. Click "closeForm" button is turned off:
(1) Key-EXIT
(2) POST-form
18. Click on the "Translations" button:
(1) Translations
19. Click on the small cross off:
(1) WHEN-WINDOW-CLOSED
(2) CLOSE-WINDOW
(3) Key-EXIT
(4) POST-form
20. Select the LOV list:
(1) Key-LISTVAL
(2) WHEN-NEW-ITEM-INSTANCE
21. Select the record in front of the strip:
(1) WHEN-NEW-RECORD-INSTANCE
(2) WHEN-NEW-ITEM-INSTANCE (data item level)
(3) WHEN-NEW-ITEM-INSTANCE
22. Cursor moves up and down:
(1) WHEN-NEW-RECORD-INSTANCE
(2) WHEN-NEW-ITEM-INSTANCE
The Tag label:
Triggers in Oracle Forms
Triggers are blocks of PL / SQL code that are written to perform tasks when a specific event occurs within an application. In effect, an Oracle Forms trigger is an event-handler written in PL / SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL / SQL statements. A trigger encapsulates PL / SQL code so that it can be associated with an event and executed and maintained as a distinct object.
Block Processing Triggers:
Block processing triggers fire in response to events related to record management in a block.
·         When-Create-Record Perform an action whenever Oracle Forms attempts to create a new record in a block.
·         When-Clear-Block Perform an action whenever Oracle Forms flushes the current block; that is, removes all records from the block.
·         When-Database-Record Perform an action whenever Oracle Forms changes a record's status to Insert or Update, thus indicating that the record should be processed by the next COMMIT_FORM operation.
Interface Event Triggers:
Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control.
·         When-Button-Pressed Initiate an action when an operator selects a button, either with the mouse or through keyboard selection.
·         When-Checkbox-Changed Initiate an action when the operator toggles the state of a check box, either with the mouse or through keyboard selection.
·         When-Image-Activated Initiate an action whenever the operator double-clicks an image item.
·         When-Image-Pressed Initiate an action whenever an operator clicks on an image item.
·         When-Radio-Changed Initiate an action when an operator changes the current radio button selected in a radio group item.
·         When-Window-Activated Initiate an action whenever an operator or the application activates a window.
·         When-Window-Closed Initiate an action whenever an operator closes a window with the window manager's Close command.
·         When-Window-Deactivated Initiate an action whenever a window is deactivated as a result of another window becoming the active window.
Master / Detail Triggers:
Oracle Forms generates master / detail triggers automatically when a master / detail relation is defined between blocks. The default master / detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless developing custom block-coordination schemes, you do not need to define these triggers.
·         On-Check-Delete-Master Fires when Oracle Forms attempts to delete a record in a block that is a master block in a master / detail relation.
·         On-Clear-Details Fires when Oracle Forms needs to clear records in a block that is a detail block in a master / detail relation because those records no longer correspond to the current record in the master block.
·         On-Populate-Details Fires when Oracle Forms needs to fetch records into a block that is the detail block in a master / detail relation so that detail records are synchronized with the current record in the master block.
Message-Handling Triggers:
Oracle Forms automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events.
·         On-Error Replace a default error message with a custom error message, or to trap and recover from an error.
·         On-Message To trap and respond to a message; for example, to replace a default message issued by Oracle Forms with a custom message.
Validation Triggers:
Validation triggers fire when Oracle Forms validates data in an item or record. Oracle Forms performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation.
·         When-Validate-Item
·         When-Validate-Record
Navigational Triggers:
Navigational triggers fire in response to navigational events. Navigational triggers can be further sub-divided into two categories: Pre-and Post-triggers, and When-New-Instance triggers. Pre-and Post-Triggers fire as Oracle Forms navigates internally through different levels of the object hierarchy. When-New-Instance-Triggers fire at the end of a navigational sequence that places the input focus on a different item.
·         Pre-Form Perform an action just before Oracle Forms navigates to the form from "outside" the form, such as at form startup.
·         Pre-Block Perform an action before Oracle Forms navigates to the block level from the form level.
·         Pre-Record Perform an action before Oracle Forms navigates to the record level from the block level.
·         Pre-Text-Item Perform an action before Oracle Forms navigates to a text item from the record level.
·         Post-Text-Item Manipulate an item when Oracle Forms leaves a text item and navigates to the record level.
·         Post-Record Manipulate a record when Oracle Forms leaves a record and navigates to the block level.
·         Post-Block Manipulate the current record when Oracle Forms leaves a block and navigates to the form level.
·         Post-Form Perform an action before Oracle Forms navigates to "outside" the form, such as when exiting the form.
·         When-New-Form-Instance Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).
·         When-New-Block-Instance Perform an action immediately after the input focus moves to an item in a block other than the block that previously had input focus.
·         When-New-Record-Instance Perform an action immediately after the input focus moves to an item in a different record.
·         When-New-Item-Instance Perform an action immediately after the input focus moves to a different item.
Transactional Triggers:
Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source.
·         On-Delete
·         On-Insert
·         On-Update
·         On-Logon
·         On-Logout
·         Post-Database-Commit
·         Post-Delete
·         Post-Insert
·         Post-Update
·         Pre-Commit
·         Pre-Delete
·         Pre-Insert
·         Pre-Update
Query-Time Triggers:
Query-time triggers fire just before and just after the operator or the application executes a query in a block.
·         Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
·         Post-Query Perform an action after fetching a record, such as looking up values ??in other tables based on a value in the current record. Fires once for each record fetched into the block.

Open, close, create, update records trigger order
PRE-......
Into a form, or navigate to a new block trigger PRE-...... series of flip-flops are generally in the WHEN-NEW-......-instance before the series, if they fail, then it can not successfully navigate to the next an object, and can only stay in the current location. These triggers can be set to determine conditions to restrict whether you can navigate to the new location.
WHEN-NEW-......-instance
This type of trigger is when the mouse cursor each time fell to a new block, record, item on the trigger, and even if it fails, it will not happen error. But when-new-form-instance, only when the form starts, navigate the cursor to the first navigation block a navigation item triggered if an application has more than one Form, when the cursor conversion between the various form, does not trigger it.

POST-TEXT-ITEM and WHEN-VALIDATE-ITEM
Post-text-item trigger point: When the input cursor from a Text-Item to other ITEM can use it to change the value of the item, when-Validate-item before leaving the trigger, but in a different role, Post -Text-Item itself is not verified, when-Validate-item can be used to add some authentication (in addition to verification form itself), but when the form validation is successful, the item flag will be 'valid', and will not go verified, when we go to modify its value, then there is likely to put an invalid value imported into the . Create and update a record, the first trigger When-Validate-Item trigger Post-Text-Item, then is the when-Validate-Record.
POST-...... series are to leave the current block, record, item triggered
WHEN-WINDOW-ACTIVATED
Do modify the attributes of raw materials platform, the first time you use this trigger to return from another window, refresh the current window, the code written on the flip-flop. Before use fnd_message.debug test, open the window pop Message. As long as the window during the event will not stop the implementation of the code of the trigger from the inside, so the implementation of the code should be used if conditions limit.
Form Trigger priority item, to block, to the form level, If another to set Execution HIERARCH property (override, before, after), in accordance with the order set. If the override is executed to finish this level triggers do not perform up before is executed after this trigger level, if the upper level of this flip-flop also continue to perform, is to go after perform on a level trigger, then come back and perform. Feeling trigger more difficult to understand or each of the trigger to fire, and in the development of how to use. Current understanding of the flip-flop is pre-..., when-new-...-instance, when-...-validate, post-query and several on-...
Verification code written in the WHEN-VALIDATE-RECORD PRE-INSERT different validation code written in the WHEN-VALIDATE-RECORD and PRE-insert different
1, written in the WHEN-VALIDATE-RECORD, error, If the validation associated and the back-end database, then write in PRE-INSERT, because PRE-INSERT, ON-INSERT one by one records This batch entry, validation of each record can be taken to the latest database records.
Batch entry, a PRE-INSERT unsuccessful, all the records are not saved successfully
Post-Query and when-New-Record-Instance relations
Post-Query and When-New-Record-Instance
Assumes that the database 100 block display the number of rows is set to 10, then when the cursor to move between the records you display only trigger When-New-Record-Instance, does not trigger the Post-query. When the cursor to the record of Article 11, triggering the Post-query

Not visible item when-validate-item question: If an item is not visible, then change its value, but also trigger when-validate-item?
Results: trigger, but not change its value after the trigger, but when the cursor is moved to another block. Close the window when triggered.
Test: Let block1 item1 is not visible in item2 when-validate-item the Tem1 assignment runtime, change the value of item2, in the block1 the item to move cursor did not trigger item1 WHEN-validate-item until the cursor to leave the block, or close the window when triggered.
The ACCEPT Trigger explain
ACCEPT APP_STANDARD.EVENT ('Accept'); processed by the trigger menu or toolbar to call the Save and Proceed (save and continue) action. It performs the save and move to the block designated as the first navigation block. Replace the code in this trigger, or create block-level trigger and the implementation type is specified as 'Override'
FOLDER_RETURN_ACTION
FOLDER_RETURN_ACTION
This trigger allows you to specify the customer Hua folder events
Replace the code required processing file folder action

KEY_DUPREC
APP_STANDARD.EVENT ('KEY-DUPREC');
This trigger is disabled Oracle Forms default duplicate records
KEY-CLRFRM
KEY-CLRFRM
APP_STANDARD.EVENT ('KEY-CLRFRM');
Verify that the record before this trigger in an attempt to empty the form
Usually you should be added GO_BLOCK If there are a plurality of regions, in the form the use GO_BLOCK call emptied from operation repopulate control menu in the original code to add additional code
KEY_MENU
KEY_MENU
APP_STANDARD.EVENT ('KEY-MENU');
This flip-flop is disabled Oracle froms Block Menu command
To enable alternative block from a specific block through the keyboard, writing block-level KEY_MENU and executive type set to 'Override' this trigger will open a pop-up menu LOV

KEY_LISTVAL
KEY_LISTVAL
APP_STANDARD.EVENT ('KEY-LISTVAL');
This trigger execution the flexfield operation or reference LOV
Create a block or item level trigger and set the execution type 'Override', you can use the calendar or dynamic execution flexfield
ON-ERROR
ON-ERROR
APP_STANDARD.EVENT ('ON-ERROR');
This trigger or client error message dictionary calls.
In order to capture the handle specified error in calling APP_STANDARD before check specified error declare
original_mess varchar2 (80);
begin
IF MESSAGE_CODE = <your message number> THEN
original_mess: = MESSAGE_TYPE | | '-' | |
to_char (MESSAGE_CODE) | | ':' | | MESSAGE_TEXT;
--- Your code handling the error goes here
message (original_mess);
ELSE
APP_STANDARD.EVENT ('ON_ERROR');
END IF
end;

POST-FORM
POST-FORM.
APP_STANDARD.EVENT ('POST-FORM');
This flip-flop is reserved for future use to add additional code after the original code.

PRE-FORM
PRE-FORM
FND_STANDARD.FORM_INFO ('$ Revision: <Number> $',
'<Form. Name>'
'<Application Shortname>',
'$ Date: <YY/MM/DD HH24:MI:SS> $',
'$ Author: <developer name> $');
APP_STANDARD.EVENT ('PRE-FORM');
APP_WINDOW.SET_WINDOW_POSITION ('BLOCKNAME',
'FIRST_WINDOW');
The internal value of this trigger to initialize the Oracle Application menu. The value entered here will be seen in the Oracle applications menu 'Help About Oracle Applications'.
You must edit the short name of the application, the application referred to control which application online help documentation will be called when the user select the 'help' button. If you will be application referred to is set to FND, your users will not see any help because Oracle applications will not be available to help target.
Form name is the user form (form title).
Oracle Corporation to use the source control system, which can automatically update the value of the "$" at the beginning, if you do not use the source control system, you can press your development information editing these values.
You must edit APP_WINDOW BLOCKNAME for your own the block. Do not edit FIRST_WINDOW
QUERY_FIND
QUERY_FIND
APP_STANDARD.EVENT ('QUERY_FIND);
This trigger will set the display string 'Query Find is not available'
Alternative code, in this triggers created when you create window or Row_LOV in your form block-level trigger and execute type set to 'Override'
WHEN-NEW-FORM-INSTANCE
WHEN-NEW-FORM-INSTANCE
FDRCSID ('$ Header: ... $');
APP_STANDARD.EVENT ('WHEN-NEW-FORM-INSTANCE');
- App_folder.define_folder_block ('template test',
'Folder_block', 'prompt_block', 'stacked_canvas',
'Window', 'disabled functions');
- App_folder event ('verify');
The call APP_STANDARD.EVENT to query-only mode to call FND_FUNCTION.EXECUTE call FNDRCSID to Oracle application source control system (source control the system). APP_FOLDER internal calls only for Oracle applications. The customized form does not require the FDRCSID or APP_FOLDER call, but if they are left in the trigger has no effect.
Add additional code before the existing code.
WHEN-NEW-RECORD-INSTANCE
WHEN-NEW-RECORD-INSTANCE
APP_STANDARD.EVENT ('WHEN-NEW-RECORD-INSTANCE');
Trigger Manager Oracle Applications menu and toolbar to create a block-level trigger and set the execution type for 'Before'
WHEN-NEW-ITEM-INSTANCE
WHEN-NEW-ITEM-INSTANCE
APP_STANDARD.EVENT ('WHEN-NEW-ITEM-INSTANCE');
Trigger Manager Oracle application menus and toolbars if you add flexfield method calls, you should add it before APP_STANDARD.EVENT call call, usually, you should not add any code in the trigger, this code will speed will affect your form and affect each
Can not be modified Trigger Oracle EBS Form.
Oracle applications do not support modifying the form level triggers
CLOSE_THIS_WINDOW
From menu Action-> Close call trigger APP_CUSTOM.CLOSE_WINDOW.
CLOSE_WINDOW
APP_CUSTOM.CLOSE_WINDOW (: SYSTEM.EVENT_WINDOW);
The departure of its handling of the Close event. The write event processing Close Window must be written in the APP_CUSTOM.CLOSE_WINDOW package
EXPORT
App_standard.event ('export');
This flip-flop is "Action, Export" menu choice.
FOLDER_ACTION
App_folder.event (: global.folder_action);
KEY-COMMIT
App_standard.event ('Key-commit');
This trigger handle the normal submission or form call
KEY-EDIT
App_standard.event ('KEY-EDIT');
This flip-flop Flexfield or calendar editor operation.
KEY-EXIT
App_standard.event ('KEY-EXIT');
Processed by the trigger off events and to exit the enter-query state
KEY-HELP
App_standard.event ('KEY-HELP');
This trigger calls windows help system.
LASTRECORD
APP_STANDARD.EVENT ('LASTRECORD');
Processed by the trigger menu events (Go -> LAST RECORD)
MENU_TO_APPCORE
App_standard.event (: global.menu_to_appcore);
This triggers support Special menu
STANDARD_ATTACHMENTS
Atchmt_api.invoke;
This trigger processing accessories menu or toolbar button calls
WHEN-WINDOW-CLOSE
Execute_trigger ('CLOSE_WINDOW');
The this trigger centralized form's Close event
WHEN-FORM-NAVIGATE
You can not modify the trigger, which contains the standard behavior when minimized form navigate to the normalization of this form.
ZOOM
Appcore_custom.event ('Zoom');
Processed by the trigger menu or toolbar button 'Action, Zoom' event,.