ORACLE PAYROLL

Module

Description

Query

Paryoll

Payslip Details

SELECT ppa.date_earned, per.full_name, per.employee_number, NVL (pet.reporting_name, pet.element_name), piv.NAME, prrv.result_value, ptp.period_name FROM pay_payroll_actions ppa, pay_assignment_actions pac, per_all_assignments_f ass, per_all_people_f per, pay_run_results prr, pay_element_types_f pet, pay_input_values_f piv, pay_run_result_values prrv, per_time_periods_v ptp WHERE ppa.payroll_action_id = pac.payroll_action_id AND pac.assignment_id = ass.assignment_id AND ass.effective_end_date = TO_DATE ('12/31/4712', 'MM/DD/RRRR') AND ass.person_id = per.person_id AND per.effective_end_date = TO_DATE ('12/31/4712', 'MM/DD/RRRR') AND pac.assignment_action_id = prr.assignment_action_id AND prr.element_type_id = pet.element_type_id AND prr.run_result_id = prrv.run_result_id AND pet.element_type_id = piv.element_type_id AND piv.input_value_id = prrv.input_value_id AND ppa.time_period_id = ptp.time_period_id ----------------------------------------- AND pet.element_name = 'Basic Salary' AND piv.NAME = 'Pay Value' AND per.employee_number = '91314' and per.person_id = 44427 AND ptp.period_name like '6 2008 Calendar Month' ORDER BY 1;

Paryoll

To get all the actions for an assignment

SELECT paa.assignment_id, ppa.payroll_id, ppa.business_group_id, hrl.meaning, ppa.action_status, ppa.effective_date, ppa.date_earned, ppa.payroll_action_id, paa.assignment_action_id, paa.action_status, paa.action_sequence, paa.source_action_id, paa.run_type_id FROM pay_assignment_actions paa, pay_payroll_actions ppa, hr_lookups hrl WHERE paa.payroll_action_id = ppa.payroll_action_id AND ppa.action_type = hrl.lookup_code AND hrl.lookup_type = 'ACTION_TYPE' AND paa.assignment_id = <<assignment_id>> ORDER BY paa.action_sequence DESC;

Paryoll

To get all the Element Entries for an Assignment

SELECT DISTINCT pet.element_name, pet.element_type_id, piv.NAME input_value, piv.input_value_id, pee.element_entry_id, pee.creator_type, pee.entry_type, pev.element_entry_value_id, pev.screen_entry_value, pev.effective_start_date, pev.effective_end_date, pee.creator_id, pee.source_id, pee.source_asg_action_id, pee.source_start_date, pee.source_end_date FROM pay_element_types_f pet, pay_input_values_f piv, pay_element_entries_f pee, pay_element_entry_values_f pev WHERE pee.assignment_id = <<assignment_id>> AND piv.element_type_id = pet.element_type_id AND pee.element_type_id = pet.element_type_id AND pee.element_entry_id = pev.element_entry_id AND pev.input_value_id = piv.input_value_id ORDER BY 1, 6, 5;

Paryoll

To get Run Results for an Assignment

SELECT DISTINCT pet.element_name, piv.NAME, ppa.action_type, ppa.effective_date payroll_run_date, paa.assignment_action_id, ppa.payroll_action_id, prr.run_result_id, prv.result_value, prr.entry_type, prr.source_id, prr.source_type, prr.start_date, prr.end_date, prr.element_entry_id, prv.formula_result_flag FROM pay_element_types_f pet, pay_input_values_f piv, pay_run_results prr, pay_run_result_values prv, pay_assignment_actions paa, pay_payroll_actions ppa WHERE paa.assignment_id = <<assignment_id>> AND paa.payroll_action_id = ppa.payroll_action_id AND paa.assignment_action_id = prr.assignment_action_id AND prr.run_result_id = prv.run_result_id AND prv.input_value_id = piv.input_value_id AND pet.element_type_id = prr.element_type_id AND piv.element_type_id = pet.element_type_id AND ppa.action_type IN ('Q', 'R', 'V', 'B') ORDER BY 1, 2, 3, 4, 9, 10;

Payroll

Consolidated Payroll Run Results after Prepayment - Group by Organization

/* Consolidated Payroll run results grouped by Organization */ --Select ppp.ASSIGNMENT_ACTION_ID, ppp.BASE_CURRENCY_VALUE, papf.FULL_NAME, org.NAME, org.ORGANIZATION_ID, pyact.PAYROLL_ACTION_ID, pyact.EFFECTIVE_DATE, to_char(pyact.EFFECTIVE_DATE,'MMYYYY') MMYYYY Select org.NAME, sum (ppp.BASE_CURRENCY_VALUE) ORG_SAL_SUM, count(paaf.ASSIGNMENT_ID) HEAD_COUNT from PAY_PRE_PAYMENTS ppp, PAY_ASSIGNMENT_ACTIONS pyaa, per_all_assignments_f paaf, Per_all_people_f papf, HR_ALL_ORGANIZATION_UNITS org, PAY_PAYROLL_ACTIONS PYACT where ppp.ASSIGNMENT_ACTION_ID = pyaa.ASSIGNMENT_ACTION_ID and paaf.ASSIGNMENT_ID = pyaa.ASSIGNMENT_ID and paaf.PERSON_ID = papf.PERSON_ID and paaf.ORGANIZATION_ID = org.ORGANIZATION_ID and pyact.PAYROLL_ACTION_ID = pyaa.PAYROLL_ACTION_ID and pyact.PAYROLL_ID = 61 --and to_char(pyact.EFFECTIVE_DATE,'MMYYYY') = nvl(:P,(to_char(pyact.EFFECTIVE_DATE,'MMYYYY'))) and to_char(pyact.EFFECTIVE_DATE,'MMYYYY') = :P_DATE and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE group by org.NAME

Payroll

Consolidated Payroll Run Results after Prepayment - Employee

/* Consolidated Payroll run results grouped by Organization */ Select papf.EMPLOYEE_NUMBER, papf.FULL_NAME, ppp.BASE_CURRENCY_VALUE --Select org.NAME, sum (ppp.BASE_CURRENCY_VALUE) ORG_SAL_SUM, count(paaf.ASSIGNMENT_ID) HEAD_COUNT from PAY_PRE_PAYMENTS ppp, PAY_ASSIGNMENT_ACTIONS pyaa, per_all_assignments_f paaf, Per_all_people_f papf, HR_ALL_ORGANIZATION_UNITS org, PAY_PAYROLL_ACTIONS PYACT where ppp.ASSIGNMENT_ACTION_ID = pyaa.ASSIGNMENT_ACTION_ID and paaf.ASSIGNMENT_ID = pyaa.ASSIGNMENT_ID and paaf.PERSON_ID = papf.PERSON_ID and paaf.ORGANIZATION_ID = org.ORGANIZATION_ID and pyact.PAYROLL_ACTION_ID = pyaa.PAYROLL_ACTION_ID --and pyact.PAYROLL_ID = 61 --and to_char(pyact.EFFECTIVE_DATE,'MMYYYY') = nvl(:P,(to_char(pyact.EFFECTIVE_DATE,'MMYYYY'))) and to_char(pyact.EFFECTIVE_DATE,'MMYYYY') = :P_DATE and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE

Payroll

Employee Element Screen Entry Value

Select papf.EMPLOYEE_NUMBER, papf.FULL_NAME, pyet.ELEMENT_NAME, pyiv.NAME, pyeev.SCREEN_ENTRY_VALUE from PAY_ELEMENT_ENTRY_VALUES_F pyeev, PAY_ELEMENT_TYPES_F pyet, PAY_INPUT_VALUES_F pyiv, PAY_ELEMENT_ENTRIES_F pyeef, per_all_assignments_f paaf, per_all_people_f papf where pyeev.INPUT_VALUE_ID = pyiv.INPUT_VALUE_ID and pyiv.ELEMENT_TYPE_ID = pyet.ELEMENT_TYPE_ID and pyeev.ELEMENT_ENTRY_ID = pyeef.ELEMENT_ENTRY_ID and paaf.ASSIGNMENT_ID = pyeef.ASSIGNMENT_ID and papf.PERSON_ID = paaf.PERSON_ID and sysdate between pyeev.EFFECTIVE_START_DATE and pyeev.EFFECTIVE_END_DATE and sysdate between pyet.EFFECTIVE_START_DATE and pyet.EFFECTIVE_END_DATE and sysdate between pyiv.EFFECTIVE_START_DATE and pyiv.EFFECTIVE_END_DATE and sysdate between pyeef.EFFECTIVE_START_DATE and pyeef.EFFECTIVE_END_DATE and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and pyet.ELEMENT_NAME like 'Basic Salary'

Payroll

Payroll Run Results

/* Payroll Run Results */ Select papf.FULL_NAME, papf.EMPLOYEE_NUMBER, pyetf.ELEMENT_NAME, pyiv.NAME, pyrrv.RESULT_VALUE,pyapf.PAYROLL_NAME, pypa.ACTION_STATUS, pypa.ACTION_TYPE, pypa.EFFECTIVE_DATE, to_char(pypa.EFFECTIVE_DATE,'MM-YYYY') from per_all_people_f papf, Per_all_assignments_f paaf, PAY_ASSIGNMENT_ACTIONS pyaa, PAY_RUN_RESULT_VALUES pyrrv, PAY_RUN_RESULTS pyrr, PAY_INPUT_VALUES_F pyiv, Pay_element_types_f pyetf, PAY_ALL_PAYROLLS_F pyapf, PAY_PAYROLL_ACTIONS pypa where papf.PERSON_ID = paaf.PERSON_ID and paaf.ASSIGNMENT_ID = pyaa.ASSIGNMENT_ID and pyaa.ASSIGNMENT_ACTION_ID = pyrr.ASSIGNMENT_ACTION_ID and pyrr.RUN_RESULT_ID = pyrrv.RUN_RESULT_ID and pyrr.ELEMENT_TYPE_ID = pyetf.ELEMENT_TYPE_ID and pyiv.INPUT_VALUE_ID = pyrrv.INPUT_VALUE_ID and pyetf.ELEMENT_TYPE_ID = pyiv.ELEMENT_TYPE_ID and pyapf.PAYROLL_ID = paaf.PAYROLL_ID and pypa.PAYROLL_ID = pyapf.PAYROLL_ID and pyaa.PAYROLL_ACTION_ID = pypa.PAYROLL_ACTION_ID -- PARAMETER -- --and pyetf.ELEMENT_NAME like 'Thirteen Month Salary' and to_char(pypa.EFFECTIVE_DATE,'MMYYYY') = :P_RUN_DATE -- Payroll Run Date --and pypa.ACTION_TYPE = :P_ACT_TYPE -- Payroll Action Type e.g. Payroll Run, Quick Pay -- pypa.ACTION_STATUS = :P_ACT_STATUS -- Status as Completed or Errored and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE and sysdate between pyetf.EFFECTIVE_START_DATE and pyetf.EFFECTIVE_END_DATE and sysdate between pyapf.EFFECTIVE_START_DATE and pyapf.EFFECTIVE_END_DATE order by papf.EMPLOYEE_NUMBER, pyetf.ELEMENT_NAME, pyiv.NAME

Payroll Reconcile

Payroll Run Results

/* Payroll Run Results */ Select papf.FULL_NAME, papf.EMPLOYEE_NUMBER, pyapf.PAYROLL_NAME, ppos.ACTUAL_TERMINATION_DATE, sum(pyrrv.RESULT_VALUE) TOTAL_DEDUCT --papf.FULL_NAME, papf.EMPLOYEE_NUMBER, pyetf.ELEMENT_NAME, pyiv.NAME,pyrrv.RESULT_VALUE,pyapf.PAYROLL_NAME, pypa.ACTION_STATUS,pypa.ACTION_TYPE, pypa.EFFECTIVE_DATE, to_char(pypa.EFFECTIVE_DATE,'MM-YYYY') PAY_DATE, pyec.CLASSIFICATION_NAME from per_all_people_f papf, Per_all_assignments_f paaf, PAY_ASSIGNMENT_ACTIONS pyaa, PAY_RUN_RESULT_VALUES pyrrv, PAY_RUN_RESULTS pyrr, PAY_INPUT_VALUES_F pyiv, Pay_element_types_f pyetf, PAY_ALL_PAYROLLS_F pyapf, PAY_PAYROLL_ACTIONS pypa, PAY_ELEMENT_CLASSIFICATIONS pyec, PER_PERIODS_OF_SERVICE PPOS where papf.PERSON_ID = paaf.PERSON_ID and paaf.ASSIGNMENT_ID = pyaa.ASSIGNMENT_ID and pyaa.ASSIGNMENT_ACTION_ID = pyrr.ASSIGNMENT_ACTION_ID and pyrr.RUN_RESULT_ID = pyrrv.RUN_RESULT_ID and pyrr.ELEMENT_TYPE_ID = pyetf.ELEMENT_TYPE_ID and pyiv.INPUT_VALUE_ID = pyrrv.INPUT_VALUE_ID and pyetf.ELEMENT_TYPE_ID = pyiv.ELEMENT_TYPE_ID and pyapf.PAYROLL_ID = paaf.PAYROLL_ID and pypa.PAYROLL_ID = pyapf.PAYROLL_ID and pyaa.PAYROLL_ACTION_ID = pypa.PAYROLL_ACTION_ID and pyec.CLASSIFICATION_ID = pyetf.CLASSIFICATION_ID and ppos.PERSON_ID = papf.PERSON_ID -- PARAMETER -- --and pyetf.ELEMENT_NAME like 'Thirteen Month Salary' and to_char(pypa.EFFECTIVE_DATE,'MMYYYY') = :P_RUN_DATE -- Payroll Run Date --and pypa.ACTION_TYPE = :P_ACT_TYPE -- Payroll Action Type e.g. Payroll Run, Quick Pay -- pypa.ACTION_STATUS = :P_ACT_STATUS -- Status as Completed or Errored and pypa.EFFECTIVE_DATE between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and pypa.EFFECTIVE_DATE between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE and pypa.EFFECTIVE_DATE between pyetf.EFFECTIVE_START_DATE and pyetf.EFFECTIVE_END_DATE and pypa.EFFECTIVE_DATE between pyapf.EFFECTIVE_START_DATE and pyapf.EFFECTIVE_END_DATE and papf.EMPLOYEE_NUMBER = '1493' and pyiv.NAME like 'Pay Value' and pyec.CLASSIFICATION_NAME like '%Deduct%' group by papf.FULL_NAME, papf.EMPLOYEE_NUMBER, pyapf.PAYROLL_NAME, ppos.ACTUAL_TERMINATION_DATE

Payroll

Employee Current Balances

/* Current Run Opening Balances */ Select papf.EMPLOYEE_NUMBER, papf.FULL_NAME, pybt.BALANCE_NAME, pyalb.VALUE, pyalb.PREV_BALANCE_VALUE, (pyalb.VALUE-pyalb.PREV_BALANCE_VALUE) DIFERENCE, pybd.DIMENSION_NAME from apps.per_all_people_f papf, apps.per_all_assignments_f paaf, apps.PAY_ASSIGNMENT_ACTIONS pyaa, apps.PAY_ASSIGNMENT_LATEST_BALANCES pyalb, -- apps.PAY_PERSON_LATEST_BALANCES -- This Table uses the Balances from person apps.PAY_DEFINED_BALANCES pydb, apps.PAY_BALANCE_TYPES pybt, apps.pay_balance_dimensions pybd where papf.PERSON_ID = paaf.PERSON_ID and paaf.ASSIGNMENT_ID = pyaa.ASSIGNMENT_ID and pyaa.ASSIGNMENT_ACTION_ID = pyalb.ASSIGNMENT_ACTION_ID and pyalb.DEFINED_BALANCE_ID = pydb.DEFINED_BALANCE_ID and pydb.BALANCE_TYPE_ID = pybt.BALANCE_TYPE_ID and pyalb.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID and pybd.BALANCE_DIMENSION_ID = pydb.BALANCE_DIMENSION_ID and sysdate between papf.EFFECTIVE_START_DATE and papf.EFFECTIVE_END_DATE and sysdate between paaf.EFFECTIVE_START_DATE and paaf.EFFECTIVE_END_DATE order by papf.EMPLOYEE_NUMBER,pybd.DIMENSION_NAME

Fast Formula

Formula Result Rules Screen Query

Select /*** Element Processing Rule ***/ psprf.ELEMENT_TYPE_ID, ACT_ELE.ELEMENT_NAME, psprf.FORMULA_ID, FF.FORMULA_NAME, /*** Formula Results ***/ PFRRF.RESULT_NAME, DECODE (PFRRF.RESULT_RULE_TYPE,'D','Direct Result','M','Message','S','Stop','I','Indirect result') Result_Type, PFRRF.ELEMENT_TYPE_ID, res_ele.ELEMENT_NAME RESULT_ELEMENT,PFRRF.INPUT_VALUE_ID, PIVF.NAME IV , PFRRF.SEVERITY_LEVEL, DECODE (PFRRF.SEVERITY_LEVEL,'I','Information','W','Warning') MSG_SEVERITY from PAY_STATUS_PROCESSING_RULES_F PSPRF, PAY_FORMULA_RESULT_RULES_F PFRRF, PAY_ELEMENT_TYPES_F ACT_ELE, FF_FORMULAS_F FF, PAY_ELEMENT_TYPES_F RES_ELE, PAY_INPUT_VALUES_F PIVF where -- Formula Processing Rules -- psprf.STATUS_PROCESSING_RULE_ID = pfrrf.STATUS_PROCESSING_RULE_ID and psprf.ELEMENT_TYPE_ID = ACT_ELE.ELEMENT_TYPE_ID and psprf.FORMULA_ID = FF.FORMULA_ID -- Formula Results links -- and PFRRF.ELEMENT_TYPE_ID = RES_ELE.ELEMENT_TYPE_ID(+) and PFRRF.INPUT_VALUE_ID = PIVF.INPUT_VALUE_ID(+) -- Effective Date -- and sysdate between psprf.EFFECTIVE_START_DATE and psprf.EFFECTIVE_END_DATE and sysdate between pfrrf.EFFECTIVE_START_DATE and pfrrf.EFFECTIVE_END_DATE and sysdate between ACT_ELE.EFFECTIVE_START_DATE and ACT_ELE.EFFECTIVE_END_DATE and sysdate between FF.EFFECTIVE_START_DATE and FF.EFFECTIVE_END_DATE

Payroll

Employee Balance Details

Select * from apps.pay_gb_balances_v

PAYROLL

*******

Script to find Bank Branches, Bank Accounts which did NOT upgrade

Background:

When we upgrade from 11.5.10 to R12, Bank Branches, Bank Accounts of type INTERNAL/PAYROLL did NOT migrate/upgrade.

Use the following script to identify the same.

Scripts:

1. Query to find the Branches that did not upgrade:

SELECT bb.bank_branch_id,

bb.bank_name,

bb.bank_branch_name,

bb.country

FROM ap_bank_branches bb, ce_upg_bank_rec cb, ce_bank_branches_v cbv

WHERE     cb.source_pk_id(+) = bb.bank_branch_id

AND cb.party_id = cbv.branch_party_id(+)

AND cb.bank_entity_type(+) = 'BRANCH'

AND cbv.bank_branch_name IS NULL

ORDER BY bb.bank_name;

2. Query to find the Bank Accounts that did not upgrade:

SELECT pay.bank_account_id, pay.bank_account_name, pay.bank_account_num

FROM ap_bank_accounts_all pay,

ce_bank_accounts cash,

ce_upg_bank_accounts cuba

WHERE     pay.bank_account_id = cuba.source_pk_id(+)

AND cuba.ce_bank_account_id = cash.bank_account_id(+)

AND cash.bank_account_name IS NULL

ORDER BY pay.bank_account_name;

Solution:

Apply post upgrade patch 13563833:R12.CE.B.

Refer Note 1419206.1 for additional details.

Note: You can run following queries to reconcile between 11i and R12

11i

select count(*) from ap_bank_accounts_all;

select count(*) from ap_bank_account_uses_all;

R12

select count(*) from IBY_EXT_BANK_ACCOUNTS;

select count(*) from CE_BANK_ACCOUNTS;

select count(*) from CE_BANK_ACCT_USES_ALL;