Search This Blog

Friday, July 28, 2017

What are the Tables that Store the Calculations from the Calculate Payroll Process

There will be not any direct table to get the Net Amount and Gross Amount.
Please use the RUN RESULTS tables to see the all the run results for the employee.  Also you can use Balance functions to get the Balance value for particular person on given date.

For Employee information
PER_ALL_PEOPLE_F
PAY_PAY_RELATIONSHIPS_DN

For run results
PAY_RUN_RESULTS
PAY_RUN_RESULT_VALUES

For Payment Method
PAY_PERSON_PAY_METHODS_F

For all HCM table information - please use below URL
http://docs.oracle.com/cloud/latest/globalcs_gs/OEDMH/toc.htm
To join these tables, the query will fetch requested information

select papf.person_number person_number
  ,pppmf.name payment_method_name
  ,PPA.PAYROLL_ACTION_ID payroll_action_id
  ,sum(nvl(decode(PBTT.BALANCE_NAME,'Net Payment',PRRV.RESULT_VALUE),0)) NET_PAYMENT
  ,sum(nvl(decode(PBTT.BALANCE_NAME,'Gross Pay',PRRV.RESULT_VALUE),0)) GROSS_PAY

from
PER_ALL_ASSIGNMENTS_m paam
,per_all_people_f papf
,per_periods_of_service ppos
,PAY_REL_GROUPS_DN prgd


,PAY_RUN_RESULTS PRR
,PAY_RUN_RESULT_VALUES PRRV
,PAY_BALANCE_FEEDS_F PBFF
,PAY_BALANCE_TYPES_TL PBTT
,PAY_PAYROLL_REL_ACTIONS PPRA
,PAY_PAYROLL_ACTIONS PPA
,pay_person_pay_methods_f pppmf


where paam.period_of_service_id = ppos.period_of_service_id
and paam.assignment_type='E'
and papf.person_id=paam.person_id
and prgd.GROUP_TYPE = 'A'
and prgd.payroll_relationship_id =PPRA.payroll_relationship_id
and prgd.assignment_id =paam.assignment_id
and ppos.date_start=(select max(pos1.date_start)
  from per_periods_of_service pos1
,per_all_assignments_f asg1
,pay_payroll_assignments ppa1
where PPA.date_earned >= pos1.date_start
  and pos1.person_id = asg1.person_id
  and asg1.assignment_id = ppa1.hr_assignment_id
  and asg1.period_of_service_id = pos1.period_of_service_id
  and ppa1.PAYROLL_RELATIONSHIP_ID = PPRA.PAYROLL_RELATIONSHIP_ID
and pos1.person_id = paam.person_id
and pos1.period_type = 'E')
AND TRUNC(SYSDATE) BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN paam.EFFECTIVE_START_DATE AND paam.EFFECTIVE_END_DATE

and papf.person_number=<PERSON_NUMBER>

AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID
AND PPRA.SOURCE_ID IS NULL
AND PPRA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRRV.INPUT_VALUE_ID = PBFF.INPUT_VALUE_ID
AND PBFF.BALANCE_TYPE_ID = PBTT.BALANCE_TYPE_ID
AND PBTT.LANGUAGE = 'US'
and PBTT.BALANCE_NAME in ('Net Payment','Gross Pay')
and pppmf.payroll_relationship_id = prgd.payroll_relationship_id
GROUP BY
  papf.person_number
,pppmf.name
,PPA.PAYROLL_ACTION_ID

Run Result Query in Fusion

select a.LEGISLATIVE_DATA_GROUP_ID,
e.payroll_relationship_number,f.full_name,D.RESULT_VALUE
from
PAY_PAYROLL_ACTIONS a, PAY_PAYROLL_REL_ACTIONS b, pay_run_results
c,PAY_RUN_RESULT_VALUES D,PAY_PAY_RELATIONSHIPS_Dn e , PER_person_names_f f
where a.LEGISLATIVE_DATA_GROUP_ID IN (SELECT LEGISLATIVE_DATA_GROUP_ID FROM
PER_LEGISLATIVE_DATA_GROUPS_VL where legislation_code IN ('SA')) and
a.action_type='R'
and a.PAYROLL_ACTION_id=b.PAYROLL_ACTION_id
and b.payroll_rel_action_id=c.payroll_rel_action_id
and c.element_type_id in
(select element_type_id from pay_element_types_vl
  where legislation_code= 'SA'
  and element_name like 'GOSI Reference Salary' ) AND D.RUN_RESULT_ID
=C.RUN_RESULT_ID and b.payroll_relationship_id=e.payroll_relationship_id
and e.person_id=f.person_id
and f.name_type='GLOBAL'
order by a.LEGISLATIVE_DATA_GROUP_ID, e.payroll_relationship_number;

SQL to validate bank load in Fusion

SQL to validate bank load
-------------------------------

SELECT
  bankparty.party_id bank_id
, bankparty.party_name bank_name
, bankorgprofile.home_country bank_home_country
, TO_CHAR(bankorgprofile.effective_start_date,'DD-MON-YYYY') bank_start_date
, TO_CHAR(bankorgprofile.effective_end_date,'DD-MON-YYYY') bank_end_date
, bankorgprofile.bank_or_branch_number bank_number
FROM
  hz_parties bankparty
, hz_organization_profiles bankorgprofile
, hz_code_assignments BankCA
WHERE
  BankCA.CLASS_CATEGORY = 'BANK_INSTITUTION_TYPE'
AND BankCA.CLASS_CODE IN ('BANK', 'CLEARINGHOUSE')
AND BankCA.OWNER_TABLE_NAME = 'HZ_PARTIES'
AND BankCA.OWNER_TABLE_ID = BankParty.PARTY_ID
AND bankorgprofile.party_id = BankParty.PARTY_ID
AND bankparty.party_type = 'ORGANIZATION'
AND trunc(SYSDATE) BETWEEN TRUNC(bankorgprofile.effective_start_date) AND NVL(TRUNC(bankorgprofile.effective_end_date),trunc(SYSDATE) + 1)

Thursday, June 1, 2017

Fusion Queries for Bank Name and Nationality


Bank Query:

Select distinct bank_name from pay_bank_accounts

 

Nationality:

select lookup_code,meaning from hr_lookups where lookup_type like 'NATIONALITY' and enabled_flag = 'Y'

Wednesday, May 24, 2017

Fusion Payroll Query to get Payroll Relationship and BU Name

select p.person_number,
py.payroll_name,haou.name BUName
from PAY_ASSIGNED_PAYROLLS_DN ap
,    pay_PAYROLL_TERMS pt
,    PAY_PAY_RELATIONSHIPS_DN pr
,    PAY_ALL_PAYROLLS_F py
,    PER_ALL_PEOPLE_F p
,    per_all_assignments_m paam
,    hr_all_organization_units haou
,    (select distinct person_id from per_person_secured_list_v) ppsl
where ap.payroll_term_id = pt.payroll_term_id
and   pt.payroll_relationship_id = pr.payroll_relationship_id
and   ap.payroll_id = py.payroll_id
and   pr.person_id = p.person_id
--and p.person_number = '2824'
and ap.lsed is null
and paam.person_id=p.person_id
and trunc(sysdate) between paam.effective_start_date and paam.effective_end_date
and trunc(sysdate) between p.effective_start_date and p.effective_end_date
and paam.assignment_type = 'E'
and paam.primary_flag = 'Y'
and haou.organization_id = paam.business_unit_id
and ppsl.person_id = p.person_id

Sunday, May 7, 2017

Encryption Concept

1. Customer will encrypt the date using Fusion provided public key.
2. Security Console setup will be done in Fusion which will decrypt the data before loading to system
3. Data to be sent out will be encrypted using Customer provided public key. So customer needs to provide public key.
4. Always public keys are shared.

Thursday, March 16, 2017

Taleo Client Connect (TCC) erroring out with Workflow Execution Failed error

Error while running TCC from Unix server

While running TCC through UNIX below error is coming.

2017-03-16 18:27:25,543 [ERROR] Client - The workflow execution failed!
Error: The HTTP request was unable to resolve the specified host.Use the monitoring feature to get more information.
2017-03-16 18:27:25,543 [ERROR] Client - The workflow execution failed!
Error: The HTTP request was unable to resolve the specified host.Use the monitoring feature to get more information.
2017-03-16 18:27:25,543 [ERROR] Client - The workflow execution failed!
Error: The HTTP request was unable to resolve the specified host.Use the monitoring feature to get more information.

Resolution:

In the cfg file for Send, Poll and Retrieve tabs, End Point source should be "Provided in the Current Step"