Trace

hr_utility.set_trace_options ('TRACE_DEST:DBMS_OUTPUT');

hr_utility.trace_on;

....

hr_utility.trace_off;

Retro Pay

1. Create the Assignment Set
2. Create the Element Set with Standard Hours, Recreation Leave, Recreation Leave Payment, Tax Deduction etc… (In NZ use PAYE Tax Deduction and Student Loan Deduction)
3. Setup the BackPay Element. The classification has to be = to the classification of the element that it refers. Non Recurring, Multiple Entries Allowed. The inputs: Hours, where applicable, Pay Value, where applicable. For Leave, please create a back pay element of the Absence and one for the Leave payment element. Please use same display sequence for main element and backpay element.
4. Setup the Retro pay Tab of the Element that you want to be processed on Retro Pay
5. Create the Element Link of the Backpay Elements
6. Attach the formula AU_RETRO_PAY at the main elements. See "Retrospective Payments & Taxation for AU" Post
   

Run Process:

• Run Quick pay or Run
• Make the changes on the Element Entry Screen or Absence Screen with the correct effective date (the date has to be within the pay period already processed)
• Run the Retro Pay by Elements

Transfer to GL

Transfer to GL

1) Mapping of the the GL Flexfield: Payroll> GL Flexfield Map

2) Setup Costing Information at Element Links and Organizations level:

Costed = 'Y'

Costing Flexfield - For Debit Account
Balancing Flexfield - For Credit Account

N.B.For Involuntary Deduction, Pre-Tax Deduction, Tax Deduction, Termination Deduction and Voluntary Deduction elements the Costing is:
Costing Flexfield - For Credit Account
Balancing Flexfield - For Debit Account

For details see:
Total Compensation > Basic > Classification
See the Costing TAB

The Balance Flexfield is just at Element Links Level. All the segments need to be filled here.

Also
Setup costing for the followed elements:

• Create the Annual Leave Liability and the LSL Liability Element if applicable

Classification : Information
Input: Pay Value

• Create the balance "PLAN_NAME"_LIABILITY.

Feed = "Liability Element"

Dimensions = _ASG_RUN, _ASG_YTD

• Change the Formula created automatically by Oracle "PLAN_NAME"_ORACLE_PAYROLL

• On the Run Result link Annual Leave Liability Element and the LSL Liability Element with "PLAN_NAME"__ORACLE_PAYROLL formula

• Create the element links and setup the costing KFF

2) Run "Payroll Run"

3) Run "Pre-payment"

4) Run "Costing"

5) Run "Partial Period Accrual Calculation" for the days between the end of the pay period and the end of the accounting period, end of the month. (If applicable)

28st Feb ----------GL acct period--------
-----------------------------------PayPd--------PayPd--------PayPd--------PayPd---- 11th Feb 25th Feb 10th March

Parameters:

1. A period_date which (as described above) is used to indicate the period to base the estimate calculations on. Thus the customer can specify that the estimate costs are based on runs in the current or previous payroll periods. In the above example if the estimate costs are to be based on the payroll runs executed in the previous (January) payroll period this parameter should be given a date somewhere in that January period.
2. A accounting_date (end of accounting period)

6) Run " Transfer to GL"

7) Choose a GL responsibility (General Ledger Super User)

8) Journals> Import> RunSource = PayrollSelection Criteria = "No Group Id"Post Error to Suspence = 'Y'Choose ImportSee the log and the Output

9) Query the JournalJournal > EnterSource = Payroll
The result will be 2 different jurnals:

1. February
2. March(With the same costing amount)

10) Post the Jurnals

11)When the actual costs become available at the end of pay period (10th March), Run the Payroll

12) Run the pre-payments

13) Run the costing process for the current pay period (25-feb-2005 10-mar-2005)

14) Run the Tranfer to GL Process

15) Import the Journals for Source = Payroll

16) Check the results in

Journal > EnterSource = Payroll
The result will be 4 journals:

1. Reverses for FEB-05 (Created automatically, not needed the reverse action of the journal)
2. Reverses for MAR-05
3. New Journal for FEB-05
4. New Journal for MAR-05
   
   

Queries:

select debit_or_credit, sum(costed_value) from pay_costs
where assignment_action_id = XXX

select sum(entered_dr), sum(entered_Cr)
from gl_interface
where reference22 in (select to_char(cost_allocation_keyflex_id) from
pay_costs where assignment_action_id = XXX)
and date_created =
to_date('15-JAN-2004','dd-mon-yyyy')

Steps to generate the Payslip (Australia)

1. QuickPay Run or Run
2. PrePayments
3. Pay Advice (Australia). For printed payslip
4. Payslip Archive (Australia). Step required to see the payslip in ESS
5. Direct Entry (Australian BECS Format)

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)