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