Export Payroll Configuration

------------------------------- Fast Formulas-----------------------------
select 'formula_name: ' formula_name , 'business_group_id: 'business_group_id, 'legislation_code: 'legislation_code, formula_textfrom ff_formulas_forder by 2,3,1

/* Save as txt */

insert into fnd_sessionsvalues (userenv('sessionid'),trunc(sysdate))

------------------------------------ Balances----------------------------------

select t.balance_name, t.business_group_id, t.legislation_code, t.balance_uom, fv.ELEMENT_NAME, fv.CLASSIFICATION, fv.BALANCE_FEED_ID, fv.INPUT_VALUE_NAME, fv.ADD_OR_SUBTRACT, fv.EFFECTIVE_START_DATE, fv.EFFECTIVE_END_DATE, fv.BUSINESS_GROUP_ID, fv.LEGISLATION_CODEfrom PAY_BALANCE_TYPES t, PAY_BALANCE_FEEDS_F f, PAY_BALANCE_FEEDS_V fvwhere t.balance_type_id = f.balance_type_idand f.balance_feed_id = fv.balance_feed_idorder by 2,1
------------------------------------------- Costing-------------------------------------------
@ Element Links Level

Select element_name,decode(et.business_group_id ,5254,'NZ',0,'AU',et.legislation_code) AU_NZ, decode(el.payroll_id,63,'Tourism Australia (Aus)', 81,'Tourism Australia Canberra (Aus)', 'Tourism Australia (NZ)') Payroll, gr.name grade, c.segment2 '' c.segment3 '' c.segment4 '' c.segment5 '' c.segment6 '' c.segment8 '' c.segment9 '' Costing, c.segment2 Office, c.segment3 Country, c.segment4 Department, c.segment5 Project, c.segment6 Function, c.segment8 Partnership_Australia, c.segment9 Spare , b.segment2 '' b.segment3 '' b.segment4 '' b.segment5 '' b.segment6 '' b.segment8 '' b.segment9 '' Balancing , b.segment2 Office, b.segment3 Country, b.segment4 Department, b.segment5 Project, b.segment6 Function, b.segment8 Partnership_Australia, b.segment9 Spare from pay_element_types_x et, pay_element_links_x el, per_grades gr, pay_cost_allocation_keyflex c, pay_cost_allocation_keyflex bwhere 1 =1 and el.element_type_id = et.element_type_id and el.COST_ALLOCATION_KEYFLEX_ID = c.COST_ALLOCATION_KEYFLEX_ID (+) and el.balancing_KEYFLEX_ID = b.COST_ALLOCATION_KEYFLEX_ID (+) and el.grade_id = gr.grade_id (+)order by decode(et.business_group_id ,5254,'NZ',0,'AU',et.legislation_code),1,3


--@ Organization level


select org.business_group_id, name, c.segment2 ''
c.segment3 ''
c.segment4 ''
c.segment5 ''
c.segment6 ''
c.segment8 ''
c.segment9 '' Costing,
c.segment2 Office,
c.segment3 Country,
c.segment4 Department,
c.segment5 Project,
c.segment6 Function,
c.segment8 Partnership_Australia,
c.segment9 Spare
from hr_all_organization_units org,
pay_cost_allocation_keyflex c,
HR_ORGANIZATION_INFORMATION info
where org.COST_ALLOCATION_KEYFLEX_ID = c.COST_ALLOCATION_KEYFLEX_ID (+)
and org.organization_id = info.organization_id
and info.org_information_context = 'CLASS'
and info.org_information1 = 'HR_ORG'
order by 1,2

-------------------------------------------- Element------------------------------------------
select et.*,cl.CLASSIFICATION_NAME classification, iv.*from pay_element_types_f et, pay_input_values_f iv, pay_element_classifications clwhere et.element_type_id = iv.element_type_id and et.classification_id = cl.classification_id order by et.business_group_id, et.element_name, iv.display_sequence


-------------------------------------------- Functions - Context------------------------------------------
SELECT f.BUSINESS_GROUP_ID, f.LEGISLATION_CODE, f.NAME, f.DATA_TYPE, f.DATA_TYPE_CODE, f.CLASS, f.CLASS_CODE, f.ALIAS_NAME, f.DESCRIPTION, f.DEFINITION, c.SEQUENCE_NUMBER, cont.* FROM FF_FUNCTIONS_V f, FF_FUNCTION_CONTEXT_USAGES c, FF_CONTEXTS contWHERE f.name like 'TA%' and f.function_id = c.function_id and c.context_id = cont.context_idorder by 1,3,c.sequence_number

----------------------------------------------- Function - Parameters---------------------------------------------
SELECT f.BUSINESS_GROUP_ID, f.LEGISLATION_CODE, f.NAME, f.DATA_TYPE, f.DATA_TYPE_CODE, f.CLASS, f.CLASS_CODE, f.ALIAS_NAME, f.DESCRIPTION, f.DEFINITION, p.* FROM FF_FUNCTIONS_V f, FF_FUNCTION_PARAMETERS pWHERE f.name like 'TA%' and f.function_id = p.function_idorder by 1,3,p.sequence_number

------------------------------- Global values-----------------------------
SELECT GLOBAL_NAME, business_group_id, GLOBAL_DESCRIPTION, DATA_TYPE_MEANING, GLOBAL_VALUE, EFFECTIVE_START_DATE, ROW_ID, GLOBAL_ID, EFFECTIVE_END_DATE, BUSINESS_GROUP_ID, LEGISLATION_CODE, DATA_TYPE, LAST_UPDATE_DATE, LAST_UPDATED_BY, LAST_UPDATE_LOGIN, CREATED_BY, CREATION_DATEFROM FF_GLOBALS_F_VWHERE (GLOBAL_NAME LIKE 'TA%') order by 2, global_name



--------------------
-- Absences
--------------------
select *
from PER_ABSENCE_ATTENDANCE_TYPES
order by 2,5

Debug PYUGEN

Bebugging of PYUGEN process, such as Payroll run, Quick Pay, Leave Liability Report for AU and NZ.

1) To turn Logging on:

In SQL*Plus:
SQL> insert into PAY_ACTION_PARAMETERS
values ('LOGGING', 'GMF');
commit;

The following is a list of the parameter letters and their definition:
G - General logging information *
M - Entry or exit routing information *
P - Performance Information
E - Element entry Information *
L - Balance fetching information
B - Balance maintenance information
I - Balance output information
R - Run results information *
F - Formula information *
V - Vertex tax calculation information *

2) Run the quickpay, payroll (using an assignment set), or process.

3) See the log file from the cuncurrent request

4) To turn payroll debug off delete the row from pay_action_parameters

Useful standard reports

Payroll Message Report
Payroll Costing Details Report
Payroll Reconciliation Report

Retrospective Payments & Taxation for AU

The Australian localisation now supports the taxation of retrospective payments
for Australia.

There are three methods of taxation:

1. Retrospective payments greater than 12 months
- Displays in Lump Sum E on Payment Summary.
- Taxed at a flat rate of 21.5%
2. Retrospective payments less than 12 months in current financial year
- Taxed at employees current marginal tax rate.
- Marginal rate determined by the number of pay periods the
payment is for.
3. Retro payments less than 12 months in previous financial year.
- Taxed at employees current marginal tax rate
- Marginal rate determined by the number of pay
periods in last financial year.

Earnings:
Any element that is to be included as a retro element must have
the AU_RETRO_PAY formula attached to it or, the formula incorporated in any existing
formulas attached to that element.

This formula will populate the Retrospective Balances depending
on when the retrospective payments were effective. That is, greater than 12 months,
less than 12 months..Etc.

Formula results need to be created to feed the retrospective
balances.

The balances are:

- Lump Sum E Payment (Retropay GT 12 Mths Amount Element)
- Retro LT 12 Mths Curr Yr Amount (Retropay LT 12 Mths Curr Yr Amount Element)
- Retro LT 12 Mths Prev Yr Amount (Retropay LT 12 Mths Prev Yr Amount Element)

Pre-tax deduction:
Any element that is to be included as a retro element must have the AU_SEEDED_PRETAX_RETRO_PAY formula attached to it or, the formula incorporated in any existing formulas attached to that element.

Voluntary Deduction:
No retroformula required

Direct payments:
No retroformula required

Employer Charges:
No retroformula required

Create a new Business Group

• Create an Organization with Classification "Business Group"
• Enter the Business Group Information from Others
• Edit the profile options at Responsibility Level listed below:

1. HR:Business Group = Business Group Name
2. HR:User Type = HR with Payroll User
3. MO: Operating Unit
4. HR: Security Profile

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)