element sets within Payroll

Is it possible to prevent users from updating particular element entries? Well, today i learned that a profile option can be set to specify an element set which defines the 'do not touch me' element types.

An overview of the different types of element sets:

Customization set, for element entries

• Limit the elements that can be seen on a configured version of the Element Entries window
• List the elements to be entered for assignments using BEE
• Prevent users updating entry values in the Element Entries form for a set of elements. (set the element set in the profile option HR:Non-Updateable Element Set)

Run set, for payroll processing

• Use a run set to specify the elements to be processed in a payroll run.

Distribution set, for costing

• Use a distribution set to define the elements over which the costs of other elements are to be distributed.

Bye bye family packs, welcome Rollups

Oracle released the first rollup patch for Hrms.

One of the new features is the FastFormula Assistant. She let's you

• choose multiple FastFormulas to compile
• show line numbers for formula text to locate easily errors
• upload/download a formula for integration with your favorite text editor
• generate FastFormula text templates

fusion = Ebusiness Suite 12

I read a blarticle about fusion that is not just repeating the messages oracle spreads, but is trying to read in between the lines. And funny.

Calculate absence duration

When you create the fast formula BG_ABSENCE_DURATION (probably a copy from TEMPLATE_ABSENCE_DURATION), this formula is used to calculate the duration of the absences entered in your business group (BG). What's in a name.

Good to known is that this formula can have as extra parameters

• assignment_id (context)
• element_type_id (context)
• absence_attendance_type_id

The last one allows you to implement calculation rules per absence type.

Online Sql Trace

When you have long running processes (like payroll PUYGEN), you do not need a sql trace file to find the top sql consumers.

Some sql statements can give you the same result.

select module, sql_hash_value, s.*
from v$session s
where module = 'PUYGEN'
order by 1, 2

select sql_text, s.executions, buffer_gets, s.*
from v$sql s
where hash_value =

select *
from v$sql_plan
where hash_value =

order by id

But this set of statements will not give you the waits.

Htmldb gives pls-00103

A customer wanted to have a decentralized application, based on the positions of Oracle Hrms.

Since self-service is non-existing for positions, we had to explore other options. At the end we opted for HtmlDb.

Installation went smooth, and the workspace was created in a snap. But when creating a first test application, we had several errors, but without error messages.

At the end we were able to see the error message, together witha piece of code. pls-00103 was popping up, together with a select in a select, dynamically generated by htmldb.

We brought the problem down to:
declare
n number;
begin
select nvl( 1, (select 2 from dual))
into n
from dual;
end;

This snippet executed fine on my laptop database (9.2.0.1) ,but not on the apps database (9.2.0.5).

We were sure we had discovered a new bug in the database software, but a lucky metalink hit brought us further.

Event 10933 mimics old plsql-sql behavior, and this event was set in our upgraded oracle apps instance.

We unset the event, and voila.

Summary: oracle apps ; htmldb ; pls-00103 ; event 103999

List all request launched via a request set

This query shows all request related to a master request with timing and dependecies.

select
level,
status_code,
(
select nvl( description, concurrent_program_name)
from fnd_concurrent_programs p
where p.concurrent_program_id = r.concurrent_program_id
and p.application_id = r.program_application_id
) prog,
trunc( ( actual_completion_date - actual_start_date) * 24 * 60, 2) minuten,
request_id,
argument_text,
priority
from fnd_concurrent_requests r
where 1 = 1
start with r.request_id = :p_top_request_id
connect by prior request_id = parent_request_id
order siblings by request_id;

See more concurrent output.

It is possible to see someone other's concurrent output, when you are linked to the same responsibility the user submitted the concurrent.

You do that by setting the profile option Concurrent:Report Access Level to Responsibility instead of the default or implicit value User.

This feature can become very handy for operational people to share each others output.

the organizational payment methods api

The update api for organizational payment methods (pay_org_payment_method_api / update_org_payment_method) shows some unpredictable effects.

In order to overcome that, i had

• to pass a null value for the p_sets_of_book_id, instead of the default value.
• to pass a value for a random pmeth_information column, instead of defaulting them all (and leave them out of the api call).

How to access concurrent output via a browser, outside apps?

The example included returns an url, which will give you the log file of request 194790. Oracle apps manages this via the fndwrr executable. The temp_id is an encrypted number, generated by the apps software.


declare
l_request_id number := 194790;
l_two_task varchar2(256);
l_gwyuid varchar2(256);
l_url varchar2(1024);
begin
select profile_option_value
into l_gwyuid
from fnd_profile_options o, fnd_profile_option_values ov
where profile_option_name = 'GWYUID'
and o.application_id = ov.application_id
and o.profile_option_id = ov.profile_option_id;
--
select profile_option_value
into l_two_task
from fnd_profile_options o, fnd_profile_option_values ov
where profile_option_name = 'TWO_TASK'
and o.application_id = ov.application_id
and o.profile_option_id = ov.profile_option_id;
--
l_url := fnd_webfile.get_url
(
file_type => fnd_webfile.request_log,
id => l_request_id,
gwyuid => l_gwyuid,
two_task => l_two_task,
expire_time => 100 -- minutes, security!.
);
dbms_output.put_line( l_url);
end;

Fast Formula Wrappers

It was only when we ran into the APP-FF-33186 error (kindly inviting us to regenerate the wrapper package) that I realized that the compiler technology behind fast formulas was changed slightly.

In order to minimize dynamic sql calls, oracle generates for each fast formula package (FFP_) now also a wrapper package, starting with FFW.

This can be seen into the code behind the ff_exec package, from familypack H on.

I cannot come up with a good reason why this wrapper packages saw the light. Performance comes in my mind, or the ability to run fast formulas directly through sql?

Run the ffgenwrap.sql script to overcome the error messages, located in $FF_TOP/patch/115/sql.

Change the employee name format in Self Service

By default, the employee's name is shown as last name, first name. You can overrule that to the full name format by setting the profile option 'HR:Display Person Name' .

Saving the output of a concurrent request on your desktop.

Often Apps is configured that way that the output of a concurrent request is opened within your browser. You can save that file from the browser, but it is possible the browser changes the content of your file.

Some steps to change that behaviour.

1. Navigate via System Administrator, Install to the Viewer Options screen.
2. Add an additional entry for TEXT, and associate a non existing mime type to it.
3. Change the profile option for 'Viewer Application for Text' for your user or responibility to the newly created option. From now on, all the concurrent programs with TEXT as output format for you or all users of the responsibility, will see the impact of your changes.
4. Open the output of a request of a TEXT concurrent program.
5. Since no mime type is associated with this extention, Windows asks you to save the file, or to open it with a specific desktop application.

The employee API fills in the background also the CRM parties table, and as a consequence generates also a lot of workflow business events.

When you do NOT use CRM, you can stop these side-actions of the employee API by setting the profile option "HZ: Execute API Callouts" to No (defaulted on Yes)

this query gives you all the Tier I loclaizations/legislations supported by Oracle core dev team:

select
nvl( territory_short_name, 'ZZ International') legislation,
(
Select 'Y'
from hr_legislation_installations li2
where li2.application_short_name = 'PAY'
and li2.legislation_code = li.legislation_code
) payroll
from hr_legislation_installations li, fnd_territories_tl t
where application_short_name = 'PER'
and legislation_code = territory_code (+)
and language (+)= 'US'
order by 2, 1

To enable full scale concurrency for inserts and updates on tables, one has to change the settings of initrans. In previous versions of Oracle this was only possible by recreating the table. In 9iR2 this can be done dynamically. Included an example:

alter table hr.hr_comments initrans 12
 
 

To find all components and version of the pro*C payroll executable (PYUGEN), run the following command on your unix flavor from the $PAY_TOP/bin directory:

strings -a PYUGEN | grep '$Header'

The seeded Retronotification Report (both normal and advanced) end up with a REP-1212 report error. The seeded print style is not wide enough.

Changing the style to US Landwide overcomes this. Note that you cannot update that field through forms, even if you copy that concurrent program.

By the way, the concurrent executes the Pro*C payroll PYUGEN, which starts the Oracle Report PAYRPRNP.rdf.

What are the database items used by a particular fast formula?

Query the ff_fdi_usages_f table to answer that question.

To start a concurrent program via the Self Service interface, add the function 'Schedule Requests' (FNDCPSRSSSWA) to your menu.

This function can be added in a pure Self Service menu, but also in a forms menu.

This function can be used as a template to start specific concurrent programs, by adding the parameters 'programApplName' and 'programName' to the html call.