Payroll Module Tables in Oracle HCM Cloud

-

  About Payroll Module

===================

What is Payroll Module?

What exactly we will do in it?

What are different components that we have in payroll module?

What are functional setups that comes in Payroll Module?


If an organization want to 

calculate payment or 

salary amount to their employees, 

make the payment, 

creating salary accounts 

and transfer the transations to General Ledger.

Or they may be export the transactions to sub-ledger groups.


For calculating

    Payments

    Accounts

The payroll module will be executed



Oracle Fusin Cloud Payroll Business Flow

=========================================

> Define Consolidation Group

> Define Organizational payment method(Check,Chash,Deposit..etc)

> Personal payment Method

> Third party payment method

> Define Payroll Definitions

> Retro, Proration pay methods

> Element Classifications

> Element

> Element Eligibility

> Input Values

> Element Categories

> Define Basic Element

> Define Bonus Element

> Define Salary basis>

> Attach Payroll To Employee

> Run the Quick Pay

> Payroll Costing

> prepayments

> Payroll tasks and Reports


Different Types of Payroll Executions

For Single Employee: Pick way  

Multiple Employees: Object Group flow 

For all the employees: Standard Payroll flow

===================================

select *from pay_consolidation_sets order by creation_date desc

link: CONSOLIDATION_SET_ID

select *from pay_all_payrolls_f order by creation_date desc

------------------------------

Real-Time Scenario

Write a SQL query to get following columns:

REPORTING_NAME,PAYROLL_NAME,PERIOD_TYPE,FIRST_PERIOD_END_DATE,NUMBER_OF_YEARS,CONSOLIDATION_SET_NAME,LedgerName and Paymethod

------------------------------

SELECT 

    papf.PAYROLL_ID,

    papf.REPORTING_NAME,

    papf.PAYROLL_NAME,

    papf.PERIOD_TYPE,

    papf.FIRST_PERIOD_END_DATE,

    papf.NUMBER_OF_YEARS,

    PCS.CONSOLIDATION_SET_NAME,

    GL.NAME AS LedgerName,

    popm.BASE_ORG_PAY_METHOD_NAME AS Paymethod

FROM pay_all_payrolls_f papf,

     pay_consolidation_sets PCS,

     gl_ledgers GL,

     pay_org_pay_methods_f POPM

WHERE papf.consolidation_set_id = PCS.consolidation_set_id

  AND papf.GL_SET_OF_BOOKS_ID = GL.LEDGER_ID

  AND papf.DEFAULT_PAYMENT_METHOD_ID = popm.ORG_PAYMENT_METHOD_ID

  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

  AND TRUNC(SYSDATE) BETWEEN popm.effective_start_date AND popm.effective_end_date

  ============================================

  SELECT 

    papf.PAYROLL_ID,

    papf.REPORTING_NAME,

    papf.PAYROLL_NAME,

    pldg.LEGISLATION_CODE,

    pldg.NAME AS Legislative_Data_Group,

    papf.PERIOD_TYPE,

    papf.FIRST_PERIOD_END_DATE,

    papf.NUMBER_OF_YEARS,

    pcs.CONSOLIDATION_SET_NAME,

    gl.NAME AS LedgerName,

    popm.BASE_ORG_PAY_METHOD_NAME AS Paymethod

FROM pay_all_payrolls_f papf,

     per_legislative_data_groups_vl pldg,

     pay_consolidation_sets pcs,

     gl_ledgers gl,

     pay_org_pay_methods_f popm

WHERE 1=1

AND papf.LEGISLATIVE_DATA_GROUP_ID = pldg.LEGISLATIVE_DATA_GROUP_ID(+)

  AND papf.consolidation_set_id = pcs.consolidation_set_id(+)

  AND papf.GL_SET_OF_BOOKS_ID = gl.LEDGER_ID(+)

  AND papf.DEFAULT_PAYMENT_METHOD_ID = popm.ORG_PAYMENT_METHOD_ID(+)

  AND TRUNC(SYSDATE) BETWEEN popm.effective_start_dateAND popm.effective_end_date

  AND TRUNC(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date

  AND pldg.LEGISLATION_CODE = 'US'

OR 

Payroll Elements Report SQL query

SELECT 

    PET.BASE_ELEMENT_NAME,

    PSC.BASE_CLASSIFICATION_NAME

FROM pay_element_types_f PET,

     pay_ele_classifications PSC  

WHERE PET.CLASSIFICATION_ID = PSC.CLASSIFICATION_ID

  AND TRUNC(SYSDATE) BETWEEN PET.effective_start_date AND PET.effective_end_date



=================

Important Tables in Global Payroll Module Oracle HCM Cloud

Core Payroll Setup

PAY_ALL_PAYROLLS_F

PAY_CONSOLIDATION_SETS

PAY_ORG_PAY_METHODS_F

2.Employee Payroll Configuration

PAY_PAY_RELATIONSHIPS_DN

PAY_ASSIGNED_PAYROLLS_DN

PAY_PERSON_PAY_METHODS_F

PAY_DIR_COMP_DETAILS_F

3.Element Setup & Entries (The "Inputs")

PAY_ELE_CLASSIFICATIONS_VL

PAY_ELEMENT_TYPES_F

PAY_INPUT_VALUES_F

PAY_ELEMENT_LINKS_F

PAY_ELEMENT_ENTRIES_F

PAY_ELEMENT_ENTRY_VALUES_F

4.Payroll Processing & Run Results

PAY_PAYROLL_ACTIONS

PAY_ASSIGNMENT_ACTIONS

PAY_ACTION_INTERLOCKS

PAY_RUN_RESULTS

PAY_RUN_RESULT_VALUES

5. Balances & Costing

PAY_BALANCE_TYPES_VL

PAY_COSTS

Comments

Post a Comment

Popular posts from this blog

Importance of Incremental Data in Oracle Fusion

-
 What is incremental data? Incremental Data Loads ----------------------- If you are sending employee data to a third-party payroll provider on every Friday, we don’t want to send all employees instead we only want the ones who changed current week. In this scenario, "LAST_UPDATE_DATE" column in the table plays vital role. LAST_UPDATE_DATE tells us when the record was physically saved. */ --Examples: ------------------------------- select count(1) from ( Select  papf.person_number, ppnf.FULL_NAME from  per_all_people_f papf, per_person_names_f ppnf WHERE 1=1 and papf.person_id=ppnf.person_id  and papf.LAST_UPDATE_DATE >=sysdate-7 --(Incremental) and ppnf.NAME_TYPE='GLOBAL' and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and sysdate between ppnf.EFFECTIVE_START_DATE and ppnf.EFFECTIVE_END_DATE ) Result:  55 Records(Only Changed Records in current week) --------------------------------- select count(1) from ( Select  papf.person_n...
Read more

Mastering Parameters in Oracle Fusion BI Publisher

-
Mastering Parameters in Oracle Fusion BI Publisher: Mandatory, Optional, and "All" Logic When building Data Models in Oracle Fusion, how you handle parameters determines the user experience and report performance. Here is the definitive syntax guide: 1. Mandatory Parameters Use this when a value must be provided for the report to run. Syntax: AND column =:parameter Example: AND Person_Number = :p_Person_Number 2. Optional Parameters (The "All" Logic) Use this to allow users to leave a field blank or select "All" to see all records. Syntax (Recommended):  AND (:parameter IS NULL OR column = :parameter) Example: AND (:p_Person_Number IS NULL OR Person_Number = :p_Person_Number) Note: Using NVL is common but can be slower on large datasets as it may bypass indexes. 3. Multi-Select Parameters Use this when the user can select multiple values from a list. Syntax: AND (column IN (:parameter) OR 'All' IN (:parameter)) Example: AND (Person_Number IN (:p_Pe...
Read more

REST Web Service in Oracle Fusion to manage Banks

-
 REST Web Service  ================= Rest Resources Resources would have 5 end-points(methods) usually.  Ex: Create a Bank Read-Get a/all Banks Update Bank Delete a Bank Fusion Navigation for managing Banks  Setup and Maintenance Tasks Global Tasks Manage Banks Get All Banks  /fscmRestApi/resources/11.13.18.05/cashBanks https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks Get a specific Bank  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Create a Bank  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks patch  Update  https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Put https://fa-eubg-saasfademo1.ds-fa.oraclepdemos.com/fscmRestApi/resources/11.13.18.05/cashBanks/{BankPartyId} Delete  https://fa-eubg-saas...
Read more