Basic Payroll Tables and their links

Basic Payroll Tables and their links, askhareesh blog for Oracle Apps

SELECT * FROM PAY_ELEMENT_TYPES_F(PK: ELEMENT_TYPE_ID)

SELECT * FROM PAY_ELEMENT_LINKS_F(PK: ELEMENT_LINK_ID)
                                  (To link PAY_ELEMENT_TYPES_F:ELEMENT_TYPE_ID)

SELECT * FROM PAY_ELEMENT_ENTRIES_F (PK: ELEMENT_ENTRY_ID)
                                    (To link PER_ALL_ASSIGNMENTS_F :ASSIGNMENT_ID,
                                     PAY_ELEMENT_LINKS_F :ELEMENT_LINK_ID,
                                     PAY_ELEMENT_TYPES_F :ELEMENT_TYPE_ID)
                                                                   
SELECT * FROM PAY_ELEMENT_ENTRY_VALUES_F (PK:ELEMENT_ENTRY_VALUE_ID)
                                         (To link PAY_ELEMENT_ENTRIES_F : ELEMENT_ENTRY_ID,
                                          PAY_RUN_RESULT_VALUES : INPUT_VALUE_ID,
                                          PAY_COSTS : INPUT_VALUE_ID)                                                                  

select * from PER_ALL_PEOPLE_F (PK:PERSON_ID)  

select * from PER_ALL_ASSIGNMENTS_F (PK:ASSIGNMENT_ID)
                                    (To link PER_ALL_PEOPLE_F:PERSON_ID,
                                     PER_JOBS:JOB_ID,
                                     PER_GRADES:GRADE_ID,
                                     PER_POSITIONS : POSITION_ID,
                                     PER_BUSINESS_GROUPS:BUSINESS_GROUP_ID,
                                     PER_ASSIGNMENT_STATUS_TYPES:ASSIGNMENT_STATUS_TYPE_ID,
                                     HR_LOCATIONS :LOCATION_ID,
                                     PAY_PAYROLLS_F : PAYROLL_ID)
                                                             
SELECT * FROM PER_PERSON_TYPE_USAGES_F (PK: PERSON_TYPE_USAGE_ID)
                                        (To link  PER_ALL_PEOPLE_F: PERSON_ID,
                                         PER_PERSON_TYPES:PERSON_TYPE_ID)

SELECT * FROM PER_PERSON_TYPES  (PK:PERSON_TYPE_ID)
                                 (To link  PER_PERSON_TYPE_USAGES_F : PERSON_TYPE_ID)



Read More »
*/

Basics of Core HRMS in Oracle Apps Part5

Basics of Core HRMS in Oracle Apps

Date Tracking

Functional aspects of date tracking have been explained before.
Date tracking enables to restore history after any changes have been done. Not all data in HRMS is date tracked. Technically, the tables ending with a ‘_f’ store the date tracked information. The primary key for such table comprises of an id field (e.g. person_id in per_all_people_f), effective_start_date and effective_end_date.
A condition needs to be put in every query to check for the effective start date and effective end date in order to fetch a single record.

Different Date track modes while updating the data are as follows:

Consider a small example. An assignment has start date 1-Jan-2001 and no effective end date (End of time 31-Dec-4712). The record is being viewed on 1-Jan-2005 from the application. Some changes are done to this assignment and these need to be saved. The system gives two options, Correction and Update

Correct
If Correction is selected, the current record with effective start date 1-Jan-2001 is updated with the new changes. i.e. there remains just one record after Correction is done.

Update
If Update is selected, a new record is created from 1-Jan-2005 till 31-Dec-4712 with the latest changes that are done. The existing record is end dated on 31-Dec-2004 and has the previous details. Thus, a history record is created that has the historical data.
If a record is updated on the same day when it was created and Update mode is selected, the mode is automatically changes to Correction.

Future Dated Changes
Consider the above assignment.
There are two records:
Start Date --> End Date
1-Jan-2001  31-Dec-2004
1-Jan-2005  31-Dec-4712

Effective date is set to 1-Jan-2004, displaying the first assignment record. Some changes are done and saved. Now, as of 1-Jan-2004, future dated change exists, which will become effective from 1-Jan-2005.

In such cases the system prompts for two options when the changes are saved:
Insert and Replace.

Insert
This will insert a new record before the next scheduled change. The change in future will be effective as of the start date of the change.
i.e. the new assignment records will be:

Start Date --> End Date
1-Jan-2001 -->31-Dec-2003
1-Jan-2004 --> 31-Dec-2004
1-Jan-2005 --> 31-Dec-4712

Replace
This option will replace the next future change with the current change. The future change will not exist anymore. i.e. the new assignment records will be:

Start Date --> End Date
1-Jan-2001 --> 31-Dec-2003
1-Jan-2004 --> 31-Dec-4712

Date Track Delete
When any date tracked data needs to be deleted, following options are available:

End Date
The record will be end dated and will not be visible on the screen after the end date.

Purge
The record will be totally removed from the database

In case of future dated records existing, following options are available:

All
All future updates are removed.

Next
Next applicable change is deleted. End date of the current record is set to the end date of the next change.



Read More »
*/

Basics of Core HRMS in Oracle Apps Part4

Basics of Core HRMS in Oracle Apps

Payroll

Navigation: Payroll --> Description

For faster pay processing, a group of employees are sorted using Payroll, whose pay processing can be done with a single frequency. The payroll frequency could be monthly, semi-monthly, weekly, daily etc. An employee is put on a payroll by assigning the payroll to the employee assignment. Payroll data is date tracked. To assign a payroll to an employee assignment, the payroll needs to be effective as of the effective date of the assignment.

Tables

PAY_ALL_PAYROLLS_F – PAYROLL_ID is linked with PER_ALL_ASSIGNMENTS_F.PAYROLL_ID

Payment Methods
Navigation: Payroll --> Payment Methods
Payment method is the way by which the employee would prefer to receive the pay. Payment method could be check, cash or NACHA.

Tables

PAY_PERSONAL_PAYMENT_METHODS_F – ASSIGNMENT_ID is linked with PER_ALL_ASSIGNMENTS_F.ASSIGNMENT_ID
PAY_ORG_PAYMENT_METHODS_F – PAYMENT_TYPE_ID is linked with PAY_PAYMENT_TYPES.PAYMENT_TYPE_ID

Consolidation Set
Navigation: Payroll --> Consolidation
A consolidation set must be selected before starting a payroll run. The consolidation set provides essential labeling for each payroll run.

Benefits and Compensation
Compensation and benefits for the employees are the integral part for any enterprise. The compensations and benefits can be setup in Oracle HRMS. All types of compensation and benefits and the rules that determine who will be paid what benefits, when and how often could be set up. With the date track option available, future dated changes can be done to take care of policy changes. Compensation heads such as earnings, deductions and other items set ups is discussed here. Please, refer the Appendix – A for benefits set ups. These heads are set up using elements.

Element
Navigation: Total Compensation --> Basic --> Element Description
This data is date tracked. The effective date for an element could be selected such that any historical entries could be made. Reporting name of the element is the name that appears on the reports. Primary classification states the purpose of the element. E.g. earnings, information, Employer liabilities, voluntary deductions etc.

Type of the element could be recurring (occurring periodically) or nonrecurring (occurring just once). Termination rule would be the rule when the element entries need to be stopped for an employee. Generally, Actual Termination is selected. Multiple Entries Allowed should be checked to receive more than one entry.
INPUT AND Output currencies will be based on the legislation. Additional information is stored in the Further Information field.

Input Value
Navigation: Total Compensation --> Basic --> Element Description --> Input Values
Up to 12 input values can be defined for an element. This data is date tracked. The name should be entered and the units. Units could be Hours, Money etc. To hold the payroll run results, the input value needs to be defined as Pay Value. The element input value could be required or optional. Default, minimum and maximum values could be entered. Minimum and Maximum value will help in validation.

Element Link
Navigation: Total Compensation --> Basic --> Link
Element links are used to determine which group of people is eligible for which elements.
The element, for which the link has to be created, needs to be selected in the window. In the eligibility criteria, the element can be linked to an organization, Job, Grade, People Group, Location, Position, Payroll, Employment Category and Salary Basis. The check box ‘Link To All Payrolls’ will link the element to all the employees.

Tables
PAY_ELEMENT_TYPES_F
PAY_ELEMENT_ENTIRES_F
PAY_INPUT_VALUES_F
PAY_ELEMENT_LINKS_F



Read More »
*/

Customer Conversion Stages and API's

Customer Conversion Stages and API's

Customer Conversion Stages and API's

1. party 
HZ_PARTY_V2PUB.create_organization

2. party contact point
HZ_CONTACT_POINT_V2PUB.create_contact_point

3. customer account
HZ_CUST_ACCOUNT_V2PUB.create_cust_account

4. customer account contact
HZ_PARTY_V2PUB.create_person

5. customer account org contact
HZ_PARTY_CONTACT_V2PUB.create_org_contact

6. customer account role
HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role

7. customer account role resp
HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility

8. customer account contact point
HZ_CONTACT_POINT_V2PUB.create_contact_point

9. physical location
HZ_LOCATION_V2PUB.create_location

10. party site
HZ_PARTY_SITE_V2PUB.create_party_site

11. party site contact point
HZ_CONTACT_POINT_V2PUB.create_contact_point

12. customer site
HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_acct_site

13. customer site use
HZ_CUST_ACCOUNT_SITE_V2PUB.create_cust_site_use

14. customer site contact
HZ_PARTY_V2PUB.create_person

15. customer site org contact
HZ_PARTY_CONTACT_V2PUB.create_org_contact

16. customer site role creation
HZ_CUST_ACCOUNT_ROLE_V2PUB.create_cust_account_role

17. customer site role resp
HZ_CUST_ACCOUNT_ROLE_V2PUB.create_role_responsibility

18. customer site contacts contact point
HZ_CONTACT_POINT_V2PUB.create_contact_point


Read More »
*/

Oracle HRMS Profile Options Part6

Oracle HRMS Profile Options
IRC: Default Max. Applicants
IRC_MAX_APPLICANTS_DFT
IRC: Document Upload Count Limit
IRC_TOTAL_DOC_UPLOAD_COUNT
IRC: External Framework Agent
IRC_FRAMEWORK_AGENT
IRC: Extract Skills
IRC_EXTRACT_SKILLS
IRC: Geocode Host
IRC_GEOCODE_HOST
IRC: HA Apply For Job Function
IRC_HA_APPLY_FOR_JOB_FUNC
IRC: HA Information Message
IRC_HA_INFO_MSG
IRC: HA Job Search Function
IRC_HA_JOB_SEARCH_FUNC
IRC: HA Login Page Function
IRC_HA_LOGIN_PAGE_FUNC
IRC: HA Redirect Information Message
IRC_HA_REDIRECT_INFO_MSG
IRC: Installed
IRC_INSTALLED_FLAG
IRC: Internal Posting Days
IRC_INTERNAL_POSTING_DAYS
IRC: Job Notification Function
IRC_JOB_NOTIFICATION_URL
IRC: Monthly Document Upload Count Limit
IRC_MONTHLY_DOC_UPLOAD_COUNT
IRC: Notification List Item Types
IRC_NOTIF_LIST_TYPE
IRC: Phone Types
IRC_PHONE_TYPES
IRC: Proxy Authorization Password
IRC_PROXY_AUTHORIZATION_PASSWORD
IRC: Proxy Authorization Username
IRC_PROXY_AUTHORIZATION_USERNAME
IRC: Registration Business Group
IRC_REGISTRATION_BG_ID
IRC: Registration Responsibility
IRC_REGISTRATION_RESP
IRC: Resume Parsing Sender Credential.
IRC_RESUME_PARSING_SENDER_CREDENTIAL
IRC: Resume Parsing Sender ID.
IRC_RESUME_PARSING_SENDER_ID
IRC: Resume Parsing Vendor URL
IRC_RESUME_PARSING_VENDOR_URL
IRC: Resume Required
IRC_RESUME_REQUIRED
IRC: Search Criteria Segment Matching
IRC_SEARCH_CRITERIA_SM
IRC: Suitable Seekers Function
IRC_SUITABLE_SEEKERS_URL
IRC: System Down Time
IRC_SYSTEM_DOWN_TIME
IRC: Text Background Color
IRC_TEXT_BACKGROUND_COLOR
IRC: Text Foreground Color
IRC_TEXT_FOREGROUND_COLOR
IRC: Vacancy Approval Function
IRC_VACANCY_APPROVAL_URL
IRC: Vacancy Approval Transaction Type
IRC_VAC_APPROVAL_TYPE
IRC: Workflow Item Type
IRC_WORKFLOW_ITEM_TYPE
PER:Automatic Save
PER_WIP_AUTOMATIC_SAVE
PER:Navigation
PER_WIP_NAVIGATION
Talent Management Source Type
HR_TALENT_MGMT_SRC_TYPE




Read More »
*/

Oracle HRMS Profile Options Part5

Oracle HRMS Profile Options
HR:Organization Information ID
PER_ORG_INFORMATION_ID
HR:Override Grade Defaults
HR_OVERRIDE_GRADE_DEFAULTS
HR:Personal Actions Menu
HR_PERSONAL_ACTIONS_MENU
HR:Personal Info Check Pending
HR_PERINFO_CHECK_PENDING
HR:Position Key Flex - Identify 1st Segment for Display
HR_POS_KEYFLEX_SEGMENT1
HR:Position Key Flex - Identify 2nd Segment for Display
HR_POS_KEYFLEX_SEGMENT2
HR:Purge Element Entry Permission
HR_ELE_ENTRY_PURGE_CONTROL
HR:Query Only Mode
PER_QUERY_ONLY_MODE
HR:Restrict Transactions Across Legislations in SSHR
HR_RESTRICT_X_BUSINESS_TRAN
HR:Self Service Save For Later
HR_SELF_SERV_SAVEFORLATER
HR:TCA_UNMERGE_PROCESS_OPTION
HR:TCA_UNMERGE_PROCESS_OPTION
HR:Use Global Applicant Numbering
PER_GLOBAL_APL_NUM
HR:Use Global Contingent Worker Numbering
PER_GLOBAL_CWK_NUM
HR:Use Global Employee Numbering
PER_GLOBAL_EMP_NUM
HR:Use Grade Defaults
HR_USE_GRADE_DEFAULTS
HR:Use Standard Attachments
PER_ATTACHMENT_USAGE
HR:User Type
HR_USER_TYPE
HR:View Payslip From Date for WEB (MM/DD/YYY)
HR_VIEW_PAYSLIP_FROM_DATE
HR:View Unpublished Employee 360 Self Appraisal
VIEW_UNPUBLISHED_360_SELF_APPR
HR:Webapps Tips Test Mode
HR_TIPS_TEST_MODE
HRMS: Workforce Turnover Calculation Method
HR_TRNVR_CALC_MTHD
IC CAP Form Query Only
IC CAP FORM QUERY ONLY
IRC: Accent Background Color
IRC_ACCENT_BACKGROUND_COLOR
IRC: Advert Posting Sender Credential
IRC_ADVERT_POSTING_SENDER_CREDENTIAL
IRC: Advert Posting Vendor URL
IRC_ADVERT_POSTING_VENDOR_URL
IRC: Agency Name
IRC_AGENCY_NAME
IRC: Alternate Instance URL
IRC_ALTERNATE_INSTANCE_URL
IRC: Anti Virus Server
IRC_VIRUS_SERVER
IRC: Availability Mode
IRC_AVAILABILITY_MODE
IRC: Background Check Sender Credential
IRC_BACKGROUND_CHECK_SENDER_CREDENTIAL
IRC: Background Checking Vendor URL
IRC_BACKGROUND_CHECK_VENDOR_URL
IRC: Core Background Color
IRC_CORE_BACKGROUND_COLOR
IRC: Default Country
IRC_DEFAULT_COUNTRY




Read More »
*/

Oracle HRMS Profile Options Part4

Oracle HRMS Profile Options
HR: Use Title in Person's Full Name
PER_USE_TITLE_IN_FULL_NAME
HR: Verification External Node
HR_VERF_EXTERNAL_NODE
HR: Views Layer Size
HR_VIEWS_LAYER_SIZE
HR: Web Proxy Host
HR_HTTP_PROXYHOST
HR: Web Proxy Port
HR_HTTP_PROXYPORT
HR: Worker Appraisals Menu
HR_WORKER_APPRAISALS_MENU
HR:Allow online W-2 viewing as of (MM/DD)
HR_VIEW_ONLINE_W2
HR:Blank Effective Date
HR_BLANK_EFFECTIVE_DATE
HR:Business Group
PER_BUSINESS_GROUP_ID
HR:Contingent Worker Manager Actions Menu
HR_CWK_MANAGER_ACTIONS_MENU
HR:Contingent Worker Personal Actions Menu
HR_CWK_ACTIONS_MENU
HR:Cross Business Group
HR_CROSS_BUSINESS_GROUP
HR:Data Pump Action Parameter Group
HR_DATA_PUMP_ACTION_PARAMETER_GROUP
HR:Date From
PER_DATE_FROM
HR:Default Assignment Costing
HR_DEFAULT_ASG_COST_ALLOC
HR:Default Payroll for Currency Conversions
HR_PAYROLL_CURRENCY_RATES
HR:Defer Update After Approval
HR_DEFER_UPDATE
HR:Display Competencies
HR_DISPLAY_SKILLS
HR:Display Person Search
HR_DISPLAY_PERSON_SEARCH
HR:Employee Directory Global Menu
HR_EMPDIR_GLOBAL_MENU
HR:Enable Bank Branch Validation
PAY_ENABLE_BANK_BRANCHES
HR:Enforce Costing Mandatory Segment Check At Data Entry
HR:COST_MAND_SEG_CHECK
HR:Execute Legislative Check Formula within Run
HR:EXECUTE_LEG_FORMULA
HR:Global Competence Flex Structure
HR_GLOBAL_COMPETENCE
HR:Grade Key Flex - Identify 1st Segment for Display
HR_GRADE_KEYFLEX_SEGMENT1
HR:Grade Key Flex - Identify 2nd Segment for Display
HR_GRADE_KEYFLEX_SEGMENT2
HR:Hide Work Phone on Person
HR_HIDE_WORK_PHONE_ON_PERSON
HR:Job Key Flex - Identify 1st Segment for Display
HR_JOB_KEYFLEX_SEGMENT1
HR:Job Key Flex - Identify 2nd Segment for Display
HR_JOB_KEYFLEX_SEGMENT2
HR:Manager Actions Menu
HR_MANAGER_ACTIONS_MENU
HR:Monitor Balance Retrieval
HR_MONITOR_BALANCE_RETRIEVAL
HR:Organization ID
PER_ORGANIZATION_ID




Read More »
*/