Comparing OAF and ADF

We will compare OAF and ADF in the post.

Similarities between OAF and ADF
Let us see the similarities between them. This will make OAF guys easier to understand ADF.

• The Model layer (considering MVC) of ADF is quite similar to ADF. It has same concepts on EO, VO, VL and AM
• Both uses JDeveloper as a development tool. However there is a basic difference here. OAF uses special JDeveloper versions created for an Oracle Applications release level whereas ADF uses more generic JDeveloper tool based on the Fusion Middleware release level.

Differences between ADF and OAF

• OAF is tightly tied to Oracle Applications and can't be used standalone. ADF is independent of Oracle Applications and runs on Fusion Middleware WebLogic server.
• The View and Controller layer of OAF is more JSP based whereas ADF uses ADF components for its view and controller layer. The ADF View and Controller components are extension of JSF components.
• Security in OAF is implicitly handled by Oracle Applications, however security for ADF pages needs to be configured.
• ADF needs more latest versions of browsers

Oracle Apps Important Tables

-------------------------------------------------------------------------------------------------
HRMS
-------------------------------------------------------------------------------------------------
People -- per_all_people_f
Assignments -- per_all_assignments_f, hr_soft_coding_keyflex
Addresses -- per_addresses
Job -- per_jobs, per_job_definitions
Position -- per_positions, per_position_definitions
Grade -- per_grades, per_grade_definitions
Payroll -- pay_all_payrolls_f
Absence -- per_absence_attendances, per_absence_attendance_types
People Groups -- pay_people_groups
Organization -- hr_organization_units, hr_organization_information
Person EIT -- per_person_extra_info
Assignment EIT -- per_assignment_extra_info
Element Types -- pay_element_types_f, pay_input_values_f
Payroll processes -- pay_assignment_actions, pay_payroll_actions
Run Results -- pay_run_results, pay_run_result_values
Payment Methods -- pay_personal_payment_methods_f, pay_org_payment_methods_f
Element Entry -- pay_element_entries_f, pay_element_entry_values_f
Medical Assessments -- per_medical_assessments
Employment Details -- per_periods_of_service
Timecard data -- hxc_time_building_blocks, hxc_time_attribute_usages, hxc_time_attributes

----------------------------------------------------------------------------------------------
-- CM
-----------------------------------------------------------------------------------------------
Bank Statement -- ce_statement_headers_all, ce_statement_lines, ce_intra_stmt_headers_all
Bank Statement Interface -- ce_statement_headers_int_all, ce_statement_lines_interface

----------------------------------------------------------------------------------------------
-- PO
----------------------------------------------------------------------------------------------
Purchase Order -- po_headers_all, po_lines_all, po_line_locations_all, po_distributions_all

Using Applet in OAF

I have came across the use of applet within OAF page for a requirement where I had to use Java APIs provided by an activeX control loaded on client machine. Since OAF code runs on server side and not on client machine, using the applet was the only option to use.

The steps for using the applet are

• Suppose XXApplet.class is the class file of your applet. First step is to package the applet into a jar file and sign the jar file. A page allow only singed applets to run, hence signing the applet is required.
• To convert an applet class to a jar, command is

jar cvf uXXApplet.jar XXApplet.class

• To sign the above jar file, follow below two links in chronological order by logging into unix server

http://java.sun.com/docs/books/tutorial/security/toolfilex/step2.html

http://java.sun.com/docs/books/tutorial/security/toolfilex/step3.html

• On your OAF page, create OARawTextBean item. Lets assume its ID is xxApplet.
• Extend the controller of the page. In your process request, you can attach the html text to the above Raw Text Bean as below. The example also shows passing the parameters to applet as "DocumentName" and "DocumentId".

String appletCode = "{applet code='XXApplet.class' ARCHIVE='XXApplet.jar' width=550 height=75}" + "{PARAM NAME='DocumentName' VALUE='XXXXXXX'}" + "{PARAM NAME='DocumentId' VALUE='A5577'}" +"This browser does not support Applets.{/applet}"; // replace } with > and { with <

OARawTextBean appletBean = (OARawTextBean)webBean.findChildRecursive("xxApplet"); !

appletBean.setText(appletCode);

• Put the signed Applet jar file at $OA_HTML.

Useful Scripts

------------------------------------------------------------------
-- CREATE USER AND ATTACH SYS ADMIN
------------------------------------------------------------------

DECLARE

  v_session_id INTEGER := userenv('sessionid');

  v_user_name  VARCHAR2(30) := upper('&Enter_User_Name');

BEGIN

  --Note, can be executed only when you have apps password.

  -- Call the procedure to Creaet FND User

  fnd_user_pkg.createuser(x_user_name                  => v_user_name

    ,x_owner                      => ''

    ,x_unencrypted_password       => 'welcome123'

    ,x_session_number             => v_session_id

    ,x_start_date                 => SYSDATE - 10

    ,x_end_date                   => SYSDATE + 100

    ,x_last_logon_date            => SYSDATE - 10

    ,x_description                => 'test'

    ,x_password_date              => SYSDATE - 10

    ,x_password_accesses_left     => 10000

    ,x_password_lifespan_accesses => 10000

    ,x_password_lifespan_days     => 10000

    ,x_employee_id                => null /*Change this id by running below SQL*/

     /*   

     SELECT person_id

           ,full_name

     FROM   per_all_people_f

     WHERE  upper(full_name) LIKE '%' || upper('full_name') || '%'

     GROUP  BY person_id

              ,full_name

     */

    ,x_email_address => null

   ,x_fax           => ''

    ,x_customer_id   => ''

    ,x_supplier_id   => '');

  fnd_user_pkg.addresp(username       => v_user_name

                      ,resp_app       => 'SYSADMIN'

                      ,resp_key       => 'SYSTEM_ADMINISTRATOR'

                      ,security_group => 'STANDARD'

                      ,description    => 'Auto Assignment'

                      ,start_date     => SYSDATE - 10

                      ,end_date       => SYSDATE + 1000);

END;

------------------------------------------------------------------
-- DELETE MEDICAL ASSESSMENTS
------------------------------------------------------------------

DECLARE

CURSOR c IS
SELECT *
FROM per_medical_assessments;

BEGIN

FOR i IN c

LOOP

per_medical_assessment_api.delete_medical_assessment
(
p_medical_assessment_id => i.medical_assessment_id
,p_object_version_number => i.object_version_number
);
DBMS_OUTPUT.PUT_LINE('Success: person_id = '||i.person_id);

END LOOP;

END;

Useful Queries

------------------------------------------------------------------------------------------
-- APPS
------------------------------------------------------------------------------------------

--Get responsibilities where a particular concurrent program is registered
SELECT resp.responsibility_name, cp.user_concurrent_program_name
  FROM apps.FND_RESPONSIBILITY_VL resp,
       apps.FND_REQUEST_GROUP_UNITS rgu,
       apps.FND_CONCURRENT_PROGRAMS_VL cp
 WHERE resp.request_group_id = rgu.request_group_id
   AND rgu.request_unit_id = cp.concurrent_program_id
   AND UPPER (user_concurrent_program_name) LIKE UPPER('concurrent%program%name%');


------------------------------------------------------------------------------------------
-- HRMS
------------------------------------------------------------------------------------------

-- Looking for assignments with a particular element entry values
SELECT
paf.assignment_number,
petf.element_name,
pivf.name,
pevf.screen_entry_value
FROM
pay_element_entries_f peef ,
pay_element_entry_values_f pevf,
per_assignments_f paf,
pay_element_types_f petf,
pay_input_values_f pivf
WHERE
peef.assignment_id = paf.assignment_id
AND peef.element_type_id = petf.element_type_id
AND petf.element_type_id = pivf.element_type_id
AND pivf.input_value_id = pevf.input_value_id
AND peef.element_entry_id = pevf.element_entry_id
AND petf.element_name IN ('ELEMENT1','ELEMENT2')
AND pivf.name = 'INPUT1'
AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN peef.effective_start_date AND peef.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pevf.effective_start_date AND pevf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN petf.effective_start_date AND petf.effective_end_date
AND TRUNC(SYSDATE) BETWEEN pivf.effective_start_date AND pivf.effective_end_date;

-- To get earning policies, payroll and responsibilities attached to user
SELECT psa.employee_number,
       psa.full_name,
       psa.user_name,
       psa.responsibility_name,
       ep.name earning_policy,
       ppf.payroll_name
  FROM
       apps.per_sec_profile_assignments_v psa,
       apps.per_all_assignments_f paf,
       apps.hxt_add_assign_info_f aif,
       apps.hxt_earning_policies ep,
       apps.pay_all_payrolls_f ppf
 WHERE      
       psa.employee_id = paf.person_id
   AND aif.assignment_id = paf.assignment_id
   AND paf.payroll_id = ppf.payroll_id
   AND aif.earning_policy = ep.id
   AND TRUNC(SYSDATE) BETWEEN NVL(aif.effective_start_date,TRUNC(SYSDATE)) AND NVL(aif.effective_end_date,TRUNC(SYSDATE))
   AND TRUNC(SYSDATE) BETWEEN paf.effective_start_date AND paf.effective_end_date
   AND TRUNC(SYSDATE) BETWEEN ppf.effective_start_date AND ppf.effective_end_date
 ORDER BY 1;


------------------------------------------------------------------------------------------
-- OTL
------------------------------------------------------------------------------------------

--Getting time attributes data
select t.time_building_block_id, t.scope,t.start_time, t.stop_time,t.object_version_number,t.date_from, t.date_to,
a.*
from hxc.hxc_time_building_blocks t
, hxc.hxc_time_attribute_usages u
, hxc.hxc_time_attributes a
where t.resource_id = 2189
--AND t.scope = 'DETAIL'
and t.start_time between to_date('18-APR-2010','DD-MON-YYYY')
and to_date('24-APR-2010','DD-MON-YYYY')
and t.time_building_block_id = u.time_building_block_id
and u.time_attribute_id = a.time_attribute_id
AND t.object_version_number = u.time_building_block_ovn
AND SYSDATE BETWEEN t.date_from AND t.date_to
--AND attribute_category not LIKE 'SECURI%'
order by t.scope,t.start_time, t.object_version_number

-- The below query will show duplicate attribute rows in case there is a data corruption due to duplicate rows
 select t.*,a.*


from hxc.hxc_time_building_blocks t

, hxc.hxc_time_attribute_usages u

, hxc.hxc_time_attributes a

where t.resource_id = 3169

AND t.scope = 'DETAIL'

and TRUNC(t.start_time) BETWEEN to_date('05-SEP-2010','DD-MON-YYYY') AND to_date('11-SEP-2010','DD-MON-YYYY')

and t.time_building_block_id = u.time_building_block_id

and u.time_attribute_id = a.time_attribute_id

AND t.object_version_number = u.time_building_block_ovn

AND SYSDATE BETWEEN t.date_from AND t.date_to

AND a.attribute_category = xxrpay_hxc_utils_pk.get_otl_context_dff('LMR','Timesheet Entry')

order by TRUNC(t.start_time),t.object_version_number;


--Below query will get time entries for each day for payroll type timecard
    SELECT ppx.employee_number ,
           ppx.full_name,
           htb1.start_time day_worked,
           elem.element_name,
           TO_CHAR(htb2.start_time,'dd-MON-YYYY HH:MI AM') start_time,
           TO_CHAR(htb2.stop_time,'dd-MON-YYYY HH:MI AM') stop_time,
           NVL(htb2.measure,ROUND((htb2.stop_time - htb2.start_time)*24,2)) hours,
           hts.recorded_hours total_timecard_hours,
           hts.start_time tc_start_date,
           hts.stop_time tc_end_date
      FROM apps.hxc_time_building_blocks htb1,
           apps.hxc_time_building_blocks htb2,
           apps.hxc_time_attribute_usages htau,
           apps.hxc_time_attributes hta,
           apps.pay_element_types_f elem,
           apps.per_people_x ppx,
           apps.hxc_timecard_summary hts
     WHERE htb1.SCOPE                          = 'DAY'
       AND htb1.date_to                        = TO_DATE('31-DEC-4712')
       AND htb2.parent_building_block_id       = htb1.time_building_block_id
       AND htb2.parent_building_block_ovn      = htb1.object_version_number
       AND htb2.SCOPE                          = 'DETAIL'
       AND htb2.date_to                        = TO_DATE('31-DEC-4712')
       AND htau.time_building_block_id         = htb2.time_building_block_id
       AND htau.time_building_block_ovn        = htb2.object_version_number
       AND htau.time_attribute_id              = hta.time_attribute_id
       AND SUBSTR (hta.attribute_category,INSTR (hta.attribute_category, ' - ') + 3,3) LIKE elem.element_type_id
       AND htb1.start_time BETWEEN elem.effective_start_date and elem.effective_end_date
       AND ppx.person_id = htb1.resource_id
       AND hts.resource_id = ppx.person_id
       AND TRUNC(htb1.start_time) BETWEEN TRUNC(hts.start_time) AND TRUNC(hts.stop_time)
      AND TRUNC(htb1.start_time) BETWEEN :p_start_date AND :p_end_date
      AND ppx.employee_number = :p_emp_no
       ORDER BY ppx.employee_number, htb1.start_time, htb2.start_time;

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