Oracle SQL Learnings

• NLS settings
  
  NLS settings supports the globalization of the db instance. It has variety of parameters like NLS_LANGUAGE, NLS_TERRITORY etc. The settings can be done at three level database, instance and session level. Session level takes precedance over the other two. The NLS settings are stored in
  NLS_DATABASE_PARAMETERS,
  NLS_INSTANCE_PARAMETERS,
  NLS_SESSION_PARAMETERS.
  
  The session can be set as
  ALTER SESSION SET parameter_name = 'parameter_value'
  Example:
  ALTER SESSION SET NLS_TERRITORU = 'AMERICA'.
  
  NLS settings governs lots of db settings like date, time format, calendar, start of week, etc

• LOCK TABLE
  
  An update of any table can be prevented by putting lock on it using below command.
  
  LOCK TABLE hr.per_medical_assessments IN EXCLUSIVE MODE;
  
  The lock is removed by either a coomit or a rollback

• CALUCLATING AGE
  
  Age can be calculated as
  FLOOR ( MONTHS_BETWEEN(trunc(sysdate) , ppx.DATE_OF_BIRTH)/12) age
  

Oracle Apps Logging techniques

• Set "FND: Debug Log Level" Profile for your user and view log messages using
  
  SELECT *
  
  FROM fnd_log_messages
  
  WHERE user_id = 1231 /*your FND_USER user_id here*/
  
  AND TIMESTAMP > SYSDATE - .3
  
  ORDER BY log_sequence DESC

• HRMS provide PYUPIP utility to gather trace in APIs. But we need to be careful while using it as if trace is not collected, it hangs up there. The way to turn on and off trace are

hr_utility.trace_on(null, '');

api_call;

hr_utility.trace_off;

The trace can be collected in a unix session in "log_file" as

$PAY_TOP/bin/PYUPIP apps/apps "" > log_file

OTL Timecard Data Model

The major tables used by timecard in OTL are

hxc_time_building_blocks
hxc_time_attribute_usages
hxc_time_attributes

HXC_TIME_BUILDING_BLOCKS
This table stores the actual time data (start_time, stop_time, hours) for the timecard. The table has a hierarchial way of storing the data.
The column scope determines the level of data stored. Following are the important scope values in terms of their order in hierarchy (top to bottom)

1. TIMECARD
2. DAY
3. DETAIL

The scope TIMECARD is highest level which stores information about the header details of timecard like total hours worked, comments, start date of timecard (start_time column), end date of timecard (end_time column).

The scope DAY is child of TIMECARD which header information for the particular DAY of timecard. Please note that this scope entry just stores information about a particular DAY and not the start_time and end_time of employee that DAY. One of the reason can be that for a particular person the end_time may go to next day in case of working through midnight.

The scope DETAIL stores the actual start_time, end_time or hours of employee on a day. The column TYPE denotes whether start_time/end_time are stored or just hours. In DETAIL, time can be stored either as start_time/end_time (against TYPE = 'RANGE') or hours worked (against TYPE = 'HOURS').

The hierarchy in time_building_blocks is created using the column parent_building_block_id. So scope = 'DAY' row will have praent_building_block_id as time_building_block_id of corresponding row SCOPE = 'TIMECARD' and scope = 'DETAIL' will have parent_building_block_id as time_building_block_id of scope = 'DAY'.

In hxc_time_building_blocks, an update to timecard doesnt update the current row but it creates another row with same time_building_block_id but increases the object_version_number by 1.

Example: If there is row for TIMECARD scope saying start_time 25-Oct-09 and end_time of 01-Nov-09, hxc_time_building_id =1 and object_version_number = 1. If we update the comments for this timecard, it will create another row with new comments (in comments column), start_time 25-Oct-09 and end_time of 01-Nov-09, hxc_time_building_id =1 and object_version_number = 2. It also updates the date_from and date_to column of hxc_time_building_blocks to identify the row which is active currently. This type of data model has been created to keep history of changes made (say for audit purposes).

Below is an example query of how data can be retrived from hxc_time_building_blocks for a WEEKLY timecard

SELECT
MAX(DECODE(TRIM(TO_CHAR(TRUNC(b1.start_time),'DAY')),'SUNDAY',TO_CHAR(b2.start_time,'HH24:MI'))) sun_start,

MAX(DECODE(TRIM(TO_CHAR(TRUNC(b1.start_time),'DAY')),'SUNDAY',TO_CHAR(b2.stop_time,'HH24:MI'))) sun_stop
..
..
..

MAX(DECODE(TRIM(TO_CHAR(TRUNC(b1.start_time),'DAY')),'SATURDAY',TO_CHAR(b2.start_time,'HH24:MI'))) sat_start,

MAX(DECODE(TRIM(TO_CHAR(TRUNC(b1.start_time),'DAY')),'SATURDAY',TO_CHAR(b2.stop_time,'HH24:MI'))) sat_stop

FROM
hxc_time_building_blocks b1,
hxc_time_building_blocks b2

WHERE b1.time_building_block_id = b2.parent_building_block_id
AND b1.scope = 'DAY'
AND b2.scope = 'DETAIL'
AND b1.parent_building_block_id = p_time_building_block_id

-- p_time_building_block_id => scope = 'TIMECARD'
AND SYSDATE BETWEEN b1.date_from AND b1.date_to
AND SYSDATE BETWEEN b2.date_from AND b2.date_to
AND b2.parent_building_block_ovn = b1.object_version_number
AND b1.object_version_number = (SELECT MAX(object_version_number)
FROM hxc_time_building_blocks
WHERE time_building_block_id = b1.time_building_block_id)
AND b2.object_version_number = (SELECT MAX(object_version_number)
FROM hxc_time_building_blocks
WHERE time_building_block_id = b2.time_building_block_id)
GROUP BY b1.parent_building_block_id

HXC_TIME_ATTRIBUTE_USAGES

The table is a link between hxc_time_building_blocks and hxc_time_attribute_usages.

HXC_TIME_ATTRIBUTES

This table holds the information about the time attributes against an attribute_category. Please note that an update to an existing attribute rows results in new row with new time_attribute_id and hxc_time_attribute_usages gets updated to point to this new row. Below qeury retrieves data for a particular attributes for a time_building_block_id

SELECT hta1.* FROM
hxc_time_attribute_usages htau,
hxc_time_attributes hta1
WHERE htau.time_attribute_id = hta1.time_attribute_id
AND hta1.attribute_category = 'ATTR_CAT'
AND htau.time_building_block_id = p_time_building_block_id
AND htau.time_building_block_ovn = p_time_building_block_n
AND NVL(hta1.object_version_number,-1) = (SELECT NVL(MAX(object_version_number),-1)
FROM hxc_time_attributes
WHERE time_attribute_id = hta1.time_attribute_id)
AND NVL(hta1.time_attribute_id,-1) = (SELECT NVL(MAX(ta.time_attribute_id),-1)
FROM hxc_time_attributes ta,hxc_time_attribute_usages tau
WHERE ta.time_attribute_id = tau.time_attribute_id
AND ta.attribute_category = 'ATTR_CAT'
AND htau.time_building_block_id = tau.time_building_block_id
AND htau.time_building_block_ovn = tau.time_building_block_ovn
);

Oracle Apps Table Registeration Script

DECLARE
l_schema VARCHAR2(30) := 'XX';
l_tab_name VARCHAR2(30) := 'XX_CUSTOM_TABLE';
CURSOR c_get_tab_info IS
SELECT next_extent,
pct_free,
pct_used
FROM all_tables
WHERE owner = l_schema
AND table_name = l_tab_name;
CURSOR c_get_col_info IS
SELECT column_name,
column_id,
data_type,
data_length,
nullable,
--, data_precision,
data_scale
FROM all_tab_columns
WHERE owner = l_schema
AND table_name = l_tab_name
ORDER BY column_id;

CURSOR c_get_pk_info IS
SELECT constraint_name,
DECODE(status, 'ENABLED', 'Y', 'N') enabled_flag
FROM all_constraints
WHERE owner = l_schema
AND table_name = l_tab_name
AND constraint_type = 'P';

CURSOR c_get_pk_col_info(cp_pk_name VARCHAR2) IS
SELECT column_name,
position
FROM all_cons_columns
WHERE owner = l_schema
AND table_name = l_tab_name
AND constraint_name = cp_pk_name
ORDER BY position;

BEGIN

FOR tab_rec IN c_get_tab_info LOOP

ad_dd.register_table(p_appl_short_name => l_schema
,p_tab_name => l_tab_name
,p_tab_type => 'T'
,p_next_extent => tab_rec.next_extent
,p_pct_free => tab_rec.pct_free
,p_pct_used => tab_rec.pct_used);

END LOOP;

FOR col_rec IN c_get_col_info
LOOP

ad_dd.register_column(p_appl_short_name => l_schema
,p_tab_name => l_tab_name
,p_col_name => col_rec.column_name
,p_col_seq => col_rec.column_id
,p_col_type => col_rec.data_type
,p_col_width => col_rec.data_length
,p_nullable => col_rec.nullable
,p_translate => 'N'
,p_precision => col_rec.data_precision
,p_scale => col_rec.data_scale);

END LOOP;

FOR pk_rec IN c_get_pk_info
LOOP
ad_dd.register_primary_key(p_appl_short_name => l_schema,
p_key_name => pk_rec.constraint_name,
p_tab_name => l_tab_name,
p_description => NULL,
p_key_type => 'S',
p_audit_flag => 'Y',
p_enabled_flag => pk_rec.enabled_flag);


FOR pk_col_rec IN c_get_pk_col_info(pk_rec.constraint_name)
LOOP
ad_dd.register_primary_key_column
(p_appl_short_name => l_schema,
p_key_name => pk_rec.constraint_name,
p_tab_name => l_tab_name,
p_col_name => pk_col_rec.column_name,
p_col_sequence => pk_col_rec.position);
END LOOP;

END LOOP;

END;
/
COMMIT;

My HRMS Learnings

• The p_validate parameters in Oracle HRMS API is used in case we want to validate the data. If set to TRUE, it validates the parameters passed and returns validation errors if any but doesnt commit the record into database.
• Various date track modes in Oracle API are: UPDATE --> To Update a record at particular date, CORRECTION --> To correct a record at a particular date, UPDATE_CHANGE_INSERT (Insert) - The changes that the user makes remain in effect until the effective end date of the current record, UPDATE_OVERRIDE (Replace) - The user's changes take effect from now until the end date of the last record in the future. All future dated changes are deleted.
• You can rollback termination using the hrempter.cancel_termination API but it is not a supported public API.

OTL Time Attribute Update API issue

OTL time attribute API fails in few cases due to the timecard attribute record table passed as IN OUT parameter doesnt contain all the attributes entry.

So in case there is no entry for a time attribute in record table, we need to use create_attribute else use update attribute. Following is the piece of code which does so

lt_attribute_tbl(1).att_name :=
lt_attribute_tbl(1).att_value :=
....
....
....
....
lt_attribute_tbl(10).att_name := ...
lt_attribute_tbl(10).att_value := ...

FOR x IN 1..10 LOOP

IF (g_tbl_attributes_info.COUNT <> 0)
THEN
l_attribute_index := hxc_timestore_deposit_util.get_index_in_attr_table
(p_attr_table => g_tbl_attributes_info,
p_attr_id_to_find => l_time_attribute_id,
p_attribute_name_to_find => lt_attribute_tbl(x).att_name );

IF (l_attribute_index < 0) THEN
hxc_timestore_deposit.create_attribute
(p_building_block_id => p_detail_block_id ,p_attribute_name => lt_attribute_tbl(x).att_name ,p_attribute_value => lt_attribute_tbl(x).att_value ,p_attribute_id => l_time_attribute_id ,p_app_attributes => g_tbl_attributes_info );
ELSE
hxc_timestore_deposit.update_attribute
(p_time_attribute_id => l_time_attribute_id
,p_attribute_name => lt_attribute_tbl(x).att_name
,p_attribute_value => lt_attribute_tbl(x).att_value
,p_app_blocks => g_tbl_timecard_info
,p_app_attributes => g_tbl_attributes_info
);
END IF;
END IF;
END LOOP;

Introduction to HRMS

HRMS suite of Oracle applications is useful in managing the employees of an enterprise. Some of the major featues are capturing employee information, day to day employee management, time entry, performance capture and appraisals, capturing compensation information and managing payroll.

Important modules within HRMS are

• HR (Person, Assignment, work structures)
• Payroll (setting up compensation for employees, running payroll, paying employees).
• Oracle Time and Labor (Time entry and linking it to payroll, projects)
• Self Service HR (self service responsibilities to carry out various manager and employee tasks)
• i-Recruitment (managing recruitment).
• Oracle Advance Benefits (OAB)- managing additonal benefits (pensions, insurances) offered to employees.

We will cover introduction of core HR and Payroll module.
Core HR
Some of the important forms in core HR are Person, Assignment, Organization, Jobs, Position.
Business Group (BG):
The Business Group is one of the most important concepts in HRMS. It is the highest level of organization in HRMS. Business Group can be created using oraganization window of an HRMS manager responsibility. The organization classification has to be Business Group. Once the business group data is saved, we need to navigate to Business Group Information (first entry in menu after clicking Others button in organization form) and define various fields. The entry into this Business Group Information flexfield defines setups which will be active in various forms in any HRMS responsibility.
Whenever a business group is defined, a security profile gets created for it with the same name as the business group. If we want to assign any responsibility to business group, we need to define profile HR:Security profile (System Administrator->Profile->System) as the given business group for that responsibility.
The other important profile associated with many HRMS responsibilities is HR: User Type. It also needs to be define before using a responsibility.
One of the important concepts in HRMS is date-tracking. Many of the HRMS tables are date-tracked and usually have _f as suffix in their table_name. Date-tracking is allowing tables/forms to store information on changes made on various date. Suppose a record gets created on 01-Jan-09 and there is a change in record on 01-Feb-09 and the new record should only be active from 01-Feb-09, we make a date-track update on 01-Feb-09. In this case, the current record starting from 01-Jan-09 gets end dated on 31-Jan-09 and a new record gets created on 01-Feb-09 with the new values.

Person Form:
Person form captures the personal information of an employee. Important points to note are

• The employee number field is either updatable or automatically generated based of the Employee number generation field value of the Business Group Information flexfield of business group.
• The Person unique identifier (NI Number for UK, Social Security Number for US) is based on country defined at Business Group Information.
• Person form contains common fields for all the countries like Last Name, First Name, Sex, Date of Birth and also country specific fields based on country defined at business group. The country specific fields can be found in descriptive flexfield Further Person Information. Each of the country (implemented in oracle applications) has a context defined in it and segments gives the information on additional fields defined. These fields are stored from PER_INFORMATION1 to PER_INFORMATION30 (based on segment definition of Further Person Information DFF) of table per_all_people_f.
• There is a descriptive flexfield (Additional Person Information) defined for capturing additonal person information for a customer. This DFF is located at base of person form at the right corner. This information is stored in columns ATTRIBUTE1 to ATTRIBUTE30 of per_all_people_f.
• The primary key for per_all_people_f is combination of effective_start_date, effective_end_date and person_id as it is a date track table.
• person_id is foriegn key for tables like per_addresses, per_all_assignments_f.

Assignment Form:
Assignment in HRMS is a type of job a person carries. A person can have multiple assignments. For example: In a medical university+hospital organization, a professer can have two assignments as a professor as well as a doctor. However at a given point of time, one and only one assignment has to be primary. Assignment form captures information like Job, Organization, Position, Location and so on. There are country specific information which are also captured on various tabs available in assignment form. This fields are defined from Key flexfield Soft Coding Key Flexfield. Each of the implemented country has a context in it. Assignment form also has an DFF Additional Assignment Information defined for capturing customer specific assignment data.
Assignment information is stored in per_all_assignments_f which has a primary key combination of assignment_id, effective_start_date and effective_end_date. Assignment_id is foreign key to tables like pay_element_entries_f (element entry form).
Other forms:
Few other important forms in HR are organization, job, position, grades, location and so on. These forms define these entitites which are captured on person and assignment form.
PAYROLL:
Payroll modules consists of setting and running up the payroll processes like payroll run, pre-payments, payment method programs like Cheque Writer, Electronic Fund Transfer (EFT- NACHA for US, BACS for UK), Costing, Retro Pay, Advanced Pay and so on. All these processes requires Payroll to setup.
Payroll:
Payroll is a top level entity in payroll module which is run as a part of above payroll processes. Payroll definition can be created at HRMS Manager --> Payroll --> Description. The important details entered are period type (monthly, weekly), payment method (cash, EFT, Cheque). Before setting up the payroll, the payment methods can be defined at HRMS Manager --> Payroll --> Payment Methods.
Table for payroll definition information is pay_all_payrolls_f.
Payroll has different forms to setup data for payroll processes. The most important process in payroll is payroll run.
Payroll Run:
It calculates the all the payment details of the employee in terms of amount he receives and amount gets deducted from his salary. The most important setup to do before running a payroll run is to setup payroll elements and assign these elements (element entries) to employee's assignment. (see the next topic for information on elements).
Once the assignments has been assigned appropriate elements as element entries, payroll run can be initiated by either running the concurrent program Payroll Run or running a quick pay from employee's assingment (assignment--> others--> quick pay).
Elements and Element Entries:
Element are basically the pay components for the person (or assignment). It denotes earning components like Regular Earnings, Bonus, different allowances and deduction components like some monthly deduction against meals. An element can be created for each of the above component. Element can be created through HRMS Manager Responsibillity --> Total Compensation: Basic --> Element Description. Elements can be of various type with most important being earnings and deductions.
Once an element has been created, it has to be made available to the assignments through element links. Element Links are kind of eligilbility crieteria determining which all assignments are eligible to have the element. Element Link can be created through HRMS Manager Responsibillity --> Total Compensation: Basic --> Link. The element can be linked as various levels such as organization, location, payroll etc. So all the assignments coming under that organization/location/payroll can have those elements as element entries. The term element entries denotes the element added to assignments through assignment form --> element entries.
An element can have one or more input values. Input values are different values which these elements can carry.
Tables related to elements are
Elements --> pay_element_types_f
Element input values --> pay_input_values_f
Element entries --> pay_element_entries_f
Element entry value (values entered for elements attached to assignment) --> pay_element_entry_values_f
Element links --> pay_element_links_f
Fast Formula:
Fast formula are ways of doing calculations in HRMS. Fast formula can be attached to various entities in hrms like elements, absences. So whenever HRMS processes these entities, fast formula gets executed. A fast formula has one or more return values. These return values can feed to one or more elements. If it feeds to same element, it is direct feed or else it is indirect feed. Please note that an element can feed input values of elements which have lower priority (please note that priority of 1 means highest and 2 means lower than 1).
Table: ff_formulas_f
Fast formula can interact with the database functions/procedures through formula functions. Formula functions are way to call the database functions and procedures in the fast formula.
Navigation:
Fast Formula: HRMS Manager Responsibillity --> Total Compensation: Basic --> Write Formula
Formula Functions:HRMS Manager Responsibillity --> Other Definition --> Formula Functions.
Fast Formula can be attached to element through formula result screen
HRMS Manager Responsibillity --> Total Compensation: Basic --> Formula Results.
Basic Setups for few payroll processes: (These are common setups and some legislations might have additional setup)
Payroll Run:

• Attach the payroll at assignment.
• There are various legislative setups for each localization, ensure the setups are done.
• Attach elements at assignment --> Elements and give input values for them.

Pre-payments:

• Attach the payment method to the assignment through assignment --> payment method. If a payment method is not coming here, then that payment method may not be in valid payment methods listed in payroll definition

NACHA/BACS/EFT/Check Writer:

• Ensure proper payment method (like check payment method for check writer) are attached to assingment.

Brief Overview of payroll run:
When a payroll run is initiated, the run proccess all the elements attached to the assignments. If the fast formulae are assigned to these elements, the run will process these fast formulae and feed their results into the elements collecting the results. These elements will also be processed for any further fast formulae attached. Whenever an element is processed in a payroll run, run outputs these elements as run results. Payroll balances are summations of these run results over a period of time. Balance Type can be defined at HRMS Manager Responsibillity --> Total Compensation: Basic --> Balance. The feed tab on balance form indicates which element run results add or substract from balance type and dimension shows for which time the balance type can be summed up. A defined balance gives the value of balance type over a period of time (dimension). For example: Consider a balance type Regular Earnings. The feed for that balance type can be element Regular Earnings. The dimensions attached to balance type can be period to date and year to date. So defined balances available will be Regular Earnings Period to Date(REGULAR_EARNINGS_PTD) and Regular Earnings Year to Date(REGULAR_EARNINGS_YTD).
The results of payroll run and other processes can be viewed from HRMS Manager Responsibillity --> View --> Payroll Process Results. This screen has navigations for viewing run results and defined balance values for each of the processed assignment of payroll run.
Tables Involved:
Payroll process information: pay_payroll_actions
For each assignment processed in payroll processes, an entry is made into pay_assignment_actions.
Payroll run results are stored into pay_run_results
The values of these run results are stored in pay_run_result_values.
Balance Type Definition: pay_balance_types
Balance Dimension Information: pay_balance_dimensions
Defined balance (definition and NOT values): pay_defined_balances.
The defined balance values are calculated either at run time from run or through run balances or latest balances tables. These are concepts of Payroll Balance Architecture which we will discuss later.

My OAF Learnings

• To have a proper layout and all the message components (messageTextInput, messageStyleText) rendered properly, put them under a message layout region.



• Changes to BC4J object code require complete mid-tier bounce.



• Advance table component can be used for a dynamice table (run-time addition of columns)



• To navigate to home page, use OA.jsp?page=/oracle/apps/fnd/framework/navigate/webui/HomePG&homePage=Y



• Different CSS styles can be found at \myhtml\OA_HTML\cabo\styles. Custom.css can be used to create custom CSS styles and it needs to be then deployed.



• It is not like that we always need extension to pass runtime parameters from one page to another. We can pass runtime VO attribute values from one page to another using personalization, however for that we should have initialized/executed the VO. Also in that case the UI component used for forwarding (button, link) should have that VO available. One of the example of passing VO attributes runtime using personalization of destination URL is OA.jsp?page=&retainAM=Y&Timecard_id={@TimecardId}&StartTime={@VoPeriodStarts}&StopTime={@VoPeriodEnds}&TimecardApprovalStatus={@StatusCode}



• While navigating from one page to another through OAF code, use retainAM flag depending upon whether you want to start fresh on next ptage or retain the values of exisiting page.



• Use the below code in processRequest in case you are using advance table for showing your data and you are not using search panel.

OAAdvancedTableBean tableBean = (OAAdvancedTableBean) webBean.findChildRecursive("TimecardAdvTabRN");

tableBean.queryData(pageContext);

• The webui xml files can be found at the mds directory of the product top i.e. for PER, it is at $PER_TOP/mds. The other OAF code (class files and xml files for server components) can be found under $JAVA_TOP/oracle/apps/.

• To share values between controller and BC4J objects, use transaction values. In controller they can be used as oapagecontext.putTransactionValue, oapagecontext.getTransactionValue and oapagecontext.removeTransactionValue. In AM it can be accessed as

OADBTransactionImpl dbTrans = (OADBTransactionImpl)getOADBTransaction();

dbTrans.getValue, dbTrans.putValue.

• To share values between multiple pages, we can use session parameters which can be used with oapagecontext.putSessionValue, oapagecontext.getSessionValue



• When an item, link or button raise an action which is used to navigate to new page, the page parameters can be passed to new page from following

• The error java.sql.SQLException: Missing IN or OUT parameter at index:: 1 can have following reasons

1. Using some VO methods before executing the VO

2. Some database objects used (package) not valid

3. Using same number bind variable (say :2) more than once in query.

Setting up a custom application and other basic setups in 11i/R12

Creating a Custom Application in Applications 11i
-------------------------------------------------

Custom Applications are required if you are creating new forms, reports, etc.
This allows you to segregate your custom written files from the standard seeded functionality that Oracle Applications provide. Customizations can therefore be preserved when applying patches or upgrades to your environment.

1) Make the directory structure for your custom application files.

2) Add the custom module into the environment

3) Create new tablespace for database objects

4) Create schema

5) Register your Oracle Schema. Login to Applications with System Administrator responsibility Navigate to Application-->Register Application = MZ Custom Short Name = MZ Basepath = MZ_TOP Description = MZ Custom Application

6) Register Oracle User Naviate to Security-->Oracle-->Register Database User Name = MZ Password = MZ Privilege = Enabled Install Group = 0 Description = MZ Custom Application User

7) Add Application to a Data Group Navigate to Security-->Oracle-->DataGroup Data Group = MZGroup Description = MZ Custom Data Group Click on "Copy Applications from" and pick Standard data Group, then add the following entry. Application = MZ Custom Oracle ID = APPS Description = MZ Custom Application

8) Create custom request group This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage) Navigate to Security-->responsbility-->Request Group = MZ Request Group Application = MZ Custom Code = MZ Description = MZ Custom Requests We will not define any requests to add to the group at this stage, but you can add some now if required.

9) Create custom menu This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage) We will create two menus, one for Core Applications and one for Self Service.

10) Create new responsibility. One for Core Applications and One for Self Service (SSWA)

11) Add responsibility to user Navigate to Security-->User-->Define Add MZ Custom responsibility to users as required.

12) Other considerations You are now ready to create your database Objects, custom Reports, Forms, Packages, etc Create the source code files in the MZ_TOP directory appropriate for the type of object. For example forms would be located in $MZ_TOP/forms/US or package source code in $MZ_TOP/admin/sql for example.

Database Objects, such as tables, indexes and sequences should be created in the MZ schema, then you need to
a) Grant all privilege from each custom data object to the APPS schema. For example : logged in as MZ user grant all privileges on myTable to apps;
b) Create a synonym in APPS for each custom data object For example : logged in as APPS user create synonym myTable for mz.myTable;
Other database objects, such as views and packages should be created directly in the APPS schema

Useful Information on OAF Personalization

1. Set the profile Personalize Self-Service Defn to Yes for Personalization
2. SPEL technique can be used to set the item properties like rendered based on a boolean value returned by page attributes. The SPEL expressions have a syntax ${oa..}. More info can be found here

Useful OAF Links

1. Finding Version of Jdev for OAF
2. Step by step example of OAF extension
3. OAF Development FAQ