Displays HRMS details

SELECT * FROM FND_PRODUCT_GROUPS


Family Pack

SELECT GREATEST(NVL((SELECT DECODE(BUG_NUMBER,'2968701','HRMS_PF.F') FROM ad_bugs
WHERE BUG_NUMBER = (SELECT MAX(BUG_NUMBER) FROM AD_BUGS where BUG_NUMBER = '2968701')),'A'),
(SELECT DECODE(SUBSTR(I.PATCH_LEVEL,9,1)
, 'H', 'HRMS_PF.A', 'I', 'HRMS_PF.B', 'J', 'HRMS_PF.C'
, 'K', 'HRMS_PF.D', 'L', 'HRMS_PF.E', 'M', 'HRMS_PF.G'
, 'N', 'HRMS_PF.H', 'O', 'HRMS_PF.I', 'P', 'HRMS_PF.J', 'Unknown')
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I
WHERE V.APPLICATION_ID = I.APPLICATION_ID
AND (V.APPLICATION_ID = '800'))) hrmsPF FROM DUAL;


HR Status

SELECT L.MEANING HRStatus
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (V.APPLICATION_ID = '800')
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status);


Payroll Status

SELECT L.MEANING PayStatus
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (V.APPLICATION_ID = '801')
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status);


Applications List

SELECT V.APPLICATION_NAME app
, to_char(V.APPLICATION_ID) appId
, L.MEANING appStatus
, DECODE(I.PATCH_LEVEL, NULL, '11i.' v.APPLICATION_SHORT_NAME '.?', I.PATCH_LEVEL) patch
FROM FND_APPLICATION_ALL_VIEW V, FND_PRODUCT_INSTALLATIONS I, FND_LOOKUPS L
WHERE (V.APPLICATION_ID = I.APPLICATION_ID)
AND (V.APPLICATION_ID IN
('0', '50', '178', '275', '453', '800', '801', '802', '803', '804', '805', '808', '809', '810', '8301', '8302', '8303'))
AND (L.LOOKUP_TYPE = 'FND_PRODUCT_STATUS')
AND (L.LOOKUP_CODE = I.Status )
ORDER BY 1;


HR Global

SELECT ap.patch_name patchNumber
, 'hrglobal.drv' patchName
, pr.end_date appliedDate
FROM ad_applied_patches ap
, ad_patch_drivers pd
, ad_patch_runs pr
, ad_patch_run_bugs prb
, ad_patch_run_bug_actions prba
, ad_files f
WHERE f.file_id = prba.file_id
AND prba.executed_flag = 'Y'
AND prba.patch_run_bug_id = prb.patch_run_bug_id
AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id
AND pd.applied_patch_id = ap.applied_patch_id
AND f.filename = 'hrglobal.drv'
AND pr.end_date = (SELECT max(pr.end_date)
FROM ad_applied_patches ap
, ad_patch_drivers pd
, ad_patch_runs pr
, ad_patch_run_bugs prb
, ad_patch_run_bug_actions prba
, ad_files f
WHERE f.file_id = prba.file_id
AND prba.executed_flag = 'Y'
AND prba.patch_run_bug_id = prb.patch_run_bug_id
AND prb.patch_run_id = pr.patch_run_id
AND pr.patch_driver_id = pd.patch_driver_id
AND pd.applied_patch_id = ap.applied_patch_id
AND f.filename = 'hrglobal.drv');


Legislations

SELECT DECODE(legislation_code
,null,'Global'
,legislation_code) legCode
, DECODE(application_short_name
, 'PER', 'Human Resources'
, 'PAY', 'Payroll'
, 'GHR', 'Federal Human Resources'
, 'CM', 'College Data'
, application_short_name) appName
, LAST_UPDATE_DATE appliedDate
FROM hr_legislation_installations
WHERE status = 'I'
ORDER BY legislation_code;


Invalid Objects

SELECT owner, object_type, object_name
FROM dba_objects
WHERE status != 'VALID'
AND object_type != 'UNDEFINED'
ORDER BY 1, 2, 3;

How to find a locking action

On occasion, when rolling back an assignment action and a locking error occurs, it might not always be obvious which assignment action is responsible for the lock. In these circumstances, the following sql may be of assistance:

select ppa.action_type,
ppa.effective_date,
ppa.payroll_action_id,
paa.assignment_action_id
from pay_payroll_actions ppa,
pay_assignment_actions paa,
pay_action_interlocks pai
where ppa.payroll_action_id = paa.payroll_action_id and
paa.assignment_action_id = pai.locking_action_id and
pai.locked_action_id = &lockedid

/ e.g. where &lockedid is the assignment_action_id of the action you are trying to remove. In this case, a Quickpay is being locked out by a Pre-Payment run.

BEE

1) BEE process needs a specific mask for input with format Data. Use the followed command "to_char(start_date,'yyyy/mm/dd')"

2) If inputs of an Element have been defined with a default value, the BEE Transfer process doesn't automatically put the default values.
You need to specify all input values with a default when there is no data in the old system

3) The BEE Transfer process doesn't put a value for input values defined as NOT USER ENTERABLE

4) If inputs of an Element have been defined with a Quick Code, you have to create the BEE Lines with value equal to the meaning in the QC and not the code

Magnetic Transfer

select 'Magnetic Transfer' a_type, org.name company, tp.period_name , lk.bank_name_alt, lk.bank_name bank_name, lk.bank_branch_name_alt branch_name, ea.segment1 bsb, ea.segment2 account_nr, pp.value, ea.segment3 account_name, per.last_name || ', ' || per.first_name full_name, per.employee_number
from pay_payroll_actions pa,
pay_assignment_actions_v aa,
pay_pre_payments pp,
pay_personal_payment_methods_f pm,
pay_external_accounts ea,
ap_bank_branches lk,
per_time_periods tp,
per_assignments_f ass,
per_people_x per,
hr_all_organization_units org,
pay_payrolls_x pay
where 1 = 1
and tp.period_name = nvl(:p_period_name, tp.period_name)
and nvl(pa.date_earned,pa.effective_date) between tp.start_date and tp.end_date
and tp.payroll_id = pa.payroll_id
and pay.payroll_id = tp.payroll_id
AND pay.payroll_name = nvl(:p_payroll_name, pay.payroll_name)
and pa.action_type = 'M'
and pa.payroll_action_id = aa.payroll_action_id
and pp.pre_payment_id = aa.pre_payment_id
and pp.personal_payment_method_id = pm.personal_payment_method_id
and :p_period_end_date between pm.effective_start_date and pm.effective_end_date
and pm.external_account_id = ea.external_account_id (+)
and ea.segment1 = lk.bank_number (+)||'-'||lk.bank_num (+)
and ass.assignment_id = aa.assignment_id
and :p_period_end_date between ass.effective_start_date and ass.effective_end_date
and per.person_id = ass.person_id
and org.organization_id (+)= pa.business_group_id
and decode(:p_business_group,'ALL', org.name, :p_business_group) = org.name

Session date is not populating

The problem manifests in various ways in the custom or regular
Human Resource Management System (HRMS) responsibility.

Repeatedly, list of values (LOV) are missing for parameters in concurrent
processes or process are not running because the session date is not being populated.
The problem occurs if the value set is table validated and the session_id is
part of the 'where' clause. Any value set that references session_id or any
process that has session date as a non displayed (but required) parameter will
not work.

Solution Description
--------------------

To resolve this issue, follow the steps listed below:

1. Sign into Oracle Applications as System Administrator GUI Responsibilty.

2. Bring up the Menu in question (either custom menu or regular HRMS menu)
and add the following function from the LOV:

Navigator: Disable Multiform.

Note: this is an AOL function that should be included on all menus
(custom or standard) to allow session_id to be populated.

3. Save your changes before exiting the form.

4. The concurrent processes you were having problems with should now work
correctly.

Explain Plan - Error

Error:
ORA-02404: specified plan table not found

Explain Plan
The Explain Plan command generates information that details the execution plan that will be used on a particular query. It uses a precreated table (PLAN_TABLE) in the current shema to store information about the execution plan chosen by the optimizer.

Creating the Plan Table

The plan table is created using the script utlxplan.sql. This script is typically found under the Oracle Home in the rdbms/admin directory.
On Unix its location will be:

$ORACLE_HOME/rdbms/admin

On WindowsNT/2000:

%ORACLE_HOME%\rdbms\admin

This script creates the output table, called PLAN_TABLE, for holding the output of the Explain plan Command. Note that the exact structure of the plan table can change with different release as new features are introduced.

Age

select employee_number,
date_of_birth,
trunc(months_between(trunc(sysdate),date_of_birth)/12) age
from per_people_x per

Pay run results

select ptp.period_name,
to_char(ptp.start_date, 'DD-MON-RRRR') start_date,
to_char(ptp.end_date, 'DD-MON-RRRR') end_date,
pay.payroll_name,
et.element_name,
per.employee_number,
per.last_name,
per.first_name,
to_number(prv.result_value)
from per_time_periods ptp,
pay_payrolls_f pay,
pay_payroll_actions ppa,
pay_assignment_actions pac,
pay_element_types_f et,
pay_run_results prr,
pay_run_result_values prv,
pay_input_values_f piv ,
per_assignments_f ass,
per_people_x per
where pay.payroll_name = NVL(:p_payroll_name ,pay.payroll_name)
and pay.payroll_id = ptp.payroll_id
and ptp.period_name = :p_period_name
and ptp.payroll_id = ppa.payroll_id
and ppa.action_type in ( 'R', 'Q')
and ppa.date_earned between ptp.start_date and ptp.end_date
and ppa.payroll_action_id = pac.payroll_action_id
and pac.assignment_action_id = prr.assignment_action_id
and ass.assignment_id = pac.assignment_id
and ptp.end_date between ass.effective_start_date and ass.effective_end_date
and et.element_name = :p_element_name
and ptp.start_date between et.effective_start_date and et.effective_end_date
and piv.element_type_id = et.element_type_id
and piv.name = 'Pay Value'
and prv.input_value_id = piv.input_value_id
and ptp.start_date between piv.effective_start_date and piv.effective_end_date
and prv.run_result_id = prr.run_result_id
and prr.element_type_id = et.element_type_id
and per.person_id = ass.person_id

Absence

select pay.payroll_name payroll_name,
org.name name,
per.employee_number employee_number,
per.first_name first_name,
per.last_name last_name,
abt.NAME Entry,
to_char(ab.date_start,'DD-MON-RRRR') start_date,
to_char(ab.date_end,'DD-MON-RRRR') end_date,
ab.absence_hours
from per_absence_attendances ab,
per_absence_attendance_types abt,
pay_payrolls_x pay,
per_assignments_f ass,
per_people_x per,
per_organization_units org
where org.organization_id = ass.organization_id
and pay.payroll_name = nvl(:p_payroll_name,pay.payroll_name )
and ass.payroll_id = pay.payroll_id
and ass.person_id = per.person_id
and ab.absence_attendance_type_id = abt.absence_attendance_type_id
and ab.person_id = per.person_id
and ab.date_notification between :p_start_date and :p_end_date
and ab.date_notification between ass.effective_start_date and ass.effective_end_date
and ass.primary_flag = 'Y'

Pre-Payments

select pay.payroll_name payroll_name,
1 employee_number,
sum(decode( aa2.action_type, 'R', decode( porg.org_payment_method_name, 'EFT', 1))) EFT_No_R,
sum(decode( aa2.action_type, 'R',decode( porg.org_payment_method_name, 'EFT', pp1.value))) EFT_Amount_R,
sum(decode( aa2.action_type, 'Q', decode( porg.org_payment_method_name, 'EFT', 1))) EFT_No_Q,
sum(decode( aa2.action_type, 'Q',decode( porg.org_payment_method_name, 'EFT', pp1.value))) EFT_Amount_Q,
sum(decode( porg.org_payment_method_name, 'Cheque', 1)) Cheque_No,
sum(decode( porg.org_payment_method_name, 'Cheque', pp1.value)) Cheque_Amount
from per_time_periods tp,
pay_payroll_actions pa,
pay_assignment_actions_v aa,
pay_assignment_actions_v aa2,
pay_pre_payments pp1,
pay_personal_payment_methods_f pm1,
pay_org_payment_methods_f porg,
pay_action_interlocks pil,
per_assignments_f ass,
per_people_x per,
pay_payrolls_x pay
where 1 = 1
and pay.payroll_name = nvl(:p_payroll_name,pay.payroll_name)
and tp.payroll_id = pa.payroll_id
and tp.period_name = :p_period_name
and pa.effective_date between tp.start_date and tp.end_date
and pa.action_type = 'P'
and aa.status_code = 'C'
and aa.payroll_action_id = pa.payroll_action_id
and pp1.assignment_action_id = aa.assignment_action_id
and pm1.personal_payment_method_id (+) = pp1.personal_payment_method_id
and :P_END_DATE_PERIOD between pm1.effective_start_date (+) and pm1.effective_start_date (+)
and pp1.org_payment_method_id = porg.org_payment_method_id
and tp.start_date between porg.effective_start_date and porg.effective_end_date
and ass.assignment_number = aa.assignment_number
and tp.end_date between ass.effective_start_date and ass.effective_end_date
and per.person_id = ass.person_id
and ass.payroll_id = pay.payroll_id
and pil.locking_action_id = aa.assignment_action_id
and aa2.assignment_action_id = pil.locked_action_id
group by payroll_name, per.employee_number

Asg Data Corruptions

Valid for mulple asg
Valid for muliple hire date

select *
from
(
select ass.assignment_id, ass.effective_start_date start_date, ass.effective_end_date end_date, ass2.effective_start_date prev_start_date, ass2.effective_end_date prev_end_date
from per_assignments_f ass,
per_people_f per,
per_assignments_f ass2
where 1 = 1
and ass.person_id = per.person_id
and ass.effective_start_date between per.effective_start_date and per.effective_end_date
and ass.assignment_id = ass2.assignment_id (+)
and ass2.effective_end_date (+) = ass.effective_start_date - 1
and ass.assignment_type = 'E' -- No Applicants
and ass.effective_start_date <> -- No First record of assignment (Valid for primary and secondary Asg)
(
Select min(effective_start_date)
from per_assignments_f ass3
where ass3.assignment_id = ass.assignment_id
)
order by ass.effective_start_date
)
where prev_start_date is null

Assignment Set

Two different ways to create an Assignment Set:

Amendments

select distinct per.employee_number
from per_assignments_f ass,
per_people_x per,
hr_assignment_set_amendments aset
where ass.person_id = per.person_id
and aset.assignment_set_id = :p_ag_set_id
and aset.assignment_id = ass.assignment_id

Criteria

select per.employee_number
from per_assignments_x ass,
per_people_x per,
hr_organization_units org,
hr_assignment_set_criteria aset
where ass.organization_id = org.organization_id
and ass.person_id = per.person_id
and aset.assignment_set_id = :p_ag_set_id
and org.name = aset.right_operan

Click on Generate to update an assignment set

Fnd Session

insert into fnd_sessions
values (userenv('sessionid'),trunc(sysdate))

Formula Sql

select et.element_name, f.formula_name, f.effective_start_date, frt.*
from PAY_ELEMENT_TYPES_F et,
PAY_STATUS_PROCESSING_RULES_F p,
FF_FORMULAS_F f,
PAY_FORMULA_RESULT_RULES_v fr,
PAY_FORMULA_RESULT_RULES_d frt
where 1 =1
and et.element_name = :p_element_name
and p.element_type_id = et.element_type_id
and p.formula_id = f.formula_id
and p.STATUS_PROCESSING_RULE_ID = fr.STATUS_PROCESSING_RULE_ID
and fr.FORMULA_RESULT_RULE_ID = frt.FORMULA_RESULT_RULE_ID
order by 1,2,3,4,5

Date value including millisecond in PL/SQL

dbms_output.put_line(current_timestamp);

or

select current_timestamp
from dual;

Errors of BEE Process

select
source_type_meaning,
identifier,
line_text,
message_level,
assignment_number,
element_name,
control_type_meaning,
line_sequence,
source_id,
source_type,
batch_id
from
pay_paywsqee_messages
where
(source_type = 'l' or 'l' is null) and
(batch_id=:p_batch_id)

Include Balance Values in Reports

There are 2 way:
1) Assignment action mode
2) Date mode


select *
from pay_balance_types
where balance_name like 'D310%'


select *
from pay_balance_dimensions
where dimension_name = '_ASG_YTD'


select *
from PAY_DEFINED_BALANCES
where balance_type_id = 264
and balance_dimension_id = 114


---- Assignment action mode -----
declare
my_value number;
begin
my_value:= pay_balance_pkg.get_value
(
p_defined_balance_id => 553,
p_assignment_action_id => 38593,
);
dbms_output.put_line('value: ' my_value);
end;


---- Date mode ----
declare
my_value number;
begin
my_value:= pay_balance_pkg.get_value
(
p_defined_balance_id => 553,
p_assignment_id => 38593,
p_virtual_date => to_date('05072004','ddmmyyyy')
);
dbms_output.put_line('value: ' my_value);
end;

select ptp.period_name,
pay.payroll_name,
ptp.start_date,
ptp.end_date,
pac.assignment_id,
es.element_set_name,
et.element_name,
max(decode(dim.dimension_name,'_ASG_RUN', pay_balance_pkg.get_value( db.defined_balance_id, pac.assignment_action_id))) this_pay,
max(decode(dim.dimension_name,'_ASG_MTD', pay_balance_pkg.get_value( db.defined_balance_id, pac.assignment_action_id))) month_2_date,
max(decode(dim.dimension_name,'_ASG_YTD', pay_balance_pkg.get_value( db.defined_balance_id, pac.assignment_action_id))) fisc_year_2_date,
max(decode(dim.dimension_name,'_ASG_CAL_YTD', pay_balance_pkg.get_value( db.defined_balance_id, pac.assignment_action_id))) cal_year_2_date
from per_time_periods ptp,
pay_payrolls_f pay,
pay_payroll_actions ppa,
pay_assignment_actions pac,
pay_element_sets es,
pay_element_type_rules tr,
pay_element_types_x et,
per_assignments_f ass,
pay_balance_types bt,
pay_defined_balances db,
pay_balance_dimensions dim
where pay.payroll_name = NVL(:p_payroll_name ,pay.payroll_name)
and pay.payroll_id = ptp.payroll_id
and ptp.period_name = :p_period_name
and ptp.payroll_id = ppa.payroll_id
and ppa.action_type = 'R'
and ppa.date_earned between ptp.start_date and ptp.end_date
and ppa.payroll_action_id = pac.payroll_action_id
and ass.assignment_id = pac.assignment_id
and ptp.end_date between ass.effective_start_date and ass.effective_end_date
and utsc_hr_rpt_utilities_pkg.org_under_node (ass.organization_id, NVL(:p_org,81)) = 'Y'
-- Element belong to the Element Set
and ( es.BUSINESS_GROUP_ID IS NULL OR es.BUSINESS_GROUP_ID = 81 )
and ( es.LEGISLATION_CODE IS NULL OR es.LEGISLATION_CODE = 'AU' )
and ELEMENT_SET_NAME in ('UTS Taxable Income','UTS Non Taxable Income')
and tr.element_set_id = es.element_set_id
and tr.element_type_id = et.element_type_id
and include_or_exclude = 'I'
and bt.balance_name = element_name
and db.balance_type_id = bt.balance_type_id
and db.balance_dimension_id = dim.balance_dimension_id
and dim.dimension_name in ('_ASG_RUN','_ASG_MTD','_ASG_YTD','_ASG_CAL_YTD')
group by ptp.period_name,pay.payroll_name, ptp.start_date, ptp.end_date, pac.assignment_id, es.element_set_name, et.element_name

Useful queries for Balances

select pbt.balance_name, pet.element_name, piv.name input_value_name,piv.effective_start_date
from pay_balance_types pbt,
pay_balance_feeds_f pbf,
Pay_input_values_f piv,
pay_element_types_f pet
where pbt.balance_type_id = pbf.balance_type_id
and trunc(sysdate) between pbf.effective_start_date and pbf.effective_end_date
and piv.input_value_id = pbf.input_value_id
and pbf.effective_start_date between piv.effective_start_date and piv.effective_end_date
and pet.element_type_id = piv.element_type_id
and pet.effective_start_date between piv.effective_start_date and piv.effective_end_date
order by 1,2

select pbt.balance_name, pbd.dimension_name, pbd.description
from pay_defined_balances pdf,
pay_balance_types pbt,
pay_balance_dimensions pbd
where pdf.balance_type_id = pbt.balance_type_id
and pdf.balance_dimension_id = pbd.balance_dimension_id
order by 1,2

PTO Data Conversion

Problem
HOW DO I LOAD PTO EMPLOYEE VACATION & SICKNESS LEAVE BALANCES FROM A PREVIOUS SYSTEM?

Solution
Check the Accrual Plans
- Total Compensation -> Basic -> Accrual Plans

For each Plan Oracle generates three elements:
1. "Plan Name"- this represents the plan. An entry for this recurring element is given to each assignment in the plan;
2. "Plan Name" Carried Over - this is used to hold any PTO carried over from one term to the next;
3. "Plan Name" Residual - this is used to hold any unused PTO that cannot be carried over.

Oracle created 2 other elements to initialise the Accrual Plan:
‘Leave Entitlement Initialise’
‘Leave Accrual Initialise’

- Create Links to these Elements as appropriate. Datetrack to the start date of the element

To enroll an employee onto a plan, you need to give them an entry for the element. This element is one of the 3 elements generated when you define the plan. It is a recurring element so you will start the entry on the date the employee joins the plan and it will not be end dated until the employee leaves the plan. To load the PTO Employees from the previous system you need to give then an entry for the ‘Leave Entitlement Initialise’ and ‘Leave Accrual Initialise’ elements.
The input value Continuous Service Date should be populated only if the plan should calculate the participant’s length of service from a date other than the hire date. The calculation of the accrual will start always from the Start date of the Plan.

To load the Element Entries use the BEE.

Create a BEE Header for each Plan
For example:
Batch Name: ‘Annual_Leave_Plan’
Action If Entry Exists
Create new Entry Enabled
Reject If future change Enabled

Create a BEE line for each element.

In NZ these elements are called “Accrual Adjustment Element” and “Entitlement Adjustment Element”.
They have the same purpose of the Australian ones, just different names.

Attention:
· Effective Start date: start date of Pay period of Go-live
· Date Earned: Null
· If you are using the api to create the bach and if the format of an element input is Date, you need to load the value in varchar format with a specific mask: "to_char(start_date,'yyyy/mm/dd')"
· The api used by the BEE Transfer process doesn’t put the default values. You need to pass to populate all input values with a default when there is no data in the old system
· The BEE Transfer process doesn't put a value for input_value defined as NOT USER ENTERABLE
· Where an input_value has a QC, you have to create the BEE Lines with the meaning and not the code


Check the results in
- View -> Employee Accruals

N.B.
HRSS doesn't support ‘Leave Entitlement Initialise’ ‘Leave Accrual Initialise’ in the balance calculation