Employee Assignment Details Query
Introduction
This Post Provides the SQL Query that returns the Assignment Details of the Employees
SELECT peo.person_number employee_number, loc.internal_location_code location_code, loc.location_name LOCATION, per_name.last_name, per_name.first_name, per_name.middle_names middle_name, pa.address_line_1, pa.address_line_2, pa.town_or_city city, pa.region_2 state, pa.postal_code zip_code, TO_CHAR (per.date_of_birth, ‘YYYY-MM-DD’) birth_date, TO_CHAR (ser.date_start, ‘YYYY-MM-DD’) last_hire_date, TO_CHAR (ser.actual_termination_date, ‘YYYY-MM-DD’) termination_date, DECODE (per_leg.sex, ‘M’, ‘Male’, ‘F’, ‘Female’, NULL) gender, asg_status.user_status employment_status, pj.job_code, pj.NAME job_name, pay.payroll_name pay_group FROM per_all_people_f peo, per_persons per, per_person_names_f per_name, per_people_legislative_f per_leg, per_periods_of_service ser, per_national_identifiers per_ssn, per_all_assignments_m asg, per_assignment_status_types_tl asg_status, per_person_addresses_v pa, hr_locations_all loc, per_jobs pj, pay_all_payrolls_f pay, pay_rel_groups_dn payrel, pay_assigned_payrolls_dn papd WHERE per.person_id = peo.person_id AND per_name.person_id = peo.person_id AND per_leg.person_id = peo.person_id AND per_ssn.person_id(+) = peo.person_id AND asg.person_id = peo.person_id AND ser.person_id = peo.person_id AND peo.person_id = pa.person_id(+) AND asg.assignment_id = payrel.assignment_id AND loc.location_id(+) = asg.location_id AND asg.job_id = pj.job_id(+) AND per_ssn.national_identifier_id(+) = peo.primary_nid_id AND per_name.legislation_code = asg.legislation_code AND per_leg.legislation_code = asg.legislation_code AND asg.period_of_service_id = ser.period_of_service_id AND papd.payroll_id = pay.payroll_id AND papd.payroll_term_id = payrel.parent_rel_group_id AND asg.assignment_status_type_id = asg_status.assignment_status_type_id AND per_name.name_type = ‘GLOBAL’ AND asg.primary_flag = ‘Y’ AND asg.system_person_type = ‘EMP’ AND asg.assignment_status_type = ‘ACTIVE’ –AND asg.employment_category = ‘FR’ –AND peo.person_number = ‘671047’ AND pa.address_type = ‘HOME’ AND payrel.group_type = ‘A’ AND ser.date_start = (SELECT MAX (ser1.date_start) FROM per_periods_of_service ser1 WHERE ser1.person_id = ser.person_id) AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN peo.effective_start_date AND peo.effective_end_date AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN per_name.effective_start_date AND per_name.effective_end_date AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN per_leg.effective_start_date AND per_leg.effective_end_date AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN asg.effective_start_date AND asg.effective_end_date AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN pa.effective_start_date(+) AND pa.effective_end_date(+) AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN loc.effective_start_date(+) AND loc.effective_end_date(+) AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN pay.effective_start_date(+) AND pay.effective_end_date(+) AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN payrel.start_date AND payrel.end_date AND NVL (TRUNC (ser.actual_termination_date), TRUNC (SYSDATE)) BETWEEN papd.start_date AND NVL (papd.lspd, TO_DATE (’31/12/4712′, ‘DD/MM/YYYY’))
To Know More about Post
Email : [email protected]
Start typing and press Enter to search
- Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
- Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
- OverflowAI GenAI features for Teams
- OverflowAPI Train & fine-tune LLMs
- Labs The future of collective knowledge sharing
- About the company Visit the blog
Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Get early access and see previews of new features.
Oracle SQL - How to get the assignment id of the record with the maximum date
I'm trying to get the assignment id of the employee who was the last occupant of the position. That would be the last assignment who had the position or the maximum date. How can I retrieve both in the below query?
I'm converting the maximum date to a character in order to substring it out in the result.
- greatest-n-per-group
5 Answers 5
Problems like that can easily be solved using a window function:
The part max(paaf.effective_start_date) over (partition by position_id) as max_start_date is essentially the same as a max(paaf.effective_start_date) ... group by position_id but without the need to group the whole result.
As you are only selecting a single position_id you could use over () instead - but by using over (partition by position_id) the query could also be used to retrieve that information for multiple positions.
- This does not give me the assignment_id that the max effective_start_date is for – Superdooperhero Commented Nov 27, 2015 at 11:31
- @Superdooperhero In what way doesn't it? It gives you the row(s) where the effective_start_date is the highest, and you can then easily just select the columns you're interested in. Perhaps you ought to consider updating your question to provide some sample data and expected output so that people trying to help you can actually run their queries against some actual data, instead of having to guess what you mean. – Boneist Commented Nov 27, 2015 at 11:35
- 1 @Superdooperhero The query returns the complete row where the effective_start_date is the highest for the the given position_id . If that row doesn't contain the assignment_id you are looking for you need to provide some sample data ( edit your question, don't post that information as comments). Ideally as insert into statements or as a sqlfiddle.com example – user330315 Commented Nov 27, 2015 at 11:35
As long as I know, you can't get another value of the row of the result returned by an aggregate function, you need to use a subquery. I think you should do something like:
- it's wrong because we can get this aggregation via analytic functions max(to_char(paaf.effective_start_date, 'yyyymmdd')) over (partition by ***) – are Commented Nov 27, 2015 at 11:07
- It's also wrong because you can use the KEEP FIRST/LAST function to retrieve the corresponding values from other columns for, e.g., the row with the max date. – Boneist Commented Nov 27, 2015 at 11:30
- This is not a single query – Superdooperhero Commented Nov 27, 2015 at 11:32
- @Superdooperhero What do you mean it's not a single query? It looks like one to me... – Boneist Commented Nov 27, 2015 at 11:41
If you only want a single id:
If you want all the IDs associated with the latest date then:
- I'm looking for the id that goes with the maximum date – Superdooperhero Commented Nov 27, 2015 at 11:34
- If you are looking for a singular ID then the option using ROWNUM will give that. – MT0 Commented Nov 27, 2015 at 11:36
Here's an alternative, which doesn't need a subquery or an analytic function to find the value you're after:
Your Answer
Reminder: Answers generated by artificial intelligence tools are not allowed on Stack Overflow. Learn more
Sign up or log in
Post as a guest.
Required, but never shown
By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy .
Not the answer you're looking for? Browse other questions tagged sql oracle greatest-n-per-group or ask your own question .
- The Overflow Blog
- Where does Postgres fit in a world of GenAI and vector databases?
- Mobile Observability: monitoring performance through cracked screens, old...
- Featured on Meta
- We've made changes to our Terms of Service & Privacy Policy - July 2024
- Bringing clarity to status tag usage on meta sites
- What does a new user need in a homepage experience on Stack Overflow?
- Feedback requested: How do you use tag hover descriptions for curating and do...
- Staging Ground Reviewer Motivation
Hot Network Questions
- Cramer's Rule when the determinant of coefficient matrix is zero?
- How can I prove the existence of multiplicative inverses for the complex number system
- How can moral disagreements be resolved when the conflicting parties are guided by fundamentally different value systems?
- What are some refutations to the etymological fallacy?
- Why are complex coordinates outlawed in physics?
- If inflation/cost of living is such a complex difficult problem, then why has the price of drugs been absoultly perfectly stable my whole life?
- Why was this lighting fixture smoking? What do I do about it?
- Where to donate foreign-language academic books?
- Where does the energy in ion propulsion come from?
- Background for the Elkies-Klagsbrun curve of rank 29
- I'm trying to remember a novel about an asteroid threatening to destroy the earth. I remember seeing the phrase "SHIVA IS COMING" on the cover
- How did Oswald Mosley escape treason charges?
- Stuck on Sokoban
- How can I get the Thevenin equivalent of this circuit?
- Planning to rebuild detached storage room (in the US)
- How to remove obligation to run as administrator in Windows?
- Has the US said why electing judges is bad in Mexico but good in the US?
- A short story about a boy who was the son of a "normal" woman and a vaguely human denizen of the deep
- about flag changes in 16-bit calculations on the MC6800
- "TSA regulations state that travellers are allowed one personal item and one carry on"?
- What is the difference between a "Complaint for Civil Protection Order" and a "Motion for Civil Protection Order"?
- Reference request: acceleration/curvature of curve in metric space
- Image Intelligence concerning alien structures on the moon
- What to do when 2 light switches are too far apart for the light switch cover plate?
- Implementing Case Management
What are work assignments?
You use the assignment engine to assign resources (for example, service personnel or territory owners) to the business objects they must work on, such as a . Being assigned to business objects gives resources and their manager's visibility into the business object.
You also use rule-based assignment to assign additional resources to objects.
Candidate and Work Objects
When setting up assignments, you must be familiar with two types of assignment objects: candidate objects and work objects.
Work objects are the business objects that are assigned, for example, cases .
Candidate objects are the possible pool of assignment candidates, for example, resources.
Rule-Based Assignment
Rule-based assignment lets you set up more rules that are used to assign resources to work objects. After you set up the rules containing the conditions that records must meet when resources match the rule conditions, they're assigned to the object.
For example, you use rules to assign a certain agent to a certain queue when the customer is in a specific state or region.
Rule-based assignment requires that you plan your rules, create the rules using the rules UI, and set profile options to configure the assignment behavior, in addition to any scheduled processes that must be run.
Assignment Profile Options
Each of the business objects available in assignment has its own set of profile options that enable you to further configure the application behavior.
Scheduled Processes
Scheduled processes are batch jobs that capture data and let business objects to act on that data. You must schedule several processes when using assignment. See the Service Request Queue Assignment topic in the Oracle Fusion Cloud Sales Understanding Scheduled Processes guide.
Assignment Reports
You use the Diagnostic Dashboard to generate reports about the assigned objects and the volume of territory data involved in assignment.
Assignment Resources
To learn more about assignment, see the following resources:
Online help: Use the keyword assignment to search for the relevant topics.
Assignment Resource Center: See the Assignment Manager Resource Center page on My Oracle Support (Doc ID 1522958.1) for more resources.
- Manage VIP Account
- Register for VIP Plan
- VIP Member-Only Content
- HCM Data Loader
- HCM Extract
- BI Publisher
- Fast Formula
- OTBI Analytics
- Personalizations
- Scheduled Processes
- Absence Management
- Performance Management
- Talent Management
- Time & Labor
- HCM Extracts Questions
- HCM Data Loader Questions
- BI Reports Questions
- Report Issues/suggestions
- Your Feedback counts
- Write for Us
- Privacy Policy
- Join Telegram Group
- Join LinkedIn Group
- Join Facebook Page
SQL Query to fetch Assignment Level Element Entries
- Post category: BI Publisher
- Post comments: 0 Comments
- Post last modified: November 30, 2021
- Reading time: 3 mins read
In this article, we will fetch the Element Entries attached to assignment level
SQL Query :
You Might Also Like
How to load Position Costing using HDL?
How to fetch the Employee Social Insurance Calculation Card details for AE legislation?
Fix for SQL query time out error in BI Reports
Session expired
Please log in again. The login page will open in a new tab. After logging in you can close it and return to this page.
IMAGES
VIDEO
COMMENTS
Fusion HCM Query to Fetch Assignment Details Home / Company Blog / Fusion HCM Query to Fetch Assignment Details SELECT papf.person_number, ppnf.full_name employee_full_name, ppnf.first_name, ppnf.last_name, to_char (ppos.date_start, 'MM/DD/YYYY') date_start, paam.assignment_number, paam.primary_flag, paam.assignment_status_type active_status,
sql_statement; select rg.relationship_group_id payroll_assignment_id, rg.start_date, rg.end_date, rg.payroll_relationship_id, rg.parent_rel_group_id payroll_term_id, rg.term_id hr_term_id, rg.assignment_id hr_assignment_id, rg.legal_employer_id, pr.person_id. from pay_rel_groups_dn rg, pay_pay_relationships_dn pr. where rg.group_type = 'a'
In this post we will look into the SQL Query to get the employee personal and assignment information which is the most frequent requirement when we have any integrations with third party systems.
In Oracle Fusion HCM (Human Capital Management), you can query employee details using SQL queries against the appropriate tables and views. Generally, the information might be stored in tables like…
sup.manager_assignment_id supervisor_assignment_id, a.set_of_books_id set_of_books_id, a.default_code_comb_id default_code_comb_id, ps.actual_termination_date actual_termination_date, a.project_title project_title, a.billing_title billing_title, email.email_address, a.expense_check_address, a.assignment_type, paf.attribute1 attribute1,
Assignment and Worker category for an assignment can be fetched by the below mentioned query.
Overview of Assignments, Mappings, and Rules. For more information about assignment mappings, assignment rules, and configuring assignment, see the following topics in the Oracle Fusion Cloud Sales Automation Implementing Sales guide: How the mapping set components work together in assignment processing.
AssignedPayroll.dat HDL file will be used to load the Assigned Payroll information into the HCM system. SQL Query to get this information: Sample output will look like: If you like the content, please follow us on LinkedIn , Facebook, and Twitter to get updated with the latest content. Do you know how to load the AssignedPayrolls and extract ...
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts. Oracle Fusion Accounting Hub - Version 11.1.1.5.1 and later: How to Determine Roles Assigned to User in Fusion Application Database Using SQL Queries.
SQL Query: You can pass the Manager Person as input to the above query to get the reportees reporting to that manager. There is one more condition which is commented above.. Manager_Level = '1'. Manager Level of '1' indicates direct reportees to that manager and anything above '1' will indicate indirect reportees.
Some Commonly Used Queries in Oracle HCM Cloud. In any typical ERP Implementation, there are requirements to prepare multiple reports. Most of the Reports try to display the Assignment related details like Job, Grade, Location, Position, Department, BusinessUnit, Assignment Status along with other fields from different Tables.
We are seeing a difference between the "Users license Usage" numbers in the Usage reports for HCM Base cloud Licenses and number of "actual user report employees, contingent worker" with Active assignment status in Production. Is there any query available to find the exact number of Employees/CW/Non workers with active assignment status ?
The Assignment Status in Oracle Fusion HCM can often be found in the "Manage Employment" or "Manage Assignments" section, depending on the configuration and customization of your system.
name; requisition_interface_id. requisition_interface_code. enterprise_id. requisition_title. interface_type. interface_source. interface_source_id. vacancy_status
Applies to: Oracle Fusion Global Human Resources Cloud Service - Version 11.13.20.07. and later Oracle Fusion Global Human Resources - Version 11.12.1.. and later Information in this document applies to any platform. This note was created for Release 11.13.19.04.. The note has been reviewed and is current for release 11.13.20.07..
In this article we will look into queries to pull the Work schedule information at employee level and legal employer level. The actual work schedule information will be stored in ZMM_SR_SCHEDULES_TL table. However the link to assignment/legal employer resides in PER_SCHEDULE_ASSIGNMENTS with different Resource Types. Table of Contents.
Employee Assignment Details Query. / Employee Assignment Details Query. Introduction. This Post Provides the SQL Query that returns the Assignment Details of the Employees. Query. SELECT peo.person_number employee_number, loc.internal_location_code location_code, loc.location_name LOCATION, per_name.last_name, per_name.first_name, per_name ...
@Superdooperhero The query returns the complete row where the effective_start_date is the highest for the the given position_id. If that row doesn't contain the assignment_id you are looking for you need to provide some sample data (edit your question, don't post that information as comments).
Oracle Fusion Global Human Resources Cloud Service - Version 11.13.24.04. and later Information in this document applies to any platform. Symptoms. Change Assignment - Page is not rendering Information STEPS-----The issue can be reproduced at will with the following steps: My Client Groups -> Change Assignment >
What are work assignments? You use the assignment engine to assign resources (for example, service personnel or territory owners) to the business objects they must work on, such as a . Being assigned to business objects gives resources and their manager's visibility into the business object. You also use rule-based assignment to assign ...
Do you know how to fetch the element entries assigned at Assignment level? Read on to know more details.
Topic: Accessing the Oracle APEX, ORDS and Database Actions from the Private Autonomous Database Using the OCI Bastion Service - Part 2. Introduction: Welcome to Part 2 of this two series blog which demonstrates creating and using the privately deployed Autonomous database services, with Oracle Bastion deployed in a Private Network of the OCI ...
Content Requirement: 1) Report should display employees belonging to the departments of the selected organization type.
Oracle Fusion Receivables Cloud Service - Version 11.13.24.04. and later: FAQ: ... FAQ: Modify Bursting Query To Deliver the BI Report Output To Different Destinations (Doc ID 3032226.1) Last updated on AUGUST 27, 2024. Applies to: Oracle Fusion Receivables Cloud Service - Version 11.13.24.04. and later Information in this document applies to ...
I can see that there is a field Work Terms Assignment ID in the assignments table, but i guess this field Work Terms Number is different. Summary I am trying to use HSDL for correcting assignment information for some employees which requires the Work Terms Number and i am unable to find it outContent I am trying to use the spreadsheet templates ...