SQL to get Payroll Balances in Oracle Fusion HCM






SELECT DISTINCT
    papf.person_number,
    pni.national_identifier_number,
    prb.balance_value,
    ppa.effective_date,
    pbtv.balance_name,
    pbd.base_dimension_name
FROM
    per_all_people_f           papf,
    per_national_identifiers   pni,
    per_periods_of_service     ppos,
    per_all_assignments_f      paaf,
    pay_payroll_assignments    ppasg,
    pay_pay_relationships_dn   pprd,
    pay_payroll_rel_actions    ppra,
    pay_run_results            prr,
    pay_run_result_values      prrv,
    pay_input_values_f         pivf,
    pay_balance_feeds_f        pbf,
    pay_balance_types_vl       pbtv,
    pay_payroll_actions        ppa,
    pay_all_payrolls_f         prl,
    pay_run_balances           prb,
    pay_defined_balances       pdb,
    pay_balance_dimensions     pbd
WHERE 1=1
    AND papf.person_number = <P_PERSON_NUMBER>
    AND trunc(SYSDATE) BETWEEN papf.effective_start_date AND papf.effective_end_date
    AND papf.primary_nid_id = pni.national_identifier_id (+)
    AND pni.national_identifier_type (+) = 'SSN'
    AND ppos.person_id = papf.person_id
    AND paaf.person_id = ppos.person_id
    AND paaf.period_of_service_id = ppos.period_of_service_id
    AND trunc(SYSDATE) BETWEEN paaf.effective_start_date AND paaf.effective_end_date
    AND paaf.assignment_id = ppasg.hr_assignment_id
    AND paaf.work_terms_assignment_id = ppasg.hr_term_id
    AND trunc(SYSDATE) BETWEEN ppasg.start_date AND ppasg.end_date
    AND ppasg.payroll_relationship_id = pprd.payroll_relationship_id
    AND trunc(SYSDATE) BETWEEN pprd.start_date AND pprd.end_date
    AND ppra.payroll_relationship_id = pprd.payroll_relationship_id
    AND ppra.payroll_relationship_id = ppasg.payroll_relationship_id
    AND ppra.payroll_rel_action_id = prr.payroll_rel_action_id
    AND prrv.run_result_id = prr.run_result_id
    AND pivf.input_value_id = prrv.input_value_id
    AND pbf.input_value_id = pivf.input_value_id
    AND pbtv.balance_type_id = pbf.balance_type_id
    AND pbf.input_value_id = pivf.input_value_id
    AND ppra.payroll_action_id = ppa.payroll_action_id
    AND prl.payroll_id = ppa.payroll_id
    AND prb.payroll_relationship_id = ppra.payroll_relationship_id
    AND prb.payroll_rel_action_id = ppra.payroll_rel_action_id
    AND trunc(prb.effective_date) = trunc(ppa.effective_date)
    AND pdb.defined_balance_id = prb.defined_balance_id
    AND pdb.balance_type_id = pbtv.balance_type_id
    AND pdb.balance_dimension_id = pbd.balance_dimension_id
    AND pbd.base_dimension_name = 'Core Relationship Tax Unit Run'
    AND pbtv.balance_name = <P_Balance_Name>
--and trunc(PPA.effective_date) = to_date('07/19/2019','MM/DD/YYYY')
ORDER BY
    papf.person_number, ppa.effective_date DESC

Comments

  1. AnonymousOctober 31, 2022 at 2:59 AM

    Hello Nice Blog, good HCM Info.

    ReplyDelete
  2. AnonymousOctober 31, 2022 at 2:59 AM

    Hello, Very good Blog excellent HCM info.

    ReplyDelete

Post a Comment

Popular posts from this blog

User Account in Oracle Fusion HCM

Image
Configuration for User Account: 1.        Set up for automatic User Account Creation. Go to FSM --> Manage Enterprise HCM Information --> This set up will ensure that user accounts are created automatically for person. 1.        User Name generation method. This set up is done in Security Console. Go to Tools -->  Security Console -->  User Categories This will ensure if work email address is there in the Worker.dat file while loading worker information – this will create user account by default which is same as the person’s work email address. However, this will be pending status as we need to Send Pending LDAP Requests to reflect the user name. Post successful completion of ESS job “Send Pending LDAP Requests” the user account will be reflected as person’s work email address. In case there is no work email addre...
Read more

Using Generic SOAP Port Webservice to Upload file to UCM

Image
In this article, we will learn about how to use SOAP Service to upload files to UCM. This is used for automation of interfaces in Oracle Cloud Fusion HCM. In this example, we will use SOAP UI to test the service. Same concepts can be applied in Fusion Middleware or any other platform to invoke Web Service to upload files to UCM. You can upload the file by Base64 encoding the binary content of the file, in the Content tag as below: <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:ucm="http://www.oracle.com/UCM"> <soapenv:Header xmlns:wsa="http://www.w3.org/2005/08/addressing"> <wsa:Action>urn:GenericSoap/GenericSoapOperation</wsa:Action> </soapenv:Header> <soapenv:Body> <ucm:GenericRequest webKey="cs"> <ucm:Service IdcService="CHECKIN_NEW"> <ucm:Document> <ucm:Field name="dUser">imp</ucm:Field> <ucm:Field name=...
Read more