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;