Retro Entries

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),prr.status,decode (pee.creator_type,'RR','Y','N') retroactive_flag,pee.source_end_date original_datefrom 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,pay_element_entries_f peewhere pay.payroll_name = NVL(:p_payroll_name ,pay.payroll_name)and pay.payroll_id = ptp.payroll_idand ptp.period_name like '8 2006%'and ptp.payroll_id = ppa.payroll_idand ppa.action_type in ( 'R', 'Q')and ppa.date_earned between ptp.start_date and ptp.end_dateand ppa.payroll_action_id = pac.payroll_action_idand pac.assignment_action_id = prr.assignment_action_idand pac.assignment_id = 227and ass.assignment_id = pac.assignment_idand ptp.end_date between ass.effective_start_date and ass.effective_end_dateand et.element_name like 'R%'and ptp.start_date between et.effective_start_date and et.effective_end_dateand piv.element_type_id = et.element_type_idand piv.name = 'Pay Value'and prv.input_value_id = piv.input_value_idand ptp.start_date between piv.effective_start_date and piv.effective_end_dateand prv.run_result_id = prr.run_result_idand prr.element_type_id = et.element_type_idand per.person_id = ass.person_idand pee.element_entry_id = prr.element_entry_idand ptp.end_date between pee.effective_start_date and pee.effective_end_date

Glossary

SOA : service-oriented architecture
BPEL: Business Process Execution Language
OLM: Oracle Learning Management
PTO: Paid Time Off
SOE: Statment Ordinary Earnings
HCM: Human Capital Management

Spinal Point query

SELECT gr.value
FROM per_spinal_point_placements_f sp,
per_spinal_point_steps_f sps,
pay_grade_rules_f gr
WHERE sp.assignment_id = p_assignment_id
AND p_effective_date BETWEEN sp.effective_start_date AND sp.effective_end_date
AND sp.business_group_id = p_business_group_id
AND sps.business_group_id = sp.business_group_id
AND sps.step_id = sp.step_id
AND p_effective_date BETWEEN sps.effective_start_date AND sps.effective_end_date
AND gr.business_group_id = sps.business_group_id
AND gr.GRADE_OR_SPINAL_POINT_ID = sps.SPINAL_POINT_ID
AND p_effective_date BETWEEN gr.effective_start_date AND gr.effective_end_date;

FTE definition

Full-time equivalent (FTE) is a way to measure a worker's productivity and/or involvement in a project. An FTE of 1.0 means that the person is equivalent to a full-time worker. An FTE of 0.5 may signal that the worker is only half-time, or that his projected output (due to differences in qualification, for example) is only half of what one may expect. Typically, different scales are used to calibrate this number, depending on the type of institution (schools, industry, research) and scope of the report (personnel cost, productivity).

The full time equivalent workforce describes the total number of full-time employees required to account for all paid ordinary time paid hours work. It is not a count of the number of employees. For example, if 18 people are employed (the headcount figure), 8 work full time and 10 of these people work part time (at 50% of a full time load) then the relative strength is:

8 full time (8 x 1.00 = 8.00) PLUS
10 part time at 50% (10 x .50 = 5.00)
a total of 13.00 FTE.

HR SS personalizations

A major change in SSHR approvals was introduced from SSHR V4.1 onwards. This is that approvals default to using Oracle Approvals Management (AME) and not the SSHR-specific method.
SSHR (or any other application) communicates with AME whenever it needs either: - a list of approvers, for example if the transaction uses dynamic approval, SSHR will call AME to get the approver names; - to get the next approver in the approval chain, for example when a manager in the chain has approved the transaction.

Each menu option in the SSHR responsibilities is defined as a function.These functions can be queried up in System Administrator ->Application->Functions.

Custom Functions:

* SSWA jsp function
*pAMETranType=SSHRMS&pAMEAppId=800&pProcessName=

XX_PAY_PAYMENTS_JSP_PRC&pItemType=

HRSSA&pCalledFrom=XX_PAY_EMP_PAYMENTS
*OA.jsp?akRegionCode=PAY_MPP_TOP_REGION&

akRegionApplicationId=801&OAFunc=

XX_PAY_EMP_PAYMENTS

Add the Custom Function to the HR_GLOBAL_SS_FUNCTIONS_CUSTOM Menu

In the Parameters field (under the Forms tab) you will see the AME parameters. They are &pAMETranType and &pAMEApplid. They identify the transaction type and application_id that are required by AME.If you remove the &pAMExxx parameters from this field then AME will not be called to identify the approvers. Instead the HR_APPROVAL_CUSTOM code will be used which can be edited to generate a list of approvers using PL/SQL.

How does Oracle Workflow differ from Oracle Alert?

Oracle Alert is a database event detection tool. Oracle Workflow technology enables automation and continuous improvement to business processes, routing information according to user-defined business rules. Oracle Alert is designed to detect database events, Oracle workflow is designed to manage the execution complex of business processes that result from database events.
Oracle Alert does contain some workflow type features such as response processing, that allow a sequence of actions to be taken depending on a users response to a message. Oracle Workflow's response processing capabilities are more advanced than Oracle Alert and it is recommended to use Oracle Workflow for new development.

Workflow Notifications Not Being Sent

Cause 1:
Workflow Notification Mailer
WF_NOTIFICATION_OUT is the queue where outbound notifications are queued up and WF_NOTIFICATION_IN is where inbound notifications are queued. Workflow Notification Mailer and the following listeners must be running smoothly to keep processing inbound and outbound notifications and flush these two queues. Otherwise, we see huge amount of data getting into one or both of queues leading to diskspace and performance issues. The following listeners must be running:
Workflow Deferred Agent Listener
Workflow Deferred Notification Agent Listener
Workflow Error Agent Listener
Workflow Inbound Notifications Agent Listener

All the Deferred Agent Listeners are Stopped.

As SYSADMIN:Navigate to Workflow Administrator Web Applications > Oracle Applications Manager > WorkflowManager > you notice that "Agent Listeners" are Unavailable.

Solution
As SYSADMIN:Navigate to Workflow Administrator Web Applications > Oracle Applications Manager > WorkflowManager > "Service Components".
Start "Workflow Deferred Agent Listener" , "Workflow Deferred Notification Agent Listener" ,"Workflow Error Agent Listener" , "Workflow Inbound Notifications Agent Listener" , "Workflow JavaDeferred Agent Listener" , "Workflow Java Error Agent Listener".After that, all the notifications should generate correctly.

Cause 2:
Some products may create a user or role without an e-mail, then updates with the e-mail later which would cause those roles to be QUERY where MAILHTML would be expected.

Solution
Check the notification preferences


select *
from fnd.WF_LOCAL_ROLES
where email_address is not null
and orig_system = 'PER'

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)

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